View: PO_WF_NOTIFICATIONS_V

Product:	PO - Purchasing
Description:	Information about purchase orders, releases and requisitions that have outstanding notifications
Implementation/DBA Data:	APPS.PO_WF_NOTIFICATIONS_V

View Text

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
, PO_NOTIFICATIONS_SV3.GET_WF_ROLE_ID(WF.RECIPIENT_ROLE)
, 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
FROM WF_NOTIFICATIONS WF
, WF_ITEM_ACTIVITY_STATUSES WIAS
, PO_DOCUMENT_TYPES_ALL_TL PODTL
, PO_HEADERS POH
, PO_ACTION_HISTORY PH
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')
AND PODTL.DOCUMENT_SUBTYPE (+) = POH.TYPE_LOOKUP_CODE
AND PODTL.LANGUAGE(+) = USERENV('LANG')
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 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)
, PO_NOTIFICATIONS_SV3.GET_WF_ROLE_ID(WF.RECIPIENT_ROLE)
, 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
FROM WF_NOTIFICATIONS WF
, WF_ITEM_ACTIVITY_STATUSES WIAS
, PO_DOCUMENT_TYPES_ALL_TL PODTL
, PO_ACTION_HISTORY PH
, PO_HEADERS_ALL POH
, PO_RELEASES POR
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 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')
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 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)
, PO_NOTIFICATIONS_SV3.GET_WF_ROLE_ID(WF.RECIPIENT_ROLE)
, 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
FROM WF_NOTIFICATIONS WF
, WF_ITEM_ACTIVITY_STATUSES WIAS
, PO_DOCUMENT_TYPES_ALL_TL PODTL
, PO_REQUISITION_HEADERS PRH
, PO_ACTION_HISTORY PH
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 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'
, 'PO_REQ_REMINDER2_JRAD'
, 'PO_REQ_APPROVE_SIMPLE'
, 'PO_REQ_APPROVE_SIMPLE_JRAD')
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

Columns