Symptoms:
Account structure Manufacturing B/S cannot be deleted because it is in use by the general journal in ledger USMF. You must first update the general journal.
Account Structure cannot be deleted because there are unposted source documents in ledger USMF
Query for Source Document
declare @dimensionhierarchy nvarchar (20);
declare @dataareaid nvarchar (4);
declare @partition bigint;
declare @accountinglegalentity bigint;
select @dimensionhierarchy = 'manufacturing p&L';
select @dataareaid = 'usmf';
select @partition = [partition] from ledger where [name] = @dataareaid;
select @accountinglegalentity = [PRIMARYFORLEGALENTITY] from ledger where [name] = @dataareaid;
SELECT PT.PURCHID AS 'DOCUMENT', SD.NAME FROM PURCHTABLE PT
INNER JOIN SOURCEDOCUMENTHEADER SDH ON PT.SOURCEDOCUMENTHEADER=SDH.RECID AND PT.PARTITION=SDH.PARTITION
INNER JOIN ACCOUNTINGDISTRIBUTION A ON SDH.RECID=A.SOURCEDOCUMENTHEADER AND SDH.PARTITION=A.PARTITION
INNER JOIN DIMENSIONATTRIBUTEVALUECOMBINATION B ON A.LEDGERDIMENSION=B.RECID AND A.PARTITION=B.PARTITION
INNER JOIN DIMENSIONATTRIBUTEVALUEGROUPCOMBINATION C ON B.RECID=C.DIMENSIONATTRIBUTEVALUECOMBINATION AND B.PARTITION=C.PARTITION
INNER JOIN DIMENSIONATTRIBUTEVALUEGROUP D ON C.DIMENSIONATTRIBUTEVALUEGROUP=D.RECID AND C.PARTITION=D.PARTITION
INNER JOIN DIMENSIONHIERARCHY E ON D.DIMENSIONHIERARCHY=E.RECID AND D.PARTITION=E.PARTITION
INNER JOIN SQLDICTIONARY SD ON SDH.SOURCERELATIONTYPE = SD.TABLEID
WHERE E.NAME = @dimensionhierarchy
AND SD.FIELDID = '0'
AND A.REFERENCEROLE <> '1'
AND (B.LEDGERDIMENSIONTYPE = '0'
OR B.LEDGERDIMENSIONTYPE = '2')
AND A.ACCOUNTINGLEGALENTITY = @accountinglegalentity
AND SD.NAME = 'PURCHTABLE'
AND A.ACCOUNTINGEVENT NOT IN (SELECT RECID FROM ACCOUNTINGEVENT)
group by pt.PURCHID, sd.NAME
UNION ALL
SELECT PRT.PURCHREQID AS 'DOCUMENT', SD.NAME FROM PURCHREQTABLE PRT
INNER JOIN SOURCEDOCUMENTHEADER SDH ON PRT.SOURCEDOCUMENTHEADER=SDH.RECID AND PRT.PARTITION=SDH.PARTITION
INNER JOIN ACCOUNTINGDISTRIBUTION A ON SDH.RECID=A.SOURCEDOCUMENTHEADER AND SDH.PARTITION=A.PARTITION
INNER JOIN DIMENSIONATTRIBUTEVALUECOMBINATION B ON A.LEDGERDIMENSION=B.RECID AND A.PARTITION=B.PARTITION
INNER JOIN DIMENSIONATTRIBUTEVALUEGROUPCOMBINATION C ON B.RECID=C.DIMENSIONATTRIBUTEVALUECOMBINATION AND B.PARTITION=C.PARTITION
INNER JOIN DIMENSIONATTRIBUTEVALUEGROUP D ON C.DIMENSIONATTRIBUTEVALUEGROUP=D.RECID AND C.PARTITION=D.PARTITION
INNER JOIN DIMENSIONHIERARCHY E ON D.DIMENSIONHIERARCHY=E.RECID AND D.PARTITION=E.PARTITION
INNER JOIN SQLDICTIONARY SD ON SDH.SOURCERELATIONTYPE = SD.TABLEID
WHERE E.NAME = @dimensionhierarchy
AND SD.FIELDID = '0'
AND A.REFERENCEROLE <> '1'
AND (B.LEDGERDIMENSIONTYPE = '0'
OR B.LEDGERDIMENSIONTYPE = '2')
AND A.ACCOUNTINGLEGALENTITY = @accountinglegalentity
AND SD.NAME = 'PURCHREQTABLE'
AND A.ACCOUNTINGEVENT NOT IN (SELECT RECID FROM ACCOUNTINGEVENT)
group by pRt.PURCHREQID, sd.NAME