View: PAY_IN_ASG_ELEMENTS_V

Product:	PAY - Payroll
Description:
Implementation/DBA Data:	APPS.PAY_IN_ASG_ELEMENTS_V

View Text

SELECT PET.ROWID ROW_ID
, PAA.ASSIGNMENT_ACTION_ID ASSIGNMENT_ACTION_ID
, DECODE(PEC.CLASSIFICATION_NAME
, 'PRE TAX DEDUCTIONS'
, 'DEDUCTIONS'
, 'TAX DEDUCTIONS'
, 'DEDUCTIONS'
, 'INVOLUNTARY DEDUCTIONS'
, 'DEDUCTIONS'
, 'VOLUNTARY DEDUCTIONS'
, 'DEDUCTIONS'
, 'ALLOWANCES'
, 'EARNINGS'
, 'ADVANCES'
, 'ADVANCES'
, 'FRINGE BENEFITS'
, 'FRINGE BENEFITS'
, 'PAID MONETARY PERQUISITE'
, 'PAID MONETARY PERQUISITE'
, PEC.CLASSIFICATION_NAME) CLASSIFICATION_NAME
, NVL(PETL.REPORTING_NAME
, PETL.ELEMENT_NAME) ELEMENT_REPORTING_NAME
, PET.PROCESSING_PRIORITY PROCESSING_PRIORITY
, SUM(DECODE(SUBSTR(PIV.UOM
, 1
, 1)
, 'M'
, PRRV.RESULT_VALUE
, NULL)) AMOUNT
, DECODE(PET.INPUT_CURRENCY_CODE
, 'INR'
, NULL
, PET.INPUT_CURRENCY_CODE) FOREIGN_CURRENCY_CODE
, PAY_IN_PAYSLIP_UTILS.GET_EXCHANGE_RATE(PET.INPUT_CURRENCY_CODE
, PET.OUTPUT_CURRENCY_CODE
, PPA.EFFECTIVE_DATE
, PPA.BUSINESS_GROUP_ID) EXCHANGE_RATE
FROM PAY_PAYROLL_ACTIONS PPA
, PAY_ASSIGNMENT_ACTIONS PAA
, PAY_RUN_RESULTS PRR
, PAY_RUN_RESULT_VALUES PRRV
, PAY_INPUT_VALUES_F PIV
, PAY_ELEMENT_TYPES_F PET
, PAY_ELEMENT_TYPES_F_TL PETL
, PAY_ELEMENT_CLASSIFICATIONS PEC
WHERE PPA.ACTION_TYPE IN ('R'
, 'Q')
AND PPA.ACTION_STATUS = 'C'
AND PPA.PAYROLL_ACTION_ID = PAA.PAYROLL_ACTION_ID
AND PAA.ASSIGNMENT_ACTION_ID = PRR.ASSIGNMENT_ACTION_ID
AND PEC.CLASSIFICATION_NAME IN ('EARNINGS'
, 'ALLOWANCES'
, 'PRE TAX DEDUCTIONS'
, 'TAX DEDUCTIONS'
, 'INVOLUNTARY DEDUCTIONS'
, 'VOLUNTARY DEDUCTIONS'
, 'PERQUISITES'
, 'EMPLOYER CHARGES'
, 'ADVANCES'
, 'FRINGE BENEFITS'
, 'TERMINATION PAYMENTS'
, 'PAID MONETARY PERQUISITE' )
AND PEC.LEGISLATION_CODE = 'IN'
AND PEC.CLASSIFICATION_ID = PET.CLASSIFICATION_ID
AND PET.ELEMENT_TYPE_ID = PETL.ELEMENT_TYPE_ID
AND PETL.LANGUAGE = USERENV('LANG')
AND PET.ELEMENT_TYPE_ID = PIV.ELEMENT_TYPE_ID
AND PIV.NAME = 'PAY VALUE'
AND PET.ELEMENT_TYPE_ID = PRR.ELEMENT_TYPE_ID
AND PRR.RUN_RESULT_ID = PRRV.RUN_RESULT_ID
AND PIV.INPUT_VALUE_ID = PRRV.INPUT_VALUE_ID
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 PAA.SOURCE_ACTION_ID IS NOT NULL GROUP BY PET.ROWID
, PAA.ASSIGNMENT_ACTION_ID
, DECODE(PEC.CLASSIFICATION_NAME
, 'PRE TAX DEDUCTIONS'
, 'DEDUCTIONS'
, 'TAX DEDUCTIONS'
, 'DEDUCTIONS'
, 'INVOLUNTARY DEDUCTIONS'
, 'DEDUCTIONS'
, 'VOLUNTARY DEDUCTIONS'
, 'DEDUCTIONS'
, 'ALLOWANCES'
, 'EARNINGS'
, 'ADVANCES'
, 'ADVANCES'
, 'FRINGE BENEFITS'
, 'FRINGE BENEFITS'
, 'PAID MONETARY PERQUISITE'
, 'PAID MONETARY PERQUISITE'
, PEC.CLASSIFICATION_NAME)
, NVL(PETL.REPORTING_NAME
, PETL.ELEMENT_NAME)
, PET.PROCESSING_PRIORITY
, PET.INPUT_CURRENCY_CODE
, PAY_IN_PAYSLIP_UTILS.GET_EXCHANGE_RATE(PET.INPUT_CURRENCY_CODE
, PET.OUTPUT_CURRENCY_CODE
, PPA.EFFECTIVE_DATE
, PPA.BUSINESS_GROUP_ID) UNION ALL SELECT PET.ROWID ROW_ID
, PAI.LOCKING_ACTION_ID ASSIGNMENT_ACTION_ID
, DECODE(PEC.CLASSIFICATION_NAME
, 'PRE TAX DEDUCTIONS'
, 'DEDUCTIONS'
, 'TAX DEDUCTIONS'
, 'DEDUCTIONS'
, 'INVOLUNTARY DEDUCTIONS'
, 'DEDUCTIONS'
, 'VOLUNTARY DEDUCTIONS'
, 'DEDUCTIONS'
, 'ALLOWANCES'
, 'EARNINGS'
, 'ADVANCES'
, 'ADVANCES'
, 'FRINGE BENEFITS'
, 'FRINGE BENEFITS'
, 'PAID MONETARY PERQUISITE'
, 'PAID MONETARY PERQUISITE'
, PEC.CLASSIFICATION_NAME) CLASSIFICATION_NAME
, NVL(PETL.REPORTING_NAME
, PETL.ELEMENT_NAME) ELEMENT_REPORTING_NAME
, PET.PROCESSING_PRIORITY PROCESSING_PRIORITY
, SUM(DECODE(SUBSTR(PIV.UOM
, 1
, 1)
, 'M'
, PRRV.RESULT_VALUE
, NULL)) AMOUNT
, DECODE(PET.INPUT_CURRENCY_CODE
, 'INR'
, NULL
, PET.INPUT_CURRENCY_CODE) FOREIGN_CURRENCY_CODE
, PAY_IN_PAYSLIP_UTILS.GET_EXCHANGE_RATE(PET.INPUT_CURRENCY_CODE
, PET.OUTPUT_CURRENCY_CODE
, PPA.EFFECTIVE_DATE
, PPA.BUSINESS_GROUP_ID) EXCHANGE_RATE
FROM PAY_PAYROLL_ACTIONS PPA
, PAY_ASSIGNMENT_ACTIONS PAA
, PAY_RUN_RESULTS PRR
, PAY_RUN_RESULT_VALUES PRRV
, PAY_INPUT_VALUES_F PIV
, PAY_ELEMENT_TYPES_F PET
, PAY_ELEMENT_TYPES_F_TL PETL
, PAY_ELEMENT_CLASSIFICATIONS PEC
, PAY_ACTION_INTERLOCKS PAI
WHERE PPA.ACTION_TYPE IN ('R'
, 'Q')
AND PPA.ACTION_STATUS = 'C'
AND PPA.PAYROLL_ACTION_ID = PAA.PAYROLL_ACTION_ID
AND PAA.ASSIGNMENT_ACTION_ID = PRR.ASSIGNMENT_ACTION_ID
AND PAI.LOCKED_ACTION_ID = PAA.ASSIGNMENT_ACTION_ID
AND PEC.CLASSIFICATION_NAME IN ('EARNINGS'
, 'ALLOWANCES'
, 'PRE TAX DEDUCTIONS'
, 'TAX DEDUCTIONS'
, 'INVOLUNTARY DEDUCTIONS'
, 'VOLUNTARY DEDUCTIONS'
, 'PERQUISITES'
, 'EMPLOYER CHARGES'
, 'ADVANCES'
, 'FRINGE BENEFITS'
, 'TERMINATION PAYMENTS'
, 'PAID MONETARY PERQUISITE' )
AND PEC.LEGISLATION_CODE = 'IN'
AND PEC.CLASSIFICATION_ID = PET.CLASSIFICATION_ID
AND PET.ELEMENT_TYPE_ID = PETL.ELEMENT_TYPE_ID
AND PETL.LANGUAGE = USERENV('LANG')
AND PET.ELEMENT_TYPE_ID = PIV.ELEMENT_TYPE_ID
AND PIV.NAME = 'PAY VALUE'
AND PET.ELEMENT_TYPE_ID = PRR.ELEMENT_TYPE_ID
AND PRR.RUN_RESULT_ID = PRRV.RUN_RESULT_ID
AND PIV.INPUT_VALUE_ID = PRRV.INPUT_VALUE_ID
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 PAA.SOURCE_ACTION_ID IS NOT NULL GROUP BY PET.ROWID
, PAI.LOCKING_ACTION_ID
, DECODE(PEC.CLASSIFICATION_NAME
, 'PRE TAX DEDUCTIONS'
, 'DEDUCTIONS'
, 'TAX DEDUCTIONS'
, 'DEDUCTIONS'
, 'INVOLUNTARY DEDUCTIONS'
, 'DEDUCTIONS'
, 'VOLUNTARY DEDUCTIONS'
, 'DEDUCTIONS'
, 'ALLOWANCES'
, 'EARNINGS'
, 'ADVANCES'
, 'ADVANCES'
, 'FRINGE BENEFITS'
, 'FRINGE BENEFITS'
, 'PAID MONETARY PERQUISITE'
, 'PAID MONETARY PERQUISITE'
, PEC.CLASSIFICATION_NAME)
, NVL(PETL.REPORTING_NAME
, PETL.ELEMENT_NAME)
, PET.PROCESSING_PRIORITY
, PET.INPUT_CURRENCY_CODE
, PAY_IN_PAYSLIP_UTILS.GET_EXCHANGE_RATE(PET.INPUT_CURRENCY_CODE
, PET.OUTPUT_CURRENCY_CODE
, PPA.EFFECTIVE_DATE
, PPA.BUSINESS_GROUP_ID)

Columns