1 package body hr_dynsql as
2 /* $Header: pydynsql.pkb 120.46.12020000.10 2012/12/11 09:32:49 panumala ship $ */
3 -- cache for get_tax_unit
4 g_cached boolean := FALSE;
5 g_tax_unit pay_legislation_rules.rule_mode%type;
6 g_local_unit pay_legislation_rules.rule_mode%type;
7 -- Define PL/SQL Table type.
8 type char60_table is table of VARCHAR2(60)
9 index by binary_integer;
10 --
11 rrsel varchar(1000); -- select list for range row select.
12 retasactsel varchar(1000); -- select list for Retropay assignment action insert.
13 retpgasactsel varchar(1000); -- select list for Retropay assignment action insert.
14 ordrrsel varchar(1000); -- select list for range row select with ORDERED hint.
15 ordrrsel_1 varchar(1000); -- Introduced this for the bug 13780337
16 ordrrsel_nohint varchar(1000); -- Introduced this for the bug 14184691
17 prrsel varchar(100); -- select list for Purge range row select.
18 brrsel varchar(1000); -- select list for range row select for BEE.
19 asactsel varchar(1000); -- select list for assignment action insert.
20 orgsel varchar(1000); -- select list for range rows for Organisations.
21 runasactsel varchar(1000); -- select list for Run assignment action insert.
22 runasactsel_rev varchar(1000); -- select list for Run assignment action insert for Batch Reversal.Bug 8725368.
23 puractsel varchar(1000); -- select list for Purge assignment action insert.
24 allasg varchar(1000); -- from and where clause for all assignments.
25 beeasg varchar(1000); -- sql for all assignments for BEE.
26 beeactsel varchar(1000); -- select list for assignment action insert.
27 revallasg varchar(3000); -- select list for reversal asg action insert.
28 purallasg varchar(1000); -- Purge range row from and where clause.
29 allretasg varchar(1000); -- from and where clause for all retropay
30 -- assignments.
31 allrcsasg varchar(3000); -- from and where clause for all retrocosting
32 -- assignments
33 alladvasg varchar(3000); -- from and where clause for all advance
34 -- assignments
35 alladeasg varchar(3000); -- from and where clause for all advance
36 -- pay by element assignments.
37 revaa varchar(3000); -- check for existenace of valid reversal assmnt
38 -- actions
39 -- assignments.
40 rspcinc varchar(1500); -- from and where clause for specific inclusions in
41 -- range creation stage.
42 spcinc varchar(1500); -- from and where clause for specific inclusions in
43 -- action creation stage.
44 purspcinc varchar(1000); -- Purge specific inclusions.
45 revspcinc varchar(1500); -- Reversal specific inclusions
46 spcretinc varchar(1000); -- from and where clause for specific retropay
47 -- inclusions.
48 adeincspc varchar(3000); -- from and where clause for specific advance
49 -- pay by element inclusions.
50 spcrcsinc varchar(3000); -- from and where clause for specific retrocosting
51 -- inclusions.
52 range varchar(1000); -- restrict to particular range of person_id.
53 poprange varchar(1000); -- use of person_id in range table.
54 nopoprange varchar(1000); -- range used by process
55 grppoprange varchar(1000); -- use of person_id in range table.
56 grpnopoprange varchar(1000); -- range used by process
57 resact varchar(1500); -- from and where clause for resricted actions.
58 nonact varchar(1500); -- from and where clause for unresricted actions.
59 pruresact varchar(1500); -- from and where clause for resricted actions.
60 prunonact varchar(1500); -- from and where clause for unresricted actions.
61 ecsresact varchar(1500); -- from and where clause for resricted actions
62 -- for estimate costing process.
63 ecsnonact varchar(1500); -- from and where clause for unresricted actions
64 -- for estimate costing process.
65 excspc varchar(1000); -- exclude assignments.
66 intind varchar(1000); -- interlock rules for time independent
67 -- legislation.
68 intretgrpdep varchar(1000); -- interlock rules for retropay time dependent
69 intretind varchar(1000); -- interlock rules for retropay time independent
70 -- legislation.
71 intdep varchar(1000); -- interlock rules for time dependent legislation.
72 intbaldep varchar(1000); -- interlock rules for bal adj time dependent legislation.
73 intretdep varchar(1000); -- interlock rules for retropay time dependent legislation.
74 intgrpdep varchar(1000); -- interlock rules for group dependent legislation.
75 intgrpdepbal varchar(1000); -- interlock rules for bal adj group dependent legislation.
76 intgrpdepret varchar(1000); -- interlock rules for retropay group dependent legislation.
77 intdepaset varchar(1000); -- interlock rules for time dependent legislation.
78 intbaldepaset varchar(1000); -- interlock rules for bal adj time dependent legislation.
79 intretdepaset varchar(1000); -- interlock rules for retropay time dependent legislation.
80 intpur varchar(2500); -- interlock rules for Purge.
81 intbal varchar(1500); -- interlock rules for balance adjustment.
82 orderby varchar(1000); -- order by clause.
83 borderby varchar(1000); -- order by clause for BEE.
84 actorderby varchar(1000); -- action order by clause.
85 fupdate varchar(1000); -- for update clause.
86 retdefasg varchar(1000); -- from and where clause for all retropay
87 -- by element asg (with retro definition id)
88 retdefasgpg varchar(2000); -- from and where clause for all retropay
89 -- by element using process groups
90 qretdefasg varchar(1000); -- Bug 1308309, Quick-Retropay Changes
91 qretdefasgpg varchar(2000); -- Bug 1308309, Quick-Retropay Changes
92 orgfrom varchar(2000); -- From clause for payment organisations
93 orgorderby varchar(1000); -- Order by for the organisation list
94 orgbind varchar(1000); -- Sets the direct bind variable
95 --
96 c_eot constant date := to_date('31/12/4712','DD/MM/YYYY');
97 max_dynsql_len constant number := 4000;
98 --
99 ----------------------------- update_recurring_ee --------------------------
100 /*
101 NAME
102 update_recurring_ee
103 NOTES
104 This function performs the actual database work of updating
105 a REE's input value as a result of an Update Formula Result Rule.
106 */
107 procedure update_recurring_ee
108 (
109 p_element_entry_id in out nocopy number,
110 p_error_code in out nocopy number,
111 p_assignment_action_id in number,
112 p_assignment_id in number,
113 p_effective_date in date,
114 p_element_type_id in number,
115 p_input_value_id in number,
116 p_updated_value in varchar2
117 ) is
118 -- Setup entry values cursor.
119 cursor get_entry_values (p_update_ee_id in number,
120 p_date in date) is
121 select input_value_id, screen_entry_value
122 from pay_element_entry_values_f eev
123 where eev.element_entry_id = p_update_ee_id
124 and p_date between eev.effective_start_date
125 and eev.effective_end_date;
126 --
127 -- Need a row variable for get_entry_values as we are now doing
128 -- explicit fetches.
129 r_entry_value get_entry_values%ROWTYPE;
130 --
131 cursor upd_entry_values (p_update_ee_id in number,
132 p_date in date) is
133 select eev.element_entry_value_id,
134 eev.input_value_id,
135 eev.element_entry_id,
136 eev.screen_entry_value
137 from pay_element_entry_values_f eev
138 where eev.element_entry_id = p_update_ee_id
139 and (p_date - 1) between
140 eev.effective_start_date and eev.effective_end_date;
141 --
142 cursor entry_record_exists(p_update_ee_id in number,
143 p_effective_end_date in date) is
144 select effective_end_date
145 from pay_element_entries_f
146 where element_entry_id = p_update_ee_id
147 and effective_start_date = p_effective_date
148 and effective_end_date = p_effective_end_date;
149 --
150 cursor entry_value_exists(p_update_ee_id in number,
151 p_input_value_id in number,
152 p_effective_end_date in date) is
153 select effective_end_date,screen_entry_value
154 from pay_element_entry_values_f
155 where element_entry_id = p_update_ee_id
156 and input_value_id = p_input_value_id
157 and effective_start_date = p_effective_date
158 and effective_end_date = p_effective_end_date;
159 --
160 c_indent constant varchar2(30) := 'pydynsql.update_recurring_ee';
161 update_ee_id number;
162 upd_act_id number; -- updating_action_id.
163 ee_effstart date;
164 -- bug 6655722
165 -- max_effend date;
166 val_date date;
167 asgno per_all_assignments_f.assignment_number%type;
168 link_id number;
169 lookup_type hr_lookups.lookup_type%type;
170 -- Bugfix 2827092
171 --value_set_id pay_input_values_f.value_set_id%type;
172 uom pay_input_values_f.uom%type;
173 input_curr pay_element_types_f.input_currency_code%type;
174 screen_value pay_element_entry_values_f.screen_entry_value%type;
175 db_value pay_element_entry_values_f.screen_entry_value%type;
176 old_value pay_element_entry_values_f.screen_entry_value%type;
177 scr_upd_value pay_element_entry_values_f.screen_entry_value%type;
178 entry_val_list char60_table;
179 l_all_entry_values_null varchar2(30);
180 l_effective_end_date date; -- bug 6655722
181 l_screen_entry_value pay_element_entrY_values_f.screen_entry_value%type;
182 ovn number(9);
183 begin
184 -- Select details about the element entry we are to update.
185 -- If p_element_entry_id is not null, the entry is restricted
186 -- to the one specified (for multiple recurring entries).
187 -- Otherwise, there should only be one normal entry for
188 -- the combination of assignment and element type.
189 -- Note that we implicitly assume that the assignment is on a
190 -- payroll, in the joins to element link, hence no reference
191 -- to link_to_all_payrolls_flag.
192 begin
193 hr_utility.set_location(c_indent,10);
194 -- Bugfix 2827092 following lines temporarily removed from below
195 -- piv.value_set_id,
196 -- value_set_id,
197 select pee.element_entry_id,
198 pee.updating_action_id,
199 pee.effective_start_date,
200 asg.assignment_number,
201 pel.element_link_id,
202 piv.lookup_type,
203 piv.uom,
204 pet.input_currency_code
205 into update_ee_id,
206 upd_act_id,
207 ee_effstart,
208 asgno,
209 link_id,
210 lookup_type,
211 uom,
212 input_curr
213 from pay_element_entries_f pee,
214 pay_element_links_f pel,
215 pay_element_types_f pet,
216 pay_input_values_f piv,
217 per_all_assignments_f asg
218 where asg.assignment_id = p_assignment_id
219 and p_effective_date between
220 asg.effective_start_date and asg.effective_end_date
221 and pel.element_type_id = p_element_type_id
222 and (pel.payroll_id = asg.payroll_id
223 or pel.payroll_id is null)
224 and p_effective_date between
225 pel.effective_start_date and pel.effective_end_date
226 and pee.element_link_id = pel.element_link_id
227 and pee.assignment_id = asg.assignment_id
228 and pee.entry_type = 'E'
229 and p_effective_date between
230 pee.effective_start_date and pee.effective_end_date
231 and (pee.element_entry_id = p_element_entry_id
232 or p_element_entry_id is null)
233 and pet.element_type_id = pel.element_type_id
234 and p_effective_date between
235 pet.effective_start_date and pet.effective_end_date
236 and piv.input_value_id = p_input_value_id
237 and p_effective_date between
238 piv.effective_start_date and piv.effective_end_date;
239 exception
240 when no_data_found then
241 -- Have failed to find an entry to update.
242 -- This most likely means that the entry does not exist
243 -- at the date of the run. In (most unusual) circumstances,
244 -- it may mean we have serious data corruption.
245 -- Return an error code to allow output of message
246 -- BUG 7272321 : Commented out raising of error(7328)
247 -- Description: This element entry is present at the Date-Earned(because it was picked up for processing)
248 -- but is not present at the date of run(may be it was end-dated between these dates).
249 -- In this case no need of UPDATE for this element entry. we can skip UPDATE operation.
250 --p_error_code := 7328;
251 return;
252 end;
253 --
254 -- Perform certain required validation checks and convert
255 -- the external format to the internal one.
256 -- Convert value from internal to extrenal format in preperation
257 -- for hr_entry_api.
258 hr_entry_api.set_formula_contexts (p_assignment_id, p_effective_date);
259 screen_value := hr_chkfmt.changeformat(p_updated_value, uom, input_curr);
260 -- Have temporarily removed the following lines from the call
261 -- to hr_entry_api.validate_entry_value to avoid a huge patching issue
262 -- with 11.5 c-code chain (where would have to pull in all other dependant
263 -- code on value set validation).
264 -- this line can be introduced in Next base release
265 -- Bugfix 2827092
266 --p_value_set_id => value_set_id,
267 hr_entry_api.validate_entry_value
268 (p_element_link_id => link_id,
269 p_input_value_id => p_input_value_id,
270 p_session_date => p_effective_date,
271 p_screen_format => screen_value,
272 p_db_format => db_value,
273 p_lookup_type => lookup_type,
274 p_uom => uom,
275 p_input_currency_code => input_curr);
276 --
277 -- We must explicitly check for a correction.
278 -- This is only allowed if the current assignment action
279 -- is the same as the previous updating action. Otherwise,
280 -- We raise an error.
281 if(ee_effstart = p_effective_date) then
282 -- We are attempting a correction. Check if it is legal.
283 if(upd_act_id = p_assignment_action_id) then
284 -- It is legal. Set the validation date to be
285 -- previous day. This ensures the date effective
286 -- stuff below will work correctly.
287 val_date := (p_effective_date - 1);
288 else
289 hr_utility.set_location(c_indent,18);
290 -- Check if update really required
291 -- ie changing entry value
292 select eev.screen_entry_value
293 into old_value
294 from pay_element_entry_values eev
295 where eev.element_entry_id = update_ee_id
296 and eev.input_value_id = p_input_value_id
297 and p_effective_date between
298 eev.effective_start_date and eev.effective_end_date;
299 --
300 if (nvl(old_value,'X') <> nvl(db_value,'X')) then
301 -- Return error code to allow output of message
302 p_error_code := 7053;
303 return;
304 else
305 return;
306 end if;
307 end if;
308 else
309 -- Not correction - validation date is effective date.
310 val_date := p_effective_date;
311 end if;
312 --
313 hr_utility.set_location(c_indent,20);
314
315 /* bug 6655722
316 select max(pee.effective_end_date)
317 into max_effend
318 from pay_element_entries_f pee
319 where pee.element_entry_id = update_ee_id;
320 */
321 --
322 begin
323 -- Set the Continuous Calc override flag, so that the trigger points
324 -- are not fired.
325 pay_continuous_calc.g_override_cc := TRUE;
326 --
327
328 hr_utility.set_location(c_indent,30);
329
330 --bug 6655722
331 -- Ok, we have the information - now we need to perform
332 -- the date track update (UPDATE_CHANGE_INSERT).
333 -- Obtain the effective_end_date of the record we are going
334 -- to update. The new record being created should have the same
335 -- end date.
336
337 SELECT effective_end_date
338 INTO l_effective_end_date
339 FROM pay_element_entries_f
340 WHERE element_entry_id = update_ee_id
341 AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
342 --
343 /*
344 delete from pay_element_entries_f pee
345 where pee.element_entry_id = update_ee_id
346 and pee.effective_start_date > val_date;
347 */
348 --
349 --
350 -- Enhancement 3478848
351 -- We need to derive the l_all_entry_values_null flag. First we
352 -- initialise l_all_entry_values_null to 'Y', then when any non-null
353 -- entry values are encountered, this value is reset to null, thereby
354 -- ensuring that the value 'Y' persists only when ALL entry values
355 -- are null.
356 --
357 l_all_entry_values_null := 'Y';
358 --
359 -- Now we populate the PL/SQL entry values table with the values of
360 -- the entries as of the effective date BUT use the derived db_value
361 -- for the entry value for p_input_value_id.
362 --
363 open get_entry_values(update_ee_id, p_effective_date);
364 --
365 loop
366 --
367 fetch get_entry_values into r_entry_value;
368 --
369 if get_entry_values%NOTFOUND and get_entry_values%ROWCOUNT = 0 then
370 --
371 -- No entry values found, therefore we need to set the
372 -- l_all_entry_values_null flag to null.
373 --
374 l_all_entry_values_null := null;
375 --
376 end if;
377 --
378 exit when get_entry_values%NOTFOUND;
379 --
380 if r_entry_value.input_value_id = p_input_value_id then
381 entry_val_list(r_entry_value.input_value_id) := db_value;
382 else
383 entry_val_list(r_entry_value.input_value_id) :=
384 r_entry_value.screen_entry_value;
385 end if;
386 --
387 hr_utility.trace('IV='||r_entry_value.input_value_id);
388 hr_utility.trace('VAL='||r_entry_value.screen_entry_value);
389 hr_utility.trace('TAB='||entry_val_list(r_entry_value.input_value_id));
390 hr_utility.trace('DATE='||val_date);
391 --
392 if entry_val_list(r_entry_value.input_value_id) is not null then
393 --
394 -- A non-null entry value has been encountered, therefore set the
395 -- l_all_entry_values_null flag to null.
396 --
397 l_all_entry_values_null := null;
398 --
399 end if;
400 --
401 end loop;
402 --
403 close get_entry_values;
404
405 -- Bug 7194700
406 -- Check if a record exists in PAY_ELEMENT_ENTRIES_F with the same effective_start_date
407 -- and effective_end_date. Proceed with the Update and Insert DMLs only if no such
408 -- record is there.
409
410 OPEN entry_record_exists(update_ee_id,l_effective_end_date);
411 FETCH entry_record_exists INTO l_effective_end_date;
412
413 IF entry_record_exists%NOTFOUND THEN
414 --
415 -- Now, update the effective_end_date of existing entry.
416 -- Note : using val_date.
417 hr_utility.set_location(c_indent,40);
418 update pay_element_entries_f pee
419 set pee.effective_end_date = (p_effective_date - 1)
420 where pee.element_entry_id = update_ee_id
421 and val_date between
422 pee.effective_start_date and pee.effective_end_date;
423 --
424 -- Finally (for entry), we wish to insert the new
425 -- entry record.
426 hr_utility.set_location(c_indent,50);
427 --
428 -- Bugfix 3110853
429 -- Derive the OVN before inserting
430 --
431 ovn := dt_api.get_object_version_number (
432 'PAY_ELEMENT_ENTRIES_F',
433 'ELEMENT_ENTRY_ID',
434 update_ee_id
435 );
436 --
437 insert into pay_element_entries_f (
438 element_entry_id,
439 effective_start_date,
440 effective_end_date,
441 cost_allocation_keyflex_id,
442 assignment_id,
443 updating_action_id,
444 updating_action_type,
445 element_link_id,
446 element_type_id,
447 original_entry_id,
448 creator_type,
449 entry_type,
450 comment_id,
451 creator_id,
452 reason,
453 target_entry_id,
454 subpriority,
455 personal_payment_method_id,
456 all_entry_values_null,
457 attribute_category,
458 attribute1,
459 attribute2,
460 attribute3,
461 attribute4,
462 attribute5,
463 attribute6,
464 attribute7,
465 attribute8,
466 attribute9,
467 attribute10,
468 attribute11,
469 attribute12,
470 attribute13,
471 attribute14,
472 attribute15,
473 attribute16,
474 attribute17,
475 attribute18,
476 attribute19,
477 attribute20,
478 entry_information_category,
479 entry_information1,
480 entry_information2,
481 entry_information3,
482 entry_information4,
483 entry_information5,
484 entry_information6,
485 entry_information7,
486 entry_information8,
487 entry_information9,
488 entry_information10,
489 entry_information11,
490 entry_information12,
491 entry_information13,
492 entry_information14,
493 entry_information15,
494 entry_information16,
495 entry_information17,
496 entry_information18,
497 entry_information19,
498 entry_information20,
499 entry_information21,
500 entry_information22,
501 entry_information23,
502 entry_information24,
503 entry_information25,
504 entry_information26,
505 entry_information27,
506 entry_information28,
507 entry_information29,
508 entry_information30,
509 object_version_number,
510 last_update_date,
511 last_updated_by,
512 last_update_login,
513 created_by,
514 creation_date)
515 select pee.element_entry_id,
516 p_effective_date,
517 l_effective_end_date,
518 pee.cost_allocation_keyflex_id,
519 pee.assignment_id,
520 p_assignment_action_id,
521 'U',
522 pee.element_link_id,
523 pee.element_type_id,
524 pee.original_entry_id,
525 pee.creator_type,
526 pee.entry_type,
527 pee.comment_id,
528 pee.creator_id,
529 pee.reason,
530 pee.target_entry_id,
531 pee.subpriority,
532 pee.personal_payment_method_id,
533 l_all_entry_values_null,
534 pee.attribute_category,
535 pee.attribute1,
536 pee.attribute2,
537 pee.attribute3,
538 pee.attribute4,
539 pee.attribute5,
540 pee.attribute6,
541 pee.attribute7,
542 pee.attribute8,
543 pee.attribute9,
544 pee.attribute10,
545 pee.attribute11,
546 pee.attribute12,
547 pee.attribute13,
548 pee.attribute14,
549 pee.attribute15,
550 pee.attribute16,
551 pee.attribute17,
552 pee.attribute18,
553 pee.attribute19,
554 pee.attribute20,
555 entry_information_category,
556 entry_information1,
557 entry_information2,
558 entry_information3,
559 entry_information4,
560 entry_information5,
561 entry_information6,
562 entry_information7,
563 entry_information8,
564 entry_information9,
565 entry_information10,
566 entry_information11,
567 entry_information12,
568 entry_information13,
569 entry_information14,
570 entry_information15,
571 entry_information16,
572 entry_information17,
573 entry_information18,
574 entry_information19,
575 entry_information20,
576 entry_information21,
577 entry_information22,
578 entry_information23,
579 entry_information24,
580 entry_information25,
581 entry_information26,
582 entry_information27,
583 entry_information28,
584 entry_information29,
585 entry_information30,
586 ovn,
587 trunc(sysdate),
588 0,
589 0,
590 pee.created_by,
591 pee.creation_date
592 from pay_element_entries_f pee
593 where pee.element_entry_id = update_ee_id
594 and (p_effective_date - 1) between
595 pee.effective_start_date and pee.effective_end_date;
596 --
597 END if;
598 CLOSE entry_record_exists;
599 --
600 -- Now populate the PL/SQL entry values table with the values
601 -- of the entries as of the effective date.
602 -- Enhancement 3478848
603 -- Removed this, fetch now performed prior to inserting the element
604 -- entry row, above.
605 /*
606 for entry_value in get_entry_values(update_ee_id, p_effective_date) loop
607 entry_val_list(entry_value.input_value_id) :=
608 entry_value.screen_entry_value;
609 hr_utility.trace('IV='||entry_value.input_value_id);
610 hr_utility.trace('VAL='||entry_value.screen_entry_value);
611 hr_utility.trace('TAB='||entry_val_list(entry_value.input_value_id));
612 hr_utility.trace('DATE='||val_date);
613 end loop;
614 */
615
616
617 -- Bug 7194700
618 -- Check if a record exists in PAY_ELEMENT_ENTRY_VALUES_F with the same effective_start_date
619 -- and effective_end_date. Proceed with the Update and Insert DMLs only if no such
620 -- record is there.
621 --
622
623 OPEN entry_value_exists(update_ee_id,p_input_value_id,l_effective_end_date);
624 FETCH entry_value_exists INTO l_effective_end_date, l_screen_entry_value;
625
626 IF entry_value_exists%NOTFOUND THEN
627 --
628 -- We now wish to perform the update on the entry values.
629 -- This is a similar process to the entry stuff.
630
631 hr_utility.set_location(c_indent,60);
632
633 /* bug 6655772
634 delete from pay_element_entry_values_f eev
635 where eev.element_entry_id = update_ee_id
636 and eev.effective_start_date > val_date;
637 */
638 --
639 -- Fix the end date of the entry values.
640 -- Note : using val_date.
641 hr_utility.set_location(c_indent,70);
642 update pay_element_entry_values_f eev
643 set eev.effective_end_date = (p_effective_date - 1)
644 where eev.element_entry_id = update_ee_id
645 and val_date between
646 eev.effective_start_date and eev.effective_end_date;
647 --
648 -- Now we insert the new entry values row.
649 -- We set the new entry value as required.
650 -- Note : using val_date.
651 hr_utility.set_location(c_indent,80);
652 for update_values in upd_entry_values(update_ee_id, p_effective_date) loop
653 -- Enhancement 3478848
654 -- Removed this, this check is now performed when the entry
655 -- values are initially fetched, above.
656 /*
657 if update_values.input_value_id = p_input_value_id then
658 scr_upd_value := db_value;
659 else
660 scr_upd_value := entry_val_list(update_values.input_value_id);
661 end if;
662 */
663 --
664 insert into pay_element_entry_values (
665 element_entry_value_id,
666 effective_start_date,
667 effective_end_date,
668 input_value_id,
669 element_entry_id,
670 screen_entry_value)
671 values (update_values.element_entry_value_id,
672 p_effective_date,
673 l_effective_end_date,
674 update_values.input_value_id,
675 update_values.element_entry_id,
676 -- Enhancement 3478848
677 -- entry_val_list now contains the correct entry values
678 decode(trim(entry_val_list(update_values.input_value_id)), NULL, NULL, entry_val_list(update_values.input_value_id))); -- bug 8482621
679 -- scr_upd_value);
680 end loop;
681 --
682 ELSIF (l_screen_entry_value is null
683 or l_screen_entry_value <> entry_val_list(p_input_value_id)) THEN -- bug 7314920
684 --
685 update pay_element_entry_values_f eev
686 set screen_entry_value = decode(trim(entry_val_list(p_input_value_id)), NULL, NULL, entry_val_list(p_input_value_id)) -- bug 7340357
687 where eev.element_entry_id = update_ee_id
688 and input_value_id = p_input_value_id
689 and p_effective_date between eev.effective_start_date and eev.effective_end_date;
690 --
691 END if;
692 --
693 CLOSE entry_value_exists;
694
695 /*
696 insert into pay_element_entry_values (
697 element_entry_value_id,
698 effective_start_date,
699 effective_end_date,
700 input_value_id,
701 element_entry_id,
702 screen_entry_value)
703 select eev.element_entry_value_id,
704 p_effective_date,
705 max_effend,
706 eev.input_value_id,
707 eev.element_entry_id,
708 decode(eev.input_value_id, p_input_value_id,
709 db_value, eev.screen_entry_value)
710 from pay_element_entry_values_f eev
711 where eev.element_entry_id = update_ee_id
712 and (p_effective_date - 1) between
713 eev.effective_start_date and eev.effective_end_date;
714 */
715 pay_continuous_calc.g_override_cc := FALSE;
716 --
717 exception
718 when others then
719 pay_continuous_calc.g_override_cc := FALSE;
720 raise;
721 end;
722 --
723 -- Return element_entry_id that we updated.
724 hr_utility.set_location(c_indent,90);
725 p_element_entry_id := update_ee_id;
726 end update_recurring_ee;
727 --
728 ----------------------------- stop_recurring_ee --------------------------
729 /*
730 NAME
731 stop_recurring_ee
732 NOTES
733 This function performs the actual database work of date effectively
734 deleting a REE as a result of a Stop Formula Result Rule.
735 */
736 procedure stop_recurring_ee
737 (
738 p_element_entry_id in number,
739 p_error_code in out nocopy number,
740 p_assignment_id in number,
741 p_effective_date in date,
742 p_element_type_id in number,
743 p_assignment_action_id in number,
744 p_date_earned in date
745 ) is
746 c_indent constant varchar2(30) := 'pydynsql.stop_recurring_ee';
747 stop_ee_id number;
748 link_id number;
749 stop_ee_start_date date;
750 v_error_flag varchar2(1);
751 begin
752 -- Select details about the element entry we are to stop.
753 -- If p_element_entry_id is not null, the entry is restricted
754 -- to the one specified (for multiple recurring entries).
755 -- Otherwise, there should only be one normal entry for
756 -- the combination of assignment and element type.
757 -- Note that we implicitly assume that the assignment is on a
758 -- payroll, in the joins to element link, hence no reference
759 -- to link_to_all_payrolls_flag.
760 begin
761 hr_utility.set_location(c_indent,10);
762 select pee.element_entry_id,
763 pel.element_link_id,
764 pee.effective_start_date
765 into stop_ee_id, link_id, stop_ee_start_date
766 from pay_element_entries_f pee,
767 pay_element_links_f pel,
768 per_all_assignments_f asg
769 where asg.assignment_id = p_assignment_id
770 and p_date_earned between
771 asg.effective_start_date and asg.effective_end_date
772 and pel.element_type_id = p_element_type_id
773 and (pel.payroll_id = asg.payroll_id
774 or pel.payroll_id is null)
775 and p_date_earned between
776 pel.effective_start_date and pel.effective_end_date
777 and pee.element_link_id = pel.element_link_id
778 and pee.assignment_id = asg.assignment_id
779 and pee.entry_type = 'E'
780 and (pee.element_entry_id = p_element_entry_id
781 or p_element_entry_id is null)
782 and p_date_earned between
783 pee.effective_start_date and pee.effective_end_date;
784 exception
785 when no_data_found then
786 -- No entry could be found to stop.
787 -- This most likely means that no entry existed at effective date.
788 -- Likely cause is that entry has already been stopped.
789 -- Return error code to allow output of error message.
790 p_error_code := 7329;
791 return;
792 end;
793 --
794 -- Check we are not attempting to orphan any adjustments.
795 -- Note the joins to assignment_id and element_link_id are
796 -- necessary to activate the index.
797 -- Also note, must join with stop_ee_id, not p_element_entry_id.
798 begin
799 select 'Y'
800 into v_error_flag
801 from sys.dual
802 where exists (
803 select null
804 from pay_element_entries_f pee
805 where pee.assignment_id = p_assignment_id
806 and pee.element_link_id = link_id
807 and pee.target_entry_id = stop_ee_id
808 and pee.effective_start_date <= c_eot
809 and pee.effective_end_date >= (p_date_earned + 1));
810 exception
811 when no_data_found then null;
812 end;
813 --
814 if v_error_flag = 'Y' then
815 hr_utility.set_message(801, 'HR_6304_ELE_ENTRY_DT_DEL_ADJ');
816 hr_utility.raise_error;
817 end if;
818 --
819 -- Check the start date of the date effective element entry, if the date
820 -- is greater than the date_earned then error, since the entry is stopped
821 -- as of date earned.
822 --
823 if stop_ee_start_date > p_date_earned then
824 hr_utility.set_message(801, 'HR_51338_HRPROC_STOP_EE_DATE');
825 hr_utility.raise_error;
826 end if;
827 --
828 -- Ok, perform date track delete (DELETE).
829 -- This means we delete any future entries and values
830 -- then set the effective_end_dates as appropriate.
831 begin
832 -- Set the Continuous Calc override flag, so that the trigger points
833 -- are not fired.
834 pay_continuous_calc.g_override_cc := TRUE;
835 --
836 delete from pay_element_entries_f pee
837 where pee.element_entry_id = stop_ee_id
838 and pee.effective_start_date > p_date_earned;
839 --
840 update pay_element_entries_f pee
841 set pee.effective_end_date = p_date_earned,
842 pee.prev_upd_action_id = DECODE(pee.updating_action_type, 'U', pee.updating_action_id),
843 pee.updating_action_id = p_assignment_action_id,
844 pee.updating_action_type = 'S'
845 where pee.element_entry_id = stop_ee_id
846 and p_date_earned between
847 pee.effective_start_date and pee.effective_end_date;
848 --
849 delete from pay_element_entry_values_f eev
850 where eev.element_entry_id = stop_ee_id
851 and eev.effective_start_date > p_date_earned;
852 --
853 update pay_element_entry_values_f eev
854 set eev.effective_end_date = p_date_earned
855 where eev.element_entry_id = stop_ee_id
856 and p_date_earned between
857 eev.effective_start_date and eev.effective_end_date;
858 --
859 pay_continuous_calc.g_override_cc := FALSE;
860 --
861 exception
862 when others then
863 pay_continuous_calc.g_override_cc := FALSE;
864 raise;
865 end;
866 end stop_recurring_ee;
867 --
868 --------------------------------- setinfo ----------------------------------
869 /*
870 NAME
871 setinfo - get information from an assignment set.
872 DESCRIPTION
873 Returns information about the assignment set supplied:
874 if there are any specific includes or excludes; the
875 formula_id of any criteria formula; whether or not a
876 payroll_id is on the set.
877 --
878 Also uses 'everyone' to indicate if we are starting
879 from the full set or empty set.
880 NOTES
881 Called for both Rollback and QuickPaint cases.
882 */
883 procedure setinfo
884 (
885 asetid in number, -- assignment_set_id.
886 everyone in out nocopy boolean, -- everyone in set or not.
887 include in out nocopy boolean, -- any specific inclusions.
888 exclude in out nocopy boolean, -- any specific exclusions.
889 formula in out nocopy number, -- has a formula been specified.
890 payroll in out nocopy boolean -- has a payroll_id been specified.
891 ) is
892 payid number; -- payroll_id.
893 dummy number; -- dummy cos selects need something to select into.
894 begin
895 -- start by selecting the information about payroll and formula.
896 hr_utility.set_location('hr_dynsql.setinfo',5);
897 select has.payroll_id,
898 nvl(has.formula_id,0)
899 into payid,
900 formula
901 from hr_assignment_sets has
902 where has.assignment_set_id = asetid;
903 --
904 payroll := (payid is not null);
905 --
906 -- Now check for specific inclusions being specified.
907 hr_utility.set_location('hr_dynsql.setinfo',10);
908 include := TRUE;
909 begin
910 select null
911 into dummy
912 from sys.dual
913 where exists (
914 select null
915 from hr_assignment_set_amendments amd
916 where amd.assignment_set_id = asetid
917 and amd.include_or_exclude = 'I');
918 exception
919 when no_data_found then include := FALSE;
920 end;
921 --
922 -- Now check for specific exclusions.
923 exclude := TRUE;
924 hr_utility.set_location('hr_dynsql.setinfo',15);
925 begin
926 select null
927 into dummy
928 from sys.dual
929 where exists (
930 select null
931 from hr_assignment_set_amendments amd
932 where amd.assignment_set_id = asetid
933 and amd.include_or_exclude = 'E');
934 exception
935 when no_data_found then exclude := FALSE;
936 end;
937 --
938 -- Having got the flags that tell us about the
939 -- specific inclusions and so on, set the
940 -- 'everyone' flag, based on standard
941 -- assignment set rules.
942 everyone := TRUE; -- start by assuming that we need everyone.
943 --
944 -- Only case where we start with empty set is
945 -- when we have specific inclusions only.
946 if(formula = 0 and include) then
947 everyone := FALSE;
948 end if;
949 --
950 -- In the case where we have a formula specified
951 -- we need to turn the include flag off, because
952 -- the restriction is processed later.
953 if(formula <> 0) then
954 include := FALSE;
955 end if;
956 end setinfo;
957 --
958 --------------------------- person_sequence_locked --------------------------
959 /*
960 NAME
961 person_sequence_locked - Person Sequence Locked
962 DESCRIPTION
963 This function is used to determine if a person has sequence locks
964 given a date.
965 NOTES
966 <none>
967 */
968 function person_sequence_locked (p_period_service_id in number,
969 p_effective_date in date)
970 return varchar2
971 is
972 --
973 cursor dp (p_per_of_serv in number) is
974 select distinct paf.assignment_id
975 from per_all_assignments_f paf
976 where paf.period_of_service_id = p_per_of_serv;
977 --
978 cursor csr_locker (p_asg_id in number,
979 p_eff_date in date)
980 is
981 select 1 res
982 from sys.dual
983 where exists (
984 select /*+ index(AC2 PAY_ASSIGNMENT_ACTIONS_N51) */ null
985 from pay_action_classifications acl,
986 pay_assignment_actions ac2,
987 pay_payroll_actions pa2
988 where ac2.assignment_id = p_asg_id
989 and pa2.payroll_action_id = ac2.payroll_action_id
990 and acl.classification_name = 'SEQUENCED'
991 and pa2.action_type = acl.action_type
992 and (pa2.effective_date > p_eff_date
993 or (ac2.action_status not in ('C', 'S')
994 and pa2.effective_date <= p_eff_date)));
995 --
996 l_locked varchar2(3);
997 --
998 begin
999 --
1000 l_locked := 'N';
1001 --
1002 for asgrec in dp(p_period_service_id) loop
1003 for resrec in csr_locker(asgrec.assignment_id,
1004 p_effective_date) loop
1005 l_locked := 'Y';
1006 end loop;
1007 end loop;
1008 --
1009 return l_locked;
1010 --
1011 end person_sequence_locked;
1012 --
1013 --------------------------- bal_person_sequence_locked --------------------------
1014 /*
1015 NAME
1016 bal_person_sequence_locked - Person Sequence Locked for balance adjustments
1017 DESCRIPTION
1018 This function is used to determine if a person has any
1019 unsuccesful actions (regardless of date).
1020 NOTES
1021 <none>
1022 */
1023 function bal_person_sequence_locked (p_period_service_id in number,
1024 p_effective_date in date)
1025 return varchar2
1026 is
1027 --
1028 cursor dp (p_per_of_serv in number) is
1029 select distinct paf.assignment_id
1030 from per_all_assignments_f paf
1031 where paf.period_of_service_id = p_per_of_serv;
1032 --
1033 cursor csr_locker (p_asg_id in number,
1034 p_eff_date in date)
1035 is
1036 select 1 res
1037 from sys.dual
1038 where exists (
1039 select null
1040 from pay_action_classifications acl,
1041 pay_assignment_actions ac2,
1042 pay_payroll_actions pa2
1043 where ac2.assignment_id = p_asg_id
1044 and pa2.payroll_action_id = ac2.payroll_action_id
1045 and acl.classification_name = 'SEQUENCED'
1046 and pa2.action_type = acl.action_type
1047 and ac2.action_status not in ('C', 'S'));
1048 --
1049 l_locked varchar2(3);
1050 --
1051 begin
1052 --
1053 l_locked := 'N';
1054 --
1055 for asgrec in dp(p_period_service_id) loop
1056 for resrec in csr_locker(asgrec.assignment_id,
1057 p_effective_date) loop
1058 l_locked := 'Y';
1059 end loop;
1060 end loop;
1061 --
1062 return l_locked;
1063 --
1064 end bal_person_sequence_locked;
1065 --
1066 --------------------------- ret_person_sequence_locked ----------------------
1067 /*
1068 NAME
1069 ret_person_sequence_locked - Retropay Person Sequence Locked
1070 DESCRIPTION
1071 This function is used to determine if a person has sequence locks
1072 given a date.
1073 NOTES
1074 <none>
1075 */
1076 function ret_person_sequence_locked (p_period_service_id in number,
1077 p_effective_date in date)
1078 return varchar2
1079 is
1080 --
1081 cursor dp (p_per_of_serv in number) is
1082 select distinct paf.assignment_id
1083 from per_all_assignments_f paf
1084 where paf.period_of_service_id = p_per_of_serv;
1085 --
1086 cursor csr_locker (p_asg_id in number,
1087 p_eff_date in date)
1088 is
1089 select 1 res
1090 from sys.dual
1091 where exists (
1092 select null
1093 from pay_action_classifications acl,
1094 pay_assignment_actions ac2,
1095 pay_payroll_actions pa2
1096 where ac2.assignment_id = p_asg_id
1097 and pa2.payroll_action_id = ac2.payroll_action_id
1098 and acl.classification_name = 'SEQUENCED'
1099 and pa2.action_type = acl.action_type
1100 and ((pa2.effective_date > p_eff_date
1101 and ac2.action_status in ('C', 'S'))
1102 or (ac2.action_status not in ('C', 'S')
1103 and pa2.effective_date <= p_eff_date)));
1104 --
1105 l_locked varchar2(3);
1106 --
1107 begin
1108 --
1109 l_locked := 'N';
1110 --
1111 for asgrec in dp(p_period_service_id) loop
1112 for resrec in csr_locker(asgrec.assignment_id,
1113 p_effective_date) loop
1114 l_locked := 'Y';
1115 end loop;
1116 end loop;
1117 --
1118 return l_locked;
1119 --
1120 end ret_person_sequence_locked;
1121 --
1122 function process_group_seq_locked (p_asg_id in number,
1123 p_effective_date in date,
1124 p_future_actions in varchar2 default 'N')
1125 return varchar2
1126 is
1127 --
1128 /* Look for all the assignments on the same group */
1129 cursor dp (p_asg_id in number) is
1130 select distinct pog_grp.source_id
1131 from pay_object_groups pog_act,
1132 pay_object_groups pog_grp
1133 where pog_act.source_id = p_asg_id
1134 and pog_act.source_type = 'PAF'
1135 and pog_act.parent_object_group_id = pog_grp.parent_object_group_id -- the personlevel group
1136 and pog_grp.source_type = 'PAF';
1137 --
1138 cursor csr_locker (p_asg_id in number,
1139 p_eff_date in date)
1140 is
1141 select 1 res
1142 from sys.dual
1143 where exists (
1144 select null
1145 from pay_action_classifications acl,
1146 pay_assignment_actions ac2,
1147 pay_payroll_actions pa2
1148 where ac2.assignment_id = p_asg_id
1149 and pa2.payroll_action_id = ac2.payroll_action_id
1150 and acl.classification_name = 'SEQUENCED'
1151 and pa2.action_type = acl.action_type
1152 and (pa2.effective_date > p_eff_date
1153 or (ac2.action_status not in ('C', 'S')
1154 and pa2.effective_date <= p_eff_date)));
1155 --
1156 cursor csr_ba_locker (p_asg_id in number,
1157 p_eff_date in date)
1158 is
1159 select 1 res
1160 from sys.dual
1161 where exists (
1162 select null
1163 from pay_action_classifications acl,
1164 pay_assignment_actions ac2,
1165 pay_payroll_actions pa2
1166 where ac2.assignment_id = p_asg_id
1167 and pa2.payroll_action_id = ac2.payroll_action_id
1168 and acl.classification_name = 'SEQUENCED'
1169 and pa2.action_type = acl.action_type
1170 and ac2.action_status not in ('C', 'S'));
1171 --
1172 cursor csr_locker_fut (p_asg_id in number,
1173 p_eff_date in date)
1174 is
1175 select 1 res
1176 from sys.dual
1177 where exists (
1178 select null
1179 from pay_action_classifications acl,
1180 pay_assignment_actions ac2,
1181 pay_payroll_actions pa2
1182 where ac2.assignment_id = p_asg_id
1183 and pa2.payroll_action_id = ac2.payroll_action_id
1184 and acl.classification_name = 'SEQUENCED'
1185 and pa2.action_type = acl.action_type
1186 and ((pa2.effective_date > p_eff_date
1187 and ac2.action_status in ('C', 'S'))
1188 or (ac2.action_status not in ('C', 'S')
1189 and pa2.effective_date <= p_eff_date)));
1190 --
1191 l_locked varchar2(3);
1192 --
1193 begin
1194 --
1195 l_locked := 'N';
1196 --
1197 for asgrec in dp(p_asg_id) loop
1198 if (p_future_actions = 'N') then
1199 for resrec in csr_locker(asgrec.source_id,
1200 p_effective_date) loop
1201 l_locked := 'Y';
1202 end loop;
1203 elsif (p_future_actions = 'B') then
1204 for resrec in csr_ba_locker(asgrec.source_id,
1205 p_effective_date) loop
1206 l_locked := 'Y';
1207 end loop;
1208 else
1209 for resfutrec in csr_locker_fut(asgrec.source_id,
1210 p_effective_date) loop
1211 l_locked := 'Y';
1212 end loop;
1213 end if;
1214 end loop;
1215 --
1216 return l_locked;
1217 --
1218 end process_group_seq_locked;
1219 --
1220 ---------------------------------- rbsql -----------------------------------
1221 /*
1222 NAME
1223 rbsql - RollBack SQL.
1224 DESCRIPTION
1225 Has two functions. Firstly, dynamically builds an sql statement
1226 for rollback by assignment set. Secondly, it passes back info
1227 about the assignment set that has been specified.
1228 NOTES
1229 <none>
1230 */
1231 procedure rbsql
1232 (
1233 asetid in number, -- assignment_set_id.
1234 spcinc out nocopy number, -- are there specific inclusions?
1235 spcexc out nocopy number, -- are there specific exclusions?
1236 formula in out nocopy number, -- what is the formula_id?
1237 sqlstr in out nocopy varchar2, -- returned dynamic sql string.
1238 len out nocopy number, -- length of sql string.
1239 chkno in number default null
1240 ) is
1241 include boolean;
1242 exclude boolean;
1243 payroll boolean;
1244 everyone boolean;
1245 l_prof_value varchar2(30);
1246 l_hints varchar2(1000);
1247 begin
1248 --
1249 -- We start by obtaining information about the assignment set.
1250 setinfo(asetid,everyone,include,exclude,formula,payroll);
1251 --
1252 -- For specific include and exclude parameters, we have
1253 -- to convert from boolean to numeric so we can pass
1254 -- the values back to the calling 'C' program.
1255 if(include) then
1256 spcinc := 1;
1257 else
1258 spcinc := 0;
1259 end if;
1260 --
1261 if(exclude) then
1262 spcexc := 1;
1263 else
1264 spcexc := 0;
1265 end if;
1266 /*Bug 12320011 */
1267 fnd_profile.get('PAY_ROLLBACK_BY_ASSIGNMENT_SET_HINTS', l_prof_value);
1268
1269 --
1270 -- now build the sql, based on the information.
1271 /* Modified both the queries(include,everyone) for performance issue Bug: 6689854 */
1272 if(everyone) then
1273 /*Bug 12320011 Used profile value to add hint or not */
1274 if l_prof_value is null or l_prof_value = 'Y' then
1275 l_hints := '/*+ use_nl(pac pop has pay_asg act)
1276 index(pac PAY_PAYROLL_ACTIONS_PK)
1277 rowid(pop)
1278 index(has HR_ASSIGNMENT_SETS_PK)
1279 index(pay_asg PER_ASSIGNMENTS_F_N12)
1280 index(act PAY_ASSIGNMENT_ACTIONS_N51) */ ';
1281 else
1282 l_hints := null;
1283 end if;
1284
1285
1286 sqlstr := '
1287 select '|| case chkno when null then '' else l_hints end ||' act.assignment_id,
1288 act.assignment_action_id
1289 from hr_assignment_sets has,
1290 pay_population_ranges pop,
1291 per_all_assignments_f pay_asg,
1292 pay_payroll_actions pac,
1293 pay_assignment_actions act
1294 where pac.payroll_action_id = :pactid
1295 and act.payroll_action_id = pac.payroll_action_id
1296 and act.source_action_id is null
1297 and pay_asg.assignment_id = act.assignment_id
1298 and ((pac.action_type = ''BEE''
1299 and pay_asg.effective_start_date = (select max(asg2.effective_start_date)
1300 from per_all_assignments_f asg2
1301 where asg2.assignment_id =
1302 pay_asg.assignment_id))
1303 or
1304 (pac.action_type <> ''BEE''
1305 and pac.effective_date between
1306 pay_asg.effective_start_date and pay_asg.effective_end_date))
1307 and pop.rowid = :chunk_rowid
1308 and has.assignment_set_id = :asetid';
1309 end if;
1310 --
1311 -- Specific inclusion.
1312 if(include) then
1313 /*Bug 12320011 Used profile value to add hint or not */
1314 if l_prof_value is null or l_prof_value = 'Y' then
1315 l_hints := '/*+ use_nl(pac pop amd act pay_asg)
1316 index(pac PAY_PAYROLL_ACTIONS_PK)
1317 rowid(pop)
1318 index(amd HR_ASSIGNMENT_SET_AMENDMEN_FK2)
1319 index(act PAY_ASSIGNMENT_ACTIONS_N51)
1320 index(pay_asg PER_ASSIGNMENTS_F_PK) */ ';
1321 else
1322 l_hints := null;
1323 end if;
1324
1325 sqlstr := '
1326 select '|| case chkno when null then '' else l_hints end ||' act.assignment_id,
1327 act.assignment_action_id
1328 from pay_payroll_actions pac,
1329 pay_population_ranges pop,
1330 hr_assignment_set_amendments amd,
1331 per_all_assignments_f pay_asg,
1332 pay_assignment_actions act
1333 where pac.payroll_action_id = :pactid
1334 and act.payroll_action_id = pac.payroll_action_id
1335 and act.source_action_id is null
1336 and pay_asg.assignment_id = act.assignment_id
1337 and ((pac.action_type = ''BEE''
1338 and pay_asg.effective_start_date = (select max(asg2.effective_start_date)
1339 from per_all_assignments_f asg2
1340 where asg2.assignment_id =
1341 pay_asg.assignment_id))
1342 or
1343 (pac.action_type <> ''BEE''
1344 and pac.effective_date between
1345 pay_asg.effective_start_date and pay_asg.effective_end_date))
1346 and pop.rowid = :chunk_rowid
1347 and amd.assignment_set_id = :asetid
1348 and amd.include_or_exclude = ''I''
1349 and pay_asg.assignment_id = amd.assignment_id';
1350 end if;
1351 --
1352 if(exclude) then
1353 sqlstr := sqlstr || '
1354 and not exists (
1355 select null
1356 from hr_assignment_set_amendments exc
1357 where exc.assignment_set_id = has.assignment_set_id
1358 and exc.include_or_exclude = ''E''
1359 and act.assignment_id = exc.assignment_id)';
1360 end if;
1361 --
1362 if (chkno is null) then
1363 sqlstr := sqlstr || '
1364 and pay_asg.person_id between
1365 pop.starting_person_id and pop.ending_person_id';
1366 else
1367 sqlstr := sqlstr || '
1368 and pay_asg.person_id = pop.person_id';
1369 end if;
1370 --
1371 -- Concatenate the order by statement.
1372 sqlstr := sqlstr || '
1373 order by act.action_sequence desc';
1374 --
1375 -- return length to allow null termination.
1376 len := length(sqlstr);
1377 end rbsql;
1378 --
1379 ---------------------------------- bkpsql ----------------------------------
1380 /*
1381 NAME
1382 bkpsql - build dynamic sql for BackPay.
1383 DESCRIPTION
1384 Builds dynamic sql statement for assignment set
1385 processing.
1386 NOTES
1387 <none>
1388 */
1389 procedure bkpsql
1390 (
1391 asetid in number, -- assignment_set_id.
1392 sqlstr in out nocopy varchar2, -- returned string.
1393 len out nocopy number -- length of returned string.
1394 ) is
1395 include boolean;
1396 exclude boolean;
1397 formula number;
1398 payroll boolean;
1399 everyone boolean; -- if true, means all assignments.
1400 begin
1401 -- Get information about the assignment set.
1402 setinfo(asetid,everyone,include,exclude,formula,payroll);
1403 --
1404 -- Use information to build sql statements.
1405 if(everyone) then
1406 sqlstr := '
1407 select pay_asg.assignment_id
1408 from per_all_assignments_f pay_asg,
1409 hr_assignment_sets has
1410 where has.assignment_set_id = :v_asg_set
1411 and pay_asg.payroll_id = has.payroll_id
1412 and fnd_date.canonical_to_date(:v_effective_date) between
1413 pay_asg.effective_start_date and pay_asg.effective_end_date';
1414 end if;
1415 --
1416 if(include) then
1417 sqlstr := '
1418 select pay_asg.assignment_id
1419 from per_all_assignments_f pay_asg,
1420 hr_assignment_sets has,
1421 hr_assignment_set_amendments amd
1422 where has.assignment_set_id = :asetid
1423 and amd.assignment_set_id = has.assignment_set_id
1424 and pay_asg.payroll_id + 0 = has.payroll_id
1425 and pay_asg.assignment_id = amd.assignment_id
1426 and amd.include_or_exclude = ''I''
1427 and fnd_date.canonical_to_date(:v_effective_date) between
1428 pay_asg.effective_start_date and pay_asg.effective_end_date';
1429 end if;
1430 --
1431 if(exclude) then
1432 sqlstr := '
1433 select pay_asg.assignment_id
1434 from per_all_assignments_f pay_asg,
1435 hr_assignment_sets has
1436 where has.assignment_set_id = :asetid
1437 and pay_asg.payroll_id = has.payroll_id
1438 and fnd_date.canonical_to_date(:v_effective_date) between
1439 pay_asg.effective_start_date and pay_asg.effective_end_date
1440 and not exists (
1441 select null
1442 from hr_assignment_set_amendments amd
1443 where amd.assignment_set_id = has.assignment_set_id
1444 and pay_asg.assignment_id = amd.assignment_id
1445 and amd.include_or_exclude = ''E'')';
1446 end if;
1447 --
1448 -- return length to allow null termination.
1449 len := length(sqlstr);
1450 --
1451 end bkpsql;
1452 --
1453 ---------------------------------- cbsql -----------------------------------
1454 /*
1455 NAME
1456 cbsql - Create Batches SQL.
1457 DESCRIPTION
1458 Has two functions. Firstly, dynamically builds an sql statement
1459 for creating batch by assignment set. Secondly, it passes back info
1460 about the assignment set that has been specified.
1461 NOTES
1462 <none>
1463 */
1464 procedure cbsql
1465 (
1466 asetid in number default 0, -- assignment_set_id.
1467 elsetid in number default null, -- element set id.
1468 spcinc out nocopy number, -- are there specific inclusions?
1469 spcexc out nocopy number, -- are there specific exclusions?
1470 formula in out nocopy number, -- what is the formula_id?
1471 sqlstr in out nocopy varchar2, -- returned dynamic sql string.
1472 len out nocopy number -- length of sql string.
1473 ) is
1474 include boolean;
1475 exclude boolean;
1476 payroll boolean;
1477 everyone boolean;
1478 begin
1479 --
1480 -- We start by obtaining information about the assignment set.
1481 if(asetid <> 0) then
1482 setinfo(asetid,everyone,include,exclude,formula,payroll);
1483 --
1484 -- For specific include and exclude parameters, we have
1485 -- to convert from boolean to numeric so we can pass
1486 -- the values back to the calling 'C' program.
1487 if(include) then
1488 spcinc := 1;
1489 else
1490 spcinc := 0;
1491 end if;
1492 --
1493 if(exclude) then
1494 spcexc := 1;
1495 else
1496 spcexc := 0;
1497 end if;
1498 --
1499 -- now build the sql, based on the information.
1500 if(everyone) then
1501 if (elsetid is not null) then
1502 --
1503 sqlstr := '
1504 select pay_asg.assignment_id, pay_asg.assignment_number,
1505 pay_asg.payroll_id, pesm.element_type_id, petf.element_name
1506 from per_all_assignments_f pay_asg,
1507 hr_assignment_sets has,
1508 PAY_ELEMENT_SET_MEMBERS pesm,
1509 pay_element_types_f petf
1510 where pay_asg.business_group_id = :p_bgid
1511 and has.assignment_set_id = :pasetid
1512 and pay_asg.assignment_type = ''E''
1513 and fnd_date.canonical_to_date(:p_effective_date)
1514 between pay_asg.effective_start_date
1515 and pay_asg.effective_end_date
1516 and pesm.element_set_id = :p_elesetid
1517 and petf.element_type_id = pesm.element_type_id
1518 and fnd_date.canonical_to_date(:p_effective_date) between
1519 petf.effective_start_date and petf.effective_end_date
1520 and ((petf.business_group_id is null and petf.legislation_code is null) or
1521 (petf.business_group_id is null and petf.legislation_code = :p_legcode) or
1522 (petf.business_group_id = :p_bgid))
1523 and (exists
1524 (select null
1525 from pay_restriction_values psv
1526 where psv.restriction_code = ''ELEMENT_TYPE''
1527 and psv.customized_restriction_id = :p_restrictid
1528 and (psv.value = ''BOTH'' or psv.value = petf.processing_type))
1529 or not exists
1530 (select null
1531 from pay_restriction_values psv
1532 where psv.restriction_code = ''ELEMENT_TYPE''
1533 and psv.customized_restriction_id = :p_restrictid))';
1534 --
1535 else
1536 --
1537 sqlstr := '
1538 select pay_asg.assignment_id, pay_asg.assignment_number,
1539 pay_asg.payroll_id,petf.element_type_id, petf.element_name
1540 from per_all_assignments_f pay_asg,
1541 hr_assignment_sets has,
1542 pay_element_types_f petf
1543 where pay_asg.business_group_id = :p_bgid
1544 and has.assignment_set_id = :pasetid
1545 and pay_asg.assignment_type = ''E''
1546 and fnd_date.canonical_to_date(:p_effective_date)
1547 between petf.effective_start_date
1548 and petf.effective_end_date
1549 and petf.element_type_id = :p_element_id
1550 and fnd_date.canonical_to_date(:p_effective_date)
1551 between pay_asg.effective_start_date
1552 and pay_asg.effective_end_date';
1553 --
1554 end if;
1555 end if;
1556 if(include) then
1557 if (elsetid is not null) then
1558 --
1559 sqlstr := '
1560 select pay_asg.assignment_id, pay_asg.assignment_number,
1561 pay_asg.payroll_id, pesm.element_type_id, petf.element_name
1562 from per_all_assignments_f pay_asg,
1563 hr_assignment_sets has,
1564 hr_assignment_set_amendments amd,
1565 PAY_ELEMENT_SET_MEMBERS pesm,
1566 pay_element_types_f petf
1567 where pay_asg.business_group_id = :p_bgid
1568 and has.assignment_set_id = :pasetid
1569 and amd.assignment_set_id = has.assignment_set_id
1570 and pay_asg.assignment_id = amd.assignment_id
1571 and pay_asg.assignment_type = ''E''
1572 and amd.include_or_exclude = ''I''
1573 and fnd_date.canonical_to_date(:p_effective_date) between
1574 pay_asg.effective_start_date and pay_asg.effective_end_date
1575 and pesm.element_set_id = :p_elesetid
1576 and petf.element_type_id = pesm.element_type_id
1577 and fnd_date.canonical_to_date(:p_effective_date) between
1578 petf.effective_start_date and petf.effective_end_date
1579 and ((petf.business_group_id is null and petf.legislation_code is null) or
1580 (petf.business_group_id is null and petf.legislation_code = :p_legcode) or
1581 (petf.business_group_id = :p_bgid))
1582 and (exists
1583 (select null
1584 from pay_restriction_values psv
1585 where psv.restriction_code = ''ELEMENT_TYPE''
1586 and psv.customized_restriction_id = :p_restrictid
1587 and (psv.value = ''BOTH'' or psv.value = petf.processing_type))
1588 or not exists
1589 (select null
1590 from pay_restriction_values psv
1591 where psv.restriction_code = ''ELEMENT_TYPE''
1592 and psv.customized_restriction_id = :p_restrictid))';
1593 --
1594 else
1595 --
1596 sqlstr := '
1597 select pay_asg.assignment_id, pay_asg.assignment_number,
1598 pay_asg.payroll_id,petf.element_type_id, petf.element_name
1599 from per_all_assignments_f pay_asg,
1600 hr_assignment_sets has,
1601 hr_assignment_set_amendments amd,
1602 pay_element_types_f petf
1603 where pay_asg.business_group_id = :p_bgid
1604 and has.assignment_set_id = :pasetid
1605 and amd.assignment_set_id = has.assignment_set_id
1606 and pay_asg.assignment_id = amd.assignment_id
1607 and pay_asg.assignment_type = ''E''
1608 and amd.include_or_exclude = ''I''
1609 and fnd_date.canonical_to_date(:p_effective_date)
1610 between petf.effective_start_date
1611 and petf.effective_end_date
1612 and petf.element_type_id = :p_element_id
1613 and fnd_date.canonical_to_date(:p_effective_date) between
1614 pay_asg.effective_start_date and pay_asg.effective_end_date';
1615 --
1616 end if;
1617 end if;
1618 if(exclude) then
1619 if (elsetid is not null) then
1620 --
1621 sqlstr := '
1622 select pay_asg.assignment_id, pay_asg.assignment_number,
1623 pay_asg.payroll_id, pesm.element_type_id, petf.element_name
1624 from per_all_assignments_f pay_asg,
1625 hr_assignment_sets has,
1626 PAY_ELEMENT_SET_MEMBERS pesm,
1627 pay_element_types_f petf
1628 where pay_asg.business_group_id = :p_bgid
1629 and has.assignment_set_id = :pasetid
1630 and pay_asg.assignment_type = ''E''
1631 and fnd_date.canonical_to_date(:p_effective_date) between
1632 pay_asg.effective_start_date and pay_asg.effective_end_date
1633 and not exists (
1634 select null
1635 from hr_assignment_set_amendments amd
1636 where amd.assignment_set_id = has.assignment_set_id
1637 and pay_asg.assignment_id = amd.assignment_id
1638 and amd.include_or_exclude = ''E'')
1639 and pesm.element_set_id = :p_elesetid
1640 and petf.element_type_id = pesm.element_type_id
1641 and fnd_date.canonical_to_date(:p_effective_date) between
1642 petf.effective_start_date and petf.effective_end_date
1643 and ((petf.business_group_id is null and petf.legislation_code is null) or
1644 (petf.business_group_id is null and petf.legislation_code = :p_legcode) or
1645 (petf.business_group_id = :p_bgid))
1646 and (exists
1647 (select null
1648 from pay_restriction_values psv
1649 where psv.restriction_code = ''ELEMENT_TYPE''
1650 and psv.customized_restriction_id = :p_restrictid
1651 and (psv.value = ''BOTH'' or psv.value = petf.processing_type))
1652 or not exists
1653 (select null
1654 from pay_restriction_values psv
1655 where psv.restriction_code = ''ELEMENT_TYPE''
1656 and psv.customized_restriction_id = :p_restrictid))';
1657 --
1658 else
1659 --
1660 sqlstr := '
1661 select pay_asg.assignment_id, pay_asg.assignment_number,
1662 pay_asg.payroll_id,petf.element_type_id, petf.element_name
1663 from per_all_assignments_f pay_asg,
1664 hr_assignment_sets has,
1665 pay_element_types_f petf
1666 where pay_asg.business_group_id = :p_bgid
1667 and has.assignment_set_id = :pasetid
1668 and pay_asg.assignment_type = ''E''
1669 and fnd_date.canonical_to_date(:p_effective_date)
1670 between petf.effective_start_date
1671 and petf.effective_end_date
1672 and petf.element_type_id = :p_element_id
1673 and fnd_date.canonical_to_date(:p_effective_date) between
1674 pay_asg.effective_start_date and pay_asg.effective_end_date
1675 and not exists (
1676 select null
1677 from hr_assignment_set_amendments amd
1678 where amd.assignment_set_id = has.assignment_set_id
1679 and pay_asg.assignment_id = amd.assignment_id
1680 and amd.include_or_exclude = ''E'')';
1681 --
1682 end if;
1683 end if;
1684 --
1685 -- Add payroll restricted clause
1686 if(payroll) then
1687 sqlstr := sqlstr || '
1688 and pay_asg.payroll_id + 0 = has.payroll_id';
1689 end if;
1690 else
1691 -- if asetid is not specified, then
1692 -- select everyone on the business group.
1693
1694 if (elsetid is not null) then
1695 --
1696 sqlstr := '
1697 select pay_asg.assignment_id, pay_asg.assignment_number,
1698 pay_asg.payroll_id, pesm.element_type_id, petf.element_name
1699 from per_all_assignments_f pay_asg,
1700 PAY_ELEMENT_SET_MEMBERS pesm,
1701 pay_element_types_f petf
1702 where pay_asg.business_group_id = :p_bgid
1703 and pay_asg.assignment_type = ''E''
1704 and fnd_date.canonical_to_date(:p_effective_date) between
1705 pay_asg.effective_start_date and pay_asg.effective_end_date
1706 and pesm.element_set_id = :p_elesetid
1707 and petf.element_type_id = pesm.element_type_id
1708 and fnd_date.canonical_to_date(:p_effective_date) between
1709 petf.effective_start_date and petf.effective_end_date
1710 and ((petf.business_group_id is null and petf.legislation_code is null) or
1711 (petf.business_group_id is null and petf.legislation_code = :p_legcode) or
1712 (petf.business_group_id = :p_bgid))
1713 and (exists
1714 (select null
1715 from pay_restriction_values psv
1716 where psv.restriction_code = ''ELEMENT_TYPE''
1717 and psv.customized_restriction_id = :p_restrictid
1718 and (psv.value = ''BOTH'' or psv.value = petf.processing_type))
1719 or not exists
1720 (select null
1721 from pay_restriction_values psv
1722 where psv.restriction_code = ''ELEMENT_TYPE''
1723 and psv.customized_restriction_id = :p_restrictid))';
1724 --
1725 else
1726 --
1727 sqlstr := '
1728 select pay_asg.assignment_id, pay_asg.assignment_number,
1729 pay_asg.payroll_id,petf.element_type_id, petf.element_name
1730 from per_all_assignments_f pay_asg,
1731 pay_element_types_f petf
1732 where pay_asg.business_group_id = :p_bgid
1733 and pay_asg.assignment_type = ''E''
1734 and fnd_date.canonical_to_date(:p_effective_date)
1735 between petf.effective_start_date
1736 and petf.effective_end_date
1737 and petf.element_type_id = :p_element_id
1738 and fnd_date.canonical_to_date(:p_effective_date) between
1739 pay_asg.effective_start_date and pay_asg.effective_end_date';
1740 --
1741 end if;
1742
1743 formula := 0;
1744 spcinc := 0;
1745 spcexc := 0;
1746 end if;
1747 -- return length to allow null termination.
1748 len := length(sqlstr);
1749 --
1750 end cbsql;
1751
1752 ---------------------------------- qptsql ----------------------------------
1753 /*
1754 NAME
1755 qptsql - build dynamic sql for QuickPaint.
1756 DESCRIPTION
1757 Builds dynamic sql strings for QuickPaint.
1758 It decides which sql is required from
1759 the assignment_set_id passed in.
1760 NOTES
1761 <none>
1762 */
1763 procedure qptsql
1764 (
1765 asetid in number, -- assignment_set_id.
1766 sqlstr in out nocopy varchar2, -- returned string.
1767 len out nocopy number -- length of returned string.
1768 ) is
1769 include boolean;
1770 exclude boolean;
1771 formula number;
1772 payroll boolean;
1773 everyone boolean; -- if true, means all assignments.
1774 begin
1775 -- get information about assignment set.
1776 setinfo(asetid,everyone,include,exclude,formula,payroll);
1777 --
1778 -- now build the sql, based on the information.
1779 if(everyone and (not include)) then
1780 sqlstr := '
1781 select pay_asg.assignment_id,
1782 pay_asg.payroll_id
1783 from per_all_assignments_f pay_asg,
1784 hr_assignment_sets has,
1785 per_quickpaint_invocations inv
1786 where inv.qp_invocation_id = :qp_invocation_id
1787 and has.assignment_set_id = inv.invocation_context
1788 and pay_asg.business_group_id = has.business_group_id
1789 and inv.effective_date between
1790 pay_asg.effective_start_date and pay_asg.effective_end_date';
1791 end if;
1792 --
1793 -- Specific inclusion.
1794 if(include) then
1795 sqlstr := '
1796 select pay_asg.assignment_id,
1797 pay_asg.payroll_id
1798 from per_all_assignments_f pay_asg,
1799 hr_assignment_sets has,
1800 hr_assignment_set_amendments amd,
1801 per_quickpaint_invocations inv
1802 where inv.qp_invocation_id = :qp_invocation_id
1803 and has.assignment_set_id = inv.invocation_context
1804 and amd.assignment_set_id = has.assignment_set_id
1805 and amd.include_or_exclude = ''I''
1806 and pay_asg.assignment_id = amd.assignment_id
1807 and pay_asg.business_group_id + 0 = has.business_group_id + 0
1808 and inv.effective_date between
1809 pay_asg.effective_start_date and pay_asg.effective_end_date';
1810 end if;
1811 --
1812 if(payroll) then
1813 sqlstr := sqlstr || '
1814 and pay_asg.payroll_id = has.payroll_id';
1815 end if;
1816 --
1817 if(exclude) then
1818 sqlstr := sqlstr || '
1819 and not exists (
1820 select null
1821 from hr_assignment_set_amendments amd
1822 where amd.assignment_set_id = has.assignment_set_id
1823 and amd.include_or_exclude = ''E''
1824 and pay_asg.assignment_id = amd.assignment_id)';
1825 end if;
1826 --
1827 -- return length to allow null termination.
1828 len := length(sqlstr);
1829 end qptsql;
1830 --
1831 ------------------------------ archive_range -------------------------------
1832 /*
1833 NAME
1834 archive_range - calls legislative range code.
1835 DESCRIPTION
1836 This checks the type of report that is running and then calls the
1837 appropreate code that defines the select statement for the
1838 population ranges.
1839 NOTES
1840 */
1841 procedure archive_range(pactid in number,
1842 sqlstr in out nocopy varchar2
1843 )
1844 is
1845 sql_cur number;
1846 ignore number;
1847 range_proc varchar2(60);
1848 statem varchar2(256);
1849 begin
1850 pay_proc_environment_pkg.pactid := pactid;
1851
1852 select range_code
1853 into range_proc
1854 from pay_report_format_mappings_f prfm,
1855 pay_payroll_actions ppa
1856 where ppa.payroll_action_id = pactid
1857 and ppa.report_type = prfm.report_type
1858 and ppa.report_qualifier = prfm.report_qualifier
1859 and ppa.report_category = prfm.report_category
1860 and ppa.effective_date between prfm.effective_start_date
1861 and prfm.effective_end_date;
1862 --
1863 /* Range code should always be set */
1864 if (range_proc is null) then
1865 hr_utility.set_message(801, 'PAY_34958_ARCRGE_MUST_EXIST');
1866 hr_utility.raise_error;
1867 end if;
1868 --
1869 statem := 'BEGIN '||range_proc||'(:pactid, :sqlstr); END;';
1870 --
1871 sql_cur := dbms_sql.open_cursor;
1872 dbms_sql.parse(sql_cur,
1873 statem,
1874 dbms_sql.v7);
1875 dbms_sql.bind_variable(sql_cur, ':pactid', pactid);
1876 dbms_sql.bind_variable(sql_cur, ':sqlstr', sqlstr, max_dynsql_len);
1877 ignore := dbms_sql.execute(sql_cur);
1878 dbms_sql.variable_value(sql_cur, ':sqlstr', sqlstr);
1879 dbms_sql.close_cursor(sql_cur);
1880 --
1881 return;
1882 --
1883 exception
1884 when others then
1885 if (dbms_sql.is_open(sql_cur)) then
1886 dbms_sql.close_cursor(sql_cur);
1887 end if;
1888 raise;
1889 end archive_range;
1890 --
1891 ------------------------------ get_local_unit -------------------------------
1892 /*
1893 NAME
1894 get_local_unit - this is used to retrieve the local unit id if valid.
1895 DESCRIPTION
1896 This is used to identify the local unit when processing run results.
1897 NOTES
1898 */
1899 function get_local_unit
1900 (
1901 p_assignment_id number
1902 ,p_effective_date date
1903 ) return number is
1904 --
1905 -- Holds the tax unit an assignment belongs to.
1906 --
1907 l_local_unit_id number;
1908 l_legislation per_business_groups_perf.legislation_code%type;
1909 l_business_group_id per_business_groups_perf.business_group_id%type;
1910 plsql_state varchar2(2000); -- used with dynamic pl/sql
1911 sql_cursor integer;
1912 l_rows integer;
1913 l_found boolean;
1914 l_dummy number;
1915 --
1916 begin
1917 --
1918 l_local_unit_id := NULL;
1919 --
1920 if g_cached = FALSE then
1921
1922 select /*+ INDEX(paf PER_ASSIGNMENTS_F_PK)*/
1923 pbg.legislation_code
1924 into l_legislation
1925 from per_all_assignments_f paf,
1926 per_business_groups_perf pbg
1927 where paf.assignment_id = p_assignment_id
1928 and p_effective_date between paf.effective_start_date
1929 and paf.effective_end_date
1930 and paf.business_group_id = pbg.business_group_id;
1931 --
1932 pay_core_utils.get_legislation_rule('LOCAL_UNIT_CONTEXT',
1933 l_legislation,
1934 g_local_unit,
1935 l_found
1936 );
1937 --
1938 if (l_found = FALSE) then
1939 g_local_unit := 'N';
1940 end if;
1941 --
1942 pay_core_utils.get_legislation_rule('TAX_UNIT',
1943 l_legislation,
1944 g_tax_unit,
1945 l_found
1946 );
1947 --
1948 if (l_found = FALSE) then
1949 g_tax_unit := 'N';
1950 end if;
1951
1952 g_cached := TRUE;
1953 end if;
1954 --
1955 --
1956 -- Get the local unit the assignment belongs to.
1957 --
1958 if (g_local_unit = 'Y') then
1959 --
1960 -- Dynamically get the tax unit.
1961 --
1962 select /*+ INDEX(paf PER_ASSIGNMENTS_F_PK)*/
1963 pbg.legislation_code,
1964 pbg.business_group_id
1965 into l_legislation,
1966 l_business_group_id
1967 from per_all_assignments_f paf,
1968 per_business_groups_perf pbg
1969 where paf.assignment_id = p_assignment_id
1970 and p_effective_date between paf.effective_start_date
1971 and paf.effective_end_date
1972 and paf.business_group_id = pbg.business_group_id;
1973 --
1974 plsql_state := 'begin pay_'||l_legislation||'_rules.get_main_local_unit_id(
1975 p_assignment_id =>:p_assignment_id,
1976 p_effective_date => :p_effective_date,
1977 p_local_unit_id => :l_local_unit_id); end;';
1978 --
1979 sql_cursor := dbms_sql.open_cursor;
1980 dbms_sql.parse(sql_cursor, plsql_state, dbms_sql.v7);
1981 dbms_sql.bind_variable(sql_cursor, 'p_assignment_id', p_assignment_id);
1982 dbms_sql.bind_variable(sql_cursor, 'p_effective_date', p_effective_date);
1983 dbms_sql.bind_variable(sql_cursor, 'l_local_unit_id', l_local_unit_id);
1984 l_rows := dbms_sql.execute(sql_cursor);
1985 if (l_rows = 1) then
1986 dbms_sql.variable_value(sql_cursor, 'l_local_unit_id',
1987 l_local_unit_id);
1988 dbms_sql.close_cursor(sql_cursor);
1989 --
1990 else
1991 l_local_unit_id := null;
1992 dbms_sql.close_cursor(sql_cursor);
1993 end if;
1994 end if;
1995
1996 --
1997 /* Before we leave, just check that the Local Unit is valid */
1998 if (l_local_unit_id is not null) then
1999 --
2000 -- If we are here then business group and legislation
2001 -- code should be known.
2002 select 1
2003 into l_dummy
2004 from dual
2005 where exists (
2006 select ''
2007 from hr_organization_units hou,
2008 hr_organization_information houi
2009 where hou.organization_id = houi.organization_id
2010 and hou.organization_id = l_local_unit_id
2011 and houi.org_information_context = 'CLASS'
2012 and houi.org_information1 =
2013 upper(l_legislation||'_LOCAL_UNIT')
2014 and hou.business_group_id = l_business_group_id
2015 );
2016 --
2017 end if;
2018 --
2019 --
2020 -- Return the tax unit.
2021 --
2022 return (l_local_unit_id);
2023 --
2024 end get_local_unit;
2025
2026 ------------------------------ get_tax_unit -------------------------------
2027 /*
2028 NAME
2029 get_tax_unit - this is used to retrieve the tax unit id if valid.
2030 DESCRIPTION
2031 This is used by the assignment action creation code to find the
2032 value of the tax unit id.
2033 NOTES
2034 */
2035 function get_tax_unit
2036 (
2037 p_assignment_id number
2038 ,p_effective_date date
2039 ) return number is
2040 --
2041 -- Retrieves the legal company an assignment belongs to at a given date.
2042 --
2043 cursor csr_tax_unit
2044 (
2045 p_assignment_id number
2046 ,p_effective_date date
2047 ) is
2048 select to_number(SCL.segment1) tax_unit_id
2049 from per_all_assignments_f ASG
2050 ,hr_soft_coding_keyflex SCL
2051 where ASG.assignment_id = p_assignment_id
2052 and SCL.soft_coding_keyflex_id = ASG.soft_coding_keyflex_id
2053 and p_effective_date between ASG.effective_start_date
2054 and ASG.effective_end_date;
2055 --
2056 -- Retrieves the establishment id an assignment belongs to at a given date.
2057 --
2058 cursor csr_est_id
2059 (
2060 p_assignment_id number
2061 ,p_effective_date date
2062 ) is
2063 select establishment_id
2064 from per_all_assignments_f ASG
2065 where ASG.assignment_id = p_assignment_id
2066 and p_effective_date between ASG.effective_start_date
2067 and ASG.effective_end_date;
2068 --
2069 -- Holds the tax unit an assignment belongs to.
2070 --
2071 l_tax_unit_id number;
2072 l_legislation per_business_groups_perf.legislation_code%type;
2073 plsql_state varchar2(2000); -- used with dynamic pl/sql
2074 sql_cursor integer;
2075 l_rows integer;
2076 l_found boolean;
2077 --
2078 begin
2079 --
2080 l_tax_unit_id := NULL;
2081 --
2082 if g_cached = FALSE then
2083
2084 select /*+ INDEX(paf PER_ASSIGNMENTS_F_PK)*/
2085 pbg.legislation_code
2086 into l_legislation
2087 from per_all_assignments_f paf,
2088 per_business_groups_perf pbg
2089 where paf.assignment_id = p_assignment_id
2090 and p_effective_date between paf.effective_start_date
2091 and paf.effective_end_date
2092 and paf.business_group_id = pbg.business_group_id;
2093 --
2094 --
2095 pay_core_utils.get_legislation_rule('LOCAL_UNIT_CONTEXT',
2096 l_legislation,
2097 g_local_unit,
2098 l_found
2099 );
2100 --
2101 if (l_found = FALSE) then
2102 g_local_unit := 'N';
2103 end if;
2104 --
2105 pay_core_utils.get_legislation_rule('TAX_UNIT',
2106 l_legislation,
2107 g_tax_unit,
2108 l_found
2109 );
2110 --
2111 if (l_found = FALSE) then
2112 g_tax_unit := 'N';
2113 end if;
2114
2115 g_cached := TRUE;
2116 end if;
2117 --
2118 --
2119 -- Get the legal company the assignment belongs to.
2120 --
2121 if (g_tax_unit = 'Y') then
2122 open csr_tax_unit(p_assignment_id
2123 ,p_effective_date);
2124 fetch csr_tax_unit into l_tax_unit_id;
2125 close csr_tax_unit;
2126 elsif (g_tax_unit = 'E') then
2127 open csr_est_id(p_assignment_id
2128 ,p_effective_date);
2129 fetch csr_est_id into l_tax_unit_id;
2130 close csr_est_id;
2131 elsif (g_tax_unit = 'D') then
2132 --
2133 -- Dynamically get the tax unit.
2134 --
2135 select /*+ INDEX(paf PER_ASSIGNMENTS_F_PK)*/
2136 pbg.legislation_code
2137 into l_legislation
2138 from per_all_assignments_f paf,
2139 per_business_groups_perf pbg
2140 where paf.assignment_id = p_assignment_id
2141 and p_effective_date between paf.effective_start_date
2142 and paf.effective_end_date
2143 and paf.business_group_id = pbg.business_group_id;
2144 --
2145 plsql_state := 'begin pay_'||l_legislation||'_rules.get_main_tax_unit_id(
2146 p_assignment_id =>:p_assignment_id,
2147 p_effective_date => :p_effective_date,
2148 p_tax_unit_id => :l_tax_unit_id); end;';
2149 --
2150 sql_cursor := dbms_sql.open_cursor;
2151 dbms_sql.parse(sql_cursor, plsql_state, dbms_sql.v7);
2152 dbms_sql.bind_variable(sql_cursor, 'p_assignment_id', p_assignment_id);
2153 dbms_sql.bind_variable(sql_cursor, 'p_effective_date', p_effective_date);
2154 dbms_sql.bind_variable(sql_cursor, 'l_tax_unit_id', l_tax_unit_id);
2155 l_rows := dbms_sql.execute(sql_cursor);
2156 if (l_rows = 1) then
2157 dbms_sql.variable_value(sql_cursor, 'l_tax_unit_id',
2158 l_tax_unit_id);
2159 dbms_sql.close_cursor(sql_cursor);
2160 --
2161 else
2162 l_tax_unit_id := null;
2163 dbms_sql.close_cursor(sql_cursor);
2164 end if;
2165
2166 end if;
2167 --
2168 --
2169 -- Return the tax unit.
2170 --
2171 return (l_tax_unit_id);
2172 --
2173 end get_tax_unit;
2174 --
2175 ---------------------------------- pyrsql ----------------------------------
2176 /*
2177 NAME
2178 pyrsql - build dynamic sql.
2179 DESCRIPTION
2180 builds an SQL statement from a 'kit of parts'.
2181 It concatenates various parts together depending on
2182 what is required, which is dependent on factors such
2183 as what sort of statement we require, whether we are
2184 dealing with time dependent/independent legislation
2185 and so on.
2186 NOTES
2187 It is useful to remember what the value of
2188 the 'interlock' flag means. If 'Y', it means
2189 the sql statement does NOT add a part to exclude
2190 assignments failing the interlock rules, if 'N'
2191 it DOES.
2192 The procedure passes back the length of the resultant
2193 string, so it can be successfully null terminated by
2194 the calling program.
2195 */
2196 procedure pyrsql
2197 (
2198 sqlid in number,
2199 timedepflg in varchar2,
2200 interlock in varchar2,
2201 sqlstr in out nocopy varchar2,
2202 len out nocopy number,
2203 action in varchar2 default 'R',
2204 pactid in number default null,
2205 chkno in number default null
2206 ) is
2207 PY_ALLASG constant number := 1;
2208 PY_SPCINC constant number := 2;
2209 PY_SPCEXC constant number := 3;
2210 PY_RUNRGE constant number := 4;
2211 PY_RESRGE constant number := 5;
2212 PY_NONRGE constant number := 6;
2213 PY_PURRGE constant number := 7; -- Purge.
2214 PY_RETRGE constant number := 8; -- RetroPay By Element
2215 PY_RETASG constant number := 9;
2216 PY_QRETASG constant number := 10; -- Quick RetroPay changes
2217 PY_QRETRGE constant number := 11; -- Quick RetroPay changes
2218 PYG_AT_RET constant varchar2(1) := 'O';
2219 PYG_AT_ARC constant varchar2(1) := 'X';
2220 PYG_AT_RUN constant varchar2(1) := 'R';
2221 PYG_AT_ADV constant varchar2(1) := 'F';
2222 PYG_AT_RTA constant varchar2(1) := 'G';
2223 PYG_AT_RTE constant varchar2(1) := 'L';
2224 PYG_AT_RCS constant varchar2(1) := 'S';
2225 PYG_AT_PUR constant varchar2(1) := 'Z'; -- Purge.
2226 PYG_AT_ADE constant varchar2(1) := 'W';
2227 PYG_AT_BEE constant varchar2(3) := 'BEE'; -- BEE Process
2228 PYG_AT_ECS constant varchar2(3) := 'EC'; -- Estimate Costing Process
2229 PYG_AT_BAL constant varchar2(1) := 'B';
2230 PYG_AT_CHQ constant varchar2(1) := 'H'; -- ChequeWriter
2231 PYG_AT_MAG constant varchar2(1) := 'M'; -- Magnetic Payment
2232 PYG_AT_PST constant varchar2(2) := 'PP'; -- Postal Payment
2233 PYG_AT_PRU constant varchar2(3) := 'PRU'; -- Payroll Roll Up
2234 PYG_AT_CSH constant varchar2(1) := 'A'; -- Cash Payment
2235 PYG_AT_REV constant varchar2(1) := 'V'; -- Reversal
2236
2237 l_ret_timedepflg varchar2(1);
2238 l_asg_set_id number;
2239 l_inc_or_excl HR_ASSIGNMENT_SET_AMENDMENTS.INCLUDE_OR_EXCLUDE%TYPE;
2240 begin
2241 --
2242 pay_proc_logging.PY_ENTRY('hr_dynsql.pyrsql');
2243 --
2244 --
2245 hr_utility.trace('sqlid = '||sqlid);
2246 hr_utility.trace('timedepflg = '||timedepflg);
2247 hr_utility.trace('interlock = '||interlock);
2248 hr_utility.trace('action = '||action);
2249 hr_utility.trace('pactid = '||pactid);
2250 hr_utility.trace('chkno = '||chkno);
2251
2252 if (chkno is null) then
2253 range := nopoprange;
2254 else
2255 range := poprange;
2256 end if;
2257 -- go through each of the sql sub strings and see if
2258 -- they are needed.
2259 if (action = PYG_AT_RET OR
2260 action = PYG_AT_RTA OR
2261 action = PYG_AT_RTE ) then
2262 --
2263 -- Force Time Independent for Retropay (if not Group Dependent)
2264 --
2265 if (timedepflg = 'N') then
2266 l_ret_timedepflg := 'Y';
2267 else
2268 l_ret_timedepflg := timedepflg;
2269 end if;
2270 if (sqlid = PY_ALLASG) then
2271 sqlstr := retasactsel || allretasg || range;
2272 if (interlock = 'N') then
2273 if(l_ret_timedepflg = 'Y') then
2274 sqlstr := sqlstr || intretind; -- time independent leg.
2275 elsif (l_ret_timedepflg = 'G') then
2276 sqlstr := sqlstr || intgrpdepret; -- time dependent on group leg.
2277 else
2278 sqlstr := sqlstr || intretdep; -- time dependent leg.
2279 end if;
2280 end if;
2281 sqlstr := sqlstr || fupdate;
2282 elsif (sqlid = PY_SPCINC) then
2283 sqlstr := retasactsel || spcretinc || range;
2284 if (interlock = 'N') then
2285 if(l_ret_timedepflg = 'Y') then
2286 sqlstr := sqlstr || intretind; -- time independent leg.
2287 elsif (l_ret_timedepflg = 'G') then
2288 sqlstr := sqlstr || intgrpdepret; -- time dependent on group leg.
2289 else
2290 sqlstr := sqlstr || intretdepaset; -- time dependent leg.
2291 end if;
2292 end if;
2293 sqlstr := sqlstr || fupdate;
2294 elsif (sqlid = PY_SPCEXC) then
2295 sqlstr := retasactsel || allretasg || range || excspc;
2296 if (interlock = 'N') then
2297 if(l_ret_timedepflg = 'Y') then
2298 sqlstr := sqlstr || intretind; -- time independent leg.
2299 elsif (l_ret_timedepflg = 'G') then
2300 sqlstr := sqlstr || intgrpdepret; -- time dependent on group leg.
2301 else
2302 sqlstr := sqlstr || intretdep; -- time dependent leg.
2303 end if;
2304 end if;
2305 sqlstr := sqlstr || fupdate;
2306 elsif (sqlid = PY_RUNRGE) then
2307 sqlstr := rrsel || allretasg || orderby;
2308 elsif (sqlid = PY_RETRGE) then
2309 sqlstr := ordrrsel || retdefasg || orderby;
2310 /* Bug 1308309, Quick-Retropay Changes */
2311 elsif (sqlid = PY_QRETRGE) then
2312 sqlstr := ordrrsel || qretdefasg || orderby;
2313 elsif (sqlid = PY_RETASG) then
2314 --
2315 -- If time dependant flag is G then the system is
2316 -- setup do do multi asg processing.
2317 --
2318 if (l_ret_timedepflg = 'G') then
2319 -- get group range
2320 if (chkno is null) then
2321 range := grpnopoprange;
2322 else
2323 range := grppoprange;
2324 end if;
2325 sqlstr := retpgasactsel || retdefasgpg || range || intretgrpdep;
2326 else
2327 sqlstr := retasactsel || retdefasg || range || intretind;
2328 end if;
2329 /* Bug 1308309, Quick-Retropay Changes start */
2330 elsif (sqlid = PY_QRETASG) then
2331 --
2332 -- If time dependant flag is G then the system is
2333 -- setup do do multi asg processing.
2334 --
2335 if (l_ret_timedepflg = 'G') then
2336 -- get group range
2337 if (chkno is null) then
2338 range := grpnopoprange;
2339 else
2340 range := grppoprange;
2341 end if;
2342 sqlstr := retpgasactsel || qretdefasgpg || range || intretgrpdep;
2343 else
2344 sqlstr := retasactsel || qretdefasg || range || intretind;
2345 end if;
2346 /* Bug 1308309, Quick-Retropay changes end */
2347 end if;
2348 elsif (action = PYG_AT_ADV) then
2349 if (sqlid = PY_ALLASG) then
2350 sqlstr := asactsel || alladvasg ||range||intretind;
2351 elsif (sqlid = PY_RUNRGE) then
2352 sqlstr := rrsel || allretasg || orderby;
2353 end if;
2354 elsif (action = PYG_AT_ADE) then
2355 if (sqlid = PY_ALLASG) then
2356 sqlstr := asactsel || alladeasg || range|| intind;
2357 elsif (sqlid = PY_SPCINC) then
2358 sqlstr := asactsel || adeincspc || range || intind;
2359 sqlstr := sqlstr || fupdate;
2360 elsif (sqlid = PY_SPCEXC) then
2361 sqlstr := asactsel || alladeasg || range || excspc || intind;
2362 sqlstr := sqlstr || fupdate;
2363 elsif (sqlid = PY_RUNRGE) then
2364 sqlstr := rrsel || alladeasg || orderby;
2365 else
2366 sqlstr := null; -- should not reach this!!
2367 end if;
2368 hr_utility.trace('sqlstr: ' ||sqlstr);
2369 elsif (action = PYG_AT_RCS) then
2370 if (sqlid = PY_ALLASG) then
2371 sqlstr := asactsel || allrcsasg || range;
2372 if (interlock = 'N') then
2373 if(timedepflg = 'Y') then
2374 sqlstr := sqlstr || intind; -- time independent leg.
2375 elsif (timedepflg = 'G') then
2376 sqlstr := sqlstr || intgrpdep; -- time dependent on group leg.
2377 else
2378 sqlstr := sqlstr || intdep; -- time dependent leg.
2379 end if;
2380 end if;
2381 sqlstr := sqlstr || fupdate;
2382 elsif (sqlid = PY_SPCINC) then
2383 sqlstr := asactsel || spcrcsinc || range;
2384 if (interlock = 'N') then
2385 if (timedepflg = 'Y') then
2386 sqlstr := sqlstr || intind; -- time independent leg.
2387 elsif (timedepflg = 'G') then
2388 sqlstr := sqlstr || intgrpdep; -- time dependent on group leg.
2389 else
2390 sqlstr := sqlstr || intdepaset; -- time dependent leg.
2391 end if;
2392 end if;
2393 sqlstr := sqlstr || fupdate;
2394 elsif (sqlid = PY_SPCEXC) then
2395 sqlstr := asactsel || allrcsasg || range || excspc;
2396 if(interlock = 'N') then
2397 if(timedepflg = 'Y') then
2398 sqlstr := sqlstr || intind; -- time independent leg.
2399 elsif (timedepflg = 'G') then
2400 sqlstr := sqlstr || intgrpdep; -- time dependent on group leg.
2401 else
2402 sqlstr := sqlstr || intdep; -- time dependent leg.
2403 end if;
2404 end if;
2405 sqlstr := sqlstr || fupdate;
2406 elsif (sqlid = PY_RESRGE) then
2407 sqlstr := ordrrsel || resact || orderby;
2408 elsif (sqlid = PY_NONRGE) then
2409 sqlstr := ordrrsel || nonact || orderby;
2410 else
2411 sqlstr := null; -- should not reach this!!
2412 end if;
2413 elsif (action = PYG_AT_ARC) then
2414 /* Must be getting a population range for the archiver */
2415 archive_range(pactid, sqlstr);
2416 elsif (action = PYG_AT_BEE) then
2417 if (sqlid = PY_ALLASG) then
2418 sqlstr := beeactsel || beeasg || range;
2419 elsif (sqlid = PY_RUNRGE) then
2420 sqlstr := brrsel || beeasg || borderby;
2421 else
2422 sqlstr := null; -- should not reach this!!
2423 end if;
2424 elsif (action = PYG_AT_PUR) then
2425 -- Set up strings for Purge.
2426 if (sqlid = PY_PURRGE) then
2427 sqlstr := prrsel || purallasg || orderby;
2428 elsif (sqlid = PY_ALLASG) then
2429 sqlstr := puractsel || purallasg || range || intpur;
2430 elsif (sqlid = PY_SPCINC) then
2431 sqlstr := puractsel || purspcinc || range || intpur;
2432 elsif (sqlid = PY_SPCEXC) then
2433 sqlstr := puractsel || purallasg || range || intpur || excspc;
2434 else
2435 sqlstr := null; -- should not reach this!!
2436 end if;
2437
2438 elsif (action = PYG_AT_BAL) then
2439 if (sqlid = PY_ALLASG) then
2440 sqlstr := runasactsel || allasg || range;
2441 if (interlock = 'N') then
2442 if(timedepflg = 'Y') then
2443 sqlstr := sqlstr || intbal; -- time independent leg.
2444 elsif (timedepflg = 'G') then
2445 sqlstr := sqlstr || intgrpdepbal; -- time dependent on group leg.
2446 else
2447 sqlstr := sqlstr || intbaldep; -- time dependent leg.
2448 end if;
2449 end if;
2450 sqlstr := sqlstr || actorderby || fupdate;
2451 elsif (sqlid = PY_SPCINC) then
2452 sqlstr := runasactsel || spcinc || range;
2453 if (interlock = 'N') then
2454 if(timedepflg = 'Y') then
2455 sqlstr := sqlstr || intbal; -- time independent leg.
2456 elsif (timedepflg = 'G') then
2457 sqlstr := sqlstr || intgrpdepbal; -- time dependent on group leg.
2458 else
2459 sqlstr := sqlstr || intbaldepaset; -- time dependent leg.
2460 end if;
2461 end if;
2462 sqlstr := sqlstr || actorderby || fupdate;
2463 elsif (sqlid = PY_SPCEXC) then
2464 sqlstr := runasactsel || allasg || range || excspc;
2465 if (interlock = 'N') then
2466 if(timedepflg = 'Y') then
2467 sqlstr := sqlstr || intbal; -- time independent leg.
2468 elsif (timedepflg = 'G') then
2469 sqlstr := sqlstr || intgrpdepbal; -- time dependent on group leg.
2470 else
2471 sqlstr := sqlstr || intbaldep; -- time dependent leg.
2472 end if;
2473 end if;
2474 sqlstr := sqlstr || actorderby || fupdate;
2475 elsif (sqlid = PY_RUNRGE) then
2476 sqlstr := rrsel || allasg || orderby;
2477 else
2478 sqlstr := null; -- should not reach this!!
2479 end if;
2480
2481 elsif (action = PYG_AT_REV) then
2482 if (sqlid = PY_ALLASG) then
2483 sqlstr := runasactsel_rev || revallasg || range || revaa;
2484 if (interlock = 'N') then
2485 if(timedepflg = 'Y') then
2486 sqlstr := sqlstr || intbal; -- time independent leg.
2487 elsif (timedepflg = 'G') then
2488 sqlstr := sqlstr || intgrpdepbal; -- time dependent on group leg.
2489 else
2490 sqlstr := sqlstr || intbaldep; -- time dependent leg.
2491 end if;
2492 end if;
2493 sqlstr := sqlstr || actorderby || fupdate;
2494 elsif (sqlid = PY_SPCINC) then
2495 sqlstr := runasactsel_rev || revspcinc || range || revaa;
2496 if (interlock = 'N') then
2497 if(timedepflg = 'Y') then
2498 sqlstr := sqlstr || intbal; -- time independent leg.
2499 elsif (timedepflg = 'G') then
2500 sqlstr := sqlstr || intgrpdepbal; -- time dependent on group leg.
2501 else
2502 sqlstr := sqlstr || intbaldepaset; -- time dependent leg.
2503 end if;
2504 end if;
2505 sqlstr := sqlstr || actorderby || fupdate;
2506 elsif (sqlid = PY_SPCEXC) then
2507 sqlstr := runasactsel_rev || revallasg || range || revaa || excspc;
2508 if (interlock = 'N') then
2509 if(timedepflg = 'Y') then
2510 sqlstr := sqlstr || intbal; -- time independent leg.
2511 elsif (timedepflg = 'G') then
2512 sqlstr := sqlstr || intgrpdepbal; -- time dependent on group leg.
2513 else
2514 sqlstr := sqlstr || intbaldep; -- time dependent leg.
2515 end if;
2516 end if;
2517 sqlstr := sqlstr || actorderby || fupdate;
2518 elsif (sqlid = PY_RUNRGE) then
2519 sqlstr := rrsel || revallasg || orderby;
2520 else
2521 sqlstr := null; -- should not reach this!!
2522 end if;
2523
2524 elsif (action = PYG_AT_RUN ) then
2525 if (sqlid = PY_ALLASG) then
2526 sqlstr := runasactsel || allasg || range;
2527 if (interlock = 'N') then
2528 if(timedepflg = 'Y') then
2529 sqlstr := sqlstr || intind; -- time independent leg.
2530 elsif (timedepflg = 'G') then
2531 sqlstr := sqlstr || intgrpdep; -- time dependent on group leg.
2532 else
2533 sqlstr := sqlstr || intdep; -- time dependent leg.
2534 end if;
2535 end if;
2536 sqlstr := sqlstr || actorderby || fupdate;
2537 elsif (sqlid = PY_SPCINC) then
2538 sqlstr := runasactsel || spcinc || range;
2539 if (interlock = 'N') then
2540 if (timedepflg = 'Y') then
2541 sqlstr := sqlstr || intind; -- time independent leg.
2542 elsif (timedepflg = 'G') then
2543 sqlstr := sqlstr || intgrpdep; -- time dependent on group leg.
2544 else
2545 sqlstr := sqlstr || intdepaset; -- time dependent leg.
2546 end if;
2547 end if;
2548 sqlstr := sqlstr || actorderby || fupdate;
2549 elsif (sqlid = PY_SPCEXC) then
2550 sqlstr := runasactsel || allasg || range || excspc;
2551 if(interlock = 'N') then
2552 if(timedepflg = 'Y') then
2553 sqlstr := sqlstr || intind; -- time independent leg.
2554 elsif (timedepflg = 'G') then
2555 sqlstr := sqlstr || intgrpdep; -- time dependent on group leg.
2556 else
2557 sqlstr := sqlstr || intdep; -- time dependent leg.
2558 end if;
2559 end if;
2560 sqlstr := sqlstr || actorderby || fupdate;
2561 elsif (sqlid = PY_RUNRGE) then
2562 --
2563 -- Determine if the payroll action is being run for an assignment
2564 -- set. If not formula-based assignment set use the following to
2565 -- limit the rows inserted into PAY_POPULATION_RANGES:
2566 -- use spcinc if Include set
2567 -- use excspc if Exclude set
2568 --
2569 BEGIN
2570 --
2571 -- Get Assignment Set ID from the Payroll Action being
2572 -- Processed - confirming its NOT formula-based.
2573 --
2574 select pac.assignment_set_id
2575 into l_asg_set_id
2576 from pay_payroll_actions pac,
2577 hr_assignment_sets has
2578 where pac.payroll_action_id = pactid
2579 and has.assignment_set_id = pac.assignment_set_id
2580 and has.formula_id is null;
2581 --
2582 -- Find out if an include or exclude assignment set
2583 --
2584 select include_or_exclude
2585 into l_inc_or_excl
2586 from hr_assignment_set_amendments
2587 where assignment_set_id = l_asg_set_id
2588 and rownum = 1;
2589 EXCEPTION
2590 When OTHERS Then
2591 --
2592 -- For any error, force it to default to original processing.
2593 --
2594 l_inc_or_excl := 'N';
2595 END;
2596
2597 --
2598 -- If it is an INCLUDE assignment set use the spcinc query,
2599 -- if it is an EXCLUDE then use allasg and excspc,
2600 -- otherwise just use allasg.
2601 --
2602 if (l_inc_or_excl = 'I') then
2603 sqlstr := rrsel || rspcinc || orderby;
2604 elsif (l_inc_or_excl = 'E') then
2605 sqlstr := rrsel || allasg || excspc || orderby;
2606 else
2607 sqlstr := rrsel || allasg || orderby;
2608 end if;
2609 elsif (sqlid = PY_RESRGE) then
2610 sqlstr := ordrrsel_nohint || resact || orderby; -- Modified here for Bug 14184691
2611 elsif (sqlid = PY_NONRGE) then
2612 sqlstr := ordrrsel_nohint || nonact || orderby; -- Modified here for Bug 14664610
2613 else
2614 sqlstr := null; -- should not reach this!!
2615 end if;
2616 elsif (action = PYG_AT_ECS) then
2617 if (sqlid = PY_RESRGE) then
2618 sqlstr := ordrrsel || ecsresact || orderby;
2619 elsif (sqlid = PY_NONRGE) then
2620 sqlstr := ordrrsel || ecsnonact || orderby;
2621 else
2622 sqlstr := null; -- should not reach this!!
2623 end if;
2624 elsif (action = PYG_AT_CHQ or
2625 action = PYG_AT_MAG or
2626 action = PYG_AT_PST or
2627 action = PYG_AT_CSH
2628 ) then
2629 pay_proc_environment_pkg.pactid := pactid;
2630 if (sqlid = PY_RESRGE) then
2631 sqlstr := ordrrsel_nohint || resact || --Modified here for Bug 13847750
2632 ' union all ' || orgsel || orgfrom ||
2633 orgorderby;
2634 elsif (sqlid = PY_NONRGE) then
2635 sqlstr := ordrrsel_1 || nonact ||
2636 ' union all ' || orgsel || orgfrom ||
2637 orgorderby;
2638 else
2639 sqlstr := null; -- should not reach this!!
2640 end if;
2641 elsif (action = PYG_AT_PRU) then
2642 --
2643 if (sqlid = PY_RESRGE) then
2644 sqlstr := ordrrsel || pruresact || orderby;
2645 elsif (sqlid = PY_NONRGE) then
2646 sqlstr := ordrrsel || prunonact || orderby;
2647 else
2648 sqlstr := null; -- should not reach this!!
2649 end if;
2650 --
2651 else
2652 if (sqlid = PY_ALLASG) then
2653 sqlstr := asactsel || allasg || range;
2654 if (interlock = 'N') then
2655 if(timedepflg = 'Y') then
2656 sqlstr := sqlstr || intind; -- time independent leg.
2657 elsif (timedepflg = 'G') then
2658 sqlstr := sqlstr || intgrpdep; -- time dependent on group leg.
2659 else
2660 sqlstr := sqlstr || intdep; -- time dependent leg.
2661 end if;
2662 end if;
2663 sqlstr := sqlstr || fupdate;
2664 elsif (sqlid = PY_SPCINC) then
2665 sqlstr := asactsel || spcinc || range;
2666 if (interlock = 'N') then
2667 if (timedepflg = 'Y') then
2668 sqlstr := sqlstr || intind; -- time independent leg.
2669 elsif (timedepflg = 'G') then
2670 sqlstr := sqlstr || intgrpdep; -- time dependent on group leg.
2671 else
2672 sqlstr := sqlstr || intdepaset; -- time dependent leg.
2673 end if;
2674 end if;
2675 sqlstr := sqlstr || fupdate;
2676 elsif (sqlid = PY_SPCEXC) then
2677 sqlstr := asactsel || allasg || range || excspc;
2678 if(interlock = 'N') then
2679 if(timedepflg = 'Y') then
2680 sqlstr := sqlstr || intind; -- time independent leg.
2681 elsif (timedepflg = 'G') then
2682 sqlstr := sqlstr || intgrpdep; -- time dependent on group leg.
2683 else
2684 sqlstr := sqlstr || intdep; -- time dependent leg.
2685 end if;
2686 end if;
2687 sqlstr := sqlstr || fupdate;
2688 elsif (sqlid = PY_RUNRGE) then
2689 sqlstr := rrsel || allasg || orderby;
2690 elsif (sqlid = PY_RESRGE) then
2691 sqlstr := ordrrsel || resact || orderby;
2692 elsif (sqlid = PY_NONRGE) then
2693 sqlstr := ordrrsel || nonact || orderby;
2694 else
2695 sqlstr := null; -- should not reach this!!
2696 end if;
2697 end if;
2698 len := length(sqlstr); -- return the length of the string.
2699 --
2700 pay_proc_logging.PY_EXIT('hr_dynsql.pyrsql');
2701 --
2702 end pyrsql;
2703 --
2704 ---------------------------- adv_override_check ----------------------------
2705 /*
2706 NAME
2707 adv_override_check
2708 DESCRIPTION
2709 Check whether the advance override input value exists
2710 for the element entry at the given start and end date.
2711 NOTES
2712 <none>
2713 */
2714 function adv_override_check
2715 (
2716 p_eeid number,
2717 p_start_date date,
2718 p_end_date date
2719 ) return varchar2 is
2720 --
2721 cursor csr_adv_override
2722 (
2723 l_eeid number,
2724 l_start_date date,
2725 l_end_date date
2726 ) is
2727 select 'Y'
2728 from dual
2729 where (NOT EXISTS
2730 (select null
2731 from pay_element_entry_values_f ev3,
2732 pay_input_values_f iv3
2733 where TRANSLATE(UPPER(iv3.name), ' ', '_') =
2734 (select TRANSLATE(UPPER(hrl1.meaning), ' ', '_')
2735 from hr_lookups hrl1
2736 WHERE hrl1.lookup_type = 'NAME_TRANSLATIONS'
2737 AND hrl1.lookup_code = 'ADV_OVERRIDE')
2738 and l_eeid = ev3.element_entry_id
2739 and ev3.input_value_id = iv3.input_value_id
2740 and ((ev3.effective_start_date between l_start_date and l_end_date )
2741 or (ev3.effective_start_date < l_start_date
2742 and ev3.effective_end_date > l_start_date ))
2743 and ((iv3.effective_start_date between l_start_date and l_end_date )
2744 or (iv3.effective_start_date < l_start_date
2745 and iv3.effective_end_date > l_start_date )))
2746 OR EXISTS
2747 (select null
2748 from pay_element_entry_values_f ev4,
2749 pay_input_values_f iv4
2750 where TRANSLATE(UPPER(iv4.name), ' ', '_') =
2751 (select TRANSLATE(UPPER(hrl2.meaning), ' ', '_')
2752 from hr_lookups hrl2
2753 WHERE hrl2.lookup_type = 'NAME_TRANSLATIONS'
2754 AND hrl2.lookup_code = 'ADV_OVERRIDE')
2755 and l_eeid = ev4.element_entry_id
2756 and ev4.input_value_id = iv4.input_value_id
2757 and ev4.screen_entry_value <> 'Y'
2758 and ((ev4.effective_start_date between l_start_date and l_end_date )
2759 or (ev4.effective_start_date < l_start_date
2760 and ev4.effective_end_date > l_start_date ))
2761 and ((iv4.effective_start_date between l_start_date and l_end_date )
2762 or (iv4.effective_start_date < l_start_date
2763 and iv4.effective_end_date >l_start_date ))));
2764 --
2765 l_check varchar2(1);
2766 --
2767 begin
2768 --
2769 --
2770 open csr_adv_override(p_eeid,p_start_date,p_end_date);
2771 fetch csr_adv_override into l_check;
2772 --
2773 if csr_adv_override%notfound then
2774 l_check := 'N';
2775 end if;
2776 --
2777 close csr_adv_override;
2778 --
2779 return (l_check);
2780 --
2781 end adv_override_check;
2782 --
2783 begin
2784 --
2785 -- Select for range row population.
2786 rrsel := 'select distinct pay_pos.person_id, null, null';
2787 ordrrsel := 'select /*+ ORDERED USE_NL(pay_asg) */ distinct pay_pos.person_id, null, null';
2788 ordrrsel_1 := 'select /*+ ORDERED */ distinct pay_pos.person_id, null, null'; /*added this for bug 13780337*/
2789 ordrrsel_nohint := 'select distinct pay_pos.person_id, null, null'; /*added this for bug 14184691*/
2790 prrsel := 'select distinct pay_pos.person_id, null, null'; -- For purge.
2791 brrsel := 'select distinct pay_asg.person_id, null, null'; -- For BEE.
2792 orgsel := 'select distinct null, hou.organization_id, ''HOU''';
2793 --
2794 -- select list for insertion into assignment actions table.
2795 -- Now needs the dummy value for secondary_status.
2796 retpgasactsel := '
2797 select
2798 pay_assignment_actions_s.nextval,
2799 null,
2800 pay_pac.payroll_action_id,
2801 ''U'',
2802 :chunk_number,
2803 pay_assignment_actions_s.nextval,
2804 1,
2805 null,
2806 ''U'',
2807 pay_pos.object_group_id';
2808 --
2809 retasactsel := '
2810 select /*+ INDEX(pay_pos PER_PERIODS_OF_SERVICE_PK)*/
2811 pay_assignment_actions_s.nextval,
2812 pay_asg.assignment_id,
2813 pay_pac.payroll_action_id,
2814 ''U'',
2815 :chunk_number,
2816 pay_assignment_actions_s.nextval,
2817 1,
2818 hr_dynsql.get_tax_unit(pay_asg.assignment_id,
2819 pay_pac.effective_date),
2820 ''U'',
2821 pay_asg.assignment_id';
2822 --
2823 asactsel := '
2824 select /*+ INDEX(pay_pos PER_PERIODS_OF_SERVICE_PK)
2825 INDEX(pay_asg PER_ASSIGNMENTS_F_N12) */
2826 pay_assignment_actions_s.nextval,
2827 pay_asg.assignment_id,
2828 pay_pac.payroll_action_id,
2829 ''U'',
2830 :chunk_number,
2831 pay_assignment_actions_s.nextval,
2832 1,
2833 hr_dynsql.get_tax_unit(pay_asg.assignment_id,
2834 pay_pac.effective_date),
2835 ''U'',
2836 null';
2837 --
2838 -- run select list for insertion into assignment actions table.
2839 -- NOTE: the assignment_action_id and action_sequence values have
2840 -- to be set later because we need to use order by here and that
2841 -- doesn't work with a sequence.
2842 runasactsel := '
2843 select /*+ INDEX(pay_pos PER_PERIODS_OF_SERVICE_PK)
2844 INDEX(pay_asg PER_ASSIGNMENTS_F_N12) */
2845 1,
2846 pay_asg.assignment_id,
2847 pay_pac.payroll_action_id,
2848 ''U'',
2849 :chunk_number,
2850 1,
2851 1,
2852 hr_dynsql.get_tax_unit(pay_asg.assignment_id,
2853 pay_pac.effective_date),
2854 ''U'',
2855 null';
2856 -- Bug 8725368 : Parent assignment action id is also picked
2857 -- to retrieve their run types later which will be used in
2858 -- populating the run type ids of child assignment actions
2859 -- after Batch Reversal.
2860 runasactsel_rev := '
2861 select /*+ INDEX(pay_pos PER_PERIODS_OF_SERVICE_PK)
2862 INDEX(pay_asg PER_ASSIGNMENTS_F_N12) */
2863 pay_paa2.assignment_action_id,
2864 pay_asg.assignment_id,
2865 pay_pac.payroll_action_id,
2866 ''U'',
2867 :chunk_number,
2868 1,
2869 1,
2870 hr_dynsql.get_tax_unit(pay_asg.assignment_id,
2871 pay_pac.effective_date),
2872 ''U'',
2873 null';
2874 --
2875 -- purge select list for insertion into assignment actions table.
2876 -- NOTE: the assignment_action_id and action_sequence values have
2877 -- to be set later because we need to use distinct here and that
2878 -- doesn't work with a sequence.
2879 -- The final value of 'U' is for secondary status.
2880 puractsel := '
2881 select /*+ INDEX(pay_pos PER_PERIODS_OF_SERVICE_PK)*/
2882 distinct 1,
2883 pay_asg.assignment_id,
2884 pay_pac.payroll_action_id,
2885 ''U'',
2886 :chunk_number,
2887 1,
2888 1,
2889 null,
2890 ''U'',
2891 null';
2892 --
2893 -- BEE sql query for all assignments.
2894 beeactsel := '
2895 select distinct 1,
2896 pay_btl.assignment_id,
2897 pay_pac.payroll_action_id,
2898 ''U'',
2899 :chunk_number,
2900 1,
2901 1,
2902 null,
2903 ''U'',
2904 null';
2905 --
2906 beeasg := '
2907 from pay_payroll_actions pay_pac,
2908 pay_batch_lines pay_btl,
2909 per_all_assignments_f pay_asg
2910 where pay_pac.payroll_action_id = :payroll_action_id
2911 and pay_pac.batch_id = pay_btl.batch_id
2912 and pay_btl.assignment_id = pay_asg.assignment_id
2913 and pay_btl.effective_date between pay_asg.effective_start_date
2914 and pay_asg.effective_end_date';
2915 --
2916 -- From and where clause for all assignments.
2917 -- Meant for insertion into assignment actions table.
2918 -- Note, assignments must be effective
2919 -- at both date paid and date earned.
2920 allasg := '
2921 from per_periods_of_service pay_pos,
2922 per_all_assignments_f pay_asg,
2923 per_all_assignments_f pay_as2,
2924 pay_payroll_actions pay_pac
2925 where pay_pac.payroll_action_id = :payroll_action_id
2926 and pay_asg.payroll_id = pay_pac.payroll_id
2927 and pay_pac.effective_date between
2928 pay_asg.effective_start_date and pay_asg.effective_end_date
2929 and pay_as2.assignment_id = pay_asg.assignment_id
2930 and pay_pac.date_earned between
2931 pay_as2.effective_start_date and pay_as2.effective_end_date
2932 and pay_pos.period_of_service_id = pay_asg.period_of_service_id
2933 and pay_as2.period_of_service_id = pay_asg.period_of_service_id';
2934 --
2935 -- Reversal range row select
2936 revallasg := '
2937 from per_periods_of_service pay_pos,
2938 per_all_assignments_f pay_asg,
2939 per_all_assignments_f pay_as2,
2940 pay_payroll_actions pay_pac,
2941 pay_assignment_actions pay_paa2,
2942 pay_payroll_actions pay_pac2
2943 where pay_pac.payroll_action_id = :payroll_action_id
2944 and pay_asg.payroll_id = pay_pac.payroll_id
2945 and pay_pac.effective_date between
2946 pay_asg.effective_start_date and pay_asg.effective_end_date
2947 and pay_as2.assignment_id = pay_asg.assignment_id
2948 and pay_pac.date_earned between
2949 pay_as2.effective_start_date and pay_as2.effective_end_date
2950 and pay_pos.period_of_service_id = pay_asg.period_of_service_id
2951 and pay_as2.period_of_service_id = pay_asg.period_of_service_id
2952 and pay_paa2.assignment_id = pay_asg.assignment_id
2953 and pay_pac2.payroll_action_id = pay_paa2.payroll_action_id
2954 and pay_pac2.payroll_action_id = pay_pac.target_payroll_action_id
2955 and pay_pac2.action_type in (''R'', ''Q'')
2956 and pay_pac2.effective_date <= pay_pac.effective_date';
2957 --
2958 -- Purge range row select.
2959 purallasg := '
2960 from per_periods_of_service pay_pos,
2961 per_all_assignments_f pay_asg,
2962 pay_payroll_actions pay_pac
2963 where pay_pac.payroll_action_id = :pactid
2964 and pay_asg.business_group_id + 0 = pay_pac.business_group_id
2965 and pay_asg.payroll_id is not null
2966 and pay_asg.effective_start_date <= pay_pac.effective_date
2967 and pay_pos.period_of_service_id = pay_asg.period_of_service_id';
2968 --
2969 -- Retropay assignments
2970 allretasg := '
2971 from per_periods_of_service pay_pos,
2972 per_all_assignments_f pay_asg,
2973 pay_payroll_actions pay_pac
2974 where pay_pac.payroll_action_id = :payroll_action_id
2975 and pay_asg.payroll_id = pay_pac.payroll_id
2976 and pay_pac.effective_date between
2977 pay_asg.effective_start_date and pay_asg.effective_end_date
2978 and pay_pos.period_of_service_id = pay_asg.period_of_service_id';
2979 --
2980 -- RetroCost assignments
2981 allrcsasg := '
2982 from per_periods_of_service pay_pos,
2983 per_all_assignments_f pay_asg,
2984 pay_payroll_actions pay_pac
2985 where pay_pac.payroll_action_id = :pactid
2986 and (pay_asg.payroll_id = pay_pac.payroll_id or pay_pac.payroll_id is null)
2987 and pay_pac.effective_date between
2988 pay_asg.effective_start_date and pay_asg.effective_end_date
2989 and pay_pos.period_of_service_id = pay_asg.period_of_service_id
2990 and exists (select null
2991 from pay_action_classifications pay_pcl,
2992 pay_assignment_actions pay_act,
2993 per_all_assignments_f pay_asg2,
2994 pay_payroll_actions pay_pac2
2995 where pay_pac2.consolidation_set_id +0 = pay_pac.consolidation_set_id
2996 and pay_pac2.effective_date between
2997 pay_pac.start_date and pay_pac.effective_date
2998 and pay_act.payroll_action_id = pay_pac2.payroll_action_id
2999 and pay_act.action_status = ''C''
3000 and pay_pcl.classification_name = ''COSTED''
3001 and pay_pac2.action_type = pay_pcl.action_type
3002 and pay_asg.assignment_id = pay_act.assignment_id
3003 and pay_asg2.assignment_id = pay_act.assignment_id
3004 and pay_pac2.effective_date between
3005 pay_asg2.effective_start_date and pay_asg2.effective_end_date
3006 and pay_asg2.payroll_id + 0 = pay_asg.payroll_id + 0
3007 and not exists (
3008 select null
3009 from pay_assignment_actions pay_ac2
3010 where pay_ac2.assignment_id = pay_asg.assignment_id
3011 and pay_pac.payroll_action_id = pay_ac2.payroll_action_id))';
3012 --
3013 -- Advancepay assignments: criteria : An existance of
3014 -- Pay Advance element in current earnings period.
3015 -- WARNING : this statment gets us very close to the 4000 character
3016 -- limit of overall statement returned ... SO CARE MUST BE TAKEN
3017 -- IN ANY CHANGES BEING MADE.
3018 alladvasg := '
3019 from per_periods_of_service pay_pos,
3020 per_all_assignments_f pay_asg,
3021 pay_payroll_actions pay_pac
3022 where pay_pac.payroll_action_id = :payroll_action_id
3023 and pay_asg.payroll_id + 0 = pay_pac.payroll_id
3024 and pay_pac.effective_date between
3025 pay_asg.effective_start_date and pay_asg.effective_end_date
3026 and pay_pos.period_of_service_id = pay_asg.period_of_service_id
3027 and exists (select null from
3028 pay_element_entries_f p_pee,
3029 pay_element_entry_values_f p_pev,
3030 pay_element_entry_values_f p_pev2,
3031 pay_input_values_f p_piv,
3032 pay_input_values_f p_piv2
3033 where p_pee.assignment_id = pay_asg.assignment_id
3034 and pay_pac.effective_date between p_pee.effective_start_date
3035 and p_pee.effective_end_date
3036 and p_pee.element_type_id =
3037 (select to_number(p_plr.rule_mode)
3038 from pay_legislation_rules p_plr,
3039 per_business_groups_perf p_pbg
3040 where p_pbg.business_group_id = pay_pac.business_group_id
3041 and p_pbg.legislation_code = p_plr.legislation_code
3042 and TRANSLATE(upper(p_plr.rule_type),''-'',''_'' )=
3043 ''PAY_ADVANCE_INDICATOR'')
3044 and p_pee.element_entry_id = p_pev.element_entry_id
3045 and p_pee.element_entry_id = p_pev2.element_entry_id
3046 and p_pev.input_value_id = p_piv.input_value_id
3047 and p_piv2.input_value_id = p_pev2.input_value_id
3048 and p_piv.input_value_id = (select to_number(p_plr.rule_mode)
3049 from pay_legislation_rules p_plr, per_business_groups_perf p_pbg
3050 where p_pbg.business_group_id = pay_pac.business_group_id
3051 and p_pbg.legislation_code = p_plr.legislation_code
3052 and TRANSLATE(upper(p_plr.rule_type),''-'',''_'') = ''PAI_START_DATE'')
3053 and p_piv2.input_value_id = (select to_number(p_plr.rule_mode)
3054 from pay_legislation_rules p_plr, per_business_groups_perf p_pbg
3055 where p_pbg.business_group_id = pay_pac.business_group_id
3056 and p_pbg.legislation_code = p_plr.legislation_code
3057 and TRANSLATE(upper(p_plr.rule_type), ''-'',''_'') = ''PAI_END_DATE'')
3058 and not exists (select null
3059 from pay_element_entries_f p_pe2
3060 where p_pe2.assignment_id = pay_asg.assignment_id
3061 and p_pe2.element_type_id =
3062 (select to_number(p_plr2.rule_mode)
3063 from pay_legislation_rules p_plr2, per_business_groups_perf p_pbg2
3064 where p_pbg2.business_group_id = pay_pac.business_group_id
3065 and p_pbg2.legislation_code = p_plr2.legislation_code
3066 and TRANSLATE(upper(p_plr2.rule_type), ''-'', ''_'') = ''ADV_DEDUCTION'')
3067 and p_pe2.effective_start_date between
3068 fnd_date.canonical_to_date(p_pev.screen_entry_value)
3069 and fnd_date.canonical_to_date(p_pev2.screen_entry_value)))';
3070 --
3071 -- Advance Pay by Element
3072 --
3073 -- WARNING : this statment gets us very close to the 4000 character
3074 -- limit of overall statement returned ... SO CARE MUST BE TAKEN
3075 -- IN ANY CHANGES BEING MADE.
3076 alladeasg := '
3077 from per_periods_of_service pay_pos,
3078 per_all_assignments_f pay_asg,
3079 pay_payroll_actions pay_pac
3080 where pay_pac.payroll_action_id = :pactid
3081 and pay_asg.payroll_id = pay_pac.payroll_id
3082 and pay_pac.effective_date between
3083 pay_asg.effective_start_date and pay_asg.effective_end_date
3084 and pay_pos.period_of_service_id = pay_asg.period_of_service_id
3085 and exists
3086 (select null
3087 from pay_element_entries_f pay_pee,
3088 pay_element_types_f pay_pet,
3089 pay_element_entry_values_f pay_pev,
3090 pay_element_entry_values_f pay_pev2,
3091 pay_input_values_f pay_piv,
3092 pay_input_values_f pay_piv2
3093 where pay_pee.assignment_id = pay_asg.assignment_id
3094 and pay_pee.element_type_id = pay_pet.element_type_id
3095 and pay_pet.advance_indicator = ''Y''
3096 and pay_pee.element_entry_id = pay_pev.element_entry_id
3097 and pay_pee.element_entry_id = pay_pev2.element_entry_id
3098 and pay_pev.input_value_id = pay_piv.input_value_id
3099 and pay_piv2.input_value_id = pay_pev2.input_value_id
3100 and hr_dynsql.adv_override_check(pay_pee.element_entry_id,pay_pac.effective_date,pay_pac.end_date) = ''Y''
3101 and TRANSLATE(UPPER(pay_piv.name), '' '', ''_'') =
3102 (select TRANSLATE(UPPER(pay_hrl3.meaning), '' '', ''_'')
3103 from hr_lookups pay_hrl3
3104 WHERE pay_hrl3.lookup_type = ''NAME_TRANSLATIONS''
3105 AND pay_hrl3.lookup_code = ''START_DATE'')
3106 and TRANSLATE(UPPER(pay_piv2.name), '' '', ''_'') =
3107 (select TRANSLATE(UPPER(pay_hrl4.meaning), '' '', ''_'')
3108 from hr_lookups pay_hrl4
3109 WHERE pay_hrl4.lookup_type = ''NAME_TRANSLATIONS''
3110 AND pay_hrl4.lookup_code = ''END_DATE'')
3111 and (pay_pev.screen_entry_value between
3112 fnd_date.date_to_canonical(pay_pac.effective_date) and
3113 fnd_date.date_to_canonical(pay_pac.end_date)
3114 OR (pay_pev.screen_entry_value < fnd_date.date_to_canonical(pay_pac.effective_date) and
3115 pay_pev2.screen_entry_value > fnd_date.date_to_canonical(pay_pac.effective_date)))
3116 )' ;
3117 --
3118 -- Advance Pay specific inclusions
3119 -- WARNING : this statment gets us very close to the 4000 character
3120 -- limit of overall statement returned ... SO CARE MUST BE TAKEN
3121 -- IN ANY CHANGES BEING MADE.
3122 adeincspc := '
3123 from per_periods_of_service pay_pos,
3124 per_all_assignments_f pay_asg,
3125 pay_payroll_actions pay_pac,
3126 hr_assignment_set_amendments pay_inc
3127 where pay_pac.payroll_action_id = :pactid
3128 and pay_asg.payroll_id = pay_pac.payroll_id
3129 and pay_inc.assignment_set_id = pay_pac.assignment_set_id + decode(pay_pos.period_of_service_id, null, 0, 0)
3130 and pay_inc.assignment_id = pay_asg.assignment_id + decode(pay_pos.period_of_service_id, null, 0, 0)
3131 and pay_pac.effective_date between
3132 pay_asg.effective_start_date and pay_asg.effective_end_date
3133 and pay_inc.include_or_exclude = ''I''
3134 and pay_pos.period_of_service_id = pay_asg.period_of_service_id
3135 and exists
3136 (select null
3137 from pay_element_entries_f pay_pee,
3138 pay_element_types_f pay_pet,
3139 pay_element_entry_values_f pay_pev,
3140 pay_element_entry_values_f pay_pev2,
3141 pay_input_values_f pay_piv,
3142 pay_input_values_f pay_piv2
3143 where pay_pee.assignment_id = pay_asg.assignment_id
3144 and pay_pee.element_type_id = pay_pet.element_type_id
3145 and pay_pet.advance_indicator = ''Y''
3146 and pay_pee.element_entry_id = pay_pev.element_entry_id
3147 and pay_pee.element_entry_id = pay_pev2.element_entry_id
3148 and pay_pev.input_value_id = pay_piv.input_value_id
3149 and pay_piv2.input_value_id = pay_pev2.input_value_id
3150 and hr_dynsql.adv_override_check(pay_pee.element_entry_id,pay_pac.effective_date,pay_pac.end_date) = ''Y''
3151 and TRANSLATE(UPPER(pay_piv.name), '' '', ''_'') =
3152 (select TRANSLATE(UPPER(pay_hrl3.meaning), '' '', ''_'')
3153 from hr_lookups pay_hrl3
3154 WHERE pay_hrl3.lookup_type = ''NAME_TRANSLATIONS''
3155 AND pay_hrl3.lookup_code = ''START_DATE'')
3156 and TRANSLATE(UPPER(pay_piv2.name), '' '', ''_'') =
3157 (select TRANSLATE(UPPER(pay_hrl4.meaning), '' '', ''_'')
3158 from hr_lookups pay_hrl4
3159 WHERE pay_hrl4.lookup_type = ''NAME_TRANSLATIONS''
3160 AND pay_hrl4.lookup_code = ''END_DATE'')
3161 and (pay_pev.screen_entry_value between
3162 fnd_date.date_to_canonical(pay_pac.effective_date) and
3163 fnd_date.date_to_canonical(pay_pac.end_date)
3164 OR (pay_pev.screen_entry_value < fnd_date.date_to_canonical(pay_pac.effective_date) and
3165 pay_pev2.screen_entry_value > fnd_date.date_to_canonical(pay_pac.effective_date)))
3166 )' ;
3167 --
3168 -- Specific inclusions in range creation assignment action phase
3169 rspcinc := '
3170 from per_periods_of_service pay_pos,
3171 per_all_assignments_f pay_asg,
3172 per_all_assignments_f pay_as2,
3173 hr_assignment_set_amendments pay_inc,
3174 pay_payroll_actions pay_pac
3175 where pay_pac.payroll_action_id = :payroll_action_id
3176 and pay_pac.assignment_set_id = pay_inc.assignment_set_id
3177 and pay_asg.assignment_id = pay_inc.assignment_id
3178 and pay_asg.payroll_id = pay_pac.payroll_id
3179 and pay_pac.effective_date between
3180 pay_asg.effective_start_date and pay_asg.effective_end_date
3181 and pay_as2.assignment_id = pay_asg.assignment_id
3182 and pay_pac.date_earned between
3183 pay_as2.effective_start_date and pay_as2.effective_end_date
3184 and pay_inc.include_or_exclude = ''I''
3185 and pay_pos.period_of_service_id = pay_asg.period_of_service_id
3186 and pay_as2.period_of_service_id = pay_asg.period_of_service_id';
3187 --
3188 -- Specific inclusions in assignment action phase
3189 -- Bug 14740436: Removed Decode decode(pay_pos.period_of_service_id, null, 0, 0)
3190 -- function which is insignificant and causing performance issue.
3191 spcinc := '
3192 from per_periods_of_service pay_pos,
3193 per_all_assignments_f pay_asg,
3194 per_all_assignments_f pay_as2,
3195 hr_assignment_set_amendments pay_inc,
3196 pay_payroll_actions pay_pac
3197 where pay_pac.payroll_action_id = :payroll_action_id
3198 and pay_inc.assignment_set_id = pay_pac.assignment_set_id
3199 and pay_inc.assignment_id = pay_asg.assignment_id
3200 and pay_asg.payroll_id = pay_pac.payroll_id
3201 and pay_pac.effective_date between
3202 pay_asg.effective_start_date and pay_asg.effective_end_date
3203 and pay_as2.assignment_id = pay_asg.assignment_id
3204 and pay_pac.date_earned between
3205 pay_as2.effective_start_date and pay_as2.effective_end_date
3206 and pay_inc.include_or_exclude = ''I''
3207 and pay_pos.period_of_service_id = pay_asg.period_of_service_id
3208 and pay_as2.period_of_service_id = pay_asg.period_of_service_id';
3209 --
3210 revspcinc := '
3211 from per_periods_of_service pay_pos,
3212 per_all_assignments_f pay_asg,
3213 per_all_assignments_f pay_as2,
3214 hr_assignment_set_amendments pay_inc,
3215 pay_payroll_actions pay_pac,
3216 pay_assignment_actions pay_paa2,
3217 pay_payroll_actions pay_pac2
3218 where pay_pac.payroll_action_id = :payroll_action_id
3219 and pay_inc.assignment_set_id = pay_pac.assignment_set_id + decode(pay_pos.period_of_service_id, null, 0, 0)
3220 and pay_inc.assignment_id = pay_asg.assignment_id + decode(pay_pos.period_of_service_id, null, 0, 0)
3221 and pay_asg.payroll_id = pay_pac.payroll_id
3222 and pay_pac.effective_date between
3223 pay_asg.effective_start_date and pay_asg.effective_end_date
3224 and pay_as2.assignment_id = pay_asg.assignment_id
3225 and pay_pac.date_earned between
3226 pay_as2.effective_start_date and pay_as2.effective_end_date
3227 and pay_inc.include_or_exclude = ''I''
3228 and pay_pos.period_of_service_id = pay_asg.period_of_service_id
3229 and pay_as2.period_of_service_id = pay_asg.period_of_service_id
3230 and pay_paa2.assignment_id = pay_asg.assignment_id
3231 and pay_pac2.payroll_action_id = pay_paa2.payroll_action_id
3232 and pay_pac2.payroll_action_id = pay_pac.target_payroll_action_id
3233 and pay_pac2.action_type in (''R'', ''Q'')
3234 and pay_pac2.effective_date <= pay_pac.effective_date';
3235 --
3236 -- Purge Specific inclusions.
3237 purspcinc := '
3238 from per_periods_of_service pay_pos,
3239 per_all_assignments_f pay_asg,
3240 hr_assignment_set_amendments pay_inc,
3241 pay_payroll_actions pay_pac
3242 where pay_pac.payroll_action_id = :payroll_action_id
3243 and pay_inc.assignment_set_id = pay_pac.assignment_set_id
3244 and pay_inc.assignment_id = pay_asg.assignment_id
3245 and pay_asg.payroll_id is not null
3246 and pay_asg.effective_start_date <= pay_pac.effective_date
3247 and pay_inc.include_or_exclude = ''I''
3248 and pay_pos.period_of_service_id = pay_asg.period_of_service_id + decode(pay_inc.assignment_id, null, 0, 0)';
3249 --
3250 -- Retropay inclusions
3251 spcretinc := '
3252 from per_periods_of_service pay_pos,
3253 per_all_assignments_f pay_asg,
3254 hr_assignment_set_amendments pay_inc,
3255 pay_payroll_actions pay_pac
3256 where pay_pac.payroll_action_id = :payroll_action_id
3257 and pay_inc.assignment_set_id = pay_pac.assignment_set_id + decode(pay_pos.period_of_service_id, null, 0, 0)
3258 and pay_inc.assignment_id = pay_asg.assignment_id + decode(pay_pos.period_of_service_id, null, 0, 0)
3259 and pay_asg.payroll_id + 0 = pay_pac.payroll_id
3260 and pay_pac.effective_date between
3261 pay_asg.effective_start_date and pay_asg.effective_end_date
3262 and pay_inc.include_or_exclude = ''I''
3263 and pay_pos.period_of_service_id = pay_asg.period_of_service_id';
3264 --
3265 -- RetroCosting inclusions
3266 spcrcsinc := '
3267 from per_periods_of_service pay_pos,
3268 hr_assignment_set_amendments pay_inc,
3269 per_all_assignments_f pay_asg,
3270 per_all_assignments_f pay_as2,
3271 pay_payroll_actions pay_pac
3272 where pay_pac.payroll_action_id = :pactid
3273 and (pay_asg.payroll_id = pay_pac.payroll_id or pay_pac.payroll_id is null)
3274 and pay_pac.effective_date between
3275 pay_asg.effective_start_date and pay_asg.effective_end_date
3276 and pay_inc.assignment_set_id = pay_pac.assignment_set_id + decode(pay_pos.period_of_service_id, null, 0, 0)
3277 and pay_inc.assignment_id = pay_asg.assignment_id + decode(pay_pos.period_of_service_id, null, 0, 0)
3278 and pay_inc.include_or_exclude = ''I''
3279 and pay_pos.period_of_service_id = pay_asg.period_of_service_id
3280 and pay_as2.rowid = pay_asg.rowid
3281 and exists (select null
3282 from pay_action_classifications pay_pcl,
3283 pay_assignment_actions pay_act,
3284 per_all_assignments_f pay_asg2,
3285 pay_payroll_actions pay_pac2
3286 where pay_pac2.consolidation_set_id +0 = pay_pac.consolidation_set_id
3287 and pay_pac2.effective_date between
3288 pay_pac.start_date and pay_pac.effective_date
3289 and pay_act.payroll_action_id = pay_pac2.payroll_action_id
3290 and pay_act.action_status = ''C''
3291 and pay_pcl.classification_name = ''COSTED''
3292 and pay_pac2.action_type = pay_pcl.action_type
3293 and pay_asg.assignment_id = pay_act.assignment_id
3294 and pay_asg2.assignment_id = pay_act.assignment_id
3295 and pay_pac2.effective_date between
3296 pay_asg2.effective_start_date and pay_asg2.effective_end_date
3297 and pay_asg2.payroll_id + 0 = pay_asg.payroll_id + 0
3298 and not exists (
3299 select null
3300 from pay_assignment_actions pay_ac2
3301 where pay_ac2.assignment_id = pay_asg.assignment_id
3302 and pay_pac.payroll_action_id = pay_ac2.payroll_action_id))';
3303 --
3304 -- Restrict by particular range of person_id.
3305 nopoprange := '
3306 and pay_asg.person_id between
3307 :start_person_id and :end_person_id';
3308 -- Use of person_id in range table
3309 poprange := '
3310 and pay_asg.person_id in (
3311 select pay_pop.person_id
3312 from pay_population_ranges pay_pop
3313 where pay_pop.payroll_action_id = pay_pac.payroll_action_id
3314 and pay_pop.chunk_number = :chunk)';
3315 --
3316 -- Ranges For Groups
3317 -- (use pay_pos instead of pay_asg because don't have pay_asg)
3318 -- Restrict by particular range of person_id.
3319 grpnopoprange := '
3320 and pay_pos.source_id between
3321 :start_person_id and :end_person_id';
3322 -- Use of person_id in range table
3323 grppoprange := '
3324 and pay_pos.source_id in (
3325 select pay_pop.person_id
3326 from pay_population_ranges pay_pop
3327 where pay_pop.payroll_action_id = pay_pac.payroll_action_id
3328 and pay_pop.chunk_number = :chunk)';
3329 --
3330 -- Estimate Costing Restricted payroll action range row
3331 -- where clause. i.e. restricted by payroll_id.
3332 -- nb have to join to per_time_periods at pa.start_date
3333 ecsresact := '
3334 from pay_payroll_actions pay_pa1,
3335 per_time_periods pay_ptp,
3336 pay_payroll_actions pay_pa2,
3337 pay_assignment_actions pay_act,
3338 per_all_assignments_f pay_asg,
3339 per_periods_of_service pay_pos
3340 where pay_pa1.payroll_action_id = :payroll_action_id
3341 and pay_ptp.payroll_id = pay_pa1.payroll_id
3342 and pay_pa1.start_date between
3343 pay_ptp.start_date and pay_ptp.end_date
3344 and pay_pa2.consolidation_set_id = pay_pa1.consolidation_set_id
3345 and pay_pa2.payroll_id = pay_pa1.payroll_id
3346 and pay_pa2.effective_date between
3347 pay_ptp.start_date and pay_ptp.end_date
3348 and pay_act.payroll_action_id = pay_pa2.payroll_action_id
3349 and pay_asg.assignment_id = pay_act.assignment_id
3350 and pay_pa1.effective_date between
3351 pay_asg.effective_start_date and pay_asg.effective_end_date
3352 and pay_pos.period_of_service_id = pay_asg.period_of_service_id';
3353 --
3354 -- Estimate Costing Unrestricted payroll action range row
3355 -- where clause. i.e. not restricted by payroll_id.
3356 -- nb have to join to per_time_periods at pa.start_date
3357 ecsnonact := '
3358 from pay_payroll_actions pay_pa1,
3359 pay_all_payrolls_f pay_pay,
3360 per_time_periods pay_ptp,
3361 pay_payroll_actions pay_pa2,
3362 pay_assignment_actions pay_act,
3363 per_all_assignments_f pay_asg,
3364 per_periods_of_service pay_pos
3365 where pay_pa1.payroll_action_id = :payroll_action_id
3366 and pay_pay.consolidation_set_id = pay_pa1.consolidation_set_id
3367 and pay_pa1.effective_date between
3368 pay_pay.effective_start_date and pay_pay.effective_end_date
3369 and pay_ptp.payroll_id = pay_pay.payroll_id
3370 and pay_pa1.start_date between
3371 pay_ptp.start_date and pay_ptp.end_date
3372 and pay_pa2.consolidation_set_id = pay_pa1.consolidation_set_id
3373 and pay_pa2.effective_date between
3374 pay_ptp.start_date and pay_ptp.end_date
3375 and pay_act.payroll_action_id = pay_pa2.payroll_action_id
3376 and pay_asg.assignment_id = pay_act.assignment_id
3377 and pay_pa1.effective_date between
3378 pay_asg.effective_start_date and pay_asg.effective_end_date
3379 and pay_pos.period_of_service_id = pay_asg.period_of_service_id';
3380 --
3381 -- Restricted payroll action range row where clause.
3382 -- i.e. restricted by payroll_id.
3383 resact := '
3384 from pay_payroll_actions pay_pa1,
3385 pay_payroll_actions pay_pa2,
3386 pay_assignment_actions pay_act,
3387 per_all_assignments_f pay_asg,
3388 per_periods_of_service pay_pos
3389 where pay_pa1.payroll_action_id = :payroll_action_id
3390 and pay_pa2.consolidation_set_id = pay_pa1.consolidation_set_id
3391 and pay_pa2.payroll_id = pay_pa1.payroll_id
3392 and pay_pa2.effective_date between
3393 pay_pa1.start_date and pay_pa1.effective_date
3394 and pay_act.payroll_action_id = pay_pa2.payroll_action_id
3395 and pay_asg.assignment_id = pay_act.assignment_id
3396 and pay_pa1.effective_date between
3397 pay_asg.effective_start_date and pay_asg.effective_end_date
3398 and pay_pos.period_of_service_id = pay_asg.period_of_service_id';
3399 --
3400 -- Unrestricted payroll action range row where clause.
3401 -- i.e. not restricted by payroll_id.
3402 nonact := '
3403 from pay_payroll_actions pay_pa1,
3404 pay_payroll_actions pay_pa2,
3405 pay_assignment_actions pay_act,
3406 per_all_assignments_f pay_asg,
3407 per_periods_of_service pay_pos
3408 where pay_pa1.payroll_action_id = :payroll_action_id
3409 and pay_pa2.consolidation_set_id = pay_pa1.consolidation_set_id
3410 and pay_pa2.effective_date between
3411 pay_pa1.start_date and pay_pa1.effective_date
3412 and pay_act.payroll_action_id = pay_pa2.payroll_action_id
3413 and pay_asg.assignment_id = pay_act.assignment_id
3414 and pay_pa1.effective_date between
3415 pay_asg.effective_start_date and pay_asg.effective_end_date
3416 and pay_pos.period_of_service_id = pay_asg.period_of_service_id';
3417 --
3418 -- Restricted payroll action range row where clause.
3419 -- i.e. restricted by payroll_id.
3420 pruresact := '
3421 from pay_payroll_actions pay_pa1,
3422 pay_payroll_actions pay_pa2,
3423 pay_assignment_actions pay_act,
3424 per_all_assignments_f pay_asg,
3425 per_periods_of_service pay_pos
3426 where pay_pa1.payroll_action_id = :payroll_action_id
3427 and pay_pa2.consolidation_set_id = pay_pa1.consolidation_set_id
3428 and pay_pa2.payroll_id = pay_pa1.payroll_id
3429 and pay_pa2.effective_date between
3430 pay_pa1.start_date and pay_pa1.effective_date
3431 and pay_act.payroll_action_id = pay_pa2.payroll_action_id
3432 and pay_asg.assignment_id = pay_act.assignment_id
3433 and pay_pa2.effective_date between
3434 pay_asg.effective_start_date and pay_asg.effective_end_date
3435 and pay_pos.period_of_service_id = pay_asg.period_of_service_id';
3436 --
3437 -- Unrestricted payroll action range row where clause.
3438 -- i.e. not restricted by payroll_id.
3439 prunonact := '
3440 from pay_payroll_actions pay_pa1,
3441 pay_payroll_actions pay_pa2,
3442 pay_assignment_actions pay_act,
3443 per_all_assignments_f pay_asg,
3444 per_periods_of_service pay_pos
3445 where pay_pa1.payroll_action_id = :payroll_action_id
3446 and pay_pa2.consolidation_set_id = pay_pa1.consolidation_set_id
3447 and pay_pa2.effective_date between
3448 pay_pa1.start_date and pay_pa1.effective_date
3449 and pay_act.payroll_action_id = pay_pa2.payroll_action_id
3450 and pay_asg.assignment_id = pay_act.assignment_id
3451 and pay_pa2.effective_date between
3452 pay_asg.effective_start_date and pay_asg.effective_end_date
3453 and pay_pos.period_of_service_id = pay_asg.period_of_service_id';
3454 --
3455 -- not exists to exclude specific assignments.
3456 excspc := '
3457 and not exists (
3458 select null
3459 from hr_assignment_set_amendments pay_exc
3460 where pay_exc.assignment_set_id = pay_pac.assignment_set_id
3461 and pay_exc.assignment_id = pay_asg.assignment_id
3462 and pay_exc.include_or_exclude = ''E'')';
3463 --
3464 -- and not exists clause to exclude people failing interlock rules.
3465 -- this one is for time independent legislation.
3466 intind := '
3467 and not exists (
3468 select /*+ INDEX (pay_pa2 pay_payroll_actions_pk) */ null
3469 from pay_action_classifications pay_acl,
3470 pay_payroll_actions pay_pa2,
3471 pay_assignment_actions pay_ac2
3472 where pay_ac2.assignment_id = pay_asg.assignment_id
3473 and pay_pa2.payroll_action_id = pay_ac2.payroll_action_id
3474 and pay_acl.classification_name = ''SEQUENCED''
3475 and pay_pa2.action_type = pay_acl.action_type
3476 and (pay_pa2.effective_date > pay_pac.effective_date
3477 or (pay_ac2.action_status not in (''C'', ''S'')
3478 and pay_pa2.effective_date <= pay_pac.effective_date)))';
3479 --
3480 intbal := '
3481 and not exists (
3482 select /*+ INDEX (pay_pa2 pay_payroll_actions_pk) */ null
3483 from pay_action_classifications pay_acl,
3484 pay_payroll_actions pay_pa2,
3485 pay_assignment_actions pay_ac2
3486 where pay_ac2.assignment_id = pay_asg.assignment_id
3487 and pay_pa2.payroll_action_id = pay_ac2.payroll_action_id
3488 and pay_acl.classification_name = ''SEQUENCED''
3489 and pay_pa2.action_type = pay_acl.action_type
3490 and pay_ac2.action_status not in (''C'', ''S''))';
3491
3492 -- and not exists clause to exclude people failing interlock rules.
3493 -- this one is for time dependent legislation.
3494 intdep := '
3495 and hr_dynsql.person_sequence_locked(pay_pos.period_of_service_id
3496 + decode(pay_pos.person_id, null, 0, 0),
3497 pay_pac.effective_date) = ''N''';
3498 -- Bug 14740436 : Removed Decode function
3499 intdepaset := '
3500 and hr_dynsql.person_sequence_locked(pay_pos.period_of_service_id,
3501 pay_pac.effective_date) = ''N''';
3502 --
3503 intbaldep := '
3504 and hr_dynsql.bal_person_sequence_locked(pay_pos.period_of_service_id
3505 + decode(pay_pos.person_id, null, 0, 0),
3506 pay_pac.effective_date) = ''N''';
3507 --
3508 intbaldepaset := '
3509 and hr_dynsql.bal_person_sequence_locked(pay_pos.period_of_service_id
3510 + decode(pay_inc.include_or_exclude, null, 0, 0),
3511 pay_pac.effective_date) = ''N''';
3512 --
3513 intretdep := '
3514 and hr_dynsql.ret_person_sequence_locked(pay_pos.period_of_service_id
3515 + decode(pay_pos.person_id, null, 0, 0),
3516 pay_pac.effective_date) = ''N''';
3517 --
3518 intretdepaset := '
3519 and hr_dynsql.ret_person_sequence_locked(pay_pos.period_of_service_id
3520 + decode(pay_inc.include_or_exclude, null, 0, 0),
3521 pay_pac.effective_date) = ''N''';
3522 --
3523 intgrpdep := '
3524 and hr_dynsql.process_group_seq_locked(pay_asg.assignment_id,
3525 pay_pac.effective_date) = ''N''';
3526 --
3527 intgrpdepbal := '
3528 and hr_dynsql.process_group_seq_locked(pay_asg.assignment_id,
3529 pay_pac.effective_date,
3530 ''B'') = ''N''';
3531 --
3532 intgrpdepret := '
3533 and hr_dynsql.process_group_seq_locked(pay_asg.assignment_id,
3534 pay_pac.effective_date,
3535 ''Y'') = ''N''';
3536 --
3537 intretgrpdep := '
3538 and not exists (select ''''
3539 from pay_object_groups pay_pog_asg2
3540 where pay_pog_asg2.parent_object_group_id = pay_pos.object_group_id
3541 and pay_pog_asg2.source_type = ''PAF''
3542 and hr_dynsql.process_group_seq_locked(pay_pog_asg2.source_id,
3543 pay_pac.effective_date,
3544 ''Y'') <> ''N''
3545 )';
3546 --
3547 -- and not exists clause to prevent creation of purge actions if
3548 -- either a purge already exists in the future or the previous
3549 -- purge hasn't been completed.
3550 -- Also reject any assignments that do not have at least one
3551 -- assignment action existing on or before the purge date
3552 -- and on or after the last purge date.
3553 -- If skip flag is set by the action parameter, terminated
3554 -- assignments are excluded. (Bug 4726174)
3555 --
3556 intpur := '
3557 and not exists (
3558 select null
3559 from pay_assignment_actions pay_ac2,
3560 pay_payroll_actions pay_pa2
3561 where pay_ac2.assignment_id = pay_asg.assignment_id +decode(pay_pos.period_of_service_id,0,0,0)
3562 and pay_pa2.payroll_action_id = pay_ac2.payroll_action_id
3563 and pay_pa2.action_type = ''Z''
3564 and (pay_ac2.secondary_status <> ''C''
3565 or (pay_pa2.effective_date >= pay_pac.effective_date)))
3566 and exists (
3567 select null
3568 from pay_assignment_actions pay_ac4,
3569 pay_payroll_actions pay_pa4
3570 where pay_ac4.assignment_id = pay_asg.assignment_id +decode(pay_pos.period_of_service_id,0,0,0)
3571 and pay_pa4.payroll_action_id = pay_ac4.payroll_action_id
3572 and pay_pa4.effective_date <= pay_pac.effective_date
3573 and pay_pa4.action_type <> ''Z''
3574 and pay_pa4.effective_date >=
3575 (select nvl(max(pay_pa42.effective_date)
3576 ,hr_general.start_of_time)
3577 from pay_assignment_actions pay_ac42,
3578 pay_payroll_actions pay_pa42
3579 where pay_ac42.assignment_id = pay_asg.assignment_id +decode(pay_pos.period_of_service_id,0,0,0)
3580 and pay_pa42.payroll_action_id = pay_ac42.payroll_action_id
3581 and pay_pa42.action_type = ''Z''
3582 and pay_ac42.secondary_status = ''C''
3583 ))
3584 and (not exists
3585 (select null from pay_action_parameters
3586 where parameter_name = ''PURGE_SKIP_TERM_ASG''
3587 and parameter_value = ''Y'')
3588 or (pay_pac.effective_date between
3589 pay_asg.effective_start_date and pay_asg.effective_end_date
3590 and exists
3591 (select null
3592 from per_time_periods pay_tp5
3593 where pay_tp5.payroll_id = pay_asg.payroll_id
3594 and pay_pac.effective_date between
3595 pay_tp5.start_date and pay_tp5.end_date)))';
3596 --
3597 -- Retropay sequence
3598 intretind := '
3599 and not exists (
3600 select /*+ INDEX (pay_pa2 pay_payroll_actions_pk) */
3601 null
3602 from pay_action_classifications pay_acl,
3603 pay_payroll_actions pay_pa2,
3604 pay_assignment_actions pay_ac2
3605 where pay_ac2.assignment_id = pay_asg.assignment_id
3606 and pay_pa2.payroll_action_id = pay_ac2.payroll_action_id
3607 and pay_acl.classification_name = ''SEQUENCED''
3608 and pay_pa2.action_type = pay_acl.action_type
3609 and ((pay_pa2.effective_date > pay_pac.effective_date
3610 and pay_ac2.action_status in (''C'', ''S''))
3611 or (pay_ac2.action_status not in (''C'', ''S'')
3612 and pay_pa2.effective_date <= pay_pac.effective_date)))';
3613 --
3614 -- Retropay by Element assignments with retro definition
3615 retdefasg := '
3616 from pay_payroll_actions pay_pac,
3617 per_all_assignments_f pay_asg,
3618 pay_retro_assignments pay_ret_asg,
3619 per_periods_of_service pay_pos
3620 where pay_pac.payroll_action_id = :payroll_action_id
3621 and pay_asg.payroll_id = pay_pac.payroll_id
3622 and pay_asg.period_of_service_id = pay_pos.period_of_service_id
3623 and pay_ret_asg.retro_assignment_action_id IS NULL
3624 and pay_ret_asg.superseding_retro_asg_id IS NULL -- 7364151
3625 and pay_ret_asg.assignment_id = pay_asg.assignment_id
3626 + decode(pay_asg.assignment_number, null, 0, 0)
3627 and pay_ret_asg.approval_status <> ''D''
3628 and pay_pac.effective_date between
3629 pay_asg.effective_start_date and pay_asg.effective_end_date';
3630 --
3631 -- Bug 1308309, Quick-Retropay, condition to pick just one assignment
3632 qretdefasg := '
3633 from pay_payroll_actions pay_pac,
3634 per_all_assignments_f pay_asg,
3635 pay_retro_assignments pay_ret_asg,
3636 per_periods_of_service pay_pos
3637 where pay_pac.payroll_action_id = :payroll_action_id
3638 and pay_asg.assignment_id = :assignment_id
3639 and pay_asg.payroll_id = pay_pac.payroll_id
3640 and pay_asg.period_of_service_id = pay_pos.period_of_service_id
3641 and pay_ret_asg.retro_assignment_action_id IS NULL
3642 and pay_ret_asg.superseding_retro_asg_id IS NULL -- 7364151
3643 and pay_ret_asg.assignment_id = pay_asg.assignment_id
3644 + decode(pay_asg.assignment_number, null, 0, 0)
3645 and pay_ret_asg.approval_status <> ''D''
3646 and pay_pac.effective_date between
3647 pay_asg.effective_start_date and pay_asg.effective_end_date';
3648 --
3649 retdefasgpg := '
3650 from
3651 pay_payroll_actions pay_pac,
3652 pay_object_groups pay_pos
3653 where pay_pac.payroll_action_id = :payroll_action_id
3654 and pay_pos.source_type = ''PPF''
3655 and exists (select ''''
3656 from pay_retro_assignments pay_ret_asg,
3657 per_all_assignments_f pay_asg2,
3658 pay_object_groups pay_pog_asg2
3659 where pay_pog_asg2.parent_object_group_id = pay_pos.object_group_id
3660 and pay_pog_asg2.source_type = ''PAF''
3661 and pay_asg2.assignment_id = pay_pog_asg2.source_id
3662 and pay_asg2.payroll_id + 0 = pay_pac.payroll_id
3663 and pay_ret_asg.assignment_id = pay_asg2.assignment_id
3664 and pay_ret_asg.retro_assignment_action_id IS NULL
3665 and pay_ret_asg.superseding_retro_asg_id IS NULL -- 7364151
3666 and pay_ret_asg.approval_status <> ''D''
3667 and pay_pac.effective_date between pay_asg2.effective_start_date
3668 and pay_asg2.effective_end_date
3669 )';
3670 -- Bug 1308309, Quick-Retropay
3671 qretdefasgpg := '
3672 from
3673 pay_payroll_actions pay_pac,
3674 pay_object_groups pay_pos
3675 where pay_pac.payroll_action_id = :payroll_action_id
3676 and pay_pos.source_type = ''PPF''
3677 and exists (select ''''
3678 from pay_retro_assignments pay_ret_asg,
3679 per_all_assignments_f pay_asg2,
3680 pay_object_groups pay_pog_asg2
3681 where pay_pog_asg2.parent_object_group_id = pay_pos.object_group_id
3682 and pay_pog_asg2.source_type = ''PAF''
3683 and pay_asg2.assignment_id = pay_pog_asg2.source_id
3684 and pay_asg2.payroll_id + 0 = pay_pac.payroll_id
3685 and pay_ret_asg.assignment_id = pay_asg2.assignment_id
3686 and pay_asg2.assignment_id = :assignment_id
3687 and pay_ret_asg.retro_assignment_action_id IS NULL
3688 and pay_ret_asg.superseding_retro_asg_id IS NULL -- 7364151
3689 and pay_ret_asg.approval_status <> ''D''
3690 and pay_pac.effective_date between pay_asg2.effective_start_date
3691 and pay_asg2.effective_end_date
3692 )';
3693 --
3694 -- Reversal amendments
3695 -- check for existence of a process that can be reversed as of reversal
3696 -- run date and that the action has not been previously reversed
3697 revaa :=
3698 '
3699 and not exists
3700 (select 1
3701 from pay_assignment_actions aa9
3702 where aa9.source_action_id = pay_paa2.assignment_action_id)
3703 /* check havent done reversal before */
3704 and not exists
3705 (select 1
3706 from pay_action_interlocks int,
3707 pay_assignment_actions aa9,
3708 pay_payroll_actions pay_ppa2
3709 where int.locked_action_id = pay_paa2.assignment_action_id
3710 and aa9.assignment_action_id = int.locking_action_id
3711 and pay_ppa2.payroll_action_id = aa9.payroll_action_id
3712 and pay_ppa2.action_type = ''V'')';
3713
3714 orgfrom := '
3715 from
3716 pay_payroll_actions pay_pac,
3717 hr_organization_units hou,
3718 hr_organization_information hoi
3719 where pay_pac.payroll_action_id = pay_proc_environment_pkg.get_pactid()
3720 and hou.business_group_id = pay_pac.business_group_id
3721 and hoi.organization_id = hou.organization_id
3722 and hoi.org_information_context = ''CLASS''
3723 and hoi.org_information1 = ''HR_PAYEE''';
3724 --
3725 orgbind := '
3726 and pay_pac.payroll_action_id = :payroll_action_id';
3727 --
3728 -- order by clause.
3729 orderby := '
3730 order by pay_pos.person_id';
3731 --
3732 orgorderby := '
3733 order by 1, 3 , 2';
3734 --
3735 -- order by clause for BEE.
3736 borderby := '
3737 order by pay_asg.person_id';
3738 --
3739 -- Bug 8725368 : Added sort by first column to facilitate batch reversal
3740 -- Run order by
3741 actorderby := '
3742 order by pay_asg.person_id, decode(pay_asg.primary_flag, ''Y'', 1, 2), pay_asg.assignment_id,1';
3743 -- actorderby := '
3744 -- order by pay_asg.person_id';
3745 --
3746 -- for update clause to lock assignment and period of service.
3747 fupdate := '
3748 for update of pay_asg.assignment_id, pay_pos.period_of_service_id';
3749 --
3750 end hr_dynsql;
[Home] [Help]