Creating a Document

This chapter covers the following topics:

• Overview
• Creating a Spreadsheet
• Working with Spreadsheets
• Uploading and Downloading Data from Spreadsheets
• Monitoring Concurrent Requests

Overview

The Create Document interface guides you through steps that allow you to create your spreadsheet. This section only applies to those products that instruct users to create documents through a function that calls the Oracle Web Applications Desktop Integrator Create Document interface. See your product documentation for information specific to accessing its spreadsheet functionality.

Note: Before you create your spreadsheet, make sure at least one layout has been defined. Layouts determine the fields that appear in the spreadsheet you will create. If you intend to automatically import information into the spreadsheet, make sure that a mapping has been defined. For details on defining layouts and mapping, see Administering Oracle Web Applications Desktop Integrator.

After you have defined the parameters for your spreadsheet, you can review them before creating the spreadsheet.

Note that the Create Document process varies depending on how it is implemented. Also, some of the parameters in the Create Document interface might be pre-configured by your system administrator.

Creating a Spreadsheet

Use this procedure to create a spreadsheet on your desktop to which you can import data from Oracle E-Business Suite.

There are three possible procedure flows for creating a spreadsheet:

• The product uses the Create Document interface, described in the following procedure.

• The product calls the Create Document interface from within its application. In this case, you will follow one or more of the following steps depending on the product implementation. See your product documentation for details.

• The product calls the Create Document interface, but does not require user input for these steps. The spreadsheet will be automatically created as described in Step 8.

Oracle Web Applications Desktop Integrator provides two versions of the Create Document interface. The default is the Create Document page, which lets you enter all document parameters in a single page. After you enter the initial parameters, the page displays additional parameter fields based on the values you specified. Alternatively, your administrator can enable the Create Document page flow, which guides you through multiple pages to enter the document parameters. After you enter the parameters in each page, choose Next to display the next page based on the values you specified.

To create a spreadsheet

1. From the Oracle E-Business Suite navigator, select the link appropriate for your product to create a document. For example, you might select the Create Document link under the Desktop Integration responsibility.

2. In the Primary Parameters region of the simplified Create Document page, or in the Integrator page of the page flow, select an integrator. The integrator defines the task you will perform. For example, to use spreadsheets to upload data to General Ledger, select the General Ledger - Journals integrator.

   Note: You must be granted access to an integrator by your system administrator. Specific security functions grant access to specific integrators. See the product documentation for information on your product's functions.

3. Select the viewer that you want to use to open your spreadsheet. For example, Excel 2010.

   Select the Reporting Flag option if you want to create a spreadsheet for reporting purposes only, that does not allow you to upload data to Oracle E-Business Suite.

   Note: In the Create Document page flow, select the Reporting option.

4. Select a layout. Layouts determine which fields are included in your spreadsheet, their placement, and any default values. See Defining Layouts for details on defining custom layouts.

5. Select the content to import. You can choose None to define a blank spreadsheet, or, depending on the integrator, choose a content that will automatically populate the spreadsheet with data from a text file or with data downloaded from Oracle E-Business Suite.

   • If you select None, skip to Step 7.

   • If you select Text File, continue to Step 6.

   • If you select a product-specific option, depending on the integrator you are using, Oracle Web Applications Desktop Integrator prompts you for mapping information and parameters required by the content. See your product documentation.

6. If you selected the Text File option, specify how to handle the text file data.

   • Select the mapping to use in order to map the text file data to the spreadsheet columns. For details on defining custom mappings, see: Defining Mappings.

   • Select Browse to locate the text file in your local system.

   • Indicate the delimiter used in the text file: Tab, Semicolon, Comma, or Other. If you select Other, enter the delimiter into the Delimiter Character field.

   • Select Ignore Consecutive Delimiters if you do not want to insert a null value into the spreadsheet when two delimiters exist side by side in the text file.

   • Enter the line in the text file where you want to start importing. The first few lines in a text file may be header information that you do not want to import into the spreadsheet.

   Note: If you work in a multi-byte environment, save the text file with UTF-8 encoding before importing the text file.

   Note: To ensure that the data from the text file can be imported correctly, use the following canonical formatting for numbers and dates:

   • Specify numbers using a period (.) as the decimal separator when needed, and do not use any group separator.

   • Specify dates that do not include a time component according to the server time zone using the following format: yyyy-MM-dd

   • Specify dates that include a time component according to the server time zone using the following format: yyyy-MM-dd HH:mm:ss

