Source: DMF/PuC/AIF/ItemUDA.sql
-- UDA63 --> if NOS, if best price else seasonable no MFCS
select SAISO,
ZZTEILSAISONKZ /*seasonable best price = 0000002, black price = 0000001*/,
ZZSTAMMKZ /* nos X = yes */, COUNT(1) FROM sap_slt_prod_pv1.mara where MTART = 'ZMOD'
group by SAISO, ZZTEILSAISONKZ, ZZSTAMMKZ;
-- UDA 56
SELECT zzwgr_wawi , M.* FROM sap_slt_prod_pv1.mara m where exists (select 1 from dmf2.PUC_SKU_VARIANT_FILTER_TMP f where f.matnr = m.matnr) and zzwgr_wawi like '%911%';
SELECT DISTINCT im.matnr,
56 AS uda_id,
TO_NUMBER(m.zzwgr_wawi) AS uda_value,
'I' AS ctrl_status,
kx.*
FROM sap_slt_prod_pv1.mara m,
dmf2.PUC_SKU_VARIANT_FILTER_TMP im,
--skus im,
sap_slt_prod_pv1.zsta_stw k,
sap_slt_prod_pv1.zsta_txt kx
WHERE m.matnr = im.matnr
AND k.kennung = '01070'
AND LTRIM(k.ident,'0') = LTRIM(m.zzwgr_wawi,'0')
AND kx.mandt = k.mandt
AND kx.kennung = k.kennung
AND kx.ident = k.ident
AND kx.status = 2
AND kx.funktcode <> 'SP'
AND kx.langu = 'D';
SELECT DISTINCT kx.*
FROM sap_slt_prod_pv1.zsta_stw k,
sap_slt_prod_pv1.zsta_txt kx
WHERE 1=1
AND k.kennung = '01070'
AND kx.mandt = k.mandt
AND kx.kennung = k.kennung
AND kx.ident = k.ident
AND kx.status = 2
AND kx.funktcode <> 'SP'
AND kx.langu = 'D'
AND KX.IDENT IN ('0000102','0000911','0009119');
----------------------
-- UDA 4
SELECT * FROM item_seasons_ctrl i where item = '000000197600902934';
SELECT saiso,saisj , M.* FROM sap_slt_prod_pv1.mara m where matnr = '000000197600902934';
SELECT DISTINCT im.matnr,
4 AS uda_id,
TO_NUMBER(m.saiso) AS uda_value,
'I' AS ctrl_status
FROM sap_slt_prod_pv1.mara m,
dmf2.PUC_SKU_VARIANT_FILTER_TMP im,
--skus im,
sap_slt_prod_pv1.zsta_kat k,
sap_slt_prod_pv1.zsta_ktx kx
WHERE m.matnr = im.matnr
AND k.kennung = '01800'
AND LTRIM(k.ident,'0') = LTRIM(m.saiso,'0')
AND k.status = 2
AND k.funktcode <> 'SP'
AND kx.mandt = k.mandt
AND kx.kennung = k.kennung
AND kx.ident = k.ident
AND kx.status = k.status
AND kx.langu = 'D'
AND M.matnr = '000000197600902934';
/*Shape
UDA ID 79*/
SELECT DISTINCT --im.item,
79 AS uda_id,
--xr.oracle_value AS uda_value,
'I' AS ctrl_status,
m.zzform,
--k.*,
k.funktcode,
kx.*
FROM sap_slt_prod_pv1.mara m,
--skus im,
item_master_ctrl im,
sap_slt_prod_pv1.zsta_kat k,
sap_slt_prod_pv1.zsta_ktx kx--,
--xref_item_attributes xr
WHERE 1=1
and m.matnr = im.item
AND k.kennung = '01410'
AND LTRIM(k.ident,'0') = LTRIM(m.zzform,'0')
AND k.status = 2
AND k.funktcode <> 'SP'
AND k.hierknotentyp = 'P3720'
AND k.hier_ident = m.zzprodukt_wawi
AND kx.mandt = k.mandt
AND kx.kennung = k.kennung
AND kx.ident = k.ident
AND kx.hierknotentyp = k.hierknotentyp
AND kx.hier_ident = k.hier_ident
AND kx.status = k.status
AND kx.langu = 'D'
--AND xr.attrib_id = '79'
--AND xr.sap_value_1 = LTRIM(m.zzprodukt_wawi,'0')
--AND xr.sap_value_2 = LTRIM(m.zzform,'0')
AND DESCRIPTION_LONG LIKE '%SWEATSHI%'
order by DESCRIPTION,DESCRIPTION_LONG
;
SELECT * FROM xref_item_attributes WHERE ORACLE_DESC LIKE '%SWEATSHI%'
--------------------
SELECT /*+ PARALLEL(16) */
m.MTART
,m.matnr
,DECODE(m.satnr, NULL, m.matnr, m.satnr) AS DISPO
,m.zzdispogrp AS STYLE
,decode(m.ZZALTWARE,'1','A','C') AS status
,m.ERSDA as create_Date
,m.BRAND_ID
,BRAND_TYPE
,(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 SIZE1
, e.lifnr as supplier
, l.name1 as supplier_name
, e.IDNLF as vpn
, l.land1 as supplier_country_id
,marm.LAENG as LENGTH
,marm.BREIT as WIDTH
,marm.HOEHE as HEIGHT
,marm.MEABM as HEIGHT
,marm.BRGEW as WEIGHT
,marm.BRGEW - 0.1 as NET_WEIGHT
,marm.GEWEI as WEIGHT_UOM
FROM sap_slt_prod_pv1.mara m
left join sap_slt_prod_pv1.marm marm on marm.matnr = m.satnr
,sap_slt_prod_pv1.wrf_brands wb
,sap_slt_prod_pv1.eina e
,sap_slt_prod_pv1.lfa1 l
WHERE m.attyp = '02'
and e.matnr = m.satnr
--and e.LIFNR = '0000940506'
and e.lifnr = l.lifnr
AND wb.mandt = m.mandt
AND wb.brand_id = m.brand_id
AND m.MATNR = '000000091143700460';
/*
brand_type = 1 (own brand) and 2 (exclusive brand)
Brand id = 4 digits exclsives and 3 own brands
*/
select * from sap_slt_prod_pv1.marm where matnr = '000000154530110030';
-- UDA 11 and 12
SELECT DISTINCT im.item,
11 AS uda_id,
TO_NUMBER(m.zzkollektionskz) AS uda_value,
'I' AS ctrl_status
FROM sap_slt_prod_pv1.mara m,
sap_slt_prod_pv1.zsta_kat k,
sap_slt_prod_pv1.zsta_ktx kx,
item_master_ctrl im
WHERE m.matnr = im.item
AND k.kennung = '01580'
AND LTRIM(k.ident,'0') = LTRIM(m.zzkollektionskz,'0')
AND k.status = 2
AND k.funktcode <> 'SP'
AND kx.mandt = k.mandt
AND kx.kennung = k.kennung
AND kx.ident = k.ident
AND kx.status = k.status
AND kx.langu = 'D'
and m.matnr = '000000102420010030';
select * from sap_slt_prod_pv1.zsta_kat k where k.kennung = '01580' and ident = '0000001'
select * from sap_slt_prod_pv1.zsta_kat k where k.kennung = '01420' and ident = '0000001'
SELECT DISTINCT im.item,
12 AS uda_id,
xi.oracle_value AS uda_text,
'I' AS ctrl_status
FROM sap_slt_prod_pv1.mara m,
sap_slt_prod_pv1.zsta_kat k,
sap_slt_prod_pv1.zsta_ktx kx,
skus im,
xref_item_attributes xi
WHERE m.matnr = im.item
AND k.kennung = '01580'
AND LTRIM(k.ident,'0') = LTRIM(m.zzkollektionskz,'0')
AND k.status = 2
AND k.funktcode <> 'SP'
AND kx.mandt = k.mandt
AND kx.kennung = k.kennung
AND kx.ident = k.ident
AND kx.status = k.status
AND kx.langu = 'D'
AND xi.attrib_id = '12'
AND xi.sap_value_1 = LTRIM(m.zzkollektionskz,'0');