# Key Databases for Purchase Orders Processing System

## Overview
The purchase orders processing system manages supplier purchase orders, stock procurement, supplier relationships, and related processes. The system uses PHP 5.6 code with MySQL databases and integrates with stock management, supplier reviews, document management, and financial tracking.

## Source Pages Analyzed
The following PHP pages were analyzed to identify the key databases and their interactions:

- `vmserver10/intranet/databases/purchaseorders/purchaseordersaddorder.php` - Main purchase order creation and management
- `vmserver10/intranet/databases/purchaseorders/listpoorderdetails.php` - Purchase order line items display
- `vmserver10/intranet/databases/purchaseorders/addtopoorder.php` - Add items to purchase orders
- `vmserver10/intranet/databases/purchaseorders/findpostock.php` - Search supplier stock for ordering
- `vmserver10/intranet/databases/purchaseorders/changeqtypurchaseordersdetails.php` - Update quantities
- `vmserver10/intranet/databases/purchaseorders/update_currency.php` - Update order currency
- `vmserver10/intranet/databases/purchaseorders/updatepaperworknotes.php` - Update order notes
- `vmserver10/intranet/databases/purchaseorders/updateinternalnotes.php` - Update internal notes
- `vmserver10/intranet/databases/purchaseorders/openorder.php` - Reopen committed orders for editing
- `vmserver10/intranet/databases/purchaseorders/cancelpo.php` - Cancel purchase orders

## Core Purchase Order Management Databases

### 1. `viamedquote_purchase_orders`
**Purpose**: Main purchase orders table storing all supplier purchase orders.
**Key Fields**:
- `id`: Purchase order ID
- `hostcompanyid`: Host company ID (buyer)
- `company_id`: Supplier company ID
- `contact_id`: Supplier contact ID
- `status`: Order status (0=Draft, etc.)
- `senton`: Commitment timestamp (0=uncommitted)
- `commitedon/commitedby`: Commitment info
- `completedon/completedby`: Completion info
- `cancelledon/cancelledby`: Cancellation info
- `currency_id`: Currency for this order
- `memonotes`: Paperwork notes
- `internalmemonotes`: Internal notes
- `hidememonotes`: Hide memo flag
- `openedby/openedon`: Reopening info
- `revisionid`: Revision number
- `addedby/addedon`: Creation info

**Interactions**:
- Links to [`viamedquote_purchase_orders_details`](AI_DATA/AI_Database_Purchase_orders_Structure.md:25) via `id`
- Links to [`viamedquote_purchase_orders_editreason`](AI_DATA/AI_Database_Purchase_orders_Structure.md:47) via `id`
- References [`viamedquoteaddress`](AI_DATA/AI_Database_Purchase_orders_Structure.md:218) via `company_id`
- References [`viamedquotecontact`](AI_DATA/AI_Database_Purchase_orders_Structure.md:280) via `contact_id`
- References [`global_companys`](AI_DATA/AI_Database_Purchase_orders_Structure.md:342) via `hostcompanyid`
- References [`global_companys_currencys`](AI_DATA/AI_Database_Purchase_orders_Structure.md:207) via `currency_id`

### 2. `viamedquote_purchase_orders_details`
**Purpose**: Detailed line items for each purchase order.
**Key Fields**:
- `orderid`: Links to [`viamedquote_purchase_orders.id`](AI_DATA/AI_Database_Purchase_orders_Structure.md:9)
- `stockref`: Stock reference
- `qty`: Ordered quantity
- `qty_received`: Quantity received
- `price`: Order price per unit
- `supplierinvoiceprice`: Supplier's actual invoice price
- `supplier_ref`: Supplier's part number
- `supplier_description`: Supplier's description
- `requireddate`: Required delivery date
- `memo`: Line item notes
- `supplierpriceby/supplierpriceon`: Supplier price update info
- `remainingcancelled/remainingcancelledby`: Cancellation info
- `addedby/addedon`: Creation info

**Interactions**:
- Child of [`viamedquote_purchase_orders`](AI_DATA/AI_Database_Purchase_orders_Structure.md:9)
- Links to [`stocklist_tarriffs`](AI_DATA/AI_Database_Purchase_orders_Structure.md:59) via `stockref`
- Links to [`viamed_customer_stock_references`](AI_DATA/AI_Database_Purchase_orders_Structure.md:177) via `stockref` and supplier
- Links to [`Serialnumbers`](AI_DATA/AI_Database_Purchase_orders_Structure.md:225) via `po_lineid` when goods received

### 3. `viamedquote_purchase_orders_editreason`
**Purpose**: Tracks reasons for reopening committed purchase orders.
**Key Fields**:
- `orderid`: Links to [`viamedquote_purchase_orders.id`](AI_DATA/AI_Database_Purchase_orders_Structure.md:9)
- `revisionid`: Revision number
- `reason`: Reason for reopening
- `addedby/addedon`: Creation info

**Interactions**:
- Child of [`viamedquote_purchase_orders`](AI_DATA/AI_Database_Purchase_orders_Structure.md:9)
- Used for audit trail of order changes

## Supplier and Stock Management Databases

### 4. `viamed_customer_stock_references`
**Purpose**: Supplier-specific stock references, prices, and descriptions.
**Key Fields**:
- `companyid`: Supplier company ID
- `hostcompanyid`: Host company ID
- `stock_ref`: Internal stock reference
- `customer_ref`: Supplier's part number
- `customerdescription`: Supplier's description
- `rawprice`: Base price from supplier
- `rawpricediscount`: Discount percentage
- `currenyid`: Currency ID
- `status`: Status (2=Active)
- `sordmemo`: Special ordering memo
- `addedby/addedon`: Creation info
- `updatedby/updatedon`: Update info

**Interactions**:
- Links to [`viamedquoteaddress`](AI_DATA/AI_Database_Purchase_orders_Structure.md:218) via `companyid`
- Links to [`stocklist_tarriffs`](AI_DATA/AI_Database_Purchase_orders_Structure.md:59) via `stock_ref`
- Links to [`global_companys_currencys`](AI_DATA/AI_Database_Purchase_orders_Structure.md:207) via `currenyid`
- Used by [`viamedquote_purchase_orders_details`](AI_DATA/AI_Database_Purchase_orders_Structure.md:25) for supplier pricing

### 5. `stocklist_tarriffs`
**Purpose**: Master stock list with product information and attributes.
**Key Fields**:
- `stock_ref`: Stock reference (primary identifier)
- `hostcompany_id`: Host company
- `label_description`: Product description
- `paperworkdescription`: Description for paperwork
- `SUPERSEDED`: Superseded by stock reference
- `stock_value_tag`: Stock value classification
- `suppliernotes`: Notes for suppliers
- `suppliernotespo`: Purchase order specific notes
- `hidefrompohm`: Hide from purchase order system
- `po_stock_cost`: Purchase order cost tracking

**Interactions**:
- Referenced by [`viamedquote_purchase_orders_details`](AI_DATA/AI_Database_Purchase_orders_Structure.md:25) via `stockref`
- Referenced by [`viamed_customer_stock_references`](AI_DATA/AI_Database_Purchase_orders_Structure.md:177) via `stock_ref`
- Referenced by [`Serialnumbers`](AI_DATA/AI_Database_Purchase_orders_Structure.md:225) via `Stock_ref`
- Contains product master data for purchasing

### 6. `Serialnumbers`
**Purpose**: Individual barcode/serial number tracking for received stock.
**Key Fields**:
- `Stock_ref`: Stock reference
- `Porref`: Purchase order reference (e.g., "P123")
- `Datebookedin`: Date received
- `Bookedinby`: User who received
- `cost_price/total_cost_price`: Cost tracking
- `gbp_buyprice/gbp_estshipcost/gbp_finalcost`: GBP cost breakdown
- `po_lineid`: Links to purchase order line
- `companyid`: Host company ID

**Interactions**:
- Links to [`stocklist_tarriffs`](AI_DATA/AI_Database_Purchase_orders_Structure.md:59) via `Stock_ref`
- Links to [`viamedquote_purchase_orders_details`](AI_DATA/AI_Database_Purchase_orders_Structure.md:25) via `po_lineid`
- Updated when goods are received against purchase orders

## Supporting Databases

### 7. `global_companys_currencys`
**Purpose**: Currency definitions per host company.
**Key Fields**:
- `id`: Currency ID
- `hostcompanyid`: Host company ID
- `currency`: Currency code (GBP, EUR, USD)
- `description`: Currency description
- `symbol`: Currency symbol
- `active`: Active flag

**Interactions**:
- Referenced by [`viamedquote_purchase_orders`](AI_DATA/AI_Database_Purchase_orders_Structure.md:9) via `currency_id`
- Referenced by [`viamed_customer_stock_references`](AI_DATA/AI_Database_Purchase_orders_Structure.md:177) via `currenyid`
- Referenced by [`viamedquoteaddress`](AI_DATA/AI_Database_Purchase_orders_Structure.md:218) via `ourpocurrency`

