How to Resolve Variances Between Open Sales Order Report and Saved Search Unbilled Amount

When a sales order line item is overbilled (Quantity Billed > Quantity), the Open Amount on the NetSuite Open Sales Order Report may not match the Unbilled Amount in a transaction saved search due to several factors. The Open Sales Order Report calculates the Open Amount based on Sales Orders Account, which excludes the tax of overbilled items. In a transaction search, the Unbilled Amount, when not specifically filtered for line-level detail, may include tax and can be influenced by the default mainline setting, leading to discrepancies. Additionally, by default, a transaction search uses the Per-Account Consolidated Exchange Rate, which can also contribute to variances.

To resolve the variance between the Unbilled Amount and the Open Sales Order Amount, configure the Transaction Saved Search with the following settings:nn

1. Under the Criteria Tab, set the following:n * Type = Sales Ordern * Mainline = Falsen * COGS Line = Falsen * Tax Line = Falsen * Shipping Line = Falsenn

2. Under the Results Tab, set the Consolidated Exchange Rate to Average.nn

3. Add the following fields to the Results tab, along with any other required fields:

   Field: Document NumbernSummary Type: GroupnLabel: Document Number

   Field: Formula (Currency)

   Summary Type: SumnLabel: Unbilled AmountnFormula:n

CASE WHEN {rate} IS NOT NULL THEN (CASE WHEN {quantity} > NVL({quantitybilled},0) THEN CASE WHEN ({taxamount} <> 0) THEN (({quantity}-{quantitybilled})*{rate})+(({taxamount}/{quantity})*({quantity}-{quantitybilled})) ELSE (({quantity}-{quantitybilled})*{rate}) END ELSE 0 END) ELSE CASE WHEN {quantity} > NVL({quantitybilled},0) THEN {amount} ELSE 0 END END