VIEW: APPS.PA_PROJ_INVOICES_VIEW

Source

View Text - Preformatted

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

View Text - HTML Formatted

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