CREATE OR REPLACE PACKAGE APPS.xxtt_pmp_extract_pkg
AS
PROCEDURE xxtt_pmp_extract_prc (
errbuf OUT VARCHAR2,
retcode OUT VARCHAR2,
p_pa_period_name IN VARCHAR2,
p_operating_unit IN VARCHAR2,
p_month_end IN VARCHAR2,
p_rpt_type IN VARCHAR2
);
END xxtt_pmp_extract_pkg;
/
==================================
CREATE OR REPLACE PACKAGE BODY APPS.xxtt_pmp_extract_pkg
AS
PROCEDURE xxtt_pmp_extract_prc (
errbuf OUT VARCHAR2,
retcode OUT VARCHAR2,
p_pa_period_name IN VARCHAR2,
p_operating_unit IN VARCHAR2,
p_month_end IN VARCHAR2,
p_rpt_type IN VARCHAR2
)
IS
l_recipient VARCHAR2 (4000):='elangovanragavan@hotmail.com';
l_error_message VARCHAR2 (4000);
l_error_status NUMBER;
CURSOR c_property (P_PA_PERIOD_NAME in varchar2,
P_OPERATING_UNIT IN varchar2,
P_MONTH_END in varchar2,
P_RPT_TYPE in varchar2)
is
SELECT pra.package_id, pfl_pkg.portfolio_descr, pra.company,
pra.gl_period_name, pra.pa_period_name, to_char(pra.run_start,'MM/DD/YYYY HH12:MI:SS AM') run_start,
to_char(pra.run_end,'MM/DD/YYYY HH12:MI:SS AM') run_end,
COUNT (rdoc.report_doc_id) CNT
FROM pmreports.portfolio_run_audit@ttrdb pra,
pmreports.report_doc@ttrdb rdoc,
pmreports.portfolio_package@ttrdb pfl_pkg
WHERE pra.trigger_name = rdoc.trigger_name
AND pra.package_id = pfl_pkg.portfolio_id
AND pra.company = NVL (p_operating_unit, pra.company)
AND pra.pa_period_name = rdoc.pa_period_name
AND p_rpt_type = 'Summarized'
AND (pra.pa_period_name in NVL(P_PA_PERIOD_NAME,pra.pa_period_name)
OR pra.pa_period_name in
(SELECT DECODE (UPPER (P_MONTH_END),
'Y', current_pa_period,
last_closed_pa_period
)
FROM param_gl_period@ttrdb
WHERE org_id = (SELECT NVL
(TO_NUMBER
(DECODE
(SUBSTRB
(USERENV
('CLIENT_INFO'
),
1,
1
),
'', NULL,
SUBSTRB
(USERENV
('CLIENT_INFO'
),
1,
10
)
)
),
-99
)
FROM DUAL) ))
GROUP BY pra.package_id,
pfl_pkg.portfolio_descr,
pra.company,
pra.gl_period_name,
pra.pa_period_name,
pra.run_start,
pra.run_end
ORDER BY pra.company, pra.package_id;
CURSOR c_property1 (P_PA_PERIOD_NAME in varchar2,
P_OPERATING_UNIT IN varchar2,
P_MONTH_END in varchar2,
P_RPT_TYPE in varchar2)
is
SELECT package_id, company, gl_period_name, pa_period_name, run_start,
run_end, report_name, SUM (COUNT) COUNT
FROM (SELECT pra.package_id, pra.company, pra.gl_period_name,
pra.pa_period_name, to_char(pra.run_start,'MM/DD/YYYY HH:MI:SS AM') run_start,
to_char(pra.run_end,'MM/DD/YYYY HH:MI:SS AM') run_end,
rdoc.report_name, 1 COUNT
FROM pmreports.portfolio_run_audit@ttrdb pra,
pmreports.report_doc@ttrdb rdoc
WHERE pra.trigger_name = rdoc.trigger_name
AND pra.pa_period_name = rdoc.pa_period_name
AND P_RPT_TYPE = 'Detailed'
AND (pra.pa_period_name in NVL(P_PA_PERIOD_NAME,pra.pa_period_name)
OR pra.pa_period_name in
(SELECT DECODE (UPPER (P_MONTH_END),
'Y', current_pa_period,
last_closed_pa_period
)
FROM param_gl_period@ttrdb
WHERE org_id = (SELECT NVL
(TO_NUMBER
(DECODE
(SUBSTRB
(USERENV
('CLIENT_INFO'
),
1,
1
),
'', NULL,
SUBSTRB
(USERENV
('CLIENT_INFO'
),
1,
10
)
)
),
-99
)
FROM DUAL) ))
AND pra.company = nvl(P_OPERATING_UNIT,pra.company)
UNION ALL
SELECT pfl_rpts.package_id, pra.company, pra.gl_period_name,
pra.pa_period_name, to_char(pra.run_start,'MM/DD/YYYY HH:MI:SS AM') run_start,
to_char(pra.run_end,'MM/DD/YYYY HH:MI:SS AM') run_end,
rpt.report_name, 0 COUNT
FROM pmreports.report_def@ttrdb rpt,
pmreports.portfolio_reports@ttrdb pfl_rpts,
pmreports.portfolio_package@ttrdb pfl_pkg,
pmreports.portfolio_run_audit@ttrdb pra
WHERE 1 = 1
AND rpt.report_def_id = pfl_rpts.report_def_id
AND pfl_rpts.package_id = pfl_pkg.portfolio_id
AND pra.package_id = pfl_pkg.portfolio_id
AND P_RPT_TYPE = 'Detailed'
AND (pra.pa_period_name in NVL(P_PA_PERIOD_NAME,pra.pa_period_name)
OR pra.pa_period_name in
(SELECT DECODE (UPPER (P_MONTH_END),
'Y', current_pa_period,
last_closed_pa_period
)
FROM param_gl_period@ttrdb
WHERE org_id = (SELECT NVL
(TO_NUMBER
(DECODE
(SUBSTRB
(USERENV
('CLIENT_INFO'
),
1,
1
),
'', NULL,
SUBSTRB
(USERENV
('CLIENT_INFO'
),
1,
10
)
)
),
-99
)
FROM DUAL) ))
AND pra.company = nvl(P_OPERATING_UNIT,pra.company)
)
GROUP BY package_id,
company,
gl_period_name,
pa_period_name,
run_start,
run_end,
report_name
ORDER BY company, package_id, report_name;
cur1 c_property%ROWTYPE;
cur2 c_property1%ROWTYPE;
l_file_dir VARCHAR2 (512);
l_header VARCHAR2 (4000);
l_utl_file UTL_FILE.file_type;
l_file_name VARCHAR2 (100);
l_rec VARCHAR2 (4000);
l_rec_update_count NUMBER := 0;
l_ftp_file_name VARCHAR2(255);
l_ftp_req_id_val NUMBER:=0;
BEGIN
BEGIN
SELECT SUBSTR (VALUE, 1, INSTR (VALUE, ',') - 1)
INTO l_file_dir
FROM v$parameter
WHERE NAME = 'utl_file_dir' AND ROWNUM = 1;
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
l_file_name := 'TT: PMP Package Extract' || '.csv';
l_ftp_file_name := l_file_dir||'/'||l_file_name;
l_utl_file := UTL_FILE.fopen (l_file_dir, l_file_name, 'W', 2000);
l_header :=
('PACKAGE_ID,PORTFOLIO_DESCR,COMPANY,GL_PERIOD_NAME,PA_PERIOD_NAME,RUN_START,RUN_END,Total Doc Id'
);
fnd_file.put_line (fnd_file.output,
'PACKAGE_ID'
|| 'PORTFOLIO_DESCR'
|| 'COMPANY'
|| 'GL_PERIOD_NAME'
|| 'PA_PERIOD_NAME'
|| 'RUN_START'
|| 'RUN_END'
|| 'Total Doc Id'
);
UTL_FILE.put_line (l_utl_file, l_header);
IF P_RPT_TYPE = 'Summarized'
THEN
FOR cur1 IN c_property (P_PA_PERIOD_NAME,P_OPERATING_UNIT,P_MONTH_END,P_RPT_TYPE)
LOOP
BEGIN
l_rec :=
NVL (cur1.PACKAGE_ID, '')
|| ','
|| NVL (cur1.PORTFOLIO_DESCR, '')
|| ','
|| NVL (cur1.COMPANY, '')
|| ','
|| NVL (cur1.GL_PERIOD_NAME, '')
|| ','
|| NVL (cur1.PA_PERIOD_NAME, '')
|| ','
|| NVL (cur1.RUN_START, '')
|| ','
|| NVL (cur1.RUN_END, '')
|| ','
|| NVL (cur1.CNT, '');
UTL_FILE.put_line (l_utl_file, l_rec);
-- l_rec_update_count := l_rec_update_count + 1;
fnd_file.put_line (fnd_file.output,
NVL (cur1.PACKAGE_ID, '')
|| NVL (cur1.PORTFOLIO_DESCR, '')
|| NVL (cur1.COMPANY, '')
|| NVL (cur1.GL_PERIOD_NAME, '')
|| NVL (cur1.PA_PERIOD_NAME, '')
|| NVL (cur1.RUN_START, '')
|| NVL (cur1.RUN_END, '')
|| NVL (cur1.CNT, '')
);
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
END LOOP;
ELSIF P_RPT_TYPE = 'Detailed'
THEN
FOR cur2 IN c_property1 (P_PA_PERIOD_NAME,P_OPERATING_UNIT,P_MONTH_END,P_RPT_TYPE)
LOOP
BEGIN
l_rec :=
NVL (cur2.PACKAGE_ID, '')
|| ','
|| NVL (cur2.REPORT_NAME, '')
|| ','
|| NVL (cur2.COMPANY, '')
|| ','
|| NVL (cur2.GL_PERIOD_NAME, '')
|| ','
|| NVL (cur2.PA_PERIOD_NAME, '')
|| ','
|| NVL (cur2.RUN_START, '')
|| ','
|| NVL (cur2.RUN_END, '')
|| ','
|| NVL (cur2.COUNT, '');
UTL_FILE.put_line (l_utl_file, l_rec);
-- l_rec_update_count := l_rec_update_count + 1;
fnd_file.put_line (fnd_file.output,
NVL (cur2.PACKAGE_ID, '')
|| NVL (cur2.COMPANY, '')
|| NVL (cur2.GL_PERIOD_NAME, '')
|| NVL (cur2.PA_PERIOD_NAME, '')
|| NVL (cur2.RUN_START, '')
|| NVL (cur2.RUN_END, '')
|| NVL ( cur2.COUNT, '')
);
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
END LOOP;
END IF;
fnd_file.put_line (fnd_file.LOG,
'Total Record Count: ' || l_rec_update_count
);
UTL_FILE.fclose_all;
l_error_status :=
xxtt_sendmailjpkg.sendmail
(smtpservername => xx_get_current_smtp,
sender => 'test.com',
recipient => 'elangovanragavan@hotmail.com',
ccrecipient => '',
bccrecipient => '',
subject => 'PMP Package Validation',
BODY => 'PMP Package Validation List',
errormessage => l_error_message,
attachments => xxtt_sendmailjpkg.attachments_list(l_ftp_file_name)
);
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
'Other error occured in program: ' || SQLERRM
);
END;
END;
/
AS
PROCEDURE xxtt_pmp_extract_prc (
errbuf OUT VARCHAR2,
retcode OUT VARCHAR2,
p_pa_period_name IN VARCHAR2,
p_operating_unit IN VARCHAR2,
p_month_end IN VARCHAR2,
p_rpt_type IN VARCHAR2
);
END xxtt_pmp_extract_pkg;
/
==================================
CREATE OR REPLACE PACKAGE BODY APPS.xxtt_pmp_extract_pkg
AS
PROCEDURE xxtt_pmp_extract_prc (
errbuf OUT VARCHAR2,
retcode OUT VARCHAR2,
p_pa_period_name IN VARCHAR2,
p_operating_unit IN VARCHAR2,
p_month_end IN VARCHAR2,
p_rpt_type IN VARCHAR2
)
IS
l_recipient VARCHAR2 (4000):='elangovanragavan@hotmail.com';
l_error_message VARCHAR2 (4000);
l_error_status NUMBER;
CURSOR c_property (P_PA_PERIOD_NAME in varchar2,
P_OPERATING_UNIT IN varchar2,
P_MONTH_END in varchar2,
P_RPT_TYPE in varchar2)
is
SELECT pra.package_id, pfl_pkg.portfolio_descr, pra.company,
pra.gl_period_name, pra.pa_period_name, to_char(pra.run_start,'MM/DD/YYYY HH12:MI:SS AM') run_start,
to_char(pra.run_end,'MM/DD/YYYY HH12:MI:SS AM') run_end,
COUNT (rdoc.report_doc_id) CNT
FROM pmreports.portfolio_run_audit@ttrdb pra,
pmreports.report_doc@ttrdb rdoc,
pmreports.portfolio_package@ttrdb pfl_pkg
WHERE pra.trigger_name = rdoc.trigger_name
AND pra.package_id = pfl_pkg.portfolio_id
AND pra.company = NVL (p_operating_unit, pra.company)
AND pra.pa_period_name = rdoc.pa_period_name
AND p_rpt_type = 'Summarized'
AND (pra.pa_period_name in NVL(P_PA_PERIOD_NAME,pra.pa_period_name)
OR pra.pa_period_name in
(SELECT DECODE (UPPER (P_MONTH_END),
'Y', current_pa_period,
last_closed_pa_period
)
FROM param_gl_period@ttrdb
WHERE org_id = (SELECT NVL
(TO_NUMBER
(DECODE
(SUBSTRB
(USERENV
('CLIENT_INFO'
),
1,
1
),
'', NULL,
SUBSTRB
(USERENV
('CLIENT_INFO'
),
1,
10
)
)
),
-99
)
FROM DUAL) ))
GROUP BY pra.package_id,
pfl_pkg.portfolio_descr,
pra.company,
pra.gl_period_name,
pra.pa_period_name,
pra.run_start,
pra.run_end
ORDER BY pra.company, pra.package_id;
CURSOR c_property1 (P_PA_PERIOD_NAME in varchar2,
P_OPERATING_UNIT IN varchar2,
P_MONTH_END in varchar2,
P_RPT_TYPE in varchar2)
is
SELECT package_id, company, gl_period_name, pa_period_name, run_start,
run_end, report_name, SUM (COUNT) COUNT
FROM (SELECT pra.package_id, pra.company, pra.gl_period_name,
pra.pa_period_name, to_char(pra.run_start,'MM/DD/YYYY HH:MI:SS AM') run_start,
to_char(pra.run_end,'MM/DD/YYYY HH:MI:SS AM') run_end,
rdoc.report_name, 1 COUNT
FROM pmreports.portfolio_run_audit@ttrdb pra,
pmreports.report_doc@ttrdb rdoc
WHERE pra.trigger_name = rdoc.trigger_name
AND pra.pa_period_name = rdoc.pa_period_name
AND P_RPT_TYPE = 'Detailed'
AND (pra.pa_period_name in NVL(P_PA_PERIOD_NAME,pra.pa_period_name)
OR pra.pa_period_name in
(SELECT DECODE (UPPER (P_MONTH_END),
'Y', current_pa_period,
last_closed_pa_period
)
FROM param_gl_period@ttrdb
WHERE org_id = (SELECT NVL
(TO_NUMBER
(DECODE
(SUBSTRB
(USERENV
('CLIENT_INFO'
),
1,
1
),
'', NULL,
SUBSTRB
(USERENV
('CLIENT_INFO'
),
1,
10
)
)
),
-99
)
FROM DUAL) ))
AND pra.company = nvl(P_OPERATING_UNIT,pra.company)
UNION ALL
SELECT pfl_rpts.package_id, pra.company, pra.gl_period_name,
pra.pa_period_name, to_char(pra.run_start,'MM/DD/YYYY HH:MI:SS AM') run_start,
to_char(pra.run_end,'MM/DD/YYYY HH:MI:SS AM') run_end,
rpt.report_name, 0 COUNT
FROM pmreports.report_def@ttrdb rpt,
pmreports.portfolio_reports@ttrdb pfl_rpts,
pmreports.portfolio_package@ttrdb pfl_pkg,
pmreports.portfolio_run_audit@ttrdb pra
WHERE 1 = 1
AND rpt.report_def_id = pfl_rpts.report_def_id
AND pfl_rpts.package_id = pfl_pkg.portfolio_id
AND pra.package_id = pfl_pkg.portfolio_id
AND P_RPT_TYPE = 'Detailed'
AND (pra.pa_period_name in NVL(P_PA_PERIOD_NAME,pra.pa_period_name)
OR pra.pa_period_name in
(SELECT DECODE (UPPER (P_MONTH_END),
'Y', current_pa_period,
last_closed_pa_period
)
FROM param_gl_period@ttrdb
WHERE org_id = (SELECT NVL
(TO_NUMBER
(DECODE
(SUBSTRB
(USERENV
('CLIENT_INFO'
),
1,
1
),
'', NULL,
SUBSTRB
(USERENV
('CLIENT_INFO'
),
1,
10
)
)
),
-99
)
FROM DUAL) ))
AND pra.company = nvl(P_OPERATING_UNIT,pra.company)
)
GROUP BY package_id,
company,
gl_period_name,
pa_period_name,
run_start,
run_end,
report_name
ORDER BY company, package_id, report_name;
cur1 c_property%ROWTYPE;
cur2 c_property1%ROWTYPE;
l_file_dir VARCHAR2 (512);
l_header VARCHAR2 (4000);
l_utl_file UTL_FILE.file_type;
l_file_name VARCHAR2 (100);
l_rec VARCHAR2 (4000);
l_rec_update_count NUMBER := 0;
l_ftp_file_name VARCHAR2(255);
l_ftp_req_id_val NUMBER:=0;
BEGIN
BEGIN
SELECT SUBSTR (VALUE, 1, INSTR (VALUE, ',') - 1)
INTO l_file_dir
FROM v$parameter
WHERE NAME = 'utl_file_dir' AND ROWNUM = 1;
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
l_file_name := 'TT: PMP Package Extract' || '.csv';
l_ftp_file_name := l_file_dir||'/'||l_file_name;
l_utl_file := UTL_FILE.fopen (l_file_dir, l_file_name, 'W', 2000);
l_header :=
('PACKAGE_ID,PORTFOLIO_DESCR,COMPANY,GL_PERIOD_NAME,PA_PERIOD_NAME,RUN_START,RUN_END,Total Doc Id'
);
fnd_file.put_line (fnd_file.output,
'PACKAGE_ID'
|| 'PORTFOLIO_DESCR'
|| 'COMPANY'
|| 'GL_PERIOD_NAME'
|| 'PA_PERIOD_NAME'
|| 'RUN_START'
|| 'RUN_END'
|| 'Total Doc Id'
);
UTL_FILE.put_line (l_utl_file, l_header);
IF P_RPT_TYPE = 'Summarized'
THEN
FOR cur1 IN c_property (P_PA_PERIOD_NAME,P_OPERATING_UNIT,P_MONTH_END,P_RPT_TYPE)
LOOP
BEGIN
l_rec :=
NVL (cur1.PACKAGE_ID, '')
|| ','
|| NVL (cur1.PORTFOLIO_DESCR, '')
|| ','
|| NVL (cur1.COMPANY, '')
|| ','
|| NVL (cur1.GL_PERIOD_NAME, '')
|| ','
|| NVL (cur1.PA_PERIOD_NAME, '')
|| ','
|| NVL (cur1.RUN_START, '')
|| ','
|| NVL (cur1.RUN_END, '')
|| ','
|| NVL (cur1.CNT, '');
UTL_FILE.put_line (l_utl_file, l_rec);
-- l_rec_update_count := l_rec_update_count + 1;
fnd_file.put_line (fnd_file.output,
NVL (cur1.PACKAGE_ID, '')
|| NVL (cur1.PORTFOLIO_DESCR, '')
|| NVL (cur1.COMPANY, '')
|| NVL (cur1.GL_PERIOD_NAME, '')
|| NVL (cur1.PA_PERIOD_NAME, '')
|| NVL (cur1.RUN_START, '')
|| NVL (cur1.RUN_END, '')
|| NVL (cur1.CNT, '')
);
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
END LOOP;
ELSIF P_RPT_TYPE = 'Detailed'
THEN
FOR cur2 IN c_property1 (P_PA_PERIOD_NAME,P_OPERATING_UNIT,P_MONTH_END,P_RPT_TYPE)
LOOP
BEGIN
l_rec :=
NVL (cur2.PACKAGE_ID, '')
|| ','
|| NVL (cur2.REPORT_NAME, '')
|| ','
|| NVL (cur2.COMPANY, '')
|| ','
|| NVL (cur2.GL_PERIOD_NAME, '')
|| ','
|| NVL (cur2.PA_PERIOD_NAME, '')
|| ','
|| NVL (cur2.RUN_START, '')
|| ','
|| NVL (cur2.RUN_END, '')
|| ','
|| NVL (cur2.COUNT, '');
UTL_FILE.put_line (l_utl_file, l_rec);
-- l_rec_update_count := l_rec_update_count + 1;
fnd_file.put_line (fnd_file.output,
NVL (cur2.PACKAGE_ID, '')
|| NVL (cur2.COMPANY, '')
|| NVL (cur2.GL_PERIOD_NAME, '')
|| NVL (cur2.PA_PERIOD_NAME, '')
|| NVL (cur2.RUN_START, '')
|| NVL (cur2.RUN_END, '')
|| NVL ( cur2.COUNT, '')
);
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
END LOOP;
END IF;
fnd_file.put_line (fnd_file.LOG,
'Total Record Count: ' || l_rec_update_count
);
UTL_FILE.fclose_all;
l_error_status :=
xxtt_sendmailjpkg.sendmail
(smtpservername => xx_get_current_smtp,
sender => 'test.com',
recipient => 'elangovanragavan@hotmail.com',
ccrecipient => '',
bccrecipient => '',
subject => 'PMP Package Validation',
BODY => 'PMP Package Validation List',
errormessage => l_error_message,
attachments => xxtt_sendmailjpkg.attachments_list(l_ftp_file_name)
);
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
'Other error occured in program: ' || SQLERRM
);
END;
END;
/
No comments:
Post a Comment