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

How to Create Enum Parameter in SSRS Report in ax 2012

Description:-

When you use Enum as a parameter in reports, it works well within the AX client. However, to deploy it to the Enterprise Portal (EP), the parameter lookup must be built through the AX Enum provider. This recipe will discuss how an Enum provider can be added to a report and used in parameters.

This topic provides the steps to use an Enum type as a report parameter. The collection of enumeration values can then be referenced by the report. For example, from the Facility Management sample for Microsoft Dynamics AX, the room type is stored as an enumeration type. You can add a dataset that binds to the ‘Enum name’ enumeration and restrict the report to display a specific room type, such as Cubicle. You will create the dataset as an AX Enum Provider data source type so that the Enum parameter can be accessed from Enterprise Portal in addition to the Microsoft Dynamics AX client.

Creating an Enum Type Parameter

To create a parameter of type Enum, you must have the following:

• Create ENUM from Data Dictionary Node.
• Give style property to Radio button.
• Give UseEnumValue Property to Yes.

Now create Contract class and create Enum parameter method for pass parameter value to ssrs report like below.

I have create SalesInvoiceRegister Enum value and Add following Element in SalesInvoiceRegister Enum and set above property in SalesInvoiceRegister Enum.

• All
• Customer
• Sales Category

After I have used SalesInvoiceRegister Enum in Contract class like below.

Public SalesInvoiceRegister ParmSalesInvoiceRegister (SalesInvoiceRegister _ SalesInvoiceRegister = SalesInvoiceRegister)
{
    SalesInvoiceRegister = _ SalesInvoiceRegister;
    Return SalesInvoiceRegister;
}

• Create Data Procedure class for assign your logic.
• Create SSRS Report design and deployed it.
• You’re Enum Parameter will look like below in SSRS Report.