0 Replies Latest reply on Dec 10, 2019 6:11 AM by AB115

    Need to optimize Cancel Order Query

    AB115

      Hello Experts,

       

      Oracle EBS version: 12.2.4

      Oracle DB version: 11.2.0.1

       

      we have a below cancel order query but to fetch 2 records, its taking 10 mins,

       

      SELECT oola_orig.flow_status_code,

             prha.segment1

                 ir_num,

             prla.line_num

                 ir_line_num,

             oola_orig.ordered_item

                 item_number,

             prla.quantity

                 ir_line_qty,

             ooha_orig.order_number

                 orig_so,

             oola_orig.line_number || '.' || oola_orig.shipment_number

                 orig_so_line,

             oola_orig.line_number

                 so_line_num,

             oola_orig.ordered_quantity

                 orig_so_line_qty,

             oola_orig.schedule_arrival_date

                 so_schdul_arr_date,

             oola_orig.schedule_ship_date

                 so_schdul_ship_date,

             oola_orig.inventory_item_id,

             ooha_orig.header_id,

             oola_orig.line_id,

             ooha_orig.cancelled_flag,

             'Internal Requisition'

                 req_type,

             prla.requisition_header_id,

             prla.requisition_line_id,

             prla.need_by_date

                 ir_need_by_dt

        FROM oe_order_headers_all        ooha_orig,

             oe_order_lines_all          oola_orig,

             po_requisition_headers_all  prha,

             po_requisition_lines_all    prla,

             hz_cust_site_uses_all       hcsua_orig,

             hz_cust_acct_sites_all      hcasa_orig,

             hz_party_sites              hps_orig,

             hz_parties                  hp_orig

      WHERE     ooha_orig.header_id = oola_orig.header_id

             AND ooha_orig.org_id = oola_orig.org_id

             AND (SUBSTR (

                      prla.note_to_agent,

                      INSTR (prla.note_to_agent, ':') + 1,

                        (INSTR (prla.note_to_agent, ','))

                      - (INSTR (prla.note_to_agent, ':'))

                      - 1)) IN

                     TO_CHAR (ooha_orig.order_number)

             AND prha.type_lookup_code = 'INTERNAL'

             AND prla.SOURCE_TYPE_CODE = 'INVENTORY'

             AND hcsua_orig.site_use_code = 'SHIP_TO'

             AND prla.requisition_header_id = prha.requisition_header_id

             AND hcsua_orig.cust_Acct_site_id = hcasa_orig.cust_acct_site_id

             AND ooha_orig.ship_to_org_id = hcsua_orig.site_use_id

             AND hcasa_orig.party_site_id = hps_orig.party_site_id

             AND hps_orig.party_id = hp_orig.party_id

             AND prla.item_id = oola_orig.inventory_item_id

             AND prla.destination_organization_id = 1180

             AND oola_orig.flow_status_code = 'CANCELLED'

             AND (    NVL (prla.cancel_flag, 'N') <> 'Y'

                  AND NVL (prla.CLOSED_CODE, 'OPEN') = 'OPEN')

             AND oola_orig.ship_from_org_id = 1180

             AND oola_orig.last_update_date >= SYSDATE - 3;

       

      Do you have any pointer or reference to optimize/ tuned this query?

       

      please suggest.

       

      Thanks