· 6 years ago · Oct 03, 2019, 06:40 AM
1/*<IssueID:112364>,Date:27-Jun-2019,Done By:Anvesh Kumar Dubey<1394>*/
2/*<IssueID:112364>,Date:04-Jul-2019,Done By:Anvesh Kumar Dubey<1394>*/
3/*<IssueID:140627>,Date:09-Sep-2019,Done By:Anvesh Kumar Dubey<1394>*/
4IF EXISTS (SELECT TOP 1 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[xspOCSaveData]') AND TYPE IN (N'P', N'PC'))
5DROP PROCEDURE xspOCSaveData
6GO
7CREATE PROCEDURE xspOCSaveData
8(
9 @guid xdtName,
10 @action xdtName = '',
11 @fromPage xdtLongName,
12 @pageParams xdtUniCodeMaxText = '',
13 @details xdtUniCodeMaxText = '',
14 @deletedIdList xdtUniCodeMaxText = '',
15 @errormsg xdtUniCodeMaxText = '' OUT,
16 @OutputMessage xdtUniCodeMaxText = '' OUT,
17 @OCdetailData xdtUniCodeMaxText = '',
18 @SummaryOCdetailData xdtUniCodeMaxText = '',
19 @RequestForBudgetData xdtUniCodeMaxText = '',
20 /*Start<IssueID:112364>*/
21 @GarmentQtyReconciliationDetails xdtUniCodeMaxText = '',
22 @RMReconciliationDetails xdtUniCodeMaxText = '',
23 @StyleID xdtID = 0
24 /*End<IssueID:112364>*/
25)
26AS
27BEGIN
28 SET NOCOUNT ON;
29 DECLARE @columnList xdtMaxText = '', @IsStyleReconciled xdtName = '0', @IsRMReco xdtName = '0', @IsShipmentDelayRemarks xdtName = '0',
30 @IsGarmentReco xdtName = '0'
31 DECLARE @companyCode xdtCompanyCode,@memberCompanyCode xdtCompanyCode,@companyDivisionCode xdtCompanyCode,@createdBy xdtName
32
33 EXEC xspGetSessionVariable @guid = @guid, @companyCode = @companyCode OUTPUT,@membercompanyCode = @membercompanyCode OUTPUT,
34 @companyDivisionCode = @companyDivisionCode OUTPUT, @userName = @createdBy OUTPUT
35
36 if (@FromPage='WFXOCList' AND @action in('DeliveryUpdate','RequestForBudgetUpdate'))
37 begin
38 exec xspUpdateOCDeliveryData @guid =@guid, @action =@action ,@fromPage =@fromPage ,
39 @pageParams =@pageParams ,@OCdetailData=@OCdetailData,@SummaryOCdetailData = @SummaryOCdetailData,
40 @RequestForBudgetData=@RequestForBudgetData,@errorMsg =@errorMsg OUTPUT,@OutputMessage=@OutputMessage output
41 RETURN
42 end
43
44 if (@FromPage='WFXOCList')
45 begin
46 exec xspOrderActionSaveData @guid =@guid, @action =@action ,@fromPage =@fromPage ,
47 @pageParams =@pageParams , @errorMsg =@errorMsg OUTPUT,@OutputMessage=@OutputMessage output
48
49 return
50 end
51
52 if @fromPage='WFXOrderTransferPrice'
53 Begin
54 Execute xspOrderTransferPriceSaveData @guid=@guid, @fromPage=@fromPage, @pageParams=@pageParams, @details=@details,@errormsg =@errormsg out
55 return
56 End
57 if @fromPage='wfx_CostSheetRO' AND @action='UpdateOrder'
58 BEGIN
59 EXEC xspUpdateOrderFromCostSheet @guid=@guid, @action=@action, @fromPage=@fromPage,@pageParams=@pageParams
60 RETURN
61 END
62 BEGIN TRY
63 CREATE TABLE [dbo].[#OrderShipment]
64 (
65 [OrderShipmentID] xdtID,
66 [OCASNDelayReasonID] xdtID,
67 [OCASNShortReasonID] xdtID,
68 [OCASNDelayOrShortRemarks] xdtUniCodeLongText,
69 [TimeStamp] bigint
70 )
71
72 CREATE TABLE #StyleReconciliation(
73 StyleID xdtID NULL,
74 ReconTranNum xdtUnicodeName,
75 CompanyShortName xdtName,
76 FinancialYear xdtName,
77 DivisionCode xdtName,
78 DivisionPrefix xdtName
79 )
80
81 IF(@action = 'Save' or @action = 'MarkAsCompleteShipment')
82 BEGIN
83 IF @fromPage='WFXOrderPendingForClosure'
84 BEGIN
85 exec xspOrderPendingForClosureSaveData @guid =@guid ,@action =@action , @fromPage =@fromPage , @errorMsg = @errorMsg oUT ,
86 @Details = @Details , @pageParams = @pageParams
87 return
88 END
89 IF(@details <> '')
90 BEGIN
91 EXEC xspGetTableFromString @Details = @details, @RowSeprator = '~', @ColumnSeperator = '|', @TableName = '#OrderShipment'
92 END
93
94 Select top 1 @errormsg = 'Unable to update Delay/Short Shipment remarks. Shipment No.: ' + b.InvoiceNo + ' is updated by some other user.'
95 From #OrderShipment a
96 JOIN xtOrderShipment b(nolock) on b.ID=a.OrderShipmentID
97 where a.TimeStamp <> CAST(b.LastTimeStamp as bigint)
98
99 IF ISNULL(@errormsg,'') <> ''
100 return
101
102 IF Exists (SELECT Top 1 1 FROM #OrderShipment)
103 BEGIN
104 UPDATE b SET LastChanged=GETDATE(),OCASNDelayReasonID = a.OCASNDelayReasonID, OCASNShortReasonID = a.OCASNShortReasonID,
105 OCASNDelayOrShortRemarks = a.OCASNDelayOrShortRemarks
106 From #OrderShipment a
107 JOIN xtOrderShipment b(nolock) on b.ID=a.OrderShipmentID
108
109 IF(@StyleID > 0)
110 BEGIN
111 INSERT INTO #StyleReconciliation(StyleID)
112 SELECT ID FROM xtArticle WHERE ID = @StyleID
113 Set @IsShipmentDelayRemarks = '1'
114 UPDATE xtArticle SET IsShipmentDelayRemarks = 1 Where ID= @StyleID
115 IF @IsShipmentDelayRemarks = '0' AND Exists(SELECT TOP 1 1 FROM xtArticle Where ID= @StyleID AND IsShipmentDelayRemarks =1)
116 Set @IsShipmentDelayRemarks = '1'
117 END
118 END
119
120 IF (@errormsg<>'') RETURN
121
122 IF(@action = 'MarkAsCompleteShipment')
123 BEGIN
124 Update xtOrder set BuyerFolderCode='Shipped'
125 From #OrderShipment a
126 JOIN xtOrderShipment b(nolock) on b.ID=a.OrderShipmentID
127 JOIN xtOrder c(nolock) on c.CompanyCode = c.CompanyCode AND c.OrderCode=b.OrderCode
128 AND c.RevisedCode=(Select Max(RevisedCode) from xtorder x(nolock) WHERE x.CompanyCode = c.CompanyCode and x.OrderCode = c.OrderCode)
129 END
130 /*Start<IssueID:112364>*/
131 IF(@GarmentQtyReconciliationDetails <> '')
132 BEGIN
133 CREATE TABLE #ActualConsCutQty(
134 ActualConsCutQtyID xdtId,StyleID xdtID ,SupplierCompanycode xdtInt,ColorCode xdtName,
135 ColorName varchar(200),OCQty decimal(24,4),GMPOQty decimal(24,4),GMPOShippedQty decimal(24,4),CutQty decimal(24,4),
136 SampleQty decimal(24,4),RejectedQty decimal(24,4),LossBySupplierQty decimal(24,4),Remarks xdtText
137 )
138 CREATE TABLE #ErrorActualConsCutQty(
139 RowID xdtID, NewRowID xdtID
140 )
141 CREATE TABLE #DeletedActualConsCutQty(
142 ActualConsCutQtyID xdtID,
143 )
144 EXEC xspGetTableFromString @Details=@GarmentQtyReconciliationDetails,@RowSeprator='~',@ColumnSeperator='|',@TableName='#ActualConsCutQty'
145
146 ALTER TABLE #ActualConsCutQty Add GMPOActualConsID xdtID
147 UPDATE #ActualConsCutQty set GMPOActualConsID = 0
148
149 Set @columnList = ''
150 SET @columnList='GMPOActualConsID,StyleID ,SupplierCompanycode,ColorCode,ColorName,OCQty,GMPOQty,GMPOShippedQty,CutQty,RejectedQty,SampleQty,LossBySupplierQty,Remarks'
151 EXEC xspValidateNSaveData @guid =@guid,@tableType='detail',@table='ActualConsCutQty',@uniqueColumnList = 'ActualConsCutQtyID',
152 @columnlist=@columnList,@errormsg =@errorMsg OUTPUT
153
154 IF(@StyleID > 0 and @errorMsg='')
155 BEGIN
156 INSERT INTO #StyleReconciliation(StyleID)
157 SELECT ID FROM xtArticle WHERE ID = @StyleID
158 Set @IsGarmentReco = '1'
159 UPDATE xtArticle SET IsGarmentQtyReco = 1 Where ID= @StyleID
160 IF @IsGarmentReco = '0' AND Exists(SELECT TOP 1 1 FROM xtArticle Where ID= @StyleID and IsGarmentQtyReco=1)
161 Set @IsGarmentReco = '1'
162 END
163
164 IF @errorMsg<>''
165 RETURN
166
167 Update a SET ActualConsCutQtyID = b.NewRowID
168 From #ActualConsCutQty a
169 JOIN #ErrorActualConsCutQty b on b.RowID=a.ActualConsCutQtyID
170 END
171 IF(@RMReconciliationDetails <> '')
172 BEGIN
173
174 CREATE TABLE #ArticleRCRM(
175 ArticleRCRMID xdtID NULL,ArticleID xdtID NULL,
176 ColorCode xdtName NULL, ColorName xdtFileName NULL,UOM xdtName NULL,
177 TotalInHouseQty xdtUnits NULL, TotalRejectedQty xdtUnits NULL, TotalConsumedQty xdtUnits NULL,
178 ShortExcessQty xdtUnits NULL, SupplierLossQty xdtUnits NULL, MissingConsumptionQty xdtUnits NULL,
179 SupplierBalanceQty xdtUnits NULL, Remarks xdtText, StyleID xdtID NULL
180 )
181
182 CREATE TABLE #ErrorArticleRCRM(
183 RowID xdtID, NewRowID xdtID
184 )
185 CREATE TABLE #DeletedArticleRCRM(
186 ArticleRCRMID xdtID,
187 )
188 EXEC xspGetTableFromString @Details=@RMReconciliationDetails,@RowSeprator='~',@ColumnSeperator='|',@TableName='#ArticleRCRM'
189
190 Set @columnList = ''
191 SET @columnList='ArticleID,ColorCode ,ColorName,UOM,TotalInHouseQty,TotalRejectedQty,TotalConsumedQty,ShortExcessQty,SupplierLossQty,'
192 + 'MissingConsumptionQty,SupplierBalanceQty,Remarks,StyleID'
193 EXEC xspValidateNSaveData @guid =@guid,@tableType='detail',@table='ArticleRCRM',@uniqueColumnList = '#ArticleRCRMID',
194 @columnlist=@columnList,@errormsg =@errorMsg OUTPUT
195
196 IF(@StyleID > 0 and @errorMsg = '')
197 BEGIN
198 INSERT INTO #StyleReconciliation(StyleID)
199 SELECT ID FROM xtArticle WHERE ID = @StyleID
200 Set @IsRMReco = '1'
201 UPDATE xtArticle SET IsRMReco = 1 Where ID= @StyleID
202 IF @IsRMReco = '0' AND Exists(SELECT TOP 1 1 FROM xtArticle Where ID= @StyleID and IsRMReco = 1 )
203 Set @IsRMReco = '1'
204 END
205
206 IF @errorMsg<>''
207 RETURN
208
209 Update a SET ArticleRCRMID = b.NewRowID
210 From #ArticleRCRM a
211 JOIN #ErrorArticleRCRM b on b.RowID=a.ArticleRCRMID
212 END
213 /*End<IssueID:112364>*/
214 /*Start<IssueID:140627>*/
215 UPDATE a Set @IsShipmentDelayRemarks =(CASE When IsNull(@IsShipmentDelayRemarks,'') = '' THEN b.IsShipmentDelayRemarks
216 ELSE @IsShipmentDelayRemarks END),
217 @IsGarmentReco = (CASE WHEN IsNull(@IsGarmentReco,'') = '' THEN b.IsGarmentQtyReco
218 ELSE @IsGarmentReco END),
219 @IsRMReco = (CASE WHEN IsNull(@IsRMReco,'') = '' THEN b.IsRMReco ELSE @IsRMReco END)
220 FROM #StyleReconciliation a
221 JOIN xtArticle b (NOLOCK) On b.ID=a.StyleID
222 /*End<IssueID:140627>*/
223 IF EXISTS(SELECT TOP 1 1 from #StyleReconciliation WHERE ReconTranNum Is Null) AND (@IsShipmentDelayRemarks = '1'
224 OR @IsGarmentReco = '1' OR @IsRMReco = '1')
225 BEGIN
226 DECLARE @ConfirmOrRevise xdtName = '', @DivisionCode xdtName = '', @ReconTranNum xdtUnicodeName = ''
227 IF (@action = 'Save') set @ConfirmOrRevise = 'Confirm'
228
229 UPDATE #StyleReconciliation set CompanyShortName=cmp.CompanyShortName
230 FROM #StyleReconciliation a
231 JOIN xtCompany cmp(nolock) on cmp.MemberCompanyCode=@MemberCompanyCode
232
233 SELECT @DivisionCode=Title From xtUserGroup (nolock) Where ID=@companyDivisionCode
234
235 UPDATE a Set DivisionCode=isnull(d.DivisionPrefix,@DivisionCode),FinancialYear=c.Name, DivisionPrefix=d.DivisionPrefix
236 FROM #StyleReconciliation a
237 LEFT JOIN xtCompanyAddresses d (nolock) on d.AddressCode=@DivisionCode and d.MemberCompanyCode=@memberCompanyCode
238 LEFT JOIN xtFinancialPeriod c (nolock) on c.MemberCompanyCode=@memberCompanyCode and datediff(d,getdate(),ToDate)>=0
239 and datediff(d,FromDate,getdate())>=0
240
241 Exec xspGenerateAutoCode @CompanyCode =@companyCode,@WFXObject ='StyleReconciliation',@headerTable ='#StyleReconciliation',@Code =@ReconTranNum output
242
243 If @ReconTranNum=''
244 Begin
245 Set @errorMsg='ReconTranNum could not be created.'
246 Return
247 End
248 UPDATE #StyleReconciliation set ReconTranNum=@ReconTranNum
249 END
250 ELSE
251 BEGIN
252 SET @ConfirmOrRevise='Revise'
253 SELECT @ReconTranNum=@ReconTranNum from #StyleReconciliation(nolock)
254 END
255 IF (@IsShipmentDelayRemarks = '1' OR @IsGarmentReco = '1' OR @IsRMReco = '1')
256 BEGIN
257 UPDATE xtArticle SET IsStyleReconciled = 1, ReconTranNum = @ReconTranNum Where ID= @StyleID
258 END
259 END
260
261 END TRY
262 BEGIN CATCH
263 DECLARE @ErrorSPName xdtLongName
264 SET @ErrorSPName = ''
265 IF ISNULL(ERROR_PROCEDURE(),'') NOT IN ('','xspOCSaveData')
266 SELECT @ErrorSPName = ERROR_PROCEDURE() + '<br />'
267 IF ISNULL(@errorMsg,'') = ''
268 SELECT @errorMsg = ERROR_MESSAGE()
269 SET @errorMsg = 'xspOCSaveData <br />' + @ErrorSPName + 'Line No: ' + CAST(ERROR_LINE() AS VARCHAR) + '<br />' + @errorMsg
270
271 END CATCH
272END