InvAdjustment_Hist.sql

Source: DMF/PuC/AIF/BKP/InvAdjustment_Hist.sql

SELECT 
  m.MATNR as ITEM, 
  oracle_id 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 * 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') >= TO_DATE(m.BUDAT_MKPF, 'YYYYMMDD') and TO_CHAR(pc.end_date, 'YYYYMMDD') < TO_DATE(m.BUDAT_MKPF, 'YYYYMMDD') 
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';