View: OKL_BPD_AR_HEADER_V

Product:	OKL - Lease and Finance Management
Description:	View for Operations, Transactions search page
Implementation/DBA Data:	APPS.OKL_BPD_AR_HEADER_V

View Text

SELECT TO_CHAR(A.ID) ID
, A.KHR_ID
, A.CONTRACT_NUMBER
, A.SOURCE_CODE
, A.SOURCE
, A.TRANSACTION_NUMBER
, A.TRANSACTION_TYPE
, A.TRANSACTION_STATUS
, A.TRANSACTION_DATE
, A.TRX_TYPE
, A.DESCRIPTION
, A.PRODUCT_NAME
, A.AMOUNT
, A.CURRENCY_CODE
, A.ACTUAL_DRAFT_STATUS_CODE
, A.ACTUAL_DRAFT_STATUS
, ( SELECT SUM(B.EXTENDED_AMOUNT)
FROM RA_CUSTOMER_TRX_LINES_ALL B
WHERE A.ID = B.CUSTOMER_TRX_ID
AND B.LINE_TYPE IN ('LINE'
, 'TAX') ) TOTAL_TRANSACTION_AMOUNT
, A.CHECK_ID
, A.LEDGER_ID
, A.ORG_ID
, A.TRY_ID
, A.ACCOUNT_NUMBER
FROM ( SELECT RCT.CUSTOMER_TRX_ID ID
, OKHB.ID KHR_ID
, OKHB.CONTRACT_NUMBER
, 'REC' SOURCE_CODE
, OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING('OKL_VIEW_ACC_SOURCES'
, 'REC') SOURCE
, RCT.TRX_NUMBER TRANSACTION_NUMBER
, OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING('OKL_VIEW_TRANSACTIONS'
, RCTT.TYPE) TRANSACTION_TYPE
, OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING('INVOICE_DETAIL_REPORT'
, RCT.COMPLETE_FLAG
, 222
, 222) TRANSACTION_STATUS
, RCT.TRX_DATE TRANSACTION_DATE
, RCTT.TYPE TRX_TYPE
, RCT.INTERFACE_HEADER_ATTRIBUTE1 || DECODE(RCT.INTERFACE_HEADER_ATTRIBUTE1
, NULL
, ''
, ' - ') || RCTT.NAME || ' - ' || OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING('INV/CM'
, RCTT.TYPE
, 222
, 222) DESCRIPTION
, OP.NAME PRODUCT_NAME
, SUM(EXTENDED_AMOUNT + NVL(TAX_RECOVERABLE
, 0)) AMOUNT
, /* APS.AMOUNT_DUE_ORIGINAL AMOUNT
, */ RCT.INVOICE_CURRENCY_CODE CURRENCY_CODE
, 'ACTUAL' ACTUAL_DRAFT_STATUS_CODE
, OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING('OKL_ACTUAL_DRAFT_STATUS'
, 'ACTUAL') ACTUAL_DRAFT_STATUS
, SUM(RCTL.QUANTITY_INVOICED * RCTL.UNIT_SELLING_PRICE) TOTAL_TRANSACTION_AMOUNT2
, NULL CHECK_ID
, RCT.SET_OF_BOOKS_ID LEDGER_ID
, RCT.ORG_ID ORG_ID
, ACCT.ACCOUNT_NUMBER ACCOUNT_NUMBER
, TAI.TRY_ID TRY_ID
FROM OKL_TXD_AR_LN_DTLS_B TXD
, OKC_K_HEADERS_B OKHB
, OKL_K_HEADERS OKH
, OKL_PRODUCTS OP
, RA_CUSTOMER_TRX_ALL RCT
, RA_CUSTOMER_TRX_LINES_ALL RCTL
, RA_CUST_TRX_TYPES RCTT
, AR_PAYMENT_SCHEDULES_ALL APS
, HZ_CUST_ACCOUNTS ACCT
, OKL_TXL_AR_INV_LNS_B TIL
, OKL_TRX_AR_INVOICES_B TAI
WHERE TXD.KHR_ID = OKHB.ID
AND OKHB.ID = OKH.ID
AND OKH.PDT_ID = OP.ID
AND TXD.ID = RCTL.INTERFACE_LINE_ATTRIBUTE14
AND RCT.CUST_TRX_TYPE_ID = RCTT.CUST_TRX_TYPE_ID
AND RCT.CUSTOMER_TRX_ID = RCTL.CUSTOMER_TRX_ID
AND RCT.ORG_ID = RCTT.ORG_ID
AND RCT.BILL_TO_CUSTOMER_ID = ACCT.CUST_ACCOUNT_ID
AND RCT.CUSTOMER_TRX_ID = APS.CUSTOMER_TRX_ID
AND TXD.TIL_ID_DETAILS = TIL.ID
AND TIL.TAI_ID = TAI.ID
AND OKHB.CONTRACT_NUMBER = RCTL.INTERFACE_LINE_ATTRIBUTE6 GROUP BY RCT.CUSTOMER_TRX_ID
, OKHB.ID
, OKHB.CONTRACT_NUMBER
, 'REC'
, OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING('OKL_VIEW_ACC_SOURCES'
, 'REC')
, RCT.TRX_NUMBER
, OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING('OKL_VIEW_TRANSACTIONS'
, RCTT.TYPE)
, OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING('INVOICE_DETAIL_REPORT'
, RCT.COMPLETE_FLAG
, 222
, 222)
, RCT.TRX_DATE
, RCTT.TYPE
, RCT.INTERFACE_HEADER_ATTRIBUTE1 || DECODE(RCT.INTERFACE_HEADER_ATTRIBUTE1
, NULL
, ''
, ' - ') || RCTT.NAME || ' - ' || OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING('INV/CM'
, RCTT.TYPE
, 222
, 222)
, OP.NAME
, APS.AMOUNT_DUE_ORIGINAL
, RCT.INVOICE_CURRENCY_CODE
, 'ACTUAL'
, OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING('OKL_ACTUAL_DRAFT_STATUS'
, 'ACTUAL')
, NULL
, RCT.SET_OF_BOOKS_ID
, RCT.ORG_ID
, ACCT.ACCOUNT_NUMBER
, TAI.TRY_ID ) A

Columns