Script for updating the vendor short name sequence after vendor data insertion to the peoplesoft FSCM finanicals database,

Script for updating the vendor short name sequence after vendor data insertion to the peoplesoft FSCM finanicals database,

CREATE TABLE SYSADM.scb_vnd_tmp_1 AS
SELECT setid,vendor_id,vndr_name_shrt_usr,
RANK() OVER (PARTITION BY vndr_name_shrt_usr ORDER BY vendor_id) AS VNDR_NAME_SEQ_NUM
FROM PS_VENDOR WHERE  SETID = 'SCMGS' AND VNDR_NAME_SEQ_NUM = 999;

UPDATE PS_VENDOR V SET
V.VENDOR_NAME_SHORT = ( SELECT DISTINCT vndr_name_shrt_usr||'-'||LPAD(TO_CHAR(VNDR_NAME_SEQ_NUM),3,'0') FROM SYSADM.scb_vnd_tmp T WHERE T.VENDOR_ID = V.VENDOR_ID )
,V.VNDR_NAME_SEQ_NUM = ( SELECT DISTINCT VNDR_NAME_SEQ_NUM FROM SYSADM.scb_vnd_tmp T WHERE T.VENDOR_ID = V.VENDOR_ID )
WHERE V.SETID = 'SHARE' AND V.VNDR_NAME_SEQ_NUM = 999;

Tree denoralization script for PeopleSoft trees --max 9 levels only

