|
|
The following table definitions exist for these business objects. Figure A.1 shows a diagram of the integration points between them.
Figure A.I: Table diagram.
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) |
|
|