[Home] [Help]
PACKAGE BODY: APPS.PAY_CA_T4_REG
Source
1 PACKAGE BODY pay_ca_t4_reg AS
2 /* $Header: pycat4rg.pkb 120.3.12000000.1 2007/01/17 17:29:50 appldev noship $ */
3 function get_user_entity_id(p_user_name varchar2) return number is
4
5 begin
6
7 declare
8
9 cursor cur_user_entity_id is
10 select user_entity_id
11 from ff_database_items
12 where user_name = p_user_name;
13
14 l_user_entity_id ff_database_items.user_entity_id%TYPE;
15
16 begin
17
18 open cur_user_entity_id;
19
20 fetch cur_user_entity_id
21 into l_user_entity_id;
22
23 close cur_user_entity_id;
24
25 return l_user_entity_id;
26
27 end;
28 end;
29
30 ----------------------------------- range_cursor ----------------------------------
31
32 procedure range_cursor (pactid in number, sqlstr out nocopy varchar2) is
33 l_payroll_id number;
34 leg_param pay_payroll_actions.legislative_parameters%type;
35
36 l_uid_caeoy_tax_year number;
37 l_uid_caeoy_tax_unit_id number;
38 l_uid_caeoy_prov_of_emp number;
39 l_uid_caeoy_person_id number;
40 --
41 begin
42
43 --hr_utility.trace_on('Y','ORACLE');
44 hr_utility.trace('begining of range_cursor 1 ');
45
46 select legislative_parameters
47 into leg_param
48 from pay_payroll_actions ppa
49 where ppa.payroll_action_id = pactid;
50
51 l_uid_caeoy_tax_year := get_user_entity_id('CAEOY_TAXATION_YEAR');
52 l_uid_caeoy_tax_unit_id := get_user_entity_id('CAEOY_TAX_UNIT_ID');
53 l_uid_caeoy_prov_of_emp :=
54 get_user_entity_id('CAEOY_PROVINCE_OF_EMPLOYMENT');
55 l_uid_caeoy_person_id := get_user_entity_id('CAEOY_PERSON_ID');
56
57
58
59 /* pay reg code */
60
61 sqlstr := 'select distinct to_number(fai4.value)
62 from ff_archive_items fai1,
63 ff_archive_items fai2,
64 ff_archive_items fai3,
65 ff_archive_items fai4,
66 pay_payroll_actions ppa,
67 pay_assignment_actions paa
68 where ppa.payroll_action_id = :payroll_action_id
69 and fai1.user_entity_id = ' || l_uid_caeoy_tax_year ||
70 ' and fai1.value =
71 nvl(pay_ca_t4_reg.get_parameter(''TAX_YEAR'',
72 ppa.legislative_parameters),fai1.value)
73 and fai1.context1 = paa.payroll_action_id
74 and fai2.user_entity_id = ' || l_uid_caeoy_tax_unit_id ||
75 ' and fai2.value =
76 nvl(pay_ca_t4_reg.get_parameter(''GRE_ID'',
77 ppa.legislative_parameters),fai2.value)
78 and fai2.context1 = paa.payroll_action_id
79 and fai3.user_entity_id = ' || l_uid_caeoy_prov_of_emp ||
80 ' and fai3.value =
81 nvl(pay_ca_t4_reg.get_parameter(''PROV_CD'',
82 ppa.legislative_parameters),fai3.value)
83 and fai3.context1 = paa.assignment_action_id
84 and fai4.user_entity_id = ' || l_uid_caeoy_person_id ||
85 ' and fai4.context1 = paa.assignment_action_id
86 and fai4.value = nvl(pay_ca_t4_reg.get_parameter(''PER_ID'',
87 ppa.legislative_parameters),fai4.value)
88 order by to_number(fai4.value)';
89
90 hr_utility.trace('End of range_cursor 2 ');
91
92 end range_cursor;
93 ---------------------------------- action_creation -------------------------
94 --
95 procedure action_creation(pactid in number,
96 stperson in number,
97 endperson in number,
98 chunk in number) is
99
100
101 --
102 lockingactid number;
103 lockedactid number;
104 l_asg_set_id number;
105 l_asg_id number;
106 l_prov_cd ff_archive_item_contexts.context%TYPE;
107 l_province ff_archive_item_contexts.context%TYPE;
108 l_tax_unit_id number;
109 l_year varchar2(4);
110 l_bus_group_id number;
111 l_year_start date;
112 l_year_end date;
113 l_t4reg_tax_unit_id number;
114 l_effective_date date;
115 l_report_type varchar2(80);
116 l_legislative_parameters varchar2(240);
117 lv_per_id varchar2(30);
118
119 lv_negative_bal_flag varchar2(10);
120 lv_neg_bal_mesg varchar2(100);
121 lv_person_type varchar2(20);
122 lv_message_level varchar2(20);
123 lv_message varchar2(500);
124
125 lv_sin varchar2(20);
126 lv_employee_full_name varchar2(300);
127 lv_employee_last_name varchar2(200);
128 lv_employee_name varchar2(200);
129
130 --
131 l_uid_caeoy_tax_unit_id ff_user_entities.user_entity_id%TYPE;
132 l_uid_caeoy_tax_year ff_user_entities.user_entity_id%TYPE;
133 l_arch_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE;
134 l_uid_caeoy_gross_earning ff_user_entities.user_entity_id%TYPE;
135 l_session_date pay_payroll_actions.effective_date%TYPE;
136
137 cursor c_all_gres is
138 SELECT
139 pay_ca_t4_reg.get_parameter('TRANSFER_GRE', ppa.legislative_parameters),
140 ppa.payroll_action_id,
141 ppa.effective_date
142 FROM
143 pay_payroll_actions ppa
144 WHERE
145 ppa.report_type = 'T4' AND
146 ppa.report_category = 'CAEOY' and
147 ppa.report_qualifier = 'CAEOY' and
148 ppa.business_group_id = l_bus_group_id and
149 ppa.effective_date = l_year_end and
150 ppa.action_status = 'C';
151
152 -- The following cursor will only be used when the tax_unit_id
153 -- (GRE name) is passed while submitting the SRS for T4 Paper
154 -- Report.
155
156 CURSOR cur_arch_paid(p_tax_unit_id number) IS
157 SELECT
158 ppa.payroll_action_id,
159 ppa.effective_date
160 FROM
161 pay_payroll_actions ppa
162 WHERE
163 ppa.report_type = 'T4' AND
164 ppa.report_category = 'CAEOY' and
165 ppa.report_qualifier = 'CAEOY' and
166 ppa.business_group_id = l_bus_group_id and
167 pay_ca_t4_reg.get_parameter('TRANSFER_GRE', ppa.legislative_parameters)
168 = to_char(p_tax_unit_id) and
169 ppa.effective_date = l_year_end and
170 ppa.action_status = 'C';
171
172 cursor c_all_asg(p_arch_pactid number
173 ,p_prov varchar2) is
174 select paf.assignment_id assignment_id,
175 faic.context prov_cd,
176 paa.assignment_action_id,
177 paa.payroll_action_id
178 from per_assignments_f paf,
179 pay_assignment_actions paa,
180 ff_archive_items fai,
181 ff_contexts fc,
182 ff_archive_item_contexts faic
183 where paf.person_id between stperson
184 and endperson
185 and paf.primary_flag = 'Y'
186 and paf.effective_start_date = (select max(paf2.effective_start_date)
187 from per_assignments_f paf2
188 where paf2.assignment_id= paf.assignment_id
189 and paf2.primary_flag = 'Y'
190 and paf2.effective_start_date
191 <= l_session_date) --l_year_end
192 and paf.effective_end_date >= l_year_start
193 and paa.payroll_action_id = p_arch_pactid
194 and paa.assignment_id = paf.assignment_id
195 and paa.assignment_action_id = fai.context1
196 and fai.user_entity_id = l_uid_caeoy_gross_earning
197 and fai.archive_item_id = faic.archive_item_id
198 and faic.context = nvl(rtrim(p_prov), faic.context)
199 and faic.context_id = fc.context_id
200 and fc.context_name = 'JURISDICTION_CODE';
201
202 /* Added this to run the report for Single Person enter at SRS level*/
203 cursor c_single_asg(p_arch_pactid number
204 ,p_per_id varchar2
205 ,p_prov varchar2) is
206 select paf.assignment_id assignment_id,
207 faic.context prov_cd,
208 paa.assignment_action_id,
209 paa.payroll_action_id
210 from per_assignments_f paf,
211 pay_assignment_actions paa,
212 ff_archive_items fai,
213 ff_contexts fc,
214 ff_archive_item_contexts faic
215 where paf.person_id between stperson
216 and endperson
217 and paf.primary_flag = 'Y'
218 and paf.effective_start_date = (select max(paf2.effective_start_date)
219 from per_assignments_f paf2
220 where paf2.assignment_id= paf.assignment_id
221 and paf2.primary_flag = 'Y'
222 and paf2.effective_start_date
223 <= l_session_date) --l_year_end
224 and paf.effective_end_date >= l_year_start
225 and paa.payroll_action_id = p_arch_pactid
226 and paa.assignment_id = paf.assignment_id
227 and paa.serial_number = p_per_id
228 and paa.assignment_action_id = fai.context1
229 and fai.user_entity_id = l_uid_caeoy_gross_earning
230 and fai.archive_item_id = faic.archive_item_id
231 and faic.context = nvl(rtrim(p_prov), faic.context)
232 and faic.context_id = fc.context_id
233 and fc.context_name = 'JURISDICTION_CODE';
234
235 /* Added this new cursor to fix bug#2135545 and this
236 will be used only if Assignment Set is passed for T4 reports */
237
238 cursor c_all_asg_in_asgset(p_arch_pactid number
239 ,p_prov varchar2) is
240 select paf.assignment_id assignment_id,
241 faic.context prov_cd,
242 paa.assignment_action_id,
243 paa.payroll_action_id
244 from per_assignments_f paf,
245 pay_assignment_actions paa,
246 ff_archive_items fai,
247 ff_archive_item_contexts faic,
248 ff_contexts fc
249 where paf.person_id >= stperson
250 and paf.person_id <= endperson
251 and paf.primary_flag = 'Y'
252 and paf.effective_start_date = (select max(paf2.effective_start_date)
253 from per_assignments_f paf2
254 where paf2.assignment_id= paf.assignment_id
255 and paf2.primary_flag = 'Y'
256 and paf2.effective_start_date
257 <= l_session_date) --l_year_end
258 and paf.effective_end_date >= l_year_start
259 and paa.payroll_action_id = p_arch_pactid
260 and paa.assignment_id = paf.assignment_id
261 and paa.assignment_action_id = fai.context1
262 and fai.user_entity_id = l_uid_caeoy_gross_earning
263 and fai.archive_item_id = faic.archive_item_id
264 and faic.context = nvl(rtrim(p_prov), faic.context)
265 and faic.context_id = fc.context_id
266 and fc.context_name = 'JURISDICTION_CODE'
267 AND exists ( select 1 /* Selected Assignment Set */
268 from hr_assignment_set_amendments hasa
269 where hasa.assignment_set_id = l_asg_set_id
270 and hasa.assignment_id = paf.assignment_id
271 and upper(hasa.include_or_exclude) = 'I');
272
273 lv_serial_number varchar2(30);
274 ln_arch_asgact_id number;
275 ln_arch_pact_id number;
276
277 begin
278
279 hr_utility.trace('begining of action creation 1 '||to_char(pactid));
280 hr_utility.trace('Start Person ID = ' || to_char(stperson));
281 hr_utility.trace('End Person ID = ' || to_char(endperson));
282 hr_utility.trace('Chunk # = ' || to_char(chunk));
283
284 /* get report type and effective date */
285
286 select effective_date,
287 report_type,
288 business_group_id,
289 legislative_parameters
290 into l_effective_date,
291 l_report_type,
292 l_bus_group_id,
293 l_legislative_parameters
294 from pay_payroll_actions
295 where payroll_action_id = pactid;
296
297 hr_utility.trace('begining of action creation 2 '||to_char(l_bus_group_id));
298 hr_utility.trace('legislative parameters is '||l_legislative_parameters);
299
300 l_year := pay_ca_t4_reg.get_parameter('TAX_YEAR',l_legislative_parameters);
301 l_year_start := trunc(to_date(l_year,'YYYY'), 'Y');
302 l_year_end := add_months(trunc(to_date(l_year,'YYYY'), 'Y'),12) - 1;
303 l_asg_set_id := pay_ca_t4_reg.get_parameter('ASG_SET_ID',l_legislative_parameters);
304 l_province := pay_ca_t4_reg.get_parameter('PROV_CD',l_legislative_parameters);
305
306 l_t4reg_tax_unit_id := to_number(pay_ca_t4_reg.get_parameter('GRE_ID',
307 l_legislative_parameters));
308
309 lv_per_id := pay_ca_t4_reg.get_parameter('PER_ID',
310 l_legislative_parameters);
311
312 hr_utility.trace('begining of action creation 4 '||
313 to_char(l_t4reg_tax_unit_id));
314
315 l_uid_caeoy_tax_year := get_user_entity_id('CAEOY_TAXATION_YEAR');
316 l_uid_caeoy_tax_unit_id := get_user_entity_id('CAEOY_TAX_UNIT_ID');
317 l_uid_caeoy_gross_earning
318 := get_user_entity_id('CAEOY_GROSS_EARNINGS_PER_JD_GRE_YTD');
319
320 if l_t4reg_tax_unit_id <> 99999 then
321
322 l_tax_unit_id := l_t4reg_tax_unit_id;
323
324 open cur_arch_paid(l_tax_unit_id);
325 fetch cur_arch_paid into
326 l_arch_payroll_action_id,
327 l_session_date;
328 close cur_arch_paid;
329
330 /* Added this validation to fix bug#2135545 */
331 if l_asg_set_id is not null then
332 open c_all_asg_in_asgset(l_arch_payroll_action_id,
333 l_province);
334 /* to run for single employee entered at SRS level */
335 elsif lv_per_id is not null then
336 open c_single_asg(l_arch_payroll_action_id,
337 lv_per_id,
338 l_province);
339 else
340 open c_all_asg(l_arch_payroll_action_id,
341 l_province);
342 end if;
343
344 hr_utility.trace('begining of if condition 5 '||to_char(l_tax_unit_id));
345
346 else
347
348 open c_all_gres;
349
350 hr_utility.trace('else condition after open c_all_gres c_all_asg cursor 6 ');
351
352 end if;
353
354
355 if l_t4reg_tax_unit_id <> 99999 then
356 loop
357
358 /* Added this validation to fix bug#2135545 */
359 if l_asg_set_id is not null then
360 fetch c_all_asg_in_asgset into l_asg_id,
361 l_prov_cd,
362 ln_arch_asgact_id,
363 ln_arch_pact_id;
364 exit when c_all_asg_in_asgset%notfound;
365 /* added to run for single employee entered at SRS level */
366 elsif lv_per_id is not null then
367 fetch c_single_asg into l_asg_id,
368 l_prov_cd,
369 ln_arch_asgact_id,
370 ln_arch_pact_id;
371 exit when c_single_asg%notfound;
372 else
373 fetch c_all_asg into l_asg_id,
374 l_prov_cd,
375 ln_arch_asgact_id,
376 ln_arch_pact_id;
377 exit when c_all_asg%notfound;
378 end if;
379
380
381 hr_utility.trace('Begining of if part loop for c_all_asg 10 '||
382 to_char(l_asg_id));
383
384
385 lv_negative_bal_flag := 'N';
386 if l_report_type in ('PYT4PR','T4_XML') then
387
388 lv_negative_bal_flag :=
389 pay_ca_archive_utils.get_archive_value(ln_arch_asgact_id,
390 l_prov_cd,
391 'JURISDICTION_CODE',
392 'CAEOY_T4_NEGATIVE_BALANCE_EXISTS');
393 end if;
394
395 if (lv_negative_bal_flag = 'N' or
396 lv_negative_bal_flag is null) then
397
398 select pay_assignment_actions_s.nextval
399 into lockingactid
400 from dual;
401
402 hr_nonrun_asact.insact(lockingactid,
403 l_asg_id,
404 pactid,
405 chunk,
406 l_tax_unit_id);
407 hr_utility.trace('in if loop after calling hr_nonrun_asact.insact pkg 11 '
408 ||to_char(l_asg_id));
409 /* Added this to implement T4 Register and T4 Amendment Register
410 using the same report file */
411
412 lv_serial_number := l_prov_cd||lpad(to_char(ln_arch_asgact_id),14,0)||
413 lpad(to_char(ln_arch_pact_id),14,0);
414
415 hr_utility.trace('lv_serial_number :' ||lv_serial_number);
416
417 update pay_assignment_actions paa
418 set paa.serial_number = lv_serial_number
419 where paa.assignment_action_id = lockingactid;
420
421 else
422
423 lv_sin := pay_ca_archive_utils.get_archive_value(ln_arch_asgact_id,
424 'CAEOY_EMPLOYEE_SIN');
425
426 lv_sin := ltrim(rtrim(replace(lv_sin, ' ')));
427 lv_sin := substr(lv_sin,1,3)||' '||substr(lv_sin,4,3)||' '||substr(lv_sin,7,3);
428
429 lv_employee_name := pay_ca_archive_utils.get_archive_value(ln_arch_asgact_id,
430 'CAEOY_EMPLOYEE_FIRST_NAME');
431
432 lv_employee_last_name := pay_ca_archive_utils.get_archive_value(ln_arch_asgact_id,
433 'CAEOY_EMPLOYEE_LAST_NAME');
434
435 lv_employee_full_name := lv_employee_last_name ||','|| lv_employee_name;
436
437 lv_neg_bal_mesg := pay_ca_t4_reg.get_label('PAY_CA_LABELS','EOY_NEG_BAL');
438 lv_person_type := pay_ca_t4_reg.get_label('PERSON_TYPE','EMP');
439 lv_message_level:= pay_ca_t4_reg.get_label('MESSAGE_LEVEL','W');
440
441 lv_message:= lv_message_level||':'|| lv_person_type ||':'|| substr(lv_employee_full_name,1,45) ||
442 '(' || lv_sin || ') ' || lv_neg_bal_mesg;
443
444 pay_core_utils.push_message(801,'HR_ELE_ENTRY_FORMULA_HINT','A');
445 pay_core_utils.push_token('FORMULA_TEXT',lv_message);
446
447 end if;
448
449 end loop;
450
451 /* Added this validation to fix bug#2135545 */
452 if l_asg_set_id is not null then
453 close c_all_asg_in_asgset;
454 elsif lv_per_id is not null then
455 close c_single_asg;
456 else
457 close c_all_asg;
458 end if;
459 hr_utility.trace('End of cursor c_all_asg 12');
460
461 else
462
463 loop
464 fetch c_all_gres into
465 l_tax_unit_id,
466 l_arch_payroll_action_id,
467 l_session_date;
468
469 hr_utility.trace('Begining of else loop for c_all_gres 7 '||to_char(l_tax_unit_id));
470 exit when c_all_gres%notfound;
471
472 /* Added this validation to fix bug#2135545 */
473 if l_asg_set_id is not null then
474 open c_all_asg_in_asgset(l_arch_payroll_action_id,
475 l_province);
476 elsif lv_per_id is not null then
477 open c_single_asg(l_arch_payroll_action_id,
478 lv_per_id,
479 l_province);
480 else
481 open c_all_asg(l_arch_payroll_action_id,
482 l_province);
483 end if;
484
485 loop
486 /* Added this validation to fix bug#2135545 */
487 if l_asg_set_id is not null then
488 fetch c_all_asg_in_asgset into l_asg_id,
489 l_prov_cd,
490 ln_arch_asgact_id,
491 ln_arch_pact_id;
492 exit when c_all_asg_in_asgset%notfound;
493 /* added to run for single employee entered at SRS level */
494 elsif lv_per_id is not null then
495 fetch c_single_asg into l_asg_id,
496 l_prov_cd,
497 ln_arch_asgact_id,
498 ln_arch_pact_id;
499 exit when c_single_asg%notfound;
500 else
501 fetch c_all_asg into l_asg_id,
502 l_prov_cd,
503 ln_arch_asgact_id,
504 ln_arch_pact_id;
505 exit when c_all_asg%notfound;
506 end if;
507
508 hr_utility.trace('Begining of loop for c_all_asg 8 '||to_char(l_asg_id));
509
510 lv_negative_bal_flag := 'N';
511 if l_report_type in ('PYT4PR','T4_XML') then
512
513 lv_negative_bal_flag :=
514 pay_ca_archive_utils.get_archive_value(ln_arch_asgact_id,
515 l_prov_cd,
516 'JURISDICTION_CODE',
517 'CAEOY_T4_NEGATIVE_BALANCE_EXISTS');
518 end if;
519
520 if (lv_negative_bal_flag = 'N' or
521 lv_negative_bal_flag is null) then
522
523 select pay_assignment_actions_s.nextval
524 into lockingactid
525 from dual;
526
527 hr_nonrun_asact.insact(lockingactid,
528 l_asg_id,
529 pactid,
530 chunk,
531 l_tax_unit_id);
532
533 hr_utility.trace('in if loop after calling hr_nonrun_asact.insact pkg 9 '
534 ||to_char(l_asg_id));
535
536 /* Added this to implement T4 Register and T4 Amendment Register
537 using the same report file */
538
539 lv_serial_number := l_prov_cd||lpad(to_char(ln_arch_asgact_id),14,0)||
540 lpad(to_char(ln_arch_pact_id),14,0);
541
542 hr_utility.trace('lv_serial_number :' ||lv_serial_number);
543
544 update pay_assignment_actions paa
545 set paa.serial_number = lv_serial_number
546 where paa.assignment_action_id = lockingactid;
547
548 else
549
550 lv_sin := pay_ca_archive_utils.get_archive_value(ln_arch_asgact_id,
551 'CAEOY_EMPLOYEE_SIN');
552
553 lv_sin := ltrim(rtrim(replace(lv_sin, ' ')));
554 lv_sin := substr(lv_sin,1,3)||' '||substr(lv_sin,4,3)||' '||substr(lv_sin,7,3);
555
556 lv_employee_name := pay_ca_archive_utils.get_archive_value(ln_arch_asgact_id,
557 'CAEOY_EMPLOYEE_FIRST_NAME');
558
559 lv_employee_last_name := pay_ca_archive_utils.get_archive_value(ln_arch_asgact_id,
560 'CAEOY_EMPLOYEE_LAST_NAME');
561
562 lv_employee_full_name := lv_employee_last_name ||','|| lv_employee_name;
563
564 lv_neg_bal_mesg := pay_ca_t4_reg.get_label('PAY_CA_LABELS','EOY_NEG_BAL');
565 lv_person_type := pay_ca_t4_reg.get_label('PERSON_TYPE','EMP');
566 lv_message_level:= pay_ca_t4_reg.get_label('MESSAGE_LEVEL','W');
567
568 lv_message:= lv_message_level||':'|| lv_person_type ||':'|| substr(lv_employee_full_name,1,45) || '(' || lv_sin || ') ' || lv_neg_bal_mesg;
569
570 pay_core_utils.push_message(801,'HR_ELE_ENTRY_FORMULA_HINT','A');
571 pay_core_utils.push_token('FORMULA_TEXT',lv_message);
572
573 end if;
574
575 end loop;
576
577 /* Added this validation to fix bug#2135545 */
578 if l_asg_set_id is not null then
579 close c_all_asg_in_asgset;
580 elsif lv_per_id is not null then
581 close c_single_asg;
582 else
583 close c_all_asg;
584 end if;
585
586 end loop;
587 close c_all_gres;
588 end if;
589
590 hr_utility.trace('End of If Condition for Loop 13');
591
592 end action_creation;
593
594 ---------------------------------- sort_action -----------------------------
595 procedure sort_action
596 (
597 payactid in varchar2, /* payroll action id */
598 sqlstr in out nocopy varchar2, /* string holding the sql statement */
599 len out nocopy number /* length of the sql string */
600 ) is
601 begin
602 hr_utility.trace('Start of Sort_Action 1');
603
604 sqlstr := 'select paa1.rowid
605 from hr_all_organization_units hou,
606 hr_all_organization_units hou1,
607 hr_locations_all loc,
608 per_all_people_f ppf,
609 per_all_assignments_f paf,
610 pay_assignment_actions paa1,
611 pay_payroll_actions ppa1
612 where ppa1.payroll_action_id = :pactid
613 and paa1.payroll_action_id = ppa1.payroll_action_id
614 and paa1.assignment_id = paf.assignment_id
615 and paf.effective_start_date =
616 (select max(paf2.effective_start_date)
617 from per_all_assignments_f paf2
618 where paf2.assignment_id= paf.assignment_id
619 and paf2.effective_start_date
620 <= ppa1.effective_date)
621 and paf.effective_end_date >= ppa1.start_date
622 and paf.assignment_type = ''E''
623 and hou1.organization_id = paa1.tax_unit_id
624 and hou.organization_id = paf.organization_id
625 and loc.location_id = paf.location_id
626 and ppf.person_id = paf.person_id
627 and ppf.effective_start_date =
628 (select max(ppf2.effective_start_date)
629 from per_all_people_f ppf2
630 where ppf2.person_id= paf.person_id
631 and ppf2.effective_start_date
632 <= ppa1.effective_date)
633 and ppf.effective_end_date >= ppa1.start_date
634 order by
635 decode(pay_ca_t4_reg.get_parameter
636 (''P_S1'',ppa1.legislative_parameters),
637 ''GRE'',hou1.name,
638 ''ORGANIZATION'',hou.name,
639 ''LOCATION'',loc.location_code,null),
640 decode(pay_ca_t4_reg.get_parameter(''P_S2'',ppa1.legislative_parameters),
641 ''GRE'',hou1.name,
642 ''ORGANIZATION'',hou.name,
643 ''LOCATION'',loc.location_code,null),
644
645 decode(pay_ca_t4_reg.get_parameter(''P_S3'',ppa1.legislative_parameters),
646 ''GRE'',hou1.name,
647 ''ORGANIZATION'',hou.name,
648 ''LOCATION'',loc.location_code,null),
649 ppf.last_name,first_name
650 for update of paa1.assignment_action_id';
651
652 len := length(sqlstr); -- return the length of the string.
653 hr_utility.trace('End of Sort_Action 2');
654 end sort_action;
655 --
656 ------------------------------ get_parameter -------------------------------
657 function get_parameter(name in varchar2,
658 parameter_list varchar2) return varchar2
659 is
660 start_ptr number;
661 end_ptr number;
662 token_val pay_payroll_actions.legislative_parameters%type;
663 par_value pay_payroll_actions.legislative_parameters%type;
664 begin
665 --
666 token_val := name||'=';
667 --
668 start_ptr := instr(parameter_list, token_val) + length(token_val);
669 end_ptr := instr(parameter_list, ' ',start_ptr);
670 --
671 /* if there is no spaces use then length of the string */
672 if end_ptr = 0 then
673 end_ptr := length(parameter_list)+1;
674 end if;
675 --
676 /* Did we find the token */
677 if instr(parameter_list, token_val) = 0 then
678 par_value := NULL;
679 else
680 par_value := substr(parameter_list, start_ptr, end_ptr - start_ptr);
681 end if;
682 --
683 return par_value;
684 --
685 end get_parameter;
686 --
687 function get_label(p_lookup_type in varchar2,
688 p_lookup_code in varchar2) return varchar2 is
689
690 l_meaning hr_lookups.meaning%TYPE;
691
692 CURSOR cur_get_meaning IS
693 SELECT hl.meaning
694 FROM hr_lookups hl
695 WHERE hl.lookup_type = p_lookup_type AND
696 hl.lookup_code = p_lookup_code;
697
698 BEGIN
699
700 OPEN cur_get_meaning;
701 FETCH cur_get_meaning
702 INTO l_meaning;
703 if cur_get_meaning%NOTFOUND then
704 l_meaning := NULL;
705 end if;
706
707 CLOSE cur_get_meaning;
708
709 RETURN l_meaning;
710
711 END get_label; -- get_label
712
713 end pay_ca_t4_reg;