GST Queries - Master Details

GST Related Queries :-

Supplier simple GST Query:-

Query to get the Supplier master data with GSTN Registration Number

SELECT   asp.segment1 || '-' || VENDOR_SITE_CODE "SupplierCode/AlphaName",

         NULL MainGroup,

         ass.ADDRESS_LINE1,

         ass.ADDRESS_LINE2,

         ass.ADDRESS_LINE3,

         ass.CITY,

         NULL SupplyType,

         asp.vendor_name MailingName,

         STATE state_name,

         flv1.meaning country,

         NULL ConstitutionOfBusiness,

         (SELECT   jprl.REGISTRATION_NUMBER

            FROM   jai_party_regs jpr, jai_party_reg_lines jprl

           WHERE       jprl.PARTY_REG_ID = jpr.PARTY_REG_ID

                   AND jpr.party_site_id = ass.vendor_site_id

                   AND jpr.party_id = asp.vendor_id

                   AND NVL (jprl.effective_from, SYSDATE) <= SYSDATE

                   AND NVL (jprl.effective_to, SYSDATE) >= SYSDATE

                   AND REGISTRATION_TYPE_CODE IN ('GTIN', 'GSTIN'))

            GSTIN,

         DECODE (

            (SELECT   jprl.REGISTRATION_NUMBER

               FROM   jai_party_regs jpr, jai_party_reg_lines jprl

              WHERE       jprl.PARTY_REG_ID = jpr.PARTY_REG_ID

                      AND jpr.party_site_id = ass.vendor_site_id

                      AND jpr.party_id = asp.vendor_id

                      AND NVL (jprl.effective_from, SYSDATE) <= SYSDATE

                      AND NVL (jprl.effective_to, SYSDATE) >= SYSDATE

                      AND REGISTRATION_TYPE_CODE IN ('GTIN', 'GSTIN')),

            NULL,

            'Unregistered',

            'Registered'

         )

            RegistrationStatus,

  FROM   ap_suppliers asp,

         ap_supplier_sites_all ass,

         fnd_lookup_values flv1

 WHERE       asp.vendor_id = ass.vendor_id

         AND ass.country = flv1.LOOKUP_CODE

         AND flv1.LOOKUP_TYPE = 'XXAR_COUNTRY_LOOKUP'

         AND flv1.LANGUAGE = 'US';

        

Customer Simple GST Query:-

Query to get the Customer master data with GSTN Registration Number

SELECT   hca.account_number || '-' || hps.party_site_number

              "CustomerCode/AlphaName",

           NULL MainGroup,

           hl.address1,

           hl.ADDRESS2,

           hl.ADDRESS3,

           hl.city,

           NULL supply_type,

           hp.party_name mailing_name,

           hl.state state_name,

           flv2.meaning country,

           (SELECT   jprl.REGISTRATION_NUMBER

              FROM   jai_party_regs jpr, jai_party_reg_lines jprl

             WHERE       jprl.PARTY_REG_ID = jpr.PARTY_REG_ID

                     AND jpr.party_site_id(+) = hcas.cust_acct_site_id

                     AND jpr.party_id = hca.cust_account_id

                     AND NVL (jprl.effective_from, SYSDATE) <= SYSDATE

                     AND NVL (jprl.effective_to, SYSDATE) >= SYSDATE

                     AND REGISTRATION_TYPE_CODE IN ('GTIN', 'GSTIN')

                     and jpr.party_type_code    IN ('THIRD_PARTY', 'THIRD_PARTY_SITE'))

              gstn,

           DECODE (

              (SELECT   jprl.REGISTRATION_NUMBER

                 FROM   jai_party_regs jpr, jai_party_reg_lines jprl

                WHERE       jprl.PARTY_REG_ID = jpr.PARTY_REG_ID

                        AND jpr.party_site_id(+) = hcas.cust_acct_site_id

                        AND jpr.party_id = hca.cust_account_id

                        AND NVL (jprl.effective_from, SYSDATE) <= SYSDATE

                        AND NVL (jprl.effective_to, SYSDATE) >= SYSDATE

                        AND REGISTRATION_TYPE_CODE IN ('GTIN', 'GSTIN')

                        and jpr.party_type_code    IN ('THIRD_PARTY', 'THIRD_PARTY_SITE')),

              NULL,

              'Unregistered',

              'Registered'

           )

              RegisterStatus,

    FROM   hz_parties hp,

           hz_party_sites hps,

           hz_cust_accounts_all hca,

           hz_cust_acct_sites_all hcas,

           hz_cust_site_uses_all hcsu,

           hz_locations hl,

           FND_LOOKUP_VALUES flv2

   WHERE       1 = 1

           AND hp.party_id = hca.party_id

           AND hca.cust_account_id = hcas.cust_account_id(+)

           AND hps.party_site_id(+) = hcas.party_site_id

           AND hcas.cust_acct_site_id = hcsu.cust_acct_site_id

           AND hps.location_id = hl.location_id(+)

           AND hl.country = flv2.LOOKUP_CODE

           AND flv2.LOOKUP_TYPE = 'XXAR_COUNTRY_LOOKUP'

           AND flv2.LANGUAGE = 'US'

           AND hp.party_type = 'ORGANIZATION'

