CNTRCT_HDR_SVW

(SQL View)
 Index Back

PTSF Contract Header

Header view for Contracts for use in Component Search for Procurement (PTSF)

SELECT CNT.SETID ,CNT.CNTRCT_ID , CNT.VERSION_NBR ,CNT.VENDOR_SETID ,CNT.VENDOR_ID ,V.VENDOR_NAME_SHORT ,CNT.CNTRCT_STATUS ,CNT.OPRID_APPROVED_BY , CNT.APPROVAL_DT ,R.AMT_RELSD_OPEN_ITM ,CNT.AMT_RELSD_OITM_CUM ,CNT.CNTRCT_TYPE ,%Coalesce(CNT.DESCR, Z.MESSAGE_TEXT) ,CNT.CNTRCT_BEGIN_DT ,CNT.CNTRCT_EXPIRE_DT ,CNT.AMT_CNTRCT_MAX ,CNT.VNDR_CNTRCT_REF ,CNT.CURRENCY_CD ,CNT.BUYER_ID ,CNT.CNTRCT_PROC_OPT ,CNT.MSTR_CNTRCT_ID ,CNT.ALLOW_OPEN_ITEM ,CNT.LAST_DTTM_UPDATE ,X.MESSAGE_TEXT || CNT.SETID ,%Substring(Y.MESSAGE_TEXT,1,30) FROM PS_CNTRCT_HDR CNT ,PS_CNTRCT_HDR_RLS R ,PS_VENDOR V , PSMSGCATDEFN X , PSMSGCATDEFN Y , PSMSGCATDEFN Z WHERE R.SETID = CNT.SETID AND R.CNTRCT_ID = CNT.CNTRCT_ID AND CNT.VENDOR_SETID = V.SETID AND CNT.VENDOR_ID = V.VENDOR_ID AND X.MESSAGE_SET_NBR = 10425 AND X.MESSAGE_NBR = 69 AND Y.MESSAGE_SET_NBR = 10425 AND Y.MESSAGE_NBR = 70 AND Z.MESSAGE_SET_NBR = 10425 AND Z.MESSAGE_NBR = 71

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 SETID Character(5) VARCHAR2(5) NOT NULL 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 VENDOR_SETID Character(5) VARCHAR2(5) NOT NULL Vendor SetID
5 VENDOR_ID Character(10) VARCHAR2(10) NOT NULL Vendor Identifier

Prompt Table: %EDITTABLE18

6 VENDOR_NAME_SHORT Character(14) VARCHAR2(14) NOT NULL Short Vendor Name
7 CNTRCT_STATUS Character(1) VARCHAR2(1) NOT NULL Contract Status
A=Approved
C=Closed
H=On-Hold
O=Open
P=Pre-Approved
X=Canceled

Default Value: O

8 OPRID_APPROVED_BY Character(30) VARCHAR2(30) NOT NULL Approved By
9 APPROVAL_DT Date(10) DATE Date of Approval
10 AMT_RELSD_OPEN_ITM Signed Number(28,3) DECIMAL(26,3) NOT NULL Amt relsd for Open Item
11 AMT_RELSD_OITM_CUM Number(27,3) DECIMAL(26,3) NOT NULL Cumulative Relsd for Open Item
12 CNTRCT_TYPE Character(2) VARCHAR2(2) NOT NULL Contract Type
AP=Recurring Voucher
GN=General Contract
MS=Master Contract
PO=Purchase Order

Default Value: GN

13 DESCR Character(30) VARCHAR2(30) NOT NULL Description
14 CNTRCT_BEGIN_DT Date(10) DATE NOT NULL Contract Begin Date

Default Value: %date

15 CNTRCT_EXPIRE_DT Date(10) DATE Expire Date
16 AMT_CNTRCT_MAX Number(27,3) DECIMAL(26,3) NOT NULL Maximum Amount of Contract.
17 VNDR_CNTRCT_REF Character(30) VARCHAR2(30) NOT NULL Supplier Contract Ref
18 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code

Default Value: CNTRCT_CONTROL.CURRENCY_CD

Prompt Table: CURRENCY_CD_TBL

19 BUYER_ID Character(30) VARCHAR2(30) NOT NULL Buyer

Prompt Table: BUYER_ACTIVE_VW

20 CNTRCT_PROC_OPT Character(4) VARCHAR2(4) NOT NULL Contract Process Option
AP=Recurring Voucher
BPO=Release to Single PO Only
DST=Distributor
GN=General Contract
GRPM=Group Multi Supplier
GRPS=Group Single Supplier
MFG=Manufacturer
PADV=Prepaid Voucher w/ Advance PO
PO=Purchase Order
PPAY=Prepaid Voucher
RPOV=Recurring PO Voucher
SPP=Special Purpose

Default Value: GN

21 MSTR_CNTRCT_ID Character(10) VARCHAR2(10) NOT NULL Master Contract ID

Prompt Table: CNTRCT_MSTR_VW

22 ALLOW_OPEN_ITEM Character(1) VARCHAR2(1) NOT NULL Allow Open Item Reference
N=No
Y=Yes

Y/N Table Edit

Default Value: N

23 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.
24 DESCR120 Character(120) VARCHAR2(120) NOT NULL Description
25 FUNC_AREA Character(30) VARCHAR2(30) NOT NULL For PTSF