R12 AR Month End Close and Reconciliation

As requested by some of reader , Here are steps and checklist for R12 AR Month End Close and Reconciliation.

1. Complete All Transactions for the Period Being Closed

2. Complete and review your unapplied receipts

3. Reconcile Receipts to Bank Statement Activity for the Period

4. Change period status to Close Pending

Navigate ..Control>Accounting>Open/Close Periods

Donot get confused with various status of periods:

• Closed: Journal entry, posting, and transaction entry are not allowed unless the accounting period is reopened. Receivables verifies that there are no unposted items in this period. Receivables does not let you close a period that contains unposted items.
• Close Pending: Similar to Closed, but does not validate for Unposted items. Journal entry, posting, and transaction entry are not allowed unless the accounting period is reopened.
• Future: This period is not yet open, but you can enter transactions in this period. However, you cannot post in this period until you open it.
• Not Opened: This period has never been opened and journal entry and posting are not allowed.
• Open: Journal entry and posting are allowed.

5.Create accounting

Due to the introduction of the Subledger Accounting Architecture in Release 12 this step is new, as it enables you to create accounting for your completed transactions, which will be feed into General Ledger.Check it out old Notes (here, here, here )for SLA Process.

6.Review Unposted Items Report

You can review the Unposted Items Report to determine if any exceptions have been encountered, that will need to be resolved, to ensure all accounting has been successful for all transactions.

7.Reconcile Transaction Activity for the Period (Transaction & Receipt)

• Run Journal Entries Report

Transaction Register Total for Postable Items = Sales Journal by GL Account for the Receivable Account Type (Total DR–Total CR)

• Receipt Register
• Receipt Journals Report

In theory this step is unchanged between Release 11i and Release 12. It’s just important to note that you should not be trying to reconcile distributions on transactions to account balances, as these are not necessarily the final accounting for these transactions.

1.Check that Receivables Receipts Balance by running the Receipt Journal report and the Receipt Register for the same GL Date range.

2. Use the Receipt Journal to View information about Receipts that appear in your Journal Entries report. Use the Receipt Register to Review a list of receipts for the date range that you specify. The total of the Receipt Journal should equal the total of all receipts in the Receipt Register. These reports display information about both Invoice–Related and Miscellaneous Receipts.

8.Reconcile outstanding customer balances

• Aging Report (last period)
• Transaction Register
• Adjustments Register
• Invoice Exceptions
• Applied Receipts Register
• Unapplied Receipts Register
• Aging Report (this period)

Most of these reports haven’t changed between Release 11i and Release 12, except that they look at the SLA tables instead of the AR tables.

If you are coming from 11i to R12, you should note that there is change in Aging Repots(Aging - 7 Buckets ) Behaviour .

Few High Points of Changes are:[Adpoted User Guide]

• In Release11i, the Aging Reports pull the data directly from the ar_payment_schedules_all table while in Release 12 the data is pulled from the SLA tables after when Create Accounting is run.
• In release 11i, all the transactions used to appear on all the Aging Reports.
  • The transactions are classified into three sections, namely:
    1. Those transactions, which were accounted for in GL through the GL interface program.
    2. Those transactions, for which accounting could not be generated because they were created with the flag “Post to GL” as un-checked.
    3. Those transactions, which were created with transaction type having the flag “Post to GL” as checked but were however, not interfaced to GL.

9.Post Receivables transactions in General Ledger

10.Reconciling AR and GL Balances

The following is a list of the Critical Reports required for Reconciliation between AR and GL

• Journal Entries Report (AR)
• Sales Journal by GL Account Report (AR)
• Receipt Journal Report (AR)
• Account Analysis Subledger Detail-180 Char (GL)
• Third Party Balance Report (New to R12)
• AR Reconciliation Report (AR)

The Total Activity in a period is calculated as follows:

Transaction Register for the Period
(-) Applied Receipts Register for the Period
(-) Un-Applied Receipts Register for the Period
(+) Adjustments Register for the Period
(-) Invoice Exceptions for the Period
(+) Rounding Differences for the Period
(+) Credit Memo gain/loss for the Period

11.Close AR Period

• Subledger Close Exception Period

From Receivables Responsibilities.
Navigate to Control --> Requests --> Run
Subledger Period Close Exceptions Report

