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

 ;