PeopleSoft Helper

Saturday, May 24, 2014

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

Thursday, April 25, 2013

Sql to find out the cont ref based out of the component name

SELECT PORTAL_NAME,portal_reftype,PORTAL_URLTEXT FROM PSPRSMDEFN WHERE PORTAL_NAME = 'EMPLOYEE' AND PORTAL_OBJNAME like '%RUN_BI_PRNTEXT%'

Best Regards

Aravind Kumar R

System Analyst/Peoplesoft Practice
Birlasoft • 36, Vijayaraghava Road , T. Nagar, Chennai 600 017 (INDIA)
Cell: +91 9566750166

************************************************************************************************************************************************************************************
"This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to which they are addressed. If you have received this email in error please notify the mail.admin@birlasoft.com immediately without opening the mail and attachment . Please note that any views or opinions presented in this email are solely those of the author and do not necessarily represent those of the company. Finally, the recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email
************************************************************************************************************************************************************************************

Thursday, April 18, 2013

PeopleSoft - PeopleCode for archiving the file in PSNT server using Application Engine

PeopleSoft - PeopleCode for archiving the file in PSNT server using Application Engine and Exec command

&TargetFilePath = "D:\FDEV9\Interface\Backup\Alliance\";

SQLExec("SELECT descr254,substr(descr254,instr(descr254,'\',-1)+1,length(descr254)) FROM PS_Z_AR_FILE_TMP WHERE PROCESS_INSTANCE = :1 AND FILE_SEQ = :2 AND ARCHIVE_PROCESS = 'A'", Z_AR_ALNCE_AET.PROCESS_INSTANCE.Value, Z_AR_ALNCE_AET.FILE_SEQ.Value, &SourceFileFull, &SrsFileName);

If Not None(&SourceFileFull) Then

&ArchiveResult = 1;

&TargetFile = &TargetFilePath | &SrsFileName;

