Cost_Hist.sql

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

--exclusive brands (pv1)
WITH isup as (
        select ims.item, ims.supplier, ime.dispo, xref.sap_id
          from item_master_ctrl im
               ,item_supplier_ctrl ims
               ,PUC_SKU_IM_EXCL_BRANDS_TMP ime
               ,xref_sups xref
               ,sups_ctrl ctrl
        where im.item = ims.item
          and im.ctrl_status = 'C'
          and ime.item = im.item
          and xref.oracle_id = ims.supplier
          and ctrl.supplier = ims.supplier
          and ctrl.ctrl_status = 'C'),  
     a018_src AS ( SELECT a.DATAB
				         ,a.LIFNR
				         ,a.KNUMH
                         ,a.KSCHL
                         ,a.MANDT 
                         ,i.item
                     FROM SAP_SLT_PROD_PV1.A018 a   
                          ,isup i
                    WHERE a.KSCHL = 'YPBE'
                      AND a.DATBI >= '20240101'
                      --AND a.DATBI < '99991231'
                      AND a.EKORG IN ('0253')
                      AND i.DISPO = a.MATNR
                      AND i.sap_id = a.LIFNR),
     konp_src AS ( SELECT a.item
                         ,a.DATAB
                         ,k.KONWA
                         ,a.LIFNR
                         ,k.KBETR
                     FROM SAP_SLT_PROD_PV1.KONP k
                         ,a018_src a
                    WHERE K.KSCHL = a.KSCHL
                      AND k.KNUMH = a.KNUMH
                      AND k.MANDT = a.MANDT),                       
  future_cost AS ( SELECT ks.KBETR 
                         ,ks.KONWA
                         ,ks.ITEM
                         ,ks.DATAB
                         ,ks.LIFNR
                         ,il.LOC
                     FROM ITEM_LOC_CTRL il, 
                          konp_src ks 
                    WHERE ks.ITEM = il.ITEM
                      and il.ctrl_status = 'C')
SELECT /*+ PARALLEL(8) */
       fc.ITEM
      ,fc.LOC ORG_NUM
      ,fc.DATAB DAY_DT
      ,fc.LIFNR SUPPLIER_NUM
      ,fc.KBETR BASE_COST_AMT_LCL
      ,fc.KBETR NET_COST_AMT_LCL
      ,fc.KBETR NET_NET_COST_AMT_LCL
      ,fc.KBETR DEAD_NET_COST_AMT_LCL
      ,fc.KONWA DOC_CURR_CODE
      ,'EUR' LOC_CURR_CODE
  FROM future_cost fc; --13554404
 
 
-- OWN BRAND (PR1)
WITH isup as (
        select im.item, ims.supplier, ime.dispo, xref.sap_id, xrefi.dispo_pr1, xrefi.item_pr1
          from item_master_ctrl im
               ,item_supplier_ctrl ims
               ,PUC_SKU_IM_OWN_BRANDS_TMP ime
               ,xref_sups xref
               ,xref_item_ibc_retail xrefi
               ,sups_ctrl ctrl
        where im.item = ims.item
          and im.ctrl_status = 'C'
          and ime.item = im.item
          and xref.oracle_id = ims.supplier
          and xrefi.item_pv1 = im.item
          and ctrl.supplier = ims.supplier
          and ctrl.ctrl_status = 'C'),  
     a018_src AS ( SELECT a.DATAB
				         ,a.LIFNR
				         ,a.KNUMH
                         ,a.KSCHL
                         ,a.MANDT 
                         ,i.item
                     FROM SAP_SLT_PROD_PR1.a084 a   
                          ,isup i
                    WHERE a.KSCHL = 'ZBRP'
                      AND a.DATBI >= '20240101'
                      --AND a.DATBI < '99991231'
                      --AND a.EKORG IN ('0253')
                      AND i.dispo_pr1 = a.MATNR
                      AND i.sap_id = a.LIFNR),
     konp_src AS ( SELECT a.item
                         ,a.DATAB
                         ,k.KONWA
                         ,a.LIFNR
                         ,k.KBETR
                     FROM SAP_SLT_PROD_PR1.KONP k
                         ,a018_src a
                    WHERE K.KSCHL = a.KSCHL
                      AND k.KNUMH = a.KNUMH
                      AND k.MANDT = a.MANDT),                       
  future_cost AS ( SELECT ks.KBETR 
                         ,ks.KONWA
                         ,ks.ITEM
                         ,ks.DATAB
                         ,ks.LIFNR
                         ,il.LOC
                     FROM ITEM_LOC_CTRL il, 
                          konp_src ks 
                    WHERE ks.ITEM = il.ITEM
                      and il.ctrl_status = 'C'
                      )
SELECT /*+ PARALLEL(8) */
       fc.ITEM
      ,fc.LOC ORG_NUM
      ,fc.DATAB DAY_DT
      ,fc.LIFNR SUPPLIER_NUM
      ,fc.KBETR BASE_COST_AMT_LCL
      ,fc.KBETR NET_COST_AMT_LCL
      ,fc.KBETR NET_NET_COST_AMT_LCL
      ,fc.KBETR DEAD_NET_COST_AMT_LCL
      ,fc.KONWA DOC_CURR_CODE
      ,'EUR' LOC_CURR_CODE
  FROM future_cost fc; --2289719