Page Comparison

USE [msdb]
GO

-- =============================================
-- Author: Anita Middleton
-- Company: Logisense
-- Create Date: Nov 2, 2017
-- Description: Modifies TaxLog_Master table with new TaxLog.IDs
-- History: Created for ticket #106505
-- =============================================

BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0

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'TaxLog_Master',
@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'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Inserts] Script Date: 1/9/2018 12:47:19 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Inserts',
@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'exec usp_TaxLog_Master_Update',
@database_name=N'EngageIP',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Updates] Script Date: 1/9/2018 12:47:19 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Updates',
@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'/* updated the TaxLog_Master invoice number
in cases where an invoice gets voided and
TaxLog_Master has already been added */

UPDATE tlm
SET InvoiceID = isc.InvoiceID
FROM TaxLog_Master tlm
INNER JOIN TaxLog tl WITH (NOLOCK) ON tlm.ID = tl.ID
INNER JOIN StatementDetails sd WITH (NOLOCK) ON tl.StatementDetailsID = sd.ID
INNER JOIN InvoiceStatementConnector isc WITH (NOLOCK) ON sd.StatementID = isc.StatementID
WHERE tlm.InvoiceID <> isc.InvoiceID
--and tlm.InvoiceID in (112225,109467)

UPDATE tlm
SET InvoiceID = sdcic.InvoiceID
FROM TaxLog_Master tlm
INNER JOIN TaxLog tl WITH (NOLOCK) ON tlm.ID = tl.ID
INNER JOIN StatementDetailsCreditInvoiceConnector sdcic WITH (NOLOCK) ON tl.StatementDetailsID = sdcic.StatementDetailsID
WHERE tlm.InvoiceID <> sdcic.InvoiceID
--and tlm.InvoiceID in (112225,109467)

UPDATE tlm
SET InvoiceID = ub.InvoiceID
FROM TaxLog_Master tlm
INNER JOIN TaxLog tl WITH (NOLOCK) ON tlm.ID = tl.ID
INNER JOIN UDRBiller ub WITH (NOLOCK) ON tl.UDRBillerID = ub.ID
WHERE tlm.InvoiceID <> ub.InvoiceID
--and tlm.InvoiceID in (112225,109467)

/* update tax month */
UPDATE tlm
SET TaxMonth = CAST(DATEADD(dd, -DAY(i.Date) + 1, i.Date) AS DATE)
FROM TaxLog_Master tlm
INNER JOIN Invoice i WITH (NOLOCK) ON tlm.InvoiceID = i.ID
WHERE tlm.TaxMonth <> CAST(DATEADD(dd, -DAY(i.Date) + 1, i.Date) AS DATE)

',
@database_name=N'EngageIP',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Delete Voided Items] Script Date: 1/9/2018 12:47:19 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Delete Voided Items',
@step_id=3,
@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'/* updated the TaxLog_Master to delete
IDs on voided invoices */

DELETE tlm
FROM TaxLog_Master tlm
INNER JOIN TaxLog tl WITH (NOLOCK) ON tlm.ID = tl.ID
INNER JOIN StatementDetails sd WITH (NOLOCK) ON tl.StatementDetailsID = sd.ID
INNER JOIN InvoiceStatementConnector isc WITH (NOLOCK) ON sd.StatementID = isc.StatementID
INNER JOIN ViewInvoice i WITH (NOLOCK) ON isc.InvoiceID = i.ID
WHERE i.Void != ''no''

DELETE tlm
FROM TaxLog_Master tlm
INNER JOIN TaxLog tl WITH (NOLOCK) ON tlm.ID = tl.ID
INNER JOIN StatementDetailsCreditInvoiceConnector sdcic WITH (NOLOCK) ON tl.StatementDetailsID = sdcic.StatementDetailsID
INNER JOIN ViewInvoice i WITH (NOLOCK) ON sdcic.InvoiceID = i.ID
WHERE i.Void != ''no''

DELETE tlm
FROM TaxLog_Master tlm
INNER JOIN TaxLog tl WITH (NOLOCK) ON tlm.ID = tl.ID
INNER JOIN UDRBiller ub WITH (NOLOCK) ON tl.UDRBillerID = ub.ID
INNER JOIN ViewInvoice i WITH (NOLOCK) ON ub.InvoiceID = i.ID
WHERE i.Void != ''no''
',
@database_name=N'EngageIP',
@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'Schedule',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=8,
@freq_subday_interval=1,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20171102,
@active_end_date=99991231,
@active_start_time=0,
@active_end_time=235959,
@schedule_uid=N'76bf65da-10f8-4c11-a725-869de3c39c19'
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