[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select * from
(
select /*+ cardinality( tmp 10 ) */ pv_locator.geocode_distance (l_geo, hzl.geometry, l_dist_unit) dis,
tmp.party_id, org.party_name, hzl.address1, hzl.address2, hzl.address3,
hzl.city, hzl.state, hzl.country, hzl.postal_code,
org.url, cp.phone_country_code, cp.phone_area_code, cp.phone_number, rownum rn
from
(
select p.party_id from
(SELECT column_value party_id FROM TABLE (CAST( x_matched_id AS JTF_NUMBER_TABLE)) ) p
) tmp,
hz_parties org,
hz_party_sites hzs,
hz_locations hzl,
hz_contact_points cp,
pv_partner_profiles pvpp
where tmp.party_id = pvpp.partner_id
and org.party_id = pvpp.partner_party_id
and org.party_type = 'ORGANIZATION'
and org.party_id = cp.owner_table_id (+)
and cp.owner_table_name (+) = 'HZ_PARTIES'
and cp.contact_point_type (+) = 'PHONE'
and cp.primary_flag (+) = 'Y'
and org.party_id = hzs.party_id
and hzs.location_id = hzl.location_id
and hzs.identifying_address_flag = 'Y'
-- and UPPER(hzl.CITY) = UPPER(p_customer_city)
and hzl.geometry is not null
)
where dis <= p_distance order by 1 asc;
,p_attr_selection_mode => 'AND'
,p_att_delmter => '+++'
,p_selection_criteria => 'ALL'
,p_resource_id => NULL
,p_lead_id => NULL
,p_auto_match_flag => 'N'
,x_matched_id => x_matched_id
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
select hzl.geometry,hzl.address1,hzl.address2,hzl.address3,hzl.city,hzl.state,hzl.country,hzl.postal_code
from hz_locations hzl
where hzl.location_id =pc_location_id;
'select * from ( ' ||
' select /*+ leading(c) */ pv_locator.geocode_distance(:1,hzl.geometry,:2) dis, ' ||
' pvpp.partner_id party_id, ' ||
' org.party_name, '||
' hzl.address1, '||
' hzl.address2, '||
' hzl.address3, '||
' hzl.city, '||
' hzl.state, '||
' hzl.country, '||
' hzl.postal_code, '||
' rownum rn '||
' from hz_parties org, ' ||
' hz_party_sites hzs, ' ||
' hz_locations hzl, '||
' pv_partner_profiles pvpp, '||
' (SELECT * ' ||
' FROM (SELECT column_value party_id ' ||
' FROM (SELECT column_value ' ||
' FROM TABLE (CAST(:p_partner_tbl AS JTF_NUMBER_TABLE))))) c ' ||
' where pvpp.partner_id = c.party_id ' ||
' and org.party_id (+) = pvpp.partner_party_id '||
' and org.party_type (+) = '||''''||'ORGANIZATION'||''''||
' and org.party_id = hzs.party_id (+) '||
' and hzs.location_id = hzl.location_id (+) and '||
' hzs.identifying_address_flag (+) = '||''''||'Y'||'''' ;
-- The second SELECT statement actually starts with /*+ leading(c) */
-- --------------------------------------------------------------------------------
/*
SELECT *
FROM (SELECT pv_locator.geocode_distance(:1,hzl.geometry,:2) dis,
pvpp.partner_id party_id, org.party_name, hzl.address1,
hzl.address2, hzl.address3, hzl.city, hzl.state, hzl.country,
hzl.postal_code, rownum rn
FROM hz_parties org,
hz_party_sites hzs,
hz_locations hzl,
pv_partner_profiles pvpp,
(SELECT *
FROM (SELECT column_value party_id
FROM (SELECT column_value
FROM TABLE(CAST(:p_partner_tbl AS JTF_NUMBER_TABLE))))) c
WHERE pvpp.partner_id = c.party_id and
org.party_id (+) = pvpp.partner_party_id and
org.party_type (+) = 'ORGANIZATION' and
org.party_id = hzs.party_id (+) and
hzs.location_id = hzl.location_id (+) and
hzs.identifying_address_flag (+) = 'Y' and
hzl.geometry is not null)
WHERE dis<=:3
ORDER BY 1 ASC;
/*SELECT latitude_str
INTO latitude
FROM DUAL;
/*SELECT longitude_str
INTO longitude
FROM DUAL;