[Home] [Help]
934: --
935: cursor agg_paye(c_person_id in number,
936: c_effective_date in date) is
937: select decode(p.per_information10,'Y','Y',NULL) agg_paye_flag
938: from per_all_people_f p
939: where p.person_id = c_person_id
940: and c_effective_date between
941: p.effective_start_date and p.effective_end_date;
942: --
960: -- Cursor to find last date after assignment's termination date until which
961: -- aggregation flag has remained Y - assuming flag is Y at the termination date
962: CURSOR get_aggregation_end IS
963: SELECT nvl((min(effective_start_date)-1), hr_general.end_of_time) agg_end_date
964: FROM per_all_people_f
965: WHERE person_id = X_PERSON_ID
966: AND effective_start_date > X_ASSIGNMENT_END_DATE
967: AND nvl(per_information10, 'N') = 'N';
968: --
1427: , org.organization_id
1428: , org.name org_name
1429: , p.date_of_birth /* P45 A4 2008/09 */
1430: , p.sex /* P45 A4 2008/09 */
1431: from per_all_people_f p
1432: , per_all_assignments_f a
1433: , per_periods_of_service serv
1434: , hr_all_organization_units org
1435: , fnd_sessions sess
2282: p.person_id,
2283: decode(p.per_information10,'Y','Y',NULL) agg_paye_flag,
2284: max(a.effective_end_date) asg_end_date,
2285: ptp.regular_payment_date
2286: FROM per_all_people_f p,
2287: per_all_assignments_f a,
2288: per_assignment_status_types past,
2289: pay_all_payrolls_f ppf,
2290: per_time_periods ptp,
2341: decode(p.per_information10,'Y','Y',NULL) agg_paye_flag,
2342: max(a.effective_end_date) asg_end_date,
2343: ptp.regular_payment_date
2344: FROM pay_population_ranges ppr,
2345: per_all_people_f p,
2346: per_all_assignments_f a,
2347: per_assignment_status_types past,
2348: pay_all_payrolls_f ppf,
2349: per_time_periods ptp,
2393: -- Cursor to find last date after assignment's termination date until which
2394: -- aggregation flag has remained Y - assuming flag is Y at the termination date
2395: CURSOR get_agg_end(p_person_id NUMBER, p_term_date DATE) IS
2396: SELECT nvl((min(effective_start_date)-1), hr_general.end_of_time) agg_end_date
2397: FROM per_all_people_f
2398: WHERE person_id = p_person_id
2399: AND effective_start_date > p_term_date
2400: AND nvl(per_information10, 'N') = 'N';
2401: --
2400: AND nvl(per_information10, 'N') = 'N';
2401: --
2402: CURSOR get_agg_start(p_person_id NUMBER, p_term_date DATE) IS
2403: SELECT nvl((max(effective_end_date)+1), hr_general.start_of_time) agg_start_date
2404: FROM per_all_people_f
2405: WHERE person_id = p_person_id
2406: AND effective_end_date < p_term_date
2407: AND nvl(per_information10, 'N') = 'N';
2408: --
3218: p.national_identifier, ass.payroll_id,
3219: decode(p.per_information10,'Y','Y',NULL) agg_paye_flag,
3220: p.date_of_birth, p.sex
3221: FROM
3222: per_all_people_f p,
3223: hr_all_organization_units org,
3224: per_periods_of_service serv,
3225: per_all_assignments_f ass
3226: WHERE ass.assignment_id = p_assid
4865:
4866: cursor csr_aggr_paye_flag (c_person_id number,
4867: c_effective_date date) is
4868: select per_information10
4869: from per_all_people_f
4870: where person_id = c_person_id
4871: and c_effective_date between
4872: effective_start_date and effective_end_date;
4873: --
4885: -- to fetch the earliest aggregation start date from the final payment date.
4886: --
4887: cursor csr_latest_aggr_start_date(c_person_id number, c_effective_date date) is
4888: select max(effective_end_date) + 1
4889: from per_all_people_f
4890: where person_id = c_person_id
4891: and nvl(per_information10,'N') = 'N'
4892: and effective_end_date < c_effective_date;
4893:
4924: l_assignment_action_id NUMBER;
4925: l_issue_date DATE;
4926: l_locked_action_id NUMBER;
4927: l_effective_date DATE;
4928: l_agg_paye_flag per_all_people_f.per_information10%type;
4929: l_found BOOLEAN;
4930: l_dummy NUMBER;
4931:
4932: l_agg_paye_reference hr_soft_coding_keyflex.segment1%type;