Join Account to Sub Account

Question

I am trying to create a GI to retrieve a list of GL Accounts with each associated Sub Account value. I have looked in the database and found the GroupMask column which ties the Account and Sub tables together with a bit relationship but I don't know how to join them in the GI. Is there another resolution table I can use to link the two entities?

Answer 1

The only thing I find in standard Acumatica is screen GL403000 (Account by Subaccount). This screen is an inquiry into historical GL data. Although I may be wrong, I believe accounts and subaccounts are defined as separate entities and then become a legitimate combination when used together. For instance, a sub account might be mapped to a department and an inventory account mapped to the item. Material issued to the department would result in the reportable combination of account and subaccount. That means you would have to look for historical usage of an account/subaccount to get the list. That's what GL403000 appears to be doing.

I can't tell you for sure that this is proper, but see if you can find what you need from:

GL.Account -> GL.GLHistory -> GL.Sub

Join Account to GLHistory via AccountID and GLHistory to Sub via SubID. You will want to aggregate the results to unique combinations or filter by period.

The actual code behind GL403000 is:

PXSelectBase<GLHistoryByPeriod> cmd = new PXSelectJoinGroupBy<GLHistoryByPeriod,
                InnerJoin<Account,
                        On<GLHistoryByPeriod.accountID, Equal<Account.accountID>, And<Match<Account, Current<AccessInfo.userName>>>>,
                InnerJoin<Sub,
                        On<GLHistoryByPeriod.subID, Equal<Sub.subID>, And<Match<Sub, Current<AccessInfo.userName>>>>,
                LeftJoin<GLHistory, On<GLHistoryByPeriod.accountID, Equal<GLHistory.accountID>,
                        And<GLHistoryByPeriod.ledgerID, Equal<GLHistory.ledgerID>,
                        And<GLHistoryByPeriod.branchID, Equal<GLHistory.branchID>,
                        And<GLHistoryByPeriod.subID, Equal<GLHistory.subID>,
                        And<GLHistoryByPeriod.finPeriodID, Equal<GLHistory.finPeriodID>>>>>>,
                LeftJoin<AH, On<GLHistoryByPeriod.ledgerID, Equal<AH.ledgerID>,
                        And<GLHistoryByPeriod.branchID, Equal<AH.branchID>,
                        And<GLHistoryByPeriod.accountID, Equal<AH.accountID>,
                        And<GLHistoryByPeriod.subID, Equal<AH.subID>,
                        And<GLHistoryByPeriod.lastActivityPeriod, Equal<AH.finPeriodID>>>>>>>>>>,
                Where<GLHistoryByPeriod.ledgerID, Equal<Current<GLHistoryEnqFilter.ledgerID>>,
                        And<GLHistoryByPeriod.accountID, Equal<Current<GLHistoryEnqFilter.accountID>>,
                        And<GLHistoryByPeriod.finPeriodID, Equal<Current<GLHistoryEnqFilter.finPeriodID>>,
                        And<
                            Where2<
                                    Where<Account.accountID,NotEqual<Current<GLSetup.ytdNetIncAccountID>>,And<Where<Account.type, Equal<AccountType.asset>, 
                                        Or<Account.type, Equal<AccountType.liability>>>>>,
                            Or<Where<GLHistoryByPeriod.lastActivityPeriod, GreaterEqual<Required<GLHistoryByPeriod.lastActivityPeriod>>,
                                And<Where<Account.type, Equal<AccountType.expense>, 
                                Or<Account.type, Equal<AccountType.income>,
                                Or<Account.accountID,Equal<Current<GLSetup.ytdNetIncAccountID>>>>>>>>>>>>>,
                Aggregate<
                        Sum<AH.finYtdBalance,
                        Sum<AH.tranYtdBalance,
                        Sum<AH.curyFinYtdBalance,
                        Sum<AH.curyTranYtdBalance,
                        Sum<GLHistory.finPtdDebit,
                        Sum<GLHistory.tranPtdDebit,
                        Sum<GLHistory.finPtdCredit,
                        Sum<GLHistory.tranPtdCredit,
                        Sum<GLHistory.finBegBalance,
                        Sum<GLHistory.tranBegBalance,
                        Sum<GLHistory.finYtdBalance,
                        Sum<GLHistory.tranYtdBalance,
                        Sum<GLHistory.curyFinBegBalance,
                        Sum<GLHistory.curyTranBegBalance,
                        Sum<GLHistory.curyFinYtdBalance,
                        Sum<GLHistory.curyTranYtdBalance,
                        Sum<GLHistory.curyFinPtdCredit,
                        Sum<GLHistory.curyTranPtdCredit,
                        Sum<GLHistory.curyFinPtdDebit,
                        Sum<GLHistory.curyTranPtdDebit,
                        GroupBy<GLHistoryByPeriod.ledgerID,
                        GroupBy<GLHistoryByPeriod.accountID,
                        GroupBy<GLHistoryByPeriod.subID>>>>>>>>>>>>>>>>>>>>>>>>>(this);

As you can see, it joins Account to GLHistoryByPeriod and then Sub to GLHistoryByPeriod. AH inherits from GLHistory.

Answer 2

Account and Subaccount entities are separate entities without a relationship between each other. GroupMask field is not about their relationship, it is about access restriction. Please correct your question and explain what do you really need? Note that Account and Subaccount are only connected in the scope of some transactional or historical entities, such as GL Transactions (GLTran), AR/AP Documents (ARRegister, APRegister) and many many others.

Answer 3

As it was already mentioned Account and sub account doesn't have direct link between them. If you still want to have some kind of connection between them, then consider adding custom column(s). Before doing this, give an answer on this question:

1. What will be relationship between account and sub account?
2. One to one, one to many, or many to many?
3. What kind of rules regarding defaulting of sub accounts be applied?
4. As an option if some account is selected on some entity, what sub account should be picked?
5. Opposite to 4, if some sub account is selected at some entity, what Account should be selected.

And so on. Then you can build customization package which will provide an answer on relationshops.