ERP Knowledge Base

Oracle Financials Functional Basics

2007-04-18T19:53:00.000+05:30

This article gives a brief overview of the core Oracle Financial modules and how they interact or exchange information.

The Oracle Financials family of applications is designed to capture the key financial transactions in a business and to generate financial statements and other useful reports from the same. With the addition of Financial Daily Business Intelligence, it also gives the manager revenue and expense information on a daily basis.

The Oracle Financial family includes the following core modules:

General ledger
Payables
Receivables
Assets

General Ledger: is the central repository of all financial transactions in your business. It is integrated with other financial application that generates financial data and, it can also be integrated with other application families like Manufacturing, Order Management and Human Resource etc to capture the financial data generated from them. Other applications in Oracle financials that send financial information to the general ledger are usually referred as Sub ledgers (E.g.: Asset, Payables, Receivables etc.). General Ledger generates the key financial reports like financial income statement, balance sheet etc. The key function of this module is to manage Journal entries, Budget Information and account balances.

Payables: is the core module that manages the enterprise spending. The payables module process the supplier invoices, automate the payment of supplier invoice, perform accounting of the invoice and payment information and transfer the accounting information to the general ledger module. It is fully integrated with the general ledger module and also performs the back end processing for Oracles Procure to Pay and Travel and expense management solutions provided by Oracle. This module can also be integrated to the application of other families like Procurement (eg. Purchasing), Human Resource Management for invoice and payment management.

Receivables: helps to manage the customer invoices and do the exact opposite function as that of Payables. It automates the processing of customer invoices and cash receipts. In simple words Payables manages what goes out of your hand (bills/invoices that you need to pay) and Receivables manages what comes in (customer bills/invoices). The key functions of Receivables are processing of customer invoices and cash receipts and sending the accounting information from the same to general ledger.

Assets: as the names implies, manages the assets of your business. It is fully integrated with Payables and General Ledger modules. The key function includes Asset addition, depreciation and transfer of accounting information related to asset management to general ledger module.

Make the best use of Depreciation Rollback!

2007-03-15T15:24:00.000+05:30

Depreciation process in asset, includes lot of complex calculations and we might want to check if the Depreciation calculated is accurate before the same is posted to Journal.
One good new is, starting from 11i, depreciation rollback feature is introduced. If you run the depreciation without closing the period, you can rollback the same if some descrepencies are identified.
The following sequence of asset depreciation process can be followed, to make the best use of depreciation rollback feature.
1. Run Depreciation (without closing the period).
2. Run Create Journal Entries - Standard (FAPOST).
3. Review FA reports and verify results are as expected.
4. Review the FA journals in GL to verify results are as expected.

If everything is correct:
5. Resubmit Depreciation (close the period).
6. Do not run Create Journal Entries - Standard again.
7. Post the FA journals already created in GL from #2.

If corrections are necessary in FA:
8. Run Rollback Journal Entries.
9. Run Rollback Depreciation.
10. Make the necessary corrections in FA.
11. Repeat the process starting with #1.

Important : Depreciation cannot be rolled back without rolling back journals first. If this is done, Rollback Depreciation will error with message to rollback Create Journals.
The period should not be closed until the FA subledger is verified accurate and complete.

Setting Up the Folder Block

2007-03-05T10:12:00.000+05:30

Folder Block is a feature provided by Oracle, to customize the data displayed in a form form. You can define a query, customize the layout of the form columns, and then save the query and layout in a folder definition that can be retrieved later.

To define query criteria for a folder:

Run a query, either by using the Find command or Query By Example.
Save your query criteria by choosing Save As from the Folder menu.
Enter a name for the folder.
Select Always, Never, or Ask Each Time from the Autoquery option group to definehow frequently to perform the saved query.
Select the Open as Default check box to set this folder definition as the defaultevery time you navigate to this form.
Select the Public check box to allow other users to access your folderdefinition. Selecting Public does not enable other users to modify your folder.
Select the Include Query check box to include your query in the folder definition. Ifyou do not select this check box, only the folder layout is saved.

