eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.POR_VIEW_REQS_BY_GROUP_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, ( 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

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
, ( 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