Hi,
Our requirement is to print the trial balance for the calendar year. On the input from i have provided range fields for the Fiscal year/period i.e. year.f/prod.f and year.t/prod.t.

Now the transactions are being retrieved from table tfgld201. E.g suppose the table is having records starting from 2008/01 to 2009/09.

If i input 2008/1 to 2009/9 in the input range, then the report should print all the transactions from Fisacl year 2008 and its period 01 to 2009 fiscal year's period 09.

The following select query is not giving me the desired result. It is selecting records multiple times.

select tfgld201.*
from tfgld201
where tfgld201._index3

between { :cono.f, :year.f, :tfgld008.leac,
:ptyp.f, :prod.f }
and
{ :cono.t, :year.t, :tfgld008.leac,
:ptyp.t, :prod.t }
and tfgld201.duac = :save.duac
order by tfgld201._index3
selectdo
process.transactions()
endselect


What could be the possible reason?

Thanks in advance

Hi,
Our requirement is to print the trial balance for the calendar year. On the input from i have provided range fields for the Fiscal year/period i.e. year.f/prod.f and year.t/prod.t.

Now the transactions are being retrieved from table tfgld201. E.g suppose the table is having records starting from 2008/01 to 2009/09.

If i input 2008/1 to 2009/9 in the input range, then the report should print all the transactions from Fisacl year 2008 and its period 01 to 2009 fiscal year's period 09.

The following select query is not giving me the desired result. It is selecting records multiple times.

select tfgld201.*
from tfgld201
where tfgld201._index3

between { :cono.f, :year.f, :tfgld008.leac,
:ptyp.f, :prod.f }
and
{ :cono.t, :year.t, :tfgld008.leac,
:ptyp.t, :prod.t }
and tfgld201.duac = :save.duac
order by tfgld201._index3
selectdo
process.transactions()
endselect


What could be the possible reason?

Thanks in advance


you don't have gld008 in the query
Try to use tfgld210.leac in the "between"

Even that could not work.....
Still getting the same result

Even that could not work.....
Still getting the same result

Are there multiple records in your table?
the query must be ok in my opinion.
I am on C4 also but don't know tfgld201.duac, maybe there's a problem, could you explain save.duac?


select tfgld201.*
from tfgld201
where tfgld201._index3 between { :cono.f, :year.f, :leac.f, typ.f, rod.f }
and { :cono.t, :year.t, :leac.t, typ.t, rod.t }
and tfgld201.duac = :save.duac

Points to observe & follow .

1. When u r giving tb in calender year , it is not good practiice to ask user to input fiscal year . U should use some enum domain on month .

2. U should convert input calender period to fiscal period . This u can do by finding difference between (calender year) * 12 + calender period on one hand and (fiscal year*12) + fiscal period on the other hand . To convert periods u may need to use remainder / integer of 12 in the period indicator arrived at using this way.

3. Always use fiscal periods in tfgld201/3 .

4. For each output row convert the fiscal period to calender period , using reverse formula in point 2 above.

5. For opening balance , sum up all records tfgld203 for fiscal year or year in tfgld003 whichever is less . Add to this get all records from tfgld201 which are less than f(from fiscal year ) * 12 + fiscal period (from)and greater that (opening balance year from tfgld003)*12 +1

6. Period transaction u will get in tfgld201 for records using the from to year/period indicator(as aforesaid).

7. Use sublevel 0 for the same only using tfgld008 .

8. Eliminate year closing p&l transaction when tfgld201.prno = 12 , by deducting the ledger transaction balance for year close trans type in tfgld106 .