VIEW: APPS.PO_WF_NOTIFICATIONS_V

Source

View Text - Preformatted

SELECT WF.NOTIFICATION_ID, WF.GROUP_ID, WF.MESSAGE_TYPE, WF.MESSAGE_NAME, WF.RECIPIENT_ROLE, WF.STATUS, WF.ACCESS_KEY, WF.MAIL_STATUS, WF.PRIORITY, WF.BEGIN_DATE, WF.END_DATE, WF.DUE_DATE, WF.USER_COMMENT, WF.CALLBACK, WF.CONTEXT, WF_NOTIFICATION.GETSUBJECT(WF.NOTIFICATION_ID) SUBJECT, WF_NOTIFICATION.GETSHORTBODY(WF.NOTIFICATION_ID) MESSAGE, WF_DIRECTORY.GETROLEDISPLAYNAME(WF.RECIPIENT_ROLE) RECIPIENT_ROLE_NAME, WU.ORIG_SYSTEM_ID , PO_NOTIFICATIONS_SV3.GET_WF_ROLE_ID(WF.ORIGINAL_RECIPIENT), WF_DIRECTORY.GETROLEDISPLAYNAME(WF.RECIPIENT_ROLE), WF_DIRECTORY.GETROLEDISPLAYNAME(WF.ORIGINAL_RECIPIENT), PODTL.TYPE_NAME, POH.TYPE_LOOKUP_CODE, POH.CREATION_DATE, POH.PO_HEADER_ID, POH.SEGMENT1, DECODE(POH.TYPE_LOOKUP_CODE,'RFQ',NULL,'QUOTATION',NULL, PO_NOTIFICATIONS_SV3.GET_DOC_TOTAL(POH.TYPE_LOOKUP_CODE, POH.PO_HEADER_ID)) AMOUNT, POH.CURRENCY_CODE, PO_NOTIFICATIONS_SV3.GET_EMP_NAME(POH.AGENT_ID), POH.AGENT_ID, POH.COMMENTS, NVL(POH.AUTHORIZATION_STATUS,'INCOMPLETE'), PH.OBJECT_TYPE_CODE, PH.OBJECT_SUB_TYPE_CODE, PH.SEQUENCE_NUM, PH.OBJECT_REVISION_NUM, nvl(PH.APPROVAL_PATH_ID,0), PH.REQUEST_ID, PH.PROGRAM_APPLICATION_ID, PH.PROGRAM_DATE, PH.PROGRAM_ID, PH.LAST_UPDATE_DATE, POH.WF_ITEM_KEY, POH.WF_ITEM_TYPE, PH.ROWID, POH.ORG_ID, PODB.SECURITY_LEVEL_CODE , PODB.ACCESS_LEVEL_CODE FROM WF_NOTIFICATIONS WF, WF_ITEM_ACTIVITY_STATUSES WIAS, PO_DOCUMENT_TYPES_ALL_TL PODTL, PO_DOCUMENT_TYPES_ALL_B PODB, PO_HEADERS POH, PO_ACTION_HISTORY PH, WF_USERS WU WHERE POH.PO_HEADER_ID = PH.OBJECT_ID AND PH.OBJECT_TYPE_CODE = PODTL.DOCUMENT_TYPE_CODE AND PODTL.DOCUMENT_TYPE_CODE IN ('PO','PA') AND PH.ACTION_CODE IS NULL AND WIAS.ITEM_TYPE = POH.WF_ITEM_TYPE AND WIAS.ITEM_KEY = POH.WF_ITEM_KEY AND WIAS.NOTIFICATION_ID = WF.NOTIFICATION_ID AND WIAS.ACTIVITY_STATUS = 'NOTIFIED' AND WF.MESSAGE_TYPE = POH.WF_ITEM_TYPE AND WF.MESSAGE_NAME IN ('PO_PO_APPROVE_PDF','PO_PO_APPROVE','PO_PO_REMINDER_1', 'PO_PO_REMINDER_2','UNABLE_TO_RESERVE','UNABLE_TO_RESERVE_CO') AND PODTL.DOCUMENT_SUBTYPE = POH.TYPE_LOOKUP_CODE AND PODTL.LANGUAGE = USERENV('LANG') AND POH.AUTHORIZATION_STATUS IN ('IN PROCESS','PRE-APPROVED') AND WF.STATUS='OPEN' AND PH.SEQUENCE_NUM = (SELECT MAX(PAH1.SEQUENCE_NUM) FROM PO_ACTION_HISTORY PAH1 WHERE PAH1.OBJECT_ID = PH.OBJECT_ID AND PAH1.OBJECT_TYPE_CODE = PH.OBJECT_TYPE_CODE AND PAH1.OBJECT_SUB_TYPE_CODE = PH.OBJECT_SUB_TYPE_CODE) AND PODTL.ORG_ID(+) = POH.ORG_ID AND PODTL.ORG_ID = PODB.ORG_ID AND PODB.DOCUMENT_TYPE_CODE = PODTL.DOCUMENT_TYPE_CODE AND PODB.DOCUMENT_SUBTYPE = PODTL.DOCUMENT_SUBTYPE AND WU.NAME=WF.RECIPIENT_ROLE AND WU.ORIG_SYSTEM IN ( 'FND' , 'PER' ) UNION ALL SELECT WF.NOTIFICATION_ID, WF.GROUP_ID, WF.MESSAGE_TYPE, WF.MESSAGE_NAME, WF.RECIPIENT_ROLE, WF.STATUS, WF.ACCESS_KEY, WF.MAIL_STATUS, WF.PRIORITY, WF.BEGIN_DATE, WF.END_DATE, WF.DUE_DATE, WF.USER_COMMENT, WF.CALLBACK, WF.CONTEXT, WF_NOTIFICATION.GETSUBJECT(WF.NOTIFICATION_ID) SUBJECT, WF_NOTIFICATION.GETSHORTBODY(WF.NOTIFICATION_ID) MESSAGE, WF_DIRECTORY.GETROLEDISPLAYNAME(WF.RECIPIENT_ROLE), WU.ORIG_SYSTEM_ID , PO_NOTIFICATIONS_SV3.GET_WF_ROLE_ID(WF.ORIGINAL_RECIPIENT), WF_DIRECTORY.GETROLEDISPLAYNAME(WF.RECIPIENT_ROLE), WF_DIRECTORY.GETROLEDISPLAYNAME(WF.ORIGINAL_RECIPIENT), PODTL.TYPE_NAME, PODTL.DOCUMENT_SUBTYPE, POR.CREATION_DATE, POR.PO_RELEASE_ID, POH.SEGMENT1||'-'|| POR.RELEASE_NUM, TO_CHAR( PO_NOTIFICATIONS_SV3.GET_DOC_TOTAL('RELEASE',POR.PO_RELEASE_ID)) AMOUNT, POH.CURRENCY_CODE, PO_NOTIFICATIONS_SV3.GET_EMP_NAME(POR.AGENT_ID), POH.AGENT_ID, POH.COMMENTS, NVL( POR.AUTHORIZATION_STATUS,'INCOMPLETE'), PH.OBJECT_TYPE_CODE, PH.OBJECT_SUB_TYPE_CODE, PH.SEQUENCE_NUM, PH.OBJECT_REVISION_NUM, nvl(PH.APPROVAL_PATH_ID,0), PH.REQUEST_ID, PH.PROGRAM_APPLICATION_ID, PH.PROGRAM_DATE, PH.PROGRAM_ID, PH.LAST_UPDATE_DATE, POR.WF_ITEM_KEY, POR.WF_ITEM_TYPE, PH.ROWID, POR.ORG_ID, PODB.SECURITY_LEVEL_CODE, PODB.ACCESS_LEVEL_CODE FROM WF_NOTIFICATIONS WF, WF_ITEM_ACTIVITY_STATUSES WIAS, PO_DOCUMENT_TYPES_ALL_TL PODTL, PO_DOCUMENT_TYPES_ALL_B PODB, PO_ACTION_HISTORY PH, PO_HEADERS_ALL POH, PO_RELEASES POR, WF_USERS WU WHERE POR.PO_RELEASE_ID = PH.OBJECT_ID AND PH.OBJECT_TYPE_CODE = PODTL.DOCUMENT_TYPE_CODE AND PODTL.DOCUMENT_TYPE_CODE = 'RELEASE' AND PODTL.DOCUMENT_SUBTYPE = POR.RELEASE_TYPE AND PODTL.LANGUAGE = USERENV('LANG') AND POR. AUTHORIZATION_STATUS IN ('INPROCESS','PRE_APPROVED') AND WF.STATUS='OPEN' AND PH.ACTION_CODE IS NULL AND WIAS.ITEM_TYPE = POR.WF_ITEM_TYPE AND WIAS.ITEM_KEY = POR.WF_ITEM_KEY AND WIAS.NOTIFICATION_ID = WF.NOTIFICATION_ID AND WIAS.ACTIVITY_STATUS = 'NOTIFIED' AND POR.PO_HEADER_ID = POH.PO_HEADER_ID AND WF.MESSAGE_TYPE = POR.WF_ITEM_TYPE AND WF.MESSAGE_NAME IN ('PO_PO_APPROVE_PDF','PO_PO_APPROVE','PO_PO_REMINDER_1', 'PO_PO_REMINDER_2','UNABLE_TO_RESERVE','UNABLE_TO_RESERVE_CO') AND PH.SEQUENCE_NUM = (SELECT MAX(PAH1.SEQUENCE_NUM) FROM PO_ACTION_HISTORY PAH1 WHERE PAH1.OBJECT_ID = PH.OBJECT_ID AND PAH1.OBJECT_TYPE_CODE = PH.OBJECT_TYPE_CODE AND PAH1.OBJECT_SUB_TYPE_CODE = PH.OBJECT_SUB_TYPE_CODE) AND PODTL.ORG_ID (+) = POR.ORG_ID AND PODTL.ORG_ID = PODB.ORG_ID AND PODB.DOCUMENT_TYPE_CODE = PODTL.DOCUMENT_TYPE_CODE AND PODB.DOCUMENT_SUBTYPE = PODTL.DOCUMENT_SUBTYPE AND WU.NAME=WF.RECIPIENT_ROLE AND WU.ORIG_SYSTEM IN ( 'FND' , 'PER' ) UNION ALL SELECT WF.NOTIFICATION_ID, WF.GROUP_ID, WF.MESSAGE_TYPE, WF.MESSAGE_NAME, WF.RECIPIENT_ROLE, WF.STATUS, WF.ACCESS_KEY, WF.MAIL_STATUS, WF.PRIORITY, WF.BEGIN_DATE, WF.END_DATE, WF.DUE_DATE, WF.USER_COMMENT, WF.CALLBACK, WF.CONTEXT, WF_NOTIFICATION.GETSUBJECT(WF.NOTIFICATION_ID) SUBJECT, WF_NOTIFICATION.GETSHORTBODY(WF.NOTIFICATION_ID) MESSAGE, WF_DIRECTORY.GETROLEDISPLAYNAME(WF.RECIPIENT_ROLE), WU.ORIG_SYSTEM_ID , PO_NOTIFICATIONS_SV3.GET_WF_ROLE_ID(WF.ORIGINAL_RECIPIENT), WF_DIRECTORY.GETROLEDISPLAYNAME(WF.RECIPIENT_ROLE), WF_DIRECTORY.GETROLEDISPLAYNAME(WF.ORIGINAL_RECIPIENT), PODTL.TYPE_NAME, PRH.TYPE_LOOKUP_CODE, PRH.CREATION_DATE, PRH.REQUISITION_HEADER_ID, PRH.SEGMENT1, DECODE(PRH.TYPE_LOOKUP_CODE,'RFQ',NULL,'QUOTATION',NULL, PO_NOTIFICATIONS_SV3.GET_DOC_TOTAL(PRH.TYPE_LOOKUP_CODE,PRH.REQUISITION_HEADER_ID) ) AMOUNT, PO_CORE_S2.GET_BASE_CURRENCY(PRH.ORG_ID), PO_NOTIFICATIONS_SV3.GET_EMP_NAME(PRH.PREPARER_ID), PRH.PREPARER_ID, PRH.DESCRIPTION, NVL(PRH.AUTHORIZATION_STATUS,'INCOMPLETE'), PH.OBJECT_TYPE_CODE, PH.OBJECT_SUB_TYPE_CODE, PH.SEQUENCE_NUM, PH.OBJECT_REVISION_NUM, nvl(PH.APPROVAL_PATH_ID,0), PH.REQUEST_ID, PH.PROGRAM_APPLICATION_ID, PH.PROGRAM_DATE, PH.PROGRAM_ID, PH.LAST_UPDATE_DATE, PRH.WF_ITEM_KEY, PRH.WF_ITEM_TYPE, PH.ROWID, PRH.ORG_ID, PODB.SECURITY_LEVEL_CODE, PODB.ACCESS_LEVEL_CODE FROM WF_NOTIFICATIONS WF, WF_ITEM_ACTIVITY_STATUSES WIAS, PO_DOCUMENT_TYPES_ALL_TL PODTL, PO_DOCUMENT_TYPES_ALL_B PODB, PO_REQUISITION_HEADERS PRH, PO_ACTION_HISTORY PH, WF_USERS WU WHERE PRH.REQUISITION_HEADER_ID = PH.OBJECT_ID AND PH.OBJECT_TYPE_CODE = PODTL.DOCUMENT_TYPE_CODE AND PODTL.DOCUMENT_TYPE_CODE = 'REQUISITION' AND PODTL.DOCUMENT_SUBTYPE = PRH.TYPE_LOOKUP_CODE AND PODTL.LANGUAGE = USERENV('LANG') AND WF.STATUS='OPEN' AND PRH.AUTHORIZATION_STATUS in ('IN PROCESS','PRE-APPROVED') AND PH.ACTION_CODE IS NULL AND WIAS.ITEM_TYPE = PRH.WF_ITEM_TYPE AND WIAS.ITEM_KEY = PRH.WF_ITEM_KEY AND WIAS.NOTIFICATION_ID = WF.NOTIFICATION_ID AND WIAS.ACTIVITY_STATUS = 'NOTIFIED' AND WF.MESSAGE_TYPE = PRH.WF_ITEM_TYPE AND WF.MESSAGE_NAME IN ('PO_REQ_APPROVE','PO_REQ_REMINDER1','PO_REQ_REMINDER2','PO_REQ_APPROVE_JRAD','PO_REQ_REMINDER1_JRAD','O_REQ_REMINDER2_JRAD', 'PO_REQ_INVALID_FORWARD','PO_REQ_INVALID_FORWARD_R1','PO_REQ_INVALID_FORWARD_R2','UNABLE_TO_RESERVE') AND PH.SEQUENCE_NUM = (SELECT MAX(PAH1.SEQUENCE_NUM) FROM PO_ACTION_HISTORY PAH1 WHERE PAH1.OBJECT_ID = PH.OBJECT_ID AND PAH1.OBJECT_TYPE_CODE = PH.OBJECT_TYPE_CODE AND PAH1.OBJECT_SUB_TYPE_CODE = PH.OBJECT_SUB_TYPE_CODE) AND PODTL.ORG_ID(+) = PRH.ORG_ID AND PODTL.ORG_ID = PODB.ORG_ID AND PODB.DOCUMENT_TYPE_CODE = PODTL.DOCUMENT_TYPE_CODE AND PODB.DOCUMENT_SUBTYPE = PODTL.DOCUMENT_SUBTYPE AND WU.NAME=WF.RECIPIENT_ROLE AND WU.ORIG_SYSTEM IN ( 'FND' , 'PER' )

