Reconciliation
Stephen wrote,
Hi all,
I am in the process of trying to setup a reconciliation procedure for AR at
month end. I would like to know what the list is doing for their month end
reconciliation of AR to GL, and if there are specific issues we should be
looking at.
Date: Mon, 5 Apr 1999 10:31:26 -0500
From: "Puchniak, Stan" Stan_Puchniak@standardaero.ca
Subject: RE:AR month end Reconciliation
The most important issue to address is the closing of AR and journal import
to GL. Also, the AR reports are extremely date sensitive, so the scheduling
of these reports to run is imperative. A report ran today, for yesterday,
may provide different information than a report ran yesterday. There is a
very interesting article from last spring's OAUG conference in San Diego
that may be of assistance. In general, the process goes like this:
Opening Aged Trial Balance
Add: Transaction Register (use GL dates from the beginning of the fiscal
period to the end)
Add/Subtract: Adjustment Register (use GL dates from the beginning of the
fiscal period to the end)
Subtract: Applied Receipts Register (use GL dates from the beginning of the
fiscal period to the end)
Subtract: Unapplied Receipts Register (use GL dates from the implementation
date to the end of the fiscal period)
This should reconcile to your ending Aged Trial Balance
This is a good process to go through on a new implementation to ensure that
everything is processing properly. Our initial conversion was mid-1996 and
we no longer follow this practice. We deal in multiple currencies (CAD, USD,
NLG, and GBP) and found that the "Aged Trial Balance - 7 Bucket - By
Account" was sufficient for reconciliations. This report lists the
outstanding balance by receivable accounting flexfield. We run this report
in our functional currency (USD). We find that the only reconciling items
between the AR and GL are non-Receivable entries to the GL (for example,
sales accrual journal entries). The process described above is a good
validation tool. If any problems occur, it is likely due to the accounting
flexfield defined for your on account, unapplied or unidentified receipts
from your bank(s). Since these receipts affect the balances of the ATB's, I
would recommend defining a GL control account in your accounts receivable
range.
I hope this helps,
Stan Puchniak
Date: Tue, 06 Apr 1999 09:03:22 -0400
From: "Marni Baggett" mbaggett@brk.photronics.com
Subject: 7) RE:AR month end Reconciliation
to stephen (person with original question):
if you'd like, I can email you the AR reconciliation spreadsheet we use at our
company, along with the month-end closing excerpt from our internal training
manual. please simply email me.
marni baggett
ar team lead...10.7SC and now 11.02
Photronics, Inc.
mbaggett@brk.photronics.com
AR statements and Payments
Date: Tue, 15 Jun 1999 18:10:51 -0400
From: "Branning, Thom" BranningT@usa.redcross.org
Subject: AR statements and Payments
We currently process payments made for the previous month through
the automated lock box interface the first week on the next month -- i.e.
process end of may payments the first week of June. These payments are not
appearing on our AR statements. The transaction date is for the previous
month -- i.e. May, but the apply date shows the current month -- i.e. June.
We want to have these transactions/payments show on the statement for May --
i.e. the month they came into the lock box. Is there a way to have this
occur ?
Thanks,
Thom Branning
EIS Systems Manager, American Red Cross
Date: Wed, 16 Jun 1999 08:50:52 +1000
From: "Bevin Watson" bevin.watson@abs.gov.au
Subject: Re: AR statements and Payments
Generally, finance systems like to tell "the truth". However, there may be a
couple of options for fooling it.
1) Try keeping May open and setting the date when you run lockbox to May. I
don't think this will fix the apply date, but worth try.
2) Changing all your processes and running it on the last day of the month.
3) Changing your statement dates (something like setup statement cycles?) , so
instead of having a statement on 31 May, have it on 5 June, 5 July, 5 August
etc.
Hope this helps. I'm afraid I think 2) is the only workable option.
Bevin Watson
Financial Systems Administrator
Australian Bureau of Statistics
How to unreconcile reversed receipt ?
Date: Sun, 27 Jun 1999 18:54:00 +0200
From: Suzie Roth suzie_roth@stcl.scitex.com
Subject: AR/CE: How can I unreconcile a receipt which was reversed
Hi,
We are using Rel 11. My user created a receipt in the AR with status
"Remitted" and after a while
he cleared it against a bank statement line (from the CE module). Since he
made a mistake, he reversed the
receipt without unreconcile it before and entered a new and correct receipt.
The problem is that this bank
statement line can not be unreconcile and reconciled again against the
correct receipt.
Does anyone have an idea how to fix this problem?
Thanks in advance,
-- Suzie Roth
MIS (Ext. 7151)
From: Teresa Reynolds teresa.reynolds@ey.com
Subject: Re: AR/CE: How can I unreconcile a receipt which was reversed
Date: Mon, 28 Jun 1999 14:37:01 -0400
You should be able to unreconcile the receipt. You need to press the
"Reconcile" button in the Statement Lines screen? This will unreconcile
the receipt.
Date: Tue, 29 Jun 1999 8:04:37 -0400
From: Teresa Reynolds teresa.reynolds@ey.com
Subject: AR/CE: How can I unreconcile a receipt which was reversed
It is not a setup problem, you just didn't do the transaction in its
natural sequence. As you know, you typically wouldn't reconcile something
that did not clear the bank. I would enter another statement line and
reconcile it to the new and correct receipt. However I would make sure the
accounting is correct for the receipt that you reversed..... do you use a
remittance account? If so, I would make sure that the accounting entry
was done correctly when you reversed the receipt......it should be a debit
to AR and and a credit to Cash (it may have done a credit to the
Remittance account). Since you did it out of sequence I am not sure
exactly what it will do.
I would do an attachment to the receipt you reconciled in error that
explains the situation so that you have some record of events.
Date: Tue, 29 Jun 1999 15:36:24 +0200
From: Suzie Roth suzie_roth@stcl.scitex.com
Subject: RE: AR/CE: How can I unreconcile a receipt which was reversed
Thanks. This is close to what we did.
In the bank statement, I created a NSF line and reconcile it to the reversed
receipt
and a new Receipt line and reconciled it to the correct receipt. At the
bottom line, the bank balance was
not changed and everything was correctly reconciled.
I check the reconciled reverse receipt, and the accounting is ok. It
credited the Cash account and not the Remittance
account.
Thanks,
-- Suzie Roth
MIS (Ext. 7151)
Report for only open items
Date: Thu, 8 Jul 1999 10:14:05 -0700
From: "Kennedy, Bob:" bkenned1@tosco.com
Subject: AR Statement Question
We recently upgraded to 10.7. Currently our statements print all
activity for a month. Our customer are asking for open items only, due
to a high volume of activity. Is there any way to do this within
Oracle AR?
Thank,
Bob Kennedy
Date: Thu, 8 Jul 1999 10:29:00 -0700
From: Tina.Thousand@weac.com
Subject: Re: AR Statement Question
Bob - in order to remove all closed activity from printing on your
statements,
you can either customize your statement printing to remove closed items, or
you
can run statements twice. The first statement run would print 0 copies, the
second run would be for the same parameters, but would actually print hard
copy
statements, but would not include closed items since they will have already
been
printed on a previous statement run.
Tina Thousand
Ernst & Young, LLP
christina.thousand@ey.com
How to get all Receipts associated with Transactions converted from 10.7 to 11
Date: Tue, 27 Jul 1999 09:20:12 -0700
From: "Orth, Tom (torth)" torth@sequent.com
Subject: AR: Receipts (and a thanks)
First, thanks to Sunny and Andrea Storer for help on Autoinvoice.
I'm trying to get all of the receipts associated with transactions that
I've converted from rel 10.7 to rel 11. However, when I tie the
ar_cash_receipts_all table with ar_receivables_applications_all,
and ra_customer_trx_all, I get only a few records. How do I get all
of the receipts that were applied against the transactions if they
aren't connected in this way?
Thanks,
Tom Orth
Date: Tue, 27 Jul 1999 19:36:17 +0200
From: Suzie Roth suzie_roth@stcl.scitex.com
Subject: RE: Receipts (and a thanks)
Your logic is correct. You should join between these three tables.
The join condition should be:
ar_cash_receipts_all.cash_receipt_id=ar_receivable_applications_all.cash_rec
eipt_id
ar_receivable_applications_all.applied_customer_trx_id
=ra_customer_trx_all.customer_trx_id.
Maybe you joined the tables using the
ar_receivable_applications_all.customer_trx_id field.
If this is the case, it is wrong.
-- Suzie Roth
MIS (Ext. 7151)
SQL Script for AR-GL reconciliation
Date: Thu, 12 Aug 1999 13:53:16 -0400
From: "Phillips, Brant" phillib@mps.bellhowell.com
To: "'OAUG Distributions'" oraapps-l@cpa.qc.ca
Subject: AR -- GL Reconciliation
Does anyone have a SQL script to verify that all invoice transactions have
been created within GL_JE_LINES, GL_JE_HEADERS and GL_JE_BATCHES. I am
currently trying to tie back through the GL_IMPORT_REFERENCES table back to
RA_CUSTOMER_TRX_ALL & RA_CUSTOMER_TRX_LINES_ALL and not having much luck. I
started with Invoice types mapping to the Reference4 attribute within
GL_IMPORT_REFERENCES but my query takes forever. Here is the script that I
am trying to run:
select rcta.trx_number, rcta.trx_date
from ra_customer_trx_all rcta
where rcta.trx_number not in (select distinct gir.reference_4
from gl_import_references gir
where gir.reference_4 = rcta.trx_number and
gir.reference_10 = 'RA_CUST_TRX_LINE_GL_DIST'
and
rcta.trx_date between TO_DATE('09-MAY-99','DD-MON-YY') AND
TO_DATE('05-JUN-99','DD-MON-YY'));
Table counts:
GL_IMPORT_REFERENCES: 3,507,066 rows
RA_CUSTOMER_TRX_ALL: 151,122 rows
RA_CUSTOMER_TRX_LINES_ALL: 887,488 rows
Our financial folks are having problems identifying transactions that are in
AR but not in the GL and I am trying to programatically trying to give them
the answers that they need. Any and all suggestions are welcome.
Also, any additional information on reconciling Debit Memos, Credit Memos,
Chargebacks, Trade Receipts, Credit Memo Applications, Miscellaneous
Receipts and Adjustments would also be very helpful.
Thanks,
Brant
Brant Phillips
Sr. Programmer Analyst
Bell & Howell Mail & Messaging Technologies
4401 Silicon Drive
Durham NC 27709
E-Mail:Brant.Phillips@mmt.bellhowell.com
Voice Mailbox #4039
Date: Fri, 13 Aug 1999 08:31:18 +0530
From: "Bagavathi Rajan K" rajankb@wipsys.soft.net
Subject: Re: AR -- GL Reconciliation
Hi,
You can run the "Unposted Items Report". It will give all the
unposted transactions in AR during a given period.
Regards,
K.Bagavathi Rajan.
Date: Thu, 12 Aug 1999 20:35:58 PDT
From: "Anil Thalakera" anil_india47@hotmail.com
Subject: Re: AR --- GL Reconciliation
if you want to use the select statement, creating indexes on reference_4 and
reference_10 might help you. I was having similar problem when I was using
attribute columns in so_headers table which is very huge.
Date: Fri, 13 Aug 1999 12:08:19 PDT
From: "prasad surisetty" surisettyp@hotmail.com
Subject: Re: AR --- GL Reconciliation
Is it poosible creating indexes on descriptive Flexfeilds..?
From: Cheryl_Moore@huntsman.com [mailto:Cheryl_Moore@huntsman.com]
Sent: Thursday, August 12, 1999 2:28 PM
To: Phillips; Brant
Subject: Re:AR -- GL Reconciliation
I'm not sure why this is being approached programatically but I may have a
functional solution
If items are in AR and not G\L then it seems they have not been posted to
the
G\L - there is a report ; Unposted Items, within the canned AR reports that
can
be generated to determine which transactions are in AR but not in G\L
because
they are not posted.
Also, we've experienced where transaction types errorneously changed and
when
the Accountants ran the Age Trial Balance to reconcile to the GL these
transactions did not appear because of the paramenters entered did not
include
these transaction types.
Cheryl Moore
Ph: 713-235-6193
pgr: 800-391-6124
e-mail cheryl_moore@huntsman.com
From: Phillips, Brant
Sent: Thursday, August 12, 1999 5:57 PM
To: 'Cheryl_Moore@huntsman.com'
Subject: RE: AR -- GL Reconciliation
Yes, our financial analysts use this report when the AR Transfer is run on a
weekly basis. However, we have transactions trapped in AR that do not show
up on the Unposted Items report. One example is a known problem of
transactions that have missing payment schedules. We currently have about
$53,000 in transactions that have this problem. I believe that we have
another unrelated problem with Invoices that have commitments(Deposits).
The commitment transaction distributions are transfered to GL but the
associated Invoice distributions did not. The invoices also cannot be
completed. We are trying to determine if the transactions we have
identified, are the only transactions to be concerned with.
I should probably use the report as a basis for my own development. Good
point. If you have any other suggestions. Please let me know.
Thanks,
Brant
Brant Phillips
Sr. Programmer Analyst
Bell & Howell Mail & Messaging Technologies
4401 Silicon Drive
Durham NC 27709
E-Mail:Brant.Phillips@mmt.bellhowell.com
Voice Mailbox #4039
Date: Fri, 13 Aug 1999 08:52:58 -0400
From: "Phillips, Brant" phillib@mps.bellhowell.com
To: "'OAUG Distributions'" oraapps-l@cpa.qc.ca
Subject: FW: AR -- GL Reconciliation
I've had a couple of responses to check the unposted items report. That is
not going to work in our case because we are trying to identify transactions
not listed on the report.
Brant Phillips
Sr. Programmer Analyst
Bell & Howell Mail & Messaging Technologies
Date: Thu, 19 Aug 1999 09:41:15 +0200
From: Suzie Roth suzie_roth@stcl.scitex.com
To: "'oraapps-l@cpa.qc.ca'" oraapps-l@cpa.qc.ca
Subject: RE: AR -- GL Reconciliation
Hi,
1. Compare the two reports from AR:
Transaction Register
Sales journals report
In the parameters, ask only for posted invoices. If you'll get the
numbers correct, it means that
all the invoices in the AR created correct journals and were posted to
the GL.
The formula is: Transaction register total + 2*Credit Memo total = Sales
journal total (DB + CR).
2. Check that all GL batches with "Receivables" source are imported and
posted in the GL.
It should be enough for reconcile the invoices and credit memos.
-- Suzie Roth
MIS (Ext. 7151)
As of and Statement Date AND Receipt Tx not printing
Date: Wed, 18 Aug 1999 13:53:06 PDT
From: "prasad surisetty" surisettyp@hotmail.com
Hi APPS Gurus,
1)Can any body explain me what for As Of Date & Statement Date in the
statement print screen in AR.
2)I am not getting the receipt transaction on the Customers statement
printing which shows the on_account amt transaction.
CAn anybody help me in these 2 issues..?
Prasad.
Date: Wed, 18 Aug 1999 16:05:53 -0500
From: "Nguyen, Michael" MNguyen@co.scott.mn.us
The "As Of" date is used to print a draft statement. This date determines
the transactions to include in your draft statement. The default is always
today's date.
The "Statement Date" is used for generating statements. When you enter a
statement cycle, the default statement date is the next available statement
date for this cycle. You can choose another date from the list of values.