Customizing the Layout of a FolderUse the Folder menu or the Folder Tools window to customize the folder layout. Somelayout changes can be made using the mouse. For example:

Change the width of a column by dragging the column’s border.
Change the sequence of a column by dragging the column’s heading to a newposition.
Change the name of a column by using the right mouse button on the column’sheading. A window prompts you for the new name.

To access the Folder Tools window:

Select the Folder Tools toolbar icon, or choose Folder Tools from the Folder menu.

Understanding the Oracle File System

2007-03-04T12:29:00.000+05:30

Before you start to explore the apis and library codes , that define your Oracle Application product to understand it in detail or debug your custom code, this basic overview should help you to look at the right place in Oracle File system.

If you are techno functional person, who have access to several instances of Oracle apps, then in order to identify the database you need to dig in, go to
Menu->Help-> Oracle Application to find the details.

Once you identify the box, the most important file that stores the informaton of various path corresponding to the different product installed is .env.For example, APPL_TOP identifies the top level directory path of Oracle Application and FA_TOP defines the top level directory of the product, Oracle Fixed Assets and so on. Now if you are extending the functionality of Oracle ERP, by defining a new product, the first step of doing the same would be to create an entry here for your top level directory.

So APPL_TOP is the topmost directory in Oracle Application.Each product will have its own sub directory.Since products can exist at different version levels, the version is typically reflected in the subdirectory name.

Important Note: Multiple releases and product versions cannot exist in a single APPL_TOP directory.

Inside the Product Subdirectory:

Following are some of the key sub directories in the product Directory that can be of interest, while understanding the product, and extending its functioanility:

forms: It ontains Oracle Forms files. Oracle Forms may be portable source files (.fmb files) or generated runtime files (.fmx files). The installation utility generates form files by converting the .fmb source file to.fmx runtime files. The source files are stored in AU_TOP/forms sogeneration of runtime files can be done more easily.A subdirectory exists for the language(s) installed. This subdirectory isnamed according to the language, e.g., /US for American English forms,/D for German forms, /F for French forms.

include: It contains header (.h) files. These files may be required by the files contained in the lib directory for the relinking process. Not all products have an include directory.

lib: At some time, you may need to relink Applications programs, for example if you upgrade the Oracle8i server. The lib subdirectory contains files pertinent to the process of relinking Applications programs:object files: (.o files), one for each C program to relink.library file: (.a file) the compiled C code common to that product’sprograms.makefile: (.mk file) specifying how to relink the .o file(s) with the .a file(s)to create the newly linked C programs.

patch :Any updates to Oracle Applications data or data model use a directory
named patch to store the patch files. Patch files are grouped by release

For more details on the directory structure of the latest release you may refer the
R12 Oracle Applications Concepts

Oracle HRMS Form Trace

2007-03-04T08:26:00.000+05:30

The following instrcutions help to setup HRMS Form Trace:

Log into the application, using HRMS Responsibility and Security Group the end user specifies, for the instance you wish to perform a trace for.
Navigate to the form where the trace needs to occur. Determine if the HR Trace option is already available via the Tools menu. If unavailable, do the following:
Select Help->Diagnostics->Examine. Set Block to “$ENVIRONMENT$”, Field to “HRDBGOPT”, and Value to “ON”.
(Note: The Field value “HRDBGOPT” will not be found in the drop down list of values. Simply type it in the Field text box directly. This will result in an error which you should disregard. )
Click OK, then exit and re-enter the form. (The HR Trace option should now be available via the Tools menu.)
Select ToolsHR Trace . Click on Trace File and note down the trace file given, then close the HR Trace pop-up window by clicking on the X button.
Perform the function for which you needed a trace.
You can go to ToolsHRTrace and then click “None” to switch off the trace.
Turn off the trace immediately as you get the error to easily nail the error.

Remember that trace is always costly and will affect performance , and also the log file will get pretty big, if you do not turn off the trace in time!

