VIEW: APPS.OKL_AR_TRX_LINE_UV

Source

View Text - Preformatted

SELECT TO_CHAR(RCTL.CUSTOMER_TRX_LINE_ID) ID, RCTL.LINE_NUMBER LINE_NUMBER, RCT.INTERFACE_HEADER_ATTRIBUTE1 || ' - ' || RCTT.NAME || ' - ' || OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING('INV/CM', RCTT.TYPE, 222, 222) DESCRIPTION, RCTL.EXTENDED_AMOUNT AMOUNT, TO_CHAR(NULL) CURRENCY_CODE, STRM.NAME STREAM_TYPE, OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING('OKL_STREAM_TYPE_PURPOSE', STRM.STREAM_TYPE_PURPOSE) STREAM_TYPE_PURPOSE, RCTL.CUSTOMER_TRX_ID TRANSACTION_ID, 'RA_CUSTOMER_TRX_LINES' SOURCE_TABLE, LSMB.KHR_ID KHR_ID , RCTL.CUSTOMER_TRX_ID CUSTOMER_TRX_ID FROM RA_CUSTOMER_TRX_LINES_ALL RCTL,OKL_CNSLD_AR_STRMS_B LSMB,OKL_STRM_TYPE_V STRM,RA_CUSTOMER_TRX_ALL RCT,RA_CUST_TRX_TYPES RCTT WHERE RCTL.INTERFACE_LINE_CONTEXT IN ( 'OKL_CONTRACTS','OKL_MANUAL') AND (RCTL.INTERFACE_LINE_ATTRIBUTE10 || RCTL.INTERFACE_LINE_ATTRIBUTE11) = LSMB.ID AND LSMB.STY_ID = STRM.ID AND RCT.CUSTOMER_TRX_ID = RCTL.CUSTOMER_TRX_ID AND RCT.CUST_TRX_TYPE_ID = RCTT.CUST_TRX_TYPE_ID UNION ALL SELECT TO_CHAR(RAP.RECEIVABLE_APPLICATION_ID) ID, RAP.APPLIED_CUSTOMER_TRX_ID LINE_NUMBER, BB.BANK_NAME || ' - ' || BB.BANK_BRANCH_NAME || ' - ' || BA.BANK_ACCOUNT_NAME || ' - ' || BA.BANK_ACCOUNT_NUM || ' - ' || ARM.NAME DESCRIPTION, RAP.AMOUNT_APPLIED AMOUNT, CR.CURRENCY_CODE CURRENCY_CODE, NULL STREAM_TYPE, NULL STREAM_TYPE_PURPOSE, CR.CASH_RECEIPT_ID TRANSACTION_ID, 'AR_CASH_RECEIPT' SOURCE_TABLE, LSM.KHR_ID KHR_ID, RAP.APPLIED_CUSTOMER_TRX_ID FROM OKL_CNSLD_AR_STRMS_B LSM, AR_RECEIVABLE_APPLICATIONS_ALL RAP, AR_CASH_RECEIPTS_ALL CR, AR_CASH_RECEIPT_HISTORY_ALL CRH, AR_RECEIPT_METHODS ARM, CE_BANK_ACCOUNTS BA, CE_BANK_ACCT_USES_ALL BAU, CE_BANK_BRANCHES_V BB WHERE lsm.receivables_invoice_id = rap.applied_customer_trx_id AND rap.cash_receipt_id = cr.cash_receipt_id AND rap.status = 'APP' AND cr.cash_receipt_id = crh.cash_receipt_id AND cr.receipt_method_id = arm.receipt_method_id AND cr.REMIT_BANK_ACCT_USE_ID = BAU.BANK_ACCT_USE_ID AND BA.BANK_ACCOUNT_ID = BAU.BANK_ACCOUNT_ID AND BA.BANK_BRANCH_ID = BB.BRANCH_PARTY_ID

View Text - HTML Formatted

