FACULTY.UPDATE_FIS_APPT

General

Source

is

procedure doAppt is

numrows number(8) := 0;
vemplid work_appt.emplid%type := null;
vempl_rec work_appt.empl_rec%type := null;
vposnum work_appt.position_number%type := null;
VINSERTCOUNT NUMBER(8) := 0;
VTERMCOUNT NUMBER(8) := 0;
VUPDATECOUNT NUMBER(8) := 0;
VDELETECOUNT NUMBER(8) := 0;

cursor c_jobs is
select * from work_appt
where appt_status in ('A','L','P','W');


begin
--DBMS_OUTPUT.ENABLE;
--dbms_output.put_line('Begin');

--loop through work_appt
--if status in A,L,P,W then update/insert FIS_APPT

for e in c_jobs loop
begin
numrows := numrows+1;

-- assign values

vemplid := e.emplid;
vempl_rec := e.empl_rec;
vposnum := e.position_number;

--ATTEMPT TO UPDATE FIS_APPT
UPDATE FIS_APPT
SET
POSITION_NUMBER=E.POSITION_NUMBER,
APPT_JOBCLASS=E.APPT_JOBCLASS,
APPT_JOBCLASS_DESC=E.APPT_JOBCLASS_DESC,
RESP_DEPT_ID=E.RESP_DEPT_ID,
DEPT_DESC=E.DEPT_DESC,
EFFECTIVE_DATE=E.EFFECTIVE_DATE,
APPT_STATUS=E.APPT_STATUS,
APPT_STATUS_DATE=E.APPT_STATUS_DATE,
OFFICER_YESNO=E.OFFICER_YESNO,
TEMPORARY_YESNO=E.TEMPORARY_YESNO,
FTE_AMT=E.FTE_AMT,
FULL_TIME_PCT=E.FULL_TIME_PCT,
APPT_SALARY=E.APPT_SALARY,
APPT_SALARY_PERIOD=E.APPT_SALARY_PERIOD,
APPT_ANNUAL_RATE=E.APPT_ANNUAL_RATE,
APPT_END_DATE=E.APPT_END_DATE,
UPDATE_DATE=E.UPDATE_DATE
WHERE
EMPLID=E.EMPLID
--AND POSITION_NUMBER=E.POSITION_NUMBER
AND EMPL_REC= E.EMPL_REC;

IF SQL%FOUND THEN
COMMIT WORK;
VUPDATECOUNT := VUPDATECOUNT + 1;
else
INSERT INTO FIS_APPT (
EMPLID,
POSITION_NUMBER,
APPT_JOBCLASS,
APPT_JOBCLASS_DESC,
RESP_DEPT_ID,
DEPT_DESC,
EMPL_REC,
EFFECTIVE_DATE,
APPT_STATUS,
APPT_STATUS_DATE,
OFFICER_YESNO,
TEMPORARY_YESNO,
FTE_AMT,
FULL_TIME_PCT,
APPT_SALARY,
APPT_SALARY_PERIOD,
APPT_ANNUAL_RATE,
APPT_END_DATE,
UPDATE_DATE) VALUES
(E.EMPLID,
E.POSITION_NUMBER,
E.APPT_JOBCLASS,
E.APPT_JOBCLASS_DESC,
E.RESP_DEPT_ID,
E.DEPT_DESC,
E.EMPL_REC,
E.EFFECTIVE_DATE,
E.APPT_STATUS,
E.APPT_STATUS_DATE,
E.OFFICER_YESNO,
E.TEMPORARY_YESNO,
E.FTE_AMT,
E.FULL_TIME_PCT,
E.APPT_SALARY,
E.APPT_SALARY_PERIOD,
E.APPT_ANNUAL_RATE,
E.APPT_END_DATE,
E.UPDATE_DATE);
commit;
VINSERTCOUNT := VINSERTCOUNT + 1;

-- other update process INSERTs A PERSON IF ONE DOES NOT EXIST

-- another INSERTs A POSITION IF ONE DOES NOT EXIST
end if;
exception
when others then
SQLCODE_VAL := SQLCODE;
SQLERRM_VAL := SUBSTR(SQLERRM,1,140);
insert into work_update_process (process_name, Process_msg, process_date) values ('update_fis_appt','EMPLID:'||VEMPLID||' POSNUM:'||VPOSNUM||' '||SQLCODE_VAL ||' '||SQLERRM_VAL,SYSDATE);
commit;
end;

end loop;

insert into work_update_process (process_name, Process_msg, process_date) values ('update_fis_appt','Number updated= '||TO_CHAR(VUPDATECOUNT)||'; Number inserted= '||to_char(VINSERTCOUNT),SYSDATE);
COMMIT;

exception
when others then
SQLCODE_VAL := SQLCODE;
SQLERRM_VAL := SUBSTR(SQLERRM,1,140);
insert into work_update_process (process_name, Process_msg, process_date) values ('update_fis_appt','EMPLID:'||VEMPLID||' POSNUM:'||VPOSNUM||' '||SQLCODE_VAL ||' '||SQLERRM_VAL,SYSDATE);
commit;
end;


END;

Status:

Valid

Object Definition

