View: PAY_IE_BIK_CASH_V

Product:	PAY - Payroll
Description:	Used for Web ADI reporting purposes
Implementation/DBA Data:	APPS.PAY_IE_BIK_CASH_V

View Text

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')

Columns