Pages

Wednesday, November 1, 2017

AUTO CREATE PS My Favorite links

auto - create FAV for user MY_OPRID

set serveroutput on

CREATE OR REPLACE Function chkFav( fav_in IN varchar2 )   RETURN number
IS
  OERR  EXCEPTION;
  ver   integer;
begin 

   ver:=0;

   select version into ver from PSPRUFDEFN  where PORTAL_NAME = 'MY_PORTAL' AND oprid='MY_OPRID' and portal_label=fav_in;

   return ver;

exception          

  when no_data_found then return 0;
  
  WHEN OTHERS then
  declare
    errcd  NUMBER := SQLCODE;
    errmsg VARCHAR2(300) := SQLERRM;
    begin
       DBMS_OUTPUT.PUT_LINE('Error: rc='|| errcd ||','||errmsg);
    end;

END;
/


--------------------------------------------------------------------------------- main proc
declare 
  fav   varchar2(200);
  ver1  integer;
  idx   integer;
  cnt   integer;  

begin
   
   cnt :=0;

   SELECT VERSION into ver1 FROM PSLOCK WHERE OBJECTTYPENAME IN ('PRUF') FOR UPDATE OF VERSION;

   -- Project
   fav := 'Project - HHS';

   select chkFav(fav) into idx from dual;

   if idx = 0  then

      ver1:=ver1+1;
      
      Insert into SYSADM.PSPRUFDEFN Values
      ('MY_PORTAL', 'MY_OPRID', 'V', fav, ver1, 
       'TX_PROJECT_GBL', 'PORTAL_USER_FAVORITES', 0, 'MY_OPRID', cast(sysdate as timestamp) , 
       ' ', 'c/H_CUSTOM_MENU.TX_PROJECT.GBL');

      delete from PSPRUFDEL where OPRID = 'MY_OPRID' and PORTAL_NAME='MY_PORTAL' and PORTAL_REFTYPE='V' and PORTAL_LABEL=fav
      and exists (select 1 from PSPRUFDEL where OPRID = 'MY_OPRID' and PORTAL_NAME='MY_PORTAL' and PORTAL_REFTYPE='V' and PORTAL_LABEL=fav);

      cnt:=cnt+1;

   end if;


   -- FS
   fav := 'Funding Source';

   select chkFav(fav) into idx from dual;

   if idx = 0  then

      ver1:=ver1+1;
      
      Insert into SYSADM.PSPRUFDEFN Values
      ('MY_PORTAL', 'MY_OPRID', 'V', fav, ver1, 
       'EP_FUND_SOURCE_GBL', 'PORTAL_USER_FAVORITES', 0, 'MY_OPRID', cast(sysdate as timestamp) , 
       ' ', 'c/MANAGE_COMMITMENT_CONTROL.KK_FUND_SOURCE.GBL');

      delete from PSPRUFDEL where OPRID = 'MY_OPRID' and PORTAL_NAME='MY_PORTAL' and PORTAL_REFTYPE='V' and PORTAL_LABEL=fav
      and exists (select 1 from PSPRUFDEL where OPRID = 'MY_OPRID' and PORTAL_NAME='MY_PORTAL' and PORTAL_REFTYPE='V' and PORTAL_LABEL=fav);

      cnt:=cnt+1;

   end if;

   -- Trace SQL
   fav := 'Trace SQL';

   select chkFav(fav) into idx from dual;

   if idx = 0  then

      ver1:=ver1+1;
      
      Insert into SYSADM.PSPRUFDEFN Values
      ('MY_PORTAL', 'MY_OPRID', 'V', fav, ver1, 
       'PT_TRACE_SQL_GBL', 'PORTAL_USER_FAVORITES', 0, 'MY_OPRID', cast(sysdate as timestamp) , 
       ' ', 'c/UTILITIES.TRACE_SQL.GBL');

      delete from PSPRUFDEL where OPRID = 'MY_OPRID' and PORTAL_NAME='MY_PORTAL' and PORTAL_REFTYPE='V' and PORTAL_LABEL=fav
      and exists (select 1 from PSPRUFDEL where OPRID = 'MY_OPRID' and PORTAL_NAME='MY_PORTAL' and PORTAL_REFTYPE='V' and PORTAL_LABEL=fav);

      cnt:=cnt+1;

   end if;

   -- Processes
   fav := 'Processes';

   select chkFav(fav) into idx from dual;

   if idx = 0  then

      ver1:=ver1+2;
             
      Insert into SYSADM.PSPRUFDEFN Values
      ('MY_PORTAL', 'MY_OPRID', 'V', fav, ver1, 
       'PT_PRCSDEFN_GBL', 'PORTAL_USER_FAVORITES', 0, 'MY_OPRID', cast(sysdate as timestamp) ,
       ' ', 'c/PROCESS_SCHEDULER.PRCSDEFN.GBL');

      delete from PSPRUFDEL where OPRID = 'MY_OPRID' and PORTAL_NAME='MY_PORTAL' and PORTAL_REFTYPE='V' and PORTAL_LABEL=fav
      and exists (select 1 from PSPRUFDEL where OPRID = 'MY_OPRID' and PORTAL_NAME='MY_PORTAL' and PORTAL_REFTYPE='V' and PORTAL_LABEL=fav);

      cnt:=cnt+1;

   end if;

   -- FS - HHS
   fav := 'Funding Source Allocation-HHS';

   select chkFav(fav) into idx from dual;

   if idx = 0  then

      ver1:=ver1+2;
       
      Insert into SYSADM.PSPRUFDEFN Values
      ('MY_PORTAL', 'MY_OPRID', 'V', fav, ver1, 
       'TX_KK_FS_ALLOCATN_GBL', 'PORTAL_USER_FAVORITES', 0, 'MY_OPRID', cast(sysdate as timestamp) ,
       ' ', 'c/H_CUSTOM_MENU.TX_KK_FS_ALLOCATN.GBL');

      delete from PSPRUFDEL where OPRID = 'MY_OPRID' and PORTAL_NAME='MY_PORTAL' and PORTAL_REFTYPE='V' and PORTAL_LABEL=fav
      and exists (select 1 from PSPRUFDEL where OPRID = 'MY_OPRID' and PORTAL_NAME='MY_PORTAL' and PORTAL_REFTYPE='V' and PORTAL_LABEL=fav);
      
      cnt:=cnt+1;

   end if;

   -- Vchr Reg Entry
   fav := 'Regular Entry';

   select chkFav(fav) into idx from dual;

   if idx = 0  then

      ver1:=ver1+2;
       
      Insert into SYSADM.PSPRUFDEFN Values
      ('MY_PORTAL', 'MY_OPRID', 'V', fav , ver1, 
       'EP_VCHR_EXPRESS_GBL', 'PORTAL_USER_FAVORITES', 0, 'MY_OPRID', cast(sysdate as timestamp) ,
       ' ', 'c/ENTER_VOUCHER_INFORMATION.VCHR_EXPRESS.GBL');

      delete from PSPRUFDEL where OPRID = 'MY_OPRID' and PORTAL_NAME='MY_PORTAL' and PORTAL_REFTYPE='V' and PORTAL_LABEL=fav
      and exists (select 1 from PSPRUFDEL where OPRID = 'MY_OPRID' and PORTAL_NAME='MY_PORTAL' and PORTAL_REFTYPE='V' and PORTAL_LABEL=fav);

      cnt:=cnt+1;

   end if;

   if cnt > 0 then

      -- PORTAL_REFTYPE
      ver1:=ver1+1;
      
      DELETE FROM PSPRUFDEFN WHERE PORTAL_NAME = 'MY_PORTAL' AND OPRID = 'MY_OPRID' AND PORTAL_REFTYPE = 'L' 
      AND PORTAL_LABEL = 'PORTAL_USER_FAVORITES';
      
      Insert into SYSADM.PSPRUFDEFN Values
      ('MY_PORTAL', 'MY_OPRID', 'L', 'PORTAL_USER_FAVORITES', ver1, ' ', ' ', 0, 'MY_OPRID', cast(sysdate as timestamp) , ' ', NULL);
      
      -- increment VERSION by # of FAVs added + 1 (PORTAL_USER_FAVORITES)
      UPDATE PSVERSION SET VERSION = VERSION + cnt + 1 WHERE OBJECTTYPENAME = 'SYS';
      
      UPDATE PSVERSION SET VERSION = ver1 WHERE OBJECTTYPENAME = 'PRUF';
         
      update pslock set VERSION= ver1 WHERE OBJECTTYPENAME = 'PRUF'; 
      
      commit;
      
   else
      rollback;
   end if;

DBMS_OUTPUT.PUT_LINE('Total FAV added:' || cnt);
      
exception
  WHEN OTHERS then
  declare
    errcd  NUMBER := SQLCODE;
    errmsg VARCHAR2(300) := SQLERRM;
    begin
       DBMS_OUTPUT.PUT_LINE('Error: fav='|| fav|| ' rc='|| errcd ||','||errmsg);
       rollback;
    end;
end;
/

---------------------------------------------------------------------------------- 
drop Function chkFav;

-- this stores deleted FAV for user 
--select * from PSPRUFDEL where OPRID = 'MY_OPRID'



   
   


drop Function chkFav;