View: PA_PROJ_INVOICES_VIEW

Product:	PA - Projects
Description:	View of all project invoices
Implementation/DBA Data:	APPS.PA_PROJ_INVOICES_VIEW

View Text

SELECT INV.PROJECT_ID
, INV.DRAFT_INVOICE_NUM
, INV.RA_INVOICE_NUMBER
, INV.BILL_THROUGH_DATE
, 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)
, INV.INVOICE_COMMENT
, INV.TRANSFER_REJECTION_REASON
, INV.CUSTOMER_BILL_SPLIT
, CUST_ACCT.CUST_ACCOUNT_ID
, CUST_ACCT.ACCOUNT_NUMBER
, SUBSTRB(PARTY.PARTY_NAME
, 1
, 50)
, INV.AGREEMENT_ID
, AGR.AGREEMENT_NUM
, AGR.AGREEMENT_TYPE
, 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.INV_CURRENCY_CODE
, SUM(ITEM.INV_AMOUNT)
, SUM(DECODE(ITEM.INVOICE_LINE_TYPE
, 'RETENTION'
, ITEM.INV_AMOUNT
, 0))
, SUM(ITEM.INV_AMOUNT - DECODE(ITEM.INVOICE_LINE_TYPE
, 'RETENTION'
, ITEM.INV_AMOUNT
, 0))
, INV.DRAFT_INVOICE_NUM_CREDITED
, INV.TRANSFER_STATUS_CODE
, LK.MEANING
, TRM.NAME
, DECODE(INV.DRAFT_INVOICE_NUM_CREDITED
, NULL
, NULL
, DECODE(INV.CANCEL_CREDIT_MEMO_FLAG
, 'Y'
, 'CANCEL'
, DECODE(INV.WRITE_OFF_FLAG
, 'Y'
, 'WRITE-OFF'
, 'CREDIT MEMO')))
, TO_NUMBER(DECODE(INV.TRANSFER_STATUS_CODE
, 'P'
, DECODE(INV.RELEASED_DATE
, ''
, DECODE(INV.APPROVED_DATE
, ''
, TRUNC(SYSDATE)-TRUNC(INV.CREATION_DATE)
, TRUNC(SYSDATE)-TRUNC(INV.APPROVED_DATE))
, '')
, ''))
, INV.CC_PROJECT_ID
, INV.RETENTION_INVOICE_FLAG
, INV.CREDIT_MEMO_REASON_CODE
, INV.BILL_TO_CUSTOMER_ID
, INV.SHIP_TO_CUSTOMER_ID
, CUST_ACCT_B.ACCOUNT_NUMBER
, SUBSTRB(PARTY_B.PARTY_NAME
, 1
, 50)
, CUST_ACCT_C.ACCOUNT_NUMBER
, SUBSTRB(PARTY_C.PARTY_NAME
, 1
, 50)
, INV.BILL_TO_CONTACT_ID
, INV.SHIP_TO_CONTACT_ID
, SUM(ITEM.PROJFUNC_BILL_AMOUNT)
, AGR.CUSTOMER_ORDER_NUMBER
, AGR.LINE_OF_ACCOUNT
, INV.PAYMENT_SET_ID
FROM PA_LOOKUPS LK
, PA_IMPLEMENTATIONS_ALL IMP
, HZ_PARTIES PARTY
, HZ_CUST_ACCOUNTS_ALL CUST_ACCT
, HZ_PARTIES PARTY_B
, HZ_CUST_ACCOUNTS_ALL CUST_ACCT_B
, HZ_PARTIES PARTY_C
, HZ_CUST_ACCOUNTS_ALL CUST_ACCT_C
, PA_AGREEMENTS_ALL AGR
, RA_TERMS TRM
, GL_PERIOD_STATUSES GL_PER
, PA_DRAFT_INVOICE_ITEMS ITEM
, PA_DRAFT_INVOICES INV
WHERE ITEM.PROJECT_ID = INV.PROJECT_ID
AND ITEM.DRAFT_INVOICE_NUM = INV.DRAFT_INVOICE_NUM
AND INV.AGREEMENT_ID = AGR.AGREEMENT_ID
AND AGR.CUSTOMER_ID = CUST_ACCT.CUST_ACCOUNT_ID
AND CUST_ACCT_B.CUST_ACCOUNT_ID = INV.BILL_TO_CUSTOMER_ID
AND CUST_ACCT_C.CUST_ACCOUNT_ID = INV.SHIP_TO_CUSTOMER_ID
AND CUST_ACCT.PARTY_ID = PARTY.PARTY_ID
AND CUST_ACCT_B.PARTY_ID = PARTY_B.PARTY_ID
AND CUST_ACCT_C.PARTY_ID = PARTY_C.PARTY_ID
AND AGR.TERM_ID = TRM.TERM_ID
AND INV.CREATION_DATE BETWEEN NVL(TRM.START_DATE_ACTIVE
, INV.CREATION_DATE)
AND NVL(TRM.END_DATE_ACTIVE
, SYSDATE+1)
AND GL_PER.SET_OF_BOOKS_ID + 0 = 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 LK.LOOKUP_TYPE = 'INVOICE STATUS' AND IMP.ORG_ID = AGR.ORG_ID
AND LK.LOOKUP_CODE = DECODE(INV.GENERATION_ERROR_FLAG
, 'Y'
, 'GENERATION ERROR'
, DECODE(INV.APPROVED_DATE
, NULL
, 'UNAPPROVED'
, DECODE(INV.RELEASED_DATE
, NULL
, 'APPROVED'
, DECODE(INV.TRANSFER_STATUS_CODE
, 'P'
, 'RELEASED'
, 'X'
, 'REJECTED IN TRANSFER'
, 'T'
, 'TRANSFERRED'
, 'A'
, 'ACCEPTED'
, 'R'
, 'REJECTED' ) ) ) ) GROUP BY INV.PROJECT_ID
, INV.DRAFT_INVOICE_NUM
, INV.RA_INVOICE_NUMBER
, INV.BILL_THROUGH_DATE
, 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)
, INV.INVOICE_COMMENT
, INV.TRANSFER_REJECTION_REASON
, INV.CUSTOMER_BILL_SPLIT
, CUST_ACCT.CUST_ACCOUNT_ID
, CUST_ACCT.ACCOUNT_NUMBER
, SUBSTRB(PARTY.PARTY_NAME
, 1
, 50)
, INV.AGREEMENT_ID
, AGR.AGREEMENT_NUM
, AGR.AGREEMENT_TYPE
, INV.INV_CURRENCY_CODE
, INV.DRAFT_INVOICE_NUM_CREDITED
, INV.TRANSFER_STATUS_CODE
, LK.MEANING
, TRM.NAME
, DECODE(INV.DRAFT_INVOICE_NUM_CREDITED
, NULL
, NULL
, DECODE(INV.CANCEL_CREDIT_MEMO_FLAG
, 'Y'
, 'CANCEL'
, DECODE(INV.WRITE_OFF_FLAG
, 'Y'
, 'WRITE-OFF'
, 'CREDIT MEMO')))
, TO_NUMBER(SYSDATE - NVL(INV.APPROVED_DATE
, INV.CREATION_DATE))
, INV.CC_PROJECT_ID
, INV.RETENTION_INVOICE_FLAG
, INV.CREDIT_MEMO_REASON_CODE
, INV.BILL_TO_CUSTOMER_ID
, INV.SHIP_TO_CUSTOMER_ID
, CUST_ACCT_B.ACCOUNT_NUMBER
, SUBSTRB(PARTY_B.PARTY_NAME
, 1
, 50)
, CUST_ACCT_C.ACCOUNT_NUMBER
, SUBSTRB(PARTY_C.PARTY_NAME
, 1
, 50)
, INV.BILL_TO_CONTACT_ID
, INV.SHIP_TO_CONTACT_ID
, AGR.CUSTOMER_ORDER_NUMBER
, AGR.LINE_OF_ACCOUNT
, INV.PAYMENT_SET_ID

Columns