Skip to content

Optimized tables

PULSE Dashboard does not read your Macola® tables directly every time a report refreshes. It first gathers what the reports on screen need into a small number of purpose-built tables — the optimized tables — and every report then reads from those. It is why a tab holding a dozen reports opens in seconds instead of running a dozen separate queries against your ERP.

Most people never need to know they exist. They matter if you write your own SQL in the Query Analyzer, or if you are deciding which report to build a number from, because they tell you what PULSE Dashboard already has to hand.

This page is generated from the same catalogue the software ships with, so it describes exactly what your copy of PULSE Dashboard builds.

You can browse these inside PULSE Dashboard

The Optimized Table Explorer in the Advanced Query Analyzer (Query → Optimized Table Explorer, or Ctrl+Shift+O) lists the same tables with every column each one can carry, the Macola® field behind each column, and whether it is built right now. See Optimized Table Explorer.

The tables at a glance

Table Area What it holds Macola® versions
Customer Orders - Recent Months
#OPT_CustomerOrdersMonth		 Sales A small, fast copy of the sales dataset covering only the months month-level dashboards need Macola® ES, Macola 10 and Progression
Customer Orders - Rolling Two Years
#OPT_CustomerOrdersYear		 Sales The main sales dataset in Pulse Macola® ES, Macola 10 and Progression
Customer Orders by Salesperson - Recent Months
#OPT_CustomerOrdersMonthSplit		 Sales A speed-oriented cut of CustomerOrdersYearSplit down to the current and prior fiscal month Macola® ES, Macola 10 and Progression
Customer Orders by Salesperson - Rolling Year
#OPT_CustomerOrdersYearSplit		 Sales Two rolling years of sales with one row per line PER SALESPERSON named on the order, every amount already multiplied by that salesperson's commission percentage Macola® ES, Macola 10 and Progression
Customer On-Time Shipping
#OPT_CustomerOnTimeShipping		 Customer Service A trailing-twelve-month extract of shipped and invoiced order lines, plus one calculated column per date pair a dashboard box has asked to compare Macola® ES, Macola 10 and Progression
Customer Open Orders / Backlog
#OPT_CustomerOpenOrders		 Customer Service The open sales-order book Macola® ES, Macola 10 and Progression
Customer Open Orders, Split by Salesperson
#OPT_CustomerOpenOrdersSplit		 Customer Service The open-order book exploded so an order shared between up to three salespeople becomes up to three rows, each carrying that salesperson's commission share of… Macola® ES, Macola 10 and Progression
A/P Aging
#OPT_APAging		 Financial Every open accounts-payable voucher: what you owe suppliers, when it is due, on what terms Macola® ES, Macola 10 and Progression
A/R Aging
#OPT_ARAging		 Financial Every open accounts-receivable document as of the Pulse user date: invoices, debit and credit memos, finance charges and unapplied cash Macola® ES, Macola 10 and Progression
Customer Average Days To Pay
#OPT_CustomerAverageDaysToPay		 Financial One row per customer giving how many days they actually take to pay: a configurable rolling-window average and a longer default average over at least 12 months Macola® ES, Macola 10 and Progression
Inventory Status
#OPT_InventoryStatus		 Inventory The master picture of what you own, where it sits, what it cost and how fast it moves Macola® ES, Macola 10 and Progression
Inventory by Bin
#OPT_InventoryBin		 Inventory InventoryStatus fanned out to bin level Macola® ES, Macola 10 and Progression
Inventory by Lot
#OPT_InventoryLot		 Inventory InventoryStatus fanned out to lot or serial level Macola® ES, Macola 10 and Progression
Purchase Open Orders
#OPT_PurchaseOpenOrders		 Purchasing Every purchase-order line still open with a supplier, with quantities, expected cost, request and promise dates, plus Pulse-calculated received-not-invoiced and… Macola® ES, Macola 10 and Progression
Purchase Orders History
#OPT_PurchaseOrdersHistory		 Purchasing Every purchase-order line placed in roughly the last two years, open or closed - the full purchasing record for spend analysis, price history and… Macola® ES, Macola 10 and Progression
Purchase Receipts
#OPT_PurchaseReceipts		 Purchasing A line-by-line log of what physically arrived from suppliers over roughly the last two to three years, plus landed-cost charges Macola® ES, Macola 10 and Progression
Production Orders
#OPT_ProductionProcessingOrders		 Production Every production (PP) order that is not closed or deleted - the manufacturing orders the plant is working through, with the sales order line that created the… Macola® ES, Macola 10 and Progression
Shop Floor Orders
#OPT_ShopFloorOrders		 Production Every shop floor (work) order raised in the last rolling year or so, with its place in the multi-level work-order tree and optionally a full… Macola® ES, Macola 10 and Progression
MRP Details - Supply & Demand Timeline
#OPT_MRP_Details		 MRP The forward-looking supply-and-demand ledger Macola® ES, Macola 10 and Progression
Flattened multi-level bill of materials
#FlatBOM		 MRP (supporting) Every manufactured item exploded down through its bill of materials, one row per parent-to-component link at every level Macola® ES, Macola 10 and Progression
Deepest BOM level each item reaches (low-level code)
#ComponentLevel		 MRP (supporting) The low-level code for every item: the deepest point at which it can appear in any bill of materials Macola® ES, Macola 10 and Progression
The planning universe for the run
#BaseItemLoc		 MRP (supporting) Which items this forecast run has to plan at all Macola® ES, Macola 10 and Progression
The weekly forecast and MRP projection, over a fully exploded bill of materials
#FlatForecast		 MRP (supporting) The result of the forecast MRP run, projected across the whole exploded bill of materials rather than the finished goods alone Macola® ES, Macola 10 and Progression

