Oracle PL/SQL Startup

Sunday, February 12, 2017

Out Bound Interface

CREATE OR REPLACE PROCEDURE PROCEDURE_TEST2(errbuf out number,retcode out number) is
Cursor C1 is select * from XXX_test;
V_outpath Varchar2(100);
V_header VARCHAR2(500);
V_line VARCHAR2(1000);
V_REQ_ID NUMBER DEFAULT (FND_GLOBAL.CONC_REQUEST_ID);
V_file_name VArchar2(100):='Test_file'||'.csv';
V_outfile UTL_file.file_type;
V_sql_code VARCHAR2(100);
V_err_msg VARCHAR2(100);
V_SUBJECT VARCHAR2 (100)
:= 'TEST_file--->' || TO_CHAR (SYSDATE, 'YYMMDDHHMISS');
V_USER_ID NUMBER DEFAULT (FND_GLOBAL.USER_ID);
V_USERNAME VArchar2(30);
V_EMAIL Varchar2(50);
begin
SELECT USER_NAME, EMAIL_ADDRESS
INTO V_USERNAME, V_EMAIL
FROM FND_USER
WHERE USER_ID = V_USER_ID;

fnd_profile.get('X_test',V_outpath);
V_outfile:= utl_file.fopen(V_outpath,V_file_name,'W');
V_header:='NAME'||','||'ID';
UTL_FILE.put_line(V_outfile,V_header);
for Cur in C1 loop
V_line:=Cur.NAME||','||Cur.id;
UTL_FILE.put_line(V_outfile,V_Line);
end loop;
UTL_FILE.fclose(V_outfile);
IF V_EMAIL IS NULL
THEN
CMG_UTILITY_PCK.WRITE_OUTPUT (
'EMAIL ADDRESS IS NOT SETUP FOR THIS USER. '
|| 'PLEASE CONTACT THE SETUP ADMINISTRATOR');
ELSE
CMG_UTILITY_PCK.WRITE_OUTPUT ('FILE CREATED SUCCESSFULLY');
-- EMAIL THE DETAIL FILE TO EMAIL ADDRESS FOR THIS USER.
CMG_ABC_SUBMIT_PCK.MAIN (V_REQ_ID,
V_OUTPATH || '/' || V_FILE_NAME,
V_FILE_NAME,
V_SUBJECT,
V_EMAIL);

CMG_UTILITY_PCK.WRITE_OUTPUT ('FILE EMAILED SUCCESSFULLY');
END IF;
Exception
When others then
V_sql_code:=SQLcode;
V_err_msg:=SQLERRm;
dbms_output.put_line('Follow the error code and error message'||V_SQL_CODE||','||V_err_msg);
end;
/

-------------------------------------------------------------------------------------------------
PROCEDURE MAIN(p_reqid IN NUMBER,
p_dir IN VARCHAR2,
p_file IN VARCHAR2,
p_subject IN VARCHAR2,
p_email IN VARCHAR2) AS

v_reqid NUMBER;

BEGIN

v_reqid := Fnd_Request.SUBMIT_REQUEST(
'CMGABC',
'CMGABC_EMAIL',
'Send E Mail from Server',
'',
FALSE,
p_dir,
p_file,
p_subject,
p_email
);
COMMIT;

cmg_abc_submit_pck.cmg_wait_req_prc(v_reqid);

EXCEPTION
WHEN OTHERS THEN
RAISE;
END;

END cmg_abc_submit_pck;
--------------------------------------------------------------------------------------------

