API To Create/Update Party

This post describes the purpose of the

1>.“csi_party_relationships_pub.update_inst_party_relationship.  This API can be used to create a new instance party relationship in Installed Base.

2>.csi_party_relationships_pub.create_inst_party_relationship”. This API can be used to update an existing instance party relationship in Installed Base.

Pre-Requisite

-----------------
1. Install Base module should be installed

API Description:

--------------------

Package And Procedure Name
The API for creating/updating the party/owner is available in the standard package csi_party_relationships_pub.
 The PL/SQL Procedure create_inst_party_relationship of the standard package mentioned above is used to create a new instance party/owner relationship in Installed Base.

The PL/SQL Procedure update_inst_party_relationship of the standard package mentioned above is used to update an existing instance party/owner relationship in Installed Base

APIs Description: Parameters
------------------------------

Parameter Name		Type(In/Out)	Data Type
p_api_version		IN	NUMBER
p_commit		IN	VARCHAR2
p_init_msg_list		IN	VARCHAR2
p_validation_level		IN	NUMBER
p_party_tbl		IN	csi_datastructures_pub.party_tbl
p_party_account_tbl		IN OUT NOCOPY	csi_datastructures_pub.party_account_tbl
p_txn_rec		IN OUT NOCOPY	csi_datastructures_pub.transaction_rec
x_return_status		OUT NOCOPY	VARCHAR2
x_msg_count		OUT NOCOPY	NUMBER
x_msg_data		OUT NOCOPY	VARCHAR2

API Description: Parameter Sources
------------------------------------- 

In the Test Script the objective of the Test to create/update the following fields of the party/owner relationship.

1. Party details.

2. Party relationship.

3. Party Account details.

As the Asset Association is updating with the API, So the p_asset_assignment_tbl is populated.

In other cases Please refer to the table below.

Table Type Parameter Name	Associated Functional part
p_party_tbl	Party Association Updation.
p_account_tbl	Party-account relationship updation.
p_txn_rec	Details of the Transation create as a part of current updation.

In the subsequent call the parameters used are: 
-----------------------------------------------

Parameter Name	Source(Tablename.column / Query)
p_party_tbl.instance_party_id	csi_i_parties.instance_party_id
p_party_tbl.instance_id	csi_i_parties.instance_id
p_party_tbl.party_id	csi_i_parties.party_id
p_party_tbl.object_version_number	csi_i_parties.object_version_number
p_party_tbl.relationship_type_code	csi_i_parties.relationship_type_code
p_party_tbl.party_source_table	'HZ_PARTIES'
p_party_tbl.active_start_date	System Date
p_party_tbl.active_end_date	NULL
p_party_tbl.contact_flag	N
P_ account_tbl.ip_account_id	csi_ip_accounts.ip_account_id
P_ account_tbl.instance_party_id	csi_ip_accounts.instance_party_id
P_ account_tbl.party_account_id	csi_ip_accounts.party_account_id
P_ account_tbl.object_version_number	csi_ip_accounts.object_version_number
P_ account_tbl. relationship_type_code	csi_ip_accounts.relationship_type_code
P_ account_tbl. parent_tbl_index	1
P_ account_tbl. active_start_date	System Date
p_api_version	1.0
p_commit	fnd_api.g_false;
p_init_msg_list	NULL
p_validation_level	3
P_ txn_rec.transaction_date	System Date
P_ txn_rec.source_transaction_date	System Date
P_ txn_rec.transaction_type_id	1 ( This value is derived from the table CSI_TXN_TYPES) ( 1 : Installed Base User Interface)
P_ txn_rec.object_version_number	1

1.       Get the above parameters from the table.columns mentioned above.

2.       Call the API though the calling script mentioned below.

*** > csi_party_relationships_pub.create_inst_party_relationship.

