Incoming Payment detail query include CM

[Former Member]

Hi Gurus,

I'm new to SAP B1 and I'm stuck to create query for following requirement.

u2022 Report to show daily payments and outstanding invoices by different payment methods (example below). The purpose of this report is to check sales made during the day and payments u2013 received for those sales

Incoming Payment No. Outstanding Paid Cheque Bank Transfer Credit Card Cash.

12001 0 100 100

12002 0 200 200

12003 300 0

12004 0 400 400

12005 500 0

Current query is following.

SELECT

T0.[DocDate],

T0.[DocNum] AS 'Incoming Payment No.',

T0.[CardName] AS 'Customer Name',

T0.[TrsfrSum],

T0.[CashSum],

T0.[CheckSum],

T0.[CreditSum],

T0.[DocTotal]

FROM ORCT T0

WHERE T0.[DocDate] >=[%0] AND T0.[DocDate] <=[%1]

I want to link sales invoice no. for each rows and show credit amount history as well as sales invoice.

Anyone could help this?

Thanks,

Gerald

[Former Member · Accepted solution]

Hi,

Welcome you post on the forum.

One incoming payment may have multiple invoices. How do you prepare to show them?

Thanks,

Gordon

[Former Member · Accepted solution]

Thanks for reply Gordon.

As you mentioned for periodic incoming payments, I might need to make another query.

For example, find document date before today first and show details which received payment today.

The purpose of this query is about daily incoming payment only for AR invoice made specific date.

[Former Member · Accepted solution]

The invoice data are in RCT2.

You can link RCT2 with ORCT and add condition:

SELECT

T0.DocDate,

T0.DocNum AS 'Incoming Payment No.',

T0.CardName AS 'Customer Name',

T0.TrsfrSum,

T0.CashSum,

T0.CheckSum,

T0.CreditSum,

T0.DocTotal

T2.DocEntry,

T2.SumApplied

FROM ORCT T0

INNER JOIN RCT2 T1 On T1.DocNum=T0.DocNum

WHERE DateDiff(D,T0.DocDate,GetDate())=0

Check this one for more info:

[Former Member · Accepted solution]

Thanks Gordon,

However, I have error message like 'The milti-part identifier "T2.DocEntry, T2.SumApplied and etc' could not be bound.

I found your previous post at

Now, I updated query followings.

SELECT

T0.DocDate as 'Posting Date',

T0.DocNum as 'AR Invoice Number',

T1.CardName,

T1.DocDate as 'Payment Date',

T1.DocNum as 'Incoming Payment Number ',

T0.DocTotal as 'AR Invoice Total',

T1.TrsfrSum,

T1.CashSum,

T1.CreditSum,

T1.CheckSum,

T2.CheckNum as 'Cheque Number'

FROM OINV T0

INNER JOIN ORCT T1 ON T0.ReceiptNum = T1.DocEntry

LEFT JOIN RCT1 T2 ON T1.DocNum = T2.DocNum

Left Join OSLP T3 ON T3.SlpCode= T0.SlpCode

where T0.DocDate >=[%0] and T0.DocDate <=[%1]

I need one more column for AR Invoice Amount which payment has not been received'

Could you help me this out?

Thanks

[Former Member · Accepted solution]

Hi Stephen......

In your above query T0.DocTotal as 'AR Invoice Total' is nothing but AR Invoice Amount......

Regards,

Rahul

[Former Member · Accepted solution]

Thanks for reply. What do you mean 'nothing'?

Basically, I can only see the list of AR Invoices which payment has been made. I want to include list of today's AR invoice which payment has not been paid as well. Is it possible?

Edited by: Stephen Kang on Jul 20, 2011 7:02 AM

[Former Member · Accepted solution]

Hi Stephen.......

Try this......
SELECT
T0.DocDate as 'Posting Date',
T0.DocNum as 'AR Invoice Number',
T0.CardName,
T0.DocDate as 'Payment Date',
T1.DocNum as 'Incoming Payment Number ',
T0.DocTotal as 'AR Invoice Total',
T1.TrsfrSum,
T1.CashSum,
T1.CreditSum,
T1.CheckSum,
T2.CheckNum as 'Cheque Number'

FROM OINV T0
LEFT JOIN ORCT T1 ON T0.ReceiptNum = T1.DocEntry
LEFT JOIN RCT1 T2 ON T1.DocNum = T2.DocNum
Left Join OSLP T3 ON T3.SlpCode= T0.SlpCode
where T0.DocDate >='[%0]' and T0.DocDate <='[%1]'

According to your requirement it seems these table wont give better result, you should include RCT2 also which includes Invoices against which Payment is received.

Regards,

Rahul

[Former Member · Accepted solution]

Wow, I think you resolve this. Thank you very much.

One things...Can I include CM to this query?

I would be appreciated if you could help this..

