My Oracle Support Banner

With 11g Database Open Requester Notification Gets Error JBO-27122 Or ORA-01417 (Doc ID 2685043.1)

Last updated on JULY 03, 2020

Applies to:

Oracle iProcurement - Version 12.2.7 and later
Information in this document applies to any platform.

Symptoms

On : 12.2.7 version, Approval List and Workflow

ACTUAL BEHAVIOR
---------------
Worklist

Open PO Notification, Gets Error:

Exception Details.
oracle.apps.fnd.framework.OAException: oracle.jbo.SQLStmtException: JBO-27122: SQL error during
statement preparation. Statement: SELECT prl.requisition_header_id,
prl.requisition_line_id,
prl.line_num AS LINE,
msi.concatenated_segments AS ITEM_NUMBER,
prl.item_revision,
prl.item_description AS DESCRIPTION,
por_apprv_wf_util_grp.get_cost_center(prl.requisition_line_id) AS COST_CENTER,
DECODE(NVL(prl.order_type_lookup_code, 'QUANTITY'),
'AMOUNT',
NULL,
NVL(muom.unit_of_measure_tl, prl.unit_meas_lookup_code)) AS UOM,
DECODE(NVL(prl.order_type_lookup_code, 'QUANTITY'),
'AMOUNT',
TO_NUMBER(NULL),
prl.quantity) AS QUANTITY,
DECODE(NVL(prl.order_type_lookup_code, 'QUANTITY'),
'AMOUNT',
TO_NUMBER(NULL),
prl.unit_price) AS UNIT_PRICE,
DECODE(NVL(prl.order_type_lookup_code, 'QUANTITY'),
'FIXED PRICE',
prl.amount,
'RATE',
prl.amount,
prl.quantity * prl.unit_price) AS LINE_AMOUNT,
DECODE(NVL(prl.order_type_lookup_code, 'QUANTITY'),
'AMOUNT',
NULL,
NVL(prl.currency_code, :1)) AS TXN_CURRENCY_CODE,
DECODE(NVL(prl.order_type_lookup_code, 'QUANTITY'),
'AMOUNT',
TO_NUMBER(NULL),
NVL(prl.currency_unit_price, prl.unit_price)) AS CURRENCY_UNIT_PRICE,
DECODE(NVL(prl.order_type_lookup_code, 'QUANTITY'),
'FIXED PRICE',
NVL(prl.currency_amount, prl.amount),
'RATE',
NVL(prl.currency_amount, prl.amount),
prl.quantity * NVL(prl.currency_unit_price, prl.unit_price)) AS CURRENCY_LINE_AMOUNT,
DECODE(NVL(prl.order_type_lookup_code, 'QUANTITY'),
'FIXED PRICE',
0,
DECODE(prl.currency_unit_price, NULL, 0, 1)) AS CURR_UNIT_PRICE_DISPLAY_FLAG,
DECODE(NVL(prl.order_type_lookup_code, 'QUANTITY'),
'FIXED PRICE',
DECODE(prl.currency_amount, NULL, 0, 1),
DECODE(prl.currency_unit_price, NULL, 0, 1)) AS CURR_AMOUNT_DISPLAY_FLAG,
DECODE(poll.po_release_id,NULL,poh.segment1,poh.segment1||'-'||por.release_num) AS
ORDER_NUMBER,
pol.po_line_id AS PO_LINE_ID,
kfv.concatenated_segments AS CATEGORY,
pov.vendor_name AS SUPPLIER,
DECODE(poll.po_release_id,NULL,poh.authorization_status,por.authorization_status) AS
ORDER_STATUS
FROM po_requisition_lines_all prl,
po_requisition_lines_all base,
po_requisition_headers_all prh,
po_headers_all poh,
po_lines_all pol,
po_line_locations_all poll,
mtl_system_items_kfv msi,
per_all_people_f per,
mtl_units_of_measure muom,
org_organization_definitions org,
fnd_currencies FC,
mtl_categories_b_kfv kfv,
po_vendors pov,
po_releases_all por
WHERE prl.requisition_header_id = :2
AND prl.requisition_header_id = prh.requisition_header_id
AND NVL(prl.cancel_flag, 'N') = 'N'
AND NVL(prl.modified_by_agent_flag, 'N') = 'N'
AND NVL(prl.closed_code, 'OPEN') != 'FINALLY CLOSED'
AND prl.item_id = msi.inventory_item_id(+)
AND NVL(prl.destination_organization_id, '-99') =
msi.organization_id(+)
AND prl.to_person_id = :3
AND prl.to_person_id = per.person_id(+)
AND per.effective_start_date(+) <= TRUNC(sysdate)
AND per.effective_end_date(+) >= TRUNC(sysdate)
AND prl.source_organization_id = org.organization_id(+)
AND muom.unit_of_measure(+) = prl.unit_meas_lookup_code
AND fc.currency_code = :4
AND (prh.revision_num IS NULL OR
(prl.AMENDMENT_TYPE IN ('CHANGED', 'ADDED', 'CANCELLED')))
AND prl.clm_base_line_num = base.requisition_line_id(+)
AND poll.line_location_id(+) = prl.line_location_id
AND poll.po_header_id = poh.po_header_id(+)
AND poll.po_line_id = pol.po_line_id(+)
AND poll.po_release_id = por.po_release_id(+)
AND poh.po_header_id = por.po_header_id(+)
AND kfv.category_id(+) = pol.category_id
AND pov.vendor_id(+) = poh.vendor_id
ORDER BY prl.line_num ASC
at oracle.apps.fnd.framework.OAException.wrapperException(OAException.java:912)
at oracle.apps.fnd.framework.OAException.wrapperException(OAException.java:886)
at oracle.apps.fnd.framework.OAException.wrapperInvocationTargetException(OAException.java:1009)
at oracle.apps.fnd.framework.server.OAUtility.invokeMethod(Unknown Source)
at oracle.apps.fnd.framework.server.OAUtility.invokeMethod(Unknown Source)
at oracle.apps.fnd.framework.server.OAApplicationModuleImpl.invokeMethod(OAApplicationModuleImpl.java:783)
at oracle.apps.po.notifications.webui.PONotifRqstrLineDetailsCO.processRequest(PONotifRqstrLineDetailsCO.java:77)
at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequest(OAWebBeanHelper.java:645)
at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processRequest(OAWebBeanContainerHelper.java:283)
at oracle.apps.fnd.framework.webui.beans.layout.OAStackLayoutBean.processRequest(Unknown Source)
at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequestChildren(OAWebBeanHelper.java:1073)
at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequestChildren(OAWebBeanHelper.java:1040)
at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequest(OAWebBeanHelper.java:704)
at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processRequest(OAWebBeanContainerHelper.java:283)
at oracle.apps.fnd.framework.webui.beans.layout.OAStackLayoutBean.processRequest(Unknown Source)
at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequestChildren(OAWebBeanHelper.java:1073)

 

sqlplus apps/apps

Run the above select statement, gets:

ORA-01417: a table may be outer joined to at most one other table
01417. 00000 - "a table may be outer joined to at most one other table"
*Cause: a.b (+) = b.b and a.c (+) = c.c is not allowed
*Action: Check that this is really what you want, then join b and c first
in a view.
Error at Line: 88 Column: 29

 

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.