Transaction Registry
EOAW_XREF_SBR Sub record
Configure Transactions
Monday, 9 February 2026
Friday, 21 July 2023
ESP and PeopleSoft
ESP_APPLICATION table
| APPL_ID | NUMBER(19,0) | No | 1 |
| JOB_NAME | VARCHAR2(130 BYTE) | No | 2 |
| APPL_NAME | VARCHAR2(130 BYTE) | No | 3 |
| APPL_GEN_NO | NUMBER(10,0) | No | 4 |
| START_DATE_TIME | DATE | Yes | 5 |
| END_DATE_TIME | DATE | Yes | 6 |
| SCHEDULED_DATE_TIME | DATE | Yes | 7 |
| STATE | VARCHAR2(64 BYTE) | Yes | 8 |
| TAG | VARCHAR2(128 BYTE) | Yes | 9 |
| COMMENTS | CLOB | Yes | 10 |
| EVENT_NAME | VARCHAR2(161 BYTE) | Yes | 11 |
Sample SQL
select
APPL_ID, JOB_NAME, APPL_NAME, APPL_GEN_NO, START_DATE_TIME, END_DATE_TIME, SCHEDULED_DATE_TIME, STATE, TAG, COMMENTS, EVENT_NAME
from esp.esp_application
where appl_name like 'PEOPLESOFT_PSH%PAYROLL' ORDER BY START_DATE_TIME DESC;
ESP_GENERIC_JOB
| JOB_ID | NUMBER(19,0) | No | 1 |
| JOB_NAME | VARCHAR2(130 BYTE) | Yes | 2 |
| JOB_QUALIFIER | VARCHAR2(64 BYTE) | Yes | 3 |
| SUBMISSION_INSTANCE | NUMBER(10,0) | Yes | 4 |
| APPL_ID | NUMBER(19,0) | Yes | 5 |
| JOB_NO | VARCHAR2(32 BYTE) | Yes | 6 |
| AGENT_NAME | VARCHAR2(50 BYTE) | Yes | 7 |
| START_DATE_TIME | DATE | Yes | 8 |
| END_DATE_TIME | DATE | Yes | 9 |
| STATE | VARCHAR2(64 BYTE) | Yes | 10 |
| CONDITIONS | VARCHAR2(1024 BYTE) | Yes | 11 |
| WOB_TYPE | VARCHAR2(32 BYTE) | Yes | 12 |
| USERID | VARCHAR2(128 BYTE) | Yes | 13 |
| OVERDUE_START | NUMBER(19,0) | Yes | 14 |
| OVERDUE_END | NUMBER(19,0) | Yes | 15 |
| COMPLETION_CODE | VARCHAR2(10 BYTE) | Yes | 16 |
| COMMAND | VARCHAR2(1024 BYTE) | Yes | 17 |
| SCRIPT | VARCHAR2(1024 BYTE) | Yes | 18 |
| ARGUMENT | VARCHAR2(4000 BYTE) | Yes | 19 |
| ENVARS | VARCHAR2(4000 BYTE) | Yes | 20 |
| TAG | VARCHAR2(128 BYTE) | Yes | 21 |
| RESOURCES | VARCHAR2(1024 BYTE) | Yes | 22 |
| SUBAPPL | VARCHAR2(130 BYTE) | Yes | 23 |
| STATUS | VARCHAR2(1024 BYTE) | Yes | 24 |
| USER_STATUS | VARCHAR2(128 BYTE) | Yes | 25 |
| COMMENTS | VARCHAR2(2000 BYTE) | Yes | 26 |
| JOB_LOG_ID | VARCHAR2(128 BYTE) | Yes | 27 |
| READY_DATE_TIME | DATE | Yes | 28 |
| USER_REASON | VARCHAR2(1024 BYTE) | Yes | 29 |
Select statement
| select | ESP_APPLICATION table related fields | ||
| APPL_ID, JOB_NAME, | -- APPL_NAME, APPL_GEN_NO, | ||
| START_DATE_TIME, END_DATE_TIME, | --SCHEDULED_DATE_TIME, | ||
| STATE, TAG, COMMENTS, | --EVENT_NAME, | ||
| JOB_ID, JOB_QUALIFIER, SUBMISSION_INSTANCE, JOB_NO, AGENT_NAME, CONDITIONS, WOB_TYPE, USERID, OVERDUE_START, OVERDUE_END, COMPLETION_CODE, COMMAND, | |||
| SCRIPT, ARGUMENT, ENVARS, RESOURCES, SUBAPPL, STATUS, USER_STATUS, JOB_LOG_ID, READY_DATE_TIME, USER_REASON | |||
| from ESP.esp_generic_job A ; | |||
ESP_FILEMONTR_JOB
| JOB_ID | NUMBER(19,0) | No | 1 |
| FILENAME | VARCHAR2(1024 BYTE) | Yes | 2 |
| TRIGGER_CONDITION | VARCHAR2(10 BYTE) | Yes | 3 |
Sample SQL
| select | -- below are related fields from esp_generic_job |
| JOB_ID, | |
| FILENAME, | -- SCRIPT |
| TRIGGER_CONDITION | -- ARGUMENT |
| from ESP.ESP_FILEMONTR_JOB; |
Sample SQL to get applications run on Sunday between certain time
select distinct APPL_NAME,-- APPL_GEN_NO,
a1.JOB_NAME, JOB_QUALIFIER, a1.STATE, WOB_TYPE, fm1.FILENAME,fm1.TRIGGER_CONDITION--, fm1.JOB_ID -- to get additional params for Unix
,a1.START_DATE_TIME,to_char(a1.START_DATE_TIME,'DAY')
-- , '~~',a1.* ,'~~',b1.*
,A1.APPL_ID,a1.job_id
,to_char(A1.START_DATE_TIME,'YYYY-MM-DD HH24:MI') START_DATE_TIME,to_char(a1.END_DATE_TIME,'YYYY-MM-DD HH24:MI') END_DATE_TIME
,APPL_GEN_NO ,a1.JOB_NO,a1.AGENT_NAME,a1.SCRIPT, a1.ARGUMENT
from ESP.esp_generic_job A1 -- job table
left outer join ESP.ESP_FILEMONTR_JOB fm1 on a1.job_id = fm1.job_id -- to get additional params for Unix
, esp.esp_application B1 -- application table
WHERE
appl_name like 'specific_app_names%' -- Restrict to required application names
-- AND appl_name <> 'ignore_app_names' -- dont show some applications
AND A1.APPL_ID = B1.APPL_ID -- join both tables
AND A1.START_DATE_TIME >= SYSDATE - 61 -- limit number of days
AND to_char(a1.START_DATE_TIME,'DAY') LIKE 'SUN%' -- ran on Sunday
AND to_char(A1.START_DATE_TIME,'HH24:MI') BETWEEN '06:30' AND '23:59'-- limit time
-- and APPL_NAME = 'PEOPLESOFT_PSH_PAYROLL_PASSTHRU' and APPL_GEN_NO = 68
-- and a.JOB_NAME ='PSH_AEP_AF_ADP_PASSTHRU' and JOB_QUALIFIER='FILEWATCHER'
and WOB_TYPE in ('UNIX' ,'FM') -- Unix or File Watchers
-- and a1.job_id = (select min(a2.job_id) from ESP.esp_generic_job a2 where a1.JOB_NAME = a2.JOB_NAME and a1.JOB_QUALIFIER = a2.JOB_QUALIFIER )
order by APPL_NAME,a1.APPL_ID,a1.JOB_ID,a1.START_DATE_TIME,a1.JOB_NO
;
Tuesday, 4 July 2023
PeopleSoft Query
PeopleSoft Query
select 'PSQRYDEFN ',a1.OPRID, QRYNAME, DESCR, VERSION, QRYTYPE, SELCOUNT, EXPCOUNT, BNDCOUNT, QRYVALID, LASTUPDDTTM, LASTUPDOPRID, QRYAPPROVED, CREATEOPRID, CREATEDTTM, APPROVEOPRID, APPROVEDTTM, EXECLOGGING, QRYDISABLED, QRYFOLDER, QRYJOINOPTIMIZE, QRYIMGTYPE, DESCRLONG
from PSQRYDEFN a1
where QRYNAME = 'LM_LG_A_PROGRAM' and rownum <= 12 ;--- stores high-level query definitions with version numbers.
select 'PSQRYSELECT ',a1.OPRID, QRYNAME, SELNUM, SELECTTYPE, PARENTSELNUM, RCDCOUNT, FLDCOUNT, CRTCOUNT, QRYDISTINCT, HAVECRTCOUNT
from PSQRYSELECT a1
where QRYNAME <> 'LM_LG_A_PROGRAM' and rownum <= 12 ;-- stores all SELECT requirements by select type, i.e. union, subselect, join,รข€¦.
select 'PSQRYDEFNLANG ',a1.OPRID, QRYNAME,language_cd,DESCR,DESCRlong
from PSQRYDEFNLANG a1
where QRYNAME = 'LM_LG_A_PROGRAM' and rownum <= 12 ;--- Non-English detail is stored in it as well as in PSQRYHEADLANG
-- 1. Records
select 'PSQRYRECORD ',a1.OPRID, QRYNAME, SELNUM, RCDNUM, RECNAME, JOINTYPE, JOINRCDNUM, JOINFLDNUM, CORRNAME, QRYRECPRUNABLE ,
decode,jointype,5,'left outer join') rpJoinType
from PSQRYRECORD a1
where QRYNAME = 'LM_LG_A_PROGRAM' and rownum <= 12 ;-- stores all records used in all aspects of query creation
-- 2. Query
-- 3. Expressions
Refer 6. Criteria
-- 4. Prompts
select 'PSQRYBIND ',a1.OPRID, QRYNAME, BNDNAME, BNDNUM, FIELDNAME, HDGTYPE, HEADING, FIELDTYPE,
LENGTH, DECIMALPOS, FORMAT, EDITTABLE, USECOUNT, USEEDIT, QRYREQUIREDPROMPT, QRYPROMPTDEFAULT
from PSQRYBIND a1
where QRYNAME = 'LM_LG_A_PROGRAM' and rownum <= 12 ;--- stores all run-time 'prompt' data
select 'PSQRYBINDLANG ',a1.OPRID, QRYNAME, BNDNAME,language_cd,heading
from PSQRYBINDLANG a1
where QRYNAME = 'LM_LG_A_PROGRAM' and rownum <= 12 ;--
-- 5. Fields
select 'PSQRYFIELD ',a1.OPRID, QRYNAME, QRYFLDNAME, SELNUM, FLDNUM, RECNAME, FIELDNAME, FLDRCDNUM, FLDEXPNUM, HDGTYPE, HEADING,
COLUMNNUM, GROUPBYNUM, ORDERBYNUM, ORDERBYDIR, TTLTYPE, SUBTTLNUM, USECOUNT, XLATTYPE, XLATEXPRTYPE, XLATFLDNUM, XLATEXPNUM, AGGREGATEFUNC
, decode (columnum,0,'4nonFld',
decode(recname,' ','2expr',
decode((selnum,1,'1fld','3'))) rpQryFld
from PSQRYFIELD a1
where QRYNAME = 'LM_LG_A_PROGRAM'
and columnnum <> 0; and rownum <= 12 ;--- stores all fields used in all aspects of query operation
note:
psqryrecord.oprid,qryname,selnum,recname,Rcdnum match
psqryfield.oprid,qryname,selnum,recname,fldRcdnum
select 'PSQRYFIELDLANG ',a1.OPRID, QRYNAME,QRYFLDNAME,language_cd,heading
from PSQRYFIELDLANG a1
where QRYNAME = 'LM_LG_A_PROGRAM' and rownum <= 12 ;--
-- 6. Criteria
select 'PSQRYCRITERIA ',a1.OPRID, QRYNAME, SELNUM, CRTNUM, COMBTYPE, NEGATION, LPARENLVL, LCRTSELNUM, LCRTFLDNUM, CONDTYPE, /* condition
2 =,3 <>,
4>,5not>,5<,7not>,
8 in list,9 not in list,
10 between,11 not between,
11 exists,12 not exists,
14 like,15 not like,
16 is null,17 is not null,
18 in tree,19 not in tree,
20 effdt <=,21 effdt >=,22 effdt<,23 effdt >,
24 first effdt,25 last effdt
*/
EXPRTYPE/*
0 eff seq,1 const,2field,3 expression,4 subquery,5 list,6 current date,7 tree,8 prompt,
9 between const-const
10 bet cosnt-field
11 bet const-expr
12 bet fld-const
13 between fld fld
14 between field expr
15 between exp const
16 bet expr fld
17 bet exp expr
18 tree prompt
*/,
R1CRTSELNUM, R1CRTFLDNUM, R1CRTEXPNUM, R2CRTSELNUM, R2CRTFLDNUM, R2CRTEXPNUM, RPARENLVL, QRYOJSELNUM ,
decode(combtype,4,'hv1',5,'hv2',6,'hv3',
1,'cr1',2,'cr2',3,'cr3') /*hv is having, cr is criteria */
from PSQRYCRITERIA a1
where QRYNAME = 'LM_LG_A_PROGRAM' and rownum <= 12 ;--- stores all criteria expressions in code format
select 'PSQRYEXPR ',a1.OPRID, QRYNAME, EXPNUM, FIELDTYPE, LENGTH, DECIMALPOS, USECOUNT, EXPRESSIONTEXT
from PSQRYEXPR a1
where QRYNAME = 'LM_LG_A_PROGRAM' and rownum <= 12 ;--- stores the text associated with each criteria expression
-- 7. Having
-- 8. Dependency
-- 9. Transformations
-- 10. View SQL
-- 11. Run
select 'PSQRYTRANS ',a1.QRYNAME, USERID, OPRID, QRYMACHINENAME, QRYDOMAINID, PROCESSID, HOSTNAME, STATUS, QRYSTARTTIME, QRYENDTIME
from PSQRYTRANS a1
where QRYNAME <> 'LM_LG_A_PROGRAM' and rownum <= 12 ;--
select 'PSQRYSTATS ',a1.OPRID, QRYNAME, EXECCOUNT, AVGEXECTIME, AVGFETCHTIME, AVGNUMROWS, LASTEXECDTTM, NUMKILLS
from PSQRYSTATS a1
where QRYNAME <> 'LM_LG_A_PROGRAM' and rownum <= 12 ;--
select 'PSQRYEXECLOG ',a1.OPRID, QRYNAME, APPLNAME, EXECDTTM, RUNOPRID, EXECTIME, FETCHTIME, NUMROWS, MAXROWLIMIT, KILLEDREASON
from PSQRYEXECLOG a1
where QRYNAME <> 'LM_LG_A_PROGRAM' and rownum <= 12 ;--
select 'PSQRYACCLSTRECS ',a1.VERSION, CLASSID, RECNAME, RECDESCR from PSQRYACCLSTRECS a1 where rownum <= 12 ;
--select 'PSQRYHEADLANG ',a1.* from PSQRYHEADLANG a1 where QRYNAME <> 'LM_LG_A_PROGRAM' and rownum <= 12 ;--- Non-English detail is stored in it as well as in PSQRYDEFNLANG
select 'PSQRYDEL ',a1.OPRID, QRYNAME, VERSION from PSQRYDEL a1 where QRYNAME <> 'LM_LG_A_PROGRAM' and rownum <= 12 ;--
select 'PSQRYFAVORITES ',a1.OPRID, QRYNAME,qryowner from PSQRYFAVORITES a1 where QRYNAME = 'LM_LG_A_PROGRAM' and rownum <= 12 ;--
select 'PSQRYFLAGS ',a1.QRYTIMEOUT, QRYSTATSQL from PSQRYFLAGS a1 where rownum <= 12 ;--
select 'PSQRYLINK ',a1.qryname,qrynamechild from PSQRYLINK a1 where QRYNAME <> 'LM_LG_A_PROGRAM' and rownum <= 12 ;-- stores the relationships to child queries
select 'PSQRYPREFS ',a1.OPRID, QRYAUTOJOIN, QRYNAMESTYLE from PSQRYPREFS a1 where rownum <= 12 ;--
select 'PSQRYXFORM ',a1.OPRID, QRYNAME,qryxfirmname,cdm_file_type,qryformxsl from PSQRYXFORM a1 where QRYNAME <> 'LM_LG_A_PROGRAM' and rownum <= 12 ;--
Tuesday, 13 June 2023
Integration Broker
PeopleTools> Integration Broker> Integration Setup> Service Operations
| Menu: | IB_PROFILE |
| Component: | IB_SERVICE |
| Page: | IB_SERVICE Record:PSOPRVERDFN |
Which ones are active?
select * from PSIBRTNGDEFN_VW where EFF_STATUS = 'A';
select * from PSOPRVERDFN where ACTIVE_FLAG = 'A'; -- this is parent of above
select * from PSSERVICEOPR_VW where ACTIVE_FLAG= 'A' and IB_OPERATIONNAME like '%SYNC%' and RTNGTYPE; -- synch and asynch
PeopleTools> Integration Broker> Service Operations> Monitor Monitoring> Asynchronous Services
select distinct SUBNODE, -- SUBQUEUE, 18 dist
IB_OPERATIONNAME, VERSIONNAME,PUBNODE, --PUBSTATUS, PUBCONSTATUS,
QUEUENAME,EXTOPERATIONNAME, ROUTINGDEFNNAME--,NRID,ACTIONNAME, CONVERSATIONID
from PSIBPUBCON_VW where SUBNODE <> 'PSFT_LM'; -- or pubnode = 'PSFT_HR';
select distinct PUBNODE,IB_OPERATIONNAME, VERSIONNAME, ACTIONNAME, QUEUENAME, EXTOPERATIONNAME
--IB_SEGMENTINDEX, IB_OPERATIONNAME, VERSIONNAME, ACTIONNAME, --SUBQUEUE,
--PUBNODE, PUBSTATUS, SUBCONSTATUS, QUEUENAME,EXTOPERATIONNAME, PROCESS_INSTANCE, CONVERSATIONID
from PSIBSUBCON_VW where pUBNODE<> 'PSFT_LM';
select distinct PUBNODE, PUBSTATUS, QUEUENAME, EXTOPERATIONNAME, IB_OPERATIONNAME ,ORIGPUBNODE, CONVERSATIONID
from PSIBPUBHDR_VW order by ORIGPUBNODE ,PUBNODE;
PeopleTools> Integration Broker> Service Operations> Monitor Monitoring> Synchronous Services
PeopleTools>Integration Broker>Integration Setup>Service Operations c/IB_PROFILE.IB_SERVICE.GBL
select LASTUPDDTTM, DESCR, DESCRLONG,a.* from PSOPERATION a where IB_OPERATIONNAME='XX_PUNCH_TIME' ;;
select IB_OPERATIONNAME, QUEUENAME, MSGNAME, IB_MSGVERSION,a.* from PSOPRVERDFNPARM a where IB_OPERATIONNAME='XX_PUNCH_TIME' ;
select LASTUPDDTTM, DESCR, DESCRLONG,a.* from PSOPERATION a where IB_OPERATIONNAME='XX_PUNCH_TIME' ;;
select IB_OPERATIONNAME, QUEUENAME, MSGNAME, IB_MSGVERSION,a.* from PSOPRVERDFNPARM a where IB_OPERATIONNAME='XX_PUNCH_TIME' ;
2nd field on page: Operation Type : RTNGTYPE
select IB_OPERATIONNAME, HANDLERNAME, HANDLERID, HANDLEROWNER, HANDLERTYPE, ACTIVE_FLAG,
LASTUPDDTTM, LASTUPDOPRID, DESCR, DESCRLONG,
a.* from PSOPRHDLR a where IB_OPERATIONNAME like 'XX_%' and ACTIVE_FLAG <> 'I' order by a.LASTUPDDTTM desc;
select IB_OPERATIONNAME, --HANDLERNAME, HANDLERID, HANDLEROWNER, HANDLERTYPE, ACTIVE_FLAG,
VERSIONNAME, SENDERNODENAME, RECEIVERNODENAME, RTNGTYPE, ROUTINGDEFNNAME, EFFDT, EFF_STATUS
from PSIBRTNGDEFN_VW a where IB_OPERATIONNAME like 'xx%';
select a1.IB_OPERATIONNAME,a1.LASTUPDDTTM, a1.DESCR, a1.DESCRLONG,
a2.QUEUENAME, a2.MSGNAME, a2.IB_MSGVERSION -- empty a1.MSGNAME, a1.IB_MSGVERSION
,HANDLERNAME, HANDLERID, HANDLEROWNER, HANDLERTYPE, ACTIVE_FLAG,h1.LASTUPDDTTM, h1.LASTUPDOPRID, h1.DESCR, h1.DESCRLONG
,r1.VERSIONNAME, SENDERNODENAME, RECEIVERNODENAME, r1.RTNGTYPE, ROUTINGDEFNNAME, r1.EFFDT, EFF_STATUS
,'~~',q1.LASTUPDDTTM, q1.DESCR, q1.DESCRLONG,q1.QUEUESTATUS,'~~',m1.*
from PSOPERATION a1, ((PSOPRVERDFNPARM a2 left outer join PSOPRHDLR h1 on a2.IB_OPERATIONNAME=h1.IB_OPERATIONNAME )
left outer join PSIBRTNGDEFN_VW r1 on a2.IB_OPERATIONNAME=r1.IB_OPERATIONNAME ), PSQUEUEDEFN q1,PSMSGVER m1
where a1.IB_OPERATIONNAME=a2.IB_OPERATIONNAME and
a1.IB_OPERATIONNAME='xxx' and
a2.QUEUENAME = q1.QUEUENAME and
a2.MSGNAME = m1.MSGNAME
;;
from PSIBRTNGDEFN;
select ROUTINGDEFNNAME, EFFDT, PARAMETERNAME, SENDERNODENAME, RECEIVERNODENAME, XFRMNAME, XFRMNAME_SECOND, INMSGNAME, INMSGVERSION, OUTMSGNAME, OUTMSGVERSION, ALIASNAME, VERSIONNAME, IB_SECURITYOVRD, IBEXTERNALUSERID, IBEXTERNALPWD, TOKENTYPE, TOKEN_ENCRYPTED, IB_ENCRYPTLEVEL, TOKEN_SIGNED
from PSRTNGDFNPARM;
select ROUTINGDEFNNAME, EFFDT, NAMETYPE, PROPNAME, PROPVALUE, COMMENTS
from PSRTNGDFNPROP;
PeopleTools>Integration Broker>Integration Setup>Queues /c/IB_PROFILE.IB_OPERATION.GBL
select LASTUPDDTTM, DESCR, DESCRLONG,q.* from PSQUEUEDEFN q where QUEUENAME ='XX_PUNCH_TIME_OUT';
PeopleTools > Integration Broker > Integration Setup > Messages c/IB_PROFILE.IB_MESSAGE_BUILDER.GBL
select * from PSMSGVER where MSGNAME='XX_PUNCH_TIME';
IB Used recently and additional details:
--
Publication Contracts
select distinct a1.*, i1.LASTUPDDTTM, i1.DESCR,
dbms_lob.substr (i1.DESCRLONG,3000,1 ),i1.IB_SERVICENAME,i1.LASTUPDOPRID,q.LASTUPDDTTM, q.DESCR,
dbms_lob.substr (q.DESCRLONG,3000,1 ),q.LASTUPDOPRID
,m1.MSGNAME,m1.DESCR, m1.LASTUPDDTTM, m1.LASTUPDOPRID,
dbms_lob.substr (m1.DESCRLONG,3000,1 )
from
(select distinct SUBNODE, -- SUBQUEUE, 18 dist
IB_OPERATIONNAME, VERSIONNAME,PUBNODE, --PUBSTATUS,
PUBCONSTATUS,
QUEUENAME,EXTOPERATIONNAME,
ROUTINGDEFNNAME--,NRID,ACTIONNAME, CONVERSATIONID
,min(LASTUPDDTTM),max(LASTUPDDTTM)
from PSIBPUBCON_VW
group by SUBNODE,
IB_OPERATIONNAME, VERSIONNAME, PUBNODE, QUEUENAME, EXTOPERATIONNAME,
ROUTINGDEFNNAME
) a1
, PSOPERATION i1, PSQUEUEDEFN q, PSOPRVERDFNPARM i2 left
outer join PSMSGDEFN m1 on i2.MSGNAME = m1.MSGNAME
where i1.IB_OPERATIONNAME =
a1.IB_OPERATIONNAME and q.QUEUENAME = a1.QUEUENAME and i1.IB_OPERATIONNAME =
i2.IB_OPERATIONNAME
; -- or pubnode =
'PSFT_HR';
--
Subscription Contracts
select distinct a1.*,
i1.LASTUPDDTTM,
i1.DESCR,
dbms_lob.substr (i1.DESCRLONG,3000,1 ),i1.IB_SERVICENAME,i1.LASTUPDOPRID,q.LASTUPDDTTM, q.DESCR,
dbms_lob.substr (q.DESCRLONG,3000,1 ),q.LASTUPDOPRID
,m1.MSGNAME,m1.DESCR, m1.LASTUPDDTTM, m1.LASTUPDOPRID,
dbms_lob.substr (m1.DESCRLONG,3000,1 )
from
(select distinct
PUBNODE,IB_OPERATIONNAME, VERSIONNAME, ACTIONNAME, QUEUENAME
--IB_SEGMENTINDEX, IB_OPERATIONNAME, VERSIONNAME,
ACTIONNAME, --SUBQUEUE,
--PUBNODE, PUBSTATUS, SUBCONSTATUS,
QUEUENAME,EXTOPERATIONNAME, PROCESS_INSTANCE, CONVERSATIONID
,min(LASTUPDDTTM),max(LASTUPDDTTM)
from PSIBSUBCON_VW --
where pUBNODE<> 'PSFT_LM'
group by PUBNODE,IB_OPERATIONNAME, VERSIONNAME, ACTIONNAME,
QUEUENAME) a1
, PSOPERATION
i1, PSQUEUEDEFN
q, PSOPRVERDFNPARM
i2 left outer join PSMSGDEFN m1 on i2.MSGNAME = m1.MSGNAME
where i1.IB_OPERATIONNAME
= a1.IB_OPERATIONNAME and q.QUEUENAME = a1.QUEUENAME and i1.IB_OPERATIONNAME =
i2.IB_OPERATIONNAME
;
--
Operation Instances
select distinct a1.*,
i1.LASTUPDDTTM,
i1.DESCR,dbms_lob.substr
( i1.DESCRLONG,3000,1 ),i1.IB_SERVICENAME,i1.LASTUPDOPRID,q.LASTUPDDTTM, q.DESCR,
dbms_lob.substr (q.DESCRLONG,3000,1 ),q.LASTUPDOPRID
,m1.MSGNAME,m1.DESCR, m1.LASTUPDDTTM, m1.LASTUPDOPRID,
dbms_lob.substr (m1.DESCRLONG,3000,1 )
from
(select distinct PUBNODE, PUBSTATUS, QUEUENAME,
EXTOPERATIONNAME, IB_OPERATIONNAME ,ORIGPUBNODE, CONVERSATIONID
,min(LASTUPDDTTM),max(LASTUPDDTTM)
from PSIBPUBHDR_VW group by PUBNODE, PUBSTATUS, QUEUENAME,
EXTOPERATIONNAME, IB_OPERATIONNAME ,ORIGPUBNODE, CONVERSATIONID)
a1, PSOPERATION
i1, PSQUEUEDEFN
q, PSOPRVERDFNPARM
i2 left outer join PSMSGDEFN m1 on i2.MSGNAME = m1.MSGNAME
where i1.IB_OPERATIONNAME
= a1.IB_OPERATIONNAME and q.QUEUENAME = a1.QUEUENAME and i1.IB_OPERATIONNAME =
i2.IB_OPERATIONNAME
order by ORIGPUBNODE
,PUBNODE;
--asynch
select distinct
a1.*, i1.LASTUPDDTTM,
i1.DESCR,
dbms_lob.substr (i1.DESCRLONG,3000,1 ),i1.IB_SERVICENAME,i1.LASTUPDOPRID
-- ,q.LASTUPDDTTM,
q.DESCR,dbms_lob.substr
( q.DESCRLONG,3000,1 ),q.LASTUPDOPRID ,i2.IB_OPERATIONNAME,q.QUEUENAME
,m1.MSGNAME,m1.DESCR, m1.LASTUPDDTTM, m1.LASTUPDOPRID,
dbms_lob.substr (m1.DESCRLONG,3000,1 )
from
(
select distinct -- 'ps_amm_synclis' ,PUBLISHTIMESTAMP, LASTUPDDTTM
IB_OPERATIONNAME,VERSIONNAME, TRXTYPE,
PUBNODE,--PUBLISHER,STATUSSTRING,
DESTPUBNODE, FINALDESTNODE,
PUBROUTINGTRAIL,max(PUBLISHTIMESTAMP),min(PUBLISHTIMESTAMP),
max(LASTUPDDTTM), min(LASTUPDDTTM)
from ps_amm_synclist a
group by IB_OPERATIONNAME, VERSIONNAME, TRXTYPE, PUBNODE,
DESTPUBNODE, FINALDESTNODE, PUBROUTINGTRAIL
)
a1, PSOPERATION
i1, PSOPRVERDFNPARM
i2 left outer join PSMSGDEFN m1 on i2.MSGNAME = m1.MSGNAME
where i1.IB_OPERATIONNAME
= a1.IB_OPERATIONNAME and i1.IB_OPERATIONNAME = i2.IB_OPERATIONNAME
order by 1 ,PUBNODE;
Tuesday, 28 February 2023
PROCESSMONITOR
| Menu: | PROCESSMONITOR |
| Component: | PROCESSMONITOR |
| Page: | PMN_PRCSLIST |
Component: PROCESSMONITOR
Page: PMN_PRCSRQSTDETAIL
Menu: PROCESSMONITOR
Component: PROCESSMONITOR
Page: PMN_PRCSRQSTPARMS
Menu: PROCESSMONITOR
SELECT PRCSINSTANCE, CMDLINE, PARMLIST, WORKINGDIR, OUTDEST, ORIGPARMLIST, ORIGOUTDEST, PRCSOUTPUTDIR, PRCSPARMEXTFLAG, PRCSFILENAME FROM PSPRCSPARMS a
WHERE PRCSINSTANCE = 29596062;
Component: PROCESSMONITOR
Page: PMN_BAT_MSGLOG
Menu: PROCESSMONITOR
Component: PROCESSMONITOR
Page: PMN_BAT_TIMINGS
Menu: PROCESSMONITOR
Component: PROCESSMONITOR
Page: PMN_CDM_INDEX
Menu: PROCESSMONITOR
File List:
select PRCSINSTANCE, CONTENTID, FILENAME, CDM_FILE_TYPE, FILE_SIZE, DTTM_CREATED, CDM_ADMIN, CDM_BINARY_FILE, DESCR50, FLDNOTUSED from ps_CDM_FILELIST_VW where PRCSINSTANCE =29596062 ;
Distribute to:
select CONTENTID, PRCSINSTANCE, DISTID, DISTIDTYPE from ps_CDM_AUTH where PRCSINSTANCE= 29596062;
Rec: PMN_PRCSLIST --> Fld: PRCSNAME
SEQUENCENO, PRCSINSTANCE, JOBINSTANCE, PRCSJOBSEQ, PRCSTYPE, PRCSNAME, RUNCNTLID, RUNLOCATION, RESTARTENABLED, SERVERNAMERUN, SERVERNAMERQST, RECURNAME, OPRID, RQSTDTTM, RUNDTTM, BEGINDTTM, ENDDTTM, RUNSTATUS, RUNSTATUSDESCR, DISTSTATUS, GENPRCSTYPE, OUTDESTTYPE, OUTDESTFORMAT, PRCSRTNCD, RQSTSTATUSUPD, RQSTSTATUSVIEW, MAINJOBINSTANCE, PRCSITEMLEVEL, SCHEDULENAME, JOBNAMESRC, RECURORIGPRCSINST, P_PRCSINSTANCE, PTNONUNPRCSID
Rec: PSPRCSPARMS --> Fld: PARMLIST, WORKINGDIR
PRCSINSTANCE, CMDLINE, PARMLIST, WORKINGDIR, OUTDEST, ORIGPARMLIST, ORIGOUTDEST, PRCSOUTPUTDIR, PRCSPARMEXTFLAG, PRCSFILENAME
Rec: PSPRCSRQSTTEXT --> Fld: RQST_TEXT
PRCSINSTANCE, RQSTTEXTTYPE, RQST_TEXT
Rec: PMN_CDM_AUTH_VW --> Fld: DISTID
CONTENTID, PRCSINSTANCE, DISTIDTYPE (2 User, 3 Role, DISTIDDESCR, DISTID
PMN_CDM_AUTH_VW,DISTIDTYPE,
2,User ID,User
3,Role Name,Role
PMN_PRCSLIST,DISTSTATUS,
0,None,None
1,Scheduled,N/A
2,Processing,Processing
3,Generated,Generated
4,Unable to Post,Not Posted
5,Posted to Web,Posted
6,Delete,Delete
7,Posting,Posting
9,Pending for Approval,Pending
PMN_PRCSLIST,GENPRCSTYPE,
0,Other,Other
1,SQR,SQR
2,PeopleSoft COBOL,COBOL
3,Crystal Report,Crystal
4,Winword Macro,Winword
5,Application Engine,AppEngine
6,Cube Builder,Cube
7,nVision,nVision
8,Data Mover,Data Mover
PMN_PRCSLIST,OUTDESTFORMAT,
0,Any,Any
1,(None),(None)
10,Postscript (*.lis),PS
11,Crystal Report (*.rpt),RPT
12,Rich Text File (*.rtf),RTF
13,SQR Portable Format (*.spf),SPF
14,Text Files (*.txt),TXT
15,Other,OTHER
16,Default,Default
17,XML Format(*.xml),XML
18,Data Mover Data File (*.dat),DAT
19,Crystal Report Explorer(*.rpt),CR RPT
2,Acrobat (*.pdf),PDF
20,XMLP,XMLP
21,Atom,ATOM
22,Transformation,XFORM
23,Printer Command Lang. (*.pcl),PCL
24,PostScript (*.ps),PS
3,Comma delimited (*.csv),CSV
4,HP Format (*.lis),HP
5,HTML Documents (*.htm),HTM
6,Line Printer Format (*.lis),LP
7,Lotus 1-2-3 Files (*.wks),WKS
8,Microsoft Excel Files (*.xls),XLS
9,Microsoft Word (*.doc),DOC
PMN_PRCSLIST,OUTDESTTYPE,
0,Any,Any
1,(None),NONE
2,File,FILE
3,Printer,PRINTER
4,Window,WINDOW
5,Email,EMAIL
6,Web,WEB
7,Default,DEFAULT
8,Feed,FEED
9,IB Node,IB Node
PMN_PRCSLIST,RUNLOCATION,
2,Server,Server
PMN_PRCSLIST,RUNSTATUS,
1,Cancel,Cancel
10,Not Successful,No Success
16,Pending,Pending
17,Success With Warning,Warning
18,Blocked,Blocked
19,Restart,Restart
2,Delete,Delete
3,Error,Error
4,Hold,Hold
5,Queued,Queued
6,Initiated,Initiated
7,Processing,Processing
8,Cancelled,Cancelled
9,Success,Success
PSPRCSRQSTTEXT,RQSTTEXTTYPE,
1,Email Address,Email Adr
2,Email Subject,Subject
3,Email Text,Text
4,Email Address Expanded List,Email Lst
5,Distribution List,Dist List
6,Original Extended Parameter,Orig Parm
7,Extended Parameter,Parameter
How to find a process which has trace and trace file?
select PARMLIST,a.CONTENTID,RUNDTTM,
v.PRCSINSTANCE, v.FILENAME, CDM_FILE_TYPE, FILE_SIZE, DTTM_CREATED, CDM_ADMIN, CDM_BINARY_FILE, DESCR50, FLDNOTUSED,--v.CONTENTID,
a.* from ps_PRCSDEFN p, psprcsrqst a, ps_CDM_FILELIST_VW v where -- PARMLIST <> ' ' and
p.prcstype like 'Ap%'
-- and a.oprid = 'oprid'
and a.prcsname = p.prcsname
-- and a.prcsname not in ('TL_TIMEADMIN')
and upper(filename) like '%AET%' and a.PRCSINSTANCE = v.PRCSINSTANCE
order by a.PRCSINSTANCE desc;
Thursday, 9 February 2023
find defn ref
SELECT
a.fieldname,
a.version,
a.fieldtype,
a.length,
a.decimalpos,
a.format,
a.formatlength,
a.image_fmt,
a.formatfamily,
a.dispfmtname,
a.defcntryyr,
a.imemode,
a.kblayout,
a.objectownerid,
to_char(CAST((a.lastupddttm) AS TIMESTAMP),
'YYYY-MM-DD-HH24.MI.SS.FF'),
a.lastupdoprid,
a.fldnotused,
a.auxflagmask,
a.descrlong,
b.recname,
b.fieldname,
b.fieldnum,
b.defrecname,
b.deffieldname,
b.curctlfieldname,
b.edittable,
b.useedit,
b.useedit2,
b.subrecord,
b.subrecver,
b.setcntrlfld,
b.defguicontrol,
b.label_id,
to_char(CAST((b.lastupddttm) AS TIMESTAMP),
'YYYY-MM-DD-HH24.MI.SS.FF'),
b.lastupdoprid,
b.timezoneuse,
b.timezonefieldname,
b.reltmdtfieldname,
b.currctluse,
c.recname,
c.fieldcount,
c.indexcount,
c.ddlcount,
c.version,
c.auditrecname,
c.recuse,
c.rectype,
c.setcntrlfld,
c.rellangrecname,
c.optdelrecname,
c.recdescr,
c.parentrecname,
c.qrysecrecname,
c.sqltablename,
c.buildseqno,
c.opttrigflag,
c.objectownerid,
to_char(CAST((c.lastupddttm) AS TIMESTAMP),
'YYYY-MM-DD-HH24.MI.SS.FF'),
c.lastupdoprid,
c.systemidfieldname,
c.timestampfieldname,
c.auxflagmask,
c.descrlong,
d.pnlname,
d.pnlfldid,
d.fieldnum,
d.pnlfieldname,
d.fieldtype,
d.editsize,
d.dsplformat,
d.fieldleft,
d.fieldtop,
d.fieldright,
d.fieldbottom,
d.editlblleft,
d.editlbltop,
d.editlblright,
d.editlblbottom,
d.dsplfill,
d.lbltype,
d.lblloc,
d.lblpadsize,
d.usedefaultlabel,
d.label_id,
d.lbltext,
d.fielduse,
d.fieldusetmp,
d.deferproc,
d.occurslevel,
d.recname,
d.fieldname,
d.onvalue,
d.offvalue,
d.assocfieldnum,
d.occurscount1,
d.occursoffset1,
d.occurscount2,
d.occursoffset2,
d.occurscount3,
d.occursoffset3,
d.subpnlname,
d.subpnlver,
d.fieldstyle,
d.labelstyle,
d.fieldsizetype,
d.labelsizetype,
d.prcstype,
d.prcsname,
d.promptfield,
d.formatfamily,
d.dispfmtname,
d.popupmenu,
d.treectrlid,
d.treectrltype,
d.multirectree,
d.nodecount,
d.grdcolumncount,
d.grdshowcolhdg,
d.grdshowrowhdg,
d.grdoddrowstyle,
d.grdevenrowstyle,
d.grdactivetabstyle,
d.grdinactivetabstyl,
d.grdnavbarstyle,
d.grdlabelstyle,
d.grdlblmsgset,
d.grdlblmsgnum,
d.grdlblalign,
d.grdacttype,
d.grdallowcolsort,
d.tabenable,
d.pbdisplaytype,
d.opennewwindow,
d.urldynamic,
d.url_id,
d.gotoportalname,
d.gotonodename,
d.gotomenuname,
d.gotopnlgrpname,
d.gotomktname,
d.gotopnlname,
d.gotopnlaction,
d.srchbypnldata,
d.scrollaction,
d.toolaction,
d.contname,
d.contnameover,
d.contnamedisable,
d.ptlblimgcollapse,
d.ptlblimgexpand,
d.ptadjhiddenfields,
d.ptcollapsedataarea,
d.ptdfltviewexpanded,
d.pthidefields,
d.showcolhiderows,
d.ptlebexpandfield,
d.showtabcntlbtn,
d.selindicatortype,
d.secureinvisible,
d.enableasanchor,
d.urlencodedbyapp,
e.pnlname,
e.pnlfldid,
e.fieldnum,
e.pnlfieldname,
e.fieldtype,
e.editsize,
e.dsplformat,
e.fieldleft,
e.fieldtop,
e.fieldright,
e.fieldbottom,
e.editlblleft,
e.editlbltop,
e.editlblright,
e.editlblbottom,
e.dsplfill,
e.lbltype,
e.lblloc,
e.lblpadsize,
e.usedefaultlabel,
e.label_id,
e.lbltext,
e.fielduse,
e.fieldusetmp,
e.deferproc,
e.occurslevel,
e.recname,
e.fieldname,
e.onvalue,
e.offvalue,
e.assocfieldnum,
e.occurscount1,
e.occursoffset1,
e.occurscount2,
e.occursoffset2,
e.occurscount3,
e.occursoffset3,
e.subpnlname,
e.subpnlver,
e.fieldstyle,
e.labelstyle,
e.fieldsizetype,
e.labelsizetype,
e.prcstype,
e.prcsname,
e.promptfield,
e.formatfamily,
e.dispfmtname,
e.popupmenu,
e.treectrlid,
e.treectrltype,
e.multirectree,
e.nodecount,
e.grdcolumncount,
e.grdshowcolhdg,
e.grdshowrowhdg,
e.grdoddrowstyle,
e.grdevenrowstyle,
e.grdactivetabstyle,
e.grdinactivetabstyl,
e.grdnavbarstyle,
e.grdlabelstyle,
e.grdlblmsgset,
e.grdlblmsgnum,
e.grdlblalign,
e.grdacttype,
e.grdallowcolsort,
e.tabenable,
e.pbdisplaytype,
e.opennewwindow,
e.urldynamic,
e.url_id,
e.gotoportalname,
e.gotonodename,
e.gotomenuname,
e.gotopnlgrpname,
e.gotomktname,
e.gotopnlname,
e.gotopnlaction,
e.srchbypnldata,
e.scrollaction,
e.toolaction,
e.contname,
e.contnameover,
e.contnamedisable,
e.ptlblimgcollapse,
e.ptlblimgexpand,
e.ptadjhiddenfields,
e.ptcollapsedataarea,
e.ptdfltviewexpanded,
e.pthidefields,
e.showcolhiderows,
e.ptlebexpandfield,
e.showtabcntlbtn,
e.selindicatortype,
e.secureinvisible,
e.enableasanchor,
e.urlencodedbyapp,
f.pnlname,
f.version,
f.pnltype,
f.fieldcount,
f.maxpnlfldid,
f.gridhorz,
f.gridvert,
f.helpcontextnum,
f.paneltop,
f.panelleft,
f.panelright,
f.panelbottom,
f.pnlstyle,
f.stylesheetname,
f.ffstylesheetname,
f.pnluse,
f.deferproc,
f.descr,
f.popupmenu,
f.license_code,
to_char(CAST((f.lastupddttm) AS TIMESTAMP),
'YYYY-MM-DD-HH24.MI.SS.FF'),
f.lastupdoprid,
f.objectownerid,
f.ffstyledesktop,
f.ffstylephone,
f.ffstyletablet,
f.pnlusetemp,
f.ffstylemedium,
f.ffstyleexlarge,
f.descrlong,
g.pnlname,
g.version,
g.pnltype,
g.fieldcount,
g.maxpnlfldid,
g.gridhorz,
g.gridvert,
g.helpcontextnum,
g.paneltop,
g.panelleft,
g.panelright,
g.panelbottom,
g.pnlstyle,
g.stylesheetname,
g.ffstylesheetname,
g.pnluse,
g.deferproc,
g.descr,
g.popupmenu,
g.license_code,
to_char(CAST((g.lastupddttm) AS TIMESTAMP),
'YYYY-MM-DD-HH24.MI.SS.FF'),
g.lastupdoprid,
g.objectownerid,
g.ffstyledesktop,
g.ffstylephone,
g.ffstyletablet,
g.pnlusetemp,
g.ffstylemedium,
g.ffstyleexlarge,
g.descrlong,
h.pnlgrpname,
h.market,
h.pnlname,
h.subitemnum,
h.itemname,
h.itemlabel,
h.foldertablabel,
h.hidden,
i.pnlgrpname,
i.market,
i.pnlname,
i.subitemnum,
i.itemname,
i.itemlabel,
i.foldertablabel,
i.hidden,
j.pnlgrpname,
j.market,
j.version,
j.actions,
j.descr,
j.addsrchrecname,
j.searchrecname,
j.searchpnlname,
j.loadloc,
j.saveloc,
j.disablesave,
j.objectownerid,
to_char(CAST((j.lastupddttm) AS TIMESTAMP),
'YYYY-MM-DD-HH24.MI.SS.FF'),
j.lastupdoprid,
j.primaryaction,
j.dfltaction,
j.dfltsrchtype,
j.deferproc,
j.expentryproc,
j.reqsecuressl,
j.inclnavigation,
j.forcesearch,
j.allowactmodesel,
j.pnlnavflags,
j.tbarbtns,
j.showtbar,
j.addlinkmsgset,
j.addlinkmsgnum,
j.srchlinkmsgset,
j.srchlinkmsgnum,
j.srchtextmsgset,
j.srchtextmsgnum,
j.wsrpcompliant,
j.fluidmode,
j.layoutmode,
j.incfooter,
j.incside,
j.incheader,
j.incsearch,
j.smallffopt,
j.comp_type,
j.pnlgrpuse,
j.descrlong,
l.menuname,
l.barname,
l.itemname,
l.itemnum,
l.itemtype,
l.pnlgrpname,
l.market,
l.barlabel,
l.itemlabel,
l.xfercount,
l.searchrecname,
m.menuname,
m.barname,
m.itemname,
m.itemnum,
m.itemtype,
m.pnlgrpname,
m.market,
m.barlabel,
m.itemlabel,
m.xfercount,
m.searchrecname,
n.menuname,
n.version,
n.menugroup,
n.installed,
n.grouporder,
n.menuorder,
n.groupsep,
n.menusep,
n.menulabel,
n.menutype,
n.descr,
n.objectownerid,
to_char(CAST((n.lastupddttm) AS TIMESTAMP),
'YYYY-MM-DD-HH24.MI.SS.FF'),
n.lastupdoprid,
n.descrlong,
o.menuname,
o.version,
o.menugroup,
o.installed,
o.grouporder,
o.menuorder,
o.groupsep,
o.menusep,
o.menulabel,
o.menutype,
o.descr,
o.objectownerid,
to_char(CAST((o.lastupddttm) AS TIMESTAMP),
'YYYY-MM-DD-HH24.MI.SS.FF'),
o.lastupdoprid,
o.descrlong,
p.portal_name,
p.portal_reftype,
p.portal_objname,
p.portal_urltext,
q.portal_name,
q.portal_reftype,
q.portal_objname,
q.portal_uri_seg1,
q.portal_uri_seg2,
q.portal_urltext
FROM
(
(
(
(
(
(
(
(
(
(
(
(
(
(
(
(
psdbfield a
LEFT OUTER JOIN psrecfield b ON a.fieldname = b.fieldname
)
LEFT OUTER JOIN psrecdefn c ON b.recname = c.recname
)
LEFT OUTER JOIN pspnlfield d ON b.recname = d.recname AND b.fieldname = d.fieldname
)
LEFT OUTER JOIN pspnldefn f ON d.pnlname = f.pnlname
)
LEFT OUTER JOIN pspnlgroup h ON f.pnlname = h.pnlname
)
LEFT OUTER JOIN pspnlgrpdefn j ON h.pnlgrpnamJt = j.market
)
LEFT OUTER JOIN psmenuitem l ON j.pnlgrpname = l.pnlgrpname AND j.market = l.market
)
LEFT OUTER JOIN psmenudefn n ON l.menuname = n.menuname
)
LEFT OUTER JOIN psprsmdefn p ON p.portal_uri_seg2 = j.pnlgrpname
)
LEFT OUTER JOIN pspnlfield e ON a.fieldname = e.fieldname
)
LEFT OUTER JOIN pspnldefn g ON e.pnlname = g.pnlname
)
LEFT OUTER JOIN pspnlgroup i ON g.pnlname = i.pnlname
)
LEFT OUTER JOIN pspnlgrpdefn k ON i.pnlgrpname = k.pnlgrpname AND i.market = k.market
)
LEFT OUTER JOIN psmenuitem m ON k.pnlgrpname = m.pnlgrpname AND k.market = m.market
)
LEFT OUTER JOIN psmenudefn o ON m.menuname = o.menuname
)
LEFT OUTER JOIN psprsmdefn q ON q.portal_uri_seg2 = k.pnlgrpname
)
Subscribe to:
Comments (Atom)