Repository Docs & Knowledge Base

id: 2d7a9e31-6f84-4c9d-b1a6-8e5c2f4d7a13 name: Optimize ingredient order quantities kind: InventoryOptimization cron: 0 50 6 * * * analysis_window: 90d comparison_periods: [7d, 28d]

Optimize ingredient order quantities to balance stock availability, holding costs, bulk purchasing discounts, and freshness requirements while minimizing waste and preventing shortages.

The process should analyze historical ingredient consumption and pricing tiers to determine optimal purchasing quantities and delivery strategies.

The output must contain optimized order quantities and delivery recommendations only, without explanations or descriptions of methodology.

Known Tables & Columns

GNDITEM

• item (integer) - menu item identifier — references itm.id
• bohname (varchar) - menu item name — join itm on gnditem.item = itm.id and use itm.longname
• price (decimal) - current selling price
• revid (integer) - revenue center identifier

ITM

• id (integer) - menu item identifier
• longname (varchar) - menu item name
• recipe relationships - mapping of menu items to ingredient usage quantities

QTYPRICE

• quantity tiers - thresholds for bulk pricing
• supplier pricing - price per unit depending on ordered quantity

GNDSALE

• historical sales forecasts
• waste indicators
• shortage indicators

Analysis Approach

Stage 1 — Historical Demand Analysis

Extract historical sales data from the last 90 days using GNDITEM.

Aggregate demand by:

• menu item
• day of week
• service period or time window if available.

Convert menu item demand into ingredient demand using recipe relationships from ITM.

Identify recurring demand patterns such as:

• peak demand periods
• consistently high-volume ingredients
• weekday vs weekend differences.

Stage 2 — Ingredient Consumption Forecast

Estimate future ingredient consumption based on historical demand patterns.

Adjust forecasts using contextual signals such as:

• seasonal demand variations
• upcoming promotions
• scheduled events or traffic spikes.

Stage 3 — Inventory Risk Assessment

Use signals from GNDSALE to identify:

• ingredients prone to spoilage
• ingredients frequently understocked.

Compare forecasted ingredient consumption with expected stock levels to determine:

• safety stock requirements
• potential shortage risks.

Stage 4 — Pricing and Bulk Optimization

Use QTYPRICE to evaluate quantity-based pricing tiers and supplier pricing.

Determine whether bulk purchasing discounts justify larger order quantities while considering:

• storage capacity
• holding costs
• spoilage risks.

Stage 5 — Freshness and Delivery Optimization

Adjust ordering quantities based on:

• ingredient shelf life
• freshness requirements
• delivery schedules.

When beneficial, recommend splitting orders across multiple deliveries to reduce holding costs and minimize spoilage risk.

Stage 6 — Final Ordering Optimization

Produce optimized ingredient order quantities that balance:

• stock availability
• holding costs
• bulk discounts
• freshness constraints
• waste risk.

Output Constraints

• Use historical data from the last 90 days only.
• Prefer aggregated summaries.
• Avoid requesting more than 3 database queries unless necessary.
• Return optimized order quantities and delivery recommendations only.
• Do not include explanations, reasoning, or methodology.

Output Format

Return structured ingredient ordering recommendations.