[Home] [Help]
199: FROM per_all_assignments_f paaf,
200: pay_element_types_f petf,
201: pay_input_values_f pivf,
202: pay_element_entries_f peef,
203: pay_element_entry_values_f peevf
204: WHERE paaf.assignment_id = V_ASSIGNMENT_ID
205: AND petf.element_name ='Pensions Information'
206: AND petf.legislation_code = 'GB'
207: AND pivf.name IN ( 'Aggregate Earnings','Main Entry for Aggregation')
227: FROM per_all_assignments_f paaf,
228: pay_element_types_f petf,
229: pay_input_values_f pivf,
230: pay_element_entries_f peef,
231: pay_element_entry_values_f peevf
232: WHERE paaf.assignment_id = V_ASSIGNMENT_ID
233: AND petf.element_name ='Pensions Information'
234: AND petf.legislation_code = 'GB'
235: AND pivf.name IN ( 'Auto Enrollment Date', 'Opt Out Date','Postponement End Date','Qualifying scheme exists','Eligible Job Holder Date')
550: SELECT peevf.*
551: FROM pay_element_types_f petf
552: ,pay_input_values_f pivf
553: ,pay_element_entries_f peef
554: ,pay_element_entry_values_f peevf
555: WHERE
556: petf.element_name = 'NI'
557: AND petf.legislation_code = 'GB'
558: AND petf.element_type_id = pivf.element_type_id
882: IS
883: SELECT PEEF.SCREEN_ENTRY_VALUE,
884: PEEF.INPUT_VALUE_ID,
885: UPPER(REPLACE(PIVF.NAME,' ','_')) NAME
886: FROM PAY_ELEMENT_ENTRY_VALUES_F PEEF,
887: PAY_INPUT_VALUES_F PIVF
888: WHERE PEEF.ELEMENT_ENTRY_ID = P_ELEM_ENTRY_ID
889: AND PEEF.INPUT_VALUE_ID = PIVF.INPUT_VALUE_ID
890: AND g_payroll_end_date BETWEEN peef.EFFECTIVE_START_DATE AND peef.EFFECTIVE_END_DATE
1199: AND P_DATE_EARNED BETWEEN fff.effective_start_date AND fff.effective_end_date
1200: AND peef.assignment_id = P_ASSIGNMENT_ID
1201: AND P_DATE_EARNED BETWEEN petf.effective_start_date AND petf.effective_end_date
1202: AND P_DATE_EARNED BETWEEN peef.effective_start_date AND peef.effective_end_date;
1203: L_SCREEN_ENTRY_VALUE PAY_ELEMENT_ENTRY_VALUES_F.SCREEN_ENTRY_VALUE%type;
1204: L_FORMULA_ID FF_FORMULAS_F.FORMULA_ID%type;
1205: L_STATUS_PROCES_ID NUMBER;
1206: L_TOTAL_EARNINGS_VALUE NUMBER :=0;
1207: L_INPUTS FF_EXEC.INPUTS_T;
1318: PAAF.ASSIGNMENT_ID,
1319: PEEF.ELEMENT_ENTRY_ID
1320: FROM PER_ALL_PEOPLE_F PAPF,
1321: PER_ALL_ASSIGNMENTS_F PAAF,
1322: PAY_ELEMENT_ENTRY_VALUES_F PEEVF,
1323: PAY_ELEMENT_ENTRIES_F PEEF,
1324: PER_ASSIGNMENT_STATUS_TYPES PAST
1325: WHERE PAAF.PERSON_ID =PAPF.PERSON_ID
1326: AND PAPF.PERSON_ID =P_PERSON_ID
1336: AND G_PRP_END_DATE BETWEEN PEEVF.EFFECTIVE_START_DATE AND PEEVF.EFFECTIVE_END_DATE
1337: AND G_PRP_END_DATE BETWEEN PAAF.EFFECTIVE_START_DATE AND PAAF.EFFECTIVE_END_DATE
1338: AND G_PRP_END_DATE BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE;
1339: -----------------------------
1340: L_SCREEN_ENTRY_VALUE PAY_ELEMENT_ENTRY_VALUES_F.SCREEN_ENTRY_VALUE%type;
1341: L_FORMULA_ID FF_FORMULAS_F.FORMULA_ID%type;
1342: L_STATUS_PROCES_ID NUMBER;
1343: L_TOTAL_EARNINGS_VALUE NUMBER :=0;
1344: L_INPUTS FF_EXEC.INPUTS_T;
2898: PEEF.ELEMENT_ENTRY_ID,
2899: PAAF.ASSIGNMENT_NUMBER
2900: FROM PER_ALL_PEOPLE_F PAPF,
2901: PER_ALL_ASSIGNMENTS_F PAAF,
2902: PAY_ELEMENT_ENTRY_VALUES_F PEEVF,
2903: PAY_ELEMENT_ENTRIES_F PEEF,
2904: PER_ASSIGNMENT_STATUS_TYPES PAST
2905: WHERE PAAF.PERSON_ID =PAPF.PERSON_ID
2906: AND PAPF.PERSON_ID =L_PERSON_ID
3048: PEEF.ELEMENT_ENTRY_ID,
3049: PAAF.ASSIGNMENT_NUMBER
3050: FROM PER_ALL_PEOPLE_F PAPF,
3051: PER_ALL_ASSIGNMENTS_F PAAF,
3052: PAY_ELEMENT_ENTRY_VALUES_F PEEVF,
3053: PAY_ELEMENT_ENTRIES_F PEEF,
3054: PER_ASSIGNMENT_STATUS_TYPES PAST
3055: WHERE PAAF.PERSON_ID =PAPF.PERSON_ID
3056: AND PAPF.PERSON_ID =L_PERSON_ID
3603: PAAF.ASSIGNMENT_ID,
3604: PEEF.ELEMENT_ENTRY_ID
3605: FROM PER_ALL_PEOPLE_F PAPF,
3606: PER_ALL_ASSIGNMENTS_F PAAF,
3607: PAY_ELEMENT_ENTRY_VALUES_F PEEVF,
3608: PAY_ELEMENT_ENTRIES_F PEEF,
3609: PER_ASSIGNMENT_STATUS_TYPES PAST
3610: WHERE PAAF.PERSON_ID =PAPF.PERSON_ID
3611: AND PAPF.PERSON_ID =P_PERSON_ID
3621: AND L_PRP_END_DATE BETWEEN PEEVF.EFFECTIVE_START_DATE AND PEEVF.EFFECTIVE_END_DATE
3622: AND L_PRP_END_DATE BETWEEN PAAF.EFFECTIVE_START_DATE AND PAAF.EFFECTIVE_END_DATE
3623: AND L_PRP_END_DATE BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE;
3624: -----------------------------
3625: L_SCREEN_ENTRY_VALUE PAY_ELEMENT_ENTRY_VALUES_F.SCREEN_ENTRY_VALUE%type;
3626: L_FORMULA_ID FF_FORMULAS_F.FORMULA_ID%type;
3627: L_STATUS_PROCES_ID NUMBER;
3628: L_TOTAL_EARNINGS_VALUE NUMBER :=0;
3629: L_INPUTS FF_EXEC.INPUTS_T;
4029: --Cursor to Check if the EJH Postponement has been applied for the assignment already
4030: CURSOR CHECK_POSTP_EJH(P_ELEMENT_ENTRY_ID NUMBER)
4031: IS
4032: SELECT 1
4033: FROM PAY_ELEMENT_ENTRY_VALUES_F
4034: WHERE ELEMENT_ENTRY_ID = P_ELEMENT_ENTRY_ID
4035: AND INPUT_VALUE_ID = G_ELEMENT_DETAILS.G_POSTPONEMENT_TYPE_ID
4036: AND EFFECTIVE_START_DATE <= G_PRP_END_DATE
4037: AND SCREEN_ENTRY_VALUE = 'Eligible Job Holder Postponement';
4056: IS
4057: SELECT PAAF.ASSIGNMENT_NUMBER
4058: FROM PER_ALL_PEOPLE_F PAPF,
4059: PER_ALL_ASSIGNMENTS_F PAAF,
4060: PAY_ELEMENT_ENTRY_VALUES_F PEEVF,
4061: PAY_ELEMENT_ENTRIES_F PEEF,
4062: PER_ASSIGNMENT_STATUS_TYPES PAST
4063: WHERE PAAF.PERSON_ID =PAPF.PERSON_ID
4064: AND PAPF.PERSON_ID =P_PERSON_ID
4093: */
4094: cursor c_ejh_history(P_ASSIGNMENT_ID number)
4095: is
4096: SELECT count (*)
4097: FROM pay_element_entry_values_f p1
4098: , pay_element_entry_values_f p2
4099: WHERE p1.element_entry_id = p2.element_entry_id
4100: AND p1.element_entry_id IN
4101: (
4094: cursor c_ejh_history(P_ASSIGNMENT_ID number)
4095: is
4096: SELECT count (*)
4097: FROM pay_element_entry_values_f p1
4098: , pay_element_entry_values_f p2
4099: WHERE p1.element_entry_id = p2.element_entry_id
4100: AND p1.element_entry_id IN
4101: (
4102: SELECT element_entry_id