Source: DMF/PuC/AIF/Item.sql
select * from item_master_ctrl where ctrl_status = 'E';
SELECT * FROM MIGR_RULES WHERE RULE_ID = 2725;
select * from sups_ctrl
-- Brand id = 4 digits exclsives and 3 own brands
select * from PUC_SKU_ISC_excl_BRANDS_TMP xr
where exists (select 1 from PUC_SKU_ISC_excl_BRANDS_TMP xr2
where xr2.item = xr.item
and xr2.supplier <> xr.supplier
and xr2.unit_cost <> xr.unit_cost) order by item--)
;
select sup_name, i.* from PUC_SKU_ISC_excl_BRANDS_TMP i, sups_ctrl c where i.supplier = c.supplier and item = '000000043633600240';
select sup_name, i.* from PUC_SKU_ISC_excl_BRANDS_TMP i, sups_ctrl c where i.supplier = c.supplier and item = '000000146927110030';
select * from addr_ctrl where key_value_1 in (236322,226394,222135,201561,107697,237594) and module = 'SUPP';
SELECT m.ean11
FROM sap_slt_prod_pv1.mara m,
sap_slt_prod_pv1.eina e
where e.matnr = m.satnr
and m.matnr = '000000197128300400';
SELECT *
FROM sap_slt_prod_pv1.mara m,
sap_slt_prod_pr1.eina e
where e.matnr = m.satnr
and matnr = ;
select * from excl_BRAND_ITEM_SUPPLIER where item = 000000146927110030;
select * from sap_slt_prod_pv1.WRF_BRANDS_T where brand_id = '0088';
-- total SKUs -- 1.511.637
select
count(1)
from
(
SELECT
/*+ PARALLEL(16) */
DISTINCT ms.mandt,
ms.matnr
FROM
sap_slt_prod_pv1.mseg ms,
sap_slt_prod_pv1.mara m
WHERE 1=1
AND 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'
);
-------------------
select ATTYP, SATNR, MATNR, DIFF_1 AS COLOR, SIZE1, ZZALTWARE,SAISO,SAISJ, BRAND_ID, M.* from sap_slt_prod_pV1.mara m, ITEM_MASTER_CTRL C where C.ITEM = MATNR AND ZZDISPOGRP = '007100078' --SATNR = '000000000001072168'
select ATTYP, SATNR, MATNR, COLOR, SIZE1 from sap_slt_prod_pr1.mara m where SATNR = '000000000144507646' and color = 632 and size1 = 10040
select SATNR, m.* from sap_slt_prod_pv1.mara m where matnr = '000000000000169586';
select * from sap_slt_prod_pv1.eina m where matnr = '000000000000169586';
select * FROM sap_slt_prod_pv1.A986 where kschl = 'ZGFA'; -- 1) items, -- a partir dos ekorgs listar lojas e criar upcharge do DEFAULT_WH (--> LOJAS
select * FROM sap_slt_prod_pv1.A986 where kschl = 'ZEWH'; -- filtar pelo ekorg = org unit da loja
select * FROM sap_slt_prod_pv1.A986 where kschl = 'ZGWH'; -- filtar pelo ekorg = org unit da loja
select * FROM sap_slt_prod_pv1.A814 where kschl = 'ZZK1'; -- ENTENDER COMO EXPLODIR A PARTIR DOS PAISES PARA OS LOCAIS -- SEMPRE %
select * FROM sap_slt_prod_pv1.A819 where kschl = 'ZZK1'; -- ENTENDER COMO EXPLODIR A PARTIR DOS PAISES PARA OS LOCAIS -- SEMPRE %
select * FROM sap_slt_prod_pv1.konp where kschl = 'ZZK1'; -- SEMPRE %
select * FROM sap_slt_prod_pv1.A950 a, sap_slt_prod_pv1.konp k
where a.kschl = 'ZZK1' and k.knumh = a.knumh and k.kschl = a.kschl and k.lifnr = a.lifnr and a.matnr = '000000000001508074' and datbi = '99991231';
select * FROM sap_slt_prod_pv1.konp where kschl = 'ZEBE' and knumh = '0209089219' -- 0.85
SELECT a.matnr, a.LIFNR, k.kbetr, a.ekorg, a.knumh
FROM sap_slt_prod_pv1.a018 a,
sap_slt_prod_pv1.konp k
WHERE 1=1
AND a.kschl = 'YPBE' --Item Initial price PO Maybe ZBRP look chat Jochen
AND a.datbi = '99991231'
AND k.knumh = a.knumh
AND k.mandt = a.mandt
and a.matnr = '000000000001508074'
;
select * -- MATNR, oracle_id, case when r = 1 then 'Y' else 'N' end primary_supp_ind, case when r = 1 or lifnr = sap_merge_id then idnlf else null end vpn
from (
select ATTYP, SATNR, m.MATNR, ZZALTWARE, ZZDISPOGRP, SAISO,SAISJ, BRAND_ID, ersda, e.lifnr, idnlf, relif, oracle_id, sap_merge_id, sap_org_unit, l.land1,
RANK() OVER (
PARTITION BY m.MATNR
ORDER BY relif desc,sap_org_unit DESC
) AS r
from sap_slt_prod_pv1.mara m,
sap_slt_prod_pv1.eina e,
sap_slt_prod_pv1.lfa1 l,
xref_sups x
where m.matnr = '000000203411510060'
and m.satnr = e.matnr
and x.sap_id = e.lifnr
and l.lifnr = e.lifnr
) --where r=1
;
select * from sap_slt_prod_pv1.mara where ZZDISPOGRP = '007577294';
000000202262310050
000000194132710040
000000206779410030
select * from xref_sups where sap_id in (
'0000190616',
'0000237558');
select m.matnr, e.lifnr, land1, WHERL
from sap_slt_prod_pv1.mara m
,sap_slt_prod_pv1.eina e
,sap_slt_prod_pv1.lfa1 l
,sap_slt_prod_pv1.maw1 ma
where m.matnr = e.matnr
and l.lifnr = e.lifnr
and ma.matnr = m.matnr
and land1 <> WHERL
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 * FROM sap_slt_prod_pv1.MARM WHERE UMREZ = 1 AND MEINH = 'PAA';-- MATNR = '000000190969200370'
select ZZPRODUKT_WAWI, zzlfaufma, m.* from sap_slt_prod_pv1.MAra m;
select brand_id from sap_slt_prod_pv1.mara where matnr in ('000000500180910020', '000000202432710020');
select * from sap_slt_prod_pv1.eina where matnr in ('000000000002024327','000000000005001809');
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 DIFF_1_SAP = '632'
and DIFF_2_SAP = '03332';
select * from puc_item_winner_xref where item = '000000126313701020';
select e.matnr, e.lifnr, LAND1 from sap_slt_prod_pr1.eina e,
sap_slt_prod_pr1.mara m,
sap_slt_prod_pr1.LFA1 L
where
m.satnr = e.matnr
AND L.LIFNR = E.LIFNR
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))
group by e.matnr, e.lifnr, LAND1 having count(1) > 1
order by e.matnr, e.lifnr, LAND1;
select m.attyp, e.* from sap_slt_prod_pr1.eina e,
sap_slt_prod_pr1.mara m
where m.satnr = e.matnr and e.matnr = '000000813617810040';
select m.attyp, m.* from sap_slt_prod_pr1.mara m where matnr = '000000000140309495';
SELECT * FROM sap_slt_prod_pv1.ZSTA_KTX where kennung = '03560' and status = 2; -- 2 production data
select * from
select * from PUC_SKU_IM_EXCL_BRANDS_TMP;
select matnr, zzprodukt_wawi as product, brand_id as brand, ZZWGR_WAWI as merch_hier from sap_slt_prod_pv1.mara where matnr = '000000095040900480';
select matnr, zzprodukt_wawi as product, brand_id as brand, ZZWGR_WAWI as merch_hier from sap_slt_prod_pv1.mara where zzprodukt_wawi = '0000105';
select count(distinct item) from ( -- 398 items faltando
select i.item,
xi.item_pr1,
xi.item_pv1,
x.oracle_id, -- if null invalid location
i.day_dt,
i.CLEARANCE_FLG,
sum(INV_SOH_QTY) as INV_SOH_QTY,
sum(INV_SOH_COST_AMT_LCL) as INV_SOH_COST_AMT_LCL,
sum(INV_SOH_RTL_AMT_LCL) as INV_SOH_RTL_AMT_LCL,
sum(INV_UNIT_RTL_AMT_LCL) as INV_UNIT_RTL_AMT_LCL,
sum(INV_AVG_COST_AMT_LCL) as INV_AVG_COST_AMT_LCL,
i.doc_curr_code,
i.loc_curr_code,
ctrl_status
from ori_inventory_raw i
left join xref_location x on x.sap_id = i.org_num and x.oracle_type = 'S'
left join XREF_ITEM_IBC_RETAIL xi on xi.item_pr1 = i.item
left join item_master_ctrl imc on imc.item = nvl(i.item,xi.item_pv1)
where to_date(i.day_dt) > to_date('05_JAN-2025')
group by i.item, item_pr1, item_pv1, oracle_id, day_dt, CLEARANCE_FLG, doc_curr_code, loc_curr_code, ctrl_status
) where (ctrl_status is null or ctrl_status = 'E') and oracle_id is not null
;
select ATTYP, SATNR, MATNR, ZZALTWARE,SAISO,SAISJ, BRAND_ID, M.*
from sap_slt_prod_pV1.mara m
where matnr in ('000000900363300540','000000179204510040','000000922258003636','000000181553610050');
SELECT *
FROM sap_slt_prod_pv1.mbew ms
WHERE lbkum > 0
and matnr in ('000000900363300540','000000179204510040','000000922258003636','000000181553610050');
SELECT *
FROM sap_slt_prod_pv1.mseg ms
WHERE matnr in ('000000900363300540','000000179204510040','000000922258003636','000000181553610050');
SELECT * FROM sap_slt_prod_pv1.makt ma, sap_slt_prod_pv1.mara m where ma.matnr = m.matnr and m.MTART = 'ZDIE' and spras <> 'D';
select * from sap_slt_prod_pv1.a073 where matnr in (SELECT matnr FROM sap_slt_prod_pv1.mara m where m.MTART = 'ZDIE');
select distinct z.item, standard_retail, zone_id from dmf2.rpm_item_zone_price_ctrl z, dmf2.item_master_ctrl c where c.ctrl_status = 'C' and c.item = z.item and c.item_level = c.tran_level and c.item_level = 1;
SET SERVEROUTPUT ON SIZE UNLIMITED;
DECLARE
l_sql CLOB;
BEGIN
FOR r IN (
select distinct z.item, standard_retail, zone_id from dmf2.rpm_item_zone_price_ctrl z, dmf2.item_master_ctrl c where c.ctrl_status = 'C' and c.item = z.item and c.item_level = c.tran_level and c.item_level = 1;
)
LOOP
l_sql :=
'UPDATE item_loc '
|| 'SET unit_retail = ' || NVL(TO_CHAR(r.standard_retail), 'NULL')
|| ', regular_unit_retail = ' || NVL(TO_CHAR(r.standard_retail), 'NULL')
|| ', selling_unit_retail = ' || NVL(TO_CHAR(r.standard_retail), 'NULL')
|| ' WHERE item = ''' || r.item || ''''
|| ' AND loc = ' || r.loc || ';';
DBMS_OUTPUT.PUT_LINE(l_sql);
END LOOP;
END;
/