Syteline Goods Receiving Note Report

在Syteline system中，默认Goods Receiving Note Report格式不适合自己需求，再加上添加了一些自定义字段，因此写了一个存储过程，能在SQL查询分析器得到需要结果。

 

CREATE PROCEDURE [dbo].[usp_SyteLine_GetGoodsReceivingNoteReport]
(
    @ConditionExpression nvarchar(3000)
)
AS 
DECLARE @sql nvarchar(4000) = 'SELECT gh.[Uf_rtnum] AS [RT Number],gl.[vend_num] AS [Vendor Number],v.[name] AS [Name],gh.[grn_num] AS [GRN Number],gl.[po_num] AS [PO Number],po.[item] AS [Item],i.[description] AS [Description],gl.[qty_rec] AS [Quantity],gl.[u_m] AS [U/M],gh.[grn_hdr_date] AS [Receiving Date],po.[Uf_Requestor] AS [Requestor]
FROM [dbo].[grn_line] gl 
LEFT JOIN [dbo].[grn_hdr] gh ON (gl.[vend_num] = gh.[vend_num]) AND (gl.[grn_num] = gh.[grn_num])
LEFT JOIN [dbo].[poitem] po ON ((gl.po_num = po.po_num) AND (gl.po_line = po.po_line))
LEFT JOIN [dbo].[item] i ON (i.[item] = po.[item])
LEFT JOIN [dbo].[vendaddr] v ON (v.[vend_num] = gl.[vend_num])'
IF LEN(@ConditionExpression) > 0
    SELECT @sql += ' WHERE ' + @ConditionExpression
EXECUTE(@sql)

GO