AUC_CNTRCT_VW

(SQL View)
 Index Back

Sourcing Copy Contract View


SELECT DISTINCT A.SETID , A.CNTRCT_ID , B.CNTRCT_LINE_NBR , B.ITM_SETID , B.INV_ITEM_ID , B.DESCR254_MIXED , B.CATEGORY_ID , B.QTY_LINE_MAX , B.UNIT_OF_MEASURE , C.PRICE_CNTRCT_BASE , B.CURRENCY_CD , A.CNTRCT_EXPIRE_DT , A.BUYER_ID , A.VENDOR_SETID , A.VENDOR_ID , A.CNTRCT_PROC_OPT , A.CNTRCT_TYPE , B.PHYSICAL_NATURE , B.LINE_STATUS , B.MFG_ID , B.MFG_ITM_ID , B.AMT_ONLY_FLG FROM PS_CNTRCT_HDR A , PS_CNTRCT_LINE B , PS_CNTRCT_LINE_UOM C WHERE A.SETID = B.SETID AND A.CNTRCT_ID = B.CNTRCT_ID AND A.CNTRCT_STATUS IN ('A','C','O') AND B.CATEGORY_ID <> ' ' AND B.UNIT_OF_MEASURE <> ' ' AND B.DESCR254_MIXED <> ' ' AND C.SETID = B.SETID AND C.CNTRCT_ID = B.CNTRCT_ID AND C.CNTRCT_LINE_NBR = B.CNTRCT_LINE_NBR AND C.UNIT_OF_MEASURE = B.UNIT_OF_MEASURE AND C.CURRENCY_CD = B.CURRENCY_CD AND EXISTS ( SELECT 'X' FROM PS_CNTRCT_LN_SHIP D WHERE D.SETID = C.SETID AND D.CNTRCT_ID = C.CNTRCT_ID AND D.CNTRCT_LINE_NBR = C.CNTRCT_LINE_NBR AND D.QTY_SCHED <> 0) UNION SELECT DISTINCT A.SETID , A.CNTRCT_ID , B.CNTRCT_LINE_NBR , B.ITM_SETID , B.INV_ITEM_ID , B.DESCR254_MIXED , B.CATEGORY_ID , B.QTY_LINE_MAX , B.UNIT_OF_MEASURE , C.PRICE_CNTRCT_BASE , C.CURRENCY_CD , A.CNTRCT_EXPIRE_DT , A.BUYER_ID , A.VENDOR_SETID , A.VENDOR_ID , A.CNTRCT_PROC_OPT , A.CNTRCT_TYPE , B.PHYSICAL_NATURE , B.LINE_STATUS , B.MFG_ID , B.MFG_ITM_ID , B.AMT_ONLY_FLG FROM PS_CNTRCT_HDR A , PS_CNTRCT_LINE B , PS_CNTRCT_LINE_UOM C WHERE A.SETID = B.SETID AND A.CNTRCT_ID = B.CNTRCT_ID AND A.CNTRCT_STATUS IN ('A','C','O') AND B.CATEGORY_ID <> ' ' AND B.UNIT_OF_MEASURE <> ' ' AND B.DESCR254_MIXED <> ' ' AND C.SETID = B.SETID AND C.CNTRCT_ID = B.CNTRCT_ID AND C.CNTRCT_LINE_NBR = B.CNTRCT_LINE_NBR AND C.UNIT_OF_MEASURE = B.UNIT_OF_MEASURE AND C.CURRENCY_CD <> B.CURRENCY_CD AND C.DFLT_UOM = 'Y' AND NOT EXISTS ( SELECT 'X' FROM PS_CNTRCT_LINE_UOM C2 WHERE C2.SETID = B.SETID AND C2.CNTRCT_ID = B.CNTRCT_ID AND C2.CNTRCT_LINE_NBR = B.CNTRCT_LINE_NBR AND C2.UNIT_OF_MEASURE = B.UNIT_OF_MEASURE AND C2.CURRENCY_CD = B.CURRENCY_CD ) AND EXISTS ( SELECT 'X' FROM PS_CNTRCT_LN_SHIP D WHERE D.SETID = C.SETID AND D.CNTRCT_ID = C.CNTRCT_ID AND D.CNTRCT_LINE_NBR = C.CNTRCT_LINE_NBR AND D.QTY_SCHED <> 0)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 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 LINE_NBR Number(5,0) INTEGER NOT NULL Line Number: 11/24/08 - Added TARGET label [PC product]
4 ITM_SETID Character(5) VARCHAR2(5) NOT NULL Item SetID
5 INV_ITEM_ID Character(18) VARCHAR2(18) NOT NULL Item ID
6 DESCR254_MIXED Character(254) VARCHAR2(254) NOT NULL Description
7 CATEGORY_ID Character(5) VARCHAR2(5) NOT NULL Category ID
8 QTY_AUC Number(16,4) DECIMAL(15,4) NOT NULL Event Quantity
9 UNIT_OF_MEASURE Character(3) VARCHAR2(3) NOT NULL Used on an approval rule set.
MHR=Muti Hourly
PER=Percentage
SQF=Square Footage
10 PRICE_AUC Number(17,5) DECIMAL(16,5) NOT NULL Event Price
11 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
12 DUE_DT Date(10) DATE Specifies the date that a transaction is due. It is a generic field used in multiple PeopleSoft applications including Receivables Payables and Purchasing. For example it represents the date that payment is due for a voucher or the date that a scheduled shipment is due to be received.
13 BUYER_ID Character(30) VARCHAR2(30) NOT NULL Buyer
14 VENDOR_SETID Character(5) VARCHAR2(5) NOT NULL Vendor SetID
15 VENDOR_ID Character(10) VARCHAR2(10) NOT NULL Vendor Identifier
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 CNTRCT_TYPE Character(2) VARCHAR2(2) NOT NULL Contract Type
AP=Recurring Voucher
GN=General Contract
MS=Master Contract
PO=Purchase Order
18 PHYSICAL_NATURE Character(1) VARCHAR2(1) NOT NULL Physical Nature
G=Goods
S=Services
19 LINE_STATUS Character(1) VARCHAR2(1) NOT NULL Line Status
A=Active
C=Cancelled
I=Inactive
O=Open
P=Pending
20 MFG_ID Character(50) VARCHAR2(50) NOT NULL Manufacturer ID
21 MFG_ITM_ID Character(50) VARCHAR2(50) NOT NULL Manufacturer's Item ID
22 AMT_ONLY_FLG Character(1) VARCHAR2(1) NOT NULL Amount Only
N=No
Y=Yes