Source: DMF/PuC/AIF/Orders_TMP_tables_v2.sql
--SELECT * FROM SAP_SLT_PROD_PV1.ZBES_POM_EH_UEF
---- IBC XDOCKING PROCESS - FROM PO IBC --> BUYING WH --> STORE
DROP TABLE PUC_IBC_PO_XDOC_STORE_TMP;
create table PUC_IBC_PO_XDOC_STORE_TMP as
WITH IBC_ALLOC1_XDOC_HEADER AS (
SELECT EKKO.BSART AS ALLOC_1_TYPE,
EKKO.EBELN as ALLOC_1
FROM SAP_SLT_PROD_PV1.EKKO EKKO
WHERE EKKO.LIFNR = '0000940506'
AND EKKO.BEDAT >= 20240101
AND EKKO.BEDAT <= 20260607
AND EKKO.BSART NOT in ('ZNNR', 'ZNFL','ZUF1','ZUFB','ZLK')),
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,
store.oracle_id AS ORACLE_STORE,
store.CHANNEL_ID
FROM IBC_ALLOC2_XDOC_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
LEFT JOIN XREF_LOCATION store on store.sap_id = ekpo_leg2.werks),
IBC_XDOC_SALES_ORDER AS (
SELECT /*+ PARALLEL(24) */
DISTINCT ALLOCS.*,
SALES_ORDER.BLNRB as IBC_PO,
SALES_ORDER.BSART AS IBC_PO_TYPE,
SALES_ORDER.WERKA as IBC_WH
FROM IBC_ALLOC2_XDOC_DETAIL ALLOCS
LEFT JOIN (SELECT DISTINCT VBAK.BSTNK, FRET.BLNRB, FRET.WERKA, EKKO.BSART
FROM SAP_SLT_PROD_PR1.FRET FRET,
SAP_SLT_PROD_PR1.VBAK VBAK,
SAP_SLT_PROD_PR1.EKKO EKKO
WHERE VBAK.VBELN = FRET.BLNRA
--AND EKKO.BSART in ('ZGNB' , 'ZGVB',)
AND FRET.BLNRB = EKKO.EBELN
) SALES_ORDER on SALES_ORDER.BSTNK = ALLOCS.ALLOC_1)
SELECT /*+ PARALLEL(24) */
--count(1)
--distinct ALLOC_1_TYPE
IBC_PO
,IBC_PO_TYPE
,nvl(IBC_WH,'0157') as IBC_WH
,nvl(x_ibc.oracle_id_by_channel,'1575') as IBC_ORACLE_WH
,ALLOC_1
,ALLOC_1_TYPE
,BUYING_WH
,x_buying.oracle_id_by_channel as BUYING_ORACLE_WH
,ALLOC_2
,ALLOC_2_TYPE
,STORE
,ORACLE_STORE
,s.CHANNEL_ID
FROM IBC_XDOC_SALES_ORDER s
left join xref_location x_buying on x_buying.sap_id = s.buying_wh and x_buying.channel_id = s.channel_id
left join xref_location x_ibc on x_ibc.sap_id = s.ibc_wh and x_ibc.channel_id = s.channel_id
;
select ibc_po_type, ALLOC_1_TYPE, count(1) from PUC_IBC_PO_XDOC_STORE_TMP where ibc_po_type = 'ZGOB' group by ibc_po_type, ALLOC_1_TYPE;-- where ALLOC_2 IS NOT NULL AND STORE IS NOT NULL AND BUYING_ORACLE_WH IS NULL;
select ALLOC_1_TYPE, count(1) from PUC_IBC_PO_XDOC_STORE_TMP group by ALLOC_1_TYPE;
select ALLOC_2_TYPE, count(1) from PUC_IBC_PO_XDOC_STORE_TMP group by ALLOC_2_TYPE;
select * from PUC_IBC_PO_XDOC_STORE_TMP where ALLOC_1_TYPE in ('ZUEF') and alloc_2 is not null;
SELECT * FROM SAP_SLT_PROD_PV1.ZBES_POM_EH_UEF where wvbnr = '4828051770';
SELECT * FROM PUC_IBC_PO_XDOC_STORE_TMP where ALLOC_1 = 4821305751;
select * from PUC_IBC_PO_XDOC_STORE_TMP where ibc_po_type = 'ZWVB';-- where ALLOC_2 IS NOT NULL AND STORE IS NOT NULL AND BUYING_ORACLE_WH IS NULL;
select * from PUC_IBC_PO_XDOC_STORE_TMP where ALLOC_2_TYPE in ('ZWVN');
SELECT /*+ PARALLEL(8) */
EKPO.LOEKZ, EKPO.ELIKZ, EKET.WEMNG, EKPO.*
FROM SAP_SLT_PROD_PV1.EKKO EKKO
JOIN SAP_SLT_PROD_PV1.EKPO EKPO ON EKPO.EBELN = EKKO.EBELN AND EKPO.UEBPO = '00000'
LEFT JOIN SAP_SLT_PROD_PV1.EKET EKET on EKET.EBELN = EKPO.EBELN AND EKET.EBELP = EKPO.EBELP and EKET.WEMNG > 0
WHERE 1=1 -- ekko.ebeln = '4301718735'
and BSART = 'ZWVN' and EKKO.BEDAT >= 20240101 and ekpo.LOEKZ = ' ';
;
SELECT *
FROM SAP_SLT_PROD_PV1.EKKO EKKO where BSART = 'ZWVN' and EKKO.BEDAT >= 20240101;
-- ZNB ZBPB - historical
-- lookup if the EKET.WEMNG > 0
--------------------------------------------
------------ REPLENISHMENT ------------
--------------------------------------------
-- IBC SUPPLIER ORDER TO REPLENISHMENT: SUPPLIER --> IBC WH
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_ORACLE_WH
FROM IBC_PO_REPL_DETAIL i;
select ibc_po, count(1) from (
select ibc_po, ibc_wh from PUC_IBC_PO_REPL_TMP group by ibc_po, ibc_wh) group by ibc_po having count(1) > 1 ;
select * from PUC_IBC_PO_REPL_TMP group by ibc_po, ibc_wh) group by ibc_po having count(1) > 1 ;
------------------
---- IBC REPLENISHMENT PROCESS - IBC WH --> BUYING WH --> STORE
DROP TABLE PUC_IBC_REPL_STORE_TMP;
create table PUC_IBC_REPL_STORE_TMP as
WITH IBC_ALLOC1_REPL_HEADER AS (
SELECT EKKO.BSART AS ALLOC_1_TYPE,
EKKO.EBELN as ALLOC_1
FROM SAP_SLT_PROD_PV1.EKKO EKKO
WHERE EKKO.LIFNR = '0000940506'
AND EKKO.BEDAT >= 20240101
AND EKKO.BEDAT <= 20260426
AND EKKO.BSART in ('ZNNR', 'ZNFL','ZUF1','ZUFB')),
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,
store.oracle_id AS ORACLE_STORE,
store.CHANNEL_ID
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
LEFT JOIN XREF_LOCATION store on store.sap_id = ekpo_leg2.werks),
IBC_REPL_SALES_ORDER AS (
SELECT /*+ PARALLEL(24) */
DISTINCT ALLOCS.*,
SALES_ORDER.WERKS as IBC_WH
FROM IBC_ALLOC2_REPL_DETAIL ALLOCS
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_ORDER on SALES_ORDER.BSTNK = ALLOCS.ALLOC_1)
SELECT /*+ PARALLEL(24) */
IBC_WH
,1575 as IBC_ORACLE_WH
--,x_ibc.oracle_id_by_channel as IBC_VIRTUAL_WH
,ALLOC_1
,ALLOC_1_TYPE
,BUYING_WH
,x_buying.oracle_id_by_channel as BUYING_ORACLE_WH
,ALLOC_2
,ALLOC_2_TYPE
,STORE
,ORACLE_STORE
,s.CHANNEL_ID
FROM IBC_REPL_SALES_ORDER s
left join xref_location x_buying on x_buying.sap_id = s.buying_wh and x_buying.channel_id = s.channel_id
left join xref_location x_ibc on x_ibc.sap_id = s.ibc_wh and x_ibc.channel_id = s.channel_id
;
select * from PUC_IBC_REPL_STORE_TMP WHERE ibc_wh is null;
select alloc_1_type, count(1) from PUC_IBC_REPL_STORE_TMP group by alloc_1_type ;
select alloc_2_type, count(1) from PUC_IBC_REPL_STORE_TMP group by alloc_2_type ;
select * from PUC_IBC_REPL_STORE_TMP where alloc_1_type = 'ZUFB';
-------------------------------------------------------------------
--------------- EXCLUSIVE BRAND PURCHASE ORDER + ALLOCTIONS ------------
-------------------------------------------------------------------
DROP TABLE PUC_TRD_PARTY_PO_XDOC_STORE_TMP;
CREATE TABLE PUC_TRD_PARTY_PO_XDOC_STORE_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.LIFNR <> '0000940506' -- 3rd party Supplier Purchase Orders
AND EKKO.BEDAT >= 20240101
AND EKKO.BEDAT <= 20260426
AND EKKO.BSART in ('ZNB', 'ZBPB', 'ZNOS')),
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,
store.oracle_id AS ORACLE_STORE,
store.CHANNEL_ID
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
LEFT JOIN XREF_LOCATION store on store.sap_id = EKPO_ALLOC_1.werks)
SELECT /*+ PARALLEL(24) */
EXCL_PO_TYPE,
EXCL_PO,
BUYING_WH,
x_buying.oracle_id_by_channel as BUYING_ORACLE_WH,
ALLOC_1_TYPE,
ALLOC_1,
STORE,
ORACLE_STORE,
s.CHANNEL_ID
FROM EXCL_PO_XDOC_ALLOC_DETAIL s
left join xref_location x_buying on x_buying.sap_id = s.buying_wh and x_buying.channel_id = s.channel_id
;
select * from PUC_TRD_PARTY_PO_XDOC_STORE_TMP WHERE ALLOC_1 IS NOT NULL AND BUYING_ORACLE_WH IS NULL;
select excl_po_type, count(1) from PUC_TRD_PARTY_PO_XDOC_STORE_TMP group by excl_po_type;
select ALLOC_1_TYPE, count(1) from PUC_TRD_PARTY_PO_XDOC_STORE_TMP group by ALLOC_1_TYPE;
select * from PUC_TRD_PARTY_PO_XDOC_STORE_TMP WHERE ALLOC_1_TYPE = 'ZRUF';
select * from DMF2.PUC_TRD_PARTY_PO_XDOC_STORE_DELTA;
select * from DMF2.queue_alloc_delta;
----------------
DROP TABLE PUC_TSF_LEGS_TMP;
CREATE TABLE PUC_TSF_LEGS_TMP NOLOGGING PARALLEL 24 AS
select * from (
-- store to store transfers / online
WITH
TSF_ST_TO_ST AS (
SELECT /*+ PARALLEL(24) */
DISTINCT EKKO.BSART AS TSF_TYPE_LEG1,
EKKO.EBELN AS TSF_LEG1,
EKKO.RESWK AS FROM_LOC_LEG1,
xFrom.oracle_id_by_channel as FROM_LOC_ORACLE_LEG1,
xFrom.oracle_type as FROM_LOC_TYPE_LEG1,
xFrom.channel_id as FROM_LOC_CHANNEL_LEG1,
EKPO.WERKS AS TO_LOC_LEG1,
xTo.oracle_id_by_channel as TO_LOC_ORACLE_LEG1,
xTo.oracle_type as TO_LOC_TYPE_LEG1,
xTo.channel_id as TO_LOC_CHANNEL_LEG1
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 = ' '
LEFT JOIN XREF_LOCATION xFrom on xFrom.sap_id = EKKO.RESWK
LEFT JOIN XREF_LOCATION xTo on xTo.sap_id = EKPO.WERKS
WHERE 1 = 1
AND EKKO.BSART in ('ZWFA','ZWFO','ZWFR','ZWFS','ZWFW') -- st --> st
AND EKKO.BEDAT >= 20240101
AND EKKO.BEDAT <= 20260607),
TSF_WH_TO_ST AS (
SELECT /*+ PARALLEL(24) */
DISTINCT EKKO.BSART AS TSF_TYPE_LEG3,
EKKO.EBELN AS TSF_LEG3,
EKKO.RESWK AS FROM_LOC_LEG3,
xFrom.oracle_id_by_channel as FROM_LOC_ORACLE_LEG3,
xFrom.oracle_type as FROM_LOC_TYPE_LEG3,
xFrom.channel_id as FROM_LOC_CHANNEL_LEG3,
EKPO.WERKS AS TO_LOC_LEG3,
xTo.oracle_id_by_channel as TO_LOC_ORACLE_LEG3,
xTo.oracle_type as TO_LOC_TYPE_LEG3,
xTo.channel_id as TO_LOC_CHANNEL_LEG3
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 = ' '
LEFT JOIN XREF_LOCATION xTo on xTo.sap_id = EKPO.WERKS
LEFT JOIN XREF_LOCATION xFrom on xFrom.sap_id = EKKO.RESWK AND xFrom.channel_id = DECODE(xTo.channel_id,7,1,8,2,9,3,xTo.channel_id) --> temp fix for missing virtual warehouses
WHERE 1 = 1
AND EKKO.BSART in ('ZWV4','ZBU4','ZWR9','ZWV8','ZWV9') -- buying wh --> st (including online store DHL '0546')
AND EKKO.BEDAT >= 20240101
AND EKKO.BEDAT <= 20260607),
TSF_ST_TO_WH AS (
SELECT /*+ PARALLEL(24) */
DISTINCT TWS.*,
TWW.BSART AS TSF_TYPE_LEG1,
TWW.EBELN AS TSF_LEG1,
TWW.WERKS AS FROM_LOC_LEG1,
xFrom.oracle_id_by_channel as FROM_LOC_ORACLE_LEG1,
xFrom.oracle_type as FROM_LOC_TYPE_LEG1,
xFrom.channel_id as FROM_LOC_CHANNEL_LEG1,
TWW.RESWK AS TO_LOC_LEG1,
xTo.oracle_id_by_channel as TO_LOC_ORACLE_LEG1,
xTo.oracle_type as TO_LOC_TYPE_LEG1,
xTo.channel_id as TO_LOC_CHANNEL_LEG1
FROM TSF_WH_TO_ST TWS
LEFT JOIN (SELECT DISTINCT EKKO.BSART, EKKO.EBELN, EKKO.RESWK, EKPO.WERKS, EKKO.ZZREFNB
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 ('ZBAB','ZWBU','ZWAW','ZWRK','ZWVR','ZWND','ZWOW','ZWVS','ZSBU','ZWVW','ZWVD','ZWSD') -- st --> buying wh (including online reverse logistics)
) TWW ON TWW.ZZREFNB = TWS.TSF_LEG3
LEFT JOIN XREF_LOCATION xFrom on xFrom.sap_id = TWW.WERKS
LEFT JOIN XREF_LOCATION xTo on xTo.sap_id = TWW.RESWK AND xTo.channel_id = DECODE(xFrom.channel_id,7,1,8,2,9,3,xFrom.channel_id)), --> temp fix for missing virtual warehouses ),
TSF_WH_TO_WH AS (
SELECT /*+ PARALLEL(24) */
DISTINCT TWS.*,
TWW.BSART AS TSF_TYPE_LEG2,
TWW.EBELN AS TSF_LEG2,
TWW.RESWK AS FROM_LOC_LEG2,
xFrom.oracle_id_by_channel as FROM_LOC_ORACLE_LEG2,
xFrom.oracle_type as FROM_LOC_TYPE_LEG2,
xFrom.channel_id as FROM_LOC_CHANNEL_LEG2,
TWW.WERKS AS TO_LOC_LEG2,
xTo.oracle_id_by_channel as TO_LOC_ORACLE_LEG2,
xTo.oracle_type as TO_LOC_TYPE_LEG2,
xTo.channel_id as TO_LOC_CHANNEL_LEG2
FROM TSF_ST_TO_WH TWS
LEFT JOIN (SELECT DISTINCT EKKO.BSART, EKKO.EBELN, EKKO.RESWK, EKPO.WERKS, EKKO.ZZREFNB
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 ('ZWV2','ZWBB','ZWR7','ZWV6','ZWV7') -- buying wh --> buying wh (including online whs '0126','0671')
) TWW ON TWW.ZZREFNB = TWS.TSF_LEG3
LEFT JOIN XREF_LOCATION xFrom on xFrom.sap_id = TWW.RESWK AND xFrom.channel_id = DECODE(nvl(FROM_LOC_CHANNEL_LEG1,FROM_LOC_CHANNEL_LEG3),7,1,8,2,9,3,nvl(FROM_LOC_CHANNEL_LEG1,FROM_LOC_CHANNEL_LEG3))
LEFT JOIN XREF_LOCATION xTo on xTo.sap_id = TWW.WERKS AND xTo.channel_id = DECODE(FROM_LOC_CHANNEL_LEG3,7,1,8,2,9,3,FROM_LOC_CHANNEL_LEG3)), --> temp fix for missing virtual warehouses ),
-- ibc and 3rd party reverse logistics
RTV_ST_WH AS (
SELECT /*+ PARALLEL(24) */
DISTINCT EKKO.BSART AS TSF_TYPE_LEG1,
EKKO.EBELN AS TSF_LEG1,
EKPO.WERKS AS FROM_LOC_LEG1,
xFrom.oracle_id_by_channel as FROM_LOC_ORACLE_LEG1,
xFrom.oracle_type as FROM_LOC_TYPE_LEG1,
xFrom.channel_id as FROM_LOC_CHANNEL_LEG1,
EKKO.RESWK AS TO_LOC_LEG1,
xTo.oracle_id_by_channel as TO_LOC_ORACLE_LEG1,
xTo.oracle_type as TO_LOC_TYPE_LEG1,
xTo.channel_id as TO_LOC_CHANNEL_LEG1
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 = ' '
LEFT JOIN XREF_LOCATION xFrom on xFrom.sap_id = EKPO.WERKS
LEFT JOIN XREF_LOCATION xTo on xTo.sap_id = EKKO.RESWK AND xTo.channel_id = xFrom.channel_id
WHERE 1 = 1
AND EKKO.BSART in ('ZEGR','ZESL','ZEGK','ZESK','ZWAU') -- st --> buying wh (including online reverse logistics) and ZWAU Aufkäufer
AND EKKO.BEDAT >= 20240101
AND EKKO.BEDAT <= 20260607)/*,
RTV_WH_WH AS (
SELECT /*+ PARALLEL(24) */
/* DISTINCT EKKO.BSART AS TSF_TYPE_LEG1,
EKKO.EBELN AS TSF_LEG1,
EKPO.WERKS AS FROM_LOC_LEG1,
xFrom.oracle_id_by_channel as FROM_LOC_ORACLE_LEG1,
xFrom.oracle_type as FROM_LOC_TYPE_LEG1,
xFrom.channel_id as FROM_LOC_CHANNEL_LEG1,
'0157' AS TO_LOC_LEG1,
xTo.oracle_id_by_channel as TO_LOC_ORACLE_LEG1,
xTo.oracle_type as TO_LOC_TYPE_LEG1,
xTo.channel_id as TO_LOC_CHANNEL_LEG1
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 = ' '
LEFT JOIN (select *
from (
select sap_id, oracle_type, channel_id, oracle_id_by_channel,
RANK() OVER (
PARTITION BY sap_id
ORDER BY channel_id) AS r
from xref_location)
where r = 1) xFrom on xFrom.sap_id = EKPO.WERKS --> temp fix not channel to map the from loc
LEFT JOIN XREF_LOCATION xTo on xTo.sap_id = '0157' AND xTo.channel_id = xFrom.channel_id
WHERE 1 = 1
AND EKKO.BSART in ('ZLK')
AND LIFNR = '0000940506' -- buying wh to ibc wh
AND EKKO.BEDAT >= 20240101
AND EKKO.BEDAT <= 20260607)*/
SELECT 'ST_ST' AS TSF_TYPE,
TSF_TYPE_LEG1,
TSF_LEG1,
FROM_LOC_LEG1,
FROM_LOC_ORACLE_LEG1,
FROM_LOC_TYPE_LEG1,
FROM_LOC_CHANNEL_LEG1,
TO_LOC_LEG1,
TO_LOC_ORACLE_LEG1,
TO_LOC_TYPE_LEG1,
TO_LOC_CHANNEL_LEG1,
NULL AS TSF_TYPE_LEG2,
NULL AS TSF_LEG2,
NULL AS FROM_LOC_LEG2,
NULL AS FROM_LOC_ORACLE_LEG2,
NULL AS FROM_LOC_TYPE_LEG2,
NULL AS FROM_LOC_CHANNEL_LEG2,
NULL AS TO_LOC_LEG2,
NULL AS TO_LOC_ORACLE_LEG2,
NULL AS TO_LOC_TYPE_LEG2,
NULL AS TO_LOC_CHANNEL_LEG2,
NULL AS TSF_TYPE_LEG3,
NULL AS TSF_LEG3,
NULL AS FROM_LOC_LEG3,
NULL AS FROM_LOC_ORACLE_LEG3,
NULL AS FROM_LOC_TYPE_LEG3,
NULL AS FROM_LOC_CHANNEL_LEG3,
NULL AS TO_LOC_LEG3,
NULL AS TO_LOC_ORACLE_LEG3,
NULL AS TO_LOC_TYPE_LEG3,
NULL AS TO_LOC_CHANNEL_LEG3
FROM TSF_ST_TO_ST
UNION ALL
SELECT 'ST_WH_ST' AS TSF_TYPE,
TSF_TYPE_LEG1,
TSF_LEG1,
FROM_LOC_LEG1,
FROM_LOC_ORACLE_LEG1,
FROM_LOC_TYPE_LEG1,
FROM_LOC_CHANNEL_LEG1,
TO_LOC_LEG1,
TO_LOC_ORACLE_LEG1,
TO_LOC_TYPE_LEG1,
TO_LOC_CHANNEL_LEG1,
TSF_TYPE_LEG2,
TSF_LEG2,
FROM_LOC_LEG2,
FROM_LOC_ORACLE_LEG2,
FROM_LOC_TYPE_LEG2,
FROM_LOC_CHANNEL_LEG2,
TO_LOC_LEG2,
TO_LOC_ORACLE_LEG2,
TO_LOC_TYPE_LEG2,
TO_LOC_CHANNEL_LEG2,
TSF_TYPE_LEG3,
TSF_LEG3,
FROM_LOC_LEG3,
FROM_LOC_ORACLE_LEG3,
FROM_LOC_TYPE_LEG3,
FROM_LOC_CHANNEL_LEG3,
TO_LOC_LEG3,
TO_LOC_ORACLE_LEG3,
TO_LOC_TYPE_LEG3,
TO_LOC_CHANNEL_LEG3
FROM TSF_WH_TO_WH --where TSF_LEG2 is null and TO_LOC_ORACLE_LEG1 <> FROM_LOC_ORACLE_LEG3 and TSF_LEG1 is not null --and TSF_LEG1 is null AND FROM_LOC_ORACLE_LEG2 IS NULL
UNION ALL
SELECT 'RTV_ST_WH' AS TSF_TYPE,
TSF_TYPE_LEG1,
TSF_LEG1,
FROM_LOC_LEG1,
FROM_LOC_ORACLE_LEG1,
FROM_LOC_TYPE_LEG1,
FROM_LOC_CHANNEL_LEG1,
TO_LOC_LEG1,
TO_LOC_ORACLE_LEG1,
TO_LOC_TYPE_LEG1,
TO_LOC_CHANNEL_LEG1,
NULL AS TSF_TYPE_LEG2,
NULL AS TSF_LEG2,
NULL AS FROM_LOC_LEG2,
NULL AS FROM_LOC_ORACLE_LEG2,
NULL AS FROM_LOC_TYPE_LEG2,
NULL AS FROM_LOC_CHANNEL_LEG2,
NULL AS TO_LOC_LEG2,
NULL AS TO_LOC_ORACLE_LEG2,
NULL AS TO_LOC_TYPE_LEG2,
NULL AS TO_LOC_CHANNEL_LEG2,
NULL AS TSF_TYPE_LEG3,
NULL AS TSF_LEG3,
NULL AS FROM_LOC_LEG3,
NULL AS FROM_LOC_ORACLE_LEG3,
NULL AS FROM_LOC_TYPE_LEG3,
NULL AS FROM_LOC_CHANNEL_LEG3,
NULL AS TO_LOC_LEG3,
NULL AS TO_LOC_ORACLE_LEG3,
NULL AS TO_LOC_TYPE_LEG3,
NULL AS TO_LOC_CHANNEL_LEG3
FROM RTV_ST_WH
/*UNION ALL
SELECT 'RTV_WH_WH' AS TSF_TYPE,
TSF_TYPE_LEG1,
TSF_LEG1,
FROM_LOC_LEG1,
FROM_LOC_ORACLE_LEG1,
FROM_LOC_TYPE_LEG1,
FROM_LOC_CHANNEL_LEG1,
TO_LOC_LEG1,
TO_LOC_ORACLE_LEG1,
TO_LOC_TYPE_LEG1,
TO_LOC_CHANNEL_LEG1,
NULL AS TSF_TYPE_LEG2,
NULL AS TSF_LEG2,
NULL AS FROM_LOC_LEG2,
NULL AS FROM_LOC_ORACLE_LEG2,
NULL AS FROM_LOC_TYPE_LEG2,
NULL AS FROM_LOC_CHANNEL_LEG2,
NULL AS TO_LOC_LEG2,
NULL AS TO_LOC_ORACLE_LEG2,
NULL AS TO_LOC_TYPE_LEG2,
NULL AS TO_LOC_CHANNEL_LEG2,
NULL AS TSF_TYPE_LEG3,
NULL AS TSF_LEG3,
NULL AS FROM_LOC_LEG3,
NULL AS FROM_LOC_ORACLE_LEG3,
NULL AS FROM_LOC_TYPE_LEG3,
NULL AS FROM_LOC_CHANNEL_LEG3,
NULL AS TO_LOC_LEG3,
NULL AS TO_LOC_ORACLE_LEG3,
NULL AS TO_LOC_TYPE_LEG3,
NULL AS TO_LOC_CHANNEL_LEG3
FROM RTV_WH_WH */
)
;
SELECT * FROM PUC_TSF_LEGS_TMP;
-- view to simplify and break the legs in transfers
DROP VIEW V_PUC_TSF;
CREATE OR REPLACE VIEW V_PUC_TSF AS
SELECT TSF_TYPE, TSF_TYPE_LEG1 AS TSF_TYPE_SAP, TSF_LEG1 AS TSF, FROM_LOC_ORACLE_LEG1 AS FROM_LOC, FROM_LOC_TYPE_LEG1 AS FROM_LOC_TYPE, FROM_LOC_CHANNEL_LEG1 AS FROM_LOC_CHANNEL, TO_LOC_ORACLE_LEG1 AS TO_LOC, TO_LOC_TYPE_LEG1 AS TO_LOC_TYPE, TO_LOC_CHANNEL_LEG1 AS TO_LOC_CHANNEL
FROM PUC_TSF_LEGS_TMP
WHERE TSF_LEG1 IS NOT NULL
UNION ALL
SELECT TSF_TYPE, TSF_TYPE_LEG2 AS TSF_TYPE_SAP, TSF_LEG2 AS TSF, FROM_LOC_ORACLE_LEG2 AS FROM_LOC, FROM_LOC_TYPE_LEG2 AS FROM_LOC_TYPE, FROM_LOC_CHANNEL_LEG2 AS FROM_LOC_CHANNEL, TO_LOC_ORACLE_LEG2 AS TO_LOC, TO_LOC_TYPE_LEG2 AS TO_LOC_TYPE, TO_LOC_CHANNEL_LEG2 AS TO_LOC_CHANNEL
FROM PUC_TSF_LEGS_TMP
WHERE TSF_LEG2 IS NOT NULL
UNION ALL
SELECT TSF_TYPE, TSF_TYPE_LEG3 AS TSF_TYPE_SAP, TSF_LEG3 AS TSF, FROM_LOC_ORACLE_LEG3 AS FROM_LOC, FROM_LOC_TYPE_LEG3 AS FROM_LOC_TYPE, FROM_LOC_CHANNEL_LEG3 AS FROM_LOC_CHANNEL, TO_LOC_ORACLE_LEG3 AS TO_LOC, TO_LOC_TYPE_LEG3 AS TO_LOC_TYPE, TO_LOC_CHANNEL_LEG3 AS TO_LOC_CHANNEL
FROM PUC_TSF_LEGS_TMP
WHERE TSF_LEG3 IS NOT NULL
UNION ALL
-- BOOK TRANSFER (LEG2) WHEN THERE IS A DIFFERENT LOCATATIONS TO AND FROM BETWEEN LEG1 AND LEG2
-- THE VALUES WILL COME FROM LEG3
SELECT 'BT' AS TSF_TYPE, NULL AS TSF_TYPE_SAP, TSF_LEG3 AS TSF, TO_LOC_ORACLE_LEG1 AS FROM_LOC, TO_LOC_TYPE_LEG1 AS FROM_LOC_TYPE, TO_LOC_CHANNEL_LEG1 AS FROM_LOC_CHANNEL, FROM_LOC_ORACLE_LEG3 AS TO_LOC, FROM_LOC_TYPE_LEG3 AS TO_LOC_TYPE, FROM_LOC_CHANNEL_LEG3 AS TO_LOC_CHANNEL
FROM PUC_TSF_LEGS_TMP
WHERE TSF_LEG2 is null and TO_LOC_ORACLE_LEG1 <> FROM_LOC_ORACLE_LEG3 and TSF_LEG1 is not null and TSF_LEG3 is not null
;
select MAX(TSF) from V_PUC_TSF WHERE TSF_TYPE = 'BT' AND TSF IN (4302253253,4302255971,4302277450);
SELECT * FROM PUC_TSF_LEGS_TMP WHERE TSF_LEG3 IN (4302253253,4302255971,4302277450);