View Text - HTML Formatted

SELECT WF.NOTIFICATION_ID
, WF.GROUP_ID
, WF.MESSAGE_TYPE
, WF.MESSAGE_NAME
, WF.RECIPIENT_ROLE
, WF.STATUS
, WF.ACCESS_KEY
, WF.MAIL_STATUS
, WF.PRIORITY
, WF.BEGIN_DATE
, WF.END_DATE
, WF.DUE_DATE
, WF.USER_COMMENT
, WF.CALLBACK
, WF.CONTEXT
, WF_NOTIFICATION.GETSUBJECT(WF.NOTIFICATION_ID) SUBJECT
, WF_NOTIFICATION.GETSHORTBODY(WF.NOTIFICATION_ID) MESSAGE
, WF_DIRECTORY.GETROLEDISPLAYNAME(WF.RECIPIENT_ROLE) RECIPIENT_ROLE_NAME
, WU.ORIG_SYSTEM_ID
, PO_NOTIFICATIONS_SV3.GET_WF_ROLE_ID(WF.ORIGINAL_RECIPIENT)
, WF_DIRECTORY.GETROLEDISPLAYNAME(WF.RECIPIENT_ROLE)
, WF_DIRECTORY.GETROLEDISPLAYNAME(WF.ORIGINAL_RECIPIENT)
, PODTL.TYPE_NAME
, POH.TYPE_LOOKUP_CODE
, POH.CREATION_DATE
, POH.PO_HEADER_ID
, POH.SEGMENT1
, DECODE(POH.TYPE_LOOKUP_CODE
, 'RFQ'
, NULL
, 'QUOTATION'
, NULL
, PO_NOTIFICATIONS_SV3.GET_DOC_TOTAL(POH.TYPE_LOOKUP_CODE
, POH.PO_HEADER_ID)) AMOUNT
, POH.CURRENCY_CODE
, PO_NOTIFICATIONS_SV3.GET_EMP_NAME(POH.AGENT_ID)
, POH.AGENT_ID
, POH.COMMENTS
, NVL(POH.AUTHORIZATION_STATUS
, 'INCOMPLETE')
, PH.OBJECT_TYPE_CODE
, PH.OBJECT_SUB_TYPE_CODE
, PH.SEQUENCE_NUM
, PH.OBJECT_REVISION_NUM
, NVL(PH.APPROVAL_PATH_ID
, 0)
, PH.REQUEST_ID
, PH.PROGRAM_APPLICATION_ID
, PH.PROGRAM_DATE
, PH.PROGRAM_ID
, PH.LAST_UPDATE_DATE
, POH.WF_ITEM_KEY
, POH.WF_ITEM_TYPE
, PH.ROWID
, POH.ORG_ID
, PODB.SECURITY_LEVEL_CODE
, PODB.ACCESS_LEVEL_CODE
FROM WF_NOTIFICATIONS WF
, WF_ITEM_ACTIVITY_STATUSES WIAS
, PO_DOCUMENT_TYPES_ALL_TL PODTL
, PO_DOCUMENT_TYPES_ALL_B PODB
, PO_HEADERS POH
, PO_ACTION_HISTORY PH
, WF_USERS WU
WHERE POH.PO_HEADER_ID = PH.OBJECT_ID
AND PH.OBJECT_TYPE_CODE = PODTL.DOCUMENT_TYPE_CODE
AND PODTL.DOCUMENT_TYPE_CODE IN ('PO'
, 'PA')
AND PH.ACTION_CODE IS NULL
AND WIAS.ITEM_TYPE = POH.WF_ITEM_TYPE
AND WIAS.ITEM_KEY = POH.WF_ITEM_KEY
AND WIAS.NOTIFICATION_ID = WF.NOTIFICATION_ID
AND WIAS.ACTIVITY_STATUS = 'NOTIFIED'
AND WF.MESSAGE_TYPE = POH.WF_ITEM_TYPE
AND WF.MESSAGE_NAME IN ('PO_PO_APPROVE_PDF'
, 'PO_PO_APPROVE'
, 'PO_PO_REMINDER_1'
, 'PO_PO_REMINDER_2'
, 'UNABLE_TO_RESERVE'
, 'UNABLE_TO_RESERVE_CO')
AND PODTL.DOCUMENT_SUBTYPE = POH.TYPE_LOOKUP_CODE
AND PODTL.LANGUAGE = USERENV('LANG')
AND POH.AUTHORIZATION_STATUS IN ('IN PROCESS'
, 'PRE-APPROVED')
AND WF.STATUS='OPEN'
AND PH.SEQUENCE_NUM = (SELECT MAX(PAH1.SEQUENCE_NUM)
FROM PO_ACTION_HISTORY PAH1
WHERE PAH1.OBJECT_ID = PH.OBJECT_ID
AND PAH1.OBJECT_TYPE_CODE = PH.OBJECT_TYPE_CODE
AND PAH1.OBJECT_SUB_TYPE_CODE = PH.OBJECT_SUB_TYPE_CODE)
AND PODTL.ORG_ID(+) = POH.ORG_ID
AND PODTL.ORG_ID = PODB.ORG_ID
AND PODB.DOCUMENT_TYPE_CODE = PODTL.DOCUMENT_TYPE_CODE
AND PODB.DOCUMENT_SUBTYPE = PODTL.DOCUMENT_SUBTYPE
AND WU.NAME=WF.RECIPIENT_ROLE
AND WU.ORIG_SYSTEM IN ( 'FND'
, 'PER' ) UNION ALL SELECT WF.NOTIFICATION_ID
, WF.GROUP_ID
, WF.MESSAGE_TYPE
, WF.MESSAGE_NAME
, WF.RECIPIENT_ROLE
, WF.STATUS
, WF.ACCESS_KEY
, WF.MAIL_STATUS
, WF.PRIORITY
, WF.BEGIN_DATE
, WF.END_DATE
, WF.DUE_DATE
, WF.USER_COMMENT
, WF.CALLBACK
, WF.CONTEXT
, WF_NOTIFICATION.GETSUBJECT(WF.NOTIFICATION_ID) SUBJECT
, WF_NOTIFICATION.GETSHORTBODY(WF.NOTIFICATION_ID) MESSAGE
, WF_DIRECTORY.GETROLEDISPLAYNAME(WF.RECIPIENT_ROLE)
, WU.ORIG_SYSTEM_ID
, PO_NOTIFICATIONS_SV3.GET_WF_ROLE_ID(WF.ORIGINAL_RECIPIENT)
, WF_DIRECTORY.GETROLEDISPLAYNAME(WF.RECIPIENT_ROLE)
, WF_DIRECTORY.GETROLEDISPLAYNAME(WF.ORIGINAL_RECIPIENT)
, PODTL.TYPE_NAME
, PODTL.DOCUMENT_SUBTYPE
, POR.CREATION_DATE
, POR.PO_RELEASE_ID
, POH.SEGMENT1||'-'|| POR.RELEASE_NUM
, TO_CHAR( PO_NOTIFICATIONS_SV3.GET_DOC_TOTAL('RELEASE'
, POR.PO_RELEASE_ID)) AMOUNT
, POH.CURRENCY_CODE
, PO_NOTIFICATIONS_SV3.GET_EMP_NAME(POR.AGENT_ID)
, POH.AGENT_ID
, POH.COMMENTS
, NVL( POR.AUTHORIZATION_STATUS
, 'INCOMPLETE')
, PH.OBJECT_TYPE_CODE
, PH.OBJECT_SUB_TYPE_CODE
, PH.SEQUENCE_NUM
, PH.OBJECT_REVISION_NUM
, NVL(PH.APPROVAL_PATH_ID
, 0)
, PH.REQUEST_ID
, PH.PROGRAM_APPLICATION_ID
, PH.PROGRAM_DATE
, PH.PROGRAM_ID
, PH.LAST_UPDATE_DATE
, POR.WF_ITEM_KEY
, POR.WF_ITEM_TYPE
, PH.ROWID
, POR.ORG_ID
, PODB.SECURITY_LEVEL_CODE
, PODB.ACCESS_LEVEL_CODE
FROM WF_NOTIFICATIONS WF
, WF_ITEM_ACTIVITY_STATUSES WIAS
, PO_DOCUMENT_TYPES_ALL_TL PODTL
, PO_DOCUMENT_TYPES_ALL_B PODB
, PO_ACTION_HISTORY PH
, PO_HEADERS_ALL POH
, PO_RELEASES POR
, WF_USERS WU
WHERE POR.PO_RELEASE_ID = PH.OBJECT_ID
AND PH.OBJECT_TYPE_CODE = PODTL.DOCUMENT_TYPE_CODE
AND PODTL.DOCUMENT_TYPE_CODE = 'RELEASE'
AND PODTL.DOCUMENT_SUBTYPE = POR.RELEASE_TYPE
AND PODTL.LANGUAGE = USERENV('LANG')
AND POR. AUTHORIZATION_STATUS IN ('INPROCESS'
, 'PRE_APPROVED')
AND WF.STATUS='OPEN'
AND PH.ACTION_CODE IS NULL
AND WIAS.ITEM_TYPE = POR.WF_ITEM_TYPE
AND WIAS.ITEM_KEY = POR.WF_ITEM_KEY
AND WIAS.NOTIFICATION_ID = WF.NOTIFICATION_ID
AND WIAS.ACTIVITY_STATUS = 'NOTIFIED'
AND POR.PO_HEADER_ID = POH.PO_HEADER_ID
AND WF.MESSAGE_TYPE = POR.WF_ITEM_TYPE
AND WF.MESSAGE_NAME IN ('PO_PO_APPROVE_PDF'
, 'PO_PO_APPROVE'
, 'PO_PO_REMINDER_1'
, 'PO_PO_REMINDER_2'
, 'UNABLE_TO_RESERVE'
, 'UNABLE_TO_RESERVE_CO')
AND PH.SEQUENCE_NUM = (SELECT MAX(PAH1.SEQUENCE_NUM)
FROM PO_ACTION_HISTORY PAH1
WHERE PAH1.OBJECT_ID = PH.OBJECT_ID
AND PAH1.OBJECT_TYPE_CODE = PH.OBJECT_TYPE_CODE
AND PAH1.OBJECT_SUB_TYPE_CODE = PH.OBJECT_SUB_TYPE_CODE)
AND PODTL.ORG_ID (+) = POR.ORG_ID
AND PODTL.ORG_ID = PODB.ORG_ID
AND PODB.DOCUMENT_TYPE_CODE = PODTL.DOCUMENT_TYPE_CODE
AND PODB.DOCUMENT_SUBTYPE = PODTL.DOCUMENT_SUBTYPE
AND WU.NAME=WF.RECIPIENT_ROLE
AND WU.ORIG_SYSTEM IN ( 'FND'
, 'PER' ) UNION ALL SELECT WF.NOTIFICATION_ID
, WF.GROUP_ID
, WF.MESSAGE_TYPE
, WF.MESSAGE_NAME
, WF.RECIPIENT_ROLE
, WF.STATUS
, WF.ACCESS_KEY
, WF.MAIL_STATUS
, WF.PRIORITY
, WF.BEGIN_DATE
, WF.END_DATE
, WF.DUE_DATE
, WF.USER_COMMENT
, WF.CALLBACK
, WF.CONTEXT
, WF_NOTIFICATION.GETSUBJECT(WF.NOTIFICATION_ID) SUBJECT
, WF_NOTIFICATION.GETSHORTBODY(WF.NOTIFICATION_ID) MESSAGE
, WF_DIRECTORY.GETROLEDISPLAYNAME(WF.RECIPIENT_ROLE)
, WU.ORIG_SYSTEM_ID
, PO_NOTIFICATIONS_SV3.GET_WF_ROLE_ID(WF.ORIGINAL_RECIPIENT)
, WF_DIRECTORY.GETROLEDISPLAYNAME(WF.RECIPIENT_ROLE)
, WF_DIRECTORY.GETROLEDISPLAYNAME(WF.ORIGINAL_RECIPIENT)
, PODTL.TYPE_NAME
, PRH.TYPE_LOOKUP_CODE
, PRH.CREATION_DATE
, PRH.REQUISITION_HEADER_ID
, PRH.SEGMENT1
, DECODE(PRH.TYPE_LOOKUP_CODE
, 'RFQ'
, NULL
, 'QUOTATION'
, NULL
, PO_NOTIFICATIONS_SV3.GET_DOC_TOTAL(PRH.TYPE_LOOKUP_CODE
, PRH.REQUISITION_HEADER_ID) ) AMOUNT
, PO_CORE_S2.GET_BASE_CURRENCY(PRH.ORG_ID)
, PO_NOTIFICATIONS_SV3.GET_EMP_NAME(PRH.PREPARER_ID)
, PRH.PREPARER_ID
, PRH.DESCRIPTION
, NVL(PRH.AUTHORIZATION_STATUS
, 'INCOMPLETE')
, PH.OBJECT_TYPE_CODE
, PH.OBJECT_SUB_TYPE_CODE
, PH.SEQUENCE_NUM
, PH.OBJECT_REVISION_NUM
, NVL(PH.APPROVAL_PATH_ID
, 0)
, PH.REQUEST_ID
, PH.PROGRAM_APPLICATION_ID
, PH.PROGRAM_DATE
, PH.PROGRAM_ID
, PH.LAST_UPDATE_DATE
, PRH.WF_ITEM_KEY
, PRH.WF_ITEM_TYPE
, PH.ROWID
, PRH.ORG_ID
, PODB.SECURITY_LEVEL_CODE
, PODB.ACCESS_LEVEL_CODE
FROM WF_NOTIFICATIONS WF
, WF_ITEM_ACTIVITY_STATUSES WIAS
, PO_DOCUMENT_TYPES_ALL_TL PODTL
, PO_DOCUMENT_TYPES_ALL_B PODB
, PO_REQUISITION_HEADERS PRH
, PO_ACTION_HISTORY PH
, WF_USERS WU
WHERE PRH.REQUISITION_HEADER_ID = PH.OBJECT_ID
AND PH.OBJECT_TYPE_CODE = PODTL.DOCUMENT_TYPE_CODE
AND PODTL.DOCUMENT_TYPE_CODE = 'REQUISITION'
AND PODTL.DOCUMENT_SUBTYPE = PRH.TYPE_LOOKUP_CODE
AND PODTL.LANGUAGE = USERENV('LANG')
AND WF.STATUS='OPEN'
AND PRH.AUTHORIZATION_STATUS IN ('IN PROCESS'
, 'PRE-APPROVED')
AND PH.ACTION_CODE IS NULL
AND WIAS.ITEM_TYPE = PRH.WF_ITEM_TYPE
AND WIAS.ITEM_KEY = PRH.WF_ITEM_KEY
AND WIAS.NOTIFICATION_ID = WF.NOTIFICATION_ID
AND WIAS.ACTIVITY_STATUS = 'NOTIFIED'
AND WF.MESSAGE_TYPE = PRH.WF_ITEM_TYPE
AND WF.MESSAGE_NAME IN ('PO_REQ_APPROVE'
, 'PO_REQ_REMINDER1'
, 'PO_REQ_REMINDER2'
, 'PO_REQ_APPROVE_JRAD'
, 'PO_REQ_REMINDER1_JRAD'
, 'O_REQ_REMINDER2_JRAD'
, 'PO_REQ_INVALID_FORWARD'
, 'PO_REQ_INVALID_FORWARD_R1'
, 'PO_REQ_INVALID_FORWARD_R2'
, 'UNABLE_TO_RESERVE')
AND PH.SEQUENCE_NUM = (SELECT MAX(PAH1.SEQUENCE_NUM)
FROM PO_ACTION_HISTORY PAH1
WHERE PAH1.OBJECT_ID = PH.OBJECT_ID
AND PAH1.OBJECT_TYPE_CODE = PH.OBJECT_TYPE_CODE
AND PAH1.OBJECT_SUB_TYPE_CODE = PH.OBJECT_SUB_TYPE_CODE)
AND PODTL.ORG_ID(+) = PRH.ORG_ID
AND PODTL.ORG_ID = PODB.ORG_ID
AND PODB.DOCUMENT_TYPE_CODE = PODTL.DOCUMENT_TYPE_CODE
AND PODB.DOCUMENT_SUBTYPE = PODTL.DOCUMENT_SUBTYPE
AND WU.NAME=WF.RECIPIENT_ROLE
AND WU.ORIG_SYSTEM IN ( 'FND'
, 'PER' )