OKL Receipt Inquiry

➜ Back to Library
Description
Categories: BI Publisher
Application: Lease and Finance Management
Source:
Short Name: OKLRECPTINQ
DB package:
Run OKL Receipt Inquiry and other Oracle EBS reports with Blitz Report™ on our demo environment
  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
Download
Related Blitz Reports
OKL Lease Advance Monies GL Account Analysis GL Account Analysis (Distributions) JA India - Debtors Ledger Report- Not Supported: Reserved For Future Use AR Customer Statement Avalara VAT listing RK: AR Transactions and Lines AR Aging - 7 Buckets - By Salesperson/Agent AR Open Balances Revaluation
Blitz Report™

Blitz Report™ provides multiple benefits: