CREATE OR REPLACE PROCEDURE APPS.XXX_CERTI_ANALYSIS_P (
retcode IN OUT VARCHAR2,
errbuff IN OUT VARCHAR2,
p_date_from VARCHAR2,
p_date_to VARCHAR2,
p_branch VARCHAR2
)
AS
CURSOR c1 (l_date_from DATE, l_date_to DATE, l_branch VARCHAR2)
IS
SELECT ppei.pei_information1 certi_type, COUNT ( * ) no_of_times
FROM per_people_extra_info ppei, per_assignments_x paaf
WHERE paaf.person_id = ppei.person_id
AND paaf.assignment_type = 'E'
AND paaf.assignment_status_type_id IN (1, 2)
AND paaf.primary_flag = 'Y'
AND ppei.information_type = 'Request_for_Letter_Certificate'
AND ppei.last_update_date BETWEEN l_date_from AND l_date_to
AND ppei.pei_information1 IS NOT NULL
AND paaf.payroll_id = DECODE (l_branch,
'INDIA',
62,
'UK',
82)
GROUP BY ppei.pei_information1;
BEGIN
fnd_file.put_line (fnd_file.output, '<html>');
fnd_file.put_line (fnd_file.output, '<body>');
-- Report Heading
fnd_file.put_line (fnd_file.output, '<h1>KFSH Certificate Analysis</h1>');
-- Display Parameters
fnd_file.put_line ( fnd_file.output,'<b><br>' || 'Start Date: </b>' || TO_CHAR(TO_DATE (p_date_from, 'YYYY/MM/DD HH24:MI:SS'),'DD-MON-YYYY') || '</br>');
fnd_file.put_line ( fnd_file.output,'<b><br>' || 'End Date : </b>' || TO_CHAR(TO_DATE (p_date_to, 'YYYY/MM/DD HH24:MI:SS'),'DD-MON-YYYY') || '</br>' );
fnd_file.put_line ( fnd_file.output,'<b><br>' || 'Branch : </b>' || p_branch || '</br>' );
-- Creating HTML Table
fnd_file.put_line (
fnd_file.output,
'<BR><TABLE width="60%" class="x1h" cellpadding="1" cellspacing="0" border="5">'
);
-- Table Heading
fnd_file.put_line (fnd_file.output, '<TR>');
fnd_file.put_line (fnd_file.output, '<b><td class="x3w" bgcolor="#808080" width="50%"> CERTIFICATE TYPE </TD>');
fnd_file.put_line (fnd_file.output,
'<b><td class="x3w" bgcolor="#808080" width="10%"> TIMES </TD></TR>');
FOR get_details
IN c1 (TO_DATE (p_date_from, 'YYYY/MM/DD HH24:MI:SS'),
TO_DATE (p_date_to, 'YYYY/MM/DD HH24:MI:SS'),
p_branch)
LOOP
-- Loop for creaing new Row
fnd_file.put_line (fnd_file.output, '<tr>');
fnd_file.put_line (
fnd_file.output,
'<b><td class="x3w" bgcolor="#E3E4FA">' || upper(get_details.certi_type) || '</TD>'
);
fnd_file.put_line (
fnd_file.output,
'<b><td class="x3w" bgcolor="#E3E4FA">' || get_details.no_of_times || ' </TD></TR>'
);
END LOOP;
-- End HTML Table
fnd_file.put_line (fnd_file.output, '</TABLE>');
-- END BODY and HTML
fnd_file.put_line (fnd_file.output, '</BODY></HTML>');
EXCEPTION
WHEN OTHERS
THEN
retcode := 2;
errbuff := 'Main Exception !!!';
fnd_file.put_line (fnd_file.output, '</BODY></HTML>');
END kfsh_certi_analysis_p;
No comments:
Post a Comment