ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > Linux操作系统 > Linux操作系统 > 組織同步的解決方案Ultimus之主管同步

組織同步的解決方案Ultimus之主管同步

原创 Linux操作系统 作者：yxw2003110 时间：2008-03-19 16:43:02 0 删除 编辑

CREATE OR REPLACE PACKAGE BODY GROUPSYNC_MANAGER is
PROCEDURE MyProcedure(Param1 IN NUMBER) is
a varchar2(100);
begin
a:='ddd';
end MyProcedure;

PROCEDURE HRM_UPDATE_MANAGER(P_EMP_ID IN VARCHAR2,P_EMP_NAME IN VARCHAR2,P_HR_DPT_ID IN VARCHAR2,P_VALID_FLAG IN NUMBER,P_M_DPT_ID IN VARCHAR2,P_EMP_JOB in VARCHAR2,P_USER IN VARCHAR2,ERRMSG OUT VARCHAR2)
IS
V_CR NUMBER;
V_COUNT_RECORD NUMBER;
V_EMP_NTACCNT VARCHAR2(255);
V_JOB_ID VARCHAR2(255);
V_HR_DPTNAME VARCHAR2(255);
V_M_DPTNAME VARCHAR2(255);
V_OLD_EMPID VARCHAR2(255);
V_OLD_DPTID VARCHAR2(255);
V_OLD_EMPNAME VARCHAR2(255);
V_DPT_LEVEL NUMBER;
V_OLD_JOBNAME VARCHAR2(255);
V_OLD_POSID VARCHAR2(255);
V_OLD_JOBID VARCHAR2(255);
V_OLD_DPTLEVEL NUMBER;
V_N_POSID VARCHAR2(255);
BEGIN
IF P_EMP_ID IS NULL OR P_EMP_NAME IS NULL OR P_HR_DPT_ID IS NULL OR P_M_DPT_ID IS NULL  or p_valid_flag is null THEN
ERRMSG:=0;--THIS FIELDS ARE NOT NULL
INSERT INTO UP_MANAGERUP_LOGTOHR(P_EMP_ID,P_EMP_NAME,P_HR_DPT_ID,P_VALID_FLAG,P_M_DPT_ID,P_EMP_JOB,MFLAG,ERRMSG,MAINT_USER) VALUES(P_EMP_ID,P_EMP_NAME,P_HR_DPT_ID,P_VALID_FLAG,P_M_DPT_ID,P_EMP_JOB,2,ERRMSG,P_USER);
RETURN;
END IF;
IF P_VALID_FLAG>1 OR P_VALID_FLAG<0 THEN
ERRMSG:=1;--THIS VALUE IS INVALID
INSERT INTO UP_MANAGERUP_LOGTOHR(P_EMP_ID,P_EMP_NAME,P_HR_DPT_ID,P_VALID_FLAG,P_M_DPT_ID,P_EMP_JOB,MFLAG,ERRMSG,MAINT_USER) VALUES(P_EMP_ID,P_EMP_NAME,P_HR_DPT_ID,P_VALID_FLAG,P_M_DPT_ID,P_EMP_JOB,2,ERRMSG,P_USER);
RETURN;
END IF;

BEGIN
SELECT TRIM(DPT_NAME) INTO V_HR_DPTNAME FROM RF_DEPT WHERE TRIM(DPT_ID)=TRIM(P_HR_DPT_ID);
EXCEPTION WHEN NO_DATA_FOUND THEN
ERRMSG:=2;--THIS HR_DPT_ID HAVE NO EXSITED
INSERT INTO UP_MANAGERUP_LOGTOHR(P_EMP_ID,P_EMP_NAME,P_HR_DPT_ID,P_VALID_FLAG,P_M_DPT_ID,P_EMP_JOB,MFLAG,ERRMSG,MAINT_USER) VALUES(P_EMP_ID,P_EMP_NAME,P_HR_DPT_ID,P_VALID_FLAG,P_M_DPT_ID,P_EMP_JOB,2,ERRMSG,P_USER);
RETURN;
END;

