Sunday, August 7, 2011

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 NameType(In/Out)Data Type



p_api_versionINNUMBER
p_commitINVARCHAR2
p_init_msg_listINVARCHAR2
p_validation_levelINNUMBER
p_party_tblINcsi_datastructures_pub.party_tbl
p_party_account_tblIN OUT NOCOPYcsi_datastructures_pub.party_account_tbl
p_txn_rec
IN OUT NOCOPY
csi_datastructures_pub.transaction_rec
x_return_statusOUT NOCOPYVARCHAR2
x_msg_countOUT NOCOPYNUMBER
x_msg_dataOUT NOCOPYVARCHAR2




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 NameAssociated Functional part


p_party_tblParty Association Updation.
p_account_tblParty-account relationship updation.
p_txn_recDetails 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>;



No comments:

Post a Comment