incoming payment and credit memos for a BP

[Former Member]

Hi,

I am trying to find credit memos and incoming payments fore BP which are open. I have come up with the following query. could someone verify if I am covering everything and what does T0.intrnmatch = '0' really means in the query below. Thanks!

select case t0.TransType when 14 then 'AR CN' when 24 then 'Incoming' end 'TransType',

t0.DueDate,case transtype when 14 then t1.comments when 24 then t2.comments end 'Comments',

t0.ref1'Docnum',(T0.BALDUEDEB - T0.BALDUECRED) as 'Balance Due'

from JDT1 t0

left join ORIN t1 on t1.DocNum=t0.Ref1 and t0.TransType=14

left join ORCT t2 on t2.DocNum=t0.Ref1 and t0.TransType=24

where ShortName='Uw1234'

and TransType in ('14','24') and

T0.BALDUEDEB != T0.BALDUECRED  and T0.intrnmatch = '0'

order by t0.TransType

[Former Member · Accepted solution]

Hi,

Try this query.

This is working fine for my report.  I hope this is exactly what you require.

-- INCOMING PAYMENT

SELECT

  T200.DOCENTRY, T200.DOCNUM 'BILL NUMBER', T200.CARDCODE, T200.CARDNAME, CONVERT(DATETIME, T200.DOCDATE, 103) Date,

  T300.CNTCTPRSN, T200.DOCTOTAL * -1 'Total', (T200.DOCTOTAL - T200.OPENBAL) * -1 'Adjusted', T200.[OpenBal] * -1 'Open Balance',

  datediff(dd, t200.docdate, getdate()) 'DUE DAYS',

  'Payment'

FROM

  ORCT T200 LEFT OUTER JOIN OCRD T300 ON T200.CARDCODE = T300.CARDCODE

  LEFT OUTER JOIN OSLP T400 ON T300.SLPCODE = T400.SLPCODE

WHERE

  T200.[OpenBal] > 0 AND T300.CARDTYPE = 'C'

--SALES RETURN

UNION ALL

SELECT

  T0.DOCENTRY, T0.DOCNUM, T0.CARDCODE, T0.CARDNAME, CONVERT(DATETIME, T0.DOCDATE, 103), T300.CNTCTPRSN, T0.DOCTOTAL * -1, T0.[PaidToDate],

  (T0.DOCTOTAL - T0.[PaidToDate]) * -1,

  datediff(dd, t0.docdate, getdate()) 'DUE DAYS',

  'SRN'

FROM

  ORIN T0 LEFT OUTER JOIN OCRD T300 ON T0.CARDCODE = T300.CARDCODE

  LEFT OUTER JOIN OSLP T400 ON T300.SLPCODE = T400.SLPCODE

WHERE

  T0.DOCTYPE = 'I' AND T0.DOCTOTAL - T0.[PaidToDate] >0

--CREDIT NOTE

UNION ALL

SELECT

  T0.DOCENTRY, T0.DOCNUM, T0.CARDCODE, T0.CARDNAME, CONVERT(DATETIME, T0.DOCDATE, 103), T300.CNTCTPRSN, T0.DOCTOTAL * -1, T0.PAIDTODATE,

  (T0.DOCTOTAL - T0.[PaidToDate]) * -1,

  datediff(dd, t0.docdate, getdate()) 'DUE DAYS',

  'CN'

FROM

  ORIN T0 LEFT OUTER JOIN OCRD T300 ON T0.CARDCODE = T300.CARDCODE

  LEFT OUTER JOIN OSLP T400 ON T300.SLPCODE = T400.SLPCODE

WHERE

  T0.DOCTYPE = 'S' AND T0.DOCTOTAL - T0.[PaidToDate] >0

Regards,

Amrut Sabnis.

[Former Member]

Hi,

Please mark the correct or helpful answer and close your thread.

Regards,

Amrut Sabnis.

[Former Member]

Amrut,

I wanted to keep it open because I wanted to see what more n more people have to say and make a decision after testing and understanding the solutions fully.