Implementing Oracle Apps Using AIM

2007-03-02T15:48:00.000+05:30

AIM or Application Implementation Method is a proven approach for implementing Oracle Applications.

AIM is comprised of well-defined processes that can be managed in several ways to guide you through an application implementation project. AIM provides the tools needed to effectively and efficiently plan, conduct, and control project steps to successfully implement new business systems.

The following diagram gives an overview of AIM Methodlogy. Each rectangle in the picture represents different phases of the project and different project tasks are grouped into processes which are listed vertically in the diagram.

The six phases of the projects are defined as:

Defintion : The requirement and business objective is defined

Operation Analysis : Analyze the existing operation,and identify the gap between standard application functionality and client requirement

Design : Develop detailed design and solution

Build : coding and testing of all customization

Transition : deploying the system to the client

Production : Go Live where implementation hand over the project to the support team!

AIM also comes with over 150 documentation templates that can be used in various phases of the project by various processes. These documentation templates are widely used as a standard in the Oracle Application implementation.

Following are the list of key templates used in various processes:

Business Process Architecture (BP)
BP.010 Define Business and Process Strategy
BP.020 Catalog and Analyze Potential Changes
BP.030 Determine Data Gathering Requirements
BP.040 Develop Current Process Model
BP.050 Review Leading Practices
BP.060 Develop High-Level Process Vision
BP.070 Develop High-Level Process Design
BP.080 Develop Future Process Model
BP.090 Document Business Procedure

Business Requirements Definition (RD)
RD.010 Identify Current Financial and Operating Structure
RD.020 Conduct Current Business Baseline
RD.030 Establish Process and Mapping Summary
RD.040 Gather Business Volumes and Metrics
RD.050 Gather Business Requirements
RD.060 Determine Audit and Control Requirements
RD.070 Identify Business Availability Requirements
RD.080 Identify Reporting and Information Access Requirements

Business Requirements Mapping
BR.010 Analyze High-Level Gaps
BR.020 Prepare mapping environment
BR.030 Map Business requirements
BR.040 Map Business Data
BR.050 Conduct Integration Fit Analysis
BR.060 Create Information Model
BR.070 Create Reporting Fit Analysis
BR.080 Test Business Solutions
BR.090 Confirm Integrated Business Solutions
BR.100 Define Applications Setup
BR.110 Define security Profiles

Application and Technical Architecture (TA)
TA.010 Define Architecture Requirements and Strategy
TA.020 Identify Current Technical Architecture
TA.030 Develop Preliminary Conceptual Architecture
TA.040 Define Application Architecture
TA.050 Define System Availability Strategy
TA.060 Define Reporting and Information Access Strategy
TA.070 Revise Conceptual Architecture
TA.080 Define Application Security Architecture
TA.090 Define Application and Database Server Archtecture
TA.100 Define and Propose Architecture Subsystems
TA.110 Define System Capacity Plan
TA.120 Define Platform and Network Architecture
TA.130 Define Application Deployment Plan
TA.140 Assess Performance Risks
TA.150 Define System Management Procedures

Module Design and Build (MD)
MD.010 Define Application Extension Strategy
MD.020 Define and estimate application extensions
MD.030 Define design standards
MD.040 Define Build Standards
MD.050 Create Application extensions functional design
MD.060 Design Database extensions
MD.070 Create Application extensions technical design
MD.080 Review functional and Technical designs
MD.090 Prepare Development environment
MD.100 Create Database extensions
MD.110 Create Application extension modules
MD.120 Create Installation routines

Data Conversion (CV)
CV.010 Define data conversion requirements and strategy
CV.020 Define Conversion standards
CV.030 Prepare conversion environment
CV.040 Perform conversion data mapping
CV.050 Define manual conversion procedures
CV.060 Design conversion programs
CV.070 Prepare conversion test plans
CV.080 Develop conversion programs
CV.090 Perform conversion unit tests
CV.100 Perform conversion business objects
CV.110 Perform conversion validation tests
CV.120 Install conversion programs
CV.130 Convert and verify data

