View: OKL_BPD_RECEIPT_APPLIC_UV

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

View Text

SELECT HD.IXX_ID CUSTOMER_ID
, SUBSTRB (PY.PARTY_NAME
, 1
, 50) CUSTOMER_NAME
, CA.ACCOUNT_NUMBER CUSTOMER_NUMBER
, HD.ID CONSOLIDATED_INVOICE_ID
, HD.CONSOLIDATED_INVOICE_NUMBER CONSOLIDATED_INVOICE_NUMBER
, HD.CURRENCY_CODE RCPT_CURRENCY
, ST.KHR_ID CONTRACT_ID
, ST.KLE_ID CONTRACT_LINE_ID
, CNT.CONTRACT_NUMBER CONTRACT_NUMBER
, EXT.CHECK_NUMBER RECEIPT_NUMBER
, EXT.ICR_ID CASH_RECEIPT_ID
, EXT.RECEIPT_DATE RECEIPT_DATE
, EXT.ID EXT_CASH_APPS_ID
, XTL.ID XTL_CASH_APPS_ID
, ST.RECEIVABLES_INVOICE_ID RECEIVABLES_INVOICE_ID
, PS.TRX_NUMBER AR_INVOICE_NUMBER
, FA.ASSET_ID ASSET_ID
, FA.ASSET_NUMBER ASSET_NUMBER
, FA.DESCRIPTION ASSET_NAME
, ST.ID LSM_ID
, SM.ID STREAM_ID
, SM.NAME STREAM_NAME
, PS.AMOUNT_DUE_REMAINING OUTSTANDING_STREAM_AMOUNT
, XTL.AMOUNT_APPLIED APPLIED_STREAM_AMOUNT
, EXT.GL_DATE GL_DATE
, RCT.IRM_ID IRM_ID
, EXT.REMITTANCE_AMOUNT RECEIPT_AMOUNT
, EXT.EXCHANGE_RATE EXCHANGE_RATE
, EXT.EXCHANGE_RATE_TYPE EXCHANGE_RATE_TYPE
, EXT.EXCHANGE_RATE_DATE EXCHANGE_RATE_DATE
, OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING('OKL_STREAM_TYPE_PURPOSE'
, SM.STREAM_TYPE_PURPOSE) STREAM_PURPOSE_MEANING
FROM FA_ADDITIONS_V FA
, OKL_STRM_TYPE_V SM
, OKL_EXT_CSH_RCPTS_ALL_B EXT
, OKL_XTL_CSH_APPS_ALL_B XTL
, OKL_TRX_CSH_RCPT_ALL_B RCT
, AR_PAYMENT_SCHEDULES_ALL PS
, OKL_CNSLD_AR_STRMS_B ST
, OKL_CNSLD_AR_LINES_B LN
, OKL_CNSLD_AR_HDRS_B HD
, HZ_CUST_ACCOUNTS CA
, HZ_PARTIES PY
, OKC_K_HEADERS_ALL_B CNT
, OKC_K_LINES_B CLE
, OKC_LINE_STYLES_B LSE
, OKC_K_ITEMS CIM
WHERE XTL.XCR_ID_DETAILS = EXT.ID
AND EXT.RCT_ID = RCT.ID
AND ST.ID = XTL.LSM_ID
AND PS.CUSTOMER_TRX_ID = ST.RECEIVABLES_INVOICE_ID
AND SM.ID = ST.STY_ID
AND LN.ID = ST.LLN_ID
AND HD.ID = LN.CNR_ID
AND CA.CUST_ACCOUNT_ID = HD.IXX_ID
AND PY.PARTY_ID = CA.PARTY_ID
AND CNT.ID = ST.KHR_ID
AND CLE.CLE_ID = ST.KLE_ID
AND CLE.LSE_ID = LSE.ID
AND LSE.LTY_CODE = 'FIXED_ASSET'
AND CIM.CLE_ID = CLE.ID
AND CIM.JTOT_OBJECT1_CODE = 'OKX_ASSET'
AND CIM.OBJECT1_ID2 = '#'
AND FA.ASSET_ID = CIM.OBJECT1_ID1 UNION SELECT HD.IXX_ID
, SUBSTRB (PY.PARTY_NAME
, 1
, 50)
, CA.ACCOUNT_NUMBER
, HD.ID
, HD.CONSOLIDATED_INVOICE_NUMBER
, HD.CURRENCY_CODE
, ST.KHR_ID
, ST.KLE_ID
, CNT.CONTRACT_NUMBER
, EXT.CHECK_NUMBER
, EXT.ICR_ID CASH_RECEIPT_ID
, EXT.RECEIPT_DATE
, EXT.ID
, XTL.ID
, ST.RECEIVABLES_INVOICE_ID
, PS.TRX_NUMBER
, TO_NUMBER (NULL)
, ''
, ''
, ST.ID
, SM.ID
, SM.NAME
, PS.AMOUNT_DUE_REMAINING
, XTL.AMOUNT_APPLIED
, EXT.GL_DATE
, RCT.IRM_ID
, EXT.REMITTANCE_AMOUNT
, EXT.EXCHANGE_RATE
, EXT.EXCHANGE_RATE_TYPE
, EXT.EXCHANGE_RATE_DATE
, OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING('OKL_STREAM_TYPE_PURPOSE'
, SM.STREAM_TYPE_PURPOSE)
FROM OKL_EXT_CSH_RCPTS_ALL_B EXT
, OKL_XTL_CSH_APPS_ALL_B XTL
, OKL_TRX_CSH_RCPT_ALL_B RCT
, AR_PAYMENT_SCHEDULES_ALL PS
, OKL_STRM_TYPE_V SM
, OKL_CNSLD_AR_STRMS_B ST
, OKL_CNSLD_AR_LINES_B LN
, OKL_CNSLD_AR_HDRS_B HD
, HZ_CUST_ACCOUNTS CA
, HZ_PARTIES PY
, OKC_K_HEADERS_ALL_B CNT
WHERE XTL.XCR_ID_DETAILS = EXT.ID
AND EXT.RCT_ID = RCT.ID
AND ST.ID = XTL.LSM_ID
AND PS.CUSTOMER_TRX_ID = ST.RECEIVABLES_INVOICE_ID
AND SM.ID = ST.STY_ID
AND LN.ID = ST.LLN_ID
AND HD.ID = LN.CNR_ID
AND CA.CUST_ACCOUNT_ID = HD.IXX_ID
AND PY.PARTY_ID = CA.PARTY_ID
AND CNT.ID (+) = ST.KHR_ID
AND ( ST.KLE_ID IS NULL OR ( ST.KLE_ID IS NOT NULL
AND NOT EXISTS (SELECT 1
FROM OKC_K_LINES_B KLE
, OKC_LINE_STYLES_B LSE
WHERE KLE.CLE_ID = ST.KLE_ID
AND KLE.LSE_ID = LSE.ID
AND LSE.LTY_CODE = 'FIXED_ASSET'))) UNION SELECT HD.IXX_ID CUSTOMER_ID
, SUBSTRB (PY.PARTY_NAME
, 1
, 50) CUSTOMER_NAME
, CA.ACCOUNT_NUMBER CUSTOMER_NUMBER
, HD.ID CONSOLIDATED_INVOICE_ID
, HD.CONSOLIDATED_INVOICE_NUMBER CONSOLIDATED_INVOICE_NUMBER
, HD.CURRENCY_CODE RCPT_CURRENCY
, ST.KHR_ID CONTRACT_ID
, ST.KLE_ID CONTRACT_LINE_ID
, CNT.CONTRACT_NUMBER CONTRACT_NUMBER
, EXT.CHECK_NUMBER RECEIPT_NUMBER
, EXT.ICR_ID CASH_RECEIPT_ID
, EXT.RECEIPT_DATE RECEIPT_DATE
, EXT.ID EXT_CASH_APPS_ID
, XTL.ID XTL_CASH_APPS_ID
, ST.RECEIVABLES_INVOICE_ID RECEIVABLES_INVOICE_ID
, PS.TRX_NUMBER AR_INVOICE_NUMBER
, TO_NUMBER (NULL)
, ''
, ''
, ST.ID LSM_ID
, SM.ID STREAM_ID
, SM.NAME STREAM_NAME
, PS.AMOUNT_DUE_REMAINING OUTSTANDING_STREAM_AMOUNT
, XTL.AMOUNT_APPLIED APPLIED_STREAM_AMOUNT
, EXT.GL_DATE GL_DATE
, RCT.IRM_ID IRM_ID
, EXT.REMITTANCE_AMOUNT RECEIPT_AMOUNT
, EXT.EXCHANGE_RATE EXCHANGE_RATE
, EXT.EXCHANGE_RATE_TYPE EXCHANGE_RATE_TYPE
, EXT.EXCHANGE_RATE_DATE EXCHANGE_RATE_DATE
, OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING('OKL_STREAM_TYPE_PURPOSE'
, SM.STREAM_TYPE_PURPOSE) STREAM_PURPOSE_MEANING
FROM OKL_STRM_TYPE_V SM
, OKL_EXT_CSH_RCPTS_ALL_B EXT
, OKL_XTL_CSH_APPS_ALL_B XTL
, OKL_TRX_CSH_RCPT_ALL_B RCT
, AR_PAYMENT_SCHEDULES_ALL PS
, OKL_CNSLD_AR_STRMS_B ST
, OKL_CNSLD_AR_LINES_B LN
, OKL_CNSLD_AR_HDRS_B HD
, HZ_CUST_ACCOUNTS CA
, HZ_PARTIES PY
, OKC_K_HEADERS_ALL_B CNT
, OKC_K_LINES_B CLE
, OKC_LINE_STYLES_B LSE
, OKC_K_ITEMS CIM
WHERE XTL.XCR_ID_DETAILS = EXT.ID
AND EXT.RCT_ID = RCT.ID
AND ST.ID = XTL.LSM_ID
AND PS.CUSTOMER_TRX_ID = ST.RECEIVABLES_INVOICE_ID
AND SM.ID = ST.STY_ID
AND LN.ID = ST.LLN_ID
AND HD.ID = LN.CNR_ID
AND CA.CUST_ACCOUNT_ID = HD.IXX_ID
AND PY.PARTY_ID = CA.PARTY_ID
AND CNT.ID = ST.KHR_ID
AND CLE.CLE_ID = ST.KLE_ID
AND CLE.LSE_ID = LSE.ID
AND LSE.LTY_CODE = 'FIXED_ASSET'
AND CIM.CLE_ID = CLE.ID
AND CIM.JTOT_OBJECT1_CODE = 'OKX_ASSET'
AND CIM.OBJECT1_ID1 IS NULL

Columns