7. Review the parameters you specified for the document in the previously completed regions of the Create Document page or in the Review page of the page flow.

   Optionally choose Save. The Shortcut popup window or Select Shortcut page allows you to save a shortcut that will appear in the shortcut list at the beginning of the Create Document UI. Specify the shortcut name and choose the settings you want to save. Any settings that you save will be pre-specified whenever you use this shortcut. In the Create Document page, those settings will appear as read-only fields. In the page flow, the pages displaying those settings will be skipped.

   Note: System administrators can also choose to save the shortcut to a function, and attach the function to a user's menu.

   In the Shortcut popup window, choose Save to save the shortcut and return to the Create Document page, or choose Save & Create to save the shortcut and immediately begin creating your spreadsheet with the specified parameters.

8. Create your spreadsheet by selecting Create in the Create Document page, Save & Create in the Shortcut popup window, Create Document in the page flow, or a link specified by your product documentation.

   • If your site is using the Office Open XML (OOXML) format for Oracle Web Applications Desktop Integrator spreadsheets, then the spreadsheet is created on the server and your browser prompts you to download the spreadsheet as a file with the .xlsm extension. Select Open, and if necessary, enable the Oracle Web Applications Desktop Integrator macros when prompted by Microsoft Excel.

     If you selected a content in Step 5, the data will be downloaded to populate the spreadsheet during the spreadsheet creation.

   • If your site is not using OOXML, then your browser prompts you to download the file "WebADI.xls". Select Open, and if necessary, enable the Oracle Web Applications Desktop Integrator macros when prompted by Microsoft Excel. The Excel file for the integrator spreadsheet will open and a small window will open that will format the Excel file.

     If you selected a content in Step 5, the data will be downloaded to populate the spreadsheet.

     Do not close the window or use Excel until this window indicates that formatting is complete.

   If exporting is enabled for tables in Oracle Application Framework pages, then you can select the Export Table Data to Excel icon for a table to create the corresponding spreadsheet. Spreadsheets exported from Oracle Application Framework tables are always created as OOXML documents, regardless of the format your site uses for other spreadsheets.

   Note: If the table contains unsaved data, then you must save the pending changes before you can export the table.

   Some types of tables do not support exporting. In this case, the export icon does not appear.

9. You can now work in the spreadsheet. If your integrator allows upload, you can also upload the data to Oracle E-Business Suite after you finishing modifying the data.

Working with Spreadsheets

Once you have created and downloaded your document, you can begin working with your data. Use the Oracle tab on the Microsoft Excel ribbon to access additional Oracle Web Applications Desktop Integrator features that extend the functionality of the spreadsheet.

Note: Except where noted, the same steps apply for working with spreadsheets created in the OOXML format as for those not created with OOXML.

Oracle Ribbon Tab

Example Spreadsheet

Working with Unsigned Spreadsheets

If you do not use digital signatures in Oracle Web Applications Desktop Integrator, then you must enable the Oracle Web Applications Desktop Integrator macros on a case by case basis within Microsoft Excel. In this case Microsoft Excel prompts you to enable the macros whenever you create or open an Oracle Web Applications Desktop Integrator spreadsheet. See: Selecting Macro Security Settings in Microsoft Excel.

Working with Digitally Signed Spreadsheets

If you set the BNE Enable Digital Signature profile option to have Oracle Web Applications Desktop Integrator affix a digital signature to the spreadsheets you create, then you can select a high macro security level in Microsoft Excel. The first time you create a signed Oracle Web Applications Desktop Integrator spreadsheet, you must identify the Oracle Web Applications Desktop Integrator macros as coming from a trusted source. Subsequently, whenever you create a signed spreadsheet, Microsoft Excel automatically allows the macros to run. See: Selecting Macro Security Settings in Microsoft Excel.

