Query to retrive Supplier Relationship Detail in iSupplier Portal

Below given query will give you supplier sites and its relationship details.

Query:


SELECT ExtRelationshipsEO.RELATIONSHIP_ID,
              ExtRelationshipsEO.PARTY_ID,
              hzp_inv.party_name SUPPLIER_NAME,
              ExtRelationshipsEO.SUPPLIER_SITE_ID,
              apsite_inv.vendor_site_code SUPPLIER_SITE_NAME,
              ExtRelationshipsEO.REMIT_PARTY_ID,
              hzp_rmt.party_name REMIT_SUPPLIER_NAME,
              ExtRelationshipsEO.REMIT_SUPPLIER_SITE_ID,
              apsite_rmt.vendor_site_code REMIT_SUPPLIER_SITE_NAME,
              ExtRelationshipsEO.FROM_DATE,
              ExtRelationshipsEO.TO_DATE,
              ExtRelationshipsEO.PRIMARY_FLAG,
              ExtRelationshipsEO.ADDITIONAL_INFORMATION,
              ExtRelationshipsEO.ACTIVE,
              ExtRelationshipsEO.CREATED_BY,
              ExtRelationshipsEO.CREATION_DATE,
              ExtRelationshipsEO.LAST_UPDATED_BY,
              ExtRelationshipsEO.LAST_UPDATE_DATE,
              ExtRelationshipsEO.LAST_UPDATE_LOGIN,
              ExtRelationshipsEO.OBJECT_VERSION_NUMBER,
              apsite_inv.org_id,
              hrou.name "Operating Unit Name"
   FROM IBY_EXT_PAYEE_RELATIONSHIPS ExtRelationshipsEO,
            hz_parties hzp_inv,
            hz_parties hzp_rmt,
            ap_supplier_sites_all apsite_inv,
            ap_supplier_sites_all apsite_rmt,
            hr_operating_units hrou
   WHERE ExtRelationshipsEO.party_id = hzp_inv.party_id
   AND ExtRelationshipsEO.remit_party_id = hzp_rmt.party_id
   AND ExtRelationshipsEO.supplier_site_id = apsite_inv.vendor_site_id
   AND ExtRelationshipsEO.remit_supplier_site_id = apsite_rmt.vendor_site_id
   AND hrou.organization_id = apsite_inv.org_id;