[Former Member · Accepted solution]

Hi Stephen......

yes offcourse you can include.....

You can have Union all also for this......

Regards,

Rahul

[Former Member · Accepted solution]

Thanks for reply. I tried but I've got error message with UNION ALL

1). [Microsoft][SQL Native Client][SQL Server]Column 'ORCT.DocNum' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. 2). [Microsoft][SQL Native Client][SQL Server]Statement 'Service Contracts' (OCTR) (s) could not be prepared.

[Former Member · Accepted solution]

Stephen.......

Please paste your query here where you modified it with Union All and let me try to do changes.....

Regards,

Rahul

[Former Member · Accepted solution]

Thanks, Rahul

SELECT

T0.DocDate as 'Posting Date',

T0.DocNum as 'AR Invoice Number',

T0.CardName,

T0.DocDate as 'Payment Date',

T1.DocNum as 'Incoming Payment Number ',

T0.DocTotal as 'AR Invoice Total',

T1.TrsfrSum,

T1.CashSum,

T1.CreditSum,

T1.CheckSum,

T2.CheckNum as 'Cheque Number'

FROM OINV T0

LEFT JOIN ORCT T1 ON T0.ReceiptNum = T1.DocEntry

LEFT JOIN RCT1 T2 ON T1.DocNum = T2.DocNum

Left Join OSLP T3 ON T3.SlpCode= T0.SlpCode

where T0.DocDate >='[%0]' and T0.DocDate <='[%1]'

UNION ALL

SELECT

T0.DocDate as 'Posting Date',

T0.DocNum as 'AR Invoice Number',

T0.CardName,

T0.DocDate as 'Payment Date',

T1.DocNum as 'Incoming Payment Number ',

T0.DocTotal as 'AR Invoice Total',

T1.TrsfrSum,

T1.CashSum,

T1.CreditSum,

T1.CheckSum,

T2.CheckNum as 'Cheque Number'

FROM OLIN T0

LEFT JOIN ORCT T1 ON T0.ReceiptNum = T1.DocEntry

LEFT JOIN RCT1 T2 ON T1.DocNum = T2.DocNum

Left Join OSLP T3 ON T3.SlpCode= T0.SlpCode

where T0.DocDate >='[%0]' and T0.DocDate <='[%1]'

[Former Member · Accepted solution]

Hi Stephen.....

You mentioned wrong Table name from Credit Memo.

I simply replaced OLIN to ORIN....

SELECT
T0.DocDate as 'Posting Date',
T0.DocNum as 'AR Invoice Number',
T0.CardName,
T0.DocDate as 'Payment Date',
T1.DocNum as 'Incoming Payment Number ',
T0.DocTotal as 'AR Invoice Total',
T1.TrsfrSum,
T1.CashSum,
T1.CreditSum,
T1.CheckSum,
T2.CheckNum as 'Cheque Number'

FROM OINV T0
LEFT JOIN ORCT T1 ON T0.ReceiptNum = T1.DocEntry
LEFT JOIN RCT1 T2 ON T1.DocNum = T2.DocNum
Left Join OSLP T3 ON T3.SlpCode= T0.SlpCode
where T0.DocDate >='[%0]' and T0.DocDate <='[%1]'

UNION ALL

SELECT
T0.DocDate as 'Posting Date',
T0.DocNum as 'AR Invoice Number',
T0.CardName,
T0.DocDate as 'Payment Date',
T1.DocNum as 'Incoming Payment Number ',
T0.DocTotal as 'AR Invoice Total',
T1.TrsfrSum,
T1.CashSum,
T1.CreditSum,
T1.CheckSum,
T2.CheckNum as 'Cheque Number'

FROM ORIN T0
LEFT JOIN ORCT T1 ON T0.ReceiptNum = T1.DocEntry
LEFT JOIN RCT1 T2 ON T1.DocNum = T2.DocNum
Left Join OSLP T3 ON T3.SlpCode= T0.SlpCode
where T0.DocDate >='[%0]' and T0.DocDate <='[%1]'

Hope this will help you

Regards,

Rahul

[Former Member · Accepted solution]

Ha~..Thanks for correct to me..

How can I sort it out to be displayed.

For example, Cash Sum rows and then Cheque Sum row etc. Maybe Group By?

Sorry to keep asking..:)

Edited by: Stephen Kang on Jul 20, 2011 9:26 AM

Edited by: Stephen Kang on Jul 20, 2011 9:30 AM

[Former Member · Accepted solution]

Stephen.....

It is really awesome that you are showing your interest but as per Forum Rules you have to raise new thread for new type of doubt.

Anyways You can manage those stuffs in Crystal report Very well....

I would suggest you to go with crystal once as it has compatibility to give you Summary Total along with details........

Regards,

Rahul

[Former Member · Accepted solution]

Sorry, I didn't know about that. Thanks for reply.