This script is not one created, troubleshot, or supported by Bold Support. It is referenced here as it may be related to Custom work by Wayne O for customers in the following cases during their migration. 00150530
USE [msdb]
GO
/****** Object: Job [SedonaOffice Quoteworks Sync] Script Date: 12/16/2025 12:55:35 PM ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Uncategorized (Local)]] Script Date: 12/16/2025 12:55:35 PM ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'SedonaOffice Quoteworks Sync',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'SedonaUser', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [--Insert New Part Product Lines] Script Date: 12/16/2025 12:55:35 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'--Insert New Part Product Lines',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'INSERT INTO QuoteWerks.dbo.Products_SedonaParts_Folders
(ExtraLogic01,
ExtraMemo01,
ExtraText01,
ExtraText02,
FolderName,
FolderType,
IndentLevel,
ParentID,
Ref1,
Ref2,
Ref3)
SELECT
0,--ExtraLogic01,
'''',--ExtraMemo01,
'''',--ExtraText01,
'''',--ExtraText02,
Product_Line_Code,--FolderName,
''System'',--FolderType,
0,--IndentLevel,
0,--ParentID,
'''',--Ref1,
'''',--Ref2,
''''--Ref3
FROM Multiguard_Final.dbo.IN_Product_Line
WHERE Product_Line_Code NOT IN (SELECT FolderName FROM QuoteWerks.dbo.Products_SedonaParts_Folders)
AND Inactive <> ''Y''',
@database_name=N'QuoteWerks',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [--Update Parts Marked Y For Sales] Script Date: 12/16/2025 12:55:35 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'--Update Parts Marked Y For Sales',
@step_id=2,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'UPDATE QTW SET
Category = PDL.Product_Line_Code,
Cost = SUP.Cost,
Created = PRT.Created_On,
CustomMemo01 = PRT.Description,
CustomText01 = PRT.Part_Code,
CustomText02 = ''P'',
Description = PRT.Description,
Discontinued = CASE WHEN PRT.Inactive = ''Y'' THEN 1 ELSE 0 END,
ExtendedDescription = PRT.Detail,
FolderList = ''(''+CONVERT(VARCHAR,(SELECT MIN(ID) FROM QuoteWerks.dbo.Products_SedonaParts_Folders WHERE FolderName = PDL.Product_Line_Code))+'')'',
InternalDescription = PRT.Description,
InternalPartNumber = PRT.Part_Code,
ItemType = PDL.Product_Line_Code,
LastModified = GETDATE(),
LastModifiedBy = ''SQLJob ''+CONVERT(VARCHAR,GETDATE(),101),
List = SUP.List_Price,
Manufacturer = MAN.Description,
ManufacturerPartNumber = LEFT(PRT.Manufacturer_Part_Code,40),
Notes = PRT.Notes,
UnitWeight = PRT.Labor_units,
Price = PRT.Sales_Price,
PriceModifier = '''',--PriceModifier*******,
PricingLastUpdated = GETDATE(),
PricingLevel01 = ISNULL(LVL.Level1_Part_Price,''''),--PricingLevel01,
PricingLevel02 = ISNULL(LVL.Level2_Part_Price,''''),--PricingLevel02,
PricingLevel03 = ISNULL(LVL.Level3_Part_Price,''''),--PricingLevel03,
PricingLevel04 = ISNULL(LVL.Level4_Part_Price,''''),--PricingLevel04,
PricingLevel05 = ISNULL(LVL.Level5_Part_Price,''''),--PricingLevel05,
PricingLevel06 = ISNULL(LVL.Level6_Part_Price,''''),--PricingLevel06,
PricingLevel07 = ISNULL(LVL.Level7_Part_Price,''''),--PricingLevel07,
PricingLevel08 = ISNULL(LVL.Level8_Part_Price,''''),--PricingLevel08,
PricingLevel09 = ISNULL(LVL.Level9_Part_Price,''''),--PricingLevel09,
PricingLevel10 = ISNULL(LVL.Level10_Part_Price,''''),--PricingLevel10,
ShortDescription = PRT.Part_Code,
TaxCode = ''Y'',
Vendor = VEN.Vendor_Code,
VendorPartNumber = SUP.Vendor_Part_Number
FROM Products_SedonaParts_Products as QTW
INNER JOIN Multiguard_Final.dbo.IN_Part as PRT on QTW.CustomText01 = PRT.Part_Code
INNER JOIN Multiguard_Final.dbo.IN_Product_Line as PDL on PRT.Product_Line_Id = PDL.Product_Line_Id
INNER JOIN Multiguard_Final.dbo.IN_Manufacturer as MAN on PRT.Manufacturer_Id = MAN.Manufacturer_Id
INNER JOIN Multiguard_Final.dbo.IN_Part_Supplier as SUP on PRT.Part_Id = SUP.Part_Id AND SUP.Is_Primary = ''Y''
INNER JOIN Multiguard_Final.dbo.AP_Vendor as VEN on SUP.Vendor_Id = VEN.Vendor_Id
INNER JOIN Multiguard_Final.dbo.AR_Item as ITM on PRT.Item_Id = ITM.Item_Id
INNER JOIN Multiguard_Final.dbo.SS_Item_Type as TYP on ITM.Item_Type_Id = TYP.Item_Type_ID
LEFT JOIN Multiguard_Final.dbo.IN_Part_Price_Level as LVL on PRT.Part_Id = LVL.Part_Id
WHERE PRT.Sales_Part = ''Y''',
@database_name=N'QuoteWerks',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [--Insert New Parts Marked Y For Sales] Script Date: 12/16/2025 12:55:35 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'--Insert New Parts Marked Y For Sales',
@step_id=3,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'INSERT INTO QuoteWerks.dbo.Products_SedonaParts_Products
(AccountingItemType,
AccountingSalesAccount,
AlternateCost,
AlternateCurrency,
AlternateList,
AlternatePrice,
Availability,
Category,
CommissionProfile,
Cost,
CostAndPriceHistory,
CostingLevel01,
CostingLevel02,
CostingLevel03,
CostingLevel04,
CostingLevel05,
CostingLevel06,
CostingLevel07,
CostingLevel08,
CostingLevel09,
CostingLevel10,
CostingLevel11,
CostingLevel12,
CostingLevel13,
CostingLevel14,
CostingLevel15,
CostModifier,
Created,
CustomDate01,
CustomDate02,
CustomDate03,
CustomDate04,
CustomDate05,
CustomMemo01,
CustomMemo02,
CustomMemo03,
CustomMemo04,
CustomMemo05,
CustomNumber01,
CustomNumber02,
CustomNumber03,
CustomNumber04,
CustomNumber05,
CustomText01,
CustomText02,
CustomText03,
CustomText04,
CustomText05,
CustomText06,
CustomText07,
CustomText08,
CustomText09,
CustomText10,
CustomText11,
CustomText12,
CustomText13,
CustomText14,
CustomText15,
Description,
Discontinued,
ExtendedDescription,
FolderList,
Formula,
InternalDescription,
InternalPartNumber,
ItemType,
KeywordList,
LastModified,
LastModifiedBy,
LineItemAttributes,
LineItemType,
List,
Manufacturer,
ManufacturerPartNumber,
Notes,
PictureFileName,
Price,
PriceModifier,
PriceProfile,
PricingLastUpdated,
PricingLevel01,
PricingLevel02,
PricingLevel03,
PricingLevel04,
PricingLevel05,
PricingLevel06,
PricingLevel07,
PricingLevel08,
PricingLevel09,
PricingLevel10,
PricingLevel11,
PricingLevel12,
PricingLevel13,
PricingLevel14,
PricingLevel15,
PrintPicture,
QtyMultiplier1,
QtyMultiplier2,
QtyMultiplier3,
QtyMultiplier4,
SerialNumber,
ShortDescription,
SpecSheetName,
TaxCode,
TaxRate,
UnitOfMeasure,
UnitOfMeasureFactor,
UnitOfPricing,
UnitOfPricingFactor,
UnitWeight,
Vendor,
VendorPartNumber)
SELECT
'''',--AccountingItemType,
'''',--AccountingSalesAccount,
0,--AlternateCost,
'''',--AlternateCurrency,
0,--AlternateList,
0,--AlternatePrice,
0,--Availability,
PDL.Product_Line_Code,--Category,
'''',--CommissionProfile,
SUP.Cost, --Cost,
'''',--CostAndPriceHistory,
'''',--CostingLevel01,
'''',--CostingLevel02,
'''',--CostingLevel03,
'''',--CostingLevel04,
'''',--CostingLevel05,
'''',--CostingLevel06,
'''',--CostingLevel07,
'''',--CostingLevel08,
'''',--CostingLevel09,
'''',--CostingLevel10,
'''',--CostingLevel11,
'''',--CostingLevel12,
'''',--CostingLevel13,
'''',--CostingLevel14,
'''',--CostingLevel15,
'''',--CostModifier,
PRT.Created_On,--Created,
NULL,--CustomDate01,
NULL,--CustomDate02,
NULL,--CustomDate03,
NULL,--CustomDate04,
NULL,--CustomDate05,
PRT.Description,--CustomMemo01,
'''',--CustomMemo02,
'''',--CustomMemo03,
'''',--CustomMemo04,
'''',--CustomMemo05,
0,--CustomNumber01,
0,--CustomNumber02,
0,--CustomNumber03,
0,--CustomNumber04,
0,--CustomNumber05,
PRT.Part_Code,--CustomText01,
''P'',--CustomText02,
'''',--CustomText03,
0,--CustomText04,
'''',--CustomText05,
'''',--CustomText06,
'''',--CustomText07,
'''',--CustomText08,
'''',--CustomText09,
'''',--CustomText10,
'''',--CustomText11,
'''',--CustomText12,
'''',--CustomText13,
'''',--CustomText14,
'''',--CustomText15,
PRT.Description,--Description,
CASE WHEN PRT.Inactive = ''Y'' THEN 1 ELSE 0 END,--Discontinued,
PRT.Detail,--ExtendedDescription,
''(''+CONVERT(VARCHAR,(SELECT MIN(ID) FROM QuoteWerks.dbo.Products_SedonaParts_Folders WHERE FolderName = PDL.Product_Line_Code))+'')'',--FolderList,
'''',--Formula,
PRT.Description,--InternalDescription,
PRT.Part_Code,--InternalPartNumber,
PDL.Product_Line_Code,--ItemType,
'''',--KeywordList,
GETDATE(),--LastModified,
''SQLJob ''+CONVERT(VARCHAR,GETDATE(),101),--LastModifiedBy,
0,--LineItemAttributes,
0,--LineItemType,
SUP.List_Price,--List,
MAN.Description,--Manufacturer,
LEFT(PRT.Manufacturer_Part_Code,40),--ManufacturerPartNumber,
PRT.Notes,--Notes,
'''',--PictureFileName,
PRT.Sales_Price,--Price,
'''',--PriceModifier*******,
'''',--PriceProfile,
GETDATE(),--PricingLastUpdated,
ISNULL(LVL.Level1_Part_Price,''''),--PricingLevel01,
ISNULL(LVL.Level2_Part_Price,''''),--PricingLevel02,
ISNULL(LVL.Level3_Part_Price,''''),--PricingLevel03,
ISNULL(LVL.Level4_Part_Price,''''),--PricingLevel04,
ISNULL(LVL.Level5_Part_Price,''''),--PricingLevel05,
ISNULL(LVL.Level6_Part_Price,''''),--PricingLevel06,
ISNULL(LVL.Level7_Part_Price,''''),--PricingLevel07,
ISNULL(LVL.Level8_Part_Price,''''),--PricingLevel08,
ISNULL(LVL.Level9_Part_Price,''''),--PricingLevel09,
ISNULL(LVL.Level10_Part_Price,''''),--PricingLevel10,
'''',--PricingLevel11,
'''',--PricingLevel12,
'''',--PricingLevel13,
'''',--PricingLevel14,
'''',--PricingLevel15,
0,--PrintPicture,
1,--QtyMultiplier1,
1,--QtyMultiplier2,
1,--QtyMultiplier3,
1,--QtyMultiplier4,
'''',--SerialNumber,
PRT.Part_Code,--ShortDescription,
'''',--SpecSheetName,
''Y'',--TaxCode,
0,--TaxRate,
'''',--UnitOfMeasure,
0,--UnitOfMeasureFactor,
'''',--UnitOfPricing,
0,--UnitOfPricingFactor,
PRT.Labor_Units,--UnitWeight,
VEN.Vendor_Code,--Vendor,
SUP.Vendor_Part_Number --VendorPartNumber
FROM Multiguard_Final.dbo.IN_Part as PRT
INNER JOIN Multiguard_Final.dbo.IN_Product_Line as PDL on PRT.Product_Line_Id = PDL.Product_Line_Id
INNER JOIN Multiguard_Final.dbo.IN_Manufacturer as MAN on PRT.Manufacturer_Id = MAN.Manufacturer_Id
INNER JOIN Multiguard_Final.dbo.IN_Part_Supplier as SUP on PRT.Part_Id = SUP.Part_Id AND SUP.Is_Primary = ''Y''
INNER JOIN Multiguard_Final.dbo.AP_Vendor as VEN on SUP.Vendor_Id = VEN.Vendor_Id
INNER JOIN Multiguard_Final.dbo.AR_Item as ITM on PRT.Item_Id = ITM.Item_Id
INNER JOIN Multiguard_Final.dbo.SS_Item_Type as TYP on ITM.Item_Type_Id = TYP.Item_Type_ID
LEFT JOIN Multiguard_Final.dbo.IN_Part_Price_Level as LVL on PRT.Part_Id = LVL.Part_Id
WHERE PRT.Part_Code NOT IN (SELECT CustomText01 FROM QuoteWerks.dbo.Products_SedonaParts_Products)
AND PRT.Sales_Part = ''Y''',
@database_name=N'QuoteWerks',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [--Remove Parts No Longer Marked Y For Sales] Script Date: 12/16/2025 12:55:35 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'--Remove Parts No Longer Marked Y For Sales',
@step_id=4,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'DELETE QuoteWerks.dbo.Products_SedonaParts_Products
WHERE CustomText01 NOT IN
(SELECT Part_Code FROM Multiguard_Final.dbo.IN_Part WHERE Sales_Part = ''Y'' )',
@database_name=N'QuoteWerks',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [--Update Items Marked Y For Sales] Script Date: 12/16/2025 12:55:35 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'--Update Items Marked Y For Sales',
@step_id=5,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'UPDATE QTW SET
Category = CAT.Category_Code,
Cost = ITM.Default_Cost,
CustomMemo01 = ITM.Description,
CustomText01 = ITM.Item_Code,
CustomText02 = ''NR'',
Description = ITM.Description,
Discontinued = CASE WHEN ITM.Inactive = ''Y'' THEN 1 ELSE 0 END,
ExtendedDescription = ITM.Description,
InternalDescription = ITM.Description,
InternalPartNumber = ITM.Item_Code,
ItemType = TYP.Item_Type_Code,
LastModified = GETDATE(),
LastModifiedBy = ''SQLJob ''+CONVERT(VARCHAR,GETDATE(),101),
List = ITM.Default_Rate,
Manufacturer = ''Sedona'',
ManufacturerPartNumber = ITM.Item_Code,
Notes = ITM.Description,
Price = CASE WHEN ITM.Item_Code LIKE ''%discount%'' THEN ITM.Default_Rate * -1 ELSE ITM.Default_Rate END,
PricingLastUpdated = GETDATE(),
ShortDescription = ITM.Item_Code,
TaxCode = ''Y'',
VendorPartNumber = ITM.Item_Code
FROM Products_SedonaItems_Products as QTW
INNER JOIN Multiguard_Final.dbo.AR_Item as ITM on QTW.CustomText01 = ITM.Item_Code
INNER JOIN Multiguard_Final.dbo.AR_Category as CAT on ITM.Category_Id = CAT.Category_Id
INNER JOIN Multiguard_Final.dbo.SS_Item_Type as TYP on ITM.Item_Type_Id = TYP.Item_Type_Id
WHERE ITM.Sales_Item = ''Y'' AND TYP.Is_Recurring <> ''Y'' AND ITM.Inactive <> ''Y''',
@database_name=N'QuoteWerks',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [--Insert New Items Marked Y For Sales] Script Date: 12/16/2025 12:55:35 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'--Insert New Items Marked Y For Sales',
@step_id=6,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'INSERT INTO QuoteWerks.dbo.Products_SedonaItems_Products
(AccountingItemType,
AccountingSalesAccount,
AlternateCost,
AlternateCurrency,
AlternateList,
AlternatePrice,
Availability,
Category,
CommissionProfile,
Cost,
CostAndPriceHistory,
CostingLevel01,
CostingLevel02,
CostingLevel03,
CostingLevel04,
CostingLevel05,
CostingLevel06,
CostingLevel07,
CostingLevel08,
CostingLevel09,
CostingLevel10,
CostingLevel11,
CostingLevel12,
CostingLevel13,
CostingLevel14,
CostingLevel15,
CostModifier,
Created,
CustomDate01,
CustomDate02,
CustomDate03,
CustomDate04,
CustomDate05,
CustomMemo01,
CustomMemo02,
CustomMemo03,
CustomMemo04,
CustomMemo05,
CustomNumber01,
CustomNumber02,
CustomNumber03,
CustomNumber04,
CustomNumber05,
CustomText01,
CustomText02,
CustomText03,
CustomText04,
CustomText05,
CustomText06,
CustomText07,
CustomText08,
CustomText09,
CustomText10,
CustomText11,
CustomText12,
CustomText13,
CustomText14,
CustomText15,
Description,
Discontinued,
ExtendedDescription,
FolderList,
Formula,
InternalDescription,
InternalPartNumber,
ItemType,
KeywordList,
LastModified,
LastModifiedBy,
LineItemAttributes,
LineItemType,
List,
Manufacturer,
ManufacturerPartNumber,
Notes,
PictureFileName,
Price,
PriceModifier,
PriceProfile,
PricingLastUpdated,
PricingLevel01,
PricingLevel02,
PricingLevel03,
PricingLevel04,
PricingLevel05,
PricingLevel06,
PricingLevel07,
PricingLevel08,
PricingLevel09,
PricingLevel10,
PricingLevel11,
PricingLevel12,
PricingLevel13,
PricingLevel14,
PricingLevel15,
PrintPicture,
QtyMultiplier1,
QtyMultiplier2,
QtyMultiplier3,
QtyMultiplier4,
SerialNumber,
ShortDescription,
SpecSheetName,
TaxCode,
TaxRate,
UnitOfMeasure,
UnitOfMeasureFactor,
UnitOfPricing,
UnitOfPricingFactor,
UnitWeight,
Vendor,
VendorPartNumber)
SELECT
'''',--AccountingItemType,
'''',--AccountingSalesAccount,
0,--AlternateCost,
'''',--AlternateCurrency,
0,--AlternateList,
0,--AlternatePrice,
0,--Availability,
CAT.Category_Code,--Category,
'''',--CommissionProfile,
ITM.Default_Cost, --Cost,
'''',--CostAndPriceHistory,
'''',--CostingLevel01,
'''',--CostingLevel02,
'''',--CostingLevel03,
'''',--CostingLevel04,
'''',--CostingLevel05,
'''',--CostingLevel06,
'''',--CostingLevel07,
'''',--CostingLevel08,
'''',--CostingLevel09,
'''',--CostingLevel10,
'''',--CostingLevel11,
'''',--CostingLevel12,
'''',--CostingLevel13,
'''',--CostingLevel14,
'''',--CostingLevel15,
'''',--CostModifier,
GETDATE(),--Created,
NULL,--CustomDate01,
NULL,--CustomDate02,
NULL,--CustomDate03,
NULL,--CustomDate04,
NULL,--CustomDate05,
ITM.Description,--CustomMemo01,
'''',--CustomMemo02,
'''',--CustomMemo03,
'''',--CustomMemo04,
'''',--CustomMemo05,
0,--CustomNumber01,
0,--CustomNumber02,
0,--CustomNumber03,
0,--CustomNumber04,
0,--CustomNumber05,
ITM.Item_Code,--CustomText01,
''NR'',--CustomText02,
'''',--CustomText03,
0,--CustomText04,
'''',--CustomText05,
'''',--CustomText06,
'''',--CustomText07,
'''',--CustomText08,
'''',--CustomText09,
'''',--CustomText10,
'''',--CustomText11,
'''',--CustomText12,
'''',--CustomText13,
'''',--CustomText14,
'''',--CustomText15,
ITM.Description,--Description,
CASE WHEN ITM.Inactive = ''Y'' THEN 1 ELSE 0 END,--Discontinued,
ITM.Description,--ExtendedDescription,
'''',--FolderList,
'''',--Formula,
ITM.Description,--InternalDescription,
ITM.Item_Code,--InternalPartNumber,
TYP.Item_Type_Code,--ItemType,
'''',--KeywordList,
GETDATE(),--LastModified,
''SQLJob ''+CONVERT(VARCHAR,GETDATE(),101),--LastModifiedBy,
0,--LineItemAttributes,
0,--LineItemType,
ITM.Default_Rate,--List,
''Sedona'',--Manufacturer,
ITM.Item_Code,--ManufacturerPartNumber,
ITM.Description,--Notes,
'''',--PictureFileName,
CASE WHEN ITM.Item_Code LIKE ''%discount%'' THEN ITM.Default_Rate * -1 ELSE ITM.Default_Rate END,--Price,
'''',--PriceModifier*******,
'''',--PriceProfile,
GETDATE(),--PricingLastUpdated,
'''',--PricingLevel01,
'''',--PricingLevel02,
'''',--PricingLevel03,
'''',--PricingLevel04,
'''',--PricingLevel05,
'''',--PricingLevel06,
'''',--PricingLevel07,
'''',--PricingLevel08,
'''',--PricingLevel09,
'''',--PricingLevel10,
'''',--PricingLevel11,
'''',--PricingLevel12,
'''',--PricingLevel13,
'''',--PricingLevel14,
'''',--PricingLevel15,
0,--PrintPicture,
1,--QtyMultiplier1,
1,--QtyMultiplier2,
1,--QtyMultiplier3,
1,--QtyMultiplier4,
'''',--SerialNumber,
ITM.Item_Code,--ShortDescription,
'''',--SpecSheetName,
''B'',--TaxCode,
0,--TaxRate,
'''',--UnitOfMeasure,
0,--UnitOfMeasureFactor,
'''',--UnitOfPricing,
0,--UnitOfPricingFactor,
0,--UnitWeight,
''SedonaItems'',--Vendor,
ITM.Item_Code --VendorPartNumber,
FROM Multiguard_Final.dbo.AR_Item as ITM
INNER JOIN Multiguard_Final.dbo.AR_Category as CAT on ITM.Category_Id = CAT.Category_Id
INNER JOIN Multiguard_Final.dbo.SS_Item_Type as TYP on ITM.Item_Type_Id = TYP.Item_Type_Id
WHERE ITM.Item_Code NOT IN (SELECT CustomText01 FROM QuoteWerks.dbo.Products_SedonaItems_Products)
AND ITM.Sales_Item = ''Y'' AND TYP.Is_Recurring <> ''Y'' AND ITM.Inactive <> ''Y''',
@database_name=N'QuoteWerks',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [--Remove Items No Longer Marked Y For Sales] Script Date: 12/16/2025 12:55:35 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'--Remove Items No Longer Marked Y For Sales',
@step_id=7,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'DELETE QuoteWerks.dbo.Products_SedonaItems_Products
WHERE CustomText01 NOT IN
(SELECT Item_Code FROM Multiguard_Final.dbo.AR_Item as ITM
INNER JOIN Multiguard_Final.dbo.SS_Item_Type as TYP on ITM.Item_Type_Id = TYP.Item_Type_Id
WHERE ITM.Sales_Item = ''Y'' AND TYP.Is_Recurring <> ''Y'' AND ITM.Inactive <> ''Y'')',
@database_name=N'QuoteWerks',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [--Update RMR Marked Y For Sales] Script Date: 12/16/2025 12:55:35 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'--Update RMR Marked Y For Sales',
@step_id=8,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'UPDATE QTW SET
Category = CAT.Category_Code,
Cost = ITM.Default_Cost,
CustomMemo01 = ITM.Description,
CustomText01 = ITM.Item_Code,
CustomText02 = ''RS'',
Description = ITM.Description,
Discontinued = CASE WHEN ITM.Inactive = ''Y'' THEN 1 ELSE 0 END,
ExtendedDescription = ITM.Description,
InternalDescription = ITM.Description,
InternalPartNumber = ITM.Item_Code,
ItemType = TYP.Item_Type_Code,
LastModified = GETDATE(),
LastModifiedBy = ''SQLJob ''+CONVERT(VARCHAR,GETDATE(),101),
List = ITM.Default_Rate,
Manufacturer = ''Sedona'',
ManufacturerPartNumber = ITM.Item_Code,
Notes =ITM.Description,
Price = CASE WHEN ITM.Item_Code LIKE ''%discount%'' THEN ITM.Default_Rate * -1 ELSE ITM.Default_Rate END,
PricingLastUpdated = GETDATE(),
ShortDescription = ITM.Item_Code,
TaxCode = ''B'',
VendorPartNumber = ITM.Item_Code
FROM Products_SedonaRecurringItems_Products as QTW
INNER JOIN Multiguard_Final.dbo.AR_Item as ITM on QTW.CustomText01 = ITM.Item_Code
INNER JOIN Multiguard_Final.dbo.AR_Category as CAT on ITM.Category_Id = CAT.Category_Id
INNER JOIN Multiguard_Final.dbo.SS_Item_Type as TYP on ITM.Item_Type_Id = TYP.Item_Type_Id
WHERE ITM.Sales_Item = ''Y'' AND TYP.Is_Recurring = ''Y'' AND ITM.Inactive <> ''Y''
',
@database_name=N'QuoteWerks',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [--Insert New RMR Marked Y For Sales] Script Date: 12/16/2025 12:55:35 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'--Insert New RMR Marked Y For Sales',
@step_id=9,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'INSERT INTO QuoteWerks.dbo.Products_SedonaRecurringItems_Products
(AccountingItemType,
AccountingSalesAccount,
AlternateCost,
AlternateCurrency,
AlternateList,
AlternatePrice,
Availability,
Category,
CommissionProfile,
Cost,
CostAndPriceHistory,
CostingLevel01,
CostingLevel02,
CostingLevel03,
CostingLevel04,
CostingLevel05,
CostingLevel06,
CostingLevel07,
CostingLevel08,
CostingLevel09,
CostingLevel10,
CostingLevel11,
CostingLevel12,
CostingLevel13,
CostingLevel14,
CostingLevel15,
CostModifier,
Created,
CustomDate01,
CustomDate02,
CustomDate03,
CustomDate04,
CustomDate05,
CustomMemo01,
CustomMemo02,
CustomMemo03,
CustomMemo04,
CustomMemo05,
CustomNumber01,
CustomNumber02,
CustomNumber03,
CustomNumber04,
CustomNumber05,
CustomText01,
CustomText02,
CustomText03,
CustomText04,
CustomText05,
CustomText06,
CustomText07,
CustomText08,
CustomText09,
CustomText10,
CustomText11,
CustomText12,
CustomText13,
CustomText14,
CustomText15,
Description,
Discontinued,
ExtendedDescription,
FolderList,
Formula,
InternalDescription,
InternalPartNumber,
ItemType,
KeywordList,
LastModified,
LastModifiedBy,
LineItemAttributes,
LineItemType,
List,
Manufacturer,
ManufacturerPartNumber,
Notes,
PictureFileName,
Price,
PriceModifier,
PriceProfile,
PricingLastUpdated,
PricingLevel01,
PricingLevel02,
PricingLevel03,
PricingLevel04,
PricingLevel05,
PricingLevel06,
PricingLevel07,
PricingLevel08,
PricingLevel09,
PricingLevel10,
PricingLevel11,
PricingLevel12,
PricingLevel13,
PricingLevel14,
PricingLevel15,
PrintPicture,
QtyMultiplier1,
QtyMultiplier2,
QtyMultiplier3,
QtyMultiplier4,
SerialNumber,
ShortDescription,
SpecSheetName,
TaxCode,
TaxRate,
UnitOfMeasure,
UnitOfMeasureFactor,
UnitOfPricing,
UnitOfPricingFactor,
UnitWeight,
Vendor,
VendorPartNumber)
SELECT
'''',--AccountingItemType,
'''',--AccountingSalesAccount,
0,--AlternateCost,
'''',--AlternateCurrency,
0,--AlternateList,
0,--AlternatePrice,
0,--Availability,
CAT.Category_Code,--Category,
'''',--CommissionProfile,
ITM.Default_Cost, --Cost,
'''',--CostAndPriceHistory,
'''',--CostingLevel01,
'''',--CostingLevel02,
'''',--CostingLevel03,
'''',--CostingLevel04,
'''',--CostingLevel05,
'''',--CostingLevel06,
'''',--CostingLevel07,
'''',--CostingLevel08,
'''',--CostingLevel09,
'''',--CostingLevel10,
'''',--CostingLevel11,
'''',--CostingLevel12,
'''',--CostingLevel13,
'''',--CostingLevel14,
'''',--CostingLevel15,
'''',--CostModifier,
GETDATE(),--Created,
NULL,--CustomDate01,
NULL,--CustomDate02,
NULL,--CustomDate03,
NULL,--CustomDate04,
NULL,--CustomDate05,
ITM.Description,--CustomMemo01,
'''',--CustomMemo02,
'''',--CustomMemo03,
'''',--CustomMemo04,
'''',--CustomMemo05,
0,--CustomNumber01,
0,--CustomNumber02,
0,--CustomNumber03,
0,--CustomNumber04,
0,--CustomNumber05,
ITM.Item_Code,--CustomText01,
''RS'',--CustomText02,
'''',--CustomText03,
0,--CustomText04,
'''',--CustomText05,
'''',--CustomText06,
'''',--CustomText07,
'''',--CustomText08,
'''',--CustomText09,
'''',--CustomText10,
'''',--CustomText11,
'''',--CustomText12,
'''',--CustomText13,
'''',--CustomText14,
'''',--CustomText15,
ITM.Description,--Description,
CASE WHEN ITM.Inactive = ''Y'' THEN 1 ELSE 0 END,--Discontinued,
ITM.Description,--ExtendedDescription,
'''','''',--Formula,
ITM.Description,--InternalDescription,
ITM.Item_Code,--InternalPartNumber,
TYP.Item_Type_Code,--ItemType,
'''',--KeywordList,
GETDATE(),--LastModified,
''SQLJob ''+CONVERT(VARCHAR,GETDATE(),101),--LastModifiedBy,
0,--LineItemAttributes,
0,--LineItemType,
ITM.Default_Rate,--List,
''Sedona'',--Manufacturer,
ITM.Item_Code,--ManufacturerPartNumber,
ITM.Description,--Notes,
'''',--PictureFileName,
CASE WHEN ITM.Item_Code LIKE ''%discount%'' THEN ITM.Default_Rate * -1 ELSE ITM.Default_Rate END,--Price,
'''',--PriceModifier*******,
'''',--PriceProfile,
GETDATE(),--PricingLastUpdated,
'''',--PricingLevel01,
'''',--PricingLevel02,
'''',--PricingLevel03,
'''',--PricingLevel04,
'''',--PricingLevel05,
'''',--PricingLevel06,
'''',--PricingLevel07,
'''',--PricingLevel08,
'''',--PricingLevel09,
'''',--PricingLevel10,
'''',--PricingLevel11,
'''',--PricingLevel12,
'''',--PricingLevel13,
'''',--PricingLevel14,
'''',--PricingLevel15,
0,--PrintPicture,
1,--QtyMultiplier1,
1,--QtyMultiplier2,
1,--QtyMultiplier3,
1,--QtyMultiplier4,
'''',--SerialNumber,
ITM.Item_Code,--ShortDescription,
'''',--SpecSheetName,
''B'',--TaxCode,
0,--TaxRate,
'''',--UnitOfMeasure,
0,--UnitOfMeasureFactor,
'''',--UnitOfPricing,
0,--UnitOfPricingFactor,
0,--UnitWeight,
''SedonaItems'',--Vendor,
ITM.Item_Code --VendorPartNumber,
FROM Multiguard_Final.dbo.AR_Item as ITM
INNER JOIN Multiguard_Final.dbo.AR_Category as CAT on ITM.Category_Id = CAT.Category_Id
INNER JOIN Multiguard_Final.dbo.SS_Item_Type as TYP on ITM.Item_Type_Id = TYP.Item_Type_Id
WHERE ITM.Item_Code NOT IN (SELECT CustomText01 FROM QuoteWerks.dbo.Products_SedonaRecurringItems_Products)
AND ITM.Sales_Item = ''Y'' AND TYP.Is_Recurring = ''Y'' AND ITM.Inactive <> ''Y''',
@database_name=N'QuoteWerks',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [--Remove RMR No Longer Marked Y For Sales] Script Date: 12/16/2025 12:55:35 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'--Remove RMR No Longer Marked Y For Sales',
@step_id=10,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'DELETE QuoteWerks.dbo.Products_SedonaRecurringItems_Products
WHERE CustomText01 NOT IN
(SELECT Item_Code FROM Multiguard_Final.dbo.AR_Item as ITM
INNER JOIN Multiguard_Final.dbo.SS_Item_Type as TYP on ITM.Item_Type_Id = TYP.Item_Type_Id
WHERE ITM.Sales_Item = ''Y'' AND TYP.Is_Recurring = ''Y'' AND ITM.Inactive <> ''Y'')',
@database_name=N'QuoteWerks',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Daily @ 10PM',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20251001,
@active_end_date=99991231,
@active_start_time=220000,
@active_end_time=235959,
@schedule_uid=N'26864100-517c-4988-86ae-53ac406509fc'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO
