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