procedure proc_update_channel_org(p_start_date in date,
p_end_date in date) is
cursor cur_dept_info is
select (select b.organ_id
from lcm_company_organ b
where a.organ_code = b.organ_code) as organ_id,
nvl2(a.close_date, 2, 1) as status,
nvl2(a.close_date, a.close_date, a.setup_date) as status_date,
a.*
from lcm_dept_info a
where a.channel_type in
(select sc.min_value
from lcm_special_control sc
where sc.control_no = 53
and sc.max_value = 'Y')
and (a.updated_date between p_start_date and p_end_date or exists
(select 'x'
from lcm_dept_hierarchy h
where a.dept_code = h.dept_code
and h.updated_date between p_start_date and p_end_date))
and a.dept_level not in ('0100', '1200')
order by a.dept_level;
r_dept_info cur_dept_info%rowtype;
cursor cur_parent_id(c_dept_code varchar2) is
select b.channel_id
from lcm_dept_hierarchy a, t_channel_org b
where a.dept_code = c_dept_code
and a.parent_dept_code = b.channle_code
order by a.start_date desc;
v_parent_id number(10);
cursor cur_channel_grade(c_dept_level varchar2) is
select dept_level_position
from lcm_dept_level
where dept_level = c_dept_level;
v_channel_grade number(2);
cursor cur_dept_id(c_department_code varchar2) is
select dept_id
from t_dept a
where a.dept_code = c_department_code
and a.dept_type = '0';
v_dept_id number(10);
cursor cur_manager_code(c_dept_code varchar2) is
select a.agent_code
from lcm_agent_rank_info a,
lcm_child_dept_synch b,
lcm_agent_rank r
where a.end_date is null
and a.dept_code = b.child_dept_code
and b.parent_dept_code = c_dept_code
and a.rank = r.rank
and r.rank_type >= '03';
cursor cur_majordomo_code(c_dept_code varchar2) is
select a.agent_code
from lcm_agent_rank_info a,
lcm_child_dept_synch b,
lcm_agent_rank r
where a.end_date is null
and a.dept_code = b.child_dept_code
and b.parent_dept_code = c_dept_code
and a.rank = r.rank
and r.rank_type >= '04';
cursor cur_leader_id(c_agent_code varchar2) is
select agent_id from t_agent where agent_code = c_agent_code;
v_leader_id number(10);
v_leader_code lcm_agent_info.AGENT_CODE%type;
v_user_id number(10);
begin
v_user_id := 401;
pkg_pub_app_context.p_set_app_user_id(v_user_id);
open cur_dept_info;
loop
fetch cur_dept_info
into r_dept_info;
exit when cur_dept_info%notfound;
v_parent_id := null;
v_leader_code := null;
v_leader_id := null;
v_channel_grade := null;
v_dept_id := null;
open cur_channel_grade(r_dept_info.dept_level);
fetch cur_channel_grade
into v_channel_grade;
close cur_channel_grade;
if r_dept_info.dept_level in ('0101', '1201') then
open cur_dept_id(r_dept_info.department_code);
fetch cur_dept_id
into v_dept_id;
close cur_dept_id;
elsif r_dept_info.dept_level in ('1602', '0201') then
v_parent_id := null;
else
open cur_parent_id(r_dept_info.dept_code);
fetch cur_parent_id
into v_parent_id;
close cur_parent_id;
end if;
if v_channel_grade in (3, 4) then
if v_channel_grade = 4 then
open cur_manager_code(r_dept_info.dept_code);
fetch cur_manager_code
into v_leader_code;
close cur_manager_code;
else
open cur_majordomo_code(r_dept_info.dept_code);
fetch cur_majordomo_code
into v_leader_code;
close cur_majordomo_code;
end if;
open cur_leader_id(v_leader_code);
fetch cur_leader_id
into v_leader_id;
close cur_leader_id;
end if;
update t_channel_org
set parent_id = v_parent_id,
channel_name = r_dept_info.dept_name,
channle_code = r_dept_info.dept_code,
leader_id = v_leader_id,
channel_type = r_dept_info.channel_type,
org_id = r_dept_info.organ_id,
status = r_dept_info.status,
status_date = r_dept_info.status_date,
status_reason = null,
channel_grade = v_channel_grade,
telephone = null,
fax = null,
email = null,
address_id = null,
updated_by = v_user_id,
update_time = sysdate,
update_timestamp = sysdate,
dept_id = v_dept_id,
nurture_id = null
where channel_id = r_dept_info.channel_id;
if sql%rowcount = 0 then
proc_insert_t_party(r_dept_info.channel_id, '2');
insert into t_channel_org
(channel_id,
parent_id,
channel_name,
channle_code,
leader_id,
channel_type,
org_id,
status,
status_date,
status_reason,
channel_grade,
telephone,
fax,
email,
address_id,
inserted_by,
updated_by,
insert_time,
update_time,
insert_timestamp,
update_timestamp,
create_date,
dept_id,
nurture_id)
values
(r_dept_info.channel_id,
v_parent_id,
r_dept_info.dept_name,
r_dept_info.dept_code,
v_leader_id,
r_dept_info.channel_type,
r_dept_info.organ_id,
r_dept_info.status,
r_dept_info.status_date,
null,
v_channel_grade,
null,
null,
null,
null,
v_user_id,
v_user_id,
sysdate,
sysdate,
sysdate,
sysdate,
sysdate,
v_dept_id,
null);
end if;
end loop;
close cur_dept_info;
end proc_update_channel_org;要将这段代码改造为kettle的格式，将
其中字段对应关系为
团险人管 t_channel_org_tmp 个险核心 t_channel_org lcm
parent_id parent_id v_parent_id
channel_id channel_id r_dept_info.channel_id
channel_name channel_name r_dept_info.dept_name
org_id org_id r_dept_info.organ_id
channle_code channle_code r_dept_info.dept_code
channel_type channel_type r_dept_info.channel_type
channel_grade channel_grade v_channel_grade 该如何书写代码
本文探讨了在设计机构表时为何需要使用org_code字段而非仅依赖parent_id字段来表示层级关系。通过对比两种方法，文章强调了org_code字段在实现简单明了及快速定位方面的优势。
扫一扫
2万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
