EOCM_USR_OFF_VW (SQL View)	Index Back
User's Catalog Offer View Returns the catalog offerings available to users. Filter on operator ID and catalog to find the specific categories for a user.

EOCM_USR_OFF_VW

(SQL View)

User's Catalog Offer View

Returns the catalog offerings available to users. Filter on operator ID and catalog to find the specific categories for a user.

SELECT A.OPRID , A.SETID , A.EOCM_CATALOG_ID , A.EOCM_CAT_VER_ID , A.EOCM_CAT_HIER_ID , A.EOCM_CATEGORY_ID , D.EOCM_ACCESS_ID , D.EOCM_TP_VER_ID , D.EOCM_ROW_ID , D.EFFDT , D.EFF_STATUS , %subrec(EOCM_COMGEN_SBR, D) , %subrec(EOCM_EPRO_SBR, D) , %subrec(EOCM_ELM_SBR, D) , %subrec(EOCM_CRM_SBR, D) , %subrec(EOCM_PROP_SBR, D) , D.EOCM_OFFER_STATUS , D.EOCM_OFFER_STATE , D.MESSAGE_NBR , D.LASTUPDDTTM , D.LASTUPDOPRID FROM %Table(EOCM_USR_HIR_VW) A , %Table(EOCM_OFFER_CAT) B , %Table(EOCM_CAT_TP_VER) C , %Table(EOCM_GEN_TBL) D WHERE A.SETID = B.SETID AND A.EOCM_CATALOG_ID = B.EOCM_CATALOG_ID AND A.EOCM_CATEGORY_ID = B.EOCM_CATEGORY_ID AND A.SETID = C.SETID AND A.EOCM_CATALOG_ID = C.EOCM_CATALOG_ID AND A.EOCM_CAT_VER_ID = C.EOCM_CAT_VER_ID AND D.SETID = D.SETID AND B.EOCM_CATALOG_ID = D.EOCM_CATALOG_ID AND B.EOCM_OFFER_ID = D.EOCM_OFFER_ID AND C.EOCM_ACCESS_ID = D.EOCM_ACCESS_ID AND C.EOCM_TP_VER_ID = D.EOCM_TP_VER_ID AND D.EOCM_OFFER_STATUS = 'S'

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

1 OPRID Character(30) VARCHAR2(30) NOT NULL A user's ID (see PSOPRDEFN).

2 SETID Character(5) VARCHAR2(5) NOT NULL SetID
Prompt Table: SETID_TBL

3 EOCM_CATALOG_ID Character(32) VARCHAR2(32) NOT NULL Catalog ID

4 EOCM_CAT_VER_ID Number(10,0) DECIMAL(10) NOT NULL Catalog Version Id

5 EOCM_CAT_HIER_ID Character(32) VARCHAR2(32) NOT NULL Catalog Hierarchy ID
Prompt Table: EOCM_CATEGORY_H

6 EOCM_CATEGORY_ID Character(32) VARCHAR2(32) NOT NULL Category ID
Prompt Table: EOCM_CATEGORY

7 EOCM_ACCESS_ID Number(15,0) DECIMAL(15) NOT NULL Field for storing the Partner ID

8 EOCM_TP_VER_ID Number(3,0) SMALLINT NOT NULL trading partner version id

9 EOCM_ROW_ID Number(10,0) DECIMAL(10) NOT NULL Row ID

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

11 EFF_STATUS Character(1) VARCHAR2(1) NOT NULL Effective Status
A=Active
I=Inactive

12 EOCM_OFFER_ID Character(32) VARCHAR2(32) NOT NULL Offering ID

13 EOCM_OFFER_NM Character(64) VARCHAR2(64) NOT NULL Offer Name

14 EOCM_SUP_OFFER_NM Character(64) VARCHAR2(64) NOT NULL Supplier Offering Name

15 EOCM_SUP_OFFER_SKU Character(50) VARCHAR2(50) NOT NULL Supplier Offering SKU

16 EOCM_SUP_OFFER_UOM Character(32) VARCHAR2(32) NOT NULL Supplier Offering UOM

17 EOCM_SUP_CAT_NM Character(254) VARCHAR2(254) NOT NULL Supplier Category Name

