Staging table structure

RECORD_SYS_ID

NOT NULL

NUMBER(18)

EXTERNAL_ID

NOT NULL

NUMBER(18)

NAME

NOT NULL

VARCHAR2(80 CHAR)

...

INTERNAL_SYS_ID

NUMBER(18)

LAST_IMPORT_ATTEMPT

DATE

SHOULD_IMPORT_NEXT_RUN

CHAR(1)

NEED_TO_RESOLVE_RELATIONSHIP

CHAR(1)

COMPANY

NOT NULL

VARCHAR(80)

RECORD_SYS_ID

Provides a unique ID for this record in the staging table and the import process.

• In Oracle, a unique value must be provided for this field. This unique value can either be explicitly supplied or automatically generated using the nextval built-in function on a sequence table (For example, IDS_XXX.nextval).

• In MS SQL Server, values are not needed for this column because it is defined as an "identity" column. This marking instructs MS SQL Server to auto-generate unique values upon insertion of rows into the table.
  
  For example, if a staging table is created as follows:
  CREATE TABLE IDD_PERSON ID NUMERIC IDENTITY(1) NOT NULL NAME VARCHAR(20) NOT NULL)
  The following SQL would be used to load this table:
  INSERT INTO IDD_PERSON (NAME) VALUES ('Mark')
  
  Note: This example does not include the ID column because an error would appear. If you need RECORD_SYS_ID, use the following code:
  

  
  SET IDENTITY OFF
  INSERT INTO IDD_PERSON (ID, NAME) VALUES (21, 'Mark')
  SET IDENTITY ON
  

EXTERNAL_ID

Populated with the unique ID of the record (if captured) in the source system. Otherwise, a unique value must be supplied for this field. Upon successful import, this value is stored internally by BMC IT Business Management Suite with the record for subsequent reference by other staging table records.

For example, client records in a source system are usually uniquely identified by their employee number. This employee number should be used in the EXTERNAL_ID field of the IDD_CLIENT table for import purposes. When you need to reference this client (for example, when providing a value for the OWNER column of the IDD_PROJECT table), BMC recommends that you use the external ID of the client to look up the object. If the record is used to update an existing record in the BMC IT Business Management Suite database, the value of the EXTERNAL_ID column has priority over that of NAME, when both are supplied.

NAME

Must be set with a unique name of the record being imported. The only exception to this is the IDD_BUSINESS_UNIT table.

INTERNAL_SYS_ID

Keeps track of records that have already been imported and determines if updates should occur on subsequent data imports (internal to BMC IT Business Management Suite).

LAST_IMPORT_ATTEMPT

Keeps track of records that have already been imported and determines if updates should occur on subsequent data imports (internal to BMC IT Business Management Suite).

SHOULD_IMPORT_NEXT_RUN

Keeps track of records that have already been imported and determines if updates should occur on subsequent data imports (internal to BMC IT Business Management Suite).

NEED_TO_RESOLVE_RELATIONSHIP

Keeps track of records that have already been imported and determines if updates should occur on subsequent data imports (internal to BMC IT Business Management Suite).

COMPANY

Must be set with the EXTERNAL_ID value for a tenant company found in the ITT_COMPANY table.