[Home] [Help]
PACKAGE BODY: APPS.PAY_RETRO_NOTIF_PKG
Source
1 package body PAY_RETRO_NOTIF_PKG as
2 /* $Header: payretno.pkb 120.20.12010000.4 2008/11/17 10:16:47 ckesanap 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 Cursor c_ele_adv_neg (cp_asg NUMBER,
526 cp_min_ed DATE,
527 cp_max_ed DATE,
528 p_event_group_id number) is
529 SELECT /*+ ORDERED INDEX(PDE PAY_DATETRACKED_EVENTS_UK1)
530 INDEX(PET PAY_ELEMENT_TYPES_F_PK)
531 INDEX(PPA PAY_PAYROLL_ACTIONS_PK)
532 USE_NL(PDE PET)*/
533 DISTINCT
534 prr.source_id entry,
535 pet.element_type_id type,
536 pde.datetracked_event_id,
537 nvl(pet.recalc_event_group_id, -1) event_group_id
538 FROM pay_assignment_actions paa
539 , pay_payroll_actions ppa
540 , pay_run_results prr
541 , pay_element_types_f pet
542 , pay_datetracked_events pde
543 WHERE prr.source_type = 'E'
544 AND prr.assignment_action_id = paa.assignment_action_id
545 AND prr.element_type_id = pet.element_type_id
546 AND paa.assignment_id = cp_asg
547 AND paa.payroll_action_id = ppa.payroll_action_id
548 -- Only bring back a row if an event group is
549 -- supplied to the process or a recalc one
550 -- is on the element
551 AND pde.event_group_id = nvl(pet.recalc_event_group_id, -1)
552 AND ppa.business_group_id = p_business_group_id
553 AND ppa.action_type in ('R', 'Q', 'B', 'V')
554 AND ppa.date_earned IS NOT NULL
555 /* Make sure that the Entry is not a Retropay Entry */
556 AND NOT EXISTS (select ''
557 from pay_element_entries_f pee
558 where pee.element_entry_id = prr.source_id
559 and nvl(pee.creator_type, 'F') in ('EE', 'RR', 'PR', 'NR')
560 )
561 AND (ppa.date_earned BETWEEN cp_min_ed AND cp_max_ed
562 OR ppa.effective_date BETWEEN cp_min_ed AND cp_max_ed)
563 UNION
564 SELECT /*+ ORDERED INDEX(PDE PAY_DATETRACKED_EVENTS_UK1)
565 INDEX(PET PAY_ELEMENT_TYPES_F_PK)
566 USE_NL(PDE PET)*/
567 DISTINCT
568 pee.element_entry_id entry,
569 pet.element_type_id type,
570 pde.datetracked_event_id,
571 nvl(pet.recalc_event_group_id, -1) event_group_id
572 FROM pay_element_entries_f pee
573 , pay_element_types_f pet
574 , pay_datetracked_events pde
575 WHERE pee.assignment_id = cp_asg
576 AND pee.element_type_id = pet.element_type_id
577 -- Only bring back a row if an event group is
578 -- supplied to the process or a recalc one
579 -- is on the element
580 AND pde.event_group_id = nvl(pet.recalc_event_group_id, -1)
581 /* Make sure that the Entry is not a Retropay Entry */
582 AND nvl(pee.creator_type, 'F') not in ('EE', 'RR', 'PR', 'NR')
583 AND pee.effective_start_date <= cp_max_ed
584 AND pee.effective_end_date >= cp_min_ed
585 AND exists (select ''
586 from pay_assignment_actions paa,
587 pay_payroll_actions ppa,
588 per_time_periods ptp
589 where ppa.payroll_action_id = paa.payroll_action_id
590 and paa.assignment_id = pee.assignment_id
591 and paa.action_status not in ('E', 'M', 'U')
592 and ppa.action_type in ('R', 'Q', 'B', 'V')
593 and (ppa.date_earned BETWEEN cp_min_ed AND cp_max_ed
594 OR ppa.effective_date BETWEEN cp_min_ed AND cp_max_ed)
595 and ppa.payroll_id = ptp.payroll_id
596 and ppa.date_earned between ptp.start_date
597 and ptp.end_date
598 and pee.effective_start_date <= ptp.end_date
599 and pee.effective_end_date >= ptp.start_date
600 )
601 ORDER BY 1, 2;
602 --
603 type t_element_entry_id is table of
604 pay_element_entries_f.element_entry_id%type
605 index by binary_integer;
606 type t_element_type_id is table of
607 pay_element_entries_f.element_type_id%type
608 index by binary_integer;
609 type t_datetracked_evt_id is table of
610 pay_datetracked_events.datetracked_event_id%type
611 index by binary_integer;
612 type t_retro_component_id is table of
613 pay_retro_components.retro_component_id%type
614 index by binary_integer;
615 type t_event_group_id is table of
616 pay_event_groups.event_group_id%type
617 index by binary_integer;
618 --
619 l_entry_id t_element_entry_id;
620 l_type_id t_element_type_id;
621 l_ele_type_id t_element_type_id; --temp store
622 l_datetracked_evt_id t_datetracked_evt_id;
623 l_retro_component_id t_retro_component_id;
624 l_ret_comp_id t_retro_component_id; --temp store
625 l_event_group_id t_event_group_id;
626 l_min_run_eff_date date;
627 l_min_run_ear_date date;
628 l_min_run_pro_date date;
629 l_min_eff_date date;
630 l_min_grp_eff_date date;
631 l_max_ppa_de_date date;
632 l_max_ppa_eff_date date;
633 l_detailed_output pay_interpreter_pkg.t_detailed_output_table_type;
634 l_ret_asg_id number;
635 --
636 l_reprocess_date date;
637 l_cache_date date;
638 l_cache_ef_date date;
639 --
640 l_proc varchar2(80) := g_package||'.process_assignment';
641 --
642 Procedure add_retro_set_assignment(
643 p_assignment_id IN NUMBER
644 , p_asg_set_id IN NUMBER) is
645 --
646 Cursor c_already_in_set is
647 SELECT 'X'
648 FROM hr_assignment_set_amendments
649 WHERE assignment_id = p_assignment_id
650 AND assignment_set_id = p_asg_set_id
651 AND include_or_exclude = 'I';
652 --
653 l_rowid VARCHAR2(30);
654 l_dummy VARCHAR2(1);
655 --
656 Begin
657 --
658 Open c_already_in_set;
659 Fetch c_already_in_set into l_dummy;
660 If c_already_in_set%NOTFOUND then
661 hr_assignment_set_amds_pkg.insert_row(
662 p_rowid => l_rowid
663 , p_assignment_id => p_assignment_id
664 , p_assignment_set_id => p_asg_set_id
665 , p_include_or_exclude => 'I');
666 End if;
667 Close c_already_in_set;
668 --
669 End add_retro_set_assignment;
670 --
671 Procedure retro_table_insert(
672 p_assignment_id IN NUMBER
673 , p_element_entry_id IN NUMBER
674 , p_date_processed IN DATE
675 , p_date_earned IN DATE
676 , p_change_type IN VARCHAR2
677 , p_asg_set_id IN NUMBER) is
678 --
679 Begin
680 --
681 INSERT INTO pay_retro_notif_reports
682 ( report_id
683 , payroll_id
684 , report_date
685 , assignment_id
686 , element_entry_id
687 , event_group_id
688 , date_processed
689 , date_earned
690 , change_type
691 , assignment_set_id
692 , business_group_id
693 )
694 VALUES
695 ( p_payroll_act_id
696 , p_payroll_id
697 , p_report_date
698 , p_assignment_id
699 , p_element_entry_id
700 , p_event_group
701 , p_date_processed
702 , p_date_earned
703 , p_change_type
704 , p_asg_set_id
705 , p_business_group_id
706 );
707 --
708 End retro_table_insert;
709 --
710 BEGIN
711 --
712 l_detailed_output.delete;
713 l_ret_asg_id := null;
714 --
715 /* Find the min effective date so that we know
716 which entries to reprocess for
717 */
718 select /*+ INDEX(ppe PAY_PROCESS_EVENTS_N3) use_nl(ppe peu pdt)
719 ORDERED */
720 min(decode(peu.event_type,
721 'U', decode(peu.column_name,
722 pdt.end_date_name, ppe.effective_date +1,
723 ppe.effective_date
724 ),
725 ppe.effective_date)
726 )
727 into l_min_eff_date
728 from pay_process_events ppe,
729 pay_event_updates peu,
730 pay_dated_tables pdt
731 where ppe.assignment_id = p_assignment_id
732 and ppe.creation_date between p_min_creation_date
733 and p_time_processing_started
734 and peu.event_update_id = ppe.event_update_id
735 and peu.dated_table_id = pdt.dated_table_id;
736 --
737 select /*+ INDEX(ppe PAY_PROCESS_EVENTS_N3) use_nl(ppe peu pdt)
738 ORDERED */
739 min(decode(peu.event_type,
740 'U', decode(peu.column_name,
741 pdt.end_date_name, ppe.effective_date +1,
742 ppe.effective_date
743 ),
744 ppe.effective_date)
745 )
746 into l_min_grp_eff_date
747 from pay_process_events ppe,
748 pay_event_updates peu,
749 pay_dated_tables pdt
750 where ppe.assignment_id is null
751 and ppe.creation_date between p_min_creation_date
752 and p_time_processing_started
753 and peu.event_update_id = ppe.event_update_id
754 and peu.dated_table_id = pdt.dated_table_id;
755 --
756 select min(effective_date),
757 min(date_earned)
758 into l_min_run_eff_date,
759 l_min_run_ear_date
760 from pay_payroll_actions ppa,
761 pay_assignment_actions paa
762 where paa.assignment_id = p_assignment_id
763 and paa.payroll_action_id = ppa.payroll_action_id
764 and ppa.action_type in ('Q', 'R', 'B', 'V');
765 --
766 if (l_min_run_eff_date is null) then
767 l_min_run_eff_date := hr_api.g_eot;
768 end if;
769 if (l_min_run_ear_date is null) then
770 l_min_run_ear_date := hr_api.g_eot;
771 end if;
772 l_min_run_pro_date := least(l_min_run_eff_date, l_min_run_ear_date);
773 --
774 if (l_min_eff_date is null) then
775 if (l_min_grp_eff_date is not null) then
776 l_min_eff_date := l_min_grp_eff_date;
777 end if;
778 else
779 if (l_min_grp_eff_date is not null
780 and l_min_grp_eff_date < l_min_eff_date) then
781 l_min_eff_date := l_min_grp_eff_date;
782 end if;
783 end if;
784 --
785 if (l_min_eff_date is not null) then
786 if (l_min_eff_date < l_min_run_pro_date) then
787 l_min_eff_date := l_min_run_pro_date;
788 end if;
789 end if;
790 --
791 if (g_dbg) then
792 hr_utility.set_location(l_proc,100);
793 hr_utility.trace(' Processing ASG '||p_assignment_id);
794 hr_utility.trace(' p_min_creation_date: '||to_char(p_min_creation_date,'YYYY/MM/DD HH24:MI:SS'));
795 hr_utility.trace(' p_time_processing_started: '||to_char(p_time_processing_started,'YYYY/MM/DD HH24:MI:SS'));
796 hr_utility.trace(' l_min_eff_date: '||to_char(l_min_eff_date,'YYYY/MM/DD HH24:MI:SS'));
797 hr_utility.trace(' l_min_grp_eff_date: '||to_char(l_min_grp_eff_date,'YYYY/MM/DD HH24:MI:SS'));
798 end if;
799
800 /* only do something if there were process events */
801 --
802 if (l_min_eff_date is not null) then
803 --
804 /* Find the element entry and datetrack details needed
805 to build the PL/SQL tables
806 Note this sursor needs to used the effective dates
807 */
808 if (p_adv_flag = 'N') then
809 --
810 open c_ele(p_assignment_id,
811 l_min_eff_date,
812 hr_api.g_eot,
813 p_event_group);
814 --
815 fetch c_ele bulk collect into
816 l_entry_id,
817 l_datetracked_evt_id;
818 --
819 else
820 --
821 if (p_event_group is not null) then
822 open c_ele_adv(p_assignment_id,
823 l_min_eff_date,
824 hr_api.g_eot,
825 p_event_group);
826 --
827 fetch c_ele_adv bulk collect into
828 l_entry_id,
829 l_type_id,
830 l_datetracked_evt_id,
831 l_event_group_id;
832 --
833 else
834 --
835 open c_ele_adv_neg(p_assignment_id,
836 l_min_eff_date,
837 hr_api.g_eot,
838 p_event_group);
839 --
840 fetch c_ele_adv_neg bulk collect into
841 l_entry_id,
842 l_type_id,
843 l_datetracked_evt_id,
844 l_event_group_id;
845 end if;
846 --
847 for i in 1..l_entry_id.count loop
848
849 l_retro_component_id(i) :=
850 pay_retro_utils_pkg.get_retro_component_id
851 (l_entry_id(i),
852 trunc(sysdate), l_type_id(i),
853 p_assignment_id);
854
855 end loop;
856 --
857 end if;
858 --
859 for i in 1..l_entry_id.count loop
860 --
861 if (p_adv_flag = 'N') then
862 --
863 pay_interpreter_pkg.add_datetrack_event_to_entry
864 (p_datetracked_evt_id => l_datetracked_evt_id(i),
865 p_element_entry_id => l_entry_id(i),
866 p_global_env => p_global_env);
867 --
868 else
869 --
870 if (l_retro_component_id(i) <> -1) then
871 --
872 pay_interpreter_pkg.add_datetrack_event_to_entry
873 (p_datetracked_evt_id => l_datetracked_evt_id(i),
874 p_element_entry_id => l_entry_id(i),
875 p_global_env => p_global_env);
876 --
877 pay_interpreter_pkg.event_group_tables(l_event_group_id(i),
878 pay_interpreter_pkg.glo_monitored_events);
879 p_global_env.monitor_start_ptr := 1;
880 p_global_env.monitor_end_ptr :=
881 pay_interpreter_pkg.glo_monitored_events.count;
882 --
883 -- Also populate our table for local store of ele type id and rc_id
884 l_ele_type_id(l_entry_id(i)) := l_type_id(i);
885 l_ret_comp_id(l_entry_id(i)) := l_retro_component_id(i);
886 else
887 if (g_dbg) then
888 hr_utility.trace('>> Element has no retro_component. Not adding '||l_entry_id(i)||' to store.');
889 end if;
890 end if;
891 --
892 end if;
893 --
894 end loop;
895 --
896 if (p_adv_flag = 'N') then
897 close c_ele;
898 else
899 if (p_event_group is not null) then
900 close c_ele_adv;
901 else
902 close c_ele_adv_neg;
903 end if;
904 end if;
905 --
906 select max(ppa.date_earned),
907 max(ppa.effective_date)
908 into l_max_ppa_de_date,
909 l_max_ppa_eff_date
910 from pay_assignment_actions paa,
911 pay_payroll_actions ppa
912 where paa.assignment_id = p_assignment_id
913 and paa.action_status not in ('U', 'M', 'E')
914 and ppa.payroll_action_id = paa.payroll_action_id
915 and ppa.action_type in ('R', 'Q');
916 --
917 /* Now we have the combination of entries and events loaded
918 call the interpreter for Date Processed
919 */
920 pay_interpreter_pkg.entries_affected(
921 p_assignment_id => p_assignment_id,
922 p_mode => 'DATE_PROCESSED',
923 p_start_date => p_min_creation_date,
924 p_end_date => p_time_processing_started,
925 p_business_group_id => p_business_group_id,
926 p_global_env => p_global_env,
927 t_detailed_output => l_detailed_output
928 );
929 --
930 for cnt in 1..l_detailed_output.count loop
931 if (l_detailed_output(cnt).effective_date <= l_max_ppa_eff_date)
932 then
933 --
934 if (p_debug_flag = FALSE) then
935 --
936 if (p_adv_flag = 'N') then
937 --
938 retro_table_insert(
939 p_assignment_id => p_assignment_id,
940 p_element_entry_id => l_detailed_output(cnt).element_entry_id,
941 p_date_processed => l_detailed_output(cnt).effective_date,
942 p_date_earned => NULL,
943 p_change_type => l_detailed_output(cnt).update_type,
944 p_asg_set_id => p_asg_set_id);
945 --
946 add_retro_set_assignment(
947 p_assignment_id => p_assignment_id,
948 p_asg_set_id => p_asg_set_id);
949 --
950 else
951 --
952 if (l_ret_asg_id is null) then
953 --
954 PAY_RETRO_UTILS_PKG.maintain_retro_asg(
955 p_asg_id => p_assignment_id
956 ,p_payroll_id => p_payroll_id
957 ,p_min_date => p_min_creation_date
958 ,p_eff_date => l_detailed_output(cnt).effective_date
959 ,p_retro_asg_id => l_ret_asg_id);
960 --
961 end if;
962 --
963 pay_retro_pkg.maintain_retro_entry(
964 p_retro_assignment_id => l_ret_asg_id
965 ,p_element_entry_id => l_detailed_output(cnt).element_entry_id
966 ,p_element_type_id => l_ele_type_id(l_detailed_output(cnt).element_entry_id)
967 ,p_reprocess_date => l_detailed_output(cnt).effective_date
968 ,p_eff_date => l_detailed_output(cnt).effective_date
969 ,p_retro_component_id => l_ret_comp_id(l_detailed_output(cnt).element_entry_id)
970 -- As this is System, need to record details to differentiate
971 -- to a User row, as the RE may get Merged in the future
972 ,p_owner_type => 'S'
973 ,p_system_reprocess_date => l_detailed_output(cnt).effective_date );
974 --
975 if (g_dbg) then
976 hr_utility.trace('>DP >Entry Saved id = '||l_detailed_output(cnt).element_entry_id);
977 hr_utility.trace('>DP >effective_date = '||l_detailed_output(cnt).effective_date);
978 hr_utility.trace('>DP >update type = '||l_detailed_output(cnt).update_type);
979 end if;
980 end if;
981 else
982 -- In debug mode
983 hr_utility.trace('>DP >Entry Saved id = '||l_detailed_output(cnt).element_entry_id);
984 hr_utility.trace('>DP >effective_date = '||l_detailed_output(cnt).effective_date);
985 hr_utility.trace('>DP >update type = '||l_detailed_output(cnt).update_type);
986 end if;
987 --
988 end if;
989
990 end loop;
991 --
992 /* Now we have the combination of entries and events loaded
993 call the interpreter for Date Earned
994 */
995 l_detailed_output.delete;
996 l_cache_date := NULL;
997 l_cache_ef_date := NULL;
998 l_reprocess_date := NULL;
999 pay_interpreter_pkg.entries_affected(
1000 p_assignment_id => p_assignment_id,
1001 p_mode => 'DATE_EARNED',
1002 p_start_date => p_min_creation_date,
1003 p_end_date => p_time_processing_started,
1004 p_business_group_id => p_business_group_id,
1005 p_global_env => p_global_env,
1006 t_detailed_output => l_detailed_output
1007 );
1008 --
1009 for cnt in 1..l_detailed_output.count loop
1010 if (l_detailed_output(cnt).effective_date <= l_max_ppa_de_date)
1011 then
1012 --
1013 if (p_debug_flag = FALSE) then
1014 --
1015 if (p_adv_flag = 'N') then
1016 --
1017 retro_table_insert(
1018 p_assignment_id => p_assignment_id,
1019 p_element_entry_id => l_detailed_output(cnt).element_entry_id,
1020 p_date_processed => null,
1021 p_date_earned => l_detailed_output(cnt).effective_date,
1022 p_change_type => l_detailed_output(cnt).update_type,
1023 p_asg_set_id => p_asg_set_id);
1024 --
1025 add_retro_set_assignment(
1026 p_assignment_id => p_assignment_id,
1027 p_asg_set_id => p_asg_set_id);
1028 --
1029 else
1030 --
1031 if (l_ret_asg_id is null) then
1032 --
1033 PAY_RETRO_UTILS_PKG.maintain_retro_asg(
1034 p_asg_id => p_assignment_id
1035 ,p_payroll_id => p_payroll_id
1036 ,p_min_date => p_min_creation_date
1037 ,p_eff_date => l_detailed_output(cnt).effective_date
1038 ,p_retro_asg_id => l_ret_asg_id);
1039 --
1040 end if;
1041 --
1042 if (l_detailed_output(cnt).effective_date <> l_cache_date
1043 or l_cache_date is null) then
1044 --
1045 begin
1046 --
1047 select min(ppa.effective_date)
1048 into l_reprocess_date
1049 from pay_payroll_actions ppa,
1050 pay_assignment_actions paa
1051 where ppa.payroll_action_id = paa.payroll_action_id
1052 and paa.assignment_id = p_assignment_id
1053 and ppa.date_earned >=
1054 l_detailed_output(cnt).effective_date
1055 and ppa.action_type in ('R','Q');
1056 --
1057 if l_reprocess_date <= l_detailed_output(cnt).effective_date then
1058 l_cache_date := l_detailed_output(cnt).effective_date;
1059 l_cache_ef_date := l_reprocess_date;
1060 else
1061 l_cache_date := l_detailed_output(cnt).effective_date;
1062 l_cache_ef_date := l_detailed_output(cnt).effective_date;
1063 end if;
1064 --
1065 exception
1066 when no_data_found then
1067 l_reprocess_date := l_detailed_output(cnt).effective_date;
1068 l_cache_ef_date := l_reprocess_date;
1069 l_cache_date := l_reprocess_date;
1070 end;
1071 --
1072 else
1073 l_reprocess_date := l_cache_ef_date;
1074 end if;
1075 --
1076 pay_retro_pkg.maintain_retro_entry(
1077 p_retro_assignment_id => l_ret_asg_id
1078 ,p_element_entry_id => l_detailed_output(cnt).element_entry_id
1079 ,p_element_type_id => l_ele_type_id(l_detailed_output(cnt).element_entry_id)
1080 ,p_reprocess_date => l_reprocess_date
1081 ,p_eff_date => l_detailed_output(cnt).effective_date
1082 ,p_retro_component_id => l_ret_comp_id(l_detailed_output(cnt).element_entry_id)
1083 ,p_owner_type => 'S'
1084 ,p_system_reprocess_date => l_reprocess_date);
1085 --
1086 if (g_dbg) then
1087 hr_utility.trace('>DE >Entry Saved id = '||l_detailed_output(cnt).element_entry_id);
1088 hr_utility.trace('>DE >effective_date = '||l_detailed_output(cnt).effective_date);
1089 hr_utility.trace('>DE >update type = '||l_detailed_output(cnt).update_type);
1090 hr_utility.trace('>DE >Reprocess Date = '||l_reprocess_date);
1091 end if;
1092 end if;
1093 --
1094 else
1095 hr_utility.trace('>DE >Entry Saved id = '||l_detailed_output(cnt).element_entry_id);
1096 hr_utility.trace('>DE >effective_date = '||l_detailed_output(cnt).effective_date);
1097 hr_utility.trace('>DE >update type = '||l_detailed_output(cnt).update_type);
1098 --
1099 end if;
1100 --
1101 end if;
1102 --
1103 end loop;
1104 --
1105 -- We have inserted all retro-entries, and stored the earliest
1106 -- effective_date for this assignment. Now update the retro_assignment
1107 -- with this date
1108 --
1109 if (l_ret_asg_id is not null) then
1110 --
1111 update pay_retro_assignments
1112 set reprocess_date = (select min(reprocess_date)
1113 from pay_retro_entries
1114 where retro_assignment_id = l_ret_asg_id),
1115 start_date = p_min_creation_date
1116 where retro_assignment_id = l_ret_asg_id;
1117 --
1118 end if;
1119 --
1120 end if;
1121 --
1122 /* now clear the caches */
1123 --
1124 pay_interpreter_pkg.clear_dt_event_for_entry
1125 (p_global_env => p_global_env);
1126 l_ele_type_id.delete;
1127 l_ret_comp_id.delete;
1128 --
1129 --
1130 if (g_traces) then
1131 hr_utility.set_location(l_proc,900);
1132 end if;
1133 end process_assignment;
1134
1135 procedure initialise_globals(p_event_group in number,
1136 p_business_group_id in number,
1137 p_payroll_action_id in number,
1138 p_payroll_id in number,
1139 p_asg_set_name in varchar2,
1140 p_adv_flag in varchar2,
1141 p_report_date in date
1142 )
1143 is
1144 begin
1145
1146 /* Setup the global area */
1147 pay_interpreter_pkg.initialise_global(g_global_env);
1148 pay_interpreter_pkg.event_group_tables(p_event_group,
1149 pay_interpreter_pkg.glo_monitored_events);
1150 g_global_env.monitor_start_ptr := 1;
1151 g_global_env.monitor_end_ptr := pay_interpreter_pkg.glo_monitored_events.count;
1152 g_global_env.datetrack_ee_tab_use := TRUE;
1153 g_global_env.validate_run_actions := TRUE;
1154 --
1155 g_event_group := p_event_group;
1156 g_business_group_id := p_business_group_id;
1157 g_payroll_act_id := p_payroll_action_id;
1158 g_payroll_id := p_payroll_id;
1159 g_adv_flag := p_adv_flag;
1160 --
1161 if (g_adv_flag = 'Y') then
1162 --
1163 -- Advanced report performs until the end of time.
1164 --
1165 g_report_date := to_date('4712/12/31', 'YYYY/MM/DD');
1166 g_asg_set_id := null;
1167 else
1168 get_asg_set_id (p_asg_set_name,
1169 g_payroll_id,
1170 g_asg_set_id);
1171 g_report_date := p_report_date;
1172 end if;
1173
1174 end initialise_globals;
1175 --
1176 /* Name : archinit
1177 Purpose : Initialise the process thread.
1178 Arguments :
1179 Notes :
1180 */
1181 procedure archinit(p_payroll_action_id in number)
1182 is
1183 l_bus_grp number;
1184 l_evt_grp number;
1185 l_payroll number;
1186 l_asg_set_name hr_assignment_sets.assignment_set_name%type;
1187 l_adv_flag varchar2(1);
1188 l_report_date date;
1189
1190 begin
1191 --
1192
1193 hr_utility.trace('In archinit');
1194
1195 get_pact_details (p_payroll_action_id,
1196 l_asg_set_name,
1197 l_bus_grp,
1198 l_payroll,
1199 l_evt_grp,
1200 l_adv_flag,
1201 l_report_date);
1202 --
1203 initialise_globals(p_event_group => l_evt_grp,
1204 p_business_group_id => l_bus_grp,
1205 p_payroll_action_id => p_payroll_action_id,
1206 p_payroll_id => l_payroll,
1207 p_asg_set_name => l_asg_set_name,
1208 p_adv_flag => l_adv_flag,
1209 p_report_date => l_report_date
1210 );
1211 --
1212 end archinit;
1213 --
1214 procedure generate_dates_and_process(p_assignment_id in number)
1215 is
1216 l_time_processing_started date;
1217 l_start_date date;
1218 l_min_creation_date date;
1219 l_old_retronot_date date;
1220 begin
1221 --
1222 -- Need to find out the dates for which the RetroNotification
1223 -- should run.
1224 --
1225 l_time_processing_started := sysdate;
1226 --
1227 begin
1228 --
1229 select start_date
1230 into l_start_date
1231 from pay_retro_assignments
1232 where assignment_id = p_assignment_id
1233 and retro_assignment_action_id is null
1234 and superseding_retro_asg_id is null;
1235 --
1236 exception
1237 when no_data_found then
1238 l_start_date := hr_api.g_eot;
1239 end;
1240 --
1241 pay_recorded_requests_pkg.get_recorded_date(
1242 p_process => 'RETRONOT_ASG',
1243 p_recorded_date => l_min_creation_date,
1244 p_attribute1 => p_assignment_id);
1245
1246 if (l_min_creation_date is not null) then
1247 --
1248 /* If this process has never run before for this
1249 assignment then we need to find the earliest
1250 date to run from.
1251 */
1252 if (l_min_creation_date = hr_api.g_sot) then
1253 select min(creation_date)
1254 into l_min_creation_date
1255 from pay_process_events
1256 where assignment_id = p_assignment_id
1257 and nvl(retroactive_status, 'P') <> 'C';
1258 end if;
1259 --
1260 if (l_start_date < l_min_creation_date) then
1261 l_min_creation_date := l_start_date;
1262 end if;
1263 --
1264 process_assignment (p_assignment_id => p_assignment_id,
1265 p_report_date => g_report_date,
1266 p_event_group => g_event_group,
1267 p_business_group_id => g_business_group_id,
1268 p_payroll_act_id => g_payroll_act_id,
1269 p_payroll_id => g_payroll_id,
1270 p_asg_set_id => g_asg_set_id,
1271 p_min_creation_date => l_min_creation_date,
1272 p_time_processing_started => l_time_processing_started,
1273 p_global_env => g_global_env,
1274 p_debug_flag => FALSE,
1275 p_adv_flag => g_adv_flag
1276 );
1277 end if;
1278 --
1279 pay_recorded_requests_pkg.set_recorded_date(
1280 p_process => 'RETRONOT_ASG',
1281 p_recorded_date => l_time_processing_started,
1282 p_recorded_date_o => l_old_retronot_date,
1283 p_attribute1 => to_char(p_assignment_id));
1284 --
1285 end generate_dates_and_process;
1286 --
1287 procedure process_action(p_assactid in number, p_effective_date in date)
1288 is
1289 --
1290 l_asg_id pay_assignment_actions.assignment_id%type;
1291 --
1292 begin
1293 --
1294 hr_utility.trace('In process_action');
1295
1296 select assignment_id
1297 into l_asg_id
1298 from pay_assignment_actions
1299 where assignment_action_id = p_assactid;
1300 --
1301 generate_dates_and_process(l_asg_id);
1302 --
1303 end process_action;
1304 --
1305 -- populate_adv_retro_tables is called from the RetroNotification Report ENh
1306 -- and is executed at Payroll level
1307 -- This run_asg_adv_retronot is called at an individual assignment level
1308 -- from the Automated RetroPay Solution
1309 procedure run_asg_adv_retronot(
1310 p_assignment_id in number,
1311 p_business_group_id in number,
1312 p_time_started in date default sysdate,
1313 p_event_group in number default null)
1314
1315 IS
1316
1317 l_proc varchar2(80) := g_package||'run_asg_adv_retronot';
1318 l_old_retronot_date date; -- debug store
1319 l_start_date date; -- existing RA date
1320 l_min_creation_date date; -- date stored for last execution
1321 l_global_env pay_interpreter_pkg.t_global_env_rec;
1322
1323 BEGIN
1324 hr_utility.set_location(l_proc,10);
1325 --
1326 initialise_globals(p_event_group => p_event_group,
1327 p_business_group_id => p_business_group_id,
1328 p_payroll_action_id => null,
1329 p_payroll_id => null,
1330 p_asg_set_name => null,
1331 p_adv_flag => 'Y',
1332 p_report_date => p_time_started
1333 );
1334 --
1335 hr_utility.set_location(l_proc,15);
1336 --
1337 generate_dates_and_process(p_assignment_id);
1338 --
1339 hr_utility.set_location(l_proc,20);
1340 --
1341 END run_asg_adv_retronot;
1342
1343
1344
1345 procedure run_debug(p_event_group in number,
1346 p_start_date in date,
1347 p_end_date in date,
1348 p_bg_id in number,
1349 p_assignment_id in number,
1350 p_rownum in number,
1351 p_adv_flag in varchar2)
1352 is
1353 --
1354 Cursor c_asg (p_bg_id in number,
1355 p_asg_id in number,
1356 p_start_date in date,
1357 p_end_date in date,
1358 p_rownum in number)
1359 is
1360 SELECT distinct assignment_id asg
1361 FROM pay_process_events ppe
1362 WHERE business_group_id = p_bg_id
1363 and assignment_id = nvl(p_asg_id, assignment_id)
1364 and creation_date between p_start_date
1365 and p_end_date
1366 and assignment_id is not null
1367 and rownum < p_rownum;
1368 --
1369 l_global_env pay_interpreter_pkg.t_global_env_rec;
1370 --
1371 begin
1372 /* Setup the global area */
1373 pay_interpreter_pkg.initialise_global(l_global_env);
1374 pay_interpreter_pkg.event_group_tables(p_event_group,
1375 pay_interpreter_pkg.glo_monitored_events);
1376 l_global_env.monitor_start_ptr := 1;
1377 l_global_env.monitor_end_ptr := pay_interpreter_pkg.glo_monitored_events.count;
1378 l_global_env.datetrack_ee_tab_use := TRUE;
1379 l_global_env.validate_run_actions := TRUE;
1380 --
1381 -- If assignment ID is null there is a group level event recorded
1382 -- this has been incorporated in to c_asg
1383 For l_asg_rec in c_asg(p_bg_id, p_assignment_id,
1384 p_start_date, p_end_date, p_rownum) loop
1385 --
1386 process_assignment (p_assignment_id => l_asg_rec.asg,
1387 p_report_date => p_end_date,
1388 p_event_group => p_event_group,
1389 p_business_group_id => p_bg_id,
1390 p_payroll_act_id => null,
1391 p_payroll_id => null,
1392 p_asg_set_id => null,
1393 p_min_creation_date => p_start_date,
1394 p_time_processing_started => p_end_date,
1395 p_global_env => l_global_env,
1396 p_debug_flag => TRUE,
1397 p_adv_flag => p_adv_flag
1398 );
1399 --
1400 end loop;
1401 end run_debug;
1402
1403 --
1404 ----------------------------------- range_cursor ----------------------------------
1405 --
1406 procedure range_cursor (pactid in number, sqlstr out nocopy varchar2) is
1407 --
1408 l_payroll_id number;
1409 l_legparam pay_payroll_actions.legislative_parameters%type;
1410 l_asg_set_name pay_payroll_actions.legislative_parameters%type;
1411 l_bus_grp number;
1412 l_adv_flag varchar2(1);
1413 --
1414 begin
1415 hr_utility.trace('In range_cursor');
1416 /* Effective date will be set to sysdate for CC*/
1417 sqlstr := 'select distinct asg.person_id
1418 from
1419 per_assignments_f asg,
1420 pay_payroll_actions pa1
1421 where pa1.payroll_action_id = :payroll_action_id
1422 and asg.payroll_id =
1423 pay_core_utils.get_parameter(''PAYROLL_ID'',
1424 pa1.legislative_parameters)
1425 and pa1.effective_date between asg.effective_start_date
1426 and asg.effective_end_date
1427 order by asg.person_id';
1428 --
1429 select legislative_parameters,
1430 business_group_id
1431 into l_legparam,
1432 l_bus_grp
1433 from pay_payroll_actions
1434 where payroll_action_id = pactid;
1435 --
1436 l_payroll_id := pay_core_utils.get_parameter('PAYROLL_ID', l_legparam);
1437 l_asg_set_name := pay_core_utils.get_parameter('ASG_SET', l_legparam)||'_'||pactid;
1438 l_adv_flag := pay_core_utils.get_parameter('ADV_FLAG', l_legparam);
1439 --
1440 -- if old style then create an assignment set
1441 if (l_adv_flag is null) then
1442 l_adv_flag := 'N';
1443 --
1444 create_retro_asg_set(l_asg_set_name,
1445 l_bus_grp,
1446 l_payroll_id);
1447 --
1448 end if;
1449
1450 hr_utility.trace('l_asg_set_name = '||l_asg_set_name);
1451 commit;
1452 hr_utility.trace('Out range_cursor');
1453 --
1454 end range_cursor;
1455 --
1456 -------------------------- action_creation ---------------------------------
1457 PROCEDURE action_creation(pactid in number,
1458 stperson in number,
1459 endperson in number,
1460 chunk in number) is
1461 CURSOR c_actions
1462 (
1463 pactid number,
1464 stperson number,
1465 endperson number
1466 ) is
1467 select /*+ ordered
1468 INDEX(paf PER_ASSIGNMENTS_N12)
1469 USE_NL(pos paf) */
1470 paf.assignment_id
1471 from
1472 per_periods_of_service pos,
1473 per_assignments_f paf,
1474 pay_payroll_actions ppa
1475 where ppa.payroll_action_id = pactid
1476 and paf.payroll_id =
1477 pay_core_utils.get_parameter('PAYROLL_ID',
1478 ppa.legislative_parameters)
1479 and pos.period_of_service_id = paf.period_of_service_id
1480 and pos.person_id = paf.person_id
1481 and pos.person_id between stperson and endperson
1482 and ppa.effective_date between paf.effective_start_date
1483 and paf.effective_end_date
1484 order by paf.assignment_id
1485 for update of paf.assignment_id, pos.period_of_service_id;
1486 --
1487 CURSOR c_get_report_type (pactid number) IS
1488
1489 SELECT report_type
1490 FROM pay_payroll_actions
1491 WHERE payroll_action_id = pactid;
1492 --
1493 CURSOR c_actions_range_on
1494 (
1495 pactid number,
1496 chunk number
1497 ) is
1498 select /*+ ordered
1499 INDEX(paf PER_ASSIGNMENTS_N12)
1500 USE_NL(pos paf) */
1501 paf.assignment_id
1502 FROM pay_population_ranges ppr,
1503 per_periods_of_service pos,
1504 per_assignments_f paf,
1505 pay_payroll_actions ppa
1506 where ppa.payroll_action_id = pactid
1507 and paf.payroll_id = pay_core_utils.get_parameter('PAYROLL_ID', ppa.legislative_parameters)
1508 and pos.period_of_service_id = paf.period_of_service_id
1509 and pos.person_id = paf.person_id
1510 AND ppa.payroll_action_id = ppr.payroll_action_id
1511 AND ppr.chunk_number = chunk
1512 and pos.person_id = ppr.person_id
1513 and ppa.effective_date between paf.effective_start_date
1514 and paf.effective_end_date
1515 order by paf.assignment_id
1516 for update of paf.assignment_id, pos.period_of_service_id;
1517 --
1518 lockingactid NUMBER;
1519 l_report_type pay_payroll_actions.report_type%type;
1520 l_range_person BOOLEAN; -- 7508169 Variable used to check if RANGE_PERSON_ID is enabled
1521
1522 --
1523 BEGIN
1524 --
1525 OPEN c_get_report_type(pactid);
1526 FETCH c_get_report_type INTO l_report_type;
1527 CLOSE c_get_report_type;
1528
1529 IF(g_traces) THEN
1530 hr_utility.trace('In action_creation');
1531 hr_utility.trace('l_report_type : '|| l_report_type);
1532 END if;
1533
1534 l_range_person := pay_ac_utility.range_person_on(
1535 p_report_type => l_report_type
1536 ,p_report_format => 'DEFAULT'
1537 ,p_report_qualifier => 'DEFAULT'
1538 ,p_report_category => 'REPORT');
1539
1540 if l_range_person THEN -- 7508169. Use the new cursor c_actions_range_on cursor to fetch the assignment_ids
1541
1542 IF(g_traces) then
1543 hr_utility.trace('l_range_person is true');
1544 END if;
1545
1546 for asgrec in c_actions_range_on(pactid, chunk) loop
1547 --
1548 SELECT pay_assignment_actions_s.nextval
1549 INTO lockingactid
1550 FROM dual;
1551 --
1552 -- insert the action record.
1553 hr_nonrun_asact.insact(lockingactid,asgrec.assignment_id,pactid,chunk, null);
1554 --
1555 end loop;
1556
1557 ELSE -- Retain Old Logic- No Range Person
1558
1559 IF(g_traces) then
1560 hr_utility.trace('l_range_person is false');
1561 END if;
1562
1563 for asgrec in c_actions(pactid, stperson, endperson) loop
1564 --
1565 SELECT pay_assignment_actions_s.nextval
1566 INTO lockingactid
1567 FROM dual;
1568 --
1569 -- insert the action record.
1570 hr_nonrun_asact.insact(lockingactid,asgrec.assignment_id,pactid,chunk, null);
1571 --
1572 end loop;
1573
1574 END IF;
1575 --
1576 END action_creation;
1577 --
1578 procedure check_retro_asg_set(p_asg_set_id IN NUMBER) is
1579 --
1580 cursor c_check_retro_set is
1581 SELECT 'X'
1582 FROM hr_assignment_set_amendments
1583 WHERE assignment_set_id = p_asg_set_id;
1584 --
1585 l_dummy VARCHAR2(1);
1586 --
1587 begin
1588 --
1589 open c_check_retro_set;
1590 fetch c_check_retro_set into l_dummy;
1591 if c_check_retro_set%NOTFOUND then
1592 DELETE FROM hr_assignment_sets
1593 WHERE assignment_set_id = p_asg_set_id;
1594 end if;
1595 close c_check_retro_set;
1596 --
1597 End check_retro_asg_set;
1598 --
1599 procedure deinitialise (pactid in number)
1600 is
1601
1602 l_bus_grp number;
1603 l_evt_grp number;
1604 l_payroll number;
1605 l_asg_set_name hr_assignment_sets.assignment_set_name%type;
1606 l_asg_set_id number;
1607 l_adv_flag varchar2(1);
1608 l_report_date date;
1609 remove_act varchar2(10);
1610 l_generate_report varchar2(10);
1611
1612 l_proc varchar2(160) := g_package||'deinitialise';
1613
1614 begin
1615 hr_utility.set_location(l_proc,10);
1616 --
1617 get_pact_details (pactid,
1618 l_asg_set_name,
1619 l_bus_grp,
1620 l_payroll,
1621 l_evt_grp,
1622 l_adv_flag,
1623 l_report_date);
1624 --
1625 --
1626 if (l_adv_flag = 'Y') then
1627 --
1628 null;
1629 --
1630 else
1631 --If its original format then just tidy up the assignment set
1632 get_asg_set_id (l_asg_set_name,
1633 l_payroll,
1634 l_asg_set_id);
1635 --
1636 check_retro_asg_set(l_asg_set_id);
1637
1638 end if;
1639 --
1640 -- Now we need to generate the report and delete the
1641 -- output if required
1642 --
1643 select pay_core_utils.get_parameter('REMOVE_ACT',
1644 pa1.legislative_parameters),
1645 pay_core_utils.get_parameter('GEN_REPORT',
1646 pa1.legislative_parameters)
1647 into remove_act,
1648 l_generate_report
1649 from pay_payroll_actions pa1
1650 where pa1.payroll_action_id = pactid;
1651 --
1652 --
1653 if (l_generate_report is null or l_generate_report = 'Y') then
1654 --
1655 -- Need to submit the report here and wait for it
1656 -- to complete
1657 --
1658 run_report(pactid,l_adv_flag);
1659 --
1660 end if;
1661 --
1662 if (remove_act is null or remove_act = 'Y') then
1663 --
1664 pay_archive.remove_report_actions(pactid);
1665 --
1666 -- Not allowing the delete of this table as this has not been
1667 -- deleted before.
1668 --
1669 -- delete from pay_retro_notif_reports
1670 -- where report_id = pactid;
1671 --
1672 end if;
1673 --
1674 hr_utility.set_location(l_proc,900);
1675 --
1676 end deinitialise;
1677 --
1678 -------------------------------------------------------------------------------
1679 Function get_person_name(
1680 p_assignment_id IN NUMBER
1681 , p_report_date IN DATE
1682 , p_business_group_id IN NUMBER
1683 , p_legislation_code IN VARCHAR2)
1684 Return varchar2 is
1685 l_asg_status VARCHAR2(80) := NULL;
1686 l_person_name VARCHAR2(240) := NULL;
1687 Begin
1688 get_asg_info(
1689 p_assignment_id
1690 , p_report_date
1691 , p_business_group_id
1692 , p_legislation_code
1693 , l_asg_status
1694 , l_person_name );
1695 Return (l_person_name);
1696 End get_person_name;
1697 -------------------------------------------------------------------------------
1698 Function get_asg_status(
1699 p_assignment_id IN NUMBER
1700 , p_report_date IN DATE
1701 , p_business_group_id IN NUMBER
1702 , p_legislation_code IN VARCHAR2)
1703
1704 Return varchar2 is
1705 l_asg_status VARCHAR2(80) := NULL;
1706 l_person_name VARCHAR2(240) := NULL;
1707 Begin
1708 get_asg_info(
1709 p_assignment_id
1710 , p_report_date
1711 , p_business_group_id
1712 , p_legislation_code
1713 , l_asg_status
1714 , l_person_name );
1715 Return l_asg_status;
1716 End get_asg_status;
1717 -------------------------------------------------------------------------------
1718
1719 --
1720 End PAY_RETRO_NOTIF_PKG;