This document provides instructions for creating a basic Web ADI journal entry upload layout in Oracle R12. It describes setting up Excel security settings to allow macros, defining the responsible module, creating a style sheet to control formatting, defining a mapping for text file imports, and defining the layout including required fields, optional fields, and their placement.
This document provides instructions for creating a basic Web ADI journal entry upload layout in Oracle R12. It describes setting up Excel security settings to allow macros, defining the responsible module, creating a style sheet to control formatting, defining a mapping for text file imports, and defining the layout including required fields, optional fields, and their placement.
This document provides instructions for creating a basic Web ADI journal entry upload layout in Oracle R12. It describes setting up Excel security settings to allow macros, defining the responsible module, creating a style sheet to control formatting, defining a mapping for text file imports, and defining the layout including required fields, optional fields, and their placement.
This document provides instructions for creating a basic Web ADI journal entry upload layout in Oracle R12. It describes setting up Excel security settings to allow macros, defining the responsible module, creating a style sheet to control formatting, defining a mapping for text file imports, and defining the layout including required fields, optional fields, and their placement.
Table of Contents INTRODUCTION ........................................................................................................................................ 1 EXCEL SECURITY SETTINGS ..................................................................................................................... 1 RESPONSIBILITY FOR WEB ADI ............................................................................................................... 4 DEFINE STYLE SHEET ............................................................................................................................... 5 DEFINE MAPPING .................................................................................................................................... 7 DEFINE LAYOUT ...................................................................................................................................... 9 WEB ADI JOURNAL CREATION ............................................................................................................ 15 USE SHORTCUT ...................................................................................................................................... 29 USE TEXT FILE TO CREATE JOURNAL DATA ........................................................................................ 32 CREATING A MENU SHORTCUT FOR A FREQUENTLY USED UPLOAD .................................................. 38
RAJU CHINTHAPATLA ORACLE WEB ADI TRAINING GUIDE FOR R12 RAJU CHINTHAPATLA
Introduction Web ADI brings Oracle E-Business Suite functionality to the desktop where the familiar Microsoft Excel, Word, and Project applications can be used to complete your Oracle E-Business Suite tasks. This guide provides instructions on using the Microsoft Excel functionality.
Web ADI (Application Desktop Integrator) is used to upload journal entries from a spreadsheet in to the general ledger. The journal entry can be saved to your local drive or a network directory.
Key Features Works via Internet Presents E-Business Suite Data in a Spreadsheet Interface Validates Data Enables Customizations Automatically Imports Data
Excel Security Settings
Security Settings for Excel 2003 For Web ADI to function properly, the macro settings in Excel must be set to at least the Medium Level. To check this, in Excel 2003, select ToolsMacroSecurity from the Menu. The Security Level should be either Medium or Low (medium is recommended).
The Security Level should be either Medium or Low (medium is recommended).
Also, on the Trusted Sources tab, the values for “Trust all installed add-ins and templates” and “Trust access to Visual Basic Project” should be checked.
RAJU CHINTHAPATLA ORACLE WEB ADI TRAINING GUIDE FOR R12 RAJU CHINTHAPATLA
These Excel settings only have to be verified one time.
Security Settings for Excel 2007
In Excel 2007, select Excel Options from the Menu.
You will see the following window:
RAJU CHINTHAPATLA ORACLE WEB ADI TRAINING GUIDE FOR R12 RAJU CHINTHAPATLA
Click on the Trust Center Settings buttons.
Click on Macro Settings
RAJU CHINTHAPATLA ORACLE WEB ADI TRAINING GUIDE FOR R12 RAJU CHINTHAPATLA
Check the “Trust access to the VBA project object model” checkbox.
Responsibility for Web ADI
Please note that in earlier versions of Web ADI, the responsibility that was used was “Oracle Web ADI”. This has been made obsolete in R12.
It is possible to create custom menus for Web ADI based on:
RAJU CHINTHAPATLA ORACLE WEB ADI TRAINING GUIDE FOR R12 RAJU CHINTHAPATLA
Application = Web Applications Desktop Integrator
Menu = Desktop Integration Menu
If creating custom menus for use with Web ADI, you must assign appropriate Profile Options for Ledger Name, MO Operating Unit, HR User Type, HR Business Group, Data Access.
Define Style Sheet
The purpose of a Style Sheet is to control the Background Color, Font Color, Font and Font Size of the Upload spreadsheet.
Note: The Default Style Sheet can be copied to create a new Style Sheet. The Default Style Sheet is read-only and cannot be modified.
Log in and select Desktop Integration responsibility.
Click on the link “Define Style Sheet”.
You should see a Style Sheet called Default, and other Style Sheets that users may have set up.
The Default Style Sheet can be copied to create a new Style Sheet. The Default Style Sheet is read-only and cannot be modified.
The Default Style Sheet can be used with any layout.
On this window you can Create a new Style Sheet or Update, Delete or Duplicate an existing Style Sheet.
RAJU CHINTHAPATLA ORACLE WEB ADI TRAINING GUIDE FOR R12 RAJU CHINTHAPATLA
Create a new Style Sheet.
Click on the Create button.
Enter a Style Sheet Name
Enter a brief Description
Pick Colors and Fonts as desired for each element.
Click on the Apply button.
You will see a message “Style Sheet saved successfully”.
Use the following graphic to determine the colors and fonts:
RAJU CHINTHAPATLA ORACLE WEB ADI TRAINING GUIDE FOR R12 RAJU CHINTHAPATLA
You can Update or Delete existing Style Sheets using the appropriate buttons.
You can also Duplicate an existing Style Sheet, save it with a different name, and Update it to meet your requirements.
The Oracle supplied Default Style Sheet is commonly used, as it has been optimized for general use.
Define Mapping A Mapping is required when you wish to use a Text file to load data from an external source into your ADI Spreadsheet.
Log in and select Desktop Integration responsibility.
RAJU CHINTHAPATLA ORACLE WEB ADI TRAINING GUIDE FOR R12 RAJU CHINTHAPATLA
Click on the link “Define Mapping”.
Select the Integrator “General Ledger – Journals”.
Click on the Go button.
A Default Text Import Mapping has been supplied by Oracle, and this may be Copied and Updated if required.
To view the Default Text Import Mapping, click on the Update button.
RAJU CHINTHAPATLA ORACLE WEB ADI TRAINING GUIDE FOR R12 RAJU CHINTHAPATLA
The Mapping Name and Number of Columns is defined in this window.
Click on the Next button.
The Mapping columns between the text file and the Spreadsheet are defined here.
Define Layout Log in and select Desktop Integration responsibility.
RAJU CHINTHAPATLA ORACLE WEB ADI TRAINING GUIDE FOR R12 RAJU CHINTHAPATLA
Click on the “Define Layout” link.
Select the Integrator “General Ledger – Journals” from the drop-down list.
Click on the Go button.
RAJU CHINTHAPATLA ORACLE WEB ADI TRAINING GUIDE FOR R12 RAJU CHINTHAPATLA
On this window, you can either Create a brand new layout, or Update, Delete or Duplicate an existing Layout.
It is easier to Duplicate one of the Oracle supplied Layouts and modify it than to create a new one from scratch.
The Default Functional Actuals – Single Layout is usually Duplicated and modified as per requirements, as this is the Layout that has been optimized by Oracle.
Create Layout Click on the Create Button.
Enter a Layout Name
Click on the Next Button.
RAJU CHINTHAPATLA ORACLE WEB ADI TRAINING GUIDE FOR R12 RAJU CHINTHAPATLA
Use the following screen to determine the placement of fields, add optional fields, and set default values for fields.
Required Fields These fields appear at the top of the screen. All of them must be included in your Layout.
Enter the Required Fields. Change Balance Type Placement to Header and enter Default Value as “Actual”. Add Default Value “REGENERON PHARMACEUTICALS, INC”, Category “Adjustment” and Source “Spreadsheet”. Change the Default Type as “Constant” for these fields.
Optional Fields Check the optional fields you want to include in your Layout. Any number of optional fields may be selected.
In the Optional Fields check Batch Name, Batch Description, Journal Name and Journal Description and select Placement as Header 1 for each. Select Line Description and select Placement as Line. Optionally, check Reverse Journal and Reversal Period/Date.
Placement The placement value you choose for each field in the Layout will determine where that field appears in the document.
Context: Appears at the top of the document as read only, contextual information Header: When uploaded, data in these fields is repeated for every record in the line region. Place fields whose values do not change amongst the records being entered in the document in the header region; this will save you from re-entering this information for every record in the line region.
RAJU CHINTHAPATLA ORACLE WEB ADI TRAINING GUIDE FOR R12 RAJU CHINTHAPATLA
Line: Place fields in the line region whose values change within the group of records you are creating in the document.
Scroll to the bottom of the form. Click on the Next button.
RAJU CHINTHAPATLA ORACLE WEB ADI TRAINING GUIDE FOR R12 RAJU CHINTHAPATLA
Scroll down till you see the following fields:
RAJU CHINTHAPATLA ORACLE WEB ADI TRAINING GUIDE FOR R12 RAJU CHINTHAPATLA
Make the Ledger, Category and Source “Read only”.
Update the Data Entry Rows to 500.
Click on the Apply button.
Use the following graphic to determine the placement of fields:
Context
Header
Lines
Web ADI Journal Creation
Once the Excel settings are verified, you open a Web ADI template by executing the following steps.
Login and select the Desktop Integration Responsibility.
RAJU CHINTHAPATLA ORACLE WEB ADI TRAINING GUIDE FOR R12 RAJU CHINTHAPATLA
Click on Create Document
Leave the Shortcut as None for now. We will learn how to create a Shortcut in a later section.
Click on Continue. Select the Integrator “General Ledger – Journals” from the drop-down list. Click the Next button.
Select the Viewer “Excel 2003” or “Excel 2007”. This will depend on the version of Excel you have on your PC.
RAJU CHINTHAPATLA ORACLE WEB ADI TRAINING GUIDE FOR R12 RAJU CHINTHAPATLA
Click on the Next button.
Select the Layout you had created earlier.
If you did not create any Layout, then select “Functional Actuals – Single”.
Click on the Next button.
In the Content field, select “None.
Click on the Next button.
Review the information. Click on Create Document.
A window will open up notifying you that the ADI document is being downloaded to your computer.
The application will download a file to your computer and open the file in Excel. Based on your computer settings, you may receive some warning or confirmation message. For example, in the screen shot above, you must click on the message and confirm that the download is allowed.
RAJU CHINTHAPATLA ORACLE WEB ADI TRAINING GUIDE FOR R12 RAJU CHINTHAPATLA
Click on the Open button.
Based on your macro security level, you will be asked if you want the macros to run.
RAJU CHINTHAPATLA ORACLE WEB ADI TRAINING GUIDE FOR R12 RAJU CHINTHAPATLA
Click on the Close button.
RAJU CHINTHAPATLA ORACLE WEB ADI TRAINING GUIDE FOR R12 RAJU CHINTHAPATLA
An Excel workbook like the above will be opened. The workbook by default is protected. The worksheet has room for 500 rows to be uploaded. If you’re entry requires more rows, unprotect the worksheet and add the additional rows needed.
Now the Journal Header fields need to be populated. Provide values for: Currency – USD. You can select the currencies by double-clicking in the field. Accounting Date – Enter a date in the period for which you are creating the entry. You can also select the date from a calendar by selecting Oracle List of Values from the menu when you are in the Accounting Date field. Batch Name – Enter a batch name in accordance with your company’s policy Batch Description – Enter a batch name in accordance with your company’s policy Journal Name – Enter a journal name in accordance with the policy listed at the beginning of this section. Journal Description – LKQ policy is to enter a description for all journal entries. The description entered on the journal header will default to all journal lines. Reverse Journal – Select Yes to have the journal entry automatically reversed. If the entry is not to be reversed, leave the field blank or select No. Reversal Period/Date – If you selected to have the entry reversed, enter the date that it should be reversed on. You can select a Period and Date from the List by double-clicking on the field. At this point, the journal lines can be entered. Alternatively, the line information can be copied from another Excel worksheet.
RAJU CHINTHAPATLA ORACLE WEB ADI TRAINING GUIDE FOR R12 RAJU CHINTHAPATLA
You will notice that any line that has a value added in the account or amount fields, will have a flag ( ) entered in Upload column.
RAJU CHINTHAPATLA ORACLE WEB ADI TRAINING GUIDE FOR R12 RAJU CHINTHAPATLA
After all the lines are entered, the data is now ready to be uploaded. To execute the upload, from the menu, select Oracle Upload from the Excel menu.
In Excel 2003 this should be in the Excel Menu Oracle > Upload.
In Excel 2007 this should be in Add-Ins > Oracle > Upload.
RAJU CHINTHAPATLA ORACLE WEB ADI TRAINING GUIDE FOR R12 RAJU CHINTHAPATLA
Click on the Upload menu.
Set the Upload Parameters as shown above, the press the Upload button.
RAJU CHINTHAPATLA ORACLE WEB ADI TRAINING GUIDE FOR R12 RAJU CHINTHAPATLA
A window will show you the progress of the upload. After the upload completes, the window should look like:
The Group ID assigned will be different for each user. It is a combination of the internal user id and the date.
RAJU CHINTHAPATLA ORACLE WEB ADI TRAINING GUIDE FOR R12 RAJU CHINTHAPATLA
The number of rows successfully uploaded should match the number of rows in the journal entry.
Save the worksheet to your local machine or network. Then close the workbook.
Review Journal Import Process
The Journal Import Process needs to be reviewed to make sure the journal imported correctly and there were no mapping issues.
The Find Requests window will be open.
RAJU CHINTHAPATLA ORACLE WEB ADI TRAINING GUIDE FOR R12 RAJU CHINTHAPATLA
Press the Find button, to review your requests.
Find the Request ID from the Journal Upload confirmation. The request should have a Phase code of “Completed” and a Status code of “Normal”.
Press the View Output button.
A successful import will look like the following:
RAJU CHINTHAPATLA ORACLE WEB ADI TRAINING GUIDE FOR R12 RAJU CHINTHAPATLA
If the import program finishes with any status code other than Normal, contact the support team.
If the Status of the Journal Import Execution Report is anything other than Success, contact the support team.
Entries that were uploaded successfully can be posted using the standard posting process.
Upload Errors We will try to upload a file with invalid combinations.
RAJU CHINTHAPATLA ORACLE WEB ADI TRAINING GUIDE FOR R12 RAJU CHINTHAPATLA
Click on the Upload button.
RAJU CHINTHAPATLA ORACLE WEB ADI TRAINING GUIDE FOR R12 RAJU CHINTHAPATLA
We see a message “No rows uploaded, 2 rows were invalid”.
Click on the Close button.
You will see the error message “This combination is disabled. Please choose another” in each line which has an error.
You must fix these lines before retrying the upload.
Use Shortcut Log in with the Desktop Integration Responsibility.
RAJU CHINTHAPATLA ORACLE WEB ADI TRAINING GUIDE FOR R12 RAJU CHINTHAPATLA
Select Create Document.
Now you can click on the drop-down button to select “REGN Functional Actuals – Single”.
Click on the Continue button.
The System will prompt you to open a WebADI.xls file.
RAJU CHINTHAPATLA ORACLE WEB ADI TRAINING GUIDE FOR R12 RAJU CHINTHAPATLA
On clicking Open, the Web ADI file will be created and can be processed as before.
RAJU CHINTHAPATLA ORACLE WEB ADI TRAINING GUIDE FOR R12 RAJU CHINTHAPATLA
Use Text File to Create Journal Data
Define Mapping Mappings must be defined to facilitate importing a text file, or Oracle Applications data into a document created by Web Applications Desktop Integrator (Web ADI). A Mapping associates columns being downloaded with fields in the document.
Log in with the Desktop Integration Responsibility.
Click on Define Mapping.
Select Integrator “General Ledger – Journals”.
Click on the Go button.
You will see a Mapping titled “Default Text Import Mapping”.
To View the Default mapping click on the Update button.
Click on the Next button.
RAJU CHINTHAPATLA ORACLE WEB ADI TRAINING GUIDE FOR R12 RAJU CHINTHAPATLA
This window will display the layout of the Text file with data that you wish to load.
Click on the Cancel button to return to the Define Mapping window.
Create a new Mapping
Click on the Duplicate button.
Change the Mapping Details name to “REGN Mapping”. Set the Key to Mapping Name_Your Initials. This serves as a standard for Mapping Keys, and also assures what you enter is unique.
Click on the Apply button.
Now you will see your mapping in the Define Mapping window.
Click on the Update button.
RAJU CHINTHAPATLA ORACLE WEB ADI TRAINING GUIDE FOR R12 RAJU CHINTHAPATLA
Click on the Next button.
Use the above screen to associate columns being imported from the Content to fields in the document. Click the LOV button for the Source Column to view a list of available columns to import; if you know the Source Column, you can type it directly into the text field. Use the Target Column field directly to the right of a Source Column to create a mapping between the two fields..
You can add Rows to this mapping by clicking on the Add Row button.
If you click on Add Rows you will be able to select additional columns from a LOV.
RAJU CHINTHAPATLA ORACLE WEB ADI TRAINING GUIDE FOR R12 RAJU CHINTHAPATLA
The Target Column LOV contains a list of columns that can exist in a document.
When you are finished, press the Apply button to save the Mapping.
Text File for Upload
The Text file for upload must have data corresponding to the Mapping that you created.
Running an Import using a Mapping
Click on the Create Documents link.
RAJU CHINTHAPATLA ORACLE WEB ADI TRAINING GUIDE FOR R12 RAJU CHINTHAPATLA
Click on the Continue Button.
Make sure you have the General Ledger – Journals Integrator selected. Click on the Next button.
Make sure you have Excel 2003 or Excel 2007 as the Viewer. Click on the Next button.
Select the Layout you created earlier. Click on the Next button. In the Content Field, select “Text File” from the drop down menu.
Click on the Next button.
Select the Text file that you had created earlier. Change the Text File Delimiter to Semicolon.
RAJU CHINTHAPATLA ORACLE WEB ADI TRAINING GUIDE FOR R12 RAJU CHINTHAPATLA
Click on the Continue button.
Click on the Create Document button.
Click on the Open button.
RAJU CHINTHAPATLA ORACLE WEB ADI TRAINING GUIDE FOR R12 RAJU CHINTHAPATLA
The spreadsheet will be created and the data from the Text file will be imported.
This file can be uploaded to Oracle through the Oracle > Upload menu.
Creating a Menu shortcut for a frequently used Upload
Creating a Shortcut Click on the Create Document menu item.
Do not select a Shortcut. Click on the Continue button.
Select the Integrator General Ledger – Journals. Click on the Next button.
RAJU CHINTHAPATLA ORACLE WEB ADI TRAINING GUIDE FOR R12 RAJU CHINTHAPATLA
Select the Viewer Excel 2003 or Excel 2007 as appropriate. Click on the Next button.
Select the Layout you created earlier. Click on the Next button.
Click on the Next button.
Click on the Save button.
Type in a name for your Shortcut. Click on the “Save to Form Function” checkbox.
Make a note of the Shortcut Name, as the System Administrator will need this name to create a Menu item.
RAJU CHINTHAPATLA ORACLE WEB ADI TRAINING GUIDE FOR R12 RAJU CHINTHAPATLA
Click on the Apply button.
You will get a confirmation that your shortcut has been saved.
Click on the Cancel button to return to the main menu.
Attaching the Shortcut to the Menu
Log in as System Administrator. Navigate to Application > Menu.
RAJU CHINTHAPATLA ORACLE WEB ADI TRAINING GUIDE FOR R12 RAJU CHINTHAPATLA
Query the DESKTOP INTEGRATION MENU.
Insert Line 102 (or any other number not in use).
Add a Prompt and the Function which you created earlier as a Form Function “REGN USD Actuals - Test 1”
On saving you will get a message about submission of your Request.
RAJU CHINTHAPATLA ORACLE WEB ADI TRAINING GUIDE FOR R12 RAJU CHINTHAPATLA
After your Request completes normally, log out and log back in. You will now see the menu item “REGN USD Actuals - Test 1” on your Navigation Window.
Creating a Web ADI file from the Menu Item
Click on the Menu item “REGN USD Actuals - Test 1”
Click on the Open button.
Your document will be created immediately.
RAJU CHINTHAPATLA ORACLE WEB ADI TRAINING GUIDE FOR R12 RAJU CHINTHAPATLA
