VIEW: APPS.PAYBV_PAYROLL_MESSAGE_V

Source

View Text - Preformatted

SELECT PRL.PAYROLL_NAME , HR_PAYROLLS.DISPLAY_PERIOD_NAME(PAC.PAYROLL_ACTION_ID) PERIOD_NAME, HR_BIS.BIS_DECODE_LOOKUP('ACTION_TYPE', PAC.ACTION_TYPE) PROCESS_TYPE_M , PAC.EFFECTIVE_DATE PROCESS_DATE, PAY_PAYROLL_ACTIONS_PKG.V_ACTION_STATUS(PAC.PAYROLL_ACTION_ID , PAC.ACTION_STATUS , PAC.REQUEST_ID ) PAYROLL_ACTION_STATUS, NULL FULL_NAME, NULL ASSIGNMENT_NUMBER, NULL ASSIGNMENT_ACTION_STATUS, HR_BIS.BIS_DECODE_LOOKUP('MESSAGE_LEVEL',PML.MESSAGE_LEVEL) SEVERITY_LEVEL_M, nvl(PML.LINE_TEXT, ' ') MESSAGE_LINE, PML.LINE_SEQUENCE LINE_SEQUENCE, HR_BIS.BIS_DECODE_LOOKUP('MESSAGE_SOURCE_TYPE',PML.SOURCE_TYPE) MESSAGE_SOURCE_M, PAC.ACTION_TYPE PROCESS_TYPE, PML.MESSAGE_LEVEL SEVERITY_LEVEL, PML.SOURCE_TYPE MESSAGE_SOURCE, PAC.PAYROLL_ACTION_ID, PAC.PAYROLL_ID, PAC.REQUEST_ID, to_number(NULL) ASSIGNMENT_ACTION_ID, CASE WHEN (PRL.BUSINESS_GROUP_ID = NVL(HR_BIS.GET_SEC_PROFILE_BG_ID, PRL.BUSINESS_GROUP_ID) ) THEN 'Y' ELSE 'N' END BG_SECURITY_FLAG_CODE FROM PAY_PAYROLL_ACTIONS PAC, PAY_PAYROLLS_F PRL, PAY_MESSAGE_LINES PML WHERE PAC.PAYROLL_ID = PRL.PAYROLL_ID AND PAC.EFFECTIVE_DATE BETWEEN PRL.EFFECTIVE_START_DATE AND PRL.EFFECTIVE_END_DATE AND PAC.PAYROLL_ID = PML.PAYROLL_ID AND PML.SOURCE_TYPE = 'P' AND PML.SOURCE_ID = PAC.PAYROLL_ACTION_ID UNION SELECT PRL.PAYROLL_NAME, HR_PAYROLLS.DISPLAY_PERIOD_NAME(PAC.PAYROLL_ACTION_ID) PERIOD_NAME, HR_GENERAL.DECODE_LOOKUP('ACTION_TYPE', PAC.ACTION_TYPE) PROCESS_TYPE_M, PAC.EFFECTIVE_DATE PROCESS_DATE, PAY_PAYROLL_ACTIONS_PKG.V_ACTION_STATUS(PAC.PAYROLL_ACTION_ID , PAC.ACTION_STATUS , PAC.REQUEST_ID ) PAYROLL_ACTION_STATUS, PPF.FULL_NAME, PAS.ASSIGNMENT_NUMBER, PAY_ASSIGNMENT_ACTIONS_PKG.GET_ACTION_STATUS( PAA.ASSIGNMENT_ACTION_ID, PAC.ACTION_TYPE, PAA.ACTION_STATUS) ASSIGNMENT_ACTION_STATUS, HR_GENERAL.DECODE_LOOKUP('MESSAGE_LEVEL',PML.MESSAGE_LEVEL) SEVERITY_LEVEL_M, nvl(PML.LINE_TEXT, ' ') MESSAGE_LINE, PML.LINE_SEQUENCE LINE_SEQUENCE, HR_GENERAL.DECODE_LOOKUP('MESSAGE_SOURCE_TYPE',PML.SOURCE_TYPE) MESSAGE_SOURCE_M, PAC.ACTION_TYPE PROCESS_TYPE, PML.MESSAGE_LEVEL SEVERITY_LEVEL, PML.SOURCE_TYPE MESSAGE_SOURCE, PAC.PAYROLL_ACTION_ID, PAC.PAYROLL_ID, PAC.REQUEST_ID, PAA.ASSIGNMENT_ACTION_ID, CASE WHEN (PRL.BUSINESS_GROUP_ID = NVL(HR_BIS.GET_SEC_PROFILE_BG_ID, PRL.BUSINESS_GROUP_ID) ) THEN 'Y' ELSE 'N' END BG_SECURITY_FLAG_CODE FROM PAY_PAYROLL_ACTIONS PAC, PAY_ASSIGNMENT_ACTIONS PAA, PER_PEOPLE_F PPF, PER_ASSIGNMENTS_F PAS, PAY_PAYROLLS_F PRL, PAY_MESSAGE_LINES PML WHERE PAC.PAYROLL_ID = PRL.PAYROLL_ID AND PAC.EFFECTIVE_DATE BETWEEN PRL.EFFECTIVE_START_DATE AND PRL.EFFECTIVE_END_DATE AND PAC.PAYROLL_ID = PML.PAYROLL_ID AND PML.SOURCE_TYPE = 'A' AND PML.SOURCE_ID = PAA.ASSIGNMENT_ACTION_ID AND PAC.PAYROLL_ACTION_ID = PAA.PAYROLL_ACTION_ID AND PAA.ASSIGNMENT_ID = PAS.ASSIGNMENT_ID AND PAC.EFFECTIVE_DATE BETWEEN PAS.EFFECTIVE_START_DATE AND PAS.EFFECTIVE_END_DATE AND PAS.PERSON_ID = PPF.PERSON_ID AND PAC.EFFECTIVE_DATE BETWEEN PPF.EFFECTIVE_START_DATE AND PPF.EFFECTIVE_END_DATE WITH READ ONLY

