select seq_usc_jit_stock.nextval, '1000', flow.wh_to_id, flow.material_id, flow.material_name, flow.package_units_code, flow.package_units_name, flow.material_spec, flow.manufacture_name, flow.manufacture_code, (select m.material_units_code from tb_usc_material m where m.material_id = flow.material_id), (select material_units_name from tb_usc_material m where m.material_id = flow.material_id), (select amount_per_package from tb_usc_material m where m.material_id = flow.material_id), flow.batch_no, flow.valid_date, flow.barcode, flow.xbarcode, flow.quantity, 0, flow.quantity, flow.trade_price, flow.location_id, flow.sterilized_date, flow.remarks, flow.create_date, 'SYSTEM' from tb_usc_material_flow flow where flow_id in ( select t.flow_id from ( select t.flow_id, t.wh_to_id, t.material_id, t.barcode, t.package_units_code,t.quantity, sum(t.quantity) over(partition by t.wh_to_id, t.material_id, nvl(t.barcode,0), t.package_units_code ) my_rank from tb_usc_material_flow t)t where my_rank!=0 and (wh_to_id, material_id, nvl(barcode,0)) not in( select stock.wh_id, stock.material_id, nvl(stock.barcode,0) from tb_usc_jit_stock stock ) ) and flow.create_date>=to_date('01-05-2017 00:00:00', 'dd-mm-yyyy hh24:mi:ss') select stock.stock_id, stock.org_id, stock.wh_id, stock.material_id, stock.material_name, stock.package_units_code, stock.package_units_name, stock.material_spec, stock.manufacture_name, stock.manufacture_code, stock.units_code, stock.units_name, stock.amount_per_package, stock.batch_no, stock.valid_date, stock.barcode, stock.xbarcode, stock.jit_stock_sum_qty, stock.picking_qty, stock.jit_stock_available_qty, stock.unite_price, stock.location_id, stock.sterilized_date, stock.remarks, stock.create_date, stock.create_by from tb_usc_jit_stock stock;