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 Only Table/Session/DB

Table
* make RO: ALTER TABLE table1 READ ONLY;

* check RO : select read_only from user_tables where table_name = 'XXX'

DB * make RO:  alter database open read only;

* check RO : select open_mode from v$database;

Session * make RO:  * set transaction read only;

Gibberish Snippets  

* clean up print file for reading: sed 's/\x00//g; s/\x0c/\x0a/g;' PRINT

check SQL/file to see process

1. 
   

SQL

To see what PS related process is doing to the DB, check MODULE & ACTION on v$sql.

Online: 
MODULE  = Component, ACTION = Page (run under PSAPPSRV)

Process:  
MODULE  = PSAE(clientinfo=User), ACTION = PS Process (PRCSNAME) (run under PSAESRV)

Program: 
MODULE  = PSAPPSRV/Toad/psae/sqr/PSAESRV(clientinfo=PRCS)

ACTION=NULL (these log into DB directly)

To see what PS Server process is running a Process Instance:

• pidof PSAESRV
• grep 'Session ID $PID'  SCHDLR_MMDD.LOG

File

To see what file is being used by PS:

1. lsof filename: get PID

2.

• grep $PID SCHDLR_MMYY.LOG: if PRCS jobs
• /proc/$PID/fd: if server process

    to see where file is accessed at

•  /proc/$PID/fdinfo: 

1. pos:    328724480 (offset); flags:  0100000 (read)
2. pos:    10072244224        ; flags:  0100001 (write)

Check Linux Email

source: linux-troubleshoot-outbound-email/

* check MTA:

ls -l /etc/alternatives/mta

If the command returns a link to “/usr/sbin/sendmail.postfix”, your system is configure to use postfix. 

If it returns a link to “/usr/sbin/sendmail.sendmail”, your system is configured to use sendmail.


* check Relay:

postfix:       grep ^relayhost /etc/postfix/main.cf
sendmail: grep ^DS /etc/mail/sendmail.cf


* mail queue:

mailq

* user mailbox: /var/spool/mail/$USER, /var/mail/$USER

How to tell where the file read offset by a process

source: https://unix.stackexchange.com/questions/34751/how-to-find-out-the-file-offset-of-an-opened-file

1. $ cat /proc/687705/fdinfo/36

pos:    26088
flags:  0100001

in case of symbolic links:

readlink /proc/$PID/fd/$N

2.

>lsof -o file1.st1
COMMAND   PID  USER   FD   TYPE DEVICE     OFFSET    NODE   NAME
process  24017 user1 18r   REG  253,3    0x14a85000 6330199 file1.st1

Oracle Inline Function for creating NEXT ID

PS commonly increments by 1 numerically for various transaction IDs. This ex sets a 5-char ID according to the algorithm. If current VID is:

* < 99999 - increment by 1
* = 99999 - A0000
* > A0000 - increment by 1 till A9999, then B0000
::::

-------------------------------------------------------------------------------------------------------- opt #1
CREATE OR REPLACE FUNCTION nxtvid 
(curvid varchar2) RETURN varchar2 IS
vid varchar2(5);
len integer;
BEGIN
 vid:='-----';
 len:=length(curvid) ;

 if len > 5
 then
    return vid;
  end if;

select decode(trim(TRANSLATE(curvid, '0123456789',' ')), null, 
             decode(curvid,'99999','A0000', lpad(curvid+1,5,'0')),
             decode(substr(curvid,2,4), '9999', chr(ascii(substr(curvid,1,1))+1)||'0000', substr(curvid,1,1)||lpad(substr(curvid,2,4)+1,4,'0'))
) into vid from dual;

RETURN vid;

exception
         WHEN OTHERS then   ::::::
END;
/

