Skip to Content

Running customer report showing only one record of the invoice and last payment date please

Hi there,

I am trying to run a query report of closed customer invoices. Sometimes, customers make multiple payments on different dates towards an invoice to close it out. However, I am trying to run this query to bring record of only one unique invoice with its corresponding last payment date recorded (which brought the invoice status to a close).

Could someone please assist on how I could pull that record up?

Below is my syntax:

SELECT

T2."DocNum" "Invoice#",

T0."CardCode" "Customer Code",

T0."CardName" "Customer Name",

CONCAT(CONCAT(T0."CardName",' ('),(CONCAT(T0."CardCode",')'))) AS "BusinessPartnerNameandCode",

T2."DocStatus",

T0."DocNum" "Last Payment No.",

T2."DocDate" "Invoice Date1",

T2."TaxDate" "Invoice Date2",

T0."DocDate" "Last Payment Date",

DAYS_BETWEEN(T2."TaxDate", T0."DocDate") "Days to Payment",

T2."DocTotal",

T4."PymntGroup",

T3."QryGroup3" "CALGA",

T3."QryGroup4" "REDER",

T3."QryGroup5" "COLMB",

T3."QryGroup6" "MEDIC",

T3."QryGroup7" "PRGEO"

FROM ORCT T0

JOIN RCT2 T1

ON T1."DocNum" = T0."DocNum"

JOIN OINV T2

ON T2."DocEntry" = T1."DocEntry"

JOIN OCRD T3

ON T3."CardCode" = T0."CardCode"

JOIN OCTG T4

ON T4."GroupNum" = T3."GroupNum"

WHERE

T2."DocStatus"='C'

Thank you,

LY

Add a comment
10|10000 characters needed characters exceeded

Related questions

1 Answer

  • Posted on Mar 19 at 12:24 PM

    Hi,

    Try this query,

    SELECT

    T2."DocNum" "Invoice#",

    T0."CardCode" "Customer Code",

    T0."CardName" "Customer Name",

    CONCAT(CONCAT(T0."CardName",' ('),(CONCAT(T0."CardCode",')'))) AS "BusinessPartnerNameandCode",

    T2."DocStatus",

    T0."DocNum" "Last Payment No.",

    T2."DocDate" "Invoice Date1",

    T2."TaxDate" "Invoice Date2",

    MAX(T0."DocDate") "Last Payment Date",

    DAYS_BETWEEN(T2."TaxDate", T0."DocDate") "Days to Payment",

    T2."DocTotal",

    T4."PymntGroup",

    T3."QryGroup3" "CALGA",

    T3."QryGroup4" "REDER",

    T3."QryGroup5" "COLMB",

    T3."QryGroup6" "MEDIC",

    T3."QryGroup7" "PRGEO"

    FROM ORCT T0

    JOIN RCT2 T1

    ON T1."DocNum" = T0."DocNum"

    JOIN OINV T2

    ON T2."DocEntry" = T1."DocEntry"

    JOIN OCRD T3

    ON T3."CardCode" = T0."CardCode"

    JOIN OCTG T4

    ON T4."GroupNum" = T3."GroupNum"

    WHERE

    T2."DocStatus"='C'

    GROUP BY

    T2."DocNum" , T0."CardCode" , T0."CardName" , T0."CardName",T0."CardCode", T2."DocStatus", T0."DocNum" , T2."DocDate" , T2."TaxDate" , T2."TaxDate", T0."DocDate", T2."DocTotal", T4."PymntGroup", T3."QryGroup3" , T3."QryGroup4" , T3."QryGroup5" , T3."QryGroup6" , T3."QryGroup7"

    Regards,

    Nagarajan

    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.