Category Archives: SQL

Helpful SQL for Integration Broker Monitoring

#1 Count of messages older than 90 days.
#2 Messages older than 90 days.
#3 Counts/per message.


SELECT COUNT(*) FROM PSAPMSGPUBHDR WHERE CREATEDTTM < SYSDATE-90;
SELECT * FROM PSAPMSGPUBHDR WHERE CREATEDTTM < SYSDATE-90;
SELECT DISTINCT(IB_OPERATIONNAME),COUNT(*) FROM PSAPMSGPUBHDR GROUP BY IB_OPERATIONNAME;

SELECT COUNT(*) FROM PSAPMSGPUBCON WHERE CREATEDTTM < SYSDATE-90;
SELECT * FROM PSAPMSGPUBCON WHERE CREATEDTTM < SYSDATE-90;
SELECT DISTINCT(IB_OPERATIONNAME),COUNT(*) FROM PSAPMSGPUBCON GROUP BY IB_OPERATIONNAME;

SELECT COUNT(*) FROM PSAPMSGSUBCON WHERE CREATEDTTM < SYSDATE-90;
SELECT * FROM PSAPMSGSUBCON WHERE CREATEDTTM < SYSDATE-90;
SELECT DISTINCT(IB_OPERATIONNAME),COUNT(*) FROM PSAPMSGSUBCON GROUP BY IB_OPERATIONNAME;

SELECT COUNT(*) FROM PSIBLOGHDR WHERE PUBLISHTIMESTAMP < SYSDATE-90;
SELECT * FROM PSIBLOGHDR WHERE PUBLISHTIMESTAMP < SYSDATE-90;
SELECT DISTINCT(IB_OPERATIONNAME),COUNT(*) FROM PSIBLOGHDR GROUP BY IB_OPERATIONNAME;

8.55 Tile Query

Useful query to find owners of Tiles created in 8.55


SELECT CREATEOPRID,
LASTUPDOPRID,
LASTUPDDTTM,
PTPPB_GROUPLET_ID,
DESCR,
PTPPB_DATATYPE_ID
FROM PS_PTPPB_GROUPLET
ORDER BY CREATEOPRID ASC;

Update PeopleSoft routing destination nodes via SQL

PeopleTools 8.52
We had a situation where we needed to update the routing destination nodes in PeopleSoft via SQL instead of doing it in PIA. Only do this if you aren’t using a WSDL with the routing and its going to a JMS queue. The statements must be run in order.


UPDATE PSIBRTNGSUBDEFN SET RECEIVERNODENAME='NEW_NODE_NAME' WHERE ROUTINGDEFNNAME IN (SELECT ROUTINGDEFNNAME FROM PSIBRTNGDEFN WHERE IB_OPERATIONNAME ='DEPT_FULLSYNC' AND RECEIVERNODENAME='OLD_NODE_NAME');

UPDATE PSRTNGDFNPARM SET RECEIVERNODENAME='NEW_NODE_NAME' WHERE ROUTINGDEFNNAME IN (SELECT ROUTINGDEFNNAME FROM PSIBRTNGDEFN WHERE IB_OPERATIONNAME ='DEPT_FULLSYNC' AND RECEIVERNODENAME='OLD_NODE_NAME');

UPDATE PSIBRTNGDEFN SET RECEIVERNODENAME='NEW_NODE_NAME' WHERE IB_OPERATIONNAME='DEPT_FULLSYNC' AND RECEIVERNODENAME='OLD_NODE_NAME';

Query – PeopleSoft Jobs & processes that took more than 60 minutes to run

The following query can be used to identify any long running processes from the process request table.

It identifies any processes that took more than 60 minutes to run.

