VIEW: APPS.OKL_BPD_AP_INV_LINES_UV

Source

View Text - Preformatted

SELECT INV.invoice_num ,INV.invoice_id ,LIN.line_number ,LIN.description ,LIN.amount + nvl(( select sum(zx_lines.tax_amt) from zx_lines zx_lines where zx_lines.application_id = LIN.program_application_id and zx_lines.TRX_ID = lin.invoice_id and zx_lines.TRX_LINE_ID = lin.line_number and zx_lines.ENTITY_CODE = 'AP_INVOICES' and nvl(zx_lines.cancel_flag, 'N') = 'N' ), 0) amount ,INV.set_of_books_id ,STY.stream_type_purpose ,STY.name ,TPL.khr_id ,KHR.contract_number FROM OKL_CNSLD_AP_INVS_ALL CIN,OKL_TRX_AP_INVS_ALL_B TAP,OKL_TXL_AP_INV_LNS_B TPL,AP_INVOICES_ALL INV,AP_INVOICE_LINES_ALL LIN,OKC_K_HEADERS_B KHR,FND_APPLICATION APP,OKL_STRM_TYPE_V STY WHERE LIN.application_id = APP.application_id AND APP.application_short_name = 'OKL' AND LIN.product_table = 'OKL_TXL_AP_INV_LNS_ALL_B' AND TPL.id = TO_NUMBER(LIN.reference_key1) AND LIN.invoice_id = INV.invoice_id AND TPL.khr_id = KHR.id AND TPL.sty_id = STY.id and tap.vendor_id = inv.vendor_id and tap.ipvs_id (+) = inv.vendor_site_id UNION SELECT INV.invoice_num , INV.invoice_id , LIN.amount + nvl(( select sum(zx_lines.tax_amt) from zx_lines zx_lines where zx_lines.application_id = LIN.program_application_id and zx_lines.TRX_ID = lin.invoice_id and zx_lines.TRX_LINE_ID = lin.line_number and zx_lines.ENTITY_CODE = 'AP_INVOICES' and nvl(zx_lines.cancel_flag, 'N') = 'N' ),0) amount, LIN.description , LIN.amount , INV.set_of_books_id , STY.stream_type_purpose , STY.name , TPL.khr_id , KHR.contract_number FROM OKL_TRX_AP_INVS_ALL_B TAP, OKL_TXL_AP_INV_LNS_B TPL, AP_INVOICES_ALL INV, AP_INVOICE_LINES_ALL LIN, OKC_K_HEADERS_B KHR, FND_APPLICATION APP, OKL_STRM_TYPE_V STY, okl_cnsld_ap_invs_all cin 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 LIN.invoice_id = INV.invoice_id AND LIN.application_id = INV.application_id AND LIN.product_table = 'OKL_TXL_AP_INV_LNS_ALL_B' AND LIN.reference_key1 IS NULL AND TPL.khr_id = KHR.id AND TPL.sty_id = STY.id AND TAP.id = TPL.tap_id and tap.vendor_id = inv.vendor_id and tap.ipvs_id (+) = inv.vendor_site_id

View Text - HTML Formatted

SELECT INV.INVOICE_NUM
, INV.INVOICE_ID
, LIN.LINE_NUMBER
, LIN.DESCRIPTION
, LIN.AMOUNT + NVL(( SELECT SUM(ZX_LINES.TAX_AMT)
FROM ZX_LINES ZX_LINES
WHERE ZX_LINES.APPLICATION_ID = LIN.PROGRAM_APPLICATION_ID
AND ZX_LINES.TRX_ID = LIN.INVOICE_ID
AND ZX_LINES.TRX_LINE_ID = LIN.LINE_NUMBER
AND ZX_LINES.ENTITY_CODE = 'AP_INVOICES'
AND NVL(ZX_LINES.CANCEL_FLAG
, 'N') = 'N' )
, 0) AMOUNT
, INV.SET_OF_BOOKS_ID
, STY.STREAM_TYPE_PURPOSE
, STY.NAME
, TPL.KHR_ID
, KHR.CONTRACT_NUMBER
FROM OKL_CNSLD_AP_INVS_ALL CIN
, OKL_TRX_AP_INVS_ALL_B TAP
, OKL_TXL_AP_INV_LNS_B TPL
, AP_INVOICES_ALL INV
, AP_INVOICE_LINES_ALL LIN
, OKC_K_HEADERS_B KHR
, FND_APPLICATION APP
, OKL_STRM_TYPE_V STY
WHERE LIN.APPLICATION_ID = APP.APPLICATION_ID
AND APP.APPLICATION_SHORT_NAME = 'OKL'
AND LIN.PRODUCT_TABLE = 'OKL_TXL_AP_INV_LNS_ALL_B'
AND TPL.ID = TO_NUMBER(LIN.REFERENCE_KEY1)
AND LIN.INVOICE_ID = INV.INVOICE_ID
AND TPL.KHR_ID = KHR.ID
AND TPL.STY_ID = STY.ID
AND TAP.VENDOR_ID = INV.VENDOR_ID
AND TAP.IPVS_ID (+) = INV.VENDOR_SITE_ID UNION SELECT INV.INVOICE_NUM
, INV.INVOICE_ID
, LIN.AMOUNT + NVL(( SELECT SUM(ZX_LINES.TAX_AMT)
FROM ZX_LINES ZX_LINES
WHERE ZX_LINES.APPLICATION_ID = LIN.PROGRAM_APPLICATION_ID
AND ZX_LINES.TRX_ID = LIN.INVOICE_ID
AND ZX_LINES.TRX_LINE_ID = LIN.LINE_NUMBER
AND ZX_LINES.ENTITY_CODE = 'AP_INVOICES'
AND NVL(ZX_LINES.CANCEL_FLAG
, 'N') = 'N' )
, 0) AMOUNT
, LIN.DESCRIPTION
, LIN.AMOUNT
, INV.SET_OF_BOOKS_ID
, STY.STREAM_TYPE_PURPOSE
, STY.NAME
, TPL.KHR_ID
, KHR.CONTRACT_NUMBER
FROM OKL_TRX_AP_INVS_ALL_B TAP
, OKL_TXL_AP_INV_LNS_B TPL
, AP_INVOICES_ALL INV
, AP_INVOICE_LINES_ALL LIN
, OKC_K_HEADERS_B KHR
, FND_APPLICATION APP
, OKL_STRM_TYPE_V STY
, OKL_CNSLD_AP_INVS_ALL CIN
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 LIN.INVOICE_ID = INV.INVOICE_ID
AND LIN.APPLICATION_ID = INV.APPLICATION_ID
AND LIN.PRODUCT_TABLE = 'OKL_TXL_AP_INV_LNS_ALL_B'
AND LIN.REFERENCE_KEY1 IS NULL
AND TPL.KHR_ID = KHR.ID
AND TPL.STY_ID = STY.ID
AND TAP.ID = TPL.TAP_ID
AND TAP.VENDOR_ID = INV.VENDOR_ID
AND TAP.IPVS_ID (+) = INV.VENDOR_SITE_ID