[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT NVL(TO_NUMBER(DECODE(l_orgId1,' ',NULL,l_orgId2)),-99) orgId from dual;
SELECT DECODE(site_use_type, c_site_use_type, 1, 2) first_orderby,
DECODE(primary_per_type,'Y', 1, 2) second_orderby,
party_site_id,
site_use_type,
primary_per_type
FROM (
SELECT party_site_id, ps.site_use_type, ps.primary_per_type
FROM hz_party_sites_v ps, hr_organization_information hr
WHERE ps.party_id = c_party_id
AND ps.status = 'A'
AND nvl(ps.end_date_active, sysdate + 1) > sysdate
AND hr.organization_id = c_org_id
AND hr.org_information_context = c_hr_type
AND hr.org_information1 = ps.country
UNION
SELECT party_site_id, ps.site_use_type, ps.primary_per_type
FROM hz_party_sites_v ps
WHERE ps.party_id = c_party_id
AND ps.status = 'A'
AND nvl(ps.end_date_active,sysdate + 1) > sysdate
AND NOT EXISTS (
SELECT 1
FROM hr_organization_information hr
WHERE hr.organization_id = c_org_id
AND hr.org_information_context = c_hr_type
AND rownum = 1
)
) o
WHERE ( NOT EXISTS (SELECT 1 FROM hz_party_site_uses psu
WHERE psu.party_site_id = o.party_site_id
AND psu.site_use_type = o.site_use_type
) OR
EXISTS (SELECT 1 FROM hz_party_site_uses psu
WHERE psu.party_site_id = o.party_site_id
AND psu.site_use_type = o.site_use_type
AND psu.status = 'A')
)
AND ( NOT EXISTS (SELECT 1 FROM hz_cust_acct_sites cas
WHERE cas.party_site_id = o.party_site_id
) OR
(EXISTS (SELECT 1 FROM hz_cust_acct_sites cas
where cas.party_site_id = o.party_site_id
and cas.status = 'A')
AND
( NOT EXISTS (SELECT 1 FROM hz_cust_acct_sites cas, hz_cust_site_uses_all csu
WHERE csu.cust_acct_site_id = cas.cust_acct_site_id
AND cas.party_site_id = o.party_site_id
AND cas.org_id = csu.org_id
AND csu.site_use_code = o.site_use_type)
OR
EXISTS ( SELECT 1
FROM hz_cust_acct_sites_all cas, hz_cust_site_uses_all csu
WHERE cas.party_site_id = o.party_site_id
AND cas.org_id = csu.org_id
AND csu.cust_acct_site_id = cas.cust_acct_site_id
AND csu.status = 'A'
AND csu.site_use_code= o.site_use_type
)
)
)
)
ORDER BY 1,2;
SELECT responsibility_id
FROM hz_role_responsibility
WHERE cust_account_role_id = role_id
AND responsibility_type = role_type;
SELECT cust_acct_site_id
FROM hz_cust_acct_sites
WHERE cust_account_id = account_id
AND party_site_id = site_id;
SELECT site_use_id
FROM hz_cust_site_uses
WHERE cust_acct_site_id = acct_site_id
AND site_use_code = Site_type;
SELECT party_site_use_id
FROM hz_party_site_uses
WHERE party_site_id = l_party_site_id
AND site_use_type = Site_type;
Select hzl.city
From hz_locations hzl,hz_party_sites hps
Where hps.party_site_id = p_party_site_id
And hzl.location_id = hps.location_id;
select cust_acct_site_id,status
from hz_cust_acct_sites
where cust_account_id = c_cust_acct_id
AND party_site_id = c_party_site_id;
select * from
( select site_use_id, status
from hz_cust_site_uses
where cust_acct_site_id = c_acct_site_id and site_use_code = c_acct_site_type
order by status)
where rownum <2 ;
select hca.cust_acct_site_id, hca.cust_account_id,hps.party_id
from hz_cust_acct_sites hca,hz_cust_site_uses hcu,hz_party_sites hps
where
hcu.site_use_id = lin_custacct_siteuse_id
and hcu.site_use_code = lin_siteuse_type
and hcu.cust_acct_site_id = hca.cust_acct_site_id
and hca.party_site_id = hps.party_site_id;
select a.cust_account_role_id, a.status
from hz_cust_account_roles a
--, hz_cust_acct_sites_all c
where
a.role_type = 'CONTACT'
and a.party_id = lin_party_id
and a.cust_account_id = lin_custacct_id
and a.cust_acct_site_id = lin_custacct_site_id;
select hca.cust_account_id, hca.party_site_id, hcs.status
from hz_cust_site_uses hcs,hz_cust_acct_sites hca
where hcs.site_use_id = lc_inv_to_org_id and
hcs.cust_acct_site_id = hca.cust_acct_site_id;
select a.party_id, a.party_name,a.party_type
from hz_parties a, hz_cust_accounts b
where b.party_id = a.party_id
and b.cust_account_id = lc_cust_acct_id;
Select Party_id
from HZ_CUST_ACCOUNT_ROLES
where cust_account_role_id = lc_inv_to_cntct_id;
Select party_name from HZ_PARTIES
where party_type = 'PERSON' and party_id = lc_cntct_partyid
union
select party_name from HZ_PARTIES
where party_id =
(select subject_id from HZ_RELATIONSHIPS
where party_id = lc_cntct_partyid and
subject_type = 'PERSON' and object_type = 'ORGANIZATION');
select contact_point_type, primary_flag,phone_line_type,
IBE_UTIL.format_phone(phone_country_code,phone_area_code,phone_number,phone_extension)phone_number,
email_address
from hz_contact_points
where contact_point_type in ('PHONE','EMAIL') and
NVL(status, 'A') = 'A' and owner_table_name = 'HZ_PARTIES'
and owner_table_id = lc_cntct_prtyId
and primary_flag='Y';