[Home] [Help]
PACKAGE BODY: APPS.PAY_MX_SUA_MAG
Source
1 PACKAGE BODY PAY_MX_SUA_MAG AS
2 /* $Header: paymxsuamag.pkb 120.32.12010000.2 2008/08/06 06:36:27 ubhat ship $ */
3 /* +======================================================================+
4 | Copyright (c) 2003 Oracle Corporation |
5 | Redwood Shores, California, USA |
6 | All rights reserved. |
7 +======================================================================+
8 Package Name : pay_mx_sua_mag
9 Package File Name : paymxsuamag.pkb
10
11 Description : Used for SUA Interface Extract
12
13 Change List:
14 ------------
15
16 Name Date Version Bug Text
17 ------------- ----------- ------- ------- ------------------------------
18 vpandya 29-Apr-2005 115.0 Initial Version
19 sdahiya 11-Jul-2005 115.1 Added range code, action creation
20 code and XML generation mechanism.
21 sdahiya 13-Jul-2005 115.2 Modified GET_START_DATE to return
22 implementation date if the SUA
23 request is run for the first time.
24 sdahiya 15-Jul-2005 115.3 Modified GENERATE_XML procedure
25 to handle custom exception tags.
26 sdahiya 27-Jul-2005 115.4 4518777 Used substring of serial_number
27 column to get person_id.
28
29 4518732 Modified get_start_date to read
30 the start date from
31 pay_payroll_actions instead of
32 pay_recorded_requests. This will
33 facilitate rollbacks of SUA
34 Interface Extract concurrent
35 program.
36 sdahiya 28-Jul-2005 115.5 Modified GENERATE_XML so that
37 XML for transactions is generated
38 even if there are no corresponding
39 person records archived.
40 sdahiya 04-Aug-2005 115.6 4518777 Global variables holding payroll
41 action information should be
42 re-initialized for each thread.
43 sdahiya 05-Aug-2005 115.7 Modified ACTION_CREATION to lock
44 all archiver asg action across
45 multiple archiver runs.
46 sdahiya 09-Aug-2005 115.8 4541979 CLOB variables should not be read
47 if no archived information exists
48 and consequently there is nothing
49 to be written to CLOB.
50 sdahiya 09-Aug-2005 115.9 Added REPORT_PERIOD tag.
51 sdahiya 10-Aug-2005 115.10 Added payroll_action_id join
52 condition in action_creation.
53 sdahiya 10-Aug-2005 115.11 Re-initialized global variables
54 in GEN_XML_FOOTER.
55 sdahiya 19-Aug-2005 115.12 Added document_type parameter in
56 calls to
57 pay_payroll_xml_extract_pkg.
58 generate.
59 vpandya 31-Oct-2005 115.13 4710619 Changed cursor get_emp_trans,
60 using action_information5 in
61 place of effective_date in
62 where clause.
63 vmehta 09-Nov-2005 115.14 Increased the size of lv_buf
64 in multiple places to allow for
65 multibyte characterset expansion.
66 vmehta 23-Nov-2005 115.15 Modified get_arch_pact_id.
67 Removed pay_action_information
68 from the list of tables.
69 sdahiya 21-NOV-2005 115.16 4773967 CLOB to BLOB changes.
70 sdahiya 01-DEC-2005 115.17 Modified PRINT_BLOB to use
71 pay_ac_utility.print_lob.
72 sdahiya 01-DEC-2005 115.18 Used core procedure
73 pay_core_files.write_to_magtape_lob
74 to manipulate core magtape BLOB.
75 sdahiya 22-Dec-2005 115.19 Removed XML header information.
76 PYUGEN will generate XML headers.
77 sdahiya 18-Apr-2006 115.20 4864237 Performance fix.
78 sdahiya 04-Aug-2006 115.21 XML should contain always contain
79 worker data record.
80 sdahiya 05-Sep-2006 115.22 Worker data for a person should
81 appear exactly once in the XML
82 even if it was archived multiple
83 times.
84 sdahiya 17-Sep-2006 115.23 Modified the order by clause of
85 c_arch_asg_range and c_arch_asg
86 to ensure that only one assignment
87 action per person is created.
88 sdahiya 13-Feb-2007 115.24 5878927 Modified the order by clause of
89 c_arch_asg_range and c_arch_asg
90 to avoid insertion of duplicate
91 action interlocks due to multiple
92 archiver runs.
93 sdahiya 02-Mar-2007 115.25 Modified the process to pick only
94 those transactions which are
95 effective in the reporting period.
96 sdahiya 08-Apr-2007 115.26 07 transactions should not be
97 reported if IDW amount hasn't
98 changed since it was reported
99 last.
100 sdahiya 19-Apr-2007 115.27 5998981 Modified cursor csr_prev_idw to
101 conditionally convert canonical
102 date stamped on action information
103 DDF.
104 sdahiya 19-Apr-2007 115.28 6004485 Modified action_creation to ensure
105 that exactly one interlock is
106 inserted for every archiver asg
107 action.
108 sdahiya 23-Apr-2007 115.29 Modified generate_xml to use
109 transaction processing from
110 affiliation report.
111 sdahiya 15-May-2007 115.30 Modified action_creation and
112 generate_xml so that past-dated
113 transactions are picked.
114 nragavar 31-May-2007 115.31 6073090 Person information selection was
115 not done in cursors c_arch_asg,
116 c_arch_asg_range
117 nragavar 12-Jul-2007 115.32 6198089 added new procedure INIT
118 sivanara 27-Jun-2008 115.33 7185703 added logic to filter the
119 transaction after implementation
120 date.
121 ========================================================================*/
122
123 --
124 -- Global Variables
125 --
126 TYPE char_tab IS TABLE OF pay_action_information.action_information1%type
127 INDEX BY BINARY_INTEGER;
128 g_xml_cache char_tab;
129 g_proc_name varchar2(240);
130 g_debug boolean;
131 g_document_type varchar2(50);
132
133 /****************************************************************************
134 Name : HR_UTILITY_TRACE
135 Description : This procedure prints debug messages.
136 *****************************************************************************/
137 PROCEDURE HR_UTILITY_TRACE
138 (
139 P_TRC_DATA varchar2
140 ) AS
141 BEGIN
142 IF g_debug THEN
143 hr_utility.trace(p_trc_data);
144 END IF;
145 END HR_UTILITY_TRACE;
146
147
148 /****************************************************************************
149 Name : PRINT_BLOB
150 Description : This procedure prints contents of BLOB passed as parameter.
151 *****************************************************************************/
152
153 PROCEDURE PRINT_BLOB(p_blob BLOB) IS
154 BEGIN
155 IF g_debug THEN
156 pay_ac_utility.print_lob(p_blob);
157 END IF;
158 END PRINT_BLOB;
159
160
161 /****************************************************************************
162 Name : WRITE_TO_MAGTAPE_LOB
163 Description : This procedure appends passed BLOB parameter to
164 pay_mag_tape.g_blob_value
165 *****************************************************************************/
166
167 PROCEDURE WRITE_TO_MAGTAPE_LOB(p_blob BLOB) IS
168 BEGIN
169 IF dbms_lob.getLength (p_blob) IS NOT NULL THEN
170 pay_core_files.write_to_magtape_lob (p_blob);
171 END IF;
172 END WRITE_TO_MAGTAPE_LOB;
173
174
175 /****************************************************************************
176 Name : WRITE_TO_MAGTAPE_LOB
177 Description : This procedure appends passed varchar2 parameter to
178 pay_mag_tape.g_blob_value
179 *****************************************************************************/
180
181 PROCEDURE WRITE_TO_MAGTAPE_LOB(p_data varchar2) IS
182 BEGIN
183 pay_core_files.write_to_magtape_lob (p_data);
184 END WRITE_TO_MAGTAPE_LOB;
185
186
187 /****************************************************************************
188 Name : GET_PACT_INFO
189 Description : This procedure fetches payroll action level information.
190 *****************************************************************************/
191 PROCEDURE GET_PACT_INFO
192 (
193 P_PAYROLL_ACTION_ID number,
194 P_GRE_ID OUT NOCOPY number,
195 P_START_DATE OUT NOCOPY varchar2,
196 P_END_DATE OUT NOCOPY varchar2,
197 P_MODE OUT NOCOPY varchar2
198 ) IS
199 CURSOR csr_get_mag_pact_info IS
200 SELECT pay_mx_utility.get_legi_param_val('GRE',
201 ppa_mag.legislative_parameters),
202 pay_mx_utility.get_legi_param_val('START_DATE',
203 ppa_mag.legislative_parameters),
204 pay_mx_utility.get_legi_param_val('END_DATE',
205 ppa_mag.legislative_parameters),
206 pay_mx_utility.get_legi_param_val('MODE',
207 ppa_mag.legislative_parameters)
208 FROM pay_payroll_actions ppa_mag
209 WHERE ppa_mag.payroll_action_id = p_payroll_action_id;
210
211 l_proc_name varchar2(100);
212 BEGIN
213 l_proc_name := g_proc_name || 'GET_PACT_INFO';
214 hr_utility_trace ('Entering '||l_proc_name);
215
216 OPEN csr_get_mag_pact_info;
217 FETCH csr_get_mag_pact_info INTO p_gre_id,
218 p_start_date,
219 p_end_date,
220 p_mode;
221 CLOSE csr_get_mag_pact_info;
222
223 hr_utility_trace ('Leaving '||l_proc_name);
224 END GET_PACT_INFO;
225
226
227 /****************************************************************************
228 Name : GET_START_DATE
229 Description : This function returns start date.
230 *****************************************************************************/
231 FUNCTION GET_START_DATE
232 (
233 P_MODE varchar2, -- FULL/INCREMENT
234 P_GRE_ID number
235 ) RETURN varchar2 AS
236
237 CURSOR c_get_bus_grp_id IS
238 SELECT hou.business_group_id
239 FROM hr_organization_units hou
240 WHERE hou.organization_id = p_gre_id;
241
242 CURSOR c_get_imp_date(cp_organization_id IN NUMBER) IS
243 SELECT org_information6
244 FROM hr_organization_information
245 WHERE org_information_context = 'MX_TAX_REGISTRATION'
246 AND organization_id = cp_organization_id;
247
248 CURSOR c_get_last_run_date IS
249 SELECT fnd_date.date_to_canonical(
250 fnd_date.canonical_to_date(
251 pay_mx_utility.get_legi_param_val ('END_DATE',
252 ppa.legislative_parameters)) +
253 1/(24 * 60 * 60))
254 FROM pay_payroll_actions ppa
255 WHERE pay_mx_utility.get_legi_param_val('GRE',
256 ppa.legislative_parameters) =
257 p_gre_id
258 AND ppa.report_type = 'SUA_MAG'
259 AND ppa.report_qualifier = 'SUA_MAG'
260 AND ppa.report_category = 'RT'
261 AND ppa.action_type = 'X'
262 AND ppa.action_status = 'C'
263 ORDER BY ppa.payroll_action_id DESC;
264
265 lv_report_imp_date varchar2(25);
266 lv_start_date varchar2(50);
267 ld_start_date date;
268 ln_legal_employer_id number;
269 ln_bus_grp_id number;
270 l_proc_name varchar2(100);
271
272 BEGIN
273 l_proc_name := g_proc_name || 'GET_START_DATE';
274 hr_utility_trace ('Entering '||l_proc_name);
275 hr_utility_trace ('Parameters ...');
276 hr_utility_trace ('P_MODE = '||P_MODE);
277 hr_utility_trace ('P_GRE_ID = '||P_GRE_ID);
278
279 -- GET LEGAL EMPLOYER ID FROM GRE ID
280
281 OPEN c_get_bus_grp_id;
282 FETCH c_get_bus_grp_id INTO ln_bus_grp_id;
283 CLOSE c_get_bus_grp_id;
284
285 ln_legal_employer_id :=
286 hr_mx_utility.get_legal_employer(ln_bus_grp_id, p_gre_id);
287
288 -- get the report Implementation Date from p_legal_emp_id
289
290 OPEN c_get_imp_date(ln_legal_employer_id);
291 FETCH c_get_imp_date INTO lv_start_date;
292 IF ((c_get_imp_date%NOTFOUND) OR (lv_start_date IS NULL)) THEN
293 -- defaulting to Report Implementation Date from
294 -- mx pay legislation info table
295 lv_start_date := pay_mx_utility.get_default_imp_date;
296 END IF;
297 CLOSE c_get_imp_date;
298
299 IF (p_mode = 'INCREMENT') THEN
300 -- Bug 4518732
301 OPEN c_get_last_run_date;
302 FETCH c_get_last_run_date INTO lv_start_date;
303 CLOSE c_get_last_run_date;
304 END IF;
305
306 hr_utility_trace ('Start date = '|| lv_start_date);
307 hr_utility_trace ('Leaving '||l_proc_name);
308 RETURN lv_start_date ;
309
310 END GET_START_DATE;
311
312
313 /****************************************************************************
314 Name : RANGE_CURSOR
315 Description : This procedure prepares range of persons to be processed.
316 *****************************************************************************/
317 PROCEDURE RANGE_CURSOR
318 (
319 P_PAYROLL_ACTION_ID number,
320 P_SQLSTR OUT NOCOPY varchar2
321 ) AS
322
323 l_proc_name varchar2(100);
324
325 BEGIN
326 l_proc_name := g_proc_name || 'RANGE_CURSOR';
327 hr_utility_trace ('Entering '||l_proc_name);
328
329 hr_utility_trace ('P_PAYROLL_ACTION_ID = '|| p_payroll_action_id);
330
331 get_pact_info (p_payroll_action_id,
332 g_mag_gre_id,
333 g_mag_start_date,
334 g_mag_end_date,
335 g_mag_mode);
336
337 -- Bug 4518777
338 p_sqlstr := '
339 SELECT DISTINCT substr(paa_arch.serial_number, 1,
340 instr(paa_arch.serial_number, ''|'')-1)
341 FROM pay_assignment_actions paa_arch,
342 pay_payroll_actions ppa_arch
343 WHERE paa_arch.payroll_action_id = ppa_arch.payroll_action_id
344 AND paa_arch.tax_unit_id = '|| g_mag_gre_id ||'/*
345 AND fnd_date.canonical_to_date (pay_mx_utility.get_legi_param_val(''END_DATE'',
346 ppa_arch.legislative_parameters))
347 BETWEEN fnd_date.canonical_to_date ('''|| g_mag_start_date ||''')
348 AND fnd_date.canonical_to_date ('''|| g_mag_end_date ||''')
349 */AND ppa_arch.action_type = ''X''
350 AND ppa_arch.report_type = ''SS_ARCHIVE''
351 AND ppa_arch.report_qualifier = ''SS_ARCHIVE''
352 AND ppa_arch.report_category = ''RT''
353 AND ppa_arch.action_status = ''C''
354 AND :p_payroll_action_id = '||p_payroll_action_id||'
355 ORDER BY 1';
356
357 hr_utility_trace ('Range cursor query : ' || p_sqlstr);
358 hr_utility_trace ('Leaving '||l_proc_name);
359
360 END RANGE_CURSOR;
361
362
363 /****************************************************************************
364 Name : ACTION_CREATION
365 Description : This procedure creates assignment actions for SUA magnetic
366 tape process.
367 *****************************************************************************/
368 PROCEDURE ACTION_CREATION
369 (
370 P_PAYROLL_ACTION_ID number,
371 P_START_PERSON_ID number,
372 P_END_PERSON_ID number,
373 P_CHUNK number
374 ) AS
375
376 CURSOR c_arch_asg IS
377 SELECT paa_arch.assignment_action_id,
378 paf.assignment_id,
379 paf.person_id,
380 ppa_arch.payroll_action_id
381 FROM pay_assignment_actions paa_arch,
382 pay_payroll_actions ppa_arch,
383 per_all_assignments_f paf,
384 pay_action_information pai
385 WHERE paa_arch.payroll_action_id = ppa_arch.payroll_action_id
386 AND paa_arch.assignment_id = paf.assignment_id
387 -- Bug 4518777
388 AND paf.person_id BETWEEN p_start_person_id AND p_end_person_id
389 AND paa_arch.tax_unit_id = g_mag_gre_id
390 /*AND fnd_date.canonical_to_date (pay_mx_utility.get_legi_param_val(
391 'END_DATE',
392 ppa_arch.legislative_parameters))
393 BETWEEN fnd_date.canonical_to_date(g_mag_start_date)
394 AND fnd_date.canonical_to_date(g_mag_end_date)*/
395 ----
396 AND paa_arch.assignment_action_id = pai.action_context_id
397 AND (( pai.action_information_category = 'MX SS TRANSACTIONS'
398 AND (fnd_date.canonical_to_date (pai.action_information2) BETWEEN
399 fnd_date.canonical_to_date (g_mag_start_date)
400 AND fnd_date.canonical_to_date (g_mag_end_date)
401 OR (pai.effective_date BETWEEN
402 fnd_date.canonical_to_date (g_mag_start_date)
403 AND fnd_date.canonical_to_date (g_mag_end_date)
404 AND fnd_date.canonical_to_date (pai.action_information2) <=
405 fnd_date.canonical_to_date (g_mag_end_date)))
406 )
407 OR pai.action_information_category = 'MX SS PERSON INFORMATION'
408 )
409 AND paa_arch.tax_unit_id = g_mag_gre_id
410 /*AND NOT EXISTS (SELECT 'X'
411 FROM pay_payroll_actions ppa_sua,
412 pay_assignment_actions paa_sua,
413 pay_action_interlocks lck
414 WHERE lck.locked_action_id = pai.action_context_id
415 AND lck.locking_action_id =
416 paa_sua.assignment_action_id
417 AND paa_sua.payroll_action_id =
418 ppa_sua.payroll_action_id
419 AND ppa_sua.report_type = 'SUA_MAG'
420 AND ppa_sua.report_qualifier = 'SUA_MAG'
421 AND ppa_sua.report_category = 'RT'
422 AND ppa_sua.action_status = 'C')*/
423 ----
424 AND ppa_arch.action_type = 'X'
425 AND ppa_arch.report_type = 'SS_ARCHIVE'
426 AND ppa_arch.report_qualifier = 'SS_ARCHIVE'
427 AND ppa_arch.report_category = 'RT'
428 AND ppa_arch.action_status = 'C'
429 ORDER BY paf.person_id,
430 decode (paf.primary_flag, 'Y', 1, 2),
431 paf.assignment_id,
432 ppa_arch.payroll_action_id,
433 paf.effective_end_date;
434
435 CURSOR c_arch_asg_range IS
436 SELECT paa_arch.assignment_action_id,
437 paf.assignment_id,
438 paf.person_id,
439 ppa_arch.payroll_action_id
440 FROM pay_assignment_actions paa_arch,
441 pay_payroll_actions ppa_arch,
442 per_all_assignments_f paf,
443 pay_population_ranges ppr,
444 pay_action_information pai
445 WHERE paa_arch.payroll_action_id = ppa_arch.payroll_action_id
446 AND paa_arch.assignment_id = paf.assignment_id
447 AND paf.person_id = ppr.person_id
448 AND ppr.chunk_number = p_chunk
449 AND ppr.payroll_action_id = p_payroll_action_id
450 AND paa_arch.tax_unit_id = g_mag_gre_id
451 /*AND fnd_date.canonical_to_date (pay_mx_utility.get_legi_param_val(
452 'END_DATE',
453 ppa_arch.legislative_parameters))
454 BETWEEN fnd_date.canonical_to_date(g_mag_start_date)
455 AND fnd_date.canonical_to_date(g_mag_end_date)*/
456 ----
457 AND paa_arch.assignment_action_id = pai.action_context_id
458 AND ((pai.action_information_category = 'MX SS TRANSACTIONS'
459 AND (fnd_date.canonical_to_date (pai.action_information2) BETWEEN
460 fnd_date.canonical_to_date (g_mag_start_date)
461 AND fnd_date.canonical_to_date (g_mag_end_date)
462 OR (pai.effective_date BETWEEN
463 fnd_date.canonical_to_date (g_mag_start_date)
464 AND fnd_date.canonical_to_date (g_mag_end_date)
465 AND fnd_date.canonical_to_date (pai.action_information2) <=
466 fnd_date.canonical_to_date (g_mag_end_date)))
467 )
468 OR pai.action_information_category = 'MX SS PERSON INFORMATION'
469 )
470 AND paa_arch.tax_unit_id = g_mag_gre_id
471 /*AND NOT EXISTS (SELECT 'X'
472 FROM pay_payroll_actions ppa_sua,
473 pay_assignment_actions paa_sua,
474 pay_action_interlocks lck
475 WHERE lck.locked_action_id = pai.action_context_id
476 AND lck.locking_action_id =
477 paa_sua.assignment_action_id
478 AND paa_sua.payroll_action_id =
479 ppa_sua.payroll_action_id
480 AND ppa_sua.report_type = 'SUA_MAG'
481 AND ppa_sua.report_qualifier = 'SUA_MAG'
482 AND ppa_sua.report_category = 'RT'
483 AND ppa_sua.action_status = 'C')*/
484 ----
485 AND ppa_arch.action_type = 'X'
486 AND ppa_arch.report_type = 'SS_ARCHIVE'
487 AND ppa_arch.report_qualifier = 'SS_ARCHIVE'
488 AND ppa_arch.report_category = 'RT'
489 AND ppa_arch.action_status = 'C'
490 ORDER BY paf.person_id,
491 decode (paf.primary_flag, 'Y', 1, 2),
492 paf.assignment_id,
493 ppa_arch.payroll_action_id,
494 paf.effective_end_date;
495
496 CURSOR csr_future_magtape_exists IS
497 SELECT 'Y'
498 FROM pay_payroll_actions ppa
499 WHERE ppa.report_type = 'SUA_MAG'
500 AND ppa.report_qualifier = 'SUA_MAG'
501 AND ppa.report_category = 'RT'
502 AND ppa.action_type = 'X'
503 AND ppa.action_status = 'C'
504 AND pay_mx_utility.get_legi_param_val('GRE',
505 ppa.legislative_parameters) =
506 g_mag_gre_id
507 AND fnd_date.canonical_to_date(pay_mx_utility.get_legi_param_val(
508 'END_DATE',
509 ppa.legislative_parameters)) >
510 fnd_date.canonical_to_date(g_mag_end_date);
511
512
513 TYPE num_tab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
514 lt_arch_act num_tab;
515 l_proc_name varchar2(100);
516 lv_future_magtape_exists varchar2(1);
517 lb_range_person_on boolean;
518 ln_person_id number;
519 ln_prev_arch_pact_id number;
520 ln_arch_pact_id number;
521 ln_prev_person_id number;
522 ln_prev_asg_id number;
523 ln_mag_asg_act_id number;
524 ln_assignment_id number;
525 ln_arch_act_id number;
526 ln_asg_count number;
527 BEGIN
528 l_proc_name := g_proc_name || 'ACTION_CREATION';
529 hr_utility_trace ('Entering '||l_proc_name);
530 hr_utility_trace ('Parameters ....');
531 hr_utility_trace ('P_PAYROLL_ACTION_ID = '|| P_PAYROLL_ACTION_ID);
532 hr_utility_trace ('P_START_PERSON_ID = '|| P_START_PERSON_ID);
533 hr_utility_trace ('P_END_PERSON_ID = '|| P_END_PERSON_ID);
534 hr_utility_trace ('P_CHUNK = '|| P_CHUNK);
535
536 ln_prev_person_id := -1;
537 ln_prev_asg_id := -1;
538 ln_prev_arch_pact_id := -1;
539
540 -- Bug 4518777
541 IF g_mag_gre_id IS NULL THEN
542 get_pact_info (p_payroll_action_id,
543 g_mag_gre_id,
544 g_mag_start_date,
545 g_mag_end_date,
546 g_mag_mode);
547 END IF;
548
549 /* Raise an error if magtape is run in FULL mode and future magtape runs
550 already exist */
551 IF (g_mag_mode = 'FULL') THEN
552 OPEN csr_future_magtape_exists;
553 FETCH csr_future_magtape_exists INTO lv_future_magtape_exists;
554 CLOSE csr_future_magtape_exists;
555
556 IF lv_future_magtape_exists = 'Y' THEN
557 /* Currently we are not supporting FULL mode magtape runs. So, this
558 portion of code will never execute. */
559 --hr_utility.set_message(801, 'PAY_FUTURE_SUA_MAG_EXISTS');
560 --hr_utility.raise_error;
561 NULL;
562 END IF;
563 END IF;
564
565 ln_asg_count := 0;
566
567 lb_range_person_on := pay_ac_utility.range_person_on(
568 p_report_type => 'SUA_MAG'
569 ,p_report_format => 'SUA_MAG'
570 ,p_report_qualifier => 'SUA_MAG'
571 ,p_report_category => 'RT');
572
573 IF lb_range_person_on THEN
574 hr_utility_trace ('Person ranges are ON');
575 OPEN c_arch_asg_range;
576 ELSE
577 hr_utility_trace ('Person ranges are OFF');
578 OPEN c_arch_asg;
579 END IF;
580
581 LOOP
582 IF lb_range_person_on THEN
583 FETCH c_arch_asg_range INTO ln_arch_act_id,
584 ln_assignment_id,
585 ln_person_id,
586 ln_arch_pact_id;
587 EXIT WHEN c_arch_asg_range%NOTFOUND;
588 ELSE
589 FETCH c_arch_asg INTO ln_arch_act_id,
590 ln_assignment_id,
591 ln_person_id,
592 ln_arch_pact_id;
593 EXIT WHEN c_arch_asg%NOTFOUND;
594 END IF;
595
596 ln_asg_count := ln_asg_count + 1;
597
598 hr_utility_trace ('-------------');
599 hr_utility_trace('Current archiver asg action = '||ln_arch_act_id);
600 hr_utility_trace('Current person = '||ln_person_id);
601 hr_utility_trace('Previous person = '||ln_prev_person_id);
602
603 IF (ln_person_id <> ln_prev_person_id) THEN
604 SELECT pay_assignment_actions_s.nextval
605 INTO ln_mag_asg_act_id
606 FROM dual;
607
608 hr_utility_trace('Creating magtape assignment action '||
609 ln_mag_asg_act_id);
610 hr_nonrun_asact.insact(ln_mag_asg_act_id,
611 ln_assignment_id,
612 p_payroll_action_id,
613 p_chunk,
614 g_mag_gre_id,
615 null,
616 'U',
617 null);
618 ln_prev_person_id := ln_person_id;
619 lt_arch_act.DELETE(); -- Bug 6004485
620 ELSE
621 hr_utility_trace('Magtape assignment action not created');
622 END IF;
623
624 hr_utility_trace ('Current payroll action id = '||ln_arch_pact_id);
625 hr_utility_trace ('Prev payroll action id = '||ln_prev_arch_pact_id);
626 hr_utility_trace ('Current assignment_id = '||ln_assignment_id);
627 hr_utility_trace ('Previous assignment_id = '||ln_prev_asg_id);
628
629 IF ln_arch_pact_id = ln_prev_arch_pact_id AND
630 ln_assignment_id = ln_prev_asg_id THEN
631 hr_utility_trace ('Action interlock not created.');
632 ELSE
633 IF lt_arch_act.EXISTS(ln_arch_act_id) THEN -- Bug 6004485
634 hr_utility_trace('Interlock for archiver action '||
635 ln_arch_act_id||' already exists.');
636 ELSE
637 hr_nonrun_asact.insint (ln_mag_asg_act_id,
638 ln_arch_act_id);
639 hr_utility_trace('Archiver asg action '||ln_arch_act_id||
640 ' locked by magtape asg action '||ln_mag_asg_act_id);
641 ln_prev_asg_id := ln_assignment_id;
642 ln_prev_arch_pact_id := ln_arch_pact_id;
643 lt_arch_act(ln_arch_act_id) := 0;
644 END IF;
645 hr_utility_trace (lt_arch_act.COUNT()||' interlocks exist for '||
646 'SUA asg action '||ln_mag_asg_act_id);
647 END IF;
648 END LOOP;
649
650 hr_utility_trace(ln_asg_count || ' archiver actions processed in chunk '||
651 p_chunk);
652
653 IF lb_range_person_on THEN
654 CLOSE c_arch_asg_range;
655 ELSE
656 CLOSE c_arch_asg;
657 END IF;
658
659 hr_utility_trace ('Leaving '||l_proc_name);
660 END ACTION_CREATION;
661
662 /****************************************************************************
663 Name : GENERATE_XML
664 Description : This procedure fetches archived data, converts it to XML
665 format and appends to pay_mag_tape.g_blob_value.
666 *****************************************************************************/
667 PROCEDURE GENERATE_XML AS
668
669 CURSOR get_emp_details (cp_assignment_action_id number) IS
670 SELECT paa_arch.payroll_action_id,
671 pai.action_context_id,
672 pai.action_information_id,
673 nvl(pai.action_information21, 'N') -- Do not report flag
674 FROM pay_action_information pai,
675 pay_assignment_actions paa_arch,
676 per_all_assignments_f paf,
677 pay_action_interlocks lck
678 WHERE pai.action_context_id = paa_arch.assignment_action_id
679 AND paf.assignment_id = paa_arch.assignment_id
680 AND pai.effective_date BETWEEN paf.effective_start_date AND
681 paf.effective_end_date
682 AND paa_arch.assignment_action_id = lck.locked_action_id
683 AND lck.locking_action_id = cp_assignment_action_id
684 AND pai.action_context_type = 'AAP'
685 AND pai.action_information_category = 'MX SS PERSON INFORMATION'
686 ORDER BY paa_arch.payroll_action_id DESC,
687 decode (paf.primary_flag, 'Y', 1, 2),
688 paf.assignment_id;
689
690 CURSOR get_emp_trans(cp_assignment_action_id number,cp_imp_date varchar2) IS
691 SELECT pai.action_information_id,
692 pai.action_information4, -- transaction type
693 pai.assignment_id,
694 pai.action_information1, -- person ID
695 pai.tax_unit_id,
696 pai.effective_date,
697 nvl(pai.action_information10, 'N'), -- Do not report flag
698 fnd_number.canonical_to_number(pai.action_information8), --IDW
699 pai.action_information2 -- transaction date
700 FROM pay_action_information pai,
701 pay_assignment_actions paa_arch,
702 per_all_assignments_f paf,
703 pay_action_interlocks lck
704 WHERE pai.action_context_id = paa_arch.assignment_action_id
705 AND paf.assignment_id = paa_arch.assignment_id
706 --Bug 7185703
707 AND fnd_date.canonical_to_date (pai.action_information2) >= fnd_date.canonical_to_date(cp_imp_date)
708 AND (fnd_date.canonical_to_date (pai.action_information2) BETWEEN
709 fnd_date.canonical_to_date (g_mag_start_date)
710 AND fnd_date.canonical_to_date (g_mag_end_date)
711 OR (pai.effective_date BETWEEN
712 fnd_date.canonical_to_date (g_mag_start_date)
713 AND fnd_date.canonical_to_date (g_mag_end_date)
714 AND fnd_date.canonical_to_date (pai.action_information2) <=
715 fnd_date.canonical_to_date (g_mag_end_date)))
716 AND fnd_date.canonical_to_date(pai.action_information2)
717 BETWEEN paf.effective_start_date
718 AND paf.effective_end_date
719 AND paa_arch.assignment_action_id = lck.locked_action_id
720 AND lck.locking_action_id = cp_assignment_action_id
721 AND pai.action_context_type = 'AAP'
722 AND pai.action_information_category = 'MX SS TRANSACTIONS'
723
724 ORDER BY fnd_date.canonical_to_date (pai.action_information2), -- tran dt
725 DECODE (pai.action_information4,
726 '08', 1,
727 '07', 2,
728 '02', 3),
729 pai.action_information_id,
730 paa_arch.payroll_action_id,
731 paf.person_id,
732 decode (paf.primary_flag, 'Y', 1, 2),
733 paf.assignment_id;
734
735 CURSOR csr_tran_exists (cp_mag_asact_id number,
736 cp_person_id varchar2,
737 cp_tran_type varchar2,
738 cp_tran_dt date) IS
739 SELECT fnd_date.canonical_to_date(pai.action_information2) -- tran date
740 FROM pay_assignment_actions paa_mag,
741 pay_payroll_actions ppa_mag,
742 pay_assignment_actions paa_mag_prev,
743 pay_payroll_actions ppa_mag_prev,
744 pay_action_information pai,
745 pay_action_interlocks lck
746 WHERE paa_mag.payroll_action_id = ppa_mag.payroll_action_id
747 AND paa_mag_prev.payroll_action_id = ppa_mag_prev.payroll_action_id
748 AND pay_mx_utility.get_legi_param_val ('GRE',
749 ppa_mag.payroll_action_id) =
750 pay_mx_utility.get_legi_param_val ('GRE',
751 ppa_mag_prev.payroll_action_id)
752 AND ppa_mag_prev.payroll_action_id < ppa_mag.payroll_action_id
753 AND paa_mag_prev.assignment_action_id = lck.locking_action_id
754 AND lck.locked_action_id = pai.action_context_id
755 AND paa_mag.assignment_action_id = cp_mag_asact_id
756 AND pai.action_information1 = cp_person_id -- person ID
757 AND pai.action_information4 = cp_tran_type -- transaction type
758 AND nvl(pai.action_information10, 'N') <> 'Y' -- do not report flag
759 AND ((fnd_date.canonical_to_date (pai.action_information2) >
760 cp_tran_dt) OR
761 (cp_tran_dt IS NULL))
762 AND pai.action_context_type = 'AAP'
763 AND pai.action_information_category = 'MX SS TRANSACTIONS'
764 AND ppa_mag_prev.action_type = 'X'
765 AND ppa_mag_prev.report_type = 'SUA_MAG'
766 AND ppa_mag_prev.report_qualifier = 'SUA_MAG'
767 AND ppa_mag_prev.report_category = 'RT'
768 AND ppa_mag_prev.action_status = 'C';
769
770 CURSOR csr_person (cp_asg_act_id number) IS
771 SELECT DISTINCT paf.person_id
772 FROM pay_assignment_actions paa,
773 per_assignments_f paf
774 WHERE paf.assignment_id = paa.assignment_id
775 AND paa.assignment_action_id = cp_asg_act_id;
776
777 CURSOR csr_asg_actions (cp_person_id number) IS
778 SELECT fnd_number.canonical_to_number(
779 pay_magtape_generic.get_parameter_value ('TRANSFER_ACT_ID')),
780 fnd_date.canonical_to_date(g_mag_end_date)
781 FROM DUAL
782 UNION
783 SELECT paa.assignment_action_id,
784 fnd_date.canonical_to_date(
785 pay_mx_utility.get_legi_param_val('END_DATE',
786 ppa.legislative_parameters))
787 FROM pay_payroll_actions ppa,
788 pay_assignment_actions paa,
789 per_assignments_f paf
790 WHERE ppa.payroll_action_id = paa.payroll_action_id
791 AND paa.assignment_id = paf.assignment_id
792 AND paf.person_id = cp_person_id
793 AND fnd_date.canonical_to_date(
794 pay_mx_utility.get_legi_param_val('END_DATE',
795 ppa.legislative_parameters))
796 BETWEEN paf.effective_start_date AND paf.effective_end_date
797 AND ppa.action_type = 'X'
798 AND ppa.report_type = 'SUA_MAG'
799 AND ppa.report_qualifier = 'SUA_MAG'
800 AND ppa.report_category = 'RT'
801 AND ppa.action_status = 'C'
802 /*AND cp_gre_id = fnd_number.canonical_to_number(
803 pay_mx_utility.get_legi_param_val('GRE',
804 ppa.legislative_parameters))
805 AND cp_curr_date > fnd_date.canonical_to_date(
806 pay_mx_utility.get_legi_param_val('END_DATE',
807 ppa.legislative_parameters))*/
808 AND fnd_number.canonical_to_number(g_mag_gre_id) =
809 fnd_number.canonical_to_number(
810 pay_mx_utility.get_legi_param_val('GRE',
811 ppa.legislative_parameters))
812 AND fnd_date.canonical_to_date(g_mag_end_date) >
813 fnd_date.canonical_to_date(
814 pay_mx_utility.get_legi_param_val('END_DATE',
815 ppa.legislative_parameters))
816 ORDER BY 2 DESC;
817
818 CURSOR csr_prev_idw (cp_person_id VARCHAR2) IS
819 SELECT fnd_number.canonical_to_number(pai.action_information8)
820 FROM pay_payroll_actions ppa_sua,
821 pay_assignment_actions paa_sua,
822 pay_assignment_actions paa_arch,
823 pay_action_interlocks lck,
824 pay_action_information pai
825 WHERE ppa_sua.payroll_action_id = paa_sua.payroll_action_id
826 AND paa_sua.assignment_action_id = lck.locking_action_id
827 AND lck.locked_action_id = paa_arch.assignment_action_id
828 AND paa_arch.assignment_action_id = pai.action_context_id
829 AND pai.action_information_category = 'MX SS TRANSACTIONS'
830 AND pai.action_information1 = cp_person_id
831 AND pai.action_information4 = '07'
832 AND ppa_sua.action_type = 'X'
833 AND ppa_sua.report_type = 'SUA_MAG'
834 AND ppa_sua.report_qualifier = 'SUA_MAG'
835 AND ppa_sua.report_category = 'RT'
836 AND ppa_sua.action_status = 'C'
837 AND fnd_number.canonical_to_number(g_mag_gre_id) =
838 fnd_number.canonical_to_number(
839 pay_mx_utility.get_legi_param_val('GRE',
840 ppa_sua.legislative_parameters))
841 AND fnd_date.canonical_to_date(g_mag_end_date) >
842 fnd_date.canonical_to_date(
843 pay_mx_utility.get_legi_param_val('END_DATE',
844 ppa_sua.legislative_parameters))
845 -- Bug 5998981
846 AND DECODE (pai.action_information_category,
847 'MX SS TRANSACTIONS',
848 fnd_date.canonical_to_date(pai.action_information2),
849 hr_general.start_of_time)
850 BETWEEN fnd_date.canonical_to_date(
851 pay_mx_utility.get_legi_param_val('START_DATE',
852 ppa_sua.legislative_parameters))
853 AND fnd_date.canonical_to_date(
854 pay_mx_utility.get_legi_param_val('END_DATE',
855 ppa_sua.legislative_parameters))
856 ORDER BY fnd_date.canonical_to_date (pai.action_information2) DESC;
857
858 CURSOR c_get_imp_date (cp_gre_id NUMBER) IS
859 SELECT org_information6
860 FROM hr_organization_information
861 WHERE org_information_context = 'MX_TAX_REGISTRATION'
862 AND organization_id = cp_gre_id;
863
864 CURSOR c_get_bus_grp_id IS
865 SELECT hou.business_group_id
866 FROM hr_organization_units hou
867 WHERE hou.organization_id = g_mag_gre_id;
868
869 l_proc_name varchar2(100);
870 lv_tran_type pay_action_information.action_information4%type;
871 lv_person_id pay_action_information.action_information1%type;
872 lv_tran_dt pay_action_information.action_information2%type;
873 ln_tax_unit_id number;
874 ld_effective_date date;
875 ld_tran_dt date;
876 ln_payroll_action_id number;
877 ln_assignment_action_id number;
878 ln_action_information_id number;
879 ln_prev_payroll_action_id number;
880 ln_assignment_id number;
881 ln_action_context_id number;
882 ln_idw number;
883 ln_prev_idw number;
884 ln_count number;
885 lv_show_curr_trans varchar2(1);
886 lv_per_do_not_report varchar2(1);
887 lv_tran_do_not_report varchar2(1);
888 l_xml BLOB;
889 lb_person_processed boolean;
890 lt_act_info_id pay_payroll_xml_extract_pkg.int_tab_type;
891 lt_act_info_id_exc_wd pay_payroll_xml_extract_pkg.int_tab_type;
892 lt_act_info_id_exc_trans pay_payroll_xml_extract_pkg.int_tab_type;
893 lt_tran per_mx_ss_affiliation.transactions;
894 ln_legal_er NUMBER;
895 ld_imp_date varchar2(30);
896 ld_event_strt_date VARCHAR2 (30);
897 ln_business_group NUMBER;
898
899 BEGIN
900 l_proc_name := g_proc_name || 'GENERATE_XML';
901 hr_utility_trace ('Entering '||l_proc_name);
902 lv_per_do_not_report := 'N';
903 ln_prev_payroll_action_id := -1;
904
905 ln_assignment_action_id := pay_magtape_generic.get_parameter_value
906 ('TRANSFER_ACT_ID');
907 hr_utility_trace ('Processing asg action '|| ln_assignment_action_id);
908 OPEN c_get_bus_grp_id;
909 FETCH c_get_bus_grp_id INTO ln_business_group;
910 CLOSE c_get_bus_grp_id;
911
912 ln_legal_er := hr_mx_utility.get_legal_employer(ln_business_group,
913 g_mag_gre_id);
914
915 hr_utility_trace ('Fetching start date from legal employer.'|| ln_legal_er);
916 OPEN c_get_imp_date (ln_legal_er);
917 FETCH c_get_imp_date INTO ld_imp_date;
918 CLOSE c_get_imp_date;
919 hr_utility_trace ('ld_imp_date '|| ld_imp_date);
920
921 SELECT fnd_date.date_to_canonical(MIN(creation_date))
922 INTO ld_event_strt_date
923 FROM pay_process_events ppe
924 WHERE EXISTS
925 (SELECT 1
926 FROM pay_event_updates peu
927 WHERE table_name IN ('PER_ALL_PEOPLE_F','PER_ALL_ASSIGNMENTS_F','PAY_ELEMENT_ENTRIES_F','PAY_ELEMENT_ENTRY_VALUES_F')
928 AND ppe.event_update_id = peu.event_update_id
929 );
930
931 IF fnd_date.canonical_to_date(ld_event_strt_date) >= fnd_date.canonical_to_date(NVL(ld_imp_date,ld_event_strt_date)) THEN
932 ld_imp_date := ld_event_strt_date;
933 END IF;
934
935 hr_utility_trace ('ld_event_strt_date '|| ld_event_strt_date);
936 hr_utility_trace ('ld_imp_date '|| ld_imp_date);
937
938 IF ld_imp_date IS NULL THEN
939 ld_imp_date := pay_mx_utility.get_default_imp_date;
940 END IF;
941 hr_utility_trace ('Actual Implementation Date is : '|| ld_imp_date);
942 OPEN csr_person(ln_assignment_action_id);
943 FETCH csr_person INTO lv_person_id;
944 CLOSE csr_person;
945
946 lb_person_processed := FALSE;
947 OPEN csr_asg_actions (fnd_number.canonical_to_number(lv_person_id));
948 LOOP
949 FETCH csr_asg_actions INTO ln_assignment_action_id,
950 ld_effective_date;
951
952 EXIT WHEN csr_asg_actions%NOTFOUND OR lb_person_processed;
953 hr_utility_trace ('Processing magtape asg action '||
954 ln_assignment_action_id);
955
956 OPEN get_emp_details (ln_assignment_action_id);
957 --LOOP
958 FETCH get_emp_details INTO ln_payroll_action_id,
959 ln_action_context_id,
960 ln_action_information_id,
961 lv_per_do_not_report;
962 IF get_emp_details%NOTFOUND AND NOT lb_person_processed THEN
963 hr_utility_trace('No archived worker information found for '||
964 'magtape asg action '||ln_assignment_action_id||
965 '. Going back in asg actions history.');
966 --END IF;
967 --EXIT WHEN get_emp_details%NOTFOUND;
968
969 ELSIF lv_per_do_not_report <> 'Y' THEN
970 hr_utility_trace('Archived worker information found '||
971 'for magtape asg action '||
972 ln_assignment_action_id);
973 hr_utility_trace('Current archiver payroll action id = '||
974 ln_payroll_action_id);
975 hr_utility_trace('Prev archiver payroll action id = '||
976 ln_prev_payroll_action_id);
977
978 IF ln_payroll_action_id <> ln_prev_payroll_action_id THEN
979 hr_utility_trace('Worker record processed');
980 lt_act_info_id(lt_act_info_id.count() + 1) :=
981 ln_action_information_id;
982 ln_prev_payroll_action_id := ln_payroll_action_id;
983 lb_person_processed := TRUE;
984 ELSE
985 hr_utility_trace('Worker record NOT processed');
986 END IF;
987 /*ELSE
988 hr_utility_trace('No archived person information found for '||
989 'asg action '||ln_assignment_action_id||
990 '. Looking for past asg actions now.');
991 EXIT;*/
992 END IF;
993 --END LOOP;
994 CLOSE get_emp_details;
995 END LOOP;
996 CLOSE csr_asg_actions;
997
998 ln_assignment_action_id := pay_magtape_generic.get_parameter_value
999 ('TRANSFER_ACT_ID');
1000 hr_utility_trace ('Fetching transactions for magtape asg action '||
1001 ln_assignment_action_id);
1002
1003 IF lv_per_do_not_report <> 'Y' THEN
1004 OPEN get_emp_trans (ln_assignment_action_id,ld_imp_date);
1005 LOOP
1006 FETCH get_emp_trans INTO ln_action_information_id,
1007 lv_tran_type,
1008 ln_assignment_id,
1009 lv_person_id,
1010 ln_tax_unit_id,
1011 ld_effective_date,
1012 lv_tran_do_not_report,
1013 ln_idw,
1014 lv_tran_dt;
1015 EXIT WHEN get_emp_trans%NOTFOUND;
1016 IF lv_tran_do_not_report <> 'Y' THEN
1017 hr_utility_trace ('Transaction type = '||lv_tran_type||'('||
1018 lv_tran_dt||')');
1019
1020 IF lv_tran_type IN ('02', '07', '08') THEN
1021 ln_count := lt_tran.COUNT();
1022 lt_tran (ln_count).act_info_id := ln_action_information_id;
1023 lt_tran (ln_count).tran_type := lv_tran_type;
1024 lt_tran (ln_count).tran_date := lv_tran_dt;
1025 lt_tran (ln_count).idw := ln_idw;
1026 ELSE
1027 lt_act_info_id(lt_act_info_id.count() + 1) :=
1028 ln_action_information_id;
1029 END IF;
1030 /*IF lv_tran_type NOT IN ('02', '08') THEN
1031
1032 IF lv_tran_type = '07' THEN
1033 /* A salary change transaction should not be reported if: -
1034 1. It is being reported for the first time in current GRE, or
1035 2. IDW amount hasn't changed since it was last reported by
1036 SUA.
1037 IF ln_prev_idw IS NULL THEN
1038 OPEN csr_prev_idw (lv_person_id);
1039 FETCH csr_prev_idw INTO ln_prev_idw;
1040 CLOSE csr_prev_idw;
1041 END IF;
1042
1043 hr_utility_trace('Previous IDW reported for person '||
1044 lv_person_id||' is :'||ln_prev_idw);
1045 IF ln_prev_idw IS NOT NULL THEN
1046 IF ln_prev_idw <> ln_idw THEN
1047 lt_act_info_id(lt_act_info_id.count() + 1) :=
1048 ln_action_information_id;
1049 ln_prev_idw := ln_idw;
1050 ELSE
1051 hr_utility_trace ('IDW has not changed. Skipping'||
1052 ' this transaction.');
1053 END IF;
1054 ELSE
1055 ln_prev_idw := -1;
1056 END IF;
1057 ELSE
1058 lt_act_info_id(lt_act_info_id.count() + 1) :=
1059 ln_action_information_id;
1060 END IF;
1061
1062 ELSE
1063 -- Filter out redundant hire/terminate transactions here.
1064 lv_show_curr_trans := 'Y';
1065 OPEN csr_tran_exists (ln_assignment_action_id,
1066 lv_person_id,
1067 lv_tran_type,
1068 NULL);
1069 FETCH csr_tran_exists INTO ld_tran_dt;
1070 CLOSE csr_tran_exists;
1071
1072 hr_utility_trace('Above transaction was reported on '||
1073 fnd_date.date_to_canonical(ld_tran_dt));
1074
1075 IF ld_tran_dt IS NOT NULL THEN
1076 ld_tran_dt := NULL;
1077 IF lv_tran_type = '08' THEN
1078 OPEN csr_tran_exists (ln_assignment_action_id,
1079 lv_person_id,
1080 '02',
1081 ld_tran_dt);
1082 ELSE
1083 OPEN csr_tran_exists (ln_assignment_action_id,
1084 lv_person_id,
1085 '08',
1086 ld_tran_dt);
1087 END IF;
1088
1089 FETCH csr_tran_exists INTO ld_tran_dt;
1090 CLOSE csr_tran_exists;
1091
1092 hr_utility_trace('Counter transaction of above '||
1093 'transaction was reported on '||
1094 fnd_date.date_to_canonical(ld_tran_dt));
1095 IF ld_tran_dt IS NULL THEN
1096 hr_utility_trace('Suppressing above transaction');
1097 lv_show_curr_trans := 'N';
1098 END IF;
1099 END IF;
1100
1101 IF lv_show_curr_trans = 'Y' THEN
1102 lt_act_info_id(lt_act_info_id.count() + 1) :=
1103 ln_action_information_id;
1104 END IF;
1105 END IF;
1106 ELSE
1107 lt_act_info_id_exc_trans (lt_act_info_id_exc_trans.count() + 1) :=
1108 ln_action_information_id;*/
1109 END IF;
1110 END LOOP;
1111 CLOSE get_emp_trans;
1112 per_mx_ss_affiliation.process_transactions (
1113 lv_person_id,
1114 fnd_number.canonical_to_number(g_mag_gre_id),
1115 fnd_date.canonical_to_date(g_mag_end_date),
1116 'SUA_MAG',
1117 'SUA_MAG',
1118 'RT',
1119 lt_tran);
1120 ln_count := lt_tran.FIRST();
1121 WHILE ln_count IS NOT NULL LOOP
1122 lt_act_info_id (lt_act_info_id.count() + 1) :=
1123 lt_tran (ln_count).act_info_id;
1124 ln_count := lt_tran.NEXT(ln_count);
1125 END LOOP;
1126 END IF;
1127
1128 -- Bug 4541979
1129 IF lt_act_info_id.count() = 0 AND
1130 lt_act_info_id_exc_wd.count() = 0 AND
1131 lt_act_info_id_exc_trans.count() = 0 THEN
1132 hr_utility_trace ('Nothing to write to BLOB for magtape asg action '||
1133 ln_assignment_action_id);
1134 ELSE
1135 pay_payroll_xml_extract_pkg.generate(lt_act_info_id,
1136 NULL,
1137 g_document_type,
1138 l_xml);
1139 write_to_magtape_lob (l_xml);
1140
1141 pay_payroll_xml_extract_pkg.generate(lt_act_info_id_exc_wd,
1142 'WD_EXCEPTION',
1143 g_document_type,
1144 l_xml);
1145 write_to_magtape_lob (l_xml);
1146
1147 pay_payroll_xml_extract_pkg.generate(lt_act_info_id_exc_trans,
1148 'TRANS_EXCEPTION',
1149 g_document_type,
1150 l_xml);
1151 write_to_magtape_lob (l_xml);
1152
1153 hr_utility_trace ('BLOB contents for magtape assignment action '||
1154 ln_assignment_action_id);
1155 print_blob (pay_mag_tape.g_blob_value);
1156 END IF;
1157
1158 hr_utility_trace ('Leaving '||l_proc_name);
1159 EXCEPTION
1160 WHEN OTHERS THEN
1161 hr_utility_trace (SQLERRM);
1162 RAISE;
1163 END GENERATE_XML;
1164
1165
1166 /****************************************************************************
1167 Name : GEN_XML_HEADER
1168 Description : This procedure generates XML header information and appends to
1169 pay_mag_tape.g_blob_value.
1170 *****************************************************************************/
1171 PROCEDURE GEN_XML_HEADER AS
1172 l_proc_name varchar2(100);
1173 lv_buf varchar2(2000);
1174 BEGIN
1175 l_proc_name := g_proc_name || 'GEN_XML_HEADER';
1176 hr_utility_trace ('Entering '||l_proc_name);
1177
1178 hr_utility_trace ('Root XML tag = '||
1179 pay_magtape_generic.get_parameter_value('ROOT_XML_TAG'));
1180
1181 lv_buf := pay_magtape_generic.get_parameter_value('ROOT_XML_TAG');
1182
1183 write_to_magtape_lob (lv_buf);
1184
1185 hr_utility_trace ('BLOB contents after appending header information');
1186 print_blob (pay_mag_tape.g_blob_value);
1187
1188 hr_utility_trace ('Leaving '||l_proc_name);
1189 END GEN_XML_HEADER;
1190
1191
1192 /****************************************************************************
1193 Name : GEN_XML_FOOTER
1194 Description : This procedure generates XML information for GRE and the final
1195 closing tag. Final result is appended to
1196 pay_mag_tape.g_blob_value.
1197 *****************************************************************************/
1198 PROCEDURE GEN_XML_FOOTER AS
1199
1200 -- Bug 4864237
1201 CURSOR get_arch_pact_id (cp_chunk NUMBER) IS
1202 SELECT DISTINCT paa_arch.payroll_action_id
1203 FROM pay_assignment_actions paa_arch,
1204 pay_assignment_actions paa_mag,
1205 pay_action_interlocks lck
1206 WHERE paa_arch.assignment_action_id = lck.locked_action_id
1207 AND lck.locking_action_id = paa_mag.assignment_action_id
1208 AND paa_mag.chunk_number >= cp_chunk
1209 AND paa_mag.payroll_action_id =
1210 pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID');
1211
1212 CURSOR get_action_info_id (cp_action_context_id number) IS
1213 SELECT pai.action_information_id
1214 FROM pay_action_information pai
1215 WHERE pai.action_context_id = cp_action_context_id
1216 AND pai.action_context_type = 'PA'
1217 AND pai.action_information_category = 'MX SS GRE INFORMATION';
1218
1219 lt_act_info_id pay_payroll_xml_extract_pkg.int_tab_type;
1220 ln_pact_id number;
1221 l_xml BLOB;
1222 l_proc_name varchar2(100);
1223 ln_chars number;
1224 ln_offset number;
1225 lv_buf varchar2(8000);
1226 lr_xml RAW (32767);
1227 ln_amt number;
1228 BEGIN
1229 l_proc_name := g_proc_name || 'GEN_XML_FOOTER';
1230 hr_utility_trace ('Entering '||l_proc_name);
1231 ln_chars := 2000;
1232 ln_offset := 1;
1233
1234 OPEN get_arch_pact_id (1);
1235 FETCH get_arch_pact_id INTO ln_pact_id;
1236 CLOSE get_arch_pact_id;
1237
1238 OPEN get_action_info_id (ln_pact_id);
1239 FETCH get_action_info_id INTO lt_act_info_id (lt_act_info_id.count()+1);
1240 CLOSE get_action_info_id;
1241
1242 -- Bug 4541979
1243 IF lt_act_info_id.count() = 0 THEN
1244 hr_utility_trace('GRE Information not found for magtape payroll action '
1245 || pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID'));
1246 ELSE
1247 pay_payroll_xml_extract_pkg.generate(lt_act_info_id,
1248 NULL,
1249 g_document_type,
1250 l_xml);
1251
1252 -- Reload global variables if they are unset between multiple threads.
1253 IF g_mag_gre_id IS NULL THEN
1254 get_pact_info (pay_magtape_generic.get_parameter_value
1255 ('PAYROLL_ACTION_ID'),
1256 g_mag_gre_id,
1257 g_mag_start_date,
1258 g_mag_end_date,
1259 g_mag_mode);
1260 END IF;
1261
1262 lv_buf := '<REPORT_PERIOD>' ||
1263 '<START_DATE>' || g_mag_start_date || '</START_DATE>' ||
1264 '<END_DATE>' || g_mag_end_date || '</END_DATE>' ||
1265 '</REPORT_PERIOD>';
1266
1267 lr_xml := utl_raw.cast_to_raw(lv_buf);
1268 ln_amt := utl_raw.length(lr_xml);
1269
1270 dbms_lob.writeAppend (l_xml,
1271 ln_amt,
1272 lr_xml);
1273
1274 write_to_magtape_lob (l_xml);
1275 END IF;
1276
1277 lv_buf := '</' ||
1278 SUBSTR(pay_magtape_generic.get_parameter_value('ROOT_XML_TAG'),
1279 2);
1280
1281 write_to_magtape_lob (lv_buf);
1282
1283 hr_utility_trace ('BLOB contents after appending footer information');
1284 print_blob (pay_mag_tape.g_blob_value);
1285
1286 hr_utility_trace ('Leaving '||l_proc_name);
1287 END GEN_XML_FOOTER;
1288
1289 /****************************************************************************
1290 Name : INIT
1291 Description : Initialization code.
1292 *****************************************************************************/
1293 PROCEDURE INIT
1294 (
1295 P_PAYROLL_ACTION_ID number
1296 ) AS
1297 l_proc_name VARCHAR2(100);
1298 BEGIN
1299 l_proc_name := g_proc_name || 'INIT';
1300 hr_utility_trace ('Entering '||l_proc_name);
1301
1302 get_pact_info (p_payroll_action_id,
1303 g_mag_gre_id,
1304 g_mag_start_date,
1305 g_mag_end_date,
1306 g_mag_mode);
1307
1308 hr_utility_trace ('Leaving '||l_proc_name);
1309 END INIT;
1310
1311 BEGIN
1312 --hr_utility.trace_on(null, 'MX_IDC');
1313 g_proc_name := 'PAY_MX_SUA_MAG.';
1314 g_debug := hr_utility.debug_enabled;
1315 g_document_type := 'MX_SUA_MAG';
1316 END PAY_MX_SUA_MAG;