VIEW: APPS.AST_INTERESTS_MV

Source

View Text - Preformatted

SELECT int_mb.interest_id , int_mb.last_update_date , int_mb.last_updated_by , int_mb.creation_date , int_mb.created_by , int_mb.last_update_login , int_mb.request_id , int_mb.program_application_id , int_mb.program_id , int_mb.program_update_date , int_mb.interest_use_code , int_mb.interest_type_id , int_mb.contact_id , int_mb.customer_id , int_mb.address_id , int_mb.primary_interest_code_id , int_mb.secondary_interest_code_id , int_mb.status_code , int_mb.description , inttype.interest_type , intcode1.code primary_interest_code, intcode2.code secondary_interest_code, lkpcode1.meaning status, int_mb.attribute_category , int_mb.attribute1 , int_mb.attribute2 , int_mb.attribute3 , int_mb.attribute4 , int_mb.attribute5 , int_mb.attribute6 , int_mb.attribute7 , int_mb.attribute8 , int_mb.attribute9 , int_mb.attribute10 , int_mb.attribute11 , int_mb.attribute12 , int_mb.attribute13 , int_mb.attribute14 , int_mb.attribute15, int_mb.org_id, int_mb.language_code lang FROM (SELECT interest.interest_id , interest.last_update_date , interest.last_updated_by , interest.creation_date , interest.created_by , interest.last_update_login , interest.request_id , interest.program_application_id , interest.program_id , interest.program_update_date , interest.interest_use_code , interest.interest_type_id , interest.contact_id , interest.customer_id , interest.address_id , interest.primary_interest_code_id , interest.secondary_interest_code_id , interest.status_code , interest.description , interest.attribute_category , interest.attribute1 , interest.attribute2 , interest.attribute3 , interest.attribute4 , interest.attribute5 , interest.attribute6 , interest.attribute7 , interest.attribute8 , interest.attribute9 , interest.attribute10 , interest.attribute11 , interest.attribute12 , interest.attribute13 , interest.attribute14 , interest.attribute15 , interest.org_id, fndlang.language_code FROM AS_INTERESTS_ALL INTEREST, fnd_languages fndlang WHERE fndlang.installed_flag in ('I', 'B') AND interest.LAST_UPDATE_DATE >= SYSDATE-NVL((select profile_option_value from fnd_profile_option_values where level_id = 10001 and (profile_option_id, application_id) = (select profile_option_id, application_id from fnd_profile_options where profile_option_name = 'AST_MV_NUM_OF_MONTH_DATA')),3)*30 ) int_mb, FND_LOOKUP_VALUES LKPCODE1, AS_INTEREST_CODES_tl INTCODE1, AS_INTEREST_CODES_tl INTCODE2, AS_INTEREST_TYPES_tl INTTYPE where intcode1.interest_code_id(+) = int_mb.primary_interest_code_id and intcode1.language(+) = int_mb.language_code and intcode2.interest_code_id(+) = int_mb.secondary_interest_code_id and intcode2.language(+) = int_mb.language_code and int_mb.status_code = lkpcode1.lookup_code(+) and lkpcode1.lookup_type(+) = 'INTEREST_STATUS' AND lkpcode1.LANGUAGE(+) = int_mb.language_code AND lkpcode1.VIEW_APPLICATION_ID(+) = 279 AND nvl(lkpcode1.SECURITY_GROUP_ID, 0) = nvl((select nvl(max(lt.security_group_id), 0) from fnd_lookup_types lt where lt.view_application_id = 279 and lt.lookup_type = 'INTEREST_STATUS' and lt.security_group_id in (0,to_number(decode(substrb(userenv('CLIENT_INFO'),55,1),' ', '0',null, '0',substrb(userenv('CLIENT_INFO'),55,10))))), -99) and int_mb.interest_type_id = inttype.interest_type_id and inttype.language = int_mb.language_code

View Text - HTML Formatted

