View: PAY_IE_BIK_COMPANY_VEHICLES_V

Product:	PAY - Payroll
Description:
Implementation/DBA Data:	APPS.PAY_IE_BIK_COMPANY_VEHICLES_V

View Text

SELECT MAX(PAPF.NATIONAL_IDENTIFIER) PPS_NO
, MAX(PAPF.LAST_NAME) SURNAME
, MAX(PAPF.FIRST_NAME) FIRST_NAME
, MAX(PAF.ASSIGNMENT_NUMBER) ASSIGNMENT_NUMBER
, MAX(PQPA.EFFECTIVE_START_DATE) DATE_VEHICLE_PROVIDED
, MAX(PQPA.EFFECTIVE_END_DATE) DATE_VEHICLE_RETURNED
, MAX(PQP.LIST_PRICE) ORIG_MARKET_VALUE_OF_VEHICLE
, MAX(PQP.REGISTRATION_NUMBER) VEHICLE_REGISTRATION_NUMBER
, MAX(DECODE(PIV.NAME
, 'CALCULATION METHOD'
, PRRV.RESULT_VALUE
, '0')) CALCULATION_METHOD
, MAX(PAY_IE_BIK.GET_BALANCE_VALUES(PAAS.ASSIGNMENT_ACTION_ID
, PRR.SOURCE_ID
, PPA.DATE_EARNED
, 'IE BIK VEHICLE CUMULATIVE BUSINESS MILEAGE')) CUMULATIVE_BUSINESS_MILEAGE
, MAX(DECODE(PIV.NAME
, 'DECLARED BUS ANNUAL MILEAGE'
, PRRV.RESULT_VALUE
, '0')) DECLARED_BUSINESS_MILEAGE
, MAX(PAY_IE_BIK.GET_BALANCE_VALUES(PAAS.ASSIGNMENT_ACTION_ID
, PRR.SOURCE_ID
, PPA.DATE_EARNED
, 'IE BIK VEHICLE CUMULATIVE TOTAL MILEAGE')) CUMULATIVE_TOTAL_MILEAGE
, MAX(PPA.ASSIGNMENT_SET_ID) ASSIGNMENT_SET_ID
, MAX(PAF.ASSIGNMENT_ID) ASSIGNMENT_ID
, MAX(PPA.PAYROLL_ID) PAYROLL_ID
, MAX(PPA.CONSOLIDATION_SET_ID) CONSOLIDATION_SET_ID
FROM PAY_ELEMENT_TYPES_F PET
, PAY_INPUT_VALUES_F PIV
, PAY_PAYROLLS_F PPF
, PAY_ASSIGNMENT_ACTIONS PAAS
, PAY_PAYROLL_ACTIONS PPA
, PAY_RUN_RESULTS PRR
, PAY_RUN_RESULT_VALUES PRRV
, PER_PEOPLE_F PAPF
, PER_ASSIGNMENTS_F PAF
, PQP_VEHICLE_REPOSITORY_F PQP
, PQP_VEHICLE_ALLOCATIONS_F PQPA
WHERE PAPF.PERSON_ID=PAF.PERSON_ID
AND PPA.CONSOLIDATION_SET_ID=PPF.CONSOLIDATION_SET_ID
AND PAAS.ASSIGNMENT_ID=PAF.ASSIGNMENT_ID
AND PAPF.BUSINESS_GROUP_ID = PAF.BUSINESS_GROUP_ID
AND PPF.BUSINESS_GROUP_ID = PPA.BUSINESS_GROUP_ID
AND PPA.DATE_EARNED BETWEEN PAF.EFFECTIVE_START_DATE
AND PAF.EFFECTIVE_END_DATE
AND PPA.EFFECTIVE_DATE BETWEEN PAPF.EFFECTIVE_START_DATE
AND PAPF.EFFECTIVE_END_DATE
AND PPF.PAYROLL_ID=PPA.PAYROLL_ID
AND PPA.ACTION_TYPE IN ('R'
, 'Q'
, 'V'
, 'B'
, 'I')
AND PAAS.PAYROLL_ACTION_ID=PPA.PAYROLL_ACTION_ID
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 COMPANY VEHICLE DETAILS'
AND PET.ELEMENT_TYPE_ID=PIV.ELEMENT_TYPE_ID
AND PET.LEGISLATION_CODE = 'IE'
AND PPA.EFFECTIVE_DATE BETWEEN PET.EFFECTIVE_START_DATE
AND PET.EFFECTIVE_END_DATE
AND PPA.EFFECTIVE_DATE BETWEEN PIV.EFFECTIVE_START_DATE
AND PIV.EFFECTIVE_END_DATE
AND PIV.INPUT_VALUE_ID=PRRV.INPUT_VALUE_ID
AND PIV.LEGISLATION_CODE='IE'
AND PQP.VEHICLE_REPOSITORY_ID=PQPA.VEHICLE_REPOSITORY_ID
AND PQPA.ASSIGNMENT_ID=PAF.ASSIGNMENT_ID
AND PQPA.BUSINESS_GROUP_ID=PAF.BUSINESS_GROUP_ID
AND PQP.BUSINESS_GROUP_ID=PQPA.BUSINESS_GROUP_ID GROUP BY PRR.RUN_RESULT_ID

Columns