[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Updates the okc_contract_usages table with the relationships provided
*/
PROCEDURE Update_contract_usages(p_api_version IN NUMBER,
p_contract_rec IN OUT NOCOPY OKC_IMP_RECORD_TYPES.contract_rec_type,
p_document_rec IN OKC_IMP_RECORD_TYPES.document_rec_type,
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 := 'Update_contract_usages';
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,
run_id,
object_version_number,
last_update_login,
creation_date,
created_by,
last_updated_by,
last_update_date
)
VALUES (okc_rep_contract_usages_s.NEXTVAL,
p_contract_rec.contract_type,
p_contract_rec.contract_id,
p_contract_rec.contract_version_num,
p_document_rec.business_document_type,
p_document_rec.business_document_id,
p_document_rec.business_document_version,
p_document_rec.document_number,
p_document_rec.pk1_value,
p_document_rec.pk2_value,
p_document_rec.pk3_value,
p_document_rec.pk4_value,
p_document_rec.pk5_value,
p_document_rec.source_code,
p_document_rec.relationship_id,
p_document_rec.display_in_contract,
p_contract_rec.run_id,
1.0,
l_user_id,
SYSDATE,
l_user_id,
l_user_id,
SYSDATE
);
END Update_contract_usages;
Updates the okc_contract_usages table with the relationships provided
*/
PROCEDURE delete_contract_usages(p_api_version IN NUMBER,
p_contract_rec IN OUT NOCOPY OKC_IMP_RECORD_TYPES.contract_rec_type,
p_document_rec IN OKC_IMP_RECORD_TYPES.document_rec_type,
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_usages';
DELETE FROM okc_rep_contract_usages
WHERE contract_type = p_contract_rec.contract_type
AND contract_id = p_contract_rec.contract_id
AND contract_version = p_contract_rec.contract_version_num;
'Leaving delete_contract_usages because of EXCEPTION: ' || sqlerrm);
END delete_contract_usages;
l_number_inserted NUMBER;
SELECT okc_rep_import_run_id_s.NEXTVAL INTO l_run_id FROM dual;
OKC_REP_UTIL_PVT.validate_and_insert_contracts(p_api_version => p_api_version,
p_init_msg_list => fnd_api.g_FALSE,
p_run_id => l_run_id,
x_msg_data => x_msg_data,
x_msg_count => x_msg_count,
x_return_status => x_return_status,
x_number_inserted => l_number_inserted);
SELECT contract_type, contract_id, contract_version_num, run_id, valid_flag
INTO p_contract_rec.contract_type, p_contract_rec.contract_id,
p_contract_rec.contract_version_num, p_contract_rec.run_id, p_contract_rec.valid_flag
FROM okc_rep_imp_contracts_t
WHERE run_id = l_run_id;
SELECT error_message INTO x_msg_data
FROM okc_rep_imp_errors_t
WHERE run_id = p_contract_rec.run_id;
SELECT OKC_REP_IMP_CONTRACTS_T_S.NEXTVAL INTO x_imp_contract_id FROM dual;
INSERT INTO okc_rep_imp_contracts_t(run_id,
imp_contract_id,
contract_number ,
contract_name ,
contract_version_num ,
description ,
contract_type_txt ,
authoring_party_txt ,
contract_status_txt ,
version_comments ,
contract_effective_date ,
contract_expiration_date ,
currency_code ,
contract_amount ,
org_name ,
owner_user_name ,
keywords ,
physical_location ,
orig_system_reference_code ,
orig_system_reference_id1 ,
orig_system_reference_id2 ,
creation_date ,
latest_signed_ver_number ,
overall_risk_txt ,
cancellation_comments ,
cancellation_date ,
termination_comments ,
termination_date ,
expire_ntf_flag ,
expire_ntf_period ,
notify_contact_role_id ,
attribute_category ,
attribute1 ,
attribute2 ,
attribute3 ,
attribute4 ,
attribute5 ,
attribute6 ,
attribute7 ,
attribute8 ,
attribute9 ,
attribute10 ,
attribute11 ,
attribute12 ,
attribute13 ,
attribute14 ,
attribute15 ,
reference_document_type ,
reference_document_number,
reference_document_id)
VALUES(p_run_id,
x_imp_contract_id,
p_contract_rec.contract_number ,
p_contract_rec.contract_name ,
p_contract_rec.contract_version_num ,
p_contract_rec.description ,
p_contract_rec.contract_type_txt ,
p_contract_rec.authoring_party_txt ,
p_contract_rec.contract_status_txt ,
p_contract_rec.version_comments ,
p_contract_rec.contract_effective_date ,
p_contract_rec.contract_expiration_date ,
p_contract_rec.currency_code ,
p_contract_rec.contract_amount ,
p_contract_rec.org_name ,
p_contract_rec.owner_user_name ,
p_contract_rec.keywords ,
p_contract_rec.physical_location ,
p_contract_rec.orig_system_reference_code ,
p_contract_rec.orig_system_reference_id1 ,
p_contract_rec.orig_system_reference_id2 ,
p_contract_rec.creation_date ,
p_contract_rec.latest_signed_ver_number ,
p_contract_rec.overall_risk_txt ,
p_contract_rec.cancellation_comments ,
p_contract_rec.cancellation_date ,
p_contract_rec.termination_comments ,
p_contract_rec.termination_date ,
p_contract_rec.expire_ntf_flag ,
p_contract_rec.expire_ntf_period ,
p_contract_rec.notify_contact_role_id ,
p_contract_rec.attribute_category ,
p_contract_rec.attribute1 ,
p_contract_rec.attribute2 ,
p_contract_rec.attribute3 ,
p_contract_rec.attribute4 ,
p_contract_rec.attribute5 ,
p_contract_rec.attribute6 ,
p_contract_rec.attribute7 ,
p_contract_rec.attribute8 ,
p_contract_rec.attribute9 ,
p_contract_rec.attribute10 ,
p_contract_rec.attribute11 ,
p_contract_rec.attribute12 ,
p_contract_rec.attribute13 ,
p_contract_rec.attribute14 ,
p_contract_rec.attribute15 ,
p_contract_rec.reference_document_type ,
p_contract_rec.reference_document_number,
p_contract_rec.reference_document_id);
INSERT INTO okc_rep_imp_parties_t(imp_contract_id,
imp_party_id,
party_index,
signed_by_txt,
signed_date,
party_name_txt,
party_role_txt,
run_id)
VALUES(p_imp_contract_id,
OKC_REP_IMP_PARTIES_T_S.NEXTVAL,
p_contract_parties_tbl(i).party_index,
p_contract_parties_tbl(i).signed_by_txt,
p_contract_parties_tbl(i).signed_date,
p_contract_parties_tbl(i).party_name_txt,
p_contract_parties_tbl(i).party_role_txt,
p_run_id);
SELECT imp_party_id INTO l_party_id
FROM okc_rep_imp_parties_t
WHERE imp_contract_id = p_imp_contract_id
AND party_role_txt = p_party_contacts_tbl(i).party_role_txt
AND party_name_txt = p_party_contacts_tbl(i).party_name_txt;
INSERT INTO okc_rep_imp_contacts_t(imp_contract_id,
imp_party_id,
imp_contact_id,
contact_index,
contact_id,
party_role_txt,
party_name_txt,
contact_name,
contact_role_txt,
run_id)
VALUES (p_imp_contract_id,
l_party_id,
OKC_REP_IMP_CONTACTS_T_S.NEXTVAL,
p_party_contacts_tbl(i).contact_index,
p_party_contacts_tbl(i).contact_id,
p_party_contacts_tbl(i).party_role_txt,
p_party_contacts_tbl(i).party_name_txt,
p_party_contacts_tbl(i).contact_name_txt,
p_party_contacts_tbl(i).contact_role_txt,
p_run_id);
INSERT INTO okc_rep_imp_risks_t(imp_contract_id,
imp_risk_id,
risk_event_txt,
probability_txt,
risk_impact_txt,
risk_comments,
risk_occured_YN,
risk_occurence_date,
run_id)
--created_by)
VALUES(p_imp_contract_id,
OKC_REP_IMP_RISKS_T_S.nextval,
p_risks_tbl(i).risk_event_txt,
p_risks_tbl(i).probability_txt,
p_risks_tbl(i).risk_impact_txt,
p_risks_tbl(i).risk_comments,
p_risks_tbl(i).risk_occured_YN,
p_risks_tbl(i).risk_occurence_date,
p_run_id);
Procedure delete_contract(p_api_version IN NUMBER,
p_commit IN VARCHAR2 := fnd_api.g_false,
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(150);
SELECT contract_status_code FROM okc_rep_contracts_all
WHERE contract_id = p_contract_id;
l_api_name := 'delete_contract ';
okc_rep_contract_process_pvt.delete_contract( p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => p_commit,
p_contract_id => p_contract_id,
x_msg_data => x_msg_data,
x_msg_count => x_msg_count,
x_return_status => x_return_status);
'Delete cannot be performed on a contract in status signed/terminated');
'Leaving delete_contract because of EXCEPTION: ' || sqlerrm);
END delete_contract;
PROCEDURE validate_and_insert_con_cp(errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY VARCHAR2,
p_org_name IN NUMBER,
p_contract_type IN VARCHAR2,
p_from_date IN DATE,
p_to_date IN DATE,
p_validate_only IN VARCHAR2 )
IS
l_api_name VARCHAR2(150);
l_update_query VARCHAR2(5000);
l_number_inserted NUMBER;
SELECT ORGANIZATION_ID
FROM HR_ALL_ORGANIZATION_UNITS
WHERE UPPER(NAME) = UPPER(p_org_name)
AND mo_global.check_access(ORGANIZATION_ID) = 'Y';
SELECT document_type
FROM OKC_BUS_DOC_TYPES_V
WHERE Upper(name) = Upper(p_contract_type)
AND document_type_class = 'REPOSITORY'
AND TRUNC(SYSDATE) BETWEEN
NVL(START_DATE, TRUNC(SYSDATE -1)) AND
NVL(END_DATE, TRUNC(SYSDATE +1));
l_api_name := 'validate_and_insert_con_cp ';
SELECT okc_rep_import_run_id_s.NEXTVAL INTO l_run_id FROM dual;
l_update_query := 'UPDATE OKC_REP_IMP_CONTRACTS_T
SET run_id = '|| l_run_id||' , request_id = ' || l_request_id ||
' WHERE nvl(valid_flag, ''U'') IN (''U'', ''Y'')
AND run_id IS NULL ' || l_where_clause;
okc_debug.log('400: Before Execute Immediate l_update_query: '|| l_update_query, 2);
EXECUTE IMMEDIATE (l_update_query);
UPDATE OKC_REP_IMP_CONTRACTS_T
SET valid_flag = 'U'
WHERE run_id = l_run_id
AND valid_flag IS NULL;
UPDATE okc_rep_imp_parties_t
SET run_id = l_run_id,
request_id = l_request_id
WHERE imp_contract_id IN (SELECT imp_contract_id
FROM OKC_REP_IMP_CONTRACTS_T
WHERE run_id = l_run_id);
okc_debug.log('700: After UPDATE okc_rep_imp_parties_t ', 1);
UPDATE okc_rep_imp_contacts_t
SET run_id = l_run_id,
request_id = l_request_id
WHERE imp_contract_id IN (SELECT imp_contract_id
FROM OKC_REP_IMP_CONTRACTS_T
WHERE run_id = l_run_id);
okc_debug.log('800: After UPDATE okc_rep_imp_contacts_t ', 1);
UPDATE okc_rep_imp_risks_t
SET run_id = l_run_id,
request_id = l_request_id
WHERE imp_contract_id IN (SELECT imp_contract_id
FROM OKC_REP_IMP_CONTRACTS_T
WHERE run_id = l_run_id);
okc_debug.log('900: After UPDATE okc_rep_imp_risks_t ', 1);
UPDATE OKC_REP_IMP_DOCUMENTS_T
SET run_id = l_run_id,
request_id = l_request_id
WHERE imp_contract_id IN (SELECT imp_contract_id
FROM OKC_REP_IMP_CONTRACTS_T
WHERE run_id = l_run_id);
okc_debug.log('1000: After UPDATE OKC_REP_IMP_DOCUMENTS_T ', 1);
okc_rep_util_pvt.validate_and_insert_contracts( p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_run_id => l_run_id,
p_call_source => 'CP_SQL',
p_validate_only => p_validate_only,
x_msg_data => l_msg_data,
x_msg_count => l_msg_count,
x_return_status => l_return_status,
x_number_inserted => l_number_inserted);
okc_debug.log('1100: After okc_rep_util_pvt.validate_and_insert_contracts l_msg_data: '|| l_msg_data, 1);
okc_debug.log('1100: After okc_rep_util_pvt.validate_and_insert_contracts l_msg_count: '|| l_msg_count, 1);
okc_debug.log('1100: After okc_rep_util_pvt.validate_and_insert_contracts l_return_status: '||l_return_status, 1);
okc_debug.log('1100: After okc_rep_util_pvt.validate_and_insert_contracts l_number_inserted: ' ||l_number_inserted, 1);
okc_debug.log('1500: Leaving validate_and_insert_con_cp because of EXCEPTION: '||sqlerrm);
END validate_and_insert_con_cp;
PROCEDURE delete_contract_risks(p_api_version IN NUMBER,
p_run_id IN NUMBER DEFAULT NULL,
p_risks_tbl IN OUT NOCOPY OKC_IMP_RECORD_TYPES.contract_risks_tbl_type,
p_imp_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(150);
SELECT RISK_EVENT_ID
FROM okc_risk_events_tl
WHERE Upper(name) = Upper(p_risk_event_txt)
AND LANGUAGE = userenv('LANG');
SELECT risk_event_id
FROM okc_contract_risks
WHERE business_document_type = p_document_type
AND business_document_id = p_document_id
AND business_document_version = p_document_version
AND risk_event_id = risk_event_id;
l_api_name := 'delete_contract_risks ';
END delete_contract_risks;
PROCEDURE delete_party_contacts(p_api_version IN NUMBER,
p_run_id IN NUMBER,
p_party_contacts_tbl IN OUT NOCOPY OKC_IMP_RECORD_TYPES.party_contacts_tbl_type,
p_imp_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(150);
l_api_name := 'delete_party_contacts ';
END delete_party_contacts;
Update_contract_usages(p_api_version => 1.0,
p_contract_rec => p_contract_rec,
p_document_rec => p_document_rec,
p_commit => p_commit,
x_msg_data => l_msg_data,
x_msg_count => l_msg_count,
x_return_status => l_return_status);
SELECT error_message FROM okc_rep_imp_errors_t
WHERE run_id = p_contract_rec.run_id; */
Deletes the Contract for the given document type.
*/
Procedure delete_contract(p_api_version IN NUMBER,
p_commit IN VARCHAR2 := fnd_api.g_false,
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,
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';
l_query := 'SELECT contract_id FROM okc_rep_contract_usages
WHERE business_document_type = ''' || p_document_rec.business_document_type ||
''' AND business_document_id = ' || p_document_rec.business_document_id;
SELECT contract_type, contract_id, contract_version_num, run_id
INTO l_contract_rec.contract_type, l_contract_rec.contract_id,
l_contract_rec.contract_version_num, l_contract_rec.run_id
FROM okc_rep_contracts_all orca
WHERE contract_id = l_contract_id;
delete_contract(p_api_version => p_api_version,
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);
delete_contract_usages(p_api_version => p_api_version,
p_contract_rec => l_contract_rec,
p_document_rec => p_document_rec,
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 because of EXCEPTION: ' || sqlerrm);
'Leaving delete_contract because of EXCEPTION: ' || sqlerrm);
'Leaving delete_contract because of EXCEPTION: ' || sqlerrm);
END delete_contract;
Updates to the contract should be done from UI.
*/
Procedure create_new_contract_version(p_api_version IN NUMBER,
p_contract_id IN NUMBER,
p_run_id IN NUMBER,
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 contract_status_code FROM okc_rep_contracts_all
WHERE contract_id = p_contract_id;
UPDATE okc_rep_contracts_all SET contract_version_num = contract_version_num +1,
contract_status_code = 'DRAFT',
last_updated_by = FND_GLOBAL.user_id(),
last_update_login = FND_GLOBAL.user_id(),
last_update_date = SYSDATE,
run_id = okc_rep_import_run_id_s.NEXTVAL
WHERE contract_id = p_contract_id;
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_run_id IN NUMBER,
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_query := 'SELECT contract_id FROM okc_rep_contract_usages
WHERE business_document_type = ''' || p_document_rec.business_document_type ||
''' AND business_document_id = ' || p_document_rec.business_document_id;
SELECT contract_type, contract_id, contract_version_num, run_id
INTO l_contract_rec.contract_type, l_contract_rec.contract_id,
l_contract_rec.contract_version_num, l_contract_rec.run_id
FROM okc_rep_contracts_all orca
WHERE contract_id = l_contract_id;
Update_contract_usages(p_api_version => p_api_version,
p_contract_rec => l_contract_rec,
p_document_rec => p_document_rec,
p_commit => p_commit,
x_msg_data => x_msg_data,
x_msg_count => x_msg_count,
x_return_status => x_return_status);
SELECT orcu.contract_type, orcu.contract_id, orcu.contract_version
FROM okc_rep_contract_usages orcu, okc_rep_contracts_all orca
WHERE orcu.business_document_type = p_document_rec.business_document_type
AND orcu.business_document_id = p_document_rec.business_document_id
--AND orcu.business_document_version = p_document_rec.business_document_version
AND orcu.contract_type = Nvl(x_contract_type,orcu.contract_type)
AND orca.contract_id = orcu.contract_id
AND orca.contract_version_num = orcu.contract_version;
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;
'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_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 = l_contract_id;
SELECT orcu.contract_type, orcu.contract_id, orcu.contract_version, orca.contract_status_code ,
orca.sbcr_coordination_type
FROM okc_rep_contract_usages orcu, okc_rep_contracts_all orca
WHERE orcu.business_document_type = p_document_rec.business_document_type
AND orcu.business_document_id = p_document_rec.business_document_id
--AND orcu.business_document_version = p_document_rec.business_document_version
AND orcu.contract_type = Nvl(x_contract_rec.contract_type,orcu.contract_type)
AND orca.contract_id = orcu.contract_id
AND orca.contract_version_num = orcu.contract_version;
Deletes/ Cancels the Contract for the given document type based on the status
*/
Procedure delete_cancel_contract(p_api_version IN NUMBER,
p_commit IN VARCHAR2 := fnd_api.g_false,
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,
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_cancel_contract';
l_query := 'SELECT contract_id, contract_version FROM okc_rep_contract_usages
WHERE business_document_type = ''' || p_document_rec.business_document_type ||
''' AND business_document_id = ' || p_document_rec.business_document_id;
SELECT contract_status_code INTO l_contract_status_code
FROM okc_rep_contracts_all WHERE contract_id = l_contract_id;
SELECT contract_type, contract_id, contract_version_num, run_id
INTO l_contract_rec.contract_type, l_contract_rec.contract_id,
l_contract_rec.contract_version_num, l_contract_rec.run_id
FROM okc_rep_contracts_all orca
WHERE contract_id = l_contract_id;
delete_contract(p_api_version => p_api_version,
p_contract_id => l_contract_id,
p_commit => p_commit,
x_msg_data => x_msg_data,
x_msg_count => x_msg_count,
x_return_status => x_return_status);
'OKC_REP_CONTRACT_IMP_PVT.delete_contract return status is : '
|| x_return_status);
delete_contract_usages(p_api_version => p_api_version,
p_contract_rec => l_contract_rec,
p_document_rec => p_document_rec,
p_commit => p_commit,
x_msg_data => x_msg_data,
x_msg_count => x_msg_count,
x_return_status => x_return_status);
'OKC_REP_CONTRACT_IMP_PVT.delete_contract return status is : '
|| x_return_status);
'Leaving delete_contract because of EXCEPTION: ' || sqlerrm);
END delete_cancel_contract;
SELECT contract_type, contract_status_code FROM okc_rep_contracts_all
WHERE contract_id = p_contract_id;
UPDATE okc_rep_contracts_all
SET CONTRACT_STATUS_CODE = 'CANCELLED',
CANCELLATION_DATE = SYSDATE
WHERE contract_id = p_contract_id
AND contract_type = l_contract_type;
l_query := 'SELECT contract_id, contract_version FROM okc_rep_contract_usages
WHERE business_document_type = ''' || p_document_rec.business_document_type ||
''' AND business_document_id = ' || p_document_rec.business_document_id;
* 1. For one row in okc_rep_contract_rels table, all the target related contracts are brought and these are inserted
* against each version of source contract.
* 2. If the relationship(combination of contract_id, contract_type, contract_version_num, business_document_id, business_document_type,
business_document_version, relationship_role_id) already exists in the new table (okc_rep_contract_usages), then this procedure doesn't insert it again in the new table
* 3. Marks the rows in okc_rep_contract_usagaes with source_code as 'MIGRATION' and populates corresponding requires_id, program_id.
*
* Parameters :
* p_purge_and_rerun if 'Yes', deletes all rows in okc_rep_contract_usages with source_code as 'MIGRATION and migrates the date
* if 'No', starts migrating the data, if the row already exists doesn't insert again.
* default value : 'No' (if no value is entered by the user, then it takes as 'No')
*
* p_simulate if 'Yes', starts migrating the data, logs all successful migrated rows and errored rows. But dont' commmit the data.
* Can be used for testing the migration process before actual run of this program.
* if 'No', migrates data and commits data
* default value : 'Yes' ( if no value is entered by the user, then it takes as 'Yes')
*/
PROCEDURE migrate_contract_relations(errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY VARCHAR2,
p_purge_and_rerun IN VARCHAR2,
p_simulate IN VARCHAR2)
IS
l_api_name VARCHAR2(50);
l_last_updated_bys tbl_number;
l_last_update_dates tbl_date;
l_last_update_logins tbl_number;
SELECT src.contract_id,
src.contract_type,
src.contract_version_num,
tgt.contract_id,
tgt.contract_type,
tgt.contract_version_num,
tgt.contract_number,
rels.relationship_role1_id,
rels.object_version_number,
rels.created_by,
rels.creation_date,
rels.last_updated_by,
rels.last_update_date,
rels.last_update_login
FROM okc_rep_contract_rels rels,
okc_rep_contracts_all src,
okc_rep_contracts_all tgt
WHERE src.contract_id = rels.contract_id
AND tgt.contract_id = rels.related_contract_id
--to avoid reruns of this procedure or on abnormal run of this program
AND NOT EXISTS (SELECT 1 FROM okc_rep_contract_usages new_rels
WHERE new_rels.contract_id = src.contract_id
AND new_rels.contract_type = src.contract_type
AND new_rels.contract_version = src.contract_version_num
AND new_rels.business_document_id = tgt.contract_id
AND new_rels.business_document_type = tgt.contract_type
AND new_rels.business_document_version = tgt.contract_version_num
AND new_rels.relationship_id = rels.relationship_role1_id)
UNION ALL
SELECT src_vers.contract_id,
src_vers.contract_type,
src_vers.contract_version_num,
tgt.contract_id,
tgt.contract_type,
tgt.contract_version_num,
tgt.contract_number,
rels.relationship_role1_id,
rels.object_version_number,
rels.created_by,
rels.creation_date,
rels.last_updated_by,
rels.last_update_date,
rels.last_update_login
FROM okc_rep_contract_rels rels,
okc_rep_contract_vers src_vers,
okc_rep_contracts_all tgt
WHERE src_vers.contract_id = rels.contract_id
AND tgt.contract_id = rels.related_contract_id
--to avoid reruns of this procedure or on abnormal run of this program
AND NOT EXISTS (SELECT 1 FROM okc_rep_contract_usages new_rels
WHERE new_rels.contract_id = src_vers.contract_id
AND new_rels.contract_type = src_vers.contract_type
AND new_rels.contract_version = src_vers.contract_version_num
AND new_rels.business_document_id = tgt.contract_id
AND new_rels.business_document_type = tgt.contract_type
AND new_rels.business_document_version = tgt.contract_version_num
AND new_rels.relationship_id = rels.relationship_role1_id);
SELECT count(1) INTO l_source_rels
FROM okc_rep_contract_rels;
SELECT sum(con.contract_version_num) INTO l_source_rows
FROM okc_rep_contract_rels rels, okc_rep_contracts_all con
WHERE rels.contract_id = con.contract_id;
SELECT count(1) INTO l_already_migrated
FROM okc_rep_contract_usages usages
WHERE source_code = 'MIGRATION';
DELETE FROM okc_rep_contract_usages
WHERE source_code = 'MIGRATION';
l_tgt_contract_nums, l_relationship_ids, l_object_ver_nums, l_created_bys, l_creation_dates, l_last_updated_bys, l_last_update_dates, l_last_update_logins LIMIT 10000;
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,
source_code,
relationship_id,
display_in_contract,
program_id,
program_login_id,
program_application_id,
request_id,
object_version_number,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
VALUES
(okc_rep_contract_usages_s.NEXTVAL,
l_src_contract_types(i),
l_src_contract_ids(i),
l_src_contract_ver_nums(i),
l_tgt_contract_types(i),
l_tgt_contract_ids(i),
l_tgt_contract_ver_nums(i),
l_tgt_contract_nums(i),
'MIGRATION',
l_relationship_ids(i),
'Y',
g_conc_program_id,
g_conc_login_id,
g_prog_appl_id,
g_conc_request_id,
l_object_ver_nums(i),
l_created_bys(i),
l_creation_dates(i),
l_last_updated_bys(i),
l_last_update_dates(i),
l_last_update_logins(i)
)
RETURNING sequence_id BULK COLLECT INTO l_seq_nums;