-- =============================================
-- Author:		Graham Rook
-- Create date: 25 July 2016
-- Description:	Get Sales Invoices that are still due
-- =============================================
CREATE PROCEDURE [dbo].[sp_GetSalesInvoicesToAllocate]
	-- Add the parameters for the stored procedure here
	@SurfaceItemID int = 0
AS
BEGIN
	SELECT	  
		invoiceNo,
		CONVERT(varchar(10), dateOfService,126) + '-INV' + CONVERT(varchar(50),invoiceNo) + '-Owed:' + CONVERT(varchar(50),SUM(amount-allocated)) AS Display
	 FROM pal_Sales
	 WHERE itemType = 'TI' AND surfaceItemId = CASE WHEN @SurfaceItemID = 0 THEN surfaceItemId ELSE @SurfaceItemID END
	 GROUP BY surfaceItemId, name, surname, itemType, dateOfService, invoiceNo, receiptNo
	 Having  SUM (amount - allocated) > 0
	 ORDER BY dateOfService DESC
END


