Oracle E-Business Suite: Entering and Reconciling Bank Statements

Exclusive offer: get 50% off this eBook here
Oracle E-Business Suite 12 Financials Cookbook

Oracle E-Business Suite 12 Financials Cookbook — Save 50%

Take the hard work out of your daily interactions with Oracle E-Business Suite financials by using the 50+ recipes from this book and eBook

$41.99    $21.00
by Yemi Onigbode | August 2011 | Enterprise Articles Oracle

Oracle E-Business Suite 12 Financials is a solution that provides out-of-the-box features to meet global financial reporting and tax requirements with one accounting, tax, banking, and payments model, and makes it easy to operate shared services across businesses and regions.

In the previous article by Yemi Onigbode, author of Oracle E-Business Suite 12 Financials Cookbook, we took a look at creating bank accounts and cash forecasts.

In this article we will take a look at entering and reconciling bank statements.


Oracle E-Business Suite 12 Financials Cookbook

Oracle E-Business Suite 12 Financials Cookbook

Take the hard work out of your daily interactions with E-Business Suite financials by using the 50+ recipes from this cookbook

        Read more about this book      

(For more resources on Oracle, see here.)

Entering bank statements

Bank statements are downloaded from the bank to a local directory. Once the file is received, the bank account balance and statement information can be loaded into the bank statement open interface tables, using the bank statement loader program or a custom loader program. The files can also be loaded automatically using an interface program or using the XML Gateway. Bank statements can also be entered manually.

In this recipe, we will look at how to enter bank statements.

Getting ready

The bank statement shown next has been loaded into the open interface table:

Oracle E-Business Suite 12 Financials Cookbook

Let's review the transactions in the open interface:

  1. Select the Cash Management responsibility.
  2. Navigate to Bank Statements | Bank Statement Interface Lines.

  3. Select 95-6891-3074 in the Account field.
  4. Click on the Lines button to view the transactions in the interface tables.

How to do it...

Let's list the steps required to automatically enter the bank statements from the import and AutoReconciliation program:

  1. Select the Cash Management responsibility.
  2. Navigate to Other | Programs | Run, or select View | Requests from the menu.

  3. Click on the Submit a New Request button.
  4. Select Single Request from the Options.
  5. Click on the OK button.
  6. In the Submit Request form, select Bank Statement Import & AutoReconciliation from the list of values. Please note that we could run the Bank Statement Import program, to run only the import.
  7. Select the Parameters field, and select Kings Cross as the Bank Branch Name, select 95-6891-3074 as the Bank Account Number, and select 20110314-0001 as the parameter for the Statement Number From and the Statement Number To fields.
  8. Accept the default values for the remaining fields.
  9. Click on the OK button.
  10. We can schedule the program to run periodically, for example, every day.
  11. Click on the Submit button to submit the request.

Let's review the imported bank statements:

  1. Navigate to Bank Statement | Bank Statements and Reconciliation.
  2. The imported statement is displayed.
  3. Click on the Review button.

    (Move the mouse over the image to enlarge it.)

  4. In the Bank Statement window, select the Lines button.
  5. The imported lines are displayed.

How it works...

Bank statements can be imported automatically, using a SQL*Loader script against the bank file to populate the bank statement open interface. The bank statement information is then imported into the Bank Statement windows using the Bank Statement Import program.

There's more...

Now, let's look at how to enter statements manually.

Entering bank statements manually

Let's enter the bank statement for the 15th of March manually. The lines on the statement are as follows:

  1. Payment of 213.80.
  2. Receipt of 3,389.89 from A.C. Networks.
  3. Credit of 7,500.00 for Non Sufficient Funds for the receipt from Advantage Corp.
  4. Bank Transfer payment of 1,000.00.
  5. Select the Cash Management responsibility.
  6. Navigate to Bank Statement | Bank Statements and Reconciliation.

    (Move the mouse over the image to enlarge it.)

  7. In the Reconcile Bank Statements window, click on the New button.
  8. In the Account Number field, enter 95-6891-3074, the other details are automatically entered.
  9. In the Date field enter 15-MAR-2011.
  10. In the Statement Number field enter 20110314-0002.
  11. In the Control Totals region, let's enter control totals based on our bank statement.
  12. The Opening Balance of 125,727.21 is entered based on the previous opening balance.
  13. In the Receipts field, enter 3,389.89 and 1 in the Lines field.
  14. In the Payments field, enter 8,713.80 and 3 in the Lines field.
  15. The Closing Balance of 98,495.56 is entered automatically.

Let's enter the bank statement lines:

  1. Click on the Lines button.

    (Move the mouse over the image to enlarge it.)

  2. In the Bank Statements Lines form, enter 1 in the Line field.
  3. Select Payment as the Type.
  4. Enter 100 as the code.
  5. In the Transaction Date field, enter 15-MAR-2011.
  6. In the Amount field, enter 213.80.
  7. Select the next line, and enter 2 in the Line field.
  8. Select Receipt as the Type.
  9. Enter 200 as the code.
  10. In the Transaction Date field, enter 15-MAR-2011.
  11. In the Amount field, enter 3,389.89.
  12. Select the Reference tab, and enter A.C. Networks.
  13. Select the next line, and enter 3 in the Line field.
  14. Select NSF as the Type.
  15. Enter 500 as the code.
  16. In the Transaction Date field, enter 15-MAR-2011.
  17. In the Amount field, enter 7,500.00.
  18. Select the Reference tab, and enter Advantage Corp.
  19. Select the next line, and enter 4 in the Line field.
  20. Select Payment as the Type.
  21. Enter 140 as the code.
  22. In the Transaction Date field, enter 15-MAR-2011.
  23. In the Amount field, enter 1,000.00.
  24. Save the record.
