How to attach or Delete Contract Terms on a BPA/PO/CPA automatically using Oracle API

CREATE OR REPLACE PACKAGE APPS.xxposhr_contract_attach_pkg




IS
PROCEDURE add_contract_attachments (p_po_header_id IN NUMBER,

p_template_id IN NUMBER,

px_return_status OUT VARCHAR2);

PROCEDURE delete_contr_attachments (p_po_header_id IN NUMBER);




END;

/

CREATE OR REPLACE PACKAGE BODY APPS.xxposhr_contract_attach_pkg




IS
PROCEDURE add_contract_attachments (p_po_header_id IN NUMBER,

p_template_id IN NUMBER,

px_return_status OUT VARCHAR2)

IS

l_msg_data VARCHAR2 (200);

l_msg_data_out VARCHAR2 (200);

lx_doc_type VARCHAR2 (100);

l_return_status VARCHAR2 (100);

l_po_num po_headers_all.segment1%TYPE;

l_contract_admin_id NUMBER;

l_msg_index NUMBER;

l_msg_count NUMBER;

lx_doc_id NUMBER;

l_location_code hr_locations_all.location_code%TYPE;

l_legal_contact_id NUMBER;

BEGIN

SELECT segment1, hr.location_code

INTO l_po_num, l_location_code

FROM po_headers_all poh, hr_locations_all hr

WHERE poh.bill_to_location_id = hr.location_id

AND poh.po_header_id = p_po_header_id;

IF SUBSTR (l_location_code, 1, 3) IN ('THM', 'TDL')

THEN --SOUTH

l_contract_admin_id := 2614; -- Dutra, Yolanda (User ID from fnd_user)

l_legal_contact_id := 1278; -- Nowaid, Zabi

NULL;

ELSIF SUBSTR (l_location_code, 1, 3) IN ('THM', 'TDL')

THEN --NORTH

l_contract_admin_id := 2424; -- Vastbinder, Courtney N

END IF;

mo_global.init ('OKC');

okc_template_usages_grp.create_template_usages (

p_api_version => 1.0,

p_init_msg_list => fnd_api.g_false,

p_validation_level => fnd_api.g_valid_level_full,

p_commit => fnd_api.g_false,

x_return_status => l_return_status,

x_msg_count => l_msg_count,

x_msg_data => l_msg_data,

p_document_type => 'PO_STANDARD',

p_document_id => p_po_header_id,

p_template_id => p_template_id,

p_doc_numbering_scheme => NULL,

p_document_number => l_po_num,

p_article_effective_date => NULL,

p_config_header_id => NULL,

p_config_revision_number => NULL,

p_valid_config_yn => NULL,

x_document_type => lx_doc_type,

x_document_id => lx_doc_id,

p_contract_admin_id => l_contract_admin_id,

p_legal_contacT_id => l_legal_contact_id);

DBMS_OUTPUT.put_line ('Status is ' || l_return_status);

IF ( (NVL (l_return_status, 'X') != 'S') AND (l_msg_count > 0))

THEN

FOR i IN 1 .. l_msg_count

LOOP

fnd_msg_pub.get (p_msg_index => i,

p_encoded => 'F',

p_data => l_msg_data_out,

p_msg_index_out => l_msg_index);

DBMS_OUTPUT.put_line ('Error is ' || l_msg_data_out);

END LOOP;

fnd_msg_pub.delete_msg;

END IF;

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.put_line ('Error');

px_return_status := 'Exception raised ' || SQLERRM;

RAISE;

END add_contract_attachments;

PROCEDURE delete_contr_attachments (p_po_header_id IN NUMBER)

IS

l_return_status VARCHAR2 (10);

l_msg_count NUMBER;

l_msg_index NUMBER;

l_msg_data VARCHAR2 (1000);

l_msg_data_out VARCHAR2 (1000);

BEGIN

mo_global.init ('OKC');

OKC_TEMPLATE_USAGES_GRP.delete_template_usages (

p_api_version => 1.0,

p_init_msg_list => FND_API.G_FALSE,

p_commit => FND_API.G_FALSE,

x_return_status => l_return_status,

x_msg_count => l_msg_count,

x_msg_data => l_msg_data,

p_document_type => 'PO_STANDARD',

p_document_id => p_po_header_id,

p_object_version_number => 1);

IF ( (NVL (l_return_status, 'X') != 'S') AND (l_msg_count > 0))

THEN

FOR I IN 1 .. L_MSG_COUNT

LOOP

FND_MSG_PUB.GET (P_MSG_INDEX => I,

P_ENCODED => 'F',

P_DATA => l_msg_data_out,

P_MSG_INDEX_OUT => l_msg_index);

DBMS_OUTPUT.PUT_LINE ('Error is ' || l_msg_data_out);

END LOOP;

FND_MSG_PUB.DELETE_MSG;

END IF;

DBMS_OUTPUT.put_line ('Status is ' || l_Return_status);

END delete_contr_attachments;

END xxposhr_contract_attach_pkg;




/