Example of a complete table definition
<Object SchemaName="ORA-Responsibility" ParentSchemaName="ORA-Application" DisplayPattern="%vrtDistinguishedName%" IsReadOnly="true" UseDistinct="false">
<ObjectKey>
<Key Column="APPLSYS.FND_RESPONSIBILITY.RESPONSIBILITY_ID" IsDNColumn="true" IsReferencedColumn="true" X500Abbreviation="RE" />
<Key Column="APPLSYS.FND_RESPONSIBILITY.APPLICATION_ID" />
</ObjectKey>
<Tables>
<Table Name="FND_RESPONSIBILITY" Schema="APPLSYS" APK="" USN="" WhereClause="" JoinParentColumn="" JoinChildColumn="" >
<PK Column="APPLSYS.FND_RESPONSIBILITY.RESPONSIBILITY_ID" />
<ParentTableFK Column="APPLSYS.FND_RESPONSIBILITY.APPLICATION_ID" ParentColumn="APPLSYS.FND_APPLICATION.APPLICATION_ID" />
</Table>
<Table Name="FND_RESPONSIBILITY_TL" Schema="APPLSYS" APK="" USN="APPLSYS.FND_RESPONSIBILITY_TL.LAST_UPDATE_DATE" WhereClause="APPLSYS.FND_RESPONSIBILITY_TL.LANGUAGE='$SYSLANGU$'" JoinParentColumn="APPLSYS.FND_RESPONSIBILITY.RESPONSIBILITY_ID,APPLSYS.FND_RESPONSIBILITY.APPLICATION_ID" JoinChildColumn="APPLSYS.FND_RESPONSIBILITY_TL.RESPONSIBILITY_ID,APPLSYS.FND_RESPONSIBILITY_TL.APPLICATION_ID" >
<PK Column="APPLSYS.FND_RESPONSIBILITY_TL.RESPONSIBILITY_ID" />
</Table>
<Table Name="FND_APPLICATION" Schema="APPLSYS" APK="" USN="" WhereClause="" JoinParentColumn="APPLSYS.FND_RESPONSIBILITY.APPLICATION_ID" JoinChildColumn="APPLSYS.FND_APPLICATION.APPLICATION_ID" >
<PK Column="APPLSYS.FND_APPLICATION.APPLICATION_ID" />
</Table>
</Tables>
</Object>
Explanation
The definition above shows the declaration of the ORA-Responsibility schema type as it is used internally by the Oracle E-Business Suite connector.
The schema type is subordinate to the ORA-Application schema type in the hierarchy (ParentSchemaName). It has to object key columns (APPLSYS.FND_RESPONSIBILITY.RESPONSIBILITY_ID and APPLSYS.FND_RESPONSIBILITY.APPLICATION_ID), of which only one is included as a part of the distinguished name IsDNColumn="true". The column APPLSYS.FND_RESPONSIBILITY.APPLICATION_ID is a part of the DistinguishedName of the superordinate schema type, which is added automatically at the end.
For the selection of all properties, records from the tables FND_RESPONSIBILITY, FND_RESPONSIBILITY_TL and FND_APPLICATION are queried by a Join operation. The columns for the Join operation are specified in the relevant attributes JoinParentColumn and JoinChildColumn.
The description text is read from the table FND_RESPONSIBILITY_TL in the language specified by the database connection configuration. For this reason, the symbolic variable $SYSLANGU$ is used in the Where clause. For more information, see Symbolic variables in WHERE clauses.
Task definitions
The <Functions> tag enables you to define methods within the object definition that can be executed for objects of the schema type. Each method executes any number of SQL functions.
The name of the XML tag for a method determines the method name. One or more functions are defined within the method section. These functions are executed in a defined sequence when the corresponding method is called on an object of the schema type.
Structure of the task definitions
<Functions>
<Insert>
<Function ... OrderNumber="1" >
</Function>
<Function ... OrderNumber="2" >
</Function>
</Insert>
<Delete>
<Function ...>
</Function>
</Delete>
</Functions>
In this example, the schema type has two methods, Insert and Delete. When Insert is called, two functions must be executed that are placed in a fixed order based on their OrderNumber attribute. When the Delete method is called, only one defined function is executed.
Function definitions
The <Function> section defines the name, execution sequence, and parameter settings of SQL function calls.
Table 12: Attributes of a function definition
|
Name |
Name of the function. Full notation in the form <Schema name>.<Package name>.<Function name>. |
|
OrderNumber |
Numerical specification of the execution sequence. The default value is 1. |
The function package that provides functions for the modification of user accounts (APPS.FND_USER_PKG) is a special case. Due to the permission restrictions when executing the functions of this package, you may need to implement a wrapper package that changes the call context. The name of this wrapper package can be saved in the connection configuration. It is replaced at runtime before execution of the function in the SQL block. The symbolic variable for the defined package name is $ebsUserPackageName$. For more information, see Setting up an initial synchronization project.
Example
<Function Name="$ebsUserPackageName$.CreateUser" OrderNumber="1" >
Parameter definitions
The <Parameter> tags define the parameters to be transferred to a function, together with their type and the source of the parameter value.
Table 13: Attributes of a parameter definition
|
Name |
Name of the parameter in the function definition. |
|
PropertyName |
Name of the object property whose value is to be transferred (full notation).
- OR -
Fixed value, if PropertyType="FIX" is defined. |
|
PropertyType |
Data type Possible values:
- CHAR: Character string.
- DATE: Date value. This value is converted as a valid date.
- FIX: Fixed string value. The fixed value specified in the PropertyName attribute is always transferred.
- NUM: Numerical value. The conversion does not permit any alphanumeric characters.
|
|
Mandatory |
Specifies whether the parameter is mandatory. The default value is false. |
|
NullValue |
Value or character string to be transferred as the null value.
This input is required in order to fill parameters with values specifically defined in function packages or generally known in Oracle Database as a Null representation. This parameter is optional. By default, when a null value is detected in a mandatory parameter, the character string null is transferred. In this case, an optional parameter is not transferred to the function call.
In three cases, a null value definition makes sense:
- Use of a constant defined in the function package, for example $ebsUserPackageName$.null_number. In this case, the name of the function package stored in the connection configuration is used for user account modification, if the variable expression $ebsUserPackageName$ is detected.
- Use of a symbolic constant defined in the Oracle Database, for example sysdate.
- Use of a specific expression not equal to null, for example to_date('-2', 'J').
|
Example
<Parameter Name="start_date" PropertyName="APPS.FND_USER_RESP_GROUPS_DIRECT.START_DATE" PropertyType="DATE" Mandatory="TRUE" NullValue="sysdate" />
