Syteline Receiving By Purchase Orders Report
PUR需求在Syteline system 做一份报表，By采购单收货的记录报表。实现时需要从三个表关联[matltran]，[poitem] 和[vendaddr]去实现。
详细存储过程，参考如下代码：
代码
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_SyteLine_GetReceivingByPOReport]
(
@ConditionExpression nvarchar(3000)
)
AS
DECLARE @sql nvarchar(4000) = 'SELECT mt.[trans_num] AS [Trans Number],mt.[trans_date] AS [Trans Date],
p.[po_num] AS [PO Number],p.[po_line],mt.[whse],p.[Uf_Requestor],p.[stat],p.[po_vend_num],va.[name],
mt.[item] AS [Item],p.[description] AS [Description],mt.[qty] AS [Quantity],p.[u_m] AS [U/M],p.[unit_mat_cost_conv],p.[non_inv_acct] AS [Acct],p.[non_inv_acct_unit1] AS [UC1],p.[non_inv_acct_unit2] AS [UC2],p.[non_inv_acct_unit3] AS [UC3],p.[non_inv_acct_unit4] AS [UC4]
FROM [dbo].[matltran] mt
LEFT JOIN [dbo].[poitem] p ON (mt.[ref_num] = p.[po_num] AND mt.[ref_line_suf] = p.[po_line])
LEFT JOIN [dbo].[vendaddr] va ON (p.[po_vend_num] = va.[vend_num])
WHERE mt.[trans_type] =''R'''
IF LEN(@ConditionExpression) > 0
SELECT @sql += ' AND ' + @ConditionExpression
EXECUTE(@sql)
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_SyteLine_GetReceivingByPOReport]
(
@ConditionExpression nvarchar(3000)
)
AS
DECLARE @sql nvarchar(4000) = 'SELECT mt.[trans_num] AS [Trans Number],mt.[trans_date] AS [Trans Date],
p.[po_num] AS [PO Number],p.[po_line],mt.[whse],p.[Uf_Requestor],p.[stat],p.[po_vend_num],va.[name],
mt.[item] AS [Item],p.[description] AS [Description],mt.[qty] AS [Quantity],p.[u_m] AS [U/M],p.[unit_mat_cost_conv],p.[non_inv_acct] AS [Acct],p.[non_inv_acct_unit1] AS [UC1],p.[non_inv_acct_unit2] AS [UC2],p.[non_inv_acct_unit3] AS [UC3],p.[non_inv_acct_unit4] AS [UC4]
FROM [dbo].[matltran] mt
LEFT JOIN [dbo].[poitem] p ON (mt.[ref_num] = p.[po_num] AND mt.[ref_line_suf] = p.[po_line])
LEFT JOIN [dbo].[vendaddr] va ON (p.[po_vend_num] = va.[vend_num])
WHERE mt.[trans_type] =''R'''
IF LEN(@ConditionExpression) > 0
SELECT @sql += ' AND ' + @ConditionExpression
EXECUTE(@sql)