--------------------------------------------------------------------------- Opt #2
with function ni (curvid varchar2) return varchar2 as
vid varchar2(8);
begin
select decode(trim(TRANSLATE(curvid, '0123456789',' ')), null, 
             decode(curvid,'99999','A0000', lpad(curvid+1,5,'0')),
             decode(substr(curvid,2,4), '9999', chr(upper(ascii(substr(curvid,1,1)))+1)||'0000', substr(curvid,1,1)||lpad(substr(curvid,2,4)+1,4,'0')))
       into vid from dual;
return vid;            
end;            
select ni('E9999') from dual          

--------------------------------------------------------------------------------- opt #3
UPDATE /*+ WITH_PLSQL */ PS_TABLE A
set FIELD_VID =(
with function ni (curvid varchar2) return varchar2 as
vid varchar2(8);
begin
select decode(trim(TRANSLATE(curvid, '0123456789',' ')), null, 
             decode(curvid,'99999','A0000', lpad(curvid+1,5,'0')),
             decode(substr(curvid,2,4), '9999', chr(upper(ascii(substr(curvid,1,1)))+1)||'0000', substr(curvid,1,1)||lpad(substr(curvid,2,4)+1,4,'0')))
       into vid from dual;
return vid;            
end;            
select ni('X0022') from dual
)

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

Acquire with NOWAIT error and TRUNCATE command

A good example - https://community.oracle.com/thread/634676

Oracle parameter: select * from v$parameter where name = 'ddl_lock_timeout'  (in sec)

Note: truncate is a DDL

Ex: 
select dummy from t where dummy='X' for update nowait;
select dummy from t where dummy='X' for update wait 5;

find files with size

#!/bin/ksh
#******************************************************************************
#
#  find files with size range.(min, max) to delete for disk space
#
#  optins:
#  -------------------------------------------------------------
#  -a: for all folders under /a/psft
#  -r: root folder (default: /a/psft)
#  -s: sort by size (default: date)
#  -u: unit (K=KB, M=MB, G=GB, default: M)                
#
#  folders:
#  --------------------------------------------------------------
#  default to exclude following folders 
#     "/vol1/psft/aaa/*"         
#     "/vol2/psft/bbb/*"      
#     "/vol1/psft/Oracle/*"      
#  
#******************************************************************************
tmp=$0.$$

trap "rm $tmp 2>/dev/null" 0 1 2 3 15

#default options:
root=/a/psft; min=100; max=99999; tmp=0; unit="M"; all=0; sort=d;

while getopts :asr:u: args
do                     
     case $args in                                        

     a) all=1; 
        ;;                             

     s) sort=s; 
        ;;                             

     r) root="$OPTARG"; 
        ;;                             

     u) unit="$OPTARG"; 
        ;;                             

     ?) echo 'invalid option: '$OPTARG 
        echo 'syntax: ff -arsu [min] [max]'
        echo 'min, max: size'
        echo '-a: all folders'
        echo '-r: starting folder'
        echo '-s: sort by size'
        echo '-u: unit (K/M/G)'
     
        return 1;;                                      
     esac              
done

shift "$(($OPTIND -1))"

if [ $# -gt 2 ]; then
echo 'Incorrect # pf parameters'
   return 1
fi

if [ $# -eq 1 ]; then
   min=$1
else
   if [ $# -eq 2 ]; then
      min=$1
      max=$2      
   fi
fi

#****** fix unit 
case $unit in
    k|K) unit="k"; out="K"
       ;;                             
    m|M) unit="M"; out="M"
       ;;                             
    g|G) unit="G"
       ;;                             
    ?) echo 'Invalid unit: '$unit
       return 1;;                                      
esac              

#****** check numeric
int='^[0-9]+$'
dec='^0.[0-9]+$'

if ! [[ $min =~ $int ]] && ! [[ $min =~ $dec ]]; then
   echo "error: $min Not a number" >&2; return 1
fi

if ! [[ $max =~ $int ]] && ! [[ $max =~ $dec ]]; then
echo "error: $max Not a number" >&2; return 1
fi

