SQL Query for get sales invoice GST Tax in ax 2012

Description:-

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.

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

Author : Umesh Patel

Related Posts