View: PA_GL_DR_EXCEPT_SUM_MRC_V

View Text

SELECT MDR.SET_OF_BOOKS_ID SET_OF_BOOKS_ID
, MDR.DRAFT_REVENUE_NUM DRAFT_REVENUE_NUM
, MDR.PROJECT_ID PROJECT_ID
, DR.GL_DATE GL_DATE
, PRD.PERIOD_NAME PERIOD_NAME
, DR.AGREEMENT_ID AGREEMENT_ID
, SUM(MDRI.AMOUNT) AMOUNT
, MDR.TRANSFER_REJECTION_REASON TRANSFER_REJECTION_REASON
, PA_EXCEPTION_REASONS_PUB.GET_EXCEPTION_TEXT('REV_EXCP'
, MDR.TRANSFER_STATUS_CODE
, MDR.TRANSFER_REJECTION_REASON
, 'R') EXCEPTION_REASON
, PA_EXCEPTION_REASONS_PUB.GET_EXCEPTION_TEXT('REV_EXCP'
, MDR.TRANSFER_STATUS_CODE
, MDR.TRANSFER_REJECTION_REASON
, 'A') CORRECTIVE_ACTION
, DR.ORG_ID ORG_ID
, IMP.SAME_PA_GL_PERIOD
, PA_EXPENDITURES_UTILS.GETORGTLNAME(DR.ORG_ID)
FROM PA_DRAFT_REVENUES_ALL DR
, PA_MC_DRAFT_REVS_ALL MDR
, PA_DRAFT_REVENUE_ITEMS_MRC_V MDRI
, PA_IMPLEMENTATIONS_ALL IMP
, GL_PERIOD_STATUSES PRD
WHERE DR.PROJECT_ID=MDR.PROJECT_ID
AND DR.DRAFT_REVENUE_NUM=MDR.DRAFT_REVENUE_NUM
AND IMP.INTERFACE_REVENUE_TO_GL_FLAG = 'Y'
AND MDR.TRANSFER_STATUS_CODE IN ('P'
, 'R'
, 'X') /* DRIVE
FROM INDEX ON TRX STATUS */
AND MDR.DRAFT_REVENUE_NUM = MDRI.DRAFT_REVENUE_NUM
AND MDR.PROJECT_ID = MDRI.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 NVL(DR.ORG_ID
, -99) = NVL(IMP.ORG_ID
, -99) GROUP BY MDR.SET_OF_BOOKS_ID
, MDR.DRAFT_REVENUE_NUM
, MDR.PROJECT_ID
, DR.GL_DATE
, PRD.PERIOD_NAME
, DR.AGREEMENT_ID
, MDR.TRANSFER_REJECTION_REASON
, MDR.TRANSFER_STATUS_CODE
, DR.ORG_ID
, IMP.SAME_PA_GL_PERIOD UNION ALL SELECT MDR.SET_OF_BOOKS_ID
, MDR.DRAFT_REVENUE_NUM
, MDR.PROJECT_ID
, DR.GL_DATE
, PRD.PERIOD_NAME
, DR.AGREEMENT_ID
, SUM(MDRI.AMOUNT)
, MDR.TRANSFER_REJECTION_REASON
, PA_EXCEPTION_REASONS_PUB.GET_EXCEPTION_TEXT('REV_EXCP'
, MDR.TRANSFER_STATUS_CODE
, MDR.TRANSFER_REJECTION_REASON
, 'R') EXCEPTION_REASON
, PA_EXCEPTION_REASONS_PUB.GET_EXCEPTION_TEXT('REV_EXCP'
, MDR.TRANSFER_STATUS_CODE
, MDR.TRANSFER_REJECTION_REASON
, 'A') CORRECTIVE_ACTION
, DR.ORG_ID ORG_ID
, IMP.SAME_PA_GL_PERIOD
, PA_EXPENDITURES_UTILS.GETORGTLNAME(DR.ORG_ID)
FROM PA_DRAFT_REVENUES_ALL DR
, PA_MC_DRAFT_REVS_ALL MDR
, PA_DRAFT_REVENUE_ITEMS_MRC_V MDRI
, GL_PERIOD_STATUSES PRD
, PA_IMPLEMENTATIONS_ALL IMP
, GL_JE_CATEGORIES C
, GL_JE_SOURCES S
, GL_INTERFACE INT
WHERE DR.PROJECT_ID=MDR.PROJECT_ID
AND DR.DRAFT_REVENUE_NUM=MDR.DRAFT_REVENUE_NUM
AND MDR.DRAFT_REVENUE_NUM = MDRI.DRAFT_REVENUE_NUM
AND MDR.PROJECT_ID = MDRI.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 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.REFERENCE21 = MDR.UNEARNED_BATCH_NAME OR INT.REFERENCE21 = MDR.UNBILLED_BATCH_NAME)
AND MDR.TRANSFER_STATUS_CODE='A'
AND NVL(DR.ORG_ID
, -99) = NVL(IMP.ORG_ID
, -99) GROUP BY MDR.SET_OF_BOOKS_ID
, MDR.DRAFT_REVENUE_NUM
, MDR.PROJECT_ID
, DR.GL_DATE
, PRD.PERIOD_NAME
, DR.AGREEMENT_ID
, MDR.TRANSFER_REJECTION_REASON
, MDR.TRANSFER_STATUS_CODE
, DR.ORG_ID
, IMP.SAME_PA_GL_PERIOD

Columns