Query for Daily Credit Card Orders For Customer Details

Daily Credit Card Orders For Customer  Details

SELECT  ooha.order_number order#,
        ooha.ordered_date,
        oola.line_number line#,
        msib.segment1 item#,
        oola.ordered_quantity,
        oola.order_quantity_uom,
        oola.unit_selling_price price,       
        (oola.ordered_quantity * oola.unit_list_price_per_pqty) total,
        hca.account_name customer_name,
        hca.account_number customer_number,            hl.address1||','||hl.address2||','||hl.address3||','||hl.address4||','||hl.city||','||hl.state||','||hl.country||','||hl.postal_code bill_to       
FROM    oe_order_headers_all ooha
        ,oe_order_lines_all oola
        ,mtl_system_items_b msib
        ,hz_cust_accounts hca
        ,hz_locations hl
        ,apps.hz_cust_acct_sites_all    hcasa
        ,apps.hz_cust_site_uses_all     hcsua
        ,apps.hz_party_sites            hps
WHERE   1=1
AND     ooha.header_id = oola.header_id
AND     oola.inventory_item_id = msib.inventory_item_id
AND     oola.ship_from_org_id = msib.organization_id
AND     ooha.SOLD_TO_ORG_ID = hca.cust_account_id
AND     ooha.invoice_to_org_id = hcsua.site_use_id
AND     hcsua.cust_acct_site_id = hcasa.cust_acct_site_id
AND     hcasa.party_site_id = hps.party_site_id
AND     hps.location_id = hl.location_id
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 nvl(:p_order_from,order_number) and nvl(:p_order_to,order_number)
ORDER BY ooha.order_number
--AND     ooha.order_number='573713'