Skip to Content
author's profile photo Former Member
Former Member

Query for A/R payment details

I would like to query the business one database for all of the payment details (what invoice(s)...) were paid on a specific check. It would sound something like this:

I can't seem to find the tables involved.

select (all detail records where payment applied) where check number = 'X'

I can find the check number and amount, but can not find how it is linked to invoices and/or credit memos.

Thanks in advance, Sam

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

1 Answer

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Jan 30, 2008 at 09:04 PM

    The tables that may want to query are ORCT, RCT1 and RCT2.

    See the SQL example below

    SELECT T1.[DocNum] AS 'Payment Number', T1.[DocDate] AS 'Payment Date', 
    T0.[CheckNum] AS 'Check Number', T0.[CheckSum] AS 'Check Amount', 
    T0.[BankCode] AS 'Account No.', T3.[DocNum] AS 'Invoice No', T2.[SumApplied] AS 'Paid to Invoice' 
    FROM  [dbo].[RCT1] T0  INNER  JOIN [dbo].[ORCT] T1  ON  T1.[DocNum] = T0.DocNum  
    INNER  JOIN [dbo].[RCT2] T2  ON  T2.[DocNum] = T1.DocNum 
    INNER  JOIN [dbo].[OINV] T3 ON T3.DocEntry = T2.DocEntry

    Suda

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.