Source: DMF/PuC/AIF/ItemPR1_PV1.sql
select * from (
SELECT /*+ PARALLEL(16) */
m.MTART
,m.matnr
,DECODE(m.satnr, NULL, m.matnr, m.satnr) AS DISPO
,m.zzdispogrp AS STYLE
,(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 DIFF_1_SAP
,(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 DIFF_2_SAP
,ZZPRODUKT_WAWI as PRODUCT
,m.brand_id
,IDNLF
,saiso
,SAISJ
,ERSDA
,ZZALTWARE
FROM sap_slt_prod_pv1.mara m
,sap_slt_prod_pv1.wrf_brands wb
,sap_slt_prod_pv1.eina e
WHERE m.attyp = '02'
and e.matnr = m.satnr
--and e.LIFNR = '0000940506'
AND wb.mandt = m.mandt
AND wb.brand_id = m.brand_id
--AND wb.brand_type = '1'
--AND (E.IDNLF = '80-2000 07863220') -- '80-2000 07863220'
--and satnr = '004792665'
order by DIFF_1_SAP, DIFF_2_SAP, saisj desc, saiso desc, ersda desc)
where DISPO like '%9640716';
0993543 005064090028000007 620 610 03132
9640716 000068410142100002 816 630 00999
9640716 000068410142100003 816 630 01040
9640716 000068410142100004 816 630 01160
9640716 000068410142100005 816 630 01280
select COUNT(1) from DMF2.XREF_ITEM_IBC_RETAIL;
DROP TABLE XREF_ITEM_IBC_RETAIL;
CREATE TABLE XREF_ITEM_IBC_RETAIL AS
-- OWN BRAND ITEMS (PR1) to RETAIL ITEMS (PV1)
with OWN_BRANDS_SKU_PV1 AS
(SELECT dispo as DISPO_PV1,
item ITEM_PV1,
diff_1 AS COLOR_PV1,
diff_2 AS SIZE1_PV1,
IDNLF as VPN_PV1,
LIFNR AS SUPPLIER_PV1,
SAISO AS SEASON,
SAISJ AS SEASON_YEAR,
ZZALTWARE STATUS,
to_char(to_date(ERSDA,'yyyymmdd'),'dd-MON-yyyy hh24:mi:ss') CREATION_DATE
FROM puc_sku_im_own_brands_tmp t,
sap_slt_prod_pv1.eina e,
sap_slt_prod_pv1.mara m
where e.matnr = t.dispo
and t.item = m.matnr
and LIFNR = '0000940506'), -- ibc supplier on PV1 (own brands)
OWN_BRANDS_SKUS_PR1 AS
(SELECT m.ATTYP AS ATTYP_PR1,
m.SATNR AS DISPO_PR1,
m.MATNR AS ITEM_PR1,
m.COLOR AS COLOR_PR1,
m.SIZE1 AS SIZE1_PR1,
p.*,
RANK() OVER (
PARTITION BY m.MATNR
ORDER BY status, season_year DESC, CREATION_DATE DESC, ITEM_PV1 DESC
) AS r
FROM OWN_BRANDS_SKU_PV1 p,
sap_slt_prod_pR1.mara m
WHERE M.SATNR(+) = p.VPN_PV1
and m.color(+) = p.color_PV1
and m.size1(+) = p.size1_PV1),
RANK_OWN_BRANDS_SKUS_PR1 AS (
SELECT *
FROM OWN_BRANDS_SKUS_PR1
WHERE r = 1
)
select * from RANK_OWN_BRANDS_SKUS_PR1
;
---------------
/*
CREATE TABLE PUC_SKU_VARIANT_MSEG_TMP_APS NOLOGGING PARALLEL 16 AS
SELECT /*+ PARALLEL(16) */
/* DISTINCT ms.mandt
,ms.matnr
FROM sap_slt_prod_pv1.mseg ms
,sap_slt_prod_pv1.mara m
WHERE TRUNC(TO_DATE(ms.budat_mkpf,'YYYYMMDD')) >= TRUNC(ADD_MONTHS(SYSDATE, -24), 'YEAR')
AND m.mandt = ms.mandt
AND m.matnr = ms.matnr
AND m.attyp = '02';
CREATE INDEX PUC_SKU_VARIANT_MSEG_TMP_APS_I01 ON PUC_SKU_VARIANT_MSEG_TMP_APS(mandt, matnr) NOLOGGING;
begin
DBMS_STATS.GATHER_TABLE_STATS(ownname => SYS_CONTEXT('USERENV','CURRENT_SCHEMA'),
tabname => 'PUC_SKU_VARIANT_MSEG_TMP_APS',
cascade => TRUE);
end;
/
*/
SELECT /*+ PARALLEL(16) */
m.MTART
,m.matnr
,DECODE(m.satnr, NULL, m.matnr, m.satnr) AS DISPO
--,m.zzdispogrp AS STYLE
,(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 DIFF_1_SAP
,(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 DIFF_2_SAP
,IDNLF
FROM sap_slt_prod_pv1.mara m
,sap_slt_prod_pv1.wrf_brands wb
,sap_slt_prod_pv1.eina e
WHERE m.attyp = '02'
and e.matnr = m.satnr
--and e.LIFNR = '0000940506'
AND wb.mandt = m.mandt
AND wb.brand_id = m.brand_id
--AND wb.brand_type = '1'
--AND (E.IDNLF = '000000000770508631' or m.satnr = '000000000770508631')
and m.matnr = '000000110635900010'
/*AND EXISTS (SELECT 1
FROM PUC_SKU_VARIANT_MSEG_TMP_APS tmp
WHERE tmp.mandt = m.mandt
AND tmp.matnr = m.matnr)*/
;
--) where DIFF_1_SAP is null OR DIFF_2_SAP IS NULL; --189.809 (supplier) -- 191.267 (brand)
-- dummy items only has the brands and not the ibc supplier
SELECT m.* FROM sap_slt_prod_pr1.mara m where satnr = '000000000569504543';
------------------------------------------
-- exist in PV1 but not a correspondent SKU in PR1 (if the dispo exist it is enough)
WITH pr1_skus AS (
SELECT /*+ PARALLEL(16) */
m.MTART AS ITEM_TYPE_PR1,
m.SATNR AS DISPO_PR1,
m.MATNR AS ITEM_PR1,
m.COLOR AS COLOR_PR1,
m.SIZE1 AS SIZE1_PR1
FROM sap_slt_prod_pr1.mara m
WHERE 1=1
AND m.attyp = '02'
),
pv1_skus_own_brand AS (
SELECT /*+ PARALLEL(16) */
m.MTART AS ITEM_TYPE_PV1
,m.SATNR AS DISPO_PV1
,m.MATNR AS ITEM_PV1
,m.zzdispogrp AS STYLE
,(SELECT /*+ PARALLEL(16) */
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(16) */
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
,IDNLF AS VPN_PV1
,SAISO AS SEASON
,SAISJ AS SEASON_YEAR
,ZZALTWARE AS STATUS -- OLD ITEM?
,ERSDA AS CREATION_DATE
FROM sap_slt_prod_pv1.mara m
,sap_slt_prod_pv1.wrf_brands wb
,sap_slt_prod_pv1.eina e
WHERE m.attyp = '02'
AND e.matnr = m.satnr
AND e.LIFNR = '0000940506'
AND wb.mandt = m.mandt
AND wb.brand_id = m.brand_id
AND wb.brand_type = '1'
AND (EXISTS (SELECT /*+ PARALLEL(16) */ 1
FROM sap_slt_prod_pv1.mbew ms
WHERE lbkum > 0
AND m.matnr = ms.matnr)
OR
EXISTS (SELECT /*+ PARALLEL(16) */ 1
FROM sap_slt_prod_pv1.mseg ms
WHERE TRUNC(TO_DATE(ms.budat_mkpf,'YYYYMMDD')) >= TRUNC(ADD_MONTHS(SYSDATE, -24), 'YEAR')
AND m.matnr = ms.matnr))
)
SELECT /*+ PARALLEL(16) */
ITEM_TYPE_PV1,
DISPO_PV1,
ITEM_PV1,
STYLE,
COLOR_PV1,
SIZE1_PV1,
VPN_PV1,
SEASON,
SEASON_YEAR,
STATUS,
CREATION_DATE,
ITEM_TYPE_PR1,
DISPO_PR1,
--(select matnr from sap_slt_prod_pr1.mara where matnr = PV1.VPN_PV1) as DISPO_PR1,
ITEM_PR1,
COLOR_PR1,
SIZE1_PR1
FROM pv1_skus_own_brand PV1
LEFT JOIN pr1_skus PR1 ON PV1.VPN_PV1 = PR1.DISPO_PR1 and PV1.COLOR_PV1 = PR1.COLOR_PR1 and PV1.SIZE1_PV1 = PR1.SIZE1_PR1
WHERE ITEM_PR1 IS NULL
;
-- exist in PR1 but not a correspondent SKU in PV1 (color not matching)
--drop table PR1_ITEM_MISSING_PV1;
--CREATE TABLE PR1_ITEM_MISSING_PV1 NOLOGGING PARALLEL 16 AS
WITH pr1_skus AS (
SELECT /*+ PARALLEL(16) */
m.MTART AS ITEM_TYPE_PR1
,m.SATNR AS DISPO_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
,m.ZZALTWARE AS STATUS_PR1 -- OLD ITEM?
,m.ERSDA AS CREATION_DATE_PR1
FROM sap_slt_prod_pr1.mara m
WHERE 1=1
AND (EXISTS (SELECT /*+ PARALLEL(16) */ 1
FROM sap_slt_prod_pr1.mbew ms
WHERE lbkum > 0
AND m.matnr = ms.matnr)
OR
EXISTS (SELECT /*+ PARALLEL(16) */ 1
FROM sap_slt_prod_pr1.mseg ms
WHERE TRUNC(TO_DATE(ms.budat_mkpf,'YYYYMMDD')) >= TRUNC(ADD_MONTHS(SYSDATE, -24), 'YEAR')
AND m.matnr = ms.matnr))
AND m.attyp = '02'
),
pv1_skus_own_brand AS (
SELECT /*+ PARALLEL(16) */
m.MTART AS ITEM_TYPE_PV1
,m.SATNR AS DISPO_PV1
,m.MATNR AS ITEM_PV1
,m.zzdispogrp AS STYLE_PV1
,(SELECT /*+ PARALLEL(16) */
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(16) */
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
,IDNLF AS VPN_PV1
,SAISO AS SEASON_PV1
,SAISJ AS SEASON_YEAR_PV1
,ZZALTWARE AS STATUS_PV1 -- OLD ITEM?
,ERSDA AS CREATION_DATE_PV1
,ZZLFFARB AS SUPPLIER_COLOR_PV1
FROM sap_slt_prod_pv1.mara m
--,sap_slt_prod_pv1.wrf_brands wb
,sap_slt_prod_pv1.eina e
WHERE m.attyp = '02'
AND e.matnr = m.satnr
--AND e.LIFNR = '0000940506'
--AND wb.mandt = m.mandt
--AND wb.brand_id = m.brand_id
--AND wb.brand_type = '1'
)
SELECT /*+ PARALLEL(16) */
*
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
WHERE ITEM_PV1 IS NULL
;
WITH pr1_skus AS (
SELECT /*+ PARALLEL(16) */
ITEM_TYPE_PR1,
DISPO_PR1,
ITEM_PR1,
COLOR_PR1,
SIZE1_PR1,
SEASON__PR1,
SEASON_YEAR,
STATUS_PR1, -- OLD ITEM?
CREATION_DATE_PR1
FROM PR1_ITEM_MISSING_PV1 m
),
pv1_skus_own_brand AS (
SELECT /*+ PARALLEL(16) */
m.MTART AS ITEM_TYPE_PV1
,m.SATNR AS DISPO_PV1
,m.MATNR AS ITEM_PV1
,m.zzdispogrp AS STYLE
,(SELECT /*+ PARALLEL(16) */
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(16) */
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
,IDNLF AS VPN_PV1
,SAISO AS SEASON_PV1
,SAISJ AS SEASON_YEAR_PV1
,ZZALTWARE AS STATUS_PV1 -- OLD ITEM?
,ERSDA AS CREATION_DATE_PV1
,ZZLFFARB AS SUPPLIER_COLOR_PV1
FROM sap_slt_prod_pv1.mara m
--,sap_slt_prod_pv1.wrf_brands wb
,sap_slt_prod_pv1.eina e
WHERE m.attyp = '02'
AND e.matnr = m.satnr
--AND e.LIFNR = '0000940506'
--AND wb.mandt = m.mandt
--AND wb.brand_id = m.brand_id
--AND wb.brand_type = '1'
)
SELECT /*+ PARALLEL(16) */
*
FROM pr1_skus PR1
LEFT JOIN pv1_skus_own_brand PV1 ON PV1.VPN_PV1 = PR1.DISPO_PR1
and PV1.SUPPLIER_COLOR_PV1 = PR1.COLOR_PR1
and PV1.SIZE1_PV1 = PR1.SIZE1_PR1
WHERE ITEM_PV1 IS NULL
;
SELECT COLOR, SIZE1,IDNLF, count(1)
FROM sap_slt_prod_pr1.mara m,
sap_slt_prod_pr1.eina e
where e.matnr = m.satnr
group by COLOR, SIZE1,IDNLF having count(1) > 1;
SELECT *
FROM sap_slt_prod_pr1.mara m,
sap_slt_prod_pr1.eina e
where e.matnr = m.satnr
and color = '632' and SIZE1 = '00860' and IDNLF = '236092';
----------------------------------
--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 >= '20240101' )
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')
AND EKKO.BEDAT >= 20240101
AND EKPO.MATNR = M.MATNR)
AND m.attyp = '02';
SELECT EKPO.*
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')
AND EKKO.BEDAT >= 2020101
AND ekpo.matnr = '005065091012900092' ;
--2) CREATE INDEX
CREATE INDEX DMF_PUC_SKU_VARIANT_FILTER_PR1_TMP_IDX1 ON PUC_SKU_VARIANT_FILTER_PR1_TMP ('MATNR');
select count(1) from PUC_SKU_VARIANT_FILTER_PR1_TMP;
SELECT MATNR FROM PUC_SKU_VARIANT_FILTER_PR1_TMP GROUP BY MATNR HAVING COUNT(1) > 1;
/*SELECT
EKPO.LOEKZ, EKPO.ELIKZ, EKPO.MENGE, EKET.WEMNG, EKPO.*
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 and EKET.WEMNG > 0
WHERE ekko.ebeln = '4502042212';*/
-- 4502031930 RECEIVED LESS THAN ORDERED -->
CREATE INDEX ZVGH_MAT_DISPO_IDX1 ON sap_slt_prod_pr1.ZVGH_MAT_DISPO ('MATNR');
DROP TABLE XREF_ITEM_IBC_RETAIL2;
CREATE TABLE XREF_ITEM_IBC_RETAIL2 (
ITEM_TYPE_PR1 VARCHAR2(4),
--DISPOGRP_PR1 VARCHAR2(9), -- NOT EXIST FOR PR1
DISPO_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),
SUPPLIER_PV1 VARCHAR2(10),
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)
) NOLOGGING;
TRUNCATE TABLE XREF_ITEM_IBC_RETAIL2;
--INSERT INTO /*+ PARALLEL(24) */ XREF_ITEM_IBC_RETAIL2
wITH pr1_skus AS (
SELECT /*+ PARALLEL(16) */
m.MTART AS ITEM_TYPE_PR1
-- ,m.zzdispogrp AS DISPOGRP_PR1 -- NOT EXIST FOR 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.wrf_brands wb
,sap_slt_prod_pv1.eina e
WHERE m.attyp = '02'
AND e.matnr = m.satnr
AND e.LIFNR = '0000940506'
--AND wb.mandt = m.mandt
--AND wb.brand_id = m.brand_id
--AND wb.brand_type = '1'
)
select *
from (SELECT /*+ PARALLEL(16) */
PR1.*,
pv1.ITEM_TYPE_PV1 ,
pv1.DISPOGRP_PV1 ,
pv1.DISPO_PV1 ,
pv1.ITEM_PV1 ,
pv1.VPN_PV1 ,
pv1.SUPPLIER_PV1 ,
pv1.COLOR_PV1 ,
pv1.SIZE1_PV1 ,
pv1.SEASON_PV1 ,
pv1.SEASON_YEAR_PV1 ,
pv1.STATUS_PV1 ,
pv1.CREATION_DATE_PV1,
pv1_2.ITEM_TYPE_PV1 AS ITEM_TYPE_PV1_2 ,
pv1_2.DISPOGRP_PV1 AS DISPOGRP_PV1_2 ,
pv1_2.DISPO_PV1 AS DISPO_PV1_2 ,
pv1_2.ITEM_PV1 AS ITEM_PV1_2 ,
pv1_2.VPN_PV1 AS VPN_PV1_2 ,
pv1_2.SUPPLIER_PV1 AS SUPPLIER_PV1_2 ,
pv1_2.COLOR_PV1 AS COLOR_PV1_2 ,
pv1_2.SIZE1_PV1 AS SIZE1_PV1_2 ,
pv1_2.SEASON_PV1 AS SEASON_PV1_2 ,
pv1_2.SEASON_YEAR_PV1 AS SEASON_YEAR_PV1_2 ,
pv1_2.STATUS_PV1 AS STATUS_PV1_2 ,
pv1_2.CREATION_DATE_PV1 AS CREATION_DATE_PV1_2,
RANK() OVER (
PARTITION BY ITEM_PR1
ORDER BY pv1.STATUS_PV1, pv1.SEASON_YEAR_PV1 DESC, pv1.CREATION_DATE_PV1 DESC, pv1.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.COLOR_PV1 = PR1.COLOR_PR1
and PV1_2.SIZE1_PV1 = PR1.SIZE1_PR1
/*LEFT JOIN pv1_skus_own_brand PV1_3 ON PV1_3.DISPO_PV1 = PR1.DISPO2_PR1
and PV1_3.COLOR_PV1 = PR1.COLOR_PR1 --ZZLFFARB
and PV1_3.SIZE1_PV1 = PR1.SIZE1_PR1*/
) a
WHERE 1=1
and r = 1
and ITEM_PV1 is null and ITEM_PV1_2 is null
--and item_pr1 = '000014010030800001'
;
---189624
select count(1) from PUC_SKU_VARIANT_FILTER_PR1_TMP;
---189624
SELECT COUNT(1) FROM XREF_ITEM_IBC_RETAIL2;
SELECT ITEM_PR1, COUNT(1) FROM XREF_ITEM_IBC_RETAIL2 GROUP BY ITEM_PR1 HAVING COUNT(1) > 1;
SELECT * FROM XREF_ITEM_IBC_RETAIL2 where ITEM_PV1 is null;
SELECT * FROM XREF_ITEM_IBC_RETAIL2 where item_pr1 = '001284651334900037';
select * from sap_slt_prod_pv1.eina where IDNLF = '000000012846513349';
select * from sap_slt_prod_pr1.ZVGH_MAT_DISPO d where d.matnr = '001284651334900037';
select * from sap_slt_prod_pv1.mara where satnr like '%1106359';
select ZZLFFARB, m .* from sap_slt_prod_pv1.mara m WHERE ZZLFFARB <> ' ' ;
select ZZLFFARB, m .* from sap_slt_prod_pv1.mara m WHERE m.attyp = '02' ;
000014060826700137 720 00460
001284651334900037 441 00010 -- PV1 DISPO = 1106359
000014060826700139 720 00500
000014060826700140 720 00520
000014060826700141 720 00540
000014060826700142 720 00560
000014060826700155 720 01060
000014060826700188 310 00480
wITH pr1_skus AS (
SELECT /*+ PARALLEL(16) */
m.MTART AS ITEM_TYPE_PR1
,m.SATNR AS DISPO_PR1
,ZZDISPO AS DISPO_PR1_2
,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
,m.ZZALTWARE AS STATUS_PR1 -- OLD ITEM?
,m.ERSDA AS CREATION_DATE_PR1
FROM sap_slt_prod_pr1.mara m
LEFT JOIN sap_slt_prod_pr1.ZVGH_MAT_DISPO d on d.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(16) */
m.MTART AS ITEM_TYPE_PV1
,m.SATNR AS DISPO_PV1
,m.MATNR AS ITEM_PV1
,m.zzdispogrp AS STYLE_PV1
,(SELECT /*+ PARALLEL(16) */
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(16) */
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
,IDNLF AS VPN_PV1
,SAISO AS SEASON_PV1
,SAISJ AS SEASON_YEAR_PV1
,ZZALTWARE AS STATUS_PV1 -- OLD ITEM?
,ERSDA AS CREATION_DATE_PV1
,ZZLFFARB AS SUPPLIER_COLOR_PV1
FROM sap_slt_prod_pv1.mara m
--,sap_slt_prod_pv1.wrf_brands wb
,sap_slt_prod_pv1.eina e
WHERE m.attyp = '02'
AND e.matnr = m.satnr
AND e.LIFNR = '0000940506'
--AND wb.mandt = m.mandt
--AND wb.brand_id = m.brand_id
--AND wb.brand_type = '1'
)
SELECT /*+ PARALLEL(16) */
*
FROM pr1_skus PR1
LEFT JOIN pv1_skus_own_brand PV1 ON PV1.DISPO_PV1 = LPAD(PR1.DISPO_PR1_2, 18, '0')
--and PV1.COLOR_PV1 = PR1.COLOR_PR1
and PV1.SIZE1_PV1 = PR1.SIZE1_PR1
WHERE ITEM_PV1 is NULL
;
-- pr1 dispo link to pv1 using ZZDISPO = PV1 DISPO (color)
select ZZDISPO, d.matnr, m.SIZE1 AS SIZE1_PR1_2 from sap_slt_prod_pr1.ZVGH_MAT_DISPO d, sap_slt_prod_pr1.mara m where m.matnr = d.matnr;
select * from sap_slt_prod_pr1.ZVGH_MAT_DISPO d;
select distinct ATTYP from sap_slt_prod_pv1.mara where mtart = 'ZMOD'
select * from xref_location where sap_id in ('0446','0546');