Cloud Computing Using Oracle Application Express

The phenomenal growth of information technology—especially the advent of cloud computing—is changing the landscape of information technology, business, and personal computing. If applied correctly, it can increase the productivity of enterprises and enable them to focus on increasing profits and lowering costs. Cloud computing in its simplest form means accessing and storing data and applications over the Internet, instead of on a native computer’s hard drive. The purpose of this book is to give you a taste of cloud computing by developing a functional general ledger accounting system for and in the cloud.

Previous versions of APEX used tabs that acted as an application’s main menu up to version 4.2. In APEX 18.1, a default navigation list named the desktop navigation menu is created automatically as a shared component for each new application. This provides the user with a hierarchical set of pull-down menus and submenus. It is displayed as a responsive sidebar. Based on the available space, the navigation bar either displays as a full menu or collapses to a narrow icon bar.

Let’s begin the application-building process by creating the Company Setup, which is very simple. Note that the Home page of your application will contain an executive dashboard that displays various charts based on existing data. Since you do not have any transaction data so far in your database, the Home page will be dealt with in Chapter 29 . The Cloud Accountant is capable of handling accounts of multiple companies simultaneously, which is why you have to develop this setup. Each company created through this setup will have a unique code. This code will be saved with every transaction to distinguish one company’s data from that of the others.

Organizations are required by law to produce a set of annual accounts. To this end, their general ledger systems must maintain a fiscal calendar. Although the specific start and end dates of a fiscal year vary from country to country, for our purposes, the year will start on July 1st and end on June 30th. In addition, each fiscal year is normally subdivided into periods. For our purposes, each period will be a calendar month.

Financial transactions are recorded in ledgers using special forms called vouchers. Before creating the actual interface for vouchers, you are going to create a Setup page to define different voucher types. Vouchers are broadly divided into three categories: payment, receipt, and journal. Each one has its own specific interface to record transactions.

In this chapter, you will create a setup that provides a list of all the different parts of the application. Just like a site map created for websites, it is displayed as a tree view of the application and is created to implement application security. There are three main components in the application to which you should apply security: menus (including the main and submenus), pages, and items (such as buttons). The fourth one (app) is called the root node and is used to distinguish between segments of multiple applications.

In the previous chapter, you laid the foundation of your application’s security that will be imposed on menus, pages, and page items. In this chapter, you will create user groups. Allocating application rights to individual users is a tedious activity, and it’s not recommended because it is highly error-prone. You can create a few user groups and assign application privileges to them instead. Users are created afterward and then associated with their respective groups. This means all users inherit application rights from the group (or groups) to which they belong. For example, to handle application security for a staff of more than 100 employees, comprising managers and data entry clerks, you will create just two groups (managers and clerks) with appropriate privileges. Any changes made to the privileges of these groups will be automatically inherited by all associated users.

After creating groups, you add users to them, as shown in Figure 8-1 later in this chapter. You input a user ID for each new user, but when you call up an existing user’s record for modification, this value is displayed as read-only text. You also allocate a default company to each new user to work in. This allocation is helpful in restricting a user to handling the accounts of a specific company. You might notice from Figure 8-1 that besides assigning the Admins group, you also specify whether the user is an administrator. This is because you have a column (Admin) in the GL_USERS table that explicitly assigns administrative rights to those users marked as administrators, irrespective of the group to which they belong. This explicit marking is necessary in some cases to quickly assess whether a user is an administrator. You’ll see an instance of this in the Chapter 9 .

A user who wants to access this application can do so only with a valid ID and password. You created some user accounts in the previous chapter and assigned them IDs and groups. In this chapter, you will create a facility for setting and changing passwords. Note that this feature will be invoked from the Utilities menu. The Reset Password interface is self-explanatory. Administrators select a user ID and then provide and confirm a new password for it. The application then checks to make sure both of these are identical and, if so, allocates the password to the user ID. Users may also use the same method to reset an existing password. The initial password allocation task is performed by the application administrator.

This page allows users to switch the company, year, and month depending on their access privileges. Recall that every new user was allotted a default company, while an associated process saved a default year and month within their profiles so that they could start using the application right away. These selections are saved in the GL_USERS table and are reflected on the top-right corner of every page through the Global Page.

Cost and revenue centers help you maintain accounts of all departments and divisions participating in your business. Using this setup, you keep track of revenues generated by departments and expenses incurred by them. After creating cost centers, you can link them to the main financial accounts in the chart of accounts. This allows you to set a default cost/revenue center for every financial account. When you pick up an account from the chart of accounts during the voucher generation process, these cost/revenue centers come along as default entries to minimize data entry work.

