The tables using ledger dimension, such as GeneralJournalAccountEntry
join up with the rest of the financial dimension tables as follows.
select * -- replace * with the fields you actually want.
FROM
GENERALJOURNALACCOUNTENTRY left outer join
DIMENSIONATTRIBUTEVALUECOMBINATION DAVC
on GENERALJOURNALACCOUNTENTRY.LEDGERDIMENSION = DAVC.RECID
INNER JOIN
DIMENSIONATTRIBUTEVALUEGROUPCOMBINATION DAVGC ON
DAVC.RECID = DAVGC.DIMENSIONATTRIBUTEVALUECOMBINATION INNER JOIN
DIMENSIONATTRIBUTELEVELVALUE AS DAVL ON
DAVL.DIMENSIONATTRIBUTEVALUEGROUP = DAVGC.DIMENSIONATTRIBUTEVALUEGROUP INNER JOIN
DIMENSIONATTRIBUTEVALUE AS DAV ON DAV.RECID = DAVL.DIMENSIONATTRIBUTEVALUE INNER JOIN
DIMENSIONATTRIBUTE DA ON DA.RECID = DAV.DIMENSIONATTRIBUTE
To filter the dimension attribute rows down to only the row for the worker dimension, add a where clause like WHERE DA.RECID = '5637144849' -- replace the filter value with the RecId for your worker dimension
You could also filter on Name instead of recid. Either way you want to select only the row for the worker dimension from DIMENSIONATTRIBUTE table.
To bring in values for workers, add a left outer join to DIMATTRIBUTEHCMWORKER
on DIMENSIONATTRIBUTE.RECID to DIMATTRIBUTEHCMWORKER.KEY_
The VALUE field in DIMATTRIBUTEHCMWORKER is the personnel number and the NAME field is the worker's name.
P.S. There may not actually be records in GeneralJournalAccountEntry are related to worker. For unrelated rows, the query with outer joins would return a NULL value for fields from DIMATTRIBUTEHCMWORKER. If you would rather leave out all the rows not related to worker dimension, change the outer joins to inner joins.
Hope this helps. I can't guarantee this gives you everything you want for your report but it should give you and idea of which tables are involved and how they link up.
-- Lance.
