PL_SRC_VNDNP_VW

(SQL View)
 Index Back

PL Sourcing Suppliers w/o Pric

11/13/01 EGS (CN#PL900-1): Added

SELECT J.BUSINESS_UNIT , J.INV_ITEM_ID , D.VENDOR_SETID , D.VENDOR_ID , E.VNDR_LOC , F.VENDOR_NAME_SHORT , D.ITM_VNDR_PRIORITY , D.ITMV_PRIORITY_LOC , E.LEAD_TIME , H.PRICE_LIST , J.PUR_MIN_ORDER , J.PUR_MAX_ORDER , J.PUR_ORDER_MULTIPLE , J.PURCHASE_YIELD , H.STD_LEAD , E.USE_STD_LEAD_TIME , H.CURRENCY_CD , 'Y' , 0 , 0 , 0 FROM PS_PL_ITEM_ATTRIB J , PS_ITM_VENDOR D , PS_ITM_VENDOR_LOC E , PS_VENDOR F , PS_PURCH_ITEM_ATTR H , PS_SET_CNTRL_REC I WHERE D.SETID = E.SETID AND D.VENDOR_SETID = E.VENDOR_SETID AND D.INV_ITEM_ID = E.INV_ITEM_ID AND D.VENDOR_ID = E.VENDOR_ID AND F.SETID = D.VENDOR_SETID AND F.VENDOR_ID = D.VENDOR_ID AND D.INV_ITEM_ID = J.INV_ITEM_ID AND D.ITM_STATUS = 'A' AND I.RECNAME = 'PURCH_ITEM_ATTR' AND I.SETID = H.SETID AND I.SETCNTRLVALUE = J.BUSINESS_UNIT AND H.INV_ITEM_ID = J.INV_ITEM_ID AND D.INCL_IN_PLAN = 'Y' AND H.INCL_IN_PLAN = 'Y' AND D.SETID = H.SETID AND D.VENDOR_ID <> ' ' AND F.VENDOR_STATUS = 'A' AND F.VNDR_STATUS_PO = 'Y' AND J.SPOT_BUY_FLG = 'N' AND ((J.USE_SHIPTO_LOC = 'N' AND J.USE_REGION_VNDR = 'N') OR (J.USE_SHIPTO_LOC = 'Y' AND J.USE_REGION_VNDR = 'N' AND EXISTS ( SELECT 'X' FROM PS_SHIPTO_TBL G , PS_ITM_SHIPTO_VNDR H WHERE H.SETID = D.SETID AND H.INV_ITEM_ID = D.INV_ITEM_ID AND H.VENDOR_SETID = D.VENDOR_SETID AND H.VENDOR_ID = D.VENDOR_ID AND H.VNDR_LOC = E.VNDR_LOC AND H.SHIPTO_SETID = G.SETID AND H.SHIPTO_ID = G.SHIPTO_ID AND G.BUSINESS_UNIT_IN = J.BUSINESS_UNIT AND H.INCL_IN_PLAN = 'Y')) OR (J.USE_REGION_VNDR = 'Y' AND J.USE_SHIPTO_LOC='N' AND EXISTS ( SELECT 'X' FROM PS_SHIPTO_TBL GG , PS_ITM_REGION_VNDR HH WHERE HH.SETID = D.SETID AND HH.INV_ITEM_ID = D.INV_ITEM_ID AND HH.VENDOR_SETID = D.VENDOR_SETID AND HH.VENDOR_ID = D.VENDOR_ID AND HH.VNDR_LOC = E.VNDR_LOC AND HH.SETID_REGION = GG.SETID_REGION AND HH.REGION_CD = GG.REGION_CD AND GG.BUSINESS_UNIT_IN = J.BUSINESS_UNIT AND HH.INCL_IN_PLAN = 'Y'))) AND NOT EXISTS ( SELECT 'X' FROM PS_ITM_VNDR_UOM_PR A , PS_VENDOR_LOC B , PS_ITM_VNDR_UOM C WHERE B.SETID = D.VENDOR_SETID AND B.VENDOR_ID = D.VENDOR_ID AND B.VNDR_LOC = E.VNDR_LOC AND B.EFFDT = ( SELECT MAX(EFFDT) FROM PS_VENDOR_LOC BB WHERE BB.SETID = B.SETID AND BB.VENDOR_ID = B.VENDOR_ID AND BB.VNDR_LOC = B.VNDR_LOC AND BB.EFF_STATUS = 'A' AND BB.EFFDT <= %CurrentDateIn) AND C.SETID = D.SETID AND C.INV_ITEM_ID = J.INV_ITEM_ID AND C.VENDOR_SETID = B.SETID AND C.VENDOR_ID = B.VENDOR_ID AND C.VNDR_LOC = B.VNDR_LOC AND C.DFLT_UOM = 'Y' AND A.SETID = C.SETID AND A.INV_ITEM_ID = C.INV_ITEM_ID AND A.VENDOR_SETID = B.PRICE_SETID AND A.VENDOR_ID = B.PRICE_VENDOR AND A.VNDR_LOC = B.PRICE_LOC AND A.UNIT_OF_MEASURE = C.UNIT_OF_MEASURE AND A.EFFDT = ( SELECT MAX(AA.EFFDT) FROM PS_ITM_VNDR_UOM_PR AA WHERE AA.SETID = A.SETID AND AA.INV_ITEM_ID = A.INV_ITEM_ID AND AA.VENDOR_SETID = A.VENDOR_SETID AND AA.VENDOR_ID = A.VENDOR_ID AND AA.VNDR_LOC = A.VNDR_LOC AND AA.UNIT_OF_MEASURE = A.UNIT_OF_MEASURE AND AA.CURRENCY_CD = A.CURRENCY_CD AND AA.EFF_STATUS = 'A' AND AA.EFFDT <= %CurrentDateIn) AND A.QTY_MIN = ( SELECT MIN(AAA.QTY_MIN) FROM PS_ITM_VNDR_UOM_PR AAA WHERE AAA.SETID = A.SETID AND AAA.INV_ITEM_ID = A.INV_ITEM_ID AND AAA.VENDOR_SETID = A.VENDOR_SETID AND AAA.VENDOR_ID = A.VENDOR_ID AND AAA.VNDR_LOC = A.VNDR_LOC AND AAA.UNIT_OF_MEASURE = A.UNIT_OF_MEASURE AND AAA.CURRENCY_CD = A.CURRENCY_CD AND AAA.EFFDT = A.EFFDT))

# 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
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 VENDOR_NAME_SHORT Character(14) VARCHAR2(14) NOT NULL Short Vendor Name
7 ITM_VNDR_PRIORITY Number(3,0) SMALLINT NOT NULL Item Vendor Priority
8 ITMV_PRIORITY_LOC Character(10) VARCHAR2(10) NOT NULL Item Supplier Priority Loc
9 LEAD_TIME Number(3,0) SMALLINT NOT NULL Lead Time Days
10 PRICE_LIST Number(16,5) DECIMAL(15,5) NOT NULL Standard Price
11 PUR_MIN_ORDER Number(16,4) DECIMAL(15,4) NOT NULL CN#PL800-18.0 MACN 11/02/99 - Changed short description from Minimum Ord Qty to Min Order Qty. jnw 01/20/00 di-jwillia2-2 - expanded field format from 7.0 to 7.4 KPN 08/23/02: Added label PUR_MIN_ORDER2
12 PUR_MAX_ORDER Number(16,4) DECIMAL(15,4) NOT NULL jnw 01/20/00 di-jwillia2-2 - expanded field format from 7.0 to 7.4 KPN 08/23/02: Added label PUR_MAX_ORDER2
13 PUR_ORDER_MULTIPLE Number(12,4) DECIMAL(11,4) NOT NULL jnw 01/20/00 di-jwillia2-2 - expanded field format from 7.0 to 7.4
14 PURCHASE_YIELD Number(5,1) DECIMAL(4,1) NOT NULL Purchase Yield
15 STD_LEAD Number(3,0) SMALLINT NOT NULL Purchase Lead Time Days
16 USE_STD_LEAD_TIME Character(1) VARCHAR2(1) NOT NULL Use Item Standard Lead Time
17 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
18 ROW_EXISTS Character(1) VARCHAR2(1) NOT NULL 12/11/01 EGS (CN#PL900-1): Added.
19 QTY_MIN Number(16,4) DECIMAL(15,4) NOT NULL Minimum Quantity
20 QTY_MAX Number(16,4) DECIMAL(15,4) NOT NULL Maximum Quantity
21 ORDER_MULTIPLE Number(6,0) INTEGER NOT NULL Order Quantity Multiple