[Home] [Help]
View: PA_DR_EXCEPTIONS_SUM_MRC_V
Product: | PA - Projects |
Description: | Is a view that identifies the Draft Revenues that prevent a period from being - Retrofitted |
Implementation/DBA Data: |
Not implemented in this database
|
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.PA_DATE PA_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
, IMP.ORG_ID
FROM PA_DRAFT_REVENUES_ALL DR
, PA_MC_DRAFT_REVS_ALL MDR
, PA_DRAFT_REVENUE_ITEMS_MRC_V MDRI
, PA_IMPLEMENTATIONS IMP
, PA_PERIODS_ALL 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')
AND MDR.DRAFT_REVENUE_NUM = MDRI.DRAFT_REVENUE_NUM
AND MDR.PROJECT_ID = MDRI.PROJECT_ID
AND DR.PA_DATE BETWEEN PRD.START_DATE
AND PRD.END_DATE
AND IMP.ORG_ID = DR.ORG_ID GROUP BY MDR.SET_OF_BOOKS_ID
, MDR.DRAFT_REVENUE_NUM
, MDR.PROJECT_ID
, DR.PA_DATE
, PRD.PERIOD_NAME
, DR.AGREEMENT_ID
, MDR.TRANSFER_REJECTION_REASON
, MDR.TRANSFER_STATUS_CODE
, IMP.ORG_ID UNION ALL SELECT MDR.SET_OF_BOOKS_ID
, MDR.DRAFT_REVENUE_NUM
, MDR.PROJECT_ID
, DR.PA_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
, IMP.ORG_ID
FROM PA_DRAFT_REVENUES_ALL DR
, PA_MC_DRAFT_REVS_ALL MDR
, PA_DRAFT_REVENUE_ITEMS_MRC_V MDRI
, PA_PERIODS_ALL PRD
, PA_IMPLEMENTATIONS 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.PA_DATE BETWEEN PRD.START_DATE
AND PRD.END_DATE
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 IMP.ORG_ID = DR.ORG_ID GROUP BY MDR.SET_OF_BOOKS_ID
, MDR.DRAFT_REVENUE_NUM
, MDR.PROJECT_ID
, DR.PA_DATE
, PRD.PERIOD_NAME
, DR.AGREEMENT_ID
, MDR.TRANSFER_REJECTION_REASON
, MDR.TRANSFER_STATUS_CODE
, IMP.ORG_ID
Columns
Name |
SET_OF_BOOKS_ID |
DRAFT_REVENUE_NUM |
PROJECT_ID |
PA_DATE |
PERIOD_NAME |
AGREEMENT_ID |
AMOUNT |
TRANSFER_REJECTION_REASON |
EXCEPTION_REASON |
CORRECTIVE_ACTION |
ORG_ID |