BEGIN
SELECT TRIM(DPT_NAME) INTO V_M_DPTNAME FROM RF_DEPT WHERE TRIM(DPT_ID)=TRIM(P_M_DPT_ID);
EXCEPTION WHEN NO_DATA_FOUND THEN
ERRMSG:=8;--THIS M_DPT_ID HAVE NO EXSITED
INSERT INTO UP_MANAGERUP_LOGTOHR(P_EMP_ID,P_EMP_NAME,P_HR_DPT_ID,P_VALID_FLAG,P_M_DPT_ID,P_EMP_JOB,MFLAG,ERRMSG,MAINT_USER) VALUES(P_EMP_ID,P_EMP_NAME,P_HR_DPT_ID,P_VALID_FLAG,P_M_DPT_ID,P_EMP_JOB,2,ERRMSG,P_USER);
RETURN;
END;
  BEGIN
    SELECT NVL(TRIM(JOB_ID),'Staffer') INTO V_JOB_ID FROM RF_JOBFUNC WHERE TRIM(JOB_NAME)=TRIM(P_EMP_JOB);
  EXCEPTION WHEN NO_DATA_FOUND THEN
     V_JOB_ID:='Staffer';
  END;
--失效時
begin
IF P_VALID_FLAG=0 THEN
SELECT COUNT(*) INTO V_CR FROM RF_EMPLOYEE WHERE TRIM(EMP_ID)=LOWER(TRIM(P_EMP_ID));
IF V_CR>0 THEN
  SELECT EMP_NTACCNT,DPT_ID INTO V_EMP_NTACCNT,V_OLD_DPTID FROM ARCHIVE.RF_EMPLOYEE WHERE RTRIM(EMP_ID) = LOWER(RTRIM(P_EMP_ID));
  --IF TRIM(V_OLD_DPTID)<>TRIM(P_HR_DPT_ID) THEN
  --ERRMSG:=11;--傳入的部門ID同此人員WF中部門ID不一致
  --INSERT INTO UP_MANAGERUP_LOGTOHR(P_EMP_ID,P_EMP_NAME,P_HR_DPT_ID,P_VALID_FLAG,P_M_DPT_ID,P_EMP_JOB,MFLAG,ERRMSG,MAINT_USER) VALUES(P_EMP_ID,P_EMP_NAME,P_HR_DPT_ID,P_VALID_FLAG,P_M_DPT_ID,P_EMP_JOB,2,ERRMSG,P_USER);
  --RETURN;
  --END IF;
  SELECT COUNT(*) INTO V_COUNT_RECORD FROM WORKFLOW.STEP WHERE STATUS = 1 AND TRIM(CLIENT) = V_EMP_NTACCNT;
  IF V_COUNT_RECORD > 0 THEN
     ERRMSG :=4;-- 'THE EMPLOYEE HAVE UNFINISHED TASK';
  INSERT INTO UP_MANAGERUP_LOGTOHR(P_EMP_ID,P_EMP_NAME,P_HR_DPT_ID,P_VALID_FLAG,P_M_DPT_ID,P_EMP_JOB,MFLAG,ERRMSG,MAINT_USER) VALUES(P_EMP_ID,P_EMP_NAME,P_HR_DPT_ID,P_VALID_FLAG,P_M_DPT_ID,P_EMP_JOB,2,ERRMSG,P_USER);
     RETURN;
  END IF;
   IF P_HR_DPT_ID=P_M_DPT_ID THEN
     SELECT COUNT(*) INTO V_COUNT_RECORD FROM RF_POSITION WHERE TRIM(POS_ID)=TRIM(P_HR_DPT_ID)||'_Staffer';
     IF V_COUNT_RECORD=0 THEN
   ADDPOSITION(P_HR_DPT_ID||'_Staffer',P_HR_DPT_ID,V_HR_DPTNAME||'_一般員工',NULL,ERRMSG);
  IF ERRMSG IS NOT NULL THEN
  ERRMSG:=3;---ADD POSITION FAIL
  INSERT INTO UP_MANAGERUP_LOGTOHR(P_EMP_ID,P_EMP_NAME,P_HR_DPT_ID,P_VALID_FLAG,P_M_DPT_ID,P_EMP_JOB,MFLAG,ERRMSG,MAINT_USER) VALUES(P_EMP_ID,P_EMP_NAME,P_HR_DPT_ID,P_VALID_FLAG,P_M_DPT_ID,P_EMP_JOB,2,ERRMSG,P_USER);
  RETURN;
  END IF;
     END IF;
  UPDATE RF_EMPLOYEE SET POS_ID=TRIM(P_HR_DPT_ID)||'_Staffer' WHERE TRIM(EMP_ID)=LOWER(TRIM(P_EMP_ID));
 ELSE
 DELETE RF_EMP_POS_ADD WHERE TRIM(DPT_ID)=TRIM(P_M_DPT_ID) AND  TRIM(EMP_ID)=LOWER(TRIM(P_EMP_ID));
 END IF;
