[Home] [Help]
58: -- 22 Jan 2003 puchil 2762276 Modified the cursor ir56_employee_info to select area_code_res
59: -- and modified tab_empl(8).item_value ('X_HK_RESIDENTIAL_ADDRESS_AREA_CODE')
60: -- to archive area_code_res instead of town_or_city if address_type is 'HK_R'
61: -- 24 Jan 2003 srrajago 2760137 Cursor quarters_info modified to include per_all_assignments_f and
62: -- per_periods_of_service tables and related joins. quarters_period_start
63: -- and quarters_period_end have also been modified.
64: -- 27 Jan 2003 srrajago 2760137 Included nvl check for actual_termination_date in the field
65: -- quarters_period_end of the cursor quarters_info.
66: -- 10 Feb 2003 puchil 2778848 Changed the effective date check in cursor process_assignments
261: is now done through a cursor check_run and hence that part of the query has been removed from process
262: assignments */
263:
264: /*Bug No : 2778848 - Modified cursor process_assignments, the select statement is changed to
265: eliminate the terminated employee details, by including per_periods_of_service and checking
266: for the existance of assignment as of 31 of march. Since terminated employees are not selected, the
267: check for whether IR56F or IR56G is run becomes invalid hence this check is removed. */
268:
269: CURSOR process_assignments
278: IS
279: SELECT DISTINCT a.assignment_id assignment_id
280: FROM per_assignments_f a,
281: pay_payroll_actions pa,
282: per_periods_of_service pps
283: WHERE pa.payroll_action_id = c_payroll_action_id
284: AND a.person_id BETWEEN c_start_person_id and c_end_person_id
285: AND a.business_group_id = pa.business_group_id
286: AND TO_DATE('31-03-'||c_reporting_year, 'DD-MM-YYYY') between a.effective_start_date and a.effective_end_date
445: fnd_territories_tl ftv, /* Bug No : 3059915 */
446: per_contact_relationships pcr,
447: per_addresses pad,
448: per_assignment_extra_info paei,
449: per_periods_of_service pps,
450: hr_soft_coding_keyflex hsck,
451: hr_lookups hrl
452: WHERE paaf.person_id = papf.person_id
453: AND TO_DATE('31-03-'|| c_reporting_year, 'DD-MM-YYYY')
1079: max(b.QUARTERS_EE_TO_LANDLORD) QUARTERS_EE_TO_LANDLORD,
1080: max(b.QUARTERS_REFUND_TO_EE) QUARTERS_REFUND_TO_EE,
1081: max(b.QUARTERS_EE_TO_ER) QUARTERS_EE_TO_ER
1082: FROM pay_hk_ir56_quarters_info_v b,
1083: per_periods_of_service pps,
1084: per_assignments_f paa
1085: WHERE b.assignment_id = c_assignment_id
1086: AND paa.assignment_id = b.assignment_id
1087: AND paa.person_id = pps.person_id
1336: ,last_name) last_name,
1337: TRIM(papf.first_name||' '||papf.middle_names) other_name
1338: FROM per_people_f papf,
1339: per_assignments_f paaf,
1340: per_periods_of_service pps
1341: WHERE paaf.person_id = papf.person_id
1342: AND TO_DATE('31-03-'|| c_reporting_year, 'DD-MM-YYYY')
1343: BETWEEN paaf.effective_start_date and paaf.effective_end_date
1344: AND TO_DATE('31-03-'|| c_reporting_year, 'DD-MM-YYYY')