4 Replies Latest reply on Oct 28, 2015 7:13 PM by User910243567

    Performance issue in ra_customer_trx_all join

    1567114

      Hello,

       

      The Query below is fetching duplicate data.

       

      Kindly assist me if there are any joins missed out.

       

      select

        l.line_number "ITEM",

        b.SEGMENT1 ITEM_NO,

        b.DESCRIPTION "ITEM_DESC",

        (

        SELECT

           -1*ORDERED_QUANTITY

        FROM

           OE_ORDER_LINES_ALL

        WHERE

           LINE_ID=L.LINE_ID and line_category_code='RETURN') "RETURNED",

        (l.ORDERED_QUANTITY) "ORDERED",

        l.SHIPPED_QUANTITY "SHIPPED",

        l.ORDER_QUANTITY_UOM "UNIT",

        NVL((

        SELECT

          TAX_AMT

        FROM

         ZX_LINES

        WHERE

          TRX_LINE_ID=rct_lines.customer_trx_line_id),0) "TAX_PRICE",

      l.UNIT_SELLING_PRICE "UNIT_PRICE",

      1*(l.ORDERED_QUANTITY * l.UNIT_SELLING_PRICE) "EXT_PRICE"

      from

        ra_customer_trx_all rct,

        ra_customer_trx_lines_all rct_lines,

        oe_order_headers_all h,

        OE_ORDER_LINES_ALL l,

        MTL_SYSTEM_ITEMS_B b

      where

          rct.customer_trx_id   = rct_lines.customer_trx_id

      AND to_char(h.ORDER_NUMBER)= rct.INTERFACE_HEADER_ATTRIBUTE1 

      AND to_char(l.LINE_ID)= rct_lines.INTERFACE_LINE_ATTRIBUTE6

      AND l.INVENTORY_ITEM_ID  = b.INVENTORY_ITEM_ID

      AND h.header_id=l.header_id

      AND h.org_id=rct.org_id

      AND l.org_id=rct_lines.org_id

      AND to_char(rct.TRX_NUMBER) BETWEEN   :P_CM_NUMBER AND :P_CM_NUMBER_TO