Skip to Content
0

List of all payments made during the year to Suppliers and details of invoices paid

Aug 18, 2017 at 01:05 PM

43

avatar image

Hi, everybody

Our Inland Revenue requires our company to provide a list of all payments made to all Suppliers during the year (whether by cash or cheque)

Each payment should give details of all the invoices paid.

I am trying to write an SQL Query to generate these data, but as I have so little time left, any help will be much appreciated.

Specimen coding much appreciated.

But if not possible, an indication of which tables to use and how to proceed will be OK.

The SQL is complicated because so many tables are involved.

----

I am using these tables (ignoring returns)

(a) Internal Reconciliation: OITR, ITR1

(b) Outgoing Payments: OVPM, VPM1, VPM2

(c) Journal Entries (OJDT,JDT1)

(d) A/P Invoice (OPCH, PCH1)

Is anything missing?

Note that we must start with payments and work back to the invoices paid

Thanks

Leon

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Balaji Selvaraj Aug 18, 2017 at 01:50 PM
1

Hi Leon

Try this

SELECT T2.[CardCode] [Vendor Code], T2.[CardName] [Vendor Name], T2.[DocNum] [Payment Number], T1.[SumApplied] AS [Amt Paid], T2.[DocDate] [Payment Date], T0.[DocNum] [Doc Num], T0.[DocDate] [Doc Date] FROM [dbo].[OPCH] T0 INNER JOIN [dbo].[VPM2] T1 ON T1.[DocEntry] = T0.[DocEntry] INNER JOIN [dbo].[OVPM] T2 ON T2.[DocNum] = T1.[DocNum] WHERE T2.[DocDate] >= [%0] AND T2.[DocDate] <= [%1] GROUP BY T2.[CardCode] , T2.[CardName] , T2.[DocNum] , T1.[SumApplied] , T2.[DocDate] , T0.[DocNum] , T0.[DocDate]
Show 1 Share
10 |10000 characters needed characters left characters exceeded

Hi, Balraji

Thanks for your reply.

Unfortunately, the problem is much more complicated than I thought.

If we do not involve the tables OITR, ITR1 (Internal Reconciliation), it will be impossible to get the details of the Invoices Paid. I tried this approach, but I quickly got entangled in the complexities.

So, I solved the problem by using simplified SQL, and using Excel and manual input of figures to help me,

Using pure SQL would be too complex (because of the OITR/ITR1 tables), and I would not have finished my task before deadline.

Thanks for your effort.

Best Regards,

Leon

0
ANKIT CHAUHAN
Aug 18, 2017 at 01:36 PM
1
Show 1 Share
10 |10000 characters needed characters left characters exceeded

Hi, Ankit

Thanks for your reply.

Unfortunately, none of the threads could help me sort out my problem.

Best Regards,

Leon

0