組織同步的解決方案Ultimus之人員同步

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

csz0100

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

阅读量91

点赞数

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

procedure ADDEMPLOYEE_FORHR(P_EMP_ID in VARCHAR2,P_DPT_ID in VARCHAR2,P_JOB_ID in VARCHAR2,P_EMP_CNAME in VARCHAR2,
P_EMP_ENAME in VARCHAR2,P_COM_POSITION in VARCHAR2,P_EMP_EMAIL in VARCHAR2,P_EMP_REPLACE in VARCHAR2,P_EMP_HIRE_DATE in VARCHAR2,P_USER VARCHAR2,ERRMSG out VARCHAR2)
is
V_COUNT_RECORD NUMBER;
P_EMP_ENABLE NUMBER;
P_EMP_OC_GRADE NUMBER;
P_EMP_NTACCNT VARCHAR2(255);
P_POS_ID    VARCHAR2(255);
V_DPT_NAME   VARCHAR2(255);
V_POS_NAME   VARCHAR2(255);
V_JOB_ID   VARCHAR2(255);
V_COM_POSITION_CODE VARCHAR2(255);
V_COM_POSITION VARCHAR2(255);
V_EMP_ID VARCHAR2(255);
V_JOB_count number;
v_enable number;
begin
P_EMP_ENABLE:=1;
P_EMP_OC_GRADE:=10;
V_EMP_ID:=lower(P_EMP_ID);
P_EMP_NTACCNT:=V_EMP_ID;
P_POS_ID:=trim(P_DPT_ID)||'_Staffer';

begin
SELECT TRIM(HJA_STATUS),TRIM(HJA_NAME) INTO V_COM_POSITION_CODE,V_COM_POSITION FROM HRM_JOBELEMENTS_ZW WHERE TRIM(HJA_CODE)=TRIM(P_COM_POSITION);
exception when others then
V_COM_POSITION_CODE:=null;
V_COM_POSITION:=null;
--dbms_output.put_line('ddddd');
end;
begin
--2.check emp_id
SELECT COUNT(*) INTO V_COUNT_RECORD FROM ARCHIVE.RF_EMPLOYEE WHERE RTRIM(EMP_ID) = RTRIM(V_EMP_ID);
IF V_COUNT_RECORD != 0 THEN
select emp_enabled into v_enable from rf_employee where RTRIM(EMP_ID) = RTRIM(V_EMP_ID);
if v_enable=0 then
update rf_employee set emp_enabled=1 where RTRIM(EMP_ID) = RTRIM(V_EMP_ID);
INSERT INTO UP_EMPLOYEE_LOGTOHR (EMP_ID,DPT_ID,JOB_ID,EMP_CNAME,EMP_ENAME,EMP_MAIL_ADDR,DEPUTY_EMP_NO,COM_POSITION,EMP_HIRE_DATE,MFLAG,ERRFLAG,CREATE_USER,CREATE_DATE,MAINT_USER,MAINT_DATE)
VALUES(P_EMP_ID, P_DPT_ID, P_JOB_ID,P_EMP_CNAME, P_EMP_ENAME, P_EMP_EMAIL,P_EMP_REPLACE,P_COM_POSITION,TO_DATE(P_EMP_HIRE_DATE,'yyyy/mm/dd'),3,ERRMSG,P_USER,trunc(SYSDATE),P_USER,trunc(SYSDATE));
RETURN;
end if;
       ERRMSG :=1;-- 'THE EMP_ID HAVE ALREADY EXISTED,ADD FAULT';
    INSERT INTO UP_EMPLOYEE_LOGTOHR (EMP_ID,DPT_ID,JOB_ID,EMP_CNAME,EMP_ENAME,EMP_MAIL_ADDR,DEPUTY_EMP_NO,COM_POSITION,EMP_HIRE_DATE,MFLAG,ERRFLAG,CREATE_USER,CREATE_DATE,MAINT_USER,MAINT_DATE)
