Planned Goods Issue Date with several conditions


#1

Hello community,

I have the following problem regarding the planned goods issue date in SAP.
I would like to get the first and the last planned goods issue date to analyze how much time is between these dates.
Therefore I have created the following code:
First planned goods issue date:

CASE
**WHEN ISNULL(PU_FIRST(“VBAP”, “_CEL_O2C_ACTIVITIES”.“CHANGED_FROM”, “_CEL_O2C_ACTIVITIES”.“CHANGED_FIELD” = ‘WADAT’)) = 0 **
THEN
(
TO_TIMESTAMP(
CASE
WHEN STR_TO_INT(PU_FIRST(“VBAP”, “_CEL_O2C_ACTIVITIES”.“CHANGED_FROM”, “_CEL_O2C_ACTIVITIES”.“CHANGED_FIELD” = ‘WADAT’)) = 0
THEN PU_FIRST(“VBAP”, “_CEL_O2C_ACTIVITIES”.“CHANGED_TO”, “_CEL_O2C_ACTIVITIES”.“CHANGED_FIELD” = ‘WADAT’)
WHEN STR_TO_INT(PU_FIRST(“VBAP”, “_CEL_O2C_ACTIVITIES”.“CHANGED_FROM”, “_CEL_O2C_ACTIVITIES”.“CHANGED_FIELD” = ‘WADAT’)) > 19700101
THEN PU_FIRST(“VBAP”, “_CEL_O2C_ACTIVITIES”.“CHANGED_FROM”, “_CEL_O2C_ACTIVITIES”.“CHANGED_FIELD” = ‘WADAT’)
ELSE NULL END
, ‘YYYYMMDD’)
)
ELSE
(
TO_TIMESTAMP(CASE WHEN STR_TO_INT(PU_FIRST(“VBAP”, “VBEP”.“WADAT”)) > 19700101 THEN PU_FIRST(“VBAP”, “VBEP”.“WADAT”) ELSE NULL END, ‘YYYYMMDD’)
)
END
Last planned goods issue date:

CASE
WHEN ISNULL(PU_LAST(“VBAP”, “_CEL_O2C_ACTIVITIES”.“CHANGED_TO”, “_CEL_O2C_ACTIVITIES”.“CHANGED_FIELD” = ‘WADAT’)) = 0
THEN
(
TO_TIMESTAMP(CASE WHEN STR_TO_INT(PU_LAST(“VBAP”, “_CEL_O2C_ACTIVITIES”.“CHANGED_TO”, “_CEL_O2C_ACTIVITIES”.“CHANGED_FIELD” = ‘WADAT’)) > 19700101
THEN PU_LAST(“VBAP”, “_CEL_O2C_ACTIVITIES”.“CHANGED_TO”, “_CEL_O2C_ACTIVITIES”.“CHANGED_FIELD” = ‘WADAT’) ELSE NULL END, ‘YYYYMMDD’)
)
ELSE
(
TO_TIMESTAMP(CASE WHEN STR_TO_INT(PU_FIRST(“VBAP”, “VBEP”.“WADAT”)) > 19700101 THEN PU_FIRST(“VBAP”, “VBEP”.“WADAT”) ELSE NULL END, ‘YYYYMMDD’)
)
END

The Problem is, that it gives me the planned goods issue date independent of the schedule line number. If there are two schedule lines for a customer order it takes the first planned goods issue date for the first schedule line, and the last planned goods issue date for the second schedule line.

I would like to add two conditions:

  1. Only schedule lines with a confirmed quantitiy should be taken into account
  2. The planned goods issue date should be read out for each schedule line with a confirmed quantity

Are there any suggestions how I can address this problem?

Best regards,

Friedemann

Plan_WA


#3

Dear FThiel,

