The following table definitions exist for these business objects. Figure A.1 shows a diagram of the integration points between them.
Each part in inventory has a master inventory row in the INVENTORY table and one row in the INVENTORY_HISTORY table for every calendar quarter the part has been carried in inventory.
INVENTORY | |
PART_NUMBER |
CHARACTER(8) |
DESCRIPTION |
CHARACTER(20) |
TYPE |
CHARACTER(1) |
UNIT_OF_MEASURE |
CHARACTER(3) |
QUANTITY_ONHAND |
INTEGER |
QUANTITY_ONORDER |
INTEGER |
ENG_DRAWING_NUM |
NUMBER(9) |
INVENTORY_PRICE |
MONEY |
LAST_PURCHASE_PRICE |
MONEY |
DATE_LAST_RECEIVED |
DATE |
LAST_PURCHASE_ORDER |
NUMBER(8) |
DO_NOT_ORDER_FLAG |
CHARACTER(1) |
INVENTORY_HISTORY | |
PART_NUMBER |
CHARACTER(8) |
YEAR |
SMALL INTEGER |
QUARTER |
SMALL INTEGER |
QUANTITY_SOLD |
INTEGER |
QUANTITY_ORDERED |
INTEGER |
QUANTITY_RECEIVED |
INTEGER |
NUM_ORDERS_PLACED |
INTEGER |
Each supplier of parts has one row in the SUPPLIER table and as many rows in the SOURCE table as parts that respective supplier is a source for. A part may be supplied by many suppliers.
SUPPLIER | |
SUPPLIER_ID |
INTEGER(6) |
COMPANY_NAME |
CHARACTER(20) |
COMPANY_ADDRESS |
CHARACTER(20) |
CITY |
CHARACTER(20) |
STATE |
CHARACTER(2) |
ZIP |
CHARACTER(10) |
PHONE |
CHARACTER(10) |
CONTACT_NAME |
CHARACTER(20) |
DATE_ESTABLISHED |
DATE |
DATE_LAST_ORDER |
DATE |
LAST_ORDER_NUMBER |
NUMBER(9) |
SOURCE | |
SUPPLIER_ID |
CHARACTER(6) |
PART_NUMBER |
CHARACTER(8) |
SUPPLIER_PART_NUM |
CHARACTER(10) |
PRIORITY |
SMALLINTEGER |
MINIMUM_QUANTITY |
INTEGER |
ORDER_MODE |
CHARACTER(1) |
ORDER_ADDRESS |
CHARACTER(20) |
ORDER_EMAIL |
CHARACTER(20) |
ORDER_EDI_ADDRESS |
CHARACTER(20) |
BILL_TO_ADDRESS |
CHARACTER(20) |
Each order for a part causes a single row to be created in the PURCHASE_ORDER table. An order can only order one part.
PURCHASE_ORDER | |
PO_NUMBER |
NUMBER(9) |
DATE_OF_ORDER |
DATE |
SUPPLIER_ID |
INTEGER(6) |
PART_NUMBER |
CHARACTER(8) |
DESCRIPTION |
CHARACTER(20) |
SUPPLIER_PART_NUM |
CHARACTER(10) |
QUANTITY |
INTEGER |
UNIT_OF_MEASURE |
CHARACTER(3) |
UNIT_PRICE |
MONEY |
DATE_SHIPPED |
DATE |
QUANTITY_SHIPPED |
INTEGER |
DATE RECEVIED |
DATE |
QUANTITY_RECEIVED |
INTEGER |
RECEIVED_BY |
PERSON_ID |
RECEIVED_BY_EXT |
CHARACTER(4) |
QUANTITY_RETURNED |
INTEGER |
DATE_RETURNED |
DATE |
STATUS |
CHARACTER(1) |
REASON |
VARIABLE CHARACTER(200) |