[Home] [Help]
471: l_proc_name VARCHAR2(150) := g_proc_name ||'Pension_Extract_Process';
472:
473: BEGIN
474:
475: Hr_Utility.set_location('Entering: '||l_proc_name, 5);
476: g_conc_request_id := Fnd_Global.conc_request_id;
477: SELECT Userenv('SESSIONID') INTO l_session_id FROM dual;
478: DELETE FROM pay_us_rpt_totals
479: WHERE organization_name = 'US Pension Extracts'
527: --
528: -- Call the actual benefit extract process with the effective date as the
529: -- extract end date along with the ext def. id and business group id.
530: --
531: Hr_Utility.set_location('..Calling Benefit Ext Process'||l_proc_name, 6);
532: Ben_Ext_Thread.process
533: (errbuf => l_errbuff,
534: retcode => l_retcode,
535: p_benefit_action_id => NULL,
544: AND organization_id = p_business_group_id
545: AND business_group_id = p_business_group_id
546: AND location_id = p_ext_dfn_id;
547:
548: Hr_Utility.set_location('Leaving: '||l_proc_name, 80);
549:
550: EXCEPTION
551: WHEN Others THEN
552: Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
548: Hr_Utility.set_location('Leaving: '||l_proc_name, 80);
549:
550: EXCEPTION
551: WHEN Others THEN
552: Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
553: UPDATE pay_us_rpt_totals
554: SET attribute30 = 'EXTRACT_COMPLETED'
555: WHERE organization_name = 'US Pension Extracts'
556: AND tax_unit_id = g_conc_request_id
581: l_return_value NUMBER(2) := 0;
582: l_df_st_date DATE := To_Date('1900/01/01','YYYY/MM/DD');
583: BEGIN
584:
585: Hr_Utility.set_location('Entering: '||l_proc_name, 5);
586: OPEN csr_asg (c_assignment_id => p_assignment_id
587: ,c_effective_date => p_effective_date);
588: FETCH csr_asg INTO l_asg_rec;
589: IF csr_asg%NOTFOUND THEN
593: CLOSE csr_asg;
594: l_return_value := -1;
595: RETURN l_return_value;
596: END IF;
597: Hr_Utility.set_location('..Get the employement Dates', 6);
598: CLOSE csr_asg;
599: OPEN csr_per_dates (c_effective_date => p_effective_date
600: ,c_person_id => l_asg_rec.person_id);
601: FETCH csr_per_dates INTO l_per_dates;
607: CLOSE csr_per_dates;
608: l_return_value := -1;
609: RETURN l_return_value;
610: ELSE
611: Hr_Utility.set_location('..Assign the Employement Dates', 6);
612: p_original_hire_date := l_per_dates.original_date_of_hire;
613: p_recent_hire_date := l_per_dates.date_start;
614: Hr_Utility.set_location('..Get the Termination Date', 8);
615: p_actual_termination_date := Nvl(l_per_dates.actual_termination_date,
610: ELSE
611: Hr_Utility.set_location('..Assign the Employement Dates', 6);
612: p_original_hire_date := l_per_dates.original_date_of_hire;
613: p_recent_hire_date := l_per_dates.date_start;
614: Hr_Utility.set_location('..Get the Termination Date', 8);
615: p_actual_termination_date := Nvl(l_per_dates.actual_termination_date,
616: l_df_st_date);
617: p_extract_date := p_effective_date;
618: END IF;
619: CLOSE csr_per_dates;
620: p_error_code := '0';
621: p_err_message := '0';
622:
623: Hr_Utility.set_location('Leaving: '||l_proc_name, 60);
624:
625: RETURN l_return_value;
626:
627: EXCEPTION
626:
627: EXCEPTION
628: WHEN Others THEN
629: p_err_message :='SQL-ERRM :'||SQLERRM;
630: Hr_Utility.set_location('..'||p_err_message,85);
631: Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
632:
633: END Get_Indicative_DateSwitch;
634:
627: EXCEPTION
628: WHEN Others THEN
629: p_err_message :='SQL-ERRM :'||SQLERRM;
630: Hr_Utility.set_location('..'||p_err_message,85);
631: Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
632:
633: END Get_Indicative_DateSwitch;
634:
635: -- =============================================================================
684: l_input_value_id pay_input_values_f.input_value_id%TYPE;
685: l_return_value NUMBER(2) :=0;
686: l_df_st_date DATE := To_Date('1900/01/01','YYYY/MM/DD');
687: BEGIN
688: Hr_Utility.set_location('Entering: '||l_proc_name, 5);
689: OPEN csr_asg (c_assignment_id => p_assignment_id
690: ,c_effective_date => p_effective_date);
691: FETCH csr_asg INTO l_asg_rec;
692: IF csr_asg%NOTFOUND THEN
696: CLOSE csr_asg;
697: l_return_value := -1;
698: RETURN l_return_value;
699: END IF;
700: Hr_Utility.set_location('..Valid Assignment Id '||p_assignment_id, 6);
701: CLOSE csr_asg;
702: OPEN csr_per_dates (c_effective_date => p_effective_date
703: ,c_person_id => l_asg_rec.person_id);
704: FETCH csr_per_dates INTO l_per_dates;
710: CLOSE csr_per_dates;
711: l_return_value := -1;
712: RETURN l_return_value;
713: ELSE
714: Hr_Utility.set_location('..Person Details found Id: '||l_asg_rec.person_id, 7);
715: p_original_hire_date := l_per_dates.original_date_of_hire;
716: p_recent_hire_date := l_per_dates.date_start;
717: p_actual_termination_date := Nvl(l_per_dates.actual_termination_date,
718: l_df_st_date);
720: p_extract_date := p_effective_date;
721: END IF;
722: CLOSE csr_per_dates;
723:
724: Hr_Utility.set_location('..Getting the screen entry value', 7);
725: l_ele_type_id := g_element.FIRST;
726: WHILE l_ele_type_id IS NOT NULL
727: LOOP
728: l_input_value_id := g_element(l_ele_type_id).input_value_id;
742: CLOSE csr_entry_dtls;
743: l_prev_ele_type_id := l_ele_type_id;
744: l_ele_type_id := g_element.NEXT(l_prev_ele_type_id);
745: END LOOP; -- While Loop
746: Hr_Utility.set_location('Leaving: '||l_proc_name, 60);
747: RETURN l_return_value;
748:
749: EXCEPTION
750: WHEN Others THEN
748:
749: EXCEPTION
750: WHEN Others THEN
751: p_err_message :='SQL-ERRM :'||SQLERRM;
752: Hr_Utility.set_location('..'||p_err_message,85);
753: Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
754:
755: END Get_Participant_Status_Code;
756:
749: EXCEPTION
750: WHEN Others THEN
751: p_err_message :='SQL-ERRM :'||SQLERRM;
752: Hr_Utility.set_location('..'||p_err_message,85);
753: Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
754:
755: END Get_Participant_Status_Code;
756:
757: -- =============================================================================
818: Extra Person Info. Details PER_PEOPLE_EXTRA_INFO -DO-
819: */
820:
821: BEGIN
822: Hr_Utility.set_location('Entering: '||l_proc_name, 5);
823: p_error_code := '0';
824: OPEN csr_asg (c_assignment_id => p_assignment_id
825: ,c_effective_date => p_effective_date);
826: FETCH csr_asg INTO l_asg_rec;
834: END IF;
835: CLOSE csr_asg;
836: IF p_flex_name IN('Extra Person Info DDF',
837: 'PER_PEOPLE_EXTRA_INFO') THEN
838: Hr_Utility.set_location('..p_flex_name = '||p_flex_name, 6);
839: OPEN csr_per_mult_occur(c_information_type => p_flex_context);
840: FETCH csr_per_mult_occur INTO l_mult_occur;
841: CLOSE csr_per_mult_occur;
842: IF l_mult_occur <> 'Y' THEN
839: OPEN csr_per_mult_occur(c_information_type => p_flex_context);
840: FETCH csr_per_mult_occur INTO l_mult_occur;
841: CLOSE csr_per_mult_occur;
842: IF l_mult_occur <> 'Y' THEN
843: Hr_Utility.set_location('..l_mult_occur = '||l_mult_occur, 6);
844: OPEN csr_pei (c_person_id => l_asg_rec.person_id
845: ,c_information_type => p_flex_context);
846: FETCH csr_pei INTO l_key_val;
847: CLOSE csr_pei;
847: CLOSE csr_pei;
848: l_key_col := 'PERSON_EXTRA_INFO_ID';
849: l_tab_name := 'PER_PEOPLE_EXTRA_INFO';
850: ELSE
851: Hr_Utility.set_location('..l_mult_occur = '||l_mult_occur, 6);
852: p_error_code := '-20001';
853: p_err_message := 'Contexts :'||p_flex_context ||
854: ' can have multiple occurances';
855: l_return_value := 'EXT_ERR_WARNING';
856: RETURN l_return_value;
857: END IF;
858: ELSIF p_flex_name IN('Assignment Developer DF',
859: 'PER_ASSIGNMENT_EXTRA_INFO') THEN
860: Hr_Utility.set_location('..p_flex_name = '||p_flex_name, 7);
861: OPEN csr_asg_mult_occur(c_information_type => p_flex_context);
862: FETCH csr_asg_mult_occur INTO l_mult_occur;
863: CLOSE csr_asg_mult_occur;
864: IF l_mult_occur <> 'Y' THEN
861: OPEN csr_asg_mult_occur(c_information_type => p_flex_context);
862: FETCH csr_asg_mult_occur INTO l_mult_occur;
863: CLOSE csr_asg_mult_occur;
864: IF l_mult_occur <> 'Y' THEN
865: Hr_Utility.set_location('..l_mult_occur = '||l_mult_occur, 7);
866: OPEN csr_aei (c_assignment_id => l_asg_rec.assignment_id
867: ,c_information_type => p_flex_context);
868: FETCH csr_aei INTO l_key_val;
869: CLOSE csr_aei;
869: CLOSE csr_aei;
870: l_key_col := 'ASSIGNMENT_EXTRA_INFO_ID';
871: l_tab_name := 'PER_ASSIGNMENT_EXTRA_INFO';
872: ELSE
873: Hr_Utility.set_location('..l_mult_occur = '||l_mult_occur, 7);
874: p_error_code := '-20001';
875: p_err_message := 'Contexts :'||p_flex_context ||
876: ' can have multiple occurances';
877: l_return_value := 'EXT_ERR_WARNING';
877: l_return_value := 'EXT_ERR_WARNING';
878: RETURN l_return_value;
879: END IF;
880: ELSE
881: Hr_Utility.set_location('..Invalid p_flex_name = '||p_flex_name, 8);
882: RAISE Invaild_DDF_or_DF;
883: END IF;
884:
885: IF p_flex_name IN ('Extra Person Info DDF',
883: END IF;
884:
885: IF p_flex_name IN ('Extra Person Info DDF',
886: 'Assignment Developer DF') THEN
887: Hr_Utility.set_location('..Calling pqp_utilities.get_ddf_value', 9);
888: l_ddf_seg_value := Pqp_Utilities.get_ddf_value(
889: p_flex_name => p_flex_name
890: ,p_flex_context => p_flex_context
891: ,p_flex_field_title => p_flex_field_title
898: ,p_error_code => p_error_code
899: ,p_message => p_err_message
900: );
901: l_return_value := l_ddf_seg_value;
902: Hr_Utility.set_location('..get_ddf_value ='||l_return_value, 10);
903: ELSIF p_flex_name IN ('PER_ASSIGNMENT_EXTRA_INFO',
904: 'PER_PEOPLE_EXTRA_INFO') THEN
905: Hr_Utility.set_location('..Calling pqp_utilities.get_df_value', 9);
906: l_df_seg_value:= Pqp_Utilities.get_df_value(
901: l_return_value := l_ddf_seg_value;
902: Hr_Utility.set_location('..get_ddf_value ='||l_return_value, 10);
903: ELSIF p_flex_name IN ('PER_ASSIGNMENT_EXTRA_INFO',
904: 'PER_PEOPLE_EXTRA_INFO') THEN
905: Hr_Utility.set_location('..Calling pqp_utilities.get_df_value', 9);
906: l_df_seg_value:= Pqp_Utilities.get_df_value(
907: p_flex_name => p_flex_name
908: ,p_flex_context => p_flex_context
909: ,p_flex_field_title => p_flex_field_title
917: ,p_error_code => p_error_code
918: ,p_message => p_err_message
919: );
920: l_return_value := l_df_seg_value;
921: Hr_Utility.set_location('..get_df_value ='||l_return_value, 10);
922: ELSE
923: RAISE Invaild_DDF_or_DF;
924: END IF;
925: l_return_value := Nvl(l_return_value,'EXT_NULL_VALUE');
922: ELSE
923: RAISE Invaild_DDF_or_DF;
924: END IF;
925: l_return_value := Nvl(l_return_value,'EXT_NULL_VALUE');
926: Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
927: RETURN l_return_value;
928:
929: EXCEPTION
930: WHEN Invaild_DDF_or_DF THEN
931: p_error_code := '-20001';
932: p_err_message := 'Currently Supported DDF/DFs :Assignment Developer DF,'||
933: 'Extra Person Info DDF,PER_ASSIGNMENT_EXTRA_INFO,PER_ASSIGNMENT_EXTRA_INFO';
934: l_return_value := 'EXT_ERR_WARNING';
935: Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
936: RETURN l_return_value;
937:
938: WHEN Others THEN
939: l_return_value := 'EXT_ERR_WARNING';
938: WHEN Others THEN
939: l_return_value := 'EXT_ERR_WARNING';
940: p_error_code := '-20001';
941: p_err_message :='SQL-ERRM :'||SQLERRM;
942: Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
943: RETURN l_return_value;
944:
945: END Get_DDF_DF_Value;
946:
995: Invaild_kff_flex EXCEPTION;
996: l_return_value VARCHAR2(150);
997: l_proc_name VARCHAR2(150) := g_proc_name ||'Get_SIT_Segment';
998: BEGIN
999: Hr_Utility.set_location('Entering: '||l_proc_name, 5);
1000: p_error_code := '0';
1001: g_business_group_id := p_business_group_id;
1002: OPEN csr_asg (c_assignment_id => p_assignment_id
1003: ,c_effective_date => p_effective_date);
1010: l_return_value := 'EXT_ERR_WARNING';
1011: RETURN l_return_value;
1012: END IF;
1013: CLOSE csr_asg;
1014: Hr_Utility.set_location('p_structure_code: '||p_structure_code, 5);
1015: Hr_Utility.set_location('p_assignment_id: '||p_assignment_id, 5);
1016: Hr_Utility.set_location('p_effective_date: '||p_effective_date, 5);
1017: Hr_Utility.set_location('p_business_group_id: '||p_business_group_id, 5);
1018:
1011: RETURN l_return_value;
1012: END IF;
1013: CLOSE csr_asg;
1014: Hr_Utility.set_location('p_structure_code: '||p_structure_code, 5);
1015: Hr_Utility.set_location('p_assignment_id: '||p_assignment_id, 5);
1016: Hr_Utility.set_location('p_effective_date: '||p_effective_date, 5);
1017: Hr_Utility.set_location('p_business_group_id: '||p_business_group_id, 5);
1018:
1019: -- Get the Key Flex Number for given Structure code
1012: END IF;
1013: CLOSE csr_asg;
1014: Hr_Utility.set_location('p_structure_code: '||p_structure_code, 5);
1015: Hr_Utility.set_location('p_assignment_id: '||p_assignment_id, 5);
1016: Hr_Utility.set_location('p_effective_date: '||p_effective_date, 5);
1017: Hr_Utility.set_location('p_business_group_id: '||p_business_group_id, 5);
1018:
1019: -- Get the Key Flex Number for given Structure code
1020: OPEN csr_flex_num (c_structure_code => p_structure_code);
1013: CLOSE csr_asg;
1014: Hr_Utility.set_location('p_structure_code: '||p_structure_code, 5);
1015: Hr_Utility.set_location('p_assignment_id: '||p_assignment_id, 5);
1016: Hr_Utility.set_location('p_effective_date: '||p_effective_date, 5);
1017: Hr_Utility.set_location('p_business_group_id: '||p_business_group_id, 5);
1018:
1019: -- Get the Key Flex Number for given Structure code
1020: OPEN csr_flex_num (c_structure_code => p_structure_code);
1021: FETCH csr_flex_num INTO l_flex;
1019: -- Get the Key Flex Number for given Structure code
1020: OPEN csr_flex_num (c_structure_code => p_structure_code);
1021: FETCH csr_flex_num INTO l_flex;
1022: CLOSE csr_flex_num;
1023: Hr_Utility.set_location('l_flex.id_flex_num: '||l_flex.id_flex_num, 5);
1024: Hr_Utility.set_location('l_asg_rec.person_id: '||l_asg_rec.person_id, 5);
1025:
1026: -- Get the Key Flex for the person if present for the person
1027: OPEN csr_pe (c_business_group_id => p_business_group_id
1020: OPEN csr_flex_num (c_structure_code => p_structure_code);
1021: FETCH csr_flex_num INTO l_flex;
1022: CLOSE csr_flex_num;
1023: Hr_Utility.set_location('l_flex.id_flex_num: '||l_flex.id_flex_num, 5);
1024: Hr_Utility.set_location('l_asg_rec.person_id: '||l_asg_rec.person_id, 5);
1025:
1026: -- Get the Key Flex for the person if present for the person
1027: OPEN csr_pe (c_business_group_id => p_business_group_id
1028: ,c_person_id => l_asg_rec.person_id
1033: CLOSE csr_pe;
1034: RETURN l_return_value;
1035: END IF;
1036: CLOSE csr_pe;
1037: Hr_Utility.set_location('analysis_criteria_id:'||l_per_analysis_rec.analysis_criteria_id, 5);
1038: -- Get the KFF segments
1039: OPEN csr_kff_seg
1040: (c_anal_criteria_id => l_per_analysis_rec.analysis_criteria_id
1041: ,c_flex_num => l_flex.id_flex_num
1041: ,c_flex_num => l_flex.id_flex_num
1042: ,c_effective_date => p_effective_date);
1043: FETCH csr_kff_seg INTO l_analysis_criteria_rec;
1044: CLOSE csr_kff_seg;
1045: Hr_Utility.set_location('p_segment_name: '||p_segment_name, 5);
1046:
1047: IF p_segment_name = 'SEGMENT1' THEN
1048: l_return_value := l_analysis_criteria_rec.segment1;
1049: ELSIF p_segment_name = 'SEGMENT2' THEN
1106: l_return_value := l_analysis_criteria_rec.segment30;
1107: END IF;
1108:
1109: l_return_value := Nvl(l_return_value,'EXT_NULL_VALUE');
1110: Hr_Utility.set_location('l_return_value: '||l_return_value,80);
1111:
1112: Hr_Utility.set_location('Leaving: '||l_proc_name,80);
1113: RETURN l_return_value;
1114:
1108:
1109: l_return_value := Nvl(l_return_value,'EXT_NULL_VALUE');
1110: Hr_Utility.set_location('l_return_value: '||l_return_value,80);
1111:
1112: Hr_Utility.set_location('Leaving: '||l_proc_name,80);
1113: RETURN l_return_value;
1114:
1115: EXCEPTION
1116: WHEN Invaild_kff_flex THEN
1116: WHEN Invaild_kff_flex THEN
1117: p_error_code := '-20001';
1118: p_err_message := 'Invalid Key Flex structure code.';
1119: l_return_value := 'EXT_ERR_WARNING';
1120: Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
1121: RETURN l_return_value;
1122:
1123: WHEN Others THEN
1124: l_return_value := 'EXT_ERR_WARNING';
1123: WHEN Others THEN
1124: l_return_value := 'EXT_ERR_WARNING';
1125: p_error_code := '-20001';
1126: p_err_message :='SQL-ERRM :'||SQLERRM;
1127: Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
1128: RETURN l_return_value;
1129:
1130: END Get_SIT_Segment;
1131: -- =============================================================================
1176: l_proc_name CONSTANT VARCHAR2(150) := g_proc_name ||'Get_PPG_Billing_Code';
1177: l_ppg_code VARCHAR2(150);
1178: l_tax_unit_id NUMBER;
1179: BEGIN
1180: Hr_Utility.set_location('Entering: '||l_proc_name, 5);
1181: -- Get the PPG Code from Assig Extra Info
1182: OPEN csr_asg_ppg (c_effective_date => p_effective_date
1183: ,c_assignment_id => p_assignment_id);
1184: FETCH csr_asg_ppg INTO l_ppg_code;
1183: ,c_assignment_id => p_assignment_id);
1184: FETCH csr_asg_ppg INTO l_ppg_code;
1185: CLOSE csr_asg_ppg;
1186: -- Get the PPG Code from Payroll
1187: Hr_Utility.set_location('Entering: '||l_proc_name, 6);
1188: IF l_ppg_code IS NULL THEN
1189: OPEN csr_pay_ppg(c_effective_date => p_effective_date
1190: ,c_payroll_id => p_payroll_id);
1191: FETCH csr_pay_ppg INTO l_ppg_code;
1191: FETCH csr_pay_ppg INTO l_ppg_code;
1192: CLOSE csr_pay_ppg;
1193: END IF;
1194: -- Get the PPG Code from Assig GRE
1195: Hr_Utility.set_location('Entering: '||l_proc_name, 7);
1196: IF l_ppg_code IS NULL AND
1197: p_tax_unit_id IS NOT NULL THEN
1198: OPEN csr_org_ppg (c_tax_unit_id => p_tax_unit_id);
1199: FETCH csr_org_ppg INTO l_ppg_code;
1208: FETCH csr_org_ppg INTO l_ppg_code;
1209: CLOSE csr_org_ppg;
1210:
1211: END IF;
1212: Hr_Utility.set_location('Leaving: '||l_proc_name, 60);
1213: RETURN l_ppg_code;
1214: EXCEPTION
1215: WHEN Others THEN
1216: Hr_Utility.set_location('..Exception OTHERS in '||l_proc_name,85);
1212: Hr_Utility.set_location('Leaving: '||l_proc_name, 60);
1213: RETURN l_ppg_code;
1214: EXCEPTION
1215: WHEN Others THEN
1216: Hr_Utility.set_location('..Exception OTHERS in '||l_proc_name,85);
1217: Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
1218: RETURN l_ppg_code;
1219: END Get_PPG_Billing_Code;
1220: -- =============================================================================
1213: RETURN l_ppg_code;
1214: EXCEPTION
1215: WHEN Others THEN
1216: Hr_Utility.set_location('..Exception OTHERS in '||l_proc_name,85);
1217: Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
1218: RETURN l_ppg_code;
1219: END Get_PPG_Billing_Code;
1220: -- =============================================================================
1221: -- Get_Payment_Mode:
1238:
1239: l_proc_name CONSTANT VARCHAR2(150) := g_proc_name ||'Get_Payment_Mode';
1240: l_payment_code VARCHAR2(150);
1241: BEGIN
1242: Hr_Utility.set_location('Entering: '||l_proc_name, 5);
1243: -- Get the Payment Mode from Payroll
1244: OPEN csr_pay_ppg(c_effective_date => p_effective_date
1245: ,c_payroll_id => p_payroll_id);
1246: FETCH csr_pay_ppg INTO l_payment_code;
1244: OPEN csr_pay_ppg(c_effective_date => p_effective_date
1245: ,c_payroll_id => p_payroll_id);
1246: FETCH csr_pay_ppg INTO l_payment_code;
1247: CLOSE csr_pay_ppg;
1248: Hr_Utility.set_location('Leaving: '||l_proc_name, 60);
1249: RETURN l_payment_code;
1250: EXCEPTION
1251: WHEN Others THEN
1252: Hr_Utility.set_location('..Exception OTHERS in '||l_proc_name,85);
1248: Hr_Utility.set_location('Leaving: '||l_proc_name, 60);
1249: RETURN l_payment_code;
1250: EXCEPTION
1251: WHEN Others THEN
1252: Hr_Utility.set_location('..Exception OTHERS in '||l_proc_name,85);
1253: Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
1254: RETURN l_payment_code;
1255: END Get_Payment_Mode;
1256: -- =============================================================================
1249: RETURN l_payment_code;
1250: EXCEPTION
1251: WHEN Others THEN
1252: Hr_Utility.set_location('..Exception OTHERS in '||l_proc_name,85);
1253: Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
1254: RETURN l_payment_code;
1255: END Get_Payment_Mode;
1256: -- =============================================================================
1257: -- Chk_If_Roth:
1273: l_ele_ext_info csr_ext_id%ROWTYPE;
1274: l_proc_name CONSTANT varchar2(150) := g_proc_name||'Chk_Ele_Type';
1275:
1276: BEGIN
1277: Hr_Utility.set_location('Entering: '||l_proc_name, 5);
1278:
1279: g_element(p_element_type_id).Roth_Element := 'N';
1280: g_element(p_element_type_id).Roth_ER_Element := 'N';
1281: g_element(p_element_type_id).ER_Element := 'N';
1295: g_element(p_element_type_id).ATER_Element := 'Y';
1296: ELSIF instr(p_balance_name,' ER') > 0 THEN
1297: g_element(p_element_type_id).ER_Element := 'Y';
1298: END IF;
1299: Hr_Utility.set_location(' p_balance_name: '||p_balance_name, 50);
1300: Hr_Utility.set_location('Leaving: '||l_proc_name, 60);
1301:
1302: END Chk_Ele_Type;
1303:
1296: ELSIF instr(p_balance_name,' ER') > 0 THEN
1297: g_element(p_element_type_id).ER_Element := 'Y';
1298: END IF;
1299: Hr_Utility.set_location(' p_balance_name: '||p_balance_name, 50);
1300: Hr_Utility.set_location('Leaving: '||l_proc_name, 60);
1301:
1302: END Chk_Ele_Type;
1303:
1304: -- =============================================================================
1333: l_pretax_category pay_element_types_f.element_information1%TYPE;
1334: l_proc_name VARCHAR2(150) := g_proc_name ||'Get_Element_Info';
1335: l_ele_info_rec csr_ele_info%ROWTYPE;
1336: BEGIN
1337: Hr_Utility.set_location('Entering: '||l_proc_name, 5);
1338: l_legislation_code := g_extract_params(p_business_group_id).legislation_code;
1339: -- Get the element information details
1340: OPEN csr_ele_info (c_element_type_id => p_element_type_id
1341: ,c_effective_date => p_effective_date
1350: g_element(p_element_type_id).Information_category := l_ele_info_rec.element_information_category;
1351: g_element(p_element_type_id).PreTax_category := l_ele_info_rec.element_information1;
1352: g_element(p_element_type_id).Primary_balance_id := l_ele_info_rec.element_information10;
1353:
1354: Hr_Utility.set_location('..Element Type Id :'||p_element_type_id, 7);
1355:
1356: IF l_ele_info_rec.element_information10 IS NOT NULL THEN
1357: l_balance_type_id := To_Number(l_ele_info_rec.element_information10);
1358: OPEN csr_bal_name(c_balance_type_id => l_balance_type_id);
1408: ,c_business_group_id => p_business_group_id
1409: ,c_legislation_code => l_legislation_code);
1410: FETCH csr_ele_info INTO l_ele_info_rec;
1411: IF csr_ele_info%FOUND THEN
1412: Hr_Utility.set_location('..AT l_ele_type_id :'||l_ele_type_id, 8);
1413: Hr_Utility.set_location('..AT l_input_value_id :'||l_input_value_id, 8);
1414: IF l_ele_info_rec.element_information_category ='US_VOLUNTARY DEDUCTIONS' THEN
1415: g_element(p_element_type_id).AT_ele_type_id := l_ele_type_id;
1416: g_element(p_element_type_id).AT_ipv_id := l_input_value_id;
1409: ,c_legislation_code => l_legislation_code);
1410: FETCH csr_ele_info INTO l_ele_info_rec;
1411: IF csr_ele_info%FOUND THEN
1412: Hr_Utility.set_location('..AT l_ele_type_id :'||l_ele_type_id, 8);
1413: Hr_Utility.set_location('..AT l_input_value_id :'||l_input_value_id, 8);
1414: IF l_ele_info_rec.element_information_category ='US_VOLUNTARY DEDUCTIONS' THEN
1415: g_element(p_element_type_id).AT_ele_type_id := l_ele_type_id;
1416: g_element(p_element_type_id).AT_ipv_id := l_input_value_id;
1417: g_element(p_element_type_id).AT_balance_id := l_ele_info_rec.element_information10;
1442: ,c_business_group_id => p_business_group_id
1443: ,c_legislation_code => l_legislation_code);
1444: FETCH csr_ele_info INTO l_ele_info_rec;
1445: IF csr_ele_info%FOUND THEN
1446: Hr_Utility.set_location('..Roth l_ele_type_id :'||l_ele_type_id, 8);
1447: Hr_Utility.set_location('..Roth l_input_value_id :'||l_input_value_id, 8);
1448: IF l_ele_info_rec.element_information_category = 'US_VOLUNTARY DEDUCTIONS' THEN
1449: g_element(p_element_type_id).Roth_ele_type_id := l_ele_type_id;
1450: g_element(p_element_type_id).Roth_ipv_id := l_input_value_id;
1443: ,c_legislation_code => l_legislation_code);
1444: FETCH csr_ele_info INTO l_ele_info_rec;
1445: IF csr_ele_info%FOUND THEN
1446: Hr_Utility.set_location('..Roth l_ele_type_id :'||l_ele_type_id, 8);
1447: Hr_Utility.set_location('..Roth l_input_value_id :'||l_input_value_id, 8);
1448: IF l_ele_info_rec.element_information_category = 'US_VOLUNTARY DEDUCTIONS' THEN
1449: g_element(p_element_type_id).Roth_ele_type_id := l_ele_type_id;
1450: g_element(p_element_type_id).Roth_ipv_id := l_input_value_id;
1451: g_element(p_element_type_id).Roth_balance_id := l_ele_info_rec.element_information10;
1475: ,c_business_group_id => p_business_group_id
1476: ,c_legislation_code => l_legislation_code);
1477: FETCH csr_ele_info INTO l_ele_info_rec;
1478: IF csr_ele_info%FOUND THEN
1479: Hr_Utility.set_location('..Catch up l_ele_type_id :'||l_ele_type_id, 8);
1480: Hr_Utility.set_location('..Catch up l_input_value_id :'||l_input_value_id, 8);
1481: IF l_ele_info_rec.element_information_category ='US_PRE-TAX DEDUCTIONS' AND
1482: l_ele_info_rec.element_information1 IN ('DC','EC','GC') THEN
1483: g_element(p_element_type_id).CatchUp_ele_type_id := l_ele_type_id;
1476: ,c_legislation_code => l_legislation_code);
1477: FETCH csr_ele_info INTO l_ele_info_rec;
1478: IF csr_ele_info%FOUND THEN
1479: Hr_Utility.set_location('..Catch up l_ele_type_id :'||l_ele_type_id, 8);
1480: Hr_Utility.set_location('..Catch up l_input_value_id :'||l_input_value_id, 8);
1481: IF l_ele_info_rec.element_information_category ='US_PRE-TAX DEDUCTIONS' AND
1482: l_ele_info_rec.element_information1 IN ('DC','EC','GC') THEN
1483: g_element(p_element_type_id).CatchUp_ele_type_id := l_ele_type_id;
1484: g_element(p_element_type_id).CatchUp_ipv_id := l_input_value_id;
1484: g_element(p_element_type_id).CatchUp_ipv_id := l_input_value_id;
1485: g_element(p_element_type_id).CatchUp_Balance_id := l_ele_info_rec.element_information10;
1486: END IF;
1487: ELSE
1488: Hr_Utility.set_location('..Cursor failed csr_ele_info for CatchUp ', 13);
1489: END IF;
1490: CLOSE csr_ele_info;
1491: ELSE
1492: Hr_Utility.set_location('..Cursor failed csr_ele_ipv for CatchUp ', 13);
1488: Hr_Utility.set_location('..Cursor failed csr_ele_info for CatchUp ', 13);
1489: END IF;
1490: CLOSE csr_ele_info;
1491: ELSE
1492: Hr_Utility.set_location('..Cursor failed csr_ele_ipv for CatchUp ', 13);
1493: END IF;
1494: CLOSE csr_ele_ipv;
1495: END IF;
1496: -- =======================================================================
1519: IF csr_bal_typid%FOUND THEN
1520: g_element(p_element_type_id).ER_Balance_id := l_balance_type_id;
1521: END IF;
1522: CLOSE csr_bal_typid;
1523: Hr_Utility.set_location('..ER:l_ele_type_id : '|| l_ele_type_id, 14);
1524: Hr_Utility.set_location('..ER:l_balance_type_id : '||l_balance_type_id, 14);
1525: END IF;
1526: -- =======================================================================
1527: -- ~ Get Roth ER Info for the Element Type Id Passed
1520: g_element(p_element_type_id).ER_Balance_id := l_balance_type_id;
1521: END IF;
1522: CLOSE csr_bal_typid;
1523: Hr_Utility.set_location('..ER:l_ele_type_id : '|| l_ele_type_id, 14);
1524: Hr_Utility.set_location('..ER:l_balance_type_id : '||l_balance_type_id, 14);
1525: END IF;
1526: -- =======================================================================
1527: -- ~ Get Roth ER Info for the Element Type Id Passed
1528: -- ~ if any exists.
1548: IF csr_bal_typid%FOUND THEN
1549: g_element(p_element_type_id).RothER_Balance_id := l_balance_type_id;
1550: END IF;
1551: CLOSE csr_bal_typid;
1552: Hr_Utility.set_location('..Roth ER:l_ele_type_id : '|| l_ele_type_id, 15);
1553: Hr_Utility.set_location('..Roth ER:l_balance_type_id : '||l_balance_type_id, 15);
1554: END IF;
1555: -- =======================================================================
1556: -- ~ Get AT ER Info for the Element Type Id Passed
1549: g_element(p_element_type_id).RothER_Balance_id := l_balance_type_id;
1550: END IF;
1551: CLOSE csr_bal_typid;
1552: Hr_Utility.set_location('..Roth ER:l_ele_type_id : '|| l_ele_type_id, 15);
1553: Hr_Utility.set_location('..Roth ER:l_balance_type_id : '||l_balance_type_id, 15);
1554: END IF;
1555: -- =======================================================================
1556: -- ~ Get AT ER Info for the Element Type Id Passed
1557: -- ~ if any exists.
1577: IF csr_bal_typid%FOUND THEN
1578: g_element(p_element_type_id).ATER_Balance_id := l_balance_type_id;
1579: END IF;
1580: CLOSE csr_bal_typid;
1581: Hr_Utility.set_location('..AT ER:l_ele_type_id : '|| l_ele_type_id, 16);
1582: Hr_Utility.set_location('..AT ER:l_balance_type_id : '||l_balance_type_id, 16);
1583: END IF;
1584: ELSE
1585: -- Cursor failed to get any matching record for the ele type id passed.
1578: g_element(p_element_type_id).ATER_Balance_id := l_balance_type_id;
1579: END IF;
1580: CLOSE csr_bal_typid;
1581: Hr_Utility.set_location('..AT ER:l_ele_type_id : '|| l_ele_type_id, 16);
1582: Hr_Utility.set_location('..AT ER:l_balance_type_id : '||l_balance_type_id, 16);
1583: END IF;
1584: ELSE
1585: -- Cursor failed to get any matching record for the ele type id passed.
1586: CLOSE csr_ele_info;
1584: ELSE
1585: -- Cursor failed to get any matching record for the ele type id passed.
1586: CLOSE csr_ele_info;
1587: END IF;-- If csr_ele_info%FOUND
1588: Hr_Utility.set_location('Leaving: '||l_proc_name, 80);
1589: END Get_Element_Info;
1590: -- =============================================================================
1591: -- Get_Element_Info: Called only for extract with specific ext dfn type.
1592: -- =============================================================================
1605: l_legislation_code per_business_groups.legislation_code%TYPE;
1606: l_proc_name VARCHAR2(150) := g_proc_name ||'Get_Element_Info';
1607: l_ele_info_rec csr_ele_info%ROWTYPE;
1608: BEGIN
1609: Hr_Utility.set_location('Entering: '||l_proc_name, 5);
1610: l_legislation_code := g_extract_params(p_business_group_id).legislation_code;
1611: -- Get the element information details
1612: OPEN csr_ele_info (c_element_type_id => p_element_type_id
1613: ,c_effective_date => p_effective_date
1621: -- =======================================================================
1622: g_element(p_element_type_id).Information_category := l_ele_info_rec.element_information_category;
1623: g_element(p_element_type_id).PreTax_category := l_ele_info_rec.element_information1;
1624: g_element(p_element_type_id).Primary_balance_id := l_ele_info_rec.element_information10;
1625: Hr_Utility.set_location('..Element Type Id :'||p_element_type_id, 7);
1626:
1627: IF l_ele_info_rec.element_information10 IS NOT NULL THEN
1628: l_balance_type_id := To_Number(l_ele_info_rec.element_information10);
1629: OPEN csr_bal_name(c_balance_type_id => l_balance_type_id);
1664: ELSE
1665: -- Cursor failed to get any matching record for the ele type id passed.
1666: CLOSE csr_ele_info;
1667: END IF;-- If csr_ele_info%FOUND
1668: Hr_Utility.set_location('Leaving: '||l_proc_name, 80);
1669: END Get_Element_Info;
1670: -- ================================================================================
1671: -- ~ Update_Record_Values :
1672: -- ================================================================================
1694: l_proc_name VARCHAR2(150):= g_proc_name||'Update_Record_Values';
1695: l_ext_dtl_rec_nc ben_ext_rslt_dtl%ROWTYPE;
1696: BEGIN
1697:
1698: Hr_Utility.set_location('Entering :'||l_proc_name, 5);
1699: -- nocopy changes
1700: l_ext_dtl_rec_nc := p_ext_dtl_rec;
1701:
1702: IF p_data_ele_seqnum IS NULL THEN
1864: p_ext_dtl_rec.val_74 := p_data_element_value;
1865: ELSIF l_seqnum_rec.seq_num = 75 THEN
1866: p_ext_dtl_rec.val_75 := p_data_element_value;
1867: END IF;
1868: Hr_Utility.set_location('Leaving :'||l_proc_name, 25);
1869: RETURN;
1870: EXCEPTION
1871: WHEN Others THEN
1872: -- nocopy changes
1886: l_proc_name VARCHAR2(150) := g_proc_name||'Ins_Rslt_Dtl';
1887: l_dtl_rec_nc ben_ext_rslt_dtl%ROWTYPE;
1888:
1889: BEGIN -- ins_rslt_dtl
1890: Hr_Utility.set_location('Entering :'||l_proc_name, 5);
1891: -- nocopy changes
1892: l_dtl_rec_nc := p_dtl_rec;
1893: -- Get the next sequence NUMBER to insert a record into the table
1894: SELECT ben_ext_rslt_dtl_s.NEXTVAL INTO p_dtl_rec.ext_rslt_dtl_id FROM dual;
2085: ,p_dtl_rec.THRD_SORT_VAL
2086: ,p_dtl_rec.TRANS_SEQ_NUM
2087: ,p_dtl_rec.RCRD_SEQ_NUM
2088: );
2089: Hr_Utility.set_location('Leaving :'||l_proc_name, 25);
2090: RETURN;
2091:
2092: EXCEPTION
2093: WHEN Others THEN
2090: RETURN;
2091:
2092: EXCEPTION
2093: WHEN Others THEN
2094: Hr_Utility.set_location('Leaving :'||l_proc_name, 25);
2095: p_dtl_rec := l_dtl_rec_nc;
2096: RAISE;
2097: END Ins_Rslt_Dtl;
2098:
2206: l_err_no NUMBER := p_err_no ;
2207:
2208: BEGIN
2209:
2210: Hr_Utility.set_location('Entering'||l_proc, 5);
2211: IF p_err_no IS NULL THEN
2212: -- Assumed the name is Error Name
2213: l_err_no := To_Number(Substr(p_err_name,5,5)) ;
2214: l_err_name := NULL ;
2229: p_business_group_id => g_business_group_id,
2230: p_ext_rslt_id => Ben_Extract.g_ext_rslt_id);
2231: END IF;
2232: --
2233: Hr_Utility.set_location('Exiting'||l_proc, 15);
2234: --
2235: --
2236: END Write_Warning;
2237: -- =============================================================================
2253: AND typ_cd <> 'W';
2254: --
2255: BEGIN
2256: --
2257: Hr_Utility.set_location('Entering'||l_proc, 5);
2258: IF p_err_no IS NULL THEN
2259: -- Assumed the name is Error Name
2260: l_err_no := To_Number(Substr(p_err_name,5,5)) ;
2261: l_err_name := NULL ;
2288: p_business_group_id => g_business_group_id,
2289: p_ext_rslt_id => Ben_Extract.g_ext_rslt_id);
2290: END IF;
2291: --
2292: Hr_Utility.set_location('Exiting'||l_proc, 15);
2293: --
2294: --
2295: END Write_Error;
2296:
2338: --
2339: --
2340: BEGIN
2341: --
2342: Hr_Utility.set_location('Entering'||l_proc, 5);
2343: --
2344: p_exclude_this_rcd_flag := FALSE;
2345: p_raise_warning := FALSE;
2346: p_rollback_person := FALSE;
2477: END IF; -- IF p_sprs_cd = 'D'
2478: --
2479: END IF; -- IF l_condition = 'T'
2480: --
2481: Hr_Utility.set_location('Exiting'||l_proc, 15);
2482: --
2483: END Rcd_In_File;
2484:
2485: -- =============================================================================
2521: l_dynamic_condition VARCHAR2(9999);
2522: --
2523: l_val_tab_mirror ValTabTyp;
2524: BEGIN
2525: Hr_Utility.set_location('Entering'||l_proc, 5);
2526: p_exclude_this_rcd_flag := FALSE;
2527: p_raise_warning := FALSE;
2528: p_rollback_person := FALSE;
2529: -- Make mirror image of table for evaluation, since values in
2628: END IF;-- IF l_cnt > 0 THEN
2629: --
2630: END LOOP; -- FOR xer IN c_xer
2631: --
2632: Hr_Utility.set_location('Exiting'||l_proc, 15);
2633: --
2634: END Data_Elmt_In_Rcd;
2635:
2636: -- =============================================================================
2641: ,p_val_tab IN OUT NOCOPY ValTabTyp) IS
2642:
2643: l_proc_name VARCHAR2(150) := g_proc_name ||'Copy_Rec_Values ';
2644: BEGIN
2645: Hr_Utility.set_location('Entering: '||l_proc_name, 5);
2646:
2647: p_val_tab(1) := p_rslt_rec.val_01;
2648: p_val_tab(2) := p_rslt_rec.val_02;
2649: p_val_tab(3) := p_rslt_rec.val_03;
2725: p_val_tab(72) := p_rslt_rec.val_72;
2726: p_val_tab(73) := p_rslt_rec.val_73;
2727: p_val_tab(74) := p_rslt_rec.val_74;
2728: p_val_tab(75) := p_rslt_rec.val_75;
2729: Hr_Utility.set_location('Leaving: '||l_proc_name, 15);
2730:
2731: END Copy_Rec_Values;
2732:
2733: -- =============================================================================
2759: l_conc_reqest_id NUMBER(20);
2760: l_exists VARCHAR2(2);
2761: l_proc_name CONSTANT VARCHAR2(150) := g_proc_name ||'Exclude_Person';
2762: BEGIN
2763: Hr_Utility.set_location('Entering: '||l_proc_name, 5);
2764: OPEN csr_ben_per (c_person_id => p_person_id
2765: ,c_benefit_action_id => p_benefit_action_id);
2766: FETCH csr_ben_per INTO l_ben_per;
2767: CLOSE csr_ben_per;
2781: AND l_ben_per.person_action_id
2782: BETWEEN bbr.starting_person_action_id
2783: AND bbr.ending_person_action_id;
2784: END IF;
2785: Hr_Utility.set_location('Leaving: '||l_proc_name, 80);
2786:
2787: END Exclude_Person;
2788:
2789: -- =============================================================================
2862: l_rollback_person BOOLEAN;
2863: l_rslt_dtl_id NUMBER;
2864: --
2865: BEGIN
2866: Hr_Utility.set_location('Entering: '||l_proc_name, 5);
2867: FOR i IN 1..75
2868: LOOP
2869: l_val_tab(i) := NULL;
2870: END LOOP;
2895: BEGIN
2896: IF i.frmt_mask_lookup_cd IS NOT NULL AND
2897: l_ff_value IS NOT NULL THEN
2898: IF Substr(i.frmt_mask_lookup_cd,1,1) = 'N' THEN
2899: Hr_Utility.set_location('..Applying NUMBER format mask :ben_ext_fmt.apply_format_mask',50);
2900: l_ff_value_fmt := Ben_Ext_Fmt.apply_format_mask(To_Number(l_ff_value), i.frmt_mask_cd);
2901: l_ff_value := l_ff_value_fmt;
2902: ELSIF Substr(i.frmt_mask_lookup_cd,1,1) = 'D' THEN
2903: Hr_Utility.set_location('..Applying Date format mask :ben_ext_fmt.apply_format_mask',55);
2899: Hr_Utility.set_location('..Applying NUMBER format mask :ben_ext_fmt.apply_format_mask',50);
2900: l_ff_value_fmt := Ben_Ext_Fmt.apply_format_mask(To_Number(l_ff_value), i.frmt_mask_cd);
2901: l_ff_value := l_ff_value_fmt;
2902: ELSIF Substr(i.frmt_mask_lookup_cd,1,1) = 'D' THEN
2903: Hr_Utility.set_location('..Applying Date format mask :ben_ext_fmt.apply_format_mask',55);
2904: l_ff_value_fmt := Ben_Ext_Fmt.apply_format_mask(Fnd_Date.canonical_to_date(l_ff_value),
2905: i.frmt_mask_cd);
2906: l_ff_value := l_ff_value_fmt;
2907: END IF;
2918: l_ff_value := Substr(l_ff_value, -l_max_len);
2919: ELSE -- everything else truncs from the right.
2920: l_ff_value := Substr(l_ff_value, 1, i.max_length_num);
2921: END IF;
2922: Hr_Utility.set_location('..After Max Length : '|| l_ff_value,56 );
2923: END IF;
2924: -- If the data element is required, and null then exit
2925: -- no need to re-execute the other data-elements in the record.
2926: IF i.rqd_flag = 'Y' AND (l_ff_value IS NULL) THEN
2997: ,p_benefit_action_id => l_ben_params.benefit_action_id
2998: ,p_flag_thread => 'N');
2999:
3000: END IF;
3001: Hr_Utility.set_location('Leaving: '||l_proc_name, 80);
3002:
3003: EXCEPTION
3004: WHEN Others THEN
3005: p_error_message :='SQL-ERRM :'||SQLERRM;
3002:
3003: EXCEPTION
3004: WHEN Others THEN
3005: p_error_message :='SQL-ERRM :'||SQLERRM;
3006: Hr_Utility.set_location('..'||p_error_message,85);
3007: Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
3008:
3009: END Process_Ext_Rslt_Dtl_Rec;
3010: -- =============================================================================
3003: EXCEPTION
3004: WHEN Others THEN
3005: p_error_message :='SQL-ERRM :'||SQLERRM;
3006: Hr_Utility.set_location('..'||p_error_message,85);
3007: Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
3008:
3009: END Process_Ext_Rslt_Dtl_Rec;
3010: -- =============================================================================
3011: -- Create_AsgAction_Lines:
3023: l_main_rec csr_rslt_dtl%ROWTYPE;
3024: l_new_rec csr_rslt_dtl%ROWTYPE;
3025: l_effective_date DATE;
3026: BEGIN
3027: Hr_Utility.set_location('Entering: '||l_proc_name, 5);
3028: FOR csr_rcd_rec IN csr_ext_rcd_id(c_hide_flag => 'N' -- N=No Y=Yes
3029: ,c_rcd_type_cd => 'D')-- D=Detail, T=Total, H-Header
3030: LOOP
3031: g_ext_dtl_rcd_id := csr_rcd_rec.ext_rcd_id;
3052: ,p_rslt_rec => l_main_rec
3053: ,p_asgaction_no => p_asgaction_no
3054: ,p_error_message => p_error_message);
3055: END LOOP;
3056: Hr_Utility.set_location('Leaving: '||l_proc_name, 80);
3057: EXCEPTION
3058: WHEN Others THEN
3059: p_error_message :='SQL-ERRM :'||SQLERRM;
3060: Hr_Utility.set_location('..'||p_error_message,85);
3056: Hr_Utility.set_location('Leaving: '||l_proc_name, 80);
3057: EXCEPTION
3058: WHEN Others THEN
3059: p_error_message :='SQL-ERRM :'||SQLERRM;
3060: Hr_Utility.set_location('..'||p_error_message,85);
3061: Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
3062:
3063: END Create_AsgAction_Lines;
3064:
3057: EXCEPTION
3058: WHEN Others THEN
3059: p_error_message :='SQL-ERRM :'||SQLERRM;
3060: Hr_Utility.set_location('..'||p_error_message,85);
3061: Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
3062:
3063: END Create_AsgAction_Lines;
3064:
3065: -- =============================================================================
3081: l_ER_element_id pay_element_types_f.element_type_id%TYPE;
3082:
3083: l_ext_dfn_type pqp_extract_attributes.ext_dfn_type%TYPE;
3084: BEGIN
3085: Hr_Utility.set_location('Entering: '||l_proc_name, 5);
3086: -- If element set was selected
3087: l_ext_dfn_type := g_extract_params(p_business_group_id).ext_dfn_type;
3088: FOR ele_rec IN csr_ele_id (c_element_set_id => p_element_set_id)
3089: LOOP
3208: l_prev_ele_type_id := l_ele_type_id;
3209: l_ele_type_id := g_element.NEXT(l_prev_ele_type_id);
3210: END LOOP; -- While Loop
3211:
3212: Hr_Utility.set_location('Leaving: '||l_proc_name, 80);
3213: END Get_Element_Details;
3214:
3215: -- =============================================================================
3216: -- Get_Element_Count:
3243: l_input_value_id pay_input_values_f.input_value_id%TYPE;
3244: l_report_dimension VARCHAR2(100);
3245: l_proc_name VARCHAR2(150) := g_proc_name ||'Get_Element_Count';
3246: BEGIN
3247: Hr_Utility.set_location('Entering: '||l_proc_name, 5);
3248: l_report_dimension := g_extract_params(g_business_group_id).reporting_dimension;
3249: IF p_ele_type ='PRIMARY' THEN
3250: IF g_element(p_ele_type_id).information_category = 'US_PRE-TAX DEDUCTIONS' AND
3251: g_element(p_ele_type_id).pretax_category NOT IN ('DC','EC','GC') THEN
3325: l_return_value := g_AfterTax.Ele_Count;
3326: END IF;
3327:
3328: END IF;
3329: Hr_Utility.set_location(' g_AfterTax.Count : '||l_return_value,6);
3330: ELSIF p_ele_type ='ROTH' THEN
3331:
3332: IF g_element(p_ele_type_id).information_category = 'US_PRE-TAX DEDUCTIONS' THEN
3333:
3383: IF l_report_dimension = 'ASG_RUN' THEN
3384: l_return_value := g_Roth.Ele_Count;
3385: END IF;
3386: END IF;
3387: Hr_Utility.set_location(' g_Roth.Count : '||l_return_value,6);
3388: ELSIF p_ele_type ='CATCHUP' THEN
3389:
3390: IF g_element(p_ele_type_id).information_category = 'US_PRE-TAX DEDUCTIONS' AND
3391: g_element(p_ele_type_id).pretax_category IN ('DC','EC','GC') THEN
3404: END IF; --l_report_dimension = 'ASG_RUN'
3405:
3406: ELSIF g_element(p_ele_type_id).information_category = 'US_PRE-TAX DEDUCTIONS' THEN
3407: IF g_element(p_ele_type_id).CatchUp_ele_type_id IS NOT NULL THEN
3408: Hr_Utility.set_location('..CatchUp l_ele_type_id : '||l_ele_type_id,6);
3409: IF l_report_dimension = 'ASG_RUN' THEN
3410: -- Only if the reporting dimension is ASG_RUN then check for run results
3411: OPEN csr_ele_run
3412: (c_asg_action_id => p_asg_action_id
3493: l_return_value := 0;
3494: END IF; -- csr_chk_entry%FOUND
3495: CLOSE csr_chk_entry;
3496: END IF;
3497: Hr_Utility.set_location(' l_return_value: '||l_return_value, 70);
3498: Hr_Utility.set_location(' p_ele_type: '||p_ele_type, 70);
3499: Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
3500: RETURN l_return_value;
3501: EXCEPTION
3494: END IF; -- csr_chk_entry%FOUND
3495: CLOSE csr_chk_entry;
3496: END IF;
3497: Hr_Utility.set_location(' l_return_value: '||l_return_value, 70);
3498: Hr_Utility.set_location(' p_ele_type: '||p_ele_type, 70);
3499: Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
3500: RETURN l_return_value;
3501: EXCEPTION
3502: WHEN Others THEN
3495: CLOSE csr_chk_entry;
3496: END IF;
3497: Hr_Utility.set_location(' l_return_value: '||l_return_value, 70);
3498: Hr_Utility.set_location(' p_ele_type: '||p_ele_type, 70);
3499: Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
3500: RETURN l_return_value;
3501: EXCEPTION
3502: WHEN Others THEN
3503: l_return_value := 0;
3500: RETURN l_return_value;
3501: EXCEPTION
3502: WHEN Others THEN
3503: l_return_value := 0;
3504: Hr_Utility.set_location('..SQL-ERRM :'||SQLERRM,85);
3505: Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
3506: RETURN l_return_value;
3507: END Get_Element_Count;
3508: -- =============================================================================
3501: EXCEPTION
3502: WHEN Others THEN
3503: l_return_value := 0;
3504: Hr_Utility.set_location('..SQL-ERRM :'||SQLERRM,85);
3505: Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
3506: RETURN l_return_value;
3507: END Get_Element_Count;
3508: -- =============================================================================
3509: -- Process_Assignments:
3522: i per_all_assignments_f.business_group_id%TYPE;
3523: l_ext_dfn_type pqp_extract_attributes.ext_dfn_type%TYPE;
3524: l_proc_name VARCHAR2(150) := g_proc_name ||'Process_Assignments';
3525: BEGIN
3526: Hr_Utility.set_location('Entering: '||l_proc_name, 5);
3527: i := p_business_group_id;
3528: l_ext_dfn_type := g_extract_params(i).ext_dfn_type;
3529: IF g_extract_params(i).reporting_dimension = 'ASG_RUN' THEN
3530: -- Reporting Dimension is ASG_RUN
3624: ,p_asgaction_no => p_no_asg_action
3625: ,p_error_message => p_error_message);
3626:
3627: END IF; -- If reporting_dimension = 'ASG_RUN'
3628: Hr_Utility.set_location('Leaving: '||l_proc_name, 80);
3629:
3630: END Process_Assignments;
3631:
3632: -- =============================================================================
3655: l_main_rec csr_rslt_dtl%ROWTYPE;
3656: l_person_id per_all_people_f.person_id%TYPE;
3657: l_assignment_id per_all_assignments_f.assignment_id%TYPE;
3658: BEGIN
3659: Hr_Utility.set_location('Entering: '||l_proc_name, 5);
3660: i := p_business_group_id;
3661:
3662: IF NOT g_primary_assig.EXISTS(p_assignment_id) THEN
3663: l_return_value := 'NOTFOUND';
3660: i := p_business_group_id;
3661:
3662: IF NOT g_primary_assig.EXISTS(p_assignment_id) THEN
3663: l_return_value := 'NOTFOUND';
3664: Hr_Utility.set_location('..Not a Valid assignment: '||p_assignment_id, 6);
3665: RETURN l_return_value;
3666: ELSIF g_primary_assig(p_assignment_id).assignment_type IN ('B','E') THEN
3667:
3668: l_person_id := g_primary_assig(p_assignment_id).person_id;
3666: ELSIF g_primary_assig(p_assignment_id).assignment_type IN ('B','E') THEN
3667:
3668: l_person_id := g_primary_assig(p_assignment_id).person_id;
3669: l_asg_type := g_primary_assig(p_assignment_id).assignment_type;
3670: Hr_Utility.set_location('..Valid Assignment Type : '||l_asg_type, 6);
3671: -- Check if there are any other assignments which might be active within the
3672: -- specified extract date range
3673: FOR sec_asg_rec IN csr_sec_assg
3674: (c_primary_assignment_id => p_assignment_id
3680: l_sec_assg_rec := sec_asg_rec;
3681: l_criteria_value := 'N';
3682: l_effective_date := Least(g_extract_params(i).extract_end_date,
3683: l_sec_assg_rec.effective_end_date);
3684: Hr_Utility.set_location('..Checking for assignment : '||l_sec_assg_rec.assignment_id, 7);
3685: Hr_Utility.set_location('..p_effective_date : '||l_effective_date, 7);
3686: -- Call the main criteria function for this assignment to check if its a valid
3687: -- assignment that can be reported based on the criteria specified.
3688: l_criteria_value := Pension_Criteria_Full_Profile
3681: l_criteria_value := 'N';
3682: l_effective_date := Least(g_extract_params(i).extract_end_date,
3683: l_sec_assg_rec.effective_end_date);
3684: Hr_Utility.set_location('..Checking for assignment : '||l_sec_assg_rec.assignment_id, 7);
3685: Hr_Utility.set_location('..p_effective_date : '||l_effective_date, 7);
3686: -- Call the main criteria function for this assignment to check if its a valid
3687: -- assignment that can be reported based on the criteria specified.
3688: l_criteria_value := Pension_Criteria_Full_Profile
3689: (p_assignment_id => l_sec_assg_rec.assignment_id
3694: );
3695:
3696: END LOOP;
3697: END IF;
3698: Hr_Utility.set_location('..Assignment Count : '||g_primary_assig.Count, 7);
3699: Hr_Utility.set_location('..l_person_id : '||l_person_id, 7);
3700: -- For each assignment for this person id check if additional rows need to be
3701: -- created and re-calculate the person level based fast-formulas.
3702: g_total_dtl_lines := 0;
3695:
3696: END LOOP;
3697: END IF;
3698: Hr_Utility.set_location('..Assignment Count : '||g_primary_assig.Count, 7);
3699: Hr_Utility.set_location('..l_person_id : '||l_person_id, 7);
3700: -- For each assignment for this person id check if additional rows need to be
3701: -- created and re-calculate the person level based fast-formulas.
3702: g_total_dtl_lines := 0;
3703: l_assignment_id := g_primary_assig.FIRST;
3702: g_total_dtl_lines := 0;
3703: l_assignment_id := g_primary_assig.FIRST;
3704: WHILE l_assignment_id IS NOT NULL
3705: LOOP
3706: Hr_Utility.set_location('..Checking for assignment : '||l_assignment_id, 7);
3707: IF g_primary_assig(l_assignment_id).person_id = l_person_id AND
3708: g_primary_assig(l_assignment_id).Assignment_Type = 'E' THEN
3709: Hr_Utility.set_location('..Valid Assignment : '||l_assignment_id, 8);
3710: Hr_Utility.set_location('..l_no_asg_action : '||l_no_asg_action, 8);
3705: LOOP
3706: Hr_Utility.set_location('..Checking for assignment : '||l_assignment_id, 7);
3707: IF g_primary_assig(l_assignment_id).person_id = l_person_id AND
3708: g_primary_assig(l_assignment_id).Assignment_Type = 'E' THEN
3709: Hr_Utility.set_location('..Valid Assignment : '||l_assignment_id, 8);
3710: Hr_Utility.set_location('..l_no_asg_action : '||l_no_asg_action, 8);
3711: g_primary_assig(l_assignment_id).Calculate_Amount := 'YES';
3712: Process_Assignments
3713: (p_assignment_id => l_assignment_id
3706: Hr_Utility.set_location('..Checking for assignment : '||l_assignment_id, 7);
3707: IF g_primary_assig(l_assignment_id).person_id = l_person_id AND
3708: g_primary_assig(l_assignment_id).Assignment_Type = 'E' THEN
3709: Hr_Utility.set_location('..Valid Assignment : '||l_assignment_id, 8);
3710: Hr_Utility.set_location('..l_no_asg_action : '||l_no_asg_action, 8);
3711: g_primary_assig(l_assignment_id).Calculate_Amount := 'YES';
3712: Process_Assignments
3713: (p_assignment_id => l_assignment_id
3714: ,p_business_group_id => p_business_group_id
3755: g_primary_assig.DELETE(l_assignment_id);
3756: END IF;
3757: l_assignment_id := g_primary_assig.NEXT(l_assignment_id);
3758: END LOOP;
3759: Hr_Utility.set_location('Leaving: '||l_proc_name, 80);
3760: RETURN l_return_value;
3761: EXCEPTION
3762: WHEN Others THEN
3763: p_error_message :='SQL-ERRM :'||SQLERRM;
3760: RETURN l_return_value;
3761: EXCEPTION
3762: WHEN Others THEN
3763: p_error_message :='SQL-ERRM :'||SQLERRM;
3764: Hr_Utility.set_location('..'||p_error_message,85);
3765: Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
3766: RETURN l_return_value;
3767: END Check_Asg_Actions;
3768:
3761: EXCEPTION
3762: WHEN Others THEN
3763: p_error_message :='SQL-ERRM :'||SQLERRM;
3764: Hr_Utility.set_location('..'||p_error_message,85);
3765: Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
3766: RETURN l_return_value;
3767: END Check_Asg_Actions;
3768:
3769: -- =============================================================================
3784: l_prev_ele_type_id pay_element_types_f.element_type_id%TYPE;
3785: l_screen_entry_value pay_element_entry_values_f.screen_entry_value%TYPE;
3786: l_Count NUMBER(5) := 0;
3787: BEGIN
3788: Hr_Utility.set_location('Entering: '||l_proc_name, 5);
3789: -- If its for a single Assig. action, then take global variable
3790: IF g_extract_params(p_business_group_id).reporting_dimension = 'ASG_RUN' AND
3791: g_primary_assig(p_assignment_id).Calculate_Amount = 'YES' THEN
3792: IF p_ele_type = 'AT' AND g_AfterTax.Ele_Count = 1 THEN
3791: g_primary_assig(p_assignment_id).Calculate_Amount = 'YES' THEN
3792: IF p_ele_type = 'AT' AND g_AfterTax.Ele_Count = 1 THEN
3793: l_ele_type_id := g_AfterTax.ele_type_id;
3794: l_input_value_id := g_AfterTax.input_value_id;
3795: Hr_Utility.set_location('..Ele_Count: '||g_AfterTax.Ele_Count,6);
3796: ELSIF p_ele_type = 'CATCHUP' AND g_CatchUp.Ele_Count = 1 THEN
3797: l_ele_type_id := g_CatchUp.ele_type_id;
3798: l_input_value_id := g_CatchUp.input_value_id;
3799: Hr_Utility.set_location('..Ele_Count: '||g_CatchUp.Ele_Count,6);
3795: Hr_Utility.set_location('..Ele_Count: '||g_AfterTax.Ele_Count,6);
3796: ELSIF p_ele_type = 'CATCHUP' AND g_CatchUp.Ele_Count = 1 THEN
3797: l_ele_type_id := g_CatchUp.ele_type_id;
3798: l_input_value_id := g_CatchUp.input_value_id;
3799: Hr_Utility.set_location('..Ele_Count: '||g_CatchUp.Ele_Count,6);
3800: ELSIF p_ele_type = 'PRIMARY' AND g_PreTax.Ele_Count = 1 THEN
3801: l_ele_type_id := g_PreTax.ele_type_id;
3802: l_input_value_id := g_PreTax.input_value_id;
3803: Hr_Utility.set_location('..Ele_Count: '||g_PreTax.Ele_Count,6);
3799: Hr_Utility.set_location('..Ele_Count: '||g_CatchUp.Ele_Count,6);
3800: ELSIF p_ele_type = 'PRIMARY' AND g_PreTax.Ele_Count = 1 THEN
3801: l_ele_type_id := g_PreTax.ele_type_id;
3802: l_input_value_id := g_PreTax.input_value_id;
3803: Hr_Utility.set_location('..Ele_Count: '||g_PreTax.Ele_Count,6);
3804: ELSIF p_ele_type = 'ROTH' AND g_Roth.Ele_Count = 1 THEN
3805: l_ele_type_id := g_Roth.ele_type_id;
3806: l_input_value_id := g_Roth.input_value_id;
3807: Hr_Utility.set_location('..Ele_Count: '||g_Roth.Ele_Count,6);
3803: Hr_Utility.set_location('..Ele_Count: '||g_PreTax.Ele_Count,6);
3804: ELSIF p_ele_type = 'ROTH' AND g_Roth.Ele_Count = 1 THEN
3805: l_ele_type_id := g_Roth.ele_type_id;
3806: l_input_value_id := g_Roth.input_value_id;
3807: Hr_Utility.set_location('..Ele_Count: '||g_Roth.Ele_Count,6);
3808: END IF;
3809:
3810: OPEN csr_run(c_asg_action_id => g_asg_action_id
3811: ,c_element_type_id => l_ele_type_id
3811: ,c_element_type_id => l_ele_type_id
3812: ,c_input_value_id => l_input_value_id);
3813: FETCH csr_run INTO l_return_value;
3814: IF csr_run%NOTFOUND THEN
3815: Hr_Utility.set_location('..p_ele_type: '||p_ele_type,10);
3816: Hr_Utility.set_location('..Ele_Count: '||g_AfterTax.Ele_Count,6);
3817: Hr_Utility.set_location('..Ele_Count: '||g_Roth.Ele_Count,6);
3818: Hr_Utility.set_location('..Run result not found',10);
3819: l_return_value := 0;
3812: ,c_input_value_id => l_input_value_id);
3813: FETCH csr_run INTO l_return_value;
3814: IF csr_run%NOTFOUND THEN
3815: Hr_Utility.set_location('..p_ele_type: '||p_ele_type,10);
3816: Hr_Utility.set_location('..Ele_Count: '||g_AfterTax.Ele_Count,6);
3817: Hr_Utility.set_location('..Ele_Count: '||g_Roth.Ele_Count,6);
3818: Hr_Utility.set_location('..Run result not found',10);
3819: l_return_value := 0;
3820: END IF;
3813: FETCH csr_run INTO l_return_value;
3814: IF csr_run%NOTFOUND THEN
3815: Hr_Utility.set_location('..p_ele_type: '||p_ele_type,10);
3816: Hr_Utility.set_location('..Ele_Count: '||g_AfterTax.Ele_Count,6);
3817: Hr_Utility.set_location('..Ele_Count: '||g_Roth.Ele_Count,6);
3818: Hr_Utility.set_location('..Run result not found',10);
3819: l_return_value := 0;
3820: END IF;
3821: Hr_Utility.set_location('ASG l_return_value :'||l_return_value , 5);
3814: IF csr_run%NOTFOUND THEN
3815: Hr_Utility.set_location('..p_ele_type: '||p_ele_type,10);
3816: Hr_Utility.set_location('..Ele_Count: '||g_AfterTax.Ele_Count,6);
3817: Hr_Utility.set_location('..Ele_Count: '||g_Roth.Ele_Count,6);
3818: Hr_Utility.set_location('..Run result not found',10);
3819: l_return_value := 0;
3820: END IF;
3821: Hr_Utility.set_location('ASG l_return_value :'||l_return_value , 5);
3822: CLOSE csr_run;
3817: Hr_Utility.set_location('..Ele_Count: '||g_Roth.Ele_Count,6);
3818: Hr_Utility.set_location('..Run result not found',10);
3819: l_return_value := 0;
3820: END IF;
3821: Hr_Utility.set_location('ASG l_return_value :'||l_return_value , 5);
3822: CLOSE csr_run;
3823:
3824: ELSIF g_extract_params(p_business_group_id).reporting_dimension <> 'ASG_RUN' THEN
3825: -- For any other reporting dimension
3908: g_PreTax.Ele_Count := 0; g_PreTax.input_value_id := NULL; g_PreTax.ele_type_id := NULL;
3909: g_CatchUp.Ele_Count := 0; g_CatchUp.input_value_id := NULL; g_CatchUp.ele_type_id := NULL;
3910: g_Roth.Ele_Count := 0; g_Roth.input_value_id := NULL; g_Roth.ele_type_id := NULL;
3911: END IF; -- If dimension <> ASG_RUN
3912: Hr_Utility.set_location('Leaving: '||l_proc_name, 80);
3913: RETURN l_return_value;
3914:
3915: EXCEPTION
3916: WHEN Others THEN
3914:
3915: EXCEPTION
3916: WHEN Others THEN
3917: p_error_message :='SQL-ERRM :'||SQLERRM;
3918: Hr_Utility.set_location('..'||p_error_message,85);
3919: Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
3920: RETURN l_return_value;
3921:
3922: END Get_Contr_AmtPer;
3915: EXCEPTION
3916: WHEN Others THEN
3917: p_error_message :='SQL-ERRM :'||SQLERRM;
3918: Hr_Utility.set_location('..'||p_error_message,85);
3919: Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
3920: RETURN l_return_value;
3921:
3922: END Get_Contr_AmtPer;
3923:
3969: l_pay_basis_id per_all_assignments_f.pay_basis_id%TYPE;
3970: l_annualization_factor per_pay_bases.pay_annualization_factor%TYPE;
3971:
3972: BEGIN
3973: Hr_Utility.set_location('Entering: '||l_proc_name, 5);
3974: i := p_business_group_id;
3975:
3976: IF g_primary_assig.EXISTS(p_assignment_id) THEN
3977: IF p_data_ele_name = 'EMPLOYMENT_CATEGORY' THEN
4011: nvl(l_annualization_factor,0)
4012: ,2);
4013: END IF;
4014: END IF;
4015: Hr_Utility.set_location(' ..p_data_ele_name : '||p_data_ele_name, 80);
4016: Hr_Utility.set_location(' ..l_return_value : '||l_return_value, 80);
4017: Hr_Utility.set_location('Leaving: '||l_proc_name, 80);
4018: RETURN l_return_value;
4019: EXCEPTION
4012: ,2);
4013: END IF;
4014: END IF;
4015: Hr_Utility.set_location(' ..p_data_ele_name : '||p_data_ele_name, 80);
4016: Hr_Utility.set_location(' ..l_return_value : '||l_return_value, 80);
4017: Hr_Utility.set_location('Leaving: '||l_proc_name, 80);
4018: RETURN l_return_value;
4019: EXCEPTION
4020: WHEN Others THEN
4013: END IF;
4014: END IF;
4015: Hr_Utility.set_location(' ..p_data_ele_name : '||p_data_ele_name, 80);
4016: Hr_Utility.set_location(' ..l_return_value : '||l_return_value, 80);
4017: Hr_Utility.set_location('Leaving: '||l_proc_name, 80);
4018: RETURN l_return_value;
4019: EXCEPTION
4020: WHEN Others THEN
4021: p_error_message :='SQL-ERRM :'||SQLERRM;
4018: RETURN l_return_value;
4019: EXCEPTION
4020: WHEN Others THEN
4021: p_error_message :='SQL-ERRM :'||SQLERRM;
4022: Hr_Utility.set_location('..'||p_error_message,85);
4023: Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
4024: RETURN l_return_value;
4025: END Get_Data_Elements;
4026:
4019: EXCEPTION
4020: WHEN Others THEN
4021: p_error_message :='SQL-ERRM :'||SQLERRM;
4022: Hr_Utility.set_location('..'||p_error_message,85);
4023: Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
4024: RETURN l_return_value;
4025: END Get_Data_Elements;
4026:
4027: -- =============================================================================
4035: ) RETURN VARCHAR2 IS
4036: l_proc_name VARCHAR2(150) := g_proc_name ||'Get_Payroll_Date';
4037: l_return_value VARCHAR2(150);
4038: BEGIN
4039: Hr_Utility.set_location('Entering: '||l_proc_name, 5);
4040: IF g_extract_params(p_business_group_id).reporting_dimension <> 'ASG_RUN' THEN
4041: l_return_value := g_extract_params(p_business_group_id).extract_end_date;
4042: ELSE
4043: l_return_value := Fnd_Date.Date_To_Canonical(g_action_effective_date);
4042: ELSE
4043: l_return_value := Fnd_Date.Date_To_Canonical(g_action_effective_date);
4044: END IF;
4045: RETURN l_return_value;
4046: Hr_Utility.set_location('Leaving: '||l_proc_name, 80);
4047: EXCEPTION
4048: WHEN Others THEN
4049: p_error_message :='SQL-ERRM :'||SQLERRM;
4050: Hr_Utility.set_location('..'||p_error_message,85);
4046: Hr_Utility.set_location('Leaving: '||l_proc_name, 80);
4047: EXCEPTION
4048: WHEN Others THEN
4049: p_error_message :='SQL-ERRM :'||SQLERRM;
4050: Hr_Utility.set_location('..'||p_error_message,85);
4051: Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
4052: RETURN l_return_value;
4053: END Get_Payroll_Date;
4054:
4047: EXCEPTION
4048: WHEN Others THEN
4049: p_error_message :='SQL-ERRM :'||SQLERRM;
4050: Hr_Utility.set_location('..'||p_error_message,85);
4051: Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
4052: RETURN l_return_value;
4053: END Get_Payroll_Date;
4054:
4055: -- =============================================================================
4063:
4064: l_balance_type_id pay_balance_types.balance_type_id%TYPE;
4065: l_proc_name VARCHAR2(150) := g_proc_name ||'Get_Balance_Id';
4066: BEGIN
4067: Hr_Utility.set_location('Entering: '||l_proc_name, 5);
4068:
4069: IF p_balance_name = 'PRIMARY' THEN
4070: IF g_element(p_ele_type_id).information_category ='US_PRE-TAX DEDUCTIONS' AND
4071: g_element(p_ele_type_id).pretax_category NOT IN ('DC','EC','GC') THEN
4116: ELSIF g_element(p_ele_type_id).information_category ='US_PRE-TAX DEDUCTIONS' THEN
4117: l_balance_type_id := g_element(p_ele_type_id).ATER_Balance_id;
4118: END IF;
4119: END IF;
4120: Hr_Utility.set_location(' l_balance_type_id: '||l_balance_type_id, 75);
4121: Hr_Utility.set_location('Leaving: '||l_proc_name, 80);
4122: RETURN l_balance_type_id;
4123: EXCEPTION
4124: WHEN Others THEN
4117: l_balance_type_id := g_element(p_ele_type_id).ATER_Balance_id;
4118: END IF;
4119: END IF;
4120: Hr_Utility.set_location(' l_balance_type_id: '||l_balance_type_id, 75);
4121: Hr_Utility.set_location('Leaving: '||l_proc_name, 80);
4122: RETURN l_balance_type_id;
4123: EXCEPTION
4124: WHEN Others THEN
4125: p_error_message :='SQL-ERRM :'||SQLERRM;
4122: RETURN l_balance_type_id;
4123: EXCEPTION
4124: WHEN Others THEN
4125: p_error_message :='SQL-ERRM :'||SQLERRM;
4126: Hr_Utility.set_location('..'||p_error_message,85);
4127: Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
4128: RETURN l_balance_type_id;
4129: END Get_Balance_Id;
4130:
4123: EXCEPTION
4124: WHEN Others THEN
4125: p_error_message :='SQL-ERRM :'||SQLERRM;
4126: Hr_Utility.set_location('..'||p_error_message,85);
4127: Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
4128: RETURN l_balance_type_id;
4129: END Get_Balance_Id;
4130:
4131: -- =============================================================================
4149: i per_all_assignments_f.business_group_id%TYPE;
4150: l_valid_action VARCHAR2(2);
4151: l_proc_name VARCHAR2(150) := g_proc_name ||'Get_Deduction_Amount';
4152: BEGIN
4153: Hr_Utility.set_location('Entering: '||l_proc_name, 5);
4154: l_bal_total_amt := 0;
4155: l_balance_amount := 0;
4156:
4157: -- Check if this assignment was process in the criteria func. else return
4189: FETCH csr_asg_balid INTO l_defined_balance_id;
4190: -- If def. balance id was not found then return 0, as it could be that
4191: -- component i.e. CatchUp or After-Tax were not created.
4192: IF csr_asg_balid%NOTFOUND THEN
4193: Hr_Utility.set_location('..Def. Balance Id not found', 5);
4194: END IF;
4195: CLOSE csr_asg_balid;
4196: l_balance_amount := Pay_Balance_Pkg.get_value
4197: (p_defined_balance_id => l_defined_balance_id,
4250: l_ele_type_id := g_element.NEXT(l_prev_ele_type_id);
4251: END LOOP; -- While Loop
4252: END LOOP; -- For Loop
4253: END IF;
4254: Hr_Utility.set_location('Leaving: '||l_proc_name, 80);
4255: RETURN l_bal_total_amt;
4256: EXCEPTION
4257: WHEN Others THEN
4258: p_error_message :='SQL-ERRM :'||SQLERRM;
4255: RETURN l_bal_total_amt;
4256: EXCEPTION
4257: WHEN Others THEN
4258: p_error_message :='SQL-ERRM :'||SQLERRM;
4259: Hr_Utility.set_location('..'||p_error_message,85);
4260: Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
4261: RETURN l_bal_total_amt;
4262: END Get_Deduction_Amount;
4263:
4256: EXCEPTION
4257: WHEN Others THEN
4258: p_error_message :='SQL-ERRM :'||SQLERRM;
4259: Hr_Utility.set_location('..'||p_error_message,85);
4260: Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
4261: RETURN l_bal_total_amt;
4262: END Get_Deduction_Amount;
4263:
4264: -- ===============================================================================
4273:
4274:
4275: BEGIN
4276:
4277: Hr_Utility.set_location('Entering: '||l_proc_name, 5);
4278: IF p_header_type = 'EXTRACT_NAME' THEN
4279: l_return_value := g_conc_prog_details(0).extract_name;
4280: ELSIF p_header_type = 'REPORT_OPTION' THEN
4281: l_return_value := g_conc_prog_details(0).reporting_options;
4291: l_return_value := g_conc_prog_details(0).endingdt;
4292: ELSIF p_header_type = 'GRE' THEN
4293: l_return_value := g_conc_prog_details(0).grename;
4294: ELSIF p_header_type = 'PAYROLL_NAME' THEN
4295: Hr_Utility.set_location('PAYROLL_NAME: '||g_conc_prog_details(0).payrollname, 5);
4296: l_return_value := g_conc_prog_details(0).payrollname;
4297: ELSIF p_header_type = 'CON_SET' THEN
4298: l_return_value := g_conc_prog_details(0).consolset;
4299: Hr_Utility.set_location('CON_SET: '||l_return_value, 5);
4295: Hr_Utility.set_location('PAYROLL_NAME: '||g_conc_prog_details(0).payrollname, 5);
4296: l_return_value := g_conc_prog_details(0).payrollname;
4297: ELSIF p_header_type = 'CON_SET' THEN
4298: l_return_value := g_conc_prog_details(0).consolset;
4299: Hr_Utility.set_location('CON_SET: '||l_return_value, 5);
4300: END IF;
4301: Hr_Utility.set_location('Leaving: '||l_proc_name, 45);
4302:
4303: RETURN l_return_value;
4297: ELSIF p_header_type = 'CON_SET' THEN
4298: l_return_value := g_conc_prog_details(0).consolset;
4299: Hr_Utility.set_location('CON_SET: '||l_return_value, 5);
4300: END IF;
4301: Hr_Utility.set_location('Leaving: '||l_proc_name, 45);
4302:
4303: RETURN l_return_value;
4304: EXCEPTION
4305: WHEN Others THEN
4303: RETURN l_return_value;
4304: EXCEPTION
4305: WHEN Others THEN
4306: p_error_message :='SQL-ERRM :'||SQLERRM;
4307: Hr_Utility.set_location('..Exception Others Raised at Get_ConcProg_Information'||p_error_message,40);
4308: Hr_Utility.set_location('Leaving: '||l_proc_name, 45);
4309: RETURN l_return_value;
4310: END Get_ConcProg_Information;
4311:
4304: EXCEPTION
4305: WHEN Others THEN
4306: p_error_message :='SQL-ERRM :'||SQLERRM;
4307: Hr_Utility.set_location('..Exception Others Raised at Get_ConcProg_Information'||p_error_message,40);
4308: Hr_Utility.set_location('Leaving: '||l_proc_name, 45);
4309: RETURN l_return_value;
4310: END Get_ConcProg_Information;
4311:
4312: -- =============================================================================
4334: l_index NUMBER :=0;
4335: l_proc_name VARCHAR2(150) := g_proc_name ||'Get_Element_Entry_Value';
4336:
4337: BEGIN
4338: Hr_Utility.set_location('Entering: '||l_proc_name, 5);
4339: l_legislation_code := g_extract_params(p_business_group_id).legislation_code;
4340:
4341: IF g_extract_params(p_business_group_id).reporting_dimension = 'ASG_RUN' THEN
4342: l_effective_date := g_action_effective_date;
4393: FETCH csr_entry INTO l_screen_entry_value;
4394: CLOSE csr_entry;
4395: l_return_value := l_screen_entry_value;
4396: END IF;
4397: Hr_Utility.set_location('Leaving: '||l_proc_name, 10);
4398: RETURN l_return_value;
4399: EXCEPTION
4400: WHEN Others THEN
4401: l_error_message := ' Error:'||SQLERRM;
4399: EXCEPTION
4400: WHEN Others THEN
4401: l_error_message := ' Error:'||SQLERRM;
4402: p_error_message := l_error_message;
4403: Hr_Utility.set_location('..'||p_error_message,10);
4404: Hr_Utility.set_location('Leaving: '||l_proc_name, 11);
4405: RETURN l_return_value;
4406:
4407: END Get_Element_Entry_Value;
4400: WHEN Others THEN
4401: l_error_message := ' Error:'||SQLERRM;
4402: p_error_message := l_error_message;
4403: Hr_Utility.set_location('..'||p_error_message,10);
4404: Hr_Utility.set_location('Leaving: '||l_proc_name, 11);
4405: RETURN l_return_value;
4406:
4407: END Get_Element_Entry_Value;
4408: -- =============================================================================
4477: )
4478: ORDER BY paa.action_sequence DESC;
4479:
4480: BEGIN
4481: Hr_Utility.set_location('Entering Get_Balance_Value function:', 5);
4482: i := p_business_group_id;
4483: IF g_legislation_code IS NULL THEN
4484: OPEN csr_leg_code (c_business_group_id => p_business_group_id);
4485: FETCH csr_leg_code INTO g_extract_params(i).legislation_code,
4541: ,c_business_group_id => p_business_group_id);
4542: FETCH csr_asg_balid INTO l_defined_balance_id;
4543: CLOSE csr_asg_balid;
4544: END IF;
4545: Hr_Utility.set_location('p_balance_name:'||p_balance_name, 5);
4546: Hr_Utility.set_location('l_balance_type_id:'||l_balance_type_id, 5);
4547: Hr_Utility.set_location('l_new_bal_dim_id:'||l_new_bal_dim_id, 5);
4548: Hr_Utility.set_location('p_dimension_name:'||p_dimension_name, 5);
4549: Hr_Utility.set_location('g_business_group_id:'||g_business_group_id, 5);
4542: FETCH csr_asg_balid INTO l_defined_balance_id;
4543: CLOSE csr_asg_balid;
4544: END IF;
4545: Hr_Utility.set_location('p_balance_name:'||p_balance_name, 5);
4546: Hr_Utility.set_location('l_balance_type_id:'||l_balance_type_id, 5);
4547: Hr_Utility.set_location('l_new_bal_dim_id:'||l_new_bal_dim_id, 5);
4548: Hr_Utility.set_location('p_dimension_name:'||p_dimension_name, 5);
4549: Hr_Utility.set_location('g_business_group_id:'||g_business_group_id, 5);
4550: Hr_Utility.set_location('g_legislation_code:'||g_legislation_code, 5);
4543: CLOSE csr_asg_balid;
4544: END IF;
4545: Hr_Utility.set_location('p_balance_name:'||p_balance_name, 5);
4546: Hr_Utility.set_location('l_balance_type_id:'||l_balance_type_id, 5);
4547: Hr_Utility.set_location('l_new_bal_dim_id:'||l_new_bal_dim_id, 5);
4548: Hr_Utility.set_location('p_dimension_name:'||p_dimension_name, 5);
4549: Hr_Utility.set_location('g_business_group_id:'||g_business_group_id, 5);
4550: Hr_Utility.set_location('g_legislation_code:'||g_legislation_code, 5);
4551: Hr_Utility.set_location('p_assignment_id:'||p_assignment_id, 5);
4544: END IF;
4545: Hr_Utility.set_location('p_balance_name:'||p_balance_name, 5);
4546: Hr_Utility.set_location('l_balance_type_id:'||l_balance_type_id, 5);
4547: Hr_Utility.set_location('l_new_bal_dim_id:'||l_new_bal_dim_id, 5);
4548: Hr_Utility.set_location('p_dimension_name:'||p_dimension_name, 5);
4549: Hr_Utility.set_location('g_business_group_id:'||g_business_group_id, 5);
4550: Hr_Utility.set_location('g_legislation_code:'||g_legislation_code, 5);
4551: Hr_Utility.set_location('p_assignment_id:'||p_assignment_id, 5);
4552:
4545: Hr_Utility.set_location('p_balance_name:'||p_balance_name, 5);
4546: Hr_Utility.set_location('l_balance_type_id:'||l_balance_type_id, 5);
4547: Hr_Utility.set_location('l_new_bal_dim_id:'||l_new_bal_dim_id, 5);
4548: Hr_Utility.set_location('p_dimension_name:'||p_dimension_name, 5);
4549: Hr_Utility.set_location('g_business_group_id:'||g_business_group_id, 5);
4550: Hr_Utility.set_location('g_legislation_code:'||g_legislation_code, 5);
4551: Hr_Utility.set_location('p_assignment_id:'||p_assignment_id, 5);
4552:
4553: Hr_Utility.set_location('l_defined_balance_id:'||l_defined_balance_id, 5);
4546: Hr_Utility.set_location('l_balance_type_id:'||l_balance_type_id, 5);
4547: Hr_Utility.set_location('l_new_bal_dim_id:'||l_new_bal_dim_id, 5);
4548: Hr_Utility.set_location('p_dimension_name:'||p_dimension_name, 5);
4549: Hr_Utility.set_location('g_business_group_id:'||g_business_group_id, 5);
4550: Hr_Utility.set_location('g_legislation_code:'||g_legislation_code, 5);
4551: Hr_Utility.set_location('p_assignment_id:'||p_assignment_id, 5);
4552:
4553: Hr_Utility.set_location('l_defined_balance_id:'||l_defined_balance_id, 5);
4554: Hr_Utility.set_location('g_asg_action_id:'||g_asg_action_id, 5);
4547: Hr_Utility.set_location('l_new_bal_dim_id:'||l_new_bal_dim_id, 5);
4548: Hr_Utility.set_location('p_dimension_name:'||p_dimension_name, 5);
4549: Hr_Utility.set_location('g_business_group_id:'||g_business_group_id, 5);
4550: Hr_Utility.set_location('g_legislation_code:'||g_legislation_code, 5);
4551: Hr_Utility.set_location('p_assignment_id:'||p_assignment_id, 5);
4552:
4553: Hr_Utility.set_location('l_defined_balance_id:'||l_defined_balance_id, 5);
4554: Hr_Utility.set_location('g_asg_action_id:'||g_asg_action_id, 5);
4555:
4549: Hr_Utility.set_location('g_business_group_id:'||g_business_group_id, 5);
4550: Hr_Utility.set_location('g_legislation_code:'||g_legislation_code, 5);
4551: Hr_Utility.set_location('p_assignment_id:'||p_assignment_id, 5);
4552:
4553: Hr_Utility.set_location('l_defined_balance_id:'||l_defined_balance_id, 5);
4554: Hr_Utility.set_location('g_asg_action_id:'||g_asg_action_id, 5);
4555:
4556: --If Reporting dimension is ASG_RUN
4557: IF g_extract_params(i).reporting_dimension = 'ASG_RUN' THEN
4550: Hr_Utility.set_location('g_legislation_code:'||g_legislation_code, 5);
4551: Hr_Utility.set_location('p_assignment_id:'||p_assignment_id, 5);
4552:
4553: Hr_Utility.set_location('l_defined_balance_id:'||l_defined_balance_id, 5);
4554: Hr_Utility.set_location('g_asg_action_id:'||g_asg_action_id, 5);
4555:
4556: --If Reporting dimension is ASG_RUN
4557: IF g_extract_params(i).reporting_dimension = 'ASG_RUN' THEN
4558: --Get the balance amount
4560: l_balance_amount := Pay_Balance_Pkg.get_value
4561: (p_defined_balance_id => l_defined_balance_id,
4562: p_assignment_action_id => g_asg_action_id );
4563: l_bal_total_amt := l_bal_total_amt + Nvl(l_balance_amount,0);
4564: Hr_Utility.set_location('l_bal_total_amt:'||l_bal_total_amt, 5);
4565:
4566: END IF;
4567: ELSE
4568: IF l_defined_balance_id IS NOT NULL THEN
4651: RETURN l_bal_total_amt;
4652: EXCEPTION
4653: WHEN Others THEN
4654: p_error_message :='SQL-CODE :'||SQLCODE;
4655: Hr_Utility.set_location('..'||p_error_message,90);
4656: Hr_Utility.set_location('Leaving Get_Balance_Value function:', 90);
4657: RETURN l_bal_total_amt;
4658: END Get_Balance_Value;
4659:
4652: EXCEPTION
4653: WHEN Others THEN
4654: p_error_message :='SQL-CODE :'||SQLCODE;
4655: Hr_Utility.set_location('..'||p_error_message,90);
4656: Hr_Utility.set_location('Leaving Get_Balance_Value function:', 90);
4657: RETURN l_bal_total_amt;
4658: END Get_Balance_Value;
4659:
4660: -- =============================================================================
4677: l_balance_type_id pay_balance_types.balance_type_id%TYPE;
4678: l_index NUMBER;
4679: BEGIN
4680: i := p_business_group_id;
4681: Hr_Utility.set_location('Entering Get_Balance_Value function:', 5);
4682: -- Check this balance Name is already exist in record
4683: -- if it is then get the balance type id
4684: FOR num IN 1..g_balance_detls.Count LOOP
4685: IF g_balance_detls(num).balance_name = p_balance_name THEN
4742: RETURN l_bal_total_amt;
4743: EXCEPTION
4744: WHEN Others THEN
4745: p_error_message :='SQL-ERRM :'||SQLERRM;
4746: Hr_Utility.set_location('..'||p_error_message,85);
4747: Hr_Utility.set_location('Leaving Get_Balance_Value function:', 80);
4748: RETURN l_bal_total_amt;
4749: END Get_Balance_Value;
4750: -- ====================================================================
4743: EXCEPTION
4744: WHEN Others THEN
4745: p_error_message :='SQL-ERRM :'||SQLERRM;
4746: Hr_Utility.set_location('..'||p_error_message,85);
4747: Hr_Utility.set_location('Leaving Get_Balance_Value function:', 80);
4748: RETURN l_bal_total_amt;
4749: END Get_Balance_Value;
4750: -- ====================================================================
4751: -- ~ Set_ConcProg_Parameter_Values : Used to get the conc program parameters values
4814: l_payroll_name PAY_PAYROLLS_F.PAYROLL_NAME%TYPE ;
4815: l_con_set_name PAY_CONSOLIDATION_SETS.CONSOLIDATION_SET_NAME%TYPE;
4816:
4817: BEGIN
4818: Hr_Utility.set_location('Entering: '||l_proc_name, 5);
4819:
4820: OPEN csr_ext_name( c_ext_dfn_id => p_ext_dfn_id);
4821: FETCH csr_ext_name INTO l_extract_name;
4822: CLOSE csr_ext_name;
4867: g_conc_prog_details(0).grename := l_gre_name;
4868: g_conc_prog_details(0).payrollname := l_payroll_name;
4869: g_conc_prog_details(0).consolset := l_con_set_name;
4870:
4871: Hr_Utility.set_location('Leaving: '||l_proc_name, 80);
4872: EXCEPTION
4873: WHEN Others THEN
4874: Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
4875: END Set_ConcProg_Parameter_Values;
4870:
4871: Hr_Utility.set_location('Leaving: '||l_proc_name, 80);
4872: EXCEPTION
4873: WHEN Others THEN
4874: Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
4875: END Set_ConcProg_Parameter_Values;
4876:
4877:
4878: -- =============================================================================
5025: l_Chg_Evt_Exists VARCHAR2(2);
5026: l_effective_date DATE;
5027: BEGIN
5028:
5029: Hr_Utility.set_location('Entering: '||l_proc_name, 5);
5030: i := p_business_group_id;
5031: l_ext_rslt_id := Ben_Ext_Thread.g_ext_rslt_id;
5032: l_ext_dfn_id := Ben_Ext_Thread.g_ext_dfn_id;
5033:
5031: l_ext_rslt_id := Ben_Ext_Thread.g_ext_rslt_id;
5032: l_ext_dfn_id := Ben_Ext_Thread.g_ext_dfn_id;
5033:
5034: IF NOT g_extract_params.EXISTS(i) THEN
5035: Hr_Utility.set_location('..Exract Params PL/SQL not populated ', 7);
5036:
5037: -- Get the extract type, Changes extract or Full Profile
5038: OPEN csr_ext_attr(c_ext_dfn_id=> l_ext_dfn_id);
5039: FETCH csr_ext_attr INTO l_ext_dfn_type;
5037: -- Get the extract type, Changes extract or Full Profile
5038: OPEN csr_ext_attr(c_ext_dfn_id=> l_ext_dfn_id);
5039: FETCH csr_ext_attr INTO l_ext_dfn_type;
5040: CLOSE csr_ext_attr;
5041: Hr_Utility.set_location('..After cursor csr_ext_attr',9);
5042:
5043: -- Get the Conc. request id to get the params
5044: OPEN csr_req_id(c_ext_rslt_id => l_ext_rslt_id
5045: ,c_ext_dfn_id => l_ext_dfn_id
5045: ,c_ext_dfn_id => l_ext_dfn_id
5046: ,c_business_group_id => p_business_group_id);
5047: FETCH csr_req_id INTO l_conc_reqest_id;
5048: CLOSE csr_req_id;
5049: Hr_Utility.set_location('..After Conc.Request id cursor csr_req_id',11);
5050:
5051: -- Get the params. based on the conc. request id.
5052: OPEN csr_ext_params (c_request_id => l_conc_reqest_id
5053: ,c_ext_dfn_id => l_ext_dfn_id
5085: g_extract_params(i).currency_code;
5086: CLOSE csr_leg_code;
5087: g_legislation_code := g_extract_params(i).legislation_code;
5088: g_business_group_id := p_business_group_id;
5089: Hr_Utility.set_location('..Stored the extract parameters in PL/SQL table', 15);
5090: -- Get Assignment Run dimension Id as we will be using for calculating the amount
5091: OPEN csr_asg_dimId(g_legislation_code);
5092: FETCH csr_asg_dimId INTO g_asgrun_dim_id;
5093: CLOSE csr_asg_dimId;
5097: (p_element_type_id => g_extract_params(i).element_type_id
5098: ,p_element_set_id => g_extract_params(i).element_set_id
5099: ,p_effective_date => g_extract_params(i).extract_end_date
5100: ,p_business_group_id => p_business_group_id);
5101: Hr_Utility.set_location('..Stored the Element Ids in PL/SQL table', 17);
5102:
5103: Set_ConcProg_Parameter_Values
5104: (p_ext_dfn_id => g_extract_params(i).ext_dfn_id
5105: ,p_reporting_dimension => g_extract_params(i).reporting_dimension
5111: ,p_gre_id => g_extract_params(i).gre_org_id
5112: ,p_payroll_id => g_extract_params(i).payroll_id
5113: ,p_con_set => g_extract_params(i).con_set_id
5114: );
5115: Hr_Utility.set_location('..Stored the Conc. Program parameters', 17);
5116: END IF;
5117: -- Check if for this assignment id there are assign. action(s) which have
5118: -- processed the element(s). If any then return return Y i.e. assign needs
5119: -- to be extracted.
5120: g_person_id:= Nvl(get_current_extract_person(p_assignment_id),
5121: Ben_Ext_Person.g_person_id);
5122: l_effective_date := Least(g_extract_params(i).extract_end_date,
5123: p_effective_date);
5124: Hr_Utility.set_location('..Processing Assig Id : '||p_assignment_id, 17);
5125: Hr_Utility.set_location('..Processing Person Id : '||g_person_id, 17);
5126: Hr_Utility.set_location('..Processing Eff.Date : '||p_effective_date, 17);
5127:
5128:
5121: Ben_Ext_Person.g_person_id);
5122: l_effective_date := Least(g_extract_params(i).extract_end_date,
5123: p_effective_date);
5124: Hr_Utility.set_location('..Processing Assig Id : '||p_assignment_id, 17);
5125: Hr_Utility.set_location('..Processing Person Id : '||g_person_id, 17);
5126: Hr_Utility.set_location('..Processing Eff.Date : '||p_effective_date, 17);
5127:
5128:
5129: OPEN csr_assig (c_assignment_id => p_assignment_id
5122: l_effective_date := Least(g_extract_params(i).extract_end_date,
5123: p_effective_date);
5124: Hr_Utility.set_location('..Processing Assig Id : '||p_assignment_id, 17);
5125: Hr_Utility.set_location('..Processing Person Id : '||g_person_id, 17);
5126: Hr_Utility.set_location('..Processing Eff.Date : '||p_effective_date, 17);
5127:
5128:
5129: OPEN csr_assig (c_assignment_id => p_assignment_id
5130: ,c_effective_date => l_effective_date
5179: ,c_effective_date => l_effective_date
5180: ,c_business_group_id => p_business_group_id);
5181: FETCH csr_assig INTO g_primary_assig(p_assignment_id);
5182: CLOSE csr_assig;
5183: Hr_Utility.set_location('..Valid Assig Id : '||p_assignment_id, 79);
5184: END IF;
5185:
5186: ELSIF g_extract_params(i).ext_dfn_type IN ('PEN_CHG' -- Reg.US Chg Extracts
5187: ,'FID_CHG' -- Changes Extracts
5206: ,c_start_date => g_extract_params(i).extract_start_date
5207: ,c_end_date => g_extract_params(i).extract_end_date
5208: );
5209: FETCH csr_val_assg INTO l_valid_action;
5210: Hr_Utility.set_location('..All Employees l_valid_action: '||l_valid_action, 79);
5211: IF csr_val_assg%FOUND THEN
5212: CLOSE csr_val_assg;
5213: l_return_value := 'Y';
5214: ELSE
5215: CLOSE csr_val_assg;
5216: END IF;
5217:
5218: ELSIF g_extract_params(i).reporting_dimension = 'CHG_PEN' THEN
5219: Hr_Utility.set_location('..Employees in pension plan ', 79);
5220: l_ele_type_id := g_element.FIRST;
5221: << Chg_Action >>
5222: WHILE l_ele_type_id IS NOT NULL
5223: LOOP
5264: END IF; -- l_return_value = 'Y'
5265:
5266: -- END IF; -- If CHG_ALL
5267: END IF;
5268: Hr_Utility.set_location('..l_return_value : '||l_return_value, 79);
5269: Hr_Utility.set_location('Leaving: '||l_proc_name, 80);
5270:
5271: RETURN l_return_value;
5272:
5265:
5266: -- END IF; -- If CHG_ALL
5267: END IF;
5268: Hr_Utility.set_location('..l_return_value : '||l_return_value, 79);
5269: Hr_Utility.set_location('Leaving: '||l_proc_name, 80);
5270:
5271: RETURN l_return_value;
5272:
5273: EXCEPTION
5272:
5273: EXCEPTION
5274: WHEN Others THEN
5275: p_error_message :='SQL-ERRM :'||SQLERRM;
5276: Hr_Utility.set_location('..'||p_error_message,85);
5277: Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
5278:
5279: RETURN l_return_value;
5280: END Pension_Criteria_Full_Profile;
5273: EXCEPTION
5274: WHEN Others THEN
5275: p_error_message :='SQL-ERRM :'||SQLERRM;
5276: Hr_Utility.set_location('..'||p_error_message,85);
5277: Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
5278:
5279: RETURN l_return_value;
5280: END Pension_Criteria_Full_Profile;
5281: -- ====================================================================
5299: l_proc_name VARCHAR2(150):= g_proc_name||'Del_Service_Detail_Recs';
5300: l_return_value NUMBER := 0; --0= Sucess, -1=Error
5301:
5302: BEGIN
5303: Hr_Utility.set_location('Entering :'||l_proc_name, 5);
5304:
5305: -- Get the record id for the Hidden Detail record
5306: Hr_Utility.set_location('..Get the hidden record for extract running..',10);
5307: FOR csr_rcd_rec IN csr_ext_rcd_id
5302: BEGIN
5303: Hr_Utility.set_location('Entering :'||l_proc_name, 5);
5304:
5305: -- Get the record id for the Hidden Detail record
5306: Hr_Utility.set_location('..Get the hidden record for extract running..',10);
5307: FOR csr_rcd_rec IN csr_ext_rcd_id
5308: (c_hide_flag => 'Y' -- Y=Record is hidden one
5309: ,c_rcd_type_cd => 'D')-- D=Detail, T=Total, H-Header
5310: -- Loop through each detail record for the extract
5339: AND dtl.person_id = err_rec.person_id
5340: AND dtl.business_group_id = p_business_group_id;
5341: END LOOP;
5342:
5343: Hr_Utility.set_location('Leaving :'||l_proc_name, 25);
5344:
5345: RETURN l_return_value;
5346:
5347: EXCEPTION
5345: RETURN l_return_value;
5346:
5347: EXCEPTION
5348: WHEN Others THEN
5349: Hr_Utility.set_location('..Exception when others raised..', 20);
5350: Hr_Utility.set_location('Leaving :'||l_proc_name, 25);
5351: RETURN -1;
5352: END Del_Service_Detail_Recs;
5353:
5346:
5347: EXCEPTION
5348: WHEN Others THEN
5349: Hr_Utility.set_location('..Exception when others raised..', 20);
5350: Hr_Utility.set_location('Leaving :'||l_proc_name, 25);
5351: RETURN -1;
5352: END Del_Service_Detail_Recs;
5353:
5354: -- ====================================================================