VIEW: APPS.IGS_AD_EXTRACURR_ACT_V

Source

View Text - Preformatted

SELECT PI.ROWID, PI.PERSON_INTEREST_ID, PI.PARTY_ID, PI.INTEREST_TYPE_CODE, FL1.MEANING, PI.COMMENTS, PI.SINCE, HEA.END_DATE, HEA.HOURS_PER_WEEK, HEA.WEEKS_PER_YEAR, PI.LEVEL_OF_INTEREST, DECODE (PI.LEVEL_OF_INTEREST,NULL, NULL, (SELECT MEANING FROM FND_LOOKUP_VALUES WHERE PI.LEVEL_OF_INTEREST = LOOKUP_CODE AND LOOKUP_TYPE = 'LEVEL_OF_INTEREST' AND VIEW_APPLICATION_ID = 222 AND SECURITY_GROUP_ID = 0 AND LANGUAGE = USERENV('LANG'))) RANK, PI.LEVEL_OF_PARTICIPATION, DECODE (PI.LEVEL_OF_PARTICIPATION,NULL, NULL,(SELECT MEANING FROM FND_LOOKUP_VALUES WHERE PI.LEVEL_OF_PARTICIPATION = LOOKUP_CODE AND LOOKUP_TYPE = 'PARTICIPATION_LEVEL' AND VIEW_APPLICATION_ID = 222 AND SECURITY_GROUP_ID = 0 AND LANGUAGE = USERENV('LANG'))) LEVEL_OF_PARTICIPATION_MEANING, PI.SPORT_INDICATOR, PI.SUB_INTEREST_TYPE_CODE, PI.INTEREST_NAME, PI.TEAM, TO_DATE(null) WH_UPDATE_DATE, HEA.ACTIVITY_SOURCE_CD, DECODE (HEA.ACTIVITY_SOURCE_CD,NULL, NULL,(SELECT MEANING FROM IGS_LOOKUP_VALUES WHERE HEA.ACTIVITY_SOURCE_CD = LOOKUP_CODE AND LOOKUP_TYPE = 'ACTIVITY_SOURCE')) ACTIVITY_SOURCE_MEANING, PI.CREATED_BY, PI.CREATION_DATE, PI.LAST_UPDATED_BY, PI.LAST_UPDATE_DATE, PI.LAST_UPDATE_LOGIN, PI.REQUEST_ID, PI.PROGRAM_APPLICATION_ID, PI.PROGRAM_ID, PI.PROGRAM_UPDATE_DATE,PI.OBJECT_VERSION_NUMBER FROM HZ_PERSON_INTEREST PI, IGS_AD_HZ_EXTRACURR_ACT HEA, FND_LOOKUP_VALUES FL1 WHERE PI.PERSON_INTEREST_ID = HEA.PERSON_INTEREST_ID (+) AND PI.INTEREST_TYPE_CODE = FL1.LOOKUP_CODE AND FL1.LOOKUP_TYPE in ('INTEREST_TYPE','ENTERTAINMENT','PREFERENCES') AND FL1.LANGUAGE = USERENV('LANG') AND FL1.VIEW_APPLICATION_ID = 222 AND FL1.SECURITY_GROUP_ID = 0 UNION ALL SELECT PI.ROWID, PI.PERSON_INTEREST_ID, PI.PARTY_ID, PI.INTEREST_TYPE_CODE, null, PI.COMMENTS, PI.SINCE, HEA.END_DATE, HEA.HOURS_PER_WEEK, HEA.WEEKS_PER_YEAR, PI.LEVEL_OF_INTEREST, DECODE (PI.LEVEL_OF_INTEREST, NULL, NULL, (SELECT MEANING FROM FND_LOOKUP_VALUES WHERE PI.LEVEL_OF_INTEREST = LOOKUP_CODE AND LOOKUP_TYPE = 'LEVEL_OF_INTEREST' AND VIEW_APPLICATION_ID = 222 AND SECURITY_GROUP_ID = 0 AND LANGUAGE = USERENV('LANG'))) RANK, PI.LEVEL_OF_PARTICIPATION, DECODE (PI.LEVEL_OF_PARTICIPATION, NULL, NULL, (SELECT MEANING FROM FND_LOOKUP_VALUES WHERE PI.LEVEL_OF_PARTICIPATION = LOOKUP_CODE AND LOOKUP_TYPE = 'PARTICIPATION_LEVEL' AND VIEW_APPLICATION_ID = 222 AND SECURITY_GROUP_ID = 0 AND LANGUAGE = USERENV('LANG'))) LEVEL_OF_PARTICIPATION_MEANING, PI.SPORT_INDICATOR, PI.SUB_INTEREST_TYPE_CODE, PI.INTEREST_NAME, PI.TEAM, TO_DATE(null) WH_UPDATE_DATE, HEA.ACTIVITY_SOURCE_CD, DECODE (HEA.ACTIVITY_SOURCE_CD, NULL, NULL, (SELECT MEANING FROM IGS_LOOKUP_VALUES WHERE HEA.ACTIVITY_SOURCE_CD = LOOKUP_CODE AND LOOKUP_TYPE = 'ACTIVITY_SOURCE')) ACTIVITY_SOURCE_MEANING, PI.CREATED_BY, PI.CREATION_DATE, PI.LAST_UPDATED_BY, PI.LAST_UPDATE_DATE, PI.LAST_UPDATE_LOGIN, PI.REQUEST_ID, PI.PROGRAM_APPLICATION_ID, PI.PROGRAM_ID, PI.PROGRAM_UPDATE_DATE , PI.OBJECT_VERSION_NUMBER FROM HZ_PERSON_INTEREST PI, IGS_AD_HZ_EXTRACURR_ACT HEA WHERE PI.PERSON_INTEREST_ID = HEA.PERSON_INTEREST_ID AND PI.INTEREST_TYPE_CODE IS NULL

