Source: DMF/PuC/AIF/SAP.sql
select TO_CHAR(SYSDATE - INTERVAL '2' YEAR, 'YYYYMMDD'), TO_CHAR(TRUNC(ADD_MONTHS(SYSDATE, -24), 'YEAR'), 'YYYYMMDD') from dual
SELECT count(1) FROM PUC_SKU_IM_OWN_BRANDS_TMP;
SELECT count(1) FROM PUC_SKU_IM_EXCL_BRANDS_TMP;
select to_number(lifnr) from sap_slt_prod_pv1.lfa1 where LFA1.KTOKK in ('LPRO','KIOK') ORDER BY to_number(lifnr) DESC
--ZZSTAMMKZ = NOS indicator
select ZZSTAMMKZ, ZZDISPOGRP , brand_id, m.* from sap_slt_prod_pv1.mara m where matnr in ('000000000008958180','000000000008994866');
select * from sap_slt_prod_pv1.WRF_BRANDS_T where brand_id in (1805,1985)
select max(ebeln) from sap_slt_prod_pr1.EKKO --> 4600002863
select max(ebeln) from sap_slt_prod_pv1.EKKO --> 5500037690
ALTER SESSION DISABLE PARALLEL DML;
select * from sap_slt_prod_pv1.mseg where bwart = '911';
select COALESCE((SELECT mk1.maktx
FROM sap_slt_prod_pv1.makt mk1
WHERE mk1.mandt = m.mandt
AND mk1.matnr = m.matnr
AND mk1.spras = 'E'
AND ROWNUM = 1),
(SELECT mk2.maktx
FROM sap_slt_prod_pv1.makt mk2
WHERE mk2.mandt = m.mandt
AND mk2.matnr = m.matnr
AND mk2.spras = 'D'
AND ROWNUM = 1)) AS ITEM_DESC from sap_slt_prod_pv1.mara m where MTART = 'ZDIE' order by ITEM_DESC;
select * from sap_slt_prod_pv1.wrf1
select * from sap_slt_prod_pv1.t001w where name1 like '%Wiethe%'
select * from sap_slt_prod_pv1.mard where WERKS = '0005' --> 2991
update xref_wh set V_WH = SUBSTR(V_WH,1,4) WHERE WH = 299
update xref_wh set WH_SLT = '0299' WHERE WH = 299
select * from xref_wh WHERE WH IS NOT NULL
select count(1) from STORE_ADD_CTRL
select * from rpm_zone_ctrl
select currency_code, store from store_add_ctrl s where not exists (select 1 from RPM_ZONE_LOCATION_STG r where r.location = s.store
select * FROM RPM_ZONE_LOCATION_ctrl
insert into RPM_ZONE_LOCATION_stg select store, 0, decode(currency_code,'CHF',415,'PLN',5201,'CZK',5301,'RON',5501,'HUF',5701,'BGN',6001,'RSD',6101,1), 'DMF',sysdate,'N' from store_add_ctrl s
SELECT * FROM mv_current_price
-- store
select * from sap_slt_prod_pv1.wrf1 where upper(zzhausvname2) like '%ONLINE%' ;
select distinct prioritaet from sap_slt_prod_pv1.wrf3;
select * from sap_slt_prod_pv1.T001L
-- item
select im.ZZDISPOGRP STYLE,
im.MATNR ITEM,
decode(im.ATTYP,'00', 'Single Item','01','Dispo','02','SKU','OTHER') ITEM_TYPE,
isup.LIFNR SUPPLIER,
decode(isup.RELIF,'X','Y','N') PRIMARY_SUPP_IND,
isup.IDNLF VPN
from sap_slt_prod_pv1.mara im,
sap_slt_prod_pv1.eina isup
where im.mandt = isup.mandt
and im.matnr = isup.matnr
and im.mtart in ('ZDIE','ZMOD','ZNLA')
and isup.IDNLF = '000003318'; --'12081';
SELECT im.ZZDISPOGRP STYLE,
decode(im.SATNR,' ',MATNR,SATNR) DISPO,
im.MATNR ITEM,
decode(im.ATTYP,'00', 'Single Item','01','Dispo','02','SKU','OTHER') ITEM_TYPE,
im.ean11 ean
from sap_slt_prod_pv1.mara im
WHERE 1=1
and
and im.ZZDISPOGRP = '007460988'
and im.mtart in ('ZMOD')
ORDER BY DISPO
select * from sap_slt_prod_pv1.lfa1 where lifnr in (
select lifnr from sap_slt_prod_pv1.eina isup where matnr in ('000000000001030600','000000103060000340','000000103060000440'))
select count(1), ean11 from sap_slt_prod_pv1.mara im where im.ATTYP = '02' group by ean11 having count(1) > 1
select * from sap_slt_prod_pv1.mara where ean11 = '4046062131203';
select * from sap_slt_prod_pv1.mseg
select * from sap_slt_prod_pv1.INOB where OBJEK = '000000103060200440'
select * from sap_slt_prod_pv1.AUSP where OBJEK = '000000000017822626'
select count(1), atwrt from sap_slt_prod_pv1.WRF_CHARVALT where spras = 'E' and atinn in ('0000000001','0000000002') group by atwrt having count(1) >1
select count(1) from (
select decode(atinn,'0000000001','C','S') DIFF_TYPE,
atwrt diff_id,
atwTB diff_id_desc,
decode(spras,'D','DE','EN') lang
from sap_slt_prod_pv1.WRF_CHARVALT
where spras in ('D','E') and atinn in ('0000000001','0000000002') order by atinn, atwrt )
select atwrt from sap_slt_prod_pv1.AUSP group by atwrt
SELECT
im.*
from sap_slt_prod_pv1.mara im
WHERE im.ZZDISPOGRP = '007460988' and im.mtart in ('ZMOD')
select * from sap_slt_test_qv1.MARA M WHERE ZZDISPOGRP = '007340700' and ATTYP = '01'
SELECT ZZDISPOGRP, MATNR, ATTYP, M.* FROM sap_slt_test_qv1.MARA M WHERE MATNR IN ('000000000001729249','000000000001729248')
select * from sap_slt_test_qv1.eina where MATNR IN ('000000000001729249','000000000001729248') order by MATNR
SELECT
a.*,
k.kbetr
FROM
sap_slt_test_qv1.a018 a,
sap_slt_test_qv1.konp k
WHERE
matnr IN ( '000000000001729249', '000000000001729248' )
AND datbi = '99991231'
AND a.kschl = 'YPBE'
AND ekorg = '0253'
AND a.kschl = k.kschl
AND a.knumh = k.knumh
select * from sap_slt_test_qv1.konp where KSCHL = 'YPBE' and knumh in ('1935287983','2155614732','1935286024','2155627801')
---- cost
select * from sap_slt_prod_pR1.konp where KSCHL = 'YPBE'
select * from sap_slt_prod_pv1.ZLOG_STO_ROUTE
select * from sap_slt_test_qv1.MAW1, sap_slt_test_qv1.MARA WHERE WHERL <> ' ' AND MARA.MATNR = MAW1.MATNR AND MARA.MANDT = MAW1.MANDT AND ATTYP = '02' AND ROWNUM < 10;
SELECT ATTYP, M.* FROM sap_slt_test_qv1.MARA M WHERE MATNR = '000000114100900280'
SELECT ATTYP, M.* FROM sap_slt_test_qv1.MARA M WHERE SATNR = '000000000001141009'
SELECT * FROM sap_slt_test_qv1.MAW1 WHERE MATNR = '000000114100900280'
SELECT * FROM sap_slt_test_qv1.A018 WHERE MATNR = '000000114100900280' AND DATBI = '99991231' AND KSCHL = 'YPBE' and KNUMH IN (2026155549,2035910167) ;
SELECT * FROM sap_slt_test_qv1.KONP WHERE KNUMH IN (2026155549,2035910167) AND KSCHL = 'YPBE';
SELECT * FROM sap_slt_test_qv1.MAW1 WHERE MATNR = '000000000001141009'
SELECT M.* FROM sap_slt_test_qv1.EINA M WHERE MATNR = '000000000000910962'
SELECT * FROM sap_slt_test_qv1.LFA1 WHERE LIFNR in ('0000101435','0000237201')
SELECT * FROM sap_slt_test_qv1.A018 WHERE MATNR = '000000000000910962' AND DATBI = '99991231' AND KSCHL = 'YPBE';
SELECT * FROM sap_slt_test_qv1.KONP WHERE KNUMH IN (2026155549,2035910167) AND KSCHL = 'YPBE';
SELECT distinct ekorg FROM sap_slt_test_qr1.A018 where DATBI = '99991231' AND KSCHL = 'YPBE';
SELECT * FROM sap_slt_test_qv1.KONP
SELECT * FROM sap_slt_test_qv1.A950
--brand_type = 1 (own brand) and 2 (exclusive brand)
--ATTYP,'00', 'Single Item','01','Dispo','02','SKU','OTHER'
select m.brand_id,
m.ATTYP,
MATNR,
SATNR,
b.*,
m.*
from sap_slt_prod_pv1.mara m , sap_slt_prod_pv1.wrf_brands b where m.mandt = b.mandt and m.brand_id = b.brand_id and m.ATTYP = '02'
select * from sap_slt_prod_pv1.wrf_brands
select * from sap_slt_prod_pv1.mara m where ATTYP = '02'
select * from sap_slt_prod_pv1.WRF_BRANDS_T where brand_id like '%565%'
select * from sap_slt_test_qv1.a018 where store_ctrl.org_unit_id in (253,5002)
select * from sap_slt_prod_pv1.a018
--
-- dispo: 000000000001141009
-- sku: 000000114100900280
-- CHECK THE COUNTRY OF SUPPLIEER = CN
-- COUNTRY OF SOURCE = TR (TURQUIA)
--
-- scenarios which the cost is diff for each sales organization (what to do? which rule) eX.: DISPO: 000000000000910962
-- scenarios which the cost is linked only to inactive supplier eX: DISPO: 000000000000910962
-- THE INVOICE WILL COME FROM CN OR TR
-- for cost (a018) shold we SHOULD WE USE THE DISPO OR SKU?
-- FOR ITEM SUPPLIER (EINA) SHOULD WE USE THE DISPO OR SKU?
-- how to figure out if the item is IBC or not?
-- FOR IBC ITEM SHOULD WE GET THE SUPPLIERS FROM PR1? AND THE COST?
SELECT * FROM sap_slt_test_qv1.MARA M WHERE MATNR = '000000091096200280' --> DISPO: 000000000000910962
SELECT * FROM sap_slt_test_qv1.EINA WHERE MATNR = '000000000000910962'
SELECT * FROM sap_slt_test_qv1.LFA1 WHERE LIFNR IN ('0000237201','0000101435')
SELECT * FROM sap_slt_prod_pv1.LFA1 where name1 like 'IB%'
SELECT distinct LIFNR FROM sap_slt_test_qv1.EINA WHERE LIFNR in (SELECT LIFNR FROM sap_slt_prod_pv1.LFA1 where name1 like 'IB%') and LIFNR not in('0009900771','0000218200','0009704394','0009701715')
0000940252 --> ehemals Ans. H.Buying->940506
0000940506
0000940505 --> ehemals MCNeal -->940506
0000940250 --> ehemals MCNeal -->940506
select * from (
SELECT m.matnr,
(SELECT k.kbetr
FROM sap_slt_test_qv1.a018 a,
sap_slt_test_qv1.konp K
WHERE 1=1
AND a.kschl = 'YPBE' --Item Initial price PO
and m.matnr = a.matnr
AND a.datbi = '99991231'
AND k.knumh = a.knumh
AND k.mandt = a.mandt
AND m.mandt = a.mandt
AND A.EKORG = '0251') cost_at,
(SELECT k.kbetr
FROM sap_slt_test_qv1.a018 a,
sap_slt_test_qv1.konp k
WHERE 1=1
AND a.kschl = 'YPBE' --Item Initial price PO
and m.matnr = a.matnr
AND a.datbi = '99991231'
AND k.knumh = a.knumh
AND k.mandt = a.mandt
AND m.mandt = a.mandt
AND A.EKORG = '0253') cost_de
FROM sap_slt_test_qv1.mara m ,
sap_slt_test_qv1.wrf_brands b
WHERE 1=1
and m.mandt = b.mandt
and m.brand_id = b.brand_id
and m.ATTYP = '01'
and b.brand_type = '2'
--and m.matnr = '000000000000910962'
) where cost_at <> cost_de;
SELECT a.matnr, a.LIFNR, k.kbetr, a.ekorg, a.knumh, count(1)
FROM sap_slt_test_qv1.a018 a,
sap_slt_test_qv1.konp k
WHERE 1=1
AND a.kschl = 'YPBE' --Item Initial price PO
AND a.datbi = '99991231'
AND k.knumh = a.knumh
AND k.mandt = a.mandt
group by a.matnr, a.LIFNR, k.kbetr, a.ekorg, a.knumh having count(1) > 1
SELECT a.matnr, a.LIFNR, k.kbetr, a.ekorg, a.knumh, count(1)
FROM sap_slt_prod_pr1.a084 a,
sap_slt_prod_pr1.konp k
WHERE 1=1
AND a.kschl = 'ZVEP' --Item Initial price PO
AND a.datbi = '99991231'
AND k.knumh = a.knumh
AND k.mandt = a.mandt
select * from sap_slt_prod_pr1.a084 where kschl = 'ZVEP' and ekorg = '0506'
select brand_id, ATTYP, m.* from sap_slt_prod_pv1.mara m where matnr = '000000000001551561'
select * from sap_slt_prod_pv1.eina where matnr = '000000000001551561'; --LIFNR : 0000940506 -- IDNLF/VPN: 000000010744105019
select brand_id, ATTYP, m.* from sap_slt_prod_pr1.mara m where matnr = '000000010744105019'
select * from sap_slt_prod_pr1.eina where matnr = '000000010744105019';
select w.*, a.* from v_addr_wh a, v_wh w where key_value_1 = w
select * from sap_slt_test_qv1.konp a where a.kschl = 'YPBE' and konwa <> 'EUR' --Item Initial price PO
AND a.datbi = '99991231'
select * from sap_slt_prod_pv1.T001L where WERKS = '0001'
select * from sap_slt_prod_pv1.T001W where WERKS = '0003'
select * from sap_slt_prod_pv1.wrf1 wf, sap_slt_prod_pv1.T001W w where locnr in ('0000000071','0000000039') and wf.locnr = w.kunnr
select * from sap_slt_prod_pv1.ADR6
select distinct price_change from MIGRATION_PRICE_CLEARANCE_HIST_STG
select distinct werks from sap_slt_prod_pv1.t0052
select distinct werks from sap_slt_prod_pr1.lqua
select * from sap_slt_prod_pr1.lqua where werks in ('0322','0157')
select distinct VSBED from sap_slt_prod_pv1.VBAK
select * from sap_slt_prod_pv1.T052 where zterm in ('ZB73','ZB91','ZB89'), 'ZB88', 'ZB15')
select * from sap_slt_prod_pv1.lfa1 where '0000101761'
-------------------
-- purchase order
select WKURS, WAERS, ERNAM, knumv, K.* from sap_slt_prod_pr1.EKKO K WHERE K.BSART in ('ZGRP', 'ZGNB' , 'ZGVB' , 'ZGUM')
--> EKKO.BEDAT: creation date
select distinct WAERS from sap_slt_prod_pr1.EKKO ;
select kbetr from sap_slt_prod_pr1.KONV --> ekko.knumv ekkpo.sequence of item
select * from sap_slt_prod_pr1.EKET
select * from sap_slt_prod_pr1.EKPA where parvw = 'ZP'
select distinct EKORG from sap_slt_prod_pr1.EKPA
select * from sap_slt_prod_pr1.lfa1 where lifnr = '0009704880'
-- purchase order item
select MENGE, LOEKZ, , NETPR, I.* from sap_slt_prod_pr1.EKPO I WHERE LOEKZ <> ' '
select DISTINCT IDNLF from sap_slt_prod_pr1.EKPO
--> EKPO.LOEKZ = L --> item deleted (POs full deleted throught the items)
--> EKPO.ELIKZ = X --> delivery complete indicator
-- expenses
select* from sap_slt_prod_pr1.KONV
select * from sap_slt_prod_pr1.likp l
select VGPOS, l.* from sap_slt_prod_pr1.lips l
-- LIPS.VGPOS = EKPO.EBELP
-----
-- TRANSFER FROM ibc to buying wh
select * from sap_slt_prod_pr1.FRET -- XREF ONLY FOR XDOCKING PROCESS FROM IBC TO BUYING WH
/*
Connection PV1 PO and PR1 PO:
PV1.EKKO-EBELN = PR1.VBAK-BSTNK
PR.VBAK-VBELN = PR1.FRET-BLNRA
Then the PR1 PO is PR1.FRET-BLNRB
*/
select * from sap_slt_prod_pv1.ekko where lifnr = '0000940506' and bsart in ('ZNB')
select * from sap_slt_prod_pv1.ekko where lifnr = '0000940506' and bsart in ('ZNNR', 'ZNFL') -- replenishment ones from IBC
select distinct bsart from sap_slt_prod_pv1.ekko where lifnr = '0000940506' and
-- ibc supplier = '0000940506'
-- bsart --> ZNB, ZBPB, ZNOS -- PURCHASE ORDER --> CUSTOMER ORDER IN PR1 --> NOT USED
-- bsart --> ZNNR, ZNFL -- REPLENISHMENT
select * from sap_slt_prod_pv1.ekPO where ebeln = 4821146263
select * from sap_slt_prod_pv1.mseg where mblnr = 4821146263
-- seconde leg
select * from sap_slt_prod_pv1.ekko where EKKO.ZZREFNB = '4821146263'
-- 3rd party orders
-- filter by bsart = ZNB, ZBPB, ZNOS -- PURCHASE ORDER --> and supplier <> '0000940506'
select * from sap_slt_prod_pv1.VEKP
select * from sap_slt_prod_pv1.VEPO
----
-- Store to Store (direct)
SELECT WERKS, netpr, ekko.*, ekpo.* from sap_slt_prod_pv1.ekpo ekpo, sap_slt_prod_pv1.ekko ekko where
ekpo.EBELN = ekko.EBELN and ekpo.mandt = ekko.mandt and bsart in ('ZWFA','ZWFO','ZWFR','ZWFS','ZWFW')
and ekko.ebeln in ('4903404187','4902824076')
-- direct store to store
-- first leg store --> wh
select * from sap_slt_prod_pv1.ekko where bsart in
('ZBAB',
'ZWBU',
'ZWAW',
'ZWRK',
'ZWVR',
'ZWND',
'ZWOW',
'ZWVS',
'ZSBU',
'ZWVW',
'ZWVD',
'ZWSD') -- firt leg from store --> wh
-- store --> wh --> (wh) --> store
SELECT WERKS, e.* FROM sap_slt_prod_pv1.ekPO e WHERE EBELN = '4902884186'
-- second leg in case Wh --> WH
SELECT WERKS, netpr, ekko.*, ekpo.* from sap_slt_prod_pv1.ekpo ekpo, sap_slt_prod_pv1.ekko ekko where
ekpo.EBELN = ekko.EBELN and ekpo.mandt = ekko.mandt and bsart in ('ZWV2','ZWBB','ZWR7','ZWV6','ZWV7')
ZWV2
ZWBB
ZWR7
ZWV6
ZWV7
-- second or third leg wh --> st
SELECT WERKS, netpr, ekko.*, ekpo.* from sap_slt_prod_pv1.ekpo ekpo, sap_slt_prod_pv1.ekko ekko where
ekpo.EBELN = ekko.EBELN and ekpo.mandt = ekko.mandt and bsart in ('ZWV4','ZBU4','ZWR9','ZWV8','ZWV9')
ZWV4
ZBU4
ZWR9
ZWV8
ZWV9
;
-- b2b
select * from sap_slt_prod_pr1.VBAK
inner join sap_slt_prod_pr1.VBAP on VBAK.VBELN = VBAP.VBELN
where VBAK.AUART = 'ZWHE' AND VBAK.KUNNR = '0000340280';
select * from sap_slt_prod_pv1.VBAK
inner join sap_slt_prod_pv1.VBAP on VBAK.VBELN = VBAP.VBELN
where VBAK.AUART = 'ZSOA';
----- DHL
select * from wh_ctrl
select * from sap_slt_prod_pv1.wrf1 where locnr = '0000000546'
select * from sap_slt_prod_pv1.t001w where werks = '0546'
select * from sap_slt_prod_pv1.EKPO where EKPO.WERKS = '0546'
--------------
select * from sap_slt_prod_pv1.ZSTA_WSA;
select * from sap_slt_prod_pv1.T001K;