VIEW: APPS.IEM_QUEUEEMAIL_CHERRYPICK_V

Source

View Text - Preformatted

SELECT ' ' IEU_OBJECT_FUNCTION, ' ' IEU_OBJECT_PARAMETERS, 'D2325ED0421D11D484CD00C04F53F265' IEU_MEDIA_TYPE_UUID, 'MESSAGE_ID' ieu_param_pk_col, to_char(message_id) ieu_param_pk_value, decode(a.mail_item_status, 'R', (SELECT meaning FROM fnd_lookups WHERE lookup_type = 'IEM_MESSAGE_STATUS_TYPE' AND lookup_code = 'R'), 'S', (SELECT meaning FROM fnd_lookups WHERE lookup_type = 'IEM_MESSAGE_STATUS_TYPE' AND lookup_code = 'R'), (SELECT meaning FROM fnd_lookups WHERE lookup_type = 'IEM_MESSAGE_STATUS_TYPE' AND lookup_code = 'N') ) status, b.from_name account, c.name classification, a.from_address sender, subject, to_date(iem_utility_pvt.gettimezone(to_date(SUBSTR(a.sent_date, 1, 20), 'DD-MON-YYYY HH24:MI:SS'), a.resource_id), fnd_profile.value_specific('ICX_DATE_FORMAT_MASK', (SELECT user_id FROM jtf_rs_resource_extns rs WHERE resource_id = a.resource_id)) || ' HH24:MI:SS') sent_date, decode(a.message_flag, NULL, NULL, (SELECT meaning FROM fnd_lookups WHERE lookup_type = 'IEM_MESSAGE_PRIORITY_TYPE' AND lookup_code = a.message_flag)) priority, decode(a.mail_item_status, 'R', d.meaning, 'S', d.meaning, nvl( (SELECT DISTINCT meaning || '(' || f.source_last_name || ',' || f.source_first_name || ')' FROM fnd_lookups, iem_agents e, jtf_rs_resource_extns f WHERE lookup_code = SUBSTR(a.mail_item_status, 1, 1) AND lookup_type = 'IEM_MESSAGE_STATUS_TYPE' AND e.resource_id = a.from_resource_id AND e.resource_id = f.resource_id), d.meaning)) description, a.email_account_id, e.resource_id resource_id, 'RS_INDIVIDUAL' resource_type, 'B9539E58E3EA11d3AEB400C04F601AB2' ieu_action_object_code FROM iem_rt_proc_emails a, iem_mstemail_accounts b, iem_route_classifications c, fnd_lookups d, iem_agents e WHERE a.resource_id=0 and ((a.group_id,e.resource_id) in (select group_id,resource_id from jtf_rs_group_members_vl where delete_flag<>'Y') OR a.group_id=0) AND a.email_account_id = b.email_account_id AND a.message_id not in ( SELECT message_id FROM iem_reroute_hists WHERE agent_id=e.resource_id ) and b.email_account_id = e.email_account_id AND a.rt_classification_id = c.route_classification_id AND SUBSTR(a.mail_item_status, 1, 1) = d.lookup_code AND d.lookup_type = 'IEM_MESSAGE_STATUS_TYPE' ORDER BY status, account, classification

View Text - HTML Formatted

