To Get ISO pegged to CSO

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

hou.NAME DC,

wda.delivery_id PICK_TICKET,

SUBSTR(md.order_number,1,(INSTR(md.order_number,'.',1,1)-1)) "Orders not in WM",

DECODE(LENGTH(SUBSTR(mfsd.end_disposition,1,(INSTR(mfsd.end_disposition,'.',1,1)-1))),9,null,SUBSTR(mfsd.end_disposition,1,(INSTR(mfsd.end_disposition,'.',1,1)-1))) "Pegging info(CSO)"

,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

--wdd.ship_to_location_id

FROM

hr_organization_units hou,

wsh_delivery_details wdd,

wsh_delivery_assignments wda,

oe_order_headers_all oh,

oe_order_lines_all ol,

msc_flp_supply_demand_v mfsd,

msc_demands md,

oe_order_lines_all ol2

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')

AND mfsd.demand_id=md.demand_id

--AND decode(substr(mfsd.end_disposition,14,14),'Standard Sales'

AND SUBSTR(md.order_number,15,15)=' Internal Order'

AND ol2.line_id=md.sales_order_line_id

AND mfsd.origination_name='Sales Orders'

--and substr(b.order_number,1,(instr(b.order_number,'.',1,1)-1))= &internal_order_number

AND mfsd.plan_id=1

AND mfsd.end_disposition IS NOT NULL

AND oh.order_number=SUBSTR(md.order_number,1,9)

AND wdd.source_line_id=md.sales_order_line_id

--and oh.order_number in ('100077315','100077314')

AND oh.order_type_id=1009

UNION

SELECT

hou.NAME DC,

wda.delivery_id PICK_TICKET,

TO_CHAR(oh.order_number) "Orders not in WM"

,null "Pegging info(CSO)"

,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 oh.order_number in ('100077315','100077314')

AND oh.order_type_id=1008

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')

UNION

SELECT

hou.NAME DC,

wda.delivery_id PICK_TICKET,

TO_CHAR(oh.order_number) "Orders not in WM"

,NULL "Pegging info(CSO)"

,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 oh.order_number in ('100077315','100077314')

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')

AND NOT EXISTS(SELECT md.sales_order_line_id FROM

msc_demands md WHERE

ol.line_id=md.sales_order_line_id