Account Structure cannot be deleted

#GeneralLedger #SourceDocument #CustomerInvoice #VendorInvoice #AccountsPayable #AccountStructure #AccountsReceivable #Budget #PostingProfile #Tax #CoA #DimensionFramework

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
AccountingDistributionDimensionStructureChangeProvider::checkIfStuctureRemovalFromLedgerIsPrevented

Account Structure cannot be deleted because it is in use by a Free Text Invoice template in ledger USMF. You must first update the free text invoice template
Recurring Free Text Invoice template has Main Account Attached
CustInvoiceDimensionStructureSynchronizationProvider::checkIfStuctureRemovalFromLedgerIsPrevented

Account Structure cannot be deleted because it is in use by a Budget Control in ledger USMF. You must first update the budget Control configuration
Budget Configuration has Main Account Attached
BudgetControlDimensionStructureSynchronizationProvider::checkIfStuctureRemovalFromLedgerIsPrevented

Account Structure cannot be deleted because it is in use by a draft Budget Account Entry in ledger USMF. You must first update the draft budget Account Entry
Have draft version BRE need to be posted or deleted
BudgetDimensionStructureSynchronizationProvider::checkIfStuctureRemovalFromLedgerIsPrevented

Account Structure cannot be deleted because it is in use by a Budget Plan Line in ledger USMF. You must first update the Budget Plan Line
Budget Plan Line has Main Account Attached
BudgetPlanDimensionStructureSynchronizationProvider::checkIfStuctureRemovalFromLedgerIsPrevented

Account Structure cannot be deleted because it is in use by a General Ledger in ledger USMF. You must first update the general journal
Check General Journal and process them
LedgerJournalDimensionStructureSynchronizationProvider::checkIfStuctureRemovalFromLedgerIsPrevented

Account Structure cannot be deleted because it is in use by a Posting Definition in ledger USMF. You must first update the general journal
Check General Journal and process them
JournalizingDefinitionDimensionStructureSynchronizationProvider::checkIfStuctureRemovalFromLedgerIsPrevented

Account Structure cannot be deleted because it is in use by a Uncommitted Tax in ledger USMF. You must first update the Uncommitted Tax

Account Structure cannot be deleted because it is in use by Ledger Account Alias in ledger USMF. You must first update the Ledger Account Alia
Ledger Account Alias
DimensionAliasStructureSynchronizationProvider::checkIfStuctureRemovalFromLedgerIsPrevented

Account structure Manufacturing P&L cannot be deleted because it is in use in ledger USMF , You must first update the deduction type template.
TAMDeductionTypeSynchronizationProvider::checkIfStuctureRemovalFromLedgerIsPrevented

Source:

Cause:

Migration:
Find Source document - Script
QueryToFindUnpostedSourceDocuments.txt

Find unposted Source Document for removing Account Structure

AccountingEvent
AccountingDistribution
DimensionAttributeValueCombination
DimensionAttributeValueGroup
DimensionAttributeValueGroupCombination
SQLDictionary
SourceDocumentHeader
SourceDocumentLine

Source Document Table

PurchTable
PurchReqTable
VendInvoiceJour
CustInvoiceTable
TrvExpTable
TSTimeSheetTable
VendPackingSlipJour
TrvRequistionTable
AdvancedLedgerEntryHeader
VendInvoiceInfoTable
PayrollEarningStatement
PayrollPayStatement

AccountingDistributionDimensionStructureChangeProvider

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

Query for Tax Uncommitted Records

