CJSmith dot me

I dump stuff I find useful here

Category: Cindercone Magma

Populate Sage table opheadm with credit note information for Cindercone Magma to send to Tungsten/OB10

Published / by Chris Smith / Leave a Comment

The below stored proc is used to populate two Sage fields shipper_code2 and shipper_code3 in the Order Header Table.
Obiously if this is used already by another Sage Project do not use, as the fields won’t be updated.
Here I look 30 days backwards and at particular invoice customers (There is no point updating every invoice entry), for an initial run you may want to increase the amount of days on both update queries

USE <Sage_Company_DB>
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:           Christopher Smith
-- Create date: 01/11/2017
-- Description:      Populate opheadm fields shipper_code2 and shipper_code3 with related invoice information.
-- Magma EDI then uses this information to provide Tungsten with required information.
-- =============================================
-- Changes
-- 1.0 - 01/11/2017 - Initial Version
-- 1.0.1 - 08/11/2017 - Improve behaviour when encountering locked records
-- =============================================
-- exec dbo.usp_tungsten_credit_notes
-- =============================================
CREATE PROCEDURE [dbo].[usp_tungsten_credit_notes] 
AS
BEGIN
--Whole tran will rollback if timeout occurs
print 'Set Lock Timeout to 10 Seconds'
SET LOCK_TIMEOUT 1000;  
--SELECT @@LOCK_TIMEOUT AS [Lock Timeout];
BEGIN TRAN
print 'work out invoice number number Credit Note Order Number'
update scheme.opheadm
       set shipper_code2 = 'OP/' + SUBSTRING(order_no,3,7)
       where invoice_customer IN ('<Sage_Invoice_Customers>') and order_no LIKE 'CNI%'
              and invoice_no LIKE 'OP/C%' and invoice_date > GETDATE() -30  and shipper_code2 = ''
print 'Use the new invoice number to pull across the invoice date'
update t1
	set shipper_code3 = replace(convert(varchar, t2.invoice_date,103),'/','')
       FROM scheme.opheadm t1
              INNER JOIN scheme.opheadm t2
                     ON t1.shipper_code2 = t2.invoice_no and t1.customer = t2.customer
       where t1.invoice_customer IN ('<Sage_Invoice_Customers>') and t1.order_no LIKE 'CNI%' and t1.invoice_no LIKE 'OP/C%' and t1.invoice_date > GETDATE() -30 and t1.shipper_code3 = ''
COMMIT TRAN
END
GO