GL_SETS_OF_BOOKS

Stores information about the sets of books you define in your Oracle General Ledger application. Each row includes the set of books name, description, functional currency, and other information. This table corresponds to the Set of Books form.

GL_IMPORT_REFERENCES

Stores individual transactions from subledgers that have been summarized into Oracle General Ledger journal entry lines through the Journal Import process. You can specify the journal entry sources for which you want to maintain your transaction’s origin by entering ’Yes’ in the Import Journal References field of the Journal Sources form. For each source that has Import Journal References set to ’Yes’, Oracle General Ledger will populate GL_IMPORT_REFERENCES with one record for each transaction in your feeder system.

GL_DAILY_RATES

Stores the daily conversion rates for foreign currency transactions. It replaces the GL_DAILY_CONVERSION_RATES table. It stores the rate to use when converting between two currencies for a given conversion date and conversion type. Each row in this table has a corresponding inverse row in which the from and to currencies are switched.

For example, if this table contains a row with a from_currency of YEN, a to_currency of CND, a conversion_type of Spot, and a conversion_date of January 1, 1997, it will also contain a row with a from_currency of CND, a to_currency of YEN, a conversion_type of Spot, and a conversion_date of January 1, 1997.

In general, this row will contain a rate that is the inverse of the matching row. One should never insert directly into this table. They should instead insert into the DAILY_RATES_INTERFACE table. Data inserted into the GL_DAILY_RATES_INTERFACE table will be automatically copied into this table.

GL_JE_LINES

Stores the journal entry lines that you enter in the Enter Journals form. There is a one to many relationship between journal entries and journal entry lines. Each row in this table stores the associated journal entry header ID, the line number, the associated code combination ID, and the debits or credits associated with the journal line. STATUS is ’U’ for unposted or ’P’ for posted.

GL_PERIODS

Stores information about the accounting periods you define using the Accounting Calendar form. Each row includes the start date and end date of the period, the period type, the fiscal year, the period number, and other information. There is a one to many relationship between a row in the GL_PERIOD_SETS table and rows inthis table.

GL_JE_HEADERS

Stores journal entries. There is a one to many relationship between journal entry batches and journal entries. Each row in this table includes the associated batch ID, the journal entry name and description, and other information about the journal entry. This table corresponds to the Journals window of the Enter Journals form. STATUS is ’U’ for unposted, ’P’ for posted. Other statuses indicate that an error condition was found. CONVERSION_FLAG equal to ’N’ indicates that you manually changed a converted amount in the Journal Entry Lines zone of a foreign currency journal entry. In this case, the posting program does not re convert your foreign amounts. This can happen only if your user profile option MULTIPLE_RATES_PER_JE is ’Yes’. BALANCING_SEGMENT_VALUE is null if there is only one balancing segment value in your journal entry. If there is more than one, BALANCING_SEGMENT_VALUE is the greatest balancing segment value in your journal entry.

GL_JE_BATCHES

Stores journal entry batches. Each row includes the batch name, description, status, running total debits and credits, and other information. This table corresponds to the Batch window of the Enter Journals form. STATUS is ’U’ for unposted, ’P’ for posted, ’S’ for selected, ’I’ for in the process of being posted. Other values of status indicate an error condition. STATUS_VERIFIED is ’N’ when you create or modify an unposted journal entry batch.

The posting program changes STATUS_VERIFIED to ’I’ when posting is in process and ’Y’ after posting is complete.

GL_BALANCES

Stores actual, budget, and encumbrance balances for detail and summary accounts. This table stores functional currency, foreign currency, and statistical balances for each accounting period
that has ever been opened.

ACTUAL_FLAG is either ’A’, ’B’, or ’E’ for actual, budget, or encumbrance balances, respectively. If ACTUAL_FLAG is ’B’, then BUDGET_VERSION_ID is required. If ACTUAL_FLAG is ’E’, then ENCUMBRANCE_TYPE_ID is required.

GL_BALANCES stores period activity for an account in the PERIOD_NET_DR and PERIOD_NET_CR columns. The table stores the period beginning balances in
BEGIN_BALANCE_DR and BEGIN_BALANCE_CR.

An account’s year to date balance is calculated as BEGIN_BALANCE_DR - BEGIN_BALANCE_CR + PERIOD_NET_DR PERIOD_NET_CR. Detail and summary foreign currency balances that are the result of posted foreign currency journal entries have TRANSLATED_FLAG set to ’R’, to indicate that the row is a candidate for revaluation.

