[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT id,object_version_number,uom_code,duration,tve_id_started,tve_id_ended,
tve_id_limited,tve_type
FROM okc_timevalues
WHERE DNZ_CHR_ID = p_chr_id;
SELECT nvl(rgp.cle_id,rgp.chr_id) comp_id ,nvl(rul.rule_information1,g_def_cle_ren) rule_type
FROM okc_rules_b rul,okc_rule_groups_b rgp
WHERE rgp.dnz_chr_id = p_chr_id
and rgp.id=rul.rgp_id
-- and rgp.rgd_code='RENEW'
and rul.rule_information_category='LRT' order by rgp.cle_id;
select id comp_id,nvl(line_renewal_type_code,g_def_cle_ren) rule_type
from okc_k_lines_b
where dnz_chr_id = p_chr_id;
select application_id from okc_k_headers_b where id = p_chr_id;
SELECT k.id,k.contract_number,k.contract_number_modifier,k.start_date,k.END_date,k.object_version_number
FROM okc_k_headers_b k,
okc_statuses_b sts
WHERE k.date_renewed is null
and k.sts_code = sts.code
and sts.ste_code in ('ACTIVE','EXPIRED','SIGNED')
and k.date_terminated is null
and k.template_yn = 'N'
and k.id = p_chr_id ;
SELECT sts_code,template_yn, application_id, scs_code,contract_number,
contract_number_modifier,end_date,date_terminated, start_date
FROM okc_k_headers_b
WHERE id = p_chr_id;
SELECT ste_code
FROM okc_statuses_v
WHERE code = p_code;
p_msg_name => 'OKC_NO_UPDATE',
p_token1 => 'CHR',
p_token1_value => l_k);
SELECT ID,
STS_CODE,
CONTRACT_NUMBER,
CONTRACT_NUMBER_MODIFIER,
TEMPLATE_YN,
DATE_TERMINATED,
DATE_RENEWED,
END_DATE
FROM okc_k_headers_b
WHERE id = p_renew_in_parameters_rec.p_contract_id;
SELECT ste_code,meaning
FROM okc_statuses_v
WHERE code = p_sts_code;
SELECT 'x'
FROM okc_time_code_units_b
WHERE uom_code = p_renew_in_parameters_rec.p_uom_code;
SELECT qte.quote_number
FROM okc_k_rel_objs_v rel,
aso_quote_headers_all_v qte
WHERE jtot_object1_code = okc_oc_int_qtk_pvt.g_jtot_qte_hdr
AND chr_id = p_chr_id
AND rty_code = okc_oc_int_qtk_pvt.g_k2q_ren
AND rel.object1_id1 = qte.quote_header_id;
SELECT ID
FROM okc_k_lines_b l, okc_statuses_b sts
WHERE sts.code = l.sts_code
and sts.ste_code in ('ACTIVE','EXPIRED','SIGNED')
and l.dnz_chr_id = p_contract_id
and l.date_renewed is null
and l.date_terminated is null;
PROCEDURE update_rules(
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_chr_id IN number
) is
BEGIN
NULL;
END update_rules;
PROCEDURE update_rules(
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_chr_id IN number
) is
CURSOR cur_rules(p_chr_id number) is
SELECT nvl(rg.cle_id,rg.chr_id) parent_ID,ru.id id,ru.object_version_number,
ru.comments,ru.rule_information_category from
okc_rules_v ru,okc_rule_groups_b rg
WHERE rgp_id=rg.id and rg.DNZ_CHR_ID = p_chr_id for update of ru.id nowait;
SELECT start_date ,end_date FROM okc_time_ia_startend_val_v
WHERE id = p_tve_id for update of id nowait;
l_api_name constant varchar2(30) := 'update_rules';
SELECT id, orig_start_date, orig_end_date, start_date, end_date
FROM okc_cle_dates_tmp
WHERE id = b_id;
--san comment update the ia
-- okc_debug.log('2300: Before update_ia_startend');
OKC_TIME_PUB.UPDATE_IA_STARTEND(
p_api_version=> p_api_version,
p_init_msg_list=> okc_api.g_false,
x_return_status=> l_return_status,
x_msg_count=> x_msg_count,
x_msg_data=> x_msg_data,
p_isev_ext_rec=> l_isev_ext_rec_type,
x_isev_ext_rec=> i_isev_ext_rec_type) ;
OKC_RULE_PUB.UPDATE_RULE( p_api_version => 1,
p_init_msg_list => OKC_API.G_FALSE,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_rulv_rec => l_rulv_rec,
x_rulv_rec => i_rulv_rec
);
okc_debug.Set_Indentation('Update_Rules');
okc_debug.log('3000: Entered Update_Rules', 2);
okc_debug.log('4300: Leaving Update_Rules', 2);
okc_debug.log('4400: Exiting Update_Rules:E_Resource_Busy Exception', 2);
okc_debug.log('4500: Exiting Update_Rules:OKC_API.G_EXCEPTION_ERROR Exception', 2);
okc_debug.log('4600: Exiting Update_Rules:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
okc_debug.log('4700: Exiting Update_Rules:OTHERS Exception', 2);
END Update_Rules;
PROCEDURE update_condition_headers (
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_chr_id IN NUMBER,
p_orig_start_date IN DATE,
p_orig_end_date IN DATE,
p_new_start_date IN DATE,
p_new_end_date IN DATE ) is
CURSOR cur_condition_headers is
SELECT cnh.id,cnh.object_version_number,cnh.date_active,cnh.date_inactive
FROM okc_condition_headers_b cnh
WHERE dnz_chr_id = p_chr_id;
l_api_name constant varchar2(30) := 'update_headers';
okc_debug.log('4800: Entered update_condition_headers', 2);
okc_debug.log('5000: Before update_cond_hdrs');
okc_conditions_pub.update_cond_hdrs(
p_api_version => 1,
p_init_msg_list => OKC_API.G_FALSE,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_cnhv_rec => l_cnh_rec,
x_cnhv_rec => i_cnh_rec );
okc_debug.log('5100: After update_cond_hdrs');
okc_debug.log(' 5200: Leaving update_condition_headers', 2);
okc_debug.log('5300: Exiting update_condition_headers:E_Resource_Busy Exception', 2);
okc_debug.log('5400: Exiting update_condition_headers:OKC_API.G_EXCEPTION_ERROR Exception', 2);
okc_debug.log('5500: Exiting update_condition_headers:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
okc_debug.log('5600: Exiting update_condition_headers:OTHERS Exception', 2);
END update_condition_headers;
PROCEDURE update_old_contract(
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_new_header IN number,
p_chr_id IN number) is
CURSOR cur_header(p_chr_id number) is
SELECT id,object_version_number
FROM okc_k_headers_b
WHERE id = p_chr_id;
SELECT a.id,b.id cle_id_ren_to ,a.object_version_number
FROM okc_k_lines_b a,okc_k_lines_b b
WHERE a.dnz_chr_id = p_chr_id and a.id=b.cle_id_renewed and b.dnz_chr_id=p_new_header;
l_api_name constant VARCHAR2(30) := 'update_old_contract';
okc_debug.log('5700: Entered update_old_contract', 2);
okc_debug.log('6000: Before update_contract_header');
okc_contract_pub.update_contract_header (
p_api_version => 1,
p_init_msg_list => OKC_API.G_FALSE,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_restricted_update => okc_api.g_true,
p_chrv_rec => l_chr_rec,
x_chrv_rec => i_chr_rec );
okc_debug.log('6100: After update_contract_header');
SELECT l.id,l.object_version_number
bulk collect into l_id_tbl,l_obj_tbl
FROM okc_k_lines_b l, okc_operation_lines a
where a.object_chr_id=p_chr_id and
a.subject_chr_id=p_new_header and
a.active_yn='Y'
and l.id=a.object_cle_id and
a.subject_cle_id is not null and a.object_cle_id is not null;
okc_contract_pub.update_contract_line (
p_api_version => 1,
p_init_msg_list => OKC_API.G_FALSE,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_restricted_update => okc_api.g_true,
p_clev_tbl => l_cle_tbl,
x_clev_tbl => x_cle_tbl );
okc_debug.log('6200: Leaving update_old_contract', 2);
okc_debug.log('6300: Exiting update_old_contract:OKC_API.G_EXCEPTION_ERROR Exception', 2);
okc_debug.log('6400: Exiting update_old_contract:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
okc_debug.log('6500: Exiting update_old_contract:OTHERS Exception', 2);
END update_old_contract;
PROCEDURE update_renewal_dates(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_chr_id IN NUMBER,
p_parent_cle_id IN NUMBER ,
p_parent_new_st_dt IN DATE,
p_parent_new_end_dt IN DATE,
p_parent_old_st_dt IN DATE,
p_cle_id IN NUMBER ,
p_rencon_yn IN VARCHAR2 ) is
BEGIN
null;
END update_renewal_dates;
PROCEDURE update_renewal_dates(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_chr_id IN NUMBER,
p_parent_cle_id IN NUMBER ,
p_parent_new_st_dt IN DATE,
p_parent_new_end_dt IN DATE,
p_parent_old_st_dt IN DATE,
p_cle_id IN NUMBER ,
p_rencon_yn IN VARCHAR2 ) is
--/Rules Migration/-added application id
CURSOR cur_headers is
Select start_date,end_date,object_version_number,application_id
from okc_k_headers_b
where id = p_chr_id;
SELECT id,object_version_number,start_date,end_date,level,cle_id
FROM okc_k_lines_b
where dnz_chr_id=p_id
start with (chr_id=p_id)
connect by prior id=cle_id;
SELECT id,object_version_number,start_date,END_date,level,cle_id,price_negotiated
FROM okc_k_lines_b
where dnz_chr_id=l_chr_id
start with (chr_id=l_chr_id)
connect by prior id=cle_id;
SELECT id,object_version_number,start_date,END_date,level,cle_id,price_negotiated
FROM okc_k_lines_b
where dnz_chr_id=l_chr_id
start with (id=l_cle_id)
connect by prior id=cle_id;
l_api_name constant VARCHAR2(30) := 'update_renewal_dates';
okc_debug.log('6700: Entered Update_Renewal_Dates', 2);
okc_debug.log('6800: Before update_contract_header');
okc_contract_pub.update_contract_header (
p_api_version => 1,
p_init_msg_list => OKC_API.G_FALSE,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_chrv_rec => l_chr_rec,
x_chrv_rec => i_chr_rec );
okc_debug.log('6900: After update_contract_header');
INSERT INTO okc_cle_dates_tmp(ID, ORIG_START_DATE, ORIG_END_DATE,
START_DATE, END_DATE)
VALUES (
p_chr_id, header_rec.start_date, header_rec.end_date,
p_parent_new_st_dt, p_parent_new_end_dt);
SELECT id
bulk collect into g_parent_id_tbl
FROM okc_k_lines_b
where dnz_chr_id=p_chr_id
start with (id=p_parent_cle_id)
connect by prior cle_id=id;
l_parent_cle_tbl.delete;
okc_debug.log('7100: Before update_contract_line');
SELECT TO_DATE(TO_CHAR(l_cle_rec.start_date, 'dd/mm/yyYY') || TO_CHAR(start_date, 'hh24:mi:ss'), 'dd/mm/yyYYhh24:mi:ss'),
decode(l_cle_rec.end_date, null, null, TO_DATE(TO_CHAR(l_cle_rec.end_date, 'dd/mm/yyYY') || TO_CHAR(end_date, 'hh24:mi:ss'), 'dd/mm/yyYYhh24:mi:ss'))
INTO l_cle_rec.start_date, l_cle_rec.end_date
FROM okc_k_lines_b
WHERE id = (SELECT object_cle_id FROM okc_operation_lines WHERE subject_cle_id = l_cle_rec.id);
okc_contract_pub.update_contract_line (
p_api_version => 1,
p_init_msg_list => OKC_API.G_FALSE,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_clev_rec => l_cle_rec,
x_clev_rec => i_cle_rec );
okc_debug.log('7200: After update_contract_line');
INSERT INTO okc_cle_dates_tmp (ID, ORIG_START_DATE, ORIG_END_DATE,
START_DATE, END_DATE)
VALUES (
l_cle_rec.id, lines_rec.start_date, lines_rec.end_date,
l_cle_rec.start_date, l_cle_rec.end_date);
OKC_CONTRACT_PUB.Delete_Contract_Line(
p_api_version => 1,
p_init_msg_list => OKC_API.G_FALSE,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_line_id => l_cle_tbl(j).id);
UPDATE OKC_K_HEADERS_B
set estimated_amount = estimated_amount - l_cle_tbl(j).price_negotiated
WHERE id = p_chr_id;
okc_debug.log('7300: Before update_rules');
update_rules(p_api_version => 1,
p_init_msg_list => OKC_API.G_FALSE,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_chr_id => p_chr_id
);
okc_debug.log('7400: After update_rules');
okc_debug.log('7500: Leaving Update_Renewal_Dates', 2);
okc_debug.log('7600: Exiting Update_Renewal_Dates:OKC_API.G_EXCEPTION_ERROR Exception', 2);
okc_debug.log('7700: Exiting Update_Renewal_Dates:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
okc_debug.log('7800: Exiting Update_Renewal_Dates:OTHERS Exception', 2);
END Update_Renewal_Dates;
SELECT nvl(rul.rule_information1,OKC_API.G_MISS_CHAR) renew_type,
nvl(rul.rule_information2,OKC_API.G_MISS_CHAR) contact
FROM okc_rules_b rul,okc_rule_groups_b rgp
WHERE rgp.dnz_chr_id = p_chr_id
and rgp.id=rul.rgp_id
--and rgp.rgd_code='RENEW'
and rul.rule_information_category='REN' ;
CURSOR cur_qa(p_chr_id number) is select nvl(qcl_id,OKC_API.G_MISS_NUM)
from OKC_K_HEADERS_b
where id=p_chr_id;
select nvl(qcl_id,OKC_API.G_MISS_NUM) qcl_id,
nvl(renewal_type_code,OKC_API.G_MISS_CHAR) renewal_type_code,renewal_notify_to,
application_id
from OKC_K_HEADERS_b
where id=p_chr_id;
is select pdf_id from okc_k_processes kp, okc_process_defs_b pd where
kp.chr_id=p_chr_id and kp.pdf_id=pd.id and pd.usage='APPROVE';
is select fnd.user_name from okx_resources_v res, fnd_user fnd where
fnd.user_id=res.user_id and res.id1=p_user_id;
SELECT k.estimated_amount,k.scs_code,scs.cls_code,k.sts_code
FROM OKC_K_HEADERS_B K,
OKC_SUBCLASSES_B SCS
WHERE k.id = p_renew_in_parameters_rec.p_contract_id
AND k.scs_code = scs.code;
select start_date,end_date from okc_k_headers_b where
id=p_chr_id;
-- call alex api to update status and resolve time values
-- update the date approved of the contract
IF (l_debug = 'Y') THEN
okc_debug.log('9300: Before k_approved');
SELECT nvl(rul.rule_information1, OKC_API.G_MISS_CHAR) renew_type,
fnd_date.Canonical_To_Date(rul.rule_information3) ultimate_end_date
--To_Date(rul.rule_information3, 'YYYY/MM/DD') ultimate_end_date
FROM okc_rules_b rul,
okc_rule_groups_b rgp
WHERE rgp.dnz_chr_id = p_chr_id
and rgp.id = rul.rgp_id
-- and rgp.rgd_code = 'RENEW'
and rul.rule_information_category = 'REN';
/*cursor cur_org is select authoring_org_id,inv_organization_id
from okc_k_headers_b
where id = p_renew_in_parameters_rec.p_contract_id;*/
select authoring_org_id,inv_organization_id,application_id,
nvl(renewal_type_code,OKC_API.G_MISS_CHAR) renewal_type_code,
renewal_end_date
from okc_k_headers_b
where id = p_renew_in_parameters_rec.p_contract_id;
okc_debug.log('12100: Before update_renewal_dates');
update_renewal_dates(p_api_version => 1,
p_init_msg_list => OKC_API.G_FALSE,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_chr_id => x_contract_id,
p_parent_new_st_dt => l_renew_in_parameters_rec.p_start_date,
p_parent_new_end_dt => l_renew_in_parameters_rec.p_end_date,
p_parent_old_st_dt => l_renew_in_parameters_rec.p_orig_start_date);
okc_debug.log('12200: After update_renewal_dates');
--update date_active and date_inactive in the condition headers for the renew copy
IF (l_debug = 'Y') THEN
okc_debug.log('123300: Before update_condition_headers');
update_condition_headers (p_api_version => 1,
p_init_msg_list => OKC_API.G_FALSE,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_chr_id => x_contract_id,
p_new_start_date => l_renew_in_parameters_rec.p_start_date,
p_new_end_date => l_renew_in_parameters_rec.p_end_date,
p_orig_start_date => l_renew_in_parameters_rec.p_orig_start_date,
p_orig_end_date => l_renew_in_parameters_rec.p_orig_end_date);
okc_debug.log('12400: After update_condition_headers');
okc_debug.log('12500: Before update_old_contract');
update_old_contract(p_api_version => 1,
p_init_msg_list => OKC_API.G_FALSE,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_new_header => x_contract_id ,
p_chr_id => l_renew_in_parameters_rec.p_contract_id );
okc_debug.log('12600: After update_old_contract');
-- If this contract is being renewed from Quote, then update
-- the contrcat with Quote information( like price, Ship-to etc.)
-- The p_context is not null then this contract is being renewed
-- by quote, so call the integration API that will update K with
-- quote information.
IF ( p_renew_in_parameters_rec.p_context IS NOT NULL
AND
p_renew_in_parameters_rec.p_context <> OKC_API.G_MISS_CHAR ) THEN
IF (l_debug = 'Y') THEN
okc_debug.log('12700: Before create_k_from_q');
Procedure Update_Parents_Date_Renewed( 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_chr_id IN NUMBER
) is
l_top_count NUMBER;
l_api_name constant VARCHAR2(30) := 'Update_Parents_Date_Renewed';
SELECT distinct(cle_id_ascendant)
FROM OKC_UPD_REN_TEMP
WHERE level_sequence = l_level;
okc_debug.log('13300: Entered Update_Parents_Date_Renewed', 2);
select count(*) into l_invalid_chr_id
from okc_k_headers_b
where id = p_chr_id;
okc_debug.log('13400: Before Insert Into OKC_UPD_REN_TEMP');
insert into OKC_UPD_REN_TEMP(DNZ_CHR_ID,
CLE_ID,
CLE_ID_ASCENDANT,
LEVEL_SEQUENCE)
select line.dnz_chr_id, ans.cle_id,
ans.cle_id_ascendant, ans.level_sequence
from okc_k_lines_b line, okc_ancestrys ans
where line.id = ans.cle_id
and line.dnz_chr_id = p_chr_id;
select count(*) into l_top_count
from okc_k_lines_b line, okc_ancestrys ans
where line.id = ans.cle_id
and line.dnz_chr_id = p_chr_id;
select MAX(level_sequence) into l_level
from OKC_UPD_REN_TEMP
where dnz_chr_id = p_chr_id;
select count(*) into l_line_count
from okc_k_lines_b
where cle_id = l_cle_id_ascendant;
select count(*) into l_date_count
from okc_k_lines_b
where cle_id = l_cle_id_ascendant
and date_renewed IS NULL;
-- Update Date_Renewed field of parent line
update okc_k_lines_b
set date_renewed = ( select MAX(date_renewed )
from okc_k_lines_b
where cle_id = l_cle_id_ascendant )
where id = l_cle_id_ascendant;
select count(*) into l_no_lines_count
from okc_k_lines_b
where chr_id = p_chr_id;
select count(*) into l_top_date_count
from okc_k_lines_b
where chr_id = p_chr_id
and date_renewed IS NULL;
-- Update Date_Renewed field of contract header
update okc_k_headers_b
set date_renewed = ( select MAX(date_renewed)
from okc_k_lines_b
where chr_id = p_chr_id )
where id = p_chr_id;
okc_debug.log('13500: Leaving Update_Parents_Date_Renewed', 2);
okc_debug.log('13600: Exiting Update_Parents_Date_Renewed:OKC_API.G_EXCEPTION_ERROR Exception', 2);
okc_debug.log('13700: Exiting Update_Parents_Date_Renewed:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
okc_debug.log('13800: Exiting Update_Parents_Date_Renewed:Contract_Not_Found Exception', 2);
okc_debug.log('13900: Exiting Update_Parents_Date_Renewed:OTHERS Exception', 2);
End Update_Parents_Date_Renewed;
SELECT a.subject_chr_id,
a.process_flag -- bugfix 2952330, selecting Process_flag to check the value 'A'
FROM okc_operation_lines a,okc_operation_instances b, okc_class_operations c
where a.object_chr_id=p_chr_id and
c.id=b.cop_id and
c.opn_code in ('RENEWAL', 'REN_CON')
and b.id=a.oie_id and
-- a.active_yn='Y' and /* Commented out to fix the bug 2108667 */
a.subject_cle_id is null and
a.object_cle_id is null
order by a.active_yn desc,a.process_flag desc; /* Added this order by to get the 'Entered' contract
SELECT CONTRACT_NUMBER,CONTRACT_NUMBER_MODIFIER,ste_code
FROM okc_k_headers_b k,okc_statuses_b s
where k.id=p_chr_id and k.sts_code=s.code;