CNT_CHI_ADD1_VW

(SQL View)
 Index Back

Contract Search for GRPS

11/15/2015 JOHAN PO0025 - Master contract fluid

SELECT A.SETID , A.VENDOR_ID , A.CNTRCT_ID , A.CNTRCT_STYLE , S.CNTRCT_STYLE_NAME , B.VENDOR_NAME_SHORT , B.NAME1 , A.CNTRCT_STATUS , A.DESCR , A.CNTRCT_BEGIN_DT , A.CNTRCT_EXPIRE_DT , a.versIon_nbr , a.version_status , a.amt_cntrct_max FROM PS_CNTRCT_HDR A , PS_VENDOR B , PS_CNTRCT_STYLE S WHERE A.VENDOR_SETID = B.SETID AND A.VENDOR_ID = B.VENDOR_ID AND A.CNTRCT_STYLE = S.CNTRCT_STYLE AND A.VERSION_STATUS = 'C' AND A.CNTRCT_PROC_OPT NOT IN ('GRPS', 'GRPM','DST') AND A.CNTRCT_STATUS <> 'X' AND A.CNTRCT_BEGIN_DT <= %CurrentDateIn AND (A.CNTRCT_EXPIRE_DT IS NULL OR (A.CNTRCT_EXPIRE_DT IS NOT NULL AND A.CNTRCT_EXPIRE_DT >= %CurrentDateIn))

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 SETID Character(5) VARCHAR2(5) NOT NULL SetID
2 SUPPLIER_ID Character(10) VARCHAR2(10) NOT NULL Supplier ID
3 CNTRCT_ID_CHILD Character(25) VARCHAR2(25) NOT NULL Contract ID To
4 CNTRCT_STYLE Character(10) VARCHAR2(10) NOT NULL Contract Style
5 CNTRCT_STYLE_NAME Character(30) VARCHAR2(30) NOT NULL Contract Style
6 VENDOR_NAME_SHORT Character(14) VARCHAR2(14) NOT NULL Short Vendor Name
7 NAME1 Character(40) VARCHAR2(40) NOT NULL Name 1
8 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

9 DESCR Character(30) VARCHAR2(30) NOT NULL Description
10 CNTRCT_BEGIN_DT Date(10) DATE Contract Begin Date
11 CNTRCT_EXPIRE_DT Date(10) DATE Expire Date
12 VERSION_NBR Number(5,0) INTEGER NOT NULL Contract Version number
13 VERSION_STATUS Character(1) VARCHAR2(1) NOT NULL Contract version status
C=Current
D=Draft
H=History
14 AMT_CNTRCT_MAX Number(27,3) DECIMAL(26,3) NOT NULL Maximum Amount of Contract.