declare @dimensionhierarchy nvarchar (20);
declare @dataareaid nvarchar (4);
declare @partition bigint;
  
  select @dimensionhierarchy = 'manufacturing p&L';
  select @dataareaid = 'usmf';
  select @partition = [partition] from ledger where [name] = @dataareaid;

  select e.PURCHID, e.PURCHSTATUS from taxuncommitted a 
  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 purchtable e on a.headingrecid=e.RECID and a.partition=e.PARTITION
  inner join accountingdistribution f on a.sourcedocumentline=f.SOURCEDOCUMENTLINE and a.partition=f.PARTITION
  inner join dimensionhierarchy g on d.dimensionhierarchy=g.recid and d.partition=g.partition
  inner join sqldictionary h on a.headingtableid=h.TABLEID
  where a.posted = '0' 
  and a.dataareaid = @dataareaid 
  and e.documentstate = '40' 
  and f.referencerole <> '1'
  and g.name = @dimensionhierarchy
  and h.name = 'purchtable'
  and h.fieldid = '0'
  group by e.purchid, e.purchstatus
  union all
  
  select e.PURCHID, e.PURCHSTATUS from taxuncommitted a 
  inner join dimensionattributevaluecombination b on a.OPERATIONLEDGERDIMENSION=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 purchtable e on a.headingrecid=e.RECID and a.partition=e.PARTITION
  inner join accountingdistribution f on a.sourcedocumentline=f.SOURCEDOCUMENTLINE and a.partition=f.PARTITION
  inner join dimensionhierarchy g on d.dimensionhierarchy=g.recid and d.partition=g.partition
  inner join sqldictionary h on a.headingtableid=h.TABLEID
  where a.posted = '0' 
  and a.dataareaid = @dataareaid 
  and e.documentstate = '40' 
  and f.referencerole <> '1'
  and g.name = @dimensionhierarchy
  and h.name = 'purchtable'
  and h.fieldid = '0'
  group by e.purchid, e.purchstatus
  union all
  
  select e.PURCHID, e.PURCHSTATUS from taxuncommitted a 
  inner join dimensionattributevaluecombination b on a.TAXOFFSETUSETAXLEDGERDIMENSION=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 purchtable e on a.headingrecid=e.RECID and a.partition=e.PARTITION
  inner join accountingdistribution f on a.sourcedocumentline=f.SOURCEDOCUMENTLINE and a.partition=f.PARTITION
  inner join dimensionhierarchy g on d.dimensionhierarchy=g.recid and d.partition=g.partition
  inner join sqldictionary h on a.headingtableid=h.TABLEID
  where a.posted = '0' 
  and a.dataareaid = @dataareaid 
  and e.documentstate = '40' 
  and f.referencerole <> '1'
  and g.name = @dimensionhierarchy
  and h.name = 'purchtable'
  and h.fieldid = '0'
  group by e.purchid, e.purchstatus

Query for Ledger Journal Trans

-- General Journal - Primary account

  declare @dimensionhierarchy nvarchar (20);
  declare @dataareaid nvarchar (4);
  declare @partition bigint;
  declare @accountinglegalentity bigint;
  declare @dimensionHierarchyRecId bigint;
 
  select @dimensionhierarchy = [enter dimension hierarchy here]; -- ie 'manufacturing p&L'
  select @dataareaid = [enter dataareaid here]; -- ie 'usmf'
  select @partition = [partition] from ledger where [name] = @dataareaid;
  select @accountinglegalentity = [PRIMARYFORLEGALENTITY] from ledger where [name] = @dataareaid;
  select @dimensionHierarchyRecId = [RECID] from DimensionHierarchy where [Name] = @dimensionHierarchy;
 

SELECT DISTINCT T1.JOURNALNUM 
FROM LEDGERJOURNALTRANS T1  
WHERE ((T1.PARTITION=@partition) 
    AND (((T1.ACCOUNTTYPE=0) 
    AND (T1.CANCEL=0)) 
    AND (T1.COMPANY=@dataareaid))) 
    AND 
