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;
