File Updates: Database Structure

The Order Processing application interfaces with and updates four major subsections of the database.  These are the sales order files, quote order files, inventory files, and accounts receivable files.

The sales order files consist of the Sales Order Header, Sales Order Line, Sales Order Line/Lot and Sales Order Comment files.  Exhibit 1 diagrams the structure of these files.

The quote order files consist of the Quote Order Header, Quote Order Line, and Quote Order Comments files.  Exhibit 2 outlines the structure of the files.

The inventory files consist of the Part Master, Warehouse Balance, Location/Lot, and Warehouse Description files.  Exhibit 3 outlines the structure of these files.

The accounts receivable files consist of the Bill-to Customer Master, Ship-to Customer Address, Open Item Header, Open Item Distribution, Invoice Lot Master, and Open Item Comment files. Exhibit 4 outlines the structure of these files.

Considerations

New sales order data is added to the sales order files by the Sales Order Maintenance conversation.

New quote order data is added to the quote order files by the Quote Order Maintenance conversation.

Existing sales order data is maintained by the Sales Order Maintenance and Open Order Shipping and Billing conversations.  The Open Order Shipping and Billing conversation will also add lines to the Sales Order Line file.

Existing quote order data is maintained by the Quote Order Maintenance conversation.

Records are purged from the sales order files by the Sales Order file Reorganization program.

Records are purged from the quote order files by the Quote Order Reorganization program.

An active Part Master file record must exist before a part can be ordered.  In addition, the part ordered cannot have a part type of planning.

An active Warehouse Balance file record must exist for the part at the sourcing company/warehouse before a part can be ordered.  The part on the Warehouse Balance file must also be saleable as defined by the part price code.  In addition, the user must be authorized to the sourcing warehouse.

Location/lot records are updated based on the shipping information entered in the billing conversations.  For lot-assignable parts, the reserved quantity is updated from Order Processing.

The Warehouse Description file provides the inventory costing option for the sourcing company/warehouse.

An active Bill-to Customer Master file record must exist before an order can be placed for a bill-to customer.  Also, transactions must be allowed for the customer.

An active Ship-to Customer Address file record must exist before an order can be placed for a ship-to customer.

The Sales Order Maintenance conversation updates on-order amounts on the Bill-to Customer Master file.

The billing conversations update accounts receivable balances on the Bill-to Customer Master file and sales statistics on the Ship-to Customer Address file, and create Open Item Header, Distribution, Invoice Lot Master, and Comment file records.

Exhibit 1
Database Sales Order Files

Exhibit 2
Database Quote Order Files

Exhibit 3
Database Inventory Files

Exhibit 4
Database Accounts Receivable Files