View: PAY_IE_BIK_ACCOMMODATION_V

Product:	PAY - Payroll
Description:	IE Views used in WEB ADI Reporting
Implementation/DBA Data:	APPS.PAY_IE_BIK_ACCOMMODATION_V

View Text

SELECT MAX(PPF.NATIONAL_IDENTIFIER) PPS_NO
, MAX(PPF.LAST_NAME) SURNAME
, MAX(PPF.FIRST_NAME) FIRST_NAME
, MAX(PAA.ASSIGNMENT_NUMBER) ASSIGNMENT_NUMBER
, MAX(DECODE(PIV.NAME
, 'RENTAL START DATE'
, PEV.SCREEN_ENTRY_VALUE
, '0')) BENEFIT_START_DATE
, MAX(DECODE(PIV.NAME
, 'RENTAL END DATE'
, PEV.SCREEN_ENTRY_VALUE
, '0')) BENEFIT_END_DATE
, MAX(DECODE(PIV.NAME
, 'ADDRESS ID'
, PAY_IE_BIK.GET_ADDRESS('ADDRESS_LINE1'
, PEV.SCREEN_ENTRY_VALUE)
, ' ')) ADDRESS_LINE_1
, MAX(DECODE(PIV.NAME
, 'ADDRESS ID'
, PAY_IE_BIK.GET_ADDRESS('ADDRESS_LINE2'
, PEV.SCREEN_ENTRY_VALUE)
, ' ')) ADDRESS_LINE_2
, MAX(DECODE(PIV.NAME
, 'ADDRESS ID'
, PAY_IE_BIK.GET_ADDRESS('ADDRESS_LINE3'
, PEV.SCREEN_ENTRY_VALUE)
, ' ')) ADDRESS_LINE_3
, MAX(DECODE(PIV.NAME
, 'ADDRESS ID'
, PAY_IE_BIK.GET_ADDRESS('TOWN_OR_CITY'
, PEV.SCREEN_ENTRY_VALUE)
, ' ')) TOWN
, MAX(DECODE(PIV.NAME
, 'ADDRESS ID'
, PAY_IE_BIK.GET_ADDRESS('REGION_1'
, PEV.SCREEN_ENTRY_VALUE)
, ' ')) COUNTY
, MAX(DECODE(PIV.NAME
, 'ADDRESS ID'
, PAY_IE_BIK.GET_ADDRESS('POSTAL_CODE'
, PEV.SCREEN_ENTRY_VALUE)
, ' ')) POST_CODE
, MAX(DECODE(PIV.NAME
, 'ADDRESS ID'
, PAY_IE_BIK.GET_ADDRESS('COUNTRY'
, PEV.SCREEN_ENTRY_VALUE)
, ' ')) COUNTRY
, MAX(DECODE(PIV.NAME
, 'LANDLORD ORGANIZATION ID'
, (SELECT NAME
FROM HR_ORGANIZATION_UNITS
WHERE ORGANIZATION_ID=PEV.SCREEN_ENTRY_VALUE)
, ' ')) LANDLORD_NAME
, MAX(DECODE(PIV.NAME
, 'LANDLORD ORGANIZATION ID'
, PAY_IE_BIK.GET_LANDLORD_ADDRESS('ADDRESS_LINE1'
, PEV.SCREEN_ENTRY_VALUE)
, ' ')) LANDLORD_ADDRESS_LINE_1
, MAX(DECODE(PIV.NAME
, 'LANDLORD ORGANIZATION ID'
, PAY_IE_BIK.GET_LANDLORD_ADDRESS('ADDRESS_LINE2'
, PEV.SCREEN_ENTRY_VALUE)
, ' ')) LANDLORD_ADDRESS_LINE_2
, MAX(DECODE(PIV.NAME
, 'LANDLORD ORGANIZATION ID'
, PAY_IE_BIK.GET_LANDLORD_ADDRESS('ADDRESS_LINE3'
, PEV.SCREEN_ENTRY_VALUE)
, ' ')) LANDLORD_ADDRESS_LINE_3
, MAX(DECODE(PIV.NAME
, 'LANDLORD ORGANIZATION ID'
, PAY_IE_BIK.GET_LANDLORD_ADDRESS('TOWN_OR_CITY'
, PEV.SCREEN_ENTRY_VALUE)
, ' ')) LANDLORD_TOWN
, MAX(DECODE(PIV.NAME
, 'LANDLORD ORGANIZATION ID'
, PAY_IE_BIK.GET_LANDLORD_ADDRESS('REGION_1'
, PEV.SCREEN_ENTRY_VALUE)
, ' ')) LANDLORD_COUNTY
, MAX(DECODE(PIV.NAME
, 'LANDLORD ORGANIZATION ID'
, PAY_IE_BIK.GET_LANDLORD_ADDRESS('POSTAL_CODE'
, PEV.SCREEN_ENTRY_VALUE)
, ' ')) LANDLORD_POST_CODE
, MAX(DECODE(PIV.NAME
, 'LANDLORD ORGANIZATION ID'
, PAY_IE_BIK.GET_LANDLORD_ADDRESS('COUNTRY'
, PEV.SCREEN_ENTRY_VALUE)
, ' ')) LANDLORD_COUNTRY
, MAX(PAY_IE_BIK.GET_BALANCE_VALUES(PAAS.ASSIGNMENT_ACTION_ID
, PRR.SOURCE_ID
, PPA.DATE_EARNED
, 'IE BIK ACCOMMODATION')) CUMULATIVE_TAXABLE_VALUE
, MAX(PPA.PAYROLL_ID) PAYROLL_ID
, MAX(PAA.ASSIGNMENT_ID) ASSIGNMENT_ID
, MAX(PPA.CONSOLIDATION_SET_ID) CONSOLIDATION_SET_ID
, MAX(PPA.ASSIGNMENT_SET_ID) ASSIGNMENT_SET_ID
FROM PER_PEOPLE_F PPF
, PER_ASSIGNMENTS_F PAA
, PAY_PAYROLL_ACTIONS PPA
, PAY_ASSIGNMENT_ACTIONS PAAS
, PAY_RUN_RESULTS PRR
, PAY_RUN_RESULT_VALUES PRRV
, PAY_ELEMENT_TYPES_F PET
, PAY_INPUT_VALUES_F PIV
, PAY_ELEMENT_ENTRIES_F PEEF
, PAY_ELEMENT_ENTRY_VALUES_F PEV
WHERE PPF.PERSON_ID=PAA.PERSON_ID
AND PPA.EFFECTIVE_DATE BETWEEN PPF.EFFECTIVE_START_DATE
AND PPF.EFFECTIVE_END_DATE
AND PAA.PAYROLL_ID=PPA.PAYROLL_ID
AND PPA.ACTION_TYPE IN ('R'
, 'Q'
, 'V'
, 'B'
, 'I')
AND PPA.PAYROLL_ACTION_ID=PAAS.PAYROLL_ACTION_ID
AND PAA.ASSIGNMENT_ID=PAAS.ASSIGNMENT_ID
AND PPA.EFFECTIVE_DATE BETWEEN PAA.EFFECTIVE_START_DATE
AND PAA.EFFECTIVE_END_DATE
AND PAAS.ACTION_STATUS='C'
AND PAAS.ASSIGNMENT_ACTION_ID=PRR.ASSIGNMENT_ACTION_ID
AND PRR.RUN_RESULT_ID=PRRV.RUN_RESULT_ID
AND PRR.ELEMENT_TYPE_ID=PET.ELEMENT_TYPE_ID
AND PET.ELEMENT_NAME='IE BIK ACCOMMODATION'
AND PET.LEGISLATION_CODE='IE'
AND PET.BUSINESS_GROUP_ID IS NULL
AND PAA.ASSIGNMENT_ID=PEEF.ASSIGNMENT_ID
AND PEEF.ELEMENT_ENTRY_ID=PEV.ELEMENT_ENTRY_ID
AND PPA.DATE_EARNED BETWEEN PEEF.EFFECTIVE_START_DATE
AND PEEF.EFFECTIVE_END_DATE
AND PPA.DATE_EARNED BETWEEN PEV.EFFECTIVE_START_DATE
AND PEV.EFFECTIVE_END_DATE
AND PEV.INPUT_VALUE_ID=PIV.INPUT_VALUE_ID
AND PPA.EFFECTIVE_DATE BETWEEN PIV.EFFECTIVE_START_DATE
AND PIV.EFFECTIVE_END_DATE
AND PIV.ELEMENT_TYPE_ID=PET.ELEMENT_TYPE_ID
AND PPA.EFFECTIVE_DATE BETWEEN PET.EFFECTIVE_START_DATE
AND PET.EFFECTIVE_END_DATE GROUP BY PRR.RUN_RESULT_ID

Columns