PICKS IN COFI NOT IN WM WITH SHIP METHOD

select ------------PICKS IN COFI NOT IN WM WITH SHIP METHOD

hou.name DC,

wda.delivery_id PICK_TICKET,

oh.order_number,

ol.line_number LINE_NUMBER,

ol.ordered_item,

ol.shipping_method_code,

wdd.requested_quantity RELEASED_QUANTITY,

ol.schedule_ship_date,

decode(wdd.released_status,'Y','PICKED/STAGED') Released_status,

wdd.tp_attribute14 REQUEST_ID

from

hr_organization_units hou,

wsh_delivery_details wdd,

wsh_delivery_assignments wda,

oe_order_headers_all oh,

oe_order_lines_all ol

where

hou.type='DC'

and wdd.delivery_detail_id=wda.delivery_detail_id

and wdd.source_header_id=oh.header_id

and wdd.source_line_id=ol.line_id

and ol.ship_from_org_id=hou.organization_id

and oh.header_id=ol.header_id

and wdd.released_status in('Y')

--and wda.delivery_id is not null

and ol.schedule_ship_date is not null

and not exists (select dl.msgid

from apps.ecx_doclogs dl

,ecx.ecx_outbound_logs ol

,apps.ecx_oxta_logmsg lm

where dl.direction = 'OUT'

and lm.sender_message_id = dl.msgid

and ol.document_number = wda.delivery_id -- po_header_id

and ol.out_msgid = dl.msgid

and dl.transaction_subtype = 'OE_PICK_O')