Incoming Payments for previous date of invoices. Not today's one.

[Former Member]

Hi Experts,

First of all, thanks for Rahul and Gordon for help this query.

About Below query, I can see all today's invoice with payment details, however, there are missing incoming payments which invoice didn't made today. I might need another query to see today's incoming payments which AR invoice made not today.

Could you guys help this?

Thanks

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',

(T0.[DocTotal] - T0.[PaidToDate]) as 'AR Sum',

T1.TrsfrSum,

T1.CashSum,

T1.CheckSum,

T1.CreditSum,

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 DateDiff(D,T0.DocDate,GetDate()) = 0

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 * -1 as 'AR Invoice Total',

T0.PaidToDate * -1 as 'AR Sum',

T1.TrsfrSum,

T1.CashSum,

T1.CheckSum,

T1.CreditSum,

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 DateDiff(D,T0.DocDate,GetDate()) = 0

ORDER BY T1.CreditSum, T1.CheckSum, T1.CashSum, T1.TrsfrSum, 'AR Sum'

[Former Member · Accepted solution]

Hi,

You can replace

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

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

Thanks,

Gordon

[Former Member · Accepted solution]

Thanks Gordon,

However, it shows today's AR Invoice details together.

Is there any other way to show only not today's invoice which incoming payment received today?

[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',
(T0.DocTotal - T0.PaidToDate) as 'AR Sum',
T1.TrsfrSum,
T1.CashSum,
T1.CheckSum,
T1.CreditSum,
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 DateDiff(D,T0.DocDate,GetDate()) = 0 Or (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 * -1 as 'AR Invoice Total',
T0.PaidToDate * -1 as 'AR Sum',
T1.TrsfrSum,
T1.CashSum,
T1.CheckSum,
T1.CreditSum,
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 DateDiff(D,T0.DocDate,GetDate()) = 0 Or (T0.DocDate>='[%0]' And T0.DocDate<='[%1]')

ORDER BY T1.CreditSum, T1.CheckSum, T1.CashSum, T1.TrsfrSum, 'AR Sum'

When you execute above query you get the Date Range Parameter and you have to put the date and the invoice will appear within that date range which will include all previous and today's invoices......

Hope this will help you.....

Regards,

Rahul

[Former Member · Accepted solution]

Hi Rahul,

Thanks for reply.

I don't want to show today's invoice then how query will be?

[Former Member · Accepted solution]

Then simplt remove datediff criteria from where clause and put only Date Range criteria and do not select today's date in To range....

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',
(T0.DocTotal - T0.PaidToDate) as 'AR Sum',
T1.TrsfrSum,
T1.CashSum,
T1.CheckSum,
T1.CreditSum,
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 * -1 as 'AR Invoice Total',
T0.PaidToDate * -1 as 'AR Sum',
T1.TrsfrSum,
T1.CashSum,
T1.CheckSum,
T1.CreditSum,
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]')
 
ORDER BY T1.CreditSum, T1.CheckSum, T1.CashSum, T1.TrsfrSum, 'AR Sum'

Regards,

Rahul

[Former Member · Accepted solution]

Thanks.

However, it shows the date of invoices as well.

Edited by: Stephen Kang on Jul 22, 2011 6:43 AM

[Former Member · Accepted solution]

Hi Stephen.....

It shows the List of Records as per Invoice Date range and as Invoice Date is taken as one of the column into the report it will show Invoice date too.....

Regards,

Rahul

[Former Member · Accepted solution]

Thanks Rahul. I think i didn't explain very well.

Can I just show only the incoming payment lists which AR invoice made before today or specific date before?

I want to exclude the date of invoices.

Edited by: Stephen Kang on Jul 22, 2011 6:55 AM

[Former Member · Accepted solution]

Hi Stephen....

then instead of AR Invoice date you have to define date range criteria as per Receipt Date....

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',
(T0.DocTotal - T0.PaidToDate) as 'AR Sum',
T1.TrsfrSum,
T1.CashSum,
T1.CheckSum,
T1.CreditSum,
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 (T1.DocDate>='[%0]' And T1.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 * -1 as 'AR Invoice Total',
T0.PaidToDate * -1 as 'AR Sum',
T1.TrsfrSum,
T1.CashSum,
T1.CheckSum,
T1.CreditSum,
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 (T1.DocDate>='[%0]' And T1.DocDate<='[%1]')
 
ORDER BY T1.CreditSum, T1.CheckSum, T1.CashSum, T1.TrsfrSum, 'AR Sum'

In above query I just replaced the Invoice date Criteria to Receipt date Criteria

Hope this will help you finally :).............

Regards,

Rahul

[Former Member · Accepted solution]

Really appreciate fast response.

Thanks again to Rahul and Gordon.

Final query is below:

SELECT

T0.DocDate as 'Posting Date',

T0.DocNum as 'AR Invoice Number',

T0.CardName,

T1.DocDate as 'Payment Date',

T1.DocNum as 'Incoming Payment Number ',

T0.DocTotal as 'AR Invoice Total',

(T0.DocTotal - T0.PaidToDate) as 'AR Sum',

T1.TrsfrSum,

T1.CashSum,

T1.CheckSum,

T1.CreditSum,

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 DateDiff(D,T1.DocDate,GetDate()) = 0 AND DateDiff(D,T0.DocDate,GetDate()) > 0

UNION ALL

SELECT

T0.DocDate as 'Posting Date',

T0.DocNum as 'AR Invoice Number',

T0.CardName,

T1.DocDate as 'Payment Date',

T1.DocNum as 'Incoming Payment Number ',

T0.DocTotal * -1 as 'AR Invoice Total',

T0.PaidToDate * -1 as 'AR Sum',

T1.TrsfrSum,

T1.CashSum,

T1.CheckSum,

T1.CreditSum,

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 DateDiff(D,T1.DocDate,GetDate()) = 0 AND DateDiff(D,T0.DocDate,GetDate()) > 0

ORDER BY T1.CreditSum, T1.CheckSum, T1.CashSum, T1.TrsfrSum, 'AR Sum'

[Former Member · Accepted solution]

Great Stephen.....

Keep your Spirit Up........

All the Best,

Rahul