REQ_CNT_SRH_VW

(SQL View)
 Index Back

Contract Search for REQ

PO002 Manufacturer Contract. This view is used in dynamic view REQ_CNTRCT_DVW.

SELECT DISTINCT DST_D.VENDOR_SETID , DST_D.VENDOR_ID , MFG_A.SETID , MFG_A.CNTRCT_ID , DST_B.VENDOR_NAME_SHORT , MFG_A.CNTRCT_STATUS , MFG_A.DESCR , MFG_A.CNTRCT_BEGIN_DT , MFG_A.CNTRCT_EXPIRE_DT , MFG_A.SETID , MFG_A.CONTROL_TYPE , MFG_A.VERSION_NBR , 1 , MFG_C.CNTRCT_ID_DST , MFG_C.PRIORITY , MFG_A.CNTRCT_PROC_OPT , MFG_A.MFG_ID , MFG_A.CNTRCT_CORP_FLG FROM PS_CNTRCT_HDR MFG_A , PS_VENDOR DST_B , PS_CNTRCT_MFG_DST MFG_C , PS_CNTRCT_HDR DST_D WHERE MFG_A.CNTRCT_PROC_OPT = 'MFG' AND MFG_A.CNTRCT_STATUS = 'A' AND MFG_A.VERSION_STATUS = 'C' AND MFG_A.CNTRCT_CF_LOCK = 'N' AND (MFG_A.CNTRCT_EXPIRE_DT IS NULL OR (MFG_A.CNTRCT_EXPIRE_DT IS NOT NULL AND MFG_A.CNTRCT_EXPIRE_DT >= %CurrentDateIn)) AND MFG_A.SETID = MFG_C.SETID AND MFG_A.CNTRCT_ID = MFG_C.CNTRCT_ID AND MFG_A.VERSION_NBR = MFG_C.VERSION_NBR AND MFG_C.SETID = DST_D.SETID AND MFG_C.CNTRCT_ID_DST = DST_D.CNTRCT_ID AND MFG_C.RELTN_STATUS = 'A' AND DST_D.VERSION_NBR = 1 AND DST_D.VENDOR_SETID = DST_B.SETID AND DST_D.VENDOR_ID = DST_B.VENDOR_ID AND DST_D.CNTRCT_PROC_OPT = 'DST' AND DST_D.CNTRCT_STATUS = 'A' AND DST_D.VERSION_STATUS = 'C' AND DST_D.CNTRCT_CF_LOCK = 'N' AND (DST_D.CNTRCT_EXPIRE_DT IS NULL OR (DST_D.CNTRCT_EXPIRE_DT IS NOT NULL AND DST_D.CNTRCT_EXPIRE_DT >= %CurrentDateIn)) AND NOT EXISTS( SELECT 'X' FROM %Table(CS_CNTRCT_HD_SN) CS WHERE MFG_A.SETID = CS.SETID AND MFG_A.CNTRCT_ID = CS.CNTRCT_ID) AND EXISTS ( SELECT 'X' FROM PS_CNTRCT_LINE_UOM C , PS_CNTRCT_LINE E WHERE E.SETID = MFG_A.SETID AND E.CNTRCT_ID = MFG_A.CNTRCT_ID AND E.VERSION_NBR = MFG_A.VERSION_NBR AND C.SETID = E.SETID AND C.CNTRCT_ID = E.CNTRCT_ID AND C.VERSION_NBR = E.VERSION_NBR AND C.CNTRCT_LINE_NBR = E.CNTRCT_LINE_NBR AND E.DESCR254_MIXED <> ' ' AND E.CATEGORY_ID <> ' ' AND E.UNIT_OF_MEASURE <> ' ' AND C.PRICE_CNTRCT_BASE <> 0 ) UNION ALL SELECT DISTINCT A.VENDOR_SETID , A.VENDOR_ID , A.SETID , A.CNTRCT_ID , B.VENDOR_NAME_SHORT , A.CNTRCT_STATUS , A.DESCR , A.CNTRCT_BEGIN_DT , A.CNTRCT_EXPIRE_DT , A.SETID , A.CONTROL_TYPE , A.VERSION_NBR , 2 , ' ' , 0 , A.CNTRCT_PROC_OPT , A.MFG_ID , A.CNTRCT_CORP_FLG FROM PS_CNTRCT_HDR A , PS_VENDOR B WHERE A.CNTRCT_PROC_OPT NOT IN ('MFG','DST') AND A.VENDOR_SETID = B.SETID AND A.VENDOR_ID = B.VENDOR_ID AND A.CNTRCT_STATUS = 'A' AND A.VERSION_STATUS = 'C' AND A.CNTRCT_CF_LOCK = 'N' AND (A.CNTRCT_TYPE = 'PO' OR A.CNTRCT_TYPE = 'GN') AND (A.CNTRCT_EXPIRE_DT IS NULL OR (A.CNTRCT_EXPIRE_DT IS NOT NULL AND A.CNTRCT_EXPIRE_DT >= %CurrentDateIn)) AND A.VCHR_SRC = 'CNTR' AND B.VNDR_STATUS_PO = 'Y'

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 VENDOR_SETID Character(5) VARCHAR2(5) NOT NULL Vendor SetID
2 VENDOR_ID Character(10) VARCHAR2(10) NOT NULL Vendor Identifier
3 CNTRCT_SETID Character(5) VARCHAR2(5) NOT NULL SetID
4 CNTRCT_ID Character(25) VARCHAR2(25) NOT NULL Buying Agreement ID
5 VENDOR_NAME_SHORT Character(14) VARCHAR2(14) NOT NULL Short Vendor Name

Prompt Table: VENDOR_PO_NMVW2

6 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

7 DESCR Character(30) VARCHAR2(30) NOT NULL Description
8 CNTRCT_BEGIN_DT Date(10) DATE Contract Begin Date
9 CNTRCT_EXPIRE_DT Date(10) DATE Expire Date
10 SETID Character(5) VARCHAR2(5) NOT NULL SetID
11 CONTROL_TYPE Character(4) VARCHAR2(4) NOT NULL PO002 Manufacturer Contract
BU=Business Unit
CDOM=Contract Domain
SHIP=Ship To
X=Not Used
12 VERSION_NBR Number(5,0) INTEGER NOT NULL Contract Version number
13 CNTRCT_PRIORITY Number(3,0) SMALLINT NOT NULL PO002 Manufacturer Contract. Contract Priority for contract search and auto default.
14 CNTRCT_ID_DST Character(25) VARCHAR2(25) NOT NULL Contract ID To
15 PRIORITY Number(3,0) SMALLINT NOT NULL Priority
16 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
17 MFG_ID Character(50) VARCHAR2(50) NOT NULL Manufacturer ID
18 CNTRCT_CORP_FLG Character(1) VARCHAR2(1) NOT NULL Corporate Contract Flag
N=No
Y=Yes