SQL Query to get purchase invoice GST details Dynamics Ax 2012

Overview:-

In this article we will see about how to get GST tax rate and tax amount through SQL query. Recently I came across a requirement which needs to print total tax amount in sales order confirmation report after India GST update. Along with that I need to print tax amount based ontax type (GST, custom duty) & tax components (i.e. CGST, SGST, IGST etc.).

Here I have given sample demonstration to get purchase invoice GST tax rate and tax amount in ax 2012. Using SQL query you can check GST tax rate and tax amount.

here I have create SQL query for get sales tax, voucher, currency code, exchange rate, item number, item quantity, purch price, line amount, discount amount, charges amount, discount percentage, GST rate and tax amount.

Here also you can get tax registration number by party name in ax 2012. Intermediate table for dirpartytable and tax registration is dirpartylocation and registration number is the field to get value of tax registration number.

You can also get line amount in INR also if you’re sales order in foreign currency. If you get IGST amount then here I have also get IGST in INR. Below is the SQL query to get tax amount details based on tax type & tax component for a given sales order. In this example I have taken tax type as GST and tax components are CGST, SGST, IGST, BCD, ECESS C, IGST CUS, SHECESS C.

SQL Query:

SELECT vendinvoicejour.purchid
       AS
       'PURCHASE ORDER NUMBER',
       vendinvoicejour.invoiceaccount
       AS VENDACCOUNT,
       dirpartytable.NAME
       AS 'VENDOR NAME',
       taxregistrationnumbers_in.registrationnumber
       AS GSTIN,
       hsncodetable_in.code
       AS 'HSN CODE',
       vendinvoicejour.invoiceid
       AS INVOICEID,
       vendinvoicejour.ledgervoucher
       AS VOUCHER,
       Cast(vendinvoicejour.sumtax AS DECIMAL(10, 2))
       AS 'SALES TAX',
       vendinvoicejour.currencycode
       AS CURRENCY,
       Cast(vendinvoicejour.exchrate / 100 AS DECIMAL(10, 2))
       AS 'EXCHANGE RATE',
       taxtrans.sourcerecid
       AS 'VENDINVOICETRANS (RECID)',
       Cast(vendinvoicetrans.linenum AS DECIMAL(10, 2))
       AS 'LINE NUMBER',
       vendinvoicetrans.itemid
       AS ITEMID,
       vendinvoicetrans.NAME
       AS 'ITEM NAME',
       Cast(vendinvoicetrans.qty AS DECIMAL(10, 2))
       AS QUANTITY,
       Cast(vendinvoicetrans.priceunit AS DECIMAL(10, 2))
       AS 'PRICE UNIT',
       Cast(vendinvoicetrans.lineamount AS DECIMAL(10, 2))
       AS 'LINE AMOUNT',
       Cast(vendinvoicetrans.lineamountmst AS DECIMAL(10, 2))
       AS 'LINE AMOUNT (INR)',
       Cast(vendinvoicetrans.discamount AS DECIMAL(10, 2))
       AS DICOUNT,
       Cast(vendinvoicetrans.discamount * vendinvoicejour.exchrate / 100 AS
            DECIMAL(10, 2))
       AS 'DICOUNT (INR)',
       Cast(vendinvoicetrans.discpercent AS DECIMAL(10, 2))
       AS 'DISCOUNT PERCENTAGE',
       Cast(markuptrans.calculatedamount AS DECIMAL(10, 2))
       AS 'TOTAL CHARGES',
       Cast(markuptrans.calculatedamount * vendinvoicejour.exchrate / 100 AS
            DECIMAL(10, 2))
       AS 'TOTAL CHARGES (INR)',
       taxtrans.taxcode
       AS 'TAX CODE',
       taxtrans.taxaccounttype,
       Cast(taxtrans.taxvalue AS DECIMAL(10, 2))
       AS 'RATE',
       Cast(taxtrans.sourceregulateamountcur AS DECIMAL(10, 2))
       AS 'TAX AMOUNT',
       Cast(taxtrans.taxamount AS DECIMAL(10, 2))
       AS 'TAX AMOUNT (INR)',
       Cast(vendinvoicejour.invoiceamount AS DECIMAL(10, 2))
       AS 'INVOICE AMOUNT',
       Cast(vendinvoicejour.invoiceamountmst AS DECIMAL(10, 2))
       AS
       'INVOICE AMOUNT (INR)',
       Cast(( taxwithholdtrans_in.taxwithholdamountcur * -1 ) AS DECIMAL(10, 2))
       AS
       'TCS AMOUNT'
