1 package body pay_au_payment_summary_magtape as
2 /* $Header: pyaupsm.pkb 120.4.12010000.4 2009/12/16 14:17:37 dduvvuri ship $*/
3 -------------------------------------------------------------------------+
4
5 -- g_debug boolean := hr_utility.debug_enabled; --Bug3132178
6 g_debug boolean := TRUE; --Bug3132178
7
8
9 procedure range_code
10 (p_payroll_action_id in pay_payroll_actions.payroll_action_id%type,
11 p_sql out NOCOPY varchar2) is
12 begin
13 -- hr_utility.trace_on(null, 'magtape');
14 hr_utility.set_location('Start of range_code',1);
15 /*Bug2920725 Corrected base tables to support security model*/
16
17 p_sql := ' select distinct p.person_id' ||
18 ' from per_people_f p,' ||
19 ' pay_payroll_actions pa ' ||
20 ' where pa.payroll_action_id = :payroll_action_id' ||
21 ' and p.business_group_id = pa.business_group_id' ||
22 ' order by p.person_id';
23
24 IF g_debug THEN
25 hr_utility.set_location('End of range_code',2);
26 END IF;
27 end range_code;
28
29 /*
30 Bug 8501365 - Added Function range_person_on
31 --------------------------------------------------------------------
32 Name : range_person_on
33 Type : Function
34 Access: Private
35 Description: Checks if RANGE_PERSON_ID is enabled for
36 Archive process.
37 --------------------------------------------------------------------
38 Bug 9113084 - Check if Range Person is enabled for Data File and its Validation
39 */
40
41 FUNCTION range_person_on
42 RETURN BOOLEAN
43 IS
44
45 CURSOR csr_action_parameter is
46 select parameter_value
47 from pay_action_parameters
48 where parameter_name = 'RANGE_PERSON_ID';
49
50 CURSOR csr_range_format_param is
51 select par.parameter_value
52 from pay_report_format_parameters par,
53 pay_report_format_mappings_f map
54 where map.report_format_mapping_id = par.report_format_mapping_id
55 and map.report_type in ('AU_PS_DATA_FILE','AU_PS_DATA_FILE_VAL')
56 and map.report_format in ('AU_PS_DATA_FILE','AU_PS_DATA_FILE_VAL')
57 and map.report_qualifier = 'AU'
58 and par.parameter_name = 'RANGE_PERSON_ID'; -- Bug fix 5567246
59
60 l_return boolean;
61 l_action_param_val varchar2(30);
62 l_report_param_val varchar2(30);
63
64 BEGIN
65
66 g_debug := hr_utility.debug_enabled;
67
68 BEGIN
69
70 open csr_action_parameter;
71 fetch csr_action_parameter into l_action_param_val;
72 close csr_action_parameter;
73
74 open csr_range_format_param;
75 fetch csr_range_format_param into l_report_param_val;
76 close csr_range_format_param;
77
78 EXCEPTION WHEN NO_DATA_FOUND THEN
79 l_return := FALSE;
80 END;
81 --
82 IF l_action_param_val = 'Y' AND l_report_param_val = 'Y' THEN
83 l_return := TRUE;
84 IF g_debug THEN
85 hr_utility.set_location('Range Person = True',1);
86 END IF;
87 ELSE
88 l_return := FALSE;
89 END IF;
90 --
91 RETURN l_return;
92 --
93 END range_person_on;
94
95 -----------------------------------------------------------------------+
96 -- This procedure is used to further restrict the Assignment Action
97 -- Creation. It calls the procedure that actually inserts the Assignment
98 -- Actions.
99 -----------------------------------------------------------------------+
100
101 procedure assignment_action_code
102 (p_payroll_action_id in pay_payroll_actions.payroll_action_id%type,
103 p_start_person_id in per_all_people_f.person_id%type,
104 p_end_person_id in per_all_people_f.person_id%type,
105 p_chunk in number) is
106
107 v_next_action_id pay_assignment_actions.assignment_action_id%type;
108 v_run_action_id pay_assignment_actions.assignment_action_id%type;
109 p_assignment_id pay_assignment_Actions.assignment_id%type;
110 ps_report_id pay_assignment_actions.assignment_action_id%type;
111 l_payment_summary_type varchar2(5) := 'O'; /* bug 6630375 */
112
113 ------start of Bug3132178-----------------------------------------------------
114 l_testing_flag varchar2(5):='N';
115 l_archive_payroll_action pay_payroll_actions.payroll_action_id%type;
116
117
118 CURSOR get_parameters
119 IS
120 SELECT pay_core_utils.get_parameter('IS_TESTING',ppa.legislative_parameters),
121 pay_core_utils.get_parameter('ARCHIVE_PAYROLL_ACTION',ppa.legislative_parameters),
122 pay_core_utils.get_parameter('PAYMENT_SUMMARY_TYPE',ppa.legislative_parameters) /* bug 6630375 */
123 FROM pay_payroll_actions ppa
124 WHERE ppa.payroll_Action_id = p_payroll_action_id;
125
126
127 CURSOR process_assignments_val
128 (c_payroll_action_id in pay_payroll_actions.payroll_action_id%type,
129 c_start_person_id in per_all_people_f.person_id%type,
130 c_end_person_id in per_all_people_f.person_id%type)
131 IS
132 SELECT DISTINCT 'ASSIGNMENT_ACTION_ID=C',
133 paa.assignment_action_id ps_report_id,
134 paa.assignment_id
135 FROM pay_assignment_actions paa,
136 per_assignments_f a
137 WHERE paa.payroll_action_id = c_payroll_action_id
138 AND paa.action_status = 'C'
139 AND a.assignment_id = paa.assignment_id
140 AND pay_au_payment_summary.get_archive_value('X_REPORTING_FLAG',paa.assignment_action_id)='YES'
141 AND pay_au_payment_summary.get_archive_value('X_CURR_TERM_0_BAL_FLAG',paa.assignment_action_id)='NO' /* Added for bug 5257622 */
142 AND a.person_id BETWEEN c_start_person_id AND c_end_person_id ;
143
144 /* 9113084 - Added range cursor for above cursor process_assignments_val */
145 /* 9113084 - Cursor to fetch assignments for Data File Validation when RANGE_PERSON_ID is enabled */
146 CURSOR rg_process_assignments_val
147 (c_payroll_action_id in pay_payroll_actions.payroll_action_id%type,
148 c_chunk IN NUMBER)
149 IS
150 SELECT DISTINCT 'ASSIGNMENT_ACTION_ID=C',
151 paa.assignment_action_id ps_report_id,
152 paa.assignment_id
153 FROM pay_assignment_actions paa,
154 per_assignments_f a,
155 pay_population_ranges ppr
156 WHERE paa.payroll_action_id = c_payroll_action_id
157 AND ppr.payroll_action_id = p_payroll_action_id
158 AND ppr.chunk_number = c_chunk
159 AND paa.action_status = 'C'
160 AND a.assignment_id = paa.assignment_id
161 AND pay_au_payment_summary.get_archive_value('X_REPORTING_FLAG',paa.assignment_action_id)='YES'
162 AND pay_au_payment_summary.get_archive_value('X_CURR_TERM_0_BAL_FLAG',paa.assignment_action_id)='NO'
163 AND a.person_id = ppr.person_id ;
164
165 --------End of Bug3132178--------------------------------------------------
166
167
168 /*Bug2920725 Corrected base tables to support security model*/
169 CURSOR process_assignments
170 (c_payroll_action_id in pay_payroll_actions.payroll_action_id%type,
171 c_start_person_id in per_all_people_f.person_id%type,
172 c_end_person_id in per_all_people_f.person_id%type) is
173 SELECT /*+ INDEX (apac PAY_ASSIGNMENT_ACTIONS_N50)
174 INDEX (ppac PAY_ASSIGNMENT_ACTIONS_PK)
175 INDEX(mpa PAY_PAYROLL_ACTIONS_PK)
176 INDEX(ppai PAY_ACTION_INTERLOCKS_FK2)
177 INDEX (p PER_PEOPLE_F_PK)
178 INDEX(a PER_ASSIGNMENTS_F_PK) */
179 DISTINCT 'ASSIGNMENT_ACTION_ID=C',
180 ppac.assignment_action_id ps_report_id,
181 ppac.assignment_id
182 FROM pay_payroll_actions mpa,
183 per_people_f p,
184 per_assignments_f a,
185 pay_payroll_actions apa,
186 pay_assignment_actions apac,
187 pay_payroll_actions ppa,
188 pay_assignment_actions ppac,
189 pay_action_interlocks ppai
190 WHERE mpa.payroll_action_id =c_payroll_action_id
191 AND p.person_id = a.person_id
192 AND p.person_id BETWEEN c_start_person_id AND c_end_person_id
193 AND p.business_group_id = mpa.business_group_id
194 AND apa.payroll_action_id = apac.payroll_action_id
195 AND ppa.payroll_action_id = ppac.payroll_action_id
196 AND apac.assignment_action_id = ppai.locked_action_id
197 AND ppac.assignment_action_id = ppai.locking_action_id
198 and apa.action_status = 'C'
199 AND ppa.action_status = 'C'
200 AND apa.payroll_action_id = pay_core_utils.get_parameter('ARCHIVE_ID',ppa.legislative_parameters)
201 AND a.assignment_id = apac.assignment_id
202 AND a.assignment_id = ppac.assignment_id
203 AND apa.report_type ='AU_PAYMENT_SUMMARY'
204 AND ppa.report_type = 'AU_PAYMENT_SUMMARY_REPORT'
205 AND pay_core_utils.get_parameter('BUSINESS_GROUP_ID',apa.legislative_parameters)=
206 pay_core_utils.get_parameter('BUSINESS_GROUP_ID',mpa.legislative_parameters)
207 AND pay_core_utils.get_parameter('REGISTERED_EMPLOYER',apa.legislative_parameters)=
208 pay_core_utils.get_parameter('REGISTERED_EMPLOYER',mpa.legislative_parameters)
209 AND pay_core_utils.get_parameter('FINANCIAL_YEAR',apa.legislative_parameters)=
210 pay_core_utils.get_parameter('FINANCIAL_YEAR',mpa.legislative_parameters)
211 AND NOT EXISTS /* 5471093 */
212 (SELECT /*+ ORDERED */ locked_action_id
213 FROM pay_action_interlocks pail,
214 pay_assignment_actions paa1,
215 pay_payroll_actions paas
216 WHERE paas.action_type='X'
217 and paas.action_status='C'
218 AND paas.report_type='AU_PS_DATA_FILE'
219 AND paa1.payroll_action_id = paas.payroll_action_id
220 AND pail.locking_action_id = paa1.assignment_action_id
221 AND pail.locked_action_id = ppac.assignment_action_id);
222
223
224 /*
225 Bug 8501365 - Added Cursor for Range Person
226 - Uses person_id in pay_population_ranges
227 --------------------------------------------------------------------+
228 -- Cursor : range_process_assignments
229 -- Description : Fetches assignments For Data File
230 -- Used when RANGE_PERSON_ID feature is enabled
231 --------------------------------------------------------------------+
232 */
233
234 CURSOR range_process_assignments(c_payroll_action_id IN pay_payroll_actions.payroll_action_id%TYPE,
235 c_chunk IN NUMBER)
236 IS
237 SELECT /*+ INDEX (mpa PAY_PAYROLL_ACTIONS_PK)
238 INDEX (ppr PAY_POPULATION_RANGES_N4)
239 INDEX (p PER_PEOPLE_F_PK)
240 INDEX (apa PAY_PAYROLL_ACTIONS_N52)
241 INDEX (a PER_ASSIGNMENTS_F_N12)
242 INDEX (apac PAY_ASSIGNMENT_ACTIONS_N51)
243 INDEX (ppai PAY_ACTION_INTERLOCKS_FK2)
244 INDEX (ppac PAY_ASSIGNMENT_ACTIONS_PK)
245 */
246 DISTINCT 'ASSIGNMENT_ACTION_ID=C',
247 ppac.assignment_action_id ps_report_id,
248 ppac.assignment_id
249 FROM pay_payroll_actions mpa,
250 pay_population_ranges ppr,
251 per_people_f p,
252 per_assignments_f a,
253 pay_payroll_actions apa,
254 pay_assignment_actions apac,
255 pay_payroll_actions ppa,
256 pay_assignment_actions ppac,
257 pay_action_interlocks ppai
258 WHERE mpa.payroll_action_id = c_payroll_action_id
259 AND ppr.payroll_action_id = mpa.payroll_action_id
260 AND p.person_id = ppr.person_id
261 AND ppr.chunk_number = c_chunk
262 AND p.person_id = a.person_id
263 AND p.business_group_id = mpa.business_group_id
264 AND apa.payroll_action_id = apac.payroll_action_id
265 AND ppa.payroll_action_id = ppac.payroll_action_id
266 AND apac.assignment_action_id = ppai.locked_action_id
267 AND ppac.assignment_action_id = ppai.locking_action_id
268 and apa.action_status = 'C'
269 AND ppa.action_status = 'C'
270 AND apa.payroll_action_id = pay_core_utils.get_parameter('ARCHIVE_ID',ppa.legislative_parameters)
271 AND a.assignment_id = apac.assignment_id
272 AND a.assignment_id = ppac.assignment_id
273 AND apa.report_type ='AU_PAYMENT_SUMMARY'
274 AND apa.report_qualifier = 'AU'
275 AND apa.report_category = 'REPORT'
276 AND ppa.report_type = 'AU_PAYMENT_SUMMARY_REPORT'
277 AND ppa.report_qualifier = 'AU'
278 AND ppa.report_category = 'REPORT'
279 AND apa.business_group_id = mpa.business_group_id
280 AND pay_core_utils.get_parameter('REGISTERED_EMPLOYER',apa.legislative_parameters)=
281 pay_core_utils.get_parameter('REGISTERED_EMPLOYER',mpa.legislative_parameters)
282 AND pay_core_utils.get_parameter('FINANCIAL_YEAR',apa.legislative_parameters)=
283 pay_core_utils.get_parameter('FINANCIAL_YEAR',mpa.legislative_parameters)
284 AND NOT EXISTS /* 5471093 */
285 (SELECT locked_action_id
286 FROM pay_action_interlocks pail,
287 pay_assignment_actions paa1,
288 pay_payroll_actions paas
289 WHERE paas.report_type='AU_PS_DATA_FILE'
290 AND paas.report_qualifier = 'AU'
291 AND paas.report_category = 'REPORT'
292 AND paas.action_status='C'
293 AND paa1.assignment_id = a.assignment_id
294 AND paa1.payroll_action_id = paas.payroll_action_id
295 AND pail.locking_action_id = paa1.assignment_action_id
296 AND pail.locked_action_id = ppac.assignment_action_id);
297
298
299 CURSOR process_assignments_val_amend /* bug 6630375 */
300 (c_payroll_action_id in pay_payroll_actions.payroll_action_id%type,
301 c_start_person_id in per_all_people_f.person_id%type,
302 c_end_person_id in per_all_people_f.person_id%type)
303 IS
304 SELECT DISTINCT 'ASSIGNMENT_ACTION_ID=C',
305 paa.assignment_action_id ps_report_id,
306 paa.assignment_id
307 FROM pay_assignment_actions paa,
308 per_assignments_f a,
309 pay_payroll_actions ppa
310 WHERE ppa.payroll_action_id = c_payroll_action_id
311 AND ppa.payroll_action_id = paa.payroll_action_id
312 AND ppa.action_status = 'C'
313 AND a.assignment_id = paa.assignment_id
314 AND pay_au_payment_summary.get_archive_value('X_REPORTING_FLAG',paa.assignment_action_id)='YES'
315 AND pay_au_payment_summary.get_archive_value('X_CURR_TERM_0_BAL_FLAG',paa.assignment_action_id)='NO'
316 AND pay_au_payment_summary.get_archive_value('X_PAYMENT_SUMMARY_TYPE',paa.assignment_action_id)='A'
317 AND ppa.report_type = 'AU_PAY_SUMM_AMEND'
318 AND a.person_id BETWEEN c_start_person_id AND c_end_person_id ;
319
320 /* 9113084 - Added below range cursor for above cursor process_assignments_val_amend */
321 /* 9113084 - Cursor to fetch assigments for Amended Data File Validation when RANGE_PERSON_ID is enabled */
322 CURSOR range_assignments_val_amend
323 (c_payroll_action_id in pay_payroll_actions.payroll_action_id%type,
324 c_chunk IN NUMBER)
325 IS
326 SELECT DISTINCT 'ASSIGNMENT_ACTION_ID=C',
327 paa.assignment_action_id ps_report_id,
328 paa.assignment_id
329 FROM pay_assignment_actions paa,
330 per_assignments_f a,
331 pay_population_ranges ppr
332 WHERE paa.payroll_action_id = c_payroll_action_id
333 AND ppr.payroll_action_id = p_payroll_action_id
334 AND ppr.chunk_number = c_chunk
335 AND paa.action_status = 'C'
336 AND a.assignment_id = paa.assignment_id
337 AND pay_au_payment_summary.get_archive_value('X_REPORTING_FLAG',paa.assignment_action_id)='YES'
338 AND pay_au_payment_summary.get_archive_value('X_CURR_TERM_0_BAL_FLAG',paa.assignment_action_id)='NO'
339 AND pay_au_payment_summary.get_archive_value('X_PAYMENT_SUMMARY_TYPE',paa.assignment_action_id)='A'
340 AND a.person_id = ppr.person_id ;
341
342
343 CURSOR process_assignments_amend /* bug 6630375 */
344 (c_payroll_action_id in pay_payroll_actions.payroll_action_id%type,
345 c_start_person_id in per_all_people_f.person_id%type,
346 c_end_person_id in per_all_people_f.person_id%type) is
347 SELECT /*+ INDEX (apac PAY_ASSIGNMENT_ACTIONS_N50)
348 INDEX (ppac PAY_ASSIGNMENT_ACTIONS_PK)
349 INDEX(mpa PAY_PAYROLL_ACTIONS_PK)
350 INDEX(ppai PAY_ACTION_INTERLOCKS_FK2)
351 INDEX (p PER_PEOPLE_F_PK)
352 INDEX(a PER_ASSIGNMENTS_F_PK) */
353 DISTINCT 'ASSIGNMENT_ACTION_ID=C',
354 ppac.assignment_action_id ps_report_id,
355 ppac.assignment_id
356 FROM pay_payroll_actions mpa,
357 per_people_f p,
358 per_assignments_f a,
359 pay_payroll_actions apa,
360 pay_assignment_actions apac,
361 pay_payroll_actions ppa,
362 pay_assignment_actions ppac,
363 pay_action_interlocks ppai
364 WHERE mpa.payroll_action_id =c_payroll_action_id
365 AND p.person_id = a.person_id
366 AND p.person_id BETWEEN c_start_person_id AND c_end_person_id
367 AND p.business_group_id = mpa.business_group_id
368 AND apa.payroll_action_id = apac.payroll_action_id
369 AND ppa.payroll_action_id = ppac.payroll_action_id
370 AND apac.assignment_action_id = ppai.locked_action_id
371 AND ppac.assignment_action_id = ppai.locking_action_id
372 and apa.action_status = 'C'
373 AND ppa.action_status = 'C'
374 AND apa.payroll_action_id = pay_core_utils.get_parameter('ARCHIVE_ID',ppa.legislative_parameters)
375 AND a.assignment_id = apac.assignment_id
376 AND a.assignment_id = ppac.assignment_id
377 AND apa.report_type ='AU_PAY_SUMM_AMEND'
378 AND ppa.report_type = 'AU_PAYMENT_SUMMARY_REPORT'
379 AND pay_core_utils.get_parameter('BUSINESS_GROUP_ID',apa.legislative_parameters)=
380 pay_core_utils.get_parameter('BUSINESS_GROUP_ID',mpa.legislative_parameters)
381 AND pay_core_utils.get_parameter('REGISTERED_EMPLOYER',apa.legislative_parameters)=
382 pay_core_utils.get_parameter('REGISTERED_EMPLOYER',mpa.legislative_parameters)
383 AND pay_core_utils.get_parameter('FINANCIAL_YEAR',apa.legislative_parameters)=
384 pay_core_utils.get_parameter('FINANCIAL_YEAR',mpa.legislative_parameters)
385 AND pay_au_payment_summary.get_archive_value('X_PAYMENT_SUMMARY_TYPE', apac.assignment_action_id)='A'
386 AND NOT EXISTS /* 5471093 */
387 (SELECT /*+ ORDERED */ locked_action_id
388 FROM pay_action_interlocks pail,
389 pay_assignment_actions paa1,
390 pay_payroll_actions paas
391 WHERE paas.action_type='X'
392 and paas.action_status='C'
393 AND paas.report_type='AU_PS_DATA_FILE'
394 AND paa1.payroll_action_id = paas.payroll_action_id
395 AND pail.locking_action_id = paa1.assignment_action_id
396 AND pail.locked_action_id = ppac.assignment_action_id);
397
398
399 /* 9113084 - Added Range Cursor for above cursor process_assignments_amend */
400 /* 9113084 - Cursor fetches assignments for Amended Data File when RANGE_PERSON_ID is enabled */
401 CURSOR rg_process_assignments_amend
402 (c_payroll_action_id in pay_payroll_actions.payroll_action_id%type,
403 c_chunk IN NUMBER) is
404 SELECT /*+ INDEX (apac PAY_ASSIGNMENT_ACTIONS_N50)
405 INDEX (ppac PAY_ASSIGNMENT_ACTIONS_PK)
406 INDEX(mpa PAY_PAYROLL_ACTIONS_PK)
407 INDEX(ppai PAY_ACTION_INTERLOCKS_FK2)
408 INDEX (p PER_PEOPLE_F_PK)
409 INDEX(a PER_ASSIGNMENTS_F_PK) */
410 DISTINCT 'ASSIGNMENT_ACTION_ID=C',
411 ppac.assignment_action_id ps_report_id,
412 ppac.assignment_id
413 FROM pay_payroll_actions mpa,
414 per_people_f p,
415 per_assignments_f a,
416 pay_payroll_actions apa,
417 pay_assignment_actions apac,
418 pay_payroll_actions ppa,
419 pay_assignment_actions ppac,
420 pay_action_interlocks ppai,
421 pay_population_ranges ppr
422 WHERE mpa.payroll_action_id =c_payroll_action_id
423 AND ppr.payroll_action_id = mpa.payroll_action_id
424 AND ppr.chunk_number = c_chunk
425 AND p.person_id = a.person_id
426 AND p.person_id = ppr.person_id
427 AND p.business_group_id = mpa.business_group_id
428 AND apa.payroll_action_id = apac.payroll_action_id
429 AND ppa.payroll_action_id = ppac.payroll_action_id
430 AND apac.assignment_action_id = ppai.locked_action_id
431 AND ppac.assignment_action_id = ppai.locking_action_id
432 and apa.action_status = 'C'
433 AND ppa.action_status = 'C'
434 AND apa.payroll_action_id = pay_core_utils.get_parameter('ARCHIVE_ID',ppa.legislative_parameters)
435 AND a.assignment_id = apac.assignment_id
436 AND a.assignment_id = ppac.assignment_id
437 AND apa.report_type ='AU_PAY_SUMM_AMEND'
438 AND ppa.report_type = 'AU_PAYMENT_SUMMARY_REPORT'
439 AND pay_core_utils.get_parameter('BUSINESS_GROUP_ID',apa.legislative_parameters)=
440 pay_core_utils.get_parameter('BUSINESS_GROUP_ID',mpa.legislative_parameters)
441 AND pay_core_utils.get_parameter('REGISTERED_EMPLOYER',apa.legislative_parameters)=
442 pay_core_utils.get_parameter('REGISTERED_EMPLOYER',mpa.legislative_parameters)
443 AND pay_core_utils.get_parameter('FINANCIAL_YEAR',apa.legislative_parameters)=
444 pay_core_utils.get_parameter('FINANCIAL_YEAR',mpa.legislative_parameters)
445 AND pay_au_payment_summary.get_archive_value('X_PAYMENT_SUMMARY_TYPE', apac.assignment_action_id)='A'
446 AND NOT EXISTS
447 (SELECT /*+ ORDERED */ locked_action_id
448 FROM pay_action_interlocks pail,
449 pay_assignment_actions paa1,
450 pay_payroll_actions paas
451 WHERE paas.action_type='X'
452 and paas.action_status='C'
453 AND paas.report_type='AU_PS_DATA_FILE'
454 AND paa1.payroll_action_id = paas.payroll_action_id
455 AND pail.locking_action_id = paa1.assignment_action_id
456 AND pail.locked_action_id = ppac.assignment_action_id);
457
458
459 CURSOR next_action_id IS
460 SELECT pay_assignment_actions_s.NEXTVAL
461 FROM dual;
462
463 BEGIN
464
465 IF g_debug THEN
466 hr_utility.set_location('Start of assignment_action_code',3);
467 END IF;
468 --------start of Bug3132178-----------------------------------------------------
469 OPEN get_parameters;
470 FETCH get_parameters INTO l_testing_flag,l_archive_payroll_action,l_payment_summary_type;
471 CLOSE get_parameters;
472
473
474 IF l_testing_flag = 'Y' THEN -- In this case fetch the assignments processed by archival process
475 IF l_payment_summary_type = 'O' THEN
476 IF range_person_on THEN /* 9113084 - Use new Range Person Cursor if Range Person is enabled */
477 IF g_debug THEN
478 hr_utility.set_location('Using Range Person Cursor for fetching assignments', 5);
479 END IF;
480 FOR process_rec IN rg_process_assignments_val (l_archive_payroll_action,
481 p_chunk)
482 LOOP
483 EXIT WHEN rg_process_assignments_val%NOTFOUND;
484 OPEN next_action_id;
485 FETCH next_action_id INTO v_next_action_id;
486 CLOSE next_action_id;
487 hr_nonrun_asact.insact(v_next_action_id,
488 process_rec.assignment_id,
489 p_payroll_action_id,
490 p_chunk,
491 NULL);
492 IF g_debug THEN
493 hr_utility.set_location('After calling hr_nonrun_asact.insint',5);
494 END IF;
495 END LOOP;
496 ELSE /* 9113084 - Use Old logic if Range Person is disabled */
497
498 FOR process_rec IN process_assignments_val (l_archive_payroll_action,
499 p_start_person_id,
500 p_end_person_id)
501 LOOP
502 EXIT WHEN process_assignments_val%NOTFOUND;
503 OPEN next_action_id;
504 FETCH next_action_id INTO v_next_action_id;
505 CLOSE next_action_id;
506 hr_nonrun_asact.insact(v_next_action_id,
507 process_rec.assignment_id,
508 p_payroll_action_id,
509 p_chunk,
510 NULL);
511 IF g_debug THEN
512 hr_utility.set_location('After calling hr_nonrun_asact.insint',14);
513 END IF;
514 END LOOP;
515 END IF;
516 ELSE /* bug 6630375 */
517 IF range_person_on THEN
518 IF g_debug THEN
519 hr_utility.set_location('Using Range Person Cursor for fetching assignments', 5);
520 END IF;
521 FOR process_rec IN range_assignments_val_amend(l_archive_payroll_action,
522 p_chunk)
523 LOOP
524 EXIT WHEN range_assignments_val_amend%NOTFOUND;
525 OPEN next_action_id;
526 FETCH next_action_id INTO v_next_action_id;
527 CLOSE next_action_id;
528 hr_nonrun_asact.insact(v_next_action_id,
529 process_rec.assignment_id,
530 p_payroll_action_id,
531 p_chunk,
532 NULL);
533 IF g_debug THEN
534 hr_utility.set_location('After calling hr_nonrun_asact.insint',14);
535 END IF;
536 END LOOP;
537 ELSE /* 9113084 - Use Old Logic when Range Person is disabled */
538
539 FOR process_rec IN process_assignments_val_amend (l_archive_payroll_action,
540 p_start_person_id,
541 p_end_person_id)
542 LOOP
543 EXIT WHEN process_assignments_val_amend%NOTFOUND;
544 OPEN next_action_id;
545 FETCH next_action_id INTO v_next_action_id;
546 CLOSE next_action_id;
547 hr_nonrun_asact.insact(v_next_action_id,
548 process_rec.assignment_id,
549 p_payroll_action_id,
550 p_chunk,
551 NULL);
552 IF g_debug THEN
553 hr_utility.set_location('After calling hr_nonrun_asact.insint',14);
554 END IF;
555 END LOOP;
556 END IF;
557 END IF;
558 ------End of Bug3132178-----------------------------------------------------
559 ELSE -- In this case fetch the assignments locked by Self-Printed Process.
560 IF l_payment_summary_type = 'O' THEN
561
562 /* Bug 8501365 - Added Changes for Range Person
563 - Call Cursor using pay_population_ranges if Range Person Enabled
564 Else call Old Cursor
565 */
566 IF range_person_on
567 THEN
568
569 FOR process_rec IN range_process_assignments
570 (p_payroll_action_id,
571 p_chunk)
572 LOOP
573 EXIT WHEN range_process_assignments%NOTFOUND;
574 OPEN next_action_id;
575 FETCH next_action_id INTO v_next_action_id;
576 CLOSE next_action_id;
577 hr_nonrun_asact.insact(v_next_action_id,
578 process_rec.assignment_id,
579 p_payroll_action_id,
580 p_chunk,
581 NULL);
582 IF g_debug THEN
583 hr_utility.set_location('Before calling hr_nonrun_asact.insint',14);
584 hr_utility.set_location('locking action' || v_next_action_id, 15);
585 hr_utility.set_location('locked action' || process_rec.ps_report_id, 16);
586 END IF;
587 hr_nonrun_asact.insint(v_next_action_id, -- locking action id
588 process_rec.ps_report_id); -- locked action id
589
590 IF g_debug THEN
591 hr_utility.set_location('After calling hr_nonrun_asact.insint',14);
592 END IF;
593 END LOOP;
594
595 ELSE
596
597 FOR process_rec IN process_assignments (p_payroll_action_id,
598 p_start_person_id,
599 p_end_person_id)
600 LOOP
601 EXIT WHEN process_assignments%NOTFOUND;
602 OPEN next_action_id;
603 FETCH next_action_id INTO v_next_action_id;
604 CLOSE next_action_id;
605 hr_nonrun_asact.insact(v_next_action_id,
606 process_rec.assignment_id,
607 p_payroll_action_id,
608 p_chunk,
609 NULL);
610 IF g_debug THEN
611 hr_utility.set_location('Before calling hr_nonrun_asact.insint',14);
612 hr_utility.set_location('locking action' || v_next_action_id, 15);
613 hr_utility.set_location('locked action' || process_rec.ps_report_id, 16);
614 END IF;
615 hr_nonrun_asact.insint(v_next_action_id, -- locking action id
616 process_rec.ps_report_id); -- locked action id
617
618 IF g_debug THEN
619 hr_utility.set_location('After calling hr_nonrun_asact.insint',14);
620 END IF;
621 END LOOP;
622 END IF;
623 ELSE /* bug 6630375 */
624 IF range_person_on THEN /* 9113084 - Use new Range Person Cursor if Range Person is enabled */
625 IF g_debug THEN
626 hr_utility.set_location('Using Range Person Cursor for fetching assignments', 5);
627 END IF;
628 FOR process_rec IN rg_process_assignments_amend (p_payroll_action_id,
629 p_chunk)
630 LOOP
631 EXIT WHEN rg_process_assignments_amend%NOTFOUND;
632 OPEN next_action_id;
633 FETCH next_action_id INTO v_next_action_id;
634 CLOSE next_action_id;
635 hr_nonrun_asact.insact(v_next_action_id,
636 process_rec.assignment_id,
637 p_payroll_action_id,
638 p_chunk,
639 NULL);
640 IF g_debug THEN
641 hr_utility.set_location('Before calling hr_nonrun_asact.insint',14);
642 hr_utility.set_location('locking action' || v_next_action_id, 15);
643 hr_utility.set_location('locked action' || process_rec.ps_report_id, 16);
644 END IF;
645 hr_nonrun_asact.insint(v_next_action_id, -- locking action id
646 process_rec.ps_report_id); -- locked action id
647
648 IF g_debug THEN
649 hr_utility.set_location('After calling hr_nonrun_asact.insint',14);
650 END IF;
651 END LOOP;
652 ELSE /* 9113084 - Use Old Logic if Range Person is disabled */
653
654 FOR process_rec IN process_assignments_amend (p_payroll_action_id,
655 p_start_person_id,
656 p_end_person_id)
657 LOOP
658 EXIT WHEN process_assignments_amend%NOTFOUND;
659 OPEN next_action_id;
660 FETCH next_action_id INTO v_next_action_id;
661 CLOSE next_action_id;
662 hr_nonrun_asact.insact(v_next_action_id,
663 process_rec.assignment_id,
664 p_payroll_action_id,
665 p_chunk,
666 NULL);
667 IF g_debug THEN
668 hr_utility.set_location('Before calling hr_nonrun_asact.insint',14);
669 hr_utility.set_location('locking action' || v_next_action_id, 15);
670 hr_utility.set_location('locked action' || process_rec.ps_report_id, 16);
671 END IF;
672 hr_nonrun_asact.insint(v_next_action_id, -- locking action id
673 process_rec.ps_report_id); -- locked action id
674
675 IF g_debug THEN
676 hr_utility.set_location('After calling hr_nonrun_asact.insint',14);
677 END IF;
678 END LOOP;
679 END IF;
680 END IF;
681 END IF;
682 IF g_debug THEN
683 hr_utility.set_location('End of assignment_action_code',5);
684 END IF;
685 END assignment_action_code;
686
687 -----------------------------------------------------------------------+
688 -- This is used by legislation groups to set global contexts that are
689 -- required for the lifetime of the archiving process. This is null
690 -- because there are no setup requirements, but a procedure needs to
691 -- exist in pay_report_format_mappings_f, otherwise the archiver will
692 -- assume that no archival of data is required.
693 ------------------------------------------------------------------------+
694
695 procedure initialization_code
696 (p_payroll_action_id in pay_payroll_actions.payroll_action_id%type) is
697 begin
698 IF g_debug THEN
699 hr_utility.set_location('Start of initialization_code',6);
700 END IF;
701 null;
702 IF g_debug THEN
703 hr_utility.set_location('End of initialization_code',7);
704 END IF;
705 end initialization_code;
706
707
708 -------------------------------------------------------------------------+
709 -- Used to actually perform the archival of data. We are not archiving
710 -- any data here, so this is null.
711 ------------------------------------------------------------------------+
712 procedure archive_code
713 (p_payroll_action_id in pay_assignment_actions.payroll_action_id%type,
714 p_effective_date in date)
715 is
716
717 begin
718 IF g_debug THEN
719 hr_utility.set_location('Start of archive_code',8);
720 END IF;
721 null;
722 IF g_debug THEN
723 hr_utility.set_location('End of archive_code',9);
724 END IF;
725 end archive_code;
726 ---------------------------------------------------------------------------+
727
728 End pay_au_payment_summary_magtape;
[Home] [Help]