Source: DMF/PuC/AIF/Item_Validation.sql
select * from PUC_SKU_VARIANT_MSEG_TMP where matnr = '000000183183410030'
SELECT COUNT(1) FROM PUC_SKU_VARIANT_MSEG_TMP; -- 1491342
select count(1) from item_master_ctrl where item_level = tran_level;
select item from item_master_ctrl group by item having count(1) > 1;
select * from item_supplier_ctrl where item = '000000110886410050';
select saiso, saisj from sap_slt_prod_pv1.MARA M WHERE M.matnr in (select matnr from PUC_SKU_VARIANT_MSEG_TMP) order by saisj;
select COUNT(1)
from sap_slt_prod_pv1.MARA M
left join sap_slt_prod_pv1.a018 a on m.matnr = a.matnr
and kschl = 'YPBE'
and ekorg = '0253'
and datbi = '99991231'
where ATTYP = '01'
and m.matnr in (select matnr from PUC_SKU_VARIANT_MSEG_TMP)
and KNUMH IS NULL;
SELECT COUNT(1) FROM (
SELECT DISTINCT ZZDISPOGRP FROM sap_slt_prod_pv1.MARA M, PUC_SKU_VARIANT_MSEG_TMP ME WHERE M.MATNR = ME.MATNR
);
SELECT COUNT(1) FROM (
SELECT DISTINCT EAN11 FROM sap_slt_prod_pv1.MEAN M, PUC_SKU_VARIANT_MSEG_TMP ME WHERE M.MATNR = ME.MATNR
);
SELECT COUNT(1) FROM sap_slt_prod_pv1.MARA M WHERE M.MTART in ('VERP','ZNLA','ZDIE'); --308
SELECT COUNT(1) FROM PUC_SKU_IM_SERVICE_TMP;
SELECT * FROM ITEM_MASTER_CTRL WHERE ITEM_LEVEL = TRAN_LEVEL AND ITEM_LEVEL = 1;
SELECT M.MTART, M.* FROM sap_slt_prod_pv1.MARA M WHERE MATNR LIKE 'TILL%';
SELECT M.MTART, M.* FROM sap_slt_prod_pv1.MARA M WHERE MATNR = '000000000000090000';
SELECT M.MTART, M.* FROM sap_slt_prod_pv1.MARA M WHERE M.MTART in ('ZNLA');
-- EXCL_BRANDS
-- PUC_SKU_EXCL_BRANDS_TMP -- 1.309.931
SELECT COUNT(1) FRoM (
SELECT /*+ PARALLEL(16) */
DISTINCT m.mandt
,m.matnr
,DECODE(m.satnr, NULL, m.matnr, m.satnr) AS DISPO
,m.zzdispogrp AS STYLE
FROM sap_slt_prod_pv1.mara m
,sap_slt_prod_pv1.wrf_brands wb
WHERE m.attyp = '02'
AND wb.mandt = m.mandt
AND wb.brand_id = m.brand_id
AND wb.brand_type = '2'
AND EXISTS (SELECT 1
FROM puc_sku_variant_mseg_tmp tmp
WHERE tmp.mandt = m.mandt
AND tmp.matnr = m.matnr)); -- 1.309.931
-- color and size
select count(1) from PUC_SKU_DIFFS_EXCL_BRANDS_TMP; -- 2619862 / 2 = 1.309.931
-- item master
select count(1) from PUC_SKU_IM_EXCL_BRANDS_TMP; -- 1309914 ( PUC_SKU_EXCL_BRANDS_TMP - 17)
SELECT MTART, ATTYP, SATNR, M.MATNR, COLOR, SIZE1, ZZALTWARE,SAISO,SAISJ, BRAND_ID, zzwgr_wawi, M.*
FROM PUC_SKU_EXCL_BRANDS_TMP so,
sap_slt_prod_pv1.mara m
,sap_slt_prod_pv1.maw1 m1
,xref_zsta_stw2 xz
,subclass_stg ss
WHERE m.mandt = so.mandt
AND m.matnr = so.MATNR
AND m1.mandt = m.mandt
AND m1.matnr = m.matnr
AND xz.wgr(+) = m.zzwgr_wawi
AND ss.class (+)= xz.woggr
AND ss.subclass(+)= xz.wog
AND xz.wgr IS NULL
;
-- item season
select count(1) from PUC_SKU_ISE_EXCL_BRANDS_TMP; -- 1280109 = ( puc_sku_im_excl_brands_tmp - 29805)
sELECT * --only for cross join purpose
FROM Puc_Sku_Im_Excl_Brands_Tmp imo
,sap_slt_prod_pv1.mara m
WHERE m.mandt = imo.mandt
AND m.matnr = imo.item
AND m.saisj <= '2023';
select * from item_master_Ctrl where item = '000000128242421160';
-- item translation
select count(1) from PUC_SKU_IM_TL_EXCL_BRANDS_TMP;
-- item supplier
select count(1) from PUC_SKU_IS_EXCL_BRANDS_TMP; -- 2211157
select count(distinct item) from PUC_SKU_IS_EXCL_BRANDS_TMP; -- 1309904
select * from PUC_SKU_IS_EXCL_BRANDS_TMP; -- 1309904
SELECT *-- count(distinct im.item) --10
FROM puc_sku_im_excl_brands_tmp im,
PUC_SKU_IS_EXCL_BRANDS_TMP isp
where im.item = isp.item(+)
and isp.item is null;
SELECT distinct IME.ITEM, ei.lifnr--count(distinct IME.ITEM) -- count(distinct ei.lifnr)
FROM puc_sku_im_excl_brands_tmp ime
,sap_slt_prod_pv1.eina ei
,xref_sups xs
WHERE ei.mandt = ime.mandt
AND ei.matnr = ime.dispo
AND xs.sap_id (+) = ei.lifnr
AND XS.SAP_ID IS NULL
and not exists (select 1 from xref_sups eiv2 where eiv2.sap_id = ei.lifnr);
-- the suppliers that not exist in xref it is because they not exist in lfm1 table
select * from sap_slt_prod_p01.lfm1 where lifnr in (SELECT distinct ei.lifnr -- count(distinct ei.lifnr)
FROM puc_sku_im_excl_brands_tmp ime
,sap_slt_prod_pv1.eina ei
,xref_sups xs
WHERE ei.mandt = ime.mandt
AND ei.matnr = ime.dispo
AND xs.sap_id (+) = ei.lifnr
AND XS.SAP_ID IS NULL
and not exists (select 1 from xref_sups eiv2 where eiv2.sap_id = ei.lifnr));
-- item supplier country
select count(1) from PUC_SKU_ISC_EXCL_BRANDS_TMP; -- 345530
select * from PUC_SKU_ISC_EXCL_BRANDS_TMP ;
SELECT count(distinct im.item) --10
FROM puc_sku_is_excl_brands_tmp im,
PUC_SKU_ISc_EXCL_BRANDS_TMP isp
where im.item = isp.item(+)
and isp.item is null;
select * from puc_sku_im_excl_brands_tmp t where exists (select 1 from sap_slt_prod_pv1.MAW1 m where m.matnr = t.item and wekgr is null) ;
-- item supplier country dim
select count(1) from PUC_SKU_ISCD_EXCL_BRANDS_TMP; --345530
-- item supplier country manufatury
select count(1) from PUC_SKU_ISCM_EXCL_BRANDS_TMP; -- 345530
-------------------------------------------------------------------------
-- OWN BRANDS
-- PUC_SKU_OWN_BRANDS_TMP
SELECT COUNT(1) FRoM (
SELECT /*+ PARALLEL(16) */
DISTINCT m.mandt
,m.matnr
,DECODE(m.satnr, NULL, m.matnr, m.satnr) AS DISPO
,m.zzdispogrp AS STYLE
FROM sap_slt_prod_pv1.mara m
,sap_slt_prod_pv1.wrf_brands wb
WHERE m.attyp = '02'
AND wb.mandt = m.mandt
AND wb.brand_id = m.brand_id
AND wb.brand_type = '1'
AND EXISTS (SELECT 1
FROM puc_sku_variant_mseg_tmp tmp
WHERE tmp.mandt = m.mandt
AND tmp.matnr = m.matnr)); -- 181.411
--PUC_SKU_DIFFS_OWN_BRANDS_TMP
-- color and size
SELECT COUNT(1) FRoM (
SELECT /*+ PARALLEL(8) */
tmp.mandt
,tmp.matnr
,ap.atinn
,ap.atwrt
FROM puc_sku_own_brands_tmp tmp
,sap_slt_prod_pv1.inob ib
,sap_slt_prod_pv1.ausp ap
WHERE ib.mandt = tmp.mandt
AND ib.objek = tmp.matnr
AND ap.mandt = ib.mandt
AND ap.objek = ib.cuobj
AND ap.atinn IN ('0000000001', '0000000002')
); -- 362822 / 2 = 181.411
-- missing merch hier on xref_zsta_stw2
SELECT COUNT(1) FROM PUC_SKU_IM_OWN_BRANDS_TMP ; --180818 (-593)
SELECT MTART, ATTYP, SATNR, M.MATNR, SIZE1, ZZALTWARE,SAISO,SAISJ, BRAND_ID, zzwgr_wawi
FROM puc_sku_own_brands_tmp so,
sap_slt_prod_pv1.mara m
,sap_slt_prod_pv1.maw1 m1
,xref_zsta_stw2 xz
,subclass_stg ss
WHERE m.mandt = so.mandt
AND m.matnr = so.MATNR
AND m1.mandt = m.mandt
AND m1.matnr = m.matnr
AND xz.wgr(+) = m.zzwgr_wawi
AND ss.class (+)= xz.woggr
AND ss.subclass(+)= xz.wog
AND xz.wgr IS NULL
;
-- item season
select count(1) from PUC_SKU_ISE_OWN_BRANDS_TMP ; --171106 ( puc_sku_im_own_brands_tmp - 9712)
sELECT count(1) --only for cross join purpose
FROM puc_sku_im_own_brands_tmp imo
,sap_slt_prod_pv1.mara m
WHERE m.mandt = imo.mandt
AND m.matnr = imo.item
AND m.saisj < '2023';
-- item translation
select count(1) from PUC_SKU_IM_TL_OWN_BRANDS_TMP; -- 180818
-- item supplier
select count(1) from PUC_SKU_IS_OWN_BRANDS_TMP where ; -- 211568
select count(1) from PUC_SKU_IS_excl_BRANDS_TMP; -- 211568
SELECT count(distinct im.item)-- count(distinct ei.lifnr)
FROM puc_sku_im_own_brands_tmp im,
PUC_SKU_IS_OWN_BRANDS_TMP isp
where im.item = isp.item(+)
and isp.item is null;
--supplier in PR1 that not exist in xref (but exist in lfa1 - checked)
SELECT distinct eir.lifnr--count(distinct IME.ITEM) -- count(distinct eir.lifnr)
FROM puc_sku_im_own_brands_tmp imo
,sap_slt_prod_pv1.eina eiv
,sap_slt_prod_pr1.eina eir
,xref_sups xs
WHERE eiv.mandt = imo.mandt
AND eiv.matnr = imo.dispo
AND eir.mandt = eiv.mandt
AND eir.matnr = eiv.idnlf
--AND xs.sap_id (+) = eir.lifnr
--AND SAP_ID IS NULL
--and not exists (select 1 from xref_sups eiv2 where eiv2.sap_id = eir.lifnr);
--
AND xs.sap_id = eir.lifnr
and not exists (select 1 from sups_ctrl eiv2 where eiv2.supplier = xs.oracle_id);
-- the suppliers that not exist in xref it is because they not exist in lfm1 table
select * from sap_slt_prod_p01.lfm1 where lifnr in (SELECT distinct eir.lifnr
FROM puc_sku_im_own_brands_tmp imo
,sap_slt_prod_pv1.eina eiv
,sap_slt_prod_pr1.eina eir
,xref_sups xs
WHERE eiv.mandt = imo.mandt
AND eiv.matnr = imo.dispo
AND eir.mandt = eiv.mandt
AND eir.matnr = eiv.idnlf
AND xs.sap_id (+) = eir.lifnr
AND SAP_ID IS NULL
and not exists (select 1 from xref_sups eiv2 where eiv2.sap_id = eir.lifnr));
-- item supplier country
select count(1) from PUC_SKU_ISC_OWN_BRANDS_TMP; --211568
select count(1) from PUC_SKU_ISC_OWN_BRANDS_TMP where supplier is null; --29880
select * from PUC_SKU_ISC_OWN_BRANDS_TMP where origin_country_id is null; --211568
-- item supplier country dim
select count(1) from PUC_SKU_ISCD_OWN_BRANDS_TMP; --211568
select * from PUC_SKU_ISCD_OWN_BRANDS_TMP where weight is null or net_weight is null or weight_uom is null or tare_weight is null;
-- item supplier country manufatury
select count(1) from PUC_SKU_ISCM_OWN_BRANDS_TMP; --211568
select * from PUC_SKU_ISCM_OWN_BRANDS_TMP where manu_country_id is null
-----------------------
CREATE TABLE ITEM_MIGRATION_REPORT AS
with COLORS AS (
SELECT DIFF_ID, DIFF_DESC
FROM DIFF_IDS
WHERE DIFF_TYPE = 'C'
),
SIZES AS (
SELECT DIFF_ID, DIFF_DESC
FROM DIFF_IDS
WHERE DIFF_TYPE = 'S'
),
SKUS as (
select IM.ITEM AS SKU,
IM.ITEM_DESC AS SKU_DESC,
IM.ITEM_PARENT AS ITEM_STYLE_DISPOGRP,
COLORS.DIFF_ID AS COLOR_ID,
COLORS.DIFF_DESC AS COLOR_DESC,
SIZES.DIFF_ID AS SIZE_ID,
SIZES.DIFF_DESC AS SIZE_DESC,
(select bc.item
from item_master bc
where item_level > tran_level
and bc.PRIMARY_REF_ITEM_IND = 'Y'
and bc.item_parent = im.item) as PRIMARY_BARCODE,
IM.DEPT AS DEPT_ID_RESSORT,
(select DEPT_NAME
from deps
where im.dept = deps.dept) as DEPT_NAME,
IM.CLASS AS CLASS_ID_WOGGR,
(select CLASS_NAME
from class
where im.dept = class.dept
and im.class = class.class) as CLASS_NAME,
IM.SUBCLASS AS SUBCLASS_ID_WOG,
(select SUB_NAME
from subclass
where im.dept = subclass.dept
and im.class = subclass.class
and im.subclass = subclass.subclass) as SUBCLASS_NAME,
DECODE(LENGTH(BRAND_NAME),4,'N',3,'Y','-') AS OWN_BRAND_IND,
IM.BRAND_NAME AS BRAND_ID,
(select BRAND_DESCRIPTION
from brand
where brand.branD_name = im.brand_name) as BRAND_NAME,
SELLABLE_IND,
INVENTORY_IND,
ORDERABLE_IND
from item_master im
LEFT JOIN colors ON diff_1 = colors.diff_id
LEFT JOIN sizes ON diff_2 = sizes.diff_id
where item_level = tran_level
),
SKU_SUPP AS (
SELECT SKUS.*,
ITEM_SUPP.PRIMARY_SUPPLIER,
ITEM_SUPP.PRIMARY_SUPPLIER_NAME,
ITEM_SUPP.VPN,
ITEM_SUPP.COUNTRY_OF_SOURCING,
ITEM_SUPP.UNIT_COST,
ITEM_SUPP.LENGTH,
ITEM_SUPP.WIDTH,
ITEM_SUPP.HEIGHT,
ITEM_SUPP.LWH_UOM,
ITEM_SUPP.WEIGHT,
ITEM_SUPP.NET_WEIGHT,
ITEM_SUPP.WEIGHT_UOM,
ITEM_SUPP.COUNTRY_OF_MANUFACTURING
FROM SKUS
LEFT JOIN (SELECT ISUP.ITEM AS ITEM_SUP,
ISUP.SUPPLIER AS PRIMARY_SUPPLIER,
SUPS.SUP_NAME AS PRIMARY_SUPPLIER_NAME,
ISUP.VPN,
ISC2.*,
ISMC.MANU_COUNTRY_ID AS COUNTRY_OF_MANUFACTURING
FROM ITEM_SUPPLIER ISUP
INNER JOIN SUPS ON SUPS.SUPPLIER = ISUP.SUPPLIER
LEFT JOIN (SELECT ISC.ITEM,
ISC.SUPPLIER,
ISC.ORIGIN_COUNTRY_ID AS COUNTRY_OF_SOURCING,
ISC.UNIT_COST,
LENGTH,
WIDTH,
HEIGHT,
LWH_UOM,
WEIGHT,
NET_WEIGHT,
WEIGHT_UOM
FROM ITEM_SUPP_COUNTRY ISC
LEFT JOIN ITEM_SUPP_COUNTRY_DIM ISCD ON ISC.ITEM = ISCD.ITEM AND ISC.SUPPLIER = ISCD.SUPPLIER AND ISC.ORIGIN_COUNTRY_ID = ISCD.ORIGIN_COUNTRY
WHERE ISC.PRIMARY_COUNTRY_IND = 'Y') ISC2 ON ISUP.SUPPLIER = ISC2.SUPPLIER AND ISUP.ITEM = ISC2.ITEM
LEFT JOIN ITEM_SUPP_MANU_COUNTRY ISMC ON ISUP.SUPPLIER = ISMC.SUPPLIER AND ISUP.ITEM = ISMC.ITEM AND ISMC.PRIMARY_MANU_CTRY_IND = 'Y'
WHERE ISUP.PRIMARY_SUPP_IND = 'Y'
) ITEM_SUPP ON SKUS.SKU = ITEM_SUPP.ITEM_SUP
),
SKU_SUPP_SEASON AS (
SELECT SS.*,
ISE2.SEASON_ID,
ISE2.SEASON_DESC,
ISE2.SEASON_CATEGORY_SAP,
--ISE2.PHASE_ID,
ISE2.YEAR
FROM SKU_SUPP SS
LEFT JOIN (SELECT ITEM,
ISE.SEASON_ID,
SEASON_DESC,
PHASE_ID,
DECODE(SUBSTR(S.SEASON_ID,0,1),'1','SUMMER','2','WINTER','3','ALL YEAR','?') SEASON_CATEGORY_SAP,
DECODE(SUBSTR(S.SEASON_ID,2,3),'99','9999','20'|| SUBSTR(S.SEASON_ID,2,3)) YEAR
FROM ITEM_SEASONS ISE,
SEASONS S
WHERE ISE.SEASON_ID = S.SEASON_ID) ISE2
ON SS.SKU = ISE2.ITEM
)
select sss.*
/*(SELECT LISTAGG(il.LOC || '-' || il.status, ';')
WITHIN GROUP (ORDER BY il.LOC)
FROM ITEM_LOC il
WHERE il.ITEM = SSS.SKU) AS LOCATIONS*/
from SKU_SUPP_SEASON SSS;
--select count(1) from (
select DISTINCT ITEM, dispo, upc from puc_item_winner_xref xr
where exists (select 1 from puc_item_winner_xref xr2
where xr2.item <> xr.item
and xr2.upc = xr.upc
and xr2.vpn <> xr.vpn
and xr2.upc <> ' '
and xr.item_winner_ind = 'Y'
and xr2.item_winner_ind = 'Y') order by upc--)
;
select * from puc_item_winner_xref where upc in ('192569493819','190375463231','190375612394','190864487595') and item_winner_ind = 'Y';
select distinct item from puc_item_winner_xref where vpn = ' ' group by length(brand_id) ;
select count(distinct item) from puc_item_winner_xref where;
select x.*, exc.dept
from puc_item_winner_xref x
join PUC_SKU_IM_EXCL_BRANDS_TMP exc on exc.item = x.item and exc.DEPT IN (100, 101, 103)
union all
select x.*, own.dept
from puc_item_winner_xref x
join PUC_SKU_IM_OWN_BRANDS_TMP own on own.item = x.item and own.DEPT IN (100, 101, 103);
-- xref de tudo
-- items sem vpns 3rd party brands -- 52
-- items sem barcodes -- 306971
-- barcodes duplicados em mais de um SKU -- 29155
-- vpn similares mas nao iguais logo nao ficam agrupados
select * from puc_item_winner_xref where dispo like '%1588618';
select * from puc_item_winner_xref where diff_1 = '036' and diff_2 = '00420' and zzprodukt_wawi = '0000290' and vpn like '311%';
select * from puc_item_winner_xref where upc = '194904333620'
select * from sap_slt_prod_pv1.MARD where werks in () order by matnr;
select count(1), CTRL_STATUS, from ITEM_SUPPLIER_ctrl group by CTRL_STATUS;