[Home] [Help]
PACKAGE BODY: APPS.PER_MX_SS_AFFILIATION
Source
1 PACKAGE BODY PER_MX_SS_AFFILIATION AS
2 /* $Header: permxssaffiltion.pkb 120.4.12010000.5 2008/08/06 09:34:09 ubhat ship $ */
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 : PER_MX_SS_AFFILIATION
20 Package File Name : permxssaffiltion.pkb
21
22 Description : Used for Social Security Affiliation report.
23
24 Change List:
25 ------------
26
27 Name Date Version Bug Text
28 ------------- ----------- ------- ------- ------------------------------
29 sdahiya 28-Jan-2007 115.0 Created.
30 sdahiya 08-Mar-2007 115.1 5919339 Modified cusror get_emp_details
31 so that it converts IDW archived
32 in canonical format into numeric
33 format.
34 sdahiya 12-Apr-2007 115.2 5985804 Modified action_creation so that
35 duplicate interlocks are not
36 inserted.
37 sdahiya 13-Apr-2007 115.3 07 transactions should not be
38 reported if IDW amount hasn't
39 changed since it was reported
40 last.
41 sdahiya 16-Apr-2007 115.4 5996000 PL/SQL table should not be read
42 after dynamic truncation.
43 sdahiya 19-Apr-2007 115.5 5998981 Modified cursor csr_prev_idw to
44 conditionally convert canonical
45 date stamped on action information
46 DDF.
47 sdahiya 20-Apr-2007 115.6 6002963 Affiliation report should suppress
48 07 transactions which are archived
49 with rehire.
50 Procedure process_transactions
51 created to identify and eliminate
52 redundant transactions.
53 sdahiya 22-Apr-2007 115.7 Modified process_transactions to
54 eliminate multiple 08 and 02
55 transactions. The earliest 08
56 and latest 02 will be reported.
57 Added parameters to this procedure
58 so that it may be called from
59 SUA.
60 sdahiya 26-Apr-2007 115.8 6008833 Modified range_cursor so that it
61 does not discard persons when
62 the report is run after persons'
63 GRE transfer.
64 sdahiya 15-May-2007 115.9 Modified action_creation and
65 generate_xml so that past-dated
66 transactions are picked.
67 sdahiya 16-May-2007 115.10 Version uprev after establishing
68 dual maintenance.
69 sdahiya 18-May-2007 115.11 6060070 Changed multiple SQL statements
70 to conditionally convert
71 canonical date stamped on DDF into
72 date.
73 sdahiya 22-May-2007 115.12 6065124 Modified get_emp_details.
74 sivanara 27-jun-2008 115.13 7185703 Added logic to filter the transaction
75 before implementation date
76 sivanara 16-jul-2008 115.14 7258802 In the procedure process_transaction
77 modified cursor csr_prev_idw by adding
78 trunc on fnd_date.canonical_to_date to
79 consider the first run of the GRE.
80 ***************************************************************************/
81
82 --
83 -- Global Variables
84 --
85
86 TYPE num_tab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
87 g_proc_name varchar2(240);
88 g_debug boolean;
89 g_document_type varchar2(50);
90 g_trans_gre_id number;
91 g_business_group number;
92 g_start_date varchar2(25);
93 g_end_date varchar2(25);
94 g_gre_tab num_tab;
95
96
97 /****************************************************************************
98 Name : HR_UTILITY_TRACE
99 Description : This procedure prints debug messages.
100 *****************************************************************************/
101 PROCEDURE HR_UTILITY_TRACE
102 (
103 P_TRC_DATA varchar2
104 ) AS
105 BEGIN
106 IF g_debug THEN
107 hr_utility.trace(p_trc_data);
108 END IF;
109 END HR_UTILITY_TRACE;
110
111
112 /****************************************************************************
113 Name : PRINT_BLOB
114 Description : This procedure prints contents of BLOB passed as parameter.
115 *****************************************************************************/
116
117 PROCEDURE PRINT_BLOB(p_blob BLOB) IS
118 BEGIN
119 IF g_debug THEN
120 pay_ac_utility.print_lob(p_blob);
121 END IF;
122 END PRINT_BLOB;
123
124
125 /****************************************************************************
126 Name : WRITE_TO_MAGTAPE_LOB
127 Description : This procedure appends passed BLOB parameter to
128 pay_mag_tape.g_blob_value
129 *****************************************************************************/
130
131 PROCEDURE WRITE_TO_MAGTAPE_LOB(p_blob BLOB) IS
132 BEGIN
133 IF dbms_lob.getLength (p_blob) IS NOT NULL THEN
134 pay_core_files.write_to_magtape_lob (p_blob);
135 END IF;
136 END WRITE_TO_MAGTAPE_LOB;
137
138
139 /****************************************************************************
140 Name : WRITE_TO_MAGTAPE_LOB
141 Description : This procedure appends passed varchar2 parameter to
142 pay_mag_tape.g_blob_value
143 *****************************************************************************/
144
145 PROCEDURE WRITE_TO_MAGTAPE_LOB(p_data varchar2) IS
146 BEGIN
147 pay_core_files.write_to_magtape_lob (p_data);
148 END WRITE_TO_MAGTAPE_LOB;
149
150
151 /****************************************************************************
152 Name : GET_START_DATE
153 Description : This procedure fetches start date of reporting period.
154 *****************************************************************************/
155 FUNCTION GET_START_DATE
156 (
157 P_TRANS_GRE number
158 ) RETURN VARCHAR2 AS
159 CURSOR csr_get_date_dispmag IS
160 SELECT fnd_date.date_to_canonical (effective_date + 1/(24 * 60 * 60))
161 FROM pay_payroll_actions
162 WHERE report_type = 'SS_AFFILIATION'
163 AND report_qualifier IN ('HIRES', 'SEPARATIONS', 'SALARY')
164 AND pay_mx_utility.get_legi_param_val('TRANS_GRE',
165 legislative_parameters,
166 ' ') = TO_CHAR(p_trans_gre)
167 ORDER BY payroll_action_id DESC;
168
169
170 CURSOR csr_get_date_affl IS
171 SELECT fnd_date.date_to_canonical(
172 fnd_date.canonical_to_date(
173 pay_mx_utility.get_legi_param_val('END_DATE',
174 legislative_parameters)) +
175 1/(24 * 60 * 60))
176 FROM pay_payroll_actions
177 WHERE report_type = 'MX_SS_AFFL'
178 AND report_qualifier = 'MX_SS_AFFL'
179 AND pay_mx_utility.get_legi_param_val('TRANS_GRE',
180 legislative_parameters) =
181 TO_CHAR(p_trans_gre)
182 ORDER BY payroll_action_id DESC;
183
184 CURSOR c_get_imp_date (cp_organization_id NUMBER) IS
185 SELECT org_information6
186 FROM hr_organization_information
187 WHERE org_information_context = 'MX_TAX_REGISTRATION'
188 AND organization_id = cp_organization_id;
189
190 CURSOR c_get_bus_grp_id IS
191 SELECT hou.business_group_id
192 FROM hr_organization_units hou
193 WHERE hou.organization_id = p_trans_gre;
194
195 l_proc_name varchar2(100);
196 l_start_date varchar2(30);
197 ln_bus_grp_id number;
198 ln_legal_er number;
199 BEGIN
200 l_proc_name := g_proc_name || 'GET_START_DATE';
201 hr_utility_trace ('Entering '||l_proc_name);
202 hr_utility_trace ('p_trans_gre = '||p_trans_gre);
203
204 OPEN csr_get_date_affl;
205 hr_utility_trace ('Fetching start date from last affiliation '||
206 'report run.');
207 FETCH csr_get_date_affl INTO l_start_date;
208 CLOSE csr_get_date_affl;
209
210 IF l_start_date IS NULL THEN
211 hr_utility_trace ('Fetching start date from last DISPMAG run.');
212 OPEN csr_get_date_dispmag;
213 FETCH csr_get_date_dispmag INTO l_start_date;
214 CLOSE csr_get_date_dispmag;
215
216 IF l_start_date IS NULL THEN
217 OPEN c_get_bus_grp_id;
218 FETCH c_get_bus_grp_id INTO ln_bus_grp_id;
219 CLOSE c_get_bus_grp_id;
220 ln_legal_er := hr_mx_utility.get_legal_employer(ln_bus_grp_id,
221 p_trans_gre);
222 hr_utility_trace ('Fetching start date from legal employer.');
223 OPEN c_get_imp_date (ln_legal_er);
224 FETCH c_get_imp_date INTO l_start_date;
225 CLOSE c_get_imp_date;
226
227 IF l_start_date IS NULL THEN
228 l_start_date := pay_mx_utility.get_default_imp_date;
229 END IF;
230 END IF;
231 END IF;
232
233 hr_utility_trace ('l_start_date = ' || l_start_date);
234 hr_utility_trace ('Leaving '||l_proc_name);
235
236 RETURN (l_start_date);
237 END GET_START_DATE;
238
239 /****************************************************************************
240 Name : GET_PACT_INFO
241 Description : This procedure fetches payroll action level information.
242 *****************************************************************************/
243 PROCEDURE GET_PACT_INFO
244 (
245 P_PAYROLL_ACTION_ID number,
246 P_BUSINESS_GROUP OUT NOCOPY number,
247 P_TRANS_GRE_ID OUT NOCOPY number,
248 P_START_DATE OUT NOCOPY varchar2,
249 P_END_DATE OUT NOCOPY varchar2
250 ) IS
251 CURSOR csr_get_pact_info IS
252 SELECT pay_mx_utility.get_legi_param_val('TRANS_GRE',
253 ppa.legislative_parameters),
254 pay_mx_utility.get_legi_param_val('START_DATE',
255 ppa.legislative_parameters),
256 pay_mx_utility.get_legi_param_val('END_DATE',
257 ppa.legislative_parameters),
258 business_group_id
259 FROM pay_payroll_actions ppa
260 WHERE ppa.payroll_action_id = p_payroll_action_id;
261
262 CURSOR csr_gre IS
263 SELECT organization_id
264 FROM hr_organization_information
265 WHERE org_information_context = 'MX_SOC_SEC_DETAILS'
266 AND org_information3 = 'N'
267 AND org_information6 = g_trans_gre_id;
268
269 l_proc_name varchar2(100);
270 ln_gre_id number;
271 BEGIN
272 l_proc_name := g_proc_name || 'GET_PACT_INFO';
273 hr_utility_trace ('Entering '||l_proc_name);
274
275 OPEN csr_get_pact_info;
276 FETCH csr_get_pact_info INTO p_trans_gre_id,
277 p_start_date,
278 p_end_date,
279 p_business_group;
280 CLOSE csr_get_pact_info;
281
282 g_gre_tab.DELETE();
283 g_gre_tab(g_trans_gre_id) := g_trans_gre_id;
284 OPEN csr_gre;
285 LOOP
286 FETCH csr_gre INTO ln_gre_id;
287 EXIT WHEN csr_gre%NOTFOUND;
288 g_gre_tab(ln_gre_id) := ln_gre_id;
289 END LOOP;
290 CLOSE csr_gre;
291
292 hr_utility_trace ('Leaving '||l_proc_name);
293 END GET_PACT_INFO;
294
295
296 /************************************************************
297 Name : DERIVE_GRE_FROM_LOC_SCL
298 Purpose : This function derives the gre from the parmeters
299 location, BG and soft-coded keyflex.
300 ************************************************************/
301 FUNCTION DERIVE_GRE_FROM_LOC_SCL(
302 P_LOCATION_ID NUMBER,
303 P_BUSINESS_GROUP_ID NUMBER,
304 P_SOFT_CODING_KEYFLEX_ID NUMBER,
305 P_EFFECTIVE_DATE DATE)
306 RETURN NUMBER AS
307
308 ln_gre_id NUMBER;
309 l_is_ambiguous BOOLEAN;
310 l_missing_gre BOOLEAN;
311 BEGIN
312 IF p_soft_coding_keyflex_id IS NOT NULL THEN
313 ln_gre_id := hr_mx_utility.get_gre_from_scl(p_soft_coding_keyflex_id);
314 END IF;
315
316 IF ln_gre_id IS NULL THEN
317 ln_gre_id := hr_mx_utility.get_gre_from_location(
318 p_location_id,
319 p_business_group_id,
320 p_effective_date,
321 l_is_ambiguous,
322 l_missing_gre );
323 IF ln_gre_id IS NULL THEN
324 IF l_is_ambiguous THEN
325 ln_gre_id := -1;
326 END IF;
327
328 IF l_missing_gre THEN
329 ln_gre_id := -2;
330 END IF;
331 END IF;
332 END IF;
333
334 RETURN (ln_gre_id);
335
336 END DERIVE_GRE_FROM_LOC_SCL;
337
338
339 /****************************************************************************
340 Name : RANGE_CURSOR
341 Description : This procedure prepares range of persons to be processed.
342 *****************************************************************************/
343 PROCEDURE RANGE_CURSOR
344 (
345 P_PAYROLL_ACTION_ID number,
346 P_SQLSTR OUT NOCOPY varchar2
347 ) AS
348
349 l_proc_name varchar2(100);
350
351 BEGIN
352 l_proc_name := g_proc_name || 'RANGE_CURSOR';
353
354 hr_utility_trace ('Entering '||l_proc_name);
355 hr_utility_trace ('P_PAYROLL_ACTION_ID = '|| p_payroll_action_id);
356
357 get_pact_info (p_payroll_action_id,
358 g_business_group,
359 g_trans_gre_id,
360 g_start_date,
361 g_end_date);
362
363
364 -- Bug 6008833
365 p_sqlstr :=
366 'SELECT DISTINCT person_id
367 FROM per_assignments_f
368 WHERE business_group_id = '||g_business_group||'
369 /*AND fnd_date.canonical_to_date('''||g_end_date
370 ||''') BETWEEN effective_start_date AND effective_end_date*/
371 AND per_mx_ss_affiliation.derive_gre_from_loc_scl (location_id,
372 business_group_id,
373 soft_coding_keyflex_id,
374 fnd_date.canonical_to_date('''||
375 g_end_date||''')) IN
376 (SELECT organization_id
377 FROM hr_organization_information
378 WHERE org_information_context = ''MX_SOC_SEC_DETAILS''
379 AND (org_information3 = ''N''
380 AND org_information6 = '|| g_trans_gre_id ||'
381 OR organization_id = '||g_trans_gre_id||'))
382 AND :p_payroll_action_id > 0';
383
384 hr_utility_trace ('Range cursor query : ' || p_sqlstr);
385 hr_utility_trace ('Leaving '||l_proc_name);
386
387 END RANGE_CURSOR;
388
389
390 /****************************************************************************
391 Name : ACTION_CREATION
392 Description : This procedure creates assignment actions.
393 *****************************************************************************/
394 PROCEDURE ACTION_CREATION
395 (
396 P_PAYROLL_ACTION_ID number,
397 P_START_PERSON_ID number,
398 P_END_PERSON_ID number,
399 P_CHUNK number
400 ) AS
401
402 CURSOR c_affl_person IS
403 SELECT DISTINCT paf.person_id,
404 paf.assignment_id,
405 pai.action_context_id,
406 paf.primary_flag,
407 pai.tax_unit_id
408 FROM per_assignments_f paf,
409 pay_action_information pai
410 WHERE pai.action_information_category = 'MX SS TRANSACTIONS'
411 AND paf.business_group_id = g_business_group
412 AND paf.person_id BETWEEN p_start_person_id AND p_end_person_id
413 AND paf.person_id = pai.action_information1
414 AND pai.action_information4 IN ('02', '07', '08')
415 AND NVL(pai.action_information10, 'N') <> 'Y'
416 -- Bug 6060070
417 AND (DECODE(pai.action_information_category, 'MX SS TRANSACTIONS',
418 fnd_date.canonical_to_date (pai.action_information2),
419 hr_general.start_of_time) BETWEEN
420 fnd_date.canonical_to_date (g_start_date)
421 AND fnd_date.canonical_to_date (g_end_date)
422 OR (pai.effective_date BETWEEN
423 fnd_date.canonical_to_date (g_start_date)
424 AND fnd_date.canonical_to_date (g_end_date)
425 /*NOT EXISTS (SELECT 'X'
426 FROM pay_payroll_actions ppa_affl,
427 pay_assignment_actions paa_affl,
428 pay_action_interlocks lck
429 WHERE lck.locked_action_id = pai.action_context_id
430 AND lck.locking_action_id = paa_affl.assignment_action_id
431 AND paa_affl.payroll_action_id = ppa_affl.payroll_action_id
432 AND ppa_affl.report_type = 'MX_SS_AFFL'
433 AND ppa_affl.report_qualifier = 'MX_SS_AFFL'
434 AND ppa_affl.report_category = 'RT'
435 AND ppa_affl.action_status = 'C')*/
436 -- Bug 6060070
437 AND DECODE(pai.action_information_category, 'MX SS TRANSACTIONS',
438 fnd_date.canonical_to_date (pai.action_information2),
439 hr_general.start_of_time) <=
440 fnd_date.canonical_to_date (g_end_date)
441 )
442 )
443 /*AND fnd_date.canonical_to_date (g_end_date) BETWEEN
444 paf.effective_start_date
445 AND paf.effective_end_date*/
446 ORDER BY paf.person_id,
447 pai.action_context_id,
448 decode (paf.primary_flag, 'Y', 1, 2),
449 paf.assignment_id;
450
451 CURSOR c_affl_person_range IS
452 SELECT DISTINCT paf.person_id,
453 paf.assignment_id,
454 pai.action_context_id,
455 paf.primary_flag,
456 pai.tax_unit_id
457 FROM per_assignments_f paf,
458 pay_action_information pai,
459 pay_population_ranges ppr
460 WHERE pai.action_information_category = 'MX SS TRANSACTIONS'
461 AND paf.business_group_id = g_business_group
462 AND ppr.payroll_action_id = p_payroll_action_id
463 AND ppr.chunk_number = p_chunk
464 AND paf.person_id = ppr.person_id
465 AND paf.person_id = pai.action_information1
466 AND pai.action_information4 IN ('02', '07', '08')
467 AND NVL(pai.action_information10, 'N') <> 'Y'
468 -- Bug 6060070
469 AND (DECODE(pai.action_information_category, 'MX SS TRANSACTIONS',
470 fnd_date.canonical_to_date (pai.action_information2),
471 hr_general.start_of_time) BETWEEN
472 fnd_date.canonical_to_date (g_start_date)
473 AND fnd_date.canonical_to_date (g_end_date)
474 OR (pai.effective_date BETWEEN
475 fnd_date.canonical_to_date (g_start_date)
476 AND fnd_date.canonical_to_date (g_end_date)
477 /*NOT EXISTS (SELECT 'X'
478 FROM pay_payroll_actions ppa_affl,
479 pay_assignment_actions paa_affl,
480 pay_action_interlocks lck
481 WHERE lck.locked_action_id = pai.action_context_id
482 AND lck.locking_action_id = paa_affl.assignment_action_id
483 AND paa_affl.payroll_action_id = ppa_affl.payroll_action_id
484 AND ppa_affl.report_type = 'MX_SS_AFFL'
485 AND ppa_affl.report_qualifier = 'MX_SS_AFFL'
486 AND ppa_affl.report_category = 'RT'
487 AND ppa_affl.action_status = 'C')*/
488 -- Bug 6060070
489 AND DECODE(pai.action_information_category, 'MX SS TRANSACTIONS',
490 fnd_date.canonical_to_date (pai.action_information2),
491 hr_general.start_of_time) <=
492 fnd_date.canonical_to_date (g_end_date)
493 )
494 )
495 /*AND fnd_date.canonical_to_date (g_end_date) BETWEEN
496 paf.effective_start_date
497 AND paf.effective_end_date*/
498 ORDER BY paf.person_id,
499 pai.action_context_id,
500 decode (paf.primary_flag, 'Y', 1, 2),
501 paf.assignment_id;
502
503 l_proc_name varchar2(100);
504 lb_range_person_on boolean;
505 ln_person_id number;
506 ln_prev_person_id number;
507 ln_prev_arch_asg_act number;
508 ln_arch_asg_act number;
509 ln_asg_id number;
510 ln_affl_asg_act number;
511 ln_tax_unit_id number;
512 lv_primary_flag per_assignments_f.primary_flag%type;
513
514 BEGIN
515 l_proc_name := g_proc_name || 'ACTION_CREATION';
516 hr_utility_trace ('Entering '||l_proc_name);
517 hr_utility_trace ('Parameters ....');
518 hr_utility_trace ('P_PAYROLL_ACTION_ID = '|| P_PAYROLL_ACTION_ID);
519 hr_utility_trace ('P_START_PERSON_ID = '|| P_START_PERSON_ID);
520 hr_utility_trace ('P_END_PERSON_ID = '|| P_END_PERSON_ID);
521 hr_utility_trace ('P_CHUNK = '|| P_CHUNK);
522
523 IF g_business_group IS NULL THEN
524 get_pact_info (p_payroll_action_id,
525 g_business_group,
526 g_trans_gre_id,
527 g_start_date,
528 g_end_date);
529 END IF;
530
531 ln_prev_person_id := -1;
532 ln_prev_arch_asg_act := -1;
533
534 lb_range_person_on := pay_ac_utility.range_person_on(
535 p_report_type => 'MX_SS_AFFL',
536 p_report_format => 'MX_SS_AFFL',
537 p_report_qualifier => 'MX_SS_AFFL',
538 p_report_category => 'RT');
539
540 IF lb_range_person_on THEN
541 hr_utility_trace ('Person ranges are ON');
542 OPEN c_affl_person_range;
543 ELSE
544 hr_utility_trace ('Person ranges are OFF');
545 OPEN c_affl_person;
546 END IF;
547
548 LOOP
549 IF lb_range_person_on THEN
550 FETCH c_affl_person_range INTO ln_person_id,
551 ln_asg_id,
552 ln_arch_asg_act,
553 lv_primary_flag,
554 ln_tax_unit_id;
555 EXIT WHEN c_affl_person_range%NOTFOUND;
556 ELSE
557 FETCH c_affl_person INTO ln_person_id,
558 ln_asg_id,
559 ln_arch_asg_act,
560 lv_primary_flag,
561 ln_tax_unit_id;
562 EXIT WHEN c_affl_person%NOTFOUND;
563 END IF;
564
565 IF g_gre_tab.EXISTS(ln_tax_unit_id) THEN
566 hr_utility_trace ('-------------');
567 hr_utility_trace('Current person = '||ln_person_id);
568 hr_utility_trace('Previous person = '||ln_prev_person_id);
569
570 IF (ln_person_id <> ln_prev_person_id) THEN
571 SELECT pay_assignment_actions_s.nextval
572 INTO ln_affl_asg_act
573 FROM dual;
574
575 hr_utility_trace('Creating affiliation report assignment action '||
576 ln_affl_asg_act);
577 hr_nonrun_asact.insact(ln_affl_asg_act,
578 ln_asg_id,
579 p_payroll_action_id,
580 p_chunk,
581 g_trans_gre_id,
582 null,
583 'U',
584 null);
585 ln_prev_person_id := ln_person_id;
586 ELSE
587 hr_utility_trace('Affiliation assignment action not created');
588 END IF;
589
590
591 -- Bug 5985804
592 IF (ln_prev_arch_asg_act <> ln_arch_asg_act) THEN
593 hr_nonrun_asact.insint (ln_affl_asg_act,
594 ln_arch_asg_act);
595 hr_utility_trace('SS archiver asg action '||ln_arch_asg_act||
596 ' locked by affiliation report asg action '||ln_affl_asg_act);
597 ln_prev_arch_asg_act := ln_arch_asg_act;
598 ELSE
599 hr_utility_trace ('SS archiver asg action '|| ln_arch_asg_act ||
600 ' already locked by affiliation asg action '|| ln_affl_asg_act);
601 END IF;
602 END IF;
603 END LOOP;
604
605 IF lb_range_person_on THEN
606 CLOSE c_affl_person_range;
607 ELSE
608 CLOSE c_affl_person;
609 END IF;
610
611 hr_utility_trace ('Leaving '||l_proc_name);
612 EXCEPTION
613 WHEN OTHERS THEN
614 hr_utility_trace (SQLERRM);
615 RAISE;
616 END ACTION_CREATION;
617
618
619 /****************************************************************************
620 Name : INIT
621 Description : Initialization code.
622 *****************************************************************************/
623 PROCEDURE INIT
624 (
625 P_PAYROLL_ACTION_ID number
626 ) AS
627 l_proc_name VARCHAR2(100);
628 BEGIN
629 l_proc_name := g_proc_name || 'INIT';
630 hr_utility_trace ('Entering '||l_proc_name);
631
632 get_pact_info (p_payroll_action_id,
633 g_business_group,
634 g_trans_gre_id,
635 g_start_date,
636 g_end_date);
637
638 hr_utility_trace ('Leaving '||l_proc_name);
639 END INIT;
640
641
642 /****************************************************************************
643 Name : PROCESS_TRANSACTIONS
644 Description : This procedures runs through transactions to eliminate
645 redundant ones as explained below: -
646 08 - Hire transactions are always reported unless followed
647 by a termination transaction (02) within the reporting
648 period.
649 07 - Salary modification transaction will be reported only
650 if there has been a change in IDW amount since the
651 previous salary modification. Salary modification
652 transactions archived with hire/re-hire will be
653 suppressed.
654 02 - Termination transactions are always reported unless
655 preceeded by a hire transaction within the reporting
656 period.
657 *****************************************************************************/
658 PROCEDURE PROCESS_TRANSACTIONS
659 (
660 P_PERSON_ID NUMBER,
661 P_GRE_ID NUMBER,
662 P_END_DATE DATE,
663 P_REPORT_TYPE VARCHAR2,
664 P_REPORT_QUALIFIER VARCHAR2,
665 P_REPORT_CATEGORY VARCHAR2,
666 P_TRANSACTIONS IN OUT NOCOPY transactions
667 ) AS
668
669 CURSOR csr_prev_idw(cp_gre_token VARCHAR2) IS
670 SELECT fnd_number.canonical_to_number(pai.action_information8)
671 FROM pay_payroll_actions ppa_mag,
672 pay_assignment_actions paa_mag,
673 --pay_assignment_actions paa_arch,
674 pay_action_interlocks lck,
675 pay_action_information pai
676 WHERE ppa_mag.payroll_action_id = paa_mag.payroll_action_id
677 AND paa_mag.assignment_action_id = lck.locking_action_id
678 /*AND lck.locked_action_id = paa_arch.assignment_action_id
679 AND paa_arch.assignment_action_id = pai.action_context_id*/
680 AND lck.locked_action_id = pai.action_context_id
681 AND pai.action_information_category = 'MX SS TRANSACTIONS'
682 AND pai.action_information1 = p_person_id
683 AND pai.action_information4 IN ('07','08')
684 AND ppa_mag.action_type = 'X'
685 AND ppa_mag.report_type = p_report_type
686 AND ppa_mag.report_qualifier = p_report_qualifier
687 AND ppa_mag.report_category = p_report_category
688 AND ppa_mag.action_status = 'C'
689 AND p_gre_id = fnd_number.canonical_to_number(
690 pay_mx_utility.get_legi_param_val(cp_gre_token,
691 ppa_mag.legislative_parameters))
692 AND p_end_date > fnd_date.canonical_to_date(
693 pay_mx_utility.get_legi_param_val('END_DATE',
694 ppa_mag.legislative_parameters))
695 -- Bug 5998981
696 AND DECODE (pai.action_information_category,
697 'MX SS TRANSACTIONS',
698 TRUNC(fnd_date.canonical_to_date(pai.action_information2)),
699 hr_general.start_of_time)
700 BETWEEN TRUNC(fnd_date.canonical_to_date(
701 pay_mx_utility.get_legi_param_val('START_DATE',
702 ppa_mag.legislative_parameters)))
703 AND TRUNC (fnd_date.canonical_to_date(
704 pay_mx_utility.get_legi_param_val('END_DATE',
705 ppa_mag.legislative_parameters)))
706 ORDER BY fnd_date.canonical_to_date (pai.action_information2) DESC;
707
708 l_proc_name VARCHAR2(100);
709 lv_gre_token VARCHAR2(20);
710 ln_cntr NUMBER;
711 ln_prev_idw NUMBER;
712 ln_hire_idx NUMBER;
713 ln_term_idx NUMBER;
714 lv_hire_date pay_action_information.action_information2%TYPE;
715 BEGIN
716 l_proc_name := g_proc_name || 'PROCESS_TRANSACTIONS';
717 hr_utility_trace ('Entering '||l_proc_name);
718
719 ln_prev_idw := -1;
720 ln_hire_idx := -1;
721 ln_term_idx := -1;
722 lv_hire_date:= 'NULL';
723 ln_cntr := p_transactions.FIRST();
724
725 IF p_report_type = 'MX_SS_AFFL' AND
726 p_report_qualifier = 'MX_SS_AFFL' AND
727 p_report_category = 'RT' THEN
728 lv_gre_token := 'TRANS_GRE';
729 ELSIF p_report_type = 'SUA_MAG' AND
730 p_report_qualifier = 'SUA_MAG' AND
731 p_report_category = 'RT' THEN
732 lv_gre_token := 'GRE';
733 END IF;
734
735 WHILE ln_cntr IS NOT NULL LOOP
736 hr_utility_trace ('Transaction = '||
737 p_transactions(ln_cntr).tran_type||' ('||
738 p_transactions(ln_cntr).tran_date||')');
739 IF p_transactions(ln_cntr).tran_type = '08' THEN
740 IF ln_hire_idx <> -1 THEN
741 -- Eliminate current hire transaction if one 08 is already in.
742 p_transactions.DELETE(ln_cntr);
743 hr_utility_trace ('One hire transaction already exists. '||
744 'Above hire transaction will not be reported.');
745 ELSE
746 lv_hire_date := p_transactions(ln_cntr).tran_date;
747 ln_hire_idx := ln_cntr;
748 END IF;
749 ELSIF p_transactions(ln_cntr).tran_type = '07' THEN
750 IF p_transactions(ln_cntr).tran_date = lv_hire_date THEN
751 p_transactions.DELETE(ln_cntr);
752 lv_hire_date := 'NULL';
753 hr_utility_trace('This 07 transaction will be suppressed as '||
754 'it was archived upon hire.');
755 ELSE
756 IF ln_prev_idw = -1 THEN
757 OPEN csr_prev_idw (lv_gre_token);
758 FETCH csr_prev_idw INTO ln_prev_idw;
759 CLOSE csr_prev_idw;
760 END IF;
761 hr_utility_trace('Previous IDW amount = '||ln_prev_idw);
762 hr_utility_trace('Current IDW amount = '||
763 p_transactions(ln_cntr).idw);
764 IF ln_prev_idw = p_transactions(ln_cntr).idw THEN
765 p_transactions.DELETE(ln_cntr);
766 hr_utility_trace('No change in IDW. Transaction '||
767 'suppressed.');
768 ELSE
769 ln_prev_idw := p_transactions(ln_cntr).idw;
770 END IF;
771 END IF;
772 ELSIF p_transactions(ln_cntr).tran_type = '02' THEN
773 -- Look ahead to see if there are any terminations in future
774 ln_term_idx := p_transactions.NEXT(ln_cntr);
775 WHILE ln_term_idx IS NOT NULL LOOP
776 IF p_transactions(ln_term_idx).tran_type = '02' THEN
777 p_transactions.DELETE(ln_cntr);
778 hr_utility_trace('A termination transaction exists in '||
779 'future. Above termination will not be reported.');
780 END IF;
781 ln_term_idx := p_transactions.NEXT(ln_term_idx);
782 END LOOP;
783
784 IF ln_hire_idx <> -1 AND p_transactions.EXISTS(ln_cntr) THEN
785 LOOP
786 p_transactions.DELETE(ln_hire_idx);
787 ln_hire_idx := p_transactions.NEXT(ln_hire_idx);
788 p_transactions.DELETE(ln_hire_idx);
789 EXIT WHEN ln_hire_idx = ln_cntr;
790 END LOOP;
791 ln_hire_idx := -1;
792 hr_utility_trace ('Person '||p_person_id||
793 ' hired and later terminated within the reporting period.');
794 END IF;
795 END IF;
796
797 ln_cntr := p_transactions.NEXT(ln_cntr);
798 END LOOP;
799
800 hr_utility_trace ('------------------------');
801 ln_cntr := p_transactions.FIRST();
802 hr_utility_trace ('After transaction filtering, eligible ones are: -');
803 WHILE ln_cntr IS NOT NULL LOOP
804 hr_utility_trace ('Transaction = '||
805 p_transactions(ln_cntr).tran_type||' ('||
806 p_transactions(ln_cntr).tran_date||')');
807 ln_cntr := p_transactions.NEXT(ln_cntr);
808 END LOOP;
809 hr_utility_trace ('------------------------');
810
811 hr_utility_trace ('Leaving '||l_proc_name);
812 END PROCESS_TRANSACTIONS;
813
814
815 /****************************************************************************
816 Name : GENERATE_XML
817 Description : This procedure fetches archived data, converts it to XML
818 format and appends to pay_mag_tape.g_blob_value.
819 *****************************************************************************/
820 PROCEDURE GENERATE_XML AS
821
822 CURSOR get_emp_details (cp_person_id number) IS
823 SELECT pai.action_information_id,
824 pai.action_information1, -- Person ID
825 pai.action_information7, -- Employee name
826 pai.action_information8, -- Worker Type
827 pai.action_information9, -- RWW Indicator
828 pai.action_information10, -- Hire Date
829 -- Bug 5919339
830 fnd_number.canonical_to_number(pai.action_information11), -- IDW
831 pai.action_information18 -- Salary Type
832 -- Bug 6065124
833 FROM pay_action_information pai /*,
834 pay_assignment_actions paa_arch,
835 pay_action_interlocks lck*/
836 WHERE pai.action_information_category = 'MX SS PERSON INFORMATION'
837 /*and lck.locking_action_id = cp_assignment_action_id
838 AND lck.locked_action_id = pai.action_context_id*/
839 AND pai.action_context_type = 'AAP'
840 AND pai.action_information1 = cp_person_id
841 AND nvl(pai.action_information21, 'N') <> 'Y' -- Do not report flag
842 AND pai.effective_date <= fnd_date.canonical_to_date (g_end_date)
843 ORDER BY pai.effective_date DESC/*,
844 decode (paf.primary_flag, 'Y', 1, 2),
845 paf.assignment_id*/;
846
847 CURSOR csr_asg_actions (cp_person_id number) IS
848 /*SELECT fnd_number.canonical_to_number(
849 pay_magtape_generic.get_parameter_value ('TRANSFER_ACT_ID')),
850 fnd_date.canonical_to_date (g_end_date)
851 FROM dual
852 UNION
853 SELECT paa.assignment_action_id,
854 fnd_date.canonical_to_date(
855 pay_mx_utility.get_legi_param_val('END_DATE',
856 ppa.legislative_parameters))
857 FROM pay_payroll_actions ppa,
858 pay_assignment_actions paa,
859 pay_action_information pai,
860 pay_action_interlocks lck
861 WHERE paa.payroll_action_id = ppa.payroll_action_id
862 AND paa.assignment_action_id = lck.locking_action_id
863 AND pai.action_context_id = lck.locked_action_id
864 AND pai.action_information_category = 'MX SS PERSON INFORMATION'
865 AND pai.action_information1 = cp_person_id
866 AND ppa.report_type = 'MX_SS_AFFL'
867 AND ppa.report_qualifier = 'MX_SS_AFFL'
868 AND ppa.report_category = 'RT'
869 AND ppa.action_status = 'C'
870 AND pay_mx_utility.get_legi_param_val('TRANS_GRE',
871 ppa.legislative_parameters) =
872 g_trans_gre_id
873 AND fnd_date.canonical_to_date(
874 pay_mx_utility.get_legi_param_val('END_DATE',
875 ppa.legislative_parameters)) <
876 fnd_date.canonical_to_date (g_end_date)
877 ORDER BY 2 DESC;*/
878
879
880 SELECT pai.action_context_id,
881 pai.effective_date
882 FROM pay_action_information pai
883 WHERE pai.action_information_category = 'MX SS PERSON INFORMATION'
884 AND pai.action_information1 = cp_person_id
885 -- Bug 6060070
886 AND pai.effective_date <= fnd_date.canonical_to_date (g_end_date)
887 /*AND (DECODE(pai.action_information_category, 'MX SS TRANSACTIONS',
888 fnd_date.canonical_to_date (pai.action_information2),
889 hr_general.start_of_time) BETWEEN
890 fnd_date.canonical_to_date (g_start_date)
891 AND fnd_date.canonical_to_date (g_end_date)
892 OR (pai.effective_date BETWEEN
893 fnd_date.canonical_to_date (g_start_date)
894 AND fnd_date.canonical_to_date (g_end_date)
895 -- Bug 6060070
896 AND DECODE(pai.action_information_category, 'MX SS TRANSACTIONS',
897 fnd_date.canonical_to_date (pai.action_information2),
898 hr_general.start_of_time) <=
899 fnd_date.canonical_to_date (g_end_date)
900 )
901 )
902 /*AND fnd_date.canonical_to_date (g_end_date) BETWEEN
903 paf.effective_start_date
904 AND paf.effective_end_date*/
905 ORDER BY pai.effective_date DESC;
906
907
908
909 CURSOR csr_person (cp_assignment_action_id number) IS
910 SELECT paf.person_id
911 FROM per_assignments_f paf,
912 pay_assignment_actions paa
913 WHERE paa.assignment_action_id = cp_assignment_action_id
914 AND paa.assignment_id = paf.assignment_id;
915
916 CURSOR csr_transactions (cp_assignment_action_id number,cp_imp_date varchar2) IS
917 SELECT pai.action_information_id,
918 pai.action_information1, -- Person ID
919 pai.action_information2, -- Date of Transaction
920 pai.action_information3, -- Employee SSN
921 pai.action_information4, -- Type of Transaction
922 pai.action_information5, -- Employer SS ID
923 fnd_number.canonical_to_number (pai.action_information8), -- IDW
924 pai.action_information9 -- Leaving reason
925 FROM pay_action_information pai,
926 pay_action_interlocks lck
927 WHERE lck.locking_action_id = cp_assignment_action_id
928 AND lck.locked_action_id = pai.action_context_id
929 AND pai.action_information_category = 'MX SS TRANSACTIONS'
930 AND pai.action_information4 IN ('02', '07', '08')
931 AND NVL(pai.action_information10, 'N') <> 'Y'
932 --Bug 7185703
933 AND DECODE(pai.action_information_category, 'MX SS TRANSACTIONS',
934 fnd_date.canonical_to_date (pai.action_information2),hr_general.start_of_time
935 ) >= fnd_date.canonical_to_date(cp_imp_date)
936 -- Bug 6060070
937 AND (DECODE(pai.action_information_category, 'MX SS TRANSACTIONS',
938 fnd_date.canonical_to_date (pai.action_information2),
939 hr_general.start_of_time) BETWEEN
940 fnd_date.canonical_to_date (g_start_date)
941 AND fnd_date.canonical_to_date (g_end_date)
942 OR (pai.effective_date BETWEEN
943 fnd_date.canonical_to_date (g_start_date)
944 AND fnd_date.canonical_to_date (g_end_date)
945 /*NOT EXISTS (SELECT 'X'
946 FROM pay_payroll_actions ppa_affl,
947 pay_assignment_actions paa_affl,
948 pay_action_interlocks lck
949 WHERE lck.locked_action_id = pai.action_context_id
950 AND lck.locking_action_id = paa_affl.assignment_action_id
951 AND paa_affl.payroll_action_id = ppa_affl.payroll_action_id
952 AND ppa_affl.report_type = 'MX_SS_AFFL'
953 AND ppa_affl.report_qualifier = 'MX_SS_AFFL'
954 AND ppa_affl.report_category = 'RT'
955 AND ppa_affl.action_status = 'C')*/
956 -- Bug 6060070
957 AND DECODE(pai.action_information_category, 'MX SS TRANSACTIONS',
958 fnd_date.canonical_to_date (pai.action_information2),
959 hr_general.start_of_time) <=
960 fnd_date.canonical_to_date (g_end_date)
961 )
962 )
963 ORDER BY fnd_date.canonical_to_date (pai.action_information2),
964 DECODE (pai.action_information4,
965 '08', 1,
966 '07', 2,
967 '02', 3);
968
969 CURSOR c_get_imp_date (cp_gre_id NUMBER) IS
970 SELECT org_information6
971 FROM hr_organization_information
972 WHERE org_information_context = 'MX_TAX_REGISTRATION'
973 AND organization_id = cp_gre_id;
974
975
976 l_proc_name varchar2(100);
977 l_xml BLOB;
978 ln_assignment_action_id number;
979 ln_per_asg_action number;
980 ln_act_info_id number;
981 ln_count number;
982 ln_person_id number;
983 lv_do_not_report varchar2(1);
984 ld_process_date date;
985 lt_tran transactions;
986 lt_act_info_id pay_payroll_xml_extract_pkg.int_tab_type;
987 lt_act_info_id_per_exc pay_payroll_xml_extract_pkg.int_tab_type;
988 lt_act_info_id_tran_exc pay_payroll_xml_extract_pkg.int_tab_type;
989 lv_person_id pay_action_information.action_information1%type;
990 lv_name pay_action_information.action_information7%type;
991 lv_worker_type pay_action_information.action_information8%type;
992 lv_rww pay_action_information.action_information9%type;
993 lv_hire_date pay_action_information.action_information10%type;
994 lv_salary_type pay_action_information.action_information18%type;
995 ln_idw number;
996 lv_tran_dt pay_action_information.action_information2%type;
997 lv_ee_ssn pay_action_information.action_information3%type;
998 lv_tran_type pay_action_information.action_information4%type;
999 lv_er_ssid pay_action_information.action_information5%type;
1000 lv_leaving_reason pay_action_information.action_information9%type;
1001 ln_legal_er NUMBER;
1002 ld_imp_date varchar2(30);
1003 ld_event_strt_date VARCHAR2 (30);
1004
1005 BEGIN
1006 l_proc_name := g_proc_name || 'GENERATE_XML';
1007 hr_utility_trace ('Entering '||l_proc_name);
1008
1009 ln_assignment_action_id := pay_magtape_generic.get_parameter_value
1010 ('TRANSFER_ACT_ID');
1011
1012 hr_utility_trace ('Processing asg action '|| ln_assignment_action_id);
1013 hr_utility_trace ('g_start_date '|| g_start_date);
1014 hr_utility_trace ('g_end_date '|| g_end_date);
1015
1016 ln_legal_er := hr_mx_utility.get_legal_employer(g_business_group,
1017 g_trans_gre_id);
1018
1019 hr_utility_trace ('Fetching start date from legal employer.'|| ln_legal_er);
1020 OPEN c_get_imp_date (ln_legal_er);
1021 FETCH c_get_imp_date INTO ld_imp_date;
1022 CLOSE c_get_imp_date;
1023 hr_utility_trace ('ld_imp_date '|| ld_imp_date);
1024
1025 SELECT fnd_date.date_to_canonical(MIN(creation_date))
1026 INTO ld_event_strt_date
1027 FROM pay_process_events ppe
1028 WHERE EXISTS
1029 (SELECT 1
1030 FROM pay_event_updates peu
1031 WHERE table_name IN ('PER_ALL_PEOPLE_F','PER_ALL_ASSIGNMENTS_F','PAY_ELEMENT_ENTRIES_F','PAY_ELEMENT_ENTRY_VALUES_F')
1032 AND ppe.event_update_id = peu.event_update_id
1033 );
1034
1035 IF fnd_date.canonical_to_date(ld_event_strt_date) >= fnd_date.canonical_to_date(NVL(ld_imp_date,ld_event_strt_date)) THEN
1036 ld_imp_date := ld_event_strt_date;
1037 END IF;
1038
1039 hr_utility_trace ('ld_event_strt_date '|| ld_event_strt_date);
1040 hr_utility_trace ('ld_imp_date '|| ld_imp_date);
1041
1042 IF ld_imp_date IS NULL THEN
1043 ld_imp_date := pay_mx_utility.get_default_imp_date;
1044 END IF;
1045 hr_utility_trace ('Actual Implementation Date is : '|| ld_imp_date);
1046
1047 OPEN csr_person (ln_assignment_action_id);
1048 FETCH csr_person INTO ln_person_id;
1049 CLOSE csr_person;
1050
1051 /*OPEN csr_asg_actions (ln_person_id);
1052 ln_person_id := NULL;
1053 LOOP
1054 FETCH csr_asg_actions INTO ln_per_asg_action,
1055 ld_process_date;
1056 EXIT WHEN csr_asg_actions%NOTFOUND OR ln_person_id IS NOT NULL;
1057 hr_utility_trace(
1058 'Attempting to fetch person info locked by affiliation asg action '||
1059 ln_per_asg_action);*/
1060 OPEN get_emp_details (ln_person_id);
1061 --OPEN get_emp_details (ln_per_asg_action);
1062 FETCH get_emp_details INTO ln_act_info_id,
1063 ln_person_id,
1064 lv_name,
1065 lv_worker_type,
1066 lv_rww,
1067 lv_hire_date,
1068 ln_idw,
1069 lv_salary_type;
1070 CLOSE get_emp_details;
1071 /*END LOOP;
1072 CLOSE csr_asg_actions;*/
1073
1074 IF (lv_name IS NULL OR
1075 lv_worker_type IS NULL OR
1076 lv_rww IS NULL OR
1077 lv_hire_date IS NULL OR
1078 NVL(ln_idw, 0) <= 0 OR
1079 lv_salary_type IS NULL) AND
1080 ln_person_id IS NOT NULL THEN
1081 hr_utility_trace ('Person ID '|| ln_person_id ||' identified as '||
1082 'exception record. No transactions will be picked for this person.');
1083 lt_act_info_id_per_exc (lt_act_info_id_per_exc.COUNT()) :=
1084 ln_act_info_id;
1085 ELSIF ln_person_id IS NOT NULL THEN
1086 lt_act_info_id (lt_act_info_id.count()) := ln_act_info_id;
1087
1088 OPEN csr_transactions (ln_assignment_action_id,ld_imp_date);
1089 LOOP
1090 FETCH csr_transactions INTO ln_act_info_id,
1091 lv_person_id,
1092 lv_tran_dt,
1093 lv_ee_ssn,
1094 lv_tran_type,
1095 lv_er_ssid,
1096 ln_idw,
1097 lv_leaving_reason;
1098 EXIT WHEN csr_transactions%NOTFOUND;
1099
1100 hr_utility_trace ('Transaction type = '||lv_tran_type||'('||
1101 lv_tran_dt||')');
1102 IF lv_tran_dt IS NULL OR
1103 lv_ee_ssn IS NULL OR
1104 lv_er_ssid IS NULL OR
1105 (lv_tran_type = '02' AND
1106 lv_leaving_reason IS NULL) THEN
1107 hr_utility_trace ('Action Information ID '||ln_act_info_id||
1108 ' identified as exception transaction.');
1109 lt_act_info_id_tran_exc (lt_act_info_id_tran_exc.COUNT()) :=
1110 ln_act_info_id;
1111 ELSE
1112 ln_count := lt_tran.COUNT();
1113 lt_tran (ln_count).act_info_id := ln_act_info_id;
1114 lt_tran (ln_count).tran_type := lv_tran_type;
1115 lt_tran (ln_count).tran_date := lv_tran_dt;
1116 lt_tran (ln_count).idw := ln_idw;
1117
1118 END IF;
1119 END LOOP;
1120 CLOSE csr_transactions;
1121 process_transactions (lv_person_id,
1122 fnd_number.canonical_to_number(g_trans_gre_id),
1123 fnd_date.canonical_to_date(g_end_date),
1124 'MX_SS_AFFL',
1125 'MX_SS_AFFL',
1126 'RT',
1127 lt_tran);
1128 ln_count := lt_tran.FIRST();
1129 WHILE ln_count IS NOT NULL LOOP
1130 lt_act_info_id (lt_act_info_id.count()) :=
1131 lt_tran (ln_count).act_info_id;
1132 ln_count := lt_tran.NEXT(ln_count);
1133 END LOOP;
1134 END IF;
1135
1136 IF lt_act_info_id.count() = 0 AND
1137 lt_act_info_id_tran_exc.count() = 0 AND
1138 lt_act_info_id_per_exc.count() = 0 THEN
1139 hr_utility_trace ('Nothing to write to XML BLOB.');
1140 ELSE
1141 pay_payroll_xml_extract_pkg.generate(lt_act_info_id,
1142 NULL,
1143 g_document_type,
1144 l_xml);
1145 write_to_magtape_lob (l_xml);
1146
1147 hr_utility_trace ('Attempting to generate XML for transaction exceptions.');
1148 pay_payroll_xml_extract_pkg.generate(lt_act_info_id_tran_exc,
1149 'TRANS_EXCEPTION',
1150 g_document_type,
1151 l_xml);
1152 write_to_magtape_lob (l_xml);
1153
1154 hr_utility_trace ('Attempting to generate XML for person exceptions.');
1155 pay_payroll_xml_extract_pkg.generate(lt_act_info_id_per_exc,
1156 'PERSON_EXCEPTION',
1157 g_document_type,
1158 l_xml);
1159 write_to_magtape_lob (l_xml);
1160 END IF;
1161
1162 hr_utility_trace ('Leaving '||l_proc_name);
1163 EXCEPTION
1164 WHEN OTHERS THEN
1165 hr_utility_trace (SQLERRM);
1166 RAISE;
1167 END GENERATE_XML;
1168
1169
1170 /****************************************************************************
1171 Name : GEN_XML_HEADER
1172 Description : This procedure generates XML header information to XML BLOB
1173 *****************************************************************************/
1174 PROCEDURE GEN_XML_HEADER AS
1175 l_proc_name varchar2(100);
1176 lv_buf varchar2(2000);
1177 BEGIN
1178 l_proc_name := g_proc_name || 'GEN_XML_HEADER';
1179 hr_utility_trace ('Entering '||l_proc_name);
1180
1181 hr_utility_trace ('Root XML tag = '||
1182 pay_magtape_generic.get_parameter_value('ROOT_XML_TAG'));
1183
1184 lv_buf := pay_magtape_generic.get_parameter_value('ROOT_XML_TAG');
1185
1186 write_to_magtape_lob (lv_buf);
1187
1188 hr_utility_trace ('BLOB contents after appending header information');
1189 print_blob (pay_mag_tape.g_blob_value);
1190
1191 hr_utility_trace ('Leaving '||l_proc_name);
1192 END GEN_XML_HEADER;
1193
1194
1195 /****************************************************************************
1196 Name : GEN_XML_FOOTER
1197 Description : This procedure generates XML footer.
1198 *****************************************************************************/
1199 PROCEDURE GEN_XML_FOOTER AS
1200
1201 CURSOR csr_employer IS
1202 SELECT pai.action_information_id
1203 FROM pay_action_information pai,
1204 pay_action_interlocks lck,
1205 pay_assignment_actions paa_affl,
1206 pay_assignment_actions paa_arch
1207 WHERE paa_affl.payroll_action_id =
1208 pay_magtape_generic.get_parameter_value ('PAYROLL_ACTION_ID')
1209 AND lck.locking_action_id = paa_affl.assignment_action_id
1210 AND paa_arch.assignment_action_id = lck.locked_action_id
1211 AND pai.action_context_id = paa_arch.payroll_action_id
1212 --AND pai.action_information2 = pai.action_information4
1213 AND pai.action_information_category = 'MX SS GRE INFORMATION'
1214 AND pai.action_context_type = 'PA'
1215 ORDER BY pai.action_information_id DESC;
1216
1217
1218 l_proc_name varchar2(100);
1219 lv_buf varchar2(8000);
1220 l_xml BLOB;
1221 lt_act_info_id pay_payroll_xml_extract_pkg.int_tab_type;
1222 BEGIN
1223 l_proc_name := g_proc_name || 'GEN_XML_FOOTER';
1224 hr_utility_trace ('Entering '||l_proc_name);
1225
1226 OPEN csr_employer;
1227 FETCH csr_employer INTO lt_act_info_id(lt_act_info_id.count());
1228 CLOSE csr_employer;
1229
1230 pay_payroll_xml_extract_pkg.generate(lt_act_info_id,
1231 NULL,
1232 g_document_type,
1233 l_xml);
1234 write_to_magtape_lob (l_xml);
1235
1236 lv_buf := lv_buf || '</' ||
1237 SUBSTR(pay_magtape_generic.get_parameter_value('ROOT_XML_TAG'),
1238 2);
1239
1240 write_to_magtape_lob (lv_buf);
1241
1242 hr_utility_trace ('BLOB contents after appending footer information');
1243 print_blob (pay_mag_tape.g_blob_value);
1244
1245 hr_utility_trace ('Leaving '||l_proc_name);
1246 END GEN_XML_FOOTER;
1247
1248 BEGIN
1249 --hr_utility.trace_on(null, 'MX_IDC');
1250 g_proc_name := 'PER_MX_SS_AFFILIATION.';
1251 g_debug := hr_utility.debug_enabled;
1252 g_document_type := 'MX_SS_AFFL';
1253 END PER_MX_SS_AFFILIATION;