Skip to main content

Query Library

Choose a question from a business process, then browse the same business topics used in Reports and Analytics. Each query appears once, with its objective, output grain, source tables, expandable SQL, and original-file download together.

Start with the guided paths below for a connected investigation, or search by question or table and select a business topic. Expand Interpret these results for topic-specific accounting guidance and Anomaly coverage where available. The guided query paths connect statement reconciliation and CAPEX steps without duplicating query cards.

Guided Paths and Query Catalog​

Guided Query Paths​

Follow these sequences when the question requires several linked queries. Each step links to its query in the catalog below.

Financial Statement Reconciliation Path

This sequence follows the statement tie from annual net income into retained earnings, then narrows into revenue reconciliation when the statement view and the operational view stop matching. Work through the queries in order so the bridge stays connected from statement to ledger to source document.

  1. Annual Income to Equity Bridge

    Start with the annual statement tie between net income, retained earnings, and the balance-sheet residual.

  2. Post Close Profit and Loss Leakage Review

    Then confirm that no revenue, expense, or 8010 balances remain open after the close.

  3. Round Dollar Manual Journal Close Sensitivity Review

    Use this sensitivity review only when a custom anomaly profile or manual edit leaves unexplained residuals after close.

  4. Annual Net Revenue Bridge

    Then move account by account into annual net revenue.

  5. Invoice Revenue Cutoff Exception Summary

    Open the cutoff summary when annual net revenue does not tie from source documents into the GL.

  6. Invoice Revenue Cutoff Exception Trace

    Then inspect the affected invoice lines, shipment lines, and revenue GL rows in detail.

CAPEX and Fixed Asset Path

This sequence starts in the fixed-asset subledger, then ties the asset lifecycle back into manufacturing cost, debt cash flows, and the budget roll-forward. Work through it in order so students keep the operational document chain separate from depreciation, note financing, and disposal accounting.

  1. Fixed Asset Rollforward by Behavior Group

    Start with the monthly rollforward by behavior group so manufacturing, warehouse, and office assets stay separated.

  2. Capex Acquisitions Financing and Disposals

    Then trace the lifecycle events behind each addition, financing choice, and disposal.

  3. Debt Amortization and Cash Impact

    Use the debt schedule next so principal, interest, and cash timing are explicit.

  4. Manufacturing Cost Component Bridge

    Then connect manufacturing-equipment depreciation back into manufacturing clearing and conversion cost.

  5. Cash Flow Statement Indirect Monthly

    Use the indirect cash flow statement to show how CAPEX moves into investing cash while note activity moves into financing cash.

  6. Pro Forma Cash Flow Indirect Monthly

    Finish with the pro forma cash flow to compare the explicit CAPEX and note schedule with the budget roll-forward.

All Queries​

Showing 170 of 170 queries

Statements and Cash​

Reconcile statements and close entries, explain cash and working capital, and trace accruals, assets, and debt.

Connect the evidence: Journals and Close · Accounting core tables · Estimate and settlement practice · Profit, cash, and close case

Interpret these results
  • Working-capital analysis gets stronger when students separate balances from timing.
  • Accrued-expense analysis should focus on 2040, PurchaseInvoiceLine.AccrualJournalEntryID, the service-item lines that settle finance-managed estimates, and the outbound-freight accrual and settlement pattern created from Shipment.FreightCost.
  • Year-end close entries are real posted journals and should be filtered when you want raw multi-year P&L activity.
  • The income-statement starter queries use pre-close P&L activity, so they exclude the year-end close journals that zero out revenue and expense accounts.
  • The balance-sheet starter queries are point-in-time ending-balance statements, not monthly activity reports.
  • The balance-sheet starter queries derive Current Year Earnings for interim periods so assets continue to tie to liabilities plus equity before the annual close moves earnings into retained earnings.
  • The indirect-method cash flow starter queries reconcile from pre-close net income into operating cash, then combine that with investing and financing cash movements.
  • The direct-method cash flow starter queries classify cash-ledger activity into teaching buckets such as customer receipts, supplier payments, payroll, and other operating cash.
  • The cash flow starter queries treat opening journals as the Beginning Cash seed for the first reporting period instead of showing them as operating, investing, or financing flows.
  • Warehouse and office fixed-asset depreciation debits 6130, which keeps those assets in operating expense even when they share the same broad fixed-asset lifecycle controls as plant equipment.
  • CAPEX additions, note-financing reclasses, debt payments, and disposals now sit in the same financial-analysis path, so students can reconcile the asset register to investing cash, financing cash, and gain-or-loss activity.
  • The budget model starts from BudgetLine, so students can move from forecast, planned price, standard cost, payroll, and timing assumptions into the summary Budget table and the pro forma statements.

Trial Balance by Period​

Produce a period-by-period trial balance from the posted general ledger.

Output
One row per fiscal period and non-header account.
Main tables
GLEntry, Account.
Download SQL

Journal and Close Cycle Review​

Review journal-entry activity, recurring journals, reversals, and year-end close volume.

Output
One row per posting month and journal entry type.
Main tables
JournalEntry.
Download SQL

Control Account Reconciliation​

Compare key control-account balances to expected subledger balances.

Output
One row per control area with expected, actual, and difference amounts.
Main tables
GLEntry, Account, SalesInvoice, CashReceipt, CashReceiptApplication, CreditMemo, CustomerRefund, PurchaseInvoice, PurchaseInvoiceLine, PurchaseOrderLine, DisbursementPayment, GoodsReceiptLine, ShipmentLine, SalesReturnLine.
Download SQL

Accrued Expense Rollforward​

Roll forward accrued-expense activity by month and accrual source.

Output
One row per fiscal year, fiscal period, accrual source, and expense family.
Main tables
JournalEntry, GLEntry, Account, PurchaseInvoice, PurchaseInvoiceLine, Shipment.
Download SQL

Accrued vs Invoiced vs Paid Timing​

Compare accrued-expense recognition timing to later invoicing and payment timing.

Output
One row per accrual-linked service invoice.
Main tables
JournalEntry, GLEntry, Account, PurchaseInvoice, PurchaseInvoiceLine, DisbursementPayment, Supplier.
Download SQL

Retained Earnings and Close Entry Impact​

Separate raw P&L activity from year-end close impact on retained earnings.

Output
One row per fiscal year with pre-close net income, statement net income, close-step amounts, and retained-earnings impact.
Main tables
GLEntry, Account, JournalEntry.
Download SQL

Working Capital Bridge by Month​

Review the month-end working-capital bridge across the main current-asset and current-liability control accounts.

Output
One row per fiscal month with ending balances for key working-capital buckets.
Main tables
GLEntry, Account.
Download SQL

Cash Conversion Timing Review​

Compare how quickly Charles River turns sales invoices, purchase invoices, and goods receipts into cash settlement events.

Output
One row per source-month and timing metric family.
Main tables
SalesInvoice, CashReceiptApplication, PurchaseInvoice, DisbursementPayment, GoodsReceipt, GoodsReceiptLine, PurchaseInvoiceLine.
Download SQL

Income Statement Monthly​

Produce a detailed monthly income statement from posted general-ledger activity.

Output
One row per fiscal month and income-statement line.
Main tables
GLEntry, Account, JournalEntry.
Download SQL

Income Statement Quarterly​

Produce a detailed quarterly income statement from posted general-ledger activity.

Output
One row per fiscal quarter and income-statement line.
Main tables
GLEntry, Account, JournalEntry.
Download SQL

Income Statement Annual​

Produce a detailed annual income statement from posted general-ledger activity.

Output
One row per fiscal year and income-statement line.
Main tables
GLEntry, Account, JournalEntry.
Download SQL

Balance Sheet Monthly​

Produce a detailed monthly classified balance sheet from posted general-ledger activity.

Output
One row per fiscal month and balance-sheet line.
Main tables
GLEntry, Account, JournalEntry.
Download SQL

Balance Sheet Quarterly​

Produce a detailed quarterly classified balance sheet from posted general-ledger activity.

