View: OKL_RECEIPT_DETAILS_UV

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

View Text

SELECT AR_HDR.CASH_RECEIPT_ID CASH_RECEIPT_ID
, AR_HDR.ORG_ID ORG_ID
, HR.NAME ORG_NAME
, AR_HDR.LEGAL_ENTITY_ID LEGAL_ENTITY_ID
, LE.NAME LEGAL_ENTITY_NAME
, AR_HDR.RECEIPT_NUMBER RECEIPT_NUMBER
, AR_HDR.RECEIPT_DATE RECEIPT_DATE
, PARTY.PARTY_NAME CUSTOMER_NAME
, CUST.ACCOUNT_NUMBER CUSTOMER_ACCOUNT
, AR_HDR.AMOUNT RECEIPT_AMOUNT
, (SELECT OKL_ACCOUNTING_UTIL.ROUND_AMOUNT(NVL(SUM(APP_LINE.AMOUNT_APPLIED*NVL(APP_LINE.TRANS_TO_RECEIPT_RATE
, 1))
, 0)
, AR_HDR.CURRENCY_CODE)
FROM AR_RECEIVABLE_APPLICATIONS_ALL APP_LINE
WHERE APP_LINE.CASH_RECEIPT_ID = AR_HDR.CASH_RECEIPT_ID
AND APP_LINE.STATUS = 'APP'
AND APP_LINE.APPLICATION_TYPE = 'CASH') APPLIED_AMOUNT
, (SELECT OKL_ACCOUNTING_UTIL.ROUND_AMOUNT(NVL(SUM(UNAPP_LINE.AMOUNT_APPLIED*NVL(UNAPP_LINE.TRANS_TO_RECEIPT_RATE
, 1))
, 0)
, AR_HDR.CURRENCY_CODE)
FROM AR_RECEIVABLE_APPLICATIONS_ALL UNAPP_LINE
WHERE UNAPP_LINE.CASH_RECEIPT_ID = AR_HDR.CASH_RECEIPT_ID
AND UNAPP_LINE.STATUS = 'UNAPP'
AND UNAPP_LINE.APPLICATION_TYPE = 'CASH') UNAPPLIED_AMOUNT
, (SELECT OKL_ACCOUNTING_UTIL.ROUND_AMOUNT(NVL(SUM(ACC_LINE.AMOUNT_APPLIED*NVL(ACC_LINE.TRANS_TO_RECEIPT_RATE
, 1))
, 0)
, AR_HDR.CURRENCY_CODE)
FROM AR_RECEIVABLE_APPLICATIONS_ALL ACC_LINE
WHERE ACC_LINE.CASH_RECEIPT_ID = AR_HDR.CASH_RECEIPT_ID
AND ACC_LINE.STATUS = 'ACC'
AND ACC_LINE.APPLICATION_TYPE = 'CASH') ONACCOUNT_AMOUNT
, AR_HDR.CURRENCY_CODE CURRENCY_CODE
, TRX.GL_DATE GL_DATE
, AR_HDR.EXCHANGE_RATE_TYPE CURRENCY_CONVERSION_TYPE
, AR_HDR.EXCHANGE_RATE CURRENCY_CONVERSION_RATE
, AR_HDR.EXCHANGE_DATE CURRENCY_CONVERSION_DATE
, RM.RECEIPT_METHOD_ID RECEIPT_METHOD_ID
, RM.RECEIPT_METHOD_NAME RECEIPT_METHOD
, RM.BANK_NAME REMIT_BANK_NAME
, RM.BANK_ACCOUNT_NUM REMIT_BANK_ACCOUNT
, RM.BANK_ACCOUNT_ID REMIT_BANK_ACCOUNT_ID
, AR_HDR.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY
, AR_HDR.ATTRIBUTE1 ATTRIBUTE1
, AR_HDR.ATTRIBUTE2 ATTRIBUTE2
, AR_HDR.ATTRIBUTE3 ATTRIBUTE3
, AR_HDR.ATTRIBUTE4 ATTRIBUTE4
, AR_HDR.ATTRIBUTE5 ATTRIBUTE5
, AR_HDR.ATTRIBUTE6 ATTRIBUTE6
, AR_HDR.ATTRIBUTE7 ATTRIBUTE7
, AR_HDR.ATTRIBUTE8 ATTRIBUTE8
, AR_HDR.ATTRIBUTE9 ATTRIBUTE9
, AR_HDR.ATTRIBUTE10 ATTRIBUTE10
, AR_HDR.ATTRIBUTE11 ATTRIBUTE11
, AR_HDR.ATTRIBUTE12 ATTRIBUTE12
, AR_HDR.ATTRIBUTE13 ATTRIBUTE13
, AR_HDR.ATTRIBUTE14 ATTRIBUTE14
, AR_HDR.ATTRIBUTE15 ATTRIBUTE15
, (SELECT DISTINCT B.KHR_ID
FROM OKL_TXL_RCPT_APPS_ALL_B B
, OKL_TRX_CSH_RCPT_ALL_B A
WHERE A.ID = B.RCT_ID_DETAILS
AND A.RECEIPT_TYPE ='ADV'
AND B.KHR_ID IS NOT NULL
AND A.CASH_RECEIPT_ID = AR_HDR.CASH_RECEIPT_ID ) ADV_RCT_KHR_ID
, CUST.CUST_ACCOUNT_ID
, PARTY.PARTY_ID
, IEBA.EXT_BANK_ACCOUNT_ID
, IEBA.BRANCH_ID BANK_BRANCH_ID
, IEBA.MASKED_BANK_ACCOUNT_NUM BANK_ACCOUNT_NUMBER
, BANK.PARTY_NAME BANK_NAME
, CRH.STATUS STATUS
FROM AR_CASH_RECEIPTS_ALL AR_HDR
, HR_OPERATING_UNITS HR
, XLE_ENTITY_PROFILES LE
, HZ_CUST_ACCOUNTS CUST
, HZ_PARTIES PARTY
, AR_CASH_RECEIPT_HISTORY_ALL TRX
, OKL_BPD_RCPT_MTHDS_UV RM
, IBY_EXT_BANK_ACCOUNTS IEBA
, HZ_PARTIES BANK
, AR_CASH_RECEIPT_HISTORY_ALL CRH
WHERE AR_HDR.ORG_ID = HR.ORGANIZATION_ID
AND AR_HDR.PAY_FROM_CUSTOMER = CUST.CUST_ACCOUNT_ID (+)
AND CUST.PARTY_ID = PARTY.PARTY_ID (+)
AND AR_HDR.LEGAL_ENTITY_ID = LE.LEGAL_ENTITY_ID (+)
AND AR_HDR.CASH_RECEIPT_ID = TRX.CASH_RECEIPT_ID
AND AR_HDR.RECEIPT_METHOD_ID = RM.RECEIPT_METHOD_ID (+)
AND AR_HDR.REMIT_BANK_ACCT_USE_ID = RM.BANK_ACCOUNT_ID (+)
AND AR_HDR.CUSTOMER_BANK_ACCOUNT_ID = IEBA.EXT_BANK_ACCOUNT_ID (+)
AND IEBA.BANK_ID = BANK.PARTY_ID(+)
AND AR_HDR.CASH_RECEIPT_ID = CRH.CASH_RECEIPT_ID
AND CRH.CURRENT_RECORD_FLAG = 'Y'

Columns