Step 1: Populate the Request Interface Tables

  1. Using SQL*Loader, your favorite tool, or direct Oracle database-to-database communication, load your data into the required input columns in the following tables:

  2. (Optional) Use the applicable field group interface tables from Open Interface Data Models.

  3. (Optional) Modify of the table records after they have been brought into the interface table.

    This can include the:

    • Setting of ID columns, such as GROUP_ID and TRANSACTION_ID

    • Defaulting of specific data not available in the source of the request, such as the third-party application or the ASCII file

    Caution: User data is not validated during import.

Table 4-1. KCRT_REQUESTS_INT interface table through Table 4-4. KCRT_TABLE_ENTRIES_INT interface table describe the interface tables that must be populated with data.

Table 4-1. KCRT_REQUESTS_INT interface table

Column

Usage

Data Type

Description

DATA_LANG

I

VARCHAR2

Specifies the language of the data being imported, so it can be properly validated in the respective language.

If no value is provided, then the language context is the same as the system language of the PPM instance into which the data is being imported.

GROUP_ID

Required

NUMBER

Groups all the records that should be processed at the same time.

Use only one GROUP_ID each time you run a report.

Derived from the KNTA_INTERFACE_GROUPS_S sequence.

TRANSACTION_ID

Required

NUMBER

Uniquely identifies each transaction.

If any detail table is being used, set the PARENT_TRANSACTION_ID in the detail interface tables to this value.

PROCESS_PHASE

Optional

NUMBER

Indicates the current stage of the record as it is being processed.

See Process State Information for details.

PROCESS_STATUS

Optional

NUMBER

Indicates the current disposition of the record.

See Process State Information for details.

REQUEST_ID

Optional

NUMBER

Identifies the request.

This is normally left blank and is derived from the KCRT_REQUESTS_S sequence.

CREATION_DATE

Optional

DATE

Indicates the transaction date.

If left blank, the current date is used.

CREATED_USERNAME

Optional

VARCHAR2

Identifies the USERNAME (from KNTA_USERS) for the user performing the transaction.

This is used only if CREATED_BY is left blank.

If both are left blank, the value is set to the user currently running the report.

CREATED_BY

Optional

NUMBER

Identifies the USER_ID (from KNTA_USERS) for the user performing the transaction.

If left blank, the value is derived from CREATED_USERNAME.

LAST_UPDATE_DATE

Optional

DATE

Indicates the transaction date.

If left blank, the current date is used.

LAST_UPDATED_USERNAME

Required

VARCHAR2

Identifies the USERNAME (from KNTA_USERS) for the user performing the transaction.

LAST_UPDATED_BY

Optional

NUMBER

Identifies the USER_ID (from KNTA_USERS) for the user performing the transaction.

This is normally left blank and is derived from LAST_UPDATED_USERNAME.

ENTITY_LAST_UPDATE_DATE

Optional

DATE

Indicates the transaction date.

This is normally left blank and the current date is used.

REQUEST_NUMBER

Optional

VARCHAR2

Identifies the request.

This is normally left blank and is derived from REQUEST_ID.

If a value is entered, it should be unique and should match the value in the REQUEST_ID field.

REQUEST_TYPE_NAME

Required

VARCHAR2

Identifies the request type.

Derived from KCRT_REQUESTS_TYPES.

REQUEST_TYPE_ID

Optional

NUMBER

Identifies the request type.

If left blank, the value is derived from REQUEST_TYPE_NAME.

REQUEST_SUBTYPE_NAME

Required

VARCHAR2

Identifies the request subtype.

If a value is entered, it should be a valid subtype from KCRT_REQUEST_SUB_TYPES.

REQUEST_SUBTYPE_ID

Optional

NUMBER

Identifies the request subtype.

If left blank, the value is derived from REQUEST_SUBTYPE_NAME.

DESCRIPTION

Required

VARCHAR2

Specifies a user-visible description of the request.

RELEASE_DATE

Optional

DATE

Indicates when the request first became active.

For new requests, this should be left blank and the current date is used.

When converting existing requests from a third-party system, enter the initial creation date of the request in the remote system.

STATUS_NAME

Optional

VARCHAR2

Indicates the current status of the request.

This should be a valid status for the given request. This should be a request status for at least one workflow step of the workflow.

If left blank, the new request will get the initial status indicated on the request type definition.

STATUS_ID

Optional

NUMBER

Indicates the current status of the request.

If left blank, the value is derived from STATUS_NAME.

WORKFLOW_NAME

Optional

VARCHAR2

Specifies the workflow that the request should follow.

This is normally left blank and its value is based on the values for request type, department, and application for the request.

WORKFLOW_ID

Optional

NUMBER

Specifies the workflow that the request should follow.

This is normally left blank and the value is derived from WORKFLOW_NAME.

DEPARTMENT_CODE

Optional

VARCHAR2

Specifies the code for the department.

DEPARTMENT_NAME

Required