Output
One row per fiscal quarter and balance-sheet line.
Main tables
GLEntry, Account, JournalEntry.
Download SQL

Balance Sheet Annual​

Produce a detailed annual classified balance sheet from posted general-ledger activity.

Output
One row per fiscal year and balance-sheet line.
Main tables
GLEntry, Account, JournalEntry.
Download SQL

Cash Flow Statement Indirect Monthly​

Produce a detailed monthly indirect-method cash flow statement from posted ledger activity.

Output
One row per fiscal month and cash-flow statement line.
Main tables
GLEntry, Account, JournalEntry.
Download SQL

Cash Flow Statement Indirect Quarterly​

Produce a detailed quarterly indirect-method cash flow statement from posted ledger activity.

Output
One row per fiscal quarter and cash-flow statement line.
Main tables
GLEntry, Account, JournalEntry.
Download SQL

Cash Flow Statement Indirect Annual​

Produce a detailed annual indirect-method cash flow statement from posted ledger activity.

Output
One row per fiscal year and cash-flow statement line.
Main tables
GLEntry, Account, JournalEntry.
Download SQL

Cash Flow Statement Direct Monthly​

Produce a detailed monthly direct-method cash flow statement from posted cash-ledger activity.

Output
One row per fiscal month and cash-flow statement line.
Main tables
GLEntry, Account, JournalEntry.
Download SQL

Cash Flow Statement Direct Quarterly​

Produce a detailed quarterly direct-method cash flow statement from posted cash-ledger activity.

Output
One row per fiscal quarter and cash-flow statement line.
Main tables
GLEntry, Account, JournalEntry.
Download SQL

Cash Flow Statement Direct Annual​

Produce a detailed annual direct-method cash flow statement from posted cash-ledger activity.

Output
One row per fiscal year and cash-flow statement line.
Main tables
GLEntry, Account, JournalEntry.
Download SQL

Annual Income to Equity Bridge​

Reconcile annual net income to the retained-earnings close and year-end balance-sheet residuals.

Output
One row per closed fiscal year with income-statement, close-process, retained-earnings, and balance-sheet bridge columns.
Main tables
GLEntry, Account, JournalEntry.
Download SQL

Post Close Profit and Loss Leakage Review​

Surface any P&L or income-summary accounts that still carry a balance after the annual close.

Output
One row per fiscal year and leaked account with a non-zero ending balance.
Main tables
GLEntry, Account, JournalEntry.
Download SQL

Round Dollar Manual Journal Close Sensitivity Review​

Highlight rounded manual journals that are likely to affect annual close and statement reconciliation.

Output
One row per close-sensitive manual journal with whole-dollar and two-line diagnostics.
Main tables
JournalEntry, GLEntry, Account.
Download SQL

Pro Forma Income Statement Monthly​

Produce a monthly pro forma income statement from driver-based budget detail.

Output
One row per fiscal month and income-statement line.
Main tables
BudgetLine, Account.
Download SQL

Pro Forma Balance Sheet Monthly​

Produce a monthly pro forma classified balance sheet from the driver-based budget roll-forward.

Output
One row per fiscal month and balance-sheet line.
Main tables
BudgetLine, Account.
Download SQL

Pro Forma Cash Flow Indirect Monthly​

Produce a monthly pro forma indirect-method cash flow statement from the driver-based budget roll-forward.

Output
One row per fiscal month and cash-flow statement line.
Main tables
BudgetLine, Account, GLEntry, JournalEntry.
Download SQL

Budget vs Actual Statement Bridge Monthly​

Compare monthly pro forma statement lines to posted actual statement lines across performance, position, and cash.

Output
One row per fiscal month and bridged statement line.
Main tables
BudgetLine, GLEntry, Account, JournalEntry.
Download SQL

Budget vs Actual Working Capital and Cash Bridge​

Compare budgeted and actual month-end working-capital balances and cash on one bridge.

Output
One row per fiscal month and working-capital or cash metric.
Main tables
BudgetLine, GLEntry, Account, JournalEntry.
Download SQL

Fixed Asset Rollforward by Behavior Group​

Roll forward fixed-asset gross cost, accumulated depreciation, and net book value by behavior group.

Output
One row per fiscal month and asset behavior group.
Main tables
FixedAsset, FixedAssetEvent, JournalEntry.
Download SQL

Capex Acquisitions Financing and Disposals​

Trace CAPEX additions, financing, and disposals across the asset lifecycle.

Output
One row per fixed-asset event excluding opening balances.
Main tables
FixedAssetEvent, FixedAsset, PurchaseInvoice, DisbursementPayment, DebtAgreement, JournalEntry.
Download SQL

Debt Amortization and Cash Impact​

Show the note-payable amortization schedule and cash impact tied to CAPEX financing.

Output
One row per scheduled debt payment line.
Main tables
DebtAgreement, DebtScheduleLine, FixedAsset.
Download SQL

Budget vs Actual by Cost Center​

Compare monthly budget to posted operating expense by cost center and account.

Output
One row per fiscal year, month, cost center, and budgeted account.
Main tables
Budget, CostCenter, Account, GLEntry, JournalEntry.
Download SQL

Cost Center Activity Summary​

Summarize operational volume and posted operating expense by cost center and month.

Output
One row per activity month and cost center.
Main tables
SalesOrder, PurchaseRequisition, GLEntry, Account, JournalEntry, CostCenter.
Download SQL

Cutoff and Timing Analysis​

Summarize timing gaps across key O2C and P2P process transitions.

Output
One row per timing metric with average, minimum, maximum, and negative-gap counts.
Main tables
SalesInvoice, SalesInvoiceLine, Shipment, ShipmentLine, PurchaseRequisition, PurchaseOrderLine, PurchaseOrder, GoodsReceipt, GoodsReceiptLine, PurchaseInvoice, PurchaseInvoiceLine.
Download SQL

Over Under Accrual Review​

Flag accrued-expense settlements that materially differ from the original estimate or remain uncleared.

Output
One row per potentially unusual accrual outcome.
Main tables
JournalEntry, GLEntry, Account, PurchaseInvoiceLine, PurchaseInvoice.
Download SQL

Accrued Service Settlement Exception Review​

Review direct service invoices that clear prior accruals and identify timing or amount exceptions.

Output
One row per accrued-service invoice line with timing, payment, and amount-difference flags.
Main tables
JournalEntry, PurchaseInvoice, PurchaseInvoiceLine, DisbursementPayment, Supplier, Item.
Download SQL

Sales and Customers​

Investigate revenue, pricing, product margins, service billing, customer balances, commissions, and related controls.

Connect the evidence: Order-to-Cash · Sales and customer tables · Customer settlement practice · Customer account case · Design Services

Interpret these results
  • Revenue posts at invoicing. COGS posts at shipment.
  • CashReceipt does not equal settled AR by itself. Use CashReceiptApplication.
  • Explicit price-list, promotion, and override lineage lets students analyze revenue and margin against commercial rules.
  • The richer item master now supports collection, style family, lifecycle, and supply-mode financial analysis without changing the underlying posting model.
  • Customer deposits and unapplied cash analysis should start from CashReceipt and CashReceiptApplication, alongside AR.
  • Sales commission analysis should start from SalesInvoiceLine.LineTotal, then trace SalesCommissionAccrual, SalesCommissionAdjustment, SalesCommissionPaymentLine, and SalesCommissionPayment into 2034 and 6290.
  • Supply mode changes the meaning of cost analysis. Manufactured items can support both absorption and contribution-margin work because fixed overhead is stored separately.
  • Portfolio mix and profitability should be read together with service-level measures such as fill rate, shipment lag, and return pressure.
  • The dataset includes commercial-pricing coverage, so students can compare list price, resolved price-list pricing, promotions, overrides, and net realized margin without introducing a separate quote system.
  • Sales commissions are modeled as a separate selling-cost subledger, so students can compare commission expense by rep, segment, and revenue type without mixing it into payroll.
  • Pricing-control review should distinguish price-list master-data failures from transaction-level override and promotion-use failures.

