2 Replies Latest reply: Sep 3, 2013 2:58 AM by Adit102 RSS

    AR - Query not returning On-Account Receipts

    Adit102

      Hi,

       

      I am working on AR Receipts and I have to migrate OnAccount Receipts. I am using AR_RECEIPT_API_PUB.APPLY_ON_ACCOUNT API for this purpose. But, before doing this I am extracting the data from legacy system. I am using below query, but I am unable to get any OnAccount Receipts from this query.

       

      SELECT acr.currency_code

                 ,acr.exchange_rate_type           -- p_exchange_rate_type null

                 ,acr.exchange_rate                   -- p_exchange_rate null

                 ,acr.exchange_date                  --p_exchange_rate_date

                 ,acr.amount                             --p_amount

                 ,acr.factor_discount_amount     --p_factor_discount_amount

                 ,acr.receipt_number                 --p_receipt_number

                 ,acr.creation_date                    --p_receipt_date

                 ,ara.gl_date                             --p_gl_date

                 ,ara.status

                 ,ara.amount_applied

                 ,ara.gl_date gl_appl

                 ,hca.cust_account_id              --p_customer_id

                 ,hp.party_name                      --p_customer_name

                 ,hca.account_number             --p_customer_number

                 ,iao.ext_bank_account_id       --p_customer_bank_account_id

                 ,ieb.bank_account_num         --p_customer_bank_account_num

                 ,site.location                         --p_location

                 ,acr.customer_site_use_id     --p_customer_site_use_id

                 ,acr.customer_receipt_reference   --p_customer_receipt_reference

                 ,acr.override_remit_account_flag   --p_override_remit_account_flag

                 ,acr.remittance_bank_account_id  --p_remittance_bank_account_id

                 --,p_remittance_bank_account_num

                 --,p_remittance_bank_account_name

                 ,acr.deposit_date                     --p_deposit_date

                 ,arm.receipt_method_id            --p_receipt_method_id

                 ,arm.name                               --p_receipt_method_name

                 ,ara.ussgl_transaction_code      --p_ussgl_transaction_code

                 --,p_org_id

      FROM AR_CASH_RECEIPTS_ALL acr

               ,AR_RECEIVABLE_APPLICATIONS ara

               ,AR_PAYMENT_SCHEDULES_ALL aps

               ,HZ_CUST_ACCOUNTS hca

               ,HZ_PARTIES hp

               ,IBY_ACCOUNT_OWNERS iao

               ,IBY_EXT_BANK_ACCOUNTS ieb

               ,HZ_CUST_SITE_USES_ALL site

               ,AR_RECEIPT_METHODS arm

      WHERE acr.cash_receipt_id = ara.cash_receipt_id

           AND acr.cash_receipt_id = aps.cash_receipt_id

           AND aps.customer_id = hca.cust_account_id

           AND hca.party_id = hp.party_id

           AND iao.account_owner_party_id = hp.party_id

           AND ieb.ext_bank_account_id = iao.ext_bank_account_id

           AND acr.customer_site_use_id = site.site_use_id

           AND arm.receipt_method_id = acr.receipt_method_id

           AND acr.org_id = 143

           AND ara.status in ('UNAPP', 'ACC');

       

       

      If tables IBY_ACCOUNT_OWNERS and IBY_EXT_BANK_ACCOUNTS are commented along with columns and joins then the query returns OnAccount Receipts.

      Am I missing any join in particular to above tables? Or do I have to change any existing joins?

       

      Thanks in advance,

      Aditya