[Home] [Help]
78: ,pap.accrual_plan_element_type_id accrual_plan_element_type_id
79: ,pee.effective_start_date effective_start_date
80: ,asg.payroll_id payroll_id
81: from pay_accrual_plans pap
82: ,pay_element_entries_f pee
83: ,per_all_assignments_f asg
84: where pap.business_group_id + 0 = p_business_group_id
85: and asg.assignment_id = p_assignment_id
86: and p_date_earned between asg.effective_start_date and asg.effective_end_date
809: ,pay_element_entry_values_f pevc
810: ,pay_element_entry_values_f pevs
811: ,pay_element_entry_values_f pevy
812: ,pay_element_entry_values_f pevdate
813: ,pay_element_entries_f pee
814: where pevm.input_value_id = p_type_m_iv_id
815: and pevp.input_value_id = p_type_p_iv_id
816: and pevs.input_value_id = p_type_s_iv_id
817: and pevc.input_value_id = p_type_c_iv_id
833: ,p_type_accrual_date_iv_id number) is
834: select sum(pevm.screen_entry_value)
835: from pay_element_entry_values_f pevm
836: ,pay_element_entry_values_f pevdate
837: ,pay_element_entries_f pee
838: where pevm.input_value_id = p_type_m_iv_id
839: and pevdate.input_value_id = p_type_accrual_date_iv_id
840: and pee.element_entry_id = pevm.element_entry_id
841: and pee.element_entry_id = pevdate.element_entry_id
2011: l_effective_start_date date;
2012:
2013: CURSOR csr_get_effective_start_date is
2014: SELECT min(pee.effective_start_date) start_date
2015: from pay_element_entries_f pee
2016: where pee.element_entry_id = p_element_entry_id;
2017:
2018: BEGIN
2019:
2221: --
2222: -- Changed for Performance bug 2662236
2223: CURSOR csr_get_assignment(c_assignment_id number) is
2224: select pee.assignment_id assignment_id
2225: from pay_element_entries_f pee
2226: where pee.element_type_id = l_fr_plan_info.accrual_plan_element_id
2227: and pee.assignment_id = nvl(c_assignment_id,pee.assignment_id)
2228: and l_calculation_date between pee.effective_start_date
2229: and pee.effective_end_date;
2941: ,pap.business_group_id business_Group_id
2942: from pay_accrual_plans pap
2943: ,per_absence_attendances paa
2944: ,per_absence_attendance_types pat
2945: ,pay_element_entries_f pee
2946: ,pay_input_values_f piv
2947: ,pay_input_values_f pivE
2948: ,pay_input_values_f pivO
2949: ,pay_input_values_f pivA
3333: ,sum(pevp.screen_entry_value)
3334: from pay_element_entry_values_f pevm
3335: ,pay_element_entry_values_f pevp
3336: ,pay_element_entry_values_f pevdate
3337: ,pay_element_entries_f pee
3338: where pevm.input_value_id = c_type_m_iv_id
3339: and pevp.input_value_id = c_type_p_iv_id
3340: and pevdate.input_value_id = c_type_accrual_date_iv_id
3341: and pee.element_entry_id = pevm.element_entry_id
3769: ,sum(to_number(nvl(paa.abs_information4,'0'))) /* conventional days booked */
3770: ,sum(to_number(nvl(paa.abs_information5,'0'))) /* Seniority days booked */
3771: ,sum(to_number(nvl(paa.abs_information6,'0'))) /* Young mothers days booked */
3772: from per_absence_attendances paa
3773: ,pay_element_entries_f pee
3774: where pee.element_link_id in (select element_link_id
3775: from pay_element_links_f
3776: where element_type_id = p_holiday_element_id
3777: )
3956: ,peevM.effective_end_date end_date
3957: ,peevM.screen_entry_value main_rate
3958: ,peevP.screen_entry_value protected_rate
3959: from
3960: pay_element_entries_f pee
3961: ,pay_element_entry_values_f peevM
3962: ,pay_element_entry_values_f peevP
3963: where
3964: pee.element_entry_id = peevM.element_entry_id
4111: --
4112: CURSOR csr_latest_ent_date (p_ent_accrual_date_iv_id in number, p_Accrual_start_date in date, p_accrual_end_date in date) is
4113: select max(pee.effective_end_Date)
4114: from pay_element_entry_values_f pevd
4115: ,pay_element_entries_f pee
4116: where pevd.input_value_id = p_ent_accrual_date_iv_id
4117: and pee.element_entry_id = pevd.element_entry_id
4118: and pevd.screen_entry_value between fnd_date.date_to_canonical(p_accrual_start_date)
4119: and fnd_date.date_to_canonical(p_accrual_end_Date)
4184: sum(fnd_number.canonical_to_number(pevs.screen_entry_value))
4185: from pay_element_entry_values_f pevn
4186: ,pay_element_entry_values_f pevd
4187: ,pay_element_entry_values_f pevs
4188: ,pay_element_entries_f pee
4189: where pevn.input_value_id = p_day_input_id
4190: and pevd.input_value_id = p_date_input_id
4191: and pevs.input_value_id = p_sal_input_id
4192: and pee.element_entry_id = pevn.element_entry_id
4608: ,pap.business_group_id business_Group_id
4609: from pay_accrual_plans pap
4610: ,per_absence_attendances paa
4611: ,per_absence_attendance_types pat
4612: ,pay_element_entries_f pee
4613: ,pay_input_values_f piv
4614: ,pay_input_values_f pivE
4615: ,pay_input_values_f pivO
4616: ,pay_input_values_f pivA
4831: ,abs.abs_information4 /* young mothers */
4832: ,abs.abs_information5 /* conventional days */
4833: ,abs.abs_information6 /* protected days */
4834: ,pee.assignment_id
4835: from pay_element_entries_f pee
4836: ,per_absence_attendances abs
4837: where pee.element_entry_id = c_element_entry_id
4838: and pee.creator_id = abs.absence_attendance_id;
4839: --
5187:
5188: CURSOR csr_get_plan is
5189: select pap.accrual_plan_id
5190: from pay_accrual_plans pap
5191: ,pay_element_entries_f pee
5192: ,pay_element_links_f pel
5193: where pee.element_entry_id = P_ELEMENT_ENTRY_ID
5194: and pee.element_link_id = pel.element_link_id
5195: and pel.element_type_id = pap.accrual_plan_element_type_id;
5741: is
5742: l_value_defaulted number(1);
5743: cursor csr_find_entry is
5744: select 0, pev_def.screen_entry_value
5745: from pay_element_entries_f pee
5746: ,pay_element_entry_values_f pev_ref
5747: ,pay_input_values_f piv_ref
5748: ,pay_element_types_f pet
5749: ,pay_input_values_f piv_def
5813: -- Added the following cursor for cheking whether the employee
5814: -- has taken a sick leave between LDW and ATD. Bug#2874154
5815: select 1
5816: from per_absence_attendances paa
5817: ,pay_element_entries_f pee
5818: ,per_absence_attendance_types pat
5819: where pee.creator_type = 'A'
5820: and paa.absence_attendance_id = pee.creator_id
5821: and fnd_date.date_to_canonical (paa.date_start) between fnd_date.date_to_canonical(p_start_date) and fnd_date.date_to_canonical(p_end_date)
5827: CURSOR csr_booked_holiday (p_assignment_id in number, p_holiday_element_id in number
5828: ,p_start_date Date, p_end_date Date) is
5829: select NVL(sum(paa.abs_information2), -1)
5830: from per_absence_attendances paa
5831: ,pay_element_entries_f pee
5832: where pee.element_link_id in (select element_link_id
5833: from pay_element_links_f
5834: where element_type_id = p_holiday_Element_id
5835: )
5910: CURSOR csr_booked_holiday (p_assignment_id in number, p_holiday_element_id in number
5911: ,p_start_date Date, p_end_date Date, p_no_absences in number) is
5912: select 1
5913: from per_absence_attendances paa
5914: ,pay_element_entries_f pee
5915: where pee.element_link_id in (select element_link_id
5916: from pay_element_links_f
5917: where element_type_id = p_holiday_element_id
5918: )
5998: ,c_start_date date
5999: ,c_end_date date) is
6000: select nvl(sum(pevm.screen_entry_value),0)
6001: from pay_element_entry_values_f pevm
6002: ,pay_element_entries_f pee
6003: ,per_all_assignments_f pasg
6004: where pevm.input_value_id = c_type_m_iv_id
6005: and pee.element_entry_id = pevm.element_entry_id
6006: and pevm.effective_start_date between c_start_date and c_end_date