VALUES(P_EMP_ID, P_DPT_ID, P_JOB_ID,P_EMP_CNAME, P_EMP_ENAME, P_EMP_EMAIL,P_EMP_REPLACE,P_COM_POSITION,TO_DATE(P_EMP_HIRE_DATE,'yyyy/mm/dd'),3,ERRMSG,P_USER,trunc(SYSDATE),P_USER,trunc(SYSDATE));
     RETURN;
END IF;
--3.check emp_ntaccnt
SELECT COUNT(*) INTO V_COUNT_RECORD FROM ARCHIVE.RF_EMPLOYEE WHERE RTRIM(EMP_NTACCNT) = RTRIM(P_EMP_NTACCNT);
IF V_COUNT_RECORD != 0 THEN
       ERRMSG := 2;--'THE EMP_NTACCNT HAVE ALREADY EXISTED,ADD FAULT';
    INSERT INTO UP_EMPLOYEE_LOGTOHR (EMP_ID,DPT_ID,JOB_ID,EMP_CNAME,EMP_ENAME,EMP_MAIL_ADDR,DEPUTY_EMP_NO,COM_POSITION,EMP_HIRE_DATE,MFLAG,ERRFLAG,CREATE_USER,CREATE_DATE,MAINT_USER,MAINT_DATE)
VALUES(P_EMP_ID, P_DPT_ID, P_JOB_ID,P_EMP_CNAME, P_EMP_ENAME, P_EMP_EMAIL,P_EMP_REPLACE,P_COM_POSITION,TO_DATE(P_EMP_HIRE_DATE,'yyyy/mm/dd'),3,ERRMSG,P_USER,trunc(SYSDATE),P_USER,trunc(SYSDATE));
     RETURN;
END IF;
--4.check dept_id
SELECT COUNT(*) INTO V_COUNT_RECORD FROM ARCHIVE.RF_DEPT WHERE RTRIM(DPT_ID) = RTRIM(P_DPT_ID);
IF V_COUNT_RECORD != 1 THEN
       ERRMSG := 3;--'THE DEPT_ID HAVE NOT EXISTED,ADD FAULT';
    INSERT INTO UP_EMPLOYEE_LOGTOHR (EMP_ID,DPT_ID,JOB_ID,EMP_CNAME,EMP_ENAME,EMP_MAIL_ADDR,DEPUTY_EMP_NO,COM_POSITION,EMP_HIRE_DATE,MFLAG,ERRFLAG,CREATE_USER,CREATE_DATE,MAINT_USER,MAINT_DATE)
VALUES(P_EMP_ID, P_DPT_ID, P_JOB_ID,P_EMP_CNAME, P_EMP_ENAME, P_EMP_EMAIL,P_EMP_REPLACE,P_COM_POSITION,TO_DATE(P_EMP_HIRE_DATE,'yyyy/mm/dd'),3,ERRMSG,P_USER,trunc(SYSDATE),P_USER,trunc(SYSDATE));
     RETURN;
END IF;
--5.check job_id
IF P_EMP_OC_GRADE != 10 AND P_EMP_OC_GRADE != 20 AND P_EMP_OC_GRADE != 30 AND P_EMP_OC_GRADE != 40 AND P_EMP_OC_GRADE != 50 AND P_EMP_OC_GRADE != 60 AND P_EMP_OC_GRADE != 70 THEN
     ERRMSG := 5;--'THE EMP_OC_GRADE NOT VALAD,ADD FAULT';
INSERT INTO UP_EMPLOYEE_LOGTOHR (EMP_ID,DPT_ID,JOB_ID,EMP_CNAME,EMP_ENAME,EMP_MAIL_ADDR,DEPUTY_EMP_NO,COM_POSITION,EMP_HIRE_DATE,MFLAG,ERRFLAG,CREATE_USER,CREATE_DATE,MAINT_USER,MAINT_DATE)
VALUES(P_EMP_ID, P_DPT_ID, P_JOB_ID,P_EMP_CNAME, P_EMP_ENAME, P_EMP_EMAIL,P_EMP_REPLACE,P_COM_POSITION,TO_DATE(P_EMP_HIRE_DATE,'yyyy/mm/dd'),3,ERRMSG,P_USER,trunc(SYSDATE),P_USER,trunc(SYSDATE));
     RETURN;
