select * from tb_usc_stock_transfer_master mst
 where mst.status in('04') --04 已入库，05 部分确认
 and mst.stock_transfer_id not in( select mst1.doc_no from tb_usc_material_flow mst1 );


--库存转移出库流水
select tt.stock_transfer_id, tt.wh_to_id, tt.material_id,tt.quantity,tt.import_quantity,'出库' type
  from (
        
        select M1.stock_transfer_id,
                M2.wh_to_id,
                M1.import_quantity,
                M1.package_units_name,
                M1.material_id,
                M2.quantity,
                M2.package_units_name
          from (select  dtl.stock_transfer_id,
                        sum(dtl.process_quantity) import_quantity,
                        dtl.material_id,
                        dtl.package_units_code,
                        dtl.wh_from_id,
                       /* dtl.wh_to_id,*/
                        dtl.package_units_name
                   from tb_usc_stock_transfer_master mst,
                        tb_usc_stock_transfer_detail dtl
                  where mst.status in ('04')
                    and mst.stock_transfer_id = dtl.stock_transfer_id
                  group by dtl.stock_transfer_id,
                           dtl.material_id,
                           dtl.package_units_code,
                           dtl.wh_from_id,
                           dtl.package_units_name) M1,
                (select mst1.doc_no,
                        mst1.material_id,
                        mst1.package_units_code,
                        mst1.package_units_name,
                        sum(mst1.quantity) quantity,
                        /* mst1.wh_from_id,*/
                        mst1.wh_to_id
                   from tb_usc_material_flow mst1
                    where  doc_type_code != '02'
                  group by mst1.doc_no,
                           mst1.material_id,
                           mst1.package_units_code,
                           mst1.package_units_name,
                           /*    mst1.wh_from_id,*/
                           mst1.wh_to_id) M2
         WHERE M1.stock_transfer_id = M2.doc_no(+)
           AND M1.material_id = M2.material_id(+)
           AND M1.wh_from_id = M2.wh_to_id(+)
           
        
        ) tt
 where tt.quantity+tt.import_quantity!=0
    or tt.quantity is null
union all

--库存转移入库库流水
select tt.stock_transfer_id, tt.wh_to_id,tt.material_id, tt.quantity,tt.import_quantity,'入库' type
  from (
        
        select M1.stock_transfer_id,
                M2.wh_to_id,
                M1.import_quantity,
                M1.package_units_name,
                M2.quantity,
                  M1.material_id,
                M2.package_units_name
          from (select  dtl.stock_transfer_id,
                        sum(dtl.process_quantity) import_quantity,
                        dtl.material_id,
                        dtl.package_units_code,
                        /*dtl.wh_from_id,*/
                        dtl.wh_to_id,
                        dtl.package_units_name
                   from tb_usc_stock_transfer_master mst,
                        tb_usc_stock_transfer_detail dtl
                  where mst.status in ('04')
                    and mst.stock_transfer_id = dtl.stock_transfer_id
                  group by dtl.stock_transfer_id,
                           dtl.material_id,
                           dtl.package_units_code,
                           dtl.wh_to_id,
                           dtl.package_units_name) M1,
                (select mst1.doc_no,
                        mst1.material_id,
                        mst1.package_units_code,
                        mst1.package_units_name,
                        sum(mst1.quantity) quantity,
                        /* mst1.wh_from_id,*/
                        mst1.wh_to_id
                   from tb_usc_material_flow mst1
                   where  doc_type_code != '02'
                  group by mst1.doc_no,
                           mst1.material_id,
                           mst1.package_units_code,
                           mst1.package_units_name,
                           /*    mst1.wh_from_id,*/
                           mst1.wh_to_id) M2
         WHERE M1.stock_transfer_id = M2.doc_no(+)
           AND M1.material_id = M2.material_id(+)
           AND M1.wh_to_id = M2.wh_to_id(+)
           
        
        ) tt
 where tt.quantity-tt.import_quantity!=0
    or tt.quantity is null;

	
---确认sql
select *
  from tb_usc_material_flow_bak
 where doc_no in ('CK201705251002363')
   and material_id = '684000000000736' ;
   
   select *
  from tb_usc_material_flow
 where doc_no in ('CK201705251002363')
   and material_id = '684000000000736' ;
/*   and doc_type_code != '02'*/
  select mst1.doc_no,
         mst1.material_id,
         mst1.package_units_code,
         mst1.package_units_name,
         sum(mst1.quantity) quantity,
          mst1.wh_from_id,
         mst1.wh_to_id
          from tb_usc_material_flow mst1
          where doc_no in ('CK201705251002392')
   and material_id = '684000000007778'
         group by mst1.doc_no,
            mst1.material_id,
            mst1.package_units_code,
            mst1.package_units_name,
                mst1.wh_from_id,
            mst1.wh_to_id;
            
              select  dtl.stock_transfer_id,
                        sum(dtl.process_quantity) import_quantity,
                        dtl.material_id,
                        dtl.package_units_code,
                        dtl.wh_from_id,
                        dtl.wh_to_id,
                        dtl.package_units_name
                   from tb_usc_stock_transfer_master mst,
                        tb_usc_stock_transfer_detail dtl
                  where mst.status in ('04')
                    and mst.stock_transfer_id = dtl.stock_transfer_id
                    and  dtl.stock_transfer_id in ('CK201705251002392')
   and dtl.material_id = '684000000007778'
                  group by dtl.stock_transfer_id,
                           dtl.material_id,
                           dtl.package_units_code,
                           dtl.wh_to_id,  dtl.wh_from_id,
                           dtl.package_units_name;