[Home] [Help]
PACKAGE BODY: APPS.PQP_EXPREPLOD_PKG
Source
1 PACKAGE BODY pqp_expreplod_pkg AS
2 /* $Header: pqexrpld.pkb 120.4 2006/05/18 23:41:44 sshetty noship $ */
3 /*
4 Copyright (c) Oracle Corporation 1991,1992,1993. All rights reserved
5 --
6 --
7
8 --
9 */
10 ------------------------------------- Global Varaibles ---------------------------
11 l_start_date pay_payroll_actions.start_date%TYPE ;
12 l_end_date pay_payroll_actions.effective_date%TYPE ;
13 l_business_group_id pay_payroll_actions.business_group_id%TYPE ;
14 l_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE ;
15 l_effective_date pay_payroll_actions.effective_date%TYPE ;
16 l_action_type pay_payroll_actions.action_type%TYPE ;
17 l_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE ;
18 l_assignment_id pay_assignment_actions.assignment_id%TYPE ;
19 l_tax_unit_id hr_organization_units.organization_id%TYPE ;
20 l_gre_name hr_organization_units.name%TYPE ;
21 l_organization_id hr_organization_units.organization_id%TYPE ;
22 l_org_name hr_organization_units.name%TYPE ;
23 l_location_id hr_locations.location_id%TYPE ;
24 l_location_code hr_locations.location_code%TYPE ;
25 l_ppp_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE ;
26 l_leg_param pay_payroll_actions.legislative_parameters%TYPE ;
27 l_leg_start_date DATE ;
28 l_leg_end_date DATE ;
29 t_payroll_id NUMBER(15) ;
30 t_consolidation_set_id NUMBER(15) ;
31 g_gre_id NUMBER(15) ;
32 g_jd_code VARCHAR2(16) ;
33 t_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE ;
34 l_row_count NUMBER :=0 ;
35 l_national_id per_people_v.national_identifier%TYPE ;
36 l_last_name per_all_people_f.last_name%TYPE ;
37 l_first_name per_all_people_f.first_name%TYPE ;
38 l_middle_name per_all_people_f.middle_names%TYPE ;
39 l_full_name per_all_people_f.full_name%TYPE ;
40 l_assignment_number per_assignments_f.assignment_number%TYPE ;
41 l_dob per_all_people_f.date_of_birth%TYPE ;
42 --l_payroll_id per_assignments_f.payroll_id%TYPE ;
43 l_legislation_code per_business_groups.legislation_code%TYPE ;
44 l_business_group_id_ct pay_payroll_actions.business_group_id%TYPE :=NULL ;
45 l_param_count NUMBER(2):=0 ;
46 l_ppa_finder VARCHAR2(20) ;
47 l_date VARCHAR2(15) ;
48 l_report_id NUMBER ;
49 l_group_id VARCHAR2(60) ;
50 l_vartype VARCHAR2(1) ;
51 l_varvalue pqp_exception_reports.variance_value%TYPE ;
52 g_proc_name Varchar2(200) :='PQP_EXPREPLOD_PKG.';
53
54 TYPE r_date_detail IS RECORD (
55 exception_report_id pqp_exception_reports.exception_report_id%TYPE,
56 defined_balance_id pay_defined_balances.defined_balance_id%TYPE,
57 payroll_id per_assignments_f.payroll_id%TYPE,
58 pay_date DATE
59 );
60 TYPE t_date_detail is Table OF r_date_detail
61 INDEX BY binary_integer ;
62 l_date_detail t_date_detail ;
63
64 TYPE r_rep_detail IS RECORD (
65 exception_report_id pqp_exception_reports.exception_report_id%TYPE,
66 balance_type_id pqp_exception_reports.balance_type_id%TYPE,
67 dimension_type_id pqp_exception_reports.balance_dimension_id%TYPE,
68 variance_type pqp_exception_reports.variance_type%TYPE,
69 variance_value pqp_exception_reports.variance_value%TYPE,
70 variance_operator pqp_exception_reports.variance_operator%TYPE,
71 comparison_type pqp_exception_reports.comparison_type%TYPE,
72 comparison_value pqp_exception_reports.comparison_value%TYPE,
73 defined_balance_id pay_defined_balances.defined_balance_id%TYPE
74 );
75
76 TYPE t_rep_detail IS TABLE OF r_rep_detail
77 INDEX BY binary_integer ;
78 l_rep_detail t_rep_detail;
79
80
81 TYPE r_ret_value IS RECORD (
82 exception_report_id pqp_exception_reports.exception_report_id%TYPE,
83 balance_type_id pqp_exception_reports.balance_type_id%TYPE,
84 curent_balance NUMBER,
85 previous_balance NUMBER,
86 ret_val VARCHAR2(1)
87 );
88 TYPE t_ret_value IS TABLE OF r_ret_value
89 INDEX BY binary_integer ;
90 ----------------------------------------------------------------------------------
91
92 PROCEDURE load_balances(p_assignment IN NUMBER ,
93 p_effective_date IN DATE ,
94 p_balance_type_id IN NUMBER ,
95 p_cur_balance IN NUMBER ,
96 p_prev_balance IN NUMBER ,
97 p_report_id IN NUMBER ,
98 p_group_name IN VARCHAR2 ,
99 p_payroll_id IN NUMBER ,
100 p_ppa_finder IN VARCHAR2 ,
101 p_business_group_id IN NUMBER
102 )
103
104
105 IS
106
107
108 BEGIN
109
110 /*Inserts final calculated values into temp table*/
111
112 hr_utility.trace('Entering load_data ...' ||SQLERRM);
113
114
115
116 INSERT INTO pay_us_rpt_totals
117 (business_group_id ,
118 tax_unit_id ,
119 organization_id ,
120 value1 ,
121 value2 ,
122 attribute1 ,
123 attribute2 ,
124 attribute3 ,
125 attribute4 ,
126 attribute5 ,
127 attribute6 ,
128 attribute7 ,
129 attribute8 ,
130 attribute9 ,
131 attribute10 ,
132 attribute11 ,
133 attribute12 ,
134 attribute13 ,
135 attribute14
136 )
137 VALUES
138 (p_business_group_id ,
139 l_payroll_action_id ,
140 p_ppa_finder ,
141 p_cur_balance ,
142 p_prev_balance ,
143 p_balance_type_id ,
144 p_report_id ,
145 p_group_name ,
146 t_consolidation_set_id ,
147 p_payroll_id ,
148 p_assignment ,
149 l_last_name ,
150 l_first_name ,
151 l_national_id ,
152 l_middle_name ,
153 p_effective_date ,
154 p_ppa_finder ,
155 l_assignment_number ,
156 l_full_name
157 );
158
159
160
161 EXCEPTION
162 ---------
163 WHEN OTHERS THEN
164
165 hr_utility.trace('Error occurred load balances...' ||SQLERRM);
166
167 END load_balances;
168
169 FUNCTION get_legislation_code (p_business_group_id IN NUMBER)
170
171 RETURN VARCHAR2
172
173 IS
174
175 l_legislation_code_l per_business_groups.legislation_code%TYPE;
176 BEGIN
177 hr_utility.trace('Enter Legislation code');
178 SELECT legislation_code
179 INTO l_legislation_code_l
180 FROM per_business_groups
181 WHERE business_group_id =p_business_group_id;
182
183 RETURN (l_legislation_code_l);
184 hr_utility.trace('Leaving Legislation code' );
185
186 EXCEPTION
187 ---------
188 WHEN OTHERS THEN
189 RETURN(NULL);
190
191 END;
192
193 /*Gets balance for different legislations,balance
194 calls for other legislation must be included here*/
195 FUNCTION get_value (p_assignment_id IN NUMBER,
196 p_defined_balance_id IN NUMBER,
197 p_paydate IN DATE,
198 p_legislation_code IN VARCHAR2,
199 p_comp_type IN VARCHAR2 default null ,
200 p_errmsg OUT NOCOPY VARCHAR2)
201
202 RETURN NUMBER
203
204 IS
205 l_ret_value NUMBER;
206 l_orgname hr_organization_units.name%TYPE;
207 CURSOR c_get_bal_dim IS
208 SELECT INSTR(DATABASE_ITEM_SUFFIX,'_GRE_'),
209 INSTR(DATABASE_ITEM_SUFFIX,'_LE_')
210 FROM pay_balance_dimensions where balance_dimension_id =
211 (SELECT balance_dimension_id
212 FROM pay_defined_balances
213 WHERE defined_balance_id =p_defined_balance_id
214 );
215 CURSOR c_grename IS
216 SELECT hou.name
217 FROM hr_organization_units hou
218 WHERE organization_id=(SELECT segment1
219 FROM hr_soft_coding_keyflex
220 WHERE soft_coding_keyflex_id =
221 (SELECT soft_coding_keyflex_id
222 FROM per_all_assignments_f
223 WHERE assignment_id=p_assignment_id
224 AND p_paydate BETWEEN effective_start_date
225 AND effective_end_date));
226
227 l_instr_count NUMBER :=0;
228 l_instr_count1 NUMBER :=0;
229
230 BEGIN
231 --Lookup:PQP_COMPARISON_TYPE
232 --IC,MC,PADP,PADT,PANP,PANT,PC,PP,QC,YC
233 IF p_legislation_code='GB' AND ( p_comp_type='IC' OR p_comp_type='MC'
234 OR p_comp_type='PADP' OR p_comp_type='PADT'
235 OR p_comp_type= 'PANP' OR p_comp_type= 'PANT'
236 OR p_comp_type= 'PC' OR p_comp_type= 'PP'
237 OR p_comp_type= 'QC' OR p_comp_type= 'YC') THEN
238 hr_utility.trace('Enter GB Legislation');
239 --l_ret_value:=hr_gbbal.calc_all_balances (p_paydate, p_assignment_id,p_defined_balance_id);
240 l_ret_value:=pay_balance_pkg.get_value(p_defined_balance_id,p_assignment_id,p_paydate );
241 --Commented out as we are using a wrapper after the bug was fixed for PTD dim.
242 -- l_ret_value:=hr_dirbal.get_balance(p_assignment_id,p_defined_balance_id,p_paydate );
243 hr_utility.trace('Leaving GB Legislation');
244 p_errmsg:='NOERROR' ;
245 --MAN,MP,QAN,QP,YP
246 ELSIF p_legislation_code='GB' AND ( p_comp_type='MAN' OR p_comp_type= 'MP'
247 OR p_comp_type= 'QAN' OR p_comp_type= 'QP'
248 OR p_comp_type= 'YP') THEN
249 l_ret_value:=hr_gbbal.calc_all_balances (p_paydate, p_assignment_id,p_defined_balance_id);
250
251 ELSIF p_legislation_code='US' OR p_legislation_code='CA' OR p_legislation_code='AU' THEN
252
253 OPEN c_get_bal_dim;
254 FETCH c_get_bal_dim INTO l_instr_count,l_instr_count1;
255 CLOSE c_get_bal_dim;
256
257 /* OPEN c_grename;
258 LOOP
259 FETCH c_grename INTO l_orgname;
260 EXIT WHEN c_grename%NOTFOUND;
261 END LOOP;
262 CLOSE c_grename;*/
263 --If the balance dimension has GRE in it then set the context to gre.
264 IF l_instr_count > 0 OR l_instr_count1 > 0 THEN
265 pay_balance_pkg.set_context ('TAX_UNIT_ID',NVL(g_gre_id,l_organization_id));
266 END IF;
267 IF g_jd_code IS NOT NULL THEN
268 pay_balance_pkg.set_context('JURISDICTION_CODE',g_jd_code);
269 END IF;
270 hr_utility.trace('Entering US OR CA Legislation');
271 l_ret_value:=pay_balance_pkg.get_value(p_defined_balance_id,p_assignment_id,p_paydate );
272 hr_utility.trace('Leaving US OR CA Legislation');
273 p_errmsg:='NOERROR' ;
274 ELSE
275 -- Call the core get_balance pkg
276 hr_utility.trace('Entering General Legislation');
277 l_ret_value:=pay_balance_pkg.get_value(p_defined_balance_id,p_assignment_id,p_paydate );
278 hr_utility.trace('Leaving General Legislation');
279 p_errmsg:='NOERROR' ;
280 END IF;
281
282 RETURN(l_ret_value);
283
284 EXCEPTION
285 ---------
286 WHEN OTHERS THEN
287 p_errmsg:='ERROR' ;
288 RETURN(0);
289 END;
290
291
292 PROCEDURE get_balances( pactid IN NUMBER,
293 p_assignment_id IN NUMBER,
294 p_business_group_id IN NUMBER,
295 p_payroll_id IN NUMBER ,
296 p_report_id IN NUMBER,
297 p_group_id IN VARCHAR2,
298 p_vartype IN VARCHAR2,
299 p_varvalue IN NUMBER,
300 p_effective_date IN DATE,
301 p_ret_value OUT NOCOPY t_ret_value)
302 IS
303 CURSOR c_maxdate
304 IS
305 SELECT MAX(greatest(ptp.end_date,ptp.regular_payment_date)) pay_date,
306 MAX (ptp.end_date)
307 FROM per_time_periods ptp
308 WHERE ptp.payroll_id=p_payroll_id
309 AND ptp.end_date <= p_effective_date;
310
311 CURSOR c_prev_per(maxdate DATE)
312 IS
313 SELECT MAX(greatest(ptp.end_date,ptp.regular_payment_date)) prev_pay_period
314 FROM per_time_periods ptp
315 WHERE ptp.payroll_id =p_payroll_id
316 AND ptp.end_date < maxdate;
317
318 CURSOR c_avg_per_days(maxdate DATE,no_days NUMBER)
319 IS
320 SELECT greatest(ptp.end_date,ptp.regular_payment_date) pay_date
321 FROM per_time_periods ptp
322 WHERE ptp.payroll_id =p_payroll_id
323 AND ptp.end_date >= maxdate-no_days
324 AND ptp.end_date < maxdate
325 ORDER BY end_date desc;
326
327 CURSOR c_avg_per (maxdate DATE,no_period NUMBER)
328 IS
329 SELECT greatest(ptp.end_date,ptp.regular_payment_date) pay_date
330 FROM per_time_periods ptp
331 WHERE ptp.payroll_id = p_payroll_id
332 AND no_period >=(Select count(*)
333 FROM per_time_periods ptp1
334 WHERE ptp1.payroll_id =p_payroll_id
335 AND ptp1.end_date < maxdate
336 AND ptp.end_date <=ptp1.end_date)
337 AND ptp.end_date < maxdate
338 ORDER BY end_date desc;
339
340 CURSOR c_rep_name (p_legislation_code VARCHAR2)
341 IS
342 SELECT exception_report_id,
343 balance_type_id,
344 balance_dimension_id ,
345 NVL(p_vartype,variance_type),
346 NVL(p_varvalue,variance_value),
347 variance_operator,
348 comparison_type,
349 comparison_value
350 FROM pqp_exception_reports
351 WHERE exception_report_id=p_report_id
352 AND (business_group_id =p_business_group_id
353 OR business_group_id IS NULL)
354 AND (legislation_code=p_legislation_code
355 OR legislation_code IS NULL);
356
357 CURSOR c_group_name(p_legislation_code VARCHAR2)
358 IS
359 SELECT per.exception_report_id,
360 per.balance_type_id,
361 per.balance_dimension_id ,
362 per.variance_type,
363 per.variance_value,
364 per.variance_operator,
365 per.comparison_type,
366 per.comparison_value
367 FROM pqp_exception_report_groups perg,
368 pqp_exception_reports per
369 WHERE exception_group_name=(SELECT exception_group_name from
370 pqp_exception_report_groups
371 where exception_group_id=to_number(p_group_id))
372 AND ( perg.business_group_id =p_business_group_id
373 OR perg.business_group_id IS NULL)
374 AND ( per.business_group_id =p_business_group_id
375 OR per.business_group_id IS NULL)
376 AND per.exception_report_id=perg.exception_report_id
377 AND (perg.legislation_code=p_legislation_code
378 OR perg.legislation_code IS NULL)
379 AND (per.legislation_code=p_legislation_code
380 OR per.legislation_code IS NULL);
381
382 CURSOR c_def_bal (bal_type_id NUMBER,
383 dim_type_id NUMBER)
384 IS
385 SELECT defined_balance_id
386 FROM pay_defined_balances
387 WHERE balance_type_id=bal_type_id
388 AND balance_dimension_id=dim_type_id;
389 l_count NUMBER ;
390 l_count1 NUMBER ;
391 l_maxdate DATE ;
392 l_maxdate1 DATE ;
393 l_prev_pay_period DATE ;
394 l_def_bal_id NUMBER ;
395 l_exp_rep_id pqp_exception_reports.exception_report_id%TYPE ;
396 l_comp_type pqp_exception_reports.comparison_type%TYPE ;
397 l_comp_value pqp_exception_reports.comparison_value%TYPE ;
398 l_balance_type NUMBER ;
399 l_variance_type pqp_exception_reports.variance_type%TYPE ;
400 l_variance_value pqp_exception_reports.variance_value%TYPE ;
401 l_variance_operator pqp_exception_reports.variance_operator%TYPE;
402 l_rowcount NUMBER ;
403 l_prev_balance NUMBER:=0 ;
404 l_prev_balance1 NUMBER:=0 ;
405 l_pay_count NUMBER:=0 ;
406 l_tot_count NUMBER:=0 ;
407 l_max_balance NUMBER :=0 ;
408 l_total_balance NUMBER:=0 ;
409 l_return_value VARCHAR2(1) ;
410 l_errmsg VARCHAR2(15);
411 l_retvalue_count NUMBER ;
412 temp_date DATE ;
413 l_loop_count NUMBER:=0;
414 -- Nocopy changes
415 l_ret_value_nc t_ret_value ;
416 l_temp_date Date;
417 l_tax_year_start_date Date;
418
419 BEGIN
420 hr_utility.trace('Enter Get balances');
421 hr_utility.trace('Enter legislation code');
422
423 -- Nocopy changes
424 l_ret_value_nc := p_ret_value;
425
426
427 IF l_business_group_id_ct IS NULL OR
428 l_business_group_id_ct<>p_business_group_id
429 OR l_legislation_code IS NULL THEN
430 l_business_group_id_ct:=p_business_group_id;
431 l_legislation_code:=get_legislation_code(p_business_group_id);
432 END IF;
433
434 hr_utility.trace('Exit legislation code');
435 /*Check Report or Group id is entered by user*/
436 IF l_rep_detail.count=0 THEN
437 IF p_report_id IS NOT NULL THEN
438 hr_utility.trace('Enter Report id loop');
439 /*Get report detail*/
440 OPEN c_rep_name (l_legislation_code);
441 LOOP
442 FETCH c_rep_name INTO l_rep_detail(1).exception_report_id ,
443 l_rep_detail(1).balance_type_id ,
444 l_rep_detail(1).dimension_type_id,
445 l_rep_detail(1).variance_type,
446 l_rep_detail(1).variance_value,
447 l_rep_detail(1).variance_operator,
448 l_rep_detail(1).comparison_type,
449 l_rep_detail(1).comparison_value;
450 EXIT WHEN c_rep_name%NOTFOUND;
451 OPEN c_def_bal(l_rep_detail(1).balance_type_id,
452 l_rep_detail(1).dimension_type_id);
453 LOOP
454 FETCH c_def_bal INTO l_rep_detail(1).defined_balance_id;
455 EXIT WHEN c_def_bal%NOTFOUND;
456 END LOOP;
457 CLOSE c_def_bal;
458 END LOOP;
459 CLOSE c_rep_name;
460 hr_utility.trace('Leaving Report id loop');
461 ELSIF p_group_id IS NOT NULL AND p_report_id IS NULL THEN
462 hr_utility.trace('Enter Group loop');
463 l_count:=0;
464 /*Get Group detail*/
465 OPEN c_group_name (l_legislation_code) ;
466 LOOP
467 FETCH c_group_name INTO l_rep_detail(l_count+1).exception_report_id ,
468 l_rep_detail(l_count+1).balance_type_id ,
469 l_rep_detail(l_count+1).dimension_type_id,
470 l_rep_detail(l_count+1).variance_type,
471 l_rep_detail(l_count+1).variance_value,
472 l_rep_detail(l_count+1).variance_operator,
473 l_rep_detail(l_count+1).comparison_type,
474 l_rep_detail(l_count+1).comparison_value;
475
476 EXIT WHEN c_group_name%NOTFOUND;
477 OPEN c_def_bal(l_rep_detail(l_count+1).balance_type_id,
478 l_rep_detail(l_count+1).dimension_type_id);
479 LOOP
480 FETCH c_def_bal INTO l_rep_detail(l_count+1).defined_balance_id;
481 EXIT WHEN c_def_bal%NOTFOUND;
482 END LOOP;
483 CLOSE c_def_bal;
484 l_count:=l_count+1;
485 END LOOP;
486 CLOSE c_group_name;
487 END IF;
488 END IF;
489 IF l_date_detail.count<>0 AND
490 l_date_detail(1).payroll_id <> p_payroll_id THEN
491 l_date_detail.DELETE;
492
493 END IF;
494 /*Calculation based on comparison type*/
495 hr_utility.trace('Enter date calc loop');
496 IF l_date_detail.count=0 THEN
497 OPEN c_maxdate;
498 LOOP
499 FETCH c_maxdate INTO l_maxdate,l_maxdate1;
500 EXIT WHEN c_maxdate%NOTFOUND;
501 FOR i in 1..l_rep_detail.count
502 LOOP
503 l_def_bal_id := l_rep_detail(i).defined_balance_id;
504 l_comp_type := l_rep_detail(i).comparison_type;
505 l_comp_value := l_rep_detail(i).comparison_value;
506
507 hr_utility.trace('Enter conditions loop');
508
509 --Added by Gattu for tax year change
510 --Getting the financial tax year
511 l_tax_year_start_date := Get_Tax_Start_Date(l_legislation_code
512 ,l_maxdate
513 ,l_rep_detail(i).dimension_type_id);
514
515
516 IF l_comp_type='PP' THEN --Previous Period
517 OPEN c_prev_per(l_maxdate1);
518 LOOP
519 FETCH c_prev_per INTO l_prev_pay_period;
520 EXIT WHEN c_prev_per%NOTFOUND;
521 IF l_date_detail.count>=1 THEN
522 l_count1:=l_date_detail.count;
523 ELSE
524 l_count1:=0;
525 END IF;
526 l_date_detail(l_count1+1).exception_report_id:=l_rep_detail(i).exception_report_id;
527 l_date_detail(l_count1+1).defined_balance_id:=l_rep_detail(i).defined_balance_id;
528 l_date_detail(l_count1+1).payroll_id :=p_payroll_id;
529 l_date_detail(l_count1+1).pay_date :=l_maxdate;
530 l_date_detail(l_count1+2).exception_report_id:=l_rep_detail(i).exception_report_id;
531 l_date_detail(l_count1+2).defined_balance_id:=l_rep_detail(i).defined_balance_id;
532 l_date_detail(l_count1+2).payroll_id :=p_payroll_id;
533 l_date_detail(l_count1+2).pay_date :=l_prev_pay_period;
534
535 END LOOP;--for c_prev_per
536 CLOSE c_prev_per;
537 --Current Period or Current year etc
538 ELSIF l_comp_type='PC' OR l_comp_type='YC'
539 OR l_comp_type='QC' OR l_comp_type='MC'
540 OR l_comp_type='IC' THEN
541 IF l_date_detail.count>=1 THEN
542 l_count1:=l_date_detail.count;
543 ELSE
544 l_count1:=0;
545 END IF;
546 l_date_detail(l_count1+1).exception_report_id:=l_rep_detail(i).exception_report_id;
547 l_date_detail(l_count1+1).defined_balance_id:=l_rep_detail(i).defined_balance_id;
548 l_date_detail(l_count1+1).payroll_id :=p_payroll_id;
549 l_date_detail(l_count1+1).pay_date :=l_maxdate;
550
551 ELSIF l_comp_type='YP' THEN --Previous Year
552 IF l_date_detail.count>=1 THEN
553 l_count1:=l_date_detail.count;
554 ELSE
555 l_count1:=0;
556 END IF;
557 --Added by Gattu for tax year change
558 --Check the Tax year is null or not
559 --If not null then call this function to get last day of previous tax year
560 IF l_tax_year_start_date IS NOT NULL THEN
561 l_temp_date :=Get_Previous_Year_Tax_Date(l_tax_year_start_date,l_maxdate);
562 ELSE
563 Select LAST_DAY(ADD_MONTHS(l_maxdate,(12-to_char(l_maxdate,'MM')-12)))
564 INTO l_temp_date
565 FROM dual;
566 END IF;
567
568 l_date_detail(l_count1+1).exception_report_id:=l_rep_detail(i).exception_report_id;
569 l_date_detail(l_count1+1).defined_balance_id:=l_rep_detail(i).defined_balance_id;
570 l_date_detail(l_count1+1).payroll_id :=p_payroll_id;
571 l_date_detail(l_count1+1).pay_date :=l_maxdate;
572 l_date_detail(l_count1+2).exception_report_id:=l_rep_detail(i).exception_report_id;
573 l_date_detail(l_count1+2).defined_balance_id:=l_rep_detail(i).defined_balance_id;
574 l_date_detail(l_count1+2).payroll_id :=p_payroll_id;
575 l_date_detail(l_count1+2).pay_date :=l_temp_date;--LAST_DAY(ADD_MONTHS(l_maxdate,(12-to_char(l_maxdate,'MM')-12)));
576
577 ELSIF l_comp_type='QP' OR l_comp_type='QAN' THEN --Previous Quarter
578 IF l_date_detail.count>=1 THEN
579 l_count1:=l_date_detail.count;
580 ELSE
581 l_count1:=0;
582 END IF;
583
584 l_date_detail(l_count1+1).exception_report_id:=l_rep_detail(i).exception_report_id;
585 l_date_detail(l_count1+1).defined_balance_id:=l_rep_detail(i).defined_balance_id;
586 l_date_detail(l_count1+1).payroll_id :=p_payroll_id;
587 l_date_detail(l_count1+1).pay_date :=l_maxdate;
588 l_count1:=l_count1+1;
589 FOR j in 1..nvl(l_comp_value,1)
590 LOOP
591 l_date_detail(l_count1+j).exception_report_id
592 :=l_rep_detail(i).exception_report_id;
593 l_date_detail(l_count1+j).defined_balance_id
594 :=l_rep_detail(i).defined_balance_id;
595 l_date_detail(l_count1+j).payroll_id
596 :=p_payroll_id;
597
598 --Added by Gattu for tax year change
599 --Check the Tax year is null or not
600 --If not null then call this function to get last day of previous tax year
601 IF l_tax_year_start_date IS NOT NULL THEN
602 l_temp_date :=Get_Previous_Quarter_Tax_Date(l_tax_year_start_date,l_maxdate,j);
603 ELSE
604 SELECT LAST_DAY(ADD_MONTHS(l_maxdate,(DECODE(MOD(to_char(l_maxdate,'MM'),3),0,0,1,2,2,1)+
605 (j*-3))))
606 INTO l_temp_date
607 FROM dual;
608 END IF;
609
610 /*Adds 3 months to iterate quarter*/
611 --Commented for financial tax year change
612 /*SELECT LAST_DAY(ADD_MONTHS(l_maxdate,
613 (DECODE(MOD(to_char(l_maxdate,'MM'),3),0,0,1,2,2,1)+
614 (j*-3))))
615 INTO temp_date
616 FROM dual; */
617 l_date_detail(l_count1+j).pay_date :=l_temp_date;--temp_date--LAST_DAY(ADD_MONTHS(l_maxdate,
618 -- (3-MOD(to_char(l_maxdate,'MM'),3)+(j*-3))));
619 END LOOP ;--end for loop
620 --Previous Month or Average In Months
621 ELSIF l_comp_type='MP' OR l_comp_type='MAN' THEN
622 IF l_date_detail.count>=1 THEN
623 l_count1:=l_date_detail.count;
624 ELSE
625 l_count1:=0;
626 END IF;
627 l_date_detail(l_count1+1).exception_report_id:=l_rep_detail(i).exception_report_id;
628 l_date_detail(l_count1+1).defined_balance_id:=l_rep_detail(i).defined_balance_id;
629 l_date_detail(l_count1+1).payroll_id :=p_payroll_id;
630 l_date_detail(l_count1+1).pay_date :=l_maxdate;
631 l_count1:=l_count1+1;
632 FOR j in 1..nvl(l_comp_value,1)
633 LOOP
634 l_date_detail(l_count1+j).exception_report_id:=l_rep_detail(i).exception_report_id;
635 l_date_detail(l_count1+j).defined_balance_id:=l_rep_detail(i).defined_balance_id;
636 l_date_detail(l_count1+j).payroll_id :=p_payroll_id;
637 /*Adds months to iterate Months*/
638 l_date_detail(l_count1+j).pay_date :=LAST_DAY(ADD_MONTHS(l_maxdate,-j));
639 END LOOP ;--end for loop
640 --Average Of Paid Periods In Days
641 ELSIF l_comp_type='PADP' OR l_comp_type='PADT'
642 OR l_comp_type='PANT' OR l_comp_type='PANP' THEN
643 IF l_date_detail.count>=1 THEN
644 l_count1:=l_date_detail.count;
645 ELSE
646 l_count1:=0;
647 END IF;
648 l_date_detail(l_count1+1).exception_report_id:=l_rep_detail(i).exception_report_id;
649 l_date_detail(l_count1+1).defined_balance_id:=l_rep_detail(i).defined_balance_id;
650 l_date_detail(l_count1+1).payroll_id :=p_payroll_id;
651 l_date_detail(l_count1+1).pay_date :=l_maxdate;
652 l_count1:=l_count1+1;
653 IF l_comp_type ='PADP' OR l_comp_type='PADT' THEN
654 OPEN c_avg_per_days(l_maxdate1 , l_comp_value);
655 LOOP
656 FETCH c_avg_per_days INTO l_prev_pay_period;
657 EXIT WHEN c_avg_per_days%NOTFOUND;
658 l_count1:=l_count1+1;
659 l_date_detail(l_count1).exception_report_id:=l_rep_detail(i).exception_report_id;
660 l_date_detail(l_count1).defined_balance_id:=l_rep_detail(i).defined_balance_id;
661 l_date_detail(l_count1).payroll_id :=p_payroll_id;
662 l_date_detail(l_count1).pay_date :=l_prev_pay_period;
663 END LOOP;--endloop for c_avg_per_days
664 CLOSE c_avg_per_days;
665 --Average Of Previous Periods
666 ELSIF l_comp_type='PANT' OR l_comp_type='PANP' THEN
667 OPEN c_avg_per(l_maxdate1 , l_comp_value);
668 LOOP
669 FETCH c_avg_per INTO l_prev_pay_period;
670 EXIT WHEN c_avg_per%NOTFOUND;
671 l_count1:=l_count1+1;
672 l_date_detail(l_count1).exception_report_id:=l_rep_detail(i).exception_report_id;
673 l_date_detail(l_count1).defined_balance_id:=l_rep_detail(i).defined_balance_id;
674 l_date_detail(l_count1).payroll_id :=p_payroll_id;
675 l_date_detail(l_count1).pay_date :=l_prev_pay_period;
676 END LOOP;--endloop for c_avg_per
677 CLOSE c_avg_per;
678 END IF;
679 END IF; --end if for comparison type
680 END LOOP;--endloop for l_repdetail_table
681 END LOOP;
682 CLOSE c_maxdate;
683 hr_utility.trace('Leaving conditions loop');
684 hr_utility.trace('Complete Populating table');
685 END IF;
686 hr_utility.trace('Enter Balance and calc loop');
687 l_loop_count:=0;
688 l_rowcount:=1;
689 FOR i IN 1..l_rep_detail.count
690 LOOP
691 --l_rowcount:=0;
692 l_exp_rep_id :=l_rep_detail(i).exception_report_id;
693 l_balance_type:=l_rep_detail(i).balance_type_id;
694 l_variance_type:=l_rep_detail(i).variance_type;
695 l_variance_value:=l_rep_detail(i).variance_value;
696 l_variance_operator:=l_rep_detail(i).variance_operator;
697 l_comp_type:=l_rep_detail(i).comparison_type;
698 l_def_bal_id:=l_rep_detail(i).defined_balance_id ;
699 l_prev_balance:=0;
700 l_pay_count:=0 ;
701 l_tot_count:=0 ;
702 --l_rowcount:=l_rowcount+l_loop_count+1;
703 l_loop_count:=0;
704 FOR j in l_rowcount..l_date_detail.count
705 LOOP
706 IF l_def_bal_id=l_date_detail(j).defined_balance_id
707 AND l_exp_rep_id=l_date_detail(j).exception_report_id THEN
708 hr_utility.trace('Enter Balance call');
709 IF l_loop_count=0 THEN
710 l_max_balance:= get_value (p_assignment_id ,
711 l_def_bal_id,
712 l_date_detail(j).pay_date,
713 l_legislation_code,
714 l_comp_type,
715 l_errmsg);
716 l_loop_count:=l_loop_count+1;
717 ELSE
718 l_prev_balance1:= get_value (p_assignment_id ,
719 l_def_bal_id,
720 l_date_detail(j).pay_date,
721 l_legislation_code,
722 l_comp_type,
723 l_errmsg);
724
725 l_loop_count:=l_loop_count+1;
726 l_prev_balance:=l_prev_balance+l_prev_balance1;
727
728 IF l_errmsg='NOERROR' THEN
729 l_tot_count:=l_tot_count+1;
730 END IF;
731 hr_utility.trace('Leaving Balance call');
732 IF l_prev_balance1<>0 THEN
733 l_pay_count:=l_pay_count+1;
734 END IF;
735 END IF;
736 ELSE
737 --l_rowcount:=j-1;
738 l_rowcount:=j;
739 EXIT;
740 END IF;--end if for def balance comparison
741 hr_utility.trace('Enter final calc loop');
742 END LOOP;--endloop for l_date_detail forloop
743 IF l_comp_type ='PADT'OR l_comp_type ='PANT'
744 OR l_comp_type='QP'OR l_comp_type='QAN'
745 OR l_comp_type='MP'OR l_comp_type='MAN' THEN
746 IF l_prev_balance<>0 AND l_tot_count<>0 THEN
747 l_prev_balance:=l_prev_balance/l_tot_count;
748 END IF;
749 ELSIF l_comp_type='PADP'OR l_comp_type ='PANP' THEN
750 IF l_prev_balance<>0 AND l_pay_count<>0 THEN
751 l_prev_balance:=l_prev_balance/l_pay_count;
752 END IF;
753 END IF; --end if for comp_type
754 --
755 -- If the comp_type is Current period.
756 --
757 IF l_comp_type ='PC' OR l_comp_type ='YC'
758 OR l_comp_type='QC' OR l_comp_type='MC' THEN
759 l_total_balance:=l_max_balance;
760 IF l_rep_detail(i).variance_operator = '=' THEN
761 IF l_total_balance=l_rep_detail(i).variance_value THEN
762 l_return_value:='Y';
763 ELSE
764 l_return_value:='N' ;
765 END IF;
766 ELSIF l_rep_detail(i).variance_operator = '>=' THEN
767 IF l_total_balance >= l_rep_detail(i).variance_value THEN
768 l_return_value:='Y';
769 ELSE
770 l_return_value:='N' ;
771 END IF;
772 ELSIF l_rep_detail(i).variance_operator = '<=' THEN
773 IF (l_total_balance) <= l_rep_detail(i).variance_value THEN
774 l_return_value:='Y';
775 ELSE
776 l_return_value:='N' ;
777 END IF;
778 ELSIF l_rep_detail(i).variance_operator = '<' THEN
779 IF (l_total_balance) < l_rep_detail(i).variance_value THEN
780 l_return_value:='Y';
781 ELSE
782 l_return_value:='N' ;
783 END IF;
784 ELSIF l_rep_detail(i).variance_operator = '>' THEN
785 IF l_total_balance > l_rep_detail(i).variance_value THEN
786 l_return_value:='Y';
787 ELSE
788 l_return_value:='N';
789 END IF;
790 ELSIF l_rep_detail(i).variance_operator = '+/-' THEN
791 IF ABS(l_total_balance) >= l_rep_detail(i).variance_value THEN
792 l_return_value:='Y';
793 ELSE
794 l_return_value:='N' ;
795 END IF;
796 END IF;
797 --
798 -- For all other comp_types
799 --
800 ELSE
801 l_total_balance:=l_max_balance-l_prev_balance;
802 --
803 -- If the var_type is Percent
804 --
805 IF l_variance_type ='P' THEN
806 IF l_prev_balance<>0 THEN --Check to Avoid exception for zero divide
807 l_total_balance:=100*l_total_balance/l_prev_balance;
808 ELSE
809 l_total_balance:=l_total_balance;
810 END IF;
811 hr_utility.trace('Leaving final calc loop');
812 END IF;
813 -- Code for variance operator
814 IF l_rep_detail(i).variance_operator = '=' THEN
815 IF (l_total_balance)=l_rep_detail(i).variance_value THEN
816 l_return_value:='Y';
817 ELSE
818 l_return_value:='N' ;
819 END IF;
820 ELSIF l_rep_detail(i).variance_operator = '>=' THEN
821 IF l_total_balance >= l_rep_detail(i).variance_value THEN
822 l_return_value:='Y';
823 ELSE
824 l_return_value:='N' ;
825 END IF;
826 ELSIF l_rep_detail(i).variance_operator = '<=' THEN
827 IF (l_total_balance) >= l_rep_detail(i).variance_value AND l_total_balance <= 0 THEN
828 l_return_value:='Y';
829 ELSE
830 l_return_value:='N' ;
831 END IF;
832 ELSIF l_rep_detail(i).variance_operator = '<' THEN
833 IF (l_total_balance) > l_rep_detail(i).variance_value AND l_total_balance < 0 THEN
834 l_return_value:='Y';
835 ELSE
836 l_return_value:='N' ;
837 END IF;
838 ELSIF l_rep_detail(i).variance_operator = '>' THEN
839 IF l_total_balance > l_rep_detail(i).variance_value THEN
840 l_return_value:='Y';
841 ELSE
842 l_return_value:='N';
843 END IF;
844 ELSIF l_rep_detail(i).variance_operator = '+/-' THEN
845 IF ABS(l_total_balance) >= l_rep_detail(i).variance_value THEN
846 l_return_value:='Y';
847 ELSE
848 l_return_value:='N' ;
849 END IF;
850 END IF;
851 END IF;
852 --
853 l_retvalue_count:=p_ret_value.count+1;
854 p_ret_value(l_retvalue_count).exception_report_id:=l_exp_rep_id;
855 p_ret_value(l_retvalue_count).balance_type_id:=l_balance_type;
856 p_ret_value(l_retvalue_count).curent_balance:=l_max_balance;
857 p_ret_value(l_retvalue_count).previous_balance:=l_prev_balance;
858 p_ret_value(l_retvalue_count).ret_val:=l_return_value;
859 END LOOP; --end loop for repdetail for loop
860 hr_utility.trace('Leaving Balance and calc loop');
861 hr_utility.trace('Leaving Get Balances');
862
863 -- Added by tmehra for nocopy changes - Feb03
864 EXCEPTION
865 ---------
866 WHEN OTHERS THEN
867 hr_utility.trace('Error occurred' ||SQLERRM);
868 p_ret_value := l_ret_value_nc;
869 RAISE;
870
871 END;
872
873
874 PROCEDURE upd_payroll_actions (pactid in number,
875 p_payroll_id IN NUMBER ,
876 p_consolidation_set_id IN NUMBER,
877 p_effective_date IN DATE)
878 IS
879
880
881 CURSOR c_set_paydate
882 IS
883 SELECT MAX(pay_date)maxdate,
884 MIN(pay_st_date) mindate
885 FROM (SELECT MAX(ptp.start_date) pay_st_date,MAX(ptp.end_date) pay_date
886 FROM per_time_periods ptp
887 WHERE ptp.payroll_id IN (SELECT payroll_id
888 FROM pay_payroll_actions ppa
889 WHERE ppa.consolidation_set_id=p_consolidation_set_id
890 AND (payroll_id =p_payroll_id
891 OR p_payroll_id IS NULL)
892 AND ppa.date_earned <= p_effective_date)
893 AND ptp.end_date <= p_effective_date
894 GROUP BY ptp.payroll_id);
895 l_mindate DATE ;
896 l_maxdate DATE ;
897 l_payroll_id NUMBER ;
898 l_cutoff_date DATE ;
899 l_temp_date DATE ;
900 l_temp_date1 DATE ;
901 l_count NUMBER ;
902 BEGIN
903 hr_utility.trace('Enter update payroll action');
904 OPEN c_set_paydate ;
905 LOOP
906 FETCH c_set_paydate INTO l_maxdate,l_mindate;
907 EXIT WHEN c_set_paydate%NOTFOUND;
908 END LOOP;
909 CLOSE c_set_paydate;
910
911 UPDATE pay_payroll_actions
912 SET Start_date= NVL(l_mindate,p_effective_date)
913 , effective_date=NVL(l_maxdate,p_effective_date)
914 WHERE payroll_action_id=pactid;
915
916 hr_utility.trace('Leaving Update payroll action') ;
917
918 END;
919
920 PROCEDURE load_details (p_assignment IN NUMBER)
921 IS
922 msg1 varchar2(2000);
923 CURSOR per_det is
924 SELECT ppv.last_name,
925 ppv.first_name,
926 ppv.middle_names,
927 ppv.full_name,
928 ppv.date_of_birth,
929 ppv.national_identifier,
930 paf.assignment_number
931 FROM
932 per_all_people_f ppv,
933 per_assignments_f paf
934 WHERE paf.assignment_id=p_assignment
935 AND paf.person_id=ppv.person_id
936 AND l_effective_date BETWEEN ppv.effective_start_date
937 AND ppv.effective_end_date
938 AND l_effective_date BETWEEN paf.effective_start_date
939 AND paf.effective_end_date;
940
941 --ORDER BY ppv.last_update_date;
942
943
944 BEGIN
945 hr_utility.trace('Enter Load details');
946 l_last_name:='';
947 l_first_name:='';
948 l_middle_name:='';
949 l_full_name := '';
950 l_dob:='';
951 l_assignment_number:='';
952 l_national_id:='';
953
954
955
956 OPEN per_det;
957 LOOP
958
959 FETCH per_det into l_last_name,
960 l_first_name,
961 l_middle_name,
962 l_full_name,
963 l_dob,
964 l_national_id,
965 l_assignment_number;
966 EXIT when per_det%NOTFOUND;
967
968
969
970 END LOOP;
971 CLOSE per_det;
972
973 hr_utility.trace('Leaving Load details');
974
975 EXCEPTION
976 --------
977 WHEN OTHERS THEN
978 msg1:=SQLERRM;
979 hr_utility.trace('Error occurred load_er_liab ...' ||SQLERRM);
980 END load_details;
981
982
983
984
985
986
987 PROCEDURE load_data
988 (
989 actid IN NUMBER,
990 p_effective_date IN DATE
991 ) IS
992 CURSOR c_filterasg (p_payroll_id NUMBER)
993 IS
994 SELECT MAX(ptp.start_date),MAX(ptp.end_date) pay_date
995 FROM per_time_periods ptp
996 WHERE ptp.payroll_id=p_payroll_id
997 AND ptp.end_date <= p_effective_date;
998
999 CURSOR sel_aaid (l_pactid number
1000 )
1001 IS
1002 SELECT
1003 distinct paa1.assignment_id assignment_id,
1004 ppa_arch.start_date start_date,
1005 ppa_arch.effective_date end_date,
1006 ppa_arch.business_group_id business_group_id,
1007 ppa_arch.payroll_action_id payroll_action_id,
1008 ppa.effective_date effective_date,
1009 ppa.action_type action_type,
1010 paa1.tax_unit_id tax_unit_id,
1011 paf.payroll_id payroll_id,
1012 paf.organization_id organization_id,
1013 hou1.name organization_name,
1014 paf.location_id location_id,
1015 paa.chunk_number chnkno,
1016 paa.payroll_action_id pactid
1017 FROM
1018 hr_organization_units hou1,
1019 per_assignments_f paf,
1020 pay_payroll_actions ppa,
1021 pay_assignment_actions paa1,
1022 pay_action_interlocks pai,
1023 pay_assignment_actions paa,
1024 pay_payroll_actions ppa_arch
1025 WHERE paa.assignment_action_id = l_pactid
1026 AND paa.payroll_action_id = ppa_arch.payroll_action_id
1027 AND pai.locking_action_id = paa.assignment_action_id
1028 AND paa1.assignment_action_id = pai.locked_action_id
1029 AND ppa.payroll_action_id = paa1.payroll_action_id
1030 AND paf.assignment_id = paa1.assignment_id
1031 AND ppa.effective_date between paf.effective_start_date
1032 AND paf.effective_end_date
1033 AND hou1.organization_id = paf.organization_id;
1034
1035 l_payroll_id NUMBER ;
1036 l_cur_balance NUMBER ;
1037 l_prev_balance NUMBER ;
1038 l_return_value VARCHAR2(1);
1039 l_balancetype_id NUMBER ;
1040 l_exp_rep_id NUMBER ;
1041 pactid NUMBER;
1042 chnkno NUMBER;
1043 l_ret_value t_ret_value;
1044 --a number;
1045 l_sdate DATE;
1046 l_edate DATE;
1047 l_act_date DATE;
1048 l_offset_date NUMBER;
1049
1050 BEGIN
1051 hr_utility.trace('ACTID = '||actid);
1052 hr_utility.trace('Enter Load data');
1053 OPEN sel_aaid (actid);
1054 LOOP
1055 FETCH sel_aaid INTO l_assignment_id,
1056 l_start_date,
1057 l_end_date,
1058 l_business_group_id,
1059 l_payroll_action_id,
1060 l_effective_date,
1061 l_action_type,
1062 l_tax_unit_id,
1063 l_payroll_id,
1064 l_organization_id,
1065 l_org_name,
1066 l_location_id,
1067 chnkno,
1068 pactid
1069 ;
1070
1071 EXIT when sel_aaid%notfound;
1072 IF l_param_count<>1 THEN
1073 BEGIN
1074 l_param_count:=1;
1075 SELECT ppa.legislative_parameters,
1076 ppa.business_group_id,
1077 ppa.start_date,
1078 ppa.effective_date,
1079 pqp_exppreproc_pkg.get_parameter('TRANSFER_CONC_SET',ppa.legislative_parameters),
1080 pqp_exppreproc_pkg.get_parameter('TRANSFER_PAYROLL',ppa.legislative_parameters),
1081 pqp_exppreproc_pkg.get_parameter('TRANSFER_REPORT',ppa.legislative_parameters),
1082 pqp_exppreproc_pkg.get_parameter('TRANSFER_GROUP',ppa.legislative_parameters),
1083 pqp_exppreproc_pkg.get_parameter('TRANSFER_PPA_FINDER',ppa.legislative_parameters),
1084 pqp_exppreproc_pkg.get_parameter('TRANSFER_DATE',ppa.legislative_parameters),
1085 pqp_exppreproc_pkg.get_parameter('TRANSFER_VARTYPE',ppa.legislative_parameters),
1086 pqp_exppreproc_pkg.get_parameter('TRANSFER_VARVALUE',ppa.legislative_parameters),
1087 pqp_exppreproc_pkg.get_parameter('TRANSFER_GRE',ppa.legislative_parameters) ,
1088 pqp_exppreproc_pkg.get_parameter('TRANSFER_JD',ppa.legislative_parameters) ,
1089 ppa.payroll_action_id
1090 INTO l_leg_param,
1091 l_business_group_id,
1092 l_leg_start_date,
1093 l_leg_end_date,
1094 t_consolidation_set_id,
1095 t_payroll_id,
1096 l_report_id ,
1097 l_group_id,
1098 l_ppa_finder,
1099 l_date,
1100 l_vartype,
1101 l_varvalue,
1102 g_gre_id,
1103 g_jd_code,
1104 t_payroll_action_id
1105 FROM pay_payroll_actions ppa
1106 WHERE ppa.payroll_action_id = pactid;
1107
1108 EXCEPTION
1109 ---------
1110 WHEN NO_DATA_FOUND THEN
1111 hr_utility.trace('Legislative Details not found...');
1112 RAISE;
1113 END;
1114 END IF;
1115 hr_utility.trace('Number of Records fetched = '||to_char(sel_aaid%ROWCOUNT));
1116 hr_utility.trace('Payroll Action ID = '||to_char(l_payroll_action_id));
1117 hr_utility.trace('Effective Date = '||to_char(l_effective_date));
1118 hr_utility.trace('Action Type = '||l_action_type);
1119 hr_utility.trace('Asg Act ID = '||to_char(l_assignment_action_id));
1120 hr_utility.trace('Asg ID = '||to_char(l_assignment_id));
1121 IF l_group_id IS NOT NULL THEN
1122 l_vartype:=NULL;
1123 l_varvalue:=NULL;
1124 END IF;
1125 OPEN c_filterasg(l_payroll_id);
1126 LOOP
1127 FETCH c_filterasg into l_sdate,l_edate;
1128 EXIT WHEN c_filterasg%NOTFOUND;
1129 END LOOP;
1130 CLOSE c_filterasg;
1131 --Added by Gattu to fix Tar#3837327.999
1132 l_offset_date := get_offset_date
1133 (l_payroll_id
1134 ,t_consolidation_set_id
1135 ,l_effective_date );
1136 IF l_offset_date <> 0 THEN
1137 l_act_date :=l_edate;
1138 ELSE
1139 l_act_date := l_effective_date;
1140 END IF;
1141 IF l_act_date BETWEEN l_sdate AND l_edate THEN
1142 --IF l_effective_date BETWEEN l_sdate AND l_edate THEN
1143 load_details(l_assignment_id);
1144 get_balances( pactid ,
1145 l_assignment_id ,
1146 l_business_group_id ,
1147 l_payroll_id ,
1148 l_report_id ,
1149 l_group_id ,
1150 l_vartype,
1151 l_varvalue,
1152 l_edate,
1153 l_ret_value );
1154
1155 FOR i in 1..l_ret_value.count
1156 LOOP
1157 IF l_ret_value(i).ret_val='Y' THEN
1158 load_balances(l_assignment_id ,
1159 l_effective_date ,
1160 l_ret_value(i).balance_type_id,
1161 l_ret_value(i).curent_balance ,
1162 l_ret_value(i).previous_balance,
1163 l_ret_value(i).exception_report_id,
1164 l_group_id,
1165 l_payroll_id ,
1166 l_ppa_finder,
1167 l_business_group_id ) ;
1168 END IF;
1169 END LOOP;
1170 END IF;
1171 END LOOP;
1172 hr_utility.trace('End of LOAD DATA');
1173 CLOSE sel_aaid;
1174 hr_utility.trace('Leaving Load data');
1175 EXCEPTION
1176 ---------
1177 WHEN OTHERS THEN
1178 hr_utility.trace('Error occurred load_data ...' ||SQLERRM);
1179 RAISE;
1180 END load_data;
1181
1182 --This function determines if there are any offset date.
1183 FUNCTION get_offset_date (
1184 p_payroll_id IN NUMBER
1185 ,p_consolidation_id IN NUMBER
1186 ,p_effective_date IN DATE )
1187 RETURN NUMBER
1188 IS
1189 CURSOR c_get_value
1190 IS
1191 SELECT ppf.pay_date_offset pod
1192 FROM pay_payrolls_f ppf
1193 WHERE ppf.payroll_id= p_payroll_id
1194 AND ppf.consolidation_set_id=p_consolidation_id
1195 AND p_effective_date BETWEEN ppf.effective_start_date
1196 AND ppf.effective_end_date;
1197 l_get_value c_get_value%ROWTYPE;
1198 BEGIN
1199 OPEN c_get_value;
1200 FETCH c_get_value into l_get_value ;
1201 CLOSE c_get_value;
1202 RETURN NVL(l_get_value.pod,0);
1203
1204 EXCEPTION
1205 ---------
1206 WHEn OTHERS THEN
1207 RETURN(0);
1208 END;
1209
1210
1211 PROCEDURE run_preprocess ( actid IN NUMBER,
1212 p_effective_date IN DATE )
1213 IS
1214 ppa_finder NUMBER;
1215 l_param NUMBER;
1216 l_trace VARCHAR2(30):=0;
1217 v_cursor NUMBER;
1218 v_alter_string VARCHAR2(100);
1219 v_numrows NUMBER;
1220 BEGIN
1221
1222 hr_utility.trace('Enter run preprocess');
1223 -- ppa_finder := pqp_ustiaa_pkg.get_parameter('TRANSFER_PPA_FINDER',l_param);
1224
1225 load_data(actid,
1226 p_effective_date );
1227 hr_utility.trace('Leaving run preprocess');
1228 EXCEPTION
1229 ---------
1230 WHEN no_data_found THEN
1231 RAISE;
1232
1233 END;
1234
1235 -- =============================================================================
1236 -- Get_Tax_Start_Date
1237 -- =============================================================================
1238 FUNCTION Get_Tax_Start_Date
1239 (p_legislation_code IN VARCHAR2
1240 ,p_effective_date IN Date
1241 ,p_dimension_type_id IN pay_balance_dimensions.balance_dimension_id%TYPE
1242 ) RETURN date IS
1243
1244 CURSOR c_tax_start_date(c_dimension_type_id IN NUMBER
1245 ,c_legislation_code IN VARCHAR2) IS
1246 SELECT pers.year_begin_date
1247 FROM pqp_exception_report_suffix pers
1248 WHERE pers.database_item_suffix =(
1249 SELECT database_item_suffix
1250 FROM pay_balance_dimensions
1251 WHERE balance_dimension_id =c_dimension_type_id
1252 AND legislation_code=c_legislation_code)
1253 AND pers.legislation_code=c_legislation_code;
1254
1255
1256 l_tax_year_start pqp_exception_report_suffix.year_begin_date%TYPE;
1257 l_tax_year_start_dt Date;
1258 l_proc_name Varchar2(150) := g_proc_name ||'Get_Tax_Start_Date';
1259
1260 BEGIN
1261 Hr_Utility.set_location('Entering: '||l_proc_name, 5);
1262 Hr_Utility.set_location('p_legislation_code '||p_legislation_code, 5);
1263 Hr_Utility.set_location('p_effective_date '||p_effective_date, 5);
1264 Hr_Utility.set_location('p_dimension_type_id '||p_dimension_type_id, 5);
1265
1266
1267 OPEN c_tax_start_date(p_dimension_type_id,p_legislation_code);
1268 FETCH c_tax_start_date INTO l_tax_year_start;
1269 CLOSE c_tax_start_date;
1270
1271 Hr_Utility.set_location('l_tax_year_start '||l_tax_year_start, 5);
1272
1273
1274 IF l_tax_year_start IS NOT NULL THEN
1275 SELECT fnd_date.canonical_to_date(to_char(p_effective_date,'YYYY') ||
1276 substr(fnd_date.date_to_canonical(l_tax_year_start), 6, 5))
1277 INTO l_tax_year_start_dt from dual;
1278
1279 --SELECT to_date(to_char(l_tax_year_start,'DD-MON-')|| to_char(p_effective_date,'YYYY'),'DD-MON-YYYY')
1280 --INTO l_tax_year_start_dt from dual;
1281 END IF;
1282 Hr_Utility.set_location('l_tax_year_start_dt '||l_tax_year_start_dt, 5);
1283 Hr_Utility.set_location('Leaving: '||l_proc_name, 5);
1284 RETURN l_tax_year_start_dt;
1285
1286 EXCEPTION
1287 WHEN Others THEN
1288 Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
1289 RETURN null;
1290 END Get_Tax_Start_Date;
1291
1292 -- =============================================================================
1293 -- Get_Previous_Year_Tax_Date
1294 -- =============================================================================
1295 FUNCTION Get_Previous_Year_Tax_Date
1296 (p_tax_year_start_date IN Date
1297 ,p_effective_date IN Date ) RETURN date IS
1298
1299
1300 l_previous_year_tax_dt Date;
1301 l_proc_name Varchar2(150) := g_proc_name ||'Get_Previous_Year_Tax_Date';
1302
1303 BEGIN
1304 Hr_Utility.set_location('Entering: '||l_proc_name, 5);
1305 Hr_Utility.set_location('p_effective_date: '||p_effective_date, 5);
1306 Hr_Utility.set_location('p_tax_year_start_date:'||p_tax_year_start_date, 5);
1307
1308 IF p_tax_year_start_date IS NOT NULL THEN
1309 IF p_tax_year_start_date > p_effective_date THEN
1310 l_previous_year_tax_dt := ADD_MONTHS(p_tax_year_start_date ,-12)-1;
1311 ELSE
1312 l_previous_year_tax_dt := p_tax_year_start_date-1;
1313 END IF;
1314 END IF;
1315 Hr_Utility.set_location('l_previous_year_tax_dt '||l_previous_year_tax_dt, 5);
1316 Hr_Utility.set_location('Leaving: '||l_proc_name, 5);
1317 RETURN l_previous_year_tax_dt;
1318
1319 EXCEPTION
1320 WHEN Others THEN
1321 Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
1322 RETURN null;
1323 END Get_Previous_Year_Tax_Date;
1324
1325
1326 -- =============================================================================
1327 -- Get_Previous_Quarter_Tax_Date
1328 -- =============================================================================
1329 FUNCTION Get_Previous_Quarter_Tax_Date
1330 (p_tax_year_start_date IN Date
1331 ,p_effective_date IN Date
1332 ,p_count IN NUMBER) RETURN DATE IS
1333
1334
1335 l_previous_quater_tax_dt Date;
1336 l_proc_name Varchar2(150) := g_proc_name ||'Get_Previous_Quarter_Tax_Date';
1337 l_tax_year_start_date Date;
1338 l_sign number;
1339 BEGIN
1340 Hr_Utility.set_location('Entering: '||l_proc_name, 5);
1341 Hr_Utility.set_location('p_tax_year_start_date:'||p_tax_year_start_date, 5);
1342 Hr_Utility.set_location('p_effective_date: '||p_effective_date, 5);
1343 Hr_Utility.set_location('p_count: '||p_count, 5);
1344
1345 IF p_tax_year_start_date IS NOT NULL THEN
1346 IF p_tax_year_start_date > p_effective_date THEN
1347 l_tax_year_start_date := ADD_MONTHS(p_tax_year_start_date ,-12);
1348 ELSE
1349 l_tax_year_start_date := p_tax_year_start_date;
1350 END IF;
1351
1352 SELECT SIGN ( ADD_MONTHS(l_tax_year_start_date,3 )-p_effective_date)
1353 INTO l_sign
1354 FROM dual;
1355
1356 IF l_sign = 1 or l_sign =0 THEN
1357 l_previous_quater_tax_dt:=l_tax_year_start_date-1;
1358 ELSE
1359 SELECT SIGN ( ADD_MONTHS(l_tax_year_start_date,6 )-p_effective_date)
1360 INTO l_sign
1361 FROM dual;
1362 IF l_sign = 1 or l_sign =0 THEN
1363 l_previous_quater_tax_dt:=add_months ((l_tax_year_start_date-1),3);
1364 ELSE
1365 SELECT SIGN ( ADD_MONTHS(l_tax_year_start_date,9 )-p_effective_date)
1366 INTO l_sign
1367 FROM dual;
1368 IF l_sign = 1 or l_sign =0 THEN
1369 l_previous_quater_tax_dt:=add_months ((l_tax_year_start_date-1),6);
1370 ELSE
1371 SELECT SIGN ( ADD_MONTHS(l_tax_year_start_date,12 )-p_effective_date)
1372 INTO l_sign
1373 FROM dual;
1374 IF l_sign = 1 or l_sign =0 THEN
1375 l_previous_quater_tax_dt:=add_months ((l_tax_year_start_date-1),9);
1376 END IF;
1377 END IF;
1378 END IF;
1379 END IF;
1380 IF p_count > 1 THEN
1381 l_previous_quater_tax_dt:= ADD_MONTHS(l_previous_quater_tax_dt,((p_count-1)*-3));
1382 END IF;
1383 END IF;
1384
1385 Hr_Utility.set_location('l_previous_quater_tax_dt '||l_previous_quater_tax_dt, 5);
1386 Hr_Utility.set_location('Leaving: '||l_proc_name, 5);
1387 RETURN l_previous_quater_tax_dt;
1388
1389 EXCEPTION
1390 WHEN Others THEN
1391 Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
1392 RETURN null;
1393 END Get_Previous_Quarter_Tax_Date;
1394 ------------------------------ end load data -------------------------------
1395 END ;