Subledger entries are not transferred to ledger Dynamics 365 F&O, but doesnt reflect in the Subledger entries not yet transferred

 There have been several instances in D365 FO where the "sub-ledger entries not yet transferred" form is empty but the ledger entry has not got posted yet.

In order to identify such cases Microsoft has provided the below temporary fix through an SQL query. Good news according to Microsoft is that versions after PU37 will not have this subledger issues any longer. 

--IDENTIFY THE VOUCHERS THAT WERE MARKED FOR TRANSFER BUT THE BATCH JOB FAILED IN MIDDLE OF TRANSFER --

SELECT * FROM SUBLEDGERJOURNALENTRY SJE WHERE SJE.TRANSFERID != 0
AND NOT EXISTS(SELECT NULL FROM SUBLEDGERVOUCHERGENERALJOURNALENTRY SJV WHERE SJV.VOUCHER = SJE.VOUCHER AND SJV.VOUCHERDATAAREAID = SJE.VOUCHERDATAAREAID)

--FIX THE ENTRIES THAT ARE STUCK --

UPDATE AE
SET AE.STATE = 2
FROM ACCOUNTINGEVENT AE
JOIN SUBLEDGERJOURNALENTRY SJE ON SJE.ACCOUNTINGEVENT = AE.RECID AND SJE.TRANSFERID != 0
AND NOT EXISTS(SELECT NULL FROM SUBLEDGERVOUCHERGENERALJOURNALENTRY SJV WHERE SJV.VOUCHER = SJE.VOUCHER AND SJV.VOUCHERDATAAREAID = SJE.VOUCHERDATAAREAID)

 

UPDATE SJE
SET SJE.STATUS = 0,
SJE.TRANSFERID = 0
FROM SUBLEDGERJOURNALENTRY SJE WHERE SJE.TRANSFERID != 0
AND NOT EXISTS(SELECT NULL FROM SUBLEDGERVOUCHERGENERALJOURNALENTRY SJV WHERE SJV.VOUCHER = SJE.VOUCHER AND SJV.VOUCHERDATAAREAID = SJE.VOUCHERDATAAREAID)