Ledger Transactions with split out Financial Dimensions

Submitted by Nathan Clouse on 09/30/15.

A view that provides a list of Ledger Transactions with all of the financial dimensions split out for all available financial dimensions. This depends on several other views and could be different per partition. Depending on the company or main account, not all financial dimensions may be available but whatever is in the ledger account for a ledger transaction, it will be broken out into it elements. For instance, ledger account 1234-56-78-90 would have 1234 in one column, 56 in another column, 78 in another and 90 in another. This was implemented for Contoso data so your specific implementation will most likely be different. Please see this post for how to build it for your financial dimension.

AX Version:

AX 2012

Module:

General Ledger

CREATE VIEW 
[dbo].[vw_ledgerGeneralJournalSubledgerVoucherSliptDim] 
AS 
  SELECT 'LedgerAccount' = dimAttrValComb.displayvalue, 
         mainAcc.mainaccountid, 
         'MainAccountName' = mainAcc.NAME, 
         'Agreement' = AgreementTags.value, 
         'BusinessUnit' = BusinessUnitTags.value, 
         'Cashflow_CN' = Cashflow_CNTags.value, 
         'CostCenter' = CostCenterTags.value, 
         'CostCenter_CN' = CostCenter_CNTags.value, 
         'Department' = DepartmentTags.value, 
         'Department_CN' = Department_CNTags.value, 
         'ExpenseAndIncomeCode' = 
            ExpenseAndIncomeCodeTags.value, 
         'Filial' = FilialTags.value, 
         'FiscalEstablishment' = 
  FiscalEstablishmentTags.value, 
         'ItemGroup' = 
            ItemGroupTags.value, 
         'LegalEntity' = LegalEntityTags.value, 
         'Ownership_CN' = Ownership_CNTags.value, 
         'Project' = ProjectTags.value, 
         'Purpose_CN' = Purpose_CNTags.value, 
         'Retailchannel' = RetailchannelTags.value, 
         'ServiceLine' = ServiceLineTags.value, 
         'Store' = StoreTags.value, 
         'Terminal' = TerminalTags.value, 
         'Worker' = WorkerTags.value, 
         'AgreementDescription' = AgreementTags.NAME, 
         'BusinessUnitDescription' = BusinessUnitTags.NAME, 
         'Cashflow_CNDescription' = Cashflow_CNTags.NAME, 
         'CostCenterDescription' = CostCenterTags.NAME, 
         'CostCenter_CNDescription' = 
         CostCenter_CNTags.NAME, 
         'DepartmentDescription' = 
         DepartmentTags.NAME, 
         'Department_CNDescription' = 
         Department_CNTags.NAME, 
'ExpenseAndIncomeCodeDescription' = 
ExpenseAndIncomeCodeTags.NAME, 
'FilialDescription' = FilialTags.NAME, 
'FiscalEstablishmentDescription' = 
FiscalEstablishmentTags.NAME, 
'ItemGroupDescription' = ItemGroupTags.NAME, 
'LegalEntityDescription' = LegalEntityTags.NAME, 
'Ownership_CNDescription' = Ownership_CNTags.NAME, 
'ProjectDescription' = ProjectTags.NAME, 
'Purpose_CNDescription' = Purpose_CNTags.NAME, 
'RetailchannelDescription' = RetailchannelTags.NAME, 
'ServiceLineDescription' = ServiceLineTags.NAME, 
'StoreDescription' = StoreTags.NAME, 
'TerminalDescription' = TerminalTags.NAME, 
'WorkerDescription' = WorkerTags.NAME, 
genJourEntry.accountingdate, 
genJourEntry.journalcategory, 
genJourAccEntry.postingtype, 
'CurrencyCode' = 
   genJourAccEntry.transactioncurrencycode, 
genJourAccEntry.transactioncurrencyamount, 
genJourAccEntry.accountingcurrencyamount, 
genJourAccEntry.reportingcurrencyamount, 
'MainAccountRecId' = genJourEntry.ledger, 
'GeneralJournalEntry' = genJourEntry.recid, 
genJourEntry.fiscalcalendarperiod, 
'LedgerVoucher' = subLedVouGenJourEntry.voucher, 
'DataAreaId' = 
   subLedVouGenJourEntry.voucherdataareaid, 
