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