AUC_INV_ITEM_VW(SQL View) |
Index Back |
|---|---|
Auctions Inventory Item View |
| SELECT A.SETID ,A.INV_ITEM_ID ,C.UNIT_MEASURE_STD ,A.INV_ITEM_HEIGHT ,A.INV_ITEM_LENGTH ,A.INV_ITEM_WIDTH ,A.INV_ITEM_WEIGHT ,A.INV_ITEM_VOLUME ,A.INV_ITEM_SIZE ,A.INV_ITEM_COLOR ,A.UNIT_MEASURE_DIM ,A.UNIT_MEASURE_WT ,A.UNIT_MEASURE_VOL ,B.DESCR ,B.DESCRSHORT ,D.CATEGORY_CD ,C.CATEGORY_ID ,C.INV_ITEM_GROUP ,C.INV_PROD_FAM_CD ,B.DESCR254_MIXED ,B.STD_LEAD ,B.PRICE_LIST ,B.CURRENCY_CD ,C.PHYSICAL_NATURE ,B.VAT_SVC_PERFRM_FLG ,D.MARKETCODE ,A.SETID ,B.PRICE_LIST ,C.UNIT_MEASURE_STD ,B.STD_LEAD ,1 FROM PS_INV_ITEMS A , PS_PURCH_ITEM_ATTR B , PS_MASTER_ITEM_TBL C , PS_ITM_CAT_TBL D WHERE A.SETID = B.SETID AND A.INV_ITEM_ID = B.INV_ITEM_ID AND B.SETID = C.SETID AND B.INV_ITEM_ID = C.INV_ITEM_ID AND C.ITM_STATUS_CURRENT = '1' AND D.EFF_STATUS = 'A' AND A.EFFDT = ( SELECT MAX(EFFDT) FROM PS_INV_ITEMS AA WHERE AA.SETID = A.SETID AND AA.INV_ITEM_ID = A.INV_ITEM_ID AND AA.EFFDT <= %CurrentDateIn) AND ((%CurrentDateIn >= B.PO_AVAIL_DT) AND (B.PO_UNAVAIL_DT > %CurrentDateIn)) AND D.SETID = C.SETID AND D.CATEGORY_ID = C.CATEGORY_ID AND D.EFFDT = ( SELECT MAX(E.EFFDT) FROM PS_ITM_CAT_TBL E WHERE E.SETID = D.SETID AND E.CATEGORY_TYPE = D.CATEGORY_TYPE AND E.CATEGORY_CD = D.CATEGORY_CD AND E.CATEGORY_ID = D.CATEGORY_ID AND E.EFFDT <= %CurrentDateIn) |
| # | 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 | INV_ITEM_ID |
Character(18) | VARCHAR2(18) NOT NULL |
Item ID
Prompt Table: MST_ITM_VW |
| 3 | UNIT_MEASURE_STD | Character(3) | VARCHAR2(3) NOT NULL | Standard Unit of Measure |
| 4 | AUC_ITEM_HEIGHT | Signed Number(17,4) | DECIMAL(15,4) NOT NULL | Item Height |
| 5 | AUC_ITEM_LENGTH | Signed Number(17,4) | DECIMAL(15,4) NOT NULL | Item Length |
| 6 | AUC_ITEM_WIDTH | Signed Number(17,4) | DECIMAL(15,4) NOT NULL | Item Width |
| 7 | AUC_ITEM_WEIGHT | Signed Number(17,4) | DECIMAL(15,4) NOT NULL | Item Weight |
| 8 | AUC_ITEM_VOLUME | Signed Number(17,4) | DECIMAL(15,4) NOT NULL | Item Volume |
| 9 | AUC_ITEM_SIZE | Character(5) | VARCHAR2(5) NOT NULL | Item Size |
| 10 | AUC_ITEM_COLOR | Character(15) | VARCHAR2(15) NOT NULL | Sourcing Item Color |
| 11 | AUC_DIMENSION_UOM | Character(3) | VARCHAR2(3) NOT NULL | Dimension UOM |
| 12 | AUC_WEIGHT_UOM | Character(3) | VARCHAR2(3) NOT NULL | Weight UOM |
| 13 | AUC_VOLUME_UOM | Character(3) | VARCHAR2(3) NOT NULL | Volume UOM |
| 14 | DESCR | Character(30) | VARCHAR2(30) NOT NULL | Description |
| 15 | DESCRSHORT | Character(10) | VARCHAR2(10) NOT NULL | Short Description |
| 16 | CATEGORY_CD | Character(18) | VARCHAR2(18) NOT NULL |
Category Code
Prompt Table: ITM_CAT_VW |
| 17 | CATEGORY_ID | Character(5) | VARCHAR2(5) NOT NULL |
Category ID
Prompt Table: ITM_CAT_VW |
| 18 | INV_ITEM_GROUP | Character(15) | VARCHAR2(15) NOT NULL |
Item Group
Prompt Table: INV_ITEM_GROUP |
| 19 | INV_PROD_FAM_CD | Character(10) | VARCHAR2(10) NOT NULL |
Family
Prompt Table: INV_ITEM_FAM |
| 20 | DESCR254_MIXED | Character(254) | VARCHAR2(254) NOT NULL | Description |
| 21 | STD_LEAD | Number(3,0) | SMALLINT NOT NULL | Purchase Lead Time Days |
| 22 | PRICE_LIST | Number(16,5) | DECIMAL(15,5) NOT NULL | Standard Price |
| 23 | CURRENCY_CD | Character(3) | VARCHAR2(3) NOT NULL | Currency Code |
| 24 | PHYSICAL_NATURE | Character(1) | VARCHAR2(1) NOT NULL |
Physical Nature
G=Goods S=Services |
| 25 | VAT_SVC_PERFRM_FLG | Character(1) | VARCHAR2(1) NOT NULL |
Where VAT Services Physically Performed Flag
1=Ship To Location 2=Ship From Location 3=Buyer's Location 4=Supplier's Location |
| 26 | 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 |
| 27 | ITM_SETID | Character(5) | VARCHAR2(5) NOT NULL | Item SetID |
| 28 | PRICE_AUC | Number(17,5) | DECIMAL(16,5) NOT NULL | Event Price |
| 29 | UNIT_OF_MEASURE | Character(3) | VARCHAR2(3) NOT NULL |
Used on an approval rule set.
MHR=Muti Hourly PER=Percentage SQF=Square Footage |
| 30 | LEAD_TIME | Number(3,0) | SMALLINT NOT NULL | Lead Time Days |
| 31 | QTY_AUC | Number(16,4) | DECIMAL(15,4) NOT NULL | Event Quantity |