Friday, January 31, 2014

GL Journal Profile Options

 

General Ledger Journal Profile options:

image

Journals: Allow Multiple Exchange Rates - Controls whether to allow multiple conversion rates within a journal entry.

Journals: Allow Posting During Journal Entry - Controls whether you can post a manual journal entry from the Enter Journals window.

Journals: Default Category - Specify the default category for manual journal entries.

Journals: Enable Prior Period Notification - Controls whether General Ledger notifies you when you are entering a journal for a prior period.

Journals: Mix Statistical and Monetary - Controls whether you can enter statistical amounts in the same journal line as monetary amounts.

Journals: Override Reversal Method - Controls whether you can override the specified default reversal method.

General Ledger - Journal Entry Sources and Categories usages

 

Using Journal Entry Sources and Categories:

image

(N) Setup > Journal > Sources

image

Journal entry sources indicate where your journal entries originate.

General Ledger supplies a list of predefined journal sources for journal entries that originate in Oracle subledger applications, such as Assets or Payables.

You can define your own journal sources for non-Oracle feeder systems.

For each journal source, specify whether to import detail reference information for summary journals imported from your Oracle subledger applications. This is required if you want to be able to drilldown to the original subledger transaction from balances in General Ledger.

With journal sources, you can:

• Define intercompany and suspense accounts for specific sources.

• Run the AutoPost program for specific sources.

• Import journals by source.

• Freeze journals imported from subledgers to prevent users from making changes to any journals that have been transferred to General Ledger from that source. This ensures that transactions from your subledger systems reconcile with those posted in General Ledger.

• Report on journals by source using the Foreign Currency Journals or General Journals reports.

If you have journal approval enabled for your ledger, you can use journal sources to enforce management approval of journals before they are posted.

If you are using average balance processing, select an effective date for your journal source. For more information, refer to the Oracle General Ledger User Guide or 11i General Ledger Financial Management Advanced Topic Average Balance Processing.

Journal Categories

(N) Setup > Journal > Categories

image

• Journal categories help you differentiate journal entries by purpose or type, such as accrual, payments, or receipts. When you create journal entries, you must choose the default or specify a category.

• Using categories, you can:

- Define intercompany and suspense accounts for specific categories.

- Use document sequences to sequentially number journals by category.

- Define journal categories for accruals and estimates. Use these categories when you define criteria for Auto Reverse and AutoPost.

• Journal categories appear in standard reports, such as the General Journals report. You can run reports by category, by source, or category and source. For example, for month end close, you might run a report listing all journals that were created for the period with a category of accruals. This way you can review the accrual entries created before finalizing your close.

Caution: Oracle General Ledger does not have a standard report showing Journal Import reference information. You must create a custom report to access this information.

R12 Web ADI Profile Options

Identifying Web ADI Profile Options

These profile options can be set to control specific Web ADI functions in your system.

image

Identifying Form Functions, Menus, and Responsibilities in Web ADI

image

Form Functions

This is a list of form functions that are automatically created. Note that integrators are not installed with Web ADI, but with their associated products.

Menus

The Desktop Integration Menu is created with the following prompts:

• Create Document (BNE_ADI_ CREATE_DOCUMENT)

• Define Layout (BNE_ADI_DEFINE_LAYOUT)

• Define Mapping (BNE_ADI_DEFINE_MAPPING)

• Manage Document Links (BNE_ADI_LOB_MANAGEMENT)

• Setup Options (BNE_ADI_SETUP_OPTIONS)

Define Style Sheet (BNE_ADI_DEFINE_STYLESHEET)

Responsibility

• The Desktop Integration responsibility is created with the Desktop Integration Menu.

General Ledger - Journal Entries Importing

Overview of Importing Journal Entries:
image
About Journal Import 

• Use Journal Import to integrate information from other applications such as payroll, accounts receivable, accounts payable and fixed assets with your General Ledger application. For each accounting period, you can import accounting data from these feeder systems, then review, update and post the journal entries. You can also use Journal Import to import historical data from your previous accounting system. General Ledger lets you import data from multiple interface tables. This enables you to customize interface tables to your specific requirements. Each particular source/group ID combination will only have data in one interface table at a time. Journal import will process data from one table at a time.

