[Home] [Help]
PACKAGE BODY: APPS.PAY_CA_T4_REG
Source
1 PACKAGE BODY pay_ca_t4_reg AS
2 /* $Header: pycat4rg.pkb 120.5.12020000.3 2013/01/25 11:34:37 rgottipa ship $ */
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 pay_payroll_actions.legislative_parameters%type;
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
133 l_state pay_payroll_actions.report_qualifier%type;
130 -- Variables declared for bug 10399514
131 l_person_on boolean ;
132 l_report_cat pay_payroll_actions.report_category%type;
134 l_report_format pay_report_format_mappings_f.report_format%type;
135 -- Variables declared for bug 10399514
136
137 --
138 l_uid_caeoy_tax_unit_id ff_user_entities.user_entity_id%TYPE;
139 l_uid_caeoy_tax_year ff_user_entities.user_entity_id%TYPE;
140 l_arch_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE;
141 l_uid_caeoy_gross_earning ff_user_entities.user_entity_id%TYPE;
142 l_session_date pay_payroll_actions.effective_date%TYPE;
143
144 cursor c_all_gres is
145 SELECT
146 pay_ca_t4_reg.get_parameter('TRANSFER_GRE', ppa.legislative_parameters),
147 ppa.payroll_action_id,
148 ppa.effective_date
149 FROM
150 pay_payroll_actions ppa
151 WHERE
152 ppa.report_type = 'T4' AND
153 ppa.report_category = 'CAEOY' and
154 ppa.report_qualifier = 'CAEOY' and
155 ppa.business_group_id = l_bus_group_id and
156 ppa.effective_date = l_year_end and
157 ppa.action_status = 'C';
158
159 -- The following cursor will only be used when the tax_unit_id
160 -- (GRE name) is passed while submitting the SRS for T4 Paper
161 -- Report.
162
163 CURSOR cur_arch_paid(p_tax_unit_id number) IS
164 SELECT
165 ppa.payroll_action_id,
166 ppa.effective_date
167 FROM
168 pay_payroll_actions ppa
169 WHERE
170 ppa.report_type = 'T4' AND
171 ppa.report_category = 'CAEOY' and
172 ppa.report_qualifier = 'CAEOY' and
173 ppa.business_group_id = l_bus_group_id and
174 pay_ca_t4_reg.get_parameter('TRANSFER_GRE', ppa.legislative_parameters)
175 = to_char(p_tax_unit_id) and
176 ppa.effective_date = l_year_end and
177 ppa.action_status = 'C';
178
179 cursor c_all_asg(p_arch_pactid number
180 ,p_prov varchar2) is
181 select paf.assignment_id assignment_id,
182 faic.context prov_cd,
183 paa.assignment_action_id,
184 paa.payroll_action_id,
185 paf.effective_end_date
186 from per_assignments_f paf,
187 pay_assignment_actions paa,
188 ff_archive_items fai,
189 ff_contexts fc,
190 ff_archive_item_contexts faic
191 where paf.person_id between stperson
192 and endperson
193 and paf.primary_flag = 'Y'
194 and paf.effective_start_date = (select max(paf2.effective_start_date)
195 from per_assignments_f paf2
196 where paf2.assignment_id= paf.assignment_id
197 and paf2.primary_flag = 'Y'
198 and paf2.effective_start_date
199 <= l_session_date) --l_year_end
200 and paf.effective_end_date >= l_year_start
201 and paa.payroll_action_id = p_arch_pactid
202 and paa.assignment_id = paf.assignment_id
203 and paa.assignment_action_id = fai.context1
204 and fai.user_entity_id = l_uid_caeoy_gross_earning
205 and fai.archive_item_id = faic.archive_item_id
206 and faic.context = nvl(rtrim(p_prov), faic.context)
207 and faic.context_id = fc.context_id
208 and fc.context_name = 'JURISDICTION_CODE';
209
210
211 -- Added for Bug# 10399514
212 -- Used when RANGE_PERSON_ID functionality is available
213 cursor c_all_asg_range(p_arch_pactid number
214 ,p_prov varchar2) is
215 select paf.assignment_id assignment_id,
216 faic.context prov_cd,
217 paa.assignment_action_id,
218 paa.payroll_action_id,
219 paf.effective_end_date
220 from per_assignments_f paf,
221 pay_assignment_actions paa,
222 ff_archive_items fai,
223 ff_contexts fc,
224 ff_archive_item_contexts faic,
225 pay_population_ranges ppr
226 where --paf.person_id between stperson
227 -- and endperson
228 paf.primary_flag = 'Y'
229 and paf.effective_start_date = (select max(paf2.effective_start_date)
230 from per_assignments_f paf2
231 where paf2.assignment_id= paf.assignment_id
232 and paf2.primary_flag = 'Y'
233 and paf2.effective_start_date
234 <= l_session_date) --l_year_end
235 and paf.effective_end_date >= l_year_start
236 and paa.payroll_action_id = p_arch_pactid
237 and paa.assignment_id = paf.assignment_id
238 and paa.assignment_action_id = fai.context1
239 and fai.user_entity_id = l_uid_caeoy_gross_earning
240 and fai.archive_item_id = faic.archive_item_id
241 and faic.context = nvl(rtrim(p_prov), faic.context)
242 and faic.context_id = fc.context_id
243 and fc.context_name = 'JURISDICTION_CODE'
244 and ppr.payroll_action_id = pactid
245 and ppr.chunk_number = chunk
246 and ppr.person_id = to_number(paf.person_id);
247
248
249 -- Changes for bug 15886428 starts
250 cursor c_all_asg_term(p_arch_pactid number
251 ,p_prov varchar2) is
252 select paf.assignment_id assignment_id,
253 faic.context prov_cd,
254 paa.assignment_action_id,
255 paa.payroll_action_id,
256 paf.effective_end_date
257 from per_assignments_f paf,
258 pay_assignment_actions paa,
259 ff_archive_items fai,
260 ff_contexts fc,
261 ff_archive_item_contexts faic,
262 per_periods_of_service PDS
263 where paf.person_id between stperson
264 and endperson
268 where paf2.assignment_id= paf.assignment_id
265 and paf.primary_flag = 'Y'
266 and paf.effective_start_date = (select max(paf2.effective_start_date)
267 from per_assignments_f paf2
269 and paf2.primary_flag = 'Y'
270 and paf2.effective_start_date
271 <= l_session_date) --l_year_end
272 and paf.effective_end_date >= l_year_start
273 and paa.payroll_action_id = p_arch_pactid
274 and paa.assignment_id = paf.assignment_id
275 and paa.assignment_action_id = fai.context1
276 and fai.user_entity_id = l_uid_caeoy_gross_earning
277 and fai.archive_item_id = faic.archive_item_id
278 and faic.context = nvl(rtrim(p_prov), faic.context)
279 and faic.context_id = fc.context_id
280 and fc.context_name = 'JURISDICTION_CODE'
281 and pds.actual_termination_date is not null
282 and pds.period_of_service_id = paf.period_of_service_id;
283
284
285 cursor c_all_asg_range_term(p_arch_pactid number
286 ,p_prov varchar2) is
287 select paf.assignment_id assignment_id,
288 faic.context prov_cd,
289 paa.assignment_action_id,
290 paa.payroll_action_id,
291 paf.effective_end_date
292 from per_assignments_f paf,
293 pay_assignment_actions paa,
294 ff_archive_items fai,
295 ff_contexts fc,
296 ff_archive_item_contexts faic,
297 pay_population_ranges ppr,
298 per_periods_of_service PDS
299 where --paf.person_id between stperson
300 -- and endperson
301 paf.primary_flag = 'Y'
302 and paf.effective_start_date = (select max(paf2.effective_start_date)
303 from per_assignments_f paf2
304 where paf2.assignment_id= paf.assignment_id
305 and paf2.primary_flag = 'Y'
306 and paf2.effective_start_date
307 <= l_session_date) --l_year_end
308 and paf.effective_end_date >= l_year_start
309 and paa.payroll_action_id = p_arch_pactid
310 and paa.assignment_id = paf.assignment_id
311 and paa.assignment_action_id = fai.context1
312 and fai.user_entity_id = l_uid_caeoy_gross_earning
313 and fai.archive_item_id = faic.archive_item_id
314 and faic.context = nvl(rtrim(p_prov), faic.context)
315 and faic.context_id = fc.context_id
316 and fc.context_name = 'JURISDICTION_CODE'
317 and ppr.payroll_action_id = pactid
318 and ppr.chunk_number = chunk
319 and ppr.person_id = to_number(paf.person_id)
320 and pds.actual_termination_date is not null
321 and pds.period_of_service_id = paf.period_of_service_id;
322 -- Changes for bug 15886428 ends
323
324
325 /* Added this to run the report for Single Person enter at SRS level*/
326 cursor c_single_asg(p_arch_pactid number
327 ,p_per_id varchar2
328 ,p_prov varchar2) is
329 select paf.assignment_id assignment_id,
330 faic.context prov_cd,
331 paa.assignment_action_id,
332 paa.payroll_action_id,
333 paf.effective_end_date
334 from per_assignments_f paf,
335 pay_assignment_actions paa,
336 ff_archive_items fai,
337 ff_contexts fc,
338 ff_archive_item_contexts faic
339 where paf.person_id between stperson
340 and endperson
341 and paf.primary_flag = 'Y'
342 and paf.effective_start_date = (select max(paf2.effective_start_date)
343 from per_assignments_f paf2
344 where paf2.assignment_id= paf.assignment_id
345 and paf2.primary_flag = 'Y'
346 and paf2.effective_start_date
347 <= l_session_date) --l_year_end
348 and paf.effective_end_date >= l_year_start
349 and paa.payroll_action_id = p_arch_pactid
350 and paa.assignment_id = paf.assignment_id
351 and paa.serial_number = p_per_id
352 and paa.assignment_action_id = fai.context1
353 and fai.user_entity_id = l_uid_caeoy_gross_earning
354 and fai.archive_item_id = faic.archive_item_id
355 and faic.context = nvl(rtrim(p_prov), faic.context)
356 and faic.context_id = fc.context_id
357 and fc.context_name = 'JURISDICTION_CODE';
358
359
360
361 -- Changes for bug 15886428 starts
362 cursor c_single_asg_term(p_arch_pactid number
363 ,p_per_id varchar2
364 ,p_prov varchar2) is
365 select paf.assignment_id assignment_id,
366 faic.context prov_cd,
367 paa.assignment_action_id,
368 paa.payroll_action_id,
369 paf.effective_end_date
370 from per_assignments_f paf,
371 pay_assignment_actions paa,
372 ff_archive_items fai,
373 ff_contexts fc,
374 ff_archive_item_contexts faic,
375 per_periods_of_service PDS
376 where paf.person_id between stperson
377 and endperson
378 and paf.primary_flag = 'Y'
379 and paf.effective_start_date = (select max(paf2.effective_start_date)
380 from per_assignments_f paf2
381 where paf2.assignment_id= paf.assignment_id
382 and paf2.primary_flag = 'Y'
383 and paf2.effective_start_date
384 <= l_session_date) --l_year_end
385 and paf.effective_end_date >= l_year_start
386 and paa.payroll_action_id = p_arch_pactid
387 and paa.assignment_id = paf.assignment_id
391 and fai.archive_item_id = faic.archive_item_id
388 and paa.serial_number = p_per_id
389 and paa.assignment_action_id = fai.context1
390 and fai.user_entity_id = l_uid_caeoy_gross_earning
392 and faic.context = nvl(rtrim(p_prov), faic.context)
393 and faic.context_id = fc.context_id
394 and fc.context_name = 'JURISDICTION_CODE'
395 and pds.actual_termination_date is not null
396 and pds.period_of_service_id = paf.period_of_service_id;
397 -- Changes for bug 15886428 ends
398
399
400 /* Added this new cursor to fix bug#2135545 and this
401 will be used only if Assignment Set is passed for T4 reports */
402
403 cursor c_all_asg_in_asgset(p_arch_pactid number
404 ,p_prov varchar2) is
405 select paf.assignment_id assignment_id,
406 faic.context prov_cd,
407 paa.assignment_action_id,
408 paa.payroll_action_id,
409 paf.effective_end_date
410 from per_assignments_f paf,
411 pay_assignment_actions paa,
412 ff_archive_items fai,
413 ff_archive_item_contexts faic,
414 ff_contexts fc
415 where paf.person_id >= stperson
416 and paf.person_id <= endperson
417 and paf.primary_flag = 'Y'
418 and paf.effective_start_date = (select max(paf2.effective_start_date)
419 from per_assignments_f paf2
420 where paf2.assignment_id= paf.assignment_id
421 and paf2.primary_flag = 'Y'
422 and paf2.effective_start_date
423 <= l_session_date) --l_year_end
424 and paf.effective_end_date >= l_year_start
425 and paa.payroll_action_id = p_arch_pactid
426 and paa.assignment_id = paf.assignment_id
427 and paa.assignment_action_id = fai.context1
428 and fai.user_entity_id = l_uid_caeoy_gross_earning
429 and fai.archive_item_id = faic.archive_item_id
430 and faic.context = nvl(rtrim(p_prov), faic.context)
431 and faic.context_id = fc.context_id
432 and fc.context_name = 'JURISDICTION_CODE'
433 AND exists ( select 1 /* Selected Assignment Set */
434 from hr_assignment_set_amendments hasa
435 where hasa.assignment_set_id = l_asg_set_id
436 and hasa.assignment_id = paf.assignment_id
437 and upper(hasa.include_or_exclude) = 'I');
438
439 -- Added for Bug# 10399514
440 -- Used when RANGE_PERSON_ID functionality is available
441 cursor c_all_asg_in_asgset_range(p_arch_pactid number
442 ,p_prov varchar2) is
443 select paf.assignment_id assignment_id,
444 faic.context prov_cd,
445 paa.assignment_action_id,
446 paa.payroll_action_id,
447 paf.effective_end_date
448 from per_assignments_f paf,
449 pay_assignment_actions paa,
450 ff_archive_items fai,
451 ff_archive_item_contexts faic,
452 ff_contexts fc,
453 pay_population_ranges ppr
454 where --paf.person_id >= stperson
455 --and paf.person_id <= endperson
456 paf.primary_flag = 'Y'
457 and paf.effective_start_date = (select max(paf2.effective_start_date)
458 from per_assignments_f paf2
459 where paf2.assignment_id= paf.assignment_id
460 and paf2.primary_flag = 'Y'
461 and paf2.effective_start_date
462 <= l_session_date) --l_year_end
463 and paf.effective_end_date >= l_year_start
464 and paa.payroll_action_id = p_arch_pactid
465 and paa.assignment_id = paf.assignment_id
466 and paa.assignment_action_id = fai.context1
467 and fai.user_entity_id = l_uid_caeoy_gross_earning
468 and fai.archive_item_id = faic.archive_item_id
469 and faic.context = nvl(rtrim(p_prov), faic.context)
470 and faic.context_id = fc.context_id
471 and fc.context_name = 'JURISDICTION_CODE'
472 AND exists ( select 1 /* Selected Assignment Set */
473 from hr_assignment_set_amendments hasa
474 where hasa.assignment_set_id = l_asg_set_id
475 and hasa.assignment_id = paf.assignment_id
476 and upper(hasa.include_or_exclude) = 'I')
477 and ppr.payroll_action_id = pactid
478 and ppr.chunk_number = chunk
479 and ppr.person_id = to_number(paf.person_id);
480
481 -- Changes for bug 15886428 starts
482 cursor c_all_asg_in_asgset_term(p_arch_pactid number
483 ,p_prov varchar2) is
484 select paf.assignment_id assignment_id,
485 faic.context prov_cd,
486 paa.assignment_action_id,
487 paa.payroll_action_id,
488 paf.effective_end_date
489 from per_assignments_f paf,
490 pay_assignment_actions paa,
491 ff_archive_items fai,
492 ff_archive_item_contexts faic,
493 ff_contexts fc,
494 per_periods_of_service PDS
495 where paf.person_id >= stperson
496 and paf.person_id <= endperson
497 and paf.primary_flag = 'Y'
498 and paf.effective_start_date = (select max(paf2.effective_start_date)
499 from per_assignments_f paf2
500 where paf2.assignment_id= paf.assignment_id
501 and paf2.primary_flag = 'Y'
502 and paf2.effective_start_date
503 <= l_session_date) --l_year_end
504 and paf.effective_end_date >= l_year_start
505 and paa.payroll_action_id = p_arch_pactid
506 and paa.assignment_id = paf.assignment_id
507 and paa.assignment_action_id = fai.context1
511 and faic.context_id = fc.context_id
508 and fai.user_entity_id = l_uid_caeoy_gross_earning
509 and fai.archive_item_id = faic.archive_item_id
510 and faic.context = nvl(rtrim(p_prov), faic.context)
512 and fc.context_name = 'JURISDICTION_CODE'
513 AND exists ( select 1 /* Selected Assignment Set */
514 from hr_assignment_set_amendments hasa
515 where hasa.assignment_set_id = l_asg_set_id
516 and hasa.assignment_id = paf.assignment_id
517 and upper(hasa.include_or_exclude) = 'I')
518 and pds.actual_termination_date is not null
519 and pds.period_of_service_id = paf.period_of_service_id;
520
521
522 cursor c_all_asg_in_asgset_range_term(p_arch_pactid number
523 ,p_prov varchar2) is
524 select paf.assignment_id assignment_id,
525 faic.context prov_cd,
526 paa.assignment_action_id,
527 paa.payroll_action_id,
528 paf.effective_end_date
529 from per_assignments_f paf,
530 pay_assignment_actions paa,
531 ff_archive_items fai,
532 ff_archive_item_contexts faic,
533 ff_contexts fc,
534 pay_population_ranges ppr,
535 per_periods_of_service PDS
536 where --paf.person_id >= stperson
537 --and paf.person_id <= endperson
538 paf.primary_flag = 'Y'
539 and paf.effective_start_date = (select max(paf2.effective_start_date)
540 from per_assignments_f paf2
541 where paf2.assignment_id= paf.assignment_id
542 and paf2.primary_flag = 'Y'
543 and paf2.effective_start_date
544 <= l_session_date) --l_year_end
545 and paf.effective_end_date >= l_year_start
546 and paa.payroll_action_id = p_arch_pactid
547 and paa.assignment_id = paf.assignment_id
548 and paa.assignment_action_id = fai.context1
549 and fai.user_entity_id = l_uid_caeoy_gross_earning
550 and fai.archive_item_id = faic.archive_item_id
551 and faic.context = nvl(rtrim(p_prov), faic.context)
552 and faic.context_id = fc.context_id
553 and fc.context_name = 'JURISDICTION_CODE'
554 AND exists ( select 1 /* Selected Assignment Set */
555 from hr_assignment_set_amendments hasa
556 where hasa.assignment_set_id = l_asg_set_id
557 and hasa.assignment_id = paf.assignment_id
558 and upper(hasa.include_or_exclude) = 'I')
559 and ppr.payroll_action_id = pactid
560 and ppr.chunk_number = chunk
561 and ppr.person_id = to_number(paf.person_id)
562 and pds.actual_termination_date is not null
563 and pds.period_of_service_id = paf.period_of_service_id;
564 -- Changes for bug 15886428 ends
565
566
567 lv_serial_number varchar2(30);
568 ln_arch_asgact_id number;
569 ln_arch_pact_id number;
570 l_print_term varchar2(1);
571 l_effective_end_date date;
572
573
574 begin
575
576 hr_utility.trace('begining of action creation 1 '||to_char(pactid));
577 hr_utility.trace('Start Person ID = ' || to_char(stperson));
578 hr_utility.trace('End Person ID = ' || to_char(endperson));
579 hr_utility.trace('Chunk # = ' || to_char(chunk));
580 hr_utility.trace('l_uid_caeoy_gross_earning '||l_uid_caeoy_gross_earning);
581
582 /* get report type and effective date */
583
584 select effective_date,
585 report_type,
586 business_group_id,
587 legislative_parameters,
588 -- Added for bug 10399514
589 report_qualifier,
590 report_category
591 -- Added for bug 10399514
592 into l_effective_date,
593 l_report_type,
594 l_bus_group_id,
595 l_legislative_parameters,
596 -- Added for bug 10399514
597 l_state,
598 l_report_cat
599 -- Added for bug 10399514
600 from pay_payroll_actions
601 where payroll_action_id = pactid;
602
603 hr_utility.trace('begining of action creation 2 '||to_char(l_bus_group_id));
604 hr_utility.trace('legislative parameters is '||l_legislative_parameters);
605
606 l_year := pay_ca_t4_reg.get_parameter('TAX_YEAR',l_legislative_parameters);
607 l_year_start := trunc(to_date(l_year,'YYYY'), 'Y');
608 l_year_end := add_months(trunc(to_date(l_year,'YYYY'), 'Y'),12) - 1;
609 l_asg_set_id := pay_ca_t4_reg.get_parameter('ASG_SET_ID',l_legislative_parameters);
610 l_province := pay_ca_t4_reg.get_parameter('PROV_CD',l_legislative_parameters);
611 l_print_term := pay_ca_t4_reg.get_parameter('PRINT_TERM',l_legislative_parameters); -- bug 15886428
612
613 l_t4reg_tax_unit_id := to_number(pay_ca_t4_reg.get_parameter('GRE_ID',
614 l_legislative_parameters));
615
616 lv_per_id := pay_ca_t4_reg.get_parameter('PER_ID',
617 l_legislative_parameters);
618
619 hr_utility.trace('begining of action creation 4 '||
620 to_char(l_t4reg_tax_unit_id));
621
622 l_uid_caeoy_tax_year := get_user_entity_id('CAEOY_TAXATION_YEAR');
623 l_uid_caeoy_tax_unit_id := get_user_entity_id('CAEOY_TAX_UNIT_ID');
624 l_uid_caeoy_gross_earning
625 := get_user_entity_id('CAEOY_GROSS_EARNINGS_PER_JD_GRE_YTD');
626
627 -- Code modification for bug 10399514 starts here
628 /* Initializing variable */
629 l_person_on := FALSE ;
630
631 Begin
632 select report_format
633 into l_report_format
634 from pay_report_format_mappings_f
635 where report_type = l_report_type
636 and report_qualifier = l_state
640 l_report_format := Null ;
637 and report_category = l_report_cat ;
638 Exception
639 When Others Then
641 End ;
642 hr_utility.trace('l_report_format: '||l_report_format);
643 hr_utility.trace('l_report_type: '||l_report_type);
644 hr_utility.trace('l_state '||l_state);
645 hr_utility.trace('l_report_cat: '||l_report_cat);
646 l_person_on := pay_ac_utility.range_person_on( p_report_type => l_report_type,
647 p_report_format => l_report_format,
648 p_report_qualifier => l_state,
649 p_report_category => l_report_cat) ;
650
651
652 if l_t4reg_tax_unit_id <> 99999 then
653
654 l_tax_unit_id := l_t4reg_tax_unit_id;
655
656 open cur_arch_paid(l_tax_unit_id);
657 fetch cur_arch_paid into
658 l_arch_payroll_action_id,
659 l_session_date;
660 close cur_arch_paid;
661
662 /* Added this validation to fix bug#2135545 */
663 if l_asg_set_id is not null then
664 if l_person_on then
665 if nvl(l_print_term,'N') = 'Y' then
666 open c_all_asg_in_asgset_range_term(l_arch_payroll_action_id,
667 l_province);
668 hr_utility.trace('opening c_all_asg_in_asgset_range_term CURSOR');
669 else
670 open c_all_asg_in_asgset_range(l_arch_payroll_action_id,
671 l_province);
672 hr_utility.trace('opening c_all_asg_in_asgset_range CURSOR');
673 end if;
674 else
675 if nvl(l_print_term,'N') = 'Y' then
676 open c_all_asg_in_asgset_term(l_arch_payroll_action_id,
677 l_province);
678 hr_utility.trace('opening c_all_asg_in_asgset_term CURSOR');
679 else
680 open c_all_asg_in_asgset(l_arch_payroll_action_id,
681 l_province);
682 hr_utility.trace('opening c_all_asg_in_asgset CURSOR');
683 end if;
684 end if;
685 /* to run for single employee entered at SRS level */
686 elsif lv_per_id is not null then
687 if nvl(l_print_term,'N') = 'Y' then
688 open c_single_asg_term(l_arch_payroll_action_id,
689 lv_per_id,
690 l_province);
691 hr_utility.trace('opening c_single_asg_term CURSOR');
692 else
693 open c_single_asg(l_arch_payroll_action_id,
694 lv_per_id,
695 l_province);
696 hr_utility.trace('opening c_single_asg CURSOR');
697 end if;
698 else
699 if l_person_on then
700 if nvl(l_print_term,'N') = 'Y' then
701 open c_all_asg_range_term(l_arch_payroll_action_id,
702 l_province);
703 hr_utility.trace('opening c_all_asg_range_term CURSOR');
704 else
705 open c_all_asg_range(l_arch_payroll_action_id,
706 l_province);
707 hr_utility.trace('opening c_all_asg_range CURSOR');
708 end if;
709 else
710 if nvl(l_print_term,'N') = 'Y' then
711 open c_all_asg_term(l_arch_payroll_action_id,
712 l_province);
713 hr_utility.trace('opening c_all_asg_term CURSOR');
714 else
715 open c_all_asg(l_arch_payroll_action_id,
716 l_province);
717 hr_utility.trace('opening c_all_asg CURSOR');
718 end if;
719 end if;
720 end if;
721
722 hr_utility.trace('begining of if condition 5 '||to_char(l_tax_unit_id));
723
724 else
725
726 open c_all_gres;
727
728 hr_utility.trace('else condition after open c_all_gres c_all_asg cursor 6 ');
729
730 end if;
731
732
733 if l_t4reg_tax_unit_id <> 99999 then
734 loop
735
736 /* Added this validation to fix bug#2135545 */
737 if l_asg_set_id is not null then
738 if l_person_on then
739 if nvl(l_print_term,'N') = 'Y' then
740 hr_utility.trace('fetching c_all_asg_in_asgset_range_term CURSOR');
741 fetch c_all_asg_in_asgset_range_term into l_asg_id,
742 l_prov_cd,
743 ln_arch_asgact_id,
744 ln_arch_pact_id,
745 l_effective_end_date;
746 exit when c_all_asg_in_asgset_range_term%notfound;
747 else
748 hr_utility.trace('fetching c_all_asg_in_asgset_range CURSOR');
749 fetch c_all_asg_in_asgset_range into l_asg_id,
750 l_prov_cd,
751 ln_arch_asgact_id,
752 ln_arch_pact_id,
753 l_effective_end_date;
754 exit when c_all_asg_in_asgset_range%notfound;
755 end if;
756 else
757 if nvl(l_print_term,'N') = 'Y' then
758 hr_utility.trace('fetching c_all_asg_in_asgset_term CURSOR');
759 fetch c_all_asg_in_asgset_term into l_asg_id,
760 l_prov_cd,
761 ln_arch_asgact_id,
762 ln_arch_pact_id,
763 l_effective_end_date;
767 fetch c_all_asg_in_asgset into l_asg_id,
764 exit when c_all_asg_in_asgset_term%notfound;
765 else
766 hr_utility.trace('fetching c_all_asg_in_asgset CURSOR');
768 l_prov_cd,
769 ln_arch_asgact_id,
770 ln_arch_pact_id,
771 l_effective_end_date;
772 exit when c_all_asg_in_asgset%notfound;
773 end if;
774 end if;
775 /* added to run for single employee entered at SRS level */
776 elsif lv_per_id is not null then
777 if nvl(l_print_term,'N') = 'Y' then
778 hr_utility.trace('fetching c_single_asg_term CURSOR');
779 fetch c_single_asg_term into l_asg_id,
780 l_prov_cd,
781 ln_arch_asgact_id,
782 ln_arch_pact_id,
783 l_effective_end_date;
784 exit when c_single_asg_term%notfound;
785 else
786 hr_utility.trace('fetching c_single_asg CURSOR');
787 fetch c_single_asg into l_asg_id,
788 l_prov_cd,
789 ln_arch_asgact_id,
790 ln_arch_pact_id,
791 l_effective_end_date;
792 exit when c_single_asg%notfound;
793 end if;
794 else
795 if l_person_on then
796 if nvl(l_print_term,'N') = 'Y' then
797 hr_utility.trace('fetching c_all_asg_range_term CURSOR');
798 fetch c_all_asg_range_term into l_asg_id,
799 l_prov_cd,
800 ln_arch_asgact_id,
801 ln_arch_pact_id,
802 l_effective_end_date;
803 exit when c_all_asg_range_term%notfound;
804 else
805 hr_utility.trace('fetching c_all_asg_range CURSOR');
806 fetch c_all_asg_range into l_asg_id,
807 l_prov_cd,
808 ln_arch_asgact_id,
809 ln_arch_pact_id,
810 l_effective_end_date;
811 exit when c_all_asg_range%notfound;
812 end if;
813 else
814 if nvl(l_print_term,'N') = 'Y' then
815 hr_utility.trace('fetching c_all_asg_term CURSOR');
816 fetch c_all_asg_term into l_asg_id,
817 l_prov_cd,
818 ln_arch_asgact_id,
819 ln_arch_pact_id,
820 l_effective_end_date;
821 exit when c_all_asg_term%notfound;
822 else
823 hr_utility.trace('fetching c_all_asg CURSOR');
824 fetch c_all_asg into l_asg_id,
825 l_prov_cd,
826 ln_arch_asgact_id,
827 ln_arch_pact_id,
828 l_effective_end_date;
829 exit when c_all_asg%notfound;
830 end if;
831 end if;
832 end if;
833
834
835 hr_utility.trace('Begining of if part loop for c_all_asg 10 '||
836 to_char(l_asg_id));
837
838
839 lv_negative_bal_flag := 'N';
840 if l_report_type in ('PYT4PR','T4_XML') then
841
842 lv_negative_bal_flag :=
843 pay_ca_archive_utils.get_archive_value(ln_arch_asgact_id,
844 l_prov_cd,
845 'JURISDICTION_CODE',
846 'CAEOY_T4_NEGATIVE_BALANCE_EXISTS');
847 end if;
848
849 hr_utility.trace('lv_negative_bal_flag '||lv_negative_bal_flag);
850 hr_utility.trace('l_asg_id '||l_asg_id);
851 hr_utility.trace('l_effective_end_date '||l_effective_end_date);
852
853 if (lv_negative_bal_flag = 'N' or
854 lv_negative_bal_flag is null) then
855
856 -- Changes for bug 15886428 starts
857 if (l_report_type = 'T4_XML' and nvl(pay_us_employee_payslip_web.get_doc_eit('T4PDF',
858 'PRINT',
859 'ASSIGNMENT',
860 l_asg_id,
861 l_effective_end_date),'Y') = 'Y') or l_report_type <> 'T4_XML' then
862
863 select pay_assignment_actions_s.nextval
864 into lockingactid
865 from dual;
866
867 hr_nonrun_asact.insact(lockingactid,
868 l_asg_id,
869 pactid,
870 chunk,
871 l_tax_unit_id);
872 hr_utility.trace('in if loop after calling hr_nonrun_asact.insact pkg 11 '
873 ||to_char(l_asg_id));
874 /* Added this to implement T4 Register and T4 Amendment Register
875 using the same report file */
876
877 lv_serial_number := l_prov_cd||lpad(to_char(ln_arch_asgact_id),14,0)||
878 lpad(to_char(ln_arch_pact_id),14,0);
879
880 hr_utility.trace('lv_serial_number :' ||lv_serial_number);
881
882 update pay_assignment_actions paa
883 set paa.serial_number = lv_serial_number
884 where paa.assignment_action_id = lockingactid;
885 end if;
886
887 else
888
889 lv_sin := pay_ca_archive_utils.get_archive_value(ln_arch_asgact_id,
890 'CAEOY_EMPLOYEE_SIN');
891
892 lv_sin := ltrim(rtrim(replace(lv_sin, ' ')));
896 'CAEOY_EMPLOYEE_FIRST_NAME');
893 lv_sin := substr(lv_sin,1,3)||' '||substr(lv_sin,4,3)||' '||substr(lv_sin,7,3);
894
895 lv_employee_name := pay_ca_archive_utils.get_archive_value(ln_arch_asgact_id,
897
898 lv_employee_last_name := pay_ca_archive_utils.get_archive_value(ln_arch_asgact_id,
899 'CAEOY_EMPLOYEE_LAST_NAME');
900
901 lv_employee_full_name := lv_employee_last_name ||','|| lv_employee_name;
902
903 lv_neg_bal_mesg := pay_ca_t4_reg.get_label('PAY_CA_LABELS','EOY_NEG_BAL');
904 lv_person_type := pay_ca_t4_reg.get_label('PERSON_TYPE','EMP');
905 lv_message_level:= pay_ca_t4_reg.get_label('MESSAGE_LEVEL','W');
906
907 lv_message:= lv_message_level||':'|| lv_person_type ||':'|| substr(lv_employee_full_name,1,45) ||
908 '(' || lv_sin || ') ' || lv_neg_bal_mesg;
909
910 pay_core_utils.push_message(801,'HR_ELE_ENTRY_FORMULA_HINT','A');
911 pay_core_utils.push_token('FORMULA_TEXT',lv_message);
912
913 end if;
914
915 end loop;
916
917 /* Added this validation to fix bug#2135545 */
918 if l_asg_set_id is not null then
919 if l_person_on then
920 if nvl(l_print_term,'N') = 'Y' then
921 hr_utility.trace('Closing c_all_asg_in_asgset_range_term CURSOR');
922 close c_all_asg_in_asgset_range_term;
923 else
924 hr_utility.trace('Closing c_all_asg_in_asgset_range CURSOR');
925 close c_all_asg_in_asgset_range;
926 end if;
927 else
928 if nvl(l_print_term,'N') = 'Y' then
929 hr_utility.trace('Closing c_all_asg_in_asgset_term CURSOR');
930 close c_all_asg_in_asgset_term;
931 else
932 hr_utility.trace('Closing c_all_asg_in_asgset CURSOR');
933 close c_all_asg_in_asgset;
934 end if;
935 end if;
936 elsif lv_per_id is not null then
937 if nvl(l_print_term,'N') = 'Y' then
938 hr_utility.trace('Closing c_single_asg_term CURSOR');
939 close c_single_asg_term;
940 else
941 hr_utility.trace('Closing c_single_asg CURSOR');
942 close c_single_asg;
943 end if;
944 else
945 if l_person_on then
946 if nvl(l_print_term,'N') = 'Y' then
947 hr_utility.trace('Closing c_all_asg_range_term CURSOR');
948 close c_all_asg_range_term;
949 else
950 hr_utility.trace('Closing c_all_asg_range CURSOR');
951 close c_all_asg_range;
952 end if;
953 else
954 if nvl(l_print_term,'N') = 'Y' then
955 hr_utility.trace('Closing c_all_asg_term CURSOR');
956 close c_all_asg_term;
957 else
958 hr_utility.trace('Closing c_all_asg CURSOR');
959 close c_all_asg;
960 end if;
961 end if;
962 end if;
963 hr_utility.trace('End of cursor c_all_asg 12');
964
965 else
966
967 loop
968 fetch c_all_gres into
969 l_tax_unit_id,
970 l_arch_payroll_action_id,
971 l_session_date;
972
973 hr_utility.trace('Begining of else loop for c_all_gres 7 '||to_char(l_tax_unit_id));
974 exit when c_all_gres%notfound;
975
976 /* Added this validation to fix bug#2135545 */
977 if l_asg_set_id is not null then
978 if l_person_on then
979 if nvl(l_print_term,'N') = 'Y' then
980 hr_utility.trace('opening c_all_asg_in_asgset_range_term CURSOR');
981 open c_all_asg_in_asgset_range_term(l_arch_payroll_action_id,
982 l_province);
983 else
984 hr_utility.trace('opening c_all_asg_in_asgset_range CURSOR');
985 open c_all_asg_in_asgset_range(l_arch_payroll_action_id,
986 l_province);
987 end if;
988 else
989 if nvl(l_print_term,'N') = 'Y' then
990 hr_utility.trace('opening c_all_asg_in_asgset_term CURSOR');
991 open c_all_asg_in_asgset_term(l_arch_payroll_action_id,
992 l_province);
993 else
994 hr_utility.trace('opening c_all_asg_in_asgset CURSOR');
995 open c_all_asg_in_asgset(l_arch_payroll_action_id,
996 l_province);
997 end if;
998 end if;
999 elsif lv_per_id is not null then
1000 if nvl(l_print_term,'N') = 'Y' then
1001 hr_utility.trace('opening c_single_asg_term CURSOR');
1002 open c_single_asg_term(l_arch_payroll_action_id,
1003 lv_per_id,
1004 l_province);
1005 else
1006 hr_utility.trace('opening c_single_asg CURSOR');
1007 open c_single_asg(l_arch_payroll_action_id,
1008 lv_per_id,
1009 l_province);
1010 end if;
1011 else
1012 if l_person_on then
1013 if nvl(l_print_term,'N') = 'Y' then
1014 hr_utility.trace('opening c_all_asg_range_term CURSOR');
1015 open c_all_asg_range_term(l_arch_payroll_action_id,
1016 l_province);
1017 else
1018 hr_utility.trace('opening c_all_asg_range CURSOR');
1019 open c_all_asg_range(l_arch_payroll_action_id,
1020 l_province);
1021 end if;
1022 else
1023 if nvl(l_print_term,'N') = 'Y' then
1024 hr_utility.trace('opening c_all_asg_term CURSOR');
1025 open c_all_asg_term(l_arch_payroll_action_id,
1026 l_province);
1027 else
1028 hr_utility.trace('opening c_all_asg CURSOR');
1029 open c_all_asg(l_arch_payroll_action_id,
1030 l_province);
1031 end if;
1032 end if;
1033 end if;
1034
1035 loop
1036 /* Added this validation to fix bug#2135545 */
1037 if l_asg_set_id is not null then
1038 if l_person_on then
1039 if nvl(l_print_term,'N') = 'Y' then
1040 hr_utility.trace('fetching c_all_asg_in_asgset_range_term CURSOR');
1041 fetch c_all_asg_in_asgset_range_term into l_asg_id,
1042 l_prov_cd,
1043 ln_arch_asgact_id,
1044 ln_arch_pact_id,
1045 l_effective_end_date;
1046 exit when c_all_asg_in_asgset_range_term%notfound;
1047 else
1048 hr_utility.trace('fetching c_all_asg_in_asgset_range CURSOR');
1049 fetch c_all_asg_in_asgset_range into l_asg_id,
1050 l_prov_cd,
1051 ln_arch_asgact_id,
1052 ln_arch_pact_id,
1053 l_effective_end_date;
1054 exit when c_all_asg_in_asgset_range%notfound;
1055 end if;
1056 else
1057 if nvl(l_print_term,'N') = 'Y' then
1058 hr_utility.trace('fetching c_all_asg_in_asgset_term CURSOR');
1059 fetch c_all_asg_in_asgset_term into l_asg_id,
1060 l_prov_cd,
1061 ln_arch_asgact_id,
1062 ln_arch_pact_id,
1063 l_effective_end_date;
1064 exit when c_all_asg_in_asgset_term%notfound;
1065 else
1066 hr_utility.trace('fetching c_all_asg_in_asgset CURSOR');
1067 fetch c_all_asg_in_asgset into l_asg_id,
1068 l_prov_cd,
1069 ln_arch_asgact_id,
1070 ln_arch_pact_id,
1071 l_effective_end_date;
1072 exit when c_all_asg_in_asgset%notfound;
1073 end if;
1074 end if; -- if l_person_on then
1075 /* added to run for single employee entered at SRS level */
1076 elsif lv_per_id is not null then
1077 if nvl(l_print_term,'N') = 'Y' then
1078 hr_utility.trace('fetching c_single_asg_term CURSOR');
1079 fetch c_single_asg_term into l_asg_id,
1080 l_prov_cd,
1081 ln_arch_asgact_id,
1082 ln_arch_pact_id,
1083 l_effective_end_date;
1084 exit when c_single_asg_term%notfound;
1085 else
1086 hr_utility.trace('fetching c_single_asg CURSOR');
1087 fetch c_single_asg into l_asg_id,
1088 l_prov_cd,
1089 ln_arch_asgact_id,
1090 ln_arch_pact_id,
1091 l_effective_end_date;
1092 exit when c_single_asg%notfound;
1093 end if;
1094 else
1095 if l_person_on then
1096 if nvl(l_print_term,'N') = 'Y' then
1097 hr_utility.trace('fetching c_all_asg_range_term CURSOR');
1098 fetch c_all_asg_range_term into l_asg_id,
1099 l_prov_cd,
1100 ln_arch_asgact_id,
1101 ln_arch_pact_id,
1102 l_effective_end_date;
1103 exit when c_all_asg_range_term%notfound;
1104 else
1105 hr_utility.trace('fetching c_all_asg_range CURSOR');
1106 fetch c_all_asg_range into l_asg_id,
1107 l_prov_cd,
1108 ln_arch_asgact_id,
1109 ln_arch_pact_id,
1110 l_effective_end_date;
1111 exit when c_all_asg_range%notfound;
1112 end if;
1113 else
1114 if nvl(l_print_term,'N') = 'Y' then
1115 hr_utility.trace('fetching c_all_asg_term CURSOR');
1116 fetch c_all_asg_term into l_asg_id,
1117 l_prov_cd,
1118 ln_arch_asgact_id,
1119 ln_arch_pact_id,
1120 l_effective_end_date;
1121 exit when c_all_asg_term%notfound;
1122 else
1123 hr_utility.trace('fetching c_all_asg CURSOR');
1124 fetch c_all_asg into l_asg_id,
1125 l_prov_cd,
1126 ln_arch_asgact_id,
1127 ln_arch_pact_id,
1128 l_effective_end_date;
1129 exit when c_all_asg%notfound;
1130 end if;
1131 end if;
1132 end if;
1133
1134 hr_utility.trace('Begining of loop for c_all_asg 8 '||to_char(l_asg_id));
1135
1136 lv_negative_bal_flag := 'N';
1137 if l_report_type in ('PYT4PR','T4_XML') then
1138
1139 lv_negative_bal_flag :=
1140 pay_ca_archive_utils.get_archive_value(ln_arch_asgact_id,
1141 l_prov_cd,
1142 'JURISDICTION_CODE',
1143 'CAEOY_T4_NEGATIVE_BALANCE_EXISTS');
1144 end if;
1145
1146 if (lv_negative_bal_flag = 'N' or
1147 lv_negative_bal_flag is null) then
1148
1149 -- Changes for bug 15886428 starts
1150 if (l_report_type = 'T4_XML' and nvl(pay_us_employee_payslip_web.get_doc_eit('T4PDF',
1151 'PRINT',
1152 'ASSIGNMENT',
1153 l_asg_id,
1154 l_effective_end_date),'Y') = 'Y') or l_report_type <> 'T4_XML' then
1155 select pay_assignment_actions_s.nextval
1156 into lockingactid
1160 l_asg_id,
1157 from dual;
1158
1159 hr_nonrun_asact.insact(lockingactid,
1161 pactid,
1162 chunk,
1163 l_tax_unit_id);
1164
1165 hr_utility.trace('in if loop after calling hr_nonrun_asact.insact pkg 9 '
1166 ||to_char(l_asg_id));
1167
1168 /* Added this to implement T4 Register and T4 Amendment Register
1169 using the same report file */
1170
1171 lv_serial_number := l_prov_cd||lpad(to_char(ln_arch_asgact_id),14,0)||
1172 lpad(to_char(ln_arch_pact_id),14,0);
1173
1174 hr_utility.trace('lv_serial_number :' ||lv_serial_number);
1175
1176 update pay_assignment_actions paa
1177 set paa.serial_number = lv_serial_number
1178 where paa.assignment_action_id = lockingactid;
1179
1180 end if;
1181
1182 else
1183
1184 lv_sin := pay_ca_archive_utils.get_archive_value(ln_arch_asgact_id,
1185 'CAEOY_EMPLOYEE_SIN');
1186
1187 lv_sin := ltrim(rtrim(replace(lv_sin, ' ')));
1188 lv_sin := substr(lv_sin,1,3)||' '||substr(lv_sin,4,3)||' '||substr(lv_sin,7,3);
1189
1190 lv_employee_name := pay_ca_archive_utils.get_archive_value(ln_arch_asgact_id,
1191 'CAEOY_EMPLOYEE_FIRST_NAME');
1192
1193 lv_employee_last_name := pay_ca_archive_utils.get_archive_value(ln_arch_asgact_id,
1194 'CAEOY_EMPLOYEE_LAST_NAME');
1195
1196 lv_employee_full_name := lv_employee_last_name ||','|| lv_employee_name;
1197
1198 lv_neg_bal_mesg := pay_ca_t4_reg.get_label('PAY_CA_LABELS','EOY_NEG_BAL');
1199 lv_person_type := pay_ca_t4_reg.get_label('PERSON_TYPE','EMP');
1200 lv_message_level:= pay_ca_t4_reg.get_label('MESSAGE_LEVEL','W');
1201
1202 lv_message:= lv_message_level||':'|| lv_person_type ||':'|| substr(lv_employee_full_name,1,45) || '(' || lv_sin || ') ' || lv_neg_bal_mesg;
1203
1204 pay_core_utils.push_message(801,'HR_ELE_ENTRY_FORMULA_HINT','A');
1205 pay_core_utils.push_token('FORMULA_TEXT',lv_message);
1206
1207 end if;
1208
1209 end loop;
1210
1211 /* Added this validation to fix bug#2135545 */
1212 if l_asg_set_id is not null then
1213 if l_person_on then
1214 if nvl(l_print_term,'N') = 'Y' then
1215 hr_utility.trace('Closing c_all_asg_in_asgset_range_term CURSOR');
1216 close c_all_asg_in_asgset_range_term;
1217 else
1218 hr_utility.trace('Closing c_all_asg_in_asgset_range CURSOR');
1219 close c_all_asg_in_asgset_range;
1220 end if;
1221 else
1222 if nvl(l_print_term,'N') = 'Y' then
1223 hr_utility.trace('Closing c_all_asg_in_asgset_term CURSOR');
1224 close c_all_asg_in_asgset_term;
1225 else
1226 hr_utility.trace('Closing c_all_asg_in_asgset CURSOR');
1227 close c_all_asg_in_asgset;
1228 end if;
1229 end if;
1230 elsif lv_per_id is not null then
1231 if nvl(l_print_term,'N') = 'Y' then
1232 hr_utility.trace('Closing c_single_asg_term CURSOR');
1233 close c_single_asg_term;
1234 else
1235 hr_utility.trace('Closing c_single_asg CURSOR');
1236 close c_single_asg;
1237 end if;
1238 else
1239 if l_person_on then
1240 if nvl(l_print_term,'N') = 'Y' then
1241 hr_utility.trace('Closing c_all_asg_range_term CURSOR');
1242 close c_all_asg_range_term;
1243 else
1244 hr_utility.trace('Closing c_all_asg_range CURSOR');
1245 close c_all_asg_range;
1246 end if;
1247 else
1248 if nvl(l_print_term,'N') = 'Y' then
1249 hr_utility.trace('Closing c_all_asg_term CURSOR');
1250 close c_all_asg_term;
1251 else
1252 hr_utility.trace('Closing c_all_asg CURSOR');
1253 close c_all_asg;
1254 end if;
1255 end if;
1256 end if;
1257
1258 end loop;
1259 close c_all_gres;
1260 end if;
1261
1262 hr_utility.trace('End of If Condition for Loop 13');
1263
1264 end action_creation;
1265
1266 ---------------------------------- sort_action -----------------------------
1267 procedure sort_action
1268 (
1269 payactid in varchar2, /* payroll action id */
1270 sqlstr in out nocopy varchar2, /* string holding the sql statement */
1271 len out nocopy number /* length of the sql string */
1272 ) is
1273 begin
1274 hr_utility.trace('Start of Sort_Action 1');
1275
1276 sqlstr := 'select paa1.rowid
1277 from hr_all_organization_units hou,
1278 hr_all_organization_units hou1,
1279 hr_locations_all loc,
1280 per_all_people_f ppf,
1281 per_all_assignments_f paf,
1282 pay_assignment_actions paa1,
1283 pay_payroll_actions ppa1
1284 where ppa1.payroll_action_id = :pactid
1285 and paa1.payroll_action_id = ppa1.payroll_action_id
1286 and paa1.assignment_id = paf.assignment_id
1287 and paf.effective_start_date =
1288 (select max(paf2.effective_start_date)
1289 from per_all_assignments_f paf2
1290 where paf2.assignment_id= paf.assignment_id
1291 and paf2.effective_start_date
1292 <= ppa1.effective_date)
1293 and paf.effective_end_date >= ppa1.start_date
1294 and paf.assignment_type = ''E''
1295 and hou1.organization_id = paa1.tax_unit_id
1296 and hou.organization_id = paf.organization_id
1297 and loc.location_id = paf.location_id
1298 and ppf.person_id = paf.person_id
1299 and ppf.effective_start_date =
1300 (select max(ppf2.effective_start_date)
1301 from per_all_people_f ppf2
1302 where ppf2.person_id= paf.person_id
1303 and ppf2.effective_start_date
1304 <= ppa1.effective_date)
1305 and ppf.effective_end_date >= ppa1.start_date
1306 order by
1307 decode(pay_ca_t4_reg.get_parameter
1308 (''P_S1'',ppa1.legislative_parameters),
1309 ''GRE'',hou1.name,
1310 ''ORGANIZATION'',hou.name,
1311 ''LOCATION'',loc.location_code,null),
1312 decode(pay_ca_t4_reg.get_parameter(''P_S2'',ppa1.legislative_parameters),
1313 ''GRE'',hou1.name,
1314 ''ORGANIZATION'',hou.name,
1315 ''LOCATION'',loc.location_code,null),
1316
1317 decode(pay_ca_t4_reg.get_parameter(''P_S3'',ppa1.legislative_parameters),
1318 ''GRE'',hou1.name,
1319 ''ORGANIZATION'',hou.name,
1320 ''LOCATION'',loc.location_code,null),
1321 ppf.last_name,first_name
1322 for update of paa1.assignment_action_id';
1323
1324 len := length(sqlstr); -- return the length of the string.
1325 hr_utility.trace('End of Sort_Action 2');
1326 end sort_action;
1327 --
1328 ------------------------------ get_parameter -------------------------------
1329 function get_parameter(name in varchar2,
1330 parameter_list varchar2) return varchar2
1331 is
1332 start_ptr number;
1333 end_ptr number;
1334 token_val pay_payroll_actions.legislative_parameters%type;
1335 par_value pay_payroll_actions.legislative_parameters%type;
1336 begin
1337 --
1338 token_val := name||'=';
1339 --
1340 start_ptr := instr(parameter_list, token_val) + length(token_val);
1341 end_ptr := instr(parameter_list, ' ',start_ptr);
1342 --
1343 /* if there is no spaces use then length of the string */
1344 if end_ptr = 0 then
1345 end_ptr := length(parameter_list)+1;
1346 end if;
1347 --
1348 /* Did we find the token */
1349 if instr(parameter_list, token_val) = 0 then
1350 par_value := NULL;
1351 else
1352 par_value := substr(parameter_list, start_ptr, end_ptr - start_ptr);
1353 end if;
1354 --
1355 return par_value;
1356 --
1357 end get_parameter;
1358 --
1359 function get_label(p_lookup_type in varchar2,
1360 p_lookup_code in varchar2) return varchar2 is
1361
1362 l_meaning hr_lookups.meaning%TYPE;
1363
1364 CURSOR cur_get_meaning IS
1365 SELECT hl.meaning
1366 FROM hr_lookups hl
1367 WHERE hl.lookup_type = p_lookup_type AND
1368 hl.lookup_code = p_lookup_code;
1369
1370 BEGIN
1371
1372 OPEN cur_get_meaning;
1373 FETCH cur_get_meaning
1374 INTO l_meaning;
1375 if cur_get_meaning%NOTFOUND then
1376 l_meaning := NULL;
1377 end if;
1378
1379 CLOSE cur_get_meaning;
1380
1381 RETURN l_meaning;
1382
1383 END get_label; -- get_label
1384
1385 end pay_ca_t4_reg;