SQL query to get list of Jurisdictions for which Tax Rates has been defined in Oracle APPS R12

SELECT zj.tax

      ,zj.tax_jurisdiction_code

  ,hzg.geography_element2_code state_code

  ,hzg.geography_element3_code county_code

  ,hzg.geography_element4_code city_code

  FROM zx_jurisdictions_b zj

      ,hz_geographies hzg

 WHERE zj.tax_regime_codE='XX_SALE_TAX'

   AND SYSDATE BETWEEN zj.effective_from AND NVL(zj.effective_to,'31-DEC-4999')

   AND SYSDATE BETWEEN hzg.start_date AND hzg.end_date

   AND zj.zone_geography_id=hzg.geography_id

   AND zj.tax=hzg.geography_type

   AND NOT EXISTS (SELECT 1 

                     FROM zx_rates_b zr

                    WHERE zr.tax_regime_code='XX_SALE_TAX'

                      AND zr.tax_jurisdiction_code=zj.tax_jurisdiction_code

   )

ORDER BY zj.tax

        ,zj.tax_jurisdiction_code

    ,hzg.geography_element2_code

,hzg.geography_element3_code

,hzg.geography_element4_code

Query to get Customer Address that doesn't have Geography Reference in Oracle APPS R12

SELECT hca.account_number

      ,hca.account_name

      ,hcs_ship.site_use_code

      ,hl_ship.address1

      ,hl_ship.state

      ,hl_ship.county

      ,hl_ship.city

      ,hl_ship.postal_code

  FROM hz_cust_site_uses_all hcs_ship

      ,hz_cust_acct_sites_all hca_ship

      ,hz_cust_accounts hca

      ,hz_party_sites hps_ship

      ,hz_locations hl_ship

 WHERE hca.cust_account_id=hca_ship.cust_account_id(+)

   AND hcs_ship.cust_acct_site_id(+) = hca_ship.cust_acct_site_id

   AND hca_ship.party_site_id = hps_ship.party_site_id

   AND hps_ship.location_id = hl_ship.location_id

   AND hca.status='A'

   AND hcs_ship.status='A'

   AND hca_ship.status='A'

   AND hl_ship.country='US'

   --AND hca.account_number='1234567890'

   AND NOT EXISTS (SELECT 1 

                     FROM hz_geographies hg

                    WHERE hg.geography_element2_code=hl_ship.state

                      AND UPPER(hl_ship.county)=UPPER(hg.geography_element3_code)

                      AND UPPER(hl_ship.city)=UPPER(hg.geography_element4_code)

                      AND SYSDATE BETWEEN hg.start_date AND hg.end_date

  )