ADempiere ERP Business Suite / Discussion / Functional-ERP: General Ledger Transaction Listing

Dimitri - 2008-02-04

Hi

I am trying to create a General ledger transaction listing using jasper reports and iReport.

The only way that i have found to get all infortmation of all transactions is from the Fact_Acct table.

The only problem is that i can't seem to link all the tables to get final results!

eg: Credit Note is saved as an invoice with different DocType so when i need to display each transaction lines and i come across a credit note it has to have a description of "Credit Note" just like an invoice needs to be "Invoice" etc. There is no way of identifying the doctype only can identify the table name from the AD_Table_id.

Anyone have any suggestions?

Thanks Dimitri

If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
- Carlos Ruiz - 2008-02-05
  
  > Anyone have any suggestions?
  
  Yes, you can try something like:
  
  -- Invoices
  SELECT -- your needed columns (must be the same in all union selects)
  FROM FACT_ACCT f, C_INVOICE i, C_DOCTYPE d
  WHERE f.ad_table_id = 318
     AND f.record_id = i.c_invoice_id
     AND i.c_doctype_id = d.c_doctype_id
  UNION
  -- Orders
  SELECT ...
  FROM FACT_ACCT f, C_ORDER o, C_DOCTYPE d
  WHERE f.ad_table_id = 259
     AND f.record_id = o.c_order_id
     AND o.c_doctype_id = d.c_doctype_id
  UNION
  --- etcetera
  
  Regards,
  
  Carlos Ruiz
  
  If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
  - Teo Sarca - 2008-02-05
    
    Hi Carlos,
    
    I think getting the doctype is a common requirement. What if we add that column to Fact_Acct ?
    
    Best regards,
    Teo Sarca
    
    If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
    - Colin Rooney - 2008-02-05
      
      yes Teo,
      but from memory not every doc has a doc type ... e.g. i don't think cash books have a doc type? Still useful mind. Really, we should probably add a docType to cashbooks.
      Also if we add your proposed reversal doc Id fields then it would be useful have these too so we can match up documents in GL reporting.
      I must say generally while he AD_Table_ID & record_id in this table is very useful from java code it does not really help a whole lot when creating reports in SQL...
      
      colin
      
      If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
      - Teo Sarca - 2008-02-05
        
        Hi,
        
        > but from memory not every doc has a doc type ... e.g. i don't think cash books have a doc type? Still useful mind.
        > Really, we should probably add a docType to cashbooks.
        
        Agree, we need a doctype for cash documents. But i think is better to add this on Cash Journal (C_Cash) and not on cashbook (C_Cashbook).
        
        Best regards,
        Teo Sarca
        
        If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
        
        Colin Rooney - 2008-02-05
        
        Yes Teo, well I was just saying cash books generally.. I wasn't yet thinking of implementation :) I would think as a general rule we can say the DocType should be at the level of the doc that is processed by the Accounting processor which in the case of cash books is, indeed, the the cash journal. BUT, the cash journal is not like other documents in that ALL cash journals must be for a specific Cash Book (which, as you know, defines currency & default GLs) ... so I see no issue with the Cash Book also defining the Doc Type (at least as a default for the Journal) so that once the CashBook is selected the DocType is too!?
        Anyway there is a lot of discussion on Cash Books/Journals at the moment perhaps we can wrapped this up with those to produce the Grand Unified Theory of cash payments & cash journals :)
        
        colin
        
        If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
    - Carlos Ruiz - 2008-02-05
      
      > I think getting the doctype is a common requirement.
      > What if we add that column to Fact_Acct ?
      
      Just one thing comes to my mind.
      
      Fact_Acct is a really big table (millions of records) - adding a column has a big impact in storage (sorry, I always keep thinking on 4GB limit on Oracle-XE)
      
      OTOH, Fact_Acct table is a *fact* table talking in BI terminology - it's really common in BI to denormalize tables, but I'm not sure in this case - we have a BI-like fact table, but we're an OLTP system, not a BI system.
      
      I suppose adding common report columns to the fact table must be a task for BI, not for us.
      
      So, my vote is to avoid touching Fact_Acct table - and maybe add a view that show more common report columns (for direct reporting or to allow easier BI ETL).
      
      Regards,
      
      Carlos Ruiz
      
      If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
      - Heng Sin - 2008-02-06
        
        hi all,
        
        Instead of capturing the c_doctype_id in fact table, this is also very easy to achieve using store procedure. Just create a function say getDocTypeId(AD_Table_Id, Record_ID) and use it in your select statement.
        
        Regards,
        Low
        
        If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
  - crimzone - 2008-03-14
    
    Hello All,
    
    we've tried...
    
    >>-- Invoices
    >>SELECT -- your needed columns (must be the same in all union selects)
    >>FROM FACT_ACCT f, C_INVOICE i, C_DOCTYPE d
    >>WHERE f.ad_table_id = 318
    >>AND f.record_id = i.c_invoice_id
    >>AND i.c_doctype_id = d.c_doctype_id
    >>UNION
    >>-- Orders
    >>...
    >>...
    
    ..this becomes an unwieldly beast - and if you miss a table you entry "disappears" either into oblivion or under a "general entry".
    
    Please post suggestions.
    
    Our auditors rejects the "Statement of Account".
    
    Thanks
    
    Pete
    
    If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
- Dimitri - 2008-02-05
  
  Thanks Carlos
  
  I will try it! Only problem that i might have is that we need to disply everything in listing , all doctypes! So might get abit messy but all is good will give it a go!
  
  Thanks again!
  
  Dimitri
  
  If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
- Alfred_Jones - 2008-03-16
  
  It is possible to set a single "GL Category" value (choose your own!) for each of the AP invoice/credit note (or AR) related 'Document Types' and then add GL Category as a selection parameter to the Accounting Fact Details report. It can also be useful to have Table and Record ID in the selected report columns so you can sort and group by them and so see the total for each invoice in the accounting facts selected by your account and date range parameters.
  Best wishes.
  
  If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
- crimzone - 2008-03-17
  
  Hi,
  
  Thanks for all :-)
  
  Atleast we have options.
  
  If you would like to refer to this comment somewhere else in this project, copy and paste the following link:

General Ledger Transaction Listing

A full ERP system - moved to GitHub. See full description for details.

Forums

Help

General Ledger Transaction Listing

General Ledger Transaction Listing

A full ERP system - moved to GitHub. See full description for details.

Forums

Help

General Ledger Transaction Listing document.SUBSCRIPTION_OPTIONS = { "thing": "topic", "subscribed": false, "url": "subscribe", "icon": { "css": "fa fa-envelope-o" } };

General Ledger Transaction Listing