cancel
Showing results for 
Search instead for 
Did you mean: 

Unreconciled transaction in past time- Report

Former Member
0 Kudos

Dear All,

I am looking a report for the following scenario

I will get the Bank reconciliation report as on today from SAP Business One, means I will get the details like all reconciled transactions and un reconciled transactions as on today

In my scenario I may have some transactions un reconciled 2008 April , which may be reconciled in 2008 May , Is it possible to get a report where we can get the details like

Truncations were un reconciled in past time

Thanks In Advance

Kind regards

Muhammed Rashid

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi........

Yes its possible to view the report. in reconciliation window itself. When you select the bank account then it will display all the unreconcilled transaction till date on left hand side. And about reconcilled records, when you select the bank account in process external bank statement it will display all the reconcilled records with repect to your SAP transactions.

regards,

Former Member
0 Kudos

Dear Rahul,

Thanks for your reply

I do agree with you,

What i am looking is A report which can show un reconciled transactions in the month of 2008 April, 2008 May, 2008 June.....

(Currently we are getting as on today un reconciled in Bank statement and Bank account )

Kind regards

Rashid

Edited by: Muhammed Rashid on Aug 28, 2009 10:44 AM

Former Member
0 Kudos

Hi......

Refer This Report......

SELECT T0.[TransId], T0.[RefDate], T0.[DueDate], T1.[Debit], T1.[Credit], T1.[IntrnMatch], 
T1.[ExtrMatch] FROM OJDT T0  INNER JOIN JDT1 T1 ON T0.TransId = T1.TransId WHERE T1.[ExtrMatch] =0 
and  
T1.[Account] ='[%0]' and ( T0.[RefDate] >='[%1]' and  T0.[RefDate] <='[%2]')

This will give you all the i=unreconciled transactions of your bank account from date to date.

Modify this report as you want.

Hope this will help you.

Regards,

Former Member
0 Kudos

HI,

Thanks

This query not generating any report

Regards

Rashid

Former Member
0 Kudos

Hi..........

Its running in my DB.........

Regards,

Former Member
0 Kudos

Hi,

I am not getting the report even using this query .

What is my exact requirement is I should be able to take a report today what are entries in my Bank account were un reconciled in past time for ex Date range 01-12-2008 to 31-12-2008..............

Best regards

Rashid

Former Member
0 Kudos

Hi Bro.........

Actully T1.[ExtrMatch] =0 condition shows all the unrecoconcilled transaction till date. SO there is nothing wrong in this. Ok tell me exactly what error you are getting while running this query?

Regards,

Former Member
0 Kudos

Hi!

The field Extrmatch is updated till 2005B, now in 2007B this Reconcillation information is stored in OMTH Table.

I belive u are using 2007B ?

Former Member
0 Kudos

I'm also using 2007B Then hows its working in my DB?

Former Member
0 Kudos

Hey,

Is your DB is an Upgraded one ? From 2005B to 2007B.

External Reconcil field will have a value if any Extrnl Reconcil happened, do u did any External Reconcillation after upgrading the DB ?

Please check that, if u get a value on that field after External Recon then please let me know.

I have checked the same for me it's not coming.

Former Member
0 Kudos

Hi

We are using 2005 B

I even tried the G/L report, In the options (All posting,Unreconciled , Reconciled only,Reconciled externally and Unreconcilied externally)

I am getting the same report for these options All posting,Unreconciled , Reconciled only

Not getting any value for Reconciled only

The selection Unreconcilied externally will give the transaction which are still unreconciled

My requirement is clear

Even though the transactions got reconciled in subsequent months, i should be able to know which are the transactions were unreconciled in a past time for e.g. from 01-12-2008 to 31-12-2008 or so

Best regards

Rashid

Former Member
0 Kudos

Hi......

No its not upgraded. Directly I created in 2007B. There I did reconciliation and some of the transactions are unreconciled.

So aftre creating his query its giving me complete records of unrecoclilled transactions.

Regards,

Former Member
0 Kudos

Hi

I am working on 2005 B, not upgraded to 2007 B

Former Member
0 Kudos

Hi!

Fine.

I will try to write a query for the same.

Former Member
0 Kudos

So might be be one of the reason that its not working in your database.......

Regards,

Former Member
0 Kudos

That will be grate

Former Member
0 Kudos

Hi!

Please check this query.

