[Home] [Help]
303: PROCEDURE get_mo_sohdr(x_mo_sohdr OUT NOCOPY t_genref, p_organization_id IN NUMBER, p_sohdr_id IN VARCHAR2) IS
304: BEGIN
305: OPEN x_mo_sohdr FOR
306: SELECT UNIQUE wdd.source_header_number
307: FROM wsh_delivery_details wdd
308: WHERE wdd.organization_id = p_organization_id
309: AND wdd.released_status = 'S'
310: AND wdd.source_header_number LIKE(p_sohdr_id);
311: END;
315: BEGIN
316: --bug 4951734, rewriting query with better performance.
317: /*
318: SELECT wnd.NAME, wnd.delivery_id
319: FROM wsh_new_deliveries wnd, wsh_delivery_details wdd, wsh_delivery_assignments_v wda, mtl_txn_request_lines ml
320: WHERE wda.delivery_id = wnd.delivery_id
321: AND wda.delivery_detail_id = wdd.delivery_detail_id
322: AND wdd.move_order_line_id = ml.line_id
323: AND wdd.organization_id = p_organization_id
330: OPEN x_delivery FOR
331: SELECT UNIQUE wnd.NAME
332: , wnd.delivery_id
333: FROM wsh_new_deliveries_ob_grp_v wnd
334: , wsh_delivery_details_ob_grp_v wdd
335: , wsh_delivery_assignments wda
336: , mtl_txn_request_lines ml
337: WHERE wda.delivery_id = wnd.delivery_id
338: AND wda.delivery_detail_id = wdd.delivery_detail_id
348: OPEN x_delivery FOR
349: SELECT UNIQUE wnd.NAME
350: , wnd.delivery_id
351: FROM wsh_new_deliveries_ob_grp_v wnd
352: , wsh_delivery_details_ob_grp_v wdd
353: , wsh_delivery_assignments wda
354: , mtl_txn_request_lines ml
355: , mtl_txn_request_headers mh
356: WHERE wda.delivery_id = wnd.delivery_id
370: OPEN x_delivery FOR
371: SELECT UNIQUE wnd.NAME
372: , wnd.delivery_id
373: FROM wsh_new_deliveries_ob_grp_v wnd
374: , wsh_delivery_details_ob_grp_v wdd
375: , wsh_delivery_assignments wda
376: , mtl_txn_request_lines ml
377: , mtl_txn_request_headers mh
378: , mtl_material_transactions_temp mmtt