A row marked supporting is built while another table refreshes rather than on its own. You can query it like any other, and its entry says what has to be switched on for it to be there at all.

Sales

Customer Orders - Recent Months

#OPT_CustomerOrdersMonth — Macola® ES, Macola 10 and Progression.

A small, fast copy of the sales dataset covering only the months month-level dashboards need. Identical columns and meaning to CustomerOrdersYear.

What one row represents. Exactly CustomerOrdersYear's grain, with fewer rows.

How much of it is in there. ord_dt OR inv_dt in either (a) 40 days before the same fiscal month one year ago through the end of that month, or (b) the previous fiscal month start through the Pulse user date.

Key fields. oe_ord_no, oelin_line_no, inv_no, pulse_openrecord_fg

Reports that use it. Customer Orders Chart, Customer Orders Summary, Customer Orders Graph, Fill Rate Graph, Sales Analysis.

Customer Orders - Rolling Two Years

#OPT_CustomerOrdersYear — Macola® ES, Macola 10 and Progression.

The main sales dataset in Pulse. Every open order line and every invoice line over roughly two years, with customer, salesperson, address, item and inventory detail joined on and amounts sign-corrected and UOM-converted.

What one row represents. One row per sales document line: open (pulse_openrecord_fg='Y') or invoiced ('N').

How much of it is in there. ~23 months ending at the Pulse user date; a row qualifies on invoice date OR order date.

The fields fall into these groups.

  • Record type — Whether the row is backlog or revenue. For example pulse_openrecord_fg, orig_ord_type, oehdr_ord_type.
  • Money — Invoiced, booked, cost, margin, credit memo and lost-business amounts. For example pulse_inv_amt, pulse_ord_amt, ord_amt, pulse_cost_amt, pulse_margin_amt.
  • Quantity — Invoiced, booked, ordered, to-ship and returned quantities. For example pulse_inv_qty, pulse_ord_qty, qty_ordered, qty_to_ship, qty_return_to_stk.
  • Date buckets — Invoice, order, requested-ship, request and promise dates each expanded into week/fiscal week/period/fiscal period/quarter/year. For example inv_period, ord_fiscal_period, req_ship_week, promise_period.
  • Customer and salesperson — Customer, parent customer, territory, default salesperson, balance. For example cus_name, cust_terr, par_cus_name, cus_balance.

Key fields. oe_ord_no, oelin_line_no, inv_no, pulse_openrecord_fg, cus_no, item_no, item_loc

Reports that use it. Customer Orders Chart, Customer Orders Summary, Customer Orders Graph, Top Invoiced, Top Credit Memos, Customer Orders Dynamic View, Fill Rate Graph, Item Details View — MRP pane, Sales Analysis, Budget / Forecast Entry.

