VIEW: APPS.OKL_CS_TRX_PAYMENTS_UV

Source

View Text - Preformatted

SELECT aRap.receivable_application_id, cUst_trx_Lines.InterFace_Line_Attribute9 Stream_Type, Nvl(cUst_trx_Lines.InterFace_Line_Attribute1,Pay_sch.trx_Number) Invoice_Number, Pay_sch.trx_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')) original_amount, (arl.Amount + arl.tax ) applied_amount, ( 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, arcash.receipt_number, arcash.amount, (SELECT nvl(sum(uNapp_Line.Amount_Applied),0) FROM ar_Receivable_Applications_All uNapp_Line WHERE uNapp_Line.Cash_Receipt_Id = ARCASH.CASH_RECEIPT_ID AND uNapp_Line.Status = 'UNAPP' AND uNapp_Line.Application_Type = 'CASH') uNapPlied_Amount, (SELECT nvl(sum(acc_Line.Amount_Applied),0) FROM ar_Receivable_Applications_All acc_Line WHERE acc_Line.Cash_Receipt_Id = ARCASH.CASH_RECEIPT_ID AND acc_Line.Status = 'ACC' AND acc_Line.Application_Type = 'CASH') onAccount_Amount, arcash.receipt_date, cUst_trx_Lines.InterFace_Line_Attribute6 Contract_Number, Pay_sch.Due_Date, arap.apply_date, alkp.meaning status, Nvl2(cUst_trx_Lines.InterFace_Line_Attribute1, (pay_sch.trx_number),To_char(cUst_trx_Lines.Line_Number)) Line_Number, arcash.currency_code, pay_sch.invoice_currency_code, arcash.receipt_method_id, ArcAsh.Cash_Receipt_Id, pay_sch.customer_trx_id, khr.id, arcash.pay_from_customer, DECODE((oklcash.receipt_type),'ADV','ADV','REG') receipt_type, arcash.org_id, hca.party_id, crh.status, rctstslk.meaning, cust_trx_lines.customer_trx_line_id, rtyplk.meaning advanced_yn, hca.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 WHERE 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 in ( 'OKL_CONTRACTS','OKL_MANUAL') 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, cUst_trx.InterFace_header_Attribute9 Stream_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 LINE_TYPE = 'LINE' AND ROWNUM < 2),Pay_sch.trx_Number) Invoice_Number, Pay_sch.trx_Date, pay_sch.amount_due_original original_amount, arap.amount_Applied, pay_sch.amount_due_remaining, arm.NAME, arcash.receipt_number, arcash.amount, (SELECT nvl(sum(uNapp_Line.Amount_Applied),0) FROM ar_Receivable_Applications_All uNapp_Line WHERE uNapp_Line.Cash_Receipt_Id = ARCASH.CASH_RECEIPT_ID AND uNapp_Line.Status = 'UNAPP' AND uNapp_Line.Application_Type = 'CASH') uNapPlied_Amount, (SELECT nvl(sum(acc_Line.Amount_Applied),0) FROM ar_Receivable_Applications_All acc_Line WHERE acc_Line.Cash_Receipt_Id = ARCASH.CASH_RECEIPT_ID AND acc_Line.Status = 'ACC' AND acc_Line.Application_Type = 'CASH') onAccount_Amount, arcash.receipt_date, cUst_trx.InterFace_header_Attribute6 Contract_Number, Pay_sch.Due_Date, arap.apply_date, alkp.meaning 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 LINE_TYPE = 'LINE' AND ROWNUM < 2) ,pay_sch.trx_number, NULL) Line_Number, arcash.currency_code, pay_sch.invoice_currency_code, arcash.receipt_method_id, ArcAsh.Cash_Receipt_Id, pay_sch.customer_trx_id, khr.id, arcash.pay_from_customer, DECODE((oklcash.receipt_type),'ADV','ADV','REG') receipt_type, arcash.org_id, hca.party_id, crh.status, rctstslk.meaning, NULL invoice_line_id, rtyplk.meaning advanced_yn, hca.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 WHERE 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 in ('OKL_CONTRACTS','OKL_MANUAL') 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 Stream_Type, NULL Invoice_Number, NULL trx_Date, NULL original_amount, NULL applied_amount, NULL amount_remaining, arm.NAME, arcash.receipt_number, arcash.amount, NULL UNAPPLIED_AMOUNT, NULL ONACCOUNT_AMOUNT, arcash.receipt_date, khr.Contract_Number, NULL due_date, null apply_date, null status, null Line_Number, arcash.currency_code, null invoice_currency_code, arcash.receipt_method_id, ArcAsh.Cash_Receipt_Id, null customer_trx_id, khr.id, arcash.pay_from_customer, 'ADV' receipt_type, arcash.org_id, hca.party_id, crh.status, rctstslk.meaning, null customer_trx_line_id, flk.meaning, hca.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 WHERE 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