Monthly Revenue and Gross Margin​

Review monthly revenue, COGS, and gross margin from posted accounting activity.

Output
One row per fiscal year and fiscal period.
Main tables
GLEntry, Account.
Download SQL

AR Aging Open Invoices​

Build an accounts receivable aging listing from invoices, cash applications, and credit memos.

Output
One row per open sales invoice.
Main tables
SalesInvoice, CashReceiptApplication, CreditMemo, Customer.
Download SQL

Customer Credit and Refunds​

Review customer credit balances created by credit memos and cleared by refunds.

Output
One row per credit memo with customer-credit and refund activity.
Main tables
CreditMemo, CustomerRefund, SalesInvoice, Customer.
Download SQL

Customer Deposits and Unapplied Cash Aging​

Review customer deposits, unapplied cash, and receipt-application timing after receipt.

Output
One row per cash receipt with application timing and open unapplied balance.
Main tables
CashReceipt, CashReceiptApplication, Customer.
Download SQL

Revenue and Gross Margin by Collection Style Lifecycle Supply Mode​

Compare billed sales, credits, net sales, standard cost, and gross margin by collection, style family, lifecycle, and supply mode.

Output
One row per item group, collection, style family, lifecycle status, and supply mode.
Main tables
SalesInvoiceLine, ShipmentLine, CreditMemoLine, SalesReturnLine, Item.
Download SQL

Price Realization vs List by Segment Customer Region Collection Style​

Compare realized billed pricing to base list price by customer, segment, region, and product portfolio.

Output
One row per month, region, customer segment, customer, collection, and style family.
Main tables
SalesInvoice, SalesInvoiceLine, Customer, Item.
Download SQL

Gross Margin Impact of Promotions vs Nonpromotion Sales​

Compare revenue and gross margin for promoted versus non-promoted sales.

Output
One row per month, collection, and promotion flag.
Main tables
SalesInvoiceLine, ShipmentLine, SalesInvoice, Item.
Download SQL

Annual Net Revenue Bridge​

Trace annual net revenue from operational source documents into posted GL activity and the annual income statement.

Output
One row per fiscal year with operational, pre-close GL, and statement net-revenue totals plus variances.
Main tables
SalesInvoice, SalesInvoiceLine, CreditMemo, CreditMemoLine, GLEntry, Account, JournalEntry.
Download SQL

Invoice Revenue Cutoff Exception Summary​

Isolate invoice-level revenue cutoff exceptions that move operational invoice revenue into a different GL fiscal year.

Output
One row per exception invoice header with shipment timing, revenue GL posting year, and root-cause classification fields.
Main tables
SalesInvoice, SalesInvoiceLine, Shipment, ShipmentLine, GLEntry, Account, SalesOrder, Customer.
Download SQL

Invoice Revenue Cutoff Exception Trace​

Trace invoice-level revenue cutoff exceptions from order and shipment source lines into the posted revenue GL rows.

Output
One row per exception invoice line and related operating-revenue GL row, including trace classifications and line-to-GL variances.
Main tables
SalesOrder, SalesOrderLine, Shipment, ShipmentLine, SalesInvoice, SalesInvoiceLine, GLEntry, Account, Item, Customer.
Download SQL

Monthly AR Aging Detail​

Reconstruct month-end accounts receivable aging positions across the full dataset timeline.

Output
One row per open sales invoice per month-end.
Main tables
SalesInvoice, CashReceiptApplication, CreditMemo, Customer.
Download SQL

Monthly AR Aging Summary​

Summarize month-end accounts receivable aging positions by customer across the full dataset timeline.

Output
One row per month-end and customer.
Main tables
SalesInvoice, CashReceiptApplication, CreditMemo, Customer.
Download SQL

Design Service Revenue and Billed Hours by Customer Month​

Review monthly billed design-service hours and posted service revenue by customer.

Output
One row per invoice month and customer.
Main tables
ServiceBillingLine, SalesInvoice, SalesInvoiceLine, Customer, GLEntry, Account.
Download SQL

Sales Commission Payable Rollforward​

Reconcile sales commission payable activity from invoice accrual through clawback and payment.

Output
One row per fiscal period with sales commission payable activity and ending balance.
Main tables
GLEntry, Account, SalesCommissionAccrual, SalesCommissionAdjustment, SalesCommissionPayment.
Download SQL

Sales Mix by Customer Region Item Group​

Analyze billed sales mix by customer geography, customer segment, and product family.

Output
One row per invoice month, region, customer segment, item group, and item.
Main tables
SalesInvoice, SalesInvoiceLine, Customer, Item.
Download SQL

Basic Product Profitability​

Estimate product-level profitability from billed revenue and shipped cost.

Output
One row per item.
Main tables
SalesOrderLine, SalesInvoiceLine, ShipmentLine, Item.
Download SQL

Absorption vs Contribution Margin​

Compare absorption margin and contribution margin by item and supply mode.

Output
One row per item.
Main tables
SalesInvoiceLine, SalesInvoice, Item.
Download SQL

Manufactured vs Purchased Margin Comparison​

Compare margin structure between manufactured and purchased finished goods.

Output
One row per supply mode.
Main tables
SalesInvoiceLine, Item.
Download SQL

Backorder Fill Rate and Shipment Lag​

Measure fill rate, remaining backorder quantity, and shipment lag from order entry.

Output
One row per order month and item group.
Main tables
SalesOrder, SalesOrderLine, Shipment, ShipmentLine, Item.
Download SQL

Returns and Refund Impact by Customer and Item​

Review how returned quantity, credit activity, and refunds affect customer and item-group performance.

Output
One row per credit-memo month, customer region, and item grouping.
Main tables
CreditMemo, CreditMemoLine, SalesReturnLine, CustomerRefund, Customer, Item.
Download SQL

Sales Margin by Collection Style Material​

Compare billed sales, credits, net sales, cost, and gross margin by product collection and lifecycle attributes.

Output
One row per item group, collection, style family, material, and lifecycle status.
Main tables
SalesInvoiceLine, ShipmentLine, CreditMemoLine, SalesReturnLine, Item.
Download SQL

Product Portfolio Mix by Collection Style Lifecycle Supply Mode​

Review the product portfolio by collection, style family, lifecycle, and supply mode with both SKU counts and billed activity.

Output
One row per sellable portfolio grouping.
Main tables
Item, SalesInvoiceLine.
Download SQL

Contribution Margin by Collection Material Lifecycle Supply Mode​

Compare contribution margin by collection, material, lifecycle, and supply mode.

Output
One row per item group and portfolio attribute grouping.
Main tables
SalesInvoiceLine, CreditMemoLine, Item.
Download SQL

Customer Service Impact by Collection Style​

Review customer-service performance by collection and style family using shipment lag, fill rate, and backorder pressure.

Output
One row per item group, collection, and style family.
Main tables
SalesOrder, SalesOrderLine, Shipment, ShipmentLine, Item.
Download SQL

Portfolio Return Refund Impact by Collection Lifecycle​

Compare return, credit, and refund impact by collection and lifecycle status.

Output
One row per item group, collection, and lifecycle grouping.
Main tables
SalesInvoiceLine, CreditMemo, CreditMemoLine, CustomerRefund, Item.
Download SQL

Sales Rep Override Rate and Discount Dispersion​

Review override concentration and promotion discount dispersion by sales rep and customer segment.

Output
One row per sales rep and customer segment.
Main tables
SalesOrder, SalesOrderLine, Employee, Customer.
Download SQL

Collection Revenue Margin Before After Promotions​

Compare collection-level revenue before promotions to net revenue after promotions.

Output
One row per month and collection.
Main tables
SalesInvoiceLine, ShipmentLine, SalesInvoice, Item.
Download SQL

Customer Specific Pricing Concentration and Dependency​

Show which customers depend most on customer-specific pricing instead of segment pricing.

Output
One row per customer.
Main tables
SalesOrder, SalesOrderLine, Customer.
Download SQL

Monthly Price Floor Pressure and Override Concentration​

