[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT contract_number
FROM okc_rep_contracts_all
WHERE contract_id = p_contract_id;
SELECT OKC_REP_WF_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;
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);
'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_RELS
WHERE CONTRACT_ID = p_CONTRACT_ID;
'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
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_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_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_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);
-- 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,
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)
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,
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
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)
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);
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';
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,
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)
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,
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
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_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 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 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;