VIEW: APPS.PAY_IE_PAYMENT_VALUES_V

Source

View Text - Preformatted

SELECT pet.rowid row_id, pet.element_type_id, piv.input_value_id, decode(pet.element_name ,'IE BIK Arrearage Details', SUM(FND_NUMBER.CANONICAL_TO_NUMBER(prv.result_value))*(-1), SUM(FND_NUMBER.CANONICAL_TO_NUMBER(prv.result_value))) result_value, pet.processing_priority, pac.locking_action_id assignment_action_id FROM pay_element_types_f pet, pay_input_values_f piv, pay_run_result_values prv, pay_run_results prr, pay_action_interlocks pac, fnd_sessions ses WHERE prr.element_type_id = pet.element_type_id AND prr.status IN ('P','PA') AND piv.element_type_id = pet.element_type_id AND prv.input_value_id = piv.input_value_id AND prv.run_result_id = prr.run_result_id AND ses.session_id = userenv('sessionid') AND ses.effective_date between piv.effective_start_date and piv.effective_end_date AND ses.effective_date between pet.effective_start_date and pet.effective_end_date AND ( (piv.name ='Pay Value' AND piv.uom = 'M') OR (pet.element_name in ('IE BIK Arrearage Details','IE BIK Arrearage Recovery Details') AND piv.name in ('BIK Arrearage','BIK Arrearage Recovered')) OR (pet.element_name in ('IE BIK Accommodation Details','IE BIK Asset Type Details','IE BIK Company Vehicle Details','IE BIK Preferential Loan Details', 'IE BIK Other Reportable Item Details') AND piv.name = ('Taxable Value for Run')) OR (pet.element_name in ('IE BIK Non Recurring Reportable Items') AND piv.name ='Benefit Value') OR (pet.element_name in ('IE PAYE at higher rate','IE PAYE at standard rate') AND piv.name ='Value' )) AND pet.element_name not in ('IE Net tax') AND pac.locked_action_id = prr.assignment_action_id GROUP BY pac.locking_action_id, pet.processing_priority, pet.element_type_id, pet.element_name,piv.input_value_id, decode(pet.element_name, 'IE BIK Accommodation Details',decode(piv.name,'Taxable Value for Run',prr.run_result_id), 'IE BIK Asset Type Details',decode(piv.name,'Taxable Value for Run',prr.run_result_id), 'IE BIK Company Vehicle Details',decode(piv.name,'Taxable Value for Run',prr.run_result_id), 'IE BIK Preferential Loan Details',decode(piv.name,'Taxable Value for Run',prr.run_result_id), 'IE BIK Other Reportable Item Details',decode(piv.name,'Taxable Value for Run',prr.run_result_id), 'IE BIK Non Recurring Reportable Items',decode(piv.name,'Benefit Value',prr.run_result_id),null),pet.rowid

View Text - HTML Formatted

SELECT PET.ROWID ROW_ID
, PET.ELEMENT_TYPE_ID
, PIV.INPUT_VALUE_ID
, DECODE(PET.ELEMENT_NAME
, 'IE BIK ARREARAGE DETAILS'
, SUM(FND_NUMBER.CANONICAL_TO_NUMBER(PRV.RESULT_VALUE))*(-1)
, SUM(FND_NUMBER.CANONICAL_TO_NUMBER(PRV.RESULT_VALUE))) RESULT_VALUE
, PET.PROCESSING_PRIORITY
, PAC.LOCKING_ACTION_ID ASSIGNMENT_ACTION_ID
FROM PAY_ELEMENT_TYPES_F PET
, PAY_INPUT_VALUES_F PIV
, PAY_RUN_RESULT_VALUES PRV
, PAY_RUN_RESULTS PRR
, PAY_ACTION_INTERLOCKS PAC
, FND_SESSIONS SES
WHERE PRR.ELEMENT_TYPE_ID = PET.ELEMENT_TYPE_ID
AND PRR.STATUS IN ('P'
, 'PA')
AND PIV.ELEMENT_TYPE_ID = PET.ELEMENT_TYPE_ID
AND PRV.INPUT_VALUE_ID = PIV.INPUT_VALUE_ID
AND PRV.RUN_RESULT_ID = PRR.RUN_RESULT_ID
AND SES.SESSION_ID = USERENV('SESSIONID')
AND SES.EFFECTIVE_DATE BETWEEN PIV.EFFECTIVE_START_DATE
AND PIV.EFFECTIVE_END_DATE
AND SES.EFFECTIVE_DATE BETWEEN PET.EFFECTIVE_START_DATE
AND PET.EFFECTIVE_END_DATE
AND ( (PIV.NAME ='PAY VALUE'
AND PIV.UOM = 'M') OR (PET.ELEMENT_NAME IN ('IE BIK ARREARAGE DETAILS'
, 'IE BIK ARREARAGE RECOVERY DETAILS')
AND PIV.NAME IN ('BIK ARREARAGE'
, 'BIK ARREARAGE RECOVERED')) OR (PET.ELEMENT_NAME IN ('IE BIK ACCOMMODATION DETAILS'
, 'IE BIK ASSET TYPE DETAILS'
, 'IE BIK COMPANY VEHICLE DETAILS'
, 'IE BIK PREFERENTIAL LOAN DETAILS'
, 'IE BIK OTHER REPORTABLE ITEM DETAILS')
AND PIV.NAME = ('TAXABLE VALUE FOR RUN')) OR (PET.ELEMENT_NAME IN ('IE BIK NON RECURRING REPORTABLE ITEMS')
AND PIV.NAME ='BENEFIT VALUE') OR (PET.ELEMENT_NAME IN ('IE PAYE AT HIGHER RATE'
, 'IE PAYE AT STANDARD RATE')
AND PIV.NAME ='VALUE' ))
AND PET.ELEMENT_NAME NOT IN ('IE NET TAX')
AND PAC.LOCKED_ACTION_ID = PRR.ASSIGNMENT_ACTION_ID GROUP BY PAC.LOCKING_ACTION_ID
, PET.PROCESSING_PRIORITY
, PET.ELEMENT_TYPE_ID
, PET.ELEMENT_NAME
, PIV.INPUT_VALUE_ID
, DECODE(PET.ELEMENT_NAME
, 'IE BIK ACCOMMODATION DETAILS'
, DECODE(PIV.NAME
, 'TAXABLE VALUE FOR RUN'
, PRR.RUN_RESULT_ID)
, 'IE BIK ASSET TYPE DETAILS'
, DECODE(PIV.NAME
, 'TAXABLE VALUE FOR RUN'
, PRR.RUN_RESULT_ID)
, 'IE BIK COMPANY VEHICLE DETAILS'
, DECODE(PIV.NAME
, 'TAXABLE VALUE FOR RUN'
, PRR.RUN_RESULT_ID)
, 'IE BIK PREFERENTIAL LOAN DETAILS'
, DECODE(PIV.NAME
, 'TAXABLE VALUE FOR RUN'
, PRR.RUN_RESULT_ID)
, 'IE BIK OTHER REPORTABLE ITEM DETAILS'
, DECODE(PIV.NAME
, 'TAXABLE VALUE FOR RUN'
, PRR.RUN_RESULT_ID)
, 'IE BIK NON RECURRING REPORTABLE ITEMS'
, DECODE(PIV.NAME
, 'BENEFIT VALUE'
, PRR.RUN_RESULT_ID)
, NULL)
, PET.ROWID