SELECT TO_CHAR(RCTL.CUSTOMER_TRX_LINE_ID) ID
, RCTL.LINE_NUMBER LINE_NUMBER
, RCT.INTERFACE_HEADER_ATTRIBUTE1 || ' - ' || RCTT.NAME || ' - ' || OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING('INV/CM'
, RCTT.TYPE
, 222
, 222) DESCRIPTION
, RCTL.EXTENDED_AMOUNT AMOUNT
, TO_CHAR(NULL) CURRENCY_CODE
, STRM.NAME STREAM_TYPE
, OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING('OKL_STREAM_TYPE_PURPOSE'
, STRM.STREAM_TYPE_PURPOSE) STREAM_TYPE_PURPOSE
, RCTL.CUSTOMER_TRX_ID TRANSACTION_ID
, 'RA_CUSTOMER_TRX_LINES' SOURCE_TABLE
, LSMB.KHR_ID KHR_ID
, RCTL.CUSTOMER_TRX_ID CUSTOMER_TRX_ID
FROM RA_CUSTOMER_TRX_LINES_ALL RCTL
, OKL_CNSLD_AR_STRMS_B LSMB
, OKL_STRM_TYPE_V STRM
, RA_CUSTOMER_TRX_ALL RCT
, RA_CUST_TRX_TYPES RCTT
WHERE RCTL.INTERFACE_LINE_CONTEXT IN ( 'OKL_CONTRACTS'
, 'OKL_MANUAL')
AND (RCTL.INTERFACE_LINE_ATTRIBUTE10 || RCTL.INTERFACE_LINE_ATTRIBUTE11) = LSMB.ID
AND LSMB.STY_ID = STRM.ID
AND RCT.CUSTOMER_TRX_ID = RCTL.CUSTOMER_TRX_ID
AND RCT.CUST_TRX_TYPE_ID = RCTT.CUST_TRX_TYPE_ID UNION ALL SELECT TO_CHAR(RAP.RECEIVABLE_APPLICATION_ID) ID
, RAP.APPLIED_CUSTOMER_TRX_ID LINE_NUMBER
, BB.BANK_NAME || ' - ' || BB.BANK_BRANCH_NAME || ' - ' || BA.BANK_ACCOUNT_NAME || ' - ' || BA.BANK_ACCOUNT_NUM || ' - ' || ARM.NAME DESCRIPTION
, RAP.AMOUNT_APPLIED AMOUNT
, CR.CURRENCY_CODE CURRENCY_CODE
, NULL STREAM_TYPE
, NULL STREAM_TYPE_PURPOSE
, CR.CASH_RECEIPT_ID TRANSACTION_ID
, 'AR_CASH_RECEIPT' SOURCE_TABLE
, LSM.KHR_ID KHR_ID
, RAP.APPLIED_CUSTOMER_TRX_ID
FROM OKL_CNSLD_AR_STRMS_B LSM
, AR_RECEIVABLE_APPLICATIONS_ALL RAP
, AR_CASH_RECEIPTS_ALL CR
, AR_CASH_RECEIPT_HISTORY_ALL CRH
, AR_RECEIPT_METHODS ARM
, CE_BANK_ACCOUNTS BA
, CE_BANK_ACCT_USES_ALL BAU
, CE_BANK_BRANCHES_V BB
WHERE LSM.RECEIVABLES_INVOICE_ID = RAP.APPLIED_CUSTOMER_TRX_ID
AND RAP.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID
AND RAP.STATUS = 'APP'
AND CR.CASH_RECEIPT_ID = CRH.CASH_RECEIPT_ID
AND CR.RECEIPT_METHOD_ID = ARM.RECEIPT_METHOD_ID
AND CR.REMIT_BANK_ACCT_USE_ID = BAU.BANK_ACCT_USE_ID
AND BA.BANK_ACCOUNT_ID = BAU.BANK_ACCOUNT_ID
AND BA.BANK_BRANCH_ID = BB.BRANCH_PARTY_ID