Review how often sales lines sit at or below the price floor and how often override approvals are used.

Output
One row per month.
Main tables
SalesOrder, SalesOrderLine, PriceListLine.
Download SQL

Budget vs Actual Revenue Price Volume Cost Bridge​

Compare budgeted and actual quantity, revenue, price realization, and standard-cost consumption by collection and style family.

Output
One row per fiscal month, collection, and style family.
Main tables
BudgetLine, Item, SalesInvoice, SalesInvoiceLine.
Download SQL

Design Service Engagement Utilization and Labor Margin​

Compare engagement staffing mix, utilization, billed revenue, and labor margin for design services.

Output
One row per service engagement.
Main tables
ServiceEngagement, ServiceEngagementAssignment, ServiceTimeEntry, ServiceBillingLine, Customer, Employee.
Download SQL

Sales Commission Expense by Rep Segment​

Review sales commission expense by sales rep, revenue type, customer segment, and invoice month.

Output
One row per sales rep, revenue type, customer segment, and fiscal month.
Main tables
SalesCommissionAccrual, SalesCommissionAdjustment, Employee, Customer.
Download SQL

O2C Document Chain Completeness​

Trace sales orders through shipment, billing, cash application, and return activity to review document-chain completeness.

Output
One row per sales order with quantity and document-chain completion indicators.
Main tables
SalesOrder, SalesOrderLine, Shipment, ShipmentLine, SalesInvoice, SalesInvoiceLine, CashReceiptApplication, SalesReturnLine, CreditMemo.
Download SQL

Backorder and Return Review​

Identify backordered orders and orders that later experienced returns or credit memos.

Output
One row per sales order with backorder and return indicators.
Main tables
SalesOrder, SalesOrderLine, ShipmentLine, SalesInvoiceLine, SalesReturn, SalesReturnLine, CreditMemo.
Download SQL

Customer Deposits and Unapplied Cash Exception Review​

Review customer receipts that remain unapplied or show unusual application timing.

Output
One row per customer receipt flagged for deposit or unapplied-cash review.
Main tables
CashReceipt, CashReceiptApplication, Customer.
Download SQL

Sales Below Floor without Approval​

Identify sales lines priced below the configured floor without a valid override approval.

Output
One row per offending sales order line.
Main tables
SalesOrder, SalesOrderLine, PriceListLine, Customer, Item.
Download SQL
Anomaly coverage

Review focus: price-floor review

Expected anomaly types: sale_below_price_floor_without_approval, missing_price_override_approval

Trace flagged rows to their source documents before concluding that a control failed.

Expired or Overlapping Price List Review​

Review price lists that are used after expiry or overlap another active list for the same scope.

Output
One row per expired-use or overlapping-scope exception.
Main tables
PriceList, PriceListLine, SalesOrder, SalesOrderLine.
Download SQL
Anomaly coverage

Review focus: price-list validity review

Expected anomaly types: expired_price_list_used, overlapping_active_price_list

Trace flagged rows to their source documents before concluding that a control failed.

Promotion Scope and Date Mismatch Review​

Find sales lines that use a promotion outside the allowed date window or scope.

Output
One row per mismatched promoted sales line.
Main tables
SalesOrder, SalesOrderLine, PromotionProgram, Customer, Item.
Download SQL
Anomaly coverage

Review focus: promotion-scope review

Expected anomaly types: promotion_outside_effective_dates

Trace flagged rows to their source documents before concluding that a control failed.

Customer Specific Price List Bypass Review​

Identify customer orders that bypass an available customer-specific price list.

Output
One row per bypassed sales order line.
Main tables
SalesOrder, SalesOrderLine, Customer, PriceList, PriceListLine.
Download SQL
Anomaly coverage

Review focus: customer-price review

Expected anomaly types: customer_specific_price_bypass

Trace flagged rows to their source documents before concluding that a control failed.

Override Approval Completeness Review​

Review override approvals that are incomplete or missing from override-priced sales lines.

Output
One row per incomplete approval or missing linked approval.
Main tables
PriceOverrideApproval, SalesOrderLine, SalesOrder, Customer, Item, PriceListLine.
Download SQL
Anomaly coverage

Review focus: override-approval review

Expected anomaly types: missing_price_override_approval, sale_below_price_floor_without_approval

Trace flagged rows to their source documents before concluding that a control failed.

Design Service Approved vs Billed Hours Review​

Review whether approved design-service hours, billed hours, and invoice coverage stay aligned by engagement.

Output
One row per service engagement.
Main tables
ServiceEngagement, ServiceEngagementAssignment, ServiceTimeEntry, ServiceBillingLine, Customer.
Download SQL

O2C Line Trace Order Shipment Invoice​

Trace one O2C line from order entry through shipment and invoice detail.

Output
One row per order-line-to-shipment-line-to-invoice-line trace.
Main tables
SalesOrder, SalesOrderLine, Shipment, ShipmentLine, SalesInvoice, SalesInvoiceLine, Customer.
Download SQL

O2C Source to GL Trace​

Tie shipment, invoice, and commission source rows back to posted GL activity.

Output
One row per shipment or invoice source line and related GL posting row.
Main tables
Shipment, ShipmentLine, SalesInvoice, SalesInvoiceLine, SalesCommissionAccrual, SalesCommissionAdjustment, SalesCommissionPayment, GLEntry, Account, SalesOrder, Customer.
Download SQL

Purchasing and Inventory​

Trace purchasing and supplier settlement, compare inventory and supply reliability, and review AP and item-master exceptions.

Connect the evidence: Procure-to-Pay · Purchasing tables · Receipt-to-payable practice · Supplier obligations case

Interpret these results
  • Forecast, policy, and replenishment tables support planning-informed working-capital and inventory-timing analysis.
  • The item master supports collection, style family, material, finish, color, lifecycle, and supply-mode analysis inside the existing Item table.
  • Item-master review should distinguish current-state lifecycle and launch-date logic from operational usage timing.

AP Aging Open Invoices​

Build an accounts payable aging listing from supplier invoices and disbursements.

Output
One row per open supplier invoice.
Main tables
PurchaseInvoice, DisbursementPayment, Supplier.
Download SQL

Monthly AP Aging Detail​

Reconstruct month-end accounts payable aging positions across the full dataset timeline.

Output
One row per open supplier invoice per month-end.
Main tables
PurchaseInvoice, DisbursementPayment, Supplier.
Download SQL

Monthly AP Aging Summary​

Summarize month-end accounts payable aging positions by supplier across the full dataset timeline.

Output
One row per month-end and supplier.
Main tables
PurchaseInvoice, DisbursementPayment, Supplier.
Download SQL

Inventory Movement by Item and Warehouse​

Compare inbound and outbound inventory movement by month, warehouse, and item.

Output
One row per activity month, warehouse, and item.
Main tables
GoodsReceipt, GoodsReceiptLine, Shipment, ShipmentLine, Warehouse, Item.
Download SQL

Purchasing Activity by Supplier Category​

Review purchasing volume by supplier, supplier category, and item group.

Output
One row per order month, supplier, and item group.
Main tables
PurchaseOrder, PurchaseOrderLine, Supplier, Item.
Download SQL

Supplier Lead Time and Receipt Reliability​

Review supplier lead time and receipt reliability from purchase order to first and final receipt.

Output
One row per order month and supplier.
Main tables
PurchaseOrder, PurchaseOrderLine, GoodsReceipt, GoodsReceiptLine, Supplier.
Download SQL

Inventory Coverage and Projected Stockout Risk​

Estimate inventory coverage and stockout risk from the latest planning recommendation state.

Output
One row per item and warehouse at the latest planned week.
Main tables
SupplyPlanRecommendation, DemandForecast, Item, Warehouse.
Download SQL

Expedite Pressure by Item Family and Month​

Measure how often planning pressure escalates to expedite recommendations.

Output
One row per month and item-family slice.
Main tables
SupplyPlanRecommendation, Item.
Download SQL

Supply Plan Driver Mix by Collection and Supply Mode​

