Source: DMF/PuC/AIF/StockTransactions.sql
SELECT /*+ PARALLEL(24) */
distinct --EKKO.BSART AS IBC_PO_TYPE,
EKKO.EBELN AS IBC_PO ,
EKPO.WERKS AS IBC_WH,
CUST_ORDER.BSTNK AS SALES_ORDER,
EKKO_LEG2.EBELN AS ALLOC_1,
EKKO_LEG2.WERKS AS BUYING_WH,
EKKO_LEG3.EBELN AS ALLOC_2,
EKKO_LEG3.WERKS AS STORE
FROM sap_slt_prod_pr1.EKKO EKKO
join sap_slt_prod_pr1.EKPO EKPO on EKKO.EBELN = EKPO.EBELN AND EKPO.UEBPO = '00000'
--join sap_slt_prod_pr1.EKET EKET on EKET.EBELN = EKPO.EBELN AND EKET.EBELP = EKPO.EBELP
left join (select DISTINCT VBAK.BSTNK, FRET.BLNRB
from sap_slt_prod_pr1.FRET FRET,
sap_slt_prod_pr1.VBAK VBAK
where VBAK.VBELN = FRET.BLNRA) CUST_ORDER on CUST_ORDER.BLNRB = EKKO.EBELN
left join (select EKKO2.EBELN, EKPO2.WERKS, EKKO2.RESWK
from sap_slt_prod_pv1.EKKO EKKO2
join sap_slt_prod_pv1.EKPO EKPO2 on EKKO2.EBELN = EKPO2.EBELN
AND EKPO2.UEBPO = '00000'
where EKKO2.BEDAT >= 20240101
and EKKO2.LIFNR = '0000940506') EKKO_LEG2 on EKKO_LEG2.EBELN = CUST_ORDER.BSTNK
left join (select EKKO3.EBELN, EKPO3.WERKS, EKKO3.RESWK, EKKO3.ZZREFNB
from sap_slt_prod_pv1.EKKO EKKO3
join sap_slt_prod_pv1.EKPO EKPO3 on EKKO3.EBELN = EKPO3.EBELN
AND EKPO3.UEBPO = '00000'
where EKKO3.BEDAT >= 20240101) EKKO_LEG3 on EKKO_LEG3.ZZREFNB = EKKO_LEG2.EBELN
where 1 = 1
AND EKKO.BSART in ('ZGNB' , 'ZGVB' )
AND EKKO.BEDAT >= 20240101
--AND EKET.WEMNG > 0
--AND EKKO_LEG3.EBELN IS NULL
;
SELECT /*+ PARALLEL(24) */
distinct EKKO.EBELN as SALES_ORDER ,
EKPO.WERKS AS IBC_WH,
EKKO.RESWK AS BUYING_WH,
EKPO.ELIKZ AS CLOSED,
EKPO.loekz as CANCELED,
EKKO_LEG2.EBELN as ALLOC_1,
EKKO_LEG2.WERKS AS STORE,
EKKO_LEG2.RESWK AS STORE2
FROM sap_slt_prod_pv1.EKKO EKKO
join sap_slt_prod_pv1.EKPO EKPO on EKKO.EBELN = EKPO.EBELN AND EKPO.UEBPO = '00000'
left join (select distinct EKKO2.EBELN, EKPO2.WERKS, EKKO2.RESWK, EKKO2.ZZREFNB
from sap_slt_prod_pv1.EKKO EKKO2
join sap_slt_prod_pv1.EKPO EKPO2 on EKKO2.EBELN = EKPO2.EBELN
AND EKPO2.UEBPO = '00000'
where EKKO2.BEDAT >= 20240101) EKKO_LEG2 on EKKO_LEG2.ZZREFNB = EKKO.EBELN
where 1 = 1
AND EKKO.BSART in ('ZNNR', 'ZNFL')
AND EKKO.LIFNR = '0000940506'
AND EKKO.BEDAT >= 20240101;
SELECT
EKPO.EBELN,EKPO.MATNR,EKPO.werks, EKPO.ELIKZ, ekpo.loekz ,EKET.WEMNG, EKET.MENGE, EKPO.MENGE, ekpo.netpr
FROM sap_slt_prod_pr1.EKET EKET,
sap_slt_prod_pr1.EKPO EKPO,
sap_slt_prod_pr1.EKKO EKKO
WHERE 1=1
and EKPO.EBELN='4502088933'
AND EKPO.MATNR='000014630803700005'
and EKKO.EBELN = EKPO.EBELN
and EKET.EBELN = EKPO.EBELN
AND EKET.EBELP = EKPO.EBELP
--and EKPO.ELIKZ = 'X'
--and EKET.WEMNG > 0
and EKKO.BSART IN ('ZGRP', 'ZGNB', 'ZGVB', 'ZGUM')
-- group by EKPO.EBELN,EKPO.MATNR,EKPO.werks, EKPO.ELIKZ,ekpo.loekz
order by EKPO.werks;
SELECT
EKPO.EBELN,EKPO.MATNR,EKPO.werks, EKPO.ELIKZ, ekpo.loekz ,sum(EKET.WEMNG), sum(EKET.MENGE), sum(EKPO.MENGE)
FROM sap_slt_prod_pr1.EKET EKET,
sap_slt_prod_pr1.EKPO EKPO,
sap_slt_prod_pr1.EKKO EKKO
WHERE 1=1
and EKPO.EBELN='4502088933'
AND EKPO.MATNR='000014630803700005'
and EKKO.EBELN = EKPO.EBELN
and EKET.EBELN = EKPO.EBELN
AND EKET.EBELP = EKPO.EBELP
--and EKPO.ELIKZ = 'X'
--and EKET.WEMNG > 0
and EKKO.BSART IN ('ZGRP', 'ZGNB', 'ZGVB', 'ZGUM')
group by EKPO.EBELN,EKPO.MATNR,EKPO.werks, EKPO.ELIKZ,ekpo.loekz
order by EKPO.werks;
SELECT EKPO.EBELN,EKPO.MATNR,EKPO.werks, EKPO.ELIKZ, ekpo.loekz , EKPO.MENGE, EKPO.UEBPO, ekpo.ebelp, ekpo.*
FROM sap_slt_prod_pr1.EKKO EKKO,
sap_slt_prod_pr1.EKPO EKPO
where 1=1
--and EKPO.EBELN='4502088933'
--AND EKPO.MATNR='000014630803700005'
and EKKO.EBELN = EKPO.EBELN
--and EKPO.UEBPO = '00000'
and EKPO.UEBPO = ' '
--and werks = '0157'
order by EKPO.werks;
-- 4600002735 000014060927300099 0 00000 00170 -- werks empty and menge = 0
-- group by EBELN,MATNR,werks,ebelp having count(1) > 1 ;
SELECT LIPS.VBELN,VGBEL,matnr,LIKP.LFDAT,LIKP.BOLNR,LIPS.LFIMG,LIPS.PSTYV, LIPS.UEPOS, lips.*--, LIPS.UEPOS
FROM sap_slt_prod_pr1.lips,
sap_slt_prod_pr1.likp
where 1=1
and LIPS.VBELN = LIKP.VBELN
and VGBEL='4502058998'
AND LIKP.VBELN='0183082416'
AND matnr='000056850920700037';
select VBELN_GEN, VEKP.*
FROM sap_slt_prod_pv1.VEKP
where VEKP.VBELN_GEN <> ' ';
--SELECT EKPO.EBELN,EKPO.MATNR,EKPO.werks, EKPO.ELIKZ, ekpo.loekz , EKPO.MENGE, EKPO.UEBPO, ekpo.ebelp, ekpo.*, EKKO.*
SELECT EBELN,werks,COUNT(1) FROM (
SELECT DISTINCT EKPO.EBELN,EKPO.werks
FROM sap_slt_prod_pr1.EKKO EKKO,
sap_slt_prod_pr1.EKPO EKPO
where 1=1
--and EKPO.EBELN='4502058998'
--AND EKPO.MATNR='000056850920700037'
and EKKO.EBELN = EKPO.EBELN
and EKPO.UEBPO = '00000'
AND EKKO.BSART IN ('ZGRP', 'ZGNB', 'ZGVB', 'ZGUM')) GROUP BY EBELN,werks HAVING COUNT(1) > 1;
--------------------------
SELECT
ekpo.ebeln,
ekpo.ebelp,
mseg.werks,
mseg.lgort,
eket.menge AS ordered_qty,
SUM(CASE WHEN mseg.bwart = '101' THEN mseg.menge ELSE 0 END) AS received_qty,
SUM(CASE WHEN mseg.bwart = '102' THEN mseg.menge ELSE 0 END) AS canceled_qty,
CASE
WHEN ekpo.elikz = 'X' THEN 'CLOSED'
ELSE 'OPEN'
END AS status
FROM sap_slt_prod_pv1.ekpo
JOIN sap_slt_prod_pv1.eket ON ekpo.ebeln = eket.ebeln AND ekpo.ebelp = eket.ebelp
LEFT JOIN sap_slt_prod_pv1.ekbe ON ekpo.ebeln = ekbe.ebeln AND ekpo.ebelp = ekbe.ebelp
LEFT JOIN sap_slt_prod_pv1.mseg ON ekbe.belnr = mseg.mblnr AND ekbe.gjahr = mseg.mjahr
WHERE ekpo.ebeln = 4821594777
GROUP BY ekpo.ebeln,
ekpo.ebelp,
mseg.werks,
mseg.lgort,
eket.menge,
ekpo.elikz;
SELECT * FROM sap_slt_prod_pv1.mseg WHERE MSEG.BWART IN (101,102);
select ebeln from (SELECT distinct ebeln, EINDT FROM sap_slt_prod_pr1.eket where EINDT > '20240101' and wemng>0) group by ebeln having count(1)> 1;
SELECT * FROM sap_slt_prod_pr1.ekPO WHERE EBELN = '4502040282' and EKPO.UEBPO = '00000' ;
SELECT * FROM sap_slt_prod_pr1.eket WHERE EBELN = '4502040282' and ebelp in ('00210','00450','00330','00010');
SELECT * FROM sap_slt_prod_pv1.EKBE WHERE EBELN = '4502040282' and ebelp in ('00210','00450','00330','00010');
ekko - header ordens (transferencias, ordem de compra e allocations)
ekpo - items das ordens
eket - recebimentos das ordens (recebimentos
Lips - detail shipment
likp - header shipments (rtv e shipments)
VBAK - header da venda
VBAP - detail da venda
FRET - liga a PO com a venda (usamos no PR1)