[Home] [Help]
PACKAGE BODY: APPS.PQP_USTIAA_PKG
Source
1 package body pqp_ustiaa_pkg AS
2 /* $Header: pqustiaa.pkb 120.8.12000000.1 2007/01/16 04:38:42 appldev noship $ */
3
4 g_proc_name VARCHAR2(50) := 'pqp_ustiaa_pkg.';
5
6 -- ---------------------------------------------------------------------
7 -- |------------------------< range_cursor >----------------------------|
8 -- ---------------------------------------------------------------------
9 PROCEDURE range_cursor
10 (pactid IN NUMBER
11 ,sqlstr OUT NOCOPY VARCHAR2) IS
12 leg_param pay_payroll_actions.legislative_parameters%TYPE ;
13 l_consolidation_set_id NUMBER;
14 l_payroll_id NUMBER;
15 l_tax_unit_id NUMBER;
16 l_proc_name VARCHAR2(150) := g_proc_name ||'range_cursor';
17 l_consolidation_set_text VARCHAR2(100);
18 l_payroll_text VARCHAR2(100);
19 l_tax_unit_text VARCHAR2(100);
20
21 BEGIN
22 hr_utility.set_location('Entering : '||l_proc_name, 10);
23 SELECT ppa.legislative_parameters,
24 pqp_ustiaa_pkg.get_parameter('TRANSFER_CONC_SET',ppa.legislative_parameters),
25 pqp_ustiaa_pkg.get_parameter('TRANSFER_PAYROLL',ppa.legislative_parameters),
26 pqp_ustiaa_pkg.get_parameter('TRANSFER_GRE',ppa.legislative_parameters)
27 INTO leg_param,
28 l_consolidation_set_id,
29 l_payroll_id,
30 l_tax_unit_id
31 FROM pay_payroll_actions ppa
32 WHERE ppa.payroll_action_id = pactid;
33 hr_utility.set_location('..Parameters are :'||leg_param,15);
34
35 -- For Bug 5636004 Performance Improvement.
36
37 IF l_consolidation_set_id is not null THEN
38 l_consolidation_set_text := 'and ppa_run.consolidation_set_id = ' || to_char(l_consolidation_set_id) ;
39 ELSE
40 l_consolidation_set_text := NULL;
41 END IF;
42
43 IF l_payroll_id is not null THEN
44 l_payroll_text := 'and ppa_run.payroll_id = ' || to_char(l_payroll_id) ;
45 ELSE
46 l_payroll_text := null;
47 END IF;
48
49 IF l_tax_unit_id is not null THEN
50 l_tax_unit_text := 'and act_run.tax_unit_id = ' || to_char(l_tax_unit_id) ;
51 ELSE
52 l_tax_unit_text := NULL;
53 END IF;
54
55
56 sqlstr := 'select distinct asg.person_id
57 from per_assignments_f asg,
58 pay_assignment_actions act_run,
59 pay_payroll_actions ppa_run,
60 pay_payroll_actions ppa_gen
61 where ppa_gen.payroll_action_id = :payroll_action_id
62 and ppa_run.action_type in (''R'',''Q'',''V'',''B'')
63 and ppa_run.action_status = ''C''
64 '||l_consolidation_set_text||'
65 '||l_payroll_text||'
66 and ppa_run.payroll_action_id = act_run.payroll_action_id
67 '||l_tax_unit_text||'
68
69 and asg.assignment_id = act_run.assignment_id
70 and ppa_run.effective_date between asg.effective_start_date
71 and asg.effective_end_date
72 and asg.business_group_id = ppa_gen.business_group_id
73 order by asg.person_id';
74 hr_utility.set_location('Leaving : '||l_proc_name, 90);
75 EXCEPTION
76 WHEN OTHERS THEN
77 sqlstr := NULL;
78 hr_utility.set_location('..Error in '||l_proc_name||' : '||SQLERRM,150);
79 hr_utility.set_location('Leaving : '||l_proc_name, 150);
80 RAISE;
81 END range_cursor;
82
83 -- ---------------------------------------------------------------------
84 -- |-----------------------< action_creation >--------------------------|
85 -- ---------------------------------------------------------------------
86 PROCEDURE action_creation
87 (pactid IN NUMBER,
88 stperson IN NUMBER ,
89 endperson IN NUMBER,
90 chunk IN NUMBER) IS
91
92 leg_param pay_payroll_actions.legislative_parameters%TYPE;
93 l_consolidation_set_id NUMBER;
94 l_payroll_id NUMBER;
95 l_tax_unit_id NUMBER;
96 l_proc_name VARCHAR2(150) := g_proc_name ||'action_creation';
97
98 CURSOR c_parameters (pactid NUMBER) IS
99 SELECT ppa.legislative_parameters,
100 pqp_ustiaa_pkg.get_parameter('TRANSFER_CONC_SET',ppa.legislative_parameters),
101 pqp_ustiaa_pkg.get_parameter('TRANSFER_PAYROLL',ppa.legislative_parameters),
102 pqp_ustiaa_pkg.get_parameter('TRANSFER_GRE',ppa.legislative_parameters)
103 FROM pay_payroll_actions ppa
104 WHERE ppa.payroll_action_id = pactid;
105
106 CURSOR c_actions (pactid NUMBER,
107 stperson NUMBER,
108 endperson NUMBER ) IS
109 SELECT
110 ppa_run.action_type ,
111 act_run.assignment_action_id,
112 asg.assignment_id ,
113 act_run.tax_unit_id
114 FROM per_assignments_f asg,
115 pay_payroll_actions ppa_run,
116 pay_assignment_actions act_run,
117 pay_payroll_actions ppa_gen
118 WHERE
119 ppa_gen.payroll_action_id = pactid
120 AND ppa_run.effective_date BETWEEN ppa_gen.start_date
121 AND ppa_gen.effective_date
122 AND ppa_run.action_type IN ('R','Q','V','B')
123 AND ppa_run.action_status = 'C'
124 AND ppa_run.consolidation_set_id = NVL(l_consolidation_set_id,
125 ppa_run.consolidation_set_id)
126 AND ppa_run.payroll_id = NVL(l_payroll_id,
127 ppa_run.payroll_id)
128 AND ppa_run.payroll_action_id = act_run.payroll_action_id
129 AND act_run.action_status = 'C'
130 AND act_run.tax_unit_id = NVL(l_tax_unit_id,
131 act_run.tax_unit_id)
132 AND asg.assignment_id = act_run.assignment_id
133 AND ppa_run.effective_date BETWEEN asg.effective_start_date
134 AND asg.effective_end_date
135 AND asg.business_group_id = ppa_gen.business_group_id
136 AND asg.person_id BETWEEN stperson
137 AND endperson
138 order by asg.assignment_id, act_run.assignment_action_id
139 FOR UPDATE OF asg.assignment_id;
140
141 CURSOR c_defbal IS
142 SELECT TO_NUMBER(ue.creator_id) creator_id,
143 di.user_name
144 FROM ff_user_entities ue,
145 ff_database_items di
146 WHERE di.user_name IN ( 'GROSS_EARNINGS_ASG_GRE_RUN',
147 'PAYMENTS_ASG_GRE_RUN' )
148 AND ue.user_entity_id = di.user_entity_id
149 AND ue.creator_type = 'B'
150 AND NVL(ue.legislation_code,'US') = 'US';
151
152 l_defbal c_defbal%ROWTYPE;
153 no_userid EXCEPTION;
154 lockingactid NUMBER;
155 lockedactid NUMBER;
156 assignid NUMBER;
157 greid NUMBER;
158 num NUMBER;
159 action_type VARCHAR2(1);
160 l_payments_bal NUMBER;
161 l_gross_defined_balance_id NUMBER;
162 l_payments_defined_balance_id NUMBER;
163 BEGIN
164 hr_utility.set_location('Entering : '||l_proc_name, 10);
165 OPEN c_parameters(pactid);
166 FETCH c_parameters INTO leg_param,
167 l_consolidation_set_id,
168 l_payroll_id,
169 l_tax_unit_id;
170 CLOSE c_parameters;
171 hr_utility.set_location('..Parameters are :'||leg_param, 15);
172 BEGIN
173 OPEN c_defbal;
174 LOOP
175 FETCH c_defbal INTO l_defbal;
176 EXIT WHEN c_defbal%NOTFOUND;
177 IF l_defbal.user_name= 'GROSS_EARNINGS_ASG_GRE_RUN' THEN
178 l_gross_defined_balance_id:=l_defbal.creator_id;
179 ELSIF l_defbal.user_name= 'PAYMENTS_ASG_GRE_RUN' THEN
180 l_payments_defined_balance_id:=l_defbal.creator_id;
181 END IF;
182 END LOOP;
183 CLOSE c_defbal;
184
185 IF l_gross_defined_balance_id IS NULL OR
186 l_payments_defined_balance_id IS NULL THEN
187 RAISE no_userid;
188 END IF;
189 EXCEPTION WHEN no_userid THEN
190 hr_utility.trace('Error getting defined balance id');
191 RAISE;
192 END;
193 --
194 -- Open the Assignment Action Creation Cursor
195 --
196 hr_utility.set_location('..Opening the Assignment Action Creation Cursor', 20);
197 OPEN c_actions(pactid,stperson,endperson);
198 num := 0;
199 LOOP
200 FETCH c_actions INTO action_type,lockedactid,assignid,greid;
201 IF c_actions%FOUND THEN
202 num := num + 1;
203 END IF;
204 EXIT WHEN c_actions%NOTFOUND;
205
206 SELECT pay_assignment_actions_s.nextval
207 INTO lockingactid
208 FROM dual;
209 -- Insert a record into pay_assignment_actions and pay_action_interlocks
210 hr_nonrun_asact.insact(lockingactid,assignid,pactid,chunk,greid);
211 hr_nonrun_asact.insint(lockingactid,lockedactid);
212
213 /* The following code is commented bug 4343076
214 pay_balance_pkg.set_context('TAX_UNIT_ID',greid);
215 l_payments_bal := NVL(pay_balance_pkg.get_value
216 (p_defined_balance_id => l_gross_defined_balance_id,
217 p_assignment_action_id => lockedactid),0);
218
219 IF l_payments_bal = 0 AND
220 action_type IN ('R','Q') THEN
221 l_payments_bal := NVL(pay_balance_pkg.get_value
222 (p_defined_balance_id => l_payments_defined_balance_id,
223 p_assignment_action_id => lockedactid),0);
224 END IF;
225 IF l_payments_bal = 0 AND
226 action_type IN ('R','Q') THEN
227 NULL;
228 ELSE
229 NULL;
230 END IF;
231 */
232
233 END LOOP;
234 CLOSE c_actions;
235 hr_utility.set_location('Leaving : '||l_proc_name, 90);
236 EXCEPTION
237 WHEN OTHERS THEN
238 hr_utility.set_location('..Error in '||l_proc_name||' : '||SQLERRM,150);
239 hr_utility.set_location('Leaving : '||l_proc_name, 150);
240 RAISE;
241 END action_creation;
242
243 -- ---------------------------------------------------------------------
244 -- |-------------------------< sort_action >----------------------------|
245 -- ---------------------------------------------------------------------
246 PROCEDURE sort_action
247 (payactid IN VARCHAR2,
248 sqlstr IN OUT NOCOPY VARCHAR2,
249 len OUT NOCOPY NUMBER
250 ) IS
251 l_proc_name VARCHAR2(150) := g_proc_name ||'sort_action';
252 BEGIN
253 hr_utility.set_location('Entering : '||l_proc_name, 10);
254 sqlstr := 'select paa1.rowid
255 from pay_assignment_actions paa1,
256 pay_payroll_actions ppa1
257 where ppa1.payroll_action_id = :pactid
258 and paa1.payroll_action_id = ppa1.payroll_action_id
259 order by paa1.assignment_id,paa1.assignment_action_id
260 for update of paa1.assignment_id';
261
262 len := LENGTH(sqlstr);
263 hr_utility.set_location('Leaving : '||l_proc_name, 90);
264 EXCEPTION
265 WHEN OTHERS THEN
266 sqlstr := NULL;
267 len := NULL;
268 hr_utility.set_location('..Error in '||l_proc_name||' : '||SQLERRM,150);
269 hr_utility.set_location('Leaving : '||l_proc_name, 150);
270 RAISE;
271 END sort_action;
272
273 -- ---------------------------------------------------------------------
274 -- |------------------------< get_parameter >---------------------------|
275 -- ---------------------------------------------------------------------
276 FUNCTION get_parameter
277 (name IN VARCHAR2,
278 parameter_list VARCHAR2
279 ) RETURN VARCHAR2 IS
280 start_ptr NUMBER;
281 end_ptr NUMBER;
282 token_val pay_payroll_actions.legislative_parameters%TYPE;
283 par_value pay_payroll_actions.legislative_parameters%TYPE;
284
285 BEGIN
286 token_val := name||'=';
287 start_ptr := INSTR(parameter_list, token_val) + LENGTH(token_val);
288 end_ptr := INSTR(parameter_list, ' ',start_ptr);
289 IF end_ptr = 0 THEN
290 end_ptr := LENGTH(parameter_list)+1;
291 END IF;
292 IF INSTR(parameter_list, token_val) = 0 THEN
293 par_value := NULL;
294 ELSE
295 par_value := SUBSTR(parameter_list, start_ptr, end_ptr - start_ptr);
296 END IF;
297 RETURN par_value;
298 END get_parameter;
299
300 -- -------------------------------------------------------------------------------------
301 -- |-----------------------< action_creation_ops >--------------------------|
302 -- -------------------------------------------------------------------------------------
303 PROCEDURE action_creation_ops
304 (pactid IN NUMBER,
305 stperson IN NUMBER ,
306 endperson IN NUMBER,
307 chunk IN NUMBER) IS
308
309 leg_param pay_payroll_actions.legislative_parameters%TYPE;
310 l_consolidation_set_id NUMBER;
311 l_payroll_id NUMBER;
312 l_tax_unit_id NUMBER;
313 l_proc_name VARCHAR2(150) := g_proc_name ||'action_creation_ops';
314
315 CURSOR c_parameters (pactid NUMBER) IS
316 SELECT ppa.legislative_parameters,
317 pqp_ustiaa_pkg.get_parameter('TRANSFER_CONC_SET',ppa.legislative_parameters),
318 pqp_ustiaa_pkg.get_parameter('TRANSFER_PAYROLL',ppa.legislative_parameters),
319 pqp_ustiaa_pkg.get_parameter('TRANSFER_GRE',ppa.legislative_parameters)
320 FROM pay_payroll_actions ppa
321 WHERE ppa.payroll_action_id = pactid;
322
323 CURSOR c_actions_ops (pactid NUMBER,
324 stperson NUMBER,
325 endperson NUMBER ) IS
326 SELECT
327 max(act_run.assignment_action_id),
328 asg.assignment_id
329 FROM per_assignments_f asg,
330 pay_payroll_actions ppa_run,
331 pay_assignment_actions act_run,
332 pay_payroll_actions ppa_gen
333 WHERE
334 ppa_gen.payroll_action_id = pactid
335 AND ppa_run.effective_date BETWEEN ppa_gen.start_date
336 AND ppa_gen.effective_date
337 AND ppa_run.action_type IN ('R','Q','V','B')
338 AND ppa_run.action_status = 'C'
339 AND ppa_run.consolidation_set_id = NVL(l_consolidation_set_id,
340 ppa_run.consolidation_set_id)
341 AND ppa_run.payroll_id = NVL(l_payroll_id,
342 ppa_run.payroll_id)
343 AND ppa_run.payroll_action_id = act_run.payroll_action_id
344 AND act_run.action_status = 'C'
345 AND act_run.tax_unit_id = NVL(l_tax_unit_id,
346 act_run.tax_unit_id)
347 AND asg.assignment_id = act_run.assignment_id
348 AND ppa_run.effective_date BETWEEN asg.effective_start_date
349 AND asg.effective_end_date
350 AND asg.business_group_id = ppa_gen.business_group_id
351 AND asg.person_id BETWEEN stperson
352 AND endperson
353 AND EXISTS (
354 select NULL
355 FROM pay_run_results rr,
356 pay_element_types_f e,
357 pay_element_type_extra_info ei
358 WHERE rr.assignment_action_id = act_run.assignment_action_id
359 AND rr.element_type_id = e.element_type_id
360 AND e.element_type_id = ei.element_type_id
361 AND ei.information_type = 'US_TIAA_CREF_CONT_TYPES'
362 AND ppa_run.effective_date BETWEEN e.effective_start_date AND e.effective_end_date
363 )
364 group by asg.assignment_id;
365
366 CURSOR c_defbal IS
367 SELECT TO_NUMBER(ue.creator_id) creator_id,
368 di.user_name
369 FROM ff_user_entities ue,
370 ff_database_items di
371 WHERE di.user_name IN ( 'GROSS_EARNINGS_ASG_GRE_RUN',
372 'PAYMENTS_ASG_GRE_RUN' )
373 AND ue.user_entity_id = di.user_entity_id
374 AND ue.creator_type = 'B'
375 AND NVL(ue.legislation_code,'US') = 'US';
376
377 l_defbal c_defbal%ROWTYPE;
378 no_userid EXCEPTION;
379 lockingactid NUMBER;
380 lockedactid NUMBER;
381 assignid NUMBER;
382 greid NUMBER;
383 num NUMBER;
384 action_type VARCHAR2(1);
385 l_payments_bal NUMBER;
386 l_gross_defined_balance_id NUMBER;
387 l_payments_defined_balance_id NUMBER;
388 BEGIN
389 hr_utility.set_location('Entering : '||l_proc_name, 10);
390
391 OPEN c_parameters(pactid);
392 FETCH c_parameters INTO leg_param,
393 l_consolidation_set_id,
394 l_payroll_id,
395 l_tax_unit_id;
396 CLOSE c_parameters;
397 hr_utility.set_location('..Parameters are :'||leg_param, 15);
398 BEGIN
399 OPEN c_defbal;
400 LOOP
401 FETCH c_defbal INTO l_defbal;
402 EXIT WHEN c_defbal%NOTFOUND;
403 IF l_defbal.user_name= 'GROSS_EARNINGS_ASG_GRE_RUN' THEN
404 l_gross_defined_balance_id:=l_defbal.creator_id;
405 ELSIF l_defbal.user_name= 'PAYMENTS_ASG_GRE_RUN' THEN
406 l_payments_defined_balance_id:=l_defbal.creator_id;
407 END IF;
408 END LOOP;
409 CLOSE c_defbal;
410
411 IF l_gross_defined_balance_id IS NULL OR
412 l_payments_defined_balance_id IS NULL THEN
413 RAISE no_userid;
414 END IF;
415 EXCEPTION WHEN no_userid THEN
416 hr_utility.trace('Error getting defined balance id');
417 RAISE;
418 END;
419 --
420 -- Open the Assignment Action Creation Cursor
421 --
422 hr_utility.set_location('..Opening the Assignment Action Creation Cursor', 20);
423 OPEN c_actions_ops(pactid,stperson,endperson);
424 LOOP
425 FETCH c_actions_ops INTO lockedactid,assignid;
426 IF c_actions_ops%FOUND THEN
427
428 SELECT pay_assignment_actions_s.nextval
429 INTO lockingactid
430 FROM dual;
431 -- Insert a record into pay_temp_object_actions
432 hr_nonrun_asact.insact(lockingactid =>lockingactid,
433 object_id =>assignid,
434 pactid =>pactid,
435 chunk =>chunk );
436 END IF;
437
438 EXIT WHEN c_actions_ops%NOTFOUND;
439
440 END LOOP;
441 CLOSE c_actions_ops;
442 hr_utility.set_location('Leaving : '||l_proc_name, 90);
443 -- hr_utility.trace_on(null, 'TIAA');
444 EXCEPTION
445 WHEN OTHERS THEN
446 hr_utility.set_location('..Error in '||l_proc_name||' : '||SQLERRM,150);
447 hr_utility.set_location('Leaving : '||l_proc_name, 150);
448 RAISE;
449 END action_creation_ops;
450
451 -- ----------------------------------------------------------------------------
452 -- |------------------------< get_amount >----------------------------|
453 -- ---------------------------------------------------------------------------
454 FUNCTION get_amount(p_bal_type_id IN NUMBER,
455 p_assactid IN NUMBER)
456 RETURN NUMBER IS
457
458 CURSOR csr_def_bal_id IS
459 SELECT db.defined_balance_id
460 FROM pay_defined_balances db,
461 pay_balance_dimensions bd
462 WHERE bd.dimension_name IN ('Assignment-Level Current Run' ,'Assignment Default Run')
463 AND bd.balance_dimension_id = db.balance_dimension_id
464 AND db.balance_type_id = p_bal_type_id;
465
466 l_def_bal_id number;
467 l_value number;
468 l_proc_name varchar2(50) := 'pqp_ustiaa_pkg.get_amount';
469
470 BEGIN
471
472 hr_utility.set_location('Entering : '||l_proc_name, 10);
473
474 OPEN csr_def_bal_id;
475 fetch csr_def_bal_id INTO l_def_bal_id;
476 CLOSE csr_def_bal_id;
477
478 --hr_utility.set_location('l_def_bal_id : '||l_def_bal_id, 12);-
479 --hr_utility.set_location('p_bal_type_id : '||p_bal_type_id, 14);
480 --hr_utility.set_location('p_assactid : '||p_assactid, 16);
481
482 IF l_def_bal_id is not null THEN
483 l_value := pay_balance_pkg.get_value
484 (p_defined_balance_id => l_def_bal_id,
485 p_assignment_action_id => p_assactid );
486 END IF;
487
488 RETURN(l_value);
489
490 hr_utility.set_location('Leaving : '||l_proc_name, 20);
491
492 END get_amount;
493
494 -- ------------------------------------------------------------------------------
495 -- |------------------------< get_plan_no >----------------------------|
496 -- -----------------------------------------------------------------------------
497 FUNCTION get_plan_no(p_asg_id IN NUMBER,
498 p_date IN DATE,
499 p_tax_unit_id IN NUMBER)
500 RETURN VARCHAR2 IS
501
502 CURSOR csr_asg_plan_no IS
503 SELECT paei.aei_information2,
504 pasg.payroll_id
505 FROM per_assignment_extra_info paei ,
506 per_assignments_f pasg
507 WHERE pasg.assignment_id = p_asg_id
508 AND pasg.assignment_id = paei.assignment_id(+)
509 AND paei.information_type(+) = 'PQP_US_TIAA_CREF_CODES'
510 AND p_date BETWEEN pasg.effective_start_date
511 AND pasg.effective_end_date;
512
513 CURSOR csr_pay_plan_no (p_payroll_id number) IS
514 SELECT prl.prl_information14
515 FROM pay_payrolls_f prl
516 WHERE prl.payroll_id = p_payroll_id
517 AND prl.prl_information_category = 'US'
518 AND p_date BETWEEN prl.effective_start_date
519 AND prl.effective_end_date;
520
521 CURSOR csr_org_plan_no IS
522 SELECT org_information2
523 FROM hr_organization_information
524 WHERE org_information_context = 'PQP_US_TIAA_CREF_CODES'
525 AND organization_id = p_tax_unit_id;
526
527 l_payroll_id NUMBER;
528 l_plan_no VARCHAR2(6);
529
530 BEGIN
531
532 OPEN csr_asg_plan_no;
533 fetch csr_asg_plan_no
534 INTO l_plan_no,
535 l_payroll_id;
536 CLOSE csr_asg_plan_no;
537
538 IF l_plan_no is not null THEN
539 RETURN l_plan_no;
540 ELSE
541 OPEN csr_pay_plan_no(l_payroll_id);
542 fetch csr_pay_plan_no INTO l_plan_no;
543 CLOSE csr_pay_plan_no;
544 END IF;
545
546 IF l_plan_no is not null THEN
547 RETURN l_plan_no;
548 ELSE
549 OPEN csr_org_plan_no;
550 FETCH csr_org_plan_no INTO l_plan_no;
551 CLOSE csr_org_plan_no;
552 END IF;
553
554 RETURN l_plan_no;
555
556 END get_plan_no;
557
558 -- -------------------------------------------------------------------------------------
559 -- |--------------------< chk_cont_type_override >-----------------------|
560 -- ------------------------------------------------------------------------------------
561 PROCEDURE chk_cont_type_override(p_asg_id IN NUMBER,
562 p_ele_entry_id IN NUMBER,
563 p_source IN out nocopy VARCHAR2,
564 p_sub_plan IN out nocopy VARCHAR2) IS
565
566 CURSOR csr_asg_info IS
567 select pei.aei_information1,pei.aei_information2
568 from per_assignment_extra_info pei
569 where pei.assignment_id = p_asg_id
570 and pei.aei_information_category = 'PAY_US_TIAA_CREF_CONT_TYPE'
571 and pei.aei_information3 = p_ele_entry_id;
572
573 l_proc_name varchar2(50) := 'pqp_ustiaa_pkg.chk_cont_type_override';
574 l_sub_plan varchar2(5);
575 l_source varchar2(2);
576 BEGIN
577 hr_utility.set_location('Entering : '||l_proc_name, 10);
578
579 OPEN csr_asg_info;
580 FETCH csr_asg_info INTO l_source, l_sub_plan;
581 IF csr_asg_info%found THEN
582 if l_source is not null then
583 p_source := l_source;
584 end if;
585 if l_sub_plan is not null then
586 p_sub_plan := l_sub_plan;
587 end if;
588 END IF;
589 CLOSE csr_asg_info;
590
591 hr_utility.set_location('Leaving : '||l_proc_name, 20);
592
593 END chk_cont_type_override;
594
595 -- -------------------------------------------------------------------
596 -- |--------------------< chk_pri_bal >-----------------------|
597 -- -------------------------------------------------------------------
598 FUNCTION chk_pri_bal(p_asgact_id NUMBER,
599 p_date DATE) RETURN boolean IS
600
601 Cursor csr_chk_dup_bal_types is
602 SELECT count(*)
603 FROM pay_run_results rr,
604 pay_element_types_f e,
605 pay_element_type_extra_info ei
606 WHERE rr.assignment_action_id = p_asgact_id
607 AND rr.element_type_id = e.element_type_id
608 AND e.element_type_id = ei.element_type_id
609 AND ei.information_type = 'US_TIAA_CREF_CONT_TYPES'
610 AND p_date BETWEEN e.effective_start_date AND e.effective_end_date
611 AND rr.entry_type not in ('A','R')
612 and rr.rowid > (SELECT min(rr1.rowid)
613 FROM pay_run_results rr1,
614 pay_element_types_f e1,
615 pay_element_type_extra_info ei1
616 WHERE rr1.assignment_action_id = rr.assignment_action_id
617 AND rr1.element_type_id = e1.element_type_id
618 AND e1.element_type_id = ei1.element_type_id
619 AND ei1.information_type = 'US_TIAA_CREF_CONT_TYPES'
620 AND e1.element_information10 = e.element_information10
621 AND p_date BETWEEN e1.effective_start_date AND e1.effective_end_date
622 AND rr1.entry_type not in ('A','R'));
623
624 l_cnt number := 0;
625
626 BEGIN
627
628 OPEN csr_chk_dup_bal_types;
629 FETCH csr_chk_dup_bal_types INTO l_cnt;
630 CLOSE csr_chk_dup_bal_types;
631
632 IF l_cnt > 0 THEN
633 RETURN TRUE;
634 ELSE
635 RETURN FALSE;
636 END IF;
637
638 END chk_pri_bal;
639
640 -- ---------------------------------------------------------------------
641 -- |-------------------------< load_xml >----------------------------|
642 -- ---------------------------------------------------------------------
643 PROCEDURE LOAD_XML (
644 P_NODE_TYPE varchar2,
645 P_NODE varchar2,
646 P_DATA varchar2
647 ) AS
648
649 l_proc_name varchar2(100) := 'pqp_ustiaa_pkg.load_xml';
650 l_data varchar2(500);
651 BEGIN
652
653 hr_utility.set_location('Entering : '||l_proc_name, 10);
654
655 IF p_node_type = 'CS' THEN
656 pay_core_files.write_to_magtape_lob('<'||p_node||'>');
657 ELSIF p_node_type = 'CE' THEN
658 pay_core_files.write_to_magtape_lob('</'||p_node||'>');
659 ELSIF p_node_type = 'D' THEN
660 /* Handle special charaters in data */
661 l_data := REPLACE (p_data, '&', '&');
662 l_data := REPLACE (l_data, '>', '>');
663 l_data := REPLACE (l_data, '<', '<');
664 l_data := REPLACE (l_data, '''', ''');
665 l_data := REPLACE (l_data, '"', '"');
666 pay_core_files.write_to_magtape_lob('<'||p_node||'>'||l_data||'</'||p_node||'>');
667 END IF;
668
669 hr_utility.set_location('Leaving : '||l_proc_name, 20);
670
671 END LOAD_XML;
672
673 -- ---------------------------------------------------------------------
674 -- |-----------------------< generate_header >--------------------------|
675 -- --------------------------------------------------------------------
676 PROCEDURE generate_header_xml is
677 l_proc_name varchar2(50) := 'pqp_ustiaa_pkg.generate_header_xml';
678
679 CURSOR csr_rpt_data(pactid number) IS
680 select ppa.start_date,
681 ppa.effective_date,
682 bg.name
683 from per_business_groups bg,
684 pay_payroll_actions ppa
685 where ppa.payroll_action_id = pactid
686 and ppa.business_group_id = bg.business_group_id;
687
688 l_payroll_action_id number;
689 l_start_date date;
690 l_end_date date;
691 l_bg_name per_business_groups.name%TYPE;
692
693 BEGIN
694 hr_utility.set_location('Entering : '||l_proc_name, 10);
695
696 l_payroll_action_id := pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID');
697 IF (l_payroll_action_id is null) THEN
698 l_payroll_action_id := pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID');
699 END IF;
700 -- hr_utility.set_location('l_payroll_action_id : '||l_payroll_action_id, 15);
701
702 OPEN csr_rpt_data(l_payroll_action_id);
703 FETCH csr_rpt_data INTO
704 l_start_date,
705 l_end_date,
706 l_bg_name;
707 CLOSE csr_rpt_data;
708
709 load_xml('CS','US_TIAA_CREF','');
710 load_xml('D','BG_NAME',l_bg_name);
711 load_xml('D','RPT_START_DATE',l_start_date);
712 load_xml('D','RPT_END_DATE',l_end_date);
713
714 -- hr_utility.set_location('l_bg_name : '||l_bg_name, 15);
715
716 hr_utility.set_location('Leaving : '||l_proc_name, 20);
717
718
719 END generate_header_xml;
720
721 -- ---------------------------------------------------------------------
722 -- |-----------------------< generate_footer >--------------------------|
723 -- --------------------------------------------------------------------
724 PROCEDURE generate_footer_xml is
725 l_proc_name varchar2(50) := 'pqp_ustiaa_pkg.generate_footer_xml';
726 BEGIN
727
728 hr_utility.set_location('Entering : '||l_proc_name, 10);
729
730 load_xml('CE','US_TIAA_CREF','');
731
732 hr_utility.set_location('Leaving : '||l_proc_name, 20);
733
734 END generate_footer_xml;
735
736 -- ---------------------------------------------------------------------
737 -- |------------------------< generate_record >-------------------------|
738 -- ---------------------------------------------------------------------
739 PROCEDURE generate_record IS
740 l_proc_name VARCHAR2 (100) := 'pqp_ustiaa_pkg.generate_record';
741 l_last_name per_all_people_f.last_name%TYPE;
742 l_first_name per_all_people_f.first_name%TYPE;
743 l_middle_name per_all_people_f.middle_names%TYPE;
744 l_ssn per_all_people_f.national_identifier%TYPE;
745 l_gender per_all_people_f.sex%TYPE;
746 l_gender1 per_all_people_f.sex%TYPE;
747 l_dob per_all_people_f.date_of_birth%TYPE;
748 l_asg_id per_all_assignments_f.assignment_id%TYPE;
749 l_asg_no per_all_assignments_f.assignment_number%TYPE;
750 l_addr1 per_addresses.address_line1%TYPE;
751 l_addr2 per_addresses.address_line2%TYPE;
752 l_addr3 per_addresses.address_line3%TYPE;
753 l_country per_addresses.country%TYPE;
754 l_city per_addresses.town_or_city%TYPE;
755 l_state per_addresses.region_2%TYPE;
756 l_state1 per_addresses.region_2%TYPE;
757 l_postal_code per_addresses.postal_code%TYPE;
758 l_postal_code1 per_addresses.postal_code%TYPE;
759 l_plan_no varchar2(6);
760 l_plan_no1 varchar2(6);
761 l_pay_date date;
762 l_tax_unit_id number;
763 l_payroll_id number;
764 l_sub_plan varchar2(5);
765 l_source varchar2(2);
766 l_mode varchar2(3);
767 l_pay_freq varchar2(1);
768 l_amount number;
769 l_period_type varchar2(30);
770 l_fmt_amount varchar2(30);
771 l_assignment_action_id number;
772 l_locked_act_id number;
773 l_err_msg varchar2(1000);
774 l_err boolean := FALSE;
775 leg_param pay_payroll_actions.legislative_parameters%TYPE;
776 l_consolidation_set_id NUMBER;
777
778 CURSOR csr_per_detail (p_asg_id number,
779 p_date date) IS
780 select papf.last_name last_name,
781 papf.first_name first_name,
782 papf.middle_names middle_name,
783 papf.national_identifier ssn,
784 papf.sex gender,
785 papf.date_of_birth dob,
786 padd.address_line1 addr1,
787 padd.address_line2 addr2,
788 padd.address_line3 addr3,
789 padd.country country,
790 padd.town_or_city city,
791 padd.region_2 state,
792 padd.postal_code postal_code,
793 paf.assignment_number
794 from per_all_assignments_f paf
795 , per_all_people_f papf
796 , per_addresses padd
797 where paf.assignment_id = p_asg_id
798 and paf.person_id = papf.person_id
799 and padd.person_id = papf.person_id
800 and padd.primary_flag = 'Y'
801 and p_date between paf.effective_start_date and paf.effective_end_date
802 and p_date between papf.effective_start_date and papf.effective_end_date
803 and p_date between padd.date_from and nvl(padd.date_to,p_date);
804
805 CURSOR csr_get_asg_actions(p_assactid number) IS
806 select paa.assignment_id asg_id,
807 paa.assignment_action_id act_id,
808 ppa_run.effective_date pay_date,
809 paa.tax_unit_id tax_unit_id
810 from pay_payroll_actions ppa_gen,
811 pay_assignment_actions paa,
812 pay_payroll_actions ppa_run,
813 pay_temp_object_actions poa
814 where poa.object_action_id = p_assactid
815 and poa.payroll_action_id = ppa_gen.payroll_action_id
816 and poa.object_id = paa.assignment_id
817 and paa.payroll_action_id = ppa_run.payroll_action_id
818 and (paa.source_action_id is not null
819 or (paa.source_action_id is null and ppa_run.action_type in ('B','V')))
820 and ppa_run.effective_date between ppa_gen.start_date and ppa_gen.effective_date
821 and ppa_run.action_type in ('R','Q','B','V')
822 and ppa_run.action_status = 'C'
823 AND ppa_run.consolidation_set_id = NVL(l_consolidation_set_id,
824 ppa_run.consolidation_set_id)
825 AND ppa_run.payroll_id = NVL(l_payroll_id,
826 ppa_run.payroll_id)
827 AND paa.tax_unit_id = NVL(l_tax_unit_id,
828 paa.tax_unit_id)
829 AND EXISTS (
830 select NULL
831 from pay_element_entries_f ee,
832 pay_element_types_f e,
833 pay_element_type_extra_info ei
834 where ee.assignment_id = paa.assignment_id
835 and ee.element_type_id = e.element_type_id
836 and ei.element_type_id = e.element_type_id
837 and ei.information_type = 'US_TIAA_CREF_CONT_TYPES'
838 and ppa_run.effective_date between e.effective_start_date and e.effective_end_date
839 and ppa_run.effective_date between ee.effective_start_date and ee.effective_end_date
840 );
841
842 CURSOR csr_get_ele_entries(p_asgact_id NUMBER,
843 p_date DATE) IS
844 SELECT rr.element_entry_id ele_entry_id,
845 ei.eei_information1 cont_source,
846 ei.eei_information2 cont_sub_plan,
847 ei.eei_information3 plan_no,
848 e.element_information10 bal_type_id,
849 e.element_name
850 FROM pay_run_results rr,
851 pay_element_types_f e,
852 pay_element_type_extra_info ei
853 WHERE rr.assignment_action_id = p_asgact_id
854 AND rr.element_type_id = e.element_type_id
855 AND e.element_type_id = ei.element_type_id
856 AND ei.information_type = 'US_TIAA_CREF_CONT_TYPES'
857 AND p_date BETWEEN e.effective_start_date AND e.effective_end_date
858 AND rr.entry_type not in ('A','R');
859
860 CURSOR csr_payroll_details(p_asg_id number,
861 p_date date) IS
862 SELECT prl.prl_information4,
863 prl.period_type
864 FROM per_all_assignments_f paf,
865 pay_payrolls_f prl
866 WHERE paf.assignment_id = p_asg_id
867 AND prl.payroll_id = paf.payroll_id
868 AND prl.prl_information_category = 'US'
869 AND p_date BETWEEN paf.effective_start_date
870 AND paf.effective_end_date
871 AND p_date BETWEEN prl.effective_start_date
872 AND prl.effective_end_date;
873
874 CURSOR c_parameters (asg_actid NUMBER) IS
875 SELECT ppa.legislative_parameters,
876 pqp_ustiaa_pkg.get_parameter('TRANSFER_CONC_SET',ppa.legislative_parameters),
877 pqp_ustiaa_pkg.get_parameter('TRANSFER_PAYROLL',ppa.legislative_parameters),
878 pqp_ustiaa_pkg.get_parameter('TRANSFER_GRE',ppa.legislative_parameters)
879 FROM pay_payroll_actions ppa,
880 pay_temp_object_actions poa
881 WHERE poa.object_action_id = asg_actid
882 and ppa.payroll_action_id = poa.payroll_action_id;
883
884 Begin
885
886 l_assignment_action_id := pay_magtape_generic.get_parameter_value('TRANSFER_ACT_ID');
887 l_err := FALSE;
888 l_err_msg := '';
889
890 OPEN c_parameters(l_assignment_action_id);
891 FETCH c_parameters INTO leg_param,
892 l_consolidation_set_id,
893 l_payroll_id,
894 l_tax_unit_id;
895 CLOSE c_parameters;
896
897 /* Validations */
898 FOR a IN csr_get_asg_actions(l_assignment_action_id) LOOP
899
900 OPEN csr_per_detail(a.asg_id, a.pay_date);
901 FETCH csr_per_detail INTO
902 l_last_name,
903 l_first_name,
904 l_middle_name,
905 l_ssn,
906 l_gender,
907 l_dob,
908 l_addr1,
909 l_addr2,
910 l_addr3,
911 l_country,
912 l_city,
913 l_state,
914 l_postal_code,
915 l_asg_no;
916 CLOSE csr_per_detail;
917
918 OPEN csr_payroll_details(a.asg_id, a.pay_date);
919 FETCH csr_payroll_details INTO
920 l_mode,
921 l_period_type;
922 CLOSE csr_payroll_details;
923
924 l_plan_no1 := get_plan_no(a.asg_id,a.pay_date,a.tax_unit_id);
925
926 IF l_mode is null THEN
927 l_err := TRUE;
928 l_err_msg := ' Mode is Null';
929 END IF;
930
931 /* Check for uniqueness of Primary Balance for element entries */
932 IF chk_pri_bal(a.act_id,a.pay_date) THEN
933 l_err := TRUE;
934 l_err_msg := l_err_msg || ' Primary Balance is not unique across element entries';
935 END IF;
936
937 FOR i IN csr_get_ele_entries(a.act_id, a.pay_date) loop
938
939 IF(i.bal_type_id is not null) THEN
940 l_amount := get_amount(i.bal_type_id,a.act_id);
941 ELSE
942 l_err := TRUE;
943 l_err_msg := l_err_msg || ' Primary Balance required for Element '||i.element_name;
944 END IF;
945
946 l_source := i.cont_source;
947 l_sub_plan := i.cont_sub_plan;
948
949 IF (i.plan_no is not null) THEN
950 l_plan_no := i.plan_no;
951 ELSE
952 l_plan_no := l_plan_no1;
953 END IF;
954
955 /* Use Overides for source and sub plan if present at Asg level*/
956 chk_cont_type_override(a.asg_id, i.ele_entry_id, l_source, l_sub_plan);
957
958 IF l_plan_no is null THEN
959 l_err := TRUE;
960 l_err_msg := l_err_msg || ' Plan Number is Null.';
961 END IF;
962
963 IF l_source is null THEN
964 l_err := TRUE;
965 l_err_msg := l_err_msg || ' Contribution Source is Null for Element'||i.element_name;
966 END IF;
967
968 IF l_sub_plan is null THEN
969 l_err := TRUE;
970 l_err_msg := l_err_msg || ' Contribution Sub Plan is Null for Element'||i.element_name;
971 END IF;
972 END LOOP; -- for ele entries
973
974 END LOOP; -- for asg actions
975
976
977 /* Generate XML */
978 IF l_err THEN
979
980 load_xml('CS','INVALID_DATA','');
981 load_xml('D','EMP_NAME',l_last_name||','||l_first_name||' '||substr(l_middle_name,1,1));
982 load_xml('D','ASG_NO',l_asg_no);
983 load_xml('D','ERROR_TEXT',l_err_msg);
984 load_xml('CE','INVALID_DATA','');
985
986 ELSE
987
988 FOR a IN csr_get_asg_actions(l_assignment_action_id) LOOP
989
990 /* Get Person Details*/
991 OPEN csr_per_detail(a.asg_id, a.pay_date);
992 FETCH csr_per_detail INTO
993 l_last_name,
994 l_first_name,
995 l_middle_name,
996 l_ssn,
997 l_gender,
998 l_dob,
999 l_addr1,
1000 l_addr2,
1001 l_addr3,
1002 l_country,
1003 l_city,
1004 l_state,
1005 l_postal_code,
1006 l_asg_no;
1007 CLOSE csr_per_detail;
1008
1009 /* Get Payroll Details*/
1010 OPEN csr_payroll_details(a.asg_id, a.pay_date);
1011 FETCH csr_payroll_details INTO
1012 l_mode,
1013 l_period_type;
1014 CLOSE csr_payroll_details;
1015
1016 /* Get Payroll Details*/
1017 l_plan_no1 := get_plan_no(a.asg_id,a.pay_date,a.tax_unit_id);
1018
1019
1020 FOR i IN csr_get_ele_entries(a.act_id, a.pay_date) loop
1021
1022 IF(i.bal_type_id is not null) THEN
1023 l_amount := get_amount(i.bal_type_id,a.act_id);
1024 END IF;
1025
1026 l_source := i.cont_source;
1027 l_sub_plan := i.cont_sub_plan;
1028
1029 IF (i.plan_no is not null) THEN
1030 l_plan_no := i.plan_no;
1031 ELSE
1032 l_plan_no := l_plan_no1;
1033 END IF;
1034
1035 /* Use Overides for source and sub plan if present at Asg level*/
1036 chk_cont_type_override(a.asg_id, i.ele_entry_id, l_source, l_sub_plan);
1037
1038 IF l_amount <> 0 THEN
1039
1040 l_pay_freq :=
1041 CASE when l_period_type = 'Year' then '1'
1042 when l_period_type = 'Semi-Year' then '2'
1043 when l_period_type = 'Quarter' then '3'
1044 when l_period_type = 'Calendar Month' then '4'
1045 when l_period_type = 'Lunar Month' then '4'
1046 when l_period_type = 'Semi-Month' then '5'
1047 when l_period_type = 'Bi-Month' then '5'
1048 when l_period_type = 'Bi-Week' then '6'
1049 when l_period_type = 'Week' then '7'
1050 END;
1051
1052 IF l_amount > 0 THEN
1053 l_fmt_amount := round(l_amount*100,0);
1054 ELSE
1055 l_fmt_amount := substr(round(l_amount*(-100),0),1,length(round(l_amount*(-100),0))-1)
1056 || translate(substr(round(l_amount*(-100),0),-1,1),'0123456789','}JKLMNOPQR');
1057 END IF;
1058
1059 load_xml('CS','G_EMPLOYEE_DATA','');
1060 load_xml('D','PLAN_NO',l_plan_no);
1061 load_xml('D','MODE',l_mode);
1062 load_xml('D','SSN',l_ssn);
1063 load_xml('D','EMP_NAME',l_last_name||','||l_first_name||' '||substr(l_middle_name,1,1));
1064 load_xml('D','GENDER',l_gender);
1065 load_xml('D','DOB',replace(substr(nvl(fnd_date.date_to_canonical(l_dob),'0'),1,10),'/','-'));
1066 load_xml('D','ADD_LINE1',l_addr1);
1067 load_xml('D','ADD_LINE2',l_addr2);
1068 load_xml('D','ADD_LINE3',l_addr3);
1069 load_xml('D','CITY',l_city);
1070 load_xml('D','STATE',l_state);
1071 load_xml('D','COUNTRY',l_country);
1072 load_xml('D','ZIP',l_postal_code);
1073 load_xml('D','PAYROLL_FREQ',l_pay_freq);
1074 load_xml('D','PERIOD_TYPE',l_period_type);
1075 load_xml('D','PAYROLL_DATE',to_char(replace(substr(nvl(fnd_date.date_to_canonical(a.pay_date),'0'),1,10),'/','-')));
1076 load_xml('D','SOURCE',l_source);
1077 load_xml('D','AMOUNT',round(l_amount,2));
1078 load_xml('D','FMT_AMOUNT',l_fmt_amount);
1079 load_xml('D','SUB_PLAN',l_sub_plan);
1080 load_xml('CE','G_EMPLOYEE_DATA','');
1081
1082 END IF;
1083
1084 END LOOP; -- for ele entries
1085
1086 END LOOP; -- for asg actions
1087
1088 END IF;
1089
1090 END generate_record;
1091
1092 END PQP_UStiaa_pkg;