function submit_request (
application IN varchar2 default NULL,
program IN varchar2 default NULL,
description IN varchar2 default NULL,
start_time IN varchar2 default NULL,
sub_request IN boolean default FALSE,
argument1 IN varchar2 default CHR(0),
argument2 IN varchar2 default CHR(0),
argument3 IN varchar2 default CHR(0),
argument4 IN varchar2 default CHR(0),
argument5 IN varchar2 default CHR(0),
argument6 IN varchar2 default CHR(0),
argument7 IN varchar2 default CHR(0),
argument8 IN varchar2 default CHR(0),
argument9 IN varchar2 default CHR(0),
argument10 IN varchar2 default CHR(0),
argument11 IN varchar2 default CHR(0),
argument12 IN varchar2 default CHR(0),
argument13 IN varchar2 default CHR(0),
argument14 IN varchar2 default CHR(0),
argument15 IN varchar2 default CHR(0),
argument16 IN varchar2 default CHR(0),
argument17 IN varchar2 default CHR(0),
argument18 IN varchar2 default CHR(0),
argument19 IN varchar2 default CHR(0),
argument20 IN varchar2 default CHR(0),
argument21 IN varchar2 default CHR(0),
argument22 IN varchar2 default CHR(0),
argument23 IN varchar2 default CHR(0),
argument24 IN varchar2 default CHR(0),
argument25 IN varchar2 default CHR(0),
argument26 IN varchar2 default CHR(0),
argument27 IN varchar2 default CHR(0),
argument28 IN varchar2 default CHR(0),
argument29 IN varchar2 default CHR(0),
argument30 IN varchar2 default CHR(0),
argument31 IN varchar2 default CHR(0),
argument32 IN varchar2 default CHR(0),
argument33 IN varchar2 default CHR(0),
argument34 IN varchar2 default CHR(0),
argument35 IN varchar2 default CHR(0),
argument36 IN varchar2 default CHR(0),
argument37 IN varchar2 default CHR(0),
argument38 IN varchar2 default CHR(0),
argument39 IN varchar2 default CHR(0),
argument40 IN varchar2 default CHR(0),
argument41 IN varchar2 default CHR(0),
argument42 IN varchar2 default CHR(0),
argument43 IN varchar2 default CHR(0),
argument44 IN varchar2 default CHR(0),
argument45 IN varchar2 default CHR(0),
argument46 IN varchar2 default CHR(0),
argument47 IN varchar2 default CHR(0),
argument48 IN varchar2 default CHR(0),
argument49 IN varchar2 default CHR(0),
argument50 IN varchar2 default CHR(0),
argument51 IN varchar2 default CHR(0),
argument52 IN varchar2 default CHR(0),
argument53 IN varchar2 default CHR(0),
argument54 IN varchar2 default CHR(0),
argument55 IN varchar2 default CHR(0),
argument56 IN varchar2 default CHR(0),
argument57 IN varchar2 default CHR(0),
argument58 IN varchar2 default CHR(0),
argument59 IN varchar2 default CHR(0),
argument60 IN varchar2 default CHR(0),
argument61 IN varchar2 default CHR(0),
argument62 IN varchar2 default CHR(0),
argument63 IN varchar2 default CHR(0),
argument64 IN varchar2 default CHR(0),
argument65 IN varchar2 default CHR(0),
argument66 IN varchar2 default CHR(0),
argument67 IN varchar2 default CHR(0),
argument68 IN varchar2 default CHR(0),
argument69 IN varchar2 default CHR(0),
argument70 IN varchar2 default CHR(0),
argument71 IN varchar2 default CHR(0),
argument72 IN varchar2 default CHR(0),
argument73 IN varchar2 default CHR(0),
argument74 IN varchar2 default CHR(0),
argument75 IN varchar2 default CHR(0),
argument76 IN varchar2 default CHR(0),
argument77 IN varchar2 default CHR(0),
argument78 IN varchar2 default CHR(0),
argument79 IN varchar2 default CHR(0),
argument80 IN varchar2 default CHR(0),
argument81 IN varchar2 default CHR(0),
argument82 IN varchar2 default CHR(0),
argument83 IN varchar2 default CHR(0),
argument84 IN varchar2 default CHR(0),
argument85 IN varchar2 default CHR(0),
argument86 IN varchar2 default CHR(0),
argument87 IN varchar2 default CHR(0),
argument88 IN varchar2 default CHR(0),
argument89 IN varchar2 default CHR(0),
argument90 IN varchar2 default CHR(0),
argument91 IN varchar2 default CHR(0),
argument92 IN varchar2 default CHR(0),
argument93 IN varchar2 default CHR(0),
argument94 IN varchar2 default CHR(0),
argument95 IN varchar2 default CHR(0),
argument96 IN varchar2 default CHR(0),
argument97 IN varchar2 default CHR(0),
argument98 IN varchar2 default CHR(0),
argument99 IN varchar2 default CHR(0),
argument100 IN varchar2 default CHR(0))
return number is

reqid number;

begin
-- If not in database trigger mode:
-- Rollback to start_of_submission if submit() function fails to
-- submit the request.
if (not (P_DB_TRIGGER_MODE)) then
savepoint start_of_submission;
end if;

