USE [PRTest]
GO
/****** Object: UserDefinedFunction [dbo].[FN_PRPrint] Script Date: 10/22/2010 13:52:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER FUNCTION [dbo].[FN_PRPrint]
(
@prno nvarchar(50)
)
RETURNS @CRE table
(
RID int IDENTITY(1,1) NOT NULL
,RW INT
,CompanyNameEN nvarchar(200)
,DeptCode nvarchar(10)
,HKAgency nvarchar(50)
,PRDate datetime
,NPPNO nvarchar(50)
,Category nvarchar(50)
,PRNO nvarchar(100)
,ABCCODE nvarchar(10)
,LineDesc nvarchar(500)
,LineUse nvarchar(200)
,VendorCode nvarchar(50)
,LineQty decimal(18,2)
,LinePrice decimal(18,4)
,Amount decimal(18,2)
,CurrencyCode nvarchar(10)
,ReqDate datetime
,ReqPay datetime
,Remark nvarchar(500)
)
AS
BEGIN
INSERT INTO @CRE(RW, CompanyNameEN, DeptCode, HKAgency, PRDate, NPPNO, CATEGORY, PRNO, ABCCode, LineDesc, LineUse, VendorCode, LineQty, LinePrice,Amount, CurrencyCode, ReqDate, ReqPay, Remark)
SELECT RW=(row_number()Over(partition by prno Order By PRLine)-1)/10, A.CompanyNameEN,A.DeptCode,A.HKAgency,A.PRDate,A.NPPNO,A.CATEGORY,A.PRNO,A.ABCCode,A.LineDesc,A.LineUse,ISNULL(B.VenDescCN,a.VendorCode) AS VendorCode,A.LineQty,A.LinePrice,Amount=CAST(Isnull(A.LineQty,0)*ISNULL(A.LinePrice,0) AS decimal(18,2)),A.CurrencyCode,A.ReqDate,A.ReqPay,A.Remark
FROM (
SELECT H.CompanyCode,H.HKAgency,CompanyNameEN=C.CompanyNameCN,H.NPPNO,H.CATEGORY, H.DeptCode, H.PRDate,H.PRNO, H.CurrencyCode,
H.ABCCode, H.Remark, D.PRLine, D.LineDesc, D.LineUse,
D.MoldNO, D.PartNO, d.VendorCode, D.LineQty, D.LinePrice,
D.ReqDate,D.ReqPay
FROM PRHeader H
LEFT JOIN PRDetail D ON H.PRNO = D.PRNO
INNER JOIN Company C ON C.CompanyCode=H.CompanyCode
) A
LEFT JOIN VENDOR B ON A.CompanyCode=B.CompanyCode And A.VendorCode=B.VenCode
WHERE PRNO=@PRNO
declare @sy int
declare @rw int
SELECT @sy=(10-max(rid)%10) from @CRE
SELECT @rw=max(rw) from @CRE
INSERT INTO @CRE(RW, CompanyNameEN, DeptCode, HKAgency, PRDate, NPPNO, CATEGORY, PRNO, ABCCode, LineDesc, LineUse, VendorCode, LineQty, LinePrice,Amount, CurrencyCode, ReqDate, ReqPay, Remark)
SELECT top (@sy) @rw,null,null,null,null,null,null,null,null,null,null,null,null,null,0,null,null,null,null From PRHeader
RETURN
END