Note: If you create a digitally signed spreadsheet, it is recommended to work without saving in order to preserve the signature. If you save an Oracle Web Applications Desktop Integrator spreadsheet, then the Oracle signature is discarded, because the content of the spreadsheet is no longer controlled by Oracle. To continue working on the spreadsheet, you can use one of these options:

• Change your macro security settings to a medium security level temporarily while performing tasks that use Oracle Web Applications Desktop Integrator macros, such as using lists of values or performing an upload to Oracle E-Business Suite.

• Create a new digitally signed Oracle Web Applications Desktop Integrator spreadsheet, and copy and paste the modified data from the saved, unsigned spreadsheet to the new signed spreadsheet. You can then continue working in the new spreadsheet at the same security level.

• Attach your own digital signature from a trusted source to the spreadsheet and save the spreadsheet again.

Working with Office Open XML Spreadsheets

If your site is using the Office Open XML (OOXML) format for Oracle Web Applications Desktop Integrator documents, then when you choose to create a spreadsheet, it is created on the server and your browser prompts you to download the spreadsheet as a file with the .xlsm extension. Select Open, and if necessary, enable the Oracle Web Applications Desktop Integrator macros when prompted by Microsoft Excel.

If you want to share the spreadsheet with other users, you must open it, enable macros, and then save the spreadsheet locally. When a user opens the saved file, that user must enter their Oracle E-Business Suite user name and password in order to access the file.

Additionally, if you close the saved file and then reopen it, you must also enter your Oracle E-Business Suite user name and password in order to access the file. This requirement applies even if your original Oracle E-Business Suite session in the browser is still valid.

Viewing Data

Use the expand and collapse buttons at the left of the spreadsheet to show or hide each of the sections: Context, Header, and Lines.

Note: When you initially create your spreadsheet, the sheet is protected by default. You must unprotect the sheet before you can use the expand and collapse buttons.

You can optionally turn on Microsoft Excel filtering on the line items. This is useful when your spreadsheet contains many rows of data. To enable filters, from the Oracle ribbon tab, select the Filters toggle button. To turn filters off, select the Filters toggle button again. To return to the default filter settings, from the Oracle ribbon tab, select Reset Filters.

The hint text for a field displays an asterisk (*) if the field is required and indicates the data type of the field, such as number or text. If the field shows the List hint, then you can double-click the field to access the list of values.

Fields that contain dates with a time component may be displayed either according to the server time zone or according to your client time zone, depending on the integrator definition. Fields that display a date and time in the server time zone show the hint DateTime. Fields that display a date and time in your client time zone show the hint Local DateTime.

Additional Information: See: User-Preferred Time Zones, Oracle E-Business Suite Setup Guide.

Date values are formatted according to your Date Format setting in the Preferences page in Oracle E-Business Suite.

Multiple Worksheets

Your spreadsheet may contain multiple worksheets if your product integrator is defined to generate multiple worksheets. Also, if your site does not use the OOXML format, then multiple worksheets will be automatically created when your downloaded data exceeds 100,000 rows.

If your spreadsheet contains multiple worksheets, then when you are uploading data, you can choose either the Upload button or the Upload All button in the Oracle ribbon tab. Upload will upload only the current worksheet. Upload All will upload all worksheets.

Viewing Graphs

If your integrator supports graphs, your spreadsheet will include additional graphing options.

If the layout for your integrator was designed with the Include Graph of Lines Data option or the Automatically Generate Graph option selected, then a graph of the data will be generated automatically when you download the spreadsheet. Otherwise, view the graph of your line data by selecting one of the following buttons from the Oracle ribbon tab.

• Graph All - Creates a graph of the data on the active worksheet.

• Graph Selection - Creates a graph of the selected data on the spreadsheet.

• Graph Variation - This option is available for multi-sheet downloads, where each sheet contains data from a different source. The data in each sheet shares the same Category and Series. The graph will display the same series column from each worksheet.

