/*
NAME: CMG_MAG_PUB_PAY
PURPOSE: THIS PACKAGE GENERATES A CSV REPORT FOR MAGCORE PUBLISHER PAYABLES.
REVISIONS:
VER DATE AUTHOR DESCRIPTION
--------- ---------- --------------- ------------------------------------
1.0 05/31/2017 SIDDHARTH MAGCORE PUBLLISHER PAYABLES
*/
PROCEDURE CMG_MAGCORE_PUB_PAY( P_ERRBUF OUT VARCHAR2,
P_RETCODE OUT VARCHAR2);
PROCEDURE CMG_MAGG_PUB_PAY( P_ERRBUF OUT VARCHAR2,
P_RETCODE OUT VARCHAR2);
PROCEDURE CMG_CORE_PUB_PAY( P_ERRBUF OUT VARCHAR2,
P_RETCODE OUT VARCHAR2);
END CMG_MAG_PUB_PAY;
/
CREATE OR REPLACE PACKAGE BODY APPS.CMG_MAG_PUB_PAY AS
/*
NAME: CMG_MAG_PUB_PAY
PURPOSE:
REVISIONS:
VER DATE AUTHOR DESCRIPTION
--------- ---------- --------------- ------------------------------------
1.0 05/31/2017 SIDDHARTH MAGCORE PUBLISHER PAYABLES.
*/
PROCEDURE CMG_MAGG_PUB_PAY( P_ERRBUF OUT VARCHAR2,
P_RETCODE OUT VARCHAR2) AS
--************ HEADER ONLY QUERY ********************
CURSOR ISS_CUR IS
SELECT
CMGPPA.CMG_PPA_CONTRACT_TBL.CTR_ID CTR_ID,
RTRIM(TO_CHAR(CMGPPA.CMG_PPA_CONTRACT_TBL.CTR_START_DATE,'DD-MON-YY') || ' thru ' || TO_CHAR(CMGPPA.CMG_PPA_CONTRACT_TBL.CTR_END_DATE,'DD-MON-YY')) START_TO_END,
CMGPPA.CMG_PPA_CONTRACT_HEADER_TBL.PUBLISHER_NAME PUBLISHER_NAME,
CMGPPA.CMG_PPA_CONTRACT_TBL.CTR_AGREEMENT_DATE CTR_AGREEMENT_DATE,
CMGPPA.CMG_PPA_CONTRACT_TBL.CTR_START_DATE CTR_START_DATE,
CMGPPA.CMG_PPA_CONTRACT_TBL.CTR_END_DATE CTR_END_DATE,
CMGPPA.CMG_PPA_CONTRACT_TBL.TERMINATION_STATUS TERMINATION_STATUS,
REPLACE(REPLACE(CMGPPA.CMG_PPA_CONTRACT_TBL.TERMINATION_DETAILS,CHR(13),''),CHR(10),' ') TERMINATION_DETAILS,
CMGPPA.CMG_PPA_CONTRACT_TBL.DIST_TERRITORY DIST_TERRITORY,
CMGPPA.CMG_PPA_CONTRACT_TBL.SPL_BROKERAGE_FLAG SPL_BROKERAGE_FLAG
FROM
CMGPPA.CMG_PPA_CONTRACT_TBL,
CMGPPA.CMG_PPA_CONTRACT_HEADER_TBL
WHERE
( CMGPPA.CMG_PPA_CONTRACT_HEADER_TBL.HEADER_CTR_ID=CMGPPA.CMG_PPA_CONTRACT_TBL.HEADER_CTR_ID )
AND
(
CMGPPA.CMG_PPA_CONTRACT_TBL.CTR_END_DATE > TO_DATE('01-01-2016','MM/DD/YYYY')
AND
CMGPPA.CMG_PPA_CONTRACT_TBL.TERMINATION_STATUS NOT IN ('T','V')
);
V_REQ_ID NUMBER DEFAULT (FND_GLOBAL.CONC_REQUEST_ID);
V_USER_NAME VARCHAR2 (80);
V_USER_ID NUMBER DEFAULT (FND_GLOBAL.USER_ID);
V_OUTFILENAME VARCHAR2 (60);
V_EMAIL VARCHAR2 (40);
V_SUBJECT VARCHAR2 (100) := 'CMG_MAG_PUB_PAYABLES-2 -->'|| TO_CHAR (SYSDATE, 'YYMMDDHHMISS');
V_OUTPATH VARCHAR2 (100);
V_OUTFILE UTL_FILE.FILE_TYPE;
V_HEADER VARCHAR2(25000);
V_LINE VARCHAR2 (25000);
BEGIN
P_ERRBUF := NULL;
P_RETCODE := 0;
SELECT USER_NAME,EMAIL_ADDRESS
INTO V_USER_NAME,V_EMAIL
FROM FND_USER
WHERE USER_ID = V_USER_ID;
FND_PROFILE.GET ('CMG_OUTGOING_PATH', V_OUTPATH);
V_OUTFILENAME :='CMG_Mag_Pub_Pay'||TO_CHAR (SYSDATE, 'MMYYYY')|| '.csv';
V_OUTFILE := UTL_FILE.FOPEN (V_OUTPATH, V_OUTFILENAME, 'w',max_linesize => 32767);
V_HEADER := 'CTR_ID,START_TO_END,PUBLISHER_NAME,CTR_AGREEMENT_DATE,CTR_START_DATE,CTR_END_DATE,TERMINATION_STATUS,TERMINATION_DETAILS,DIST_TERRITORY,SPL_BROKERAGE_FLAG';
UTL_FILE.PUT_LINE (V_OUTFILE, V_HEADER);
CMG_UTILITY_PCK.WRITE_OUTPUT (
'+--------------------------------------' || '-------------+');
CMG_UTILITY_PCK.WRITE_OUTPUT ('Oracle Applications');
CMG_UTILITY_PCK.WRITE_OUTPUT ('Copyright (c) Comag Marketing Group');
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 (
'+-------------------------------------' || '--------------+');
FOR ISS_REC IN ISS_CUR LOOP
V_LINE := ISS_REC.CTR_ID
||','
||ISS_REC.START_TO_END
||','
||'"'
||ISS_REC.PUBLISHER_NAME
||'"'
||','
||ISS_REC.CTR_AGREEMENT_DATE
||','
||ISS_REC.CTR_START_DATE
||','
||ISS_REC.CTR_END_DATE
||','
||ISS_REC.TERMINATION_STATUS
||','
||'"'
||ISS_REC.TERMINATION_DETAILS
||'"'
||','
||'"'
||ISS_REC.DIST_TERRITORY
||'"'
||','
||ISS_REC.SPL_BROKERAGE_FLAG;
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;
CMG_UTILITY_PCK.WRITE_OUTPUT ('');
CMG_UTILITY_PCK.WRITE_OUTPUT ('');
CMG_UTILITY_PCK.WRITE_OUTPUT ('+-------------------------------------' || '--------------+');
EXCEPTION
WHEN UTL_FILE.INVALID_OPERATION THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'INVALID OPERATION');
WHEN UTL_FILE.INVALID_PATH THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'INVALID PATH');
WHEN UTL_FILE.INVALID_MODE THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'INVALID MODE');
WHEN UTL_FILE.INVALID_FILEHANDLE THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'INVALID FILE');
WHEN UTL_FILE.READ_ERROR THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'READ ERROR');
WHEN UTL_FILE.INTERNAL_ERROR THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'INTERNAL ERROR');
WHEN OTHERS THEN
P_RETCODE:=2;
P_ERRBUF := SQLERRM;
FND_FILE.PUT_LINE(FND_FILE.LOG,'OTHER ERROR');
END CMG_MAGG_PUB_PAY;
PROCEDURE CMG_MAGCORE_PUB_PAY ( P_ERRBUF OUT VARCHAR2,
P_RETCODE OUT VARCHAR2) AS
--******************************QUERY FOR SENDING TO KABLE FOR MAGCORE PUBLISHER PAYABLES*************************************
CURSOR ISS_CUR IS
SELECT
CMGPPA.CMG_PPA_CONTRACT_TBL.ORG_ID ORG_ID,
CMGPPA.CMG_PPA_CONTRACT_HEADER_TBL.VENDOR_SITE_ID VENDOR_SITE_ID,
TRIM(CMGPPA.CMG_PPA_CONTRACT_HEADER_TBL.PUBLISHER_NAME) PUBLISHER_NAME,
CMGPPA.CMG_PPA_CONTRACT_HEADER_TBL.HEADER_CTR_ID HEADER_CTR_ID,
CMGPPA.CMG_PPA_CONTRACT_TBL.CTR_ID CTR_ID,
CMGPPA.CMG_PPA_SUBCONTRACT_TBL.SUBCTR_ID SUBCTR_ID,
CMGPPA.CMG_PPA_CONTRACT_TBL.CTR_START_DATE CTR_START_DATE,
CMGPPA.CMG_PPA_CONTRACT_TBL.CTR_END_DATE CTR_END_DATE,
CMGPPA.CMG_PPA_CONTRACT_TBL.CTR_AGREEMENT_DATE CTR_AGREEMENT_DATE,
CMGPPA.CMG_PPA_CONTRACT_TBL.TERMINATION_STATUS TERMINATION_STATUS,
CMGPPA.CMG_PPA_TITLE_SUBCTR_TBL.START_DATE START_DATE,
REPLACE(REPLACE(CMGPPA.CMG_PPA_CONTRACT_TBL.RENEWAL_INFORMATION,CHR(13),''),CHR(10),' ') RENEWAL_INFORMATION,
CMGPPA.CMG_PPA_SUBCONTRACT_TBL.BROK_BASED_ON BROK_BASED_ON,
CMGPPA.CMG_PPA_SUBCONTRACT_TBL.BROK_BY_SET_FEE BROK_BY_SET_FEE,
CMGPPA.CMG_PPA_SUBCONTRACT_TBL.STD_BROK_PERCENT STD_BROK_PERCENT,
CMGPPA.CMG_PPA_SUBCONTRACT_TBL.SET_FEE_DESCRIPTION SET_FEE_DESCRIPTION,
CMGPPA.CMG_PPA_BROK_DATE_ISS_TBL.BROK_START_DATE BROK_START_DATE,
CMGPPA.CMG_PPA_BROK_DATE_ISS_TBL.BROK_END_DATE BROK_END_DATE,
CMGPPA.CMG_PPA_BROK_DATE_ISS_TBL.BROK_PERCENT BROK_PERCENT,
CMGPPA.CMG_PPA_SUBCONTRACT_TBL.DISCOUNT_PERCENT DISCOUNT_PERCENT,
CMGPPA.CMG_PPA_SUBCONTRACT_TBL.STD_COMM_TYPE STD_COMM_TYPE,
CMGPPA.CMG_PPA_EXCEPTIONS_TBL.WHOLESALER_NAME WHOLESALER_NAME,
CMGPPA.CMG_PPA_EXCEPTIONS_TBL.EXCP_BROK_PERCENT EXCP_BROK_PERCENT,
CMGPPA.CMG_PPA_EXCEPTIONS_TBL.EXCP_BASED_ON EXCP_BASED_ON,
CMGPPA.CMG_PPA_EXCEPTIONS_TBL.EXCP_DISCOUNT_PERCENT EXCP_DISCOUNT_PERCENT
FROM
CMGPPA.CMG_PPA_CONTRACT_TBL,
CMGPPA.CMG_PPA_CONTRACT_HEADER_TBL,
CMGPPA.CMG_PPA_SUBCONTRACT_TBL,
CMGPPA.CMG_PPA_TITLE_SUBCTR_TBL,
CMGPPA.CMG_PPA_BROK_DATE_ISS_TBL,
CMGPPA.CMG_PPA_EXCEPTIONS_TBL
WHERE
( CMGPPA.CMG_PPA_CONTRACT_HEADER_TBL.HEADER_CTR_ID=CMGPPA.CMG_PPA_CONTRACT_TBL.HEADER_CTR_ID )
AND ( CMGPPA.CMG_PPA_CONTRACT_TBL.CTR_ID=CMGPPA.CMG_PPA_SUBCONTRACT_TBL.CTR_ID )
AND ( CMGPPA.CMG_PPA_SUBCONTRACT_TBL.SUBCTR_ID=CMGPPA.CMG_PPA_EXCEPTIONS_TBL.SUBCTR_ID(+) )
AND ( CMGPPA.CMG_PPA_SUBCONTRACT_TBL.SUBCTR_ID=CMGPPA.CMG_PPA_BROK_DATE_ISS_TBL.SUBCTR_ID(+) )
AND ( CMGPPA.CMG_PPA_SUBCONTRACT_TBL.SUBCTR_ID=CMGPPA.CMG_PPA_TITLE_SUBCTR_TBL.SUBCTR_ID )
AND
(
CMGPPA.CMG_PPA_CONTRACT_TBL.CTR_END_DATE >= TO_DATE('01-01-2016','MM/DD/YYYY')
AND
CMGPPA.CMG_PPA_CONTRACT_TBL.TERMINATION_STATUS IN ( 'A','P' )
);
V_REQ_ID NUMBER DEFAULT (FND_GLOBAL.CONC_REQUEST_ID);
V_USER_NAME VARCHAR2 (80);
V_USER_ID NUMBER DEFAULT (FND_GLOBAL.USER_ID);
V_OUTFILENAME VARCHAR2 (60);
V_EMAIL VARCHAR2 (40);
V_SUBJECT VARCHAR2 (100) := 'CMG_MAG_PUB_PAYABLES-1 -->'|| TO_CHAR (SYSDATE, 'YYMMDDHHMISS');
V_OUTPATH VARCHAR2 (100);
V_OUTFILE UTL_FILE.FILE_TYPE;
V_HEADER VARCHAR2(25000);
V_LINE VARCHAR2 (25000);
BEGIN
P_ERRBUF := NULL;
P_RETCODE := 0;
SELECT USER_NAME,EMAIL_ADDRESS
INTO V_USER_NAME,V_EMAIL
FROM FND_USER
WHERE USER_ID = V_USER_ID;
FND_PROFILE.GET ('CMG_OUTGOING_PATH', V_OUTPATH);
V_OUTFILENAME :='CMG_Mag_Pub_Pay'||TO_CHAR (SYSDATE, 'MMYYYY')|| '.csv';
V_OUTFILE := UTL_FILE.FOPEN (V_OUTPATH, V_OUTFILENAME, 'w');
V_HEADER := 'ORG_ID,VENDOR_SITE_ID,HEADER_CTR_ID,CTR_ID,SUBCTR_ID,CTR_START_DATE,CTR_END_DATE,CTR_AGREEMENT_DATE,TERMINATION_STATUS,START_DATE,RENEWAL_INFORMATION,BROK_BASED_ON,BROK_BY_SET_FEE,STD_BROK_PERCENT,SET_FEE_DESCRIPTION,BROK_START_DATE,BROK_END_DATE,BROK_PERCENT,DISCOUNT_PERCENT,STD_COMM_TYPE,WHOLESALER_NAME,EXCP_BROK_PERCENT,EXCP_BASED_ON,EXCP_DISCOUNT_PERCENT';
UTL_FILE.PUT_LINE (V_OUTFILE, V_HEADER);
CMG_UTILITY_PCK.WRITE_OUTPUT (
'+--------------------------------------' || '-------------+');
CMG_UTILITY_PCK.WRITE_OUTPUT ('Oracle Applications');
CMG_UTILITY_PCK.WRITE_OUTPUT ('Copyright (c) Comag Marketing Group');
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 (
'+-------------------------------------' || '--------------+');
FOR ISS_REC IN ISS_CUR LOOP
V_LINE := ISS_REC.ORG_ID
||','
||ISS_REC.VENDOR_SITE_ID
||','
||'"'
||ISS_REC.PUBLISHER_NAME
||'"'
||','
||ISS_REC.HEADER_CTR_ID
||','
||ISS_REC.CTR_ID
||','
||ISS_REC.SUBCTR_ID
||','
||ISS_REC.CTR_START_DATE
||','
||ISS_REC.CTR_END_DATE
||','
||ISS_REC.CTR_AGREEMENT_DATE
||','
||'"'
||ISS_REC.TERMINATION_STATUS
||'"'
||','
||ISS_REC.START_DATE
||','
||'"'
||ISS_REC.RENEWAL_INFORMATION
||'"'
||','
||'"'
||ISS_REC.BROK_BASED_ON
||'"'
||','
||'"'
||ISS_REC.BROK_BY_SET_FEE
||'"'
||','
||'"'
||ISS_REC.STD_BROK_PERCENT
||'"'
||','
||'"'
||ISS_REC.SET_FEE_DESCRIPTION
||'"'
||','
||ISS_REC.BROK_START_DATE
||','
||ISS_REC.BROK_END_DATE
||','
||ISS_REC.BROK_PERCENT
||','
||'"'
||ISS_REC.DISCOUNT_PERCENT
||'"'
||','
||'"'
||ISS_REC.STD_COMM_TYPE
||'"'
||','
||'"'
||ISS_REC.WHOLESALER_NAME
||'"'
||','
||'"'
||ISS_REC.EXCP_BROK_PERCENT
||'"'
||','
||'"'
||ISS_REC.EXCP_BASED_ON
||'"'
||','
||ISS_REC.EXCP_DISCOUNT_PERCENT;
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;
CMG_UTILITY_PCK.WRITE_OUTPUT ('');
CMG_UTILITY_PCK.WRITE_OUTPUT ('');
CMG_UTILITY_PCK.WRITE_OUTPUT ('+-------------------------------------' || '--------------+');
EXCEPTION
WHEN UTL_FILE.INVALID_OPERATION THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'INVALID OPERATION');
WHEN UTL_FILE.INVALID_PATH THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'INVALID PATH');
WHEN UTL_FILE.INVALID_MODE THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'INVALID MODE');
WHEN UTL_FILE.INVALID_FILEHANDLE THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'INVALID FILE');
WHEN UTL_FILE.READ_ERROR THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'READ ERROR');
WHEN UTL_FILE.INTERNAL_ERROR THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'INTERNAL ERROR');
WHEN OTHERS THEN
P_RETCODE:=2;
P_ERRBUF := SQLERRM;
FND_FILE.PUT_LINE(FND_FILE.LOG,'OTHER ERROR');
END CMG_MAGCORE_PUB_PAY;
PROCEDURE CMG_CORE_PUB_PAY( P_ERRBUF OUT VARCHAR2,
P_RETCODE OUT VARCHAR2) AS
--*****************TITLES ATTACHED TO CONTRACT QUERY*******************************
CURSOR ISS_CUR IS
SELECT
CMGPPA.CMG_PPA_SUBCONTRACT_TBL.SUBCTR_ID SUBCTR_ID,
CMGPPA.CMG_PPA_CONTRACT_HEADER_TBL.PUBLISHER_NAME PUBLISHER_NAME,
CMGPPA.CMG_PPA_CONTRACT_HEADER_TBL.VENDOR_SITE_ID VENDOR_SITE_ID,
CMGPPA.CMG_PPA_TITLE_DIM_TBL.TITLE TITLE,
CMGPPA.CMG_PPA_TITLE_DIM_TBL.BIPAD BIPAD,
CMGPPA.CMG_PPA_TITLE_SUBCTR_TBL.ASSIGNED ASSIGNED,
CMGPPA.CMG_PPA_TITLE_SUBCTR_TBL.START_DATE START_DATE,
CMGPPA.CMG_PPA_TITLE_SUBCTR_TBL.END_DATE END_DATE,
CMGPPA.CMG_PPA_TITLE_SUBCTR_TBL.LAST_ISSUE LAST_ISSUE,
CMGPPA.CMG_PPA_SUBCONTRACT_TBL.SUBCTR_STATUS SUBCTR_STATUS,
CMGPPA.CMG_PPA_SUBCONTRACT_TBL.VALID_FLAG VALID_FLAG
FROM
CMGPPA.CMG_PPA_SUBCONTRACT_TBL,
CMGPPA.CMG_PPA_CONTRACT_HEADER_TBL,
CMGPPA.CMG_PPA_TITLE_DIM_TBL,
CMGPPA.CMG_PPA_TITLE_SUBCTR_TBL,
CMGPPA.CMG_PPA_CONTRACT_TBL
WHERE
( CMGPPA.CMG_PPA_CONTRACT_HEADER_TBL.HEADER_CTR_ID=CMGPPA.CMG_PPA_CONTRACT_TBL.HEADER_CTR_ID )
AND ( CMGPPA.CMG_PPA_CONTRACT_TBL.CTR_ID=CMGPPA.CMG_PPA_SUBCONTRACT_TBL.CTR_ID )
AND ( CMGPPA.CMG_PPA_SUBCONTRACT_TBL.SUBCTR_ID=CMGPPA.CMG_PPA_TITLE_SUBCTR_TBL.SUBCTR_ID )
AND ( CMGPPA.CMG_PPA_TITLE_DIM_TBL.TITLE_DIM_ID=CMGPPA.CMG_PPA_TITLE_SUBCTR_TBL.TITLE_DIM_ID )
AND
(
CMGPPA.CMG_PPA_CONTRACT_TBL.CTR_END_DATE >= TO_DATE('01-01-2016','MM/DD/YYYY')
AND
CMGPPA.CMG_PPA_CONTRACT_TBL.TERMINATION_STATUS IN ( 'A','P' )
AND
CMGPPA.CMG_PPA_TITLE_SUBCTR_TBL.ASSIGNED IN ( 'Y' )
);
V_REQ_ID NUMBER DEFAULT (FND_GLOBAL.CONC_REQUEST_ID);
V_USER_NAME VARCHAR2 (80);
V_USER_ID NUMBER DEFAULT (FND_GLOBAL.USER_ID);
V_OUTFILENAME VARCHAR2 (60);
V_EMAIL VARCHAR2 (40);
V_SUBJECT VARCHAR2 (100) := 'CMG_MAG_PUB_PAYABLES-3'|| TO_CHAR (SYSDATE, 'YYMMDDHHMISS');
V_OUTPATH VARCHAR2 (100);
V_OUTFILE UTL_FILE.FILE_TYPE;
V_HEADER VARCHAR2(25000);
V_LINE VARCHAR2 (25000);
BEGIN
P_ERRBUF := NULL;
P_RETCODE := 0;
SELECT USER_NAME,EMAIL_ADDRESS
INTO V_USER_NAME,V_EMAIL
FROM FND_USER
WHERE USER_ID = V_USER_ID;
FND_PROFILE.GET ('CMG_OUTGOING_PATH', V_OUTPATH);
V_OUTFILENAME :='CMG_Core_Pub_Pay'||TO_CHAR (SYSDATE, 'MMYYYY')|| '.csv';
V_OUTFILE := UTL_FILE.FOPEN (V_OUTPATH, V_OUTFILENAME, 'w');
V_HEADER := 'SUBCTR_ID,PUBLISHER_NAME,VENDOR_SITE_ID,TITLE,BIPAD,ASSIGNED,START_DATE,END_DATE,LAST_ISSUE,SUBCTR_STATUS,VALID_FLAG';
UTL_FILE.PUT_LINE (V_OUTFILE, V_HEADER);
CMG_UTILITY_PCK.WRITE_OUTPUT (
'+--------------------------------------' || '-------------+');
CMG_UTILITY_PCK.WRITE_OUTPUT ('Oracle Applications');
CMG_UTILITY_PCK.WRITE_OUTPUT ('Copyright (c) Comag Marketing Group');
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 (
'+-------------------------------------' || '--------------+');
FOR ISS_REC IN ISS_CUR LOOP
V_LINE := ISS_REC.SUBCTR_ID
||','
||'"'
||ISS_REC.PUBLISHER_NAME
||'"'
||','
||ISS_REC.VENDOR_SITE_ID
||','
||'"'
||ISS_REC.TITLE
||'"'
||','
||ISS_REC.BIPAD
||','
||ISS_REC.ASSIGNED
||','
||ISS_REC.START_DATE
||','
||ISS_REC.END_DATE
||','
||ISS_REC.LAST_ISSUE
||','
||ISS_REC.SUBCTR_STATUS
||','
||ISS_REC.VALID_FLAG;
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;
CMG_UTILITY_PCK.WRITE_OUTPUT ('');
CMG_UTILITY_PCK.WRITE_OUTPUT ('');
CMG_UTILITY_PCK.WRITE_OUTPUT ('+-------------------------------------' || '--------------+');
EXCEPTION
WHEN UTL_FILE.INVALID_OPERATION THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'INVALID OPERATION');
WHEN UTL_FILE.INVALID_PATH THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'INVALID PATH');
WHEN UTL_FILE.INVALID_MODE THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'INVALID MODE');
WHEN UTL_FILE.INVALID_FILEHANDLE THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'INVALID FILE');
WHEN UTL_FILE.READ_ERROR THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'READ ERROR');
WHEN UTL_FILE.INTERNAL_ERROR THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'INTERNAL ERROR');
WHEN OTHERS THEN
P_RETCODE:=2;
P_ERRBUF := SQLERRM;
FND_FILE.PUT_LINE(FND_FILE.LOG,'OTHER ERROR');
END CMG_CORE_PUB_PAY;
END CMG_MAG_PUB_PAY;
/
No comments:
Post a Comment