[Home] [Help]
PACKAGE BODY: APPS.PAY_CA_T4A_REG
Source
1 PACKAGE BODY pay_ca_t4a_reg AS
2 /* $Header: pycat4ar.pkb 120.1 2005/06/15 16:42:44 ssouresr noship $ */
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 l_legislative_parameters varchar2(240);
100 l_uid_caeoy_tax_year ff_user_entities.user_entity_id%TYPE;
101 l_uid_caeoy_tax_unit_id ff_user_entities.user_entity_id%TYPE;
102 l_arch_pactid pay_payroll_actions.payroll_action_id%TYPE;
103 l_session_date pay_payroll_actions.effective_date%TYPE;
104 lv_per_id varchar2(30);
105
106 cursor c_all_gres is
107 select distinct to_number(fai2.value) tax_unit_id,
108 payroll_action_id arch_pactid,
109 ppa.effective_date
110 from pay_payroll_actions ppa,
111 ff_archive_items fai1,
112 ff_archive_items fai2
113 where fai1.user_entity_id = l_uid_caeoy_tax_year
114 and fai1.value = l_year
115 and fai2.context1 = fai1.context1
116 and fai2.user_entity_id = l_uid_caeoy_tax_unit_id
117 and ppa.payroll_action_id = fai1.context1
118 and ppa.report_type = 'T4A'
119 and ppa.report_qualifier = 'CAEOY'
120 and ppa.report_category = 'CAEOY'
121 and ppa.action_type = 'X'
122 and ppa.business_group_id+0 = l_bus_group_id;
123
124 cursor cur_gre is
125 select payroll_action_id arch_pactid,
126 ppa.effective_date
127 from pay_payroll_actions ppa,
128 ff_archive_items fai1,
129 ff_archive_items fai2
130 where fai1.user_entity_id = l_uid_caeoy_tax_year
131 and fai1.value = l_year
132 and ppa.payroll_action_id = fai1.context1
133 and ppa.report_type = 'T4A'
134 and ppa.report_qualifier = 'CAEOY'
135 and ppa.report_category = 'CAEOY'
136 and ppa.action_type = 'X'
137 and ppa.business_group_id + 0 = l_bus_group_id
138 and fai1.context1 = fai2.context1
139 and fai2.user_entity_id = l_uid_caeoy_tax_unit_id
140 and fai2.value = to_char(l_t4areg_tax_unit_id);
141
142 cursor c_all_asg(p_arch_pactid number) is
143 select
144 paf.assignment_id assignment_id,
145 paa.assignment_action_id,
146 paa.payroll_action_id
147 from
148 per_assignments_f paf,
149 pay_assignment_actions paa
150 where
151 paf.person_id >= stperson and
152 paf.person_id <= endperson and
153 paf.primary_flag = 'Y' and
154 paf.assignment_type = 'E' and
155 paf.business_group_id = l_bus_group_id and
156 paf.effective_start_date = (select max(paf2.effective_start_date)
157 from per_assignments_f paf2
158 where paf2.assignment_id= paf.assignment_id
159 and paf2.primary_flag = 'Y'
160 and paf2.effective_start_date
161 <= l_session_date) and --l_year_end
162 paf.effective_end_date >= l_year_start and
163 paf.assignment_id = paa.assignment_id and
164 paa.payroll_action_id = p_arch_pactid;
165
166 /* Added this to run the report for Single Person enter at SRS level*/
167 cursor c_single_asg(p_arch_pactid number
168 ,p_per_id varchar2 ) is
169 select paf.assignment_id assignment_id,
170 paa.assignment_action_id,
171 paa.payroll_action_id
172 from per_assignments_f paf,
173 pay_assignment_actions paa
174 where paf.person_id between stperson
175 and endperson
176 and paf.primary_flag = 'Y'
177 and paf.assignment_type = 'E'
178 and paf.business_group_id = l_bus_group_id
179 and paf.effective_start_date = (select max(paf2.effective_start_date)
180 from per_assignments_f paf2
181 where paf2.assignment_id= paf.assignment_id
182 and paf2.primary_flag = 'Y'
183 and paf2.effective_start_date
184 <= l_session_date)
185 and paf.effective_end_date >= l_year_start
186 and paa.payroll_action_id = p_arch_pactid
187 and paa.assignment_id = paf.assignment_id
188 and paa.serial_number = p_per_id;
189
190 /* Added this new cursor to fix bug#2135545 and this
191 will be used only if Assignment Set is passed for T4A reports */
192
193 cursor c_all_asg_in_asgset(p_arch_pactid number) is
194 select
195 paf.assignment_id assignment_id,
196 paa.assignment_action_id,
197 paa.payroll_action_id
198 from
199 per_assignments_f paf,
200 pay_assignment_actions paa
201 where
202 paf.person_id >= stperson and
203 paf.person_id <= endperson and
204 paf.primary_flag = 'Y' and
205 paf.assignment_type = 'E' and
206 paf.business_group_id = l_bus_group_id and
207 paf.effective_start_date = (select max(paf2.effective_start_date)
208 from per_assignments_f paf2
209 where paf2.assignment_id= paf.assignment_id
210 and paf2.primary_flag = 'Y'
211 and paf2.effective_start_date
212 <= l_session_date) and --l_year_end
213 paf.effective_end_date >= l_year_start and
214 paf.assignment_id = paa.assignment_id and
215 paa.payroll_action_id = p_arch_pactid and
216 exists ( select 1 /* Selected Assignment Set */
217 from hr_assignment_set_amendments hasa
218 where hasa.assignment_set_id = l_asg_set_id
219 and hasa.assignment_id = paf.assignment_id
220 and upper(hasa.include_or_exclude) = 'I');
221
222 lv_serial_number varchar2(30);
223 ln_arch_asgact_id number;
224 ln_arch_pact_id number;
225
226
227 begin
228
229 hr_utility.trace('begining of action creation 1 '||to_char(pactid));
230
231 /* get report type and effective date */
232
233 select
234 effective_date,
235 report_type,
236 business_group_id,
237 legislative_parameters
238 into
239 l_effective_date,
240 l_report_type,
241 l_bus_group_id,
242 l_legislative_parameters
243 from
244 pay_payroll_actions
245 where
246 payroll_action_id = pactid;
247
248 hr_utility.trace('begining of action creation 2 '||
249 to_char(l_bus_group_id));
250
251 hr_utility.trace('legislative parameters is '||l_legislative_parameters);
252 hr_utility.trace('Start Person ID = '||to_char(stperson));
253 hr_utility.trace('End Person ID = '||to_char(endperson));
254 hr_utility.trace('Chunk # = '||to_char(chunk));
255
256 l_year := pay_ca_t4a_reg.get_parameter('TAX_YEAR',l_legislative_parameters);
257 l_year_start := trunc(to_date(l_year,'YYYY'), 'Y');
258 l_year_end := add_months(trunc(to_date(l_year,'YYYY'), 'Y'),12) - 1;
259 l_asg_set_id := pay_ca_t4a_reg.get_parameter('ASG_SET_ID',
260 l_legislative_parameters);
261 lv_per_id := pay_ca_t4a_reg.get_parameter('PER_ID',l_legislative_parameters);
262
263 hr_utility.trace('begining of action creation 3 '||
264 l_year||to_char(l_year_start)||to_char(l_year_end));
265
266 l_t4areg_tax_unit_id := to_number(pay_ca_t4a_reg.get_parameter('GRE_ID',
267 l_legislative_parameters));
268 l_uid_caeoy_tax_year := get_user_entity_id('CAEOY_TAXATION_YEAR');
269 l_uid_caeoy_tax_unit_id := get_user_entity_id('CAEOY_TAX_UNIT_ID');
270
271 hr_utility.trace('begining of action creation 4 '
272 ||to_char(l_t4areg_tax_unit_id));
273
274 if l_t4areg_tax_unit_id is not null then
275
276 hr_utility.trace(' Tax Unit ID is passed = '|| to_char(l_t4areg_tax_unit_id));
277
278 open cur_gre;
279
280 fetch cur_gre
281 into l_arch_pactid,
282 l_session_date;
283
284 close cur_gre;
285
286 /* Added this validation to fix bug#2135545 */
287
288 if l_asg_set_id is not null then
289 open c_all_asg_in_asgset(l_arch_pactid);
290 elsif lv_per_id is not null then
291 open c_single_asg(l_arch_pactid, lv_per_id);
292 else
293 open c_all_asg(l_arch_pactid);
294 end if;
295
296 loop
297
298 hr_utility.trace('l_t4areg_tax_unit_id is = ' ||
299 to_char(l_t4areg_tax_unit_id));
300
301 l_tax_unit_id := l_t4areg_tax_unit_id;
302
303
304 hr_utility.trace('begining of if condition 5 '||to_char(l_tax_unit_id));
305
306 /* Added this validation to fix bug#2135545 */
307 if l_asg_set_id is not null then
308 fetch c_all_asg_in_asgset into l_asg_id,
309 ln_arch_asgact_id,
310 ln_arch_pact_id;
311 exit when c_all_asg_in_asgset%notfound;
312 elsif lv_per_id is not null then
313 fetch c_single_asg into l_asg_id,
314 ln_arch_asgact_id,
315 ln_arch_pact_id;
316 exit when c_single_asg%notfound;
317 else
318 fetch c_all_asg into l_asg_id,
319 ln_arch_asgact_id,
320 ln_arch_pact_id;
321 exit when c_all_asg%notfound;
322 end if;
323
324 hr_utility.trace('Begining of if part loop for c_all_asg 10 '||
325 to_char(l_asg_id));
326
327 select pay_assignment_actions_s.nextval
328 into lockingactid
329 from dual;
330
331 hr_nonrun_asact.insact(lockingactid,
332 l_asg_id,
333 pactid,
334 chunk,
335 l_tax_unit_id);
336
337 hr_utility.trace('in if loop after calling hr_nonrun_asact.insact pkg 11 '||to_char(l_asg_id));
338
339 /* Added this to implement T4A Register and T4A Amendment Register
340 using the same report file */
341
342 lv_serial_number := lpad(to_char(ln_arch_asgact_id),14,0)||
343 lpad(to_char(ln_arch_pact_id),14,0);
344
345 hr_utility.trace('lv_serial_number :' ||lv_serial_number);
346
347 update pay_assignment_actions paa
348 set paa.serial_number = lv_serial_number
349 where paa.assignment_action_id = lockingactid;
350
351 end loop;
352
353 /* Added this validation to fix bug#2135545 */
354 if l_asg_set_id is not null then
355 close c_all_asg_in_asgset;
356 elsif lv_per_id is not null then
357 close c_single_asg;
358 else
359 close c_all_asg;
360 end if;
361
362 hr_utility.trace('End of cursor c_all_asg 12');
363
364 else
365
366 hr_utility.trace('All the GREs will be processed !!!');
367
368 open c_all_gres;
369 loop
370
371 fetch c_all_gres
372 into
373 l_tax_unit_id,
374 l_arch_pactid,
375 l_session_date;
376
377 exit when c_all_gres%notfound;
378
379 hr_utility.trace('l_tax_unit_id = ' || to_char(l_tax_unit_id));
380 hr_utility.trace('l_arch_pactid = ' || to_char(l_arch_pactid));
381
382 if l_asg_set_id is not null then
383 open c_all_asg_in_asgset(l_arch_pactid);
384 elsif lv_per_id is not null then
385 open c_single_asg (l_arch_pactid, lv_per_id);
386 else
387 open c_all_asg(l_arch_pactid);
388 end if;
389
390 loop
391
392 /* Added this validation to fix bug#2135545 */
393 if l_asg_set_id is not null then
394 fetch c_all_asg_in_asgset into l_asg_id,
395 ln_arch_asgact_id,
396 ln_arch_pact_id;
397 exit when c_all_asg_in_asgset%notfound;
398 elsif lv_per_id is not null then
399 fetch c_single_asg into l_asg_id,
400 ln_arch_asgact_id,
401 ln_arch_pact_id;
402 exit when c_single_asg%notfound;
403 else
404 hr_utility.trace(' Fetching c_all_asg !!!');
405 fetch c_all_asg into l_asg_id,
406 ln_arch_asgact_id,
407 ln_arch_pact_id;
411 select pay_assignment_actions_s.nextval
408 exit when c_all_asg%notfound;
409 end if;
410
412 into lockingactid
413 from dual;
414
415 hr_nonrun_asact.insact(lockingactid,
416 l_asg_id,
417 pactid,
418 chunk,
419 l_tax_unit_id);
420
421 hr_utility.trace('in if loop after calling hr_nonrun_asact.insact pkg 9 '||
422 to_char(l_asg_id));
423
424 /* Added this to implement T4A Register and T4A Amendment Register
425 using the same report file */
426
427 lv_serial_number := lpad(to_char(ln_arch_asgact_id),14,0)||
428 lpad(to_char(ln_arch_pact_id),14,0);
429
430 hr_utility.trace('lv_serial_number :' ||lv_serial_number);
431
432 update pay_assignment_actions paa
433 set paa.serial_number = lv_serial_number
434 where paa.assignment_action_id = lockingactid;
435
436 end loop;
437
438 /* Added this validation to fix bug#2135545 */
439 if l_asg_set_id is not null then
440 close c_all_asg_in_asgset;
441 elsif lv_per_id is not null then
442 close c_single_asg;
443 else
444 close c_all_asg;
445 end if;
446
447 end loop;
448
449 close c_all_gres;
450
451 end if;
452
453 hr_utility.trace('End of If Condition for Loop 13');
454 end action_creation;
455
456 ---------------------------------- sort_action ---------------------------------
457
458 procedure sort_action
459 (
460 payactid in varchar2, /* payroll action id */
461 sqlstr in out nocopy varchar2, /* string holding the sql statement */
462 len out nocopy number /* length of the sql string */
463 ) is
464 begin
465 hr_utility.trace('Start of Sort_Action 1');
466
467
468 sqlstr := 'select paa1.rowid
469 from hr_all_organization_units hou,
470 hr_all_organization_units hou1,
471 hr_locations_all loc,
472 per_all_people_f ppf,
473 per_all_assignments_f paf,
474 pay_assignment_actions paa1,
475 pay_payroll_actions ppa1
476 where ppa1.payroll_action_id = :pactid
477 and paa1.payroll_action_id = ppa1.payroll_action_id
478 and paa1.assignment_id = paf.assignment_id
479 and paf.effective_start_date =
480 (select max(paf2.effective_start_date)
481 from per_all_assignments_f paf2
482 where paf2.assignment_id= paf.assignment_id
483 and paf2.effective_start_date
484 <= ppa1.effective_date)
485 and paf.effective_end_date >= ppa1.start_date
486 and paf.assignment_type = ''E''
487 and hou1.organization_id = paa1.tax_unit_id
488 and hou.organization_id = paf.organization_id
489 and loc.location_id = paf.location_id
493 from per_all_people_f ppf2
490 and ppf.person_id = paf.person_id
491 and ppf.effective_start_date =
492 (select max(ppf2.effective_start_date)
494 where ppf2.person_id= paf.person_id
495 and ppf2.effective_start_date
496 <= ppa1.effective_date)
497 and ppf.effective_end_date >= ppa1.start_date
498 order by
499 decode(pay_ca_t4_reg.get_parameter
500 (''P_S1'',ppa1.legislative_parameters),
501 ''GRE'',hou1.name,
502 ''ORGANIZATION'',hou.name,
503 ''LOCATION'',loc.location_code,null),
504 decode(pay_ca_t4_reg.get_parameter(''P_S2'',
505 ppa1.legislative_parameters),
506 ''GRE'',hou1.name,
507 ''ORGANIZATION'',hou.name,
508 ''LOCATION'',loc.location_code,null),
509 decode(pay_ca_t4_reg.get_parameter(''P_S3'',
510 ppa1.legislative_parameters),
511 ''GRE'',hou1.name,
512 ''ORGANIZATION'',hou.name,
513 ''LOCATION'',loc.location_code,null),
514 ppf.last_name,first_name
515 for update of paa1.assignment_action_id';
516
517 len := length(sqlstr); -- return the length of the string.
518 hr_utility.trace('End of Sort_Action 2');
519 end sort_action;
520 --
521 ------------------------------ get_parameter -------------------------------
522 function get_parameter(name in varchar2,
523 parameter_list varchar2) return varchar2
524 is
525 start_ptr number;
526 end_ptr number;
527 token_val pay_payroll_actions.legislative_parameters%type;
528 par_value pay_payroll_actions.legislative_parameters%type;
529 begin
530 --
531 token_val := name||'=';
532 --
533 start_ptr := instr(parameter_list, token_val) + length(token_val);
534 end_ptr := instr(parameter_list, ' ',start_ptr);
535 --
536 /* if there is no spaces use then length of the string */
537 if end_ptr = 0 then
538 end_ptr := length(parameter_list)+1;
539 end if;
540 --
541 /* Did we find the token */
542 if instr(parameter_list, token_val) = 0 then
543 par_value := NULL;
544 else
545 par_value := substr(parameter_list, start_ptr, end_ptr - start_ptr);
546 end if;
547 --
548 return par_value;
549 --
550 end get_parameter;
551
552 function get_label(p_lookup_type in VARCHAR2,
553 p_lookup_code in VARCHAR2)
554 return VARCHAR2 is
555 cursor csr_label_meaning is
556 select meaning
557 from hr_lookups
558 where lookup_type = p_lookup_type
559 and lookup_code = p_lookup_code;
560
561 l_label_meaning varchar2(80);
562 begin
563 open csr_label_meaning;
564
565 fetch csr_label_meaning into l_label_meaning;
566 if csr_label_meaning%NOTFOUND then
567 l_label_meaning := NULL;
568 end if;
569 close csr_label_meaning;
570
571 return l_label_meaning;
572 end get_label;
573
574
575 function get_label(p_lookup_type in VARCHAR2,
576 p_lookup_code in VARCHAR2,
577 p_person_language in varchar2)
578 return VARCHAR2 is
579 cursor csr_label_meaning is
580 select 1 ord, meaning
581 from fnd_lookup_values
582 where lookup_type = p_lookup_type
583 and lookup_code = p_lookup_code
584 and ( ( p_person_language is null and language = 'US' ) or
585 ( p_person_language is not null and language = p_person_language ) )
586 union all
587 select 2 ord, meaning
588 from fnd_lookup_values
589 where lookup_type = p_lookup_type
590 and lookup_code = p_lookup_code
591 and ( language = 'US' and p_person_language is not null
592 and language <> p_person_language )
593 order by 1;
594
595 l_order number;
596 l_label_meaning varchar2(80);
597 begin
598 open csr_label_meaning;
599
600 fetch csr_label_meaning into l_order, l_label_meaning;
601 if csr_label_meaning%NOTFOUND then
602 l_label_meaning := NULL;
603 end if;
604 close csr_label_meaning;
605
606 return l_label_meaning;
607 end get_label;
608
609 end pay_ca_t4a_reg;