• To import subledger and feeder system data to General Ledger:
- Set up General Ledger to accept Journal Import data by defining your ledger, currencies, accounts, journal sources, and categories. You should also run the Optimizer program, and define your concurrent program controls.

- Export data from your feeder system and populate the GL_INTERFACE table. Note: If you use reporting currencies and Oracle subledger systems, you must post to General Ledger from each subledger multiple times. Post first using your primary subledger responsibility, which transfers amounts denominated in your functional currency. Post next using each of your subledger reporting responsibilities, which transfers amounts denominated in your reporting currencies.

- Run Journal Import. If your import program converts your journal entries from other sources into the required data format, and all of the data is valid in your General Ledger application, then Journal Import should run successfully the first time. However, if you load data into the GL_INTERFACE table which is not valid in your General Ledger application, Journal Import informs you of the specific errors on the Journal Import Execution Report. Note: If you use reporting currencies and Oracle subledger systems, and have chosen not to run Journal Import automatically when posting amounts to General Ledger from your subledgers, you must run Journal Import manually in your primary ledger and in each of your reporting ledgers.

- Use the Journal Import Execution Report to review the status of all import journal entries. The Journal Import Execution Report prints a line for each journal entry source from which you are importing journal entries.

- If you encounter relatively few Journal Import errors, you can correct the data in the GL_INTERFACE table.

- If you encounter several Journal Import errors, you should delete the Journal Import data from the GL_INTERFACE table, and correct the information in your feeder system before rerunning Journal Import.

- Review the journal entries created by Journal Import before you post them.

- Post your Journal Import journal entries.

Importing Journals:
image

(N) Journals > Import > Run

To run journal import from General Ledger, navigate to the Import Journals window. Enter the source. You can choose No Group ID, All Group IDs, or specific Group IDs. You can use the list of values for this field to determine if the Group ID exists in the interface table. Then select the Import button to start the import program.

Note: Oracle subledgers create a report when the transfer is run from the subledger that displays the Journal Import Group ID. You can view the output in the Request window to locate the Group ID.
You can run Journal Import in parallel for several sources as long as each request corresponds to a unique Source/Group ID combination. The maximum number of combinations you can run at one time is 20.
Oracle General Ledger names the created batch with the following naming convention:

<Optional User-Entered Reference><Source><Request ID><Actual Flag><Group ID>

Suspense Posting

• If suspense posting is enabled for your ledger, select the Post Errors to Suspense check box. Journal Import posts entries with invalid account combinations to a predefined suspense account or accounts, if you have defined one for each journal source and category. Typical account errors are:

- Detailed posting not allowed.
- Account disabled for this date.
- Disabled account.
- Account code combination is not valid.
- Account code combination ID does not exist.

• If you choose not to post errors to a suspense account, Journal Import rejects any source/group ID combination with account errors.

Multi-Table Journal Import:
image

Using Multi–Table Journal Import

• General Ledger provides you with the Journal Import Package (GL_JOURNAL_IMPORT_PKG) to create a new interface table and populate the GL_INTERFACE_CONTROL table. In addition, you can create your own procedures to populate your interface table with data and to launch Journal Import. This enables you to automate the entire procedure.

• Below are the steps to follow to use Multi-Table Journal Import:

- Create a new interface table. New interface tables must have the same columns as the GL_INTERFACE table but you can add more if your needs require.

- Populate the new interface table with data.

- Populate the GL_INTERFACE_CONTROL table with one record for each source/group ID combination that was put into the interface table. Specify a table name that the data is to be retrieved from for each combination. Specify what should be done with the data once it has been processed.

- Start Journal Import using the Import Journals window. Specify each of the source/group ID combinations that you want to import. If there are multiple tables, Journal Import will be launched multiple times.

- If Journal Import indicates that the data is erroneous, then correct the data using the Correct Journal Import Data window or delete it using the Delete Journal Import Data window. If you choose to correct it, then start Journal Import again using the Import Journals window.

General Ledger Summary account

Summary Account Examples

Here are some common dimensions and examples of ways you can summarize information within each dimension:

Company:

• A segment that indicates legal entities. You might summarize companies by major industry, such as Electronics Companies; by regions within a country, such as Eastern Companies; or by country group, such as European Companies.

Cost Center:

• A segment that indicates functional areas of your business, such as Accounting, Facilities, Shipping, and so on. You might keep track of functional areas at a detailed level, but produce summary reports that group cost centers such as Accounting, Planning & Analysis and Facilities, into one division called Administration.

Account:

• A segment that indicates your "natural" account, such as Cash, Accounts Payable, or Salary Expense. You will likely summarize your accounts by account type, namely your Assets, Liabilities, Equity, Revenues and Expenses. You might also summarize at a more detailed level, with summary accounts like Current Assets or Long–Term Liabilities.

Product:

• A segment that indicates products. You might want to summarize products into product groups such as personal computer components, storage devices, and so on.

District:

• A segment that indicates geographical locations, such as Northern California, Central Florida or Western New York. If you define segments that record data within smaller geographical areas, such as districts, you can easily summarize districts into states, or even into groups of states you can call regions.

image

Summary Accounts:

• Updated when journals are posted to a corresponding detail account

• Enable online summary inquiries

• Speed concurrent processing

Detail Accounts:

• Allow direct posting of business transactions and journals

Summary Accounts Versus Parent Values:

image

Parent Values and Rollup Groups

clip_image002

Parent Values and Rollup Groups

After determining your needs and organizing your summary account structure, define your parent values and your rollup groups.

Note: You can use the Account Hierarchy Manager or the Account Hierarchy Editor, if Applications Desktop Integrator is installed, to create and edit your account hierarchies graphically. You can use the Account Hierarchy Manager or the Account Hierarchy Editor to define parent and child segment values, as well as rollup groups.

Rollup Groups

About Rollup Groups:

• A rollup group is a collection of parent segment values for a given segment

• A value cannot belong to a rollup group unless it is a parent value that has child values

• Parent values and child values belong to the same value set, which is attached to a key flexfield segment

Summary Account Templates

• Oracle General Ledger uses summary templates to create summary accounts.

• Oracle General Ledger uses the templates in combination with parent segment value definitions to create summary accounts.

• You specify when you want Oracle General Ledger to begin maintaining your summary account balances.

• When you delete a summary template, Oracle General Ledger deletes all summary accounts created from that template and their associated balances.

clip_image002[7]

Defining Summary Accounts

clip_image002[9]

Plan Your Summary Account Templates

• Set up templates to define and maintain summary accounts. You can enhance the speed of your summarizations by controlling the number of summary accounts created by your template. The number of summary accounts your template creates depends on the template segment values.

- Use the formula above to determine the number of summary accounts any given template will create.

image

Summary Account Creation Example

How to Define a New Summary Account Template

1. Open the Summary Accounts window.

2. Enter a Name for the summary account template.

3. Enter the Template.

4. Enter the Earliest Period for which you want General Ledger to maintain your actual, encumbrance and budget summary account balances. General Ledger maintains summary account balances for this accounting period and for subsequent periods.

5. If you are using budgetary control for your ledger, set the budgetary control options for the summary template.

6. Save your work. General Ledger submits a concurrent request to add the summary accounts, and displays the Status of your summary template.

- Current: The summary accounts are active.

- Adding: The concurrent request to create summary accounts is pending or running.

- Deleting: The concurrent request to delete summary accounts is pending or running.

Thursday, January 30, 2014

General ledger Journal Import and Group By Effective Dates Usage

 

image

Journal Import now provides an option to automatically group journal lines into journal entries based on effective dates. This functionality was previously only available to customers using average balance processing.

Through a new profile option called GL Journal Import: Separate Journals by Accounting Date, you can choose to separate journal lines into separate journals by different accounting dates, even if using a standard ledger. This includes journals originating from Oracle subledger applications

image

By grouping and monitoring business transactions by date, rather than by accounting period:

• You have greater flexibility in how you want to account for your business transactions. You can account for them by effective date or by accounting period.

• You facilitate financial auditing and compliance with the new Sarbanes-Oxley Act.

• For DBI customers, you have better information that is updated on a daily basis. This enables you to make better business decisions and react more quickly to opportunities.

image

image

How to Set Up Journal Import Group By Effective Dates

1. Set the profile option called GL Journal Import: Separate Journals by Accounting Date.

- If set to Yes, journals that pass the GL_INTERFACE table via Journal Import will be separated into separate journals by accounting date.

- If set to No, journals will be grouped by accounting period.