### 8. `supplier_items`
**Purpose**: Tracks completion status of purchase order items.
**Key Fields**:
- `POR`: Purchase order reference
- `Stock_ref`: Stock reference
- `Qty`: Quantity
- `Order_complete`: Completion flag (Y/N)
- `addedby/addedon`: Creation info

**Interactions**:
- Links to purchase orders via `POR` field
- Updated when orders are cancelled or completed
- Used for tracking outstanding supplier deliveries

### 9. `viamedquoteorderwatch`
**Purpose**: User watchlists for purchase order notifications.
**Key Fields**:
- `company_id`: Supplier company ID
- `userid`: User ID to notify
- `status`: Watch status (On/Issued)
- `ordertype`: Type (1=Purchase Orders)
- `repeatwatch`: Repeat flag

**Interactions**:
- Links to [`viamedquoteaddress`](AI_DATA/AI_Database_Purchase_orders_Structure.md:218) via `company_id`
- Creates [`viamedagendahistory`](AI_DATA/AI_Database_Purchase_orders_Structure.md:324) entries when triggered
- Used for purchase order notifications

### 10. `ISO_supplier_reviews`
**Purpose**: Supplier review and approval tracking.
**Key Fields**:
- `cid`: Supplier company ID
- `completedon/completedby`: Review completion info
- `reviewscore`: Review score
- `notes`: Review notes
- `addedby/addedon`: Creation info

**Interactions**:
- Links to [`viamedquoteaddress`](AI_DATA/AI_Database_Purchase_orders_Structure.md:218) via `cid`
- Blocks purchase order commitment if review overdue
- Required for supplier approval process

## System Integration Databases

### 11. `global_companys`
**Purpose**: Host company information and settings.
**Key Fields**:
- `id`: Company ID
- `name/fullname`: Company names
- `prefix`: Purchase order prefix
- `address/contact`: Company details
- `vatrate`: VAT rate
- `bankaccount`: Bank details

**Interactions**:
- Referenced by [`viamedquote_purchase_orders`](AI_DATA/AI_Database_Purchase_orders_Structure.md:9) via `hostcompanyid`
- Provides company-specific settings
- Used for purchase order numbering (prefix)

### 12. `pw_companys`
**Purpose**: User permissions per company.
**Key Fields**:
- `vui`: User ID
- `company_id`: Company ID
- `purchaseorder`: Purchase order permission flag
- `adminmode`: Admin mode flag

**Interactions**:
- Controls user access to purchase order functions
- Referenced during permission checks

### 13. `viamed_memos_history`
**Purpose**: Customer/supplier memos and notes.
**Key Fields**:
- `companyid`: Company ID
- `memotype`: Memo type (6=General purchasing, 7=Fixed notes)
- `memo`: Memo content
- `addedon/addedby`: Creation info

**Interactions**:
- Links to [`viamedquoteaddress`](AI_DATA/AI_Database_Purchase_orders_Structure.md:218) via `companyid`
- Provides default notes for purchase orders
- Used for supplier-specific purchasing instructions

### 14. `viamedagendahistory`
**Purpose**: Issue tracking and notifications.
**Key Fields**:
- `subject`: Issue subject
- `notes`: Issue notes
- `porref`: Purchase order reference
- `createdate/createpresent`: Creation info
- `informationonly`: Information flag

**Interactions**:
- Created when purchase order watch notifications trigger
- Links to purchase orders via `porref`
- Used for purchase order issue tracking

### 15. `ISO_quality_manual_files`
**Purpose**: Document storage and management.
**Key Fields**:
- `docid`: Document ID
- `description`: Document description
- `porref`: Purchase order reference
- `purchaseorderid`: Purchase order ID
- `hostcompany`: Host company ID
- `archived`: Archive flag

**Interactions**:
- Links to purchase orders via `porref` or `purchaseorderid`
- Stores supplier documents (quotes, confirmations, invoices)
- Used for purchase order document management

## Database Relationships Summary

```
viamedquote_purchase_orders (main purchase orders)
├── viamedquote_purchase_orders_details (line items)
├── viamedquote_purchase_orders_editreason (edit history)
├── viamedquoteaddress (supplier addresses)
├── viamedquotecontact (supplier contacts)
├── global_companys_currencys (currencies)
├── viamed_customer_stock_references (supplier stock catalog)
│   └── stocklist_tarriffs (stock master)
├── Serialnumbers (received stock tracking)
├── supplier_items (completion tracking)
├── viamedquoteorderwatch (user notifications)
│   └── viamedagendahistory (notification issues)
├── ISO_supplier_reviews (supplier approvals)
├── viamed_memos_history (supplier notes)
├── ISO_quality_manual_files (documents)
├── global_companys (host companies)
└── pw_companys (user permissions)
```