For foreign currency rows, the begin balance and period net columns contain the foreign currency balance, while the begin balance and period net BEQ columns contain the converted functional currency balance. Detail foreign currency balances that are the result of foreign currency translation have TRANSLATED_FLAG set to ’Y’ or ’N’. ’N’ indicates that the translation is out of date (i.e., the account needs to be re translated). ’Y’ indicates that the translation is current.

Summary foreign currency balances that are the result of foreign currency translation have TRANSLATED_FLAG set to NULL. All summary account balances have TEMPLATE_ID not NULL. The columns that end in ADB are not used. Also, the REVALUATION_STATUS column is not used.

GL_CODE_COMBINATIONS

Stores valid account combinations foreach Accounting Flexfield structure within your Oracle General Ledger application. Associated with each account are certain codes and flags, including whether the account is enabled, whether detail posting ordetail budgeting is allowed, and others. Segment values are stored in the SEGMENT columns. Note that each Accounting Flexfield structure may use different SEGMENT columns within the table to store the flexfield value combination. Moreover, the SEGMENT columns that are used are not guaranteed to be in any order. The Oracle Application Object Library table FND_ID_FLEX_SEGMENTS stores information about which column in this table is used for each segment of each Accounting Flexfield structure. Summary accounts have SUMMARY_FLAG = ’Y’ and TEMPLATE_ID not NULL. Detail accounts have SUMMARY_FLAG = ’N’ and TEMPLATE_ID NULL.

Client Industry - Banking and Financial Institution

Summary One of my banking clients had a business need to enhance end of day reporting by using a new "processing date" field, by preventing posting of future dated transactions, and storing daily balances to support end of day reporting requirements.

Standard Functionality Limitations since Oracle General Ledger (GL) is a real-time system, the standard posting functionality will update the account balances of detail and summary accounts. When you post to an earlier effective date or open period, actual balances roll forward through the latest open period. If you post a journal entry into a prior year, Oracle GL adjusts your retained earnings balance for the effect on your income and expense accounts. End of day reporting is complicated with a single instance and server time stamp worldwide.

Business Case Banks and Financial Institutions in US are regulated Corporations subject to daily, weekly, monthly, quarterly and annual report requirements as defined by the SEC, the Federal Reserve, and IRS. Additionally, the global branches and subsidiaries are subject to foreign regulatory agencies. These banks requires that the Oracle Financials Application's GL support the requirement that transaction processing be cut off based on an end of day and reporting generated as a result of that end of day. Currently, the standard functionality of Oracle GL does not support the concept of end of day. The Open/Close is controlled by Accounting Periods in months, not days.

Solution Overview A custom solution was developed incorporating three types of changes that are required to support end of day processing requirements:

■ Add Processing Date
■ Store Daily Balances
■ Prevent future dated transactions from affecting end of day balances

The commonly used tables in GL modules are

GL_SETS_OF_BOOKS
GL_IMPORT_REFERENCES
GL_DAILY_RATES
GL_JE_LINES
GL_PERIODS
GL_JE_HEADERS
GL_JE_BATCHES
GL_BALANCES
GL_CODE_COMBINATIONS

GL_SETS_OF_BOOKS
Stores information about the sets of books you define in your Oracle General Ledger application. Each row includes the set of books name, description, functional currency, and other information. This table corresponds to the Set of Books form.

GL_IMPORT_REFERENCES
Stores individual transactions from subledgers that have been summarized into Oracle General Ledger journal entry lines through the Journal Import process. You can specify the journal entry sources for which you want to maintain your transaction’s origin by entering ’Yes’ in the Import Journal References field of the Journal Sources form. For each source that has Import Journal References set to ’Yes’, Oracle General Ledger will populate GL_IMPORT_REFERENCES with one record for each transaction in your feeder system.

GL_DAILY_RATES
Stores the daily conversion rates for foreign currency transactions. It replaces the GL_DAILY_CONVERSION_RATES table. It stores the rate to use when converting between two currencies for a given conversion date and conversion type. Each row in this table has a corresponding inverse row in which the from and to currencies are switched.

For example, if this table contains a row with a from_currency of YEN, a to_currency of CND, a conversion_type of Spot, and a conversion_date of January 1, 1997, it will also contain a row with a from_currency of CND, a to_currency of YEN, a conversion_type of Spot, and a conversion_date of January 1, 1997.
In general, this row will contain a rate that is the inverse of the matching row. One should never insert directly into this table. They should instead insert into the DAILY_RATES_INTERFACE table. Data inserted into the GL_DAILY_RATES_INTERFACE table will be automatically copied into this table.