• Change Period status to Closed
• Open next Period

Thats all about R12 Month End Close and Reconciliation of AR.

References:

http://www.oracleappshub.com/release12/r12/r12-ar-month-end-close-and-reconciliation/

Query to list Customer (Party), Account, Site data

As my client was going through its customer related data clean up in its Oracle R12 applications, I wrote the following query to retrieve all the information that were needed to provide a list of its customer (party), account, site, address, collector, and other related information.

--=============================================================================

-- Filename   : Customer Data Query

-- Programmer : Abul Mohsin

-- Date       : 29-Oct-2012

-- Language   : SQL

-- Module     : AR

-- Purpose    : Lists Customer (Party), Customer Account, and Customer Site

--              related information.

--=============================================================================

SELECT

       ----------------------------------------------------------

       -- Party Information

       ----------------------------------------------------------

       hp.party_number                      "Registry ID",

       hp.party_name                        "Party Name",

       hp.party_type                        "Party Type",

       DECODE(hp.status,

              'A', 'Active',

              'I', 'Inactive',

              hp.status)                    "Party Status",

       ----------------------------------------------------------

       -- Account Information

       ----------------------------------------------------------

       hca.account_number                   "Account Number",

       DECODE(hca.status,

              'A', 'Active',

              'I', 'Inactive',

              hca.status)                   "Account Status",

       hca.account_name                     "Account Description",

       hca.customer_class_code              "Classification",

       DECODE(hca.customer_type,

              'R', 'External',

              'I', 'Internal',

              hca.customer_type)            "Account Type",

       ----------------------------------------------------------

       -- Site Information

       ----------------------------------------------------------

       hps.party_site_number                "Customer Site Number",

       DECODE(hcas.status,

              'A', 'Active',

              'Inactive')                   "Site Status",

       DECODE(hcas.bill_to_flag,

              'P', 'Primary',

              'Y', 'Yes',

              hcas.bill_to_flag)            "Bill To Flag",

       DECODE(hcas.ship_to_flag,

              'P', 'Primary',

              'Y', 'Yes',

              hcas.ship_to_flag)            "Ship To Flag",

       hcas.cust_acct_site_id               "Customer Acct Site ID",

       ----------------------------------------------------------

       -- Address Information

       ----------------------------------------------------------

       hl.address1                          "Address1",

       hl.address2                          "Address2",

       hl.address3                          "Address3",

       hl.address4                          "Address4",

       hl.city                              "City",

       hl.state                             "State",

       hl.postal_code                       "Zip Code",

       ter.name                             "Territory",

       ----------------------------------------------------------

       -- DFF Information (specific to client)

       ----------------------------------------------------------

       hcas.attribute4                      "SMG Key",

       hcas.attribute8                      "GLN Key",

       hca.attribute3                       "Credit Approval Date",

       hca.attribute7                       "Credit Approved By",

       hca.attribute4                       "Acct Opened Date",

       hca.attribute5                       "Credit Collection Status",

       hca.attribute1                       "BPCS Last Trx Date",

       hca.attribute2                       "BPCS Avg Pay Days",

       hca.attribute6                       "BPCS RCM Reference",

       ----------------------------------------------------------

       -- Collector Information

       ----------------------------------------------------------

       col.name                             "Collector Name",

       ----------------------------------------------------------

       -- Account Profile Information

       ----------------------------------------------------------

       hcp.credit_checking                  "Credit Check Flag",

       hcp.credit_hold                      "Credit Hold Flag",

       hcpa.auto_rec_min_receipt_amount     "Min Receipt Amount",

       hcpa.overall_credit_limit            "Credit Limit",

       hcpa.trx_credit_limit                "Order Credit Limit",

       ----------------------------------------------------------

       -- Attachment Flag

       ----------------------------------------------------------

       NVL((SELECT 'Y'

              FROM fnd_documents_vl doc,

                   fnd_lobs         blo,

                   fnd_attached_documents att

             WHERE doc.media_id = blo.file_id

               AND doc.document_id = att.document_id

               AND att.entity_name = 'AR_CUSTOMERS'

               AND att.pk1_value   = hca.cust_account_id

               AND ROWNUM = 1), 'N'

       ) "Attachment Flag",

       ----------------------------------------------------------

       -- Party Relationship Flag

       ----------------------------------------------------------

       NVL((SELECT 'Y'

              FROM hz_cust_acct_relate_all hzcar

             WHERE hzcar.cust_account_id = hca.cust_account_id

               AND hzcar.relationship_type = 'ALL'

               AND ROWNUM = 1), 'N'

       ) "Party Relationship Flag",

       ----------------------------------------------------------

       -- Account Relationship Flag

       ----------------------------------------------------------

       NVL((SELECT 'Y'

              FROM hz_cust_acct_relate_all hzcar

             WHERE hzcar.cust_account_id = hca.cust_account_id

               AND ROWNUM = 1), 'N'

       ) "Account Relationship Flag",

       ----------------------------------------------------------

       -- Party Contact Flag

       ----------------------------------------------------------

       NVL((SELECT 'Y'

              FROM hz_parties hp2

             WHERE 1=1

               AND hp2.party_id = hp.party_id

               AND (

                    hp2.url IS NOT NULL OR

                    -- LENGTH(TRIM(hp.email_address)) > 5

                    INSTR(hp2.email_address, '@') > 0 OR

                    hp2.primary_phone_purpose IS NOT NULL

                    )

           ), 'N'

       ) "Party Contact Flag",

       ----------------------------------------------------------

       -- Account Contact Flag

       ----------------------------------------------------------

       NVL((SELECT 'Y'

              FROM hz_contact_points

             WHERE status = 'A'

               AND owner_table_id =

                   (SELECT hcar.party_id

                      FROM hz_cust_account_roles   hcar,

                           ar_contacts_v           acv

                     WHERE hcar.cust_account_id   = hca.cust_account_id

                       AND hcar.cust_account_role_id = acv.contact_id

                       AND hcar.cust_acct_site_id IS NULL  -- look for account level only

                       AND ROWNUM = 1 -- add this row to show inactive sites (i.e. with no site id)

                    )

               AND ROWNUM = 1), 'N'

       ) "Account Contact Flag",

       ----------------------------------------------------------

       -- Site Contact Flag

       ----------------------------------------------------------

       NVL((SELECT 'Y'

              FROM hz_contact_points

             WHERE status = 'A'

               AND owner_table_id =

                   (

                      SELECT hcar.party_id

                        FROM hz_cust_account_roles   hcar,

                             ar_contacts_v           acv

                       WHERE hcar.cust_acct_site_id     =  hcas.cust_acct_site_id

                         AND hcar.cust_account_role_id  =  acv.contact_id

                         AND ROWNUM = 1  -- add this row to show inactive sites (i.e. with no site id)

                   )

               AND ROWNUM = 1), 'N'      -- any contact (email, phone, fax) would suffice this condition

       ) "Site Contact Flag"

  FROM

       hz_parties              hp,

       hz_party_sites          hps,

       hz_cust_accounts_all    hca,

       hz_cust_acct_sites_all  hcas,

       hz_customer_profiles    hcp,

       hz_cust_profile_amts    hcpa,

       hz_locations            hl,

       ra_territories          ter,

       ar_collectors           col

 WHERE

       1=1

   AND hp.party_id            =  hca.party_id

   AND hca.cust_account_id    =  hcas.cust_account_id(+)

   AND hps.party_site_id(+)   =  hcas.party_site_id

   AND hp.party_id            =  hcp.party_id  

   AND hca.cust_account_id    =  hcp.cust_account_id

   AND hps.location_id        =  hl.location_id(+)

   AND col.collector_id       =  hcp.collector_id

   AND hcas.territory_id      =  ter.territory_id(+)

   AND hcp.cust_account_profile_id = hcpa.cust_account_profile_id

   ----

   AND hp.party_type          = 'ORGANIZATION'    -- only ORGANIZATION Party types

   AND hp.status              = 'A'               -- only Active Parties/Customers

   ----

   -- following conditions are for testing purpose only

   -- comment/uncomment as needed

   ----

   -- AND hp.party_number        = 11530

   -- AND hca.account_number     = 32253 --32396 --31753 --32253 --31038

 ORDER BY TO_NUMBER(hp.party_number), hp.party_name, hca.account_number;

References:

http://appsr12help.blogspot.com/2012/12/query-to-list-customer-party-account.html