Trade Promotion Deduction Auto-Matching: Technical Implementation in NetSuite
The Problem
CPG companies selling through distributors like UNFI and KeHE receive deductions against invoices that correspond to trade promotions: promotional allowances, slotting fees, MCBs, and off-invoice discounts. Remittance data arrives with reason codes that must be matched against promotional accruals to close the loop on trade spend. Manual matching is time-intensive and unmatched deductions are often written off without investigation.
Why Saved Search Is Insufficient
NetSuite Saved Searches support only one level of joining. A deduction-to-accrual match requires joining across customers (including parent/child hierarchies), transactions, custom accrual records, and potentially item records. Saved Search cannot traverse this structure. Additionally, Saved Search has no mechanism for weighted multi-criteria scoring.
Solution Architecture
The solution uses a SuiteScript 2.x Map/Reduce script with SuiteQL queries executed via the N/query module. An optional AI extension integrates the Anthropic API for reason code interpretation.
getInputData Stage: Returns unmatched deductions from a custom record type. SuiteQL query executed via query.runSuiteQL(options) returns results as key-value pairs where each key is the deduction internal ID.
map Stage: For each deduction, executes a parameterized SuiteQL query against promotional accrual records. Calculates a weighted match score using:
Customer match (exact, parent hierarchy, or buying group)
Date overlap (invoice date within promotion window)
Amount proximity:
ABS(deduction_amount - accrual_balance) / accrual_balanceUPC/item intersection when line-level data exists
Promotion type match via LLM-normalized reason codes (if AI extension enabled)
Reason Code Normalization (Optional AI Extension): Distributor reason codes are often inconsistent (e.g., “PROMO 47”, “MKT ALLOW”, “TPR-Q3”, “SCAN DEAL 1542”). A RESTlet can call the Anthropic API to interpret each reason code and return a standardized promotion type (e.g., “Scan Promotion”, “Off-Invoice Allowance”, “Slotting Fee”). This normalized type is matched against promotion categories on accrual records, adding a weighted factor to the overall confidence score. Uninterpretable codes are flagged for manual review rather than guessed.
Candidates exceeding the confidence threshold are passed to reduce via context.write() with deduction ID as key.
reduce Stage: Receives all candidate matches for each deduction. Selects highest-scoring accrual or flags for manual review if multiple candidates score within a configurable delta.
Settlement process:
record.create()with typerecord.Type.CREDIT_MEMOgenerates the credit memorecord.transform()converts the original invoice (record.Type.INVOICE) to a customer payment (record.Type.CUSTOMER_PAYMENT)Credit application is marked on the ‘credit’ sublist using
selectLine(),setCurrentSublistValue()with field ‘apply’ set to true, andcommitLine()to save the lineDeduction and accrual custom records are updated to reflect the match
summarize Stage: Aggregates match statistics using context.output.iterator(). Writes metrics (match rate, confidence distribution, unmatched aging) to a custom analytics record.
Governance
Map/Reduce scripts have a 10,000 usage unit soft limit per job. When exceeded, the job yields and the system spawns a replacement job to continue processing. Individual function invocations have hard limits: 1,000 units for map, 5,000 units for reduce. Total script duration is unlimited, allowing large-scale matching (thousands of deductions against tens of thousands of accruals) to run to completion without manual intervention.
Deployment
Scheduled: Script Deployment configured to run nightly after bank file import
On-demand: Suitelet interface allows finance team to trigger matching after large remittance batches
Outcome
Deduction matching shifts from manual spreadsheet reconciliation to automated processing with exception-based review. Write-offs require documented justification. Closed-loop data connects promotional accruals to actual redemptions, enabling measurement of trade promotion effectiveness.