View Text - HTML Formatted

SELECT ARAP.RECEIVABLE_APPLICATION_ID
, CUST_TRX_LINES.INTERFACE_LINE_ATTRIBUTE9 STREAM_TYPE
, NVL(CUST_TRX_LINES.INTERFACE_LINE_ATTRIBUTE1
, PAY_SCH.TRX_NUMBER) INVOICE_NUMBER
, PAY_SCH.TRX_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')) ORIGINAL_AMOUNT
, (ARL.AMOUNT + ARL.TAX ) APPLIED_AMOUNT
, ( 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
, ARCASH.RECEIPT_NUMBER
, ARCASH.AMOUNT
, (SELECT NVL(SUM(UNAPP_LINE.AMOUNT_APPLIED)
, 0)
FROM AR_RECEIVABLE_APPLICATIONS_ALL UNAPP_LINE
WHERE UNAPP_LINE.CASH_RECEIPT_ID = ARCASH.CASH_RECEIPT_ID
AND UNAPP_LINE.STATUS = 'UNAPP'
AND UNAPP_LINE.APPLICATION_TYPE = 'CASH') UNAPPLIED_AMOUNT
, (SELECT NVL(SUM(ACC_LINE.AMOUNT_APPLIED)
, 0)
FROM AR_RECEIVABLE_APPLICATIONS_ALL ACC_LINE
WHERE ACC_LINE.CASH_RECEIPT_ID = ARCASH.CASH_RECEIPT_ID
AND ACC_LINE.STATUS = 'ACC'
AND ACC_LINE.APPLICATION_TYPE = 'CASH') ONACCOUNT_AMOUNT
, ARCASH.RECEIPT_DATE
, CUST_TRX_LINES.INTERFACE_LINE_ATTRIBUTE6 CONTRACT_NUMBER
, PAY_SCH.DUE_DATE
, ARAP.APPLY_DATE
, ALKP.MEANING STATUS
, NVL2(CUST_TRX_LINES.INTERFACE_LINE_ATTRIBUTE1
, (PAY_SCH.TRX_NUMBER)
, TO_CHAR(CUST_TRX_LINES.LINE_NUMBER)) LINE_NUMBER
, ARCASH.CURRENCY_CODE
, PAY_SCH.INVOICE_CURRENCY_CODE
, ARCASH.RECEIPT_METHOD_ID
, ARCASH.CASH_RECEIPT_ID
, PAY_SCH.CUSTOMER_TRX_ID
, KHR.ID
, ARCASH.PAY_FROM_CUSTOMER
, DECODE((OKLCASH.RECEIPT_TYPE)
, 'ADV'
, 'ADV'
, 'REG') RECEIPT_TYPE
, ARCASH.ORG_ID
, HCA.PARTY_ID
, CRH.STATUS
, RCTSTSLK.MEANING
, CUST_TRX_LINES.CUSTOMER_TRX_LINE_ID
, RTYPLK.MEANING ADVANCED_YN
, HCA.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
WHERE 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 IN ( 'OKL_CONTRACTS'
, 'OKL_MANUAL')
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
, CUST_TRX.INTERFACE_HEADER_ATTRIBUTE9 STREAM_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 LINE_TYPE = 'LINE'
AND ROWNUM < 2)
, PAY_SCH.TRX_NUMBER) INVOICE_NUMBER
, PAY_SCH.TRX_DATE
, PAY_SCH.AMOUNT_DUE_ORIGINAL ORIGINAL_AMOUNT
, ARAP.AMOUNT_APPLIED
, PAY_SCH.AMOUNT_DUE_REMAINING
, ARM.NAME
, ARCASH.RECEIPT_NUMBER
, ARCASH.AMOUNT
, (SELECT NVL(SUM(UNAPP_LINE.AMOUNT_APPLIED)
, 0)
FROM AR_RECEIVABLE_APPLICATIONS_ALL UNAPP_LINE
WHERE UNAPP_LINE.CASH_RECEIPT_ID = ARCASH.CASH_RECEIPT_ID
AND UNAPP_LINE.STATUS = 'UNAPP'
AND UNAPP_LINE.APPLICATION_TYPE = 'CASH') UNAPPLIED_AMOUNT
, (SELECT NVL(SUM(ACC_LINE.AMOUNT_APPLIED)
, 0)
FROM AR_RECEIVABLE_APPLICATIONS_ALL ACC_LINE
WHERE ACC_LINE.CASH_RECEIPT_ID = ARCASH.CASH_RECEIPT_ID
AND ACC_LINE.STATUS = 'ACC'
AND ACC_LINE.APPLICATION_TYPE = 'CASH') ONACCOUNT_AMOUNT
, ARCASH.RECEIPT_DATE
, CUST_TRX.INTERFACE_HEADER_ATTRIBUTE6 CONTRACT_NUMBER
, PAY_SCH.DUE_DATE
, ARAP.APPLY_DATE
, ALKP.MEANING 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 LINE_TYPE = 'LINE'
AND ROWNUM < 2)
, PAY_SCH.TRX_NUMBER
, NULL) LINE_NUMBER
, ARCASH.CURRENCY_CODE
, PAY_SCH.INVOICE_CURRENCY_CODE
, ARCASH.RECEIPT_METHOD_ID
, ARCASH.CASH_RECEIPT_ID
, PAY_SCH.CUSTOMER_TRX_ID
, KHR.ID
, ARCASH.PAY_FROM_CUSTOMER
, DECODE((OKLCASH.RECEIPT_TYPE)
, 'ADV'
, 'ADV'
, 'REG') RECEIPT_TYPE
, ARCASH.ORG_ID
, HCA.PARTY_ID
, CRH.STATUS
, RCTSTSLK.MEANING
, NULL INVOICE_LINE_ID
, RTYPLK.MEANING ADVANCED_YN
, HCA.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
WHERE 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 IN ('OKL_CONTRACTS'
, 'OKL_MANUAL')
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 STREAM_TYPE
, NULL INVOICE_NUMBER
, NULL TRX_DATE
, NULL ORIGINAL_AMOUNT
, NULL APPLIED_AMOUNT
, NULL AMOUNT_REMAINING
, ARM.NAME
, ARCASH.RECEIPT_NUMBER
, ARCASH.AMOUNT
, NULL UNAPPLIED_AMOUNT
, NULL ONACCOUNT_AMOUNT
, ARCASH.RECEIPT_DATE
, KHR.CONTRACT_NUMBER
, NULL DUE_DATE
, NULL APPLY_DATE
, NULL STATUS
, NULL LINE_NUMBER
, ARCASH.CURRENCY_CODE
, NULL INVOICE_CURRENCY_CODE
, ARCASH.RECEIPT_METHOD_ID
, ARCASH.CASH_RECEIPT_ID
, NULL CUSTOMER_TRX_ID
, KHR.ID
, ARCASH.PAY_FROM_CUSTOMER
, 'ADV' RECEIPT_TYPE
, ARCASH.ORG_ID
, HCA.PARTY_ID
, CRH.STATUS
, RCTSTSLK.MEANING
, NULL CUSTOMER_TRX_LINE_ID
, FLK.MEANING
, HCA.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
WHERE 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