Source: DMF/PuC/MFCS/ItemSizeGroups.sql
-----------
-- Size Groups
select
--m.satnr, m.matnr, m.ZZPRODUKT_WAWI, m.size1 as sizeMara, m.bismt, spl.*
--count(1)
--distinct
--m.matnr,
m.ZZPRODUKT_WAWI as product,
m.size1 as msize1,
--m.bismt,
--size2.atwrt as item_size,
k01715_size_desc,
k01710_size,
count(1)
--k01710_size_desc
from
sap_slt_prod_pv1.mara m
left join (
SELECT
k.kennung k01715,
k.ident k01715_ident,
kx.description as k01715_size_desc
--k.ident, kx.description, count(1)
FROM
sap_slt_prod_pv1.ZSTA_KAT k,
sap_slt_prod_pv1.ZSTA_KTX kx
WHERE
1 = 1
AND k.kennung = '01715'
AND kx.mandt = k.mandt
AND kx.kennung = k.kennung
AND kx.ident = k.ident
AND kx.status = k.status
AND k.status = 2
AND kx.langu = 'D'
) sizeK on sizeK.k01715_ident = LPAD(m.size1, 7, '0')
left join (
SELECT
k.ident as k01710_size,
kx.description as k01710_size_desc,
k.hier_ident as k01710_product
FROM
sap_slt_prod_pv1.ZSTA_KAT k,
sap_slt_prod_pv1.ZSTA_KTX kx
WHERE
1 = 1
AND k.kennung = '01710'
AND K.HIERKNOTENTYP = 'P3720'
-- product level
AND kx.mandt = k.mandt
AND kx.kennung = k.kennung
AND kx.HIERKNOTENTYP = k.HIERKNOTENTYP
AND kx.hier_ident = k.hier_ident
AND kx.ident = k.ident
AND kx.status = k.status
AND k.status = 2
AND kx.langu = 'D'
) spl on spl.k01710_product = m.ZZPRODUKT_WAWI
and spl.k01710_size = LPAD(m.size1, 7, '0')
/*left join (
SELECT
ap.atwrt,
ib.mandt,
ib.objek
FROM
sap_slt_prod_pv1.inob ib,
sap_slt_prod_pv1.ausp ap
WHERE
1 = 1
AND ap.mandt = ib.mandt
AND ap.objek = ib.cuobj
AND ap.atinn IN ('0000000002')
) size2 on size2.mandt = m.mandt
AND size2.objek = m.matnr */
where
1 = 1
and ATTYP = '02'
and MTART = 'ZMOD'
--and k01710_size is null
--and spl.size1 is null
--and ZZPRODUKT_WAWI = '0000155' and size2.atwrt = '00010'
--and (size_desc = ' ' or size_desc is null)
/*and exists (
select 1 from PUC_SKU_VARIANT_FILTER_TMP t where t.matnr = m.matnr
)*/
--and m.satnr = '000000000002091726'
--and m.satnr = '000000000009972488'
group by m.ZZPRODUKT_WAWI,
m.size1,
k01715_size_desc,
k01710_size
order by m.ZZPRODUKT_WAWI, m.size1
;
select k01715_ident, k01715_size_desc, k01710_product, k01710_size_group, m.ZZPRODUKT_WAWI as MARA_PRODUCT, count(m.matnr)--, count(m2.matnr)
from
(
SELECT
k.kennung k01715,
k.ident k01715_ident,
kx.description as k01715_size_desc
--k.ident, kx.description, count(1)
FROM
sap_slt_prod_pv1.ZSTA_KAT k,
sap_slt_prod_pv1.ZSTA_KTX kx
WHERE
1 = 1
AND k.kennung = '01715'
AND kx.mandt = k.mandt
AND kx.kennung = k.kennung
AND kx.ident = k.ident
AND kx.status = k.status
AND k.status = 2
AND kx.langu = 'D'
) sizeK
left join (
SELECT
k.ident as k01710_size,
kx.description as k01710_size_desc,
k.hier_ident as k01710_product ,
k.OGR_IDENT as k01710_size_group
FROM
sap_slt_prod_pv1.ZSTA_KAT k,
sap_slt_prod_pv1.ZSTA_KTX kx
WHERE
1 = 1
AND k.kennung = '01710'
AND K.HIERKNOTENTYP = 'P3720'
-- product level
AND kx.mandt = k.mandt
AND kx.kennung = k.kennung
AND kx.HIERKNOTENTYP = k.HIERKNOTENTYP
AND kx.hier_ident = k.hier_ident
AND kx.ident = k.ident
AND kx.status = k.status
AND k.status = 2
AND kx.langu = 'D'
) spl on sizeK.k01715_ident = spl.k01710_size
left join sap_slt_prod_pv1.mara m on LPAD(m.size1, 7, '0') = k01715_ident and m.ZZPRODUKT_WAWI = k01710_product AND exists ( Select 1 from PUC_SKU_VARIANT_FILTER_TMP t where t.matnr = m.matnr)
--left join sap_slt_prod_pv1.mara m2 on LPAD(m2.size1, 7, '0') = k01715_ident
where 1=1
--and k01710_product is null
--and k01715_ident = '0000210'
group by k01715_ident, k01715_size_desc, k01710_product, k01710_size_group, m.ZZPRODUKT_WAWI
order by --k01710_product,
sizeK.k01715_ident
;
select * from sap_slt_prod_pv1.mara m where size1 = '00010' and ZZPRODUKT_WAWI in ('0000160','0000175');
SELECT k.kennung, k.ident, funktcode, kx.langu, kx.description, k.*, kx.*
--k.ident, kx.description, count(1)
FROM sap_slt_prod_pv1.ZSTA_KAT k,
sap_slt_prod_pv1.ZSTA_KTX kx
WHERE 1=1
AND k.kennung = '01715'
AND kx.mandt = k.mandt
AND kx.kennung = k.kennung
AND kx.ident = k.ident
AND kx.status = k.status
AND k.status = 2
AND kx.langu = 'D'
--AND KX.IDENT IN ('0000500','0000520','0000540') -- necessario fazer to number or ltrim
--AND KX.IDENT IN ('0010050','0010060','0010020','0010030','0010040') -- necessario fazer to number or ltrim
AND KX.IDENT IN ('0000010') -- necessario fazer to number or ltrim
--group by k.ident, kx.description having count(1) > 1
;
SELECT --kx.kennung, k.HIERKNOTENTYP, funktcode, kx.langu, k.ident as size1, kx.description as size_desc, k.hier_ident as product, k.OGR_IDENT as size_group--, kx.*
--distinct k.OGR_IDENT
--length(k.ident)
length(to_number(k.HIER_IDENT))
FROM sap_slt_prod_pv1.ZSTA_KAT k,
sap_slt_prod_pv1.ZSTA_KTX kx
WHERE 1=1
AND k.kennung = '01710'
AND K.HIERKNOTENTYP = 'P3720' -- product level
AND kx.mandt = k.mandt
AND kx.kennung = k.kennung
AND kx.HIERKNOTENTYP = k.HIERKNOTENTYP
AND kx.hier_ident = k.hier_ident
AND kx.ident = k.ident
AND kx.status = k.status
--AND k.status = 2
--AND kx.langu = 'D'
--and k.HIER_IDENT in ('0000155')
--and kx.description IN ('L') --
--and k.ident IN ('0000010')
--and k.ident IN ('0010050','0010060','0010020','0010030','0010040')
--and k.ident IN ('0000500','0000520','0000540')
--order by k.HIER_IDENT
group by length(to_number(k.HIER_IDENT))
;
SELECT k.*, kx.* -- no description for size groups
--count(1)
FROM sap_slt_prod_pv1.ZSTA_KAT k,
sap_slt_prod_pv1.ZSTA_KTX kx
WHERE 1=1
AND k.kennung = '01700'
AND K.HIERKNOTENTYP = 'P3720' -- product level
AND kx.mandt = k.mandt
AND kx.kennung = k.kennung
AND kx.HIERKNOTENTYP = k.HIERKNOTENTYP
AND kx.hier_ident = k.hier_ident
AND kx.ident = k.ident
AND kx.status = k.status
AND k.status = 2
AND kx.langu = 'D'
--and k.HIER_IDENT = '0000100'
--and k.ident IN ('0000001')
order by k.ident
;
SELECT --kx.kennung, funktcode, kx.langu, k.ident as product, k.OGR_IDENT as product_group, kx.description as product_desc, kx.description_long as product_long_desc
--count(1)
k.*, kx.*
FROM sap_slt_prod_pv1.ZSTA_KAT k,
sap_slt_prod_pv1.ZSTA_KTX kx
WHERE 1=1
AND k.kennung = '03720' -- product list
--AND K.HIERKNOTENTYP = 'P3720'
AND kx.mandt = k.mandt
AND kx.kennung = k.kennung
AND kx.HIERKNOTENTYP = k.HIERKNOTENTYP
AND kx.hier_ident = k.hier_ident
AND kx.ident = k.ident
AND kx.status = k.status
AND k.status = 2
AND kx.langu = 'D'
--and k.OGR_IDENT = '0000100' -- product group
--and k.ident = '0000100' -- product
order by k.HIER_IDENT
;
SELECT kx.kennung, funktcode, kx.langu, k.OGR_IDENT as confection, k.ident as product_group, kx.description as product_group_desc, kx.description_long as product_group_long_desc--, k.*
--count(1)
FROM sap_slt_prod_pv1.ZSTA_KAT k,
sap_slt_prod_pv1.ZSTA_KTX kx
WHERE 1=1
AND k.kennung = '03725' -- product group list
--AND K.HIERKNOTENTYP = 'P3720'
AND kx.mandt = k.mandt
AND kx.kennung = k.kennung
AND kx.HIERKNOTENTYP = k.HIERKNOTENTYP
AND kx.hier_ident = k.hier_ident
AND kx.ident = k.ident
AND kx.status = k.status
AND k.status = 2
AND kx.langu = 'D'
--and k.ident = '0000145' -- product group id
order by k.HIER_IDENT
;
-- just informative
SELECT kx.kennung, funktcode, kx.langu, k.OGR_KENNUNG, k.ident as product_group, kx.description as product_desc, kx.description_long as product_long_desc
--count(1)
FROM sap_slt_prod_pv1.ZSTA_KAT k,
sap_slt_prod_pv1.ZSTA_KTX kx
WHERE 1=1
AND k.kennung = '03663' -- confection
--AND K.HIERKNOTENTYP = 'P3720'
AND kx.mandt = k.mandt
AND kx.kennung = k.kennung
AND kx.HIERKNOTENTYP = k.HIERKNOTENTYP
AND kx.hier_ident = k.hier_ident
AND kx.ident = k.ident
AND kx.status = k.status
AND k.status = 2
AND kx.langu = 'D'
and k.ident = '0000003' -- confection id
order by k.HIER_IDENT
;
select * from sap_slt_prod_pv1.ZSTA_KSD;
----
select zzdispogrp, ZZPRODUKT_WAWI, count(1) from (
select distinct zzdispogrp, ZZPRODUKT_WAWI
from sap_slt_prod_pv1.mara m
where exists (select 1 from PUC_SKU_VARIANT_FILTER_TMP t where t.matnr = m.matnr))
group by zzdispogrp, ZZPRODUKT_WAWI having count(1) > 1;
select distinct ZZPRODUKT_WAWI, size1, bismt
from sap_slt_prod_pv1.mara m
where exists (select 1 from PUC_SKU_VARIANT_FILTER_TMP t where t.matnr = m.matnr) order by ZZPRODUKT_WAWI, size1;
select * from ORI_PRICE_FIRST_RAW