Customer Orders by Salesperson - Recent Months

#OPT_CustomerOrdersMonthSplit — Macola® ES, Macola 10 and Progression.

A speed-oriented cut of CustomerOrdersYearSplit down to the current and prior fiscal month. Identical columns and pro-rating; only the date range is narrower.

What one row represents. As CustomerOrdersYearSplit, restricted to the recent-month window.

How much of it is in there. Copied from the year split table on ord_dt or inv_dt in the prior-fiscal-month-minus-40-days window or the previous-fiscal-month-to-user-date window.

Reports that use it. Customer Orders Chart, Customer Orders Summary, Customer Orders Graph, Sales Analysis.

Customer Orders by Salesperson - Rolling Year

#OPT_CustomerOrdersYearSplit — Macola® ES, Macola 10 and Progression.

Two rolling years of sales with one row per line PER SALESPERSON named on the order, every amount already multiplied by that salesperson's commission percentage. For company totals use CustomerOrdersYear instead.

What one row represents. One row per (open order line OR invoiced line) x (populated salesperson slot 1/2/3).

How much of it is in there. Same window as CustomerOrdersYear, plus the user's Sales Person Selection criteria.

The fields fall into these groups.

  • Salesperson split identity — Which salesperson slot this row represents and at what commission share. For example slspsn_seq_no, slspsn_no, oehdr_slspsn_pct_comm, slspsn_comm_amt.
  • Commission-weighted measures — Every money and quantity column, already pro-rated. For example pulse_inv_amt, pulse_ord_amt, pulse_cost_amt, pulse_margin_amt.

Key fields. oe_ord_no, oelin_line_no, inv_no, pulse_openrecord_fg, slspsn_seq_no, slspsn_no

Reports that use it. Customer Orders Chart, Customer Orders Summary, Customer Orders Graph, Sales Analysis.

Customer Service

Customer On-Time Shipping

#OPT_CustomerOnTimeShipping — Macola® ES, Macola 10 and Progression.

A trailing-twelve-month extract of shipped and invoiced order lines, plus one calculated column per date pair a dashboard box has asked to compare. Each holds the number of WORKING days between two dates on the line, so a report can classify each line as early, on time or late.

What one row represents. One row per order line invoiced within the current rolling year with a positive shipped quantity. Credit-memo lines qualify on negative return-to-stock quantity instead.

How much of it is in there. inv_dt within GetRelativeRollingYear(0,0) - exactly one rolling year, regardless of how much history CustomerOrdersYear itself holds.

Differences between Macola® versions. Works on all three because it never touches an ERP table directly - its only source is #OPT_CustomerOrdersYear, and each ERP has its own generator for that.

The fields fall into these groups.

  • Inherited from Customer Orders Year — The source is SELECT OPT.*, so every column of the year table is present. For example cus_no, slspsn_no, item_no, prod_cat, inv_dt.
  • On-time measurement — One column per configured date pair, in WORKING days. For example [datediff_6_vs_1].

Key fields. cus_no, oe_ord_no, inv_no, item_no, inv_dt, ord_dt, slspsn_no

Reports that use it. On Time Shipping, Shipping Graph.

Customer Open Orders / Backlog

#OPT_CustomerOpenOrders — Macola® ES, Macola 10 and Progression.

The open sales-order book. Every line not yet fully shipped and closed, classified into Quote / Hold / SNP / Backorder / Short / Available, with quantity and value split into matching buckets so a dashboard can show how much backlog is shippable today versus stuck.

What one row represents. One row per open order line, plus a second 'split line' row (oelin_subline_no = 2) for standard lines that are partially shippable.

How much of it is in there. No date range at all - a live snapshot. ord_type in ('O','I','C','Q'), selection_cd <> 'I', status not 'L', plus a second union half for partially shippable lines. Both halves append the salesperson-selection criteria.

The fields fall into these groups.

  • Status buckets by quantity — The line's quantity apportioned across the six shippability states. For example pulse_qty_quote, pulse_qty_hold, pulse_qty_snp, pulse_qty_bo, pulse_qty_short.
  • Status buckets by value — The same six states in dollars. For example pulse_amt_quote, pulse_amt_bo, pulse_amt_avail.
  • Terms and shipping — Requested specifically by the Cash Flow Forecast module. For example ar_terms_due_days, ar_terms_dsc_days, ar_terms_dsc_pct.
  • Inventory context — Joined from InventoryStatus. For example qty_on_hand_il, qty_allocated_il, stocked_fg, controlled_fg.