WITH GOT_RUN_MINUTES AS
(SELECT SERVERNAMERUN,
PRCSINSTANCE,
ORIGPRCSINSTANCE,
PRCSTYPE,
PRCSJOBNAME,
PRCSNAME,
RUNDTTM,
RQSTDTTM,
OPRID,
(SELECT XLATSHORTNAME
FROM PSXLATITEM
WHERE FIELDNAME = 'RUNSTATUS'
AND FIELDVALUE = RUNSTATUS
) AS RUNSTATUS,
BEGINDTTM,
ENDDTTM,
(TO_DATE (TO_CHAR (ENDDTTM, 'dd/mm/yyyy hh24:mi:ss'), 'dd/mm/yyyy hh24:mi:ss' )-TO_DATE (TO_CHAR (BEGINDTTM, 'dd/mm/yyyy hh24:mi:ss'), 'dd/mm/yyyy hh24:mi:ss' ))*(24*60) AS MINUTES
FROM PSPRCSRQST
WHERE RQSTDTTM > SYSDATE-1
ORDER BY RQSTDTTM DESC
)
SELECT SERVERNAMERUN,
PRCSINSTANCE,
ORIGPRCSINSTANCE,
PRCSTYPE,
PRCSJOBNAME,
PRCSNAME,
RUNDTTM,
RQSTDTTM,
OPRID,
RUNSTATUS,
BEGINDTTM,
ENDDTTM,
MINUTES
FROM GOT_RUN_MINUTES
WHERE MINUTES > '60';

Viewing Locked Objects in Oracle 11g

-- view all currently locked objects:
SELECT username U_NAME, owner OBJ_OWNER,
object_name, object_type, s.osuser,
DECODE(l.block,
0, 'Not Blocking',
1, 'Blocking',
2, 'Global') STATUS,
DECODE(v.locked_mode,
0, 'None',
1, 'Null',
2, 'Row-S (SS)',
3, 'Row-X (SX)',
4, 'Share',
5, 'S/Row-X (SSX)',
6, 'Exclusive', TO_CHAR(lmode)
) MODE_HELD
FROM gv$locked_object v, dba_objects d,
gv$lock l, gv$session s
WHERE v.object_id = d.object_id
AND (v.object_id = l.id1)
AND v.session_id = s.sid
ORDER BY username, session_id;

-- list objects that have been
-- locked for 60 seconds or more:
SELECT SUBSTR(TO_CHAR(w.session_id),1,5) WSID, p1.spid WPID,
SUBSTR(s1.username,1,12) "WAITING User",
SUBSTR(s1.osuser,1,8) "OS User",
SUBSTR(s1.program,1,20) "WAITING Program",
s1.client_info "WAITING Client",
SUBSTR(TO_CHAR(h.session_id),1,5) HSID, p2.spid HPID,
SUBSTR(s2.username,1,12) "HOLDING User",
SUBSTR(s2.osuser,1,8) "OS User",
SUBSTR(s2.program,1,20) "HOLDING Program",
s2.client_info "HOLDING Client",
o.object_name "HOLDING Object"
FROM gv$process p1, gv$process p2, gv$session s1,
gv$session s2, dba_locks w, dba_locks h, dba_objects o
WHERE w.last_convert > 60
AND h.mode_held != 'None'
AND h.mode_held != 'Null'
AND w.mode_requested != 'None'
AND s1.row_wait_obj# = o.object_id
AND w.lock_type(+) = h.lock_type
AND w.lock_id1(+) = h.lock_id1
AND w.lock_id2 (+) = h.lock_id2
AND w.session_id = s1.sid (+)
AND h.session_id = s2.sid (+)
AND s1.paddr = p1.addr (+)
AND s2.paddr = p2.addr (+)
ORDER BY w.last_convert DESC;

Checking for changed objects in PeopleSoft by LASTUPDOPRID

If you want to check for objects changed in your PeopleSoft database via sql you can run the statements below to find those objects. This can be quicker than doing a full database compare using Application Designer.

