Incoming payments

[Former Member]

Hi

I am trying to find a report which I can see all the incoming payments along with payments .I saw cash flow report and it doesn't meet my requirement .Payments are made by check and Bank transfer

Does any one has created this kind of query or report ?

Thank you in advance

Bishal

[former_member583013 · Accepted solution]

Best wishes

Suda

[Former Member · Accepted solution]

Hi Suda

Thank you for your comments .I am working on my issue and I am testing my query to get the desired outpput .

For your kind information ,

I am not expecting all my results to be answered by post members . I would like to have but expecting everything from members is not my philosophy.

Thank you

Bishal

[Former Member · Accepted solution]

HI Bishal,

You can try this query if you have 2005 version. For 2007, try to match the JE table you have:

Select T1.RefDate,  T1.TransID, T4.CardCode, T4.CardName,
T2.DocNum 'Incomming#', T0.Credit, T3.DocEntry, 
T3.SumApplied
from JDT1 T0 
Inner Join OJDT T1 ON T1.TransId = T0.TransId
Left Join ORCT T2 ON T2.TransId = T1.TransId
Left Join RCT2 T3 ON T3.Docnum = T2.DocEntry
Inner Join OCRD T4 on T4.CardCode = T0.ShortName
where T1.TransType = '24' and T1.RefDate > '[%0]' and 
T0.Credit > 0

Please note: If you have reversed some payment, you would better have a separate report.

Thanks,

Gordon

[Former Member · Accepted solution]

Hi Gordon

Thank you for your help.I am testing your query along with my requirement.

Thank you

Bishal

[jitin_chawla]

Hi,

Check the following query for the Incoming Payments by check for AR Invoices :

SELECT T1.CardCode, T1.CardName, T0.DocNum as 'AR Invoice Number', T1.DocDate as 'Payment Date', T2.DocNum as 'Incoming Payment Number ', T2.DueDate as 'Check Due Date' , T2.CheckNum as 'Check Number', T2.CheckSum as 'Amount'

FROM OINV T0 INNER JOIN ORCT T1 ON T0.ReceiptNum = T1.DocEntry INNER JOIN RCT1 T2 ON T1.DocNum = T2.DocNum

You can customise the same with other relevant fields from the tables.

Regards,

Jitin Chawla

[Former Member]

Hi Jitin

Thank you for your response.I check at your query .Can we add payment from bank transfer and date parameter.

Actually I am trying to create is actually a transaction journal by incoming payments (default report) with some addition of field . Payment will be received from Bank transfer and check only

Thank you

Hi Suda ,

Thank you for your quick response.I forget to mention about that report .It does fulfill some of my purpose but I need to customise because I need to have more field name like A/R invoice no .So Actually I was trying to duplicate the reports through query with more fields .I cannot do it in PLD as my accountant doesn't like it .So that's the reason I was planning to develop this query with some experts help .

Thank you

Bishal

[Former Member]

Hi Jitin

Query you have provided has helped me and my accountant kind of liked it .However ,realised that I am not getting

payment made against JE . We usually won't have this transaction but we are in initial phase of SAP B1 and we have opening balance transfer .so we have to make payment against JE .

Can you help on this matter

Thank you in advance

Bishal

[jitin_chawla]

Hi,

I am not sure that below will help in your case but JE are not in the OINV therefore they would not be picked in the above query.

Try this and take more field relevant for your scenario. check if it helps :

SELECT T0.[DocNum] as 'Incoming payment Number', T0.[CardCode] as 'Customer Code', T0.[CardName] as 'customer name' , T0.[DocDate] as 'Posting Date', T1.[CheckSum] as 'Check Amount' FROM ORCT T0 INNER JOIN RCT1 T1 ON T0.DocNum = T1.DocNum

Regards,

Jitin

[Former Member]

Hi Jitin ,

I am missing lots of information in the query . It didn't help me out

Thank you

Bishal

[former_member583013]

Bishal,

I would still suggest that you take a look at the system reports...I am sure there are some members who would be willing to help you out with the SQL Code...but please remember that this is a forum to get directions for the solution....

It would be unfair to expect someone to write the whole report for you while you should be doing this.........

Suda

[former_member583013]

Use the Transaction Journal Report

Reports > Financials > Accounting > Transaction Journal Report.....Incoming Payment

Or use the Document Journal and Click Expanded and select Incoming Payment