Query for Credit Card Orders Which Are in Hold Status

Query for Credit Card Orders Which Are in Hold status

SELECT  ooha.order_number,
        --ooha.credit_card_approval_code, These fields are blanks in order headers
        --ooha.credit_card_number,
        ooha.ordered_date,
        NVL(ooha.payment_amount,0),
        ooha.flow_status_code,
        ic.card_issuer_code,
        ic.masked_cc_number,
        ohd.name hold_status,
        NVL((itc.authcode),'No Authorization code') authorization_code--Credit card authorization code
FROM    oe_order_headers_all ooha,
        oe_order_lines_all oola,        
        oe_order_holds_all holds,
        oe_hold_sources_all ohsa,
        oe_hold_definitions ohd,
        iby_trxn_summaries_all itsa,
        iby_trxn_core itc,
        iby_creditcard ic
WHERE   1=1
AND     ooha.header_id = oola.header_id
AND     holds.header_id = oola.header_id(+)
AND     holds.hold_source_id = ohsa.hold_source_id
AND     ohsa.hold_id = ohd.hold_id
AND     holds.header_id = ooha.header_id
AND     ooha.sold_to_org_id = itsa.cust_account_id
AND     oola.sold_to_org_id = itsa.cust_account_id
AND     itsa.trxnmid = itc.trxnmid
AND     itsa.payerinstrid = ic.instrid
and     trunc(ooha.ordered_date) between TO_DATE(:p_from_date, 'yyyy/mm/dd hh24:mi:ss') AND TO_DATE(:p_to_date, 'yyyy/mm/dd hh24:mi:ss')
AND     ooha.order_number between :from_order and :to_order
--and ooha.order_number='573742'
GROUP BY ooha.order_number,
        --ooha.credit_card_approval_code,
        --ooha.credit_card_number,
        ooha.ordered_date,        
        ooha.payment_amount,
        ooha.flow_status_code,
        ic.card_issuer_code,
        ic.masked_cc_number,
        ohd.name ,
        itc.authcode
ORDER BY ooha.order_number