VARCHAR2

Specifies the name of the department.

This should be a valid MEANING from KNTA_LOOKUPS where LOOKUP_TYPE = 'DEPARTMENT_CODE'.

PRIORITY_CODE

Optional

VARCHAR2

Specifies the user-defined priority for the request.

PRIORITY_NAME

Required

VARCHAR2

Specifies the user-defined priority name for the request.

If entered, this should be a valid MEANING from KNTA_LOOKUPS where LOOKUP_TYPE = 'REQUEST_PRIORITY'.

APPLICATION

Required

VARCHAR2

Indicates the user-defined application for the request.

This should be a valid LOOKUP_CODE from KNTA_LOOKUPS where LOOKUP_TYPE = 'APPLICATION'.

ASSIGNED_TO_USERNAME

Required

VARCHAR2

Specifies the USERNAME (from KNTA_USERS) that should initially be assigned the request.

ASSIGNED_TO_USER_ID

Optional

NUMBER

Specifies the USER_ID (from KNTA_USERS) that should initially be assigned the request.

If left blank, the value is derived from ASSIGNED_TO_USERNAME.

ASSIGNED_TO_GROUP_NAME

Required

VARCHAR2

Specifies the SECURITY_GROUP_ID (from KNTA_SECURITY_GROUPS) that should initially be assigned the request.

ASSIGNED_TO_GROUP_ID

Optional

NUMBER

Specifies the SECURITY_GROUP_ID that should initially be assigned to the request.

This is normally left blank and the value is derived from ASSIGNED_TO_GROUP_NAME.

PROJECT_CODE

Required

VARCHAR2

Indicates the user-defined project for the request.

This should be a valid value from KNTA_LOOKUPS where LOOKUP_TYPE = 'PROJECT'.

CONTACT_FIRST_NAME

Required

VARCHAR2

Specifies the first name of the contact for the request.

This should be a valid value from FIRST_NAME in KCRT_CONTACTS.

If a value is entered, CONTACT_LAST_NAME must also be populated.

CONTACT_LAST_NAME

Required

VARCHAR2

Specifies the last name of the contact for the request.

This should be a valid value from LAST_NAME in KCRT_CONTACTS.

If a value is entered, CONTACT_FIRST_NAME must also be populated.

CONTACT_ID

Optional

NUMBER

Specifies the ID of the contact for the request.

This is derived from the CONTACT_FIRST_NAME and CONTACT_LAST_NAME.

RELEASED_FLAG

Required

VARCHAR2

Indicates whether or not the request should be released after import.

Valid values are:

  • Y

  • N

The default value is N.

USER_DATA_SET_CONTEXT_ID

Obsolete

NUMBER

No longer used.

USER_DATA1

VISIBLE_USER_DATA1

through

USER_DATA20

VISIBLE_USERS_DATA20

Optional

VARCHAR2

Specifies the user-defined fields attached to the user screen.

This is required only if user data is defined.

This information is not validated nor does it have a default value.

PARAMETER_SET_CONTEXT_ID

Required

NUMBER

Sets the context identifier for the detail fields.

Either this or REQUEST_TYPE_NAME must be populated.

NOTES

Required

LONG

Optional. Provides free-form note text that is visible in the Notes tab of the request window.

Carriage returns should be represented as {\n} and is replaced with actual carriage returns when the note is moved into the notes table. This can be helpful when the interface table is populated through SQL*Loader.

SOURCE_TYPE_CODE

Required

VARCHAR2

Specifies the type of external update.

This should be a specific interface or migrator name, left blank, or have a value of INTERFACE_RI.

SOURCE

Required

VARCHAR2

Specifies the source of the information. This information is not validated during an import.

For example, the name of the third-party application or a value of CONVERSION.

WORKFLOW_STEP_ID

Optional

NUMBER

Identifies the workflow step that becomes eligible for user processing.

COMPANY

Required

VARCHAR2

Identifies the name of the company associated with this request.

