PV_VER_NULL_VW

(SQL View)
Index Back

SELECT A.SETID ,A.INV_ITEM_ID ,' ' ,' ' ,A.CATEGORY_ID ,A.UNIT_MEASURE_STD ,F.DESCR ,A.DESCR60 ,F.DESCR254_MIXED ,A.INVENTORY_ITEM ,F.PRICE_LIST ,F.STD_LEAD ,F.MODEL ,F.CURRENCY_CD ,F.AVAIL_ALL_RGNS ,A.ITM_STATUS_CURRENT ,A.ITM_STATUS_FUTURE ,A.ITM_STATUS_EFFDT ,A.ITM_STAT_DT_FUTURE ,' ' ,' ' ,' ' ,1 ,' ' ,' ' ,' ' ,' ' ,%DATENULL ,' ' ,0 ,' ' FROM PS_MASTER_ITEM_TBL A , PS_PURCH_ITEM_ATTR F WHERE A.SETID = F.SETID AND A.INV_ITEM_ID = F.INV_ITEM_ID AND ((%CurrentDateIn >= F.PO_AVAIL_DT) AND (F.PO_UNAVAIL_DT > %CurrentDateIn )) AND NOT EXISTS ( SELECT 'X' FROM PS_ITM_VENDOR B , PS_VENDOR C WHERE A.SETID= B.SETID AND A.INV_ITEM_ID = B.INV_ITEM_ID AND C.SETID = B.VENDOR_SETID AND C.VENDOR_ID = B.VENDOR_ID AND C.VENDOR_STATUS = 'A' AND C.VNDR_STATUS_PO = 'Y' AND B.ITM_STATUS = 'A')

# 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 CATEGORY_ID Character(5) VARCHAR2(5) NOT NULL Category ID
6 UNIT_MEASURE_STD Character(3) VARCHAR2(3) NOT NULL Standard Unit of Measure
7 DESCR Character(30) VARCHAR2(30) NOT NULL Description
8 DESCR60 Character(60) VARCHAR2(60) NOT NULL Description
9 DESCR254_MIXED Character(254) VARCHAR2(254) NOT NULL Description
10 INVENTORY_ITEM Character(1) VARCHAR2(1) NOT NULL Inventory Item
N=No
Y=Yes
11 PRICE_LIST Number(16,5) DECIMAL(15,5) NOT NULL Standard Price
12 STD_LEAD Number(3,0) SMALLINT NOT NULL Purchase Lead Time Days
13 MODEL Character(30) VARCHAR2(30) NOT NULL Model
14 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
15 AVAIL_ALL_RGNS Character(1) VARCHAR2(1) NOT NULL Available in All Regions
16 ITM_STATUS_CURRENT Character(1) VARCHAR2(1) NOT NULL Item Status Current
1=Active
2=Hold
3=Discontinue
4=Inactive
5=Pending Approval
6=Denied Approval
7=Under Initialization
17 ITM_STATUS_FUTURE Character(1) VARCHAR2(1) NOT NULL Item Status Future
1=Active
2=Hold
3=Discontinue
4=Inactive
18 ITM_STATUS_EFFDT Date(10) DATE Item Status Date
19 ITM_STAT_DT_FUTURE Date(10) DATE Item Date Future
20 UNIT_OF_MEASURE Character(3) VARCHAR2(3) NOT NULL Used on an approval rule set.
MHR=Muti Hourly
PER=Percentage
SQF=Square Footage
21 ITM_ID_VNDR Character(50) VARCHAR2(50) NOT NULL Vendor Item ID
22 VNDR_CATALOG_ID Character(20) VARCHAR2(20) NOT NULL Vendor's Catalog Number
23 ITM_VNDR_PRIORITY Number(3,0) SMALLINT NOT NULL Item Vendor Priority
24 VNDR_NAME1 Character(40) VARCHAR2(40) NOT NULL Supplier Name
25 VNDR_STATUS_PO Character(1) VARCHAR2(1) NOT NULL Open For Ordering
N=No
Y=Yes
26 ITM_STATUS Character(1) VARCHAR2(1) NOT NULL Item Status
A=Active
D=Discntued
H=Hold
I=Inactive
X=To be Del
27 VENDOR_STATUS Character(1) VARCHAR2(1) NOT NULL Vendor Status
A=Approved
D=Denied
E=Unapproved
I=Inactive
X=To Be Archived
28 EFFDT Date(10) DATE Effective Date

Default Value: %date

29 VNDR_LOC Character(10) VARCHAR2(10) NOT NULL Vendor Location
30 PRICE_VNDR Number(16,5) DECIMAL(15,5) NOT NULL Vendor Price
31 CURRENCY_CD_VNDR Character(3) VARCHAR2(3) NOT NULL Business Unit Base Currency