[Home] [Help]
PACKAGE BODY: APPS.HR_JP_AST_UTILITY_PKG
Source
1 package body hr_jp_ast_utility_pkg as
2 /* $Header: hrjpastu.pkb 120.0.12010000.1 2008/10/14 08:16:03 keyazawa noship $ */
3 --
4 -- Constants
5 --
6 c_package constant varchar2(31) := 'hr_jp_ast_utility_pkg.';
7 --
8 -- Global Variables
9 --
10 g_assignment_set_id number;
11 g_formula_id number;
12 g_amendment_type varchar2(1);
13 type t_include_or_exclude_tbl is table of hr_assignment_set_amendments.include_or_exclude%type
14 index by binary_integer;
15 g_include_or_excludes t_include_or_exclude_tbl;
16 -- ----------------------------------------------------------------------------
17 -- |-----------------------< chk_assignment_set_name >------------------------|
18 -- ----------------------------------------------------------------------------
19 procedure chk_assignment_set_name(
20 p_assignment_set_name in varchar2,
21 p_business_group_id in number)
22 is
23 l_proc varchar2(61) := c_package || 'chk_assignment_set_name';
24 --
25 l_dummy varchar2(1);
26 l_formula_type_id number;
27 l_formula_name ff_formulas_f.formula_name%type;
28 cursor csr_asg_set is
29 select null
30 from hr_assignment_sets
31 where upper(assignment_set_name) = upper(p_assignment_set_name)
32 and business_group_id = p_business_group_id;
33 begin
34 hr_utility.set_location('Entering : ' || l_proc, 10);
35 --
36 open csr_asg_set;
37 fetch csr_asg_set into l_dummy;
38 if csr_asg_set%found then
39 close csr_asg_set;
40 fnd_message.set_name('PER','HR_6395_SETUP_SET_EXISTS');
41 fnd_message.raise_error;
42 else
43 close csr_asg_set;
44 --
45 select formula_type_id
46 into l_formula_type_id
47 from ff_formula_types
48 where formula_type_name = 'Assignment Set';
49 --
50 l_formula_name := p_assignment_set_name;
51 ffdict.validate_formula(
52 p_formula_name => l_formula_name,
53 p_formula_type_id => l_formula_type_id,
54 p_bus_grp => p_business_group_id,
55 p_leg_code => hr_api.return_legislation_code(p_business_group_id));
56 end if;
57 --
58 hr_utility.set_location('Leaving : ' || l_proc, 20);
59 end chk_assignment_set_name;
60 -- ----------------------------------------------------------------------------
61 -- |----------------------------< create_asg_set >----------------------------|
62 -- ----------------------------------------------------------------------------
63 procedure create_asg_set(
64 p_assignment_set_name in varchar2,
65 p_business_group_id in number,
66 p_payroll_id in number,
67 p_assignment_set_id out nocopy number)
68 is
69 l_proc varchar2(61) := c_package || 'create_asg_set';
70 --
71 l_rowid varchar2(18);
72 l_assignment_set_id number;
73 begin
74 hr_utility.set_location('Entering : ' || l_proc, 10);
75 --
76 chk_assignment_set_name(
77 p_assignment_set_name => p_assignment_set_name,
78 p_business_group_id => p_business_group_id);
79 --
80 select hr_assignment_sets_s.nextval
81 into p_assignment_set_id
82 from dual;
83 --
84 hr_assignment_sets_pkg.insert_row(
85 p_rowid => l_rowid,
86 p_assignment_set_id => p_assignment_set_id,
87 p_business_group_id => p_business_group_id,
88 p_payroll_id => p_payroll_id,
89 p_assignment_set_name => p_assignment_set_name,
90 p_formula_id => null);
91 --
92 hr_utility.set_location('Leaving : ' || l_proc, 20);
93 end create_asg_set;
94 -- ----------------------------------------------------------------------------
95 -- |--------------------< create_asg_set_with_request_id >--------------------|
96 -- ----------------------------------------------------------------------------
97 procedure create_asg_set_with_request_id(
98 p_prefix in varchar2,
99 p_business_group_id in number,
100 p_payroll_id in number,
101 p_assignment_set_id out nocopy number,
102 p_assignment_set_name out nocopy varchar2)
103 is
104 l_proc varchar2(61) := c_package || 'create_asg_set_with_request_id';
105 --
106 l_rowid varchar2(18);
107 --
108 -- If not submitted from SRS, fnd_global.conc_request_id returns "-1"
109 -- which causes error in hr_chkfmt package.
110 --
111 l_assignment_set_name hr_assignment_sets.assignment_set_name%type
112 := p_prefix || to_char(greatest(fnd_global.conc_request_id, 0));
113 l_counter number := 0;
114 begin
115 hr_utility.set_location('Entering : ' || l_proc, 10);
116 --
117 -- Assignment Set Name : <Prefix>_<Request ID>
118 -- If the assignment name already exists, add sequence
119 -- to above assignment set name as suffix like this.
120 -- Assignment Set Name : REQUEST_ID_<Request ID>_<Counter>
121 --
122 loop
123 if l_counter = 0 then
124 p_assignment_set_name := l_assignment_set_name;
125 else
126 p_assignment_set_name := l_assignment_set_name || '_' || to_char(l_counter);
127 end if;
128 --
129 hr_utility.trace('Assignment Set Name : ' || p_assignment_set_name);
130 --
131 begin
132 create_asg_set(
133 p_assignment_set_name => p_assignment_set_name,
134 p_business_group_id => p_business_group_id,
135 p_payroll_id => p_payroll_id,
136 p_assignment_set_id => p_assignment_set_id);
137 --
138 exit;
139 exception
140 when others then
141 --
142 -- There's possibility the prefix is inappropriate
143 -- which will cause infinite loop.
144 -- The following code raises error if the loop counter
145 -- gets over 1000.
146 --
147 if l_counter > 1000 then
148 raise;
149 else
150 l_counter := l_counter + 1;
151 end if;
152 end;
153 end loop;
154 --
155 hr_utility.set_location('Leaving : ' || l_proc, 20);
156 end create_asg_set_with_request_id;
157 -- ----------------------------------------------------------------------------
158 -- |--------------------------< create_asg_set_amd >--------------------------|
159 -- ----------------------------------------------------------------------------
160 procedure create_asg_set_amd(
161 p_assignment_set_id in number,
162 p_assignment_id in number,
163 p_include_or_exclude in varchar2)
164 is
165 l_rowid varchar2(18);
166 begin
167 hr_assignment_set_amds_pkg.insert_row(
168 p_rowid => l_rowid,
169 p_assignment_id => p_assignment_id,
170 p_assignment_set_id => p_assignment_set_id,
171 p_include_or_exclude => p_include_or_exclude);
172 end create_asg_set_amd;
173 -- ----------------------------------------------------------------------------
174 -- |-----------------------< get_assignment_set_info >------------------------|
175 -- ----------------------------------------------------------------------------
176 procedure get_assignment_set_info(p_assignment_set_id in number)
177 is
178 l_proc varchar2(61) := c_package || 'get_assignment_set_info';
179 --
180 function amendment_exists(p_include_or_exclude in varchar2) return boolean
181 is
182 l_exists varchar2(1);
183 --
184 cursor csr_exists is
185 select 'Y'
186 from dual
187 where exists(
188 select null
189 from hr_assignment_set_amendments
190 where assignment_set_id = p_assignment_set_id
191 and include_or_exclude = p_include_or_exclude);
192 begin
193 open csr_exists;
194 fetch csr_exists into l_exists;
195 if csr_exists%notfound then
196 l_exists := 'N';
197 end if;
198 close csr_exists;
199 --
200 return (l_exists = 'Y');
201 end amendment_exists;
202 begin
203 hr_utility.set_location('Entering: ' || l_proc, 10);
204 --
205 -- Setup assignment set information and cache those into global variables.
206 --
207 if g_assignment_set_id is null or g_assignment_set_id <> p_assignment_set_id then
208 hr_utility.trace('Caching...');
209 --
210 g_assignment_set_id := null;
211 g_formula_id := null;
212 g_amendment_type := null;
213 g_include_or_excludes.delete;
214 --
215 select formula_id
216 into g_formula_id
217 from hr_assignment_sets
218 where assignment_set_id = p_assignment_set_id;
219 --
220 g_amendment_type := 'N';
221 if g_formula_id is null then
222 if amendment_exists('I') then
223 g_amendment_type := 'I';
224 elsif amendment_exists('E') then
225 g_amendment_type := 'E';
226 end if;
227 else
228 if amendment_exists('I') then
229 if amendment_exists('E') then
230 g_amendment_type := 'B';
231 else
232 g_amendment_type := 'I';
233 end if;
234 elsif amendment_exists('E') then
235 g_amendment_type := 'E';
236 end if;
237 end if;
238 --
239 g_assignment_set_id := p_assignment_set_id;
240 end if;
241 --
242 hr_utility.trace('assignment_set_id: ' || g_assignment_set_id);
243 hr_utility.trace('formula_id : ' || g_formula_id);
244 hr_utility.trace('amendment_type : ' || g_amendment_type);
245 hr_utility.set_location('Leaving : ' || l_proc, 100);
246 end get_assignment_set_info;
247 --
248 procedure get_assignment_set_info(
249 p_assignment_set_id in number,
250 p_formula_id out nocopy number,
251 p_amendment_type out nocopy varchar2)
252 is
253 begin
254 get_assignment_set_info(p_assignment_set_id);
255 --
256 p_formula_id := g_formula_id;
257 p_amendment_type := g_amendment_type;
258 end get_assignment_set_info;
259 -- ----------------------------------------------------------------------------
260 -- |--------------------------< amendment_validate >--------------------------|
261 -- ----------------------------------------------------------------------------
262 function amendment_validate(
263 p_assignment_set_id in number,
264 p_assignment_id in number) return varchar2
265 is
266 l_proc varchar2(61) := c_package || 'amendment_validate';
267 --
268 type t_number_tbl is table of number index by binary_integer;
269 l_assignment_ids t_number_tbl;
270 l_include_or_excludes t_include_or_exclude_tbl;
271 l_include_or_exclude hr_assignment_set_amendments.include_or_exclude%type;
272 l_include_flag varchar2(1);
273 begin
274 hr_utility.set_location('Entering: ' || l_proc, 10);
275 --
276 get_assignment_set_info(p_assignment_set_id);
277 --
278 -- include_flag = F -> Additional formula validation required.
279 -- include_flag = Y -> To be included. No formula validation required.
280 -- include_flag = N -> To be excluded. No formula validation required.
281 --
282 if g_amendment_type = 'N' then
283 hr_utility.set_location(l_proc, 20);
284 --
285 if g_formula_id is null then
286 l_include_flag := 'Y';
287 else
288 l_include_flag := 'F';
289 end if;
290 else
291 hr_utility.set_location(l_proc, 30);
292 --
293 -- Cache amendment information into PL/SQL table.
294 -- In most cases, amendment information is very few (< 100 records),
295 -- which will raise performance when cached with bulk collect.
296 --
297 if g_include_or_excludes.count = 0 then
298 hr_utility.trace('Caching...');
299 --
300 select assignment_id,
301 include_or_exclude
302 bulk collect into
303 l_assignment_ids,
304 l_include_or_excludes
305 from hr_assignment_set_amendments
306 where assignment_set_id = p_assignment_set_id;
307 --
308 for i in 1..l_assignment_ids.count loop
309 g_include_or_excludes(l_assignment_ids(i)) := l_include_or_excludes(i);
310 end loop;
311 end if;
312 --
313 if g_include_or_excludes.exists(p_assignment_id) then
314 hr_utility.set_location(l_proc, 31);
315 --
316 l_include_or_exclude := g_include_or_excludes(p_assignment_id);
317 end if;
318 --
319 if l_include_or_exclude = 'E' then
320 l_include_flag := 'N';
321 elsif l_include_or_exclude = 'I' then
322 l_include_flag := 'Y';
323 else
324 if g_formula_id is null then
325 if g_amendment_type = 'E' then
326 l_include_flag := 'Y';
327 else
328 l_include_flag := 'N';
329 end if;
330 else
331 l_include_flag := 'F';
332 end if;
333 end if;
334 end if;
335 --
336 hr_utility.trace('include_flag: ' || l_include_flag);
337 hr_utility.set_location('Leaving: ' || l_proc, 100);
338 --
339 return l_include_flag;
340 end amendment_validate;
341 -- ----------------------------------------------------------------------------
342 -- |---------------------------< formula_validate >---------------------------|
343 -- ----------------------------------------------------------------------------
344 function formula_validate(
345 p_formula_id in number,
346 p_assignment_id in number,
347 p_effective_date in date,
348 p_populate_fs in boolean default false) return boolean
349 is
350 l_proc varchar2(61) := c_package || 'formula_validate';
351 --
352 l_inputs ff_exec.inputs_t;
353 l_outputs ff_exec.outputs_t;
354 l_include_flag varchar2(255);
355 --
356 l_rowid rowid;
357 l_effective_date date;
358 begin
359 hr_utility.set_location('Entering : ' || l_proc, 10);
360 --
361 ff_exec.init_formula(
362 p_formula_id => p_formula_id,
363 p_effective_date => p_effective_date,
364 p_inputs => l_inputs,
365 p_outputs => l_outputs);
366 --
367 -- Set the Formula Contexts.
368 -- Assignment Set only supports the context "ASSIGNMENT_ID"/"DATE_EARNED".
369 --
370 for i in 1..l_inputs.count loop
371 if l_inputs(i).class = 'CONTEXT' then
372 if l_inputs(i).name = 'ASSIGNMENT_ID' then
373 l_inputs(i).value := to_char(p_assignment_id);
374 elsif l_inputs(i).name = 'DATE_EARNED' then
375 l_inputs(i).value := fnd_date.date_to_canonical(p_effective_date);
376 end if;
377 end if;
378 end loop;
379 --
380 begin
381 --
382 -- savepoint/rollback to savepoint for FND_SESSIONS
383 -- will raise error ORA-14552 when this function is used in SQL.
384 -- To suppress this error when this function is used in SQL,
385 -- do not pass "true" to input parameter "p_populate_fs".
386 --
387 if p_populate_fs then
388 savepoint jp_ast_utility1;
389 --
390 begin
391 select rowid,
392 effective_date
393 into l_rowid,
394 l_effective_date
395 from fnd_sessions
396 where session_id = userenv('sessionid');
397 --
398 if l_effective_date <> p_effective_date then
399 update fnd_sessions
400 set effective_date = p_effective_date
401 where rowid = l_rowid;
402 end if;
403 exception
404 when no_data_found then
405 insert into fnd_sessions(
406 session_id,
407 effective_date)
408 values( userenv('sessionid'),
409 p_effective_date);
410 end;
411 end if;
412 --
413 ff_exec.run_formula(
414 p_inputs => l_inputs,
415 p_outputs => l_outputs,
416 p_use_dbi_cache => TRUE);
417 --
418 if p_populate_fs then
419 rollback to savepoint jp_ast_utility1;
420 end if;
421 exception
422 when others then
423 if p_populate_fs then
424 rollback to savepoint jp_ast_utility1;
425 end if;
426 --
427 raise;
428 end;
429 --
430 for i in 1..l_outputs.count loop
431 if l_outputs(i).name = 'INCLUDE_FLAG' then
432 l_include_flag := l_outputs(i).value;
433 exit;
434 end if;
435 end loop;
436 --
437 if l_include_flag = 'Y' then
438 return true;
439 else
440 return false;
441 end if;
442 --
443 hr_utility.set_location('Leaving : ' || l_proc, 20);
444 end formula_validate;
445 -- ----------------------------------------------------------------------------
446 -- |-----------------------< assignment_set_validate >------------------------|
447 -- ----------------------------------------------------------------------------
448 function assignment_set_validate(
449 p_assignment_set_id in number,
450 p_assignment_id in number,
451 p_effective_date in date,
452 p_populate_fs_flag in varchar2 default 'N') return varchar2
453 is
454 l_proc varchar2(61) := c_package || 'assignment_set_validate';
455 l_include_flag varchar2(1);
456 l_include_or_exclude hr_assignment_set_amendments.include_or_exclude%type;
457 begin
458 hr_utility.set_location('Entering: ' || l_proc, 10);
459 --
460 get_assignment_set_info(p_assignment_set_id);
461 --
462 -- Check the assignment is to be included/excluded.
463 --
464 l_include_flag := amendment_validate(p_assignment_set_id, p_assignment_id);
465 --
466 if l_include_flag = 'F' then
467 hr_utility.set_location(l_proc, 20);
468 --
469 if formula_validate(g_formula_id, p_assignment_id, p_effective_date, (p_populate_fs_flag = 'Y')) then
470 l_include_flag := 'Y';
471 else
472 l_include_flag := 'N';
473 end if;
474 end if;
475 --
476 hr_utility.trace('include_flag: ' || l_include_flag);
477 hr_utility.set_location('Leaving: ' || l_proc, 100);
478 --
479 return l_include_flag;
480 end assignment_set_validate;
481 -- ----------------------------------------------------------------------------
482 -- |-------------------------------< pay_asgs >-------------------------------|
483 -- ----------------------------------------------------------------------------
484 procedure pay_asgs(
485 p_payroll_id in number,
486 p_effective_date in date,
487 p_start_date in date,
488 p_end_date in date,
489 p_assignment_set_id in number,
490 p_asg_rec out nocopy t_asg_rec)
491 is
492 l_proc varchar2(61) := c_package || 'pay_asgs';
493 --
494 l_formula_id number;
495 l_include_flag varchar2(1);
496 cursor csr_formula_id is
497 select formula_id
498 from hr_assignment_sets
499 where assignment_set_id = p_assignment_set_id;
500 cursor csr_include is
501 select 'Y'
502 from hr_assignment_set_amendments
503 where assignment_set_id = p_assignment_set_id
504 and include_or_exclude = 'I'
505 and rownum < 2;
506 --
507 l_include_or_exclude_tbl t_varchar2_tbl;
508 l_index number := 0;
509 l_process boolean;
510 --
511 -- 1. Assignment must exist on p_effective_date with payroll specified.
512 -- 2. Assignment must exist between p_start_date and p_end_date with payroll specified.
513 --
514 cursor csr_asg_all is
515 select /*+ ORDERED */
516 asg3.assignment_id,
517 greatest(asg3.effective_start_date, p_start_date) effective_date,
518 asg3.assignment_number,
519 per.full_name
520 from (
521 select /*+ ORDERED
522 USE_NL(ASG1, PPOS, ASG2)
523 INDEX(ASG1 PER_ASSIGNMENTS_F_N7)
524 INDEX(PPOS PER_PERIODS_OF_SERVICE_PK)
525 INDEX(ASG2 PER_ASSIGNMENTS_F_PK) */
526 asg2.assignment_id,
527 min(asg2.effective_start_date) effective_start_date
528 from per_all_assignments_f asg1,
529 per_periods_of_service ppos,
530 per_all_assignments_f asg2
531 where asg1.payroll_id = p_payroll_id
532 and p_effective_date
533 between asg1.effective_start_date and asg1.effective_end_date
534 and ppos.period_of_service_id = asg1.period_of_service_id
535 and p_effective_date
536 between ppos.date_start and nvl(ppos.final_process_date,p_effective_date)
537 and asg2.assignment_id = asg1.assignment_id
538 and asg2.effective_end_date >= p_start_date
539 and asg2.effective_start_date <= p_end_date
540 and asg2.payroll_id +0 = asg1.payroll_id
541 group by asg2.assignment_id
542 ) v,
543 per_all_assignments_f asg3,
544 per_all_people_f per
545 --
546 -- Assignment information must be retrieved by Batch Line Upload Date.
547 --
548 where asg3.assignment_id = v.assignment_id
549 and asg3.effective_start_date = v.effective_start_date
550 and per.person_id = asg3.person_id
551 --
552 -- Person information must be retrieved by Batch Line Upload Date.
553 --
554 and greatest(asg3.effective_start_date, p_start_date)
555 between per.effective_start_date and per.effective_end_date
556 order by nvl(per.order_name, per.full_name), asg3.assignment_number;
557 --
558 cursor csr_asg_inc is
559 select /*+ ORDERED */
560 asg3.assignment_id,
561 greatest(asg3.effective_start_date, p_start_date) effective_date,
562 asg3.assignment_number,
563 per.full_name
564 from (
565 select /*+ ORDERED */
566 asg2.assignment_id,
567 min(asg2.effective_start_date) effective_start_date
568 from hr_assignment_set_amendments asa,
569 per_all_assignments_f asg1,
570 per_periods_of_service ppos,
571 per_all_assignments_f asg2
572 where asa.assignment_set_id = p_assignment_set_id
573 and asa.include_or_exclude = 'I'
574 and asg1.assignment_id = asa.assignment_id
575 and p_effective_date
576 between asg1.effective_start_date and asg1.effective_end_date
577 and ppos.period_of_service_id = asg1.period_of_service_id
578 and p_effective_date
579 between ppos.date_start and nvl(ppos.final_process_date,p_effective_date)
580 and asg1.payroll_id + 0 = p_payroll_id
581 and asg2.assignment_id = asg1.assignment_id
582 and asg2.effective_end_date >= p_start_date
583 and asg2.effective_start_date <= p_end_date
584 and asg2.payroll_id + 0 = asg1.payroll_id
585 group by asg2.assignment_id
586 ) v,
587 per_all_assignments_f asg3,
588 per_all_people_f per
589 where asg3.assignment_id = v.assignment_id
590 and asg3.effective_start_date = v.effective_start_date
591 and per.person_id = asg3.person_id
592 and greatest(asg3.effective_start_date, p_start_date)
593 between per.effective_start_date and per.effective_end_date
594 order by nvl(per.order_name, per.full_name), asg3.assignment_number;
595 --
596 cursor csr_asg_exc is
597 select /*+ ORDERED */
598 asg3.assignment_id,
599 greatest(asg3.effective_start_date, p_start_date) effective_date,
600 asg3.assignment_number,
601 per.full_name,
602 v.include_or_exclude
603 from (
604 select /*+ ORDERED
605 USE_NL(ASG1, PPOS, ASG2, ASA)
606 INDEX(ASG1 PER_ASSIGNMENTS_F_N7)
607 INDEX(PPOS PER_PERIODS_OF_SERVICE_PK)
608 INDEX(ASG2 PER_ASSIGNMENTS_F_PK)
609 INDEX(ASA HR_ASSIGNMENT_SET_AMENDMEN_PK) */
610 asg2.assignment_id,
611 min(asg2.effective_start_date) effective_start_date,
612 min(asa.include_or_exclude) include_or_exclude
613 from per_all_assignments_f asg1,
614 per_periods_of_service ppos,
615 per_all_assignments_f asg2,
616 hr_assignment_set_amendments asa
617 where asg1.payroll_id = p_payroll_id
618 and p_effective_date
619 between asg1.effective_start_date and asg1.effective_end_date
620 and ppos.period_of_service_id = asg1.period_of_service_id
621 and p_effective_date
622 between ppos.date_start and nvl(ppos.final_process_date,p_effective_date)
623 and asg2.assignment_id = asg1.assignment_id
624 and asg2.effective_end_date >= p_start_date
625 and asg2.effective_start_date <= p_end_date
626 and asg2.payroll_id + 0 = asg1.payroll_id
627 and asa.assignment_set_id(+) = p_assignment_set_id
628 and asa.assignment_id(+) = asg2.assignment_id
629 and nvl(asa.include_or_exclude, 'I') = 'I'
630 group by asg2.assignment_id
631 ) v,
632 per_all_assignments_f asg3,
633 per_all_people_f per
634 where asg3.assignment_id = v.assignment_id
635 and asg3.effective_start_date = v.effective_start_date
636 and per.person_id = asg3.person_id
637 and greatest(asg3.effective_start_date, p_start_date)
638 between per.effective_start_date and per.effective_end_date
639 order by nvl(per.order_name, per.full_name), asg3.assignment_number;
640 --
641 begin
642 hr_utility.set_location('Entering : ' || l_proc, 10);
643 --
644 -- When assignment set is not specified,
645 -- all assignments are the target.
646 --
647 if p_assignment_set_id is null then
648 hr_utility.trace('csr_asg_all bulk collect');
649 --
650 open csr_asg_all;
651 fetch csr_asg_all bulk collect into
652 p_asg_rec.assignment_id_tbl,
653 p_asg_rec.effective_date_tbl,
654 p_asg_rec.assignment_number_tbl,
655 p_asg_rec.full_name_tbl;
656 close csr_asg_all;
657 --
658 -- When assignment set is specified,
659 --
660 else
661 --
662 -- Derive formula_id of the assignment set
663 --
664 open csr_formula_id;
665 fetch csr_formula_id into l_formula_id;
666 if csr_formula_id%NOTFOUND then
667 close csr_formula_id;
668 raise no_data_found;
669 end if;
670 close csr_formula_id;
671 --
672 -- Check whether "Include" exists or not as amendments
673 --
674 open csr_include;
675 fetch csr_include into l_include_flag;
676 if csr_include%NOTFOUND then
677 l_include_flag := 'N';
678 end if;
679 close csr_include;
680 --
681 -- In case criteria is not set
682 --
683 if l_formula_id is null then
684 --
685 -- When only "Include" is set as amendments (no criteria)
686 --
687 if l_include_flag = 'Y' then
688 hr_utility.trace('csr_asg_inc bulk collect');
689 --
690 open csr_asg_inc;
691 fetch csr_asg_inc bulk collect into
692 p_asg_rec.assignment_id_tbl,
693 p_asg_rec.effective_date_tbl,
694 p_asg_rec.assignment_number_tbl,
695 p_asg_rec.full_name_tbl;
696 close csr_asg_inc;
697 --
698 -- When only "Exclude" is set as amendments (no criteria)
699 --
700 else
701 hr_utility.trace('csr_asg_exc bulk collect');
702 --
703 open csr_asg_exc;
704 fetch csr_asg_exc bulk collect into
705 p_asg_rec.assignment_id_tbl,
706 p_asg_rec.effective_date_tbl,
707 p_asg_rec.assignment_number_tbl,
708 p_asg_rec.full_name_tbl,
709 l_include_or_exclude_tbl;
710 close csr_asg_exc;
711 end if;
712 --
713 -- In case criteria is set
714 --
715 else
716 --
717 -- Need to validate whether each assignment should be processed or not using FastFormula.
718 --
719 hr_utility.trace('csr_asg_exc for loop');
720 --
721 for l_asg_rec in csr_asg_exc loop
722 l_process := false;
723 --
724 -- When include_or_exclude is 'I'(not null),
725 -- the assignment must be processed without validating FastFormula.
726 --
727 if l_asg_rec.include_or_exclude is not null then
728 hr_utility.trace('INC : ' || to_char(l_asg_rec.assignment_id));
729 --
730 l_process := true;
731 --
732 -- Validate the assignment with FastFormula as of Upload Date.
733 --
734 elsif formula_validate(l_formula_id, l_asg_rec.assignment_id, l_asg_rec.effective_date) then
735 hr_utility.trace('FF : ' || to_char(l_asg_rec.assignment_id));
736 --
737 l_process := true;
738 end if;
739 --
740 -- When the assignment is validated to be processed
741 --
742 if l_process then
743 l_index := l_index + 1;
744 p_asg_rec.assignment_id_tbl(l_index) := l_asg_rec.assignment_id;
745 p_asg_rec.effective_date_tbl(l_index) := l_asg_rec.effective_date;
746 p_asg_rec.assignment_number_tbl(l_index):= l_asg_rec.assignment_number;
747 p_asg_rec.full_name_tbl(l_index) := l_asg_rec.full_name;
748 end if;
749 end loop;
750 end if;
751 end if;
752 --
753 hr_utility.set_location('Leaving : ' || l_proc, 20);
754 end pay_asgs;
755 --
756 end hr_jp_ast_utility_pkg;