InvAdjustment_Hist_v2.sql

Source: DMF/PuC/AIF/InvAdjustment_Hist_v2.sql

-- INVENTORY ADJUSTMENTS --
-- 0. Truncate ctrl table ORI_INV_ADJUST_CTRL
truncate table ORI_INV_ADJUST_CTRL;
 
-- 1. Insert into CTRL (ORI_INV_ADJUST_CTRL) table:
   INSERT INTO ORI_INV_ADJUST_CTRL(ITEM
                                ,ORG_NUM
                                ,DAY_DT
                                ,REASON_CODE
                                ,INVADJ_TYPE_CODE
                                ,INVADJ_QTY
                                ,INVADJ_COST_AMT_LCL
                                ,INVADJ_RTL_AMT_LCL
                                ,DOC_CURR_CODE
                                ,LOC_CURR_CODE
                                ,DATASOURCE_NUM_ID)
 
 SELECT 
  m.MATNR as ITEM, 
  oracle_id ORG_NUM, 
  CASE 
	WHEN REGEXP_LIKE(TO_CHAR(m.BUDAT_MKPF),'^\d{8}$') THEN 
		TO_CHAR(m.BUDAT_MKPF) 
	ELSE 
		TO_CHAR(m.BUDAT_MKPF, 'YYYYMMDD') 
  END  DAY_DT,
  TO_CHAR(rx.ORACLE_CODE) REASON_CODE, 
  '22' INVADJ_TYPE_CODE, 
  CASE WHEN m.SHKZG = 'H' THEN - ABS(m.MENGE) WHEN m.SHKZG = 'S' THEN ABS(m.MENGE) ELSE m.MENGE END INVADJ_QTY, 
  m.DMBTR INVADJ_COST_AMT_LCL, 
  (m.MENGE * pc.price) as INVADJ_RTL_AMT_LCL, 
  m.WAERS DOC_CURR_CODE, 
  'EUR' LOC_CURR_CODE, 
  1 DATASOURCE_NUM_ID 
FROM 
  sap_slt_prod_pv1.MSEG m 
  JOIN inv_adjustment_reason_xref rx ON rx.SAP_CODE = m.BWART 
  JOIN xref_location xref ON xref.sap_id = m.WERKS and oracle_type = 'S' 
  JOIN ITEM_MASTER_CTRL im ON IM.ITEM = m.MATNR AND CTRL_STATUS = 'C' 
  left join ORI_PRICE_CLEARANCE_RAW pc on pc.item = m.matnr and pc.store = xref.oracle_id and TO_CHAR(pc.start_date, 'YYYYMMDD') >= CASE 
            WHEN REGEXP_LIKE(TO_CHAR(m.BUDAT_MKPF),'^\d{8}$') THEN 
                TO_CHAR(m.BUDAT_MKPF) 
            ELSE 
                TO_CHAR(m.BUDAT_MKPF, 'YYYYMMDD') 
            END and TO_CHAR(pc.end_date, 'YYYYMMDD') < CASE 
            WHEN REGEXP_LIKE(TO_CHAR(m.BUDAT_MKPF),'^\d{8}$') THEN 
                TO_CHAR(m.BUDAT_MKPF) 
            ELSE 
                TO_CHAR(m.BUDAT_MKPF, 'YYYYMMDD') 
            END 
WHERE 
  (
    (m.BWART IN ('551', '911', 'Z51', '552', '912', 'Z52', 'Z71', 'Z72', '701', '702' )) 
        or 
    (m.BWART = '101' AND m.XBLNR_MKPF LIKE 'AUTO%')
  ) 
  AND CASE 
            WHEN REGEXP_LIKE(TO_CHAR(m.BUDAT_MKPF),'^\d{8}$') THEN 
                TO_CHAR(m.BUDAT_MKPF) 
            ELSE 
                TO_CHAR(m.BUDAT_MKPF, 'YYYYMMDD') 
            END >= '20240101'; 
			
-- 3. Export inventory.csv
 
python oracleExport_adjustment.py