Skip to Content

Tax Query Sample for Item Group Wise – Indian Localisation

Tax Query Sample for Item Group Wise for Indian Localisation ….

SELECT DISTINCT(T0.[DocNum]) AS ‘INV NO’,

   T0.[DocDate] AS ‘INV DATE’,

   T0.[TaxDate] AS “Document Date”,

   T0.[NumAtCard] AS ‘ Vendor Ref. No’,

   T0.[CardName] AS ‘NAME OF THE PARTY’,

   T1.[ItemCode] AS ‘PART NO’,

   T1.[Dscription] AS ‘PART DESCRIPTION’,

   T1.[Quantity],

   T1.[Price],

   T1.[LineTotal] AS ‘BASE AMOUNT’,

   T1.[LineTotal]+t1.[VatSum] AS‘TOTAL’,

   T0.[DocStatus],

   T0.[UserSign],

   T6.[U_Name],

  (SELECT sum(TaxSum)

   FROM PCH4

   WHERE statype=-90

   AND DocEntry=T0.DocEntry) AS ‘BED 10%’,

  (SELECT sum(TaxSum)

   FROM PCH4

   WHERE statype=-60

   AND DocEntry=T0.DocEntry) AS ‘ECESS’,

  (SELECT sum(TaxSum)

   FROM PCH4

   WHERE statype=-55

   AND DocEntry=T0.DocEntry) AS ‘HSCESS’,

  (SELECT sum(TaxSum)

   FROM PCH4

   WHERE statype=4

   AND DocEntry=T0.DocEntry) AS ‘VAT’,

  (SELECT sum(TaxSum)

   FROM PCH4

   WHERE statype=1

   AND DocEntry=T0.DocEntry) AS ‘CST’,

  (SELECT sum(TaxSum)

   FROM PCH4

   WHERE statype=5

   AND DocEntry=T0.DocEntry) AS ‘SERVICETAX’,

  (SELECT sum(TaxSum)

   FROM PCH4

   WHERE statype=6

   AND DocEntry=T0.DocEntry) AS ‘ECESS 2%’,

  (SELECT sum(TaxSum)

   FROM PCH4

   WHERE statype=-10

   AND DocEntry=T0.DocEntry

   AND LineNum=t1.LineNum) AS ‘HSCESS 1%’,

   isnull(

   (SELECT Top 1 T10.TotalExpns

   FROM OPCH T10

   WHERE T10.DocEntry = T0.DocEntry),0) AS ‘FreightAmount’,

   isnull(

   (SELECT Top 1 T10.TaxOnExpSc

   FROM OPCH T10

   WHERE T10.DocEntry = T0.DocEntry),0) AS ‘FreightTax’

FROM OPCH T0

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

INNER JOIN PCH4 T2 ON T0.DocEntry = T2.DocEntry

INNER JOIN OITM T4 ON T1. ItemCode = T4. ItemCode

INNER JOIN OITB T5 ON T4.ItmsGrpCod = T5.ItmsGrpCod

INNER JOIN OCRD T3 ON T3.CardCode=T0.CardCode

INNER JOIN OUSR T6 ON T6.USERID = T0.UserSign

WHERE T0.[DocDate] >=[%0]

  AND T0.[DocDate] <=[%1]

  AND (T5.[ItmsGrpNam]=‘[%3]’

   OR ‘[%3]’=‘ ‘)

  AND (T3.[CardName]=‘[%4]’

   OR ‘[%4]’=‘ ‘)

  AND T1.[BaseType]<>’22’

  AND (T0.[DocStatus]=‘[%5]’

   OR ‘[%5]’=‘ ‘)

  AND T0.Series<>45

ORDER BY T0.[DocDate],

   T0.[DocNum]

Be the first to leave a comment
You must be Logged on to comment or reply to a post.