How to Delete a Service Contract

How to Delete a Service Contract
================================


To delete a service contract from Application, the contract status should be either 'Entered' or 'Cancelled'.
In any other status you can not delete a Service contract from Application.

To Delete Contracts from Application
1. Go to Service Contracts Super User Responsibility --> Launch Contract --> Find Contract --> Open Contract
2. Status of the contract should be 'Entered' or 'Cancelled'
3. Go to Actions --> Delete Contract
4. Click OK.

However, if the contract is Activated once or is on QA Hold status you can not delete it from Application.
You can use below mentioned script to delete the contract.

Pseudo Code :

1. Change status of the Contract to 'Entered' if it is not in Entered state using API okc_contract_pub.update_contract_header.
2. Change status of all the contract lines to 'Entered' if not in Entered state using API okc_contract_pub.update_contract_line.
3. Delete all the lines of contract using API okc_contract_pub.delete_contract_line.
4. Delete contract header using API okc_contract_pub.delete_contract_header.
5. COMMIT the change or ROLLBACK in case of any error.

Code :

**************************************************************************************

DECLARE
CURSOR cur_hdr
IS
SELECT ID, contract_number, contract_number_modifier, start_date,
end_date, sts_code
FROM okc_k_headers_b okhb
WHERE 1 = 1
AND contract_number = :p_contract_number
AND NVL (contract_number_modifier, 'AAA') =
NVL (:p_contract_number_modifier, 'AAA');

x_head_update_rec_type okc_contract_pub.chrv_rec_type;
x_out_chrv_rec okc_contract_pub.chrv_rec_type;
x_clev_rec_type okc_cle_pvt.clev_rec_type;
x_out_clev_rec okc_cle_pvt.clev_rec_type;
x_return_status VARCHAR2 (1);
x_msg_count NUMBER;
x_msg_data VARCHAR2 (20000);
x_msg_index_out NUMBER;
x_init_msg_list VARCHAR2 (1) := okc_api.g_false;
g_api_error EXCEPTION;
BEGIN
DBMS_OUTPUT.put_line ('Start');

FOR hdr_rec IN cur_hdr
LOOP
BEGIN
--- Setting header status to ENTERED to allow update
DBMS_OUTPUT.put_line ( 'Start Delete Contracts for Contract #: '
|| hdr_rec.contract_number
|| ' '
|| hdr_rec.contract_number_modifier
);

IF hdr_rec.sts_code <> 'ENTERED'
THEN
x_head_update_rec_type.ID := hdr_rec.ID;
x_head_update_rec_type.sts_code := 'ENTERED';
x_return_status := NULL;
x_msg_count := 0;
x_msg_data := NULL;
x_msg_index_out := NULL;
okc_contract_pub.update_contract_header
(p_api_version => 1.0,
p_init_msg_list => fnd_api.g_true,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_restricted_update => fnd_api.g_true,
p_chrv_rec => x_head_update_rec_type,
x_chrv_rec => x_out_chrv_rec
);

IF (x_return_status <> fnd_api.g_ret_sts_success)
THEN
DBMS_OUTPUT.put_line
('Set Header Status to ENTERED API Error ');
RAISE g_api_error;
END IF;
END IF; -- end if contract status <> 'ENTERED'

--- Setting lines status to ENTERED to allow update
FOR i_line IN (SELECT ID, sts_code
FROM okc_k_lines_b
WHERE dnz_chr_id = hdr_rec.ID)
LOOP
IF i_line.sts_code <> 'ENTERED'
THEN
x_return_status := NULL;
x_msg_count := 0;
x_msg_data := NULL;
x_msg_index_out := NULL;
x_clev_rec_type.sts_code := 'ENTERED';
x_clev_rec_type.ID := i_line.ID;
okc_contract_pub.update_contract_line
(p_api_version => 1.0,
p_init_msg_list => fnd_api.g_true,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_restricted_update => fnd_api.g_true,
p_clev_rec => x_clev_rec_type,
x_clev_rec => x_out_clev_rec
);

IF (x_return_status <> fnd_api.g_ret_sts_success)
THEN
DBMS_OUTPUT.put_line
('Set Line Status to ENTERED API Error ');
RAISE g_api_error;
END IF;
END IF; -- end if line is ENTERED
END LOOP; -- end loop for setting lines status to ENTERED

FOR line_rec IN (SELECT ID, line_number
FROM okc_k_lines_b
WHERE chr_id = hdr_rec.ID)
LOOP
DBMS_OUTPUT.put_line ('In Line Loop');
x_return_status := NULL;
x_msg_count := 0;
x_msg_data := NULL;
x_msg_index_out := NULL;
--- Call Delete Contract Line API
okc_contract_pub.delete_contract_line
(p_api_version => 1.0,
p_init_msg_list => fnd_api.g_true,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_line_id => line_rec.ID
);
DBMS_OUTPUT.put_line ( 'Delete Line Numer: '
|| line_rec.line_number
|| ' x_return_status: '
|| x_return_status
);

IF (x_return_status <> fnd_api.g_ret_sts_success)
THEN
DBMS_OUTPUT.put_line
('Delete_Contract_Line API Error: Delete Line.');
RAISE g_api_error;
END IF; -- end if delete line successfull
END LOOP;

---- Deleting contract
x_head_update_rec_type.ID := hdr_rec.ID;
x_return_status := NULL;
x_msg_count := 0;
x_msg_data := NULL;
x_msg_index_out := NULL;
okc_contract_pub.delete_contract_header
(p_api_version => 1.0,
p_init_msg_list => fnd_api.g_true,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_chrv_rec => x_head_update_rec_type
);

IF (x_return_status <> fnd_api.g_ret_sts_success)
THEN
DBMS_OUTPUT.put_line ('Delete Contract API Error: ');
RAISE g_api_error;
END IF;

DBMS_OUTPUT.put_line ('Delete Successfull');
COMMIT;
EXCEPTION
WHEN g_api_error
THEN
ROLLBACK;

FOR i IN 1 .. x_msg_count
LOOP
fnd_msg_pub.get (p_msg_index => i,
p_encoded => fnd_api.g_false,
p_data => x_msg_data,
p_msg_index_out => x_msg_index_out
);
DBMS_OUTPUT.put_line ('x_msg_data: ' || x_msg_data);
END LOOP;
WHEN OTHERS
THEN
ROLLBACK;
DBMS_OUTPUT.put_line ('When Others Error: ' || SQLERRM);
END; -- end begin for header cursor
END LOOP; -- end loop contract header

DBMS_OUTPUT.put_line ('End');
EXCEPTION
WHEN OTHERS
THEN
ROLLBACK;
DBMS_OUTPUT.put_line ('Error: ' || SQLERRM);
END;


**************************************************************************************


P.S.: APIs used in the code are not supported by Oracle.
You may also try out OKC_DELETE_CONTRACT_PUB.delete_contract API

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>;