Declare @FromDate Datetime
Declare @ToDate Datetime
Declare @Code nvarchar(20)
SET @Code = (Select max(s2.Account) from jdt1 s2 where s2.Account='[%0]')
SET @FromDate = (Select min(S0.Refdate) from JDT1 S0 where S0.Refdate >= '[%1]')
SET @ToDate = (Select max(S1.Refdate) from JDT1 S1 where S1.Refdate <= '[%2]')

SELECT T0.[TransId], T0.[RefDate], T0.[DueDate], T1.[Debit], T1.[Credit]
FROM OJDT T0  INNER JOIN JDT1 T1 ON T0.TransId = T1.TransId WHERE 
(T1.[ExtrMatch] = 0 and T1.MthDate between @Fromdate and @Todate)
and  
T1.[Account] =@code and T0.[RefDate] >=@FromDate and  T0.[RefDate] <=@Todate

Result will come only if u select the correct Account Number which is Reconcilled.

Former Member
0 Kudos

Hi,

Thanks for the query .

At the time of running the query I am getting following message

Record retrieved by this query 0

As you are aware the query should consider the following

1.We will have one GL account as bank account

2.In that bank account transactions will happened

3.We will upload external bank statement in process external statement

4.We reconcile Bank GL account with External Bank statement

5.Some of the transactions will get reconciled externally

6.and some of the transactions will remain unreconciled ( Unreconciled externally)

So I am looking these unreconciled transactions , which may get reconciled externally in subsequent months

But these transactions were unreconciled in past time

Best regards

Rashid

Former Member
0 Kudos

Hi!

What is the result coming in

Financial > Financial Report > Accounting > General Ledger > unreconcilled externally.

Former Member
0 Kudos

Hi!

Check this

Declare @FromDate Datetime
Declare @ToDate Datetime
Declare @Code nvarchar(20)
SET @Code = (Select max(s2.Formatcode) from oact s2 where s2.formatcode='[%0]')
SET @FromDate = (Select min(S0.Refdate) from JDT1 S0 where S0.Refdate >= '[%1]')
SET @ToDate = (Select max(S1.Refdate) from JDT1 S1 where S1.Refdate <= '[%2]')

select * from jdt1 j1,oact a1
where
a1.formatcode = @Code and
a1.acctcode=j1.account and
(j1.Extrmatch=0 or j1.mthdate not between @FromDate and @ToDate)
and
j1.refdate >=@FromDate and J1.refdate <=@Todate

Former Member
0 Kudos

Hi!

pl check this

Declare @FromDate Datetime
Declare @ToDate Datetime
Declare @Code nvarchar(20)

SET @FromDate = (Select min(S0.Refdate) from JDT1 S0 where S0.Refdate >= '[%0]')
SET @ToDate = (Select max(S1.Refdate) from JDT1 S1 where S1.Refdate <= '[%1]')
SET @Code = (Select max(s2.Formatcode) from oact s2 where s2.formatcode ='[%2]')
 
select j1.refdate as  [Posting Date],
j1.DueDate as [Due Date],
j1.ExtrMatch as [Recon no],
m1.matchdate as [Reconciliation Date],
j1.TransId as [Transaction Code],
j1.Ref1 as [Ref1],
j1.Ref2 as [Ref2],
j1.BaseRef as [Remarks],
j1.ProfitCode as [Profit Code],
j1.Debit as [Debit (LC)],
J1.Credit as [Credit (LC)]
from jdt1 j1,oact a1,OMTH M1
where
a1.formatcode = @Code and
a1.acctcode=j1.account and
j1.ExtrMatch = M1.matchnum and
m1.IsInternal = 'E' and
m1.MthAcctCod = j1.account and
m1.IsCard = 'A' and
j1.refdate >=@FromDate and J1.refdate <=@Todate

Former Member
0 Kudos

Hi ,

Thanks for your extensive support

I am getting the result

Former Member
0 Kudos

Hi!

Great.

Answers (1)

Answers (1)

former_member187989
Active Contributor
0 Kudos

You have seen option to select reconciled/uncreconciled transactions in general ledger report,

Select Bank g/l acct,Select reqd. dates and select unreconciled transactions.

Former Member
0 Kudos

Thanks, That selection (Unreconciled transaction ) not making any difference in Value

I am getting the same value if i am selecting reconciled transaction and All transaction

TIA

Regards

Rashid