Purchase Tax report including AP Credit memo

[Former Member]

Hi All,

I have the query for purchase tax report. Now i want to add few fields from AP Credit Memo.

Query:

/*select from ORDR t0*/ declare @start as datetime /* where */ set @start = /* t0.taxdate*/ '[%0]'  /*select from ORDR t0*/

declare @end as datetime /* where */ set @end = /* t0.taxdate*/ '[%1]'

SELECT DISTINCT T0.DocDate ,T0.DocNum ,

T0.NumAtCard AS 'Ex Invoice No', T0.CardCode as 'Customer Code', T0.CardName as 'Customer Name', T0.Address,T3.TaxId0 AS' PAN No',T3.TaxId1 as 'TIN No',T3.TaxId3 as'service tax number',

T0.DocTotal as 'Invoice Total',  T0.VatSum as 'Total Tax', T4.[Location],

ISNULL((SELECT SUM(Z0.BaseSum) FROM PCH4 Z0 WHERE Z0.DocEntry = T0.DocEntry AND Z0.staType IN(5)),0) AS 'Base Amount',

  ISNULL((SELECT SUM(Z0.TaxSum) FROM PCH4 Z0 WHERE Z0.DocEntry = T0.DocEntry AND Z0.staType IN(5)),0) as 'Service Tax Amt',

   ISNULL((SELECT SUM(Z0.TaxSum) FROM PCH4 Z0 WHERE Z0.DocEntry = T0.DocEntry AND Z0.staType IN(6)),0) as 'Service _Cess Amt',

    ISNULL((SELECT SUM(Z0.TaxSum) FROM PCH4 Z0 WHERE Z0.DocEntry = T0.DocEntry AND Z0.staType IN(-10)),0) as 'Service_HCess Amt' 

    FROM OPCH T0 INNER JOIN PCH1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN PCH4 T2 ON T0.DocEntry = T2.DocEntry

LEFT OUTER JOIN PCH12 T3 ON T0.DocEntry=T3.DocEntry  left JOIN OLCT T4 ON T1.LocCode = T4.Code

    WHERE  (T0.docdate >= @start and T0.docdate <= @end)  AND (T2.[staType]=5)  ORDER BY 'DocNum'

i need AP credit memo(ORPC) docdate, doc num, base amount, total tax, document total, remarks fields to be added and like against which AP invoice the AP credit memo has been raised.

Please do the needful.

Thanks & Regards,

Ravi.

[kothandaraman_nagarajan · Accepted solution]

Hi,

Try this:

/*select from ORDR t0*/ declare @start as datetime /* where */ set @start = /* t0.taxdate*/ '[%0]'  /*select from ORDR t0*/

declare @end as datetime /* where */ set @end = /* t0.taxdate*/ '[%1]'

SELECT DISTINCT T0.DocDate ,T0.DocNum ,

T0.NumAtCard AS 'Ex Invoice No', T0.CardCode as 'Customer Code', T0.CardName as 'Customer Name', T0.Address,T3.TaxId0 AS' PAN No',T3.TaxId1 as 'TIN No',T3.TaxId3 as'service tax number',

T0.DocTotal as 'Invoice Total',  T0.VatSum as 'Total Tax', T4.[Location], T6.DocNum, T6.docdate,T6.doctotal, T6.comments,

ISNULL((SELECT SUM(Z0.BaseSum) FROM PCH4 Z0 WHERE Z0.DocEntry = T0.DocEntry AND Z0.staType IN(5)),0) AS 'Base Amount',

  ISNULL((SELECT SUM(Z0.TaxSum) FROM PCH4 Z0 WHERE Z0.DocEntry = T0.DocEntry AND Z0.staType IN(5)),0) as 'Service Tax Amt',

   ISNULL((SELECT SUM(Z0.TaxSum) FROM PCH4 Z0 WHERE Z0.DocEntry = T0.DocEntry AND Z0.staType IN(6)),0) as 'Service _Cess Amt',

    ISNULL((SELECT SUM(Z0.TaxSum) FROM PCH4 Z0 WHERE Z0.DocEntry = T0.DocEntry AND Z0.staType IN(-10)),0) as 'Service_HCess Amt'

    FROM OPCH T0 INNER JOIN PCH1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN PCH4 T2 ON T0.DocEntry = T2.DocEntry

LEFT OUTER JOIN PCH12 T3 ON T0.DocEntry=T3.DocEntry  left JOIN OLCT T4 ON T1.LocCode = T4.Code  left join RPC1 T5 on T5.[BaseEntry] = T1.docentry and  T5.[BaseLine] =  T1.[LineNum] INNER JOIN ORPC T6 ON T5.DocEntry = T6.DocEntry

    WHERE  (T0.docdate >= @start and T0.docdate <= @end)  AND (T2.[staType]=5) 

Thanks & Regards,

Nagarajan

[Former Member · Accepted solution]

Hi Nagarajan,

Thanks for the query. I need a change in this query. The query should display all the AP Invoices as well as by side it should display the AP credit memo against that AP invoice.

Is it possible? Can you help me in this?

Thanks,

Ravi.

[kothandaraman_nagarajan · Accepted solution]

Hi,

Try:

/*select from ORDR t0*/ declare @start as datetime /* where */ set @start = /* t0.taxdate*/ '[%0]'  /*select from ORDR t0*/

declare @end as datetime /* where */ set @end = /* t0.taxdate*/ '[%1]'

SELECT DISTINCT T0.DocDate ,T0.DocNum ,