## Key System Flows

1. **Purchase Order Creation**: [`viamedquote_purchase_orders`](AI_DATA/AI_Database_Purchase_orders_Structure.md:9) → [`viamedquote_purchase_orders_details`](AI_DATA/AI_Database_Purchase_orders_Structure.md:25)
2. **Stock Search**: [`viamed_customer_stock_references`](AI_DATA/AI_Database_Purchase_orders_Structure.md:177) → [`stocklist_tarriffs`](AI_DATA/AI_Database_Purchase_orders_Structure.md:59) for supplier catalog
3. **Order Commitment**: Status change from draft to committed via `senton` field
4. **Goods Receipt**: [`Serialnumbers`](AI_DATA/AI_Database_Purchase_orders_Structure.md:225) updated with `Porref` and `po_lineid`
5. **Supplier Review**: [`ISO_supplier_reviews`](AI_DATA/AI_Database_Purchase_orders_Structure.md:201) blocks commitment if overdue
6. **Document Management**: [`ISO_quality_manual_files`](AI_DATA/AI_Database_Purchase_orders_Structure.md:324) linked via `porref` or `purchaseorderid`
7. **Order Revision**: [`viamedquote_purchase_orders_editreason`](AI_DATA/AI_Database_Purchase_orders_Structure.md:47) tracks reopening reasons
8. **Notification System**: [`viamedquoteorderwatch`](AI_DATA/AI_Database_Purchase_orders_Structure.md:193) → [`viamedagendahistory`](AI_DATA/AI_Database_Purchase_orders_Structure.md:324)

## Purchase Order Lifecycle

### Draft Stage (senton = 0)
- Order created in [`viamedquote_purchase_orders`](AI_DATA/AI_Database_Purchase_orders_Structure.md:9)
- Line items added to [`viamedquote_purchase_orders_details`](AI_DATA/AI_Database_Purchase_orders_Structure.md:25)
- Stock searched via [`viamed_customer_stock_references`](AI_DATA/AI_Database_Purchase_orders_Structure.md:177)
- Currency must be set via [`global_companys_currencys`](AI_DATA/AI_Database_Purchase_orders_Structure.md:207)
- Supplier review checked via [`ISO_supplier_reviews`](AI_DATA/AI_Database_Purchase_orders_Structure.md:201)

### Commitment Stage (senton > 0)
- Order committed with `senton` timestamp
- PDF generated with purchase order reference (e.g., "P123")
- [`viamedquoteorderwatch`](AI_DATA/AI_Database_Purchase_orders_Structure.md:193) notifications triggered
- Order becomes read-only except for supplier price updates

### Goods Receipt Stage
- [`Serialnumbers`](AI_DATA/AI_Database_Purchase_orders_Structure.md:225) updated with `Porref`
- `qty_received` updated in [`viamedquote_purchase_orders_details`](AI_DATA/AI_Database_Purchase_orders_Structure.md:25)
- Supplier invoice prices recorded via `supplierinvoiceprice`

### Completion/Cancellation
- [`supplier_items`](AI_DATA/AI_Database_Purchase_orders_Structure.md:185) marked complete
- Cancellation tracked via `cancelledon/cancelledby`
- [`viamedquote_purchase_orders_details`](AI_DATA/AI_Database_Purchase_orders_Structure.md:25) marked cancelled

## Currency and Pricing Integration

The system supports multi-currency purchasing:
- Default currency from [`viamedquoteaddress.ourpocurrency`](AI_DATA/AI_Database_Purchase_orders_Structure.md:218)
- Order currency in [`viamedquote_purchase_orders.currency_id`](AI_DATA/AI_Database_Purchase_orders_Structure.md:9)
- Supplier prices in [`viamed_customer_stock_references`](AI_DATA/AI_Database_Purchase_orders_Structure.md:177) with currency
- Actual invoice prices tracked separately in [`viamedquote_purchase_orders_details`](AI_DATA/AI_Database_Purchase_orders_Structure.md:25)

## Document and Quality Integration

Purchase orders integrate with document management:
- Supplier documents linked via [`ISO_quality_manual_files`](AI_DATA/AI_Database_Purchase_orders_Structure.md:324)
- Supplier reviews required via [`ISO_supplier_reviews`](AI_DATA/AI_Database_Purchase_orders_Structure.md:201)
- Quality documentation tracked per stock item
- Compliance requirements enforced before commitment

This structure supports comprehensive supplier relationship management with integrated purchasing, quality control, and document management processes.