18 EOCM_SUP_CAT_CD Character(32) VARCHAR2(32) NOT NULL Supplier Category Code

19 EOCM_SUP_OFFER_PR Number(16,5) DECIMAL(15,5) NOT NULL Supplier Offering Price

20 EOCM_SUP_OFFER_D Character(64) VARCHAR2(64) NOT NULL Supplier Offering Description

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

22 EOCM_EPRO_FILENAME Character(80) VARCHAR2(80) NOT NULL Epro file name

23 EOCM_FILEEXTENSION Character(3) VARCHAR2(3) NOT NULL Epro File Extension

24 EOCM_FILEPATH Character(30) VARCHAR2(30) NOT NULL Epro File path

25 URL Character(254) VARCHAR2(254) NOT NULL Internet URL (Universal Resource Locator)

26 EOCM_MFG_ID Character(50) VARCHAR2(50) NOT NULL Manufacturer's Part ID

27 EOCM_MFG_PART_NUM Character(50) VARCHAR2(50) NOT NULL Manufacturer Part Number

28 EOCM_CONVERSION_RT Signed Number(17,8) DECIMAL(15,8) NOT NULL ePro field

29 EOCM_DISCONTINU_DT Date(10) DATE Distcontinue Date for epro

30 EOCM_MIN_QTY Number(16,4) DECIMAL(15,4) NOT NULL Minimum Quantity epro

31 EOCM_LEAD_TIME Number(11,0) DECIMAL(11) NOT NULL Lead Time epro

32 EOCM_ITMVPRIORITY Number(3,0) SMALLINT NOT NULL epro item vendor priority

33 EOCM_EPRO_DESCR Character(254) VARCHAR2(254) NOT NULL epro specific field

34 EOCM_VNDR_PART_NUM Character(50) VARCHAR2(50) NOT NULL Epro specific field

35 EOCM_VNDR_PART_EXT Character(50) VARCHAR2(50) NOT NULL epro specific field

36 EOCM_ELM_CEU Number(3,0) SMALLINT NOT NULL Continuing Education Units

37 EOCM_ELM_KEYWORDS Character(30) VARCHAR2(30) NOT NULL ELM keywords

38 EOCM_LEARN_OBJ_ID Number(10,0) DECIMAL(10) NOT NULL ELM Learning Objectives ID

39 EOCM_REQUISITE_ID Number(10,0) DECIMAL(10) NOT NULL ELM Requisite ID

40 EOCM_ELM_NOTES Character(80) VARCHAR2(80) NOT NULL ELM Notes

41 EOCM_MODEL_NBR Character(18) VARCHAR2(18) NOT NULL CRM specific field

42 EOCM_CRM_CAT_NBR Character(18) VARCHAR2(18) NOT NULL CRM specific field

43 EOCM_PROD_KIT_FLAG Character(1) VARCHAR2(1) NOT NULL CRM specific field
Y/N Table Edit

44 EOCM_CRM_SERVICE Character(1) VARCHAR2(1) NOT NULL CRM specific field
Y/N Table Edit

45 EOCM_PROD_BRAND Character(15) VARCHAR2(15) NOT NULL CRM specific field

46 EOCM_CRM_ORDERABLE Character(1) VARCHAR2(1) NOT NULL CRM specific field
Y/N Table Edit

47 EOCM_PROP_NAME_1 Character(32) VARCHAR2(32) NOT NULL Property 1 Name

48 EOCM_PROP_VAL_1 Character(64) VARCHAR2(64) NOT NULL Property 1 Value

49 EOCM_PROP_NAME_2 Character(32) VARCHAR2(32) NOT NULL Property 2 Name

50 EOCM_PROP_VAL_2 Character(64) VARCHAR2(64) NOT NULL Property Value 2

51 EOCM_PROP_NAME_3 Character(32) VARCHAR2(32) NOT NULL Property 3 Name

52 EOCM_PROP_VAL_3 Character(64) VARCHAR2(64) NOT NULL Property 3 Value

53 EOCM_PROP_NAME_4 Character(32) VARCHAR2(32) NOT NULL Property Name 4

54 EOCM_PROP_VAL_4 Character(64) VARCHAR2(64) NOT NULL Property 4 Value

55 EOCM_PROP_NAME_5 Character(32) VARCHAR2(32) NOT NULL Property 5 Name

