OKL Receipt Inquiry
Description
Categories: BI Publisher
Application: Lease and Finance Management
Source:
Short Name: OKLRECPTINQ
DB package:
Source:
Short Name: OKLRECPTINQ
DB package:
Download Blitz Report™ – World’s fastest reporting and data upload for Oracle EBS
Contact us to schedule a demo or if you need help with the installation
SELECT aRap.receivable_application_id RECEIVABLE_APPLICATION_ID , cUst_trx_Lines.InterFace_Line_Attribute9 TRANSACTION_TYPE, Nvl(cUst_trx_Lines.InterFace_Line_Attribute1,Pay_sch.trx_Number) INVOICE_NUMBER, Pay_sch.trx_Date INVOICE_DATE, ( nvl(cUst_trx_Lines.extended_amount,0) + (SELECT Nvl(SUM(extended_amount),0) FROM ra_Customer_trx_Lines_All Tax_Lines WHERE Tax_Lines.Link_To_cUst_trx_Line_Id = cUst_trx_Lines.Customer_trx_Line_Id AND Tax_Lines.Line_Type = 'TAX' ) ) TRANSACTION_AMOUNT, (arl.Amount + arl.tax ) AMOUNT_APPLIED, ( cUst_trx_Lines.Amount_Due_RemainIng + (SELECT Nvl(SUM(Amount_Due_RemainIng),0) FROM ra_Customer_trx_Lines_All Tax_Lines WHERE Tax_Lines.Link_To_cUst_trx_Line_Id = cUst_trx_Lines.Customer_trx_Line_Id AND Tax_Lines.Line_Type = 'TAX' ) ) AMOUNT_REMAINING, arm.NAME PAYMENT_METHOD, arcash.receipt_number RECEIPT_NUMBER, arcash.amount RECEIPT_AMOUNT, arcash.receipt_date RECEIPT_DATE, cUst_trx_Lines.InterFace_Line_Attribute6 CONTRACT_NUMBER, Pay_sch.Due_Date DUE_DATE, arap.apply_date PAYMENT_APPLICATION_DATE, alkp.meaning APPLICATION_STATUS, Nvl2(cUst_trx_Lines.InterFace_Line_Attribute1, (pay_sch.trx_number),To_char(cUst_trx_Lines.Line_Number)) LINE_IDENTIFIER, arcash.currency_code RECEIPT_CURRENCY_CODE, pay_sch.invoice_currency_code INVOICE_CURRENCY_CODE, arcash.receipt_method_id RECEIPT_METHOD_ID, ArcAsh.Cash_Receipt_Id CASH_RECEIPT_ID, pay_sch.customer_trx_id INVOICE_ID, khr.id CONTRACT_ID, arcash.pay_from_customer CUST_ACCOUNT_ID, DECODE((oklcash.receipt_type),'ADV','ADV','REG') RECEIPT_TYPE_CODE, arcash.org_id ORG_ID, hca.party_id CUSTOMER_ID, crh.status RECEIPT_STATUS_CODE, rctstslk.meaning RECEIPT_STATUS, cust_trx_lines.customer_trx_line_id INVOICE_LINE_ID, rtyplk.meaning ADVANCED_YN, hca.account_number CUSTOMER_ACCOUNT_NUMBER FROM ar_Cash_Receipts_All ArcAsh , ar_Receivable_Applications_All aRap , ar_Activity_Details arl , ar_Payment_Schedules_All Pay_sch , ra_customer_trx_all cust_trx , ra_Customer_trx_Lines_All cUst_trx_Lines, ar_Receipt_Methods Arm , okc_k_headers_all_b khr , okl_trx_csh_rcpt_all_b oklcash , hz_cust_accounts hca , ar_Cash_receipt_history_all crh , ar_lookups rctstslk , ar_lookups alkp , fnd_lookups rtyplk , okl_xmlp_params xmlp WHERE xmlp.batch_id = :BATCH_ID AND arap.receivable_application_id = xmlp.param_value and ArcAsh.Cash_Receipt_Id = aRap.Cash_Receipt_Id AND aRap.Status = 'APP' AND aRap.Display = 'Y' AND aRap.Receivable_Application_Id = arl.Source_Id AND arl.Source_Table = 'RA' AND arl.Customer_trx_Line_Id = cUst_trx_Lines.Customer_trx_Line_Id AND cUst_trx_Lines.Line_Type = 'LINE' AND Pay_sch.Payment_Schedule_Id = aRap.Applied_Payment_Schedule_Id AND pay_sch.customer_trx_id = cust_trx.customer_trx_id AND arm.receipt_method_id = arcash.receipt_method_id AND cust_trx_lines.interface_line_context = 'OKL_CONTRACTS' AND cust_trx_lines.interface_line_attribute6 = khr.contract_number AND arcash.cash_receipt_id = oklcash.cash_receipt_id(+) AND arcash.pay_from_customer = hca.cust_account_id AND arcash.cash_receipt_id = crh.cash_receipt_id AND crh.current_record_flag = 'Y' AND crh.status = rctstslk.lookup_code AND rctstslk.lookup_type = 'RECEIPT_CREATION_STATUS' AND arap.status = alkp.lookup_code AND ALKP.LOOKUP_TYPE = 'PAYMENT_TYPE' AND rtyplk.lookup_code = DECODE((oklcash.receipt_type),'ADV','Y','N') AND rtyplk.lookup_type = 'OKL_YES_NO' UNION ALL SELECT aRap.receivable_application_id RECEIVABLE_APPLICATION_ID , cUst_trx.InterFace_header_Attribute9 TRANSACTION_TYPE, Nvl( (SELECT ln.InterFace_Line_Attribute1 FROM ra_Customer_trx_Lines_All ln WHERE ln.Customer_trx_Id = cUst_trx.Customer_trx_Id AND ROWNUM < 2 ) ,Pay_sch.trx_Number) INVOICE_NUMBER, Pay_sch.trx_Date INVOICE_DATE, pay_sch.amount_due_original TRANSACTION_AMOUNT, arap.amount_Applied AMOUNT_APPLIED, pay_sch.amount_due_remaining AMOUNT_REMAINING, arm.NAME PAYMENT_METHOD, arcash.receipt_number RECEIPT_NUMBER, arcash.amount RECEIPT_AMOUNT, arcash.receipt_date RECEIPT_DATE, cUst_trx.InterFace_header_Attribute6 CONTRACT_NUMBER, Pay_sch.Due_Date DUE_DATE, arap.apply_date PAYMENT_APPLICATION_DATE, alkp.meaning APPLICATION_STATUS, Nvl2( (SELECT ln.InterFace_Line_Attribute1 FROM ra_Customer_trx_Lines_All ln WHERE ln.Customer_trx_Id = cUst_trx.Customer_trx_Id AND ROWNUM < 2 ) ,pay_sch.trx_number, NULL) LINE_IDENTIFIER, arcash.currency_code RECEIPT_CURRENCY_CODE, pay_sch.invoice_currency_code INVOICE_CURRENCY_CODE, arcash.receipt_method_id RECEIPT_METHOD_ID, ArcAsh.Cash_Receipt_Id CASH_RECEIPT_ID, pay_sch.customer_trx_id INVOICE_ID, khr.id CONTRACT_ID, arcash.pay_from_customer CUST_ACCOUNT_ID, DECODE((oklcash.receipt_type),'ADV','ADV','REG') RECEIPT_TYPE_CODE, arcash.org_id ORG_ID, hca.party_id CUSTOMER_ID, crh.status RECEIPT_STATUS_CODE, rctstslk.meaning RECEIPT_STATUS, NULL INVOICE_LINE_ID, rtyplk.meaning ADVANCED_YN, hca.account_number CUSTOMER_ACCOUNT_NUMBER FROM ar_Cash_Receipts_All ArcAsh , ar_Receivable_Applications_All aRap, ar_Payment_Schedules_All Pay_sch , ra_customer_trx_all cust_trx , ar_Receipt_Methods Arm , okc_k_headers_all_b khr , okl_trx_csh_rcpt_all_b oklcash , hz_cust_accounts hca , ar_Cash_receipt_history_all crh , ar_lookups rctstslk , ar_lookups alkp , fnd_lookups rtyplk , okl_xmlp_params xmlp WHERE xmlp.batch_id = :BATCH_ID AND arap.receivable_application_id = xmlp.param_value AND ArcAsh.Cash_Receipt_Id = aRap.Cash_Receipt_Id AND aRap.Status = 'APP' AND aRap.Display = 'Y' AND Pay_sch.Payment_Schedule_Id = aRap.Applied_Payment_Schedule_Id AND pay_sch.customer_trx_id = cust_trx.customer_trx_id AND arm.receipt_method_id = arcash.receipt_method_id AND cust_trx.interface_header_context = 'OKL_CONTRACTS' AND cust_trx.interface_header_attribute6 = khr.contract_number AND arcash.cash_receipt_id = oklcash.cash_receipt_id(+) AND arcash.pay_from_customer = hca.cust_account_id AND arcash.cash_receipt_id = crh.cash_receipt_id AND crh.current_record_flag = 'Y' AND crh.status = rctstslk.lookup_code AND rctstslk.lookup_type = 'RECEIPT_CREATION_STATUS' AND arap.status = alkp.lookup_code AND ALKP.LOOKUP_TYPE = 'PAYMENT_TYPE' AND rtyplk.lookup_code = DECODE((oklcash.receipt_type),'ADV','Y','N') AND rtyplk.lookup_type = 'OKL_YES_NO' AND NOT EXISTS (SELECT 'X' FROM ar_Activity_Details arl WHERE arl.Source_Id = aRap.Receivable_Application_Id AND arl.Source_Table = 'RA' ) UNION ALL SELECT DISTINCT NULL RECEIVABLE_APPLICATION_ID, NULL TRANSACTION_TYPE, NULL INVOICE_NUMBER, NULL INVOICE_DATE, NULL TRANSACTION_AMOUNT, NULL AMOUNT_APPLIED, NULL AMOUNT_REMAINING, arm.NAME PAYMENT_METHOD, arcash.receipt_number RECEIPT_NUMBER, arcash.amount RECEIPT_AMOUNT, arcash.receipt_date RECEIPT_DATE, khr.Contract_Number CONTRACT_NUMBER, NULL DUE_DATE, null PAYMENT_APPLICATION_DATE, null APPLICATION_STATUS, null LINE_IDENTIFIER, arcash.currency_code RECEIPT_CURRENCY_CODE, null INVOICE_CURRENCY_CODE, arcash.receipt_method_id RECEIPT_METHOD_ID, ArcAsh.Cash_Receipt_Id CASH_RECEIPT_ID, null INVOICE_ID, khr.id CONTRACT_ID, arcash.pay_from_customer CUST_ACCOUNT_ID, 'ADV' RECEIPT_TYPE_CODE, arcash.org_id ORG_ID, hca.party_id CUSTOMER_ID, crh.status RECEIPT_STATUS_CODE, rctstslk.meaning RECEIPT_STATUS, null INVOICE_LINE_ID, flk.meaning ADVANCED_YN, hca.account_number CUSTOMER_ACCOUNT_NUMBER FROM ar_Cash_Receipts_All ArcAsh , ar_Payment_Schedules_All Pay_sch, ar_Receipt_Methods Arm , okc_k_headers_all_b khr , okl_trx_csh_rcpt_all_b oklcash , hz_cust_accounts hca , ar_Cash_receipt_history_all crh , ar_lookups rctstslk , OKL_TXL_RCPT_APPS_all_B oklappl , fnd_lookups flk , okl_xmlp_params xmlp WHERE xmlp.batch_id = :BATCH_ID AND arm.receipt_method_id = xmlp.param_value AND arm.receipt_method_id = arcash.receipt_method_id AND arcash.cash_receipt_id = oklcash.cash_receipt_id AND oklcash.receipt_type = 'ADV' AND arcash.pay_from_customer = hca.cust_account_id AND arcash.cash_receipt_id = crh.cash_receipt_id AND crh.current_record_flag = 'Y' AND crh.status = rctstslk.lookup_code AND rctstslk.lookup_type = 'RECEIPT_CREATION_STATUS' AND oklcash.id = oklappl.rct_id_details AND oklappl.khr_id = khr.id AND flk.lookup_code = 'Y' AND flk.lookup_type ='OKL_YES_NO' AND (SELECT SUM(amount_applied) FROM ar_receivable_applications_all ra WHERE arcash.cash_receipt_id = ra.cash_receipt_id AND ra.status = 'ACC') = arcash.amount |
