inventory_adjustment.sql

Source: DMF/PuC/AIF/DR1 scripts/inventory_adjustment.sql

-- INVENTORY ADJUSTMENTS --
 
-- 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 ITEM, 
          CASE 
            WHEN sc.STORE IS NOT NULL THEN 
                LTRIM(m.WERKS, '0') 
            ELSE 
                LTRIM(w.V_WH, '0') 
            END ORG_NUM,
            TO_DATE(m.BUDAT_MKPF, 'YYYYMMDD') 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 * current_price) 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 item_master_ctrl im
          ON im.CTRL_STATUS = 'C'
         AND im.ITEM = m.MATNR
   LEFT JOIN store_add_ctrl sc ON sc.CTRL_STATUS = 'C' AND LTRIM(sc.STORE, '0') = LTRIM(m.WERKS, '0')   /* STORE control (C) */
   LEFT JOIN (SELECT wc.WH, wx.V_WH
                FROM wh_ctrl wc,
                     xref_wh wx
               Where wc.CTRL_STATUS = 'C'
                  AND wx.wh = wc.WH) w ON LTRIM(w.WH, '0') = LTRIM(m.WERKS, '0') 
   LEFT JOIN mv_current_price cp
          ON cp.ITEM = m.MATNR
         AND cp.STORE = sc.STORE
       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 m.BUDAT_MKPF >= '20240101' 
         AND (sc.STORE IS NOT NULL OR (W.WH IS NOT NULL AND w.V_WH IS NOT NULL))
      
   UNION ALL
 
    SELECT 
          xi.ITEM_PV1 as ITEM, 
          LTRIM(m.WERKS, '0') ORG_NUM,
          TO_DATE(m.BUDAT_MKPF, 'YYYYMMDD') 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, 
            NULL INVADJ_RTL_AMT_LCL, 
            m.WAERS DOC_CURR_CODE, 
            'EUR' LOC_CURR_CODE, 
            1 DATASOURCE_NUM_ID
        FROM sap_slt_prod_pr1.MSEG m 
        JOIN inv_adjustment_reason_xref rx ON rx.SAP_CODE = m.BWART
          /* PR1 -> PV1 item */
        JOIN xref_item_ibc_retail xi ON ITEM_PR1 = m.MATNR
		JOIN item_master_ctrl im
          ON im.CTRL_STATUS = 'C'
         AND im.ITEM = xi.ITEM_PV1
   LEFT JOIN (SELECT wc.WH, wx.V_WH
                FROM wh_ctrl wc,
                     xref_wh wx
               WHERE wc.CTRL_STATUS = 'C'
                  AND wx.wh = wc.WH) w ON LTRIM(w.WH, '0') = LTRIM(m.WERKS, '0') 
        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 m.BUDAT_MKPF >= '20240101' 
          AND W.WH IS NOT NULL 
          AND w.V_WH IS NOT NULL; 
		  
		  
-- 3. Export inventory.csv
 
python oracleExport_adjustment.py