#****** check range
if [[ "$(($min - $max ))" -gt 0 ]]; then
   tmp=$max; max=$min; min=$tmp;
fi 

#****** execute
echo "options: all=$all, min=$min, max=$max, unit=$unit, sort=$sort"

if [ $all -eq 1 ]; then
   find $root -type f -size +"$min"$unit -size -"$max"$unit -exec ls -lh --time-style='+%Y_%m_%d %H:%M' {} \; > $tmp
else   
   # exclude folders and files
   find $root -path /a/psft/lost+found -prune -o  -type f -size +"$min"$unit -size -"$max"$unit \
   -not -path "/vol1/psft/aaa/*" \
   -not -path "/vol2/psft/bbb/**" \
   -not -path "/vol1/psft/Oracle/*" \
   ! -name "stderr" \
   ! -name "stdout"  \
   -exec ls -lh --time-style='+%Y_%m_%d %H:%M' {} ";"  > $tmp
fi   

if [ $sort = 'd' ]; then
# sort by date
cat $tmp | sort -k6,7
else
   # sort by size, convert to unit
   awk -v unit="$unit" -v out="$out" '{ 
        if (unit == "k") { 
           if ( $5 ~ /G/) { 
              # convert GB to MB 
              size=$5;  
              sub(/G/,"",$size); 
              size=size*1024; 
              $5=size"M"; } 
              
           if ( $5 ~ /M/) { 
              # conver MB to KB
              size=$5;  
              sub(/M/,"",$size); 
              size=size*1024; 
              $5=size out; }
         }
       
        if (unit == "M") { 
           if ( $5 ~ /G/) { 
              # convert GB to MB 
              size=$5;  
              sub(/G/,"",$size); 
              size=size*1024; 
              $5=size out; 
           } 
         }    
           
         print $0; 
        } ' $tmp | sort -n -k5
fi        

Unix Integer & Float calculations

Source: https://unix.stackexchange.com/questions/40786/how-to-do-integer-float-calculations-in-bash-or-other-languages-frameworks

Q:  How to do integer & float calculations, in bash or other languages/frameworks?

$ printf %.10f\\n "$((10**9 * 20/7))e-9"   # many shells. Not mksh.
$ echo "$((20.0/7))"                       # (ksh93/zsh/yash, not bash)
$ awk "BEGIN {print (20+5)/2}"
$ zcalc
$ bc <<< 20+5/2
$ bc <<< "scale=4; (20+5)/2"
$ dc <<< "4 k 20 5 + 2 / p"
$ expr 20 + 5
$ calc 2 + 4
$ node -pe 20+5/2  # Uses the power of JavaScript, e.g. : node -pe 20+5/Math.PI
$ echo 20 5 2 / + p | dc 
$ echo 4 k 20 5 2 / + p | dc 
$ perl -E "say 20+5/2"
$ python -c "print 20+5/2"
$ python -c "print 20+5/2.0"
$ clisp -x "(+ 2 2)"
$ lua -e "print(20+5/2)"
$ php -r 'echo 20+5/2;'
$ ruby -e 'p 20+5/2'
$ ruby -e 'p 20+5/2.0'
$ guile -c '(display (+ 20 (/ 5 2)))'
$ guile -c '(display (+ 20 (/ 5 2.0)))'
$ slsh -e 'printf("%f",20+5/2)'
$ slsh -e 'printf("%f",20+5/2.0)'
$ tclsh <<< 'puts [expr 20+5/2]'
$ tclsh <<< 'puts [expr 20+5/2.0]'
$ sqlite3 <<< 'select 20+5/2;'
$ sqlite3 <<< 'select 20+5/2.0;'
$ echo 'select 1 + 1;' | sqlite3 
$ psql -tAc 'select 1+1'
$ R -q -e 'print(sd(rnorm(1000)))'
$ r -e 'cat(pi^2, "\n")'
$ r -e 'print(sum(1:100))'
$ smjs
$ jspl