View: PAY_PAYROLL_ACTIONS_A_V

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

View Text

SELECT PAC1.ROWID ROW_ID
, PAC1.BUSINESS_GROUP_ID + 0 BUSINESS_GROUP_ID
, PAC1.PAYROLL_ID
, PAC1.PAYROLL_ACTION_ID
, PAC1.ACTION_TYPE
, PAC1.EFFECTIVE_DATE
, /* "DATE" */ HR_PAYROLLS.DISPLAY_PERIOD_NAME(PAC1.PAYROLL_ACTION_ID) PERIOD_NAME
, /* "PERIOD" */ LO1.MEANING
, /* "TYPE" */ PRO.PAYROLL_NAME
, /* "PAYROLL"*/ AAC.ASSIGNMENT_ID
, PAC1.ACTION_STATUS STATUS_CODE
, LO2.MEANING ACTION_STATUS
, /* "STATUS" */ PAC1.ACTION_SEQUENCE
, PAC1.DISPLAY_RUN_NUMBER
, PAC1.CONSOLIDATION_SET_ID
, PAC1.ASSIGNMENT_SET_ID
, PAC1.ELEMENT_SET_ID
, PAC1.REQUEST_ID
, DECODE( PML.LINE_SEQUENCE
, NULL
, 'N'
, 'Y' ) MESSAGES_EXIST
FROM HR_LOOKUPS LO1
, HR_LOOKUPS LO2
, PAY_ASSIGNMENT_ACTIONS AAC
, PAY_MESSAGE_LINES PML
, PAY_PAYROLL_ACTIONS PAC1
, PAY_PAYROLLS_F PRO
WHERE /***************************/ /* ACTION TYPE DESCRIPTION */ /***************************/ LO1.LOOKUP_TYPE = 'ACTION_TYPE'
AND LO1.LOOKUP_CODE = PAC1.ACTION_TYPE /****************/ /* PAYROLL NAME */ /****************/
AND PAC1.PAYROLL_ID = PRO.PAYROLL_ID
AND ( PAC1.EFFECTIVE_DATE BETWEEN PRO.EFFECTIVE_START_DATE
AND PRO.EFFECTIVE_END_DATE ) /****************************************/ /* ACTION STATUS CODE
AND ASSIGNMENT ID */ /****************************************/
AND AAC.PAYROLL_ACTION_ID (+) = PAC1.PAYROLL_ACTION_ID
AND ( AAC.PAYROLL_ACTION_ID IS NULL OR AAC.ACTION_STATUS || TO_CHAR(AAC.ASSIGNMENT_ACTION_ID) = ( SELECT TRANSLATE( MIN( TRANSLATE(AAC1.ACTION_STATUS
, 'CBV'
, '~+=') || TO_CHAR(AAC1.ASSIGNMENT_ACTION_ID))
, '~+='
, 'CBV' )
FROM PAY_ASSIGNMENT_ACTIONS AAC1
WHERE AAC1.PAYROLL_ACTION_ID = PAC1.PAYROLL_ACTION_ID ) ) /***************************************************************/ /* ACTION STATUS DESCRIPTION */ /* NOTE THAT THE ACTION STATUS FOR A COMPLETED ACTION */ /* DISPLAYS AS INCOMPLETE IF THERE IS AN ASSOCIATED ASSIGNMENT */ /* ACTION WHICH IS NOT COMPLETE */ /***************************************************************/
AND LO2.LOOKUP_TYPE = 'ACTION_STATUS'
AND LO2.LOOKUP_CODE = DECODE( PAC1.ACTION_STATUS
, 'C'
, DECODE( AAC.ACTION_STATUS
, NULL
, 'C'
, 'C'
, 'C'
, 'B'
, 'C'
, 'V'
, 'C'
, 'I')
, PAC1.ACTION_STATUS ) /**************************************************************/ /* MESSAGES EXIST MESSAGE */ /**************************************************************/
AND PAC1.PAYROLL_ACTION_ID = PML.SOURCE_ID (+)
AND PML.SOURCE_TYPE (+) = 'P'
AND ( PML.LINE_SEQUENCE IS NULL OR PML.LINE_SEQUENCE = ( SELECT MIN(PML2.LINE_SEQUENCE)
FROM PAY_MESSAGE_LINES PML2
WHERE PML2.SOURCE_ID = PAC1.PAYROLL_ACTION_ID ) )
AND PAC1.ACTION_STATUS != 'U' UNION ALL SELECT PAC1.ROWID ROW_ID
, PAC1.BUSINESS_GROUP_ID + 0 BUSINESS_GROUP_ID
, PAC1.PAYROLL_ID
, PAC1.PAYROLL_ACTION_ID
, PAC1.ACTION_TYPE
, PAC1.EFFECTIVE_DATE
, /* "DATE" */ NULL
, LO1.MEANING
, /* "TYPE" */ NULL
, AAC.ASSIGNMENT_ID
, PAC1.ACTION_STATUS STATUS_CODE
, LO2.MEANING ACTION_STATUS
, /* "STATUS" */ PAC1.ACTION_SEQUENCE
, PAC1.DISPLAY_RUN_NUMBER
, PAC1.CONSOLIDATION_SET_ID
, PAC1.ASSIGNMENT_SET_ID
, PAC1.ELEMENT_SET_ID
, PAC1.REQUEST_ID
, DECODE( PML.LINE_SEQUENCE
, NULL
, 'N'
, 'Y' ) MESSAGES_EXIST
FROM HR_LOOKUPS LO1
, HR_LOOKUPS LO2
, PAY_ASSIGNMENT_ACTIONS AAC
, PAY_MESSAGE_LINES PML
, PAY_PAYROLL_ACTIONS PAC1 /*****************************************/ /* JUST PICK UP THE UNRESTRICTED ACTIONS */ /* I.E. THOSE NOT FOR A SPECIFIC PAYROLL */ /*****************************************/
WHERE PAC1.PAYROLL_ID IS NULL /***************************/ /* ACTION TYPE DESCRIPTION */ /***************************/
AND LO1.LOOKUP_TYPE = 'ACTION_TYPE'
AND LO1.LOOKUP_CODE = PAC1.ACTION_TYPE /****************************************/ /* ACTION STATUS CODE
AND ASSIGNMENT ID */ /****************************************/
AND AAC.PAYROLL_ACTION_ID (+) = PAC1.PAYROLL_ACTION_ID
AND ( AAC.PAYROLL_ACTION_ID IS NULL OR AAC.ACTION_STATUS || TO_CHAR(AAC.ASSIGNMENT_ACTION_ID) = ( SELECT TRANSLATE( MIN( TRANSLATE(AAC1.ACTION_STATUS
, 'CBV'
, '~+=') || TO_CHAR(AAC1.ASSIGNMENT_ACTION_ID))
, '~+='
, 'CBV' )
FROM PAY_ASSIGNMENT_ACTIONS AAC1
WHERE AAC1.PAYROLL_ACTION_ID = PAC1.PAYROLL_ACTION_ID ) ) /***************************************************************/ /* ACTION STATUS DESCRIPTION */ /* NOTE THAT THE ACTION STATUS FOR A COMPLETED ACTION */ /* DISPLAYS AS INCOMPLETE IF THERE IS AN ASSOCIATED ASSIGNMENT */ /* ACTION WHICH IS NOT COMPLETE */ /***************************************************************/
AND LO2.LOOKUP_TYPE = 'ACTION_STATUS'
AND LO2.LOOKUP_CODE = DECODE( PAC1.ACTION_STATUS
, 'C'
, DECODE( AAC.ACTION_STATUS
, NULL
, 'C'
, 'C'
, 'C'
, 'B'
, 'C'
, 'V'
, 'C'
, 'I')
, PAC1.ACTION_STATUS ) /**************************************************************/ /* MESSAGES EXIST MESSAGE */ /**************************************************************/
AND PAC1.PAYROLL_ACTION_ID = PML.SOURCE_ID (+)
AND PML.SOURCE_TYPE (+) = 'P'
AND ( PML.LINE_SEQUENCE IS NULL OR PML.LINE_SEQUENCE = ( SELECT MIN(PML2.LINE_SEQUENCE)
FROM PAY_MESSAGE_LINES PML2
WHERE PML2.SOURCE_ID = PAC1.PAYROLL_ACTION_ID ) )
AND PAC1.ACTION_STATUS != 'U'

Columns