Category: Sage Line 500 ERP

Sage Line 500 V7.1 CSAgent TCR and Form appear to start but ports never open

Published July 20, 2019 / by Chris Smith / Leave a Comment

Recently we had this issue where after windows patches were applied, after a reboot the Sage Line 500 v7.1 Windows services no longer worked on our development and live servers. It was due to the Visual Studio dll msvcr71.dll being removed SYSWOW64 (32 Bit systems will be System32 and you can see in process monitor where the processes try to access the dll file and are unable to find the dll)

This can be copied back from a client Sage Line 500 install, as I found the dll was actually still registered

One possible cause I can think of is the registry entry for the dll in HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\SharedDLLs is set to 1, an installer upgrades a program to a newer version of Visual Studio and takes one away from the dll entry in the registry, where by it thinks it is no longer used and deletes the dll.

Posted In Sage Line 500 ERP

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

Published June 09, 2019 / 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

USE <Sage_Company_DB>

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

-- =============================================

-- 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]

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

Posted In Cindercone Magma | Sage Line 500 ERP | SQL Server

Sage EDI IN Intermediary Email

Published May 01, 2019 / by Chris Smith / Leave a Comment

I have found users don’t always read the logs from the Adaptus EDI Module anymore in Sage Line 500.
There are a few reasons why orders get stuck, the most common is a customer has sent a duplicate EDI file although one was caused by incorrect product supersession setup causing an endless loop.

USE <Affected Sage Company MSSQL Database>
GO
CREATE procedure [dbo].[usp_email_edi_held_orders]
AS
-- =============================================
-- Author:		Christopher Smith
-- Create date: 01/08/2017
-- Description:	The procedure looks for orders held in the EDI IN Intermediary Tables
-- Changes:
-- 1.0.1 - 02/08/2017 - Added dateformat dmy as when run was reporting "Conversion failed when converting date and/or time from character string.".  Ensured all date values were cast as datetime as well.
-- 1.0.2 - 11/04/2019 - Corrected the Sales Order lookup to check the invoice customer instead of delivery customer on opheadm
-- =============================================
-- exec [dbo].[usp_email_edi_held_orders]
-- =============================================
--------------------------------------------------------------------------------------------------------------------------
print 'Change to DMY format'
--------------------------------------------------------------------------------------------------------------------------
SET DATEFORMAT dmy;
--------------------------------------------------------------------------------------------------------------------------
print 'Populate temp table'
--------------------------------------------------------------------------------------------------------------------------
Create Table #results(
	customer VARCHAR(8),
	existingorderno varchar(10),
	received_date char(8),
	customer_order VARCHAR(20),
	"filename" char(30),
	warehouse char(2),
	product char(20),
	long_description char(40),
	required_quantity int
	)
insert into #results (customer,received_date,customer_order,"filename",warehouse,product,long_description,required_quantity) 
	SELECT customer,received_date,customer_order,"filename",warehouse,product,long_description,required_quantity
	FROM "scheme"."eiorhdm" t1
		INNER JOIN "scheme"."eiordtm" t2
			ON t1.key_code = LEFT(t2.key_code,LEN(RTRIM(t1.key_code)))
	WHERE t1.update_status = 'h' and CAST(received_date as datetime) BETWEEN CAST(GETDATE() -7 as datetime) and CAST(GETDATE() as datetime)
