View: OKL_CS_DISBURSEMENTS_UV

Product:	OKL - Lease and Finance Management
Description:
Implementation/DBA Data:	APPS.OKL_CS_DISBURSEMENTS_UV

View Text

SELECT CHR.CONTRACT_NUMBER CONTRACT_NUMBER
, FAA.ASSET_NUMBER ASSET_NUMBER
, POV.VENDOR_NAME VENDOR_NAME
, STYT.NAME TRANSACTION_TYPE
, CIN.VENDOR_INVOICE_NUMBER VENDOR_INVOICE_NUMBER
, CIN.DATE_INVOICED INVOICE_DATE
, TPL.AMOUNT DISBURSEMENT_AMOUNT
, API.AMOUNT_PAID AMOUNT_PAID
, APC.PAYMENT_METHOD_LOOKUP_CODE PAYMENT_METHOD
, APC.CHECK_NUMBER CHECK_NUMBER
, APC.CHECK_DATE CHECK_DATE
, APC.AMOUNT CHECK_AMOUNT
, APC.CURRENCY_CODE CHECK_CURRENCY
, CIN.CNSLD_AP_INV_ID CNSLD_INV_ID
, TPL.KHR_ID KHR_ID
, FAA.ASSET_ID ASSET_ID
, STYT.ID STY_ID
, POV.VENDOR_ID VENDOR_ID
, APC.CHECK_ID CHECK_ID
, CIN.ORG_ID ORG_ID
, CIN.CURRENCY_CODE
, FND_TYPE.MEANING INVOICE_TYPE_MEANING
FROM OKL_CNSLD_AP_INVS_ALL CIN
, OKL_TXL_AP_INV_LNS_ALL_B TPL
, OKL_STRM_TYPE_TL STYT
, OKC_K_HEADERS_B CHR
, OKC_K_LINES_B CLE
, OKC_LINE_STYLES_B LSE
, OKC_K_ITEMS CIM
, FA_ADDITIONS_B FAA
, AP_INVOICES_ALL API
, PO_VENDORS POV
, AP_INVOICE_PAYMENTS_ALL APP
, AP_CHECKS_ALL APC
, FND_APPLICATION FND
, FND_LOOKUPS FND_TYPE
WHERE CIN.CNSLD_AP_INV_ID = TPL.CNSLD_AP_INV_ID
AND TPL.KLE_ID IS NOT NULL
AND TPL.KLE_ID = CLE.CLE_ID
AND CLE.LSE_ID = LSE.ID
AND LSE.LTY_CODE = 'FIXED_ASSET'
AND CLE.ID = CIM.CLE_ID
AND CIM.JTOT_OBJECT1_CODE = 'OKX_ASSET'
AND CIM.OBJECT1_ID1 = FAA.ASSET_ID
AND TPL.STY_ID = STYT.ID(+)
AND STYT.LANGUAGE(+) = USERENV('LANG')
AND TPL.KHR_ID = CHR.ID
AND NVL(CIN.ORG_ID
, -99) = NVL(CIN.ORG_ID
, -99)
AND API.INVOICE_NUM = CIN.VENDOR_INVOICE_NUMBER
AND API.VENDOR_ID = POV.VENDOR_ID
AND API.INVOICE_ID = APP.INVOICE_ID(+)
AND APP.CHECK_ID = APC.CHECK_ID(+)
AND CIN.CNSLD_AP_INV_ID = TO_NUMBER(API.REFERENCE_KEY1)
AND API.APPLICATION_ID = FND.APPLICATION_ID
AND FND.APPLICATION_SHORT_NAME = 'OKL'
AND API.PRODUCT_TABLE = 'OKL_CNSLD_AP_INVS_ALL'
AND FND_TYPE.LOOKUP_TYPE(+) = 'OKL_PAYABLES_INVOICE_TYPE'
AND FND_TYPE.LOOKUP_CODE(+) = CIN.INVOICE_TYPE UNION SELECT CHR.CONTRACT_NUMBER CONTRACT_NUMBER
, NULL ASSET_NUMBER
, POV.VENDOR_NAME VENDOR_NAME
, STYT.NAME TRANSACTION_TYPE
, CIN.VENDOR_INVOICE_NUMBER VENDOR_INVOICE_NUMBER
, CIN.DATE_INVOICED INVOICE_DATE
, TPL.AMOUNT DISBURSEMENT_AMOUNT
, API.AMOUNT_PAID
, APC.PAYMENT_METHOD_LOOKUP_CODE PAYMENT_METHOD
, APC.CHECK_NUMBER CHECK_NUMBER
, APC.CHECK_DATE CHECK_DATE
, APC.AMOUNT CHECK_AMOUNT
, APC.CURRENCY_CODE CHECK_CURRENCY
, CIN.CNSLD_AP_INV_ID CNSLD_INV_ID
, TPL.KHR_ID KHR_ID
, TO_NUMBER(NULL) ASSET_ID
, STYT.ID STY_ID
, POV.VENDOR_ID VENDOR_ID
, APC.CHECK_ID CHECK_ID
, CIN.ORG_ID ORG_ID
, CIN.CURRENCY_CODE
, FND_TYPE.MEANING INVOICE_TYPE_MEANING
FROM OKL_CNSLD_AP_INVS_ALL CIN
, OKL_TXL_AP_INV_LNS_ALL_B TPL
, OKL_STRM_TYPE_TL STYT
, OKC_K_HEADERS_B CHR
, AP_INVOICES_ALL API
, PO_VENDORS POV
, AP_INVOICE_PAYMENTS_ALL APP
, AP_CHECKS_ALL APC
, FND_APPLICATION FND
, FND_LOOKUPS FND_TYPE
WHERE CIN.CNSLD_AP_INV_ID = TPL.CNSLD_AP_INV_ID
AND TPL.KLE_ID IS NULL
AND TPL.STY_ID = STYT.ID(+)
AND STYT.LANGUAGE(+) = USERENV('LANG')
AND TPL.KHR_ID = CHR.ID
AND NVL(CIN.ORG_ID
, -99) = NVL(API.ORG_ID
, -99)
AND API.INVOICE_NUM = CIN.VENDOR_INVOICE_NUMBER
AND API.VENDOR_ID = POV.VENDOR_ID
AND API.INVOICE_ID = APP.INVOICE_ID(+)
AND APP.CHECK_ID = APC.CHECK_ID(+)
AND CIN.CNSLD_AP_INV_ID = TO_NUMBER(API.REFERENCE_KEY1)
AND API.APPLICATION_ID = FND.APPLICATION_ID
AND FND.APPLICATION_SHORT_NAME = 'OKL'
AND API.PRODUCT_TABLE = 'OKL_CNSLD_AP_INVS_ALL'
AND FND_TYPE.LOOKUP_TYPE(+) = 'OKL_PAYABLES_INVOICE_TYPE'
AND FND_TYPE.LOOKUP_CODE(+) = CIN.INVOICE_TYPE UNION SELECT CHR.CONTRACT_NUMBER CONTRACT_NUMBER
, CLT.NAME ASSET_NUMBER
, POV.VENDOR_NAME VENDOR_NAME
, STYT.NAME TRANSACTION_TYPE
, CIN.VENDOR_INVOICE_NUMBER VENDOR_INVOICE_NUMBER
, CIN.DATE_INVOICED INVOICE_DATE
, TPL.AMOUNT DISBURSEMENT_AMOUNT
, API.AMOUNT_PAID
, APC.PAYMENT_METHOD_LOOKUP_CODE PAYMENT_METHOD
, APC.CHECK_NUMBER CHECK_NUMBER
, APC.CHECK_DATE CHECK_DATE
, APC.AMOUNT CHECK_AMOUNT
, APC.CURRENCY_CODE CHECK_CURRENCY
, CIN.CNSLD_AP_INV_ID CNSLD_INV_ID
, TPL.KHR_ID KHR_ID
, TO_NUMBER(NULL) ASSET_ID
, STYT.ID STY_ID
, POV.VENDOR_ID VENDOR_ID
, APC.CHECK_ID CHECK_ID
, CIN.ORG_ID ORG_ID
, CIN.CURRENCY_CODE
, FND_TYPE.MEANING INVOICE_TYPE_MEANING
FROM OKL_CNSLD_AP_INVS_ALL CIN
, OKL_TXL_AP_INV_LNS_ALL_B TPL
, OKL_STRM_TYPE_TL STYT
, OKC_K_HEADERS_B CHR
, OKL_K_HEADERS CHL
, OKC_K_LINES_B CLE
, OKC_LINE_STYLES_B LSE
, OKC_K_LINES_TL CLT
, AP_INVOICES_ALL API
, PO_VENDORS POV
, AP_INVOICE_PAYMENTS_ALL APP
, AP_CHECKS_ALL APC
, FND_APPLICATION FND
, FND_LOOKUPS FND_TYPE
WHERE CIN.CNSLD_AP_INV_ID = TPL.CNSLD_AP_INV_ID
AND TPL.KLE_ID IS NOT NULL
AND TPL.KLE_ID = CLE.ID
AND TPL.STY_ID = STYT.ID(+)
AND STYT.LANGUAGE(+) = USERENV('LANG')
AND TPL.KHR_ID = CHR.ID
AND CHR.ID = CHL.ID
AND CHL.DEAL_TYPE = 'LOAN'
AND CLE.LSE_ID = LSE.ID
AND LSE.LTY_CODE = 'FREE_FORM1'
AND CLE.ID = CLT.ID
AND CLT.LANGUAGE(+) = USERENV('LANG')
AND NVL(CIN.ORG_ID
, -99) = NVL(API.ORG_ID
, -99)
AND API.INVOICE_NUM = CIN.VENDOR_INVOICE_NUMBER
AND API.VENDOR_ID = POV.VENDOR_ID
AND API.INVOICE_ID = APP.INVOICE_ID(+)
AND APP.CHECK_ID = APC.CHECK_ID(+)
AND CIN.CNSLD_AP_INV_ID = TO_NUMBER(API.REFERENCE_KEY1)
AND API.APPLICATION_ID = FND.APPLICATION_ID
AND FND.APPLICATION_SHORT_NAME = 'OKL'
AND API.PRODUCT_TABLE = 'OKL_CNSLD_AP_INVS_ALL'
AND FND_TYPE.LOOKUP_TYPE(+) = 'OKL_PAYABLES_INVOICE_TYPE'
AND FND_TYPE.LOOKUP_CODE(+) = CIN.INVOICE_TYPE UNION SELECT CHR.CONTRACT_NUMBER CONTRACT_NUMBER
, CLE.NAME ASSET_NUMBER
, POV.VENDOR_NAME VENDOR_NAME
, STYT.NAME TRANSACTION_TYPE
, CIN.VENDOR_INVOICE_NUMBER VENDOR_INVOICE_NUMBER
, CIN.DATE_INVOICED INVOICE_DATE
, TPL.AMOUNT DISBURSEMENT_AMOUNT
, API.AMOUNT_PAID
, APC.PAYMENT_METHOD_LOOKUP_CODE PAYMENT_METHOD
, APC.CHECK_NUMBER CHECK_NUMBER
, APC.CHECK_DATE CHECK_DATE
, APC.AMOUNT CHECK_AMOUNT
, APC.CURRENCY_CODE CHECK_CURRENCY
, CIN.CNSLD_AP_INV_ID CNSLD_INV_ID
, TPL.KHR_ID KHR_ID
, TO_NUMBER(NULL) ASSET_ID
, STYT.ID STY_ID
, POV.VENDOR_ID VENDOR_ID
, APC.CHECK_ID CHECK_ID
, CIN.ORG_ID ORG_ID
, CIN.CURRENCY_CODE
, FND_TYPE.MEANING INVOICE_TYPE_MEANING
FROM OKL_CNSLD_AP_INVS_ALL CIN
, OKL_TXL_AP_INV_LNS_ALL_B TPL
, OKL_STRM_TYPE_TL STYT
, OKC_K_HEADERS_B CHR
, OKC_K_LINES_V CLE
, AP_INVOICES_ALL API
, PO_VENDORS POV
, AP_INVOICE_PAYMENTS_ALL APP
, AP_CHECKS_ALL APC
, FND_APPLICATION FND
, FND_LOOKUPS FND_TYPE
WHERE CIN.CNSLD_AP_INV_ID = TPL.CNSLD_AP_INV_ID
AND TPL.KLE_ID IS NOT NULL
AND TPL.STY_ID = STYT.ID(+)
AND STYT.LANGUAGE(+) = USERENV('LANG')
AND TPL.KHR_ID = CHR.ID
AND NVL(CIN.ORG_ID
, -99) = NVL(API.ORG_ID
, -99)
AND CIN.VENDOR_INVOICE_NUMBER = API.INVOICE_NUM
AND API.VENDOR_ID = POV.VENDOR_ID
AND API.INVOICE_ID = APP.INVOICE_ID(+)
AND APP.CHECK_ID = APC.CHECK_ID(+)
AND NOT EXISTS (SELECT 1
FROM OKC_K_LINES_B CLE2
, OKC_LINE_STYLES_B LSE
WHERE CLE2.CLE_ID = TPL.KLE_ID
AND CLE2.LSE_ID = LSE.ID
AND LSE.LTY_CODE = 'FIXED_ASSET')
AND TPL.KLE_ID = CLE.ID
AND CIN.CNSLD_AP_INV_ID = TO_NUMBER(API.REFERENCE_KEY1)
AND API.APPLICATION_ID = FND.APPLICATION_ID
AND FND.APPLICATION_SHORT_NAME = 'OKL'
AND API.PRODUCT_TABLE = 'OKL_CNSLD_AP_INVS_ALL'
AND FND_TYPE.LOOKUP_TYPE(+) = 'OKL_PAYABLES_INVOICE_TYPE'
AND FND_TYPE.LOOKUP_CODE(+) = CIN.INVOICE_TYPE UNION SELECT CHR.CONTRACT_NUMBER CONTRACT_NUMBER
, CLT.NAME ASSET_NUMBER
, POV.VENDOR_NAME VENDOR_NAME
, NULL TRANSACTION_TYPE
, CIN.VENDOR_INVOICE_NUMBER VENDOR_INVOICE_NUMBER
, CIN.DATE_INVOICED INVOICE_DATE
, TPL.AMOUNT DISBURSEMENT_AMOUNT
, API.AMOUNT_PAID
, APC.PAYMENT_METHOD_LOOKUP_CODE PAYMENT_METHOD
, APC.CHECK_NUMBER CHECK_NUMBER
, APC.CHECK_DATE CHECK_DATE
, APC.AMOUNT CHECK_AMOUNT
, APC.CURRENCY_CODE CHECK_CURRENCY
, CIN.CNSLD_AP_INV_ID CNSLD_INV_ID
, TPL.KHR_ID KHR_ID
, TO_NUMBER(NULL) ASSET_ID
, TO_NUMBER(NULL) STY_ID
, POV.VENDOR_ID VENDOR_ID
, APC.CHECK_ID CHECK_ID
, CIN.ORG_ID ORG_ID
, CIN.CURRENCY_CODE
, FND_TYPE.MEANING INVOICE_TYPE_MEANING
FROM OKL_CNSLD_AP_INVS_ALL CIN
, OKL_TXL_AP_INV_LNS_ALL_B TPL
, OKC_K_HEADERS_B CHR
, OKC_K_LINES_B CLE
, OKC_LINE_STYLES_B LSE
, OKC_K_LINES_TL CLT
, AP_INVOICES_ALL API
, PO_VENDORS POV
, AP_INVOICE_PAYMENTS_ALL APP
, AP_CHECKS_ALL APC
, FND_APPLICATION FND
, FND_LOOKUPS FND_TYPE
WHERE CIN.CNSLD_AP_INV_ID = TPL.CNSLD_AP_INV_ID
AND TPL.KLE_ID IS NOT NULL
AND TPL.FUNDING_REFERENCE_TYPE_CODE = 'ASSET'
AND TPL.KHR_ID = CHR.ID
AND TPL.KLE_ID = CLE.ID
AND CLE.LSE_ID = LSE.ID
AND LSE.LTY_CODE = 'FREE_FORM1'
AND CLE.ID = CLT.ID
AND CLT.LANGUAGE = USERENV('LANG')
AND NVL(CIN.ORG_ID
, -99) = NVL(API.ORG_ID
, -99)
AND CIN.VENDOR_INVOICE_NUMBER = API.INVOICE_NUM
AND API.VENDOR_ID = POV.VENDOR_ID
AND API.INVOICE_ID = APP.INVOICE_ID(+)
AND APP.CHECK_ID = APC.CHECK_ID(+)
AND NOT EXISTS (SELECT 1
FROM OKC_K_LINES_B CLE2
, OKC_LINE_STYLES_B LSE2
, OKC_K_ITEMS CIM
, FA_ADDITIONS_B FAA
WHERE TPL.KLE_ID = CLE2.CLE_ID
AND CLE2.LSE_ID = LSE2.ID
AND LSE2.LTY_CODE = 'FIXED_ASSET'
AND CLE2.ID = CIM.CLE_ID
AND CIM.JTOT_OBJECT1_CODE = 'OKX_ASSET'
AND CIM.OBJECT1_ID1 = FAA.ASSET_ID)
AND CIN.CNSLD_AP_INV_ID = TO_NUMBER(API.REFERENCE_KEY1)
AND API.APPLICATION_ID = FND.APPLICATION_ID
AND FND.APPLICATION_SHORT_NAME = 'OKL'
AND API.PRODUCT_TABLE = 'OKL_CNSLD_AP_INVS_ALL'
AND FND_TYPE.LOOKUP_TYPE(+) = 'OKL_PAYABLES_INVOICE_TYPE'
AND FND_TYPE.LOOKUP_CODE(+) = CIN.INVOICE_TYPE

Columns