In OTBI, what query can I use to report on Oracle Sales and Service contacts exclusively?
In Oracle Transactional Business Intelligence (OTBI), you can use the following query to create a report that lists contacts that are available in Oracle Sales and Oracle Service.
select
hzp.PARTY_ID,
ppf.PERSON_PROFILE_ID
from
fusion.HZ_PARTIES hzp,
fusion.HZ_PERSON_PROFILES ppf
where
hzp.PARTY_ID = ppf.PARTY_ID
and hzp.STATUS = 'A'
and nvl(hzp.INTERNAL_FLAG,'N') = 'N'
and hzp.PARTY_ID in
(
select
usg.PARTY_ID
from
fusion.HZ_PARTY_USG_ASSIGNMENTS usg
where
hzp.PARTY_ID = usg.PARTY_ID
and usg.STATUS_FLAG = 'A'
and trunc(sysdate) between usg.EFFECTIVE_START_DATE and usg.EFFECTIVE_END_DATE
and usg.PARTY_USAGE_CODE in ('CONTACT') )
Note: Contacts in the report aren't exclusive to Sales if they're also used in other
products.