ALTER PROCEDURE [dbo].[InvoicesSelectOutstanding] >>( >> @crt_date date >>) >>AS >> SET NOCOUNT ON; >>;WITH cte as >> (SELECT ivd_invfk, SUM(ivd_amount) as inv_total FROM InvoiceDetails >>WHERE ivd_invfk NOT IN (SELECT crt_invfk >> FROM CashReceipts >> INNER JOIN Deposits on crt_depfk = dep_pk >> WHERE dep_date <= @crt_date >> UNION ALL >> SELECT rdz_invfk >> FROM receiptdetailszipx >> INNER JOIN Receipts ON rdz_rctfk = rct_pk >> WHERE rct_date <= @crt_date >> UNION ALL >> SELECT rdi_iinfk >> FROM ReceiptDetailsInbound >> INNER JOIN Receipts ON rdi_rctfk = rct_pk >> WHERE rct_date <= @crt_date >> UNION ALL >> SELECT rdo_oinfk >> FROM ReceiptDetailsOutbound >> INNER JOIN Receipts ON rdo_rctfk = rct_pk >> WHERE rct_date <= @crt_date >> UNION ALL >> SELECT rda_cwbfk >> FROM ReceiptDetailsAWBs >> INNER JOIN Receipts ON rda_rctfk = rct_pk >> WHERE rct_date <= @crt_date >> ) >> GROUP BY ivd_invfk ) >> SELECT [inv_awbfk], >> [inv_awbnumber], >> [inv_cancelled], >> [inv_ccufk], >> [inv_consignee], >> [inv_cusfk], >> [inv_cusnumber], >> [inv_cwbfk], >> [inv_date], >> [inv_goodsvalue], >> [inv_insurance], >> [inv_number], >> [inv_pieces], >> [inv_pk], >> [inv_printed], >> [inv_shpfk], >> [inv_type], >> [inv_weight] >> ,cus_company >> ,cus_lastname >> ,cus_firstname >> ,cus_number >> ,cnt_code >> ,cte.inv_total >> ,awb_number >> ,asatdate = @crt_date >> FROM [dbo].[Invoices] >> INNER JOIN cte on invoices.inv_pk = cte.ivd_invfk >> INNER JOIN Customers on inv_cusfk = cus_pk >> INNER JOIN AirWayBills on inv_awbfk = awb_pk >> INNER JOIN Countries ON cus_cntfk = cnt_pk >> WHERE inv_date <= @crt_date >> AND inv_printed = 1 >> AND inv_cancelled is null >>-- AND cus_credit = 1 >> ORDER BY cus_company>>