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