📄 pacbody_regdateclass.sql
字号:
-- Start of DDL Script for Package Body ORALDBA.OMPS_PAGE_REGDATACLASS
-- Generated 13-一月-2008 0:14:35 from ORALDBA@ORAL
CREATE OR REPLACE
PACKAGE BODY omps_page_regdataclass
AS
-- To modify this template, edit file PKGBODY.TXT in TEMPLATE
-- directory of SQL Navigator
--
-- Purpose: Briefly explain the functionality of the package body
--
-- MODIFICATION HISTORY
-- Person Date Comments
-- --------- ------ ------------------------------------------
-- Enter procedure, function bodies as shown below
PROCEDURE PROC_OMPS_ADDNULLDATECLASS( --日期规则类型
i_rulecode IN VARCHAR2, --规则类型ID号
i_rulename IN VARCHAR2,
i_describe IN VARCHAR2, --规则描述
i_startdate IN VARCHAR2,
i_overdate IN VARCHAR2,
i_maker1 IN VARCHAR2,
i_state1 IN VARCHAR2,
i_remark1 IN VARCHAR2,
o_retCode OUT VARCHAR2, --返回标志,
o_err_msg OUT VARCHAR2 --错误信息
)
IS
in_last_editor_time varchar2(20) ;
BEGIN o_retCode := '0';
in_last_editor_time := TO_CHAR(CURRENT_TIMESTAMP,'YYYY-MM-DD HH24:MI:SS');
INSERT INTO OPDATEClASSRULE
(ruleid,rulecode,rulename,describes,startdate,enddate,maker1,state1,remark1,last_editor_time,state2)
VALUES
(OPDATERULE.NEXTVAL,i_rulecode,i_rulename,i_describe,i_startdate,i_overdate,i_maker1,i_state1,i_remark1,in_last_editor_time,'1');
commit;
EXCEPTION
WHEN OTHERS THEN
o_err_msg := SQLCODE||SQLERRM;
o_retCode := '-1';
ROLLBACK;
RETURN;
END PROC_OMPS_ADDNULLDATECLASS;
PROCEDURE PROC_OMPS_ADDUNREGDATECLASS1( --日期规则类型
i_rulecode IN VARCHAR2, --规则类型ID号
i_rulename IN VARCHAR2,
i_describe IN VARCHAR2, --规则描述
i_startdate IN VARCHAR2,
i_overdate IN VARCHAR2,
i_maker1 IN VARCHAR2,
i_state1 IN VARCHAR2,
i_remark1 IN VARCHAR2,
i_datestr IN VARCHAR2,
o_retCode OUT VARCHAR2, --返回标志,
o_err_msg OUT VARCHAR2 --错误信息
)
IS arrayobj ctp_type_arraytype;
i NUMBER (10);
v_temp VARCHAR2 (10);
in_last_editor_time varchar2(20) ;
countdate number;
r VARCHAR2(100);
t t_table;
BEGIN o_retCode := '0';
in_last_editor_time := TO_CHAR(CURRENT_TIMESTAMP,'YYYY-MM-DD HH24:MI:SS');
select count(*) into countdate
from OPDATEClASSRULE
where (rulename=i_rulename) ;
if(countdate=0)
then
INSERT INTO OPDATEClASSRULE
(ruleid,rulecode,rulename,describes,startdate,enddate,maker1,state1,remark1,last_editor_time,state2)
VALUES
(OPDATERULE.NEXTVAL,i_rulecode,i_rulename,i_describe,i_startdate,i_overdate,i_maker1,i_state1,i_remark1,in_last_editor_time,'1');
r := str2table(i_datestr
,t);
FOR i IN t.FIRST .. t.LAST
LOOP
INSERT INTO OPDATEClASS
(id,opdate,state,ruleid)
VALUES
(OPDATE.NEXTVAL,t(i),i_state1,OPDATERULE.CURRVAL);
END LOOP;
else
INSERT INTO OPDATEClASSRULE
(ruleid,rulecode,rulename,describes,startdate,enddate,maker1,state1,remark1,last_editor_time,state2)
VALUES
(OPDATERULE.NEXTVAL,i_rulecode,i_rulename||OPDATERULE.CURRVAL,i_describe,i_startdate,i_overdate,i_maker1,i_state1,i_remark1,in_last_editor_time,'1');
r := str2table(i_datestr
,t);
FOR i IN t.FIRST .. t.LAST
LOOP
INSERT INTO OPDATEClASS
(id,opdate,state,ruleid)
VALUES
(OPDATE.NEXTVAL,t(i),i_state1,OPDATERULE.CURRVAL);
END LOOP;
end if;
EXCEPTION
WHEN OTHERS THEN
o_err_msg := SQLCODE||SQLERRM;
o_retCode := '-1';
ROLLBACK;
RETURN;
END PROC_OMPS_ADDUNREGDATECLASS1;
PROCEDURE PROC_OMPS_QUYREGDATECLASS1(
i_maker1 IN VARCHAR2,
i_state1 IN VARCHAR2,
-- i_info IN VARCHAR2,
o_retCode OUT VARCHAR2,
o_err_msg OUT VARCHAR2,
o_rule_ref_detail OUT ref_detail
)
IS
BEGIN o_retcode := '0';
OPEN o_rule_ref_detail FOR
SELECT ruleid,rulecode,rulename,describes,startdate,enddate,remark1
FROM OPDATEClASSRULE
WHERE (maker1 = i_maker1) AND
(state1 = i_state1)
ORDER BY rulecode asc
;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
o_retcode := '1';
RETURN;
WHEN OTHERS
THEN
o_retcode := '-1';
RETURN;
END PROC_OMPS_QUYREGDATECLASS1;
PROCEDURE PROC_OMPS_DELUNREGDATERULE(
i_ruleid IN VARCHAR2,
-- i_info IN VARCHAR2,
o_retCode OUT VARCHAR2,
o_err_msg OUT VARCHAR2
)
IS
BEGIN o_retcode := '0';
delete
FROM OPDATEClASSRULE
WHERE (ruleid = i_ruleid) ;
delete
FROM OPDATEClASS
WHERE (ruleid = i_ruleid);
EXCEPTION
WHEN OTHERS
THEN
rollback;
o_retcode := '-1';
RETURN;
END PROC_OMPS_DELUNREGDATERULE;
PROCEDURE PROC_OMPS_QUYMAKER1(
o_retCode OUT VARCHAR2,
o_err_msg OUT VARCHAR2,
o_maker_ref_detail OUT ref_detail
)
IS
BEGIN o_retcode := '0';
OPEN o_maker_ref_detail FOR
SELECT distinct CTP_USER.ID,CTP_USER.NAME
FROM OPDATEClASSRULE left JOIN CTP_USER
ON OPDATEClASSRULE.maker1 =CTP_USER.ID or OPDATEClASSRULE.maker2 =CTP_USER.ID
where (OPDATEClASSRULE.state1 <>'1' and OPDATEClASSRULE.state1 <>'3')
ORDER BY CTP_USER.name asc
;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
o_retcode := '1';
RETURN;
WHEN OTHERS
THEN
o_retcode := '-1';
RETURN;
END PROC_OMPS_QUYMAKER1;
PROCEDURE PROC_OMPS_QUYMAKER2(
o_retCode OUT VARCHAR2,
o_err_msg OUT VARCHAR2,
o_maker2_ref_detail OUT ref_detail
)
IS
BEGIN o_retcode := '0';
OPEN o_maker2_ref_detail FOR
SELECT distinct CTP_USER.ID,CTP_USER.NAME
FROM OPDATEClASSRULE left JOIN CTP_USER
ON OPDATEClASSRULE.maker1 =CTP_USER.ID
ORDER BY CTP_USER.name asc
;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
o_retcode := '1';
RETURN;
WHEN OTHERS
THEN
o_retcode := '-1';
RETURN;
END PROC_OMPS_QUYMAKER2;
PROCEDURE PROC_OMPS_QUYMAKER3(
o_retCode OUT VARCHAR2,
o_err_msg OUT VARCHAR2,
o_maker3_ref_detail OUT ref_detail
)
IS
BEGIN o_retcode := '0';
OPEN o_maker3_ref_detail FOR
SELECT distinct CTP_USER.ID,CTP_USER.NAME
FROM OPDATEClASSRULE left JOIN CTP_USER
ON OPDATEClASSRULE.maker1 =CTP_USER.ID
where OPDATECLASSRULE.state1<>('2')
ORDER BY CTP_USER.name asc
;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
o_retcode := '1';
RETURN;
WHEN OTHERS
THEN
o_retcode := '-1';
RETURN;
END PROC_OMPS_QUYMAKER3;
PROCEDURE PROC_OMPS_QUYMAKER4(
o_retCode OUT VARCHAR2,
o_err_msg OUT VARCHAR2,
o_maker4_ref_detail OUT ref_detail
)
IS
BEGIN o_retcode := '0';
OPEN o_maker4_ref_detail FOR
SELECT distinct CTP_USER.ID,CTP_USER.NAME
FROM OPDATEClASSRULE left JOIN CTP_USER
ON OPDATEClASSRULE.maker1 =CTP_USER.ID
where OPDATECLASSRULE.state1=('2')
ORDER BY CTP_USER.name asc
;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
o_retcode := '1';
RETURN;
WHEN OTHERS
THEN
o_retcode := '-1';
RETURN;
END PROC_OMPS_QUYMAKER4;
PROCEDURE PROC_OMPS_QUYDATECLASS1(
i_rulename IN VARCHAR2,
i_rulecode IN VARCHAR2,
i_startdate IN VARCHAR2,
i_maker IN VARCHAR2,
i_state1 IN VARCHAR2,
-- i_info IN VARCHAR2,
o_retCode OUT VARCHAR2,
o_err_msg OUT VARCHAR2,
o_dateclass_ref_detail OUT ref_detail
)
IS
BEGIN o_retcode := '0';
if(i_maker='所有人')
then
OPEN o_dateclass_ref_detail FOR
SELECT a.ruleid,a.rulecode,a.rulename,
a.describes,a.startdate,a.enddate,a.remark1,b.name,c.name,a.state1
FROM OPDATEClASSRULE a join ctp_user b on a.maker1=b.ID full join ctp_user c on a.maker2=c.ID
WHERE
(a.state1 like '%'||i_state1||'%' ) and (upper(a.rulename) like '%'||upper(i_rulename)||'%') AND
(upper(a.rulecode) like '%'||upper(i_rulecode)||'%') AND
(a.startdate like '%'||i_startdate||'%')
ORDER BY a.rulecode desc;
else
OPEN o_dateclass_ref_detail FOR
SELECT a.ruleid,a.rulecode,a.rulename,a.describes,a.startdate,a.enddate,a.remark1,b.name,c.name,a.state1
FROM OPDATEClASSRULE a join ctp_user b on b.ID=a.maker1 left join ctp_user c on c.ID=a.maker2
WHERE (a.state1 like '%'||i_state1||'%') and (upper(a.rulename) like '%'||upper(i_rulename)||'%') AND
(upper(a.rulecode) like '%'||upper(i_rulecode)||'%') AND
(a.startdate like '%'||i_startdate||'%') AND
(a.maker1 like '%'||i_maker||'%' or a.maker2 like '%'||i_maker||'%' )
ORDER BY a.rulecode desc
;
end if;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
o_retcode := '1';
RETURN;
WHEN OTHERS
THEN
o_retcode := '-1';
RETURN;
END PROC_OMPS_QUYDATECLASS1;
PROCEDURE PROC_OMPS_QUYDATECLASS2(
i_rulename IN VARCHAR2,
i_rulecode IN VARCHAR2,
i_startdate IN VARCHAR2,
i_maker IN VARCHAR2,
i_state1 IN VARCHAR2,
-- i_info IN VARCHAR2,
o_retCode OUT VARCHAR2,
o_err_msg OUT VARCHAR2,
o_dateclass_ref_detail OUT ref_detail
)
IS
BEGIN o_retcode := '0';
if(i_maker='所有人')
then
OPEN o_dateclass_ref_detail FOR
SELECT a.ruleid,a.rulecode,a.rulename,
a.describes,a.startdate,a.enddate,a.remark1,b.name,c.name,a.state1
FROM OPDATEClASSRULE a join ctp_user b on a.maker1=b.ID full join ctp_user c on a.maker2=c.ID
WHERE
(a.state1='1' or a.state1='2') and (upper(a.rulename) like '%'||upper(i_rulename)||'%') AND
(upper(a.rulecode) like '%'||upper(i_rulecode)||'%') AND
(a.startdate like '%'||i_startdate||'%')
ORDER BY a.rulecode desc;
else
OPEN o_dateclass_ref_detail FOR
SELECT a.ruleid,a.rulecode,a.rulename,a.describes,a.startdate,a.enddate,a.remark1,b.name,c.name,a.state1
FROM OPDATEClASSRULE a join ctp_user b on b.ID=a.maker1 left join ctp_user c on c.ID=a.maker2
WHERE (a.state1='1' or a.state1='2' or a.state1='4') and (upper(a.rulename) like '%'||upper(i_rulename)||'%') AND
(upper(a.rulecode) like '%'||upper(i_rulecode)||'%') AND
(a.startdate like '%'||i_startdate||'%') AND
(a.maker1 like '%'||i_maker||'%' or a.maker2 like '%'||i_maker||'%' )
ORDER BY a.rulecode desc
;
end if;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
o_retcode := '1';
RETURN;
WHEN OTHERS
THEN
o_retcode := '-1';
RETURN;
END PROC_OMPS_QUYDATECLASS2;
PROCEDURE PROC_OMPS_QUYDETAILDATECLASS1(
i_ruleid IN VARCHAR2,
-- i_info IN VARCHAR2,
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -