001.USE [TM_ERP]
002.GO
003./****** Object: StoredProcedure [dbo].[MyProcedure] Script Date: 6/14/2018 9:27:14 AM ******/
004.SET ANSI_NULLS ON
005.GO
006.SET QUOTED_IDENTIFIER ON
007.GO
008.
009.
010.
011.
012.
013.ALTER PROCEDURE [dbo].[MyProcedure]
014.
015. @StartDate datetime,
016. @ToDate datetime,
017. @FromSupplierCode nvarchar(20),
018. @ToSupplierCode nvarchar(20),
019. @OtherSideStatus bit,
020. @SideCode nvarchar(10),
021. @SupplierType nvarchar(1)
022.AS
023.BEGIN
024. IF @OtherSideStatus = 0
025. BEGIN
026. IF @SideCode IS NULL
027. BEGIN
028. SELECT sa.PoNo,
029. po.SupplierID,
030. (SELECT sup.SupplierName FROM
031. [dbo].[Suppliers] sup WHERE sup.SupplierID=po.SupplierID)
032. AS SupplierName,
033. sa.SupplierType,
034. sa.SupplierAssessSubjectID,
035. sa.Grade
036.
037.
038. FROM [dbo].[SupplierAssess] sa INNER JOIN
039. [dbo].[PurchaseOrders] po ON sa.PoNo = po.PoNo
040.
041.
042.
043. WHERE(po.SupplierID BETWEEN @FromSupplierCode AND @ToSupplierCode) AND
044. (sa.Grade = '5' OR sa.Grade = '4' OR sa.Grade = '3' OR sa.Grade = '2' OR sa.Grade = '1') AND
045. (sa.GrDocumentDate BETWEEN @StartDate AND @ToDate) AND
046. (po.SupplierAssessStatus = 1)AND
047. po.DocumentType = 'A' AND
048. sa.SupplierType = @SupplierType
049. ORDER BY po.SupplierID,po.PoNo,sa.SupplierAssessSubjectID
050. END
051. ELSE
052. BEGIN
053. SELECT sa.PoNo,
054. po.SupplierID,
055. (SELECT sup.SupplierName FROM
056. [dbo].[Suppliers] sup WHERE sup.SupplierID=po.SupplierID)
057. AS SupplierName,
058. sa.SupplierType,
059. sa.SupplierAssessSubjectID,
060. sa.Grade
061. FROM [dbo].[SupplierAssess] sa INNER JOIN
062. [dbo].[PurchaseOrders] po ON sa.PoNo = po.PoNo
063. WHERE(po.SupplierID BETWEEN @FromSupplierCode AND @ToSupplierCode)AND
064. (sa.Grade = '5' OR sa.Grade = '4' OR sa.Grade = '3' OR sa.Grade = '2' OR sa.Grade = '1') AND
065. (sa.GrDocumentDate BETWEEN @StartDate AND @ToDate) AND
066. (po.SupplierAssessStatus = 1)AND
067. po.DocumentType = 'A' AND
068. po.SideID=@SideCode AND
069. sa.SupplierType = @SupplierType
070. ORDER BY po.SupplierID,po.PoNo,sa.SupplierAssessSubjectID
071.
072. END
073. END
074. ELSE
075. BEGIN
076. IF @SideCode IS NULL
077. BEGIN
078. SELECT sa.PoNo,
079. po.SupplierID,
080. (SELECT sup.SupplierName FROM
081. [dbo].[Suppliers] sup WHERE sup.SupplierID=po.SupplierID)
082. AS SupplierName,
083. sa.SupplierType,
084. sa.SupplierAssessSubjectID,
085. sa.Grade
086. FROM [dbo].[SupplierAssess] sa INNER JOIN
087. [dbo].[PurchaseOrders] po ON sa.PoNo = po.PoNo
088. WHERE(po.SupplierID BETWEEN @FromSupplierCode AND @ToSupplierCode)AND
089. (sa.Grade = '5' OR sa.Grade = '4' OR sa.Grade = '3' OR sa.Grade = '2' OR sa.Grade = '1') AND
090. (sa.GrDocumentDate BETWEEN @StartDate AND @ToDate) AND
091. (po.SupplierAssessStatus = 1)AND
092. po.DocumentType = 'B' OR po.DocumentType = 'C' AND
093. sa.SupplierType = @SupplierType
094. ORDER BY po.SupplierID,po.PoNo,sa.SupplierAssessSubjectID
095.
096. END
097. ELSE
098. BEGIN
099. SELECT sa.PoNo,
100. po.SupplierID,
101. (SELECT sup.SupplierName FROM
102. [dbo].[Suppliers] sup WHERE sup.SupplierID=po.SupplierID)
103. AS SupplierName,
104. sa.SupplierType,
105. sa.SupplierAssessSubjectID,
106. sa.Grade
107. FROM [dbo].[SupplierAssess] sa INNER JOIN
108. [dbo].[PurchaseOrders] po ON sa.PoNo = po.PoNo
109. WHERE(po.SupplierID BETWEEN @FromSupplierCode AND @ToSupplierCode)AND
110. (sa.Grade = '5' OR sa.Grade = '4' OR sa.Grade = '3' OR sa.Grade = '2' OR sa.Grade = '1') AND
111. (sa.GrDocumentDate BETWEEN @StartDate AND @ToDate) AND
112. (po.SupplierAssessStatus = 1)AND
113. po.DocumentType = 'B' OR po.DocumentType = 'C' AND
114. po.SideID=@SideCode AND
115. sa.SupplierType = @SupplierType
116. ORDER BY po.SupplierID,po.PoNo,sa.SupplierAssessSubjectID
117.
118. END
119. END
120. END
