[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE update_pmp_rule_id(p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)
IS
CURSOR cu_pm_schedule IS
select
pms.id,
to_number(pml.object1_id1) pmp_rule_id
from
okc_rules_b pml,
oks_pm_schedules pms
where
rule_information_category ='PML'
and pml.id =pms.rule_id;
OKS_PMS_PVT.update_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_oks_pm_schedules_v_tbl => l_pms_tbl_in,
x_oks_pm_schedules_v_tbl =>l_pms_tbl_out);
END update_pmp_rule_id;
SELECT
id,object_version_number,dnz_chr_id
from oks_k_lines_b
where cle_id=p_cle_id;
SELECT
ID,
ACTIVITY_ID,
SELECT_YN,
CONF_REQ_YN,
SCH_EXISTS_YN,
--02/13 PROGRAM_ID,
CLE_ID
DNZ_CHR_ID
FROM
OKS_PM_ACTIVITIES_V
WHERE cle_id= p_template_cle_id;
SELECT
SEQUENCE_NUMBER ,
NUMBER_OF_OCCURENCES ,
START_DATE ,
END_DATE ,
FREQUENCY ,
FREQUENCY_UOM ,
OFFSET_DURATION ,
OFFSET_UOM,
AUTOSCHEDULE_YN,
--02/13 PROGRAM_ID ,
ACTIVITY_LINE_ID,
CLE_ID
DNZ_CHR_ID
FROM
oks_pm_stream_levels_v
WHERE cle_id = p_template_cle_id
AND ACTIVITY_LINE_ID is null
ORDER by SEQUENCE_NUMBER;
SELECT
SEQUENCE_NUMBER ,
NUMBER_OF_OCCURENCES ,
START_DATE ,
END_DATE ,
FREQUENCY ,
FREQUENCY_UOM ,
OFFSET_DURATION ,
OFFSET_UOM,
AUTOSCHEDULE_YN,
--02/13 PROGRAM_ID ,
ACTIVITY_LINE_ID,
CLE_ID
DNZ_CHR_ID
FROM
oks_pm_stream_levels_v
WHERE cle_id = p_template_cle_id
AND ACTIVITY_LINE_ID = cp_ACTIVITY_LINE_ID
ORDER by SEQUENCE_NUMBER;
SELECT pm_program_id
FROM OKS_K_LINES_B
WHERE CLE_ID = p_template_cle_id;
select max(to_number(SEQUENCE_NUMBER))
from oks_pm_stream_levels_V
where cle_id =p_template_cle_id
and ACTIVITY_LINE_ID is null;
select max(to_number(SEQUENCE_NUMBER))
from oks_pm_stream_levels_V
where cle_id = p_template_cle_id
and ACTIVITY_LINE_ID = cp_ACTIVITY_LINE_ID;
SELECT 'Y'
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')
AND ROWNUM=1;
x_pms_tbl.DELETE;
oks_pml_pvt.insert_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_pmlv_rec =>l_pmlrulv_tbl(pml_ctr),
x_pmlv_rec =>l_pmlrulvrec_Out);
okc_debug.log('After oks_pml_pvt insert_row', 3);
l_pmschvtbl_In.delete;
l_pmschvtbl_Out.delete; --CK 09/02
OKS_PMS_PVT.insert_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_oks_pm_schedules_v_tbl => l_pmschvtbl_In,
x_oks_pm_schedules_v_tbl => l_pmschvtbl_Out);
okc_debug.log('After OKS_PMS_PVT insert_row', 3);
OKS_CONTRACT_LINE_PUB.UPDATE_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_klnv_tbl => l_klnv_tbl_in,
x_klnv_tbl => l_klnv_tbl_out,
p_validate_yn => 'N');
okc_debug.log('After OKS_CONTRACT_LINE_PUB UPDATE_LINE', 3);
'update Oks_k_lines_b ');
/*CK RUL -- Insert program schedules
OKS_PMS_PVT.insert_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_oks_pm_schedules_v_tbl => l_pmschvtbl_In,
x_oks_pm_schedules_v_tbl => l_pmschvtbl_Out);
l_pmarulv_tbl(pma_ctr).SELECT_YN := cr_activities.SELECT_YN;
/*CK 09/27 oks_pma_pvt.insert_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_pmav_rec =>l_pmarulv_tbl(pma_ctr),
x_pmav_rec =>l_pmarulvrec_Out);
oks_pma_pvt.insert_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_pmav_rec =>l_pmarulv_tbl(pma_ctr),
x_pmav_rec =>l_pmarulvrec_Out);
okc_debug.log('After oks_pma_pvt insert_row', 3);
x_pms_tbl.DELETE; --CK 09/02
oks_pml_pvt.insert_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_pmlv_rec =>l_pmlrulv_tbl(pml_ctr),
x_pmlv_rec =>l_pmlrulvrec_Out);
okc_debug.log('After act oks_pml_pvt insert_row', 3);
l_pmschvtbl_In.delete;
l_pmschvtbl_Out.delete; --
OKS_PMS_PVT.insert_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_oks_pm_schedules_v_tbl => l_pmschvtbl_In,
x_oks_pm_schedules_v_tbl => l_pmschvtbl_Out);
okc_debug.log('After act OKS_PMS_PVT insert_row', 3);
oks_pma_pvt.update_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_pmav_rec =>l_pmarulv_tbl(pma_ctr),
x_pmav_rec =>l_pmarulvrec_Out);
'UPDATE pm activities');
/* -- Insert act schedules
OKS_PMS_PVT.insert_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_oks_pm_schedules_v_tbl => l_pmschvtbl_In,
x_oks_pm_schedules_v_tbl => l_pmschvtbl_Out);
x_pms_tbl.DELETE;
l_pmlrulv_tbl.DELETE;
SELECT
orig_system_id1 id
FROM
okc_k_lines_b
WHERE
id=p_contract_line_id;
SELECT ID,
START_DATE , --COV.START_DATE START_DATE,
END_DATE --COV.END_DATE END_DATE,
FROM OKC_K_LINES_B
WHERE id=p_contract_line_id;
SELECT
object_version_number
FROM
OKS_PM_ACTIVITIES
WHERE id=cp_activity_line_id;
oks_pml_pvt.insert_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_pmlv_rec =>l_pmlrulv_tbl(l_pml_index),
x_pmlv_rec =>l_pmlrulvrec_Out);
l_sch_tbl.delete;
OKS_PMS_PVT.insert_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_oks_pm_schedules_v_tbl => l_sch_tbl,
x_oks_pm_schedules_v_tbl =>l_sch_tbl_out);
oks_pma_pvt.update_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_pmav_rec =>l_pmarulv_rec,
x_pmav_rec =>l_pmarulv_rec_Out);
'update schedule exists');
delete all schedules and rules if new effectivity does not touch any of the existing schedules
thereafter create a rule and a schedules based on the old rule nearest to the new effectivity
and exit.
delete all schedules till new leading schedule and after new trailing schedule
delete all rules till rule of new leading schedule and rule after rule of new trailing schedule
update the new leading rule start date and the new trailing rule end date
update the new leading rule offset for all rules
if p_new_start_date < new leading schedule
(except when new leading schedule is first schedule of the first rule and the rule is autoscheduled)
create schedules and the rule offset by calculating backdates based on the first rule info and the p_new_start_date
if p_new_start_date < new leading schedule and
new leading schedule is first schedule of the first rule and the rule is autoscheduled
thereafter change the schedule sequences and the rule periods based on new number of
schedules for the rule
create schedules by calculating forward dates based on the last rule info and the p_new_end_date
if p_new_end_date > new trailing schedule and
new trailing schedule is last schedule of the last rule and the rule is autoscheduled
thereafter change rule periods based on new number of schedules for the rule
update new leading schedule schedule_date_from
if p_new_start_date >= new leading schedule schedule_date_from
therafter if not first schedule of the rule
update schedule sequence for all the schedules of the rule for the leading schedule
update periods for the rule of the new leading schedule
update new trailing schedule schedule_date_to
if p_new_end_date <= new trailing schedule schedule_date_to
therafter if not last schedule of the rule
update periods for the rule of the new trailing schedule
*/
PROCEDURE ADJUST_PM_PROGRAM_SCHEDULE
(p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
p_contract_line_id IN NUMBER,
p_new_start_date IN DATE,
p_new_end_date IN DATE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)
IS
CURSOR CU_CLE_ID is
select id
from okc_k_lines_b
where id=p_contract_line_id;
SELECT SEQUENCE_NUMBER ,
NUMBER_OF_OCCURENCES ,
START_DATE ,
END_DATE ,
FREQUENCY ,
FREQUENCY_UOM ,
OFFSET_DURATION ,
OFFSET_UOM,
AUTOSCHEDULE_YN,
PROGRAM_ID ,
ACTIVITY_LINE_ID,
CLE_ID
DNZ_CHR_ID
FROM OKS_PM_STREAM_LEVELS_V PML
WHERE CLE_ID=(select id from okc_k_lines_b where cle_id=p_contract_line_id
and lse_id in (2,15,20));
SELECT ID,
START_DATE, --COV.START_DATE START_DATE,
END_DATE --COV.END_DATE END_DATE,
FROM OKC_K_LINES_B
WHERE ID = p_contract_line_id;
SELECT ID,
RULE_ID,
PMA_RULE_ID,
PMP_RULE_ID,
OBJECT_VERSION_NUMBER,
DNZ_CHR_ID,
CLE_ID,
SCH_SEQUENCE,
SCHEDULE_DATE,
SCHEDULE_DATE_FROM,
SCHEDULE_DATE_TO,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
ACTIVITY_LINE_ID ,
STREAM_LINE_ID ,
SECURITY_GROUP_ID ,
PROGRAM_APPLICATION_ID ,
PROGRAM_ID ,
PROGRAM_UPDATE_DATE,
REQUEST_ID
FROM OKS_PM_SCHEDULES
WHERE STREAM_LINE_ID=cp_stream_line_id;
SELECT ID,
RULE_ID,
PMA_RULE_ID,
PMP_RULE_ID,
OBJECT_VERSION_NUMBER,
DNZ_CHR_ID,
CLE_ID,
SCH_SEQUENCE,
SCHEDULE_DATE,
SCHEDULE_DATE_FROM,
SCHEDULE_DATE_TO,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
ACTIVITY_LINE_ID ,
STREAM_LINE_ID ,
SECURITY_GROUP_ID ,
PROGRAM_APPLICATION_ID ,
PROGRAM_ID ,
PROGRAM_UPDATE_DATE,
REQUEST_ID
FROM OKS_PM_SCHEDULES
WHERE CLE_ID =(select id from okc_k_lines_b where cle_id=p_contract_line_id
and lse_id in (2,15,20))
order by nvl(schedule_date,schedule_date_from);*/
SELECT ID,
RULE_ID,
PMA_RULE_ID,
PMP_RULE_ID,
OBJECT_VERSION_NUMBER,
DNZ_CHR_ID,
CLE_ID,
SCH_SEQUENCE,
SCHEDULE_DATE,
SCHEDULE_DATE_FROM,
SCHEDULE_DATE_TO,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
ACTIVITY_LINE_ID ,
STREAM_LINE_ID ,
SECURITY_GROUP_ID ,
PROGRAM_APPLICATION_ID ,
PROGRAM_ID ,
PROGRAM_UPDATE_DATE,
REQUEST_ID
FROM OKS_PM_SCHEDULES
WHERE CLE_ID= p_contract_line_id --(select id from okc_k_lines_b where cle_id=p_contract_line_id
--and lse_id in (2,15,20)) commented by jvorugan as pm is associated with contract line
and ACTIVITY_LINE_ID is null
order by nvl(schedule_date,schedule_date_from);
SELECT ID,
RULE_ID,
PMA_RULE_ID,
PMP_RULE_ID,
OBJECT_VERSION_NUMBER,
DNZ_CHR_ID,
CLE_ID,
SCH_SEQUENCE,
SCHEDULE_DATE,
SCHEDULE_DATE_FROM,
SCHEDULE_DATE_TO,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
ACTIVITY_LINE_ID ,
STREAM_LINE_ID ,
SECURITY_GROUP_ID ,
PROGRAM_APPLICATION_ID ,
PROGRAM_ID ,
PROGRAM_UPDATE_DATE,
REQUEST_ID
FROM OKS_PM_SCHEDULES
WHERE ACTIVITY_LINE_ID = P_ACTIVITY_LINE_ID
order by nvl(schedule_date,schedule_date_from);
SELECT id,'PMP' TYPE
FROM OKS_K_LINES_B
WHERE CLE_ID= p_contract_line_id;--(select id from okc_k_lines_b where cle_id=p_contract_line_id
SELECT id,'PMA' TYPE
FROM OKS_PM_ACTIVITIES
WHERE CLE_ID= p_contract_line_id;
SELECT
ID,
SEQUENCE_NUMBER ,
NUMBER_OF_OCCURENCES ,
START_DATE ,
END_DATE ,
FREQUENCY ,
FREQUENCY_UOM ,
OFFSET_DURATION ,
OFFSET_UOM,
AUTOSCHEDULE_YN,
--02/12 PROGRAM_ID ,
ACTIVITY_LINE_ID,
CLE_ID,
DNZ_CHR_ID,
OBJECT_VERSION_NUMBER
FROM OKS_PM_STREAM_LEVELS_V PML
WHERE CLE_ID=p_contract_line_id
-- and lse_id in (2,15,20)) commented by jvorugan as pm is associated with contract line
AND ACTIVITY_LINE_ID IS NULL
ORDER BY ACTIVITY_LINE_ID,SEQUENCE_NUMBER;
SELECT
ID,
SEQUENCE_NUMBER ,
NUMBER_OF_OCCURENCES ,
START_DATE ,
END_DATE ,
FREQUENCY ,
FREQUENCY_UOM ,
OFFSET_DURATION ,
OFFSET_UOM,
AUTOSCHEDULE_YN,
--02/12 PROGRAM_ID ,
ACTIVITY_LINE_ID,
CLE_ID,
DNZ_CHR_ID,
OBJECT_VERSION_NUMBER
FROM OKS_PM_STREAM_LEVELS_V PML
WHERE CLE_ID=p_contract_line_id
-- and lse_id in (2,15,20)) commented by jvorugan as pm is associated with contract line
AND ACTIVITY_LINE_ID = cp_activity_line_id
ORDER BY ACTIVITY_LINE_ID,SEQUENCE_NUMBER;
pml_deleted varchar2(1);
l_pmschv_start.DELETE;
l_pmschv_end.DELETE;
l_rulv_tbl_in.delete;
l_pmlrulv_tbl.delete;
l_pmlrulv_tbl_del.delete;
l_pmlrulv_tbl_ins.delete;
l_pmlrulv_tbl_ins_out.delete;
l_pmlrulv_tbl_upd.delete;
l_pmlrulv_tbl_upd_out.delete;
l_pmlrulv_tbl_rfr.delete;
l_pmlrulv_tbl_start.delete;
l_pmlrulv_tbl_end.delete;
l_pms_tbl.delete;
l_rulv_Tbl_In.delete;
l_rulv_Tbl_Out.delete;
l_pmschvtbl_In.delete;
l_pmschvtbl_Out.delete;
l_pmschvtbl_Del.delete;
l_pmschvtbl_Upd.delete;
l_pmschvtbl_Upd_out.delete;
l_pmschv_start.delete;
l_pmschv_end.delete;
l_pmschvtbl_Ins.delete;
l_pmschvtbl_Ins_out.delete;
l_pmschv_tbl.delete;
l_pmschv_start.DELETE;
l_pmschv_end.DELETE;
l_pmschv_end.DELETE;
l_pmschv_end.DELETE;
l_pmlrulv_tbl_start.DELETE;
l_pmlrulv_tbl_end.DELETE;
OKS_PMS_PVT.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_oks_pm_schedules_v_tbl => l_pmschvtbl_Del);
okc_debug.log('After oks_pms_pvt delete_row', 3);
'delete pm schedules');
oks_pml_pvt.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_pmlv_tbl => l_pmlrulv_tbl_del);
okc_debug.log('After oks_pml_pvt delete_row', 3);
'delete pml rules');
OKS_PMS_PVT.insert_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_oks_pm_schedules_v_tbl => l_pmschvtbl_ins,
x_oks_pm_schedules_v_tbl => l_pmschvtbl_ins_out);
okc_debug.log('After oks_pms_pvt insert_row', 3);
'insert pm schedules');
oks_pml_pvt.insert_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_pmlv_tbl => l_pmlrulv_tbl_ins,
x_pmlv_tbl => l_pmlrulv_tbl_ins_out);
okc_debug.log('After oks_pml_pvt insert_row', 3);
'insert pml rules');
OKS_PMS_PVT.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_oks_pm_schedules_v_tbl => l_pmschvtbl_Del);
okc_debug.log('After oks_pms_pvt delete_row', 3);
'delete pm schedules');
oks_pml_pvt.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_pmlv_tbl => l_pmlrulv_tbl_del);
okc_debug.log('After oks_pml_pvt delete_row', 3);
'delete pml rules');
l_pmschvtbl_Ins.DELETE;
OKS_PMS_PVT.insert_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_oks_pm_schedules_v_tbl => l_pmschvtbl_ins,
x_oks_pm_schedules_v_tbl => l_pmschvtbl_ins_out);
okc_debug.log('After oks_pms_pvt insert_row', 3);
'inserting PM schedules');
l_pmschvtbl_Upd.DELETE;
OKS_PMS_PVT.update_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_oks_pm_schedules_v_tbl => l_pmschvtbl_upd,
x_oks_pm_schedules_v_tbl => l_pmschvtbl_upd_out);
okc_debug.log('After oks_pms_pvt update_row', 3);
l_pmschvtbl_Ins.DELETE;
OKS_PMS_PVT.insert_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_oks_pm_schedules_v_tbl => l_pmschvtbl_ins,
x_oks_pm_schedules_v_tbl => l_pmschvtbl_ins_out);
okc_debug.log('After oks_pms_pvt insert_row', 3);
'inserting PM schedules');
l_pmschvtbl_ins.DELETE;
l_pmschvtbl_Ins.DELETE;
OKS_PMS_PVT.insert_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_oks_pm_schedules_v_tbl => l_pmschvtbl_ins,
x_oks_pm_schedules_v_tbl => l_pmschvtbl_ins_out);
okc_debug.log('After oks_pms_pvt insert_row', 3);
'inserting PM schedules');
l_pmschvtbl_Upd.DELETE;
OKS_PMS_PVT.update_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_oks_pm_schedules_v_tbl => l_pmschvtbl_upd,
x_oks_pm_schedules_v_tbl => l_pmschvtbl_upd_out);
okc_debug.log('After oks_pms_pvt update_row', 3);
l_pmschvtbl_ins.DELETE;
l_pmschvtbl_Ins.DELETE;
OKS_PMS_PVT.insert_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_oks_pm_schedules_v_tbl => l_pmschvtbl_ins,
x_oks_pm_schedules_v_tbl => l_pmschvtbl_ins_out);
okc_debug.log('After oks_pms_pvt insert_row', 3);
'inserting PM schedules');
l_pmschvtbl_Ins.DELETE;
l_pmschvtbl_Upd.DELETE;
OKS_PMS_PVT.update_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_oks_pm_schedules_v_tbl => l_pmschvtbl_upd,
x_oks_pm_schedules_v_tbl => l_pmschvtbl_upd_out);
okc_debug.log('After oks_pms_pvt update_row', 3);
l_pmschvtbl_Upd.DELETE;
l_pmschvtbl_Upd.DELETE;
OKS_PMS_PVT.update_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_oks_pm_schedules_v_tbl => l_pmschvtbl_upd,
x_oks_pm_schedules_v_tbl => l_pmschvtbl_upd_out);
okc_debug.log('After oks_pms_pvt update_row', 3);
l_pmschvtbl_Upd.DELETE;
oks_pml_pvt.update_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_pmlv_tbl => l_pmlrulv_tbl_upd,
x_pmlv_tbl => l_pmlrulv_tbl_upd_out);
okc_debug.log('After oks_pml_pvt update_row', 3);
OKS_PMS_PVT.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_oks_pm_schedules_v_tbl => l_pmschvtbl_Del);
okc_debug.log('After oks_pms_pvt delete_row', 3);
'delete pm schedules');
oks_pml_pvt.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_pmlv_tbl => l_pmlrulv_tbl_del);
okc_debug.log('After oks_pml_pvt delete_row', 3);
'delete pml rules');
OKS_PMS_PVT.update_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_oks_pm_schedules_v_tbl => l_pmschvtbl_upd,
x_oks_pm_schedules_v_tbl => l_pmschvtbl_upd_out);
okc_debug.log('After oks_pms_pvt update_row', 3);
l_pmschvtbl_Ins.DELETE;
OKS_PMS_PVT.insert_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_oks_pm_schedules_v_tbl => l_pmschvtbl_ins,
x_oks_pm_schedules_v_tbl => l_pmschvtbl_ins_out);
okc_debug.log('After oks_pms_pvt insert_row', 3);
'insert pm schedules');
OKS_PMS_PVT.update_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_oks_pm_schedules_v_tbl => l_pmschvtbl_upd,
x_oks_pm_schedules_v_tbl => l_pmschvtbl_upd_out);
okc_debug.log('After oks_pms_pvt update_row', 3);
'update pm schedules');
oks_pml_pvt.update_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_pmlv_tbl => l_pmlrulv_tbl_upd,
x_pmlv_tbl => l_pmlrulv_tbl_upd_out);
okc_debug.log('After oks_pml_pvt update_row', 3);
OKS_PMS_PVT.update_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_oks_pm_schedules_v_tbl => l_pmschvtbl_upd,
x_oks_pm_schedules_v_tbl => l_pmschvtbl_upd_out);
okc_debug.log('After oks_pms_pvt update_row', 3);
oks_pml_pvt.update_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_pmlv_tbl => l_pmlrulv_tbl_upd,
x_pmlv_tbl => l_pmlrulv_tbl_upd_out);
okc_debug.log('After oks_pml_pvt update_row', 3);
OKS_PMS_PVT.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_oks_pm_schedules_v_tbl => l_pmschvtbl_Del);
okc_debug.log('After oks_pms_pvt delete_row', 3);
'delete pm schedules');
oks_pml_pvt.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_pmlv_tbl => l_pmlrulv_tbl_del);
'delete pml rules');
OKS_PMS_PVT.update_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_oks_pm_schedules_v_tbl => l_pmschvtbl_upd,
x_oks_pm_schedules_v_tbl => l_pmschvtbl_upd_out);
okc_debug.log('After oks_pms_pvt update_row', 3);
'update pm schedules');
oks_pml_pvt.update_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_pmlv_tbl => l_pmlrulv_tbl_upd,
x_pmlv_tbl => l_pmlrulv_tbl_upd_out);
l_pmschvtbl_Ins.DELETE;
OKS_PMS_PVT.insert_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_oks_pm_schedules_v_tbl => l_pmschvtbl_ins,
x_oks_pm_schedules_v_tbl => l_pmschvtbl_ins_out);
okc_debug.log('After oks_pms_pvt insert_row', 3);
'update pm schedules');
oks_pml_pvt.update_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_pmlv_tbl => l_pmlrulv_tbl_upd,
x_pmlv_tbl => l_pmlrulv_tbl_upd_out);
okc_debug.log('After oks_pml_pvt update_row', 3);
SELECT SEQUENCE_NUMBER ,
NUMBER_OF_OCCURENCES ,
FREQUENCY ,
FREQUENCY_UOM ,
nvl(OFFSET_DURATION,0) OFFSET_DURATION ,
nvl(OFFSET_UOM,0) OFFSET_UOM ,
AUTOSCHEDULE_YN,
ACTIVITY_LINE_ID,
CLE_ID
DNZ_CHR_ID
FROM OKS_PM_STREAM_LEVELS_V PML
WHERE CLE_ID=cp_cov_line_id
and activity_line_id is null
ORDER BY SEQUENCE_NUMBER;
SELECT SEQUENCE_NUMBER ,
NUMBER_OF_OCCURENCES ,
FREQUENCY ,
FREQUENCY_UOM ,
nvl(OFFSET_DURATION,0) OFFSET_DURATION ,
nvl(OFFSET_UOM,0) OFFSET_UOM ,
AUTOSCHEDULE_YN,
ACTIVITY_LINE_ID,
CLE_ID
DNZ_CHR_ID
FROM OKS_PM_STREAM_LEVELS_V PML
WHERE activity_line_id =cp_activity_line_id
ORDER BY SEQUENCE_NUMBER;
l_source_pml_tbl.delete;
l_target_pml_tbl.delete;
SELECT
ID ,
CLE_ID,
ACTIVITY_ID,
SELECT_YN,
CONF_REQ_YN,
SCH_EXISTS_YN
FROM
oks_pm_activities
WHERE
cle_id=cp_cov_line_id;
l_source_pma_tbl(src_pma_index).SELECT_YN := CR_get_PMA.SELECT_YN;
l_target_pma_tbl(tgt_pma_index).SELECT_YN := CR_get_PMA.SELECT_YN;
AND l_source_pma_tbl(src_pma_index1).SELECT_YN = l_target_pma_tbl(tgt_pma_index1).SELECT_YN
AND l_source_pma_tbl(src_pma_index1).CONF_REQ_YN = l_target_pma_tbl(tgt_pma_index1).CONF_REQ_YN
AND l_source_pma_tbl(src_pma_index1).SCH_EXISTS_YN = l_target_pma_tbl(tgt_pma_index1).SCH_EXISTS_YN THEN
l_pma_match :='Y';
l_source_pma_tbl.delete;
l_target_pma_tbl.delete;
SELECT srvcle.id
, srvcle.line_number
FROM
OKC_K_LINES_B srvcle,
OKC_K_LINES_B covcle,
OKS_K_LINES_B cov
WHERE
srvcle.chr_id = p_chr_id
and srvcle.id=covcle.cle_id
and covcle.id=cov.cle_id
and cov.pm_program_id is not null
and covcle.lse_id in (2,15,20);
select 'x' terminate
FROM okc_k_lines_b cle,
oks_k_lines_b pmp,
okx_pm_programs_v opv
WHERE cle.id = pmp.cle_id
and cle.dnz_chr_id = pmp.dnz_chr_id
and pmp.pm_program_id= opv.id1
and cle.cle_id=p_cle_id
-- and opv.mr_status_code ='TERMINATED';
select 'x' terminate
FROM okc_k_lines_b cle,
oks_k_lines_b pmp,
oks_pm_activities oksact,
okx_pm_activities_v act
WHERE cle.id = pmp.cle_id
and cle.dnz_chr_id = pmp.dnz_chr_id
--changed for using index and pmp.pm_program_id= oksact.program_id
and pmp.cle_id= oksact.cle_id
and oksact.activity_id=act.id1
and cle.cle_id=p_cle_id
-- and act.mr_status_code = 'TERMINATED'
and act.mr_status_code in ('TERMINATED','TERMINATE_PENDING')
and rownum <2;
SELECT srvcle.id
, srvcle.line_number
FROM
OKC_K_LINES_B srvcle,
OKC_K_LINES_B covcle,
OKS_K_LINES_B cov
WHERE
srvcle.chr_id = p_chr_id
and srvcle.id=covcle.cle_id
and covcle.id=cov.cle_id
and cov.pm_program_id is not null
and covcle.lse_id in (2,15,20);
select opv.mr_header_id
FROM okc_k_lines_b cle,
oks_k_lines_b pmp,
--okx_pm_programs_v opv
ahl_mr_headers_b opv
WHERE cle.id = pmp.cle_id
and cle.dnz_chr_id = pmp.dnz_chr_id
and pmp.pm_program_id= opv.mr_header_id
and cle.cle_id=p_cle_id
and trunc(opv.effective_from) > trunc(cle.start_date);
select act.mr_header_id
FROM okc_k_lines_b cle,
oks_k_lines_b pmp,
oks_pm_activities oksact,
ahl_mr_headers_b act
WHERE cle.id = pmp.cle_id
and cle.dnz_chr_id = pmp.dnz_chr_id
and pmp.cle_id= oksact.cle_id
and oksact.activity_id=act.mr_header_id
and cle.cle_id=p_cle_id
and trunc(act.effective_from) > trunc(cle.start_date)
and rownum <2;
SELECT srvcle.id
, srvcle.line_number
FROM
OKC_K_LINES_B srvcle,
OKC_K_LINES_B covcle,
OKS_K_LINES_B cov
WHERE
srvcle.chr_id = p_chr_id
and srvcle.id=covcle.cle_id
and covcle.id=cov.cle_id
and cov.pm_program_id is not null
and covcle.lse_id in (2,15,20);
SELECT srvcle.id
, srvcle.line_number
FROM
OKC_K_LINES_B srvcle,
OKS_K_LINES_B cov
WHERE
srvcle.chr_id = p_chr_id
and srvcle.id=cov.cle_id
and cov.pm_program_id is not null
and srvcle.lse_id in (1,14,19);
SELECT cle.id
, lse.lty_code
, cle.name
, cle.start_date
, cle.end_date
, cim.jtot_object1_code
, cim.object1_id1
, cim.object1_id2
, cle.line_number
FROM OKC_K_ITEMS cim,
OKC_LINE_STYLES_B lse,
OKC_K_LINES_V cle
WHERE cim.cle_id = cle.id
and lse.id = cle.lse_id
and cle.chr_id = p_chr_id
and cle.lse_id in (2,15,20);*/
SELECT pmp.id
FROM okc_k_lines_b cle,oks_k_lines_b pmp
WHERE cle.id = pmp.cle_id
and cle.dnz_chr_id = pmp.dnz_chr_id
and cle.cle_id=p_cle_id
and pmp.pm_program_id is not null;
SELECT pms.id
FROM okc_k_lines_b cle,
oks_pm_schedules_v pms
WHERE cle.id = pms.cle_id
and cle.cle_id=p_cle_id
--added condition
and pms.activity_line_id is null;
SELECT pms.id
FROM oks_pm_schedules_v pms
WHERE pms.cle_id = p_cle_id
--added condition
and pms.activity_line_id is null;
SELECT pma.id
FROM okc_k_lines_b cle,
oks_pm_activities pma
WHERE cle.id = pma.cle_id
and cle.cle_id=p_cle_id;
SELECT pma.id
FROM oks_pm_activities pma
WHERE pma.cle_id = p_cle_id;
SELECT pms.id
FROM okc_k_lines_b cle,
oks_pm_schedules_v pms
WHERE cle.id = pms.cle_id
and cle.cle_id=p_cle_id
and activity_line_id is not null;
SELECT pms.id
FROM oks_pm_schedules_v pms
WHERE pms.cle_id = p_cle_id
and activity_line_id is not null;
SELECT srvcle.id
, srvcle.line_number
FROM
OKC_K_LINES_B srvcle,
OKC_K_LINES_B covcle,
OKS_K_LINES_B cov
WHERE
srvcle.chr_id = p_chr_id
and srvcle.id=covcle.cle_id
and covcle.id=cov.cle_id
and cov.pm_program_id is not null
and covcle.lse_id in (2,15,20);
SELECT srvcle.id
, srvcle.line_number
FROM
OKC_K_LINES_B srvcle,
OKS_K_LINES_B cov
WHERE
srvcle.chr_id = p_chr_id
and srvcle.id=cov.cle_id
and cov.pm_program_id is not null
and srvcle.lse_id in (1,14,19);
SELECT act.id1 activity_id
FROM okc_k_lines_b cle,
oks_k_lines_b okscle,
okx_pm_activities_v act
WHERE cle.id = okscle.cle_id
and cle.dnz_chr_id = okscle.dnz_chr_id
and okscle.pm_program_id= act.program_id
and cle.id =p_cle_id
-- modified by Jvorugan for Bug:5215180 and cle.cle_id=p_cle_id
and act.mr_status_code='COMPLETE';
SELECT oksact.activity_id
FROM okc_k_lines_b cle,
oks_k_lines_b okscle,
oks_pm_activities_v oksact
WHERE cle.id = okscle.cle_id
and cle.dnz_chr_id = okscle.dnz_chr_id
and cle.id=oksact.cle_id
and cle.dnz_chr_id=oksact.dnz_chr_id
and cle.id=p_cle_id;
l_act_tbl.DELETE;
SELECT ID FROM OKS_PM_ACTIVITIES
WHERE CLE_ID = p_cle_id;
SELECT ID FROM OKS_PM_STREAM_LEVELS
WHERE CLE_ID = p_cle_id;
SELECT ID FROM OKS_PM_SCHEDULES
WHERE CLE_ID = p_cle_id;
OKS_PMA_PVT.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_pmav_tbl => l_pmav_tbl);
okc_debug.log('After OKS_PMA_PVT delete_row', 3);
OKS_PML_PVT.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_pmlv_tbl => l_pmlv_tbl);
okc_debug.log('After OKS_PML_PVT delete_row', 3);
OKS_PMS_PVT.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_oks_pm_schedules_v_tbl => l_pm_schedules_v_tbl);
okc_debug.log('After OKS_PMS_PVT delete_row', 3);
x_klnv_tbl(1).LAST_UPDATED_BY := OKC_API.G_MISS_NUM;
x_klnv_tbl(1).LAST_UPDATE_DATE := OKC_API.G_MISS_DATE;
x_klnv_tbl(1).LAST_UPDATE_LOGIN := OKC_API.G_MISS_NUM;
PROCEDURE Delete_PMHistory(
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_chr_id CONSTANT NUMBER := p_chr_id;
DELETE OKS_PM_SCHEDULES
WHERE dnz_chr_id = l_chr_id;
DELETE OKS_PM_STREAM_LEVELS
WHERE dnz_chr_id = l_chr_id;
DELETE OKS_PM_ACTIVITIES
WHERE dnz_chr_id = l_chr_id;
END Delete_PMHistory;
PROCEDURE Delete_PMSaved_Version(
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_api_version NUMBER := 1;
l_api_name VARCHAR2(30):= 'Delete_Saved_Version';
DELETE OKS_PM_SCHEDULES_H
WHERE dnz_chr_id = l_chr_id
And major_version = -1;
DELETE OKS_PM_STREAM_LEVELS_H
WHERE dnz_chr_id = l_chr_id
And major_version = -1;
DELETE OKS_PM_ACTIVITIES_H
WHERE dnz_chr_id = l_chr_id
And major_version = -1;
END Delete_PMSaved_Version;
INSERT INTO oks_pm_activities
( ID,
CLE_ID,
DNZ_CHR_ID,
ACTIVITY_ID,
SELECT_YN,
CONF_REQ_YN,
SCH_EXISTS_YN,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
OBJECT_VERSION_NUMBER,
SECURITY_GROUP_ID,
REQUEST_ID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
-- SERVICE_LINE_ID,
ORIG_SYSTEM_ID1,
ORIG_SYSTEM_SOURCE_CODE,
ORIG_SYSTEM_REFERENCE1 )
SELECT
okc_p_util.raw_to_number(sys_guid()),
p_new_cle_id CLE_ID,
DNZ_CHR_ID,
ACTIVITY_ID,
SELECT_YN,
CONF_REQ_YN,
SCH_EXISTS_YN,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
OBJECT_VERSION_NUMBER,
SECURITY_GROUP_ID,
REQUEST_ID,
FND_GLOBAL.USER_ID CREATED_BY,
SYSDATE CREATION_DATE,
FND_GLOBAL.USER_ID LAST_UPDATED_BY,
SYSDATE LAST_UPDATE_DATE,
FND_GLOBAL.LOGIN_ID LAST_UPDATE_LOGIN,
-- SERVICE_LINE_ID,
ID ORIG_SYSTEM_ID1,
ORIG_SYSTEM_SOURCE_CODE,
ORIG_SYSTEM_REFERENCE1
FROM oks_pm_activities
WHERE cle_id =p_old_cle_id;
INSERT INTO oks_pm_stream_levels
( ID,
CLE_ID,
DNZ_CHR_ID,
ACTIVITY_LINE_ID,
SEQUENCE_NUMBER,
NUMBER_OF_OCCURENCES,
START_DATE,
END_DATE,
FREQUENCY,
FREQUENCY_UOM,
OFFSET_DURATION,
OFFSET_UOM,
AUTOSCHEDULE_YN,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
OBJECT_VERSION_NUMBER,
SECURITY_GROUP_ID,
REQUEST_ID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
-- TOLERANCE_BEFORE,
-- TOLERANCE_AFTER,
-- SERVICE_LINE_ID,
ORIG_SYSTEM_ID1,
ORIG_SYSTEM_SOURCE_CODE,
ORIG_SYSTEM_REFERENCE1 )
SELECT
okc_p_util.raw_to_number(sys_guid()),
p_new_cle_id CLE_ID,
DNZ_CHR_ID,
DECODE(ACTIVITY_LINE_ID,NULL,NULL,(SELECT id from oks_pm_activities where ORIG_SYSTEM_ID1 =ACTIVITY_LINE_ID and cle_id =p_new_cle_id)),
SEQUENCE_NUMBER,
NUMBER_OF_OCCURENCES,
START_DATE,
END_DATE,
FREQUENCY,
FREQUENCY_UOM,
OFFSET_DURATION,
OFFSET_UOM,
AUTOSCHEDULE_YN,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
OBJECT_VERSION_NUMBER,
SECURITY_GROUP_ID,
REQUEST_ID,
FND_GLOBAL.USER_ID CREATED_BY,
SYSDATE CREATION_DATE,
FND_GLOBAL.USER_ID LAST_UPDATED_BY,
SYSDATE LAST_UPDATE_DATE,
FND_GLOBAL.LOGIN_ID LAST_UPDATE_LOGIN,
-- TOLERANCE_BEFORE,
-- TOLERANCE_AFTER,
-- SERVICE_LINE_ID,
ID ORIG_SYSTEM_ID1,
ORIG_SYSTEM_SOURCE_CODE,
ORIG_SYSTEM_REFERENCE1
FROM oks_pm_stream_levels
WHERE cle_id=p_old_cle_id;