[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT contract_type, owner_id, contract_number
FROM OKC_REP_CONTRACTS_ALL
WHERE contract_id = p_target_contract_id;
SELECT party_id
FROM OKC_REP_CONTRACT_PARTIES
WHERE contract_id = p_target_contract_id
AND party_role_code = 'INTERNAL_ORG';
SELECT business_document_type, business_document_id
FROM okc_rep_contract_usages
WHERE contract_type = p_contract_type
AND contract_id = p_contract_id
AND contract_version = p_contract_version_num;
SELECT contract_version_num INTO x_new_version_num
FROM okc_rep_contracts_all
WHERE contract_id = p_contract_id
AND contract_type = p_contract_type;
SELECT orcu.business_document_type, orcu.business_document_id,
orca.contract_effective_date
FROM okc_rep_contract_usages orcu, okc_rep_contracts_all orca
WHERE orca.contract_id = p_contract_id
AND orca.contract_type = orcu.contract_type
AND orcu.contract_version = orca.contract_version_num
AND orca.contract_id = orcu.contract_id;
SELECT orcu.business_document_type, orcu.business_document_id,
orca.contract_effective_date
FROM okc_rep_contract_usages orcu, okc_rep_contracts_all orca
WHERE orca.contract_id = p_contract_id
AND orca.contract_type = orcu.contract_type
AND orcu.contract_version = orca.contract_version_num
AND orca.contract_id = orcu.contract_id;
l_update_event_tbl okc_rep_contract_process_pvt.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
AND contract_type = p_contract_type;
SELECT contract_type, contract_id FROM okc_rep_contract_usages
WHERE business_document_id = p_document_rec.business_document_id
AND business_document_type = p_document_rec.business_document_type;
SELECT DELIVERABLE_STATUS FROM okc_deliverables del, okc_rep_contracts_all orca
WHERE del.BUSINESS_DOCUMENT_TYPE = orca.contract_type
AND del.BUSINESS_DOCUMENT_ID = orca.contract_id
AND del.BUSINESS_DOCUMENT_VERSION = orca.contract_version_num
AND contract_id = l_contract_id
AND del.DELIVERABLE_STATUS <> 'COMPLETED'
AND ROWNUM = 1;
'Before checking if we need to call updateDeliverable and disableDeliverable()');
l_update_event_tbl(1).event_code := 'PO_PHYSICAL_COMPLETE';
l_update_event_tbl(1).event_date := p_physical_completion_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 => l_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_MANAGE_DELIVERABLES_GRP.updateDeliverables return status is : '
|| x_return_status);
UPDATE okc_rep_contracts_all
SET latest_signed_ver_number = contract_rec.contract_version_num ,
contract_effective_date = p_physical_completion_date
WHERE contract_id = l_contract_id;
select auto_num_enabled_yn INTO l_auto_num_doc
from okc_bus_doc_types_vl
where name = p_contract_rec.contract_type_txt;
Deletes the Contract for the given document type.
*/
Procedure delete_contract(p_api_version IN NUMBER,
p_document_rec IN OKC_IMP_RECORD_TYPES.document_rec_type,
p_contract_type IN VARCHAR2 DEFAULT NULL,
p_contract_id IN NUMBER DEFAULT NULL,
p_commit IN VARCHAR2 := fnd_api.g_false,
x_msg_data OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2
)
IS
l_api_name VARCHAR2(50);
l_api_name := 'delete_contract';
SAVEPOINT delete_contract_PVT;
OKC_REP_CONTRACT_IMP_PVT.delete_contract( p_api_version => p_api_version,
p_document_rec => p_document_rec,
p_contract_type => p_contract_type,
p_contract_id => p_contract_id,
p_commit => p_commit,
x_msg_data => x_msg_data,
x_msg_count => x_msg_count,
x_return_status => x_return_status);
'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;
Updates to the contract should be done from UI.
The relation of parent document which is obtained in p_document_rec is stored in the okc_rep_contract_usages table
*/
Procedure create_new_contract_version(p_api_version IN NUMBER,
p_document_rec IN OKC_IMP_RECORD_TYPES.document_rec_type,
p_contract_type IN VARCHAR2 DEFAULT NULL,
p_contract_id IN NUMBER DEFAULT NULL,
p_commit IN VARCHAR2 := fnd_api.g_false,
x_msg_data OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2
)
IS
l_api_name VARCHAR2(50);
SELECT okc_rep_import_run_id_s.NEXTVAL INTO l_run_id FROM dual;
SELECT *
FROM okc_deliverables
WHERE deliverable_id = l_deliverable_id;
SELECT 'N' INTO all_dels_completed
FROM okc_deliverables del
WHERE del.business_document_id = l_del_rec.business_document_id
AND del.business_document_type = l_del_rec.business_document_type
AND del.business_document_version = l_del_rec.business_document_version
AND del.deliverable_status NOT IN ('COMPLETED', 'CANCELLED', 'FAILED_TO_PERFORM')
AND ROWNUM = 1;
SELECT orcu.business_document_id INTO l_po_header_id
FROM okc_rep_contract_usages orcu, okc_rep_contracts_all orc
WHERE orcu.contract_id = l_del_rec.business_document_id
AND orcu.contract_type = l_del_rec.business_document_type
AND orc.contract_id = orcu.contract_id
AND orc.contract_type = orcu.contract_type
AND orc.contract_version_num = orcu.contract_version;
SELECT orcu.contract_type, orcu.contract_id,
orca.contract_effective_date
FROM okc_rep_contract_usages orcu, okc_rep_contracts_all orca
WHERE orcu.business_document_id = p_document_rec.business_document_id
and orcu.business_document_type = p_document_rec.business_document_type
AND orca.contract_type = orcu.contract_type
AND orcu.contract_version = orca.contract_version_num
AND orca.contract_id = orcu.contract_id;
SELECT orcu.contract_id
FROM okc_rep_contract_usages orcu, okc_rep_contracts_all orca
WHERE orcu.business_document_id = p_src_document_rec.business_document_id
and orcu.business_document_type = p_src_document_rec.business_document_type
AND orca.contract_type = orcu.contract_type
AND orcu.contract_version = orca.contract_version_num
AND orca.contract_id = orcu.contract_id;
SELECT orcu.contract_id
FROM okc_rep_contract_usages orcu, okc_rep_contracts_all orca
WHERE orcu.business_document_id = p_tgt_document_rec.business_document_id
and orcu.business_document_type = p_tgt_document_rec.business_document_type
AND orca.contract_type = orcu.contract_type
AND orcu.contract_version = orca.contract_version_num
AND orca.contract_id = orcu.contract_id;
select auto_num_enabled_yn INTO l_auto_num_doc
from okc_bus_doc_types_vl
where name = p_contract_rec.contract_type_txt;
UPDATE okc_deliverables set EXTERNAL_PARTY_ID =
(SELECT PARTY_ID
FROM okc_rep_contract_parties
WHERE contract_id = l_target_contract_id
AND PARTY_ROLE_CODE = 'SUPPLIER_ORG')
WHERE business_document_type = 'REP_CCC'
AND business_document_id = l_target_contract_id
AND DELIVERABLE_TYPE = 'CONTRACTUAL';
SELECT Count(deliverable_id)
FROM okc_rep_contract_usages orcu, okc_deliverables del
WHERE orcu.business_document_type = p_document_rec.business_document_type
AND orcu.business_document_id = p_document_rec.business_document_id
AND del.business_document_type = orcu.contract_type
AND del.business_document_id = orcu.contract_id
AND del.business_document_version = -99 ;