VIEW: APPS.PAY_IE_BIK_CASH_V

Source

View Text - Preformatted

SELECT max(decode(piv.name,'Benefit Start Date',prrv.result_value,'Benefit Date',prrv.result_value,'0')) Benefit_Start_Date, max(decode(piv.name,'Benefit End Date',prrv.result_value,'Benefit Date',prrv.result_value,'0')) Benefit_End_Date, max(decode(piv.name,'Reportable Item Type',(select meaning from hr_lookups where lookup_type='IE_BIK_REPORTABLE_ITEM' and lookup_code=prrv.result_value),' ')) Type, max(decode(piv.name,'Item Description',prrv.result_value,' ')) Description, max(decode(pet.element_name,'IE BIK Recurring Reportable Items', pay_ie_bik.get_balance_values(paas.assignment_action_id, prr.source_id,ppa.date_earned,'IE BIK Other Reportable Item'), 'IE BIK Non Recurring Reportable Items', decode(piv.name,'Benefit Value',prrv.result_value,'0'))) Amount, max(papf.national_identifier) PPS_No, max(papf.last_name) Surname, max(papf.first_name) First_Name, max(paf.assignment_number) Assignment_Number, max(paf.assignment_id) Assignment_Id, max(ppa.payroll_id) Payroll_Id, max(ppa.consolidation_set_id) Consolidation_Set_Id, max(ppa.assignment_set_id) Assignment_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 in ('IE BIK Recurring Reportable Items','IE BIK Non Recurring Reportable Items')) and pet.element_type_id=piv.element_type_id and pet.legislation_code='IE' and pet.legislation_code =piv.legislation_code 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 having max(decode(piv.name,'P11D Report Section',prrv.result_value,'0')) IN ('CBDEL','CBE')

View Text - HTML Formatted

SELECT MAX(DECODE(PIV.NAME
, 'BENEFIT START DATE'
, PRRV.RESULT_VALUE
, 'BENEFIT DATE'
, PRRV.RESULT_VALUE
, '0')) BENEFIT_START_DATE
, MAX(DECODE(PIV.NAME
, 'BENEFIT END DATE'
, PRRV.RESULT_VALUE
, 'BENEFIT DATE'
, PRRV.RESULT_VALUE
, '0')) BENEFIT_END_DATE
, MAX(DECODE(PIV.NAME
, 'REPORTABLE ITEM TYPE'
, (SELECT MEANING
FROM HR_LOOKUPS
WHERE LOOKUP_TYPE='IE_BIK_REPORTABLE_ITEM'
AND LOOKUP_CODE=PRRV.RESULT_VALUE)
, ' ')) TYPE
, MAX(DECODE(PIV.NAME
, 'ITEM DESCRIPTION'
, PRRV.RESULT_VALUE
, ' ')) DESCRIPTION
, MAX(DECODE(PET.ELEMENT_NAME
, 'IE BIK RECURRING REPORTABLE ITEMS'
, PAY_IE_BIK.GET_BALANCE_VALUES(PAAS.ASSIGNMENT_ACTION_ID
, PRR.SOURCE_ID
, PPA.DATE_EARNED
, 'IE BIK OTHER REPORTABLE ITEM')
, 'IE BIK NON RECURRING REPORTABLE ITEMS'
, DECODE(PIV.NAME
, 'BENEFIT VALUE'
, PRRV.RESULT_VALUE
, '0'))) AMOUNT
, MAX(PAPF.NATIONAL_IDENTIFIER) PPS_NO
, MAX(PAPF.LAST_NAME) SURNAME
, MAX(PAPF.FIRST_NAME) FIRST_NAME
, MAX(PAF.ASSIGNMENT_NUMBER) ASSIGNMENT_NUMBER
, MAX(PAF.ASSIGNMENT_ID) ASSIGNMENT_ID
, MAX(PPA.PAYROLL_ID) PAYROLL_ID
, MAX(PPA.CONSOLIDATION_SET_ID) CONSOLIDATION_SET_ID
, MAX(PPA.ASSIGNMENT_SET_ID) ASSIGNMENT_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 IN ('IE BIK RECURRING REPORTABLE ITEMS'
, 'IE BIK NON RECURRING REPORTABLE ITEMS'))
AND PET.ELEMENT_TYPE_ID=PIV.ELEMENT_TYPE_ID
AND PET.LEGISLATION_CODE='IE'
AND PET.LEGISLATION_CODE =PIV.LEGISLATION_CODE
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 HAVING MAX(DECODE(PIV.NAME
, 'P11D REPORT SECTION'
, PRRV.RESULT_VALUE
, '0')) IN ('CBDEL'
, 'CBE')