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 Days

create 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;

---

Notes #

Click to create a new notes page