Oracle eBS經驗分享

When I create Data Link between two sql query , I got below error message.

< Root Cause >

==Query Group : Q_DELIVERY_DETAIL_ID==

/*PICKED*/
SELECT wdd.delivery_detail_id delivery_detail_id,
       NVL (wdd.transaction_id, -99) lot_txn_id,
       wdd.lot_number lot_number,
       requested_quantity lot_qty,
       (SELECT age
          FROM mtl_lot_numbers
         WHERE organization_id = wdd.organization_id
           AND inventory_item_id = wdd.inventory_item_id
           AND lot_number = wdd.lot_Number)
          lot_age
  FROM wsh_delivery_details wdd
WHERE lot_number IS NOT NULL OR revision IS NOT NULL
UNION ALL
  /*UNPICKED*/
  SELECT -99 delivery_detail_id,
         mtlt.transaction_temp_id lot_txn_id,
         mtlt.lot_number lot_number,
         SUM (ABS (mtlt.transaction_quantity)) lot_qty,
         (SELECT age
            FROM mtl_lot_numbers
           WHERE organization_id = mmtt.organization_id
             AND inventory_item_id = mmtt.inventory_item_id
             AND lot_number = mtlt.lot_Number)
            lot_age
    FROM mtl_transaction_lots_temp mtlt, mtl_material_transactions_temp mmtt
   WHERE mmtt.transaction_temp_id = mtlt.transaction_temp_id(+)
GROUP BY mmtt.organization_id,
         mtlt.transaction_temp_id,
         mmtt.inventory_item_id,
         mtlt.lot_number

------

<Solution>

1. Remove the sub-query from child sql query.

2. Create data link again.