Item Master GST Query:-

Query to get the Item master data with Inventory Organizations GSTN Registration Number and Item Attribute (Item Class).

SELECT   msi.segment1 "Goods/ItemName",

         msi.primary_unit_of_measure Sales_UOM,

         msi.primary_unit_of_measure Purchase_UOM,

         (SELECT   concatenated_segments

            FROM   gl_code_combinations_kfv

           WHERE   CODE_COMBINATION_ID = msi.sales_account)

            ToAccount_Sales,

         (SELECT   concatenated_segments

            FROM   gl_code_combinations_kfv

           WHERE   CODE_COMBINATION_ID = msi.ENCUMBRANCE_ACCOUNT)

            ToAccount_Purchase,

         1 ConversionRatio,

         DECODE (msi.Service_item_flag, 'Y', 'Services', 'Goods')

            GoodsORServices,

         NULL SACCode,

         NULL ServicesCategory,

         NULL HSNCode,

         DECODE (msi.purchasing_item_flag, 'Y', 'Stockable', 'Non-Stockable')

            StockType,

         msi.primary_unit_of_measure Stockunit,

         (SELECT   CASE

                      WHEN JL.LOOKUP_CODE IN ('FGEX', 'FGIN', 'PRFG')

                      THEN

                         'Finished'

                      WHEN JL.LOOKUP_CODE IN ('OTEX', 'OTIN')

                      THEN

                         'Trading'

                      WHEN JL.LOOKUP_CODE IN ('RMEX', 'RMIN')

                      THEN

                         'Raw Material'

                        else jl.meaning

                   END

            FROM   JAI_ITEM_TEMPL_HDR JTH, JA_LOOKUPS JL

           WHERE       JTH.ORGANIZATION_ID = msi.organization_id

                   AND JTH.INVENTORY_ITEM_ID = msi.inventory_item_id

                   AND JTH.ITEM_CLASSIFICATION = JL.LOOKUP_CODE

                   AND JL.LOOKUP_TYPE(+) = 'JAI_ITEM_CLASS_CD')

            "Goods/Service Type",

         msi.description,

         (SELECT   cst.ITEM_COST

            FROM   cst_item_costs cst, cst_cost_types cct

           WHERE       cst.inventory_item_id = msi.INVENTORY_ITEM_ID

                   AND cst.organization_id = msi.organization_id

                   AND cst.cost_type_id = cct.cost_type_id

                   AND cct.COST_TYPE = 'Average')

            average_cost,

         msi.creation_date START_DATE_ACTIVE,

         msi.END_DATE_ACTIVE,

         (SELECT   jprl.REGISTRATION_NUMBER

            FROM   jai_party_regs jpr, jai_party_reg_lines jprl

           WHERE       jprl.PARTY_REG_ID = jpr.PARTY_REG_ID

                   AND jpr.party_id = ood.organization_id

                   AND NVL (jprl.effective_from, SYSDATE) <= SYSDATE

                   AND NVL (jprl.effective_to, SYSDATE) >= SYSDATE

                   AND REGISTRATION_TYPE_CODE IN ('GTIN', 'GSTIN')

                   AND jpr.party_type_code IN ('IO'))

            "GSTN NUMBER of FIRST PARTY",

         organization_code,

         organization_name,
 (SELECT   rpt.REPORTING_CODE
                               FROM   jai_reporting_associations rpt,
                                      JAI_ITEM_TEMPL_HDR JTH1
                              WHERE   JTH1.ORGANIZATION_ID = 2532
                                      AND JTH1.INVENTORY_ITEM_ID =
                                            msi.inventory_item_id
                                      AND jth1.entity_id =
                                            msi.inventory_item_id
                                      AND jth1.TEMPLATE_HDR_ID =
                                            rpt.ENTITY_ID
                                      AND rpt.entity_code = 'ITEM'
                                      AND rpt.reporting_type_ID = 10000
                                      --     AND rpt.reporting_type_name='HSN Code Type for GST'
                                      AND NVL (rpt.effective_from, SYSDATE) <=
                                            SYSDATE
                                      AND NVL (rpt.effective_to, SYSDATE) >=
                                            SYSDATE)
                               HSNCode

  FROM   mtl_system_items_b msi,

         org_organization_definitions ood,

         apps.fnd_lookup_values ml

 WHERE       msi.organization_id = ood.organization_id

         AND msi.item_type = ml.lookup_code(+)

         AND ml.lookup_type(+) = 'ITEM_TYPE'

         AND ml.language = 'US' ;