Hello Emad, The views are Virtual tables and their physical existence doesn't exist in the database. The views generate the data dynamically whenever we use those views in the code.
Here is the SQL query which runs whenever we see the data from AcctgTransEntrySums view. https://demo-stable.ofbiz.apache.org/webtools/control/FindGeneric?entityName=AcctgTransEntrySums ========================================== SELECT ATE.GL_ACCOUNT_ID, GLA.GL_ACCOUNT_TYPE_ID, GLA.GL_ACCOUNT_CLASS_ID, GLA.ACCOUNT_NAME, GLA.ACCOUNT_CODE, ACT.GL_FISCAL_TYPE_ID, ATE.DEBIT_CREDIT_FLAG, SUM(ATE.AMOUNT) FROM (OFBIZ.ACCTG_TRANS_ENTRY ATE INNER JOIN OFBIZ.ACCTG_TRANS ACT ON ATE.ACCTG_TRANS_ID = ACT.ACCTG_TRANS_ID) INNER JOIN OFBIZ.GL_ACCOUNT GLA ON ATE.GL_ACCOUNT_ID = GLA.GL_ACCOUNT_ID GROUP BY ATE.GL_ACCOUNT_ID, GLA.GL_ACCOUNT_TYPE_ID, GLA.GL_ACCOUNT_CLASS_ID, GLA.ACCOUNT_NAME, GLA.ACCOUNT_CODE, ACT.GL_FISCAL_TYPE_ID, ATE.DEBIT_CREDIT_FLAG ========================================== Hope this helps! -- Kind Regards, Ashish Vijaywargiya Vice President of Operations *HotWax Systems* *Enterprise open source experts* cell: +91-9893479711 http://www.hotwaxsystems.com On Tue, Oct 18, 2022 at 8:16 PM Emad Radwan <[email protected]> wrote: > 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 > > >