reqid := submit (
application, program, description, start_time, sub_request,
Argument1, Argument2, Argument3, Argument4, Argument5,
Argument6, Argument7, Argument8, Argument9, Argument10,
Argument11, Argument12, Argument13, Argument14, Argument15,
Argument16, Argument17, Argument18, Argument19, Argument20,
Argument21, Argument22, Argument23, Argument24, Argument25,
Argument26, Argument27, Argument28, Argument29, Argument30,
Argument31, Argument32, Argument33, Argument34, Argument35,
Argument36, Argument37, Argument38, Argument39, Argument40,
Argument41, Argument42, Argument43, Argument44, Argument45,
Argument46, Argument47, Argument48, Argument49, Argument50,
Argument51, Argument52, Argument53, Argument54, Argument55,
Argument56, Argument57, Argument58, Argument59, Argument60,
Argument61, Argument62, Argument63, Argument64, Argument65,
Argument66, Argument67, Argument68, Argument69, Argument70,
Argument71, Argument72, Argument73, Argument74, Argument75,
Argument76, Argument77, Argument78, Argument79, Argument80,
Argument81, Argument82, Argument83, Argument84, Argument85,
Argument86, Argument87, Argument88, Argument89, Argument90,
Argument91, Argument92, Argument93, Argument94, Argument95,
Argument96, Argument97, Argument98, Argument99, Argument100);

if (not (P_DB_TRIGGER_MODE)) then
if (reqid = 0) then
rollback to start_of_submission;
end if;
end if;

init_pvt_vars;

return (reqid);

end submit_request;
-------------------------------------------------------------------------------------------------

PROCEDURE cmg_wait_req_prc(p_req_id IN VARCHAR2) AS

CURSOR get_status IS
SELECT status_code
FROM fnd_concurrent_requests
WHERE request_id = p_req_id;

v_call_status BOOLEAN;
v_comp_status BOOLEAN;
v_phase VARCHAR2(100);
v_status VARCHAR2(100);
v_dev_phase VARCHAR2(100);
v_dev_status VARCHAR2(100);
v_message VARCHAR2(100);
v_get_status VARCHAR2(100);

BEGIN
v_call_status := Fnd_Concurrent.WAIT_FOR_REQUEST(
p_req_id,
5,
0,
v_phase,
v_status,
v_dev_phase,
v_dev_status,
v_message);
COMMIT;
--
OPEN get_status;
FETCH get_status INTO v_get_status;
CLOSE get_status;
--
IF v_get_status ='E'THEN
v_comp_status := Fnd_Concurrent.SET_COMPLETION_STATUS('WARNING','');
END IF;
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20004,'ERROR WHILE EXECUTING - '||SQLERRM);
END cmg_wait_req_prc;

Out Bound Interface(1)

CREATE OR REPLACE PACKAGE BODY APPS.cmg_reserve_ens_report_pck
AS
PROCEDURE MAIN_PRC (P_ERRBUF OUT VARCHAR2,
P_RETCODE OUT VARCHAR2)
IS

CURSOR FEED_CUR IS
SELECT DECODE(r.profitcenter,1000,'CMG', 3000,'GEN') PROFITCENTER,
V.VENDOR_NAME,
R.CURRENCY_CODE,
T.TITLE CMG_TITLE,
t.bipad CMG_MAG_NO,
i.issue_code CMG_ISSUE_cODE,
r.COVER_PRICE CMG_cOVER_PRICE,
R.DRAW CMG_DRAW,
TRUNC(NVL(R.EST_SALE_PERC,25),2) CMG_ENS,
-- (1-R.RETURNS/R.DRAW)*100 ACT_ENS,
TRUNC(R.DRAW * (1- NVL(R.EST_SALE_PERC,25)/100)) EST_RETURNS_BASED_ON_ENS,
R.DRAW-( ( R.DRAW *((1-R.RETURNS/R.DRAW)*100)/100)) EST_RET_BASED_ON_SALES_PERC,
( R.DRAW *((1-R.RETURNS/R.DRAW)*100)/100) ACTUAL_SALES,
R.RETURNS ACTUAL_RETURN,
R.DEALER_RATE CMG_DEALER_RATE,
R.PUB_RESERVE CMG_PUB_RESERVE,
R.WH_RESERVE CMG_WHOLESALER_RESERVE,
i.On_sale_date On_sale_date-- Added by Siddharth
,R.EST_SALE_PERC EST_SALE_PERC-- Added by Siddharth
,TRUNC((SYSDATE - i.ON_SALE_DATE)/7) OFF_SALE_WEEKS-- Added by Siddharth
FROM cmg_ppa_brokerage_Reserve_tbl@prodapps.world R,
cmg_ppa_title_dim_tbl@prodapps.world t,
cmg_ppa_issue_dim_tbl@prodapps.world i,
PO.PO_VENDORS@prodapps.world V,
cmg_ppa_acctg_pd_dim_tbl@prodapps.world a
WHERE r.accounting_month = a.accounting_month--'201602'
AND a.data_type='Brokerage Reserve'
AND a.data_loaded='N'
AND a.period_closed='N'
AND t.title_dim_id = i.title_dim_id
AND r.title_dim_id = t.title_dim_id
AND R.VENDOR_ID = V.VENDOR_ID
AND r.issue_dim_id = i.issue_Dim_id
ORDER BY r.profitcenter;

