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;
/