EXISTS (
SELECT TOP 1 T2.RECID
FROM DIMENSIONATTRIBUTEVALUECOMBINATION T2 
WHERE ((T2.PARTITION=@partition) 
    AND ((T2.RECID=T1.LEDGERDIMENSION) 
    AND ((T2.LEDGERDIMENSIONTYPE=0) 
    OR (T2.LEDGERDIMENSIONTYPE=2))))
    AND 
EXISTS (
SELECT TOP 1 T3.RECID 
FROM DIMENSIONATTRIBUTEVALUEGROUPCOMBINATION T3 
WHERE ((T3.PARTITION=@partition) 
    AND (T3.DIMENSIONATTRIBUTEVALUECOMBINATION=T2.RECID))
    AND 
EXISTS (
SELECT TOP 1 T4.RECID 
FROM DIMENSIONATTRIBUTEVALUEGROUP T4 
WHERE ((T4.PARTITION=@partition) 
    AND ((T4.RECID=T3.DIMENSIONATTRIBUTEVALUEGROUP) 
    AND (T4.DIMENSIONHIERARCHY=@dimensionHierarchyRecId)))
    AND 
EXISTS (
SELECT TOP 1 T5.RECID 
FROM LEDGERJOURNALTABLE T5 
WHERE (((T5.PARTITION=@partition) 
    AND (T5.DATAAREAID=@dataareaid)) 
    AND (((((T5.JOURNALNUM=T1.JOURNALNUM) 
    AND (T5.POSTED=0)) 
    AND (T5.JOURNALTYPE<>25)) 
    AND (T5.JOURNALTYPE<>118)) 
    AND (T5.JOURNALTYPE<>119)))))))

SELECT DISTINCT T1.JOURNALNUM 
FROM LEDGERJOURNALTRANS T1  
WHERE ((T1.PARTITION=@partition) 
    AND (((T1.ACCOUNTTYPE=0) 
    AND (T1.CANCEL=0)) 
    AND (T1.OFFSETCOMPANY=@dataareaid))) 
    AND 
EXISTS (
SELECT TOP 1 T2.RECID
FROM DIMENSIONATTRIBUTEVALUECOMBINATION T2 
WHERE ((T2.PARTITION=@partition) 
    AND ((T2.RECID=T1.OffsetLedgerLEDGERDIMENSION) 
    AND ((T2.LEDGERDIMENSIONTYPE=0) 
    OR (T2.LEDGERDIMENSIONTYPE=2))))
    AND 
EXISTS (
SELECT TOP 1 T3.RECID 
FROM DIMENSIONATTRIBUTEVALUEGROUPCOMBINATION T3 
WHERE ((T3.PARTITION=@partition) 
    AND (T3.DIMENSIONATTRIBUTEVALUECOMBINATION=T2.RECID))
    AND 
EXISTS (
SELECT TOP 1 T4.RECID 
FROM DIMENSIONATTRIBUTEVALUEGROUP T4 
WHERE ((T4.PARTITION=@partition) 
    AND ((T4.RECID=T3.DIMENSIONATTRIBUTEVALUEGROUP) 
    AND (T4.DIMENSIONHIERARCHY=@dimensionHierarchyRecId)))
    AND 
EXISTS (
SELECT TOP 1 T5.RECID 
FROM LEDGERJOURNALTABLE T5 
WHERE (((T5.PARTITION=@partition) 
    AND (T5.DATAAREAID=@dataareaid)) 
    AND (((((T5.JOURNALNUM=T1.JOURNALNUM) 
    AND (T5.POSTED=0)) 
    AND (T5.JOURNALTYPE<>25)) 
    AND (T5.JOURNALTYPE<>118)) 
    AND (T5.JOURNALTYPE<>119)))))))

Source document headers with distributions using dimension combinations that use the dimension hierarchy being removed

---Replace the placeholder 'manufacturing p&l' below with the customer value
SELECT sdh.RECID, * from SOURCEDOCUMENTHEADER SDH
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 = 'manufacturing p&L'
  AND SD.FIELDID = '0'
  AND A.REFERENCEROLE <> '1'
  AND (B.LEDGERDIMENSIONTYPE = '0'
  OR B.LEDGERDIMENSIONTYPE = '2')
  AND A.ACCOUNTINGEVENT NOT IN (SELECT RECID FROM ACCOUNTINGEVENT)

Related Tables

Source Document Table

Related Class

Related Form

Query for Source Document

Query for Tax Uncommitted Records

Query for Ledger Journal Trans

Source document headers with distributions using dimension combinations that use the dimension hierarchy being removed