I wrote a simple script to aid users moving to a new password policy, users were put on a policy which would expire a password after a day. Once they had changed their password the policy is amended to the new policy. I now use this for forcing password resets. Replace and with your Sage Line 500/1000 password policies

-- =============================================
-- Author:           Christopher Smith
-- Create date: 18/07/2018
-- Description:      Implement the new password policy in stages.
-- 1. Change the password policy first to enforce a password change in a short time period
-- 2. Backup and truncate the table [csmaster].[scheme].[pwdhistm]
-- 3. Run this routine nightly, it will pickup users who have changed their password and set the new policy
-- =============================================
-- Change Log:
-- 1.0 - 18/07/2018 - First Version
-- =============================================
-- exec dbo.usp_SageImplementPWPolicy
-- exec [dbo].[usp_ForceSagePWChange] 'csmithte'
-- =============================================
CREATE PROCEDURE [dbo].[usp_SageImplementPWPolicy]
AS
BEGIN
       SET NOCOUNT ON;
-- =============================================
print 'Declare Variables and setup'
-- =============================================
  declare @spdate1 date
  declare @spdate2 int
  declare @secgroup char(3)
  declare @oldsecgroup char(4)
-- Set variable to 2 days ago
  set @spdate1 = CAST(GETDATE() as date)
  print @spdate1
-- Convert the date to one which matches the Sage table
  set @spdate2 = DATEDIFF(d,'01 jan 1900',@spdate1)
  print 'Sage Date Code for Today'
  print @spdate2
  print 'New user security group'
  set @secgroup = ''
  set @oldsecgroup = ''
  print @secgroup
  print @oldsecgroup
-- =============================================
print 'Populate working table'
-- =============================================
       select t1.name, dateadd(d,t2."setup",'01 jan 1900') as pw_date, t1.sec_group, 'NO ' as pw_changed into #pwworking
              from scheme.[usermastm](nolock) t1
                     INNER JOIN scheme.passexpm(nolock) t2
                           on t1.name = t2.user_id
              where t1.sec_group = @oldsecgroup
       select * from #pwworking
-- =============================================
print 'Check if password recently changed'
-- =============================================
       update #pwworking
              set pw_changed = 'YES'
                     where name IN
                           (select "username" from [csmaster].[scheme].[pwdhistm] group by "username")
       select * from #pwworking
-- =============================================
print 'Change security group for those who have a new password on the new policy'
-- =============================================
       update scheme.[usermastm]
              set sec_group = @secgroup
                     where name IN (select name from #pwworking where pw_changed = 'YES')
drop table #pwworking
END
GO

Below is the code used to force users to enter a new password upon login

USE [csmaster]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[usp_ForceSagePWChange] @user char(8)
--EXEC usp_ForceSagePWChange ''
AS
--IF @user = 'ALL' GOTO ALLUSERS
--IF @user != 'ALL' GOTO SINGLEUSER
BEGIN
/*ALLUSERS:
update scheme.passexpm
set setup = '10001'
GOTO FINISH
SINGLEUSER:*/

update csmaster.scheme.passexpm
set setup = '10001'
where user_id = @user
update csmaster.[scheme].[usermastm]
set [sec_group] = ''
where name = @user
GOTO FINISH
FINISH:
END

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:

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