VIEW: APPS.PAY_PAYROLL_ACTIONS_A_V

Source

View Text - Preformatted

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'

View Text - HTML Formatted

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'