VIEW: APPS.CSF_M_MAIL_RECIPIENTS_V

Source

View Text - Preformatted

SELECT acc.access_id , wfn.notification_id , wfn.from_role as msgorigaddress , csm_util_pkg.get_wf_attrText(wfn.notification_id,'DELETE_FLAG') as delete_flag , csm_util_pkg.get_wf_attrText(wfn.notification_id,'READ_FLAG') as read_flag , wfn.begin_date as datereceived , csm_util_pkg.get_varchar(wfn.recipient_role) as recipaddress , jtrs.resource_id , jtrs_recv.resource_id as resource_recv_id , asg_base.get_user_name as client_name FROM CSM_NOTIFICATIONS_ACC acc, WF_NOTIFICATIONS wfn, JTF_RS_RESOURCE_EXTNS jtrs, JTF_RS_RESOURCE_EXTNS jtrs_recv WHERE acc.notification_id = wfn.notification_id AND acc.user_id = asg_base.get_user_id AND ( (NVL(csm_util_pkg.get_wf_attrText(wfn.notification_id,'MESSAGE_TYPE'),'i')='i' AND EXISTS(select 1 from asg_user au where wfn.recipient_role = au.user_name and acc.user_id=au.owner_id) ) OR (NVL(csm_util_pkg.get_wf_attrText(wfn.notification_id,'MESSAGE_TYPE'),'i')='b' AND CSM_UTIL_PKG.get_group_owner(substr(wfn.recipient_role,instr(wfn.recipient_role,':')+1))=acc.user_id ) ) AND wfn.from_role =jtrs.user_name(+) AND wfn.recipient_role =jtrs_recv.user_name(+)

View Text - HTML Formatted

SELECT ACC.ACCESS_ID
, WFN.NOTIFICATION_ID
, WFN.FROM_ROLE AS MSGORIGADDRESS
, CSM_UTIL_PKG.GET_WF_ATTRTEXT(WFN.NOTIFICATION_ID
, 'DELETE_FLAG') AS DELETE_FLAG
, CSM_UTIL_PKG.GET_WF_ATTRTEXT(WFN.NOTIFICATION_ID
, 'READ_FLAG') AS READ_FLAG
, WFN.BEGIN_DATE AS DATERECEIVED
, CSM_UTIL_PKG.GET_VARCHAR(WFN.RECIPIENT_ROLE) AS RECIPADDRESS
, JTRS.RESOURCE_ID
, JTRS_RECV.RESOURCE_ID AS RESOURCE_RECV_ID
, ASG_BASE.GET_USER_NAME AS CLIENT_NAME
FROM CSM_NOTIFICATIONS_ACC ACC
, WF_NOTIFICATIONS WFN
, JTF_RS_RESOURCE_EXTNS JTRS
, JTF_RS_RESOURCE_EXTNS JTRS_RECV
WHERE ACC.NOTIFICATION_ID = WFN.NOTIFICATION_ID
AND ACC.USER_ID = ASG_BASE.GET_USER_ID
AND ( (NVL(CSM_UTIL_PKG.GET_WF_ATTRTEXT(WFN.NOTIFICATION_ID
, 'MESSAGE_TYPE')
, 'I')='I'
AND EXISTS(SELECT 1
FROM ASG_USER AU
WHERE WFN.RECIPIENT_ROLE = AU.USER_NAME
AND ACC.USER_ID=AU.OWNER_ID) ) OR (NVL(CSM_UTIL_PKG.GET_WF_ATTRTEXT(WFN.NOTIFICATION_ID
, 'MESSAGE_TYPE')
, 'I')='B'
AND CSM_UTIL_PKG.GET_GROUP_OWNER(SUBSTR(WFN.RECIPIENT_ROLE
, INSTR(WFN.RECIPIENT_ROLE
, ':')+1))=ACC.USER_ID ) )
AND WFN.FROM_ROLE =JTRS.USER_NAME(+)
AND WFN.RECIPIENT_ROLE =JTRS_RECV.USER_NAME(+)