[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT ID from OKC_CLASS_OPERATIONS
WHERE OPN_CODE = 'REN_CON' and CLS_CODE = 'SERVICE';
SELECT subject_chr_id
FROM okc_operation_lines
WHERE object_cle_id = source_line_id
AND process_flag = 'P'
AND active_yn = 'Y';
SELECT 'X' x
FROM okc_operation_lines
WHERE subject_chr_id = p_subject_chr_id
AND object_cle_id = p_object_cle_id;
PROCEDURE SET_OL_SELECTED(p_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2) IS
CURSOR parent_cur IS
SELECT id, select_yn
FROM okc_operation_lines
WHERE id = (SELECT parent_ole_id
FROM okc_operation_lines
WHERE id = p_id);
FND_LOG.string(FND_LOG.level_statement, l_mod_name,' SET_OL_SELECTED p_ole_id = '|| p_ole_id);
l_olev_tbl_in(1).select_yn := 'Y';
OKC_OPER_INST_PUB.Update_Operation_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_olev_tbl => l_olev_tbl_in,
x_olev_tbl => l_olev_tbl_out );
FND_LOG.string(FND_LOG.level_statement, l_mod_name,'OKC_OPER_INST_PUB.Update_Operation_Line l_return_status = ' || l_return_status);
FND_LOG.string(FND_LOG.level_statement, l_mod_name,'OKC_OPER_INST_PUB.Update_Operation_Line l_msg_data = ' || l_msg_data);
FND_LOG.string(FND_LOG.level_statement, l_mod_name,'OKC_OPER_INST_PUB.Update_Operation_Line l_msg_data = ' || l_msg_data);
END SET_OL_SELECTED;
p_select_yn IN VARCHAR2 DEFAULT 'N')
IS
l_api_name CONSTANT VARCHAR2(30) := 'GET_VALID_OPER_LINE_SOURCES';
SELECT
h.scs_code,
h.start_date,
h.end_date,
h.org_id, --mmadhavi MOAC : changed to org_id
h.inv_organization_id,
pr.object1_id1,
h.currency_code
FROM okc_K_Headers_all_b h, --mmadhavi MOAC - changed to _ALL_B for performance
okc_k_party_roles_b pr,
okc_k_party_roles_b pr1,
okc_statuses_b st
WHERE h.id = p_target_id
AND h.id = pr.dnz_chr_id AND pr.cle_id IS NULL
AND pr.rle_code = 'CUSTOMER'
AND h.id = pr1.dnz_chr_id AND pr1.cle_id IS NULL
AND pr1.rle_code = 'VENDOR'
AND h.scs_code IN ('SERVICE', 'WARRANTY')
AND h.template_yn = x_template_yn
AND h.buy_or_sell = x_buy_or_sell
AND h.issue_or_receive = x_issue_or_receive
AND h.chr_type = x_chr_type
AND h.sts_code = st.code
AND st.ste_code = 'ENTERED'
FOR UPDATE OF h.scs_code NOWAIT;
SELECT opl.id, oie_id, object_chr_id, object_cle_id, parent_ole_id, select_yn
FROM okc_operation_lines OPL,
okc_k_lines_b cle
WHERE OPL.subject_chr_id = p_target_id
AND EXISTS (SELECT 'X'
FROM okc_operation_instances OPI
WHERE OPI.id = OPL.oie_id
AND OPI.cop_id = G_RENCON_CLASS_OPERATION_ID -- Bug#5981381: Use cached class_operation_id
/* (SELECT ID
FROM OKC_CLASS_OPERATIONS
WHERE OPN_CODE = 'REN_CON'
AND CLS_CODE = 'SERVICE'
)
*/
)
and cle.id = opl.object_cle_id
and NVL(line_renewal_type_code,'FUL') not in ('KEP','DNR') -- bug 5078797
and date_terminated is null
and cle_id is not null
AND exists
(SELECT 1 from okc_k_lines_b cle1
WHERE cle1.id = cle.cle_id
AND NVL(cle1.line_renewal_type_code,'FUL') not in ('DNR', 'KEP'))
/*
MINUS
(SELECT id, oie_id, object_chr_id, object_cle_id, parent_ole_id, select_yn
FROM okc_operation_lines
WHERE subject_chr_id = p_target_id
AND parent_ole_id IS NULL
UNION
SELECT a.id, a.oie_id, a.object_chr_id, a.object_cle_id, a.parent_ole_id, a.select_yn
FROM okc_operation_lines a, okc_operation_lines b
WHERE a.subject_chr_id = p_target_id
AND b.subject_chr_id = p_target_id
AND a.id = b.parent_ole_id)
*/
-- Added following code for bug#5981381 i.e. removed looping of function call of not_processed_by_other
-- and moved the entire code from the cursor to here
AND NOT EXISTS
( SELECT '1' FROM okc_operation_lines
WHERE object_cle_id = opl.object_cle_id
AND subject_chr_id <> p_target_id
AND process_flag = 'P'
AND active_yn = 'Y')
ORDER BY parent_ole_id;
SELECT h.id
FROM okc_K_Headers_all_b h, --mmadhavi MOAC - changed to _ALL_B for performance
okc_k_party_roles_b pr,
okc_statuses_b st
, okc_k_lines_b s
WHERE h.id = l_source_chr_id
AND h.scs_code IN ('WARRANTY', x_scs_code)
AND h.chr_type = x_chr_type
AND h.template_yn = x_template_yn
AND h.buy_or_sell = x_buy_or_sell
AND h.issue_or_receive = x_issue_or_receive
AND h.end_date BETWEEN x_start_date AND x_end_date
AND h.inv_organization_id = x_inv_organization_id
AND h.org_id = x_org_id
AND h.id = pr.dnz_chr_id AND pr.cle_id IS NULL
AND pr.rle_code = 'CUSTOMER'
AND pr.object1_id1 = x_party_id
--and h.currency_code = x_currency_code
AND (
(x_currency_code <> 'EUR' AND x_currency_code = h.currency_code)
OR
(x_currency_code = 'EUR' AND (h.currency_code = 'EUR' OR
OKC_CURRENCY_API.IS_EURO_CONVERSION_NEEDED(h.currency_code) = 'Y' )
)
)
--and h.date_renewed is NULL
AND h.sts_code = st.code
AND st.ste_code IN ('ACTIVE', 'EXPIRED', 'SIGNED')
AND s.dnz_chr_id = h.id
AND s.id = l_source_subline_id
AND s.end_date BETWEEN x_start_date AND x_end_date
AND s.cle_id IS NOT NULL
--AND s.date_renewed is NULL
AND s.lse_id IN (35, 7, 8, 9, 10, 11, 25);
SELECT 1
FROM okc_operation_lines OPL,
okc_k_lines_b KLN
WHERE OPL.id = cp_opl_id
AND OPL.object_cle_id = KLN.id
AND KLN.date_terminated IS NOT NULL;
x_sources_tbl(i).select_yn := cur_operation_lines.select_yn;
IF p_select_yn = 'Y' AND NVL(cur_operation_lines.select_yn, 'N') = 'N' THEN
set_ol_selected(p_id => cur_operation_lines.id,
x_return_status => x_return_status);
FND_LOG.string(FND_LOG.level_statement, l_mod_name,'set_ol_selected l_return_status: '|| x_return_status);
x_sources_tbl(i).select_yn := 'Y';
FND_LOG.string(FND_LOG.level_statement, l_mod_name,'Unable to set select_yn to Y ');
p_select_yn IN VARCHAR2 DEFAULT 'N')
IS
l_api_name CONSTANT VARCHAR2(30) := 'GET_VALID_LINE_SOURCES';
SELECT
h.scs_code,
h.start_date,
h.end_date,
h.org_id, --mmadhavi MOAC : changed to org_id
h.inv_organization_id,
pr.object1_id1,
h.currency_code
FROM okc_K_Headers_all_b h, --mmadhavi MOAC - changed to _ALL_B for performance
okc_k_party_roles_b pr,
okc_k_party_roles_b pr1,
okc_statuses_b st
WHERE h.id = p_target_id
AND h.id = pr.dnz_chr_id AND pr.cle_id IS NULL
AND pr.rle_code = 'CUSTOMER'
AND h.id = pr1.dnz_chr_id AND pr1.cle_id IS NULL
AND pr1.rle_code = 'VENDOR'
AND h.scs_code IN ('SERVICE', 'WARRANTY')
AND h.template_yn = x_template_yn
AND h.buy_or_sell = x_buy_or_sell
AND h.issue_or_receive = x_issue_or_receive
AND h.chr_type = x_chr_type
AND h.sts_code = st.code
AND st.ste_code = 'ENTERED'
FOR UPDATE OF h.scs_code NOWAIT;
SELECT
h.id
FROM okc_K_Headers_all_b h, --mmadhavi MOAC - changed to _ALL_B for performance
okc_k_party_roles_b pr,
okc_statuses_b st
WHERE h.scs_code IN ('WARRANTY', x_scs_code)
AND h.chr_type = x_chr_type
AND h.template_yn = x_template_yn
AND h.buy_or_sell = x_buy_or_sell
AND h.issue_or_receive = x_issue_or_receive
AND h.end_date BETWEEN x_start_date AND x_end_date
AND h.inv_organization_id = x_inv_organization_id
AND h.org_id = x_org_id --mmadhavi MOAC : changed to org_id
AND h.id = pr.dnz_chr_id AND pr.cle_id IS NULL
AND pr.rle_code = 'CUSTOMER'
AND pr.object1_id1 = x_party_id
AND h.currency_code = x_currency_code
--and h.date_renewed is NULL
AND h.sts_code = st.code
AND st.ste_code IN ('ACTIVE', 'EXPIRED', 'SIGNED');
SELECT
h.id
FROM okc_K_Headers_all_b h, --mmadhavi MOAC - changed to _ALL_B for performance
okc_k_party_roles_b pr,
okc_statuses_b st
WHERE h.scs_code IN ('WARRANTY', x_scs_code)
AND h.chr_type = x_chr_type
AND h.template_yn = x_template_yn
AND h.buy_or_sell = x_buy_or_sell
AND h.issue_or_receive = x_issue_or_receive
AND h.end_date BETWEEN x_start_date AND x_end_date
AND h.inv_organization_id = x_inv_organization_id
AND h.org_id = x_org_id --mmadhavi MOAC : changed to org_id
AND h.id = pr.dnz_chr_id AND pr.cle_id IS NULL
AND pr.rle_code = 'CUSTOMER'
AND pr.object1_id1 = x_party_id
AND (h.currency_code = 'EUR' OR
OKC_CURRENCY_API.IS_EURO_CONVERSION_NEEDED(h.currency_code) = 'Y' )
--and h.date_renewed is NULL
AND h.sts_code = st.code
AND st.ste_code IN ('ACTIVE', 'EXPIRED', 'SIGNED');
SELECT s.id,
s.cle_id
FROM okc_k_lines_b s
WHERE s.dnz_chr_id = p_chr_id
AND s.end_date BETWEEN x_start_date AND x_end_date
--AND s.date_renewed is NULL
AND s.date_terminated IS NULL
AND s.date_cancelled IS NULL
AND s.lse_id IN (35, 7, 8, 9, 10, 11, 25)
AND NVL(s.line_renewal_type_code,'FUL') not in ('DNR', 'KEP') -- Added by MKS
AND exists
(SELECT 1 from okc_k_lines_b cle
WHERE cle.id = s.cle_id
AND NVL(cle.line_renewal_type_code,'FUL') not in ('DNR', 'KEP')) -- Added by MKS
-- Added following code for bug#5981381 i.e. removed looping of function call of is_laready_in_ol
-- and moved the entire code from that function to here
AND NOT EXISTS
( SELECT '1' FROM okc_operation_lines
WHERE subject_chr_id = p_target_chr_id
AND object_cle_id = s.id) ;
SELECT id FROM okc_operation_instances
WHERE target_chr_id = p_target_id
AND cop_id = G_RENCON_CLASS_OPERATION_ID; -- Bug#5981381: Use cached class_operation_id
/*(SELECT ID FROM OKC_CLASS_OPERATIONS
WHERE OPN_CODE = 'REN_CON' AND CLS_CODE = 'SERVICE');
FND_LOG.string(FND_LOG.level_statement, l_mod_name,'SELECTING ELIGIBLE SUBLINES valid_header_sources_rec.id = ' || valid_header_sources_rec.id);
x_sources_tbl(i).select_yn := p_select_yn;
FND_LOG.string(FND_LOG.level_statement, l_mod_name,'SELECTING ELIGIBLE SUBLINES valid_header_sources_rec.id = ' || valid_header_sources_rec.id);
x_sources_tbl(i).select_yn := p_select_yn;
x_sources_tbl.DELETE;
p_select_yn => p_select_yn);
SELECT ID FROM OKC_CLASS_OPERATIONS
WHERE OPN_CODE = 'REN_CON' AND CLS_CODE = 'SERVICE';
l_oiev_tbl_in(1).last_updated_by := OKC_API.G_MISS_NUM;
l_oiev_tbl_in(1).last_update_date := SYSDATE;
l_oiev_tbl_in(1).last_update_login := OKC_API.G_MISS_NUM;
p_select_yn IN VARCHAR2 DEFAULT 'N') IS
----------------------------------------------------------------------------
---TAPI variables
----------------------------------------------------------------------------
l_api_version CONSTANT NUMBER := 1.0;
SELECT id FROM OKC_OPERATION_LINES
WHERE oie_id = p_oie_id
AND object_chr_id = p_header_id
AND object_cle_id IS NULL;
SELECT id FROM OKC_OPERATION_LINES
WHERE oie_id = p_oie_id
AND object_cle_id = p_topline_id;
l_olev_tbl_in(i).select_yn := p_select_yn;
l_olev_tbl_in(i).last_updated_by := OKC_API.G_MISS_NUM;
l_olev_tbl_in(i).last_update_date := SYSDATE;
l_olev_tbl_in(i).last_update_login := OKC_API.G_MISS_NUM;
l_olev_tbl_in(i).program_update_date := OKC_API.G_MISS_DATE;
FND_LOG.string(FND_LOG.level_statement, l_mod_name,'Update the v_id_list_k with the operation_lines_id');
l_olev_tbl_in(i).select_yn := p_select_yn;
l_olev_tbl_in(i).last_updated_by := OKC_API.G_MISS_NUM;
l_olev_tbl_in(i).last_update_date := SYSDATE;
l_olev_tbl_in(i).last_update_login := OKC_API.G_MISS_NUM;
l_olev_tbl_in(i).program_update_date := OKC_API.G_MISS_DATE;
FND_LOG.string(FND_LOG.level_statement, l_mod_name,'Update the v_id_list with the operation_lines_id');
l_olev_tbl_in.DELETE;
l_olev_tbl_in(i).select_yn := p_select_yn;
l_olev_tbl_in(i).last_updated_by := OKC_API.G_MISS_NUM;
l_olev_tbl_in(i).last_update_date := SYSDATE;
l_olev_tbl_in(i).last_update_login := OKC_API.G_MISS_NUM;
l_olev_tbl_in(i).program_update_date := OKC_API.G_MISS_DATE;
SELECT process_flag
FROM okc_operation_lines
WHERE object_cle_id = p_object_cle_id
AND active_yn = 'Y';
SELECT
'X'
FROM okc_K_Headers_all_b h,
okc_k_party_roles_b pr,
okc_k_party_roles_b pr1,
okc_statuses_b st
WHERE h.id = p_target_id
AND h.id = pr.dnz_chr_id AND pr.cle_id IS NULL
AND pr.rle_code = 'CUSTOMER'
AND h.id = pr1.dnz_chr_id AND pr1.cle_id IS NULL
AND pr1.rle_code = 'VENDOR'
AND h.scs_code IN ('SERVICE', 'WARRANTY')
AND h.template_yn = 'N'
AND h.buy_or_sell = 'S'
AND h.issue_or_receive = 'I'
AND h.chr_type = 'CYA'
AND h.sts_code = st.code
AND st.ste_code = 'ENTERED';
SELECT object_cle_id
FROM okc_operation_lines
WHERE p_parent_ole_id = id;
SELECT NVL(LINE_RENEWAL_TYPE_CODE,'FUL') LINE_RENEWAL_TYPE_CODE
FROM OKC_K_LINES_B
WHERE ID = p_line_id;
SELECT object1_id1, object1_id2
FROM okc_k_items
WHERE cle_id = p_line_id;
SELECT NVL(LINE_RENEWAL_TYPE_CODE,'FUL') LINE_RENEWAL_TYPE_CODE, BILL_TO_SITE_USE_ID, START_DATE, END_DATE
FROM OKC_K_LINES_B
WHERE ID = p_line_id;
SELECT id
FROM okc_k_lines_b
WHERE dnz_chr_id = p_target_contract_id
AND cle_id IS NULL
AND lse_id IN (1, 12, 14, 19);
SELECT currency_code
FROM okc_k_headers_all_b
WHERE id = p_chr_id;
SELECT object_cle_id FROM okc_operation_lines ol, okc_k_lines_b cle
WHERE ol.parent_ole_id = p_id
and ol.process_flag IN ('A','E')
and ol.select_yn = 'Y'
and cle.id = ol.object_cle_id
and cle.date_terminated is null
and NVL(cle.line_renewal_type_code,'FUL') not in ('DNR', 'KEP'); -- bug 5078797
SELECT * FROM okc_operation_lines
WHERE parent_ole_id = p_id
AND process_flag IN ('A', 'E')
AND select_yn = 'Y';
SELECT org_id FROM okc_k_headers_all_b WHERE
id = (SELECT target_chr_id FROM okc_operation_instances WHERE id = cp_oie_id);
SELECT b. * FROM okc_operation_lines a, okc_operation_lines b
WHERE a.oie_id = p_oie_id
AND b.oie_id = p_oie_id
AND a.id = b.parent_ole_id
AND a.parent_ole_id IS NULL
AND b.process_flag IN ('A', 'E')
AND b.select_yn = 'Y';
SELECT b.*,
chr.inv_organization_id,
chr.authoring_org_id,
chr.currency_code
FROM okc_operation_lines b,
okc_k_lines_b cle,
okc_k_headers_all_b chr,
okc_statuses_b st
WHERE b.oie_id = p_oie_id
and cle.id = b.object_cle_id
and cle.cle_id is null
and b.process_flag IN ('A','E')
and b.select_yn = 'Y'
and cle.date_terminated is NULL
and NVL(cle.line_renewal_type_code,'FUL') not in ('DNR', 'KEP') -- bug 5078797
and chr.id = cle.dnz_chr_id
and chr.sts_code = st.code
and st.ste_code in ('ACTIVE','EXPIRED','SIGNED')
and nvl(renewal_type_code, G_GCD_RENEWAL_TYPE) <> 'DNR';
SELECT * FROM okc_operation_lines
WHERE parent_ole_id = p_id
AND process_flag IN ('A', 'E')
AND select_yn = 'Y';
SELECT ol.*, lse_id, start_date, end_date FROM okc_operation_lines ol,
okc_k_lines_b cle
WHERE parent_ole_id = p_id
and process_flag IN ('A','E')
and select_yn = 'Y'
and cle.id = ol.object_cle_id
and cle.date_terminated is NULL
and NVL(cle.line_renewal_type_code,'FUL') not in ('DNR', 'KEP') -- bug 5078797
and NOT EXISTS
( SELECT 'x'
FROM okc_operation_lines
WHERE object_cle_id = ol.object_cle_id
AND subject_chr_id <> ol.subject_chr_id
AND process_flag = 'P'
AND active_yn = 'Y'
); -- bug 5085556
SELECT inv_organization_id, org_id, currency_code --mmadhavi changed to Org_id for MOAC
FROM okc_k_headers_all_b
WHERE id = p_header_id;
select lse_id,start_date,end_date
from okc_k_lines_b
where id =p_subline_id;
SELECT
h.start_date,
h.authoring_org_id,
h.inv_organization_id,
pr.object1_id1,
h.currency_code
FROM okc_K_Headers_all_b h,
okc_k_party_roles_b pr,
okc_statuses_b st
WHERE
h.id=pr.dnz_chr_id and pr.cle_id is null
and pr.rle_code = 'CUSTOMER'
and h.scs_code IN ('SERVICE','WARRANTY')
and h.template_yn = 'N'
and h.sts_code = st.code
and st.ste_code = 'ENTERED'
and h.id in
(select target_chr_id from okc_operation_instances where id = p_oie_id);
l_update_date DATE;
l_subject_sub_line_tbl.delete;
FND_LOG.string(FND_LOG.level_statement, l_mod_name || '.top_line_billing','Calling OKS_BILL_UTIL_PUB.delete_slh_rule, p_cle_id='|| l_cle_id);
LOG_MESSAGES('Calling OKS_BILL_UTIL_PUB.delete_slh_rule');
OKS_BILL_UTIL_PUB.delete_slh_rule(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_cle_id => l_cle_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
LOG_MESSAGES('After Calling OKS_BILL_UTIL_PUB.delete_slh_rule l_return_status : '||l_return_status);
FND_LOG.string(FND_LOG.level_statement, l_mod_name || '.top_line_billing','After call to OKS_BILL_UTIL_PUB.delete_slh_rule, l_return_status='|| l_return_status);
l_subject_sub_line_tbl.delete;
FND_LOG.string(FND_LOG.level_statement, l_mod_name || '.sub_line_billing','Calling OKS_BILL_UTIL_PUB.delete_slh_rule, p_cle_id='|| s_cle_id);
LOG_MESSAGES('Calling OKS_BILL_UTIL_PUB.delete_slh_rule');
OKS_BILL_UTIL_PUB.delete_slh_rule(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_cle_id => s_cle_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
LOG_MESSAGES('After Calling OKS_BILL_UTIL_PUB.delete_slh_rule, l_return_status: '||l_return_status);
FND_LOG.string(FND_LOG.level_statement, l_mod_name || '.sub_line_billing','After call to OKS_BILL_UTIL_PUB.delete_slh_rule, l_return_status='|| l_return_status);
* Bug 6114024 Call moved after update to operation lines
OKS_REPRICE_PVT.call_pricing_api(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_subject_chr_id => l_target_header_id,
p_subject_top_line_id => l_cle_id,
p_subject_sub_line_tbl => l_subject_sub_line_tbl );
l_subject_sub_line_tbl.delete;
FND_LOG.string(FND_LOG.level_statement, l_mod_name || '.sub_line_billing','Calling OKS_BILL_UTIL_PUB.delete_slh_rule, p_cle_id='|| s_cle_id);
LOG_MESSAGES('Calling OKS_BILL_UTIL_PUB.delete_slh_rule');
OKS_BILL_UTIL_PUB.delete_slh_rule(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_cle_id => s_cle_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
LOG_MESSAGES('After Calling OKS_BILL_UTIL_PUB.delete_slh_rule,l_return_status: '||l_return_status);
FND_LOG.string(FND_LOG.level_statement, l_mod_name || '.sub_line_billing','After call to OKS_BILL_UTIL_PUB.delete_slh_rule, l_return_status='|| l_return_status);
* Bug 6114024 Call moved after update to operation lines
OKS_REPRICE_PVT.call_pricing_api(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_subject_chr_id => l_target_header_id,
p_subject_top_line_id => l_target_line_id,
p_subject_sub_line_tbl => l_subject_sub_line_tbl);
l_update_date := sysdate;
FND_LOG.string(FND_LOG.level_statement, l_mod_name||'.update_opn_lines','updating okc_operation_lines l_opl_id_tbl.COUNT='||l_opl_id_tbl.COUNT);
UPDATE okc_operation_lines SET
subject_cle_id = l_opl_sub_cle_id_tbl(i),
process_flag = l_opl_status_tbl(i),
object_version_number = object_version_number + 1,
last_updated_by = l_user_id,
last_update_date = l_update_date,
last_update_login = l_login_id,
request_id = l_request_id,
program_application_id = l_prog_appl_id,
program_id = l_prog_id,
program_update_date = l_update_date
WHERE id = l_opl_id_tbl(i);
FND_LOG.string(FND_LOG.level_statement, l_mod_name||'.update_sub_lines','updating date_renewed for source sublines l_src_sub_line_id_tbl.COUNT='||l_src_sub_line_id_tbl.COUNT);
UPDATE okc_k_lines_b SET
date_renewed = l_date_renewed,
object_version_number = object_version_number + 1,
last_updated_by = l_user_id,
last_update_date = l_update_date,
last_update_login = l_login_id,
request_id = l_request_id,
program_application_id = l_prog_appl_id,
program_id = l_prog_id,
program_update_date = l_update_date
WHERE id = l_src_sub_line_id_tbl(i);
FND_LOG.string(FND_LOG.level_statement, l_mod_name||'.update_top_lines','updating date_renewed for source toplines');
UPDATE okc_k_lines_b tl SET
tl.date_renewed = l_date_renewed,
tl.object_version_number = tl.object_version_number + 1,
tl.last_updated_by = l_user_id,
tl.last_update_date = l_update_date,
tl.last_update_login = l_login_id,
tl.request_id = l_request_id,
tl.program_application_id = l_prog_appl_id,
tl.program_id = l_prog_id,
tl.program_update_date = l_update_date
WHERE tl.id IN
(SELECT b.object_cle_id FROM okc_operation_lines a, okc_operation_lines b
WHERE a.oie_id = p_oie_id
AND b.oie_id = p_oie_id
AND a.id = b.parent_ole_id
AND a.parent_ole_id IS NULL
AND b.select_yn = 'Y')
AND NOT EXISTS
(SELECT 1 FROM okc_k_lines_b sl
WHERE sl.cle_id = tl.id
AND sl.date_terminated IS NULL
AND sl.date_cancelled IS NULL
AND sl.date_renewed IS NULL);
FND_LOG.string(FND_LOG.level_statement, l_mod_name||'.update_headers','updating date_renewed for source headers');
UPDATE okc_k_headers_all_b h SET
h.date_renewed = l_date_renewed,
h.object_version_number = h.object_version_number + 1,
h.last_updated_by = l_user_id,
h.last_update_date = l_update_date,
h.last_update_login = l_login_id,
h.request_id = l_request_id,
h.program_application_id = l_prog_appl_id,
h.program_id = l_prog_id,
h.program_update_date = l_update_date
WHERE h.id IN
(SELECT a.object_chr_id FROM okc_operation_lines a
WHERE a.oie_id = p_oie_id
AND a.object_cle_id IS NULL
AND a.subject_cle_id IS NULL
AND a.select_yn = 'Y')
AND NOT EXISTS
(SELECT 1 FROM okc_k_lines_b tl
WHERE tl.dnz_chr_id = h.id
AND tl.cle_id IS NULL
AND tl.lse_id IN (1,12,14,19)
AND tl.date_terminated IS NULL
AND tl.date_cancelled IS NULL
AND tl.date_renewed IS NULL);
l_opl_id_tbl.delete;
l_opl_sub_cle_id_tbl.delete;
l_opl_status_tbl.delete;
l_src_sub_line_id_tbl.delete;
l_subject_sub_line_tbl.delete;
log_messages('Processed selected lines');
FND_LOG.string(FND_LOG.level_statement, l_mod_name||'.update_invoice_text','Calling OKS_RENEW_CONTRACT_PVT.update_invoice_text p_chr_id='||l_target_header_id);
LOG_MESSAGES('Calling OKS_RENEW_CONTRACT_PVT.update_invoice_text');
OKS_RENEW_CONTRACT_PVT.update_invoice_text(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_chr_id => l_target_header_id);
LOG_MESSAGES('After Calling OKS_RENEW_CONTRACT_PVT.update_invoice_text, l_return_status: '||l_return_status);
FND_LOG.string(FND_LOG.level_statement, l_mod_name||'.update_invoice_text','After call to OKS_RENEW_CONTRACT_PVT.update_invoice_text l_return_status='||l_return_status);
log_messages('Updated invoice text');
FND_LOG.string(FND_LOG.level_statement, l_mod_name||'.update_contract_amount','calling update_contract_amount p_header_id='||l_target_header_id);
UPDATE_CONTRACT_AMOUNT(
p_header_id => l_target_header_id,
x_return_status => l_return_status);
FND_LOG.string(FND_LOG.level_statement, l_mod_name||'.update_contract_amount','after call to update_contract_amount l_return_status='||l_return_status);
log_messages('Updated contract header and line amounts');
PROCEDURE UPDATE_CONTRACT_AMOUNT(p_header_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2) IS
TYPE num_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_CONTRACT_AMOUNT';
SELECT c.cle_id, SUM(nvl(c.price_negotiated, 0)), SUM(nvl(s.tax_amount, 0))
FROM okc_k_lines_b c, oks_k_lines_b s
WHERE c.dnz_chr_id = cp_chr_id
--get only sublines for 1,12,19 (14:no renewal, 46:no sublines)
AND c.lse_id IN (7, 8, 9, 10, 11, 35, 13, 25)
AND s.cle_id = c.id
GROUP BY c.cle_id;
l_update_date DATE;
SAVEPOINT update_contract_amount_PVT;
l_update_date := sysdate;
UPDATE okc_k_lines_b
SET price_negotiated = l_price_tbl(i),
object_version_number = object_version_number + 1,
last_updated_by = l_user_id,
last_update_date = l_update_date,
last_update_login = l_login_id,
request_id = l_request_id,
program_application_id = l_prog_appl_id,
program_id = l_prog_id,
program_update_date = l_update_date
WHERE id = l_id_tbl(i);
UPDATE oks_k_lines_b
SET tax_amount = l_tax_tbl(i),
object_version_number = object_version_number + 1,
last_updated_by = l_user_id,
last_update_date = l_update_date,
last_update_login = l_login_id,
request_id = l_request_id
WHERE cle_id = l_id_tbl(i);
l_id_tbl.delete;
l_price_tbl.delete;
l_tax_tbl.delete;
FND_LOG.string(FND_LOG.level_statement, l_mod_name||'.top_lines','top lines price_negotiated and tax_amount updated');
UPDATE okc_k_headers_all_b h
SET h.estimated_amount =
(SELECT SUM(price_negotiated) FROM okc_k_lines_b tl
WHERE tl.dnz_chr_id = p_header_id AND tl.cle_id IS NULL
AND tl.lse_id IN (1, 12, 19, 46)),
h.object_version_number = h.object_version_number + 1,
h.last_updated_by = l_user_id,
h.last_update_date = l_update_date,
h.last_update_login = l_login_id,
h.request_id = l_request_id,
h.program_application_id = l_prog_appl_id,
h.program_id = l_prog_id,
h.program_update_date = l_update_date
WHERE h.id = p_header_id;
UPDATE oks_k_headers_b h
SET h.tax_amount =
(SELECT SUM(stl.tax_amount) FROM okc_k_lines_b ctl, oks_k_lines_b stl
WHERE ctl.dnz_chr_id = p_header_id AND ctl.cle_id IS NULL
AND ctl.lse_id IN (1, 12, 19, 46) AND stl.cle_id = ctl.id),
h.object_version_number = h.object_version_number + 1,
h.last_updated_by = l_user_id,
h.last_update_date = l_update_date,
h.last_update_login = l_login_id,
h.request_id = l_request_id
WHERE h.chr_id = p_header_id;
FND_LOG.string(FND_LOG.level_statement, l_mod_name||'.header','header estimated_amount and tax_amount updated');
ROLLBACK TO update_contract_amount_PVT;
END UPDATE_CONTRACT_AMOUNT;