VIEW: APPS.PA_DR_EXCEPTIONS_SUM_V

Source

View Text - Preformatted

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 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', 'FA', DR.TRANSFER_REJECTION_REASON, 'R') EXCEPTION_REASON , PA_EXCEPTION_REASONS_PUB.GET_EXCEPTION_TEXT('REV_EXCP', 'FA', DR.TRANSFER_REJECTION_REASON, 'A') CORRECTIVE_ACTION FROM PA_DRAFT_REVENUES DR, PA_DRAFT_REVENUE_ITEMS DRI, PA_IMPLEMENTATIONS IMP, PA_PERIODS PRD, XLA_EVENTS XE WHERE IMP.INTERFACE_REVENUE_TO_GL_FLAG = 'Y' AND DR.TRANSFER_STATUS_CODE ='A' AND DR.EVENT_ID = XE.EVENT_ID AND (XE.EVENT_STATUS_CODE<>'P' OR XE.PROCESS_STATUS_CODE<>'P') 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

View Text - HTML Formatted

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 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'
, 'FA'
, DR.TRANSFER_REJECTION_REASON
, 'R') EXCEPTION_REASON
, PA_EXCEPTION_REASONS_PUB.GET_EXCEPTION_TEXT('REV_EXCP'
, 'FA'
, DR.TRANSFER_REJECTION_REASON
, 'A') CORRECTIVE_ACTION
FROM PA_DRAFT_REVENUES DR
, PA_DRAFT_REVENUE_ITEMS DRI
, PA_IMPLEMENTATIONS IMP
, PA_PERIODS PRD
, XLA_EVENTS XE
WHERE IMP.INTERFACE_REVENUE_TO_GL_FLAG = 'Y'
AND DR.TRANSFER_STATUS_CODE ='A'
AND DR.EVENT_ID = XE.EVENT_ID
AND (XE.EVENT_STATUS_CODE<>'P' OR XE.PROCESS_STATUS_CODE<>'P')
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