View: PAY_ASSIGNMENT_ACTIONS_V3

Product:	PAY - Payroll
Description:	Used to support user interface
Implementation/DBA Data:	APPS.PAY_ASSIGNMENT_ACTIONS_V3

View Text

SELECT /*+ LEADING(AAC)*/ AAC.ASSIGNMENT_ID ASSIGNMENT_ID
, PET.ELEMENT_TYPE_ID ELEMENT_TYPE_ID
, PAC.EFFECTIVE_DATE EFFECTIVE_DATE
, HR_PAYROLLS.DISPLAY_PERIOD_NAME(PAC.PAYROLL_ACTION_ID) PERIOD_NAME
, HR_GENERAL.DECODE_LOOKUP('ACTION_TYPE'
, PAC.ACTION_TYPE) ACTION_TYPE_MEANING
, AAC.ASSIGNMENT_ACTION_ID ASSIGNMENT_ACTION_ID
, PET.OUTPUT_CURRENCY_CODE OUTPUT_CURRENCY_CODE
, HR_GENERAL.DECODE_LOOKUP('UNITS'
, PIV.UOM) PAY_VALUE_UOM_MEANING
, PRR.RUN_RESULT_ID RUN_RESULT_ID
, HR_CHKFMT.CHANGEFORMAT (PRRV.RESULT_VALUE
, PIV.UOM
, PET.OUTPUT_CURRENCY_CODE) PAY_VALUE_RESULT
, PAC.DATE_EARNED
FROM PAY_ELEMENT_TYPES_F PET
, PAY_INPUT_VALUES_F PIV
, PER_ALL_ASSIGNMENTS_F ASG
, PAY_RUN_RESULT_VALUES PRRV
, PAY_RUN_RESULTS PRR
, PAY_PAYROLL_ACTIONS PAC
, PAY_ASSIGNMENT_ACTIONS AAC
WHERE AAC.ASSIGNMENT_ID = ASG.ASSIGNMENT_ID /* ONLY SELECT THE FOLLOWING PAYROLL ACTIONS: ACTION TYPE MEANING ----------- ------- B BALANCE ADJUSTMENT Q QUICKPAY RUN V REVERSAL R RUN */
AND (PAC.ACTION_TYPE = 'B' OR PAC.ACTION_TYPE = 'Q' OR PAC.ACTION_TYPE = 'V' OR PAC.ACTION_TYPE = 'R') /* ONLY SELECT ASSIGNMENT ACTIONS WHICH ARE COMPLETED */
AND AAC.ACTION_STATUS = 'C' /* FOR THE EMPLOYEE ASSIGNMENT JOIN TO THE TIME PERIODS RESTRICTING BY THE PAYROLL ACTION DATE */
AND AAC.PAYROLL_ACTION_ID = PAC.PAYROLL_ACTION_ID /* FOR THE ASSIGNMENT ACTION SELECT THE RUN RESULTS WHICH WILL CONTRIBUTE TO THE BALANCES: STATUS MEANING ------ ------- PA PROCESSED
AND ADJUSTED P PROCESSED */
AND PRR.ASSIGNMENT_ACTION_ID = AAC.ASSIGNMENT_ACTION_ID
AND (PRR.STATUS = 'PA' OR PRR.STATUS = 'P') /* FOR THE RUN RESULT SELECT THE 'PAY VALUE' IF IT EXISTS */
AND PRRV.RUN_RESULT_ID = PRR.RUN_RESULT_ID
AND PIV.INPUT_VALUE_ID = PRRV.INPUT_VALUE_ID
AND PIV.NAME = 'PAY VALUE'
AND PET.ELEMENT_TYPE_ID (+) = PIV.ELEMENT_TYPE_ID /* DATE EFFECTIVE JOINS */
AND PAC.EFFECTIVE_DATE BETWEEN ASG.EFFECTIVE_START_DATE
AND ASG.EFFECTIVE_END_DATE
AND PAC.EFFECTIVE_DATE BETWEEN PIV.EFFECTIVE_START_DATE
AND PIV.EFFECTIVE_END_DATE
AND PAC.EFFECTIVE_DATE BETWEEN PET.EFFECTIVE_START_DATE
AND PET.EFFECTIVE_END_DATE UNION ALL SELECT /*+ LEADING (AAC)*/ AAC.ASSIGNMENT_ID ASSIGNMENT_ID
, PET.ELEMENT_TYPE_ID ELEMENT_TYPE_ID
, PAC.EFFECTIVE_DATE EFFECTIVE_DATE
, HR_PAYROLLS.DISPLAY_PERIOD_NAME(PAC.PAYROLL_ACTION_ID) PERIOD_NAME
, HR_GENERAL.DECODE_LOOKUP('ACTION_TYPE'
, PAC.ACTION_TYPE) ACTION_TYPE_MEANING
, AAC.ASSIGNMENT_ACTION_ID ASSIGNMENT_ACTION_ID
, PET.OUTPUT_CURRENCY_CODE OUTPUT_CURRENCY_CODE
, NULL
, /* NO PAY VALUE = NO MEANING */ PRR.RUN_RESULT_ID RUN_RESULT_ID
, NULL
, /* NO PAY VALUE = NO MEANING */ PAC.DATE_EARNED
FROM PAY_ELEMENT_TYPES_F PET
, PER_ALL_ASSIGNMENTS_F ASG
, PAY_RUN_RESULTS PRR
, PAY_PAYROLL_ACTIONS PAC
, PAY_ASSIGNMENT_ACTIONS AAC
WHERE AAC.ASSIGNMENT_ID = ASG.ASSIGNMENT_ID
AND (PAC.ACTION_TYPE = 'B' OR PAC.ACTION_TYPE = 'Q' OR PAC.ACTION_TYPE = 'V' OR PAC.ACTION_TYPE = 'R')
AND AAC.ACTION_STATUS = 'C'
AND AAC.PAYROLL_ACTION_ID = PAC.PAYROLL_ACTION_ID
AND PRR.ASSIGNMENT_ACTION_ID = AAC.ASSIGNMENT_ACTION_ID
AND (PRR.STATUS = 'PA' OR PRR.STATUS = 'P') /* SELECT ELEMENTS WITHOUT A PAY VALUE */
AND NOT EXISTS (SELECT 'X'
FROM PAY_INPUT_VALUES_F PIV1
, PAY_RUN_RESULT_VALUES PRRV1
WHERE PRRV1.RUN_RESULT_ID = PRR.RUN_RESULT_ID
AND PRRV1.INPUT_VALUE_ID = PIV1.INPUT_VALUE_ID
AND PIV1.NAME = 'PAY VALUE')
AND PET.ELEMENT_TYPE_ID = PRR.ELEMENT_TYPE_ID /* DATE EFFECTIVE JOINS */
AND PAC.EFFECTIVE_DATE BETWEEN ASG.EFFECTIVE_START_DATE
AND ASG.EFFECTIVE_END_DATE
AND PAC.EFFECTIVE_DATE BETWEEN PET.EFFECTIVE_START_DATE
AND PET.EFFECTIVE_END_DATE

Columns