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

 ;

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

      

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

;;

select ROUTINGDEFNNAME, EFFDT, VERSION, EFF_STATUS, SENDERNODENAME, RECEIVERNODENAME, RTNGTYPE, IB_DELAYPROCESSING, IB_RESTMETHOD, IB_SYNCHNONBLOCK, IB_ACCEPT_COMP, IB_SCHEMA_DETAILS, IB_UNORDER_SEGMENT, IB_OPERATIONNAME, VERSIONNAME, CONNOVERRIDE, CONNGATEWAYID, CONNID, LOGMSGDTLFLG, IB_DELIVERYMODE, ONSNDHDLRNAME, ONRCVHDLRNAME, ONPREHDLRNAME, ONPOSTHDLRNAME, GENERATED, IB_ARR_SECURE_EP, IB_LOCALIDFLG, LASTUPDDTTM, LASTUPDOPRID, OBJECTOWNERID, DESCR, DESCRLONG

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;

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

Xlats

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;

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

    )