END IF;
--6.check pos_id
SELECT COUNT(*) INTO V_COUNT_RECORD FROM ARCHIVE.RF_POSITION WHERE RTRIM(POS_ID) = RTRIM(P_POS_ID);
IF V_COUNT_RECORD != 1 THEN
SELECT TRIM(DPT_NAME) INTO V_DPT_NAME FROM RF_DEPT WHERE TRIM(DPT_ID)=trim(P_DPT_ID);
SELECT TRIM(JOB_NAME) INTO V_POS_NAME FROM RF_JOBFUNC WHERE TRIM(JOB_ID)=TRIM('Staffer');
addposition(RTRIM(P_POS_ID),trim(P_DPT_ID),RTRIM(V_DPT_NAME)||'_'||RTRIM(V_POS_NAME),null,ERRMSG);
   IF ERRMSG IS NOT NULL THEN
       ERRMSG := 4;--'THE POS_ID ADD FAULT';
    INSERT INTO UP_EMPLOYEE_LOGTOHR (EMP_ID,DPT_ID,JOB_ID,EMP_CNAME,EMP_ENAME,EMP_MAIL_ADDR,DEPUTY_EMP_NO,COM_POSITION,EMP_HIRE_DATE,MFLAG,ERRFLAG,CREATE_USER,CREATE_DATE,MAINT_USER,MAINT_DATE)
VALUES(P_EMP_ID, P_DPT_ID, P_JOB_ID,P_EMP_CNAME, P_EMP_ENAME, P_EMP_EMAIL,P_EMP_REPLACE,P_COM_POSITION,TO_DATE(P_EMP_HIRE_DATE,'yyyy/mm/dd'),3,ERRMSG,P_USER,trunc(SYSDATE),P_USER,trunc(SYSDATE));
     RETURN;
END IF;
END IF;

    --2 GET JOB_ID CODE
SELECT count(JOB_ID) INTO V_JOB_count FROM RF_JOBFUNC WHERE TRIM(JOB_NAME)=TRIM(P_JOB_ID);
if v_job_count=0 then
V_JOB_ID:='Staffer';
else
SELECT TRIM(JOB_ID) INTO V_JOB_ID FROM RF_JOBFUNC WHERE TRIM(JOB_NAME)=TRIM(P_JOB_ID);
IF V_JOB_ID IS NULL THEN
V_JOB_ID:='Staffer';
end if;
END IF;

--7.check position valad
--SELECT COUNT(*) INTO V_COUNT_RECORD FROM ARCHIVE.RF_POSITION WHERE RTRIM(POS_PPOS_ID) = RTRIM(P_POS_ID);
--IF V_COUNT_RECORD != 0 THEN
    -- SELECT COUNT(*) INTO V_COUNT_RECORD FROM ARCHIVE.RF_EMPLOYEE WHERE RTRIM(POS_ID) = RTRIM(P_POS_ID);
     --IF V_COUNT_RECORD != 0 THEN
       -- ERRMSG := 'POSITION HAVE ASSGIN TO PERSON';
       -- RETURN;
     --END IF;
--END IF;
--insert record(NO emp_email field)
INSERT INTO ARCHIVE.RF_EMPLOYEE EMP (EMP.EMP_ID,EMP.DPT_ID,EMP.JOB_ID,EMP.POS_ID,EMP.EMP_CNAME,EMP.EMP_ENAME,EMP.EMP_NTACCNT,EMP.EMP_ENABLED,EMP.EMP_MAIL_ADDR,EMP.DEPUTY_EMP_NO,EMP.OC_GRADE,COM_POSITION_CODE,COM_POSITION,EMP_HIRE_DATE)
VALUES(V_EMP_ID,P_DPT_ID,V_JOB_ID,P_POS_ID,P_EMP_CNAME,P_EMP_ENAME,P_EMP_NTACCNT,P_EMP_ENABLE,P_EMP_EMAIL,P_EMP_REPLACE,P_EMP_OC_GRADE,V_COM_POSITION_CODE,V_COM_POSITION,TO_DATE(P_EMP_HIRE_DATE,'yyyy/mm/dd'));