Review recommendation mix by planning driver, collection, and supply mode.

Output
One row per collection, supply mode, and driver.
Main tables
SupplyPlanRecommendation, Item.
Download SQL

P2P Document Chain Completeness​

Trace requisitions through PO lines, receipts, supplier invoices, and payments to review P2P completeness.

Output
One row per purchase requisition.
Main tables
PurchaseRequisition, PurchaseOrderLine, PurchaseOrder, GoodsReceiptLine, PurchaseInvoiceLine, PurchaseInvoice, DisbursementPayment.
Download SQL

Duplicate Payment Reference Review​

Search for duplicate disbursement references and duplicate supplier invoice numbers.

Output
One row per duplicate-pattern candidate.
Main tables
DisbursementPayment, PurchaseInvoice, Supplier.
Download SQL
Anomaly coverage

Review focus: AP payment review

Expected anomaly types: duplicate_vendor_payment_reference, duplicate_supplier_invoice_number

Trace flagged rows to their source documents before concluding that a control failed.

Duplicate AP Reference Detail Review​

Review detailed duplicate AP reference patterns by supplier, document, and amount.

Output
One row per duplicated AP document reference.
Main tables
PurchaseInvoice, DisbursementPayment, Supplier.
Download SQL
Anomaly coverage

Review focus: AP duplicate-reference review

Expected anomaly types: duplicate_vendor_payment_reference, duplicate_supplier_invoice_number

Trace flagged rows to their source documents before concluding that a control failed.

Item Master Completeness Review​

Review whether item-master rows carry the expected catalog attributes for their item group.

Output
One row per item with one or more missing expected attributes.
Main tables
Item.
Download SQL
Anomaly coverage

Review focus: item-master completeness review

Expected anomaly types: missing_item_catalog_attribute

Trace flagged rows to their source documents before concluding that a control failed.

Discontinued or Prelaunch Item Activity Review​

Review operational activity that uses items before launch or after they are discontinued from the active catalog.

Output
One row per flagged activity line or work order.
Main tables
Item, SalesOrder, SalesOrderLine, PurchaseOrder, PurchaseOrderLine, WorkOrder, Shipment, ShipmentLine, SalesInvoice, SalesInvoiceLine.
Download SQL
Anomaly coverage

Review focus: item-lifecycle review

Expected anomaly types: discontinued_item_in_new_activity, prelaunch_item_in_new_activity

Trace flagged rows to their source documents before concluding that a control failed.

Item Status Alignment Review​

Review current-state item master conflicts between lifecycle status and active-flag logic.

Output
One row per item with a current-state lifecycle or active-status conflict.
Main tables
Item.
Download SQL
Anomaly coverage

Review focus: item-status alignment review

Expected anomaly types: item_status_alignment_conflict

Trace flagged rows to their source documents before concluding that a control failed.

Inactive or Stale Inventory Policy Review​

Review active inventory items that lack a current active policy or carry inactive policy rows.

Output
One row per policy exception.
Main tables
InventoryPolicy, Item, Warehouse, Employee.
Download SQL
Anomaly coverage

Review focus: inventory-policy review

Expected anomaly types: inactive_policy_for_active_item

Trace flagged rows to their source documents before concluding that a control failed.

Recommendation Converted After Need by Date Review​

Review planning recommendations that converted after their need-by date.

Output
One row per late conversion.
Main tables
SupplyPlanRecommendation, PurchaseRequisition, WorkOrder, Item.
Download SQL
Anomaly coverage

Review focus: recommendation-timing review

Expected anomaly types: late_recommendation_conversion

Trace flagged rows to their source documents before concluding that a control failed.

P2P Invoice Line Trace Receipt vs Accrual​

Separate receipt-matched supplier invoice lines from accrual-linked service-settlement lines.

Output
One row per purchase invoice line.
Main tables
PurchaseInvoiceLine, PurchaseInvoice, PurchaseOrderLine, PurchaseRequisition, GoodsReceiptLine, JournalEntry, Supplier, Item.
Download SQL

P2P Accrual Journal Invoice Payment GL Trace​

Trace an accrual-linked supplier invoice line from accrual journal through invoice clearing and disbursement postings.

Output
One row per accrual-linked purchase invoice line.
Main tables
JournalEntry, PurchaseInvoiceLine, PurchaseInvoice, DisbursementPayment, GLEntry, Account, Supplier, Item.
Download SQL

Production and Planning​

Connect forecasts, material requirements, capacity, work orders, manufacturing cost, and production-control evidence.

Connect the evidence: Manufacturing · Production tables · Manufacturing cost practice · Production cost case

Interpret these results
  • Manufacturing-equipment depreciation debits 1090 Manufacturing Cost Clearing, so it belongs in manufacturing-cost interpretation rather than warehouse or office operating expense review.
  • The dataset includes a weekly planning layer, so students can compare forecast, policy, recommendation, and rough-cut capacity pressure before execution starts.
  • The current manufacturing model is still a foundation. It supports operations, labor, and contribution-margin analysis without switching inventory valuation to actual cost.
  • Demand-planning analysis is most useful when students connect DemandForecast, InventoryPolicy, SupplyPlanRecommendation, MaterialRequirementPlan, and RoughCutCapacityPlan in one analytical path.

Manufacturing WIP Clearing Variance​

Review manufacturing-related ledger movement by period.

Output
One row per fiscal period.
Main tables
GLEntry, Account, WorkOrderClose.
Download SQL

Manufacturing Cost Component Bridge​

Bridge manufacturing material, conversion, and variance activity by period.

Output
One row per month with operational cost components and ledger-account impact.
Main tables
MaterialIssue, MaterialIssueLine, ProductionCompletion, ProductionCompletionLine, WorkOrderClose, GLEntry, Account.
Download SQL

Forecast vs Actual Demand by Week Item Group Collection Lifecycle​

Compare weekly forecast demand to realized sales-order demand by item family.

Output
One row per week and item-family slice.
Main tables
DemandForecast, SalesOrder, SalesOrderLine, Item.
Download SQL

Recommendation Conversion by Type Priority Planner​

Review how planning recommendations convert into requisitions and work orders by planner and priority.

Output
One row per recommendation grouping.
Main tables
SupplyPlanRecommendation, Employee.
Download SQL

BOM Standard Cost Rollup​

Compare manufactured-item standard cost to BOM material plus standard conversion cost.

Output
One row per manufactured finished good.
Main tables
Item, BillOfMaterial, BillOfMaterialLine.
Download SQL

Work Order Throughput by Month​

Summarize released, completed, and closed work-order activity by month.

Output
One row per release month.
Main tables
WorkOrder, ProductionCompletionLine, WorkOrderClose.
Download SQL

Material Usage and Scrap Review​

Compare BOM-based expected component usage to actual issued material by work order.

Output
One row per work order component.
Main tables
WorkOrder, BillOfMaterialLine, MaterialIssue, MaterialIssueLine, Item.
Download SQL

Production Completion and FG Availability​

Review manufactured finished-goods activity by month, warehouse, and item group.

Output
One row per fiscal period, warehouse, and item group.
Main tables
ProductionCompletion, ProductionCompletionLine, Shipment, ShipmentLine, SalesReturn, SalesReturnLine, WorkOrder, Warehouse, Item.
Download SQL

Manufacturing Variance by Month Item Group​

Review manufacturing variance by month, item group, and warehouse.

Output
One row per close month, warehouse, and item group.
Main tables
WorkOrderClose, WorkOrder, Item, Warehouse.
Download SQL

Direct Labor by Work Order and Employee Class​

Review direct labor hours and cost by work order, employee title, and pay class.

Output
One row per work order and employee class grouping.
Main tables
LaborTimeEntry, Employee, WorkOrder, Item, PayrollPeriod.
Download SQL

Unit Cost Bridge Dm Dl Varoh Fixedoh​

Show the standard unit-cost bridge for manufactured finished goods.

Output
One row per manufactured item.
Main tables
Item.
Download SQL

