Hi Experts,
A customer wants a query that shows sales invoice and their respective receipts been made on the invoices. The customer wants to see the sales amount, the received amount paid, the days of payment between the sales invoice and received amount.
Also some of the receipts are paid on account and it is reconciled to the various invoices using reconciliation under the banking module and the customer want to see the reconciled documents in the query to the corresponding invoices.
I was able to write a query to show incoming payments and their corresponding sales invoices but i have a challenge on how to show the reconciled amount and their corresponding invoices.
Below is my query but i will be glad if a complete query with reconciliation amount, docnum, invoice docnum ,date are added to my query to make it a complete report for my customer;
SELECT T0.[DocNum] as 'Invoice DocNum', T0.[DocDate] as 'Invoice Date', T0.[CardCode], T0.[CardName], T0.[DocTotal] as 'Invoice Total', T0.[DocTotalFC] as 'Invoice Total FC', T0.[PaidToDate], T0.[PaidFC], T0.[DocCur], T0.[JrnlMemo], T0.[Comments], T2.[DocDate] AS 'Receipt Date', T2.[DocNum] as 'Receipt DocNum', T1.[SumApplied], T1.[AppliedFC], T1.[PaidSum] FROM OINV T0 INNER JOIN RCT2 T1 ON
T0.DocEntry =T1.DocEntry INNER JOIN ORCT T2 ON T1.[DocNum] = T2.[DocEntry] WHERE T0.[CANCELED] ='N' AND T0.[ObjType] =13 AND T2.[DocDate] >=[%0] AND T2.[DocDate] <=[%1]
GROUP BY T0.[DocNum], T0.[DocDate], T0.[CardCode], T0.[CardName], T0.[DocTotal], T0.[DocTotalFC], T0.[PaidToDate], T0.[PaidFC], T0.[DocCur], T0.[JrnlMemo], T0.[Comments], T2.[DocDate], T2.[DocNum], T1.[SumApplied], T1.[AppliedFC], T1.[PaidSum] ORDER BY T0.[DocNum]
Urgent solution will be appreciated.
Regards
Justice