genJourEntry.ledgerentryjournal, 
genJourEntry.documentnumber, 
'CategoryRef' = mainAcc.accountcategoryref, 
genJourAccEntry.partition, 
ledEntryJour.journalnumber, 
'ValueCombinationRecId' = dimAttrValComb.recid, 
genJourAccEntry.text, 
genJourEntry.createdby, 
genJourEntry.createddatetime 
FROM   generaljournalaccountentry AS genJourAccEntry 
JOIN generaljournalentry AS genJourEntry 
  ON genJourEntry.recid = 
     genJourAccEntry.generaljournalentry 
     AND genJourEntry.partition = 
         genJourAccEntry.partition 
JOIN subledgervouchergeneraljournalentry AS 
     subLedVouGenJourEntry 
  ON subLedVouGenJourEntry.generaljournalentry = 
          genJourEntry.recid 
     AND subLedVouGenJourEntry.partition = 
         genJourEntry.partition 
INNER JOIN dimensionattributevaluecombination AS 
           dimAttrValComb 
        ON dimAttrValComb.recid = 
           genJourAccEntry.ledgerdimension 
           AND dimAttrValComb.partition = 
               genJourAccEntry.partition 
LEFT JOIN ledgerentryjournal AS ledEntryJour 
       ON ledEntryJour.recid = 
          genJourEntry.ledgerentryjournal 
          AND ledEntryJour.partition = 
              genJourEntry.partition 
INNER JOIN mainaccount AS mainAcc 
        ON mainAcc.recid = dimAttrValComb.mainaccount 
           AND mainAcc.partition = 
               dimAttrValComb.partition 
LEFT JOIN vw_ledgersplitdimensionentity AS dimension 
       ON dimension.valuecombinationrecid = 
                    genJourAccEntry.ledgerdimension 
          AND dimension.partition = 
              genJourAccEntry.partition 
LEFT JOIN vw_ledgerallfinancialtags AS AgreementTags 
       ON AgreementTags.recid = dimension.agreement 
          AND AgreementTags.partition = 
              dimension.partition 
          AND AgreementTags.dimensionname = 
              'Agreement' 
LEFT JOIN vw_ledgerallfinancialtags AS 
          BusinessUnitTags 
       ON BusinessUnitTags.recid = 
          dimension.businessunit 
          AND BusinessUnitTags.partition = 
              dimension.partition 
          AND BusinessUnitTags.dimensionname = 
              'BusinessUnit' 
LEFT JOIN vw_ledgerallfinancialtags AS 
          Cashflow_CNTags 
       ON Cashflow_CNTags.recid = 
          dimension.cashflow_cn 
          AND Cashflow_CNTags.partition = 
              dimension.partition 
          AND Cashflow_CNTags.dimensionname = 
              'Cashflow_CN' 
LEFT JOIN vw_ledgerallfinancialtags AS CostCenterTags 
       ON CostCenterTags.recid = dimension.costcenter 
          AND CostCenterTags.partition = 
              dimension.partition 
          AND CostCenterTags.dimensionname = 
              'CostCenter' 
LEFT JOIN vw_ledgerallfinancialtags AS 
          CostCenter_CNTags 
       ON CostCenter_CNTags.recid = 
          dimension.costcenter_cn 
          AND CostCenter_CNTags.partition = 
              dimension.partition 
          AND CostCenter_CNTags.dimensionname = 
              'CostCenter_CN' 
LEFT JOIN vw_ledgerallfinancialtags AS DepartmentTags 
       ON DepartmentTags.recid = dimension.department 
          AND DepartmentTags.partition = 
              dimension.partition 
          AND DepartmentTags.dimensionname = 
              'Department' 
LEFT JOIN vw_ledgerallfinancialtags AS 
          Department_CNTags 
       ON Department_CNTags.recid = 
          dimension.department_cn 
          AND Department_CNTags.partition = 
              dimension.partition 
          AND Department_CNTags.dimensionname = 
              'Department_CN' 