SELECT ' ' IEU_OBJECT_FUNCTION
, ' ' IEU_OBJECT_PARAMETERS
, 'D2325ED0421D11D484CD00C04F53F265' IEU_MEDIA_TYPE_UUID
, 'MESSAGE_ID' IEU_PARAM_PK_COL
, TO_CHAR(MESSAGE_ID) IEU_PARAM_PK_VALUE
, DECODE(A.MAIL_ITEM_STATUS
, 'R'
, (SELECT MEANING
FROM FND_LOOKUPS
WHERE LOOKUP_TYPE = 'IEM_MESSAGE_STATUS_TYPE'
AND LOOKUP_CODE = 'R')
, 'S'
, (SELECT MEANING
FROM FND_LOOKUPS
WHERE LOOKUP_TYPE = 'IEM_MESSAGE_STATUS_TYPE'
AND LOOKUP_CODE = 'R')
, (SELECT MEANING
FROM FND_LOOKUPS
WHERE LOOKUP_TYPE = 'IEM_MESSAGE_STATUS_TYPE'
AND LOOKUP_CODE = 'N') ) STATUS
, B.FROM_NAME ACCOUNT
, C.NAME CLASSIFICATION
, A.FROM_ADDRESS SENDER
, SUBJECT
, TO_DATE(IEM_UTILITY_PVT.GETTIMEZONE(TO_DATE(SUBSTR(A.SENT_DATE
, 1
, 20)
, 'DD-MON-YYYY HH24:MI:SS')
, A.RESOURCE_ID)
, FND_PROFILE.VALUE_SPECIFIC('ICX_DATE_FORMAT_MASK'
, (SELECT USER_ID
FROM JTF_RS_RESOURCE_EXTNS RS
WHERE RESOURCE_ID = A.RESOURCE_ID)) || ' HH24:MI:SS') SENT_DATE
, DECODE(A.MESSAGE_FLAG
, NULL
, NULL
, (SELECT MEANING
FROM FND_LOOKUPS
WHERE LOOKUP_TYPE = 'IEM_MESSAGE_PRIORITY_TYPE'
AND LOOKUP_CODE = A.MESSAGE_FLAG)) PRIORITY
, DECODE(A.MAIL_ITEM_STATUS
, 'R'
, D.MEANING
, 'S'
, D.MEANING
, NVL( (SELECT DISTINCT MEANING || '(' || F.SOURCE_LAST_NAME || '
, ' || F.SOURCE_FIRST_NAME || ')'
FROM FND_LOOKUPS
, IEM_AGENTS E
, JTF_RS_RESOURCE_EXTNS F
WHERE LOOKUP_CODE = SUBSTR(A.MAIL_ITEM_STATUS
, 1
, 1)
AND LOOKUP_TYPE = 'IEM_MESSAGE_STATUS_TYPE'
AND E.RESOURCE_ID = A.FROM_RESOURCE_ID
AND E.RESOURCE_ID = F.RESOURCE_ID)
, D.MEANING)) DESCRIPTION
, A.EMAIL_ACCOUNT_ID
, E.RESOURCE_ID RESOURCE_ID
, 'RS_INDIVIDUAL' RESOURCE_TYPE
, 'B9539E58E3EA11D3AEB400C04F601AB2' IEU_ACTION_OBJECT_CODE
FROM IEM_RT_PROC_EMAILS A
, IEM_MSTEMAIL_ACCOUNTS B
, IEM_ROUTE_CLASSIFICATIONS C
, FND_LOOKUPS D
, IEM_AGENTS E
WHERE A.RESOURCE_ID=0
AND ((A.GROUP_ID
, E.RESOURCE_ID) IN (SELECT GROUP_ID
, RESOURCE_ID
FROM JTF_RS_GROUP_MEMBERS_VL
WHERE DELETE_FLAG<>'Y') OR A.GROUP_ID=0)
AND A.EMAIL_ACCOUNT_ID = B.EMAIL_ACCOUNT_ID
AND A.MESSAGE_ID NOT IN ( SELECT MESSAGE_ID
FROM IEM_REROUTE_HISTS
WHERE AGENT_ID=E.RESOURCE_ID )
AND B.EMAIL_ACCOUNT_ID = E.EMAIL_ACCOUNT_ID
AND A.RT_CLASSIFICATION_ID = C.ROUTE_CLASSIFICATION_ID
AND SUBSTR(A.MAIL_ITEM_STATUS
, 1
, 1) = D.LOOKUP_CODE
AND D.LOOKUP_TYPE = 'IEM_MESSAGE_STATUS_TYPE' ORDER BY STATUS
, ACCOUNT
, CLASSIFICATION