Create Relationship API

TCA Create Relationship API

Name of the API : CREATE RELATIONSHIP
Base Tables Affected : HZ_RELATIONSHIPS
PL/SQL Procedure used : CREATE_RELATIONSHIP
Package Used : HZ_RELATIONSHIP_V2PUB

Mandatory Parameters :
Parameter Name Parameter Type Data Type
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

Procedure:

 General Tips for Running the Create Relationship API:
a) Save the API in a script file and then run the script from the SQL Prompt
(Example : RUN createrelation.sql)
b) Set the organization id before running the script as :
EXEC fnd_client_info.set_org_context(‘<orgid>’); for 11i
OR For R12
EXEC mo_global.init (‘AR’);
EXEC mo_global.set_org_context(<org_id>, NULL, ‘AR’);
EXEC fnd_global.set_nls_context(‘AMERICAN’);
EXEC mo_global.set_policy_context(‘S’, <org_id>);
c) Enter the Values of the Parameters :
Relationship_Type,
Relationship_Code,
Subject_Id,
Subject_Table_Name,
Subject_Type,
Object_Id,
Object_Table_Name,
Object_Type,
Start_Date,
Created_By_Moudle
either from inside the API or at the SQL Prompt.
i. The value for Parameter Relationship_Type should be one of the Values defined
for the Look Up Code PARTY_RELATIONS_TYPE .
ii. The Value for Parameter Relationship_Code should be one of the Values
for the Look Up Code PARTY_RELATIONS_TYPE .
iii. The Value for Parameter Subject_Id should be one of the Values
from the Column Object_Id in FND_OBJECT_INSTANCE_SETS .
iv. The Value for Parameter Subject_Table_Name should be one of the values
from the Column Obj_Name from table FND_OBJECTS.
v. The Value for Parameter Subject_Type should be one of the Values
from the Column INSTANCE_SET_NAME from the table
FND_OBJECT_INSTANCE_SETS .
vi. The Value for Parameter Object_Id should be one of the Values
from the Column Object_Id from table FND_OBJECTS.
vii. The Value for Parameter Object_Table_Name should be one of the Values
from the Column Obj_Name from table FND_OBJECTS.
viii. The Value for Parameter Object_Type should be one of the Values
from the Column INSTANCE_SET_NAME from the table
FND_OBJECT_INSTANCE_SETS .
ix. When user call the create relationship procedure, two new records will be
created in HZ_RELATIONSHIPS table.
x. Users 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, for example, 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.
xi. The relationship code must be either a forward or backward relationship code
defined in active records in the HZ_RELATIONSHIP_TYPES table for the
relationship type.
xii. The end date will be default to ’31-DEC-4712′ if not specified. The end date
must be greater than the start date.
xiii. For a given subject_id, object_id, relationship_code, there can be no overlap of
the start date and the end date.
xiv. 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.
xv. 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.
xvi. 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.
xvii. A record will be created in the HZ_RELATIONSHIPS table with the relationship
code Entered as input parameter.
xviii. It will find the 2 nd relationship code from the HZ_RELATIONSHIP_TYPES
table, and use it to create a second relationship. The 2 nd 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).

Example For Running Create Relationship API:

 Sample Output of the Create Relationship Script:
x_return_status = S
x_msg_count = 0
Relationship Id =430373
Party Id = 553708
Party Number = 60551
x_msg_data =

Script To Verify whether Create Relationship API is successful:

 

Leave a Reply