The database framework allows 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.
- Invoke Extensible APIs that store and retrieve data from hang-off
tables.
- Invoke 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 which apply to the creation of custom
or hang-off tables:
- You can only determine if an entity is enabled for hang-off by
referencing the associated Entity Relationship Diagram (ERD) located
in the install_dir/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 till "n" levels for both OOB tables
and custom tables. However, creating a chain of Hang-off tables may
impact the system performance as more SQL calls will be 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 will not export entities
unless they are associated with a parent LockingEntity. For example,
the following 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 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.
- Javadocs are 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 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 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 by the Sterling Selling and Fulfillment
Foundation framework.