Oracle E-Business Suite 12 Financials Cookbook Take the hard work out of your daily interactions with Oracle E-Business Suite financials by using the 50+ recipes from this book and eBook
Published: August 2011
eBook Price: $41.99
Book Price: $69.99
See more
Select your format and quantity:
        Read more about this book      

(For more resources on Oracle, see here.)

Reconciling bank statements

After importing or entering the bank statements, we can run the AutoReconciliation program to automatically reconcile the bank transactions to the payables payments and receivables receipts. We can also run the bank statement import and AutoReconciliation program as we did in the previous recipe.

We can then review the AutoReconciliation execution report to identify and correct errors. We can rerun the AutoReconciliation program or perform a manual reconciliation, if necessary. In this recipe, we will review the results of the autoreconciliation program, and manually reconcile bank statements.

Getting ready

We need to have entered bank statements.

How to do it...

Let's list the steps required to reconcile bank statements:

  1. Select the Cash Management responsibility.
  2. Navigate to Bank Statement | Bank Statements and Reconciliation.
  3. Navigate to the Menu and select View | Find.
  4. In the Account Number field, enter 95-6891-3074.
  5. Click on the Find button.
  6. Click on the Review button to view the status of your bank reconciliation.
  7. Some of the lines have been autoreconciled.

    (Move the mouse over the image to enlarge it.)

Let's manually reconcile the Miscellaneous Receipt for bank interest:

  1. In the Bank Statement Lines window, select the Misc Receipt line.
  2. Click on the Create button, and select Miscellaneous.
  3. In the Miscellaneous Receipts window, enter Interest Income and click on the OK button.
  4. In the Method field, enter Manual – SB (this is the receipt class for the Bank Receipts).
  5. Click on the Create button.
  6. A Decision box is displayed with the message "Do you want to reconcile the receipt(s) to current statement line?" click on the Yes button.
  7. Save the record.

Let's manually reconcile the Miscellaneous Payment for Bank Charges:

  1. In the Bank Statement Lines window, select the Misc Payment line.
  2. Click on the Create button, and select Miscellaneous.

    (Move the mouse over the image to enlarge it.)

  3. In the Miscellaneous Receipts window, enter Bank Charges and click on the OK button.
  4. In the Method field, enter Manual – SB (this is the receipt class for the Bank Receipts).
  5. Click on the Create button.
  6. A Decision box is displayed with the message "Do you want to reconcile the receipt(s) to current statement line?" click on the Yes button.
  7. All the lines are now reconciled.
  8. Save the record.

    (Move the mouse over the image to enlarge it.)

  9. Click on the Complete checkbox to complete the reconciliation.

How it works...

The cash management reconciliation options are set up up in the system controls. The reconciliation controls region. The settings determine if:

  • Only cleared transactions will be available for reconciliation
  • Voided payments should be available for reconciliation
  • Lines can be added to imported statements

The AutoReconciliation program performs validations when the bank statement information is loaded into the bank statement open interface tables. This includes validating:

  1. Bank statement header
  2. Control total
  3. Statement line
  4. Multicurrency

The matching order is determined when we create the bank account.

There's more...

Now, let's look at some other options that are relevant to reconciling bank statements.

Manual reconciliation

Let's manually reconcile some further transactions:

  1. Select the Cash Management responsibility.
  2. Navigate to Bank Statement | Bank Statements and Reconciliation.

    (Move the mouse over the image to enlarge it.)

  3. Navigate to the Menu and select View | Find.
  4. In the Account Number field, enter 95-6891-3074.
  5. Click on the Find button.
  6. Click on the Review button to view the status of the bank reconciliation.
  7. In the Bank Statement form, select the Lines button.

Let's reconcile a payment already entered in accounts payable:

  1. Select line 1 for 213.80, click on the Available button.
  2. Select the Detail option.
  3. Enter 15-Mar-2011 in the Date field.
  4. In the Amount field enter 213.80.

    (Move the mouse over the image to enlarge it.)

  5. Click on the Find button.
  6. Select the line and click on the Reconcile button.
  7. The line is now reconciled.

Entering AR receipts

Let's reconcile a receipt not yet entered:

  1. Select line 2 for 3,389.89, click on the Create button.
  2. Select AR Receipts.
  3. Enter Manual – SB in the Receipt Method field.
  4. Enter CE-0001 in the Receipt Number field.
  5. In the Customer Name field, enter A. C. Networks.
  6. Click on the Search and Apply button in the Search and Apply form.

    (Move the mouse over the image to enlarge it.)

  7. Select the Apply checkbox on the invoice number 10037503.
  8. Save the record.
  9. Close the form, and the Bank Statement Lines will be displayed.
  10. Select receipt line 2, click on the Available button.
  11. Enter CE-0001 in the Number field.
  12. Click on the Find button.
  13. Select the line, and click on the Reconcile button.
  14. The line is now reconciled.

