A.2 Tables

The following table definitions exist for these business objects. Figure A.1 shows a diagram of the integration points between them.

click to expand
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)

A.3 Column Properties

The following is not an exhaustive list of column properties. The following column properties are described to demonstrate concepts.

Domain:	PART_NUMBER
Business Name :	Inventory Part Number
Business Meaning:	Internally generated value to uniquely identify each part maintained in inventory. Numbers are assigned through a single program that is part of the inventory application (NEW_PART_NUMBER). Once assigned, a part number never changes.
Data Type:	CHARACTER
Length Boundaries:	Minimum and maximum are both 8.
Value Rules:	Character pattern requires that the first character identify the business area that owns responsibility for the part number. This must be the uppercase letter A, B, C, D, or E. The remaining seven digits are numerals from 0 to 9. All zeros are permitted.

Storage Name:	INVENTORY.PART_NUMBER
Business Meaning:	Primary key for this table
Domain Name:	PART_NUMBER
Property Rules:	UNIQUE

Storage Name:	INVENTORY_HISTORY.PART_NUMBER
Business Meaning:	Part of primary key for this table
Domain Name:	PART_NUMBER
Property Rules:	NOT NULL

Storage Name:	INVENTORY.TYPE
Business Meaning:	Identifies the type of part. The types that are described are critical manufacturing, general manufacturing, and business general operations.
Domain Name:	None
Business Name:	Inventory Type
Data Type:	CHARACTER
Length Boundaries:	1
Value Rules:	Must come from list of C, G, or O
Property Rules:	NOT NULL

Storage Name:	INVENTORY.UNIT_OF_MEASURE
Business Meaning:	Identifies unit of measure of a part in inventory. This determines the measurement of a minimum unit of the part used for ordering and supplying the part.
Domain Name:	None
Business Name:	Inventory Unit of Measure
Data Type:	CHARACTER
Length Boundaries:	1 to 3 characters
Value Rules:	Must come from list of approved unit of measures. This list is maintained in a separate table called APPROVED_UNIT_OF_MEASURES.
Property Rules:	NOT NULL

Storage Name:	INVENTORY.QUANTITY_ONHAND
Business Meaning:	The amount of a part that is currently on the shelf in the warehouse. The amount available to supply a requisition .
Domain Name:	None
Business Name:	Inventory Quantity Available
Data Type:	INTEGER
Length Boundaries:	Integer restrictions
Value Rules:	Must be in the range 0 to 100000
Property Rules:	NOT NULL

Storage Name:	INVENTORY.DO_NOT_ORDER_FLAG
Business Meaning:	This part number should not be reordered. It has been either discontinued or replaced by another part. Any inventory remaining can be requisitioned until it goes to zero.
Domain Name:	None
Business Name:	Inventory Do Not Order Flag
Data Type:	CHARACTER
Length Boundaries:	1 byte
Discrete Values:	D for do not order; blank for normal
Property Rules:	NOT NULL

Storage Name:	PURCHASE_ORDER.STATUS
Business Meaning:	Defines the current state of the order. It can be either in a state of placed, shipped, received, cancelled, or returned. Cancelled can only be used if not shipped. Returned means the product received was returned. Returns must be for all quantities received.
Domain Name:	None
Business Name:	Purchase Order Status
Data Type:	CHARACTER
Length Boundaries:	1 byte
Discrete Values:	P for placed, S for shipped, R for received, C for cancelled, and X for returned
Property Rules:	NOT NULL and not blank

Storage Name:	PURCHASE_ORDER.REASON
Business Meaning:	A text field that gives a short reason for cancelling an order or returning the parts received
Domain Name:	None
Business Name:	Purchase Order Cancel or Return Explanation
Data Type:	VARIABLE CHARACTER
Length Boundaries:	More than 5 bytes if present
Values:	Any text
Property Rules:	NULLS OK

Flylib.com

Category list

Similar pages

A.2 Tables

A.2 Tables

A.3 Column Properties

Similar products

Similar products