GM_AWD_MILE_VW (SQL View)	Index Back
Milestone Notification inquiry

GM_AWD_MILE_VW

(SQL View)

Milestone Notification inquiry

SELECT A.CONTRACT_NUM , A.BUSINESS_UNIT , A.MILESTONE_ID , A.MILESTONE_TYPE , A.MILESTONE_CODE , A.GM_MLST_PRI , A.DUE_DT , A.MILESTONE_STAT , A.PRIOR_DAYS , B.EMPLID , B.GM_ADMIN_CNTCT , %DateTimeNull FROM PS_GM_AWD_MILE A , PS_GM_AWARD B WHERE A.CONTRACT_NUM = B.CONTRACT_NUM AND A.BUSINESS_UNIT = B.BUSINESS_UNIT AND NOT EXISTS ( SELECT 'X' FROM PS_GM_MILE_HISTORY C WHERE C.CONTRACT_NUM = A.CONTRACT_NUM AND C.MILESTONE_ID = A.MILESTONE_ID) UNION SELECT A.CONTRACT_NUM , A.BUSINESS_UNIT , A.MILESTONE_ID , A.MILESTONE_TYPE , A.MILESTONE_CODE , A.GM_MLST_PRI , A.DUE_DT , A.MILESTONE_STAT , A.PRIOR_DAYS , B.EMPLID , B.GM_ADMIN_CNTCT , C.NOTIFED_DTTM FROM PS_GM_AWD_MILE A , PS_GM_MILE_HISTORY C , PS_GM_AWARD B WHERE A.CONTRACT_NUM = B.CONTRACT_NUM AND A.BUSINESS_UNIT = B.BUSINESS_UNIT AND C.CONTRACT_NUM = A.CONTRACT_NUM AND C.MILESTONE_ID = A.MILESTONE_ID AND C.LAST_SEQ_NBR = ( SELECT MAX(LAST_SEQ_NBR) FROM PS_GM_MILE_HISTORY D WHERE D.CONTRACT_NUM = A.CONTRACT_NUM AND D.MILESTONE_ID = A.MILESTONE_ID)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

1 CONTRACT_NUM Character(25) VARCHAR2(25) NOT NULL Contract

2 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit

3 MILESTONE_ID Character(6) VARCHAR2(6) NOT NULL Milestone_ID identifies the milestones that can be referenced from multiple functional areas.

4 MILESTONE_TYPE Character(5) VARCHAR2(5) NOT NULL Milestone Type

5 MILESTONE_CODE Character(15) VARCHAR2(15) NOT NULL Milestone Code

6 GM_MLST_PRI Character(1) VARCHAR2(1) NOT NULL GM_MLST_PRI
H=High
L=Low
M=Medium

7 DUE_DT Date(10) DATE Specifies the date that a transaction is due. It is a generic field used in multiple PeopleSoft applications including Receivables Payables and Purchasing. For example it represents the date that payment is due for a voucher or the date that a scheduled shipment is due to be received.

8 MILESTONE_STAT Character(3) VARCHAR2(3) NOT NULL Milestone Status

9 PRIOR_DAYS Number(3,0) SMALLINT NOT NULL PRIOR_DAYS

10 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID

11 GM_ADMIN_CNTCT Character(11) VARCHAR2(11) NOT NULL Grant Administrator

12 NOTIFED_DTTM DateTime(26) TIMESTAMP The date and time a designated person was notified of a given position limit violation

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	CONTRACT_NUM	Character(25)	VARCHAR2(25) NOT NULL	Contract
2	BUSINESS_UNIT	Character(5)	VARCHAR2(5) NOT NULL	Business Unit
3	MILESTONE_ID	Character(6)	VARCHAR2(6) NOT NULL	Milestone_ID identifies the milestones that can be referenced from multiple functional areas.
4	MILESTONE_TYPE	Character(5)	VARCHAR2(5) NOT NULL	Milestone Type
5	MILESTONE_CODE	Character(15)	VARCHAR2(15) NOT NULL	Milestone Code
6	GM_MLST_PRI	Character(1)	VARCHAR2(1) NOT NULL	GM_MLST_PRI H=High L=Low M=Medium
7	DUE_DT	Date(10)	DATE	Specifies the date that a transaction is due. It is a generic field used in multiple PeopleSoft applications including Receivables Payables and Purchasing. For example it represents the date that payment is due for a voucher or the date that a scheduled shipment is due to be received.
8	MILESTONE_STAT	Character(3)	VARCHAR2(3) NOT NULL	Milestone Status
9	PRIOR_DAYS	Number(3,0)	SMALLINT NOT NULL	PRIOR_DAYS
10	EMPLID	Character(11)	VARCHAR2(11) NOT NULL	Employee ID
11	GM_ADMIN_CNTCT	Character(11)	VARCHAR2(11) NOT NULL	Grant Administrator
12	NOTIFED_DTTM	DateTime(26)	TIMESTAMP	The date and time a designated person was notified of a given position limit violation