LEFT JOIN vw_ledgerallfinancialtags AS 
          ExpenseAndIncomeCodeTags 
       ON ExpenseAndIncomeCodeTags.recid = 
                    dimension.expenseandincomecode 
          AND ExpenseAndIncomeCodeTags.partition = 
              dimension.partition 
          AND ExpenseAndIncomeCodeTags.dimensionname 
              = 
              'ExpenseAndIncomeCode' 
LEFT JOIN vw_ledgerallfinancialtags AS FilialTags 
       ON FilialTags.recid = dimension.filial 
          AND FilialTags.partition = 
              dimension.partition 
          AND FilialTags.dimensionname = 'Filial' 
LEFT JOIN vw_ledgerallfinancialtags AS 
          FiscalEstablishmentTags 
       ON FiscalEstablishmentTags.recid = 
                    dimension.fiscalestablishment 
          AND FiscalEstablishmentTags.partition = 
              dimension.partition 
          AND FiscalEstablishmentTags.dimensionname = 
              'FiscalEstablishment' 
LEFT JOIN vw_ledgerallfinancialtags AS ItemGroupTags 
       ON ItemGroupTags.recid = dimension.itemgroup 
          AND ItemGroupTags.partition = 
              dimension.partition 
          AND ItemGroupTags.dimensionname = 
              'ItemGroup' 
LEFT JOIN vw_ledgerallfinancialtags AS 
          LegalEntityTags 
       ON LegalEntityTags.recid = 
          dimension.legalentity 
          AND LegalEntityTags.partition = 
              dimension.partition 
          AND LegalEntityTags.dimensionname = 
              'LegalEntity' 
LEFT JOIN vw_ledgerallfinancialtags AS 
          Ownership_CNTags 
       ON Ownership_CNTags.recid = 
          dimension.ownership_cn 
          AND Ownership_CNTags.partition = 
              dimension.partition 
          AND Ownership_CNTags.dimensionname = 
              'Ownership_CN' 
LEFT JOIN vw_ledgerallfinancialtags AS ProjectTags 
       ON ProjectTags.recid = dimension.project 
          AND ProjectTags.partition = 
              dimension.partition 
          AND ProjectTags.dimensionname = 'Project' 
LEFT JOIN vw_ledgerallfinancialtags AS Purpose_CNTags 
       ON Purpose_CNTags.recid = dimension.purpose_cn 
          AND Purpose_CNTags.partition = 
              dimension.partition 
          AND Purpose_CNTags.dimensionname = 
              'Purpose_CN' 
LEFT JOIN vw_ledgerallfinancialtags AS 
          RetailchannelTags 
       ON RetailchannelTags.recid = 
          dimension.retailchannel 
          AND RetailchannelTags.partition = 
              dimension.partition 
          AND RetailchannelTags.dimensionname = 
              'Retailchannel' 
LEFT JOIN vw_ledgerallfinancialtags AS 
          ServiceLineTags 
       ON ServiceLineTags.recid = 
          dimension.serviceline 
          AND ServiceLineTags.partition = 
              dimension.partition 
          AND ServiceLineTags.dimensionname = 
              'ServiceLine' 
LEFT JOIN vw_ledgerallfinancialtags AS StoreTags 
       ON StoreTags.recid = dimension.store 
          AND StoreTags.partition = 
              dimension.partition 
          AND StoreTags.dimensionname = 'Store' 
LEFT JOIN vw_ledgerallfinancialtags AS TerminalTags 
       ON TerminalTags.recid = dimension.terminal 
          AND TerminalTags.partition = 
              dimension.partition 
          AND TerminalTags.dimensionname = 'Terminal' 
LEFT JOIN vw_ledgerallfinancialtags AS WorkerTags 
       ON WorkerTags.recid = dimension.worker 
          AND WorkerTags.partition = 
              dimension.partition 
          AND WorkerTags.dimensionname = 'Worker' 

go