ELSE
ERRMSG:=5;--NO PERSON!
INSERT INTO UP_MANAGERUP_LOGTOHR(P_EMP_ID,P_EMP_NAME,P_HR_DPT_ID,P_VALID_FLAG,P_M_DPT_ID,P_EMP_JOB,MFLAG,ERRMSG,MAINT_USER) VALUES(P_EMP_ID,P_EMP_NAME,P_HR_DPT_ID,P_VALID_FLAG,P_M_DPT_ID,P_EMP_JOB,2,ERRMSG,P_USER);
RETURN;
END IF;
END IF;
exception when others then
errmsg:=10;--code error!
INSERT INTO UP_MANAGERUP_LOGTOHR(P_EMP_ID,P_EMP_NAME,P_HR_DPT_ID,P_VALID_FLAG,P_M_DPT_ID,P_EMP_JOB,MFLAG,ERRMSG,MAINT_USER) VALUES(P_EMP_ID,P_EMP_NAME,P_HR_DPT_ID,P_VALID_FLAG,P_M_DPT_ID,P_EMP_JOB,2,ERRMSG,P_USER);
dbms_output.put_line('err');
end;
--生效更新時
begin
IF P_VALID_FLAG=1 THEN
SELECT COUNT(*) INTO V_CR  FROM RF_EMPLOYEE WHERE TRIM(EMP_ID)=LOWER(TRIM(P_EMP_ID));
IF V_CR>0 THEN--wf中有此人時
SELECT DPT_ID,POS_ID INTO V_OLD_DPTID,V_OLD_POSID  FROM RF_EMPLOYEE  WHERE  TRIM(EMP_ID)=LOWER(TRIM(P_EMP_ID));

