PL_SRC_PIA_VW

(SQL View)
 Index Back

PL Sourcing Suppliers w/o Pric

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

SELECT B.BUSINESS_UNIT , B.INV_ITEM_ID , A.PRICE_LIST , B.PUR_MIN_ORDER , B.PUR_MAX_ORDER , B.PUR_ORDER_MULTIPLE , B.PURCHASE_YIELD , A.STD_LEAD , A.CURRENCY_CD , 'Y' FROM PS_PURCH_ITEM_ATTR A , PS_PL_ITEM_ATTRIB B , PS_SET_CNTRL_REC C WHERE C.RECNAME = 'PURCH_ITEM_ATTR' AND C.SETCNTRLVALUE = B.BUSINESS_UNIT AND A.SETID = C.SETID AND A.INV_ITEM_ID = B.INV_ITEM_ID AND A.INCL_IN_PLAN = 'Y' AND ((B.SPOT_BUY_FLG = 'N' AND B.USE_SHIPTO_LOC = 'N' AND B.USE_REGION_VNDR = 'N') OR B.SPOT_BUY_FLG = 'Y' OR (B.USE_SHIPTO_LOC = 'Y' AND EXISTS ( SELECT 'X' FROM PS_SHIPTO_TBL C , PS_ITM_SHIPTO D WHERE D.SETID = A.SETID AND D.INV_ITEM_ID = B.INV_ITEM_ID AND D.SHIPTO_SETID = C.SETID AND D.SHIPTO_ID = C.SHIPTO_ID AND D.INCL_IN_PLAN = 'Y' AND C.BUSINESS_UNIT_IN = B.BUSINESS_UNIT)) OR (B.USE_REGION_VNDR = 'Y' AND EXISTS ( SELECT 'X' FROM PS_SHIPTO_TBL GG , PS_ITM_REGION HH WHERE HH.SETID = A.SETID AND HH.INV_ITEM_ID = B.INV_ITEM_ID AND HH.SETID_REGION = GG.SETID_REGION AND HH.REGION_CD = GG.REGION_CD AND GG.BUSINESS_UNIT_IN = B.BUSINESS_UNIT )))

# 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 PRICE_LIST Number(16,5) DECIMAL(15,5) NOT NULL Standard Price
4 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
5 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
6 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
7 PURCHASE_YIELD Number(5,1) DECIMAL(4,1) NOT NULL Purchase Yield
8 LEAD_TIME Number(3,0) SMALLINT NOT NULL Lead Time Days
9 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
10 ROW_EXISTS Character(1) VARCHAR2(1) NOT NULL 12/11/01 EGS (CN#PL900-1): Added.