VIEW: APPS.PAY_IN_MEDICAL_WEB_ADI_V

Source

View Text - Preformatted

SELECT pae.aei_information1 assessment_year ,ppf.employee_number employee_number ,pae.aei_information2 month ,fnd_date.canonical_to_date(pae.aei_information3) bill_date ,pay_in_utils.get_person_name(pae.aei_information4,ppf.effective_start_date) name ,pae.aei_information5 bill_number ,pae.aei_information6 bill_amount ,pae.aei_information7 approved_bill_amount ,pae.aei_information8 employee_remarks ,pae.aei_information9 employer_remarks ,pae.aei_information10 element_entry_id ,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_information3) between ppf.effective_start_date and ppf.effective_end_date AND fnd_date.canonical_to_date(pae.aei_information3) between paf.effective_start_date and paf.effective_end_date AND pae.aei_information_category = 'PER_IN_MEDICAL_BILLS'

View Text - HTML Formatted

SELECT PAE.AEI_INFORMATION1 ASSESSMENT_YEAR
, PPF.EMPLOYEE_NUMBER EMPLOYEE_NUMBER
, PAE.AEI_INFORMATION2 MONTH
, FND_DATE.CANONICAL_TO_DATE(PAE.AEI_INFORMATION3) BILL_DATE
, PAY_IN_UTILS.GET_PERSON_NAME(PAE.AEI_INFORMATION4
, PPF.EFFECTIVE_START_DATE) NAME
, PAE.AEI_INFORMATION5 BILL_NUMBER
, PAE.AEI_INFORMATION6 BILL_AMOUNT
, PAE.AEI_INFORMATION7 APPROVED_BILL_AMOUNT
, PAE.AEI_INFORMATION8 EMPLOYEE_REMARKS
, PAE.AEI_INFORMATION9 EMPLOYER_REMARKS
, PAE.AEI_INFORMATION10 ELEMENT_ENTRY_ID
, 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_INFORMATION3) BETWEEN PPF.EFFECTIVE_START_DATE
AND PPF.EFFECTIVE_END_DATE
AND FND_DATE.CANONICAL_TO_DATE(PAE.AEI_INFORMATION3) BETWEEN PAF.EFFECTIVE_START_DATE
AND PAF.EFFECTIVE_END_DATE
AND PAE.AEI_INFORMATION_CATEGORY = 'PER_IN_MEDICAL_BILLS'