DECLARE
X_PARTY_TBL CSI_DATASTRUCTURES_PUB.PARTY_TBL;
X_ACCOUNT_TBL CSI_DATASTRUCTURES_PUB.PARTY_ACCOUNT_TBL;
X2_TXN_REC CSI_DATASTRUCTURES_PUB.TRANSACTION_REC;
x_return_status VARCHAR2(100);
x_msg_count NUMBER;
x_msg_data VARCHAR2(30000) := NULL;
x_created_manually_flag VARCHAR2(100);
l_org_id number:=41;
n number :=1;
V_INSTANCE_ID NUMBER;
P_COMMIT VARCHAR2(5);
P2_COMMIT VARCHAR2(5) := fnd_api.g_false;
P_VALIDATION_LEVEL NUMBER;
P_INIT_MSG_LST VARCHAR2(500);
V_INSTANCE_PARTY_ID NUMBER := 0;
V_IP_ACCOUNT_ID NUMBER := 0;
V_RELATIONSHIP_ID NUMBER;
V_SUCCESS VARCHAR2(1) := 'T';
X2_RETURN_STATUS VARCHAR2(100) := NULL;
X2_MSG_COUNT NUMBER;
x2_msg_data VARCHAR2(20000) := NULL;
P2_VALIDATION_LEVEL NUMBER;
P2_INIT_MSg_LST VARCHAR2(500) := NULL;
x_my_text VARCHAR2(30000) := NULL;
x_error_text VARCHAR2(30000) := NULL;
l_error VARCHAR2(30000) := NULL;
t_output                     VARCHAR2(2000);
t_msg_dummy               NUMBER;
BEGIN
V_INSTANCE_ID := <Instance id>;
V_INSTANCE_PARTY_ID := <Instance party id>;
V_IP_ACCOUNT_ID := <IP account id of the customer>;
csi_datastructures_pub.g_install_param_rec.fetch_flag := NULL;

g_party_tbl.instance_party_id := <INSTANCE_PARTY_ID>;
g_party_tbl(1).instance_id := <INS_ID>;
g_party_tbl(1).party_id := <PARTY_ID>;
g_party_tbl(1).object_version_number := <VERSION number>;
g_party_tbl(1).relationship_type_code := 'LEGAL OWNER';
g_party_tbl(1).party_source_table := 'HZ_PARTIES';
g_party_tbl(1).active_start_date := SYSDATE;
g_party_tbl(1).active_end_date := NULL;
g_party_tbl(1).contact_flag := 'N'
x2_txn_rec.transaction_date := TRUNC(SYSDATE);
x2_txn_rec.source_transaction_date := TRUNC(SYSDATE);
x2_txn_rec.transaction_type_id := 1;
x2_txn_rec.object_version_number := 1;


P2_VALIDATION_LEVEL := 3;
P2_INIT_MSg_LST := 'T';

g_account_tbl(1).ip_account_id := <IP_ACCOUNT_ID> ;
g_account_tbl(1).instance_party_id := <INSTANCE_PARTY_ID>;
g_account_tbl(1).party_account_id := <CUST_ACCOUNT_ID>;
g_account_tbl(1).object_version_number := <ACNT_VERSION>;
g_account_tbl(1).relationship_type_code := <Relationship Type code>;--'LEGAL OWNER';
g_account_tbl(1).parent_tbl_index := 1;
g_account_tbl(1).active_start_date := SYSDATE;

CSI_PARTY_RELATIONSHIPS_PUB.CREATE_INST_PARTY_RELATIONSHIP
(
p_api_version => 1.0
,p_commit => P2_COMMIT
,p_init_msg_list => P2_INIT_MSg_LST
,p_validation_level => 3  -- P2_VALIDATION_LEVEL
,p_party_tbl => X_PARTY_TBL
,p_party_account_tbl =>  X_ACCOUNT_TBL
,p_txn_rec => X2_TXN_REC
,x_return_status => X2_RETURN_STATUS
,x_msg_count => X2_MSG_COUNT
,x_msg_data => X2_MSG_DATA
);
dbms_output.put_line(X2_RETURN_STATUS);
IF X2_MSG_COUNT > 0 THEN
    FOR j in 1 .. X2_MSG_COUNT
      LOOP
      fnd_msg_pub.get ( j
                          , FND_API.G_FALSE
                          , X2_MSG_DATA
                         , t_msg_dummy );
  t_output := ( 'Error' || To_Char ( j ) || ': ' || x2_msg_data );
dbms_output.put_line(t_output);
END LOOP;
END IF;
END ;

*** > csi_party_relationships_pub.update_inst_party_relationship.

DECLARE

X_PARTY_TBL CSI_DATASTRUCTURES_PUB.PARTY_TBL;

X_ACCOUNT_TBL CSI_DATASTRUCTURES_PUB.PARTY_ACCOUNT_TBL;

X2_TXN_REC CSI_DATASTRUCTURES_PUB.TRANSACTION_REC;

x_return_status VARCHAR2(100);

x_msg_count NUMBER;

x_msg_data VARCHAR2(30000) := NULL;

x_created_manually_flag VARCHAR2(100);

l_org_id number:=41;

n number :=1;

V_INSTANCE_ID NUMBER;

P_COMMIT VARCHAR2(5);

P2_COMMIT VARCHAR2(5) := fnd_api.g_false;

P_VALIDATION_LEVEL NUMBER;

P_INIT_MSG_LST VARCHAR2(500);

V_INSTANCE_PARTY_ID NUMBER := 0;