--delete rf_gp_emp where gp_id='IDPBG_GUP' and emp_id=v_emp_id;
--insert into RF_GP_EMP (GP_ID,EMP_ID,MEMBER_TYPE,TYPE,JOB_ID,EMP_NTACCNT) values('IDPBG_GUP',v_emp_id,4,3,p_pos_id,v_emp_id);

INSERT INTO UP_EMPLOYEE_LOGTOHR (EMP_ID,DPT_ID,JOB_ID,EMP_CNAME,EMP_ENAME,EMP_MAIL_ADDR,DEPUTY_EMP_NO,COM_POSITION,EMP_HIRE_DATE,MFLAG,ERRFLAG,CREATE_USER,CREATE_DATE,MAINT_USER,MAINT_DATE)
VALUES(P_EMP_ID, P_DPT_ID, P_JOB_ID,P_EMP_CNAME, P_EMP_ENAME, P_EMP_EMAIL,P_EMP_REPLACE,P_COM_POSITION,TO_DATE(P_EMP_HIRE_DATE,'yyyy/mm/dd'),3,ERRMSG,P_USER,trunc(SYSDATE),P_USER,trunc(SYSDATE));
ERRMSG := '';
exception when others then
errmsg:=10;
    INSERT INTO UP_EMPLOYEE_LOGTOHR (EMP_ID,DPT_ID,JOB_ID,EMP_CNAME,EMP_ENAME,EMP_MAIL_ADDR,DEPUTY_EMP_NO,COM_POSITION,EMP_HIRE_DATE,MFLAG,ERRFLAG,CREATE_USER,CREATE_DATE,MAINT_USER,MAINT_DATE)
VALUES(P_EMP_ID, P_DPT_ID, P_JOB_ID,P_EMP_CNAME, P_EMP_ENAME, P_EMP_EMAIL,P_EMP_REPLACE,P_COM_POSITION,TO_DATE(P_EMP_HIRE_DATE,'yyyy/mm/dd'),3,ERRMSG,P_USER,trunc(SYSDATE),P_USER,trunc(SYSDATE));
end;
end ADDEMPLOYEE_FORHR;

PROCEDURE EMP_MODINF_TOHR(P_EMP_NO IN VARCHAR2,P_DPT_ID in VARCHAR2,P_JOB_ID in VARCHAR2,P_EMP_CNAME in VARCHAR2,
P_EMP_ENAME in VARCHAR2,P_COM_POSITION in VARCHAR2,P_EMP_EMAIL in VARCHAR2,P_EMP_REPLACE in VARCHAR2,P_EMP_HIRE_DATE in VARCHAR2,
P_UP_USER IN VARCHAR2,P_ERRMSG OUT VARCHAR2)
IS
V_COUNT NUMBER;
V_DPT_COUNT NUMBER;
V_COUNT_RECORD NUMBER;
V_TYPE NUMBER;
V_DPT_ID RF_EMPLOYEE.DPT_ID%TYPE;
V_OLD_DPT_ID RF_EMPLOYEE.DPT_ID%TYPE;
V_JOB_ID VARCHAR2(255);
V_COM_POSITION_CODE VARCHAR2(255);
V_COM_POSITION VARCHAR2(255);
V_POS_ID VARCHAR2(255);
V_OLD_POS_ID VARCHAR2(255);
V_POS_NAME VARCHAR2(255);
V_DPT_NAME VARCHAR2(255);
V_OLD_DPT_NAME VARCHAR2(255);
V_ERR_FLAG NUMBER;
v_m_dptid varchar2(255);
v_job_count number;
BEGIN
V_ERR_FLAG:=1;
begin
SELECT COUNT(EMP_ID) INTO V_COUNT FROM RF_EMPLOYEE WHERE TRIM(EMP_ID)=TRIM(P_EMP_NO);
IF V_COUNT=0 THEN
P_ERRMSG:=1;--THIS EMP_ID NO EXIST!
INSERT INTO UP_EMPLOYEE_LOGTOHR (
EMP_ID, DPT_ID, JOB_ID,
EMP_CNAME,EMP_ENAME,EMP_MAIL_ADDR,
DEPUTY_EMP_NO,COM_POSITION,EMP_HIRE_DATE,
MFLAG,ERRFLAG,MAINT_USER)
VALUES(
P_EMP_NO,P_DPT_ID,P_JOB_ID,
P_EMP_CNAME,P_EMP_ENAME,P_EMP_EMAIL,
P_EMP_REPLACE,P_COM_POSITION,TO_DATE(P_EMP_HIRE_DATE,'yyyy/mm/dd'),
2,P_ERRMSG,P_UP_USER);
RETURN;
END IF;