GL_JE_LINES
Stores the journal entry lines that you enter in the Enter Journals form. There is a one–to–many relationship between journal entries and journal entry lines. Each row in this table stores the associated journal entry header ID, the line number, the associated code combination ID, and the debits or credits associated with the journal line. STATUS is ’U’ for unposted or ’P’ for posted.

GL_PERIODS
Stores information about the accounting periods you define using the Accounting Calendar form. Each row includes the start date and end date of the period, the period type, the fiscal year, the period number, and other information. There is a one–to–many relationship between a row in the GL_PERIOD_SETS table and rows inthis table.

GL_JE_HEADERS
Stores journal entries. There is a one–to–many relationship between journal entry batches and journal entries. Each row in this table includes the associated batch ID, the journal entry name and description, and other information about the journal entry. This table corresponds to the Journals window of the Enter Journals form. STATUS is ’U’ for unposted, ’P’ for posted. Other statuses indicate that an error condition was found. CONVERSION_FLAG equal to ’N’ indicates that you manually changed a converted amount in the Journal Entry Lines zone of a foreign currency journal entry. In this case, the posting program does not re–convert your foreign amounts. This can happen only if your user profile option MULTIPLE_RATES_PER_JE is ’Yes’. BALANCING_SEGMENT_VALUE is null if there is only one balancing segment value in your journal entry. If there is more than one, BALANCING_SEGMENT_VALUE is the greatest balancing segment value in your journal entry.

GL_JE_BATCHES
Stores journal entry batches. Each row includes the batch name, description, status, running total debits and credits, and other information. This table corresponds to the Batch window of the Enter Journals form. STATUS is ’U’ for unposted, ’P’ for posted, ’S’ for selected, ’I’ for in the process of being posted. Other values of status indicate an error condition. STATUS_VERIFIED is ’N’ when you create or modify an unposted journal entry batch. The posting program changes STATUS_VERIFIED to ’I’ when posting is in process and ’Y’ after posting is complete.

GL_BALANCES
Stores actual, budget, and encumbrance balances for detail and summary accounts. This table stores functional currency, foreign currency, and statistical balances for each accounting period that has ever been opened. ACTUAL_FLAG is either ’A’, ’B’, or ’E’ for actual, budget, or encumbrance balances, respectively. If ACTUAL_FLAG is ’B’, then BUDGET_VERSION_ID is required. If ACTUAL_FLAG is ’E’, then ENCUMBRANCE_TYPE_ID is required. GL_BALANCES stores period activity for an account in the PERIOD_NET_DR and PERIOD_NET_CR columns. The table stores the period beginning balances in BEGIN_BALANCE_DR and BEGIN_BALANCE_CR.

An account’s year–to–date balance is calculated as BEGIN_BALANCE_DR – BEGIN_BALANCE_CR + PERIOD_NET_DR – PERIOD_NET_CR. Detail and summary foreign currency balances that are the result of posted foreign currency journal entries have TRANSLATED_FLAG set to ’R’, to indicate that the row is a candidate for revaluation.

For foreign currency rows, the begin balance and period net columns contain the foreign currency balance, while the begin balance and period net BEQ columns contain the converted functional currency balance. Detail foreign currency balances that are the result of foreign currency translation have TRANSLATED_FLAG set to ’Y’ or ’N’. ’N’ indicates that the translation is out of date (i.e., the account needs to be re–translated). ’Y’ indicates that the translation is current.

Summary foreign currency balances that are the result of foreign currency translation have TRANSLATED_FLAG set to NULL. All summary account balances have TEMPLATE_ID not NULL. The columns that end in ADB are not used. Also, the REVALUATION_STATUS column is notused.

GL_CODE_COMBINATIONS
Stores valid account combinations foreach Accounting Flexfield structure within your Oracle General Ledger application. Associated with each account are certain codes and flags, including whether the account is enabled, whether detail posting ordetail budgeting is allowed, and others. Segment values are stored in the SEGMENT columns. Note that each Accounting Flexfield structure may use different SEGMENT columns within the table to store the flexfield value combination. Moreover, the SEGMENT columns that are used are not guaranteed to be in any order. The Oracle Application Object Library table FND_ID_FLEX_SEGMENTS stores information about which column in this table is used for each segment of each Accounting Flexfield structure. Summary accounts have SUMMARY_FLAG = ’Y’ and TEMPLATE_ID not NULL. Detail accounts have SUMMARY_FLAG = ’N’ and TEMPLATE_ID NULL.

When the Auto invoice program is run, data is populated into the tables listed below.