Note: Excel limits the number of data points that can be used in a graph.

Note: Oracle Web Applications Desktop Integrator graphs the data according to the graph definition specified in the layout. For more information, see Defining Layouts. The graph can be changed on the graph worksheet by selecting Modify Graph Type or Modify Graph Options from the Oracle ribbon tab.

Oracle Ribbon Tab with Modify Graph Type and Modify Graph Options Buttons

Adding Rows to a Spreadsheet

To add rows to an open spreadsheet:

Steps

1. Unprotect the spreadsheet: In the Review tab, select Unprotect Sheet. Note that some spreadsheets are defined with protection turned off.

   Note: Certain regions of the spreadsheet will remain uneditable even when protection is turned off.

2. Select a row in the Lines section of the spreadsheet.

3. In the Home tab, select the Insert button, then Insert Sheet Rows. Repeat to add as many rows as you need. Oracle Web Applications Desktop Integrator displays a flag in the Upl column beside every row you add.

   Note: To preserve the structure of your Oracle Web Applications Desktop Integrator spreadsheet, do not insert individual cells in the Lines section. Inserting an individual cell causes the spreadsheet's structure to become inconsistent as the remaining cells shift in position.

   Additionally, do not insert any cells or rows into the Context and Header sections.

4. Make changes in the sheet to add the data you want to upload.

5. If desired, protect the spreadsheet by selecting Protect Sheet in the Review tab.

6. If you need to remove a row of data from the Lines section of the spreadsheet, select the entire row and delete it.

   Note: To preserve the structure of your Oracle Web Applications Desktop Integrator spreadsheet, do not delete or cut individual cells from the Lines section. Deleting or cutting an individual cell causes the spreadsheet's structure to become inconsistent as the remaining cells shift in position.

   Additionally, do not delete or cut any cells or rows from the Context and Header sections.

Entering Data

To enter data

1. To enter data, either type the data directly in the spreadsheet field, or if the field shows the List hint, then you can double-click the field to access the list of values. Alternatively, you can select the field and then choose List of Values from the Oracle ribbon tab. The hint text for the field displays an asterisk (*) if the field is required, and indicates the data type of the field, such as number or text.

   If the list of values depends on another field, then you must a enter a value for the referenced field before you can select a value for the dependent field. If you change the value in the referenced field, then any value previously set in the dependent field is cleared, and the dependent field's list of values is adjusted according to the new value in the referenced field. You can then select a new value for the dependent field from the adjusted list.

   You can also use the auto fill options in Microsoft Excel to populate a range of cells automatically with a sequence of values. To do so, select the cells that contain the starting values for the sequence, then drag the fill handle across the cells that you want to fill.

   Note: Do not drag and drop cells within the spreadsheet. Moving cells to a different position in the spreadsheet causes the spreadsheet's structure to become inconsistent. Use dragging only as part of an auto fill action.

   Fields that contain dates with a time component may be displayed either according to the server time zone or according to your client time zone, depending on the integrator definition. Fields that display a date and time in the server time zone show the hint DateTime. Fields that display a date and time in your client time zone show the hint Local DateTime. If a field is defined to use your client time zone in the spreadsheet, then the value you enter is converted to the server time zone when you upload the data to Oracle E-Business Suite.

   Note: For a field that contains a date value, the date format should match the date format defined for the integrator in the integrator LDT file.

2. If desired, protect the spreadsheet by selecting Protect Sheet in the Review tab.

To upload a password-protected spreadsheet without entering the password during the upload process, you must protect the spreadsheet according to the following steps.

To prepare a password-protected spreadsheet for uploading

1. In the Review ribbon tab, choose the Unprotect Sheet button.

2. Select the Messages column and the column prior to it.

3. Right-click in the spreadsheet, and from the resulting menu, select Format Cells.

4. Navigate to the Protection tab.

5. Deselect the Locked checkbox.

6. Make changes in the sheet to add the data you want to upload.

7. Navigate to the Review ribbon tab and choose the Protect Sheet button.

8. Enter the password you want to use.

