CREATE OR REPLACE PACKAGE BODY dp_inv_pkg AS

 

  /*****************************************************************************

  Purpose:  This package loads and transforms Dashboard Inventory Metric items.

            Currently the data is read from extract files and EUC tables.

           

  Usage:    Module                        Purpose

            ---------------               -----------

            run_process                  Builds ALL DP Inventory metrics.

              ld_dp_inv_master_mnp        Loads dp_inv_master_mnp.

              ld_dp_inv_master_pr1        Loads dp_inv_master_pr1.

              ld_dp_inv_gl                Loads dp_inv_gl.

              ld_dp_inv_prg               Loads dp_inv_prg.

              ld_dp_inv_reserves_mnp      Loads dp_inv_reserves_mnp.

           

  Info:     The package instruments it's status in V$SESSION.

            The package produces the following Inventory metric objects:

           

            Name                    Type       Description

            ----                    ----       -----------

            dp_inv_reserves_mnp     history    table of dp_inv_reserves_mnp_stg snapshots.

            dp_inv_gl               history    table of dp_inv_gl_stg snapshots.

            dp_inv_prg              history    table of dp_inv_prg_stg snapshots.

            dp_inv_master_pr1       history    table of dp_inv_master_pr1_stg snapshots.

            dp_inv_master_mnp       history    table of dp_inv_master_nmp_stg snapshots.

                                               and EUC Inventory/Material schema.

                                          

            Business Object Views created outside this package:

           

            Name                        Description

            ----                        -----------

            dp_inv_master_mv            merges MNP/PR1 Master histories.

            dp_inv_master_current_mv    current MNP/PR1 Master data.

            dp_inv_master_totals_mv     contains dp_inv_master_vw totals.

            dp_inv_wip_aging_mv         contains WIP/COMM Proj/Aging sums.

            dp_inv_prg_mv               presents Progress Payment history.

            dp_inv_gl_mv                presents GL history.

            dp_inv_aop_uct_vw           presents AOP history.

            dp_inv_rofo_uct_vw          presents ROFO history.

            dp_inv_actuals_mv           presents ACTUALS sums.

                                                                                                                             

  History

  -------

  25-Feb-2004 JJM:

  1) added DP_UTILS.dbg. removed inv_dbg.

    

  18-Feb-2004 JJM:

  1) added ld_dp_inv_reserves_mnp

 

  15-Feb-2004 JJM:

  1) added Kindschi's hst_check.

 

  04-jan-2004 JJM:

  1) added ld_inv_master.

 

  03-Jan-2004 JJM:

  1) added: ld_dp_inv_prg, ld_inv_gl and ld_dp_inv_reject_sink.

          

  03-Dec-2003 JJM:

  1) Initial checkin.

  *****************************************************************************/

 

  -------------------------------------------------------------------------------

  -- ld_dp_inv_reject_sink

  -- stores rows rejected during loads/transformations.

  -------------------------------------------------------------------------------

  PROCEDURE ld_dp_inv_reject_sink(p_module IN VARCHAR2,

                                  p_reason IN VARCHAR2,

                                  p_rec    IN VARCHAR2) IS

 

  BEGIN

    --age off msg's older than 15 days

    DELETE FROM EUC.dp_inv_reject_sink

     WHERE trunc(reject_dt) < trunc(SYSDATE - 15);

 

    BEGIN

      INSERT INTO EUC.dp_inv_reject_sink

        (module, reason, rec, reject_dt)

      VALUES

        (p_module, p_reason, p_rec, SYSDATE);

   

    EXCEPTION

      WHEN OTHERS THEN

        DP_UTILS.dbg(DP_UTILS.c_inv_metric,

                     'Can''t insert into dp_inv_reject_sink. ' || SQLERRM);

    END;

    COMMIT;

 

  END ld_dp_inv_reject_sink;

 

  --------------------------------------------------------------------------------

  -- ld_dp_inv_prg

  -- append table dp_inv_prg from staging and EUC.

  --------------------------------------------------------------------------------

  PROCEDURE ld_dp_inv_prg IS

 

    l_reject_count NUMBER DEFAULT 0;

    l_record_count NUMBER DEFAULT 0;

    l_commit_rate  NUMBER DEFAULT 1000;

 

    CURSOR cur_stg IS

      SELECT * FROM EUC.dp_inv_prg_stg s;

 

  BEGIN

    DP_UTILS.dbg(DP_UTILS.c_inv_metric, 'ld_dp_inv_prg: Begin');

 

    -- get master inventory extracts from SAP

    DBMS_SNAPSHOT.REFRESH('EUC.dp_inv_prg_stg');

 

    FOR REC IN cur_stg LOOP

      l_record_count := l_record_count + 1;

      IF (MOD(l_record_count, l_commit_rate) = 0) THEN

        DP_UTILS.dbg(DP_UTILS.c_inv_metric,

                     'Appending record ' || l_record_count ||

                     ' to dp_inv_prg');

        COMMIT;

      END IF;

   

      BEGIN

        INSERT INTO EUC.dp_inv_prg

        VALUES

          (TRIM(REC.REC_TYPE),

           TRIM(REC.REC_CATEGORY),

           TRIM(REC.RUNDATE),

           TRIM(REC.SAP_SYSTEM_ID),

           TRIM(REC.ORIGIN_DOCUMENT),

           TRIM(REC.VALUE),

           TRIM(REC.BILL_PLAN_NUMBER),

           TRIM(REC.BILLING_DATE),

           TRIM(REC.PROGRESS_PMT),

           TRIM(REC.PROGRESS_PMT_USED),

           TRIM(REC.MODALITY),

           TRIM(REC.ZONE_CODE),

           TRIM(REC.ZONE_DESC),

           TRIM(REC.INVOICE_DATE),

           TRIM(REC.STORAGE_LOC),

           TRIM(REC.STORAGE_LOC_NAME),

           TRIM(REC.SALES_DOCUMENT_N0));

      EXCEPTION

        WHEN DUP_VAL_ON_INDEX THEN

          NULL;

        WHEN OTHERS THEN

          l_reject_count := l_reject_count + 1;

          ld_dp_inv_reject_sink('ld_dp_inv_prg',

                                SQLERRM,

                                REC.REC_TYPE || '|' || REC.REC_CATEGORY || '|' ||

                                REC.RUNDATE || '|' || REC.SAP_SYSTEM_ID || '|' ||

                                REC.ORIGIN_DOCUMENT || '|' || REC.VALUE || '|' ||

                                REC.BILL_PLAN_NUMBER || '|' ||

                                REC.BILLING_DATE || '|' || REC.PROGRESS_PMT || '|' ||

                                REC.PROGRESS_PMT_USED || '|' ||

                                REC.MODALITY || '|' || REC.ZONE_CODE || '|' ||

                                REC.ZONE_DESC || '|' || REC.INVOICE_DATE || '|' ||

                                REC.STORAGE_LOC || '|' ||

                                REC.STORAGE_LOC_NAME || '|' ||

                                REC.SALES_DOCUMENT_N0 || '|');

      END;

    END LOOP;

 

    IF (l_reject_count > 0) THEN

      DP_UTILS.dbg(DP_UTILS.c_inv_metric,

                   'ld_dp_inv_prg: rejected ' || l_reject_count ||

                   ' records. ' || 'See dp_ld_dp_inv_reject_sink');

    END IF;

    COMMIT;

 

  END ld_dp_inv_prg;

 

  --------------------------------------------------------------------------------

  -- ld_dp_inv_master

  -- append table dp_inv_master from MNP and EUC.

  --------------------------------------------------------------------------------

  PROCEDURE ld_dp_inv_master_mnp IS

 

    l_reject_count NUMBER DEFAULT 0;

    l_record_count NUMBER DEFAULT 0;

    l_commit_rate  NUMBER DEFAULT 1000;

 

    l_bg_desc  VARCHAR2(40);

    l_bu_desc  VARCHAR2(40);

    l_mag_desc VARCHAR2(40);

    l_ag_desc  VARCHAR2(40);

 

    CURSOR cur_mnp_stg IS

    --get a recordset from staging, ref table and MATERIALS

      SELECT DISTINCT A.*,

                      B.MATERIAL_DESCRIPTION,

                      NVL(C.INVENTORY_BL, 'MISC') AS BUSINESS_LINE

        FROM EUC.DP_INV_MASTER_MNP_STG A,

             EUC.PMSNA_MATERIALS       B,

             EUC.DP_INV_BL_BU_LU       C

       WHERE A.MATERIAL_ID = B.MATERIAL(+) AND a.bu_code = c.bu_code(+);

 

  BEGIN

    DP_UTILS.dbg(DP_UTILS.c_inv_metric, 'ld_dp_inv_master_mnp: Begin');

 

    -- get master inventory snapshots from MNP

    DBMS_SNAPSHOT.REFRESH('EUC.dp_inv_master_MNP_stg');

 

    FOR REC IN cur_mnp_stg LOOP

      l_record_count := l_record_count + 1;

      IF (MOD(l_record_count, l_commit_rate) = 0) THEN

        DP_UTILS.dbg(DP_UTILS.c_inv_metric,

                     'Appending record ' || l_record_count ||

                     ' to dp_inv_master_MNP');

        COMMIT;

      END IF;

   

      BEGIN

        -- Add BG, BU, MAG, AG names

        -- (also keeps the cursor trivial)

        l_bg_desc  := 'NULL';

        l_bu_desc  := 'NULL';

        l_mag_desc := 'NULL';

        l_ag_desc  := 'NULL';

        BEGIN

          SELECT bg_desc, bu_desc, mag_desc, ag_desc

            INTO l_bg_desc, l_bu_desc, l_mag_desc, l_ag_desc

            FROM EUC.pmsna_sap_product_hierarchy

           WHERE REC.bg_code = bg_code AND rownum = 1;

       

        EXCEPTION

          WHEN NO_DATA_FOUND THEN

            DP_UTILS.dbg(DP_UTILS.c_inv_metric,

                         'Can`t find Product Hierarchy for |' ||

                         REC.REC_TYPE || '|' || REC.REC_CATEGORY || '|' ||

                         REC.SALES_DOCUMENT_NO || '|' || REC.MATERIAL_ID || '|');

        END;

     

        INSERT INTO EUC.dp_inv_master_mnp

        VALUES

          (TRIM(REC.REC_TYPE),

           TRIM(REC.REC_CATEGORY),

           TRIM(REC.RUNDATE),

           TRIM(REC.SAP_SYSTEM_ID),

           TRIM(REC.SALES_DOCUMENT_NO),

           TRIM(REC.SALES_LINE_ITEM),

           TRIM(REC.MATERIAL_ID),

           NVL(REC.MATERIAL_DESCRIPTION,

               'No Description in PMSNA_MATERIALS'),

           TRIM(REC.PLANT),

           TRIM(REC.STORAGE_LOC),

           TRIM(REC.STORAGE_LOC_NAME),

           REC.QTY,

           REC.VALUE,

           TRIM(REC.PO_DOCUMENT_NO),

           TRIM(REC.PO_LINE_ITEM),

           REC.PROGRESS_PMT,

           TRIM(REC.BG_CODE),

           TRIM(l_bg_desc),

           REC.BUSINESS_LINE,

           TRIM(REC.BU_CODE),

           TRIM(l_bu_desc),

           TRIM(REC.MAG_CODE),

           TRIM(l_mag_desc),

           TRIM(REC.AG_CODE),

           TRIM(l_ag_desc),

           TRIM(REC.SAP_CUST_NO),

           TRIM(REC.ACCOUNT_NAME),

           TRIM(REC.CITY),

           TRIM(REC.STATE),

           TRIM(REC.ZIP),

           TRIM(REC.ZONE_CODE),

           TRIM(REC.ZONE_DESC),

           TRIM(REC.REGION_CODE),

           TRIM(REC.REGION_DESC),

           TRIM(REC.POST_YEAR),

           TRIM(REC.POST_MONTH),

           TRIM(REC.LAST_MAT_MOVE_DATE),

           TRIM(REC.PROJ_USAGE_DATE),

           SUBSTR(REC.SAP_CREATE_DATE, 1, 8),

           decode(REC.REC_TYPE,

                  'WIP',

                  substr(REC.sap_create_date, 5, 2),

                  'DEMO',

                  substr(REC.sap_create_date, 5, 2),

                  'IN_TRANSIT',

                  substr(REC.sap_create_date, 5, 2),

                  'FREE',

                  substr(REC.last_mat_move_date, 5, 2),

                  'OTHER',

                  substr(REC.last_mat_move_date, 5, 2),

                  'FSE',

                  substr(REC.last_mat_move_date, 5, 2),

                  '00'),

           decode(REC.REC_TYPE,

                  'WIP',

                  substr(REC.sap_create_date, 1, 4),

                  'DEMO',

                  substr(REC.sap_create_date, 1, 4),

                  'IN_TRANSIT',

                  substr(REC.sap_create_date, 1, 4),

                  'FREE',

                  substr(REC.last_mat_move_date, 1, 4),

                  'OTHER',

                  substr(REC.last_mat_move_date, 1, 4),

                  'FSE',

                  substr(REC.last_mat_move_date, 1, 4),

                  '0000'));

      EXCEPTION

        WHEN DUP_VAL_ON_INDEX THEN

          NULL;

        WHEN OTHERS THEN

          l_reject_count := l_reject_count + 1;

          ld_dp_inv_reject_sink('ld_dp_inv_master_MNP',

                                SQLERRM,

                                REC.REC_TYPE || '|' || REC.REC_CATEGORY || '|' ||

                                REC.RUNDATE || '|' || REC.SAP_SYSTEM_ID || '|' ||

                                REC.SALES_DOCUMENT_NO || '|' ||

                                REC.SALES_LINE_ITEM || '|' ||

                                REC.MATERIAL_ID || '|' ||

                                REC.MATERIAL_DESCRIPTION || '|' ||

                                REC.PLANT || '|' || REC.STORAGE_LOC || '|' ||

                                REC.STORAGE_LOC_NAME || '|' || REC.QTY || '|' ||

                                REC.VALUE || '|' || REC.PO_DOCUMENT_NO || '|' ||

                                REC.PO_LINE_ITEM || '|' || REC.PROGRESS_PMT || '|' ||

                                REC.BG_CODE || '|' || l_bg_desc || '|' ||

                                REC.BU_CODE || '|' || l_bu_desc || '|' ||

                                REC.MAG_CODE || '|' || l_mag_desc || '|' ||

                                REC.AG_CODE || '|' || l_ag_desc || '|' ||

                                REC.SAP_CUST_NO || '|' || REC.ACCOUNT_NAME || '|' ||

                                REC.CITY || '|' || REC.STATE || '|' ||

                                REC.ZIP || '|' || REC.ZONE_CODE || '|' ||

                                REC.ZONE_DESC || '|' || REC.REGION_CODE || '|' ||

                                REC.REGION_DESC || '|' || REC.POST_YEAR || '|' ||

                                REC.POST_MONTH || '|' ||

                                REC.LAST_MAT_MOVE_DATE || '|' ||

                                REC.PROJ_USAGE_DATE || '|' ||

                                REC.SAP_CREATE_DATE || '|');

      END;

    END LOOP;

 

    IF (l_reject_count > 0) THEN

      DP_UTILS.dbg(DP_UTILS.c_inv_metric,

                   'ld_dp_inv_master_MNP: rejected ' || l_reject_count ||

                   ' records. See dp_ld_dp_inv_reject_sink');

    END IF;

    COMMIT;

 

  END ld_dp_inv_master_mnp;

 

  --------------------------------------------------------------------------------

  -- ld_dp_inv_master_pr1

  -- append table dp_inv_master from PR1.

  -- This procedure exists primarily because PR1 extract

  -- is wildly different them MNP in terms of KEYS and FORMAT.

  --------------------------------------------------------------------------------

  PROCEDURE ld_dp_inv_master_pr1 IS

 

    l_mnp_rundate    VARCHAR2(8);

    l_transfer_price NUMBER DEFAULT 2.4;

 

    l_reject_count NUMBER DEFAULT 0;

    l_record_count NUMBER DEFAULT 0;

    l_commit_rate  NUMBER DEFAULT 1000;

 

    CURSOR cur_mnp_stg IS

      SELECT DISTINCT a.*, NVL(b.inventory_bl, 'MISC') AS "BUSINESS_LINE"

        FROM EUC.DP_INV_MASTER_PR1_STG a, EUC.DP_INV_BL_BU_LU B

       WHERE a.bu_code = b.bu_code(+);

 

  BEGIN

    DP_UTILS.dbg(DP_UTILS.c_inv_metric, 'ld_dp_inv_master_pr1: Begin');

 

    -- get master inventory snapshots from PR1

    DBMS_SNAPSHOT.REFRESH('EUC.dp_inv_master_PR1_stg');

 

    -- get last RUNDATE from MNP

    BEGIN

      SELECT RUNDATE

        INTO l_mnp_rundate

        FROM EUC.dp_inv_master_MNP_stg

       WHERE rownum = 1;

   

    EXCEPTION

      WHEN NO_DATA_FOUND THEN

        SELECT RUNDATE

          INTO l_mnp_rundate

          FROM EUC.dp_inv_master_PR1_stg

         WHERE rownum = 1;

    END;

 

    -- get last TRANSFER PRICE from dp_constants

    BEGIN

      SELECT to_number(VALUE)

        INTO l_transfer_price

        FROM EUC.dp_constants

       WHERE KEY = 'TRANSFER PRICE' AND METRIC = DP_UTILS.c_inv_metric;

   

    EXCEPTION

      WHEN NO_DATA_FOUND THEN

        DP_UTILS.dbg(DP_UTILS.c_inv_metric,

                     'ld_dp_inv_master_pr1: ' ||

                     'Can`t find TRANSFER PRICE in dp_constants.  ' ||

                     'Using ' || l_transfer_price);

      WHEN OTHERS THEN

        DP_UTILS.dbg(DP_UTILS.c_inv_metric,

                     'ld_dp_inv_master_pr1: ' ||

                     'TRANSFER PRICE in dp_constants is INVALID. ' ||

                     'Using ' || l_transfer_price);

    END;

 

    FOR REC IN cur_mnp_stg LOOP

      l_record_count := l_record_count + 1;

      IF (MOD(l_record_count, l_commit_rate) = 0) THEN

        DP_UTILS.dbg(DP_UTILS.c_inv_metric,

                     'Appending record ' || l_record_count ||

                     ' to dp_inv_master_pr1');

        COMMIT;

      END IF;

   

      BEGIN

        INSERT INTO EUC.dp_inv_master_pr1

        VALUES

          (TRIM(REC.REC_NUMBER),

           TRIM(REC.REC_TYPE),

           TRIM(REC.REC_CATEGORY),

           l_mnp_rundate,

           TRIM(REC.RUNDATE),

           TRIM(REC.SAP_SYSTEM_ID),

           TRIM(REC.SALES_DOCUMENT_NO),

           TRIM(REC.SALES_LINE_ITEM),

           TRIM(REC.MATERIAL_ID),

           TRIM(REC.MATERIAL_DESC),

           TRIM(REC.PLANT),

           TRIM(REC.STORAGE_LOC),

           TRIM(REC.STORAGE_LOC_NAME),

           REC.QTY,

           -- REC.VALUE * l_transfer_price,

           REC.VALUE,

           0,

           TRIM(REC.PO_DOCUMENT_NO),

           TRIM(REC.PO_LINE_ITEM),

           REC.PROGRESS_PMT,

           TRIM(REC.BG_CODE),

           TRIM(REC.BG_DESC),

           REC.BUSINESS_LINE,

           TRIM(REC.BU_CODE),

           TRIM(REC.BU_DESC),

           TRIM(REC.MAG_CODE),

           TRIM(REC.MAG_DESC),

           TRIM(REC.AG_CODE),

           TRIM(REC.AG_DESC),

           TRIM(REC.SAP_CUST_NO),

           TRIM(REC.ACCOUNT_NAME),

           TRIM(REC.CITY),

           TRIM(REC.STATE),

           TRIM(REC.ZIP),

           TRIM(REC.ZONE_CODE),

           TRIM(REC.ZONE_DESC),

           TRIM(REC.REGION_CODE),

           TRIM(REC.REGION_DESC),

           TRIM(REC.POST_YEAR),

           TRIM(REC.POST_MONTH),

           TRIM(REC.LAST_MAT_MOVE_DATE),

           TRIM(REC.PROJ_USAGE_DATE),

           SUBSTR(REC.SAP_CREATE_DATE, 1, 8),

           DECODE(REC.REC_TYPE,

                  'DEMO',

                  substr(REC.sap_create_date, 5, 2),

                  'RCA',

                  substr(REC.sap_create_date, 5, 2),

                  'FSE',

                  substr(REC.last_mat_move_date, 5, 2),

                  '00'),

           DECODE(REC.REC_TYPE,

                  'DEMO',

                  substr(REC.sap_create_date, 1, 4),

                  'RCA',

                  substr(REC.sap_create_date, 1, 4),

                  'FSE',

                  substr(REC.last_mat_move_date, 1, 4),

                  '0000'));

      EXCEPTION

        WHEN DUP_VAL_ON_INDEX THEN

          NULL;

        WHEN OTHERS THEN

          l_reject_count := l_reject_count + 1;

          ld_dp_inv_reject_sink('ld_dp_inv_master_PR1',

                                SQLERRM,

                                REC.REC_NUMBER || '|' || REC.REC_TYPE || '|' ||

                                REC.REC_CATEGORY || '|' || REC.RUNDATE || '|' ||

                                REC.SAP_SYSTEM_ID || '|' ||

                                REC.SALES_DOCUMENT_NO || '|' ||

                                REC.SALES_LINE_ITEM || '|' ||

                                REC.MATERIAL_ID || '|' || REC.MATERIAL_DESC || '|' ||

                                REC.PLANT || '|' || REC.STORAGE_LOC || '|' ||

                                REC.STORAGE_LOC_NAME || '|' || REC.QTY || '|' ||

                                REC.VALUE || '|' || REC.PO_DOCUMENT_NO || '|' ||

                                REC.PO_LINE_ITEM || '|' || REC.PROGRESS_PMT || '|' ||

                                REC.BG_CODE || '|' || REC.BG_DESC || '|' ||

                                REC.BU_CODE || '|' || REC.BU_DESC || '|' ||

                                REC.MAG_CODE || '|' || REC.MAG_DESC || '|' ||

                                REC.AG_CODE || '|' || REC.AG_DESC || '|' ||

                                REC.SAP_CUST_NO || '|' || REC.ACCOUNT_NAME || '|' ||

                                REC.CITY || '|' || REC.STATE || '|' ||

                                REC.ZIP || '|' || REC.ZONE_CODE || '|' ||

                                REC.ZONE_DESC || '|' || REC.REGION_CODE || '|' ||

                                REC.REGION_DESC || '|' || REC.POST_YEAR || '|' ||

                                REC.POST_MONTH || '|' ||

                                REC.LAST_MAT_MOVE_DATE || '|' ||

                                REC.PROJ_USAGE_DATE || '|' ||

                                REC.SAP_CREATE_DATE || '|');

      END;

    END LOOP;

 

    IF (l_reject_count > 0) THEN

      DP_UTILS.dbg(DP_UTILS.c_inv_metric,

                   'ld_dp_inv_master_PR1: rejected ' || l_reject_count ||

                   ' records. See dp_ld_dp_inv_reject_sink');

    END IF;

    COMMIT;

 

  END ld_dp_inv_master_pr1;

 

  --------------------------------------------------------------------------------

  -- ld_dp_inv_gl

  -- append table dp_inv_gl from staging and EUC.

  --------------------------------------------------------------------------------

  PROCEDURE ld_dp_inv_gl IS

 

    l_reject_count NUMBER DEFAULT 0;

    l_record_count NUMBER DEFAULT 0;

    l_commit_rate  NUMBER DEFAULT 1000;

 

    CURSOR cur_stg IS

      SELECT s.* FROM EUC.dp_inv_gl_stg s;

 

  BEGIN

    DP_UTILS.dbg(DP_UTILS.c_inv_metric, 'ld_dp_inv_gl: Begin');

 

    -- get GL inventory extract from SAP

    DBMS_SNAPSHOT.REFRESH('EUC.dp_inv_gl_stg');

 

    FOR REC IN cur_stg LOOP

      l_record_count := l_record_count + 1;

      IF (MOD(l_record_count, l_commit_rate) = 0) THEN

        DP_UTILS.dbg(DP_UTILS.c_inv_metric,

                     'Appending record ' || l_record_count ||

                     ' to dp_inv_gl');

        COMMIT;

      END IF;

   

      BEGIN

     

        INSERT INTO EUC.dp_inv_gl

        VALUES

          (TRIM(REC.REC_TYPE),

           TRIM(REC.REC_CATEGORY),

           TRIM(REC.RUNDATE),

           TRIM(REC.SAP_SYSTEM_ID),

           TRIM(REC.COMPANY_CODE),

           TRIM(REC.GL_ACCOUNT_NUMBER),

           TRIM(REC.GL_ACCOUNT_NAME),

           TRIM(REC.GL_FISCAL_YEAR),

           TRIM(REC.GL_FISCAL_PERIOD),

           REC.DEBITS_PERIOD,

           REC.CREDITS_PERIOD,

           REC.SALES_PERIOD,

           REC.GL_ACCOUNT_BALANCE);

      EXCEPTION

        WHEN DUP_VAL_ON_INDEX THEN

          NULL;

        WHEN OTHERS THEN

          l_reject_count := l_reject_count + 1;

          ld_dp_inv_reject_sink('ld_dp_inv_gl',

                                SQLERRM,

                                REC.REC_TYPE || '|' || REC.REC_CATEGORY || '|' ||

                                REC.RUNDATE || '|' || REC.SAP_SYSTEM_ID || '|' ||

                                REC.COMPANY_CODE || '|' ||

                                REC.GL_ACCOUNT_NUMBER || '|' ||

                                REC.GL_ACCOUNT_NAME || '|' ||

                                REC.GL_FISCAL_YEAR || '|' ||

                                REC.GL_FISCAL_PERIOD || '|' ||

                                REC.DEBITS_PERIOD || '|' ||

                                REC.CREDITS_PERIOD || '|' ||

                                REC.SALES_PERIOD || '|' ||

                                REC.GL_ACCOUNT_BALANCE || '|');

      END;

    END LOOP;

 

    IF (l_reject_count > 0) THEN

      DP_UTILS.dbg(DP_UTILS.c_inv_metric,

                   'ld_dp_inv_gl: rejected ' || l_reject_count ||

                   ' records. See dp_ld_dp_inv_reject_sink');

    END IF;

    COMMIT;

 

  END ld_dp_inv_gl;

 

  --------------------------------------------------------------------------------

  -- ld_dp_inv_reserves_mnp

  -- append table dp_inv_reserves_mnp from MNP.

  --------------------------------------------------------------------------------

  PROCEDURE ld_dp_inv_reserves_mnp IS

 

    l_mnp_rundate         VARCHAR2(8);

    l_profit_center_group VARCHAR2(20);

 

    l_reject_count NUMBER DEFAULT 0;

    l_record_count NUMBER DEFAULT 0;

    l_commit_rate  NUMBER DEFAULT 1000;

    l_len          NUMBER DEFAULT 0;

 

    CURSOR cur_stg IS

      SELECT s.* FROM EUC.dp_inv_reserves_mnp_stg s;

 

  BEGIN

    DP_UTILS.dbg(DP_UTILS.c_inv_metric, 'ld_dp_inv_reserves_mnp: Begin');

 

    -- get Reserves snapshot from MNP

    DBMS_SNAPSHOT.REFRESH('EUC.dp_inv_reserves_mnp_stg');

 

    -- get the MNP rundate

    BEGIN

      SELECT RUNDATE

        INTO l_mnp_rundate

        FROM EUC.dp_inv_master_MNP_stg

       WHERE rownum = 1;

   

    EXCEPTION

      WHEN NO_DATA_FOUND THEN

        SELECT RUNDATE

          INTO l_mnp_rundate

          FROM EUC.dp_inv_reserves_mnp_stg

         WHERE rownum = 1;

    END;

 

    FOR REC IN cur_stg LOOP

      l_record_count := l_record_count + 1;

      IF (MOD(l_record_count, l_commit_rate) = 0) THEN

        DP_UTILS.dbg(DP_UTILS.c_inv_metric,

                     'Appending record ' || l_record_count ||

                     ' to dp_inv_reserves_mnp');

        COMMIT;

      END IF;

   

      BEGIN

     

        --extract Inventory BL strings from profit_center_group 

        l_len                 := LENGTH(REC.PROFIT_CENTER_GROUP);

        l_profit_center_group := '';

        --get all the characters after the first space character

        --rather then parsing a static size that could change.

        FOR i IN (instr(REC.PROFIT_CENTER_GROUP, ' ') + 1) .. l_len LOOP

          l_profit_center_group := l_profit_center_group ||

                                   substr(REC.PROFIT_CENTER_GROUP, i, 1);

        END LOOP;

     

        INSERT INTO EUC.dp_inv_reserves_mnp

        VALUES

          (TRIM(REC.REC_TYPE),

           TRIM(REC.REC_CATEGORY),

           l_mnp_rundate,

           TRIM(REC.RUNDATE),

           TRIM(REC.SAP_SYSTEM_ID),

           TRIM(REC.COMPANY_CODE),

           TRIM(REC.GL_ACCOUNT_NUMBER),

           TRIM(REC.GL_FISCAL_YEAR),

           nvl(TRIM(REC.PROFIT_CENTER), 'N/A'),

           TRIM(REC.PROFIT_CENTER_GROUP),

           TRIM(REC.VALUE),

           decode(l_profit_center_group,

                  'X-R',

                  'GXR',

                  'Und',

                  'GXR',

                  'U/S',

                  'ULTRASOUND',

                  'Nuc',

                  'ULTRASOUND',

                  l_profit_center_group));

     

      EXCEPTION

        --WHEN DUP_VAL_ON_INDEX THEN

        -- NULL;

        WHEN OTHERS THEN

          l_reject_count := l_reject_count + 1;

          ld_dp_inv_reject_sink('ld_dp_inv_reserves_mnp',

                                SQLERRM,

                                REC.REC_TYPE || '|' || REC.REC_CATEGORY || '|' ||

                                REC.RUNDATE || '|' || REC.SAP_SYSTEM_ID || '|' ||

                                REC.COMPANY_CODE || '|' ||

                                REC.GL_ACCOUNT_NUMBER || '|' ||

                                REC.GL_FISCAL_YEAR || '|' ||

                                REC.PROFIT_CENTER || '|' ||

                                REC.PROFIT_CENTER_GROUP || '|' || REC.VALUE || '|');

      END;

    END LOOP;

 

    IF (l_reject_count > 0) THEN

      DP_UTILS.dbg(DP_UTILS.c_inv_metric,

                   'ld_dp_inv_reserves_mnp: rejected ' || l_reject_count ||

                   ' records. See dp_ld_dp_inv_reject_sink');

    END IF;

    COMMIT;

 

  END ld_dp_inv_reserves_mnp;

 

  /* -----------------------------------------------------------------------------

  --  ld_actuals_into_mnp

  --  adhoc code to seed history (past periods) from ACTUALS spreadsheets.

  ----------------------------------------------------------------------------- 

  PROCEDURE ld_actuals_into_mnp IS

      

    l_counter   NUMBER DEFAULT 0;   

      

    CURSOR cur_stg IS

     SELECT s.*

     FROM   EUC.dp_inv_actuals s;  

  

   BEGIN  

  

   FOR REC IN cur_STG LOOP

     l_counter := l_counter + 1;  

     INSERT INTO EUC.dp_inv_master_mnp

        VALUES    (  

                  decode(trim(REC.INVENTORY_TYPE),

                         'GROSS WIP', 'WIP',

                         'PROGRESS BILLINGS', 'PROGRESS_BILLINGS',

                         trim(REC.INVENTORY_TYPE)),

                  'BOTHELL',

                  '20040101',

                  'MNP',

                  l_counter,

                  l_counter,

                  l_counter,

                  'SEED MNP',

                  'PLT',

                  l_counter,

                  'LOC NAME',

                  0,

                  REC.PERIOD_01,

                  1,

                  1,

                  1,

                  'BG',

                  'BG',

                  REC.BUSINESS_LINE,

                  'BU',

                  'BU',

                  'MAG',

                  'MAG',

                  'AG',

                  'AG',

                   1,

                  'ACCOUNT_NAME',

                  'CITY',

                  'OH',

                  'ZIP',

                  'ZONE',

                  'ZONE',

                  'REG',

                  'REG',

                  '2004',

                  '01',

                  '2004',

                  '2004',

                  '2004',

                  '01',

                  '2004');

  END LOOP; 

               

   COMMIT; 

   END ld_actuals_into_mnp;*/

 

  --------------------------------------------------------------------------------

  -- updmviews

  -- append table dp_inv_gl from staging and EUC.

  --------------------------------------------------------------------------------

  PROCEDURE updmviews IS

  BEGIN

    DP_UTILS.dbg(DP_UTILS.c_inv_metric, 'updmviews: Begin');

    BEGIN

      dbms_snapshot.refresh('dp_inv_master_mv,' ||

                            'dp_inv_master_current_mv,' ||

                            'dp_inv_prg_mv,' || 'dp_inv_gl_mv,' ||

                            'dp_inv_master_totals_mv,' ||

                            'dp_inv_actuals_mv,' || 'dp_inv_reserves_mv');

    EXCEPTION

      WHEN OTHERS THEN

        DP_UTILS.dbg(DP_UTILS.c_inv_metric, 'updmviews: ' || SQLERRM);

    END;

  END updmviews;

 

  --------------------------------------------------------------------------------

  -- gen_dp_inventory

  -- build non-rofo/aop objects.

  --------------------------------------------------------------------------------

  PROCEDURE run_process IS

  BEGIN

    DP_UTILS.dbg(DP_UTILS.c_inv_metric, 'run_process: Begin');

 

    -- remove duplicate datasets.

    DP_UTILS.hst_check(DP_UTILS.c_inv_metric, 'dp_inv_aop');

    DP_UTILS.hst_check(DP_UTILS.c_inv_metric, 'dp_inv_rofo');

    DP_UTILS.hst_check(DP_UTILS.c_inv_metric, 'dp_inv_actuals');

 

    -- build PROGRESS Payment views

    ld_dp_inv_prg;

 

    -- build GL Views

    ld_dp_inv_gl;

 

    -- build Reserves views

    ld_dp_inv_reserves_mnp;

 

    -- build Inventory Master views from MNP and EUC

    ld_dp_inv_master_mnp;

 

    -- build Inventory Master views from PR1

    -- ld_dp_inv_master_mnp should run first.

    ld_dp_inv_master_pr1;

 

    --materialize views

    updmviews;

 

    DP_UTILS.dbg(DP_UTILS.c_inv_metric, 'run_process: Begin');

  END run_process;

 

  -------------------------------------------------------------------------------- 

  -- public test module 

  --------------------------------------------------------------------------------

  PROCEDURE test IS

  BEGIN

    --updmviews;

    ld_dp_inv_master_pr1;

  END test;

 

-------------------------------------------------------------------------------- 

-- dp_inv_pkg Initialization 

--------------------------------------------------------------------------------

BEGIN

 

  DP_UTILS.dbg(DP_UTILS.c_inv_metric, 'DP_INV_PKG is Starting');

 

END dp_inv_pkg;

/