Query for Credit Card Orders Which Are in Hold Status

Query for Credit Card Orders Which Are in Hold Status, AskHareesh.blogspot.com

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


*/