This chapter covers the following topics:
PL/SQL Package Name: HZ_RELATIONSHIP_TYPE_V2PUB
Java Class Name: HzRelationshipTypeV2Pub
TYPE relationship_type_rec_type IS RECORD( relationship_type_id NUMBER, relationship_type VARCHAR2(30), forward_rel_code VARCHAR2(30), backward_rel_code VARCHAR2(30), direction_code VARCHAR2(30), hierarchical_flag VARCHAR2(1), create_party_flag VARCHAR2(1), allow_relate_to_self_flag VARCHAR2(1), allow_circular_relationships VARCHAR2(1), subject_type VARCHAR2(30), object_type VARCHAR2(30), status VARCHAR2(1), created_by_module VARCHAR2(150), application_id NUMBER multiple_parent_allowed VARCHAR2(1), incl_unrelated_entities VARCHAR2(1) forward_role VARCHAR2(30) backward_role VARCHAR2(30) )
public static class RelationshipTypeRec { public BigDecimal relationship_type_id; public String relationship_type; public String forward_rel_code; public String backward_rel_code; public String direction_code; public String hierarchical_flag; public String create_party_flag; public String allow_relate_to_self_flag; public String allow_circular_relationships; public String subject_type; public String object_type; public String status; public String created_by_module; public BigDecimal application_id; public String multiple_parent_allowed; public String incl_unrelated_entities; public String forward_role public String backward_role public RelationshipTypeRec(); public RelationshipTypeRec(boolean __RosettaUseGMISSValues); }
This routine is used to create a Relationship Type. The API creates a record in the HZ_RELATIONSHIP_TYPES table. The relationship type defines the possible relationships that can be created between different types of parties or other entities. The API internally creates an additional record when forward relationship code and backward relationship code are different indicating the relationship can be created in two ways.
PROCEDURE create_relationship_type ( p_init_msg_list IN VARCHAR2:= FND_API.G_FALSE, p_relationship_type_rec IN RELATIONSHIP_TYPE_REC_TYPE, x_relationship_type_id OUT NUMBER, x_return_status OUT VARCHAR2, x_msg_count OUT NUMBER, x_msg_data OUT VARCHAR2, )
public static void createRelationshipType( OracleConnection_connection, String p_init_msg_list, RelationshipTypeRec p_relationship_type_rec, BigDecimal [ ] x_relationship_type_id, 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 Relationship Type 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 |
---|---|---|---|---|
relationship_type_id | IN | NUMBER | No | Validation: Unique if passed in, else generated from sequence |
relationship_type | IN | VARCHAR2 | Yes | Validation: Mandatory attribute |
forward_rel_code | IN | VARCHAR2 | Yes | Validation:
|
backward_rel_code | IN | VARCHAR2 | Yes | Validation:
|
direction_code | IN | VARCHAR2 | Yes | Validation:
|
hierarchical_flag | IN | VARCHAR2 | No | Validation: Validated against AR lookup type YES/NO.
Default : N Comment : Indicates whether the relationship type is hierarchical. |
create_party_flag | IN | VARCHAR2 | No | Validation: Validated against AR lookup type YES/NO Default: N Comment: Indicates whether a denormalized party will be created for a relationship having this relationship type |
allow_relate_to_self_flag | IN | VARCHAR2 | No | Validation: Validated against AR lookup type YES/NO Default: N |
allow_circular_relationships | IN | VARCHAR2 | No | Validation: Validated against AR lookup type YES/NO
Default: Y 7 |
subject_type | IN | VARCHAR2 | Yes | Validation: Mandatory attribute Foreign key to fnd_object_instance_sets.instance_set_name |
object_type | IN | VARCHAR2 | Yes | Validation: Mandatory attribute Foreign key to fnd_object_instance_sets.instance_set_name |
status | IN | VARCHAR2 | No | Validation: Validated against AR lookup type CODE_STATUS |
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 module from which creation of record is initiated |
multiple_parent_allowed | IN | VARCHAR2 | No | Validation : Validated against AR lookup type YES/NO
Comment : Indicates whether a child can have multiple parents. This is applicable when hierarchical_flag is Y. Default : Y |
incl_unrelated_entities | IN | VARCHAR2 | No | Validation :Validated against AR lookup type YES/NO Comment : Indicates whether entities not having a relationship will be included in a hierarchy or not. This is applicable when hierarchical_flag is Y. Default : N |
forward_role | IN | VARCHAR2 | No | Validation:
Default : Defaulted to 'USER_ROLE_'||to_char(relationship_type_id) Comment : This describes the role a subject party plays in a relationship. |
backward_role | IN | VARCHAR2 | No | Validation:
Comment: This describes the role an object party plays in a relationship. Default: Defaulted to 'USER_ROLE_'||to_char(relationship_type_id) |
x_relationship_type_id | OUT | NUMBER | No | Comment: Return relationship_type_id of the relationship type record created |
The combination of relationship type, forward relationship code, backward relationship code, subject type, and object_type should be unique.
The combination of a relationship type, relationship phrase (code), subject_type, and object_type results in a unique relationship phrase (code) in the reverse direction.
The combination of a forward relationship code, subject type, and object type identify a unique backward relationship code. Therefore, another record with same combination of forward relationship code, subject type, and object type has the same backward relationship code.
For example, if A and B have the same relationship type, the forward relationship code of A is the same as backward relationship code of B, the subject type of A is the same as the object type of B and the object type of A is the same as the subject type of B, then the backward relationship code of A must be the same as the forward relationship code of B.
The combination of a backward relationship code, subject type, and object type in a relationship_type similarly identifies a unique forward relationship code.
For example, if A and B have the same relationship type, the backward relationship code of A is the same as forward relationship code of B, and the subject type of A is the same as the object type of B and the object type of A is the same as the subject type of B, then the forward relationship code of A must be the same as that of the backward relationship code of B.
The direction code will be used to determine if a backward relationship type should be created. If the direction code is P or C, we will create a second relationship type. If the forward relationship code is the same as the backward relationship code, the direction code should be N - Non directional.
If a relationship type is created with the hierarchical_flag set to Y, the API does not allow a circular relationship with that relationship type.
If a relationship type is created with the hierarchical_flag set to Y, that is generally called hierarchical relationship type. If you create or update relationships of that hierarchical relationship type, relationship information is denormalized to the HZ_HIERARCHY_NODES table with level, date effectivity information, and so on. You can query that table at any time to view the hierarchy information.
If forward_rel_code and backward_rel_code are different, then the forward_role and the backward_role should also be different.
If forward_rel_code and backward_rel_code are the same, then the forward_role and the backward_role should also be same.
If forward_rel_code and backward_rel_code are the same, then the subject_type and object_type should also be the same.
When forward_role or backward_role is not passed, it defaults to 'USER_ROLE_ '||to_char(relationship_type_id) and no lookup is created through the API for this defaulted role.
A new lookup type, HZ_RELATIONSHIP_TYPE, is introduced for relationship types. In the API, no validation is done for a relationship_type against the lookup because of backward compatibility. Before creating a relationship type, a lookup for that relationship type must be created.
This routine is used to update a Relationship Type. The API updates a record in the HZ_RELATIONSHIP_TYPES table.
PROCEDURE update_relationship_type ( p_init_msg_list IN VARCHAR2:= FND_API.G_FALSE, p_relationship_type_rec IN RELATIONSHIP_TYPE_REC_TYPE, p_object_version_number IN OUT NUMBER, x_return_status OUT VARCHAR2, x_msg_count OUT NUMBER, x_msg_data OUT VARCHAR2 )
public static void updateRelationshipType( OracleConnection_connection, String p_init_msg_list, RelationshipTypeRec p_relationship_type_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 Relationship Type 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 |
---|---|---|---|---|
relationship_type_id | IN | NUMBER | Yes | Validation: valid relationship_type_id should be passed in Comment: Pass the relationship_type_id from hz_relationship_types record |
relationship_type | IN | VARCHAR2 | No | Validation: Not updateable |
forward_rel_code | IN | VARCHAR2 | No | Validation: Not updateable |
backward_rel_code | IN | VARCHAR2 | No | Validation: Not updateable |
direction_code | IN | VARCHAR2 | No | Validation: Not updateable |
hierarchical_flag | IN | VARCHAR2 | No | Validation: Not updateable |
create_party_flag | IN | VARCHAR2 | No | Validation:
|
allow_relate_to_self_flag | IN | VARCHAR2 | No | Validation: Not updateable |
allow_circular_relationships | IN | VARCHAR2 | No | Validation: Not updateable |
subject_type | IN | VARCHAR2 | No | Validation: Not updateable |
object_type | IN | VARCHAR2 | No | Validation: Not updateable |
status | IN | VARCHAR2 | No | Validation:
|
created_by_module | IN | VARCHAR2 | No | Validation: Non updateable if value exists, else validated against AR lookup type HZ_CREATED_BY_MODULE. |
application_id | IN | NUMBER | No | Validation: Not updateable if value exists |
multiple_parent_allowed | IN | VARCHAR2 | No | Validation : Non updateable |
incl_unrelated_entities | IN | VARCHAR2 | No | Validation :Validated against AR lookup type YES/NO |
forward_role | IN | VARCHAR2 | No | Validation : Non updateable. Non nullable |
backward_role | IN | VARCHAR2 | No | Validation : Non updateable. Non nullable |
p_object_version_number | IN OUT |
NUMBER | Yes | Validation:
Comment:
|
The combination of relationship type, forward relationship code, backward relationship code, subject type, and object_type should be unique.
The combination of a relationship type, relationship phrase (code), subject_type and object_type results in a unique relationship phrase (code) in the reverse direction.
The direction code will be used to determine if a backward relationship type should be created. If the direction code is 'P' or 'C', we will create a second relationship type. If the forward relationship code is the same as the backward relationship code, the direction code should be 'N' - Non directional.
PL/SQL Package Name: HZ_RELATIONSHIP_V2PUB
Java Class Name: HzRelationshipV2Pub
PL/SQL Constant:
G_MISS_CONTENT_ CONSTANT VARCHAR2(30) := USER_ENTERED;
TYPE relationship_rec_type IS RECORD( relationship_id NUMBER, subject_id NUMBER, subject_type VARCHAR2(30), subject_table_name VARCHAR2(30), object_id NUMBER, object_type VARCHAR2(30), object_table_name VARCHAR2(30), relationship_code VARCHAR2(30), relationship_type VARCHAR2(30), comments VARCHAR2(240), start_date DATE, end_date DATE, status VARCHAR2(1), content_source_type VARCHAR2(30), := G_MISS_CONTENT_SOURCE_TYPE, 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), created_by_module VARCHAR2(150), application_id NUMBER, party_rec HZ_PARTY_V2PUB.PARTY_REC_TYPE:= HZ_PARTY_V2PUB.G_MISS_PARTY_REC additional_information1 VARCHAR2(150), additional_information2 VARCHAR2(150), additional_information3 VARCHAR2(150), additional_information4 VARCHAR2(150), additional_information5 VARCHAR2(150), additional_information6 VARCHAR2(150), additional_information7 VARCHAR2(150), additional_information8 VARCHAR2(150), additional_information9 VARCHAR2(150), additional_information10 VARCHAR2(150), additional_information11 VARCHAR2(150), additional_information12 VARCHAR2(150), additional_information13 VARCHAR2(150), additional_information14 VARCHAR2(150), additional_information15 VARCHAR2(150), additional_information16 VARCHAR2(150), additional_information17 VARCHAR2(150), additional_information18 VARCHAR2(150), additional_information19 VARCHAR2(150), additional_information20 VARCHAR2(150), additional_information21 VARCHAR2(150), additional_information22 VARCHAR2(150), additional_information23 VARCHAR2(150), additional_information24 VARCHAR2(150), additional_information25 VARCHAR2(150), additional_information26 VARCHAR2(150), additional_information27 VARCHAR2(150), additional_information28 VARCHAR2(150), additional_information29 VARCHAR2(150), additional_information30 VARCHAR2(150), percentage_ownership NUMBER actual_content_source VARCHAR2(30) )
public static class RelationshipRec { public BigDecimal relationship_id; public BigDecimal subject_id; public String subject_type; public String subject_table_name; public BigDecimal object_id; public String object_type; public String object_table_name; public String relationship_code; public String relationship_type; public String comments; public java.sql.Timestamp start_date; public java.sql.Timestamp end_date; public String status; public String content_source_type; 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 String created_by_module; public BigDecimal application_id; public party_rec; HzPartyV2Pub.PartyRec public String additional_information1; public String additional_information2; public String additional_information3; public String additional_information4; public String additional_information5; public String additional_information6; public String additional_information7; public String additional_information8; public String additional_information9; public String additional_information10; public String additional_information11; public String additional_information12; public String additional_information13; public String additional_information14; public String additional_information15; public String additional_information16; public String additional_information17; public String additional_information18; public String additional_information19; public String additional_information20; public String additional_information21; public String additional_information22; public String additional_information23; public String additional_information24; public String additional_information25; public String additional_information26; public String additional_information27; public String additional_information28; public String additional_information29; public String additional_information30; public BigDecimal percentage_ownership; public String actual_content_source; public RelationshipRec(); public RelationshipRec(boolean __RosettaUseGMISSValues); }
This routine is used to create a Relationship between two parties or other entities. The API creates a record in the HZ_RELATIONSHIPS table. This defines the relationship that exists between Parties of type PERSON, ORGANIZATION, and other entities that are defined in FND_OBJECT_INSTANCE_SETS. Each relationship can be viewed from either ways. So an additional relationship record is created to store the reverse relationship. The relationship code, relationship type, subject type and object type must be a valid combination already defined in the HZ_RELATIONSHIP_TYPES table. The two relationship records have the same relationship_id, they are distinguishable by the directional_flag column.
If a hierarchical relationship type (hierarchical_flag = Y) is used to create a relationship, the relationship information is denormalized to the HZ_HIERARCHY_NODES table with level, effective date, and so on. The API ensures that no circular relationship is created, so that all of the relationships using that relationship type are hierarchical.
There are two signatures for this API. One accepts p_create_org_contact as a parameter, the other does not. In the second case, p_create_org_contact is defaulted to Y, and the other overloaded procedure is called.
Important: Do not use the Create Relationship API to create D&B hierarchy relationships. See: D&B Hierarchy, Oracle Trading Community Architecture User Guide.
The p_create_org_contact parameter is not in the overloaded procedure.
PROCEDURE create_relationship ( p_init_msg_list IN VARCHAR2:= FND_API.G_FALSE, p_relationship_rec IN RELATIONSHIP_REC_TYPE, x_relationship_id OUT NUMBER, x_party_id OUT NUMBER, x_party_number OUT VARCHAR2, x_return_status OUT VARCHAR2, x_msg_count OUT NUMBER, x_msg_data OUT VARCHAR2, p_create_org_create IN VARCHAR:=Y p_create_org_contact IN VARCHAR)
The p_create_org_contact parameter is not in the overloaded procedure.
public static void createRelationship( OracleConnection_connection, String p_init_msg_list, RelationshipRec p_relationship_rec, BigDecimal [ ] x_relationship_id, BigDecimal [ ] x_party_id, String [ ] x_party_number, String [ ] x_return_status, BigDecimal [ ] x_msg_count, String [ ] x_msg_data String p_create_org_contact ) throws SQLException;
The following tables list information about the parameters in the Create Relationship API. The tables include 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 |
---|---|---|---|---|
relationship_id | IN | NUMBER | No | Validation: Unique if passed in, else generated by sequence |
subject_id | IN | NUMBER | Yes | Validation:
|
subject_type | IN | VARCHAR2 | Yes | Validation:
|
subject_table_name | IN | VARCHAR2 | Yes | Validation:
|
object_id | IN | NUMBER | Yes | Validation:
|
object_type | IN | VARCHAR2 | Yes | Validation:
|
object_table_name | IN | VARCHAR2 | Yes | Validation:
|
relationship_code | IN | VARCHAR2 | Yes | Validation:
|
relationship_type | IN | VARCHAR2 | Yes | Validation:
|
comments | IN | VARCHAR2 | No | |
start_date | IN | DATE | Yes | Validation: Must be less than end_date if end_date is passed Default: sysdate |
end_date | IN | DATE | No | Default: 31-DEC-4712 Validation: Must be greater than start_date |
status | IN | VARCHAR2 | No | Validation: Validated against AR lookup type REGISTRY_STATUS Default: A |
content_source_type | IN | VARCHAR2 | No | Comment: This parameter is no longer used. Use actual_content_source. Validation: Foreign key to HZ_ORIG_SYSTEMS_B.orig_system with sst_flag value of Y. Default: USER_ENTERED |
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 | |
additional_information1 | IN | VARCHAR2 | No | |
additional_information2 | IN | VARCHAR2 | No | |
additional_information3 | IN | VARCHAR2 | No | |
additional_information4 | IN | VARCHAR2 | No | |
additional_information5 | IN | VARCHAR2 | No | |
additional_information6 | IN | VARCHAR2 | No | |
additional_information7 | IN | VARCHAR2 | No | |
additional_information8 | IN | VARCHAR2 | No | |
additional_information9 | IN | VARCHAR2 | No | |
additional_information10 | IN | VARCHAR2 | No | |
additional_information11 | IN | VARCHAR2 | No | |
additional_information12 | IN | VARCHAR2 | No | |
additional_information13 | IN | VARCHAR2 | No | |
additional_information14 | IN | VARCHAR2 | No | |
additional_information15 | IN | VARCHAR2 | No | |
additional_information16 | IN | VARCHAR2 | No | |
additional_information17 | IN | VARCHAR2 | No | |
additional_information18 | IN | VARCHAR2 | No | |
additional_information19 | IN | VARCHAR2 | No | |
additional_information20 | IN | VARCHAR2 | No | |
additional_information21 | IN | VARCHAR2 | No | |
additional_information22 | IN | VARCHAR2 | No | |
additional_information23 | IN | VARCHAR2 | No | |
additional_information24 | IN | VARCHAR2 | No | |
additional_information25 | IN | VARCHAR2 | No | |
additional_information26 | IN | VARCHAR2 | No | |
additional_information27 | IN | VARCHAR2 | No | |
additional_information28 | IN | VARCHAR2 | No | |
additional_information29 | IN | VARCHAR2 | No | |
additional_information30 | IN | VARCHAR2 | No | |
percentage_ownership | IN | NUMBER | No | |
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 from which creation of record is initiated |
actual_content_source | IN | VARCHAR2 | No | Validation : Foreign key to HZ_ORIG_SYSTEMS_B.orig_system with sst_flag value of Y. Default : USER_ENTERED |
Parameter | Type | Data Type | Required | Validation, Default, Comment |
---|---|---|---|---|
party_id | IN | NUMBER | No | Validation: Unique if passed in, else generated by sequence |
party_number | IN | VARCHAR2 | Yes/No | Validation: Mandatory if HZ_GENERATE_PARTY_NUMBER=N, else generated by sequence |
validated_flag | IN | VARCHAR2 | No | Default: N |
orig_system_reference | IN | VARCHAR2 | No | Default: party_id |
orig_system | IN | VARCHAR2 | No | Validation: Foreign key to HZ_ORIG_SYSTEMS_B.orig_system. |
status | IN | VARCHAR2 | No | Validation: Validated against AR lookup type REGISTRY_STATUS Default: A |
category_code | IN | VARCHAR2 | No | Validation: Validated against lookup type CUSTOMER_CATEGORY |
salutation | IN | VARCHAR2 | No | |
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 | |
attribute21 | IN | VARCHAR2 | No | |
attribute22 | IN | VARCHAR2 | No | |
attribute23 | IN | VARCHAR2 | No | |
attribute24 | IN | VARCHAR2 | No | |
x_relationship_id | OUT | NUMBER | No | Comment: Return relationship_id of the relationship record created |
x_party_id | OUT | NUMBER | No | Comment: Return party_id for the relationship created |
x_party_number | OUT | NUMBER | No | Comment: Return party number of the party created |
p_create_org_contact | IN | VARCHAR2 | No | Comment: This parameter is for use only by TCA development. This parameter is not in the overloaded procedure. |
When you call the create relationship procedure, two new records will be created in HZ_RELATIONSHIPS table and one record will be created in the HZ_ORG_CONTACTS table.
You are required to specify a subject id, an object id, a subject type, an object type, a subject table name (where the subject belongs to e.g. 'HZ_PARTIES' for subject type of 'PERSON', 'ORGANIZATION'), an object table name, a relationship type, a relationship code, and start date to create a relationship.
The relationship code must be either a forward or backward relationship code defined in active records in the HZ_RELATIONSHIP_TYPES table for your relationship type.
The end date will be default to '31-DEC-4712' if not specified. The end date must be greater than the start date.
For a given subject_id, object_id, relationship_code, there can be no overlap of the start date and the end date.
The subject type and the object type will be used to do the foreign key check for the subject id and the object id column. The subject id should be a valid object instance defined in the object instance set which is specified as the subject type. The object id should be a valid object instance defined in the object instance set which is specified as the object type.
The subject id and the object id cannot be the same value unless the self related flag of the relationship type is set to yes.
It will check the “denormalized to party” flag in the HZ_RELATIONSHIP_TYPES table to see if a denormalized party is necessary. If the flag is set to yes, a party will be created for the relationship. Only the relationship with both subject_table_name and object_table_name as 'HZ_PARTIES' can be denormalized to HZ_PARTIES table.
A record will be created in the HZ_RELATIONSHIPS table with the relationship code passed as input parameter.
It will find the 2nd relationship code from the HZ_RELATIONSHIP_TYPES table, and use it to create a second relationship. The 2nd relationship will have the same relationship_id and party_id as the first relationship. The requested relationship will be created with directional_code value F (meaning forward) and the reciprocal relationship will be created with directional_code value B (meaning backward).
If a hierarchical relationship type is used, the API ensures no circular relationship is created during the creation of a relationship. Usually the API also ensures that a child has only one parent at a time. However, if the attribute multiple_parents_allowed = Y, then you can create multiple parents for a child. This is a very special case and you must cautiously use this attribute setting.
If a hierarchical relationship type is used, API denormalizes the relationship information into HZ_HIERARCHY_NODES table. This denormalized table contains relationship information along with level, date effectivity of relationships etc. Simple query can be written against that table to query hierarchy information.
This routine is used to update a Relationship. Because there are two records for each relationship (forward and backward), the API updates two records in the HZ_RELATIONSHIPS table. Additionally you can update the denormalized party record for the relationship (if it is present) by passing party's id and party's object version number.
PROCEDURE update_relationship ( p_init_msg_list IN VARCHAR2:= FND_API.G_FALSE, p_relationship_rec IN RELATIONSHIP_REC_TYPE, p_object_version_number IN OUT NUMBER, p_party_object_version_number IN OUT NUMBER, x_return_status OUT VARCHAR2, x_msg_count OUT NUMBER, x_msg_data OUT VARCHAR2 )
public static void updateRelationship( OracleConnection_connection, String p_init_msg_list, RelationshipRec p_relationship_rec, BigDecimal [ ] p_object_version_number, BigDecimal [ ] p_party_object_version_number, String [ ] x_return_status, BigDecimal [ ] x_msg_count, String [ ] x_msg_data ) throws SQLException;
The following tables list information about the parameters in the Update Relationship API. The tables include 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 |
---|---|---|---|---|
relationship_id | IN | NUMBER | Yes | Validation: Valid relationship_id should be passed in Comment: Pass the relationship_id from the hz_relationships record |
subject_id | IN | NUMBER | No | Validation: Not updateable |
subject_type | IN | VARCHAR2 | No | Validation: Not updateable |
subject_table_name | IN | VARCHAR2 | No | Validation: Not updateable |
object_id | IN | NUMBER | No | Validation: Not updateable |
object_type | IN | VARCHAR2 | No | Validation: Not updateable |
object_table_name | IN | VARCHAR2 | No | Validation: Not updateable |
relationship_code | IN | VARCHAR2 | No | Validation: Not updateable |
relationship_type | IN | VARCHAR2 | No | Validation: Not updateable |
comments | IN | VARCHAR2 | No | |
start_date | IN | DATE | No | Validation: Cannot be set to null during update Cannot be greater than end_date |
end_date | IN | DATE | No | Validation: Must be greater than start date |
status | IN | VARCHAR2 | No | Validation:
|
content_source_type | IN | VARCHAR2 | No | Comment: This parameter is no longer used. Use actual_content_source. Validation: Cannot be updated. |
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 | |
additional_information1 | IN | VARCHAR2 | No | |
additional_information2 | IN | VARCHAR2 | No | |
additional_information3 | IN | VARCHAR2 | No | |
additional_information4 | IN | VARCHAR2 | No | |
additional_information5 | IN | VARCHAR2 | No | |
additional_information6 | IN | VARCHAR2 | No | |
additional_information7 | IN | VARCHAR2 | No | |
additional_information8 | IN | VARCHAR2 | No | |
additional_information9 | IN | VARCHAR2 | No | |
additional_information10 | IN | VARCHAR2 | No | |
additional_information11 | IN | VARCHAR2 | No | |
additional_information12 | IN | VARCHAR2 | No | |
additional_information13 | IN | VARCHAR2 | No | |
additional_information14 | IN | VARCHAR2 | No | |
additional_information15 | IN | VARCHAR2 | No | |
additional_information16 | IN | VARCHAR2 | No | |
additional_information17 | IN | VARCHAR2 | No | |
additional_information18 | IN | VARCHAR2 | No | |
additional_information19 | IN | VARCHAR2 | No | |
additional_information20 | IN | VARCHAR2 | No | |
additional_information21 | IN | VARCHAR2 | No | |
additional_information22 | IN | VARCHAR2 | No | |
additional_information23 | IN | VARCHAR2 | No | |
additional_information24 | IN | VARCHAR2 | No | |
additional_information25 | IN | VARCHAR2 | No | |
additional_information26 | IN | VARCHAR2 | No | |
additional_information27 | IN | VARCHAR2 | No | |
additional_information28 | IN | VARCHAR2 | No | |
additional_information29 | IN | VARCHAR2 | No | |
additional_information30 | IN | VARCHAR2 | No | |
percentage_ownership | IN | NUMBER | No | |
created_by_module | IN | VARCHAR2 | No | Validation: Non updateable if value exists, else validated against AR lookup type HZ_CREATED_BY_MODULE. |
application_id | IN | NUMBER | No | Validation: Not updateable if value exists |
actual_content_source | IN | VARCHAR2 | No | Validation: Value will not be updated in the database. |
Parameter | Type | Data Type | Required | Validation, Default, Comment |
---|---|---|---|---|
party_id | IN | NUMBER | No | Validation: Valid party_id should be passed in to update party sequence Comment:
|
party_number | IN | VARCHAR2 | No | Validation: Not updateable |
validated_flag | IN | VARCHAR2 | No | Validation: Not updateable |
orig_system_reference | IN | VARCHAR2 | No | Validation: Not updateable |
orig_system | IN | VARCHAR2 | No | Validation: Foreign key to HZ_ORIG_SYSTEMS_B.orig_system. |
status | IN | VARCHAR2 | No | Validation:
|
category_code | IN | VARCHAR2 | No | Validation: Validated against lookup type CUSTOMER_CATEGORY |
salutation | IN | VARCHAR2 | No | |
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 | |
attribute21 | IN | VARCHAR2 | No | |
attribute22 | IN | VARCHAR2 | No | |
attribute23 | IN | VARCHAR2 | No | |
attribute24 | IN | VARCHAR2 | No | |
p_object_version_number | IN OUT |
NUMBER | Yes | Validation:
Comment:
|
p_party_object_version_number | IN OUT |
NUMBER | Yes/No | Validation:
Comment:
|
You are required to specify a subject id, an object id, a subject type, an object type, a subject table name (where the subject belongs to e.g. 'HZ_PARTIES' for subject type of 'PERSON', 'ORGANIZATION'), an object table name, a relationship type, a relationship code, and start date to create a relationship.
The relationship code must be either a forward or backward relationship code defined in active records in the HZ_RELATIONSHIP_TYPES table for your relationship type.
The end date will be default to '31-DEC-4712' if not specified. The end date must be greater than the start date.
For a given subject_id, object_id, relationship_code, there can be no overlap of the start_date and the end_date.
The subject type and the object type will be used to do the foreign key check for the subject id and the object id column. The subject id should be a valid object instance defined in the object instance set which is specified as the subject type. The object id should be a valid object instance defined in the object instance set which is specified as the object type.
The subject id and the object id cannot be the same value unless the self related flag of the relationship type is set to yes.
It will check the “denormalized to party” flag in the HZ_RELATIONSHIP_TYPES table to see if a denormalized party is necessary. If the flag is set to yes, a party will be created for the relationship. Only the relationship with both subject_table_name and object_table_name as 'HZ_PARTIES' can be denormalized to HZ_PARTIES table.
A record will be created in the HZ_RELATIONSHIPS table with the relationship code passed as input parameter.
It will find the 2nd relationship code from the HZ_RELATIONSHIP_TYPES table, and use it to create a second relationship. The 2nd relationship will have the same relationship_id and party_id as the first relationship. The requested relationship will be created with directional_code value 'F' (meaning forward) and the reciprocal relationship will be created with directional_code value 'B' (meaning backward).
Information in the Create Relationship API section about hierarchical types is also valid for the Update Relationship API section.
PL/SQL Package Name: HZ_HIERARCHY_V2PUB
TYPE related_nodes_list_rec IS RECORD ( related_node_id NUMBER(15), related_node_table_name VARCHAR2(30), related_node_object_type VARCHAR2(30), level_number NUMBER(15), top_parent_flag VARCHAR2(1), leaf_child_flag VARCHAR2(1), effective_start_date DATE, effective_end_date DATE, relationship_id NUMBER(15), ) TYPE related_nodes_list_type IS TABLE OF related_nodes_list_rec INDEX BY BINARY_INTEGER;
public static class RelatedNodesListRec { public BigDecimal related_node_id; public String related_node_table_name; public String related_node_object_type; public BigDecimal level_number; public String top_parent_flag; public String leaf_child_flag; public java.sql.Timestamp effective_start_date; public java.sql.Timestamp effective_end_date; public BigDecimal relationship_id; public RelatedNodesListRec() {); public RelatedNodesListRec(boolean __RosettaUseGMISSValues) )
The following tables list information about the parameters in the Hierarchy Retrieval API. The tables include 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 |
---|---|---|---|---|
p_init_msg_list | IN | VARCHAR2 | No | Comment : Indicates whether message stack should initialized Default : FND_API.G_FALSE |
p_hierarchy_type | IN | VARCHAR2 | Yes | Validation : Must be a hierarchical relationship type from hz_relationship_types table |
p_parent_id | IN | NUMBER | Yes | |
p_parent_table_name | IN | VARCHAR2 | No | Default : 'HZ_PARTIES' |
p_parent_object_type | IN | VARCHAR2 | No | Default : 'ORGANIZATION' |
p_child_id | IN | NUMBER | Yes | |
p_child_table_name | IN | VARCHAR2 | No | Default : 'HZ_PARTIES' |
p_child_object_type | IN | VARCHAR2 | No | Default : 'ORGANIZATION' |
p_effective_date | IN | DATE | No | Default : SYSDATE |
p_include_node | IN | VARCHAR2 | No | Comment : Indicates whether the parent/child itself should be included in the result set Default : Y |
p_no_of_records | IN | NUMBER | No | Comment : Number of records to be returned. Maximum is 100 .Default : 100 |
x_result | OUT | VARCHAR2 | Yes | Comment : Y/N |
x_return_status | OUT | VARCHAR2 | Yes | Comment : API return status |
x_msg_count | OUT | NUMBER | Yes | Comment : Number of messages in stack |
x_msg_data | OUT | VARCHAR2 | Yes | Comment : Message text if x_msg_count = 1 |
Parameter | Type | Data Type | Required | Validation, Default, Comment |
---|---|---|---|---|
related_node_id | NUMBER | Id of the related entity | ||
related_node_table_name | VARCHAR2 | Table name for the related entity | ||
related_node_object_type | VARCHAR2 | Object type of the related entity | ||
level_number | NUMBER | Level at which the entity is related to | ||
top_parent_flag | VARCHAR2 | Indicates whether related entity is top parent | ||
leaf_child_flag | VARCHAR2 | Indicates whether related entity is a leaf node | ||
effective_start_date | DATE | Start date of the relationship | ||
effective_end_date | DATE | End date of the relationship | ||
relationship_id | NUMBER | Relationship Id if it is a direct link |
Use this routine to check whether an entity is top parent, or root, in a hierarchy or not. Please pass a valid hierarchy relationship type and any necessary parent information. The API will return Y or N.
PROCEDURE is_top_parent( p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE, p_hierarchy_type IN VARCHAR2, p_parent_id IN NUMBER, p_parent_table_name IN VARCHAR2 := 'HZ_PARTIES', p_parent_object_type IN VARCHAR2 := 'ORGANIZATION', p_effective_date IN DATE := SYSDATE, x_result OUT VARCHAR2, x_return_status OUT VARCHAR2, x_msg_count OUT NUMBER, x_msg_data OUT VARCHAR2 )
public static void isTopParent( OracleConnection _connection, String p_init_msg_list, String p_hierarchy_type, BigDecimal p_parent_id, String p_parent_table_name, String p_parent_object_type, java.sql.Timestamp p_effective_date, String [] x_result, String [] x_return_status, BigDecimal [] x_msg_count, String [] x_msg_data ) throws SQLException;
Use this routine to check whether two entities have a parent-child relationship in a hierarchy. Pass a valid hierarchy relationship type and any necessary parent and child information. The API will return Y or N.
PROCEDURE check_parent_child( p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE, p_hierarchy_type IN VARCHAR2, p_parent_id IN NUMBER, p_parent_table_name IN VARCHAR2 := 'HZ_PARTIES', p_parent_object_type IN VARCHAR2 := 'ORGANIZATION', p_child_id IN NUMBER, p_child_table_name IN VARCHAR2 := 'HZ_PARTIES', p_child_object_type IN VARCHAR2 := 'ORGANIZATION', p_effective_date IN DATE := SYSDATE, x_result OUT VARCHAR2, x_level_number OUT NUMBER, x_return_status OUT VARCHAR2, x_msg_count OUT NUMBER, x_msg_data OUT VARCHAR2 )
public static void checkParentChild( OracleConnection _connection, String p_init_msg_list, String p_hierarchy_type, BigDecimal p_parent_id, String p_parent_table_name, String p_parent_object_type, BigDecimal p_child_id, String p_child_table_name, String p_child_object_type, java.sql.Timestamp p_effective_date, String [] x_result, BigDecimal [] x_level_number, String [] x_return_status, BigDecimal [] x_msg_count, String [] x_msg_data ) throws SQLException;
Use this routine to retrieve the parent nodes of a child in a hierarchy. Pass a valid hierarchy relationship type and any necessary parent type and child node information. The API returns a set of parent nodes in that hierarchy.
PROCEDURE get_parent_nodes( p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE, p_hierarchy_type IN VARCHAR2, p_child_id IN NUMBER, p_child_table_name IN VARCHAR2, p_child_object_type IN VARCHAR2, p_parent_table_name IN VARCHAR2, p_parent_object_type IN VARCHAR2, p_include_node IN VARCHAR2 := 'Y', p_effective_date IN DATE := SYSDATE, p_no_of_records IN NUMBER := 100, x_related_nodes_list OUT NOCOPY RELATED_NODES_LIST_TYPE, x_return_status OUT VARCHAR2, x_msg_count OUT NUMBER, x_msg_data OUT VARCHAR2 )
public static void getParentNodes( OracleConnection _connection, String p_init_msg_list, String p_hierarchy_type, BigDecimal p_child_id, String p_child_table_name, String p_child_object_type, String p_parent_table_name, String p_parent_object_type, String p_include_node, java.sql.Timestamp p_effective_date, BigDecimal p_no_of_records, RelatedNodesListRec [][] x_related_nodes_list, String [] x_return_status, BigDecimal [] x_msg_count, String [] x_msg_data ) throws SQLException;
Use this routine to retrieve the child nodes of a parent in a hierarchy. Pass a valid hierarchy relationship type and any necessary child type and parent node information. The API returns a set of child nodes in that hierarchy.
PROCEDURE get_child_nodes( p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE, p_hierarchy_type IN VARCHAR2, p_parent_id IN NUMBER, p_parent_table_name IN VARCHAR2, p_parent_object_type IN VARCHAR2, p_child_table_name IN VARCHAR2, p_child_object_type IN VARCHAR2, p_include_node IN VARCHAR2 := 'Y', p_effective_date IN DATE := SYSDATE, p_no_of_records IN NUMBER := 100, x_related_nodes_list OUT NOCOPY RELATED_NODES_LIST_TYPE, x_return_status OUT VARCHAR2, x_msg_count OUT NUMBER, x_msg_data OUT VARCHAR2 )
public static void getChildNodes( OracleConnection _connection, String p_init_msg_list, String p_hierarchy_type, BigDecimal p_parent_id, String p_parent_table_name, String p_parent_object_type, String p_child_table_name, String p_child_object_type, String p_include_node, java.sql.Timestamp p_effective_date, BigDecimal p_no_of_records, RelatedNodesListRec [][] x_related_nodes_list, String [] x_return_status, BigDecimal [] x_msg_count, String [] x_msg_data ) throws SQLException;
Use this routine to retrieve the top parent nodes in a hierarchy. Pass a valid hierarchy relationship type. The API returns a set of the top parent nodes in that hierarchy.
PROCEDURE get_top_parent_nodes( p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE, p_hierarchy_type IN VARCHAR2, p_parent_table_name IN VARCHAR2 := 'HZ_PARTIES', p_parent_object_type IN VARCHAR2 := 'ALL', p_effective_date IN DATE := SYSDATE, p_no_of_records IN NUMBER := 100, x_top_parent_list OUT NOCOPY RELATED_NODES_LIST_TYPE, x_return_status OUT VARCHAR2, x_msg_count OUT NUMBER, x_msg_data OUT VARCHAR2 )
public static void getTopParentNodes( OracleConnection _connection, String p_init_msg_list, String p_hierarchy_type, String p_parent_table_name, String p_parent_object_type, java.sql.Timestamp p_effective_date, BigDecimal p_no_of_records, RelatedNodesListRec [][] x_top_parent_list, String [] x_return_status, BigDecimal [] x_msg_count, String [] x_msg_data ) throws SQLException;