Question

TRIAL BALANCE QUERY

  • 25 November 2022
  • 7 replies
  • 351 views

Badge +1

We are using IFS application Version 9, I am working on a project of reporting in that I need to fetch the Trial Balance Data directly from the Database. I know that from the application I can run the Trial Balance Report and Export it to Excel which I do not want. I need help to fetch the Trial Balance directly from the DB or any if you know the view which can provide me the TB Data.


7 replies

Userlevel 7
Badge +13

Hi @WAHEEDFM,

You can use below query in PLSQL to extract TB values.

Select g.account,
       sum(nvl(g.debet_amount,0) - nvl(g.credit_amount,0)) amount
from  gen_led_voucher_row_tab g
WHERE   g.company = 'XXX'
AND g.YEAR_PERIOD_KEY between 202001 and 202112
group by g.account
         

Best Regards

Narmada

 

Badge +1

Hi Narmada

Thanks it works fine but I am having one issue in this When I calculate the AR for October it is matching but when I want to look the current Date AR the values does not change. I don’t understand that why it is happening any idea why it like that. 

Userlevel 7
Badge +13

Hi @WAHEEDFM,

You need to put from date as transactions start date in the data base as we are reffering to GL Voucher rows tab. 

BR/Narmada

Badge +1

Hi @Narmada 

It is in the same table or some other view/table? as I did not know about IFS so it is hard for me to trace it if you can guide me more specific view/table as you mentioned for GL Voucher Row it would be easier for me to fetch.  

As I explained the total for month of October is perfect there are transactions in November but the totals are not affecting. It means some where the date is there is the GL VOUCHER ROW Tab if I am not mistaken.

Badge +1

Hi @Narmada

I found this view which is combination of Header and Details. But with this view the result is same, it is so confusing for me. 

select * from GEN_LED_VOUCHER_ROW_UNION_QRY where COMPANY='XXX’

Badge +1

@Narmada this is interesting now.

I run the query which you gave me so the OCTOBER balance is showing correct as per the AR analysis report but going forward in November at any given date the figures does not change it remains same as October then when I go back to September or any given date in September the figures are not matching what report says. 

@Narmada I need your help in this if you can give your expert advise.

Userlevel 7
Badge +13

Hi @WAHEEDFM ,

Have you given the “YEAR_PERIOD_KEY” correctly? if you need YTD balance it is required to extract from the very beginning period. 

BR/Narmada

Reply