Clicking Intelligence Reports Like Awarded Bids Gives Error java.sql.SQLSyntaxErrorException: ORA-00907: Missing Right Parenthesis (Doc ID 2176212.1)

Last updated on APRIL 29, 2024

Applies to:

Symptoms

When attempting to see the Intelligence reports, the following error occurs.

ERROR
-----------------------
oracle.apps.fnd.framework.OAException: oracle.jbo.SQLStmtException: JBO-27122: SQL error during statement preparation. Statement: SELECT * FROM (SELECT
decode(ah.contract_type, 'STANDARD', sum(nvl(bi.award_price*bi.award_quantity,0)),decode(:10, 'Y', max(bh.po_agreed_amount), null)) amount,
ah.auction_header_id auction_number,
ah.auction_title auction_title,
ah.document_number auction_display_number,
bh.bid_number bid_number,
bh.trading_partner_name seller_name,
ah.currency_code currency_code,
ah.award_complete_date completed_date,
message_text outcome,
f.precision precision,
NULL as amount_to_display
from
pon_auction_headers_v ah,
pon_bid_headers bh,
pon_bid_item_prices bi,
pon_auc_doctypes ad,
fnd_new_messages fm,
fnd_application fa,
fnd_currencies f
WHERE
ah.auction_header_id = bh.auction_header_id
and bh.auction_header_id = bi.auction_header_id(+)
and bh.bid_number = bi.bid_number (+)
and ah.doctype_id = ad.doctype_id(+)
and decode(nvl(ah.has_items_flag,'N'),'Y',bi.award_status,'N',bh.award_status)='AWARDED'
and (((ad.internal_name is null) and (ah.auction_type = 'REVERSE') and (ad.internal_name <> 'REQUEST_FOR_QUOTE')) or (ad.internal_name= :1))
and fm.message_name = 'PON_MI_AUCOUTCM_'||decode(ah.contract_type,'BLANKET', 'LONG','CONTRACT','CNTR','SPOT')
AND fm.application_id = fa.application_id
and fa.application_short_name = 'PON'
AND fm.language_code = USERENV('LANG')
and decode(nvl(ah.has_items_flag,'N'),'Y',bi.auc_trading_partner_id,'N',ah.trading_partner_id) like :2
AND decode(:3,'%','%',bi.category_id) LIKE :4
AND ah.currency_code like :5
AND decode(ah.contract_type,'BLANKET', 'LONG','CONTRACT','CNTR','SPOT') like :6
--update the and condition to fix the bug 18237327
DECODE(NVL(ah.has_items_flag,'N'),'Y',DECODE(NVL(bh.surrog_bid_flag,'N'),'Y',bh.trading_partner_id,'N',bi.bid_trading_partner_id),'N',bh.trading_partner_id) like :7
AND f.currency_code = ah.currency_code
GROUP BY
ah.auction_header_id,
ah.auction_title,
ah.document_number,
bh.trading_partner_name,
ah.currency_code,
f.precision,
ah.award_complete_date,
fm.message_text,
bh.bid_number,
ah.contract_type) QRSLT WHERE (completed_date BETWEEN :8 AND :9) ORDER BY CURRENCY_CODE ASC, AMOUNT DESC

## Detail 0 ##
java.sql.SQLSyntaxErrorException: ORA-00907: missing right parenthesis


STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Go to Sourcing Super User responsibility
2. Go to Intelligence > Awards by Supplier
3. Search from 15-Jun-2015 or any date
4. Click Awarded Quotes link or any other link

Changes

Cause

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