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