This should be a valid LOOKUP_CODE from KNTA_LOOKUPS where LOOKUP_TYPE = `COMPANY'.
Table 4-2. KCRT_REQUEST_DETAILS_INT interface table

Column

Usage

Data Type

Description

GROUP_ID

Required

NUMBER

Groups all the records that should be processed at the same time.

Use only one GROUP_ID each time you run a report.

Derived from the KNTA_INTERFACE_GROUPS_S sequence.

TRANSACTION_ID

Required

NUMBER

Uniquely identifies each transaction.

PARENT_TRANSACTION_ID

Required

NUMBER

Provides the transaction ID (from KCRT_REQUESTS_INT) of the parent table being imported.

REQUEST_DETAIL_ID

Optional

NUMBER

Identifies the detail ID of the request (from KCRT_REQUEST_DETAILS).

REQUEST_ID

Optional

NUMBER

Identifies the request.

If left blank, the value is derived from the KCRT_REQUESTS_S sequence.

REQUEST_TYPE_ID

Optional

NUMBER

Identifies the request type.

If left blank, the value is derived from REQUEST_TYPE_NAME.

PARAMETER_SET_CONTEXT_ID

Optional

NUMBER

Sets the context identifier for the detail fields.

If left blank, the value is derived from the REQUEST_TYPE_NAME.

BATCH_NUMBER

Required

NUMBER

Specifies the batch number for the custom fields.

This corresponds to the Storage tab in the field definition window on the request type.

PARAMETER1

VISIBLE_PARAMETER1

through

PARAMETER50

VISIBLE_PARAMETER50

Required

VARCHAR2

Specifies the values for all the custom fields defined in the request.

LOOKUP_TYPE1

VALIDATION_TYPE_CODE1

through

LOOKUP_TYPE50

VALIDATION_TYPE_CODE50

Optional

VARCHAR2

VARCHAR2

Identifies the lookup type for each PARAMETER and the validation type code for each PARAMETER.

This is required only if custom data is defined.
Table 4-3. KCRT_REQ_HEADER_DETAILS_INT interface table

Column

Usage

Data Type

Description

GROUP_ID

Required

NUMBER

Groups all the records that should be processed at the same time.

Use only one GROUP_ID each time you run a report.

Derived from the KNTA_INTERFACE_GROUPS_S sequence.

This value should be the same as the parent's GROUP_ID in KCRT_REQUEST_INT.

TRANSACTION_ID

Required

NUMBER

Uniquely identifies each transaction.

PARENT_TRANSACTION_ID

Required

NUMBER

Provides the transaction ID (from KCRT_REQUESTS_INT) of the parent table being imported.

REQ_HEADER_DETAIL_ID

Optional

NUMBER

Identifies the header detail ID for the request.

If left blank, the value is derived from the KCRT_REQ_HEADER_DETAILS_S sequence.

REQUEST_ID

Optional

NUMBER

Identifies the request.

This is normally left blank and is derived from the KCRT_REQUESTS_S sequence.

REQUEST_TYPE_ID

Optional

NUMBER

Identifies the request type.

This is normally left blank and is derived from REQUEST_TYPE_NAME.

BATCH_NUMBER

Required

NUMBER

Specifies the batch number for the custom fields.

This corresponds to the Storage tab in the field definition window on the request type.

PARAMETER1

VISIBLE_PARAMETER1

through

PARAMETER50

VISIBLE_PARAMETER50

Required

VARCHAR2

Specifies the values for all the custom fields defined in the request.

LOOKUP_TYPE1

VALIDATION_TYPE_CODE1

through

LOOKUP_TYPE50

VALIDATION_TYPE_CODE50

Required

VARCHAR2

VARCHAR2

Identifies the lookup type for each PARAMETER and the validation type code for each PARAMETER.

This is required only if custom data is defined.
Table 4-4. KCRT_TABLE_ENTRIES_INT interface table

Column

Usage

Data Type

Description

GROUP_ID

Required

NUMBER

Groups all the records that should be processed at the same time.

Use only one GROUP_ID each time you run a report.

Derived from the KNTA_INTERFACE_GROUPS_S sequence.

This value should be the same as the parent's GROUP_ID in KCRT_REQUEST_INT.

TRANSACTION_ID

Required

NUMBER

Uniquely identifies each transaction.

PARENT_TRANSACTION_ID

Required

NUMBER

Provides the transaction ID (from KCRT_REQUEST_DETAILS_INT) of the parent table being imported.

PARENT_FIELD_TOKEN

Required

VARCHAR2

Specifies the token.

TABLE_ENTRY_ID

Optional

NUMBER

Identifies the table entry record.

If left blank, the value is derived from the KCRT_TABLE_ENTRIES_S sequence.

REQUEST_ID

Optional

NUMBER

Identifies the request.

This is normally left blank and is derived from the KCRT_REQUESTS_S sequence.

PARAMETER_SET_FIELD_ID

Optional

NUMBER

Specifies the field in the table to which this entry belongs.

SEQ

Required

NUMBER

Provides a user-visible sequence number for the package line.

This must be a unique, positive integer that does not conflict with other records being imported.

PARAMETER_SET_CONTEXT_ID

Optional

NUMBER

Sets the context identifier for the detail fields.

If left blank, the value is derived from the REQUEST_TYPE_NAME.

VISIBLE_PARAMETER1

PARAMETER1

through

VISIBLE_PARAMETER50

PARAMETER50

Required

VARCHAR2

Specifies the user-defined fields attached to the user screen.

This is required only if user data is defined.

LOOKUP_TYPE1

through

LOOKUP_TYPE50

Required

VARCHAR2

Identifies the lookup type for each PARAMETER.

This is required only if user data is defined.

VALIDATION_TYPE_CODE1

through

VALIDATION_TYPE_CODE50

Optional

VARCHAR2

Identifies the validation type code for each PARAMETER.

This is required only if user data is defined.