Query to find HSN Code and GSTIN

 QUERY to find HSN Code for Items

 SELECT jra.reporting_code
        INTO lc_hsn_code
        FROM jai_reporting_associations jra, jai_regimes jr
       WHERE jra.entity_id =
                (SELECT jith.template_hdr_id
                   FROM jai_item_templ_hdr jith
                  WHERE jith.inventory_item_id = p_item_id
                    AND organization_id = p_organization_id)
         AND jra.entity_code = 'ITEM'
         AND jra.reporting_type_name = 'HSN Code Type for GST'
         AND jra.regime_id = jr.regime_id
         AND jr.regime_name = 'GST-India'
         AND TRUNC (SYSDATE) BETWEEN jra.effective_from
                                 AND NVL (jra.effective_to, TRUNC (SYSDATE))

QUERY to find GSTIn for Party

SELECT registration_number
        INTO lc_gstin_no_mssl
        FROM jai_party_regs jpr, jai_party_reg_lines jprl
       WHERE jpr.party_reg_id = jprl.party_reg_id
         AND jpr.party_id = p_party_id
         AND jpr.party_site_id = p_party_site_id
         AND jpr.org_id = p_org_id
         AND registration_type_code = 'GST-INDIA'
         AND TRUNC (SYSDATE) BETWEEN effective_from
                                 AND TRUNC (NVL (effective_to, SYSDATE))
         AND jpr.party_type_code = p_party_type

QUERY to find total tax for tax type for shipment of Order , trx_id is delivery id

 SELECT SUM (NVL (jtl.rounded_tax_amt_tax_curr, 0))
        INTO v_amount
        FROM jai_tax_lines jtl, jai_tax_types jtt
       WHERE 1 = 1
         AND jtl.tax_type_id = jtt.tax_type_id
         AND jtl.tax_regime_id = jtt.regime_id
         AND jtl.tax_invoice_num = NVL (p_invoice_num, jtl.tax_invoice_num) --Excise Invoice Num
         AND jtl.tax_type_id = NVL (p_tax_type_id, jtl.tax_type_id) -- Tax Type
         AND jtl.trx_id = p_trx_id                                 --Deiviery ID
         AND jtl.trx_line_id = NVL (p_trx_line_id, jtl.trx_line_id) --Delivery Detail ID
         AND jtl.ref_doc_trx_id = p_header_id               -- Order Header ID
         AND jtl.ref_doc_line_id = NVL (p_line_id, jtl.ref_doc_line_id)-- Order Line ID
         AND jtl.tax_rate_code LIKE '%' || UPPER (p_tax_type_name) || '%'

7 comments:

  1. Anonymous11 October 2017 at 03:57

    Hello Sumit, We are on RUP9 GST patch level. What we observed that from front end HSN assigned to item-org is different than shown in query. The one which is showing there is actually assigned to other org. We are very sure about org_id, Item etc.

    ReplyDelete
  2. Unknown17 October 2018 at 23:07

    Thanks for sharing such information, HSN Code plays a crucial role in the taxation of a country and helps to get the accurate rate of tax applicable on any product in a country.

    ReplyDelete
  3. HSN Code11 January 2019 at 01:20

    Thanks for information

    ReplyDelete
  4. MTOracle7 March 2019 at 03:17

    Thanks

    ReplyDelete
  5. Digilin20 April 2020 at 05:45

    To know more, about GST & HSN Code Finder, visit https://vakilsearch.com/gst-hsn-finder

    ReplyDelete
  6. prathab21 April 2020 at 05:50

    thanks for the very usefull infomration get register your own busniess here Company Registration In Mumbai

    ReplyDelete
  7. Anonymous28 January 2021 at 02:36

    Thanks for sharing. Commute Vakilsearch website to HSN Code Finder

    ReplyDelete

Subscribe to: Posts (Atom)