1. RA_BATCHES_ALL
2. RA_CUSTOMER_TRX_ALL
3. RA_CUSTOMER_TRX_LINES_ALL
4. RA_CUST_TRX_LINE_GL_DIST_ALL
5. RA_CUST_TRX_LINE_SALESREPS_ALL
6. AR_RECEIVABLE_APPLICATIONS_ALL
7. AR_PAYMENT_SCHEDULES_ALL
8. AR_PAYMENT_SCHEDULES_ALL
9. RA_INTERFACE_ERRORS_ALL

Query to check Invoices in Error

SELECT
,ria.line_type
,ria.description
,ria.currency_code
,ria.amount
,ria.trx_date
,ria.quantity
,ria.quantity_ordered
,ria.sales_order
,ria.sales_order_line
,ria.sales_order_date
,ria.inventory_item_id
,riea.interface_line_id
,riea.MESSAGE_TEXT
,riea.org_id
FROM
,ra_interface_errors_all riea
,ra_interface_lines_all ria
WHERE
,riea.interface_line_id = ria.interface_line_id
AND
,riea.org_id = ria.org_id

■ Complete manual payments and adjustments for the period (including cleanup of ‘Out of Balance’ batches)

■ Check and Complete final Receivables Interface from Order Management

■ Complete manual invoices, DEBIT Memos and credit memos

■ Run your final Auto Invoice process

■ Correct ALL Auto Invoice rejects and re-run

■ Clear all receipts from Cash Management

■ Apply receipts against Invoices and Debit Memos

■ Review the Unapplied Receipts Register

■ Reconcile receipts

■ Apply Credit memo to Debit Memo and Invoices

■ Approve or reject ALL Pending Adjustments

■ Complete or delete ALL ‘Incomplete’ invoices

■ Complete or delete ALL ‘Incomplete’ credit memos

■ Correct and post ALL open Lockbox batches (If Lock Box Implemented)

■ Print Dunning (Reminder) Letters (Optional)

■ Run Journal Entries Reports

■ Review Journal Entries reports for ‘unusual’ accounts and correct as needed

■ Run Period End Reports

■ Balance to the Aged Trial Balance

■ Post to the General Ledger using the period start date through the period end date - Verify all the steps.

■ Close the period - verify that there are no items in error

■ Open the next period and the next Future period

■ Post Journal Entries (In General Ledger)

■ Balance to the General Ledger (for the key accounts)

Using the Employee/Supervisor Relationship

Strengths
Allows flexibility of establishing approval groups around either jobs or positions.
Assuming Oracle Human Resources (HR) is installed and the data is being maintained, Purchasing uses the supervisor name on the employee which should be entered and maintained for HR purposes already.

Weaknesses
Documents may not travel by the employee/supervisor relationship for approval purposes, but take a different path in part or entirely. Documents can get hung up more frequently due to the volume of personnel changes that typically occur in companies. Higher volumes of documents need to be re-routed using the Forward-To settings. Recall that the first name alphabetically appears as the default.

Using Position Approval Hierarchies

Strengths
Allows approval paths to be set up that follow the actual flow of documents for approval. Assuming Human Resources is installed and the data is being maintained, Purchasing can make an employee representation of the position approval hierarchy by simply running the Fill Employee Hierarchy process. This will allow changes occurring in personnel assignments to be updated in the position approval hierarchy. Documents can get hung up less frequently due to the lower volume of structural hierarchy changes compared to the higher volume of personnel changes that typically occur in companies. Lower volumes of documents need to be re-routed using the Forward To settings, especially when there are multiple holders in a particular job or position. Recall that the first name alphabetically appears as the default when there are multiple holders.

Weaknesses
Cannot be used with jobs, but must be used with positions.

What if there is more than one holder in a job or position?
Multiple holders in the same job or position can only occur if Oracle Human Resources is installed. If only Oracle Purchasing is installed, you can assign only one employee to each job or position. If multiple holders exist when using position approval hierarchies, Oracle Workflow will route the document to the holder based on alphabetical order. You can see who the system will route the document to by clicking the Forward radio button, overriding the defaulted name if needed.

What if there are changes to the position approval hierarchy?
Personnel changes are updated in the position approval hierarchy by running the Fill Employee Hierarchy process which reviews the employee record to determine the current position assignment. You will probably want to schedule this process to run on a frequent basis to ensure smooth processing of all approval requests. Structural changes to the position approval hierarchy require the hierarchy to be rebuilt from the point where the change occurs to the bottom of that branch. Documents are routed according to the hierarchy in effect at the time they are submitted for approval.