--check is manager
begin
select rf.dpt_id into v_m_dptid from rf_employee rf,rf_dept rd where trim(rf.pos_id)=trim(rd.DPT_LEADER_ID) and trim(rf.emp_id)=trim(p_emp_no);
groupsync_manager.HRM_UPDATE_MANAGER(P_EMP_NO,P_EMP_CNAME,P_DPT_ID,1,v_m_dptid,P_JOB_ID,P_UP_USER,P_ERRMSG);
if p_errmsg is not null then
p_errmsg:=4;--this manager update faul;
INSERT INTO UP_EMPLOYEE_LOGTOHR (
EMP_ID, DPT_ID, JOB_ID,
EMP_CNAME,EMP_ENAME,EMP_MAIL_ADDR,
DEPUTY_EMP_NO,COM_POSITION,EMP_HIRE_DATE,
MFLAG,ERRFLAG,MAINT_USER,MAINT_DATE)
VALUES(
P_EMP_NO,P_DPT_ID,v_JOB_ID,
P_EMP_CNAME,P_EMP_ENAME,P_EMP_EMAIL,
P_EMP_REPLACE,V_COM_POSITION,TO_DATE(P_EMP_HIRE_DATE,'yyyy/mm/dd'),
V_ERR_FLAG,P_ERRMSG,P_UP_USER,TRUNC(SYSDATE));
return;
end if;
exception when no_data_found then
v_m_dptid:=null;
end;

--2 GET JOB_ID CODE
BEGIN
SELECT NVL(TRIM(JOB_ID),'Staffer') INTO V_JOB_ID FROM RF_JOBFUNC WHERE TRIM(JOB_NAME)=TRIM(P_JOB_ID);
EXCEPTION WHEN NO_DATA_FOUND THEN
V_JOB_ID:='Staffer';
END;

--GET COM_POSITION
BEGIN
   SELECT TRIM(HJA_STATUS),TRIM(HJA_NAME) INTO V_COM_POSITION_CODE,V_COM_POSITION FROM HRM_JOBELEMENTS_ZW WHERE TRIM(HJA_CODE)=TRIM(P_COM_POSITION);
EXCEPTION WHEN NO_DATA_FOUND THEN
     V_COM_POSITION_CODE:=NULL;
