PGM_MAT_TEAM

(SQL View)
Index Back

Materials View


SELECT A.SETID , B.BUSINESS_UNIT , A.INV_ITEM_ID , A.DESCR , A.UNIT_MEASURE_STD , B.SOURCE_CODE , A.INVENTORY_ITEM , A.ITM_STATUS_EFFDT , A.CATEGORY_ID FROM PS_MASTER_ITEM_TBL A , PS_BU_ITEMS_INV B , PS_SET_CNTRL_REC CNTRL WHERE CNTRL.RECNAME = 'MASTER_ITEM_TBL' AND CNTRL.SETCNTRLVALUE = B.BUSINESS_UNIT AND CNTRL.SETID = A.SETID AND A.INV_ITEM_ID = B.INV_ITEM_ID AND A.ITM_STATUS_CURRENT = '1' AND A.ITM_STATUS_EFFDT = ( SELECT MAX (X.ITM_STATUS_EFFDT) FROM PS_MASTER_ITEM_TBL X WHERE A.INV_ITEM_ID = X.INV_ITEM_ID AND A. SETID = X.SETID AND X.ITM_STATUS_EFFDT <= %CurrentDateIn)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 SETID Character(5) VARCHAR2(5) NOT NULL SetID

Prompt Table: SP_SETID_NONVW

2 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
3 INV_ITEM_ID Character(18) VARCHAR2(18) NOT NULL Item ID
4 DESCR Character(30) VARCHAR2(30) NOT NULL Description
5 UNIT_MEASURE_STD Character(3) VARCHAR2(3) NOT NULL Standard Unit of Measure
6 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
7 INVENTORY_ITEM Character(1) VARCHAR2(1) NOT NULL Inventory Item
N=No
Y=Yes
8 ITM_STATUS_EFFDT Date(10) DATE Item Status Date
9 CATEGORY_ID Character(5) VARCHAR2(5) NOT NULL Category ID