T0.NumAtCard AS 'Ex Invoice No', T0.CardCode as 'Customer Code', T0.CardName as 'Customer Name', T0.Address,T3.TaxId0 AS' PAN No',T3.TaxId1 as 'TIN No',T3.TaxId3 as'service tax number',

T0.DocTotal as 'Invoice Total',  T0.VatSum as 'Total Tax', T4.[Location],

ISNULL((SELECT SUM(Z0.BaseSum) FROM PCH4 Z0 WHERE Z0.DocEntry = T0.DocEntry AND Z0.staType IN(5)),0) AS 'Base Amount',

  ISNULL((SELECT SUM(Z0.TaxSum) FROM PCH4 Z0 WHERE Z0.DocEntry = T0.DocEntry AND Z0.staType IN(5)),0) as 'Service Tax Amt',

   ISNULL((SELECT SUM(Z0.TaxSum) FROM PCH4 Z0 WHERE Z0.DocEntry = T0.DocEntry AND Z0.staType IN(6)),0) as 'Service _Cess Amt',

    ISNULL((SELECT SUM(Z0.TaxSum) FROM PCH4 Z0 WHERE Z0.DocEntry = T0.DocEntry AND Z0.staType IN(-10)),0) as 'Service_HCess Amt',T6.DocNum, T6.docdate,T6.doctotal, T6.comments

    FROM OPCH T0 INNER JOIN PCH1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN PCH4 T2 ON T0.DocEntry = T2.DocEntry

LEFT OUTER JOIN PCH12 T3 ON T0.DocEntry=T3.DocEntry  left JOIN OLCT T4 ON T1.LocCode = T4.Code  left join RPC1 T5 on T5.[BaseEntry] = T1.docentry and  T5.[BaseLine] =  T1.[LineNum] INNER JOIN ORPC T6 ON T5.DocEntry = T6.DocEntry

    WHERE  (T0.docdate >= @start and T0.docdate <= @end)  AND (T2.[staType]=5) 
order by T0.docnum, T6.docnum

Thanks & Regards,

Nagarajan

[Former Member · Accepted solution]

Hi,

Now also it is displaying the same. It should display all the documents as well as the credit memos.

Thanks,

Ravi.

[kothandaraman_nagarajan · Accepted solution]

Post your query result and show me order of requirment.

[Former Member · Accepted solution]

Hi,

If you check this query in OEC India demo databse, the result will give only one line of values. it is ok and the order of fields are also fine. but in OEC database there are 3 invoices are there which includes "Service Tax". I raised AP credit memo against one of the Invoices. now what all i need is, it should display all the three invoices list + this AP credit memo values.

If you can slove this, i can use this query in my client's database.

Thanks,

Ravi.

[kothandaraman_nagarajan · Accepted solution]

Sorry i dont have data to check right now. If possible, post screen shot of above result to understand your requirement. Do you mean,

1. AP invoice

2. AP invoice          AP credit memo

3. AP invoice

[Former Member · Accepted solution]

Hi,

Yes exactly.

Regards,

Ravi.

[kothandaraman_nagarajan · Accepted solution]

Before next step, Why did you used ORDR table in your query for date?

[Former Member · Accepted solution]

Hi,

You can use OPCH only.. Use OPCH and change the query according to my requirement.

Regards,

Ravi.

[kothandaraman_nagarajan · Accepted solution]

Try:

/*select from OPCH t0*/ declare @start as datetime /* where */ set @start = /* t0.Docdate*/ '[%0]'  /*select from OPCH t0*/

declare @end as datetime /* where */ set @end = /* t0.Docdate*/ '[%1]'

SELECT DISTINCT T0.DocDate ,T0.DocNum ,

T0.NumAtCard AS 'Ex Invoice No', T0.CardCode as 'Customer Code', T0.CardName as 'Customer Name', T0.Address,T3.TaxId0 AS' PAN No',T3.TaxId1 as 'TIN No',T3.TaxId3 as'service tax number',

T0.DocTotal as 'Invoice Total',  T0.VatSum as 'Total Tax', T4.[Location],

ISNULL((SELECT SUM(Z0.BaseSum) FROM PCH4 Z0 WHERE Z0.DocEntry = T0.DocEntry AND Z0.staType IN(5)),0) AS 'Base Amount',

  ISNULL((SELECT SUM(Z0.TaxSum) FROM PCH4 Z0 WHERE Z0.DocEntry = T0.DocEntry AND Z0.staType IN(5)),0) as 'Service Tax Amt',

   ISNULL((SELECT SUM(Z0.TaxSum) FROM PCH4 Z0 WHERE Z0.DocEntry = T0.DocEntry AND Z0.staType IN(6)),0) as 'Service _Cess Amt',

    ISNULL((SELECT SUM(Z0.TaxSum) FROM PCH4 Z0 WHERE Z0.DocEntry = T0.DocEntry AND Z0.staType IN(-10)),0) as 'Service_HCess Amt',T6.DocNum, T6.docdate,T6.doctotal, T6.comments

    FROM OPCH T0 INNER JOIN PCH1 T1 ON T0.DocEntry = T1.DocEntry left JOIN PCH4 T2 ON T0.DocEntry = T2.DocEntry

LEFT OUTER JOIN PCH12 T3 ON T0.DocEntry=T3.DocEntry  left JOIN OLCT T4 ON T1.LocCode = T4.Code  left join RPC1 T5 on T5.[BaseEntry] = T1.docentry and  T5.[BaseLine] =  T1.[LineNum] left JOIN ORPC T6 ON T5.DocEntry = T6.DocEntry

    WHERE  T0.docdate >= @start and T0.docdate <= @end   AND T2.[staType]=5
order by T0.docnum, T6.docnum