Entering AP payments

Let's reconcile a direct payment of $1,000.00:

  1. Select line 4 for 1,000.00, click on the Create button.
  2. Select AP Payments, and the Payments form is displayed.

    Oracle E-Business Suite 12 Financials Cookbook

  3. In the Trading Partner field select General Electric Utilities, and select GE for the Supplier Site.
  4. In the Payment Amount field, enter 1,000.00.
  5. Select the Payment Method of Wire.
  6. Select Manual as the Payment Process Profile.
  7. Select SB_Cheques as the Payment Document.
  8. The Document Number is automatically selected.
  9. Click on the Enter/Adjust Invoices button.
  10. Select the invoice GE-10009.
  11. Save the record.
  12. Click on the Actions buttons, and select the Final Post option.
  13. Click on the OK button.
  14. Close the form and we should be back at the Bank Statements Line form.
  15. Click on the Available button, and search for the Item using the Number 100000002.
  16. The document is displayed in the Available Transactions window.
  17. Select the Transaction and click on the Reconcile button.
  18. The line is now reconciled.

Reversing transactions

Let's reverse the receipt of $7,500 due to insufficient funds:

  • Select the reversal button from the Bank Statement page.
  • In the Date field, enter 14-Mar-2011.
  • In the Amount field, enter 7,500 and click on the Find button.

    Oracle E-Business Suite 12 Financials Cookbook

  • Select the Transaction.
  • Select the Comment tab, and select Non-sufficient Funds in the Category field and NSF in the Reason field.
  • Click on the Reverse Receipt button.
  • Close the form and select the Lines button.
  • Select the NSF line and click on the Available button.
  • Search for the Item using the amount 7,500 and the Number SB-0972.
  • The document is displayed in the Available Transactions window.
  • Select the Transaction and click on the Reconcile button.
  • The line is now reconciled.

Summary

In this article we took a look at entering and reconciling bank statements.


Further resources on this subject:


Oracle E-Business Suite 12 Financials Cookbook Take the hard work out of your daily interactions with Oracle E-Business Suite financials by using the 50+ recipes from this book and eBook
Published: August 2011
eBook Price: $41.99
Book Price: $69.99
See more
Select your format and quantity:

About the Author :


Yemi Onigbode

Yemi has over a decade of experience in ERP systems development and maintenance. He has progressed from the early days of the IBM mainframe-based systems to the present day of web-based distributed systems. He has worked with large multinational consulting firms including Oracle Corporation, Accenture, Deloitte, Fujitsu Consulting, IBM, Sun Microsystems, and CSC.

Yemi is a hybrid techno-functional expert. He is a hands-on Project Manager, Business Architect and Instructor, specialized mainly in Financials, Human Resources, and Supply Chain models. He has assisted a number of businesses to find solutions to complex business issues using various technologies.

Yemi is also a keen believer in self-development and research; he is at par with the current developments in the IT and communications industry. He is currently developing and implementing e-business solutions for large and medium sized companies, fully integrating their business applications with the Internet and providing a totally integrated solution. He has been developing in Java since 1996 and implementing Oracle Applications since 1997.

Yemi is an Oracle Financials Certified Trainer, Sun Certified Instructor, and a Fellow of the Association of Chartered Certified Accountants. He has a Bachelor of Science (Honors) degree in Pure and Applied Mathematics. Yemi is also a technical writer on Accountancy and IT issues, and presents at Accounting and IT seminars. He also motivates and mentors young adults on career management and planning. In his spare time Yemi loves to write and play musical instruments.

Books From Packt


Oracle E-Business Suite R12 Supply Chain Management
Oracle E-Business Suite R12 Supply Chain Management

Oracle Warehouse Builder 11g R2: Getting Started 2011
Oracle Warehouse Builder 11g R2: Getting Started 2011

Oracle Business Intelligence : The Condensed Guide to Analysis and Reporting
Oracle Business Intelligence : The Condensed Guide to Analysis and Reporting

Oracle Database 11g – Underground Advice for Database Administrators
Oracle Database 11g – Underground Advice for Database Administrators

Oracle ADF Enterprise Application Development—Made Simple
Oracle ADF Enterprise Application Development
—Made Simple

Oracle PeopleSoft Enterprise Financial Management 9.1 Implementation
Oracle PeopleSoft Enterprise Financial Management 9.1 Implementation

Oracle Enterprise Manager Grid Control 11g R1: Business Service Management
Oracle Enterprise Manager Grid Control 11g R1: Business Service Management

Oracle GoldenGate 11g Implementer's guide
Oracle GoldenGate 11g Implementer's guide


Code Download and Errata
Packt Anytime, Anywhere
Register Books
Print Upgrades
eBook Downloads
Video Support
Contact Us
Awards Voting Nominations Previous Winners
Judges Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software
Resources
Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software