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;
/
No comments:
Post a Comment