How to Prevent Negative Inventory with Business Rules

Rule Set: INVENTORY-PREVENT-NEGATIVE-SALES
Trigger Table: 37 (Sales Line)
Trigger Event: Before Posting Shipment
Linked Tables: Item (27)

Scenarios:
  1. [37:1] is 'Order'                    // Document type is Order
  2. [27:1] is 'Inventory'                // Item type is Inventory (not service)

**Placeholders Used in These Scenarios:**
- `[37:1]` - Sales Line (Table 37): Document Type (Field 1) - Filters to order documents only
- `[27:1]` - Item (Table 27): Type (Field 1) - Ensures inventory items (not services)

Rule 1: Insufficient Inventory Hard Block
Condition: [37:15] is >[27:18]
// Quantity to ship > Inventory on hand

Action: Error - "INSUFFICIENT INVENTORY: Cannot ship more than available stock.

Item: [27:3] - [27:Description]
Requested Quantity: [37:15]
Available Inventory: [27:18]
Shortage: {[37:15] - [27:18]} units

Cannot create negative inventory for this item. Options:

1. Reduce shipment quantity to [27:18] units (available stock)
2. Partial shipment: Ship [27:18] now, backorder remaining {[37:15] - [27:18]}
3. Transfer from other location (check location availability)
4. Wait for incoming stock (next receipt: [EstimatedReceiptDate])

Check item availability across all locations:
- Location A: [LocationA_Inventory] units
- Location B: [LocationB_Inventory] units
- Location C: [LocationC_Inventory] units

Contact Warehouse Manager at ext. 6000 for stock alternatives."

**Placeholders Used in This Rule:**
- `[37:15]` - Sales Line (Table 37): Quantity (Field 15) - Quantity being shipped
- `[27:18]` - Item (Table 27): Inventory (Field 18) - Current inventory on hand
- `[27:3]` - Item (Table 27): Description (Field 3) - Item description
- `{[37:15] - [27:18]}` - Calculated expression: Shortage amount (requested minus available)

Rule 2: Approaching Zero Inventory Warning
Scenario 1: {[27:18] - [37:15]} is >0      // Won't go negative
Condition: {[27:18] - [37:15]} is <10      // But will leave less than 10

Action: Warning - "LOW INVENTORY: Shipment will deplete stock to critical level.

Item: [27:3]
Current Inventory: [27:18]
Shipment Quantity: [37:15]
Remaining After Shipment: {[27:18] - [37:15]} units

This shipment will leave minimal stock. Consider:
- Creating purchase order to replenish stock
- Reserving remaining units for priority customers
- Reviewing other pending orders for this item

Confirm you want to proceed with this shipment."

**Placeholders Used in This Rule:**
- `[27:18]` - Item (Table 27): Inventory (Field 18) - Current inventory level
- `[37:15]` - Sales Line (Table 37): Quantity (Field 15) - Quantity to ship
- `[27:3]` - Item (Table 27): Description (Field 3) - Item description
- `{[27:18] - [37:15]}` - Calculated expression: Remaining inventory after shipment

Rule 3: Below Safety Stock Warning
Linked Tables: Item (27) with safety stock field
Scenario 1: {[27:18] - [37:15]} is <[27:CustomSafetyStock]  // Below safety stock
Scenario 2: {[27:18] - [37:15]} is >0                       // But not negative

Action: Warning - "SAFETY STOCK BREACH: Shipment will drop inventory below safety stock level.

Item: [27:3]
Safety Stock Level: [27:CustomSafetyStock] units
Current Inventory: [27:18]
After Shipment: {[27:18] - [37:15]} units
Below Safety Stock by: {[27:CustomSafetyStock] - {[27:18] - [37:15]}} units

Safety stock exists to prevent stockouts. Proceeding will increase stockout risk.

Recommended actions:

**Placeholders Used in This Rule:**

Rule Set: INVENTORY-LOCATION-VALIDATION
Trigger Table: 37 (Sales Line)
Trigger Event: Before Posting Shipment
Linked Tables: Item (27), Location (14)

Rule 1: Location Inventory Validation
Condition: [37:15] is >[27:CustomInventoryAtLocation]
// Quantity exceeds inventory at this specific location

Action: Error - "LOCATION INSUFFICIENT INVENTORY: Not enough stock at this location.

Item: [27:3] - [27:Description]
Location: [14:1] - [14:2]
Requested Quantity: [37:15]
Available at This Location: [27:CustomInventoryAtLocation]
Shortage at Location: {[37:15] - [27:CustomInventoryAtLocation]} units

Total company inventory: [27:18] units
Other locations:
- Main Warehouse: [MainWarehouse_Qty] units
- Location A: [LocationA_Qty] units
- Location B: [LocationB_Qty] units

Options:
1. Change shipment location to one with sufficient stock
2. Transfer stock to [14:1] from another location (requires [TransferTime])
3. Reduce shipment quantity to [27:CustomInventoryAtLocation]
4. Split shipment across multiple locations

Contact Warehouse Coordinator for transfer assistance."

**Placeholders Used in This Rule:**
- `[37:15]` - Sales Line (Table 37): Quantity (Field 15) - Requested shipment quantity
- `[27:CustomInventoryAtLocation]` - Item (Table 27): Custom Inventory at Location Field - Location-specific inventory
- `[27:3]` - Item (Table 27): Description (Field 3) - Item description
- `[14:1]` - Location (Table 14): Code (Field 1) - Location code
- `[14:2]` - Location (Table 14): Name (Field 2) - Location name
- `[27:18]` - Item (Table 27): Inventory (Field 18) - Total company-wide inventory
- `{[37:15] - [27:CustomInventoryAtLocation]}` - Calculated expression: Shortage at specific location

Rule 2: Negative Location with Positive Total
Scenario 1: [27:18] is >[37:15]
Scenario 2: [27:CustomInventoryAtLocation] is <[37:15]
// Total inventory is sufficient but location inventory insufficient

Action: Warning - "LOCATION MISMATCH: Sufficient total inventory but not at ship location.

Item: [27:3]
Total Company Inventory: [27:18] units (sufficient)
Inventory at [14:1]: [27:CustomInventoryAtLocation] units (insufficient)

This suggests stock is at wrong location. Consider:
- Transfer from [LocationWithMostStock] ([LocationQty] units available)
- Update ship location to [LocationWithMostStock]

Rule Set: INVENTORY-BACKORDER-MANAGEMENT
Trigger Table: 37 (Sales Line)
Trigger Event: Before Insert, Before Modify
Linked Tables: Item (27), Customer (18)

Scenarios:
  1. [37:1] is 'Order'
  2. [37:15] is >[27:18]                  // Order quantity > available

Rule 1: Backorder Allowed for Approved Items
Scenario 1: [37:15] is >[27:18]                    // Order quantity > available
Scenario 2: [27:CustomAllowBackorder] is true      // Backorder allowed
Condition: {[37:15] - [27:18]} is >[27:CustomMaxBackorder]
// Exceeds maximum backorder quantity

Action: Error - "BACKORDER LIMIT EXCEEDED

Item: [27:3] - [27:Description]
Order Quantity: [37:15]
Available Now: [27:18]
Backorder Quantity: {[37:15] - [27:18]}
Maximum Backorder Allowed: [27:CustomMaxBackorder]
Overage: {[37:15] - [27:18] - [27:CustomMaxBackorder]}

This item allows backorders, but requested backorder quantity exceeds limit.

Options:
1. Reduce order to [27:18] + [27:CustomMaxBackorder] = [Total] units max
2. Split into multiple orders as stock becomes available
3. Request backorder limit increase (requires Manager approval)

Next scheduled receipt: [NextReceiptDate] for [NextReceiptQty] units"

**Placeholders Used in This Backorder Rule:**
- `[37:1]` - Sales Line (Table 37): Document Type (Field 1) - Filters to orders
- `[37:15]` - Sales Line (Table 37): Quantity (Field 15) - Order quantity
- `[27:18]` - Item (Table 27): Inventory (Field 18) - Available inventory
- `[27:CustomAllowBackorder]` - Item (Table 27): Custom Allow Backorder Flag - Boolean for backorder permission
- `[27:CustomMaxBackorder]` - Item (Table 27): Custom Max Backorder Field - Maximum backorder quantity
- `[27:3]` - Item (Table 27): Description (Field 3) - Item description
- `{[37:15] - [27:18]}` - Calculated expression: Backorder quantity (requested minus available)
- `{[37:15] - [27:18] - [27:CustomMaxBackorder]}` - Calculated expression: Excess beyond maximum backorder

Rule 2: Backorder Not Allowed
Condition: [37:15] is >[27:18] AND
            [27:CustomAllowBackorder] is false
// Item doesn't allow backorder

Action: Error - "BACKORDER NOT ALLOWED: This item cannot be backordered.

Item: [27:3] - [27:Description]
Order Quantity: [37:15]
Available Inventory: [27:18]
Shortage: ([37:15] - [27:18])

Item policy: No backorders (must have stock to sell)

Options:
1. Reduce order quantity to [27:18] (available stock)
2. Wait for next receipt (estimated [NextReceiptDate])
3. Check substitute items: [SubstituteItem1], [SubstituteItem2]
4. Special order from vendor (lead time: [VendorLeadTime] days)

