EGO_CHANGE_AUDIT_AUTHOR

Details

Schema: FUSION
Object owner: EGO
Object type: VIEW

Columns

Name
USER_ID USER_GUID USERNAME DISPLAY_PERSON_NAME DISPLAY_EMAIL_ADDRESS PERSON_ID PARTY_ID

Name

USER_ID

USER_GUID

USERNAME

DISPLAY_PERSON_NAME

DISPLAY_EMAIL_ADDRESS

PERSON_ID

PARTY_ID

Query

SQL_Statement
select USER_ID, USER_GUID, USERNAME, DISPLAY_PERSON_NAME, DISPLAY_EMAIL_ADDRESS, PERSON_ID, PARTY_ID from (select UserPEO.USER_ID as USER_ID, UserPEO.USER_GUID as USER_GUID, UserPEO.USERNAME as USERNAME, PersonNameDPEO.display_name AS DISPLAY_PERSON_NAME, EmailAddressPEO.email_address AS DISPLAY_EMAIL_ADDRESS, UserPEO.PERSON_ID as PERSON_ID, UserPEO.PARTY_ID as PARTY_ID FROM PER_USERS UserPEO, PER_PERSON_NAMES_F_V PersonNameDPEO, PER_EMAIL_ADDRESSES EmailAddressPEO, per_all_people_f per WHERE UserPEO.person_id = per.person_id and per.primary_email_id=EmailAddressPEO.email_address_id(+) and UserPEO.person_id = PersonNameDPEO.person_id(+) union all select UserPEO.USER_ID as USER_ID, UserPEO.USER_GUID as USER_GUID, UserPEO.USERNAME as USERNAME, hzp.party_name AS DISPLAY_PERSON_NAME, hzp.email_address AS DISPLAY_EMAIL_ADDRESS, UserPEO.PERSON_ID as PERSON_ID, UserPEO.PARTY_ID as PARTY_ID FROM PER_USERS UserPEO, HZ_PARTIES hzp WHERE UserPEO.party_id=hzp.party_id and UserPEO.person_id is null union all select UserPEO.USER_ID as USER_ID, UserPEO.USER_GUID as USER_GUID, UserPEO.USERNAME as USERNAME, UserPEO.username AS DISPLAY_PERSON_NAME, UserPEO.username AS DISPLAY_EMAIL_ADDRESS, UserPEO.PERSON_ID as PERSON_ID, UserPEO.PARTY_ID as PARTY_ID FROM PER_USERS UserPEO WHERE UserPEO.person_id is null and UserPEO.party_id is null union all SELECT -1 AS USER_ID, MIG_DATA.ATTRIBUTE_VALUE AS USER_GUID, MIG_DATA.ATTRIBUTE_VALUE AS USERNAME, MIG_DATA.ATTRIBUTE_VALUE AS DISPLAY_PERSON_NAME, MIG_DATA.ATTRIBUTE_VALUE AS DISPLAY_EMAIL_ADDRESS, -1 AS PERSON_ID, null AS PARTY_ID FROM EGO_AUDIT_MIG_ATTR_DATA MIG_DATA where MIG_DATA.ATTRIBUTE_NAME='AUTHOR_NAME_VALUE' union all SELECT -1 as USER_ID, 'SYSTEM' as USER_GUID, meaning as USERNAME, meaning AS DISPLAY_PERSON_NAME, meaning AS DISPLAY_EMAIL_ADDRESS, -1 as PERSON_ID, null as PARTY_ID FROM fnd_lookup_values_vl WHERE lookup_type = 'ORA_EGO_CHG_AUD_OTHERS' AND lookup_code = 'ORA_EGO_CHG_AUD_OTH_SYSTEM') where 1=1

SQL_Statement

select USER_ID, USER_GUID, USERNAME,

DISPLAY_PERSON_NAME,

DISPLAY_EMAIL_ADDRESS,

PERSON_ID,

PARTY_ID from

(select UserPEO.USER_ID as USER_ID,

UserPEO.USER_GUID as USER_GUID,

UserPEO.USERNAME as USERNAME,

PersonNameDPEO.display_name AS DISPLAY_PERSON_NAME,

EmailAddressPEO.email_address AS DISPLAY_EMAIL_ADDRESS,

UserPEO.PERSON_ID as PERSON_ID,

UserPEO.PARTY_ID as PARTY_ID

FROM PER_USERS UserPEO, PER_PERSON_NAMES_F_V PersonNameDPEO,

PER_EMAIL_ADDRESSES EmailAddressPEO, per_all_people_f per

WHERE UserPEO.person_id = per.person_id

and per.primary_email_id=EmailAddressPEO.email_address_id(+)

and UserPEO.person_id = PersonNameDPEO.person_id(+)

union all

select UserPEO.USER_ID as USER_ID,

UserPEO.USER_GUID as USER_GUID,

UserPEO.USERNAME as USERNAME,

hzp.party_name AS DISPLAY_PERSON_NAME,

hzp.email_address AS DISPLAY_EMAIL_ADDRESS,

UserPEO.PERSON_ID as PERSON_ID,

UserPEO.PARTY_ID as PARTY_ID

FROM PER_USERS UserPEO, HZ_PARTIES hzp

WHERE UserPEO.party_id=hzp.party_id

and UserPEO.person_id is null

union all

select UserPEO.USER_ID as USER_ID,

UserPEO.USER_GUID as USER_GUID,

UserPEO.USERNAME as USERNAME,

UserPEO.username AS DISPLAY_PERSON_NAME,

UserPEO.username AS DISPLAY_EMAIL_ADDRESS,

UserPEO.PERSON_ID as PERSON_ID,

UserPEO.PARTY_ID as PARTY_ID

FROM PER_USERS UserPEO

WHERE UserPEO.person_id is null

and UserPEO.party_id is null

union all

SELECT -1 AS USER_ID, MIG_DATA.ATTRIBUTE_VALUE AS USER_GUID,

MIG_DATA.ATTRIBUTE_VALUE AS USERNAME, MIG_DATA.ATTRIBUTE_VALUE AS DISPLAY_PERSON_NAME,

MIG_DATA.ATTRIBUTE_VALUE AS DISPLAY_EMAIL_ADDRESS, -1 AS PERSON_ID,

null AS PARTY_ID FROM EGO_AUDIT_MIG_ATTR_DATA MIG_DATA

where MIG_DATA.ATTRIBUTE_NAME='AUTHOR_NAME_VALUE'

union all

SELECT -1 as USER_ID,

'SYSTEM' as USER_GUID,

meaning as USERNAME,

meaning AS DISPLAY_PERSON_NAME,

meaning AS DISPLAY_EMAIL_ADDRESS,

-1 as PERSON_ID,

null as PARTY_ID

FROM fnd_lookup_values_vl WHERE lookup_type = 'ORA_EGO_CHG_AUD_OTHERS' AND lookup_code = 'ORA_EGO_CHG_AUD_OTH_SYSTEM')

where 1=1