--------------------------------------------------------------------------------------------------------------------------
print 'Check if need to send email'
--------------------------------------------------------------------------------------------------------------------------
if (select count(*) AS nos from #results) = 0 
begin
print 'No Email to Send'
goto END1
end 
--------------------------------------------------------------------------------------------------------------------------
print 'Check if existing SOP number exists and update table'
--------------------------------------------------------------------------------------------------------------------------
update t1
	set t1.existingorderno = t2.order_no
		from #results t1
			INNER JOIN scheme.opheadm(nolock) t2
				ON t1.customer_order = t2.customer_order_no and t1.customer = t2.invoice_customer
update #results
	set existingorderno = 'NOMATCH'
		from #results
		where existingorderno IS NULL
--------------------------------------------------------------------------------------------------------------------------
print 'Create HTML for Email'
--------------------------------------------------------------------------------------------------------------------------

DECLARE @tableHTML  NVARCHAR(MAX) ;

SET @tableHTML =
    N'<H1>Orders held in the EDI IN Intermediary Tables</H1>' +
	N'<font size="5" face="arial" color="red"> <H2>Please review orders held in the EDI IN module, if the order held does not match an existing order further checks may need to be done</H2></font>' +
    N'<table border="1">' +
    N'<tr><th>Customer No</th><th>Existing SO No</th>' +
    N'<th>Customer Order No</th><th>EDI Filename</th><th>Received Date</th>' +
	N'<th>WH</th><th>Product</th>' +
	N'<th>Description</th><th>Qty</th>' +
    CAST ( ( select td =   customer,'',
	td = existingorderno,'',
	td = customer_order,'',
	td = "filename",'',
	td = received_date,'',
	td = warehouse,'',
	td = product,'',
	td = long_description,'',
	td = required_quantity
from #results
order by customer, customer_order
FOR XML PATH('tr'), TYPE ) AS NVARCHAR(MAX) ) + 
N'</table>'  + 
N'<p> </p>' + 
N'<p> </p>' + 
N'<p>Regards.</p>' + 
N'<p> </p>' +
N'<p>usp_email_edi_held_orders</p>' 
--------------------------------------------------------------------------------------------------------------------------
print 'Send Email'
--------------------------------------------------------------------------------------------------------------------------
    EXEC msdb.dbo.sp_send_dbmail
    @profile_name = '<SQL Email Profile>',
	@recipients = '<Recipients>',
    @copy_recipients = '<Copy_Address>',
	@body = @tableHTML,
    @body_format = 'HTML',
	@subject = 'Orders held in the EDI IN Intermediary Tables'
END1:

100

101

USE <Affected Sage Company MSSQL Database>

CREATE procedure [dbo].[usp_email_edi_held_orders]

-- =============================================

-- Author: Christopher Smith

-- Create date: 01/08/2017

-- Description: The procedure looks for orders held in the EDI IN Intermediary Tables

-- Changes:

-- 1.0.1 - 02/08/2017 - Added dateformat dmy as when run was reporting "Conversion failed when converting date and/or time from character string.". Ensured all date values were cast as datetime as well.

-- 1.0.2 - 11/04/2019 - Corrected the Sales Order lookup to check the invoice customer instead of delivery customer on opheadm

-- =============================================

-- exec [dbo].[usp_email_edi_held_orders]

-- =============================================

--------------------------------------------------------------------------------------------------------------------------

print 'Change to DMY format'

--------------------------------------------------------------------------------------------------------------------------

SET DATEFORMAT dmy;

--------------------------------------------------------------------------------------------------------------------------

print 'Populate temp table'

--------------------------------------------------------------------------------------------------------------------------

Create Table #results(

customer VARCHAR(8),

existingorderno varchar(10),

received_date char(8),

customer_order VARCHAR(20),

"filename" char(30),

warehouse char(2),

product char(20),

long_description char(40),

required_quantity int

)

insert into #results (customer,received_date,customer_order,"filename",warehouse,product,long_description,required_quantity)

SELECT customer,received_date,customer_order,"filename",warehouse,product,long_description,required_quantity

FROM "scheme"."eiorhdm" t1

INNER JOIN "scheme"."eiordtm" t2

ON t1.key_code = LEFT(t2.key_code,LEN(RTRIM(t1.key_code)))

WHERE t1.update_status = 'h' and CAST(received_date as datetime) BETWEEN CAST(GETDATE() -7 as datetime) and CAST(GETDATE() as datetime)

--------------------------------------------------------------------------------------------------------------------------

print 'Check if need to send email'

--------------------------------------------------------------------------------------------------------------------------

