Hello Ashish, Sorry for the late coming back to this one. Is there a way to view the SQL for AcctgTransEntrySums? I have Ofbiz using MS SQL Server and I can only find tables in Ofbiz DB and no views.
Regards, Emad On Thu, Sep 1, 2022 at 2:58 PM Ashish Vijaywargiya < [email protected]> wrote: > Hello Emad, > > Please check the "AcctgTransEntrySums" view entity(It has all credit/debit > entries based on the GlAccount). > > https://demo-stable.ofbiz.apache.org/webtools/control/FindGeneric?entityName=AcctgTransEntrySums > > Here is the definition of View Entity from accounting-entitymodel.xml: > <view-entity entity-name="AcctgTransEntrySums" > package-name="org.apache.ofbiz.accounting.ledger" > title="Sum of AcctgTransEntry entity amounts grouped by > glAccountId, debitCreditFlag"> > <member-entity entity-alias="ATE" entity-name="AcctgTransEntry"/> > <member-entity entity-alias="ACT" entity-name="AcctgTrans"/> > <member-entity entity-alias="GLA" entity-name="GlAccount"/> > <alias entity-alias="ATE" name="glAccountId" group-by="true"/> > <alias entity-alias="GLA" name="glAccountTypeId" group-by="true"/> > <alias entity-alias="GLA" name="glAccountClassId" group-by="true"/> > <alias entity-alias="GLA" name="accountName" group-by="true"/> > <alias entity-alias="GLA" name="accountCode" group-by="true"/> > <alias entity-alias="ACT" name="glFiscalTypeId" group-by="true"/> > <alias entity-alias="ACT" name="acctgTransTypeId" group-by="false"/> > <alias entity-alias="ATE" name="debitCreditFlag" group-by="true"/> > <alias entity-alias="ATE" name="amount" function="sum"/> > <alias entity-alias="ATE" name="organizationPartyId" > group-by="false"/> > <alias entity-alias="ACT" name="isPosted" group-by="false"/> > <alias entity-alias="ACT" name="transactionDate" group-by="false"/> > <!-- so we can select the sum by date --> > <view-link entity-alias="ATE" rel-entity-alias="ACT"> > <key-map field-name="acctgTransId"/> > </view-link> > <view-link entity-alias="ATE" rel-entity-alias="GLA"> > <key-map field-name="glAccountId"/> > </view-link> > </view-entity> > > Following line in the above view entity is important one: > <alias entity-alias="ATE" name="amount" function="sum"/> > > Please let me know if you need further assistance on this. > > -- > Kind Regards, > Ashish Vijaywargiya > Vice President of Operations > *HotWax Systems* > *Enterprise open source experts* > http://www.hotwaxsystems.com > > > > On Sun, Aug 28, 2022 at 11:17 PM Emad Radwan <[email protected]> > wrote: > > > Hello Community, > > > > Does Ofbiz maintain the balance of each GL account somehow and if yes, > > where its kept, and in which table? I see that both GL_Accounts and > > GL_Accounts_organization tables have no room for that. > > > > Is it being calculated on the fly when running reports from different > > transactions? > > > > Regards, > > Emad >
