Skip to Content
avatar image
Former Member

Query Link AR Invoice & AR Credit Note with Date selection

I would like to have the query to link the AR Invoice & AR credit note in the same report but also with the date selection

SELECT T0.[CardCode], T0.[CardName], T0.[DocDate], T0.[DocNum], T0.[DocCur], (T0.[DocTotalFC]-T0.[VatSumFC]+T0.[DpmAmntFC]) as 'Net FC', (T0.[DocTotal]-T0.[VatSum]+T0.[DpmAmnt]) as 'Net LC' FROM OINV T0

UNION ALL

SELECT T0.[CardCode], T0.[CardName], T0.[DocDate], T0.[DocNum], T0.[DocCur],(( -1 * T0.[DocTotalFC]) - (-1 * T0.[VatSumFC])+ (-1 * T0.[DpmAmntFC])) as 'Net FC',(( -1 * T0.[DocTotal])- (-1 * T0.[VatSum])+ (-1 * T0.[DpmAmnt])) as 'Net LC' FROM ORIN T0

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Dec 13, 2016 at 10:49 AM

    Hi Maggie,

    Please give this a try:

    /* select * from OINV x */
    DECLARE @DocDate AS DATETIME
    SET @DocDate = /* x.DocDate */ [%0]
    SELECT T0.[CardCode], T0.[CardName], T0.[DocDate], T0.[DocNum], T0.[DocCur], (T0.[DocTotalFC]-T0.[VatSumFC]+T0.[DpmAmntFC]) as 'Net FC', (T0.[DocTotal]-T0.[VatSum]+T0.[DpmAmnt]) as 'Net LC' 
    FROM OINV T0
    WHERE T0.DocDate = @DocDate
    UNION ALL
    SELECT T0.[CardCode], T0.[CardName], T0.[DocDate], T0.[DocNum], T0.[DocCur],(( -1 * T0.[DocTotalFC]) - (-1 * T0.[VatSumFC])+ (-1 * T0.[DpmAmntFC])) as 'Net FC',(( -1 * T0.[DocTotal])- (-1 * T0.[VatSum])+ (-1 * T0.[DpmAmnt])) as 'Net LC' 
    FROM ORIN T0
    WHERE T0.DocDate = @DocDate

    Regards,

    Johan

    Add comment
    10|10000 characters needed characters exceeded

  • Dec 13, 2016 at 11:06 AM

    Hi,

    Check this please, if it helps:

    select T0.[CardCode], T0.[CardName], T0.[DocDate], T0.[DocNum] as 'AR Invoice Number', T2.DocNum as 'AR Credit Memo Number', T0.[DocCur], (T0.[DocTotalFC]-T0.[VatSumFC]+T0.[DpmAmntFC]) as 'AR Invoice Net FC', (T0.[DocTotal]-T0.[VatSum]+T0.[DpmAmnt]) as 'AR Invoice Net LC', (( -1 * T2.[DocTotalFC]) - (-1 * T2.[VatSumFC])+ (-1 * T2.[DpmAmntFC])) as 'Credit Memo Net FC', (( -1 * T2.[DocTotal])- (-1 * T2.[VatSum])+ (-1 * T2.[DpmAmnt])) as 'Credit Memo Net LC' from OINV t0 inner join INV1 T1 on T1.DocEntry = t0.DocEntry inner join ORIN t2 on t2.DocEntry = t1.TrgetEntry Where t0.DocDate >= [%1] and T0.DocDate <= [%2] Group by T0.[CardCode], T0.[CardName], T0.[DocDate], T0.[DocNum], T2.DocNum, T0.[DocCur], T0.DocTotalFC, t0.VatSumFC, t0.DpmAmntFC,t0.DocTotal, T0.VatSum, t0.DpmAmnt, T2.[DocTotalFC], T2.[VatSumFC], T2.[DpmAmntFC], T2.[DocTotal], T2.[VatSum], T2.[DpmAmnt]

    Regards,

    Jitin

    Add comment
    10|10000 characters needed characters exceeded

  • Dec 13, 2016 at 05:50 PM

    Hello

    Try this...............

    SELECT T0.[CardCode], T0.[CardName], T0.[DocDate], T0.[DocNum], T0.[DocCur], (T0.[DocTotalFC]-T0.[VatSumFC]+T0.[DpmAmntFC]) as 'Net FC', (T0.[DocTotal]-T0.[VatSum]+T0.[DpmAmnt]) as 'Net LC' FROM OINV T0 where t0.DocDate between [%0] and [%1] UNION ALL SELECT T0.[CardCode], T0.[CardName], T0.[DocDate], T0.[DocNum], T0.[DocCur],(( -1 * T0.[DocTotalFC]) - (-1 * T0.[VatSumFC])+ (-1 * T0.[DpmAmntFC])) as 'Net FC',(( -1 * T0.[DocTotal])- (-1 * T0.[VatSum])+ (-1 * T0.[DpmAmnt])) as 'Net LC' FROM ORIN T0 where t0.DocDate between [%0] and [%1]

    Regards

    BHanu

    Add comment
    10|10000 characters needed characters exceeded