Scan Data Offer In Trade Mgmt - Creating Error On Settlement Tab

(Doc ID 2341680.1)

Last updated on DECEMBER 31, 2017

Applies to:

Oracle Trade Management - Version 12.2.6 and later
Information in this document applies to any platform.

Symptoms

Trade Management > Trade Planning > Offers,

Create a manual claim, settle claim with Scan Data offer and request
approval. Claim is now in pending close status. When clicking on the
Settlement tab on claim screen the following error is seen. This is
happening to several claims. Unable to pinpoint the cause.

Error:
----------
oracle.apps.fnd.framework.OAException: oracle.jbo.SQLStmtException:
JBO-27122: SQL error during statement preparation. Statement: SELECT 'F'
selectFlag, utiz.actId actId, lk.meaning actTypeName, utiz.actType actType,
utiz.actOfferType actOfferType, utiz.offerCustomSetupId
offerCustomSetupId,(CASE (utiz.actType) WHEN 'OFFR' THEN (select
offer.offer_code || ' (' || qp.description || ')' from ozf_offers offer,
qp_list_headers_vl qp where qp.list_header_id = offer.qp_list_header_id and
offer.qp_list_header_id = utiz.actId) WHEN 'PRIC' THEN (select
qp.description from qp_list_headers_vl qp where qp.list_header_id =
utiz.actId) WHEN 'CAMP' THEN (select campaign_name from ams_campaigns_v
where campaign_id = utiz.actId) ELSE NULL END ) AS actCode, NULL
docTypeName, NULL docType, NULL documentId, NULL docNum, NULL docDate, NULL
itemTypeName, NULL itemType, NULL itemId, NULL itemName, NULL period,
curr.name planCurrency, SUM(utiz.plan_curr_amount) planCurAmount,
SUM(utiz.plan_curr_amount_remaining) planCurAmRem, SUM(utiz.acctd_amount)
acctdAmount, SUM(utiz.acctd_amount_remaining) acctdAmRem,'US Dollar'
acctdCurrency, utiz.custAcctId custAcctId, (SELECT party_name FROM hz_parties
hp, hz_cust_accounts hca WHERE hp.party_id = hca.party_id AND
hca.cust_account_id = utiz.custAcctId) custName, utiz.plan_currency_code
currencyCode, sum(asso_amt) asso_amt FROM(SELECT 'OFFR' actType , fu.plan_id
actId , offer.offer_type actOfferType , offer.custom_setup_id
offerCustomSetupId , fu.cust_account_id custAcctId , fu.product_level_type ,
fu.product_id , fu.object_type object_type , fu.object_id object_id ,
fu.plan_curr_amount plan_curr_amount , fu.plan_curr_amount_remaining
plan_curr_amount_remaining , fu.acctd_amount acctd_amount ,
fu.acctd_amount_remaining acctd_amount_remaining , fu.plan_currency_code
plan_currency_code , fu.gl_date ,(select sum(clu.amount) from
ozf_claim_lines_util_all clu, ozf_claim_lines_all cl where clu.utilization_id
= fu.utilization_id and clu.claim_line_id = cl.claim_line_id and cl.claim_id
= :1) asso_amt FROM ozf_funds_utilized_all_b fu , ozf_offers offer ,
qp_list_headers_b qp WHERE fu.plan_type = 'OFFR' AND fu.plan_id =
offer.qp_list_header_id AND fu.org_id = :2 AND offer.qp_list_header_id =
qp.list_header_id AND offer.offer_type <> 'SCAN_DATA' AND
(fu.reference_type IS NULL OR fu.reference_type NOT IN ('SOFT_FUND',
'SPECIAL_PRICE')) AND ( (fu.acctd_amount_remaining <> 0 AND ( select /*+
push_subq no_unnest */ sum(fuu.acctd_amount_remaining) from
ozf_funds_utilized_all_b fuu WHERE fuu.plan_id = fu.plan_id and
fuu.cust_account_id = fu.cust_account_id AND fuu.org_id = :3 AND
fuu.utilization_type IN ('ACCRUAL', 'ADJUSTMENT') AND fuu.year_id = (SELECT
ent_year_id FROM OZF_TIME_ENT_YEAR WHERE sysdate between start_date and
end_date) AND fuu.gl_posted_flag = 'Y' ) <> 0 ) OR (
NVL(qp.end_date_active, trunc(sysdate)) >= trunc(sysdate) AND
offer.user_status_id NOT IN (1607,1608,1609) )OR fu.utilization_id IN (SELECT
utilization_id FROM ozf_claim_lines_util where claim_line_id in (select
claim_line_id from ozf_claim_lines_all where claim_id = : 4))) AND
fu.utilization_type IN ('ACCRUAL', 'ADJUSTMENT') AND fu.cust_account_id = :5
AND fu.year_id = (SELECT ent_year_id FROM OZF_TIME_ENT_YEAR WHERE sysdate
between start_date and end_date) AND fu.gl_posted_flag = 'Y' UNION ALL
SELECT fu.plan_type actType , fu.plan_id actId , null actOfferType ,
to_number(null) offerCustomSetupId , fu.cust_account_id custAcctId ,
fu.product_level_type , fu.product_id , fu.object_type object_type ,
fu.object_id object_id , fu.plan_curr_amount plan_curr_amount ,
fu.plan_curr_amount_remaining plan_curr_amount_remaining , fu.acctd_amount
acctd_amount , fu.acctd_amount_remaining acctd_amount_remaining ,
fu.plan_currency_code plan_currency_code , fu.gl_date ,(select
sum(clu.amount) from ozf_claim_lines_util_all clu, ozf_claim_lines_all cl
where clu.utilization_id = fu.utilization_id and clu.claim_line_id =
cl.claim_line_id and cl.claim_id = :6) asso_amt FROM ozf_funds_utilized_all_b
fu WHERE fu.plan_type = 'PRIC' AND fu.org_id = :7 AND
(fu.acctd_amount_remaining <> 0 OR fu.utilization_id IN (SELECT
utilization_id FROM ozf_claim_lines_util where claim_line_id in (select
claim_line_id from ozf_claim_lines_all where claim_id = :8 ))) AND
fu.utilization_type IN ('ACCRUAL', 'ADJUSTMENT') AND fu.cust_account_id = :9
AND fu.year_id = (SELECT ent_year_id FROM OZF_TIME_ENT_YEAR WHERE sysdate
between start_date and end_date) AND fu.gl_posted_flag = 'Y' ) utiz ,
fnd_currencies_vl curr , ozf_lookups lk WHERE (utiz.plan_currency_code =
curr.currency_code AND utiz.actType = lk.lookup_code AND lk.lookup_type =
'OZF_CLAIM_ASSO_ACT_TYPE') GROUP BY utiz.actType, utiz.actId,
utiz.actOfferType, utiz.offerCustomSetupId, lk.meaning, curr.name,
utiz.custAcctId , utiz.plan_currency_code, utiz.custAcctId



Cause

Sign In with your My Oracle Support account

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

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms