VIEW: APPS.POR_VIEW_REQS_BY_APPROVER_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, plc_cancel.displayed_field, NVL(prh.closed_code,'OPEN'), plc_closed.displayed_field, 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(prl.total, fnd_currency.safe_get_format_mask(sob.currency_code, 30)), upper(prl.supplier), prl.placed_on_po, prh.emergency_po_num FROM po_lookup_codes plc_auth, po_lookup_codes plc_closed, po_lookup_codes plc_cancel, po_lookup_codes plc_doctype, hr_employees_current_v hre, ap_cards apc, gl_sets_of_books sob, financials_system_parameters fsp, po_requisition_headers prh, po_approval_list_headers plh, po_approval_list_lines pll, fnd_user fnu, (SELECT prl2.requisition_header_id, SUM(prl2.unit_price * (prl2.quantity - nvl(prl2.quantity_cancelled, 0))) AS total, decode (count(distinct nvl(prl2.vendor_id,0)), 1, min(pv.vendor_name), 'MULTIPLE_VALUE') AS supplier, decode (min(prl2.line_location_id), NULL, 'N', 'Y') AS placed_on_po FROM po_requisition_lines prl2, po_line_types plt, po_vendors pv WHERE prl2.line_type_id = plt.line_type_id AND plt.outside_operation_flag = 'N' AND prl2.source_type_code = 'VENDOR' AND pv.vendor_id(+) = prl2.vendor_id GROUP BY prl2.requisition_header_id) prl WHERE plh.document_id = prh.requisition_header_id AND plh.document_type = 'REQUISITION' AND plh.document_subtype = 'PURCHASE' AND plh.latest_revision = 'Y' AND pll.approval_list_header_id = plh.approval_list_header_id AND fnu.user_id = FND_GLOBAL.USER_ID AND pll.approver_id = fnu.employee_id AND pll.status = 'APPROVE' AND prh.preparer_id = hre.employee_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 plc_closed.lookup_code = NVL(prh.closed_code, 'OPEN') AND plc_closed.lookup_type = 'DOCUMENT STATE' AND plc_cancel.lookup_code = NVL(prh.cancel_flag, 'N') AND plc_cancel.lookup_type = 'YES/NO' AND plc_doctype.lookup_code = prh.type_lookup_code AND plc_doctype.lookup_type = 'REQUISITION TYPE' AND prl.requisition_header_id = prh.requisition_header_id 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
, PLC_CANCEL.DISPLAYED_FIELD
, NVL(PRH.CLOSED_CODE
, 'OPEN')
, PLC_CLOSED.DISPLAYED_FIELD
, 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(PRL.TOTAL
, FND_CURRENCY.SAFE_GET_FORMAT_MASK(SOB.CURRENCY_CODE
, 30))
, UPPER(PRL.SUPPLIER)
, PRL.PLACED_ON_PO
, PRH.EMERGENCY_PO_NUM
FROM PO_LOOKUP_CODES PLC_AUTH
, PO_LOOKUP_CODES PLC_CLOSED
, PO_LOOKUP_CODES PLC_CANCEL
, PO_LOOKUP_CODES PLC_DOCTYPE
, HR_EMPLOYEES_CURRENT_V HRE
, AP_CARDS APC
, GL_SETS_OF_BOOKS SOB
, FINANCIALS_SYSTEM_PARAMETERS FSP
, PO_REQUISITION_HEADERS PRH
, PO_APPROVAL_LIST_HEADERS PLH
, PO_APPROVAL_LIST_LINES PLL
, FND_USER FNU
, (SELECT PRL2.REQUISITION_HEADER_ID
, SUM(PRL2.UNIT_PRICE * (PRL2.QUANTITY - NVL(PRL2.QUANTITY_CANCELLED
, 0))) AS TOTAL
, DECODE (COUNT(DISTINCT NVL(PRL2.VENDOR_ID
, 0))
, 1
, MIN(PV.VENDOR_NAME)
, 'MULTIPLE_VALUE') AS SUPPLIER
, DECODE (MIN(PRL2.LINE_LOCATION_ID)
, NULL
, 'N'
, 'Y') AS PLACED_ON_PO
FROM PO_REQUISITION_LINES PRL2
, PO_LINE_TYPES PLT
, PO_VENDORS PV
WHERE PRL2.LINE_TYPE_ID = PLT.LINE_TYPE_ID
AND PLT.OUTSIDE_OPERATION_FLAG = 'N'
AND PRL2.SOURCE_TYPE_CODE = 'VENDOR'
AND PV.VENDOR_ID(+) = PRL2.VENDOR_ID GROUP BY PRL2.REQUISITION_HEADER_ID) PRL
WHERE PLH.DOCUMENT_ID = PRH.REQUISITION_HEADER_ID
AND PLH.DOCUMENT_TYPE = 'REQUISITION'
AND PLH.DOCUMENT_SUBTYPE = 'PURCHASE'
AND PLH.LATEST_REVISION = 'Y'
AND PLL.APPROVAL_LIST_HEADER_ID = PLH.APPROVAL_LIST_HEADER_ID
AND FNU.USER_ID = FND_GLOBAL.USER_ID
AND PLL.APPROVER_ID = FNU.EMPLOYEE_ID
AND PLL.STATUS = 'APPROVE'
AND PRH.PREPARER_ID = HRE.EMPLOYEE_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 PLC_CLOSED.LOOKUP_CODE = NVL(PRH.CLOSED_CODE
, 'OPEN')
AND PLC_CLOSED.LOOKUP_TYPE = 'DOCUMENT STATE'
AND PLC_CANCEL.LOOKUP_CODE = NVL(PRH.CANCEL_FLAG
, 'N')
AND PLC_CANCEL.LOOKUP_TYPE = 'YES/NO'
AND PLC_DOCTYPE.LOOKUP_CODE = PRH.TYPE_LOOKUP_CODE
AND PLC_DOCTYPE.LOOKUP_TYPE = 'REQUISITION TYPE'
AND PRL.REQUISITION_HEADER_ID = PRH.REQUISITION_HEADER_ID
AND SOB.SET_OF_BOOKS_ID = FSP.SET_OF_BOOKS_ID