Query to find Sales Order Pick Release Date & Ship Confirm Date in Oracle APPS R12

/*Query to find Sales Order Pick Date*/

OPTION1:

SELECT
       (SELECT name
        FROM hr_organization_units hou
        WHERE hou.organization_id = wdd.org_id) org_name,
       wdd.source_header_number,
       wdd.source_line_number,
       (SELECT msi.segment1
        FROM mtl_system_items_b msi
        WHERE msi.inventory_item_id = wdd.inventory_item_id
        AND ROWNUM = 1) item,
       wdd.item_description,
       wdd.unit_price,
       requested_quantity,
       requested_quantity_uom,
       picked_quantity,
       mmt.transaction_date pickup_date,
       mmt.subinventory_code,
       mmt.transfer_subinventory
FROM   wsh_delivery_details wdd,
       mtl_material_transactions mmt
WHERE  1 = 1
AND    mmt.transaction_id = wdd.transaction_id
AND    mmt.transaction_type_id = 52 --Sales Order Pick
AND    wdd.source_header_number = <sales_order_number>
       --and SOURCE_HEADER_ID = 
       ;

OPTION2:

/*Query for when line is pick released*/
--The table wsh_picking_batches stores the picking related information.We can use wsh_picking_batches table to get the last_update_date,last_updated_by information. The batch_id in wsh_picking_batches is stamped in wsh_delivery_details table.

SELECT wpb.last_update_date PICK_RELEASED_DATE
FROM   wsh_delivery_details wdd,
       mtl_txn_request_lines mtrl,
       oe_order_lines_all oel,
       wsh_picking_batches wpb
WHERE  wpb.batch_id = wdd.batch_id
       AND mtrl.line_id = wdd.move_order_line_id
       AND wdd.released_status IN ( 'S', 'Y', 'C' )
       AND wdd.source_code = 'OE'
       AND wdd.source_line_id = oel.line_id
       AND oel.line_id = &line_id;

OPTION3:

/*Query for when the move order line was closed:*/
--There is a date stamp in Inventory which shows when a move order was closed. This is the closest to PICKED status.

SELECT TRL.status_date PICKED_DATE
FROM   mtl_txn_request_lines_v TRL,
       mtl_txn_request_headers TRH,
       wsh_delivery_details DET
WHERE  TRL.line_id = DET.move_order_line_id
       AND TRL.header_id = TRH.header_id
       AND TRL.line_status = 5 -- 5 means Closed
       AND DET.source_header_id = &header_id
       AND det.source_line_id = &line_id; 

/*Query for when the delivery was Ship Confirmed*/

--Query for CONFIRM_DATE in the table WSH_NEW_DELIVERIES.

SELECT confirm_date SHIPPED_DATE
FROM   wsh_new_deliveries
WHERE  delivery_id = &delivery_id;

(OR)

SELECT ool.header_id,
       ool.line_id,
       wnd.confirm_date,
       ool.actual_shipment_date,
       ool.actual_fulfillment_date,
       Nvl(Nvl(ool.actual_shipment_date,ool.actual_fulfillment_date),wnd.confirm_date) shipping_date,
from   wsh_delivery_details wdd,
       wsh_delivery_assignments wda,
       wsh_new_deliveries wnd,
       oe_order_lines_all ool
WHERE  ool.line_id = wdd.source_line_id
AND    wdd.delivery_detail_id = wda.delivery_detail_id
AND    wda.delivery_id = wnd.delivery_id
AND    wnd.delivery_id = <---->;

P.S. If ACTUAL_SHIPMENT_DATE In Sales Order Line Is Not Same As Confirm Date In Delivery, Please log a SR with Oracle Support providing output of below SQL:

SELECT ol.header_id,
       ol.line_id,
       To_char(wnd.confirm_date, 'DD-MON-YYYY'),
       To_char(ol.actual_shipment_date, 'DD-MON-YYYY')
FROM   wsh_delivery_details wdd,
       wsh_delivery_assignments wda,
       wsh_new_deliveries wnd,
       oe_order_lines_all ol
WHERE  ol.line_id = wdd.source_line_id
       AND wdd.delivery_detail_id = wda.delivery_detail_id
       AND wda.delivery_id = wnd.delivery_id
       AND To_char(ol.actual_shipment_date, 'DD-MON-YYYY') <>
           To_char(wnd.confirm_date, 'DD-MON-YYYY');