FEED_REC FEED_CUR%ROWTYPE;

V_FILEHANDLE UTL_FILE.FILE_TYPE;
V_OUTPATH VARCHAR2 (100);
V_OUTFILE UTL_FILE.FILE_TYPE;
V_LOGFILE VARCHAR2 (20);
V_REQ_ID NUMBER DEFAULT (FND_GLOBAL.CONC_REQUEST_ID);
V_USER_ID NUMBER DEFAULT (FND_GLOBAL.USER_ID);
V_SUBJECT VARCHAR2 (100)
:= 'ENS Excel Report-->' || TO_CHAR (SYSDATE, 'YYMMDDHHMISS');
V_USERNAME VARCHAR2 (20);
V_EMAIL VARCHAR2 (30);
V_OUTFILENAME VARCHAR2 (100);
V_HEADER VARCHAR2 (2500);
V_LINE VARCHAR2 (2500);
V_DIR VARCHAR2(10) DEFAULT('time');

BEGIN
SELECT USER_NAME, EMAIL_ADDRESS
INTO V_USERNAME, V_EMAIL
FROM FND_USER
WHERE USER_ID = V_USER_ID;

CMG_UTILITY_PCK.WRITE_OUTPUT (
'+--------------------------------------' || '------------+');
CMG_UTILITY_PCK.WRITE_OUTPUT ('HDG APPLICATIONS');
CMG_UTILITY_PCK.WRITE_OUTPUT ('COPYRIGHT (C) GENERA SOLUTIONS');
CMG_UTILITY_PCK.WRITE_OUTPUT (
'+-------------------------------------' || '--------------+');
CMG_UTILITY_PCK.WRITE_OUTPUT (
'CURRENT SYSTEM TIME IS : '
|| TO_CHAR (SYSDATE, 'DD-MON-YYYY HH24:MM:SS'));
CMG_UTILITY_PCK.WRITE_OUTPUT (' ');
CMG_UTILITY_PCK.WRITE_OUTPUT (
'+-------------------------------------' || '--------------+');

FND_PROFILE.GET ('CMG_OUTGOING_PATH', V_OUTPATH);

V_OUTFILENAME :='ENS_EXCEL_FEED' || '.csv';
V_OUTPATH := V_OUTPATH||'/'||V_DIR;

V_OUTFILE := UTL_FILE.FOPEN (V_OUTPATH, V_OUTFILENAME, 'w');

V_HEADER :=
'PROFITCENTER'||','||
'VENDOR_NAME' ||','||
'CURRENCY_CODE' ||','||
'CMG_TITLE' ||','||
'CMG_MAG_NO' ||','||
'CMG_ISSUE_CODE' ||','||
'CMG_COVER_PRICE' ||','||
'CMG_DRAW' ||','||
'CMG_ENS' ||','||
'EST_RETURNS_BASED_ON_ENS' ||','||
'EST_RET_BASED_ON_SALES_PERC' ||','||
'ACTUAL_SALES' ||','||
'ACTUAL_RETURN'||','||
'CMG_DEALER_RATE' ||','||
'CMG_PUB_RESERVE' ||','||
'CMG_WHOLESALER_RESERVE'||','||
'ON_SALE_DATE'||','|| -- Added by Siddharth
'EST_SALE_PERC'||','||--||','|| -- Addded by Siddharth
'OFF_SALE_WEEKS';-- Added by Siddharth

