[Home] [Help]
PACKAGE BODY: APPS.PAY_CA_T4A_REG
Source
1 PACKAGE BODY pay_ca_t4a_reg AS
2 /* $Header: pycat4ar.pkb 120.5.12020000.3 2013/01/25 13:00:01 rgottipa ship $ */
3
4 ----------------------------- range_cursor ----------------------------------
5
6 function get_user_entity_id(p_user_name varchar2) return number is
7
8 begin
9
10 declare
11
12 cursor cur_user_entity_id is
13 select user_entity_id
14 from ff_database_items
15 where user_name = p_user_name;
16
17 l_user_entity_id ff_database_items.user_entity_id%TYPE;
18
19 begin
20
21 open cur_user_entity_id;
22
23 fetch cur_user_entity_id
24 into l_user_entity_id;
25
26 close cur_user_entity_id;
27
28 return l_user_entity_id;
29
30 end;
31 end;
32
33 procedure range_cursor (pactid in number, sqlstr out nocopy varchar2) is
34 l_payroll_id number;
35 leg_param pay_payroll_actions.legislative_parameters%type;
36 l_uid_tax_year ff_user_entities.user_entity_id%TYPE;
37 l_uid_tax_unit_id ff_user_entities.user_entity_id%TYPE;
38 l_uid_person_id ff_user_entities.user_entity_id%TYPE;
39 --
40 begin
41 --hr_utility.trace_on('Y','ORACLE');
42 hr_utility.trace('begining of range_cursor 1 ');
43
44 select
45 legislative_parameters
46 into
47 leg_param
48 from
49 pay_payroll_actions ppa
50 where ppa.payroll_action_id = pactid;
51
52 l_uid_tax_year := get_user_entity_id('CAEOY_TAXATION_YEAR');
53 l_uid_tax_unit_id := get_user_entity_id('CAEOY_TAX_UNIT_ID');
54 l_uid_person_id := get_user_entity_id('CAEOY_PERSON_ID');
55
56 sqlstr := 'select distinct to_number(fai3.value)
57 from ff_archive_items fai1,
58 ff_archive_items fai2,
59 ff_archive_items fai3,
60 pay_payroll_actions ppa,
61 pay_assignment_actions paa
62 where ppa.payroll_action_id = :payroll_action_id
63 and fai1.user_entity_id = ' || l_uid_tax_year ||
64 ' and fai1.value =
65 nvl(pay_ca_t4a_reg.get_parameter(''TAX_YEAR'',ppa.legislative_parameters),fai1.value)
66 and fai2.user_entity_id = ' || l_uid_tax_unit_id ||
67 ' and fai2.value =
68 nvl(pay_ca_t4a_reg.get_parameter(''GRE_ID'',ppa.legislative_parameters),fai2.value)
69 and fai1.context1 = fai2.context1
70 and paa.payroll_action_id= fai2.context1
71 and paa.assignment_action_id=fai3.context1
72 and fai3.user_entity_id = ' || l_uid_person_id ||
73 ' and fai3.value =
74 nvl(pay_ca_t4a_reg.get_parameter(''PER_ID'',ppa.legislative_parameters),fai3.value)
75 order by to_number(fai3.value)';
76
77 hr_utility.trace('End of range_cursor 2 ');
78
79 end range_cursor;
80 ------------------------- action_creation ----------------------------------
81
82 procedure action_creation(pactid in number,
83 stperson in number,
84 endperson in number,
85 chunk in number) is
86
87 lockingactid number;
88 lockedactid number;
89 l_asg_set_id number;
90 l_asg_id number;
91 l_tax_unit_id number;
92 l_year varchar2(4);
93 l_bus_group_id number;
94 l_year_start date;
95 l_year_end date;
96 l_t4areg_tax_unit_id number;
97 l_effective_date date;
98 l_report_type varchar2(80);
99
100 -- Variables declared for bug 10399514
101 l_person_on boolean ;
102 l_report_cat pay_payroll_actions.report_category%type;
103 l_state pay_payroll_actions.report_qualifier%type;
104 l_report_format pay_report_format_mappings_f.report_format%type;
105 -- Variables declared for bug 10399514
106
107 l_legislative_parameters pay_payroll_actions.legislative_parameters%TYPE;
108 l_uid_caeoy_tax_year ff_user_entities.user_entity_id%TYPE;
109 l_uid_caeoy_tax_unit_id ff_user_entities.user_entity_id%TYPE;
110 l_arch_pactid pay_payroll_actions.payroll_action_id%TYPE;
111 l_session_date pay_payroll_actions.effective_date%TYPE;
112 lv_per_id varchar2(30);
113
114 l_print_term varchar2(1);
115 l_effective_end_date date;
116
117
118 cursor c_all_gres is
119 select distinct to_number(fai2.value) tax_unit_id,
120 payroll_action_id arch_pactid,
121 ppa.effective_date
122 from pay_payroll_actions ppa,
123 ff_archive_items fai1,
124 ff_archive_items fai2
125 where fai1.user_entity_id = l_uid_caeoy_tax_year
126 and fai1.value = l_year
127 and fai2.context1 = fai1.context1
128 and fai2.user_entity_id = l_uid_caeoy_tax_unit_id
129 and ppa.payroll_action_id = fai1.context1
130 and ppa.report_type = 'T4A'
131 and ppa.report_qualifier = 'CAEOY'
132 and ppa.report_category = 'CAEOY'
133 and ppa.action_type = 'X'
134 and ppa.business_group_id+0 = l_bus_group_id;
135
136 cursor cur_gre is
137 select payroll_action_id arch_pactid,
138 ppa.effective_date
139 from pay_payroll_actions ppa,
140 ff_archive_items fai1,
141 ff_archive_items fai2
142 where fai1.user_entity_id = l_uid_caeoy_tax_year
143 and fai1.value = l_year
144 and ppa.payroll_action_id = fai1.context1
145 and ppa.report_type = 'T4A'
146 and ppa.report_qualifier = 'CAEOY'
147 and ppa.report_category = 'CAEOY'
148 and ppa.action_type = 'X'
149 and ppa.business_group_id + 0 = l_bus_group_id
150 and fai1.context1 = fai2.context1
151 and fai2.user_entity_id = l_uid_caeoy_tax_unit_id
152 and fai2.value = to_char(l_t4areg_tax_unit_id);
153
154 cursor c_all_asg(p_arch_pactid number) is
155 select
156 paf.assignment_id assignment_id,
157 paa.assignment_action_id,
158 paa.payroll_action_id,
159 paf.effective_end_date
160 from
161 per_assignments_f paf,
162 pay_assignment_actions paa
163 where
164 paf.person_id >= stperson and
165 paf.person_id <= endperson and
166 paf.primary_flag = 'Y' and
167 paf.assignment_type = 'E' and
168 paf.business_group_id = l_bus_group_id and
169 paf.effective_start_date = (select max(paf2.effective_start_date)
170 from per_assignments_f paf2
171 where paf2.assignment_id= paf.assignment_id
172 and paf2.primary_flag = 'Y'
173 and paf2.effective_start_date
174 <= l_session_date) and --l_year_end
175 paf.effective_end_date >= l_year_start and
176 paf.assignment_id = paa.assignment_id and
177 paa.payroll_action_id = p_arch_pactid;
178
179 -- Added for Bug# 10399514
180 -- Used when RANGE_PERSON_ID functionality is available
181 cursor c_all_asg_range(p_arch_pactid number) is
182 select
183 paf.assignment_id assignment_id,
184 paa.assignment_action_id,
185 paa.payroll_action_id,
186 paf.effective_end_date
187 from
188 per_assignments_f paf,
189 pay_assignment_actions paa,
190 pay_population_ranges ppr
191 where
192 -- paf.person_id >= stperson and
193 -- paf.person_id <= endperson and
194 ppr.payroll_action_id = pactid and
195 ppr.chunk_number = chunk and
196 ppr.person_id = paf.person_id and
197 paf.primary_flag = 'Y' and
198 paf.assignment_type = 'E' and
199 paf.business_group_id = l_bus_group_id and
200 paf.effective_start_date = (select max(paf2.effective_start_date)
201 from per_assignments_f paf2
202 where paf2.assignment_id= paf.assignment_id
203 and paf2.primary_flag = 'Y'
204 and paf2.effective_start_date
205 <= l_session_date) and --l_year_end
206 paf.effective_end_date >= l_year_start and
207 paf.assignment_id = paa.assignment_id and
208 paa.payroll_action_id = p_arch_pactid;
209
210
211 --Changes for bug 15886428 starts
212 cursor c_all_asg_term(p_arch_pactid number) is
213 select
214 paf.assignment_id assignment_id,
215 paa.assignment_action_id,
216 paa.payroll_action_id,
217 paf.effective_end_date
218 from
219 per_assignments_f paf,
220 pay_assignment_actions paa,
221 per_periods_of_service PDS
222 where
223 paf.person_id >= stperson and
224 paf.person_id <= endperson and
225 paf.primary_flag = 'Y' and
226 paf.assignment_type = 'E' and
227 paf.business_group_id = l_bus_group_id and
228 paf.effective_start_date = (select max(paf2.effective_start_date)
229 from per_assignments_f paf2
230 where paf2.assignment_id= paf.assignment_id
231 and paf2.primary_flag = 'Y'
232 and paf2.effective_start_date
233 <= l_session_date) and --l_year_end
234 paf.effective_end_date >= l_year_start and
235 paf.assignment_id = paa.assignment_id and
236 paa.payroll_action_id = p_arch_pactid and
237 pds.actual_termination_date is not null and
238 pds.period_of_service_id = paf.period_of_service_id;
239
240 cursor c_all_asg_range_term(p_arch_pactid number) is
241 select
242 paf.assignment_id assignment_id,
243 paa.assignment_action_id,
244 paa.payroll_action_id,
245 paf.effective_end_date
246 from
247 per_assignments_f paf,
248 pay_assignment_actions paa,
249 pay_population_ranges ppr,
250 per_periods_of_service PDS
251 where
252 -- paf.person_id >= stperson and
253 -- paf.person_id <= endperson and
254 ppr.payroll_action_id = pactid and
255 ppr.chunk_number = chunk and
256 ppr.person_id = paf.person_id and
257 paf.primary_flag = 'Y' and
258 paf.assignment_type = 'E' and
259 paf.business_group_id = l_bus_group_id and
260 paf.effective_start_date = (select max(paf2.effective_start_date)
261 from per_assignments_f paf2
262 where paf2.assignment_id= paf.assignment_id
263 and paf2.primary_flag = 'Y'
264 and paf2.effective_start_date
265 <= l_session_date) and --l_year_end
266 paf.effective_end_date >= l_year_start and
267 paf.assignment_id = paa.assignment_id and
268 paa.payroll_action_id = p_arch_pactid and
269 pds.actual_termination_date is not null and
270 pds.period_of_service_id = paf.period_of_service_id;
271
272 --Changes for bug 15886428 ends
273
274 /* Added this to run the report for Single Person enter at SRS level*/
275 cursor c_single_asg(p_arch_pactid number
276 ,p_per_id varchar2 ) is
277 select paf.assignment_id assignment_id,
278 paa.assignment_action_id,
279 paa.payroll_action_id,
280 paf.effective_end_date
281 from per_assignments_f paf,
282 pay_assignment_actions paa
283 where paf.person_id between stperson
284 and endperson
285 and paf.primary_flag = 'Y'
286 and paf.assignment_type = 'E'
287 and paf.business_group_id = l_bus_group_id
288 and paf.effective_start_date = (select max(paf2.effective_start_date)
289 from per_assignments_f paf2
290 where paf2.assignment_id= paf.assignment_id
291 and paf2.primary_flag = 'Y'
292 and paf2.effective_start_date
293 <= l_session_date)
294 and paf.effective_end_date >= l_year_start
295 and paa.payroll_action_id = p_arch_pactid
296 and paa.assignment_id = paf.assignment_id
297 and paa.serial_number = p_per_id;
298
299 --Changes for bug 15886428 starts
300 cursor c_single_asg_term(p_arch_pactid number
301 ,p_per_id varchar2 ) is
302 select paf.assignment_id assignment_id,
303 paa.assignment_action_id,
304 paa.payroll_action_id,
305 paf.effective_end_date
306 from per_assignments_f paf,
307 pay_assignment_actions paa,
308 per_periods_of_service pds
309 where paf.person_id between stperson
310 and endperson
311 and paf.primary_flag = 'Y'
312 and paf.assignment_type = 'E'
313 and paf.business_group_id = l_bus_group_id
314 and paf.effective_start_date = (select max(paf2.effective_start_date)
315 from per_assignments_f paf2
316 where paf2.assignment_id= paf.assignment_id
317 and paf2.primary_flag = 'Y'
318 and paf2.effective_start_date
319 <= l_session_date)
320 and paf.effective_end_date >= l_year_start
321 and paa.payroll_action_id = p_arch_pactid
322 and paa.assignment_id = paf.assignment_id
323 and paa.serial_number = p_per_id
324 and pds.actual_termination_date is not null
325 and pds.period_of_service_id = paf.period_of_service_id;
326 --Changes for bug 15886428 ends
327
328 /* Added this new cursor to fix bug#2135545 and this
329 will be used only if Assignment Set is passed for T4A reports */
330
331 cursor c_all_asg_in_asgset(p_arch_pactid number) is
332 select
333 paf.assignment_id assignment_id,
334 paa.assignment_action_id,
335 paa.payroll_action_id,
336 paf.effective_end_date
337 from
338 per_assignments_f paf,
339 pay_assignment_actions paa
340 where
341 paf.person_id >= stperson and
342 paf.person_id <= endperson and
343 paf.primary_flag = 'Y' and
344 paf.assignment_type = 'E' and
345 paf.business_group_id = l_bus_group_id and
346 paf.effective_start_date = (select max(paf2.effective_start_date)
347 from per_assignments_f paf2
348 where paf2.assignment_id= paf.assignment_id
349 and paf2.primary_flag = 'Y'
350 and paf2.effective_start_date
351 <= l_session_date) and --l_year_end
352 paf.effective_end_date >= l_year_start and
353 paf.assignment_id = paa.assignment_id and
354 paa.payroll_action_id = p_arch_pactid and
355 exists ( select 1 /* Selected Assignment Set */
356 from hr_assignment_set_amendments hasa
357 where hasa.assignment_set_id = l_asg_set_id
358 and hasa.assignment_id = paf.assignment_id
359 and upper(hasa.include_or_exclude) = 'I');
360
361 cursor c_all_asg_in_asgset_range(p_arch_pactid number) is
362 select
363 paf.assignment_id assignment_id,
364 paa.assignment_action_id,
365 paa.payroll_action_id,
366 paf.effective_end_date
367 from
368 per_assignments_f paf,
369 pay_assignment_actions paa,
370 pay_population_ranges ppr
371 where
372 -- paf.person_id >= stperson and
373 -- paf.person_id <= endperson and
374 ppr.payroll_action_id = pactid and
375 ppr.chunk_number = chunk and
376 ppr.person_id = paf.person_id and
377 paf.primary_flag = 'Y' and
378 paf.assignment_type = 'E' and
379 paf.business_group_id = l_bus_group_id and
380 paf.effective_start_date = (select max(paf2.effective_start_date)
381 from per_assignments_f paf2
382 where paf2.assignment_id= paf.assignment_id
383 and paf2.primary_flag = 'Y'
384 and paf2.effective_start_date
385 <= l_session_date) and --l_year_end
386 paf.effective_end_date >= l_year_start and
387 paf.assignment_id = paa.assignment_id and
388 paa.payroll_action_id = p_arch_pactid and
389 exists ( select 1 /* Selected Assignment Set */
390 from hr_assignment_set_amendments hasa
391 where hasa.assignment_set_id = l_asg_set_id
392 and hasa.assignment_id = paf.assignment_id
393 and upper(hasa.include_or_exclude) = 'I');
394
395
396 --rogittpa termination starts
397 cursor c_all_asg_in_asgset_term(p_arch_pactid number) is
398 select
399 paf.assignment_id assignment_id,
400 paa.assignment_action_id,
401 paa.payroll_action_id,
402 paf.effective_end_date
403 from
404 per_assignments_f paf,
405 pay_assignment_actions paa,
406 per_periods_of_service pds
407 where
408 paf.person_id >= stperson and
409 paf.person_id <= endperson and
410 paf.primary_flag = 'Y' and
411 paf.assignment_type = 'E' and
412 paf.business_group_id = l_bus_group_id and
413 paf.effective_start_date = (select max(paf2.effective_start_date)
414 from per_assignments_f paf2
415 where paf2.assignment_id= paf.assignment_id
416 and paf2.primary_flag = 'Y'
417 and paf2.effective_start_date
418 <= l_session_date) and --l_year_end
419 paf.effective_end_date >= l_year_start and
420 paf.assignment_id = paa.assignment_id and
421 paa.payroll_action_id = p_arch_pactid and
422 exists ( select 1 /* Selected Assignment Set */
423 from hr_assignment_set_amendments hasa
424 where hasa.assignment_set_id = l_asg_set_id
425 and hasa.assignment_id = paf.assignment_id
426 and upper(hasa.include_or_exclude) = 'I')
427 and pds.actual_termination_date is not null
428 and pds.period_of_service_id = paf.period_of_service_id;
429
430 cursor c_all_asg_in_asgset_range_term(p_arch_pactid number) is
431 select
432 paf.assignment_id assignment_id,
433 paa.assignment_action_id,
434 paa.payroll_action_id,
435 paf.effective_end_date
436 from
437 per_assignments_f paf,
438 pay_assignment_actions paa,
439 pay_population_ranges ppr,
440 per_periods_of_service pds
441 where
442 -- paf.person_id >= stperson and
443 -- paf.person_id <= endperson and
444 ppr.payroll_action_id = pactid and
445 ppr.chunk_number = chunk and
446 ppr.person_id = paf.person_id and
447 paf.primary_flag = 'Y' and
448 paf.assignment_type = 'E' and
449 paf.business_group_id = l_bus_group_id and
450 paf.effective_start_date = (select max(paf2.effective_start_date)
451 from per_assignments_f paf2
452 where paf2.assignment_id= paf.assignment_id
453 and paf2.primary_flag = 'Y'
454 and paf2.effective_start_date
455 <= l_session_date) and --l_year_end
456 paf.effective_end_date >= l_year_start and
457 paf.assignment_id = paa.assignment_id and
458 paa.payroll_action_id = p_arch_pactid and
459 exists ( select 1 /* Selected Assignment Set */
460 from hr_assignment_set_amendments hasa
461 where hasa.assignment_set_id = l_asg_set_id
462 and hasa.assignment_id = paf.assignment_id
463 and upper(hasa.include_or_exclude) = 'I')
464 and pds.actual_termination_date is not null
465 and pds.period_of_service_id = paf.period_of_service_id;
466 --Changes for bug 15886428 ends
467
468
469 lv_serial_number varchar2(30);
470 ln_arch_asgact_id number;
471 ln_arch_pact_id number;
472
473
474 begin
475
476 hr_utility.trace('begining of action creation 1 '||to_char(pactid));
477
478 /* get report type and effective date */
479
480 select
481 effective_date,
482 report_type,
483 -- Added for bug 10399514
484 report_qualifier,
485 report_category,
486 -- Added for bug 10399514
487 business_group_id,
488 legislative_parameters
489 into
490 l_effective_date,
491 l_report_type,
492 -- Added for bug 10399514
493 l_state,
494 l_report_cat,
495 -- Added for bug 10399514
496 l_bus_group_id,
497 l_legislative_parameters
498 from
499 pay_payroll_actions
500 where
501 payroll_action_id = pactid;
502
503 hr_utility.trace('begining of action creation 2 '||
504 to_char(l_bus_group_id));
505
506 hr_utility.trace('legislative parameters is '||l_legislative_parameters);
507 hr_utility.trace('Start Person ID = '||to_char(stperson));
508 hr_utility.trace('End Person ID = '||to_char(endperson));
509 hr_utility.trace('Chunk # = '||to_char(chunk));
510
511 l_year := pay_ca_t4a_reg.get_parameter('TAX_YEAR',l_legislative_parameters);
512 l_year_start := trunc(to_date(l_year,'YYYY'), 'Y');
513 l_year_end := add_months(trunc(to_date(l_year,'YYYY'), 'Y'),12) - 1;
514 l_asg_set_id := pay_ca_t4a_reg.get_parameter('ASG_SET_ID',
515 l_legislative_parameters);
516 lv_per_id := pay_ca_t4a_reg.get_parameter('PER_ID',l_legislative_parameters);
517 l_print_term := pay_ca_t4_reg.get_parameter('PRINT_TERM',l_legislative_parameters);
518
519 hr_utility.trace('begining of action creation 3 '||
520 l_year||to_char(l_year_start)||to_char(l_year_end));
521
522 l_t4areg_tax_unit_id := to_number(pay_ca_t4a_reg.get_parameter('GRE_ID',
523 l_legislative_parameters));
524 l_uid_caeoy_tax_year := get_user_entity_id('CAEOY_TAXATION_YEAR');
525 l_uid_caeoy_tax_unit_id := get_user_entity_id('CAEOY_TAX_UNIT_ID');
526
527 hr_utility.trace('begining of action creation 4 '
528 ||to_char(l_t4areg_tax_unit_id));
529
530 -- Code modification for bug 10399514 starts here
531 /* Initializing variable */
532 l_person_on := FALSE ;
533
534 Begin
535 select report_format
536 into l_report_format
537 from pay_report_format_mappings_f
538 where report_type = l_report_type
539 and report_qualifier = l_state
540 and report_category = l_report_cat ;
541 Exception
542 When Others Then
543 l_report_format := Null ;
544 End ;
545
546 l_person_on := pay_ac_utility.range_person_on( p_report_type => l_report_type,
547 p_report_format => l_report_format,
548 p_report_qualifier => l_state,
549 p_report_category => l_report_cat) ;
550
551 -- Code modification for bug 10399514 ends here
552
553
554 if l_t4areg_tax_unit_id is not null then
555
556 hr_utility.trace(' Tax Unit ID is passed = '|| to_char(l_t4areg_tax_unit_id));
557
558 open cur_gre;
559
560 fetch cur_gre
561 into l_arch_pactid,
562 l_session_date;
563
564 close cur_gre;
565
566
567 /* Added this validation to fix bug#2135545 */
568
569 if l_asg_set_id is not null then
570 if l_person_on then -- Added if for bug 10399514
571 if nvl(l_print_term,'N') = 'Y' then
572 open c_all_asg_in_asgset_range_term(l_arch_pactid);
573 hr_utility.trace('opening c_all_asg_in_asgset_range_term CURSOR');
574 else
575 open c_all_asg_in_asgset_range(l_arch_pactid);
576 hr_utility.trace('opening c_all_asg_in_asgset_range CURSOR');
577 end if;
578 else
579 if nvl(l_print_term,'N') = 'Y' then
580 open c_all_asg_in_asgset_term(l_arch_pactid);
581 hr_utility.trace('opening c_all_asg_in_asgset_term CURSOR');
582 else
583 open c_all_asg_in_asgset(l_arch_pactid);
584 hr_utility.trace('opening c_all_asg_in_asgset CURSOR');
585 end if;
586 end if;
587 elsif lv_per_id is not null then
588 if nvl(l_print_term,'N') = 'Y' then
589 open c_single_asg_term(l_arch_pactid, lv_per_id);
590 hr_utility.trace('opening c_single_asg_term CURSOR');
591 else
592 open c_single_asg(l_arch_pactid, lv_per_id);
593 hr_utility.trace('opening c_single_asg CURSOR');
594 end if;
595 else
596 if l_person_on then -- Added if for bug 10399514
597 if nvl(l_print_term,'N') = 'Y' then
598 open c_all_asg_range_term(l_arch_pactid);
599 hr_utility.trace('opening c_all_asg_range_term CURSOR');
600 else
601 open c_all_asg_range(l_arch_pactid);
602 hr_utility.trace('opening c_all_asg_range CURSOR');
603 end if;
604 else
605 if nvl(l_print_term,'N') = 'Y' then
606 open c_all_asg_term(l_arch_pactid);
607 hr_utility.trace('opening c_all_asg_term CURSOR');
608 else
609 open c_all_asg(l_arch_pactid);
610 hr_utility.trace('opening c_all_asg CURSOR');
611 end if;
612 end if;
613 end if;
614
615 loop
616
617 hr_utility.trace('l_t4areg_tax_unit_id is = ' ||
618 to_char(l_t4areg_tax_unit_id));
619
620 l_tax_unit_id := l_t4areg_tax_unit_id;
621
622
623 hr_utility.trace('begining of if condition 5 '||to_char(l_tax_unit_id));
624
625 /* Added this validation to fix bug#2135545 */
626 if l_asg_set_id is not null then
627 if l_person_on then -- Added if for bug 10399514
628 if nvl(l_print_term,'N') = 'Y' then
629 fetch c_all_asg_in_asgset_range_term into l_asg_id,
630 ln_arch_asgact_id,
631 ln_arch_pact_id,
632 l_effective_end_date;
633 hr_utility.trace('fetching from c_all_asg_in_asgset_range_term CURSOR');
634 exit when c_all_asg_in_asgset_range_term%notfound;
635 else
636 fetch c_all_asg_in_asgset_range into l_asg_id,
637 ln_arch_asgact_id,
638 ln_arch_pact_id,
639 l_effective_end_date;
640 hr_utility.trace('fetching from c_all_asg_in_asgset_range CURSOR');
641 exit when c_all_asg_in_asgset_range%notfound;
642 end if;
643 else
644 if nvl(l_print_term,'N') = 'Y' then
645 fetch c_all_asg_in_asgset_term into l_asg_id,
646 ln_arch_asgact_id,
647 ln_arch_pact_id,
648 l_effective_end_date;
649 hr_utility.trace('fetching from c_all_asg_in_asgset_term CURSOR');
650 exit when c_all_asg_in_asgset_term%notfound;
651 else
652 fetch c_all_asg_in_asgset into l_asg_id,
653 ln_arch_asgact_id,
654 ln_arch_pact_id,
655 l_effective_end_date;
656 hr_utility.trace('fetching from c_all_asg_in_asgset CURSOR');
657 exit when c_all_asg_in_asgset%notfound;
658 end if;
659 end if;
660 elsif lv_per_id is not null then
661 if nvl(l_print_term,'N') = 'Y' then
662 fetch c_single_asg_term into l_asg_id,
663 ln_arch_asgact_id,
664 ln_arch_pact_id,
665 l_effective_end_date;
666 hr_utility.trace('fetching from c_single_asg_term CURSOR');
667 exit when c_single_asg_term%notfound;
668 else
669 fetch c_single_asg into l_asg_id,
670 ln_arch_asgact_id,
671 ln_arch_pact_id,
672 l_effective_end_date;
673 hr_utility.trace('fetching from c_single_asg CURSOR');
674 exit when c_single_asg%notfound;
675 end if;
676 else
677 if l_person_on then -- Added if for bug 10399514
678 if nvl(l_print_term,'N') = 'Y' then
679 fetch c_all_asg_range_term into l_asg_id,
680 ln_arch_asgact_id,
681 ln_arch_pact_id,
682 l_effective_end_date;
683 hr_utility.trace('fetching from c_all_asg_range_term CURSOR');
684 exit when c_all_asg_range_term%notfound;
685 else
686 fetch c_all_asg_range into l_asg_id,
687 ln_arch_asgact_id,
688 ln_arch_pact_id,
689 l_effective_end_date;
690 hr_utility.trace('fetching from c_all_asg_range CURSOR');
691 exit when c_all_asg_range%notfound;
692 end if;
693 else
694 if nvl(l_print_term,'N') = 'Y' then
695 fetch c_all_asg_term into l_asg_id,
696 ln_arch_asgact_id,
697 ln_arch_pact_id,
698 l_effective_end_date;
699 hr_utility.trace('fetching from c_all_asg_term CURSOR');
700 exit when c_all_asg_term%notfound;
701 else
702 fetch c_all_asg into l_asg_id,
703 ln_arch_asgact_id,
704 ln_arch_pact_id,
705 l_effective_end_date;
706 hr_utility.trace('fetching from c_all_asg CURSOR');
707 exit when c_all_asg%notfound;
708 end if;
709 end if;
710 end if;
711
712 hr_utility.trace('Begining of if part loop for c_all_asg 10 '||
713 to_char(l_asg_id));
714
715
716 if (l_report_type = 'PAYCAT4APDF' and nvl(pay_us_employee_payslip_web.get_doc_eit('T4APDF',
717 'PRINT',
718 'ASSIGNMENT',
719 l_asg_id,
720 l_effective_end_date),'Y') = 'Y') or l_report_type <> 'PAYCAT4APDF' then
721 select pay_assignment_actions_s.nextval
722 into lockingactid
723 from dual;
724
725 hr_nonrun_asact.insact(lockingactid,
726 l_asg_id,
727 pactid,
728 chunk,
729 l_tax_unit_id);
730
731 hr_utility.trace('in if loop after calling hr_nonrun_asact.insact pkg 11 '||to_char(l_asg_id));
732
733 /* Added this to implement T4A Register and T4A Amendment Register
734 using the same report file */
735
736 lv_serial_number := lpad(to_char(ln_arch_asgact_id),14,0)||
737 lpad(to_char(ln_arch_pact_id),14,0);
738
739 /* Bug 4932662 - Negative balance is marked in serial_number for T4A PDF */
740 if (l_report_type = 'PAYCAT4APDF') then
741 lv_serial_number := lv_serial_number ||trim(pay_ca_archive_utils.get_archive_value(ln_arch_asgact_id ,
742 'CAEOY_T4A_NEGATIVE_BALANCE_EXISTS'));
743 end if;
744
745 hr_utility.trace('lv_serial_number :' ||lv_serial_number);
746
747 update pay_assignment_actions paa
748 set paa.serial_number = lv_serial_number
749 where paa.assignment_action_id = lockingactid;
750 end if;
751
752 end loop;
753
754 /* Added this validation to fix bug#2135545 */
755 if l_asg_set_id is not null then
756 if l_person_on then -- Added if for bug 10399514
757 if nvl(l_print_term,'N') = 'Y' then
758 close c_all_asg_in_asgset_range_term;
759 hr_utility.trace('closing c_all_asg_in_asgset_range_term CURSOR');
760 else
761 close c_all_asg_in_asgset_range;
762 hr_utility.trace('closing c_all_asg_in_asgset_range CURSOR');
763 end if;
764 else
765 if nvl(l_print_term,'N') = 'Y' then
766 close c_all_asg_in_asgset_term;
767 hr_utility.trace('closing c_all_asg_in_asgset_term CURSOR');
768 else
769 close c_all_asg_in_asgset;
770 hr_utility.trace('closing c_all_asg_in_asgset CURSOR');
771 end if;
772 end if;
773 elsif lv_per_id is not null then
774 if nvl(l_print_term,'N') = 'Y' then
775 close c_single_asg_term;
776 hr_utility.trace('closing c_single_asg_term CURSOR');
777 else
778 close c_single_asg;
779 hr_utility.trace('closing c_single_asg CURSOR');
780 end if;
781 else
782 if l_person_on then -- Added if for bug 10399514
783 if nvl(l_print_term,'N') = 'Y' then
784 close c_all_asg_range_term;
785 hr_utility.trace('closing c_all_asg_range_term CURSOR');
786 else
787 close c_all_asg_range;
788 hr_utility.trace('closing c_all_asg_range CURSOR');
789 end if;
790 else
791 if nvl(l_print_term,'N') = 'Y' then
792 close c_all_asg_term;
793 hr_utility.trace('closing c_all_asg_term CURSOR');
794 else
795 close c_all_asg;
796 hr_utility.trace('closing c_all_asg CURSOR');
797 end if;
798 end if;
799 end if;
800
801 hr_utility.trace('End of cursor c_all_asg 12');
802
803 else
804
805 hr_utility.trace('All the GREs will be processed !!!');
806
807 open c_all_gres;
808 loop
809
810 fetch c_all_gres
811 into
812 l_tax_unit_id,
813 l_arch_pactid,
814 l_session_date;
815
816 exit when c_all_gres%notfound;
817
818 hr_utility.trace('l_tax_unit_id = ' || to_char(l_tax_unit_id));
819 hr_utility.trace('l_arch_pactid = ' || to_char(l_arch_pactid));
820
821 if l_asg_set_id is not null then
822 if l_person_on then -- Added if for bug 10399514
823 if nvl(l_print_term,'N') = 'Y' then
824 open c_all_asg_in_asgset_range_term(l_arch_pactid);
825 hr_utility.trace('opening c_all_asg_in_asgset_range_term CURSOR');
826 else
827 open c_all_asg_in_asgset_range(l_arch_pactid);
828 hr_utility.trace('opening c_all_asg_in_asgset_range CURSOR');
829 end if;
830 else
831 if nvl(l_print_term,'N') = 'Y' then
832 open c_all_asg_in_asgset_term(l_arch_pactid);
833 hr_utility.trace('opening c_all_asg_in_asgset_term CURSOR');
834 else
835 open c_all_asg_in_asgset(l_arch_pactid);
836 hr_utility.trace('opening c_all_asg_in_asgset CURSOR');
837 end if;
838 end if;
839 elsif lv_per_id is not null then
840 if nvl(l_print_term,'N') = 'Y' then
841 open c_single_asg_term(l_arch_pactid, lv_per_id);
842 hr_utility.trace('opening c_single_asg_term CURSOR');
843 else
844 open c_single_asg(l_arch_pactid, lv_per_id);
845 hr_utility.trace('opening c_single_asg CURSOR');
846 end if;
847 else
848 if l_person_on then -- Added if for bug 10399514
849 if nvl(l_print_term,'N') = 'Y' then
850 open c_all_asg_range_term(l_arch_pactid);
851 hr_utility.trace('opening c_all_asg_range_term CURSOR');
852 else
853 open c_all_asg_range(l_arch_pactid);
854 hr_utility.trace('opening c_all_asg_range CURSOR');
855 end if;
856 else
857 if nvl(l_print_term,'N') = 'Y' then
858 open c_all_asg_term(l_arch_pactid);
859 hr_utility.trace('opening c_all_asg_term CURSOR');
860 else
861 open c_all_asg(l_arch_pactid);
862 hr_utility.trace('opening c_all_asg CURSOR');
863 end if;
864 end if;
865 end if;
866
867 loop
868
869 /* Added this validation to fix bug#2135545 */
870 if l_asg_set_id is not null then
871 if l_person_on then -- Added if for bug 10399514
872 if nvl(l_print_term,'N') = 'Y' then
873 fetch c_all_asg_in_asgset_range_term into l_asg_id,
874 ln_arch_asgact_id,
875 ln_arch_pact_id,
876 l_effective_end_date;
877 hr_utility.trace('fetching from c_all_asg_in_asgset_range_term CURSOR');
878 exit when c_all_asg_in_asgset_range_term%notfound;
879 else
880 fetch c_all_asg_in_asgset_range into l_asg_id,
881 ln_arch_asgact_id,
882 ln_arch_pact_id,
883 l_effective_end_date;
884 hr_utility.trace('fetching from c_all_asg_in_asgset_range CURSOR');
885 exit when c_all_asg_in_asgset_range%notfound;
886 end if;
887 else
888 if nvl(l_print_term,'N') = 'Y' then
889 fetch c_all_asg_in_asgset_term into l_asg_id,
890 ln_arch_asgact_id,
891 ln_arch_pact_id,
892 l_effective_end_date;
893 hr_utility.trace('fetching from c_all_asg_in_asgset_term CURSOR');
894 exit when c_all_asg_in_asgset_term%notfound;
895 else
896 fetch c_all_asg_in_asgset into l_asg_id,
897 ln_arch_asgact_id,
898 ln_arch_pact_id,
899 l_effective_end_date;
900 hr_utility.trace('fetching from c_all_asg_in_asgset CURSOR');
901 exit when c_all_asg_in_asgset%notfound;
902 end if;
903 end if;
904 elsif lv_per_id is not null then
905 if nvl(l_print_term,'N') = 'Y' then
906 fetch c_single_asg_term into l_asg_id,
907 ln_arch_asgact_id,
908 ln_arch_pact_id,
909 l_effective_end_date;
910 hr_utility.trace('fetching from c_single_asg_term CURSOR');
911 exit when c_single_asg_term%notfound;
912 else
913 fetch c_single_asg into l_asg_id,
914 ln_arch_asgact_id,
915 ln_arch_pact_id,
916 l_effective_end_date;
917 hr_utility.trace('fetching from c_single_asg CURSOR');
918 exit when c_single_asg%notfound;
919 end if;
920 else
921 if l_person_on then -- Added if for bug 10399514
922 if nvl(l_print_term,'N') = 'Y' then
923 fetch c_all_asg_range_term into l_asg_id,
924 ln_arch_asgact_id,
925 ln_arch_pact_id,
926 l_effective_end_date;
927 hr_utility.trace('fetching from c_all_asg_range_term CURSOR');
928 exit when c_all_asg_range_term%notfound;
929 else
930 fetch c_all_asg_range into l_asg_id,
931 ln_arch_asgact_id,
932 ln_arch_pact_id,
933 l_effective_end_date;
934 hr_utility.trace('fetching from c_all_asg_range CURSOR');
935 exit when c_all_asg_range%notfound;
936 end if;
937 else
938 if nvl(l_print_term,'N') = 'Y' then
939 fetch c_all_asg_term into l_asg_id,
940 ln_arch_asgact_id,
941 ln_arch_pact_id,
942 l_effective_end_date;
943 hr_utility.trace('fetching from c_all_asg_term CURSOR');
944 exit when c_all_asg_term%notfound;
945 else
946 fetch c_all_asg into l_asg_id,
947 ln_arch_asgact_id,
948 ln_arch_pact_id,
949 l_effective_end_date;
950 hr_utility.trace('fetching from c_all_asg CURSOR');
951 exit when c_all_asg%notfound;
952 end if;
953 end if;
954 end if;
955
956 hr_utility.trace('Begining of if part loop for c_all_asg 20 '||
957 to_char(l_asg_id));
958
959
960 if (l_report_type = 'PAYCAT4APDF' and nvl(pay_us_employee_payslip_web.get_doc_eit('T4APDF',
961 'PRINT',
962 'ASSIGNMENT',
963 l_asg_id,
964 l_effective_end_date),'Y') = 'Y') or l_report_type <> 'PAYCAT4APDF' then
965 select pay_assignment_actions_s.nextval
966 into lockingactid
967 from dual;
968
969 hr_nonrun_asact.insact(lockingactid,
970 l_asg_id,
971 pactid,
972 chunk,
973 l_tax_unit_id);
974
975 hr_utility.trace('in if loop after calling hr_nonrun_asact.insact pkg 9 '||
976 to_char(l_asg_id));
977
978 /* Added this to implement T4A Register and T4A Amendment Register
979 using the same report file */
980
981 lv_serial_number := lpad(to_char(ln_arch_asgact_id),14,0)||
982 lpad(to_char(ln_arch_pact_id),14,0);
983
984 /* Bug 4932662 - Negative balance is marked in serial_number for T4A PDF */
985 if (l_report_type = 'PAYCAT4APDF') then
986 lv_serial_number := lv_serial_number ||trim(pay_ca_archive_utils.get_archive_value(ln_arch_asgact_id ,
987 'CAEOY_T4A_NEGATIVE_BALANCE_EXISTS'));
988 end if;
989
990 hr_utility.trace('lv_serial_number :' ||lv_serial_number);
991
992 update pay_assignment_actions paa
993 set paa.serial_number = lv_serial_number
994 where paa.assignment_action_id = lockingactid;
995 end if;
996
997 end loop;
998
999 /* Added this validation to fix bug#2135545 */
1000 if l_asg_set_id is not null then
1001 if l_person_on then -- Added if for bug 10399514
1002 if nvl(l_print_term,'N') = 'Y' then
1003 close c_all_asg_in_asgset_range_term;
1004 hr_utility.trace('closing c_all_asg_in_asgset_range_term CURSOR');
1005 else
1006 close c_all_asg_in_asgset_range;
1007 hr_utility.trace('closing c_all_asg_in_asgset_range CURSOR');
1008 end if;
1009 else
1010 if nvl(l_print_term,'N') = 'Y' then
1011 close c_all_asg_in_asgset_term;
1012 hr_utility.trace('closing c_all_asg_in_asgset_term CURSOR');
1013 else
1014 close c_all_asg_in_asgset;
1015 hr_utility.trace('closing c_all_asg_in_asgset CURSOR');
1016 end if;
1017 end if;
1018 elsif lv_per_id is not null then
1019 if nvl(l_print_term,'N') = 'Y' then
1020 close c_single_asg_term;
1021 hr_utility.trace('closing c_single_asg_term CURSOR');
1022 else
1023 close c_single_asg;
1024 hr_utility.trace('closing c_single_asg CURSOR');
1025 end if;
1026 else
1027 if l_person_on then -- Added if for bug 10399514
1028 if nvl(l_print_term,'N') = 'Y' then
1029 close c_all_asg_range_term;
1030 hr_utility.trace('closing c_all_asg_range_term CURSOR');
1031 else
1032 close c_all_asg_range;
1033 hr_utility.trace('closing c_all_asg_range CURSOR');
1034 end if;
1035 else
1036 if nvl(l_print_term,'N') = 'Y' then
1037 close c_all_asg_term;
1038 hr_utility.trace('closing c_all_asg_term CURSOR');
1039 else
1040 close c_all_asg;
1041 hr_utility.trace('closing c_all_asg CURSOR');
1042 end if;
1043 end if;
1044 end if;
1045
1046 end loop;
1047
1048 close c_all_gres;
1049
1050 end if;
1051
1052 hr_utility.trace('End of If Condition for Loop 13');
1053 end action_creation;
1054
1055 ---------------------------------- sort_action ---------------------------------
1056
1057 procedure sort_action
1058 (
1059 payactid in varchar2, /* payroll action id */
1060 sqlstr in out nocopy varchar2, /* string holding the sql statement */
1061 len out nocopy number /* length of the sql string */
1062 ) is
1063 begin
1064 hr_utility.trace('Start of Sort_Action 1');
1065
1066
1067 sqlstr := 'select paa1.rowid
1068 from hr_all_organization_units hou,
1069 hr_all_organization_units hou1,
1070 hr_locations_all loc,
1071 per_all_people_f ppf,
1072 per_all_assignments_f paf,
1073 pay_assignment_actions paa1,
1074 pay_payroll_actions ppa1
1075 where ppa1.payroll_action_id = :pactid
1076 and paa1.payroll_action_id = ppa1.payroll_action_id
1077 and paa1.assignment_id = paf.assignment_id
1078 and paf.effective_start_date =
1079 (select max(paf2.effective_start_date)
1080 from per_all_assignments_f paf2
1081 where paf2.assignment_id= paf.assignment_id
1082 and paf2.effective_start_date
1083 <= ppa1.effective_date)
1084 and paf.effective_end_date >= ppa1.start_date
1085 and paf.assignment_type = ''E''
1086 and hou1.organization_id = paa1.tax_unit_id
1087 and hou.organization_id = paf.organization_id
1088 and loc.location_id = paf.location_id
1089 and ppf.person_id = paf.person_id
1090 and ppf.effective_start_date =
1091 (select max(ppf2.effective_start_date)
1092 from per_all_people_f ppf2
1093 where ppf2.person_id= paf.person_id
1094 and ppf2.effective_start_date
1095 <= ppa1.effective_date)
1096 and ppf.effective_end_date >= ppa1.start_date
1097 order by
1098 decode(pay_ca_t4_reg.get_parameter
1099 (''P_S1'',ppa1.legislative_parameters),
1100 ''GRE'',hou1.name,
1101 ''ORGANIZATION'',hou.name,
1102 ''LOCATION'',loc.location_code,null),
1103 decode(pay_ca_t4_reg.get_parameter(''P_S2'',
1104 ppa1.legislative_parameters),
1105 ''GRE'',hou1.name,
1106 ''ORGANIZATION'',hou.name,
1107 ''LOCATION'',loc.location_code,null),
1108 decode(pay_ca_t4_reg.get_parameter(''P_S3'',
1109 ppa1.legislative_parameters),
1110 ''GRE'',hou1.name,
1111 ''ORGANIZATION'',hou.name,
1112 ''LOCATION'',loc.location_code,null),
1113 ppf.last_name,first_name
1114 for update of paa1.assignment_action_id';
1115
1116 len := length(sqlstr); -- return the length of the string.
1117 hr_utility.trace('End of Sort_Action 2');
1118 end sort_action;
1119 --
1120 ------------------------------ get_parameter -------------------------------
1121 function get_parameter(name in varchar2,
1122 parameter_list varchar2) return varchar2
1123 is
1124 start_ptr number;
1125 end_ptr number;
1126 token_val pay_payroll_actions.legislative_parameters%type;
1127 par_value pay_payroll_actions.legislative_parameters%type;
1128 begin
1129 --
1130 token_val := name||'=';
1131 --
1132 start_ptr := instr(parameter_list, token_val) + length(token_val);
1133 end_ptr := instr(parameter_list, ' ',start_ptr);
1134 --
1135 /* if there is no spaces use then length of the string */
1136 if end_ptr = 0 then
1137 end_ptr := length(parameter_list)+1;
1138 end if;
1139 --
1140 /* Did we find the token */
1141 if instr(parameter_list, token_val) = 0 then
1142 par_value := NULL;
1143 else
1144 par_value := substr(parameter_list, start_ptr, end_ptr - start_ptr);
1145 end if;
1146 --
1147 return par_value;
1148 --
1149 end get_parameter;
1150
1151 function get_label(p_lookup_type in VARCHAR2,
1152 p_lookup_code in VARCHAR2)
1153 return VARCHAR2 is
1154 cursor csr_label_meaning is
1155 select meaning
1156 from hr_lookups
1157 where lookup_type = p_lookup_type
1158 and lookup_code = p_lookup_code;
1159
1160 l_label_meaning varchar2(80);
1161 begin
1162 open csr_label_meaning;
1163
1164 fetch csr_label_meaning into l_label_meaning;
1165 if csr_label_meaning%NOTFOUND then
1166 l_label_meaning := NULL;
1167 end if;
1168 close csr_label_meaning;
1169
1170 return l_label_meaning;
1171 end get_label;
1172
1173
1174 function get_label(p_lookup_type in VARCHAR2,
1175 p_lookup_code in VARCHAR2,
1176 p_person_language in varchar2)
1177 return VARCHAR2 is
1178 cursor csr_label_meaning is
1179 select 1 ord, meaning
1180 from fnd_lookup_values
1181 where lookup_type = p_lookup_type
1182 and lookup_code = p_lookup_code
1183 and ( ( p_person_language is null and language = 'US' ) or
1184 ( p_person_language is not null and language = p_person_language ) )
1185 union all
1186 select 2 ord, meaning
1187 from fnd_lookup_values
1188 where lookup_type = p_lookup_type
1189 and lookup_code = p_lookup_code
1190 and ( language = 'US' and p_person_language is not null
1191 and language <> p_person_language )
1192 order by 1;
1193
1194 l_order number;
1195 l_label_meaning varchar2(80);
1196 begin
1197 open csr_label_meaning;
1198
1199 fetch csr_label_meaning into l_order, l_label_meaning;
1200 if csr_label_meaning%NOTFOUND then
1201 l_label_meaning := NULL;
1202 end if;
1203 close csr_label_meaning;
1204
1205 return l_label_meaning;
1206 end get_label;
1207
1208 end pay_ca_t4a_reg;