select mst.* from tb_usc_import_master mst where mst.status='05' and mst.import_master_id not in( select mst1.doc_no from tb_usc_material_flow mst1 ); --大库采购有流水有误差 select tt.import_master_id, tt.wh_to_id, tt.material_id, tt.quantity, tt.import_quantity, '大库采购入库' type from ( select M1.import_master_id, M1.wh_to_id, M1.material_id, M1.import_quantity, M1.package_units_name, M2.quantity, M2.package_units_name from (select dtl.import_master_id, dtl.material_id, SUM(dtl.import_quantity) import_quantity, dtl.package_units_code, dtl.wh_to_id, --mst.wh_bill_id, dtl.package_units_name from tb_usc_import_master mst, tb_usc_import_detail dtl where mst.status = '05' and mst.import_master_id = dtl.import_master_id and mst.wh_bill_id = dtl.wh_to_id --大库采购 group by dtl.import_master_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 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.import_master_id = M2.doc_no(+) AND M1.material_id = M2.material_id(+) AND M1.wh_to_id = M2.wh_to_id(+) AND M1.import_master_id not in (select mst.import_master_id from tb_usc_import_master mst where mst.status = '05' and mst.import_master_id not in (select mst1.doc_no from tb_usc_material_flow mst1))) tt where tt.import_quantity - tt.quantity != 0 union all --直送采购有流水有误差 select tt.import_master_id, tt.wh_to_id, tt.material_id, tt.quantity, tt.import_quantity, '直送采购入库' type from ( select M1.import_master_id, M1.wh_to_id, M1.material_id, M1.import_quantity, M1.package_units_name, M2.quantity, M2.package_units_name from (select dtl.import_master_id, dtl.material_id, SUM(dtl.import_quantity) import_quantity, dtl.package_units_code, dtl.wh_to_id, --mst.wh_bill_id, dtl.package_units_name from tb_usc_import_master mst, tb_usc_import_detail dtl where mst.status = '05' and mst.import_master_id = dtl.import_master_id and mst.wh_bill_id != dtl.wh_to_id --大库采购 group by dtl.import_master_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 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.import_master_id = M2.doc_no(+) AND M1.material_id = M2.material_id(+) AND M1.wh_to_id = M2.wh_to_id(+) AND M1.import_master_id not in (select mst.import_master_id from tb_usc_import_master mst where mst.status = '05' and mst.import_master_id not in (select mst1.doc_no from tb_usc_material_flow mst1))) tt where tt.import_quantity - tt.quantity != 0 union all --直送采购单大库流水 select tt.import_master_id, tt.wh_to_id, tt.material_id, tt.quantity, tt.import_quantity, '直送采购大库出入库' type from ( select M1.import_master_id, M1.wh_bill_id wh_to_id, M1.material_id, M1.import_quantity, M1.package_units_name, M2.quantity, M2.package_units_name from (select dtl.import_master_id, dtl.material_id, SUM(dtl.import_quantity) import_quantity, dtl.package_units_code, mst.wh_bill_id, --mst.wh_bill_id, dtl.package_units_name from tb_usc_import_master mst, tb_usc_import_detail dtl where mst.status = '05' and mst.import_master_id = dtl.import_master_id and mst.wh_bill_id != dtl.wh_to_id group by dtl.import_master_id, dtl.material_id, dtl.package_units_code, mst.wh_bill_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 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.import_master_id = M2.doc_no(+) AND M1.material_id = M2.material_id(+) AND M1.wh_bill_id = M2.wh_to_id(+) AND M1.import_master_id not in (select mst.import_master_id from tb_usc_import_master mst where mst.status = '05' and mst.import_master_id not in (select mst1.doc_no from tb_usc_material_flow mst1)) ) tt where tt.quantity != 0 or tt.quantity is null