[Home] [Help]
PACKAGE BODY: APPS.PAY_CC_PROCESS_UTILS
Source
1 PACKAGE BODY PAY_CC_PROCESS_UTILS AS
2 /* $Header: pyccutl.pkb 120.5.12020000.4 2012/12/13 12:14:35 jkvallab ship $ */
3
4 g_pkg varchar2(80) := 'PAY_CC_PROCESS_UTILS';
5
6 -- Global for PAY schema name
7 g_pay_schema varchar2(30) := null;
8
9 /* Name : range_cursor
10 Purpose : This returns the select statement that is used to created the
11 range rows.
12 Arguments :
13 Notes :
14 */
15 procedure range_cursor (pactid in number, sqlstr out nocopy varchar2) is
16
17 begin
18
19 --
20 -- hr_utility.trace('>>> NOW build sql string');
21 /* Effective date will be set to sysdate for CC*/
22 sqlstr := 'select distinct asg.person_id
23 from
24 per_assignments_f asg,
25 pay_payroll_actions pa1
26 where pa1.payroll_action_id = :payroll_action_id
27 and asg.payroll_id =
28 pay_core_utils.get_parameter(''PAYROLL_ID'',
29 pa1.legislative_parameters)
30 and pa1.effective_date < asg.effective_end_date
31 order by asg.person_id';
32 --
33
34
35
36 end range_cursor;
37
38 --
39 /* Name : action_creation
40 Purpose : This creates the assignment actions for a specific chunk.
41 Arguments :
42 Notes :
43 */
44 --
45 procedure action_creation(p_pactid in number,
46 p_stperson in number,
47 p_endperson in number,
48 p_chunk in number) is
49 CURSOR c_actions
50 (
51 cp_pactid number,
52 cp_payroll_id number,
53 cp_stperson number,
54 cp_endperson number
55 ) is
56 select distinct ppe.assignment_id,ppe.creation_date
57 from
58 per_all_assignments_f paf,
59 per_periods_of_service pos,
60 pay_payroll_actions ppa,
61 pay_process_events ppe
62 where ppa.payroll_action_id = cp_pactid
63 and paf.payroll_id = cp_payroll_id
64 and ppe.effective_date between paf.effective_start_date
65 and paf.effective_end_date
66 and pos.period_of_service_id = paf.period_of_service_id
67 and pos.person_id between cp_stperson and cp_endperson
68 and ppe.assignment_id = paf.assignment_id
69 and ppe.change_type in ('ASG', 'GRE', 'COST_CENTRE', 'PAYMENT',
70 'DATE_EARNED', 'DATE_PROCESSED');
71 -- for update of paf.assignment_id, pos.period_of_service_id;
72 --
73
74 CURSOR csr_act_info(cp_actid number) is
75 select
76 pay_core_utils.get_parameter('PAYROLL_ID',ppa.legislative_parameters)
77 from pay_payroll_actions ppa
78 where ppa.payroll_action_id = cp_actid;
79
80
81 l_lockingactid NUMBER;
82 l_last_run_date DATE;
83 l_payroll_id NUMBER;
84
85 l_proc varchar2(80) := g_pkg||'action_creation';
86 --
87 begin
88 hr_utility.set_location(l_proc, 10);
89
90 -->>> PHASE 1: Get the payroll id for this pay act id
91 --
92 open csr_act_info(p_pactid);
93 fetch csr_act_info into l_payroll_id;
94 close csr_act_info;
95
96 -->>> PHASE 2: Create action records
97 for asgrec in c_actions(p_pactid,l_payroll_id,p_stperson, p_endperson ) loop
98 -->>> PHASE 3: Get the date the last time the CC process was run for this payroll.
99 pay_recorded_requests_pkg.get_recorded_date(
100 p_process => 'CC_ASG',
101 p_recorded_date => l_last_run_date,
102 p_attribute1 => asgrec.assignment_id
103 );
104 hr_utility.trace('>>> Last time CC was run for payroll '||l_payroll_id
105 ||' is: '||l_last_run_date);
106
107 if (l_last_run_date < asgrec.creation_date) then
108 SELECT pay_assignment_actions_s.nextval
109 INTO l_lockingactid
110 FROM dual;
111 -- insert the action record.
112 hr_nonrun_asact.insact(l_lockingactid,asgrec.assignment_id,p_pactid,p_chunk, null);
113 end if;
114 end loop;
115 hr_utility.set_location(l_proc, 900);
116
117 end action_creation;
118 --
119
120
121 --
122 /* Name : archinit
123 Purpose : This performs the US specific initialisation section.
124 Arguments :
125 Notes :
126 */
127 procedure archinit(p_payroll_action_id in number) is
128 jurisdiction_code pay_state_rules.jurisdiction_code%TYPE;
129 l_state VARCHAR2(30);
130 begin
131
132 null;
133 end archinit;
134 --
135
136 /* Name : archive_data
137 Purpose : This performs the US specific employee context setting for the SQWL
138 report.
139 Arguments :
140 Notes :
141 */
142 procedure archive_data(p_assactid in number, p_effective_date in date) is
143 --
144 --
145 cursor get_dates (assact_id number, p_change_type varchar2) is
146 select nvl(min(ppe.effective_date), hr_api.g_eot)
147 effective_date
148 from pay_process_events ppe,
149 pay_assignment_actions paa
150 where paa.assignment_action_id = assact_id
151 and paa.assignment_id = ppe.assignment_id
152 and change_type = p_change_type;
153 --
154 cursor get_costings(p_assact_id number, p_effdate date) is
155 select paa.assignment_action_id
156 from pay_payroll_actions ppa,
157 pay_assignment_actions paa,
158 pay_assignment_actions paa_arch
159 where paa_arch.assignment_action_id = p_assact_id
160 and paa.assignment_id = paa_arch.assignment_id
161 and paa.action_status = 'C'
162 and paa.payroll_action_id = ppa.payroll_action_id
163 and ppa.action_type = 'C'
164 and ppa.effective_date >= p_effdate
165 and not exists (select ''
166 from pay_action_interlocks pai
167 where pai.locked_action_id = paa.assignment_action_id)
168 order by paa.action_sequence desc;
169 --
170 cursor get_prepay(p_assact_id number, p_effdate date) is
171 select paa.assignment_action_id
172 from pay_payroll_actions ppa,
173 pay_assignment_actions paa,
174 pay_assignment_actions paa_arch
175 where paa_arch.assignment_action_id = p_assact_id
176 and paa.assignment_id = paa_arch.assignment_id
177 and paa.action_status = 'C'
178 and paa.payroll_action_id = ppa.payroll_action_id
179 and ppa.action_type in ('P', 'U')
180 and ppa.effective_date >= p_effdate
181 and not exists (select ''
182 from pay_action_interlocks pai
183 where pai.locked_action_id = paa.assignment_action_id)
184 order by paa.action_sequence desc;
185 --
186 cursor get_run(p_assact_id number, p_effdate date) is
187 select paa.assignment_action_id,
188 ppa.effective_date,
189 paa.assignment_id
190 from pay_payroll_actions ppa,
191 pay_assignment_actions paa,
192 pay_assignment_actions paa_arch
193 where paa_arch.assignment_action_id = p_assact_id
194 and paa.assignment_id = paa_arch.assignment_id
195 and paa.action_status in ('C', 'S')
196 and paa.payroll_action_id = ppa.payroll_action_id
197 and paa.source_action_id is null
198 and ppa.action_type in ('R', 'Q','V','B')
199 and ppa.effective_date >= p_effdate
200 and not exists (select ''
201 from pay_action_interlocks pai,
202 pay_assignment_actions paa2,
203 pay_payroll_actions ppa2,
204 pay_action_interlocks pai2
205 where pai.locked_action_id = paa.assignment_action_id
206 and pai.locking_action_id = paa2.assignment_action_id
207 and paa2.payroll_action_id = ppa2.payroll_action_id
208 and ppa2.action_type in ('P', 'U', 'C')
209 and pai2.locked_action_id = paa2.assignment_action_id
210 )
211 and not exists (
212 select null
213 from pay_action_classifications acl,
214 pay_payroll_actions pa2,
215 pay_assignment_actions ac2
216 where ac2.assignment_id = paa.assignment_id
217 and pa2.payroll_action_id = ac2.payroll_action_id
218 and acl.classification_name = 'SEQUENCED'
219 and pa2.action_type = acl.action_type
220 and pa2.action_type not in ('R', 'Q','V','B')
221 and ac2.action_sequence > paa.action_sequence
222 )
223 and not exists (
224 select null
225 from pay_action_classifications acl,
226 pay_payroll_actions pa2,
227 pay_assignment_actions ac2
228 where ac2.assignment_id = paa.assignment_id
229 and pa2.payroll_action_id = ac2.payroll_action_id
230 and acl.classification_name = 'SEQUENCED'
231 and pa2.action_type = acl.action_type
232 and pa2.action_type in ('R', 'Q','V','B')
233 and ac2.action_sequence > paa.action_sequence
234 and exists (select ''
235 from pay_action_interlocks pai,
236 pay_assignment_actions paa2,
237 pay_payroll_actions ppa2,
238 pay_action_interlocks pai2
239 where pai.locked_action_id = ac2.assignment_action_id
240 and pai.locking_action_id = paa2.assignment_action_id
241 and paa2.payroll_action_id = ppa2.payroll_action_id
242 and ppa2.action_type in ('P', 'U', 'C')
243 and pai2.locked_action_id = paa2.assignment_action_id
244 )
245 )
246 order by paa.action_sequence desc;
247 --
248 cursor get_locked_actions (p_assact_id number) is
249 select paa.assignment_action_id
250 from pay_assignment_actions paa,
251 pay_action_interlocks pai,
252 pay_payroll_actions ppa
253 where pai.locked_action_id = p_assact_id
254 and pai.locking_action_id = paa.assignment_action_id
255 and paa.payroll_action_id = ppa.payroll_action_id
256 and paa.action_status = 'C'
257 and ppa.action_type in ('P','U', 'C')
258 order by paa.action_sequence desc;
259 --
260
261 cursor csr_act_info(cp_actid number) is
262 select
263 paa.assignment_id,
264 pay_core_utils.get_parameter('PAYROLL_ID',ppa.legislative_parameters),
265 ppa.business_group_id
266 from pay_assignment_actions paa,
267 pay_payroll_actions ppa
268 where paa.payroll_action_id = ppa.payroll_action_id
269 and paa.assignment_action_id = cp_actid;
270
271
272
273 cursor csr_get_de_min(cp_assact_id number) is
274 select min(ppa.date_earned)
275 from pay_payroll_actions ppa,
276 pay_assignment_actions paa,
277 pay_assignment_actions paa_arch
278 where paa_arch.assignment_action_id = cp_assact_id
279 and paa.assignment_id = paa_arch.assignment_id
280 and paa.action_status in ('C', 'S')
281 and paa.payroll_action_id = ppa.payroll_action_id
282 and paa.source_action_id is null
283 and ppa.action_type in ('R', 'Q')
284 and not exists (
285 select 1
286 from pay_action_interlocks pai,
287 pay_assignment_actions paa2,
288 pay_payroll_actions ppa2,
289 pay_action_interlocks pai2
290 where pai.locked_action_id = paa.assignment_action_id
291 and pai.locking_action_id = paa2.assignment_action_id
292 and paa2.payroll_action_id = ppa2.payroll_action_id
293 and ppa2.action_type in ('P', 'U', 'C')
294 and pai2.locked_action_id = paa2.assignment_action_id
295 )
296 and not exists (
297 select 1
298 from pay_action_classifications acl,
299 pay_payroll_actions pa2,
300 pay_assignment_actions ac2
301 where ac2.assignment_id = paa.assignment_id
302 and pa2.payroll_action_id = ac2.payroll_action_id
303 and acl.classification_name = 'SEQUENCED'
304 and pa2.action_type = acl.action_type
305 and pa2.action_type not in ('R', 'Q')
306 and ac2.action_sequence > paa.action_sequence
307 );
308
309 cursor c_ele(cp_assact_id number, cp_bg number
310 , cp_de_min date, cp_this_run_date date) is
311 SELECT DISTINCT
312 prr.source_id entry
313 , pet.recalc_event_group_id event_group
314 FROM pay_run_results prr
315 , pay_assignment_actions paa
316 , pay_payroll_actions ppa
317 , pay_assignment_actions paa_arch
318 , pay_element_types_f pet
319 WHERE paa_arch.assignment_action_id = cp_assact_id
320 and paa.assignment_id = paa_arch.assignment_id
321 AND prr.source_type = 'E'
322 AND prr.assignment_action_id = paa.assignment_action_id
323 AND paa.payroll_action_id = ppa.payroll_action_id
324 AND prr.element_type_id = pet.element_type_id
325 AND cp_this_run_date between pet.effective_start_date and pet.effective_end_date
326 AND ppa.business_group_id = cp_bg
327 AND ppa.action_type in ('R', 'Q', 'B', 'V')
328 AND ppa.date_earned >= cp_de_min
329 UNION
330 SELECT DISTINCT
331 pee.element_entry_id entry
332 , pet.recalc_event_group_id event_group
333 FROM pay_element_entries_f pee
334 , pay_assignment_actions paa
335 , pay_element_links_f pel
336 , pay_element_types_f pet
337 WHERE paa.assignment_action_id = cp_assact_id
338 AND paa.assignment_id = pee.assignment_id
339 AND pee.element_link_id = pel.element_link_id
340 AND pel.element_type_id = pet.element_type_id
341 AND pee.effective_end_date
342 between pel.effective_start_date and pel.effective_end_date
343 AND cp_this_run_date
344 between pet.effective_start_date and pet.effective_end_date
345 AND pee.effective_end_date >= cp_de_min;
346
347 cursor csr_min_date(cp_creation_date_from date,
348 cp_change_type varchar2,
349 cp_ass_id number) is
350 select least(effective_date)
351 from pay_process_events
352 where creation_date > cp_creation_date_from
353 and change_type = cp_change_type
354 and status <> 'C'
355 and assignment_id = cp_ass_id;
356
357
358 l_min_de_date date; --Placeholder for result of above csr_get_de_min
359 l_tax_unit_id number;
360 l_business_group_id number;
361 l_min_date DATE;
362 l_max_date DATE;
363 l_run_date DATE;
364 l_payroll_id NUMBER;
365 l_assignment_id NUMBER;
366 l_ee_min_dedate DATE := hr_api.g_eot; --Interim placeholders holding
367 l_ee_min_dpdate DATE := hr_api.g_eot; --min of ee's events type DATE_EARNED and DATE_PROCESSED
368 l_aact_min_dedate DATE := hr_api.g_eot; --Interim placeholders for
369 l_aact_min_dedate_eff DATE := hr_api.g_eot; --
370 l_aact_min_dpdate DATE := hr_api.g_eot; --Overall min for asg action events types
371 cstdate DATE := hr_api.g_eot; -- Final placeholders for the final dates
372 rundate DATE := hr_api.g_eot;
373 paydate DATE := hr_api.g_eot;
374
375 -- Holders for results from the interpreter package
376 l_de_det_tab_out pay_interpreter_pkg.t_detailed_output_table_type;
377 l_de_date_out pay_interpreter_pkg.t_proration_dates_table_type;
378 l_de_chge_out pay_interpreter_pkg.t_proration_type_table_type;
379 l_de_type_out pay_interpreter_pkg.t_proration_type_table_type;
380
381 l_dp_det_tab_out pay_interpreter_pkg.t_detailed_output_table_type;
382 l_dp_date_out pay_interpreter_pkg.t_proration_dates_table_type;
383 l_dp_chge_out pay_interpreter_pkg.t_proration_type_table_type;
384 l_dp_type_out pay_interpreter_pkg.t_proration_type_table_type;
385
386 l_proc varchar2(80) := g_pkg||'.archive_data';
387 BEGIN
388 hr_utility.set_location(' Entering: '||l_proc,10);
389
390 -->>> PHASE 1: Get details on the p act, include the date this CC run started
391 -- held in the legis_params column, put there by range_cursor
392
393 -- get current date and time , this can be set in pay_recorded requests
394 -- when assignment action has been processed.
395 select sysdate
396 into l_run_date
397 from dual;
398
399 -- hr_utility.trace('>>> p_assactid '||p_assactid);
400 -- hr_utility.set_location(l_proc, 20);
401
402 open csr_act_info(p_assactid);
403 fetch csr_act_info into
404 l_assignment_id,l_payroll_id,
405 l_business_group_id;
406 close csr_act_info;
407
408
409 -->>> PHASE 2: Loop through all element_entries in our date range
410 --
411
412 --Get the element entries for the asg_act, where date earned
413 -- >= min de date , (ie those that will have been included in the last payroll.)
414
415 open csr_get_de_min(p_assactid);
416 fetch csr_get_de_min into l_min_de_date;
417 close csr_get_de_min;
418
419 -- We need to tell the Interpreter we are only interested in events between
420 -- the last time CC was run, and the time this run was begun
421
422 pay_recorded_requests_pkg.get_recorded_date(
423 p_process => 'CC_ASG',
424 p_recorded_date => l_min_date,
425 p_attribute1 => l_assignment_id);
426
427 -- just got the min creation date were interested in, max is
428 -- this run date got from csr_act_info
429 l_max_date := l_run_date;
430
431 -- hr_utility.trace('>> Get element entry ids asg act id: '||p_assactid||', since l_min_de_date: '||to_char(l_min_de_date,'DD-MM-RRRR'));
432 -- hr_utility.trace('>> Got date for payroll id: '||l_payroll_id||' last CC date: '||to_char(l_min_date,'DD-MM-RRRR'));
433 FOR l_ele_rec in c_ele(p_assactid,l_business_group_id
434 ,l_min_de_date,l_run_date) LOOP
435 --
436 -- Get the tables of results from the interpreter package
437 -- This is a construct of all the valid events that have occurred
438 --
439 hr_utility.trace('>>> Calling interpreter for ee: '||l_ele_rec.entry);
440 hr_utility.trace('>>> Events in range '||l_min_date||' to '||l_max_date);
441
442
443 -->>> PHASE 3a: Call Interpreter in DATE_EARNED mode
444 --
445 -- CC mark for retry requires the min date for an events with DATE_EARNED
446 -- So get all such events and find minimum
447 pay_interpreter_pkg.entry_affected(
448 p_element_entry_id => l_ele_rec.entry
449 , p_assignment_action_id => null
450 , p_assignment_id => l_assignment_id
451 , p_mode => 'DATE_EARNED'
452 , p_process => null --dont care (as long as doesnt restrict)
453 , p_event_group_id => l_ele_rec.event_group
454 , p_process_mode => 'ENTRY_CREATION_DATE'
455 , p_start_date => l_min_date
456 , p_end_date => l_max_date
457 , p_process_date => l_run_date
458 , t_detailed_output => l_de_det_tab_out
459 , t_proration_dates => l_de_date_out
460 , t_proration_change_type => l_de_chge_out
461 , t_proration_type => l_de_type_out);
462 --
463
464 -- Need the min date for all of the modes
465 hr_utility.trace('>>> TOTAL NUMBER OF DATE_EARNED EVENTS FOR ee '
466 ||l_ele_rec.entry ||' IS '||l_de_det_tab_out.COUNT);
467
468 if (l_de_det_tab_out.COUNT <> 0) then
469 FOR i in 1..l_de_det_tab_out.COUNT loop
470 hr_utility.trace('Discovered Event: '||l_de_det_tab_out(i).datetracked_event
471 ||' Change mode '||l_de_det_tab_out(i).change_mode
472 ||' - '||l_de_det_tab_out(i).effective_date );
473
474 IF( l_de_det_tab_out(i).effective_date < l_ee_min_dedate) then
475 l_ee_min_dedate := l_de_det_tab_out(i).effective_date;
476 END IF;
477 END LOOP;
478 end if;
479
480 -->>> PHASE 3b: Call Interpreter in DATE_PROCESSED mode
481 --
482 -- CC mark for retry also requires the min date for an events with DATE_PROCESSED
483 -- So get all such events and find minimum
484 pay_interpreter_pkg.entry_affected(
485 p_element_entry_id => l_ele_rec.entry
486 , p_assignment_action_id => null
487 , p_assignment_id => l_assignment_id
488 , p_mode => 'DATE_PROCESSED'
489 , p_process => null --dont care (as long as doesnt restrict)
490 , p_event_group_id => l_ele_rec.event_group
491 , p_process_mode => 'ENTRY_CREATION_DATE'
492 , p_start_date => l_min_date
493 , p_end_date => l_max_date
494 , p_process_date => l_run_date
495 , t_detailed_output => l_dp_det_tab_out
496 , t_proration_dates => l_dp_date_out
497 , t_proration_change_type => l_dp_chge_out
498 , t_proration_type => l_dp_type_out);
499 --
500 -- Need the min date
501 hr_utility.trace('>>> TOTAL NUMBER OF DATE_PROCESSED EVENTS FOR ee '
502 ||l_ele_rec.entry ||' IS '||l_dp_det_tab_out.COUNT);
503
504 if (l_dp_det_tab_out.COUNT <> 0) then
505 FOR i in 1..l_dp_det_tab_out.COUNT loop
506 hr_utility.trace('Discovered Event: '||l_dp_det_tab_out(i).datetracked_event
507 ||' Change mode '||l_dp_det_tab_out(i).change_mode
508 ||' - '||l_dp_det_tab_out(i).effective_date );
509
510 IF(l_dp_det_tab_out(i).effective_date < l_ee_min_dpdate) then
511 l_ee_min_dpdate := l_dp_det_tab_out(i).effective_date;
512 END IF;
513 END LOOP;
514 end if;
515
516 -->>> PHASE 3c: Record dates against asg act if earlier than past record
517 --
518 --Now we have the min dates for this ee, only record them permanently
519 --against this asg act if they're earlier than our current candidate
520
521 if (l_ee_min_dedate < l_aact_min_dedate) then
522 l_aact_min_dedate := l_ee_min_dedate;
523 end if;
524
525 if (l_ee_min_dpdate < l_aact_min_dpdate) then
526 l_aact_min_dpdate := l_ee_min_dpdate;
527 end if;
528
529 END LOOP; -- get next elem entry
530
531
532 -->>> PHASE 4: We are only interested in 'effective dates' and although DATE_PROCESSED
533 --records eff_date in PPE, date_earned does exactly what it says on the tin.
534 --So we now fish out the eff_date corresponding to this date_earned.
535
536 select nvl(min(ppa.effective_date), hr_api.g_eot)
537 into l_aact_min_dedate_eff
538 from pay_payroll_actions ppa,
539 pay_assignment_actions paa,
540 pay_assignment_actions paa_arch
541 where paa_arch.assignment_action_id = p_assactid
542 and paa.assignment_id = paa_arch.assignment_id
543 and paa.payroll_action_id = ppa.payroll_action_id
544 and ppa.action_type in ('R', 'Q')
545 and ppa.date_earned >= l_aact_min_dedate;
546
547 --Finally get the min of our two candidates
548 rundate := least(l_aact_min_dedate_eff,l_aact_min_dpdate);
549
550 -->>> PHASE 5: Get the prepayment and costing dates
551 --
552
553 --Earliest date from PPE
554 open csr_min_date(l_min_date,'COST_CENTRE',l_assignment_id);
555 fetch csr_min_date into cstdate;
556 close csr_min_date;
557
558 open csr_min_date(l_min_date,'PAYMENT',l_assignment_id);
559 fetch csr_min_date into paydate;
560 close csr_min_date;
561
562 -- So Now we have finally got our three driving dates to be used for
563 -- mark for retry (yes, a lot of work for three dates)
564 -- but now we can finally move on to mark for retry, the most important bit.
565
566 hr_utility.trace('+----- Resulting Dates from Interpreter ----+');
567 hr_utility.trace('| for asg act: '||p_assactid);
568 hr_utility.trace('| rundate: '||rundate);
569 hr_utility.trace('| cstdate: '||cstdate);
570 hr_utility.trace('| paydate: '||paydate);
571 hr_utility.trace('+-------------------------------------------+');
572
573
574
575 -->>> PHASE 6: Mark for retry all relevant costings
576 -- nb. Not interested in GRE dates anymore since retropay changes
577 for cstrec in get_costings(p_assactid, cstdate) loop
578 hr_utility.trace('| Rolling Costing back '||cstrec.assignment_action_id);
579 py_rollback_pkg.rollback_ass_action
580 (
581 p_assignment_action_id => cstrec.assignment_action_id,
582 p_rollback_mode => 'RETRY',
583 p_multi_thread => TRUE
584 );
585 end loop;
586
587 -->>> PHASE 7: Mark for retry all relevant prepayments
588 for prerec in get_prepay(p_assactid, paydate) loop
589 hr_utility.trace('| Rolling Prepay back '||prerec.assignment_action_id);
590 py_rollback_pkg.rollback_ass_action
591 (
592 p_assignment_action_id => prerec.assignment_action_id,
593 p_rollback_mode => 'RETRY',
594 p_multi_thread => TRUE
595 );
596 end loop;
597
598 -->>> PHASE 8: Mark for retry all relevant runs
599 for runrec in get_run(p_assactid, rundate) loop
600 for lockrec in get_locked_actions(runrec.assignment_action_id) loop
601 hr_utility.trace('| Rolling locked action back '
602 ||lockrec.assignment_action_id);
603 py_rollback_pkg.rollback_ass_action
604 (
605 p_assignment_action_id => lockrec.assignment_action_id,
606 p_rollback_mode => 'RETRY',
607 p_multi_thread => TRUE
608 );
609 end loop;
610 hr_utility.trace('| Rolling Run back '||runrec.assignment_action_id);
611 py_rollback_pkg.rollback_ass_action
612 (
613 p_assignment_action_id => runrec.assignment_action_id,
614 p_rollback_mode => 'RETRY',
615 p_multi_thread => TRUE,
616 p_grp_multi_thread => TRUE
617 );
618 end loop; --end of runrec
619
620 pay_recorded_requests_pkg.set_recorded_date(
621 p_process => 'CC_ASG',
622 p_recorded_date => l_run_date,
623 p_recorded_date_o => l_min_date,
624 p_attribute1 => l_assignment_id);
625
626 hr_utility.trace('+-------------------------------------------+');
627 hr_utility.set_location(' Leaving: '||l_proc,900);
628
629 END archive_data;
630
631 /* Name : deinitialise
632 Purpose : This procedure simply removes all the actions processed
633 in this run
634 Arguments :
635 Notes :
636 */
637 procedure deinitialise (pactid in number)
638 is
639
640 cursor csr_params(cp_pactid number) is
641 select
642 pay_core_utils.get_parameter('REMOVE_ACT',pa1.legislative_parameters),
643 pay_core_utils.get_parameter('PAYROLL_ID',pa1.legislative_parameters)
644 from pay_payroll_actions pa1
645 where pa1.payroll_action_id = cp_pactid;
646
647 l_remove_act varchar2(10);
648 l_payroll_id number;
649 begin
650
651 -->>> PHASE 1: Get values of temp stored values
652 --
653
654 open csr_params(pactid);
655 fetch csr_params into l_remove_act,l_payroll_id;
656 close csr_params;
657
658
659 -->>> PHASE 2: Remove report actions
660 --
661 if (l_remove_act is null or l_remove_act = 'Y') then
662 pay_archive.remove_report_actions(pactid);
663 end if;
664 --
665
666
667 -- hr_utility.trace('jf store pactid '||pactid);
668 -- hr_utility.trace('jf store new date '||l_new_cc_date_v);
669 -- hr_utility.trace('jf store remove act'||l_remove_act);
670 -- hr_utility.trace('jf store pay id '||l_payroll_id);
671 -- hr_utility.trace('jf store full orig '||l_orig_params);
672
673
674
675 end deinitialise;
676 --
677
678 --
679 procedure generate_trg_data(p_table_name in varchar2,
680 p_eff_str_name in varchar2,
681 p_eff_end_name in varchar2,
682 p_pkg_proc_name in varchar2,
683 p_bg_select in varchar2
684 )
685 is
686 --
687 -- cursor get_columns (p_tab_name in varchar2)
688 -- is
689 -- select substr(column_name, 1, 24) column_name,
690 -- column_name full_column_name,
691 -- data_type
692 -- from all_tab_columns
693 -- where table_name = p_tab_name
694 -- and owner = g_pay_schema
695 -- and column_name not in ('LAST_UPDATE_DATE',
696 -- 'LAST_UPDATED_BY',
697 -- 'LAST_UPDATE_LOGIN',
698 -- 'CREATED_BY',
699 -- 'CREATION_DATE',
700 -- 'OBJECT_VERSION_NUMBER')
701 -- and data_type in ('NUMBER', 'VARCHAR2', 'DATE')
702 -- order by column_name;
703
704
705 -- Bug 15882261 : Modified cursor definition to refer dba_tab_columns
706 -- instead of all_tab_columns
707
708 cursor get_columns (p_tab_name in varchar2)
709 is
710 select substr(tab.column_name, 1, 24) column_name,
711 tab.column_name full_column_name,
712 tab.data_type
713 from dba_tab_columns tab, user_synonyms syn
714 where syn.synonym_name = p_tab_name
715 and tab.table_name = syn.table_name
716 and tab.owner = syn.table_owner
717 and tab.owner = g_pay_schema
718 and tab.column_name not in ('LAST_UPDATE_DATE',
719 'LAST_UPDATED_BY',
720 'LAST_UPDATE_LOGIN',
721 'CREATED_BY',
722 'CREATION_DATE',
723 'OBJECT_VERSION_NUMBER')
724 and tab.data_type in ('NUMBER', 'VARCHAR2', 'DATE')
725 order by column_name;
726 --
727 begin
728 --
729 g_pay_schema := paywsdyg_pkg.get_table_owner(p_table_name);
730 --
731 /* Set up the trigger */
732 pay_dyn_triggers.create_trigger_event(
733 p_table_name||'_ARU',
734 p_table_name,
735 'Continuous Calcuation trigger on update of '||p_table_name,
736 'N',
737 'N',
738 'U',
739 NULL
740 );
741 --
742 /* Setup the business Group */
743 pay_dyn_triggers.create_trg_declaration(
744 p_table_name||'_ARU',
745 'business_group_id',
746 'N',
747 NULL,
748 NULL
749 );
750 --
751 pay_dyn_triggers.create_trg_initialisation(
752 p_table_name||'_ARU',
753 '1',
754 'pay_core_utils.get_business_group',
755 'F',
756 NULL
757 );
758 --
759 pay_dyn_triggers.create_trg_parameter(
760 p_table_name||'_ARU',
761 1,
762 NULL,
763 NULL,
764 NULL,
765 NULL,
766 'I',
767 'R',
768 'business_group_id',
769 'l_business_group_id',
770 'N',
771 NULL
772 );
773 --
774 pay_dyn_triggers.create_trg_parameter(
775 p_table_name||'_ARU',
776 1,
777 NULL,
778 NULL,
779 NULL,
780 NULL,
781 'I',
782 'I',
783 'p_statement',
784 p_bg_select,
785 'N',
786 NULL
787 );
788 --
789 /* Setup the legislation code */
790 pay_dyn_triggers.create_trg_declaration(
791 p_table_name||'_ARU',
792 'legislation_code',
793 'C',
794 10,
795 NULL
796 );
797 --
798 pay_dyn_triggers.create_trg_initialisation(
799 p_table_name||'_ARU',
800 '2',
801 'pay_core_utils.get_legislation_code',
802 'F',
803 NULL
804 );
805 --
806 pay_dyn_triggers.create_trg_parameter(
807 p_table_name||'_ARU',
808 2,
809 NULL,
810 NULL,
811 NULL,
812 NULL,
813 'I',
814 'R',
815 'legislation_code',
816 'l_legislation_code',
817 'N',
818 NULL
819 );
820 --
821 pay_dyn_triggers.create_trg_parameter(
822 p_table_name||'_ARU',
823 2,
824 NULL,
825 NULL,
826 NULL,
827 NULL,
828 'I',
829 'I',
830 'p_bg_id',
831 'l_business_group_id',
832 'N',
833 NULL
834 );
835 --
836 pay_dyn_triggers.create_trg_components(
837 p_table_name||'_ARU',
838 NULL,
839 NULL,
840 NULL,
841 p_pkg_proc_name,
842 'N',
843 NULL
844 );
845 --
846 pay_dyn_triggers.create_trg_parameter(
847 p_table_name||'_ARU',
848 NULL,
849 NULL,
850 NULL,
851 NULL,
852 p_pkg_proc_name,
853 'C',
854 'I',
855 'p_business_group_id',
856 'l_business_group_id',
857 'N',
858 NULL
859 );
860 --
861 pay_dyn_triggers.create_trg_parameter(
862 p_table_name||'_ARU',
863 NULL,
864 NULL,
865 NULL,
866 NULL,
867 p_pkg_proc_name,
868 'C',
869 'I',
870 'p_legislation_code',
871 'l_legislation_code',
872 'N',
873 NULL
874 );
875 --
876 pay_dyn_triggers.create_trg_parameter(
877 p_table_name||'_ARU',
878 NULL,
879 NULL,
880 NULL,
881 NULL,
882 p_pkg_proc_name,
883 'C',
884 'I',
885 'p_effective_date',
886 ':new.effective_start_date',
887 'N',
888 NULL
889 );
890 --
891 for colrec in get_columns(p_table_name) loop
892 pay_dyn_triggers.create_trg_parameter(
893 p_table_name||'_ARU',
894 NULL,
895 NULL,
896 NULL,
897 NULL,
898 p_pkg_proc_name,
899 'C',
900 'I',
901 'p_old_'||colrec.column_name,
902 ':old.'||colrec.full_column_name,
903 'N',
904 NULL
905 );
906 --
907 pay_dyn_triggers.create_trg_parameter(
908 p_table_name||'_ARU',
909 NULL,
910 NULL,
911 NULL,
912 NULL,
913 p_pkg_proc_name,
914 'C',
915 'I',
916 'p_new_'||colrec.column_name,
917 ':new.'||colrec.full_column_name,
918 'N',
919 NULL
920 );
921 --
922 end loop;
923 --
924 end generate_trg_data;
925 --
926 /* Name : generate_upd_trigger
927 Purpose : This procedure is used for as a generator tool for development to
928 create both the DYnamic Trigger code content (which is then stored in
929 PAY_CC_DYT_CODE_PKG) and also the data driven DYT iunformation. Eg populate
930 base tables.
931 The former behaviour is mode 'PROCEDURE', the latter 'TRIGGER DATA'.
932 For more detailed info please see CC White Paper.
933 */
934
935 procedure generate_upd_trigger(p_table_name in varchar2,
936 p_owner in varchar2,
937 p_surr_key_name in varchar2,
938 p_eff_str_name in varchar2,
939 p_eff_end_name in varchar2,
940 p_pkg_proc_name in varchar2 default null,
941 p_bg_select in varchar2 default null,
942 p_mode in varchar2 default 'PROCEDURE')
943 is
944 --
945 cursor dtexists is
946 select dated_table_id, object_version_number
947 from pay_dated_tables pdt
948 where table_name = p_table_name;
949 --
950 found boolean;
951 l_dated_tables_id number;
952 lv_object_version_number number;
953 l_result boolean;
954 l_prod_status varchar2(1);
955 l_industry varchar2(1);
956 l_owner varchar2(30);
957 --
958 begin
959 --
960 if (p_owner is null) then
961 if g_pay_schema is null then
962 l_result := fnd_installation.get_app_info ( 'PAY',
963 l_prod_status,
964 l_industry,
965 g_pay_schema );
966 end if;
967 l_owner := g_pay_schema;
968 else
969 l_owner := p_owner;
970 end if;
971 --
972 if (p_mode = 'PROCEDURE') then
973 --
974 generate_cc_procedure(p_table_name,
975 p_surr_key_name,
976 p_eff_str_name,
977 p_eff_end_name,
978 l_owner
979 );
980 --
981 elsif (p_mode = 'TRIGGER DATA') then
982 --
983 generate_trg_data(p_table_name, p_eff_str_name, p_eff_end_name,
984 p_pkg_proc_name, p_bg_select);
985 --
986 found := FALSE;
987 for getrec in dtexists loop
988 l_dated_tables_id := getrec.dated_table_id;
989 lv_object_version_number := getrec.object_version_number;
990 found := TRUE;
991 end loop;
992 --
993 if (found = FALSE) then
994 pay_dated_tables_api.CREATE_DATED_TABLE(
995 p_table_name => p_table_name
996 , p_application_id => null
997 , p_surrogate_key_name => p_surr_key_name
998 , p_start_date_name => p_eff_str_name
999 , p_end_date_name => p_eff_str_name
1000 , p_business_group_id => null
1001 , p_legislation_code => null
1002 , p_dated_table_id => l_dated_tables_id
1003 , p_object_version_number => lv_object_version_number);
1004 end if;
1005 end if;
1006 --
1007 end generate_upd_trigger;
1008 --
1009 /* Name : generate_cc_procedure
1010 Purpose : This procedure generates a default continuous calc update
1011 procedure for the specified Table. The procedure is
1012 generated into the log file.
1013 Arguments :
1014 Notes :
1015 */
1016 procedure generate_cc_procedure(p_table_name in varchar2,
1017 p_surr_key_name in varchar2,
1018 p_eff_str_name in varchar2,
1019 p_eff_end_name in varchar2,
1020 p_owner in varchar2
1021 )
1022 is
1023 --
1024 -- cursor get_columns (p_tab_name in varchar2,
1025 -- p_start in varchar2,
1026 -- p_end in varchar2,
1027 -- l_owner in varchar2)
1028 -- is
1029 -- select substr(column_name, 1, 24) column_name,
1030 -- column_name full_column_name,
1031 -- data_type
1032 -- from all_tab_columns
1033 -- where table_name = p_tab_name
1034 -- and owner = l_owner
1035 -- and column_name not in ('LAST_UPDATE_DATE',
1036 -- 'LAST_UPDATED_BY',
1037 -- 'LAST_UPDATE_LOGIN',
1038 -- 'CREATED_BY',
1039 -- 'CREATION_DATE',
1040 -- 'OBJECT_VERSION_NUMBER')
1041 -- and data_type in ('NUMBER', 'VARCHAR2', 'DATE')
1042 -- order by decode (column_name, p_start, 3,
1043 -- p_end, 2,
1044 -- 1),
1045 -- column_name;
1046
1047
1048 -- Bug 15882261 : Modified cursor definition to refer dba_tab_columns
1049 -- instead of all_tab_columns
1050
1051 cursor get_columns (p_tab_name in varchar2,
1052 p_start in varchar2,
1053 p_end in varchar2,
1054 l_owner in varchar2)
1055 is
1056 select substr(tab.column_name, 1, 24) column_name,
1057 tab.column_name full_column_name,
1058 tab.data_type
1059 from dba_tab_columns tab, user_synonyms syn
1060 where syn.synonym_name = p_tab_name
1061 and tab.table_name = syn.table_name
1062 and tab.owner = syn.table_owner
1063 and tab.owner = l_owner
1064 and tab.column_name not in ('LAST_UPDATE_DATE',
1065 'LAST_UPDATED_BY',
1066 'LAST_UPDATE_LOGIN',
1067 'CREATED_BY',
1068 'CREATION_DATE',
1069 'OBJECT_VERSION_NUMBER')
1070 and tab.data_type in ('NUMBER', 'VARCHAR2', 'DATE')
1071 order by decode (column_name, p_start, 3,
1072 p_end, 2,
1073 1),
1074 column_name;
1075 --
1076 proc varchar2(32767);
1077 l_result boolean;
1078 l_prod_status varchar2(1);
1079 l_industry varchar2(1);
1080 l_owner varchar2(30);
1081 l_eff_str_up varchar2(40);
1082 l_eff_str_low varchar2(40);
1083 l_eff_end_up varchar2(40);
1084 l_eff_end_low varchar2(40);
1085 begin
1086 --
1087 /* OK put trace on */
1088 hr_utility.trace_on(null, p_table_name);
1089 --
1090 if (p_owner is null) then
1091 g_pay_schema := paywsdyg_pkg.get_table_owner(p_table_name);
1092 l_owner := g_pay_schema;
1093 else
1094 if paywsdyg_pkg.is_table_owner_valid(p_table_name,p_owner) = 'N' then
1095 hr_utility.trace('-- WARNING: owner '||p_owner||' is not valid for table '||p_table_name);
1096 end if;
1097 l_owner := p_owner;
1098 end if;
1099 --
1100 l_eff_str_up := upper(p_eff_str_name);
1101 l_eff_str_low := lower(p_eff_str_name);
1102 l_eff_end_up := upper(p_eff_end_name);
1103 l_eff_end_low := lower(p_eff_end_name);
1104 --
1105 proc := 'procedure '||p_table_name||'_aru(
1106 p_business_group_id in number,
1107 p_legislation_code in varchar2,
1108 p_effective_date in date';
1109 --
1110 hr_utility.trace(proc);
1111 for colrec in get_columns(p_table_name, l_eff_str_up, l_eff_end_up, l_owner) loop
1112 proc := ',
1113 ';
1114 proc := proc||'p_old_'||colrec.column_name||' in '||colrec.data_type||',
1115 ';
1116 proc := proc||'p_new_'||colrec.column_name||' in '||colrec.data_type;
1117 --
1118 hr_utility.trace(proc);
1119 end loop;
1120 --
1121 proc := '
1122 )
1123 is
1124 --
1125 begin
1126 /* If the continuous calc is overriden then do nothing */
1127 if (pay_continuous_calc.g_override_cc = TRUE) then
1128 return;
1129 end if;
1130 --
1131 /* If the dates havent changed it must be a correction */
1132 if (p_old_'||l_eff_end_up||' = p_new_'||l_eff_end_up||'
1133 and p_old_'||l_eff_str_up||' = p_new_'||l_eff_str_up||') then';
1134 --
1135 hr_utility.trace(proc);
1136 for colrec in get_columns(p_table_name, l_eff_str_up, l_eff_end_up, l_owner) loop
1137 --
1138 if (colrec.column_name = l_eff_end_up) then
1139 proc := proc||'
1140 else
1141 /* OK it must be a date track change */';
1142 hr_utility.trace(proc);
1143 end if;
1144 --
1145 proc := '--
1146 pay_continuous_calc.event_update(p_business_group_id,
1147 p_legislation_code,
1148 '''||p_table_name||''',
1149 '''||colrec.full_column_name||''',
1150 p_old_'||colrec.column_name||',
1151 p_new_'||colrec.column_name||',';
1152 if (colrec.column_name = l_eff_str_up) then
1153 proc := proc||'
1154 p_new_'||l_eff_str_low||',
1155 least(p_old_'||l_eff_str_low||',
1156 p_new_'||l_eff_str_low||')';
1157 else
1158 if (colrec.column_name = l_eff_end_up) then
1159 proc := proc||'
1160 p_new_'||l_eff_end_low||',
1161 least(p_old_'||l_eff_end_low||',
1162 p_new_'||l_eff_end_low||')';
1163 else
1164 proc := proc||'
1165 p_effective_date';
1166 end if;
1167 end if;
1168 --
1169 proc := proc||'
1170 );';
1171 hr_utility.trace(proc);
1172 end loop;
1173 proc := '
1174 end if;
1175 --
1176 /* Now call the API for the affected assignments */
1177 declare
1178 l_process_event_id number;
1179 l_object_version_number number;
1180 cnt number;
1181 begin
1182 if (pay_continuous_calc.g_event_list.sz <> 0) then
1183 for cnt in 1..pay_continuous_calc.g_event_list.sz loop
1184 pay_ppe_api.create_process_event(
1185 p_assignment_id => p_assignment_id?,
1186 p_effective_date => pay_continuous_calc.g_event_list.effective_date(cnt),
1187 p_change_type => pay_continuous_calc.g_event_list.change_type(cnt),
1188 p_status => ''U'',
1189 p_description => pay_continuous_calc.g_event_list.description(cnt),
1190 p_process_event_id => l_process_event_id,
1191 p_object_version_number => l_object_version_number,
1192 p_event_update_id => pay_continuous_calc.g_event_list.event_update_id(cnt),
1193 p_business_group_id => p_business_group_id,
1194 p_calculation_date => pay_continuous_calc.g_event_list.calc_date(cnt),
1195 p_surrogate_key => p_new_'||lower(p_surr_key_name)||'
1196 );
1197 end loop;
1198 end if;
1199 pay_continuous_calc.g_event_list.sz := 0;
1200 end;
1201 --
1202 end '||p_table_name||'_aru;';
1203 --
1204 hr_utility.trace(proc);
1205 end generate_cc_procedure;
1206 --
1207
1208 --
1209 /* Name : get_asg_act_status
1210 Purpose : This function returns whether a asg_act has been modified
1211 By checking the results of PPE. The crucial point is establishing
1212 the date the CC process was run for the payroll and making sure
1213 more recent changes exist.
1214 Similar copy of code in pyasa01t, removing redundant status restriction
1215 on cursors
1216 Arguments :
1217 Notes :
1218 */
1219 FUNCTION get_asg_act_status( p_assignment_action_id in number,
1220 p_action_type in varchar2,
1221 p_action_status in varchar2) return varchar2
1222 is
1223
1224 l_payroll_id number;
1225 l_assignment_id number;
1226 l_date date;
1227
1228 l_dummy_action_id pay_assignment_actions.assignment_action_id%type ;
1229 ischanged boolean;
1230
1231 cursor get_payroll (cp_asg_act_id in number) is
1232 select ppa.payroll_id
1233 from
1234 pay_payroll_actions ppa,
1235 pay_assignment_actions paa
1236 where paa.assignment_action_id = cp_asg_act_id
1237 and paa.payroll_action_id = ppa.payroll_action_id;
1238
1239 cursor get_assignment_id (cp_asg_act_id in number) is
1240 select assignment_id
1241 from pay_assignment_actions paa
1242 where paa.assignment_action_id = cp_asg_act_id;
1243
1244 --
1245 -- A given assignment action is void if there is a payroll action of type 'D'
1246 -- locks ( though PAY_ACTION_INTERLOCKS ) the assignment action.
1247 -- Note that this cursor does not check whether the void assignment action has
1248 -- a status of complete
1249 --
1250 cursor c_is_voided ( p_assignment_action_id in number ) is
1251 select intloc.locking_action_id
1252 from pay_assignment_actions assact,
1253 pay_action_interlocks intloc,
1254 pay_payroll_actions pact
1255 where intloc.locked_action_id = p_assignment_action_id
1256 and intloc.locking_action_id = assact.assignment_action_id
1257 and assact.payroll_action_id = pact.payroll_action_id
1258 and pact.action_type = 'D';
1259 --
1260 cursor run_modified (p_assignment_action_id in number,
1261 cp_last_cc_run_date in date ) is
1262 select paa.assignment_action_id
1263 from
1264 pay_payroll_actions ppa,
1265 pay_assignment_actions paa
1266 where paa.assignment_action_id = p_assignment_action_id
1267 and paa.payroll_action_id = ppa.payroll_action_id
1268 and paa.action_status = 'C'
1269 and exists (select ''
1270 from pay_process_events ppe
1271 where ppe.assignment_id = paa.assignment_id
1272 and ppe.change_type in ('GRE', 'DATE_EARNED', 'DATE_PROCESSED')
1273 and ppe.creation_date > cp_last_cc_run_date
1274 and ppe.effective_date <= nvl(ppa.date_earned,ppa.effective_date)
1275 )
1276 and not exists (select ''
1277 from pay_assignment_actions paa1, -- Prepay/Costing
1278 pay_action_interlocks pai1,
1279 pay_assignment_actions paa2,-- Payment/Trans GL
1280 pay_action_interlocks pai2
1281 where pai1.locked_action_id = paa.assignment_action_id
1282 and pai1.locking_action_id = paa1.assignment_action_id
1283 and pai2.locked_action_id = paa1.assignment_action_id
1284 and pai2.locking_action_id = paa2.assignment_action_id);
1285 --
1286 cursor prepay_modified (p_assignment_action_id in number,
1287 cp_last_cc_run_date in date ) is
1288 select paa.assignment_action_id
1289 from
1290 pay_payroll_actions ppa,
1291 pay_assignment_actions paa
1292 where paa.assignment_action_id = p_assignment_action_id
1293 and paa.payroll_action_id = ppa.payroll_action_id
1294 and paa.action_status = 'C'
1295 and not exists (select ''
1296 from pay_assignment_actions paa1, -- Payment/Trans GL
1297 pay_action_interlocks pai1
1298 where pai1.locked_action_id = paa.assignment_action_id
1299 and pai1.locking_action_id = paa1.assignment_action_id)
1300 and (exists (select ''
1301 from pay_process_events ppe
1302 where ppe.assignment_id = paa.assignment_id
1303 and ppe.effective_date < ppa.effective_date
1304 and ppe.change_type in ('PAYMENT')
1305 and ppe.creation_date > cp_last_cc_run_date
1306 )
1307 or
1308 exists (select ''
1309 from pay_action_interlocks pai,
1310 pay_assignment_actions paa2,
1311 pay_payroll_actions ppa2
1312 where pai.locking_action_id = paa.assignment_action_id
1313 and pai.locked_action_id = paa2.assignment_action_id
1314 and paa2.payroll_action_id = ppa2.payroll_action_id
1315 and ppa2.action_type in ('R','Q')
1316 and exists (select ''
1317 from pay_process_events ppe
1318 where ppe.assignment_id = paa2.assignment_id
1319 and ppe.effective_date < ppa2.effective_date
1320 and ppe.change_type in ('GRE', 'DATE_EARNED', 'DATE_PROCESSED')
1321 and ppe.creation_date > cp_last_cc_run_date
1322 )
1323 )
1324 );
1325 --
1326 cursor cost_modified (p_assignment_action_id in number,
1327 cp_last_cc_run_date in date ) is
1328 select paa.assignment_action_id
1329 from
1330 pay_payroll_actions ppa,
1331 pay_assignment_actions paa
1332 where paa.assignment_action_id = p_assignment_action_id
1333 and paa.payroll_action_id = ppa.payroll_action_id
1334 and paa.action_status = 'C'
1335 and not exists (select ''
1336 from pay_assignment_actions paa1, -- Payment/Trans GL
1337 pay_action_interlocks pai1
1338 where pai1.locked_action_id = paa.assignment_action_id
1339 and pai1.locking_action_id = paa1.assignment_action_id)
1340 and exists (select ''
1341 from pay_process_events ppe
1342 where ppe.assignment_id = paa.assignment_id
1343 and ppe.effective_date < ppa.effective_date
1344 and ppe.change_type in ('COST_CENTRE')
1345 and ppe.creation_date > cp_last_cc_run_date
1346 )
1347 and exists (select ''
1348 from pay_action_interlocks pai,
1349 pay_assignment_actions paa2,
1350 pay_payroll_actions ppa2
1351 where pai.locking_action_id = paa.assignment_action_id
1352 and pai.locked_action_id = paa2.assignment_action_id
1353 and paa2.payroll_action_id = ppa2.payroll_action_id
1354 and ppa2.action_type in ('R','Q')
1355 and exists (select ''
1356 from pay_process_events ppe
1357 where ppe.assignment_id = paa2.assignment_id
1358 and ppe.effective_date < ppa2.effective_date
1359 and ppe.change_type in ('GRE', 'DATE_EARNED', 'DATE_PROCESSED')
1360 and ppe.creation_date > cp_last_cc_run_date
1361 )
1362 );
1363 --
1364 --
1365 l_return_value hr_lookups.meaning%type ;
1366 l_proc varchar2(80) := g_pkg||'.get_asg_act_status';
1367
1368 BEGIN
1369 hr_utility.set_location(l_proc,10);
1370
1371 --
1372 ischanged := FALSE;
1373 --
1374 --Get assignment_id for this asg_act_id
1375 --
1376 open get_assignment_id (p_assignment_action_id);
1377 fetch get_assignment_id into l_assignment_id;
1378 close get_assignment_id;
1379 hr_utility.trace('-assignment_id: '||l_assignment_id);
1380 --Get date CC was last executed
1381 --
1382 -- As highlighted in bug 3146928
1383 -- This function is used in a view and thus no dml can occur, so call new proc
1384 --
1385 PAY_RECORDED_REQUESTS_PKG.get_recorded_date_no_ins('CC_ASG',l_date,l_assignment_id);
1386 hr_utility.trace('-last CC run date is '||l_date);
1387
1388 --
1389 -- bug 3265814
1390 --If looks like CC not in use then dont bother to look for modified
1391 --better to have global payroll level switch, but now compare date
1392 if (l_date = hr_api.g_sot) then
1393 -- l_return_value := hr_general.decode_lookup('ACTION_STATUS',p_action_status ) ;
1394 -- Bug 3576520: Repeating the special case for the Cheque Writer.
1395 if ( p_action_type in ('M', 'H' )) then
1396 open c_is_voided( p_assignment_action_id ) ;
1397 fetch c_is_voided into l_dummy_action_id ;
1398 if c_is_voided%found then
1399 l_return_value := hr_general.decode_lookup('ACTION_STATUS','V');
1400 hr_utility.set_location(l_proc,50);
1401 else
1402 l_return_value := hr_general.decode_lookup('ACTION_STATUS',p_action_status ) ;
1403 hr_utility.set_location(l_proc,55);
1404 end if;
1405 close c_is_voided ;
1406 else
1407 l_return_value := hr_general.decode_lookup('ACTION_STATUS',p_action_status ) ;
1408 end if;
1409 --
1410 else
1411 if ( p_action_type in ('R', 'Q')) then
1412 --
1413 ischanged := FALSE;
1414 --
1415 -- Check Run change.
1416 open run_modified( p_assignment_action_id, l_date );
1417 fetch run_modified into l_dummy_action_id ;
1418 if run_modified%found then
1419 ischanged := TRUE;
1420 end if;
1421 close run_modified ;
1422 --
1423 if (ischanged) then
1424 l_return_value := hr_general.decode_lookup('ACTION_STATUS','MO');
1425 hr_utility.set_location(l_proc,20);
1426 else
1427 l_return_value := hr_general.decode_lookup('ACTION_STATUS',p_action_status ) ;
1428 hr_utility.set_location(l_proc,25);
1429 end if;
1430 --
1431 elsif ( p_action_type in ('P', 'U')) then
1432 --
1433 ischanged := FALSE;
1434 --
1435 -- Check Prepay change.
1436 open prepay_modified( p_assignment_action_id, l_date );
1437 fetch prepay_modified into l_dummy_action_id ;
1438 if prepay_modified%found then
1439 ischanged := TRUE;
1440 end if;
1441 close prepay_modified ;
1442 --
1443 if (ischanged) then
1444 l_return_value := hr_general.decode_lookup('ACTION_STATUS','MO');
1445 hr_utility.set_location(l_proc,30);
1446 else
1447 l_return_value := hr_general.decode_lookup('ACTION_STATUS',p_action_status ) ;
1448 hr_utility.set_location(l_proc,35);
1449 end if;
1450 --
1451 elsif ( p_action_type = 'C') then
1452 --
1453 ischanged := FALSE;
1454 --
1455 -- Check Costing change.
1456 open cost_modified( p_assignment_action_id, l_date );
1457 fetch cost_modified into l_dummy_action_id ;
1458 if cost_modified%found then
1459 ischanged := TRUE;
1460 end if;
1461 close cost_modified ;
1462 --
1463 if (ischanged) then
1464 l_return_value := hr_general.decode_lookup('ACTION_STATUS','MO');
1465 hr_utility.set_location(l_proc,40);
1466 else
1467 l_return_value := hr_general.decode_lookup('ACTION_STATUS',p_action_status ) ;
1468 hr_utility.set_location(l_proc,45);
1469 end if;
1470 --
1471 --
1472 elsif ( p_action_type = 'H' ) then
1473 open c_is_voided( p_assignment_action_id ) ;
1474 fetch c_is_voided into l_dummy_action_id ;
1475 if c_is_voided%found then
1476 l_return_value := hr_general.decode_lookup('ACTION_STATUS','V');
1477 hr_utility.set_location(l_proc,50);
1478 else
1479 l_return_value := hr_general.decode_lookup('ACTION_STATUS',p_action_status ) ;
1480 hr_utility.set_location(l_proc,55);
1481 end if;
1482 close c_is_voided ;
1483 else
1484 l_return_value := hr_general.decode_lookup('ACTION_STATUS',p_action_status ) ;
1485 end if;
1486 end if;
1487 hr_utility.set_location(l_proc,900);
1488 return ( l_return_value ) ;
1489 end get_asg_act_status ;
1490
1491
1492 /* Name : generate_upd_script
1493 Purpose : This procedure generates a the upload script to load the
1494 trigger definition of a generated cc procedure into the
1495 dynamic trigger tables.
1496 Arguments :
1497 Notes :
1498 */
1499 procedure generate_upd_script(p_table_name in varchar2
1500 )
1501 is
1502 --
1503 -- cursor get_columns (p_tab_name in varchar2)
1504 -- is
1505 -- select substr(column_name, 1, 24) column_name,
1506 -- column_name full_column_name,
1507 -- data_type
1508 -- from all_tab_columns
1509 -- where table_name = p_tab_name
1510 -- and owner = g_pay_schema
1511 -- and column_name not in ('LAST_UPDATE_DATE',
1512 -- 'LAST_UPDATED_BY',
1513 -- 'LAST_UPDATE_LOGIN',
1514 -- 'CREATED_BY',
1515 -- 'CREATION_DATE',
1516 -- 'OBJECT_VERSION_NUMBER')
1517 -- and data_type in ('NUMBER', 'VARCHAR2', 'DATE')
1518 -- order by column_name;
1519
1520 -- Bug 15882261 : Modified cursor definition to refer dba_tab_columns
1521 -- instead of all_tab_columns
1522
1523 cursor get_columns (p_tab_name in varchar2)
1524 is
1525 select substr(tab.column_name, 1, 24) column_name,
1526 tab.column_name full_column_name,
1527 tab.data_type
1528 from dba_tab_columns tab, user_synonyms syn
1529 where syn.synonym_name = p_tab_name
1530 and tab.table_name = syn.table_name
1531 and tab.owner = syn.table_owner
1532 and tab.owner = g_pay_schema
1533 and tab.column_name not in ('LAST_UPDATE_DATE',
1534 'LAST_UPDATED_BY',
1535 'LAST_UPDATE_LOGIN',
1536 'CREATED_BY',
1537 'CREATION_DATE',
1538 'OBJECT_VERSION_NUMBER')
1539 and tab.data_type in ('NUMBER', 'VARCHAR2', 'DATE')
1540 order by column_name;
1541 --
1542 proc varchar2(32767);
1543 l_result boolean;
1544 l_prod_status varchar2(1);
1545 l_industry varchar2(1);
1546 --
1547 begin
1548 /* OK put trace on */
1549 hr_utility.trace_on(null, p_table_name);
1550 --
1551 g_pay_schema := paywsdyg_pkg.get_table_owner(p_table_name);
1552 --
1553 proc := ' pay_dyn_triggers.create_trigger_event(
1554 '''||p_table_name||'_ARU'',
1555 '''||p_table_name||''',
1556 ''Description'',
1557 ''N'',
1558 ''N'',
1559 ''U'',
1560 NULL
1561 );
1562 --
1563 ';
1564 --
1565 hr_utility.trace(proc);
1566 --
1567 proc := ' pay_dyn_triggers.create_trg_declaration(
1568 '''||p_table_name||'_ARU'',
1569 ''business_group_id'',
1570 ''N'',
1571 NULL,
1572 NULL
1573 );
1574 --
1575 ';
1576 hr_utility.trace(proc);
1577 --
1578 proc := ' pay_dyn_triggers.create_trg_initialisation(
1579 '''||p_table_name||'_ARU'',
1580 ''1'',
1581 ''pay_core_utils.get_business_group'',
1582 ''F'',
1583 sysdate,
1584 NULL
1585 );
1586 --
1587 ';
1588 hr_utility.trace(proc);
1589 --
1590 proc := ' pay_dyn_triggers.create_trg_parameter(
1591 '''||p_table_name||'_ARU'',
1592 1,
1593 NULL,
1594 NULL,
1595 NULL,
1596 NULL,
1597 ''I'',
1598 ''R'',
1599 ''business_group_id'',
1600 ''l_business_group_id'',
1601 ''N'',
1602 NULL
1603 );
1604 --
1605 ';
1606 hr_utility.trace(proc);
1607 --
1608 proc := ' pay_dyn_triggers.create_trg_parameter(
1609 '''||p_table_name||'_ARU'',
1610 1,
1611 NULL,
1612 NULL,
1613 NULL,
1614 NULL,
1615 ''I'',
1616 ''I'',
1617 ''p_statement'',
1618 ''''''select statement'''''',
1619 ''N'',
1620 NULL
1621 );
1622 --
1623 ';
1624 hr_utility.trace(proc);
1625 --
1626 proc := ' pay_dyn_triggers.create_trg_declaration(
1627 '''||p_table_name||'_ARU'',
1628 ''legislation_code'',
1629 ''C'',
1630 10,
1631 NULL
1632 );
1633 --
1634 ';
1635 hr_utility.trace(proc);
1636 --
1637 proc := ' pay_dyn_triggers.create_trg_initialisation(
1638 '''||p_table_name||'_ARU'',
1639 ''2'',
1640 ''pay_core_utils.get_legislation_code'',
1641 ''F'',
1642 sysdate,
1643 NULL
1644 );
1645 --
1646 ';
1647 hr_utility.trace(proc);
1648 --
1649 proc := ' pay_dyn_triggers.create_trg_parameter(
1650 '''||p_table_name||'_ARU'',
1651 2,
1652 NULL,
1653 NULL,
1654 NULL,
1655 NULL,
1656 ''I'',
1657 ''R'',
1658 ''legislation_code'',
1659 ''l_legislation_code'',
1660 ''N'',
1661 NULL
1662 );
1663 --
1664 ';
1665 hr_utility.trace(proc);
1666 --
1667 proc := ' pay_dyn_triggers.create_trg_parameter(
1668 '''||p_table_name||'_ARU'',
1669 2,
1670 NULL,
1671 NULL,
1672 NULL,
1673 NULL,
1674 ''I'',
1675 ''I'',
1676 ''p_bg_id'',
1677 ''l_business_group_id'',
1678 ''N'',
1679 NULL
1680 );
1681 --
1682 ';
1683 hr_utility.trace(proc);
1684 --
1685 proc := ' pay_dyn_triggers.create_trg_components(
1686 '''||p_table_name||'_ARU'',
1687 NULL,
1688 NULL,
1689 NULL,
1690 ''package.procedure'',
1691 ''N'',
1692 sysdate,
1693 NULL
1694 );
1695 --
1696 ';
1697 hr_utility.trace(proc);
1698 --
1699 proc := ' pay_dyn_triggers.create_trg_parameter(
1700 '''||p_table_name||'_ARU'',
1701 NULL,
1702 NULL,
1703 NULL,
1704 NULL,
1705 ''package.procedure'',
1706 ''C'',
1707 ''I'',
1708 ''p_business_group_id'',
1709 ''l_business_group_id'',
1710 ''N'',
1711 NULL
1712 );
1713 --
1714 ';
1715 hr_utility.trace(proc);
1716 --
1717 proc := ' pay_dyn_triggers.create_trg_parameter(
1718 '''||p_table_name||'_ARU'',
1719 NULL,
1720 NULL,
1721 NULL,
1722 NULL,
1723 ''package.procedure'',
1724 ''C'',
1725 ''I'',
1726 ''p_legislation_code'',
1727 ''l_legislation_code'',
1728 ''N'',
1729 NULL
1730 );
1731 --
1732 ';
1733 hr_utility.trace(proc);
1734 --
1735 proc := ' pay_dyn_triggers.create_trg_parameter(
1736 '''||p_table_name||'_ARU'',
1737 NULL,
1738 NULL,
1739 NULL,
1740 NULL,
1741 ''package.procedure'',
1742 ''C'',
1743 ''I'',
1744 ''p_effective_date'',
1745 '':new.effective_start_date'',
1746 ''N'',
1747 NULL
1748 );
1749 --
1750 ';
1751 hr_utility.trace(proc);
1752 --
1753 for colrec in get_columns(p_table_name) loop
1754 proc := ' pay_dyn_triggers.create_trg_parameter(
1755 '''||p_table_name||'_ARU'',
1756 NULL,
1757 NULL,
1758 NULL,
1759 NULL,
1760 ''package.procedure'',
1761 ''C'',
1762 ''I'',
1763 ''p_old_'||colrec.column_name||''',
1764 '':old.'||colrec.column_name||''',
1765 ''N'',
1766 NULL
1767 );
1768 --
1769 ';
1770 hr_utility.trace(proc);
1771 --
1772 proc := ' pay_dyn_triggers.create_trg_parameter(
1773 '''||p_table_name||'_ARU'',
1774 NULL,
1775 NULL,
1776 NULL,
1777 NULL,
1778 ''package.procedure'',
1779 ''C'',
1780 ''I'',
1781 ''p_new_'||colrec.column_name||''',
1782 '':new.'||colrec.column_name||''',
1783 ''N'',
1784 NULL
1785 );
1786 --
1787 ';
1788 hr_utility.trace(proc);
1789 end loop;
1790 --
1791 end generate_upd_script;
1792 --
1793
1794 /* Name : generate_dyt_pkg_behaviour
1795 Purpose : This procedure is used as a generator tool for development to
1796 alter the stored definitions of the Dynamic Trigger data such
1797 that the DYT wrapper code will now be held in a package rather than
1798 as explicit database triggers.
1799 For more information see the CC White Paper.
1800 Paramaters:
1801 + p_table_name -The dated table in question
1802 + p_tab_rki_pkg -The package containing the user hook information.
1803 Usually this is defined in the row handler file for this table.
1804 This is needed so we can look up the paramater listing as we require
1805 the same list. Similarly for the after_update and after_delete
1806 (...rku_pkg, ...rkd_pkg)
1807 Prerequisites: The DYT must have been created in the old-skool manner,
1808 using the generate_upd_trigger in mode PROCEDURE then mode TRIGGER DATA
1809 The former creates code that should be edited and then placed in
1810 pay_cc_dyt_code_pkg.
1811 */
1812
1813 PROCEDURE GENERATE_DYT_PKG_BEHAVIOUR(p_table_name in varchar2,
1814 p_tab_rki_pkg in varchar2,
1815 p_tab_rku_pkg in varchar2,
1816 p_tab_rkd_pkg in varchar2 ) IS
1817
1818 --Include all control parameters, under the assumption that the finished
1819 --rows will be manually edited, usually removing some of these
1820
1821 cursor csr_args (cp_rki varchar2, cp_rku varchar2, cp_rkd varchar2)
1822 is
1823 SELECT a.argument value_name, a.procedure$ proc_name
1824 FROM SYS.ARGUMENT$ A,
1825 USER_OBJECTS B
1826 WHERE A.OBJ# = B.OBJECT_ID
1827 AND B.OBJECT_NAME in (CP_RKI,CP_RKU,CP_RKD)
1828 AND A.LEVEL# = 0
1829 --AND a.argument not in ('P_VALIDATE',
1830 -- 'P_EFFECTIVE_DATE',
1831 -- 'P_DATETRACK_UPDATE_MODE',
1832 -- 'P_DATETRACK_DELETE_MODE',
1833 -- 'P_VALIDATION_START_DATE',
1834 -- 'P_VALIDATION_END_DATE',
1835 -- 'P_LANGUAGE_CODE')
1836 ORDER BY a.procedure$;
1837
1838 l_prefix varchar2(15); --Local Form prefix
1839 l_o varchar2(15) := ':old.'; --Old Style Local Form prefix
1840 l_n varchar2(15) := ':new.'; --Old Style Local Form prefix
1841
1842 l_pkg_name varchar2(80) ;
1843 l_local_form varchar2(80);
1844
1845 l_usage_type varchar2(15);
1846 l_dated_table_id number;
1847 l_app_id number;
1848
1849 BEGIN
1850 l_pkg_name := p_table_name;
1851
1852 select pdt.dated_table_id
1853 into l_dated_table_id
1854 from pay_dated_tables pdt
1855 where pdt.table_name = p_table_name;
1856
1857 -- >>> PHASE 1: Create the dyt_pkg name based on table_name
1858 --
1859 --Remove _f if exists and replace with _pkg
1860 --
1861 if ( upper(substr(p_table_name,length(p_table_name) - 1, 2)) = '_F' ) then
1862 l_pkg_name := substr(p_table_name,1,length(p_table_name)-2);
1863 end if;
1864
1865 --Insert _dyt after first underscore, nb overwriting owner prefix to PAY
1866 --
1867 l_pkg_name :='PAY_' --substr(l_pkg_name,1,instr(l_pkg_name,'_'))
1868 ||'DYT'
1869 ||substr(l_pkg_name,instr(l_pkg_name,'_'),18)--max 30chars for full pkg
1870 ||'_PKG';
1871 --Get application
1872 if (substr(p_table_name,1,instr(p_table_name,'_')-1) = 'PAY') then
1873 l_app_id := 801;
1874 elsif (substr(p_table_name,1,instr(p_table_name,'_')-1) = 'PER') then
1875 l_app_id := 800;
1876 elsif (substr(p_table_name,1,instr(p_table_name,'_')-1) = 'PQH') then
1877 l_app_id := 8302;
1878 end if;
1879
1880 -- >>> PHASE 2: Set the dated table to have dyt in package
1881 --
1882
1883 update pay_dated_tables pdt
1884 set application_id = l_app_id,
1885 dyn_trigger_type = 'P',
1886 dyn_trigger_package_name = l_pkg_name,
1887 dyn_trig_pkg_generated = 'N'
1888 where pdt.table_name = p_table_name;
1889
1890 -- >>> PHASE 3: Create the parameter mappings for dbtrigs to pkg procedure
1891 --
1892 --get the parameters that have been created for the row handler user hook pkg.
1893 --This generated file has all the parameters that we will also have to create mappings for.
1894 FOR args_rec in csr_args(p_tab_rki_pkg,p_tab_rku_pkg,p_tab_rkd_pkg) LOOP
1895 -- if _o then old style (and remove _o) else new style
1896 --
1897 if ( upper(substr(args_rec.value_name,length(args_rec.value_name) - 1, 2)) = '_O' ) then
1898 l_local_form := substr(args_rec.value_name,1,length(args_rec.value_name)-2);
1899 l_prefix := l_o;
1900 else
1901 l_local_form := args_rec.value_name;
1902 l_prefix := l_n;
1903 end if;
1904
1905 -- if first 2 chars are p_ (which we expect is them all) strip it out
1906 --
1907 if ( upper(substr(l_local_form,1, 2)) = 'P_' ) then
1908 l_local_form := substr(l_local_form,3);
1909 end if;
1910 -- add our prefix
1911 l_local_form := l_prefix||l_local_form;
1912
1913 l_usage_type := 'P'||substr(args_rec.proc_name,7,1);
1914 --dbms_output.put_line(l_dated_table_id||' Insert a '||l_usage_type||' val_name: '||args_rec.value_name||' local form: '||l_local_form);
1915
1916
1917 -- Create rows in trigger_parameters
1918 --
1919 pay_dyn_triggers.create_trg_parameter (
1920 p_short_name => p_table_name,
1921 p_process_order => null,
1922 p_legislative_code => null,
1923 p_business_group => null,
1924 p_payroll_name => null,
1925 p_module_name => null,
1926 p_usage_type => l_usage_type,
1927 p_parameter_type => 'I', --All param are INs 'cos all hook params are INs
1928 p_parameter_name => l_local_form,
1929 p_value_name => args_rec.value_name,
1930 p_automatic => 'Y',
1931 p_owner => null
1932 );
1933
1934 END LOOP;
1935
1936
1937 END GENERATE_DYT_PKG_BEHAVIOUR;
1938
1939 /*
1940 Revert back away from the dyt_pkg behaviour. Intended as development util only.
1941 Obsoleted please see paywsdyg_pkg.convert_tab_style
1942 */
1943 PROCEDURE DROP_DYT_PKG_BEHAVIOUR(p_table_name in varchar2) IS
1944
1945 BEGIN
1946 -- >>> PHASE 1: Set the dated table to have old-skool dyt
1947 --
1948 update pay_dated_tables pdt
1949 set dyn_trigger_type = 'T',
1950 dyn_trigger_package_name = null,
1951 dyn_trig_pkg_generated = null
1952 where pdt.table_name = p_table_name;
1953
1954 -- >>> PHASE 2: Junk all parameters
1955 --
1956 DELETE
1957 FROM pay_trigger_parameters ptp
1958 WHERE ptp.usage_id = (select dated_table_id
1959 from pay_dated_tables pdt
1960 where pdt.table_name = p_table_name)
1961 AND ptp.usage_type in ('PI','PU','PD');
1962
1963 END DROP_DYT_PKG_BEHAVIOUR;
1964
1965 procedure set_req_dates_for_run(p_process in varchar2,
1966 p_asg_id in number,
1967 p_sysdate in date,
1968 p_assact_id in number)
1969 is
1970 cursor get_min_dates(p_asg_id number, p_cca_date date, p_sysdate date)
1971 is
1972 select min(effective_date) effective_date,
1973 change_type
1974 from pay_process_events
1975 where assignment_id = p_asg_id
1976 and creation_date between p_cca_date
1977 and p_sysdate
1978 and change_type in ('DATE_PROCESSED', 'DATE_EARNED')
1979 group by change_type
1980 order by change_type desc;
1981
1982 cursor get_group_events(p_cca_date date, p_sysdate date) is
1983 select pdt.table_name,ppe.surrogate_key
1984 from pay_process_events ppe,
1985 pay_event_updates peu,
1986 pay_dated_tables pdt
1987 where ppe.assignment_id is null
1988 and ppe.creation_date between p_cca_date
1989 and p_sysdate
1990 AND ppe.change_type in ('DATE_PROCESSED', 'DATE_EARNED')
1991 and peu.event_update_id = ppe.event_update_id
1992 and peu.dated_table_id = pdt.dated_table_id
1993 and pdt.table_name in ('PAY_GRADE_RULES_F','PQH_RATE_MATRIX_RATES_F','FF_GLOBALS_F','PAY_USER_COLUMN_INSTANCES_F'); /*Added for Bug 8302596 */
1994
1995 l_effective_date date;
1996 l_change_type pay_process_events.change_type%type;
1997 l_cca_date date;
1998 run_counts number;
1999 l_update_cc_date boolean;
2000 new_cc_date date;
2001 old_cc_date date;
2002 l_table_name pay_dated_tables.table_name%type;
2003 l_surrogate_key pay_process_events.surrogate_key%type;
2004 l_grp_event_valid varchar2(5);
2005
2006 begin
2007
2008 pay_recorded_requests_pkg.get_recorded_date(
2009 p_process => p_process,
2010 p_recorded_date => l_cca_date,
2011 p_attribute1 => p_asg_id);
2012
2013 l_update_cc_date := TRUE;
2014 open get_min_dates(p_asg_id,
2015 l_cca_date,
2016 p_sysdate);
2017 fetch get_min_dates into l_effective_date, l_change_type;
2018
2019 while (get_min_dates%notfound = FALSE
2020 and l_update_cc_date = TRUE) loop
2021
2022 if (l_change_type = 'DATE_PROCESSED') then
2023
2024 select count(*)
2025 into run_counts
2026 from pay_payroll_actions ppa,
2027 pay_assignment_actions paa,
2028 pay_assignment_actions paa_curr
2029 where paa_curr.assignment_action_id = p_assact_id
2030 and paa.assignment_id = p_asg_id
2031 and paa.payroll_action_id = ppa.payroll_action_id
2032 and paa.payroll_action_id <> paa_curr.payroll_action_id
2033 and paa.action_sequence < paa_curr.action_sequence
2034 and ppa.action_type in ('R', 'Q')
2035 and ppa.effective_date >= l_effective_date; /*Bug 13855961 */
2036
2037 if (run_counts > 0) then
2038 l_update_cc_date := FALSE;
2039 end if;
2040
2041 elsif (l_change_type = 'DATE_EARNED') then
2042
2043 select count(*)
2044 into run_counts
2045 from pay_payroll_actions ppa,
2046 pay_assignment_actions paa,
2047 pay_assignment_actions paa_curr
2048 where paa_curr.assignment_action_id = p_assact_id
2049 and paa.assignment_id = p_asg_id
2050 and paa.payroll_action_id = ppa.payroll_action_id
2051 and paa.payroll_action_id <> paa_curr.payroll_action_id
2052 and paa.action_sequence < paa_curr.action_sequence
2053 and ppa.action_type in ('R', 'Q')
2054 and ppa.date_earned >= l_effective_date; /* Bug 13855961 */
2055
2056 if (run_counts > 0) then
2057 l_update_cc_date := FALSE;
2058 end if;
2059
2060 end if;
2061
2062 fetch get_min_dates into l_effective_date, l_change_type;
2063
2064 end loop;
2065
2066 close get_min_dates;
2067
2068 -- 7205112
2069 -- Now check for group level events
2070
2071 IF (l_update_cc_date= TRUE) then
2072
2073 open get_group_events(l_cca_date,
2074 p_sysdate);
2075 fetch get_group_events into l_table_name,l_surrogate_key;
2076
2077 while (get_group_events%notfound = FALSE
2078 and l_update_cc_date = TRUE) loop
2079
2080 l_grp_event_valid := pay_interpreter_pkg.valid_group_event_for_asg(l_table_name,
2081 p_asg_id,
2082 l_surrogate_key);
2083
2084 if l_grp_event_valid = 'Y' then
2085
2086 l_update_cc_date := FALSE;
2087
2088 end if;
2089
2090 fetch get_group_events into l_table_name,l_surrogate_key;
2091
2092 end loop;
2093
2094 close get_group_events;
2095
2096 END IF;
2097
2098 if (l_update_cc_date = TRUE) then
2099
2100 new_cc_date :=p_sysdate;
2101
2102 hr_utility.trace('Updating pay_recorded_requests, process and recorded_date : '|| p_process || ' ' || new_cc_date);
2103
2104 pay_recorded_requests_pkg.set_recorded_date(
2105 p_process => p_process,
2106 p_recorded_date => new_cc_date,
2107 p_recorded_date_o => old_cc_date,
2108 p_attribute1 => p_asg_id);
2109
2110 end if;
2111 --
2112 end set_req_dates_for_run;
2113 --
2114 /* Name : reset_dates_for_run
2115 Purpose :
2116 This procedure is used in the Payroll Run to reset the request
2117 submission dates of dependent processes.
2118 */
2119 procedure reset_dates_for_run( p_asg_id in number,
2120 p_sysdate in date,
2121 p_assact_id in number)
2122 is
2123 begin
2124 --
2125 set_req_dates_for_run(p_process => 'CCA',
2126 p_asg_id => p_asg_id,
2127 p_sysdate => p_sysdate,
2128 p_assact_id => p_assact_id
2129 );
2130 --
2131 set_req_dates_for_run(p_process => 'RETRONOT_ASG',
2132 p_asg_id => p_asg_id,
2133 p_sysdate => p_sysdate,
2134 p_assact_id => p_assact_id
2135 );
2136 --
2137 end reset_dates_for_run;
2138 --
2139 END PAY_CC_PROCESS_UTILS;