[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
old contract. It will update the new contract header and lines start dates and end dates
based on renewal type for the line (Full Duration, Keep Duration, Do not renew). It uses
bulk operations to maximize performance. Since only start_date and end_date are updated in OKC_K_LINES_B and OKC_K_HEADERS_B, TAPI is ignored. It replaces the existing OKC procedure OKC_RENEW_PVT.update_renewal_dates.
Parameters
p_chr_id : id of the renewed contract
p_new_start_date : header start date for the renewed contract
p_new_end_date : header end date for the renewed contract
p_old_end_date : header start date for the source contract
*/
PROCEDURE UPDATE_RENEWAL_DATES
(
p_chr_id IN NUMBER,
p_new_start_date IN DATE,
p_new_end_date IN DATE,
p_old_start_date IN DATE,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2
)
IS
TYPE line_rec IS RECORD
(id NUMBER,
cle_id NUMBER,
lrt okc_k_lines_b.line_renewal_type_code%TYPE,
old_start_date DATE,
old_end_date DATE,
new_start_date DATE,
new_end_date DATE);
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_RENEWAL_DATES';
SELECT id from okc_k_lines_b
where dnz_chr_id = cp_chr_id and nvl(line_renewal_type_code,'X') = 'KEP';
select id, cle_id, line_renewal_type_code, start_date, end_date, null, null
from okc_k_lines_b
start with (dnz_chr_id = cp_chr_id and cle_id is null)
connect by prior id = cle_id;
l_cached_tbl_tmp.delete;
l_cached_tbl_tmp.delete;
PROCEDURE DELETE_DNR_LINES
IS
cursor c_dnr_lines(cp_chr_id in number) is
select id from okc_k_lines_b
where dnz_chr_id = cp_chr_id and nvl(line_renewal_type_code,'X') = 'DNR';
FND_LOG.string(FND_LOG.level_procedure, l_mod_name || '.delete_dnr_lines.begin', 'p_chr_id='||p_chr_id);
FND_LOG.string(FND_LOG.level_statement, l_mod_name || '.delete_dnr_lines.bulk_fetch', 'l_dnr_lines_tbl.count='||l_dnr_lines_tbl.count);
FND_LOG.string(FND_LOG.level_statement, l_mod_name || '.delete_dnr_lines.delete_line', 'calling delete_contract_line, p_line_id='||l_dnr_lines_tbl(i)||' x_return_status='||x_return_status);
delete_contract_line(
p_api_version => 1,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_line_id => l_dnr_lines_tbl(i),
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
FND_LOG.string(FND_LOG.level_statement, l_mod_name || '.delete_dnr_lines.delete_line', 'after call to delete_contract_line, x_return_status='||x_return_status);
l_dnr_lines_tbl.delete;
FND_LOG.string(FND_LOG.level_procedure, l_mod_name || '.delete_dnr_lines.end', 'x_return_status='||x_return_status);
FND_LOG.string(FND_LOG.level_error, l_mod_name || '.delete_dnr_lines.end_error', 'x_return_status=' || x_return_status);
FND_LOG.string(FND_LOG.level_unexpected, l_mod_name || '.delete_dnr_lines.end_unexpected_error', 'x_return_status=' || x_return_status);
FND_LOG.string(FND_LOG.level_unexpected, l_mod_name || '.delete_dnr_lines.end_other_error', l_error_text);
END DELETE_DNR_LINES;
update okc_k_headers_all_b SET
start_date = to_date(to_char(p_new_start_date, 'DD/MM/YYYY')|| to_char(start_date,'HH24:MI:SS'), 'DD/MM/YYYYHH24:MI:SS'),
end_date = to_date(to_char(p_new_end_date, 'DD/MM/YYYY')|| to_char(end_date,'HH24:MI:SS'), 'DD/MM/YYYYHH24:MI:SS')
WHERE id = p_chr_id;
UPDATE okc_k_lines_b SET
start_date = to_date(to_char(p_new_start_date, 'DD/MM/YYYY')|| to_char(start_date,'HH24:MI:SS'), 'DD/MM/YYYYHH24:MI:SS'),
end_date = to_date(to_char(p_new_end_date, 'DD/MM/YYYY')|| to_char(end_date,'HH24:MI:SS'), 'DD/MM/YYYYHH24:MI:SS')
WHERE dnz_chr_id = p_chr_id;
FND_LOG.string(FND_LOG.level_statement, l_mod_name || '.check_keep', 'end no keep duration lines, done with date updates ,x_return_status='||x_return_status);
FND_LOG.string(FND_LOG.level_statement, l_mod_name || '.forall_update_stmt ', 'l_id_tbl.count='||l_id_tbl.count||' ,x_return_status='||x_return_status);
update okc_k_lines_b set
start_date = to_date(to_char(l_start_date_tbl(i), 'DD/MM/YYYY')|| to_char(start_date,'HH24:MI:SS'), 'DD/MM/YYYYHH24:MI:SS'),
end_date = to_date(to_char(l_end_date_tbl(i), 'DD/MM/YYYY')|| to_char(end_date,'HH24:MI:SS'), 'DD/MM/YYYYHH24:MI:SS')
where id = l_id_tbl(i);
l_id_tbl.delete;
l_start_date_tbl.delete;
l_end_date_tbl.delete;
l_lines_tbl.delete;
l_id_tbl.delete;
l_start_date_tbl.delete;
l_end_date_tbl.delete;
END UPDATE_RENEWAL_DATES;
Internal procedure that updates the date_renewed column for the source contract header
and the source contract lines are actually renewed (i.e., ignores any DNR lines)
*/
PROCEDURE UPDATE_SOURCE_CONTRACT
(
p_new_chr_id IN NUMBER,
p_old_chr_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_SOURCE_CONTRACT';
SELECT okl.id
FROM okc_k_lines_b okl, okc_operation_lines ol
WHERE ol.object_chr_id = cp_old_chr_id
AND ol.subject_chr_id = cp_new_chr_id
AND ol.object_cle_id IS NOT NULL AND ol.subject_cle_id IS NOT NULL
AND ol.process_flag = 'P' AND ol.active_yn = 'Y'
AND ol.object_cle_id = okl.id AND okl.dnz_chr_id = cp_old_chr_id;
UPDATE okc_k_headers_all_b
SET date_renewed = l_date,
object_version_number = (object_version_number + 1)
WHERE id = p_old_chr_id;
UPDATE okc_k_lines_b
SET date_renewed = l_date,
object_version_number = (object_version_number + 1)
WHERE id = l_id_tbl(i);
l_id_tbl.delete;
l_id_tbl.delete;
END UPDATE_SOURCE_CONTRACT;
Internal procedure that updates the date_active and date_inactive of the conditions
associated with the renewed contract
*/
PROCEDURE UPDATE_CONDITION_HEADERS
(
p_chr_id IN NUMBER,
p_new_start_date IN DATE,
p_new_end_date IN DATE,
p_old_start_date IN DATE,
p_old_end_date IN DATE,
x_return_status OUT NOCOPY VARCHAR2
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_CONDITION_HEADERS';
select id, date_active, date_inactive
from okc_condition_headers_b
where dnz_chr_id = cp_chr_id;
update okc_condition_headers_b set
date_active = p_new_start_date,
date_inactive = l_date_inactive_tbl(i),
object_version_number = (object_version_number +1)
where id = l_id_tbl(i);
l_cond_tbl.delete;
l_id_tbl.delete;
l_date_inactive_tbl.delete;
END UPDATE_CONDITION_HEADERS;
Updates okc_k_lines_b.price_list_id for 'LST' pricing method
Calls OKS_REPRICE_PVT.Call_Pricing_API to reprice the contract
Parameters
p_chr_id : id of the contract that need to be repriced
p_price_method : Pricing method, 'MAN', 'LST' or 'PCT'
p_price_list_id : Price List Id for 'LST'/'PCT' pricing methods
p_markup_percent : Markup percent for 'PCT' procing method
*/
PROCEDURE REPRICE_CONTRACT
(
p_chr_id IN NUMBER,
p_price_method IN VARCHAR2,
p_price_list_id IN VARCHAR2,
p_markup_percent IN NUMBER,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'REPRICE_CONTRACT';
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;
UPDATE okc_k_lines_b
SET price_list_id = p_price_list_id
WHERE dnz_chr_id = p_chr_id
AND cle_id IS NULL;
UPDATE okc_k_lines_b
SET price_negotiated = l_price_tbl(i)
WHERE id = l_id_tbl(i);
UPDATE oks_k_lines_b
SET tax_amount = l_tax_tbl(i)
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 || '.update_header', 'updating okc-oks header etimated_amount and tax_amount');
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_chr_id AND tl.cle_id IS NULL
AND tl.lse_id IN (1,12,19,46))
WHERE h.id = p_chr_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_chr_id AND ctl.cle_id IS NULL
AND ctl.lse_id IN (1,12,19,46) AND stl.cle_id = ctl.id)
WHERE h.chr_id = p_chr_id;
SELECT hz.party_name, hrl.country, hrl.region_2
FROM hr_locations hrl, hr_all_organization_units hr, hz_parties hz
WHERE hrl.location_id = hr.location_id
AND hr.organization_id = cp_org_id
AND hz.party_id = cp_party_id;
SELECT hz.party_name, hzl.country, hzl.state
FROM hz_parties hz, hz_party_sites hzs , hz_locations hzl
WHERE hz.party_id = cp_party_id
AND hzs.party_id = hz.party_id
AND hzs.identifying_address_flag = 'Y'
AND hzl.location_id = hzs.location_id;
SELECT nvl(a.org_id, a.authoring_org_id), to_number(b.object1_id1)
FROM okc_k_headers_all_b a, okc_k_party_roles_b b
WHERE a.id = cp_chr_id
AND b.rle_code IN ('CUSTOMER', 'SUBSCRIBER')
AND b.dnz_chr_id = a.id
AND b.cle_id IS NULL;
SELECT salesrep_id
FROM jtf_rs_salesreps
WHERE resource_id = cp_res_id AND org_id = cp_org_id;
SELECT s.org_id, v.resource_name
FROM jtf_rs_salesreps s , jtf_rs_resource_extns_vl v
WHERE s.resource_id = v.resource_id
AND s.salesrep_id = cp_salesrep_id
AND org_id = cp_org_id; /*bugfix for 6672863*/
SELECT hr.name
FROM hr_all_organization_units hr
WHERE hr.organization_id = cp_org_id;
SELECT id, rle_code
FROM okc_k_party_roles_b
WHERE dnz_chr_id = cp_chr_id AND cle_id IS NULL
AND rle_code IN ('VENDOR', 'MERCHANT');
SELECT cro_code
FROM okc_contact_sources
WHERE buy_or_sell = 'S' AND rle_code = cp_rle_code
AND jtot_object_code = 'OKX_SALEPERS'
AND SYSDATE BETWEEN start_date AND NVL(end_date,SYSDATE + 1) ; -- bug 5938308
SELECT id
FROM okc_k_lines_b
WHERE dnz_chr_id = cp_chr_id AND cle_id IS NULL AND lse_id IN (1,12,19,46);
DELETE FROM oks_k_sales_credits
WHERE chr_id = p_chr_id;
FND_LOG.string(FND_LOG.level_procedure, l_mod_name || '.end_2', 'Profile OKS_ENABLE_SALES_CREDIT=NO(Drop), deleted sales credits, no fruther processsing');
DELETE FROM oks_k_sales_credits
WHERE chr_id = p_chr_id;
DELETE FROM oks_k_sales_credits
WHERE chr_id = p_chr_id AND sales_credit_type_id1 = l_prof_rev_type;
DELETE FROM okc_contacts
WHERE dnz_chr_id = p_chr_id
AND cpl_id = l_cpl_id
AND cro_code IN (SELECT cro_code FROM okc_contact_sources
WHERE buy_or_sell = 'S' AND rle_code = l_rle_code
AND jtot_object_code = 'OKX_SALEPERS');
FND_LOG.string(FND_LOG.level_statement, l_mod_name || '.create_contact', 'deleted old contacts, creating new contact for salesrep id='||l_salesrep_id);
INSERT INTO OKC_CONTACTS(
id,
cpl_id,
cro_code,
dnz_chr_id,
object1_id1,
object1_id2,
jtot_object1_code,
object_version_number,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
sales_group_id)
VALUES(
okc_p_util.raw_to_number(sys_guid()),
l_cpl_id,
l_cro_code,
p_chr_id,
l_salesrep_id,
'#',
'OKX_SALEPERS',
1,
l_created_by,
l_date,
l_created_by,
l_date,
l_login_id,
l_sales_group_id);
INSERT INTO oks_k_sales_credits(
id,
percent,
chr_id,
cle_id,
ctc_id,
sales_credit_type_id1,
sales_credit_type_id2,
object_version_number,
created_by,
creation_date,
last_updated_by,
last_update_date,
security_group_id,
sales_group_id)
VALUES (
okc_p_util.raw_to_number(sys_guid()),
l_percent,
p_chr_id,
null,
l_salesrep_id,
l_prof_rev_type,
'#',
1,
l_created_by,
l_date,
l_created_by,
l_date,
null,
l_sales_group_id);
INSERT INTO oks_k_sales_credits(
id,
percent,
chr_id,
cle_id,
ctc_id,
sales_credit_type_id1,
sales_credit_type_id2,
object_version_number,
created_by,
creation_date,
last_updated_by,
last_update_date,
security_group_id,
sales_group_id)
VALUES (
okc_p_util.raw_to_number(sys_guid()),
l_percent,
p_chr_id,
l_id_tbl(i),
l_salesrep_id,
l_prof_rev_type,
'#',
1,
l_created_by,
l_date,
l_created_by,
l_date,
null,
l_sales_group_id);
l_id_tbl.delete;
SELECT id, nvl(orig_system_id1, cle_id_renewed) old_id, lse_id
FROM okc_k_lines_b
WHERE dnz_chr_id = cp_chr_id AND cle_id IS NULL
AND lse_id IN (1,19,46);
l_id_tbl.delete;
l_old_id_tbl.delete;
l_lse_id_tbl.delete;
b. Update new oks lines with the price lock information (locked price list id
and locked price list line id)
*/
PROCEDURE COPY_USAGE_PRICE_LOCKS
(
p_chr_id IN NUMBER,
p_org_id IN NUMBER,
p_contract_number IN VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'COPY_USAGE_PRICE_LOCKS';
SELECT olds.locked_price_list_line_id, newc.id, nvl(olds.break_uom, 'X')
FROM okc_k_lines_b oldc, oks_k_lines_b olds, okc_k_lines_b newc
WHERE newc.dnz_chr_id = cp_chr_id AND newc.lse_id IN (12, 13)
AND oldc.id = newc.orig_system_id1 AND olds.cle_id = oldc.id
AND olds.locked_price_list_id IS NOT NULL
AND nvl(oldc.price_list_id, -99) = nvl(newc.price_list_id, -98);
UPDATE oks_k_lines_b
SET locked_price_list_id = l_new_lpl_tbl(i),
locked_price_list_line_id = l_new_lpll_tbl(i),
break_uom = l_old_break_uom_tbl(i)
WHERE cle_id = l_new_cid_tbl(i);
l_old_lpll_tbl.delete;
l_new_cid_tbl.delete;
l_new_lpl_tbl.delete;
l_new_lpll_tbl.delete;
l_old_break_uom_tbl.delete;
SELECT id, chr_id, cle_id, dnz_chr_id, sequence_no, uom_code, start_date, level_periods,
uom_per_period, advance_periods, level_amount, invoice_offset_days, interface_offset_days,
comments, due_arr_yn, amount, lines_detailed_yn
FROM oks_stream_levels_b
WHERE chr_id = cp_chr_id;
l_hdr_strlvl_tbl.delete;
l_sllv_tbl.delete;
x_sllv_tbl.delete;
SELECT renc.id, renc.inv_rule_id, renc.orig_system_id1, rens.billing_schedule_type,
nvl(renc.price_negotiated,0) new_line_amt,
(nvl(oldc.price_negotiated, 0) + nvl(olds.ubt_amount, 0) +
nvl(olds.credit_amount, 0) + nvl(olds.suppressed_credit, 0) ) old_line_amt
FROM okc_k_lines_b renc, oks_k_lines_b rens,
okc_k_lines_b oldc, oks_k_lines_b olds
WHERE renc.dnz_chr_id = cp_chr_id
AND renc.cle_id IS NULL AND renc.lse_id IN (1,12,19,46) AND rens.cle_id = renc.id
AND oldc.id = renc.orig_system_id1
AND olds.cle_id = renc.orig_system_id1;
SELECT id, chr_id, cle_id, dnz_chr_id, sequence_no, uom_code, start_date, end_date,
level_periods, uom_per_period, advance_periods, level_amount, invoice_offset_days,
interface_offset_days, comments, due_arr_yn, amount, lines_detailed_yn
FROM oks_stream_levels_b
WHERE cle_id = cp_cle_id;
l_line_sllv_tbl.delete;
l_line_strlvl_tbl.delete;
l_line_strlvl_tbl.delete;
l_bil_sch_out_tbl.delete;
l_line_sllv_tbl.delete;
l_top_line_tbl.delete;
SELECT a.id, a.start_date, a.end_date, nvl(b.billing_schedule_type, 'XX'),
a.lse_id, nvl(b.usage_type, 'XX')
FROM okc_k_lines_b a, oks_k_lines_b b
WHERE a.dnz_chr_id = cp_chr_id AND a.id = b.cle_id
AND a.cle_id IS NULL;
SELECT RULE_ID
FROM RA_RULES
WHERE TYPE IN ('A', 'ACC_DUR')
AND RULE_ID = l_id;
SELECT RULE_ID
FROM RA_RULES
WHERE TYPE = 'I'
AND RULE_ID = l_id;
l_sll_tbl_out.delete;
l_sll_tbl.delete;
l_bil_sch_out_tbl.delete;
l_id_tbl.delete;
l_start_dt_tbl.delete;
l_end_dt_tbl.delete;
l_bsch_typ_tbl.delete;
UPDATE okc_k_lines_b
SET inv_rule_id = l_invoice_rule_id
WHERE dnz_chr_id = p_chr_id AND cle_id IS NULL;
UPDATE okc_k_lines_b a
SET a.inv_rule_id = -3
WHERE a.dnz_chr_id = p_chr_id AND a.cle_id IS NULL AND a.lse_id = 12
AND EXISTS (SELECT 1 FROM oks_k_lines_b b
WHERE b.cle_id = a.id AND b.usage_type IN ('VRT', 'QTY'));
UPDATE oks_k_lines_b
SET acct_rule_id = l_account_rule_id
WHERE cle_id IN (SELECT id FROM okc_k_lines_b
WHERE dnz_chr_id = p_chr_id AND cle_id IS NULL);
a. Delete all existing billing schedules
b. Recreate billing shcedule using the billing profile parameters
2. If renewed contract duration = old contract duration and no billing profile id specified
a. Recreate billing schedule using existing rules
3. If renewed contract duration <> old contract duration and no billing profile specified
a. Delete all existing billing schedules
b. Such a contract will later fail QA check, as it will have no billing schedule
*/
PROCEDURE RECREATE_BILLING
(
p_chr_id IN NUMBER,
p_billing_profile_id IN NUMBER,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'RECREATE_BILLING';
SELECT ren.start_date, ren.end_date, old.start_date, old.end_date, old.id,
rens.period_type, rens.period_start, olds.period_type, olds.period_start
FROM okc_k_headers_all_b ren, okc_k_headers_all_b old,
oks_k_headers_b rens, oks_k_headers_b olds
WHERE ren.id = cp_chr_id
AND rens.chr_id = ren.id
AND old.id = ren.orig_system_id1
AND olds.chr_id = old.id;
FND_LOG.string(FND_LOG.level_statement, l_mod_name || '.delete_billing', 'p_billing_profile_id='||p_billing_profile_id);
DELETE FROM oks_level_elements
WHERE dnz_chr_id = p_chr_id;
DELETE FROM oks_stream_levels_b
WHERE dnz_chr_id = p_chr_id;
UPDATE oks_k_lines_b
SET billing_schedule_type = NULL
WHERE cle_id IN
(SELECT id FROM OKC_K_LINES_B WHERE dnz_chr_id = p_chr_id
AND lse_id IN (7,8,9,10,11,35,13,18,25));
FND_LOG.string(FND_LOG.level_statement, l_mod_name || '.delete_billing', 'done');
SELECT cgp_parent_id id
FROM okc_k_grpings
WHERE included_chr_id = cp_chr_id
AND cgp_parent_id = cp_grp_id;
l_cgcv_rec_in.last_updated_by := FND_API.G_MISS_NUM;
l_cgcv_rec_in.last_update_date := FND_API.G_MISS_DATE;
l_cgcv_rec_in.last_update_login := FND_API.G_MISS_NUM;
SELECT id, pdf_id
FROM okc_k_processes
WHERE chr_id = cp_chr_id
AND pdf_id IN (SELECT id FROM okc_process_defs_b WHERE pdf_type = 'WPS' AND usage = 'APPROVE');
l_cpsv_rec_in.last_updated_by := FND_API.G_MISS_NUM;
l_cpsv_rec_in.last_update_date := FND_API.G_MISS_DATE;
l_cpsv_rec_in.last_update_login := FND_API.G_MISS_NUM;
FND_LOG.string(FND_LOG.level_statement, l_mod_name || '.update_k_process', 'calling OKC_CONTRACT_PVT.update_contract_process');
OKC_CONTRACT_PVT.update_contract_process(
p_api_version => 1,
p_init_msg_list => FND_API.G_FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_cpsv_rec => l_cpsv_rec_in,
x_cpsv_rec => l_cpsv_rec_out);
FND_LOG.string(FND_LOG.level_statement, l_mod_name || '.update_k_process', 'after call to OKC_CONTRACT_PVT.update_contract_process, x_return_status='||x_return_status);
PROCEDURE UPDATE_RENEWED_CONTRACT
(
p_chr_id IN NUMBER,
p_rnrl_rec IN OKS_RENEW_UTIL_PVT.rnrl_rec_type,
p_notify_to IN NUMBER,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_RENEWED_CONTRACT';
SELECT a.contract_number, a.contract_number_modifier,
nvl(a.org_id, a.authoring_org_id), a.qcl_id, a.estimated_amount, a.currency_code,
a.payment_term_id, a.conversion_type, a.conversion_rate, a.conversion_rate_date,
a.conversion_euro_rate, b.renewal_po_number, b.trxn_extension_id,
b.grace_period, b.grace_duration, c.date_renewed, -- added for bug 6086893
a.start_date, a.end_date, c.scs_code, c.sts_code, c.estimated_amount,
c.start_date, c.end_date, scs.cls_code, c.id
FROM okc_k_headers_all_b a, oks_k_headers_b b, okc_k_headers_all_b c, OKC_SUBCLASSES_B SCS
WHERE a.id = b.chr_id
AND a.id = cp_chr_id
AND c.id = a.orig_system_id1
AND c.scs_code = scs.code;
UPDATE oks_k_headers_b
SET renewal_status = l_renewal_status,
est_rev_percent = l_est_rev_percent,
est_rev_date = l_est_rev_date,
grace_duration = l_grace_duration,
grace_period = l_grace_period,
renewal_type_used = l_renewal_type,
renewal_grace_duration_used = l_grace_duration,
renewal_grace_period_used = l_grace_period,
renewal_notification_to = p_notify_to,
renewal_po_used = l_renewal_po_used,
renewal_pricing_type_used = l_renewal_pricing_type_used,
renewal_markup_percent_used = l_renewal_markup_percent_used,
renewal_price_list_used = l_renewal_price_list_used,
rev_est_percent_used = p_rnrl_rec.revenue_estimated_percent,
rev_est_duration_used = p_rnrl_rec.revenue_estimated_duration,
rev_est_period_used = p_rnrl_rec.revenue_estimated_period,
billing_profile_used = p_rnrl_rec.billing_profile_id,
ern_flag_used_yn = NULL, --obsolete column
evn_threshold_amt = l_evn_threshold_amt,
evn_threshold_cur = l_evn_threshold_cur,
ern_threshold_amt = l_ern_threshold_amt,
ern_threshold_cur = l_ern_threshold_cur,
electronic_renewal_flag = NULL, --obsolete column
approval_type_used = l_approval_type,
wf_item_key = l_wf_item_key
WHERE chr_id = p_chr_id;
UPDATE okc_k_headers_all_b
SET qcl_id = l_qcl_id,
payment_term_id = l_payment_term_id,
cust_po_number = l_cust_po_number,
cust_po_number_req_yn = l_cust_po_number_req_yn,
payment_instruction_type = l_payment_instruction_type,
conversion_type = l_conv_type,
conversion_rate = l_conv_rate,
conversion_rate_date = l_conv_rate_date,
conversion_euro_rate = l_conv_euro_rate
WHERE id = p_chr_id;
UPDATE okc_k_lines_b
SET payment_instruction_type = NULL
WHERE dnz_chr_id = p_chr_id AND cle_id IS NULL AND lse_id IN (1,12,19,46);
UPDATE oks_k_lines_b b
SET b.cust_po_number = NULL,
b.cust_po_number_req_yn = NULL
WHERE b.dnz_chr_id = p_chr_id
AND b.cle_id IN (SELECT a.id FROM okc_k_lines_b a
WHERE a.dnz_chr_id = p_chr_id AND a.cle_id IS NULL AND a.lse_id IN (1,12,19,46));
END UPDATE_RENEWED_CONTRACT;
6. If the user does not update access to the contract
7. If all sublines (or subscription top lines) in status ACTIVE, EXPIRED or SIGNED
have already been renew consolidated.
8. The effective renewal type for contract is 'Do not renew'. If the renewal type
is not defined for the contract, it is derived from Party -> Org -> Global setup
by calling the get_renew_rules procedure.
9. If the contract contains any warranty lines (lse id = 14) it cannot be renewed
(Currently there is no check for warranty lines, a contract having warranty lines
can be renewed, but all warranty lines are dropped during copy.)
Warning Conditions
1. Contract has been renewed and the renewal has been cancelled.
For background (events) renewal this is an error condition.
2. All contract sublines and subscription toplines have been terminated or cancelled.
For background (events) renewal this is an error condition.
3. All contract sublines and subscription toplines have an effective line renewal type
code of DNR. (Effective line renewal type code = nvl(line renewal type code ,
parent line renewal type code). For background (events) renewal this is an error
condition.
This procedure does not stop if any error/warning condition is found, all validations
are always done
*/
PROCEDURE VALIDATE_RENEWAL
(
p_api_version IN NUMBER DEFAULT 1,
p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_chr_id IN NUMBER,
p_date IN DATE,
p_validation_level IN VARCHAR2 DEFAULT G_VALIDATE_ALL,
x_rnrl_rec OUT NOCOPY OKS_RENEW_UTIL_PVT.rnrl_rec_type,
x_validation_status OUT NOCOPY VARCHAR2,
x_validation_tbl OUT NOCOPY validation_tbl_type
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'VALIDATE_RENEWAL';
SELECT a.application_id, a.contract_number, a.contract_number_modifier,
b.ste_code, b.meaning, a.scs_code, a.template_yn,
a.date_terminated, a.date_renewed, a.end_date
FROM okc_k_headers_all_b a, okc_statuses_v b
WHERE a.id = cp_chr_id AND a.sts_code = b.code;
SELECT kl.id
FROM okc_k_lines_b kl
WHERE kl.dnz_chr_id = cp_chr_id AND kl.lse_id IN (7, 8, 9, 10, 11, 13, 25, 35, 46)
AND kl.id NOT IN(
SELECT ol.object_cle_id
FROM okc_operation_lines ol, okc_operation_instances oi, okc_class_operations oo
WHERE oo.cls_code = 'SERVICE' AND oo.opn_code = 'REN_CON' AND oo.id = oi.cop_id
AND ol.oie_id = oi.id AND ol.object_chr_id = cp_chr_id
AND ol.subject_chr_id IS NOT NULL
AND ol.process_flag = 'P' AND ol.active_yn = 'Y');
SELECT k.contract_number, k.contract_number_modifier, st.ste_code
FROM okc_operation_lines ol, okc_operation_instances oi, okc_class_operations oo,
okc_statuses_b st, okc_k_headers_all_b k
WHERE oo.cls_code = 'SERVICE' AND oo.opn_code = 'RENEWAL'
AND oo.id = oi.cop_id
AND ol.oie_id = oi.id AND ol.object_chr_id = cp_chr_id
AND ol.subject_chr_id IS NOT NULL AND ol.object_cle_id IS NULL
AND ol.subject_cle_id IS NULL
AND ol.process_flag = 'P' AND ol.subject_chr_id = k.id
AND k.sts_code = st.code
ORDER BY ol.active_yn DESC;
SELECT id
FROM okc_k_lines_b
WHERE dnz_chr_id = cp_chr_id AND lse_id IN (7, 8, 9, 10, 11, 13, 25, 35, 46)
AND date_terminated IS NULL AND date_cancelled IS NULL;
SELECT a.id
FROM okc_k_lines_b a, okc_k_lines_b b
WHERE a.dnz_chr_id = cp_chr_id
AND b.dnz_chr_id (+) = cp_chr_id
AND a.cle_id = b.id (+)
AND a.lse_id IN (7,8,9,10,11,13,25,35,46)
AND decode(b.line_renewal_type_code, 'DNR', 'DNR',
NULL, nvl(a.line_renewal_type_code, 'FUL'),
nvl(a.line_renewal_type_code, b.line_renewal_type_code)) <> 'DNR';
SELECT id
FROM okc_k_lines_b
WHERE dnz_chr_id = cp_chr_id AND lse_id = 14;
SELECT id
FROM okc_k_lines_b kl, okc_statuses_b st
WHERE kl.dnz_chr_id = cp_chr_id AND kl.lse_id IN (7, 8, 9, 10, 11, 13, 25, 35, 46)
AND kl.sts_code = st.ste_code
AND st.ste_code IN ('ACTIVE', 'EXPIRED', 'SIGNED', 'CANCELLED', 'TERMINATED');
FND_MESSAGE.set_name(G_OKS_APP_NAME, 'OKS_NO_UPDATE');
x_validation_tbl(l_msg_count).code := 'OKS_NO_UPDATE';
p_chr_id : id of the contract whose lines need to be updated
The format of the invoice text is as follows
topline = SUBSTR(l_item_desc || ':' || p_start_date || ':' || p_end_date, 1, 450);
PROCEDURE UPDATE_INVOICE_TEXT
(
p_api_version IN NUMBER DEFAULT 1,
p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_chr_id IN NUMBER
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_INVOICE_TEXT';
SELECT
--kl.lse_id, bk.inventory_item_id id1, bk.organization_id id2 , bk.organization_id inv_org_id,
sl.id, bt.description name, bk.concatenated_segments description, null,
null, null, to_char(kl.start_date,'DD-MON-YYYY'), to_char(kl.end_date,'DD-MON-YYYY')
FROM mtl_system_items_b_kfv bk, mtl_system_items_tl bt,
okc_k_items it, oks_k_lines_b sl, okc_k_lines_b kl
WHERE bk.inventory_item_id = bt.inventory_item_id
AND bk.organization_id = bt.organization_id
AND bt.language = USERENV('LANG')
AND bk.inventory_item_id = it.object1_id1
AND bk.organization_id = it.object1_id2
AND it.cle_id = kl.id
AND sl.cle_id = kl.id
AND kl.lse_id IN (1, 12, 14, 19, 46)
AND kl.cle_id IS NULL
AND kl.dnz_chr_id = cp_chr_id;
SELECT
--kl.lse_id, iv.id1, iv.id2, iv.inv_org_id,
sl.id, iv.name, iv.description, it.number_of_items,
kl.cle_id, kl.lse_id, to_char(kl.START_DATE,'DD-MON-YYYY'), to_char(kl.end_date,'DD-MON-YYYY')
FROM
(
SELECT 'OKX_COVSYST' TYPE, T.SYSTEM_ID ID1, '#' ID2, T.NAME NAME,
T.DESCRIPTION DESCRIPTION, NULL INV_ORG_ID
FROM CSI_SYSTEMS_TL T
WHERE T.LANGUAGE = USERENV('LANG')
UNION ALL
SELECT 'OKX_PARTYSITE' TYPE, PSE.PARTY_SITE_ID ID1, '#' ID2, PSE.PARTY_SITE_NAME NAME,
SUBSTR(arp_addr_label_pkg.format_address(NULL,LCN.ADDRESS1,LCN.ADDRESS2,LCN.ADDRESS3,
LCN.ADDRESS4,LCN.CITY,LCN.COUNTY,LCN.STATE,LCN.PROVINCE,LCN.POSTAL_CODE,NULL,LCN.COUNTRY,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,'N','N',80,1,1
),1,80) DESCRIPTION, NULL INV_ORG_ID
FROM HZ_PARTY_SITES PSE,HZ_LOCATIONS LCN
WHERE LCN.LOCATION_ID = PSE.LOCATION_ID
AND LCN.CONTENT_SOURCE_TYPE = 'USER_ENTERED'
UNION ALL
SELECT 'OKX_PARTY' TYPE, P.PARTY_ID ID1, '#' ID2, P.PARTY_NAME NAME,
P.PARTY_NUMBER DESCRIPTION, NULL INV_ORG_ID
FROM HZ_PARTIES P
WHERE P.PARTY_TYPE IN ( 'PERSON','ORGANIZATION')
UNION ALL
SELECT 'OKX_CUSTPROD' TYPE, CII.INSTANCE_ID ID1, '#' ID2, SIT.DESCRIPTION NAME,
BK.CONCATENATED_SEGMENTS DESCRIPTION, BK.ORGANIZATION_ID INV_ORG_ID
FROM CSI_ITEM_INSTANCES CII, CSI_I_PARTIES CIP,
MTL_SYSTEM_ITEMS_B_KFV BK, MTL_SYSTEM_ITEMS_TL SIT
WHERE CII.INSTANCE_ID = CIP.INSTANCE_ID AND CIP.RELATIONSHIP_TYPE_CODE = 'OWNER'
AND CIP.PARTY_SOURCE_TABLE = 'HZ_PARTIES' AND
NOT EXISTS ( SELECT 1 FROM CSI_INSTALL_PARAMETERS CIPM
WHERE CIPM.INTERNAL_PARTY_ID = CIP.PARTY_ID )
AND BK.INVENTORY_ITEM_ID = CII.INVENTORY_ITEM_ID
AND SIT.INVENTORY_ITEM_ID = BK.INVENTORY_ITEM_ID
AND SIT.ORGANIZATION_ID = BK.ORGANIZATION_ID
AND SIT.LANGUAGE = USERENV('LANG')
UNION ALL
SELECT 'OKX_CUSTACCT' TYPE, CA.CUST_ACCOUNT_ID ID1, '#' ID2,
decode(CA.ACCOUNT_NAME, null, P.PARTY_NAME, CA.Account_NAME) NAME,
CA.ACCOUNT_NUMBER DESCRIPTION, NULL INV_ORG_ID
FROM HZ_CUST_ACCOUNTS CA,HZ_PARTIES P
WHERE CA.PARTY_ID = P.PARTY_ID
UNION ALL
SELECT 'OKX_COUNTER' TYPE, CCT.COUNTER_ID ID1, '#' ID2, CCT.NAME NAME,
CCT.DESCRIPTION DESCRIPTION, NULL INV_ORG_ID
FROM CSI_COUNTERS_TL CCT WHERE CCT.LANGUAGE = USERENV('LANG')
UNION ALL
SELECT 'OKX_COVITEM' TYPE, B.INVENTORY_ITEM_ID ID1, to_char(B.ORGANIZATION_ID) ID2,
T.DESCRIPTION NAME, B.CONCATENATED_SEGMENTS DESCRIPTION, B.ORGANIZATION_ID INV_ORG_ID
FROM MTL_SYSTEM_ITEMS_B_KFV B,MTL_SYSTEM_ITEMS_TL T
WHERE B.INVENTORY_ITEM_ID = T.INVENTORY_ITEM_ID AND B.ORGANIZATION_ID = T.ORGANIZATION_ID
AND T.LANGUAGE = USERENV('LANG')
) iv, okc_k_items it, oks_k_lines_b sl, okc_k_lines_b kl, okc_k_headers_all_b kh
WHERE iv.type = it.jtot_object1_code -- bug 5218936
AND iv.id1 = it.object1_id1
AND iv.id2 = it.object1_id2
AND decode(iv.inv_org_id, null, kh.inv_organization_id, iv.inv_org_id) = kh.inv_organization_id
AND it.cle_id = kl.id
AND sl.cle_id = kl.id
AND kl.lse_id IN (7,8,9,10,11,35, 13, 18, 25)
AND kl.dnz_chr_id = kh.id
AND kh.id = cp_chr_id;
SELECT it.concatenated_segments AS Name ,
it.Description AS Description
FROM csi_counters_b ccb ,
csi_counters_tl cct ,
cs_csi_counter_groups cg ,
csi_counter_associations cca ,
csi_item_instances cp ,
mtl_system_items_kfv it,
okc_k_items items,
oks_k_lines_b kl,
okc_k_headers_all_b khr
WHERE ccb.counter_id = cct.counter_id
AND cct.language = USERENV('LANG')
AND ccb.group_id = cg.counter_group_id
AND ccb.counter_id = cca.counter_id
AND cca.source_object_code = 'CP'
AND cca.source_object_id = cp.instance_id
AND cp.inventory_item_id = it.inventory_item_id
AND ccb.counter_id = items.object1_id1
AND items.dnz_chr_id = khr.id
AND it.organization_id = khr.inv_organization_id
AND kl.cle_id = items.cle_id
AND khr.id = cp_chr_id
AND kl.id = cp_sl_id;
SAVEPOINT update_invoice_text_PVT;
UPDATE oks_k_lines_tl
SET invoice_text = l_inv_txt_tbl(j)
WHERE id = l_sl_id_tbl(j) AND language = USERENV('LANG');
l_line_tbl.delete;
l_inv_txt_tbl.delete;
l_sl_id_tbl.delete;
l_line_tbl.delete;
l_inv_txt_tbl.delete;
l_sl_id_tbl.delete;
UPDATE oks_k_lines_tl c
SET c.invoice_text =
(SELECT SUBSTR(a.invoice_text,1, decode(INSTR(a.invoice_text, ':'),0,
LENGTH(a.invoice_text), INSTR(a.invoice_text, ':'))) ||l_inv_txt_tbl(j)
FROM oks_k_lines_tl a, oks_k_lines_b b
WHERE a.id = b.id AND a.language = USERENV('LANG')
AND b.cle_id = l_cle_id_tbl(j))
WHERE id = l_sl_id_tbl(j) AND language = USERENV('LANG');
l_line_tbl.delete;
l_inv_txt_tbl.delete;
l_sl_id_tbl.delete;
l_cle_id_tbl.delete;
l_line_tbl.delete;
l_inv_txt_tbl.delete;
l_sl_id_tbl.delete;
l_cle_id_tbl.delete;
ROLLBACK TO update_invoice_text_PVT;
ROLLBACK TO update_invoice_text_PVT;
ROLLBACK TO update_invoice_text_PVT;
END UPDATE_INVOICE_TEXT;
SELECT nvl(a.org_id, a.authoring_org_id), b.id
FROM okc_k_headers_all_b a LEFT OUTER JOIN okc_k_party_roles_b b
ON a.id = b.dnz_chr_id
AND b.cle_id IS NULL
AND b.rle_code IN ('VENDOR', 'MERCHANT')
WHERE a.id = cp_chr_id;
SELECT
--rsc.resource_id, srp.salesrep_id, srp.org_id, ctc.cro_code,
fnd.user_id, fnd.user_name
FROM okc_contacts ctc, fnd_user fnd,
jtf_rs_resource_extns rsc, jtf_rs_salesreps srp
WHERE ctc.dnz_chr_id = cp_chr_id
AND ctc.cpl_id = cp_cpl_id
AND ctc.cro_code IN (SELECT src.cro_code FROM okc_contact_sources src
WHERE src.rle_code IN ('VENDOR', 'MERCHANT')
AND src.jtot_object_code = 'OKX_SALEPERS'
AND src.buy_or_sell = 'S')
AND srp.salesrep_id = to_number(ctc.object1_id1)
AND nvl(srp.org_id, -99) = cp_org_id
AND srp.resource_id = rsc.resource_id
AND rsc.user_id = fnd.user_id;
SELECT user_name
FROM fnd_user
WHERE user_id = cp_user_id;
SELECT contract_number, contract_number_modifier, start_date, end_date,
renewal_type_code, renewal_end_date, currency_code
FROM okc_k_headers_all_b
WHERE id = cp_chr_id AND application_id = 515;
SELECT currency_code, org_id
FROM okc_k_headers_all_b WHERE id = cp_chr_id;
FND_LOG.string(FND_LOG.level_statement, l_mod_name || '.update_dates', 'calling update_renewal_dates, p_chr_id='||x_chr_id||' ,p_new_start_date='||l_renk_start_date||
' ,p_new_end_date='||l_renk_end_date||' ,p_old_start_date='||l_k_start_date);
update_renewal_dates(
p_chr_id => x_chr_id,
p_new_start_date => l_renk_start_date,
p_new_end_date => l_renk_end_date,
p_old_start_date => l_k_start_date,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_return_status => x_return_status);
FND_LOG.string(FND_LOG.level_statement, l_mod_name || '.update_dates', 'after update_renewal_dates, x_return_status='||x_return_status);
Step 6.1 : Update annualized_factor for the renewed contract lines
*/
fnd_file.put_line(FND_FILE.LOG,' ');
fnd_file.put_line(FND_FILE.LOG,'Step 6.1 : Update annualized_factor for the renewed contract lines');
FND_LOG.string(FND_LOG.level_statement, l_mod_name , ' calling update to annualized_factor , p_new_chr_id='||x_chr_id);
UPDATE okc_k_lines_b
SET annualized_factor = OKS_SETUP_UTIL_PUB.Get_Annualized_Factor(start_date, end_date, lse_id)
WHERE dnz_chr_id = x_chr_id;
FND_LOG.string(FND_LOG.level_statement, l_mod_name , ' After calling update to annualized_factor , p_new_chr_id='||x_chr_id);
Step 7: update the old contract's date renewed column for the lines that are actually renewed
*/
fnd_file.put_line(FND_FILE.LOG,' ');
fnd_file.put_line(FND_FILE.LOG,'Step 7 : update the old contract date renewed column for the lines that are actually renewed');
FND_LOG.string(FND_LOG.level_statement, l_mod_name || '.update_source_contract', 'calling update_source_contract, p_new_chr_id='||x_chr_id||' ,p_old_chr_id='||p_chr_id);
update_source_contract(
p_new_chr_id => x_chr_id,
p_old_chr_id => p_chr_id,
x_return_status => x_return_status);
FND_LOG.string(FND_LOG.level_statement, l_mod_name || '.update_source_contract', 'after update_source_contract, x_return_status='||x_return_status);
FND_LOG.string(FND_LOG.level_statement, l_mod_name || '.invoice_text', 'calling update_invoice_text, p_chr_id='||x_chr_id);
update_invoice_text(
p_api_version => 1,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_chr_id => x_chr_id);
FND_LOG.string(FND_LOG.level_statement, l_mod_name || '.invoice_text', 'after update_invoice_text, x_return_status='||x_return_status);
bug 4775295 : Commented call to procedure update_condition_headers
--Step 9 update contract condition(event) headers
IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
FND_LOG.string(FND_LOG.level_statement, l_mod_name || '.condition_header', 'calling update_condition_headers, p_chr_id='||x_chr_id||' ,p_new_start_date='||l_renk_start_date||' ,p_new_end_date='||l_renk_end_date
||' ,p_old_start_date='||l_k_start_date||' ,p_old_end_date='||l_k_end_date);
update_condition_headers(
p_chr_id => x_chr_id,
p_new_start_date => l_renk_start_date,
p_new_end_date => l_renk_end_date,
p_old_start_date => l_k_start_date,
p_old_end_date => l_k_end_date,
x_return_status => x_return_status);
FND_LOG.string(FND_LOG.level_statement, l_mod_name || '.condition_header', 'after update_condition_headers, x_return_status='||x_return_status);
Step 17: check and update/create contract approval process specified in GCD
*/
fnd_file.put_line(FND_FILE.LOG,' ');
fnd_file.put_line(FND_FILE.LOG,'Step 17 : check and update/create contract approval process specified in GCD');
Step 18: update contract (OKC and OKS) with the renewal rules
*/
fnd_file.put_line(FND_FILE.LOG,' ');
fnd_file.put_line(FND_FILE.LOG,'Step 18 : update contract (OKC and OKS) with the renewal rules');
FND_LOG.string(FND_LOG.level_statement, l_mod_name || '.update_contract', 'calling update_renewed_contract p_chr_id='||p_chr_id||' ,p_notify_to='||l_user_id);
update_renewed_contract(
p_chr_id => x_chr_id,
p_rnrl_rec => l_rnrl_rec,
p_notify_to => l_user_id,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_return_status => x_return_status);
FND_LOG.string(FND_LOG.level_statement, l_mod_name || '.update_contract', 'after call to update_renewed_contract, x_return_status='||x_return_status);