View: OKL_CS_BILLINGTRX_UV

Product:	OKL - Lease and Finance Management
Description:	Displays transaction-level billing information
Implementation/DBA Data:	APPS.OKL_CS_BILLINGTRX_UV

View Text

SELECT DTLS.LSM_ID LSM_ID
, DTLS.CONTRACT_NUMBER CONTRACT_NUMBER
, STYT.NAME TRANSACTION_TYPE
, DTLS.INVOICE_NUMBER INVOICE_NUMBER
, DTLS.INVOICE_AMOUNT INVOICE_AMOUNT
, DTLS.LINE_IDENTIFIER LINE_IDENTIFIER
, FAA.ASSET_NUMBER ASSET_NUMBER
, DTLS.INVOICE_DATE INVOICE_DATE
, DTLS.DUE_DATE DUE_DATE
, DTLS.AMOUNT_ORIGINAL TRANSACTION_AMOUNT
, DTLS.AMOUNT_APPLIED AMOUNT_APPLIED
, DTLS.AMOUNT_REMAINING AMOUNT_REMAINING
, DTLS.CUSTOMER_ACCT_ID CUSTOMER_ACCOUNT_ID
, DTLS.ORG_ID CONTRACT_AUTHORING_ORG_ID
, DTLS.CHR_ID KHR_ID
, DTLS.KLE_ID KLE_ID
, FAA.ASSET_ID ASSET_ID
, DTLS.INVOICE_ID INVOICE_ID
, STYT.ID STY_ID
, DTLS.INVOICE_CURRENCY_CODE CURRENCY_CODE
, APS.EXCHANGE_RATE_TYPE AR_EXCHANGE_RATE_TYPE
, APS.EXCHANGE_RATE AR_EXCHANGE_RATE
, APS.EXCHANGE_DATE AR_EXCHANGE_DATE
, APS.TRX_DATE AR_TRX_DATE
, DTLS.LINE_AMOUNT AMOUNT_LINE_ITEMS_ORIGINAL
, DTLS.AMOUNT_REMAINING AMOUNT_LINE_ITEMS_REMAINING
, DTLS.TAX_AMOUNT TAX_ORIGINAL
, DTLS.TAX_REMAINING TAX_REMAINING
, DTLS.AMOUNT_CREDITED AMOUNT_CREDITED
, DTLS.AR_INVOICE_ID CUSTOMER_TRX_ID
, DTLS.INVOICE_LINE_ID INVOICE_LINE_ID
, DTLS.PAYMENT_TERM PAYMENT_TERM
, DTLS.INVOICE_TYPE INVOICE_TYPE
FROM OKL_CS_BPD_INV_DTL_V DTLS
, AR_PAYMENT_SCHEDULES_ALL APS
, OKL_STRM_TYPE_TL STYT
, FA_ADDITIONS_B FAA
, OKC_K_ITEMS CIM
, OKC_LINE_STYLES_B LSE
, OKC_K_LINES_B CLE
, OKL_K_HEADERS CHL
WHERE 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 DTLS.CHR_ID = CHL.ID
AND CHL.DEAL_TYPE <> 'LOAN'
AND DTLS.KLE_ID IS NOT NULL
AND DTLS.AR_INVOICE_ID = APS.CUSTOMER_TRX_ID
AND DTLS.STY_ID = STYT.ID
AND STYT.LANGUAGE = USERENV('LANG')
AND DTLS.KLE_ID = CLE.CLE_ID UNION ALL SELECT DTLS.LSM_ID LSM_ID
, DTLS.CONTRACT_NUMBER CONTRACT_NUMBER
, STYT.NAME TRANSACTION_TYPE
, DTLS.INVOICE_NUMBER INVOICE_NUMBER
, DTLS.INVOICE_AMOUNT INVOICE_AMOUNT
, DTLS.LINE_IDENTIFIER LINE_IDENTIFIER
, OKL_CS_LC_CONTRACT_PVT.GET_ASSET_NUMBER(DTLS.KLE_ID) ASSET_NUMBER
, DTLS.INVOICE_DATE INVOICE_DATE
, DTLS.DUE_DATE DUE_DATE
, DTLS.AMOUNT_ORIGINAL TRANSACTION_AMOUNT
, DTLS.AMOUNT_APPLIED AMOUNT_APPLIED
, DTLS.AMOUNT_REMAINING AMOUNT_REMAINING
, DTLS.CUSTOMER_ACCT_ID CUSTOMER_ACCOUNT_ID
, DTLS.ORG_ID CONTRACT_AUTHORING_ORG_ID
, DTLS.CHR_ID KHR_ID
, DTLS.KLE_ID KLE_ID
, TO_NUMBER(OKL_CS_LC_CONTRACT_PVT.GET_COV_ASSET_ID(DTLS.KLE_ID)) ASSET_ID
, DTLS.INVOICE_ID INVOICE_ID
, STYT.ID STY_ID
, DTLS.INVOICE_CURRENCY_CODE CURRENCY_CODE
, APS.EXCHANGE_RATE_TYPE AR_EXCHANGE_RATE_TYPE
, APS.EXCHANGE_RATE AR_EXCHANGE_RATE
, APS.EXCHANGE_DATE AR_EXCHANGE_DATE
, APS.TRX_DATE AR_TRX_DATE
, DTLS.LINE_AMOUNT AMOUNT_LINE_ITEMS_ORIGINAL
, DTLS.AMOUNT_REMAINING AMOUNT_LINE_ITEMS_REMAINING
, DTLS.TAX_AMOUNT TAX_ORIGINAL
, DTLS.TAX_REMAINING TAX_REMAINING
, DTLS.AMOUNT_CREDITED AMOUNT_CREDITED
, DTLS.AR_INVOICE_ID CUSTOMER_TRX_ID
, DTLS.INVOICE_LINE_ID INVOICE_LINE_ID
, DTLS.PAYMENT_TERM PAYMENT_TERM
, DTLS.INVOICE_TYPE INVOICE_TYPE
FROM AR_PAYMENT_SCHEDULES_ALL APS
, OKL_STRM_TYPE_TL STYT
, OKL_CS_BPD_INV_DTL_V DTLS
WHERE DTLS.KLE_ID IS NOT NULL
AND DTLS.AR_INVOICE_ID = APS.CUSTOMER_TRX_ID
AND DTLS.STY_ID = STYT.ID
AND STYT.LANGUAGE = USERENV('LANG')
AND NOT EXISTS (SELECT 1
FROM OKC_K_LINES_B CLE2
, OKC_LINE_STYLES_B LSE
WHERE CLE2.CLE_ID = DTLS.KLE_ID
AND CLE2.LSE_ID = LSE.ID
AND LSE.LTY_CODE = 'FIXED_ASSET') UNION ALL SELECT DTLS.LSM_ID LSM_ID
, DTLS.CONTRACT_NUMBER CONTRACT_NUMBER
, STYT.NAME TRANSACTION_TYPE
, DTLS.INVOICE_NUMBER INVOICE_NUMBER
, DTLS.INVOICE_AMOUNT INVOICE_AMOUNT
, DTLS.LINE_IDENTIFIER LINE_IDENTIFIER
, NULL ASSET_NUMBER
, DTLS.INVOICE_DATE INVOICE_DATE
, DTLS.DUE_DATE DUE_DATE
, DTLS.AMOUNT_ORIGINAL TRANSACTION_AMOUNT
, DTLS.AMOUNT_APPLIED AMOUNT_APPLIED
, DTLS.AMOUNT_REMAINING AMOUNT_REMAINING
, DTLS.CUSTOMER_ACCT_ID CUSTOMER_ACCOUNT_ID
, DTLS.ORG_ID CONTRACT_AUTHORING_ORG_ID
, DTLS.CHR_ID KHR_ID
, TO_NUMBER(NULL) KLE_ID
, TO_NUMBER(NULL) ASSET_ID
, DTLS.INVOICE_ID INVOICE_ID
, STYT.ID STY_ID
, DTLS.INVOICE_CURRENCY_CODE CURRENCY_CODE
, APS.EXCHANGE_RATE_TYPE AR_EXCHANGE_RATE_TYPE
, APS.EXCHANGE_RATE AR_EXCHANGE_RATE
, APS.EXCHANGE_DATE AR_EXCHANGE_DATE
, APS.TRX_DATE AR_TRX_DATE
, DTLS.LINE_AMOUNT AMOUNT_LINE_ITEMS_ORIGINAL
, DTLS.AMOUNT_REMAINING AMOUNT_LINE_ITEMS_REMAINING
, DTLS.TAX_AMOUNT TAX_ORIGINAL
, DTLS.TAX_REMAINING TAX_REMAINING
, DTLS.AMOUNT_CREDITED AMOUNT_CREDITED
, DTLS.AR_INVOICE_ID CUSTOMER_TRX_ID
, DTLS.INVOICE_LINE_ID INVOICE_LINE_ID
, DTLS.PAYMENT_TERM PAYMENT_TERM
, DTLS.INVOICE_TYPE INVOICE_TYPE
FROM OKL_CS_BPD_INV_DTL_V DTLS
, AR_PAYMENT_SCHEDULES_ALL APS
, OKL_STRM_TYPE_TL STYT
WHERE DTLS.KLE_ID IS NULL
AND DTLS.AR_INVOICE_ID = APS.CUSTOMER_TRX_ID
AND DTLS.STY_ID = STYT.ID
AND STYT.LANGUAGE = USERENV('LANG') UNION ALL SELECT DTLS.LSM_ID LSM_ID
, DTLS.CONTRACT_NUMBER CONTRACT_NUMBER
, STYT.NAME TRANSACTION_TYPE
, DTLS.INVOICE_NUMBER INVOICE_NUMBER
, DTLS.INVOICE_AMOUNT INVOICE_AMOUNT
, DTLS.LINE_IDENTIFIER LINE_IDENTIFIER
, CLT.NAME ASSET_NUMBER
, DTLS.INVOICE_DATE INVOICE_DATE
, DTLS.DUE_DATE DUE_DATE
, DTLS.AMOUNT_ORIGINAL TRANSACTION_AMOUNT
, DTLS.AMOUNT_APPLIED AMOUNT_APPLIED
, DTLS.AMOUNT_REMAINING AMOUNT_REMAINING
, DTLS.CUSTOMER_ACCT_ID CUSTOMER_ACCOUNT_ID
, DTLS.ORG_ID CONTRACT_AUTHORING_ORG_ID
, DTLS.CHR_ID KHR_ID
, DTLS.KLE_ID KLE_ID
, TO_NUMBER(NULL) ASSET_ID
, DTLS.INVOICE_ID INVOICE_ID
, STYT.ID STY_ID
, DTLS.INVOICE_CURRENCY_CODE CURRENCY_CODE
, APS.EXCHANGE_RATE_TYPE AR_EXCHANGE_RATE_TYPE
, APS.EXCHANGE_RATE AR_EXCHANGE_RATE
, APS.EXCHANGE_DATE AR_EXCHANGE_DATE
, APS.TRX_DATE AR_TRX_DATE
, DTLS.LINE_AMOUNT AMOUNT_LINE_ITEMS_ORIGINAL
, DTLS.AMOUNT_REMAINING AMOUNT_LINE_ITEMS_REMAINING
, DTLS.TAX_AMOUNT TAX_ORIGINAL
, DTLS.TAX_REMAINING TAX_REMAINING
, DTLS.AMOUNT_CREDITED AMOUNT_CREDITED
, DTLS.AR_INVOICE_ID CUSTOMER_TRX_ID
, DTLS.INVOICE_LINE_ID INVOICE_LINE_ID
, DTLS.PAYMENT_TERM PAYMENT_TERM
, DTLS.INVOICE_TYPE INVOICE_TYPE
FROM OKL_CS_BPD_INV_DTL_V DTLS
, AR_PAYMENT_SCHEDULES_ALL APS
, OKL_STRM_TYPE_TL STYT
, OKL_K_HEADERS CHL
, OKC_K_LINES_B CLE
, OKC_LINE_STYLES_B LSE
, OKC_K_LINES_TL CLT
WHERE DTLS.KLE_ID IS NOT NULL
AND DTLS.AR_INVOICE_ID = APS.CUSTOMER_TRX_ID
AND DTLS.STY_ID = STYT.ID
AND STYT.LANGUAGE = USERENV('LANG')
AND DTLS.CHR_ID = CHL.ID
AND CHL.DEAL_TYPE = 'LOAN'
AND DTLS.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')

Columns