The Total Non Recurring Billed amount on the job sales summary screen can often become out of sync with the actual amount that has been billed on job invoices. The following script can be run to force the system to recalculate the Total Non Recurring Billed amount to match the current job invoices.
/* This will be used to Update Job Non recurring Billed Amount on a Sales Summary */
-- Usually applicable when the job shows the wrong Non recurring Balance to Bill
--The Actual query starts from below.
declare @job_code nvarchar(25) = '1234-1'; ---> Enter the Job code here
update j set Billed_Amount = Isnull(ba.billed_Amount, 0)
from oe_job j
Outer Apply (select sum (i.Amount) [billed_Amount]
from ar_invoice_item i
join AR_Invoice iv on iv.Invoice_Id = i.Invoice_Id
where iv.job_id = j.job_id
and i.job_Recurring_id = 1
) ba
where job_code = @job_code
and Master_Job_Id = 1
and j.Job_Status_Id <> 2
and j.job_id > 1
