eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.OKL_RECEIPT_DETAILS_UV

Source

View Text - Preformatted


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(nvl(app_line.amount_applied_from,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, ar_hdr.Receipt_Method_Id Receipt_Method_Id, ar_hdr.Remit_Bank_acct_Use_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, 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.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'

View Text - HTML Formatted

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(NVL(APP_LINE.AMOUNT_APPLIED_FROM
, 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
, AR_HDR.RECEIPT_METHOD_ID RECEIPT_METHOD_ID
, AR_HDR.REMIT_BANK_ACCT_USE_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
, 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.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'