Key fields. oe_ord_no, oelin_line_no, oelin_subline_no, oelin_ord_type, cus_no, item_no, item_loc, slspsn_no, pulse_line_status

Reports that use it. Open Customer Orders, Open Customer Orders 2, Customer Orders Summary, Cash Flow Forecast, Cash Flow Forecast — OE column, Cash Flow Forecast (config), Item Details View, Item Details View — MRP pane, Sales Analysis, Test Pulse Report.

Customer Open Orders, Split by Salesperson

#OPT_CustomerOpenOrdersSplit — Macola® ES, Macola 10 and Progression.

The open-order book exploded so an order shared between up to three salespeople becomes up to three rows, each carrying that salesperson's commission share of every quantity and dollar.

What one row represents. One row per (order line, split-line indicator, salesperson slot).

How much of it is in there. As CustomerOpenOrders, except the salesperson criteria is applied once at the very end on the final rebuild rather than inside the union halves.

Key fields. oe_ord_no, oelin_line_no, slspsn_seq_no, slspsn_no, cus_no, item_no, item_loc

Reports that use it. Open Customer Orders, Open Customer Orders 2, Customer Orders Summary.

Financial

A/P Aging

#OPT_APAging — Macola® ES, Macola 10 and Progression.

Every open accounts-payable voucher: what you owe suppliers, when it is due, on what terms. Deliberately holds only currently-open items, not paid history.

What one row represents. Progression: one open APOPNFIL_SQL voucher line. ES: one open BankTransactions creditor entry.

How much of it is in there. Progression: open voucher lines whose voucher opened on or before the server's own date in status V/C/P. ES: no date cutoff at all.

The fields fall into these groups.

  • Open balance — Remaining amount owed. For example pulse_total_amt, pulse_inv_amt, ap_balance_amt, disc_amt.
  • Aging dates — Invoice, due, transaction and voucher dates with fiscal companions. For example inv_dt, due_dt, trx_dt, vchr_dt.
  • Vendor and terms — Vendor master columns and payment terms. For example vend_no, ap_terms_cd, PaymentCondition.

Key fields. vend_no, vchr_no, apply_to_no, apopn_inv_no

Reports that use it. AP Aging, Cash Flow Forecast, Cash Flow Forecast — AP column, Cash Flow Forecast (config).

A/R Aging

#OPT_ARAging — Macola® ES, Macola 10 and Progression.

Every open accounts-receivable document as of the Pulse user date: invoices, debit and credit memos, finance charges and unapplied cash. Who owes what, how old it is, who owns the relationship, and any collections notes a Pulse user attached.

What one row represents. Progression: one open AROPNFIL_SQL record per row. ES: one open BankTransactions debtor entry.

How much of it is in there. Progression: doc_dt <= UserDate, keeping only apply-to groups whose net balance is non-zero. ES: cut off at the A/R aging reference date plus salesperson criteria.

Differences between Macola® versions. Three completely different source sets. Only Progression and ES join CustomerAverageDaysToPay and ArAgingPriorities.

The fields fall into these groups.

  • Open balance — What is still owed on the document. For example pulse_inv_amt, pulse_total_amt, ar_balance_amt, pulse_current_currency_rate, aropn_curr_cd.
  • Aging dates — Document and due dates with fiscal-period companions. For example doc_dt, doc_period, doc_due_dt, doc_due_period.
  • Collections workflow — Pulse-owned, not ERP: notes, follow-up dates and priority names attached by users. For example AgingCustomerNotes, AgingInvoiceNotes, AgingCustomerPrioritiesName, AgingInvoicerPrioritiesName.
  • Payment behaviour — Joined from CustomerAverageDaysToPay (Progression/ES only). For example CADTP_AvgDaysToPay_User, CADTP_AvgDaysToPay_Default.

Key fields. cus_no, doc_no, inv_no, ar_apply_to_no, slspsn_no

