VIEW: APPS.PAY_IE_BIK_ACCOMMODATION_V

Source

View Text - Preformatted

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

View Text - HTML Formatted

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