GST Tables- Oracle apps R12

# Getting Vendors/Customers GSTIN number

Sql 1:

For supplier:
select party_reg_id from Jai_Party_Regs where supplier_flag='Y';

party id is vendor id in ap_suppliers
party_site_id is vendor_site_id in ap_supplier_sites_all

For Customer
select party_reg_id from Jai_Party_Regs where customer_flag='Y';


party id is  cust_account_id in hz_cust_accounts
party_site_id is  cust_acct_site_id in hz_cust_acct_sites_all



sql 2:
select REGISTRATION_NUMBER from jai_party_reg_lines where party_reg_id=&party_reg_id (party_Reg_id from 1st query)

----------------------------------------------------------------------

 

# Getting Tax Category

SELECT  JAIR.REGIME_ID,
        JAIR.REGIME_CODE,
        JAIR.REGIME_NAME,
        JAIR.TAX_AUTHORITY_ID,
        JAITT.TAX_TYPE_ID,
        JAITT.TAX_TYPE_CODE,
        JAITT.TAX_TYPE_NAME,
        JAITR.TAX_RATE_ID,
        JAITR.TAX_RATE_CODE,
        JAITR.TAX_RATE_NAME,
        JAITC.TAX_CATEGORY_ID,
        JAITC.TAX_CATEGORY_NAME,
        JAITC.TAX_CATEGORY_DESC,
        JAITCL.TAX_CATEGORY_LINE_ID,
        (SELECT DISTINCT TAX_CATEGORY_LIST
        FROM    JA.JAI_INV_ITM_TAXCTG_DTLS
        WHERE   TAX_CATEGORY_ID = JAITC.TAX_CATEGORY_ID
        ) TAX_CATEGORY_LIST               
FROM    JA.JAI_REGIMES JAIR,
        JA.JAI_TAX_TYPES JAITT,
        JA.JAI_TAX_RATES JAITR,
        JA.JAI_TAX_CATEGORY_LINES JAITCL,
        JA.JAI_TAX_CATEGORIES JAITC
WHERE   JAIR.REGIME_ID = JAITT.REGIME_ID
AND     JAITT.TAX_TYPE_ID =JAITR.TAX_TYPE_ID
AND     JAITT.REGIME_ID = JAITR.REGIME_ID
AND     JAITR.REGIME_ID = JAITCL.REGIME_ID
AND     JAITR.TAX_TYPE_ID = JAITCL.TAX_TYPE_ID
AND     JAITR.TAX_RATE_ID = JAITCL.TAX_RATE_ID
AND     JAITC.TAX_CATEGORY_ID = JAITCL.TAX_CATEGORY_ID
AND     REGIME_CODE IN ('Others-India', 'GST-India')
--AND     JAITCL.TAX_CATEGORY_ID = 10109
ORDER BY JAIR.REGIME_CODE,
JAITT.TAX_TYPE_CODE,
JAITR.TAX_RATE_CODE,
JAITC.TAX_CATEGORY_NAME;