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

最新推荐文章于 2022-07-18 14:24:02 发布

csz0100

最新推荐文章于 2022-07-18 14:24:02 发布

阅读量88

点赞数

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;
/