select ACEMODELID,DESCR,lastupddttm,lastupdoprid From PSACEMDLDEFN where lastupddttm > to_date('01012000','ddmmyyyy') and lastupdoprid <> 'PPLSOFT';
select ACTIVITYNAME,' ',lastupddttm,lastupdoprid From PSACTIVITYDEFN where lastupddttm > to_date('01012000','ddmmyyyy') and lastupdoprid <> 'PPLSOFT' ;
select AE_APPLID,DESCR,lastupddttm,lastupdoprid From PSAEAPPLDEFN where lastupddttm > to_date('01012000','ddmmyyyy') and lastupdoprid <> 'PPLSOFT' ;
select AE_APPLID,' ',lastupddttm,lastupdoprid From PSAESECTDEFN where lastupddttm > to_date('01012000','ddmmyyyy') and lastupdoprid <> 'PPLSOFT' ;
select AE_SECTION,' ',lastupddttm,lastupdoprid From PSAESECTDEFN where lastupddttm > to_date('01012000','ddmmyyyy') and lastupdoprid <> 'PPLSOFT' ;
select PSARCH_OBJECT,DESCR,lastupddttm,lastupdoprid From PSARCHOBJDEFN where lastupddttm > to_date('01012000','ddmmyyyy') and lastupdoprid <> 'PPLSOFT' ;
select BCNAME,DESCR,lastupddttm,lastupdoprid From PSBCDEFN where lastupddttm > to_date('01012000','ddmmyyyy') and lastupdoprid <> 'PPLSOFT' ;
select BUSPROCNAME,' ',lastupddttm,lastupdoprid From PSBUSPROCDEFN where lastupddttm > to_date('01012000','ddmmyyyy') and lastupdoprid <> 'PPLSOFT' ;
select CERTALIAS,' ',lastupddttm,lastupdoprid From PSCERTDEFN where lastupddttm > to_date('01012000','ddmmyyyy') and lastupdoprid <> 'PPLSOFT' ;
select CERTTYPE,' ',lastupddttm,lastupdoprid From PSCERTDEFN where lastupddttm > to_date('01012000','ddmmyyyy') and lastupdoprid <> 'PPLSOFT' ;
select CHNLNAME,DESCR,lastupddttm,lastupdoprid From PSCHNLDEFN where lastupddttm > to_date('01012000','ddmmyyyy') and lastupdoprid <> 'PPLSOFT' ;
select CLASSID,' ',lastupddttm,lastupdoprid From PSCLASSDEFN where lastupddttm > to_date('01012000','ddmmyyyy') and lastupdoprid <> 'PPLSOFT' ;
select COLORNAME,' ',lastupddttm,lastupdoprid From PSCOLORDEFN where lastupddttm > to_date('01012000','ddmmyyyy') and lastupdoprid <> 'PPLSOFT' ;
select OPRID,' ',lastupddttm,lastupdoprid From PSCOLORDEFN where lastupddttm > to_date('01012000','ddmmyyyy') and lastupdoprid <> 'PPLSOFT' ;
select CONQRSNAME,DESCR,lastupddttm,lastupdoprid From PSCONQRSDEFN where lastupddttm > to_date('01012000','ddmmyyyy') and lastupdoprid <> 'PPLSOFT' ;
select OPRID,DESCR,lastupddttm,lastupdoprid From PSCONQRSDEFN where lastupddttm > to_date('01012000','ddmmyyyy') and lastupdoprid <> 'PPLSOFT' ;
select ALTCONTNUM,DESCR,lastupddttm,lastupdoprid From PSCONTDEFN where lastupddttm > to_date('01012000','ddmmyyyy') and lastupdoprid <> 'PPLSOFT' ;
select CONTNAME,DESCR,lastupddttm,lastupdoprid From PSCONTDEFN where lastupddttm > to_date('01012000','ddmmyyyy') and lastupdoprid <> 'PPLSOFT' ;
select CONTTYPE,DESCR,lastupddttm,lastupdoprid From PSCONTDEFN where lastupddttm > to_date('01012000','ddmmyyyy') and lastupdoprid <> 'PPLSOFT' ;
select FILEID,' ',lastupddttm,lastupdoprid From PSFILEDEFN where lastupddttm > to_date('01012000','ddmmyyyy') and lastupdoprid <> 'PPLSOFT' ;
select FILEREFNAME,' ',lastupddttm,lastupdoprid From PSFILEREDEFN where lastupddttm > to_date('01012000','ddmmyyyy') and lastupdoprid <> 'PPLSOFT' ;
select FILEREFTYPECODE,' ',lastupddttm,lastupdoprid From PSFILEREDEFN where lastupddttm > to_date('01012000','ddmmyyyy') and lastupdoprid <> 'PPLSOFT' ;
select FLDDEFNNAME,DESCR,lastupddttm,lastupdoprid From PSFLDDEFN where lastupddttm > to_date('01012000','ddmmyyyy') and lastupdoprid <> 'PPLSOFT' ;
select FORMATFAMILY,DESCR,lastupddttm,lastupdoprid From PSFMTDEFN where lastupddttm > to_date('01012000','ddmmyyyy') and lastupdoprid <> 'PPLSOFT' ;
select HOLIDAYNAME,' ',lastupddttm,lastupdoprid From PSHOLIDAYDEFN where lastupddttm > to_date('01012000','ddmmyyyy') and lastupdoprid <> 'PPLSOFT' ;
select EFFDT,DESCR,lastupddttm,lastupdoprid From PSIBRTNGDEFN where lastupddttm > to_date('01012000','ddmmyyyy') and lastupdoprid <> 'PPLSOFT' ;
select ROUTINGDEFNNAME,DESCR,lastupddttm,lastupdoprid From PSIBRTNGDEFN where lastupddttm > to_date('01012000','ddmmyyyy') and lastupdoprid <> 'PPLSOFT' ;
select IMPNAME,' ',lastupddttm,lastupdoprid From PSIMPDEFN where lastupddttm > to_date('01012000','ddmmyyyy') and lastupdoprid <> 'PPLSOFT' ;
select IONAME,IODESCR,lastupddttm,lastupdoprid From PSIODEFN where lastupddttm > to_date('01012000','ddmmyyyy') and lastupdoprid <> 'PPLSOFT' ;
select OPRID,' ',lastupddttm,lastupdoprid From PSJPUPDEFN where lastupddttm > to_date('01012000','ddmmyyyy') and lastupdoprid <> 'PPLSOFT' ;
select PTJPLT_APPNAME,' ',lastupddttm,lastupdoprid From PSJPUPDEFN where lastupddttm > to_date('01012000','ddmmyyyy') and lastupdoprid <> 'PPLSOFT' ;
select PTJPLT_NAME,' ',lastupddttm,lastupdoprid From PSJPUPDEFN where lastupddttm > to_date('01012000','ddmmyyyy') and lastupdoprid <> 'PPLSOFT' ;
select MENUNAME,DESCR,lastupddttm,lastupdoprid From PSMENUDEFN where lastupddttm > to_date('01012000','ddmmyyyy') and lastupdoprid <> 'PPLSOFT' ;
select MOBILEPAGENAME,DESCR,lastupddttm,lastupdoprid From PSMPDEFN where lastupddttm > to_date('01012000','ddmmyyyy') and lastupdoprid <> 'PPLSOFT' ;
select MSGNAME,DESCR,lastupddttm,lastupdoprid From PSMSGDEFN where lastupddttm > to_date('01012000','ddmmyyyy') and lastupdoprid <> 'PPLSOFT' ;
select MSGNODENAME,DESCR,lastupddttm,lastupdoprid From PSMSGNODEDEFN where lastupddttm > to_date('01012000','ddmmyyyy') and lastupdoprid <> 'PPLSOFT' ;
select OPRID,' ',lastupddttm,lastupdoprid From PSOPRDEFN where lastupddttm > to_date('01012000','ddmmyyyy') and lastupdoprid <> 'PPLSOFT' ;
select PACKAGEID,DESCR,lastupddttm,lastupdoprid From PSPACKAGEDEFN where lastupddttm > to_date('01012000','ddmmyyyy') and lastupdoprid <> 'PPLSOFT' ;
select PACKAGEROOT,DESCR,lastupddttm,lastupdoprid From PSPACKAGEDEFN where lastupddttm > to_date('01012000','ddmmyyyy') and lastupdoprid <> 'PPLSOFT' ;
select QUALIFYPATH,DESCR,lastupddttm,lastupdoprid From PSPACKAGEDEFN where lastupddttm > to_date('01012000','ddmmyyyy') and lastupdoprid <> 'PPLSOFT' ;
select PM_SYSTEMID,' ',lastupddttm,lastupdoprid From PSPMSYSDEFN where lastupddttm > to_date('01012000','ddmmyyyy') and lastupdoprid <> 'PPLSOFT' ;
select PNLNAME,DESCR,lastupddttm,lastupdoprid From PSPNLDEFN where lastupddttm > to_date('01012000','ddmmyyyy') and lastupdoprid <> 'PPLSOFT' ;
select MARKET,DESCR,lastupddttm,lastupdoprid From PSPNLGRPDEFN where lastupddttm > to_date('01012000','ddmmyyyy') and lastupdoprid <> 'PPLSOFT' ;
select PNLGRPNAME,DESCR,lastupddttm,lastupdoprid From PSPNLGRPDEFN where lastupddttm > to_date('01012000','ddmmyyyy') and lastupdoprid <> 'PPLSOFT' ;
select PORTAL_NAME,' ',lastupddttm,lastupdoprid From PSPRDMDEFN where lastupddttm > to_date('01012000','ddmmyyyy') and lastupdoprid <> 'PPLSOFT' ;
select PROJECTNAME,PROJECTDESCR,lastupddttm,lastupdoprid From PSPROJECTDEFN where lastupddttm > to_date('01012000','ddmmyyyy') and lastupdoprid <> 'PPLSOFT' ;
select PORTAL_NAME,' ',lastupddttm,lastupdoprid From PSPRSMDEFN where lastupddttm > to_date('01012000','ddmmyyyy') and lastupdoprid <> 'PPLSOFT' ;
select PORTAL_OBJNAME,' ',lastupddttm,lastupdoprid From PSPRSMDEFN where lastupddttm > to_date('01012000','ddmmyyyy') and lastupdoprid <> 'PPLSOFT' ;
select PORTAL_REFTYPE,' ',lastupddttm,lastupdoprid From PSPRSMDEFN where lastupddttm > to_date('01012000','ddmmyyyy') and lastupdoprid <> 'PPLSOFT' ;
select OPRID,' ',lastupddttm,lastupdoprid From PSPRUFDEFN where lastupddttm > to_date('01012000','ddmmyyyy') and lastupdoprid <> 'PPLSOFT' ;
select PORTAL_LABEL,' ',lastupddttm,lastupdoprid From PSPRUFDEFN where lastupddttm > to_date('01012000','ddmmyyyy') and lastupdoprid <> 'PPLSOFT' ;
select PORTAL_NAME,' ',lastupddttm,lastupdoprid From PSPRUFDEFN where lastupddttm > to_date('01012000','ddmmyyyy') and lastupdoprid <> 'PPLSOFT' ;
select PORTAL_REFTYPE,' ',lastupddttm,lastupdoprid From PSPRUFDEFN where lastupddttm > to_date('01012000','ddmmyyyy') and lastupdoprid <> 'PPLSOFT' ;
select OPRID,' ',lastupddttm,lastupdoprid From PSPRUHDEFN where lastupddttm > to_date('01012000','ddmmyyyy') and lastupdoprid <> 'PPLSOFT' ;
select PORTAL_NAME,' ',lastupddttm,lastupdoprid From PSPRUHDEFN where lastupddttm > to_date('01012000','ddmmyyyy') and lastupdoprid <> 'PPLSOFT' ;
select OPRID,DESCR,lastupddttm,lastupdoprid From PSQRYDEFN where lastupddttm > to_date('01012000','ddmmyyyy') and lastupdoprid <> 'PPLSOFT' ;
select QRYNAME,DESCR,lastupddttm,lastupdoprid From PSQRYDEFN where lastupddttm > to_date('01012000','ddmmyyyy') and lastupdoprid <> 'PPLSOFT' ;
select QUEUENAME,DESCR,lastupddttm,lastupdoprid From PSQUEUEDEFN where lastupddttm > to_date('01012000','ddmmyyyy') and lastupdoprid <> 'PPLSOFT' ;
select RECNAME,RECDESCR,lastupddttm,lastupdoprid From PSRECDEFN where lastupddttm > to_date('01012000','ddmmyyyy') and lastupdoprid <> 'PPLSOFT' ;
select ROLENAME,DESCR,lastupddttm,lastupdoprid From PSROLEDEFN where lastupddttm > to_date('01012000','ddmmyyyy') and lastupdoprid <> 'PPLSOFT' ;
select SQLID,' ',lastupddttm,lastupdoprid From PSSQLDEFN where lastupddttm > to_date('01012000','ddmmyyyy') and lastupdoprid <> 'PPLSOFT' ;
select SQLTYPE,' ',lastupddttm,lastupdoprid From PSSQLDEFN where lastupddttm > to_date('01012000','ddmmyyyy') and lastupdoprid <> 'PPLSOFT' ;
select STYLENAME,' ',lastupddttm,lastupdoprid From PSSTYLEDEFN where lastupddttm > to_date('01012000','ddmmyyyy') and lastupdoprid <> 'PPLSOFT' ;
select STYLESHEETNAME,DESCR,lastupddttm,lastupdoprid From PSSTYLSHEETDEFN where lastupddttm > to_date('01012000','ddmmyyyy') and lastupdoprid <> 'PPLSOFT' ;
select MSGNAME,' ',lastupddttm,lastupdoprid From PSSUBDEFN where lastupddttm > to_date('01012000','ddmmyyyy') and lastupdoprid <> 'PPLSOFT' ;
select SUBNAME,' ',lastupddttm,lastupdoprid From PSSUBDEFN where lastupddttm > to_date('01012000','ddmmyyyy') and lastupdoprid <> 'PPLSOFT' ;
select OPRID,' ',lastupddttm,lastupdoprid From PSTOOLBARDEFN where lastupddttm > to_date('01012000','ddmmyyyy') and lastupdoprid <> 'PPLSOFT' ;
select EFFDT,DESCR,lastupddttm,lastupdoprid From PSTREEDEFN where lastupddttm > to_date('01012000','ddmmyyyy') and lastupdoprid <> 'PPLSOFT' ;
select SETCNTRLVALUE,DESCR,lastupddttm,lastupdoprid From PSTREEDEFN where lastupddttm > to_date('01012000','ddmmyyyy') and lastupdoprid <> 'PPLSOFT' ;
select SETID,DESCR,lastupddttm,lastupdoprid From PSTREEDEFN where lastupddttm > to_date('01012000','ddmmyyyy') and lastupdoprid <> 'PPLSOFT' ;
select TREE_NAME,DESCR,lastupddttm,lastupdoprid From PSTREEDEFN where lastupddttm > to_date('01012000','ddmmyyyy') and lastupdoprid <> 'PPLSOFT' ;
select FILEREFTYPECODE,' ',lastupddttm,lastupdoprid From PSTYPECODEDEFN where lastupddttm > to_date('01012000','ddmmyyyy') and lastupdoprid <> 'PPLSOFT' ;
select URL_ID,DESCR,lastupddttm,lastupdoprid From PSURLDEFN where lastupddttm > to_date('01012000','ddmmyyyy') and lastupdoprid <> 'PPLSOFT' ;
select OPTN_CATEGORY_LVL,DESCR,lastupddttm,lastupdoprid From PSUSEROPTNDEFN where lastupddttm > to_date('01012000','ddmmyyyy') and lastupdoprid <> 'PPLSOFT' ;
select USEROPTN,DESCR,lastupddttm,lastupdoprid From PSUSEROPTNDEFN where lastupddttm > to_date('01012000','ddmmyyyy') and lastupdoprid <> 'PPLSOFT' ;
select OPRID,' ',lastupddttm,lastupdoprid From PSWSRPCPHDEFN where lastupddttm > to_date('01012000','ddmmyyyy') and lastupdoprid <> 'PPLSOFT' ;
select PTWSRPPORTLETNAME,' ',lastupddttm,lastupdoprid From PSWSRPCPHDEFN where lastupddttm > to_date('01012000','ddmmyyyy') and lastupdoprid <> 'PPLSOFT' ;
select PTWSRPPRODUCERNAME,' ',lastupddttm,lastupdoprid From PSWSRPCPHDEFN where lastupddttm > to_date('01012000','ddmmyyyy') and lastupdoprid <> 'PPLSOFT' ;
select PTWSRPPORTLETNAME,PTWSRP_PLTDESCR,lastupddttm,lastupdoprid From PSWSRPPLTDEFN where lastupddttm > to_date('01012000','ddmmyyyy') and lastupdoprid <> 'PPLSOFT' ;
select PTWSRPPRODUCERNAME,PTWSRP_PLTDESCR,lastupddttm,lastupdoprid From PSWSRPPLTDEFN where lastupddttm > to_date('01012000','ddmmyyyy') and lastupdoprid <> 'PPLSOFT' ;
select PTWSRPPRODUCERNAME,' ',lastupddttm,lastupdoprid From PSWSRPPRDDEFN where lastupddttm > to_date('01012000','ddmmyyyy') and lastupdoprid <> 'PPLSOFT' ;
select REPORT_DEFN_ID,DESCR,lastupddttm,lastupdoprid From PSXPRPTDEFN where lastupddttm > to_date('01012000','ddmmyyyy') and lastupdoprid <> 'PPLSOFT' ;
select TMPLDEFN_ID,DESCR,lastupddttm,lastupdoprid From PSXPTMPLDEFN where lastupddttm > to_date('01012000','ddmmyyyy') and lastupdoprid <> 'PPLSOFT' ;