Product: | AMS - Marketing |
---|---|
Description: | This view returns the team ( access ) information associated to marketing activity.. |
Implementation/DBA Data: | APPS.AMS_CAMPAIGN_TEAM_V |
SELECT A.ACTIVITY_ACCESS_ID
, B.FULL_NAME
, NVL(A.ADMIN_FLAG
, 'N')
, A.ACTIVE_FROM_DATE
, B.PHONE
, B.EMAIL
, A.OBJECT_VERSION_NUMBER
, A.ACT_ACCESS_TO_OBJECT_ID
, A.ARC_ACT_ACCESS_TO_OBJECT
, A.USER_OR_ROLE_ID
, A.ARC_USER_OR_ROLE_TYPE
, A.CREATION_DATE
, D. MEANING
, A.DELETE_FLAG
, A.OWNER_FLAG
FROM AMS_ACT_ACCESS A
, AMS_JTF_RS_EMP_V B
, AMS_LOOKUPS D
WHERE A.USER_OR_ROLE_ID = B.RESOURCE_ID
AND A.ARC_USER_OR_ROLE_TYPE = 'USER'
AND D.LOOKUP_TYPE = 'AMS_ACCESS_TYPE'
AND D.LOOKUP_CODE = A.ARC_USER_OR_ROLE_TYPE UNION ALL SELECT A.ACTIVITY_ACCESS_ID
, C.GROUP_NAME FULL_NAME
, NVL(A.ADMIN_FLAG
, 'N')
, A.ACTIVE_FROM_DATE
, ''
, C.EMAIL_ADDRESS
, A.OBJECT_VERSION_NUMBER
, A.ACT_ACCESS_TO_OBJECT_ID
, A.ARC_ACT_ACCESS_TO_OBJECT
, A.USER_OR_ROLE_ID
, A.ARC_USER_OR_ROLE_TYPE
, A.CREATION_DATE
, D.MEANING
, A.DELETE_FLAG
, A.OWNER_FLAG
FROM AMS_ACT_ACCESS A
, JTF_RS_GROUPS_VL C
, AMS_LOOKUPS D
WHERE A.ARC_USER_OR_ROLE_TYPE = 'GROUP'
AND A.USER_OR_ROLE_ID = C.GROUP_ID
AND D.LOOKUP_TYPE = 'AMS_ACCESS_TYPE'
AND D.LOOKUP_CODE = A.ARC_USER_OR_ROLE_TYPE