UTL_FILE.PUT_LINE (V_OUTFILE, V_HEADER);
FOR FEED_REC IN FEED_CUR LOOP

V_LINE :=
FEED_REC.PROFITCENTER||','||
-- FEED_REC.VENDOR_NAME ||','||
REPLACE(FEED_REC.VENDOR_NAME,',',' ')||','||
FEED_REC.CURRENCY_CODE ||','||
REPLACE(FEED_REC.CMG_TITLE,',',' ') ||','||
FEED_REC.CMG_MAG_NO ||','||
FEED_REC.CMG_ISSUE_CODE ||','||
FEED_REC.CMG_COVER_PRICE ||','||
FEED_REC.CMG_DRAW ||','||
FEED_REC.CMG_ENS ||','||
FEED_REC.EST_RETURNS_BASED_ON_ENS ||','||
FEED_REC.EST_RET_BASED_ON_SALES_PERC ||','||
FEED_REC.ACTUAL_SALES ||','||
FEED_REC.ACTUAL_RETURN ||','||
FEED_REC.CMG_DEALER_RATE ||','||
FEED_REC.CMG_PUB_RESERVE ||','||
FEED_REC.CMG_WHOLESALER_RESERVE||','||
FEED_REC.ON_SALE_DATE||','||-- Added by Siddharth
FEED_REC.EST_SALE_PERC||','||-- Added by Siddharth
FEED_REC.OFF_SALE_WEEKS;--Added by Siddharth;

UTL_FILE.PUT_LINE (V_OUTFILE, V_LINE);

END LOOP;

UTL_FILE.FCLOSE (V_OUTFILE);

IF V_EMAIL IS NULL
THEN
CMG_UTILITY_PCK.WRITE_OUTPUT (
'EMAIL ADDRESS IS NOT SETUP FOR THIS USER. '
|| 'PLEASE CONTACT THE SETUP ADMINISTRATOR');
ELSE
CMG_UTILITY_PCK.WRITE_OUTPUT ('FILE CREATED SUCCESSFULLY');
-- EMAIL THE DETAIL FILE TO EMAIL ADDRESS FOR THIS USER.
CMG_ABC_SUBMIT_PCK.MAIN (V_REQ_ID,
V_OUTPATH || '/' || V_OUTFILENAME,
V_OUTFILENAME,
V_SUBJECT,
V_EMAIL);

CMG_UTILITY_PCK.WRITE_OUTPUT ('FILE EMAILED SUCCESSFULLY');
END IF;

EXCEPTION

WHEN OTHERS
THEN
P_RETCODE := SQLCODE;
P_ERRBUF :=
SUBSTR ('cmg_reserve_ens_report_pck: Err Others = > ' || SQLERRM,
1,
150);
CMG_UTILITY_PCK.WRITE_OUTPUT (SQLERRM);
CMG_UTILITY_PCK.WRITE_OUTPUT (' ');
CMG_UTILITY_PCK.WRITE_OUTPUT (' ');
CMG_UTILITY_PCK.WRITE_OUTPUT (' ');
CMG_UTILITY_PCK.WRITE_OUTPUT (' ');
CMG_UTILITY_PCK.WRITE_OUTPUT (' ');
CMG_UTILITY_PCK.WRITE_OUTPUT (
'+----------------------' || '-----------------------------+');
CMG_UTILITY_PCK.WRITE_OUTPUT (' ');
CMG_UTILITY_PCK.WRITE_OUTPUT (
'Concurrent Request ' || 'Completed Successfully');
CMG_UTILITY_PCK.WRITE_OUTPUT (
'Current System Time is : '
|| TO_CHAR (SYSDATE, 'DD-MON-YYYY HH24:MM:SS'));
CMG_UTILITY_PCK.WRITE_OUTPUT (' ');
CMG_UTILITY_PCK.WRITE_OUTPUT (
'+----------------------' || '-----------------------------+');
ROLLBACK;
END MAIN_PRC;
END cmg_reserve_ens_report_pck;
/

Tuesday, February 7, 2017

Package,Function,Procedure

CREATE OR REPLACE PACKAGE APPS.cmg_ppa_load_gl_sales_pck
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/
/