19 Working with Database Output

This chapter contains the following topics:

• Section 19.1, "Understanding Database Output"

• Section 19.2, "Using Database Output to Update Data"

19.1 Understanding Database Output

You can use database output to update the database using batch applications. You can attach a database output specification to any report section with an attached business view. The Database Output option is available only for report templates that are defined as update reports.

You can use database output in Oracle's JD Edwards EnterpriseOne Report Design Aid (RDA) to update, insert, or delete records in Oracle's JD Edwards EnterpriseOne tables. Use the Insert or Update special operation to insert a record into a table. If a record with the same primary key already exists in the table, the insert fails and the system updates the existing record instead.

You can use database output to load data into JD Edwards EnterpriseOne tables or into text files. Text files can be specified as either comma delimited or fixed-length text records. The comma-delimited format is useful for transferring data to spreadsheets.

19.2 Using Database Output to Update Data

This section provides an overview of database output in JD Edwards EnterpriseOne, lists the prerequisites, and discusses how to:

• Define database output.

• Override environments for database output.

19.2.1 Understanding Database Output in JD Edwards EnterpriseOne

This table describes the tools that you use to perform database maintenance in JD Edwards EnterpriseOne:

Tool	Description
Table Conversion	Use for high-performance SQL table-to-table processing. Enables access to non-JD Edwards EnterpriseOne tables. No reports are created.
Database Output	Use to affect tables in different environments that are defined on the Advanced tab of the Report Properties form. Enables you to send data to text files. Output to the database and reports is processed simultaneously.
Table I/O	Use for transforming input data using event rules. The results can be reviewed before affecting the database. Output to the database and reports is processed simultaneously.

19.2.1.1 Database Output

Database output is performed for every row of data that is processed in the detail section. All database operations occur on a record-by-record basis using the standard JDE Base middleware Application Programming Interfaces (APIs).

Defining database output involves defining the output and then overriding the environment, if appropriate. If no override is defined, the database output runs against the default data source.

When you design database mappings in batch applications, RDA determines where the system stores the flat files that are produced by the mapping. You can also define a default destination using the UBEDBOutputLocation setting in the UBE section of the jde.ini file. If no location is defined in RDA or in the jde.ini file, or if the location is invalid, the files are stored in the current working directory.

The first step in defining database output is to select an operation for mapping the targets:

• Insert only

  Can be used for tables, but is the only appropriate option for text files.

• Update

• Insert or update

• Delete

Next, you need to select the type of target that you are using:

• JD Edwards EnterpriseOne table.

• Comma-delimited text file.

• Fixed-record length text file.

You can suppress a defined database output:

• Override the database output in the version, and delete the mapping record to process the database output for a specific version of the report.

• Define the criteria using If statements and then using the Suppress Section Write system function to process the database output for specific rows of data.

  The Suppress Section Write system function suppresses not only printed output but also database mappings.

See Also:

• "Designing Tables in JD Edwards EnterpriseOne", "Working with Table Input, Output" in the JD Edwards EnterpriseOne Tools Table Conversion Guide.

19.2.2 Prerequisites

Before you begin working with database output, ensure that you:

• Create a batch application object.

• Define the report template as an Update report.

• Complete the design of the report template.

19.2.3 Defining Database Output

Open a report template in RDA.

1. Click a detail section of the report, and select Database Output from the Section menu.

2. On the Mapping Targets form, select an operation in the Operation(s) field of the detail area.

   You can double-click the Operation(s) and Type fields to view a list of available options. Double-click the option to select it.

3. In the Type field, select the type of file that you are using as a target.

   You can use the same source type several times using different operations.

4. In the Name field, enter the name of the file that you are using as a target, and click Next.

5. On the Mappings form, for each target column, specify which of the available objects from the report section should be assigned (mapped).

   Double-click a Source Section or a Source Object field to display a list of options.

   You can also enable data dictionary overrides from this form.

6. On the Mappings form, click the Map Same button if you want to map all columns.

7. When you have completed the mapping, click Finish.

19.2.4 Overriding Environments for Database Output

Open a report template in RDA.

1. From the File menu, select Report Properties.

2. On the Properties form, select the Advanced tab.

3. Complete the Target field.

   You can also click the Browse button to select the target environment.

4. Complete the Source field

   You can also click the Browse button to select the source environment.

5. Select the Prompt for overrides at runtime option, and click OK.