Source: DMF/PuC/MFCS/ItemPR1_PV1_v2.sql
--1) CREATE TEMP TABLE FOR PR1 ELIGBLE ITEMS
drop table PUC_SKU_VARIANT_FILTER_PR1_TMP;
CREATE TABLE PUC_SKU_VARIANT_FILTER_PR1_TMP NOLOGGING PARALLEL 16 AS
-- ITEMS TRANSACTED FROM 2024 -- 160.665
SELECT /*+ PARALLEL(16) */
m.mandt,
m.matnr
--count(1)
FROM sap_slt_prod_pr1.mara m
WHERE EXISTS ( SELECT 1
FROM sap_slt_prod_pr1.mseg ms
WHERE ms.mandt = m.mandt
AND ms.matnr = m.matnr
AND ms.budat_mkpf >= '20241027' )
AND m.attyp = '02'
UNION
-- STOCK ITEM > 0 -- 10.744
SELECT /*+ PARALLEL(16) */
m.mandt,
m.matnr
--count(1)
FROM sap_slt_prod_pr1.mara m
WHERE EXISTS ( SELECT 1
FROM sap_slt_prod_pr1.mbew mb
WHERE mb.mandt = m.mandt
AND mb.matnr = m.matnr
AND mb.lbkum > 0 )
AND m.attyp = '02'
UNION
-- OPEN ORDER ITEMS (RECEIVED LESS THAN ORDERED) -- 52.864
SELECT /*+ PARALLEL(16) */
m.mandt,
m.matnr
--count(1)
FROM sap_slt_prod_pr1.mara m
WHERE EXISTS ( SELECT 1
FROM SAP_SLT_PROD_PR1.EKKO EKKO
JOIN SAP_SLT_PROD_PR1.EKPO EKPO ON EKPO.EBELN = EKKO.EBELN AND EKPO.UEBPO = '00000' AND EKPO.LOEKZ = ' ' AND EKPO.ELIKZ = ' ' AND EKPO.MENGE > 0
LEFT JOIN SAP_SLT_PROD_PR1.EKET EKET on EKET.EBELN = EKPO.EBELN AND EKET.EBELP = EKPO.EBELP
WHERE 1=1
AND EKPO.MENGE > EKET.WEMNG
AND EKKO.BSART in ('ZGRP', 'ZGUM','ZGNB','ZGVB','ZGWK','ZGWK','ZGXB','ZUB2')
AND EKKO.BEDAT >= 20241027
AND EKPO.MATNR = M.MATNR)
AND m.attyp = '02';
--2) CREATE INDEX
CREATE INDEX DMF_PUC_SKU_VARIANT_FILTER_PR1_TMP_IDX1 ON PUC_SKU_VARIANT_FILTER_PR1_TMP ('MATNR');
DROP TABLE XREF_ITEM_IBC_RETAIL;
CREATE TABLE XREF_ITEM_IBC_RETAIL (
ITEM_TYPE_PR1 VARCHAR2(4),
--DISPOGRP_PR1 VARCHAR2(9), -- NOT EXIST FOR PR1
DISPO_PR1 VARCHAR2(18),
DISPO2_PR1 VARCHAR2(18),
ITEM_PR1 VARCHAR2(18),
COLOR_PR1 VARCHAR2(10),
SIZE1_PR1 VARCHAR2(10),
SEASON_PR1 VARCHAR2(4),
SEASON_YEAR_PR1 VARCHAR2(4),
STATUS_PR1 VARCHAR2(1),
CREATION_DATE_PR1 VARCHAR2(8),
ITEM_TYPE_PV1 VARCHAR2(4),
DISPOGRP_PV1 VARCHAR2(9),
DISPO_PV1 VARCHAR2(18),
ITEM_PV1 VARCHAR2(18),
VPN_PV1 VARCHAR2(35),
COLOR_PV1 VARCHAR2(10),
SIZE1_PV1 VARCHAR2(10),
SEASON_PV1 VARCHAR2(4),
SEASON_YEAR_PV1 VARCHAR2(4),
STATUS_PV1 VARCHAR2(1),
CREATION_DATE_PV1 VARCHAR2(8),
RANKING NUMBER(1)
) NOLOGGING;
INSERT INTO /*+ PARALLEL(24) */ XREF_ITEM_IBC_RETAIL
wITH pr1_skus AS (
SELECT /*+ PARALLEL(16) */
m.MTART AS ITEM_TYPE_PR1
,m.SATNR AS DISPO_PR1
,LPAD(dispo2.ZZDISPO, 18, '0') AS DISPO2_PR1
,m.MATNR AS ITEM_PR1
,m.COLOR AS COLOR_PR1
,m.SIZE1 AS SIZE1_PR1
,m.SAISO AS SEASON_PR1
,m.SAISJ AS SEASON_YEAR_PR1
,m.ZZALTWARE AS STATUS_PR1
,m.ERSDA AS CREATION_DATE_PR1
FROM sap_slt_prod_pr1.mara m
LEFT JOIN sap_slt_prod_pr1.ZVGH_MAT_DISPO dispo2 on dispo2.matnr = m.matnr
WHERE 1=1
AND EXISTS (SELECT /*+ PARALLEL(16) */ 1
FROM PUC_SKU_VARIANT_FILTER_PR1_TMP ms
WHERE m.matnr = ms.matnr)
AND m.attyp = '02'
),
pv1_skus_own_brand AS (
SELECT /*+ PARALLEL(8) */
m.MTART AS ITEM_TYPE_PV1
,m.zzdispogrp AS DISPOGRP_PV1
,m.SATNR AS DISPO_PV1
,m.MATNR AS ITEM_PV1
,IDNLF AS VPN_PV1
,LIFNR AS SUPPLIER_PV1
,(SELECT /*+ PARALLEL(8) */
ap.atwrt
FROM sap_slt_prod_pv1.inob ib
,sap_slt_prod_pv1.ausp ap
WHERE ib.mandt = m.mandt
AND ib.objek = m.matnr
AND ap.mandt = ib.mandt
AND ap.objek = ib.cuobj
AND ap.atinn IN ('0000000001')) AS COLOR_PV1
,(SELECT /*+ PARALLEL(8) */
ap.atwrt
FROM sap_slt_prod_pv1.inob ib
,sap_slt_prod_pv1.ausp ap
WHERE ib.mandt = m.mandt
AND ib.objek = m.matnr
AND ap.mandt = ib.mandt
AND ap.objek = ib.cuobj
AND ap.atinn IN ('0000000002')) AS SIZE1_PV1
,SAISO AS SEASON_PV1
,SAISJ AS SEASON_YEAR_PV1
,ZZALTWARE AS STATUS_PV1
,ERSDA AS CREATION_DATE_PV1
,m.ZZLFFARB AS SUPPLIER_COLOR
FROM sap_slt_prod_pv1.mara m
,sap_slt_prod_pv1.eina e
WHERE m.attyp = '02'
AND e.matnr = m.satnr
AND e.LIFNR = '0000940506'
)
SELECT *
FROM (SELECT /*+ PARALLEL(16) */
PR1.*,
nvl(pv1.ITEM_TYPE_PV1,pv1_2.ITEM_TYPE_PV1) as ITEM_TYPE_PV1,
nvl(pv1.DISPOGRP_PV1,pv1_2.DISPOGRP_PV1) as DISPOGRP_PV1,
nvl(pv1.DISPO_PV1,pv1_2.DISPO_PV1) as DISPO_PV1,
nvl(pv1.ITEM_PV1,pv1_2.ITEM_PV1) as ITEM_PV1,
nvl(pv1.VPN_PV1,pv1_2.VPN_PV1) as VPN_PV1,
nvl(pv1.COLOR_PV1,pv1_2.COLOR_PV1) as COLOR_PV1,
nvl(pv1.SIZE1_PV1,pv1_2.SIZE1_PV1) as SIZE1_PV1,
nvl(pv1.SEASON_PV1,pv1_2.SEASON_PV1) as SEASON_PV1_2,
nvl(pv1.SEASON_YEAR_PV1,pv1_2.SEASON_YEAR_PV1) as SEASON_YEAR_PV1,
nvl(pv1.STATUS_PV1,pv1_2.STATUS_PV1) as STATUS_PV1,
nvl(pv1.CREATION_DATE_PV1,pv1_2.CREATION_DATE_PV1) as CREATION_DATE_PV1_2,
RANK() OVER (
PARTITION BY ITEM_PR1
ORDER BY nvl(pv1.STATUS_PV1,pv1_2.STATUS_PV1), nvl(pv1.SEASON_YEAR_PV1,pv1_2.SEASON_YEAR_PV1) DESC, nvl(pv1.CREATION_DATE_PV1,pv1_2.CREATION_DATE_PV1) DESC, nvl(pv1.ITEM_PV1,pv1_2.ITEM_PV1) DESC
) AS r
FROM pr1_skus pr1
LEFT JOIN pv1_skus_own_brand pv1 ON pv1.VPN_PV1 = pr1.DISPO_PR1
AND pv1.COLOR_PV1 = pr1.COLOR_PR1
AND pv1.SIZE1_PV1 = pr1.SIZE1_PR1
LEFT JOIN pv1_skus_own_brand pv1_2 ON pv1_2.DISPO_PV1 = pr1.DISPO2_PR1
AND pv1_2.SIZE1_PV1 = pr1.SIZE1_PR1) a
WHERE 1=1
AND r = 1
;
SELECT '040' AS MANDT, ITEM_PV1 FROM XREF_ITEM_IBC_RETAIL