PV_ITM_CAT5_VW(SQL View) |
Index Back |
|---|---|
Active Category Codes |
| SELECT A.SETID ,A.CATEGORY_ID ,A.CATEGORY_CD ,A.EFFDT ,A.EFF_STATUS ,A.DESCR60 ,A.DESCRSHORT ,A.LEAD_TIME ,A.ACCOUNT ,A.ALTACCT ,A.PRIMARY_BUYER ,A.COMMENTS_LONG ,A.CURRENCY_CD ,A.PHYSICAL_NATURE ,A.MARKETCODE ,A.ULTIMATE_USE_CD FROM PS_ITM_CAT_TBL A WHERE A.EFFDT = ( SELECT MAX(A1.EFFDT) FROM PS_ITM_CAT_TBL A1 WHERE A1.SETID = A.SETID AND A1.CATEGORY_TYPE = A.CATEGORY_TYPE AND A1.CATEGORY_ID = A.CATEGORY_ID AND A1.EFFDT <= %CurrentDateIn) AND A.EFF_STATUS = 'A' |
| # | 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 Prompt Table: SP_SETID_NONVW |
| 2 | CATEGORY_ID |
Character(5) | VARCHAR2(5) NOT NULL | Category ID |
| 3 | CATEGORY_CD | Character(18) | VARCHAR2(18) NOT NULL | Category Code |
| 4 | EFFDT |
Date(10) | DATE |
Effective Date
Default Value: %date |
| 5 | EFF_STATUS | Character(1) | VARCHAR2(1) NOT NULL |
Effective Status
A=Active I=Inactive Default Value: A |
| 6 | DESCR60 | Character(60) | VARCHAR2(60) NOT NULL | Description |
| 7 | DESCRSHORT | Character(10) | VARCHAR2(10) NOT NULL | Short Description |
| 8 | LEAD_TIME | Number(3,0) | SMALLINT NOT NULL | Lead Time Days |
| 9 | ACCOUNT | Character(10) | VARCHAR2(10) NOT NULL |
Account
Prompt Table: %EDIT_ACCOUNT |
| 10 | ALTACCT | Character(10) | VARCHAR2(10) NOT NULL |
Alternate Account
Prompt Table: ALTACCT_XREF |
| 11 | PRIMARY_BUYER | Character(30) | VARCHAR2(30) NOT NULL |
Primary Buyer
Prompt Table: %EDIT_BUYER |
| 12 | COMMENTS_LONG | Long Character | CLOB | Comment Text |
| 13 | CURRENCY_CD | Character(3) | VARCHAR2(3) NOT NULL |
Currency Code
Prompt Table: CURRENCY_CD_TBL |
| 14 | PHYSICAL_NATURE | Character(1) | VARCHAR2(1) NOT NULL |
Physical Nature
G=Goods S=Services |
| 15 | MARKETCODE | Character(2) | VARCHAR2(2) NOT NULL |
This field is added to help the following data entry programs to determine how to handle different coding schemes for CATEGORY_CD:
- Item Categories (Component)
- PV_CP_LOAD (App. Engine)
- IN_ITMLOAD (App. Engine)
Since release 8, CATEGORY_TYPE was added to allow multiple coding schemes for CATEGORY_CD. The limitation imposed upon by how it was implemented is that although multiple coding schemes can be maintained separately but only one coding schemes can be used based on INSTALLATION_PO.CATEGORY_TYPE.
01=User Defined 02=UN/SPSC |
| 16 | ULTIMATE_USE_CD | Character(8) | VARCHAR2(8) NOT NULL | Ultimate Use Code |