EBS Object Types - Data Fixes
XML files containing one or more data fix sql statements, sql statements to back up data, and validation statements used to determine whether the data fix should be committed or rolled back.
Object Type Identification
If a file with .xml extension is in the folder specified by the Data Fix Root Source Directory project property, then it is considered a data fix file
File Extensions
Name | Description |
|---|---|
.xml | Extensible Markup Language file format used to create common information formats and share both the format and the data using standard ASCII text. |
Object Type
Name | Code |
|---|---|
Data Fix |
|
Object Type Attributes
Name | Code | Description | Required | Default Value | Supported Values |
|---|---|---|---|---|---|
Source |
| Object source location type | Yes | SCM | SCM |
Target Location |
| Path to where the file should be deployed to | No | ||
Database User Property |
| Property name for database user name | No |
| |
Database Password Property |
| Property name for database password | No |
|
Related Project Properties
Name | Code | Description | Default Value |
|---|---|---|---|
Data Fix Root Source Directory |
| The source directory to recognize files as data fixes | datafix |
Data Fix Root Destination Directory |
| The destination directory to copy file to. Leave blank to not copy file anywhere |
Related Target Properties
Name | Code | Required | Description |
|---|---|---|---|
JDBC Driver Path |
| Yes | Location of JDBC driver file required only for running data fix files. For example, /u01/app/oracle/product/12.1.0/dbhome_1/jdbc/lib/ojdbc6.jar |
Sample Build Commands
N/A - Build commands not supported for this type.
Sample Deploy Commands
N/A - Deploy commands not supported for this type.
XML Schema
Elements without min/max occurrences listed have the default values for cardinality, which are minOccurs="1" and maxOccurs="1"
Data Fix XML Schema (XSD)
<?xml version="1.0" encoding="windows-1252" ?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://flexagon.com/database/datafix" targetNamespace="http://flexagon.com/database/datafix" elementFormDefault="qualified">
<xsd:element name="DataFix">
<xsd:annotation>
<xsd:documentation>Data fix object</xsd:documentation>
</xsd:annotation>
<xsd:complexType>
<xsd:all>
<xsd:element type="xsd:string" name="Description"/>
<xsd:element type="xsd:string" name="FixSQL"/>
<xsd:element type="xsd:string" name="BackupSQL"/>
<xsd:element type="xsd:string" name="ConnectStringProperty" minOccurs="0" maxOccurs="1"/>
<xsd:element type="xsd:string" name="UserProperty" minOccurs="0" maxOccurs="1"/>
<xsd:element type="xsd:string" name="PasswordProperty" minOccurs="0" maxOccurs="1"/>
<xsd:element name="Validations">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="Validation" minOccurs="1" maxOccurs="unbounded">
<xsd:complexType>
<xsd:sequence>
<xsd:element type="xsd:string" name="Description"/>
<xsd:element type="xsd:string" name="ValidationSQL" minOccurs="0" maxOccurs="1"/>
<xsd:element name="Environment" minOccurs="1" maxOccurs="unbounded">
<xsd:complexType>
<xsd:all>
<xsd:element type="xsd:string" name="EnvironmentCode"/>
<xsd:element name="Before" type="Operators" minOccurs="0" maxOccurs="1"/>
<xsd:element name="After" type="Operators" minOccurs="0" maxOccurs="1"/>
<xsd:element name="UpdateCount" type="Operators" minOccurs="0" maxOccurs="1"/>
</xsd:all>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:all>
</xsd:complexType>
</xsd:element>
<xsd:complexType name="Operators">
<xsd:all>
<xsd:element name="Equals" type="xsd:int" minOccurs="0" maxOccurs="1"/>
<xsd:element name="NotEquals" type="xsd:int" minOccurs="0" maxOccurs="1"/>
<xsd:element name="LessThan" type="xsd:int" minOccurs="0" maxOccurs="1"/>
<xsd:element name="LessThanEqual" type="xsd:int" minOccurs="0" maxOccurs="1"/>
<xsd:element name="GreaterThan" type="xsd:int" minOccurs="0" maxOccurs="1"/>
<xsd:element name="GreaterThanEqual" type="xsd:int" minOccurs="0" maxOccurs="1"/>
</xsd:all>
</xsd:complexType>
</xsd:schema>Details of data fix elements are described in the table below.
Element | Path | XSD Line Number | Description |
|---|---|---|---|
DataFix | / | 3 | The root element for the data fix |
Description | /DataFix | 9 | A description for the data fix being run |
FixSQL | /DataFix | 10 | The SQL which implements the data fix. It can be multiple statements delimited by a semicolon or slash |
BackupSQL | /DataFix | 11 | SQL to perform necessary backup of data before executing FixSQL. This can be one or more statements delimited by a semicolon or slash Include ${{FD_PROJECT_VERSION}} in the title of your backup table to easily identify it after execution |
ConnectStringProperty | /DataFix | 12 | The JDBC connection string for the database. The APPS_JDBC_URL environment variable will be used if not specified in file |
UserProperty | /DataFix | 13 | User to connect to database with. The object attribute will be used if property is not specified in file |
PasswordProperty | /DataFix | 14 | Password for the database user. The object attribute will be used if property is not specified in file |
Validations | /DataFix | 15 | The rule(s) used to validate the data fix before committing changes |
Validation | /DataFix/Validations | 18 | A single validation rule |
Description | /DataFix/Validations/Validation | 21 | A description for the validation rule |
ValidationSQL | /DataFix/Validations/Validation | 22 | Select query which is executed before and after the FixSQL is executed, and result is compared with before and after values. This must be one valid SQL statement, not followed by a semicolon |
Environment | /DataFix/Validations/Validation | 23 | The expected results defined by an environment |
EnvironmentCode | /DataFix/Validations/Validation/Environment | 26 | FlexDeploy environment code, or DEFAULT to apply to any deploy environment not explicitly specified |
Before | /DataFix/Validations/Validation/Environment | 27 | Contains results to check before FixSQL is executed |
After | /DataFix/Validations/Validation/Environment | 28 | Contains results to check after FixSQL is executed |
UpdateCount | /DataFix/Validations/Validation/Environment | 29 | Results to compare return of JDBC executeUpdate() of FixSQL. If the FixSQL contains more than one statement, it is the sum of the updated rows |
Operators | N/A | 42 | Complex type containing list of accepted operators to be used in validations |
Data Fix Source Template
Basic Data Fix Template
<?xml version="1.0" encoding="UTF-8"?>
<DataFix xmlns="http://flexagon.com/database/datafix">
<Description></Description>
<FixSQL></FixSQL>
<BackupSQL></BackupSQL>
<ConnectStringProperty></ConnectStringProperty>
<UserProperty></UserProperty>
<PasswordProperty></PasswordProperty>
<Validations>
<!-- Can be one or more -->
<Validation>
<Description></Description>
<ValidationSQL></ValidationSQL>
<!-- Can be one or more -->
<Environment>
<EnvironmentCode></EnvironmentCode>
<Before>
<Equals></Equals>
<NotEquals></NotEquals>
<GreaterThan></GreaterThan>
<GreaterThanEqual></GreaterThanEqual>
<LessThan></LessThan>
<LessThanEqual></LessThanEqual>
</Before>
<After>
<Equals></Equals>
<NotEquals></NotEquals>
<GreaterThan></GreaterThan>
<GreaterThanEqual></GreaterThanEqual>
<LessThan></LessThan>
<LessThanEqual></LessThanEqual>
</After>
<UpdateCount>
<Equals></Equals>
<NotEquals></NotEquals>
<GreaterThan></GreaterThan>
<GreaterThanEqual></GreaterThanEqual>
<LessThan></LessThan>
<LessThanEqual></LessThanEqual>
</UpdateCount>
</Environment>
</Validation>
</Validations>
</DataFix>Data Fix Source Examples
The first example is a simple data fix that updates one row of a table, then verifies only the expected row is updated.
Product Price Fix
<?xml version="1.0" encoding="UTF-8"?>
<DataFix xmlns="http://flexagon.com/database/datafix">
<Description>Fix list price of product ID 47809</Description>
<FixSQL>update xxhr.XXHR_PRODUCT_LIST set list_price = 150 where product_id = 47809;</FixSQL>
<BackupSQL>create table xxhr.xxhr_product_list_bkp as (select * from xxhr.xxhr_product_list)</BackupSQL>
<ConnectStringProperty>CONNECT_STRING_PROPERTY</ConnectStringProperty>
<UserProperty>DB_USER_PROPERTY</UserProperty>
<PasswordProperty>DB_PASSWORD_PROPERTY</PasswordProperty>
<Validations>
<Validation>
<Description>Make sure product_id = 47809 has a list_price of 150</Description>
<ValidationSQL>select count(*) from xxhr.xxhr_product_list where list_price = 150 and product_id = 47809</ValidationSQL>
<Environment>
<EnvironmentCode>DEV</EnvironmentCode>
<Before>
<Equals>0</Equals>
</Before>
<After>
<Equals>1</Equals>
</After>
<UpdateCount>
<Equals>1</Equals>
</UpdateCount>
</Environment>
</Validation>
</Validations>
</DataFix>This example updates a row on the order_header table, then validates that the order_item total matches the updated order_header total.
Order Header Fix
This example is a data fix that updates the price of multiple products, then verifies the amount of items affected by this change.
Line Item Fix
- style