CREATE OR REPLACE PACKAGE BODY "FACULTY"."UPDATE_FIS_APPT" is

procedure doAppt is

numrows number(8) := 0;
vemplid work_appt.emplid%type := null;
vempl_rec work_appt.empl_rec%type := null;
vposnum work_appt.position_number%type := null;
VINSERTCOUNT NUMBER(8) := 0;
VTERMCOUNT NUMBER(8) := 0;
VUPDATECOUNT NUMBER(8) := 0;
VDELETECOUNT NUMBER(8) := 0;

cursor c_jobs is
select * from work_appt
where appt_status in ('A','L','P','W');


begin
--DBMS_OUTPUT.ENABLE;
--dbms_output.put_line('Begin');

--loop through work_appt
--if status in A,L,P,W then update/insert FIS_APPT

for e in c_jobs loop
begin
numrows := numrows+1;

-- assign values

vemplid := e.emplid;
vempl_rec := e.empl_rec;
vposnum := e.position_number;

--ATTEMPT TO UPDATE FIS_APPT
UPDATE FIS_APPT
SET
POSITION_NUMBER=E.POSITION_NUMBER,
APPT_JOBCLASS=E.APPT_JOBCLASS,
APPT_JOBCLASS_DESC=E.APPT_JOBCLASS_DESC,
RESP_DEPT_ID=E.RESP_DEPT_ID,
DEPT_DESC=E.DEPT_DESC,
EFFECTIVE_DATE=E.EFFECTIVE_DATE,
APPT_STATUS=E.APPT_STATUS,
APPT_STATUS_DATE=E.APPT_STATUS_DATE,
OFFICER_YESNO=E.OFFICER_YESNO,
TEMPORARY_YESNO=E.TEMPORARY_YESNO,
FTE_AMT=E.FTE_AMT,
FULL_TIME_PCT=E.FULL_TIME_PCT,
APPT_SALARY=E.APPT_SALARY,
APPT_SALARY_PERIOD=E.APPT_SALARY_PERIOD,
APPT_ANNUAL_RATE=E.APPT_ANNUAL_RATE,
APPT_END_DATE=E.APPT_END_DATE,
UPDATE_DATE=E.UPDATE_DATE
WHERE
EMPLID=E.EMPLID
--AND POSITION_NUMBER=E.POSITION_NUMBER
AND EMPL_REC= E.EMPL_REC;

IF SQL%FOUND THEN
COMMIT WORK;
VUPDATECOUNT := VUPDATECOUNT + 1;
else
INSERT INTO FIS_APPT (
EMPLID,
POSITION_NUMBER,
APPT_JOBCLASS,
APPT_JOBCLASS_DESC,
RESP_DEPT_ID,
DEPT_DESC,
EMPL_REC,
EFFECTIVE_DATE,
APPT_STATUS,
APPT_STATUS_DATE,
OFFICER_YESNO,
TEMPORARY_YESNO,
FTE_AMT,
FULL_TIME_PCT,
APPT_SALARY,
APPT_SALARY_PERIOD,
APPT_ANNUAL_RATE,
APPT_END_DATE,
UPDATE_DATE) VALUES
(E.EMPLID,
E.POSITION_NUMBER,
E.APPT_JOBCLASS,
E.APPT_JOBCLASS_DESC,
E.RESP_DEPT_ID,
E.DEPT_DESC,
E.EMPL_REC,
E.EFFECTIVE_DATE,
E.APPT_STATUS,
E.APPT_STATUS_DATE,
E.OFFICER_YESNO,
E.TEMPORARY_YESNO,
E.FTE_AMT,
E.FULL_TIME_PCT,
E.APPT_SALARY,
E.APPT_SALARY_PERIOD,
E.APPT_ANNUAL_RATE,
E.APPT_END_DATE,
E.UPDATE_DATE);
commit;
VINSERTCOUNT := VINSERTCOUNT + 1;

-- other update process INSERTs A PERSON IF ONE DOES NOT EXIST

-- another INSERTs A POSITION IF ONE DOES NOT EXIST
end if;
exception
when others then
SQLCODE_VAL := SQLCODE;
SQLERRM_VAL := SUBSTR(SQLERRM,1,140);
insert into work_update_process (process_name, Process_msg, process_date) values ('update_fis_appt','EMPLID:'||VEMPLID||' POSNUM:'||VPOSNUM||' '||SQLCODE_VAL ||' '||SQLERRM_VAL,SYSDATE);
commit;
end;

end loop;

insert into work_update_process (process_name, Process_msg, process_date) values ('update_fis_appt','Number updated= '||TO_CHAR(VUPDATECOUNT)||'; Number inserted= '||to_char(VINSERTCOUNT),SYSDATE);
COMMIT;

exception
when others then
SQLCODE_VAL := SQLCODE;
SQLERRM_VAL := SUBSTR(SQLERRM,1,140);
insert into work_update_process (process_name, Process_msg, process_date) values ('update_fis_appt','EMPLID:'||VEMPLID||' POSNUM:'||VPOSNUM||' '||SQLCODE_VAL ||' '||SQLERRM_VAL,SYSDATE);
commit;
end;


END;