45: site_use_type,
46: primary_per_type
47: FROM (
48: SELECT party_site_id, ps.site_use_type, ps.primary_per_type
49: FROM hz_party_sites_v ps, hr_organization_information hr
50: WHERE ps.party_id = c_party_id
51: AND ps.status = 'A'
52: AND nvl(ps.end_date_active, sysdate + 1) > sysdate
53: AND hr.organization_id = c_org_id
54: AND hr.org_information_context = c_hr_type
55: AND hr.org_information1 = ps.country
56: UNION
57: SELECT party_site_id, ps.site_use_type, ps.primary_per_type
58: FROM hz_party_sites_v ps
59: WHERE ps.party_id = c_party_id
60: AND ps.status = 'A'
61: AND nvl(ps.end_date_active,sysdate + 1) > sysdate
62: AND NOT EXISTS (