[Home] [Help]
PACKAGE BODY: APPS.PAY_CC_PROCESS_UTILS
Source
1 PACKAGE BODY PAY_CC_PROCESS_UTILS AS
2 /* $Header: pyccutl.pkb 120.2.12010000.3 2008/08/06 07:00:28 ubhat 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 begin
705 --
706 g_pay_schema := paywsdyg_pkg.get_table_owner(p_table_name);
707 --
708 /* Set up the trigger */
709 pay_dyn_triggers.create_trigger_event(
710 p_table_name||'_ARU',
711 p_table_name,
712 'Continuous Calcuation trigger on update of '||p_table_name,
713 'N',
714 'N',
715 'U',
716 NULL
717 );
718 --
719 /* Setup the business Group */
720 pay_dyn_triggers.create_trg_declaration(
721 p_table_name||'_ARU',
722 'business_group_id',
723 'N',
724 NULL,
725 NULL
726 );
727 --
728 pay_dyn_triggers.create_trg_initialisation(
729 p_table_name||'_ARU',
730 '1',
731 'pay_core_utils.get_business_group',
732 'F',
733 NULL
734 );
735 --
736 pay_dyn_triggers.create_trg_parameter(
737 p_table_name||'_ARU',
738 1,
739 NULL,
740 NULL,
741 NULL,
742 NULL,
743 'I',
744 'R',
745 'business_group_id',
746 'l_business_group_id',
747 'N',
748 NULL
749 );
750 --
751 pay_dyn_triggers.create_trg_parameter(
752 p_table_name||'_ARU',
753 1,
754 NULL,
755 NULL,
756 NULL,
757 NULL,
758 'I',
759 'I',
760 'p_statement',
761 p_bg_select,
762 'N',
763 NULL
764 );
765 --
766 /* Setup the legislation code */
767 pay_dyn_triggers.create_trg_declaration(
768 p_table_name||'_ARU',
769 'legislation_code',
770 'C',
771 10,
772 NULL
773 );
774 --
775 pay_dyn_triggers.create_trg_initialisation(
776 p_table_name||'_ARU',
777 '2',
778 'pay_core_utils.get_legislation_code',
779 'F',
780 NULL
781 );
782 --
783 pay_dyn_triggers.create_trg_parameter(
784 p_table_name||'_ARU',
785 2,
786 NULL,
787 NULL,
788 NULL,
789 NULL,
790 'I',
791 'R',
792 'legislation_code',
793 'l_legislation_code',
794 'N',
795 NULL
796 );
797 --
798 pay_dyn_triggers.create_trg_parameter(
799 p_table_name||'_ARU',
800 2,
801 NULL,
802 NULL,
803 NULL,
804 NULL,
805 'I',
806 'I',
807 'p_bg_id',
808 'l_business_group_id',
809 'N',
810 NULL
811 );
812 --
813 pay_dyn_triggers.create_trg_components(
814 p_table_name||'_ARU',
815 NULL,
816 NULL,
817 NULL,
818 p_pkg_proc_name,
819 'N',
820 NULL
821 );
822 --
823 pay_dyn_triggers.create_trg_parameter(
824 p_table_name||'_ARU',
825 NULL,
826 NULL,
827 NULL,
828 NULL,
829 p_pkg_proc_name,
830 'C',
831 'I',
832 'p_business_group_id',
833 'l_business_group_id',
834 'N',
835 NULL
836 );
837 --
838 pay_dyn_triggers.create_trg_parameter(
839 p_table_name||'_ARU',
840 NULL,
841 NULL,
842 NULL,
843 NULL,
844 p_pkg_proc_name,
845 'C',
846 'I',
847 'p_legislation_code',
848 'l_legislation_code',
849 'N',
850 NULL
851 );
852 --
853 pay_dyn_triggers.create_trg_parameter(
854 p_table_name||'_ARU',
855 NULL,
856 NULL,
857 NULL,
858 NULL,
859 p_pkg_proc_name,
860 'C',
861 'I',
862 'p_effective_date',
863 ':new.effective_start_date',
864 'N',
865 NULL
866 );
867 --
868 for colrec in get_columns(p_table_name) loop
869 pay_dyn_triggers.create_trg_parameter(
870 p_table_name||'_ARU',
871 NULL,
872 NULL,
873 NULL,
874 NULL,
875 p_pkg_proc_name,
876 'C',
877 'I',
878 'p_old_'||colrec.column_name,
879 ':old.'||colrec.full_column_name,
880 'N',
881 NULL
882 );
883 --
884 pay_dyn_triggers.create_trg_parameter(
885 p_table_name||'_ARU',
886 NULL,
887 NULL,
888 NULL,
889 NULL,
890 p_pkg_proc_name,
891 'C',
892 'I',
893 'p_new_'||colrec.column_name,
894 ':new.'||colrec.full_column_name,
895 'N',
896 NULL
897 );
898 --
899 end loop;
900 --
901 end generate_trg_data;
902 --
903 /* Name : generate_upd_trigger
904 Purpose : This procedure is used for as a generator tool for development to
905 create both the DYnamic Trigger code content (which is then stored in
906 PAY_CC_DYT_CODE_PKG) and also the data driven DYT iunformation. Eg populate
907 base tables.
908 The former behaviour is mode 'PROCEDURE', the latter 'TRIGGER DATA'.
909 For more detailed info please see CC White Paper.
910 */
911
912 procedure generate_upd_trigger(p_table_name in varchar2,
913 p_owner in varchar2,
914 p_surr_key_name in varchar2,
915 p_eff_str_name in varchar2,
916 p_eff_end_name in varchar2,
917 p_pkg_proc_name in varchar2 default null,
918 p_bg_select in varchar2 default null,
919 p_mode in varchar2 default 'PROCEDURE')
920 is
921 --
922 cursor dtexists is
923 select dated_table_id, object_version_number
924 from pay_dated_tables pdt
925 where table_name = p_table_name;
926 --
927 found boolean;
928 l_dated_tables_id number;
929 lv_object_version_number number;
930 l_result boolean;
931 l_prod_status varchar2(1);
932 l_industry varchar2(1);
933 l_owner varchar2(30);
934 --
935 begin
936 --
937 if (p_owner is null) then
938 if g_pay_schema is null then
939 l_result := fnd_installation.get_app_info ( 'PAY',
940 l_prod_status,
941 l_industry,
942 g_pay_schema );
943 end if;
944 l_owner := g_pay_schema;
945 else
946 l_owner := p_owner;
947 end if;
948 --
949 if (p_mode = 'PROCEDURE') then
950 --
951 generate_cc_procedure(p_table_name,
952 p_surr_key_name,
953 p_eff_str_name,
954 p_eff_end_name,
955 l_owner
956 );
957 --
958 elsif (p_mode = 'TRIGGER DATA') then
959 --
960 generate_trg_data(p_table_name, p_eff_str_name, p_eff_end_name,
961 p_pkg_proc_name, p_bg_select);
962 --
963 found := FALSE;
964 for getrec in dtexists loop
965 l_dated_tables_id := getrec.dated_table_id;
966 lv_object_version_number := getrec.object_version_number;
967 found := TRUE;
968 end loop;
969 --
970 if (found = FALSE) then
971 pay_dated_tables_api.CREATE_DATED_TABLE(
972 p_table_name => p_table_name
973 , p_application_id => null
974 , p_surrogate_key_name => p_surr_key_name
975 , p_start_date_name => p_eff_str_name
976 , p_end_date_name => p_eff_str_name
977 , p_business_group_id => null
978 , p_legislation_code => null
979 , p_dated_table_id => l_dated_tables_id
980 , p_object_version_number => lv_object_version_number);
981 end if;
982 end if;
983 --
984 end generate_upd_trigger;
985 --
986 /* Name : generate_cc_procedure
987 Purpose : This procedure generates a default continuous calc update
988 procedure for the specified Table. The procedure is
989 generated into the log file.
990 Arguments :
991 Notes :
992 */
993 procedure generate_cc_procedure(p_table_name in varchar2,
994 p_surr_key_name in varchar2,
995 p_eff_str_name in varchar2,
996 p_eff_end_name in varchar2,
997 p_owner in varchar2
998 )
999 is
1000 --
1001 cursor get_columns (p_tab_name in varchar2,
1002 p_start in varchar2,
1003 p_end in varchar2,
1004 l_owner in varchar2)
1005 is
1006 select substr(column_name, 1, 24) column_name,
1007 column_name full_column_name,
1008 data_type
1009 from all_tab_columns
1010 where table_name = p_tab_name
1011 and owner = l_owner
1012 and column_name not in ('LAST_UPDATE_DATE',
1013 'LAST_UPDATED_BY',
1014 'LAST_UPDATE_LOGIN',
1015 'CREATED_BY',
1016 'CREATION_DATE',
1017 'OBJECT_VERSION_NUMBER')
1018 and data_type in ('NUMBER', 'VARCHAR2', 'DATE')
1019 order by decode (column_name, p_start, 3,
1020 p_end, 2,
1021 1),
1022 column_name;
1023 --
1024 proc varchar2(32767);
1025 l_result boolean;
1026 l_prod_status varchar2(1);
1027 l_industry varchar2(1);
1028 l_owner varchar2(30);
1029 l_eff_str_up varchar2(40);
1030 l_eff_str_low varchar2(40);
1031 l_eff_end_up varchar2(40);
1032 l_eff_end_low varchar2(40);
1033 begin
1034 --
1035 /* OK put trace on */
1036 hr_utility.trace_on(null, p_table_name);
1037 --
1038 if (p_owner is null) then
1039 g_pay_schema := paywsdyg_pkg.get_table_owner(p_table_name);
1040 l_owner := g_pay_schema;
1041 else
1042 if paywsdyg_pkg.is_table_owner_valid(p_table_name,p_owner) = 'N' then
1043 hr_utility.trace('-- WARNING: owner '||p_owner||' is not valid for table '||p_table_name);
1044 end if;
1045 l_owner := p_owner;
1046 end if;
1047 --
1048 l_eff_str_up := upper(p_eff_str_name);
1049 l_eff_str_low := lower(p_eff_str_name);
1050 l_eff_end_up := upper(p_eff_end_name);
1051 l_eff_end_low := lower(p_eff_end_name);
1052 --
1053 proc := 'procedure '||p_table_name||'_aru(
1054 p_business_group_id in number,
1055 p_legislation_code in varchar2,
1056 p_effective_date in date';
1057 --
1058 hr_utility.trace(proc);
1059 for colrec in get_columns(p_table_name, l_eff_str_up, l_eff_end_up, l_owner) loop
1060 proc := ',
1061 ';
1062 proc := proc||'p_old_'||colrec.column_name||' in '||colrec.data_type||',
1063 ';
1064 proc := proc||'p_new_'||colrec.column_name||' in '||colrec.data_type;
1065 --
1066 hr_utility.trace(proc);
1067 end loop;
1068 --
1069 proc := '
1070 )
1071 is
1072 --
1073 begin
1074 /* If the continuous calc is overriden then do nothing */
1075 if (pay_continuous_calc.g_override_cc = TRUE) then
1076 return;
1077 end if;
1078 --
1079 /* If the dates havent changed it must be a correction */
1080 if (p_old_'||l_eff_end_up||' = p_new_'||l_eff_end_up||'
1081 and p_old_'||l_eff_str_up||' = p_new_'||l_eff_str_up||') then';
1082 --
1083 hr_utility.trace(proc);
1084 for colrec in get_columns(p_table_name, l_eff_str_up, l_eff_end_up, l_owner) loop
1085 --
1086 if (colrec.column_name = l_eff_end_up) then
1087 proc := proc||'
1088 else
1089 /* OK it must be a date track change */';
1090 hr_utility.trace(proc);
1091 end if;
1092 --
1093 proc := '--
1094 pay_continuous_calc.event_update(p_business_group_id,
1095 p_legislation_code,
1096 '''||p_table_name||''',
1097 '''||colrec.full_column_name||''',
1098 p_old_'||colrec.column_name||',
1099 p_new_'||colrec.column_name||',';
1100 if (colrec.column_name = l_eff_str_up) then
1101 proc := proc||'
1102 p_new_'||l_eff_str_low||',
1103 least(p_old_'||l_eff_str_low||',
1104 p_new_'||l_eff_str_low||')';
1105 else
1106 if (colrec.column_name = l_eff_end_up) then
1107 proc := proc||'
1108 p_new_'||l_eff_end_low||',
1109 least(p_old_'||l_eff_end_low||',
1110 p_new_'||l_eff_end_low||')';
1111 else
1112 proc := proc||'
1113 p_effective_date';
1114 end if;
1115 end if;
1116 --
1117 proc := proc||'
1118 );';
1119 hr_utility.trace(proc);
1120 end loop;
1121 proc := '
1122 end if;
1123 --
1124 /* Now call the API for the affected assignments */
1125 declare
1126 l_process_event_id number;
1127 l_object_version_number number;
1128 cnt number;
1129 begin
1130 if (pay_continuous_calc.g_event_list.sz <> 0) then
1131 for cnt in 1..pay_continuous_calc.g_event_list.sz loop
1132 pay_ppe_api.create_process_event(
1133 p_assignment_id => p_assignment_id?,
1134 p_effective_date => pay_continuous_calc.g_event_list.effective_date(cnt),
1135 p_change_type => pay_continuous_calc.g_event_list.change_type(cnt),
1136 p_status => ''U'',
1137 p_description => pay_continuous_calc.g_event_list.description(cnt),
1138 p_process_event_id => l_process_event_id,
1139 p_object_version_number => l_object_version_number,
1140 p_event_update_id => pay_continuous_calc.g_event_list.event_update_id(cnt),
1141 p_business_group_id => p_business_group_id,
1142 p_calculation_date => pay_continuous_calc.g_event_list.calc_date(cnt),
1143 p_surrogate_key => p_new_'||lower(p_surr_key_name)||'
1144 );
1145 end loop;
1146 end if;
1147 pay_continuous_calc.g_event_list.sz := 0;
1148 end;
1149 --
1150 end '||p_table_name||'_aru;';
1151 --
1152 hr_utility.trace(proc);
1153 end generate_cc_procedure;
1154 --
1155
1156 --
1157 /* Name : get_asg_act_status
1158 Purpose : This function returns whether a asg_act has been modified
1159 By checking the results of PPE. The crucial point is establishing
1160 the date the CC process was run for the payroll and making sure
1161 more recent changes exist.
1162 Similar copy of code in pyasa01t, removing redundant status restriction
1163 on cursors
1164 Arguments :
1165 Notes :
1166 */
1167 FUNCTION get_asg_act_status( p_assignment_action_id in number,
1168 p_action_type in varchar2,
1169 p_action_status in varchar2) return varchar2
1170 is
1171
1172 l_payroll_id number;
1173 l_assignment_id number;
1174 l_date date;
1175
1176 l_dummy_action_id pay_assignment_actions.assignment_action_id%type ;
1177 ischanged boolean;
1178
1179 cursor get_payroll (cp_asg_act_id in number) is
1180 select ppa.payroll_id
1181 from
1182 pay_payroll_actions ppa,
1183 pay_assignment_actions paa
1184 where paa.assignment_action_id = cp_asg_act_id
1185 and paa.payroll_action_id = ppa.payroll_action_id;
1186
1187 cursor get_assignment_id (cp_asg_act_id in number) is
1188 select assignment_id
1189 from pay_assignment_actions paa
1190 where paa.assignment_action_id = cp_asg_act_id;
1191
1192 --
1193 -- A given assignment action is void if there is a payroll action of type 'D'
1194 -- locks ( though PAY_ACTION_INTERLOCKS ) the assignment action.
1195 -- Note that this cursor does not check whether the void assignment action has
1196 -- a status of complete
1197 --
1198 cursor c_is_voided ( p_assignment_action_id in number ) is
1199 select intloc.locking_action_id
1200 from pay_assignment_actions assact,
1201 pay_action_interlocks intloc,
1202 pay_payroll_actions pact
1203 where intloc.locked_action_id = p_assignment_action_id
1204 and intloc.locking_action_id = assact.assignment_action_id
1205 and assact.payroll_action_id = pact.payroll_action_id
1206 and pact.action_type = 'D';
1207 --
1208 cursor run_modified (p_assignment_action_id in number,
1209 cp_last_cc_run_date in date ) is
1210 select paa.assignment_action_id
1211 from
1212 pay_payroll_actions ppa,
1213 pay_assignment_actions paa
1214 where paa.assignment_action_id = p_assignment_action_id
1215 and paa.payroll_action_id = ppa.payroll_action_id
1216 and paa.action_status = 'C'
1217 and exists (select ''
1218 from pay_process_events ppe
1219 where ppe.assignment_id = paa.assignment_id
1220 and ppe.change_type in ('GRE', 'DATE_EARNED', 'DATE_PROCESSED')
1221 and ppe.creation_date > cp_last_cc_run_date
1222 and ppe.effective_date <= nvl(ppa.date_earned,ppa.effective_date)
1223 )
1224 and not exists (select ''
1225 from pay_assignment_actions paa1, -- Prepay/Costing
1226 pay_action_interlocks pai1,
1227 pay_assignment_actions paa2,-- Payment/Trans GL
1228 pay_action_interlocks pai2
1229 where pai1.locked_action_id = paa.assignment_action_id
1230 and pai1.locking_action_id = paa1.assignment_action_id
1231 and pai2.locked_action_id = paa1.assignment_action_id
1232 and pai2.locking_action_id = paa2.assignment_action_id);
1233 --
1234 cursor prepay_modified (p_assignment_action_id in number,
1235 cp_last_cc_run_date in date ) is
1236 select paa.assignment_action_id
1237 from
1238 pay_payroll_actions ppa,
1239 pay_assignment_actions paa
1240 where paa.assignment_action_id = p_assignment_action_id
1241 and paa.payroll_action_id = ppa.payroll_action_id
1242 and paa.action_status = 'C'
1243 and not exists (select ''
1244 from pay_assignment_actions paa1, -- Payment/Trans GL
1245 pay_action_interlocks pai1
1246 where pai1.locked_action_id = paa.assignment_action_id
1247 and pai1.locking_action_id = paa1.assignment_action_id)
1248 and (exists (select ''
1249 from pay_process_events ppe
1250 where ppe.assignment_id = paa.assignment_id
1251 and ppe.effective_date < ppa.effective_date
1252 and ppe.change_type in ('PAYMENT')
1253 and ppe.creation_date > cp_last_cc_run_date
1254 )
1255 or
1256 exists (select ''
1257 from pay_action_interlocks pai,
1258 pay_assignment_actions paa2,
1259 pay_payroll_actions ppa2
1260 where pai.locking_action_id = paa.assignment_action_id
1261 and pai.locked_action_id = paa2.assignment_action_id
1262 and paa2.payroll_action_id = ppa2.payroll_action_id
1263 and ppa2.action_type in ('R','Q')
1264 and exists (select ''
1265 from pay_process_events ppe
1266 where ppe.assignment_id = paa2.assignment_id
1267 and ppe.effective_date < ppa2.effective_date
1268 and ppe.change_type in ('GRE', 'DATE_EARNED', 'DATE_PROCESSED')
1269 and ppe.creation_date > cp_last_cc_run_date
1270 )
1271 )
1272 );
1273 --
1274 cursor cost_modified (p_assignment_action_id in number,
1275 cp_last_cc_run_date in date ) is
1276 select paa.assignment_action_id
1277 from
1278 pay_payroll_actions ppa,
1279 pay_assignment_actions paa
1280 where paa.assignment_action_id = p_assignment_action_id
1281 and paa.payroll_action_id = ppa.payroll_action_id
1282 and paa.action_status = 'C'
1283 and not exists (select ''
1284 from pay_assignment_actions paa1, -- Payment/Trans GL
1285 pay_action_interlocks pai1
1286 where pai1.locked_action_id = paa.assignment_action_id
1287 and pai1.locking_action_id = paa1.assignment_action_id)
1288 and exists (select ''
1289 from pay_process_events ppe
1290 where ppe.assignment_id = paa.assignment_id
1291 and ppe.effective_date < ppa.effective_date
1292 and ppe.change_type in ('COST_CENTRE')
1293 and ppe.creation_date > cp_last_cc_run_date
1294 )
1295 and exists (select ''
1296 from pay_action_interlocks pai,
1297 pay_assignment_actions paa2,
1298 pay_payroll_actions ppa2
1299 where pai.locking_action_id = paa.assignment_action_id
1300 and pai.locked_action_id = paa2.assignment_action_id
1301 and paa2.payroll_action_id = ppa2.payroll_action_id
1302 and ppa2.action_type in ('R','Q')
1303 and exists (select ''
1304 from pay_process_events ppe
1305 where ppe.assignment_id = paa2.assignment_id
1306 and ppe.effective_date < ppa2.effective_date
1307 and ppe.change_type in ('GRE', 'DATE_EARNED', 'DATE_PROCESSED')
1308 and ppe.creation_date > cp_last_cc_run_date
1309 )
1310 );
1311 --
1312 --
1313 l_return_value hr_lookups.meaning%type ;
1314 l_proc varchar2(80) := g_pkg||'.get_asg_act_status';
1315
1316 BEGIN
1317 hr_utility.set_location(l_proc,10);
1318
1319 --
1320 ischanged := FALSE;
1321 --
1322 --Get assignment_id for this asg_act_id
1323 --
1324 open get_assignment_id (p_assignment_action_id);
1325 fetch get_assignment_id into l_assignment_id;
1326 close get_assignment_id;
1327 hr_utility.trace('-assignment_id: '||l_assignment_id);
1328 --Get date CC was last executed
1329 --
1330 -- As highlighted in bug 3146928
1331 -- This function is used in a view and thus no dml can occur, so call new proc
1332 --
1333 PAY_RECORDED_REQUESTS_PKG.get_recorded_date_no_ins('CC_ASG',l_date,l_assignment_id);
1334 hr_utility.trace('-last CC run date is '||l_date);
1335
1336 --
1337 -- bug 3265814
1338 --If looks like CC not in use then dont bother to look for modified
1339 --better to have global payroll level switch, but now compare date
1340 if (l_date = hr_api.g_sot) then
1341 -- l_return_value := hr_general.decode_lookup('ACTION_STATUS',p_action_status ) ;
1342 -- Bug 3576520: Repeating the special case for the Cheque Writer.
1343 if ( p_action_type in ('M', 'H' )) then
1344 open c_is_voided( p_assignment_action_id ) ;
1345 fetch c_is_voided into l_dummy_action_id ;
1346 if c_is_voided%found then
1347 l_return_value := hr_general.decode_lookup('ACTION_STATUS','V');
1348 hr_utility.set_location(l_proc,50);
1349 else
1350 l_return_value := hr_general.decode_lookup('ACTION_STATUS',p_action_status ) ;
1351 hr_utility.set_location(l_proc,55);
1352 end if;
1353 close c_is_voided ;
1354 else
1355 l_return_value := hr_general.decode_lookup('ACTION_STATUS',p_action_status ) ;
1356 end if;
1357 --
1358 else
1359 if ( p_action_type in ('R', 'Q')) then
1360 --
1361 ischanged := FALSE;
1362 --
1363 -- Check Run change.
1364 open run_modified( p_assignment_action_id, l_date );
1365 fetch run_modified into l_dummy_action_id ;
1366 if run_modified%found then
1367 ischanged := TRUE;
1368 end if;
1369 close run_modified ;
1370 --
1371 if (ischanged) then
1372 l_return_value := hr_general.decode_lookup('ACTION_STATUS','MO');
1373 hr_utility.set_location(l_proc,20);
1374 else
1375 l_return_value := hr_general.decode_lookup('ACTION_STATUS',p_action_status ) ;
1376 hr_utility.set_location(l_proc,25);
1377 end if;
1378 --
1379 elsif ( p_action_type in ('P', 'U')) then
1380 --
1381 ischanged := FALSE;
1382 --
1383 -- Check Prepay change.
1384 open prepay_modified( p_assignment_action_id, l_date );
1385 fetch prepay_modified into l_dummy_action_id ;
1386 if prepay_modified%found then
1387 ischanged := TRUE;
1388 end if;
1389 close prepay_modified ;
1390 --
1391 if (ischanged) then
1392 l_return_value := hr_general.decode_lookup('ACTION_STATUS','MO');
1393 hr_utility.set_location(l_proc,30);
1394 else
1395 l_return_value := hr_general.decode_lookup('ACTION_STATUS',p_action_status ) ;
1396 hr_utility.set_location(l_proc,35);
1397 end if;
1398 --
1399 elsif ( p_action_type = 'C') then
1400 --
1401 ischanged := FALSE;
1402 --
1403 -- Check Costing change.
1404 open cost_modified( p_assignment_action_id, l_date );
1405 fetch cost_modified into l_dummy_action_id ;
1406 if cost_modified%found then
1407 ischanged := TRUE;
1408 end if;
1409 close cost_modified ;
1410 --
1411 if (ischanged) then
1412 l_return_value := hr_general.decode_lookup('ACTION_STATUS','MO');
1413 hr_utility.set_location(l_proc,40);
1414 else
1415 l_return_value := hr_general.decode_lookup('ACTION_STATUS',p_action_status ) ;
1416 hr_utility.set_location(l_proc,45);
1417 end if;
1418 --
1419 --
1420 elsif ( p_action_type = 'H' ) then
1421 open c_is_voided( p_assignment_action_id ) ;
1422 fetch c_is_voided into l_dummy_action_id ;
1423 if c_is_voided%found then
1424 l_return_value := hr_general.decode_lookup('ACTION_STATUS','V');
1425 hr_utility.set_location(l_proc,50);
1426 else
1427 l_return_value := hr_general.decode_lookup('ACTION_STATUS',p_action_status ) ;
1428 hr_utility.set_location(l_proc,55);
1429 end if;
1430 close c_is_voided ;
1431 else
1432 l_return_value := hr_general.decode_lookup('ACTION_STATUS',p_action_status ) ;
1433 end if;
1434 end if;
1435 hr_utility.set_location(l_proc,900);
1436 return ( l_return_value ) ;
1437 end get_asg_act_status ;
1438
1439
1440 /* Name : generate_upd_script
1441 Purpose : This procedure generates a the upload script to load the
1442 trigger definition of a generated cc procedure into the
1443 dynamic trigger tables.
1444 Arguments :
1445 Notes :
1446 */
1447 procedure generate_upd_script(p_table_name in varchar2
1448 )
1449 is
1450 --
1451 cursor get_columns (p_tab_name in varchar2)
1452 is
1453 select substr(column_name, 1, 24) column_name,
1454 column_name full_column_name,
1455 data_type
1456 from all_tab_columns
1457 where table_name = p_tab_name
1458 and owner = g_pay_schema
1459 and column_name not in ('LAST_UPDATE_DATE',
1460 'LAST_UPDATED_BY',
1461 'LAST_UPDATE_LOGIN',
1462 'CREATED_BY',
1463 'CREATION_DATE',
1464 'OBJECT_VERSION_NUMBER')
1465 and data_type in ('NUMBER', 'VARCHAR2', 'DATE')
1466 order by column_name;
1467 --
1468 proc varchar2(32767);
1469 l_result boolean;
1470 l_prod_status varchar2(1);
1471 l_industry varchar2(1);
1472 --
1473 begin
1474 /* OK put trace on */
1475 hr_utility.trace_on(null, p_table_name);
1476 --
1477 g_pay_schema := paywsdyg_pkg.get_table_owner(p_table_name);
1478 --
1479 proc := ' pay_dyn_triggers.create_trigger_event(
1480 '''||p_table_name||'_ARU'',
1481 '''||p_table_name||''',
1482 ''Description'',
1483 ''N'',
1484 ''N'',
1485 ''U'',
1486 NULL
1487 );
1488 --
1489 ';
1490 --
1491 hr_utility.trace(proc);
1492 --
1493 proc := ' pay_dyn_triggers.create_trg_declaration(
1494 '''||p_table_name||'_ARU'',
1495 ''business_group_id'',
1496 ''N'',
1497 NULL,
1498 NULL
1499 );
1500 --
1501 ';
1502 hr_utility.trace(proc);
1503 --
1504 proc := ' pay_dyn_triggers.create_trg_initialisation(
1505 '''||p_table_name||'_ARU'',
1506 ''1'',
1507 ''pay_core_utils.get_business_group'',
1508 ''F'',
1509 sysdate,
1510 NULL
1511 );
1512 --
1513 ';
1514 hr_utility.trace(proc);
1515 --
1516 proc := ' pay_dyn_triggers.create_trg_parameter(
1517 '''||p_table_name||'_ARU'',
1518 1,
1519 NULL,
1520 NULL,
1521 NULL,
1522 NULL,
1523 ''I'',
1524 ''R'',
1525 ''business_group_id'',
1526 ''l_business_group_id'',
1527 ''N'',
1528 NULL
1529 );
1530 --
1531 ';
1532 hr_utility.trace(proc);
1533 --
1534 proc := ' pay_dyn_triggers.create_trg_parameter(
1535 '''||p_table_name||'_ARU'',
1536 1,
1537 NULL,
1538 NULL,
1539 NULL,
1540 NULL,
1541 ''I'',
1542 ''I'',
1543 ''p_statement'',
1544 ''''''select statement'''''',
1545 ''N'',
1546 NULL
1547 );
1548 --
1549 ';
1550 hr_utility.trace(proc);
1551 --
1552 proc := ' pay_dyn_triggers.create_trg_declaration(
1553 '''||p_table_name||'_ARU'',
1554 ''legislation_code'',
1555 ''C'',
1556 10,
1557 NULL
1558 );
1559 --
1560 ';
1561 hr_utility.trace(proc);
1562 --
1563 proc := ' pay_dyn_triggers.create_trg_initialisation(
1564 '''||p_table_name||'_ARU'',
1565 ''2'',
1566 ''pay_core_utils.get_legislation_code'',
1567 ''F'',
1568 sysdate,
1569 NULL
1570 );
1571 --
1572 ';
1573 hr_utility.trace(proc);
1574 --
1575 proc := ' pay_dyn_triggers.create_trg_parameter(
1576 '''||p_table_name||'_ARU'',
1577 2,
1578 NULL,
1579 NULL,
1580 NULL,
1581 NULL,
1582 ''I'',
1583 ''R'',
1584 ''legislation_code'',
1585 ''l_legislation_code'',
1586 ''N'',
1587 NULL
1588 );
1589 --
1590 ';
1591 hr_utility.trace(proc);
1592 --
1593 proc := ' pay_dyn_triggers.create_trg_parameter(
1594 '''||p_table_name||'_ARU'',
1595 2,
1596 NULL,
1597 NULL,
1598 NULL,
1599 NULL,
1600 ''I'',
1601 ''I'',
1602 ''p_bg_id'',
1603 ''l_business_group_id'',
1604 ''N'',
1605 NULL
1606 );
1607 --
1608 ';
1609 hr_utility.trace(proc);
1610 --
1611 proc := ' pay_dyn_triggers.create_trg_components(
1612 '''||p_table_name||'_ARU'',
1613 NULL,
1614 NULL,
1615 NULL,
1616 ''package.procedure'',
1617 ''N'',
1618 sysdate,
1619 NULL
1620 );
1621 --
1622 ';
1623 hr_utility.trace(proc);
1624 --
1625 proc := ' pay_dyn_triggers.create_trg_parameter(
1626 '''||p_table_name||'_ARU'',
1627 NULL,
1628 NULL,
1629 NULL,
1630 NULL,
1631 ''package.procedure'',
1632 ''C'',
1633 ''I'',
1634 ''p_business_group_id'',
1635 ''l_business_group_id'',
1636 ''N'',
1637 NULL
1638 );
1639 --
1640 ';
1641 hr_utility.trace(proc);
1642 --
1643 proc := ' pay_dyn_triggers.create_trg_parameter(
1644 '''||p_table_name||'_ARU'',
1645 NULL,
1646 NULL,
1647 NULL,
1648 NULL,
1649 ''package.procedure'',
1650 ''C'',
1651 ''I'',
1652 ''p_legislation_code'',
1653 ''l_legislation_code'',
1654 ''N'',
1655 NULL
1656 );
1657 --
1658 ';
1659 hr_utility.trace(proc);
1660 --
1661 proc := ' pay_dyn_triggers.create_trg_parameter(
1662 '''||p_table_name||'_ARU'',
1663 NULL,
1664 NULL,
1665 NULL,
1666 NULL,
1667 ''package.procedure'',
1668 ''C'',
1669 ''I'',
1670 ''p_effective_date'',
1671 '':new.effective_start_date'',
1672 ''N'',
1673 NULL
1674 );
1675 --
1676 ';
1677 hr_utility.trace(proc);
1678 --
1679 for colrec in get_columns(p_table_name) loop
1680 proc := ' pay_dyn_triggers.create_trg_parameter(
1681 '''||p_table_name||'_ARU'',
1682 NULL,
1683 NULL,
1684 NULL,
1685 NULL,
1686 ''package.procedure'',
1687 ''C'',
1688 ''I'',
1689 ''p_old_'||colrec.column_name||''',
1690 '':old.'||colrec.column_name||''',
1691 ''N'',
1692 NULL
1693 );
1694 --
1695 ';
1696 hr_utility.trace(proc);
1697 --
1698 proc := ' pay_dyn_triggers.create_trg_parameter(
1699 '''||p_table_name||'_ARU'',
1700 NULL,
1701 NULL,
1702 NULL,
1703 NULL,
1704 ''package.procedure'',
1705 ''C'',
1706 ''I'',
1707 ''p_new_'||colrec.column_name||''',
1708 '':new.'||colrec.column_name||''',
1709 ''N'',
1710 NULL
1711 );
1712 --
1713 ';
1714 hr_utility.trace(proc);
1715 end loop;
1716 --
1717 end generate_upd_script;
1718 --
1719
1720 /* Name : generate_dyt_pkg_behaviour
1721 Purpose : This procedure is used as a generator tool for development to
1722 alter the stored definitions of the Dynamic Trigger data such
1723 that the DYT wrapper code will now be held in a package rather than
1724 as explicit database triggers.
1725 For more information see the CC White Paper.
1726 Paramaters:
1727 + p_table_name -The dated table in question
1728 + p_tab_rki_pkg -The package containing the user hook information.
1729 Usually this is defined in the row handler file for this table.
1730 This is needed so we can look up the paramater listing as we require
1731 the same list. Similarly for the after_update and after_delete
1732 (...rku_pkg, ...rkd_pkg)
1733 Prerequisites: The DYT must have been created in the old-skool manner,
1734 using the generate_upd_trigger in mode PROCEDURE then mode TRIGGER DATA
1735 The former creates code that should be edited and then placed in
1736 pay_cc_dyt_code_pkg.
1737 */
1738
1739 PROCEDURE GENERATE_DYT_PKG_BEHAVIOUR(p_table_name in varchar2,
1740 p_tab_rki_pkg in varchar2,
1741 p_tab_rku_pkg in varchar2,
1742 p_tab_rkd_pkg in varchar2 ) IS
1743
1744 --Include all control parameters, under the assumption that the finished
1745 --rows will be manually edited, usually removing some of these
1746
1747 cursor csr_args (cp_rki varchar2, cp_rku varchar2, cp_rkd varchar2)
1748 is
1749 SELECT a.argument value_name, a.procedure$ proc_name
1750 FROM SYS.ARGUMENT$ A,
1751 USER_OBJECTS B
1752 WHERE A.OBJ# = B.OBJECT_ID
1753 AND B.OBJECT_NAME in (CP_RKI,CP_RKU,CP_RKD)
1754 AND A.LEVEL# = 0
1755 --AND a.argument not in ('P_VALIDATE',
1756 -- 'P_EFFECTIVE_DATE',
1757 -- 'P_DATETRACK_UPDATE_MODE',
1758 -- 'P_DATETRACK_DELETE_MODE',
1759 -- 'P_VALIDATION_START_DATE',
1760 -- 'P_VALIDATION_END_DATE',
1761 -- 'P_LANGUAGE_CODE')
1762 ORDER BY a.procedure$;
1763
1764 l_prefix varchar2(15); --Local Form prefix
1765 l_o varchar2(15) := ':old.'; --Old Style Local Form prefix
1766 l_n varchar2(15) := ':new.'; --Old Style Local Form prefix
1767
1768 l_pkg_name varchar2(80) ;
1769 l_local_form varchar2(80);
1770
1771 l_usage_type varchar2(15);
1772 l_dated_table_id number;
1773 l_app_id number;
1774
1775 BEGIN
1776 l_pkg_name := p_table_name;
1777
1778 select pdt.dated_table_id
1779 into l_dated_table_id
1780 from pay_dated_tables pdt
1781 where pdt.table_name = p_table_name;
1782
1783 -- >>> PHASE 1: Create the dyt_pkg name based on table_name
1784 --
1785 --Remove _f if exists and replace with _pkg
1786 --
1787 if ( upper(substr(p_table_name,length(p_table_name) - 1, 2)) = '_F' ) then
1788 l_pkg_name := substr(p_table_name,1,length(p_table_name)-2);
1789 end if;
1790
1791 --Insert _dyt after first underscore, nb overwriting owner prefix to PAY
1792 --
1793 l_pkg_name :='PAY_' --substr(l_pkg_name,1,instr(l_pkg_name,'_'))
1794 ||'DYT'
1795 ||substr(l_pkg_name,instr(l_pkg_name,'_'),18)--max 30chars for full pkg
1796 ||'_PKG';
1797 --Get application
1798 if (substr(p_table_name,1,instr(p_table_name,'_')-1) = 'PAY') then
1799 l_app_id := 801;
1800 elsif (substr(p_table_name,1,instr(p_table_name,'_')-1) = 'PER') then
1801 l_app_id := 800;
1802 elsif (substr(p_table_name,1,instr(p_table_name,'_')-1) = 'PQH') then
1803 l_app_id := 8302;
1804 end if;
1805
1806 -- >>> PHASE 2: Set the dated table to have dyt in package
1807 --
1808
1809 update pay_dated_tables pdt
1810 set application_id = l_app_id,
1811 dyn_trigger_type = 'P',
1812 dyn_trigger_package_name = l_pkg_name,
1813 dyn_trig_pkg_generated = 'N'
1814 where pdt.table_name = p_table_name;
1815
1816 -- >>> PHASE 3: Create the parameter mappings for dbtrigs to pkg procedure
1817 --
1818 --get the parameters that have been created for the row handler user hook pkg.
1819 --This generated file has all the parameters that we will also have to create mappings for.
1820 FOR args_rec in csr_args(p_tab_rki_pkg,p_tab_rku_pkg,p_tab_rkd_pkg) LOOP
1821 -- if _o then old style (and remove _o) else new style
1822 --
1823 if ( upper(substr(args_rec.value_name,length(args_rec.value_name) - 1, 2)) = '_O' ) then
1824 l_local_form := substr(args_rec.value_name,1,length(args_rec.value_name)-2);
1825 l_prefix := l_o;
1826 else
1827 l_local_form := args_rec.value_name;
1828 l_prefix := l_n;
1829 end if;
1830
1831 -- if first 2 chars are p_ (which we expect is them all) strip it out
1832 --
1833 if ( upper(substr(l_local_form,1, 2)) = 'P_' ) then
1834 l_local_form := substr(l_local_form,3);
1835 end if;
1836 -- add our prefix
1837 l_local_form := l_prefix||l_local_form;
1838
1839 l_usage_type := 'P'||substr(args_rec.proc_name,7,1);
1840 --dbms_output.put_line(l_dated_table_id||' Insert a '||l_usage_type||' val_name: '||args_rec.value_name||' local form: '||l_local_form);
1841
1842
1843 -- Create rows in trigger_parameters
1844 --
1845 pay_dyn_triggers.create_trg_parameter (
1846 p_short_name => p_table_name,
1847 p_process_order => null,
1848 p_legislative_code => null,
1849 p_business_group => null,
1850 p_payroll_name => null,
1851 p_module_name => null,
1852 p_usage_type => l_usage_type,
1853 p_parameter_type => 'I', --All param are INs 'cos all hook params are INs
1854 p_parameter_name => l_local_form,
1855 p_value_name => args_rec.value_name,
1856 p_automatic => 'Y',
1857 p_owner => null
1858 );
1859
1860 END LOOP;
1861
1862
1863 END GENERATE_DYT_PKG_BEHAVIOUR;
1864
1865 /*
1866 Revert back away from the dyt_pkg behaviour. Intended as development util only.
1867 Obsoleted please see paywsdyg_pkg.convert_tab_style
1868 */
1869 PROCEDURE DROP_DYT_PKG_BEHAVIOUR(p_table_name in varchar2) IS
1870
1871 BEGIN
1872 -- >>> PHASE 1: Set the dated table to have old-skool dyt
1873 --
1874 update pay_dated_tables pdt
1875 set dyn_trigger_type = 'T',
1876 dyn_trigger_package_name = null,
1877 dyn_trig_pkg_generated = null
1878 where pdt.table_name = p_table_name;
1879
1880 -- >>> PHASE 2: Junk all parameters
1881 --
1882 DELETE
1883 FROM pay_trigger_parameters ptp
1884 WHERE ptp.usage_id = (select dated_table_id
1885 from pay_dated_tables pdt
1886 where pdt.table_name = p_table_name)
1887 AND ptp.usage_type in ('PI','PU','PD');
1888
1889 END DROP_DYT_PKG_BEHAVIOUR;
1890
1891 procedure set_req_dates_for_run(p_process in varchar2,
1892 p_asg_id in number,
1893 p_sysdate in date,
1894 p_assact_id in number)
1895 is
1896 cursor get_min_dates(p_asg_id number, p_cca_date date, p_sysdate date)
1897 is
1898 select min(effective_date) effective_date,
1899 change_type
1900 from pay_process_events
1901 where assignment_id = p_asg_id
1902 and creation_date between p_cca_date
1903 and p_sysdate
1904 and change_type in ('DATE_PROCESSED', 'DATE_EARNED')
1905 group by change_type
1906 order by change_type desc;
1907
1908 cursor get_group_events(p_cca_date date, p_sysdate date) is
1909 select pdt.table_name,ppe.surrogate_key
1910 from pay_process_events ppe,
1911 pay_event_updates peu,
1912 pay_dated_tables pdt
1913 where ppe.assignment_id is null
1914 and ppe.creation_date between p_cca_date
1915 and p_sysdate
1916 AND ppe.change_type in ('DATE_PROCESSED', 'DATE_EARNED')
1917 and peu.event_update_id = ppe.event_update_id
1918 and peu.dated_table_id = pdt.dated_table_id;
1919
1920 l_effective_date date;
1921 l_change_type pay_process_events.change_type%type;
1922 l_cca_date date;
1923 run_counts number;
1924 l_update_cc_date boolean;
1925 new_cc_date date;
1926 old_cc_date date;
1927 l_table_name pay_dated_tables.table_name%type;
1928 l_surrogate_key pay_process_events.surrogate_key%type;
1929 l_grp_event_valid varchar2(5);
1930
1931 begin
1932
1933 pay_recorded_requests_pkg.get_recorded_date(
1934 p_process => p_process,
1935 p_recorded_date => l_cca_date,
1936 p_attribute1 => p_asg_id);
1937
1938 l_update_cc_date := TRUE;
1939 open get_min_dates(p_asg_id,
1940 l_cca_date,
1941 p_sysdate);
1942 fetch get_min_dates into l_effective_date, l_change_type;
1943
1944 while (get_min_dates%notfound = FALSE
1945 and l_update_cc_date = TRUE) loop
1946
1947 if (l_change_type = 'DATE_PROCESSED') then
1948
1949 select count(*)
1950 into run_counts
1951 from pay_payroll_actions ppa,
1952 pay_assignment_actions paa,
1953 pay_assignment_actions paa_curr
1954 where paa_curr.assignment_action_id = p_assact_id
1955 and paa.assignment_id = p_asg_id
1956 and paa.payroll_action_id = ppa.payroll_action_id
1957 and paa.payroll_action_id <> paa_curr.payroll_action_id
1958 and paa.action_sequence < paa_curr.action_sequence
1959 and ppa.action_type in ('R', 'Q')
1960 and ppa.effective_date > l_effective_date;
1961
1962 if (run_counts > 0) then
1963 l_update_cc_date := FALSE;
1964 end if;
1965
1966 elsif (l_change_type = 'DATE_EARNED') then
1967
1968 select count(*)
1969 into run_counts
1970 from pay_payroll_actions ppa,
1971 pay_assignment_actions paa,
1972 pay_assignment_actions paa_curr
1973 where paa_curr.assignment_action_id = p_assact_id
1974 and paa.assignment_id = p_asg_id
1975 and paa.payroll_action_id = ppa.payroll_action_id
1976 and paa.payroll_action_id <> paa_curr.payroll_action_id
1977 and paa.action_sequence < paa_curr.action_sequence
1978 and ppa.action_type in ('R', 'Q')
1979 and ppa.date_earned > l_effective_date;
1980
1981 if (run_counts > 0) then
1982 l_update_cc_date := FALSE;
1983 end if;
1984
1985 end if;
1986
1987 fetch get_min_dates into l_effective_date, l_change_type;
1988
1989 end loop;
1990
1991 close get_min_dates;
1992
1993 -- 7205112
1994 -- Now check for group level events
1995
1996 IF (l_update_cc_date= TRUE) then
1997
1998 open get_group_events(l_cca_date,
1999 p_sysdate);
2000 fetch get_group_events into l_table_name,l_surrogate_key;
2001
2002 while (get_group_events%notfound = FALSE
2003 and l_update_cc_date = TRUE) loop
2004
2005 l_grp_event_valid := pay_interpreter_pkg.valid_group_event_for_asg(l_table_name,
2006 p_asg_id,
2007 l_surrogate_key);
2008
2009 if l_grp_event_valid = 'Y' then
2010
2011 l_update_cc_date := FALSE;
2012
2013 end if;
2014
2015 fetch get_group_events into l_table_name,l_surrogate_key;
2016
2017 end loop;
2018
2019 close get_group_events;
2020
2021 END IF;
2022
2023 if (l_update_cc_date = TRUE) then
2024
2025 new_cc_date :=p_sysdate;
2026
2027 hr_utility.trace('Updating pay_recorded_requests, process and recorded_date : '|| p_process || ' ' || new_cc_date);
2028
2029 pay_recorded_requests_pkg.set_recorded_date(
2030 p_process => p_process,
2031 p_recorded_date => new_cc_date,
2032 p_recorded_date_o => old_cc_date,
2033 p_attribute1 => p_asg_id);
2034
2035 end if;
2036 --
2037 end set_req_dates_for_run;
2038 --
2039 /* Name : reset_dates_for_run
2040 Purpose :
2041 This procedure is used in the Payroll Run to reset the request
2042 submission dates of dependent processes.
2043 */
2044 procedure reset_dates_for_run( p_asg_id in number,
2045 p_sysdate in date,
2046 p_assact_id in number)
2047 is
2048 begin
2049 --
2050 set_req_dates_for_run(p_process => 'CCA',
2051 p_asg_id => p_asg_id,
2052 p_sysdate => p_sysdate,
2053 p_assact_id => p_assact_id
2054 );
2055 --
2056 set_req_dates_for_run(p_process => 'RETRONOT_ASG',
2057 p_asg_id => p_asg_id,
2058 p_sysdate => p_sysdate,
2059 p_assact_id => p_assact_id
2060 );
2061 --
2062 end reset_dates_for_run;
2063 --
2064 END PAY_CC_PROCESS_UTILS;