Selecting and configuring tables, views, and synonyms or nicknames for outbound processing

Select and configure business objects for tables, views, and synonyms or nicknames to be used in your module. For inbound processing, the selected business objects are delivered in events.

1. In the Discovered objects list of the Find Objects in the Enterprise System window, select one or more tables, views, or synonyms from the Discovered objects list, and click the > (Add) button to add the object or objects to the Selected objects list. Alternatively, you can also filter the tables, views, or synonyms by specifying a valid name or pattern for at least one of the filter fields in the Filter Properties window.
   1. Click the object you want to filter, and then click the (Create or edit filter) button, at the top of the Discovered objects pane.
   2. In the Filter Properties window, type a name or pattern in the Object name or pattern field.
      Use the question mark or underscore (? or _) to match a single character, and the asterisk or percentage (* or %) to match multiple characters. The name is not case-sensitive.
   3. Click OK. The object that matches the defined filter condition is displayed.
   4. Select one or more objects from the discovered list, and click the > (Add) button to add the object to the Selected objects list.
      

   The following two figures show a typical Specify the Configuration Properties for 'object' window for a table, view, synonym, or nickname business object. The first figure shows a typical window for the first table or group of tables that are selected.

   

   The following figure shows a typical window for subsequent tables you select. After you select and configure at least one table, the Specify the Configuration Properties for 'object' window for subsequent tables displays an area where you can optionally define a parent-child hierarchy between tables.

   

   As you configure the object, choices that require advanced configuration might present additional fields in this window, causing the window to scroll. Ensure that you examine all the fields on the window before clicking OK.

2. If the table has a column that is used to indicate logical deletes:
   1. Select the column name in the Name of the column used to perform logical deletes field.
   2. In the Value used to indicate a deleted object field, type the value that indicates that a row is logically deleted. You can get this value from your database administrator.
3. If the Select primary key for table table_name area is displayed, click Add, select the column to be used as the primary key for the table business object, and then click OK. If the table has a composite key, you can select multiple columns. The Select primary key for table table_name area is displayed only when the database table does not have a column designated as the primary key. Each table business object must have a primary key, even if the associated database table does not have a key. If the primary key is defined in the database, this section of the window is not displayed.
4. Optional: Define a parent-child relationship between business objects.

   To build a parent-child hierarchy, configure the parent table first, and return to the Find Objects in the Enterprise System window to select and configure the child tables.

   Configure the parent-child relationship using the area of the Specify the Configuration Properties for 'object' window shown in the following figure. These fields are not displayed for the first table you configure.

   
   1. In the Choose parent table field, select the name of the parent table you are configuring. If you do not see the parent table in the list, the parent table has not yet been configured. Go back and configure the parent object before configuring the child objects.
   2. Specify the cardinality of the relationship:
      • If the table has a single-cardinality relationship with the parent table, select the Single cardinality check box. In a single cardinality relationship, a parent can have only one child business object of this type. A single-cardinality relationship can be used with ownership to represent a true child or without ownership to represent lookup tables or other peer objects in a database.
      • If the table has a multiple-cardinality relationship, do not select the Single cardinality check box. In a multiple-cardinality relationship, a parent can have an array of child business objects of this type.
   3. Build the foreign key relationship between the parent and child by specifying for each child column whether it is a foreign key in the parent table.
      • If the child column is not a foreign key, select NONE.
      • If a child column is a foreign key, select the column in the parent table that corresponds to the child column.
        Note: The wizard can configure only a single parent table. If the child table has multiple parent tables, you must use the business object editor to configure the remaining parent tables after exiting the wizard.
   4. If the parent object owns the child object, then the child objects in the database are deleted when the parent is deleted. To indicate that this child is owned by its parent, select the Parent object owns child object (cascade delete) check box. Otherwise, clear this option to prevent child objects, such as lookup tables, from being deleted when their parent is deleted.
   5. If you do not want the child objects to be deleted as part of an Update operation, select the Preserves child_table_name when the parent is updated check box.

      When a parent table is updated, the adapter compares the child business objects present in the input with the child business objects returned from the database. By default, the adapter deletes any child objects returned from the database that are not present in the input business object.

   6. By default, you can perform operations on parent business objects without specifying the child business objects. If you want to ensure that a parent business object specifies its child business objects when the parent is submitted for a change, select the Child_table_name required for operations on parent check box.
5. An operation can be performed using either a standard SQL statement generated by the adapter or using stored procedures or stored functions from the database. If you want to use stored procedures or stored functions:
   1. Click Add.
   2. In the Add window, select the type of the stored procedure you want to run. For each operation, you can select a stored procedure that performs the operation, as well as stored procedures that run before or after the operation. For example, for the Create operation, you can specify any of these stored procedures: CreateSP, BeforeCreateSP, and AfterCreateSP.
      Note: If you configure the table with RetrieveAllSP, ensure that at least one parameter of the stored procedure is a Cursor and the ResultSet ASI for the stored procedure is set to true to avoid the "No resultset found associated with the stored procedure" exception being generated at run time.
   3. Click OK. The Specify the Configuration Properties for 'object' window now shows the stored procedure types you selected and expands to display an area where you configure each one. It might be necessary to scroll down to see the new areas.
      
      Note: In a hierarchical business object, if you want the stored procedure to be performed for each business object in the hierarchy, you must separately associate a stored procedure with the top-level business object and each child business object or an array of business objects. If you associate a stored procedure with the top-level business object but do not associate it with each child business object, then the top-level business object is processed with the stored procedure, but the child business objects are processed using the standard SQL query.
6. For each stored procedure type that you selected, specify the name of the stored procedure in the database and then configure the business object.
   1. In the Schema name field, select the name of the schema that contains the stored procedure.
      
      1. Click Select.
      2. In the Select Value window, select the name of the schema you want to work with.
      3. Click OK.
   2. Specify the name of the stored procedure or stored function.
      1. In the Stored procedure name or pattern field, either type the name of the stored procedure or stored function, or type a name pattern. Use the question mark or underscore (? or _ ) to match a single character, and the asterisk or percentage sign (* or %) to match multiple characters.
      2. In the Stored procedure name field, select the name of the procedure you want. If the stored procedure list contains many items, the Select button is displayed next to the Stored procedure name field. Click Select to open the Select window and select the name of the stored procedure or stored function.
      The Specify the Configuration Properties for 'object' window expands to provide an area where you configure the stored procedure. The wizard automatically generates the list of parameters by examining the stored procedure in the database.
   3. For each parameter in the stored procedure (on the left), select the table column (on the right) to pass to the stored procedure in that parameter. The following figure shows a portion of the window after a stored procedure has been configured.
      
7. To specify the data type mapping for each column in the table:
   1. Click Advanced.
   2. Expand Table columns. For each column in the table, the default data type mapping is displayed. For Oracle databases, if the table contains any complex data type such as an array, structure, nested structure or table, the type name and the child attribute details are also automatically discovered and displayed. The following figure displays the type name and child attribute details of an Oracle table containing complex data types.
      
   3. Review the mapping and make the required changes.
   Note: If the primary key in a table is of the date or timestamp type, then the object_key in the event_table must be in the 'yyyy-mm-dd hh-mm-ss' format.
8. When all the fields in the window are completed, click OK to save the configuration of the business object. The table, view, synonym, and nickname business objects you defined are now listed in the Find Objects in the Enterprise System window.
9. To change the configuration of an object from the Selected objects list, select the object name and then click the (Edit) icon.
   
10. When you have selected and configured all business objects that you need, click Next to set global properties and configure wrapper business objects.