Documentation (DO)
DO.010 Define documentation requirements and strategy
DO.020 Define Documentation standards and procedures
DO.030 Prepare glossary
DO.040 Prepare documentation environment
DO.050 Produce documentation prototypes and templates
DO.060 Publish user reference manual
DO.070 Publish user guide
DO.080 Publish technical reference manual
DO.090 Publish system management guide

Business System Testing (TE)
TE.010 Define testing requirements and strategy
TE.020 Develop unit test script
TE.030 Develop link test script
TE.040 Develop system test script
TE.050 Develop systems integration test script
TE.060 Prepare testing environments
TE.070 Perform unit test
TE.080 Perform link test
TE.090 perform installation test
TE.100 Prepare key users for testing
TE.110 Perform system test
TE.120 Perform systems integration test
TE.130 Perform Acceptance test

How Date Tracking Works in Oracle

2007-03-02T02:42:00.000+05:30

This article details the table structure required to implement the date track feature and other technical details. You may refer the article :

Understanding Date Track Feature , to know more about this feature.

A DateTracked (DT) record is what the application and the user see: a single DT record for each key value. However, this DT record may change over time, so it may correspond to one or more physical rows in the database. The history for the record is held by storing a row when the record is created, and an extra row every time the record changes. To control these rows, every DateTracked table must include these columns:

EFFECTIVE_START_DATE DATE NOT NULL
EFFECTIVE_END_DATE DATE NOT NULL

The effective start date indicates when the record was inserted. The effective end date indicates when the record was deleted or updated. A deleted record has the highest end date of all the rows with that key, but for an updated record there will be at least one row for this key with a higher effective end date.
As time support is not provided, the effective start date commences at 0000 hours and the effective end date finishes at 2359 hours. This means that a DT record can change at most once per day.

Example

The table above shows the physical table after the user has done the following:
Set the effective date to 12-MAR-1989. Inserted record for SMITH.
Set the effective date to 20-JUL-1989. Updated SMITH record with new salary.
Set the effective date to 21-JUL-1989. Again updated SMITH record with new salary.
Set the effective date to 1-DEC-1989. Deleted record for SMITH.

The table below shows what the user sees on querying the SMITH record at different effective dates.

Because the primary key column in the table is no longer unique, any indexes on the table that included the primary key column must now also include the EFFECTIVE_START_DATE and EFFECTIVE_END_DATE columns.

List of DateTracked Tables

To get a list of the DateTracked tables used in Oracle Human Resources, select from the data dictionary where the table name is like Application Short Name%F. Substitute in the HRMS application short code you are interested in (such as PER or BEN). For each of the DateTracked tables there is a DateTracked view called 'TABLE NAME' and a synonym pointing to the full table called 'TABLE NAME_F'

Creating a DateTracked Table and View

The previous section described the table structure of a DateTracked table. This section describes the steps to go through to create a DateTracked table and view.
You must use the following nomenclature for DateTracked tables:
Base table: 'TABLE NAME_F '
DateTracked view: 'TABLE NAME '
In addition to the DateTracked view, there is another view that shows the rows in the table as of SYSDATE. The name of this view is derived by replacing the _F at the end of the table name by _X.

Example
To incorporate DateTrack on to an existing table called EMPLOYEES, follow these steps:
1. Create a new table called EMPLOYEES_F that is identical to EMPLOYEES but with the columns EFFECTIVE_START_DATE and EFFECTIVE_END_DATE added. Normally you would set the EFFECTIVE_START_DATE and EFFECTIVE_END_DATE columns to the maximum range. CREATE TABLE EMPLOYEES_F AS SELECT EMPLOYEES.*, TO_DATE('01-01-0001','DD-MON-YYYY') EFFECTIVE_START_DATE, TO_DATE('31-12-4712','DD-MON-YYYY') EFFECTIVE_END_DATE FROM EMPLOYEES; ALTER TABLE EMPLOYEES_F MODIFY (EFFECTIVE_START_DATE NOT NULL, EFFECTIVE_END_DATE NOT NULL);
Remove the old table. DROP TABLE EMPLOYEES
If the old table already has the two new columns, just rename it. RENAME EMPLOYEES TO EMPLOYEES_F;

