DELETE
FROM WORK_APPT;
INSERT
INTO WORK_APPT
SELECT
a.EMPLID,
a.POSITION_NUMBER,
a.JOBCODE AS APPT_JOBCLASS,
b.jobclass_title
AS APPT_JOBCLASS_DESC,
a.DEPT_ID
AS RESP_DEPT_ID,
c.descr
as DEPT_DESC,
a.EMPL_RCD
AS EMPL_REC,
a.EFFDT
AS EFFECTIVE_DATE,
a.EMPL_STATUS
AS APPT_STATUS,
a.action_dt
AS APPT_STATUS_DATE,
a.OFFICER_CD
AS OFFICER_YESNO,
decode(a.REG_TEMP,'T','Y','R','N')
AS TEMPORARY_YESNO,
a.FTE
AS FTE_AMT,
a.PERCENT_OF_TIME_CU
AS FULL_TIME_PCT,
a.COMPRATE
AS APPT_SALARY,
a.COMP_FREQUENCY
AS APPT_SALARY_PERIOD,
a.ANNUAL_RT
APPT_ANNUAL_RATE,
a.APPOINT_END_DT
AS APPT_END_DATE,
TO_DATE(TO_CHAR(SYSDATE,'MM/DD/YYYY'),'MM/DD/YYYY')
as UPDATE_DATE
FROM
WORK_PSJOB
a,
fis_jobclass
b ,
fis_dept
c
where
b.jobclass_code=a.jobcode
and
c.dept_id=a.dept_id
and
a.empl_status IN ('A','L','P','W')
and
(a.appoint_end_dt > sysdate or a.appoint_end_dt is null)
UNION
SELECT
a.EMPLID,
a.POSITION_NUMBER,
a.JOBCODE AS APPT_JOBCLASS,
b.jobclass_title
AS APPT_JOBCLASS_DESC,
a.DEPT_ID
AS RESP_DEPT_ID,
c.descr
as DEPT_DESC,
a.EMPL_RCD
AS EMPL_REC,
a.EFFDT
AS EFFECTIVE_DATE,
a.EMPL_STATUS
AS APPT_STATUS,
a.action_dt
AS APPT_STATUS_DATE,
a.OFFICER_CD
AS OFFICER_YESNO,
decode(a.REG_TEMP,'T','Y','R','N')
AS TEMPORARY_YESNO,
a.FTE
AS FTE_AMT,
a.PERCENT_OF_TIME_CU
AS FULL_TIME_PCT,
a.COMPRATE
AS APPT_SALARY,
a.COMP_FREQUENCY
AS APPT_SALARY_PERIOD,
a.ANNUAL_RT
APPT_ANNUAL_RATE,
a.APPOINT_END_DT
AS APPT_END_DATE,
TO_DATE(TO_CHAR(SYSDATE,'MM/DD/YYYY'),'MM/DD/YYYY')
as UPDATE_DATE
FROM
WORK_PSJOB
a,
fis_jobclass
b ,
fis_dept
c
where
b.jobclass_code=a.jobcode
and
c.dept_id=a.dept_id
AND
EXISTS
(SELECT
'X' FROM FIS_APPT
WHERE EMPLID=A.EMPLID AND EMPL_REC=A.EMPL_RCD
);
EXEC
UPDATE_FIS_APPT.doAppt;
CREATE
TABLE "FACULTY"."FIS_APPT"(
"EMPLID"
VARCHAR2(11),
"POSITION_NUMBER" VARCHAR2(8),
"APPT_JOBCLASS"
VARCHAR2(6),
"APPT_JOBCLASS_DESC" VARCHAR2(50),
"RESP_DEPT_ID"
VARCHAR2(10),
"DEPT_DESC" VARCHAR2(30),
"EMPL_REC"
NUMBER,
"EFFECTIVE_DATE"
DATE,
"APPT_STATUS"
CHAR(1),
"APPT_STATUS_DATE"
DATE,
"OFFICER_YESNO" CHAR(1),
"TEMPORARY_YESNO"
CHAR(1),
"FTE_AMT"
NUMBER(5, 2),
"FULL_TIME_PCT"
NUMBER(7, 2),
"APPT_SALARY"
NUMBER(18, 6),
"APPT_SALARY_PERIOD"
VARCHAR2(5),
"APPT_ANNUAL_RATE"
NUMBER(15, 3),
"APPT_END_DATE"
DATE,
"UPDATE_DATE"
DATE)
TABLESPACE "FACDATA1"
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS
255
STORAGE ( INITIAL 64K NEXT 128K
MINEXTENTS 1 MAXEXTENTS 4096
PCTINCREASE 0 FREELISTS 1 FREELIST
GROUPS 1)
LOGGING