View Text - HTML Formatted

SELECT PRL.PAYROLL_NAME
, HR_PAYROLLS.DISPLAY_PERIOD_NAME(PAC.PAYROLL_ACTION_ID) PERIOD_NAME
, HR_BIS.BIS_DECODE_LOOKUP('ACTION_TYPE'
, PAC.ACTION_TYPE) PROCESS_TYPE_M
, PAC.EFFECTIVE_DATE PROCESS_DATE
, PAY_PAYROLL_ACTIONS_PKG.V_ACTION_STATUS(PAC.PAYROLL_ACTION_ID
, PAC.ACTION_STATUS
, PAC.REQUEST_ID ) PAYROLL_ACTION_STATUS
, NULL FULL_NAME
, NULL ASSIGNMENT_NUMBER
, NULL ASSIGNMENT_ACTION_STATUS
, HR_BIS.BIS_DECODE_LOOKUP('MESSAGE_LEVEL'
, PML.MESSAGE_LEVEL) SEVERITY_LEVEL_M
, NVL(PML.LINE_TEXT
, ' ') MESSAGE_LINE
, PML.LINE_SEQUENCE LINE_SEQUENCE
, HR_BIS.BIS_DECODE_LOOKUP('MESSAGE_SOURCE_TYPE'
, PML.SOURCE_TYPE) MESSAGE_SOURCE_M
, PAC.ACTION_TYPE PROCESS_TYPE
, PML.MESSAGE_LEVEL SEVERITY_LEVEL
, PML.SOURCE_TYPE MESSAGE_SOURCE
, PAC.PAYROLL_ACTION_ID
, PAC.PAYROLL_ID
, PAC.REQUEST_ID
, TO_NUMBER(NULL) ASSIGNMENT_ACTION_ID
, CASE WHEN (PRL.BUSINESS_GROUP_ID = NVL(HR_BIS.GET_SEC_PROFILE_BG_ID
, PRL.BUSINESS_GROUP_ID) ) THEN 'Y' ELSE 'N' END BG_SECURITY_FLAG_CODE
FROM PAY_PAYROLL_ACTIONS PAC
, PAY_PAYROLLS_F PRL
, PAY_MESSAGE_LINES PML
WHERE PAC.PAYROLL_ID = PRL.PAYROLL_ID
AND PAC.EFFECTIVE_DATE BETWEEN PRL.EFFECTIVE_START_DATE
AND PRL.EFFECTIVE_END_DATE
AND PAC.PAYROLL_ID = PML.PAYROLL_ID
AND PML.SOURCE_TYPE = 'P'
AND PML.SOURCE_ID = PAC.PAYROLL_ACTION_ID UNION SELECT PRL.PAYROLL_NAME
, HR_PAYROLLS.DISPLAY_PERIOD_NAME(PAC.PAYROLL_ACTION_ID) PERIOD_NAME
, HR_GENERAL.DECODE_LOOKUP('ACTION_TYPE'
, PAC.ACTION_TYPE) PROCESS_TYPE_M
, PAC.EFFECTIVE_DATE PROCESS_DATE
, PAY_PAYROLL_ACTIONS_PKG.V_ACTION_STATUS(PAC.PAYROLL_ACTION_ID
, PAC.ACTION_STATUS
, PAC.REQUEST_ID ) PAYROLL_ACTION_STATUS
, PPF.FULL_NAME
, PAS.ASSIGNMENT_NUMBER
, PAY_ASSIGNMENT_ACTIONS_PKG.GET_ACTION_STATUS( PAA.ASSIGNMENT_ACTION_ID
, PAC.ACTION_TYPE
, PAA.ACTION_STATUS) ASSIGNMENT_ACTION_STATUS
, HR_GENERAL.DECODE_LOOKUP('MESSAGE_LEVEL'
, PML.MESSAGE_LEVEL) SEVERITY_LEVEL_M
, NVL(PML.LINE_TEXT
, ' ') MESSAGE_LINE
, PML.LINE_SEQUENCE LINE_SEQUENCE
, HR_GENERAL.DECODE_LOOKUP('MESSAGE_SOURCE_TYPE'
, PML.SOURCE_TYPE) MESSAGE_SOURCE_M
, PAC.ACTION_TYPE PROCESS_TYPE
, PML.MESSAGE_LEVEL SEVERITY_LEVEL
, PML.SOURCE_TYPE MESSAGE_SOURCE
, PAC.PAYROLL_ACTION_ID
, PAC.PAYROLL_ID
, PAC.REQUEST_ID
, PAA.ASSIGNMENT_ACTION_ID
, CASE WHEN (PRL.BUSINESS_GROUP_ID = NVL(HR_BIS.GET_SEC_PROFILE_BG_ID
, PRL.BUSINESS_GROUP_ID) ) THEN 'Y' ELSE 'N' END BG_SECURITY_FLAG_CODE
FROM PAY_PAYROLL_ACTIONS PAC
, PAY_ASSIGNMENT_ACTIONS PAA
, PER_PEOPLE_F PPF
, PER_ASSIGNMENTS_F PAS
, PAY_PAYROLLS_F PRL
, PAY_MESSAGE_LINES PML
WHERE PAC.PAYROLL_ID = PRL.PAYROLL_ID
AND PAC.EFFECTIVE_DATE BETWEEN PRL.EFFECTIVE_START_DATE
AND PRL.EFFECTIVE_END_DATE
AND PAC.PAYROLL_ID = PML.PAYROLL_ID
AND PML.SOURCE_TYPE = 'A'
AND PML.SOURCE_ID = PAA.ASSIGNMENT_ACTION_ID
AND PAC.PAYROLL_ACTION_ID = PAA.PAYROLL_ACTION_ID
AND PAA.ASSIGNMENT_ID = PAS.ASSIGNMENT_ID
AND PAC.EFFECTIVE_DATE BETWEEN PAS.EFFECTIVE_START_DATE
AND PAS.EFFECTIVE_END_DATE
AND PAS.PERSON_ID = PPF.PERSON_ID
AND PAC.EFFECTIVE_DATE BETWEEN PPF.EFFECTIVE_START_DATE
AND PPF.EFFECTIVE_END_DATE WITH READ ONLY