CREATE view [dbo].[v_SalesView]
As
SELECT	S.surfaceItemId,
		S.dateOfService, 
		S.name, 
		S.surname, 
		S.itemType,  
		S.invoiceNo,
		S.receiptNo, 
		S.reference,
		CASE S.itemType 
			WHEN 'PM' THEN (Select Top 1 ISNULL(Nullif(saleRCTPrefix, ''), 'RCT') FROM pal_Setup) + 
				ISNULL(REPLICATE('0', (Select Top 1 ISNULL(saleRCTNumLength, 0) FROM pal_Setup) - (LEN(RTRIM(S.receiptNo)) + LEN((Select Top 1 ISNULL(Nullif(saleRCTPrefix, ''), 'RCT') FROM pal_Setup)))), '') + RTRIM(S.receiptNo)	 
			WHEN 'QT' THEN (Select Top 1 ISNULL(Nullif(saleQTEPrefix, ''), 'QTE') FROM pal_Setup) + 
				ISNULL(REPLICATE('0', (Select Top 1 ISNULL(saleQTENumLength, 0) FROM pal_Setup) - (LEN(RTRIM(S.invoiceNo)) + LEN((Select Top 1 ISNULL(Nullif(saleQTEPrefix, ''), 'QTE') FROM pal_Setup)))), '') + RTRIM(S.invoiceNo)	 
			WHEN 'CN' THEN (Select Top 1 ISNULL(Nullif(saleCRNPrefix, ''), 'CRN') FROM pal_Setup) + 
				ISNULL(REPLICATE('0', (Select Top 1 ISNULL(saleCRNNumLength, 0) FROM pal_Setup) - (LEN(RTRIM(S.invoiceNo)) + LEN((Select Top 1 ISNULL(Nullif(saleCRNPrefix, ''), 'CRN') FROM pal_Setup)))), '') + RTRIM(S.invoiceNo)	 
			ELSE (Select Top 1 ISNULL(Nullif(saleINVPrefix, ''), 'INV') FROM pal_Setup) + 
				ISNULL(REPLICATE('0', (Select Top 1 ISNULL(saleINVNumLength, 0) FROM pal_Setup) - (LEN(RTRIM(S.invoiceNo)) + LEN((Select Top 1 ISNULL(Nullif(saleINVPrefix, ''), 'INV') FROM pal_Setup)))), '') + RTRIM(S.invoiceNo)	 
			END [Number],
		CASE S.itemType 
			WHEN 'QT' THEN 'Quote ' + (Select Top 1 ISNULL(Nullif(saleQTEPrefix, ''), 'QTE') FROM pal_Setup) + 
				ISNULL(REPLICATE('0', (Select Top 1 ISNULL(saleQTENumLength, 0) FROM pal_Setup) - (LEN(RTRIM(S.invoiceNo)) + LEN((Select Top 1 ISNULL(Nullif(saleQTEPrefix, ''), 'QTE') FROM pal_Setup)))), '') + RTRIM(S.invoiceNo) 
			WHEN 'TI' THEN 'Invoice '+ (Select Top 1 ISNULL(Nullif(saleINVPrefix, ''), 'INV') FROM pal_Setup) + 
				ISNULL(REPLICATE('0', (Select Top 1 ISNULL(saleINVNumLength, 0) FROM pal_Setup) - (LEN(RTRIM(S.invoiceNo)) + LEN((Select Top 1 ISNULL(Nullif(saleINVPrefix, ''), 'INV') FROM pal_Setup)))), '') + RTRIM(S.invoiceNo) +
				ISNULL((SELECT TOP 1 ' (' + (Select Top 1 ISNULL(Nullif(saleQTEPrefix, ''), 'QTE') FROM pal_Setup) + ISNULL(REPLICATE('0', (Select Top 1 ISNULL(saleINVNumLength, 0) FROM pal_Setup) - (LEN(RTRIM(a.invoiceNo)) + LEN((Select Top 1 ISNULL(Nullif(saleQTEPrefix, ''), 'QTE') FROM pal_Setup)))), '') + RTRIM(a.invoiceNo) + ')' FROM pal_Sales a 
					WHERE a.itemType = 'QT' AND a.surfaceItemId = S.surfaceItemId AND a.isVisible = 1
					AND a.allocatedReference = CAST(S.invoiceNo AS VARCHAR(20))), '') 	 
			WHEN 'PM' THEN 'Receipt ' + (Select Top 1 ISNULL(Nullif(saleRCTPrefix, ''), 'RCT') FROM pal_Setup) + 
				ISNULL(REPLICATE('0', (Select Top 1 ISNULL(saleRCTNumLength, 0) FROM pal_Setup) - (LEN(RTRIM(S.receiptNo)) + LEN((Select Top 1 ISNULL(Nullif(saleRCTPrefix, ''), 'RCT') FROM pal_Setup)))), '') + RTRIM(S.receiptNo) +
				ISNULL((SELECT ' (' + (SELECT stuff((
					SELECT DISTINCT ',' + (Select Top 1 ISNULL(Nullif(saleINVPrefix, ''), 'INV') FROM pal_Setup) + ISNULL(REPLICATE('0',  (Select Top 1 ISNULL(saleINVNumLength, 0) FROM pal_Setup) - (LEN(RTRIM(a.invoiceNo)) + LEN((Select Top 1 ISNULL(Nullif(saleINVPrefix, ''), 'INV') FROM pal_Setup)))), '') + RTRIM(a.invoiceNo)  fROM pal_Sales a
					WHERE ',' + (SELECT allocatedReference FROM pal_Sales Where receiptNo = S.receiptNo AND itemType = 'PM') + ',' LIKE '%,' + CAST(recId AS varchar) + ',%'
					FOR XML PATH('')
					), 1, 1, ''))+ ')'),'')	
			WHEN 'CN' THEN 'Credit Note ' + (Select Top 1 ISNULL(Nullif(saleCRNPrefix, ''), 'CRN') FROM pal_Setup) +  
				ISNULL(REPLICATE('0', (Select Top 1 ISNULL(saleCRNNumLength, 0) FROM pal_Setup) - (LEN(RTRIM(invoiceNo)) + LEN((Select Top 1 ISNULL(Nullif(saleCRNPrefix, ''), 'CRN') FROM pal_Setup)))), '') + RTRIM(invoiceNo) +
				ISNULL((SELECT ' (' + (SELECT stuff((
					SELECT DISTINCT ',' + (Select Top 1 ISNULL(Nullif(saleINVPrefix, ''), 'INV') FROM pal_Setup) + ISNULL(REPLICATE('0', (Select Top 1 ISNULL(saleINVNumLength, 0) FROM pal_Setup) - (LEN(RTRIM(invoiceNo)) + LEN((Select Top 1 ISNULL(Nullif(saleINVPrefix, ''), 'INV') FROM pal_Setup)))), '') + RTRIM(invoiceNo)  fROM pal_Sales a
					WHERE ',' + (SELECT allocatedReference FROM pal_Sales Where invoiceNo = S.invoiceNo AND itemType = 'CN') + ',' LIKE '%,' + CAST(recId AS varchar) + ',%' AND a.ItemType = 'TI'						
					FOR XML PATH('')
					), 1, 1, ''))+ ')'),'')	
			ELSE ''
		END Display,
		CASE WHEN SUM(S.amount) < 0 THEN (SUM(S.amount * -1)) ELSE 0 END [Received],
		CASE WHEN SUM(S.amount) > 0 AND S.itemType <> 'QT' THEN SUM(S.amount) ELSE 0 END [Invoiced],
		CASE WHEN SUM(S.amount) > 0 AND S.itemType = 'QT' THEN SUM(S.amount) ELSE 0 END [Quoted],
		SUM(S.allocated) [allocated],
		CASE WHEN S.itemType = 'TI' THEN SUM (S.amount - S.allocated) ELSE 0 END AS [Due],
		SUM(CASE WHEN SUM(S.amount) > 0 AND S.itemType <> 'QT' THEN SUM(S.amount) ELSE 0 END 
			- CASE WHEN SUM(S.amount) < 0 THEN (SUM(S.amount * -1)) ELSE 0 END) 
			OVER (ORDER BY S.dateOfService DESC, CASE WHEN S.invoiceNo > 0 THEN CAST(S.invoiceNo AS VARCHAR(20)) ELSE S.itemType END ROWS UNBOUNDED PRECEDING) RunningBalance
FROM	pal_Sales S

WHERE S.isDraft = 0 

GROUP BY S.surfaceItemId, S.name, S.surname, S.itemType, S.dateOfService, S.invoiceNo, S.receiptNo, S.reference





GO


