PV_CHNG_TRK3_VW

(SQL View)

SELECT DISTINCT A.BUSINESS_UNIT , A.REQ_ID , A.REQ_NAME , A.REQUESTOR_ID , B.LAST_DTTM_UPDATE , B.OPRID_MODIFIED_BY , B.CHNG_ORD_BATCH FROM PS_REQ_HDR A , PS_REQ_CHNG_SHIP B , PS_REQ_CHNG_ORD C WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.REQ_ID = B.REQ_ID AND (A.ORIG_TRAN='EPO' OR (A.ORIGIN='ONL' AND A.ORIG_TRAN='REQ' AND A.OPRID_ENTERED_BY='REQLOAD')) AND B.BUSINESS_UNIT = C.BUSINESS_UNIT AND B.REQ_ID = C.REQ_ID AND B.CHNG_ORD_BATCH = C.CHNG_ORD_BATCH AND C.CHNG_ORD_BATCH = ( SELECT MAX(D.CHNG_ORD_BATCH) FROM PS_REQ_CHNG_SHIP D WHERE D.BUSINESS_UNIT = C.BUSINESS_UNIT AND D.REQ_ID = C.REQ_ID) AND NOT EXISTS ( SELECT 'X' FROM PS_PV_CHNG_TRK1_VW A1 WHERE A.BUSINESS_UNIT = A1.BUSINESS_UNIT AND A.REQ_ID = A1.REQ_ID AND (A.ORIG_TRAN='EPO' OR (A.ORIGIN='ONL' AND A.ORIG_TRAN='REQ' AND A.OPRID_ENTERED_BY='REQLOAD')) ) AND NOT EXISTS ( SELECT 'X' FROM PS_PV_CHNG_TRK2_VW A2 WHERE A.BUSINESS_UNIT = A2.BUSINESS_UNIT AND A.REQ_ID = A2.REQ_ID AND (A.ORIG_TRAN='EPO' OR (A.ORIGIN='ONL' AND A.ORIG_TRAN='REQ' AND A.OPRID_ENTERED_BY='REQLOAD')) )

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

1 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
Prompt Table: SP_BU_PM_NONVW

2 REQ_ID Character(10) VARCHAR2(10) NOT NULL Requisition ID

3 REQ_NAME Character(30) VARCHAR2(30) NOT NULL Requisition Name

4 REQUESTOR_ID Character(30) VARCHAR2(30) NOT NULL Requestor id
Prompt Table: %EDITTABLE16

5 LAST_DTTM_UPDATE DateTime(26) TIMESTAMP Specifies the date and time of the last update to an entry. This field is maintained by PeopleSoft and is used in a variety of contexts.

6 OPRID_MODIFIED_BY Character(30) VARCHAR2(30) NOT NULL Last User to Modify Entered By 07/25/2011:Ensured that OPRID_MODIFIED_BY is set with format type of MixedCase.

7 CHNG_ORD_BATCH Number(3,0) SMALLINT NOT NULL Change Order Number

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	BUSINESS_UNIT	Character(5)	VARCHAR2(5) NOT NULL	Business Unit Prompt Table: SP_BU_PM_NONVW
2	REQ_ID	Character(10)	VARCHAR2(10) NOT NULL	Requisition ID
3	REQ_NAME	Character(30)	VARCHAR2(30) NOT NULL	Requisition Name
4	REQUESTOR_ID	Character(30)	VARCHAR2(30) NOT NULL	Requestor id Prompt Table: %EDITTABLE16
5	LAST_DTTM_UPDATE	DateTime(26)	TIMESTAMP	Specifies the date and time of the last update to an entry. This field is maintained by PeopleSoft and is used in a variety of contexts.
6	OPRID_MODIFIED_BY	Character(30)	VARCHAR2(30) NOT NULL	Last User to Modify Entered By 07/25/2011:Ensured that OPRID_MODIFIED_BY is set with format type of MixedCase.
7	CHNG_ORD_BATCH	Number(3,0)	SMALLINT NOT NULL	Change Order Number