2. Run the Journal Import process.

Note: If you want journals that originated from Oracle subledgers to be separated by accounting date, you must set this profile option for each Oracle subledger application.

image

If you have two journal lines in the same accounting period but with different effective dates, by setting the profile option to Yes, you can create two separate journals; one for each line that has a different effective date. Both journals will still be grouped in the same journal batch.

By setting the profile option to No, the lines will be grouped by accounting period and placed in the same journal. This is the current functionality for standard ledgers.

 

image

You can import subledger transactions into General Ledger in detail. The Journal Import process creates one journal entry line for every transaction line in the subledger.

To reduce the size of journal lines and speed the import process, you can choose to summarize journal entry information when you run Journal Import:

• Select Create Summary Journals check box to have Journal Import summarize all transactions for the same account, period, and currency into one debit and credit line.

• When Journal Import creates summary journal lines, all mapping back to the source information is lost. However, you can preserve transaction detail for summary journal lines in the GL_IMPORT_REFERENCES table:

- Select the Import Journal References check box in the Journal Sources window for each journal entry source you wish to preserve. (N) Setup > Journal > Sources.

- Oracle General Ledger populates the GL_IMPORT_REFERENCE table with one record for every transaction in your feeder system.

You cannot import descriptive flexfields if you create summary journals.

Note: If you want to be able to drill down to subledger transaction lines, such as Payables invoices, from General Ledger, select the Import Journal References check box for the subledger source in the Journal Sources window whether you transfer in summary or detail from subledgers.

Reviewing Journal Import Data

Review the status of accounting data imported into Oracle General Ledger using the Journal Import Execution Report. Use the Error Key section on the report to identify the types of errors found.

The journal import program rejects all transactions of a Source and Group ID if any of its journals have errors.

Use the journal import verification process to identify and correct journal import errors.

Journal Import Verification Process

image

Validation When Using Open Interfaces

• Journal Import validates all of your data before it creates journal entries in General Ledger. If you allow suspense posting for your ledger, Journal Import assigns lines with invalid accounts to the suspense account. Journal Import rejects all other invalid lines, and they remain in the GL_INTERFACE table, where you can correct them online in the Correct Journal Import Data window or in your feeder systems. Journal Import also prints your error lines in the Journal Import Execution report.

Journal Level Validation

• Journal Import validates the following attributes to ensure that your journals contain the appropriate accounting data:

- Account combinations

- Unbalanced journal entries

- Periods

- Foreign currency errors

- Budget information

- Encumbrance information

- Other miscellaneous items

Correcting Journal Import Errors Online

• If your Journal Import results in relatively few errors, you can make online corrections to the data that was rejected, then rerun Journal Import to import the corrected data.

(N) Journals > Import > Correct

• The Correct Journals Import window displays each field of the GL_INTERFACE table. From this window you can query import lines that have a status of Error or Corrected.

• Make your corrections.

- If you are correcting Accounts data, you must enter an account segment value or enter a valid Code Combination ID.

- Segment values override Code Combination IDs, so you must first clear all displayed segment values before changing the displayed Code Combination ID.

• The Status changes to Corrected after you save your changes.

• Select the Import Journals button to return to the Import Journals window.

• Deleting Journal Import Data to Correct Errors

• If you encountered a high number of errors from the Journal Import process, you should delete all information from the interface table and rerun Journal Import after correcting the errors.

(N) Journals > Import > Delete

• If you delete import data that originated from an Oracle subledger, you must correct the data in the subledger and reimport it from the original source:

- Delete all the import data for your journal entry source and group ID from the GL_INTERFACE table.

- Correct the errors in feeder system.

- Repopulate the GL_Interface table.

- Rerun Journal Import.

Caution: Do not delete journal import data from Oracle subledgers such as Oracle Payables or Oracle Receivables. The Oracle subledgers set flags to indicate the transactions have been sent to Oracle General Ledger. These flags must be reset before the transactions can be resent.

Oracle error 1403: java.sql.SQLException: ORA-01403: no data found ORA-06512: at line 1 has been detected in FND_SESSION_MANAGEMENT.CHECK_SESSION. Your session is no longer valid.

Issue:

Error after login page while executing operations/welcome