Reports that use it. AR Aging, AR Aging (config), AR Recap, Cash Flow Forecast, Cash Flow Forecast — AR column, Cash Flow Forecast (config).

Customer Average Days To Pay

#OPT_CustomerAverageDaysToPay — Macola® ES, Macola 10 and Progression.

One row per customer giving how many days they actually take to pay: a configurable rolling-window average and a longer default average over at least 12 months. Exists so A/R and sales reports do not each repeat an expensive payment-history scan.

What one row represents. One row per customer. Customers with no qualifying payments in the window do not appear.

How much of it is in there. Always scans at least a full year back: StartDate = MIN(UserDate - RollingMonths, UserDate - 1 year). The shorter window is a CASE inside the _User average, not a filter.

Key fields. cus_no

Inventory

Inventory Status

#OPT_InventoryStatus — Macola® ES, Macola 10 and Progression.

The master picture of what you own, where it sits, what it cost and how fast it moves. One row per item per warehouse with quantities, value at three costing methods, ABC buckets, usage and turns over rolling 3-36 month windows, last-activity dates, months and days of supply, MRP gross demand and near-term forecast. Eleven other optimized tables depend on it.

What one row represents. One row per item_no + item_loc. Every row also carries pre-aggregated all-locations (_i) and location-group (_ilg) totals repeated on it.

How much of it is in there. No WhereStatement. Session: every item/location record in the ERP. Shared full rebuild: restricted to #IS_ActiveItems (a transaction in the last rolling year OR any non-zero quantity). Shared incremental: only rows in PDSTRACKER_InventoryStatus.

Differences between Macola® versions. The three variants are NOT column-compatible. The shared variant is the only one with the C01..C12/P01..P12 period columns; the session variant is the only one with mrpf_forecast_* and the SoldQty columns.