Thanks for your question. We had the same problem. You will need to solve this in th HANA Studio and get the right table into it. I personally I am not a programmer but know “how to get to the data”.
I will give you some code lines so you can start. Let me know if this has worked for you.
SELECT
V_ORDERS._CASE_KEY AS “_CASE_KEY”
,CASE
WHEN VBFA_GI.VBTYP_N = ‘R’ THEN ‘Logistik: Erfasse Warenausgang’
WHEN VBFA_GI.VBTYP_N = ‘h’ THEN ‘Logistik: Storniere Warenausgang’
END AS “ACTIVITY_DE”
,CASE
WHEN VBFA_GI.VBTYP_N = ‘R’ THEN ‘Logistics: Record Goods Issue’
WHEN VBFA_GI.VBTYP_N = ‘h’ THEN ‘Logistics: Cancel Goods Issue’
END AS “ACTIVITY_EN”
,CAST(VBFA_GI.ERDAT AS DATE) || ’ ’ || CAST(VBFA_GI.ERZET AS TIME) AS “EVENTTIME”
,80 +
CASE
WHEN VBFA_GI.VBTYP_N = ‘R’ THEN 1
WHEN VBFA_GI.VBTYP_N = ‘h’ THEN 2
END AS “_SORTING”
, CASE
WHEN LIPS.ERNAM IS NOT NULL THEN LIPS.ERNAM
WHEN LIKP.ERNAM IS NOT NULL THEN LIKP.ERNAM
ELSE NULL
END AS “USER_NAME”
, CASE
WHEN LIPS.ERNAM IS NOT NULL THEN USR02_LIPS.USTYP
WHEN LIKP.ERNAM IS NOT NULL THEN USR02_LIKP.USTYP
ELSE NULL
END AS “USER_TYPE”
,NULL AS “CHANGED_TABLE”
,NULL AS “CHANGED_TABLE_TEXT_DE”
,NULL AS “CHANGED_TABLE_TEXT_EN”
,NULL AS “CHANGED_FIELD”
,NULL AS “CHANGED_FIELD_TEXT_DE”
,NULL AS “CHANGED_FIELD_TEXT_EN”
,NULL AS “CHANGED_FROM”
,NULL AS “CHANGED_TO”
,NULL AS “CHANGED_FROM_FLOAT”
,NULL AS “CHANGED_TO_FLOAT”
,NULL AS “CHANGE_NUMBER”
,NULL AS “TRANSACTION_CODE”
,V_ORDERS.MANDT AS “MANDT”
,V_ORDERS.VBELN AS “VBELN”
,V_ORDERS.POSNR AS “POSNR”
,1.0 AS “ACTIVITY_COUNT”
,NULL AS “EBELN”
,NULL AS “EBELP”
FROM
:TMP_O2C_VBAK_VBAP_VIEW AS V_ORDERS
JOIN SC_ED_SAP_PE1.VBFA AS VBFA_DEL ON
V_ORDERS.MANDT = VBFA_DEL.MANDT AND
V_ORDERS.VBELN = VBFA_DEL.VBELV AND
V_ORDERS.POSNR = VBFA_DEL.POSNV
JOIN SC_ED_SAP_PE1.LIPS AS LIPS ON
VBFA_DEL.MANDT = LIPS.MANDT AND
VBFA_DEL.VBELN = LIPS.VBELN AND
VBFA_DEL.POSNN = LIPS.POSNR
JOIN SC_ED_SAP_PE1.LIKP AS LIKP ON
LIPS.MANDT = LIKP.MANDT AND
LIPS.VBELN = LIKP.VBELN
JOIN SC_ED_SAP_PE1.VBFA AS VBFA_GI ON
LIPS.MANDT = VBFA_GI.MANDT AND
LIPS.VBELN = VBFA_GI.VBELV AND
LIPS.POSNR = VBFA_GI.POSNV
LEFT JOIN SC_ED_SAP_PE1.USR02 AS USR02_LIPS ON 1=1
AND LIPS.MANDT = USR02_LIPS.MANDT
AND LIPS.ERNAM = USR02_LIPS.BNAME
LEFT JOIN SC_ED_SAP_PE1.USR02 AS USR02_LIKP ON 1=1
AND LIKP.MANDT = USR02_LIKP.MANDT
AND LIKP.ERNAM = USR02_LIKP.BNAME

WHERE
–V_ORDERS.VBTYP = ‘C’ AND
VBFA_GI.VBTYP_N IN (‘R’, ‘h’);
END;

Have fun!

Hans