IF TRIM(V_OLD_DPTID)=TRIM(P_HR_DPT_ID) THEN--判斷wf同hr關於此人是不是同一部門的
  BEGIN
   SELECT EMP_ID INTO V_OLD_EMPID FROM RF_EMP_POS_ADD WHERE TRIM(DPT_ID)=TRIM(P_M_DPT_ID) AND TRIM(POS_ID)=TRIM(P_M_DPT_ID);
 IF TRIM(V_OLD_EMPID)=TRIM(P_EMP_ID) THEN
 ERRMSG:=7;--此人已是此部門主管
 INSERT INTO UP_MANAGERUP_LOGTOHR(P_EMP_ID,P_EMP_NAME,P_HR_DPT_ID,P_VALID_FLAG,P_M_DPT_ID,P_EMP_JOB,MFLAG,ERRMSG,MAINT_USER) VALUES(P_EMP_ID,P_EMP_NAME,P_HR_DPT_ID,P_VALID_FLAG,P_M_DPT_ID,P_EMP_JOB,2,ERRMSG,P_USER);
 RETURN;
 END IF;
    DELETE RF_EMP_POS_ADD WHERE TRIM(DPT_ID)=TRIM(P_M_DPT_ID) AND TRIM(POS_ID)=TRIM(P_M_DPT_ID);
  EXCEPTION WHEN NO_DATA_FOUND THEN
 BEGIN
    SELECT EMP_ID,DPT_ID, EMP_CNAME,JOB_ID INTO V_OLD_EMPID,V_OLD_DPTID,V_OLD_EMPNAME,V_OLD_JOBID  FROM RF_EMPLOYEE WHERE TRIM(DPT_ID)=TRIM(P_M_DPT_ID) AND TRIM(POS_ID)=TRIM(P_M_DPT_ID);
 IF TRIM(V_OLD_EMPID)=TRIM(P_EMP_ID) THEN
 ERRMSG:=7;--此人已是此部門主管
 INSERT INTO UP_MANAGERUP_LOGTOHR(P_EMP_ID,P_EMP_NAME,P_HR_DPT_ID,P_VALID_FLAG,P_M_DPT_ID,P_EMP_JOB,MFLAG,ERRMSG,MAINT_USER) VALUES(P_EMP_ID,P_EMP_NAME,P_HR_DPT_ID,P_VALID_FLAG,P_M_DPT_ID,P_EMP_JOB,2,ERRMSG,P_USER);
 RETURN;
 END IF;
 SELECT TRIM(JOB_NAME) INTO V_OLD_JOBNAME from RF_JOBFUNC WHERE TRIM(JOB_ID)=TRIM(V_OLD_JOBID);
 HRM_UPDATE_MANAGER(V_OLD_EMPID,V_OLD_EMPNAME,V_OLD_DPTID,0,V_OLD_DPTID,V_OLD_JOBNAME,P_USER,ERRMSG);
 IF ERRMSG IS NOT NULL THEN
 ERRMSG:=9;--原部門主管更新失敗
 INSERT INTO UP_MANAGERUP_LOGTOHR(P_EMP_ID,P_EMP_NAME,P_HR_DPT_ID,P_VALID_FLAG,P_M_DPT_ID,P_EMP_JOB,MFLAG,ERRMSG,MAINT_USER) VALUES(P_EMP_ID,P_EMP_NAME,P_HR_DPT_ID,P_VALID_FLAG,P_M_DPT_ID,P_EMP_JOB,2,ERRMSG,P_USER);
 RETURN;
 END IF;
 EXCEPTION WHEN NO_DATA_FOUND THEN
 ERRMSG:=NULL;
 END;
  END;
   SELECT DPT_LEVEL INTO V_DPT_LEVEL FROM RF_DEPT WHERE TRIM(DPT_ID)=TRIM(P_M_DPT_ID);
 IF TRIM(P_M_DPT_ID)=TRIM(P_HR_DPT_ID) THEN
 UPDATE RF_EMPLOYEE SET POS_ID=TRIM(P_M_DPT_ID),JOB_ID=TRIM(V_JOB_ID),OC_GRADE=TRIM(V_DPT_LEVEL) WHERE TRIM(EMP_ID)=TRIM(P_EMP_ID);
 ELSE
 UPDATE RF_EMPLOYEE SET JOB_ID=TRIM(V_JOB_ID) WHERE TRIM(EMP_ID)=TRIM(P_EMP_ID);
 INSERT INTO RF_EMP_POS_ADD(EMP_ID,POS_ID,DPT_ID,OC_GRADE) VALUES(TRIM(P_EMP_ID),TRIM(P_M_DPT_ID),TRIM(P_M_DPT_ID),V_DPT_LEVEL);
 END IF;
