[Home] [Help]
PACKAGE BODY: APPS.PAY_SE_TRNR
Source
1 PACKAGE BODY pay_se_trnr AS
2 /* $Header: pysetrnr.pkb 120.2 2007/07/30 12:24:31 psingla noship $ */
3 PROCEDURE get_data (
4 p_business_group_id IN NUMBER,
5 p_payroll_action_id IN VARCHAR2,
6 p_template_name IN VARCHAR2,
7 p_xml OUT NOCOPY CLOB
8 ) IS
9 CURSOR csr_report_parameters IS
10 SELECT fnd_date.canonical_to_date (action_information5) joinig_start_date,
11 fnd_date.canonical_to_date (action_information6)
12 joinig_end_date, action_information9 division,
13 action_information12 agreement_area, action_information13 empployee_category,
14 action_information14
15 assignment_category,
16 fnd_date.canonical_to_date (action_information16) start_date_of_birth,
17 fnd_date.canonical_to_date (action_information17)
18 end_date_of_birth,
19 fnd_date.canonical_to_date (action_information18) precedence_end_date,
20 fnd_date.canonical_to_date (action_information19)
21 report_date, action_information20 sort_order
22 FROM pay_action_information
23 WHERE action_context_id = p_payroll_action_id AND action_information_category = 'EMEA REPORT DETAILS';
24
25 rec_report_parameters csr_report_parameters%ROWTYPE;
26
27 --Cursors needed for report
28 CURSOR csr_all_legal_employer (
29 csr_v_pa_id pay_action_information.action_context_id%TYPE
30 ) IS
31 SELECT action_information3 legal_emp_id, action_information4 legal_employer_name, action_information5 org_number,
32 effective_date
33 FROM pay_action_information
34 WHERE action_context_type = 'PA'
35 AND action_context_id = csr_v_pa_id
36 AND action_information_category = 'EMEA REPORT INFORMATION'
37 AND action_information1 = 'PYSETRNA'
38 AND action_information2 = 'LE';
39
40 /* All Division */
41 CURSOR csr_get_all_divisons (
42 csr_v_pa_id pay_action_information.action_context_id%TYPE,
43 p_legal_employer_id pay_action_information.action_information13%TYPE --,
44 -- p_sort_order pay_action_information.action_information20%TYPE
45 ) IS
46 SELECT DISTINCT action_information14 division_code, action_information16 division
47 FROM pay_payroll_actions paa, pay_assignment_actions assg, pay_action_information pai
48 WHERE paa.payroll_action_id = p_payroll_action_id
49 AND assg.payroll_action_id = paa.payroll_action_id
50 AND pai.action_context_id = assg.assignment_action_id
51 AND action_context_type = 'AAP'
52 --AND action_context_id = csr_v_pa_id
53 AND action_information_category = 'EMEA REPORT INFORMATION'
54 AND action_information1 = 'PYSETRNA'
55 AND action_information13 = p_legal_employer_id;
56
57 /* All Agreement Areas */
58 CURSOR csr_get_all_areas (
59 csr_v_pa_id pay_action_information.action_context_id%TYPE,
60 p_legal_employer_id pay_action_information.action_information13%TYPE,
61 p_div pay_action_information.action_information14%TYPE
62 ) IS
63 SELECT DISTINCT action_information15 area_code, action_information17 agreement_area
64 FROM pay_payroll_actions paa, pay_assignment_actions assg, pay_action_information pai
65 WHERE paa.payroll_action_id = p_payroll_action_id
66 AND assg.payroll_action_id = paa.payroll_action_id
67 AND pai.action_context_id = assg.assignment_action_id
68 AND action_context_type = 'AAP'
69 --AND action_context_id = csr_v_pa_id
70 AND action_information_category = 'EMEA REPORT INFORMATION'
71 AND action_information1 = 'PYSETRNA'
72 AND action_information13 = p_legal_employer_id
73 AND action_information14 = p_div;
74
75 l_payroll_action_id pay_action_information.action_information1%TYPE;
76 l_counter NUMBER := 0;
77 l_count NUMBER := 0;
78 p_sql VARCHAR2 (10000);
79 l_national_identifier pay_action_information.action_information4%TYPE;
80 l_assignment_number pay_action_information.action_information5%TYPE;
81 l_full_name pay_action_information.action_information6%TYPE;
82 l_hire_date DATE;
83 l_total_emp_time NUMBER;
84 l_precedence_date DATE;
85 l_termination_date DATE;
86 l_emp_type pay_action_information.action_information11%TYPE;
87 l_emp_sec pay_action_information.action_information12%TYPE;
88 l_select_str VARCHAR2 (3000);
89
90 TYPE emp_ref IS REF CURSOR;
91
92 csr_get_emp emp_ref;
93 l_sort_order VARCHAR2 (100);
94 BEGIN
95 IF p_payroll_action_id IS NULL THEN
96 BEGIN
97 SELECT payroll_action_id
98 INTO l_payroll_action_id
99 FROM pay_payroll_actions ppa, fnd_conc_req_summary_v fcrs, fnd_conc_req_summary_v fcrs1
100 WHERE fcrs.request_id = fnd_global.conc_request_id
101 AND fcrs.priority_request_id = fcrs1.priority_request_id
102 AND ppa.request_id BETWEEN fcrs1.request_id AND fcrs.request_id
103 AND ppa.request_id = fcrs1.request_id;
104 EXCEPTION
105 WHEN OTHERS THEN
106 NULL;
107 END;
108 ELSE
109 l_payroll_action_id := p_payroll_action_id;
110 END IF;
111
112 OPEN csr_report_parameters;
113 FETCH csr_report_parameters INTO rec_report_parameters;
114 CLOSE csr_report_parameters;
115
116 SELECT 'order by '
117 || decode (
118 nvl (rec_report_parameters.sort_order, 'EMP_TIME'),
119 'EMP_TIME', ' total_emp_time desc ',
120 'ASG_NUM', ' assignment_number asc ',
121 'NAME', ' full_name asc ',
122 'PIN', ' national_identifier asc '
123 )
124 INTO l_sort_order
125 FROM dual;
126
127 FOR rec_leg_emp IN csr_all_legal_employer (l_payroll_action_id)
128 LOOP
129 FOR rec_all_division IN csr_get_all_divisons (l_payroll_action_id, rec_leg_emp.legal_emp_id)
130 LOOP
131 FOR rec_all_area IN csr_get_all_areas (
132 l_payroll_action_id,
133 rec_leg_emp.legal_emp_id,
134 rec_all_division.division_code
135 )
136 LOOP
137 xml_tab (l_counter).tagname := 'ORG_NAME';
138 xml_tab (l_counter).tagvalue := rec_leg_emp.legal_employer_name;
139 l_counter := l_counter + 1;
140
141 --
142 xml_tab (l_counter).tagname := 'J_START_DATE';
143 xml_tab (l_counter).tagvalue := to_char (rec_report_parameters.joinig_start_date, 'YYYYMMDD');
144 l_counter := l_counter + 1;
145 --
146
147 xml_tab (l_counter).tagname := 'J_END_DATE';
148 xml_tab (l_counter).tagvalue := to_char (rec_report_parameters.joinig_end_date, 'YYYYMMDD');
149 l_counter := l_counter + 1;
150 --
151 xml_tab (l_counter).tagname := 'DIV';
152 xml_tab (l_counter).tagvalue := rec_all_division.division;
153 l_counter := l_counter + 1;
154 --
155 xml_tab (l_counter).tagname := 'AGREEMENT_AREA';
156 xml_tab (l_counter).tagvalue := rec_report_parameters.agreement_area;
157 l_counter := l_counter + 1;
158 --
159 xml_tab (l_counter).tagname := 'EMP_CATG';
160 xml_tab (l_counter).tagvalue := rec_report_parameters.empployee_category;
161 l_counter := l_counter + 1;
162 --
163 xml_tab (l_counter).tagname := 'ASG_CATG';
164 xml_tab (l_counter).tagvalue := rec_report_parameters.assignment_category;
165 l_counter := l_counter + 1;
166 --
167
168 xml_tab (l_counter).tagname := 'S_DOB';
169 xml_tab (l_counter).tagvalue := to_char (rec_report_parameters.start_date_of_birth, 'YYYYMMDD');
170 l_counter := l_counter + 1;
171 --
172 xml_tab (l_counter).tagname := 'E_DOB';
173 xml_tab (l_counter).tagvalue := to_char (rec_report_parameters.end_date_of_birth, 'YYYYMMDD');
174 l_counter := l_counter + 1;
175 --
176 xml_tab (l_counter).tagname := 'PREC_DATE';
177 xml_tab (l_counter).tagvalue := to_char (rec_report_parameters.precedence_end_date, 'YYYYMMDD');
178 l_counter := l_counter + 1;
179 ----
180
181 xml_tab (l_counter).tagname := 'REPORT_DATE';
182 xml_tab (l_counter).tagvalue := to_char (rec_report_parameters.report_date, 'YYYYMMDD');
183 l_counter := l_counter + 1;
184 --
185
186 xml_tab (l_counter).tagname := 'PRIORITY_AREA';
187 xml_tab (l_counter).tagvalue := rec_all_area.agreement_area;
188 l_counter := l_counter + 1;
189 --
190 xml_tab (l_counter).tagname := 'PRIORITY_BRANCH';
191 xml_tab (l_counter).tagvalue := rec_all_area.agreement_area;
192 l_counter := l_counter + 1;
193 --
194 xml_tab (l_counter).tagname := 'ORG_NUM';
195 xml_tab (l_counter).tagvalue := rec_leg_emp.org_number;
196 l_counter := l_counter + 1;
197
198
199 l_select_str :=
200 'SELECT action_information4 national_identifier,
201 action_information5
202 assignment_number, action_information6 full_name,
203 fnd_date.canonical_to_date (action_information7)
204 hire_date,
205 fnd_number.canonical_to_number (action_information8) total_emp_time,
206 fnd_date.canonical_to_date (action_information9)
207 precedence_date,
208 fnd_date.canonical_to_date (action_information10) termination_date, action_information11 emp_type,
209 action_information12
210 emp_sec
211 FROM pay_payroll_actions paa, pay_assignment_actions assg, pay_action_information pai
212 WHERE paa.payroll_action_id = '
213 || l_payroll_action_id
214 || ' AND assg.payroll_action_id = paa.payroll_action_id
215 AND pai.action_context_id = assg.assignment_action_id
216 AND action_context_type = '
217 || '''AAP''' || ' AND action_information_category = ' || '''EMEA REPORT INFORMATION'''
218 || ' AND action_information1 = ' || '''PYSETRNA''' || ' AND action_information13 = '
219 || rec_leg_emp.legal_emp_id || ' AND action_information14 = ' || '''' || rec_all_division.division_code || ''''
220 || ' AND action_information15 = ' || '''' || rec_all_area.area_code || '''' || ' ' || l_sort_order;
221
222 fnd_file.put_line (fnd_file.LOG, 'l_select_str :- '||length(l_select_str));
223 l_national_identifier := NULL;
224 l_assignment_number := NULL;
225 l_full_name := NULL;
226 l_hire_date := NULL;
227 l_total_emp_time := NULL;
228 l_precedence_date := NULL;
229 l_termination_date := NULL;
230 l_emp_type := NULL;
231 l_emp_sec := NULL;
232 OPEN csr_get_emp FOR l_select_str;
233
234 LOOP
235 FETCH csr_get_emp INTO l_national_identifier,
236 l_assignment_number,
237 l_full_name,
238 l_hire_date,
239 l_total_emp_time,
240 l_precedence_date,
241 l_termination_date,
242 l_emp_type,
243 l_emp_sec;
244 EXIT WHEN csr_get_emp%NOTFOUND;
245
246 xml_tab (l_counter).tagname := 'NATIONAL_IDENTIFIER';
247 xml_tab (l_counter).tagvalue := l_national_identifier;
248 l_counter := l_counter + 1;
249 --
250 xml_tab (l_counter).tagname := 'EMP_NAME';
251 xml_tab (l_counter).tagvalue := l_full_name;
252 l_counter := l_counter + 1;
253 --
254 xml_tab (l_counter).tagname := 'ASG_NUMBER';
255 xml_tab (l_counter).tagvalue := l_assignment_number;
256 l_counter := l_counter + 1;
257
258 --
259
260 xml_tab (l_counter).tagname := 'TOTAL_EMP_TIME';
261 xml_tab (l_counter).tagvalue := to_char (l_total_emp_time);
262 l_counter := l_counter + 1;
263 --
264 xml_tab (l_counter).tagname := 'EMP_TYPE';
265 xml_tab (l_counter).tagvalue := l_emp_type;
266 l_counter := l_counter + 1;
267 --
268 xml_tab (l_counter).tagname := 'PRECEDENCE_DATE';
269 xml_tab (l_counter).tagvalue := to_char (l_precedence_date, 'YYYYMMDD');
270 l_counter := l_counter + 1;
271 --
272 xml_tab (l_counter).tagname := 'TERMINATION_DATE';
273 xml_tab (l_counter).tagvalue := to_char (l_termination_date, 'YYYYMMDD');
274 l_counter := l_counter + 1;
275 --
276 xml_tab (l_counter).tagname := 'HIRE_DATE';
277 xml_tab (l_counter).tagvalue := to_char (l_hire_date, 'YYYYMMDD');
278 l_counter := l_counter + 1;
279
280 l_select_str := null;
281 END LOOP; -- End of Employee Loop
282 END LOOP; -- End of Agreement Area Loop
283 END LOOP; -- End of Division Loop
284 END LOOP; -- End of Legal Employer Loop
285 writetoclob (p_xml);
286 END get_data;
287
288 -----------------------------------------------------------------------------------------------------------------
289 PROCEDURE writetoclob (
290 p_xfdf_clob OUT NOCOPY CLOB
291 ) IS
292 l_xfdf_string CLOB;
293 l_iana_charset VARCHAR2 (30);
294 current_index PLS_INTEGER;
295 l_str1 VARCHAR2 (1000);
296 l_str2 VARCHAR2 (20);
297 l_str3 VARCHAR2 (20);
298 l_str4 VARCHAR2 (20);
299 l_str5 VARCHAR2 (20);
300 l_str6 VARCHAR2 (30);
301 l_str7 VARCHAR2 (1000);
302 l_str8 VARCHAR2 (240);
303 l_str9 VARCHAR2 (240);
304 l_str10 VARCHAR2 (20);
305 l_str11 VARCHAR2 (20);
306 l_str12 VARCHAR2 (30);
307 l_str13 VARCHAR2 (30);
308 l_str14 VARCHAR2 (30);
309 l_str15 VARCHAR2 (30);
310 l_str16 VARCHAR2 (30);
311 l_str17 VARCHAR2 (30);
312 BEGIN
313 l_iana_charset := hr_se_utility.get_iana_charset;
314 l_str1 := '<?xml version="1.0" encoding="' || l_iana_charset || '"?> <ROOT><PAACR>';
315 l_str2 := '<';
316 l_str3 := '>';
317 l_str4 := '</';
318 l_str5 := '>';
319 l_str6 := '</PAACR></ROOT>';
320 l_str7 := '<?xml version="1.0" encoding="' || l_iana_charset || '"?> <ROOT></ROOT>';
321 l_str10 := '<PAACR>';
322 l_str11 := '</PAACR>';
323 l_str12 := '<FILE_HEADER_START>';
324 l_str13 := '</FILE_HEADER_START>';
325 l_str14 := '<LE_RECORD>';
326 l_str15 := '</LE_RECORD>';
327 l_str14 := '<LE_RECORD>';
328 l_str15 := '</LE_RECORD>';
329 l_str16 := '<EMP_RECORD>';
330 l_str17 := '</EMP_RECORD>';
331 dbms_lob.createtemporary (l_xfdf_string, FALSE , dbms_lob.CALL);
332 dbms_lob.OPEN (l_xfdf_string, dbms_lob.lob_readwrite);
333 current_index := 0;
334
335 IF xml_tab.count > 0 THEN
336 dbms_lob.writeappend (l_xfdf_string, LENGTH (l_str1), l_str1);
337 dbms_lob.writeappend (l_xfdf_string, LENGTH (l_str12), l_str12);
338
339 FOR table_counter IN xml_tab.FIRST .. xml_tab.LAST
340 LOOP
341 l_str8 := xml_tab (table_counter).tagname;
342 l_str9 := xml_tab (table_counter).tagvalue;
343
344 IF l_str8 = 'ORG_NAME' THEN
345 dbms_lob.writeappend (l_xfdf_string, LENGTH (l_str14), l_str14);
346 ELSIF l_str8 = 'NATIONAL_IDENTIFIER' THEN
347 dbms_lob.writeappend (l_xfdf_string, LENGTH (l_str16), l_str16);
348 END IF;
349
350 IF l_str9 IS NOT NULL THEN
351 l_str9 := '<![CDATA[' || l_str9 || ']]>';
352 dbms_lob.writeappend (l_xfdf_string, LENGTH (l_str2), l_str2);
353 dbms_lob.writeappend (l_xfdf_string, LENGTH (l_str8), l_str8);
354 dbms_lob.writeappend (l_xfdf_string, LENGTH (l_str3), l_str3);
355 dbms_lob.writeappend (l_xfdf_string, LENGTH (l_str9), l_str9);
356 dbms_lob.writeappend (l_xfdf_string, LENGTH (l_str4), l_str4);
357 dbms_lob.writeappend (l_xfdf_string, LENGTH (l_str8), l_str8);
358 dbms_lob.writeappend (l_xfdf_string, LENGTH (l_str5), l_str5);
359 ELSE
360 dbms_lob.writeappend (l_xfdf_string, LENGTH (l_str2), l_str2);
361 dbms_lob.writeappend (l_xfdf_string, LENGTH (l_str8), l_str8);
362 dbms_lob.writeappend (l_xfdf_string, LENGTH (l_str3), l_str3);
363 dbms_lob.writeappend (l_xfdf_string, LENGTH (l_str4), l_str4);
364 dbms_lob.writeappend (l_xfdf_string, LENGTH (l_str8), l_str8);
365 dbms_lob.writeappend (l_xfdf_string, LENGTH (l_str5), l_str5);
366 END IF;
367
368 IF xml_tab.LAST = table_counter THEN
369 dbms_lob.writeappend (l_xfdf_string, LENGTH (l_str17), l_str17);
370 dbms_lob.writeappend (l_xfdf_string, LENGTH (l_str15), l_str15);
371 ELSIF l_str8 = 'HIRE_DATE' AND xml_tab (table_counter + 1).tagname = 'NATIONAL_IDENTIFIER' THEN
372 dbms_lob.writeappend (l_xfdf_string, LENGTH (l_str17), l_str17);
373 ELSIF l_str8 = 'HIRE_DATE' AND xml_tab (table_counter + 1).tagname = 'ORG_NAME' THEN
374 dbms_lob.writeappend (l_xfdf_string, LENGTH (l_str17), l_str17);
375 dbms_lob.writeappend (l_xfdf_string, LENGTH (l_str15), l_str15);
376 ELSIF xml_tab (table_counter + 1).tagname = 'ORG_NAME' THEN
377 dbms_lob.writeappend (l_xfdf_string, LENGTH (l_str15), l_str15);
378 END IF;
379 END LOOP;
380
381 dbms_lob.writeappend (l_xfdf_string, LENGTH (l_str13), l_str13);
382 dbms_lob.writeappend (l_xfdf_string, LENGTH (l_str6), l_str6);
383 ELSE
384 dbms_lob.writeappend (l_xfdf_string, LENGTH (l_str7), l_str7);
385 END IF;
386
387 p_xfdf_clob := l_xfdf_string;
388 hr_utility.set_location ('Leaving WritetoCLOB ', 20);
389 END writetoclob;
390 -------------------------------------------------------------------------------------------------------------------------
391
392 END pay_se_trnr;