[kothandaraman_nagarajan]

Hi,

Did you check my query?

Thanks & Regards,

Nagarajan

[Former Member]

I haven't checked your query yet as I am trying to understand it first.

Thanks for your help.

[kothandaraman_nagarajan]

Hi,

OK.

Thanks & Regards,

Nagarajan

[jitin_chawla]

Hi,

First you have to let us know which Version of SAP Business One you are working with.

From Version 2007 Higher, the IntrMatch field does not hold the record for the Internal Reconciliations happening  in the database.

Please elaborate you requirement more with the Version you are working with to provide you with the relevant query.

Kind Regards,

Jitin

SAP Business One Forum Team

[Former Member]

Hi Jitin,we are using version 8.82 (8.82.075) PL:11.

[jitin_chawla]

Hi,

To determine all the open Credit memo and Payment (Not applied to any Invoice or JE etc), check this for start :

select ShortName as [BP Code], CASE When TransType = '14' then 'Credit Memo' Else 'Payment' END

as [Document], TransId, Line_ID, Debit, Credit, BalDueDeb, BalDueCred

from JDT1 where transtype in ('14','24')

and ShortName <> Account

group by ShortName, Transtype,TransId, Line_ID, Debit, Credit, BalDueDeb, BalDueCred

Having SUM(Balduedeb + BaldueCred) <> 0

Please check this and update us with the requirement to check what can be modifed to be included in the report.

Just for information, the record of Internal Reconciliation are kept in the OITR and ITR1 table but in your case the Credit memo and Payments can be partially applied as well and hence checking the Balance due would bring the required information.

Please do check and let us know.

Kind regards,

Jitin

SAP Business One Forum Team

[Former Member]

Thanks Jitin, how ever is my query completely wrong? I am just asking for my info..I dont understand completely the whole thing so i am asking. Also why did you add balanceduedeb and balance due cred in having clause?

I guess i need accounting 101..

[jitin_chawla]

Hi,

The requirement mentioned by you in initial post was "trying to find credit memos and incoming payments fore BP which are open"

For BP only transactions, you need to check for Shortname <> Account in JDT1 table.

For Credit memos , the transtype should be 14 and for Payments , the transtype should be 24.

The IntrMatch is not requried as for reconciled transaction also the filed will have 0 as value populated since the new reconciliation was implemented from Version 2007 onwards.

We can check for the Balance Due fields in the JDT1 to get the balance which is outstanding (which will let us know which documents are open). Since, only Balance Due deb or Balance due credit would only be booked and not both, we are checking for (balduedeb +BaldueCred <> 0).

I hope it clarifies some of the things.

Kind Regards,

Jitin

SAP Business One Forum Team

[kothandaraman_nagarajan]

Hi,

T1.[IntrnMatch] means Internal Reconciliation No.

Try this query:

SELECT T0.[RefDate],
case
when T0.[TransType] = 14 then 'ARCredit'

when T0.[TransType] = 24 then 'Incoming Payment'
end as DocType
,T0.[TransId], T2.[U_NAME] as Creator, T3.[FormatCode] as 'G/L Acct/BP code', T1.[ShortName] as 'G/L Acct/BP Name', T3.[AcctName], T1.[Debit] as Debit, T1.[Credit] as Credit

FROM OJDT T0  INNER JOIN JDT1 T1 ON T0.TransId = T1.TransId inner join OUSR T2 on t0.usersign = t2.userid INNER JOIN OACT T3 ON T1.Account = T3.AcctCode

WHERE  T1.shortname = [%0] and  T1.[IntrnMatch] = '0' and  T0.[TransType] in ('14','24')

GROUP BY T0.[RefDate],T0.[TransType],T0.[TransId], T2.[U_NAME], T3.[AcctName], T1.[Debit], T1.[Credit], T1.[ShortName], T3.[FormatCode] ORDER BY T0.[TransId]

Hope helpful.

Thanks & Regards,

Nagarajan