My Oracle Support Banner

No Where Clause For IBY_PMT_INSTR_USES_ALL In OE_PREPAYMENT_UTIL (Doc ID 1584139.1)

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.

Goal

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
UNION
SELECT distinct /* MOAC_SQL_CHANGE */ oh.header_id
,oh.orig_sys_document_ref
,oh.source_document_id
,oh.order_source_id
,oh.change_sequence
,oh.source_document_type_id
,oh.org_id --bug4689411 https://bug.company.com/pls/bug/webbug_edit.edit_info_top?rptno=4689411
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
,IBY_FNDCPT_TX_EXTENSIONS x
,IBY_PMT_INSTR_USES_ALL u
,IBY_CREDITCARD c
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 https://bug.company.com/pls/bug/webbug_edit.edit_info_top?rptno=4689411 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).

Solution

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
Goal
Solution
References


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