[Home] [Help]
306: BEGIN
307:
308: -- get the total insurable weeks for the last period max action id
309:
310: hr_utility.set_location('Period action id..'||p_assignment_action_id,101);
311: hr_utility.set_location('Max action id..'|| p_max_action_id,101);
312:
313: OPEN c1;
314: FETCH c1 into l_child_assignemnt_action;
307:
308: -- get the total insurable weeks for the last period max action id
309:
310: hr_utility.set_location('Period action id..'||p_assignment_action_id,101);
311: hr_utility.set_location('Max action id..'|| p_max_action_id,101);
312:
313: OPEN c1;
314: FETCH c1 into l_child_assignemnt_action;
315: CLOSE c1;
323: ,p_dimension_name --6633719
324: ,p_ppsn_override), --6633719
325: 0
326: ));
327: hr_utility.set_location('p_tot_insurable_weeks..'|| p_tot_insurable_weeks,103);
328: hr_utility.set_location('p_insurable_weeks..'|| p_insurable_weeks,104);
329: -- get the employee and total PRSI contribution till the last period max action id
330: p_this_emp_prsi_cont := to_char(p_prsi_employee_cont -
331: ROUND (
324: ,p_ppsn_override), --6633719
325: 0
326: ));
327: hr_utility.set_location('p_tot_insurable_weeks..'|| p_tot_insurable_weeks,103);
328: hr_utility.set_location('p_insurable_weeks..'|| p_insurable_weeks,104);
329: -- get the employee and total PRSI contribution till the last period max action id
330: p_this_emp_prsi_cont := to_char(p_prsi_employee_cont -
331: ROUND (
332: NVL (
384: 0
385: ),
386: 2
387: ));
388: hr_utility.set_location ('p_prsi_employee_cont'|| p_this_emp_prsi_cont,105);
389: hr_utility.set_location ('p_this_emp_prsi_cont'|| p_this_emp_prsi_cont,106);
390:
391: p_this_tot_prsi := to_char(p_prsi_tot_cont -
392: ROUND (
385: ),
386: 2
387: ));
388: hr_utility.set_location ('p_prsi_employee_cont'|| p_this_emp_prsi_cont,105);
389: hr_utility.set_location ('p_this_emp_prsi_cont'|| p_this_emp_prsi_cont,106);
390:
391: p_this_tot_prsi := to_char(p_prsi_tot_cont -
392: ROUND (
393: NVL (
499: 0
500: ),
501: 2
502: ));
503: hr_utility.set_location ('p_this_tot_prsi'|| p_this_tot_prsi,105);
504: hr_utility.set_location ('p_prsi_tot_cont'|| p_prsi_tot_cont,106);
505: FOR i IN get_prsi_classes
506: LOOP
507: IF l_counter = 0 THEN
500: ),
501: 2
502: ));
503: hr_utility.set_location ('p_this_tot_prsi'|| p_this_tot_prsi,105);
504: hr_utility.set_location ('p_prsi_tot_cont'|| p_prsi_tot_cont,106);
505: FOR i IN get_prsi_classes
506: LOOP
507: IF l_counter = 0 THEN
508: l_counter := l_counter + 1;
506: LOOP
507: IF l_counter = 0 THEN
508: l_counter := l_counter + 1;
509: l_p60_prsi_classes(l_counter) := i.result_value;
510: hr_utility.set_location ('Initial Class Value is :'|| l_p60_prsi_classes(l_counter), 16);
511: ELSE
512: FOR k IN 1 .. l_counter
513: loop
514: IF l_p60_prsi_classes(k) = i.result_value THEN
520: END LOOP;
521: IF temp_flag = 0 AND l_counter <=9 THEN
522: l_counter := l_counter + 1;
523: l_p60_prsi_classes(l_counter) := i.result_value;
524: hr_utility.set_location ('Subsequent Class Value is :'|| l_p60_prsi_classes(l_counter), 17);
525: END IF;
526: temp_flag := null;
527: END IF;
528: END LOOP;
602:
603: l_business_group_id NUMBER;
604: l_token_value VARCHAR2 (50);
605: BEGIN
606: hr_utility.set_location ( 'p_token_name = '
607: || p_token_name, 20);
608: OPEN csr_parameter_info (p_payroll_action_id, p_token_name);
609: FETCH csr_parameter_info INTO l_token_value, l_business_group_id;
610: CLOSE csr_parameter_info;
615: ELSE
616: l_token_value := TRIM (l_token_value);
617: END IF;
618:
619: hr_utility.set_location ( 'l_token_value = '
620: || l_token_value, 20);
621: hr_utility.set_location ( 'Leaving '
622: || 'get_parameters', 30);
623: RETURN l_token_value;
617: END IF;
618:
619: hr_utility.set_location ( 'l_token_value = '
620: || l_token_value, 20);
621: hr_utility.set_location ( 'Leaving '
622: || 'get_parameters', 30);
623: RETURN l_token_value;
624: END get_parameter;
625:
687: ) between l_start_date and l_end_date
688: AND ppa.business_group_id = l_bg_id;
689: vik_str varchar2(2000);
690: BEGIN
691: --hr_utility.trace_on(null,'vikp35');
692:
693: hr_utility.set_location (l_proc_name, 1);
694: l_out_var := pay_ie_p35.get_parameter (
695: p_payroll_action_id=> p_payroll_action_id,
689: vik_str varchar2(2000);
690: BEGIN
691: --hr_utility.trace_on(null,'vikp35');
692:
693: hr_utility.set_location (l_proc_name, 1);
694: l_out_var := pay_ie_p35.get_parameter (
695: p_payroll_action_id=> p_payroll_action_id,
696: p_token_name=> 'END_DATE'
697: );
706: p_token_name=> 'BG_ID'
707: );
708: l_bg_id := TO_NUMBER (l_out_var);
709:
710: /* hr_utility.set_location ('Start Date '||to_char(l_start_date), 2);
711: hr_utility.set_location ('End Date '|| to_char(l_end_date), 3);
712: hr_utility.set_location ('Business Group id '|| to_char(l_bg_id), 4);*/
713:
714: -- Check to make sure there is a p30 process run
707: );
708: l_bg_id := TO_NUMBER (l_out_var);
709:
710: /* hr_utility.set_location ('Start Date '||to_char(l_start_date), 2);
711: hr_utility.set_location ('End Date '|| to_char(l_end_date), 3);
712: hr_utility.set_location ('Business Group id '|| to_char(l_bg_id), 4);*/
713:
714: -- Check to make sure there is a p30 process run
715: -- existing for business group, otherwise fail
708: l_bg_id := TO_NUMBER (l_out_var);
709:
710: /* hr_utility.set_location ('Start Date '||to_char(l_start_date), 2);
711: hr_utility.set_location ('End Date '|| to_char(l_end_date), 3);
712: hr_utility.set_location ('Business Group id '|| to_char(l_bg_id), 4);*/
713:
714: -- Check to make sure there is a p30 process run
715: -- existing for business group, otherwise fail
716: hr_utility.set_location ('End Date 1',22);
712: hr_utility.set_location ('Business Group id '|| to_char(l_bg_id), 4);*/
713:
714: -- Check to make sure there is a p30 process run
715: -- existing for business group, otherwise fail
716: hr_utility.set_location ('End Date 1',22);
717: OPEN csr_p30_process;
718: FETCH csr_p30_process INTO l_dummy;
719: hr_utility.set_location ('End Date 2',22);
720: IF l_dummy = 0
715: -- existing for business group, otherwise fail
716: hr_utility.set_location ('End Date 1',22);
717: OPEN csr_p30_process;
718: FETCH csr_p30_process INTO l_dummy;
719: hr_utility.set_location ('End Date 2',22);
720: IF l_dummy = 0
721: THEN
722: CLOSE csr_p30_process;
723: RAISE p30_error;
724: END IF;
725:
726: CLOSE csr_p30_process;
727: --
728: hr_utility.set_location ('End Date 3',22);
729: sqlstr := 'select distinct asg.person_id
730: from per_periods_of_service pos,
731: per_assignments_f asg,
732: pay_payroll_actions ppa
735: and pos.period_of_service_id = asg.period_of_service_id
736: and pos.business_group_id = ppa.business_group_id
737: and asg.business_group_id = ppa.business_group_id
738: order by asg.person_id';
739: hr_utility.set_location ('End Date 4',22);
740:
741: EXCEPTION
742: WHEN p30_error
743: THEN
741: EXCEPTION
742: WHEN p30_error
743: THEN
744: -- Write to the conc logfile, and try to archive err msg.
745: hr_utility.set_location (
746: ' Leaving with EXCEPTION: '
747: || l_proc_name,
748: 100
749: );
1026: p_token_name=> 'BG_ID'
1027: );
1028: l_bg_id := TO_NUMBER (l_out_var);
1029:
1030: hr_utility.set_location ('l_segment4 ' ||l_segment4, 12);
1031: hr_utility.set_location ('l_assignment_set_id ' || to_char(l_assignment_set_id), 12);
1032: hr_utility.set_location ('l_payroll_id ' || to_char(l_payroll_id), 13);
1033: hr_utility.set_location ('stperson ' || to_char(stperson), 14);
1034: hr_utility.set_location ('endperson ' || to_char(endperson), 15);
1027: );
1028: l_bg_id := TO_NUMBER (l_out_var);
1029:
1030: hr_utility.set_location ('l_segment4 ' ||l_segment4, 12);
1031: hr_utility.set_location ('l_assignment_set_id ' || to_char(l_assignment_set_id), 12);
1032: hr_utility.set_location ('l_payroll_id ' || to_char(l_payroll_id), 13);
1033: hr_utility.set_location ('stperson ' || to_char(stperson), 14);
1034: hr_utility.set_location ('endperson ' || to_char(endperson), 15);
1035: hr_utility.set_location ('l_bg_id ' || to_char(l_bg_id), 16);
1028: l_bg_id := TO_NUMBER (l_out_var);
1029:
1030: hr_utility.set_location ('l_segment4 ' ||l_segment4, 12);
1031: hr_utility.set_location ('l_assignment_set_id ' || to_char(l_assignment_set_id), 12);
1032: hr_utility.set_location ('l_payroll_id ' || to_char(l_payroll_id), 13);
1033: hr_utility.set_location ('stperson ' || to_char(stperson), 14);
1034: hr_utility.set_location ('endperson ' || to_char(endperson), 15);
1035: hr_utility.set_location ('l_bg_id ' || to_char(l_bg_id), 16);
1036: hr_utility.set_location ('pactid ' || to_char(pactid), 16);
1029:
1030: hr_utility.set_location ('l_segment4 ' ||l_segment4, 12);
1031: hr_utility.set_location ('l_assignment_set_id ' || to_char(l_assignment_set_id), 12);
1032: hr_utility.set_location ('l_payroll_id ' || to_char(l_payroll_id), 13);
1033: hr_utility.set_location ('stperson ' || to_char(stperson), 14);
1034: hr_utility.set_location ('endperson ' || to_char(endperson), 15);
1035: hr_utility.set_location ('l_bg_id ' || to_char(l_bg_id), 16);
1036: hr_utility.set_location ('pactid ' || to_char(pactid), 16);
1037:
1030: hr_utility.set_location ('l_segment4 ' ||l_segment4, 12);
1031: hr_utility.set_location ('l_assignment_set_id ' || to_char(l_assignment_set_id), 12);
1032: hr_utility.set_location ('l_payroll_id ' || to_char(l_payroll_id), 13);
1033: hr_utility.set_location ('stperson ' || to_char(stperson), 14);
1034: hr_utility.set_location ('endperson ' || to_char(endperson), 15);
1035: hr_utility.set_location ('l_bg_id ' || to_char(l_bg_id), 16);
1036: hr_utility.set_location ('pactid ' || to_char(pactid), 16);
1037:
1038: --
1031: hr_utility.set_location ('l_assignment_set_id ' || to_char(l_assignment_set_id), 12);
1032: hr_utility.set_location ('l_payroll_id ' || to_char(l_payroll_id), 13);
1033: hr_utility.set_location ('stperson ' || to_char(stperson), 14);
1034: hr_utility.set_location ('endperson ' || to_char(endperson), 15);
1035: hr_utility.set_location ('l_bg_id ' || to_char(l_bg_id), 16);
1036: hr_utility.set_location ('pactid ' || to_char(pactid), 16);
1037:
1038: --
1039: OPEN csr_get_flag_from_set;
1032: hr_utility.set_location ('l_payroll_id ' || to_char(l_payroll_id), 13);
1033: hr_utility.set_location ('stperson ' || to_char(stperson), 14);
1034: hr_utility.set_location ('endperson ' || to_char(endperson), 15);
1035: hr_utility.set_location ('l_bg_id ' || to_char(l_bg_id), 16);
1036: hr_utility.set_location ('pactid ' || to_char(pactid), 16);
1037:
1038: --
1039: OPEN csr_get_flag_from_set;
1040: FETCH csr_get_flag_from_set into l_set_flag;
1047: --6633719QA
1048:
1049: FOR csr_select_asg_rec IN csr_locked_asgs
1050: LOOP
1051: hr_utility.set_location('Person id..'||to_char(csr_select_asg_rec.person_id),17);
1052: hr_utility.set_location('Temp Person id..'||to_char(l_temp_person_id),18);
1053: hr_utility.set_location('csr_select_asg_rec.assignment_id'||to_char(csr_select_asg_rec.assignment_id),18);
1054: --6633719QA
1055: --Reinitailising the variables as person changes.
1048:
1049: FOR csr_select_asg_rec IN csr_locked_asgs
1050: LOOP
1051: hr_utility.set_location('Person id..'||to_char(csr_select_asg_rec.person_id),17);
1052: hr_utility.set_location('Temp Person id..'||to_char(l_temp_person_id),18);
1053: hr_utility.set_location('csr_select_asg_rec.assignment_id'||to_char(csr_select_asg_rec.assignment_id),18);
1054: --6633719QA
1055: --Reinitailising the variables as person changes.
1056: IF l_temp_person_id <> csr_select_asg_rec.person_id
1049: FOR csr_select_asg_rec IN csr_locked_asgs
1050: LOOP
1051: hr_utility.set_location('Person id..'||to_char(csr_select_asg_rec.person_id),17);
1052: hr_utility.set_location('Temp Person id..'||to_char(l_temp_person_id),18);
1053: hr_utility.set_location('csr_select_asg_rec.assignment_id'||to_char(csr_select_asg_rec.assignment_id),18);
1054: --6633719QA
1055: --Reinitailising the variables as person changes.
1056: IF l_temp_person_id <> csr_select_asg_rec.person_id
1057: THEN
1069: END IF;
1070: --6633719QA
1071: CLOSE csr_ppsn_override;
1072:
1073: hr_utility.set_location('l_ppsn_override'||to_char(l_ppsn_override),19);
1074: IF l_ppsn_override IS NOT NULL --6633719QA
1075: THEN
1076: OPEN csr_ppsn_max_asg(l_ppsn_override,csr_select_asg_rec.person_id);
1077: FETCH csr_ppsn_max_asg INTO l_ppsn_override_asg;
1076: OPEN csr_ppsn_max_asg(l_ppsn_override,csr_select_asg_rec.person_id);
1077: FETCH csr_ppsn_max_asg INTO l_ppsn_override_asg;
1078: CLOSE csr_ppsn_max_asg;
1079: END IF; --6633719QA
1080: hr_utility.set_location('l_ppsn_override_asg'||to_char(l_ppsn_override_asg),20);
1081:
1082: -- Create assignment action per person
1083: IF ((l_temp_person_id <> csr_select_asg_rec.person_id
1084: and l_ppsn_override IS NULL)
1099: SELECT pay_assignment_actions_s.NEXTVAL
1100: INTO l_actid
1101: FROM DUAL;
1102:
1103: hr_utility.set_location('INSIDE IF',18);
1104: hr_utility.set_location('Insert asg actions asg_id '||to_char(csr_select_asg_rec.assignment_id),18);
1105: hr_utility.set_location('Insert asg actions l_actid '||to_char(l_actid),18);
1106: hr_utility.set_location('Insert asg actions pactid '||to_char(pactid),18);
1107:
1100: INTO l_actid
1101: FROM DUAL;
1102:
1103: hr_utility.set_location('INSIDE IF',18);
1104: hr_utility.set_location('Insert asg actions asg_id '||to_char(csr_select_asg_rec.assignment_id),18);
1105: hr_utility.set_location('Insert asg actions l_actid '||to_char(l_actid),18);
1106: hr_utility.set_location('Insert asg actions pactid '||to_char(pactid),18);
1107:
1108: hr_nonrun_asact.insact (l_actid,
1101: FROM DUAL;
1102:
1103: hr_utility.set_location('INSIDE IF',18);
1104: hr_utility.set_location('Insert asg actions asg_id '||to_char(csr_select_asg_rec.assignment_id),18);
1105: hr_utility.set_location('Insert asg actions l_actid '||to_char(l_actid),18);
1106: hr_utility.set_location('Insert asg actions pactid '||to_char(pactid),18);
1107:
1108: hr_nonrun_asact.insact (l_actid,
1109: csr_select_asg_rec.assignment_id,
1102:
1103: hr_utility.set_location('INSIDE IF',18);
1104: hr_utility.set_location('Insert asg actions asg_id '||to_char(csr_select_asg_rec.assignment_id),18);
1105: hr_utility.set_location('Insert asg actions l_actid '||to_char(l_actid),18);
1106: hr_utility.set_location('Insert asg actions pactid '||to_char(pactid),18);
1107:
1108: hr_nonrun_asact.insact (l_actid,
1109: csr_select_asg_rec.assignment_id,
1110: pactid,
1124: IF l_flag_exclusion <> 'Y' then
1125: SELECT pay_assignment_actions_s.NEXTVAL
1126: INTO l_actid
1127: FROM DUAL;
1128: hr_utility.set_location('INSIDE ELSE',18);
1129: hr_utility.set_location('Insert asg actions asg_id '||to_char(csr_select_asg_rec.assignment_id),18);
1130: hr_utility.set_location('Insert asg actions l_actid '||to_char(l_actid),18);
1131: hr_utility.set_location('Insert asg actions pactid '||to_char(pactid),18);
1132: hr_nonrun_asact.insact (l_actid,
1125: SELECT pay_assignment_actions_s.NEXTVAL
1126: INTO l_actid
1127: FROM DUAL;
1128: hr_utility.set_location('INSIDE ELSE',18);
1129: hr_utility.set_location('Insert asg actions asg_id '||to_char(csr_select_asg_rec.assignment_id),18);
1130: hr_utility.set_location('Insert asg actions l_actid '||to_char(l_actid),18);
1131: hr_utility.set_location('Insert asg actions pactid '||to_char(pactid),18);
1132: hr_nonrun_asact.insact (l_actid,
1133: csr_select_asg_rec.assignment_id,
1126: INTO l_actid
1127: FROM DUAL;
1128: hr_utility.set_location('INSIDE ELSE',18);
1129: hr_utility.set_location('Insert asg actions asg_id '||to_char(csr_select_asg_rec.assignment_id),18);
1130: hr_utility.set_location('Insert asg actions l_actid '||to_char(l_actid),18);
1131: hr_utility.set_location('Insert asg actions pactid '||to_char(pactid),18);
1132: hr_nonrun_asact.insact (l_actid,
1133: csr_select_asg_rec.assignment_id,
1134: pactid,
1127: FROM DUAL;
1128: hr_utility.set_location('INSIDE ELSE',18);
1129: hr_utility.set_location('Insert asg actions asg_id '||to_char(csr_select_asg_rec.assignment_id),18);
1130: hr_utility.set_location('Insert asg actions l_actid '||to_char(l_actid),18);
1131: hr_utility.set_location('Insert asg actions pactid '||to_char(pactid),18);
1132: hr_nonrun_asact.insact (l_actid,
1133: csr_select_asg_rec.assignment_id,
1134: pactid,
1135: CHUNK,
1142: --
1143: -- Lock all the run-result assignment actions for a person per assignment.
1144: IF l_flag_exclusion <> 'Y' then
1145:
1146: hr_utility.set_location('Insert asg action Intlks l_actid'||to_char(l_actid),18);
1147: hr_utility.set_location('Insert asg action Intlks csr_select_asg_rec.aa'||to_char(csr_select_asg_rec.aa),18);
1148: hr_nonrun_asact.insint (l_actid,csr_select_asg_rec.aa);
1149: END IF;
1150: l_temp_person_id := csr_select_asg_rec.person_id;
1143: -- Lock all the run-result assignment actions for a person per assignment.
1144: IF l_flag_exclusion <> 'Y' then
1145:
1146: hr_utility.set_location('Insert asg action Intlks l_actid'||to_char(l_actid),18);
1147: hr_utility.set_location('Insert asg action Intlks csr_select_asg_rec.aa'||to_char(csr_select_asg_rec.aa),18);
1148: hr_nonrun_asact.insint (l_actid,csr_select_asg_rec.aa);
1149: END IF;
1150: l_temp_person_id := csr_select_asg_rec.person_id;
1151: --
1150: l_temp_person_id := csr_select_asg_rec.person_id;
1151: --
1152: END LOOP;
1153:
1154: hr_utility.set_location ('Leaving action_creation', 18);
1155: END action_creation;
1156:
1157:
1158: Procedure archive_code (p_assactid IN NUMBER
2167: l_child_assignment_action_id := NULL;
2168: l_Act_Context_id := NULL;
2169: l_Act_Context_value := NULL;
2170: --6633719
2171: hr_utility.set_location('p_assactid ..'||p_assactid,1000);
2172: hr_utility.set_location('p_effective_date..'||p_effective_date,1000);
2173:
2174:
2175: OPEN get_assignment_id;
2168: l_Act_Context_id := NULL;
2169: l_Act_Context_value := NULL;
2170: --6633719
2171: hr_utility.set_location('p_assactid ..'||p_assactid,1000);
2172: hr_utility.set_location('p_effective_date..'||p_effective_date,1000);
2173:
2174:
2175: OPEN get_assignment_id;
2176: FETCH get_assignment_id into v_payroll_action_id,v_assignment_id,v_chunk;
2175: OPEN get_assignment_id;
2176: FETCH get_assignment_id into v_payroll_action_id,v_assignment_id,v_chunk;
2177: CLOSE get_assignment_id;
2178:
2179: hr_utility.set_location('v_payroll_action_id..'||v_payroll_action_id,1000);
2180: hr_utility.set_location('v_assignment_id..'||v_assignment_id,1000);
2181: hr_utility.set_location('v_chunk..'||v_chunk,1000);
2182: --6633719
2183: OPEN csr_ppsn_override(v_assignment_id);
2176: FETCH get_assignment_id into v_payroll_action_id,v_assignment_id,v_chunk;
2177: CLOSE get_assignment_id;
2178:
2179: hr_utility.set_location('v_payroll_action_id..'||v_payroll_action_id,1000);
2180: hr_utility.set_location('v_assignment_id..'||v_assignment_id,1000);
2181: hr_utility.set_location('v_chunk..'||v_chunk,1000);
2182: --6633719
2183: OPEN csr_ppsn_override(v_assignment_id);
2184: FETCH csr_ppsn_override INTO l_ppsn_override;
2177: CLOSE get_assignment_id;
2178:
2179: hr_utility.set_location('v_payroll_action_id..'||v_payroll_action_id,1000);
2180: hr_utility.set_location('v_assignment_id..'||v_assignment_id,1000);
2181: hr_utility.set_location('v_chunk..'||v_chunk,1000);
2182: --6633719
2183: OPEN csr_ppsn_override(v_assignment_id);
2184: FETCH csr_ppsn_override INTO l_ppsn_override;
2185: CLOSE csr_ppsn_override;
2182: --6633719
2183: OPEN csr_ppsn_override(v_assignment_id);
2184: FETCH csr_ppsn_override INTO l_ppsn_override;
2185: CLOSE csr_ppsn_override;
2186: hr_utility.set_location('l_ppsn_override..'||l_ppsn_override,1000);
2187:
2188: IF l_ppsn_override IS NOT NULL THEN
2189: l_dimension_name := '_PER_PAYE_REF_PPSN_YTD';
2190: ELSE
2190: ELSE
2191: l_dimension_name := '_PER_PAYE_REF_YTD';
2192: END IF;
2193:
2194: hr_utility.set_location('l_dimension_name..'||l_dimension_name,1000);
2195:
2196: /* -- commented as new dimension ('_PER_PAYE_REF_PPSN_YTD') does not require contexes.
2197: IF l_ppsn_override IS NOT NULL THEN
2198: OPEN csr_child_actions(v_assignment_id);
2197: IF l_ppsn_override IS NOT NULL THEN
2198: OPEN csr_child_actions(v_assignment_id);
2199: FETCH csr_child_actions INTO l_child_assignment_action_id;
2200: CLOSE csr_child_actions;
2201: hr_utility.set_location('l_child_assignment_action_id..'||l_child_assignment_action_id,1000);
2202:
2203: IF l_child_assignment_action_id IS NOT NULL THEN
2204: OPEN Cur_Act_Contexts(l_child_assignment_action_id);
2205: FETCH Cur_Act_Contexts INTO l_Act_Context_id,l_Act_Context_value;
2203: IF l_child_assignment_action_id IS NOT NULL THEN
2204: OPEN Cur_Act_Contexts(l_child_assignment_action_id);
2205: FETCH Cur_Act_Contexts INTO l_Act_Context_id,l_Act_Context_value;
2206: CLOSE Cur_Act_Contexts;
2207: hr_utility.set_location('l_Act_Context_id..'||l_Act_Context_id,1000);
2208: hr_utility.set_location('l_Act_Context_value..'||l_Act_Context_value,1000);
2209: END IF;
2210:
2211: END IF;
2204: OPEN Cur_Act_Contexts(l_child_assignment_action_id);
2205: FETCH Cur_Act_Contexts INTO l_Act_Context_id,l_Act_Context_value;
2206: CLOSE Cur_Act_Contexts;
2207: hr_utility.set_location('l_Act_Context_id..'||l_Act_Context_id,1000);
2208: hr_utility.set_location('l_Act_Context_value..'||l_Act_Context_value,1000);
2209: END IF;
2210:
2211: END IF;
2212: */
2301: l_arch_pen_empr_prsa := '0';
2302: l_arch_pen_emp_rac := '0';
2303:
2304: cnt := 0;
2305: --hr_utility.trace_on(null,'P35');
2306: hr_utility.set_location('In archive code..'||to_char(cnt),1001);
2307: FOR csr_interlocks IN get_actid_from_interlocks
2308: LOOP
2309: v_action_id := csr_interlocks.locked_action_id;
2302: l_arch_pen_emp_rac := '0';
2303:
2304: cnt := 0;
2305: --hr_utility.trace_on(null,'P35');
2306: hr_utility.set_location('In archive code..'||to_char(cnt),1001);
2307: FOR csr_interlocks IN get_actid_from_interlocks
2308: LOOP
2309: v_action_id := csr_interlocks.locked_action_id;
2310: hr_utility.set_location('csr_interlocks.locked_action_id ..'||csr_interlocks.locked_action_id,1002);
2306: hr_utility.set_location('In archive code..'||to_char(cnt),1001);
2307: FOR csr_interlocks IN get_actid_from_interlocks
2308: LOOP
2309: v_action_id := csr_interlocks.locked_action_id;
2310: hr_utility.set_location('csr_interlocks.locked_action_id ..'||csr_interlocks.locked_action_id,1002);
2311:
2312:
2313: -- v_action_id is either prepay or P30 action id
2314: OPEN get_action_type(csr_interlocks.locked_action_id);
2314: OPEN get_action_type(csr_interlocks.locked_action_id);
2315: FETCH get_action_type into v_action_id,v_action_type;
2316: CLOSE get_action_type;
2317:
2318: hr_utility.set_location('Inside the loop ..',1002);
2319: hr_utility.set_location('Action Type ..'||v_action_type,1003);
2320: hr_utility.set_location('v_action_id ..'||v_action_id,1003);
2321: --6633719
2322: IF l_ppsn_override IS NOT NULL THEN
2315: FETCH get_action_type into v_action_id,v_action_type;
2316: CLOSE get_action_type;
2317:
2318: hr_utility.set_location('Inside the loop ..',1002);
2319: hr_utility.set_location('Action Type ..'||v_action_type,1003);
2320: hr_utility.set_location('v_action_id ..'||v_action_id,1003);
2321: --6633719
2322: IF l_ppsn_override IS NOT NULL THEN
2323: OPEN csr_rehire_ppsn_arch(v_action_id);
2316: CLOSE get_action_type;
2317:
2318: hr_utility.set_location('Inside the loop ..',1002);
2319: hr_utility.set_location('Action Type ..'||v_action_type,1003);
2320: hr_utility.set_location('v_action_id ..'||v_action_id,1003);
2321: --6633719
2322: IF l_ppsn_override IS NOT NULL THEN
2323: OPEN csr_rehire_ppsn_arch(v_action_id);
2324: FETCH csr_rehire_ppsn_arch INTO l_ppsn_bal_archived;
2329: END IF;
2330: --6633719QA
2331: CLOSE csr_rehire_ppsn_arch;
2332:
2333: hr_utility.set_location('l_ppsn_bal_archived ..'||l_ppsn_bal_archived,1004);
2334: END IF;
2335: --6633719
2336: IF cnt = 0 then
2337: -- This part to executed only once for each locking action id to fetch balances for max assignment actionid
2340: IF v_action_type = 'X' then
2341:
2342: l_max_action_id := csr_interlocks.locked_action_id;
2343:
2344: hr_utility.set_location('l_max_action_id ..'||l_max_action_id,1005);
2345: hr_utility.set_location('Inside IEPS ..'||to_char(l_actid),1005);
2346:
2347: OPEN csr_p30_bal_value (v_action_id, 'IE TAXABLE PAY',l_dimension_name);
2348: FETCH csr_p30_bal_value INTO l_arch_pay;
2341:
2342: l_max_action_id := csr_interlocks.locked_action_id;
2343:
2344: hr_utility.set_location('l_max_action_id ..'||l_max_action_id,1005);
2345: hr_utility.set_location('Inside IEPS ..'||to_char(l_actid),1005);
2346:
2347: OPEN csr_p30_bal_value (v_action_id, 'IE TAXABLE PAY',l_dimension_name);
2348: FETCH csr_p30_bal_value INTO l_arch_pay;
2349: --6633719 if condition added for the scenarios where employee is rehired
2376: 2
2377: )
2378: );
2379:
2380: hr_utility.set_location('l_arch_pay not archived ..'||l_arch_pay,1004);
2381:
2382: END IF;
2383: --6633719
2384: CLOSE csr_p30_bal_value;
2381:
2382: END IF;
2383: --6633719
2384: CLOSE csr_p30_bal_value;
2385: hr_utility.set_location('l_arch_pay..'||l_arch_pay,1005);
2386:
2387: OPEN csr_p30_bal_value (v_action_id, 'IE NET TAX',l_dimension_name);
2388: FETCH csr_p30_bal_value INTO l_arch_net_tax;
2389: --6633719
2413: 2
2414: )
2415: );
2416:
2417: hr_utility.set_location('l_arch_net_tax not archived ..'||l_arch_net_tax,1004);
2418:
2419: END IF;
2420: --6633719
2421: CLOSE csr_p30_bal_value;
2418:
2419: END IF;
2420: --6633719
2421: CLOSE csr_p30_bal_value;
2422: hr_utility.set_location('l_arch_net_tax..'||l_arch_net_tax,1005);
2423:
2424: OPEN csr_p30_bal_value (v_action_id, 'IE PRSI EMPLOYEE',l_dimension_name);
2425: FETCH csr_p30_bal_value INTO l_arch_employees_prsi_cont;
2426: --6633719
2450: 2
2451: )
2452: );
2453:
2454: hr_utility.set_location('l_arch_employees_prsi_cont not archived ..'||l_arch_employees_prsi_cont,1004);
2455:
2456: END IF;
2457: --6633719
2458: CLOSE csr_p30_bal_value;
2455:
2456: END IF;
2457: --6633719
2458: CLOSE csr_p30_bal_value;
2459: hr_utility.set_location('l_arch_employees_prsi_cont..'||l_arch_employees_prsi_cont,1005);
2460:
2461: --Bug 4553755
2462: /*OPEN csr_p30_bal_value (v_action_id, 'IE BIK TAXABLE AND PRSIABLE PAY');
2463: FETCH csr_p30_bal_value INTO l_arch_total_notional_pay;
2493: 2
2494: )
2495: );
2496:
2497: hr_utility.set_location('l_temp_prsi_cont not archived ..'||l_temp_prsi_cont,1004);
2498:
2499: END IF;
2500: --6633719
2501: CLOSE csr_p30_bal_value;
2498:
2499: END IF;
2500: --6633719
2501: CLOSE csr_p30_bal_value;
2502: hr_utility.set_location('IE PRSI K EMPLOYEE LUMP SUM l_temp_prsi_cont..'||l_temp_prsi_cont,1005);
2503:
2504: l_arch_employees_prsi_cont :=
2505: TO_CHAR (
2506: TO_NUMBER (l_arch_employees_prsi_cont)
2506: TO_NUMBER (l_arch_employees_prsi_cont)
2507: + TO_NUMBER (NVL (l_temp_prsi_cont, '0'))
2508: );
2509: l_temp_prsi_cont := NULL;
2510: hr_utility.set_location('IE PRSI K EMPLOYEE LUMP SUM l_arch_employees_prsi_cont..'||l_arch_employees_prsi_cont,1005);
2511:
2512: OPEN csr_p30_bal_value (v_action_id,'IE PRSI M EMPLOYEE LUMP SUM',l_dimension_name);
2513: FETCH csr_p30_bal_value INTO l_temp_prsi_cont;
2514: --6633719
2538: 2
2539: )
2540: );
2541:
2542: hr_utility.set_location('l_temp_prsi_cont not archived ..'||l_temp_prsi_cont,1004);
2543:
2544: END IF;
2545: --6633719
2546: CLOSE csr_p30_bal_value;
2543:
2544: END IF;
2545: --6633719
2546: CLOSE csr_p30_bal_value;
2547: hr_utility.set_location('IE PRSI M EMPLOYEE LUMP SUM l_temp_prsi_cont..'||l_temp_prsi_cont,1005);
2548:
2549: l_arch_employees_prsi_cont :=
2550: TO_CHAR (
2551: TO_NUMBER (l_arch_employees_prsi_cont)
2551: TO_NUMBER (l_arch_employees_prsi_cont)
2552: + TO_NUMBER (NVL (l_temp_prsi_cont, '0'))
2553: );
2554: l_temp_prsi_cont := NULL;
2555: hr_utility.set_location('E PRSI M EMPLOYEE LUMP SUM l_arch_employees_prsi_cont..'||l_arch_employees_prsi_cont,1005);
2556:
2557: OPEN csr_p30_bal_value (v_action_id, 'IE PRSI EMPLOYER',l_dimension_name);
2558: FETCH csr_p30_bal_value INTO l_arch_employer_prsi_cont;
2559: --6633719
2583: 2
2584: )
2585: );
2586:
2587: hr_utility.set_location('l_arch_employer_prsi_cont not archived ..'||l_arch_employer_prsi_cont,1004);
2588:
2589: END IF;
2590: --6633719
2591: CLOSE csr_p30_bal_value;
2588:
2589: END IF;
2590: --6633719
2591: CLOSE csr_p30_bal_value;
2592: hr_utility.set_location('IE PRSI EMPLOYER l_arch_employer_prsi_cont..'||l_arch_employer_prsi_cont,1005);
2593:
2594: -- Added K and M Employer figuresin case of severance payment
2595: OPEN csr_p30_bal_value (v_action_id,'IE PRSI K EMPLOYER LUMP SUM',l_dimension_name);
2596: FETCH csr_p30_bal_value INTO l_temp_prsi_cont;
2621: 2
2622: )
2623: );
2624:
2625: hr_utility.set_location('l_temp_prsi_cont not archived ..'||l_temp_prsi_cont,1004);
2626:
2627: END IF;
2628: --6633719
2629: CLOSE csr_p30_bal_value;
2626:
2627: END IF;
2628: --6633719
2629: CLOSE csr_p30_bal_value;
2630: hr_utility.set_location('IE PRSI K EMPLOYER LUMP SUM l_temp_prsi_cont..'||l_temp_prsi_cont,1005);
2631:
2632: l_arch_employer_prsi_cont :=
2633: TO_CHAR (
2634: TO_NUMBER (l_arch_employer_prsi_cont)
2634: TO_NUMBER (l_arch_employer_prsi_cont)
2635: + TO_NUMBER (NVL (l_temp_prsi_cont, '0'))
2636: );
2637: l_temp_prsi_cont := NULL;
2638: hr_utility.set_location('IE PRSI K EMPLOYER LUMP SUM l_arch_employer_prsi_cont..'||l_arch_employer_prsi_cont,1005);
2639:
2640: OPEN csr_p30_bal_value (v_action_id,'IE PRSI M EMPLOYER LUMP SUM',l_dimension_name);
2641: FETCH csr_p30_bal_value INTO l_temp_prsi_cont;
2642: --6633719
2666: 2
2667: )
2668: );
2669:
2670: hr_utility.set_location('l_temp_prsi_cont not archived ..'||l_temp_prsi_cont,1004);
2671:
2672: END IF;
2673: --6633719
2674: CLOSE csr_p30_bal_value;
2671:
2672: END IF;
2673: --6633719
2674: CLOSE csr_p30_bal_value;
2675: hr_utility.set_location('IE PRSI M EMPLOYER LUMP SUM l_temp_prsi_cont..'||l_temp_prsi_cont,1005);
2676:
2677: l_arch_employer_prsi_cont :=
2678: TO_CHAR (
2679: TO_NUMBER (l_arch_employer_prsi_cont)
2684: TO_CHAR (
2685: TO_NUMBER (NVL (l_arch_employees_prsi_cont, 0))
2686: + TO_NUMBER (NVL (l_arch_employer_prsi_cont, 0))
2687: );
2688: hr_utility.set_location('l_arch_total_prsi_cont..'||l_arch_total_prsi_cont,1005);
2689: --Bug 4553755
2690: -- Passed Payroll Run assignment action id csr_interlocks.locked_action_id 4672715
2691:
2692: l_arch_total_notional_pay :=
2712: ),
2713: 2
2714: )
2715: );
2716: hr_utility.set_location('l_arch_total_notional_pay..'||l_arch_total_notional_pay,1005);
2717: -- Passed Payroll Run assignment action id csr_interlocks.locked_action_id 4672715
2718: /* Start Pension Balances */
2719: l_arch_pen_emp_rbs :=
2720: TO_CHAR (
2761: ),
2762: 2
2763: )
2764: );
2765: hr_utility.set_location('l_arch_pen_emp_rbs..'||l_arch_pen_emp_rbs,1005);
2766: l_arch_pen_empr_rbs :=
2767: TO_CHAR (
2768: ROUND (
2769: NVL (
2786: ),
2787: 2
2788: )
2789: );
2790: hr_utility.set_location('l_arch_pen_empr_rbs..'||l_arch_pen_empr_rbs,1005);
2791: l_arch_pen_emp_prsa :=
2792: TO_CHAR (
2793: ROUND (
2794: NVL (
2833: ),
2834: 2
2835: )
2836: );
2837: hr_utility.set_location('l_arch_pen_emp_prsa..'||l_arch_pen_emp_prsa,1005);
2838: l_arch_pen_empr_prsa :=
2839: TO_CHAR (
2840: ROUND (
2841: NVL (
2858: ),
2859: 2
2860: )
2861: );
2862: hr_utility.set_location('l_arch_pen_empr_prsa..'||l_arch_pen_empr_prsa,1005);
2863: l_arch_pen_emp_rac :=
2864: TO_CHAR (
2865: ROUND (
2866: NVL (
2884: 2
2885: )
2886: );
2887: /* End Pension Balances */
2888: hr_utility.set_location('l_arch_pen_emp_rac..'||l_arch_pen_emp_rac,1005);
2889: -- BIK Medical insurance 5867343
2890: l_medical_insurance :=
2891: TO_CHAR (
2892: ROUND (
2910: ),
2911: 2
2912: )
2913: );
2914: hr_utility.set_location('l_medical_insurance..'||l_medical_insurance,1005);
2915: /*Bug No. 4063502*/
2916: IF nvl(l_arch_pay,0) = 0 THEN -- if IE TAXABLE PAY is 0
2917: hr_utility.set_location('Inside If l_arch_non_tax_pay..'||l_arch_non_tax_pay,1005);
2918: l_arch_non_tax_pay :=
2913: );
2914: hr_utility.set_location('l_medical_insurance..'||l_medical_insurance,1005);
2915: /*Bug No. 4063502*/
2916: IF nvl(l_arch_pay,0) = 0 THEN -- if IE TAXABLE PAY is 0
2917: hr_utility.set_location('Inside If l_arch_non_tax_pay..'||l_arch_non_tax_pay,1005);
2918: l_arch_non_tax_pay :=
2919: TO_CHAR (
2920: ROUND (
2921: NVL (
2938: ),
2939: 2
2940: )
2941: );
2942: hr_utility.set_location('l_arch_non_tax_pay..'||l_arch_non_tax_pay,1005);
2943: END IF;
2944:
2945:
2946: SELECT DECODE (
2952: INTO l_arch_tax_or_refund
2953: FROM DUAL;
2954:
2955:
2956: hr_utility.set_location('l_arch_tax_or_refund..'||l_arch_tax_or_refund,1005);
2957: hr_utility.set_location('b4 cursor csr_p30_found..',1005);
2958:
2959: --
2960: -- BUG 3306202 Added cursor CSR_P30_PERSON
2953: FROM DUAL;
2954:
2955:
2956: hr_utility.set_location('l_arch_tax_or_refund..'||l_arch_tax_or_refund,1005);
2957: hr_utility.set_location('b4 cursor csr_p30_found..',1005);
2958:
2959: --
2960: -- BUG 3306202 Added cursor CSR_P30_PERSON
2961: OPEN csr_p30_found (v_action_id, l_arch_net_tax,csr_interlocks.locked_action_id);
2982: l_arch_previous_emp_pay,
2983: l_arch_previous_emp_tax;
2984: CLOSE csr_p30_found;
2985: /* lock this v_action_id*/
2986: hr_utility.set_location('after cursor csr_p30_found..',1005);
2987: hr_utility.set_location('l_arch_works_number..'||l_arch_works_number,1005);
2988: hr_utility.set_location('l_arch_ppsn..'||l_arch_ppsn,1005);
2989: hr_utility.set_location('l_arch_primary_flag..'||l_arch_primary_flag,1005);
2990: hr_utility.set_location('l_arch_assignment_id..'||l_arch_assignment_id,1005);
2983: l_arch_previous_emp_tax;
2984: CLOSE csr_p30_found;
2985: /* lock this v_action_id*/
2986: hr_utility.set_location('after cursor csr_p30_found..',1005);
2987: hr_utility.set_location('l_arch_works_number..'||l_arch_works_number,1005);
2988: hr_utility.set_location('l_arch_ppsn..'||l_arch_ppsn,1005);
2989: hr_utility.set_location('l_arch_primary_flag..'||l_arch_primary_flag,1005);
2990: hr_utility.set_location('l_arch_assignment_id..'||l_arch_assignment_id,1005);
2991:
2984: CLOSE csr_p30_found;
2985: /* lock this v_action_id*/
2986: hr_utility.set_location('after cursor csr_p30_found..',1005);
2987: hr_utility.set_location('l_arch_works_number..'||l_arch_works_number,1005);
2988: hr_utility.set_location('l_arch_ppsn..'||l_arch_ppsn,1005);
2989: hr_utility.set_location('l_arch_primary_flag..'||l_arch_primary_flag,1005);
2990: hr_utility.set_location('l_arch_assignment_id..'||l_arch_assignment_id,1005);
2991:
2992: ELSIF v_action_type in ('P','U') THEN
2985: /* lock this v_action_id*/
2986: hr_utility.set_location('after cursor csr_p30_found..',1005);
2987: hr_utility.set_location('l_arch_works_number..'||l_arch_works_number,1005);
2988: hr_utility.set_location('l_arch_ppsn..'||l_arch_ppsn,1005);
2989: hr_utility.set_location('l_arch_primary_flag..'||l_arch_primary_flag,1005);
2990: hr_utility.set_location('l_arch_assignment_id..'||l_arch_assignment_id,1005);
2991:
2992: ELSIF v_action_type in ('P','U') THEN
2993: hr_utility.set_location('Inside P,U ..'||to_char(l_actid),1006);
2986: hr_utility.set_location('after cursor csr_p30_found..',1005);
2987: hr_utility.set_location('l_arch_works_number..'||l_arch_works_number,1005);
2988: hr_utility.set_location('l_arch_ppsn..'||l_arch_ppsn,1005);
2989: hr_utility.set_location('l_arch_primary_flag..'||l_arch_primary_flag,1005);
2990: hr_utility.set_location('l_arch_assignment_id..'||l_arch_assignment_id,1005);
2991:
2992: ELSIF v_action_type in ('P','U') THEN
2993: hr_utility.set_location('Inside P,U ..'||to_char(l_actid),1006);
2994: l_max_action_id := csr_interlocks.locked_action_id;
2989: hr_utility.set_location('l_arch_primary_flag..'||l_arch_primary_flag,1005);
2990: hr_utility.set_location('l_arch_assignment_id..'||l_arch_assignment_id,1005);
2991:
2992: ELSIF v_action_type in ('P','U') THEN
2993: hr_utility.set_location('Inside P,U ..'||to_char(l_actid),1006);
2994: l_max_action_id := csr_interlocks.locked_action_id;
2995: hr_utility.set_location('l_max_action_id..'||l_max_action_id,1006);
2996: hr_utility.set_location('v_action_id..'||v_action_id,1006);
2997: hr_utility.set_location('B4 cursor csr_prepay_found..',1006);
2991:
2992: ELSIF v_action_type in ('P','U') THEN
2993: hr_utility.set_location('Inside P,U ..'||to_char(l_actid),1006);
2994: l_max_action_id := csr_interlocks.locked_action_id;
2995: hr_utility.set_location('l_max_action_id..'||l_max_action_id,1006);
2996: hr_utility.set_location('v_action_id..'||v_action_id,1006);
2997: hr_utility.set_location('B4 cursor csr_prepay_found..',1006);
2998:
2999: OPEN csr_prepay_found (v_action_id,
2992: ELSIF v_action_type in ('P','U') THEN
2993: hr_utility.set_location('Inside P,U ..'||to_char(l_actid),1006);
2994: l_max_action_id := csr_interlocks.locked_action_id;
2995: hr_utility.set_location('l_max_action_id..'||l_max_action_id,1006);
2996: hr_utility.set_location('v_action_id..'||v_action_id,1006);
2997: hr_utility.set_location('B4 cursor csr_prepay_found..',1006);
2998:
2999: OPEN csr_prepay_found (v_action_id,
3000: csr_interlocks.locked_action_id,
2993: hr_utility.set_location('Inside P,U ..'||to_char(l_actid),1006);
2994: l_max_action_id := csr_interlocks.locked_action_id;
2995: hr_utility.set_location('l_max_action_id..'||l_max_action_id,1006);
2996: hr_utility.set_location('v_action_id..'||v_action_id,1006);
2997: hr_utility.set_location('B4 cursor csr_prepay_found..',1006);
2998:
2999: OPEN csr_prepay_found (v_action_id,
3000: csr_interlocks.locked_action_id,
3001: l_dimension_name,
3024: l_arch_pr_indicator,
3025: l_arch_previous_emp_pay,
3026: l_arch_previous_emp_tax;
3027: CLOSE csr_prepay_found;
3028: hr_utility.set_location('After cursor csr_prepay_found..',1006);
3029: hr_utility.set_location('l_arch_works_number..'||l_arch_works_number,1006);
3030: hr_utility.set_location('l_arch_ppsn..'||l_arch_ppsn,1006);
3031: hr_utility.set_location('l_arch_primary_flag..'||l_arch_primary_flag,1006);
3032: hr_utility.set_location('l_arch_assignment_id..'||l_arch_assignment_id,1006);
3025: l_arch_previous_emp_pay,
3026: l_arch_previous_emp_tax;
3027: CLOSE csr_prepay_found;
3028: hr_utility.set_location('After cursor csr_prepay_found..',1006);
3029: hr_utility.set_location('l_arch_works_number..'||l_arch_works_number,1006);
3030: hr_utility.set_location('l_arch_ppsn..'||l_arch_ppsn,1006);
3031: hr_utility.set_location('l_arch_primary_flag..'||l_arch_primary_flag,1006);
3032: hr_utility.set_location('l_arch_assignment_id..'||l_arch_assignment_id,1006);
3033:
3026: l_arch_previous_emp_tax;
3027: CLOSE csr_prepay_found;
3028: hr_utility.set_location('After cursor csr_prepay_found..',1006);
3029: hr_utility.set_location('l_arch_works_number..'||l_arch_works_number,1006);
3030: hr_utility.set_location('l_arch_ppsn..'||l_arch_ppsn,1006);
3031: hr_utility.set_location('l_arch_primary_flag..'||l_arch_primary_flag,1006);
3032: hr_utility.set_location('l_arch_assignment_id..'||l_arch_assignment_id,1006);
3033:
3034:
3027: CLOSE csr_prepay_found;
3028: hr_utility.set_location('After cursor csr_prepay_found..',1006);
3029: hr_utility.set_location('l_arch_works_number..'||l_arch_works_number,1006);
3030: hr_utility.set_location('l_arch_ppsn..'||l_arch_ppsn,1006);
3031: hr_utility.set_location('l_arch_primary_flag..'||l_arch_primary_flag,1006);
3032: hr_utility.set_location('l_arch_assignment_id..'||l_arch_assignment_id,1006);
3033:
3034:
3035: /* if data found in the prepayment cursor then call the below functions to retrieve the remaining data for archiving */
3028: hr_utility.set_location('After cursor csr_prepay_found..',1006);
3029: hr_utility.set_location('l_arch_works_number..'||l_arch_works_number,1006);
3030: hr_utility.set_location('l_arch_ppsn..'||l_arch_ppsn,1006);
3031: hr_utility.set_location('l_arch_primary_flag..'||l_arch_primary_flag,1006);
3032: hr_utility.set_location('l_arch_assignment_id..'||l_arch_assignment_id,1006);
3033:
3034:
3035: /* if data found in the prepayment cursor then call the below functions to retrieve the remaining data for archiving */
3036: -- Added K and M Employee and Employer figures for severance payment to variables l_arch_employees_prsi_cont and l_arch_total_prsi_cont
3058: ),
3059: 2
3060: )
3061: );
3062: hr_utility.set_location('l_arch_net_tax..'||l_arch_net_tax,1006);
3063: l_arch_employees_prsi_cont :=
3064: TO_CHAR (
3065: ROUND (
3066: NVL (
3119: ),
3120: 2
3121: )
3122: );
3123: hr_utility.set_location('l_arch_employees_prsi_cont..'||l_arch_employees_prsi_cont,1006);
3124: l_arch_total_prsi_cont :=
3125: TO_CHAR (
3126: ROUND (
3127: NVL (
3234: ),
3235: 2
3236: )
3237: );
3238: hr_utility.set_location('l_arch_total_prsi_cont..'||l_arch_total_prsi_cont,1006);
3239: l_arch_pay :=
3240: TO_CHAR (
3241: ROUND (
3242: NVL (
3259: ),
3260: 2
3261: )
3262: );
3263: hr_utility.set_location('l_arch_pay..'||l_arch_pay,1006);
3264: l_arch_total_notional_pay :=
3265: TO_CHAR (
3266: ROUND (
3267: NVL (
3284: ),
3285: 2
3286: )
3287: );
3288: hr_utility.set_location('l_arch_total_notional_pay..'||l_arch_total_notional_pay,1006);
3289: /* Start Pension Balances */
3290: l_arch_pen_emp_rbs :=
3291: TO_CHAR (
3292: ROUND (
3332: ),
3333: 2
3334: )
3335: );
3336: hr_utility.set_location('l_arch_pen_emp_rbs..'||l_arch_pen_emp_rbs,1006);
3337: l_arch_pen_empr_rbs :=
3338: TO_CHAR (
3339: ROUND (
3340: NVL (
3357: ),
3358: 2
3359: )
3360: );
3361: hr_utility.set_location('l_arch_pen_empr_rbs..'||l_arch_pen_empr_rbs,1006);
3362: l_arch_pen_emp_prsa :=
3363: TO_CHAR (
3364: ROUND (
3365: NVL (
3404: ),
3405: 2
3406: )
3407: );
3408: hr_utility.set_location('l_arch_pen_emp_prsa..'||l_arch_pen_emp_prsa,1006);
3409: l_arch_pen_empr_prsa :=
3410: TO_CHAR (
3411: ROUND (
3412: NVL (
3429: ),
3430: 2
3431: )
3432: );
3433: hr_utility.set_location('l_arch_pen_empr_prsa..'||l_arch_pen_empr_prsa,1006);
3434: l_arch_pen_emp_rac :=
3435: TO_CHAR (
3436: ROUND (
3437: NVL (
3454: ),
3455: 2
3456: )
3457: );
3458: hr_utility.set_location('l_arch_pen_emp_rac..'||l_arch_pen_emp_rac,1006);
3459: /* End Pension Balances */
3460: -- BIK Medical insurance 5867343
3461: l_medical_insurance :=
3462: TO_CHAR (
3481: ),
3482: 2
3483: )
3484: );
3485: hr_utility.set_location('l_medical_insurance..'||l_medical_insurance,1006);
3486: /*Bug No. 4063502*/
3487: --
3488: IF nvl(l_arch_pay,0) = 0 THEN
3489: l_arch_non_tax_pay :=
3509: ),
3510: 2
3511: )
3512: );
3513: hr_utility.set_location('l_arch_non_tax_pay..'||l_arch_non_tax_pay,1006);
3514: END IF;
3515: /* lock this v_action_id*/
3516: --
3517: ELSE --v_action_type in ('R','Q','I','B','V') THEN
3514: END IF;
3515: /* lock this v_action_id*/
3516: --
3517: ELSE --v_action_type in ('R','Q','I','B','V') THEN
3518: hr_utility.set_location('Inside R,Q ..'||to_char(l_actid),1007);
3519: -- get data from run-reults
3520: hr_utility.set_location('csr_interlocks.locked_action_id..'||csr_interlocks.locked_action_id,1007);
3521:
3522: OPEN csr_run_results_found (csr_interlocks.locked_action_id,
3516: --
3517: ELSE --v_action_type in ('R','Q','I','B','V') THEN
3518: hr_utility.set_location('Inside R,Q ..'||to_char(l_actid),1007);
3519: -- get data from run-reults
3520: hr_utility.set_location('csr_interlocks.locked_action_id..'||csr_interlocks.locked_action_id,1007);
3521:
3522: OPEN csr_run_results_found (csr_interlocks.locked_action_id,
3523: l_dimension_name,
3524: l_Act_Context_id,
3547: l_arch_previous_emp_pay,
3548: l_arch_previous_emp_tax;
3549: CLOSE csr_run_results_found;
3550:
3551: hr_utility.set_location('After csr_run_results_found..',5001);
3552: hr_utility.set_location('l_arch_works_number..'||l_arch_works_number,5001);
3553: hr_utility.set_location('l_arch_ppsn..'||l_arch_ppsn,5001);
3554: hr_utility.set_location('l_arch_primary_flag..'||l_arch_primary_flag,5001);
3555: hr_utility.set_location('l_arch_assignment_id..'||l_arch_assignment_id,5001);
3548: l_arch_previous_emp_tax;
3549: CLOSE csr_run_results_found;
3550:
3551: hr_utility.set_location('After csr_run_results_found..',5001);
3552: hr_utility.set_location('l_arch_works_number..'||l_arch_works_number,5001);
3553: hr_utility.set_location('l_arch_ppsn..'||l_arch_ppsn,5001);
3554: hr_utility.set_location('l_arch_primary_flag..'||l_arch_primary_flag,5001);
3555: hr_utility.set_location('l_arch_assignment_id..'||l_arch_assignment_id,5001);
3556:
3549: CLOSE csr_run_results_found;
3550:
3551: hr_utility.set_location('After csr_run_results_found..',5001);
3552: hr_utility.set_location('l_arch_works_number..'||l_arch_works_number,5001);
3553: hr_utility.set_location('l_arch_ppsn..'||l_arch_ppsn,5001);
3554: hr_utility.set_location('l_arch_primary_flag..'||l_arch_primary_flag,5001);
3555: hr_utility.set_location('l_arch_assignment_id..'||l_arch_assignment_id,5001);
3556:
3557: hr_utility.set_location('l_arch_previous_emp_pay..'||l_arch_previous_emp_pay,5001);
3550:
3551: hr_utility.set_location('After csr_run_results_found..',5001);
3552: hr_utility.set_location('l_arch_works_number..'||l_arch_works_number,5001);
3553: hr_utility.set_location('l_arch_ppsn..'||l_arch_ppsn,5001);
3554: hr_utility.set_location('l_arch_primary_flag..'||l_arch_primary_flag,5001);
3555: hr_utility.set_location('l_arch_assignment_id..'||l_arch_assignment_id,5001);
3556:
3557: hr_utility.set_location('l_arch_previous_emp_pay..'||l_arch_previous_emp_pay,5001);
3558: /* if data is found in the payroll run results cursor, then calculate the remaining values required for archiving */
3551: hr_utility.set_location('After csr_run_results_found..',5001);
3552: hr_utility.set_location('l_arch_works_number..'||l_arch_works_number,5001);
3553: hr_utility.set_location('l_arch_ppsn..'||l_arch_ppsn,5001);
3554: hr_utility.set_location('l_arch_primary_flag..'||l_arch_primary_flag,5001);
3555: hr_utility.set_location('l_arch_assignment_id..'||l_arch_assignment_id,5001);
3556:
3557: hr_utility.set_location('l_arch_previous_emp_pay..'||l_arch_previous_emp_pay,5001);
3558: /* if data is found in the payroll run results cursor, then calculate the remaining values required for archiving */
3559: -- Added K and M Employee and Employer figures for severance payment to variables l_arch_employees_prsi_cont and l_arch_total_prsi_cont
3553: hr_utility.set_location('l_arch_ppsn..'||l_arch_ppsn,5001);
3554: hr_utility.set_location('l_arch_primary_flag..'||l_arch_primary_flag,5001);
3555: hr_utility.set_location('l_arch_assignment_id..'||l_arch_assignment_id,5001);
3556:
3557: hr_utility.set_location('l_arch_previous_emp_pay..'||l_arch_previous_emp_pay,5001);
3558: /* if data is found in the payroll run results cursor, then calculate the remaining values required for archiving */
3559: -- Added K and M Employee and Employer figures for severance payment to variables l_arch_employees_prsi_cont and l_arch_total_prsi_cont
3560: hr_utility.set_location ('B4 Arch Net Tax getval'|| l_arch_assmt_action_id_bal,996);
3561: l_arch_net_tax :=
3556:
3557: hr_utility.set_location('l_arch_previous_emp_pay..'||l_arch_previous_emp_pay,5001);
3558: /* if data is found in the payroll run results cursor, then calculate the remaining values required for archiving */
3559: -- Added K and M Employee and Employer figures for severance payment to variables l_arch_employees_prsi_cont and l_arch_total_prsi_cont
3560: hr_utility.set_location ('B4 Arch Net Tax getval'|| l_arch_assmt_action_id_bal,996);
3561: l_arch_net_tax :=
3562: TO_CHAR (
3563: ROUND (
3564: NVL (
3581: ),
3582: 2
3583: )
3584: );
3585: hr_utility.set_location ('l_arch_net_tax'|| l_arch_net_tax,997);
3586: l_arch_employees_prsi_cont :=
3587: TO_CHAR (
3588: ROUND (
3589: NVL (
3642: ),
3643: 2
3644: )
3645: );
3646: hr_utility.set_location ('l_arch_employees_prsi_cont'|| l_arch_employees_prsi_cont,997);
3647:
3648: l_arch_total_prsi_cont :=
3649: TO_CHAR (
3650: ROUND (
3758: ),
3759: 2
3760: )
3761: );
3762: hr_utility.set_location ('l_arch_total_prsi_cont'|| l_arch_total_prsi_cont,998);
3763: l_arch_pay :=
3764: TO_CHAR (
3765: ROUND (
3766: NVL (
3783: ),
3784: 2
3785: )
3786: );
3787: hr_utility.set_location ('vik l_arch_assmt_action_id_bal '|| l_arch_assmt_action_id_bal,999);
3788: hr_utility.set_location ('vik l_segment4 '|| l_segment4,999);
3789: hr_utility.set_location ('vik l_arch_pay '|| l_arch_pay,999);
3790:
3791: hr_utility.set_location ('l_arch_total_notional_pay'|| l_arch_assmt_action_id_bal,1990);
3784: 2
3785: )
3786: );
3787: hr_utility.set_location ('vik l_arch_assmt_action_id_bal '|| l_arch_assmt_action_id_bal,999);
3788: hr_utility.set_location ('vik l_segment4 '|| l_segment4,999);
3789: hr_utility.set_location ('vik l_arch_pay '|| l_arch_pay,999);
3790:
3791: hr_utility.set_location ('l_arch_total_notional_pay'|| l_arch_assmt_action_id_bal,1990);
3792: l_arch_total_notional_pay :=
3785: )
3786: );
3787: hr_utility.set_location ('vik l_arch_assmt_action_id_bal '|| l_arch_assmt_action_id_bal,999);
3788: hr_utility.set_location ('vik l_segment4 '|| l_segment4,999);
3789: hr_utility.set_location ('vik l_arch_pay '|| l_arch_pay,999);
3790:
3791: hr_utility.set_location ('l_arch_total_notional_pay'|| l_arch_assmt_action_id_bal,1990);
3792: l_arch_total_notional_pay :=
3793: TO_CHAR (
3787: hr_utility.set_location ('vik l_arch_assmt_action_id_bal '|| l_arch_assmt_action_id_bal,999);
3788: hr_utility.set_location ('vik l_segment4 '|| l_segment4,999);
3789: hr_utility.set_location ('vik l_arch_pay '|| l_arch_pay,999);
3790:
3791: hr_utility.set_location ('l_arch_total_notional_pay'|| l_arch_assmt_action_id_bal,1990);
3792: l_arch_total_notional_pay :=
3793: TO_CHAR (
3794: ROUND (
3795: NVL (
3812: ),
3813: 2
3814: )
3815: );
3816: hr_utility.set_location ('l_arch_total_notional_pay'|| l_arch_total_notional_pay,1990);
3817: /* Start Pension Balances */
3818: l_arch_pen_emp_rbs :=
3819: TO_CHAR (
3820: ROUND (
3860: ),
3861: 2
3862: )
3863: );
3864: hr_utility.set_location ('l_arch_pen_emp_rbs'|| l_arch_pen_emp_rbs,1990);
3865: l_arch_pen_empr_rbs :=
3866: TO_CHAR (
3867: ROUND (
3868: NVL (
3885: ),
3886: 2
3887: )
3888: );
3889: hr_utility.set_location ('l_arch_pen_empr_rbs'|| l_arch_pen_empr_rbs,1990);
3890: l_arch_pen_emp_prsa :=
3891: TO_CHAR (
3892: ROUND (
3893: NVL (
3932: ),
3933: 2
3934: )
3935: );
3936: hr_utility.set_location ('l_arch_pen_emp_prsa'|| l_arch_pen_emp_prsa,1990);
3937: l_arch_pen_empr_prsa :=
3938: TO_CHAR (
3939: ROUND (
3940: NVL (
3957: ),
3958: 2
3959: )
3960: );
3961: hr_utility.set_location ('l_arch_pen_empr_prsa'|| l_arch_pen_empr_prsa,1990);
3962: l_arch_pen_emp_rac :=
3963: TO_CHAR (
3964: ROUND (
3965: NVL (
3982: ),
3983: 2
3984: )
3985: );
3986: hr_utility.set_location ('l_arch_pen_emp_rac'|| l_arch_pen_emp_rac,1990);
3987: /* End Pension Balances */
3988: -- BIK Medical insurance
3989: l_medical_insurance :=
3990: TO_CHAR (
4009: ),
4010: 2
4011: )
4012: );
4013: hr_utility.set_location ('l_medical_insurance'|| l_medical_insurance,1990);
4014: /*Bug No. 4063502*/
4015: IF nvl(l_arch_pay,0) = 0 THEN
4016: hr_utility.set_location ('l_arch_non_tax_pay'|| l_arch_assmt_action_id_bal,1991);
4017: l_arch_non_tax_pay :=
4012: );
4013: hr_utility.set_location ('l_medical_insurance'|| l_medical_insurance,1990);
4014: /*Bug No. 4063502*/
4015: IF nvl(l_arch_pay,0) = 0 THEN
4016: hr_utility.set_location ('l_arch_non_tax_pay'|| l_arch_assmt_action_id_bal,1991);
4017: l_arch_non_tax_pay :=
4018: TO_CHAR (
4019: ROUND (
4020: NVL (
4038: 2
4039: )
4040: );
4041: END IF;
4042: hr_utility.set_location ('l_arch_non_tax_pay'|| l_arch_non_tax_pay,1990);
4043: END IF;
4044: --setting the assignment id to fetch PRSI values
4045: l_max_assignment_id := l_arch_assignment_id;
4046: l_max_action_id := csr_interlocks.locked_action_id;
4046: l_max_action_id := csr_interlocks.locked_action_id;
4047: END IF;
4048: cnt := cnt +1;
4049:
4050: hr_utility.set_location('before csr_get_primary_flag_active.'||v_action_id,1991);
4051: hr_utility.set_location('l_arch_previous_emp_pay.'||l_arch_previous_emp_pay,5001);
4052:
4053: OPEN csr_get_primary_flag_active(v_action_id);
4054: FETCH csr_get_primary_flag_active into v_work_number,v_primary_flag,asg_assignment_id,v_date;
4047: END IF;
4048: cnt := cnt +1;
4049:
4050: hr_utility.set_location('before csr_get_primary_flag_active.'||v_action_id,1991);
4051: hr_utility.set_location('l_arch_previous_emp_pay.'||l_arch_previous_emp_pay,5001);
4052:
4053: OPEN csr_get_primary_flag_active(v_action_id);
4054: FETCH csr_get_primary_flag_active into v_work_number,v_primary_flag,asg_assignment_id,v_date;
4055: IF csr_get_primary_flag_active%NOTFOUND then
4052:
4053: OPEN csr_get_primary_flag_active(v_action_id);
4054: FETCH csr_get_primary_flag_active into v_work_number,v_primary_flag,asg_assignment_id,v_date;
4055: IF csr_get_primary_flag_active%NOTFOUND then
4056: hr_utility.set_location('Inside csr_get_primary_flag_active.'||v_action_id,1992);
4057: OPEN csr_get_primary_flag(v_action_id);
4058: FETCH csr_get_primary_flag into v_work_number,v_primary_flag,asg_assignment_id;
4059: CLOSE csr_get_primary_flag;
4060: hr_utility.set_location('Inside csr_get_primary_flag_active.'||v_action_id,1993);
4056: hr_utility.set_location('Inside csr_get_primary_flag_active.'||v_action_id,1992);
4057: OPEN csr_get_primary_flag(v_action_id);
4058: FETCH csr_get_primary_flag into v_work_number,v_primary_flag,asg_assignment_id;
4059: CLOSE csr_get_primary_flag;
4060: hr_utility.set_location('Inside csr_get_primary_flag_active.'||v_action_id,1993);
4061: END IF;
4062: hr_utility.set_location('CLOSE csr_get_primary_flag_active.'||v_action_id,1994);
4063: CLOSE csr_get_primary_flag_active;
4064:
4058: FETCH csr_get_primary_flag into v_work_number,v_primary_flag,asg_assignment_id;
4059: CLOSE csr_get_primary_flag;
4060: hr_utility.set_location('Inside csr_get_primary_flag_active.'||v_action_id,1993);
4061: END IF;
4062: hr_utility.set_location('CLOSE csr_get_primary_flag_active.'||v_action_id,1994);
4063: CLOSE csr_get_primary_flag_active;
4064:
4065: hr_utility.set_location('v_primary_flag ..'||v_primary_flag,1008);
4066: hr_utility.set_location('l_arch_works_number ..'||l_arch_works_number,1008);
4061: END IF;
4062: hr_utility.set_location('CLOSE csr_get_primary_flag_active.'||v_action_id,1994);
4063: CLOSE csr_get_primary_flag_active;
4064:
4065: hr_utility.set_location('v_primary_flag ..'||v_primary_flag,1008);
4066: hr_utility.set_location('l_arch_works_number ..'||l_arch_works_number,1008);
4067: hr_utility.set_location('v_work_number ..'||v_work_number,1008);
4068:
4069: IF v_primary_flag = 'Y' then
4062: hr_utility.set_location('CLOSE csr_get_primary_flag_active.'||v_action_id,1994);
4063: CLOSE csr_get_primary_flag_active;
4064:
4065: hr_utility.set_location('v_primary_flag ..'||v_primary_flag,1008);
4066: hr_utility.set_location('l_arch_works_number ..'||l_arch_works_number,1008);
4067: hr_utility.set_location('v_work_number ..'||v_work_number,1008);
4068:
4069: IF v_primary_flag = 'Y' then
4070: IF l_ppsn_override IS NULL THEN --6633719
4063: CLOSE csr_get_primary_flag_active;
4064:
4065: hr_utility.set_location('v_primary_flag ..'||v_primary_flag,1008);
4066: hr_utility.set_location('l_arch_works_number ..'||l_arch_works_number,1008);
4067: hr_utility.set_location('v_work_number ..'||v_work_number,1008);
4068:
4069: IF v_primary_flag = 'Y' then
4070: IF l_ppsn_override IS NULL THEN --6633719
4071: l_arch_works_number := v_work_number;
4084: 2
4085: ),
4086: 0
4087: );
4088: hr_utility.set_location('l_arch_previous_emp_pay.'||l_arch_previous_emp_pay,5001);
4089: l_arch_previous_emp_tax := NVL (
4090: ROUND (
4091: TO_NUMBER (
4092: pay_balance_pkg.get_value (
4100: 2
4101: ),
4102: 0
4103: );
4104: hr_utility.set_location('l_arch_previous_emp_tax.'||l_arch_previous_emp_tax,5001);
4105: --EXIT;
4106: --setting the assignment id to fetch PRSI values
4107: --6633719
4108: IF asg_assignment_id <> l_max_assignment_id AND l_ppsn_override IS NULL THEN
4109: l_max_assignment_id := asg_assignment_id;
4110: END IF;
4111: END IF;
4112: --
4113: hr_utility.set_location('l_arch_previous_emp_pay.'||l_arch_previous_emp_pay,5002);
4114: --
4115: IF ( v_action_type not in ('P','U','X') or v_action_type is null) then
4116: v_action_id := csr_interlocks.locked_action_id;
4117: hr_utility.set_location('Inside if ..'||v_action_id,2001);
4113: hr_utility.set_location('l_arch_previous_emp_pay.'||l_arch_previous_emp_pay,5002);
4114: --
4115: IF ( v_action_type not in ('P','U','X') or v_action_type is null) then
4116: v_action_id := csr_interlocks.locked_action_id;
4117: hr_utility.set_location('Inside if ..'||v_action_id,2001);
4118: ELSE
4119: /* create a child assignment action and lock the P30 or prepayment
4120: action. The need for having child assignment action for
4121: retry of pay_payroll action.
4119: /* create a child assignment action and lock the P30 or prepayment
4120: action. The need for having child assignment action for
4121: retry of pay_payroll action.
4122: */
4123: hr_utility.set_location('Inside Else ..',2002);
4124: SELECT pay_assignment_actions_s.NEXTVAL
4125: INTO l_actid
4126: FROM DUAL;
4127: OPEN get_assignment_id;
4134: to_number(l_segment4),
4135: status => 'C',
4136: source_act => p_assactid
4137: );
4138: hr_utility.set_location('Inside Else ..'||v_action_id,2003);
4139: hr_nonrun_asact.insint (l_actid,v_action_id);
4140: END IF;
4141: END LOOP;
4142: -- Calculate values common for the three
4143: --6633719
4144: /* picking the works number of primary assignment for PPSN OVERRIDE*/
4145: IF l_ppsn_override IS NOT NULL THEN
4146:
4147: hr_utility.set_location('before l_arch_works_number ..'||l_arch_works_number,1008);
4148: hr_utility.set_location('before l_ppsn_override ..'||l_ppsn_override,1008);
4149:
4150: OPEN csr_get_primary_flag_active1(l_ppsn_override);
4151: FETCH csr_get_primary_flag_active1 into l_arch_works_number;
4144: /* picking the works number of primary assignment for PPSN OVERRIDE*/
4145: IF l_ppsn_override IS NOT NULL THEN
4146:
4147: hr_utility.set_location('before l_arch_works_number ..'||l_arch_works_number,1008);
4148: hr_utility.set_location('before l_ppsn_override ..'||l_ppsn_override,1008);
4149:
4150: OPEN csr_get_primary_flag_active1(l_ppsn_override);
4151: FETCH csr_get_primary_flag_active1 into l_arch_works_number;
4152: hr_utility.set_location('Inside csr_get_primary_flag_active1.'||v_assignment_id,1992);
4148: hr_utility.set_location('before l_ppsn_override ..'||l_ppsn_override,1008);
4149:
4150: OPEN csr_get_primary_flag_active1(l_ppsn_override);
4151: FETCH csr_get_primary_flag_active1 into l_arch_works_number;
4152: hr_utility.set_location('Inside csr_get_primary_flag_active1.'||v_assignment_id,1992);
4153: IF csr_get_primary_flag_active1%NOTFOUND then
4154: OPEN csr_get_primary_flag1(l_ppsn_override);
4155: FETCH csr_get_primary_flag1 into l_arch_works_number;
4156: CLOSE csr_get_primary_flag1;
4153: IF csr_get_primary_flag_active1%NOTFOUND then
4154: OPEN csr_get_primary_flag1(l_ppsn_override);
4155: FETCH csr_get_primary_flag1 into l_arch_works_number;
4156: CLOSE csr_get_primary_flag1;
4157: hr_utility.set_location('Inside csr_get_primary_flag1.'||v_assignment_id,1993);
4158: END IF;
4159: hr_utility.set_location('CLOSE csr_get_primary_flag_active1.',1994);
4160: CLOSE csr_get_primary_flag_active1;
4161: hr_utility.set_location('after l_arch_works_number ..'||l_arch_works_number,1008);
4155: FETCH csr_get_primary_flag1 into l_arch_works_number;
4156: CLOSE csr_get_primary_flag1;
4157: hr_utility.set_location('Inside csr_get_primary_flag1.'||v_assignment_id,1993);
4158: END IF;
4159: hr_utility.set_location('CLOSE csr_get_primary_flag_active1.',1994);
4160: CLOSE csr_get_primary_flag_active1;
4161: hr_utility.set_location('after l_arch_works_number ..'||l_arch_works_number,1008);
4162: END IF;
4163: --6633719
4157: hr_utility.set_location('Inside csr_get_primary_flag1.'||v_assignment_id,1993);
4158: END IF;
4159: hr_utility.set_location('CLOSE csr_get_primary_flag_active1.',1994);
4160: CLOSE csr_get_primary_flag_active1;
4161: hr_utility.set_location('after l_arch_works_number ..'||l_arch_works_number,1008);
4162: END IF;
4163: --6633719
4164:
4165: hr_utility.set_location ('Starting on other common values', 10);
4161: hr_utility.set_location('after l_arch_works_number ..'||l_arch_works_number,1008);
4162: END IF;
4163: --6633719
4164:
4165: hr_utility.set_location ('Starting on other common values', 10);
4166: l_arch_totwks_insurble_emplmnt :=
4167: TO_CHAR (
4168: NVL (
4169: pay_ie_p35.get_total_insurable_weeks (l_arch_person_id
4175: ,l_ppsn_override), --6633719
4176: 0
4177: )
4178: );
4179: hr_utility.set_location('l_arch_totwks_insurble_emplmnt.'||l_arch_totwks_insurble_emplmnt,5001);
4180: --
4181:
4182: l_arch_initial_class :=
4183: NVL (pay_ie_p35.get_initial_class (l_max_action_id,to_number(l_segment4),l_ppsn_override), ' '); --6633719
4187: l_arch_initial_class := NULL;
4188: END if;
4189: /*Bug fix 4049920*/
4190: IF l_arch_term_date <> '31-12-4712' THEN
4191: hr_utility.set_location ( 'Actual Termination date -'
4192: || l_arch_term_date, 15);
4193: OPEN csr_annual_tax_credit(l_max_assignment_id,TO_DATE(l_arch_term_date,'dd-mm-yyyy'));
4194: FETCH csr_annual_tax_credit INTO l_arch_tax_deduction_basis,l_arch_annual_tax_credit;
4195: CLOSE csr_annual_tax_credit;
4198: FETCH csr_paye_details INTO l_arch_tax_deduction_basis,l_arch_annual_tax_credit;
4199: CLOSE csr_paye_details;
4200:
4201: END IF;
4202: hr_utility.set_location ('Termination date -'|| l_arch_term_date, 15);
4203:
4204: hr_utility.set_location('Before initial Class',101);
4205: hr_utility.set_location('initial Class..'||nvl(l_arch_initial_class,'A'),101);
4206:
4200:
4201: END IF;
4202: hr_utility.set_location ('Termination date -'|| l_arch_term_date, 15);
4203:
4204: hr_utility.set_location('Before initial Class',101);
4205: hr_utility.set_location('initial Class..'||nvl(l_arch_initial_class,'A'),101);
4206:
4207: -- diaplay classes only if the total pay has value.
4208: IF l_arch_pay <> 0 THEN
4201: END IF;
4202: hr_utility.set_location ('Termination date -'|| l_arch_term_date, 15);
4203:
4204: hr_utility.set_location('Before initial Class',101);
4205: hr_utility.set_location('initial Class..'||nvl(l_arch_initial_class,'A'),101);
4206:
4207: -- diaplay classes only if the total pay has value.
4208: IF l_arch_pay <> 0 THEN
4209: --l_prsi_class_tab(1).prsi_class := l_arch_initial_class;
4208: IF l_arch_pay <> 0 THEN
4209: --l_prsi_class_tab(1).prsi_class := l_arch_initial_class;
4210: --l_prsi_class_tab(1).prsi_class_bal := get_prsi_weeks(t_context_value(1),
4211: -- to_number(l_segment4)); -- some dummy value
4212: hr_utility.set_location('vik t_context_value.COUNT..'||t_context_value.COUNT,100);
4213: FOR j in 1..t_context_value.COUNT
4214: LOOP
4215: l_prsi_class_tab(j).prsi_class := substr(t_context_value(j),4,2);
4216: l_prsi_class_tab(j).prsi_class_bal := get_prsi_weeks(t_context_value(j),
4216: l_prsi_class_tab(j).prsi_class_bal := get_prsi_weeks(t_context_value(j),
4217: to_number(l_segment4));
4218: END LOOP;
4219: END IF;
4220: hr_utility.set_location('l_arch_previous_emp_pay..'||l_arch_previous_emp_pay,5002.4);
4221: hr_utility.set_location('initial Class..'||nvl(l_arch_initial_class,'B'),102);
4222:
4223: /* if for eg second class and third class exists but weeks at second class is zero
4224: then third class will become the second class ie it is promoted */
4217: to_number(l_segment4));
4218: END LOOP;
4219: END IF;
4220: hr_utility.set_location('l_arch_previous_emp_pay..'||l_arch_previous_emp_pay,5002.4);
4221: hr_utility.set_location('initial Class..'||nvl(l_arch_initial_class,'B'),102);
4222:
4223: /* if for eg second class and third class exists but weeks at second class is zero
4224: then third class will become the second class ie it is promoted */
4225: hr_utility.set_location('Before initial Class',101);
4221: hr_utility.set_location('initial Class..'||nvl(l_arch_initial_class,'B'),102);
4222:
4223: /* if for eg second class and third class exists but weeks at second class is zero
4224: then third class will become the second class ie it is promoted */
4225: hr_utility.set_location('Before initial Class',101);
4226: hr_utility.set_location('initial Class..'||nvl(l_arch_initial_class,'Y'),101);
4227: hr_utility.set_location('vik l_prsi_class_tab.COUNT..'||l_prsi_class_tab.COUNT,102);
4228:
4229: --Collect all PRSI classes with non-zero PRSI weeks in a separate PL/SQL table
4222:
4223: /* if for eg second class and third class exists but weeks at second class is zero
4224: then third class will become the second class ie it is promoted */
4225: hr_utility.set_location('Before initial Class',101);
4226: hr_utility.set_location('initial Class..'||nvl(l_arch_initial_class,'Y'),101);
4227: hr_utility.set_location('vik l_prsi_class_tab.COUNT..'||l_prsi_class_tab.COUNT,102);
4228:
4229: --Collect all PRSI classes with non-zero PRSI weeks in a separate PL/SQL table
4230: l_cnt :=0;
4223: /* if for eg second class and third class exists but weeks at second class is zero
4224: then third class will become the second class ie it is promoted */
4225: hr_utility.set_location('Before initial Class',101);
4226: hr_utility.set_location('initial Class..'||nvl(l_arch_initial_class,'Y'),101);
4227: hr_utility.set_location('vik l_prsi_class_tab.COUNT..'||l_prsi_class_tab.COUNT,102);
4228:
4229: --Collect all PRSI classes with non-zero PRSI weeks in a separate PL/SQL table
4230: l_cnt :=0;
4231: FOR i in 1..l_prsi_class_tab.COUNT
4234: IF (l_prsi_class_tab(i).prsi_class_bal<>0 or l_arch_total_prsi_cont > 0) and l_prsi_class_tab(i).prsi_class <> 'A' then
4235: l_cnt := l_cnt + 1;
4236: l_prsi_class_bal(l_cnt).prsi_class := l_prsi_class_tab(i).prsi_class;
4237: l_prsi_class_bal(l_cnt).prsi_class_bal := l_prsi_class_tab(i).prsi_class_bal;
4238: hr_utility.set_location('l_prsi_class_tab.COUNT'||l_prsi_class_tab.COUNT,101);
4239: hr_utility.set_location('l_prsi_class_bal'||l_cnt||l_prsi_class_tab(i).prsi_class,101);
4240: hr_utility.set_location('l_prsi_class_bal'||l_cnt||l_prsi_class_tab(i).prsi_class_bal,102);
4241: END IF;
4242: END LOOP;
4235: l_cnt := l_cnt + 1;
4236: l_prsi_class_bal(l_cnt).prsi_class := l_prsi_class_tab(i).prsi_class;
4237: l_prsi_class_bal(l_cnt).prsi_class_bal := l_prsi_class_tab(i).prsi_class_bal;
4238: hr_utility.set_location('l_prsi_class_tab.COUNT'||l_prsi_class_tab.COUNT,101);
4239: hr_utility.set_location('l_prsi_class_bal'||l_cnt||l_prsi_class_tab(i).prsi_class,101);
4240: hr_utility.set_location('l_prsi_class_bal'||l_cnt||l_prsi_class_tab(i).prsi_class_bal,102);
4241: END IF;
4242: END LOOP;
4243:
4236: l_prsi_class_bal(l_cnt).prsi_class := l_prsi_class_tab(i).prsi_class;
4237: l_prsi_class_bal(l_cnt).prsi_class_bal := l_prsi_class_tab(i).prsi_class_bal;
4238: hr_utility.set_location('l_prsi_class_tab.COUNT'||l_prsi_class_tab.COUNT,101);
4239: hr_utility.set_location('l_prsi_class_bal'||l_cnt||l_prsi_class_tab(i).prsi_class,101);
4240: hr_utility.set_location('l_prsi_class_bal'||l_cnt||l_prsi_class_tab(i).prsi_class_bal,102);
4241: END IF;
4242: END LOOP;
4243:
4244: hr_utility.set_location('l_arch_previous_emp_pay..'||l_arch_previous_emp_pay,5002.5);
4240: hr_utility.set_location('l_prsi_class_bal'||l_cnt||l_prsi_class_tab(i).prsi_class_bal,102);
4241: END IF;
4242: END LOOP;
4243:
4244: hr_utility.set_location('l_arch_previous_emp_pay..'||l_arch_previous_emp_pay,5002.5);
4245:
4246: -- get the initial ,first , second, third, fourth and fifth from the non zero
4247: -- plsql table.Sine the req is to display only non zero clases
4248: IF l_cnt >=1 then
4258: l_arch_second_class := l_prsi_class_bal(2).prsi_class;
4259: l_arch_weeks_at_second_class := l_prsi_class_bal(2).prsi_class_bal;
4260: end if;
4261: END IF;
4262: hr_utility.set_location('Hi',104);
4263: IF l_cnt >2 then
4264: if l_prsi_class_bal(3).prsi_class is not null then
4265: l_arch_third_class := l_prsi_class_bal(3).prsi_class;
4266: l_arch_weeks_at_third_class := l_prsi_class_bal(3).prsi_class_bal;
4290: l_arch_third_class := NVL(l_arch_third_class,' ');
4291: l_arch_fourth_class := NVL(l_arch_fourth_class,' ');
4292:
4293: /* Date will be locked now. */
4294: hr_utility.set_location ('b4 INSINT Locking ID' ||L_ACTID,994);
4295: hr_utility.set_location ('b4 INSACT Locked ID ' ||l_locked_action,995);
4296:
4297:
4298: hr_utility.set_location ('Asg ID -'|| l_max_assignment_id,14);
4291: l_arch_fourth_class := NVL(l_arch_fourth_class,' ');
4292:
4293: /* Date will be locked now. */
4294: hr_utility.set_location ('b4 INSINT Locking ID' ||L_ACTID,994);
4295: hr_utility.set_location ('b4 INSACT Locked ID ' ||l_locked_action,995);
4296:
4297:
4298: hr_utility.set_location ('Asg ID -'|| l_max_assignment_id,14);
4299: hr_utility.set_location ( 'Asg Act ID -'|| l_locked_action, 15);
4294: hr_utility.set_location ('b4 INSINT Locking ID' ||L_ACTID,994);
4295: hr_utility.set_location ('b4 INSACT Locked ID ' ||l_locked_action,995);
4296:
4297:
4298: hr_utility.set_location ('Asg ID -'|| l_max_assignment_id,14);
4299: hr_utility.set_location ( 'Asg Act ID -'|| l_locked_action, 15);
4300:
4301: -- For BUG Fix 4066315
4302: -- Concatination of employee address line 2 with line 3 and remaining line 3 with line 4
4295: hr_utility.set_location ('b4 INSACT Locked ID ' ||l_locked_action,995);
4296:
4297:
4298: hr_utility.set_location ('Asg ID -'|| l_max_assignment_id,14);
4299: hr_utility.set_location ( 'Asg Act ID -'|| l_locked_action, 15);
4300:
4301: -- For BUG Fix 4066315
4302: -- Concatination of employee address line 2 with line 3 and remaining line 3 with line 4
4303: if (l_arch_address_line3 is not null) then
4320: --IF l_arch_previous_emp_pay <> 0 THEN
4321: OPEN c_get_periods_of_service(l_arch_person_id,l_max_assignment_id);
4322: FETCH c_get_periods_of_service into l_pds_id;
4323: CLOSE c_get_periods_of_service;
4324: hr_utility.set_location('Period of Service id..'||to_char(l_pds_id),3002);
4325: IF l_pds_id IS NOT NULL THEN
4326: /*OPEN c_get_terminated_asg(l_pds_id);
4327: FETCH c_get_terminated_asg into l_asg_id;
4328: CLOSE c_get_terminated_asg;
4330: IF l_asg_id IS NOT NULL THEN*/
4331: OPEN c_get_max_aact(l_pds_id);
4332: FETCH c_get_max_aact INTO l_aact_id;
4333: CLOSE c_get_max_aact;
4334: hr_utility.set_location('l_aact_id..'||to_char(l_aact_id),3003);
4335: END IF;
4336: hr_utility.set_location('l_arch_previous_emp_pay..'||l_arch_previous_emp_pay,5004);
4337: hr_utility.set_location('l_arch_pay..'||l_arch_pay,5004);
4338: hr_utility.set_location('l_aact_id..'||l_aact_id,5004);
4332: FETCH c_get_max_aact INTO l_aact_id;
4333: CLOSE c_get_max_aact;
4334: hr_utility.set_location('l_aact_id..'||to_char(l_aact_id),3003);
4335: END IF;
4336: hr_utility.set_location('l_arch_previous_emp_pay..'||l_arch_previous_emp_pay,5004);
4337: hr_utility.set_location('l_arch_pay..'||l_arch_pay,5004);
4338: hr_utility.set_location('l_aact_id..'||l_aact_id,5004);
4339: IF l_aact_id IS NOT NULL THEN
4340: -- for bug 5435931.
4333: CLOSE c_get_max_aact;
4334: hr_utility.set_location('l_aact_id..'||to_char(l_aact_id),3003);
4335: END IF;
4336: hr_utility.set_location('l_arch_previous_emp_pay..'||l_arch_previous_emp_pay,5004);
4337: hr_utility.set_location('l_arch_pay..'||l_arch_pay,5004);
4338: hr_utility.set_location('l_aact_id..'||l_aact_id,5004);
4339: IF l_aact_id IS NOT NULL THEN
4340: -- for bug 5435931.
4341: l_this_pay := l_arch_pay - pay_balance_pkg.get_value (
4334: hr_utility.set_location('l_aact_id..'||to_char(l_aact_id),3003);
4335: END IF;
4336: hr_utility.set_location('l_arch_previous_emp_pay..'||l_arch_previous_emp_pay,5004);
4337: hr_utility.set_location('l_arch_pay..'||l_arch_pay,5004);
4338: hr_utility.set_location('l_aact_id..'||l_aact_id,5004);
4339: IF l_aact_id IS NOT NULL THEN
4340: -- for bug 5435931.
4341: l_this_pay := l_arch_pay - pay_balance_pkg.get_value (
4342: pay_ie_p35.get_defined_balance_id (
4353: null,
4354: 'TRUE'
4355:
4356: );
4357: hr_utility.set_location('l_this_pay..'||l_this_pay,5004);
4358: l_this_tax := l_arch_net_tax - pay_balance_pkg.get_value (
4359: pay_ie_p35.get_defined_balance_id (
4360: l_dimension_name, --'_PER_PAYE_REF_YTD',--6633719
4361: 'IE Net Tax'
4370: null,
4371: 'TRUE'
4372:
4373: );
4374: hr_utility.set_location('l_this_tax..'||l_this_tax,5004);
4375: -- For P60 changes, PRSI this employment Section C.
4376: get_p60_prsi_details(p_assignment_action_id => l_aact_id,
4377: p_max_action_id => l_max_action_id,
4378: p_person_id => l_arch_person_id,
4391: p_dimension_name => l_dimension_name, --6633719
4392: p_ppsn_override => l_ppsn_override --6633719
4393: );
4394:
4395: hr_utility.set_location('After get_p60_prsi_details call',5004);
4396:
4397: ELSE
4398: -- this case will come say employee joins on 2-jan-2006.
4399: -- Enter P45 details. So in this case pervious emp pay is <> 0
4416: l_this_pay := l_arch_pay;
4417: l_this_tax := l_arch_net_tax;
4418: -- end bug 5435931
4419: END IF;*/-- l_arch_previous_emp_pay <> 0
4420: hr_utility.set_location('l_arch_previous_emp_pay..'||l_arch_previous_emp_pay,5004);
4421: hr_utility.set_location('Before Update',1009);
4422: /* update assignment actions */
4423:
4424: IF v_assignment_id <> l_max_assignment_id THEN
4417: l_this_tax := l_arch_net_tax;
4418: -- end bug 5435931
4419: END IF;*/-- l_arch_previous_emp_pay <> 0
4420: hr_utility.set_location('l_arch_previous_emp_pay..'||l_arch_previous_emp_pay,5004);
4421: hr_utility.set_location('Before Update',1009);
4422: /* update assignment actions */
4423:
4424: IF v_assignment_id <> l_max_assignment_id THEN
4425: UPDATE pay_assignment_actions SET assignment_id = l_max_assignment_id
4430: WHERE source_action_id = p_assactid
4431: AND payroll_action_id = v_payroll_action_id;
4432: END IF;
4433:
4434: hr_utility.set_location('After Update',1009);
4435:
4436: -- print the hiredate only it falls in the current tax year.
4437: IF to_char(to_date(l_arch_hire_date,'DD-MM-YYYY'),'YYYY') <> to_char(l_start_date,'YYYY') THEN
4438: l_arch_hire_date := null;
4442: IF instr(l_this_initial_class,'-',1) > 0 THEN
4443: l_this_initial_class := substr(l_this_initial_class,1,instr(l_this_initial_class,'-',1)-1);
4444: END IF;
4445:
4446: hr_utility.set_location('Before create_action_information IE P35 DETAIL',1009);
4447:
4448: IF l_arch_pay > 0 --OR l_arch_non_tax_pay > 0 --6620003
4449: THEN
4450:
4447:
4448: IF l_arch_pay > 0 --OR l_arch_non_tax_pay > 0 --6620003
4449: THEN
4450:
4451: hr_utility.set_location('l_arch_works_number..'||l_arch_works_number,1009);
4452: hr_utility.set_location('l_ppsn_override..'||l_ppsn_override,1009);
4453: hr_utility.set_location('l_arch_ppsn..'||l_arch_ppsn,1009);
4454: hr_utility.set_location('l_max_assignment_id..'||l_max_assignment_id,1009);
4455:
4448: IF l_arch_pay > 0 --OR l_arch_non_tax_pay > 0 --6620003
4449: THEN
4450:
4451: hr_utility.set_location('l_arch_works_number..'||l_arch_works_number,1009);
4452: hr_utility.set_location('l_ppsn_override..'||l_ppsn_override,1009);
4453: hr_utility.set_location('l_arch_ppsn..'||l_arch_ppsn,1009);
4454: hr_utility.set_location('l_max_assignment_id..'||l_max_assignment_id,1009);
4455:
4456: pay_action_information_api.create_action_information (
4449: THEN
4450:
4451: hr_utility.set_location('l_arch_works_number..'||l_arch_works_number,1009);
4452: hr_utility.set_location('l_ppsn_override..'||l_ppsn_override,1009);
4453: hr_utility.set_location('l_arch_ppsn..'||l_arch_ppsn,1009);
4454: hr_utility.set_location('l_max_assignment_id..'||l_max_assignment_id,1009);
4455:
4456: pay_action_information_api.create_action_information (
4457: p_action_context_id=> p_assactid,
4450:
4451: hr_utility.set_location('l_arch_works_number..'||l_arch_works_number,1009);
4452: hr_utility.set_location('l_ppsn_override..'||l_ppsn_override,1009);
4453: hr_utility.set_location('l_arch_ppsn..'||l_arch_ppsn,1009);
4454: hr_utility.set_location('l_max_assignment_id..'||l_max_assignment_id,1009);
4455:
4456: pay_action_information_api.create_action_information (
4457: p_action_context_id=> p_assactid,
4458: p_action_context_type=> 'AAP',
4492: p_action_information30=> l_arch_pr_indicator,
4493: p_action_information_id=> l_arch_action_info_id,
4494: p_object_version_number=> l_arch_ovn);
4495:
4496: hr_utility.set_location('Before create_action_information IE P35 ADDITIONAL DETAILS',1009);
4497:
4498: pay_action_information_api.create_action_information (
4499: p_action_context_id=> p_assactid,
4500: p_action_context_type=> 'AAP',
4515: p_action_information17=> to_char(floor(to_number(l_medical_insurance))), -- 5867343,6502227
4516: p_action_information_id=> l_arch_action_info_id,
4517: p_object_version_number=> l_oth_arch_ovn
4518: );
4519: hr_utility.set_location('After create_action_information IE P35 ADDITIONAL DETAILS',1009);
4520:
4521: END IF;
4522: -- Empty the pl/sql tables
4523: l_prsi_class_bal := l_prsi_class_temp;
4959:
4960:
4961: --
4962:
4963: -- hr_utility.TRACE ('In procedure get_initial_class');
4964: -- Fetching the values of context_id, balance_dimension_id, assignment_action_id for furthur
4965: -- usage in the get_weeks_at_class procedures
4966:
4967: OPEN c_context_id;
4988: l_index := l_index + 1;
4989: END LOOP;*/
4990: t_asg_action_id := p_max_action_id;
4991:
4992: hr_utility.set_location ( 'context_id='|| l_context_id, 10);
4993: hr_utility.set_location ( 'l_balance_dimension_id=' || l_defined_balance_id, 20 );
4994: hr_utility.set_location ( 'l_assignment_action_id='|| t_asg_action_id, 30);
4995: -- looping the c_context_name cursor and stroring the first 5 classes for the assignment_id passed into the pl/sql table
4996: hr_utility.set_location ( 'p_person_id_global' || p_person_id_global, 31);
4989: END LOOP;*/
4990: t_asg_action_id := p_max_action_id;
4991:
4992: hr_utility.set_location ( 'context_id='|| l_context_id, 10);
4993: hr_utility.set_location ( 'l_balance_dimension_id=' || l_defined_balance_id, 20 );
4994: hr_utility.set_location ( 'l_assignment_action_id='|| t_asg_action_id, 30);
4995: -- looping the c_context_name cursor and stroring the first 5 classes for the assignment_id passed into the pl/sql table
4996: hr_utility.set_location ( 'p_person_id_global' || p_person_id_global, 31);
4997: hr_utility.set_location ( 'p_start_date' || p_start_date, 32);
4990: t_asg_action_id := p_max_action_id;
4991:
4992: hr_utility.set_location ( 'context_id='|| l_context_id, 10);
4993: hr_utility.set_location ( 'l_balance_dimension_id=' || l_defined_balance_id, 20 );
4994: hr_utility.set_location ( 'l_assignment_action_id='|| t_asg_action_id, 30);
4995: -- looping the c_context_name cursor and stroring the first 5 classes for the assignment_id passed into the pl/sql table
4996: hr_utility.set_location ( 'p_person_id_global' || p_person_id_global, 31);
4997: hr_utility.set_location ( 'p_start_date' || p_start_date, 32);
4998: hr_utility.set_location ( 'p_end_date' || p_end_date, 33);
4992: hr_utility.set_location ( 'context_id='|| l_context_id, 10);
4993: hr_utility.set_location ( 'l_balance_dimension_id=' || l_defined_balance_id, 20 );
4994: hr_utility.set_location ( 'l_assignment_action_id='|| t_asg_action_id, 30);
4995: -- looping the c_context_name cursor and stroring the first 5 classes for the assignment_id passed into the pl/sql table
4996: hr_utility.set_location ( 'p_person_id_global' || p_person_id_global, 31);
4997: hr_utility.set_location ( 'p_start_date' || p_start_date, 32);
4998: hr_utility.set_location ( 'p_end_date' || p_end_date, 33);
4999:
5000: FOR r_c_context_name IN c_context_name (
4993: hr_utility.set_location ( 'l_balance_dimension_id=' || l_defined_balance_id, 20 );
4994: hr_utility.set_location ( 'l_assignment_action_id='|| t_asg_action_id, 30);
4995: -- looping the c_context_name cursor and stroring the first 5 classes for the assignment_id passed into the pl/sql table
4996: hr_utility.set_location ( 'p_person_id_global' || p_person_id_global, 31);
4997: hr_utility.set_location ( 'p_start_date' || p_start_date, 32);
4998: hr_utility.set_location ( 'p_end_date' || p_end_date, 33);
4999:
5000: FOR r_c_context_name IN c_context_name (
5001: p_person_id_global,
4994: hr_utility.set_location ( 'l_assignment_action_id='|| t_asg_action_id, 30);
4995: -- looping the c_context_name cursor and stroring the first 5 classes for the assignment_id passed into the pl/sql table
4996: hr_utility.set_location ( 'p_person_id_global' || p_person_id_global, 31);
4997: hr_utility.set_location ( 'p_start_date' || p_start_date, 32);
4998: hr_utility.set_location ( 'p_end_date' || p_end_date, 33);
4999:
5000: FOR r_c_context_name IN c_context_name (
5001: p_person_id_global,
5002: p_start_date,
5003: p_end_date
5004: )
5005: LOOP
5006:
5007: hr_utility.set_location('Inside c_context_name cursor..',203);
5008: hr_utility.set_location('Inside c_context_name cursor..',203);
5009: if l_context_value_counter = 0 then
5010: l_context_value_counter := l_context_value_counter + 1;
5011: t_context_value(l_context_value_counter) := r_c_context_name.result_value;
5004: )
5005: LOOP
5006:
5007: hr_utility.set_location('Inside c_context_name cursor..',203);
5008: hr_utility.set_location('Inside c_context_name cursor..',203);
5009: if l_context_value_counter = 0 then
5010: l_context_value_counter := l_context_value_counter + 1;
5011: t_context_value(l_context_value_counter) := r_c_context_name.result_value;
5012: hr_utility.set_location ('Initial Class Value is :'|| t_context_value (l_context_value_counter), 16);
5008: hr_utility.set_location('Inside c_context_name cursor..',203);
5009: if l_context_value_counter = 0 then
5010: l_context_value_counter := l_context_value_counter + 1;
5011: t_context_value(l_context_value_counter) := r_c_context_name.result_value;
5012: hr_utility.set_location ('Initial Class Value is :'|| t_context_value (l_context_value_counter), 16);
5013: else
5014: --For j in 1 .. 1
5015: --LOOP
5016: For k in 1 .. l_context_value_counter
5024: END LOOP;
5025: if temp_flag = 0 and l_context_value_counter <=9 then
5026: l_context_value_counter := l_context_value_counter + 1;
5027: t_context_value(l_context_value_counter) := r_c_context_name.result_value;
5028: hr_utility.set_location ('Subsequent Class Value is :'|| t_context_value (l_context_value_counter), 17);
5029: --temp := null;
5030: --exit;
5031: END IF;
5032: temp_flag := null;
5040: LOOP
5041: IF r_c_context_name.result_value <> t_context_value (i) THEN
5042: t_context_value (l_context_value_counter) := r_c_context_name.result_value;
5043: l_context_value_counter := l_context_value_counter + 1;
5044: hr_utility.set_location ('Class Value is :'|| t_context_value (l_context_value_counter), 16);
5045: ELSE
5046: EXIT;
5047: END IF;
5048: END LOOP;
5061: l_context_value_counter_bal := l_context_value_counter_bal
5062: + 1;
5063: t_context_value_balinit (l_context_value_counter_bal) :=
5064: bal_context_name.class_name;
5065: hr_utility.set_location (
5066: 'Class Value for Balance Initialization is :'
5067: || t_context_value_balinit (l_context_value_counter_bal),
5068: 16
5069: );
5106: l_context_value_counter_tmp
5107: + 1;
5108: t_context_value_tmp (l_context_value_counter_tmp) :=
5109: t_context_value (i);
5110: /*hr_utility.TRACE (
5111: 'A Payroll class unmatched with Balance Initialization found'
5112: );*/
5113: END IF;
5114:
5124: -- The below if clause has been added to fix the issue.
5125: IF l_context_value_counter <> 0
5126: THEN
5127: l_initial_class := NVL (t_context_value (1), NULL);
5128: hr_utility.set_location ( 'l_initial_class' || l_initial_class, 50);
5129: --hr_utility.TRACE ('About to leave initial class');
5130: END IF;
5131:
5132: RETURN SUBSTR (l_initial_class, 4, 2);
5125: IF l_context_value_counter <> 0
5126: THEN
5127: l_initial_class := NVL (t_context_value (1), NULL);
5128: hr_utility.set_location ( 'l_initial_class' || l_initial_class, 50);
5129: --hr_utility.TRACE ('About to leave initial class');
5130: END IF;
5131:
5132: RETURN SUBSTR (l_initial_class, 4, 2);
5133: EXCEPTION
5137: -- The below if clause has been added to fix the issue.
5138: -- close c_context_id;
5139: -- close c_assignment_action_id;
5140: t_context_value := t_empty_table;
5141: hr_utility.set_location ('Error in get_total_insurable_weeks', 200);
5142: RAISE;
5143: END get_initial_class;
5144:
5145:
5152: -- Bug 2979713 - PRSI Context Balance Design Change
5153: --
5154: -- fetching the second class value
5155:
5156: -- hr_utility.TRACE ('In procedure get second class');
5157: hr_utility.set_location ( 't_context_value '
5158: || p_assignment_id, 20);
5159:
5160: IF l_context_value_counter >= 2
5153: --
5154: -- fetching the second class value
5155:
5156: -- hr_utility.TRACE ('In procedure get second class');
5157: hr_utility.set_location ( 't_context_value '
5158: || p_assignment_id, 20);
5159:
5160: IF l_context_value_counter >= 2
5161: THEN
5163: ELSE
5164: l_second_class := NULL;
5165: END IF;
5166:
5167: -- hr_utility.TRACE ('About to leave get second class');
5168: RETURN SUBSTR (l_second_class, 4, 2);
5169: END get_second_class;
5170:
5171:
5203: AND SUBSTR (piw3.combined_class, 1, 2) <>
5204: RPAD (l_initial_class, 2));
5205: --
5206: BEGIN
5207: -- hr_utility.TRACE ('In get P60 second class');
5208: /*hr_utility.TRACE ( 'p_assignment_id : '
5209: || TO_CHAR (p_assignment_id));
5210: hr_utility.TRACE (
5211: 'l_class_count_at_second_class : '
5204: RPAD (l_initial_class, 2));
5205: --
5206: BEGIN
5207: -- hr_utility.TRACE ('In get P60 second class');
5208: /*hr_utility.TRACE ( 'p_assignment_id : '
5209: || TO_CHAR (p_assignment_id));
5210: hr_utility.TRACE (
5211: 'l_class_count_at_second_class : '
5212: || TO_CHAR (l_class_count)
5206: BEGIN
5207: -- hr_utility.TRACE ('In get P60 second class');
5208: /*hr_utility.TRACE ( 'p_assignment_id : '
5209: || TO_CHAR (p_assignment_id));
5210: hr_utility.TRACE (
5211: 'l_class_count_at_second_class : '
5212: || TO_CHAR (l_class_count)
5213: );
5214: */
5220: ELSE
5221: l_second_class := ' ';
5222: END IF;
5223:
5224: /*hr_utility.TRACE ( 'l_second_class : '
5225: || l_second_class);
5226: hr_utility.TRACE (
5227: 'l_weeks_at_second_class : '
5228: || TO_CHAR (l_weeks_at_second_class)
5222: END IF;
5223:
5224: /*hr_utility.TRACE ( 'l_second_class : '
5225: || l_second_class);
5226: hr_utility.TRACE (
5227: 'l_weeks_at_second_class : '
5228: || TO_CHAR (l_weeks_at_second_class)
5229: );
5230: hr_utility.TRACE ( 'l_initial_class : '
5226: hr_utility.TRACE (
5227: 'l_weeks_at_second_class : '
5228: || TO_CHAR (l_weeks_at_second_class)
5229: );
5230: hr_utility.TRACE ( 'l_initial_class : '
5231: || l_initial_class);
5232: hr_utility.TRACE ('Leaving get_second_class');*/
5233: RETURN l_second_class;
5234: END get_p60_second_class;
5228: || TO_CHAR (l_weeks_at_second_class)
5229: );
5230: hr_utility.TRACE ( 'l_initial_class : '
5231: || l_initial_class);
5232: hr_utility.TRACE ('Leaving get_second_class');*/
5233: RETURN l_second_class;
5234: END get_p60_second_class;
5235:
5236:
5241: BEGIN
5242: -- Bug 2979713 - PRSI Context Balance Design Change
5243: -- fetching the third class value
5244:
5245: --hr_utility.TRACE ('IN Procedure get third class');
5246:
5247: IF l_context_value_counter >= 3
5248: THEN
5249: l_third_class := NVL (t_context_value (3), NULL);
5250: ELSE
5251: l_third_class := NULL;
5252: END IF;
5253:
5254: --hr_utility.TRACE ('About to leave get third class');
5255: RETURN SUBSTR (l_third_class, 4, 2);
5256: END get_third_class;
5257:
5258:
5265: -- Bug 2979713 - PRSI Context Balance Design Change
5266: --
5267: -- fetching the forth class value
5268:
5269: -- hr_utility.TRACE ('In procedure get fourth class');
5270:
5271: IF l_context_value_counter >= 4
5272: THEN
5273: l_fourth_class := NVL (t_context_value (4), NULL);
5274: ELSE
5275: l_fourth_class := NULL;
5276: END IF;
5277:
5278: -- hr_utility.TRACE ('About to leave get fourth class');
5279: RETURN SUBSTR (l_fourth_class, 4, 2);
5280: END get_fourth_class;
5281:
5282:
5289: -- Bug 2979713 - PRSI Context Balance Design Change
5290: --
5291: -- fetching the fifth class value
5292:
5293: --hr_utility.TRACE ('In procedure get fifith class');
5294:
5295: IF l_context_value_counter >= 5
5296: THEN
5297: l_fifth_class := NVL (t_context_value (5), NULL);
5298: ELSE
5299: l_fifth_class := NULL;
5300: END IF;
5301:
5302: -- hr_utility.TRACE ('About to leave get fifith class');
5303: RETURN SUBSTR (l_fifth_class, 4, 2);
5304: END get_fifth_class;
5305:
5306:
5313:
5314: AS
5315: l_weeks number:=0;
5316: BEGIN
5317: hr_utility.set_location('l_class..'||l_class,11);
5318: hr_utility.set_location('l_defined_balance_id..'||l_defined_balance_id,11);
5319: hr_utility.set_location('t_asg_action_id..'||t_asg_action_id,11);
5320: hr_utility.set_location('l_segment4..'||l_segment4,11);
5321: hr_utility.set_location('l_class..'||l_class,11);
5314: AS
5315: l_weeks number:=0;
5316: BEGIN
5317: hr_utility.set_location('l_class..'||l_class,11);
5318: hr_utility.set_location('l_defined_balance_id..'||l_defined_balance_id,11);
5319: hr_utility.set_location('t_asg_action_id..'||t_asg_action_id,11);
5320: hr_utility.set_location('l_segment4..'||l_segment4,11);
5321: hr_utility.set_location('l_class..'||l_class,11);
5322: hr_utility.set_location('l_context_id..'||l_context_id,11);
5315: l_weeks number:=0;
5316: BEGIN
5317: hr_utility.set_location('l_class..'||l_class,11);
5318: hr_utility.set_location('l_defined_balance_id..'||l_defined_balance_id,11);
5319: hr_utility.set_location('t_asg_action_id..'||t_asg_action_id,11);
5320: hr_utility.set_location('l_segment4..'||l_segment4,11);
5321: hr_utility.set_location('l_class..'||l_class,11);
5322: hr_utility.set_location('l_context_id..'||l_context_id,11);
5323: hr_utility.set_location('l_class..'||l_class,11);
5316: BEGIN
5317: hr_utility.set_location('l_class..'||l_class,11);
5318: hr_utility.set_location('l_defined_balance_id..'||l_defined_balance_id,11);
5319: hr_utility.set_location('t_asg_action_id..'||t_asg_action_id,11);
5320: hr_utility.set_location('l_segment4..'||l_segment4,11);
5321: hr_utility.set_location('l_class..'||l_class,11);
5322: hr_utility.set_location('l_context_id..'||l_context_id,11);
5323: hr_utility.set_location('l_class..'||l_class,11);
5324: hr_utility.set_location('l_class..'||l_class,11);
5317: hr_utility.set_location('l_class..'||l_class,11);
5318: hr_utility.set_location('l_defined_balance_id..'||l_defined_balance_id,11);
5319: hr_utility.set_location('t_asg_action_id..'||t_asg_action_id,11);
5320: hr_utility.set_location('l_segment4..'||l_segment4,11);
5321: hr_utility.set_location('l_class..'||l_class,11);
5322: hr_utility.set_location('l_context_id..'||l_context_id,11);
5323: hr_utility.set_location('l_class..'||l_class,11);
5324: hr_utility.set_location('l_class..'||l_class,11);
5325:
5318: hr_utility.set_location('l_defined_balance_id..'||l_defined_balance_id,11);
5319: hr_utility.set_location('t_asg_action_id..'||t_asg_action_id,11);
5320: hr_utility.set_location('l_segment4..'||l_segment4,11);
5321: hr_utility.set_location('l_class..'||l_class,11);
5322: hr_utility.set_location('l_context_id..'||l_context_id,11);
5323: hr_utility.set_location('l_class..'||l_class,11);
5324: hr_utility.set_location('l_class..'||l_class,11);
5325:
5326:
5319: hr_utility.set_location('t_asg_action_id..'||t_asg_action_id,11);
5320: hr_utility.set_location('l_segment4..'||l_segment4,11);
5321: hr_utility.set_location('l_class..'||l_class,11);
5322: hr_utility.set_location('l_context_id..'||l_context_id,11);
5323: hr_utility.set_location('l_class..'||l_class,11);
5324: hr_utility.set_location('l_class..'||l_class,11);
5325:
5326:
5327: l_weeks := 0;
5320: hr_utility.set_location('l_segment4..'||l_segment4,11);
5321: hr_utility.set_location('l_class..'||l_class,11);
5322: hr_utility.set_location('l_context_id..'||l_context_id,11);
5323: hr_utility.set_location('l_class..'||l_class,11);
5324: hr_utility.set_location('l_class..'||l_class,11);
5325:
5326:
5327: l_weeks := 0;
5328:
5336: l_class,
5337: NULL,
5338: NULL
5339: );
5340: hr_utility.set_location('l_weeks..'||l_weeks,11);
5341: RETURN l_weeks;
5342: END get_prsi_weeks;
5343: /* End of new function */
5344:
5350: BEGIN
5351: --
5352: -- Bug 2979713 - PRSI Context Balance Design Change
5353: --
5354: -- hr_utility.TRACE ('In procedure weeks_at_initial_class');
5355:
5356: IF l_initial_class IS NOT NULL
5357: THEN
5358: --Bug fix 4023794, flusing of variables
5369: l_initial_class,
5370: NULL,
5371: NULL
5372: );
5373: hr_utility.set_location ( 'l_initial_weeks'
5374: || l_initial_weeks, 40);
5375: --END LOOP;
5376: ELSE
5377: l_initial_weeks := 0;
5377: l_initial_weeks := 0;
5378: END IF;
5379:
5380:
5381: -- hr_utility.TRACE ('Leaving weeks_at_initial_class');
5382: RETURN l_initial_weeks;
5383: END weeks_at_initial_class;
5384:
5385:
5391: BEGIN
5392: --
5393: -- Bug 2979713 - PRSI Context Balance Design Change
5394: --
5395: -- hr_utility.TRACE ('In proceduer weeks_at_second_class');
5396:
5397: IF l_second_class IS NOT NULL
5398: THEN -- Bug fix 4004470
5399: l_weeks_at_second_class := 0; --Bug fix 4023794, flusing of variables
5414: --END LOOP;
5415: ELSE
5416: l_weeks_at_second_class := 0;
5417: END IF;
5418: -- hr_utility.TRACE ('Leaving proceduer weeks_at_second_class');
5419: RETURN l_weeks_at_second_class;
5420: END weeks_at_second_class;
5421:
5422:
5428: BEGIN
5429: --
5430: -- Bug 2979713 - PRSI Context Balance Design Change
5431: --
5432: -- hr_utility.TRACE ('In procedure weeks_at_third_class');
5433: IF l_third_class IS NOT NULL
5434: THEN -- Bug fix 4004470
5435: l_weeks_at_third_class := 0; --Bug fix 4023794, flusing of variables
5436: --FOR csr_action_rec IN 1..t_asg_action_id.count
5450: --END LOOP;
5451: ELSE
5452: l_weeks_at_third_class := 0;
5453: END IF;
5454: -- hr_utility.TRACE ('Leaving procedure weeks_at_third_class');
5455: RETURN l_weeks_at_third_class;
5456: END weeks_at_third_class;
5457:
5458:
5463: AS
5464: BEGIN
5465: -- Bug 2979713 - PRSI Context Balance Design Change
5466:
5467: -- hr_utility.TRACE ('In procedure weeks_at_fourth_class');
5468: IF l_fourth_class IS NOT NULL
5469: THEN -- Bug fix 4004470
5470: l_weeks_at_fourth_class := 0; --Bug fix 4023794, flusing of variables
5471: --FOR csr_action_rec IN 1..t_asg_action_id.count
5486: ELSE
5487: l_weeks_at_fourth_class := 0;
5488: END IF;
5489:
5490: --hr_utility.TRACE ('Ieaving weeks_at_fourth_class');
5491: RETURN l_weeks_at_fourth_class;
5492: --hr_utility.TRACE ('About to leave get weeks at fourth class');
5493: END weeks_at_fourth_class;
5494:
5488: END IF;
5489:
5490: --hr_utility.TRACE ('Ieaving weeks_at_fourth_class');
5491: RETURN l_weeks_at_fourth_class;
5492: --hr_utility.TRACE ('About to leave get weeks at fourth class');
5493: END weeks_at_fourth_class;
5494:
5495:
5496: /* Added for fifth class by vik */
5501: AS
5502: BEGIN
5503: -- Bug 2979713 - PRSI Context Balance Design Change
5504:
5505: -- hr_utility.TRACE ('In procedure weeks_at_fourth_class');
5506: IF l_fifth_class IS NOT NULL
5507: THEN
5508: l_weeks_at_fifth_class := 0;
5509: l_weeks_at_fifth_class := l_weeks_at_fifth_class +
5544: --
5545:
5546: BEGIN
5547: --
5548: hr_utility.set_location('In Insurable Weeks.',201);
5549: hr_utility.set_location('In Insurable Weeks.'||to_char(p_assignment_action_id),202);
5550:
5551: p_start_date := get_start_date ();
5552: p_end_date := get_end_date ();
5545:
5546: BEGIN
5547: --
5548: hr_utility.set_location('In Insurable Weeks.',201);
5549: hr_utility.set_location('In Insurable Weeks.'||to_char(p_assignment_action_id),202);
5550:
5551: p_start_date := get_start_date ();
5552: p_end_date := get_end_date ();
5553:
5613:
5614:
5615:
5616: /* CLOSE c_get_assignment_action_id;*/
5617: --hr_utility.TRACE ('Else l_total_weeks='|| l_total_weeks);
5618: RETURN l_total_weeks;
5619: EXCEPTION
5620: WHEN OTHERS
5621: THEN
5622: --CLOSE c_get_assignment_action_id;
5623: t_context_value := t_empty_table;
5624: t_context_value_balinit := t_empty_table;
5625: t_context_value_tmp := t_empty_table;
5626: hr_utility.set_location ('Error in get_total_insurable_weeks', 200);
5627: RAISE;
5628: END get_total_insurable_weeks;
5629:
5630: