[Home] [Help]
PACKAGE BODY: APPS.PAY_NO_SELF_CERTIFICATION_RPT
Source
1 PACKAGE BODY PAY_NO_SELF_CERTIFICATION_RPT AS
2 /* $Header: pynosfcr.pkb 120.0.12000000.1 2007/05/20 09:28:37 rlingama noship $ */
3 FUNCTION get_archive_payroll_action_id (p_payroll_action_id IN NUMBER)
4 RETURN NUMBER
5 IS
6 l_payroll_action_id NUMBER;
7 BEGIN
8 IF p_payroll_action_id IS NULL
9 THEN
10 BEGIN
11 SELECT payroll_action_id
12 INTO l_payroll_action_id
13 FROM pay_payroll_actions ppa,
14 fnd_conc_req_summary_v fcrs,
15 fnd_conc_req_summary_v fcrs1
16 WHERE fcrs.request_id = fnd_global.conc_request_id
17 AND fcrs.priority_request_id = fcrs1.priority_request_id
18 AND ppa.request_id BETWEEN fcrs1.request_id
19 AND fcrs.request_id
20 AND ppa.request_id = fcrs1.request_id;
21 EXCEPTION
22 WHEN OTHERS
23 THEN
24 NULL;
25 END;
26 ELSE
27 l_payroll_action_id := p_payroll_action_id;
28 END IF;
29 RETURN l_payroll_action_id;
30 END;
31 PROCEDURE get_data (
32 p_business_group_id in varchar2,
33 p_payroll_action_id IN VARCHAR2,
34 p_template_name IN VARCHAR2,
35 p_xml OUT NOCOPY CLOB
36 )
37 IS
38 /* Start of declaration*/
39 -- Variables needed for the report
40 l_sum NUMBER;
41 l_counter NUMBER := 1;
42 l_payroll_action_id pay_action_information.action_information1%TYPE;
43 l_legal_employer_id number;
44 l_legal_employer HR_ORGANIZATION_UNITS.name%type;
45 l_eff_date date;
46
47 CURSOR csr_legal_employer is select o.name,fnd_date.canonical_to_date(pai.action_information4) from
48 HR_ORGANIZATION_UNITS o,
49 HR_ORGANIZATION_INFORMATION hoi1,
50 pay_action_information pai
51 where pai.action_context_id = l_payroll_action_id
52 and pai.action_information_category ='EMEA REPORT DETAILS'
53 and o.organization_id = pai.action_information2
54 and hoi1.organization_id = o.organization_id
55 and hoi1.org_information_context = 'CLASS'
56 and hoi1.org_information1 = 'HR_LEGAL_EMPLOYER';
57
58
59 CURSOR csr_scr_data (csr_v_pa_id IN VARCHAR2)
60 IS
61 SELECT pai.assignment_id,
62 pai.action_information1,
63 pai.action_information2 employee_number,
64 pai.action_information3 full_name,
65 pai.action_information4 sc_days
66 FROM pay_action_information pai
67 ,pay_assignment_actions paa
68 WHERE pai.action_context_id = paa.assignment_action_id
69 AND paa.payroll_action_id = csr_v_pa_id
70 AND action_information_category ='EMEA REPORT INFORMATION'
71 AND action_information1 = 'PYNOSFCA';
72
73
74
75 BEGIN
76 l_payroll_action_id := get_archive_payroll_action_id (p_payroll_action_id);
77
78 open csr_legal_employer;
79 fetch csr_legal_employer into l_legal_employer,l_eff_date;
80 close csr_legal_employer;
81
82 gplsqltable (0).tagname := 'LEGAL_EMPLOYER';
83 gplsqltable (0).tagvalue := l_legal_employer;
84 gplsqltable (l_counter).tagname := 'EFFECTIVE_DATE';
85 gplsqltable (l_counter).tagvalue :=to_char(l_eff_date,'DD.Mon.YYYY');
86 l_counter := l_counter+ 1;
87
88 FOR csr_scr_datas IN csr_scr_data(l_payroll_action_id)
89 LOOP
90
91 gplsqltable (l_counter).tagname := 'START';
92 gplsqltable (l_counter).tagvalue := 'START';
93 l_counter := l_counter+ 1;
94
95 gplsqltable (l_counter).tagname := 'FULL_NAME';
96 gplsqltable (l_counter).tagvalue := csr_scr_datas.full_name||' ['||csr_scr_datas.employee_number||']';
97 l_counter := l_counter+ 1;
98
99 gplsqltable (l_counter).tagname := 'SC_DAYS';
100 gplsqltable (l_counter).tagvalue := csr_scr_datas.sc_days;
101 l_counter := l_counter+ 1;
102
103
104 gplsqltable (l_counter).tagname := 'END';
105 gplsqltable (l_counter).tagvalue := 'END';
106 l_counter := l_counter + 1;
107
108 END LOOP;
109
110 writetoclob (p_xml);
111
112 END get_data;
113 -----------------------------------------------------------------------------------------------------------------
114 PROCEDURE writetoclob (p_xfdf_clob OUT NOCOPY CLOB)
115 IS
116 l_xfdf_string CLOB;
117 l_str1 VARCHAR2 (1000);
118 l_str2 VARCHAR2 (20);
119 l_str3 VARCHAR2 (20);
120 l_str4 VARCHAR2 (20);
121 l_str5 VARCHAR2 (20);
122 l_str6 VARCHAR2 (30);
123 l_str7 VARCHAR2 (1000);
124 l_str8 VARCHAR2 (240);
125 l_str9 VARCHAR2 (240);
126 l_str10 VARCHAR2 (20);
127 l_str11 VARCHAR2 (20);
128 current_index PLS_INTEGER;
129 l_counter PLS_INTEGER;
130 l_IANA_charset VARCHAR2 (50);
131 BEGIN
132 l_IANA_charset :=hr_no_utility.get_IANA_charset ;
133 hr_utility.set_location ('Entering WritetoCLOB ', 70);
134 l_str1 := '<?xml version="1.0" encoding="'||l_IANA_charset||'"?> <ROOT>';
135 l_str2 := '<';
136 l_str3 := '>';
137 l_str4 := '</';
138 l_str5 := '>';
139 l_str6 := '</ROOT>';
140 l_str7 := '<?xml version="1.0" encoding="'||l_IANA_charset||'"?> <ROOT></ROOT>';
141 l_str10 := '<SCR>';
142 l_str11 := '</SCR>';
143 DBMS_LOB.createtemporary (l_xfdf_string, FALSE, DBMS_LOB.CALL);
144 DBMS_LOB.OPEN (l_xfdf_string, DBMS_LOB.lob_readwrite);
145 current_index := 0;
146 IF gplsqltable.COUNT > 0
147 THEN
148 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str1), l_str1);
149 FOR table_counter IN gplsqltable.FIRST .. gplsqltable.LAST
150 LOOP
151 l_str8 := gplsqltable (table_counter).tagname;
152 l_str9 := gplsqltable (table_counter).tagvalue;
153 IF l_str9 = 'END'
154 THEN
155 DBMS_LOB.writeappend (
156 l_xfdf_string,
157 LENGTH (l_str11),
158 l_str11
159 );
160 ELSIF l_str9 = 'START'
161 THEN
162 DBMS_LOB.writeappend (
163 l_xfdf_string,
164 LENGTH (l_str10),
165 l_str10
166 );
167 ELSIF l_str9 IS NOT NULL
168 THEN
169 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str2), l_str2);
170 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str8), l_str8);
171 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str3), l_str3);
172 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str9), l_str9);
173 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str4), l_str4);
174 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str8), l_str8);
175 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str5), l_str5);
176 ELSE
177 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str2), l_str2);
178 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str8), l_str8);
179 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str3), l_str3);
180 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str4), l_str4);
181 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str8), l_str8);
182 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str5), l_str5);
183 END IF;
184 END LOOP;
185 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str6), l_str6);
186 ELSE
187 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str7), l_str7);
188 END IF;
189 p_xfdf_clob := l_xfdf_string;
190
191 EXCEPTION
192 WHEN OTHERS
193 THEN
194 hr_utility.TRACE ( 'sqlerrm '
195 || SQLERRM);
196 hr_utility.raise_error;
197 END writetoclob;
198
199 END PAY_NO_SELF_CERTIFICATION_RPT;