AR Duplicate DUNS

Description

Application: Receivables
Source: Duplicate DUNS Report (XML)
Short Name: ARHDUNDP_XML
DB package: AR_ARHDUNDP_XMLP_PKG

Download Blitz Report™ – World’s fastest reporting and data upload for Oracle EBS

Copy

SELECT
        org.duns_number_c                          duns_number,
        org.organization_name                     party_name,
        party.party_number                          party_number,
        location.address1                             address,
        location.city                                      city,
        location.state                                   state,
        lookup.territory_short_name           country
FROM  hz_organization_profiles           org,
        hz_parties                               party,
        fnd_territories_vl                    lookup,
        hz_locations                            location,
        (
          SELECT  UNIQUE duns_number_c
            FROM  hz_organization_profiles
           WHERE  duns_number_c IS NOT NULL
             AND  actual_content_source = 'DNB'
             AND  effective_end_date IS NULL
             AND  ( ( :p_duns_number IS NOT NULL
                           AND duns_number_c like :p_duns_number )
                        OR :p_duns_number IS NULL )
           GROUP  BY duns_number_c
          HAVING  COUNT(*) > 1 ) duns
 WHERE  org.actual_content_source = 'DNB'
   AND  org.effective_end_date IS NULL
   AND  org.duns_number_c IS NOT NULL
   AND  org.duns_number_c = duns.duns_number_c
   AND  org.party_id = party.party_id
   AND  location.location_id = HZ_DNBUI_PVT.get_location_id(org.party_id, 'DNB')
   AND  location.country = lookup.territory_code
 ORDER BY
	org.duns_number_c