VIEW: APPS.IEM_ACQEMAIL_DTL_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, a.resource_id RESOURCE_ID, 'RS_INDIVIDUAL' RESOURCE_TYPE, '27A096D0422111D484CD00C04F53F265' IEU_ACTION_OBJECT_CODE from iem_rt_proc_emails a,iem_mstemail_accounts b,iem_route_classifications c,fnd_lookups d where a.resource_id <> 0 and a.email_account_id=b.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' and queue_status is null 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
, A.RESOURCE_ID RESOURCE_ID
, 'RS_INDIVIDUAL' RESOURCE_TYPE
, '27A096D0422111D484CD00C04F53F265' IEU_ACTION_OBJECT_CODE
FROM IEM_RT_PROC_EMAILS A
, IEM_MSTEMAIL_ACCOUNTS B
, IEM_ROUTE_CLASSIFICATIONS C
, FND_LOOKUPS D
WHERE A.RESOURCE_ID <> 0
AND A.EMAIL_ACCOUNT_ID=B.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'
AND QUEUE_STATUS IS NULL ORDER BY STATUS
, ACCOUNT
, CLASSIFICATION