VIEW: APPS.PAY_IN_LTC_ELE_WEB_ADI_V

Source

View Text - Preformatted

SELECT pae.aei_information1 ltc_block , ppf.employee_number employee_number , pay_in_utils.get_person_name (pae.aei_information2, ppf.effective_start_date) name , pae.aei_information3 place_from , pae.aei_information7 place_to , hr_general.decode_lookup ('IN_TRAVEL_MODE_CLASS', pae.aei_information8) mode_class , hr_general.decode_lookup ('YES_NO', pae.aei_information18) carry_over , pae.aei_information9 claim_amt , pae.aei_information10 exempted_fare , pae.aei_information11 element_entry_id , fnd_date.canonical_to_date (pae.aei_information12) start_date , fnd_date.canonical_to_date (pae.aei_information13) end_date , pae.aei_information4 bill_num , pae.aei_information5 ee_comments , pae.aei_information6 er_comments , pae.last_update_date last_updated_date , paf.assignment_id assignment_id , ppf.person_id employee_id , ppf.full_name employee_name , pae.assignment_extra_info_id assignment_extra_info_id , paf.payroll_id payroll_id , paf.position_id position_id , paf.grade_id grade_id , paf.job_id job_id , paf.organization_id organization_id , paf.business_group_id business_group_id , peev.screen_entry_value benefit FROM per_people_f ppf , per_person_types ptypes , per_assignments_f paf , per_assignment_extra_info pae ,pay_element_entries_f pee ,pay_element_entry_values_f peev ,pay_input_values_f piv WHERE ppf.person_id = paf.person_id AND ptypes.system_person_type IN ('EMP', 'EX_EMP', 'APL_EX_EMP' , 'EMP_APL', 'EX_EMP_APL') AND ptypes.person_type_id = ppf.person_type_id AND paf.assignment_id = pae.assignment_id AND ppf.business_group_id = paf.business_group_id AND fnd_date.canonical_to_date (pae.aei_information13) BETWEEN ppf.effective_start_date AND ppf.effective_end_date AND fnd_date.canonical_to_date (pae.aei_information13) BETWEEN paf.effective_start_date AND paf.effective_end_date AND pae.aei_information_category = 'PER_IN_LTC_BILLS' and pee.element_entry_id = pae.aei_information11 and peev.element_entry_id = pee.element_entry_id and piv.element_type_id = pee.element_type_id and peev.input_value_id= piv.input_value_id and piv.name = 'Employer Contribution' UNION SELECT pae.aei_information1 ltc_block , ppf.employee_number employee_number , pay_in_utils.get_person_name (pae.aei_information2, ppf.effective_start_date) name , pae.aei_information3 place_from , pae.aei_information7 place_to , hr_general.decode_lookup ('IN_TRAVEL_MODE_CLASS', pae.aei_information8) mode_class , hr_general.decode_lookup ('YES_NO', pae.aei_information18) carry_over , pae.aei_information9 claim_amt , pae.aei_information10 exempted_fare , pae.aei_information11 element_entry_id , fnd_date.canonical_to_date (pae.aei_information12) start_date , fnd_date.canonical_to_date (pae.aei_information13) end_date , pae.aei_information4 bill_num , pae.aei_information5 ee_comments , pae.aei_information6 er_comments , pae.last_update_date last_updated_date , paf.assignment_id assignment_id , ppf.person_id employee_id , ppf.full_name employee_name , pae.assignment_extra_info_id assignment_extra_info_id , paf.payroll_id payroll_id , paf.position_id position_id , paf.grade_id grade_id , paf.job_id job_id , paf.organization_id organization_id , paf.business_group_id business_group_id , pae.aei_information21 benefit FROM per_people_f ppf , per_person_types ptypes , per_assignments_f paf , per_assignment_extra_info pae WHERE ppf.person_id = paf.person_id AND ptypes.system_person_type IN ('EMP', 'EX_EMP', 'APL_EX_EMP' , 'EMP_APL', 'EX_EMP_APL') AND ptypes.person_type_id = ppf.person_type_id AND paf.assignment_id = pae.assignment_id AND ppf.business_group_id = paf.business_group_id AND fnd_date.canonical_to_date (pae.aei_information13) BETWEEN ppf.effective_start_date AND ppf.effective_end_date AND fnd_date.canonical_to_date (pae.aei_information13) BETWEEN paf.effective_start_date AND paf.effective_end_date AND pae.aei_information_category = 'PER_IN_LTC_BILLS' and pae.aei_information11 is null

