Creating custom and hang-off tables

The database framework facilitates you to extend the application database by creating custom or hang-off tables.

A custom table is an independent table and cannot be modeled as an extension to a standard application database table.

A hang-off table is a table with a many-to-one relationship with a standard application database table.

Creating a custom or hang-off entity enables you to:

  • Create a relationship between a standard table and a hang-off table.
  • Start Extensible APIs that store and retrieve data from hang-off tables.
  • Start dbverify for generating appropriate SQL scripts to create or alter tables for custom or hang-off entities.
  • Audit item and organization tables.
Keep in mind the following guidelines that apply to the creation of custom or hang-off tables:
  • You can determine whether an entity is enabled only for hang-off by referencing the associated Entity Relationship Diagram (ERD) located in the <runtime_sandbox>/xapidocs/ERD directory.
  • Based on the Extensions.xml file, the application does not create a foreign key constraint in the EFrame_TableChanges.sql, but the foreign key relationship is enforced.
  • Currently, only order, order line, work order, shipment, item, and organization tables are marked as hang-off enabled.
  • Hang-off table can be created until "n" levels for both OOB tables and custom tables. However, creating a chain of Hang-off tables might impact the system performance as more SQL calls are made.
  • Custom and hang-off table names must not start with a Y.
  • A child custom or hang-off table must include the LockingEntity attribute, and its value must match the value of the Name attribute of its parent table. Data Sync in Delta mode does not export entities unless they are associated with a parent LockingEntity. For example, the following attribute settings illustrates that YFS_CARRIER_SERVICE_DOW is locked by its parent YFS_CARRIER_SERVICE.
    In the parent YFS_CARRIER_SERVICE entity definition, the Name attribute is set to "Carrier_Service":
    <Entity AuditRequired="Y" Cacheable="true"
   ConfigurableByOrgOrNode="Y"
   Description="Services provided by a carrier."
   EntityType="CONFIGURATION" Extensible="Y" Module="ysc"
   Name="Carrier_Service" Prefix="YFS_"
   TableName="YFS_CARRIER_SERVICE" TableType="CONFIGURATION" XMLName="CarrierService">
    In the child YFS_CARRIER_SERVICE_DOW entity definition, the LockingEntity attribute must be included and it must be set to "Carrier_Service" to match the value of the Name attribute of its parent table.
    <Entity AuditRequired="Y" Cacheable="true"
   ConfigurableByOrgOrNode="Y"
   Description="Defines a carrier service's schedule for days that can be delivered and are available for transit."
   EntityType="CONFIGURATION" Extensible="N" Module="ysc"
   Name="Carrier_Service_Dow" Prefix="YFS_"
   TableName="YFS_CARRIER_SERVICE_DOW"
   TableType="CONFIGURATION" XMLName="CarrierServiceSchedule"
   LockingEntity="Carrier_Service">
  • The "Extn" part is trimmed off from the XML name of the custom and hang-off tables.
  • Primary key name must not start with a Y.
  • Primary key can be of numeric data type.
  • Entity names must start with the prefix that is provided in the entity definition.
  • The YIFApi interface does not extend APIs for custom/hang-off tables. Therefore, the APIs for these tables must be configured as services.
  • Javadoc is not created for the APIs created by the infrastructure to support custom and hang-off tables.
  • XSD generation and validation is not done for custom or hang-off tables.
  • The XMLName and Name attributes are both required when using a non-ASCII character set.
  • Every custom or hang-off entity must have a primary key.

    Column Name

    Data Type

    Default Value

    Key-Column

    Key OR Any numeric data type

    ' ' (space)
  • A custom or hang-off entity must have the following columns that are described in the following table:

    Column Name

    Data Type

    Default Value

    CREATETS

    TimeStamp

    sysdate

    MODIFYTS

    TimeStamp

    sysdate

    CREATEUSERID

    UserId

    ' ' (space)

    MODIFYUSERID

    UserId

    ' ' (space)
  • (Optional) A custom or hang-off entity can have the following columns that are described in the following table:

    Column Name

    Data Type

    Default Value

    CREATEPROGID

    ProgramID

    ' ' (space)

    MODIFYPROGID

    ProgramID

    ' ' (space)

    LOCKID

    Lockid

    0 (zero)
    Note: In DB2® database, the Date data type is generated as TIMESTAMP.