if (select count(*) AS nos from #results) = 0

begin

print 'No Email to Send'

goto END1

end

--------------------------------------------------------------------------------------------------------------------------

print 'Check if existing SOP number exists and update table'

--------------------------------------------------------------------------------------------------------------------------

update t1

set t1.existingorderno = t2.order_no

from #results t1

INNER JOIN scheme.opheadm(nolock) t2

ON t1.customer_order = t2.customer_order_no and t1.customer = t2.invoice_customer

update #results

set existingorderno = 'NOMATCH'

from #results

where existingorderno IS NULL

--------------------------------------------------------------------------------------------------------------------------

print 'Create HTML for Email'

--------------------------------------------------------------------------------------------------------------------------

DECLARE @tableHTML NVARCHAR(MAX) ;

SET @tableHTML =

N'<H1>Orders held in the EDI IN Intermediary Tables</H1>' +

N'<font size="5" face="arial" color="red"> <H2>Please review orders held in the EDI IN module, if the order held does not match an existing order further checks may need to be done</H2></font>' +

N'<table border="1">' +

N'<tr><th>Customer No</th><th>Existing SO No</th>' +

N'<th>Customer Order No</th><th>EDI Filename</th><th>Received Date</th>' +

N'<th>WH</th><th>Product</th>' +

N'<th>Description</th><th>Qty</th>' +

CAST ( ( select td = customer,'',

td = existingorderno,'',

td = customer_order,'',

td = "filename",'',

td = received_date,'',

td = warehouse,'',

td = product,'',

td = long_description,'',

td = required_quantity

from #results

order by customer, customer_order

FOR XML PATH('tr'), TYPE ) AS NVARCHAR(MAX) ) +

N'</table>' +

N'<p> </p>' +

N'<p>Regards.</p>' +

N'<p> </p>' +

N'<p>usp_email_edi_held_orders</p>'

--------------------------------------------------------------------------------------------------------------------------

print 'Send Email'

--------------------------------------------------------------------------------------------------------------------------

EXEC msdb.dbo.sp_send_dbmail

@profile_name = '<SQL Email Profile>',

@recipients = '<Recipients>',

@copy_recipients = '<Copy_Address>',

@body = @tableHTML,

@body_format = 'HTML',

@subject = 'Orders held in the EDI IN Intermediary Tables'

END1:

Posted In Sage Line 500 ERP | SQL Server 2008 | SQL Server 2012

DBForms: Check and format email addresses from Sage Line 500/1000

Published October 28, 2018 / by Chris Smith / Leave a Comment

As the email field can sometimes not be used for the correct purpose I use a bit of code to look for an @ symbol and then a dot. If more than one email address is in I replace the comma with -t which is required by dbmailsend.
If using Sage Line 500/1000 the CustomerTable will be scheme.slcustm
I expect the DBForms def below to pickup the customer code already so there is only a lookup to the customers email address.

In [INPUT]
 
VARIABLE=EMAIL_ADDRESS
VARIABLE=ISQL_CMD , , "<PATHTOOSQL.exe>"
 
In [EXTRACT]
LET EMAIL_ADDRESS= EXECUTE(((ISQL_CMD + " -h-1 -S <SQLSERVER> -U <USER> -P <PASSWORD> -d <DATABASE> -Q\"set nocount on;select email from <CustomerTable> where customer='") + <CUSTOMERVARIABLE>) + "'")
DEBUG ("Email >" + EMAIL_ADDRESS) + "<"
 
 
In [DELIVERY]
LET EMAIL_ADDRESS= TRIM(EMAIL_RESULT)
                NOTE Check for valid email address; is there an @?
                IF  INSTR(EMAIL_ADDRESS,"@",1) = 0 THEN
                {
                        LET EMAIL_ADDRESS=""
                        DEBUG "Email address invalid"
                }
                NOTE Check for valid email address; are there spaces?
                NOTE Check for valid email address; is there at least one full stop?
                IF  INSTR(EMAIL_ADDRESS,".",1) = 0 THEN
                {
                        LET EMAIL_ADDRESS=""
                        DEBUG "Email address invalid2"
                }
                DEBUG ("Email B >" + EMAIL_ADDRESS) + "<"
                NOTE Format EMAIL_ADDRESS Variable if more than one email address entered.
                IF  INSTR(EMAIL_ADDRESS,",",1) <> 0 THEN
                {
                        LET EMAIL_ADDRESS= REPLACE_STRING(EMAIL_ADDRESS,",","\" -t \"")
                        DEBUG ("Email C >" + EMAIL_ADDRESS) + "<"
                }
 
 
 
In [OUTPUT]
 
OUTPUT=<EMAILOUTPUTNAME>
INVOCATION="<pathtodbmailsend>"
PARAMETERS=
{
        "-f"
        QUOTED <EmailAddress>
        "-r"
        QUOTED <EmailAddress>
        "-c"
        QUOTED <EmailAddress>
        "-s"
        QUOTED <EMAILSUBJECT>
        "-a"
        <ATTACHMENT>
        "-t"
        QUOTED EMAIL_ADDRESS
        OUTPUT_FILE
}

In [INPUT]

VARIABLE=EMAIL_ADDRESS

VARIABLE=ISQL_CMD , , "<PATHTOOSQL.exe>"

In [EXTRACT]

LET EMAIL_ADDRESS= EXECUTE(((ISQL_CMD + " -h-1 -S <SQLSERVER> -U <USER> -P <PASSWORD> -d <DATABASE> -Q\"set nocount on;select email from <CustomerTable> where customer='") + <CUSTOMERVARIABLE>) + "'")

DEBUG ("Email >" + EMAIL_ADDRESS) + "<"

In [DELIVERY]

LET EMAIL_ADDRESS= TRIM(EMAIL_RESULT)

NOTE Check for valid email address; is there an @?

IF INSTR(EMAIL_ADDRESS,"@",1) = 0 THEN

{

LET EMAIL_ADDRESS=""

DEBUG "Email address invalid"

}

NOTE Check for valid email address; are there spaces?

NOTE Check for valid email address; is there at least one full stop?

IF INSTR(EMAIL_ADDRESS,".",1) = 0 THEN

{

LET EMAIL_ADDRESS=""

DEBUG "Email address invalid2"

}

DEBUG ("Email B >" + EMAIL_ADDRESS) + "<"

NOTE Format EMAIL_ADDRESS Variable if more than one email address entered.

IF INSTR(EMAIL_ADDRESS,",",1) <> 0 THEN

{

LET EMAIL_ADDRESS= REPLACE_STRING(EMAIL_ADDRESS,",","\" -t \"")

DEBUG ("Email C >" + EMAIL_ADDRESS) + "<"

}

In [OUTPUT]

OUTPUT=<EMAILOUTPUTNAME>

INVOCATION="<pathtodbmailsend>"

PARAMETERS=

{

"-f"

QUOTED <EmailAddress>

"-r"

QUOTED <EmailAddress>

"-c"

QUOTED <EmailAddress>

"-s"

QUOTED <EMAILSUBJECT>

"-a"

"-t"

QUOTED EMAIL_ADDRESS

OUTPUT_FILE

}

Posted In Sage Line 500 ERP | V1 DBForms

Sage Line 500 EDI IN Intermediary tables inner join on sql query

Published August 12, 2017 / by Chris Smith / Leave a Comment

On ocassion I need to look at what is in the EDI IN modules intermediary tables, scheme.eiordtm has an annoying key_code which it combines multiple objects. It also doesn’t always have the same amount of characters. As the key_code on scheme.eiordtm starts with the key_code from scheme.eiorhdm all I do is find out how many characters are in key_code from scheme.eiorhdm and use the SQL LEFT function on the join.
Additional columns can be added but this is just the defaults I use.

SELECT customer,received_date,customer_order,"filename",warehouse,product,long_description,required_quantity
 FROM "scheme"."eiorhdm" t1
  INNER JOIN "scheme"."eiordtm" t2
   ON t1.key_code = LEFT(t2.key_code,LEN(RTRIM(t1.key_code)))

SELECT customer,received_date,customer_order,"filename",warehouse,product,long_description,required_quantity

FROM "scheme"."eiorhdm" t1

INNER JOIN "scheme"."eiordtm" t2

ON t1.key_code = LEFT(t2.key_code,LEN(RTRIM(t1.key_code)))

Posted In Sage Line 500 ERP | SQL Server 2008

CJSmith dot me

I dump stuff I find useful here

Category: Sage Line 500 ERP

Sage Line 500 V7.1 CSAgent TCR and Form appear to start but ports never open

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

Sage EDI IN Intermediary Email

DBForms: Check and format email addresses from Sage Line 500/1000

Sage Line 500 EDI IN Intermediary tables inner join on sql query