Trade Management - Offer Checkbook Paid Link Throwing Exception (Doc ID 2257165.1)

Last updated on JULY 25, 2017

Applies to:

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

Symptoms

On : 12.2.4  version, Budget Checkbooks

Error on Offer Paid checkbook UI - 

The following error occurred:

oracle.apps.fnd.framework.OAException: oracle.jbo.SQLStmtException: JBO-27122: SQL error during statement preparation. Statement: select pay.object_type objectType, pay.claim_id claimId, pay.claim_num claimNum, pay.claim_date claimDate, DECODE(pay.doc_type, 'INVOICE','INVOICE', 'ORDER','ORDER') docType, '' docTypeMeaning, pay.doc_id docId, DECODE(pay.doc_type, 'INVOICE', (SELECT n.TRX_NUMBER FROM AR_PAYMENT_SCHEDULES n , RA_CUST_TRX_TYPES t WHERE n.customer_trx_id = pay.doc_id AND t.TYPE = 'INV' AND n.CUST_TRX_TYPE_ID = t.CUST_TRX_TYPE_ID ), 'ORDER', (SELECT TO_CHAR(so.order_number) FROM oe_order_headers_all so WHERE so.header_id = pay.doc_id ), 'TP_ORDER', (SELECT TO_CHAR(cbheader.order_number) FROM ozf_resale_headers_all cbheader, ozf_resale_lines_all cbline WHERE cbheader.resale_header_id = cbline.resale_header_id AND cbline.resale_line_id = pay.doc_id ), pay.doc_id) docNum, DECODE(util.object_type, 'TP_ORDER', (select rsb.batch_number from ozf_resale_batches_all rsb, OZF_RESALE_BATCH_LINE_MAPS_ALL rsbm where rsb.resale_batch_id = rsbm.resale_batch_id and rsbm.resale_line_id = util.object_id)) batchNum, DECODE(pay.doc_type, 'ORDER', (select line_number from oe_order_lines_all where line_id = util.order_line_id), 'TP_ORDER', (select order_line_number from ozf_resale_lines_all where resale_line_id = pay.doc_id))lineNum, util.cust_account_id custAcctId, pay.cust_name beneficiaryName, pay.product_level_type, pay.product_id, OZF_Utility_PVT.get_product_name(pay.product_level_type, pay.product_id, -1) productName, DECODE(pay.doc_type, 'INVOICE', (SELECT n.TRX_DATE FROM AR_PAYMENT_SCHEDULES n , RA_CUST_TRX_TYPES t WHERE n.customer_trx_id = pay.doc_id AND t.TYPE = 'INV' AND n.CUST_TRX_TYPE_ID = t.CUST_TRX_TYPE_ID ), 'ORDER', (SELECT TO_CHAR(so.ordered_date) FROM oe_order_headers_all so WHERE so.header_id = pay.doc_id )) docDate, pay.payment_method payTerm, '' payTermMeaning, (select settlement_number from ozf_settlement_docs_all where claim_id = pay.claim_id) paymentDetail, util.billto_cust_account_id billToCustId, (SELECT hz.party_name FROM hz_parties hz, hz_cust_accounts hza WHERE hza.party_id = hz.party_id AND hza.cust_account_id =util.billto_cust_account_id) custName, util.utilization_type utilType, '' utilTypeMeaning, util.adjustment_type_id adjustmentTypeId, '' adjustmentTypeMeaning, util.adjustment_date adjustmentDate, pay.amount amount, '' transCurrency, pay.amount_in_budget budgetCurrAmt, pay.fund_currency fundCurrency, util.order_line_id, DECODE(util.object_type, 'ORDER', (select DELIVERY_NAME from IBE_SHIP_DETAIL_V where DELIVERY_NAME is not null and rownum=1 and line_id=util.order_line_id)) shipmentNum, DECODE(util.object_type, 'ORDER', (select ACTUAL_SHIPMENT_DATE from oe_order_lines_all where line_id=order_line_id)) shipmentDate, (SELECT RCT.TRX_NUMBER FROM RA_CUSTOMER_TRX_ALL RCT, RA_CUSTOMER_TRX_LINES_ALL RCTL, oe_order_headers_all oh WHERE RCTL.CUSTOMER_TRX_ID = RCT.CUSTOMER_TRX_ID AND RCTL.interface_line_attribute1 = TO_CHAR(oh.order_number) AND RCTL.LINE_TYPE ='LINE' AND ROWNUM = 1 AND rctl.INTERFACE_LINE_ATTRIBUTE6 = TO_CHAR(util.order_line_id) AND rctl.interface_line_context = FND_PROFILE.value('ONT_SOURCE_CODE') AND oh.org_id = util.org_id ) invoiceNum, DECODE(util.object_type, 'ORDER', (SELECT RCT.TRX_DATE FROM RA_CUSTOMER_TRX_ALL RCT, RA_CUSTOMER_TRX_LINES_ALL RCTL where RCTL.CUSTOMER_TRX_ID = RCT.CUSTOMER_TRX_ID AND RCTL.LINE_TYPE ='LINE' AND ROWNUM = 1 and rctl.INTERFACE_LINE_ATTRIBUTE6 = to_char(util.order_line_id))) invoiceDate, (SELECT COUNT(*) FROM ozf_acctng_events_all WHERE source_table = NVL2(pay.claim_id,'OZF_CLAIMS_ALL','OZF_FUNDS_UTILIZED_ALL_B') AND source_id = NVL2(pay.claim_id,pay.claim_id,pay.utilization_id) ) displayOldGLDetails, pay.utilization_id utilId, pay.claim_line_util_id claimLineUtilId, util.ATTRIBUTE_CATEGORY, util.ATTRIBUTE1, util.ATTRIBUTE2, util.ATTRIBUTE3, util.ATTRIBUTE4, util.ATTRIBUTE5, util.ATTRIBUTE6, util.ATTRIBUTE7, util.ATTRIBUTE8, util.ATTRIBUTE9, util.ATTRIBUTE10, util.ATTRIBUTE11, util.ATTRIBUTE12, util.ATTRIBUTE13, util.ATTRIBUTE14, util.ATTRIBUTE15, util.CREATED_FROM createdFrom FROM ozf_funds_paid_v pay , OZF_FUNDS_ALL_B FUND, ozf_funds_utilized_all_b util, ozf_offers offr WHERE ((pay.plan_id = :1 and PAY.PLAN_TYPE ='OFFR')) AND util.plan_id = offr.qp_list_header_id AND pay.fund_id = fund.fund_id AND util.utilization_id = pay.utilization_id AND NVL(pay.gl_posted_flag,'Y') = 'Y' ORDER BY custAcctId, docNum, utilId

 

Changes

 

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