View Text - HTML Formatted

SELECT PAE.AEI_INFORMATION1 LTC_BLOCK
, PPF.EMPLOYEE_NUMBER EMPLOYEE_NUMBER
, PAY_IN_UTILS.GET_PERSON_NAME (PAE.AEI_INFORMATION2
, PPF.EFFECTIVE_START_DATE) NAME
, PAE.AEI_INFORMATION3 PLACE_FROM
, PAE.AEI_INFORMATION7 PLACE_TO
, HR_GENERAL.DECODE_LOOKUP ('IN_TRAVEL_MODE_CLASS'
, PAE.AEI_INFORMATION8) MODE_CLASS
, HR_GENERAL.DECODE_LOOKUP ('YES_NO'
, PAE.AEI_INFORMATION18) CARRY_OVER
, PAE.AEI_INFORMATION9 CLAIM_AMT
, PAE.AEI_INFORMATION10 EXEMPTED_FARE
, PAE.AEI_INFORMATION11 ELEMENT_ENTRY_ID
, FND_DATE.CANONICAL_TO_DATE (PAE.AEI_INFORMATION12) START_DATE
, FND_DATE.CANONICAL_TO_DATE (PAE.AEI_INFORMATION13) END_DATE
, PAE.AEI_INFORMATION4 BILL_NUM
, PAE.AEI_INFORMATION5 EE_COMMENTS
, PAE.AEI_INFORMATION6 ER_COMMENTS
, PAE.LAST_UPDATE_DATE LAST_UPDATED_DATE
, PAF.ASSIGNMENT_ID ASSIGNMENT_ID
, PPF.PERSON_ID EMPLOYEE_ID
, PPF.FULL_NAME EMPLOYEE_NAME
, PAE.ASSIGNMENT_EXTRA_INFO_ID ASSIGNMENT_EXTRA_INFO_ID
, PAF.PAYROLL_ID PAYROLL_ID
, PAF.POSITION_ID POSITION_ID
, PAF.GRADE_ID GRADE_ID
, PAF.JOB_ID JOB_ID
, PAF.ORGANIZATION_ID ORGANIZATION_ID
, PAF.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, PEEV.SCREEN_ENTRY_VALUE BENEFIT
FROM PER_PEOPLE_F PPF
, PER_PERSON_TYPES PTYPES
, PER_ASSIGNMENTS_F PAF
, PER_ASSIGNMENT_EXTRA_INFO PAE
, PAY_ELEMENT_ENTRIES_F PEE
, PAY_ELEMENT_ENTRY_VALUES_F PEEV
, PAY_INPUT_VALUES_F PIV
WHERE PPF.PERSON_ID = PAF.PERSON_ID
AND PTYPES.SYSTEM_PERSON_TYPE IN ('EMP'
, 'EX_EMP'
, 'APL_EX_EMP'
, 'EMP_APL'
, 'EX_EMP_APL')
AND PTYPES.PERSON_TYPE_ID = PPF.PERSON_TYPE_ID
AND PAF.ASSIGNMENT_ID = PAE.ASSIGNMENT_ID
AND PPF.BUSINESS_GROUP_ID = PAF.BUSINESS_GROUP_ID
AND FND_DATE.CANONICAL_TO_DATE (PAE.AEI_INFORMATION13) BETWEEN PPF.EFFECTIVE_START_DATE
AND PPF.EFFECTIVE_END_DATE
AND FND_DATE.CANONICAL_TO_DATE (PAE.AEI_INFORMATION13) BETWEEN PAF.EFFECTIVE_START_DATE
AND PAF.EFFECTIVE_END_DATE
AND PAE.AEI_INFORMATION_CATEGORY = 'PER_IN_LTC_BILLS'
AND PEE.ELEMENT_ENTRY_ID = PAE.AEI_INFORMATION11
AND PEEV.ELEMENT_ENTRY_ID = PEE.ELEMENT_ENTRY_ID
AND PIV.ELEMENT_TYPE_ID = PEE.ELEMENT_TYPE_ID
AND PEEV.INPUT_VALUE_ID= PIV.INPUT_VALUE_ID
AND PIV.NAME = 'EMPLOYER CONTRIBUTION' UNION SELECT PAE.AEI_INFORMATION1 LTC_BLOCK
, PPF.EMPLOYEE_NUMBER EMPLOYEE_NUMBER
, PAY_IN_UTILS.GET_PERSON_NAME (PAE.AEI_INFORMATION2
, PPF.EFFECTIVE_START_DATE) NAME
, PAE.AEI_INFORMATION3 PLACE_FROM
, PAE.AEI_INFORMATION7 PLACE_TO
, HR_GENERAL.DECODE_LOOKUP ('IN_TRAVEL_MODE_CLASS'
, PAE.AEI_INFORMATION8) MODE_CLASS
, HR_GENERAL.DECODE_LOOKUP ('YES_NO'
, PAE.AEI_INFORMATION18) CARRY_OVER
, PAE.AEI_INFORMATION9 CLAIM_AMT
, PAE.AEI_INFORMATION10 EXEMPTED_FARE
, PAE.AEI_INFORMATION11 ELEMENT_ENTRY_ID
, FND_DATE.CANONICAL_TO_DATE (PAE.AEI_INFORMATION12) START_DATE
, FND_DATE.CANONICAL_TO_DATE (PAE.AEI_INFORMATION13) END_DATE
, PAE.AEI_INFORMATION4 BILL_NUM
, PAE.AEI_INFORMATION5 EE_COMMENTS
, PAE.AEI_INFORMATION6 ER_COMMENTS
, PAE.LAST_UPDATE_DATE LAST_UPDATED_DATE
, PAF.ASSIGNMENT_ID ASSIGNMENT_ID
, PPF.PERSON_ID EMPLOYEE_ID
, PPF.FULL_NAME EMPLOYEE_NAME
, PAE.ASSIGNMENT_EXTRA_INFO_ID ASSIGNMENT_EXTRA_INFO_ID
, PAF.PAYROLL_ID PAYROLL_ID
, PAF.POSITION_ID POSITION_ID
, PAF.GRADE_ID GRADE_ID
, PAF.JOB_ID JOB_ID
, PAF.ORGANIZATION_ID ORGANIZATION_ID
, PAF.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, PAE.AEI_INFORMATION21 BENEFIT
FROM PER_PEOPLE_F PPF
, PER_PERSON_TYPES PTYPES
, PER_ASSIGNMENTS_F PAF
, PER_ASSIGNMENT_EXTRA_INFO PAE
WHERE PPF.PERSON_ID = PAF.PERSON_ID
AND PTYPES.SYSTEM_PERSON_TYPE IN ('EMP'
, 'EX_EMP'
, 'APL_EX_EMP'
, 'EMP_APL'
, 'EX_EMP_APL')
AND PTYPES.PERSON_TYPE_ID = PPF.PERSON_TYPE_ID
AND PAF.ASSIGNMENT_ID = PAE.ASSIGNMENT_ID
AND PPF.BUSINESS_GROUP_ID = PAF.BUSINESS_GROUP_ID
AND FND_DATE.CANONICAL_TO_DATE (PAE.AEI_INFORMATION13) BETWEEN PPF.EFFECTIVE_START_DATE
AND PPF.EFFECTIVE_END_DATE
AND FND_DATE.CANONICAL_TO_DATE (PAE.AEI_INFORMATION13) BETWEEN PAF.EFFECTIVE_START_DATE
AND PAF.EFFECTIVE_END_DATE
AND PAE.AEI_INFORMATION_CATEGORY = 'PER_IN_LTC_BILLS'
AND PAE.AEI_INFORMATION11 IS NULL