Contact Sales Manager for alternative solutions."

Rule 3: Customer-Approved Backorder
Scenario 1: [37:15] is >[27:18]                    // Order quantity > available
Scenario 2: [27:CustomAllowBackorder] is true      // Backorder allowed for item
Condition: [37:CustomBackorderApproved] is false   // But customer hasn't approved

Action: Warning - "CUSTOMER BACKORDER APPROVAL REQUIRED

Item: [27:3]
Available Now: [27:18]
On Backorder: {[37:15] - [27:18]}
Estimated Fulfillment: [EstimatedDate]

This order includes backordered items. Customer [18:2] must approve:
- Partial shipment ([27:18] units now)
- Backorder for {[37:15] - [27:18]} units
- Estimated delivery of backorder: [EstimatedDate]

Check 'Backorder Approved' flag after customer confirmation, or reduce 
quantity to [27:18]

Rule Set: INVENTORY-PRODUCTION-CONSUMPTION
Trigger Table: Production Order Component Line
Trigger Event: Before Posting Consumption
Linked Tables: Item (27)

Rule 1: Component Shortage Prevention
Condition: [ComponentQtyNeeded] is >[27:18]
// Component required > component available

Action: Error - "COMPONENT SHORTAGE: Cannot consume more components than available.

Component Item: [27:3] - [27:Description]
Required for Production: [ComponentQtyNeeded]
Available Inventory: [27:18]
Shortage: {[ComponentQtyNeeded] - [27:18]}

Production Order: [ProdOrderNo]
Output Item: [OutputItem]
Quantity to Produce: [ProdQty]

Cannot post production consumption with insufficient components.

Options:
1. Reduce production quantity to match available components
2. Purchase additional components (lead time: [VendorLeadTime])
3. Find substitute component if approved in BOM
4. Check for components in other locations

Material Planner: ext. 7000"

Rule 2: Partial Production Allowed
Scenario 1: [ComponentQtyNeeded] is >[27:18]            // More needed than available
Scenario 2: [27:18] is >0                                // Some available
Scenario 3: [27:CustomAllowPartialProduction] is true   // Partial production allowed

Action: Warning - "PARTIAL PRODUCTION: Insufficient components for full production run.

Component: [27:3]
Required: [ComponentQtyNeeded]
Available: [27:18]
Can Produce: {[27:18] / [QtyPer]} units of [OutputItem]

Full production order is for [ProdQty] units but can only produce 
{[27:18] / [QtyPer]} units with available components.

