VIEW: APPS.POR_VIEW_REQS_V

Source

View Text - Preformatted

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, TO_CHAR(PRH.CREATION_DATE, FND_PROFILE.VALUE_WNPS('ICX_DATE_FORMAT_MASK')), TO_CHAR(PRH.CREATION_DATE, 'YYYY-MM-DD'), TO_CHAR(POR_VIEW_REQS_PKG.GET_REQ_TOTAL(PRH.REQUISITION_HEADER_ID), FND_CURRENCY.SAFE_GET_FORMAT_MASK(SOB.CURRENCY_CODE, 30)), POR_VIEW_REQS_PKG.GET_SUPPLIER_NAME(PRH.REQUISITION_HEADER_ID), POR_VIEW_REQS_PKG.IS_PLACEDONPO(PRH.REQUISITION_HEADER_ID), 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, PER_ALL_PEOPLE_F HRE, AP_CARDS APC, GL_SETS_OF_BOOKS SOB, FINANCIALS_SYSTEM_PARAMETERS FSP, PO_REQUISITION_HEADERS PRH WHERE 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' AND SOB.SET_OF_BOOKS_ID = FSP.SET_OF_BOOKS_ID

View Text - HTML Formatted

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
, TO_CHAR(PRH.CREATION_DATE
, FND_PROFILE.VALUE_WNPS('ICX_DATE_FORMAT_MASK'))
, TO_CHAR(PRH.CREATION_DATE
, 'YYYY-MM-DD')
, TO_CHAR(POR_VIEW_REQS_PKG.GET_REQ_TOTAL(PRH.REQUISITION_HEADER_ID)
, FND_CURRENCY.SAFE_GET_FORMAT_MASK(SOB.CURRENCY_CODE
, 30))
, POR_VIEW_REQS_PKG.GET_SUPPLIER_NAME(PRH.REQUISITION_HEADER_ID)
, POR_VIEW_REQS_PKG.IS_PLACEDONPO(PRH.REQUISITION_HEADER_ID)
, 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
, PER_ALL_PEOPLE_F HRE
, AP_CARDS APC
, GL_SETS_OF_BOOKS SOB
, FINANCIALS_SYSTEM_PARAMETERS FSP
, PO_REQUISITION_HEADERS PRH
WHERE 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'
AND SOB.SET_OF_BOOKS_ID = FSP.SET_OF_BOOKS_ID