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),