--> Script to denormalize the PeopleSoft tree with 9 levels max

 SELECT *
  FROM (
 SELECT DISTINCT T1.SETID
 ,T1.TREE_NAME
 ,T1.EFFDT
 ,T1.TREE_NODE LVL1
 ,' ' LVL2
 ,' ' LVL3
 ,' ' LVL4
 ,' ' LVL5
 ,' ' LVL6
 ,' ' LVL7
 ,' ' LVL8
 ,' ' LVL9
 ,LF.RANGE_FROM
 ,LF.RANGE_TO
  FROM PSTREENODE T1
  ,PSTREELEAF lf
 WHERE T1.SETID = LF.SETID
   AND T1.TREE_NAME = LF.TREE_NAME
   AND T1.EFFDT = LF.EFFDT
   AND T1.TREE_NODE_NUM = 1
   AND T1.TREE_NODE_NUM = LF.TREE_NODE_NUM
   AND T1.PARENT_NODE_NUM = 0
  UNION
 SELECT DISTINCT T1.SETID
 ,T1.TREE_NAME
 ,T1.EFFDT
 ,T1.TREE_NODE LVL1
 ,T2.TREE_NODE LVL2
 ,' ' LVL3
 ,' ' LVL4
 ,' ' LVL5
 ,' ' LVL6
 ,' ' LVL7
 ,' ' LVL8
 ,' ' LVL9
 ,LF.RANGE_FROM
 ,LF.RANGE_TO
  FROM PSTREENODE T1
  , PSTREENODE T2
  ,PSTREELEAF lf
 WHERE T1.SETID = T2.SETID
   AND T1.TREE_NAME = T2.TREE_NAME
   AND T1.EFFDT = T2.EFFDT
   AND T1.TREE_NODE_NUM = T2.PARENT_NODE_NUM
   AND T1.SETID = LF.SETID
   AND T1.TREE_NAME = LF.TREE_NAME
   AND T1.EFFDT = LF.EFFDT
   AND T2.TREE_NODE_NUM = LF.TREE_NODE_NUM
   AND T1.PARENT_NODE_NUM = 0
  UNION
 SELECT DISTINCT T1.SETID
 ,T1.TREE_NAME
 ,T1.EFFDT
 ,T1.TREE_NODE LVL1
 ,T2.TREE_NODE LVL2
 ,T3.TREE_NODE LVL3
 ,' ' LVL4
 ,' ' LVL5
 ,' ' LVL6
 ,' ' LVL7
 ,' ' LVL8
 ,' ' LVL9
 ,LF.RANGE_FROM
 ,LF.RANGE_TO
  FROM PSTREENODE T1
  , PSTREENODE T2
  , PSTREENODE T3
  ,PSTREELEAF lf
 WHERE T1.SETID = T2.SETID
   AND T1.TREE_NAME = T2.TREE_NAME
   AND T1.EFFDT = T2.EFFDT
   AND T1.TREE_NODE_NUM = T2.PARENT_NODE_NUM
   AND T1.SETID = T3.SETID
   AND T1.TREE_NAME = T3.TREE_NAME
   AND T1.EFFDT = T3.EFFDT
   AND T1.TREE_NODE_NUM = T2.PARENT_NODE_NUM
   AND T2.TREE_NODE_NUM = T3.PARENT_NODE_NUM
   AND T1.SETID = LF.SETID
   AND T1.TREE_NAME = LF.TREE_NAME
   AND T1.EFFDT = LF.EFFDT
   AND T3.TREE_NODE_NUM = LF.TREE_NODE_NUM
   AND T1.PARENT_NODE_NUM = 0
  UNION
 SELECT DISTINCT T1.SETID
 ,T1.TREE_NAME
 ,T1.EFFDT
 ,T1.TREE_NODE LVL1
 ,T2.TREE_NODE LVL2
 ,T3.TREE_NODE LVL3
 ,T4.TREE_NODE LVL4
 ,' ' LVL5
 ,' ' LVL6
 ,' ' LVL7
 ,' ' LVL8
 ,' ' LVL9
 ,LF.RANGE_FROM
 ,LF.RANGE_TO
  FROM PSTREENODE T1
  , PSTREENODE T2
  , PSTREENODE T3
  , PSTREENODE T4
  ,PSTREELEAF lf
 WHERE T1.SETID = T2.SETID
   AND T1.TREE_NAME = T2.TREE_NAME
   AND T1.EFFDT = T2.EFFDT
   AND T1.TREE_NODE_NUM = T2.PARENT_NODE_NUM
   AND T1.SETID = T3.SETID
   AND T1.TREE_NAME = T3.TREE_NAME
   AND T1.EFFDT = T3.EFFDT
   AND T1.SETID = T4.SETID
   AND T1.TREE_NAME = T4.TREE_NAME
   AND T1.EFFDT = T4.EFFDT
   AND T1.TREE_NODE_NUM = T2.PARENT_NODE_NUM
   AND T2.TREE_NODE_NUM = T3.PARENT_NODE_NUM
   AND T3.TREE_NODE_NUM = T4.PARENT_NODE_NUM
   AND T1.SETID = LF.SETID
   AND T1.TREE_NAME = LF.TREE_NAME
   AND T1.EFFDT = LF.EFFDT
   AND T4.TREE_NODE_NUM = LF.TREE_NODE_NUM
   AND T1.PARENT_NODE_NUM = 0
  UNION
 SELECT DISTINCT T1.SETID
 ,T1.TREE_NAME
 ,T1.EFFDT
 ,T1.TREE_NODE LVL1
 ,T2.TREE_NODE LVL2
 ,T3.TREE_NODE LVL3
 ,T4.TREE_NODE LVL4
 ,T5.TREE_NODE LVL5
 ,' ' LVL6
 ,' ' LVL7
 ,' ' LVL8
 ,' ' LVL9
 ,LF.RANGE_FROM
 ,LF.RANGE_TO
  FROM PSTREENODE T1
  , PSTREENODE T2
  , PSTREENODE T3
  , PSTREENODE T4
  , PSTREENODE T5
  ,PSTREELEAF lf
 WHERE T1.SETID = T2.SETID
   AND T1.TREE_NAME = T2.TREE_NAME
   AND T1.EFFDT = T2.EFFDT
   AND T1.TREE_NODE_NUM = T2.PARENT_NODE_NUM
   AND T1.SETID = T3.SETID
   AND T1.TREE_NAME = T3.TREE_NAME
   AND T1.EFFDT = T3.EFFDT
   AND T1.SETID = T4.SETID
   AND T1.TREE_NAME = T4.TREE_NAME
   AND T1.EFFDT = T4.EFFDT
   AND T1.SETID = T5.SETID
   AND T1.TREE_NAME = T5.TREE_NAME
   AND T1.EFFDT = T5.EFFDT
   AND T1.TREE_NODE_NUM = T2.PARENT_NODE_NUM
   AND T2.TREE_NODE_NUM = T3.PARENT_NODE_NUM
   AND T3.TREE_NODE_NUM = T4.PARENT_NODE_NUM
   AND T4.TREE_NODE_NUM = T5.PARENT_NODE_NUM
   AND T1.SETID = LF.SETID
   AND T1.TREE_NAME = LF.TREE_NAME
   AND T1.EFFDT = LF.EFFDT
   AND T5.TREE_NODE_NUM = LF.TREE_NODE_NUM
   AND T1.PARENT_NODE_NUM = 0
  UNION
 SELECT DISTINCT T1.SETID
 ,T1.TREE_NAME
 ,T1.EFFDT
 ,T1.TREE_NODE LVL1
 ,T2.TREE_NODE LVL2
 ,T3.TREE_NODE LVL3
 ,T4.TREE_NODE LVL4
 ,T5.TREE_NODE LVL5
 ,T6.TREE_NODE LVL6
 ,' ' LVL7
 ,' ' LVL8
 ,' ' LVL9
 ,LF.RANGE_FROM
 ,LF.RANGE_TO
  FROM PSTREENODE T1
  , PSTREENODE T2
  , PSTREENODE T3
  , PSTREENODE T4
  , PSTREENODE T5
  ,PSTREENODE T6
  ,PSTREELEAF lf
 WHERE T1.SETID = T2.SETID
   AND T1.TREE_NAME = T2.TREE_NAME
   AND T1.EFFDT = T2.EFFDT
   AND T1.TREE_NODE_NUM = T2.PARENT_NODE_NUM
   AND T1.SETID = T3.SETID
   AND T1.TREE_NAME = T3.TREE_NAME
   AND T1.EFFDT = T3.EFFDT
   AND T1.SETID = T4.SETID
   AND T1.TREE_NAME = T4.TREE_NAME
   AND T1.EFFDT = T4.EFFDT
   AND T1.SETID = T5.SETID
   AND T1.TREE_NAME = T5.TREE_NAME
   AND T1.EFFDT = T5.EFFDT
   AND T1.SETID = T6.SETID
   AND T1.TREE_NAME = T6.TREE_NAME
   AND T1.EFFDT = T6.EFFDT
   AND T1.TREE_NODE_NUM = T2.PARENT_NODE_NUM
   AND T2.TREE_NODE_NUM = T3.PARENT_NODE_NUM
   AND T3.TREE_NODE_NUM = T4.PARENT_NODE_NUM
   AND T4.TREE_NODE_NUM = T5.PARENT_NODE_NUM
   AND T5.TREE_NODE_NUM = T6.PARENT_NODE_NUM
   AND T1.SETID = LF.SETID
   AND T1.TREE_NAME = LF.TREE_NAME
   AND T1.EFFDT = LF.EFFDT
   AND T6.TREE_NODE_NUM = LF.TREE_NODE_NUM
   AND T1.PARENT_NODE_NUM = 0
  UNION
 SELECT DISTINCT T1.SETID
 ,T1.TREE_NAME
 ,T1.EFFDT
 ,T1.TREE_NODE LVL1
 ,T2.TREE_NODE LVL2
 ,T3.TREE_NODE LVL3
 ,T4.TREE_NODE LVL4
 ,T5.TREE_NODE LVL5
 ,T6.TREE_NODE LVL6
 ,T7.TREE_NODE LVL7
 ,' ' LVL8
 ,' ' LVL9
 ,LF.RANGE_FROM
 ,LF.RANGE_TO
  FROM PSTREENODE T1
  , PSTREENODE T2
  , PSTREENODE T3
  , PSTREENODE T4
  , PSTREENODE T5
  ,PSTREENODE T6
  ,PSTREENODE T7
  ,PSTREELEAF lf
 WHERE T1.SETID = T2.SETID
   AND T1.TREE_NAME = T2.TREE_NAME
   AND T1.EFFDT = T2.EFFDT
   AND T1.TREE_NODE_NUM = T2.PARENT_NODE_NUM
   AND T1.SETID = T3.SETID
   AND T1.TREE_NAME = T3.TREE_NAME
   AND T1.EFFDT = T3.EFFDT
   AND T1.SETID = T4.SETID
   AND T1.TREE_NAME = T4.TREE_NAME
   AND T1.EFFDT = T4.EFFDT
   AND T1.SETID = T5.SETID
   AND T1.TREE_NAME = T5.TREE_NAME
   AND T1.EFFDT = T5.EFFDT
   AND T1.SETID = T6.SETID
   AND T1.TREE_NAME = T6.TREE_NAME
   AND T1.EFFDT = T6.EFFDT
   AND T1.SETID = T7.SETID
   AND T1.TREE_NAME = T7.TREE_NAME
   AND T1.EFFDT = T7.EFFDT
   AND T1.TREE_NODE_NUM = T2.PARENT_NODE_NUM
   AND T2.TREE_NODE_NUM = T3.PARENT_NODE_NUM
   AND T3.TREE_NODE_NUM = T4.PARENT_NODE_NUM
   AND T4.TREE_NODE_NUM = T5.PARENT_NODE_NUM
   AND T5.TREE_NODE_NUM = T6.PARENT_NODE_NUM
   AND T6.TREE_NODE_NUM = T7.PARENT_NODE_NUM
   AND T1.SETID = LF.SETID
   AND T1.TREE_NAME = LF.TREE_NAME
   AND T1.EFFDT = LF.EFFDT
   AND T7.TREE_NODE_NUM = LF.TREE_NODE_NUM
   AND T1.PARENT_NODE_NUM = 0
  UNION
 SELECT DISTINCT T1.SETID
 ,T1.TREE_NAME
 ,T1.EFFDT
 ,T1.TREE_NODE LVL1
 ,T2.TREE_NODE LVL2
 ,T3.TREE_NODE LVL3
 ,T4.TREE_NODE LVL4
 ,T5.TREE_NODE LVL5
 ,T6.TREE_NODE LVL6
 ,T7.TREE_NODE LVL7
 ,T8.TREE_NODE LVL8
 ,' ' LVL9
 ,LF.RANGE_FROM
 ,LF.RANGE_TO
  FROM PSTREENODE T1
  ,PSTREENODE T2
  ,PSTREENODE T3
  ,PSTREENODE T4
  ,PSTREENODE T5
  ,PSTREENODE T6
  ,PSTREENODE T7
  ,PSTREENODE T8
  ,PSTREELEAF lf
 WHERE T1.SETID = T2.SETID
   AND T1.TREE_NAME = T2.TREE_NAME
   AND T1.EFFDT = T2.EFFDT
   AND T1.TREE_NODE_NUM = T2.PARENT_NODE_NUM
   AND T1.SETID = T3.SETID
   AND T1.TREE_NAME = T3.TREE_NAME
   AND T1.EFFDT = T3.EFFDT
   AND T1.SETID = T4.SETID
   AND T1.TREE_NAME = T4.TREE_NAME
   AND T1.EFFDT = T4.EFFDT
   AND T1.SETID = T5.SETID
   AND T1.TREE_NAME = T5.TREE_NAME
   AND T1.EFFDT = T5.EFFDT
   AND T1.SETID = T6.SETID
   AND T1.TREE_NAME = T6.TREE_NAME
   AND T1.EFFDT = T6.EFFDT
   AND T1.SETID = T7.SETID
   AND T1.TREE_NAME = T7.TREE_NAME
   AND T1.EFFDT = T7.EFFDT
   AND T1.SETID = T8.SETID
   AND T1.TREE_NAME = T8.TREE_NAME
   AND T1.EFFDT = T8.EFFDT
   AND T1.TREE_NODE_NUM = T2.PARENT_NODE_NUM
   AND T2.TREE_NODE_NUM = T3.PARENT_NODE_NUM
   AND T3.TREE_NODE_NUM = T4.PARENT_NODE_NUM
   AND T4.TREE_NODE_NUM = T5.PARENT_NODE_NUM
   AND T5.TREE_NODE_NUM = T6.PARENT_NODE_NUM
   AND T6.TREE_NODE_NUM = T7.PARENT_NODE_NUM
   AND T7.TREE_NODE_NUM = T8.PARENT_NODE_NUM
   AND T1.SETID = LF.SETID
   AND T1.TREE_NAME = LF.TREE_NAME
   AND T1.EFFDT = LF.EFFDT
   AND T8.TREE_NODE_NUM = LF.TREE_NODE_NUM
   AND T1.PARENT_NODE_NUM = 0
  UNION
 SELECT DISTINCT T1.SETID
 ,T1.TREE_NAME
 ,T1.EFFDT
 ,T1.TREE_NODE LVL1
 ,T2.TREE_NODE LVL2
 ,T3.TREE_NODE LVL3
 ,T4.TREE_NODE LVL4
 ,T5.TREE_NODE LVL5
 ,T6.TREE_NODE LVL6
 ,T7.TREE_NODE LVL7
 ,T8.TREE_NODE LVL8
 ,T9.TREE_NODE LVL9
 ,LF.RANGE_FROM
 ,LF.RANGE_TO
  FROM PSTREENODE T1
  ,PSTREENODE T2
  ,PSTREENODE T3
  ,PSTREENODE T4
  ,PSTREENODE T5
  ,PSTREENODE T6
  ,PSTREENODE T7
  ,PSTREENODE T8
  ,PSTREENODE T9
  ,PSTREELEAF lf
 WHERE T1.SETID = T2.SETID
   AND T1.TREE_NAME = T2.TREE_NAME
   AND T1.EFFDT = T2.EFFDT
   AND T1.TREE_NODE_NUM = T2.PARENT_NODE_NUM
   AND T1.SETID = T3.SETID
   AND T1.TREE_NAME = T3.TREE_NAME
   AND T1.EFFDT = T3.EFFDT
   AND T1.SETID = T4.SETID
   AND T1.TREE_NAME = T4.TREE_NAME
   AND T1.EFFDT = T4.EFFDT
   AND T1.SETID = T5.SETID
   AND T1.TREE_NAME = T5.TREE_NAME
   AND T1.EFFDT = T5.EFFDT
   AND T1.SETID = T6.SETID
   AND T1.TREE_NAME = T6.TREE_NAME
   AND T1.EFFDT = T6.EFFDT
   AND T1.SETID = T7.SETID
   AND T1.TREE_NAME = T7.TREE_NAME
   AND T1.EFFDT = T7.EFFDT
   AND T1.SETID = T8.SETID
   AND T1.TREE_NAME = T8.TREE_NAME
   AND T1.EFFDT = T8.EFFDT
   AND T1.SETID = T9.SETID
   AND T1.TREE_NAME = T9.TREE_NAME
   AND T1.EFFDT = T9.EFFDT
   AND T1.TREE_NODE_NUM = T2.PARENT_NODE_NUM
   AND T2.TREE_NODE_NUM = T3.PARENT_NODE_NUM
   AND T3.TREE_NODE_NUM = T4.PARENT_NODE_NUM
   AND T4.TREE_NODE_NUM = T5.PARENT_NODE_NUM
   AND T5.TREE_NODE_NUM = T6.PARENT_NODE_NUM
   AND T6.TREE_NODE_NUM = T7.PARENT_NODE_NUM
   AND T7.TREE_NODE_NUM = T8.PARENT_NODE_NUM
   AND T8.TREE_NODE_NUM = T9.PARENT_NODE_NUM
   AND T1.SETID = LF.SETID
   AND T1.TREE_NAME = LF.TREE_NAME
   AND T1.EFFDT = LF.EFFDT
   AND T9.TREE_NODE_NUM = LF.TREE_NODE_NUM
   AND T1.PARENT_NODE_NUM = 0 ) DNM
 WHERE DNM.TREE_NAME = 'ACCOUNT_GLOBAL'
   AND DNM.SETID = 'SHARE'
   AND DNM.EFFDT = '01-Jan-2008'
  

