/* ORACLE CASH MANAGEMENT
Oracle Cash Management : We use the Oracle Cash Management for
the main purposes of reconciliation. Basically Reconciliation is process
of matching and clearing the transactions that happened for ex; in Payables,Receivables,
Payroll and Treasury. This is how it works.
Let us say we have made a payment using Payables for a particular invoice.
Then typically this payment goes thru the standard process of Build,
Formatted and confirmed and then finally to GL. It is important to
understand that this payment is always associated with a bank account#.
Now when the Bank periodically sends the statements detailing
all the payments and receipts, we enter this information into the Oracle
Cash Management using the Bank stmt workbench.
Now we can reconcile these bank statements against our regular payments
and receipts transactions. This is what is called Reconciliation.
There are two major steps involved in reconciliation and they are
-- Matching and
-- Clearing.
The Bank Statement transferred will consist of the following types of transactions,
AP Payments
AR Receipts
GL Journal Entries
Bank Charges (Bank Orginated)
Interest (Bank Orginated)
Payroll entries (Bank Orginated)
Treasury Settlements (Bank Orginated)
/*Lockbox vs Bank Statement Upload :
The lockbox process in Receivables, is where the bank gives us the statement
periodically consisting of complete receipts and we try to apply to the debit
items/on account using the autocash rules.(i.e whether the oldest invoice
or the highest invoice etc).
However if use the reconciliation process, the bank gives all the activity
completely until that point, which includes the payments and receipts. And
if you try to start the AutoReconciliation process, it will apply it
against the invoices and receipts in the receivables and payables systems(???????).
*/
Matching is the process by which
Case 1 :
Go the Accounts payable system and do the following.
Create an invoice batch, an invoice and then distributions, and go thru the
process of validating,accounting, approving etc.
Create a payment batch,a payment in it and build the payments. (Remember
for simplicity purposes enter exactly the same amount for payments
as for the invoice)
Run the conc program "Payables Transfer to GL' and post the transactions.
Come to the Oracle Cash Management application using the OCM responsibility.
Enter a bank statement, for say 'Comerica bank' with the amount exactly same
as payment created above.
(A bank stmt typically has header and line and line amt should be same
as payment amount).
While entering the line information, enter in the TRANSACTION NUMBER SAME
AS THE PAYMENT BATCH NUMBER
(this is very important Matching criteria for reconciling the txns for
the payable txns)
Call the AutoReconciliation program from the tools menu.
(look for the errors and reconciliations).
Case 2 :
We can do the same process for the Receivables transactions as well.
The important thing is that, we need to give the TRANSACTION NUMBER
AS THE RECEIPT BATCH NAME.
select * from ce_statement_headers_int
select * from ce_statement_lines_int
You can reconcile the bank statements in Cash Management either automatically or
Manually.
The Automatic Reconciliation process can be setup to create miscellaneous
transactions automatically.
You cannot clear payroll entries, GL Journal entries, or treasury settlements. why is that so??
what is the package CE_999_PKG ??
each line corresponds to a transaction code and each transaction code
corresponds to a transaction type like payment, receipt,misc payment,misc receipt.
WRITE more about this.
-------------------------------
SWEEP IN/SWEEP OUT
SWEEP functionality is available with banks. Bank uses the balance in the customers
accounts for investment overnight and returns the balance to the customer account
by the start of next business day.
For the overnight use of the funds bank will provide interest to the customer.
This is a standard facility provided by the bank. The SWEEP transactions (investments
and sale of investments) and the interest earned figure in Bank Statement sent by the Bank.
Another explanation of Sweep Account is below.
Sweep Account : A sweep account is one where if the balance reaches above a certain
level, maximum or minimum level then the additional funds are moved out/in to/from
another account. As an ex; say we want to have a bank account whose balance should
not go below $3,000 and should not go above $10,000. Then we can have arrangement
that if the balance goes above the $10,000 limit, by say $500 then that $500 will
be moved to another account, say, some savings account so that it can be useful there.
Similarly if the account balance goes below the $3,000 by ,say $1,000 then that
additional $1000 should be brought from some other account to this account so that
the minimum balance is maintained.
So in the above ex, the sweep in $1,000, while the sweep out is $500. In the
statement for this account, the lines will show as the sweep-in and sweep-out lines.
Banks and Bank Accounts :
------------------------
In 11i, the banks,bank accounts were owned by AP. However in R12, the banks are
owned by Cash Management,however the banks forms can still be accessed from AP/AR.
Also the functionality of bank account transfer was part of the Treasury Module,
which is now available as part of the Cash Management.
Bank Account transfer is a functionality in which we can initiate a balance transfer
between two intra bank accounts i.e transfer between two accounts.
So basically there is no direct interface from cash management to GL. All the entries
that cash management creates are in either AP or AR. So to move the transaction
you need to do a GL transfer from AP ,AR and then run the Journal Import,Posting
etc.
-------------
Usually here is the flow for the bank statatement coming from the bank to the
companys financial system,its upload and reconciliation process. Before we talk
about the process it is important to understand that just like any interface,here
too we will have the CM Temporary, tables,Interface tables and CM standard tables
and a starndard import program exists to import that data into the Cash Management.
Just like the lockbox file has a standard format(like BAI2 etc), the bank statement
files also have a standard format like BAI,SWIFT etc.
Basically there are 3 ways thru which the statement coming from the bank can be
loaded into the system.
-- Manually enter the data by looking at the statement into the system.
-- We can write a custom program to load the data from the files into the interface
tables,if the format is not a standar format.
-- Use the Oracle Standard Program to load the data. Basically Oracle provides
mainly two proram to load the data
Bank Statement Loader Program
Bank Statement Import Program.
Regardless, we can see that there are load, import and Reconcile steps in Cash
Management.
The bank statement Loader program loads the data from the temporary tables to
Interface table and runs in two phases,
First phase, the sql loader program loads the standard file format data (using a
standard control file) into the CM temporary tables.
Second phase, ,using the mapping rules, the data from the temporary tables is
loaded into the standard interface tables.
We can automatically run the Import and Reconcile programs from the Bank Statement
Loader program after the load is completed.
The Bank Statement Import Program loads the data from the Interface table to
Standard table. Similarly then we can start automatically the Reconcile program
after the Import process is completed.
Reconciliation can be done in two modes, Manual and Automatic.
Manual Reconciliation means manually matching up the payment, receipts etc
with the banks statement lines and clearing them.
Automatical Reconciliation means, using the standard program to reconcile. This
is useful when we have a huge volume of transactions and details to reconcile.
Reconciliation Process : Cash management provides a program called "AutoReconciliation"
lets talk a bit about this program's parameters. Usually a bank statement is
sent for each account, corresponding to what are all the transactions that have
happened for that account. Hence when we reconcile we provide a particular
bank , bank account, Payment Method, Receivable Activity,NSF Handling.
There are a couple of things that we need to make sure before the Autoreconcilication
program is run.
Firstly, ensure that you have run your Autolockbox program so that all the
receipts are created in your AR system, otherwise there will be nothing for
Reconciliation program to match the lines against.
Secondly, The bank statement typically consists of bank-initiated sweep lines. Cash
Management provides for a standard program called "Sweep Transaction Generation"
program, what this program does is, it searches for the bank account transfer
transactions in the cash management and compares them with the sweep lines.
If there is a match found,then there is nothing to do.
If there is no match, then, it will create a transaction in Cash Management.
Following this, the AutoReconciliation is ready to match and reconcile.
Usually when the reconciliation program runs, if it finds the Matching records,
then they are matched and there is nothing to create.
However when they dont match, i.e say if a AR receipt does not match with the
bank statement line with in the tolerance limit, then we can do various things
like create misc trx, post to bank charges or errors accounts. see below for
different options.
While Reconcliing AP Payments, (transaction code "Payment")
If the difference between the payment amount and the statement line amount is
within defined tolerances, the AutoReconciliation program posts the difference
to either Bank Charges Account or Bank Errors account depending on the setting.
While Reconcliing AP Payment Batches, (transaction code "Payment")
If the difference between the payment batch amount and the statement line amount is
within defined tolerances, the AutoReconciliation program posts the difference
to either Bank Charges Account or Bank Errors account depending on the setting.
While Reconcliing AR Receipts, (transaction code "Receipt")
If the difference between the receipt amount and the statement amount line is
within defined tolerances,the AutoReconciliation program posts the difference
to either Bank Charges Account or Bank Errors account depending on the setting.
While Reconcliing AR Remittance Batches, (transaction code "Receipt")
If the difference between the remittance batch amount and the statement amount line is
within defined tolerances, you can choose to have the AutoReconciliation program
create a miscellaneous transaction for the difference. This setting is done
in the Cash Management Receivables Activity system parameter. Incidentally
even for this we can have a bank errors or bank charges being set up so that
no transaction is created.
setup=> system => system parameters => General Tab(Main)
-- For GL we have to see , it is done is Cash Mgmt only.
Miscellaneous transactions in Cash Mgmt : (transaction code "Misc Receipt", "Misc Payment")
During the Reconciliation process, for the bank originated entries like the
bank charges, interest etc (for which there is nothing to match in the AP,
AR,GL system), you can create miscellaneous transactions. The AutoReconciliation
progress can be setup to create miscellaneous transactions automatically like
an AR receipt or AP payment. So the transactions such as AP Payments and AR
Receipts created by Cash Management are called Miscellaneous transactions.
For Banking activity like bank charges or interest, miscellaneous transactions
are created. since bank charges are something we owe the bank, a miscellaneous
payment is created. When you create a miscellaneous payment, it is actually
recorded as a negative receipt, i .e a miscellaneous AR receipt with a negative
amount. Now since this receipt should have a payment method and it is applied
to a receivable activity, we need to specify the same as a parameter at the
time of runningthe AuotReconcilaition progrom.
Exceptions : (transaction codes Rejected, Stopped,NSF,
A Bank statement may contain following exception information
--NSF (Non Sufficient Funds) => what this means is that customer bank account
is not having sufficient funds to make the payment to the invoice.
--Rejected Receipts
--Stopped Payments
--Direct Deposits
--Direct Debits
--Transactions created outside Oracle Applications
To Handle rejected receipt(or NSF), if you specify as Reverse, AutoReconciliation unmatches
and unclears the receipt and reopens the invoice.
To handle the stopped payment, atuorec will void the payment in AP, then reconcile
the voided payment to the "stopped" payment statement line.
To handle a direct debit that you have not entered, Autorec will create a payment
in AP and match that payment with the statement line.
To handle a direct deposit: This is a very interesting point.
Actually we can use the same statement file to process for the autolockbox program
as well. So to handle the direct deposit, first run the autolockbox process
for the same statement file to create the receipts corresponding to the direct
deposit, then runt he AutoReconciliation program to reconcile the direct deposit
receipts to the statement lines.
To handle a bank error: what this means is that the bank has made an error in the
previous statement file and has provided a correction to that in this statement
file. So in order to correct that , if we are doing manually, we can try to find
the previous statement line, and then mark that line. There is no need to reconclie
in this case.
Handling corrections and Adjustments :
Need to read this in more detail ????
Transferring Bank Reconciliation Transactions to Your General Ledgers :
You need to transfer the accounting transactions you created during reconciliation for
Payables and Receivables to your general ledger interface tables. You cannot transfer
these transactions directly from Cash Management. Instead, transfer them from
Payables and/or Receivables, depending on the transaction type.
Cash Transactions in Cash management
------------------------------------
There are two types of cash Transactions in cash management
-- Bank Acount Transfer
-- Cashflow Transaction.
Bank Account Transfer : In order for the banks to show up in the Creat bank
account transfer, it is important to set the profile option "CE: Bank Account Transfers"
to Cash management. By default it is begin set to "Treasury".
And once the Reconciliation comples, we can run the following out-of-the-box
reports,
-- Cash In Transit Report
-- GL Reconciliation Report
-- Bank Statement Summary Report
-- Transaction Available for Reconciliation Report
