AUTHID CURRENT_USER AS
/**************************************************************************
*
* $Header$
*
* PURPOSE: This package aggregates sales data from the
* CMG_PPA_WH_GL_SALES_AGGR_TBL to CMG_PPA_GL_SALES_AGGR_TBL.
* This process is run daily.
*
* This package body is organized in the following sections:
*
* 1 - Private global variables and cursors
*
* 2 - Private procedures/functions
*
* 3 - Public procedures/functions
*
* MODIFICATION HISTORY
* Person Date Comments LUN#
* ----------- -------- ---------------------------- --------------
* 10/9/2002 Initial Development
*
* PVCS CHECK-IN HISTORY
*
* $Log$
*
***************************************************************************/
PROCEDURE aggregate_main
( p_errbuf OUT VARCHAR2 ,
p_retcode OUT VARCHAR2,
p_pub_id IN NUMBER,
p_title_dim IN NUMBER,
p_issue_dim IN NUMBER );
/**************************************************************************
*
* PURPOSE: This is the main procedure for aggregating CMG_PPA_WH_GL_SALES_AGGR_TBL
* to the CMG_PPA_GL_SALES_AGGR_TBL. The basic logic is:
*
* FOR all_wh_sales_records.aggregated_flg = 'N'
* IF sales_aggr not exists THEN
* insert_saless;
* ELSE
* update_saless ( l_sales_aggr_id, l_sum_sales_qty )
* END IF
* update_wh_saless
*
*
* PARAMETERS:
* p_errbuf - Standard Oracle parameter
* p_retcode - Standard Oracle parameter
*
***************************************************************************/
END cmg_ppa_load_gl_sales_pck;
/
CREATE OR REPLACE PACKAGE BODY
APPS.cmg_ppa_load_gl_sales_pck
AS
/**************************************************************************
*
* $Header$
*
* PURPOSE: This package aggregates GL sales data drom the
* CMG_PPA_WH_gl_sales_AGGR_TBL to CMG_PPA_GL_SALES_AGGR_TBL.
* This process is run daily.
*
* This package body is organized in the following sections:
*
* 1 - Private global variables and cursors
*
* 2 - Private procedures/functions
*
* 3 - Public procedures/functions
*
* MODIFICATION HISTORY
* Person Date Comments LUN#
* ----------- -------- ---------------------------- --------------
* 10/9/2002 Initial Development
8/25/05 Add Parameters
* 10/19/05 Performance tuning
* 11/3/05 Performance tuning - II
* 11/7/05 SCR 3829 - Remove Duplicate copy count
* 4/23/06 SCR 3902 - Title not assigned error
* 7/21/06 SCR 4678 - PFR Aggregation errors
* 11/2006 SCR 4774 - Performance tuning.
* 01/2007 SCR 5169 - update_wh_gl_sales SQL change
* 06/2007 SCR 5169 - unit_pvb
* 04/03/2008 SCR 5853 - Permanent Fix for Unit PBV Calculation caused by Subcontract Status
* 09/18/2008 SCR 6669- Permanent Fix for SCR 6649 ( Aggregation errors caused by exceptional Brokerage)
* PVCS CHECK-IN HISTORY
*
* $Log$
*
***************************************************************************/
-- Section 1 - Declare private variables/cursors
-- variables
g_unit_pbv cmg_ppa_sales_pbv_tbl.unit_pbv%TYPE; -- SCR 5169
-- cursors
CURSOR c_wh_gl_sales_aggr(p_pub_id IN NUMBER
,p_title_dim IN NUMBER
,p_issue_dim IN NUMBER) IS
SELECT cpwg.title_dim_id,
cpwg.issue_dim_id,
cpwg.order_code,
cpwg.cover_price,
cpwg.selling_price,
cpwg.csr_flg,
cpwg.currency_code,
cpwg.exchange_rate,
cpwg.payment_exchange_rate,--c2c test
cpwg.profitcenter,--c2c test
cpwg.org_id,--c2c test
cpwg.gl_period AS accounting_month,
cpwg.final_flg,
cpws.unit_pbv,
cpws.commission_pct AS brok_percent,
cpws.brok_type,
cpws.brok_basis,
cpws.wholesaler_pct,
cpws.pub_remit_pct,
SUM(NVL(cpwg.copies,0)) AS copies
FROM cmg_ppa_wh_gl_sales_aggr_tbl cpwg,
(SELECT a.title_dim_id,
a.issue_dim_id,
a.curr_bill_to_acct,
a.cover_price,
a.selling_price,
a.order_code,
a.csr_flg,
-- a.accounting_month,
a.currency_code,
-- a.exchange_rate,
a.profitcenter,--c2c
a.org_id,--c2c
b.unit_pbv,
b.commission_pct,
b.brok_type,
b.brok_basis,
b.wholesaler_pct,
b.pub_remit_pct
FROM cmg_ppa_wh_sales_aggr_tbl a,
cmg_ppa_sales_pbv_tbl b
WHERE a.unit_pbv_interface = 'Y'
AND a.wh_sales_aggr_id = b.wh_sales_aggr_id
AND trunc(sysdate) BETWEEN trunc(b.start_date) AND trunc(b.end_date)
AND b.sales_pbv_id = (SELECT MAX(sales_pbv_id)
FROM cmg_ppa_sales_pbv_tbl
WHERE wh_sales_aggr_id = a.wh_sales_aggr_id
AND trunc(sysdate) BETWEEN trunc(start_date) AND trunc(end_date))
GROUP BY a.title_dim_id,
a.issue_dim_id,
a.curr_bill_to_acct,
a.cover_price,
a.selling_price,
a.order_code,
a.csr_flg,
-- a.accounting_month,
a.currency_code,
-- a.exchange_rate,
a.profitcenter,
a.org_id,
b.unit_pbv,
b.commission_pct,
b.brok_type,
b.brok_basis,
b.wholesaler_pct,
b.pub_remit_pct ) cpws
,cmg_ppa_issue_dim_tbl cpi
,cmg_ppa_contract_tbl cpc -- SCR 5853
,cmg_ppa_subcontract_tbl cps -- SCR 5853
WHERE cpwg.aggregated_flg = 'N'
AND cpwg.title_dim_id = cpws.title_dim_id
AND cpwg.issue_dim_id = cpws.issue_dim_id
AND cpwg.issue_dim_id = cpi.issue_dim_id
AND cpi.iss_subctr_id= cps.subctr_id
AND cps.ctr_id=cpc.ctr_id
AND cpi.iss_pub_id = NVL(p_pub_id,cpi.iss_pub_id)
AND cpwg.issue_dim_id = NVL(p_issue_dim,cpwg.issue_dim_id)
AND cpwg.title_dim_id = NVL(p_title_dim,cpwg.title_dim_id)
AND cpwg.curr_bill_to_acct = cpws.curr_bill_to_acct
AND cpwg.cover_price = cpws.cover_price
AND cpwg.selling_price = cpws.selling_price
AND cpwg.order_code = cpws.order_code
AND cpwg.org_id =cpws.org_id
AND cpwg.profitcenter=cpws.profitcenter
-- AND cpwg.gl_period = cpws.accounting_month
AND cpwg.csr_flg = cpws.csr_flg
AND cpwg.currency_code = cpws.currency_code
-- AND cpwg.exchange_rate = cpws.exchange_rate
AND cpc.ctr_status='Y' -- SCR 5853
AND cps.subctr_status='Y' -- SCR 5853
GROUP BY cpwg.title_dim_id,
cpwg.issue_dim_id,
cpwg.order_code,
cpwg.cover_price,
cpwg.selling_price,
cpwg.csr_flg,
cpwg.currency_code,
cpwg.exchange_rate,
cpwg.payment_exchange_rate, --c2c test
cpwg.profitcenter,--c2c test
cpwg.org_id,--c2c test
cpwg.gl_period,
cpwg.final_flg,
cpws.unit_pbv,
cpws.commission_pct,
cpws.brok_type,
cpws.brok_basis,
cpws.wholesaler_pct,
cpws.pub_remit_pct;
CURSOR c_gl_sales_aggr ( p_title_dim_id NUMBER,
p_issue_dim_id NUMBER,
p_order_code VARCHAR2,
p_cover_price NUMBER,
p_selling_price NUMBER,
p_csr_flg VARCHAR2,
p_currency_code VARCHAR2,
p_exchange_rate NUMBER,
p_payment_exchange_rate NUMBER,--C2C TEST
p_profitcenter NUMBER,--C2C TEST
p_org_id NUMBER,--C2C TEST
p_unit_pbv NUMBER,
p_accounting_month VARCHAR2,
p_final_flg VARCHAR2 ) IS
SELECT gl_sales_aggr_id
FROM cmg_ppa_gl_sales_aggr_tbl
WHERE title_dim_id = p_title_dim_id
AND issue_dim_id = p_issue_dim_id
AND order_code = p_order_code
AND cover_price = p_cover_price
AND selling_price = p_selling_price
AND csr_flg = p_csr_flg
AND currency_code = p_currency_code
AND exchange_rate = p_exchange_rate
AND payment_exchange_rate = p_payment_exchange_rate --c2c
AND profitcenter = p_profitcenter--c2c
AND org_id = p_org_id--c2c
AND unit_pbv = p_unit_pbv
AND accounting_month = p_accounting_month
AND final_flg = p_final_flg;
CURSOR c_wh_sum_gl_sales ( p_title_dim_id NUMBER,
p_issue_dim_id NUMBER,
p_order_code VARCHAR2,
p_cover_price NUMBER,
p_selling_price NUMBER,
p_csr_flg VARCHAR2,
p_currency_code VARCHAR2,
p_exchange_rate NUMBER,
p_payment_exchange_rate NUMBER,--C2C TEST
p_profitcenter NUMBER,--C2C TEST
p_org_id NUMBER,--c2c
p_unit_pbv NUMBER,
p_accounting_month VARCHAR2,
p_final_flg VARCHAR2 ) IS
SELECT SUM(NVL(cpwg.copies,0)) AS copies
FROM cmg_ppa_wh_gl_sales_aggr_tbl cpwg,
(SELECT a.title_dim_id,
a.issue_dim_id,
a.curr_bill_to_acct,
a.cover_price,
a.selling_price,
b.unit_pbv
FROM cmg_ppa_wh_sales_aggr_tbl a,
cmg_ppa_sales_pbv_tbl b
WHERE a.unit_pbv_interface = 'Y'
AND a.wh_sales_aggr_id = b.wh_sales_aggr_id
AND TRUNC(SYSDATE) BETWEEN TRUNC(b.start_date) AND TRUNC(b.end_date)
GROUP BY a.title_dim_id,
a.issue_dim_id,
a.curr_bill_to_acct,
a.cover_price,
a.selling_price,
b.unit_pbv ) cpws
WHERE cpwg.aggregated_flg = 'N' -- SCR# 4678
AND cpwg.gl_sales_aggr_id is null -- SCR# 4678
AND cpwg.final_flg = p_final_flg
AND cpwg.title_dim_id = p_title_dim_id
AND cpwg.issue_dim_id = p_issue_dim_id
AND cpwg.order_code = p_order_code
AND cpwg.cover_price = p_cover_price
AND cpwg.selling_price = p_selling_price
AND cpwg.csr_flg = p_csr_flg
AND cpwg.currency_code = p_currency_code
AND cpwg.exchange_rate = p_exchange_rate
AND cpwg.payment_exchange_rate =p_payment_exchange_rate --c2c teset
AND cpwg.profitcenter = p_profitcenter --c2c test
AND cpwg.org_id = p_org_id --c2c
AND cpwg.gl_period = p_accounting_month
AND cpws.unit_pbv = p_unit_pbv
AND cpwg.title_dim_id = cpws.title_dim_id
AND cpwg.issue_dim_id = cpws.issue_dim_id
AND cpwg.curr_bill_to_acct = cpws.curr_bill_to_acct
AND cpwg.cover_price = cpws.cover_price
AND cpwg.selling_price = cpws.selling_price;
-- Section 2 - Declare private functions/procedures
FUNCTION lookup_gl_sales_aggr_id
( p_wh_gl_sales_aggr_rec IN c_wh_gl_sales_aggr%ROWTYPE)
RETURN NUMBER IS
-----------------------------------------------------------------
--
-- PURPOSE: This function will return the gl_sales_aggr_id from th
-- cmg_ppa_gl_sales_aggr_tbl if wholesale aggregates exists
--
-----------------------------------------------------------------
l_progress VARCHAR2(30) := '0010';
l_procedure VARCHAR2(60) := 'cmg_ppa_load_gl_sales_pck.lookup_gl_sales_aggr_id';
l_gl_sales_aggr_id cmg_ppa_gl_sales_aggr_tbl.gl_sales_aggr_id%TYPE;
BEGIN
OPEN c_gl_sales_aggr ( p_wh_gl_sales_aggr_rec.title_dim_id,
p_wh_gl_sales_aggr_rec.issue_dim_id,
p_wh_gl_sales_aggr_rec.order_code,
p_wh_gl_sales_aggr_rec.cover_price,
p_wh_gl_sales_aggr_rec.selling_price,
p_wh_gl_sales_aggr_rec.csr_flg,
p_wh_gl_sales_aggr_rec.currency_code,
p_wh_gl_sales_aggr_rec.exchange_rate,
p_wh_gl_sales_aggr_rec.payment_exchange_rate,--c2c test
p_wh_gl_sales_aggr_rec.profitcenter,--c2c test
p_wh_gl_sales_aggr_rec.org_id,--c2c test
p_wh_gl_sales_aggr_rec.unit_pbv,
p_wh_gl_sales_aggr_rec.accounting_month,
p_wh_gl_sales_aggr_rec.final_flg);
l_progress := '0020';
FETCH c_gl_sales_aggr INTO l_gl_sales_aggr_id;
l_progress := '0030';
CLOSE c_gl_sales_aggr;
l_progress := '0050';
RETURN l_gl_sales_aggr_id;
EXCEPTION
WHEN no_data_found THEN
IF c_gl_sales_aggr%ISOPEN THEN
CLOSE c_wh_gl_sales_aggr;
END IF;
RETURN l_gl_sales_aggr_id;
WHEN others THEN
cmg_ppa_log_pck.writesqlerrlog( l_procedure , l_progress ) ;
IF c_gl_sales_aggr%ISOPEN THEN
CLOSE c_wh_gl_sales_aggr;
END IF;
RAISE;
END lookup_gl_sales_aggr_id; -- end of function
PROCEDURE build_gl_sales_aggr_record
( p_wh_gl_sales_aggr_rec IN c_wh_gl_sales_aggr%ROWTYPE,
p_gl_sales_aggr_rec OUT cmg_ppa_gl_sales_aggr_tbl%ROWTYPE ) IS
-----------------------------------------------------------------
--
-- PURPOSE: This procedure will build the sales aggregate record from the
-- C_WH_GL_SALES_AGGR cursor record and the default values.
--
-----------------------------------------------------------------
l_progress VARCHAR2(30) := '0010';
l_procedure VARCHAR2(60) := 'cmg_ppa_load_gl_sales_pck.build_gl_sales_aggr_record';
BEGIN
-- define "who" data
p_gl_sales_aggr_rec.creation_date := SYSDATE;
p_gl_sales_aggr_rec.created_by := fnd_global.user_id;
p_gl_sales_aggr_rec.last_update_date := SYSDATE;
p_gl_sales_aggr_rec.last_updated_by := fnd_global.user_id;
-- define normal data
l_progress := '0020';
p_gl_sales_aggr_rec.title_dim_id := p_wh_gl_sales_aggr_rec.title_dim_id;
p_gl_sales_aggr_rec.issue_dim_id := p_wh_gl_sales_aggr_rec.issue_dim_id;
p_gl_sales_aggr_rec.order_code := p_wh_gl_sales_aggr_rec.order_code;
p_gl_sales_aggr_rec.csr_flg := p_wh_gl_sales_aggr_rec.csr_flg;
p_gl_sales_aggr_rec.currency_code := p_wh_gl_sales_aggr_rec.currency_code;
l_progress := '0030';
p_gl_sales_aggr_rec.exchange_rate := p_wh_gl_sales_aggr_rec.exchange_rate;
p_gl_sales_aggr_rec.payment_exchange_rate :=p_wh_gl_sales_aggr_rec.payment_exchange_rate;--c2c test
p_gl_sales_aggr_rec.profitcenter :=p_wh_gl_sales_aggr_rec.profitcenter;--c2c teset
p_gl_sales_aggr_rec.org_id :=p_wh_gl_sales_aggr_rec.org_id;--c2c
p_gl_sales_aggr_rec.cover_price := p_wh_gl_sales_aggr_rec.cover_price;
p_gl_sales_aggr_rec.selling_price := p_wh_gl_sales_aggr_rec.selling_price;
p_gl_sales_aggr_rec.unit_pbv := p_wh_gl_sales_aggr_rec.unit_pbv;
p_gl_sales_aggr_rec.copies := p_wh_gl_sales_aggr_rec.copies;
l_progress := '0040';
p_gl_sales_aggr_rec.unit_pbv := p_wh_gl_sales_aggr_rec.unit_pbv;
p_gl_sales_aggr_rec.brok_type := p_wh_gl_sales_aggr_rec.brok_type;
p_gl_sales_aggr_rec.brok_basis := p_wh_gl_sales_aggr_rec.brok_basis;
p_gl_sales_aggr_rec.brok_percent := p_wh_gl_sales_aggr_rec.brok_percent;
p_gl_sales_aggr_rec.wholesaler_pct := p_wh_gl_sales_aggr_rec.wholesaler_pct;
p_gl_sales_aggr_rec.pub_bill_value := p_wh_gl_sales_aggr_rec.pub_remit_pct;
p_gl_sales_aggr_rec.accounting_month := p_wh_gl_sales_aggr_rec.accounting_month;
p_gl_sales_aggr_rec.final_flg := p_wh_gl_sales_aggr_rec.final_flg;
EXCEPTION
WHEN others THEN
cmg_ppa_log_pck.writesqlerrlog( l_procedure , l_progress ) ;
RAISE;
END build_gl_sales_aggr_record; -- end of procedure
FUNCTION insert_gl_sales
( p_gl_sales_aggr_rec IN cmg_ppa_gl_sales_aggr_tbl%ROWTYPE )
RETURN NUMBER IS
/**************************************************************************
*
* PURPOSE: This function will insert the sales_aggr record into
* cmg_ppa_gl_sales_aggr_tbl and return the sales_aggregate_id.
*
***************************************************************************/
l_progress VARCHAR2(30) := '0010';
l_procedure VARCHAR2(60) := 'cmg_ppa_load_gl_sales_pck.insert_gl_sales';
l_retvalue NUMBER;
BEGIN
INSERT INTO
cmg_ppa_gl_sales_aggr_tbl (
gl_sales_aggr_id,
order_code,
title_dim_id,
issue_dim_id,
csr_flg,
currency_code,
exchange_rate,
payment_exchange_rate,--c2c test
profitcenter,--c2c test
org_id,--c2c
cover_price,
selling_price,
unit_pbv,
wholesaler_pct,
pub_bill_value,
brok_type,
brok_basis,
brok_percent,
accounting_month,
copies,
final_flg,
created_by,
creation_date,
last_updated_by,
last_update_date
)
VALUES (
cmg_ppa_gl_sales_aggr_seq.nextval,
p_gl_sales_aggr_rec.order_code,
p_gl_sales_aggr_rec.title_dim_id,
p_gl_sales_aggr_rec.issue_dim_id,
p_gl_sales_aggr_rec.csr_flg,
p_gl_sales_aggr_rec.currency_code,
p_gl_sales_aggr_rec.exchange_rate,
p_gl_sales_aggr_rec.payment_exchange_rate,--c2c test
p_gl_sales_aggr_rec.profitcenter,--c2c test
p_gl_sales_aggr_rec.org_id,--c2c
p_gl_sales_aggr_rec.cover_price,
p_gl_sales_aggr_rec.selling_price,
p_gl_sales_aggr_rec.unit_pbv,
p_gl_sales_aggr_rec.wholesaler_pct,
p_gl_sales_aggr_rec.pub_bill_value,
p_gl_sales_aggr_rec.brok_type,
p_gl_sales_aggr_rec.brok_basis,
p_gl_sales_aggr_rec.brok_percent,
p_gl_sales_aggr_rec.accounting_month,
p_gl_sales_aggr_rec.copies,
p_gl_sales_aggr_rec.final_flg,
p_gl_sales_aggr_rec.created_by,
p_gl_sales_aggr_rec.creation_date,
p_gl_sales_aggr_rec.last_updated_by,
p_gl_sales_aggr_rec.last_update_date )
RETURNING gl_sales_aggr_id INTO l_retvalue;
RETURN l_retvalue;
EXCEPTION
WHEN others THEN
cmg_ppa_log_pck.writesqlerrlog( l_procedure , l_progress ) ;
RAISE ;
END insert_gl_sales; -- end of function
PROCEDURE update_wh_gl_sales
( p_wh_gl_sales_aggr_rec IN c_wh_gl_sales_aggr%ROWTYPE,
p_gl_sales_aggr_id IN NUMBER ) IS
/**************************************************************************
*
* PURPOSE: This procedure will update the cmg_ppa_wh_gl_sales_aggr_tbl
* with sales_aggr_id
*
***************************************************************************/
l_progress VARCHAR2(30) := '0010';
l_procedure VARCHAR2(60) := 'cmg_ppa_load_gl_sales_pck.update_wh_gl_sales';
l_retvalue NUMBER;
BEGIN
UPDATE cmg_ppa_wh_gl_sales_aggr_tbl cpwg -- SCR 5169
SET cpwg.gl_sales_aggr_id = p_gl_sales_aggr_id,
cpwg.aggregated_flg = 'Y',
cpwg.last_updated_by = fnd_global.user_id,
cpwg.last_update_date = SYSDATE
WHERE cpwg.aggregated_flg = 'N'
AND cpwg.title_dim_id = p_wh_gl_sales_aggr_rec.title_dim_id
AND cpwg.issue_dim_id = p_wh_gl_sales_aggr_rec.issue_dim_id
AND cpwg.order_code = p_wh_gl_sales_aggr_rec.order_code
AND cpwg.cover_price = p_wh_gl_sales_aggr_rec.cover_price
AND cpwg.selling_price = p_wh_gl_sales_aggr_rec.selling_price
AND cpwg.csr_flg = p_wh_gl_sales_aggr_rec.csr_flg
AND cpwg.currency_code = p_wh_gl_sales_aggr_rec.currency_code
AND cpwg.exchange_rate = p_wh_gl_sales_aggr_rec.exchange_rate
AND cpwg.gl_period = p_wh_gl_sales_aggr_rec.accounting_month
AND cpwg.payment_exchange_rate =p_wh_gl_sales_aggr_rec.payment_exchange_rate--c2c test
AND cpwg.profitcenter = p_wh_gl_sales_aggr_rec.profitcenter--c2c test
AND cpwg.org_id = p_wh_gl_sales_aggr_rec.org_id --c2c
AND curr_bill_to_acct IN (SELECT a.curr_bill_to_acct
FROM cmg_ppa_wh_sales_aggr_tbl a,
cmg_ppa_sales_pbv_tbl b
WHERE a.unit_pbv_interface = 'Y'
AND a.wh_sales_aggr_id = b.wh_sales_aggr_id
AND cpwg.final_flg = p_wh_gl_sales_aggr_rec.final_flg
AND cpwg.title_dim_id = a.title_dim_id
AND cpwg.issue_dim_id = a.issue_dim_id
AND cpwg.curr_bill_to_acct = a.curr_bill_to_acct
AND cpwg.cover_price = a.cover_price
AND cpwg.selling_price = a.selling_price
AND b.end_date >= sysdate -- SCR 6669
AND b.unit_pbv = g_unit_pbv);
EXCEPTION
WHEN others THEN
cmg_ppa_log_pck.writesqlerrlog( l_procedure , l_progress ) ;
RAISE;
END update_wh_gl_sales; -- end of procedure
PROCEDURE update_gl_sales_aggr
( p_gl_sales_aggr_id IN NUMBER,
p_copies IN NUMBER,
p_wh_gl_sales_aggr_rec IN c_wh_gl_sales_aggr%ROWTYPE ) IS
/**************************************************************************
*
* PURPOSE: This procedure will update the cmg_ppa_gl_sales_aggr_tbl
* with the wholesale sum(copies)
*
***************************************************************************/
l_progress VARCHAR2(30) := '0010';
l_procedure VARCHAR2(60) := 'cmg_ppa_load_gl_sales_pck.update_gl_sales_aggr';
l_retvalue NUMBER ;
BEGIN
UPDATE cmg_ppa_gl_sales_aggr_tbl
SET copies = copies + NVL(p_copies,0), --SCR# 4678
unit_pbv = p_wh_gl_sales_aggr_rec.unit_pbv,
wholesaler_pct = p_wh_gl_sales_aggr_rec.wholesaler_pct,
pub_bill_value = p_wh_gl_sales_aggr_rec.pub_remit_pct,
brok_type = p_wh_gl_sales_aggr_rec.brok_type,
brok_basis = p_wh_gl_sales_aggr_rec.brok_basis,
brok_percent = p_wh_gl_sales_aggr_rec.brok_percent,
last_updated_by = fnd_global.user_id,
last_update_date = SYSDATE
WHERE gl_sales_aggr_id = p_gl_sales_aggr_id;
EXCEPTION
WHEN others THEN
cmg_ppa_log_pck.writesqlerrlog( l_procedure , l_progress||' '||p_gl_sales_aggr_id||' pc:'||p_copies ) ;
RAISE;
END update_gl_sales_aggr; -- end of procedure
FUNCTION get_sum_wh_gl_sales
( p_wh_gl_sales_aggr_rec IN c_wh_gl_sales_aggr%ROWTYPE )
RETURN NUMBER IS
/**************************************************************************
*
* PURPOSE: This function will sum the wholesale sales quantities for a
*
*
***************************************************************************/
l_progress VARCHAR2(30) := '0010';
l_procedure VARCHAR2(60) := 'cmg_ppa_load_gl_sales_pck.get_sum_wh_gl_sales';
l_retvalue NUMBER ;
BEGIN
OPEN c_wh_sum_gl_sales ( p_wh_gl_sales_aggr_rec.title_dim_id,
p_wh_gl_sales_aggr_rec.issue_dim_id,
p_wh_gl_sales_aggr_rec.order_code,
p_wh_gl_sales_aggr_rec.cover_price,
p_wh_gl_sales_aggr_rec.selling_price,
p_wh_gl_sales_aggr_rec.csr_flg,
p_wh_gl_sales_aggr_rec.currency_code,
p_wh_gl_sales_aggr_rec.exchange_rate,
p_wh_gl_sales_aggr_rec.payment_exchange_rate,--c2c test
p_wh_gl_sales_aggr_rec.profitcenter,--c2c test
p_wh_gl_sales_aggr_rec.org_id, --c2c
p_wh_gl_sales_aggr_rec.unit_pbv,
p_wh_gl_sales_aggr_rec.accounting_month,
p_wh_gl_sales_aggr_rec.final_flg );
l_progress := '0020';
FETCH c_wh_sum_gl_sales INTO l_retvalue;
l_progress := '0030';
CLOSE c_wh_sum_gl_sales;
RETURN l_retvalue;
EXCEPTION
WHEN others THEN
cmg_ppa_log_pck.writesqlerrlog( l_procedure , l_progress ) ;
IF c_wh_sum_gl_sales%ISOPEN THEN
CLOSE c_wh_sum_gl_sales;
END IF;
RAISE ;
END get_sum_wh_gl_sales; --end of function
-- Section 3 - Declare public functions/procedures
PROCEDURE aggregate_main
( p_errbuf OUT VARCHAR2 ,
p_retcode OUT VARCHAR2 ,
p_pub_id IN NUMBER,
p_title_dim IN NUMBER,
p_issue_dim IN NUMBER) IS
/**************************************************************************
*
* PURPOSE: This is the main procedure for aggregating cmg_ppa_wh_gl_sales_aggr_tbl
* to the cmg_ppa_gl_sales_aggr_tbl. The basic logic is:
*
* FOR all_wh_gl_sales_records.aggregated_flg = 'N'
* IF sales_aggr not exists THEN
* insert_gl_sales;
* ELSE
* update_gl_sales ( l_gl_sales_aggr_id, l_sum_copies )
* END IF
* update_wh_gl_sales
*
*
* PARAMETERS:
* p_errbuf - Standard Oracle parameter
* p_retcode - Standard Oracle parameter
*
***************************************************************************/
l_progress VARCHAR2(30) := '0010';
l_procedure VARCHAR2(60) := 'cmg_ppa_load_gl_sales_pck.aggregate_main';
l_sum_copies NUMBER;
l_gl_sales_aggr_id cmg_ppa_gl_sales_aggr_tbl.gl_sales_aggr_id%TYPE;
l_gl_sales_rec cmg_ppa_gl_sales_aggr_tbl%ROWTYPE;
l_record_count NUMBER := 0;
l_commit_count NUMBER := 2000;
CURSOR total_agg IS -- SCR 4774
SELECT SUM(NVL(ps.copies,0)) copies
FROM cmg_ppa_wh_gl_sales_aggr_tbl ps
WHERE ps.aggregated_flg = 'Y';
CURSOR total_agg_old IS
SELECT SUM(NVL(ps.copies,0)) copies
FROM cmg_ppa_wh_gl_sales_aggr_tbl ps
,(SELECT DISTINCT wh_aggr_id
FROM cmg_ppa_total_tbl
WHERE source = 'ERROR_FINAL_OLD'
AND report_flag = 'Y') tot
WHERE ps.aggregated_flg = 'Y'
AND ps.wh_gl_sales_aggr_id = tot.wh_aggr_id;
CURSOR total_err IS -- SCR 4774
SELECT /*+ INDEX(w cmg_ppa_wh_gl_sales_aggr_i1) */
NVL(SUM(NVL(w.copies,0)),0) copies
FROM cmg_ppa_wh_gl_sales_aggr_tbl w
WHERE w.aggregated_flg = 'N';
CURSOR total_err_old IS -- SCR 4774
SELECT NVL(SUM(NVL(w.copies,0)),0) copies
FROM cmg_ppa_wh_gl_sales_aggr_tbl w
,(SELECT DISTINCT wh_aggr_id
FROM cmg_ppa_total_tbl
WHERE source = 'ERROR_FINAL_OLD'
AND report_flag = 'Y') c
WHERE w.aggregated_flg = 'N'
AND w.wh_gl_sales_aggr_id = c.wh_aggr_id;
CURSOR sel_err IS --SCR 4774
SELECT /*+ INDEX(ps cmg_ppa_wh_gl_sales_aggr_i1) */
sum(NVL(ps.copies,0)) copies
,ps.issue_dim_id
,ps.title_dim_id
,ps.gl_period
,TRUNC(ps.creation_date) cr_date
,ps.wh_gl_sales_aggr_id
FROM cmg_ppa_wh_gl_sales_aggr_tbl ps,
(SELECT wh_gl_sales_aggr_id
FROM cmg_ppa_wh_gl_sales_aggr_tbl
MINUS
SELECT wh_aggr_id
FROM cmg_ppa_total_tbl
WHERE source = 'ERROR_FINAL_OLD'
AND report_flag = 'Y') c
WHERE c.wh_gl_sales_aggr_id = ps.wh_gl_sales_aggr_id
AND ps.aggregated_flg = 'N'
GROUP BY ps.issue_dim_id
,ps.title_dim_id
,ps.gl_period
,TRUNC(ps.creation_date)
,ps.wh_gl_sales_aggr_id;
CURSOR old_err IS
SELECT SUM(NVL(w.copies,0)) copies
,w.issue_dim_id
,w.title_dim_id
,w.gl_period
,TRUNC(w.creation_date) cr_date
,w.wh_gl_sales_aggr_id
FROM cmg_ppa_wh_gl_sales_aggr_tbl w,
cmg_ppa_total_tbl p
WHERE w.aggregated_flg = 'N'
AND p.source = 'ERROR_FINAL_OLD'
AND p.report_flag = 'Y'
AND w.wh_gl_sales_aggr_id = p.wh_aggr_id
GROUP BY w.issue_dim_id
,w.title_dim_id
,w.gl_period
,TRUNC(w.creation_date)
,w.wh_gl_sales_aggr_id;
CURSOR onsale(p_issue IN NUMBER) IS
SELECT on_sale_date
FROM cmg_ppa_issue_dim_tbl
WHERE issue_dim_id = p_issue;
CURSOR title_assigned(p_title_dim_id IN NUMBER) IS
SELECT /*+ INDEX(sub cmg_ppa_title_subctr_pk) */ -- SCR 4774
title_dim_id
,assigned
,subctr_id
FROM cmg_ppa_title_subctr_tbl sub
WHERE title_dim_id = p_title_dim_id
AND TRUNC(SYSDATE) BETWEEN TRUNC(start_date) AND TRUNC(end_date)
ORDER BY subctr_id DESC;
CURSOR issue_range(p_title_dim_id IN NUMBER
,p_issue_dim_id IN NUMBER) IS
SELECT /*+ INDEX(i cmg_ppa_issue_dim_i1) */ -- SCR 4774
i.issue_dim_id
FROM cmg_ppa_issue_dim_tbl i
WHERE i.title_dim_id = p_title_dim_id
AND i.issue_dim_id = p_issue_dim_id
AND EXISTS (SELECT 1
FROM cmg_ppa_title_subctr_tbl t
WHERE t.title_dim_id = i.title_dim_id
AND t.start_date <= i.on_sale_date
AND t.end_date >= i.on_sale_date);
CURSOR ctr_status(p_subctr_id IN NUMBER) IS
SELECT s.subctr_id,
s.subctr_status,
s.ctr_id,
c.ctr_status
FROM cmg_ppa_subcontract_tbl s,
cmg_ppa_contract_tbl c
WHERE s.subctr_id = p_subctr_id
and c.ctr_id = s.ctr_id;
CURSOR ISS_CTR_STATUS(p_issue_dim_id IN NUMBER) IS
select sct.valid_flag,
ct.valid_flag
from cmg_ppa_issue_dim_tbl iss,
cmg_ppa_subcontract_tbl sct,
cmg_ppa_contract_tbl ct
where iss.issue_dim_id=p_issue_dim_id and
iss.iss_subctr_id=sct.subctr_id
and sct.ctr_id=ct.ctr_id;
v_agg_copies NUMBER := 0;
v_agg_old_copies NUMBER := 0;
v_error_copies NUMBER := 0;
v_error_old_copies NUMBER := 0;
v_error_code VARCHAR2(300) := NULL;
v_issue_dim_id NUMBER := NULL;
v_title_dim_id NUMBER := NULL;
v_subctr_id NUMBER := NULL;
v_ctr_id NUMBER := NULL;
v_iss_err_copies NUMBER := 0;
v_onsale DATE := NULL;
v_assigned VARCHAR2(1) := NULL;
v_ctr_status ctr_status%ROWTYPE;
v_subctr_status varchar2(10);
v_contract_status varchar2(10);
BEGIN
cmg_ppa_log_pck.writetimelog('Beginning main procedure of sales aggregate - ' , 1 , 1 );
FOR wh_gl_sales_aggr_rec IN c_wh_gl_sales_aggr(p_pub_id,p_title_dim,p_issue_dim) LOOP
l_record_count := l_record_count + 1;
l_progress := '0020';
l_gl_sales_aggr_id := lookup_gl_sales_aggr_id ( wh_gl_sales_aggr_rec );
g_unit_pbv := wh_gl_sales_aggr_rec.unit_pbv; --SCR 5169
IF l_gl_sales_aggr_id IS NULL THEN
l_progress := '0030';
build_gl_sales_aggr_record ( wh_gl_sales_aggr_rec, l_gl_sales_rec );
l_progress := '0040';
l_gl_sales_aggr_id := insert_gl_sales ( l_gl_sales_rec );
cmg_ppa_log_pck.writesqlerrlog( l_procedure , l_progress||' '||l_gl_sales_aggr_id );
ELSE
l_progress := '0050';
l_sum_copies := get_sum_wh_gl_sales ( wh_gl_sales_aggr_rec );
l_progress := '0060';
update_gl_sales_aggr ( l_gl_sales_aggr_id, l_sum_copies, wh_gl_sales_aggr_rec );
cmg_ppa_log_pck.writesqlerrlog( l_procedure , l_progress||' '||l_gl_sales_aggr_id );
END IF;
l_progress := '0070';
update_wh_gl_sales ( wh_gl_sales_aggr_rec, l_gl_sales_aggr_id );
IF MOD(l_record_count,l_commit_count) = 0 THEN
COMMIT;
END IF;
END LOOP;
p_retcode := 0 ;
p_errbuf := 'Finished processing wholesale sales records.';
COMMIT;
cmg_ppa_log_pck.writelnlog( l_record_count || ' gl sales record(s) processed inserted.' , 1 , 1 );
cmg_ppa_log_pck.writetimelog('Ending main procedure of sales aggregate - ' , 1 , 1 );
OPEN total_agg;
FETCH total_agg INTO v_agg_copies;
CLOSE total_agg;
OPEN total_agg_old;
FETCH total_agg_old INTO v_agg_old_copies;
CLOSE total_agg_old;
v_agg_copies := v_agg_copies - v_agg_old_copies; --SCR 4774
OPEN total_err;
FETCH total_err INTO v_error_copies;
CLOSE total_err;
OPEN total_err_old;
FETCH total_err_old INTO v_error_old_copies;
CLOSE total_err_old;
v_error_copies := v_error_copies - v_error_old_copies; --SCR 4774
INSERT INTO cmg_ppa_total_tbl (
source ,
creation_date,
copies,
error_copies,
error_code ,
issue_dim_id,
report_flag,
acc_month,
trx_date,
ppa_total_id)
VALUES (
'AGG_FINAL',
(SYSDATE),
v_agg_copies,
v_error_copies,
NULL,
NULL,
'N',
NULL,
NULL,
0);
IF v_error_copies <> 0 THEN
FOR err IN sel_err LOOP
OPEN title_assigned(err.title_dim_id);
FETCH title_assigned INTO v_title_dim_id,v_assigned,v_subctr_id;
CLOSE title_assigned;
OPEN issue_range(err.title_dim_id,err.issue_dim_id);
FETCH issue_range INTO v_issue_dim_id;
CLOSE issue_range;
OPEN ctr_status(v_subctr_id);
FETCH ctr_status INTO v_ctr_status;
CLOSE ctr_status;
IF v_title_dim_id IS NULL THEN
v_error_code := 'Either Title not attached to Contract or Issue outside Contract Date range ';
ELSIF v_assigned = 'N' THEN
v_error_code := 'Title not assigned to Contract';
ELSIF v_assigned = 'Y' AND v_issue_dim_id IS NULL THEN
v_error_code := 'Issue outside Contract Date range ';
ELSIF v_ctr_status.subctr_status = 'N' THEN
v_error_code := 'Subcontract Unapproved ';
ELSIF v_ctr_status.ctr_status = 'N' THEN
v_error_code := 'Contract Unapproved ';
ELSE
v_error_code := 'Error - Please contact IT';
END IF;
INSERT INTO cmg_ppa_total_tbl (
source ,
creation_date,
copies,
error_copies,
error_code ,
issue_dim_id,
report_flag,
acc_month,
trx_date,
ppa_total_id,
wh_aggr_id)
VALUES (
'ERROR_FINAL',
(SYSDATE),
0,
err.copies,
v_error_code,
err.issue_dim_id,
'N',err.gl_period,err.cr_date,0,err.wh_gl_sales_aggr_id);
END LOOP;
END IF;
FOR err IN old_err LOOP
OPEN title_assigned(err.title_dim_id);
FETCH title_assigned INTO v_title_dim_id,v_assigned,v_subctr_id;
CLOSE title_assigned;
OPEN issue_range(err.title_dim_id,err.issue_dim_id);
FETCH issue_range INTO v_issue_dim_id;
CLOSE issue_range;
OPEN ctr_status(v_subctr_id);
FETCH ctr_status INTO v_ctr_status;
CLOSE ctr_status;
IF v_title_dim_id IS NULL THEN
v_error_code := 'Either Title not attached to Contract or Issue outside Contract Date range ';
ELSIF v_assigned = 'N' THEN
v_error_code := 'Title not assigned to Contract';
ELSIF v_assigned = 'Y' AND v_issue_dim_id IS NULL THEN
v_error_code := 'Issue outside Contract Date range ';
ELSIF v_ctr_status.subctr_status = 'N' THEN
v_error_code := 'Subcontract Unapproved ';
ELSIF v_ctr_status.ctr_status = 'N' THEN
v_error_code := 'Contract Unapproved ';
ELSE
v_error_code := 'Error - Please contact IT';
END IF;
INSERT INTO cmg_ppa_total_tbl (
source ,
creation_date,
copies,
error_copies,
error_code ,
issue_dim_id,
report_flag,
acc_month,
trx_date,
ppa_total_id,
wh_aggr_id)
VALUES (
'ERROR_FINAL_OLD',
(SYSDATE),
0,
err.copies,
v_error_code,
err.issue_dim_id,
'N',
err.gl_period,
err.cr_date,
0,
err.wh_gl_sales_aggr_id);
END LOOP;
COMMIT;
EXCEPTION
WHEN others THEN
ROLLBACK;
p_retcode := 2 ;
p_errbuf := 'When others error occurred. See log file below for details';
cmg_ppa_log_pck.writesqlerrlog( l_procedure , l_progress ) ;
END aggregate_main; -- end of procedure
END cmg_ppa_load_gl_sales_pck; -- end of package body/
/
No comments:
Post a Comment