View: PA_PROJECT_INVOICE_VIEW

Product:	PA - Projects
Description:	is a view of released invoices for a project that can be used for management reporting and online queries.
Implementation/DBA Data:	APPS.PA_PROJECT_INVOICE_VIEW

View Text

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

Columns