SQL to list out the records and fields in a PeopleSoft pages

/* Formatted on 2014/03/12 14:28 (Formatter Plus v4.8.7) */
SELECT a.pnlname, a.recname, a.fieldname, a.lbltext, c.fieldtype, c.LENGTH,
       (SELECT CASE
                  WHEN BITAND (m.useedit, 256) > 0
                     THEN 'YES'
                  ELSE 'NO'
               END AS required_field
          FROM PSRECFIELD m, PSDBFIELD n
         WHERE m.recname = b.recname
           AND m.fieldname = n.fieldname
           AND m.fieldname = c.fieldname) AS req_field,
       (SELECT CASE
                  WHEN EXISTS (
                         SELECT 'X'
                           FROM all_ind_columns
                          WHERE table_name = 'PS_' || a.recname
                            AND column_name = a.fieldname)
                     THEN 'YES'
                  ELSE 'NO'
               END AS key_field
          FROM PS_INSTALLATION) AS keys_field
  FROM PSPNLFIELD a, PSRECFIELDDB b, PSDBFIELD c
 WHERE a.recname = b.recname
   AND a.fieldname = b.fieldname
   AND b.fieldname = c.fieldname
   AND a.pnlname IN ('CS_CNTRCT_HDR','CNTRCT_HDR_SBP')
   ORDER BY a.recname

