· 10 years ago · Sep 07, 2016, 06:40 PM
1/*
2Deployment script for GBLProducts_20160105
3
4This code was generated by a tool.
5Changes to this file may cause incorrect behavior and will be lost if
6the code is regenerated.
7*/
8
9GO
10SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER ON;
11
12SET NUMERIC_ROUNDABORT OFF;
13
14
15GO
16:setvar DatabaseName "GBLProducts_20160105"
17:setvar DefaultFilePrefix "GBLProducts_20160105"
18:setvar DefaultDataPath "K:\MSSQL\DATA\MSSQL10_50.RATEL\MSSQL\DATA\"
19:setvar DefaultLogPath "K:\MSSQL\RATEL_LOG_1\LOG\"
20
21GO
22:on error exit
23GO
24/*
25Detect SQLCMD mode and disable script execution if SQLCMD mode is not supported.
26To re-enable the script after enabling SQLCMD mode, execute the following:
27SET NOEXEC OFF;
28*/
29:setvar __IsSqlCmdEnabled "True"
30GO
31IF N'$(__IsSqlCmdEnabled)' NOT LIKE N'True'
32 BEGIN
33 PRINT N'SQLCMD mode must be enabled to successfully execute this script.';
34 SET NOEXEC ON;
35 END
36
37
38GO
39USE [$(DatabaseName)];
40
41
42GO
43
44IF (SELECT OBJECT_ID('tempdb..#tmpErrors')) IS NOT NULL DROP TABLE #tmpErrors
45GO
46CREATE TABLE #tmpErrors (Error int)
47GO
48SET XACT_ABORT ON
49GO
50SET TRANSACTION ISOLATION LEVEL READ COMMITTED
51GO
52BEGIN TRANSACTION
53GO
54PRINT N'Altering [dbo].[GP_InsertUpdate_OpsCore_ItemMasterExtended]...';
55
56
57GO
58-- =============================================
59-- Author: Aaron VanMeter
60-- Create date: 8/10/2016
61-- Description: Insert or Update a row into the Great Plains Blue Moon OpsCore IV Item Master Extended table (RBI30103),
62-- and add a record to the WMI10500 table for auto-release to the WMS.
63-- =============================================
64ALTER PROCEDURE [dbo].[GP_InsertUpdate_OpsCore_ItemMasterExtended]
65 @ItemNumber NVARCHAR(31)
66
67AS
68BEGIN
69 -- SET NOCOUNT ON added to prevent extra result sets from
70 -- interfering with SELECT statements.
71 SET NOCOUNT ON;
72 SET XACT_ABORT ON;
73
74 BEGIN TRY
75 IF EXISTS(SELECT NULL FROM [DBSQL08VC7\RAVEN].BBQFG.dbo.RBI31101 WHERE ITEMNMBR = @ItemNumber)
76 UPDATE [DBSQL08VC7\RAVEN].BBQFG.dbo.RBI31101 SET
77 [RBI_UPCA_Number] = ISNULL([GPImport].UPCA, RBI_UPCA_Number)
78
79 ,[RBI_Cubage] = ISNULL([GPImport].InPackCubage, [RBI_Cubage])
80 ,[RBI_Height] = ISNULL([GPImport].InPackHeight, [RBI_Height])
81 ,[RBI_Width] = ISNULL([GPImport].InPackWidth, [RBI_Width])
82 ,[RBI_Length] = ISNULL([GPImport].InPackLength, [RBI_Length])
83 --,[WMI_Handle_Code] = <WMI_Handle_Code, char(1),>
84 --,[WMI_Inner_Pack_Size] = <WMI_Inner_Pack_Size, numeric(19,5),>
85 --,[WMI_Middle_Pack_Size] = <WMI_Middle_Pack_Size, numeric(19,5),>
86 --,[WMI_Outer_Pack_Size] = <WMI_Outer_Pack_Size, numeric(19,5),>
87 --,[RBI_Value_Price] = <RBI_Value_Price, numeric(19,5),>
88
89 ,[WMI_Item_Hold] = ISNULL([GPImport].OnHold, WMI_Item_Hold)
90 ,[WMI_Tariff_Code] = ISNULL([GPImport].TariffCode, WMI_Tariff_Code)
91 ,[WMI_Freight_Class] = ISNULL([GPImport].FreightClass, WMI_Freight_Class)
92 ,[RBI_Country_Code] = ISNULL([GPImport].CountryCode, RBI_Country_Code)
93
94 from GP_Extended_Data as [GPImport]
95 inner join [DBSQL08VC7\RAVEN].BBQFG.dbo.RBI31101 as [GP] on [GPImport].ItemNumber=[GP].ITEMNMBR
96 WHERE ITEMNMBR = @ItemNumber;
97 ELSE
98 INSERT INTO [DBSQL08VC7\RAVEN].BBQFG.dbo.RBI31101
99 (ITEMNMBR, RBI_UPCA_Number, WMI_Item_Hold, WMI_Tariff_Code, WMI_Freight_Class, RBI_Country_Code,RBI_UPCA_Number,RBI_Cubage ,RBI_Height,RBI_Width,RBI_Length)
100 SELECT
101 [GPImport].ItemNumber,
102 ISNULL([GPImport].UPCA,''),
103 ISNULL([GPImport].OnHold, ''),
104 ISNULL([GPImport].TariffCode, ''),
105 ISNULL([GPImport].FreightClass, ''),
106 ISNULL([GPImport].CountryCode, '',
107 ISNULL([GPImport].UPCA, ''),
108 ISNULL([GPImport].InPackCubage, 0),
109 ISNULL([GPImport].InPackHeight, 0),
110 ISNULL([GPImport].InPackWidth, 0),
111 ISNULL([GPImport].InPackLength, 0)
112 )
113 FROM GP_Extended_Data as [GPImport]
114 where [GPImport].ItemNumber=@ItemNumber
115 ;
116
117
118 --BlueMoon's SQL Auto Release Integration (pushes Item's down to WMS)
119 IF NOT EXISTS (SELECT top 1 * FROM [DBSQL08VC7\RAVEN].BBQFG.dbo.WMI10500 WHERE ITEMNMBR = @ItemNumber)
120 BEGIN
121 --SELECT * FROM [DBSQL08VC7\RAVEN].BBQFG.dbo.WMI10500
122 INSERT INTO [DBSQL08VC7\RAVEN].BBQFG.dbo.WMI10500
123 (TIMESTMP, DOCNUMBR, WMI_Document_Type, ITEMNMBR, Transaction_Type, WMI_Priority)
124 VALUES (@ItemNumber, 0, 0, @ItemNumber, 20, '');
125 END
126
127 RETURN 1
128 END TRY
129 BEGIN CATCH
130 RETURN 0
131 END CATCH
132END
133GO
134IF @@ERROR <> 0
135 AND @@TRANCOUNT > 0
136 BEGIN
137 ROLLBACK;
138 END
139
140IF @@TRANCOUNT = 0
141 BEGIN
142 INSERT INTO #tmpErrors (Error)
143 VALUES (1);
144 BEGIN TRANSACTION;
145 END
146
147
148GO
149PRINT N'Altering [dbo].[SyncSalespad]...';
150
151
152GO
153ALTER PROCEDURE [dbo].SyncSalespad
154
155 @ItemNumber varchar(31)
156
157AS
158
159 SET NOCOUNT ON;
160
161 SET XACT_ABORT ON;
162
163
164
165 UPDATE [DBSQL08VC7\RAVEN].[BBQFG].[dbo].[spxItemMaster]
166 set
167 xLTLShippingRequired =[GPImport].LTLShippingRequired,
168 xItemStatus =[GPImport].Status,
169 xItemLeadTime =[GPImport].LeadTime,
170 xItemLeadTimeDesc =[GPImport].LeadTimeDesc,
171 xFreightClass =[GPImport].FreightClass,
172 xFreightClassDesc =[GPImport].FreightClassDesc,
173 xNMFCNumber =[GPImport].NMFCNumber
174
175 from GP_Extended_Data as [GPImport]
176 inner join [DBSQL08VC7\RAVEN].[BBQFG].[dbo].[spxItemMaster] as [GP] on [GPImport].ItemNumber=[GP].Item_Number
177 where Item_Number=@ItemNumber;
178
179 if @@ROWCOUNT=0
180
181
182 INSERT INTO [DBSQL08VC7\RAVEN].[BBQFG].[dbo].[spxItemMaster]
183 (
184 Item_Number,
185 xLTLShippingRequired,
186 xItemStatus,
187 xItemLeadTime,
188 xItemLeadTimeDesc,
189 xFreightClass,
190 xFreightClassDesc,
191 xNMFCNumber
192 )
193 select
194
195 [GPImport].ItemNumber,
196 [GPImport].LTLShippingRequired,
197 [GPImport].Status,
198 [GPImport].LeadTime,
199 [GPImport].LeadTimeDesc,
200 [GPImport].FreightClass,
201 [GPImport].FreightClassDesc,
202 [GPImport].NMFCNumber
203
204 from GP_Extended_Data as [GPImport]
205 inner join [DBSQL08VC7\RAVEN].[BBQFG].[dbo].[spxItemMaster] as [GP] on [GPImport].ItemNumber=@ItemNumber
206
207 ;
208GO
209IF @@ERROR <> 0
210 AND @@TRANCOUNT > 0
211 BEGIN
212 ROLLBACK;
213 END
214
215IF @@TRANCOUNT = 0
216 BEGIN
217 INSERT INTO #tmpErrors (Error)
218 VALUES (1);
219 BEGIN TRANSACTION;
220 END
221
222
223GO
224
225IF EXISTS (SELECT * FROM #tmpErrors) ROLLBACK TRANSACTION
226GO
227IF @@TRANCOUNT>0 BEGIN
228PRINT N'The transacted portion of the database update succeeded.'
229COMMIT TRANSACTION
230END
231ELSE PRINT N'The transacted portion of the database update failed.'
232GO
233DROP TABLE #tmpErrors
234GO
235PRINT N'Update complete.';
236
237
238GO