Labor Efficiency and Rate Variance​

Review direct labor rate and efficiency variance by work order.

Output
One row per work order with direct labor activity.
Main tables
LaborTimeEntry, WorkOrder, Item, ProductionCompletionLine.
Download SQL

Routing Master Review​

Review the routing design assigned to manufactured items.

Output
One row per manufactured item and routing operation.
Main tables
Item, Routing, RoutingOperation, WorkCenter.
Download SQL

Work Center Activity and Operation Hours​

Compare operation-level direct labor and work-center activity by month.

Output
One row per month, work center, and operation code.
Main tables
WorkOrderOperation, RoutingOperation, WorkCenter, LaborTimeEntry, WorkOrder, Item.
Download SQL

Daily Load vs Capacity​

Compare daily scheduled load against available work-center capacity.

Output
One row per work center and calendar date.
Main tables
WorkCenterCalendar, WorkOrderOperationSchedule, WorkCenter.
Download SQL

Monthly Work Center Utilization​

Summarize monthly work-center utilization and identify recurring bottlenecks.

Output
One row per month and work center.
Main tables
WorkCenterCalendar, WorkOrderOperationSchedule, WorkCenter.
Download SQL

Operation Delay and Bottleneck Review​

Review planned versus actual operation timing and highlight bottleneck-prone work centers.

Output
One row per work-order operation.
Main tables
WorkOrderOperation, RoutingOperation, WorkCenter, WorkOrder, Item.
Download SQL

Backlog Aging by Work Center​

Show open operation backlog and how far those operations sit past their planned end dates.

Output
One row per open work-order operation.
Main tables
WorkOrderOperation, WorkOrder, RoutingOperation, WorkCenter.
Download SQL

Rough Cut Capacity Load vs Available Hours​

Compare rough-cut planned load to available hours by work center and week.

Output
One row per work center and week.
Main tables
RoughCutCapacityPlan, WorkCenter.
Download SQL

Forecast Error and Bias by Collection Style Family​

Summarize forecast error and bias by collection and style family.

Output
One row per collection and style family.
Main tables
DemandForecast, SalesOrder, SalesOrderLine, Item.
Download SQL

Missing BOM or Supply Mode Conflict​

Identify supply-mode and BOM-structure conflicts.

Output
One row per item with a potential BOM or supply-mode issue.
Main tables
Item, BillOfMaterial.
Download SQL

Over Issue and Open WIP Review​

Review work orders with possible material over-issue or unusually old open WIP.

Output
One row per work order needing review.
Main tables
WorkOrder, BillOfMaterialLine, MaterialIssue, MaterialIssueLine, ProductionCompletionLine, WorkOrderClose.
Download SQL

Work Order Close Timing Review​

Review the timing between the last production activity and work-order close.

Output
One row per work order with delayed or missing close behavior.
Main tables
WorkOrder, MaterialIssue, ProductionCompletion, WorkOrderClose.
Download SQL

Review routing, work-order operation, and direct-labor linkage completeness.

Output
One row per potential routing or operation-link exception.
Main tables
Item, Routing, WorkOrder, WorkOrderOperation, LaborTimeEntry.
Download SQL
Anomaly coverage

Review focus: manufacturing linkage review

Expected anomaly types: missing_work_order_operations, invalid_direct_labor_operation_link

Trace flagged rows to their source documents before concluding that a control failed.

Operation Sequence and Final Completion Review​

Review operation sequencing and final completion timing for manufactured work orders.

Output
One row per potential sequencing or completion-timing exception.
Main tables
WorkOrder, WorkOrderOperation, RoutingOperation.
Download SQL
Anomaly coverage

Review focus: operation sequence review

Expected anomaly types: overlapping_operation_sequence, completion_before_operation_end

Trace flagged rows to their source documents before concluding that a control failed.

Schedule on Nonworking Day Review​

Detect work-center schedule rows that fall on non-working days.

Output
One row per suspicious schedule row.
Main tables
WorkOrderOperationSchedule, WorkCenterCalendar, WorkCenter, WorkOrderOperation, WorkOrder.
Download SQL
Anomaly coverage

Review focus: capacity-calendar review

Expected anomaly types: scheduled_on_nonworking_day

Trace flagged rows to their source documents before concluding that a control failed.

Over Capacity Day Review​

Detect work-center days where scheduled hours exceed available capacity.

Output
One row per over-capacity work-center day.
Main tables
WorkOrderOperationSchedule, WorkCenterCalendar, WorkCenter.
Download SQL
Anomaly coverage

Review focus: capacity review

Expected anomaly types: overbooked_work_center_day

Trace flagged rows to their source documents before concluding that a control failed.

Completion Before Scheduled Operation End​

Detect work orders completed before their final scheduled operation window ends.

Output
One row per suspicious work order.
Main tables
WorkOrder, WorkOrderOperation, RoutingOperation.
Download SQL
Anomaly coverage

Review focus: schedule-completion review

Expected anomaly types: completion_before_operation_end

Trace flagged rows to their source documents before concluding that a control failed.

Labor Outside Scheduled Operation Window Review​

Find direct labor booked outside the scheduled or actual operation window.

Output
One row per potential labor-window exception.
Main tables
LaborTimeEntry, TimeClockEntry, WorkOrderOperation, WorkOrder, RoutingOperation, Employee.
Download SQL
Anomaly coverage

Review focus: labor timing review

Expected anomaly types: labor_after_operation_close

Trace flagged rows to their source documents before concluding that a control failed.

Forecast Approval and Override Review​

Review weekly forecasts that lack approval or show unusually large overrides.

Output
One row per flagged forecast row.
Main tables
DemandForecast, Item, Employee.
Download SQL
Anomaly coverage

Review focus: forecast-governance review

Expected anomaly types: missing_forecast_approval, forecast_override_outlier

Trace flagged rows to their source documents before concluding that a control failed.

Requisitions and Work Orders without Planning Support​

Review replenishment documents that lack planning support.

Output
One row per unsupported requisition or work order.
Main tables
PurchaseRequisition, WorkOrder, Item.
Download SQL
Anomaly coverage

Review focus: planning-support review

Expected anomaly types: purchase_requisition_without_plan, work_order_without_plan

Trace flagged rows to their source documents before concluding that a control failed.

Discontinued or Prelaunch Planning Activity Review​

Review planning activity that occurs before launch or against discontinued inactive items.

Output
One row per planning-timing exception.
Main tables
DemandForecast, SupplyPlanRecommendation, PurchaseRequisition, WorkOrder, Item.
Download SQL
Anomaly coverage

Review focus: planning-lifecycle review

Expected anomaly types: inactive_policy_for_active_item, missing_forecast_approval, forecast_override_outlier, late_recommendation_conversion

Trace flagged rows to their source documents before concluding that a control failed.

Released Work Orders Due without Actual Start Review​

Review released work orders that are already due in-horizon but still have no actual start recorded.

Output
One row per released work order with no actual start.
Main tables
WorkOrder, WorkOrderOperation, WorkOrderOperationSchedule, WorkCenter, Item.
Download SQL

Released Work Orders Due without Actual Start Summary​

Summarize the released-work-order-without-actual-start pattern by release month, due month, and first scheduled work center.

Output
Aggregated rows by due month, release month, and first scheduled work center.
Main tables
WorkOrder, WorkOrderOperation, WorkOrderOperationSchedule, WorkCenter.
Download SQL

Manufacturing Work Order Operation Trace​

Trace one work order through operation sequence, schedule rows, approved clocks, and direct labor support.

Output
One row per work-order operation.
Main tables
WorkOrderOperation, WorkOrder, Item, RoutingOperation, WorkCenter, WorkOrderOperationSchedule, TimeClockEntry, LaborTimeEntry.
Download SQL

Manufacturing Work Order Close GL Trace​

Trace one work order from material issue through completion, close variance, and supporting GL postings.

Output
One row per work order.
Main tables
WorkOrder, MaterialIssue, MaterialIssueLine, ProductionCompletion, ProductionCompletionLine, WorkOrderClose, GLEntry, Account, Item.
Download SQL