ELSE--不是同一部門時

  BEGIN
  SELECT EMP_ID,DPT_ID, EMP_CNAME,JOB_ID INTO V_OLD_EMPID,V_OLD_DPTID,V_OLD_EMPNAME,V_OLD_JOBID FROM RF_EMPLOYEE WHERE TRIM(DPT_ID)=TRIM(V_OLD_DPTID) AND TRIM(POS_ID)=TRIM(V_OLD_DPTID) AND TRIM(EMP_ID)=TRIM(P_EMP_ID);
  SELECT DPT_LEVEL INTO V_OLD_DPTLEVEL FROM RF_DEPT WHERE TRIM(DPT_ID)=TRIM(V_OLD_DPTID);
  SELECT EMP_NTACCNT INTO V_EMP_NTACCNT FROM ARCHIVE.RF_EMPLOYEE WHERE RTRIM(EMP_ID) = LOWER(RTRIM(P_EMP_ID));
  SELECT COUNT(*) INTO V_COUNT_RECORD FROM WORKFLOW.STEP WHERE STATUS = 1 AND TRIM(CLIENT) = V_EMP_NTACCNT;
  IF V_COUNT_RECORD > 0 THEN
     ERRMSG :=4;-- 'THE EMPLOYEE HAVE UNFINISHED TASK';
  INSERT INTO UP_MANAGERUP_LOGTOHR(P_EMP_ID,P_EMP_NAME,P_HR_DPT_ID,P_VALID_FLAG,P_M_DPT_ID,P_EMP_JOB,MFLAG,ERRMSG,MAINT_USER) VALUES(P_EMP_ID,P_EMP_NAME,P_HR_DPT_ID,P_VALID_FLAG,P_M_DPT_ID,P_EMP_JOB,2,ERRMSG,P_USER);
     RETURN;
  END IF;
  SELECT DPT_LEVEL INTO V_DPT_LEVEL FROM RF_DEPT WHERE TRIM(DPT_ID)=TRIM(P_M_DPT_ID);
  IF TRIM(P_HR_DPT_ID)=TRIM(P_M_DPT_ID) THEN
    DELETE RF_EMP_POS_ADD WHERE TRIM(DPT_ID)=TRIM(P_M_DPT_ID) AND TRIM(POS_ID)=TRIM(P_M_DPT_ID);
  UPDATE RF_EMPLOYEE SET DPT_ID=TRIM(P_HR_DPT_ID),POS_ID=TRIM(P_M_DPT_ID),JOB_ID=TRIM(V_JOB_ID),OC_GRADE=TRIM(V_DPT_LEVEL) WHERE TRIM(EMP_ID)=TRIM(P_EMP_ID);
  INSERT INTO RF_EMP_POS_ADD(EMP_ID,POS_ID,DPT_ID,OC_GRADE) VALUES(TRIM(P_EMP_ID),TRIM(V_OLD_DPTID),TRIM(V_OLD_DPTID),V_OLD_DPTLEVEL);
  ELSE
    BEGIN
   SELECT EMP_ID INTO V_OLD_EMPID FROM RF_EMP_POS_ADD WHERE TRIM(DPT_ID)=TRIM(P_HR_DPT_ID) AND TRIM(POS_ID)=TRIM(P_HR_DPT_ID);
  EXCEPTION WHEN NO_DATA_FOUND THEN  
 V_OLD_EMPID:=NULL;
  END; 
  IF TRIM(V_OLD_EMPID)=TRIM(P_EMP_ID) THEN
 DELETE RF_EMP_POS_ADD WHERE TRIM(DPT_ID)=TRIM(P_HR_DPT_ID) AND TRIM(POS_ID)=TRIM(P_HR_DPT_ID);
 V_N_POSID:=TRIM(P_HR_DPT_ID);
 SELECT DPT_LEVEL INTO V_DPT_LEVEL FROM RF_DEPT WHERE TRIM(DPT_ID)=TRIM(P_HR_DPT_ID);
 ELSE
 V_N_POSID:=TRIM(P_HR_DPT_ID)||'_Staffer';
 V_DPT_LEVEL:=10;
   SELECT COUNT(*) INTO V_COUNT_RECORD FROM RF_POSITION WHERE TRIM(POS_ID)=TRIM(P_HR_DPT_ID)||'_Staffer';
    IF V_COUNT_RECORD=0 THEN
 ADDPOSITION(P_HR_DPT_ID||'_Staffer',P_HR_DPT_ID,V_HR_DPTNAME||'_一般員工',NULL,ERRMSG);
 IF ERRMSG IS NOT NULL THEN
 ERRMSG:=3;---ADD POSITION FAIL
 INSERT INTO UP_MANAGERUP_LOGTOHR(P_EMP_ID,P_EMP_NAME,P_HR_DPT_ID,P_VALID_FLAG,P_M_DPT_ID,P_EMP_JOB,MFLAG,ERRMSG,MAINT_USER) VALUES(P_EMP_ID,P_EMP_NAME,P_HR_DPT_ID,P_VALID_FLAG,P_M_DPT_ID,P_EMP_JOB,2,ERRMSG,P_USER);
 RETURN;
 END IF;
 END IF;
 END IF;
   DELETE RF_EMP_POS_ADD WHERE TRIM(DPT_ID)=TRIM(P_M_DPT_ID) AND TRIM(POS_ID)=TRIM(P_M_DPT_ID);
 UPDATE RF_EMPLOYEE SET DPT_ID=TRIM(P_HR_DPT_ID),POS_ID=V_N_POSID,JOB_ID=TRIM(V_JOB_ID),OC_GRADE=V_DPT_LEVEL WHERE TRIM(EMP_ID)=TRIM(P_EMP_ID);
    INSERT INTO RF_EMP_POS_ADD(EMP_ID,POS_ID,DPT_ID,OC_GRADE) VALUES(TRIM(P_EMP_ID),TRIM(P_M_DPT_ID),TRIM(P_M_DPT_ID),V_DPT_LEVEL);
  END IF;
  EXCEPTION WHEN NO_DATA_FOUND THEN
   SELECT DPT_LEVEL INTO V_DPT_LEVEL FROM RF_DEPT WHERE TRIM(DPT_ID)=TRIM(P_M_DPT_ID);
   IF TRIM(P_HR_DPT_ID)=TRIM(P_M_DPT_ID) THEN
     DELETE RF_EMP_POS_ADD WHERE TRIM(DPT_ID)=TRIM(P_M_DPT_ID) AND TRIM(POS_ID)=TRIM(P_M_DPT_ID);
   UPDATE RF_EMPLOYEE SET DPT_ID=TRIM(P_HR_DPT_ID),POS_ID=TRIM(P_M_DPT_ID),JOB_ID=TRIM(V_JOB_ID),OC_GRADE=TRIM(V_DPT_LEVEL) WHERE TRIM(EMP_ID)=TRIM(P_EMP_ID);
  
   ELSE
 BEGIN
   SELECT EMP_ID INTO V_OLD_EMPID FROM RF_EMP_POS_ADD WHERE TRIM(DPT_ID)=TRIM(P_HR_DPT_ID) AND TRIM(POS_ID)=TRIM(P_HR_DPT_ID);
  EXCEPTION WHEN NO_DATA_FOUND THEN  
 V_OLD_EMPID:=NULL;
  END; 
  IF TRIM(V_OLD_EMPID)=TRIM(P_EMP_ID) THEN
 DELETE RF_EMP_POS_ADD WHERE TRIM(DPT_ID)=TRIM(P_HR_DPT_ID) AND TRIM(POS_ID)=TRIM(P_HR_DPT_ID);
 V_N_POSID:=TRIM(P_HR_DPT_ID);
 SELECT DPT_LEVEL INTO V_DPT_LEVEL FROM RF_DEPT WHERE TRIM(DPT_ID)=TRIM(P_HR_DPT_ID);
 ELSE
 V_N_POSID:=TRIM(P_HR_DPT_ID)||'_Staffer';
 V_DPT_LEVEL:=10;
   SELECT COUNT(*) INTO V_COUNT_RECORD FROM RF_POSITION WHERE TRIM(POS_ID)=TRIM(P_HR_DPT_ID)||'_Staffer';
    IF V_COUNT_RECORD=0 THEN
 ADDPOSITION(P_HR_DPT_ID||'_Staffer',P_HR_DPT_ID,V_HR_DPTNAME||'_一般員工',NULL,ERRMSG);
 IF ERRMSG IS NOT NULL THEN
 ERRMSG:=3;---ADD POSITION FAIL
 INSERT INTO UP_MANAGERUP_LOGTOHR(P_EMP_ID,P_EMP_NAME,P_HR_DPT_ID,P_VALID_FLAG,P_M_DPT_ID,P_EMP_JOB,MFLAG,ERRMSG,MAINT_USER) VALUES(P_EMP_ID,P_EMP_NAME,P_HR_DPT_ID,P_VALID_FLAG,P_M_DPT_ID,P_EMP_JOB,2,ERRMSG,P_USER);
 RETURN;
 END IF;
 END IF;
 END IF;
   DELETE RF_EMP_POS_ADD WHERE TRIM(DPT_ID)=TRIM(P_M_DPT_ID) AND TRIM(POS_ID)=TRIM(P_M_DPT_ID);
 UPDATE RF_EMPLOYEE SET DPT_ID=TRIM(P_HR_DPT_ID),POS_ID=V_N_POSID,JOB_ID=TRIM(V_JOB_ID),OC_GRADE=V_DPT_LEVEL WHERE TRIM(EMP_ID)=TRIM(P_EMP_ID);
    INSERT INTO RF_EMP_POS_ADD(EMP_ID,POS_ID,DPT_ID,OC_GRADE) VALUES(TRIM(P_EMP_ID),TRIM(P_M_DPT_ID),TRIM(P_M_DPT_ID),V_DPT_LEVEL);
   END IF;  
  END;
