[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT p.party_name,
p.party_number,
p.party_id,
s.score
FROM hz_matched_parties_gt s,
hz_parties p
WHERE s.party_id = p.party_id
AND s.search_context_id = i_ctx_id
AND NVL(s.score,0) >= 0
-- AND DECODE(i_status,'ALL','ALL',NVL(p.status,'A'))= i_status
ORDER BY s.score desc;
SELECT gs.score,
gs.party_site_id,
l.address1,
l.city,
l.state,
l.postal_code,
s.identifying_address_flag
FROM hz_matched_party_sites_gt gs,
hz_party_sites s,
hz_locations l
WHERE gs.search_context_id = i_ctx_id
AND gs.party_id = i_party_id
AND NVL(gs.score,0) >= 0
AND gs.party_site_id = s.party_site_id
AND s.location_id = l.location_id
AND DECODE(prof_status,'Y','A',NVL(s.status,'A')) = NVL(s.status,'A');
select a.score,
o.org_contact_id,
r.party_id rel_pty_id,
r.relationship_code rel_code,
p.party_id person_id,
p.party_name,
p.party_number
from hz_matched_contacts_gt a,
hz_org_contacts o,
hz_relationships r,
hz_parties p
where a.search_context_id = i_ctx_id
and a.party_id = i_party_id
and a.org_contact_id = o.org_contact_id
and nvl(a.score,0) >= 0
and o.party_site_id IS NULL
and o.party_relationship_id = r.relationship_id
and r.directional_flag = 'B'
and r.object_id = p.party_id;
SELECT gc.org_contact_id ,
gc.score ,
p.party_name ,
p.party_number ,
p.party_id person_id,
r.party_id rel_pty_id,
r.relationship_code rel_code
FROM hz_matched_contacts_gt gc,
hz_org_contacts c,
hz_relationships r,
hz_parties p
WHERE gc.search_context_id = i_ctx_id
AND gc.party_id = i_party_id
AND gc.org_contact_id = c.org_contact_id
AND NVL(gc.score,0) >= 0
AND c.party_site_id = i_party_site_id
AND c.party_relationship_id = r.relationship_id
AND r.directional_flag = 'B'
AND r.object_id = p.party_id;
SELECT ctpt.contact_point_type,
ctpt.contact_point_id,
gcpt.score,
decode(ctpt.contact_point_type, 'EMAIL', ctpt.email_address,
'PHONE', DECODE(ctpt.phone_area_code,NULL,NULL,ctpt.phone_area_code||'-')||ctpt.phone_number,
'TELEX', ctpt.telex_number,
'WEB', ctpt.url ) ad
FROM hz_matched_cpts_gt gcpt,
hz_contact_points ctpt
WHERE gcpt.search_context_id = i_ctx_id
AND gcpt.party_id = i_party_id
AND NVL(gcpt.score,0) >= 0
AND ctpt.owner_table_name = 'HZ_PARTIES'
AND ctpt.owner_table_id = i_rel_pty_id
AND gcpt.contact_point_id = ctpt.contact_point_id;
SELECT ctpt.contact_point_type,
ctpt.contact_point_id,
gcpt.score,
decode(ctpt.contact_point_type, 'EMAIL', ctpt.email_address,
'PHONE', DECODE(ctpt.phone_area_code,NULL,NULL,ctpt.phone_area_code||'-')||ctpt.phone_number,
'TLX' , ctpt.telex_number,
'WEB' , ctpt.url ) ad
FROM hz_matched_cpts_gt gcpt,
hz_contact_points ctpt
WHERE gcpt.search_context_id = i_ctx_id
AND gcpt.party_id = i_party_id
AND NVL(gcpt.score,0) >= 0
AND ctpt.owner_table_name = 'HZ_PARTIES'
AND ctpt.owner_table_id = i_party_id
AND gcpt.contact_point_id = ctpt.contact_point_id;
SELECT ctpt.contact_point_type,
ctpt.contact_point_id,
gcpt.score,
decode(ctpt.contact_point_type, 'EMAIL', ctpt.email_address,
'PHONE', DECODE(ctpt.phone_area_code,NULL,NULL,ctpt.phone_area_code||'-')||ctpt.phone_number,
'TELEX', ctpt.telex_number,
'WEB', ctpt.url ) ad
FROM hz_matched_cpts_gt gcpt,
hz_contact_points ctpt
WHERE gcpt.search_context_id = i_ctx_id
AND gcpt.party_id = i_party_id
AND NVL(gcpt.score,0) >= 0
AND ctpt.owner_table_name = 'HZ_PARTY_SITES'
AND ctpt.owner_table_id = i_pty_site_id
AND gcpt.contact_point_id = ctpt.contact_point_id;
SELECT distinct p.party_name,
p.party_number,
p.party_id,
s.score
FROM hz_matched_parties_gt s,
hz_parties p
/*hz_matched_parties_gt ca1 Bug2678267 Commented to avoid cartesian join pblm */
WHERE s.party_id = p.party_id
AND s.search_context_id = i_ctx_id
AND NVL(s.score,0) >= 0
-- AND DECODE(i_status, 'ALL', 'ALL', NVL(p.status,'A')) = i_status
ORDER BY s.score desc;
SELECT distinct p.party_name,
p.party_number,
p.party_id,
s.score
FROM hz_matched_parties_gt s,
hz_parties p,
hz_matched_parties_gt ca1,
hz_cust_accounts ca2
WHERE s.party_id = p.party_id
AND s.search_context_id = i_ctx_id
AND NVL(s.score,0) >= 0
-- AND DECODE(i_status, 'ALL', 'ALL', NVL(p.status,'A')) = i_status
AND s.party_id = ca2.party_id
AND ca1.score < 0
AND ca1.search_context_id = i_ctx_id
AND ca2.cust_account_id = -ca1.party_id
AND DECODE(i_status, 'ALL', 'ALL', NVL(ca2.status,'A')) = i_status
ORDER BY s.score desc;
SELECT distinct a.cust_account_id,
a.account_number,
a.customer_class_code,
a.account_name,
p.party_name,
p.party_number,
p.party_id,
s.score
FROM hz_cust_accounts a,
hz_matched_parties_gt s,
hz_parties p
WHERE a.party_id = i_party_id
AND a.cust_account_id = -s.party_id
AND s.score < 0
AND s.search_context_id = i_ctx_id
AND a.party_id = p.party_id
AND DECODE(i_status, 'ALL', 'ALL', NVL(a.status,'A')) = i_status
ORDER BY s.score asc;
SELECT distinct gs.score,
ps.party_id,
ps.party_site_id,
a.cust_account_id,
a.cust_acct_site_id,
l.address1,
l.city,
l.state,
l.postal_code,
ps.identifying_address_flag
FROM hz_matched_party_sites_gt gs,
hz_cust_acct_sites_all a,
hz_party_sites ps,
hz_locations l
WHERE gs.search_context_id = p_ctx_id
AND gs.score < 0
AND -gs.party_id = p_cust_acct_id
AND -gs.party_site_id = a.cust_acct_site_id
AND a.party_site_id = ps.party_site_id
AND ps.location_id = l.location_id
AND DECODE(l_active_site, 'Y', 'A', NVL(a.status,'A')) = NVL(a.status,'A');
SELECT distinct gs.score,
ps.party_id,
ps.party_site_id,
a.cust_account_id,
a.cust_acct_site_id,
l.address1,
l.city,
l.state,
l.postal_code,
ps.identifying_address_flag
FROM hz_matched_party_sites_gt gs,
hz_cust_acct_sites a,
hz_party_sites ps,
hz_locations l
WHERE gs.search_context_id = p_ctx_id
AND gs.score < 0
AND -gs.party_id = p_cust_acct_id
AND -gs.party_site_id = a.cust_acct_site_id
AND a.party_site_id = ps.party_site_id
AND ps.location_id = l.location_id
AND DECODE(l_active_site, 'Y', 'A', NVL(a.status,'A')) = NVL(a.status,'A');
select distinct a.score,
r.party_id rel_pty_id,
r.relationship_code rel_code,
ro.cust_account_role_id,
p.party_id person_id,
p.party_name,
p.party_number,
o.org_contact_id
from hz_matched_contacts_gt a,
hz_relationships r,
hz_parties p,
hz_cust_account_roles ro,
hz_org_contacts o
where a.search_context_id = i_ctx_id
and a.score < 0
and -a.party_id = i_cust_acct_id
and -a.org_contact_id = ro.cust_account_role_id
and ro.party_id = r.party_id
and r.relationship_id = o.party_relationship_id
and r.directional_flag = 'B'
and r.object_id = p.party_id
and ro.cust_acct_site_id IS NULL;
SELECT distinct gc.score ,
p.party_name ,
p.party_number ,
p.party_id person_id,
ro.cust_account_role_id ,
r.party_id rel_pty_id,
r.relationship_code rel_code ,
o.org_contact_id
FROM hz_matched_contacts_gt gc,
hz_relationships r,
hz_parties p,
hz_cust_account_roles ro,
hz_cust_acct_sites asite,
hz_org_contacts o
WHERE gc.search_context_id = i_ctx_id
AND gc.score < 0
AND -gc.party_id = i_cust_acct_id
AND -gc.org_contact_id = ro.cust_account_role_id
AND ro.cust_acct_site_id = i_acct_site_id
AND ro.party_id = r.party_id
AND r.directional_flag = 'B'
AND r.object_id = p.party_id
AND asite.cust_acct_site_id = ro.cust_acct_site_id
AND asite.party_site_id = o.party_site_id
AND o.party_relationship_id = r.relationship_id;
SELECT distinct gc.score ,
p.party_name ,
p.party_number ,
p.party_id person_id,
ro.cust_account_role_id ,
r.party_id rel_pty_id,
r.relationship_code rel_code ,
o.org_contact_id
FROM hz_matched_contacts_gt gc,
hz_relationships r,
hz_parties p,
hz_cust_account_roles ro,
hz_cust_acct_sites_all asite,
hz_org_contacts o
WHERE gc.search_context_id = i_ctx_id
AND gc.score < 0
AND -gc.party_id = i_cust_acct_id
AND -gc.org_contact_id = ro.cust_account_role_id
AND ro.cust_acct_site_id = i_acct_site_id
AND ro.party_id = r.party_id
AND r.directional_flag = 'B'
AND r.object_id = p.party_id
AND asite.cust_acct_site_id = ro.cust_acct_site_id
AND asite.party_site_id = o.party_site_id
AND o.party_relationship_id = r.relationship_id;