VIEW: APPS.OKL_BPD_AP_INVOICE_UV

Source

View Text - Preformatted

SELECT INV.invoice_id ,INV.invoice_num ,INV.invoice_date ,INV.description ,sum(tpl.amount) khr_line_amount ,INV.invoice_amount ,INV.payment_status_flag ,INV.invoice_currency_code ,INV.invoice_type_lookup_code ,INV.set_of_books_id ,AP_INVOICES_PKG.get_approval_status(INV.invoice_id, INV.invoice_amount, INV.payment_status_flag, INV.invoice_type_lookup_code) transaction_status ,TPL.khr_id ,KHR.contract_number ,TAP.try_id ,KHR1.pdt_id ,KHR.authoring_org_id org_id ,INV.vendor_id ,INV.reference_key1 cnsld_ap_inv_id FROM OKL_CNSLD_AP_INVS_ALL CIN,OKL_TXL_AP_INV_LNS_B TPL,AP_INVOICES_ALL INV,OKC_K_HEADERS_B KHR,OKL_K_HEADERS KHR1,FND_APPLICATION APP,OKL_TRX_AP_INVOICES_B TAP WHERE inv.application_id = APP.application_id AND APP.application_short_name = 'OKL' AND INV.product_table = 'OKL_CNSLD_AP_INVS_ALL' AND cin.cnsld_ap_inv_id = TO_NUMBER(INV.reference_key1) AND TPL.CNSLD_AP_INV_ID = cin.cnsld_ap_inv_id AND TPL.khr_id = KHR.id AND KHR.id = KHR1.id AND TPL.TAP_ID = TAP.id AND tap.vendor_id = inv.vendor_id and tap.ipvs_id(+) = inv.vendor_site_id group by INV.invoice_id , INV.invoice_num , INV.invoice_date , INV.description , INV.invoice_amount , INV.payment_status_flag , INV.invoice_currency_code , INV.invoice_type_lookup_code , INV.set_of_books_id , AP_INVOICES_PKG.get_approval_status(INV.invoice_id, INV.invoice_amount, INV.payment_status_flag, INV.invoice_type_lookup_code) , TPL.khr_id , KHR.contract_number , TAP.try_id , KHR1.pdt_id , KHR.authoring_org_id , INV.vendor_id , INV.reference_key1

View Text - HTML Formatted

SELECT INV.INVOICE_ID
, INV.INVOICE_NUM
, INV.INVOICE_DATE
, INV.DESCRIPTION
, SUM(TPL.AMOUNT) KHR_LINE_AMOUNT
, INV.INVOICE_AMOUNT
, INV.PAYMENT_STATUS_FLAG
, INV.INVOICE_CURRENCY_CODE
, INV.INVOICE_TYPE_LOOKUP_CODE
, INV.SET_OF_BOOKS_ID
, AP_INVOICES_PKG.GET_APPROVAL_STATUS(INV.INVOICE_ID
, INV.INVOICE_AMOUNT
, INV.PAYMENT_STATUS_FLAG
, INV.INVOICE_TYPE_LOOKUP_CODE) TRANSACTION_STATUS
, TPL.KHR_ID
, KHR.CONTRACT_NUMBER
, TAP.TRY_ID
, KHR1.PDT_ID
, KHR.AUTHORING_ORG_ID ORG_ID
, INV.VENDOR_ID
, INV.REFERENCE_KEY1 CNSLD_AP_INV_ID
FROM OKL_CNSLD_AP_INVS_ALL CIN
, OKL_TXL_AP_INV_LNS_B TPL
, AP_INVOICES_ALL INV
, OKC_K_HEADERS_B KHR
, OKL_K_HEADERS KHR1
, FND_APPLICATION APP
, OKL_TRX_AP_INVOICES_B TAP
WHERE INV.APPLICATION_ID = APP.APPLICATION_ID
AND APP.APPLICATION_SHORT_NAME = 'OKL'
AND INV.PRODUCT_TABLE = 'OKL_CNSLD_AP_INVS_ALL'
AND CIN.CNSLD_AP_INV_ID = TO_NUMBER(INV.REFERENCE_KEY1)
AND TPL.CNSLD_AP_INV_ID = CIN.CNSLD_AP_INV_ID
AND TPL.KHR_ID = KHR.ID
AND KHR.ID = KHR1.ID
AND TPL.TAP_ID = TAP.ID
AND TAP.VENDOR_ID = INV.VENDOR_ID
AND TAP.IPVS_ID(+) = INV.VENDOR_SITE_ID GROUP BY INV.INVOICE_ID
, INV.INVOICE_NUM
, INV.INVOICE_DATE
, INV.DESCRIPTION
, INV.INVOICE_AMOUNT
, INV.PAYMENT_STATUS_FLAG
, INV.INVOICE_CURRENCY_CODE
, INV.INVOICE_TYPE_LOOKUP_CODE
, INV.SET_OF_BOOKS_ID
, AP_INVOICES_PKG.GET_APPROVAL_STATUS(INV.INVOICE_ID
, INV.INVOICE_AMOUNT
, INV.PAYMENT_STATUS_FLAG
, INV.INVOICE_TYPE_LOOKUP_CODE)
, TPL.KHR_ID
, KHR.CONTRACT_NUMBER
, TAP.TRY_ID
, KHR1.PDT_ID
, KHR.AUTHORING_ORG_ID
, INV.VENDOR_ID
, INV.REFERENCE_KEY1