Account Structure cannot be deleted

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
UNION ALL

    SELECT VIJ.COSTLEDGERVOUCHER AS 'DOCUMENT', SD.NAME
    FROM VENDINVOICEJOUR VIJ INNER JOIN
        SOURCEDOCUMENTHEADER SDH ON VIJ.SOURCEDOCUMENTHEADER=SDH.RECID AND VIJ.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 = 'VENDINVOICEJOUR'
        AND A.ACCOUNTINGEVENT NOT IN (SELECT RECID
        FROM ACCOUNTINGEVENT)
    group by VIJ.COSTLEDGERVOUCHER, sd.NAME
UNION ALL

    SELECT CIT.INVOICEID AS 'DOCUMENT', SD.NAME
    FROM CUSTINVOICETABLE CIT INNER JOIN
        SOURCEDOCUMENTHEADER SDH ON CIT.SOURCEDOCUMENTHEADER=SDH.RECID AND CIT.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 = 'CUSTINVOICETABLE'
        AND A.ACCOUNTINGEVENT NOT IN (SELECT RECID
        FROM ACCOUNTINGEVENT)
    group by CIT.INVOICEID, sd.NAME
UNION ALL

    SELECT TET.EXPNUMBER AS 'DOCUMENT', SD.NAME
    FROM TRVEXPTABLE TET INNER JOIN
        SOURCEDOCUMENTHEADER SDH ON TET.SOURCEDOCUMENTHEADER=SDH.RECID AND TET.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 = 'TRVEXPTABLE'
        AND A.ACCOUNTINGEVENT NOT IN (SELECT RECID
        FROM ACCOUNTINGEVENT)
    group by TET.EXPNUMBER, sd.NAME
UNION ALL

    SELECT TST.TIMESHEETNBR AS 'DOCUMENT', SD.NAME
    FROM TSTIMESHEETTABLE TST INNER JOIN
        SOURCEDOCUMENTHEADER SDH ON TST.SOURCEDOCUMENTHEADER=SDH.RECID AND TST.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 = 'TSTIMESHEETTABLE'
        AND A.ACCOUNTINGEVENT NOT IN (SELECT RECID
        FROM ACCOUNTINGEVENT)
    group by TST.TIMESHEETNBR, sd.NAME
UNION ALL

    SELECT VPSJ.PACKINGSLIPID AS 'DOCUMENT', SD.NAME
    FROM VENDPACKINGSLIPJOUR VPSJ INNER JOIN
        SOURCEDOCUMENTHEADER SDH ON VPSJ.SOURCEDOCUMENTHEADER=SDH.RECID AND VPSJ.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 = 'TSTIMESHEETTABLE'
        AND A.ACCOUNTINGEVENT NOT IN (SELECT RECID
        FROM ACCOUNTINGEVENT)
    group by VPSJ.PACKINGSLIPID, sd.NAME
UNION ALL

    SELECT TRT.REQUISITIONNUMBER AS 'DOCUMENT', SD.NAME
    FROM TRVREQUISITIONTABLE TRT INNER JOIN
        SOURCEDOCUMENTHEADER SDH ON TRT.SOURCEDOCUMENTHEADER=SDH.RECID AND TRT.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 = 'TRVREQUISITIONTABLE'
        AND A.ACCOUNTINGEVENT NOT IN (SELECT RECID
        FROM ACCOUNTINGEVENT)
    group by TRT.REQUISITIONNUMBER, sd.NAME
UNION ALL

    SELECT TRT.REQUISITIONNUMBER AS 'DOCUMENT', SD.NAME
    FROM TRVREQUISITIONTABLE TRT INNER JOIN
        SOURCEDOCUMENTHEADER SDH ON TRT.SOURCEDOCUMENTHEADER=SDH.RECID AND TRT.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 = 'TRVREQUISITIONTABLE'
        AND A.ACCOUNTINGEVENT NOT IN (SELECT RECID
        FROM ACCOUNTINGEVENT)
    group by TRT.REQUISITIONNUMBER, sd.NAME
UNION ALL

    SELECT ALEH.TRANSACTIONNUMBER AS 'DOCUMENT', SD.NAME
    FROM ADVANCEDLEDGERENTRYHEADER ALEH INNER JOIN
        SOURCEDOCUMENTHEADER SDH ON ALEH.SOURCEDOCUMENTHEADER=SDH.RECID AND ALEH.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 = 'ADVANCEDLEDGERENTRYHEADER'
        AND A.ACCOUNTINGEVENT NOT IN (SELECT RECID
        FROM ACCOUNTINGEVENT)
    group by ALEH.TRANSACTIONNUMBER, sd.NAME
UNION ALL

    SELECT VIIT.NUM AS 'DOCUMENT', SD.NAME
    FROM VENDINVOICEINFOTABLE VIIT INNER JOIN
        SOURCEDOCUMENTHEADER SDH ON VIIT.SOURCEDOCUMENTHEADER=SDH.RECID AND VIIT.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 = 'VENDINVOICEINFOTABLE'
        AND A.ACCOUNTINGEVENT NOT IN (SELECT RECID
        FROM ACCOUNTINGEVENT)
    group by VIIT.NUM, sd.NAME
UNION ALL

    SELECT PES.DOCUMENTNUMBER AS 'DOCUMENT', SD.NAME
    FROM PAYROLLEARNINGSTATEMENT PES INNER JOIN
        SOURCEDOCUMENTHEADER SDH ON PES.SOURCEDOCUMENTHEADER=SDH.RECID AND PES.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 = 'PAYROLLEARNINGSTATEMENT'
        AND A.ACCOUNTINGEVENT NOT IN (SELECT RECID
        FROM ACCOUNTINGEVENT)
    group by PES.DOCUMENTNUMBER, sd.NAME
UNION ALL

    SELECT PPS.DOCUMENTNUMBER AS 'DOCUMENT', SD.NAME
    FROM PAYROLLPAYSTATEMENT PPS INNER
        JOIN SOURCEDOCUMENTHEADER SDH ON PPS.SOURCEDOCUMENTHEADER=SDH.RECID AND PPS.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 = 'PAYROLLPAYSTATEMENT'
        AND A.ACCOUNTINGEVENT NOT IN (SELECT RECID
        FROM ACCOUNTINGEVENT)
    group by PPS.DOCUMENTNUMBER, sd.NAME