People and Payroll​

Follow workforce structure, rosters, punches, approved time, labor allocation, payroll, and employee-validity controls.

Connect the evidence: Payroll · Payroll and time tables · Hours-to-payroll practice · Staffing and payroll case

Interpret these results
  • Payroll register activity records liabilities first; cash leaves through payroll payments and remittances later.
  • The richer employee master now supports job-family, job-level, and people-cost review without requiring a separate HR-history subledger.
  • The employee master supports work location, job family, job level, employment status, and current-state active analysis without a separate HR-history model.
  • Work location and cost center answer different questions. Use both when students compare workforce structure to payroll or labor usage.
  • Workforce-planning analysis compares rostered hours, approved worked hours, absences, raw punches, and overtime approvals while retaining their different source grains.
  • Employee-master review should distinguish current-state IsActive from historical validity driven by HireDate and TerminationDate.

Payroll Liability Rollforward​

Review payroll-liability movement by month and liability account.

Output
One row per fiscal period and payroll-liability account.
Main tables
GLEntry, Account.
Download SQL

Gross to Net Payroll Review​

Review the gross-to-net payroll bridge by employee and pay period.

Output
One row per employee payroll register.
Main tables
PayrollPeriod, PayrollRegister, Employee, CostCenter.
Download SQL

Payroll Cash Payments and Remittances​

Compare payroll cash outflows between employee payments and liability remittances.

Output
One row per fiscal period.
Main tables
PayrollPayment, PayrollRegister, PayrollPeriod, PayrollLiabilityRemittance.
Download SQL

Hourly Payroll Hours to Paid Earnings Bridge​

Bridge approved hourly time-clock hours to payroll earnings and payment timing.

Output
One row per hourly employee payroll register.
Main tables
TimeClockEntry, LaborTimeEntry, PayrollRegister, PayrollRegisterLine, PayrollPayment, PayrollPeriod, Employee.
Download SQL

Payroll Expense Mix by Cost Center and Pay Class​

Review payroll expense mix by cost center and pay class.

Output
One row per pay month, cost center, and pay class.
Main tables
PayrollRegister, PayrollPeriod, Employee, CostCenter.
Download SQL

Payroll and People Cost Mix by Cost Center Job Family Level​

Review payroll cost mix by cost center, job family, job level, and pay class.

Output
One row per cost center and workforce grouping.
Main tables
PayrollRegister, Employee, CostCenter.
Download SQL

Shift Adherence and Overtime by Work Center​

Review shift adherence and overtime concentration by work center and month.

Output
One row per month, shift, and work-center grouping.
Main tables
TimeClockEntry, ShiftDefinition, EmployeeShiftAssignment, WorkCenter, Employee.
Download SQL

Approved Clock Hours vs Labor Allocation​

Compare approved direct-labor time-clock hours to the labor allocation recorded against work-order operations.

Output
One row per month, work center, and routing operation.
Main tables
TimeClockEntry, LaborTimeEntry, WorkOrderOperation, RoutingOperation, WorkCenter, WorkOrder.
Download SQL

Paid Hours vs Productive Labor by Work Center​

Compare approved paid hours to productive labor allocation by work center and month.

Output
One row per month and work center.
Main tables
TimeClockEntry, LaborTimeEntry, WorkOrderOperation, WorkCenter, ShiftDefinition.
Download SQL

Headcount by Cost Center Job Family Status​

Review workforce composition by cost center, job family, level, and employment status.

Output
One row per cost center, job family, job level, and employment status.
Main tables
Employee, CostCenter.
Download SQL

Labor and Headcount by Work Location Job Family Cost Center​

Compare headcount, payroll cost, approved time, and direct labor by work location, job family, and cost center.

Output
One row per work location, cost center, and job family grouping.
Main tables
Employee, CostCenter, PayrollRegister, TimeClockEntry, LaborTimeEntry.
Download SQL

Staffing Coverage vs Work Center Planned Load​

Compare rostered staffing hours to scheduled work-center load at the daily grain with monthly rollup context.

Output
One row per work center and calendar day with rostered hours, scheduled load hours, and the coverage gap.
Main tables
EmployeeShiftRoster, WorkOrderOperationSchedule, WorkCenter.
Download SQL

Rostered vs Worked Hours by Work Center Shift​

Compare planned rostered hours to approved worked hours by work center, shift, and month.

Output
One row per month, work center, and shift with planned hours, worked hours, and variance.
Main tables
EmployeeShiftRoster, TimeClockEntry, ShiftDefinition, WorkCenter.
Download SQL

Absence Rate by Work Location Job Family Month​

Measure absence rates by month, work location, and job family.

Output
One row per month, work location, and job family with absence hours, rostered hours, and absence rate.
Main tables
EmployeeAbsence, EmployeeShiftRoster, Employee.
Download SQL

Overtime Approval Coverage and Concentration​

Review how overtime hours concentrate by work center and how consistently those hours carry overtime approvals.

Output
One row per month and work center with overtime totals and approval coverage metrics.
Main tables
TimeClockEntry, OvertimeApproval, WorkCenter.
Download SQL

Punch to Pay Bridge for Hourly Workers​

Trace hourly payroll from raw punch counts through approved time, labor allocation, and gross pay.

Output
One row per hourly employee and payroll period.
Main tables
TimeClockPunch, TimeClockEntry, LaborTimeEntry, PayrollRegister, Employee.
Download SQL

Late Arrival Early Departure by Shift Department​

Measure late-arrival and early-departure patterns by shift and department.

Output
One row per month, department, and shift.
Main tables
EmployeeShiftRoster, TimeClockEntry, ShiftDefinition.
Download SQL

Payroll Control Review​

Review common payroll-control exceptions around approvals, payments, and liabilities.

Output
One row per potential payroll-control issue.
Main tables
PayrollRegister, PayrollPayment, PayrollPeriod, Employee.
Download SQL
Anomaly coverage

Review focus: payroll settlement review

Expected anomaly types: missing_payroll_payment, payroll_payment_before_approval

Trace flagged rows to their source documents before concluding that a control failed.

Labor Time After Close and Paid without Time​

Review labor-control exceptions around time posted after work-order close and hourly employees paid without time.

Output
One row per potential control issue.
Main tables
LaborTimeEntry, WorkOrder, PayrollRegister, PayrollPeriod, Employee.
Download SQL
Anomaly coverage

Review focus: labor support review

Expected anomaly types: labor_after_operation_close, paid_without_clock

Trace flagged rows to their source documents before concluding that a control failed.

Time Clock Exceptions by Employee Supervisor Work Center​

Review time-clock and attendance exceptions by employee, supervisor, and work center.

Output
One row per attendance exception.
Main tables
AttendanceException, TimeClockEntry, ShiftDefinition, Employee, WorkCenter.
Download SQL
Anomaly coverage

Review focus: attendance exception review

Expected anomaly types: missing_clock_out, duplicate_time_clock_day, off_shift_clocking

Trace flagged rows to their source documents before concluding that a control failed.

Paid without Clock and Clock without Pay Review​

Review hourly payroll lines without approved time-clock support and approved time clocks that do not feed hourly pay.

Output
One row per potential payroll-support exception.
Main tables
PayrollRegister, PayrollRegisterLine, LaborTimeEntry, TimeClockEntry, PayrollPeriod, Employee.
Download SQL
Anomaly coverage

Review focus: payroll-time reconciliation

Expected anomaly types: paid_without_clock, missing_clock_out

Trace flagged rows to their source documents before concluding that a control failed.

Time Clock Payroll Labor Bridge Review​

Reconcile approved time clocks, labor allocation, and hourly payroll hours.

Output
One row per employee and payroll period with an exception-oriented bridge result.
Main tables
TimeClockEntry, LaborTimeEntry, PayrollRegister, PayrollRegisterLine, PayrollPeriod, Employee.
Download SQL
Anomaly coverage

Review focus: workforce bridge review

Expected anomaly types: paid_without_clock, missing_clock_out, labor_after_operation_close