V_IP_ACCOUNT_ID NUMBER := 0;

V_RELATIONSHIP_ID NUMBER;

V_SUCCESS VARCHAR2(1) := 'T';

X2_RETURN_STATUS VARCHAR2(100) := NULL;

X2_MSG_COUNT NUMBER;

x2_msg_data VARCHAR2(20000) := NULL;

P2_VALIDATION_LEVEL NUMBER;

P2_INIT_MSg_LST VARCHAR2(500) := NULL;

x_my_text VARCHAR2(30000) := NULL;

x_error_text VARCHAR2(30000) := NULL;

l_error VARCHAR2(30000) := NULL;

t_output                     VARCHAR2(2000);

t_msg_dummy               NUMBER;

BEGIN

V_INSTANCE_ID := <Instance id>;

V_INSTANCE_PARTY_ID := <Instance party id>; 

V_IP_ACCOUNT_ID := <IP account id of the customer>;

csi_datastructures_pub.g_install_param_rec.fetch_flag := NULL;

g_party_tbl.instance_party_id := <INSTANCE_PARTY_ID>;

g_party_tbl(1).instance_id := <INS_ID>;

g_party_tbl(1).party_id := <PARTY_ID>;

g_party_tbl(1).object_version_number := <VERSION number>;

g_party_tbl(1).relationship_type_code := 'LEGAL OWNER';

g_party_tbl(1).party_source_table := 'HZ_PARTIES';

g_party_tbl(1).active_start_date := SYSDATE;

g_party_tbl(1).active_end_date := NULL;

g_party_tbl(1).contact_flag := 'N'

x2_txn_rec.transaction_date := TRUNC(SYSDATE);

x2_txn_rec.source_transaction_date := TRUNC(SYSDATE);

x2_txn_rec.transaction_type_id := 1;

x2_txn_rec.object_version_number := 1;

P2_VALIDATION_LEVEL := 3;

P2_INIT_MSg_LST := 'T';

g_account_tbl(1).ip_account_id := <IP_ACCOUNT_ID> ;

g_account_tbl(1).instance_party_id := <INSTANCE_PARTY_ID>;

g_account_tbl(1).party_account_id := <CUST_ACCOUNT_ID>;

g_account_tbl(1).object_version_number := <ACNT_VERSION>;

g_account_tbl(1).relationship_type_code := <Relationship Type code>;--'LEGAL OWNER';

g_account_tbl(1).parent_tbl_index := 1;

g_account_tbl(1).active_start_date := SYSDATE;

CSI_PARTY_RELATIONSHIPS_PUB.UPDATE_INST_PARTY_RELATIONSHIP

(

p_api_version => 1.0

,p_commit => P2_COMMIT

,p_init_msg_list => P2_INIT_MSg_LST

,p_validation_level => 3  -- P2_VALIDATION_LEVEL

,p_party_tbl => X_PARTY_TBL

,p_party_account_tbl =>  X_ACCOUNT_TBL

,p_txn_rec => X2_TXN_REC

,x_return_status => X2_RETURN_STATUS

,x_msg_count => X2_MSG_COUNT

,x_msg_data => X2_MSG_DATA

);

dbms_output.put_line(X2_RETURN_STATUS);

IF X2_MSG_COUNT > 0 THEN

    FOR j in 1 .. X2_MSG_COUNT

      LOOP

      fnd_msg_pub.get ( j

                          , FND_API.G_FALSE

                          , X2_MSG_DATA

                         , t_msg_dummy );

  t_output := ( 'Error' || To_Char ( j ) || ': ' || x2_msg_data );

dbms_output.put_line(t_output);

END LOOP;

END IF;

END ;

Steps To Verify the Effect of 
-----------------------------
csi_party_relationships_pub.update_inst_party_relationship/  csi_party_relationships_pub.create_inst_party_relationship
----------------------------------------------------------

Verification: Changes that are expected
----------------------------------------

a> It should create/update the party/owner relationship within the Install Base

b> In csi_i_parties and csi_ip_accounts tables there should be column for the created/updated party id and account id.

Verification: Changes That Occur 

> Navigation to check the changes –

 Oracle Installed Base User 
a> Oracle Installed Base 
b> (Search with item instance -> Click on “Discription) 
c> Party Relationship à Parties/Accounts

In csi_i_parties and csi_ip_accounts tables there are columns for the created/updated party id and account id.

1>. SELECT relationship_type_code

, object_version_number

, last_update_date 

FROM csi_i_parties

WHERE party_id=<above mentioned party id>;

2>. SELECT relationship_type_code

, object_version_number

, last_update_date 

FROM csi_ip_accounts

WHERE ip_account_id=<ip account id>;