SQL Scripts for iPaaS.com

All SQL scripts used in Counterpoint for work on iPaaS.com

Add Location Group "IPAAS"
begin tran IF NOT EXISTS (SELECT 1 FROM IM_LOC_GRP where LOC_GRP_ID = 'IPAAS') BEGIN INSERT INTO [dbo].[IM_LOC_GRP]([LOC_GRP_ID],[DESCR],[DESCR_UP...
Fri, May 22, 2020 at 9:46 AM
Create Procedure USR_MAG_ITEM_EXP_RUN_PRC_AFTER
IF OBJECT_ID('USR_MAG_ITEM_EXP_RUN_PRC_AFTER', 'P') IS NULL EXEC ('CREATE PROCEDURE [USR_MAG_ITEM_EXP_RUN_PRC_AFTER] as BEGIN SELEC...
Fri, May 22, 2020 at 10:02 AM
Create SQL Agent Job [ipaas - update location specific inventory] (YOU MUST CHANGE DATABASE AND OWNER)
-- CHANGE ALL PARTS IN BOLD USE [msdb] GO /*Delete existing Job [ipaas - update location specific inventory]*/ --EXEC msdb.dbo.sp_delete_job @job_id=N...
Fri, May 22, 2020 at 9:54 AM
Create Trigger Copy Web Prices to Items Table for IPaaS
--You must change which price field is used. The code below uses PRC_2 IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'IM_...
Fri, May 22, 2020 at 10:42 AM
Create Trigger to Copy HTML description to Item Table
IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'IM_ITEM' AND COLUMN_NAME = 'USR_HTML_DESCR') BEGIN ALTER TAB...
Fri, May 22, 2020 at 10:19 AM
Create Trigger USR_COPY_BARCOD_JSON for copying barcodes in JSON format
IF  NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[USR_COPY_BARCOD_JSON]') AND type in (N'TR')) EXEC ('...
Fri, May 22, 2020 at 10:01 AM
Query: Barcodes in JSON Format
select im_item.item_no, '['+ REPLACE( REPLACE( REPLACE( REPLACE( (select barcod_id, barcod from im_barcod where item_no = im_item.item_no and barc...
Fri, May 22, 2020 at 9:47 AM
Turn On Items Subscriptions
update USR_CPHIVE_SUBSCRIPTION set SUBSCRIBED = 'Y' where scope like 'product%' and scope not like '%deleted'
Fri, May 22, 2020 at 9:48 AM