SQL Query to get sales 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. Here I have given sample demonstration to get sales 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, Sales 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 taxRegistration is DirPartyLocation and registrationNumber 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.

SQL Query:

SELECT 
       CUSTINVOICEJOUR.SALESID AS 'SALES ORDER NUMBER',
       CUSTINVOICEJOUR.INVOICEACCOUNT AS VENDACCOUNT,
       DIRPARTYTABLE.NAME AS 'CUSTOMER NAME',
       TAXREGISTRATIONNUMBERS_IN.REGISTRATIONNUMBER AS GSTIN, 
       HSNCODETABLE_IN.CODE AS 'HSN CODE',
       CUSTINVOICEJOUR.INVOICEID AS INVOICEID,
       CUSTINVOICEJOUR.LEDGERVOUCHER AS VOUCHER,
       CAST(CUSTINVOICEJOUR.SUMTAX AS DECIMAL(10,2)) AS 'SALES TAX',
       CUSTINVOICEJOUR.CURRENCYCODE AS CURRENCY,
       CAST(CUSTINVOICEJOUR.EXCHRATE/100 AS DECIMAL(10,2)) AS 'EXCHANGE RATE',
       TAXTRANS.SOURCERECID AS 'CUSTINVOICETRANS (RECID)',    
       CAST(CUSTINVOICETRANS.LINENUM AS DECIMAL(10,2)) AS 'LINE NUMBER',
       CUSTINVOICETRANS.ITEMID AS ITEMID,
       CUSTINVOICETRANS.NAME AS 'ITEM NAME',
       CAST(CUSTINVOICETRANS.QTY AS DECIMAL(10,2)) AS QUANTITY,
       CAST(CUSTINVOICETRANS.PRICEUNIT AS DECIMAL(10,2)) AS 'PRICE UNIT',
       CAST(CUSTINVOICETRANS.LINEAMOUNT AS DECIMAL(10,2)) AS 'LINE AMOUNT',
       CAST(CUSTINVOICETRANS.LINEAMOUNTMST AS DECIMAL(10,2)) AS 'LINE AMOUNT (INR)',
       CAST(CUSTINVOICETRANS.DISCAMOUNT AS DECIMAL(10,2)) AS DICOUNT,
       CAST(CUSTINVOICETRANS.DISCAMOUNT * CUSTINVOICEJOUR.EXCHRATE/100 AS DECIMAL(10,2)) AS 'DICOUNT (INR)',
       CAST(CUSTINVOICETRANS.DISCPERCENT AS DECIMAL(10,2)) AS 'DISCOUNT PERCENTAGE',     
       CAST(MARKUPTRANS.CALCULATEDAMOUNT AS DECIMAL(10,2)) AS 'TOTAL CHARGES',
       CAST(MARKUPTRANS.CALCULATEDAMOUNT * CUSTINVOICEJOUR.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(CUSTINVOICEJOUR.INVOICEAMOUNT AS DECIMAL(10,2)) AS 'INVOICE AMOUNT',
       CAST(CUSTINVOICEJOUR.INVOICEAMOUNTMST AS DECIMAL(10,2)) AS 'INVOICE AMOUNT (INR)'
       --CAST((TAXWITHHOLDTRANS_IN.TAXWITHHOLDAMOUNTCUR * -1)  AS DECIMAL(10,2)) AS 'TCS AMOUNT'
       FROM CUSTINVOICETRANS
JOIN CUSTINVOICEJOUR ON CUSTINVOICEJOUR.INVOICEID = CUSTINVOICETRANS.INVOICEID    
       AND CUSTINVOICEJOUR.SALESID = CUSTINVOICETRANS.SALESID
       AND CUSTINVOICEJOUR.NUMBERSEQUENCEGROUP = CUSTINVOICETRANS.NUMBERSEQUENCEGROUP
       AND CUSTINVOICEJOUR.INVOICEDATE = CUSTINVOICETRANS.INVOICEDATE
--LEFT OUTER JOIN TAXWITHHOLDTRANS_IN ON TAXWITHHOLDTRANS_IN.VOUCHER = CUSTINVOICEJOUR.LEDGERVOUCHER
LEFT OUTER JOIN TAXTRANS ON TAXTRANS.SOURCERECID = CUSTINVOICETRANS.RECID 
       AND    TAXTRANS.VOUCHER = CUSTINVOICEJOUR.LEDGERVOUCHER
       AND TAXTRANS.TRANSDATE = CUSTINVOICEJOUR.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 = CUSTINVOICETRANS.RECID    
LEFT OUTER JOIN VENDTABLE ON VENDTABLE.ACCOUNTNUM = CUSTINVOICEJOUR.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 = CUSTINVOICETRANS.ITEMID
LEFT OUTER JOIN HSNCODETABLE_IN ON HSNCODETABLE_IN.RECID = INVENTTABLE.HSNCODETABLE_IN
where CUSTINVOICEJOUR.SALESID = 'SalesOrderNumber'
GROUP BY 
       --TAXWITHHOLDTRANS_IN.TAXWITHHOLDAMOUNTCUR,
       TAXTRANS.TAXCODE,
       TAXTRANS.TAXACCOUNTTYPE,
       TAXTRANS.TAXAMOUNT,
       TAXTRANS.SOURCEREGULATEAMOUNTCUR,
       CUSTINVOICEJOUR.INVOICEID,
       CUSTINVOICEJOUR.SALESID,
       CUSTINVOICEJOUR.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,
       CUSTINVOICETRANS.ITEMID,
       CUSTINVOICETRANS.NAME,
       CUSTINVOICETRANS.LINEAMOUNT,
       CUSTINVOICETRANS.LINEAMOUNTMST,
       CUSTINVOICETRANS.RECID,
       CUSTINVOICEJOUR.INVOICEAMOUNT,
       CUSTINVOICEJOUR.INVOICEAMOUNTMST,
       CUSTINVOICEJOUR.INVOICEACCOUNT,
       CUSTINVOICEJOUR.CURRENCYCODE,
       CUSTINVOICEJOUR.EXCHRATE,
       CUSTINVOICEJOUR.LEDGERVOUCHER,
       CUSTINVOICEJOUR.SUMTAX,
       CUSTINVOICETRANS.QTY,
       CUSTINVOICETRANS.PRICEUNIT,
       CUSTINVOICETRANS.DISCAMOUNT,
       CUSTINVOICETRANS.DISCPERCENT,
       CUSTINVOICETRANS.LINENUM
ORDER BY 
       CUSTINVOICEJOUR.INVOICEID ASC