&ArchiveResult = PutAttachment("\\10.0.24.231\", &TargetFile, &SourceFileFull);

rem MessageBox(0, "", 0, 0, "&ExecCommand-" | &ExecCommand);

try

&ArchiveResult = Exec(&ExecCommand, %Exec_Asynchronous + %FilePath_Absolute);

catch Exception &ex

rem Handle the exception;

MessageBox(0, "", 0, 0, &ex.ToString());

end-try;

If &ArchiveResult = 0 Then

MessageBox(0, "", 0, 0, "File archive failed for - " | &TargetFile);

Else

MessageBox(0, "", 0, 0, "File archived successfully to folder- " | &TargetFile);

End-If;

Else

MessageBox(0, "", 0, 0, "File not archived DUE TO ERROROS");

End-If;

************************************************************************************************************************************************************************************

Tuesday, April 16, 2013

SQL Check if a user has access to a PeopleSoft portal content reference/folder - PIA

SQL to check if a user has access to a PeopleSoft portal content reference/folder -PIA

SELECT * FROM (

SELECT A.*, C.ROLEUSER, C.ROLENAME

FROM PSPRSMPERM A, PSROLECLASS B, PSROLEUSER C

WHERE A.PORTAL_PERMTYPE = 'P' AND A.PORTAL_PERMNAME= B.CLASSID AND B.ROLENAME = C.ROLENAME

UNION

SELECT A.*, C.ROLEUSER, C.ROLENAME

FROM PSPRSMPERM A, PSROLEUSER C

WHERE A.PORTAL_PERMTYPE = 'R' AND A.PORTAL_PERMNAME = C.ROLENAME) X

WHERE X.PORTAL_NAME = 'EMPLOYEE'

and X.PORTAL_OBJNAME like '%CUSTOMER%'

AND X.ROLEUSER = 'aravindkumar'

Best Regards

Aravind Kumar R

System Analyst/Peoplesoft Practice
Birlasoft • 36, Vijayaraghava Road , T. Nagar, Chennai 600 017 (INDIA)
Cell: +91 9566750166

PeopleSoft FSCM important tables

PeopleSoft finance and supply chain management important tables list

SELECT * FROM PS_JRNL_LN_BUDG

SELECT * FROM PS_LEDGER_BUDG

SELECT * FROM PS_COMBO_DATA_TBL

SELECT * FROM PS_COMBO_DATA_BUDG

SELECT * FROM PS_REQ_HDR

SELECT * FROM PS_REQ_LINE

SELECT * FROM PS_REQ_LINE_SHIP

SELECT * FROM PS_REQ_LINE_DISTRIB

SELECT * FROM PS_REQ_LINE_ACCTG

SELECT * FROM PS_PO_HDR

SELECT * FROM PS_PO_LINE

SELECT * FROM PS_PO_LINE_SHIP

SELECT * FROM PS_PO_LINE_DISTRIB

SELECT * FROM PS_PO_LINE_ACCTG

SELECT * FROM PS_RECV_HDR

SELECT * FROM PS_RECV_LN

SELECT * FROM PS_RECV_LN_SHIP

SELECT * FROM PS_RECV_LN_DISTRIB

SELECT * FROM PS_VOUCHER

SELECT * FROM PS_VOUCHER_LINE

SELECT * FROM PS_DISTRIB_LINE

SELECT * FROM PS_VCHR_ACCTG_LINE

SELECT * FROM PS_PO_LINE_MATCHED

SELECT * FROM PS_PYMNT_ADVICE

SELECT * FROM PS_PYMNT_VCHR_XREF

SELECT * FROM PS_PAYMENT_TBL

SELECT * FROM PS_VENDOR

SELECT * FROM PS_VENDOR_LOC

SELECT * FROM PS_VENDOR_PAY

SELECT * FROM PS_VNDR_LOC_ADDR

SELECT * FROM PS_VENDOR_WTHD

SELECT * FROM PS_VENDOR_ADDR

--AR

SELECT * FROM PS_AR_PAYMENT_EC

SELECT * FROM PS_AR_IDCUST_EC

SELECT * FROM PS_AR_IDITEM_EC

SELECT * FROM PS_PAYMENT_ID_CUST

SELECT * FROM PS_PAYMENT_ID_ITEM

SELECT * FROM PS_DEPOSIT_CONTROL

SELECT * FROM PS_PAYMENT

Best Regards

Aravind Kumar R

System Analyst/Peoplesoft Practice
Birlasoft • 36, Vijayaraghava Road , T. Nagar, Chennai 600 017 (INDIA)
Cell: +91 9566750166

PeopleCode for inserting the AR pending item interface using Component Interface

Peoplesoft -- PeopleCode for inserting the AR pending item interface using Component Interface

Local File &fileLog;

Local ApiObject &oSession, &oDcPenditemCi;

Local ApiObject &oPendingItemCollection, &oPendingItem;

Local ApiObject &oPendingDstCollection, &oPendingDst;

Local ApiObject &oPendingVatCollection, &oPendingVat;

Function errorHandler()

Local ApiObject &oPSMessageCollection, &oPSMessage;

Local number &i;

Local string &sErrMsgSetNum, &sErrMsgNum, &sErrMsgText, &sErrType;

&oPSMessageCollection = &oSession.PSMessages;

For &i = 1 To &oPSMessageCollection.Count

&oPSMessage = &oPSMessageCollection.Item(&i);

&sErrMsgSetNum = &oPSMessage.MessageSetNumber;

&sErrMsgNum = &oPSMessage.MessageNumber;

&sErrMsgText = &oPSMessage.Text;

&fileLog.WriteLine(&sErrType | " (" | &sErrMsgSetNum | "," | &sErrMsgNum | ") - " | &sErrMsgText);

End-For;

rem ***** Delete the Messages from the collection *****;

&oPSMessageCollection.DeleteAll();

End-Function;

SQLExec("select GROUP_BU,GROUP_ID,OPRID,ASSN_OPRID,GROUP_TYPE,POST_ACTION,CONTROL_AMT,CONTROL_CNT,ORIGIN_ID,%dateout(RECEIVED_DT),%dateout(ENTRY_DT), %dateout(ACCOUNTING_DT),CONTROL_CURRENCY,FORMAT_CURRENCY,ALLOW_DST FROM PS_Z_AR_GCTL_TMP WHERE PROCESS_INSTANCE = :1 AND Z_AR_STATUS ='P'", Z_AR_ALNCE_AET.PROCESS_INSTANCE.Value, &GROUP_BU, &GROUP_ID, &OPRID, &ASSN_OPRID, &GROUP_TYPE, &POST_ACTION, &CONTROL_AMT, &CONTROL_CNT, &ORIGIN_ID, &RECEIVED_DT, &ENTRY_DT, &ACCOUNTING_DT, &CONTROL_CURRENCY, &FORMAT_CURRENCY, &ALLOW_DST);

try

rem ***** Set the Log File *****;

&fileLog = GetFile("C:\temp\DC_PENDITEM_CI.log", "w", "a", %FilePath_Absolute);

&fileLog.WriteLine("Begin");

rem ***** Get current PeopleSoft Session *****;

&oSession = %Session;

rem ***** Set the PeopleSoft Session Error Message Mode *****;

rem ***** 0 - None *****;

rem ***** 1 - PSMessage Collection only (default) *****;

rem ***** 2 - Message Box only *****;

rem ***** 3 - Both collection and message box *****;

&oSession.PSMessagesMode = 1;

rem ***** Get the Component Interface *****;

&oDcPenditemCi = &oSession.GetCompIntfc(CompIntfc.DC_PENDITEM_CI);

If &oDcPenditemCi = Null Then

errorHandler();

throw CreateException(0, 0, "GetCompIntfc failed for pending item ");

End-If;

rem ***** Set the Component Interface Mode *****;

&oDcPenditemCi.InteractiveMode = False;

&oDcPenditemCi.GetHistoryItems = True;

&oDcPenditemCi.EditHistoryItems = False;

rem ***** Set Component Interface Get/Create Keys *****;

&oDcPenditemCi.GROUP_BU = &GROUP_BU;

&oDcPenditemCi.GROUP_ID = "NEXT";

rem ***** Execute Create ******;

If Not &oDcPenditemCi.Create() Then;

rem ***** Unable to Create Component Interface for the Add keys provided. *****;

errorHandler();

throw CreateException(0, 0, "Create pending item failed ");

End-If;

rem ***** Begin: Get/Set Component Interface Properties *****;

rem ***** Get/Set Level 0 Field Properties *****;

&oDcPenditemCi.GROUP_BU = &GROUP_BU;

&oDcPenditemCi.GROUP_ID = "NEXT";

&oDcPenditemCi.OPRID = &OPRID;

&oDcPenditemCi.ASSN_OPRID = &ASSN_OPRID;

&oDcPenditemCi.GROUP_TYPE = "B";

rem &oDcPenditemCi.GROUP_SEQ_NUM = 17;

&oDcPenditemCi.POST_ACTION = "L";

&oDcPenditemCi.CONTROL_AMT = &CONTROL_AMT;

&oDcPenditemCi.CONTROL_CNT = &CONTROL_CNT;

&oDcPenditemCi.ORIGIN_ID = &ORIGIN_ID;

&oDcPenditemCi.RECEIVED_DT = &RECEIVED_DT;

&oDcPenditemCi.ENTRY_DT = &ENTRY_DT;

&oDcPenditemCi.ACCOUNTING_DT = &ACCOUNTING_DT;

rem &oDcPenditemCi.DST_BAL_STATUS = "I";

&oDcPenditemCi.FORMAT_CURRENCY = &FORMAT_CURRENCY;

&oDcPenditemCi.ALLOW_DST = &ALLOW_DST;

rem ***** Set/Get PENDING_ITEM Collection Field Properties -- Parent: PS_ROOT Collection *****;

&oPendingItemCollection = &oDcPenditemCi.PENDING_ITEM;

Local integer &i126;

&i126 = 1;

&SelPndRecord = CreateRecord(Record.Z_AR_PITM_TMP);

&SQL = CreateSQL("%Selectall(:1) WHERE PROCESS_INSTANCE = :2 AND Z_AR_STATUS ='P'", Record.Z_AR_PITM_TMP, Z_AR_ALNCE_AET.PROCESS_INSTANCE.Value);

While &SQL.fetch(&SelPndRecord)

&oPendingItem = &oPendingItemCollection.InsertItem(&i126);

REM MessageBox(0, "", 0, 0, &SelPndRecord.CUST_ID.VALUE);

&oPendingItem.GROUP_BU = &SelPndRecord.GROUP_BU.VALUE;

&oPendingItem.GROUP_ID = "NEXT";

&oPendingItem.BUSINESS_UNIT = &SelPndRecord.BUSINESS_UNIT.VALUE;

&oPendingItem.CUST_ID = &SelPndRecord.CUST_ID.VALUE;

&oPendingItem.ITEM = &SelPndRecord.ITEM.VALUE;

&oPendingItem.ITEM_LINE = &SelPndRecord.ITEM_LINE.VALUE;

&EntryType = &SelPndRecord.ENTRY_TYPE.VALUE;

If &SelPndRecord.GROUP_SEQ_NUM.VALUE = 1 Then

&EntryType = "IN";

End-If;

If &SelPndRecord.GROUP_SEQ_NUM.VALUE = 2 Then

&EntryType = "CR";

End-If;

&oPendingItem.GROUP_SEQ_NUM = &SelPndRecord.GROUP_SEQ_NUM.VALUE;

&oPendingItem.ENTRY_TYPE = &EntryType;

REM &SelPndRecord.ENTRY_TYPE.VALUE;

&oPendingItem.ENTRY_REASON = &SelPndRecord.ENTRY_REASON.VALUE;

&oPendingItem.ENTRY_AMT = &SelPndRecord.ENTRY_AMT.VALUE;

&oPendingItem.ENTRY_EVENT = &SelPndRecord.ENTRY_EVENT.VALUE;

&oPendingItem.ACCOUNTING_DT = &SelPndRecord.ACCOUNTING_DT.VALUE;

&oPendingItem.ASOF_DT = &SelPndRecord.ASOF_DT.VALUE;

&oPendingItem.DUE_DT = &SelPndRecord.DUE_DT.VALUE;

&oPendingItem.CR_ANALYST = &SelPndRecord.CR_ANALYST.VALUE;

&oPendingItem.SALES_PERSON = &SelPndRecord.SALES_PERSON.VALUE;

&oPendingItem.COLLECTOR = &SelPndRecord.COLLECTOR.VALUE;

REM &oPendingItem.DISPUTE_STATUS = &SelPndRecord.DISPUTE_STATUS.VALUE;

&oPendingItem.PO_REF = &SelPndRecord.PO_REF.VALUE;

REM &oPendingItem.PO_LINE = &SelPndRecord.PO_LINE.VALUE;

REM &oPendingItem.DOCUMENT = &SelPndRecord.DOCUMENT.VALUE;

REM &oPendingItem.DOCUMENT_LINE = &SelPndRecord.DOCUMENT_LINE.VALUE;

&oPendingItem.POSTED_FLAG = "N";

&oPendingItem.PYMNT_TERMS_CD = &SelPndRecord.PYMNT_TERMS_CD.VALUE;

REM &oPendingItem.DISC_AMT = &SelPndRecord.DISC_AMT.VALUE;

REM &oPendingItem.DISC_AMT1 = &SelPndRecord.DISC_AMT1.VALUE;

REM &oPendingItem.DISC_DT = &SelPndRecord.DISC_DT.VALUE;

REM &oPendingItem.DISC_DT1 = &SelPndRecord.DISC_DT1.VALUE;

REM &oPendingItem.DISC_DAYS = &SelPndRecord.DISC_DAYS.VALUE;

REM &oPendingItem.DUE_DAYS = &SelPndRecord.DUE_DAYS.VALUE;

REM &oPendingItem.ALLOW_DISC = &SelPndRecord.ALLOW_DISC.VALUE;

&oPendingItem.DST_ID_AR = &SelPndRecord.DST_ID_AR.VALUE;

&oPendingItem.ENTRY_USE_ID = &SelPndRecord.ENTRY_USE_ID.VALUE;

&oPendingItem.DST_BAL_ITEM = &SelPndRecord.DST_BAL_ITEM.VALUE;

REM &oPendingItem.COLLECTION_STATUS = &SelPndRecord.COLLECTION_STATUS.VALUE;

REM &oPendingItem.COLLECTION_DT = &SelPndRecord.COLLECTION_DT.VALUE;

REM &oPendingItem.BILL_OF_LADING = &SelPndRecord.BILL_OF_LADING.VALUE;

&oPendingItem.SUBCUST_QUAL1 = &SelPndRecord.SUBCUST_QUAL1.VALUE;

&oPendingItem.SUBCUST_QUAL2 = &SelPndRecord.SUBCUST_QUAL2.VALUE;

&oPendingItem.ENTRY_CURRENCY = &SelPndRecord.ENTRY_CURRENCY.VALUE;

REM &oPendingItem.RT_TYPE = &SelPndRecord.RT_TYPE.VALUE;

REM &oPendingItem.RATE_MULT = &SelPndRecord.RATE_MULT.VALUE;

REM &oPendingItem.RATE_DIV = &SelPndRecord.RATE_DIV.VALUE;

REM &oPendingItem.ENTRY_AMT_BASE = &SelPndRecord.ENTRY_AMT_BASE.VALUE;

&oPendingItem.PAYMENT_METHOD = &SelPndRecord.PAYMENT_METHOD.VALUE;

&oPendingItem.USER_AMT7 = &SelPndRecord.USER_AMT7.VALUE;

&oPendingItem.USER7 = &SelPndRecord.USER7.VALUE;

&oPendingItem.USER8 = &SelPndRecord.USER8.VALUE;

&oPendingItem.USER9 = &SelPndRecord.USER9.VALUE;

&oPendingItem.REVALUE_FLAG = &SelPndRecord.REVALUE_FLAG.VALUE;;

rem &oPendingItem.PC_DISTRIB_STATUS = &SelPndRecord.PC_DISTRIB_STATUS.VALUE;;

&oPendingItem.CURRENCY_CD = &SelPndRecord.CURRENCY_CD.VALUE;

&oPendingItem.DST_ID_REAL = &SelPndRecord.DST_ID_REAL.VALUE;

&oPendingItem.ITEM_ACCTG_DT = &SelPndRecord.ITEM_ACCTG_DT.VALUE;

&oPendingItem.ORDER_NO = &SelPndRecord.ORDER_NO.VALUE;

&oPendingItem.CONTRACT_NUM = &SelPndRecord.CONTRACT_NUM.VALUE;

&oPendingItem.BUSINESS_UNIT_BI = &SelPndRecord.BUSINESS_UNIT_BI.VALUE;

&oPendingItem.BUSINESS_UNIT_OM = &SelPndRecord.BUSINESS_UNIT_OM.VALUE;

&oPendingItem.ADDRESS_SEQ_NUM = &SelPndRecord.ADDRESS_SEQ_NUM.VALUE;

&oPendingItem.LETTER_CD = &SelPndRecord.LETTER_CD.VALUE;

&oPendingItem.CONSOL_BUS_UNIT = &SelPndRecord.CONSOL_BUS_UNIT.VALUE;

&oPendingItem.CONSOL_INVOICE = &SelPndRecord.CONSOL_INVOICE.VALUE;

&oPendingItem.PAYMENT_METHOD = &SelPndRecord.PAYMENT_METHOD.VALUE;

&oPendingItem.DRAFT_FORMAT = &SelPndRecord.DRAFT_FORMAT.VALUE;

&oPendingItem.BANK_SETID = &SelPndRecord.BANK_SETID.VALUE;

&oPendingItem.BANK_CD = &SelPndRecord.BANK_CD.VALUE;

&oPendingItem.BANK_ACCT_KEY = &SelPndRecord.BANK_ACCT_KEY.VALUE;

&oPendingItem.DRAFT_TYPE = &SelPndRecord.DRAFT_TYPE.VALUE;

&oPendingItem.DRAFT_AMT_BASE = &SelPndRecord.DRAFT_AMT_BASE.VALUE;

&oPendingItem.DOC_TYPE = &SelPndRecord.DOC_TYPE.VALUE;

&oPendingItem.DOC_SEQ_NBR = &SelPndRecord.DOC_SEQ_NBR.VALUE;

&oPendingItem.DOC_SEQ_DATE = &SelPndRecord.DOC_SEQ_DATE.VALUE;

&oPendingItem.USER_AMT1 = &SelPndRecord.USER_AMT1.VALUE;

&oPendingItem.USER1 = &SelPndRecord.USER1.VALUE;

&oPendingItem.USER2 = &SelPndRecord.USER2.VALUE;

&oPendingItem.REGION_CD = &SelPndRecord.REGION_CD.VALUE;

&oPendingItem.PACKSLIP_NO = &SelPndRecord.PACKSLIP_NO.VALUE;

&oPendingItem.VAT_ADVPAY_FLG = &SelPndRecord.VAT_ADVPAY_FLG.VALUE;

&oPendingItem.LC_ID = &SelPndRecord.LC_ID.VALUE;

&oPendingItem.RECEIVABLE_TYPE = &SelPndRecord.RECEIVABLE_TYPE.VALUE;

&oPendingItem.ENTITY_CODE = &SelPndRecord.ENTITY_CODE.VALUE;

&oPendingItem.PPRC_PROMO_CD = &SelPndRecord.PPRC_PROMO_CD.VALUE;

&oPendingItem.CLAIM_NO = &SelPndRecord.CLAIM_NO.VALUE;

&oPendingItem.CONTROL_CURRENCY = &SelPndRecord.CONTROL_CURRENCY.VALUE;

&oPendingItem.CONTROL_CURR_BASE = &SelPndRecord.CONTROL_CURR_BASE.VALUE;

&i126 = &i126 + 1;

End-While;

&oPendingItemCollection.DeleteItem(1);

rem ***** Execute Save *****;

If Not &oDcPenditemCi.Save() Then;

errorHandler();

throw CreateException(0, 0, "Pending item save failed");

Else

Z_AR_ALNCE_AET.GROUP_ID.Value = &oDcPenditemCi.GROUP_ID;

Z_AR_ALNCE_AET.GROUP_BU.Value = &oDcPenditemCi.GROUP_BU;

MessageBox(0, "", 0, 0, "Group ID created -" | Z_AR_ALNCE_AET.GROUP_ID.Value);

End-If;

rem ***** Execute Cancel *****;

rem If Not &oDcPenditemCi.Cancel() Then;

rem errorHandler();

rem throw CreateException(0, 0, "Cancel failed");

rem End-If;

catch Exception &ex

rem Handle the exception;

MessageBox(0, "", 0, 0, &ex.ToString());

Z_AR_ALNCE_AET.GROUP_ID.Value = " ";

Z_AR_ALNCE_AET.GROUP_BU.Value = " ";

end-try;

Best Regards

Aravind Kumar R

System Analyst/Peoplesoft Practice
Birlasoft • 36, Vijayaraghava Road , T. Nagar, Chennai 600 017 (INDIA)
Cell: +91 9566750166

PeopleCode for reading the file using the rowset and file layout objects - file with headers & lines combination

PeopleSoft – PeopleCode for reading the file using the rowset and file layout objects, the file being read contains the header and lines & this combination repeats.

Function EditRecord(&REC As Record) Returns boolean;

Local integer &E;

REM &REC.ExecuteEdits(%Edit_Required + %Edit_DateRange + %Edit_YesNo + %Edit_TranslateTable + %Edit_PromptTable + %Edit_OneZero);

&REC.ExecuteEdits(%Edit_DateRange + %Edit_YesNo + %Edit_OneZero);

If &REC.IsEditError Then

For &E = 1 To &REC.FieldCount

&MYFIELD = &REC.GetField(&E);

If &MYFIELD.EditError Then

&MSGNUM = &MYFIELD.MessageNumber;

&MSGSET = &MYFIELD.MessageSetNumber;

&LOGFILE.WriteLine("****Record:" | &REC.Name | ", Field:" | &MYFIELD.Name);

&LOGFILE.WriteLine("****" | MsgGet(&MSGSET, &MSGNUM, ""));

End-If;

End-For;

MessageBox(0, "", 0, 0, "Error(s) found in file " | &FileNameForReading | " is being read");

&FileTemp = CreateRecord(Record.Z_AR_FILE_TMP);

&FileTemp.PROCESS_INSTANCE.VALUE = Z_AR_ALNCE_AET.PROCESS_INSTANCE.Value;

&FileTemp.FILE_SEQ.VALUE = Z_AR_FILE_TMP.FILE_SEQ.Value;

&FileTemp.ARCHIVE_PROCESS.VALUE = "D"; /*if errors found in file then do not archive this file*/

&FileTemp.update();

Return False;

Else

Return True;

End-If;

End-Function;

Function ImportSegment(&RS2 As Rowset, &RSParent As Rowset)

Local Rowset &RS1, &RSP;

Local string &RecordName;

Local Record &REC2, &RECP;

Local SQL &SQL1;

Local integer &I, &L;

&SQL1 = CreateSQL("%Insert(:1)");

&RecordName = "RECORD." | &RS2.DBRecordName;

&REC2 = CreateRecord(@(&RecordName));

&RECP = &RSParent(1).GetRecord(@(&RecordName));

For &I = 1 To &RS2.ActiveRowCount

&RS2(&I).GetRecord(1).CopyFieldsTo(&REC2);

If (EditRecord(&REC2)) Then

&REC2.PROCESS_INSTANCE.Value = Z_AR_ALNCE_AET.PROCESS_INSTANCE.Value;

&REC2.FILE_SEQ.Value = Z_AR_ALNCE_AET.FILE_SEQ.Value;

&REC2.Z_AR_STATUS.Value = "P";

If &REC2.Name = "Z_AR_GCTL_TMP" Then

&REC2.OPRID.Value = %OperatorId;

&REC2.ASSN_OPRID.Value = %OperatorId;

MessageBox(0, "", 0, 0, "ORIGIN-EEE" | &REC2.ORIGIN_ID.Value | "EEE");

End-If;

&SQL1.Execute(&REC2);

&RS2(&I).GetRecord(1).CopyFieldsTo(&RECP);

For &L = 1 To &RS2.GetRow(&I).ChildCount

&RS1 = &RS2.GetRow(&I).GetRowset(&L);

If (&RS1 <> Null) Then

&RSP = &RSParent.GetRow(1).GetRowset(&L);

ImportSegment(&RS1, &RSP);

End-If;

End-For;

If &RSParent.ActiveRowCount > 0 Then

&RSParent.DeleteRow(1);

End-If;

Else

&LOGFILE.WriteRowset(&RS);

&LOGFILE.WriteLine("****Correct error in this record and delete all error messages");

&LOGFILE.WriteRecord(&REC2);

For &L = 1 To &RS2.GetRow(&I).ChildCount

&RS1 = &RS2.GetRow(&I).GetRowset(&L);

If (&RS1 <> Null) Then

&LOGFILE.WriteRowset(&RS1);

End-If;

End-For;

End-If;

End-For;

End-Function;

rem *****************************************************************;

rem * PeopleCode to Import Data *;

rem *****************************************************************;

Local File &FILE1;

Local Record &REC1;

Local SQL &SQL1;

Local Rowset &RS1, &RS2;

Local integer &M;

SQLExec("SELECT DESCR254 FROM PS_Z_AR_FILE_TMP WHERE PROCESS_INSTANCE = :1 AND FILE_SEQ = :2", Z_AR_ALNCE_AET.PROCESS_INSTANCE.Value, Z_AR_ALNCE_AET.FILE_SEQ.Value, &FileNameForReading);

MessageBox(0, "", 0, 0, &FileNameForReading | " is being read");

&FILE1 = GetFile(&FileNameForReading, "r", "a", %FilePath_Absolute);

&LOGFILE = GetFile(&FileNameForReading | ".err", "W", %FilePath_Absolute);

&FILE1.SetFileLayout(FileLayout.Z_AR_ALLN_FLO);

&LOGFILE.SetFileLayout(FileLayout.Z_AR_ALLN_FLO);

&RS1 = &FILE1.CreateRowset();

&RS = CreateRowset(Record.Z_AR_GCTL_TMP, CreateRowset(Record.Z_AR_PITM_TMP));

&SQL1 = CreateSQL("%Insert(:1)");

&RS1 = &FILE1.ReadRowset();

While &RS1 <> Null;

ImportSegment(&RS1, &RS);

&RS1 = &FILE1.ReadRowset();

rem MessageBox(0, "", 0, 0, "File contains " | &RS1.RowCount | " lines to be proccessed");

End-While;

&FILE1.Close();

&LOGFILE.Close();

************************************************************************************************************************************************************************************

Sunday, March 24, 2013

PeopleCode to access the other databases from JDBC and Java

Below code is based out of java JDBC classes and executes the SQL queries on Oracle databases, Provided that JDBC is installed and TNS entry is know,

&TEMP = Z_PRC_PRM_TBL.USER1.Value;

Z_PRC_PRM_TBL.USER1.Value = "";

Z_PRC_PRM_TBL.USER1.Value = &TEMP;

DoSaveNow();

Local JavaObject &driverManager = GetJavaClass("java.sql.DriverManager");

&driverManager.registerDriver(CreateJavaObject("oracle.jdbc.OracleDriver"));

Local string &QueryString;

Local Rowset &PrcsMonRS;

Local JavaObject &driver = CreateJavaObject("oracle.jdbc.OracleDriver");

Local JavaObject &info = CreateJavaObject("java.util.Properties");

&info.put("user", Z_PRC_PRM_TBL.USER1.Value);

&info.put("password", Z_PRC_PRM_TBL.PASSWD.Value);

Local JavaObject &conn = &driverManager.getConnection(Z_PRC_PRM_TBL.CONNECTSTRING.Value, "sysadm", "ad%sy8");

&QueryString1 = "SELECT R.RUNSTATUS ,SERVERNAMERUN ,R.RUNDTTM ,(CASE WHEN R.RUNSTATUS IN (16,5,4) THEN to_char(floor(((RUNDTTM-SYSDATE)*60*24/60)/24))||'d'|| to_char(MOD(floor( (RUNDTTM-SYSDATE)*60*24/60),24)) ||'h'||to_char(MOD(FLOOR((RUNDTTM-SYSDATE)*60*24),60)) ";

&QueryString2 = " ||'m' WHEN R.RUNSTATUS IN (10,3,17,1,8) THEN '0' WHEN R.RUNSTATUS IN (6,7) THEN to_char(CEIL((SYSDATE-BEGINDTTM)*60*24)) WHEN R.RUNSTATUS IN (9) THEN to_char(CEIL((ENDDTTM-BEGINDTTM)*60*24)) ELSE TO_CHAR(R.RUNDTTM,'mm-Mon-yyyy') END) AS TTE_ETA ,DECODE(R.PRCSTYPE,'Application Engine','AE','SQR Process','SQR P','SQR Report','SQR R',R.PRCSTYPE) AS PRCSTYPE ";

&QueryString3 = " ,R.PRCSNAME,NVL(P.DESCR,J.DESCR) AS PRCS_DESCR ,R.RUNCNTLID AS RUNCNTLID ,R.RECURNAME AS RECURNAME ,R.PRCSINSTANCE AS PI ,R.RQSTDTTM AS RQSTDTTM ,SYSDATE AS CURDTTM ,ENDDTTM FROM PSPRCSRQST R LEFT OUTER JOIN PS_PRCSJOBDEFN J ON R.MAINJOBNAME = J.PRCSJOBNAME LEFT OUTER JOIN PS_PRCSDEFN P ON R.PRCSTYPE = P.PRCSTYPE AND R.PRCSNAME = P.PRCSNAME ";

&QueryString11 = "WHERE R.OPRID LIKE ':1' AND R.RQSTDTTM BETWEEN SYSDATE-DECODE(R.RUNSTATUS,:2,4) AND SYSDATE AND R.PRCSTYPE LIKE '%' AND R.PRCSJOBSEQ=0 AND R.RUNSTATUS IN (:3) ORDER BY 1 ASC,RUNDTTM ASC";

SQLExec("SELECT CONNECTSTRING, RTRIM(XMLAGG (XMLELEMENT (b, RUNSTATUS|| ','||DAYSTOCACHE || ',')).EXTRACT ('//text()'), ',') AS EXPR FROM PS_Z_PRC_STS_TBL WHERE CONNECTSTRING = :1 AND Z_PRC_NAME = :2 AND FLAG = 'Y' GROUP BY CONNECTSTRING", Z_PRC_PRM_TBL.CONNECTSTRING.Value, Z_PRC_PRM_TBL.Z_PRC_NAME.Value, &dMY1, &StatusSting);

SQLExec("SELECT CONNECTSTRING, RTRIM(XMLAGG (XMLELEMENT (b, RUNSTATUS||',')).EXTRACT ('//text()'), ',') AS EXPR FROM PS_Z_PRC_STS_TBL WHERE CONNECTSTRING = :1 AND Z_PRC_NAME = :2 AND FLAG = 'Y' GROUP BY CONNECTSTRING", Z_PRC_PRM_TBL.CONNECTSTRING.Value, Z_PRC_PRM_TBL.Z_PRC_NAME.Value, &dMY1, &StatusSelSting);

&QueryString14 = Substitute(&QueryString11, ":1", Z_PRC_PRM_TBL.OPRID.Value);

&QueryString13 = Substitute(&QueryString14, ":2", &StatusSting);

&QueryString12 = Substitute(&QueryString13, ":3", &StatusSelSting);

&QueryString = &QueryString1 | &QueryString2 | &QueryString3 | &QueryString12;

rem MessageBox(0, "", 0, 0, &QueryString12);

&PrcsMonRS = GetLevel0()(1).GetRowset(Scroll.DUMMY_MSG_TBL);

&PrcsMonRS.Flush();

&rowcounter = 0;

Local JavaObject &stmt = &conn.createStatement();

Local JavaObject &rs = &stmt.executeQuery(&QueryString);

Local number &rowIdx;

While &rs.next()

&rowIdx = &rowIdx + 1;

&rowcounter = &rowcounter + 1;

&PrcsMonRS.InsertRow(&rowcounter);

rem MessageBox(0, "", 0, 0, "Row " | &rowIdx | " column 1: " | &rs.getObject(1).toString());

&PrcsMonRS.GetRow(&rowcounter).Z_PRC_MON_TBL.RUNSTATUS.Value = &rs.getString("RUNSTATUS");

&PrcsMonRS.GetRow(&rowcounter).Z_PRC_MON_TBL.SERVERNAMERUN.Value = &rs.getString("SERVERNAMERUN");

&PrcsMonRS.GetRow(&rowcounter).Z_PRC_MON_TBL.RUNDTTM.Value = &rs.getString("RUNDTTM");

&PrcsMonRS.GetRow(&rowcounter).Z_PRC_MON_TBL.DESCR.Value = &rs.getString("TTE_ETA");

&PrcsMonRS.GetRow(&rowcounter).Z_PRC_MON_TBL.PRCSTYPE.Value = &rs.getString("PRCSTYPE");

&PrcsMonRS.GetRow(&rowcounter).Z_PRC_MON_TBL.PRCSNAME.Value = &rs.getString("PRCSNAME");

&PrcsMonRS.GetRow(&rowcounter).Z_PRC_MON_TBL.DESCR1.Value = &rs.getString("PRCS_DESCR");

&PrcsMonRS.GetRow(&rowcounter).Z_PRC_MON_TBL.RUNCNTLID.Value = &rs.getString("RUNCNTLID");

&PrcsMonRS.GetRow(&rowcounter).Z_PRC_MON_TBL.RECURNAME.Value = &rs.getString("RECURNAME");

&PrcsMonRS.GetRow(&rowcounter).Z_PRC_MON_TBL.PROCESS_INSTANCE.Value = &rs.getString("PI");

&PrcsMonRS.GetRow(&rowcounter).Z_PRC_MON_TBL.CURRENT_DTTM.Value = &rs.getString("CURDTTM");

&PrcsMonRS.GetRow(&rowcounter).Z_PRC_MON_TBL.END_DTTM.Value = &rs.getString("ENDDTTM");

End-While;

If &rowcounter > 1 Then

&PrcsMonRS.DeleteRow(&rowcounter + 1);

End-If;

&PrcsMonRS.Sort(Z_PRC_MON_TBL.RUNSTATUS, "A", Z_PRC_MON_TBL.RUNDTTM, "D");

&rs.close();

&stmt.close();

&conn.close();

Wednesday, February 13, 2013

Production Monitoring SQL for PeopleSoft

Below SQL is used to monitor the production jobs in your production environment, It lists the jobs based on the job statuses

1)Error for 4 days

