Product: | OKX - Contracts Integration |
---|---|
Description: | Message notifications |
Implementation/DBA Data: | APPS.OKX_LAUNCHPAD_INBOX_V |
SELECT WF.NOTIFICATION_ID NOTIFICATION_ID
, WF.GROUP_ID GROUP_ID
, F.USER_ID USER_ID
, WF.MESSAGE_TYPE MESSAGE_TYPE
, WF.MESSAGE_NAME MESSAGE_NAME
, WF.RECIPIENT_ROLE RECIPIENT_ROLE
, L.MEANING STATUS
, WF.STATUS STATUS_CODE
, WF.PRIORITY PRIORITY
, WF.BEGIN_DATE BEGIN_DATE
, WF.END_DATE END_DATE
, WF.DUE_DATE DUE_DATE
, WF.USER_COMMENT USER_COMMENT
, WF_NOTIFICATION.GETSUBJECT(WF.NOTIFICATION_ID) SUBJECT
, WF_NOTIFICATION.GETSHORTBODY(WF.NOTIFICATION_ID) MESSAGE
, OKC_QUERY.GETKNUMBER(WF.NOTIFICATION_ID
, 'CONTRACT_ID') CONTRACT_NUMBER
, OKC_QUERY.GETKMODIFIER(WF.NOTIFICATION_ID
, 'CONTRACT_ID') CONTRACT_NUMBER_MODIFIER
, OKC_QUERY.GETKID(WF.NOTIFICATION_ID
, 'CONTRACT_ID') CONTRACT_ID
, 1 DUMMY_SORT
FROM FND_USER F
, WF_LOOKUPS_TL L
, WF_NOTIFICATIONS WF
WHERE L.LANGUAGE = USERENV('LANG')
AND L.LOOKUP_TYPE = 'WF_NOTIFICATION_STATUS'
AND WF.STATUS = L.LOOKUP_CODE
AND WF.RECIPIENT_ROLE = F.USER_NAME
AND WF.MESSAGE_TYPE IN ( SELECT WF_NAME
FROM OKC_PROCESS_DEFS_B
WHERE PDF_TYPE='WPS'
AND USAGE IN ('APPROVE'
, 'CHG_REQ_APPROVE') UNION SELECT 'OKCALERT'
FROM DUAL )