cost.sql

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

-- COST --
 
-- 1. Insert into CTRL (ORI_COST_CTRL) table:
 
--exclusive brands (pv1)
INSERT INTO ORI_COST_CTRL(ITEM
                         ,ORG_NUM
                         ,DAY_DT
                         ,SUPPLIER_NUM
                         ,BASE_COST_AMT_LCL
                         ,NET_COST_AMT_LCL
                         ,NET_NET_COST_AMT_LCL
                         ,DEAD_NET_COST_AMT_LCL
                         ,DOC_CURR_CODE
                         ,LOC_CURR_CODE
                         ,DATASOURCE_NUM_ID)
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
      ,LTRIM(fc.LOC, '0') ORG_NUM
      ,TO_DATE(fc.DATAB, 'YYYYMMDD') DAY_DT
      ,LTRIM(fc.LIFNR, '0') 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
      ,1 DATASOURCE_NUM_ID
  FROM future_cost fc;    
 
 
-- OWN BRAND (PR1)
INSERT INTO ORI_COST_CTRL(ITEM
                         ,ORG_NUM
                         ,DAY_DT
                         ,SUPPLIER_NUM
                         ,BASE_COST_AMT_LCL
                         ,NET_COST_AMT_LCL
                         ,NET_NET_COST_AMT_LCL
                         ,DEAD_NET_COST_AMT_LCL
                         ,DOC_CURR_CODE
                         ,LOC_CURR_CODE
						 ,DATASOURCE_NUM_ID)
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'),  
     a084_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
                         ,a084_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
      ,LTRIM(fc.LOC, '0') ORG_NUM
      ,TO_DATE(fc.DATAB, 'YYYYMMDD') DAY_DT
      ,LTRIM(fc.LIFNR, '0') 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
	  ,1 DATASOURCE_NUM_ID
  FROM future_cost fc;   
 
 
 
-- 3. Export COST.csv
 
python oracleExport_cost.py