HR_OBD_AG_VW (SQL View)	Index Back
OBD AG Search Def OnBoarding AG search definition additional AG data Returns AG due date calculated as: Null - if no step has a due date. (Item count = 0) Earliest step due date. (Item count = count of all steps with a due date)

HR_OBD_AG_VW

(SQL View)

OBD AG Search Def

OnBoarding AG search definition additional AG data Returns AG due date calculated as: Null - if no step has a due date. (Item count = 0) Earliest step due date. (Item count = count of all steps with a due date)

SELECT A.EMPLID , A.EMPL_RCD , CASE WHEN B.PTAI_LIST_STATUS='IP' THEN ( SELECT MIN(ITM.PTAI_DUE_DATE) FROM PS_PTAI_ITEM ITM WHERE ITM.PTAI_LIST_ID =B.PTAI_LIST_ID and ITM.PTAI_DUE_DATE IS NOT NULL AND ITM.PTAI_STATUS <> 4) ELSE %DateNull END , (CASE WHEN B.PTAI_LIST_STATUS='IP' THEN ( SELECT COUNT(*) FROM PS_PTAI_ITEM ITM WHERE ITM.PTAI_LIST_ID=B.PTAI_LIST_ID AND ITM.PTAI_DUE_DATE IS NOT NULL AND ITM.PTAI_STATUS <> 4) ELSE 0 END ) FROM PS_HR_OBD_TRIGGER A , PS_PTAI_LIST B , PS_PTAI_CONTEXT C , PS_PTAI_CONTEXT D WHERE B.PTAI_PARENT_TMPL = A.AGC_LIST_ID AND C.PTAI_LIST_ID = B.PTAI_LIST_ID AND C.RECNAME = 'HR_OBD_CONTEXT' AND C.FIELDNAME = 'EMPLID' AND C.PTAI_KEY_VALUE = A.EMPLID AND D.PTAI_LIST_ID = B.PTAI_LIST_ID AND D.RECNAME = 'HR_OBD_CONTEXT' AND D.FIELDNAME = 'EMPL_RCD' AND D.PTAI_KEY_VALUE = %NumToChar(A.EMPL_RCD)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
Prompt Table: PERS_SRCH_GBL

2 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Record

3 HR_OBD_AG_DUE_DT Date(10) DATE OnBoarding Due Date

4 HR_OBD_AG_ITM_CNT Number(3,0) SMALLINT NOT NULL Item Due Count

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	EMPLID	Character(11)	VARCHAR2(11) NOT NULL	Employee ID Prompt Table: PERS_SRCH_GBL
2	EMPL_RCD	Number(3,0)	SMALLINT NOT NULL	Empl Record
3	HR_OBD_AG_DUE_DT	Date(10)	DATE	OnBoarding Due Date
4	HR_OBD_AG_ITM_CNT	Number(3,0)	SMALLINT NOT NULL	Item Due Count