View Text - HTML Formatted

SELECT PI.ROWID
, PI.PERSON_INTEREST_ID
, PI.PARTY_ID
, PI.INTEREST_TYPE_CODE
, FL1.MEANING
, PI.COMMENTS
, PI.SINCE
, HEA.END_DATE
, HEA.HOURS_PER_WEEK
, HEA.WEEKS_PER_YEAR
, PI.LEVEL_OF_INTEREST
, DECODE (PI.LEVEL_OF_INTEREST
, NULL
, NULL
, (SELECT MEANING
FROM FND_LOOKUP_VALUES
WHERE PI.LEVEL_OF_INTEREST = LOOKUP_CODE
AND LOOKUP_TYPE = 'LEVEL_OF_INTEREST'
AND VIEW_APPLICATION_ID = 222
AND SECURITY_GROUP_ID = 0
AND LANGUAGE = USERENV('LANG'))) RANK
, PI.LEVEL_OF_PARTICIPATION
, DECODE (PI.LEVEL_OF_PARTICIPATION
, NULL
, NULL
, (SELECT MEANING
FROM FND_LOOKUP_VALUES
WHERE PI.LEVEL_OF_PARTICIPATION = LOOKUP_CODE
AND LOOKUP_TYPE = 'PARTICIPATION_LEVEL'
AND VIEW_APPLICATION_ID = 222
AND SECURITY_GROUP_ID = 0
AND LANGUAGE = USERENV('LANG'))) LEVEL_OF_PARTICIPATION_MEANING
, PI.SPORT_INDICATOR
, PI.SUB_INTEREST_TYPE_CODE
, PI.INTEREST_NAME
, PI.TEAM
, TO_DATE(NULL) WH_UPDATE_DATE
, HEA.ACTIVITY_SOURCE_CD
, DECODE (HEA.ACTIVITY_SOURCE_CD
, NULL
, NULL
, (SELECT MEANING
FROM IGS_LOOKUP_VALUES
WHERE HEA.ACTIVITY_SOURCE_CD = LOOKUP_CODE
AND LOOKUP_TYPE = 'ACTIVITY_SOURCE')) ACTIVITY_SOURCE_MEANING
, PI.CREATED_BY
, PI.CREATION_DATE
, PI.LAST_UPDATED_BY
, PI.LAST_UPDATE_DATE
, PI.LAST_UPDATE_LOGIN
, PI.REQUEST_ID
, PI.PROGRAM_APPLICATION_ID
, PI.PROGRAM_ID
, PI.PROGRAM_UPDATE_DATE
, PI.OBJECT_VERSION_NUMBER
FROM HZ_PERSON_INTEREST PI
, IGS_AD_HZ_EXTRACURR_ACT HEA
, FND_LOOKUP_VALUES FL1
WHERE PI.PERSON_INTEREST_ID = HEA.PERSON_INTEREST_ID (+)
AND PI.INTEREST_TYPE_CODE = FL1.LOOKUP_CODE
AND FL1.LOOKUP_TYPE IN ('INTEREST_TYPE'
, 'ENTERTAINMENT'
, 'PREFERENCES')
AND FL1.LANGUAGE = USERENV('LANG')
AND FL1.VIEW_APPLICATION_ID = 222
AND FL1.SECURITY_GROUP_ID = 0 UNION ALL SELECT PI.ROWID
, PI.PERSON_INTEREST_ID
, PI.PARTY_ID
, PI.INTEREST_TYPE_CODE
, NULL
, PI.COMMENTS
, PI.SINCE
, HEA.END_DATE
, HEA.HOURS_PER_WEEK
, HEA.WEEKS_PER_YEAR
, PI.LEVEL_OF_INTEREST
, DECODE (PI.LEVEL_OF_INTEREST
, NULL
, NULL
, (SELECT MEANING
FROM FND_LOOKUP_VALUES
WHERE PI.LEVEL_OF_INTEREST = LOOKUP_CODE
AND LOOKUP_TYPE = 'LEVEL_OF_INTEREST'
AND VIEW_APPLICATION_ID = 222
AND SECURITY_GROUP_ID = 0
AND LANGUAGE = USERENV('LANG'))) RANK
, PI.LEVEL_OF_PARTICIPATION
, DECODE (PI.LEVEL_OF_PARTICIPATION
, NULL
, NULL
, (SELECT MEANING
FROM FND_LOOKUP_VALUES
WHERE PI.LEVEL_OF_PARTICIPATION = LOOKUP_CODE
AND LOOKUP_TYPE = 'PARTICIPATION_LEVEL'
AND VIEW_APPLICATION_ID = 222
AND SECURITY_GROUP_ID = 0
AND LANGUAGE = USERENV('LANG'))) LEVEL_OF_PARTICIPATION_MEANING
, PI.SPORT_INDICATOR
, PI.SUB_INTEREST_TYPE_CODE
, PI.INTEREST_NAME
, PI.TEAM
, TO_DATE(NULL) WH_UPDATE_DATE
, HEA.ACTIVITY_SOURCE_CD
, DECODE (HEA.ACTIVITY_SOURCE_CD
, NULL
, NULL
, (SELECT MEANING
FROM IGS_LOOKUP_VALUES
WHERE HEA.ACTIVITY_SOURCE_CD = LOOKUP_CODE
AND LOOKUP_TYPE = 'ACTIVITY_SOURCE')) ACTIVITY_SOURCE_MEANING
, PI.CREATED_BY
, PI.CREATION_DATE
, PI.LAST_UPDATED_BY
, PI.LAST_UPDATE_DATE
, PI.LAST_UPDATE_LOGIN
, PI.REQUEST_ID
, PI.PROGRAM_APPLICATION_ID
, PI.PROGRAM_ID
, PI.PROGRAM_UPDATE_DATE
, PI.OBJECT_VERSION_NUMBER
FROM HZ_PERSON_INTEREST PI
, IGS_AD_HZ_EXTRACURR_ACT HEA
WHERE PI.PERSON_INTEREST_ID = HEA.PERSON_INTEREST_ID
AND PI.INTEREST_TYPE_CODE IS NULL