Skip to content
English
  • There are no suggestions because the search field is empty.

This batch is not accessible (MEM only)

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.

To fix broken or inaccessible transactions in Microsoft Dynamics GP, run the SQL script below against your company database. The script restores visibility of missing transactions by repairing broken database links.

This script does not correct underlying batch data. After running the script, review and delete the affected batches from within GP at Purchasing > Transactions > Batches.

What are the prerequisites before running this script?

  • Run this script on the COMPANY database (not DYNAMICS or master).
  • Take a full database backup before executing.
  • Ensure all GP users are logged out before running.
  • SQL Server access is required — contact your database administrator if needed.

What does this script do?

  1. Adds missing batch records to B3900900
  2. Adds missing voucher records to B3920000
  3. Adds missing batch headers to SY00500
  4. Corrects invalid facility IDs in B3900900
 

-- 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 
-- NOTE: This script will NOT fully fix transactions (e.g., move them into correct batches).
-- After running this script, batches should be reviewed and deleted from within Microsoft Dynamics GP (Purchasing > Transactions > Batches).

-- 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: Drops the temporary log table. Comment out the DROP statement below if you want to retain the log for further review.
DROP TABLE #BatchCleanupLog
PRINT 'Cleanup complete.'