Multibyte characters related to peoplesoft in oracle database, can be used in multilingual databases in peoplesoft

Below codes will be used to convert the data in the non unicode database to the unicode database, as it oracle will not allow us to insert Multibyte chars.

--script to identify the difference,
SELECT descr FROM PS_PRODUCT_TBL
WHERE LENGTH(asciistr(descr))!=LENGTH(descr)
ORDER BY descr;

--script to fix the multibyte characters,
SELECT DUMP(a,1016), a FROM (
SELECT REGEXP_REPLACE (
          CONVERT (
             '3735844533120%$03  ',
             'US7ASCII',
             'WE8ISO8859P1'),
          '[^!@/\.,;:<>#$%&()_=[:alnum:][:blank:]]') a
  FROM DUAL);

Oracle long running operations /sql in the database

SELECT SQL.HASH_VALUE ,SQL.ADDRESS ,nvl(ses.username,'ORACLE PROC')||' ('||ses.sid||')' USERNAME,
       SID,
       MACHINE,
       REPLACE(SQL.SQL_TEXT,CHR(10),'') STMT,
      ltrim(to_char(floor(SES.LAST_CALL_ET/3600), '09')) || ':'
       || ltrim(to_char(floor(mod(SES.LAST_CALL_ET, 3600)/60), '09')) || ':'
       || ltrim(to_char(mod(SES.LAST_CALL_ET, 60), '09'))    RUNT
  FROM V$SESSION SES,
       V$SQLtext_with_newlines SQL
 where SES.STATUS = 'ACTIVE'
   and SES.USERNAME is not null
   and SES.SQL_ADDRESS    = SQL.ADDRESS
   and SES.SQL_HASH_VALUE = SQL.HASH_VALUE
   and Ses.AUDSID <> userenv('SESSIONID')
 order by runt desc, 1,sql.piece;

