DECLARE @SalesRepNum int = 36
DECLARE @MailTos varchar(600) = NULL

DECLARE @SQLString  varchar(2500)
DECLARE @SQLSubject varchar(100)
DECLARE @SQLMessage varchar(550)

SELECT [Customers].[name] AS [Customer],
    [WorkOrders].[repair_order_id] AS [Order Number],
    CONVERT(varchar(5), [WorkOrderLineItems].[workorder_id]) + '-' + CONVERT(varchar(3), [WorkOrderLineItems].[line_number]) AS [ADR Wo#],
    [WorkOrderLineItems].[part_id] AS [Order Part Number],
    [WorkScopes].[out_part_id] AS [Return Part Number],
    CONVERT(varchar(10), [WorkOrderLineItems].[date_received], 10) AS [Date Rec],
    CONVERT(varchar(10), [WorkOrderLineItems].[email_date], 10) AS [Email Date],
    CONVERT(varchar(10), [WorkOrderLineItems].[fax_date], 10) AS [Fax Date],
    CONVERT(varchar(10), [WorkOrderLineItems].[acknowledged_date], 10) AS [Ack Date],
    CONVERT(varchar(10), [WorkOrderLineItems].[revised_due_date], 10) AS [Est Date],
    CONVERT(varchar(10), [ShippingManifests].[when_shipped], 10) AS [Ship Date],
    [WorkOrderLineItems].[work_performed] AS [Work Performed]
--INTO #Temp1
FROM [WorkScopes]
  FULL OUTER JOIN
     [QCParts]
        ON [WorkScopes].[out_part_id] = [QCParts].[id]
  FULL OUTER JOIN [ShippingManifests] FULL OUTER JOIN [WorkOrderLineItems]
        ON [ShippingManifests].[shipper_id] = [WorkOrderLineItems].[shipper_id]
        ON [WorkScopes].[work_order_id] = [WorkOrderLineItems].[workorder_id]
           AND [WorkScopes].[id] = [WorkOrderLineItems].[line_number]
  FULL OUTER JOIN [WorkOrders] FULL OUTER JOIN  [Customers]
     ON [WorkOrders].[customer_id] = [Customers].[id]
     ON [WorkOrderLineItems].[workorder_id] = [WorkOrders].[id]
  LEFT JOIN [Invoices] ON [Invoices].[id] =  [WorkOrderLineItems].[invoice_id]
WHERE [Customers].[sales_representative_id] =  + CONVERT(varchar(4), @SalesRepNum)
    AND [Invoices].[id] IN (
        SELECT [me].[id]
          FROM [Invoices] [me]
          LEFT JOIN [AccountsReceivableDisbursement] [disbursements]
            ON [disbursements].[invoice_id] = [me].[id]
        WHERE [disbursements].[adjust] = '0' OR [disbursements].[adjust] IS NULL
        GROUP BY [me].[id], [disbursements].[net_price]
        HAVING SUM( disbursements.amount_received ) < disbursements.net_price
                     OR COUNT( disbursements.invoice_id ) = 0
       )
ORDER BY [Customers].[name], [WorkOrders].[repair_order_id], [WorkOrderLineItems].[workorder_id], [WorkOrderLineItems].[line_number]