[Home] [Help]
8: --
9: -- Global Variables
10: --
11: -- Bug c_context_name - PRSI Context Balance Design Change
12: l_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE;
13: l_context_id ff_contexts.context_id%TYPE;
14: l_defined_balance_id pay_defined_balances.defined_balance_id%TYPE;
15: p_person_id_global per_people_f.person_id%TYPE; -- to store the person_id in a package level variable to be used in c_context_name cursor
16: l_class_count NUMBER (4);
49: l_prsi_class_temp prsi_class_tab; -- For making other tables empty
50:
51: -- declaring a pl/sql table for storing the the max assignment action ids against assignments for a person.
52: -- Bug fix4004470
53: TYPE asg_action_ids_tab IS TABLE OF pay_assignment_actions.assignment_action_id%TYPE
54: INDEX BY BINARY_INTEGER;
55:
56: t_context_value type_context_value_tab;
57: t_empty_table type_context_value_tab; -- for emptying the t_context_value pl/sql table
57: t_empty_table type_context_value_tab; -- for emptying the t_context_value pl/sql table
58:
59: -- t_asg_action_id asg_action_ids_tab; --Bug fix 4004470
60: -- t_empty_asg_table asg_action_ids_tab; -- for emptying the t_asg_action_id pl/sql table( Bug fix 4023794)
61: t_asg_action_id pay_assignment_actions.assignment_action_id%TYPE;
62:
63:
64: -- Bug 3460687 Temporary tables to hold Class Names
65: t_context_value_balinit type_context_value_tab;
98: ,p_end_date date
99: ,p_bg_id number)
100: RETURN NUMBER AS
101: l_person_id per_people_f.person_id%TYPE;
102: l_actid pay_assignment_actions.assignment_action_id%TYPE;
103:
104: cursor csr_check_multiple_asg IS
105: select paaf.person_id
106: from per_assignments_f paaf, per_assignments_f paaf1
114:
115: cursor csr_get_max_assact is
116: SELECT MAX(paa.assignment_action_id)
117: FROM per_assignments_f paf,
118: pay_assignment_actions paa,
119: pay_payroll_actions ppa,
120: per_time_periods ptp
121: WHERE paf.person_id = l_person_id
122: AND paf.assignment_id = paa.assignment_id
217: p_ppsn_override VARCHAR2 default NULL --6633719
218: ) is
219:
220:
221: l_child_assignemnt_action pay_assignment_actions.assignment_action_id%TYPE;
222: l_p60_prsi prsi_class_tab;
223:
224: -- cursor to get child action
225: cursor c1 is
222: l_p60_prsi prsi_class_tab;
223:
224: -- cursor to get child action
225: cursor c1 is
226: select assignment_action_id from pay_assignment_actions
227: where source_action_id = p_max_action_id;
228:
229: -- This fetch all prsi classes from run-results where assignment action id
230: -- lies between child of p_max_action_id and p_assignment_action_id(last period max action id)
238: paa.assignment_action_id,
239: trim(rrv1.result_value) result_value
240: FROM per_people_f per,
241: per_assignments_f asg,
242: pay_assignment_actions paa,
243: pay_payroll_actions ppa,
244: per_time_periods ptp,
245: pay_run_results prr,
246: pay_element_types_f pet,
774: l_proc_name VARCHAR2 (100) := l_package|| 'assignment_action_code';
775: l_actid NUMBER;
776: l_locked_action NUMBER;
777: l_out_var VARCHAR2 (30);
778: l_aact_id pay_assignment_actions.assignment_action_id%TYPE;
779: l_set_flag hr_assignment_set_amendments.include_or_exclude%TYPE ;
780: l_temp_person_id per_people_f.person_id%TYPE :=0;
781: l_start_date date;
782: l_end_date date;
797: asg.person_id,
798: paa.assignment_id,
799: fnd_number.canonical_to_number(substr(max(lpad(paa.action_sequence,15,'0')||paa.assignment_action_id),16)) aa -- Bug 4672715
800: FROM per_assignments_f asg,
801: pay_assignment_actions paa,
802: pay_payroll_actions ppa,
803: pay_all_payrolls_f pap,
804: per_time_periods ptp,
805: hr_soft_coding_keyflex flex
826: --decode added to pick the previous assignments also in case of ReHire having diff overrides.
827: AND asg.effective_end_date = DECODE(OVERRIDE_PPSN(asg.assignment_id),NULL, --6633719
828: (SELECT MAX (paf.effective_end_date)
829: FROM per_assignments_f paf,
830: pay_assignment_actions paa1, --Bug fix 4130665
831: pay_payroll_actions ppa1,
832: per_time_periods ptp1 --Tar 15081088.6
833: ,pay_all_payrolls_f pay
834: ,hr_soft_coding_keyflex flex1
905: )))
906: OR l_assignment_set_id IS NULL)
907: AND NOT EXISTS (
908: SELECT 1
909: FROM pay_assignment_actions paa_p35,
910: pay_payroll_actions ppa_p35,
911: per_assignments_f paaf_p35,
912: pay_all_payrolls_f ppf_p35,
913: hr_soft_coding_keyflex flex_p35,
923: --AND paa_p35.assignment_id = asg.assignment_id
924: AND paa_p35.assignment_id = paaf_p35.assignment_id
925: AND paaf_p35.person_id = asg.person_id
926: and paa_p35.assignment_action_id = plock.locking_action_id
927: and plock.locked_action_id in (select assignment_action_id from pay_assignment_actions
928: where assignment_id=asg.assignment_id)
929: AND paaf_p35.payroll_id = ppf_p35.payroll_id
930: AND ppf_p35.soft_coding_keyflex_id = flex_p35.soft_coding_keyflex_id
931: AND flex_p35.segment4 = l_segment4)
966: FROM pay_run_result_values prrv,
967: pay_run_results prr,
968: pay_input_values_f pivf,
969: pay_element_types_f pet,
970: pay_assignment_actions paa,
971: pay_payroll_actions ppa
972: WHERE ppa.payroll_action_id= paa.payroll_action_id
973: AND ((paa.source_action_id= p_action_id
974: AND prr.assignment_action_id = paa.assignment_action_id )
981: and result_value = 'IE_EXCLUDE';
982:
983: CURSOR csr_action_type (p_assignment_id NUMBER) IS
984: SELECT fnd_number.canonical_to_number(substr(max(lpad(paa.action_sequence,15,'0')||paa.assignment_action_id),16)) asg_action_id
985: FROM pay_assignment_actions paa,
986: pay_payroll_actions ppa
987: WHERE ppa.payroll_action_id = paa.payroll_action_id
988: and paa.assignment_id = p_assignment_id
989: and ppa.action_type in ('R','Q')
992:
993:
994: l_flag_exclusion varchar2(1) := 'N';
995: l_flag_action_type pay_payroll_actions.action_type%TYPE;
996: l_flag_action_id pay_assignment_actions.assignment_action_id%TYPE;
997: BEGIN
998:
999: --
1000: l_segment4 := pay_ie_p35.get_parameter( p_payroll_action_id=> pactid,
1095: CLOSE csr_exclusion;
1096:
1097: IF l_flag_exclusion <> 'Y' then
1098: IF l_flag_action_type not in ('I','B','V') then
1099: SELECT pay_assignment_actions_s.NEXTVAL
1100: INTO l_actid
1101: FROM DUAL;
1102:
1103: hr_utility.set_location('INSIDE IF',18);
1121: FETCH csr_exclusion into l_flag_exclusion, l_flag_action_type;
1122: CLOSE csr_exclusion;
1123:
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);
1168: l_arch_works_number pay_action_information.action_information14%TYPE
1169: DEFAULT ' '; --BUG 3306202 Added default value
1170: l_segment4 hr_soft_coding_keyflex.segment4%TYPE;
1171: l_arch_person_id per_assignments_f.person_id%TYPE;
1172: l_arch_assignment_id pay_assignment_actions.assignment_id%TYPE;
1173: l_arch_assmt_action_id_bal pay_assignment_actions.assignment_action_id%TYPE;
1174: l_arch_tax_deduction_basis pay_action_information.action_information17%TYPE;
1175: l_arch_surname pay_action_information.action_information29%TYPE;
1176: l_arch_first_name pay_action_information.action_information28%TYPE;
1169: DEFAULT ' '; --BUG 3306202 Added default value
1170: l_segment4 hr_soft_coding_keyflex.segment4%TYPE;
1171: l_arch_person_id per_assignments_f.person_id%TYPE;
1172: l_arch_assignment_id pay_assignment_actions.assignment_id%TYPE;
1173: l_arch_assmt_action_id_bal pay_assignment_actions.assignment_action_id%TYPE;
1174: l_arch_tax_deduction_basis pay_action_information.action_information17%TYPE;
1175: l_arch_surname pay_action_information.action_information29%TYPE;
1176: l_arch_first_name pay_action_information.action_information28%TYPE;
1177: l_arch_dob pay_action_information.action_information25%TYPE;
1181: l_arch_address_line4 pay_action_information.action_information9%TYPE; -- BUG 4066315
1182: l_length_address_line2 NUMBER; -- BUG 4066315
1183: l_available_space NUMBER; -- BUG 4066315
1184: l_arch_hire_date pay_action_information.action_information24%TYPE;
1185: l_arch_payroll_action_id pay_assignment_actions.payroll_action_id%TYPE;
1186: l_arch_annual_tax_credit pay_action_information.action_information26%TYPE;
1187: l_arch_term_date pay_action_information.action_information25%TYPE;
1188: l_arch_mothers_name per_people_f.per_information1%TYPE;
1189: l_arch_totwks_insurble_emplmnt pay_run_result_values.result_value%TYPE;
1217: l_temp_prsi_cont pay_action_information.action_information4%TYPE := NULL;
1218: l_oth_arch_ovn pay_action_information.object_version_number%TYPE;
1219: l_pds_id per_periods_of_service.period_of_service_id%TYPE;
1220: l_asg_id per_assignments_f.assignment_id%TYPE;
1221: l_aact_id pay_assignment_actions.assignment_action_id%TYPE;
1222: l_max_act_for_bal pay_assignment_actions.assignment_action_id%TYPE;
1223: l_set_flag hr_assignment_set_amendments.include_or_exclude%TYPE ;
1224: l_arch_primary_flag per_assignments_f.primary_flag%TYPE;
1225: v_primary_flag per_assignments_f.primary_flag%TYPE;
1218: l_oth_arch_ovn pay_action_information.object_version_number%TYPE;
1219: l_pds_id per_periods_of_service.period_of_service_id%TYPE;
1220: l_asg_id per_assignments_f.assignment_id%TYPE;
1221: l_aact_id pay_assignment_actions.assignment_action_id%TYPE;
1222: l_max_act_for_bal pay_assignment_actions.assignment_action_id%TYPE;
1223: l_set_flag hr_assignment_set_amendments.include_or_exclude%TYPE ;
1224: l_arch_primary_flag per_assignments_f.primary_flag%TYPE;
1225: v_primary_flag per_assignments_f.primary_flag%TYPE;
1226: -- pension variables
1247: CURSOR get_action_type(p_action_id number)
1248: IS
1249: SELECT paa.assignment_action_id,ppa.action_type
1250: FROM pay_action_interlocks pal,
1251: pay_assignment_actions paa,
1252: pay_payroll_actions ppa
1253: WHERE pal.locked_action_id = p_action_id
1254: AND pal.locking_action_id = paa.assignment_action_id
1255: AND ppa.payroll_action_id = paa.payroll_action_id
1265:
1266: /*CURSOR get_action_type(p_action_id number) is
1267: select ppa.action_type, ppa.report_type
1268: from pay_payroll_actions ppa,
1269: pay_assignment_actions paa
1270: where ppa.payroll_action_id = paa.payroll_action_id
1271: and paa.source_action_id IS NULL
1272: and paa.assignment_action_id = p_action_id;*/
1273:
1277: /*CURSOR csr_latest_p30_action (p_asg_act_id NUMBER)
1278: IS
1279: SELECT paa.assignment_action_id
1280: FROM pay_action_interlocks pal,
1281: pay_assignment_actions paa,
1282: pay_payroll_actions ppa,
1283: pay_action_information pai
1284: WHERE pal.locked_action_id = p_asg_act_id
1285: AND pal.locking_action_id = paa.assignment_action_id
1298: CURSOR csr_latest_prepay_action (p_asg_act_id NUMBER)
1299: IS
1300: SELECT paa.assignment_action_id
1301: FROM pay_action_interlocks pal,
1302: pay_assignment_actions paa,
1303: pay_payroll_actions ppa
1304: WHERE pal.locked_action_id = p_asg_act_id
1305: AND pal.locking_action_id = paa.assignment_action_id
1306: AND ppa.payroll_action_id = paa.payroll_action_id
1629: ),
1630: 0
1631: ) -- Q1_Previous_Emp_Tax
1632: FROM pay_action_interlocks pai,
1633: pay_assignment_actions paa,
1634: per_people_f ppf,
1635: per_periods_of_service pps,
1636: per_assignments_f paf,
1637: per_addresses pad,
1832: 2
1833: ),
1834: 0
1835: ) -- Q1_Previous_Emp_Tax
1836: FROM pay_assignment_actions paa,
1837: per_people_f ppf,
1838: per_periods_of_service pps,
1839: per_assignments_f paf,
1840: per_addresses pad,
1968: ORDER BY payef.effective_end_date desc;
1969:
1970: CURSOR get_assignment_id is
1971: select payroll_action_id,assignment_id,chunk_number
1972: from pay_assignment_actions
1973: where assignment_action_id = p_assactid;
1974:
1975: /*** Cursors for Previous employment balances */
1976: CURSOR c_get_periods_of_service(v_person_id NUMBER,
2005: AND primary_flag = 'Y';*/
2006:
2007: CURSOR c_get_max_aact(p_pds_id NUMBER) IS
2008: SELECT max(paa.assignment_action_id)
2009: FROM pay_assignment_Actions paa,
2010: pay_payroll_actions ppa
2011: -- ,per_time_periods ptp -- removed to improve performance 4771780
2012: WHERE paa.assignment_id in (SELECT assignment_id
2013: FROM per_assignments_f
2031: select NVL (SUBSTR (paf.assignment_number, 1, 12), ' '), -- WORKS NUMBER
2032: paf.primary_flag,
2033: paf.assignment_id
2034: from per_assignments_f paf,
2035: pay_assignment_actions paa
2036: where paf.assignment_id = paa.assignment_id
2037: and paa.assignment_action_id = p_action_id
2038: and paf.effective_start_date <= l_end_date
2039: and paf.effective_end_date >= l_start_date;
2046: paf.primary_flag,
2047: paf.assignment_id,
2048: max(effective_end_date) end_date
2049: from per_assignments_f paf,
2050: pay_assignment_actions paa
2051: where paf.assignment_id = paa.assignment_id
2052: and paa.assignment_action_id = p_action_id
2053: and paf.effective_start_date <= l_end_date
2054: and paf.effective_end_date >= l_start_date
2085: cnt number;
2086: v_action_type pay_payroll_actions.action_type%TYPE;
2087: v_report_type pay_payroll_actions.report_type%TYPE;
2088: v_work_number per_assignments_f.assignment_number%TYPE;
2089: v_assignment_id pay_assignment_actions.assignment_id%TYPE;
2090: v_action_id pay_assignment_actions.assignment_action_id%TYPE;
2091: v_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE;
2092: v_chunk pay_assignment_actions.chunk_number%TYPE;
2093: l_max_assignment_id per_assignments_f.assignment_id%TYPE;
2086: v_action_type pay_payroll_actions.action_type%TYPE;
2087: v_report_type pay_payroll_actions.report_type%TYPE;
2088: v_work_number per_assignments_f.assignment_number%TYPE;
2089: v_assignment_id pay_assignment_actions.assignment_id%TYPE;
2090: v_action_id pay_assignment_actions.assignment_action_id%TYPE;
2091: v_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE;
2092: v_chunk pay_assignment_actions.chunk_number%TYPE;
2093: l_max_assignment_id per_assignments_f.assignment_id%TYPE;
2094: v_date date;
2088: v_work_number per_assignments_f.assignment_number%TYPE;
2089: v_assignment_id pay_assignment_actions.assignment_id%TYPE;
2090: v_action_id pay_assignment_actions.assignment_action_id%TYPE;
2091: v_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE;
2092: v_chunk pay_assignment_actions.chunk_number%TYPE;
2093: l_max_assignment_id per_assignments_f.assignment_id%TYPE;
2094: v_date date;
2095: asg_assignment_id per_assignments_f.assignment_id%TYPE;
2096: l_max_action_id pay_assignment_actions.assignment_action_id%TYPE;
2092: v_chunk pay_assignment_actions.chunk_number%TYPE;
2093: l_max_assignment_id per_assignments_f.assignment_id%TYPE;
2094: v_date date;
2095: asg_assignment_id per_assignments_f.assignment_id%TYPE;
2096: l_max_action_id pay_assignment_actions.assignment_action_id%TYPE;
2097: l_cnt number;
2098: -- for P60 enhancement.
2099: l_prev_pay pay_action_information.action_information4%TYPE;
2100: l_this_pay pay_action_information.action_information4%TYPE;
2122: from per_assignment_extra_info
2123: where assignment_id = p_asg_id
2124: and aei_information_category = 'IE_ASG_OVERRIDE';
2125:
2126: l_child_assignment_action_id pay_assignment_actions.assignment_action_id%type;
2127:
2128: CURSOR csr_child_actions(p_asg_id number) IS
2129: SELECT paa.assignment_action_id child_assignment_action_id
2130: --,prt.run_method run_type
2127:
2128: CURSOR csr_child_actions(p_asg_id number) IS
2129: SELECT paa.assignment_action_id child_assignment_action_id
2130: --,prt.run_method run_type
2131: FROM pay_assignment_actions paa,
2132: pay_run_types_f prt
2133: WHERE paa.run_type_id = prt.run_type_id
2134: AND prt.run_method IN ('N','P')
2135: AND p_effective_date BETWEEN prt.effective_start_date AND prt.effective_end_date
2135: AND p_effective_date BETWEEN prt.effective_start_date AND prt.effective_end_date
2136: AND paa.assignment_action_id = (SELECT /*+ USE_NL(paa, ppa) */
2137: fnd_number.canonical_to_number(substr(max(lpad(paa.action_sequence,15,'0')||
2138: paa.assignment_action_id),16)) child_assignment_action_id
2139: FROM pay_assignment_actions paa,
2140: pay_payroll_actions ppa
2141: WHERE paa.assignment_id = p_asg_id
2142: AND ppa.payroll_action_id = paa.payroll_action_id
2143: AND (paa.source_action_id is not null or ppa.action_type in ('I','V'))
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;
4128: FETCH get_assignment_id into v_payroll_action_id,v_assignment_id,v_chunk;
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
4426: WHERE assignment_action_id = p_assactid
4427: AND payroll_action_id= v_payroll_action_id;
4428:
4429: UPDATE pay_assignment_actions SET assignment_id = l_max_assignment_id
4425: UPDATE pay_assignment_actions SET assignment_id = l_max_assignment_id
4426: WHERE assignment_action_id = p_assactid
4427: AND payroll_action_id= v_payroll_action_id;
4428:
4429: 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:
4672: fnd_number.canonical_to_number(substr(max(lpad(paa.action_sequence,15,'0')||
4673: paa.assignment_action_id),16)) assignment_action_id --bug fix 4004470
4674: FROM per_people_f ppf,
4675: per_assignments_f paf,
4676: pay_assignment_actions paa,
4677: pay_payroll_actions ppa,
4678: per_time_periods ptp
4679: WHERE
4680: ppf.person_id = p_person_id
4717: paa.assignment_action_id,
4718: trim(rrv1.result_value) result_value
4719: FROM per_people_f per,
4720: per_assignments_f asg,
4721: pay_assignment_actions paa,
4722: pay_payroll_actions ppa,
4723: per_time_periods ptp,
4724: pay_run_results prr,
4725: pay_element_types_f pet,
4837: )
4838: ) class_name
4839: FROM per_people_f per,
4840: per_assignments_f asg,
4841: pay_assignment_actions paa,
4842: pay_payroll_actions ppa,
4843: per_time_periods ptp,
4844: pay_run_results prr,
4845: pay_element_types_f pet,
5538: RETURN NUMBER
5539: AS
5540:
5541: l_total_weeks NUMBER (4) := 0; --Bug No 4555227
5542: l_get_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE;
5543: --
5544: --
5545:
5546: BEGIN