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