VIEW: APPS.PAY_IE_BIK_COMPANY_VEHICLES_V

Source

View Text - Preformatted

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

View Text - HTML Formatted

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