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 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'

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
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'