Script to list out long running operations or sql executions in Oracle...


SELECT s.sid,
       s.serial#,
       S.username,
       s.OSUser,
       s.machine,
       ROUND(sl.elapsed_seconds/60) || ':' || MOD(sl.elapsed_seconds,60) elapsed,
       ROUND(sl.time_remaining/60) || ':' || MOD(sl.time_remaining,60) remaining,
       ROUND(sl.sofar/sl.totalwork*100, 2) progress_pct
FROM   v$session s,
       v$session_longops sl
WHERE  s.sid     = sl.sid
AND    s.serial# = sl.serial#
AND TOTALWORK != 0 AND SOFAR <> TOTALWORK
order by 1;

Oracle PL/SQL script for reading a Long data column,

CREATE OR REPLACE FUNCTION  getlong( p_tname IN VARCHAR2,p_cname IN VARCHAR2,p_rowid IN ROWID ) RETURN VARCHAR2
 AS
      l_cursor    INTEGER DEFAULT DBMS_SQL.OPEN_CURSOR;
      l_n         NUMBER;
       l_long_val  VARCHAR2(4000);
       l_buflen    NUMBER := 4000;
     l_long_len  NUMBER;
       l_curpos    NUMBER := 0;
   BEGIN
       DBMS_SQL.PARSE( l_cursor,
                      'select ' || p_cname || ' from ' || p_tname ||
                                                          ' where rowid = :x',
                       dbms_sql.native );
       DBMS_SQL.BIND_VARIABLE( l_cursor, ':x', p_rowid );

       DBMS_SQL.DEFINE_COLUMN_LONG(l_cursor, 1);
       l_n := DBMS_SQL.EXECUTE(l_cursor);

       IF (DBMS_SQL.FETCH_ROWS(l_cursor)>0)
       THEN
          DBMS_SQL.COLUMN_VALUE_LONG(l_cursor, 1, l_buflen, l_curpos ,
                                     l_long_val, l_long_len );
      END IF;
      DBMS_SQL.CLOSE_CURSOR(l_cursor);
     RETURN l_long_val;
