Oracle Retail Knowledge Base

OrdersSAP.sql

2 min read

Source: DMF/PuC/AIF/OrdersSAP.sql

select BSART, count(1) from SAP_SLT_PROD_PR1.EKKO where EKKO.BEDAT >= 20240101 group by BSART;
 
 
-- open transfer: 4903607327
-- close transfer: 
SELECT * FROM sap_slt_prod_pv1.LIPS WHERE VGBEL = '4903607327';
select matnr, werks, sum(menge) from sap_slt_prod_pv1.EKPO WHERE ebeln = '4903448992' and EKPO.UEBPO = '00000' group by matnr, werks ;
select * from PUC_TSF_TMP WHERE tsf = '4903607327';
 
SELECT ebeln/*, matnr */FROM (
   SELECT EKPO.ebeln, /*EKPO.matnr,*/ sum(ekpo.menge) tsf_qty, sum(e.WEMNG) as received_qty, LOEKZ --, ELIKZ
     FROM sap_slt_prod_pv1.EKPO
LEFT JOIN sap_slt_prod_pv1.eket e on e.EBELN = EKPO.EBELN AND e.EBELP = EKPO.EBELP
    WHERE 1=1
      AND EKPO.UEBPO = '00000'
      AND ELIKZ = ' '
    group by EKPO.ebeln, /*EKPO.matnr, */EKPO.LOEKZ)
    group by ebeln /*, matnr*/ having count (1) > 1;
    
SELECT EBELN, 
       MATNR, 
      -- EBELP, 
       --LOEKZ, 
       --ELIKZ, 
       SUM(tsf_qty) as tsf_qty, 
       SUM(cancel_qty) as cancel_qty, 
       SUM(ship_qty) as ship_qty , 
       SUM(received_qty) as received_qty FROM (  
  SELECT EKPO.EBELN, 
         EKPO.MATNR, 
         EKPO.EBELP, 
         decode(LOEKZ,'L',0,EKPO.MENGE) tsf_qty, 
         decode(LOEKZ,'L',EKPO.MENGE,0) cancel_qty,
         nvl(LIPS.LFIMG,0) ship_qty,
         EKET.WEMNG as received_qty, 
         LOEKZ,
         ELIKZ
     FROM --PUC_TSF_TMP t,
          SAP_SLT_PROD_PV1.EKPO
LEFT JOIN SAP_SLT_PROD_PV1.EKET EKET on EKET.EBELN = EKPO.EBELN AND EKET.EBELP = EKPO.EBELP
LEFT JOIN SAP_SLT_PROD_PV1.LIPS LIPS on LIPS.VGBEL = EKPO.EBELN AND TO_NUMBER(LIPS.VGPOS) = TO_NUMBER(EKPO.EBELP)
LEFT JOIN SAP_SLT_PROD_PV1.LIKP LIKP on LIKP.VBELN = LIPS.VBELN
    WHERE 1=1 
      --AND EKPO.ebeln = t.tsf
      AND EKPO.ebeln = 4821297171
      --AND EKPO.MATNR = '000000104673400380'
      AND EKPO.UEBPO = '00000'
    )
    group by ebeln, matnr--,EBELP, LOEKZ,ELIKZ
;
 
 
SELECT EBELN, 
       --MATNR, 
       --EBELP, 
       --LOEKZ,
       --ELIKZ,
       MIN(STATUS) STATUS--, 
       --SUM(tsf_qty) as tsf_qty, 
       --SUM(cancel_qty) as cancel_qty, 
       --SUM(ship_qty) as ship_qty , 
       --SUM(received_qty) as received_qty 
  FROM (  
        SELECT EKPO.EBELN, 
                 /*EKPO.MATNR, 
                 EKPO.EBELP, 
                 decode(LOEKZ,'L',0,EKPO.MENGE) tsf_qty, 
                 decode(LOEKZ,'L',EKPO.MENGE,0) cancel_qty,
                 nvl(LIPS.LFIMG,0) ship_qty,
                 EKET.WEMNG as received_qty, 
                 EKPO.LOEKZ,
                 EKPO.ELIKZ,*/
                 CASE 
                    WHEN EKPO.ELIKZ = 'X' THEN 'C'
                    WHEN EKPO.LOEKZ = 'L' THEN 'C'
                    WHEN EKPO.MENGE = EKET.WEMNG THEN 'C'
                    ELSE 'A'
                 END STATUS
             FROM --PUC_TSF_TMP t,
                  SAP_SLT_PROD_PV1.EKPO
        LEFT JOIN SAP_SLT_PROD_PV1.EKET EKET on EKET.EBELN = EKPO.EBELN AND EKET.EBELP = EKPO.EBELP
        --LEFT JOIN SAP_SLT_PROD_PV1.LIPS LIPS on LIPS.VGBEL = EKPO.EBELN AND TO_NUMBER(LIPS.VGPOS) = TO_NUMBER(EKPO.EBELP)
        --LEFT JOIN SAP_SLT_PROD_PV1.LIKP LIKP on LIKP.VBELN = LIPS.VBELN
            WHERE 1=1 
              --AND EKPO.ebeln = t.tsf
              AND EKPO.ebeln = 4821297171
              AND EKPO.UEBPO = '00000'
    )
    group by ebeln --, matnr, EBELP, LOEKZ, ELIKZ
;
 
 
-- 3528899442 -- RECEIVED, LINHAS CANCELAS E NAO FECHADA (ELIKZ = ' ') --> STATUS C
-- 3528984173 -- NOT RECEIVED , LINHAS CANCELAS E NAO FECHADA (ELIKZ = ' ')  --> STATUS A

Graph View