The fields fall into these groups.

  • Three roll-up levels — Every measure appears as _il (this location), _i (all locations for the item) and _ilg (the item's MRP location group), repeated on every row. For example qty_avail_il, qty_avail_i, qty_avail_ilg.
  • Current position — On hand, on order, allocated, available. For example qty_on_hand_il, qty_on_ord_il, qty_allocated_il, qty_avail_il, pulse_net_qty.
  • Inventory value — Extended value at average, last and standard cost. For example pulse_inventory_oh_amt_il_avg, pulse_inventory_oh_amt_i_std.
  • ABC value buckets — On-hand value split by ABC class. For example pulse_inventory_A_oh_amt, pulse_inventory_B_oh_amt, pulse_inventory_C_oh_amt, pulse_inventory_OTHER_oh_amt.
  • Usage and turns — Rolling windows of consumption and inventory turns. For example pulse_isp_usage_12_month, pulse_isp_avg_usage_6_month_il, pulse_isp_turn_12_month.
  • Supply cover — How long current stock lasts at current usage. For example pulse_isp_months_on_hand, pulse_isp_days_on_hand_il.
  • Last-activity dates — When the item was last touched in each way. For example pulse_isp_last_usage_dt, pulse_isp_last_sale_dt, pulse_isp_last_purchase_dt, pulse_inventory_aging_dt.
  • MRP columns — Written back by MRP_Details after it runs. For example mrp_net_qty_oh_il, min_short_dt_il, nr_min_short_dt_il, gross_demand_qty_il, mrp_include_fg.

Key fields. item_no, item_loc, mrp_loc_group, prod_cat, pulse_tracker_insert_dt

Reports that use it. Inventory Status, Stock Status, Inventory Aging, Inventory Exceptions Summary, Inventory Turns Graph, Inventory Turns Details, Inventory Transactions, Physical Inventory Count, Item Details View, Item Details View — MRP pane, Purchasing Exception Items, Production Graph, Reported Production, Production Orders History, Shop Floor Order View — BOM one-click, Shop Floor Order View — labor one-click, Shop Floor Order View — outside-ops one-click.

Inventory by Bin

#OPT_InventoryBin — Macola® ES, Macola 10 and Progression.

InventoryStatus fanned out to bin level. Query it to see where inside a warehouse the stock physically sits, while keeping every Inventory Status column alongside.

What one row represents. One row per item/location/bin. The bin file is LEFT JOINed, so an item/location with no bin record still produces one row with NULL bin columns.

How much of it is in there. No WhereStatement at all. MandatoryFields = 'item_no, item_loc'.

Key fields. item_no, item_loc, bin_no

Reports that use it. Inventory Exceptions Summary, Item Details View, Purchasing Exception POs.

Inventory by Lot

#OPT_InventoryLot — Macola® ES, Macola 10 and Progression.

InventoryStatus fanned out to lot or serial level. Query it for traceability and for stock held on quality hold.

What one row represents. One row per item/location/lot. The lot file is LEFT JOINed, so an item/location with no lot record still produces one row with NULL lot columns.

How much of it is in there. No WhereStatement at all. MandatoryFields = 'item_no, item_loc'.

Key fields. item_no, item_loc, lot_no

Reports that use it. Inventory Exceptions Summary, Item Details View.

Purchasing

Purchase Open Orders

#OPT_PurchaseOpenOrders — Macola® ES, Macola 10 and Progression.

Every purchase-order line still open with a supplier, with quantities, expected cost, request and promise dates, plus Pulse-calculated received-not-invoiced and over-receipt/over-invoice exposure.

What one row represents. One row per open PO line. Can fan out where the optional vendor/item cross-reference or comment join is not 1:1.

How much of it is in there. Session: open/partial lines on unposted/released/posted normal, drop-ship and blanket POs - NO lower date bound. Shared: additionally ord_dt >= start of the prior rolling year.

Key fields. po_ord_no, po_line_no, item_no, item_loc, ap_vend_no, job_no

Reports that use it. Open Purchase Orders, AP Aging, Cash Flow Forecast, Cash Flow Forecast — open-PO column, Cash Flow Forecast — received-not-invoiced column, Cash Flow Forecast (config), Item Details View, Item Details View — MRP pane, Purchasing Exception POs, POs Scheduled to be Received.

Purchase Orders History

#OPT_PurchaseOrdersHistory — Macola® ES, Macola 10 and Progression.

Every purchase-order line placed in roughly the last two years, open or closed - the full purchasing record for spend analysis, price history and PO-versus-receipt reconciliation.

What one row represents. One row per PO line with an order date in the window.

How much of it is in there. ord_dt >= start of the prior rolling year, po_type in ('N','D','B'), cancelled headers and lines excluded. No order-status filter - open and closed lines both kept.

Key fields. po_ord_no, po_line_no, ap_vend_no, item_no, item_loc, ord_dt

Reports that use it. Inventory Transactions, Item Details View, Item Details View — MRP pane.

Purchase Receipts

#OPT_PurchaseReceipts — Macola® ES, Macola 10 and Progression.

A line-by-line log of what physically arrived from suppliers over roughly the last two to three years, plus landed-cost charges. Feeds vendor on-time and quality performance.

What one row represents. After post-processing: one row per receipt-history record that is a physical receipt ('R') or a landed-cost charge ('L'). Inspection records ('I') are rolled up and then deleted.

How much of it is in there. rec_hst_dt >= start of the rolling year two years back - roughly two to three years.

Key fields. receipt_no, rec_receipt_dt, po_ord_no, po_line_no, item_no, item_loc, ap_vend_no, rec_record_type_cd

Reports that use it. Cash Flow Forecast, Cash Flow Forecast — received-not-invoiced, PO Receipts Over X Days, Top Vendors, Vendor Performance, Vendor Performance — summary.

Production

Production Orders

#OPT_ProductionProcessingOrders — Macola® ES, Macola 10 and Progression.

Every production (PP) order that is not closed or deleted - the manufacturing orders the plant is working through, with the sales order line that created the demand, quantities, dates, status and up to six value-added-cost rate/factor pairs.

What one row represents. One row per ppordfil_sql row with ord_status not in ('C','D').

How much of it is in there. No date bound at all - a live open-orders snapshot. Long-run production history lives in the separate OPT_ProductionScheduleHistory module table.

Differences between Macola® versions. Registered for every ERP but every joined table is hard-coded Macola®, with no EnableShopFloor-style guard. On a non-Macola® database it depends entirely on table overrides.

Key fields. pp_ord_no, org_ord_no, item_no, item_loc, job_no, cus_no, oe_ord_no

Reports that use it. Item Details View, Item Details View — MRP pane, Production Orders, Production Orders Aging, Production Exceptions Summary.

Shop Floor Orders

#OPT_ShopFloorOrders — Macola® ES, Macola 10 and Progression.

Every shop floor (work) order raised in the last rolling year or so, with its place in the multi-level work-order tree and optionally a full planned-versus-actual cost and hours breakdown.

What one row represents. One row per shop floor order header created on or after the start of the previous rolling year.

How much of it is in there. create_dt >= start of the previous rolling year. All statuses included; cancelled and closed orders get NULL mrp_rec_avail and '?' component availability.

Differences between Macola® versions. Registered for every ERP but hard-codes Macola® table names. GenerateData exits immediately when EnableShopFloor is False.

The fields fall into these groups.

  • Work-order tree — Position in the multi-level work order hierarchy. For example sf_ord_no, sf_par_ord_no, pulse_ord_level, pulse_is_top_level_ord, pulse_is_bottom_level_ord.
  • Planned vs actual money — Cost variance by category. For example sf_planned_labor_amt, sf_actual_labor_amt, sf_delta_totalcost_amt.
  • Planned vs actual time — Hours variance. For example sf_planned_labor_hours, sf_actual_machine_hours, sf_delta_labor_hours.

Key fields. sf_ord_no, sf_par_ord_no, item_no, item_loc, job_no, cus_no, oe_ord_no

Reports that use it. Item Details View, Item Details View — MRP pane, Shop Floor Orders Schedule, Shop Floor Order View, Shop Floor Order View — header, Multi-Level Shop Floor.

MRP

MRP Details - Supply & Demand Timeline

#OPT_MRP_Details — Macola® ES, Macola 10 and Progression.

The forward-looking supply-and-demand ledger. Every open transaction that will consume or replenish an item at a location is flattened into one row per event with a date and a signed quantity, then walked in date order so each row carries a running projected on-hand balance at item, item+location and location-group level.

What one row represents. One row per supply-or-demand event: (source system, order number, order line [+ subline in the shared variant], item_no, item_loc, transaction date).

How much of it is in there. 'Everything currently open, forward-looking' rather than a date window.

Differences between Macola® versions. Three separate hand-written implementations. The shared variant is Progression/ES only and drops in-transit and forecast demand entirely.

The fields fall into these groups.

  • Event identity — What kind of supply or demand this row is. For example mrp_ord_source, mrp_ord_type, mrp_demand_or_replenish_cd, mrp_ord_no, mrp_ord_line.
  • Running projected balance — Projected on-hand after this event, at three aggregation levels, plus ignore-replenishment variants. For example mrp_new_qty_i, mrp_new_qty_il, mrp_new_qty_ilg, mrp_new_nr_qty_il.
  • Quantities — Signed transaction quantity - negative for demand. For example mrp_trx_qty, pulse_ics_qty_demand, pulse_ics_qty_replenish.
  • Traceability — Which order caused this event. For example oe_ord_no, org_ord_no, par_item_no, kit_item_no, po_ord_no.

Key fields. item_no, item_loc, mrp_loc_group, mrp_ord_source, mrp_ord_no, mrp_ord_line, mrp_ord_subline, mrp_demand_or_replenish_cd, mrp_trx_dt

Reports that use it. Items Causing Shortages, Item Details View, Components Causing Shortages, Shop Floor Order View — BOM one-click, MRP Orders Causing Shortages.

Supporting tables you can query

The refresh builds these on the way to its result and leaves them in the session, so your own SQL can reuse the work instead of repeating it. The Optimized Table Explorer lists them under the table's Supporting tables folder, and IntelliSense completes their names. They exist only with Fully Explode BOM enabled and MRP module licensed.

#FlatBOM — flattened multi-level bill of materials

Every manufactured item exploded down through its bill of materials, one row per parent-to-component link at every level. Built by repeatedly joining the previous level's components back to the BOM, so a level-3 row still names the finished good the branch started from and carries both the quantity per finished good and the quantity per immediate parent. Only manufactured items are exploded (pur_or_mfg = 'M'); a purchased item ends the branch.

What one row represents. One parent-to-component link, per finished good, per BOM level.

Worth knowing. The explosion stops at level 10. A bill of materials deeper than ten levels is silently truncated and nothing warns you.

#ComponentLevel — deepest BOM level each item reaches (low-level code)

The low-level code for every item: the deepest point at which it can appear in any bill of materials. Taken as the maximum level per component in #FlatBOM, then every item that is never anybody's component is added at level 0. The forecast nets one level at a time from finished goods downward, so this table is what guarantees a parent is planned before the components it consumes.

What one row represents. One row per item.

Worth knowing. Inherits #FlatBOM's level-10 ceiling: an item that only appears below level 10 is reported at level 0 as though it were a finished good.

#BaseItemLoc — the planning universe for the run

Which items this forecast run has to plan at all. Starts as every item that has a forecast, an open customer order or a production order, then expands through #FlatBOM to pull in every component and sub-component those items consume. It is what keeps the wide forecast table from being built across the whole item master.

What one row represents. One item, or one item and location when the run aggregates by location.

Worth knowing. The location column is called 'loc' here but 'item_loc' in #FlatForecast. Joining the two on item_loc fails.

#FlatForecast — the weekly forecast and MRP projection, over a fully exploded bill of materials

The result of the forecast MRP run, projected across the whole exploded bill of materials rather than the finished goods alone. Its rows are not just the items somebody forecast or ordered: that starting list is expanded through #FlatBOM to bring in every component and sub-component beneath them, at every level, so each one gets a planned row of its own tagged with its BOM level. Demand is then netted one level at a time from finished goods downward, a parent's shortage becoming its children's component demand multiplied by the quantity per parent. Every row carries on-hand, the minimum quantity to hold and the item's BOM level, followed by ten measures repeated once for every weekly planning period.

What one row represents. One row per planned item, or per item and location: finished goods and every component beneath them alike, with each planning period held as columns rather than rows.

The repeating measures. Suffixed _000 through _NNN, one per weekly planning period. Set at runtime from the Forecast Number of Weeks setting, so the table is 7 + 10 x N columns wide and differs between installs and between runs.

  • BaseForecast — The independent forecast for the period - from the Macola® forecast, or from the selected Pulse budget scenario when one is chosen.
  • CustomerDemand — Demand from open sales orders falling in the period. Quote lines are always excluded; pick tickets are excluded when that option is set.
  • ProductionDemand — Demand from real production orders in the period.
  • ComponentShortageDemand — Demand created for this item by a shortage of one of its parents, propagated down the bill of materials.
  • Replenish — Supply arriving in the period.
  • NetDemand — The netted requirement: production and component-shortage demand, plus whichever is larger of the remaining forecast or the open customer orders.
  • NetForecast — Computed and stored but consumed by nothing - the source comment says NOT USED FOR ANYTHING. Do not build on it.
  • Shortage — How far the period's demand exceeds what is projected to be available.
  • EndQty — Projected balance at the end of the period.
  • AdjustedEndQty — Projected balance at the end of the period after the minimum-quantity floor is applied.

Worth knowing. The column set is not fixed. Ten measures repeat once per weekly planning period, so the width follows the Forecast Number of Weeks setting. Never assume a fixed column list - read the columns, or use the measure pattern.

Names you may see that are not tables you can query

These appear in PULSE Dashboard's own internals and in older notes, but there is no table of that name to select from. If you have been given one of these names, the entry says what to use instead.

Customer Orders History (logical parent)

The master definition of Pulse's sales-activity cache: every open order line and every invoiced line for roughly two years, enriched with customer, salesperson, item and address attributes. It is the recipe and refresh owner - the data lands in CustomerOrdersYear and CustomerOrdersMonth.

There is no table of this name. Query #OPT_CustomerOrdersYear or #OPT_CustomerOrdersMonth.

Customer Orders History, Split by Salesperson (controller)

The control handle for the commission-split sales cache. A box configured with 'Split per Salesperson' registers here; that triggers one rebuild of CustomerOrdersYearSplit and CustomerOrdersMonthSplit.

Querying #OPT_CustomerOrdersHistorySplit fails - the data is in the Year and Month split tables.

Generated from the PULSE Dashboard optimized-table catalogue, version 1.1.3 (2026-08-27). The exact fields available to you are resolved from your own Query Matrix when the software runs, so they can differ from another installation — the Optimized Table Explorer shows yours.