56 EOCM_PROP_VAL_5 Character(64) VARCHAR2(64) NOT NULL Property 5 Value

57 EOCM_OFFER_STATUS Character(1) VARCHAR2(1) NOT NULL Offering Status
C=Categorized
E=Error
L=Loaded
N=Rejected
S=Staged

58 EOCM_OFFER_STATE Character(1) VARCHAR2(1) NOT NULL State of the suppl product
A=New/Unapproved
C=Unchanged
D=Deleted
E=Error
N=New
U=Updated

59 MESSAGE_NBR Number(5,0) INTEGER NOT NULL Message Number. This field refers to the Message Number in the Message Catalog.

60 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.

61 LASTUPDOPRID Character(30) VARCHAR2(30) NOT NULL Specifies the User ID which made 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	OPRID	Character(30)	VARCHAR2(30) NOT NULL	A user's ID (see PSOPRDEFN).
2	SETID	Character(5)	VARCHAR2(5) NOT NULL	SetID Prompt Table: SETID_TBL
3	EOCM_CATALOG_ID	Character(32)	VARCHAR2(32) NOT NULL	Catalog ID
4	EOCM_CAT_VER_ID	Number(10,0)	DECIMAL(10) NOT NULL	Catalog Version Id
5	EOCM_CAT_HIER_ID	Character(32)	VARCHAR2(32) NOT NULL	Catalog Hierarchy ID Prompt Table: EOCM_CATEGORY_H
6	EOCM_CATEGORY_ID	Character(32)	VARCHAR2(32) NOT NULL	Category ID Prompt Table: EOCM_CATEGORY
7	EOCM_ACCESS_ID	Number(15,0)	DECIMAL(15) NOT NULL	Field for storing the Partner ID
8	EOCM_TP_VER_ID	Number(3,0)	SMALLINT NOT NULL	trading partner version id
9	EOCM_ROW_ID	Number(10,0)	DECIMAL(10) NOT NULL	Row ID
10	EFFDT	Date(10)	DATE	Effective Date Default Value: %date
11	EFF_STATUS	Character(1)	VARCHAR2(1) NOT NULL	Effective Status A=Active I=Inactive
12	EOCM_OFFER_ID	Character(32)	VARCHAR2(32) NOT NULL	Offering ID
13	EOCM_OFFER_NM	Character(64)	VARCHAR2(64) NOT NULL	Offer Name
14	EOCM_SUP_OFFER_NM	Character(64)	VARCHAR2(64) NOT NULL	Supplier Offering Name
15	EOCM_SUP_OFFER_SKU	Character(50)	VARCHAR2(50) NOT NULL	Supplier Offering SKU
16	EOCM_SUP_OFFER_UOM	Character(32)	VARCHAR2(32) NOT NULL	Supplier Offering UOM
17	EOCM_SUP_CAT_NM	Character(254)	VARCHAR2(254) NOT NULL	Supplier Category Name
18	EOCM_SUP_CAT_CD	Character(32)	VARCHAR2(32) NOT NULL	Supplier Category Code
19	EOCM_SUP_OFFER_PR	Number(16,5)	DECIMAL(15,5) NOT NULL	Supplier Offering Price
20	EOCM_SUP_OFFER_D	Character(64)	VARCHAR2(64) NOT NULL	Supplier Offering Description
21	CURRENCY_CD	Character(3)	VARCHAR2(3) NOT NULL	Currency Code
22	EOCM_EPRO_FILENAME	Character(80)	VARCHAR2(80) NOT NULL	Epro file name
23	EOCM_FILEEXTENSION	Character(3)	VARCHAR2(3) NOT NULL	Epro File Extension
24	EOCM_FILEPATH	Character(30)	VARCHAR2(30) NOT NULL	Epro File path
25	URL	Character(254)	VARCHAR2(254) NOT NULL	Internet URL (Universal Resource Locator)
26	EOCM_MFG_ID	Character(50)	VARCHAR2(50) NOT NULL	Manufacturer's Part ID
27	EOCM_MFG_PART_NUM	Character(50)	VARCHAR2(50) NOT NULL	Manufacturer Part Number
28	EOCM_CONVERSION_RT	Signed Number(17,8)	DECIMAL(15,8) NOT NULL	ePro field
29	EOCM_DISCONTINU_DT	Date(10)	DATE	Distcontinue Date for epro
30	EOCM_MIN_QTY	Number(16,4)	DECIMAL(15,4) NOT NULL	Minimum Quantity epro
31	EOCM_LEAD_TIME	Number(11,0)	DECIMAL(11) NOT NULL	Lead Time epro
32	EOCM_ITMVPRIORITY	Number(3,0)	SMALLINT NOT NULL	epro item vendor priority
33	EOCM_EPRO_DESCR	Character(254)	VARCHAR2(254) NOT NULL	epro specific field
34	EOCM_VNDR_PART_NUM	Character(50)	VARCHAR2(50) NOT NULL	Epro specific field
35	EOCM_VNDR_PART_EXT	Character(50)	VARCHAR2(50) NOT NULL	epro specific field
36	EOCM_ELM_CEU	Number(3,0)	SMALLINT NOT NULL	Continuing Education Units
37	EOCM_ELM_KEYWORDS	Character(30)	VARCHAR2(30) NOT NULL	ELM keywords
38	EOCM_LEARN_OBJ_ID	Number(10,0)	DECIMAL(10) NOT NULL	ELM Learning Objectives ID
39	EOCM_REQUISITE_ID	Number(10,0)	DECIMAL(10) NOT NULL	ELM Requisite ID
40	EOCM_ELM_NOTES	Character(80)	VARCHAR2(80) NOT NULL	ELM Notes
41	EOCM_MODEL_NBR	Character(18)	VARCHAR2(18) NOT NULL	CRM specific field
42	EOCM_CRM_CAT_NBR	Character(18)	VARCHAR2(18) NOT NULL	CRM specific field
43	EOCM_PROD_KIT_FLAG	Character(1)	VARCHAR2(1) NOT NULL	CRM specific field Y/N Table Edit
44	EOCM_CRM_SERVICE	Character(1)	VARCHAR2(1) NOT NULL	CRM specific field Y/N Table Edit
45	EOCM_PROD_BRAND	Character(15)	VARCHAR2(15) NOT NULL	CRM specific field
46	EOCM_CRM_ORDERABLE	Character(1)	VARCHAR2(1) NOT NULL	CRM specific field Y/N Table Edit
47	EOCM_PROP_NAME_1	Character(32)	VARCHAR2(32) NOT NULL	Property 1 Name
48	EOCM_PROP_VAL_1	Character(64)	VARCHAR2(64) NOT NULL	Property 1 Value
49	EOCM_PROP_NAME_2	Character(32)	VARCHAR2(32) NOT NULL	Property 2 Name
50	EOCM_PROP_VAL_2	Character(64)	VARCHAR2(64) NOT NULL	Property Value 2
51	EOCM_PROP_NAME_3	Character(32)	VARCHAR2(32) NOT NULL	Property 3 Name
52	EOCM_PROP_VAL_3	Character(64)	VARCHAR2(64) NOT NULL	Property 3 Value
53	EOCM_PROP_NAME_4	Character(32)	VARCHAR2(32) NOT NULL	Property Name 4
54	EOCM_PROP_VAL_4	Character(64)	VARCHAR2(64) NOT NULL	Property 4 Value
55	EOCM_PROP_NAME_5	Character(32)	VARCHAR2(32) NOT NULL	Property 5 Name
56	EOCM_PROP_VAL_5	Character(64)	VARCHAR2(64) NOT NULL	Property 5 Value
57	EOCM_OFFER_STATUS	Character(1)	VARCHAR2(1) NOT NULL	Offering Status C=Categorized E=Error L=Loaded N=Rejected S=Staged
58	EOCM_OFFER_STATE	Character(1)	VARCHAR2(1) NOT NULL	State of the suppl product A=New/Unapproved C=Unchanged D=Deleted E=Error N=New U=Updated
59	MESSAGE_NBR	Number(5,0)	INTEGER NOT NULL	Message Number. This field refers to the Message Number in the Message Catalog.
60	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.
61	LASTUPDOPRID	Character(30)	VARCHAR2(30) NOT NULL	Specifies the User ID which made the last update to an entry. This field is maintained by PeopleSoft and is used in a variety of contexts.