2)Cancelled for 4 days

3)Deleted for 4 days

4)Queued for last 99 days

5)Success for 4 days

SELECT
/*+ index (PSPRCSRQST PSBPSPRCSRQST) */
DECODE(R.RUNSTATUS,16,'333',5,'333',4,'333', 10,'111',3,'111',17,'111',1,'111',8,'111',6,'222',7,'222',9,'444',2,'000','555')
||DECODE(R.RUNSTATUS,1,'Cancel',10,'NOT Successful',16,'Pending',17,'Warning',18,'Blocked'
,19,'Restart',2,'Delete',3,'Error',4,'Hold',5,'Queued',6,'Initiated',7,'Processing',8,'Cancelled',9,'Success','N/A') AS STATUS,SERVERNAMERUN
,R.RUNDTTM AS RUNDTTM
,(CASE WHEN R.RUNSTATUS IN (16,5,4) THEN
to_char(floor(((RUNDTTM-SYSDATE)*60*24/60)/24))||'d'|| to_char(MOD(floor( (RUNDTTM-SYSDATE)*60*24/60),24)) ||'h'||to_char(MOD(FLOOR((RUNDTTM-SYSDATE)*60*24),60))||'m'
WHEN R.RUNSTATUS IN (10,3,17,1,8) THEN '0'
WHEN R.RUNSTATUS IN (6,7) THEN to_char(CEIL((SYSDATE-BEGINDTTM)*60*24))
WHEN R.RUNSTATUS IN (9) THEN to_char(CEIL((ENDDTTM-BEGINDTTM)*60*24)) ELSE TO_CHAR(R.RUNDTTM,'mm-Mon-yyyy') END) AS TTE_ETA
,DECODE(R.PRCSTYPE,'Application Engine','AE','SQR Process','SQR P','SQR Report','SQR R',R.PRCSTYPE) AS PRCSTYPE
,R.PRCSNAME,NVL(P.DESCR,J.DESCR) AS PRCS_DESCR
,R.RUNCNTLID AS RUNCNTLID
,R.RECURNAME AS RECURNAME
,R.PRCSINSTANCE AS PI
,R.RQSTDTTM AS RQSTDTTM
,SYSDATE AS CURDTTM
,ENDDTTM
FROM
PSPRCSRQST R LEFT OUTER JOIN PS_PRCSJOBDEFN J
ON R.MAINJOBNAME = J.PRCSJOBNAME LEFT OUTER JOIN PS_PRCSDEFN P
ON R.PRCSTYPE = P.PRCSTYPE AND R.PRCSNAME = P.PRCSNAME
WHERE
R.OPRID IN ('TC0996')
AND R.RQSTDTTM BETWEEN SYSDATE-DECODE(R.RUNSTATUS,16,99,5,99,4) AND SYSDATE
AND R.PRCSTYPE LIKE '%'
AND R.PRCSJOBSEQ=0
AND R.RUNSTATUS IN (16,5,4,10,3,17,1,8,6,7,9,3)
ORDER BY 1 ASC,RUNDTTM ASC;

Code for calling the run control process selection page from the pushbutton - field change event

Below code is used to call the run control process selection page from the field change event,

This can be used instead of adding the run control page and using the RUN push button in the page

Declare Function LaunchProcessRequestDlg PeopleCode PRCSRQSTDLG_WRK.LOADPRCSRQSTDLGPB FieldFormula;

LaunchProcessRequestDlg(GetRowset().GetRow(1).GetRecord(1), GetRowset().GetRow(1).GetRecord(1).RUN_CNTL_ID.Value, " ", True);

PeopleSoft Helper

Saturday, May 24, 2014

Thursday, April 25, 2013

Thursday, April 18, 2013

Tuesday, April 16, 2013

Sunday, March 24, 2013

Wednesday, February 13, 2013

Aravind's Blogs