FROM   vendinvoicetrans
       JOIN vendinvoicejour
         ON vendinvoicejour.invoiceid = vendinvoicetrans.invoiceid
            AND vendinvoicejour.purchid = vendinvoicetrans.purchid
            AND vendinvoicejour.invoicedate = vendinvoicetrans.invoicedate
            AND vendinvoicejour.numbersequencegroup =
                vendinvoicetrans.numbersequencegroup
            AND vendinvoicejour.internalinvoiceid =
                vendinvoicetrans.internalinvoiceid
       LEFT OUTER JOIN taxwithholdtrans_in
                    ON taxwithholdtrans_in.voucher =
                       vendinvoicejour.ledgervoucher
       LEFT OUTER JOIN taxtrans
                    ON taxtrans.sourcerecid = vendinvoicetrans.recid
                       AND taxtrans.voucher = vendinvoicejour.ledgervoucher
                       AND taxtrans.transdate = vendinvoicejour.invoicedate
                       AND taxtrans.taxaccounttype = 1
                       AND taxtrans.taxcode IN ( 'IGST', 'CGST', 'SGST' )
       --,'BCD','ECESS C','IGST CUS','SHECESS C')
       LEFT OUTER JOIN markuptrans
                    ON markuptrans.transrecid = vendinvoicetrans.recid
       LEFT OUTER JOIN vendtable
                    ON vendtable.accountnum = vendinvoicejour.invoiceaccount
       LEFT OUTER JOIN dirpartytable
                    ON dirpartytable.recid = vendtable.party
       LEFT OUTER JOIN taxinformation_in
                    ON taxinformation_in.registrationlocation =
                       dirpartytable.primaryaddresslocation
                       AND taxinformation_in.isprimary = 1
       LEFT OUTER JOIN taxregistrationnumbers_in
                    ON taxregistrationnumbers_in.recid = taxinformation_in.gstin
       LEFT OUTER JOIN inventtable
                    ON inventtable.itemid = vendinvoicetrans.itemid
       LEFT OUTER JOIN hsncodetable_in
                    ON hsncodetable_in.recid = inventtable.hsncodetable_in
WHERE  --VENDINVOICEJOUR.PURCHID = 'Purchase Order Number'
  vendinvoicejour.invoiceid = 'Invoice Number'
GROUP  BY taxwithholdtrans_in.taxwithholdamountcur,
          taxtrans.taxcode,
          taxtrans.taxaccounttype,
          taxtrans.taxamount,
          taxtrans.sourceregulateamountcur,
          vendinvoicejour.invoiceid,
          vendinvoicejour.purchid,
          vendinvoicejour.invoiceaccount,
          taxtrans.sourcerecid,
          taxtrans.taxvalue,
          dirpartytable.NAME,
          dirpartytable.primaryaddresslocation,
          taxregistrationnumbers_in.registrationnumber,
          taxinformation_in.gstin,
          taxinformation_in.registrationlocation,
          hsncodetable_in.code,
          vendtable.party,
          vendtable.accountnum,
          markuptrans.calculatedamount,
          markuptrans.transrecid,
          vendinvoicetrans.itemid,
          vendinvoicetrans.NAME,
          vendinvoicetrans.lineamount,
          vendinvoicetrans.lineamountmst,
          vendinvoicetrans.recid,
          vendinvoicejour.invoiceamount,
          vendinvoicejour.invoiceamountmst,
          vendinvoicejour.invoiceaccount,
          vendinvoicejour.currencycode,
          vendinvoicejour.exchrate,
          vendinvoicejour.ledgervoucher,
          vendinvoicejour.sumtax,
          vendinvoicetrans.qty,
          vendinvoicetrans.priceunit,
          vendinvoicetrans.discamount,
          vendinvoicetrans.discpercent,
          vendinvoicetrans.linenum
ORDER  BY vendinvoicejour.invoiceid ASC