Oracle Retail Knowledge Base

_PUC_DMF.sql

3 min read

Source: DMF/PuC/MFCS/_PUC_DMF.sql

/* 
ekko - header ordens (transferencias, ordem de compra e allocations)
ekpo - items das ordens 
eket - delivery schedule 
EKBE - 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) 
*/
--------------
 
  SELECT /*M.BWART, -- transaction code
          M.MBLNR, -- SAP document ID (sequence) -- IT CAN BE REPEATED BY YEAR
          M.EBELN, -- order number
          M.LINE_ID, sap document line id
          M.PaRENT_ID, -- LINK ONE LINE TO ANOTHER IN MSEG (LINE_ID)
          EKKO.BSART, -- movement type
          EKKO.LIFNR, -- vendor number. probably use from EKKO table
          m.LLIEF, -- partner that delivery the order
          M.WAERS, -- currency
          M.WERKS, --- location store or wh
          M.LGORT, -- storage location
          M.INSMK, -- stock status/inv status F or empty is available to sell
          M.BUDAT_MKPF, -- transaction date
          M.CPUDT_MKPF, --  transaction timestamp date (which date the transaction must be integrated to Oracle)
          M.CPUTM_MKPF, --  transaction timestamp time (which date the transaction must be integrated to Oracle)
          m.KUNNR, -- check if it will need for B2B process
          m.KDAUF, -- check if it will need to identify the buying order and B2b
          m.KDPOS, -- check if it will need to identify the buying order item and B2b
          M.EBELP, -- order line id (ekpo)
          M.MATNR, -- sku id
          M.MENGE, -- quantity
          M.SHKZG, -- S positive and H negative
          M.DMBTR, -- total cost amount (expenses and upcharges included)
          M.BNBTR,-- additionaly cost (expenses and upchages???)
          m.SHKUM, -- revaluation sign
          m.DMBUM, -- revaluation amount
          M.VBELN_IM, -- sap shipment id for supplier asnin or internal asnout LINK to lips and likp
          M.VBELP_IM, -- sap shipment item id LINK to lips item
          m.XBLNR_MKPF, -- referece from supplier (just a reference)
          m.WEMPF, -- for shipments the target location, but we should use the base tables
          M.UMWRK, -- for shipments the target location, but we should use the base tables
          M.UMLGO, -- for shipments the target location storage, but we should use the base tables
                   -- check the inv staus for each storage location
          m.KZBEW, -- indicated the document linked to the movement. use the EKKO types
 
          */
          --VKWRT, -- sales price + tax
          --EXVKW, -- sales price to the stores
          --VKWRA,
          --DMBTR,
          --SALK3,
          --LBKUM,   
          m.ebeln,
          --DMBTR,
          m.*
          --distinct M.BWART, ekko.BSART
            --count(distinct m.ebeln), sum(DMBTR)
     FROM sap_slt_prod_pv1.MSEG m
LEFT JOIN sap_slt_prod_pv1.EKKO ekko on m.ebeln = ekko.ebeln and m.mandt = ekko.mandt
    WHERE 1=1
      and BUDAT_MKPF > CPUDT_MKPF
      --and BWART in ('641','101')
      --and SHKZG = 'S'
      --and ekko.BSART = 'ZUB2'
      --and m.ebeln = '4501965061'
      and ekko.BSART not in ('ZUBM','ZUBA','ZUVF','ZUPF','ZU2F','ZU3F')
      --order by matnr
;
 
 
select --ekko.ebeln, bsart, EKKO.RESWK, ekpo.werks, lifnr, ekpo.*
  ekko.ebeln, ekpo.menge, ekpo.netpr, ekpo.menge*ekpo.netpr
  from sap_slt_prod_pr1.EKKO ekko,
       sap_slt_prod_pr1.EKpO ekpo
    where ekko.ebeln = ekpo.ebeln
      and ekko.BSART = 'ZUB2'
      and ekko.ebeln = '4501965061'
      and ekpo.matnr = '000014060932600001'
      ;
      
 
-- CHECK HOW SAP HANDLE AN INV ADJUST OF TWO ITEMS WITH DIFF GLD
 
 
select * from sap_slt_prod_pv1.lfa1 where lifnr in ('0000236620','0000236100');
 
select * from channels_ctrl;
select * from xref_location where sap_id in ('0157','0322','0156','0317');
 
 
select ibc_wh, count(1) from PUC_IBC_PO_REPL_TMP group by ibc_wh;
 
4902880380
4902880380
4902879443
4902875463
 
SELECT * FROM DMF2.XREF_ITEM_IBC_RETAIL;
 
 
select max(EKKO.BEDAT) 
from sap_slt_prod_pr1.EKko ekko,
    sap_slt_prod_pr1.Eket eket 
    where 1=1
    and ekko.ebeln = eket.ebeln
    and ekko.BSART = 'ZGXB' 
    and EKET.WEMNG > 0;
    
    
    select * from inv_adjustment_reason_xref;
    
    
--------------
 
select * from deps_ctrl;
select im.dept, d.dept_name, count(1) from item_master_ctrl im, deps_ctrl d where item_level = tran_level and d.dept = im.dept group by im.dept, d.dept_name order by dept_name;
select * from item_master_ctrl im where item_level = tran_level and dept = 130;
 
select BUDAT_MKPF, m.* from sap_slt_prod_pv1.mseg m where matnr in ('000000118659800390','000000140317310050');-- and BUDAT_MKPF >= 20241027; 
 
--------------------------------------------------------

Graph View