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