<PRE>Oracle error 1403: java.sql.SQLException: ORA-01403: no data found ORA-06512: at line 1 has been detected in FND_SESSION_MANAGEMENT.CHECK_SESSION. Your session is no longer valid. </PRE>

Servlet error: An exception occurred. The current application deployment descriptors do not allow for including it in this response. Please consult the application log for details.


Solution:

1. Please execute the following scripts from $FND_TOP/patch/115/sql:
SQL> @AFICXSMS.pls
SQL> @AFICXSMB.pls
 

NOTE:

Please ensure that the package FND_SESSION_MANAGEMENT is valid and confirm the version again.

2. Bounce the server.

3. Retest the issue.


4. Migrate the solution to production instance.

Sunday, January 19, 2014

Journal Upload using Desktop Integrator (Web ADI)

 

Overview

Web Applications Desktop Integrator is a tool that leverages the standard desktop applications to perform some of the Oracle E-Business Suite task.

The Web ADI brings Oracle E-Business suite to the desktop where familiar desktop tool like Excel can be used to create spreadsheet, enter and modify data in the spreadsheet and finally upload the data into the Oracle Applications.

Web ADI can be very useful for the users who are experienced with Excel and will like to use Excel to enter the data related to Oracle Applications.

These users can use the special features of excel i.e. copy, paste, drag cells to increment and various arithmetic functions to optimize their business task and increase their productivity.

Data that is entered into the spreadsheet can then be finally be uploaded in to the Oracle Applications with or without validation.

Features:

Works Via Internet

Web ADI uses the Internet Computing Architecture that lowers the total cost of ownership by having the product installed centrally at one place. This feature is very much unlike client ADI where the product needs to be installed at all the client machines. Only Internet browser and Microsoft Excel is required at client machine to access Web ADI.

Validates Data

The data entered in the spreadsheet can be uploaded into the Oracle Applications. The data during upload can be validated against the segment security rules and cross validation rules defined in Oracle Applications. If any errors are found, messages are returned directly to the spreadsheet, enabling you to correct the errors and successfully upload the data.

Enables Customizations

The Layout functionality can be used to modify the user interface of the spreadsheet. The appearance, the position of the fields in the spreadsheet can be changed as per the end users requirement. The field can be positioned in a sequence that is comfortable to the end user and expedites the data entry process. The fields in the spreadsheet can also be specified to contain default values. These definitions can be saved, reused, and modified as needed.

Automatically Imports Data

The data in the spreadsheet can either be manually entered or can be imported automatically from the text file. This feature can be useful when migrating data from a legacy system to the Oracle E-Business Suite. Imported information can be quickly modified in Excel, validated, and uploaded to the Oracle E-Business Suite.

About Web ADI:

Web ADI is an auto configurable product and would require the access to Desktop Integration responsibility for Release 12 and Oracle Web ADI responsibility for Release 11i. The System Administrator can assign this responsibility.

Navigate to Desktop integrator responsibility

Click on ‘create document’

image

Select the General Ledger – Journal and click ‘Next’

clip_image002

Select viewer ‘excel 2007’

clip_image004

Select “functional actual – multiple” from LOV

clip_image006

Click on Next

clip_image008

Click ‘Create document’

clip_image010

After click create document we will get popup screen for opening ‘Wed ADI.xls’ click on OK

clip_image012

After click ok button will get warning message, click ‘Enable’ macro button

clip_image015

Wait till generate the document, see below screen

clip_image017

Now document has been created successfully.

clip_image019

Enter category as “Adjustment” , Sources ‘spreadsheet’, your ledger, currency, code combination, debit and credit amount

clip_image024

Upload excel to Oracle instance

Click ADD-INS in excel sheet and click on ‘upload’ button

clip_image027

Upload parameter:

Enable flogged rows, validate before upload and click on automatically submit journal import then click on ‘Upload’

clip_image029

After upload done successfully we will get the popup message like below with request ID

clip_image031

Navigate to General Ledger click find Journal

clip_image034

Click on review journal, now we can see the line which we uploaded from spreadsheet

clip_image036

Now Journal ready to post, click on POST button

clip_image038

Saturday, January 11, 2014

Error19 - Unopened reporting currency period


Solution:
When posting a Journal in Primary Ledger, the reporting journal is trying to be generated/posted in the Reporting Ledger.
You need to open the period in the Reporting Ledgers