cancel
Showing results for 
Search instead for 
Did you mean: 

Incoming Payment List

Former Member
0 Kudos

Hi Experts,

The Following query is about to show incoming payment list by customers / Search By payment date.


SELECT 
T0.[CardName] AS 'Customer/Supplier Name', 
T0.[DocTotal] AS 'Document Total', 
T0.[DocDate] AS 'Payment Date' 

FROM  ORCT T0
WHERE T0.[DocDate] = [%0]
ORDER BY T0.[CardName]

I need to show following requirements.

1) exclude incoming payment history for today's sales payment

2) Sub Total by Payment Method whether Cash, Cheque, Credit Card or Bank Transfer.

Is it possible?

Edited by: Stephen Kang on Jul 26, 2011 3:18 AM

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi Stephen......

Try this sample query.....

SELECT 'Cash', T0.[DocNum], T0.[DocDate], T0.[DocDueDate], T0.[CardCode], T0.[CardName], T0.[CashAcct], T0.[CashSum] FROM ORCT T0 Where T0.[CashSum]>0 
Union All
Select 'Cheque', T0.[DocNum], T0.[DocDate], T0.[DocDueDate], T0.[CardCode], T0.[CardName], T0.[CheckAcct], T0.[CreditSum] From ORCT T0  INNER JOIN RCT1 T1 ON T0.DocEntry = T1.DocNum Where T0.[CheckSum]>0  

Union All
Select 'Credit', T0.[DocNum], T0.[DocDate], T0.[DocDueDate], T0.[CardCode], T0.[CardName], T1.[CreditAcct], T0.[CreditSum] From ORCT T0  INNER JOIN RCT3 T1 ON T0.DocEntry = T1.DocNum Where T0.[CreditSum]>0 

Union All
SELECT 'Transfer', T0.[DocNum], T0.[DocDate], T0.[DocDueDate], T0.[CardCode], T0.[CardName], T0.[TrsfrAcct], T0.[TrsfrSum] FROM ORCT T0 Where T0.[TrsfrSum]>0

Hope you get an Idea......

Regards,

Rahul

Former Member
0 Kudos

Thanks Rahul,

I want to get rid of today/s sales invoice payments history. Could you let me know how can I do it?

Former Member
0 Kudos

Hi Stephen......

In order to stay your Today's Sales Payment away in above query you have to add condition as

DateDiff (dd,ORCT.DocDate, GetDate()) != 0

Above condition wont display the Today's Payment........

Regards,

Rahul

Former Member
0 Kudos

Hi Rahul.

Thanks for reply.


SELECT
'CHEQUE',
T0.DocDate as 'Posting Date',
T1.CardName,
T1.DocDate as 'Payment Date',
T1.DocTotal as 'Paid To Date'

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

UNION ALL

SELECT
'CREDIT',
T0.DocDate as 'Posting Date',
T1.CardName,
T1.DocDate as 'Payment Date',
T1.DocTotal as 'Paid To Date'

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

UNION ALL

SELECT
'CASH',
T0.DocDate as 'Posting Date',
T1.CardName,
T1.DocDate as 'Payment Date',
T1.DocTotal as 'Paid To Date'

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

UNION ALL

SELECT
'BANK TRANSFER',
T0.DocDate as 'Posting Date',
T1.CardName,
T1.DocDate as 'Payment Date',
T1.DocTotal as 'Paid To Date'

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

The above "where" statement is perfect, however, the Paid amount is duplicating if incoming payment link to multiple posting date's invoice. How can I keep "WHERE" statement and just show incoming payment total for each customer only?

Former Member
0 Kudos

Hi,

Try this to see if it return what you need for one kind of payment:

SELECT

'BANK TRANSFER' 'Type',

T0.DocDate as 'Posting Date',

T1.CardName,

T1.DocDate as 'Payment Date',

SUM(T2.SumApplied) as 'Paid To Date'

FROM RCT2 T2

INNER JOIN OINV T0 ON T0.DocEntry=T2.DocEntry

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

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

WHERE T1.TrsfrSum > 0 AND DateDiff(D,T1.DocDate,GetDate()) != 0 AND DateDiff(D,T0.DocDate,GetDate()) > 0

GROUP BY T0.DocDate,T1.CardName,T1.DocDate

Thanks,

Gordon

Former Member
0 Kudos

Hi Gordon. Thanks for reply.

The above query result in all the payment date's record and shows seperated amount per records as paid to date even though one incoming payment was.

Conditions:

1) Records might not include today's sales incoming payment. It need to show today's incoming payment which linked previous date of A/R invoice only.

2) Incoming Payment Amount need to show only total amount of incoming payment not seperated as document total for sales A/R Invoice.

Is it possible?

Type CardName Payment Date Incoming Payment Amount

Bank Transfer ABC 29.07.11 10.00

Former Member
0 Kudos

Please clarify what you define for total amount of incoming payment. Is it total per day or per customer?

Former Member
0 Kudos

Hi Gordon,

As you know, incoming payment document no. has total amount of payment whatever payment means and how many invoices are paid.

I need to show that total amount of each incoming payment document. Is it too complicated?

Former Member
0 Kudos

It is not very simple because one Incoming Payment can have multiple invoices, credit memo including partial payment to invoice.

The total must be clearly defined.

Former Member
0 Kudos

Hi Gordon. Thanks for reply.

Total Amount is equal to incoming payment amount (per payment method) even though there could be multiple A/R invoice link with.

Is possible to make query?

Edited by: Stephen Kang on Aug 1, 2011 12:55 AM

Former Member
0 Kudos

I mean is it per each customer or per each customer and date?

Former Member
0 Kudos

I resolved this from group sub total for each customer in Crystal Report Function.

Thanks to Gordon and Rahul again.

Answers (0)