[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT contract_number,CONTRACT_TYPE,ORG_ID,contract_version_num
FROM okc_rep_contracts_all
WHERE contract_id = p_contract_id;
SELECT approval_workflow_name FROM okc_bus_doc_types_b WHERE document_type= p_contract_type;
SELECT OKC_REP_WF_S.nextval INTO l_wf_sequence FROM dual;
UPDATE OKC_REP_CONTRACTS_ALL
SET wf_item_type = l_wf_name, wf_item_key = l_wf_sequence
WHERE contract_id=p_contract_id;
PROCEDURE delete_contacts(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_commit IN VARCHAR2,
p_contract_id IN NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2) IS
l_api_name VARCHAR2(30);
'Entered OKC_REP_CONTRACT_PROCESS_PVT.delete_contacts');
l_api_name := 'delete_contacts';
SAVEPOINT delete_contacts_PVT;
DELETE FROM OKC_REP_PARTY_CONTACTS
WHERE CONTRACT_ID = p_CONTRACT_ID;
'Leaving OKC_REP_CONTRACT_PROCESS_PVT.delete_contacts');
'Leaving delete_contacts:FND_API.G_EXC_ERROR Exception');
ROLLBACK TO delete_contacts_PVT;
'Leaving delete_contacts:FND_API.G_EXC_UNEXPECTED_ERROR Exception');
ROLLBACK TO delete_contacts_PVT;
'Leaving delete_contacts because of EXCEPTION: ' || sqlerrm);
ROLLBACK TO delete_contacts_PVT;
END delete_contacts;
PROCEDURE delete_parties(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_commit IN VARCHAR2,
p_contract_id IN NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2) IS
l_api_name VARCHAR2(30);
'Entered OKC_REP_CONTRACT_PROCESS_PVT.delete_parties');
l_api_name := 'delete_parties';
SAVEPOINT delete_parties_PVT;
DELETE FROM OKC_REP_CONTRACT_PARTIES
WHERE CONTRACT_ID = p_CONTRACT_ID;
'Leaving OKC_REP_CONTRACT_PROCESS_PVT.delete_parties');
'Leaving delete_parties:FND_API.G_EXC_ERROR Exception');
ROLLBACK TO delete_parties_PVT;
'Leaving delete_parties:FND_API.G_EXC_UNEXPECTED_ERROR Exception');
ROLLBACK TO delete_parties_PVT;
'Leaving delete_parties because of EXCEPTION: ' || sqlerrm);
ROLLBACK TO delete_parties_PVT;
END delete_parties;
PROCEDURE delete_risks(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_commit IN VARCHAR2,
p_contract_id IN NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2) IS
l_api_name VARCHAR2(30);
SELECT contract_type, contract_version_num
FROM OKC_REP_CONTRACTS_ALL
WHERE contract_id = p_contract_id;
'Entered OKC_REP_CONTRACT_PROCESS_PVT.delete_risks');
l_api_name := 'delete_risks';
SAVEPOINT delete_risks_PVT;
DELETE FROM OKC_CONTRACT_RISKS
WHERE BUSINESS_DOCUMENT_TYPE = contract_rec.contract_type
AND BUSINESS_DOCUMENT_ID = p_CONTRACT_ID
AND BUSINESS_DOCUMENT_VERSION = contract_rec.contract_version_num;
'Leaving OKC_REP_CONTRACT_PROCESS_PVT.delete_risks');
'Leaving delete_risks:FND_API.G_EXC_ERROR Exception');
ROLLBACK TO delete_risks_PVT;
'Leaving delete_risks:FND_API.G_EXC_UNEXPECTED_ERROR Exception');
ROLLBACK TO delete_risks_PVT;
'Leaving delete_risks because of EXCEPTION: ' || sqlerrm);
ROLLBACK TO delete_risks_PVT;
END delete_risks;
PROCEDURE delete_related_contracts(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_commit IN VARCHAR2,
p_contract_id IN NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2) IS
l_api_name VARCHAR2(30);
SELECT contract_type, contract_version_num
FROM OKC_REP_CONTRACTS_ALL
WHERE contract_id = p_contract_id;
'Entered OKC_REP_CONTRACT_PROCESS_PVT.delete_related_contracts');
l_api_name := 'delete_related_contracts';
SAVEPOINT delete_related_contracts_PVT;
DELETE FROM OKC_REP_CONTRACT_USAGES
WHERE contract_TYPE = contract_rec.contract_type
AND contract_ID = p_CONTRACT_ID
AND contract_VERSION = contract_rec.contract_version_num;
'Leaving OKC_REP_CONTRACT_PROCESS_PVT.delete_related_contracts');
'Leaving delete_related_contracts:FND_API.G_EXC_ERROR Exception');
ROLLBACK TO delete_related_contracts_PVT;
'Leaving delete_related_contracts:FND_API.G_EXC_UNEXPECTED_ERROR Exception');
ROLLBACK TO delete_related_contracts_PVT;
'Leaving delete_related_contracts because of EXCEPTION: ' || sqlerrm);
ROLLBACK TO delete_related_contracts_PVT;
END delete_related_contracts;
PROCEDURE delete_ACL(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_commit IN VARCHAR2,
p_contract_id IN NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2) IS
l_api_name VARCHAR2(30);
SELECT
fgrant.grantee_type grantee_type,
fgrant.grantee_key grantee_key,
fgrant.instance_type instance_type,
fgrant.instance_set_id instance_set_id,
fmenu.menu_name menu_name,
fgrant.program_name program_name,
fgrant.program_tag program_tag
FROM FND_GRANTS fgrant, FND_OBJECTS fobj, FND_MENUS fmenu
WHERE fgrant.menu_id = fmenu.menu_id
AND fgrant.object_id = fobj.object_id
AND fobj.obj_name = 'OKC_REP_CONTRACT'
AND fgrant.instance_pk1_value = to_char(p_contract_id);
'Entered OKC_REP_CONTRACT_PROCESS_PVT.delete_ACL');
l_api_name := 'delete_ACL';
SAVEPOINT delete_ACL_PVT;
FND_GRANTS_PKG.delete_grant(
p_grantee_type => acl_rec.grantee_type, -- USER or GROUP
p_grantee_key => acl_rec.grantee_key, -- user_id or group_id
p_object_name => G_REP_CONTRACT,
p_instance_type => acl_rec.instance_type, -- INSTANCE or SET
p_instance_set_id => acl_rec.instance_set_id, -- Instance set id.
p_instance_pk1_value => to_char(p_contract_id), -- Object PK Value
p_menu_name => acl_rec.menu_name, -- Menu to be deleted.
p_program_name => acl_rec.program_name, -- name of the program that handles grant.
p_program_tag => acl_rec.program_tag, -- tag used by the program that handles grant.
x_success => x_success, -- return param. 'T' or 'F'
x_errcode => x_errcode );
'Leaving OKC_REP_CONTRACT_PROCESS_PVT.delete_ACL');
'Leaving delete_ACL:FND_API.G_EXC_ERROR Exception');
ROLLBACK TO delete_ACL_PVT;
'Leaving delete_ACL:FND_API.G_EXC_UNEXPECTED_ERROR Exception');
ROLLBACK TO delete_ACL_PVT;
'Leaving delete_ACL because of EXCEPTION: ' || sqlerrm);
ROLLBACK TO delete_ACL_PVT;
END delete_ACL;
PROCEDURE delete_status_history(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_commit IN VARCHAR2,
p_contract_id IN NUMBER,
p_contract_version IN NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2) IS
l_api_name VARCHAR2(30);
'Entered OKC_REP_CONTRACT_PROCESS_PVT.delete_status_history');
l_api_name := 'delete_status_history';
SAVEPOINT delete_status_history_PVT;
DELETE FROM OKC_REP_CON_STATUS_HIST
WHERE CONTRACT_ID = p_CONTRACT_ID
AND CONTRACT_VERSION_NUM = p_contract_version;
'Leaving OKC_REP_CONTRACT_PROCESS_PVT.delete_status_history');
'Leaving delete_status_history:FND_API.G_EXC_ERROR Exception');
ROLLBACK TO delete_status_history_PVT;
'Leaving delete_status_history:FND_API.G_EXC_UNEXPECTED_ERROR Exception');
ROLLBACK TO delete_status_history_PVT;
'Leaving delete_status_history because of EXCEPTION: ' || sqlerrm);
ROLLBACK TO delete_status_history_PVT;
END delete_status_history;
PROCEDURE delete_approval_history(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_commit IN VARCHAR2,
p_contract_id IN NUMBER,
p_contract_version IN NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2)IS
l_api_name VARCHAR2(30);
'Entered OKC_REP_CONTRACT_PROCESS_PVT.delete_approval_history');
l_api_name := 'delete_approval_history';
SAVEPOINT delete_approval_history_PVT;
DELETE FROM OKC_REP_CON_APPROVALS
WHERE CONTRACT_ID = p_CONTRACT_ID
AND CONTRACT_VERSION_NUM = p_contract_version;
'Leaving OKC_REP_CONTRACT_PROCESS_PVT.delete_approval_history');
'Leaving delete_approval_history:FND_API.G_EXC_ERROR Exception');
ROLLBACK TO delete_approval_history_PVT;
'Leaving delete_approval_history:FND_API.G_EXC_UNEXPECTED_ERROR Exception');
ROLLBACK TO delete_approval_history_PVT;
'Leaving delete_approval_history because of EXCEPTION: ' || sqlerrm);
ROLLBACK TO delete_approval_history_PVT;
END delete_approval_history;
PROCEDURE delete_bookmarks(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_commit IN VARCHAR2,
p_contract_type IN VARCHAR2,
p_contract_id IN NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2) IS
l_api_name VARCHAR2(30);
'Entered OKC_REP_CONTRACT_PROCESS_PVT.delete_bookmarks');
l_api_name := 'delete_bookmarks';
SAVEPOINT delete_bookmarks_PVT;
DELETE FROM OKC_REP_BOOKMARKS
WHERE OBJECT_TYPE = p_contract_type
AND OBJECT_ID = p_CONTRACT_ID
AND BOOKMARK_TYPE_CODE = G_CONTRACT_BOOKMARK_TYPE;
'Leaving OKC_REP_CONTRACT_PROCESS_PVT.delete_bookmarks');
'Leaving delete_bookmarks:FND_API.G_EXC_ERROR Exception');
ROLLBACK TO delete_bookmarks_PVT;
'Leaving delete_bookmarks:FND_API.G_EXC_UNEXPECTED_ERROR Exception');
ROLLBACK TO delete_bookmarks_PVT;
'Leaving delete_bookmarks because of EXCEPTION: ' || sqlerrm);
ROLLBACK TO delete_bookmarks_PVT;
END delete_bookmarks;
SELECT object_version_number
FROM OKC_REP_CONTRACTS_ALL
WHERE CONTRACT_ID = cp_contract_id
AND (object_version_number = cp_object_version_number OR cp_object_version_number IS NULL)
FOR UPDATE OF object_version_number NOWAIT;
SELECT object_version_number
FROM OKC_REP_CONTRACTS_ALL
WHERE CONTRACT_ID = cp_contract_id;
Okc_Api.Set_Message(G_APP_NAME,G_RECORD_DELETED);
Okc_Api.Set_Message(G_APP_NAME,G_RECORD_LOGICALLY_DELETED);
PROCEDURE delete_contract(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_commit IN VARCHAR2,
p_contract_id IN NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2) IS
l_api_name VARCHAR2(30);
SELECT contract_type, contract_version_num
FROM OKC_REP_CONTRACTS_ALL
WHERE contract_id = p_contract_id;
SELECT contract_status_code, esignature_required
FROM okc_rep_contract_vers
WHERE contract_id = p_contract_id
AND contract_version_num = p_con_version - 1;
'Entered OKC_REP_CONTRACT_PROCESS_PVT.delete_contract');
l_api_name := 'delete_contract';
SAVEPOINT delete_contract_PVT;
'Calling OKC_REP_CONTRACT_PROCESS_PVT.delete_contacts()');
delete_contacts(
p_api_version => 1.0,
p_commit => FND_API.G_FALSE,
p_init_msg_list => FND_API.G_FALSE,
p_contract_id => p_contract_id,
x_msg_data => x_msg_data,
x_msg_count => x_msg_count,
x_return_status => x_return_status);
'OKC_REP_CONTRACT_PROCESS_PVT.delete_contacts return status is: '
|| x_return_status);
'Calling OKC_REP_CONTRACT_PROCESS_PVT.delete_parties()');
delete_parties(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_contract_id => p_contract_id,
x_msg_data => x_msg_data,
x_msg_count => x_msg_count,
x_return_status => x_return_status);
'OKC_REP_CONTRACT_PROCESS_PVT.delete_parties return status is: '
|| x_return_status);
'Calling OKC_REP_CONTRACT_PROCESS_PVT.delete_risks()');
delete_risks(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_contract_id => p_contract_id,
x_msg_data => x_msg_data,
x_msg_count => x_msg_count,
x_return_status => x_return_status);
'OKC_REP_CONTRACT_PROCESS_PVT.delete_risks return status is: '
|| x_return_status);
'Calling OKC_REP_CONTRACT_PROCESS_PVT.delete_ACL()');
delete_ACL(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_contract_id => p_contract_id,
x_msg_data => x_msg_data,
x_msg_count => x_msg_count,
x_return_status => x_return_status);
'OKC_REP_CONTRACT_PROCESS_PVT.delete_ACL return status is: '
|| x_return_status);
'Calling OKC_REP_CONTRACT_PROCESS_PVT.delete_status_history()');
delete_status_history(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_contract_id => p_contract_id,
p_contract_version => contract_rec.contract_version_num,
x_msg_data => x_msg_data,
x_msg_count => x_msg_count,
x_return_status => x_return_status);
'OKC_REP_CONTRACT_PROCESS_PVT.delete_status_history return status is: '
|| x_return_status);
'Calling OKC_REP_CONTRACT_PROCESS_PVT.delete_approval_history()');
delete_approval_history(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_contract_id => p_contract_id,
p_contract_version => contract_rec.contract_version_num,
x_msg_data => x_msg_data,
x_msg_count => x_msg_count,
x_return_status => x_return_status);
'OKC_REP_CONTRACT_PROCESS_PVT.delete_approval_history return status is: '
|| x_return_status);
'Calling OKC_REP_CONTRACT_PROCESS_PVT.delete_bookmarks()');
delete_bookmarks(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_contract_type => contract_rec.contract_type,
p_contract_id => p_contract_id,
x_msg_data => x_msg_data,
x_msg_count => x_msg_count,
x_return_status => x_return_status);
'OKC_REP_CONTRACT_PROCESS_PVT.delete_bookmarks return status is: '
|| x_return_status);
'Calling OKC_REP_CONTRACT_PROCESS_PVT.delete_related_contracts()');
delete_related_contracts(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_contract_id => p_contract_id,
x_msg_data => x_msg_data,
x_msg_count => x_msg_count,
x_return_status => x_return_status);
'OKC_REP_CONTRACT_PROCESS_PVT.delete_related_contracts return status is: '
|| x_return_status);
'Calling OKC_CONTRACT_DOCS_GRP.Delete_Ver_Attachments');
OKC_CONTRACT_DOCS_GRP.Delete_Ver_Attachments(
p_api_version => 1.0,
p_business_document_type => contract_rec.contract_type,
p_business_document_id => p_contract_id,
p_business_document_version => G_CURRENT_VERSION,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
'OKC_CONTRACT_DOCS_GRP.Delete_Ver_Attachments return status is : '
|| x_return_status);
'Calling OKC_REP_CONTRACT_PROCESS_PVT.delete_uda_attribute_values()');
delete_uda_attribute_values(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_contract_id => p_contract_id,
x_msg_data => x_msg_data,
x_msg_count => x_msg_count,
x_return_status => x_return_status);
'OKC_REP_CONTRACT_PROCESS_PVT.delete_uda_attribute_values return status is: '
|| x_return_status);
-- Repository Enhancement 12.1 (For Delete Action)
IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1000: Calling Ptivate API to Delete the doc');
OKC_TERMS_UTIL_PVT.Delete_Doc(
x_return_status => x_return_status,
p_doc_type => contract_rec.contract_type,
p_doc_id => p_contract_id
);
OKC_TERMS_VERSION_PVT.Delete_Doc_Version(
x_return_status => x_return_status,
p_doc_type => contract_rec.contract_type,
p_doc_id => p_contract_id,
p_version_number => contract_rec.contract_version_num - 1
);
-- Repository Enhancement 12.1 Ends(For Delete Action)
IF(contract_rec.contract_version_num = 1) THEN
IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
'Calling OKC_DELIVERABLE_PROCESS_PVT.delete_deliverables');
OKC_DELIVERABLE_PROCESS_PVT.deleteDeliverables (
p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_bus_doc_id => p_contract_id,
p_bus_doc_type => contract_rec.contract_type,
p_bus_doc_version => G_CURRENT_VERSION,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
OKC_DELIVERABLE_PROCESS_PVT.deleteDeliverables(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_bus_doc_id => p_contract_id,
p_bus_doc_type => contract_rec.contract_type,
p_bus_doc_version => contract_rec.contract_version_num,
p_prev_del_active => l_is_activated,
p_revert_dels => 'Y',
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
'OKC_DELIVERABLE_PROCESS_PVT.deleteDeliverables return status is : '
|| x_return_status);
DELETE FROM OKC_REP_CONTRACTS_ALL
WHERE contract_id = p_contract_id;
'Deleted Contract header record');
INSERT INTO OKC_REP_CONTRACTS_ALL(
CONTRACT_ID,
CONTRACT_VERSION_NUM,
CONTRACT_NUMBER,
CONTRACT_TYPE,
CONTRACT_STATUS_CODE,
ORG_ID,
OWNER_ID,
SOURCE_LANGUAGE,
CONTRACT_NAME,
CONTRACT_DESC,
VERSION_COMMENTS,
AUTHORING_PARTY_CODE,
CONTRACT_EFFECTIVE_DATE,
CONTRACT_EXPIRATION_DATE,
CURRENCY_CODE,
AMOUNT,
OVERALL_RISK_CODE,
CANCELLATION_COMMENTS,
CANCELLATION_DATE,
TERMINATION_COMMENTS,
TERMINATION_DATE,
KEYWORDS,
PHYSICAL_LOCATION,
EXPIRE_NTF_FLAG,
EXPIRE_NTF_PERIOD,
NOTIFY_CONTACT_ROLE_ID,
WF_EXP_NTF_ITEM_KEY,
USE_ACL_FLAG,
WF_ITEM_TYPE,
WF_ITEM_KEY,
PROGRAM_ID,
PROGRAM_LOGIN_ID,
PROGRAM_APPLICATION_ID,
REQUEST_ID,
LATEST_SIGNED_VER_NUMBER,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
OBJECT_VERSION_NUMBER,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE,
CONTRACT_LAST_UPDATED_BY,
CONTRACT_LAST_UPDATE_DATE,
REFERENCE_DOCUMENT_TYPE,
REFERENCE_DOCUMENT_NUMBER,
ESIGNATURE_REQUIRED,
UDA_TEMPLATE_ID,
SBCR_COORDINATION_TYPE)
SELECT
CONTRACT_ID,
CONTRACT_VERSION_NUM,
CONTRACT_NUMBER,
CONTRACT_TYPE,
CONTRACT_STATUS_CODE,
ORG_ID,
OWNER_ID,
SOURCE_LANGUAGE,
CONTRACT_NAME,
CONTRACT_DESC,
VERSION_COMMENTS,
AUTHORING_PARTY_CODE,
CONTRACT_EFFECTIVE_DATE,
CONTRACT_EXPIRATION_DATE,
CURRENCY_CODE,
AMOUNT,
OVERALL_RISK_CODE,
CANCELLATION_COMMENTS,
CANCELLATION_DATE,
TERMINATION_COMMENTS,
TERMINATION_DATE,
KEYWORDS,
PHYSICAL_LOCATION,
EXPIRE_NTF_FLAG,
EXPIRE_NTF_PERIOD,
NOTIFY_CONTACT_ROLE_ID,
WF_EXP_NTF_ITEM_KEY,
USE_ACL_FLAG,
WF_ITEM_TYPE,
WF_ITEM_KEY,
PROGRAM_ID,
PROGRAM_LOGIN_ID,
PROGRAM_APPLICATION_ID,
REQUEST_ID,
LATEST_SIGNED_VER_NUMBER,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
OBJECT_VERSION_NUMBER,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE,
CONTRACT_LAST_UPDATED_BY,
CONTRACT_LAST_UPDATE_DATE,
REFERENCE_DOCUMENT_TYPE,
REFERENCE_DOCUMENT_NUMBER,
ESIGNATURE_REQUIRED,
UDA_TEMPLATE_ID,
SBCR_COORDINATION_TYPE
FROM OKC_REP_CONTRACT_VERS
WHERE contract_id = p_contract_id
AND contract_version_num = l_prev_version;
DELETE FROM OKC_REP_CONTRACT_VERS
WHERE contract_id = p_contract_id
AND contract_version_num = l_prev_version;
'Leaving OKC_REP_CONTRACT_PROCESS_PVT.delete_contract');
'Leaving delete_contract:FND_API.G_EXC_ERROR Exception');
ROLLBACK TO delete_contract_PVT;
'Leaving delete_contract:FND_API.G_EXC_UNEXPECTED_ERROR Exception');
ROLLBACK TO delete_contract_PVT;
'Leaving delete_contract because of EXCEPTION: ' || sqlerrm);
ROLLBACK TO delete_contract_PVT;
END delete_contract;
l_last_updated_by OKC_REP_PARTY_CONTACTS.LAST_UPDATED_BY%TYPE;
l_last_update_login OKC_REP_PARTY_CONTACTS.LAST_UPDATE_LOGIN%TYPE;
l_last_update_date OKC_REP_PARTY_CONTACTS.LAST_UPDATE_DATE%TYPE;
SELECT *
FROM OKC_REP_PARTY_CONTACTS
WHERE contract_id = p_source_contract_id;
l_last_update_date := l_creation_date;
l_last_updated_by := l_created_by;
l_last_update_login := Fnd_Global.Login_Id;
INSERT INTO OKC_REP_PARTY_CONTACTS (
CONTRACT_ID,
PARTY_ID,
PARTY_ROLE_CODE,
CONTACT_ID,
CONTACT_ROLE_ID,
OBJECT_VERSION_NUMBER,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
SIGNATURE_SEQUENCE,
ESIGNATURE_TYPE)
VALUES(
p_target_contract_id,
contact_rec.PARTY_ID,
contact_rec.PARTY_ROLE_CODE,
contact_rec.CONTACT_ID,
contact_rec.CONTACT_ROLE_ID,
1,
l_created_by,
l_creation_date,
l_last_updated_by,
l_last_update_date,
l_last_update_login,
contact_rec.SIGNATURE_SEQUENCE,
contact_rec.ESIGNATURE_TYPE);
l_last_updated_by OKC_REP_CONTRACT_PARTIES.LAST_UPDATED_BY%TYPE;
l_last_update_login OKC_REP_CONTRACT_PARTIES.LAST_UPDATE_LOGIN%TYPE;
l_last_update_date OKC_REP_CONTRACT_PARTIES.LAST_UPDATE_DATE%TYPE;
SELECT *
FROM OKC_REP_CONTRACT_PARTIES
WHERE contract_id = p_source_contract_id;
l_last_update_date := l_creation_date;
l_last_updated_by := l_created_by;
l_last_update_login := Fnd_Global.Login_Id;
INSERT INTO OKC_REP_CONTRACT_PARTIES (
CONTRACT_ID,
PARTY_ID,
PARTY_ROLE_CODE,
PARTY_LOCATION_ID,
OBJECT_VERSION_NUMBER,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN)
VALUES(
p_target_contract_id,
party_rec.PARTY_ID,
party_rec.PARTY_ROLE_CODE,
party_rec.PARTY_LOCATION_ID,
1,
l_created_by,
l_creation_date,
l_last_updated_by,
l_last_update_date,
l_last_update_login);
l_last_updated_by OKC_CONTRACT_RISKS.LAST_UPDATED_BY%TYPE;
l_last_update_login OKC_CONTRACT_RISKS.LAST_UPDATE_LOGIN%TYPE;
l_last_update_date OKC_CONTRACT_RISKS.LAST_UPDATE_DATE%TYPE;
SELECT *
FROM OKC_CONTRACT_RISKS
WHERE business_document_type = doc_type
AND business_document_id = doc_id;
SELECT contract_type, contract_version_num
FROM OKC_REP_CONTRACTS_ALL
WHERE contract_id = p_source_contract_id;
l_last_update_date := l_creation_date;
l_last_updated_by := l_created_by;
l_last_update_login := Fnd_Global.Login_Id;
INSERT INTO OKC_CONTRACT_RISKS (
-- CONTRACT_RISK_ID,
BUSINESS_DOCUMENT_TYPE,
BUSINESS_DOCUMENT_ID,
BUSINESS_DOCUMENT_VERSION,
RISK_EVENT_ID,
PROBABILITY_CODE,
IMPACT_CODE,
COMMENTS,
RISK_OCCURRED_FLAG,
OCCURRENCE_DATE,
OBJECT_VERSION_NUMBER,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN)
VALUES(
-- l_contract_risk_id,
--risk_rec.CONTRACT_ID,
contract_rec.contract_type,
p_target_contract_id,
risk_rec.BUSINESS_DOCUMENT_VERSION,
risk_rec.RISK_EVENT_ID,
risk_rec.PROBABILITY_CODE,
risk_rec.IMPACT_CODE,
risk_rec.COMMENTS,
risk_rec.RISK_OCCURRED_FLAG,
risk_rec.OCCURRENCE_DATE,
1,
l_created_by,
l_creation_date,
l_last_updated_by,
l_last_update_date,
l_last_update_login);
SELECT
fgrant.grantee_type grantee_type,
fgrant.grantee_key grantee_key,
fgrant.instance_type instance_type,
fgrant.instance_set_id instance_set_id,
fmenu.menu_name menu_name,
fgrant.program_name program_name,
fgrant.program_tag program_tag,
fgrant.parameter1 parameter1,
fgrant.parameter2 parameter2,
fgrant.parameter3 parameter3
FROM FND_GRANTS fgrant, FND_OBJECTS fobj, FND_MENUS fmenu
WHERE fgrant.menu_id = fmenu.menu_id
AND fgrant.object_id = fobj.object_id
AND fobj.obj_name = 'OKC_REP_CONTRACT'
AND fgrant.instance_pk1_value = to_char(p_source_contract_id);
p_menu_name => acl_rec.menu_name, -- Menu to be deleted.
p_object_name => G_REP_CONTRACT,
p_instance_type => acl_rec.instance_type, -- INSTANCE or SET
p_instance_set_id => acl_rec.instance_set_id, -- Instance set id.
p_instance_pk1_value => to_char(p_target_contract_id), -- Object PK Value
p_grantee_type => acl_rec.grantee_type, -- USER or GROUP
p_grantee_key => acl_rec.grantee_key, -- user_id or group_id
p_start_date => sysdate,
p_end_date => null,
p_program_name => acl_rec.program_name, -- name of the program that handles grant.
p_program_tag => acl_rec.program_tag, -- tag used by the program that handles grant.
p_parameter1 => acl_rec.parameter1, -- resource type
p_parameter2 => acl_rec.parameter2, -- resource id
p_parameter3 => acl_rec.parameter3, -- access type
x_grant_guid => x_grant_guid,
x_success => x_success, -- return param. 'T' or 'F'
x_errorcode => x_errorcode );
SELECT contract_type, owner_id
FROM OKC_REP_CONTRACTS_ALL
WHERE contract_id = p_source_contract_id;
SELECT party_id
FROM OKC_REP_CONTRACT_PARTIES
WHERE contract_id = p_source_contract_id
AND party_role_code = 'INTERNAL_ORG';
l_last_updated_by OKC_REP_CONTRACT_USAGES.LAST_UPDATED_BY%TYPE;
l_last_update_login OKC_REP_CONTRACT_USAGES.LAST_UPDATE_LOGIN%TYPE;
l_last_update_date OKC_REP_CONTRACT_USAGES.LAST_UPDATE_DATE%TYPE;
SELECT *
FROM OKC_REP_CONTRACT_USAGES
WHERE contract_type = doc_type
AND contract_id = doc_id
AND contract_version = doc_ver;
SELECT contract_type, contract_version_num
FROM OKC_REP_CONTRACTS_ALL
WHERE contract_id = p_contract_id;
l_last_update_date := l_creation_date;
l_last_updated_by := l_created_by;
l_last_update_login := Fnd_Global.Login_Id;
INSERT INTO okc_rep_contract_usages ( sequence_id,
contract_type,
contract_id,
contract_version,
business_document_type,
business_document_id,
business_document_version,
document_number,
PK1_VALUE,
PK2_VALUE,
PK3_VALUE,
PK4_VALUE,
PK5_VALUE,
source_code,
relationship_id,
display_in_contract,
object_version_number,
last_update_login,
creation_date,
created_by,
last_updated_by,
last_update_date
)
VALUES (okc_rep_contract_usages_s.NEXTVAL,
contract_rec.contract_type,
p_contract_id,
contract_rec.contract_version_num +1 ,
usages_rec.business_document_type,
usages_rec.business_document_id,
usages_rec.business_document_version,
usages_rec.document_number,
usages_rec.pk1_value,
usages_rec.pk2_value,
usages_rec.pk3_value,
usages_rec.pk4_value,
usages_rec.pk5_value,
-- if the previous version contract relationships data is migrated from okc_rep_contrcat_rels to okc_rep_contract_usages
-- then, source_code value is 'MIGRATION', we should not copy as MIGRATION, copying it as INTERNAL
decode(usages_rec.source_code, 'MIGRATION', 'INTERNAL', usages_rec.source_code),
usages_rec.relationship_id,
usages_rec.display_in_contract,
1.0,
l_last_update_login,
l_creation_date,
l_created_by,
l_last_updated_by,
l_last_update_date
);
SELECT contract_type, contract_version_num, contract_status_code
FROM OKC_REP_CONTRACTS_ALL
WHERE contract_id = p_contract_id;
SELECT 'Y' FROM DUAL
WHERE EXISTS (SELECT 'Y'
FROM okc_template_usages
WHERE DOCUMENT_ID = p_contract_id
AND DOCUMENT_TYPE= contract_rec.contract_type);
INSERT INTO OKC_REP_CONTRACT_VERS(
CONTRACT_ID,
CONTRACT_VERSION_NUM,
CONTRACT_NUMBER,
CONTRACT_TYPE,
CONTRACT_STATUS_CODE,
ORG_ID,
OWNER_ID,
SOURCE_LANGUAGE,
CONTRACT_NAME,
CONTRACT_DESC,
VERSION_COMMENTS,
AUTHORING_PARTY_CODE,
CONTRACT_EFFECTIVE_DATE,
CONTRACT_EXPIRATION_DATE,
CURRENCY_CODE,
AMOUNT,
OVERALL_RISK_CODE,
CANCELLATION_COMMENTS,
CANCELLATION_DATE,
TERMINATION_COMMENTS,
TERMINATION_DATE,
KEYWORDS,
PHYSICAL_LOCATION,
EXPIRE_NTF_FLAG,
EXPIRE_NTF_PERIOD,
NOTIFY_CONTACT_ROLE_ID,
WF_EXP_NTF_ITEM_KEY,
USE_ACL_FLAG,
WF_ITEM_TYPE,
WF_ITEM_KEY,
PROGRAM_ID,
PROGRAM_LOGIN_ID,
PROGRAM_APPLICATION_ID,
REQUEST_ID,
LATEST_SIGNED_VER_NUMBER,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
OBJECT_VERSION_NUMBER,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE,
CONTRACT_LAST_UPDATE_DATE,
CONTRACT_LAST_UPDATED_BY,
REFERENCE_DOCUMENT_TYPE,
REFERENCE_DOCUMENT_NUMBER,
ORIG_SYSTEM_REFERENCE_CODE,
ORIG_SYSTEM_REFERENCE_ID1,
ORIG_SYSTEM_REFERENCE_ID2,
ESIGNATURE_REQUIRED,
UDA_TEMPLATE_ID,
SBCR_COORDINATION_TYPE)
SELECT
CONTRACT_ID,
CONTRACT_VERSION_NUM,
CONTRACT_NUMBER,
CONTRACT_TYPE,
CONTRACT_STATUS_CODE,
ORG_ID,
OWNER_ID,
SOURCE_LANGUAGE,
CONTRACT_NAME,
CONTRACT_DESC,
VERSION_COMMENTS,
AUTHORING_PARTY_CODE,
CONTRACT_EFFECTIVE_DATE,
CONTRACT_EXPIRATION_DATE,
CURRENCY_CODE,
AMOUNT,
OVERALL_RISK_CODE,
CANCELLATION_COMMENTS,
CANCELLATION_DATE,
TERMINATION_COMMENTS,
TERMINATION_DATE,
KEYWORDS,
PHYSICAL_LOCATION,
EXPIRE_NTF_FLAG,
EXPIRE_NTF_PERIOD,
NOTIFY_CONTACT_ROLE_ID,
WF_EXP_NTF_ITEM_KEY,
USE_ACL_FLAG,
WF_ITEM_TYPE,
WF_ITEM_KEY,
PROGRAM_ID,
PROGRAM_LOGIN_ID,
PROGRAM_APPLICATION_ID,
REQUEST_ID,
LATEST_SIGNED_VER_NUMBER,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
OBJECT_VERSION_NUMBER,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE,
CONTRACT_LAST_UPDATE_DATE,
CONTRACT_LAST_UPDATED_BY,
REFERENCE_DOCUMENT_TYPE,
REFERENCE_DOCUMENT_NUMBER,
ORIG_SYSTEM_REFERENCE_CODE,
ORIG_SYSTEM_REFERENCE_ID1,
ORIG_SYSTEM_REFERENCE_ID2,
ESIGNATURE_REQUIRED,
UDA_TEMPLATE_ID,
SBCR_COORDINATION_TYPE
FROM OKC_REP_CONTRACTS_ALL
WHERE contract_id = p_contract_id;
'Delete contract header record.');
DELETE FROM OKC_REP_CONTRACTS_ALL
WHERE contract_id=p_contract_id;
l_update_event_tbl EVENT_TBL_TYPE;
SELECT contract_type, contract_version_num, latest_signed_ver_number, contract_effective_date, contract_expiration_date
FROM OKC_REP_CONTRACTS_ALL
WHERE contract_id = p_contract_id;
SELECT contract_effective_date, contract_expiration_date
FROM OKC_REP_CONTRACT_VERS
WHERE contract_id = p_contract_id
AND contract_version_num = l_contract_version;
'Before checking if we need to call updateDeliverable and disableDeliverable()');
l_update_event_tbl(1).event_code := G_CONTRACT_EFFECTIVE_EVENT;
l_update_event_tbl(1).event_date := contract_rec.contract_effective_date;
l_update_event_tbl(2).event_code := G_CONTRACT_EXPIRE_EVENT;
l_update_event_tbl(2).event_date := contract_rec.contract_expiration_date;
'Calling OKC_MANAGE_DELIVERABLES_GRP.updateDeliverables');
OKC_MANAGE_DELIVERABLES_GRP.updateDeliverables (
p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_bus_doc_id => p_contract_id,
p_bus_doc_type => contract_rec.contract_type,
p_bus_doc_version => contract_rec.contract_version_num,
p_bus_doc_date_events_tbl => l_update_event_tbl,
x_msg_data => x_msg_data,
x_msg_count => x_msg_count,
x_return_status => x_return_status);
'OKC_DELIVERABLE_PROCESS_PVT.updateDeliverables return status is : '
|| x_return_status);
UPDATE okc_rep_contracts_all
SET latest_signed_ver_number = contract_rec.contract_version_num
WHERE contract_id = p_contract_id;
l_update_event_tbl EVENT_TBL_TYPE;
SELECT contract_type,contract_name, contract_version_num, latest_signed_ver_number, contract_effective_date, contract_expiration_date
FROM OKC_REP_CONTRACTS_ALL
WHERE contract_id = p_contract_id;
SELECT contract_effective_date, contract_expiration_date
FROM OKC_REP_CONTRACT_VERS
WHERE contract_id = p_contract_id
AND contract_version_num = l_contract_version;
SELECT signature_workflow_name FROM okc_bus_doc_types_b WHERE document_type= p_contract_type;
SELECT OKC_REP_WF_SIGN_S.nextval INTO l_wf_sequence FROM dual;
/*UPDATE OKC_REP_CONTRACTS_ALL
SET wf_item_type = G_APPROVAL_ITEM_TYPE, wf_item_key = l_wf_sequence
WHERE contract_id=p_contract_id;
l_update_event_tbl EVENT_TBL_TYPE;
SELECT contract_type, contract_version_num, latest_signed_ver_number, contract_effective_date,
contract_expiration_date, termination_date
FROM OKC_REP_CONTRACTS_ALL
WHERE contract_id = p_contract_id;
l_update_event_tbl(1).event_code := G_CONTRACT_TERMINATED_EVENT;
l_update_event_tbl(1).event_date := p_termination_date;
'Calling OKC_MANAGE_DELIVERABLES_GRP.updateDeliverables ');
OKC_MANAGE_DELIVERABLES_GRP.updateDeliverables (
p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_bus_doc_id => p_contract_id,
p_bus_doc_type => contract_rec.contract_type,
p_bus_doc_version => contract_rec.contract_version_num,
p_bus_doc_date_events_tbl => l_update_event_tbl,
x_msg_data => x_msg_data,
x_msg_count => x_msg_count,
x_return_status => x_return_status);
'OKC_DELIVERABLE_PROCESS_PVT.updateDeliverables return status is : '
|| x_return_status);
SELECT contact_id
FROM okc_rep_party_contacts
WHERE contract_id = c_contract_id
AND party_role_code = G_PARTY_TYPE_INTERNAL
AND contact_role_id = p_notify_contact_role_id;
SELECT email_address
FROM per_all_people_f
WHERE person_id = c_contact_id
AND effective_start_date = (SELECT MAX(effective_start_date)
FROM per_all_people_f
WHERE person_id = c_contact_id);
SELECT NAME FROM okc_rep_contact_roles_vl
WHERE CONTACT_ROLE_ID = p_notify_contact_role_id;
SELECT TO_CHAR(okc_wf_notify_s1.NEXTVAL) INTO l_item_key FROM DUAL;
UPDATE okc_rep_contracts_all c
SET c.wf_exp_ntf_item_key = l_item_key
WHERE c.contract_id = p_contract_id
AND c.contract_version_num = p_contract_version;
UPDATE okc_rep_contract_vers c
SET c.wf_exp_ntf_item_key = l_item_key
WHERE c.contract_id = p_contract_id
AND c.contract_version_num = p_contract_version;
SELECT wf_item_type, wf_item_key, contract_status_code, contract_number
FROM okc_rep_contracts_all
WHERE contract_id = p_contract_id;
SELECT ITEM_KEY FROM wf_notifications WHERE ITEM_KEY LIKE Concat(chld_item_key,'\_%') ESCAPE '\'
AND STATUS='OPEN';
SELECT OI2.ORG_INFORMATION3 SET_OF_BOOKS_ID
FROM HR_ORGANIZATION_INFORMATION OI1,
HR_ORGANIZATION_INFORMATION OI2,
HR_ALL_ORGANIZATION_UNITS OU
WHERE OI1.ORGANIZATION_ID = OU.ORGANIZATION_ID AND
OI2.ORGANIZATION_ID = OU.ORGANIZATION_ID AND
OI1.ORG_INFORMATION_CONTEXT = 'CLASS' AND
OI2.ORG_INFORMATION_CONTEXT = 'Operating Unit Information' AND
OI1.ORG_INFORMATION1 = 'OPERATING_UNIT'AND
OI1.ORGANIZATION_ID = p_org_id;
SELECT CONTRACT_NUMBER
FROM OKC_REP_CONTRACTS_ALL
WHERE CONTRACT_NUMBER = p_contract_number
AND ROWNUM < 2;
PROCEDURE delete_uda_attribute_values(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_commit IN VARCHAR2,
p_contract_id IN NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2)
IS
l_api_name VARCHAR2(30);
'Entered OKC_REP_CONTRACT_PROCESS_PVT.delete_uda_attribute_values');
l_api_name := 'delete_uda_attribute_values';
SAVEPOINT delete_uda_attr_values_PVT;
select contract_version_num into l_con_version
from okc_rep_contracts_all
where CONTRACT_ID = p_contract_id ;
DELETE FROM OKC_REP_CONTRACTS_ALL_EXT_B
WHERE CONTRACT_ID = p_contract_id
AND contract_version_num = l_con_version;
DELETE FROM OKC_REP_CONTRACTS_ALL_EXT_TL
WHERE CONTRACT_ID = p_contract_id
AND contract_version_num = l_con_version;
INSERT INTO OKC_REP_CONTRACTS_ALL_EXT_B(extension_id,
ATTR_GROUP_ID,
contract_id,
DATA_LEVEL_ID,
UDA_TEMPLATE_ID,
PK1_VALUE,
PK2_VALUE,
PK3_VALUE,
PK4_VALUE,
PK5_VALUE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATED_BY,
CREATION_DATE,
C_EXT_ATTR1,
C_EXT_ATTR2,
C_EXT_ATTR3,
C_EXT_ATTR4,
C_EXT_ATTR5,
C_EXT_ATTR6,
C_EXT_ATTR7,
C_EXT_ATTR8,
C_EXT_ATTR9,
C_EXT_ATTR10,
C_EXT_ATTR11,
C_EXT_ATTR12,
C_EXT_ATTR13,
C_EXT_ATTR14,
C_EXT_ATTR15,
C_EXT_ATTR16,
C_EXT_ATTR17,
C_EXT_ATTR18,
C_EXT_ATTR19,
C_EXT_ATTR20,
C_EXT_ATTR21,
C_EXT_ATTR22,
C_EXT_ATTR23,
C_EXT_ATTR24,
C_EXT_ATTR25,
C_EXT_ATTR26,
C_EXT_ATTR27,
C_EXT_ATTR28,
C_EXT_ATTR29,
C_EXT_ATTR30,
C_EXT_ATTR31,
C_EXT_ATTR32,
C_EXT_ATTR33,
C_EXT_ATTR34,
C_EXT_ATTR35,
C_EXT_ATTR36,
C_EXT_ATTR37,
C_EXT_ATTR38,
C_EXT_ATTR39,
C_EXT_ATTR40,
N_EXT_ATTR1,
N_EXT_ATTR2,
N_EXT_ATTR3,
N_EXT_ATTR4,
N_EXT_ATTR5,
N_EXT_ATTR6,
N_EXT_ATTR7,
N_EXT_ATTR8,
N_EXT_ATTR9,
N_EXT_ATTR10,
N_EXT_ATTR11,
N_EXT_ATTR12,
N_EXT_ATTR13,
N_EXT_ATTR14,
N_EXT_ATTR15,
N_EXT_ATTR16,
N_EXT_ATTR17,
N_EXT_ATTR18,
N_EXT_ATTR19,
N_EXT_ATTR20,
UOM_EXT_ATTR1,
UOM_EXT_ATTR2,
UOM_EXT_ATTR3,
UOM_EXT_ATTR4,
UOM_EXT_ATTR5,
UOM_EXT_ATTR6,
UOM_EXT_ATTR7,
UOM_EXT_ATTR8,
UOM_EXT_ATTR9,
UOM_EXT_ATTR10,
UOM_EXT_ATTR11,
UOM_EXT_ATTR12,
UOM_EXT_ATTR13,
UOM_EXT_ATTR14,
UOM_EXT_ATTR15,
UOM_EXT_ATTR16,
UOM_EXT_ATTR17,
UOM_EXT_ATTR18,
UOM_EXT_ATTR19,
UOM_EXT_ATTR20,
D_EXT_ATTR1,
D_EXT_ATTR2,
D_EXT_ATTR3,
D_EXT_ATTR4,
D_EXT_ATTR5,
D_EXT_ATTR6,
D_EXT_ATTR7,
D_EXT_ATTR8,
D_EXT_ATTR9,
D_EXT_ATTR10)
SELECT EXTENSION_ID,
ATTR_GROUP_ID,
CONTRACT_ID,
DATA_LEVEL_ID,
UDA_TEMPLATE_ID,
PK1_VALUE,
PK2_VALUE,
PK3_VALUE,
PK4_VALUE,
PK5_VALUE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATED_BY,
CREATION_DATE,
C_EXT_ATTR1,
C_EXT_ATTR2,
C_EXT_ATTR3,
C_EXT_ATTR4,
C_EXT_ATTR5,
C_EXT_ATTR6,
C_EXT_ATTR7,
C_EXT_ATTR8,
C_EXT_ATTR9,
C_EXT_ATTR10,
C_EXT_ATTR11,
C_EXT_ATTR12,
C_EXT_ATTR13,
C_EXT_ATTR14,
C_EXT_ATTR15,
C_EXT_ATTR16,
C_EXT_ATTR17,
C_EXT_ATTR18,
C_EXT_ATTR19,
C_EXT_ATTR20,
C_EXT_ATTR21,
C_EXT_ATTR22,
C_EXT_ATTR23,
C_EXT_ATTR24,
C_EXT_ATTR25,
C_EXT_ATTR26,
C_EXT_ATTR27,
C_EXT_ATTR28,
C_EXT_ATTR29,
C_EXT_ATTR30,
C_EXT_ATTR31,
C_EXT_ATTR32,
C_EXT_ATTR33,
C_EXT_ATTR34,
C_EXT_ATTR35,
C_EXT_ATTR36,
C_EXT_ATTR37,
C_EXT_ATTR38,
C_EXT_ATTR39,
C_EXT_ATTR40,
N_EXT_ATTR1,
N_EXT_ATTR2,
N_EXT_ATTR3,
N_EXT_ATTR4,
N_EXT_ATTR5,
N_EXT_ATTR6,
N_EXT_ATTR7,
N_EXT_ATTR8,
N_EXT_ATTR9,
N_EXT_ATTR10,
N_EXT_ATTR11,
N_EXT_ATTR12,
N_EXT_ATTR13,
N_EXT_ATTR14,
N_EXT_ATTR15,
N_EXT_ATTR16,
N_EXT_ATTR17,
N_EXT_ATTR18,
N_EXT_ATTR19,
N_EXT_ATTR20,
UOM_EXT_ATTR1,
UOM_EXT_ATTR2,
UOM_EXT_ATTR3,
UOM_EXT_ATTR4,
UOM_EXT_ATTR5,
UOM_EXT_ATTR6,
UOM_EXT_ATTR7,
UOM_EXT_ATTR8,
UOM_EXT_ATTR9,
UOM_EXT_ATTR10,
UOM_EXT_ATTR11,
UOM_EXT_ATTR12,
UOM_EXT_ATTR13,
UOM_EXT_ATTR14,
UOM_EXT_ATTR15,
UOM_EXT_ATTR16,
UOM_EXT_ATTR17,
UOM_EXT_ATTR18,
UOM_EXT_ATTR19,
UOM_EXT_ATTR20,
D_EXT_ATTR1,
D_EXT_ATTR2,
D_EXT_ATTR3,
D_EXT_ATTR4,
D_EXT_ATTR5,
D_EXT_ATTR6,
D_EXT_ATTR7,
D_EXT_ATTR8,
D_EXT_ATTR9,
D_EXT_ATTR10
FROM okc_rep_contract_vers_ext_b
WHERE contract_id = p_contract_id
AND contract_version_num = l_con_version - 1 ;
INSERT INTO OKC_REP_CONTRACTS_ALL_EXT_TL(EXTENSION_ID
,ATTR_GROUP_ID
,CONTRACT_ID
,DATA_LEVEL_ID
,UDA_TEMPLATE_ID
,SOURCE_LANG
,LANGUAGE
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,CREATED_BY
,CREATION_DATE
,TL_EXT_ATTR1
,TL_EXT_ATTR2
,TL_EXT_ATTR3
,TL_EXT_ATTR4
,TL_EXT_ATTR5
,TL_EXT_ATTR6
,TL_EXT_ATTR7
,TL_EXT_ATTR8
,TL_EXT_ATTR9
,TL_EXT_ATTR10
,TL_EXT_ATTR11
,TL_EXT_ATTR12
,TL_EXT_ATTR13
,TL_EXT_ATTR14
,TL_EXT_ATTR15
,TL_EXT_ATTR16
,TL_EXT_ATTR17
,TL_EXT_ATTR18
,TL_EXT_ATTR19
,TL_EXT_ATTR20
,TL_EXT_ATTR21
,TL_EXT_ATTR22
,TL_EXT_ATTR23
,TL_EXT_ATTR24
,TL_EXT_ATTR25
,TL_EXT_ATTR26
,TL_EXT_ATTR27
,TL_EXT_ATTR28
,TL_EXT_ATTR29
,TL_EXT_ATTR30
,TL_EXT_ATTR31
,TL_EXT_ATTR32
,TL_EXT_ATTR33
,TL_EXT_ATTR34
,TL_EXT_ATTR35
,TL_EXT_ATTR36
,TL_EXT_ATTR37
,TL_EXT_ATTR38
,TL_EXT_ATTR39
,TL_EXT_ATTR40)
SELECT EXTENSION_ID
,ATTR_GROUP_ID
,CONTRACT_ID
,DATA_LEVEL_ID
,UDA_TEMPLATE_ID
,SOURCE_LANG
,LANGUAGE
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,CREATED_BY
,CREATION_DATE
,TL_EXT_ATTR1
,TL_EXT_ATTR2
,TL_EXT_ATTR3
,TL_EXT_ATTR4
,TL_EXT_ATTR5
,TL_EXT_ATTR6
,TL_EXT_ATTR7
,TL_EXT_ATTR8
,TL_EXT_ATTR9
,TL_EXT_ATTR10
,TL_EXT_ATTR11
,TL_EXT_ATTR12
,TL_EXT_ATTR13
,TL_EXT_ATTR14
,TL_EXT_ATTR15
,TL_EXT_ATTR16
,TL_EXT_ATTR17
,TL_EXT_ATTR18
,TL_EXT_ATTR19
,TL_EXT_ATTR20
,TL_EXT_ATTR21
,TL_EXT_ATTR22
,TL_EXT_ATTR23
,TL_EXT_ATTR24
,TL_EXT_ATTR25
,TL_EXT_ATTR26
,TL_EXT_ATTR27
,TL_EXT_ATTR28
,TL_EXT_ATTR29
,TL_EXT_ATTR30
,TL_EXT_ATTR31
,TL_EXT_ATTR32
,TL_EXT_ATTR33
,TL_EXT_ATTR34
,TL_EXT_ATTR35
,TL_EXT_ATTR36
,TL_EXT_ATTR37
,TL_EXT_ATTR38
,TL_EXT_ATTR39
,TL_EXT_ATTR40
FROM okc_rep_contract_vers_ext_tl
WHERE contract_id = p_contract_id
AND contract_version_num = l_con_version - 1 ;
DELETE FROM OKC_REP_CONTRACT_VERS_EXT_B
WHERE CONTRACT_ID = p_contract_id
AND contract_version_num = l_con_version - 1 ;
DELETE FROM OKC_REP_CONTRACT_VERS_EXT_TL
WHERE CONTRACT_ID = p_contract_id
AND contract_version_num = l_con_version - 1 ;
'Leaving OKC_REP_CONTRACT_PROCESS_PVT.delete_uda_attribute_values');
'Leaving delete_uda_attribute_values:FND_API.G_EXC_ERROR Exception');
ROLLBACK TO delete_uda_attr_values_PVT;
'Leaving delete_uda_attribute_values:FND_API.G_EXC_UNEXPECTED_ERROR Exception');
ROLLBACK TO delete_uda_attr_values_PVT;
'Leaving delete_uda_attribute_values because of EXCEPTION: ' || sqlerrm);
ROLLBACK TO delete_uda_attr_values_PVT;
END delete_uda_attribute_values;
SELECT contract_type, contract_version_num
FROM okc_rep_contracts_all
WHERE contract_id = p_contract_id;
SELECT *
INTO l_object
FROM FND_OBJECTS
WHERE OBJ_NAME = 'OKC_REP_CONTRACTS_UDA';
SELECT *
INTO l_main_data_level
FROM EGO_DATA_LEVEL_B
WHERE ATTR_GROUP_TYPE = 'OKC_REP_CONTRACT_HDR_EXT_ATTRS'
AND DATA_LEVEL_NAME LIKE 'OKC_HDR';
SELECT extension_id FROM okc_rep_contracts_all_ext_b
WHERE contract_id = p_contract_id;
SELECT EGO_EXTFWK_S.NEXTVAL INTO l_extension_id_vers FROM dual;
INSERT INTO okc_rep_contracts_all_ext_b(EXTENSION_ID,
ATTR_GROUP_ID,
CONTRACT_TYPE,
CONTRACT_ID,
CONTRACT_VERSION_NUM,
DATA_LEVEL_ID,
UDA_TEMPLATE_ID,
PK1_VALUE,
PK2_VALUE,
PK3_VALUE,
PK4_VALUE,
PK5_VALUE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATED_BY,
CREATION_DATE,
C_EXT_ATTR1,
C_EXT_ATTR2,
C_EXT_ATTR3,
C_EXT_ATTR4,
C_EXT_ATTR5,
C_EXT_ATTR6,
C_EXT_ATTR7,
C_EXT_ATTR8,
C_EXT_ATTR9,
C_EXT_ATTR10,
C_EXT_ATTR11,
C_EXT_ATTR12,
C_EXT_ATTR13,
C_EXT_ATTR14,
C_EXT_ATTR15,
C_EXT_ATTR16,
C_EXT_ATTR17,
C_EXT_ATTR18,
C_EXT_ATTR19,
C_EXT_ATTR20,
C_EXT_ATTR21,
C_EXT_ATTR22,
C_EXT_ATTR23,
C_EXT_ATTR24,
C_EXT_ATTR25,
C_EXT_ATTR26,
C_EXT_ATTR27,
C_EXT_ATTR28,
C_EXT_ATTR29,
C_EXT_ATTR30,
C_EXT_ATTR31,
C_EXT_ATTR32,
C_EXT_ATTR33,
C_EXT_ATTR34,
C_EXT_ATTR35,
C_EXT_ATTR36,
C_EXT_ATTR37,
C_EXT_ATTR38,
C_EXT_ATTR39,
C_EXT_ATTR40,
N_EXT_ATTR1,
N_EXT_ATTR2,
N_EXT_ATTR3,
N_EXT_ATTR4,
N_EXT_ATTR5,
N_EXT_ATTR6,
N_EXT_ATTR7,
N_EXT_ATTR8,
N_EXT_ATTR9,
N_EXT_ATTR10,
N_EXT_ATTR11,
N_EXT_ATTR12,
N_EXT_ATTR13,
N_EXT_ATTR14,
N_EXT_ATTR15,
N_EXT_ATTR16,
N_EXT_ATTR17,
N_EXT_ATTR18,
N_EXT_ATTR19,
N_EXT_ATTR20,
UOM_EXT_ATTR1,
UOM_EXT_ATTR2,
UOM_EXT_ATTR3,
UOM_EXT_ATTR4,
UOM_EXT_ATTR5,
UOM_EXT_ATTR6,
UOM_EXT_ATTR7,
UOM_EXT_ATTR8,
UOM_EXT_ATTR9,
UOM_EXT_ATTR10,
UOM_EXT_ATTR11,
UOM_EXT_ATTR12 ,
UOM_EXT_ATTR13,
UOM_EXT_ATTR14,
UOM_EXT_ATTR15,
UOM_EXT_ATTR16,
UOM_EXT_ATTR17,
UOM_EXT_ATTR18,
UOM_EXT_ATTR19,
UOM_EXT_ATTR20,
D_EXT_ATTR1,
D_EXT_ATTR2,
D_EXT_ATTR3,
D_EXT_ATTR4,
D_EXT_ATTR5,
D_EXT_ATTR6,
D_EXT_ATTR7,
D_EXT_ATTR8,
D_EXT_ATTR9,
D_EXT_ATTR10)
SELECT l_extension_id_vers,
ATTR_GROUP_ID,
CONTRACT_TYPE,
contract_id,
p_contract_version+1,
DATA_LEVEL_ID,
UDA_TEMPLATE_ID,
PK1_VALUE,
PK2_VALUE,
PK3_VALUE,
PK4_VALUE,
PK5_VALUE,
sysdate,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATED_BY,
sysdate,
C_EXT_ATTR1,
C_EXT_ATTR2,
C_EXT_ATTR3,
C_EXT_ATTR4,
C_EXT_ATTR5,
C_EXT_ATTR6,
C_EXT_ATTR7,
C_EXT_ATTR8,
C_EXT_ATTR9,
C_EXT_ATTR10,
C_EXT_ATTR11,
C_EXT_ATTR12,
C_EXT_ATTR13,
C_EXT_ATTR14,
C_EXT_ATTR15,
C_EXT_ATTR16,
C_EXT_ATTR17,
C_EXT_ATTR18,
C_EXT_ATTR19,
C_EXT_ATTR20,
C_EXT_ATTR21,
C_EXT_ATTR22,
C_EXT_ATTR23,
C_EXT_ATTR24,
C_EXT_ATTR25,
C_EXT_ATTR26,
C_EXT_ATTR27,
C_EXT_ATTR28,
C_EXT_ATTR29,
C_EXT_ATTR30,
C_EXT_ATTR31,
C_EXT_ATTR32,
C_EXT_ATTR33,
C_EXT_ATTR34,
C_EXT_ATTR35,
C_EXT_ATTR36,
C_EXT_ATTR37,
C_EXT_ATTR38,
C_EXT_ATTR39,
C_EXT_ATTR40,
N_EXT_ATTR1,
N_EXT_ATTR2,
N_EXT_ATTR3,
N_EXT_ATTR4,
N_EXT_ATTR5,
N_EXT_ATTR6,
N_EXT_ATTR7,
N_EXT_ATTR8,
N_EXT_ATTR9,
N_EXT_ATTR10,
N_EXT_ATTR11,
N_EXT_ATTR12,
N_EXT_ATTR13,
N_EXT_ATTR14,
N_EXT_ATTR15,
N_EXT_ATTR16,
N_EXT_ATTR17,
N_EXT_ATTR18,
N_EXT_ATTR19,
N_EXT_ATTR20,
UOM_EXT_ATTR1,
UOM_EXT_ATTR2,
UOM_EXT_ATTR3,
UOM_EXT_ATTR4,
UOM_EXT_ATTR5,
UOM_EXT_ATTR6,
UOM_EXT_ATTR7,
UOM_EXT_ATTR8,
UOM_EXT_ATTR9,
UOM_EXT_ATTR10,
UOM_EXT_ATTR11,
UOM_EXT_ATTR12,
UOM_EXT_ATTR13,
UOM_EXT_ATTR14,
UOM_EXT_ATTR15,
UOM_EXT_ATTR16,
UOM_EXT_ATTR17,
UOM_EXT_ATTR18,
UOM_EXT_ATTR19,
UOM_EXT_ATTR20,
D_EXT_ATTR1,
D_EXT_ATTR2,
D_EXT_ATTR3,
D_EXT_ATTR4,
D_EXT_ATTR5,
D_EXT_ATTR6,
D_EXT_ATTR7,
D_EXT_ATTR8,
D_EXT_ATTR9,
D_EXT_ATTR10
FROM okc_rep_contracts_all_ext_b
WHERE contract_id = p_contract_id
AND extension_id = c_get_uda_attr_for_con_rec.extension_id;
INSERT INTO okc_rep_contracts_all_ext_tl(EXTENSION_ID
,ATTR_GROUP_ID
,CONTRACT_TYPE
,CONTRACT_ID
,CONTRACT_VERSION_NUM
,DATA_LEVEL_ID
,UDA_TEMPLATE_ID
,SOURCE_LANG
,LANGUAGE
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,CREATED_BY
,CREATION_DATE
,TL_EXT_ATTR1
,TL_EXT_ATTR2
,TL_EXT_ATTR3
,TL_EXT_ATTR4
,TL_EXT_ATTR5
,TL_EXT_ATTR6
,TL_EXT_ATTR7
,TL_EXT_ATTR8
,TL_EXT_ATTR9
,TL_EXT_ATTR10
,TL_EXT_ATTR11
,TL_EXT_ATTR12
,TL_EXT_ATTR13
,TL_EXT_ATTR14
,TL_EXT_ATTR15
,TL_EXT_ATTR16
,TL_EXT_ATTR17
,TL_EXT_ATTR18
,TL_EXT_ATTR19
,TL_EXT_ATTR20
,TL_EXT_ATTR21
,TL_EXT_ATTR22
,TL_EXT_ATTR23
,TL_EXT_ATTR24
,TL_EXT_ATTR25
,TL_EXT_ATTR26
,TL_EXT_ATTR27
,TL_EXT_ATTR28
,TL_EXT_ATTR29
,TL_EXT_ATTR30
,TL_EXT_ATTR31
,TL_EXT_ATTR32
,TL_EXT_ATTR33
,TL_EXT_ATTR34
,TL_EXT_ATTR35
,TL_EXT_ATTR36
,TL_EXT_ATTR37
,TL_EXT_ATTR38
,TL_EXT_ATTR39
,TL_EXT_ATTR40)
SELECT l_extension_id_vers
,ATTR_GROUP_ID
, CONTRACT_TYPE
,CONTRACT_ID
,p_contract_version+1
,DATA_LEVEL_ID
,UDA_TEMPLATE_ID
,SOURCE_LANG
,LANGUAGE
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,CREATED_BY
,CREATION_DATE
,TL_EXT_ATTR1
,TL_EXT_ATTR2
,TL_EXT_ATTR3
,TL_EXT_ATTR4
,TL_EXT_ATTR5
,TL_EXT_ATTR6
,TL_EXT_ATTR7
,TL_EXT_ATTR8
,TL_EXT_ATTR9
,TL_EXT_ATTR10
,TL_EXT_ATTR11
,TL_EXT_ATTR12
,TL_EXT_ATTR13
,TL_EXT_ATTR14
,TL_EXT_ATTR15
,TL_EXT_ATTR16
,TL_EXT_ATTR17
,TL_EXT_ATTR18
,TL_EXT_ATTR19
,TL_EXT_ATTR20
,TL_EXT_ATTR21
,TL_EXT_ATTR22
,TL_EXT_ATTR23
,TL_EXT_ATTR24
,TL_EXT_ATTR25
,TL_EXT_ATTR26
,TL_EXT_ATTR27
,TL_EXT_ATTR28
,TL_EXT_ATTR29
,TL_EXT_ATTR30
,TL_EXT_ATTR31
,TL_EXT_ATTR32
,TL_EXT_ATTR33
,TL_EXT_ATTR34
,TL_EXT_ATTR35
,TL_EXT_ATTR36
,TL_EXT_ATTR37
,TL_EXT_ATTR38
,TL_EXT_ATTR39
,TL_EXT_ATTR40
FROM okc_rep_contracts_all_ext_tl
WHERE contract_id = p_contract_id
AND extension_id = c_get_uda_attr_for_con_rec.extension_id;
SELECT layout_template_id INTO l_layout_template_id
FROM okc_bus_doc_types_b
WHERE document_type = p_contract_type;
SELECT template_id INTO l_terms_template_id
FROM okc_template_usages
WHERE document_id = p_contract_id AND document_type = p_contract_type;
SELECT PRINT_TEMPLATE_ID INTO l_print_template_id
FROM okc_terms_templates_all
WHERE template_id = l_terms_template_id;