oracle produce

发布时间：2020-12-12 16:06:32 所属栏目：百科来源：网络整理

导读：CREATE OR REPLACE PACKAGE BODY COP_CUST_POOL_PKG IS FUNCTION get_lock_status(P_CUST_PARTY_ID IN NUMBER, P_person_Id IN NUMBER) RETURN VARCHAR2 is v_flag varchar(20) := 'NEVER_LOCKED'; v_person_id number; BEGIN begin select c.person_id into

CREATE OR REPLACE PACKAGE BODY COP_CUST_POOL_PKG IS

FUNCTION get_lock_status(P_CUST_PARTY_ID IN NUMBER,
P_person_Id IN NUMBER) RETURN VARCHAR2

is
v_flag varchar(20) := 'NEVER_LOCKED';
v_person_id number;
BEGIN
begin
select c.person_id
into v_person_id
from COP_CUSTOMER_LOCKING c
where c.cust_party_id = P_CUST_PARTY_ID
and c.lock_status = 'VALID';
exception
when others then
v_flag := 'NEVER_LOCKED';
return v_flag;
end;
if v_person_id = P_person_Id then
begin
v_flag := 'MYSELF_LOCKED';
end;
else
begin
v_flag := 'OTHERS_LOCKED';
end;
end if;
RETURN v_flag;
END;

FUNCTION get_person_type(p_person_id in number) RETURN VARCHAR2

is
v_role varchar2(50);
v_role_id number;
BEGIN
begin
select r.role_id
into v_role_id
from cop_user_role u,cop_role r
where u.role_id = r.role_id
and u.user_id = p_person_id
and rownum = 1;
exception
when others then
v_role := '';
end;
if v_role_id in (2,5,1,10) then
v_role := 'FAE';
elsif v_role_id in (3,4,7,11) then
v_role := 'TSE';
elsif v_role_id in (12) then
v_role := 'OCS';
elsif v_role_id in (14) then
v_role := 'CS';
end if;
RETURN v_role;
exception
when others then
v_role := '';
return v_role;
END;

FUNCTION customer_Show_flag(P_CUST_PARTY_ID IN NUMBER,
P_person_Id IN NUMBER) RETURN VARCHAR2 is
BEGIN
RETURN 'Y';
END;

FUNCTION is_boss(p_person_id IN NUMBER) RETURN VARCHAR2 is
v_flag varchar(2) := 'N'; ----N:不存在下属 Y:存在下属
BEGIN
begin
select 'Y'
into v_flag
from web_employees_v emp
where emp.STATUS = 1
and (emp.UP1_BOSS_ID = p_person_id or
emp.UP2_BOSS_ID = p_person_id)
and rownum = 1;
exception
when others then
v_flag := 'N';
end;
-- dbms_output.put_line('v_flag=' || v_flag);
RETURN v_flag;
END;

PROCEDURE lock_customer(P_CUST_PARTY_ID IN NUMBER,
P_person_Id IN NUMBER,
P_PERSON_TYPE IN VARCHAR2,
P_out_code OUT VARCHAR2,
P_out_msg OUT VARCHAR2) IS
v_flag varchar2(20) := 'N'; ------v_flag N:不存在记录 Y:存在记录
BEGIN
savepoint step_1;
P_out_code := 'S';
P_out_msg := '执行成功';
----1.查询是否存在记录
begin
select 'Y'
into v_flag
from COP_CUSTOMER_LOCKING c
where c.person_id = P_person_Id
and c.cust_party_id = P_CUST_PARTY_ID
and rownum = 1;
exception
when others then
v_flag := 'N';
end;
----2.不存在记录时向锁定表中添加一条记录
IF v_flag = 'N' THEN
begin
insert into COP_CUSTOMER_LOCKING
(LOCK_ID,
PERSON_ID,
CUST_PARTY_ID,
LOCK_TYPE,
EFFECT_START_DATE,
LOCK_STATUS)
values
(cop_customer_locking_sq.nextval,
P_person_Id,
P_CUST_PARTY_ID,
P_PERSON_TYPE,
to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),
'VALID');
end;
end if;
---3.存在记录时修改记录的状态
if v_flag = 'Y' then
begin
update COP_CUSTOMER_LOCKING c
set c.effect_end_date = '',c.lock_status = 'VALID'
where c.person_id = P_person_Id
and c.cust_party_id = P_CUST_PARTY_ID;
end;
end if;
----3.日志表中添加一条记录
insert into COP_WORK_LOG
(LOG_ID,LOG_NAME,LOG_DATE,LOG_OWNER,CREATION_DATE,CUST_PARTY_ID)
values
(cop_work_log_id.nextval,
'锁定客户',
to_date(to_char(sysdate,'yyyy/mm/dd hh24:mi:ss'),
'yyyy/mm/dd hh24:mi:ss'),
P_CUST_PARTY_ID);
commit;
EXCEPTION
WHEN OTHERS THEN
rollback to step_1;
P_out_code := 'E';
P_out_msg := '执行失败';
commit;
--dbms_output.put_line('锁定回滚原因: '|| SQLERRm);
END lock_customer;
/* */
PROCEDURE unlock_customer(P_CUST_PARTY_ID IN NUMBER,
P_out_msg OUT VARCHAR2) IS

BEGIN
savepoint step_1;
P_out_code := 'S';
P_out_msg := '执行成功';
----------1.修改客户有效期
update COP_CUSTOMER_LOCKING c
set c.effect_end_date = to_char(sysdate,
c.lock_status = 'INVALID'
where c.person_id = P_person_Id
and c.cust_party_id = P_CUST_PARTY_ID;
---------2.日志表中添加一条记录
insert into COP_WORK_LOG
(LOG_ID,
'解锁客户',
P_CUST_PARTY_ID);
commit;
EXCEPTION
WHEN OTHERS THEN
rollback to step_1;
P_out_code := 'E';
P_out_msg := '执行失败';
commit;
END unlock_customer;

PROCEDURE assign_customer(P_CUST_PARTY_ID IN NUMBER,
p_unlock_flag IN VARCHAR2,
p_lock_person IN NUMBER,
P_out_msg OUT VARCHAR2) IS
v_flag varchar2(20) := 'E'; ------v_flag N:不存在记录 Y:存在
cursor t_cursor is
select c.lock_id,c.person_id
from COP_CUSTOMER_LOCKING c
where c.cust_party_id = P_CUST_PARTY_ID;
l_lock_id number;
BEGIN
savepoint step_1;
P_out_code := 'S';
P_out_msg := '执行成功';
-------1.如果不保存以前的锁定人先让其失效并保存一条记录
if p_unlock_flag = 'false' then
begin
for rec in t_cursor loop
update COP_CUSTOMER_LOCKING c
set c.effect_end_date = to_char(sysdate,
c.lock_status = 'INVALID'
where c.lock_id = rec.lock_id;

insert into COP_WORK_LOG
(LOG_ID,
LOG_NAME,
LOG_DATE,
LOG_OWNER,
CREATED_BY,
CREATION_DATE,
CUST_PARTY_ID)
values
(cop_work_log_id.nextval,
'领导释放客户',
rec.person_id,
P_CUST_PARTY_ID);
end loop;
end;
end if;
-----2.指派是查看是否已经存在记录，如果存在就修改时间和状态，如果不存在就添加一条记录
begin
select 'Y'
into v_flag
from COP_CUSTOMER_LOCKING c
where c.person_id = P_person_Id
and c.cust_party_id = P_CUST_PARTY_ID
and rownum = 1;
exception
when others then
v_flag := 'N';
end;
IF v_flag = 'N' THEN
begin
select cop_customer_locking_sq.nextval into l_lock_id from dual;
insert into COP_CUSTOMER_LOCKING
(LOCK_ID,
LOCK_STATUS)
values
(l_lock_id,
'领导指派客户',
P_CUST_PARTY_ID);
commit;
EXCEPTION
WHEN OTHERS THEN
P_out_code := 'e';
P_out_msg := '执行失败';
rollback to step_1;
commit;
END assign_customer;

PROCEDURE isFocus(id IN NUMBER,
person_id IN NUMBER,
tp IN VARCHAR2,
P_out_msg OUT VARCHAR2) IS
v_flag varchar2(20) := 'S';
begin
savepoint step_1;
P_out_code := 'S';
begin
select 'Y'
into v_flag
from COP_COP_MY_ATTENTION CMA
where CMA.OBJECT_ID = id
AND CMA.ATTEND_TYPE = tp
AND CMA.USER_ID = person_id
and rownum = 1;
exception
when others then
v_flag := 'N';
end;
IF v_flag = 'N' THEN
insert into COP_COP_MY_ATTENTION
(attend_type,Object_Id,User_Id,ATTEND_DATE)
values
(tp,
id,
person_id,
'yyyy/mm/dd hh24:mi:ss'));

else
delete from COP_COP_MY_ATTENTION CMA
where CMA.OBJECT_ID = id
AND CMA.ATTEND_TYPE = tp
AND CMA.USER_ID = person_id;
end if;
if v_flag = 'N' then
P_out_msg := '关注成功!';
else
P_out_msg := '取消关注成功!';
end if;
commit;
exception
when others then
P_out_code := 'E';
if v_flag = 'N' then
P_out_msg := '关注失败!';
else
P_out_msg := '取消关注失败!';
end if;
rollback to step_1;
commit;
END isFocus;

PROCEDURE addWorkLog(custPartyId IN NUMBER,
projectNo IN NUMBER,
orLineId IN NUMBER,
personId IN NUMBER,
logName IN VARCHAR2,
P_out_msg OUT VARCHAR2) IS

l_iface cop_work_log%rowtype;
begin
select cop_work_log_id.nextval into l_iface.log_id from dual;
l_iface.project_no := projectNo;
l_iface.log_name := logName;
l_iface.log_date := to_date(to_char(sysdate,
'yyyy/mm/dd hh24:mi:ss');
l_iface.log_owner := personId;
l_iface.creation_date := to_date(to_char(sysdate,
'yyyy/mm/dd hh24:mi:ss');
l_iface.created_by := personId;
l_iface.or_line_id := orLineId;
l_iface.cust_party_id := custPartyId;
insert into cop_work_log values l_iface;
P_out_code := 'S';
-- RAISE_APPLICATION_ERROR(-20000,'出异常了');
commit;
exception
when others then
P_out_code := 'E';
P_out_msg := '添加失败';
rollback to step_1;
commit;
END addWorkLog;

END;

declare l_in1 number; l_in2 number; l_in3 number; l_in4 number; l_in5 number; l_out1 varchar2(240); l_out2 varchar2(240); begin COP_CUST_POOL_PKG.addWorkLog(null,l_out1,l_out2); dbms_output.put_line(l_out1); dbms_output.put_line(l_out2); end;

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!