END getlong;
/

PeopleSoft IB message transaction table names,

/* Formatted on 2014/04/30 09:41 (Formatter Plus v4.8.7) */
SELECT ibtransactionid, ib_segmentindex, ib_operationname, ibpubtransactid,
       actionname, pubnode, queuename, queueseqid, subqueue, trxtype,
       versionname, createdttm, publishtimestamp, actionowner, conversationid,
       inreplytoid, isrequest, routingdefnname, subconstatus, statusstring,
       lastupddttm, retrycount, machinename, slaveprocessname, ib_slavequeued,
       tuxdomain, processid
  FROM psapmsgsubcon

/* Formatted on 2014/04/30 09:42 (Formatter Plus v4.8.7) */
SELECT ibtransactionid, ib_segmentindex, subnode, ibpubtransactid,
       ib_sendpubid, pubnode, queuename, queueseqid, subqueue, createdttm,
       publishtimestamp, trxtype, ib_operationname, versionname,
       conversationid, canonicaltrsfrmid, inreplytoid, isrequest,
       routingdefnname, pubconstatus, statusstring, lastupddttm, retrycount,
       machinename, slaveprocessname, ib_slavequeued, tuxdomain, processid,
       nrid
  FROM psapmsgpubcon

Script to ask email ids in PeopleSoft FSCM financials module, to be used in test regions

