ITM_VNDBU_CONVW

(SQL View)
 Index Back

Consgn not in itm vnd bu attr

08/01/02 CEL CN#WV880-1.0: Supplier Collaboration

SELECT C.SETID , A.VENDOR_ID , D.SETID , A.INV_ITEM_ID , A.VNDR_LOC , A.BUSINESS_UNIT , B.UNIT_MEASURE_STD , F.QTY_PRECISION , F.ROUND_RULE FROM PS_BU_ITEMS_INV A , PS_MASTER_ITEM_TBL B , PS_SET_CNTRL_REC C , PS_SET_CNTRL_REC D , PS_INV_ITEM_UOM F , PS_ITM_VENDOR_LOC G , PS_ITM_VENDOR H WHERE A.CONSIGNED_FLAG = 'Y' AND A.BUSINESS_UNIT = C.SETCNTRLVALUE AND C.RECNAME = 'VENDOR' AND A.BUSINESS_UNIT = D.SETCNTRLVALUE AND D.RECNAME = 'MASTER_ITEM_TBL' AND D.SETID = B.SETID AND A.INV_ITEM_ID = B.INV_ITEM_ID AND F.SETID = B.SETID AND F.INV_ITEM_ID = B.INV_ITEM_ID AND F.UNIT_OF_MEASURE = B.UNIT_MEASURE_STD AND G.SETID = D.SETID AND G.INV_ITEM_ID = A.INV_ITEM_ID AND G.VENDOR_SETID = C.SETID AND G.VENDOR_ID = A.VENDOR_ID AND G.VNDR_LOC = A.VNDR_LOC AND H.SETID = D.SETID AND H.INV_ITEM_ID = A.INV_ITEM_ID AND H.VENDOR_SETID = C.SETID AND H.VENDOR_ID = A.VENDOR_ID AND H.ITM_STATUS = 'A' AND NOT EXISTS ( SELECT 'X' FROM PS_ITM_VND_BU_ATTR Z WHERE Z.VENDOR_SETID = C.SETID AND Z.VENDOR_ID = A.VENDOR_ID AND Z.SETID = D.SETID AND Z.INV_ITEM_ID = A.INV_ITEM_ID AND Z.VNDR_LOC = A.VNDR_LOC AND Z.BUSINESS_UNIT_IN = A.BUSINESS_UNIT)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 VENDOR_SETID Character(5) VARCHAR2(5) NOT NULL Vendor SetID
2 VENDOR_ID Character(10) VARCHAR2(10) NOT NULL Vendor Identifier
3 SETID Character(5) VARCHAR2(5) NOT NULL SetID
4 INV_ITEM_ID Character(18) VARCHAR2(18) NOT NULL Item ID
5 VNDR_LOC Character(10) VARCHAR2(10) NOT NULL Vendor Location
6 BUSINESS_UNIT_IN Character(5) VARCHAR2(5) NOT NULL Inventory Unit
7 UNIT_MEASURE_STD Character(3) VARCHAR2(3) NOT NULL Standard Unit of Measure
8 QTY_PRECISION Character(1) VARCHAR2(1) NOT NULL Quantity Precision
D=Decimal
W=Whole Nbr
9 ROUND_RULE Character(1) VARCHAR2(1) NOT NULL Rounding Rule
D=Round Down
N=Natural Round
U=Round Up