View: POR_VIEW_REQS_BY_GROUP_V

Product:	ICX - Oracle iProcurement
Description:	View Requisition By Group
Implementation/DBA Data:	APPS.POR_VIEW_REQS_BY_GROUP_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
, 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
, ( 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_HEADERS PRH2
, PO_REQUISITION_LINES PRL2
, PO_LINE_TYPES PLT
, PO_VENDORS PV
, PO_EMPLOYEE_HIERARCHIES PEH1
, PO_SYSTEM_PARAMETERS PSP1
, FND_USER FNU1
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
AND PRH2.REQUISITION_HEADER_ID = PRL2.REQUISITION_HEADER_ID
AND FNU1.USER_ID = FND_GLOBAL.USER_ID
AND PEH1.SUPERIOR_ID = FNU1.EMPLOYEE_ID
AND PSP1.SECURITY_POSITION_STRUCTURE_ID = PEH1.POSITION_STRUCTURE_ID
AND PRL2.TO_PERSON_ID = PEH1.EMPLOYEE_ID
AND PRH2.PREPARER_ID != PEH1.EMPLOYEE_ID GROUP BY PRL2.REQUISITION_HEADER_ID UNION ALL 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_HEADERS PRH2
, PO_REQUISITION_LINES PRL2
, PO_LINE_TYPES PLT
, PO_VENDORS PV
, PO_EMPLOYEE_HIERARCHIES PEH1
, PO_SYSTEM_PARAMETERS PSP1
, FND_USER FNU1
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
AND PRH2.REQUISITION_HEADER_ID = PRL2.REQUISITION_HEADER_ID
AND FNU1.USER_ID = FND_GLOBAL.USER_ID
AND PEH1.SUPERIOR_ID = FNU1.EMPLOYEE_ID
AND PSP1.SECURITY_POSITION_STRUCTURE_ID = PEH1.POSITION_STRUCTURE_ID
AND PRH2.PREPARER_ID = PEH1.EMPLOYEE_ID GROUP BY PRL2.REQUISITION_HEADER_ID ) PRL
WHERE 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

Columns