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

Delivery Document Number need in Report

Hi

i have created a query based on A/R Invoice in which i need to show Delivery Document No

SELECT T0.[CardCode] as 'Business Partner Code', T0.[CardName] as 'Business Partner',convert(varchar(10),

T0.[DocDate],105) as 'Invoice Date',

T0.[DocNum] as 'Invoice No', T0.[DocTotal] as 'Invoice Amount',

T0.[PaidtoDate] as 'Invoice Paid Amount', (T0.[docTotal] - T0.[PaidtoDate]) as 'Remaining Invoice Amount',

case T0.[DocStatus]

when ('C') then 'Closed'

when ('O') then 'Open'

else 'Invalid Parameters' end AS 'Status'

FROM OINV T0

where CardCode between @CardCodeFrom and @CardCodeTo and DocDate between @DocFromDate and @DocToDate

END

Regards

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

1 Answer

  • Best Answer
    Posted on Jun 28, 2016 at 01:50 PM

    Hi,

    Please try this,

    SELECT T0.[CardCode] as 'Business Partner Code', T0.[CardName] as 'Business Partner',convert(varchar(10),

    T0.[DocDate],105) as 'Invoice Date',

    T0.[DocNum] as 'Invoice No', T0.[DocTotal] as 'Invoice Amount',

    T0.[PaidtoDate] as 'Invoice Paid Amount', (T0.[docTotal] - T0.[PaidtoDate]) as 'Remaining Invoice Amount',

    case T0.[DocStatus]

    when ('C') then 'Closed'

    when ('O') then 'Open'

    else 'Invalid Parameters' end AS 'Status', T1.BaseRef as DO

    FROM OINV T0 INNER JOIN INV1 on T0.docentry = T1.Docentry

    where CardCode between @CardCodeFrom and @CardCodeTo and DocDate between @DocFromDate and @DocToDate

    END

    Thanks

    Add a comment
    10|10000 characters needed characters exceeded

    • sorry missed T1,

      SELECT T0.[CardCode] as 'Business Partner Code', T0.[CardName] as 'Business Partner',convert(varchar(10),

      T0.[DocDate],105) as 'Invoice Date',

      T0.[DocNum] as 'Invoice No', T0.[DocTotal] as 'Invoice Amount',

      T0.[PaidtoDate] as 'Invoice Paid Amount', (T0.[docTotal] - T0.[PaidtoDate]) as 'Remaining Invoice Amount',

      case T0.[DocStatus]

      when ('C') then 'Closed'

      when ('O') then 'Open'

      else 'Invalid Parameters' end AS 'Status', T1.BaseRef as DO

      FROM OINV T0 INNER JOIN INV1 T1 ON T0.[DocEntry] = T1.[DocEntry]

      where CardCode between @CardCodeFrom and @CardCodeTo and DocDate between @DocFromDate and @DocToDate

      END

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.