sql_id=dvgaumugu1vht


SELECT * FROM ( select p.party_id,p.party_name,p.person_first_name,p.person_last_name,p.party_type,partytype.meaning type_lookup 
,p.party_number,p.tax_reference,p.jgzz_fiscal_code taxpayer_id,p.duns_number_c duns_number,p.known_as,p.known_as2,p.known_as3,
p.known_as4,p.known_as5,p.organization_name_phonetic,p.person_firs 
t_name_phonetic,p.person_last_name_phonetic,ps.location_id,terr.territory_short_name country,p.primary_phone_contact_pt_id,p.pri 
mary_phone_country_code,p.primary_phone_area_code,p.primary_phone_number,p.primary_phone_line_type,p.primary_phone_extension,p.e 
mail_address email,p.url primary_url,certification.meaning certification_level_meaning,registrystatus.meaning registry_meaning,p 
.status party_status from hz_parties p,hz_party_sites ps,fnd_ter ritories_vl terr,fnd_lookup_values partytype,fnd_lookup_values c 
ertification,fnd_lookup_values registrystatus where p.party_type in ('PERSON', 'ORGANIZATION') and registrystatus.lookup_code =  
p.status and ps.party_id(+) = p.party_id and ps.identifying_address_flag(+) = 'Y' and terr.territory_code(+) = p.country and par 
tytype.view_application_id = 222 and partytype.lookup_type = 'PARTY_TYPE' and partytype.language = userenv('LANG') and partytype 
.lookup_code = p.party_type and certification.view_application_id(+) = 222 and certification.lookup_type(+) = 'HZ_PARTY_CERT_LEV 
EL' and certification.language(+) = userenv('LANG') and certification.lookup_code(+) = p.certification_level and registrystatus. 
lookup_type = 'HZ_CPUI_REGISTRY_STATUS' and registrystatus.language = userenv('LANG') AND EXISTS (SELECT 'This is a customer' FR 
OM HZ_CUST_ACCOUNTS hca WHERE hca.PARTY_ID = p.PARTY_ID)) QRSLT WHERE (party_type = :1  and  (party_status='A' or party_status= 
'I')  and exists (    select /*+ push_subq */ null from XXNCV_HZ_CUSTOMER_PARTY_FIND_V hpfv, ar_cust_search_gt acsg    where  hp 
fv.org_per_number = qrslt.party_number and hpfv.customer_id = acsg.customer_id and (acsg.org_id = -1 or acsg.org_id = 88) ))