VIEW: APPS.PA_PROJ_REVENUE_VIEW

Source

View Text - Preformatted

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

View Text - HTML Formatted

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