IN_USAGE_VW(SQL View) |
Index Back |
|---|---|
Main View for Usage Inquiry |
| SELECT A.BUSINESS_UNIT , A.INV_ITEM_ID , F.CATEGORY_CD , B.INV_PROD_FAM_CD , B.INV_ITEM_GROUP , G.MFG_ID , G.MFG_ITM_ID , B.UNIT_MEASURE_STD , A.QTY_ONHAND , A.QTY_AVAILABLE , A.NO_REPLENISH_FLG , A.REORDER_POINT , A.REORDER_QTY , A.QTY_MAXIMUM , A.SAFETY_STOCK , A.EOQ , A.SOURCE_CODE , A.LAST_PRICE_PAID , A.AVERAGE_COST , B.SETID FROM PS_BU_ITEMS_INV A , PS_MASTER_ITEM_TBL B , PS_ITEM_MFG G , PS_SET_CNTRL_REC C , PS_ITM_CAT_TBL F WHERE A.INV_ITEM_ID = B.INV_ITEM_ID AND C.SETID = B.SETID AND C.SETCNTRLVALUE=A.BUSINESS_UNIT AND C.REC_GROUP_ID = 'FS_18' AND C.RECNAME = 'MASTER_ITEM_TBL' AND B.SETID = G.SETID (+) AND B.INV_ITEM_ID = G.INV_ITEM_ID (+) AND G.PREFERRED_MFG (+) = 'Y' AND B.SETID = F.SETID AND B.CATEGORY_ID = F.CATEGORY_ID AND F.EFFDT = ( SELECT MAX(F1.EFFDT) FROM PS_ITM_CAT_TBL F1 WHERE F.SETID = F1.SETID AND F.CATEGORY_TYPE = F1.CATEGORY_TYPE AND F.CATEGORY_CD = F1.CATEGORY_CD AND F.CATEGORY_ID = F1.CATEGORY_ID AND F.EFFDT <= %CurrentDateIn) |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 |  BUSINESS_UNIT |
Character(5) | VARCHAR2(5) NOT NULL |
Business Unit
Default Value: OPR_DEF_TBL_FS.BUSINESS_UNIT Prompt Table:
SP_BUIN_NONVW
|
| 2 | INV_ITEM_ID |
Character(18) | VARCHAR2(18) NOT NULL |
Item ID
Prompt Table: BU_ITM_INV_VW |
| 3 | CATEGORY_CD | Character(18) | VARCHAR2(18) NOT NULL |
Category Code
Prompt Table: ITM_CAT_VW |
| 4 | INV_PROD_FAM_CD | Character(10) | VARCHAR2(10) NOT NULL |
Family
Prompt Table: BU_ITM_FAM_VW |
| 5 | INV_ITEM_GROUP | Character(15) | VARCHAR2(15) NOT NULL |
Item Group
Prompt Table: BU_ITM_GROUP_VW |
| 6 | MFG_ID | Character(50) | VARCHAR2(50) NOT NULL |
Manufacturer ID
Prompt Table: BU_MFG_VW |
| 7 | MFG_ITM_ID | Character(50) | VARCHAR2(50) NOT NULL | Manufacturer's Item ID |
| 8 | STANDARD_UOM | Character(3) | VARCHAR2(3) NOT NULL | Standard Unit of Measure |
| 9 | ONHAND_QTY | Signed Number(17,4) | DECIMAL(15,4) NOT NULL | On Hand Quantity |
| 10 | AVAIL_QTY | Signed Number(17,4) | DECIMAL(15,4) NOT NULL | Available Qty |
| 11 | NO_REPLENISH_FLG | Character(1) | VARCHAR2(1) NOT NULL |
Not Included In Replenishment
Y/N Table Edit |
| 12 | REORDER_POINT | Signed Number(17,4) | DECIMAL(15,4) NOT NULL | Reorder Point |
| 13 | REORDER_QTY | Signed Number(17,4) | DECIMAL(15,4) NOT NULL | Reorder Qty |
| 14 | MAX_QTY | Signed Number(20,6) | DECIMAL(18,6) NOT NULL | Maximum Quantity |
| 15 | SAFETY_STOCK | Signed Number(17,4) | DECIMAL(15,4) NOT NULL | Safety Stock |
| 16 | EOQ | Signed Number(17,4) | DECIMAL(15,4) NOT NULL | Economic Order Quantity |
| 17 | SOURCE_CODE | Character(2) | VARCHAR2(2) NOT NULL |
8/5/97 lee - inactivated source code 4 - service
1=Make 2=Buy 3=Expense 4=Service 5=Floor Stock 6=Planning |
| 18 | LAST_PRICE_PAID | Number(15,4) | DECIMAL(14,4) NOT NULL | Last Price Paid |
| 19 | AVERAGE_COST | Number(15,4) | DECIMAL(14,4) NOT NULL | Average Purchase Price |
| 20 | SETID | Character(5) | VARCHAR2(5) NOT NULL | SetID |