VIEW: APPS.POR_VIEW_REQS_TO_APPR_V

Source

View Text - Preformatted

SELECT PRH.REQUISITION_HEADER_ID, PRH.SEGMENT1, PRH.DESCRIPTION, PRH.AUTHORIZATION_STATUS, PLC_AUTH.DISPLAYED_FIELD, APC.CARD_NUMBER, PRH.CREATION_DATE, PRH.TRANSFERRED_TO_OE_FLAG, POR_VIEW_REQS_PKG.IS_PLACEDONNG(PRH.REQUISITION_HEADER_ID), PRH.EMERGENCY_PO_NUM, WN.NOTIFICATION_ID FROM PO_REQUISITION_HEADERS PRH, PO_LOOKUP_CODES PLC_AUTH, AP_CARDS APC, PER_ALL_PEOPLE_F HRE, GL_SETS_OF_BOOKS SOB, FINANCIALS_SYSTEM_PARAMETERS FSP, WF_NOTIFICATION_ATTRIBUTES WNA, WF_NOTIFICATIONS WN, WF_USER_ROLES WLUR WHERE WLUR.USER_NAME = FND_GLOBAL.USER_NAME AND PRH.SEGMENT1 = WNA.TEXT_VALUE AND WNA.NAME = 'DOCUMENT_NUMBER' AND WNA.NOTIFICATION_ID = WN.NOTIFICATION_ID AND WN.RECIPIENT_ROLE = WLUR.ROLE_NAME AND WN.STATUS = 'OPEN' AND WN.MESSAGE_TYPE IN ( SELECT DISTINCT WF_APPROVAL_ITEMTYPE FROM PO_DOCUMENT_TYPES) AND WN.MESSAGE_NAME IN ( 'PO_REQ_APPROVE', 'PO_REQ_REMINDER1', 'PO_REQ_APPROVE_WEB_MSG', 'PO_REQ_REMINDER2', 'PO_REQ_REMINDER1_WEB', 'PO_REQ_REMINDER2_WEB') AND PRH.PREPARER_ID = HRE.PERSON_ID AND HRE.EFFECTIVE_START_DATE <= SYSDATE AND HRE.EFFECTIVE_END_DATE >= SYSDATE AND SOB.SET_OF_BOOKS_ID = FSP.SET_OF_BOOKS_ID AND PRH.PCARD_ID = APC.CARD_ID(+) AND PLC_AUTH.LOOKUP_CODE = NVL(PRH.AUTHORIZATION_STATUS, 'INCOMPLETE') AND PLC_AUTH.LOOKUP_TYPE = 'AUTHORIZATION STATUS' AND PRH.TYPE_LOOKUP_CODE = 'PURCHASE'

View Text - HTML Formatted

SELECT PRH.REQUISITION_HEADER_ID
, PRH.SEGMENT1
, PRH.DESCRIPTION
, PRH.AUTHORIZATION_STATUS
, PLC_AUTH.DISPLAYED_FIELD
, APC.CARD_NUMBER
, PRH.CREATION_DATE
, PRH.TRANSFERRED_TO_OE_FLAG
, POR_VIEW_REQS_PKG.IS_PLACEDONNG(PRH.REQUISITION_HEADER_ID)
, PRH.EMERGENCY_PO_NUM
, WN.NOTIFICATION_ID
FROM PO_REQUISITION_HEADERS PRH
, PO_LOOKUP_CODES PLC_AUTH
, AP_CARDS APC
, PER_ALL_PEOPLE_F HRE
, GL_SETS_OF_BOOKS SOB
, FINANCIALS_SYSTEM_PARAMETERS FSP
, WF_NOTIFICATION_ATTRIBUTES WNA
, WF_NOTIFICATIONS WN
, WF_USER_ROLES WLUR
WHERE WLUR.USER_NAME = FND_GLOBAL.USER_NAME
AND PRH.SEGMENT1 = WNA.TEXT_VALUE
AND WNA.NAME = 'DOCUMENT_NUMBER'
AND WNA.NOTIFICATION_ID = WN.NOTIFICATION_ID
AND WN.RECIPIENT_ROLE = WLUR.ROLE_NAME
AND WN.STATUS = 'OPEN'
AND WN.MESSAGE_TYPE IN ( SELECT DISTINCT WF_APPROVAL_ITEMTYPE
FROM PO_DOCUMENT_TYPES)
AND WN.MESSAGE_NAME IN ( 'PO_REQ_APPROVE'
, 'PO_REQ_REMINDER1'
, 'PO_REQ_APPROVE_WEB_MSG'
, 'PO_REQ_REMINDER2'
, 'PO_REQ_REMINDER1_WEB'
, 'PO_REQ_REMINDER2_WEB')
AND PRH.PREPARER_ID = HRE.PERSON_ID
AND HRE.EFFECTIVE_START_DATE <= SYSDATE
AND HRE.EFFECTIVE_END_DATE >= SYSDATE
AND SOB.SET_OF_BOOKS_ID = FSP.SET_OF_BOOKS_ID
AND PRH.PCARD_ID = APC.CARD_ID(+)
AND PLC_AUTH.LOOKUP_CODE = NVL(PRH.AUTHORIZATION_STATUS
, 'INCOMPLETE')
AND PLC_AUTH.LOOKUP_TYPE = 'AUTHORIZATION STATUS'
AND PRH.TYPE_LOOKUP_CODE = 'PURCHASE'