View: POR_VIEW_REQS_BY_PURCHASING_V

Product:	ICX - Oracle iProcurement
Description:	This view access is restricted to the document owner, subsequent approvers and those individuals defined as buyers
Implementation/DBA Data:	APPS.POR_VIEW_REQS_BY_PURCHASING_V

View Text

SELECT PRH.REQUISITION_HEADER_ID
, PRH.SEGMENT1
, PRH.PREPARER_ID
, HRE.FULL_NAME
, UPPER(HRE.LAST_NAME)
, PRH.DESCRIPTION
, PRH.AUTHORIZATION_STATUS
, PLC_AUTH.DISPLAYED_FIELD
, PRH.CANCEL_FLAG
, NVL(PRH.CLOSED_CODE
, 'OPEN')
, APC.CARD_NUMBER
, PRH.CREATION_DATE
, TO_CHAR(PRH.CREATION_DATE
, 'YYYY-MM-DD')
, PRH.TRANSFERRED_TO_OE_FLAG
, CASE WHEN (EXISTS (SELECT 1
FROM PO_REQUISITION_LINES_ALL PRL
WHERE PRL.REQUISITION_HEADER_ID = PRH.REQUISITION_HEADER_ID
AND PRL.AUCTION_DISPLAY_NUMBER IS NOT NULL)) THEN 'Y' ELSE 'N' END
, PRH.EMERGENCY_PO_NUM
, NVL(PRH.CHANGE_PENDING_FLAG
, 'N')
FROM PO_LOOKUP_CODES PLC_AUTH
, AP_CARDS APC
, FND_USER FNU
, PER_ALL_PEOPLE_F HRE
, PO_REQUISITION_HEADERS PRH
WHERE FNU.USER_ID = FND_GLOBAL.USER_ID
AND ((FNU.EMPLOYEE_ID = PRH.PREPARER_ID) OR EXISTS ( SELECT 'THIS EMPLOYEE IS A REQUESTOR'
FROM PO_REQUISITION_LINES PRL
WHERE PRL.REQUISITION_HEADER_ID = PRH.REQUISITION_HEADER_ID
AND NVL(PRL.TO_PERSON_ID
, 0) = FNU.EMPLOYEE_ID) OR EXISTS ( SELECT 'THIS EMPLOYEE IS IN APPROVAL PATH OF THIS DOC'
FROM PO_ACTION_HISTORY POAH
WHERE POAH.EMPLOYEE_ID = FNU.EMPLOYEE_ID
AND POAH.OBJECT_ID = REQUISITION_HEADER_ID
AND POAH.OBJECT_TYPE_CODE = 'REQUISITION'
AND POAH.OBJECT_SUB_TYPE_CODE = 'PURCHASE') OR EXISTS (SELECT 'THIS EMPLOYEE IS AN AGENT'
FROM PO_AGENTS POA
WHERE POA.AGENT_ID = FNU.EMPLOYEE_ID
AND SYSDATE BETWEEN NVL(POA.START_DATE_ACTIVE
, SYSDATE)
AND NVL(POA.END_DATE_ACTIVE
, SYSDATE + 1) ))
AND PRH.PREPARER_ID = HRE.PERSON_ID
AND HRE.EFFECTIVE_START_DATE <= SYSDATE
AND HRE.EFFECTIVE_END_DATE >= SYSDATE
AND APC.CARD_ID(+) = PRH.PCARD_ID
AND PLC_AUTH.LOOKUP_CODE = NVL(PRH.AUTHORIZATION_STATUS
, 'INCOMPLETE')
AND PLC_AUTH.LOOKUP_TYPE = 'AUTHORIZATION STATUS'
AND PRH.TYPE_LOOKUP_CODE = 'PURCHASE'

Columns