Skip to Content

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

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Aug 18, 2017 at 01:50 PM

    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]
    Add comment
    10|10000 characters needed 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

  • Aug 18, 2017 at 01:36 PM
    Add comment
    10|10000 characters needed characters exceeded