Trace flagged rows to their source documents before concluding that a control failed.

Terminated Employee Activity Review​

Detect post-termination payroll, approval, and labor activity.

Output
One row per employee activity that occurs after the employee's termination date.
Main tables
Employee, PayrollRegister, TimeClockEntry, LaborTimeEntry, PurchaseOrder, JournalEntry.
Download SQL
Anomaly coverage

Review focus: employment validity review

Expected anomaly types: terminated_employee_on_payroll, terminated_employee_approval, inactive_employee_time_or_labor

Trace flagged rows to their source documents before concluding that a control failed.

Terminated Employee Activity Rollup by Process Area​

Roll up post-termination activity by process area so students can see where workforce-control failures appear.

Output
One row per process area and source table with counts of post-termination activity.
Main tables
Employee, PayrollRegister, TimeClockEntry, LaborTimeEntry, PurchaseRequisition, PurchaseOrder, PurchaseInvoice, CreditMemo, CustomerRefund, JournalEntry.
Download SQL
Anomaly coverage

Review focus: employment rollup review

Expected anomaly types: terminated_employee_on_payroll, terminated_employee_approval, inactive_employee_time_or_labor

Trace flagged rows to their source documents before concluding that a control failed.

Current State Employee Assignment Review​

Review current-state master assignments that still point to inactive or terminated employees.

Output
One row per current-state assignment linked to an inactive or terminated employee.
Main tables
CostCenter, Warehouse, WorkCenter, Customer, Employee.
Download SQL
Anomaly coverage

Review focus: assignment ownership review

Expected anomaly types: inactive_employee_current_assignment

Trace flagged rows to their source documents before concluding that a control failed.

Scheduled without Punch and Punch without Schedule Review​

Find rostered days with no punch activity and punch activity without a valid roster assignment.

Output
One row per flagged roster or punch event.
Main tables
EmployeeShiftRoster, TimeClockEntry, TimeClockPunch, Employee.
Download SQL
Anomaly coverage

Review focus: roster-punch review

Expected anomaly types: punch_without_roster, missing_final_punch

Trace flagged rows to their source documents before concluding that a control failed.

Overtime without Approval Review​

Identify approved worked overtime that is missing a linked overtime approval or exceeds the approved amount.

Output
One row per flagged time-clock entry.
Main tables
TimeClockEntry, OvertimeApproval, Employee, WorkCenter.
Download SQL
Anomaly coverage

Review focus: overtime approval review

Expected anomaly types: overtime_without_approval

Trace flagged rows to their source documents before concluding that a control failed.

Absence with Worked Time Review​

Find rostered absences that still carry time summaries or raw punch activity.

Output
One row per conflicting absence record.
Main tables
EmployeeAbsence, EmployeeShiftRoster, TimeClockEntry, TimeClockPunch, Employee.
Download SQL
Anomaly coverage

Review focus: absence-time conflict review

Expected anomaly types: absence_with_worked_time

Trace flagged rows to their source documents before concluding that a control failed.

Overlapping or Incomplete Punch Review​

Detect incomplete punch sets and punch sequences with invalid ordering.

Output
One row per flagged time-clock entry.
Main tables
TimeClockPunch, TimeClockEntry, Employee.
Download SQL
Anomaly coverage

Review focus: punch-sequence review

Expected anomaly types: missing_final_punch, overlapping_punch_sequence

Trace flagged rows to their source documents before concluding that a control failed.

Roster After Termination Review​

Review roster rows scheduled after an employee's termination date.

Output
One row per invalid rostered day.
Main tables
EmployeeShiftRoster, Employee, TimeClockEntry, TimeClockPunch.
Download SQL
Anomaly coverage

Review focus: post-termination roster review

Expected anomaly types: roster_after_termination

Trace flagged rows to their source documents before concluding that a control failed.

Controls and Exceptions​

Review cross-process approvals, role assignments, authority limits, segregation of duties, and broad anomaly screening.

Connect the evidence: Process responsibilities and controls · Source traceability · BPMN and controls workshop · Exceptions and controls case

Interpret these results
  • The published default SQLite is anomaly-enriched when anomaly_mode is standard, so it is useful for teaching comparisons but not as the clean baseline for database-integrity reconciliation.
  • Exception-oriented queries are designed to return reviewable control patterns from the published dataset.
  • The GL remains balanced even when audit queries identify exceptions.
  • Source-table tracing should remain the final step in explaining each exception pattern.

Approval and SOD Review​

Review basic approval and segregation-of-duties conditions across major document types.

Output
One row per potentially suspicious document.
Main tables
PurchaseRequisition, PurchaseOrder, PurchaseInvoice, JournalEntry, Employee.
Download SQL

Potential Anomaly Review​

Run heuristic anomaly checks using currently implemented tables and fields.

Output
One row per potential anomaly candidate with a type, document reference, and explanatory detail.
Main tables
JournalEntry, PurchaseOrder, PurchaseRequisition, SalesInvoice, Shipment, DisbursementPayment.
Download SQL
Anomaly coverage

Review focus: broad control screening

Expected anomaly types: weekend_journal_entry, same_creator_approver, same_creator_approver_journal, missing_approval, invoice_before_shipment, duplicate_vendor_payment_reference

Trace flagged rows to their source documents before concluding that a control failed.

Approval Role Review by Org Position​

Review who is approving operational and accounting documents by role and organization position.

Output
One row per document family and approver role.
Main tables
Employee, PurchaseRequisition, PurchaseOrder, PurchaseInvoice, CreditMemo, CustomerRefund, JournalEntry, PayrollRegister.
Download SQL

Executive Role Uniqueness and Control Assignment Review​

Review whether key executive and control-owner roles are unique and where those role holders appear in current-state assignments and approvals.

Output
One row per key role holder, with role counts and control-assignment evidence.
Main tables
Employee, CostCenter, Warehouse, WorkCenter, PurchaseOrder, PurchaseInvoice, CreditMemo, CustomerRefund, JournalEntry, PayrollRegister.
Download SQL
Anomaly coverage

Review focus: executive-role review

Expected anomaly types: duplicate_executive_title_assignment

Trace flagged rows to their source documents before concluding that a control failed.

Approval Authority Review by Expected Role Family​

Compare expected approver role families to the roles that actually approve key document families.

Output
One row per document type and observed approver role family.
Main tables
Employee, PurchaseRequisition, PurchaseOrder, PurchaseInvoice, CreditMemo, CustomerRefund, JournalEntry, PayrollRegister.
Download SQL
Anomaly coverage

Review focus: approval-role review

Expected anomaly types: unexpected_role_family_approval

Trace flagged rows to their source documents before concluding that a control failed.

Approval Authority Limit Review​

Compare document approval amounts to approver limits and expected approver role families.

Output
One row per flagged approval event.
Main tables
PurchaseOrder, PurchaseInvoice, CreditMemo, CustomerRefund, JournalEntry, PayrollRegister, Employee.
Download SQL
Anomaly coverage

Review focus: authority-limit review

Expected anomaly types: approval_above_authority_limit, unexpected_role_family_approval

Trace flagged rows to their source documents before concluding that a control failed.

Search and topic selections are kept in the URL for classroom links and Back/Forward recovery. A direct query link reveals the destination even if the current filters would hide it. If SQL cannot load, use Retry SQL or download its original file.

Build Your Analysis​

For accounting SQL practice, begin with connecting records to examine join keys and row grain, then finding accounting entries to trace source documents into GLEntry. These lessons prepare you to adapt the catalog examples without treating every matching row as independent evidence.

Start with the SQL Guide for a first query or Learn and Practice for a focused evidence trace. The cases provide structured investigations and assessed outputs.

Before comparing results, define the fiscal period, source population, row grain, sign convention, and filters. Keep the source records unchanged. Align dates and populations, retain document keys, and distinguish an exception flag from a supported accounting or control conclusion. The Schema Reference and Posting Reference support the final source-to-ledger check.

Notice a mistake?Report an error on this page