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