[Home] [Help]
PACKAGE BODY: APPS.PQP_TIAALOD_PKG
Source
1 Package Body PQP_Tiaalod_Pkg As
2 /* $Header: pqtiaald.pkb 120.0.12000000.1 2007/01/16 04:34:33 appldev noship $ */
3 ------------------------------------- Global Varaibles ---------------------------
4 l_start_date pay_payroll_actions.start_date%TYPE;
5 l_end_date pay_payroll_actions.effective_date%TYPE;
6 l_business_group_id pay_payroll_actions.business_group_id%TYPE;
7 l_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE;
8 l_effective_date pay_payroll_actions.effective_date%TYPE;
9 l_action_type pay_payroll_actions.action_type%TYPE;
10 l_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE;
11 l_assignment_id pay_assignment_actions.assignment_id%TYPE;
12 l_tax_unit_id hr_organization_units.organization_id%TYPE;
13 l_gre_name hr_organization_units.name%TYPE;
14 l_organization_id hr_organization_units.organization_id%TYPE;
15 l_org_name hr_organization_units.name%TYPE;
16 l_location_id hr_locations.location_id%TYPE;
17 l_location_code hr_locations.location_code%TYPE;
18 l_ppp_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE;
19 l_bal_value NUMBER(11,2);
20 l_leg_param VARCHAR2(240);
21 l_leg_start_date DATE;
22 l_leg_end_date DATE;
23 t_payroll_id NUMBER(15);
24 t_consolidation_set_id NUMBER(15);
25 t_gre_id NUMBER(15);
26 t_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE;
27 l_defined_balance_id NUMBER;
28 l_row_count NUMBER :=0;
29 l_national_id per_people_v.national_identifier%TYPE;
30 l_last_name per_all_people_f.last_name%TYPE;
31 l_first_name per_all_people_f.first_name%TYPE;
32 l_middle_name per_all_people_f.middle_names%TYPE;
33 l_dob per_all_people_f.date_of_birth%TYPE;
34 l_asg_ppg_code per_assignment_extra_info.aei_information1%TYPE;
35 l_pay_mode pay_payrolls_f.prl_information4%TYPE;
36 l_ppg_billing pay_payrolls_f.prl_information7%TYPE;
37 l_payroll_id per_assignments_f.payroll_id%TYPE;
38 l_org_ppg hr_organization_information.org_information1%TYPE;
39 l_err_msg VARCHAR2(800);
40 l_err_num VARCHAR2(800);
41 l_chunk_no number;
42 l_ld_payroll_id pay_payroll_actions.payroll_id%TYPE;
43 l_prev_payroll_id pay_payroll_actions.payroll_id%TYPE;
44
45 TYPE r_pay_mode IS RECORD (payroll_id per_assignments_f.payroll_id%TYPE,
46 payment_mode pay_payrolls_f.prl_information4%TYPE,
47 ppg_billing_code pay_payrolls_f.prl_information7%TYPE,
48 effective_date date);
49
50 TYPE t_pay_mode IS TABLE OF r_pay_mode INDEX BY BINARY_INTEGER;
51
52 pay_mode_t t_pay_mode;
53
54 TYPE r_org_ppg IS RECORD (org_ppg_code hr_organization_information.org_information1%TYPE,
55 tax_unit_id pay_assignment_actions.tax_unit_id%TYPE);
56
57 TYPE t_org_ppg IS TABLE OF r_org_ppg INDEX BY BINARY_INTEGER;
58 org_ppg_t t_org_ppg;
59
60 TYPE r_ins_val IS RECORD
61 (last_name per_all_people_f.last_name%TYPE,
62 first_name per_all_people_f.first_name%TYPE,
63 middle_name per_all_people_f.middle_names%TYPE,
64 dob per_all_people_f.date_of_birth%TYPE,
65 national_id per_all_people_f.national_identifier%TYPE,
66 asg_ppg_code per_assignment_extra_info.aei_information1%TYPE,
67 org_ppg hr_organization_information.org_information1%TYPE,
68 pay_mode pay_payrolls_f.prl_information4%TYPE,
69 gre_name hr_organization_units.name%TYPE,
70 org_name hr_organization_units.name%TYPE,
71 effective_date DATE,
72 ppg_billing pay_payrolls_f.prl_information7%TYPE,
73
74 balance_name1 pay_balance_types.balance_name%TYPE,
75 balance_value1 NUMBER,
76 balance_name2 pay_balance_types.balance_name%TYPE,
77 balance_value2 NUMBER,
78 balance_name3 pay_balance_types.balance_name%TYPE,
79 balance_value3 NUMBER,
80 balance_name4 pay_balance_types.balance_name%TYPE,
81 balance_value4 NUMBER,
82 balance_name5 pay_balance_types.balance_name%TYPE,
83 balance_value5 NUMBER,
84 balance_name6 pay_balance_types.balance_name%TYPE,
85 balance_value6 NUMBER,
86 input_date per_assignments_f.effective_end_date%TYPE,
87 input_start_date per_assignments_f.effective_end_date%TYPE,
88 assignment_id per_assignments_f.assignment_id%TYPE,
89 assignment_action_id pay_assignment_actions.assignment_action_id%TYPE,
90 err_num VARCHAR2(800),
91 err_msg VARCHAR2(800),
92 payroll_id NUMBER
93 );
94 TYPE t_ins_val IS TABLE OF r_ins_val
95 INDEX BY BINARY_INTEGER;
96 ins_val_t t_ins_val;
97
98 CURSOR c1 IS
99 SELECT db.defined_balance_id, pbt.balance_name
100 FROM pay_balance_types pbt,
101 pay_defined_balances db,
102 pay_balance_dimensions bd
103 WHERE pbt.balance_name IN ('RA GRA PLAN BY INST',
104 'RA GRA PLAN REDUCT',
105 'RA PLAN DEDUCT',
106 'RA ADDL DEDUCT',
107 'RA ADDL REDUCT',
108 'SRA GSRA REDUCT')
109 AND bd.dimension_name = 'Assignment Default Run'
110 AND pbt.balance_type_id = db.balance_type_id
111 AND bd.balance_dimension_id = db.balance_dimension_id
112 ORDER BY pbt.balance_name;
113
114 TYPE t_def_bal IS TABLE OF c1%ROWTYPE
115 INDEX BY BINARY_INTEGER;
116 g_balance_rec t_def_bal;
117
118 -- ------------------------------------------------------------------------
119 -- |-----------------------------< Chk_Neg_Amt>----------------------------|
120 -- ------------------------------------------------------------------------
121 -- This procedure was added as assignment_actions are spilt across
122 -- various chunks when the values CHUNK_SIZE and the THREADS in
123 -- pay_action_parameters are more than 1 and there may be more than one
124 -- record in the pay_us_rpt_totals for an assignment and we need to
125 -- consider the sum of all the records for a given pay_mode and assignment_id
126 -- so see if there any -ve balances being reported, which would be reported
127 -- in the exception report. This procedure would be called in the report
128 -- PAYUSTIM.rdf after-param report trigger.
129 --
130 PROCEDURE Chk_Neg_Amt( p_payroll_action_id IN number) IS
131
132 CURSOR csr_rpt IS
133 SELECT DISTINCT
134 attribute5
135 ,attribute12
136 ,value9
137 FROM pay_us_rpt_totals
138 WHERE tax_unit_id = p_payroll_action_id
139 AND attribute14 = '999'
140 AND attribute15 = 'NEGATIVE BALANCE'
141 AND attribute1 <> 'TIAA-CREF';
142
143 CURSOR csr_asg ( c_payroll_action_id IN NUMBER
144 ,c_assignment_id IN VARCHAR2
145 ,c_payroll_id IN NUMBER) IS
146 SELECT attribute5
147 ,SUM(value2) value2
148 ,SUM(value3) value3
149 ,SUM(value4) value4
150 ,SUM(value5) value5
151 ,SUM(value6) value6
152 ,SUM(value7) value7
153 FROM pay_us_rpt_totals
154 WHERE tax_unit_id = c_payroll_action_id
155 AND attribute1 <> 'TIAA-CREF'
156 AND attribute5 = c_assignment_id
157 AND value9 = c_payroll_id
158 -- AND NVL(attribute12,'X') = NVL(c_payment_mode,'X')
159 GROUP BY attribute5
160 HAVING SUM(value2) < 0 OR
161 SUM(value3) < 0 OR
162 SUM(value4) < 0 OR
163 SUM(value5) < 0 OR
164 SUM(value6) < 0 OR
165 SUM(value7) < 0;
166
167 l_proc_name VARCHAR2(150) := g_proc_name ||'Chk_Neg_Amt';
168 csr_asg_rec csr_asg%ROWTYPE;
169
170 BEGIN
171 hr_utility.set_location('Entering : '||l_proc_name, 10);
172 FOR rpt_rec IN csr_rpt
173 LOOP
174 OPEN csr_asg (c_payroll_action_id => p_payroll_action_id
175 ,c_assignment_id => rpt_rec.attribute5
176 ,c_payroll_id => rpt_rec.value9);
177 FETCH csr_asg INTO csr_asg_rec;
178 IF csr_asg%NOTFOUND THEN
179 UPDATE pay_us_rpt_totals
180 SET attribute14 = NULL
181 ,attribute15 = NULL
182 WHERE tax_unit_id = p_payroll_action_id
183 AND attribute5 = rpt_rec.attribute5
184 AND attribute15 = 'NEGATIVE BALANCE'
185 AND value9 = rpt_rec.value9;
186 END IF;
187 CLOSE csr_asg;
188 COMMIT;
189 END LOOP;
190 hr_utility.set_location('Leaving : '||l_proc_name, 90);
191 EXCEPTION
192 WHEN others THEN
193 hr_utility.set_location('..Error in Chk_Neg_Amt :' ||SQLERRM,150);
194 hr_utility.set_location('Leaving : '||l_proc_name, 150);
195 RAISE;
196 END Chk_Neg_Amt;
197
198 -- ---------------------------------------------------------------------
199 -- |-----------------------< insert_rpt_data >--------------------------|
200 -- ---------------------------------------------------------------------
201 -- Insert_Rpt_Data procedure inserts the records from the PL/SQL table
202 -- into pay_us_rpt_totals table only if there exists at least one balance
203 -- value which is <> 0, i.e. if all the six balances for the TIAA-CREF
204 -- are zero for the assignment then that record from the PL/SQL table is
205 -- ignored. The PL/SQL record for the assignment is deleted from the PL/SQL
206 -- table once the insert is done(or not).
207 -- ---------------------------------------------------------------------
208 PROCEDURE insert_rpt_data (p_assignment_id IN NUMBER
209 ,p_assignment_action_id IN NUMBER
210 ,p_dimension_name IN VARCHAR2
211 ,p_effective_date IN DATE
212 ,p_ppa_finder IN VARCHAR2) IS
213 l_insert_valid BOOLEAN := FALSE;
214 l_proc_name VARCHAR2(150) := g_proc_name ||'insert_rpt_data';
215 i per_assignments_f.assignment_id%TYPE;
216
217 BEGIN
218 hr_utility.set_location('Entering : '||l_proc_name, 10);
219 -- Check if for the assignment id if there are any non-zero balances
220 i := p_assignment_id;
221 IF ins_val_t.EXISTS(i) THEN
222 IF ins_val_t(i).assignment_id = p_assignment_id AND
223 (ins_val_t(i).balance_value1 <> 0 OR
224 ins_val_t(i).balance_value2 <> 0 OR
225 ins_val_t(i).balance_value3 <> 0 OR
226 ins_val_t(i).balance_value4 <> 0 OR
227 ins_val_t(i).balance_value5 <> 0 OR
228 ins_val_t(i).balance_value6 <> 0 ) THEN
229 l_insert_valid := TRUE;
230 IF (ins_val_t(i).balance_value1 < 0 OR
231 ins_val_t(i).balance_value2 < 0 OR
232 ins_val_t(i).balance_value3 < 0 OR
233 ins_val_t(i).balance_value4 < 0 OR
234 ins_val_t(i).balance_value5 < 0 OR
235 ins_val_t(i).balance_value6 < 0 ) THEN
236
237 ins_val_t(i).err_num := '999';
238 ins_val_t(i).err_msg := 'NEGATIVE BALANCE';
239 END IF;
240 END IF;
241 END IF;
242
243 hr_utility.set_location('..After the check if atleast one balance is <> 0', 15);
244 IF l_insert_valid THEN
245 hr_utility.set_location('..Valid for Assignment : '||p_assignment_id, 20);
246 INSERT INTO pay_us_rpt_totals
247 (tax_unit_id,
248 gre_name,
249 organization_name,
250 location_name,
251 attribute1,
252 value1,
253 attribute2,
254 attribute3,
255 attribute5,
256 attribute6,
257 attribute7,
258 attribute8,
259 attribute9,
260 attribute10,
261 attribute11,
262 attribute12,
263 attribute13,
264 attribute14,
265 attribute15,
266 attribute16,
267 attribute17,
268 attribute18,
269 attribute19,
270 attribute21,
271 attribute22,
272 attribute23,
273 attribute24,
274 attribute25,
275 attribute26,
276 value2,
277 value3,
278 value4,
279 value5,
280 value6,
281 value7,
282 organization_id, value8,value9 )
283 VALUES
284 (l_payroll_action_id, --tax_unit_id
285 ins_val_t(i).gre_name, --gre_name
286 ins_val_t(i).org_name, --org_name
287 l_location_code, --location_code
288 'BALANCE', --'BALANCE'
289 l_payroll_action_id, --value1
290 '', --attribute2
291 p_dimension_name, --attribute3
292 p_assignment_id, --attribute5
293 ins_val_t(i).last_name, --attribute6
294 ins_val_t(i).first_name, --attribute7
295 TO_CHAR(ins_val_t(i).dob,'DD-MON-YYYY'), --attribute8
296 ins_val_t(i).national_id, --attribute9
297 ins_val_t(i).asg_ppg_code, --attribute10
298 ins_val_t(i).org_ppg, --attribute11
299 ins_val_t(i).pay_mode, --attribute12
300 ins_val_t(i).middle_name, --attribute13
301 ins_val_t(i).err_num, --attribute14
302 ins_val_t(i).err_msg, --attribute15
303 TO_CHAR(ins_val_t(i).input_start_date,'DD-MON-YYYY'), --attribute16
304 TO_CHAR(ins_val_t(i).input_date,'DD-MON-YYYY'), --attribute17
305 TO_CHAR(l_effective_date,'DD-MON-YYYY'), --attribute18
306 ins_val_t(i).ppg_billing, --attribute19
307 ins_val_t(i).balance_name1, --attribute21
308 ins_val_t(i).balance_name2, --attribute22
309 ins_val_t(i).balance_name3, --attribute23
310 ins_val_t(i).balance_name4, --attribute24
311 ins_val_t(i).balance_name5, --attribute25
312 ins_val_t(i).balance_name6, --attribute26
313 ins_val_t(i).balance_value1, --value2
314 ins_val_t(i).balance_value2, --value3
315 ins_val_t(i).balance_value3, --value4
316 ins_val_t(i).balance_value4, --value5
317 ins_val_t(i).balance_value5, --value6
318 ins_val_t(i).balance_value6, --value7
319 ins_val_t(i).assignment_action_id, --organization_id
320 l_chunk_no, --value8
321 ins_val_t(i).payroll_id ); --value9
322 hr_utility.set_location('..Inserted for assignment :'||p_assignment_id, 25);
323 END IF; -- IF l_insert_valid Then
324 -- Delete all the records from the PL/SQL table for the assignment id
325 hr_utility.set_location('..After Inserting into pay_us_rpt_totals ', 70);
326
327 IF ins_val_t.EXISTS(i) THEN
328 ins_val_t.DELETE(i);
329 hr_utility.set_location('..Deleting for Assg ID : '||p_assignment_id,75);
330 END IF;
331 l_err_msg := NULL;
332 l_err_num := NULL;
333
334 hr_utility.set_location('..After Deleting rows from PL/SQL table', 80);
335 hr_utility.set_location('Leaving : '||l_proc_name, 90);
336
337 EXCEPTION
338 WHEN OTHERS THEN
339 hr_utility.set_location('..Error in Insert_Rpt_Data :' ||SQLERRM,150);
340 hr_utility.set_location('Leaving : '||l_proc_name, 150);
341 RAISE;
342
343 END insert_rpt_data;
344
345 -- ---------------------------------------------------------------------
346 -- |------------------------< load_balances >---------------------------|
347 -- ---------------------------------------------------------------------
348 PROCEDURE load_balances(p_assignment_id IN NUMBER,
349 p_assignment_action_id IN NUMBER,
350 p_dimension_name IN VARCHAR2,
351 p_effective_date IN DATE,
352 p_ppa_finder IN VARCHAR2) IS
353
354 l_comp_balance NUMBER :=0;
355 l_balance NUMBER;
356 l_balance_start NUMBER;
357 l_balance_end NUMBER;
358 l_defined_balance_id NUMBER;
359 l_def_balance_id pay_defined_balances.defined_balance_id%TYPE;
360 l_balance_name pay_balance_types.balance_name%TYPE := ' ';
361 l_tax_id NUMBER;
362 l_count_bal NUMBER :=0;
363 l_input_date per_assignments_f.effective_end_date%TYPE;
364 l_input_start_date per_assignments_f.effective_start_date%TYPE;
365 v_start_date per_assignments_f.effective_start_date%TYPE;
366 v_end_date per_assignments_f.effective_end_date%TYPE;
367 l_update_flag BOOLEAN;
368 l_insert_valid BOOLEAN;
369 l_proc_name VARCHAR2(150) := g_proc_name ||'load_balances';
370 i per_all_assignments_f.assignment_id%TYPE;
371
372 BEGIN
373 hr_utility.set_location('Entering : '||l_proc_name, 10);
374 IF l_org_ppg IS NULL AND
375 l_asg_ppg_code IS NULL AND
376 l_ppg_billing IS NULL THEN
377 l_err_num := '999';
378 l_err_msg := 'PPG CODE REQUIRED';
379 END IF;
380
381 -- set the date earned and tax unit id context for the balance pkg
382 hr_utility.set_location('..Set the tax and date earned contexts ', 15);
383 pay_balance_pkg.set_context('tax_unit_id',l_tax_unit_id);
384
385 hr_utility.set_location('..No. of def. balances : '||g_balance_rec.count, 20);
386
387 FOR i_bals IN 1..g_balance_rec.count
388 LOOP
389 l_defined_balance_id := g_balance_rec(i_bals).defined_balance_id;
390 l_balance_name := g_balance_rec(i_bals).balance_name;
391
392 -- Get the value for each of the def. balance id for the given assig. action
393 l_balance_end := pay_balance_pkg.get_value
394 (p_defined_balance_id => l_defined_balance_id,
395 p_assignment_action_id => p_assignment_action_id );
396 hr_utility.set_location('..Balance name : '||l_balance_name, 25);
397
398 l_balance := NVL(l_balance_end,0);
399 l_update_flag := FALSE;
400 i := p_assignment_id;
401
402 IF ins_val_t.EXISTS(i) THEN
403 IF l_balance_name = 'RA GRA PLAN BY INST' AND
404 l_balance_name = ins_val_t(i).balance_name1 THEN
405 ins_val_t(i).balance_value1 := ins_val_t(i).balance_value1 + l_balance;
406 l_update_flag := TRUE;
407 ELSIF l_balance_name = 'RA GRA PLAN REDUCT' AND
408 l_balance_name = ins_val_t(i).balance_name2 THEN
409 ins_val_t(i).balance_value2 := ins_val_t(i).balance_value2 + l_balance;
410 l_update_flag := TRUE;
411 ELSIF l_balance_name = 'RA PLAN DEDUCT' AND
412 l_balance_name = ins_val_t(i).balance_name3 THEN
413 ins_val_t(i).balance_value3 := ins_val_t(i).balance_value3 + l_balance;
414 l_update_flag := TRUE;
415 ELSIF l_balance_name = 'RA ADDL REDUCT' AND
416 l_balance_name = ins_val_t(i).balance_name4 THEN
417 ins_val_t(i).balance_value4 := ins_val_t(i).balance_value4 + l_balance;
418 l_update_flag := TRUE;
419 ELSIF l_balance_name = 'RA ADDL DEDUCT' AND
420 l_balance_name = ins_val_t(i).balance_name5 THEN
421 ins_val_t(i).balance_value5 := ins_val_t(i).balance_value5 + l_balance;
422 l_update_flag := TRUE;
423 ELSIF l_balance_name = 'SRA GSRA REDUCT' AND
424 l_balance_name = ins_val_t(i).balance_name6 THEN
425 ins_val_t(i).balance_value6 := ins_val_t(i).balance_value6 + l_balance;
426 l_update_flag := TRUE;
427 END IF;
428 IF NOT l_update_flag THEN
429 hr_utility.set_location('..New balance for the same assignment id :'||l_balance_name, 25);
430 IF l_balance_name = 'RA GRA PLAN BY INST' THEN
431 ins_val_t(i).balance_name1 := l_balance_name;
432 ins_val_t(i).balance_value1 := l_balance;
433 ELSIF l_balance_name = 'RA GRA PLAN REDUCT' THEN
434 ins_val_t(i).balance_name2 := l_balance_name;
435 ins_val_t(i).balance_value2 := l_balance;
436 ELSIF l_balance_name = 'RA PLAN DEDUCT' THEN
437 ins_val_t(i).balance_name3 := l_balance_name;
438 ins_val_t(i).balance_value3 := l_balance;
439 ELSIF l_balance_name = 'RA ADDL REDUCT' THEN
440 ins_val_t(i).balance_name4 := l_balance_name;
441 ins_val_t(i).balance_value4 := l_balance;
442 ELSIF l_balance_name = 'RA ADDL DEDUCT' THEN
443 ins_val_t(i).balance_name5 := l_balance_name;
444 ins_val_t(i).balance_value5 := l_balance;
445 ELSIF l_balance_name = 'SRA GSRA REDUCT' THEN
446 ins_val_t(i).balance_name6 := l_balance_name;
447 ins_val_t(i).balance_value6 := l_balance;
448 END IF;
449 l_update_flag := TRUE;
450 END IF;
451 IF l_update_flag THEN
452 ins_val_t(i).asg_ppg_code := l_asg_ppg_code;
453 ins_val_t(i).org_ppg := l_org_ppg;
454 ins_val_t(i).pay_mode := l_pay_mode;
455 ins_val_t(i).gre_name := l_gre_name;
456 ins_val_t(i).org_name := l_org_name;
457 ins_val_t(i).effective_date := l_effective_date;
458 ins_val_t(i).ppg_billing := l_ppg_billing;
459 ins_val_t(i).input_date := l_input_date;
460 ins_val_t(i).input_start_date := l_input_start_date;
461 ins_val_t(i).payroll_id := l_ld_payroll_id;
462 END IF;
463 END IF;
464 hr_utility.set_location('..After Checking the PL/SQL table ', 40);
465
466 IF NOT l_update_flag THEN
467 i := p_assignment_id;
468 hr_utility.set_location('..Next new index used : '||i, 40);
469 ins_val_t(i).assignment_id := p_assignment_id;
470 ins_val_t(i).assignment_action_id := p_assignment_action_id;
471
472 IF l_balance_name = 'RA GRA PLAN BY INST' THEN
473 ins_val_t(i).balance_name1 := l_balance_name;
474 ins_val_t(i).balance_value1 := l_balance;
475 ELSIF l_balance_name = 'RA GRA PLAN REDUCT' THEN
476 ins_val_t(i).balance_name2 := l_balance_name;
477 ins_val_t(i).balance_value2 := l_balance;
478 ELSIF l_balance_name = 'RA PLAN DEDUCT' THEN
479 ins_val_t(i).balance_name3 := l_balance_name;
480 ins_val_t(i).balance_value3 := l_balance;
481 ELSIF l_balance_name = 'RA ADDL REDUCT' THEN
482 ins_val_t(i).balance_name4 := l_balance_name;
483 ins_val_t(i).balance_value4 := l_balance;
484 ELSIF l_balance_name = 'RA ADDL DEDUCT' THEN
485 ins_val_t(i).balance_name5 := l_balance_name;
486 ins_val_t(i).balance_value5 := l_balance;
487 ELSIF l_balance_name = 'SRA GSRA REDUCT' THEN
488 ins_val_t(i).balance_name6 := l_balance_name;
489 ins_val_t(i).balance_value6 := l_balance;
490 END IF;
491
492 ins_val_t(i).last_name := l_last_name;
493 ins_val_t(i).first_name := l_first_name;
494 ins_val_t(i).middle_name := l_middle_name;
495 ins_val_t(i).dob := l_dob;
496 ins_val_t(i).national_id := l_national_id;
497 ins_val_t(i).asg_ppg_code := l_asg_ppg_code;
498 ins_val_t(i).org_ppg := l_org_ppg;
499 ins_val_t(i).pay_mode := l_pay_mode;
500 ins_val_t(i).gre_name := l_gre_name;
501 ins_val_t(i).org_name := l_org_name;
502 ins_val_t(i).effective_date := l_effective_date;
503 ins_val_t(i).ppg_billing := l_ppg_billing;
504 ins_val_t(i).input_date := l_input_date;
505 ins_val_t(i).input_start_date := l_input_start_date;
506 END IF;
507 IF (l_err_num IS NOT NULL OR
508 l_err_msg IS NOT NULL ) AND
509 ins_val_t.EXISTS(i) THEN
510 IF ins_val_t(i).err_num IS NULL THEN
511 ins_val_t(i).err_msg := l_err_msg;
512 ins_val_t(i).err_num := l_err_num;
513 END IF;
514 END IF;
515 END LOOP; --For i_bals in 1..g_balance_rec.count
516
517 hr_utility.set_location('..After looping thru g_balance_rec PL/SQL table ', 85);
518 hr_utility.set_location('Leaving : '||l_proc_name, 90);
519 EXCEPTION
520 WHEN OTHERS THEN
521 hr_utility.set_location('..Error:' ||SQLERRM,150);
522 hr_utility.set_location('Leaving : '||l_proc_name, 150);
523 RAISE;
524 END load_balances;
525
526 -- ---------------------------------------------------------------------
527 -- |-------------------------< ppg_billing >----------------------------|
528 -- ---------------------------------------------------------------------
529 PROCEDURE ppg_billing(p_payroll_id IN NUMBER) IS
530
531 CURSOR c_ppg_billing IS
532 SELECT prl.prl_information7
533 FROM pay_payrolls_f prl
534 WHERE prl.payroll_id = p_payroll_id
535 AND prl.prl_information_category = 'US'
536 AND l_effective_date BETWEEN prl.effective_start_date
537 AND prl.effective_end_date;
538 l_count NUMBER ;
539 lpayroll_id per_assignments_f.payroll_id%TYPE;
540 BEGIN
541 l_ppg_billing:='';
542 FOR i IN 1..pay_mode_t.count
543 LOOP
544 IF pay_mode_t(i).payroll_id = p_payroll_id AND
545 pay_mode_t(i).effective_date = TRUNC(l_effective_date) THEN
546 l_count := pay_mode_t.count;
547 l_ppg_billing := pay_mode_t(i).ppg_billing_code;
548 lpayroll_id := p_payroll_id;
549 END IF;
550 END LOOP;
551 IF l_ppg_billing IS NULL THEN
552 OPEN c_ppg_billing;
553 LOOP
554 FETCH c_ppg_billing INTO l_ppg_billing;
555 EXIT WHEN c_ppg_billing%NOTFOUND;
556 pay_mode_t(l_count).ppg_billing_code:= l_ppg_billing;
557 END LOOP;
558 CLOSE c_ppg_billing;
559 END IF;
560 EXCEPTION
561 WHEN OTHERS THEN
562 hr_utility.trace('Error occurred load_er_liab ...' ||SQLERRM);
563 END ppg_billing;
564
565 -- ---------------------------------------------------------------------
566 -- |---------------------------< pay_mode >-----------------------------|
567 -- ---------------------------------------------------------------------
568 PROCEDURE pay_mode(p_payroll_id IN NUMBER) IS
569
570 CURSOR c_pay_mode IS
571 SELECT prl.prl_information4
572 ,prl.prl_information7
573 FROM pay_payrolls_f prl
574 WHERE prl.payroll_id = p_payroll_id
575 AND prl.prl_information_category = 'US'
576 AND l_effective_date BETWEEN prl.effective_start_date
577 AND prl.effective_end_date;
578 l_count NUMBER;
579 lpayroll_id per_assignments_f.payroll_id%TYPE;
580 BEGIN
581 l_pay_mode:='';
582 lpayroll_id:='';
583 l_ppg_billing:='';
584
585 FOR i IN 1..pay_mode_t.count
586 LOOP
587 IF pay_mode_t(i).payroll_id = p_payroll_id AND
588 pay_mode_t(i).effective_date = TRUNC(l_effective_date) THEN
589 l_pay_mode := pay_mode_t(i).payment_mode;
590 l_ppg_billing := pay_mode_t(i).ppg_billing_code;
591 lpayroll_id := p_payroll_id;
592 END IF;
593 END LOOP;
594 IF lpayroll_id IS NULL THEN
595 OPEN c_pay_mode;
596 LOOP
597 FETCH c_pay_mode INTO l_pay_mode,l_ppg_billing;
598 EXIT WHEN c_pay_mode%NOTFOUND;
599 l_count:= pay_mode_t.count + 1;
600 pay_mode_t(l_count).payroll_id := p_payroll_id;
601 pay_mode_t(l_count).payment_mode := l_pay_mode;
602 pay_mode_t(l_count).ppg_billing_code:= l_ppg_billing;
603 pay_mode_t(l_count).effective_date := TRUNC(l_effective_date);
604 END LOOP;
605 CLOSE c_pay_mode;
606 END IF;
607
608 IF l_pay_mode IS NULL THEN
609 l_err_num:='999';
610 l_err_msg:='MODE IS NULL';
611 END IF;
612
613 EXCEPTION
614 WHEN OTHERS THEN
615 hr_utility.trace('Error occurred load_er_liab ...' ||SQLERRM);
616 END pay_mode;
617
618 -- ---------------------------------------------------------------------
619 -- |------------------------< load_asg_ppg >----------------------------|
620 -- ---------------------------------------------------------------------
621 PROCEDURE load_asg_ppg(p_assignment IN NUMBER) IS
622 CURSOR asg_ppg IS
623 SELECT paei.aei_information1,
624 pasg.payroll_id
625 FROM per_assignment_extra_info paei ,
626 per_assignments_f pasg
627 WHERE pasg.assignment_id = p_assignment
628 AND pasg.assignment_id = paei.assignment_id(+)
629 AND paei.information_type(+) = 'PQP_US_TIAA_CREF_CODES'
630 AND l_effective_date BETWEEN pasg.effective_start_date
631 AND pasg.effective_end_date;
632
633 BEGIN
634 l_asg_ppg_code:='';
635 OPEN asg_ppg;
636 LOOP
637 FETCH asg_ppg INTO l_asg_ppg_code,l_payroll_id;
638 EXIT WHEN asg_ppg%NOTFOUND;
639 pay_mode(l_payroll_id );
640 END LOOP;
641 CLOSE asg_ppg;
642 EXCEPTION
643 WHEN OTHERS THEN
644 hr_utility.trace('Error occurred load_asg_ppg ...' ||SQLERRM);
645 END load_asg_ppg;
646
647 -- ---------------------------------------------------------------------
648 -- |------------------------< load_details >----------------------------|
649 -- ---------------------------------------------------------------------
650 PROCEDURE load_details (p_assignment IN NUMBER) IS
651 msg1 VARCHAR2(2000);
652 l_term_date date;
653 l_actual_date date;
654 CURSOR per_det IS
655 SELECT ppv.last_name,
656 ppv.first_name,
657 ppv.middle_names,
658 ppv.date_of_birth,
659 ppv.national_identifier
660 FROM per_all_people_f ppv,
661 per_assignments_f paf
662 WHERE paf.assignment_id = p_assignment
663 AND paf.person_id = ppv.person_id
664 AND l_actual_date BETWEEN ppv.effective_start_date
665 AND ppv.effective_end_date
666 AND l_actual_date BETWEEN paf.effective_start_date
667 AND paf.effective_end_date;
668 CURSOR asg_end_date IS
669 SELECT MAX(effective_end_date)
670 FROM per_assignments_f paf
671 WHERE paf.assignment_id = p_assignment
672 AND paf.business_group_id =l_business_group_id;
673
674 BEGIN
675 l_last_name :='';
676 l_first_name :='';
677 l_middle_name:='';
678 l_dob :='';
679 l_national_id:='';
680
681
682 l_actual_date := l_leg_end_date;
683
684
685 OPEN per_det;
686 FETCH per_det INTO l_last_name,l_first_name,l_middle_name,l_dob,l_national_id;
687 CLOSE per_det;
688
689
690 IF l_national_id IS NULL THEN
691
692 OPEN asg_end_date;
693 FETCH asg_end_date INTO l_term_date;
694 CLOSE asg_end_date;
695
696 IF l_term_date < l_leg_end_date AND l_term_date IS NOT NULL THEN
697 l_actual_date := l_term_date;
698
699 ELSE
700 l_actual_date := l_leg_end_date;
701 END IF;
702 OPEN per_det;
703 FETCH per_det INTO l_last_name,l_first_name,l_middle_name,l_dob,l_national_id;
704 CLOSE per_det;
705 END IF;
706 IF l_national_id IS NULL THEN
707 l_err_num := '999';
708 l_err_msg := 'SSN MISSING';
709 END IF;
710
711 EXCEPTION
712 WHEN OTHERS THEN
713 msg1:=SQLERRM;
714 hr_utility.trace('Error occurred load_details ...' ||SQLERRM);
715 END load_details;
716 -- ---------------------------------------------------------------------
717 -- |------------------------< load_org_ppg >----------------------------|
718 -- ---------------------------------------------------------------------
719 PROCEDURE load_org_ppg(p_tax_unit_id IN NUMBER) IS
720
721 CURSOR c_org_ppg IS
722 SELECT org_information1
723 FROM hr_organization_information
724 WHERE org_information_context = 'PQP_US_TIAA_CREF_CODES'
725 AND organization_id = p_tax_unit_id;
726 ltaxunit_id hr_organization_units.organization_id%TYPE := NULL;
727 BEGIN
728 l_org_ppg:='';
729 FOR i IN 1..org_ppg_t.count
730 LOOP
731 IF org_ppg_t(i).tax_unit_id = p_tax_unit_id THEN
732 l_org_ppg := org_ppg_t(i).org_ppg_code;
733 ltaxunit_id := p_tax_unit_id;
734 END IF;
735 END LOOP;
736 IF ltaxunit_id IS NULL THEN
737 OPEN c_org_ppg;
738 LOOP
739 FETCH c_org_ppg INTO l_org_ppg;
740 EXIT WHEN c_org_ppg%NOTFOUND;
741 org_ppg_t(1).tax_unit_id := p_tax_unit_id;
742 org_ppg_t(1).org_ppg_code := l_org_ppg;
743 END LOOP;
744 CLOSE c_org_ppg;
745 END IF;
746 EXCEPTION
747 WHEN OTHERS THEN
748 hr_utility.trace('Error occurred load_org_ppg ...' ||SQLERRM);
749 END load_org_ppg;
750
751 -- ---------------------------------------------------------------------
752 -- |-------------------------< load_data >------------------------------|
753 -- ---------------------------------------------------------------------
754 PROCEDURE load_data
755 (pactid IN VARCHAR2,
756 chnkno IN NUMBER,
757 ppa_finder IN VARCHAR2,
758 p_dimension_name IN VARCHAR2) IS
759
760 CURSOR sel_aaid (l_pactid NUMBER,
761 l_chnkno NUMBER) IS
762 SELECT DISTINCT
763 paa.assignment_id assignment_id,
764 ppa_gen.start_date start_date,
765 ppa_gen.effective_date end_date,
766 ppa_gen.business_group_id business_group_id,
767 ppa_gen.payroll_action_id payroll_action_id,
768 ppa.effective_date effective_date,
769 ppa.action_type action_type,
770 paa.tax_unit_id tax_unit_id,
771 hou.name gre_name,
772 paf.organization_id organization_id,
773 hox.name organization_name,
774 paf.location_id location_id,
775 hrl.location_code location_code,
776 paa.assignment_action_id assignment_action_id,
777 ppa.payroll_id pay_payroll_id
778 FROM hr_locations_all hrl,
779 hr_organization_units hox,
780 hr_organization_units hou,
781 per_assignments_f paf,
782 pay_payroll_actions ppa,
783 pay_assignment_actions paa,
784 pay_action_interlocks pai,
785 pay_assignment_actions paa_gen,
786 pay_payroll_actions ppa_gen
787 WHERE
788 ppa_gen.payroll_action_id = l_pactid
789 AND paa_gen.payroll_action_id = ppa_gen.payroll_action_id
790 AND paa_gen.chunk_number = l_chnkno
791 AND pai.locking_action_id = paa_gen.assignment_action_id
792 AND paa.assignment_action_id = pai.locked_action_id
793 AND paa.action_status = 'C'
794 AND paa.tax_unit_id = NVL(t_gre_id,
795 paa.tax_unit_id)
796 AND ppa.consolidation_set_id = NVL(t_consolidation_set_id,
797 ppa.consolidation_set_id)
798 AND ppa.payroll_id = NVL(t_payroll_id,
799 ppa.payroll_id)
800 AND ppa.payroll_action_id = paa.payroll_action_id
801 AND ppa.action_type IN ('R','V','Q','B')
802 AND ppa.action_status = 'C'
803 AND ppa.effective_date BETWEEN ppa_gen.start_date
804 AND ppa_gen.effective_date
805 AND ppa.effective_date BETWEEN paf.effective_start_date
806 AND paf.effective_end_date
807 AND paf.assignment_id = paa.assignment_id
808 AND paf.business_group_id = ppa_gen.business_group_id
809 AND hrl.location_id = paf.location_id
810 AND hox.organization_id = paf.organization_id
811 AND hou.organization_id = paa.tax_unit_id
812 ORDER BY paa.assignment_id,ppa.payroll_id, paa.assignment_action_id;
813
814 l_prev_assignment_id per_all_assignments_f.assignment_id%TYPE := NULL;
815 l_prev_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE;
816 l_prev_end_date date;
817 l_count NUMBER(5);
818 l_proc_name VARCHAR2(150) := g_proc_name ||'load_data';
819
820 BEGIN
821 hr_utility.set_location('Entering : '||l_proc_name, 10);
822 l_chunk_no := chnkno;
823 BEGIN
824 SELECT ppa.legislative_parameters,
825 ppa.business_group_id,
826 ppa.start_date,
827 ppa.effective_date,
828 pay_paygtn_pkg.get_parameter('TRANSFER_CONC_SET',ppa.legislative_parameters),
829 pay_paygtn_pkg.get_parameter('TRANSFER_PAYROLL',ppa.legislative_parameters),
830 pay_paygtn_pkg.get_parameter('TRANSFER_GRE',ppa.legislative_parameters),
831 ppa.payroll_action_id
832 INTO l_leg_param,
833 l_business_group_id,
834 l_leg_start_date,
835 l_leg_end_date,
836 t_consolidation_set_id,
837 t_payroll_id,
838 t_gre_id,
839 t_payroll_action_id
840 FROM pay_payroll_actions ppa
841 WHERE ppa.payroll_action_id = pactid;
842
843 EXCEPTION
844 WHEN NO_DATA_FOUND THEN
845 hr_utility.set_location('..Legislative Details not found...',30);
846 RAISE;
847 END;
848
849 IF chnkno = 1 THEN
850 INSERT INTO pay_us_rpt_totals
851 ( tax_unit_id, attribute1, organization_id,
852 attribute2, attribute3, attribute4,
853 attribute5
854 )
855 VALUES
856 (pactid, 'TIAA-CREF', ppa_finder,
857 l_leg_param, l_business_group_id, TO_CHAR(l_leg_start_date,'MM/DD/YYYY'),
858 TO_CHAR(l_leg_end_date,'MM/DD/YYYY')
859 );
860 COMMIT;
861 END IF;
862 --
863 -- Store all the six balances in a PL/SQL as they would be the same for
864 -- all assignments.
865 --
866 hr_utility.set_location('..Store the balances in the PL/SQL table',35);
867 l_count := 1;
868 FOR bal_rec IN c1
869 LOOP
870 g_balance_rec(l_count).defined_balance_id := bal_rec.defined_balance_id;
871 g_balance_rec(l_count).balance_name := bal_rec.balance_name;
872 l_count := l_count + 1;
873 END LOOP;
874 hr_utility.set_location('..Open and loop thru the SEL_AAID Cursor',40);
875 OPEN sel_aaid (TO_NUMBER(pactid),chnkno);
876 LOOP
877 FETCH sel_aaid INTO l_assignment_id,
878 l_start_date,
879 l_end_date,
880 l_business_group_id,
881 l_payroll_action_id,
882 l_effective_date,
883 l_action_type,
884 l_tax_unit_id,
885 l_gre_name,
886 l_organization_id,
887 l_org_name,
888 l_location_id,
889 l_location_code,
890 l_assignment_action_id,
891 l_ld_payroll_id;
892 EXIT WHEN sel_aaid%NOTFOUND;
893
894 hr_utility.set_location('..Chunk No = '||TO_CHAR(chnkno),50);
895 hr_utility.set_location('..PPA_FINDER = '||ppa_finder,25);
896 hr_utility.set_location('..Start Date = '||TO_CHAR(l_start_date),50);
897 hr_utility.set_location('..End Date = '||TO_CHAR(l_end_date),50);
898 hr_utility.set_location('..BG ID = '||TO_CHAR(l_business_group_id),50);
899 hr_utility.set_location('..Payroll Action ID = '||TO_CHAR(l_payroll_action_id),50);
900 hr_utility.set_location('..Effective Date = '||TO_CHAR(l_effective_date),50);
901 hr_utility.set_location('..Action Type = '||l_action_type,50);
902 hr_utility.set_location('..Asg Act ID = '||TO_CHAR(l_assignment_action_id),50);
903 hr_utility.set_location('..Asg ID = '||TO_CHAR(l_assignment_id),50);
904 hr_utility.set_location('..Tax Unit ID = '||TO_CHAR(l_tax_unit_id),50);
905 hr_utility.set_location('..GRE Name = '||l_gre_name,50);
906 hr_utility.set_location('..ORG ID = '||TO_CHAR(l_organization_id),50);
907 hr_utility.set_location('..ORG Name = '||l_org_name,50);
908 hr_utility.set_location('..Loc ID = '||TO_CHAR(l_location_id),50);
909 hr_utility.set_location('..Loc Code = '||l_location_code,50);
910
911 -- If its diff. assign. Id then insert into pay_us_rpt_totals for that
912 -- assignment id.
913 IF l_prev_assignment_id IS NOT NULL AND
914 (l_assignment_id <> l_prev_assignment_id OR
915 l_ld_payroll_id <> l_prev_payroll_id ) THEN
916 hr_utility.set_location('..Calling INSERT_RPT_DATA within loop ', 55);
917 insert_rpt_data (p_assignment_id => l_prev_assignment_id
918 ,p_assignment_action_id => l_prev_assignment_action_id
919 ,p_dimension_name => p_dimension_name
920 ,p_effective_date => l_prev_end_date
921 ,p_ppa_finder => ppa_finder);
922 END IF;
923 l_prev_assignment_id := l_assignment_id;
924 l_prev_assignment_action_id := l_assignment_action_id;
925 l_prev_end_date := l_end_date;
926 l_prev_payroll_id := l_ld_payroll_id;
927
928 load_asg_ppg(l_assignment_id);
929 load_details(l_assignment_id);
930 load_org_ppg(l_tax_unit_id);
931
932 load_balances(p_assignment_id => l_assignment_id
933 ,p_assignment_action_id => l_assignment_action_id
934 ,p_dimension_name => p_dimension_name
935 ,p_effective_date => l_end_date
936 ,p_ppa_finder => ppa_finder
937 );
938
939 -- Issue a commit after processing 200 records
940 l_row_count := l_row_count +1 ;
941 IF l_row_count = 200 THEN
942 l_row_count := 0;
943 COMMIT;
944 END IF;
945 END LOOP;
946 hr_utility.set_location('..Calling INSERT_RPT_DATA outside loop ', 60);
947 insert_rpt_data (p_assignment_id => l_prev_assignment_id
948 ,p_assignment_action_id => l_prev_assignment_action_id
949 ,p_dimension_name => p_dimension_name
950 ,p_effective_date => l_prev_end_date
951 ,p_ppa_finder => ppa_finder);
952
953 CLOSE sel_aaid;
954 ins_val_t.DELETE;
955 COMMIT;
956 hr_utility.set_location('Leaving : '||l_proc_name, 90);
957 EXCEPTION
958 WHEN others THEN
959 hr_utility.set_location('..Error in LOAD_DATA :' ||SQLERRM,150);
960 hr_utility.set_location('Leaving : '||l_proc_name, 150);
961 RAISE;
962 END load_data;
963
964 END pqp_tiaalod_pkg;