UPDATE PSOPRDEFN SET EMAILID='a@a.a'

UPDATE PSUSEREMAIL SET EMAILID='a@a.a'

UPDATE PS_ROLEXLATOPR SET EMAILID='a@a.a'

UPDATE  PS_SAC_EMC_PND_HDR SET SENT_TO='a@a.a'

UPDATE  PS_VENDOR_ADDR SET EMAILID='a@a.a'

UPDATE PS_VENDOR_CNTCT SET EMAILID='a@a.a'

TRUNCATE TABLE PS_PO_EMAIL_QUEUE

Query to identify a table size in Oracle

SELECT segment_name,segment_type,bytes/1024/1024 MB
 FROM dba_segments
 WHERE segment_type='TABLE' AND segment_name='PS_BI_EXTRCT_LINE';

-->provides navigation path of a component
SELECT a.portal_name,f.portal_label AS parent5_folder,e.portal_label AS parent4_folder,d.portal_label AS parent3_folder,c.portal_label AS parent2_folder,
b.portal_label AS parent_folder,a.portal_label AS component,A.portal_uri_seg2 FROM PSPRSMDEFN a
LEFT JOIN PSPRSMDEFN b ON b.portal_name = a.portal_name AND b.portal_objname = a.portal_prntobjname LEFT JOIN PSPRSMDEFN c ON c.portal_name = b.portal_name
AND c.portal_objname = b.portal_prntobjname LEFT JOIN PSPRSMDEFN d ON d.portal_name = c.portal_name AND d.portal_objname = c.portal_prntobjname
LEFT JOIN PSPRSMDEFN e ON e.portal_name = d.portal_name AND e.portal_objname = d.portal_prntobjname LEFT JOIN PSPRSMDEFN f ON F.portal_name = E.portal_name
AND F.portal_objname = E.portal_prntobjname WHERE a.portal_reftype = 'C'   AND a.portal_uri_seg2 LIKE '%CNTRCT_MSTR%'--substitute component name here