Options:
1. Proceed with partial production of {[27:18] / [QtyPer]} units
2. Wait for component receipt (due: [ComponentReceiptDate]

Rule Set: INVENTORY-ADJUSTMENT-VALIDATION
Trigger Table: Item Journal Line (83)
Trigger Event: Before Posting
Linked Tables: Item (27)

Rule 1: Negative Adjustment Exceeds Inventory
Scenario 1: [83:13] is <0           // Negative adjustment
Condition: ABS([83:13]) is >[27:18] // Larger than current inventory

Action: Error - "INVALID NEGATIVE ADJUSTMENT: Adjustment would create negative inventory.

Item: [27:3] - [27:Description]
Current Inventory: [27:18]
Adjustment Quantity: [83:13]
Resulting Inventory: {[27:18] + [83:13]}

Negative adjustments cannot exceed current inventory.

Possible issues:
- Data entry error (check if [83:13] should be -[AdjustedValue] instead)
- Wrong item selected
- Inventory already consumed elsewhere

Maximum negative adjustment allowed: -[27:18]

Verify adjustment is correct. If this is physical count adjustment showing 
shortage, document discrepancy reason in Reason Code field."

Rule 2: Large Negative Adjustment Warning
Scenario 1: [83:13] is <0                       // Negative adjustment
Condition: ABS([83:13]) is >{[27:18] * 0.5}    // > 50% of current inventory

Action: Warning - "LARGE NEGATIVE ADJUSTMENT: Adjustment reduces inventory by more than 50%.

Item: [27:3]
Current Inventory: [27:18]
Adjustment: [83:13]
Percentage Decrease: {ABS([83:13]) / [27:18] * 100}%
New Inventory: {[27:18] + [83:13]}

Large negative adjustments may indicate:
- Physical count discrepancy (theft, damage, miscounting)
- System error (transactions not recorded)
- Location transfer not posted

Require manager approval for adjustments over 50%. Document reason in 
Reason Code and Comments fields.

Confirm this adjustment is accurate before posting."

Rule 3: Reason Code Required for Adjustments
Scenario 1: [83:13] is <0
Scenario 2: {ABS([83:13])} is >10
Scenario 3: [83:CustomReasonCode] is ''
// Significant negative adjustment without documented reason

Action: Error - "REASON CODE REQUIRED: Inventory adjustments require documented justification.

Adjustment Quantity: [83:13]
Value Impact: $([83:13] * [27:22]

Rule Set: INVENTORY-TRANSFER-VALIDATION
Trigger Table: Transfer Line
Trigger Event: Before Posting Shipment
Linked Tables: Item (27), Location (14)

Rule 1: Transfer From Location Insufficient
Condition: [TransferQty] is >[27:InventoryAtFromLocation]
// Transfer quantity exceeds inventory at source location

Action: Error - "INSUFFICIENT INVENTORY AT SOURCE LOCATION

Item: [27:3] - [27:Description]
Transfer From: [FromLocation]
Transfer To: [ToLocation]
Transfer Quantity: [TransferQty]
Available at [FromLocation]: [27:InventoryAtFromLocation]
Shortage: ([TransferQty] - [27:InventoryAtFromLocation])

Cannot transfer more than available at source location.

Options:
1. Reduce transfer quantity to [27:InventoryAtFromLocation]
2. Transfer from different location with more stock
3. Split transfer across multiple source locations

Current inventory by location:
- [LocationA]: [QtyA] units
- [LocationB]: [QtyB] units
- [LocationC]: [QtyC] units

Contact Warehouse Coordinator for transfer planning."

Rule 2: Transfer Creates Critical Low Stock at Source
Condition: [27:InventoryAtFromLocation] - [TransferQty] is <[27:CustomSafetyStock] AND
            [27:InventoryAtFromLocation] - [TransferQty] is >0
// Transfer won't go negative but will drop source below safety stock

Action: Warning - "TRANSFER DEPLETES SOURCE LOCATION BELOW SAFETY STOCK

Item: [27:3]
From Location: [FromLocation]
Safety Stock Level: [27:CustomSafetyStock]
Current Inventory: [27:InventoryAtFromLocation]
After Transfer: ([27:InventoryAtFromLocation] - [TransferQty])
Below Safety Stock by: ([27:CustomSafetyStock] - ([27:InventoryAtFromLocation] - [TransferQty]))

This transfer will leave source location with insufficient safety stock, 
increasing stockout risk at [FromLocation].

Consider:
- Transferring less quantity
- Replenishing [FromLocation] from central warehouse
- Triggering purchase order for [FromLocation]

Rule Set: INVENTORY-RESERVATION-VALIDATION
Trigger Table: 37 (Sales Line)
Trigger Event: Before Insert
Linked Tables: Item (27)

Rule 1: Available to Promise Check
Condition: [37:15] is >([27:18] - [27:CustomReservedQuantity])
// Order quantity > (inventory - already reserved)

Action: Error - "INSUFFICIENT AVAILABLE INVENTORY

Item: [27:3] - [27:Description]
On-Hand Inventory: [27:18]
Already Reserved: [27:CustomReservedQuantity]
Available to Promise: ([27:18] - [27:CustomReservedQuantity])
Requested Quantity: [37:15]
Shortage: ([37:15] - ([27:18] - [27:CustomReservedQuantity]))

Although total inventory appears sufficient, units are already allocated 
to other orders.

Reserved for:
- Order #[OrderNo1]: [Qty1] units (ship date: [Date1])
- Order #[OrderNo2]: [Qty2] units (ship date: [Date2])
- Order #[OrderNo3]: [Qty3] units (ship date: [Date3])

Options:
1. Reduce order quantity to ([27:18] - [27:CustomReservedQuantity]) units
2. Push ship date out to [NextAvailableDate] (after reserved orders ship)
3. Wait for incoming stock (receipt date: [ReceiptDate])
4. Request priority allocation (requires manager approval)

Contact Order Management for reservation priority review."

Rule 2: Auto-Reserve on Order Creation
Condition: [37:15] is <=([27:18] - [27:CustomReservedQuantity]) AND
            [37:CustomReservationCreated] is false
// Sufficient available inventory but not yet reserved

Action: Auto-Reserve + Message
  "INVENTORY RESERVED: [37:15] units of [27:3] reserved for this order.
  
  Available before reservation: ([27:18] - [27:CustomReservedQuantity])
  Reserved for this order: [37:15]
  Remaining available: ([27:18] - [27:CustomReservedQuantity] - [37:15]

Poor: "Negative inventory not allowed"
Better: "Insufficient inventory"
Best: "Insufficient inventory. Available: 45, Requested: 60. Reduce 
quantity or wait for receipt on [Date]