eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.PA_GL_DR_EXCEPT_SUM_V

Source

View Text - Preformatted


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

View Text - HTML Formatted

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