DBA Data[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;