[Home] [Help]
PACKAGE BODY: APPS.PAY_RETRO_NOTIF_PKG
Source
1 package body PAY_RETRO_NOTIF_PKG as
2 /* $Header: payretno.pkb 120.26.12020000.3 2012/09/20 06:13:54 apudiped ship $ */
3 -------------------------------------------------------------------------------
4 g_package varchar2(80) := 'PAY_RETRO_NOTIF_PKG.';
5 g_traces BOOLEAN := hr_utility.debug_enabled; --See if hr_utility.traces should show
6 g_dbg BOOLEAN := FALSE; --Extra debugging messages
7
8 g_event_group pay_event_groups.event_group_id%type;
9 g_business_group_id per_business_groups.business_group_id%type;
10 g_payroll_act_id pay_payroll_actions.payroll_action_id%type;
11 g_payroll_id pay_payrolls_f.payroll_id%type;
12 g_asg_set_id hr_assignment_sets.assignment_set_id%type;
13 g_global_env pay_interpreter_pkg.t_global_env_rec;
14 g_adv_flag varchar2(5);
15 g_report_date date;
16
17
18 procedure get_pact_details (pactid in number,
19 p_asg_set_name out nocopy varchar2,
20 p_bus_grp out nocopy number,
21 p_payroll out nocopy number,
22 p_evt_grp out nocopy number,
23 p_adv_flag out nocopy varchar2,
24 p_report_date out nocopy date)
25 is
26 l_payroll_id number;
27 l_evt_grp_id number;
28 l_legparam pay_payroll_actions.legislative_parameters%type;
29 l_asg_set_name pay_payroll_actions.legislative_parameters%type;
30 l_bus_grp number;
31 l_adv_flag varchar2(1) := 'N';
32 l_report_date date;
33 begin
34 select legislative_parameters,
35 business_group_id,
36 nvl(to_date( pay_core_utils.get_parameter('REPORT_DATE',
37 l_legparam)
38 ,'DD/MM/YYYYHH24:MI:SS'),
39 effective_date)
40 into l_legparam,
41 l_bus_grp,
42 l_report_date
43 from pay_payroll_actions
44 where payroll_action_id = pactid;
45 --
46 l_payroll_id := pay_core_utils.get_parameter('PAYROLL_ID', l_legparam);
47 l_asg_set_name := pay_core_utils.get_parameter('ASG_SET', l_legparam)||'_'||pactid;
48 l_evt_grp_id := pay_core_utils.get_parameter('EVT_GRP_ID', l_legparam);
49 l_adv_flag := pay_core_utils.get_parameter('ADV_FLAG', l_legparam);
50 --
51 p_asg_set_name := l_asg_set_name;
52 p_payroll := l_payroll_id;
53 p_bus_grp := l_bus_grp;
54 p_evt_grp := l_evt_grp_id;
55 if (l_adv_flag is null) then
56 l_adv_flag := 'N';
57 end if;
58 p_adv_flag := l_adv_flag;
59 p_report_date := l_report_date;
60 --
61 if (g_traces) then
62 hr_utility.trace('Full param string: '||l_legparam);
63 hr_utility.trace('Got report date in get_pact_details '
64 ||to_char(l_report_date,'DD-MON-YYYY HH24:MI:SS'));
65 end if;
66
67 end get_pact_details;
68
69
70 procedure get_asg_set_id (p_asg_set_name in varchar2,
71 p_payroll in number,
72 p_asg_set_id out nocopy number)
73 is
74 l_asg_set_id number;
75 begin
76 --
77 select assignment_set_id
78 into l_asg_set_id
79 from hr_assignment_sets
80 where assignment_set_name = p_asg_set_name
81 and payroll_id = p_payroll;
82 --
83 p_asg_set_id := l_asg_set_id;
84 --
85 exception when no_data_found then
86 p_asg_set_id := -1;
87 --
88 end get_asg_set_id;
89 --
90 procedure validate_asg_set (p_asg_set in varchar2) IS
91 --
92 cursor c_set_check is
93 SELECT 'X'
94 FROM hr_assignment_sets
95 WHERE UPPER(assignment_set_name) = UPPER(p_asg_set);
96 --
97 l_dummy VARCHAR2(1);
98 --
99 begin
100 --
101 open c_set_check;
102 fetch c_set_check into l_dummy;
103 if c_set_check%FOUND then
104 hr_utility.set_message(801, 'HR_6395_SETUP_SET_EXISTS');
105 hr_utility.raise_error;
106 end if;
107 close c_set_check;
108 --
109 end validate_asg_set;
110 --
111 procedure run_report (p_payroll_action_id in number,
112 p_adv_flag in varchar2)
113 --
114 is
115 l_wait_outcome BOOLEAN;
116 l_phase VARCHAR2(80);
117 l_status VARCHAR2(80);
118 l_dev_phase VARCHAR2(80);
119 l_dev_status VARCHAR2(80);
120 l_message VARCHAR2(80);
121 l_errbuf VARCHAR2(240);
122 l_req_id NUMBER;
123 --
124 l_copies_buffer varchar2(80) := null;
125 l_print_buffer varchar2(80) := null;
126 l_printer_buffer varchar2(80) := null;
127 l_style_buffer varchar2(80) := null;
128 l_save_buffer boolean := null;
129 l_save_result varchar2(1) := null;
130 c_req_id VARCHAR2(80) := NULL; /* Request Id of the main request */
131 l_dummy BOOLEAN;
132 --
133 zero_req_id Exception;
134 pragma exception_init(zero_req_id, -9999);
135 --
136 begin
137
138 c_req_id:=fnd_profile.value('CONC_REQUEST_ID');
139 l_print_buffer:= fnd_profile.value('CONC_PRINT_TOGETHER');
140
141 select number_of_copies,
142 printer,
143 print_style,
144 save_output_flag
145 into l_copies_buffer,
146 l_printer_buffer,
147 l_style_buffer,
148 l_save_result
149 from fnd_concurrent_requests
150 where request_id = to_number(c_req_id);
151
152 if (l_save_result='Y') then
153 l_save_buffer:=true;
154 elsif (l_save_result='N') then
155 l_save_buffer:=false;
156 else
157 l_save_buffer:=NULL;
158 end if;
159
160 l_dummy := FND_REQUEST.set_print_options(
161 printer => l_printer_buffer,
162 style => l_style_buffer,
163 copies => l_copies_buffer,
164 save_output => l_save_buffer,
165 print_together => l_print_buffer);
166
167
168 l_req_id := fnd_request.submit_request(
169 application => 'PAY',
170 program => 'PYXMLRNP3',
171 sub_request => FALSE,
172 argument1 => p_payroll_action_id);
173
174 IF l_req_id = 0 THEN
175 fnd_message.retrieve(l_errbuf);
176 hr_utility.trace('Error when submitting request: ' || SQLERRM || ' ' || SQLCODE);
177 raise zero_req_id;
178 ELSE
179 --
180 if p_adv_flag = 'Y' then
181 update fnd_concurrent_requests
182 set output_file_type = 'XML'
183 where request_id = l_req_id;
184 end if;
185 --
186 COMMIT;
187 --
188 l_wait_outcome := FND_CONCURRENT.WAIT_FOR_REQUEST(
189 request_id => l_req_id,
190 interval => 30,
191 max_wait => 86400,
192 phase => l_phase,
193 status => l_status,
194 dev_phase => l_dev_phase,
195 dev_status => l_dev_status,
196 message => l_message);
197 --
198 -- IF (l_dev_phase = 'COMPLETE' and l_status = 'NORMAL') THEN
199 -- update fnd_concurrent_requests
200 -- set PARENT_REQUEST_ID = to_number(c_req_id)
201 -- where request_id = l_req_id;
202 -- ELSE
203 -- hr_utility.set_message(801, 'HR_51002_REPORT_CANT_SUBMITTED');
204 -- hr_utility.raise_error;
205 -- END IF;
206 --
207 END IF;
208
209 exception
210 when zero_req_id then
211 hr_utility.set_message(801, 'HR_51002_REPORT_CANT_SUBMITTED');
212 hr_utility.raise_error;
213 when others then
214 l_errbuf := SQLERRM;
215 hr_utility.trace('Error when submitting request: ' || SQLERRM || ' ' || SQLCODE);
216 hr_utility.set_message(801, 'HR_51002_REPORT_CANT_SUBMITTED');
217 hr_utility.raise_error;
218
219 end run_report;
220 --
221 procedure create_retro_asg_set(p_asg_set_name in varchar2,
222 p_business_group_id in number,
223 p_payroll_id in number) is
224 --
225 cursor c_sequence is
226 SELECT hr_assignment_sets_s.nextval
227 FROM dual;
228 --
229 l_rowid VARCHAR2(30);
230 l_asg_set_id NUMBER;
231 --
232 begin
233 --
234 validate_asg_set (p_asg_set_name);
235 --
236 open c_sequence;
237 fetch c_sequence into l_asg_set_id;
238 close c_sequence;
239 --
240 hr_assignment_sets_pkg.insert_row(
241 p_rowid => l_rowid
242 , p_assignment_set_id => l_asg_set_id
243 , p_business_group_id => p_business_group_id
244 , p_payroll_id => p_payroll_id
245 , p_assignment_set_name => p_asg_set_name
246 , p_formula_id => null);
247 --
248 end create_retro_asg_set;
249 --
250 -------------------------------------------------------------------------------
251 Procedure get_asg_info(
252 p_assignment_id IN NUMBER
253 , p_report_date IN DATE
254 , p_business_group_id IN NUMBER
255 , p_legislation_code IN VARCHAR2
256 , p_asg_status OUT NOCOPY VARCHAR2
257 , p_person_name OUT NOCOPY VARCHAR2) is
258 --
259 l_asg_status VARCHAR2(80) := NULL;
260 l_person_name VARCHAR2(240) := NULL;
261 --
262 Begin
263 --
264 SELECT astTL.user_status
265 , ppf.full_name
266 INTO l_asg_status
267 , l_person_name
268 FROM per_assignments_f paf
269 , per_assignment_status_types ast
270 , per_assignment_status_types_tl astTL
271 , per_people_f ppf
272 WHERE paf.assignment_id = p_assignment_id
273 AND paf.business_group_id = p_business_group_id
274 AND paf.person_id = ppf.person_id
275 AND ppf.business_group_id = p_business_group_id
276 AND paf.assignment_status_type_id = ast.assignment_status_type_id
277 AND (ast.business_group_id = p_business_group_id
278 OR (ast.business_group_id IS NULL
279 AND ast.legislation_code = p_legislation_code)
280 OR (ast.business_group_id IS NULL
281 AND ast.legislation_code IS NULL))
282 AND ast.assignment_status_type_id = astTL.assignment_status_type_id
283 AND astTL.language = userenv('LANG')
284 AND p_report_date BETWEEN paf.effective_start_date
285 AND paf.effective_end_date
286 AND p_report_date BETWEEN ppf.effective_start_date
287 AND ppf.effective_end_date;
288 --
289 p_asg_status := l_asg_status;
290 p_person_name := l_person_name;
291 --
292 EXCEPTION
293 WHEN OTHERS THEN NULL;
294 --
295 End get_asg_info;
296 -------------------------------------------------------------------------------
297 Procedure get_ele_info(
298 p_element_entry_id IN NUMBER
299 , p_report_date IN DATE
300 , p_business_group_id IN NUMBER
301 , p_legislation_code IN VARCHAR2
302 , p_element_name OUT NOCOPY VARCHAR2) is
303 --
304 l_element_name VARCHAR2(80) := NULL;
305 --
306 Cursor c_ins_upd_ele is
307 SELECT petTL.element_name element
308 FROM pay_element_types_f_tl petTL
309 , pay_element_types_f pet
310 , pay_element_links_f pel
311 , pay_element_entries_f pef
312 WHERE pef.element_entry_id = p_element_entry_id
313 AND pef.element_link_id = pel.element_link_id
314 AND pel.business_group_id = p_business_group_id
315 AND pel.element_type_id = pet.element_type_id
316 AND pet.element_type_id = petTL.element_type_id
317 AND petTL.language = userenv('LANG')
318 AND (pet.business_group_id = p_business_group_id
319 OR (pet.business_group_id IS NULL
320 AND pet.legislation_code = p_legislation_code)
321 OR (pet.business_group_id IS NULL
322 AND pet.legislation_code IS NULL))
323 AND p_report_date BETWEEN pef.effective_start_date
324 AND pef.effective_end_date
325 AND p_report_date BETWEEN pel.effective_start_date
326 AND pel.effective_end_date
327 AND p_report_date BETWEEN pet.effective_start_date
328 AND pet.effective_end_date;
329 --
330 Cursor c_del_ele is
331 SELECT petTL.element_name
332 FROM pay_element_types_f_tl petTL
333 , pay_element_types_f pet
334 , pay_run_results prr
335 WHERE prr.source_id = p_element_entry_id
336 AND prr.source_type = 'E'
337 AND prr.element_type_id = pet.element_type_id
338 AND pet.element_type_id = petTL.element_type_id
339 AND petTL.language = userenv('LANG')
340 AND (pet.business_group_id = p_business_group_id
341 OR (pet.business_group_id IS NULL
342 AND pet.legislation_code = p_legislation_code)
343 OR (pet.business_group_id IS NULL
344 AND pet.legislation_code IS NULL))
345 AND p_report_date BETWEEN pet.effective_start_date
346 AND pet.effective_end_date;
347 --
348 Begin
349 --
350 open c_ins_upd_ele;
351 fetch c_ins_upd_ele into l_element_name;
352 if c_ins_upd_ele%NOTFOUND then
353 open c_del_ele;
354 fetch c_del_ele into l_element_name;
355 if c_del_ele%NOTFOUND then
356 close c_del_ele;
357 end if;
358 close c_del_ele;
359 end if;
360 close c_ins_upd_ele;
361 --
362 p_element_name := l_element_name;
363 --
364 EXCEPTION
365 WHEN OTHERS THEN NULL;
366 --
367 End get_ele_info;
368 -------------------------------------------------------------
369
370 procedure process_assignment (p_assignment_id in number,
371 p_report_date in date,
372 p_event_group in number,
373 p_business_group_id in number,
374 p_payroll_act_id in number,
375 p_payroll_id in number,
376 p_asg_set_id in number,
377 p_min_creation_date in date,
378 p_time_processing_started in date,
379 p_global_env in out nocopy pay_interpreter_pkg.t_global_env_rec,
380 p_debug_flag in boolean,
381 p_adv_flag in varchar2 default 'N'
382 )
383 is
384 --
385 Cursor c_ele (p_asg NUMBER,
386 p_min DATE,
387 p_max DATE,
388 p_event_group_id number) is
389 SELECT /*+ ORDERED INDEX(PDE PAY_DATETRACKED_EVENTS_UK1)
390 INDEX(PPA PAY_PAYROLL_ACTIONS_PK)
391 USE_NL(PDE)*/
392 DISTINCT
393 prr.source_id entry,
394 pde.datetracked_event_id
395 FROM pay_assignment_actions paa
396 , pay_payroll_actions ppa
397 , pay_run_results prr
398 , pay_datetracked_events pde
399 WHERE prr.source_type = 'E'
400 AND prr.assignment_action_id = paa.assignment_action_id
401 AND paa.assignment_id = p_asg
402 AND paa.payroll_action_id = ppa.payroll_action_id
403 AND ppa.business_group_id = p_business_group_id
404 AND ppa.action_type in ('R', 'Q', 'B', 'V')
405 AND pde.event_group_id = p_event_group_id
406 AND ppa.date_earned IS NOT NULL
407 AND (ppa.date_earned BETWEEN p_min AND p_max
408 OR ppa.effective_date BETWEEN p_min AND p_max)
409 /* Make sure that the Entry is not a Retropay Entry */
410 AND NOT EXISTS (select ''
411 from pay_element_entries_f pee
412 where pee.element_entry_id = prr.source_id
413 and nvl(pee.creator_type, 'F') in ('EE', 'RR', 'PR', 'NR')
414 )
415 UNION
416 SELECT /*+ ORDERED INDEX(PDE PAY_DATETRACKED_EVENTS_UK1)
417 INDEX(PET PAY_ELEMENT_TYPES_F_PK)
418 INDEX(PAF PER_ASSIGNMENTS_F_PK)
419 USE_NL(PDE PAF)*/
420 DISTINCT
421 pee.element_entry_id entry,
422 pde.datetracked_event_id
423 FROM pay_element_entries_f pee
424 , pay_datetracked_events pde
425 WHERE pee.assignment_id = p_asg
426 AND pde.event_group_id = p_event_group_id
427 /* Make sure that the Entry is not a Retropay Entry */
428 AND nvl(pee.creator_type, 'F') not in ('EE', 'RR', 'PR', 'NR')
429 AND pee.effective_start_date <= p_max
430 AND pee.effective_end_date >= p_min
431 AND exists (select /*+ ORDERED INDEX(paa PAY_ASSIGNMENT_ACTIONS_N51)
432 INDEX(ppa PAY_PAYROLL_ACTIONS_PK)
433 USE_NL(paa ppa) */
434 ''
435 from pay_assignment_actions paa,
436 pay_payroll_actions ppa,
437 per_time_periods ptp
438 where ppa.payroll_action_id = paa.payroll_action_id
439 and paa.assignment_id = pee.assignment_id
440 and paa.action_status not in ('E', 'M', 'U')
441 and ppa.action_type in ('R', 'Q', 'B', 'V')
442 and (ppa.date_earned BETWEEN p_min AND p_max
443 OR ppa.effective_date BETWEEN p_min AND p_max)
444 and ppa.payroll_id = ptp.payroll_id
445 and ppa.date_earned between ptp.start_date
446 and ptp.end_date
447 and pee.effective_start_date <= ptp.end_date
448 and pee.effective_end_date >= ptp.start_date
449 )
450 ORDER BY 2;
451 --
452 Cursor c_ele_adv (cp_asg NUMBER,
453 cp_min_ed DATE,
454 cp_max_ed DATE,
455 p_event_group_id number) is
456 SELECT /*+ ORDERED INDEX(PDE PAY_DATETRACKED_EVENTS_UK1)
457 INDEX(PPA PAY_PAYROLL_ACTIONS_PK)
458 USE_NL(PDE)*/
459 DISTINCT
460 prr.source_id entry,
461 prr.element_type_id type,
462 pde.datetracked_event_id,
463 p_event_group_id
464 FROM pay_assignment_actions paa
465 , pay_payroll_actions ppa
466 , pay_run_results prr
467 , pay_datetracked_events pde
468 WHERE prr.source_type = 'E'
469 AND prr.assignment_action_id = paa.assignment_action_id
470 AND prr.element_type_id = prr.element_type_id
471 AND paa.assignment_id = cp_asg
472 AND paa.payroll_action_id = ppa.payroll_action_id
473 -- Only bring back a row if an event group is
474 -- supplied to the process or a recalc one
475 -- is on the element
476 AND pde.event_group_id = p_event_group_id
477 AND ppa.business_group_id = p_business_group_id
478 AND ppa.action_type in ('R', 'Q', 'B', 'V')
479 AND ppa.date_earned IS NOT NULL
480 /* Make sure that the Entry is not a Retropay Entry */
481 AND NOT EXISTS (select ''
482 from pay_element_entries_f pee
483 where pee.element_entry_id = prr.source_id
484 and nvl(pee.creator_type, 'F') in ('EE', 'RR', 'PR', 'NR')
485 )
486 AND (ppa.date_earned BETWEEN cp_min_ed AND cp_max_ed
487 OR ppa.effective_date BETWEEN cp_min_ed AND cp_max_ed)
488 UNION
489 SELECT /*+ ORDERED INDEX(PDE PAY_DATETRACKED_EVENTS_UK1)
490 USE_NL(PDE)*/
491 DISTINCT
492 pee.element_entry_id entry,
493 pee.element_type_id type,
494 pde.datetracked_event_id,
495 p_event_group_id
496 FROM pay_element_entries_f pee
497 , pay_datetracked_events pde
498 WHERE pee.assignment_id = cp_asg
499 -- Only bring back a row if an event group is
500 -- supplied to the process or a recalc one
501 -- is on the element
502 AND pde.event_group_id = p_event_group_id
503 /* Make sure that the Entry is not a Retropay Entry */
504 AND nvl(pee.creator_type, 'F') not in ('EE', 'RR', 'PR', 'NR')
505 AND pee.effective_start_date <= cp_max_ed
506 AND pee.effective_end_date >= cp_min_ed
507 AND exists (select ''
508 from pay_assignment_actions paa,
509 pay_payroll_actions ppa,
510 per_time_periods ptp
511 where ppa.payroll_action_id = paa.payroll_action_id
512 and paa.assignment_id = pee.assignment_id
513 and paa.action_status not in ('E', 'M', 'U')
514 and ppa.action_type in ('R', 'Q', 'B', 'V')
515 and (ppa.date_earned BETWEEN cp_min_ed AND cp_max_ed
516 OR ppa.effective_date BETWEEN cp_min_ed AND cp_max_ed)
517 and ppa.payroll_id = ptp.payroll_id
518 and ppa.date_earned between ptp.start_date
519 and ptp.end_date
520 and pee.effective_start_date <= ptp.end_date
521 and pee.effective_end_date >= ptp.start_date
522 )
523 ORDER BY 1, 2;
524 --
525 /*For Bug 11781204 Removed the Hints to this cursor to enhance performance
526 refer the trace files in the bug for further info*/
527
528 /*For Bug 14532162 Added the Hints in the Query after the UNION ALL
529 as the performance is getting degraded after the fix 11781204 */
530
531 Cursor c_ele_adv_neg (cp_asg NUMBER,
532 cp_min_ed DATE,
533 cp_max_ed DATE,
534 p_event_group_id number) is
535 SELECT DISTINCT
536 prr.source_id entry,
537 pet.element_type_id type,
538 pde.datetracked_event_id,
539 nvl(pet.recalc_event_group_id, -1) event_group_id
540 FROM pay_assignment_actions paa
541 , pay_payroll_actions ppa
542 , pay_run_results prr
543 , pay_element_types_f pet
544 , pay_datetracked_events pde
545 WHERE prr.source_type = 'E'
546 AND prr.assignment_action_id = paa.assignment_action_id
547 AND prr.element_type_id = pet.element_type_id
548 AND paa.assignment_id = cp_asg
549 AND paa.payroll_action_id = ppa.payroll_action_id
550 -- Only bring back a row if an event group is
551 -- supplied to the process or a recalc one
552 -- is on the element
553 AND pde.event_group_id = nvl(pet.recalc_event_group_id, -1)
554 AND ppa.business_group_id = p_business_group_id
555 AND ppa.action_type in ('R', 'Q', 'B', 'V')
556 AND ppa.date_earned IS NOT NULL
557 /* Make sure that the Entry is not a Retropay Entry */
558 AND NOT EXISTS (select ''
559 from pay_element_entries_f pee
560 where pee.element_entry_id = prr.source_id
561 and nvl(pee.creator_type, 'F') in ('EE', 'RR', 'PR', 'NR')
562 )
563 AND (ppa.date_earned BETWEEN cp_min_ed AND cp_max_ed
564 OR ppa.effective_date BETWEEN cp_min_ed AND cp_max_ed)
565 UNION
566 SELECT /*+ ORDERED INDEX(PDE PAY_DATETRACKED_EVENTS_UK1)
567 INDEX(PET PAY_ELEMENT_TYPES_F_PK)
568 USE_NL(PDE PET)*/
569 DISTINCT
570 pee.element_entry_id entry,
571 pet.element_type_id type,
572 pde.datetracked_event_id,
573 nvl(pet.recalc_event_group_id, -1) event_group_id
574 FROM pay_element_entries_f pee
575 , pay_element_types_f pet
576 , pay_datetracked_events pde
577 WHERE pee.assignment_id = cp_asg
578 AND pee.element_type_id = pet.element_type_id
579 -- Only bring back a row if an event group is
580 -- supplied to the process or a recalc one
581 -- is on the element
582 AND pde.event_group_id = nvl(pet.recalc_event_group_id, -1)
583 /* Make sure that the Entry is not a Retropay Entry */
584 AND nvl(pee.creator_type, 'F') not in ('EE', 'RR', 'PR', 'NR')
585 AND pee.effective_start_date <= cp_max_ed
586 AND pee.effective_end_date >= cp_min_ed
587 AND exists (select ''
588 from pay_assignment_actions paa,
589 pay_payroll_actions ppa,
590 per_time_periods ptp
591 where ppa.payroll_action_id = paa.payroll_action_id
592 and paa.assignment_id = pee.assignment_id
593 and paa.action_status not in ('E', 'M', 'U')
594 and ppa.action_type in ('R', 'Q', 'B', 'V')
595 and (ppa.date_earned BETWEEN cp_min_ed AND cp_max_ed
596 OR ppa.effective_date BETWEEN cp_min_ed AND cp_max_ed)
597 and ppa.payroll_id = ptp.payroll_id
598 and ppa.date_earned between ptp.start_date
599 and ptp.end_date
600 and pee.effective_start_date <= ptp.end_date
601 and pee.effective_end_date >= ptp.start_date
602 )
603 ORDER BY 1, 2;
604 --
605 type t_element_entry_id is table of
606 pay_element_entries_f.element_entry_id%type
607 index by binary_integer;
608 type t_element_type_id is table of
609 pay_element_entries_f.element_type_id%type
610 index by binary_integer;
611 type t_datetracked_evt_id is table of
612 pay_datetracked_events.datetracked_event_id%type
613 index by binary_integer;
614 type t_retro_component_id is table of
615 pay_retro_components.retro_component_id%type
616 index by binary_integer;
617 type t_event_group_id is table of
618 pay_event_groups.event_group_id%type
619 index by binary_integer;
620 --
621 l_entry_id t_element_entry_id;
622 l_type_id t_element_type_id;
623 l_ele_type_id t_element_type_id; --temp store
624 l_datetracked_evt_id t_datetracked_evt_id;
625 l_retro_component_id t_retro_component_id;
626 l_ret_comp_id t_retro_component_id; --temp store
627 l_event_group_id t_event_group_id;
628 l_min_run_eff_date date;
629 l_min_run_ear_date date;
630 l_min_run_pro_date date;
631 l_min_eff_date date;
632 l_min_grp_eff_date date;
633 l_max_ppa_de_date date;
634 l_max_ppa_eff_date date;
635 l_detailed_output pay_interpreter_pkg.t_detailed_output_table_type;
636 l_ret_asg_id number;
637 --
638 l_reprocess_date date;
639 l_cache_date date;
640 l_cache_ef_date date;
641 --
642 /*The following variables are introduced for bug 12695674*/
643 l_payroll_id_ear_date number;
644 l_payroll_id_eff_date number;
645 l_min_eff_date_ptp date;
646 l_min_ear_date_ptp date;
647 --
648 l_proc varchar2(80) := g_package||'.process_assignment';
649 --
650 Procedure add_retro_set_assignment(
651 p_assignment_id IN NUMBER
652 , p_asg_set_id IN NUMBER) is
653 --
654 Cursor c_already_in_set is
655 SELECT 'X'
656 FROM hr_assignment_set_amendments
657 WHERE assignment_id = p_assignment_id
658 AND assignment_set_id = p_asg_set_id
659 AND include_or_exclude = 'I';
660 --
661 l_rowid VARCHAR2(30);
662 l_dummy VARCHAR2(1);
663 --
664 Begin
665 --
666 Open c_already_in_set;
667 Fetch c_already_in_set into l_dummy;
668 If c_already_in_set%NOTFOUND then
669 hr_assignment_set_amds_pkg.insert_row(
670 p_rowid => l_rowid
671 , p_assignment_id => p_assignment_id
672 , p_assignment_set_id => p_asg_set_id
673 , p_include_or_exclude => 'I');
674 End if;
675 Close c_already_in_set;
676 --
677 End add_retro_set_assignment;
678 --
679 Procedure retro_table_insert(
680 p_assignment_id IN NUMBER
681 , p_element_entry_id IN NUMBER
682 , p_date_processed IN DATE
683 , p_date_earned IN DATE
684 , p_change_type IN VARCHAR2
685 , p_asg_set_id IN NUMBER) is
686 --
687 Begin
688 --
689 INSERT INTO pay_retro_notif_reports
690 ( report_id
691 , payroll_id
692 , report_date
693 , assignment_id
694 , element_entry_id
695 , event_group_id
696 , date_processed
697 , date_earned
698 , change_type
699 , assignment_set_id
700 , business_group_id
701 )
702 VALUES
703 ( p_payroll_act_id
704 , p_payroll_id
705 , p_report_date
706 , p_assignment_id
707 , p_element_entry_id
708 , p_event_group
709 , p_date_processed
710 , p_date_earned
711 , p_change_type
712 , p_asg_set_id
713 , p_business_group_id
714 );
715 --
716 End retro_table_insert;
717 --
718 BEGIN
719 --
720 l_detailed_output.delete;
721 l_ret_asg_id := null;
722 --
723 /* Find the min effective date so that we know
724 which entries to reprocess for
725 */
726 select /*+ INDEX(ppe PAY_PROCESS_EVENTS_N3) use_nl(ppe peu pdt)
727 ORDERED */
728 min(decode(peu.event_type,
729 'U', decode(peu.column_name,
730 pdt.end_date_name, ppe.effective_date +1,
731 ppe.effective_date
732 ),
733 ppe.effective_date)
734 )
735 into l_min_eff_date
736 from pay_process_events ppe,
737 pay_event_updates peu,
738 pay_dated_tables pdt
739 where ppe.assignment_id = p_assignment_id
740 and ppe.creation_date between p_min_creation_date
741 and p_time_processing_started
742 and peu.event_update_id = ppe.event_update_id
743 and peu.dated_table_id = pdt.dated_table_id;
744 --
745 select /*+ INDEX(ppe PAY_PROCESS_EVENTS_N3) use_nl(ppe peu pdt)
746 ORDERED */
747 min(decode(peu.event_type,
748 'U', decode(peu.column_name,
749 pdt.end_date_name, ppe.effective_date +1,
750 ppe.effective_date
751 ),
752 ppe.effective_date)
753 )
754 into l_min_grp_eff_date
755 from pay_process_events ppe,
756 pay_event_updates peu,
757 pay_dated_tables pdt
758 where ppe.assignment_id is null
759 and ppe.creation_date between p_min_creation_date
760 and p_time_processing_started
761 and peu.event_update_id = ppe.event_update_id
762 and peu.dated_table_id = pdt.dated_table_id;
763
764 Begin
765 --
766 /*Changes start for the bug 12695674*/
767 /*previously minimum of date earned of all the payroll actions
768 now returning the start date of the period in which minimum of date earned is present*/
769 select min_date,
770 payroll_id
771 into l_min_ear_date_ptp,
772 l_payroll_id_ear_date
773 from (
774 select min(date_earned) min_date,
775 ppa.payroll_id payroll_id
776 from pay_payroll_actions ppa,
777 pay_assignment_actions paa
778 where paa.assignment_id = p_assignment_id
779 and paa.payroll_action_id = ppa.payroll_action_id
780 and ppa.action_type in ('Q', 'R', 'B', 'V')
781 group by payroll_id
782 order by min_date asc) a
783 where rownum=1;
784
785 hr_utility.trace('The payroll id is '||l_payroll_id_ear_date);
786
787 select start_date
788 into l_min_run_ear_date
789 from per_time_periods
790 where l_min_ear_date_ptp between start_date and end_date
791 and payroll_id = l_payroll_id_ear_date;
792
793 hr_utility.trace('The start date finally calculated is '||l_min_run_ear_date);
794
795 select min_date,
796 payroll_id
797 into l_min_eff_date_ptp,
798 l_payroll_id_eff_date
799 from (
800 select min(effective_date) min_date,
801 ppa.payroll_id payroll_id
802 from pay_payroll_actions ppa,
803 pay_assignment_actions paa
804 where paa.assignment_id = p_assignment_id
805 and paa.payroll_action_id = ppa.payroll_action_id
806 and ppa.action_type in ('Q', 'R', 'B', 'V')
807 group by payroll_id
808 order by min_date asc) a
809 where rownum=1;
810
811 hr_utility.trace('The payroll id wrt eff date is '||l_payroll_id_eff_date);
812
813 select start_date
814 into l_min_run_eff_date
815 from per_time_periods
816 where l_min_eff_date_ptp between start_date and end_date
817 and payroll_id = l_payroll_id_eff_date;
818
819 hr_utility.trace('The start date wrt effective date finally calculated is '||l_min_run_eff_date);
820
821 exception
822 when no_data_found then
823 hr_utility.trace('The case comes where the assignment has no payroll actions as yet');
824 end;
825
826 /*Changes end for the bug 12695674*/
827
828 /*Modified the code for the bug 12695674*/
829 /*Introduced the above begin end block for 14292634*/
830 --
831 if (l_min_run_eff_date is null) then
832 l_min_run_eff_date := hr_api.g_eot;
833 end if;
834 if (l_min_run_ear_date is null) then
835 l_min_run_ear_date := hr_api.g_eot;
836 end if;
837 l_min_run_pro_date := least(l_min_run_eff_date, l_min_run_ear_date);
838 --
839 if (l_min_eff_date is null) then
840 if (l_min_grp_eff_date is not null) then
841 l_min_eff_date := l_min_grp_eff_date;
842 end if;
843 else
844 if (l_min_grp_eff_date is not null
845 and l_min_grp_eff_date < l_min_eff_date) then
846 l_min_eff_date := l_min_grp_eff_date;
847 end if;
848 end if;
849 --
850 if (l_min_eff_date is not null) then
851 if (l_min_eff_date < l_min_run_pro_date) then
852 l_min_eff_date := l_min_run_pro_date;
853 end if;
854 end if;
855 --
856 if (g_dbg) then
857 hr_utility.set_location(l_proc,100);
858 hr_utility.trace(' Processing ASG '||p_assignment_id);
859 hr_utility.trace(' p_min_creation_date: '||to_char(p_min_creation_date,'YYYY/MM/DD HH24:MI:SS'));
860 hr_utility.trace(' p_time_processing_started: '||to_char(p_time_processing_started,'YYYY/MM/DD HH24:MI:SS'));
861 hr_utility.trace(' l_min_eff_date: '||to_char(l_min_eff_date,'YYYY/MM/DD HH24:MI:SS'));
862 hr_utility.trace(' l_min_grp_eff_date: '||to_char(l_min_grp_eff_date,'YYYY/MM/DD HH24:MI:SS'));
863 end if;
864
865 /* only do something if there were process events */
866 --
867 if (l_min_eff_date is not null) then
868 --
869 /* Find the element entry and datetrack details needed
870 to build the PL/SQL tables
871 Note this sursor needs to used the effective dates
872 */
873 if (p_adv_flag = 'N') then
874 --
875 open c_ele(p_assignment_id,
876 l_min_eff_date,
877 hr_api.g_eot,
878 p_event_group);
879 --
880 fetch c_ele bulk collect into
881 l_entry_id,
882 l_datetracked_evt_id;
883 --
884 else
885 --
886 if (p_event_group is not null) then
887 open c_ele_adv(p_assignment_id,
888 l_min_eff_date,
889 hr_api.g_eot,
890 p_event_group);
891 --
892 fetch c_ele_adv bulk collect into
893 l_entry_id,
894 l_type_id,
895 l_datetracked_evt_id,
896 l_event_group_id;
897 --
898 else
899 --
900 open c_ele_adv_neg(p_assignment_id,
901 l_min_eff_date,
902 hr_api.g_eot,
903 p_event_group);
904 --
905 fetch c_ele_adv_neg bulk collect into
906 l_entry_id,
907 l_type_id,
908 l_datetracked_evt_id,
909 l_event_group_id;
910 end if;
911 --
912 for i in 1..l_entry_id.count loop
913
914 l_retro_component_id(i) :=
915 pay_retro_utils_pkg.get_retro_component_id
916 (l_entry_id(i),
917 trunc(sysdate), l_type_id(i),
918 p_assignment_id);
919
920 end loop;
921 --
922 end if;
923 --
924 for i in 1..l_entry_id.count loop
925 --
926 if (p_adv_flag = 'N') then
927 --
928 pay_interpreter_pkg.add_datetrack_event_to_entry
929 (p_datetracked_evt_id => l_datetracked_evt_id(i),
930 p_element_entry_id => l_entry_id(i),
931 p_global_env => p_global_env);
932 --
933 else
934 --
935 if (l_retro_component_id(i) <> -1) then
936 --
937 pay_interpreter_pkg.add_datetrack_event_to_entry
938 (p_datetracked_evt_id => l_datetracked_evt_id(i),
939 p_element_entry_id => l_entry_id(i),
940 p_global_env => p_global_env);
941 --
942 pay_interpreter_pkg.event_group_tables(l_event_group_id(i),
943 pay_interpreter_pkg.glo_monitored_events);
944 p_global_env.monitor_start_ptr := 1;
945 p_global_env.monitor_end_ptr :=
946 pay_interpreter_pkg.glo_monitored_events.count;
947 --
948 -- Also populate our table for local store of ele type id and rc_id
949 l_ele_type_id(l_entry_id(i)) := l_type_id(i);
950 l_ret_comp_id(l_entry_id(i)) := l_retro_component_id(i);
951 else
952 if (g_dbg) then
953 hr_utility.trace('>> Element has no retro_component. Not adding '||l_entry_id(i)||' to store.');
954 end if;
955 end if;
956 --
957 end if;
958 --
959 end loop;
960 --
961 if (p_adv_flag = 'N') then
962 close c_ele;
963 else
964 if (p_event_group is not null) then
965 close c_ele_adv;
966 else
967 close c_ele_adv_neg;
968 end if;
969 end if;
970 --
971 select max(ppa.date_earned),
972 max(ppa.effective_date)
973 into l_max_ppa_de_date,
974 l_max_ppa_eff_date
975 from pay_assignment_actions paa,
976 pay_payroll_actions ppa
977 where paa.assignment_id = p_assignment_id
978 and paa.action_status not in ('U', 'M', 'E')
979 and ppa.payroll_action_id = paa.payroll_action_id
980 and ppa.action_type in ('R', 'Q');
981 --
982 /* Now we have the combination of entries and events loaded
983 call the interpreter for Date Processed
984 */
985 pay_interpreter_pkg.entries_affected(
986 p_assignment_id => p_assignment_id,
987 p_mode => 'DATE_PROCESSED',
988 p_start_date => p_min_creation_date,
989 p_end_date => p_time_processing_started,
990 p_business_group_id => p_business_group_id,
991 p_global_env => p_global_env,
992 t_detailed_output => l_detailed_output
993 );
994 --
995 for cnt in 1..l_detailed_output.count loop
996 if (l_detailed_output(cnt).effective_date <= l_max_ppa_eff_date)
997 then
998 --
999 if (p_debug_flag = FALSE) then
1000 --
1001 if (p_adv_flag = 'N') then
1002 --
1003 retro_table_insert(
1004 p_assignment_id => p_assignment_id,
1005 p_element_entry_id => l_detailed_output(cnt).element_entry_id,
1006 p_date_processed => l_detailed_output(cnt).effective_date,
1007 p_date_earned => NULL,
1008 p_change_type => l_detailed_output(cnt).update_type,
1009 p_asg_set_id => p_asg_set_id);
1010 --
1011 add_retro_set_assignment(
1012 p_assignment_id => p_assignment_id,
1013 p_asg_set_id => p_asg_set_id);
1014 --
1015 else
1016 --
1017 if (l_ret_asg_id is null) then
1018 --
1019 PAY_RETRO_UTILS_PKG.maintain_retro_asg(
1020 p_asg_id => p_assignment_id
1021 ,p_payroll_id => p_payroll_id
1022 ,p_min_date => p_min_creation_date
1023 ,p_eff_date => l_detailed_output(cnt).effective_date
1024 ,p_retro_asg_id => l_ret_asg_id);
1025 --
1026 end if;
1027 --
1028 pay_retro_pkg.maintain_retro_entry(
1029 p_retro_assignment_id => l_ret_asg_id
1030 ,p_element_entry_id => l_detailed_output(cnt).element_entry_id
1031 ,p_element_type_id => l_ele_type_id(l_detailed_output(cnt).element_entry_id)
1032 ,p_reprocess_date => l_detailed_output(cnt).effective_date
1033 ,p_eff_date => l_detailed_output(cnt).effective_date
1034 ,p_retro_component_id => l_ret_comp_id(l_detailed_output(cnt).element_entry_id)
1035 -- As this is System, need to record details to differentiate
1036 -- to a User row, as the RE may get Merged in the future
1037 ,p_owner_type => 'S'
1038 ,p_system_reprocess_date => l_detailed_output(cnt).effective_date );
1039 --
1040 if (g_dbg) then
1041 hr_utility.trace('>DP >Entry Saved id = '||l_detailed_output(cnt).element_entry_id);
1042 hr_utility.trace('>DP >effective_date = '||l_detailed_output(cnt).effective_date);
1043 hr_utility.trace('>DP >update type = '||l_detailed_output(cnt).update_type);
1044 end if;
1045 end if;
1046 else
1047 -- In debug mode
1048 hr_utility.trace('>DP >Entry Saved id = '||l_detailed_output(cnt).element_entry_id);
1049 hr_utility.trace('>DP >effective_date = '||l_detailed_output(cnt).effective_date);
1050 hr_utility.trace('>DP >update type = '||l_detailed_output(cnt).update_type);
1051 end if;
1052 --
1053 end if;
1054
1055 end loop;
1056 --
1057 /* Now we have the combination of entries and events loaded
1058 call the interpreter for Date Earned
1059 */
1060 l_detailed_output.delete;
1061 l_cache_date := NULL;
1062 l_cache_ef_date := NULL;
1063 l_reprocess_date := NULL;
1064 pay_interpreter_pkg.entries_affected(
1065 p_assignment_id => p_assignment_id,
1066 p_mode => 'DATE_EARNED',
1067 p_start_date => p_min_creation_date,
1068 p_end_date => p_time_processing_started,
1069 p_business_group_id => p_business_group_id,
1070 p_global_env => p_global_env,
1071 t_detailed_output => l_detailed_output
1072 );
1073 --
1074 for cnt in 1..l_detailed_output.count loop
1075 if (l_detailed_output(cnt).effective_date <= l_max_ppa_de_date)
1076 then
1077 --
1078 if (p_debug_flag = FALSE) then
1079 --
1080 if (p_adv_flag = 'N') then
1081 --
1082 retro_table_insert(
1083 p_assignment_id => p_assignment_id,
1084 p_element_entry_id => l_detailed_output(cnt).element_entry_id,
1085 p_date_processed => null,
1086 p_date_earned => l_detailed_output(cnt).effective_date,
1087 p_change_type => l_detailed_output(cnt).update_type,
1088 p_asg_set_id => p_asg_set_id);
1089 --
1090 add_retro_set_assignment(
1091 p_assignment_id => p_assignment_id,
1092 p_asg_set_id => p_asg_set_id);
1093 --
1094 else
1095 --
1096 if (l_ret_asg_id is null) then
1097 --
1098 PAY_RETRO_UTILS_PKG.maintain_retro_asg(
1099 p_asg_id => p_assignment_id
1100 ,p_payroll_id => p_payroll_id
1101 ,p_min_date => p_min_creation_date
1102 ,p_eff_date => l_detailed_output(cnt).effective_date
1103 ,p_retro_asg_id => l_ret_asg_id);
1104 --
1105 end if;
1106 --
1107 if (l_detailed_output(cnt).effective_date <> l_cache_date
1108 or l_cache_date is null) then
1109 --
1110 begin
1111 --
1112 select min(ppa.effective_date)
1113 into l_reprocess_date
1114 from pay_payroll_actions ppa,
1115 pay_assignment_actions paa
1116 where ppa.payroll_action_id = paa.payroll_action_id
1117 and paa.assignment_id = p_assignment_id
1118 and ppa.date_earned >=
1119 l_detailed_output(cnt).effective_date
1120 and ppa.action_type in ('R','Q');
1121 --
1122 if l_reprocess_date <= l_detailed_output(cnt).effective_date then
1123 l_cache_date := l_detailed_output(cnt).effective_date;
1124 l_cache_ef_date := l_reprocess_date;
1125 else
1126 l_cache_date := l_detailed_output(cnt).effective_date;
1127 l_cache_ef_date := l_detailed_output(cnt).effective_date;
1128 end if;
1129 --
1130 exception
1131 when no_data_found then
1132 l_reprocess_date := l_detailed_output(cnt).effective_date;
1133 l_cache_ef_date := l_reprocess_date;
1134 l_cache_date := l_reprocess_date;
1135 end;
1136 --
1137 else
1138 l_reprocess_date := l_cache_ef_date;
1139 end if;
1140 --
1141 pay_retro_pkg.maintain_retro_entry(
1142 p_retro_assignment_id => l_ret_asg_id
1143 ,p_element_entry_id => l_detailed_output(cnt).element_entry_id
1144 ,p_element_type_id => l_ele_type_id(l_detailed_output(cnt).element_entry_id)
1145 ,p_reprocess_date => l_reprocess_date
1146 ,p_eff_date => l_detailed_output(cnt).effective_date
1147 ,p_retro_component_id => l_ret_comp_id(l_detailed_output(cnt).element_entry_id)
1148 ,p_owner_type => 'S'
1149 ,p_system_reprocess_date => l_reprocess_date);
1150 --
1151 if (g_dbg) then
1152 hr_utility.trace('>DE >Entry Saved id = '||l_detailed_output(cnt).element_entry_id);
1153 hr_utility.trace('>DE >effective_date = '||l_detailed_output(cnt).effective_date);
1154 hr_utility.trace('>DE >update type = '||l_detailed_output(cnt).update_type);
1155 hr_utility.trace('>DE >Reprocess Date = '||l_reprocess_date);
1156 end if;
1157 end if;
1158 --
1159 else
1160 hr_utility.trace('>DE >Entry Saved id = '||l_detailed_output(cnt).element_entry_id);
1161 hr_utility.trace('>DE >effective_date = '||l_detailed_output(cnt).effective_date);
1162 hr_utility.trace('>DE >update type = '||l_detailed_output(cnt).update_type);
1163 --
1164 end if;
1165 --
1166 end if;
1167 --
1168 end loop;
1169 --
1170 -- We have inserted all retro-entries, and stored the earliest
1171 -- effective_date for this assignment. Now update the retro_assignment
1172 -- with this date
1173 --
1174 if (l_ret_asg_id is not null) then
1175 --
1176 update pay_retro_assignments
1177 set reprocess_date = (select min(reprocess_date)
1178 from pay_retro_entries
1179 where retro_assignment_id = l_ret_asg_id),
1180 start_date = p_min_creation_date
1181 where retro_assignment_id = l_ret_asg_id;
1182 --
1183 end if;
1184 --
1185 end if;
1186 --
1187 /* now clear the caches */
1188 --
1189 pay_interpreter_pkg.clear_dt_event_for_entry
1190 (p_global_env => p_global_env);
1191 l_ele_type_id.delete;
1192 l_ret_comp_id.delete;
1193 --
1194 --
1195 if (g_traces) then
1196 hr_utility.set_location(l_proc,900);
1197 end if;
1198 end process_assignment;
1199
1200 procedure initialise_globals(p_event_group in number,
1201 p_business_group_id in number,
1202 p_payroll_action_id in number,
1203 p_payroll_id in number,
1204 p_asg_set_name in varchar2,
1205 p_adv_flag in varchar2,
1206 p_report_date in date
1207 )
1208 is
1209 begin
1210
1211 /* Setup the global area */
1212 pay_interpreter_pkg.initialise_global(g_global_env);
1213 pay_interpreter_pkg.event_group_tables(p_event_group,
1214 pay_interpreter_pkg.glo_monitored_events);
1215 g_global_env.monitor_start_ptr := 1;
1216 g_global_env.monitor_end_ptr := pay_interpreter_pkg.glo_monitored_events.count;
1217 g_global_env.datetrack_ee_tab_use := TRUE;
1218 g_global_env.validate_run_actions := TRUE;
1219 --
1220 g_event_group := p_event_group;
1221 g_business_group_id := p_business_group_id;
1222 g_payroll_act_id := p_payroll_action_id;
1223 g_payroll_id := p_payroll_id;
1224 g_adv_flag := p_adv_flag;
1225 --
1226 if (g_adv_flag = 'Y') then
1227 --
1228 -- Advanced report performs until the end of time.
1229 --
1230 g_report_date := to_date('4712/12/31', 'YYYY/MM/DD');
1231 g_asg_set_id := null;
1232 else
1233 get_asg_set_id (p_asg_set_name,
1234 g_payroll_id,
1235 g_asg_set_id);
1236 g_report_date := p_report_date;
1237 end if;
1238
1239 end initialise_globals;
1240 --
1241 /* Name : archinit
1242 Purpose : Initialise the process thread.
1243 Arguments :
1244 Notes :
1245 */
1246 procedure archinit(p_payroll_action_id in number)
1247 is
1248 l_bus_grp number;
1249 l_evt_grp number;
1250 l_payroll number;
1251 l_asg_set_name hr_assignment_sets.assignment_set_name%type;
1252 l_adv_flag varchar2(1);
1253 l_report_date date;
1254
1255 begin
1256 --
1257
1258 hr_utility.trace('In archinit');
1259
1260 get_pact_details (p_payroll_action_id,
1261 l_asg_set_name,
1262 l_bus_grp,
1263 l_payroll,
1264 l_evt_grp,
1265 l_adv_flag,
1266 l_report_date);
1267 --
1268 initialise_globals(p_event_group => l_evt_grp,
1269 p_business_group_id => l_bus_grp,
1270 p_payroll_action_id => p_payroll_action_id,
1271 p_payroll_id => l_payroll,
1272 p_asg_set_name => l_asg_set_name,
1273 p_adv_flag => l_adv_flag,
1274 p_report_date => l_report_date
1275 );
1276 --
1277 end archinit;
1278 --
1279 procedure generate_dates_and_process(p_assignment_id in number)
1280 is
1281 l_time_processing_started date;
1282 l_start_date date;
1283 l_min_creation_date date;
1284 l_old_retronot_date date;
1285 begin
1286 --
1287 -- Need to find out the dates for which the RetroNotification
1288 -- should run.
1289 --
1290 l_time_processing_started := sysdate;
1291 --
1292 begin
1293 --
1294 select start_date
1295 into l_start_date
1296 from pay_retro_assignments
1297 where assignment_id = p_assignment_id
1298 and retro_assignment_action_id is null
1299 and superseding_retro_asg_id is null;
1300 --
1301 exception
1302 when no_data_found then
1303 l_start_date := hr_api.g_eot;
1304 end;
1305 --
1306 pay_recorded_requests_pkg.get_recorded_date(
1307 p_process => 'RETRONOT_ASG',
1308 p_recorded_date => l_min_creation_date,
1309 p_attribute1 => p_assignment_id);
1310
1311 if (l_min_creation_date is not null) then
1312 --
1313 /* If this process has never run before for this
1314 assignment then we need to find the earliest
1315 date to run from.
1316 */
1317 if (l_min_creation_date = hr_api.g_sot) then
1318 select min(creation_date)
1319 into l_min_creation_date
1320 from pay_process_events
1321 where assignment_id = p_assignment_id
1322 and nvl(retroactive_status, 'P') <> 'C';
1323 end if;
1324 --
1325 if (l_start_date < l_min_creation_date) then
1326 l_min_creation_date := l_start_date;
1327 end if;
1328 --
1329 process_assignment (p_assignment_id => p_assignment_id,
1330 p_report_date => g_report_date,
1331 p_event_group => g_event_group,
1332 p_business_group_id => g_business_group_id,
1333 p_payroll_act_id => g_payroll_act_id,
1334 p_payroll_id => g_payroll_id,
1335 p_asg_set_id => g_asg_set_id,
1336 p_min_creation_date => l_min_creation_date,
1337 p_time_processing_started => l_time_processing_started,
1338 p_global_env => g_global_env,
1339 p_debug_flag => FALSE,
1340 p_adv_flag => g_adv_flag
1341 );
1342 end if;
1343 --
1344 pay_recorded_requests_pkg.set_recorded_date(
1345 p_process => 'RETRONOT_ASG',
1346 p_recorded_date => l_time_processing_started,
1347 p_recorded_date_o => l_old_retronot_date,
1348 p_attribute1 => to_char(p_assignment_id));
1349 --
1350 end generate_dates_and_process;
1351 --
1352 procedure process_action(p_assactid in number, p_effective_date in date)
1353 is
1354 --
1355 l_asg_id pay_assignment_actions.assignment_id%type;
1356 --
1357 begin
1358 --
1359 hr_utility.trace('In process_action');
1360
1361 select assignment_id
1362 into l_asg_id
1363 from pay_assignment_actions
1364 where assignment_action_id = p_assactid;
1365 --
1366 generate_dates_and_process(l_asg_id);
1367 --
1368 end process_action;
1369 --
1370 -- populate_adv_retro_tables is called from the RetroNotification Report ENh
1371 -- and is executed at Payroll level
1372 -- This run_asg_adv_retronot is called at an individual assignment level
1373 -- from the Automated RetroPay Solution
1374 procedure run_asg_adv_retronot(
1375 p_assignment_id in number,
1376 p_business_group_id in number,
1377 p_time_started in date default sysdate,
1378 p_event_group in number default null)
1379
1380 IS
1381
1382 l_proc varchar2(80) := g_package||'run_asg_adv_retronot';
1383 l_old_retronot_date date; -- debug store
1384 l_start_date date; -- existing RA date
1385 l_min_creation_date date; -- date stored for last execution
1386 l_global_env pay_interpreter_pkg.t_global_env_rec;
1387
1388 BEGIN
1389 hr_utility.set_location(l_proc,10);
1390 --
1391 initialise_globals(p_event_group => p_event_group,
1392 p_business_group_id => p_business_group_id,
1393 p_payroll_action_id => null,
1394 p_payroll_id => null,
1395 p_asg_set_name => null,
1396 p_adv_flag => 'Y',
1397 p_report_date => p_time_started
1398 );
1399 --
1400 hr_utility.set_location(l_proc,15);
1401 --
1402 generate_dates_and_process(p_assignment_id);
1403 --
1404 hr_utility.set_location(l_proc,20);
1405 --
1406 END run_asg_adv_retronot;
1407
1408
1409
1410 procedure run_debug(p_event_group in number,
1411 p_start_date in date,
1412 p_end_date in date,
1413 p_bg_id in number,
1414 p_assignment_id in number,
1415 p_rownum in number,
1416 p_adv_flag in varchar2)
1417 is
1418 --
1419 Cursor c_asg (p_bg_id in number,
1420 p_asg_id in number,
1421 p_start_date in date,
1422 p_end_date in date,
1423 p_rownum in number)
1424 is
1425 SELECT distinct assignment_id asg
1426 FROM pay_process_events ppe
1427 WHERE business_group_id = p_bg_id
1428 and assignment_id = nvl(p_asg_id, assignment_id)
1429 and creation_date between p_start_date
1430 and p_end_date
1431 and assignment_id is not null
1432 and rownum < p_rownum;
1433 --
1434 l_global_env pay_interpreter_pkg.t_global_env_rec;
1435 --
1436 begin
1437 /* Setup the global area */
1438 pay_interpreter_pkg.initialise_global(l_global_env);
1439 pay_interpreter_pkg.event_group_tables(p_event_group,
1440 pay_interpreter_pkg.glo_monitored_events);
1441 l_global_env.monitor_start_ptr := 1;
1442 l_global_env.monitor_end_ptr := pay_interpreter_pkg.glo_monitored_events.count;
1443 l_global_env.datetrack_ee_tab_use := TRUE;
1444 l_global_env.validate_run_actions := TRUE;
1445 --
1446 -- If assignment ID is null there is a group level event recorded
1447 -- this has been incorporated in to c_asg
1448 For l_asg_rec in c_asg(p_bg_id, p_assignment_id,
1449 p_start_date, p_end_date, p_rownum) loop
1450 --
1451 process_assignment (p_assignment_id => l_asg_rec.asg,
1452 p_report_date => p_end_date,
1453 p_event_group => p_event_group,
1454 p_business_group_id => p_bg_id,
1455 p_payroll_act_id => null,
1456 p_payroll_id => null,
1457 p_asg_set_id => null,
1458 p_min_creation_date => p_start_date,
1459 p_time_processing_started => p_end_date,
1460 p_global_env => l_global_env,
1461 p_debug_flag => TRUE,
1462 p_adv_flag => p_adv_flag
1463 );
1464 --
1465 end loop;
1466 end run_debug;
1467
1468 --
1469 ----------------------------------- range_cursor ----------------------------------
1470 --
1471 procedure range_cursor (pactid in number, sqlstr out nocopy varchar2) is
1472 --
1473 l_payroll_id number;
1474 l_legparam pay_payroll_actions.legislative_parameters%type;
1475 l_asg_set_name pay_payroll_actions.legislative_parameters%type;
1476 l_bus_grp number;
1477 l_adv_flag varchar2(1);
1478 --
1479 begin
1480 hr_utility.trace('In range_cursor');
1481 /* Effective date will be set to sysdate for CC*/
1482 sqlstr := 'select distinct asg.person_id
1483 from
1484 per_assignments_f asg,
1485 pay_payroll_actions pa1
1486 where pa1.payroll_action_id = :payroll_action_id
1487 and asg.payroll_id =
1488 pay_core_utils.get_parameter(''PAYROLL_ID'',
1489 pa1.legislative_parameters)
1490 and pa1.effective_date between asg.effective_start_date
1491 and asg.effective_end_date
1492 order by asg.person_id';
1493 --
1494 select legislative_parameters,
1495 business_group_id
1496 into l_legparam,
1497 l_bus_grp
1498 from pay_payroll_actions
1499 where payroll_action_id = pactid;
1500 --
1501 l_payroll_id := pay_core_utils.get_parameter('PAYROLL_ID', l_legparam);
1502 l_asg_set_name := pay_core_utils.get_parameter('ASG_SET', l_legparam)||'_'||pactid;
1503 l_adv_flag := pay_core_utils.get_parameter('ADV_FLAG', l_legparam);
1504 --
1505 -- if old style then create an assignment set
1506 if (l_adv_flag is null) then
1507 l_adv_flag := 'N';
1508 --
1509 create_retro_asg_set(l_asg_set_name,
1510 l_bus_grp,
1511 l_payroll_id);
1512 --
1513 end if;
1514
1515 hr_utility.trace('l_asg_set_name = '||l_asg_set_name);
1516 commit;
1517 hr_utility.trace('Out range_cursor');
1518 --
1519 end range_cursor;
1520 --
1521 -------------------------- action_creation ---------------------------------
1522 PROCEDURE action_creation(pactid in number,
1523 stperson in number,
1524 endperson in number,
1525 chunk in number) is
1526 CURSOR c_actions
1527 (
1528 pactid number,
1529 stperson number,
1530 endperson number
1531 ) is
1532 select /*+ ordered
1533 INDEX(paf PER_ASSIGNMENTS_N12)
1534 USE_NL(pos paf) */
1535 paf.assignment_id
1536 from
1537 per_periods_of_service pos,
1538 per_assignments_f paf,
1539 pay_payroll_actions ppa
1540 where ppa.payroll_action_id = pactid
1541 and paf.payroll_id =
1542 pay_core_utils.get_parameter('PAYROLL_ID',
1543 ppa.legislative_parameters)
1544 and pos.period_of_service_id = paf.period_of_service_id
1545 and pos.person_id = paf.person_id
1546 and pos.person_id between stperson and endperson
1547 and ppa.effective_date between paf.effective_start_date
1548 and paf.effective_end_date
1549 order by paf.assignment_id
1550 for update of paf.assignment_id, pos.period_of_service_id;
1551 --
1552 CURSOR c_get_report_type (pactid number) IS
1553
1554 SELECT report_type
1555 FROM pay_payroll_actions
1556 WHERE payroll_action_id = pactid;
1557 --
1558 CURSOR c_actions_range_on
1559 (
1560 pactid number,
1561 chunk number
1562 ) is
1563 select /*+ ordered
1564 INDEX(paf PER_ASSIGNMENTS_N12)
1565 USE_NL(pos paf) */
1566 paf.assignment_id
1567 FROM pay_population_ranges ppr,
1568 per_periods_of_service pos,
1569 per_assignments_f paf,
1570 pay_payroll_actions ppa
1571 where ppa.payroll_action_id = pactid
1572 and paf.payroll_id = pay_core_utils.get_parameter('PAYROLL_ID', ppa.legislative_parameters)
1573 and pos.period_of_service_id = paf.period_of_service_id
1574 and pos.person_id = paf.person_id
1575 AND ppa.payroll_action_id = ppr.payroll_action_id
1576 AND ppr.chunk_number = chunk
1577 and pos.person_id = ppr.person_id
1578 and ppa.effective_date between paf.effective_start_date
1579 and paf.effective_end_date
1580 order by paf.assignment_id
1581 for update of paf.assignment_id, pos.period_of_service_id;
1582 --
1583 lockingactid NUMBER;
1584 l_report_type pay_payroll_actions.report_type%type;
1585 l_range_person BOOLEAN; -- 7508169 Variable used to check if RANGE_PERSON_ID is enabled
1586
1587 --
1588 BEGIN
1589 --
1590 OPEN c_get_report_type(pactid);
1591 FETCH c_get_report_type INTO l_report_type;
1592 CLOSE c_get_report_type;
1593
1594 IF(g_traces) THEN
1595 hr_utility.trace('In action_creation');
1596 hr_utility.trace('l_report_type : '|| l_report_type);
1597 END if;
1598
1599 l_range_person := pay_ac_utility.range_person_on(
1600 p_report_type => l_report_type
1601 ,p_report_format => 'DEFAULT'
1602 ,p_report_qualifier => 'DEFAULT'
1603 ,p_report_category => 'REPORT');
1604
1605 if l_range_person THEN -- 7508169. Use the new cursor c_actions_range_on cursor to fetch the assignment_ids
1606
1607 IF(g_traces) then
1608 hr_utility.trace('l_range_person is true');
1609 END if;
1610
1611 for asgrec in c_actions_range_on(pactid, chunk) loop
1612 --
1613 SELECT pay_assignment_actions_s.nextval
1614 INTO lockingactid
1615 FROM dual;
1616 --
1617 -- insert the action record.
1618 hr_nonrun_asact.insact(lockingactid,asgrec.assignment_id,pactid,chunk, null);
1619 --
1620 end loop;
1621
1622 ELSE -- Retain Old Logic- No Range Person
1623
1624 IF(g_traces) then
1625 hr_utility.trace('l_range_person is false');
1626 END if;
1627
1628 for asgrec in c_actions(pactid, stperson, endperson) loop
1629 --
1630 SELECT pay_assignment_actions_s.nextval
1631 INTO lockingactid
1632 FROM dual;
1633 --
1634 -- insert the action record.
1635 hr_nonrun_asact.insact(lockingactid,asgrec.assignment_id,pactid,chunk, null);
1636 --
1637 end loop;
1638
1639 END IF;
1640 --
1641 END action_creation;
1642 --
1643 procedure check_retro_asg_set(p_asg_set_id IN NUMBER) is
1644 --
1645 cursor c_check_retro_set is
1646 SELECT 'X'
1647 FROM hr_assignment_set_amendments
1648 WHERE assignment_set_id = p_asg_set_id;
1649 --
1650 l_dummy VARCHAR2(1);
1651 --
1652 begin
1653 --
1654 open c_check_retro_set;
1655 fetch c_check_retro_set into l_dummy;
1656 if c_check_retro_set%NOTFOUND then
1657 DELETE FROM hr_assignment_sets
1658 WHERE assignment_set_id = p_asg_set_id;
1659 end if;
1660 close c_check_retro_set;
1661 --
1662 End check_retro_asg_set;
1663 --
1664 procedure deinitialise (pactid in number)
1665 is
1666
1667 l_bus_grp number;
1668 l_evt_grp number;
1669 l_payroll number;
1670 l_asg_set_name hr_assignment_sets.assignment_set_name%type;
1671 l_asg_set_id number;
1672 l_adv_flag varchar2(1);
1673 l_report_date date;
1674 remove_act varchar2(10);
1675 l_generate_report varchar2(10);
1676
1677 l_proc varchar2(160) := g_package||'deinitialise';
1678
1679 begin
1680 hr_utility.set_location(l_proc,10);
1681 --
1682 get_pact_details (pactid,
1683 l_asg_set_name,
1684 l_bus_grp,
1685 l_payroll,
1686 l_evt_grp,
1687 l_adv_flag,
1688 l_report_date);
1689 --
1690 --
1691 if (l_adv_flag = 'Y') then
1692 --
1693 null;
1694 --
1695 else
1696 --If its original format then just tidy up the assignment set
1697 get_asg_set_id (l_asg_set_name,
1698 l_payroll,
1699 l_asg_set_id);
1700 --
1701 check_retro_asg_set(l_asg_set_id);
1702
1703 end if;
1704 --
1705 -- Now we need to generate the report and delete the
1706 -- output if required
1707 --
1708 select pay_core_utils.get_parameter('REMOVE_ACT',
1709 pa1.legislative_parameters),
1710 pay_core_utils.get_parameter('GEN_REPORT',
1711 pa1.legislative_parameters)
1712 into remove_act,
1713 l_generate_report
1714 from pay_payroll_actions pa1
1715 where pa1.payroll_action_id = pactid;
1716 --
1717 --
1718 if (l_generate_report is null or l_generate_report = 'Y') then
1719 --
1720 -- Need to submit the report here and wait for it
1721 -- to complete
1722 --
1723 run_report(pactid,l_adv_flag);
1724 --
1725 end if;
1726 --
1727 if (remove_act is null or remove_act = 'Y') then
1728 --
1729 pay_archive.remove_report_actions(pactid);
1730 --
1731 -- Not allowing the delete of this table as this has not been
1732 -- deleted before.
1733 --
1734 -- delete from pay_retro_notif_reports
1735 -- where report_id = pactid;
1736 --
1737 end if;
1738 --
1739 hr_utility.set_location(l_proc,900);
1740 --
1741 end deinitialise;
1742 --
1743 -------------------------------------------------------------------------------
1744 Function get_person_name(
1745 p_assignment_id IN NUMBER
1746 , p_report_date IN DATE
1747 , p_business_group_id IN NUMBER
1748 , p_legislation_code IN VARCHAR2)
1749 Return varchar2 is
1750 l_asg_status VARCHAR2(80) := NULL;
1751 l_person_name VARCHAR2(240) := NULL;
1752 Begin
1753 get_asg_info(
1754 p_assignment_id
1755 , p_report_date
1756 , p_business_group_id
1757 , p_legislation_code
1758 , l_asg_status
1759 , l_person_name );
1760 Return (l_person_name);
1761 End get_person_name;
1762 -------------------------------------------------------------------------------
1763 Function get_asg_status(
1764 p_assignment_id IN NUMBER
1765 , p_report_date IN DATE
1766 , p_business_group_id IN NUMBER
1767 , p_legislation_code IN VARCHAR2)
1768
1769 Return varchar2 is
1770 l_asg_status VARCHAR2(80) := NULL;
1771 l_person_name VARCHAR2(240) := NULL;
1772 Begin
1773 get_asg_info(
1774 p_assignment_id
1775 , p_report_date
1776 , p_business_group_id
1777 , p_legislation_code
1778 , l_asg_status
1779 , l_person_name );
1780 Return l_asg_status;
1781 End get_asg_status;
1782 -------------------------------------------------------------------------------
1783
1784 --
1785 End PAY_RETRO_NOTIF_PKG;