Skip to Content

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

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.

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

5 Answers

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Mar 04, 2015 at 03:23 AM

    Hi,

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

    Hope this helps,

    TVSon

    Add a comment
    10|10000 characters needed characters exceeded

    • 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!!

      Company Details.jpg (185.5 kB)
  • Posted on Mar 03, 2015 at 02:59 PM

    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.

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Mar 04, 2015 at 03:34 AM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Jul 11, 2015 at 08:23 AM

    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.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Nov 11, 2015 at 06:17 AM

    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.

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.