What if Human Resources were already implemented with jobs set up, but no positions?
Unless the decision to set up jobs only and no positions can be revisited, you will have to route all documents by the employee/supervisor relationship and maintain this data on the employee records.

What if the approver is unavailable for an extended period?
Oracle Purchasing has the ability to automatically forward documents when users do not respond to notifications. This tool should be used when possible to prevent documents from holding up business productivity. Oracle Workflow manages this functionality, typically set up to send a first and second reminder after pre-determined time periods before forwarding the notification to the next approver.

What if the document has a status of Pre-Approved?
The status of Pre-Approved is the outcome of a person forwarding a document for approval even though the forwarding person has the necessary authority to approve it. The document may have been forwarded by mistake or for business reasons. Once the person it was forwarded to approves the document, the status will be changed to Approved and subsequent actions such as receiving and invoicing can be completed.

What if there is no account range on an approval group assigned to a job or position?
By default, all accounts are excluded from an approval group and therefore documents governed by this approval group cannot be approved. You avoid this situation by ensuring that there is at least one Account Range Include rule on every Approval Group.

What if Workflow can’t find a supervisor to approve the document?
If this occurs, the person submitting the request for approval must forward the document to a different person in the list of values for Forward To. If this isn’t done, the document will be returned to an Incomplete status and a notification will be sent stating No Approver Found - Please Select a Forward To Employee. If using employee/supervisor relationship to determine approval paths, the list of values will include all active employees. If using position approval hierarchies to determine approval paths, the list of values will include all employees in the hierarchy selected.

What if a job or position has different authority levels for different document types?
You may have jobs or positions that can approve one document up to a specified dollar amount, while they can approve another document at a lower dollar amount. If this occurs, simply set up multiple approval groups with the rules properly defined for the differences and ensure that the right approval group is assigned to the correct document type.

What if documents flow employee/supervisor, but approval rules are needed?
Approval Groups can be used whether approval paths are determined by employee/supervisor relationships or by position approval hierarchies.

Set Following Profile Options as Required at User or Site

Personalize Self-Service Defn...........................= YES
FND: Personalization Region Link Enabled.........= YES
Disable Self-Service Personal..........................= NO (Only at Site)
FND: Diagnostics ...........................................= Yes

Bounce the Apache Server

Login to Application you can see the About This Page Link on all web pages

By Using About This Page link you can have almost all information of technology Stack

Step 1

Review the action history of the document having a problem. This is done in the Purchasing Summary screens – either Requisitions Requisitions Summary or Purchase Orders Purchase Order Summary. If you attempt to view the action history and you receive the following error:

APP-14288 This document is either incomplete or you do not have access to it

Then this represents that Workflow has not yet performed a submit into the action history table. The document approval manager performs the action of submitting a record into the Action History and therefore is required to be running. It should be confirmed that the document approval manager is running.

Step 2

Notification Summary – review the Notification Summary for the user that submitted the document. Are there any notifications present? Because online form messaging alerts are now present in the form of notifications, it is imperative that the summary be reviewed for any informative notifications explaining in more detail as to the problem.

Step 3

Confirmation that the Workflow Background Process has been run via the System Administrator responsibility. This is done via Requests Run within the System Administrator Responsibility. The parameters should be the Item Type – PO or Requisition Approval and then Processed Deferred = Yes, Process Time Out = Yes. Process Stuck = Yes if the Process Stuck parameter is available.

Step 4

Ensure that all tables contain the necessary space to record the Workflow processing data. As users submit a document for Approval and the Workflow process is called, data is recorded into a series of Workflow tables. To quickly confirm whether the tables are having space limitations or not? Run the following in SQL:

select value
from v$parameter
where name like ‘background_dump_dest’;

The value returned by this SQL statement represents the location of the alert log on the database server, For the instance in question. Please ask the Database Administrator to review the latest entries into the alert.log to confirm if any errors are being thrown with regards to table space. The most common scenario that has been linked to table space, is when a user hits the ‘Approve’ button in the approval screen, only to have nothing happen. There is no message returned stating document has been submitted for Approval, nothing happens. The hourglass comes for 1 second, and then leaves, and the approval form is still present. This is most likely a table space issue.

Step 5

Workflow Monitor from the Purchase Summary screens. Utilize the Workflow monitor to track the path which the document has taken during its submission to Workflow. Some important milestones to look for in the Workflow monitor are ‘Can Owner Approve’ and ‘Does Approver have Approval Authority’. Are there any processes that contain errors? Or does it appear that a process has stopped at a point that is incorrect?

Example is if a process stopped at a point that is not referencing a notification or some other request point for user interaction