If you are using Dynamics GP with MEM and run into the error "This batch is not accessible", you are likely missing the MEM scripts.
Below is a script to fix the broken transactions.
-- CLEAN UP BROKEN TRANSACTIONS.
-- This needs to be run on the COMPANY database.
-- This script will make missing transactions viewable by adding any missing links in the database
-- It will not *FIX* the transactions (put into correct batches etc)
-- batches should be deleted from inside GP
-- Create temp table to log all changes
CREATE TABLE #BatchCleanupLog (
Action NVARCHAR(50),
BACHNUMB NVARCHAR(15),
VCHNUMWK NVARCHAR(17),
BSSI_Facility_ID NVARCHAR(15),
Details NVARCHAR(200),
Timestamp DATETIME DEFAULT GETDATE()
)
PRINT 'Starting batch cleanup process...'
PRINT 'Created logging table #BatchCleanupLog'
-- Step 1: Insert missing batch records into B3900900
PRINT 'Step 1: Adding missing batch records to B3900900...'
INSERT INTO B3900900
OUTPUT 'B3900900 INSERT', INSERTED.BACHNUMB, '', INSERTED.BSSI_Facility_ID, 'Added missing batch record for PM_Trxent', GETDATE()
INTO #BatchCleanupLog (Action, BACHNUMB, VCHNUMWK, BSSI_Facility_ID, Details, Timestamp)
SELECT 'PM_Trxent',p.BACHNUMB,k.BSSI_Facility_ID,0,0,0,0,'',0
FROM PM10000 p
LEFT JOIN B3900900 b ON b.BACHNUMB = p.BACHNUMB
CROSS APPLY (SELECT TOP 1 k.BSSI_Facility_ID FROM B3900200 k) k
WHERE b.BACHNUMB IS NULL
GROUP BY p.BACHNUMB, k.BSSI_Facility_ID
-- Step 2: Insert missing voucher records into B3920000
PRINT 'Step 2: Adding missing voucher records to B3920000...'
-- First, log what we're about to insert (since OUTPUT might not work with complex joins)
INSERT INTO #BatchCleanupLog (Action, BACHNUMB, VCHNUMWK, BSSI_Facility_ID, Details, Timestamp)
SELECT 'B3920000 INSERT', p.BACHNUMB, p.VCHNUMWK, b.BSSI_Facility_ID, 'Added missing voucher record', GETDATE()
FROM PM10000 p
JOIN B3900900 b ON b.BACHNUMB = p.BACHNUMB
LEFT JOIN B3920000 B2 ON b2.VCHNUMWK = p.VCHNUMWK
WHERE b2.VCHNUMWK IS NULL
-- Now do the actual insert
INSERT INTO B3920000
SELECT 1, p.VCHNUMWK, b.BSSI_Facility_ID, '', '', 1, VADDCDPR, 0
FROM PM10000 p
JOIN B3900900 b ON b.BACHNUMB = p.BACHNUMB
LEFT JOIN B3920000 B2 ON b2.VCHNUMWK = p.VCHNUMWK
WHERE b2.VCHNUMWK IS NULL
-- Step 3: Insert missing batch headers into SY00500
PRINT 'Step 3: Adding missing batch headers to SY00500...'
-- First, log what we're about to insert
INSERT INTO #BatchCleanupLog (Action, BACHNUMB, VCHNUMWK, BSSI_Facility_ID, Details, Timestamp)
SELECT 'SY00500 INSERT', b.BACHNUMB, '', '', 'Added missing batch header record', GETDATE()
FROM B3900900 b
LEFT JOIN SY00500 s ON b.BACHNUMB = s.BACHNUMB
CROSS APPLY (
SELECT TOP 1 *
FROM SY00500 ss
WHERE ss.BCHSOURC='PM_Trxent'
AND ss.BCHCOMNT LIKE 'Batch Created by Fidesic%'
) ss
WHERE s.BACHNUMB IS NULL
-- Now do the actual insert
INSERT INTO SY00500 (GLPOSTDT,BCHSOURC,BACHNUMB,SERIES,MKDTOPST,NUMOFTRX,RECPSTGS,DELBACH,MSCBDINC,BACHFREQ,RCLPSTDT,NOFPSTGS,BCHCOMNT,BRKDNALL,CHKSPRTD,RVRSBACH,USERID,CHEKBKID,BCHTOTAL,BCHEMSG1,BCHEMSG2,BACHDATE,BCHSTRG1,BCHSTRG2,POSTTOGL,MODIFDT,CREATDDT,NOTEINDX,CURNCYID,BCHSTTUS,CNTRLTRX,CNTRLTOT,PETRXCNT,APPROVL,APPRVLDT,APRVLUSERID,ORIGIN,ERRSTATE,GLBCHVAL,Computer_Check_Doc_Date,Sort_Checks_By,SEPRMTNC,REPRNTED,CHKFRMTS,TRXSORCE,PmtMethod,EFTFileFormat,Workflow_Approval_Status,Workflow_Priority,Workflow_Status,TIME1,ClearRecAmts,PurchasingPrepaymentBch,CARDNAME)
SELECT ss.GLPOSTDT, ss.BCHSOURC, b.BACHNUMB, ss.SERIES, ss.MKDTOPST, ss.NUMOFTRX, ss.RECPSTGS, ss.DELBACH,
ss.MSCBDINC, ss.BACHFREQ, ss.RCLPSTDT, ss.NOFPSTGS, ss.BCHCOMNT, ss.BRKDNALL, ss.CHKSPRTD, ss.RVRSBACH, ss.USERID, ss.CHEKBKID, ss.BCHTOTAL, ss.BCHEMSG1, ss.BCHEMSG2,
ss.BACHDATE, ss.BCHSTRG1, ss.BCHSTRG2, ss.POSTTOGL, ss.MODIFDT, ss.CREATDDT, ss.NOTEINDX, ss.CURNCYID, ss.BCHSTTUS, ss.CNTRLTRX, ss.CNTRLTOT, ss.PETRXCNT, ss.APPROVL,
ss.APPRVLDT, ss.APRVLUSERID, ss.ORIGIN, ss.ERRSTATE, ss.GLBCHVAL, ss.Computer_Check_Doc_Date, ss.Sort_Checks_By, ss.SEPRMTNC, ss.REPRNTED, ss.CHKFRMTS,
ss.TRXSORCE, ss.PmtMethod, ss.EFTFileFormat, ss.Workflow_Approval_Status, ss.Workflow_Priority, ss.Workflow_Status, ss.TIME1,
ss.ClearRecAmts, ss.PurchasingPrepaymentBch, ss.CARDNAME
FROM B3900900 b
LEFT JOIN SY00500 s ON b.BACHNUMB = s.BACHNUMB
CROSS APPLY (
SELECT TOP 1 *
FROM SY00500 ss
WHERE ss.BCHSOURC='PM_Trxent'
AND ss.BCHCOMNT LIKE 'Batch Created by Fidesic%'
) ss
WHERE s.BACHNUMB IS NULL
-- Step 4: Update facility IDs for invalid facilities
PRINT 'Step 4: Updating invalid facility IDs...'
UPDATE b
SET BSSI_Facility_ID = k.BSSI_Facility_ID
OUTPUT 'FACILITY UPDATE', INSERTED.BACHNUMB, '', INSERTED.BSSI_Facility_ID,
'Updated facility ID from [' + ISNULL(DELETED.BSSI_Facility_ID, 'NULL') + '] to [' + INSERTED.BSSI_Facility_ID + ']',
GETDATE()
INTO #BatchCleanupLog (Action, BACHNUMB, VCHNUMWK, BSSI_Facility_ID, Details, Timestamp)
FROM B3900900 b
LEFT JOIN B3900800 f ON RTRIM(f.BSSI_Facility_ID) = RTRIM(b.BSSI_Facility_ID)
CROSS APPLY (SELECT TOP 1 BSSI_Facility_ID FROM B3900800) k
WHERE f.BSSI_Facility_ID IS NULL
PRINT 'Batch cleanup process completed.'
PRINT ''
-- Show summary of what was corrected
PRINT '=== BATCH CLEANUP SUMMARY ==='
SELECT
Action,
COUNT(*) as RecordsAffected,
MIN(Timestamp) as StartTime,
MAX(Timestamp) as EndTime
FROM #BatchCleanupLog
GROUP BY Action
ORDER BY MIN(Timestamp)
PRINT ''
PRINT '=== DETAILED LOG OF ALL CHANGES ==='
SELECT
FORMAT(Timestamp, 'yyyy-MM-dd HH:mm:ss') as [Time],
Action,
BACHNUMB as [Batch Number],
ISNULL(VCHNUMWK, '') as [Voucher Number],
ISNULL(BSSI_Facility_ID, '') as [Facility ID],
Details
FROM #BatchCleanupLog
ORDER BY Timestamp, Action
-- Show total counts
DECLARE @TotalRecords INT = (SELECT COUNT(*) FROM #BatchCleanupLog)
DECLARE @TotalBatches INT = (SELECT COUNT(DISTINCT BACHNUMB) FROM #BatchCleanupLog)
PRINT ''
PRINT '=== FINAL STATISTICS ==='
PRINT 'Total records modified: ' + CAST(@TotalRecords AS VARCHAR(10))
PRINT 'Total unique batches affected: ' + CAST(@TotalBatches AS VARCHAR(10))
-- Optionally, uncomment the next line if you want to keep the log table for further analysis
-- PRINT 'Log table #BatchCleanupLog has been preserved for further analysis'
-- Clean up the temp table (comment this out if you want to keep it)
DROP TABLE #BatchCleanupLog
PRINT 'Cleanup complete.'