The chart of accounts (COA) is part of the application that needs to be planned carefully before implementation. A well-planned COA provides better insight into the financial matters of an organization. A separate COA is created for each company; however, you can create a COA for one company—the master COA—and then copy it to the others using the Copy COA utility as developed in the next chapter. The accounts created here are selected for each transaction during voucher generation, report calls, and preparation of financial statements. Because this setup is similar to the cost center setup, you will use the copy utility to save some time. The account code used in this setup uses the format 9-99-999-99999, which contains four separate number groupings or levels. The first level defines the nature of account, while the next three levels act as its subcategories. Each account specified on the first level belongs to one of the following natures: equities, liabilities, assets, revenues, and expenses. The initial three levels are called group levels, whereas the bottom level (in other words, level 4) is called the transaction level because accounts from this level are selected to generate transactions in the vouchers interface. The group-level accounts are used in trial balance and financial statement reports to extract summarized group-level information.

As mentioned earlier, the application has the capability of maintaining the accounts of multiple companies simultaneously. To complement this feature, the application contains a utility to allow you to copy the chart of accounts (COA) from one company to another. Obviously, this is a great time-saver when setting up a new company. Before you start development, make sure that the source COA has been created successfully before invoking this utility. The page for this module consists of two select lists. The first allows you to specify the source company whose COA you want to copy from, while the second allows you to select the target company to which you want to copy the source COA.

This is the segment that you use to enter and record financial transactions. On the initial page of this segment, you select a voucher type, which you created in Chapter 5 . The underneath report gets populated with all the vouchers related to the selected type.

The Transaction Search utility helps you locate transactions in a matter of seconds. There are numerous occasions when an accountant needs to search for a specific financial activity entered into a ledger. For example, the accountant may want to know the details of a payment made against invoice number 78345. In this chapter, you will create a search utility to locate a transaction. To search for transactions, a criteria list is provided along with a search box. You select a value from the criteria list (e.g., Reference), then enter a value in the Search box (e.g., 78345), and finally hit the Search button. The utility searches for the provided value in the selected column and displays the matching records in an interactive report.

The Vouchers Verification segment serves two purposes. First, it allows authorized staff to check the accuracy of business transactions, and second, it prevents verified vouchers from being modified or deleted. Its main interface provides a Parameters region, where you enter a range of dates and specify whether you want to see Unverified or Verified vouchers. The date parameters receive voucher creation dates (not the voucher date). The lower region of the page displays a list of vouchers based on the selected criteria. Each voucher’s detail record starts with a link column, which calls another page where an authorized person can see a complete voucher with all its details. The page carries appropriate buttons to verify/unverify the voucher being shown.

The application enables you to make hard copies of vouchers. Using this feature you can print either a single voucher or multiple vouchers at once. Note that the output format for all the reports created in this application will be PDF. To get these PDFs, you must have access to a print server, such as Oracle BI Publisher or JasperReport. In this chapter, you will learn how to integrate Jasper Reports in your Apex application that will provide a cost-free alternative for high-fidelity printing in Oracle APEX. For this purpose you will use a free kit known as JasperReportsIntegration kit to run JasperReports reports from within an APEX application.

A ledger report is a report that shows all financial activities performed in an account. In this application, it consists of seven columns, as illustrated later in the chapter in Figure 18-1. When a voucher is saved, all transactions you define in it are posted to the relevant ledger accounts with the respective debit and credit figures. The ledger report shows accounts with their transactions and balances. To generate this report, you use the Ledger Report Parameters form, where you specify the duration and the range of accounts you want to browse. As you can see, the parameter form has two buttons: the Display button is used to produce an onscreen view of the ledger report, while the Print button produces a hard copy. A useful ability of the report is that it contains a link for viewing the source voucher. This link will be created on the Voucher Date column.

The trial balance report shows the summarized balances of accounts up to a specific date. It delivers the opening balance, activity, and closing balance of each account. Unlike the Ledger Report, this report can be produced for any level. For example, you can run a Trial Balance Report for the first level only simply to see top-level account activities. You can also filter the report to display a specific range of financial accounts from the COA along with a cost center, should you want to see account balances for only one cost center. The parameters form has just one date picker from where you select the date up to which closing balances are calculated and displayed in the report.

The application provides you with a complete module to deal with your banking. Recall that while creating accounts in the chart of accounts (COA) you used a specific type to mark bank accounts; that was the first step in the bank reconciliation process. In addition, the application allows you to reconcile the bank transactions recorded through vouchers with the statements provided by your banks. But, before that, you have to incorporate some bank transactions into this application. These are the transactions recorded either manually or in another system and were not reconciled with the banks. In this chapter, you’ll record all such transactions into a separate table that will keep appearing on the Bank Reconciliation page and report unless you mark them as reconciled. The actual reconciliation process will be created in Chapter 21 .

