View: PA_PROJ_REVENUE_VIEW

Product:	PA - Projects
Description:	Project draft revenues
Implementation/DBA Data:	APPS.PA_PROJ_REVENUE_VIEW

View Text

SELECT P.PROJECT_ID
, P.SEGMENT1
, DR.DRAFT_REVENUE_NUM
, DR.RELEASED_DATE
, DR.CREATION_DATE
, DR.GL_DATE
, DECODE(DR.GL_DATE
, NULL
, NULL
, GL_PER.PERIOD_NAME)
, DR.PA_DATE
, DR.TRANSFER_REJECTION_REASON
, PA_PER.PERIOD_NAME
, SUM(DRI.AMOUNT) AMOUNT
, DR.DRAFT_REVENUE_NUM_CREDITED
, DR.TRANSFER_STATUS_CODE
, LK.MEANING
FROM PA_PERIODS PA_PER
, PA_IMPLEMENTATIONS IMP
, GL_PERIOD_STATUSES GL_PER
, PA_LOOKUPS LK
, PA_DRAFT_REVENUE_ITEMS DRI
, PA_DRAFT_REVENUES_ALL DR
, PA_PROJECTS_ALL P
WHERE DRI.PROJECT_ID = DR.PROJECT_ID
AND DRI.DRAFT_REVENUE_NUM = DR.DRAFT_REVENUE_NUM
AND DR.PROJECT_ID = P.PROJECT_ID
AND IMP.ORG_ID = P.ORG_ID
AND GL_PER.SET_OF_BOOKS_ID = IMP.SET_OF_BOOKS_ID
AND GL_PER.APPLICATION_ID = PA_PERIOD_PROCESS_PKG.APPLICATION_ID
AND GL_PER.ADJUSTMENT_PERIOD_FLAG = 'N'
AND NVL(DR.GL_DATE
, TRUNC(SYSDATE)) BETWEEN GL_PER.START_DATE
AND GL_PER.END_DATE
AND DR.PA_DATE BETWEEN PA_PER.START_DATE
AND PA_PER.END_DATE
AND LK.LOOKUP_TYPE =DECODE(DRI.PROJECT_ID
, NULL
, 'INVOICE/REVENUE STATUS'
, 'INVOICE/REVENUE STATUS')
AND LK.LOOKUP_CODE = DECODE(DR.GENERATION_ERROR_FLAG
, 'Y'
, 'GENERATION ERROR'
, DECODE(DR.RELEASED_DATE
, NULL
, 'UNRELEASED'
, DECODE(DR.TRANSFER_STATUS_CODE
, 'P'
, 'RELEASED'
, 'X'
, 'REJECTED IN TRANSFER'
, 'T'
, 'TRANSFERRED'
, 'A'
, 'ACCEPTED'
, 'R'
, 'REJECTED' ) ) ) GROUP BY P.PROJECT_ID
, P.SEGMENT1
, DR.DRAFT_REVENUE_NUM
, DR.RELEASED_DATE
, DR.CREATION_DATE
, DR.GL_DATE
, DECODE(DR.GL_DATE
, NULL
, NULL
, GL_PER.PERIOD_NAME)
, DR.PA_DATE
, DR.TRANSFER_REJECTION_REASON
, PA_PER.PERIOD_NAME
, DR.DRAFT_REVENUE_NUM_CREDITED
, DR.TRANSFER_STATUS_CODE
, LK.MEANING

Columns