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