V_COM_POSITION:=NULL;
END;

   SELECT TRIM(DPT_ID),TRIM(POS_ID) INTO V_DPT_ID,V_POS_ID FROM RF_EMPLOYEE WHERE TRIM(EMP_ID)=TRIM(P_EMP_NO);
   V_OLD_POS_ID:=V_POS_ID;
   V_OLD_DPT_ID:=V_DPT_ID;
   IF TRIM(V_DPT_ID)!=TRIM(P_DPT_ID) THEN
         SELECT COUNT(DPT_ID) INTO V_DPT_COUNT FROM RF_DEPT WHERE TRIM(DPT_ID)=TRIM(P_DPT_ID);
      IF V_DPT_COUNT=0 THEN
         P_ERRMSG:=2;--THIS DPT_ID NO EXIST
         INSERT INTO UP_EMPLOYEE_LOGTOHR (
          EMP_ID, DPT_ID, JOB_ID,
          EMP_CNAME,EMP_ENAME,EMP_MAIL_ADDR,
          DEPUTY_EMP_NO,COM_POSITION,EMP_HIRE_DATE,
          MFLAG,ERRFLAG,MAINT_USER)
          VALUES(
                 P_EMP_NO,P_DPT_ID,P_JOB_ID,
           P_EMP_CNAME,P_EMP_ENAME,P_EMP_EMAIL,
           P_EMP_REPLACE,P_COM_POSITION,TO_DATE(P_EMP_HIRE_DATE,'yyyy/mm/dd'),
           2,P_ERRMSG,P_UP_USER);
         RETURN;
      END IF;
      SELECT TRIM(DPT_NAME) INTO V_DPT_NAME FROM RF_DEPT WHERE TRIM(DPT_ID)=trim(P_DPT_ID);
      SELECT TRIM(DPT_NAME) INTO V_OLD_DPT_NAME FROM RF_DEPT WHERE TRIM(DPT_ID)=trim(V_OLD_DPT_ID);
      V_POS_ID:=REPLACE(TRIM(V_POS_ID),TRIM(V_DPT_ID),TRIM(P_DPT_ID));
        --6.check pos_id
        SELECT COUNT(*) INTO V_COUNT_RECORD FROM ARCHIVE.RF_POSITION WHERE RTRIM(POS_ID) = RTRIM(V_POS_ID);
        IF V_COUNT_RECORD != 1 THEN

        SELECT TRIM(JOB_NAME) INTO V_POS_NAME FROM RF_JOBFUNC WHERE TRIM(JOB_ID)=TRIM('Staffer');
        addposition(RTRIM(V_POS_ID),trim(P_DPT_ID),RTRIM(V_DPT_NAME)||'_'||RTRIM(V_POS_NAME),null,P_ERRMSG);
         IF P_ERRMSG IS NOT NULL THEN
             P_ERRMSG := 3;--'THE POS_ID ADD FAULT';
          INSERT INTO UP_EMPLOYEE_LOGTOHR (EMP_ID, DPT_ID, JOB_ID,EMP_CNAME,EMP_ENAME,EMP_MAIL_ADDR,DEPUTY_EMP_NO,COM_POSITION,EMP_HIRE_DATE,
           MFLAG,ERRFLAG,MAINT_USER)VALUES(P_EMP_NO,P_DPT_ID,P_JOB_ID,P_EMP_CNAME,P_EMP_ENAME,P_EMP_EMAIL, P_EMP_REPLACE,P_COM_POSITION,TO_DATE(P_EMP_HIRE_DATE,'yyyy/mm/dd'),
           2,P_ERRMSG,P_UP_USER);
           RETURN;
         END IF;
        END IF;
END IF;
--1.MODIFY RECORD
UPDATE ARCHIVE.RF_EMPLOYEE SET JOB_ID=TRIM(V_JOB_ID),POS_ID=TRIM(V_POS_ID),dpt_id=trim(p_dpt_id),
         emp_cname=P_EMP_CNAME,emp_ename=P_EMP_ENAME,
         deputy_emp_no=P_EMP_REPLACE,EMP_HIRE_DATE=TO_DATE(P_EMP_HIRE_DATE,'yyyy/mm/dd'),COM_POSITION_CODE=TRIM(V_COM_POSITION_CODE),COM_POSITION=TRIM(V_COM_POSITION)
         WHERE RTRIM(EMP_ID) = RTRIM(P_EMP_NO);
