[Home] [Help]
22: 19-Dec-2012 sampmand 115.7 15989066 Modified to include validation for effective date.
23: 26-Dec-2012 sampmand 115.8 15989066 Modified to remove display of end date for active assignments
24: in warnings section.
25: 09-Jan-2013 sampmand 115.13 15989066 Modified the range cursor to
26: to use per_all_people_f instead of per_people_f
27: 22-Mar-2013 rajganga 115.14 16469408 Modified for address fetching and validations.
28: 25-Mar-2013 rajganga 115.14 16469408 Modified for address fetching and validations.
29: ==========================================================================*/
30:
266:
267: cursor c_get_employee_flag(asg_id number) is
268: select distinct pap.effective_start_date , pap.effective_end_date , pap.current_employee_flag current_employee_flag
269: from per_all_assignments_f paa,
270: per_all_people_f pap,
271: per_periods_of_service serv
272: where paa.assignment_id = asg_id
273: and pap.person_id = paa.person_id
274: and paa.period_of_service_id = serv.period_of_service_id and
293: pap.effective_start_date effective_start_date,
294: serv.actual_termination_date actual_termination_date
295: FROM pay_assignment_actions act,
296: per_all_assignments_f paa,
297: per_all_people_f pap,
298: per_periods_of_service serv,
299: per_assignment_status_types past
300: where act.assignment_action_id = p_assactid
301: and act.assignment_id = paa.assignment_id
326: pap.effective_start_date effective_start_date,
327: serv.actual_termination_date actual_termination_date
328: FROM pay_assignment_actions act,
329: per_all_assignments_f paa,
330: per_all_people_f pap,
331: per_periods_of_service serv,
332: per_assignment_status_types past
333: where act.assignment_action_id = p_assactid
334: and act.assignment_id = paa.assignment_id
906:
907: cursor csr_check_cont_emp(p_person_id number,p_tax_ref varchar2) is
908: select min(paaf.EFFECTIVE_START_DATE) , max(paaf1.EFFECTIVE_END_DATE) , max(paaf.assignment_id) l_prev_asg_id
909: from
910: per_all_people_f peo,
911: per_all_people_f peo1,
912: per_all_assignments_f paaf,
913: per_all_assignments_f paaf1,
914: per_assignment_status_types past,
907: cursor csr_check_cont_emp(p_person_id number,p_tax_ref varchar2) is
908: select min(paaf.EFFECTIVE_START_DATE) , max(paaf1.EFFECTIVE_END_DATE) , max(paaf.assignment_id) l_prev_asg_id
909: from
910: per_all_people_f peo,
911: per_all_people_f peo1,
912: per_all_assignments_f paaf,
913: per_all_assignments_f paaf1,
914: per_assignment_status_types past,
915: per_assignment_status_types past1,
1538:
1539: l_proc CONSTANT VARCHAR2(50):= g_package||'person_addr_validations';
1540: l_ovn number;
1541: l_action_id number;
1542: l_employee_number per_all_people_f.employee_number%TYPE;
1543:
1544:
1545: l_assignment_number per_assignments_f.assignment_number%TYPE;
1546:
1600: l_year2 := to_number(to_char(l_date_of_birth,'YYYY'));
1601: l_year1 := l_year1- l_year2;
1602:
1603: --address validation
1604: SELECT DISTINCT employee_number into l_employee_number from per_all_people_f where EMPLOYEE_NUMBER IS NOT NULL AND person_id=p_tab_rec_data(0).person_id;
1605:
1606:
1607: IF l_address_line1 = ' ' OR l_address_line1 IS NULL THEN
1608: populate_run_msg(p_assactid,'The Address Line 1 of the employee ' || l_employee_number || ' is missing.');
2233:
2234: IF l_report_type = 'RTI_EAS_REP_13' THEN
2235:
2236: sqlstr := 'select distinct person_id '||
2237: 'from per_all_people_f ppf, '||
2238: 'pay_payroll_actions ppa '||
2239: 'where ppa.payroll_action_id = :payroll_action_id '||
2240: 'and ppa.business_group_id = ppf.business_group_id '||
2241: 'order by ppf.person_id';
2269: hr_utility.trace(' Employee ID : '||l_employee_number);
2270:
2271: IF (l_payroll_id is null) THEN
2272: sqlstr := 'select distinct person_id '||
2273: 'from per_all_people_f ppf, '||
2274: 'pay_payroll_actions ppa '||
2275: 'where ppa.payroll_action_id = :payroll_action_id '||
2276: 'and ppa.business_group_id = ppf.business_group_id '||
2277: 'order by ppf.person_id';
2291: END IF;
2292:
2293: IF (l_employee_number is not null ) THEN
2294: sqlstr := 'select distinct ppf.person_id '||
2295: 'from per_all_people_f ppf, '||
2296: 'per_all_assignments_f paaf, '||
2297: 'pay_payroll_actions ppa '||
2298: 'where ppa.payroll_action_id = :payroll_action_id '||
2299: ' and paaf.person_id=ppf.person_id '||
2305:
2306: IF (l_assignment_set is not null)THEN
2307:
2308: sqlstr := 'select distinct person_id '||
2309: 'from per_all_people_f ppf, '||
2310: 'pay_payroll_actions ppa '||
2311: 'where ppa.payroll_action_id = :payroll_action_id '||
2312: 'and ppa.business_group_id = ppf.business_group_id '||
2313: 'order by ppf.person_id';
2385: -- Cursor to fetch Active Assignments
2386: cursor csr_asg_active is
2387: select distinct asg.assignment_id assignment_id,trim(asg.primary_flag) asg_primary_flag,trim(pap.per_information10) per_agg_flag,
2388: pap.person_id person_id,past.per_system_status status
2389: from per_all_people_f pap,
2390: per_all_assignments_f asg,
2391: per_assignment_status_types past,
2392: per_periods_of_service serv,
2393: pay_all_payrolls_f pay,
2425: --Cursor to fetch Terminated Employees
2426: cursor csr_asg_terminated is
2427: select distinct asg.assignment_id assignment_id,trim(asg.primary_flag) asg_primary_flag,
2428: pap.person_id person_id, serv.actual_termination_date actual_termination_date
2429: from per_all_people_f pap,
2430: per_all_assignments_f asg,
2431: per_periods_of_service serv,
2432: pay_all_payrolls_f pay,
2433: hr_soft_coding_keyflex sck,
2449: and nvl(org.org_information10,'UK') = 'UK'
2450: and nvl(l_unique_part_id,substr(sck.segment10,1,12)) = substr(sck.segment10,1,12)
2451: and pap.effective_start_date =
2452: ( select max(pap2.effective_start_date) from
2453: per_all_people_f pap2
2454: where pap2.person_id = pap.person_id
2455: and pap2.effective_start_date <= l_effective_date
2456: )
2457: and asg.effective_start_date =
2468: order by person_id,assignment_id;
2469:
2470: -- Cursor to fetch Agg Flag for a Person
2471: cursor csr_get_agg_flag (p_person_id number) is
2472: select per_information10 per_agg_flag from per_all_people_f
2473: where PERSON_ID = p_person_id
2474: and l_effective_date between effective_start_date and effective_end_date;
2475:
2476: cursor csr_find_next_asg(p_person_id number,p_assignment_id number) is
2474: and l_effective_date between effective_start_date and effective_end_date;
2475:
2476: cursor csr_find_next_asg(p_person_id number,p_assignment_id number) is
2477: select paaf.assignment_id
2478: from per_all_people_f peo,
2479: per_all_assignments_f paaf,
2480: per_all_assignments_f paaf1,
2481: per_assignment_status_types past,
2482: per_assignment_status_types past1,
2709:
2710: cursor csr_asg is
2711: select distinct asg.assignment_id assignment_id,trim(asg.primary_flag) asg_primary_flag,trim(pap.per_information10) per_agg_flag,
2712: pap.person_id person_id,asg.effective_start_date effective_start_date
2713: from per_all_people_f pap,
2714: per_all_assignments_f asg,
2715: per_periods_of_service serv,
2716: pay_all_payrolls_f pay,
2717: hr_soft_coding_keyflex sck,
3706: paa.EFFECTIVE_END_DATE end_date, --to_char(paa.EFFECTIVE_END_DATE,'DD-MON-RRRR') end_date
3707: pap.person_id
3708: from per_all_assignments_f paa,
3709: per_assignment_status_types past,
3710: per_all_people_f pap
3711: ,pay_payroll_actions ppa
3712: where paa.person_id = pap.person_id
3713: and paa.assignment_id = c_assignment_id
3714: and past.ASSIGNMENT_STATUS_TYPE_ID = paa.ASSIGNMENT_STATUS_TYPE_ID
3738: paa.EFFECTIVE_END_DATE end_date, --to_char(paa.EFFECTIVE_END_DATE,'DD-MON-RRRR') end_date
3739: pap.person_id
3740: from per_all_assignments_f paa,
3741: per_assignment_status_types past,
3742: per_all_people_f pap
3743: where paa.person_id = pap.person_id
3744: and paa.assignment_id = c_assignment_id
3745: and past.ASSIGNMENT_STATUS_TYPE_ID = paa.ASSIGNMENT_STATUS_TYPE_ID
3746: and past.per_system_status in ('ACTIVE_ASSIGN', 'SUSP_ASSIGN')