View: PA_DR_EXCEPTIONS_SUM_V

Product:	PA - Projects
Description:	Is a view that identifies the Draft Revenues that prevent a period from being closed
Implementation/DBA Data:	APPS.PA_DR_EXCEPTIONS_SUM_V

View Text

SELECT DR.DRAFT_REVENUE_NUM DRAFT_REVENUE_NUM
, DR.PROJECT_ID PROJECT_ID
, DR.PA_DATE PA_DATE
, PRD.PERIOD_NAME PERIOD_NAME
, DR.AGREEMENT_ID AGREEMENT_ID
, SUM(DRI.AMOUNT) AMOUNT
, DR.TRANSFER_REJECTION_REASON TRANSFER_REJECTION_REASON
, PA_EXCEPTION_REASONS_PUB.GET_EXCEPTION_TEXT('REV_EXCP'
, DR.TRANSFER_STATUS_CODE
, DR.TRANSFER_REJECTION_REASON
, 'R') EXCEPTION_REASON
, PA_EXCEPTION_REASONS_PUB.GET_EXCEPTION_TEXT('REV_EXCP'
, DR.TRANSFER_STATUS_CODE
, DR.TRANSFER_REJECTION_REASON
, 'A') CORRECTIVE_ACTION
FROM PA_DRAFT_REVENUES DR
, PA_DRAFT_REVENUE_ITEMS DRI
, PA_IMPLEMENTATIONS IMP
, PA_PERIODS PRD
WHERE IMP.INTERFACE_REVENUE_TO_GL_FLAG = 'Y'
AND DR.TRANSFER_STATUS_CODE IN ('P'
, 'R'
, 'X'
, 'T')
AND DR.DRAFT_REVENUE_NUM = DRI.DRAFT_REVENUE_NUM
AND DR.PROJECT_ID = DRI.PROJECT_ID
AND DR.PA_DATE BETWEEN PRD.START_DATE
AND PRD.END_DATE
AND DR.GENERATION_ERROR_FLAG != 'Y' GROUP BY DR.DRAFT_REVENUE_NUM
, DR.PROJECT_ID
, DR.PA_DATE
, PRD.PERIOD_NAME
, DR.AGREEMENT_ID
, DR.TRANSFER_REJECTION_REASON
, DR.TRANSFER_STATUS_CODE UNION SELECT DR.DRAFT_REVENUE_NUM
, DR.PROJECT_ID
, DR.PA_DATE
, PRD.PERIOD_NAME
, DR.AGREEMENT_ID
, SUM(DRI.AMOUNT)
, DR.TRANSFER_REJECTION_REASON
, PA_EXCEPTION_REASONS_PUB.GET_EXCEPTION_TEXT('REV_EXCP'
, DR.TRANSFER_STATUS_CODE
, TRANSFER_REJECTION_REASON
, 'R') EXCEPTION_REASON
, PA_EXCEPTION_REASONS_PUB.GET_EXCEPTION_TEXT('REV_EXCP'
, DR.TRANSFER_STATUS_CODE
, TRANSFER_REJECTION_REASON
, 'A') CORRECTIVE_ACTION
FROM PA_DRAFT_REVENUES DR
, PA_DRAFT_REVENUE_ITEMS DRI
, PA_PERIODS PRD
WHERE DR.DRAFT_REVENUE_NUM = DRI.DRAFT_REVENUE_NUM
AND DR.PROJECT_ID = DRI.PROJECT_ID
AND DR.PA_DATE BETWEEN PRD.START_DATE
AND PRD.END_DATE
AND EXISTS ( SELECT 1
FROM PA_IMPLEMENTATIONS IMP
, GL_JE_CATEGORIES C
, GL_JE_SOURCES S
, GL_INTERFACE INT
WHERE IMP.INTERFACE_REVENUE_TO_GL_FLAG = 'Y'
AND INT.USER_JE_CATEGORY_NAME = C.USER_JE_CATEGORY_NAME
AND C.JE_CATEGORY_NAME = 'REVENUE'
AND INT.USER_JE_SOURCE_NAME = S.USER_JE_SOURCE_NAME
AND S.JE_SOURCE_NAME = 'PROJECT ACCOUNTING'
AND INT.SET_OF_BOOKS_ID = IMP.SET_OF_BOOKS_ID
AND INT.REFERENCE26 IN ( DR.UNEARNED_BATCH_NAME
, DR.UNBILLED_BATCH_NAME)) GROUP BY DR.DRAFT_REVENUE_NUM
, DR.PROJECT_ID
, DR.PA_DATE
, PRD.PERIOD_NAME
, DR.AGREEMENT_ID
, DR.TRANSFER_REJECTION_REASON
, DR.TRANSFER_STATUS_CODE

Columns