50: buffer RAW( 32767 );
51: res BLOB;
52: lob_len PLS_INTEGER := DBMS_LOB.getLength( c );
53: BEGIN
54: Hr_Utility.set_location('Entering: pay_ie_cess_report.c2b',260);
55: DBMS_LOB.createTemporary( res, TRUE );
56: DBMS_LOB.OPEN( res, DBMS_LOB.LOB_ReadWrite );
57:
58:
66: pos := pos + 16000;
67: EXIT WHEN pos > lob_len;
68: END LOOP;
69:
70: Hr_Utility.set_location('Leaving: pay_ie_cess_report.c2b',265);
71: RETURN res; -- res is OPEN here
72: END c2b;
73:
74: PROCEDURE get_parameters(p_payroll_action_id IN NUMBER,
92: l_proc VARCHAR2(50) := g_package || 'get_parameters';
93:
94: BEGIN
95:
96: hr_utility.set_location('Entering ' || l_proc,10);
97:
98: hr_utility.set_location('Step ' || l_proc,20);
99: hr_utility.set_location('p_token_name = ' || p_token_name,20);
100:
94: BEGIN
95:
96: hr_utility.set_location('Entering ' || l_proc,10);
97:
98: hr_utility.set_location('Step ' || l_proc,20);
99: hr_utility.set_location('p_token_name = ' || p_token_name,20);
100:
101: OPEN csr_parameter_info(p_payroll_action_id,
102: p_token_name);
95:
96: hr_utility.set_location('Entering ' || l_proc,10);
97:
98: hr_utility.set_location('Step ' || l_proc,20);
99: hr_utility.set_location('p_token_name = ' || p_token_name,20);
100:
101: OPEN csr_parameter_info(p_payroll_action_id,
102: p_token_name);
103:
117: p_token_value := l_token_value;
118:
119: END IF;
120:
121: hr_utility.set_location('l_token_value = ' || l_token_value,20);
122: hr_utility.set_location('Leaving ' || l_proc,30);
123:
124: END get_parameters;
125:
118:
119: END IF;
120:
121: hr_utility.set_location('l_token_value = ' || l_token_value,20);
122: hr_utility.set_location('Leaving ' || l_proc,30);
123:
124: END get_parameters;
125:
126:
209: l_asg_end_date DATE;
210: l_last_end_date DATE;
211:
212: BEGIN
213: hr_utility.set_location('Entering ' || l_proc,20);
214: hr_utility.set_location('Step ' || l_proc,20);
215: hr_utility.set_location('p_action_context_id = ' || p_action_context_id,20);
216: hr_utility.set_location('p_assignment_id = ' || p_assignment_id,20);
217: hr_utility.set_location('p_person_id = ' || p_person_id,20);
210: l_last_end_date DATE;
211:
212: BEGIN
213: hr_utility.set_location('Entering ' || l_proc,20);
214: hr_utility.set_location('Step ' || l_proc,20);
215: hr_utility.set_location('p_action_context_id = ' || p_action_context_id,20);
216: hr_utility.set_location('p_assignment_id = ' || p_assignment_id,20);
217: hr_utility.set_location('p_person_id = ' || p_person_id,20);
218: hr_utility.set_location('g_paye_ref = ' || g_paye_ref,20);
211:
212: BEGIN
213: hr_utility.set_location('Entering ' || l_proc,20);
214: hr_utility.set_location('Step ' || l_proc,20);
215: hr_utility.set_location('p_action_context_id = ' || p_action_context_id,20);
216: hr_utility.set_location('p_assignment_id = ' || p_assignment_id,20);
217: hr_utility.set_location('p_person_id = ' || p_person_id,20);
218: hr_utility.set_location('g_paye_ref = ' || g_paye_ref,20);
219: hr_utility.set_location('p_termination_date = ' || p_termination_date,20);
212: BEGIN
213: hr_utility.set_location('Entering ' || l_proc,20);
214: hr_utility.set_location('Step ' || l_proc,20);
215: hr_utility.set_location('p_action_context_id = ' || p_action_context_id,20);
216: hr_utility.set_location('p_assignment_id = ' || p_assignment_id,20);
217: hr_utility.set_location('p_person_id = ' || p_person_id,20);
218: hr_utility.set_location('g_paye_ref = ' || g_paye_ref,20);
219: hr_utility.set_location('p_termination_date = ' || p_termination_date,20);
220:
213: hr_utility.set_location('Entering ' || l_proc,20);
214: hr_utility.set_location('Step ' || l_proc,20);
215: hr_utility.set_location('p_action_context_id = ' || p_action_context_id,20);
216: hr_utility.set_location('p_assignment_id = ' || p_assignment_id,20);
217: hr_utility.set_location('p_person_id = ' || p_person_id,20);
218: hr_utility.set_location('g_paye_ref = ' || g_paye_ref,20);
219: hr_utility.set_location('p_termination_date = ' || p_termination_date,20);
220:
221:
214: hr_utility.set_location('Step ' || l_proc,20);
215: hr_utility.set_location('p_action_context_id = ' || p_action_context_id,20);
216: hr_utility.set_location('p_assignment_id = ' || p_assignment_id,20);
217: hr_utility.set_location('p_person_id = ' || p_person_id,20);
218: hr_utility.set_location('g_paye_ref = ' || g_paye_ref,20);
219: hr_utility.set_location('p_termination_date = ' || p_termination_date,20);
220:
221:
222:
215: hr_utility.set_location('p_action_context_id = ' || p_action_context_id,20);
216: hr_utility.set_location('p_assignment_id = ' || p_assignment_id,20);
217: hr_utility.set_location('p_person_id = ' || p_person_id,20);
218: hr_utility.set_location('g_paye_ref = ' || g_paye_ref,20);
219: hr_utility.set_location('p_termination_date = ' || p_termination_date,20);
220:
221:
222:
223: -- get deceased flag, date of leaving
228:
229: p_deceased_flag := l_deceased_flg;
230:
231: l_asg_end_date := l_termination_date;
232: hr_utility.set_location('l_termination_date = ' || l_termination_date,21);
233:
234: /* If employee is not terminated using end employment check for asg end date */
235: IF l_termination_date IS NULL THEN
236: /* Get End Date of Employement with Employer */
241: OPEN cur_get_asg_end_date;
242: FETCH cur_get_asg_end_date INTO l_asg_end_date;
243: CLOSE cur_get_asg_end_date;
244: END IF;
245: hr_utility.set_location('l_termination_date = ' || l_termination_date,22);
246: p_termination_date := l_termination_date;
247: OPEN cur_supp_run;
248: FETCH cur_supp_run INTO l_last_end_date;
249: hr_utility.set_location('l_last_end_date = '|| l_last_end_date,20);
245: hr_utility.set_location('l_termination_date = ' || l_termination_date,22);
246: p_termination_date := l_termination_date;
247: OPEN cur_supp_run;
248: FETCH cur_supp_run INTO l_last_end_date;
249: hr_utility.set_location('l_last_end_date = '|| l_last_end_date,20);
250: IF l_last_end_date IS NOT NULL THEN
251: p_supp_pymt_date := p_date_earned;
252: p_supp_flag:= 'Y';
253: p_termination_date := l_last_end_date;
475:
476:
477: BEGIN
478: --
479: hr_utility.set_location('Entering ' || l_proc,20);
480: hr_utility.set_location('Step ' || l_proc,20);
481: hr_utility.set_location('p_action_context_id = ' || p_action_context_id,20);
482: hr_utility.set_location('p_assignment_id = ' || p_assignment_id,20);
483: hr_utility.set_location('p_payroll_id = ' || p_payroll_id,20);
476:
477: BEGIN
478: --
479: hr_utility.set_location('Entering ' || l_proc,20);
480: hr_utility.set_location('Step ' || l_proc,20);
481: hr_utility.set_location('p_action_context_id = ' || p_action_context_id,20);
482: hr_utility.set_location('p_assignment_id = ' || p_assignment_id,20);
483: hr_utility.set_location('p_payroll_id = ' || p_payroll_id,20);
484: hr_utility.set_location('p_date_earned = ' || p_date_earned,20);
477: BEGIN
478: --
479: hr_utility.set_location('Entering ' || l_proc,20);
480: hr_utility.set_location('Step ' || l_proc,20);
481: hr_utility.set_location('p_action_context_id = ' || p_action_context_id,20);
482: hr_utility.set_location('p_assignment_id = ' || p_assignment_id,20);
483: hr_utility.set_location('p_payroll_id = ' || p_payroll_id,20);
484: hr_utility.set_location('p_date_earned = ' || p_date_earned,20);
485: hr_utility.set_location('p_child_run_ass_act_id = ' || p_child_run_ass_act_id,20);
478: --
479: hr_utility.set_location('Entering ' || l_proc,20);
480: hr_utility.set_location('Step ' || l_proc,20);
481: hr_utility.set_location('p_action_context_id = ' || p_action_context_id,20);
482: hr_utility.set_location('p_assignment_id = ' || p_assignment_id,20);
483: hr_utility.set_location('p_payroll_id = ' || p_payroll_id,20);
484: hr_utility.set_location('p_date_earned = ' || p_date_earned,20);
485: hr_utility.set_location('p_child_run_ass_act_id = ' || p_child_run_ass_act_id,20);
486: hr_utility.set_location('p_supp_flag = ' || p_supp_flag,20);
479: hr_utility.set_location('Entering ' || l_proc,20);
480: hr_utility.set_location('Step ' || l_proc,20);
481: hr_utility.set_location('p_action_context_id = ' || p_action_context_id,20);
482: hr_utility.set_location('p_assignment_id = ' || p_assignment_id,20);
483: hr_utility.set_location('p_payroll_id = ' || p_payroll_id,20);
484: hr_utility.set_location('p_date_earned = ' || p_date_earned,20);
485: hr_utility.set_location('p_child_run_ass_act_id = ' || p_child_run_ass_act_id,20);
486: hr_utility.set_location('p_supp_flag = ' || p_supp_flag,20);
487: hr_utility.set_location('p_person_id = ' || p_person_id,20);
480: hr_utility.set_location('Step ' || l_proc,20);
481: hr_utility.set_location('p_action_context_id = ' || p_action_context_id,20);
482: hr_utility.set_location('p_assignment_id = ' || p_assignment_id,20);
483: hr_utility.set_location('p_payroll_id = ' || p_payroll_id,20);
484: hr_utility.set_location('p_date_earned = ' || p_date_earned,20);
485: hr_utility.set_location('p_child_run_ass_act_id = ' || p_child_run_ass_act_id,20);
486: hr_utility.set_location('p_supp_flag = ' || p_supp_flag,20);
487: hr_utility.set_location('p_person_id = ' || p_person_id,20);
488: hr_utility.set_location('p_termination_date = ' || p_termination_date,20);
481: hr_utility.set_location('p_action_context_id = ' || p_action_context_id,20);
482: hr_utility.set_location('p_assignment_id = ' || p_assignment_id,20);
483: hr_utility.set_location('p_payroll_id = ' || p_payroll_id,20);
484: hr_utility.set_location('p_date_earned = ' || p_date_earned,20);
485: hr_utility.set_location('p_child_run_ass_act_id = ' || p_child_run_ass_act_id,20);
486: hr_utility.set_location('p_supp_flag = ' || p_supp_flag,20);
487: hr_utility.set_location('p_person_id = ' || p_person_id,20);
488: hr_utility.set_location('p_termination_date = ' || p_termination_date,20);
489: hr_utility.set_location('p_child_pay_action = ' || p_child_pay_action,20);
482: hr_utility.set_location('p_assignment_id = ' || p_assignment_id,20);
483: hr_utility.set_location('p_payroll_id = ' || p_payroll_id,20);
484: hr_utility.set_location('p_date_earned = ' || p_date_earned,20);
485: hr_utility.set_location('p_child_run_ass_act_id = ' || p_child_run_ass_act_id,20);
486: hr_utility.set_location('p_supp_flag = ' || p_supp_flag,20);
487: hr_utility.set_location('p_person_id = ' || p_person_id,20);
488: hr_utility.set_location('p_termination_date = ' || p_termination_date,20);
489: hr_utility.set_location('p_child_pay_action = ' || p_child_pay_action,20);
490: hr_utility.set_location('p_supp_pymt_date = ' || p_supp_pymt_date,20);
483: hr_utility.set_location('p_payroll_id = ' || p_payroll_id,20);
484: hr_utility.set_location('p_date_earned = ' || p_date_earned,20);
485: hr_utility.set_location('p_child_run_ass_act_id = ' || p_child_run_ass_act_id,20);
486: hr_utility.set_location('p_supp_flag = ' || p_supp_flag,20);
487: hr_utility.set_location('p_person_id = ' || p_person_id,20);
488: hr_utility.set_location('p_termination_date = ' || p_termination_date,20);
489: hr_utility.set_location('p_child_pay_action = ' || p_child_pay_action,20);
490: hr_utility.set_location('p_supp_pymt_date = ' || p_supp_pymt_date,20);
491: hr_utility.set_location('p_deceased_flag = ' || p_deceased_flag,20);
484: hr_utility.set_location('p_date_earned = ' || p_date_earned,20);
485: hr_utility.set_location('p_child_run_ass_act_id = ' || p_child_run_ass_act_id,20);
486: hr_utility.set_location('p_supp_flag = ' || p_supp_flag,20);
487: hr_utility.set_location('p_person_id = ' || p_person_id,20);
488: hr_utility.set_location('p_termination_date = ' || p_termination_date,20);
489: hr_utility.set_location('p_child_pay_action = ' || p_child_pay_action,20);
490: hr_utility.set_location('p_supp_pymt_date = ' || p_supp_pymt_date,20);
491: hr_utility.set_location('p_deceased_flag = ' || p_deceased_flag,20);
492: hr_utility.set_location('p_last_cess_action = ' || p_last_cess_action,20);
485: hr_utility.set_location('p_child_run_ass_act_id = ' || p_child_run_ass_act_id,20);
486: hr_utility.set_location('p_supp_flag = ' || p_supp_flag,20);
487: hr_utility.set_location('p_person_id = ' || p_person_id,20);
488: hr_utility.set_location('p_termination_date = ' || p_termination_date,20);
489: hr_utility.set_location('p_child_pay_action = ' || p_child_pay_action,20);
490: hr_utility.set_location('p_supp_pymt_date = ' || p_supp_pymt_date,20);
491: hr_utility.set_location('p_deceased_flag = ' || p_deceased_flag,20);
492: hr_utility.set_location('p_last_cess_action = ' || p_last_cess_action,20);
493: hr_utility.set_location('p_prev_src_id = ' || p_prev_src_id,20);
486: hr_utility.set_location('p_supp_flag = ' || p_supp_flag,20);
487: hr_utility.set_location('p_person_id = ' || p_person_id,20);
488: hr_utility.set_location('p_termination_date = ' || p_termination_date,20);
489: hr_utility.set_location('p_child_pay_action = ' || p_child_pay_action,20);
490: hr_utility.set_location('p_supp_pymt_date = ' || p_supp_pymt_date,20);
491: hr_utility.set_location('p_deceased_flag = ' || p_deceased_flag,20);
492: hr_utility.set_location('p_last_cess_action = ' || p_last_cess_action,20);
493: hr_utility.set_location('p_prev_src_id = ' || p_prev_src_id,20);
494: hr_utility.set_location('g_paye_ref = ' || g_paye_ref,20);
487: hr_utility.set_location('p_person_id = ' || p_person_id,20);
488: hr_utility.set_location('p_termination_date = ' || p_termination_date,20);
489: hr_utility.set_location('p_child_pay_action = ' || p_child_pay_action,20);
490: hr_utility.set_location('p_supp_pymt_date = ' || p_supp_pymt_date,20);
491: hr_utility.set_location('p_deceased_flag = ' || p_deceased_flag,20);
492: hr_utility.set_location('p_last_cess_action = ' || p_last_cess_action,20);
493: hr_utility.set_location('p_prev_src_id = ' || p_prev_src_id,20);
494: hr_utility.set_location('g_paye_ref = ' || g_paye_ref,20);
495:
488: hr_utility.set_location('p_termination_date = ' || p_termination_date,20);
489: hr_utility.set_location('p_child_pay_action = ' || p_child_pay_action,20);
490: hr_utility.set_location('p_supp_pymt_date = ' || p_supp_pymt_date,20);
491: hr_utility.set_location('p_deceased_flag = ' || p_deceased_flag,20);
492: hr_utility.set_location('p_last_cess_action = ' || p_last_cess_action,20);
493: hr_utility.set_location('p_prev_src_id = ' || p_prev_src_id,20);
494: hr_utility.set_location('g_paye_ref = ' || g_paye_ref,20);
495:
496: hr_utility.set_location('before PPSN cursor l_ppsn_override = ' || l_ppsn_override,20);
489: hr_utility.set_location('p_child_pay_action = ' || p_child_pay_action,20);
490: hr_utility.set_location('p_supp_pymt_date = ' || p_supp_pymt_date,20);
491: hr_utility.set_location('p_deceased_flag = ' || p_deceased_flag,20);
492: hr_utility.set_location('p_last_cess_action = ' || p_last_cess_action,20);
493: hr_utility.set_location('p_prev_src_id = ' || p_prev_src_id,20);
494: hr_utility.set_location('g_paye_ref = ' || g_paye_ref,20);
495:
496: hr_utility.set_location('before PPSN cursor l_ppsn_override = ' || l_ppsn_override,20);
497: OPEN csr_ppsn_override(p_assignment_id);
490: hr_utility.set_location('p_supp_pymt_date = ' || p_supp_pymt_date,20);
491: hr_utility.set_location('p_deceased_flag = ' || p_deceased_flag,20);
492: hr_utility.set_location('p_last_cess_action = ' || p_last_cess_action,20);
493: hr_utility.set_location('p_prev_src_id = ' || p_prev_src_id,20);
494: hr_utility.set_location('g_paye_ref = ' || g_paye_ref,20);
495:
496: hr_utility.set_location('before PPSN cursor l_ppsn_override = ' || l_ppsn_override,20);
497: OPEN csr_ppsn_override(p_assignment_id);
498: FETCH csr_ppsn_override INTO l_ppsn_override;
492: hr_utility.set_location('p_last_cess_action = ' || p_last_cess_action,20);
493: hr_utility.set_location('p_prev_src_id = ' || p_prev_src_id,20);
494: hr_utility.set_location('g_paye_ref = ' || g_paye_ref,20);
495:
496: hr_utility.set_location('before PPSN cursor l_ppsn_override = ' || l_ppsn_override,20);
497: OPEN csr_ppsn_override(p_assignment_id);
498: FETCH csr_ppsn_override INTO l_ppsn_override;
499: IF csr_ppsn_override%NOTFOUND THEN
500: l_ppsn_override:=null;
500: l_ppsn_override:=null;
501: END IF;
502:
503: CLOSE csr_ppsn_override;
504: hr_utility.set_location('after PPSN cursor l_ppsn_override = ' || l_ppsn_override,20);
505:
506:
507: l_supp_flg := p_supp_flag;
508: l_supp_pymt_date := p_supp_pymt_date;
506:
507: l_supp_flg := p_supp_flag;
508: l_supp_pymt_date := p_supp_pymt_date;
509: l_termination_date := p_termination_date;
510: hr_utility.set_location('supplementary flag = '||l_supp_flg,20);
511: hr_utility.set_location('supplementary date = '||l_supp_pymt_date,20);
512:
513: IF l_ppsn_override is null THEN
514: /* OPEN cur_defined_balance_id('IE Gross Income','_PER_PAYE_REF_YTD'); */
507: l_supp_flg := p_supp_flag;
508: l_supp_pymt_date := p_supp_pymt_date;
509: l_termination_date := p_termination_date;
510: hr_utility.set_location('supplementary flag = '||l_supp_flg,20);
511: hr_utility.set_location('supplementary date = '||l_supp_pymt_date,20);
512:
513: IF l_ppsn_override is null THEN
514: /* OPEN cur_defined_balance_id('IE Gross Income','_PER_PAYE_REF_YTD'); */
515: OPEN cur_defined_balance_id('IE USCable Pay','_PER_PAYE_REF_YTD'); -- bug 11908300
513: IF l_ppsn_override is null THEN
514: /* OPEN cur_defined_balance_id('IE Gross Income','_PER_PAYE_REF_YTD'); */
515: OPEN cur_defined_balance_id('IE USCable Pay','_PER_PAYE_REF_YTD'); -- bug 11908300
516:
517: hr_utility.set_location(' balance type _PER_PAYE_REF_YTD' ,30);
518:
519: ELSE
520: /* OPEN cur_defined_balance_id('IE Gross Income','_PER_PAYE_REF_PPSN_YTD'); */
521: OPEN cur_defined_balance_id('IE USCable Pay','_PER_PAYE_REF_PPSN_YTD'); -- bug 11908300
518:
519: ELSE
520: /* OPEN cur_defined_balance_id('IE Gross Income','_PER_PAYE_REF_PPSN_YTD'); */
521: OPEN cur_defined_balance_id('IE USCable Pay','_PER_PAYE_REF_PPSN_YTD'); -- bug 11908300
522: hr_utility.set_location(' balance type _PER_PAYE_REF_PPSN_YTD' ,30);
523: END IF;
524:
525: FETCH cur_defined_balance_id INTO l_defined_balance_id;
526: CLOSE cur_defined_balance_id;
524:
525: FETCH cur_defined_balance_id INTO l_defined_balance_id;
526: CLOSE cur_defined_balance_id;
527:
528: hr_utility.set_location(' l_defined_balance_id' || l_defined_balance_id,30);
529: hr_utility.set_location(' l_balance_value' || l_balance_value,30);
530: IF (p_child_run_ass_act_id IS NOT NULL) THEN /* 8615992 */
531: l_balance_value := PAY_BALANCE_PKG.GET_VALUE(l_defined_balance_id,
532: p_child_run_ass_act_id,
525: FETCH cur_defined_balance_id INTO l_defined_balance_id;
526: CLOSE cur_defined_balance_id;
527:
528: hr_utility.set_location(' l_defined_balance_id' || l_defined_balance_id,30);
529: hr_utility.set_location(' l_balance_value' || l_balance_value,30);
530: IF (p_child_run_ass_act_id IS NOT NULL) THEN /* 8615992 */
531: l_balance_value := PAY_BALANCE_PKG.GET_VALUE(l_defined_balance_id,
532: p_child_run_ass_act_id,
533: g_paye_ref,
538: null);
539: ELSE
540: l_balance_value:=0;
541: END IF;
542: hr_utility.set_location(' l_balance_value' || l_balance_value,30);
543: IF (nvl(p_supp_flag,'N') = 'N') AND (p_last_cess_action IS NOT NULL) THEN
544: l_cess_last_bal_value := PAY_BALANCE_PKG.GET_VALUE(l_defined_balance_id,
545: p_prev_src_id,
546: g_paye_ref,
548: null,
549: null,
550: null,
551: null);
552: hr_utility.set_location(' l_cess_last_bal_value' || l_cess_last_bal_value,30);
553: l_balance_value:=l_balance_value - l_cess_last_bal_value;
554: END IF;
555:
556: IF p_supp_flag ='Y' THEN
558: OPEN get_last_source_id;
559: FETCH get_last_source_id into l_prev_source_id;
560: CLOSE get_last_source_id;
561:
562: hr_utility.set_location(' l_prev_source_id' || l_prev_source_id,30);
563: IF l_prev_source_id IS NOT NULL THEN /* 9337590 */
564: l_balance_value1 := PAY_BALANCE_PKG.GET_VALUE(l_defined_balance_id,
565: l_prev_source_id,
566: g_paye_ref,
568: null,
569: null,
570: null,
571: null);
572: hr_utility.set_location(' l_balance_value1' || l_balance_value1,30);
573: END IF;
574: ELSE
575: l_balance_value1:=0;
576: hr_utility.set_location(' l_balance_value1' || l_balance_value1,30);
572: hr_utility.set_location(' l_balance_value1' || l_balance_value1,30);
573: END IF;
574: ELSE
575: l_balance_value1:=0;
576: hr_utility.set_location(' l_balance_value1' || l_balance_value1,30);
577: END IF;
578: l_gross_pay := l_balance_value - l_balance_value1;
579:
580: hr_utility.set_location(' l_gross_pay' || l_gross_pay,30);
576: hr_utility.set_location(' l_balance_value1' || l_balance_value1,30);
577: END IF;
578: l_gross_pay := l_balance_value - l_balance_value1;
579:
580: hr_utility.set_location(' l_gross_pay' || l_gross_pay,30);
581:
582: l_defined_balance_id:=null;
583: l_balance_value:=0;
584: l_balance_value1:=0;
585:
586:
587: IF l_ppsn_override is null THEN
588: OPEN cur_defined_balance_id('IE Gross Income Adjustment','_PER_PAYE_REF_YTD');
589: hr_utility.set_location(' balance type _PER_PAYE_REF_YTD' ,30);
590:
591: ELSE
592: OPEN cur_defined_balance_id('IE Gross Income Adjustment','_PER_PAYE_REF_PPSN_YTD');
593: hr_utility.set_location(' balance type _PER_PAYE_REF_PPSN_YTD' ,30);
589: hr_utility.set_location(' balance type _PER_PAYE_REF_YTD' ,30);
590:
591: ELSE
592: OPEN cur_defined_balance_id('IE Gross Income Adjustment','_PER_PAYE_REF_PPSN_YTD');
593: hr_utility.set_location(' balance type _PER_PAYE_REF_PPSN_YTD' ,30);
594: END IF;
595:
596:
597: FETCH cur_defined_balance_id INTO l_defined_balance_id;
596:
597: FETCH cur_defined_balance_id INTO l_defined_balance_id;
598: CLOSE cur_defined_balance_id;
599:
600: hr_utility.set_location(' l_balance_value' || l_balance_value,40);
601: IF (p_child_run_ass_act_id IS NOT NULL) THEN
602: l_balance_value := PAY_BALANCE_PKG.GET_VALUE(l_defined_balance_id,
603: p_child_run_ass_act_id,
604: g_paye_ref,
609: null);
610: ELSE
611: l_balance_value:=0;
612: END IF;
613: hr_utility.set_location(' l_balance_value' || l_balance_value,40);
614: IF (nvl(p_supp_flag,'N') = 'N') AND (p_last_cess_action IS NOT NULL) THEN
615: l_cess_last_bal_value := PAY_BALANCE_PKG.GET_VALUE(l_defined_balance_id,
616: p_prev_src_id,
617: g_paye_ref,
619: null,
620: null,
621: null,
622: null);
623: hr_utility.set_location(' l_cess_last_bal_value' || l_cess_last_bal_value,40);
624: l_balance_value:=l_balance_value - l_cess_last_bal_value;
625: END IF;
626:
627: IF p_supp_flag ='Y' THEN
628:
629: OPEN get_last_source_id;
630: FETCH get_last_source_id into l_prev_source_id;
631: CLOSE get_last_source_id;
632: hr_utility.set_location(' l_prev_source_id' || l_prev_source_id,40);
633: IF l_prev_source_id IS NOT NULL THEN /* 9337590 */
634: l_balance_value1 := PAY_BALANCE_PKG.GET_VALUE(l_defined_balance_id,
635: l_prev_source_id,
636: g_paye_ref,
638: null,
639: null,
640: null,
641: null);
642: hr_utility.set_location(' l_balance_value1' || l_balance_value1,40);
643: END IF;
644: ELSE
645: l_balance_value1:=0;
646: hr_utility.set_location(' l_balance_value1' || l_balance_value1,40);
642: hr_utility.set_location(' l_balance_value1' || l_balance_value1,40);
643: END IF;
644: ELSE
645: l_balance_value1:=0;
646: hr_utility.set_location(' l_balance_value1' || l_balance_value1,40);
647: END IF;
648: l_gross_pay_adjust := l_balance_value - l_balance_value1;
649:
650: hr_utility.set_location(' l_gross_pay_adjust' || l_gross_pay_adjust,30);
646: hr_utility.set_location(' l_balance_value1' || l_balance_value1,40);
647: END IF;
648: l_gross_pay_adjust := l_balance_value - l_balance_value1;
649:
650: hr_utility.set_location(' l_gross_pay_adjust' || l_gross_pay_adjust,30);
651: l_defined_balance_id:=null;
652: l_balance_value:=0;
653: l_balance_value1:=0;
654:
656: l_defined_balance_id:=null;
657:
658: IF l_ppsn_override is null THEN
659: OPEN cur_defined_balance_id('IE BIK Taxable and PRSIable Pay','_PER_PAYE_REF_YTD');
660: hr_utility.set_location(' balance type _PER_PAYE_REF_YTD' ,30);
661:
662: ELSE
663: OPEN cur_defined_balance_id('IE BIK Taxable and PRSIable Pay','_PER_PAYE_REF_PPSN_YTD');
664: hr_utility.set_location(' balance type _PER_PAYE_REF_PPSN_YTD' ,30);
660: hr_utility.set_location(' balance type _PER_PAYE_REF_YTD' ,30);
661:
662: ELSE
663: OPEN cur_defined_balance_id('IE BIK Taxable and PRSIable Pay','_PER_PAYE_REF_PPSN_YTD');
664: hr_utility.set_location(' balance type _PER_PAYE_REF_PPSN_YTD' ,30);
665: END IF;
666:
667:
668: FETCH cur_defined_balance_id INTO l_defined_balance_id;
666:
667:
668: FETCH cur_defined_balance_id INTO l_defined_balance_id;
669: CLOSE cur_defined_balance_id;
670: hr_utility.set_location(' l_balance_value' || l_balance_value,50);
671: IF (p_child_run_ass_act_id IS NOT NULL) THEN
672: l_balance_value := PAY_BALANCE_PKG.GET_VALUE(l_defined_balance_id,
673: p_child_run_ass_act_id,
674: g_paye_ref,
679: null);
680: ELSE
681: l_balance_value:=0;
682: END IF;
683: hr_utility.set_location(' l_balance_value' || l_balance_value,50);
684: IF (nvl(p_supp_flag,'N') = 'N') AND (p_last_cess_action IS NOT NULL) THEN
685: l_cess_last_bal_value := PAY_BALANCE_PKG.GET_VALUE(l_defined_balance_id,
686: p_prev_src_id,
687: g_paye_ref,
689: null,
690: null,
691: null,
692: null);
693: hr_utility.set_location(' l_cess_last_bal_value' || l_cess_last_bal_value,50);
694: l_balance_value:=l_balance_value - l_cess_last_bal_value;
695: END IF;
696:
697: IF p_supp_flag ='Y' THEN
698:
699: OPEN get_last_source_id;
700: FETCH get_last_source_id into l_prev_source_id;
701: CLOSE get_last_source_id;
702: hr_utility.set_location(' l_prev_source_id' || l_prev_source_id,50);
703: IF l_prev_source_id IS NOT NULL THEN /* 9337590 */
704: l_balance_value1 := PAY_BALANCE_PKG.GET_VALUE(l_defined_balance_id,
705: l_prev_source_id,
706: g_paye_ref,
708: null,
709: null,
710: null,
711: null);
712: hr_utility.set_location(' l_balance_value1' || l_balance_value1,50);
713: END IF;
714: ELSE
715: l_balance_value1:=0;
716: hr_utility.set_location(' l_balance_value1' || l_balance_value1,50);
712: hr_utility.set_location(' l_balance_value1' || l_balance_value1,50);
713: END IF;
714: ELSE
715: l_balance_value1:=0;
716: hr_utility.set_location(' l_balance_value1' || l_balance_value1,50);
717: END IF;
718: l_bik_prsi_taxable := l_balance_value - l_balance_value1;
719:
720: hr_utility.set_location(' l_bik_prsi_taxable' || l_bik_prsi_taxable,50);
716: hr_utility.set_location(' l_balance_value1' || l_balance_value1,50);
717: END IF;
718: l_bik_prsi_taxable := l_balance_value - l_balance_value1;
719:
720: hr_utility.set_location(' l_bik_prsi_taxable' || l_bik_prsi_taxable,50);
721:
722: l_defined_balance_id:=null;
723: l_balance_value:=0;
724: l_balance_value1:=0;
727:
728: IF l_ppsn_override is null THEN
729: OPEN cur_defined_balance_id('IE Income Tax Levy','_PER_PAYE_REF_YTD');
730:
731: hr_utility.set_location(' balance type _PER_PAYE_REF_YTD' ,30);
732:
733: ELSE
734: OPEN cur_defined_balance_id('IE Income Tax Levy','_PER_PAYE_REF_PPSN_YTD');
735: hr_utility.set_location(' balance type _PER_PAYE_REF_PPSN_YTD' ,30);
731: hr_utility.set_location(' balance type _PER_PAYE_REF_YTD' ,30);
732:
733: ELSE
734: OPEN cur_defined_balance_id('IE Income Tax Levy','_PER_PAYE_REF_PPSN_YTD');
735: hr_utility.set_location(' balance type _PER_PAYE_REF_PPSN_YTD' ,30);
736: END IF;
737:
738:
739: FETCH cur_defined_balance_id INTO l_defined_balance_id;
738:
739: FETCH cur_defined_balance_id INTO l_defined_balance_id;
740: CLOSE cur_defined_balance_id;
741:
742: hr_utility.set_location(' l_balance_value' || l_balance_value,60);
743: IF (p_child_run_ass_act_id IS NOT NULL) THEN
744: l_balance_value := PAY_BALANCE_PKG.GET_VALUE(l_defined_balance_id,
745: p_child_run_ass_act_id,
746: g_paye_ref,
751: null);
752: ELSE
753: l_balance_value:=0;
754: END IF;
755: hr_utility.set_location(' l_balance_value' || l_balance_value,60);
756: IF (nvl(p_supp_flag,'N') = 'N') AND (p_last_cess_action IS NOT NULL) THEN
757: l_cess_last_bal_value := PAY_BALANCE_PKG.GET_VALUE(l_defined_balance_id,
758: p_prev_src_id,
759: g_paye_ref,
761: null,
762: null,
763: null,
764: null);
765: hr_utility.set_location(' l_cess_last_bal_value' || l_cess_last_bal_value,60);
766: l_balance_value:=l_balance_value - l_cess_last_bal_value;
767: END IF;
768:
769: IF p_supp_flag ='Y' THEN
770:
771: OPEN get_last_source_id;
772: FETCH get_last_source_id into l_prev_source_id;
773: CLOSE get_last_source_id;
774: hr_utility.set_location(' l_prev_source_id' || l_prev_source_id,60);
775: IF l_prev_source_id IS NOT NULL THEN /* 9337590 */
776: l_balance_value1 := PAY_BALANCE_PKG.GET_VALUE(l_defined_balance_id,
777: l_prev_source_id,
778: g_paye_ref,
780: null,
781: null,
782: null,
783: null);
784: hr_utility.set_location(' l_balance_value1' || l_balance_value1,60);
785: END IF;
786: ELSE
787: l_balance_value1:=0;
788: hr_utility.set_location(' l_balance_value1' || l_balance_value1,60);
784: hr_utility.set_location(' l_balance_value1' || l_balance_value1,60);
785: END IF;
786: ELSE
787: l_balance_value1:=0;
788: hr_utility.set_location(' l_balance_value1' || l_balance_value1,60);
789: END IF;
790: l_income_levy := l_balance_value - l_balance_value1;
791: hr_utility.set_location(' l_income_levy' || l_income_levy,30);
792: /* ---------------------------------------------------------------------------------------- */
787: l_balance_value1:=0;
788: hr_utility.set_location(' l_balance_value1' || l_balance_value1,60);
789: END IF;
790: l_income_levy := l_balance_value - l_balance_value1;
791: hr_utility.set_location(' l_income_levy' || l_income_levy,30);
792: /* ---------------------------------------------------------------------------------------- */
793: /* 8615992 fetch the till april balance and make the split accordingly */
794:
795: OPEN get_asg_action_eff_date(p_child_run_ass_act_id);
794:
795: OPEN get_asg_action_eff_date(p_child_run_ass_act_id);
796: FETCH get_asg_action_eff_date INTO l_child_action_eff_date;
797: CLOSE get_asg_action_eff_date;
798: hr_utility.set_location(' l_child_action_eff_date' || l_child_action_eff_date,60);
799: IF l_child_action_eff_date is not null and (to_char(l_child_action_eff_date,'yyyy') = '2009')
800: THEN
801: OPEN cur_assignment_action_apr_09(l_ppsn_override);
802: FETCH cur_assignment_action_apr_09 into l_assignment_action_apr_09;
801: OPEN cur_assignment_action_apr_09(l_ppsn_override);
802: FETCH cur_assignment_action_apr_09 into l_assignment_action_apr_09;
803: CLOSE cur_assignment_action_apr_09;
804:
805: hr_utility.set_location(' l_assignment_action_apr_09 '||l_assignment_action_apr_09 ,30);
806:
807: /* gross pay */
808: IF l_ppsn_override is null THEN
809: OPEN cur_defined_balance_id('IE Gross Income','_PER_PAYE_REF_YTD');
806:
807: /* gross pay */
808: IF l_ppsn_override is null THEN
809: OPEN cur_defined_balance_id('IE Gross Income','_PER_PAYE_REF_YTD');
810: hr_utility.set_location(' balance type _PER_PAYE_REF_YTD' ,30);
811: ELSE
812: OPEN cur_defined_balance_id('IE Gross Income','_PER_PAYE_REF_PPSN_YTD');
813: hr_utility.set_location(' balance type _PER_PAYE_REF_PPSN_YTD' ,30);
814: END IF;
809: OPEN cur_defined_balance_id('IE Gross Income','_PER_PAYE_REF_YTD');
810: hr_utility.set_location(' balance type _PER_PAYE_REF_YTD' ,30);
811: ELSE
812: OPEN cur_defined_balance_id('IE Gross Income','_PER_PAYE_REF_PPSN_YTD');
813: hr_utility.set_location(' balance type _PER_PAYE_REF_PPSN_YTD' ,30);
814: END IF;
815:
816: FETCH cur_defined_balance_id INTO l_defined_balance_id;
817: CLOSE cur_defined_balance_id;
815:
816: FETCH cur_defined_balance_id INTO l_defined_balance_id;
817: CLOSE cur_defined_balance_id;
818:
819: hr_utility.set_location(' l_defined_balance_id' || l_defined_balance_id,30);
820: hr_utility.set_location(' l_balance_value_apr_09' || l_balance_value_apr_09,30);
821: IF l_assignment_action_apr_09 is not null THEN
822: l_balance_value_apr_09 := PAY_BALANCE_PKG.GET_VALUE(l_defined_balance_id,
823: l_assignment_action_apr_09,
816: FETCH cur_defined_balance_id INTO l_defined_balance_id;
817: CLOSE cur_defined_balance_id;
818:
819: hr_utility.set_location(' l_defined_balance_id' || l_defined_balance_id,30);
820: hr_utility.set_location(' l_balance_value_apr_09' || l_balance_value_apr_09,30);
821: IF l_assignment_action_apr_09 is not null THEN
822: l_balance_value_apr_09 := PAY_BALANCE_PKG.GET_VALUE(l_defined_balance_id,
823: l_assignment_action_apr_09,
824: g_paye_ref,
827: null,
828: null,
829: null);
830: END IF;
831: hr_utility.set_location(' l_balance_value_apr_09' || l_balance_value_apr_09,30);
832: IF (nvl(p_supp_flag,'N') = 'N') AND (p_last_cess_action IS NOT NULL) and l_assignment_action_apr_09 is not NULL THEN
833: OPEN get_asg_action_eff_date(p_prev_src_id);
834: FETCH get_asg_action_eff_date INTO l_action_effective_date;
835: CLOSE get_asg_action_eff_date;
832: IF (nvl(p_supp_flag,'N') = 'N') AND (p_last_cess_action IS NOT NULL) and l_assignment_action_apr_09 is not NULL THEN
833: OPEN get_asg_action_eff_date(p_prev_src_id);
834: FETCH get_asg_action_eff_date INTO l_action_effective_date;
835: CLOSE get_asg_action_eff_date;
836: hr_utility.set_location('l_action_effective_date = ' || l_action_effective_date,40);
837: IF l_action_effective_date is not null and l_action_effective_date <= to_date('30/04/2009','dd/mm/yyyy') THEN
838: l_cess_last_bal_value_apr_09 := PAY_BALANCE_PKG.GET_VALUE(l_defined_balance_id,
839: p_prev_src_id,
840: g_paye_ref,
842: null,
843: null,
844: null,
845: null);
846: hr_utility.set_location(' l_cess_last_bal_value_apr_09' || l_cess_last_bal_value_apr_09,30);
847: ELSE
848: l_cess_last_bal_value_apr_09 :=0;
849: END IF;
850: l_balance_value_apr_09:=l_balance_value_apr_09 - l_cess_last_bal_value_apr_09;
855: OPEN get_last_source_id_apr_09;
856: FETCH get_last_source_id_apr_09 into l_prev_source_id_apr_09,l_payroll_effective_date;
857: CLOSE get_last_source_id_apr_09;
858:
859: hr_utility.set_location(' l_prev_source_id_apr_09' || l_prev_source_id_apr_09,40);
860: hr_utility.set_location('l_payroll_effective_date = ' || l_payroll_effective_date,40);
861: IF l_prev_source_id_apr_09 is not null and l_assignment_action_apr_09 is not null and l_payroll_effective_date <= to_date('30/04/2009','dd/mm/yyyy') THEN
862: l_balance_value1_apr_09 := PAY_BALANCE_PKG.GET_VALUE(l_defined_balance_id,
863: l_prev_source_id_apr_09,
856: FETCH get_last_source_id_apr_09 into l_prev_source_id_apr_09,l_payroll_effective_date;
857: CLOSE get_last_source_id_apr_09;
858:
859: hr_utility.set_location(' l_prev_source_id_apr_09' || l_prev_source_id_apr_09,40);
860: hr_utility.set_location('l_payroll_effective_date = ' || l_payroll_effective_date,40);
861: IF l_prev_source_id_apr_09 is not null and l_assignment_action_apr_09 is not null and l_payroll_effective_date <= to_date('30/04/2009','dd/mm/yyyy') THEN
862: l_balance_value1_apr_09 := PAY_BALANCE_PKG.GET_VALUE(l_defined_balance_id,
863: l_prev_source_id_apr_09,
864: g_paye_ref,
867: null,
868: null,
869: null);
870:
871: hr_utility.set_location(' l_balance_value1_apr_09' || l_balance_value1_apr_09,40);
872:
873: ELSE
874: l_balance_value_apr_09:=0;
875: l_balance_value1_apr_09:=0;
876: END IF;
877:
878: ELSE
879: l_balance_value1_apr_09:=0;
880: hr_utility.set_location(' l_balance_value1_apr_09' || l_balance_value1_apr_09,40);
881: END IF;
882:
883: hr_utility.set_location(' l_balance_value_apr_09' || l_balance_value_apr_09,100);
884: hr_utility.set_location(' l_balance_value1_apr_09' || l_balance_value1_apr_09,100);
879: l_balance_value1_apr_09:=0;
880: hr_utility.set_location(' l_balance_value1_apr_09' || l_balance_value1_apr_09,40);
881: END IF;
882:
883: hr_utility.set_location(' l_balance_value_apr_09' || l_balance_value_apr_09,100);
884: hr_utility.set_location(' l_balance_value1_apr_09' || l_balance_value1_apr_09,100);
885:
886: l_gross_pay_apr_09 := nvl(l_balance_value_apr_09,0) - nvl(l_balance_value1_apr_09,0);
887: hr_utility.set_location(' l_gross_pay' || l_gross_pay,40);
880: hr_utility.set_location(' l_balance_value1_apr_09' || l_balance_value1_apr_09,40);
881: END IF;
882:
883: hr_utility.set_location(' l_balance_value_apr_09' || l_balance_value_apr_09,100);
884: hr_utility.set_location(' l_balance_value1_apr_09' || l_balance_value1_apr_09,100);
885:
886: l_gross_pay_apr_09 := nvl(l_balance_value_apr_09,0) - nvl(l_balance_value1_apr_09,0);
887: hr_utility.set_location(' l_gross_pay' || l_gross_pay,40);
888: hr_utility.set_location(' l_gross_pay_apr_09' || l_gross_pay_apr_09,40);
883: hr_utility.set_location(' l_balance_value_apr_09' || l_balance_value_apr_09,100);
884: hr_utility.set_location(' l_balance_value1_apr_09' || l_balance_value1_apr_09,100);
885:
886: l_gross_pay_apr_09 := nvl(l_balance_value_apr_09,0) - nvl(l_balance_value1_apr_09,0);
887: hr_utility.set_location(' l_gross_pay' || l_gross_pay,40);
888: hr_utility.set_location(' l_gross_pay_apr_09' || l_gross_pay_apr_09,40);
889: l_gross_pay := l_gross_pay - l_gross_pay_apr_09;
890: hr_utility.set_location(' l_gross_pay from may 2009 ' || l_gross_pay,40);
891: l_defined_balance_id:=null;
884: hr_utility.set_location(' l_balance_value1_apr_09' || l_balance_value1_apr_09,100);
885:
886: l_gross_pay_apr_09 := nvl(l_balance_value_apr_09,0) - nvl(l_balance_value1_apr_09,0);
887: hr_utility.set_location(' l_gross_pay' || l_gross_pay,40);
888: hr_utility.set_location(' l_gross_pay_apr_09' || l_gross_pay_apr_09,40);
889: l_gross_pay := l_gross_pay - l_gross_pay_apr_09;
890: hr_utility.set_location(' l_gross_pay from may 2009 ' || l_gross_pay,40);
891: l_defined_balance_id:=null;
892: l_balance_value_apr_09:=0;
886: l_gross_pay_apr_09 := nvl(l_balance_value_apr_09,0) - nvl(l_balance_value1_apr_09,0);
887: hr_utility.set_location(' l_gross_pay' || l_gross_pay,40);
888: hr_utility.set_location(' l_gross_pay_apr_09' || l_gross_pay_apr_09,40);
889: l_gross_pay := l_gross_pay - l_gross_pay_apr_09;
890: hr_utility.set_location(' l_gross_pay from may 2009 ' || l_gross_pay,40);
891: l_defined_balance_id:=null;
892: l_balance_value_apr_09:=0;
893: l_balance_value1_apr_09:=0;
894:
895:
896: /* gross pay adjust */
897: IF l_ppsn_override is null THEN
898: OPEN cur_defined_balance_id('IE Gross Income Adjustment','_PER_PAYE_REF_YTD');
899: hr_utility.set_location(' balance type _PER_PAYE_REF_YTD' ,30);
900: ELSE
901: OPEN cur_defined_balance_id('IE Gross Income Adjustment','_PER_PAYE_REF_PPSN_YTD');
902: hr_utility.set_location(' balance type _PER_PAYE_REF_PPSN_YTD' ,30);
903: END IF;
898: OPEN cur_defined_balance_id('IE Gross Income Adjustment','_PER_PAYE_REF_YTD');
899: hr_utility.set_location(' balance type _PER_PAYE_REF_YTD' ,30);
900: ELSE
901: OPEN cur_defined_balance_id('IE Gross Income Adjustment','_PER_PAYE_REF_PPSN_YTD');
902: hr_utility.set_location(' balance type _PER_PAYE_REF_PPSN_YTD' ,30);
903: END IF;
904:
905: FETCH cur_defined_balance_id INTO l_defined_balance_id;
906: CLOSE cur_defined_balance_id;
904:
905: FETCH cur_defined_balance_id INTO l_defined_balance_id;
906: CLOSE cur_defined_balance_id;
907:
908: hr_utility.set_location(' l_defined_balance_id' || l_defined_balance_id,30);
909: hr_utility.set_location(' l_balance_value_apr_09' || l_balance_value_apr_09,30);
910: IF l_assignment_action_apr_09 is not null THEN
911: l_balance_value_apr_09 := PAY_BALANCE_PKG.GET_VALUE(l_defined_balance_id,
912: l_assignment_action_apr_09,
905: FETCH cur_defined_balance_id INTO l_defined_balance_id;
906: CLOSE cur_defined_balance_id;
907:
908: hr_utility.set_location(' l_defined_balance_id' || l_defined_balance_id,30);
909: hr_utility.set_location(' l_balance_value_apr_09' || l_balance_value_apr_09,30);
910: IF l_assignment_action_apr_09 is not null THEN
911: l_balance_value_apr_09 := PAY_BALANCE_PKG.GET_VALUE(l_defined_balance_id,
912: l_assignment_action_apr_09,
913: g_paye_ref,
916: null,
917: null,
918: null);
919: END IF;
920: hr_utility.set_location(' l_balance_value_apr_09' || l_balance_value_apr_09,30);
921: IF (nvl(p_supp_flag,'N') = 'N') AND (p_last_cess_action IS NOT NULL) and l_assignment_action_apr_09 is not NULL THEN
922: OPEN get_asg_action_eff_date(p_prev_src_id);
923: FETCH get_asg_action_eff_date INTO l_action_effective_date;
924: CLOSE get_asg_action_eff_date;
921: IF (nvl(p_supp_flag,'N') = 'N') AND (p_last_cess_action IS NOT NULL) and l_assignment_action_apr_09 is not NULL THEN
922: OPEN get_asg_action_eff_date(p_prev_src_id);
923: FETCH get_asg_action_eff_date INTO l_action_effective_date;
924: CLOSE get_asg_action_eff_date;
925: hr_utility.set_location('l_action_effective_date = ' || l_action_effective_date,40);
926: IF l_action_effective_date is not null and l_action_effective_date <= to_date('30/04/2009','dd/mm/yyyy') THEN
927: l_cess_last_bal_value_apr_09 := PAY_BALANCE_PKG.GET_VALUE(l_defined_balance_id,
928: p_prev_src_id,
929: g_paye_ref,
931: null,
932: null,
933: null,
934: null);
935: hr_utility.set_location(' l_cess_last_bal_value_apr_09' || l_cess_last_bal_value_apr_09,30);
936: ELSE
937: l_cess_last_bal_value_apr_09 :=0;
938: END IF;
939: l_balance_value_apr_09:=l_balance_value_apr_09 - l_cess_last_bal_value_apr_09;
943: OPEN get_last_source_id_apr_09;
944: FETCH get_last_source_id_apr_09 into l_prev_source_id_apr_09,l_payroll_effective_date;
945: CLOSE get_last_source_id_apr_09;
946:
947: hr_utility.set_location(' l_prev_source_id_apr_09' || l_prev_source_id_apr_09,40);
948: hr_utility.set_location('l_payroll_effective_date = ' || l_payroll_effective_date,40);
949: IF l_prev_source_id_apr_09 is not null and l_assignment_action_apr_09 is not null and l_payroll_effective_date <= to_date('30/04/2009','dd/mm/yyyy') THEN
950: l_balance_value1_apr_09 := PAY_BALANCE_PKG.GET_VALUE(l_defined_balance_id,
951: l_prev_source_id_apr_09,
944: FETCH get_last_source_id_apr_09 into l_prev_source_id_apr_09,l_payroll_effective_date;
945: CLOSE get_last_source_id_apr_09;
946:
947: hr_utility.set_location(' l_prev_source_id_apr_09' || l_prev_source_id_apr_09,40);
948: hr_utility.set_location('l_payroll_effective_date = ' || l_payroll_effective_date,40);
949: IF l_prev_source_id_apr_09 is not null and l_assignment_action_apr_09 is not null and l_payroll_effective_date <= to_date('30/04/2009','dd/mm/yyyy') THEN
950: l_balance_value1_apr_09 := PAY_BALANCE_PKG.GET_VALUE(l_defined_balance_id,
951: l_prev_source_id_apr_09,
952: g_paye_ref,
955: null,
956: null,
957: null);
958:
959: hr_utility.set_location(' l_balance_value1_apr_09' || l_balance_value1_apr_09,40);
960:
961: ELSE
962: l_balance_value_apr_09:=0;
963: l_balance_value1_apr_09:=0;
964: END IF;
965:
966: ELSE
967: l_balance_value1_apr_09:=0;
968: hr_utility.set_location(' l_balance_value1_apr_09' || l_balance_value1_apr_09,40);
969: END IF;
970:
971: hr_utility.set_location(' l_balance_value_apr_09' || l_balance_value_apr_09,100);
972: hr_utility.set_location(' l_balance_value1_apr_09' || l_balance_value1_apr_09,100);
967: l_balance_value1_apr_09:=0;
968: hr_utility.set_location(' l_balance_value1_apr_09' || l_balance_value1_apr_09,40);
969: END IF;
970:
971: hr_utility.set_location(' l_balance_value_apr_09' || l_balance_value_apr_09,100);
972: hr_utility.set_location(' l_balance_value1_apr_09' || l_balance_value1_apr_09,100);
973:
974: l_gross_pay_adjust_apr_09 := nvl(l_balance_value_apr_09,0) - nvl(l_balance_value1_apr_09,0);
975: hr_utility.set_location(' l_gross_pay_adjust' || l_gross_pay_adjust,40);
968: hr_utility.set_location(' l_balance_value1_apr_09' || l_balance_value1_apr_09,40);
969: END IF;
970:
971: hr_utility.set_location(' l_balance_value_apr_09' || l_balance_value_apr_09,100);
972: hr_utility.set_location(' l_balance_value1_apr_09' || l_balance_value1_apr_09,100);
973:
974: l_gross_pay_adjust_apr_09 := nvl(l_balance_value_apr_09,0) - nvl(l_balance_value1_apr_09,0);
975: hr_utility.set_location(' l_gross_pay_adjust' || l_gross_pay_adjust,40);
976: hr_utility.set_location(' l_gross_pay_adjust_apr_09' || l_gross_pay_adjust_apr_09,40);
971: hr_utility.set_location(' l_balance_value_apr_09' || l_balance_value_apr_09,100);
972: hr_utility.set_location(' l_balance_value1_apr_09' || l_balance_value1_apr_09,100);
973:
974: l_gross_pay_adjust_apr_09 := nvl(l_balance_value_apr_09,0) - nvl(l_balance_value1_apr_09,0);
975: hr_utility.set_location(' l_gross_pay_adjust' || l_gross_pay_adjust,40);
976: hr_utility.set_location(' l_gross_pay_adjust_apr_09' || l_gross_pay_adjust_apr_09,40);
977: l_gross_pay_adjust := l_gross_pay_adjust - l_gross_pay_adjust_apr_09;
978: hr_utility.set_location(' l_gross_pay_adjust from may 2009 ' || l_gross_pay_adjust,40);
979: l_defined_balance_id:=null;
972: hr_utility.set_location(' l_balance_value1_apr_09' || l_balance_value1_apr_09,100);
973:
974: l_gross_pay_adjust_apr_09 := nvl(l_balance_value_apr_09,0) - nvl(l_balance_value1_apr_09,0);
975: hr_utility.set_location(' l_gross_pay_adjust' || l_gross_pay_adjust,40);
976: hr_utility.set_location(' l_gross_pay_adjust_apr_09' || l_gross_pay_adjust_apr_09,40);
977: l_gross_pay_adjust := l_gross_pay_adjust - l_gross_pay_adjust_apr_09;
978: hr_utility.set_location(' l_gross_pay_adjust from may 2009 ' || l_gross_pay_adjust,40);
979: l_defined_balance_id:=null;
980: l_balance_value_apr_09:=0;
974: l_gross_pay_adjust_apr_09 := nvl(l_balance_value_apr_09,0) - nvl(l_balance_value1_apr_09,0);
975: hr_utility.set_location(' l_gross_pay_adjust' || l_gross_pay_adjust,40);
976: hr_utility.set_location(' l_gross_pay_adjust_apr_09' || l_gross_pay_adjust_apr_09,40);
977: l_gross_pay_adjust := l_gross_pay_adjust - l_gross_pay_adjust_apr_09;
978: hr_utility.set_location(' l_gross_pay_adjust from may 2009 ' || l_gross_pay_adjust,40);
979: l_defined_balance_id:=null;
980: l_balance_value_apr_09:=0;
981: l_balance_value1_apr_09:=0;
982:
983:
984: /* IE BIK PRSIable and Taxanel pay */
985: IF l_ppsn_override is null THEN
986: OPEN cur_defined_balance_id('IE BIK Taxable and PRSIable Pay','_PER_PAYE_REF_YTD');
987: hr_utility.set_location(' balance type _PER_PAYE_REF_YTD' ,30);
988: ELSE
989: OPEN cur_defined_balance_id('IE BIK Taxable and PRSIable Pay','_PER_PAYE_REF_PPSN_YTD');
990: hr_utility.set_location(' balance type _PER_PAYE_REF_PPSN_YTD' ,30);
991: END IF;
986: OPEN cur_defined_balance_id('IE BIK Taxable and PRSIable Pay','_PER_PAYE_REF_YTD');
987: hr_utility.set_location(' balance type _PER_PAYE_REF_YTD' ,30);
988: ELSE
989: OPEN cur_defined_balance_id('IE BIK Taxable and PRSIable Pay','_PER_PAYE_REF_PPSN_YTD');
990: hr_utility.set_location(' balance type _PER_PAYE_REF_PPSN_YTD' ,30);
991: END IF;
992:
993: FETCH cur_defined_balance_id INTO l_defined_balance_id;
994: CLOSE cur_defined_balance_id;
992:
993: FETCH cur_defined_balance_id INTO l_defined_balance_id;
994: CLOSE cur_defined_balance_id;
995:
996: hr_utility.set_location(' l_defined_balance_id' || l_defined_balance_id,30);
997: hr_utility.set_location(' l_balance_value_apr_09' || l_balance_value_apr_09,30);
998: IF l_assignment_action_apr_09 is not null THEN
999: l_balance_value_apr_09 := PAY_BALANCE_PKG.GET_VALUE(l_defined_balance_id,
1000: l_assignment_action_apr_09,
993: FETCH cur_defined_balance_id INTO l_defined_balance_id;
994: CLOSE cur_defined_balance_id;
995:
996: hr_utility.set_location(' l_defined_balance_id' || l_defined_balance_id,30);
997: hr_utility.set_location(' l_balance_value_apr_09' || l_balance_value_apr_09,30);
998: IF l_assignment_action_apr_09 is not null THEN
999: l_balance_value_apr_09 := PAY_BALANCE_PKG.GET_VALUE(l_defined_balance_id,
1000: l_assignment_action_apr_09,
1001: g_paye_ref,
1004: null,
1005: null,
1006: null);
1007: END IF;
1008: hr_utility.set_location(' l_balance_value_apr_09' || l_balance_value_apr_09,30);
1009:
1010: IF (nvl(p_supp_flag,'N') = 'N') AND (p_last_cess_action IS NOT NULL) and l_assignment_action_apr_09 is not NULL THEN
1011: OPEN get_asg_action_eff_date(p_prev_src_id);
1012: FETCH get_asg_action_eff_date INTO l_action_effective_date;
1010: IF (nvl(p_supp_flag,'N') = 'N') AND (p_last_cess_action IS NOT NULL) and l_assignment_action_apr_09 is not NULL THEN
1011: OPEN get_asg_action_eff_date(p_prev_src_id);
1012: FETCH get_asg_action_eff_date INTO l_action_effective_date;
1013: CLOSE get_asg_action_eff_date;
1014: hr_utility.set_location('l_action_effective_date = ' || l_action_effective_date,40);
1015: IF l_action_effective_date is not null and l_action_effective_date <= to_date('30/04/2009','dd/mm/yyyy') THEN
1016: l_cess_last_bal_value_apr_09 := PAY_BALANCE_PKG.GET_VALUE(l_defined_balance_id,
1017: p_prev_src_id,
1018: g_paye_ref,
1020: null,
1021: null,
1022: null,
1023: null);
1024: hr_utility.set_location(' l_cess_last_bal_value_apr_09' || l_cess_last_bal_value_apr_09,30);
1025: ELSE
1026: l_cess_last_bal_value_apr_09 :=0;
1027: END IF;
1028: l_balance_value_apr_09:=l_balance_value_apr_09 - l_cess_last_bal_value_apr_09;
1032: OPEN get_last_source_id_apr_09;
1033: FETCH get_last_source_id_apr_09 into l_prev_source_id_apr_09,l_payroll_effective_date;
1034: CLOSE get_last_source_id_apr_09;
1035:
1036: hr_utility.set_location(' l_prev_source_id_apr_09' || l_prev_source_id_apr_09,40);
1037: hr_utility.set_location('l_payroll_effective_date = ' || l_payroll_effective_date,40);
1038: IF l_prev_source_id_apr_09 is not null and l_assignment_action_apr_09 is not null and l_payroll_effective_date <= to_date('30/04/2009','dd/mm/yyyy') THEN
1039: l_balance_value1_apr_09 := PAY_BALANCE_PKG.GET_VALUE(l_defined_balance_id,
1040: l_prev_source_id_apr_09,
1033: FETCH get_last_source_id_apr_09 into l_prev_source_id_apr_09,l_payroll_effective_date;
1034: CLOSE get_last_source_id_apr_09;
1035:
1036: hr_utility.set_location(' l_prev_source_id_apr_09' || l_prev_source_id_apr_09,40);
1037: hr_utility.set_location('l_payroll_effective_date = ' || l_payroll_effective_date,40);
1038: IF l_prev_source_id_apr_09 is not null and l_assignment_action_apr_09 is not null and l_payroll_effective_date <= to_date('30/04/2009','dd/mm/yyyy') THEN
1039: l_balance_value1_apr_09 := PAY_BALANCE_PKG.GET_VALUE(l_defined_balance_id,
1040: l_prev_source_id_apr_09,
1041: g_paye_ref,
1044: null,
1045: null,
1046: null);
1047:
1048: hr_utility.set_location(' l_balance_value1_apr_09' || l_balance_value1_apr_09,40);
1049:
1050: ELSE
1051: l_balance_value_apr_09:=0;
1052: l_balance_value1_apr_09:=0;
1053: END IF;
1054:
1055: ELSE
1056: l_balance_value1_apr_09:=0;
1057: hr_utility.set_location(' l_balance_value1_apr_09' || l_balance_value1_apr_09,40);
1058: END IF;
1059: hr_utility.set_location(' l_balance_value_apr_09' || l_balance_value_apr_09,100);
1060: hr_utility.set_location(' l_balance_value1_apr_09' || l_balance_value1_apr_09,100);
1061:
1055: ELSE
1056: l_balance_value1_apr_09:=0;
1057: hr_utility.set_location(' l_balance_value1_apr_09' || l_balance_value1_apr_09,40);
1058: END IF;
1059: hr_utility.set_location(' l_balance_value_apr_09' || l_balance_value_apr_09,100);
1060: hr_utility.set_location(' l_balance_value1_apr_09' || l_balance_value1_apr_09,100);
1061:
1062: l_bik_prsi_taxable_apr_09 := nvl(l_balance_value_apr_09,0) - nvl(l_balance_value1_apr_09,0);
1063: hr_utility.set_location(' l_bik_prsi_taxable' || l_bik_prsi_taxable,40);
1056: l_balance_value1_apr_09:=0;
1057: hr_utility.set_location(' l_balance_value1_apr_09' || l_balance_value1_apr_09,40);
1058: END IF;
1059: hr_utility.set_location(' l_balance_value_apr_09' || l_balance_value_apr_09,100);
1060: hr_utility.set_location(' l_balance_value1_apr_09' || l_balance_value1_apr_09,100);
1061:
1062: l_bik_prsi_taxable_apr_09 := nvl(l_balance_value_apr_09,0) - nvl(l_balance_value1_apr_09,0);
1063: hr_utility.set_location(' l_bik_prsi_taxable' || l_bik_prsi_taxable,40);
1064: hr_utility.set_location(' l_bik_prsi_taxable_apr_09' || l_bik_prsi_taxable_apr_09,40);
1059: hr_utility.set_location(' l_balance_value_apr_09' || l_balance_value_apr_09,100);
1060: hr_utility.set_location(' l_balance_value1_apr_09' || l_balance_value1_apr_09,100);
1061:
1062: l_bik_prsi_taxable_apr_09 := nvl(l_balance_value_apr_09,0) - nvl(l_balance_value1_apr_09,0);
1063: hr_utility.set_location(' l_bik_prsi_taxable' || l_bik_prsi_taxable,40);
1064: hr_utility.set_location(' l_bik_prsi_taxable_apr_09' || l_bik_prsi_taxable_apr_09,40);
1065: l_bik_prsi_taxable := l_bik_prsi_taxable - l_bik_prsi_taxable_apr_09;
1066: hr_utility.set_location(' l_bik_prsi_taxable from may 2009 ' || l_bik_prsi_taxable,40);
1067: l_defined_balance_id:=null;
1060: hr_utility.set_location(' l_balance_value1_apr_09' || l_balance_value1_apr_09,100);
1061:
1062: l_bik_prsi_taxable_apr_09 := nvl(l_balance_value_apr_09,0) - nvl(l_balance_value1_apr_09,0);
1063: hr_utility.set_location(' l_bik_prsi_taxable' || l_bik_prsi_taxable,40);
1064: hr_utility.set_location(' l_bik_prsi_taxable_apr_09' || l_bik_prsi_taxable_apr_09,40);
1065: l_bik_prsi_taxable := l_bik_prsi_taxable - l_bik_prsi_taxable_apr_09;
1066: hr_utility.set_location(' l_bik_prsi_taxable from may 2009 ' || l_bik_prsi_taxable,40);
1067: l_defined_balance_id:=null;
1068: l_balance_value_apr_09:=0;
1062: l_bik_prsi_taxable_apr_09 := nvl(l_balance_value_apr_09,0) - nvl(l_balance_value1_apr_09,0);
1063: hr_utility.set_location(' l_bik_prsi_taxable' || l_bik_prsi_taxable,40);
1064: hr_utility.set_location(' l_bik_prsi_taxable_apr_09' || l_bik_prsi_taxable_apr_09,40);
1065: l_bik_prsi_taxable := l_bik_prsi_taxable - l_bik_prsi_taxable_apr_09;
1066: hr_utility.set_location(' l_bik_prsi_taxable from may 2009 ' || l_bik_prsi_taxable,40);
1067: l_defined_balance_id:=null;
1068: l_balance_value_apr_09:=0;
1069: l_balance_value1_apr_09:=0;
1070:
1071:
1072: /* IE Incoem Levy */
1073: IF l_ppsn_override is null THEN
1074: OPEN cur_defined_balance_id('IE Income Tax Levy','_PER_PAYE_REF_YTD');
1075: hr_utility.set_location(' balance type _PER_PAYE_REF_YTD' ,30);
1076: ELSE
1077: OPEN cur_defined_balance_id('IE Income Tax Levy','_PER_PAYE_REF_PPSN_YTD');
1078: hr_utility.set_location(' balance type _PER_PAYE_REF_PPSN_YTD' ,30);
1079: END IF;
1074: OPEN cur_defined_balance_id('IE Income Tax Levy','_PER_PAYE_REF_YTD');
1075: hr_utility.set_location(' balance type _PER_PAYE_REF_YTD' ,30);
1076: ELSE
1077: OPEN cur_defined_balance_id('IE Income Tax Levy','_PER_PAYE_REF_PPSN_YTD');
1078: hr_utility.set_location(' balance type _PER_PAYE_REF_PPSN_YTD' ,30);
1079: END IF;
1080:
1081: FETCH cur_defined_balance_id INTO l_defined_balance_id;
1082: CLOSE cur_defined_balance_id;
1080:
1081: FETCH cur_defined_balance_id INTO l_defined_balance_id;
1082: CLOSE cur_defined_balance_id;
1083:
1084: hr_utility.set_location(' l_defined_balance_id' || l_defined_balance_id,30);
1085: hr_utility.set_location(' l_balance_value_apr_09' || l_balance_value_apr_09,30);
1086: IF l_assignment_action_apr_09 is not null THEN
1087: l_balance_value_apr_09 := PAY_BALANCE_PKG.GET_VALUE(l_defined_balance_id,
1088: l_assignment_action_apr_09,
1081: FETCH cur_defined_balance_id INTO l_defined_balance_id;
1082: CLOSE cur_defined_balance_id;
1083:
1084: hr_utility.set_location(' l_defined_balance_id' || l_defined_balance_id,30);
1085: hr_utility.set_location(' l_balance_value_apr_09' || l_balance_value_apr_09,30);
1086: IF l_assignment_action_apr_09 is not null THEN
1087: l_balance_value_apr_09 := PAY_BALANCE_PKG.GET_VALUE(l_defined_balance_id,
1088: l_assignment_action_apr_09,
1089: g_paye_ref,
1092: null,
1093: null,
1094: null);
1095: END IF;
1096: hr_utility.set_location(' l_balance_value_apr_09' || l_balance_value_apr_09,30);
1097: IF (nvl(p_supp_flag,'N') = 'N') AND (p_last_cess_action IS NOT NULL) and l_assignment_action_apr_09 is not NULL THEN
1098: OPEN get_asg_action_eff_date(p_prev_src_id);
1099: FETCH get_asg_action_eff_date INTO l_action_effective_date;
1100: CLOSE get_asg_action_eff_date;
1097: IF (nvl(p_supp_flag,'N') = 'N') AND (p_last_cess_action IS NOT NULL) and l_assignment_action_apr_09 is not NULL THEN
1098: OPEN get_asg_action_eff_date(p_prev_src_id);
1099: FETCH get_asg_action_eff_date INTO l_action_effective_date;
1100: CLOSE get_asg_action_eff_date;
1101: hr_utility.set_location('l_action_effective_date = ' || l_action_effective_date,40);
1102: IF l_action_effective_date is not null and l_action_effective_date <= to_date('30/04/2009','dd/mm/yyyy') THEN
1103: l_cess_last_bal_value_apr_09 := PAY_BALANCE_PKG.GET_VALUE(l_defined_balance_id,
1104: p_prev_src_id,
1105: g_paye_ref,
1107: null,
1108: null,
1109: null,
1110: null);
1111: hr_utility.set_location(' l_cess_last_bal_value_apr_09' || l_cess_last_bal_value_apr_09,30);
1112: ELSE
1113: l_cess_last_bal_value_apr_09 :=0;
1114: END IF;
1115: l_balance_value_apr_09:=l_balance_value_apr_09 - l_cess_last_bal_value_apr_09;
1119: OPEN get_last_source_id_apr_09;
1120: FETCH get_last_source_id_apr_09 into l_prev_source_id_apr_09,l_payroll_effective_date;
1121: CLOSE get_last_source_id_apr_09;
1122:
1123: hr_utility.set_location(' l_prev_source_id_apr_09' || l_prev_source_id_apr_09,40);
1124: hr_utility.set_location('l_payroll_effective_date = ' || l_payroll_effective_date,40);
1125: IF l_prev_source_id_apr_09 is not null and l_assignment_action_apr_09 is not null and l_payroll_effective_date <= to_date('30/04/2009','dd/mm/yyyy') THEN
1126: l_balance_value1_apr_09 := PAY_BALANCE_PKG.GET_VALUE(l_defined_balance_id,
1127: l_prev_source_id_apr_09,
1120: FETCH get_last_source_id_apr_09 into l_prev_source_id_apr_09,l_payroll_effective_date;
1121: CLOSE get_last_source_id_apr_09;
1122:
1123: hr_utility.set_location(' l_prev_source_id_apr_09' || l_prev_source_id_apr_09,40);
1124: hr_utility.set_location('l_payroll_effective_date = ' || l_payroll_effective_date,40);
1125: IF l_prev_source_id_apr_09 is not null and l_assignment_action_apr_09 is not null and l_payroll_effective_date <= to_date('30/04/2009','dd/mm/yyyy') THEN
1126: l_balance_value1_apr_09 := PAY_BALANCE_PKG.GET_VALUE(l_defined_balance_id,
1127: l_prev_source_id_apr_09,
1128: g_paye_ref,
1131: null,
1132: null,
1133: null);
1134:
1135: hr_utility.set_location(' l_balance_value1_apr_09' || l_balance_value1_apr_09,40);
1136: ELSE
1137: l_balance_value_apr_09:=0;
1138: l_balance_value1_apr_09:=0;
1139: END IF;
1139: END IF;
1140:
1141: ELSE
1142: l_balance_value1_apr_09:=0;
1143: hr_utility.set_location(' l_balance_value1_apr_09' || l_balance_value1_apr_09,40);
1144: END IF;
1145: hr_utility.set_location(' l_balance_value_apr_09' || l_balance_value_apr_09,100);
1146: hr_utility.set_location(' l_balance_value1_apr_09' || l_balance_value1_apr_09,100);
1147:
1141: ELSE
1142: l_balance_value1_apr_09:=0;
1143: hr_utility.set_location(' l_balance_value1_apr_09' || l_balance_value1_apr_09,40);
1144: END IF;
1145: hr_utility.set_location(' l_balance_value_apr_09' || l_balance_value_apr_09,100);
1146: hr_utility.set_location(' l_balance_value1_apr_09' || l_balance_value1_apr_09,100);
1147:
1148: l_income_levy_apr_09 := nvl(l_balance_value_apr_09,0) - nvl(l_balance_value1_apr_09,0);
1149: hr_utility.set_location(' l_income_levy' || l_income_levy,40);
1142: l_balance_value1_apr_09:=0;
1143: hr_utility.set_location(' l_balance_value1_apr_09' || l_balance_value1_apr_09,40);
1144: END IF;
1145: hr_utility.set_location(' l_balance_value_apr_09' || l_balance_value_apr_09,100);
1146: hr_utility.set_location(' l_balance_value1_apr_09' || l_balance_value1_apr_09,100);
1147:
1148: l_income_levy_apr_09 := nvl(l_balance_value_apr_09,0) - nvl(l_balance_value1_apr_09,0);
1149: hr_utility.set_location(' l_income_levy' || l_income_levy,40);
1150: hr_utility.set_location(' l_income_levy_apr_09' || l_income_levy_apr_09,40);
1145: hr_utility.set_location(' l_balance_value_apr_09' || l_balance_value_apr_09,100);
1146: hr_utility.set_location(' l_balance_value1_apr_09' || l_balance_value1_apr_09,100);
1147:
1148: l_income_levy_apr_09 := nvl(l_balance_value_apr_09,0) - nvl(l_balance_value1_apr_09,0);
1149: hr_utility.set_location(' l_income_levy' || l_income_levy,40);
1150: hr_utility.set_location(' l_income_levy_apr_09' || l_income_levy_apr_09,40);
1151: l_income_levy := l_income_levy - l_income_levy_apr_09;
1152: hr_utility.set_location(' l_income_levy from may 2009 ' || l_income_levy,40);
1153: l_defined_balance_id:=null;
1146: hr_utility.set_location(' l_balance_value1_apr_09' || l_balance_value1_apr_09,100);
1147:
1148: l_income_levy_apr_09 := nvl(l_balance_value_apr_09,0) - nvl(l_balance_value1_apr_09,0);
1149: hr_utility.set_location(' l_income_levy' || l_income_levy,40);
1150: hr_utility.set_location(' l_income_levy_apr_09' || l_income_levy_apr_09,40);
1151: l_income_levy := l_income_levy - l_income_levy_apr_09;
1152: hr_utility.set_location(' l_income_levy from may 2009 ' || l_income_levy,40);
1153: l_defined_balance_id:=null;
1154: l_balance_value_apr_09:=0;
1148: l_income_levy_apr_09 := nvl(l_balance_value_apr_09,0) - nvl(l_balance_value1_apr_09,0);
1149: hr_utility.set_location(' l_income_levy' || l_income_levy,40);
1150: hr_utility.set_location(' l_income_levy_apr_09' || l_income_levy_apr_09,40);
1151: l_income_levy := l_income_levy - l_income_levy_apr_09;
1152: hr_utility.set_location(' l_income_levy from may 2009 ' || l_income_levy,40);
1153: l_defined_balance_id:=null;
1154: l_balance_value_apr_09:=0;
1155: l_balance_value1_apr_09:=0;
1156:
1158: END IF;
1159: /* ---------------------------------------------------------------------------------------------- */
1160: l_gross_pay_total :=l_gross_pay+l_gross_pay_adjust+l_bik_prsi_taxable;
1161:
1162: hr_utility.set_location(' l_gross_pay_total' || l_gross_pay_total,30);
1163:
1164: /*--- USC Code --Start abraghun */
1165:
1166: l_defined_balance_id:=null;
1169:
1170: IF l_ppsn_override is null THEN
1171: OPEN cur_defined_balance_id('IE USC Balance','_PER_PAYE_REF_YTD');
1172:
1173: hr_utility.set_location(' USC _PER_PAYE_REF_YTD' ,30);
1174:
1175: ELSE
1176: OPEN cur_defined_balance_id('IE USC Balance','_PER_PAYE_REF_PPSN_YTD');
1177: hr_utility.set_location(' USC _PER_PAYE_REF_PPSN_YTD' ,30);
1173: hr_utility.set_location(' USC _PER_PAYE_REF_YTD' ,30);
1174:
1175: ELSE
1176: OPEN cur_defined_balance_id('IE USC Balance','_PER_PAYE_REF_PPSN_YTD');
1177: hr_utility.set_location(' USC _PER_PAYE_REF_PPSN_YTD' ,30);
1178: END IF;
1179:
1180:
1181: FETCH cur_defined_balance_id INTO l_defined_balance_id;
1180:
1181: FETCH cur_defined_balance_id INTO l_defined_balance_id;
1182: CLOSE cur_defined_balance_id;
1183:
1184: hr_utility.set_location(' USC Balance value : ' || l_balance_value,60);
1185: IF (p_child_run_ass_act_id IS NOT NULL) THEN
1186: l_balance_value := PAY_BALANCE_PKG.GET_VALUE(l_defined_balance_id,
1187: p_child_run_ass_act_id,
1188: g_paye_ref,
1193: null);
1194: ELSE
1195: l_balance_value:=0;
1196: END IF;
1197: hr_utility.set_location(' USC Balance value : ' || l_balance_value,60);
1198: IF (nvl(p_supp_flag,'N') = 'N') AND (p_last_cess_action IS NOT NULL) THEN
1199: l_cess_last_bal_value := PAY_BALANCE_PKG.GET_VALUE(l_defined_balance_id,
1200: p_prev_src_id,
1201: g_paye_ref,
1203: null,
1204: null,
1205: null,
1206: null);
1207: hr_utility.set_location(' USC last balance value : ' || l_cess_last_bal_value,60);
1208: l_balance_value:=l_balance_value - l_cess_last_bal_value;
1209: END IF;
1210:
1211: IF p_supp_flag ='Y' THEN
1212:
1213: OPEN get_last_source_id;
1214: FETCH get_last_source_id into l_prev_source_id;
1215: CLOSE get_last_source_id;
1216: hr_utility.set_location(' l_prev_source_id' || l_prev_source_id,60);
1217: IF l_prev_source_id IS NOT NULL THEN /* 9337590 */
1218: l_balance_value1 := PAY_BALANCE_PKG.GET_VALUE(l_defined_balance_id,
1219: l_prev_source_id,
1220: g_paye_ref,
1222: null,
1223: null,
1224: null,
1225: null);
1226: hr_utility.set_location(' l_balance_value1' || l_balance_value1,60);
1227: END IF;
1228: ELSE
1229: l_balance_value1:=0;
1230: hr_utility.set_location(' l_balance_value1' || l_balance_value1,60);
1226: hr_utility.set_location(' l_balance_value1' || l_balance_value1,60);
1227: END IF;
1228: ELSE
1229: l_balance_value1:=0;
1230: hr_utility.set_location(' l_balance_value1' || l_balance_value1,60);
1231: END IF;
1232: l_usc := l_balance_value - l_balance_value1;
1233: hr_utility.set_location(' USC : ' || l_usc,100);
1234:
1229: l_balance_value1:=0;
1230: hr_utility.set_location(' l_balance_value1' || l_balance_value1,60);
1231: END IF;
1232: l_usc := l_balance_value - l_balance_value1;
1233: hr_utility.set_location(' USC : ' || l_usc,100);
1234:
1235: /* IE Court Order*/
1236: l_defined_balance_id:=null;
1237: l_balance_value:=0;
1238: l_balance_value1:=0;
1239:
1240: IF l_ppsn_override is null THEN
1241: OPEN cur_defined_balance_id('IE Court Order','_PER_PAYE_REF_YTD');
1242: hr_utility.set_location(' Court Order _PER_PAYE_REF_YTD' ,30);
1243: ELSE
1244: OPEN cur_defined_balance_id('IE Court Order','_PER_PAYE_REF_PPSN_YTD');
1245: hr_utility.set_location(' Court Order _PER_PAYE_REF_PPSN_YTD' ,30);
1246: END IF;
1241: OPEN cur_defined_balance_id('IE Court Order','_PER_PAYE_REF_YTD');
1242: hr_utility.set_location(' Court Order _PER_PAYE_REF_YTD' ,30);
1243: ELSE
1244: OPEN cur_defined_balance_id('IE Court Order','_PER_PAYE_REF_PPSN_YTD');
1245: hr_utility.set_location(' Court Order _PER_PAYE_REF_PPSN_YTD' ,30);
1246: END IF;
1247:
1248: FETCH cur_defined_balance_id INTO l_defined_balance_id;
1249: CLOSE cur_defined_balance_id;
1247:
1248: FETCH cur_defined_balance_id INTO l_defined_balance_id;
1249: CLOSE cur_defined_balance_id;
1250:
1251: hr_utility.set_location(' Court Order Balance value : ' || l_balance_value,60);
1252: IF (p_child_run_ass_act_id IS NOT NULL) THEN
1253: l_balance_value := PAY_BALANCE_PKG.GET_VALUE(l_defined_balance_id,
1254: p_child_run_ass_act_id,
1255: g_paye_ref,
1260: null);
1261: ELSE
1262: l_balance_value:=0;
1263: END IF;
1264: hr_utility.set_location(' Court Order Balance value : ' || l_balance_value,60);
1265: IF (nvl(p_supp_flag,'N') = 'N') AND (p_last_cess_action IS NOT NULL) THEN
1266: l_cess_last_bal_value := PAY_BALANCE_PKG.GET_VALUE(l_defined_balance_id,
1267: p_prev_src_id,
1268: g_paye_ref,
1270: null,
1271: null,
1272: null,
1273: null);
1274: hr_utility.set_location(' Court Order last balance value : ' || l_cess_last_bal_value,60);
1275: l_balance_value:=l_balance_value - l_cess_last_bal_value;
1276: END IF;
1277:
1278: IF p_supp_flag ='Y' THEN
1279:
1280: OPEN get_last_source_id;
1281: FETCH get_last_source_id into l_prev_source_id;
1282: CLOSE get_last_source_id;
1283: hr_utility.set_location(' l_prev_source_id' || l_prev_source_id,60);
1284: IF l_prev_source_id IS NOT NULL THEN /* 9337590 */
1285: l_balance_value1 := PAY_BALANCE_PKG.GET_VALUE(l_defined_balance_id,
1286: l_prev_source_id,
1287: g_paye_ref,
1289: null,
1290: null,
1291: null,
1292: null);
1293: hr_utility.set_location(' l_balance_value1' || l_balance_value1,60);
1294: END IF;
1295: ELSE
1296: l_balance_value1:=0;
1297: hr_utility.set_location(' l_balance_value1' || l_balance_value1,60);
1293: hr_utility.set_location(' l_balance_value1' || l_balance_value1,60);
1294: END IF;
1295: ELSE
1296: l_balance_value1:=0;
1297: hr_utility.set_location(' l_balance_value1' || l_balance_value1,60);
1298: END IF;
1299: l_court_order := l_balance_value - l_balance_value1;
1300: hr_utility.set_location(' Court Order Deductions : ' || l_court_order,101);
1301:
1296: l_balance_value1:=0;
1297: hr_utility.set_location(' l_balance_value1' || l_balance_value1,60);
1298: END IF;
1299: l_court_order := l_balance_value - l_balance_value1;
1300: hr_utility.set_location(' Court Order Deductions : ' || l_court_order,101);
1301:
1302: l_gross_uscable := l_gross_pay_total - l_court_order;
1303:
1304: --11076169
1308: l_balance_value1:=0;
1309:
1310: IF l_ppsn_override is null THEN
1311: OPEN cur_defined_balance_id('IE Court Order Child Portion','_PER_PAYE_REF_YTD');
1312: hr_utility.set_location(' IE Court Order Child Portion _PER_PAYE_REF_YTD' ,30);
1313: ELSE
1314: OPEN cur_defined_balance_id('IE Court Order Child Portion','_PER_PAYE_REF_PPSN_YTD');
1315: hr_utility.set_location(' IE Court Order Child Portion _PER_PAYE_REF_PPSN_YTD' ,30);
1316: END IF;
1311: OPEN cur_defined_balance_id('IE Court Order Child Portion','_PER_PAYE_REF_YTD');
1312: hr_utility.set_location(' IE Court Order Child Portion _PER_PAYE_REF_YTD' ,30);
1313: ELSE
1314: OPEN cur_defined_balance_id('IE Court Order Child Portion','_PER_PAYE_REF_PPSN_YTD');
1315: hr_utility.set_location(' IE Court Order Child Portion _PER_PAYE_REF_PPSN_YTD' ,30);
1316: END IF;
1317:
1318: FETCH cur_defined_balance_id INTO l_defined_balance_id;
1319: CLOSE cur_defined_balance_id;
1317:
1318: FETCH cur_defined_balance_id INTO l_defined_balance_id;
1319: CLOSE cur_defined_balance_id;
1320:
1321: hr_utility.set_location(' IE Court Order Child Portion Balance value : ' || l_balance_value,60);
1322: IF (p_child_run_ass_act_id IS NOT NULL) THEN
1323: l_balance_value := PAY_BALANCE_PKG.GET_VALUE(l_defined_balance_id,
1324: p_child_run_ass_act_id,
1325: g_paye_ref,
1330: null);
1331: ELSE
1332: l_balance_value:=0;
1333: END IF;
1334: hr_utility.set_location(' IE Court Order Child Portion Balance value : ' || l_balance_value,60);
1335: IF (nvl(p_supp_flag,'N') = 'N') AND (p_last_cess_action IS NOT NULL) THEN
1336: l_cess_last_bal_value := 0;
1337: l_cess_last_bal_value := PAY_BALANCE_PKG.GET_VALUE(l_defined_balance_id,
1338: p_prev_src_id,
1341: null,
1342: null,
1343: null,
1344: null);
1345: hr_utility.set_location(' IE Court Order Child Portion last balance value : ' || l_cess_last_bal_value,60);
1346: l_balance_value:=l_balance_value - l_cess_last_bal_value;
1347: END IF;
1348:
1349: IF p_supp_flag ='Y' THEN
1350:
1351: OPEN get_last_source_id;
1352: FETCH get_last_source_id into l_prev_source_id;
1353: CLOSE get_last_source_id;
1354: hr_utility.set_location(' l_prev_source_id' || l_prev_source_id,60);
1355: IF l_prev_source_id IS NOT NULL THEN /* 9337590 */
1356: l_balance_value1 := PAY_BALANCE_PKG.GET_VALUE(l_defined_balance_id,
1357: l_prev_source_id,
1358: g_paye_ref,
1360: null,
1361: null,
1362: null,
1363: null);
1364: hr_utility.set_location(' l_balance_value1' || l_balance_value1,60);
1365: END IF;
1366: ELSE
1367: l_balance_value1:=0;
1368: hr_utility.set_location(' l_balance_value1' || l_balance_value1,60);
1364: hr_utility.set_location(' l_balance_value1' || l_balance_value1,60);
1365: END IF;
1366: ELSE
1367: l_balance_value1:=0;
1368: hr_utility.set_location(' l_balance_value1' || l_balance_value1,60);
1369: END IF;
1370:
1371: l_court_order_child_port := l_balance_value - l_balance_value1;
1372: hr_utility.set_location(' IE Court Order Child Portion Deductions : ' || l_court_order_child_port,101);
1368: hr_utility.set_location(' l_balance_value1' || l_balance_value1,60);
1369: END IF;
1370:
1371: l_court_order_child_port := l_balance_value - l_balance_value1;
1372: hr_utility.set_location(' IE Court Order Child Portion Deductions : ' || l_court_order_child_port,101);
1373:
1374: l_gross_uscable := l_gross_pay_total + l_court_order_child_port;
1375: --11076169
1376:
1381: l_balance_value1:=0;
1382:
1383: IF l_ppsn_override is null THEN
1384: OPEN cur_defined_balance_id('IE PRSA ER Contribution','_PER_PAYE_REF_YTD');
1385: hr_utility.set_location(' IE PRSA ER Contribution _PER_PAYE_REF_YTD' ,30);
1386: ELSE
1387: OPEN cur_defined_balance_id('IE PRSA ER Contribution','_PER_PAYE_REF_PPSN_YTD');
1388: hr_utility.set_location(' IE PRSA ER Contribution _PER_PAYE_REF_PPSN_YTD' ,30);
1389: END IF;
1384: OPEN cur_defined_balance_id('IE PRSA ER Contribution','_PER_PAYE_REF_YTD');
1385: hr_utility.set_location(' IE PRSA ER Contribution _PER_PAYE_REF_YTD' ,30);
1386: ELSE
1387: OPEN cur_defined_balance_id('IE PRSA ER Contribution','_PER_PAYE_REF_PPSN_YTD');
1388: hr_utility.set_location(' IE PRSA ER Contribution _PER_PAYE_REF_PPSN_YTD' ,30);
1389: END IF;
1390:
1391: FETCH cur_defined_balance_id INTO l_defined_balance_id;
1392: CLOSE cur_defined_balance_id;
1390:
1391: FETCH cur_defined_balance_id INTO l_defined_balance_id;
1392: CLOSE cur_defined_balance_id;
1393:
1394: hr_utility.set_location(' IE PRSA ER Contribution Balance value : ' || l_balance_value,60);
1395: IF (p_child_run_ass_act_id IS NOT NULL) THEN
1396: l_balance_value := PAY_BALANCE_PKG.GET_VALUE(l_defined_balance_id,
1397: p_child_run_ass_act_id,
1398: g_paye_ref,
1403: null);
1404: ELSE
1405: l_balance_value:=0;
1406: END IF;
1407: hr_utility.set_location(' IE PRSA ER Contribution Balance value : ' || l_balance_value,60);
1408: IF (nvl(p_supp_flag,'N') = 'N') AND (p_last_cess_action IS NOT NULL) THEN
1409: l_cess_last_bal_value := 0;
1410: l_cess_last_bal_value := PAY_BALANCE_PKG.GET_VALUE(l_defined_balance_id,
1411: p_prev_src_id,
1414: null,
1415: null,
1416: null,
1417: null);
1418: hr_utility.set_location(' IE PRSA ER Contribution last balance value : ' || l_cess_last_bal_value,60);
1419: l_balance_value:=l_balance_value - l_cess_last_bal_value;
1420: END IF;
1421:
1422: IF p_supp_flag ='Y' THEN
1423:
1424: OPEN get_last_source_id;
1425: FETCH get_last_source_id into l_prev_source_id;
1426: CLOSE get_last_source_id;
1427: hr_utility.set_location(' l_prev_source_id' || l_prev_source_id,60);
1428: IF l_prev_source_id IS NOT NULL THEN /* 9337590 */
1429: l_balance_value1 := PAY_BALANCE_PKG.GET_VALUE(l_defined_balance_id,
1430: l_prev_source_id,
1431: g_paye_ref,
1433: null,
1434: null,
1435: null,
1436: null);
1437: hr_utility.set_location(' l_balance_value1' || l_balance_value1,60);
1438: END IF;
1439: ELSE
1440: l_balance_value1:=0;
1441: hr_utility.set_location(' l_balance_value1' || l_balance_value1,60);
1437: hr_utility.set_location(' l_balance_value1' || l_balance_value1,60);
1438: END IF;
1439: ELSE
1440: l_balance_value1:=0;
1441: hr_utility.set_location(' l_balance_value1' || l_balance_value1,60);
1442: END IF;
1443:
1444: l_prsa_er_contribution := l_balance_value - l_balance_value1;
1445: hr_utility.set_location(' IE PRSA ER Contribution Deductions : ' || l_prsa_er_contribution,101);
1441: hr_utility.set_location(' l_balance_value1' || l_balance_value1,60);
1442: END IF;
1443:
1444: l_prsa_er_contribution := l_balance_value - l_balance_value1;
1445: hr_utility.set_location(' IE PRSA ER Contribution Deductions : ' || l_prsa_er_contribution,101);
1446:
1447: l_gross_uscable := l_gross_pay_total + l_prsa_er_contribution;
1448: --13013273
1449:
1446:
1447: l_gross_uscable := l_gross_pay_total + l_prsa_er_contribution;
1448: --13013273
1449:
1450: hr_utility.set_location(' Gross USCable : ' || l_gross_uscable,102);
1451: /*--- USC Code --Ends abraghun */
1452:
1453: OPEN c_employee_details(p_assignment_id,p_date_earned);
1454: FETCH c_employee_details INTO c_employee_details_rec;
1499:
1500:
1501: );
1502: --
1503: hr_utility.set_location('Leaving '||l_proc,20);
1504: END archive_cess_info;
1505:
1506:
1507:
1524: l_start_date VARCHAR2(30);
1525: l_employer NUMBER;
1526:
1527: BEGIN
1528: -- hr_utility.trace_on(null,'cess');
1529: hr_utility.set_location('Entering ' || l_proc,10);
1530:
1531: pay_ie_cess_report.get_parameters (
1532: p_payroll_action_id => pactid
1525: l_employer NUMBER;
1526:
1527: BEGIN
1528: -- hr_utility.trace_on(null,'cess');
1529: hr_utility.set_location('Entering ' || l_proc,10);
1530:
1531: pay_ie_cess_report.get_parameters (
1532: p_payroll_action_id => pactid
1533: , p_token_name => 'END_DATE'
1547: p_payroll_action_id => pactid
1548: , p_token_name => 'START_DATE'
1549: , p_token_value => l_start_date);
1550:
1551: hr_utility.set_location('Step ' || l_proc,20);
1552:
1553: hr_utility.set_location('l_start_date = ' || l_start_date,20);
1554: hr_utility.set_location('l_end_date = ' || l_end_date,20);
1555: hr_utility.set_location('l_employer = ' || l_employer,20);
1549: , p_token_value => l_start_date);
1550:
1551: hr_utility.set_location('Step ' || l_proc,20);
1552:
1553: hr_utility.set_location('l_start_date = ' || l_start_date,20);
1554: hr_utility.set_location('l_end_date = ' || l_end_date,20);
1555: hr_utility.set_location('l_employer = ' || l_employer,20);
1556: hr_utility.set_location('l_bg_id = ' || l_bg_id,20);
1557:
1550:
1551: hr_utility.set_location('Step ' || l_proc,20);
1552:
1553: hr_utility.set_location('l_start_date = ' || l_start_date,20);
1554: hr_utility.set_location('l_end_date = ' || l_end_date,20);
1555: hr_utility.set_location('l_employer = ' || l_employer,20);
1556: hr_utility.set_location('l_bg_id = ' || l_bg_id,20);
1557:
1558: sqlstr := 'SELECT DISTINCT person_id
1551: hr_utility.set_location('Step ' || l_proc,20);
1552:
1553: hr_utility.set_location('l_start_date = ' || l_start_date,20);
1554: hr_utility.set_location('l_end_date = ' || l_end_date,20);
1555: hr_utility.set_location('l_employer = ' || l_employer,20);
1556: hr_utility.set_location('l_bg_id = ' || l_bg_id,20);
1557:
1558: sqlstr := 'SELECT DISTINCT person_id
1559: FROM per_people_f ppf,
1552:
1553: hr_utility.set_location('l_start_date = ' || l_start_date,20);
1554: hr_utility.set_location('l_end_date = ' || l_end_date,20);
1555: hr_utility.set_location('l_employer = ' || l_employer,20);
1556: hr_utility.set_location('l_bg_id = ' || l_bg_id,20);
1557:
1558: sqlstr := 'SELECT DISTINCT person_id
1559: FROM per_people_f ppf,
1560: pay_payroll_actions ppa
1561: WHERE ppa.payroll_action_id = :payroll_action_id
1562: AND ppa.business_group_id +0= ppf.business_group_id
1563: ORDER BY ppf.person_id';
1564:
1565: hr_utility.set_location('After sqlstr formed ' || l_proc,30);
1566:
1567: hr_utility.set_location('Leaving ' || l_proc,40);
1568:
1569: Exception
1563: ORDER BY ppf.person_id';
1564:
1565: hr_utility.set_location('After sqlstr formed ' || l_proc,30);
1566:
1567: hr_utility.set_location('Leaving ' || l_proc,40);
1568:
1569: Exception
1570: when others then
1571: hr_utility.set_location('Leaving via exception section ' || l_proc,40);
1567: hr_utility.set_location('Leaving ' || l_proc,40);
1568:
1569: Exception
1570: when others then
1571: hr_utility.set_location('Leaving via exception section ' || l_proc,40);
1572: sqlstr:='select 1 from dual where to_char(:payroll_action_id) = dummy';
1573: END range_code;
1574:
1575:
1768: --
1769: l_proc VARCHAR2(50) := g_package||'assignment_action_code';
1770: BEGIN
1771:
1772: --hr_utility.trace_on(null,'cess');
1773: hr_utility.set_location('Entering ' || l_proc,10);
1774: pay_ie_cess_report.get_parameters (
1775: p_payroll_action_id => pactid
1776: , p_token_name => 'EMPLOYER'
1769: l_proc VARCHAR2(50) := g_package||'assignment_action_code';
1770: BEGIN
1771:
1772: --hr_utility.trace_on(null,'cess');
1773: hr_utility.set_location('Entering ' || l_proc,10);
1774: pay_ie_cess_report.get_parameters (
1775: p_payroll_action_id => pactid
1776: , p_token_name => 'EMPLOYER'
1777: , p_token_value => g_paye_ref);
1800: p_payroll_action_id => pactid
1801: , p_token_name => 'EMPLOYEE'
1802: , p_token_value => l_employee_person_id); /* knadhan */
1803:
1804: hr_utility.set_location('Step ' || l_proc,20);
1805: hr_utility.set_location('g_paye_ref = ' || g_paye_ref,20);
1806: hr_utility.set_location('l_end_date = ' || l_end_date,20);
1807: hr_utility.set_location('l_start_date = ' || l_start_date,20);
1808: hr_utility.set_location('l_payroll_id = ' || l_payroll_id,20);
1801: , p_token_name => 'EMPLOYEE'
1802: , p_token_value => l_employee_person_id); /* knadhan */
1803:
1804: hr_utility.set_location('Step ' || l_proc,20);
1805: hr_utility.set_location('g_paye_ref = ' || g_paye_ref,20);
1806: hr_utility.set_location('l_end_date = ' || l_end_date,20);
1807: hr_utility.set_location('l_start_date = ' || l_start_date,20);
1808: hr_utility.set_location('l_payroll_id = ' || l_payroll_id,20);
1809: hr_utility.set_location('l_employee_person_id = ' || l_employee_person_id,20);
1802: , p_token_value => l_employee_person_id); /* knadhan */
1803:
1804: hr_utility.set_location('Step ' || l_proc,20);
1805: hr_utility.set_location('g_paye_ref = ' || g_paye_ref,20);
1806: hr_utility.set_location('l_end_date = ' || l_end_date,20);
1807: hr_utility.set_location('l_start_date = ' || l_start_date,20);
1808: hr_utility.set_location('l_payroll_id = ' || l_payroll_id,20);
1809: hr_utility.set_location('l_employee_person_id = ' || l_employee_person_id,20);
1810: hr_utility.set_location('l_bg_id = ' || l_bg_id,20);
1803:
1804: hr_utility.set_location('Step ' || l_proc,20);
1805: hr_utility.set_location('g_paye_ref = ' || g_paye_ref,20);
1806: hr_utility.set_location('l_end_date = ' || l_end_date,20);
1807: hr_utility.set_location('l_start_date = ' || l_start_date,20);
1808: hr_utility.set_location('l_payroll_id = ' || l_payroll_id,20);
1809: hr_utility.set_location('l_employee_person_id = ' || l_employee_person_id,20);
1810: hr_utility.set_location('l_bg_id = ' || l_bg_id,20);
1811:
1804: hr_utility.set_location('Step ' || l_proc,20);
1805: hr_utility.set_location('g_paye_ref = ' || g_paye_ref,20);
1806: hr_utility.set_location('l_end_date = ' || l_end_date,20);
1807: hr_utility.set_location('l_start_date = ' || l_start_date,20);
1808: hr_utility.set_location('l_payroll_id = ' || l_payroll_id,20);
1809: hr_utility.set_location('l_employee_person_id = ' || l_employee_person_id,20);
1810: hr_utility.set_location('l_bg_id = ' || l_bg_id,20);
1811:
1812: l_canonical_start_date := TO_DATE(l_start_date,'yyyy/mm/dd');
1805: hr_utility.set_location('g_paye_ref = ' || g_paye_ref,20);
1806: hr_utility.set_location('l_end_date = ' || l_end_date,20);
1807: hr_utility.set_location('l_start_date = ' || l_start_date,20);
1808: hr_utility.set_location('l_payroll_id = ' || l_payroll_id,20);
1809: hr_utility.set_location('l_employee_person_id = ' || l_employee_person_id,20);
1810: hr_utility.set_location('l_bg_id = ' || l_bg_id,20);
1811:
1812: l_canonical_start_date := TO_DATE(l_start_date,'yyyy/mm/dd');
1813: l_canonical_end_date := TO_DATE(l_end_date,'yyyy/mm/dd');
1806: hr_utility.set_location('l_end_date = ' || l_end_date,20);
1807: hr_utility.set_location('l_start_date = ' || l_start_date,20);
1808: hr_utility.set_location('l_payroll_id = ' || l_payroll_id,20);
1809: hr_utility.set_location('l_employee_person_id = ' || l_employee_person_id,20);
1810: hr_utility.set_location('l_bg_id = ' || l_bg_id,20);
1811:
1812: l_canonical_start_date := TO_DATE(l_start_date,'yyyy/mm/dd');
1813: l_canonical_end_date := TO_DATE(l_end_date,'yyyy/mm/dd');
1814: g_archive_start_date := l_canonical_start_date;
1819: l_person_id := 0;
1820: l_assignment_id:=0;
1821: l_period_of_service_id := 0;
1822:
1823: hr_utility.set_location('l_canonical_start_date = ' || l_canonical_start_date,20);
1824: hr_utility.set_location('l_canonical_end_date = ' || l_canonical_end_date,20);
1825:
1826: hr_utility.set_location('Before csr_prepaid_assignments',21);
1827:
1820: l_assignment_id:=0;
1821: l_period_of_service_id := 0;
1822:
1823: hr_utility.set_location('l_canonical_start_date = ' || l_canonical_start_date,20);
1824: hr_utility.set_location('l_canonical_end_date = ' || l_canonical_end_date,20);
1825:
1826: hr_utility.set_location('Before csr_prepaid_assignments',21);
1827:
1828: l_ppsn_override := NULL;
1822:
1823: hr_utility.set_location('l_canonical_start_date = ' || l_canonical_start_date,20);
1824: hr_utility.set_location('l_canonical_end_date = ' || l_canonical_end_date,20);
1825:
1826: hr_utility.set_location('Before csr_prepaid_assignments',21);
1827:
1828: l_ppsn_override := NULL;
1829: l_ppsn_override_asg := NULL;
1830:
1835: l_payroll_id,
1836: l_employee_person_id)
1837: LOOP
1838:
1839: hr_utility.set_location('Person id..'||to_char(csr_rec.person_id),21-1);
1840: hr_utility.set_location('assignment_id..'||to_char(csr_rec.assignment_id),21-1);
1841: hr_utility.set_location('run_action_id..'||to_char(csr_rec.run_action_id),21-1);
1842: hr_utility.set_location('prepaid_action_id.'||to_char(csr_rec.prepaid_action_id),21-1);
1843: hr_utility.set_location('works_number..'||to_char(csr_rec.works_number),21-1);
1836: l_employee_person_id)
1837: LOOP
1838:
1839: hr_utility.set_location('Person id..'||to_char(csr_rec.person_id),21-1);
1840: hr_utility.set_location('assignment_id..'||to_char(csr_rec.assignment_id),21-1);
1841: hr_utility.set_location('run_action_id..'||to_char(csr_rec.run_action_id),21-1);
1842: hr_utility.set_location('prepaid_action_id.'||to_char(csr_rec.prepaid_action_id),21-1);
1843: hr_utility.set_location('works_number..'||to_char(csr_rec.works_number),21-1);
1844: hr_utility.set_location('period_of_service_id..'||to_char(csr_rec.period_of_service_id),21-1);
1837: LOOP
1838:
1839: hr_utility.set_location('Person id..'||to_char(csr_rec.person_id),21-1);
1840: hr_utility.set_location('assignment_id..'||to_char(csr_rec.assignment_id),21-1);
1841: hr_utility.set_location('run_action_id..'||to_char(csr_rec.run_action_id),21-1);
1842: hr_utility.set_location('prepaid_action_id.'||to_char(csr_rec.prepaid_action_id),21-1);
1843: hr_utility.set_location('works_number..'||to_char(csr_rec.works_number),21-1);
1844: hr_utility.set_location('period_of_service_id..'||to_char(csr_rec.period_of_service_id),21-1);
1845:
1838:
1839: hr_utility.set_location('Person id..'||to_char(csr_rec.person_id),21-1);
1840: hr_utility.set_location('assignment_id..'||to_char(csr_rec.assignment_id),21-1);
1841: hr_utility.set_location('run_action_id..'||to_char(csr_rec.run_action_id),21-1);
1842: hr_utility.set_location('prepaid_action_id.'||to_char(csr_rec.prepaid_action_id),21-1);
1843: hr_utility.set_location('works_number..'||to_char(csr_rec.works_number),21-1);
1844: hr_utility.set_location('period_of_service_id..'||to_char(csr_rec.period_of_service_id),21-1);
1845:
1846: hr_utility.set_location('Person id..'||to_char(csr_rec.person_id),21-1);
1839: hr_utility.set_location('Person id..'||to_char(csr_rec.person_id),21-1);
1840: hr_utility.set_location('assignment_id..'||to_char(csr_rec.assignment_id),21-1);
1841: hr_utility.set_location('run_action_id..'||to_char(csr_rec.run_action_id),21-1);
1842: hr_utility.set_location('prepaid_action_id.'||to_char(csr_rec.prepaid_action_id),21-1);
1843: hr_utility.set_location('works_number..'||to_char(csr_rec.works_number),21-1);
1844: hr_utility.set_location('period_of_service_id..'||to_char(csr_rec.period_of_service_id),21-1);
1845:
1846: hr_utility.set_location('Person id..'||to_char(csr_rec.person_id),21-1);
1847: hr_utility.set_location('Temp Person id..'||to_char(l_person_id),21-2);
1840: hr_utility.set_location('assignment_id..'||to_char(csr_rec.assignment_id),21-1);
1841: hr_utility.set_location('run_action_id..'||to_char(csr_rec.run_action_id),21-1);
1842: hr_utility.set_location('prepaid_action_id.'||to_char(csr_rec.prepaid_action_id),21-1);
1843: hr_utility.set_location('works_number..'||to_char(csr_rec.works_number),21-1);
1844: hr_utility.set_location('period_of_service_id..'||to_char(csr_rec.period_of_service_id),21-1);
1845:
1846: hr_utility.set_location('Person id..'||to_char(csr_rec.person_id),21-1);
1847: hr_utility.set_location('Temp Person id..'||to_char(l_person_id),21-2);
1848:
1842: hr_utility.set_location('prepaid_action_id.'||to_char(csr_rec.prepaid_action_id),21-1);
1843: hr_utility.set_location('works_number..'||to_char(csr_rec.works_number),21-1);
1844: hr_utility.set_location('period_of_service_id..'||to_char(csr_rec.period_of_service_id),21-1);
1845:
1846: hr_utility.set_location('Person id..'||to_char(csr_rec.person_id),21-1);
1847: hr_utility.set_location('Temp Person id..'||to_char(l_person_id),21-2);
1848:
1849: l_ppsn_override := NULL;
1850: l_ppsn_override_asg := NULL;
1843: hr_utility.set_location('works_number..'||to_char(csr_rec.works_number),21-1);
1844: hr_utility.set_location('period_of_service_id..'||to_char(csr_rec.period_of_service_id),21-1);
1845:
1846: hr_utility.set_location('Person id..'||to_char(csr_rec.person_id),21-1);
1847: hr_utility.set_location('Temp Person id..'||to_char(l_person_id),21-2);
1848:
1849: l_ppsn_override := NULL;
1850: l_ppsn_override_asg := NULL;
1851: hr_utility.set_location('before fetch l_ppsn_override'||to_char(l_ppsn_override),21-3);
1847: hr_utility.set_location('Temp Person id..'||to_char(l_person_id),21-2);
1848:
1849: l_ppsn_override := NULL;
1850: l_ppsn_override_asg := NULL;
1851: hr_utility.set_location('before fetch l_ppsn_override'||to_char(l_ppsn_override),21-3);
1852: hr_utility.set_location(' before fetch l_ppsn_override_asg'||to_char(l_ppsn_override_asg),21-3);
1853:
1854: OPEN csr_ppsn_override(csr_rec.assignment_id);
1855: FETCH csr_ppsn_override INTO l_ppsn_override;
1848:
1849: l_ppsn_override := NULL;
1850: l_ppsn_override_asg := NULL;
1851: hr_utility.set_location('before fetch l_ppsn_override'||to_char(l_ppsn_override),21-3);
1852: hr_utility.set_location(' before fetch l_ppsn_override_asg'||to_char(l_ppsn_override_asg),21-3);
1853:
1854: OPEN csr_ppsn_override(csr_rec.assignment_id);
1855: FETCH csr_ppsn_override INTO l_ppsn_override;
1856: CLOSE csr_ppsn_override;
1854: OPEN csr_ppsn_override(csr_rec.assignment_id);
1855: FETCH csr_ppsn_override INTO l_ppsn_override;
1856: CLOSE csr_ppsn_override;
1857:
1858: hr_utility.set_location('l_ppsn_override'||to_char(l_ppsn_override),21-3);
1859:
1860: IF l_ppsn_override IS NOT NULL THEN
1861: OPEN csr_ppsn_min_asg(l_ppsn_override,csr_rec.person_id,csr_rec.period_of_service_id);
1862: FETCH csr_ppsn_min_asg INTO l_ppsn_override_asg;
1860: IF l_ppsn_override IS NOT NULL THEN
1861: OPEN csr_ppsn_min_asg(l_ppsn_override,csr_rec.person_id,csr_rec.period_of_service_id);
1862: FETCH csr_ppsn_min_asg INTO l_ppsn_override_asg;
1863: CLOSE csr_ppsn_min_asg;
1864: hr_utility.set_location('l_ppsn_override_asg'||to_char(l_ppsn_override_asg),21-4);
1865: END IF;
1866:
1867:
1868:
1865: END IF;
1866:
1867:
1868:
1869: hr_utility.set_location('csr_rec.assignment_id'||csr_rec.assignment_id,21-4);
1870:
1871: IF (l_person_id <> csr_rec.person_id and l_ppsn_override IS NULL )
1872: OR /* knadhan */
1873: ((l_person_id <> csr_rec.person_id and l_ppsn_override IS NOT NULL) OR (l_ppsn_override_asg=csr_rec.assignment_id and l_ppsn_override IS NOT NULL))
1872: OR /* knadhan */
1873: ((l_person_id <> csr_rec.person_id and l_ppsn_override IS NOT NULL) OR (l_ppsn_override_asg=csr_rec.assignment_id and l_ppsn_override IS NOT NULL))
1874: THEN
1875:
1876: hr_utility.set_location('Different Person '|| csr_rec.person_id ,22);
1877:
1878: SELECT pay_assignment_actions_s.NEXTVAL
1879: INTO l_actid
1880: FROM dual;
1879: INTO l_actid
1880: FROM dual;
1881:
1882: -- CREATE THE ARCHIVE ASSIGNMENT ACTION FOR THE MASTER ASSIGNMENT ACTION
1883: hr_utility.set_location('ASSIGNMENT ID : ' || csr_rec.assignment_id,23);
1884: hr_utility.trace('ASSIGNMENT ID : ' || csr_rec.assignment_id);
1885:
1886: hr_nonrun_asact.insact(l_actid,csr_rec.assignment_id,pactid,chunk,g_paye_ref);
1887: -- CREATE THE ARCHIVE TO PAYROLL MASTER ASSIGNMENT ACTION INTERLOCK AND
1880: FROM dual;
1881:
1882: -- CREATE THE ARCHIVE ASSIGNMENT ACTION FOR THE MASTER ASSIGNMENT ACTION
1883: hr_utility.set_location('ASSIGNMENT ID : ' || csr_rec.assignment_id,23);
1884: hr_utility.trace('ASSIGNMENT ID : ' || csr_rec.assignment_id);
1885:
1886: hr_nonrun_asact.insact(l_actid,csr_rec.assignment_id,pactid,chunk,g_paye_ref);
1887: -- CREATE THE ARCHIVE TO PAYROLL MASTER ASSIGNMENT ACTION INTERLOCK AND
1888: -- THE ARCHIVE TO PREPAYMENT ASSIGNMENT ACTION INTERLOCK
1885:
1886: hr_nonrun_asact.insact(l_actid,csr_rec.assignment_id,pactid,chunk,g_paye_ref);
1887: -- CREATE THE ARCHIVE TO PAYROLL MASTER ASSIGNMENT ACTION INTERLOCK AND
1888: -- THE ARCHIVE TO PREPAYMENT ASSIGNMENT ACTION INTERLOCK
1889: -- hr_utility.set_location('creating lock1 ' || l_actid || ' to ' || csr_rec.run_action_id,20);
1890: -- hr_utility.set_location('creating lock2 ' || l_actid || ' to ' || csr_rec.prepaid_action_id,20);
1891: END IF; --
1892: hr_utility.set_location('l_prepay_action_id : ' || l_prepay_action_id,100);
1893: hr_utility.set_location('csr_rec.prepaid_action_id : ' || csr_rec.prepaid_action_id,101);
1886: hr_nonrun_asact.insact(l_actid,csr_rec.assignment_id,pactid,chunk,g_paye_ref);
1887: -- CREATE THE ARCHIVE TO PAYROLL MASTER ASSIGNMENT ACTION INTERLOCK AND
1888: -- THE ARCHIVE TO PREPAYMENT ASSIGNMENT ACTION INTERLOCK
1889: -- hr_utility.set_location('creating lock1 ' || l_actid || ' to ' || csr_rec.run_action_id,20);
1890: -- hr_utility.set_location('creating lock2 ' || l_actid || ' to ' || csr_rec.prepaid_action_id,20);
1891: END IF; --
1892: hr_utility.set_location('l_prepay_action_id : ' || l_prepay_action_id,100);
1893: hr_utility.set_location('csr_rec.prepaid_action_id : ' || csr_rec.prepaid_action_id,101);
1894: hr_utility.set_location('l_actid : ' || l_actid,102);
1888: -- THE ARCHIVE TO PREPAYMENT ASSIGNMENT ACTION INTERLOCK
1889: -- hr_utility.set_location('creating lock1 ' || l_actid || ' to ' || csr_rec.run_action_id,20);
1890: -- hr_utility.set_location('creating lock2 ' || l_actid || ' to ' || csr_rec.prepaid_action_id,20);
1891: END IF; --
1892: hr_utility.set_location('l_prepay_action_id : ' || l_prepay_action_id,100);
1893: hr_utility.set_location('csr_rec.prepaid_action_id : ' || csr_rec.prepaid_action_id,101);
1894: hr_utility.set_location('l_actid : ' || l_actid,102);
1895:
1896: IF l_prepay_action_id <> csr_rec.prepaid_action_id THEN
1889: -- hr_utility.set_location('creating lock1 ' || l_actid || ' to ' || csr_rec.run_action_id,20);
1890: -- hr_utility.set_location('creating lock2 ' || l_actid || ' to ' || csr_rec.prepaid_action_id,20);
1891: END IF; --
1892: hr_utility.set_location('l_prepay_action_id : ' || l_prepay_action_id,100);
1893: hr_utility.set_location('csr_rec.prepaid_action_id : ' || csr_rec.prepaid_action_id,101);
1894: hr_utility.set_location('l_actid : ' || l_actid,102);
1895:
1896: IF l_prepay_action_id <> csr_rec.prepaid_action_id THEN
1897: hr_utility.set_location('locked id : ' || csr_rec.prepaid_action_id,23);
1890: -- hr_utility.set_location('creating lock2 ' || l_actid || ' to ' || csr_rec.prepaid_action_id,20);
1891: END IF; --
1892: hr_utility.set_location('l_prepay_action_id : ' || l_prepay_action_id,100);
1893: hr_utility.set_location('csr_rec.prepaid_action_id : ' || csr_rec.prepaid_action_id,101);
1894: hr_utility.set_location('l_actid : ' || l_actid,102);
1895:
1896: IF l_prepay_action_id <> csr_rec.prepaid_action_id THEN
1897: hr_utility.set_location('locked id : ' || csr_rec.prepaid_action_id,23);
1898: hr_nonrun_asact.insint(l_actid,csr_rec.prepaid_action_id);
1893: hr_utility.set_location('csr_rec.prepaid_action_id : ' || csr_rec.prepaid_action_id,101);
1894: hr_utility.set_location('l_actid : ' || l_actid,102);
1895:
1896: IF l_prepay_action_id <> csr_rec.prepaid_action_id THEN
1897: hr_utility.set_location('locked id : ' || csr_rec.prepaid_action_id,23);
1898: hr_nonrun_asact.insint(l_actid,csr_rec.prepaid_action_id);
1899: END IF;
1900:
1901: hr_nonrun_asact.insint(l_actid,csr_rec.run_action_id);
1905: l_period_of_service_id := csr_rec.period_of_service_id;
1906:
1907: END LOOP;
1908:
1909: hr_utility.set_location('Leaving ' || l_proc,20);
1910: END assignment_action_code;
1911:
1912:
1913:
1945: l_error varchar2(1) ;
1946: BEGIN
1947:
1948:
1949: hr_utility.set_location('Entering ' || l_proc,10);
1950:
1951: g_archive_pact := p_payroll_action_id;
1952:
1953: OPEN csr_archive_effective_date(p_payroll_action_id);
1974: p_payroll_action_id => p_payroll_action_id
1975: , p_token_name => 'BG_ID'
1976: , p_token_value => l_bg_id);
1977:
1978: hr_utility.set_location('Step ' || l_proc,20);
1979: hr_utility.set_location('g_paye_ref = ' || g_paye_ref,20);
1980: hr_utility.set_location('l_end_date = ' || l_end_date,20);
1981: hr_utility.set_location('l_start_date = ' || l_start_date,20);
1982: hr_utility.set_location('l_bg_id = ' || l_bg_id,20);
1975: , p_token_name => 'BG_ID'
1976: , p_token_value => l_bg_id);
1977:
1978: hr_utility.set_location('Step ' || l_proc,20);
1979: hr_utility.set_location('g_paye_ref = ' || g_paye_ref,20);
1980: hr_utility.set_location('l_end_date = ' || l_end_date,20);
1981: hr_utility.set_location('l_start_date = ' || l_start_date,20);
1982: hr_utility.set_location('l_bg_id = ' || l_bg_id,20);
1983:
1976: , p_token_value => l_bg_id);
1977:
1978: hr_utility.set_location('Step ' || l_proc,20);
1979: hr_utility.set_location('g_paye_ref = ' || g_paye_ref,20);
1980: hr_utility.set_location('l_end_date = ' || l_end_date,20);
1981: hr_utility.set_location('l_start_date = ' || l_start_date,20);
1982: hr_utility.set_location('l_bg_id = ' || l_bg_id,20);
1983:
1984: l_canonical_start_date := TO_DATE(l_start_date,'yyyy/mm/dd');
1977:
1978: hr_utility.set_location('Step ' || l_proc,20);
1979: hr_utility.set_location('g_paye_ref = ' || g_paye_ref,20);
1980: hr_utility.set_location('l_end_date = ' || l_end_date,20);
1981: hr_utility.set_location('l_start_date = ' || l_start_date,20);
1982: hr_utility.set_location('l_bg_id = ' || l_bg_id,20);
1983:
1984: l_canonical_start_date := TO_DATE(l_start_date,'yyyy/mm/dd');
1985: l_canonical_end_date := TO_DATE(l_end_date,'yyyy/mm/dd');
1978: hr_utility.set_location('Step ' || l_proc,20);
1979: hr_utility.set_location('g_paye_ref = ' || g_paye_ref,20);
1980: hr_utility.set_location('l_end_date = ' || l_end_date,20);
1981: hr_utility.set_location('l_start_date = ' || l_start_date,20);
1982: hr_utility.set_location('l_bg_id = ' || l_bg_id,20);
1983:
1984: l_canonical_start_date := TO_DATE(l_start_date,'yyyy/mm/dd');
1985: l_canonical_end_date := TO_DATE(l_end_date,'yyyy/mm/dd');
1986:
1987: -- Initialized g_archive_end_date to support Retry Option
1988: g_archive_end_date := TO_DATE(l_end_date,'yyyy/mm/dd');
1989: g_archive_start_date := l_canonical_start_date;
1990:
1991: hr_utility.set_location('l_canonical_start_date = ' || l_canonical_start_date,20);
1992:
1993:
1994:
1995: hr_utility.set_location('stage 1',22);
1991: hr_utility.set_location('l_canonical_start_date = ' || l_canonical_start_date,20);
1992:
1993:
1994:
1995: hr_utility.set_location('stage 1',22);
1996:
1997: hr_utility.set_location('stage 2',23);
1998:
1999: hr_utility.set_location('stage 3',24);
1993:
1994:
1995: hr_utility.set_location('stage 1',22);
1996:
1997: hr_utility.set_location('stage 2',23);
1998:
1999: hr_utility.set_location('stage 3',24);
2000:
2001:
1995: hr_utility.set_location('stage 1',22);
1996:
1997: hr_utility.set_location('stage 2',23);
1998:
1999: hr_utility.set_location('stage 3',24);
2000:
2001:
2002: hr_utility.set_location('Leaving ' || l_proc,20);
2003: END archive_init;
1998:
1999: hr_utility.set_location('stage 3',24);
2000:
2001:
2002: hr_utility.set_location('Leaving ' || l_proc,20);
2003: END archive_init;
2004:
2005:
2006:
2200:
2201: BEGIN
2202:
2203: l_lat_act_seq := NULL;
2204: hr_utility.set_location('Entering'|| l_proc,10);
2205: hr_utility.set_location('Step '|| l_proc,20);
2206: hr_utility.set_location('p_assactid = ' || p_assactid,20);
2207:
2208: -- retrieve the chunk number for the current assignment action
2201: BEGIN
2202:
2203: l_lat_act_seq := NULL;
2204: hr_utility.set_location('Entering'|| l_proc,10);
2205: hr_utility.set_location('Step '|| l_proc,20);
2206: hr_utility.set_location('p_assactid = ' || p_assactid,20);
2207:
2208: -- retrieve the chunk number for the current assignment action
2209: SELECT paa.chunk_number,paa.assignment_id
2202:
2203: l_lat_act_seq := NULL;
2204: hr_utility.set_location('Entering'|| l_proc,10);
2205: hr_utility.set_location('Step '|| l_proc,20);
2206: hr_utility.set_location('p_assactid = ' || p_assactid,20);
2207:
2208: -- retrieve the chunk number for the current assignment action
2209: SELECT paa.chunk_number,paa.assignment_id
2210: INTO l_chunk_number,l_assignment_id
2215: l_record_count := 0;
2216:
2217: FOR csr_rec IN csr_assignment_actions(p_assactid)
2218: LOOP
2219: hr_utility.set_location('csr_rec.master_assignment_action_id = ' || csr_rec.master_assignment_action_id,20);
2220: hr_utility.set_location('csr_rec.pre_assignment_action_id = ' || csr_rec.pre_assignment_action_id,20);
2221: hr_utility.set_location('csr_rec.assignment_id = ' || csr_rec.assignment_id,20);
2222: hr_utility.set_location('csr_rec.date_earned = ' ||to_char( csr_rec.date_earned,'dd-mon-yyyy'),20);
2223: hr_utility.set_location('csr_rec.pre_effective_date = ' ||to_char( csr_rec.pre_effective_date,'dd-mon-yyyy'),20);
2216:
2217: FOR csr_rec IN csr_assignment_actions(p_assactid)
2218: LOOP
2219: hr_utility.set_location('csr_rec.master_assignment_action_id = ' || csr_rec.master_assignment_action_id,20);
2220: hr_utility.set_location('csr_rec.pre_assignment_action_id = ' || csr_rec.pre_assignment_action_id,20);
2221: hr_utility.set_location('csr_rec.assignment_id = ' || csr_rec.assignment_id,20);
2222: hr_utility.set_location('csr_rec.date_earned = ' ||to_char( csr_rec.date_earned,'dd-mon-yyyy'),20);
2223: hr_utility.set_location('csr_rec.pre_effective_date = ' ||to_char( csr_rec.pre_effective_date,'dd-mon-yyyy'),20);
2224: hr_utility.set_location('csr_rec.time_period_id = ' || csr_rec.time_period_id,20);
2217: FOR csr_rec IN csr_assignment_actions(p_assactid)
2218: LOOP
2219: hr_utility.set_location('csr_rec.master_assignment_action_id = ' || csr_rec.master_assignment_action_id,20);
2220: hr_utility.set_location('csr_rec.pre_assignment_action_id = ' || csr_rec.pre_assignment_action_id,20);
2221: hr_utility.set_location('csr_rec.assignment_id = ' || csr_rec.assignment_id,20);
2222: hr_utility.set_location('csr_rec.date_earned = ' ||to_char( csr_rec.date_earned,'dd-mon-yyyy'),20);
2223: hr_utility.set_location('csr_rec.pre_effective_date = ' ||to_char( csr_rec.pre_effective_date,'dd-mon-yyyy'),20);
2224: hr_utility.set_location('csr_rec.time_period_id = ' || csr_rec.time_period_id,20);
2225:
2218: LOOP
2219: hr_utility.set_location('csr_rec.master_assignment_action_id = ' || csr_rec.master_assignment_action_id,20);
2220: hr_utility.set_location('csr_rec.pre_assignment_action_id = ' || csr_rec.pre_assignment_action_id,20);
2221: hr_utility.set_location('csr_rec.assignment_id = ' || csr_rec.assignment_id,20);
2222: hr_utility.set_location('csr_rec.date_earned = ' ||to_char( csr_rec.date_earned,'dd-mon-yyyy'),20);
2223: hr_utility.set_location('csr_rec.pre_effective_date = ' ||to_char( csr_rec.pre_effective_date,'dd-mon-yyyy'),20);
2224: hr_utility.set_location('csr_rec.time_period_id = ' || csr_rec.time_period_id,20);
2225:
2226: OPEN cur_assgn_payroll(csr_rec.assignment_id,csr_rec.date_earned);
2219: hr_utility.set_location('csr_rec.master_assignment_action_id = ' || csr_rec.master_assignment_action_id,20);
2220: hr_utility.set_location('csr_rec.pre_assignment_action_id = ' || csr_rec.pre_assignment_action_id,20);
2221: hr_utility.set_location('csr_rec.assignment_id = ' || csr_rec.assignment_id,20);
2222: hr_utility.set_location('csr_rec.date_earned = ' ||to_char( csr_rec.date_earned,'dd-mon-yyyy'),20);
2223: hr_utility.set_location('csr_rec.pre_effective_date = ' ||to_char( csr_rec.pre_effective_date,'dd-mon-yyyy'),20);
2224: hr_utility.set_location('csr_rec.time_period_id = ' || csr_rec.time_period_id,20);
2225:
2226: OPEN cur_assgn_payroll(csr_rec.assignment_id,csr_rec.date_earned);
2227: FETCH cur_assgn_payroll INTO l_payroll_id,l_person_id,l_pos_id;
2220: hr_utility.set_location('csr_rec.pre_assignment_action_id = ' || csr_rec.pre_assignment_action_id,20);
2221: hr_utility.set_location('csr_rec.assignment_id = ' || csr_rec.assignment_id,20);
2222: hr_utility.set_location('csr_rec.date_earned = ' ||to_char( csr_rec.date_earned,'dd-mon-yyyy'),20);
2223: hr_utility.set_location('csr_rec.pre_effective_date = ' ||to_char( csr_rec.pre_effective_date,'dd-mon-yyyy'),20);
2224: hr_utility.set_location('csr_rec.time_period_id = ' || csr_rec.time_period_id,20);
2225:
2226: OPEN cur_assgn_payroll(csr_rec.assignment_id,csr_rec.date_earned);
2227: FETCH cur_assgn_payroll INTO l_payroll_id,l_person_id,l_pos_id;
2228: CLOSE cur_assgn_payroll;
2230: l_ppsn_override:=null;
2231: open csr_ppsn_override(csr_rec.assignment_id);
2232: fetch csr_ppsn_override into l_ppsn_override;
2233: close csr_ppsn_override;
2234: hr_utility.set_location('PPSN Override value = ' || l_ppsn_override,20);
2235:
2236:
2237: --Fetch the action sequence of latest payroll run child action locked by latest cess
2238: --For the assignment 4468864
2239: OPEN cur_get_latest_cess(g_archive_pact,l_person_id,l_ppsn_override);
2240: FETCH cur_get_latest_cess INTO l_concat_sequence;
2241:
2242: IF cur_get_latest_cess%NOTFOUND THEN
2243: hr_utility.set_location('Action Sequence notfound = ' || l_lat_act_seq,21);
2244: l_lat_act_seq := 0;
2245: l_prev_src_id := 0;
2246: END IF;
2247:
2247:
2248: l_lat_act_seq := nvl(substr(l_concat_sequence,1,15),0);
2249: l_prev_src_id := nvl(substr(l_concat_sequence,16),0);
2250:
2251: hr_utility.set_location('Action Sequence = ' || l_lat_act_seq,21);
2252: CLOSE cur_get_latest_cess;
2253:
2254: hr_utility.set_location('Action Sequence = ' || l_lat_act_seq,21);
2255:
2250:
2251: hr_utility.set_location('Action Sequence = ' || l_lat_act_seq,21);
2252: CLOSE cur_get_latest_cess;
2253:
2254: hr_utility.set_location('Action Sequence = ' || l_lat_act_seq,21);
2255:
2256:
2257:
2258: l_child_pay_action_ppsn := NULL;
2257:
2258: l_child_pay_action_ppsn := NULL;
2259: OPEN cur_child_pay_action_ppsn(l_person_id,g_archive_end_date,l_lat_act_seq,l_ppsn_override);
2260: FETCH cur_child_pay_action_ppsn INTO l_child_pay_action_ppsn;
2261: hr_utility.set_location('Child Action PPSN ='||l_child_pay_action_ppsn,20);
2262: CLOSE cur_child_pay_action_ppsn;
2263:
2264: l_child_pay_action := NULL;
2265: OPEN cur_child_pay_action(l_person_id,g_archive_end_date,l_lat_act_seq);
2267:
2268: if (l_child_pay_action_ppsn is null) THEN
2269: l_child_pay_action_ppsn:=l_child_pay_action;
2270: end if;
2271: hr_utility.set_location('Child Action PPSN after assigning ='||l_child_pay_action_ppsn,20);
2272:
2273: -- hr_utility.set_location('Child Action PPSN ='|| l_child_pay_action_ppsn,24);
2274: hr_utility.set_location('Child Action ='||l_child_pay_action,24);
2275:
2269: l_child_pay_action_ppsn:=l_child_pay_action;
2270: end if;
2271: hr_utility.set_location('Child Action PPSN after assigning ='||l_child_pay_action_ppsn,20);
2272:
2273: -- hr_utility.set_location('Child Action PPSN ='|| l_child_pay_action_ppsn,24);
2274: hr_utility.set_location('Child Action ='||l_child_pay_action,24);
2275:
2276: -------------- Moved here for bug 5386432 ----
2277: get_termination_date(p_action_context_id => p_assactid,
2270: end if;
2271: hr_utility.set_location('Child Action PPSN after assigning ='||l_child_pay_action_ppsn,20);
2272:
2273: -- hr_utility.set_location('Child Action PPSN ='|| l_child_pay_action_ppsn,24);
2274: hr_utility.set_location('Child Action ='||l_child_pay_action,24);
2275:
2276: -------------- Moved here for bug 5386432 ----
2277: get_termination_date(p_action_context_id => p_assactid,
2278: p_assignment_id => csr_rec.assignment_id,
2290: -- Fetch the Payroll action of Last cess 5005788
2291: OPEN cur_get_cess_pact(l_last_cess_action);
2292: FETCH cur_get_cess_pact INTO l_last_cess_pact;
2293: CLOSE cur_get_cess_pact;
2294: hr_utility.set_location(' l_termination_date = '||l_termination_date,30);
2295: hr_utility.set_location(' l_supp_pymt_date = '||l_supp_pymt_date,30);
2296: hr_utility.set_location(' l_supp_flag = '||l_supp_flag,30);
2297: hr_utility.set_location(' l_child_pay_action = '||l_child_pay_action,30);
2298: hr_utility.set_location(' l_record_count = '|| l_record_count,30);
2291: OPEN cur_get_cess_pact(l_last_cess_action);
2292: FETCH cur_get_cess_pact INTO l_last_cess_pact;
2293: CLOSE cur_get_cess_pact;
2294: hr_utility.set_location(' l_termination_date = '||l_termination_date,30);
2295: hr_utility.set_location(' l_supp_pymt_date = '||l_supp_pymt_date,30);
2296: hr_utility.set_location(' l_supp_flag = '||l_supp_flag,30);
2297: hr_utility.set_location(' l_child_pay_action = '||l_child_pay_action,30);
2298: hr_utility.set_location(' l_record_count = '|| l_record_count,30);
2299: hr_utility.set_location(' csr_rec.assignment_id = '|| csr_rec.assignment_id,30);
2292: FETCH cur_get_cess_pact INTO l_last_cess_pact;
2293: CLOSE cur_get_cess_pact;
2294: hr_utility.set_location(' l_termination_date = '||l_termination_date,30);
2295: hr_utility.set_location(' l_supp_pymt_date = '||l_supp_pymt_date,30);
2296: hr_utility.set_location(' l_supp_flag = '||l_supp_flag,30);
2297: hr_utility.set_location(' l_child_pay_action = '||l_child_pay_action,30);
2298: hr_utility.set_location(' l_record_count = '|| l_record_count,30);
2299: hr_utility.set_location(' csr_rec.assignment_id = '|| csr_rec.assignment_id,30);
2300: hr_utility.set_location(' l_assignment_id = '|| l_assignment_id,30);
2293: CLOSE cur_get_cess_pact;
2294: hr_utility.set_location(' l_termination_date = '||l_termination_date,30);
2295: hr_utility.set_location(' l_supp_pymt_date = '||l_supp_pymt_date,30);
2296: hr_utility.set_location(' l_supp_flag = '||l_supp_flag,30);
2297: hr_utility.set_location(' l_child_pay_action = '||l_child_pay_action,30);
2298: hr_utility.set_location(' l_record_count = '|| l_record_count,30);
2299: hr_utility.set_location(' csr_rec.assignment_id = '|| csr_rec.assignment_id,30);
2300: hr_utility.set_location(' l_assignment_id = '|| l_assignment_id,30);
2301: ------------------
2294: hr_utility.set_location(' l_termination_date = '||l_termination_date,30);
2295: hr_utility.set_location(' l_supp_pymt_date = '||l_supp_pymt_date,30);
2296: hr_utility.set_location(' l_supp_flag = '||l_supp_flag,30);
2297: hr_utility.set_location(' l_child_pay_action = '||l_child_pay_action,30);
2298: hr_utility.set_location(' l_record_count = '|| l_record_count,30);
2299: hr_utility.set_location(' csr_rec.assignment_id = '|| csr_rec.assignment_id,30);
2300: hr_utility.set_location(' l_assignment_id = '|| l_assignment_id,30);
2301: ------------------
2302:
2295: hr_utility.set_location(' l_supp_pymt_date = '||l_supp_pymt_date,30);
2296: hr_utility.set_location(' l_supp_flag = '||l_supp_flag,30);
2297: hr_utility.set_location(' l_child_pay_action = '||l_child_pay_action,30);
2298: hr_utility.set_location(' l_record_count = '|| l_record_count,30);
2299: hr_utility.set_location(' csr_rec.assignment_id = '|| csr_rec.assignment_id,30);
2300: hr_utility.set_location(' l_assignment_id = '|| l_assignment_id,30);
2301: ------------------
2302:
2303: IF ((l_child_pay_action IS NULL) and l_supp_flag = 'Y' ) THEN
2296: hr_utility.set_location(' l_supp_flag = '||l_supp_flag,30);
2297: hr_utility.set_location(' l_child_pay_action = '||l_child_pay_action,30);
2298: hr_utility.set_location(' l_record_count = '|| l_record_count,30);
2299: hr_utility.set_location(' csr_rec.assignment_id = '|| csr_rec.assignment_id,30);
2300: hr_utility.set_location(' l_assignment_id = '|| l_assignment_id,30);
2301: ------------------
2302:
2303: IF ((l_child_pay_action IS NULL) and l_supp_flag = 'Y' ) THEN
2304: NULL;
2304: NULL;
2305: ELSE
2306: IF (l_record_count = 0 AND csr_rec.assignment_id = l_assignment_id)
2307: THEN
2308: hr_utility.set_location(' entered if of else ',30);
2309: -- Create child cess action to lock the child payroll process child action
2310: -- To avoid data corruption 4468864
2311: SELECT pay_assignment_actions_s.NEXTVAL
2312: INTO l_actid
2321: , prepayid => NULL
2322: , status => 'C'
2323: , source_act => p_assactid);
2324:
2325: hr_utility.set_location('creating lock4 ' || l_actid || ' to ' || l_child_pay_action,30);
2326: -- bug 5386432, checks l_child_pay_action is not null, since for zero
2327: -- earnigns there will not child actions, so cant lock any
2328: IF l_child_pay_action IS NOT NULL THEN
2329: hr_nonrun_asact.insint(
2348: , p_prev_src_id => l_prev_src_id);
2349:
2350:
2351:
2352: hr_utility.set_location('sg Person Id ='||l_person_id,32);
2353: hr_utility.set_location('sg Termination Date ='||l_termination_date,33);
2354: hr_utility.set_location('sg Payroll action ='||g_archive_pact,34);
2355: hr_utility.set_location('sg cess action ='||l_last_cess_action,35);
2356:
2349:
2350:
2351:
2352: hr_utility.set_location('sg Person Id ='||l_person_id,32);
2353: hr_utility.set_location('sg Termination Date ='||l_termination_date,33);
2354: hr_utility.set_location('sg Payroll action ='||g_archive_pact,34);
2355: hr_utility.set_location('sg cess action ='||l_last_cess_action,35);
2356:
2357: IF l_last_cess_action IS NOT NULL THEN
2350:
2351:
2352: hr_utility.set_location('sg Person Id ='||l_person_id,32);
2353: hr_utility.set_location('sg Termination Date ='||l_termination_date,33);
2354: hr_utility.set_location('sg Payroll action ='||g_archive_pact,34);
2355: hr_utility.set_location('sg cess action ='||l_last_cess_action,35);
2356:
2357: IF l_last_cess_action IS NOT NULL THEN
2358: hr_nonrun_asact.insint(
2351:
2352: hr_utility.set_location('sg Person Id ='||l_person_id,32);
2353: hr_utility.set_location('sg Termination Date ='||l_termination_date,33);
2354: hr_utility.set_location('sg Payroll action ='||g_archive_pact,34);
2355: hr_utility.set_location('sg cess action ='||l_last_cess_action,35);
2356:
2357: IF l_last_cess_action IS NOT NULL THEN
2358: hr_nonrun_asact.insint(
2359: lockingactid => l_actid
2363: END IF;
2364: END IF;
2365: CLOSE cur_child_pay_action;
2366: l_date_earned := csr_rec.date_earned;
2367: hr_utility.set_location('Before loop end for assignment '||csr_rec.assignment_id,80);
2368: END LOOP;
2369: hr_utility.set_location('Leaving '|| l_proc,80);
2370: END archive_data;
2371:
2365: CLOSE cur_child_pay_action;
2366: l_date_earned := csr_rec.date_earned;
2367: hr_utility.set_location('Before loop end for assignment '||csr_rec.assignment_id,80);
2368: END LOOP;
2369: hr_utility.set_location('Leaving '|| l_proc,80);
2370: END archive_data;
2371:
2372:
2373: PROCEDURE gen_header_xml
2379: l_proc VARCHAR2(100);
2380: l_payroll_action_id number;
2381: BEGIN
2382: l_proc := g_package || 'gen_header_xml';
2383: hr_utility.set_location ('Entering '||l_proc,1500);
2384:
2385: l_payroll_action_id := pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID');
2386: hr_utility.set_location('Inside pay_ie_cess_report.gen_header_xml,l_payroll_action_id: '||l_payroll_action_id,300);
2387:
2382: l_proc := g_package || 'gen_header_xml';
2383: hr_utility.set_location ('Entering '||l_proc,1500);
2384:
2385: l_payroll_action_id := pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID');
2386: hr_utility.set_location('Inside pay_ie_cess_report.gen_header_xml,l_payroll_action_id: '||l_payroll_action_id,300);
2387:
2388:
2389:
2390:
2397: END IF;
2398:
2399: EXCEPTION
2400: WHEN Others THEN
2401: Hr_Utility.set_location('..'||'SQL-ERRM :'||SQLERRM,1214);
2402:
2403: END gen_header_xml;
2404:
2405: PROCEDURE gen_footer_xml
2407: l_buf VARCHAR2(2000);
2408: l_proc VARCHAR2(100);
2409: begin
2410: l_proc := g_package || 'gen_footer_xml';
2411: hr_utility.set_location ('Entering '||l_proc, 1520);
2412:
2413: l_buf := l_buf || ''||EOL ;
2414: --
2415: pay_core_files.write_to_magtape_lob(l_buf);
2412:
2413: l_buf := l_buf || ''||EOL ;
2414: --
2415: pay_core_files.write_to_magtape_lob(l_buf);
2416: hr_utility.set_location ('Leaving '||l_proc, 1530);
2417:
2418: end gen_footer_xml;
2419:
2420:
2487:
2488:
2489:
2490: BEGIN
2491: hr_utility.set_location(' Entering: pay_ie_cess_report.gen_body_xml: ', 270);
2492:
2493: l_payroll_action_id := pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID');
2494: l_asg_action_id := pay_magtape_generic.get_parameter_value('TRANSFER_ACT_ID');
2495:
2493: l_payroll_action_id := pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID');
2494: l_asg_action_id := pay_magtape_generic.get_parameter_value('TRANSFER_ACT_ID');
2495:
2496:
2497: hr_utility.set_location('l_payroll_action_id '||TO_CHAR(l_payroll_action_id),275);
2498: hr_utility.set_location('l_asg_action_id '||TO_CHAR(l_asg_action_id),280);
2499:
2500:
2501: OPEN cur_cess_emp_details(l_asg_action_id);
2494: l_asg_action_id := pay_magtape_generic.get_parameter_value('TRANSFER_ACT_ID');
2495:
2496:
2497: hr_utility.set_location('l_payroll_action_id '||TO_CHAR(l_payroll_action_id),275);
2498: hr_utility.set_location('l_asg_action_id '||TO_CHAR(l_asg_action_id),280);
2499:
2500:
2501: OPEN cur_cess_emp_details(l_asg_action_id);
2502: FETCH cur_cess_emp_details into cur_cess_emp_details_rec;
2535:
2536: l_string := l_string ||'';
2537:
2538:
2539: hr_utility.set_location('Before leaving gen_body_xml: length(l_string) = '||length(l_string),290);
2540: l_clob := l_clob||l_string;
2541:
2542: IF l_clob IS NOT NULL THEN
2543: l_blob := c2b(l_clob);
2545: END IF;
2546:
2547: EXCEPTION
2548: WHEN Others THEN
2549: Hr_Utility.set_location('..'||'SQL-ERRM :'||SQLERRM,1213);
2550: END gen_body_xml;
2551: end;
2552: