ors_chargedealoper.sql

主要 用于通知故障现场通过短信的方式对技术员工提供处理故障的信息

CREATE OR REPLACE PROCEDURE ors_chargedealoper(
s_faultcause IN VARCHAR2,
s_rights IN VARCHAR2,
i_dealoper OUT INT
)
AS
	i_tmp	INT;
BEGIN
	DELETE ors_tmp_dealoper;
	DELETE ors_tmp_dealoper1;
	i_dealoper := 0;

	IF s_faultcause = '88' THEN
	BEGIN
		INSERT INTO ors_tmp_dealoper(operator,casenum) 
		SELECT operator,isbusy FROM ors_operator WHERE operator IN (
		SELECT operator FROM ors_operdealrelation WHERE flag = 2 AND rights = s_rights);
		INSERT INTO ors_tmp_dealoper(operator,casenum)
		SELECT dealoper,COUNT(*) FROM ors_ldrecord WHERE curstate < 4 AND
		dealoper IN (SELECT operator FROM ors_operdealrelation WHERE flag = 2
		AND rights = s_rights) and to_char(recepttime,'yyyy-mm-dd') = to_char(sysdate,'yyyy-mm-dd') 
		GROUP BY dealoper;
		commit;
	END;
	ELSE
	BEGIN
		INSERT INTO ors_tmp_dealoper(operator,casenum) 
		SELECT operator,isbusy FROM ors_operator WHERE operator IN (
		SELECT operator FROM ors_operdealrelation WHERE flag = 1 AND rights = s_faultcause);
		INSERT INTO ors_tmp_dealoper(operator,casenum)
		SELECT dealoper,COUNT(*) FROM ors_ldrecord WHERE curstate < 4 AND
		dealoper IN (SELECT operator FROM ors_operdealrelation WHERE flag = 1
		AND rights = s_faultcause) and to_char(recepttime,'yyyy-mm-dd') = to_char(sysdate,'yyyy-mm-dd') 
		GROUP BY dealoper;
		commit;
	END;
	END IF;
	INSERT INTO ors_tmp_dealoper1(operator,casenum)
	SELECT operator,sum(casenum) FROM ors_tmp_dealoper GROUP BY operator ORDER BY operator desc;
	commit;

  	SELECT nvl(operator,0) INTO i_dealoper FROM ors_tmp_dealoper1
  	WHERE casenum = (SELECT min(casenum) FROM ors_tmp_dealoper1) and rownum = 1;
  --end if;
END;
/
show errors;