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