---IF V_DPT_COUNT>0 THEN
---V_ERR_FLAG:=2;
--SELECT TOP 1 TYPE INTO V_TYPE FROM RF_GP_EMP WHERE TRIM(EMP_ID)=TRIM(P_EMP_NO)
--IF V_TYPE=4 THEN
--UPDATE RF_GP_EMP SET TYPE=3,JOB_ID=TRIM(P_DPT_ID)||'_Staffer' WHERE TRIM(EMP_ID)=TRIM(P_EMP_NO)
--ELSE
---IF TRIM(V_DPT_ID)!=TRIM(P_DPT_ID) THEN
---delete rf_gp_emp where emp_id=RTRIM(P_EMP_NO);
---insert into RF_GP_EMP (GP_ID,EMP_ID,MEMBER_TYPE,TYPE,JOB_ID,EMP_NTACCNT) values('IDPBG_GUP',RTRIM(P_EMP_NO),4,3,V_POS_ID,RTRIM(P_EMP_NO));
---END IF;
--END IF
---END IF;
INSERT INTO UP_EMPLOYEE_LOGTOHR (
EMP_ID, DPT_ID, JOB_ID,
EMP_CNAME,EMP_ENAME,EMP_MAIL_ADDR,
DEPUTY_EMP_NO,COM_POSITION,EMP_HIRE_DATE,
MFLAG,ERRFLAG,MAINT_USER)
VALUES(
        P_EMP_NO,P_DPT_ID,P_JOB_ID,
P_EMP_CNAME,P_EMP_ENAME,P_EMP_EMAIL,
P_EMP_REPLACE,P_COM_POSITION,TO_DATE(P_EMP_HIRE_DATE,'yyyy/mm/dd'),
V_ERR_FLAG,P_ERRMSG,P_UP_USER);
exception when others then
P_ERRMSG:=10;
INSERT INTO UP_EMPLOYEE_LOGTOHR (
EMP_ID, DPT_ID, JOB_ID,
EMP_CNAME,EMP_ENAME,EMP_MAIL_ADDR,
DEPUTY_EMP_NO,COM_POSITION,EMP_HIRE_DATE,
MFLAG,ERRFLAG,MAINT_USER)
VALUES(
        P_EMP_NO,P_DPT_ID,P_JOB_ID,
P_EMP_CNAME,P_EMP_ENAME,P_EMP_EMAIL,
P_EMP_REPLACE,P_COM_POSITION,TO_DATE(P_EMP_HIRE_DATE,'yyyy/mm/dd'),
V_ERR_FLAG,P_ERRMSG,P_UP_USER);
end;
END EMP_MODINF_TOHR;

PROCEDURE HIRE_POSITION_TOHR(P_EMP_NO IN VARCHAR2,P_FIRE_DATE IN VARCHAR2,P_USER IN VARCHAR2,P_ERRMSG OUT VARCHAR2)
IS
V_COUNT NUMBER;
BEGIN
SELECT COUNT(EMP_ID) INTO V_COUNT FROM RF_EMPLOYEE WHERE TRIM(EMP_ID)=TRIM(P_EMP_NO);
IF V_COUNT=0 THEN
P_ERRMSG:=1;--THIS EMP_ID NO EXIST!
INSERT INTO UP_EMPLOYEE_LOGTOHR (EMP_ID,EMP_FIRE_DATE,MFLAG,ERRFLAG,MAINT_USER,MAINT_DATE)
VALUES(P_EMP_NO, TO_DATE(P_FIRE_DATE,'yyyy/mm/dd'),4, P_ERRMSG,P_USER,TRUNC(SYSDATE));
RETURN;
END IF;
UPDATE RF_EMPLOYEE SET EMP_ENABLED=0,EMP_FIRE_DATE=TO_DATE(P_FIRE_DATE,'yyyy/mm/dd') WHERE TRIM(EMP_ID)=TRIM(P_EMP_NO);

INSERT INTO UP_EMPLOYEE_LOGTOHR (EMP_ID,EMP_FIRE_DATE,MFLAG,ERRFLAG,MAINT_USER,MAINT_DATE)
VALUES(P_EMP_NO, TO_DATE(P_FIRE_DATE,'yyyy/mm/dd'),4, P_ERRMSG,P_USER,TRUNC(SYSDATE));
END HIRE_POSITION_TOHR;
end;
/

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

转载于:http://blog.itpub.net/13538095/viewspace-211694/

csz0100

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
組織同步的解決方案Ultimus之人員同步

CREATE OR REPLACE PACKAGE BODY GROUPSYNC_EMPLOYEE isPROCEDURE MyProcedure(Param1 IN NUMBER) isa varchar2(100);begi...
复制链接

扫一扫