VIEW: APPS.PAY_IE_BIK_PREF_LOANS_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(decode(piv.name,'Preferential Loan Type' , prrv.result_value ,' ')) Loan_Types, max(decode(piv.name,'Preferential Loan Reference' , prrv.result_value ,' ')) Loan_Reference, max(decode(piv.name,'Loan Start Date' , prrv.result_value ,'0') ) Date_Loan_Accquired, max(decode(piv.name,'Loan End Date' , prrv.result_value ,'0')) Date_loan_Ended, max(decode(piv.name,'Annual Initial Loan Amount' ,prrv.result_value ,'0')) Amount_Outstanding, max(decode(piv.name,'Effective Rate of Interest', prrv.result_value ,'0')) Rate_Percentage, max(decode(piv.name,'Annual Interest Charged',prrv.result_value , '0')) Annual_Interest_Charged, max(pay_ie_bik.get_balance_values(paas.assignment_action_id,prr.source_id,ppa.date_earned,'IE BIK Preferential Loan')) Cumulative_Taxable_Value, 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 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 Preferential Loan 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' 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(DECODE(PIV.NAME
, 'PREFERENTIAL LOAN TYPE'
, PRRV.RESULT_VALUE
, ' ')) LOAN_TYPES
, MAX(DECODE(PIV.NAME
, 'PREFERENTIAL LOAN REFERENCE'
, PRRV.RESULT_VALUE
, ' ')) LOAN_REFERENCE
, MAX(DECODE(PIV.NAME
, 'LOAN START DATE'
, PRRV.RESULT_VALUE
, '0') ) DATE_LOAN_ACCQUIRED
, MAX(DECODE(PIV.NAME
, 'LOAN END DATE'
, PRRV.RESULT_VALUE
, '0')) DATE_LOAN_ENDED
, MAX(DECODE(PIV.NAME
, 'ANNUAL INITIAL LOAN AMOUNT'
, PRRV.RESULT_VALUE
, '0')) AMOUNT_OUTSTANDING
, MAX(DECODE(PIV.NAME
, 'EFFECTIVE RATE OF INTEREST'
, PRRV.RESULT_VALUE
, '0')) RATE_PERCENTAGE
, MAX(DECODE(PIV.NAME
, 'ANNUAL INTEREST CHARGED'
, PRRV.RESULT_VALUE
, '0')) ANNUAL_INTEREST_CHARGED
, MAX(PAY_IE_BIK.GET_BALANCE_VALUES(PAAS.ASSIGNMENT_ACTION_ID
, PRR.SOURCE_ID
, PPA.DATE_EARNED
, 'IE BIK PREFERENTIAL LOAN')) CUMULATIVE_TAXABLE_VALUE
, 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
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 PREFERENTIAL LOAN 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' GROUP BY PRR.RUN_RESULT_ID