[Home] [Help]
95: FROM per_assignments_f asg
96: ,pay_assignment_actions paa
97: ,pay_payroll_actions ppa
98: ,hr_soft_coding_keyflex hscl
99: ,per_people_f ppf
100: WHERE asg.assignment_id = paa.assignment_id
101: AND paa.payroll_action_id = ppa.payroll_action_id
102: AND ppa.action_type in ('R','Q')
103: AND ppa.action_status = 'C'
132:
133: /* Cursor for fetching person's phone details */
134: CURSOR csr_p_phone_data (l_person_id number,l_ph_type varchar2,l_effective_date date) IS
135: SELECT pp.phone_number
136: FROM per_phones pp,per_people_f ppf
137: WHERE pp.parent_id = ppf.person_id
138: AND pp.phone_type = l_ph_type
139: AND ppf.person_id = l_person_id
140: AND l_effective_date between pp.date_from and nvl(pp.date_to,to_date('31-12-4712','DD-MM-YYYY'));
154:
155: /* Cursor for fetching person's full name */
156: CURSOR csr_get_full_name (l_person_id number, l_effective_date date) IS
157: SELECT ppf.full_name
158: FROM per_people_f ppf
159: WHERE ppf.person_id = l_person_id
160: AND l_effective_date between ppf.effective_start_date and ppf.effective_end_date;
161:
162: /* Cursor for fetching the person's assignment data */
679: FROM per_assignments_f asg
680: ,pay_assignment_actions paa
681: ,pay_payroll_actions ppa
682: ,hr_soft_coding_keyflex hscl
683: ,per_people_f ppf
684: WHERE asg.assignment_id = paa.assignment_id
685: AND paa.payroll_action_id = ppa.payroll_action_id
686: AND ppa.action_type in ('R','Q')
687: AND ppa.action_status = 'C'
704: FROM per_assignments_f asg
705: ,pay_assignment_actions paa
706: ,pay_payroll_actions ppa
707: ,hr_soft_coding_keyflex hscl
708: ,per_people_f ppf
709: WHERE asg.assignment_id = p_assignment_id
710: AND asg.assignment_id = paa.assignment_id
711: AND paa.payroll_action_id = ppa.payroll_action_id
712: AND ppa.action_type in ('R','Q')
725: SELECT decode(l_format,'L',PER_INFORMATION3,SUBSTR(first_name,1,60)) first_name ,
726: decode(l_format,'L',PER_INFORMATION4,SUBSTR(PER_INFORMATION1,1,60)) father_name ,
727: decode(l_format,'L',PER_INFORMATION5,SUBSTR(PER_INFORMATION2,1,60)) grandfather_name,
728: decode(l_format,'L',PER_INFORMATION6,SUBSTR(last_name,1,60)) last_name
729: FROM per_people_f ppf
730: WHERE person_id = p_person_id
731: AND l_effective_date BETWEEN effective_start_date AND effective_end_date;
732: rec_get_emp_name csr_get_emp_name%ROWTYPE;
733:
1341: ,pay_assignment_actions paa
1342: ,pay_payroll_actions ppa
1343: ,hr_soft_coding_keyflex hscl
1344: ,per_periods_of_service pos
1345: ,per_people_f ppf
1346: WHERE asg.assignment_id = paa.assignment_id
1347: AND paa.payroll_action_id = ppa.payroll_action_id
1348: AND pos.period_of_service_id = asg.period_of_service_id
1349: AND ppa.action_type in ('R','Q')
1367: decode(l_format,'L',PER_INFORMATION4,per_information1),
1368: decode(l_format,'L',PER_INFORMATION5,per_information2),
1369: sex,date_of_birth, pos.date_start , national_identifier , per_information9,per_information10,
1370: fnd_date.canonical_to_date(per_information11)
1371: FROM per_people_f ppf , per_periods_of_service pos
1372: WHERE ppf.person_id = p_person_id
1373: AND ppf.person_id = pos.person_id
1374: AND l_effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
1375: AND trunc(l_effective_date,'MM') between trunc(pos.date_start,'MM') and nvl(pos.actual_termination_date,to_date('31-12-4712','DD-MM-YYYY'));
1540:
1541: /* Cursor to fetch person id and termination flag when Employee is the parameter */
1542: CURSOR csr_get_per_term_data (l_asg_id number , l_date date , l_nat varchar2) IS
1543: SELECT ppf.person_id , paf.assignment_id , decode(trunc(pos.actual_termination_date,'MM'),trunc(l_date,'MM'),'Y','N') , decode(trunc(pos.date_start,'MM'),trunc(l_date,'MM'),'Y','N')
1544: FROM per_people_f ppf, per_periods_of_service pos , per_assignments_f paf
1545: WHERE paf.assignment_id = l_asg_id
1546: AND paf.person_id = ppf.person_id
1547: AND paf.period_of_service_id = pos.period_of_service_id
1548: AND paf.person_id = pos.person_id