-->provides all navigation paths of a folder
SELECT a.portal_name,f.portal_label AS parent5_folder,e.portal_label AS parent4_folder,d.portal_label AS parent3_folder,c.portal_label AS parent2_folder,
b.portal_label AS parent_folder,a.portal_label AS component,A.portal_uri_seg2 FROM PSPRSMDEFN a
LEFT JOIN PSPRSMDEFN b ON b.portal_name = a.portal_name AND b.portal_objname = a.portal_prntobjname LEFT JOIN PSPRSMDEFN c ON c.portal_name = b.portal_name
AND c.portal_objname = b.portal_prntobjname LEFT JOIN PSPRSMDEFN d ON d.portal_name = c.portal_name AND d.portal_objname = c.portal_prntobjname
LEFT JOIN PSPRSMDEFN e ON e.portal_name = d.portal_name AND e.portal_objname = d.portal_prntobjname LEFT JOIN PSPRSMDEFN f ON F.portal_name = E.portal_name
AND F.portal_objname = E.portal_prntobjname WHERE a.portal_reftype = 'C'   AND a.portal_uri_seg1 LIKE '%EXTRACT%'--substitute folder name here

-->provides navigation path of a folder
SELECT a.portal_name,f.portal_label AS parent5_folder,e.portal_label AS parent4_folder,d.portal_label AS parent3_folder,c.portal_label AS parent2_folder,
b.portal_label AS parent_folder,a.portal_label AS component,A.portal_uri_seg2 FROM PSPRSMDEFN a
LEFT JOIN PSPRSMDEFN b ON b.portal_name = a.portal_name AND b.portal_objname = a.portal_prntobjname LEFT JOIN PSPRSMDEFN c ON c.portal_name = b.portal_name
AND c.portal_objname = b.portal_prntobjname LEFT JOIN PSPRSMDEFN d ON d.portal_name = c.portal_name AND d.portal_objname = c.portal_prntobjname
LEFT JOIN PSPRSMDEFN e ON e.portal_name = d.portal_name AND e.portal_objname = d.portal_prntobjname LEFT JOIN PSPRSMDEFN f ON F.portal_name = E.portal_name
AND F.portal_objname = E.portal_prntobjname WHERE a.portal_reftype = 'C'   AND a.portal_prntobjname LIKE '%DAILY_PROCESSING1%'--substitute folder name here

--provides PIA navigation path of a Component/folder
SELECT a.portal_name,f.portal_label AS parent5_folder,e.portal_label AS parent4_folder,d.portal_label AS parent3_folder,c.portal_label AS parent2_folder,
b.portal_label AS parent_folder,a.portal_label AS component,A.portal_uri_seg2 FROM PSPRSMDEFN a
LEFT JOIN PSPRSMDEFN b ON b.portal_name = a.portal_name AND b.portal_objname = a.portal_prntobjname LEFT JOIN PSPRSMDEFN c ON c.portal_name = b.portal_name
AND c.portal_objname = b.portal_prntobjname LEFT JOIN PSPRSMDEFN d ON d.portal_name = c.portal_name AND d.portal_objname = c.portal_prntobjname
LEFT JOIN PSPRSMDEFN e ON e.portal_name = d.portal_name AND e.portal_objname = d.portal_prntobjname LEFT JOIN PSPRSMDEFN f ON F.portal_name = E.portal_name
AND F.portal_objname = E.portal_prntobjname WHERE a.portal_reftype = 'C'   AND a.portal_uri_seg1 IN ('COMPONENET_NAME') ORDER BY 5,6