View: PA_GL_DR_EXCEPT_SUM_V

Product:	PA - Projects
Description:	PA_GL_DR_EXCEPT_SUM_V is a view that identifies the Draft Revenues that prevent a period from being closed. Full Description: returns summary information on draft revenues that have not been interfaced to GL and therefore are preventing th
Implementation/DBA Data:	APPS.PA_GL_DR_EXCEPT_SUM_V

View Text

SELECT DR.DRAFT_REVENUE_NUM DRAFT_REVENUE_NUM
, DR.PROJECT_ID PROJECT_ID
, DR.GL_DATE GL_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
, DR.ORG_ID ORG_ID
, IMP.SAME_PA_GL_PERIOD
, PA_EXPENDITURES_UTILS.GETORGTLNAME(DR.ORG_ID) OU_NAME
, IMP.SET_OF_BOOKS_ID
FROM PA_DRAFT_REVENUES_ALL DR
, PA_DRAFT_REVENUE_ITEMS DRI
, PA_IMPLEMENTATIONS_ALL IMP
, GL_PERIOD_STATUSES PRD
WHERE IMP.INTERFACE_REVENUE_TO_GL_FLAG = 'Y'
AND DR.TRANSFER_STATUS_CODE IN ('P'
, 'R'
, 'X'
, 'T') /* DRIVE
FROM INDEX ON TRX STATUS */
AND DR.DRAFT_REVENUE_NUM = DRI.DRAFT_REVENUE_NUM
AND DR.PROJECT_ID = DRI.PROJECT_ID
AND DR.GL_DATE BETWEEN PRD.START_DATE
AND PRD.END_DATE
AND PRD.APPLICATION_ID = 8721
AND PRD.SET_OF_BOOKS_ID = IMP.SET_OF_BOOKS_ID
AND PRD.ADJUSTMENT_PERIOD_FLAG = 'N'
AND DR.GENERATION_ERROR_FLAG <> 'Y'
AND NVL(DR.ORG_ID
, -99) =IMP.ORG_ID GROUP BY DR.DRAFT_REVENUE_NUM
, DR.PROJECT_ID
, DR.GL_DATE
, PRD.PERIOD_NAME
, DR.AGREEMENT_ID
, DR.TRANSFER_REJECTION_REASON
, DR.TRANSFER_STATUS_CODE
, DR.ORG_ID
, IMP.SAME_PA_GL_PERIOD
, IMP.SET_OF_BOOKS_ID UNION SELECT DR.DRAFT_REVENUE_NUM
, DR.PROJECT_ID
, DR.GL_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
, DR.ORG_ID ORG_ID
, IMP1.SAME_PA_GL_PERIOD
, PA_EXPENDITURES_UTILS.GETORGTLNAME(DR.ORG_ID) OU_NAME
, IMP1.SET_OF_BOOKS_ID
FROM PA_DRAFT_REVENUES_ALL DR
, PA_DRAFT_REVENUE_ITEMS DRI
, PA_IMPLEMENTATIONS_ALL IMP1
, GL_PERIOD_STATUSES PRD
WHERE DR.DRAFT_REVENUE_NUM = DRI.DRAFT_REVENUE_NUM
AND DR.PROJECT_ID = DRI.PROJECT_ID
AND DR.GL_DATE BETWEEN PRD.START_DATE
AND PRD.END_DATE
AND PRD.APPLICATION_ID = 8721
AND PRD.SET_OF_BOOKS_ID = IMP1.SET_OF_BOOKS_ID
AND PRD.ADJUSTMENT_PERIOD_FLAG = 'N'
AND NVL(DR.ORG_ID
, -99) = IMP1.ORG_ID
AND EXISTS ( SELECT 1
FROM PA_IMPLEMENTATIONS_ALL 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.REFERENCE21 = DR.UNEARNED_BATCH_NAME OR INT.REFERENCE21 = DR.UNBILLED_BATCH_NAME)) GROUP BY DR.DRAFT_REVENUE_NUM
, DR.PROJECT_ID
, DR.GL_DATE
, PRD.PERIOD_NAME
, DR.AGREEMENT_ID
, DR.TRANSFER_REJECTION_REASON
, DR.TRANSFER_STATUS_CODE
, DR.ORG_ID
, IMP1.SAME_PA_GL_PERIOD
, IMP1.SET_OF_BOOKS_ID

Columns