A bank reconciliation is the process by which you match your bank ledger transactions with those in your bank statement and attempt to find any differences between the two. All income transactions appearing on the debit side of your ledger are shown on the credit side of the bank statement. Conversely, all payments that you make are recorded on the credit side of your ledger, and the same transactions are reported on the debit side of the bank statement. This part of the application allows you to identify transactions and perform a reconciliation.

Even after reconciling your ledger entries with those appearing in the bank statement, there might still be some unsettled transactions on both sides. For example, say a check was issued to a vendor that doesn’t appear in the bank statement. Although it was recorded in the application, for some reason it was not presented to the bank in due course. Similarly, say the bank statement reports a credit amount deposited directly by one of your customers that you were not aware of. In the latter case, accountants prepare a receipt voucher to incorporate and reconcile the credit. For the former case, you have to wait until the check appears in a subsequent bank statement. After completing the reconciliation process, the application produces a report that shows the current ledger balance and a calculated bank statement balance, with some unsettled transactions (such as unpresented checks). The two balances should match, if there are no more outstanding figures on either side.

It is common practice for organizations to close those fiscal periods for which they are sure to receive no more data. This process is usually referred to as month closure. The main purpose of this process is to prevent data manipulation in a closed month. In this chapter, you will create this feature first by creating some procedures to mark a fiscal period as closed and then modifying the main transaction interfaces to apply the desired data security.

The year-end process consists of two options: temporary and permanent. The temporary year-end process performs two actions: first, it generates the next fiscal year, and then it transfers the balances of accounts from the closing year to the new fiscal year. When you select this option from the Closing menu, the system asks you to provide a profit and loss account from the chart of accounts (COA), which is used to transfer the difference of revenues and expenses.

A budget is a useful tool to keep spending under control. Every good organization uses this tool to keep an eye on its activities. After allocating a budget to an account, it is compared to the actual expenditure to make sure there is no overspend of money. In the initial year, budgets are allocated manually because of the absence of historical data. In subsequent years, you have two options to define budgets: you can reuse last year’s budget or allocate last year’s actual spend to act as the current year’s budget.

The Budget Report displays the variance between the allocated budget figure and the actual figure recorded through vouchers. It also carries a Status column to signify whether the actual value exceeded (over-applied: O) the allocated budget or it remained within the defined limit (under-applied: U). The parameters form allows you to generate this report for a particular nature, for a single account or a range of accounts, and for different durations.

Organizations prepare financial statements periodically (especially at the end of a fiscal year) to assess business performance. The two most common financial statements are the Profit and Loss (P&L) Statement and the Balance Sheet. In this chapter, you will create a setup where users will provide parameters in the form of account codes from the chart of accounts (COA) for these two reports. These accounts will be used in the next chapter to produce the two financial statements.

This is the segment that returns the final result of the efforts you have made so far. In this chapter, you will create the Profit and Loss (P&L) and Balance Sheet financial statements. These statements are based on the setup parameters you specified in Chapter 27 . The P&L Statement shows the profitability, whereas the Balance Sheet reports on the equities, liabilities, and assets of a business. Combined, these two reports reveal the financial state of an organization.

You created the two most vital accounting reports in the previous chapter. With the information provided by these reports, all stakeholders of an organization assess their business health. Among these stakeholders are the business executives who need some more information so that they can evaluate their business at a glance. To satisfy the need of these key stakeholders, you will create an executive dashboard in this chapter to graphically present the information they are looking for.

This segment is added to create interaction among the application administrator and its users. It allows end-users to communicate application issues to the application administrator. It consists of a form and a report. The form is created for the users to input their feedback, while the report is used by the application administrator to browse the issues added through the form.

Before you go live with an application, it is important to apply proper security measures. One of the most basic forms of protection that any web application must have is the enforcement of an authentication and authorization policy. Authentication deals with identifying users to the application; you’ve already implemented it in this application using a custom authentication scheme: a username and password. Authorization is the process of assessing whether the authenticated user is privileged to access certain data or perform a particular action. Recall that you have already laid the foundation of application authorization in Chapter 6 , where you specified the application segments to which you want to apply authorization. Then, you created user groups and provided them with the appropriate application access privileges. In this chapter, you will create a bunch of authorization schemes to protect your application. These schemes will be created to protect menus, pages, buttons, and processes.