SELECT INT_MB.INTEREST_ID
, INT_MB.LAST_UPDATE_DATE
, INT_MB.LAST_UPDATED_BY
, INT_MB.CREATION_DATE
, INT_MB.CREATED_BY
, INT_MB.LAST_UPDATE_LOGIN
, INT_MB.REQUEST_ID
, INT_MB.PROGRAM_APPLICATION_ID
, INT_MB.PROGRAM_ID
, INT_MB.PROGRAM_UPDATE_DATE
, INT_MB.INTEREST_USE_CODE
, INT_MB.INTEREST_TYPE_ID
, INT_MB.CONTACT_ID
, INT_MB.CUSTOMER_ID
, INT_MB.ADDRESS_ID
, INT_MB.PRIMARY_INTEREST_CODE_ID
, INT_MB.SECONDARY_INTEREST_CODE_ID
, INT_MB.STATUS_CODE
, INT_MB.DESCRIPTION
, INTTYPE.INTEREST_TYPE
, INTCODE1.CODE PRIMARY_INTEREST_CODE
, INTCODE2.CODE SECONDARY_INTEREST_CODE
, LKPCODE1.MEANING STATUS
, INT_MB.ATTRIBUTE_CATEGORY
, INT_MB.ATTRIBUTE1
, INT_MB.ATTRIBUTE2
, INT_MB.ATTRIBUTE3
, INT_MB.ATTRIBUTE4
, INT_MB.ATTRIBUTE5
, INT_MB.ATTRIBUTE6
, INT_MB.ATTRIBUTE7
, INT_MB.ATTRIBUTE8
, INT_MB.ATTRIBUTE9
, INT_MB.ATTRIBUTE10
, INT_MB.ATTRIBUTE11
, INT_MB.ATTRIBUTE12
, INT_MB.ATTRIBUTE13
, INT_MB.ATTRIBUTE14
, INT_MB.ATTRIBUTE15
, INT_MB.ORG_ID
, INT_MB.LANGUAGE_CODE LANG
FROM (SELECT INTEREST.INTEREST_ID
, INTEREST.LAST_UPDATE_DATE
, INTEREST.LAST_UPDATED_BY
, INTEREST.CREATION_DATE
, INTEREST.CREATED_BY
, INTEREST.LAST_UPDATE_LOGIN
, INTEREST.REQUEST_ID
, INTEREST.PROGRAM_APPLICATION_ID
, INTEREST.PROGRAM_ID
, INTEREST.PROGRAM_UPDATE_DATE
, INTEREST.INTEREST_USE_CODE
, INTEREST.INTEREST_TYPE_ID
, INTEREST.CONTACT_ID
, INTEREST.CUSTOMER_ID
, INTEREST.ADDRESS_ID
, INTEREST.PRIMARY_INTEREST_CODE_ID
, INTEREST.SECONDARY_INTEREST_CODE_ID
, INTEREST.STATUS_CODE
, INTEREST.DESCRIPTION
, INTEREST.ATTRIBUTE_CATEGORY
, INTEREST.ATTRIBUTE1
, INTEREST.ATTRIBUTE2
, INTEREST.ATTRIBUTE3
, INTEREST.ATTRIBUTE4
, INTEREST.ATTRIBUTE5
, INTEREST.ATTRIBUTE6
, INTEREST.ATTRIBUTE7
, INTEREST.ATTRIBUTE8
, INTEREST.ATTRIBUTE9
, INTEREST.ATTRIBUTE10
, INTEREST.ATTRIBUTE11
, INTEREST.ATTRIBUTE12
, INTEREST.ATTRIBUTE13
, INTEREST.ATTRIBUTE14
, INTEREST.ATTRIBUTE15
, INTEREST.ORG_ID
, FNDLANG.LANGUAGE_CODE
FROM AS_INTERESTS_ALL INTEREST
, FND_LANGUAGES FNDLANG
WHERE FNDLANG.INSTALLED_FLAG IN ('I'
, 'B')
AND INTEREST.LAST_UPDATE_DATE >= SYSDATE-NVL((SELECT PROFILE_OPTION_VALUE
FROM FND_PROFILE_OPTION_VALUES
WHERE LEVEL_ID = 10001
AND (PROFILE_OPTION_ID
, APPLICATION_ID) = (SELECT PROFILE_OPTION_ID
, APPLICATION_ID
FROM FND_PROFILE_OPTIONS
WHERE PROFILE_OPTION_NAME = 'AST_MV_NUM_OF_MONTH_DATA'))
, 3)*30 ) INT_MB
, FND_LOOKUP_VALUES LKPCODE1
, AS_INTEREST_CODES_TL INTCODE1
, AS_INTEREST_CODES_TL INTCODE2
, AS_INTEREST_TYPES_TL INTTYPE
WHERE INTCODE1.INTEREST_CODE_ID(+) = INT_MB.PRIMARY_INTEREST_CODE_ID
AND INTCODE1.LANGUAGE(+) = INT_MB.LANGUAGE_CODE
AND INTCODE2.INTEREST_CODE_ID(+) = INT_MB.SECONDARY_INTEREST_CODE_ID
AND INTCODE2.LANGUAGE(+) = INT_MB.LANGUAGE_CODE
AND INT_MB.STATUS_CODE = LKPCODE1.LOOKUP_CODE(+)
AND LKPCODE1.LOOKUP_TYPE(+) = 'INTEREST_STATUS'
AND LKPCODE1.LANGUAGE(+) = INT_MB.LANGUAGE_CODE
AND LKPCODE1.VIEW_APPLICATION_ID(+) = 279
AND NVL(LKPCODE1.SECURITY_GROUP_ID
, 0) = NVL((SELECT NVL(MAX(LT.SECURITY_GROUP_ID)
, 0)
FROM FND_LOOKUP_TYPES LT
WHERE LT.VIEW_APPLICATION_ID = 279
AND LT.LOOKUP_TYPE = 'INTEREST_STATUS'
AND LT.SECURITY_GROUP_ID IN (0
, TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO')
, 55
, 1)
, ' '
, '0'
, NULL
, '0'
, SUBSTRB(USERENV('CLIENT_INFO')
, 55
, 10)))))
, -99)
AND INT_MB.INTEREST_TYPE_ID = INTTYPE.INTEREST_TYPE_ID
AND INTTYPE.LANGUAGE = INT_MB.LANGUAGE_CODE