Query to Find Vendor First Party Location Code in Oracle Apps R12 - Orapoint

Overview: This SQL query is use to get the vendor like first party location code and organization name in Oracle R12 Application.

SELECT   ood.organization_name,
          hr.location_code
   FROM   jai_party_regs jpr,
          jai_party_reg_lines jprl,
          jai_regimes jr,
          jai_regimes jr1,
          jai_regimes jr2,
          jai_reporting_associations jra,
          jai_tax_other_rounding jtor,
          org_organization_definitions ood,
          hr_locations hr,
          ap_suppliers pv,
          ap_supplier_sites_all aps,
          gl_code_combinations_kfv gcc,
          gl_code_combinations_kfv gcc1
  WHERE       jpr.party_reg_id = jprl.party_reg_id
          AND jr.regime_id = jprl.regime_id
          AND jra.regime_id = jr2.regime_id(+)
          AND jpr.party_site_id = hr.location_id
          AND jpr.party_id = ood.organization_id
          AND jra.entity_code(+) = 'FIRST_PARTY'
          AND jra.entity_id(+) = jpr.party_reg_id
          AND jtor.entity_code(+) = 'FIRST_PARTY'
          AND jtor.entity_id(+) = jpr.party_reg_id
          AND jtor.regime_id = jr1.regime_id
          AND aps.vendor_site_id(+) = jprl.tax_authority_site_id
          AND pv.vendor_id = jprl.tax_authority_id
          AND jtor.entity_source_table(+) = 'JAI_PARTY_REGS'
          AND jprl.intercompany_receivable_ccid = gcc.code_combination_id
          AND jprl.intercompany_payable_ccid = gcc1.code_combination_id
          AND jpr.party_type_code IN ('IO')
          GROUP BY ood.organization_name,
           hr.location_code
           order by 1