The follow sample functions may be modified for use by your installation to be functional User Formula Columns:
Mask the Social Security Number for display:#
On the P2K_HR_IDENTITIES table:CREATE OR REPLACE FUNCTION ACME_SSN( P_EID_ID IN NUMBER) RETURN VARCHAR2 AS v_return varchar2(11); BEGIN select government_code into v_return from P2K_HR_IDENTITIES where ID = P_EID_ID; v_return := 'XXX-XX-'|| substr(v_return,8); return v_return; EXCEPTION WHEN OTHERS THEN return NULL; END ACME_SSN;
To show Seniority Time:#
On the P2K_HR_EMPLOYMENTS table, to show seniority in Years, Months and Dayscreate or replace FUNCTION ACME_SERVICE (P_EEM_ID IN NUMBER) RETURN VARCHAR2 AS v_years number := 0; v_months number := 0; v_days number(18,6) := 0; v_day_of_month number; v_seniority_date date; v_return varchar2(40) := 'ERROR'; v_asof_date date := TRUNC(SYSDATE); v_last_day_last_mo varchar2(8); BEGIN select SENIORITY_DATE into V_SENIORITY_DATE from P2K_HR_EMPLOYMENTS where ID = P_EEM_ID; if V_SENIORITY_DATE is NULL then v_return := 'No Seniority Date'; elsif V_SENIORITY_DATE >V_ASOF_DATE then v_return := 'Not yet reached'; else V_LAST_DAY_LAST_MO := to_char(V_SENIORITY_DATE,'DDMMYYYY'); V_LAST_DAY_LAST_MO := substr(V_LAST_DAY_LAST_MO,1,2)|| lpad(to_number(substr(V_LAST_DAY_LAST_MO,3,2))-1,2,'0')|| substr(V_LAST_DAY_LAST_MO,5); if substr(V_LAST_DAY_LAST_MO,3,2) = '00' then V_LAST_DAY_LAST_MO := substr(V_LAST_DAY_LAST_MO,1,2)||'12'||lpad(to_number(substr(V_LAST_DAY_LAST_MO,5))-1,4,'0'); end if; BEGIN V_DAY_OF_MONTH := last_day(to_date(V_LAST_DAY_LAST_MO,'DDMMYYYY')) - to_date(V_LAST_DAY_LAST_MO,'DDMMYYYY'); EXCEPTION WHEN OTHERS THEN V_DAY_OF_MONTH := 0; END; V_YEARS := TRUNC(months_between(V_ASOF_DATE,V_SENIORITY_DATE) / 12); V_MONTHS := TRUNC(months_between(V_ASOF_DATE,V_SENIORITY_DATE)) mod 12; if to_char(V_SENIORITY_DATE,'DD') < to_char(V_ASOF_DATE,'DD') THEN V_DAYS := to_number(to_char(V_ASOF_DATE,'DD')) - to_number(to_char(V_SENIORITY_DATE,'DD')); else V_DAYS := to_number(to_char(V_ASOF_DATE,'DD')) + V_DAY_OF_MONTH; end if; v_return := to_char(V_YEARS) ||' yrs '|| to_char(V_MONTHS) || ' mos ' || to_char(V_DAYS) || ' days'; end if; return v_return; EXCEPTION WHEN OTHERS then v_return := 'ERR ';--||sqlerrm(sqlcode); return v_return; END ACME_SERVICE;