END IF;

ELSE--wf中無有此人時
 --清除原有主管即改變其職位為一般員工
 SELECT COUNT(*) INTO V_COUNT_RECORD FROM RF_EMPLOYEE WHERE TRIM(POS_ID)=TRIM(P_M_DPT_ID) AND TRIM(DPT_ID)=TRIM(P_M_DPT_ID);
 IF V_COUNT_RECORD>0 THEN
    SELECT EMP_ID,DPT_ID, EMP_CNAME,JOB_ID INTO V_OLD_EMPID,V_OLD_DPTID,V_OLD_EMPNAME,V_OLD_JOBID  FROM RF_EMPLOYEE WHERE TRIM(POS_ID)=TRIM(P_M_DPT_ID) AND TRIM(DPT_ID)=TRIM(P_M_DPT_ID);
    SELECT TRIM(JOB_NAME) INTO V_OLD_JOBNAME from RF_JOBFUNC WHERE TRIM(JOB_ID)=TRIM(V_OLD_JOBID);
     HRM_UPDATE_MANAGER(V_OLD_EMPID,V_OLD_EMPNAME,V_OLD_DPTID,0,V_OLD_DPTID,V_OLD_JOBNAME,P_USER,ERRMSG);
     IF ERRMSG IS NOT NULL THEN
     ERRMSG:=9;--原部門主管更新失敗
     INSERT INTO UP_MANAGERUP_LOGTOHR(P_EMP_ID,P_EMP_NAME,P_HR_DPT_ID,P_VALID_FLAG,P_M_DPT_ID,P_EMP_JOB,MFLAG,ERRMSG,MAINT_USER) VALUES(P_EMP_ID,P_EMP_NAME,P_HR_DPT_ID,P_VALID_FLAG,P_M_DPT_ID,P_EMP_JOB,2,ERRMSG,P_USER);
     RETURN;
     END IF; 
  ELSE
    SELECT COUNT(*) INTO V_COUNT_RECORD FROM RF_EMP_POS_ADD WHERE  TRIM(POS_ID)=TRIM(P_M_DPT_ID) AND TRIM(DPT_ID)=TRIM(P_M_DPT_ID);
    DELETE RF_EMP_POS_ADD WHERE TRIM(DPT_ID)=TRIM(P_M_DPT_ID) AND  TRIM(POS_ID)=TRIM(P_M_DPT_ID);
  END IF;
 
  ----添加部門主管
  IF P_HR_DPT_ID=P_M_DPT_ID THEN
  SELECT DPT_LEVEL INTO V_DPT_LEVEL FROM RF_DEPT WHERE TRIM(DPT_ID)=TRIM(P_HR_DPT_ID);
  ADDEMPLOYEE(P_EMP_ID,P_HR_DPT_ID,V_JOB_ID,P_HR_DPT_ID,P_EMP_NAME,P_EMP_NAME,P_EMP_ID,NULL,1,V_DPT_LEVEL,NULL,ERRMSG);    
     IF ERRMSG IS NOT NULL THEN
     ERRMSG:=6;--添加部門主管失敗
     INSERT INTO UP_MANAGERUP_LOGTOHR(P_EMP_ID,P_EMP_NAME,P_HR_DPT_ID,P_VALID_FLAG,P_M_DPT_ID,P_EMP_JOB,MFLAG,ERRMSG,MAINT_USER) VALUES(P_EMP_ID,P_EMP_NAME,P_HR_DPT_ID,P_VALID_FLAG,P_M_DPT_ID,P_EMP_JOB,2,ERRMSG,P_USER);
     RETURN;
     END IF;
  ELSE
   SELECT DPT_LEVEL,DPT_NAME INTO V_DPT_LEVEL,V_HR_DPTNAME FROM RF_DEPT WHERE TRIM(DPT_ID)=TRIM(P_HR_DPT_ID);
   SELECT COUNT(*) INTO V_COUNT_RECORD FROM RF_POSITION WHERE TRIM(POS_ID)=TRIM(P_HR_DPT_ID)||'_Staffer';
       IF V_COUNT_RECORD=0 THEN
    ADDPOSITION(P_HR_DPT_ID||'_Staffer',P_HR_DPT_ID,V_HR_DPTNAME||'_一般員工',NULL,ERRMSG);
   IF ERRMSG IS NOT NULL THEN
   ERRMSG:=3;---ADD POSITION FAIL
   INSERT INTO UP_MANAGERUP_LOGTOHR(P_EMP_ID,P_EMP_NAME,P_HR_DPT_ID,P_VALID_FLAG,P_M_DPT_ID,P_EMP_JOB,MFLAG,ERRMSG,MAINT_USER) VALUES(P_EMP_ID,P_EMP_NAME,P_HR_DPT_ID,P_VALID_FLAG,P_M_DPT_ID,P_EMP_JOB,2,ERRMSG,P_USER);
   RETURN;
   END IF;
   END IF;
   ADDEMPLOYEE(P_EMP_ID,P_HR_DPT_ID,V_JOB_ID,P_HR_DPT_ID||'_Staffer',P_EMP_NAME,P_EMP_NAME,P_EMP_ID,NULL,1,V_DPT_LEVEL,NULL,ERRMSG);    
     IF ERRMSG IS NOT NULL THEN
     ERRMSG:=6;--添加部門主管失敗
     INSERT INTO UP_MANAGERUP_LOGTOHR(P_EMP_ID,P_EMP_NAME,P_HR_DPT_ID,P_VALID_FLAG,P_M_DPT_ID,P_EMP_JOB,MFLAG,ERRMSG,MAINT_USER) VALUES(P_EMP_ID,P_EMP_NAME,P_HR_DPT_ID,P_VALID_FLAG,P_M_DPT_ID,P_EMP_JOB,2,ERRMSG,P_USER);
     RETURN;
     END IF;
   SELECT DPT_LEVEL INTO V_DPT_LEVEL  FROM RF_DEPT WHERE TRIM(DPT_ID)=TRIM(P_M_DPT_ID);
   INSERT INTO RF_EMP_POS_ADD(EMP_ID,POS_ID,DPT_ID,OC_GRADE) VALUES(P_EMP_ID,P_M_DPT_ID,P_M_DPT_ID,10);
  END IF;
