-
Tracing back from purchline to main account and Financial Dimension values?I'm not familiar with accounting distribution templates, but I guess you're looking for AccountingDistributionTemplateDetail table.
-
Tracing back from purchline to main account and Financial Dimension values?I've been at this on and off for 4 days now. I can get our four dimensions if purchline[defaultdimension] is populated - just get those out of dimensionattributevalueset via [recid]. If that's not populated (and on our system, that's more often than not), purchline[accountingdistributiontemplate] will point to accountingdistributiontemplate and allow to to fetch the template name - but I still can't work out how to get to the dimension values in each template. So i've parked it just to give the template names (english worded ones in column 2 below) until I can get hold of any ERD:
So, i'm still stuck. If anyone knows how to lookup dimension values in GL dimension templates, i'd really appreciate the name of the table I can find them in.
Thank you. -
Tracing back from purchline to main account and Financial Dimension values?One step forward.If purchline.defaultdimension is non zero, that fields content is the recid in dimensionattributevalueset.recid - and that gives access to our four dimension values.If purchline.accountingdistributiontemplate is non zero, that fields content is the recid in accountingdistributiontemplate.recidHowever, that record in accountingdistributiontemplate only contains the template name, and doesn't provide any of the corresponding dimension values in it. Any ideas what table those dimension values reside in?
-
Tracing back from purchline to main account and Financial Dimension values?An order is not a result of a posting, such as a entries in general ledger created for an invoice journal or a packing slip. An order is used as an input for posting and you can, but don't have to, select some default dimension.
-
Tracing back from purchline to main account and Financial Dimension values?Thankyou - that's the strange thing. I thought that if you posted any purchase order, there would be dimensions associated with it that would make it into the purchase ledger, Hence curious how I address the gaps where defaultdimension=0. I wonder if I should pick the dimensions out of the template definitions if that is the case - which is the lookup into AccountingDistributionTemplate.recid. I'll try that...
-
Tracing back from purchline to main account and Financial Dimension values?This is the relation from PurchLine:If DefaultDimension is empty (zero), it means it doesn't have a reference to any record in DimensionAttributeValueSet table. You can't expect to find a record with RecId = 0, because that's not allowed at all.Accounting distribution template is a different thing stored in a different table. Again, you can look at the relation in F&O:
-
Tracing back from purchline to main account and Financial Dimension values?Results in
-
Tracing back from purchline to main account and Financial Dimension values?
-
Tracing back from purchline to main account and Financial Dimension values?Thankyou Martin.We have Financial Dimension Templates set up on our system that are entitled thinks like "P1011 - Directorate Allocated Training" and contain a Cost Centre, Fund, Item Group and Project code - eg: C3525, U1000, NONE and P10011. "Posting String" is just me concatenating them with a dash between each (ie: "C3525-U1000-NONE-P10011").I have a purchase order line on the system that uses the named template, but the Default Dimension (0) nor Accounting Distribution Template (5637149828) appear in the dimensionattributevalueset table. There is an instance of the right values in there (recid 5637164836) but that bears no relation to any values in the purchline record, hence me thinking there must be another hop, skip, jump around another table to get there.
-
Tracing back from purchline to main account and Financial Dimension values?Hi @Ian,
PurchLine is linked to DimensionAttributeValueSet using DefaultDimension
DimensionAttributeValueSet is linked to DimensionAttributeValueSetItem table usning RecId
DimensionAttributeValueSetItem table is having field called dispalyValue which will give you the financial Dimension value
e.g.
select * from purchline pl join DimensionAttributeValueSet davs on pl.DefaultDimension = davs.RECID join DimensionAttributeValueSetItem davsi on davsi.DimensionAttributeValueSet = davs.RecId where pl.RECID = 5637145326
Best regards,Mohamed Amine MAHMOUDI
Helpful resources
Quick Links
Subscribe to
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
Leaderboard
Andre Arnaud de Cal... 283,183 Super User
Martin Dráb 222,733 Super User
nmaenpaa 101,138