Source: DMF/PuC/AIF/BKP/Orders_TMP_tables.sql
--------------------------------------------
------------ REPLENISHMENT ------------
--------------------------------------------
-- IBC SUPPLIER ORDER TO REPLENISHMENT
DROP TABLE PUC_IBC_PO_REPL_TMP;
CREATE TABLE PUC_IBC_PO_REPL_TMP NOLOGGING PARALLEL 24 AS
WITH IBC_PO_REPL_HEADER AS (
SELECT /*+ PARALLEL(24) */
EKKO.BSART,
EKKO.EBELN
FROM SAP_SLT_PROD_PR1.EKKO EKKO
WHERE EKKO.BSART in ('ZGRP', 'ZGUM')
AND EKKO.BEDAT >= 20240101
AND EKKO.BEDAT <= 20260426),
IBC_PO_REPL_DETAIL AS (
SELECT /*+ PARALLEL(24) */
DISTINCT EKKO.*,
EKPO.WERKS
FROM IBC_PO_REPL_HEADER EKKO
JOIN (SELECT DISTINCT EKPO.EBELN, EKPO.WERKS, EKPO.UEBPO, EKPO.LOEKZ
FROM SAP_SLT_PROD_PR1.EKPO EKPO
WHERE EKPO.UEBPO = '00000'
AND EKPO.LOEKZ = ' ') EKPO on EKKO.EBELN = EKPO.EBELN )
SELECT /*+ PARALLEL(i 24) */
BSART AS IBC_PO_TYPE,
EBELN AS IBC_PO,
WERKS AS IBC_WH,
1575 AS IBC_VIRTUAL_WH
FROM IBC_PO_REPL_DETAIL i;
SELECT * FROM PUC_IBC_PO_REPL_TMP;
-- IBC REPLENISHMENT ORDERS (ALLOCATIONS) TO THE stores
DROP TABLE PUC_IBC_ALLOC_REPL_TMP;
CREATE TABLE PUC_IBC_ALLOC_REPL_TMP NOLOGGING PARALLEL 24 AS ;
WITH IBC_ALLOC1_REPL_HEADER AS (
SELECT /*+ PARALLEL(24) */
EKKO.BSART AS ALLOC_1_TYPE,
EKKO.EBELN AS ALLOC_1
FROM SAP_SLT_PROD_PV1.EKKO EKKO
WHERE EKKO.BSART in ('ZNNR', 'ZNFL')
AND EKKO.LIFNR = '0000940506'
AND EKKO.BEDAT >= 20240101
AND EKKO.BEDAT <= 20260426),
IBC_ALLOC1_REPL_DETAIL AS (
SELECT /*+ PARALLEL(24) */
DISTINCT EKKO.*,
EKPO.WERKS AS BUYING_WH
FROM IBC_ALLOC1_REPL_HEADER EKKO
JOIN (SELECT DISTINCT EKPO.EBELN, EKPO.WERKS, EKPO.UEBPO, EKPO.LOEKZ
FROM SAP_SLT_PROD_PV1.EKPO EKPO
WHERE EKPO.UEBPO = '00000'
AND EKPO.LOEKZ = ' ') EKPO on EKKO.ALLOC_1 = EKPO.EBELN ),
IBC_ALLOC2_REPL_HEADER AS (
SELECT /*+ PARALLEL(24) */
DISTINCT ALLOC1.*,
EKKO_LEG2.BSART AS ALLOC_2_TYPE,
EKKO_LEG2.EBELN AS ALLOC_2
FROM IBC_ALLOC1_REPL_DETAIL ALLOC1
LEFT JOIN (SELECT EKKO2.BSART, EKKO2.EBELN, EKKO2.ZZREFNB
FROM SAP_SLT_PROD_PV1.EKKO EKKO2) EKKO_LEG2 ON EKKO_LEG2.ZZREFNB = ALLOC1.ALLOC_1),
IBC_ALLOC2_REPL_DETAIL AS (
SELECT /*+ PARALLEL(24) */
DISTINCT ALLOC2_H.*,
EKPO_LEG2.WERKS AS STORE
FROM IBC_ALLOC2_REPL_HEADER ALLOC2_H
LEFT JOIN (SELECT DISTINCT EKPO2.EBELN, EKPO2.WERKS
FROM SAP_SLT_PROD_PV1.EKPO EKPO2
WHERE EKPO2.UEBPO = '00000'
AND EKPO2.LOEKZ = ' ') EKPO_LEG2 ON EKPO_LEG2.EBELN = ALLOC2_H.ALLOC_2),
IBC_ALLOC1_REPL_DETAIL_SALES AS (
SELECT /*+ PARALLEL(24) */
DISTINCT PO.*,
SALES_ODER.WERKS AS IBC_WH
FROM IBC_ALLOC2_REPL_DETAIL PO
LEFT JOIN (SELECT DISTINCT VBAK.BSTNK, VBAP.WERKS
FROM SAP_SLT_PROD_PR1.VBAK,
SAP_SLT_PROD_PR1.VBAP
WHERE VBAK.VBELN = VBAP.VBELN) SALES_ODER on SALES_ODER.BSTNK = PO.ALLOC_1)
SELECT
IBC_WH
,ALLOC_1_TYPE
,ALLOC_1
,BUYING_WH
,ALLOC_2_TYPE
,ALLOC_2
,STORE
FROM IBC_ALLOC1_REPL_DETAIL_SALES where ibc_wh is null;
SELECT * FROM PUC_IBC_ALLOC_REPL_TMP;
-----------------------------------------
-- IBC SUPPLIER ORDER XDOCKING TO THE STORES
DROP TABLE PUC_IBC_PO_XDOC_TMP;
CREATE TABLE PUC_IBC_PO_XDOC_TMP NOLOGGING PARALLEL 24 AS
WITH IBC_PO_XDOC_HEADER AS (
SELECT /*+ PARALLEL(24) */
EKKO.BSART AS IBC_PO_TYPE,
EKKO.EBELN AS IBC_PO
FROM SAP_SLT_PROD_PR1.EKKO EKKO
WHERE EKKO.BSART in ('ZGNB' , 'ZGVB' )
AND EKKO.BEDAT >= 20240101),
IBC_PO_XDOC_DETAIL AS (
SELECT /*+ PARALLEL(24) */
DISTINCT EKKO.*,
EKPO.WERKS AS IBC_WH
FROM IBC_PO_XDOC_HEADER EKKO
JOIN (SELECT DISTINCT EKPO.EBELN, EKPO.WERKS, EKPO.UEBPO, EKPO.LOEKZ
FROM SAP_SLT_PROD_PR1.EKPO EKPO
WHERE EKPO.UEBPO = '00000'
AND EKPO.LOEKZ = ' ') EKPO on EKKO.IBC_PO = EKPO.EBELN ),
IBC_PO_XDOC_SALES AS (
SELECT /*+ PARALLEL(24) */
DISTINCT PO.*,
SALES_ORDER.BSTNK AS SALES_ORDER
FROM IBC_PO_XDOC_DETAIL PO
LEFT JOIN (SELECT DISTINCT VBAK.BSTNK, FRET.BLNRB, FRET.WERKA
FROM SAP_SLT_PROD_PR1.FRET FRET,
SAP_SLT_PROD_PR1.VBAK VBAK
WHERE VBAK.VBELN = FRET.BLNRA) SALES_ORDER on SALES_ORDER.BLNRB = PO.IBC_PO AND IBC_WH = SALES_ORDER.WERKA)
SELECT /*+ PARALLEL(24) */
DISTINCT IBC_PO_TYPE,
IBC_PO ,
IBC_WH,
SALES_ORDER
FROM IBC_PO_XDOC_SALES;
select * from PUC_IBC_PO_XDOC_TMP;
select * from PUC_IBC_ALLOC_XDOC_TMP
-- IBC PURCHASE ORDERS X-DOCKING TO THE STORES
DROP TABLE PUC_IBC_ALLOC_XDOC_TMP;
CREATE TABLE PUC_IBC_ALLOC_XDOC_TMP NOLOGGING PARALLEL 24 AS
WITH IBC_PO_XDOC_SALES AS (
SELECT DISTINCT SALES_ORDER
FROM PUC_IBC_PO_XDOC_TMP),
IBC_ALLOC1_XDOC_HEADER AS (
SELECT EKKO.BSART AS ALLOC_1_TYPE,
EKKO.EBELN as ALLOC_1
FROM IBC_PO_XDOC_SALES SALES_ORDER
JOIN SAP_SLT_PROD_PV1.EKKO EKKO on EKKO.EBELN = SALES_ORDER.SALES_ORDER AND EKKO.LIFNR = '0000940506' AND EKKO.BEDAT >= 20240101),
IBC_ALLOC1_XDOC_DETAIL AS (
SELECT /*+ PARALLEL(24) */
DISTINCT EKKO.*,
EKPO.WERKS AS BUYING_WH
FROM IBC_ALLOC1_XDOC_HEADER EKKO
JOIN (SELECT DISTINCT EKPO.EBELN, EKPO.WERKS, EKPO.UEBPO, EKPO.LOEKZ
FROM SAP_SLT_PROD_PV1.EKPO EKPO
WHERE EKPO.UEBPO = '00000'
AND EKPO.LOEKZ = ' ') EKPO on EKKO.ALLOC_1 = EKPO.EBELN ),
IBC_ALLOC2_XDOC_HEADER AS (
SELECT /*+ PARALLEL(24) */
DISTINCT ALLOC1.*,
EKKO_LEG2.BSART AS ALLOC_2_TYPE,
EKKO_LEG2.EBELN AS ALLOC_2
FROM IBC_ALLOC1_XDOC_DETAIL ALLOC1
LEFT JOIN (SELECT EKKO2.BSART, EKKO2.EBELN, EKKO2.ZZREFNB
FROM sap_slt_prod_pv1.EKKO EKKO2) EKKO_LEG2 ON EKKO_LEG2.ZZREFNB = ALLOC1.ALLOC_1),
IBC_ALLOC2_XDOC_DETAIL AS (
SELECT /*+ PARALLEL(24) */
DISTINCT ALLOC2_H.*,
EKPO_LEG2.WERKS AS STORE
FROM IBC_ALLOC2_XDOC_HEADER ALLOC2_H
LEFT JOIN (SELECT DISTINCT EKPO2.EBELN, EKPO2.WERKS
FROM sap_slt_prod_pv1.EKPO EKPO2) EKPO_LEG2 ON EKPO_LEG2.EBELN = ALLOC2_H.ALLOC_2)
SELECT /*+ PARALLEL(24) */
ALLOC_1_TYPE
,ALLOC_1
,BUYING_WH
,ALLOC_2_TYPE
,ALLOC_2
,STORE
FROM IBC_ALLOC2_XDOC_DETAIL
;
----------------------------------------------------------
------------ EXCL PURCHASE ORDER + ALLOCTIONS ------------
----------------------------------------------------------
DROP TABLE PUC_EXCL_PO_ALLOC_XDOC_TMP;
CREATE TABLE PUC_EXCL_PO_ALLOC_XDOC_TMP NOLOGGING PARALLEL 24 AS ;
WITH EXCL_PO_XDOC_HEADER AS (
SELECT /*+ PARALLEL(24) */
EKKO.BSART AS EXCL_PO_TYPE,
EKKO.EBELN as EXCL_PO
FROM sap_slt_prod_pv1.EKKO EKKO
WHERE EKKO.BSART in ('ZNB', 'ZBPB', 'ZNOS') -- 3rd party Supplier Purchase Orders
AND EKKO.LIFNR <> '0000940506'
AND EKKO.BEDAT >= 20240101),
EXCL_PO_XDOC_DETAIL AS (
SELECT /*+ PARALLEL(24) */
DISTINCT EKKO.*,
EKPO.WERKS AS BUYING_WH
FROM EXCL_PO_XDOC_HEADER EKKO
JOIN (SELECT DISTINCT EKPO.EBELN, EKPO.WERKS, EKPO.UEBPO, EKPO.LOEKZ
FROM SAP_SLT_PROD_PV1.EKPO EKPO
WHERE EKPO.UEBPO = '00000'
AND EKPO.LOEKZ = ' ') EKPO on EKKO.EXCL_PO = EKPO.EBELN ),
EXCL_PO_XDOC_ALLOC_HEADER AS (
SELECT /*+ PARALLEL(24) */
DISTINCT PO.*,
ALLOC_1.BSART AS ALLOC_1_TYPE,
ALLOC_1.EBELN AS ALLOC_1
FROM EXCL_PO_XDOC_DETAIL PO
LEFT JOIN (SELECT EKKO2.BSART, EKKO2.EBELN, EKKO2.ZZREFNB
FROM sap_slt_prod_pv1.EKKO EKKO2) ALLOC_1 ON ALLOC_1.ZZREFNB = PO.EXCL_PO),
EXCL_PO_XDOC_ALLOC_DETAIL AS (
SELECT /*+ PARALLEL(24) */
DISTINCT ALLOC1_H.*,
EKPO_ALLOC_1.WERKS AS STORE
FROM EXCL_PO_XDOC_ALLOC_HEADER ALLOC1_H
LEFT JOIN (SELECT DISTINCT EKPO2.EBELN, EKPO2.WERKS
FROM SAP_SLT_PROD_PV1.EKPO EKPO2
WHERE EKPO2.UEBPO = '00000'
AND EKPO2.LOEKZ = ' ') EKPO_ALLOC_1 ON EKPO_ALLOC_1.EBELN = ALLOC1_H.ALLOC_1)
SELECT /*+ PARALLEL(24) */
EXCL_PO_TYPE,
EXCL_PO,
BUYING_WH,
ALLOC_1_TYPE,
ALLOC_1,
STORE
FROM EXCL_PO_XDOC_ALLOC_DETAIL
;
-----------------------------------
------------ TRANSFERS ------------
-----------------------------------
DROP TABLE PUC_TSF_TMP;
CREATE TABLE PUC_TSF_TMP NOLOGGING PARALLEL 24 AS
-- store to store transfers / online
SELECT /*+ PARALLEL(24) */
DISTINCT EKKO.BSART AS TSF_TYPE,
EKKO.EBELN AS TSF,
CASE
WHEN EKKO.BSART in ('ZBAB','ZWBU','ZWAW','ZWRK','ZWVR','ZWND','ZWOW','ZWVS','ZSBU','ZWVW','ZWVD','ZWSD') THEN
EKPO.WERKS
ELSE
EKKO.RESWK
END AS FROM_LOC,
CASE
WHEN EKKO.BSART in ('ZBAB','ZWBU','ZWAW','ZWRK','ZWVR','ZWND','ZWOW','ZWVS','ZSBU','ZWVW','ZWVD','ZWSD') THEN
EKKO.RESWK
ELSE
EKPO.WERKS
END AS TO_LOC
FROM sap_slt_prod_pv1.EKKO EKKO
JOIN sap_slt_prod_pv1.EKPO EKPO on EKKO.EBELN = EKPO.EBELN AND EKPO.UEBPO = '00000' and EKPO.LOEKZ = ' '
WHERE 1 = 1
AND EKKO.BSART in ('ZWFA','ZWFO','ZWFR','ZWFS','ZWFW', -- st --> st
'ZBAB','ZWBU','ZWAW','ZWRK','ZWVR','ZWND','ZWOW','ZWVS','ZSBU','ZWVW','ZWVD','ZWSD', -- st --> buying wh (including online reverse logistics)
'ZWV2','ZWBB','ZWR7','ZWV6','ZWV7', -- buying wh --> buying wh (including online whs '0126','0671')
'ZWV4','ZBU4','ZWR9','ZWV8','ZWV9' -- buying wh --> st (including online store DHL '0546')
)
AND EKKO.BEDAT >= 20240101
union all
-- ibc and 3rd party reverse logistics
SELECT /*+ PARALLEL(24) */
DISTINCT EKKO.BSART AS TSF_TYPE,
EKKO.EBELN AS TSF,
CASE
WHEN EKKO.BSART in ('ZEGR','ZESL','ZEGK','ZESK','ZLK') THEN
EKPO.WERKS
ELSE
EKKO.RESWK
END AS FROM_LOC,
CASE
WHEN EKKO.BSART in ('ZEGR','ZESL','ZEGK','ZESK','ZLK') THEN
EKKO.RESWK --decode(EKKO.RESWK,' ','0157',EKKO.RESWK) -- fixed 0157 ibs wh
ELSE
EKPO.WERKS
END AS TO_LOC
FROM sap_slt_prod_pv1.EKKO EKKO
JOIN sap_slt_prod_pv1.EKPO EKPO on EKKO.EBELN = EKPO.EBELN AND EKPO.UEBPO = '00000' and EKPO.LOEKZ = ' '
WHERE 1 = 1
AND (EKKO.BSART in ('ZEGR','ZESL','ZEGK','ZESK') -- st --> wh (ibc and 3rd party)
OR
(EKKO.BSART in ('ZLK') AND LIFNR = '0000940506')) -- buying wh to ibc wh
AND EKKO.BEDAT >= 20240101;
select ZZREFNB, ekko.* from sap_slt_prod_pv1.EKKO EKKO where ZZREFNB = '3600687733';
SELECT * FROM PUC_TSF_TMP WHERE TSF_TYPE = 'ZLK';
SELECT /*+ PARALLEL(24) */
DISTINCT EKKO.BSART AS TSF_TYPE,
EKKO.EBELN AS TSF,
EKPO.WERKS,
EKKO.RESWK
FROM sap_slt_prod_pv1.EKKO EKKO
JOIN sap_slt_prod_pv1.EKPO EKPO on EKKO.EBELN = EKPO.EBELN AND EKPO.UEBPO = '00000' and EKPO.LOEKZ = ' '
WHERE BSART = 'ZESK';
-------------------------------- RECEIPTS FORMAT --------------------------------
-- IBC Purchase Order
SELECT
--EKPO.EBELN,
EKPO.MATNR AS ITEM,
--ekpo.ebelp,
EKPO.werks AS ORG_NUM,
--EKPO.ELIKZ,
--ekpo.loekz,
EKET.EINDT AS DAY_DT,
'20' AS INVRC_TYPE_CODE,
EKET.WEMNG AS INVRC_QTY,
EKPO.NETPR AS INVRC_COST_AMT_LCL,
'EUR' AS LOC_CURR_CODE,
EKKO.WAERS AS DOC_CURR_CODE,
--EKPO.MENGE,
1 AS DATASOURCE_NUM_ID ,
EKPO.EBELP
FROM
sap_slt_prod_pr1.EKKO EKKO,
sap_slt_prod_pr1.EKPO EKPO,
sap_slt_prod_pr1.EKET EKET
where 1 = 1
AND EKKO.BSART in ('ZGRP', 'ZGNB' , 'ZGVB' , 'ZGUM')
AND EKKO.EBELN = EKPO.EBELN
AND EKPO.UEBPO = '00000'
and EKET.EBELN = EKPO.EBELN
AND EKET.EBELP = EKPO.EBELP
--AND EKKO.BEDAT >= 20240101 -- po date
--AND EKET.EINDT >= 20240101 -- receiving date
AND EKET.WEMNG > 0
AND EKKO.EBELN = '20260212'
--AND EKPO.MATNR = '000056850920700001'
;
-------------------------------- VALIDATIONS --------------------------------
select ibc_po from PUC_IBC_PO_REPL_TMP group by ibc_po having count(1) > 1;
select ekko.lifnr, elikz, EKKO.bedat, EKPO.*
from sap_slt_prod_pr1.EKPO EKPO,
sap_slt_prod_pr1.EKKO EKKO
where EKKO.EBELN = EKPO.EBELN
and EKPO.UEBPO = '00000'
and EKKO.bedat > 20260101
and EKPO.ebeln in (select ibc_po from PUC_IBC_PO_REPL_TMP group by ibc_po having count(1) > 1)
--and elikz = 'X'
;
select * from sap_slt_prod_pr1.EKPO where ebeln = 2438251167;
select * from PUC_IBC_PO_XDOC_TMP;
select ibc_po from (select distinct ibc_po, ibc_wh from PUC_IBC_PO_XDOC_TMP) group by ibc_po having count(1) > 1;
select e.* from sap_slt_prod_pr1.EKKO e where ebeln = 4502135936;
--select * from sap_slt_prod_pr1.lfa1 where lifnr in (
select elikz, ekko.lifnr, EKKO.bedat, EKPO.*
from sap_slt_prod_pr1.EKPO EKPO,
sap_slt_prod_pr1.EKKO EKKO
where EKKO.EBELN = EKPO.EBELN
and EKPO.UEBPO = '00000'
and EKKO.bedat > 20260101
and EKPO.ebeln in (select ibc_po from (select distinct ibc_po, ibc_wh from PUC_IBC_PO_XDOC_TMP) group by ibc_po having count(1) > 1)
and lifnr = '0000235517'
--and elikz = 'X'
and ekpo.ebeln = '4502146854'
--)
;
select * from PUC_IBC_PO_XDOC_TMP where ibc_po = 4502146854;
select elikz, ekko.lifnr, EKKO.bedat, EKPO.*
from sap_slt_prod_pv1.EKPO EKPO,
sap_slt_prod_pv1.EKKO EKKO
where EKKO.EBELN = EKPO.EBELN
and EKPO.UEBPO = '00000'
and EKKO.bedat > 20260101
--and lifnr = '0000235517'
--and elikz = 'X'
and ekpo.ebeln in (select sales_order from PUC_IBC_PO_XDOC_TMP where ibc_po = 4502146854 and ibc_wh = '0317')
--)
;
select * from sap_slt_prod_pr1.lfa1 where lifnr = '0000219525';
-- IBC POs
select * from PUC_IBC_PO_REPL_TMP;
select * from PUC_IBC_PO_XDOC_TMP where sales_order = 4825693965;
-- IBC to Buying WH to Stores
select * from PUC_IBC_ALLOC_REPL_TMP;
select * from PUC_IBC_ALLOC_XDOC_TMP where alloc_1 = 4825693965;
-- Buying POs
select * from PUC_EXCL_PO_ALLOC_XDOC_TMP;
-- Transfers
select * from PUC_TSF_TMP;
select count(1) from PUC_IBC_PO_REPL_TMP;
select count(1) from PUC_IBC_PO_XDOC_TMP;
select count(1) from PUC_IBC_ALLOC_REPL_TMP;
select count(1) from PUC_IBC_ALLOC_XDOC_TMP ;
select count(1) from PUC_EXCL_PO_ALLOC_XDOC_TMP;
select count(1) from PUC_TSF_TMP;
SELECT * --COUNT(1)
FROM
(SELECT 'LEG1', ITEM, SUM(INVRC_QTY) QTY, ORDER_ID FROM ORI_RECEIPTS_RAW WHERE ORDER_TYPE = 'IBC_ALLOC_XDOC_LEG1' GROUP BY ITEM, ORDER_ID) LEG1,
(SELECT 'LEG2', ITEM, SUM(INVRC_QTY) QTY, ORDER_ID FROM ORI_RECEIPTS_RAW WHERE ORDER_TYPE = 'IBC_ALLOC_XDOC_LEG2' GROUP BY ITEM, ORDER_ID) LEG2,
PUC_IBC_ALLOC_XDOC_TMP T
WHERE T.ALLOC_1 = LEG1.ORDER_ID
AND T.ALLOC_2 = LEG2.ORDER_ID
AND LEG1.ITEM = LEG2.ITEM
AND LEG1.QTY <> LEG2.QTY ;
---------------