[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
G_NO_UPDATE_ALLOWED_EXCEPTION exception;
G_NO_UPDATE_ALLOWED CONSTANT VARCHAR2(200) := 'OKC_NO_UPDATE_ALLOWED';
Select application_id
from okc_k_headers_b
where id = p_id;
SELECT pdfv.wf_name, cpsv.process_id
FROM okc_process_defs_b pdfv,
okc_k_processes cpsv
WHERE pdfv.id = cpsv.pdf_id
AND cpsv.chr_id = p_chr_id;
SELECT end_date
FROM wf_items
WHERE item_type = l_wf_name
AND item_key = l_item_key;
Select access_level
from okc_subclass_resps_v
where scs_code=p_scs_code
and resp_id=fnd_global.resp_id
and sysdate between start_date and nvl(end_date,sysdate);
FUNCTION Update_Minor_Version(p_chr_id IN NUMBER) RETURN VARCHAR2 Is
l_api_version NUMBER := 1;
OKC_CVM_PVT.update_contract_version(
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_cvmv_rec => l_cvmv_rec,
x_cvmv_rec => x_out_rec);
OKC_CVM_PVT.update_row(
p_api_version,
p_init_msg_list,
x_return_status,
x_msg_count,
x_msg_data,
l_cvmv_rec,
x_cvmv_rec);
FUNCTION Delete_Version(p_chr_id IN NUMBER) RETURN VARCHAR2 Is
l_api_version NUMBER := 1;
OKC_CVM_PVT.delete_contract_version(
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_cvmv_rec => l_cvmv_rec);
Select meaning
from okc_subclasses_v
where code=p_chrv_rec.scs_code;
SELECT to_char (major_version)||'.'||to_char(minor_version)
FROM okc_k_vers_numbers
WHERE chr_id=p_chr_id;
OKC_CHR_PVT.Insert_Row(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_chrv_rec => l_chrv_rec,
x_chrv_rec => x_chrv_rec);
OKC_CHR_PVT.Insert_Row(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_chrv_tbl => p_chrv_tbl,
x_chrv_tbl => x_chrv_tbl);
PROCEDURE update_contract_header(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 ,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_restricted_update IN VARCHAR2 ,
p_chrv_rec IN OKC_CHR_PVT.chrv_rec_type,
x_chrv_rec OUT NOCOPY OKC_CHR_PVT.chrv_rec_type) IS
l_currency_code VARCHAR2(5);
SELECT currency_code
--npalepu 26-10-2005 modified for bug # 4691662.
--Replaced table okc_k_headers_b with headers_All_b table
/* FROM okc_k_headers_b */
FROM okc_k_headers_all_b
--end npalepu
WHERE ID = p_chrv_rec.id;
SELECT to_char (major_version)||'.'||to_char(minor_version)
FROM okc_k_vers_numbers
WHERE chr_id=p_chr_id;
SELECT sts_code
--npalepu 26-10-2005 modified for bug # 4691662.
--Replaced table okc_k_headers_b with headers_All_b table
/* FROM okc_k_headers_b */
FROM okc_k_headers_all_b
--end npalepu
WHERE ID = p_chrv_rec.id;
SELECT ste_code
FROM okc_statuses_b
WHERE code=p_status_code;
If (p_restricted_update <> OKC_API.G_TRUE) Then
If (OKC_CONTRACT_PUB.Update_Allowed(p_chrv_rec.id) <> 'Y') Then
raise G_NO_UPDATE_ALLOWED_EXCEPTION;
OKC_CHR_PVT.Update_Row(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_restricted_update => p_restricted_update,
p_chrv_rec => p_chrv_rec,
x_chrv_rec => x_chrv_rec);
x_return_status := Update_Minor_Version(p_chrv_rec.id);
OKC_CTC_PVT.update_contact_stecode(p_chr_id => p_chrv_rec.id,
x_return_status=>l_return_status);
UPDATE okc_k_lines_b
SET currency_code = x_chrv_rec.currency_code
WHERE dnz_chr_id = x_chrv_rec.id;
x_return_status := Update_Minor_Version(p_chrv_rec.id);
when G_NO_UPDATE_ALLOWED_EXCEPTION then
x_return_status := OKC_API.G_RET_STS_ERROR;
p_msg_name => g_no_update_allowed,
p_token1 => 'VALUE1',
p_token1_value => 'Contract Header');
END update_contract_header;
PROCEDURE update_contract_header(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 ,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_restricted_update IN VARCHAR2 ,
p_chrv_tbl IN OKC_CHR_PVT.chrv_tbl_type,
x_chrv_tbl OUT NOCOPY OKC_CHR_PVT.chrv_tbl_type) IS
BEGIN
OKC_CHR_PVT.Update_Row(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_restricted_update => p_restricted_update,
p_chrv_tbl => p_chrv_tbl,
x_chrv_tbl => x_chrv_tbl);
END update_contract_header;
PROCEDURE update_contract_header(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 ,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_restricted_update IN VARCHAR2 ,
p_chrv_rec IN OKC_CHR_PVT.chrv_rec_type,
p_control_rec IN control_rec_type,
x_chrv_rec OUT NOCOPY OKC_CHR_PVT.chrv_rec_type) IS
l_currency_code VARCHAR2(5);
SELECT currency_code
--npalepu 26-10-2005 modified for bug # 4691662.
--Replaced table okc_k_headers_b with headers_All_b table
/* FROM okc_k_headers_b */
FROM okc_k_headers_all_b
--end npalepu
WHERE ID = p_chrv_rec.id;
SELECT to_char (major_version)||'.'||to_char(minor_version)
FROM okc_k_vers_numbers
WHERE chr_id=p_chr_id;
SELECT sts_code
--npalepu 26-10-2005 modified for bug # 4691662.
--Replaced table okc_k_headers_b with headers_All_b table
/* FROM okc_k_headers_b */
FROM okc_k_headers_all_b
--end npalepu
WHERE ID = p_chrv_rec.id;
SELECT ste_code
FROM okc_statuses_b
WHERE code=p_status_code;
If (p_restricted_update <> OKC_API.G_TRUE) Then
If (OKC_CONTRACT_PUB.Update_Allowed(p_chrv_rec.id) <> 'Y') Then
raise G_NO_UPDATE_ALLOWED_EXCEPTION;
OKC_CHR_PVT.Update_Row(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_restricted_update => p_restricted_update,
p_chrv_rec => p_chrv_rec,
x_chrv_rec => x_chrv_rec);
x_return_status := Update_Minor_Version(p_chrv_rec.id);
OKC_CTC_PVT.update_contact_stecode(p_chr_id => p_chrv_rec.id,
x_return_status=>l_return_status);
UPDATE okc_k_lines_b
SET currency_code = x_chrv_rec.currency_code
WHERE dnz_chr_id = x_chrv_rec.id;
x_return_status := Update_Minor_Version(p_chrv_rec.id);
when G_NO_UPDATE_ALLOWED_EXCEPTION then
x_return_status := OKC_API.G_RET_STS_ERROR;
p_msg_name => g_no_update_allowed,
p_token1 => 'VALUE1',
p_token1_value => 'Contract Header');
END update_contract_header;
PROCEDURE delete_contract_header(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 ,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_chrv_rec IN OKC_CHR_PVT.chrv_rec_type) IS
l_dummy_val NUMBER;
select count(*)
from OKC_K_LINES_B
where chr_id = p_chrv_rec.id;
SELECT id, object_version_number
FROM OKC_K_REL_OBJS
WHERE chr_id = p_chrv_rec.id;
SELECT to_char(major_version)
FROM okc_k_vers_numbers
WHERE chr_id = p_chrv_rec.id;
SELECT id
FROM okc_operation_instances
WHERE target_chr_id = p_chrv_rec.id;
SELECT id, object_version_number, dnz_chr_id
FROM okc_k_sales_credits
WHERE chr_id = p_chrv_rec.id;
SELECT ste_code from okc_statuses_b status, okc_k_headers_b header
where status.code = header.sts_code
AND header.id = p_chrv_rec.id;
If (OKC_CONTRACT_PUB.Update_Allowed(p_chrv_rec.id) <> 'Y') Then
raise G_NO_UPDATE_ALLOWED_EXCEPTION;
OKC_SALES_credit_PUB.delete_Sales_credit(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_scrv_rec => l_scrv_rec);
OKC_VERSION_PVT.delete_version (p_chr_id => p_chrv_rec.id,
p_major_version => 0,
p_minor_version => 0,
p_called_from => 'RESTORE_VERSION');
OKC_CHR_PVT.Delete_Row(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_chrv_rec => p_chrv_rec);
OKC_OPER_INST_PUB.Delete_Operation_Instance (
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_oiev_tbl => l_oiev_tbl);
OKC_K_REL_OBJS_PUB.delete_row(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_crjv_rec => l_crjv_rec);
fnd_attached_documents2_pkg.delete_attachments(
x_entity_name => 'OKC_K_HEADERS_B',
x_pk1_value => p_chrv_rec.id,
x_pk2_value => l_major_version
);
x_return_status := Delete_Version(p_chrv_rec.id);
when G_NO_UPDATE_ALLOWED_EXCEPTION then
x_return_status := OKC_API.G_RET_STS_ERROR;
p_msg_name => g_no_update_allowed,
p_token1 => 'VALUE1',
p_token1_value => 'Contract Header');
END delete_contract_header;
PROCEDURE delete_contract_header(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 ,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_chrv_tbl IN OKC_CHR_PVT.chrv_tbl_type) IS
BEGIN
OKC_CHR_PVT.Delete_Row(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_chrv_tbl => p_chrv_tbl);
END delete_contract_header;
select NVL(MAX(level_sequence),0) + 1
from OKC_ANCESTRYS
where cle_id = p_clev_rec.cle_id;
select cle_id_ascendant, level_sequence
from OKC_ANCESTRYS
where cle_id = p_clev_rec.cle_id;
OKC_ACY_PVT.insert_row(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_acyv_rec => l_acyv_rec,
x_acyv_rec => l_out_rec);
OKC_ACY_PVT.insert_row(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_acyv_rec => l_acyv_rec,
x_acyv_rec => l_out_rec);
SELECT dnz_chr_id
FROM OKC_K_LINES_B
WHERE id = p_clev_rec.id;
p_restricted_update IN VARCHAR2 ,
p_clev_rec IN OKC_CLE_PVT.clev_rec_type,
x_clev_rec OUT NOCOPY OKC_CLE_PVT.clev_rec_type) IS
l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
If (p_restricted_update <> OKC_API.G_TRUE) Then
If (OKC_CONTRACT_PUB.Update_Allowed(l_chr_id) <> 'Y') Then
raise G_NO_UPDATE_ALLOWED_EXCEPTION;
OKC_CLE_PVT.Insert_Row(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_clev_rec => p_clev_rec,
x_clev_rec => x_clev_rec);
x_return_status := Update_Minor_Version(x_clev_rec.dnz_chr_id);
when G_NO_UPDATE_ALLOWED_EXCEPTION then
OKC_API.SET_MESSAGE(p_app_name => g_app_name,
p_msg_name => g_no_update_allowed,
p_token1 => 'VALUE1',
p_token1_value => 'Contract Lines');
OKC_CLE_PVT.Insert_Row(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_clev_tbl => p_clev_tbl,
x_clev_tbl => x_clev_tbl);
PROCEDURE update_contract_line(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 ,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_restricted_update IN VARCHAR2 ,
p_clev_rec IN OKC_CLE_PVT.clev_rec_type,
x_clev_rec OUT NOCOPY OKC_CLE_PVT.clev_rec_type) IS
l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
l_api_name CONSTANT VARCHAR2(30) := 'Update_Contract_Line';
SELECT contract_number,contract_number_modifier
--npalepu 26-10-2005 modified for bug # 4691662.
--Replaced table okc_k_headers_b with headers_All_b table
/* FROM okc_k_headers_b */
FROM okc_k_headers_all_b
--end npalepu
where id = p_id;
If (p_restricted_update <> OKC_API.G_TRUE) Then
If (OKC_CONTRACT_PUB.Update_Allowed(l_chr_id) <> 'Y') Then
raise G_NO_UPDATE_ALLOWED_EXCEPTION;
OKC_CLE_PVT.Update_Row(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_restricted_update => p_restricted_update,
p_clev_rec => p_clev_rec,
x_clev_rec => x_clev_rec);
x_return_status := Update_Minor_Version(x_clev_rec.dnz_chr_id);
when G_NO_UPDATE_ALLOWED_EXCEPTION then
OKC_API.SET_MESSAGE(p_app_name => g_app_name,
p_msg_name => g_no_update_allowed,
p_token1 => 'VALUE1',
p_token1_value => 'Contract Lines');
END update_contract_line;
PROCEDURE update_contract_line(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 ,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_restricted_update IN VARCHAR2 ,
p_clev_tbl IN OKC_CLE_PVT.clev_tbl_type,
x_clev_tbl OUT NOCOPY OKC_CLE_PVT.clev_tbl_type) IS
BEGIN
OKC_CLE_PVT.Update_Row(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_restricted_update => p_restricted_update,
p_clev_tbl => p_clev_tbl,
x_clev_tbl => x_clev_tbl);
END update_contract_line;
PROCEDURE delete_ancestry(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 ,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_cle_id IN NUMBER) Is
l_acyv_rec OKC_ACY_PVT.acyv_rec_type;
select cle_id, cle_id_ascendant
from OKC_ANCESTRYS
where cle_id = p_cle_id;
OKC_ACY_PVT.delete_row(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_acyv_rec => l_acyv_rec);
END delete_ancestry;
PROCEDURE delete_contract_line(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 ,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_clev_rec IN OKC_CLE_PVT.clev_rec_type) IS
l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
select count(*)
from OKC_K_LINES_B
where cle_id = p_clev_rec.id;
select count(*)
from OKC_K_ITEMS
where cle_id = p_clev_rec.id;
SELECT id, object_version_number
FROM OKC_K_REL_OBJS
WHERE cle_id = p_clev_rec.id;
SELECT to_char(major_version)
FROM okc_k_vers_numbers
WHERE chr_id = p_chr_id;
SELECT id, object_version_number, dnz_chr_id
FROM okc_k_sales_credits
WHERE cle_id = p_clev_rec.id;
SELECT id, object_version_number, cle_id
FROM okc_ph_line_breaks
WHERE cle_id = p_clev_rec.id;
SELECT id, object_version_number
FROM okc_governances
WHERE cle_id = p_clev_rec.id
AND dnz_chr_id = l_chr_id;
If (OKC_CONTRACT_PUB.Update_Allowed(l_chr_id) <> 'Y') Then
raise G_NO_UPDATE_ALLOWED_EXCEPTION;
OKC_CLE_PVT.Delete_Row(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_clev_rec => p_clev_rec);
delete_ancestry(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_cle_id => p_clev_rec.id);
OKC_K_REL_OBJS_PUB.delete_row(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_crjv_rec => l_crjv_rec);
OKC_SALES_credit_PUB.delete_Sales_credit(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_scrv_rec => l_scrv_rec);
don't need to do this for delete
--added for price hold top lines
IF l_lse_id = 61 THEN
--if the contract line being deleted is a Price Hold top line,
--we need to delete the corresponding entries in QP
OKC_PHI_PVT.process_price_hold(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_chr_id => l_dnz_chr_id,
p_operation_code => 'TERMINATE');
OKC_PH_LINE_BREAKS_PUB.delete_Price_Hold_Line_Breaks(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_okc_ph_line_breaks_v_rec => l_okc_ph_line_breaks_v_rec
);
OKC_GVE_PVT.Delete_Row(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_gvev_rec => l_gvev_rec
);
fnd_attached_documents2_pkg.delete_attachments(
x_entity_name => 'OKC_K_LINES_B',
x_pk1_value => p_clev_rec.id,
x_pk2_value => l_major_version
);
x_return_status := Update_Minor_Version(l_chr_id);
when G_NO_UPDATE_ALLOWED_EXCEPTION then
OKC_API.SET_MESSAGE(p_app_name => g_app_name,
p_msg_name => g_no_update_allowed,
p_token1 => 'VALUE1',
p_token1_value => 'Contract Lines');
END delete_contract_line;
PROCEDURE delete_contract_line(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 ,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_clev_tbl IN OKC_CLE_PVT.clev_tbl_type) IS
BEGIN
OKC_CLE_PVT.Delete_Row(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_clev_tbl => p_clev_tbl);
END delete_contract_line;
PROCEDURE delete_contract_line(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 ,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_line_id IN NUMBER) IS
l_Cov_cle_Id NUMBER;
SELECT dnz_chr_id
FROM okc_k_lines_b
WHERE id = p_id;
IS SELECT ID
FROM Okc_K_Lines_b
WHERE cle_Id=P_Parent_Id;
IS SELECT ID
FROM Okc_K_Lines_b
WHERE cle_Id=P_Parent_Id;
IS SELECT ID
FROM Okc_K_Lines_b
WHERE cle_Id=P_Parent_Id;
IS SELECT ID
FROM Okc_K_Lines_b
WHERE cle_Id=P_Parent_Id;
IS SELECT ID
FROM Okc_K_Lines_b
WHERE cle_Id=P_Parent_Id;
IS SELECT ID
FROM Okc_K_ITEMS
WHERE cle_Id=P_Line_Id;
IS SELECT Tve_ID
FROM OKC_React_Intervals
WHERE Rul_Id =P_Rule_Id;
SELECT ID FROM OKC_K_PARTY_ROLES_B
WHERE dnz_chr_id = l_chr_id
AND cle_Id=P_cle_Id;
SELECT ID FROM OKC_CONTACTS
WHERE cpl_Id=P_cpl_Id;
SELECT ID FROM OKC_RULES_B
WHERE Rgp_Id=P_Rgp_Id
AND Rule_Information_category=P_rule_Type;
SELECT ID FROM OKC_RULES_B
WHERE Rgp_Id=P_Rgp_Id;
SELECT ID FROM OKC_RULE_GROUPS_B
WHERE cle_Id=P_Cle_Id;
SELECT Id FROM OKC_K_REL_OBJS
WHERE cle_Id = P_cle_Id;
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Contract_Line';
SELECT COUNT(*) FROM OKC_K_LINES_B
WHERE id=P_Line_Id;
OKC_K_REL_OBJS_PUB.Delete_Row(
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_crjv_tbl => l_crjv_tbl_in);
OKC_CONTRACT_PARTY_PUB.Delete_Contact(
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_ctcv_tbl => l_ctcv_tbl_in);
OKC_CONTRACT_PARTY_PUB.Delete_k_Party_Role(
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_cplv_tbl => l_cplv_tbl_in);
okc_Rule_pub.delete_Rule (
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_rulv_tbl => l_rulv_tbl_in);
okc_Rule_pub.delete_Rule_group (
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_rgpv_tbl => l_rgpv_tbl_in);
okc_contract_ITEM_pub.delete_Contract_ITEM (
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_cimv_tbl => l_cimv_tbl_in);
okc_contract_pub.delete_contract_line (
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_clev_tbl => l_clev_tbl_in);
'Delete_Contract_Line',
'OKC_API.G_RET_STS_ERROR',
l_msg_count,
l_msg_data,
'_PVT'
);
'Delete_Contract_Line',
'OKC_API.G_RET_STS_ERROR',
l_msg_count,
l_msg_data,
'_PVT'
);
'Delete_Contract_Line',
'OKC_API.G_RET_STS_UNEXP_ERROR',
l_msg_count,
l_msg_data,
'_PVT'
);
END delete_contract_line;
PROCEDURE force_delete_contract_line(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 ,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_line_id IN NUMBER) IS
l_Cov_cle_Id NUMBER;
SELECT dnz_chr_id
FROM okc_k_lines_b
WHERE id = p_id;
IS SELECT ID
FROM Okc_K_Lines_b
WHERE cle_Id=P_Parent_Id;
IS SELECT ID
FROM Okc_K_Lines_b
WHERE cle_Id=P_Parent_Id;
IS SELECT ID
FROM Okc_K_Lines_b
WHERE cle_Id=P_Parent_Id;
IS SELECT ID
FROM Okc_K_Lines_b
WHERE cle_Id=P_Parent_Id;
IS SELECT ID
FROM Okc_K_Lines_b
WHERE cle_Id=P_Parent_Id;
IS SELECT ID
FROM Okc_K_ITEMS
WHERE cle_Id=P_Line_Id;
IS SELECT Tve_ID
FROM OKC_React_Intervals
WHERE Rul_Id =P_Rule_Id;
SELECT ID FROM OKC_K_PARTY_ROLES_B
WHERE dnz_chr_id = l_chr_id
AND cle_Id=P_cle_Id;
SELECT ID FROM OKC_CONTACTS
WHERE cpl_Id=P_cpl_Id;
SELECT ID FROM OKC_RULES_B
WHERE Rgp_Id=P_Rgp_Id
AND Rule_Information_category=P_rule_Type;
SELECT ID FROM OKC_RULES_B
WHERE Rgp_Id=P_Rgp_Id;
SELECT ID FROM OKC_RULE_GROUPS_B
WHERE cle_Id=P_Cle_Id;
SELECT Id FROM OKC_K_REL_OBJS
WHERE cle_Id = P_cle_Id;
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Contract_Line';
SELECT COUNT(*) FROM OKC_K_LINES_B
WHERE id=P_Line_Id;
PROCEDURE delete_rule(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 ,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_rulv_rec IN OKC_RULE_PUB.rulv_rec_type) IS
--x_return_status VARCHAR2 := 'S';
SELECT *
FROM OKC_COVER_TIMES_V ctiv
WHERE ctiv.RUL_ID = p_rulv_rec.id;
SELECT 'x'
FROM OKC_ARTICLE_TRANS_V atnv
WHERE atnv.RUL_ID = p_rulv_rec.id;
SELECT *
FROM OKC_REACT_INTERVALS_V rilv
WHERE rilv.RUL_ID = p_rulv_rec.id;
SELECT RULE_INFORMATION_CATEGORY
FROM OKC_RULES_B
WHERE ID = p_rulv_rec.id;
l_proc varchar2(72) := 'delete_rule';
okc_debug.log('10: starting delete rule', 2);
okc_debug.log('G_EXCEPTION_CANNOT_DELETE');
okc_debug.log('100: calling OKC_RULE_PUB.delete_cover_time', 2);
OKC_RULE_PUB.delete_cover_time(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_ctiv_tbl => l_ctiv_tbl);
okc_debug.log('300: calling OKC_RULE_PUB.delete_react_interval', 2);
OKC_RULE_PUB.delete_react_interval(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_rilv_tbl => l_rilv_tbl);
okc_debug.log('600: calling okc_time_pub.delete_timevalues_n_tasks', 2);
okc_time_pub.delete_timevalues_n_tasks(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_tve_id => l_col_vals(i).col_value);
okc_debug.log('800: calling OKC_RUL_PVT.delete_row', 2);
OKC_RUL_PVT.delete_row(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_rulv_rec => p_rulv_rec);
WHEN G_EXCEPTION_CANNOT_DELETE THEN
-- store SQL error message on message stack
OKC_API.SET_MESSAGE(
p_app_name => G_APP_NAME,
p_msg_name => G_CANNOT_DELETE_MASTER);
END delete_rule;
okc_debug.log('100: starting force delete', 2);
okc_debug.log('400: calling OKC_K_REL_OBJS_PUB.Delete_Row', 2);
OKC_K_REL_OBJS_PUB.Delete_Row(
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_crjv_tbl => l_crjv_tbl_in);
okc_debug.log('600: calling OKC_CONTRACT_PARTY_PUB.Delete_Contact', 2);
OKC_CONTRACT_PARTY_PUB.Delete_Contact(
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_ctcv_tbl => l_ctcv_tbl_in);
okc_debug.log('800: calling OKC_CONTRACT_PARTY_PUB.Delete_k_Party_Role', 2);
OKC_CONTRACT_PARTY_PUB.Delete_k_Party_Role(
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_cplv_tbl => l_cplv_tbl_in);
okc_debug.log('1000: calling okc_Rule_pub.delete_Rule', 2);
delete_Rule(
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_rulv_rec => l_rulv_tbl_in(i));
okc_debug.log('1100: calling OKC_CONTRACT_PARTY_PUB.Delete_Contact', 2);
okc_debug.log('1200: calling okc_Rule_pub.delete_Rule_group', 2);
okc_Rule_pub.delete_Rule_group (
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_rgpv_tbl => l_rgpv_tbl_in);
okc_debug.log('1400: calling okc_contract_ITEM_pub.delete_Contract_ITEM', 2);
okc_contract_ITEM_pub.delete_Contract_ITEM (
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_cimv_tbl => l_cimv_tbl_in);
okc_debug.log('1400: calling okc_cle_pvt.force_delete_row', 2);
okc_cle_pvt.force_delete_row (
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_clev_tbl => l_clev_tbl_in);
okc_debug.log('1600: calling delete_ancestry', 2);
delete_ancestry(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_cle_id => l_clev_tbl_in(v_Index).id);
'Delete_Contract_Line',
'OKC_API.G_RET_STS_ERROR',
l_msg_count,
l_msg_data,
'_PVT'
);
'Delete_Contract_Line',
'OKC_API.G_RET_STS_ERROR',
l_msg_count,
l_msg_data,
'_PVT'
);
'Force_Delete_K_Line',
'OKC_API.G_RET_STS_UNEXP_ERROR',
l_msg_count,
l_msg_data,
'_PVT'
);
END force_delete_contract_line;
OKC_GVE_PVT.Insert_Row(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_gvev_rec => p_gvev_rec,
x_gvev_rec => x_gvev_rec);
x_return_status := Update_Minor_Version(x_gvev_rec.dnz_chr_id);
OKC_GVE_PVT.Insert_Row(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_gvev_tbl => p_gvev_tbl,
x_gvev_tbl => x_gvev_tbl);
PROCEDURE update_governance(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 ,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_gvev_rec IN OKC_GVE_PVT.gvev_rec_type,
x_gvev_rec OUT NOCOPY OKC_GVE_PVT.gvev_rec_type) IS
BEGIN
OKC_GVE_PVT.Update_Row(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_gvev_rec => p_gvev_rec,
x_gvev_rec => x_gvev_rec);
x_return_status := Update_Minor_Version(x_gvev_rec.dnz_chr_id);
END update_governance;
PROCEDURE update_governance(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 ,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_gvev_tbl IN OKC_GVE_PVT.gvev_tbl_type,
x_gvev_tbl OUT NOCOPY OKC_GVE_PVT.gvev_tbl_type) IS
BEGIN
OKC_GVE_PVT.Update_Row(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_gvev_tbl => p_gvev_tbl,
x_gvev_tbl => x_gvev_tbl);
END update_governance;
PROCEDURE delete_governance(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 ,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_gvev_rec IN OKC_GVE_PVT.gvev_rec_type) IS
l_chr_id NUMBER;
SELECT dnz_chr_id
FROM OKC_GOVERNANCES
WHERE id = p_gvev_rec.id;
OKC_GVE_PVT.Delete_Row(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_gvev_rec => p_gvev_rec);
x_return_status := Update_Minor_Version(l_chr_id);
END delete_governance;
PROCEDURE delete_governance(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 ,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_gvev_tbl IN OKC_GVE_PVT.gvev_tbl_type) IS
BEGIN
OKC_GVE_PVT.Delete_Row(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_gvev_tbl => p_gvev_tbl);
END delete_governance;
OKC_CPS_PVT.Insert_Row(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_cpsv_rec => p_cpsv_rec,
x_cpsv_rec => x_cpsv_rec);
x_return_status := Update_Minor_Version(p_cpsv_rec.chr_id);
OKC_CPS_PVT.Insert_Row(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_cpsv_tbl => p_cpsv_tbl,
x_cpsv_tbl => x_cpsv_tbl);
PROCEDURE update_contract_process(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 ,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_cpsv_rec IN OKC_CPS_PVT.cpsv_rec_type,
x_cpsv_rec OUT NOCOPY OKC_CPS_PVT.cpsv_rec_type) IS
l_process_active_yn VARCHAR2(1) := 'N';
raise G_NO_UPDATE_ALLOWED_EXCEPTION;
OKC_CPS_PVT.Update_Row(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_cpsv_rec => p_cpsv_rec,
x_cpsv_rec => x_cpsv_rec);
x_return_status := Update_Minor_Version(p_cpsv_rec.chr_id);
when G_NO_UPDATE_ALLOWED_EXCEPTION then
OKC_API.SET_MESSAGE(p_app_name => g_app_name,
p_msg_name => g_no_update_allowed,
p_token1 => 'VALUE1',
p_token1_value => 'Contract Processes');
END update_contract_process;
PROCEDURE update_contract_process(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 ,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_cpsv_tbl IN OKC_CPS_PVT.cpsv_tbl_type,
x_cpsv_tbl OUT NOCOPY OKC_CPS_PVT.cpsv_tbl_type) IS
BEGIN
OKC_CPS_PVT.Update_Row(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_cpsv_tbl => p_cpsv_tbl,
x_cpsv_tbl => x_cpsv_tbl);
END update_contract_process;
PROCEDURE delete_contract_process(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 ,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_cpsv_rec IN OKC_CPS_PVT.cpsv_rec_type) IS
l_process_active_yn VARCHAR2(1) := 'N';
SELECT CHR_ID
FROM OKC_K_PROCESSES
WHERE ID = p_id;
raise G_NO_UPDATE_ALLOWED_EXCEPTION;
OKC_CPS_PVT.Delete_Row(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_cpsv_rec => p_cpsv_rec);
x_return_status := Update_Minor_Version(l_chr_id);
when G_NO_UPDATE_ALLOWED_EXCEPTION then
OKC_API.SET_MESSAGE(p_app_name => g_app_name,
p_msg_name => g_no_update_allowed,
p_token1 => 'VALUE1',
p_token1_value => 'Contract Processes');
END delete_contract_process;
PROCEDURE delete_contract_process(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 ,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_cpsv_tbl IN OKC_CPS_PVT.cpsv_tbl_type) IS
BEGIN
OKC_CPS_PVT.Delete_Row(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_cpsv_tbl => p_cpsv_tbl);
END delete_contract_process;
OKC_CAC_PVT.Insert_Row(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_cacv_rec => p_cacv_rec,
x_cacv_rec => x_cacv_rec);
x_return_status := Update_Minor_Version(x_cacv_rec.chr_id);
OKC_CAC_PVT.Insert_Row(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_cacv_tbl => p_cacv_tbl,
x_cacv_tbl => x_cacv_tbl);
PROCEDURE update_contract_access(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 ,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_cacv_rec IN OKC_CAC_PVT.cacv_rec_type,
x_cacv_rec OUT NOCOPY OKC_CAC_PVT.cacv_rec_type) IS
BEGIN
OKC_CAC_PVT.Update_Row(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_cacv_rec => p_cacv_rec,
x_cacv_rec => x_cacv_rec);
x_return_status := Update_Minor_Version(x_cacv_rec.chr_id);
END update_contract_access;
PROCEDURE update_contract_access(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 ,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_cacv_tbl IN OKC_CAC_PVT.cacv_tbl_type,
x_cacv_tbl OUT NOCOPY OKC_CAC_PVT.cacv_tbl_type) IS
BEGIN
OKC_CAC_PVT.Update_Row(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_cacv_tbl => p_cacv_tbl,
x_cacv_tbl => x_cacv_tbl);
END update_contract_access;
PROCEDURE delete_contract_access(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 ,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_cacv_rec IN OKC_CAC_PVT.cacv_rec_type) IS
l_chr_id NUMBER;
SELECT chr_id
FROM OKC_K_ACCESSES
WHERE id = p_cacv_rec.id;
OKC_CAC_PVT.Delete_Row(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_cacv_rec => p_cacv_rec);
x_return_status := Update_Minor_Version(l_chr_id);
END delete_contract_access;
PROCEDURE delete_contract_access(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 ,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_cacv_tbl IN OKC_CAC_PVT.cacv_tbl_type) IS
BEGIN
OKC_CAC_PVT.Delete_Row(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_cacv_tbl => p_cacv_tbl);
END delete_contract_access;
SELECT id
FROM OKC_K_HEADERS_B
WHERE contract_number = p_contract_number
AND contract_number_modifier = p_contract_number_modifier;
SELECT id
FROM OKC_K_HEADERS_B
WHERE contract_number = p_contract_number
AND contract_number_modifier is null;
SELECT
usage,
name,
wf_process_name,
procedure_name,
package_name
FROM okc_process_defs_v pdfv,
okc_k_processes cpsv
WHERE pdfv.id = cpsv.pdf_id
AND cpsv.chr_id = l_chr_id
AND cpsv.last_update_date = (SELECT MAX(last_update_date)
FROM okc_k_processes
WHERE chr_id = l_chr_id);
FUNCTION Update_Allowed(p_chr_id IN NUMBER) RETURN VARCHAR2 Is
l_sts_code OKC_ASSENTS.STS_CODE%TYPE;
SELECT sts_code, scs_code
--npalepu 26-10-2005 modified for bug # 4691662.
--Replaced table okc_k_headers_b with headers_All_b table
/* FROM OKC_K_HEADERS_B */
FROM OKC_K_HEADERS_ALL_B
--end npalepu
WHERE id = p_chr_id;
SELECT upper(substr(allowed_yn,1,1))
FROM okc_assents
WHERE sts_code = l_sts_code
AND scs_code = l_scs_code
AND opn_code = 'UPDATE';
END Update_Allowed;
SELECT distinct ol.object_chr_id
FROM okc_operation_instances op
, okc_class_operations cls
, okc_subclasses_b sl
, okc_operation_lines ol
WHERE ol.subject_chr_id = p_target_chr_id
And op.id = ol.oie_id
AND op.cop_id = cls.id
And cls.cls_code = sl.cls_code
And sl.code = 'SERVICE'
And cls.opn_code in ('RENEWAL','REN_CON');
/*SELECT distinct object_chr_id
FROM okc_operation_lines
WHERE subject_chr_id = p_target_chr_id;
SELECT count(*)
FROM okc_operation_lines
WHERE SUBJECT_CHR_ID = p_subject_chr_id
AND OBJECT_CHR_ID = p_object_chr_id
AND SUBJECT_CLE_ID is not null;
SELECT count(*)
FROM okc_k_lines_b
WHERE id IN (SELECT OBJECT_CLE_ID
FROM okc_operation_lines
WHERE SUBJECT_CHR_ID = p_subject_chr_id
AND OBJECT_CHR_ID = p_object_chr_id)
AND dnz_chr_id = p_object_chr_id;
UPDATE OKC_K_HEADERS_B
SET date_renewed = null,
object_version_number = object_version_number + 1,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_date = sysdate,
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE id in (
SELECT distinct object_chr_id
FROM okc_operation_lines
WHERE subject_chr_id = p_target_chr_id
AND active_yn = 'Y');
UPDATE OKC_K_HEADERS_B
SET date_renewed = sysdate,
object_version_number = object_version_number + 1,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_date = sysdate,
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE id = ole_rec.object_chr_id;
UPDATE okc_operation_lines ol
SET active_yn = l_active_yn,
object_version_number = object_version_number + 1,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_date = sysdate,
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE subject_chr_id = p_target_chr_id
AND subject_cle_id is null
AND object_cle_id is null
And exists(Select 'x' --Fix Bug 4948793
FROM okc_operation_instances op
, okc_class_operations cls
, okc_subclasses_b sl
WHERE op.id = ol.oie_id
AND op.cop_id = cls.id
And cls.cls_code = sl.cls_code
And sl.code = 'SERVICE'
And cls.opn_code in ('RENEWAL','REN_CON') );
UPDATE OKC_K_LINES_B
SET date_renewed = decode(l_active_yn,'Y',sysdate,null),
object_version_number = object_version_number + 1,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_date = sysdate,
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE id in (Select ol.object_cle_id --Fix Bug 4948793
FROM okc_operation_instances op
, okc_class_operations cls
, okc_subclasses_b sl
, okc_operation_lines ol
WHERE ol.subject_chr_id = p_target_chr_id
And ol.object_cle_id is not null
And op.id = ol.oie_id
AND op.cop_id = cls.id
And cls.cls_code = sl.cls_code
And sl.code = 'SERVICE'
And cls.opn_code in ('RENEWAL','REN_CON') );
UPDATE okc_operation_lines ol
SET active_yn = l_active_yn,
object_version_number = object_version_number + 1,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_date = sysdate,
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE subject_chr_id = p_target_chr_id
AND subject_cle_id is not null
AND object_cle_id is not null
And exists(Select 'x' --Fix Bug 4948793
FROM okc_operation_instances op
, okc_class_operations cls
, okc_subclasses_b sl
WHERE op.id = ol.oie_id
AND op.cop_id = cls.id
And cls.cls_code = sl.cls_code
And sl.code = 'SERVICE'
And cls.opn_code in ('RENEWAL','REN_CON') );
x_return_status := update_minor_version(ole_rec.object_chr_id);
SELECT display_sequence from
OKC_K_LINES_B
connect by prior cle_id = id
start with id = p_cle_id;
SELECT cls_code from
OKC_SUBCLASSES_B WHERE code=(SELECT scs_code from OKC_K_HEADERS_B where id in
(select dnz_chr_id from okc_k_lines_b where id =p_cle_id));
SELECT clev.cle_id,RTRIM(clev.line_number) line_number,clev.lse_id
FROM OKC_K_LINES_V clev
WHERE clev.id = p_cle_id;
SELECT line_number
FROM OKC_K_LINES_B
WHERE id = p_line_id;
SELECT line_number "LINE_NAME"
FROM OKC_LINE_STYLES_V lsev,
OKC_K_LINES_V clev
WHERE lsev.id = clev.lse_id
AND clev.id = p_cle_id;
The Header and Line Amounts should be updated when Change Status action is taken
at the header/line/subline level. This is to ensure that the calualated amounts
(price_negotiated, cancelled_amount, estimated_amount) ignores cancelled lines/sublines.
A new procedure Update_Contract_Amount is created which is called
when cancel actions is taken at header/line/subline level.
*/
PROCEDURE UPDATE_CONTRACT_AMOUNT (
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
p_id IN NUMBER,
p_from_ste_code IN VARCHAR2,
p_to_ste_code IN VARCHAR2,
p_cle_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2 )
IS
l_cle_id Number := NULL;
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_CONTRACT_AMOUNT';
Select cle_id
from okc_k_lines_b
where id = p_cle_id
and dnz_chr_id = p_id;
Select cle.price_negotiated
from okc_k_lines_b cle
where cle.id = p_cle_id
and cle.dnz_chr_id = p_id;
select nvl(sum(nvl(price_negotiated,0)),0), nvl(sum(nvl(cancelled_amount,0)),0)
from okc_k_lines_b cle
where cle.dnz_chr_id = p_id
and cle.lse_id in (1, 12, 14, 19, 46)
and cle.cle_id is null;
select lse_id
from okc_k_lines_b
where id=p_cle_id;
select nvl(sum(nvl(price_negotiated,0)),0)
from okc_k_lines_b
where cle_id = p_cle_id
and dnz_chr_id = p_id
and date_cancelled is null;
SELECT id,lse_id,cancelled_amount
FROM okc_k_lines_b
WHERE dnz_chr_id = p_id
and cle_id is null
and term_cancel_source is null;
select cle.cancelled_amount
from okc_k_lines_b cle
where cle.id = p_cle_id
and cle.dnz_chr_id = p_id
and cle.lse_id in (7, 8, 9, 10, 11, 13, 18, 25, 35) ;
select okslb.id,okslb.price_negotiated
from okc_k_lines_b cle,
okc_k_lines_b okslb
where cle.id = p_cle_id
and okslb.lse_id in (7, 8, 9, 10, 11, 13, 18, 25, 35)
and okslb.cle_id =cle.id
and okslb.term_cancel_source IN ('MANUAL','CUSTOMER') ----Modified condition for bug 12956286
and okslb.date_cancelled is not null;
select okslb.id,okslb.cancelled_amount
from okc_k_lines_b cle,
okc_k_lines_b okslb
where cle.id = p_cle_id
and okslb.lse_id in (7, 8, 9, 10, 11, 13, 18, 25, 35)
and okslb.cle_id =cle.id
and okslb.date_cancelled is null;
select oklb.id,oklb.price_negotiated
from okc_k_lines_b oklb,
okc_k_headers_all_b okhb
where oklb.chr_id = okhb.id
and okhb.id = p_id
and oklb.lse_id in (1,12,14,19,46);
okc_debug.log('2200: Entered UPDATE_CONTRACT_AMOUNT');
Update okc_k_lines_b
set price_negotiated = nvl(price_negotiated,0) - nvl(l_sub_line_amt,0),
cancelled_amount = nvl(cancelled_amount,0) + nvl(l_sub_line_amt,0)
Where id = l_cle_id -- top line id
and dnz_chr_id = p_id;
Update okc_k_lines_b
set price_negotiated = nvl(price_negotiated,0) - nvl(l_sub_line_amt,0),
cancelled_amount = nvl(cancelled_amount,0) + nvl(l_sub_line_amt,0)
Where cle_id = l_cle_id
and id= p_cle_id
and dnz_chr_id = p_id;
Update okc_k_lines_b
set price_negotiated = nvl(price_negotiated,0) + nvl(l_can_line_amt,0),
cancelled_amount = nvl(cancelled_amount,0) - nvl(l_can_line_amt,0)
Where cle_id = l_cle_id
and id= p_cle_id
and dnz_chr_id = p_id;
Update okc_k_lines_b
set price_negotiated = nvl(price_negotiated,0) + nvl(l_can_line_amt,0), /*Changed for bug:8775250*/
cancelled_amount = nvl(cancelled_amount,0) - nvl(l_can_line_amt,0)
Where id = l_cle_id -- top line id
And dnz_chr_id = p_id;
Update okc_k_lines_b
set cancelled_amount = nvl(cancelled_amount, 0) + nvl(price_negotiated, 0),
price_negotiated = 0
Where id = p_cle_id
and dnz_chr_id = p_id;
Update okc_k_lines_b
set price_negotiated = nvl(price_negotiated,0) - nvl(get_subline_price_csr_rec.price_negotiated,0),
cancelled_amount = nvl(cancelled_amount,0) + nvl(get_subline_price_csr_rec.price_negotiated,0)
Where cle_id = p_cle_id
and id= get_subline_price_csr_rec.id
and dnz_chr_id = p_id;
Update okc_k_lines_b
set price_negotiated = nvl(cancelled_amount,0),
cancelled_amount = 0
Where id = p_cle_id
and dnz_chr_id = p_id;
Update okc_k_lines_b
set price_negotiated = nvl(price_negotiated,0) + nvl(get_subline_cancel_csr_rec.cancelled_amount,0),
cancelled_amount = nvl(cancelled_amount,0) - nvl(get_subline_cancel_csr_rec.cancelled_amount,0)
Where cle_id = p_cle_id
and id= get_subline_cancel_csr_rec.id
and dnz_chr_id = p_id;
Update okc_k_lines_b
set price_negotiated = nvl(price_negotiated, 0) + nvl(l_uncancelled_amt,0),
cancelled_amount = nvl(cancelled_amount,0) - nvl(l_uncancelled_amt,0)
Where id = p_cle_id
and dnz_chr_id = p_id;
update okc_k_lines_b
set cancelled_amount = nvl(cancelled_amount, 0) + nvl(price_negotiated, 0),
price_negotiated = 0
where dnz_chr_id = p_id
and cle_id is NULL
and lse_id in (1, 12, 14, 19, 46);
Update okc_k_lines_b
set price_negotiated = nvl(price_negotiated,0) - nvl(get_subline_price_csr_rec.price_negotiated,0),
cancelled_amount = nvl(cancelled_amount,0) + nvl(get_subline_price_csr_rec.price_negotiated,0)
Where cle_id = get_lines_id_rec.id
and id= get_subline_price_csr_rec.id
and dnz_chr_id = p_id;
Update okc_k_lines_b
set price_negotiated = nvl(price_negotiated,0) + nvl(get_subline_cancel_csr_rec.cancelled_amount,0),
cancelled_amount = nvl(cancelled_amount,0) - nvl(get_subline_cancel_csr_rec.cancelled_amount,0)
Where cle_id = get_lines_id_rec.id
and id= get_subline_cancel_csr_rec.id
and dnz_chr_id = p_id;
Update okc_k_lines_b
set price_negotiated = nvl(price_negotiated,0) + nvl(topline.cancelled_amount,0),
cancelled_amount = nvl(cancelled_amount,0) - nvl(topline.cancelled_amount,0)
Where id = topline.id
and dnz_chr_id = p_id;
-- updating price_negotiated and cancelled_amount for selected top line
Update okc_k_lines_b
set price_negotiated = nvl(price_negotiated, 0) + nvl(l_uncancelled_amt,0),
cancelled_amount = nvl(cancelled_amount,0) - nvl(l_uncancelled_amt,0)
Where id = topline.id
and dnz_chr_id = p_id;
update okc_k_lines_b
set price_negotiated = nvl(price_negotiated, 0) + nvl(cancelled_amount, 0),
cancelled_amount = 0
where dnz_chr_id = p_id
and cle_id is NULL
and lse_id in (1, 12, 14, 19, 46);
Update okc_k_headers_b
set estimated_amount = l_hdr_estimated_amt,
cancelled_amount = l_hdr_cancelled_amt
where id = p_id;
END UPDATE_CONTRACT_AMOUNT;
SELECT cancelled_amount
FROM okc_k_headers_b
WHERE id = p_id;
SELECT nvl(cancelled_amount, 0)
FROM okc_k_lines_b
WHERE id = p_cle_id
and dnz_chr_id = p_id;
SELECT nvl(price_negotiated, 0)
FROM okc_k_lines_b
WHERE id = p_cle_id
and dnz_chr_id = p_id
and date_cancelled is not null;
SELECT cle_id
FROM okc_k_lines_b
WHERE id = p_cle_id
and dnz_chr_id = p_id;
Select term_cancel_source, object_cle_id
from okc_k_lines_b olb, okc_operation_lines opl
where olb.id= p_target_line_id
and ( ( opl.object_cle_id = olb.id )
OR
(opl.subject_cle_id= olb.id )
);
UPDATE okc_operation_lines
SET active_yn = l_active_yn,
object_version_number = object_version_number + 1,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_date = sysdate,
last_update_login = FND_GLOBAL.LOGIN_ID
where subject_cle_id in ( select id
from okc_k_lines_b kle1
start with kle1.id = p_target_line_id
connect by prior kle1.id = kle1.cle_id
and kle1.dnz_chr_id = p_target_chr_id)
and subject_chr_id = p_target_chr_id;
UPDATE OKC_K_LINES_B
SET date_renewed = decode(l_active_yn,'Y',sysdate,null),
object_version_number = object_version_number + 1,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_date = sysdate,
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE id in
( SELECT object_cle_id
FROM okc_operation_lines ol1
WHERE ol1.subject_cle_id = p_target_line_id );
UPDATE OKC_K_LINES_B
SET date_renewed = decode(l_active_yn,'Y',sysdate,null),
object_version_number = object_version_number + 1,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_date = sysdate,
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE cle_id in
( SELECT object_cle_id
FROM okc_operation_lines ol1
WHERE ol1.subject_cle_id = p_target_line_id );
x_return_status := update_minor_version(p_target_chr_id);