[Home] [Help]
PACKAGE BODY: APPS.PQP_USTIAA_PKG
Source
1 package body pqp_ustiaa_pkg AS
2 /* $Header: pqustiaa.pkb 120.9.12020000.2 2012/07/05 12:24:44 amnaraya ship $ */
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_mar_status per_all_people_f.marital_status%TYPE;
746 l_gender per_all_people_f.sex%TYPE;
747 l_gender1 per_all_people_f.sex%TYPE;
748 l_employee_number per_all_people_f.employee_number%TYPE;
749 l_title per_all_people_f.title%TYPE;
750 l_dob per_all_people_f.date_of_birth%TYPE;
751 l_email per_all_people_f.email_address%TYPE;
752 l_asg_id per_all_assignments_f.assignment_id%TYPE;
753 l_asg_no per_all_assignments_f.assignment_number%TYPE;
754 l_loc_id per_all_assignments_f.location_id%TYPE;
755 l_addr1 per_addresses.address_line1%TYPE;
756 l_addr2 per_addresses.address_line2%TYPE;
757 l_addr3 per_addresses.address_line3%TYPE;
758 l_country per_addresses.country%TYPE;
759 l_city per_addresses.town_or_city%TYPE;
760 l_state per_addresses.region_2%TYPE;
761 l_state1 per_addresses.region_2%TYPE;
762 l_postal_code per_addresses.postal_code%TYPE;
763 l_postal_code1 per_addresses.postal_code%TYPE;
764 l_day_phone per_addresses.telephone_number_1%TYPE;
765 l_evening_phone per_addresses.telephone_number_2%TYPE;
766 l_hire_date per_periods_of_service.date_start%TYPE;
767 l_term_date per_periods_of_service.actual_termination_date%TYPE;
768 l_adj_date per_periods_of_service.adjusted_svc_date%TYPE;
769 l_plan_no varchar2(6);
770 l_plan_no1 varchar2(6);
771 l_pay_date date;
772 l_tax_unit_id number;
773 l_payroll_id number;
774 l_sub_plan varchar2(5);
775 l_source varchar2(2);
776 l_mode varchar2(3);
777 l_pay_freq varchar2(1);
778 l_amount number;
779 l_period_type varchar2(30);
780 l_fmt_amount varchar2(30);
781 l_assignment_action_id number;
782 l_locked_act_id number;
783 l_err_msg varchar2(1000);
784 l_err boolean := FALSE;
785 leg_param pay_payroll_actions.legislative_parameters%TYPE;
786 l_consolidation_set_id NUMBER;
787
788 CURSOR csr_per_detail (p_asg_id number,
789 p_date date) IS
790 select papf.last_name last_name,
791 papf.first_name first_name,
792 papf.middle_names middle_name,
793 papf.national_identifier ssn,
794 papf.marital_status,
795 papf.sex gender,
796 papf.employee_number,
797 papf.title,
798 papf.date_of_birth dob,
799 papf.email_address,
800 padd.address_line1 addr1,
801 padd.address_line2 addr2,
802 padd.address_line3 addr3,
803 padd.country country,
804 padd.town_or_city city,
805 padd.region_2 state,
806 padd.postal_code postal_code,
807 padd.telephone_number_1,
808 padd.telephone_number_2,
809 paf.assignment_number,
810 paf.location_id,
811 ppos.date_start,
812 ppos.actual_termination_date,
813 ppos.adjusted_svc_date
814 from per_all_assignments_f paf
815 , per_all_people_f papf
816 , per_addresses padd
817 , per_periods_of_service ppos
818 where paf.assignment_id = p_asg_id
819 and paf.person_id = papf.person_id
820 and padd.person_id = papf.person_id
821 and paf.person_id = ppos.person_id
822 and padd.person_id = ppos.person_id
823 and padd.primary_flag = 'Y'
824 and p_date between paf.effective_start_date and paf.effective_end_date
825 and p_date between papf.effective_start_date and papf.effective_end_date
826 and p_date between padd.date_from and nvl(padd.date_to,p_date);
827
828 CURSOR csr_get_asg_actions(p_assactid number) IS
829 select paa.assignment_id asg_id,
830 paa.assignment_action_id act_id,
831 ppa_run.effective_date pay_date,
832 paa.tax_unit_id tax_unit_id
833 from pay_payroll_actions ppa_gen,
834 pay_assignment_actions paa,
835 pay_payroll_actions ppa_run,
836 pay_temp_object_actions poa
837 where poa.object_action_id = p_assactid
838 and poa.payroll_action_id = ppa_gen.payroll_action_id
839 and poa.object_id = paa.assignment_id
840 and paa.payroll_action_id = ppa_run.payroll_action_id
841 and (paa.source_action_id is not null
842 or (paa.source_action_id is null and ppa_run.action_type in ('B','V')))
843 and ppa_run.effective_date between ppa_gen.start_date and ppa_gen.effective_date
844 and ppa_run.action_type in ('R','Q','B','V')
845 and ppa_run.action_status = 'C'
846 AND ppa_run.consolidation_set_id = NVL(l_consolidation_set_id,
847 ppa_run.consolidation_set_id)
848 AND ppa_run.payroll_id = NVL(l_payroll_id,
849 ppa_run.payroll_id)
850 AND paa.tax_unit_id = NVL(l_tax_unit_id,
851 paa.tax_unit_id)
852 AND EXISTS (
853 select NULL
854 from pay_element_entries_f ee,
855 pay_element_types_f e,
856 pay_element_type_extra_info ei
857 where ee.assignment_id = paa.assignment_id
858 and ee.element_type_id = e.element_type_id
859 and ei.element_type_id = e.element_type_id
860 and ei.information_type = 'US_TIAA_CREF_CONT_TYPES'
861 and ppa_run.effective_date between e.effective_start_date and e.effective_end_date
862 -- Condition added as part of the Bug: 11869238
863 and NVL(pay_paywsmee_pkg.PROCESSED(ee.element_entry_id,ee.original_entry_id,e.processing_type,ee.entry_type,ppa_run.date_earned),'N')='Y'
864 -- and ppa_run.effective_date between ee.effective_start_date and ee.effective_end_date
865 );
866
867 CURSOR csr_get_ele_entries(p_asgact_id NUMBER,
868 p_date DATE) IS
869 SELECT rr.element_entry_id ele_entry_id,
870 ei.eei_information1 cont_source,
871 ei.eei_information2 cont_sub_plan,
872 ei.eei_information3 plan_no,
873 e.element_information10 bal_type_id,
874 e.element_name
875 FROM pay_run_results rr,
876 pay_element_types_f e,
877 pay_element_type_extra_info ei
878 WHERE rr.assignment_action_id = p_asgact_id
879 AND rr.element_type_id = e.element_type_id
880 AND e.element_type_id = ei.element_type_id
881 AND ei.information_type = 'US_TIAA_CREF_CONT_TYPES'
882 AND p_date BETWEEN e.effective_start_date AND e.effective_end_date
883 AND rr.entry_type not in ('A','R');
884
885 CURSOR csr_payroll_details(p_asg_id number,
886 p_date date) IS
887 SELECT prl.prl_information4,
888 prl.period_type
889 FROM per_all_assignments_f paf,
890 pay_payrolls_f prl
891 WHERE paf.assignment_id = p_asg_id
892 AND prl.payroll_id = paf.payroll_id
893 AND prl.prl_information_category = 'US'
894 AND p_date BETWEEN paf.effective_start_date
895 AND paf.effective_end_date
896 AND p_date BETWEEN prl.effective_start_date
897 AND prl.effective_end_date;
898
899 CURSOR c_parameters (asg_actid NUMBER) IS
900 SELECT ppa.legislative_parameters,
901 pqp_ustiaa_pkg.get_parameter('TRANSFER_CONC_SET',ppa.legislative_parameters),
902 pqp_ustiaa_pkg.get_parameter('TRANSFER_PAYROLL',ppa.legislative_parameters),
903 pqp_ustiaa_pkg.get_parameter('TRANSFER_GRE',ppa.legislative_parameters)
904 FROM pay_payroll_actions ppa,
905 pay_temp_object_actions poa
906 WHERE poa.object_action_id = asg_actid
907 and ppa.payroll_action_id = poa.payroll_action_id;
908
909 Begin
910
911 l_assignment_action_id := pay_magtape_generic.get_parameter_value('TRANSFER_ACT_ID');
912 l_err := FALSE;
913 l_err_msg := '';
914
915 OPEN c_parameters(l_assignment_action_id);
916 FETCH c_parameters INTO leg_param,
917 l_consolidation_set_id,
918 l_payroll_id,
919 l_tax_unit_id;
920 CLOSE c_parameters;
921
922 /* Validations */
923 FOR a IN csr_get_asg_actions(l_assignment_action_id) LOOP
924
925 OPEN csr_per_detail(a.asg_id, a.pay_date);
926 FETCH csr_per_detail INTO
927 l_last_name,
928 l_first_name,
929 l_middle_name,
930 l_ssn,
931 l_mar_status,
932 l_gender,
933 l_employee_number,
934 l_title,
935 l_dob,
936 l_email,
937 l_addr1,
938 l_addr2,
939 l_addr3,
940 l_country,
941 l_city,
942 l_state,
943 l_postal_code,
944 l_day_phone ,
945 l_evening_phone,
946 l_asg_no,
947 l_loc_id,
948 l_hire_date,
949 l_term_date,
950 l_adj_date ;
951 CLOSE csr_per_detail;
952
953 OPEN csr_payroll_details(a.asg_id, a.pay_date);
954 FETCH csr_payroll_details INTO
955 l_mode,
956 l_period_type;
957 CLOSE csr_payroll_details;
958
959 l_plan_no1 := get_plan_no(a.asg_id,a.pay_date,a.tax_unit_id);
960
961 IF l_mode is null THEN
962 l_err := TRUE;
963 l_err_msg := ' Mode is Null';
964 END IF;
965
966 /* Check for uniqueness of Primary Balance for element entries */
967 IF chk_pri_bal(a.act_id,a.pay_date) THEN
968 l_err := TRUE;
969 l_err_msg := l_err_msg || ' Primary Balance is not unique across element entries';
970 END IF;
971
972 FOR i IN csr_get_ele_entries(a.act_id, a.pay_date) loop
973
974 IF(i.bal_type_id is not null) THEN
975 l_amount := get_amount(i.bal_type_id,a.act_id);
976 ELSE
977 l_err := TRUE;
978 l_err_msg := l_err_msg || ' Primary Balance required for Element '||i.element_name;
979 END IF;
980
981 l_source := i.cont_source;
982 l_sub_plan := i.cont_sub_plan;
983
984 IF (i.plan_no is not null) THEN
985 l_plan_no := i.plan_no;
986 ELSE
987 l_plan_no := l_plan_no1;
988 END IF;
989
990 /* Use Overides for source and sub plan if present at Asg level*/
991 chk_cont_type_override(a.asg_id, i.ele_entry_id, l_source, l_sub_plan);
992
993 IF l_plan_no is null THEN
994 l_err := TRUE;
995 l_err_msg := l_err_msg || ' Plan Number is Null.';
996 END IF;
997
998 IF l_source is null THEN
999 l_err := TRUE;
1000 l_err_msg := l_err_msg || ' Contribution Source is Null for Element'||i.element_name;
1001 END IF;
1002
1003 IF l_sub_plan is null THEN
1004 l_err := TRUE;
1005 l_err_msg := l_err_msg || ' Contribution Sub Plan is Null for Element'||i.element_name;
1006 END IF;
1007 END LOOP; -- for ele entries
1008
1009 END LOOP; -- for asg actions
1010
1011
1012 /* Generate XML */
1013 IF l_err THEN
1014
1015 load_xml('CS','INVALID_DATA','');
1016 load_xml('D','EMP_NAME',l_last_name||','||l_first_name||' '||substr(l_middle_name,1,1));
1017 load_xml('D','ASG_NO',l_asg_no);
1018 load_xml('D','ERROR_TEXT',l_err_msg);
1019 load_xml('CE','INVALID_DATA','');
1020
1021 ELSE
1022
1023 FOR a IN csr_get_asg_actions(l_assignment_action_id) LOOP
1024
1025 /* Get Person Details*/
1026 OPEN csr_per_detail(a.asg_id, a.pay_date);
1027 FETCH csr_per_detail INTO
1028 l_last_name,
1029 l_first_name,
1030 l_middle_name,
1031 l_ssn,
1032 l_mar_status,
1033 l_gender,
1034 l_employee_number,
1035 l_title,
1036 l_dob,
1037 l_email,
1038 l_addr1,
1039 l_addr2,
1040 l_addr3,
1041 l_country,
1042 l_city,
1043 l_state,
1044 l_postal_code,
1045 l_day_phone ,
1046 l_evening_phone,
1047 l_asg_no,
1048 l_loc_id,
1049 l_hire_date,
1050 l_term_date,
1051 l_adj_date ;
1052 CLOSE csr_per_detail;
1053
1054 /* Get Payroll Details*/
1055 OPEN csr_payroll_details(a.asg_id, a.pay_date);
1056 FETCH csr_payroll_details INTO
1057 l_mode,
1058 l_period_type;
1059 CLOSE csr_payroll_details;
1060
1061 /* Get Payroll Details*/
1062 l_plan_no1 := get_plan_no(a.asg_id,a.pay_date,a.tax_unit_id);
1063
1064
1065 FOR i IN csr_get_ele_entries(a.act_id, a.pay_date) loop
1066
1067 IF(i.bal_type_id is not null) THEN
1068 l_amount := get_amount(i.bal_type_id,a.act_id);
1069 END IF;
1070
1071 l_source := i.cont_source;
1072 l_sub_plan := i.cont_sub_plan;
1073
1074 IF (i.plan_no is not null) THEN
1075 l_plan_no := i.plan_no;
1076 ELSE
1077 l_plan_no := l_plan_no1;
1078 END IF;
1079
1080 /* Use Overides for source and sub plan if present at Asg level*/
1081 chk_cont_type_override(a.asg_id, i.ele_entry_id, l_source, l_sub_plan);
1082
1083 IF l_amount <> 0 THEN
1084
1085 l_pay_freq :=
1086 CASE when l_period_type = 'Year' then '1'
1087 when l_period_type = 'Semi-Year' then '2'
1088 when l_period_type = 'Quarter' then '3'
1089 when l_period_type = 'Calendar Month' then '4'
1090 when l_period_type = 'Lunar Month' then '4'
1091 when l_period_type = 'Semi-Month' then '5'
1092 when l_period_type = 'Bi-Month' then '5'
1093 when l_period_type = 'Bi-Week' then '6'
1094 when l_period_type = 'Week' then '7'
1095 END;
1096
1097 IF l_amount > 0 THEN
1098 l_fmt_amount := round(l_amount*100,0);
1099 ELSE
1100 l_fmt_amount := substr(round(l_amount*(-100),0),1,length(round(l_amount*(-100),0))-1)
1101 || translate(substr(round(l_amount*(-100),0),-1,1),'0123456789','}JKLMNOPQR');
1102 END IF;
1103
1104 load_xml('CS','G_EMPLOYEE_DATA','');
1105 load_xml('D','PLAN_NO',l_plan_no);
1106 load_xml('D','MODE',l_mode);
1107 load_xml('D','SSN',l_ssn);
1108 load_xml('D','MARITAL_STATUS',l_mar_status);
1109 load_xml('D','EMP_NAME',l_last_name||','||l_first_name||' '||substr(l_middle_name,1,1));
1110 load_xml('D','GENDER',l_gender);
1111 load_xml('D','EMPLOYEE_ID',l_employee_number);
1112 load_xml('D','TITLE',l_title);
1113 load_xml('D','DOB',replace(substr(nvl(fnd_date.date_to_canonical(l_dob),'0'),1,10),'/','-'));
1114 load_xml('D','E_MAIL',l_email);
1115 load_xml('D','ADD_LINE1',l_addr1);
1116 load_xml('D','ADD_LINE2',l_addr2);
1117 load_xml('D','ADD_LINE3',l_addr3);
1118 load_xml('D','CITY',l_city);
1119 load_xml('D','STATE',l_state);
1120 load_xml('D','COUNTRY',l_country);
1121 load_xml('D','ZIP',l_postal_code);
1122 load_xml('D','DAY_PHONE',l_day_phone);
1123 load_xml('D','EVENING_PHONE',l_evening_phone);
1124 load_xml('D','DIVLOC',l_loc_id);
1125 load_xml('D','PAYROLL_FREQ',l_pay_freq);
1126 load_xml('D','PERIOD_TYPE',l_period_type);
1127 load_xml('D','PAYROLL_DATE',to_char(replace(substr(nvl(fnd_date.date_to_canonical(a.pay_date),'0'),1,10),'/','-')));
1128 load_xml('D','SOURCE',l_source);
1129 load_xml('D','AMOUNT',round(l_amount,2));
1130 load_xml('D','FMT_AMOUNT',l_fmt_amount);
1131 load_xml('D','SUB_PLAN',l_sub_plan);
1132 load_xml('D','HIRE_DATE',to_char(replace(substr(nvl(fnd_date.date_to_canonical(l_hire_date),'0'),1,10),'/','-')));
1133 load_xml('D','TERMINATION_DATE',to_char(replace(substr(nvl(fnd_date.date_to_canonical(l_term_date),'0'),1,10),'/','-')));
1134 load_xml('D','ADJ_DATE_HIRE',to_char(replace(substr(nvl(fnd_date.date_to_canonical(l_adj_date),'0'),1,10),'/','-')));
1135 load_xml('CE','G_EMPLOYEE_DATA','');
1136
1137 END IF;
1138
1139 END LOOP; -- for ele entries
1140
1141 END LOOP; -- for asg actions
1142
1143 END IF;
1144
1145 END generate_record;
1146
1147 END PQP_UStiaa_pkg;