3 Replies Latest reply: Sep 10, 2013 4:06 AM by Octopus Rex RSS

    AR - On-Account Receipts

    Adit102

      Hi,

       

      I am trying to extract OnAccount Receipts using below query. But I noticed that in some cases there are multiple cash_receipt_id for a single receipt_number. Also there are multiple records for one cash_receipt_id in AR_RECEIVABLE_APPLICATIONS_ALL table. This is because there are different amount_applied values with negative and positive amount. I am not sure which one to use. This is returning duplicate records. I also tried removing the outer joins, but they are needed to get required data. The query I used is:

       

      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

                 ,aps.customer_id

                 ,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_ALL 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_ID(+) = hca.CUST_ACCOUNT_ID

          AND ieb.ext_bank_account_id(+) = hca.CUST_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 = 'ACC'

         AND acr.status = 'APP'

        --AND aps.CLASS = 'PMT'

        --AND aps.status = 'OP'

        --AND site.site_use_code = 'BILL_TO' 

        --AND NVL (site.status, 'A') = 'A'

       

      Please let me know what other validations do I need to get OnAccount Receipts.

       

      Thanks in advance,

      Aditya

        • 1. Re: AR - On-Account Receipts
          Octopus Rex

          Hi,

           

          This query works fine for me:

           

          SELECT CR.CASH_RECEIPT_ID,

                      CR.RECEIPT_NUMBER,

                      CR.RECEIPT_DATE,

                      CR.CURRENCY_CODE,

                      DECODE ( CR.TYPE, 'MISC', NULL, NVL (SUM (DECODE (RA.STATUS, 'ACC', NVL (RA.AMOUNT_APPLIED, 0), 0)), 0)) ON_ACCOUNT_AMOUNT

                   FROM AR_RECEIVABLE_APPLICATIONS_ALL RA,

                      AR_CASH_RECEIPTS_ALL CR,

                      AR_RECEIPT_METHODS RM

                WHERE RA.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID

                      AND CR.RECEIPT_METHOD_ID = RM.RECEIPT_METHOD_ID

                      AND CR.ORG_ID = <org_id>

                GROUP BY CR.CASH_RECEIPT_ID,

                      CR.RECEIPT_DATE,

                      CR.RECEIPT_NUMBER,

                      RM.NAME,

                      CR.CURRENCY_CODE,

                      CR.TYPE order by receipt_date desc

           

          Let me know if it worked.

           

          Octavio

          • 2. Re: AR - On-Account Receipts
            Adit102

            Thank you Octavio for the script, but it still does return the records I need.

            Is there any validation specifically for any field from any other table, like AR_PAYMENT_SCHEDULES_ALL? Do I need to check if any column is NULL or any particular value?

            Also is it normal that there are multiple receipt id for receipt number in front end as well as in back end?

             

            Thanks,

            Aditya

            • 3. Re: AR - On-Account Receipts
              Octopus Rex

              Hi Aditya,

               

              You will have multiple CASH_RECEIPT_ID on table AR_RECEIVABLE_APPLICATIONS_ALL, perhaps you should only consider only the records with STATUS = 'ACC'...

               

               

              Octavio