VIEW: APPS.PA_PROJECT_INVOICE_VIEW

Source

View Text - Preformatted

SELECT inv.project_id , inv.draft_invoice_num , inv.ra_invoice_number , inv.invoice_date , inv.approved_date , inv.released_date , inv.creation_date , inv.gl_date , decode(inv.gl_date,null,null,gl_per.period_name) , pa_per.period_name , sum(item.amount - decode(item.invoice_line_type,'RETENTION', item.amount,0)) , - sum(decode(item.invoice_line_type,'RETENTION',item.amount,0)) , sum(item.amount) , inv.draft_invoice_num_credited FROM pa_implementations imp, pa_periods pa_per, gl_period_statuses gl_per, pa_draft_invoice_items item, pa_draft_invoices_all inv WHERE inv.released_date is not null AND item.project_id = inv.project_id AND item.draft_invoice_num = inv.draft_invoice_num AND imp.org_id = inv.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(inv.gl_date,trunc(sysdate)) between gl_per.start_date and gl_per.end_date AND inv.pa_date between pa_per.start_date and pa_per.end_date GROUP BY inv.project_id, inv.draft_invoice_num, inv.ra_invoice_number , inv.invoice_date, inv.approved_date, inv.released_date , inv.creation_date, inv.gl_date, gl_per.period_name , pa_per.period_name, inv.draft_invoice_num_credited

View Text - HTML Formatted

SELECT INV.PROJECT_ID
, INV.DRAFT_INVOICE_NUM
, INV.RA_INVOICE_NUMBER
, INV.INVOICE_DATE
, INV.APPROVED_DATE
, INV.RELEASED_DATE
, INV.CREATION_DATE
, INV.GL_DATE
, DECODE(INV.GL_DATE
, NULL
, NULL
, GL_PER.PERIOD_NAME)
, PA_PER.PERIOD_NAME
, SUM(ITEM.AMOUNT - DECODE(ITEM.INVOICE_LINE_TYPE
, 'RETENTION'
, ITEM.AMOUNT
, 0))
, - SUM(DECODE(ITEM.INVOICE_LINE_TYPE
, 'RETENTION'
, ITEM.AMOUNT
, 0))
, SUM(ITEM.AMOUNT)
, INV.DRAFT_INVOICE_NUM_CREDITED
FROM PA_IMPLEMENTATIONS IMP
, PA_PERIODS PA_PER
, GL_PERIOD_STATUSES GL_PER
, PA_DRAFT_INVOICE_ITEMS ITEM
, PA_DRAFT_INVOICES_ALL INV
WHERE INV.RELEASED_DATE IS NOT NULL
AND ITEM.PROJECT_ID = INV.PROJECT_ID
AND ITEM.DRAFT_INVOICE_NUM = INV.DRAFT_INVOICE_NUM
AND IMP.ORG_ID = INV.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(INV.GL_DATE
, TRUNC(SYSDATE)) BETWEEN GL_PER.START_DATE
AND GL_PER.END_DATE
AND INV.PA_DATE BETWEEN PA_PER.START_DATE
AND PA_PER.END_DATE GROUP BY INV.PROJECT_ID
, INV.DRAFT_INVOICE_NUM
, INV.RA_INVOICE_NUMBER
, INV.INVOICE_DATE
, INV.APPROVED_DATE
, INV.RELEASED_DATE
, INV.CREATION_DATE
, INV.GL_DATE
, GL_PER.PERIOD_NAME
, PA_PER.PERIOD_NAME
, INV.DRAFT_INVOICE_NUM_CREDITED