Exchange Rate differences summary shows by BP, does not show the breakup document wise

[rajesh_khater]

Hi,

When we run the Exchange Rate Differences for BP, it shows one row per BP which is a summary of Unrealised Forex Gain or Loss per BP.

In order to verify the figures what the report is showing, I also need to see the unrealised forex gain or loss per document of the BP in a Detailed View.

For example, in the Ageing Report, under each BP row, there is an option to expand the list of documents for the BP.

I want a similar option Expand All or Collapse All in the Exchange Rate Differences report.

What is the solution?

Thanks.

[Former Member · Accepted solution]

Hi,

After report execution, you can double click on row of BP to open detail of exchange rate differences.

Hope this helps,

TVSon

[rajesh_khater · Accepted solution]

Great TVSon!! You are right. There is indeed such a window; there was no visual clue at all that such an option existed !!

This solves the problem partially, as the detail window which opens up does not show the Exchange Rate applied in the Document on the Posting Date. It only shows the Exchange Rate on the date of Reconciliation, and in the Form Settings also, there is no such field.

If I have to write query which will give me identical results for each document as per this window, and the additional column for the Document Rate or the System Rate on the Posting Date, how can I write? Can you please show me the query for a specific Execution Date?

Thanks a lot!!

[Former Member]

Declare @datefrom datetime;

Declare @dateto datetime;

declare @gm int, @gl int;

--Set @datefrom = {?datefrom}

--Set @dateto = {?dateto}

Set @datefrom = '7-1-2011'

Set @dateto = '10-31-2015'

set @gm = 1

set @gl = 16

Select

a.TransType,

b.CompnyName,

b.CompnyAddr,

a.AcctName,

a.DocDate 'Posting Date',

a.Ref1 'Ref No.',

a.NumAtCard 'Invoice No.',

a.Comments 'Detail',

a.Balance 'Balance (LC)',

a.DocRate 'Forex Rate',

Case

When a.DocCur = 'AUD' Then a.[FC Balance]

End 'Balance (AUD)',

Case

When a.DocCur = 'USD' Then a.[FC Balance]

End 'Balance (USD)',

Case

When a.[FC Balance]= 0 then a.Balance

else

a.[FC Balance]*(select top 1 Rate from ORTT where Currency = a.DocCur and RateDate = @dateto order by ratedate asc) End 'Revalued Amount',

Case when a.[FC Balance]=0 then null

else a.Balance - (a.[FC Balance]*(select top 1 Rate from ORTT where Currency = a.DocCur and RateDate = @dateto order by ratedate asc)) End 'Forex Clearing',

a.[FC Balance] 'Outstanding Balance (FC)',

a.Balance 'Outstanding Balance (PHP)',

a.CardName,

Case --Transaction Definition

When a.TransType = 13 Then 'AR Inv'

When a.TransType = 14 Then 'AR CM'

When a.TransType = 18 Then 'AP Inv'

When a.TransType = 19 Then 'AP CM'

When a.TransType = 21 Then 'GR'

When a.TransType = 20 Then 'GRPO'

When a.TransType = 24 Then 'In Pay'

When a.TransType = 30 Then 'JE'

End As 'Trans Def',

a.TransId,

Case When c.Currency = 'AUD' then Rate End 'AUD MER',

Case When c.Currency = 'USD' then Rate End 'USD MER',

a.DocNum

From

(select

a.TransId,

a.DocDate,

a.CardName,

c.AcctName,

a.Comments,

b.BalDueDeb-b.BalDueCred 'Balance',

b.BalFcDeb-b.BalFcCred 'FC Balance',

a.DocCur,

b.TransType,

a.DocRate,

b.Ref1,

a.NumAtCard,

a.docnum

from oinv a

left join jdt1 b on a.TransId = b.TransId

left join oact c on c.AcctCode = b.Account

--left join INV1 d on d.docentry = a.DocEntry

where

--DocCur <> 'PHP'

--and

GroupMask = @gm and GrpLine = @gl and b.BalDueDeb-b.BalDueCred <> 0

and a.docdate between @datefrom and @dateto

union all

select

a.TransId,

a.DocDate,

a.CardName,

c.AcctName,

a.Comments,

b.BalDueDeb-b.BalDueCred 'Balance',

b.BalFcDeb-b.BalFcCred 'FC Balance',

a.DocCur,

b.TransType,

a.DocRate,

b.Ref1,

a.NumAtCard,

a.docnum

from orin a

left join jdt1 b on a.TransId = b.TransId

left join oact c on c.AcctCode = b.Account

--left join INV1 d on d.docentry = a.DocEntry

where

--DocCur <> 'PHP'

--and

GroupMask = @gm and GrpLine = @gl and b.BalDueDeb-b.BalDueCred <> 0

and a.docdate between @datefrom and @dateto

union all

select

a.TransId,

a.DocDate,

a.CardName,

c.AcctName,

a.Comments,

b.BalDueDeb-b.BalDueCred 'Balance',

b.BalFcDeb-b.BalFcCred 'FC Balance',

a.DocCurr,

b.TransType,

a.DocRate,

b.Ref1,

a.Comments,

a.docnum

from orct a

left join jdt1 b on a.TransId = b.TransId

left join oact c on c.AcctCode = b.Account

where

GroupMask = @gm and GrpLine = @gl

and a.DocDate between @datefrom and @dateto

and b.BalDueDeb-b.BalDueCred<>0

union all

select

a.TransId,

a.RefDate,

d.CardName,

b.AcctName,

a.LineMemo,

a.BalDueDeb-a.BalDueCred ' Balance',

a.BalFcDeb-a.BalFcCred 'FC Balance',

e.TransCurr,

a.TransType,

f.Rate,

a.Ref1,

a.Ref2,

a.BaseRef

from jdt1 a

left join oact b on a.Account = b.AcctCode

left join oact c on a.ContraAct = c.AcctCode

left join ocrd d on a.shortname = d.CardCode

left join ojdt e on e.TransId = a.TransId

left join ortt f on f.Currency = e.TransCurr and f.RateDate = e.RefDate

where

a.RefDate between @datefrom and @dateto

and b.GroupMask = @gm and b.GrpLine = @gl

and a.BalDueDeb-a.BalDueCred <> 0

and a.TransType = '30'

and 1 = (case when c.GroupMask=7 and c.GrpLine=4 then 2 else 1 end)

)a

left join ortt c on a.DocCur = c.Currency and c.RateDate = @dateto, OADM b

-- The only problem with this query is that if there is an existing standalon JE for adjustment. If only I can identify those kind of JE then this one could be the answer.

[rajesh_khater]

Hi,

I have posted a similar question but with slight differences here:

I tried the RTM1 table, but it does not contain any data. Please reply on the new thread.

Thanks.

[kothandaraman_nagarajan]

Hi,

Yes Mr.TVson is correct. After excecute, double click row of BP to get details of journal ID for that BP with balance, rate etc details.

Still you need query, use RTM1, OJDT, JDT1 to get more details for documents

Thanks & Regards,

Nagarajan

[rajesh_khater]

Any update on this?

If this is not possible in standard, how can I write a query for all the BPs which will show the Forex Gain Loss for each open document of the BP.

Thanks.

[Former Member]

Hi Rajesh,

This may only be done through customized report. You may search the forum first and start your own. We can fix any problems if you face.

Thanks,

Gordon