Amount to Reconcile in Debtors Outstanding balance query

[Former Member]

Hi Experts.

Below is the query i used to run Debtors Outstanding Balance. and its working fine but we need Amount To Reconcile Debit and Amount To Reconcile Credit Fields also..

so pls modify this query and help me...

SELECT T0.[TransId],T0.[RefDate], T0.[TransType] AS 'Origin Doc.Type', T1.[BaseRef] AS 'Origin Doc.No', T1.[ShortName], T2.[CardName], 
T1.[Account], T1.[ContraAct], T1.[CreatedBy], T1.[Debit], T1.[Credit], T1.[BalDueDeb], T1.[BalDueCred], T1.[LineMemo]
FROM dbo.OJDT T0  
INNER JOIN dbo.JDT1 T1 ON T0.TransId = T1.TransId
INNER JOIN dbo.OCRD T2 on T2.CardCode = T1.[ShortName]
WHERE T1.[MthDate] IS NULL AND T2.[CardName] LIKE '[%0]%'

Regards,

Vamsi

[Former Member]

Do you mean this:

SELECT T0.[TransId],T0.[RefDate], T0.[TransType] AS 'Origin Doc.Type', T1.[BaseRef] AS 'Origin Doc.No', T1.[ShortName], T2.[CardName], 
T1.[Account], T1.[ContraAct], T1.[CreatedBy], T1.[Debit], T1.[Credit], T1.[BalDueDeb], T1.[BalDueCred], T1.[LineMemo], (T1.[Debit] - T1.[BalDueDeb]) AS 'Amount To Reconcile Debit', (T1.[Credit]-T1.[BalDueCred]) AS 'Amount To Reconcile Credit'
FROM dbo.OJDT T0  
INNER JOIN dbo.JDT1 T1 ON T0.TransId = T1.TransId
INNER JOIN dbo.OCRD T2 on T2.CardCode = T1.[ShortName]
WHERE T1.[MthDate] IS NULL AND T2.[CardName] LIKE '[%0]%'

Thanks,

Gordon

[Former Member]

Hi Gordon..

Thanks for the query.

But when i run the query the amount to reconclie fields is showing Zero value whatever the BP Code i selectecd.And we need only the unreconciled Transactions for Business Partner in the above query.

Pls Update the Query.

Regards,

Vamsi.

[Former Member]

Hi Vamsi,

Check the thread,

Regards,

Madhan.

[Former Member]

Hi Madhan...

I need an extra field ( amount to reconcile) to see the party wise outstanding balance.

Regards,

Vamsi.

Edited by: Parimis on Jan 27, 2010 1:21 PM