Peoplecode Scroll, Rowset, Row, Fields

* OriginalValue vs PriorValue

AE Write Log

 Local File &Logfile;

&Logfile = GetFile(“Filename”,”W”,%FilePath_Absolute);

&Logfile.WriteLine(“Your messages”);

&Logfile.Close();

AE Trace Quick Look at Tables Modified

 

UPDATE

--- get all UPDATEs

awk '/^UPDATE /,/^\//' AE_AP_MATCH_999_0302143109.AET > xxx 

--- combine multiple lines into 1 line

sed -n 'H;:a /^\/$/{x;s/\n//g;p;n;h;b a}' xxx > yyy             

--- ignore temp tables

egrep -v '^UPDATE PS_.*_(T.*[0-9]|TAO) SET ' yyy                        

 

INSERT

grep ^INSERT AE_AP_MATCH_999_0302143109.AET| cut -d' ' -f3| egrep -v 'PS_.*_(T.*[0-9]|TAO)'

Calculate Field Offset in a Record

Useful for generate load file:

select table_name, COLUMN_ID, COLUMN_NAME,DATA_TYPE, DATA_LENGTH, sum(DATA_LENGTH) 

over(PARTITION BY  table_name order by COLUMN_ID RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as "Running Length"

from all_tab_columns where table_name ='PS_REQ_HDR'

Export Process Instance into test environment

-----------------------------------------------reset PI 

insert into psprcsrqst  select * from psprcsrqst@db where PRCSINSTANCE = 88888888

 

insert into PSPRCSPARMS select * from PSPRCSPARMS@db WHERE    PRCSINSTANCE  = 88888888

 

update PSPRCSPARMS set PARMLIST='...', ORIGPARMLIST='...' WHERE    PRCSINSTANCE  = 88888888

 

UPDATE PS_AERUNCONTROL SET ae_run_data = to_clob('INTFAPAM    MAIN    Notify  INTFAPAM    PrcNtfy Step05  ') where PROCESS_INSTANCE=88888888

 

UPDATE PS_AERUNCONTROL SET ae_run_data = to_clob('INTFAPAM    MAIN    Notify  INTFAPAM    PrcNtfy Step06  ') where PROCESS_INSTANCE=88888888

 

insert into PSPRCSQUE select * from PSPRCSQUE@db where PRCSINSTANCE =88888888

 

insert into PS_CDM_file_LIST select * from PS_CDM_file_LIST@db where PRCSINSTANCE =88888888

 

insert into ps_cdm_list select * from ps_cdm_list@db where PRCSINSTANCE =88888888

 

------------------------------------------------- reassign output folder/content ID

update PS_CDM_file_LIST set CONTENTID=4071614, filename='AP_CRDT_MEMO.pdf',  file_size=3570, CDM_FILE_TYPE='PDF'

where PRCSINSTANCE =88888888  and CDM_FILE_TYPE='LOG'

 

update ps_cdm_list set PRCSOUTPUTDIR='...',OUTPUTDIR='...' where PRCSINSTANCE =88888888

 

update psprcsrqst set CONTENTID=4071614 where PRCSINSTANCE =88888888

------------------------------------------------- stuck in Posting

update psprcsrqst set DISTSTATUS=4, CONTENTID=4144646 where PRCSINSTANCE = 88888888                                   

update PSPRCSQUE set DISTSTATUS=4 where PRCSINSTANCE = 88888888                                                        

insert into PS_CDM_file_list                                                                                          

select 88888888,CONTENTID,'AMPS1000_4648811.log',CDM_FILE_TYPE, 166249,sysdate-1                                      

from PS_CDM_file_list where PRCSINSTANCE =88888888                                                     

insert into PS_CDM_list                                                                                              

select 88888888,CONTENTID,PRCSNAME,PRCSTYPE,'XXXXXXXX/log_output/AE_AMPS1000_88888888',

CONTENT_DESCR,OUTDESTFORMAT,RQSTDTTM,ENDDTTM,EXPIRATION_DATE,4,DISTNODENAME,OUTPUTDIR,0,ADMIN_FILENAME,GENPRCSTYPE,  

0,FILENAME,PSRF_FOLDER_NAME,PRCSBURSTRPT,MSGNODENAME,CDM_APPROVAL_FLAG,QRYXFORMFILETYPE                               

from PS_CDM_list where PRCSINSTANCE = 4687995    

J



 

How many times a sql gets run?

select SQL_ID, CHILD_NUMBER, EXECUTIONS, FIRST_LOAD_TIME, ROWS_PROCESSED, LAST_LOAD_TIME, LAST_ACTIVE_TIME,module, action
from gv$sql where sql_text like 'select * from %'

CHILD_NUMBER: 0,1,2,...
EXECUTIONS: # of time a sql runs
ROWS_PROCESSED: # of rows fetched, can increase with more fetching
MODULE: name of process created the 1st sql, set by DBMS_APPLICATION_INFO.SET_MODULE
ACTION: name of process created the 1st sql, set by DBMS_APPLICATION_INFO.SET_ACTION

Source: V$SQL reference

Source: Why some sqls show up multiple times?  CHILD_NUMBER (unique, 0 ~...)

• caused by schema difference(owner), optimizer mode(ALL_ROWS,FIRST_ROWS), bind var(implicit conversion)...
• Why cursor IS_SHAREABLE = 'No' (reason: select * from v$SQL_SHARED_CURSOR where sql_id=...) 

Source: Different v$sql views

• v$sql the details -- if you have multiple copies of the query: "select * from T" in your shared pool, v$sql will have a row per query. This can happen if user U1 and user U2 both have a table T and both issue "select * from T". Those are entirely different queries with different plans and so on. v$sql will have 2 rows.
• v$sqlarea is a aggregate of v$sql. It selects out DISTINCT sql. "select * from T" will appear there.
• v$sqltext is simply a way to see the entire query. the v$sql and v$sqlarea views only show the first 1000 bytes. newlines and other control characters are replace with whitespace.
• v$sqltext_with_newlines is v$sqltext without the whitespace replacment.
• 
  

Compare:

select sql_id, CHILD_NUMBER,EXECUTIONS, FIRST_LOAD_TIME, ROWS_PROCESSED, LAST_LOAD_TIME, LAST_ACTIVE_TIME, module, action, sql_text from gv$sql where sql_text  ='....'

with:
select sql_id, EXECUTIONS, FIRST_LOAD_TIME, ROWS_PROCESSED,  LAST_LOAD_TIME, LAST_ACTIVE_TIME , module, action, sql_text  

from gv$sqlarea where sql_text ='..'

2nd cursor will aggregate info on all columns from 1st  cursor and sum up totals from  EXECUTIONS, ROWS_PROCESSED

Source: PS 8.50 uses DBMS_APPLICATION_INFO to Identify DB Sessions

For some components the action is set to ‘xyzzy’. This seems to be a default value set when the component is opened, but before any of the pages are processed.  Therefore, it refers to activity in the search dialogue, including processing of :

• look ups to obtain values for search criteria
• SQL issued during SearchSave PeopleCode to validate the search criteria.
• the query on the Component Search record

MODULE & ACTION are different in v$sql vs v$session.

Source: Histogram vs Stats

• improve skew condition - histograms are useful for improving cardinality estimates
• Histograms are created automatically when statistics are gathered using the SKEWONLY and AUTO options in METHOD_OPT: EXEC DBMS_STATS.GATHER_TABLE_STATS( … METHOD_OPT=>'FOR ALL COLUMNS SIZE AUTO' …)
• How does the Oracle Database know that a particular column is used in a query predicate or join? This information is gathered by the Oracle Optimizer at parse time and ultimately stored in the Oracle data dictionary in a table called SYS.COL_USAGE$. 

To manually flush monitoring info: dbms_stats.flush_database_monitoring_info()

PS BP Notes

A summary from various sources

https://docs.oracle.com/cd/F25059_01/fscm92pbr34/eng/fscm/fscc/task_PeopleSoftCommitmentControlReportsListAndGeneralDescription.html

• A successful Check Only budget entry will have a Budget Hdr Status of P to indicate a valid Budget Check Only. The value P is equivalent to N (not checked). Subsequently, after full processing, a successful budget check is indicated by the Budget Hdr Status V (valid), which indicates a successful budget check and posting to the Ledger_KK record.

• A Check Only that results in errors being logged updates the Budget Hdr Status to E (errors) and the applications links an access to the exception table functions as with normal budget checking and posting. Lines with errors are updated to an E (error) status. Those that are valid remain with an N (not checked) status.

• Note: If a budget transaction line is not subject to budget checking for any Commitment Control ledger groups assigned to the General Ledger business unit, the Budget processor sets the budget line status to B. If the budget transaction line is subject to budget checking, the budget line status has a V.

• Budget Type = Ledger Group

–AP = Appropriation (APPROP)

–OR = Organization (ORG)

–PR = Project /Grant (PROJ_GRT)

–RE = Revenue Estimate (REV_EST)

• Amount Field = Ledgers within Controlled Ledger Group

–Encumbered Amount = APPROP_ENC + Posted Total Amount

–Pre‐encumbered Amount = APPROP_PRE + Posted Total Amount

–Expended Amount = APPROP_EXP + Posted Total Amount

–Budget Amount = APPROP_BUD + Posted Total Amount

https://support.oracle.com/knowledge/PeopleSoft%20Enterprise/845460_1.html

https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=226133918804235&parent=EXTERNAL_SEARCH&sourceId=PROBLEM&id=845460.1&_afrWindowMode=0&_adf.ctrl-state=ublcblto3_4

• Budget Period

-Budget Period equals Appropriation Year

-Budget Date derives Budget Period

-Budget Period no longer specified on transactions

-Accounting Date derives Fiscal Year and Accounting Period

• Table Changes

• LEDGER_BUDG replaced with LEDGER_KK
• BUD_JRNL_HEADER & BUD_JRNL_LN replaced with KK_BUDGET_HDR and KK_BUDGET_LN

• Link to KK tables 

•KK_SOURCE_HDR

* Header information for source transactions to GL

* This table has the source transaction information such as Voucher ID, Journal ID, etc. Join KK_ACTIVITY_LOG to get the amounts and ChartFields
* SEQUENCE_NBR_9 shows seq # of Trans processed by specific  KK_PROC_INSTANCE
* FY & PRD are not populated

*Key fields include: KK_TRAN_ID and KK_TRAN_DT. Other fields include SOURCE_TRAN (AP_VOUCHER, GL_JOURNAL, POENC, PREENC,CM_TRNXTN) SEQUENCE_NBR, KK_PROCESS_STATUS, PO_ID, REQ_ID,VOUCHER_ID, JOURNAL_ID.

•KK_SOURCE_LN

Only transaction line that pass budget checking and impacts the ledger will be inserted into KK_SOURCE_LN table.

•KK_ACTIVITY_LOG

* Child table of KK_SOURCE_LN ;
* Detailed listing of each round of BP transactions posted against budget, identified by SEQUENCE_NBR 

* used to update LEDGER_KK when successful BP of a tran, both LEDGER_KK and KK_ACTIVITY_LOG table are updated at the same time  

* stores the details behind the LEDGER_KK balances

* Does not store the source transaction ID (i.e. voucher number, journal id, etc.).
* KK_SOURCE_HDR, KK_SOURCE_LN & ACTIVITY_LOG are populated by BP from the corresponding Header table (PO_HDR, REQ_HDR, VOUCHER, JRNL_HEADER). If the field BUDGET_HDR_STATUS is "V", then the data has been populated in the KK tables.

* join KK_SOURCE_HDR by the KK_TRAN_ID, KK_TRAN_DT, * AND KK_TRAN_LN
* stores actual FY + PRD values

* Key fields include: SEQNBR, KK_TRAN_ID, KK_TRAN_DT, KK_TRAN_LN,REFERENCED_BUDGET, BALANCING_LINE, LEDGER_GROUP, LEDGER (ORG_PR, ORG_EN, ORG_EX), FISCAL_YEAR, ACCOUNTING_PERIOD.

•KK_TRANS_LOG

Captures all transactions related to each pass of BP ;
1-1 match to JRNL_LN for budget_line_status <> ‘B’
CF at detailed level
capture documents changes between each passes of the BP, identified by SEQUENCE_NBR 
for PO & REQ, BU=XXXXX while BU=AGY_NO on SOURCE_HDR
entries removed when PO is deleted

•KK_BP_LOG

        Active BP instance, cleared when done, not cleared on abend.

•KK_LIQUIDATION

Details what/has been liquidated against encumbrances

KK_SOURCE_TRAN = PO_POENC / REQ_PREENC

KK_TRAN_ID = predecessor document transaction id (PO trans id that is referenced to PO Voucher)

KK_REFD_ID = referenced document

KK_POSTED_AMT = total source tran amt

MONETARY_AMOUNT = the remaining open balance.

•KK_REFERENCED

Stores the reference data between Requisition and Purchase Order or Purchase Order and Voucher

•KK_BUDGET_HDR

Stored the budget journal header information associated with commitment control budget journals.

Key fields include: BUSINESS_UNIT, JOURNAL_ID, JOURNAL_DATE,UNPOST_SEQ

•KK_BUDGET_LN

Child record of the KK_BUDGET_HDR table and store the ChartField and Amount information associated with commitment control budget journals

Key fields include: BUSINESS_UNIT, JOURNAL_ID, JOURNAL_DATE,UNPOST_SEQ, JOURNAL_LINE

•KK_EXCPTN_TBL

Details exceptions per trans

•KK_OVERRIDE_TBL

Captures any overrides made to the KK ledger(s)

•KK_TRAN_ID_TBL

Last KK_TRAN_ID

•LEDGER_KK

Budget ledgers that store balances from budget checking.

Updated along with KK_ACTIVITY_LOG

Key fields include: BUSINESS_UNIT, LEDGER, FISCAL_YEAR,ACCOUNTING_PERIOD, All ChartFields

•LEDGER_BUDG_KK

Captures budget by KK ledger

reconciled to the activity/transaction logs

Trans Rules:

* PO Vchr: 2 rows on Activity_Log: 

1. DETAIL_EX: REFERENCED_BUDGET='N'; KK_QUANTITY>0; ACTIVITY=0 
2. DETAIL_EN: REFERENCED_BUDGET='Y'; KK_QUANTITY=0;  ACTIVITY=MONETARY_AMOUNT; MONETARY_AMOUNT= offset of DETAIL_EX, 

*  update KK_PROCESS_STATUS field to 'I' on tables KK SOURCE HEADER, KK_SHDR_GLJRNL, add data to PS_KK_BP_LOG for the journals that are locked when trying to open (Header Unlock)  them 

 Archiving for Commitment Control

https://docs.oracle.com/cd/E39583_01/fscm92pbr0/eng/fscm/fscc/concept_UnderstandingArchivingforCommitmentControl-9f227d.html#DeliveredArchiveProceduresforCommitmentControl-9f227b__cm0152e22

Resetting AE Restart Point

----- SET AE RESTART POINT
SELECT to_char(ae_run_data), dump(to_char(ae_run_data))  
FROM PS_AERUNCONTROL WHERE PROCESS_INSTANCE =  3766254 

AE_PROG = 12 bytes
AE_SECTION = 8
AE_STEP =8 

-- this must be the step before Abend

UPDATE PS_AERUNCONTROL 
SET ae_run_data = to_clob(rpad('PROG',12,' ')||rpad('SECT',8,' ')||rpad('SETP',8,' ')||rpad('PROG',12,' ')||rpad('SECT',8,' ')||rpad('SETP',8,' '))
where   PROCESS_INSTANCE =  3766254

----- SET UP AE FOR RESTART: set up a test area for debugging, need to bring data from original db

 Using INTFAPAM as example:

1. state recs (select 'PS_'||AE_STATE_RECNAME from PSAEAPPLSTATE where  AE_APPLID=' INTFAPAM' ):

PS_AP_VCHR_AUD_AET

PS_INTFC_APAM0_AET

PS_INTFC_APAM1_AET

PS_INTFC_APAM2_AET

PS_INTFC_AP_AM_AET

2. temp recs: (select * from PS_AETEMPTBLMGR where PRocess_INSTANCE   = 3766254):

PS_INTFC_AP_AM_TAO

PS_VCHR_APAM_TAO1

::::

3. staging recs:

PS_PRE_AM_STG

PS_INTFC_PRE_AM

3. AE control:

PS_AERUNCONTROL

PS_AETEMPTBLMGR

PS_AEREQUESTTBL

4. PRCS:

PSPRCSPARMS

PSPRCSQUE

PSPRCSRQST

5. Message:

PS_MESSAGE_LOG

PS_MESSAGE_LOGPARM

analyze _trc to get the recs used

read PS Process Message Log

    

An easy way to review process message log, even if instance is deleted (not purged). 


create or replace type H_PRCS_MSG_LOG is object (msg_seq NUMBER, msg_dttm varchar2(50), msg_txt varchar2(2000));

create or replace type H_PRCS_MSG_DTL is table of H_PRCS_MSG_LOG;

CREATE OR REPLACE FUNCTION H_GET_MSG_DTL(prcsinstance integer) RETURN H_PRCS_MSG_DTL is
  idx NUMBER := 0;
  msgs varchar2(2000);
  parm varchar2(2000);
  sqls varchar2(2000); 
  l_msg_dtls H_PRCS_MSG_DTL:= H_PRCS_MSG_DTL();
  
   cursor csr is 
        select a.PROCESSINSTANCE, to_char(a.MSGLOG_DTTM, 'mm/dd/yy hh24:mi:ss') as MSG_DTTM, MESSAGE_SEQ, a.MESSAGE_SET_NBR, a.MESSAGE_NBR,b.MESSAGE_TEXT 
        from  ps_PMN_MSGLOG_VW a, PSMSGCATDEFN b  
        where a.MESSAGE_SET_NBR = b.MESSAGE_SET_NBR and a.MESSAGE_NBR=b.MESSAGE_NBR 
        and a.PROCESSINSTANCE=prcsinstance 
        union
        select a.PROCESSINSTANCE, to_char(a.MSGLOG_DTTM, 'mm/dd/yy hh24:mi:ss') as MSG_DTTM, MESSAGE_SEQ, a.MESSAGE_SET_NBR, a.MESSAGE_NBR,b.MESSAGE_TEXT 
        from  ps_PMN_MSGLOG_VW a, PSMSGCATDEFN b  
        where a.MESSAGE_SET_NBR = 0 and b.MESSAGE_SET_NBR =65 
        and a.MESSAGE_NBR= 0 and b.MESSAGE_NBR=30 
        and a.PROCESSINSTANCE=prcsinstance
        order by   MESSAGE_SEQ;

  BEGIN
  
  for zz in csr Loop
   
   idx:=idx+1;
   msgs:=zz.MESSAGE_TEXT;
  
  begin
     for x in (select PARM_SEQ, trim(MESSAGE_PARM) as MESSAGE_PARM from PS_MESSAGE_LOGPARM
                   where PROCESS_INSTANCE = zz.PROCESSINSTANCE
                   and MESSAGE_SEQ                = zz.MESSAGE_SEQ order by PARM_SEQ )
     loop
         -- escape quotes adding extra "'" 
         select replace(x.MESSAGE_PARM, '''', '''''') into parm from dual;
         select replace(msgs, '''', '''''') into msgs from dual;
     
         msgs := ''''||msgs ||''',''%' ||  x.PARM_SEQ ||''','''|| parm ||'''';
         sqls:='select replace('|| msgs ||') from dual';

          EXECUTE IMMEDIATE  sqls into msgs;          
      end loop;
      
      SELECT REGEXP_REPLACE(msgs, '%.','') into msgs from dual;
         
       l_msg_dtls.extend();
       
       l_msg_dtls(idx) := H_PRCS_MSG_LOG(zz.MESSAGE_SEQ, zz.MSG_DTTM, msgs); 
         
  exception
  WHEN OTHERS then
    null;    
  end;
  
 -- dbms_output.put_line(idx||':'|| l_msg_dtls(idx) );
  
  END LOOP;
  
  --dbms_output.put_line(idx);
  return l_msg_dtls;
  
END;
/

Restore purged PS Reports

PRCSYSPURGE deletes reports per the retention value:

SELECT PT_RETENTIONDAYS FROM PS_PRCSSYSTEM

Tables impacted:

• PSPRCSRQST
• PSPRCSQUE
• PSPRCSPARMS
• PS_MESSAGE_LOG
• PS_MESSAGE_LOGPARM
• PS_PRCSRQSTDIST
• PS_CDM_LIST
• PS_CDM_AUTH
• PS_CDM_FILE_LIST

Plug a file into PS's report depository - store a file into an existing folder and point url to it to access the file:

1. Choose an existing folder on web server, say /w/psft/psreports/AAA/20180119/928013 - there are some files in the folder, say AP_MATCH_111111. so Process Instance = 111111, CONTENT_ID=928013

2. Insert into 4 tables:

• insert into PS_PRCSRQSTDIST values (111111,'MY_OPRID',2)
• insert into PS_CDM_LIST values (111111, 928013,'AP_MATCH','Application Engine','$PS_CFG_HOME/appserv/prcs/$DB_NAME/log_output/AE_AP_MATCH_1889633','AP Matching',14,sysdate, sysdate, sysdate+7,5, '$REPORT_NODE', '$DB_NAME/20180119/928013', 9999999, 'Index.html',5,0,' ','GENERAL',0,' ',' ',' ')
• insert into PS_CDM_AUTH values (928013,111111,'MY_OPRID',2)
• insert into PS_CDM_FILE_LIST values (111111, 928013, 'AP_MATCH_1889633.log', 'LOG',2490,sysdate)

PS Rec Field Defn

Quick Access w/o App Designer:

SELECT VERSION, A.FIELDNAME, FIELDTYPE, LENGTH, DECIMALPOS, FORMAT, FORMATLENGTH, IMAGE_FMT, FORMATFAMILY, DISPFMTNAME, DEFCNTRYYR,IMEMODE,KBLAYOUT,OBJECTOWNERID, DEFRECNAME, DEFFIELDNAME, CURCTLFIELDNAME, USEEDIT, USEEDIT2, EDITTABLE, DEFGUICONTROL, SETCNTRLFLD, LABEL_ID, TIMEZONEUSE, TIMEZONEFIELDNAME, CURRCTLUSE, RELTMDTFIELDNAME, TO_CHAR(CAST((B.LASTUPDDTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF'), B.LASTUPDOPRID, B.FIELDNUM, A.FLDNOTUSED, A.AUXFLAGMASK, B.RECNAME 
FROM PSDBFIELD A, PSRECFIELD B 
WHERE B.RECNAME = 'OPR_DEF_TBL_GL' AND A.FIELDNAME = B.FIELDNAME AND B.SUBRECORD = 'N' 
ORDER BY B.RECNAME, B.FIELDNUM

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;