Newbie Needs help with dates

Hi All,
My first post and I hope that someone will be able to help me???

OK I have just started to work with Oracle and need to try and better the following or look for a more efficient way of running the following scripts.

What I am trying to do is the following..........
A DBLINK has been created for me to create Views on our local server, this information is extracted from tables on another server.

Due to security and inhouse policies the person running the script has to be logged on as me........(more DBLINKS can be created in the future)

So here goes;

I am running the following script on my local server.

With the following Function on the server.

CREATE OR REPLACE FORCE VIEW HR_RTD.V_SYSPER2_JOBS
(JOB_ID, CAT_CD, JOB_DATE_FROM, ESTABL_PLAN_ONLY_FLAG, FUNC_ORG_CHART_FLAG, HEAD_OF_ORG_UNIT_FLAG,
MGMT_JOB_FLAG, ORG_CHART_FLAG, SENS_DATE_FROM, SENS_JOB_FLAG, ACR_COMPL, OCCP_DATE_FROM,
OCCP_DATE_TO,JOB_STATUS, LOGIN, DN_USUAL_FAMILY_NAME_UPPER, DN_USUAL_GIVEN_NAME_UPPER)
AS
(
Select J.JOB_ID, J.CAT_CD, J.DATE_FROM JOB_DATE_FROM, J.ESTABL_PLAN_ONLY_FLAG, J.FUNC_ORG_CHART_FLAG, J.HEAD_OF_ORG_UNIT_FLAG,
J.MGMT_JOB_FLAG, J.ORG_CHART_FLAG, J.SENS_DATE_FROM, J.SENS_JOB_FLAG, U.ACR_COMPL, JO.DATE_FROM OCCP_DATE_FROM,
JO.DATE_TO OCCP_DATE_TO, (DECODE(JO.DATE_FROM,
NULL, 'VACANT',
(DECODE(JO.DATE_TO,
NULL, 'OCCUPIED',
DECODE (xxxl_test_sysdate(JO.DATE_TO),
'YES', 'OCCUPIED',
'Vacant'))))) JOB_STATUS,
P.LOGIN, P.DN_USUAL_FAMILY_NAME_UPPER, P.DN_USUAL_GIVEN_NAME_UPPER
From SP2_JOBS@MAC.world J,
SP2_JOB_OCCUPATION_STATUSES@MAC.world JO,
SP2_JOB_OCCUPATION_STATUSES@MAC.world JO2,
SP2_ORG_UNIT_VERSIONS@MAC.world U,
SP2_PERSONS@MAC.world P
Where JO.DATE_FROM = (SELECT MAX(JO2.DATE_FROM) FROM SP2_JOB_OCCUPATION_STATUSES JO2)
AND J.JOB_ID = JO.JOB_ID (+)
AND JO.PER_ID = P.PER_ID (+)
AND J.OU_ID = U.OU_ID
AND U.ACR_COMPL LIKE 'RTD%'
AND (U.DATE_TO is null OR U.DATE_TO >= sysdate)
/*AND (JO.DATE_FROM is null or JO.DATE_FROM <= sysdate)*/
AND J.DATE_FROM is not null
);


What I am trying to do is check on the JO.DATE_TO and JO.DATE_FROM fields for the following.

First if the JO.DATE_TO is blank then under the field heading JOB_STATUS put OCCUPIED.
Second if the JO.DATE_TO is less than SYSDATE put VACANT
Third if the JO.DATE_TO is greater than SYSDATE put OCCUPIED
Fourth and hardest, if there is a double entry under the heading JOB_ID from table JOBS then return the last entry from the JO.DATE_TO placing either VACANT or OCCUPIED depending on sysdate and the date that is in the entry.

I get the message to say that the view has been created but with one compile error.
The below mentioned Function is on the server and has a red cross beside the view name stating invalid.



CREATE OR REPLACE FUNCTION xxxl_test_sysdate (p_date_to DATE)
RETURN VARCHAR2
IS
tmpvar VARCHAR2(25);
/******************************************************************************
NAME: xxxl_test_sysdate
PURPOSE: Return YES if Sysdate is less than value in JO.DATE_TO field.

REVISIONS:
Ver Date Author Description
--------- ---------- --------------- ------------------------------------
1.0 8/31/2004 1. Created this function.

NOTES:
******************************************************************************/
BEGIN
IF (p_date_to > SYSDATE)
THEN
tmpvar := 'YES';
ELSE
tmpvar := 'NO';
END IF;

RETURN tmpvar;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
NULL;
WHEN OTHERS
THEN
-- Consider logging the error and then re-raise
RAISE;
END xxxl_test_sysdate;
/


Would it be possible to help me create the script that would make the correct checks and then give the correct values.

Is it possible to create a table on the local server that contains the information from the query and then make a view from that or create a view and then have a table updated with that information???

The reason is that I would have to run this script every morning and have the updates from the other table inserted into the local table or view ( other persons cannot view the info as they would have to be logged on the pc as me)

Working on the princple as above could someone tell me how I would be able to insert the info from the view created onto a table on the local server..........I have searched the internet and cannot find any sample scripts.

I hope that you are able to understand this and help me.

Mac