VIEW: APPS.PAY_IN_MED_BEN_WEB_ADI_V

Source

View Text - Preformatted

SELECT ppf.employee_number employee_number ,ppf.full_name ,pev.effective_start_date start_date ,pev.effective_end_date ,pay_in_india_benefits.get_entry_value(pev.assignment_id,pev.element_entry_id,'Benefit Amount',pev.effective_start_date) Benefit ,HR_GENERAL.DECODE_LOOKUP('YES_NO',pay_in_india_benefits.get_entry_value(pev.assignment_id,pev.element_entry_id,'Add to Net Pay',pev.effective_start_date)) NetPay ,pay_in_india_benefits.get_entry_value(pev.assignment_id,pev.element_entry_id,'Maximum Annual Limit',pev.effective_start_date) AnnualLimit ,paf.assignment_id ,pev.element_entry_id ,paf.payroll_id ,paf.position_id ,paf.grade_id ,paf.job_id ,paf.organization_id ,paf.business_group_id FROM per_assignments_f paf ,per_people_f ppf ,pay_element_entries_f pev ,pay_element_types_f pet ,hr_organization_information hoi WHERE paf.person_id = ppf.person_id and paf.assignment_id = pev.assignment_id and pev.element_type_id = pet.element_type_id AND pet.element_type_id = hoi.org_information2 AND hoi.org_information_context='PER_IN_REIMBURSE_ELEMENTS' AND hoi.organization_id = paf.business_group_id and pev.effective_start_date between paf.effective_start_date and paf.effective_end_date and pev.effective_start_date between ppf.effective_start_date and ppf.effective_end_date and pev.effective_start_date between pet.effective_start_date and pet.effective_end_date

View Text - HTML Formatted

SELECT PPF.EMPLOYEE_NUMBER EMPLOYEE_NUMBER
, PPF.FULL_NAME
, PEV.EFFECTIVE_START_DATE START_DATE
, PEV.EFFECTIVE_END_DATE
, PAY_IN_INDIA_BENEFITS.GET_ENTRY_VALUE(PEV.ASSIGNMENT_ID
, PEV.ELEMENT_ENTRY_ID
, 'BENEFIT AMOUNT'
, PEV.EFFECTIVE_START_DATE) BENEFIT
, HR_GENERAL.DECODE_LOOKUP('YES_NO'
, PAY_IN_INDIA_BENEFITS.GET_ENTRY_VALUE(PEV.ASSIGNMENT_ID
, PEV.ELEMENT_ENTRY_ID
, 'ADD TO NET PAY'
, PEV.EFFECTIVE_START_DATE)) NETPAY
, PAY_IN_INDIA_BENEFITS.GET_ENTRY_VALUE(PEV.ASSIGNMENT_ID
, PEV.ELEMENT_ENTRY_ID
, 'MAXIMUM ANNUAL LIMIT'
, PEV.EFFECTIVE_START_DATE) ANNUALLIMIT
, PAF.ASSIGNMENT_ID
, PEV.ELEMENT_ENTRY_ID
, PAF.PAYROLL_ID
, PAF.POSITION_ID
, PAF.GRADE_ID
, PAF.JOB_ID
, PAF.ORGANIZATION_ID
, PAF.BUSINESS_GROUP_ID
FROM PER_ASSIGNMENTS_F PAF
, PER_PEOPLE_F PPF
, PAY_ELEMENT_ENTRIES_F PEV
, PAY_ELEMENT_TYPES_F PET
, HR_ORGANIZATION_INFORMATION HOI
WHERE PAF.PERSON_ID = PPF.PERSON_ID
AND PAF.ASSIGNMENT_ID = PEV.ASSIGNMENT_ID
AND PEV.ELEMENT_TYPE_ID = PET.ELEMENT_TYPE_ID
AND PET.ELEMENT_TYPE_ID = HOI.ORG_INFORMATION2
AND HOI.ORG_INFORMATION_CONTEXT='PER_IN_REIMBURSE_ELEMENTS'
AND HOI.ORGANIZATION_ID = PAF.BUSINESS_GROUP_ID
AND PEV.EFFECTIVE_START_DATE BETWEEN PAF.EFFECTIVE_START_DATE
AND PAF.EFFECTIVE_END_DATE
AND PEV.EFFECTIVE_START_DATE BETWEEN PPF.EFFECTIVE_START_DATE
AND PPF.EFFECTIVE_END_DATE
AND PEV.EFFECTIVE_START_DATE BETWEEN PET.EFFECTIVE_START_DATE
AND PET.EFFECTIVE_END_DATE