END IF;
END IF;
exception when others then
errmsg:=10;--code error!
INSERT INTO UP_MANAGERUP_LOGTOHR(P_EMP_ID,P_EMP_NAME,P_HR_DPT_ID,P_VALID_FLAG,P_M_DPT_ID,P_EMP_JOB,MFLAG,ERRMSG,MAINT_USER) VALUES(P_EMP_ID,P_EMP_NAME,P_HR_DPT_ID,P_VALID_FLAG,P_M_DPT_ID,P_EMP_JOB,2,ERRMSG,P_USER);
dbms_output.put_line('err1');
end;
INSERT INTO UP_MANAGERUP_LOGTOHR(P_EMP_ID,P_EMP_NAME,P_HR_DPT_ID,P_VALID_FLAG,P_M_DPT_ID,P_EMP_JOB,MFLAG,ERRMSG,MAINT_USER) VALUES(P_EMP_ID,P_EMP_NAME,P_HR_DPT_ID,P_VALID_FLAG,P_M_DPT_ID,P_EMP_JOB,2,ERRMSG,P_USER);
END HRM_UPDATE_MANAGER;
end;
/

来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/13538095/viewspace-211693/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： 組織同步的解決方案Ultimus之部門同步
下一篇： 組織同步的解決方案Ultimus之人員同步
请登录后发表评论 登录
全部评论
yxw2003110

注册时间：2008-03-19

  • 博文量
    12
  • 访问量
    6878

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593