ITM_VNDRSUB_VW3

(SQL View)
Index Back

Substitute Item Table

02/12/2015 JOHAN 18544902 We ensured that override suggested supplier flag is allowed to edit if the item substitution is only at item level and not at supplier level.

SELECT A.SETID ,A.INV_ITEM_ID ,D.VENDOR_SETID ,D.VENDOR_ID ,F.VNDR_LOC ,A.SUB_ITM_ID ,A.SUB_PRIORITY_NBR ,B.DESCR ,B.CATEGORY_ID ,C.CATEGORY_CD ,A.EFFDT_FROM ,A.EFFDT_TO ,A.UNIT_OF_MEASURE ,A.CONVERSION_RATE ,A.USE_PROCUREMENT FROM PS_ITM_VNDR_SUB A , PS_MASTER_ITEM_TBL B , PS_ITM_CAT_TBL C , PS_ITM_VENDOR D , PS_ITM_VENDOR_LOC F WHERE A.USE_PROCUREMENT IN ('B','A','M') AND A.SETID = B.SETID AND B.INV_ITEM_ID = A.SUB_ITM_ID AND C.SETID = B.SETID AND C.CATEGORY_ID = B.CATEGORY_ID AND A.SETID = D.SETID AND A.INV_ITEM_ID = D.INV_ITEM_ID AND A.VENDOR_SETID = D.VENDOR_SETID AND A.VENDOR_ID = D.VENDOR_ID AND A.SETID = F.SETID AND A.INV_ITEM_ID = F.INV_ITEM_ID AND A.VENDOR_SETID = F.VENDOR_SETID AND A.VENDOR_ID = F.VENDOR_ID AND A.VNDR_LOC = F.VNDR_LOC AND A.EFFDT_FROM >= C.EFFDT AND C.EFFDT = ( SELECT MAX(E.EFFDT) FROM PS_ITM_CAT_TBL E WHERE C.SETID = E.SETID AND C.CATEGORY_TYPE = E.CATEGORY_TYPE AND C.CATEGORY_CD = E.CATEGORY_CD AND C.CATEGORY_ID = E.CATEGORY_ID AND C.EFFDT <= A.EFFDT_TO)

# 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_VW4

3 VENDOR_SETID Character(5) VARCHAR2(5) NOT NULL Vendor SetID
4 VENDOR_ID Character(10) VARCHAR2(10) NOT NULL Vendor Identifier
5 VNDR_LOC Character(10) VARCHAR2(10) NOT NULL Vendor Location
6 SUB_ITM_ID Character(18) VARCHAR2(18) NOT NULL 11/30/99 RML Added label "Substitute"

Prompt Table: ITM_PURCH_VW

7 SUB_PRIORITY_NBR Number(3,0) SMALLINT NOT NULL 03/30/99 laj...CN#EN800-1.0 This field was "cloned" for BOM mass maintenance. Any field attribute changes here need also to be to done to en_new_sub_pri_nbr.
8 DESCR Character(30) VARCHAR2(30) NOT NULL Description
9 CATEGORY_ID Character(5) VARCHAR2(5) NOT NULL Category ID
10 CATEGORY_CD Character(18) VARCHAR2(18) NOT NULL Category Code
11 EFFDT_FROM Date(10) DATE Effective Date From
12 EFFDT_TO Date(10) DATE Effective Date To
13 UNIT_OF_MEASURE Character(3) VARCHAR2(3) NOT NULL Used on an approval rule set.
MHR=Muti Hourly
PER=Percentage
SQF=Square Footage

Prompt Table: %EDIT_UOM

14 CONVERSION_RATE Signed Number(17,8) DECIMAL(15,8) NOT NULL Conversion Rate
15 USE_PROCUREMENT Character(1) VARCHAR2(1) NOT NULL Use for Procurement
A=Automatic Substitution
B=Manual and Automatic
M=Manual Substitution
N=Do Not Use