9. Select the following checkboxes:

   • Select locked cells

   • Select unlocked cells

   • Format cells

   • Format columns

   • Edit objects

   Then choose the OK button.

10. In the confirmation dialog box, reenter the password and choose the OK button.

    You can then upload the data as usual.

Copying Data

You cannot copy an entire worksheet to a different worksheet. However, you can copy row and column values from one worksheet to another Oracle Web Applications Desktop Integrator worksheet, and then upload the data. Copying the entire worksheet is not supported because, while rows and columns are copied, the macro code behind the Oracle Web Applications Desktop Integrator worksheet is not copied over to the new sheet.

Working with Spreadsheets Exported from Oracle Application Framework Tables

If exporting is enabled for tables in Oracle Application Framework pages, then you can download the information from these tables as Oracle Web Applications Desktop Integrator spreadsheets for offline review and analysis. The exported spreadsheet includes all the records in the table, not only those displayed in the page.

Spreadsheets exported from Oracle Application Framework tables are read-only. You cannot upload data from this type of spreadsheet to Oracle E-Business Suite. You also cannot re-download to refresh the data within this type of spreadsheet. However, you can perform a new export from the original table to download a new spreadsheet with the latest data.

Note: Because the relevant functions are not available for spreadsheets exported from Oracle Application Framework tables, the Oracle ribbon menu does not appear in these spreadsheets. These spreadsheets also do not display data entry hints.

Spreadsheets exported from Oracle Application Framework tables are always created as OOXML documents, regardless of the format your site uses for other spreadsheets. Number values in these spreadsheets are formatted according to your Microsoft Excel settings, while date values in these spreadsheets are formatted according to your Date Format setting in the Preferences page in Oracle E-Business Suite. Additionally, date values that include a time component are displayed according to your client time zone.

Uploading and Downloading Data from Spreadsheets

Uploading Data

After creating and modifying your spreadsheet, you can upload the data to Oracle E-Business Suite.

Note: Not all spreadsheets support upload.

Note: Oracle Web Applications Desktop Integrator is designed for transactional worksheet uploads and is not meant to be used for mass data uploads. If you need to perform a mass batch upload, you should review the documentation for the relevant product to evaluate the open interface tables exposed by that product for such uploads. The Oracle Web Applications Desktop Integrator framework has been tested with up to 100,000 rows, though results may vary depending on the number of columns in the spreadsheet, which determines the total number of data cells to upload, as well as the processor speed and amount of memory (RAM) on the desktop. If your site uses OOXML, then the document is automatically uploaded as a ZIP compressed OOXML file. Otherwise, compressing the data using WinZip or 7Zip can help increase the number of records you can upload; see: Compressing Data for Upload. For data sets larger than approximately 100,000 rows, you should evaluate other upload and download alternatives.

To upload data:

1. If your site uses OOXML, you must have access to a temp directory on your client PC where Oracle Web Applications Desktop Integrator can create the ZIP file used to perform the upload.

   • If you have access to the %temp% directory on your PC, then Oracle Web Applications Desktop Integrator can use that directory to create the ZIP file for the upload.

   • If you do not have access to the %temp% directory, then before performing an upload, you must create a %bnetemp% environment variable on your PC and set it to a valid folder with permissions set for the current user.

2. To ensure that you perform the upload with the correct privileges for your Oracle E-Business Suite application, you can optionally specify the responsibility to use for the upload. For instance, if you perform multiple uploads during the same Microsoft Excel session, you can use this option to switch responsibilities as needed between uploads. While viewing your spreadsheet, choose Switch Responsibility from the Oracle ribbon tab. Then select the responsibility you want, and choose the Select button.

3. To initiate the upload, choose Upload from the Oracle ribbon tab. If you are working in a spreadsheet with multiple worksheets, the Upload option uploads only the current worksheet; in this case you can also choose Upload All to upload all the worksheets at once. The Upload Parameters window appears.

