Source: DMF/PuC/AIF/ItemDispo_Validation.sql
drop table APS_ITEM_DISPOGROUP_VALIDATION;
CREATE TABLE APS_ITEM_DISPOGROUP_VALIDATION AS
SELECT /*+ PARALLEL(16) */ disctinct
m.MTART
,m.matnr as ITEM
,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 COLOR1
,(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 SIZE2
,ZZPRODUKT_WAWI as PRODUCT
,m.brand_id
,IDNLF as VPN
,saiso as SEASON
,SAISJ as SEASON_YEAR
,ERSDA as CREATE_DATE
,ZZALTWARE as OLD_ITEM
FROM sap_slt_prod_pv1.mara m
,sap_slt_prod_pv1.wrf_brands wb
,sap_slt_prod_pv1.eina e
,dmf2.item_master_ctrl im
WHERE 1=1
and im.item = m.matnr
and m.attyp = '02'
and e.matnr = m.satnr
AND wb.mandt = m.mandt
AND wb.brand_id = m.brand_id
--)
--group by VPN, PRODUCT, COLOR1, SIZE2, SEASON, SEASON_YEAR
--order by COLOR1, SIZE2, saisj desc, saiso desc, ersda desc
;
select ITEM, item_winner_ind from dmf2.puc_item_winner_xref where item_winner_ind = 'Y' GROUP BY ITEM, item_winner_ind HAVING COUNT(1) > 1;
select * from dmf2.puc_item_winner_xref where ITEM = 000000178156503334;
SELECT * FROM APS_ITEM_DISPOGROUP_VALIDATION;
ALTER TABLE APS_ITEM_DISPOGROUP_VALIDATION ADD ITEM_WINNER_IND VARCHAR2(1);
ALTER TABLE APS_ITEM_DISPOGROUP_VALIDATION DROP COLUMN ITEM_WINNER_IND;
select item from (SELECT item, item_winner_ind FROM dmf2.puc_item_winner_xref x group by item, item_winner_ind) group by item having count(1) >1;
SELECT * FROM dmf2.puc_item_winner_xref where item = '000000205204900360';
--SELECT COUNT(1) FROM (
SELECT VPN, COLOR1, SIZE2, SEASON, SEASON_YEAR, COUNT(1)
FROM APS_ITEM_DISPOGROUP_VALIDATION
group by VPN, COLOR1, SIZE2, SEASON, SEASON_YEAR
HAVING COUNT(1) > 1;
--)
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 = '003893035') -- '80-2000 07863220'
--and satnr = '004792665'
order by DIFF_1_SAP, DIFF_2_SAP, saisj desc, saiso desc, ersda desc)
where DIFF_1_SAP = '816'
--and DIFF_2_SAP = '10020'
;
select * from dmf2.puc_item_winner_xref order by DIFF_1, DIFF_2, vpn, ZZPRODUKT_WAWI;
select * from dmf2.puc_item_winner_xref where sap_item_parent = '004344996';
select * from dmf2.puc_item_winner_xref where DIFF_1 = 036 and vpn = '50281893/00';
select DIFF_1, DIFF_2, vpn, ZZPRODUKT_WAWI, lifnr, COUNT(1) from dmf2.puc_item_winner_xref group by DIFF_1, DIFF_2, vpn, ZZPRODUKT_WAWI, lifnr having count(1) > 1;
select DIFF_1, DIFF_2, vpn, ZZPRODUKT_WAWI, lifnr, saiso, saisj, sap_item_parent, COUNT(1)
from dmf2.puc_item_winner_xref
group by DIFF_1, DIFF_2, vpn, ZZPRODUKT_WAWI, lifnr, saiso, saisj, sap_item_parent having count(1) > 2;
v
select * from dmf2.puc_item_winner_xref where DIFF_1 = '036' and diff_2 = '10020' and vpn = 'V2YB15KB3P2';
DISPOGROUP - 007569443 (STYLE)
DISPO - 000000000002021353 (CFA)
SKU - 000000202135310020 (SKU)
select * from dmf2.puc_item_winner_xref WHERE ITEM_WINNER_IND = 'N';
select ATTYP, SATNR, MATNR, ZZALTWARE,SAISO,SAISJ, BRAND_ID, M.*
from sap_slt_prod_pV1.mara m
where matnr in (
select item from dmf2.puc_item_winner_xref WHERE DIFF_1 = '632' and diff_2 = '10030' and vpn = '1040544' and ZZPRODUKT_WAWI = '0000525');
select * from dmf2.puc_item_winner_xref WHERE DIFF_1 = '036' and diff_2 = '10020' and vpn = 'V2YB15KB3P2' ORDER BY ERSDA DESC;
select * from dmf2.puc_item_winner_xref WHERE DIFF_1 = '612' and diff_2 = '00420' and vpn = '074564' ORDER BY ERSDA DESC;
select * from dmf2.puc_item_winner_xref WHERE DIFF_1 = '632' and diff_2 = '10060' and vpn = 'EM003011.AF10762' and ZZPRODUKT_WAWI = '0000220' and lifnr = '0000236100' ORDER BY ERSDA DESC;
select * from dmf2.puc_item_winner_xref WHERE DIFF_1 = '632' and diff_2 = '10030' and vpn = '1040544' and ZZPRODUKT_WAWI = '0000525' and lifnr = '0000236970' ORDER BY ERSDA DESC;
-- dispos not in the same style. is it a problem?
select * from dmf2.puc_item_winner_xref WHERE DIFF_1 = '451' and diff_2 in ('10060','10010') and vpn = '20105036' and ZZPRODUKT_WAWI = '0000555' and lifnr = '0000236331' ORDER BY ERSDA DESC;
select x.*, rank() over (partition by item order by dispo,diff_1,diff_2,item_winner_ind,saisj,ersda) from dmf2.puc_item_winner_xref x WHERE vpn = '20105036' and ZZPRODUKT_WAWI = '0000555' and lifnr = '0000236331' ORDER BY ERSDA DESC;
select * from dmf2.puc_item_winner_xref WHERE DIFF_1 = '632' and diff_2 = '10030' and vpn = '1040544' and ZZPRODUKT_WAWI = '0000525';
select * from dmf2.puc_item_winner_xref WHERE sap_item_parent = '007424946' and item_winner_ind = 'Y';
select count(1) from dmf2.puc_item_winner_xref WHERE item_winner_ind = 'Y';
select * from dmf2.puc_item_winner_xref WHERE DIFF_1 = '036' and diff_2 in ('10060') and vpn = '50449501/00' and lifnr = '0000226394' ORDER BY ERSDA DESC;
select * from dmf2.puc_item_winner_xref WHERE DIFF_1 = '036' and diff_2 in ('01100','00460') and vpn = '22781543' and ZZPRODUKT_WAWI = '0000117' and lifnr = '0000140465' ORDER BY ERSDA DESC;
select x.*, rank() over (partition by Diff_1, diff_2,vpn,ZZPRODUKT_WAWI order by dispo) as STYLE_RK
from dmf2.puc_item_winner_xref x
WHERE vpn = '22781543' and ZZPRODUKT_WAWI = '0000117' and lifnr = '0000140465'
--AND DISPO = '000000000001143776'
ORDER BY STYLE_RK;
WITH r AS (
SELECT t.*,
DENSE_RANK() OVER (
PARTITION by sap_item_parent, Diff_1, diff_2,vpn,ZZPRODUKT_WAWI
ORDER BY dispo, diff_1, diff_2
) rank_color_size
FROM dmf2.puc_item_winner_xref t
)
SELECT r.*,
max(rank_color_size)
OVER (PARTITION BY vpn, ZZPRODUKT_WAWI, dispo, sap_item_parent) final_rank
FROM r
WHERE --vpn = '22781543' and ZZPRODUKT_WAWI = '0000117' and lifnr = '0000140465'
--vpn = '113679' and ZZPRODUKT_WAWI = '0000190' and lifnr = '0000236554'
--vpn = '50561776/00' and ZZPRODUKT_WAWI = '0000160' and lifnr = '0000107697'
--vpn = 'PSW2510323' and ZZPRODUKT_WAWI = '0000200' and lifnr = '0000228958'
--vpn = '25328' and ZZPRODUKT_WAWI = '0000510' and lifnr = '0000235693' -- multiple dispo groups and dispo for the same vpn and product
vpn = '000000010740904045' and ZZPRODUKT_WAWI = '0000150' and lifnr = '0000940506' -- ibc
order by diff_1, diff_2;
selecT *
from PUC_ITEM_WINNER_XREF x
left join PUC_ITEM_WINNER_IDS_TMP y
on y.vpn = x.vpn
and y.diff_1 = x.diff_1
and y.diff_2 = x.diff_2
and y.zzprodukt_wawi = x.zzprodukt_wawi
and y.rank = x.rank
where x.vpn = '22781543' and x.ZZPRODUKT_WAWI = '0000117' and x.lifnr = '0000140465';
select * from dmf2.puc_item_winner_IDS;
select * from dmf2.puc_item_winner_xref WHERE vpn = 'O415880' and ZZPRODUKT_WAWI = '0000175' and lifnr = '0000233234';
select * from dmf2.puc_item_winner_xref WHERE vpn = '000000010740904045' and ZZPRODUKT_WAWI = '0000150' and lifnr = '0000940506';
select * from dmf2.puc_item_winner_xref x1,
dmf2.puc_item_winner_xref x2
where 1=1
and x1.diff_1 = x2.diff_1
and x1.diff_2 = x2.diff_2
and x1.vpn = x2.vpn
--and x1.item_winner_ind = 'Y'
--and x1.item_winner_ind = x2.item_winner_ind
and x1.ZZPRODUKT_WAWI = x2.ZZPRODUKT_WAWI
--and x1.dispo = x2.dispo
and x1.lifnr = x2.lifnr
and x1.sap_item_parent = x2.sap_item_parent
and x1.item <> x2.item;