CNTRCT_CAT_SRCH (SQL View)	Index Back
Contract Category Search Contract Category Search

CNTRCT_CAT_SRCH

(SQL View)

Contract Category Search

SELECT A.SETID , A.CNTRCT_ID , A.VERSION_NBR , C.CATEGORY_CD , A.CAT_LINE_NBR , A.LINE_STATUS FROM PS_CNTRCT_CATEGORY A , PS_CNTRCT_HDR B , PS_ITM_CAT_TBL C WHERE A.CATEGORY_ID <> ' ' AND A.CNTRCT_ID = B.CNTRCT_ID AND A.VERSION_NBR = B.VERSION_NBR AND A.SETID = B.SETID AND A.LINE_STATUS = 'A' AND B.VERSION_STATUS = 'C' AND C.SETID = A.CATEGORY_SETID AND C.CATEGORY_ID = A.CATEGORY_ID AND B.CURRENCY_CD = C.CURRENCY_CD AND C.EFFDT = ( SELECT MAX(F.EFFDT) FROM PS_ITM_CAT_TBL F WHERE C.SETID = F.SETID AND C.CATEGORY_TYPE = F.CATEGORY_TYPE AND C.CATEGORY_CD = F.CATEGORY_CD AND C.CATEGORY_ID = F.CATEGORY_ID AND F.EFFDT <= %CurrentDateIn)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

1 CNTRCT_SETID Character(5) VARCHAR2(5) NOT NULL SetID
Default Value: OPR_DEF_TBL_FS.SETID

2 CNTRCT_ID Character(25) VARCHAR2(25) NOT NULL Buying Agreement ID

3 VERSION_NBR Number(5,0) INTEGER NOT NULL Contract Version number

4 CATEGORY_CD Character(18) VARCHAR2(18) NOT NULL Category Code

5 CAT_LINE_NBR Number(5,0) INTEGER NOT NULL Contract Category Line Number

6 LINE_STATUS Character(1) VARCHAR2(1) NOT NULL Line Status
A=Active
C=Cancelled
I=Inactive
O=Open
P=Pending
Default Value: A

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	CNTRCT_SETID	Character(5)	VARCHAR2(5) NOT NULL	SetID Default Value: OPR_DEF_TBL_FS.SETID
2	CNTRCT_ID	Character(25)	VARCHAR2(25) NOT NULL	Buying Agreement ID
3	VERSION_NBR	Number(5,0)	INTEGER NOT NULL	Contract Version number
4	CATEGORY_CD	Character(18)	VARCHAR2(18) NOT NULL	Category Code
5	CAT_LINE_NBR	Number(5,0)	INTEGER NOT NULL	Contract Category Line Number
6	LINE_STATUS	Character(1)	VARCHAR2(1) NOT NULL	Line Status A=Active C=Cancelled I=Inactive O=Open P=Pending Default Value: A