4. Select the desired parameters.

   Upload parameters depend on the integrator you select. Moreover, your system administrator might disallow you from changing upload parameters using the BNE Allow Set Parameters profile option. See your product documentation for information on the available parameters.

   The following table shows some common parameters that you may see:

   Upload Parameters

   Parameter	Description
   Rows to Upload	All Rows Select this option to upload all rows in your spreadsheet, regardless of whether changes have been made. Flagged Rows Select this option to upload only those rows that are marked with a flag character in the Upl column of your spreadsheet. Oracle Web Applications Desktop Integrator displays a flag character against a row that has changed.
   Validate Before Upload	Recommended. Validating prevents invalid data from being uploaded to Oracle E-Business Suite. It is useful when you expect the import process to be quite lengthy or when you plan to run it unattended, such as overnight.
   Automatically Submit Import	Select this option to automatically submit a processing request for the data after upload. This parameter is available when a post-processing procedure has been set up for the integrator. A post-processing procedure may be a call to a server-side procedure or a concurrent program to continue processing the data in Oracle E-Business Suite.
   Commit Rows	All Rows Select this option to commit the uploaded rows only if all the rows are valid. If any row is invalid, then no rows are committed. Each Row Select this option to commit each uploaded row that is valid, even if other rows are invalid.

5. Start the upload process. After the upload process completes, the upload window indicates whether or not the upload was successful. Oracle Web Applications Desktop Integrator returns messages to the spreadsheet identifying all rows containing invalid values. By default, if the data in any rows are invalid, Oracle Web Applications Desktop Integrator does not upload any of the data in the spreadsheet. You must correct all errors to successfully upload the spreadsheet. However, if your integrator provides the Commit Rows upload parameter and you chose the Each Row option, then Oracle Web Applications Desktop Integrator does upload the valid rows. You can correct the errors in any invalid rows and retry the upload for those rows.

   Any errors that occur during the upload of a multi-sheet workbook are also displayed in a Summary Worksheet. The Summary Worksheet enables you to see all errors and link to them.

Compressing Data for Upload

The size of the data that you can upload from a spreadsheet is determined by the available memory (RAM) on your PC. If your site does not use OOXML and you need to upload a large amount of data, you can choose to compress the data using WinZip or 7Zip. Compression increases the number of records that you can upload at once.

To use this feature, you must have either the WinZip or 7Zip compression application installed on your PC.

Note: The compression feature is designed to optimize the upload capacity. However, the total size of the upload is still limited by the available memory. If the size of the data in a very large file exceeds this limit even after compression, you may still encounter an error. In this case, divide the data into smaller batches to perform the uploads.

If your site uses OOXML, then the document is automatically uploaded as a ZIP compressed OOXML file.

To compress data for upload:

1. Set the BNE: Enable Upload Compression profile option to Yes.

2. Create your spreadsheet.

3. In the spreadsheet, select Settings from the Oracle ribbon tab and specify the compression application to use, either WinZip or 7Zip, and the location of the corresponding executable. The standard locations for these executables are as follows:

   • WinZip: C:\Program Files\WinZip\WZZIP.EXE

   • 7Zip: C:\Program Files\7-Zip\7z.exe

4. Submit the upload.

Downloading Data

Download enables you to refresh data that you have imported into your spreadsheet. This option is available only if the spreadsheet contains imported data. To download or refresh data, from the Oracle ribbon tab, select Download.

Note: Any new rows of data or other modifications that you have made on the spreadsheet will be overwritten when you use Download to refresh the data. Some product integrators may not allow refresh.

Additionally, for OOXML documents, manually inserted worksheets are not retained when you use Download. To refresh an OOXML document, Oracle Web Applications Desktop Integrator creates a new document on the server that then replaces the current version on the desktop. Consequently, any manually inserted worksheets are no longer included after the refresh.

If you imported data from a text file, Download will not be available.

Monitoring Concurrent Requests

The upload process of some of the integrators starts a concurrent program to import the data you have uploaded to an interface table in Oracle E-Business Suite. You can monitor concurrent requests from the spreadsheet.

To monitor concurrent requests from the spreadsheet, from the Oracle ribbon tab, select Monitor. The Monitor Requests window displays the last 10 concurrent requests submitted by the current user.