[Home] [Help]
PACKAGE BODY: APPS.PAY_MX_ANNUAL_WRI
Source
1 PACKAGE BODY PAY_MX_ANNUAL_WRI AS
2 /* $Header: paymxannualwri.pkb 120.0.12000000.1 2007/02/22 16:24:48 vmehta noship $ */
3 /*
4 ******************************************************************
5 * *
6 * Copyright (C) 2004, Oracle India Pvt. Ltd., Hyderabad *
7 * All rights reserved. *
8 * *
9 * This material has been provided pursuant to an agreement *
10 * containing restrictions on its use. The material is also *
11 * protected by copyright law. No part of this material may *
12 * be copied or distributed, transmitted or transcribed, in *
13 * any form or by any means, electronic, mechanical, magnetic, *
14 * manual, or otherwise, or disclosed to third parties without *
15 * the express written permission of Oracle Corporation, *
16 * 500 Oracle Parkway, Redwood City, CA, 94065. *
17 * *
18 ******************************************************************
19 Package Name : PAY_MX_ANNUAL_WRI
20 Package File Name : paymxannualwri.pkb
21
22 Description : Used for Annual Work Risk Incidents report.
23
24 Change List:
25 ------------
26
27 Name Date Version Bug Text
28 ------------- ----------- ------- ------- ------------------------------
29 sdahiya 18-Oct-2006 115.0 Created.
30 sdahiya 20-Nov-2006 115.1 Modified generate_xml to fetch
31 archived person information
32 exactly once.
33 sdahiya 30-Nov-2006 115.2 5688450 Asg. actions should be created
34 only if a person has 'Incident at
35 Work' or 'Labour Disease' type
36 of risk.
37 ***************************************************************************/
38
39 --
40 -- Global Variables
41 --
42 g_proc_name varchar2(240);
43 g_debug boolean;
44 g_document_type varchar2(50);
45 g_gre_id number;
46 g_start_date varchar2(25);
47 g_end_date varchar2(25);
48
49
50 /****************************************************************************
51 Name : HR_UTILITY_TRACE
52 Description : This procedure prints debug messages.
53 *****************************************************************************/
54 PROCEDURE HR_UTILITY_TRACE
55 (
56 P_TRC_DATA varchar2
57 ) AS
58 BEGIN
59 IF g_debug THEN
60 hr_utility.trace(p_trc_data);
61 END IF;
62 END HR_UTILITY_TRACE;
63
64
65 /****************************************************************************
66 Name : PRINT_BLOB
67 Description : This procedure prints contents of BLOB passed as parameter.
68 *****************************************************************************/
69
70 PROCEDURE PRINT_BLOB(p_blob BLOB) IS
71 BEGIN
72 IF g_debug THEN
73 pay_ac_utility.print_lob(p_blob);
74 END IF;
75 END PRINT_BLOB;
76
77
78 /****************************************************************************
79 Name : WRITE_TO_MAGTAPE_LOB
80 Description : This procedure appends passed BLOB parameter to
81 pay_mag_tape.g_blob_value
82 *****************************************************************************/
83
84 PROCEDURE WRITE_TO_MAGTAPE_LOB(p_blob BLOB) IS
85 BEGIN
86 IF dbms_lob.getLength (p_blob) IS NOT NULL THEN
87 pay_core_files.write_to_magtape_lob (p_blob);
88 END IF;
89 END WRITE_TO_MAGTAPE_LOB;
90
91
92 /****************************************************************************
93 Name : WRITE_TO_MAGTAPE_LOB
94 Description : This procedure appends passed varchar2 parameter to
95 pay_mag_tape.g_blob_value
96 *****************************************************************************/
97
98 PROCEDURE WRITE_TO_MAGTAPE_LOB(p_data varchar2) IS
99 BEGIN
100 pay_core_files.write_to_magtape_lob (p_data);
101 END WRITE_TO_MAGTAPE_LOB;
102
103
104 /****************************************************************************
105 Name : GET_PACT_INFO
106 Description : This procedure fetches payroll action level information.
107 *****************************************************************************/
108 PROCEDURE GET_PACT_INFO
109 (
110 P_PAYROLL_ACTION_ID number,
111 P_GRE_ID OUT NOCOPY number,
112 P_START_DATE OUT NOCOPY varchar2,
113 P_END_DATE OUT NOCOPY varchar2
114 ) IS
115 CURSOR csr_get_mag_pact_info IS
116 SELECT pay_mx_utility.get_legi_param_val('GRE',
117 ppa_mag.legislative_parameters),
118 fnd_date.date_to_canonical (start_date),
119 fnd_date.date_to_canonical (effective_date)
120 FROM pay_payroll_actions ppa_mag
121 WHERE ppa_mag.payroll_action_id = p_payroll_action_id;
122
123 l_proc_name varchar2(100);
124 BEGIN
125 l_proc_name := g_proc_name || 'GET_PACT_INFO';
126 hr_utility_trace ('Entering '||l_proc_name);
127
128 OPEN csr_get_mag_pact_info;
129 FETCH csr_get_mag_pact_info INTO p_gre_id,
130 p_start_date,
131 p_end_date;
132 CLOSE csr_get_mag_pact_info;
133
134 hr_utility_trace ('Leaving '||l_proc_name);
135 END GET_PACT_INFO;
136
137
138 /****************************************************************************
139 Name : RANGE_CURSOR
140 Description : This procedure prepares range of persons to be processed.
141 *****************************************************************************/
142 PROCEDURE RANGE_CURSOR
143 (
144 P_PAYROLL_ACTION_ID number,
145 P_SQLSTR OUT NOCOPY varchar2
146 ) AS
147
148 l_proc_name varchar2(100);
149
150 BEGIN
151 l_proc_name := g_proc_name || 'RANGE_CURSOR';
152
153 hr_utility_trace ('Entering '||l_proc_name);
154 hr_utility_trace ('P_PAYROLL_ACTION_ID = '|| p_payroll_action_id);
155
156 get_pact_info (p_payroll_action_id,
157 g_gre_id,
158 g_start_date,
159 g_end_date);
160
161 p_sqlstr := '
162 SELECT DISTINCT paf.person_id
163 FROM per_assignments_f paf,
164 pay_payroll_actions ppa_sua,
165 pay_assignment_actions paa_sua
166 WHERE ppa_sua.payroll_action_id = paa_sua.payroll_action_id
167 AND paa_sua.assignment_id = paf.assignment_id
168 AND fnd_date.canonical_to_date (pay_mx_utility.get_legi_param_val
169 (''END_DATE'',
170 ppa_sua.legislative_parameters))
171 BETWEEN paf.effective_start_date
172 AND paf.effective_end_date
173 AND fnd_date.canonical_to_date (pay_mx_utility.get_legi_param_val
174 (''END_DATE'',
175 ppa_sua.legislative_parameters))
176 BETWEEN fnd_date.canonical_to_date (''' || g_start_date ||
177 ''') AND fnd_date.canonical_to_date (''' || g_end_date ||
178 ''') AND pay_mx_utility.get_legi_param_val (''GRE'',
179 ppa_sua.legislative_parameters) = '||
180 g_gre_id ||
181 ' AND ppa_sua.report_type = ''SUA_MAG''
182 AND ppa_sua.report_qualifier = ''SUA_MAG''
183 AND ppa_sua.report_category = ''RT''
184 AND ppa_sua.action_status = ''C''
185 AND :p_payroll_action_id > 0';
186
187 hr_utility_trace ('Range cursor query : ' || p_sqlstr);
188 hr_utility_trace ('Leaving '||l_proc_name);
189
190 END RANGE_CURSOR;
191
192
193 /****************************************************************************
194 Name : ACTION_CREATION
195 Description : This procedure creates assignment actions.
196 *****************************************************************************/
197 PROCEDURE ACTION_CREATION
198 (
199 P_PAYROLL_ACTION_ID number,
200 P_START_PERSON_ID number,
201 P_END_PERSON_ID number,
202 P_CHUNK number
203 ) AS
204
205 CURSOR c_sua_asg_act IS
206 SELECT DISTINCT paf.person_id,
207 paf.assignment_id,
208 paa_sua.assignment_action_id,
209 paf.primary_flag,
210 paf.effective_end_date
211 FROM per_assignments_f paf,
212 pay_payroll_actions ppa_sua,
213 pay_assignment_actions paa_sua,
214 pay_action_interlocks lck,
215 pay_action_information pai
216 WHERE ppa_sua.payroll_action_id = paa_sua.payroll_action_id
217 AND paa_sua.assignment_id = paf.assignment_id
218 AND paa_sua.assignment_action_id = lck.locking_action_id
219 AND lck.locked_action_id = pai.action_context_id
220 AND pai.action_information_category = 'MX SS TRANSACTIONS'
221 AND pai.action_information4 = '12'
222 AND pai.action_information23 IS NOT NULL -- Absence end date
223 AND pai.action_information20 IN ('1', '3')--Type of risk(Bug 5688450)
224 AND fnd_date.canonical_to_date (pay_mx_utility.get_legi_param_val
225 ('END_DATE',
226 ppa_sua.legislative_parameters))
227 BETWEEN paf.effective_start_date
228 AND paf.effective_end_date
229 AND fnd_date.canonical_to_date (pay_mx_utility.get_legi_param_val
230 ('END_DATE',
231 ppa_sua.legislative_parameters))
232 BETWEEN fnd_date.canonical_to_date (g_start_date)
233 AND fnd_date.canonical_to_date (g_end_date)
234 AND pay_mx_utility.get_legi_param_val ('GRE',
235 ppa_sua.legislative_parameters) =
236 g_gre_id
237 AND paf.person_id BETWEEN p_start_person_id
238 AND p_end_person_id
239 AND ppa_sua.report_type = 'SUA_MAG'
240 AND ppa_sua.report_qualifier = 'SUA_MAG'
241 AND ppa_sua.report_category = 'RT'
242 AND ppa_sua.action_status = 'C'
243 ORDER BY paf.person_id,
244 decode (paf.primary_flag, 'Y', 1, 2),
245 paf.assignment_id,
246 paf.effective_end_date;
247
248 CURSOR c_sua_asg_act_range IS
249 SELECT DISTINCT paf.person_id,
250 paf.assignment_id,
251 paa_sua.assignment_action_id,
252 paf.primary_flag,
253 paf.effective_end_date
254 FROM per_assignments_f paf,
255 pay_payroll_actions ppa_sua,
256 pay_assignment_actions paa_sua,
257 pay_action_interlocks lck,
258 pay_action_information pai,
259 pay_population_ranges ppr
260 WHERE ppa_sua.payroll_action_id = paa_sua.payroll_action_id
261 AND paa_sua.assignment_id = paf.assignment_id
262 AND paa_sua.assignment_action_id = lck.locking_action_id
263 AND lck.locked_action_id = pai.action_context_id
264 AND pai.action_information_category = 'MX SS TRANSACTIONS'
265 AND pai.action_information4 = '12'
266 AND pai.action_information23 IS NOT NULL -- Absence end date
267 AND pai.action_information20 IN ('1', '3')--Type of risk(Bug 5688450)
268 AND fnd_date.canonical_to_date (pay_mx_utility.get_legi_param_val
269 ('END_DATE',
270 ppa_sua.legislative_parameters))
271 BETWEEN paf.effective_start_date
272 AND paf.effective_end_date
273 AND fnd_date.canonical_to_date (pay_mx_utility.get_legi_param_val
274 ('END_DATE',
275 ppa_sua.legislative_parameters))
276 BETWEEN fnd_date.canonical_to_date (g_start_date)
277 AND fnd_date.canonical_to_date (g_end_date)
278 AND pay_mx_utility.get_legi_param_val ('GRE',
279 ppa_sua.legislative_parameters) =
280 g_gre_id
281 AND ppr.payroll_action_id = p_payroll_action_id
282 AND ppr.chunk_number = p_chunk
283 AND ppr.person_id = paf.person_id
284 AND ppa_sua.report_type = 'SUA_MAG'
285 AND ppa_sua.report_qualifier = 'SUA_MAG'
286 AND ppa_sua.report_category = 'RT'
287 AND ppa_sua.action_status = 'C'
288 ORDER BY paf.person_id,
289 decode (paf.primary_flag, 'Y', 1, 2),
290 paf.assignment_id,
291 paf.effective_end_date;
292
293 l_proc_name varchar2(100);
294 lb_range_person_on boolean;
295 ln_person_id number;
296 ln_prev_person_id number;
297 ln_asg_id number;
298 ln_sua_asg_act number;
299 ln_wri_asg_act number;
300 ld_asg_end_date date;
301 lv_primary_flag per_assignments_f.primary_flag%type;
302
303 BEGIN
304 l_proc_name := g_proc_name || 'ACTION_CREATION';
305 hr_utility_trace ('Entering '||l_proc_name);
306 hr_utility_trace ('Parameters ....');
307 hr_utility_trace ('P_PAYROLL_ACTION_ID = '|| P_PAYROLL_ACTION_ID);
308 hr_utility_trace ('P_START_PERSON_ID = '|| P_START_PERSON_ID);
309 hr_utility_trace ('P_END_PERSON_ID = '|| P_END_PERSON_ID);
310 hr_utility_trace ('P_CHUNK = '|| P_CHUNK);
311
312 ln_prev_person_id := -1;
313
314 IF g_gre_id IS NULL THEN
315 get_pact_info (p_payroll_action_id,
316 g_gre_id,
317 g_start_date,
318 g_end_date);
319 END IF;
320
321 lb_range_person_on := pay_ac_utility.range_person_on(
322 p_report_type => 'MX_ANN_WRI'
323 ,p_report_format => 'MX_ANN_WRI'
324 ,p_report_qualifier => 'MX_ANN_WRI'
325 ,p_report_category => 'RT');
326
327 IF lb_range_person_on THEN
328 hr_utility_trace ('Person ranges are ON');
329 OPEN c_sua_asg_act_range;
330 ELSE
331 hr_utility_trace ('Person ranges are OFF');
332 OPEN c_sua_asg_act;
333 END IF;
334
335 LOOP
336 IF lb_range_person_on THEN
337 FETCH c_sua_asg_act_range INTO ln_person_id,
338 ln_asg_id,
339 ln_sua_asg_act,
340 lv_primary_flag,
341 ld_asg_end_date;
342 EXIT WHEN c_sua_asg_act_range%NOTFOUND;
343 ELSE
344 FETCH c_sua_asg_act INTO ln_person_id,
345 ln_asg_id,
346 ln_sua_asg_act,
347 lv_primary_flag,
348 ld_asg_end_date;
349 EXIT WHEN c_sua_asg_act%NOTFOUND;
350 END IF;
351
352 hr_utility_trace ('-------------');
353 hr_utility_trace('Current person = '||ln_person_id);
354 hr_utility_trace('Previous person = '||ln_prev_person_id);
355
356 IF (ln_person_id <> ln_prev_person_id) THEN
357 SELECT pay_assignment_actions_s.nextval
358 INTO ln_wri_asg_act
359 FROM dual;
360
361 hr_utility_trace('Creating WRI assignment action '||
362 ln_wri_asg_act);
363 hr_nonrun_asact.insact(ln_wri_asg_act,
364 ln_asg_id,
365 p_payroll_action_id,
366 p_chunk,
367 g_gre_id,
368 null,
369 'U',
370 null);
371 ln_prev_person_id := ln_person_id;
372 ELSE
373 hr_utility_trace('WRI assignment action not created');
374 END IF;
375
376 hr_nonrun_asact.insint (ln_wri_asg_act,
377 ln_sua_asg_act);
378 hr_utility_trace('SUA asg action '||ln_sua_asg_act||
379 ' locked by WRI asg action '||ln_wri_asg_act);
380 END LOOP;
381
382 IF lb_range_person_on THEN
383 CLOSE c_sua_asg_act_range;
384 ELSE
385 CLOSE c_sua_asg_act;
386 END IF;
387
388 hr_utility_trace ('Leaving '||l_proc_name);
389 END ACTION_CREATION;
390
391
392 /****************************************************************************
393 Name : INIT
394 Description : Initialization code.
395 *****************************************************************************/
396 PROCEDURE INIT
397 (
398 P_PAYROLL_ACTION_ID number
399 ) AS
400 l_proc_name VARCHAR2(100);
401 BEGIN
402 l_proc_name := g_proc_name || 'INIT';
403 hr_utility_trace ('Entering '||l_proc_name);
404
405 get_pact_info (p_payroll_action_id,
406 g_gre_id,
407 g_start_date,
408 g_end_date);
409
410 hr_utility_trace ('Leaving '||l_proc_name);
411 END INIT;
412
413
414 /****************************************************************************
415 Name : GENERATE_XML
416 Description : This procedure fetches archived data, converts it to XML
417 format and appends to pay_mag_tape.g_blob_value.
418 *****************************************************************************/
419 PROCEDURE GENERATE_XML AS
420
421 CURSOR csr_transactions (cp_wri_asg_act number) IS
422 SELECT pai.action_information_id,
423 nvl(pai.action_information10, 'N') -- Do not report flag
424 FROM pay_action_information pai,
425 pay_action_interlocks lck_sua,
426 pay_action_interlocks lck_arch
427 WHERE lck_sua.locking_action_id = cp_wri_asg_act
428 AND lck_sua.locked_action_id = lck_arch.locking_action_id
429 AND lck_arch.locked_action_id = pai.action_context_id
430 AND pai.action_information_category = 'MX SS TRANSACTIONS'
431 AND pai.action_information4 = '12'
432 AND pai.action_information23 IS NOT NULL; -- Absence end date
433
434 CURSOR csr_person (cp_asg_act_id number) IS
435 SELECT DISTINCT paf.person_id
436 FROM pay_assignment_actions paa,
437 per_assignments_f paf
438 WHERE paf.assignment_id = paa.assignment_id
439 AND paa.assignment_action_id = cp_asg_act_id;
440
441 CURSOR csr_person_info (cp_person_id number) IS
442 SELECT pai.action_information_id
443 FROM pay_action_information pai,
444 pay_assignment_actions paa,
445 pay_payroll_actions ppa,
446 pay_action_interlocks lck,
447 pay_assignment_actions paa_arch,
448 pay_payroll_actions ppa_arch
449 WHERE paa.payroll_action_id = ppa.payroll_action_id
450 AND fnd_number.canonical_to_number(
451 pay_mx_utility.get_legi_param_val('GRE',
452 ppa.legislative_parameters)) = g_gre_id
453 AND fnd_date.canonical_to_date(
454 pay_mx_utility.get_legi_param_val('END_DATE',
455 ppa.legislative_parameters)) <=
456 fnd_date.canonical_to_date(g_end_date)
457 AND paa.assignment_action_id = lck.locking_action_id
458 AND lck.locked_action_id = paa_arch.assignment_action_id
459 AND paa_arch.payroll_action_id = ppa_arch.payroll_action_id
460 AND pai.action_context_id = paa_arch.assignment_action_id
461 AND pai.action_information_category = 'MX SS PERSON INFORMATION'
462 AND nvl(pai.action_information21, 'N') = 'N' -- Do not report flag
463 AND pai.action_information1 = cp_person_id
464 AND ppa.action_type = 'X'
465 AND ppa.report_type = 'SUA_MAG'
466 AND ppa.report_qualifier = 'SUA_MAG'
467 AND ppa.report_category = 'RT'
468 AND ppa.action_status = 'C'
469 AND ppa_arch.action_type = 'X'
470 AND ppa_arch.report_type = 'SS_ARCHIVE'
471 AND ppa_arch.report_qualifier = 'SS_ARCHIVE'
472 AND ppa_arch.report_category = 'RT'
473 AND ppa_arch.action_status = 'C'
474 ORDER BY fnd_date.canonical_to_date(
475 pay_mx_utility.get_legi_param_val('END_DATE',
476 ppa.legislative_parameters)) DESC;
477 /*SELECT pai.action_information_id
478 FROM pay_action_information pai,
479 pay_action_interlocks lck_sua,
480 pay_action_interlocks lck_arch,
481 pay_payroll_actions ppa_arch,
482 pay_assignment_actions paa_arch
483 WHERE lck_sua.locking_action_id = cp_wri_asg_act
484 AND lck_sua.locked_action_id = lck_arch.locking_action_id
485 AND lck_arch.locked_action_id = pai.action_context_id
486 AND pai.action_information_category = 'MX SS PERSON INFORMATION'
487 AND nvl(pai.action_information21, 'N') = 'N' -- Do not report flag
488 AND pai.action_context_id = paa_arch.assignment_action_id
489 AND paa_arch.payroll_action_id = ppa_arch.payroll_action_id
490 ORDER BY fnd_date.canonical_to_date (
491 pay_mx_utility.get_legi_param_val('END_DATE',
492 ppa_arch.legislative_parameters)) DESC;*/
493
494
495 l_proc_name varchar2(100);
496 l_xml BLOB;
497 ln_assignment_action_id number;
498 ln_act_info_id number;
499 ln_person_id number;
500 lv_do_not_report varchar2(1);
501 lt_act_info_id pay_payroll_xml_extract_pkg.int_tab_type;
502 lt_act_info_id_exc pay_payroll_xml_extract_pkg.int_tab_type;
503
504 BEGIN
505 l_proc_name := g_proc_name || 'GENERATE_XML';
506 hr_utility_trace ('Entering '||l_proc_name);
507
508 ln_assignment_action_id := pay_magtape_generic.get_parameter_value
509 ('TRANSFER_ACT_ID');
510
511 hr_utility_trace ('Processing WRI asg action '|| ln_assignment_action_id);
512
513 OPEN csr_transactions (ln_assignment_action_id);
514 LOOP
515 FETCH csr_transactions INTO ln_act_info_id,
516 lv_do_not_report;
517 EXIT WHEN csr_transactions%NOTFOUND;
518
519 IF lv_do_not_report = 'N' THEN
520 lt_act_info_id(lt_act_info_id.count()) := ln_act_info_id;
521 ELSE
522 lt_act_info_id_exc(lt_act_info_id_exc.count()) := ln_act_info_id;
523 END IF;
524 END LOOP;
525 CLOSE csr_transactions;
526
527 OPEN csr_person (ln_assignment_action_id);
528 FETCH csr_person INTO ln_person_id;
529 CLOSE csr_person;
530
531 hr_utility_trace ('WRI asg action '|| ln_assignment_action_id ||
532 ' belongs to person '||ln_person_id);
533
534 OPEN csr_person_info (ln_person_id);
535 FETCH csr_person_info INTO lt_act_info_id(lt_act_info_id.count());
536 CLOSE csr_person_info;
537
538 IF lt_act_info_id.count() = 0 AND
539 lt_act_info_id_exc.count() = 0 THEN
540 hr_utility_trace ('No data to write to BLOB.');
541 ELSE
542 pay_payroll_xml_extract_pkg.generate(lt_act_info_id,
543 NULL,
544 g_document_type,
545 l_xml);
546 write_to_magtape_lob (l_xml);
547
548 pay_payroll_xml_extract_pkg.generate(lt_act_info_id_exc,
549 'WRI_EXCEPTION',
550 g_document_type,
551 l_xml);
552 write_to_magtape_lob (l_xml);
553 END IF;
554
555 print_blob (pay_mag_tape.g_blob_value);
556
557 hr_utility_trace ('Leaving '||l_proc_name);
558 EXCEPTION
559 WHEN OTHERS THEN
560 hr_utility_trace (SQLERRM);
561 RAISE;
562 END GENERATE_XML;
563
564
565 /****************************************************************************
566 Name : GEN_XML_HEADER
567 Description : This procedure generates XML header information to XML BLOB
568 *****************************************************************************/
569 PROCEDURE GEN_XML_HEADER AS
570 l_proc_name varchar2(100);
571 lv_buf varchar2(2000);
572 BEGIN
573 l_proc_name := g_proc_name || 'GEN_XML_HEADER';
574 hr_utility_trace ('Entering '||l_proc_name);
575
576 hr_utility_trace ('Root XML tag = '||
577 pay_magtape_generic.get_parameter_value('ROOT_XML_TAG'));
578
579 lv_buf := pay_magtape_generic.get_parameter_value('ROOT_XML_TAG');
580
581 write_to_magtape_lob (lv_buf);
582
583 hr_utility_trace ('BLOB contents after appending header information');
584 print_blob (pay_mag_tape.g_blob_value);
585
586 hr_utility_trace ('Leaving '||l_proc_name);
587 END GEN_XML_HEADER;
588
589
590 /****************************************************************************
591 Name : GEN_XML_FOOTER
592 Description : This procedure generates XML footer.
593 *****************************************************************************/
594 PROCEDURE GEN_XML_FOOTER AS
595
596 CURSOR csr_employer IS
597 SELECT pai.action_information_id
598 FROM pay_action_information pai,
599 pay_action_interlocks lck_sua,
600 pay_action_interlocks lck_arch,
601 pay_assignment_actions paa_arch,
602 pay_assignment_actions paa_wri
603 WHERE paa_wri.payroll_action_id =
604 pay_magtape_generic.get_parameter_value ('PAYROLL_ACTION_ID')
605 AND lck_sua.locking_action_id = paa_wri.assignment_action_id
606 AND lck_sua.locked_action_id = lck_arch.locking_action_id
607 AND lck_arch.locked_action_id = paa_arch.assignment_action_id
608 AND paa_arch.payroll_action_id = pai.action_context_id
609 AND pai.action_information_category = 'MX SS GRE INFORMATION'
610 AND pai.action_context_type = 'PA'
611 AND ROWNUM = 1;
612
613 CURSOR csr_er_address IS
614 SELECT hl.address_line_1,
615 hl.address_line_2,
616 hl.region_2,
617 hl.postal_code,
618 hl.town_or_city,
619 hr_general.decode_lookup('PER_MX_STATE_CODES', hl.region_1),
620 ft.territory_short_name,
621 hl.telephone_number_1,
622 hl.telephone_number_2
623 FROM hr_locations hl,
624 hr_organization_units hou,
625 fnd_territories_vl ft
626 WHERE hou.location_id = hl.location_id
627 AND ft.territory_code = hl.country
628 AND hou.organization_id = g_gre_id;
629
630
631 lv_street hr_locations.address_line_1%type;
632 lv_neighborhood hr_locations.address_line_2%type;
633 lv_municipality hr_locations.region_2%type;
634 lv_postal_code hr_locations.postal_code%type;
635 lv_city hr_locations.town_or_city%type;
636 lv_state hr_locations.region_1%type;
637 lv_country hr_lookups.meaning%type;
638 lv_telephone hr_locations.telephone_number_1%type;
639 lv_fax hr_locations.telephone_number_2%type;
640 l_proc_name varchar2(100);
641 lv_buf varchar2(8000);
642 l_xml BLOB;
643 lt_act_info_id pay_payroll_xml_extract_pkg.int_tab_type;
644 BEGIN
645 l_proc_name := g_proc_name || 'GEN_XML_FOOTER';
646 hr_utility_trace ('Entering '||l_proc_name);
647
648 OPEN csr_employer;
649 FETCH csr_employer INTO lt_act_info_id(lt_act_info_id.count());
650 CLOSE csr_employer;
651
652 pay_payroll_xml_extract_pkg.generate(lt_act_info_id,
653 NULL,
654 g_document_type,
655 l_xml);
656 write_to_magtape_lob (l_xml);
657
658 OPEN csr_er_address;
659 FETCH csr_er_address INTO lv_street,
660 lv_neighborhood,
661 lv_municipality,
662 lv_postal_code,
663 lv_city,
664 lv_state,
665 lv_country,
666 lv_telephone,
667 lv_fax;
668 CLOSE csr_er_address;
669
670 lv_buf := '<GRE_ADDRESS><STREET>' || lv_street || '</STREET>';
671 lv_buf := lv_buf || '<NEIGHBORHOOD>' ||lv_neighborhood||'</NEIGHBORHOOD>';
672 lv_buf := lv_buf || '<MUNICIPALITY>' ||lv_municipality||'</MUNICIPALITY>';
673 lv_buf := lv_buf || '<POSTAL_CODE>' ||lv_postal_code||'</POSTAL_CODE>';
674 lv_buf := lv_buf || '<CITY>' ||lv_city||'</CITY>';
675 lv_buf := lv_buf || '<STATE>' ||lv_state||'</STATE>';
676 lv_buf := lv_buf || '<COUNTRY>' ||lv_country||'</COUNTRY>';
677 lv_buf := lv_buf || '<TELEPHONE>' ||lv_telephone||'</TELEPHONE>';
678 lv_buf := lv_buf || '<FAX>' ||lv_fax||'</FAX>';
679 lv_buf := lv_buf || '<REPORTING_YEAR>' ||
680 SUBSTR (g_end_date, 1, 4)||'</REPORTING_YEAR></GRE_ADDRESS>';
681
682 lv_buf := lv_buf || '</' ||
683 SUBSTR(pay_magtape_generic.get_parameter_value('ROOT_XML_TAG'),
684 2);
685
686 write_to_magtape_lob (lv_buf);
687
688 hr_utility_trace ('BLOB contents after appending footer information');
689 print_blob (pay_mag_tape.g_blob_value);
690
691 hr_utility_trace ('Leaving '||l_proc_name);
692 END GEN_XML_FOOTER;
693
694 BEGIN
695 --hr_utility.trace_on(null, 'MX_IDC');
696 g_proc_name := 'PAY_MX_ANNUAL_WRI.';
697 g_debug := hr_utility.debug_enabled;
698 g_document_type := 'MX_ANN_WRI';
699 END PAY_MX_ANNUAL_WRI;