Recently, a customer wanted to send the billing schedule for a sales order back to Salesforce to store on the Opportunity. The goal of the information was to show these billing schedules on future quotes.

One wrinkle in this scenario is they wanted the billing schedule to not include any sales tax or VAT. When they quote customers, they quote without tax and they didn't want to mix pre-tax and post-tax amounts on their quote documents.

In this case, the pattern was to wait for a sales order to be approved, collect the pre-tax billing schedule (and some other data), and place that data into a MuleSoft queue where MuleSoft would update the corresponding Salesforce opportunity.

The SuiteQL query wasn't immediately obvious at first but the data is in the transactionBilling table. In our case, we can start with a base query.

SELECT 
	*
FROM transactionBilling
WHERE transaction = 11374967

Here we can see columns like transactionLine, billCount, billDate, quantity, and billAmountForeign. The first impulse is to GROUP these by billDate and SUM the billAmountForeign column.

SELECT
	billDate,
	SUM(billAmountForeign)
FROM transactionBilling
WHERE transaction = 11374967
GROUP BY billDate	

When we compare this with the billing schedule shown on the sales order, we'll see that these dates and amounts match so this is the data we're looking for. Now to get the pre-tax amounts for each of these.

In NetSuite, billing schedules bill a portion of a sales order on specific dates. This is handled by billing a portion of the quantity on the original sales order. If the original quantity was 1000 and there are two installments in the billing schedule, then each invoice will bill a quantity of 500.

The transactionBilling table includes a billCount which shows the quantity which will be billed for each line item in each installment. The transactionLine value represents the line item's lineSequenceNumber. We can use this column to join our tables and get the item rate from the sales order.

To get the pre-tax amount, we can use the quantity from transactionBilling and the rate from transactionLine to do the math. Since quantities can be in partial units, we'll round to 2 decimal places.

SELECT
	tb.billDate as bill_date,
	round(sum(tb.billcount * tl.rate), 2) as bill_amount

FROM transactionBilling tb

INNER JOIN transactionLine tl 
	ON tl.transaction = tb.transaction
	AND tl.lineSequenceNumber = tb.transactionLine
	
WHERE tb.transaction = 11374967

GROUP BY tb.billDate

We now have the billing date and the pre-tax amount for each invoice. One more piece of information we need is the Salesforce opportunity ID. We want to make it easy for MuleSoft to update the correct opportunity without unnecessary API requests.

SELECT
	tb.billDate as bill_date,
	t.custbody_sf_opportunity_id AS opportunity_id,
	round(sum(tb.billcount * tl.rate), 2) as bill_amount

FROM transactionBilling tb

INNER JOIN transactionLine tl 
	ON tl.transaction = tb.transaction
	AND tl.lineSequenceNumber = tb.transactionLine
	
INNER JOIN transaction t 
	ON t.id = tl.transaction
	
WHERE tb.transaction = 11374967

GROUP BY tb.billDate, t.custbody_sf_opportunity_id

Now that we have the query, how do we operationalize it?

Remember, we want to send this information to MuleSoft when a transaction is approved, but we don't want users to wait for outbound HTTP requests when they update a record in NetSuite, so we'll build a JSON object and use a background process to send the data to MuleSoft.

{
	"salesOrderId": 11374967,
	"sfOpportunityId": "006Pc000009PDkrIAG",
	"billingSchedule": [
		{
			"billDate": "09/01/2026",
			"billAmount": 1879.82
		},
		{
			"billDate": "09/01/2027",
			"billAmount": 1879.82
		}
	]
}

New article series coming soon about how to build a generic background worker that easily offloads this kind of action and includes automatic retries when necessary.