2. Create the New Unique Indexes of the DateTracked Table by dropping the old indexes, creating the new unique indexes as old unique index + EFFECTIVE_START_DATE + EFFECTIVE_END_DATE, and creating the new non-unique indexes the same as the old non-unique indexes.

3. Create a DateTracked view called EMPLOYEES. This view uses the entry in FND_SESSIONS for the current user effective id for the effective date. CREATE VIEW EMPLOYEES AS SELECT * FROM EMPLOYEES_F WHERE EFFECTIVE_START_DATE <= (SELECT EFFECTIVE_DATE FROM FND_SESSIONS WHERE FND_SESSIONS.SESSION_ID = USERENV('SESSIONID')) AND EFFECTIVE_END_DATE >= (SELECT EFFECTIVE_DATE FROM FND_SESSIONS WHERE FND_SESSIONS.SESSION_ID = USERENV('SESSIONID'))

4. To create the view EMPLOYEES_X based on the table EMPLOYEES_F, use the following SQL: CREATE VIEW EMPLOYEES_X AS SELECT * FROM EMPLOYEES_F WHERE EFFECTIVE_START_DATE <= SYSDATE AND EFFECTIVE_END_DATE >= SYSDATE

Restricting Datetrack Options Available to Forms Users

When a user edits or deletes a datetracked record, the system displays a window asking the user what type of update or deletion to perfom. Before it displays this window, the system calls a custom library event (called DT_SELECT_MODE). It passes in the list of buttons that DateTrack would normally display (such as Update and Correction).

Your custom code can restrict the buttons displayed. If necessary, it can require that the user is given no update or delete options, and receives an error message instead. However, it cannot display buttons that DateTrack would not normally display for the entity, effective date, and operation the user is performing.

If the user chooses Update and future changes exist, the custom library event point may be executed a second time so your custom code can determine whether the user is given the two update options: Insert and Replace.

Understanding the Date Track Feature

2007-03-02T02:16:00.000+05:30

Date Track Feature is extensively used in many of the Oracle HRMS forms to view/modify the details within the form based on the snapshot of the information which is dependent on time. It adds dimension of time to an application's database. For example, querying an employee's annual salary with an effective date of 27-JUN-2006 might give a different value than a query with an effective date of 06-SEP-2006. Only a single record depending on the date specified in the effective Date field, will be retrieved within the form.

UPDATE/DELETE/INSERT in Date tracked Blocks:

UPDATE:

When a user first alters a field in a DateTracked block in the current Commit unit, he or she sees a choice of Update prompts as follows:

UPDATE - Updated values are written to the database as a new row, effective from today until 31-DEC-4712. The old values remain effective up to and including yesterday.
CORRECTION - The updated values override the old record values and inherit the same effective dates.

If the user selects UPDATE, DateTrack checks whether the record being updated starts today. If it does, a message warns that the previous values will be lost (because DateTrack can only store information on a day by day basis). DateTrack then changes the mode for that record to CORRECTION.
Next, if UPDATE was selected, DateTrack checks whether the record being updated has already had future updates entered. If it has been updated in the future, the user is further prompted for the type of update, as follows:

UPDATE_CHANGE_INSERT (Insert) - The changes that the user makes remain in effect until the effective end date of the current record. At that point the future scheduled changes take effect.
UPDATE_OVERRIDE (Replace) - The user's changes take effect from now until the end date of the last record in the future. All future dated changes are deleted.

DELETE:

When deleting a record, the user is prompted for the type of delete. There are four options, as follows:

DELETE (End Date) - This is the DateTracked delete. The record that the user is currently viewing has its effective end date set to today's date. The record disappears from the form although the user can requery it.
ZAP (Purge) - This is the total delete. All records matching the key value, whatever their date stamps, are deleted.
FUTURE CHANGE (All) - This choice causes any future dated changes to the current record, including a future DateTracked delete, to be removed. The current record has its effective end date set to 31-DEC-4712.

The record can again be displayed by requerying.

DELETE NEXT CHANGE (Next Change) - This choice causes the next change to the current DateTracked record to be removed.

Where another future dated DateTracked row exists for this record, it is removed and the current row has its effective end date set to the effective end date of the deleted row.
Where no future DateTracked row exists, but the current row has an end date other than 31-DEC-4712, then this option causes the effective end date to be set to 31-DEC-4712. This means that a date effective end is considered to be a change.
Notice that this option again removes the current row from the form, though it can be displayed again by requerying.

INSERT:

The user is not prompted for any modes when inserting a record. The effective start date is always set to today (Effective Date). The effective end date is set as late as possible. Usually this is 31-DEC-4712, although it can be earlier especially when the record has a parent DateTracked record.

To know about the underlying table structure and details of implementing Date Track Feature, please refer:

How Date Tracking Works in Oracle

Task Flow in Oracle HRMS

2007-03-02T01:13:00.000+05:30

Task flow is a key feature provided by Oracle HRMS to group related windows or forms to perform a task.
For example, to hire an employee, you need to go from the People window, to the Address window, to the Special Information window, to the Assignment window, and so on. You can link these windows together using a task flow so that users can choose a button to bring up each window in appropriate sequence without returning to the menu.

You can create taskflow using the following methods:
1.Using Forms
2. Using Work Flow tool

Defining Task Flow Using Forms

Create Task Flow Nodes using Define Task Flow Node Window.
Create Task Flows using Define Task Flow Window.
You can also query and copy and existing task flow and customize it by saving it in different name. Oracle provides many pre defined task flows like Benefit WorkBench for the purpose.

Remember:

Only certain windows are valid first windows.Following are a list of the windows you can choose as the first window in your task flow:
• A Maintenance window created from a People Management template
• People and Assignment (PERWSHRG)
• People (PERWSEPI)
• People Folder (PERWSFPE)
• Assignments Folder (PERWSFAS)
• Assignment (PERWSEMA)
• Job (PERWSDJT)
• Position (HRWSPSF)
• Position (non HRMS users) (PERWSDPO)
• Location (PERWSLOC)
• Tax Balance (PAYUSTBA)
The windows require certain context from which it can be opened. For example Address window cannot be opened from Assignment window.

Nailing the ORA Error

2007-02-26T09:19:00.000+05:30

The next time you get an ORA error,replace the XXXXX in the following link with the error number
http://ora-XXXXX.ora-code.com

and you will get a useful description of the error, and sometimes the Action suggested might help you to actually nail the error instantly!

Digging out the Log File

2007-02-26T08:47:00.000+05:30

Get the Local Copy:

If you have the appropriate access, you would prefer to get the log file in your machine than viewing it from Oracle Apps Concurrent Manager.

I hope the following steps may help you:

1.Run the following query from back end:
select logfile_name, outfile_name from fnd_concurrent_requests where request_id = &request_id
2. If you do not know in which unix box your apps is running, please navigate to
Help - > Oracle Applications to get the information.

So once the file is located, I hope you know the basic commands to get it to your local machine.

View as HTML:
Another way you would prefer to see your Log is as an HTML page.

In the Apps Screen, click on Edit -> Preferences -> Profiles
Query for "Viewer%"

Set Viewer: Text to "Browser"
Set "Viewer: Application for Text" to "Browser"
Set "Viewer: Application for HTML" to "Browser"

(OR)

Once you run the Concurrent Request, follow the below steps

1) Click on "View log" for the particular request id.
2) Then you can click on Tools -> Copy File.

The contents of the log are then opened in a new explorer window.

Happy Debugging!