PV_PERF_V3_VW

(SQL View)

SELECT G.SETID ,G.INV_ITEM_ID ,G.VENDOR_SETID ,G.VENDOR_ID ,G.UNIT_OF_MEASURE ,G.EFFDT ,G.VNDR_LOC ,G.PRICE_VNDR ,G.CURRENCY_CD FROM PS_ITM_VNDR_UOM_PR G WHERE G.EFFDT = ( SELECT MAX(EFFDT) FROM PS_ITM_VNDR_UOM_PR WHERE SETID = G.SETID AND INV_ITEM_ID = G.INV_ITEM_ID AND VENDOR_ID = G.VENDOR_ID AND VENDOR_SETID = G.VENDOR_SETID AND VNDR_LOC = G.VNDR_LOC AND UNIT_OF_MEASURE = G.UNIT_OF_MEASURE AND CURRENCY_CD = G.CURRENCY_CD AND QTY_MIN = G.QTY_MIN AND EFF_STATUS = 'A' AND EFFDT <= %CurrentDateIn ) AND G.UNIT_OF_MEASURE = ( SELECT U.UNIT_OF_MEASURE FROM PS_ITM_VNDR_UOM U WHERE G.VENDOR_SETID = U.VENDOR_SETID AND G.VENDOR_ID = U.VENDOR_ID AND G.SETID = U.SETID AND G.INV_ITEM_ID = U.INV_ITEM_ID AND G.VNDR_LOC = U.VNDR_LOC AND U.DFLT_UOM = 'Y') AND G.CURRENCY_CD = ( SELECT MAX(W.CURRENCY_CD) FROM PS_ITM_VNDR_UOM_PR W WHERE G.VENDOR_SETID = W.VENDOR_SETID AND G.VENDOR_ID = W.VENDOR_ID AND G.SETID = W.SETID AND G.INV_ITEM_ID = W.INV_ITEM_ID AND G.VNDR_LOC = W.VNDR_LOC AND G.UNIT_OF_MEASURE = W.UNIT_OF_MEASURE AND G.QTY_MIN = W.QTY_MIN) AND G.VNDR_LOC = ( SELECT VNDR_LOC FROM PS_VENDOR_LOC L WHERE G.VENDOR_SETID = L.SETID AND G.VENDOR_ID = L.VENDOR_ID AND G.VNDR_LOC = L.VNDR_LOC AND EFFDT =( SELECT MAX(EFFDT) FROM PS_VENDOR_LOC WHERE SETID = L.SETID AND VENDOR_ID = L.VENDOR_ID AND VNDR_LOC = L.VNDR_LOC AND EFF_STATUS = 'A' AND EFFDT <= %CurrentDateIn ) ) AND G.QTY_MIN = ( SELECT MIN(QTY_MIN) FROM PS_ITM_VNDR_UOM_PR WHERE SETID = G.SETID AND INV_ITEM_ID = G.INV_ITEM_ID AND VENDOR_ID = G.VENDOR_ID AND VENDOR_SETID = G.VENDOR_SETID AND VNDR_LOC = G.VNDR_LOC AND UNIT_OF_MEASURE = G.UNIT_OF_MEASURE AND EFF_STATUS = 'A' AND EFFDT <= %CurrentDateIn)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

1 SETID Character(5) VARCHAR2(5) NOT NULL SetID

2 INV_ITEM_ID Character(18) VARCHAR2(18) NOT NULL Item ID

3 VENDOR_SETID Character(5) VARCHAR2(5) NOT NULL Vendor SetID

4 VENDOR_ID Character(10) VARCHAR2(10) NOT NULL Vendor Identifier

5 UNIT_OF_MEASURE Character(3) VARCHAR2(3) NOT NULL Used on an approval rule set.
MHR=Muti Hourly
PER=Percentage
SQF=Square Footage

6 EFFDT Date(10) DATE Effective Date
Default Value: %date

7 VNDR_LOC Character(10) VARCHAR2(10) NOT NULL Vendor Location

8 PRICE_VNDR Number(16,5) DECIMAL(15,5) NOT NULL Vendor Price

9 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	SETID	Character(5)	VARCHAR2(5) NOT NULL	SetID
2	INV_ITEM_ID	Character(18)	VARCHAR2(18) NOT NULL	Item ID
3	VENDOR_SETID	Character(5)	VARCHAR2(5) NOT NULL	Vendor SetID
4	VENDOR_ID	Character(10)	VARCHAR2(10) NOT NULL	Vendor Identifier
5	UNIT_OF_MEASURE	Character(3)	VARCHAR2(3) NOT NULL	Used on an approval rule set. MHR=Muti Hourly PER=Percentage SQF=Square Footage
6	EFFDT	Date(10)	DATE	Effective Date Default Value: %date
7	VNDR_LOC	Character(10)	VARCHAR2(10) NOT NULL	Vendor Location
8	PRICE_VNDR	Number(16,5)	DECIMAL(15,5) NOT NULL	Vendor Price
9	CURRENCY_CD	Character(3)	VARCHAR2(3) NOT NULL	Currency Code