Oracle EBS (ERP)

---Find the Operating Unit with AR and AP and Company code and Location

SELECT DISTINCT hrl.country,
                hroutl_bg.NAME bg,
                hroutl_bg.organization_id,
                lep.legal_entity_id,
                lep.NAME legal_entity,
                hroutl_ou.NAME ou_name,
                hroutl_ou.organization_id org_id,
                hrl.location_id,
                hrl.location_code,
                glev.flex_segment_value company_code,
                (SELECT NAME FROM hr_operating_units
                              WHERE organization_id = arsp.org_id) ar_ou,
               (SELECT NAME FROM hr_operating_units
                              WHERE organization_id = apsp.org_id) ap_ou
FROM xle_entity_profiles          lep,
       xle_registrations            reg,
       hr_locations_all             hrl,
       hz_parties                   hzp,
       fnd_territories_vl           ter,
       hr_operating_units           hro,
       hr_all_organization_units_tl hroutl_bg,
       hr_all_organization_units_tl hroutl_ou,
       hr_organization_units        gloperatingunitseo,
       gl_legal_entities_bsvs       glev,
       ar_system_parameters_all     arsp,
       ap_system_parameters_all     apsp
WHERE lep.transacting_entity_flag        = 'Y'
   AND lep.party_id                       = hzp.party_id
   AND lep.legal_entity_id                = reg.source_id
   AND reg.source_table                   = 'XLE_ENTITY_PROFILES'
   AND hrl.location_id                    = reg.location_id
   AND reg.identifying_flag               = 'Y'
   AND ter.territory_code                 = hrl.country
   AND lep.legal_entity_id                = hro.default_legal_context_id
   AND gloperatingunitseo.organization_id = hro.organization_id
   AND hroutl_bg.organization_id          = hro.business_group_id
   AND hroutl_ou.organization_id          = hro.organization_id
   AND glev.legal_entity_id               = lep.legal_entity_id
   AND hro.set_of_books_id = arsp.set_of_books_id(+)
   AND hro.organization_id = arsp.org_id(+)
   AND hro.set_of_books_id = apsp.set_of_books_id(+)
   AND hro.organization_id = apsp.org_id(+)
Order by company_code

SELECT     ap.segment1 vendor_code,
           ap.vendor_name Suppliers_Name,
           aps.vendor_site_code Supplier_Site_Code,
           ap.VENDOR_TYPE_LOOKUP_CODE Type_Code,
           aps.address_line1,
           aps.address_line2,
           aps.address_line3,
           aps.city,
           aps.state,
           aps.zip,
           aps.Country,
           apc.AREA_CODE,
           apc.PHONE,
           apc.FAX_AREA_CODE,
           apc.FAX,
           apc.EMAIL_ADDRESS,
           ap.PAYMENT_CURRENCY_CODE CURRENCY_CODE,
           (Select Name from hr_operating_units where organization_id=aps.ORG_ID) OU_NAME,
           aps.vat_registration_num,
           aps.auto_tax_calc_flag,
           ap.offset_tax_flag offset_tax_flag_header,
           aps.offset_tax_flag offset_tax_flag_line,
           aps.attribute2 free_field,
           (Select LOCATION_CODE from hr_locations where location_id= aps.ship_to_location_id) ship_location,
           (Select LOCATION_CODE from hr_locations where location_id= aps.bill_to_location_id) bill_location,
           (SELECT terms.NAME
               From ap_terms_tl terms
               WHERE terms.term_id = ap.terms_id
               AND terms.LANGUAGE(+) = USERENV ('LANG')
               AND terms.enabled_flag(+) = 'Y')AS terms_header,
           (SELECT terms.NAME
               From ap_terms_tl terms
               WHERE terms.term_id = aps.terms_id
               AND terms.LANGUAGE(+) = USERENV ('LANG')
               AND terms.enabled_flag(+) = 'Y')AS terms_line,
           ap.attribute9 supplier_category,
            (SELECT concatenated_segments FROM gl_code_combinations_kfv WHERE code_combination_id=aps.ACCTS_PAY_CODE_COMBINATION_ID) ACCTS_AC,
       (SELECT concatenated_segments FROM gl_code_combinations_kfv WHERE code_combination_id=aps.PREPAY_CODE_COMBINATION_ID) PRPAY_AC
    FROM   ap_suppliers ap,
           ap_supplier_sites_all aps,
           ap_supplier_contacts apc
   WHERE       ap.vendor_id = aps.vendor_id
           AND apc.VENDOR_CONTACT_ID(+) = ap.VENDOR_ID
           AND ap.VENDOR_TYPE_LOOKUP_CODE <> 'EMPLOYEE'
           AND aps.vendor_site_code NOT IN 'OFFICE'
           AND ap.enabled_flag ='Y'
           --AND ap.segment1='19173'
ORDER BY   ap.vendor_name,ap.segment1

Find the Operating Unit with AR and AP and Company Code and Location

How to find the Supplier extraction with account script for EBS Tax