Error Oracle.apps.fnd.framework.OAException - While Accessing Offers In The Channel Revenue Management After Upgrade To 12.2.10 (Doc ID 2918813.1)

Last updated on JUNE 24, 2024

Applies to:

Symptoms

On : 12.2.10 version, Offers

Error while accessing offers in the Channel revenue management after upgrade to 12.2.10



ERROR
-----------------------

oracle.apps.fnd.framework.OAException: oracle.jbo.SQLStmtException: JBO-27122: SQL error during statement preparation. Statement: SELECT * FROM (SELECT qpl.name offerName, off.user_status_id userStatusId, off.transaction_currency_code currencyCode, 'AMS_SCENARIO_PTL_ACCR' defaultScenarioCode, 'OFFR' reportObjType, 'OA' offerPageType, 'Y' accessFlag, NVL(budget_offer_yn,'N') budgetOffer, off.budget_source_type budgetSourceType, 'N' modifiable, off.status_code statusCode, 'Y' budgetSourcable, off.budget_amount_tc budgetAmountTc, act.EDIT_METRICS_YN editMetrics, qpl.source_system_code sourceSystemCode , DECODE(off.offer_type,'VOLUME_OFFER',ozf_offer_pvt.get_offer_discount_id(off.offer_id),-1) offerDiscountLineId, 'N' multipleBdgtSourced, NULL sourceBudgetId , 'OFFR' actTypeCode , NULL costType, NULL objectId, (SELECT name FROM fnd_currencies_vl WHERE currency_code = off.transaction_currency_code) currencyDesc, (SELECT SUM(NVL(plan_curr_recal_committed_amt,0)) FROM ozf_object_fund_summary WHERE object_id = off.qp_list_header_id AND object_type = 'OFFR') recalCommitted , off.qp_list_header_id qpListHeaderId , off.org_id orgId, off.owner_id ownerId , qpl.start_date_active startDateActiveFirst , (SELECT SUM(NVL(plan_curr_earned_amt,0)) FROM ozf_object_fund_summary WHERE object_id = off.qp_list_header_id AND object_type = 'OFFR') earned , (SELECT meaning FROM ozf_lookups WHERE lookup_type = UPPER ('OZF_OFFER_STATUS') AND lookup_code = UPPER (Off.Status_Code)) statusName, off.object_version_number objectVersionNumber, (SELECT meaning FROM ozf_lookups WHERE lookup_type = UPPER ('OZF_OFFER_TYPE') AND lookup_code = UPPER (off.offer_type)) offerTypeName, off.qp_list_header_id objId, off.custom_setup_id customSetupId, qpl.end_date_active endDateActiveFirst, (SELECT name FROM hr_all_organization_units_tl WHERE organization_id = off.org_id AND language = userenv('LANG')) orgName, off.offer_code offerCode, qpl.description description, qpl.ask_for_flag askFor, off.reusable reusableFlag, (SELECT SUM(NVL(plan_curr_committed_amt,0)) FROM ozf_object_fund_summary WHERE object_id = off.qp_list_header_id AND object_type = 'OFFR') committed, qpl.ask_for_flag askForFlag, off.offer_type offerType, (SELECT SUM(NVL(plan_curr_paid_amt,0)) FROM ozf_object_fund_summary WHERE object_id = off.qp_list_header_id AND object_type = 'OFFR') paid, (SELECT SUM(NVL(plan_curr_utilized_amt,0)) FROM ozf_object_fund_summary WHERE object_id = off.qp_list_header_id AND object_type = 'OFFR') utilized, off.confidential_flag confFlag, act.resource_id resID, Qualifier_Type qualifierType, qualifier_id qualifierID, qpl.GLOBAL_FLAG globalFlag, off.FUND_REQUEST_CURR_CODE budgetReqCurr,& #40;select qpq.QUALIFIER_ATTR_VALUE from qp_qualifiers qpq where qpq.QUALIFIER_CONTEXT = 'SOLD_BY' AND qpq.QUALIFIER_ATTRIBUTE = 'QUALIFIER_ATTRIBUTE1' AND qpq.LIST_HEADER_ID = off.qp_list_header_id) salesMethFlag, qpl.context, qpl.attribute1, qpl.attribute2, qpl.attribute3, qpl.attribute4, qpl.attribute5, qpl.attribute6, qpl.attribute7, qpl.attribute8, qpl.attribute9, qpl.attribute10, qpl.attribute11, qpl.attribute12, qpl.attribute13, qpl.attribute14, qpl.attribute15, (SELECT source_name FROM JTF_RS_ACTIVE_RESOURCES_VL where resource_id = off.owner_id) ownerName, (SELECT meaning from OZF_LOOKUPS WHERE LOOKUP_TYPE='OZF_SALES_METHOD' and lookup_code=(select qpq.QUALIFIER_ATTR_VALUE from qp_qualifiers qpq where qpq.QUALIFIER_CONTEXT = 'SOLD_BY' AND qpq.QUALIFIER_ATTRIBUTE = 'QUALIFIER_ATTRIBUTE1' AND qpq.LIST_HEADER_ID = off.qp_list_header_id)) salesMethName,& #39;BILL_TO' billto FROM ozf_offers OFF , qp_list_headers_vl qpl , ams_act_access_denorm act WHERE off.qp_list_header_id = qpl.list_header_id AND act.object_type(+) = 'OFFR' AND act.object_id(+) = off.qp_list_header_id AND act.resource_id(+) = ozf_utility_pvt.get_resource_id (fnd_global.user_id)) QRSLT ORDER BY startDateActiveFirst DESC,UPPER(offerTypeName) ASC



STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. (R)Oracle Channel Revenue Management-->(N)Trade Planning
2. Query a Offer

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.