Skip to Content

Need Help in Query

Hello Expert,

   I have created AP invoice and AP Credit memo

  In AP invoice i choose 2 items

IItemCode      Quantity         Unitprice     TaxCode      

I001                  2                  200            Vat@5

I002                  1                  100             Vat@5

AND then Added Credit memo:

I001                 1                    200             Vat@5

I Created Following Query:

SELECT T0.docentry,T0.Docnum,T0.CardCode,T0.CardName,T0.DocDate,T2.stcCode,(T2.baseSum-T5.BaseSum) as 'Tax Base Amt',(T2.taxsum-T5.TaxSum) as 'Tax Amount',(T0.docTotal-T4.DocTotal) as 'Document Total'

FROM [dbo].[OPCH]  T0 INNER JOIN PCH1 T1 ON T0.DocEntry = T1.DocEntry

inner join PCH4 T2 on T1.DocEntry=T2.DocEntry

left outer join RPC1 T3 on T3.BaseEntry=T1.DocEntry and T3.Baseline=T1.Linenum and T3.BaseType=T1.Objtype And T3.visorder=T1.Visorder

inner join ORPC T4 on T3. DocEntry= T4.DocEntry

Inner join RPC4 T5 on T4.DocEntry=T5.DocEntry

where  T0.DocDate>=[%0] and T0.DocDate<=[%1] and (T0.docTotal-T4.DocTotal)<>0

When I execute the query it giving me wrong ans Please help me to sort out this issue.

Regards,

Sandesh

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

4 Answers

  • Jan 14, 2015 at 07:27 AM

    Hi,

    Try:

    SELECT T0.docentry,T0.Docnum,T0.CardCode,T0.CardName,T0.DocDate,T2.stcCode,(T2.baseSum-T5.BaseSum) as 'Tax Base Amt',(T2.taxsum-T5.TaxSum) as 'Tax Amount',(T0.docTotal-T4.DocTotal) as 'Document Total'

    FROM [dbo].[OPCH]  T0 INNER JOIN PCH1 T1 ON T0.DocEntry = T1.DocEntry

    inner join PCH4 T2 on T1.DocEntry=T2.DocEntry

    left outer join RPC1 T3 on T3.BaseEntry=T1.DocEntry and T3.Baseline=T1.Linenum

    inner join ORPC T4 on T3. DocEntry= T4.DocEntry

    Inner join RPC4 T5 on T4.DocEntry=T5.DocEntry

    where  T0.DocDate>=[%0] and T0.DocDate<=[%1] and (T0.docTotal-T4.DocTotal)<>0

    Add comment
    10|10000 characters needed characters exceeded

    • Dear Sir,

        Please try to enter one Doc For AP Invoice and One for Credit memo as shown above.

        I have an issue in Tax Base Amt and Tax Amount.

        Apply any tax in Above doc. This amount is recorded in 4th table.

        Please check and revert.

      Regards,

      Sandesh

  • Jan 14, 2015 at 03:18 PM

    Try:

    SELECT T0.[DocNum], T0.[DocDate], T0.[CardCode], T0.[CardName], T1.[ItemCode], T1.[Dscription], T1.[Quantity], T0.[VatSum] -  T3.[VatSum], T0.[DocTotal] - T3.[DocTotal] FROM OPCH T0  INNER JOIN PCH1 T1 ON T0.DocEntry = T1.DocEntry left join  RPC1 T2 on  T2.[BaseEntry]  = T1.docentry and  T2.[BaseLine]  =  T1.[LineNum] INNER JOIN ORPC T3 ON T2.DocEntry = T3.DocEntry WHERE T0.[DocDate]  between [%0] and [%1]

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jan 15, 2015 at 01:53 AM

    Hi Sandesh,

    Try this:

    SELECT T0.docentry,T0.Docnum,T0.CardCode,T0.CardName,T0.DocDate,T2.stcCode,(T2.baseSum-T5.BaseSum) as 'Tax Base Amt',(T2.taxsum-T5.TaxSum) as 'Tax Amount',(T0.docTotal-T4.DocTotal) as 'Document Total'

    FROM [dbo].[OPCH] T0

    INNER JOIN PCH1 T1 ON T0.DocEntry = T1.DocEntry

    inner join PCH4 T2 on T0.DocEntry = T2.DocEntry

    left join RPC1 T3 on T3.BaseEntry=T1.DocEntry and T3.Baseline=T1.Linenum and T3.BaseType=T1.Objtype And T3.visorder=T1.Visorder

    left join ORPC T4 on T3. DocEntry= T4.DocEntry

    left join RPC4 T5 on T4.DocEntry=T5.DocEntry

    where  T0.DocDate>=[%0] and T0.DocDate<=[%1] and (T0.docTotal-T4.DocTotal)<>0

    However, we don't have any data for PCH4/RPC4. You need to find the link between PCH1 and PCH4.

    Thanks,

    Gordon

    Add comment
    10|10000 characters needed characters exceeded

  • Jan 20, 2015 at 01:01 AM

    Any update?

    Add comment
    10|10000 characters needed characters exceeded