PV_CM_SINDX_VW (SQL View)	Index Back
Search Index Selection This view will be used for the search engine to select items

PV_CM_SINDX_VW

(SQL View)

Search Index Selection

This view will be used for the search engine to select items

SELECT A.SETID ,%Substring(A.EOCM_OFFER_ID,1,18) ,S.SETID ,S.VENDOR_ID ,IC.CATEGORY_ID ,%Substring(A.EOCM_SUP_OFFER_UOM,1,3) ,%Substring(A.EOCM_SUP_OFFER_NM,1,30) ,%Substring(A.EOCM_SUP_OFFER_NM,1,60) ,A.EOCM_SUP_OFFER_NM ,'N' ,A.EOCM_SUP_OFFER_PR ,A.EOCM_LEAD_TIME ,' ' ,A.CURRENCY_CD ,'Y' ,'1' ,' ' ,A.EFFDT ,%DateNULL ,%Substring(A.EOCM_SUP_OFFER_UOM,1,3) ,A.EOCM_SUP_OFFER_SKU ,' ' ,A.EOCM_ITMVPRIORITY ,V.NAME1 ,V.VNDR_STATUS_PO ,'A' ,V.VENDOR_STATUS ,A.EFFDT ,S.VNDR_LOC ,A.EOCM_SUP_OFFER_PR ,A.CURRENCY_CD ,A.LASTUPDDTTM FROM PS_EOCM_GEN_TBL A , PS_EOCM_CAT_TP_VER B , PS_EOCM_TP_VER C , PS_EOCM_CAT_VER D , PS_EOCM_OFFER_CAT OC , PS_ITM_CAT_TBL IC , PS_VENDOR V , PS_PV_MS_SUPPLIER S , PS_INSTALLATION_PV Z WHERE A.EOCM_OFFER_STATUS IN ('S', 'P') AND C.EOCM_TP_VER_STATE = 'P' AND D.EOCM_CATVER_STATUS = 'P' AND A.LASTUPDDTTM = ( SELECT MAX(X.LASTUPDDTTM) FROM PS_EOCM_GEN_TBL X WHERE A.SETID = X.SETID AND A.EOCM_CATALOG_ID = X.EOCM_CATALOG_ID AND A.EOCM_ACCESS_ID = X.EOCM_ACCESS_ID AND A.EOCM_OFFER_ID = X.EOCM_OFFER_ID AND A.EOCM_SUP_OFFER_SKU = X.EOCM_SUP_OFFER_SKU AND A.EOCM_SUP_OFFER_UOM = X.EOCM_SUP_OFFER_UOM) AND A.SETID = B.SETID AND A.EOCM_CATALOG_ID = B.EOCM_CATALOG_ID AND A.EOCM_ACCESS_ID = B.EOCM_ACCESS_ID AND A.EOCM_TP_VER_ID = B.EOCM_TP_VER_ID AND B.SETID = C.SETID AND B.EOCM_CATALOG_ID = C.EOCM_CATALOG_ID AND B.EOCM_ACCESS_ID = C.EOCM_ACCESS_ID AND B.EOCM_TP_VER_ID = C.EOCM_TP_VER_ID AND B.SETID = D.SETID AND B.EOCM_CATALOG_ID = D.EOCM_CATALOG_ID AND B.EOCM_CAT_VER_ID = D.EOCM_CAT_VER_ID AND Z.PVCM_SW = 'Y' AND A.SETID = OC.SETID AND A.EOCM_CATALOG_ID =OC.EOCM_CATALOG_ID AND A.EOCM_OFFER_ID = OC.EOCM_OFFER_ID AND OC.EOCM_PRIMARY_CATEG = 'Y' AND IC.SETID = A.SETID AND IC.CATEGORY_CD = A.EOCM_SUP_CAT_CD AND V.SETID = S.SETID AND V.VENDOR_ID = S.VENDOR_ID AND S.EOTP_PARTNERID = A.EOCM_ACCESS_ID AND A.EOCM_TP_VER_ID = ( SELECT MAX(EOCM_TP_VER_ID) FROM PS_EOCM_GEN_TBL AA WHERE A.SETID = AA.SETID AND A.EOCM_CATALOG_ID = AA.EOCM_CATALOG_ID AND A.EOCM_ACCESS_ID = AA.EOCM_ACCESS_ID AND A.EOCM_ROW_ID = AA.EOCM_ROW_ID) AND NOT EXISTS( SELECT 'X' FROM PS_ITM_VENDOR X WHERE X.SETID = A.SETID AND X.INV_ITEM_ID = A.EOCM_OFFER_ID AND X.SETID = S.SETID AND X.VENDOR_ID = S.VENDOR_ID )

# 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

32 LASTUPDDTTM DateTime(26) TIMESTAMP Specifies the date and time of the last update to an entry. This field is maintained by PeopleSoft and is used in a variety of contexts.

#	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
32	LASTUPDDTTM	DateTime(26)	TIMESTAMP	Specifies the date and time of the last update to an entry. This field is maintained by PeopleSoft and is used in a variety of contexts.