Oracle PeopleSoft General Ledger data model and data flow

The PeopleSoft General Ledger is a module in the Oracle PeopleSoft Enterprise suite of applications. This post gives a brief overview of the data flow and data model of PeopleSoft General Ledger.  General Ledger applications are generally a central point for key financial reporting and this is also the case for PeopleSoft applications.

Oracle Business Intelligence Applications (OBIA) offers pre-built integration with Oracle Peoplesoft Enterprise, and the tables here are some of the source tables for Oracle Business Intelligence Application Financial Analytics.


Data is brought into the GL Journal Header (JRNL_HEADER) and Journal Lines (JRNL_LN) tables via a number of methods – spreadsheet template import, flat-file import, or via a load process from other PeopleSoft modules.  There are multiple Journal Lines for each Journal Header.

The Journals are edited, and when valid, they can be Posted.  The Posting creates records in the Ledger table (LEDGER).  Ledger data is a common source for reporting – for example, P&L and Balance Sheet reporting will be done against Ledger data.

Some supporting tables for the GL module are:
GL_ACCOUNT_TBL:  contains the value and description of Account values
PRODUCT:   contains the value and description of Product values
DEPT_TBL: contains the value and description of Department values
BUS_UNIT_TBL_GL:  contains the value and description of Business Unit values
ACCT_TYPE_TBL:  contains the descriptions of Account Types – Asset, Liability, Equity, Expense, Revenue


Oracle EBS Receivables Data Flow and Data Model

The Accounts Receivable function is responsible for managing outgoing invoices to customers who purchased goods or services, and the collection and application of all payments, including payments for invoices.  The Oracle Receivables module (a part of the Oracle EBS Financials Suite) helps the Accounts Receivable departments to manage this function effectively and efficiently.

This post describes a summary of the Oracle Receivables data model and data flow.  Some of these tables are source tables for the Oracle Business Intelligence Applications – Financial Analytics module, specifically providing information for the Payabales dashboard.


To be in the position where you need to handle and process a payment in Receivables, you need to have a buyer/payer (most times this is a customer but there are exceptions). Customer records are stored in the HZ_CUST_ACCOUNTS and HZ_PARTIES tables.  Each customer needs to have a site (a location/address of business) for which information is stored in HZ_CUST_ACCT_SITES_ALL and HZ_PARTY_SITES_ALL.

When a customer purchases goods or services from your company, an invoice is generated for the customer.  These invoice transactions are recorded in RA_CUSTOMER_TRX_ALL (invoice headers) and RA_CUSTOMER_TRX_LINES_ALL (invoice lines).

When the customer makes a payment, this generates new transactions.  These are recorded in AR_CASH_RECEIPTS_ALL and AR_CASH_RECEIPT_HISTORY.  If there is adjustment to an invoice, this is recorded in AR_ADJUSTMENTS.

Sometimes payments are received in batches, where a single payment is for multiple invoices.  These batch payments have records in AR_BATCHES.

The AR_PAYMENT_SCHEDULE table holds one record per payment.  Therefore, for payments that pay an invoice in full, there will only be one record related to that invoice.  However, if payments for an invoice are broken up into a payment plan, or if a partial payment is received for an invoice, additional records will be generated in this table for each payment.

I mentioned above that “most times payments are from customers, but there are exceptions”. An example of an exception is “payment from a bank for interest earned”.  The payment is not from a customer and it’s not for goods/services provided.  These types of payments are recorded in AR_MISC_CASH_DISTRIBUTIONS.

These transactions affect accounting which will eventually make their way to the GL (when the Receivables Transfer to GL program is run). The accounting transactions are generated in RA_CUST_TRX_LINE_GL_DIST and AR_RECEIVABLE_APPLICATIONS.

Windows command to find equivalent directory name without space

You may occasionally run into an issue with OBIEE where it cannot locate the Java directories or files.  This is usually because Java has been installed in its default location under “C:\Program Files\…”.  This post may be helpful in resolving this issue.

At times, some OBIEE processes or programs do not like the spaces in directory or file names.  So, for example, you may get an error because the program does not locate the files under the “C:\Program Files\…” directory (due to the space in the name).

To resolve this, you will need to update the necessary Environment Variables to use the no-space equivalent of the windows directory.  To find the equivalent no-space name,  from your windows command prompt, go to the relevant directory (for example … cd C:\) and enter … dir /X (as shown below).


Notice to the left of the regular directory name, there is another no-space name.  For example, “Program Files” has a no-space equivalent of PROGRA~1.

Now update all the relevant OBIEE related Environment Variables to use this no-space name instead of the regular name (see example below).


Workaround for installing OBIEE10g on Windows7

It has been a while since I have had to install OBIEE 10g.  But recently I needed to install it on a Windows 7 laptop.

When installing OBIEE 10g on Windows 7, you may run into the following error.  This post may help you to get around this issue.


The error reads:
Oracle Business Intelligence is not supported on this Windows version. Oracle Business Intelligence is only supported on Windows XP x86, Windows 2003 x86, Windows 2003 AMD64, Windows 2003 EM64T, Windows Vista x86, Windows Vista AMD64, Windows Vista EM64T, and Windows 2000 x86.

There is a workaround that will allow you to complete the install. Please follow these steps:

– From within Windows Explorer, right-click the setup.exe file and choose Properties. 
– Select the Compatability tab, and go to the “Compatability mode” section.
– Check the check-box beside “Run this program in compatibility mode for:”
– And select “Windows Vista” from the drop down box.
– Save your change and then re-run the setup.exe program.

This time you should get past where you encountered the previous message that prevented you from installing the software.