This chapter describes Source System Management APIs. The provided information includes: PL/SQL record structure and procedure, Java inner class and method, and parameter descriptions and validations.
This chapter covers the following topics:
PL/SQL Package Name: HZ_ORIG_SYSTEM_REF_PUB
Java Class Name: HzOrigSystemRefPub
TYPE ORIG_SYS_REFERENCE_REC_TYPE IS RECORD (
ORIG_SYSTEM_REF_ID NUMBER(15),
ORIG_SYSTEM VARCHAR2(30),
ORIG_SYSTEM_REFERENCE VARCHAR2(255),
OWNER_TABLE_NAME VARCHAR2(30),
OWNER_TABLE_ID NUMBER(15),
STATUS VARCHAR(1),
START_DATE_ACTIVE DATE,
END_DATE_ACTIVE DATE,
REASON_CODE VARCHAR2(30),
OLD_ORIG_SYSTEM_REFERNCE VARCHAR2(255),
CREATED_BY_MODULE VARCHAR2(150),
APPLICATION_ID NUMBER,
ATTRIBUTE_CATEGORY VARCHAR2(30),
ATTRIBUTE1 VARCHAR2(150) ,
ATTRIBUTE2 VARCHAR2(150) ,
ATTRIBUTE3 VARCHAR2(150) ,
ATTRIBUTE4 VARCHAR2(150) ,
ATTRIBUTE5 VARCHAR2(150) ,
ATTRIBUTE6 VARCHAR2(150) ,
ATTRIBUTE7 VARCHAR2(150) ,
ATTRIBUTE8 VARCHAR2(150) ,
ATTRIBUTE9 VARCHAR2(150) ,
ATTRIBUTE10 VARCHAR2(150) ,
ATTRIBUTE11 VARCHAR2(150) ,
ATTRIBUTE12 VARCHAR2(150) ,
ATTRIBUTE13 VARCHAR2(150) ,
ATTRIBUTE14 VARCHAR2(150) ,
ATTRIBUTE15 VARCHAR2(150) ,
ATTRIBUTE16 VARCHAR2(150) ,
ATTRIBUTE17 VARCHAR2(150) ,
ATTRIBUTE18 VARCHAR2(150) ,
ATTRIBUTE19 VARCHAR2(150) ,
ATTRIBUTE20 VARCHAR2(150)
);
public static class OrigSysReferenceRec {
public BigDecimal orig_system_ref_id;
public String orig_system;
public String orig_system_reference;
public String owner_table_name;
public BigDecimal owner_table_id;
public String status;
public String reason_code;
public String old_orig_system_reference;
public java.sql.Timestamp start_date_active;
public java.sql.Timestamp end_date_active;
public String created_by_module;
public BigDecimal application_id;
public String attribute_category;
public String attribute1;
public String attribute2;
public String attribute3;
public String attribute4;
public String attribute5;
public String attribute6;
public String attribute7;
public String attribute8;
public String attribute9;
public String attribute10;
public String attribute11;
public String attribute12;
public String attribute13;
public String attribute14;
public String attribute15;
public String attribute16;
public String attribute17;
public String attribute18;
public String attribute19;
public String attribute20;
public OrigSysReferenceRec()
public OrigSysReferenceRec(boolean __RosettaUseGMISSValues);
You can use this routine to create a mapping between a source system reference and a TCA owner_table_id.
PROCEDURE create_orig_system_reference( p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE, p_orig_sys_reference_rec IN ORIG_SYS_REFERENCE_REC_TYPE, x_return_status OUT NOCOPY VARCHAR2, x_msg_count OUT NOCOPY NUMBER, x_msg_data OUT NOCOPY VARCHAR2, );
public static void createOrigSystemReference(
OracleConnection _connection,
String p_init_msg_list,
OrigSysReferenceRec p_orig_sys_reference_rec,
String [] x_return_status,
BigDecimal [] x_msg_count,
String [] x_msg_data
) throws SQLException;
The following table lists information about the parameters in the Create Source System Reference API. The table includes the parameter names, the type of each parameter, the data type of each parameter, the necessity of the parameter, and other information about the parameter such as validation, defaults, and other comments.
| Parameter Name | Type | Data Type | Required | Validation Default Comment |
|---|---|---|---|---|
| orig_system_ref_id | IN | NUMBER | No | Generated from sequence |
| orig_system | IN | VARCHAR2 | Yes | Validations: Foreign key to HZ_ORIG_SYSTEMS_B.orig_system where status is active. If multiple_flag = N, then the combination of orig_system, orig_system_reference, owner_table_name, and status=A must be unique. If multiple_flag = Y, then the combination of orig_system, orig_system_reference, owner_table_name, owner_table_id, and status=A must be unique. |
| orig_system_reference | IN | VARCHAR2 | Yes | Validations: If multiple_flag = N, then the combination of orig_system, orig_system_reference, owner_table_name, and status=A must be unique. If multiple_flag = Y, then the combination of orig_system, orig_system_reference, owner_table_name, owner_table_id, and status=A must be unique. |
| owner_table_name | IN | VARCHAR2 | Yes | Validations: Valid lookup code under TCA_OWNER_TABLE lookup type. If multiple_flag = N, then the combination of orig_system, orig_system_reference, and owner_table_name must be unique. If multiple_flag = Y, then the combination of orig_system, orig_system_reference, owner_table_name, owner_table_id, and status=A must be unique. |
| owner_table_id | IN | NUMBER | Yes | Validation: The combination of owner_table_name and owner_table_id must be valid. |
| status | IN | VARCHAR | No | Validation: Validated against the MOSR_STATUS lookup type. Default: A Sync up status and start/end_date_active. |
| start_date_active | IN | DATE | No | Validation: Start date cannot be a future date. |
| end_date_active | IN | DATE | No | Validation: End date cannot be a past date. |
| reason_code | IN | VARCHAR2 | Yes | Validation: Validated against the MOSR_REASON lookup type. |
| old_orig_system_refernce | IN | VARCHAR2 | Yes | Validation: The combination of orig_system, old_orig_system_reference, and owner_table_name must exist. |
| created_by _module | IN | VARCHAR2 | Yes | Validation: Mandatory attribute. Validated against AR lookup type HZ_CREATED_BY_MODULE. |
| application_id | IN | NUMBER | No | Comment : Text to indicate application that initiates creation of record. |
| attribute_category | IN | VARCHAR2 | No | |
| attribute1 | IN | VARCHAR2 | No | |
| attribute2 | IN | VARCHAR2 | No | |
| attribute3 | IN | VARCHAR2 | No | |
| attribute4 | IN | VARCHAR2 | No | |
| attribute5 | IN | VARCHAR2 | No | |
| attribute6 | IN | VARCHAR2 | No | |
| attribute7 | IN | VARCHAR2 | No | |
| attribute8 | IN | VARCHAR2 | No | |
| attribute9 | IN | VARCHAR2 | No | |
| attribute10 | IN | VARCHAR2 | No | |
| attribute11 | IN | VARCHAR2 | No | |
| attribute12 | IN | VARCHAR2 | No | |
| attribute13 | IN | VARCHAR2 | No | |
| attribute14 | IN | VARCHAR2 | No | |
| attribute15 | IN | VARCHAR2 | No | |
| attribute16 | IN | VARCHAR2 | No | |
| attribute17 | IN | VARCHAR2 | No | |
| attribute18 | IN | VARCHAR2 | No | |
| attribute19 | IN | VARCHAR2 | No | |
| attribute20 | IN | VARCHAR2 | No |
This routine is used to re-map or update source system references.
PROCEDURE update_orig_system_reference( p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE, p_orig_sys_reference_rec IN ORIG_SYS_REFERENCE_REC_TYPE, p_object_version_number IN OUT NOCOPY NUMBER, x_return_status OUT NOCOPY VARCHAR2, x_msg_count OUT NOCOPY NUMBER, x_msg_data OUT NOCOPY VARCHAR2 );
public static void updateOrigSystemReference(
OracleConnection _connection,
String p_init_msg_list,
OrigSysReferenceRec p_orig_sys_reference_rec,
BigDecimal [] p_object_version_number,
String [] x_return_status,
BigDecimal [] x_msg_count,
String [] x_msg_data
) throws SQLException;
The following table lists information about the parameters in the Update Source System Reference API. The table includes the parameter names, the type of each parameter, the data type of each parameter, the necessity of the parameter, and other information about the parameter such as validation, defaults, and other comments.
| Parameter Name | Type | Data Type | Required | Validation Default Comment |
|---|---|---|---|---|
| orig_system_ref_id | IN | NUMBER | No | Comment: This ID is based on the combination of orig_system, and owner_table_name. |
| orig_system | IN | VARCHAR2 | Yes | Validations: Foreign key to HZ_ORIG_SYSTEMS_B.orig_system where status is active. If the old_orig_system_ref is not passed in the combination of orig_system, old_orig_system,_reference, and owner_table_name must exist. |
| orgi_system_reference | IN | VARCHAR2 | Yes | Validations: The combination of orig_system, orig_system_reference, and owner_table_name must exist in the HZ_ORIG_SYSTEM_REFERENCE table. If the old_orig_system_ref is not passed in the combination of orig_system, old_orig_system_reference, and owner_table_name must exist. |
| owner_table_name | IN | VARCHAR2 | Yes | Validations: Valid lookup code under TCA_OWNER_TABLE lookup type. If the old_orig_system_ref is not passed in the combination of orig_system, old_orig_system,_reference, and owner_table_name must exist. |
| owner_table_id | IN | NUMBER | Yes | Validation: The combination of owner_table_name and owner_table_id must be valid. |
| status | IN | VARCHAR | No | Validation: Validated against the MOSR_STATUS lookup type. Sync up status and start/end_date_active |
| start_date_active | IN | DATE | No | Validation : Start date must not be future date. |
| end_date_active | IN | DATE | No | Validation : End date cannot be a past date. |
| reason_code | IN | VARCHAR2 | Yes | Validation : Validated against the MOSR_STATUS lookup type. |
| old_orig_system_refernce | IN | VARCHAR2 | Yes | Validation : The combination of orig_system, old_orig_system_reference, and owner_table_name must exist. |
| created_by_module | IN | VARCHAR2 | Yes | Validation: Non updateable if value exists, else validated against AR lookup type HZ_CREATED_BY_MODULE. |
| application_id | IN | NUMBER | No | Validation : Cannot be updated if value exists. |
| attribute_category | IN | VARCHAR2 | No | |
| attribute1 | IN | VARCHAR2 | No | |
| attribute2 | IN | VARCHAR2 | No | |
| attribute3 | IN | VARCHAR2 | No | |
| attribute4 | IN | VARCHAR2 | No | |
| attribute5 | IN | VARCHAR2 | No | |
| attribute6 | IN | VARCHAR2 | No | |
| attribute7 | IN | VARCHAR2 | No | |
| attribute8 | IN | VARCHAR2 | No | |
| attribute9 | IN | VARCHAR2 | No | |
| attribute10 | IN | VARCHAR2 | No | |
| attribute11 | IN | VARCHAR2 | No | |
| attribute12 | IN | VARCHAR2 | No | |
| attribute13 | IN | VARCHAR2 | No | |
| attribute14 | IN | VARCHAR2 | No | |
| attribute15 | IN | VARCHAR2 | No | |
| attribute16 | IN | VARCHAR2 | No | |
| attribute17 | IN | VARCHAR2 | No | |
| attribute18 | IN | VARCHAR2 | No | |
| attribute19 | IN | VARCHAR2 | No | |
| attribute20 | IN | VARCHAR2 | No |
This routine is used to re-map owner_table_id from existing owner table id to new owner table id for any system or certain system and to inactivate existing mapping with reason code.
PROCEDURE remap_internal_identifier(
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_old_owner_table_id IN NUMBER,
p_new_owner_table_id IN NUMBER,
p_owner_table_name IN VARCHAR2,
p_orig_system IN VARCHAR2,
p_orig_system_reference IN VARCHAR2,
p_reason_code IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
);
public static void remapInternalIdentifier(
OracleConnection _connection,
String p_init_msg_list,
BigDecimal p_old_owner_table_id,
BigDecimal p_new_owner_table_id,
String p_owner_table_name,
String p_orig_system,
String p_orig_system_reference,
String p_reason_code,
String [] x_return_status,
BigDecimal [] x_msg_count,
String [] x_msg_data
) throws SQLException;
The following table lists information about the parameters in the Remap Internal Identifier API. The table includes the parameter names, the type of each parameter, the data type of each parameter, the necessity of the parameter, and other information about the parameter such as validation, defaults, and other comments.
| Parameter Names | Type | Data Type | Required | Validation Default Comment |
|---|---|---|---|---|
| old_owner_table_id | IN | NUMBER | Yes | Validation : The combination of owner_table_name and owner_table_id must be valid. |
| new_owner_table_id | IN | NUMBER | Yes | Validation : The combination of owner_table_name and owner_table_id must be valid. |
| owner_table_name | IN | VARCHAR2 | Yes | Validation : Validated against the OWNER_TABLE_NAME lookup type. |
| orig_system | IN | VARCHAR2 | No | Validation: Foreign key to HZ_ORIG_SYSTEMS_B.orig_system where status is active. If orig_system and orig_system_reference are passed in, then the combination of orig_system, orig_system_reference, and owner_table_name must exist in the HZ_ORIG_SYSTEM_REFERENCE table. |
| orig_system_reference | IN | VARCHAR2 | No | Validation: If orig_system and orig_system_reference are passed in, then the combination of orig_system, orig_system_reference, and owner_table_name must exist in the HZ_ORIG_SYSTEM_REFERENCE table. |
| reason_code | IN | VARCHAR2 | Yes | Validation : Validated against the MOSR_STATUS lookup type. |