· 6 years ago · Oct 16, 2019, 11:02 AM
1ALTER PROCEDURE xspStyleReconciliationGetList
2(
3 @guid xdtLongName,
4 @fromPage xdtName,
5 @pageParams xdtUniCodeMaxText='',
6 @searchParams xdtUniCodeMaxText='',
7 @sortParams xdtUniCodeLongText='',
8 @pageIndex smallInt=1,
9 @pageSize Int=20,
10 @errorMsg VARCHAR(1000)='' OUTPUT
11)
12AS
13BEGIN
14 DECLARE @companyCode xdtCompanyCode,@memberCompanyCode xdtCompanyCode,@companyDivisionCode xdtCompanyCode,@userName xdtName
15 EXEC xspGetSessionVariable @guid=@guid ,@companyCode =@companyCode OUTPUT,@membercompanyCode =@membercompanyCode OUTPUT,
16 @companyDivisionCode =@companyDivisionCode OUTPUT, @userName =@userName OUTPUT
17
18 DECLARE @StyleCode xdtLongCode, @StyleName xdtText, @BuyerStyleRef xdtName, @StyleClosedDate xdtName
19 , @StyleClosedDateFrom xdtDate, @StyleClosedDateTo xdtDate, @ClosedBy xdtUniCodeLongText
20 , @LastETDDate xdtName, @LastETDDateFrom xdtDate, @LastETDDateTo xdtDate, @TranNum xdtName, @CalledFrom xdtName
21 , @Buyer xdtName, @DivisionManager xdtUnicodeLongText, @Manager xdtUnicodeLongText, @Status xdtName
22 , @IsExportToExcel xdtTinyNum
23
24 IF (ISNULL(@searchParams,'') <>'')
25 BEGIN
26 CREATE TABLE #SearchParam(ParamName Nvarchar(1000),ParamValue Nvarchar(max))
27 EXEC dbo.xspGetTableFromString @details =@searchParams,@rowSeprator ='~',@columnSeperator ='|',@tableName ='#SearchParam'
28 SELECT @StyleCode=ParamValue From #SearchParam Where ParamName='colStyleCode'
29 SELECT @StyleName=ParamValue From #SearchParam Where ParamName='colStyleName'
30 SELECT @BuyerStyleRef=ParamValue From #SearchParam Where ParamName='colBuyerStyleRef'
31 SELECT @StyleClosedDate=ParamValue From #SearchParam Where ParamName='colClosedDate'
32 SELECT @LastETDDate=ParamValue From #SearchParam Where ParamName='colLastETDDate'
33 SELECT @ClosedBy=ParamValue From #SearchParam Where ParamName='colClosedBy'
34 SELECT @TranNum=ParamValue From #SearchParam Where ParamName='colTranNum'
35 SELECT @Status = ParamValue From #SearchParam Where ParamName='colStatus'
36 SELECT @Buyer = ParamValue From #SearchParam Where ParamName='colBuyer'
37
38 IF ISNULL(@StyleClosedDate,'')=''
39 BEGIN
40 SELECT @StyleClosedDateFrom=paramvalue from #SearchParam where ParamName='colClosedDateFrom';
41 SELECT @StyleClosedDateTo=paramvalue from #SearchParam where paramname='colClosedDateTo'
42 END
43 ELSE
44 EXEC xspGetDateRange @param=@StyleClosedDate, @fromDate=@StyleClosedDateFrom output, @toDate=@StyleClosedDateTo output, @errorMsg=@errorMsg output
45
46 IF ISNULL(@LastETDDate,'')=''
47 BEGIN
48 SELECT @LastETDDateFrom=paramvalue from #SearchParam where ParamName='colLastETDDateFrom';
49 SELECT @LastETDDateTo=paramvalue from #SearchParam where paramname='colLastETDDateTo'
50 END
51 ELSE
52 EXEC xspGetDateRange @param=@LastETDDate, @fromDate=@LastETDDateFrom output, @toDate=@LastETDDateTo output, @errorMsg=@errorMsg output
53 END
54
55 if ISNULL(@pageParams,'') <>''
56 BEGIN
57 CREATE TABLE #PageParam(ParamName varchar(1000),ParamValue varchar(max))
58 EXEC dbo.xspGetTableFromString @Details =@pageParams,@RowSeprator ='~',@ColumnSeperator ='|',@TableName ='#PageParam'
59 SELECT @fromPage=ParamValue FROM #PageParam where ParamName='FromPage'
60 SELECT @CalledFrom=ParamValue FROM #PageParam where ParamName='CalledFrom'
61 select @IsExportToExcel=ParamValue FROM #PageParam where ParamName='IsExportToExcel'
62 END
63
64 CREATE TABLE #PendingStyleReconciledData(
65 [StyleReconciliationID] xdtId, [BuyerCompanyName] xdtUnicodeText, [DivisionManager] xdtUniCodeLongText NULL,
66 [Manager] xdtUniCodeLongText NULL, [Style] xdtText NULL, [StyleCode] xdtText NULL, [StyleName] xdtText NULL,
67 [BuyerStyleRefNo] xdtName NULL, [LastETDDate] xdtName NULL, [GMPOETDDate] xdtName NULL, [StyleClosedDate] xdtName NULL,
68 [TranNum] xdtName NULL, [ClosedBy] xdtLongName NULL, [Status] xdtName NULL, [IsStyleReconciled] xdtbit NULL,
69 [SupplierShipmentCode] xdtInt NULL
70 )
71
72 IF (@CalledFrom='PendingStyleReconciliationList')
73 BEGIN
74 /*Start<IssueID:112364>*/
75 INSERT INTO #PendingStyleReconciledData(StyleReconciliationID, StyleCode, StyleName, BuyerStyleRefNo, BuyerCompanyName, DivisionManager, Manager
76 , LastETDDate,GMPOETDDate, ClosedBy, Status, TranNum,IsStyleReconciled,StyleClosedDate,SupplierShipmentCode)
77 SELECT st.id , st.ArticleCode, st.ArticleName, st.BuyerReference, buyer.CompanyName , ''[DivisionManager], ''[Manager],
78 ''[LastETDDate],NULL , ''[ClosedBy],
79 CASE WHEN (IsNull(st.IsRMReco,0)= 1 OR IsNull(st.IsShipmentDelayRemarks,0)= 1 OR IsNull(st.IsGarmentQtyReco,0)= 1) THEN 'Part Reco'
80 ELSE '' END,'',st.IsStyleReconciled,
81 Convert(varchar(11),st.LastChanged,106), 0 SupplierShipmentCode
82 From xtArticle st(nolock)
83 JOIN xtCompany buyer(NOLOCK) on buyer.MemberCompanyCode=st.Buyer
84 where Not Exists(Select top 1 1 from xtArticle art
85 Where art.ID=st.ID and Isnull(art.IsStyleReconciled,0) = 1 And IsNull(art.ReconTranNum,'')<>''
86 And IsNull(art.IsShipmentDelayRemarks,0)=1 And IsNull(art.IsGarmentQtyReco,0)=1 And IsNull(art.IsRMReco,0)=1)
87
88 AND EXISTS(
89 SELECT TOP 1 1 FROM xtOrder ord
90 JOIN xtOrderShipment os ON os.OrderCode = ord.OrderCode and os.IsLastVersion =1 and os.Confirmed=1
91 WHERE ord.OCType<>-1 and ord.IsLastVersion = 1 And ord.OrderType<>6 and ord.StyleID = st.ID
92 )
93 AND NOT EXISTS (
94 SELECT TOP 1 1 FROM xtOrder ord
95 JOIN xtOrderShipment os ON os.OrderCode = ord.OrderCode and os.IsLastVersion =1 and os.Confirmed=1
96 LEFT JOIN xtGMPOActualConsASN asn (NOLOCK) ON asn.SupplierShipmentCode=os.GMPOASNSupplierShipmentCode
97 AND asn.ShipmentRevisedCode = os.ShipmentRevisedCode
98 WHERE ord.OCType<>-1 and ord.IsLastVersion = 1 And ord.OrderType<>6 and ord.StyleID = st.ID and asn.GMPOActualConsASNID IS NULL
99 )
100
101 AND (IsNull(@StyleCode,'')='' OR st.ArticleCode LIKE '%' + @StyleCode + '%')
102 AND (IsNull(@StyleName,'')='' OR st.ArticleName LIKE '%' + @StyleName + '%')
103 AND (IsNull(@BuyerStyleRef,'')='' OR st.BuyerReference like '%' + @BuyerStyleRef + '%')
104 AND (IsNull(@Buyer,'')='' OR Buyer.CompanyName like '%' + @Buyer + '%')
105 Order By st.ID Desc
106 /*End<IssueID:112364>*/
107 END
108 Else IF (@CalledFrom='StyleReconciliationList')
109 BEGIN
110 /*Start<IssueID:112364>*/
111 INSERT INTO #PendingStyleReconciledData(StyleReconciliationID, StyleCode, StyleName, BuyerStyleRefNo, BuyerCompanyName, DivisionManager, Manager
112 , LastETDDate,GMPOETDDate, ClosedBy, Status, TranNum,IsStyleReconciled,StyleClosedDate,SupplierShipmentCode)
113 SELECT st.ID , st.ArticleCode, st.ArticleName, st.BuyerReference, buyer.CompanyName , ''[DivisionManager], ''[Manager],
114 ''[LastETDDate],NULL , us.FullName [ClosedBy],'',st.ReconTranNum,st.IsStyleReconciled,Convert(varchar(11),st.LastChanged,106), 0 SupplierShipmentCode
115 FROM xtArticle st(NOLOCK)
116 JOIN xtCompany buyer(NOLOCK) on buyer.MemberCompanyCode=st.Buyer
117 JOIN xtUser us(NOLOCK) on us.UserName=st.CreatedBy
118 where IsStyleReconciled = 1 And ReconTranNum Is Not NULL
119 AND (IsNull(@StyleCode,'')='' OR st.ArticleCode LIKE '%' + @StyleCode + '%')
120 AND (IsNull(@StyleName,'')='' OR st.ArticleName LIKE '%' + @StyleName + '%')
121 AND (IsNull(@BuyerStyleRef,'')='' OR st.BuyerReference like '%' + @BuyerStyleRef + '%')
122 AND (IsNull(@Buyer,'')='' OR Buyer.CompanyName like '%' + @Buyer + '%')
123 AND (@StyleClosedDateFrom IS NULL OR (st.LastChanged) > = @StyleClosedDateFrom)
124 AND (@StyleClosedDateTo IS NULL OR (st.LastChanged) < = @StyleClosedDateTo)
125 AND (IsNull(@ClosedBy,'')='' OR us.FullName like '%' + @ClosedBy + '%')
126 AND (IsNull(@TranNum,'')='' OR st.ReconTranNum like '%' + @TranNum + '%')
127 /*End<IssueID:112364>*/
128 END
129
130 UPDATE g SET g.DivisionManager=u.FullName,g.Manager = u1.FullName
131 from #PendingStyleReconciledData g
132 JOIN xtOrder a(NOLOCK) on a.StyleID = g.StyleReconciliationID and a.OrderCode>0 and a.OCType<>-1 and a.IsLastVersion=1
133 and a.ID =(select Min(ID) from xtOrder where StyleID = a.StyleID and OrderCode>0 and OCType<>-1 and IsLastVersion=1)
134 JOIN xtUser u(NOLOCK) On u.UserName = a.DivisionManagerUserName
135 JOIN xtUser u1(NOLOCK) On u1.UserName = a.ManagerUserName
136
137 Select g.StyleReconciliationID, max(dbo.xfGetETDDate(b.ShipmentMode,b.AWBETD,b.SubETD,b.BLMotherETD)) LastETDDate, b.SupplierShipmentCode
138 INTO #ShipmentETDDate
139 from #PendingStyleReconciledData g
140 Join xtOrder a(NOLOCK) on a.StyleID = g.StyleReconciliationID and a.OrderCode>0 and a.OCType<>-1 and a.IsLastVersion=1
141 Join xtOrderShipment b on b.CompanyCode = a.CompanyCode AND b.OrderCode=a.OrderCode and b.IsLastVersion = 1
142 GROUP BY g.StyleReconciliationID, b.SupplierShipmentCode
143
144 UPDATE a SET a.LastETDDate = Convert(varchar(11),b.LastETDDate,106), a.GMPOETDDate = Convert(varchar(11),b.LastETDDate,106),
145 a.SupplierShipmentCode = b.SupplierShipmentCode
146 From #PendingStyleReconciledData a
147 JOIN #ShipmentETDDate b On a.StyleReconciliationID = b.StyleReconciliationID
148
149 --UPDATE a set a.ClosedBy = c.FullName
150 --From #PendingStyleReconciledData a
151 --JOIN xtArticleRC b (NOLOCK) ON b.StyleID = a.StyleReconciliationID
152 --JOIN xtUser c (NOLOCK) ON c.UserName = b.CreatedBy
153
154 SELECT * INTO #GMPOData1
155 FROM #PendingStyleReconciledData
156 Where (IsNull(@DivisionManager,'')='' OR DivisionManager like '%' + @DivisionManager + '%')
157 AND (IsNull(@Manager,'')='' OR Manager like '%' + @Manager + '%')
158
159 SELECT * INTO #GMPOData
160 FROM #GMPOData1
161 Where (IsNull(@LastETDDateFrom ,'')='' OR (LastETDDate) > = @LastETDDateFrom)
162 AND (IsNull(@LastETDDateTo ,'')='' OR (LastETDDate) < = @LastETDDateTo)
163 And (@Status Is NULL OR Status LIKE '%' + @Status + '%')
164 -- and (@searchParams <> '' OR ISNULL(@LastETDDate, '') <> '' OR (ISNULL(@LastETDDateFrom, '') = '' AND ISNULL(@LastETDDateTo, '') = ''
165 -- AND DATEDIFF(M, LastETDDate, GETDate()) <=2 ))
166
167 If ISNULL(@sortParams,'') =''
168 set @sortParams = 'colStyleReconciliationID|Desc'
169
170 Select * into #GMPODataFinal From #GMPOData where 1=2
171
172 IF @IsExportToExcel = '1'
173 BEGIN
174 SELECT @pageIndex = 1, @pageSize = COUNT(1) FROM #GMPOData
175 END
176 Execute xspGetPageData @sourceTable = '#GMPOData',@sortParams=@sortParams,@pageIndex=@pageIndex, @pageSize=@pageSize,@errorMsg=@errorMsg output
177
178 SELECT * FROM #GMPODataFinal Order By StyleReconciliationID Desc
179 SELECT Count(1) RecordCount FROM [#GMPODataFinal]
180END