My Oracle Support Banner


Last updated on AUGUST 29, 2021

Applies to:

Oracle Order Management - Version 12.1.3 and later
Information in this document applies to any platform.


Package OE_PREPAYMENT_UTIL, in the last UNION, no WHERE clause for table 'IBY_PMT_INSTR_USES_ALL U’

The issue is cursor ppp_order_hold_cur in OE_PREPAYMENT_UTIL.PendProcessPayments_Conc_Prog. This query
contains several UNIONed SQL statements. The final query of the union is:

-- get all orders that have at least one line being on line level
-- authorization holds for multiple payments
SELECT distinct /* MOAC_SQL_CHANGE */ oh.header_id
,oh.org_id --bug4689411
FROM oe_order_lines_all ol --moac
,oe_order_headers oh
,oe_order_holds_all hd --moac
,oe_hold_sources_all hs --moac
,oe_payments op
-- ,iby_trxn_extensions_v ite -- ccencryption
WHERE oh.header_id = ol.header_id
AND ol.line_id= hd.line_id
AND hd.hold_source_id = hs.hold_source_id
AND hs.hold_id in (l_hold1, l_hold2, l_hold3, l_hold4)
/* (SELECT hold_id -- replace the sql with hardcoded hold_id once it is seeded: Also based on what holds should be processed
FROM oe_hold_definitions
WHERE type_code='EPAYMENT') */
AND hs.hold_entity_code = 'O'
AND hs.released_flag = 'N'
AND oh.order_type_id = NVL(p_order_type_id, oh.order_type_id)
AND oh.org_id = hs.org_id --moac
AND hs.org_id = hd.org_id --moac
AND oh.sold_to_org_id IN (SELECT cust_account_id
FROM hz_cust_accounts
WHERE account_number BETWEEN NVL(p_customer_number_from, account_number)
AND NVL(p_customer_number_to, account_number)
AND nvl(CUSTOMER_CLASS_CODE, 'XXX') = NVL(p_customer_class_code, nvl(CUSTOMER_CLASS_CODE, 'XXX')))
AND oh.order_number BETWEEN NVL(p_order_number_from, oh.order_number)
AND NVL(p_order_number_to, oh.order_number)
AND ol.line_id = op.line_id
AND ol.header_id = op.header_id
AND op.payment_type_code = 'CREDIT_CARD'
AND to_char(ol.header_id) = x.order_id -- jga
AND ol.line_id = x.trxn_ref_number1 --order line_id
AND c.cc_number_hash1 = DECODE(p_credit_card_number, null, c.cc_number_hash1, iby_fndcpt_setup_pub.get_hash(p_Credit_Card_Number, 'F'))
AND c.cc_number_hash2 = DECODE(p_credit_card_number, null, c.cc_number_hash2, iby_fndcpt_setup_pub.get_hash(p_credit_card_number, 'T'))
AND nvl(c.card_issuer_code, 'XXX') = nvl(p_credit_card_type,
nvl(c.card_issuer_code, 'XXX'))
AND oh.invoice_to_org_id = NVL(p_bill_to_org_id, oh.invoice_to_org_id)
AND oh.booked_date >= NVL(l_booked_date_since, oh.booked_date)
AND oh.order_category_code 'RETURN'
AND op.trxn_extension_id = x.trxn_extension_id)
ORDER BY 7; --bug4689411 Using the column number to order by org_id. Please make sure that org_id is the 7th column when any changes are made to the select clause.

If you look closely at the code, the second-to-last table in the "from" list is IBY_PMT_INSTR_USES_ALL (table alias is 'u').
This table is NOT used anywhere in the query, either by name or by alias. Thus, it will (and does) cause a cartesian product
when it executes. This cartesian product is causing our system problems (consuming undo, etc).


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

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