select
h.transfer_doc_number
,h.created_by
,h.organization_id
,h.fm_sub_code
,h.to_sub_code
,h.fm_loc_code
,h.to_loc_code
,l.request_quantity
,l.transfer_reason
,l.item_no
,l.item_id
,substrb(l.item_desc,1,250) item_desc
,l.uom
,l.vendor_name
,(SELECT document_number
FROM ecs_wip_confirm_doc
WHERE 1 = 1
AND document_id =l.confirm_document_id
) confirm_doc
,l.from_keeper
,l.to_keeper
,ecs_wip_function_pkg.get_bin(h.organization_id , l.item_id , h.fm_sub_code, h.fm_loc_id) from_bin
,ecs_wip_function_pkg.get_bin(h.organization_id , l.item_id , h.to_sub_code , h.to_loc_id) to_bin
,(CASE WHEN xcdd.from_organization_id = 8595
THEN SELECT eib.bin
from apps.ecs_inv_bin_details eib
where eib.organization_id = xcdd.from_organization_id
and eib.subinventory_code = h.fm_sub_code
and eib.inventory_item_id = xcdd.inventory_item_id ) from xxwi_sub_transfer_h_v h
,xxwi_sub_transfer_l_v l
,xxwi_cai_document_details xcdd
where h.transfer_header_id = l.transfer_header_id
and h.TRANSFER_DOC_NUMBER = 'MY00000023'
and xcdd.document_id(+) = h.TRANSFER_HEADER_ID
order by h.transfer_header_id, l.transfer_line_id
------解决方案--------------------