---需要更新 insert into TB_USC_MONTHLY_BAL_DTL_JIT3 ( MONTHLY_BALANCE_DTL_JIT_ID,MONTHLY_BALANCE_MST_ID,ACCOUNT_PERIOD,DEL_FLAG,CREATE_BY ,CREATE_DATE ,UPDATE_BY,UPDATE_DATE, stock_id,org_id,wh_id,material_id,material_name, package_units_code, package_units_name,material_spec,manufacture_name,manufacture_code, units_code,units_name,amount_per_package,batch_no,valid_date, barcode,xbarcode,jit_stock_sum_qty,picking_qty,jit_stock_available_qty, unite_price,location_id,sterilized_date,remarks) select SEQ_USC_MONTHLY_BALANCE_DTL_J.NEXTVAL, '0604', --V_MONTHLY_BALANCE_MST_ID '201705', -- V_ACCOUNT_PERIOD 'N', 'PLDRB', -- 批量导入barcode SYSDATE, 'PLDRB', SYSDATE, 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, t2.my_sum, 0, t2.my_sum, flow.trade_price, flow.location_id, flow.sterilized_date, flow.remarks from tb_usc_monthly_balance_dtl_flw flow, ( select t.flow_id,my_sum from ( select t.flow_id, t.wh_to_id, t.material_id, t.batch_no, t.package_units_code, sum(t.quantity) over(partition by t.wh_to_id, t.material_id,t.batch_no, t.package_units_code ) my_sum, row_number() over(partition by t.wh_to_id, t.material_id,t.batch_no, t.package_units_code order by t.batch_no ) my_rank from tb_usc_monthly_balance_dtl_flw t where t.account_period='201705' and t.barcode is null and material_id not in ('684000000000736', '684000000000747', '684000000000938', '684000000000948')) t where t.my_sum>0 and my_rank=1 ) t2 where t2.flow_id=flow.flow_id insert into TB_USC_MONTHLY_BAL_DTL_JIT3 ( MONTHLY_BALANCE_DTL_JIT_ID,MONTHLY_BALANCE_MST_ID,ACCOUNT_PERIOD,DEL_FLAG,CREATE_BY ,CREATE_DATE ,UPDATE_BY,UPDATE_DATE, stock_id,org_id,wh_id,material_id,material_name, package_units_code, package_units_name,material_spec,manufacture_name,manufacture_code, units_code,units_name,amount_per_package,batch_no,valid_date, barcode,xbarcode,jit_stock_sum_qty,picking_qty,jit_stock_available_qty, unite_price,location_id,sterilized_date,remarks) select SEQ_USC_MONTHLY_BALANCE_DTL_J.NEXTVAL, '0604', --V_MONTHLY_BALANCE_MST_ID '201705', -- V_ACCOUNT_PERIOD 'N', 'PLDRB', -- 批量导入barcode SYSDATE, 'PLDRB', SYSDATE, 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 from tb_usc_monthly_balance_dtl_flw 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,t.barcode, t.package_units_code ) my_rank from tb_usc_monthly_balance_dtl_flw t where t.account_period='201705' and t.barcode is not null and material_id not in ('684000000000736', '684000000000747', '684000000000938', '684000000000948'))t where my_rank=1 ) INSERT INTO TB_USC_MONTHLY_BAL_DTL_JIT3 DJ ( DJ.MONTHLY_BALANCE_DTL_JIT_ID ,DJ.MONTHLY_BALANCE_MST_ID ,DJ.ACCOUNT_PERIOD ,DJ.DEL_FLAG ,DJ.CREATE_BY ,DJ.CREATE_DATE ,DJ.UPDATE_BY ,DJ.UPDATE_DATE ,DJ.STOCK_ID ,DJ.ORG_ID ,DJ.WH_ID ,DJ.MATERIAL_ID ,DJ.MATERIAL_NAME ,DJ.PACKAGE_UNITS_CODE ,DJ.PACKAGE_UNITS_NAME ,DJ.MATERIAL_SPEC ,DJ.MANUFACTURE_NAME ,DJ.MANUFACTURE_CODE ,DJ.UNITS_CODE ,DJ.UNITS_NAME ,DJ.AMOUNT_PER_PACKAGE ,DJ.BATCH_NO ,DJ.VALID_DATE ,DJ.BARCODE ,DJ.XBARCODE ,DJ.JIT_STOCK_SUM_QTY ,DJ.PICKING_QTY ,DJ.JIT_STOCK_AVAILABLE_QTY ,DJ.UNITE_PRICE ,DJ.STOCK_PRICE ,DJ.LOCATION_ID ,DJ.STERILIZED_DATE ,DJ.UNPACK_YN ,DJ.REMARKS) SELECT SEQ_USC_MONTHLY_BALANCE_DTL_J.NEXTVAL ,'0604' ,'201705' ,'N' ,'SYSTEM' ,SYSDATE ,'SYSTEM' ,SYSDATE ,A.* FROM(SELECT JS.STOCK_ID ,JS.ORG_ID ,JS.WH_ID ,JS.MATERIAL_ID ,JS.MATERIAL_NAME ,JS.PACKAGE_UNITS_CODE ,JS.PACKAGE_UNITS_NAME ,JS.MATERIAL_SPEC ,JS.MANUFACTURE_NAME ,JS.MANUFACTURE_CODE ,JS.UNITS_CODE ,JS.UNITS_NAME ,JS.AMOUNT_PER_PACKAGE ,JS.BATCH_NO ,JS.VALID_DATE ,JS.BARCODE ,JS.XBARCODE ,JS.JIT_STOCK_SUM_QTY ,JS.PICKING_QTY ,JS.JIT_STOCK_AVAILABLE_QTY ,JS.UNITE_PRICE ,JS.STOCK_PRICE ,JS.LOCATION_ID ,JS.STERILIZED_DATE ,JS.UNPACK_YN ,JS.REMARKS FROM TB_USC_JIT_STOCK JS where material_id in ('684000000000736', '684000000000747', '684000000000938', '684000000000948')) A; insert into TB_USC_MONTHLY_BAL_DTL_JIT3 (MONTHLY_BALANCE_DTL_JIT_ID, MONTHLY_BALANCE_MST_ID, ACCOUNT_PERIOD, STOCK_ID, ORG_ID, WH_ID, MATERIAL_ID, MATERIAL_NAME, PACKAGE_UNITS_CODE, PACKAGE_UNITS_NAME, MATERIAL_SPEC, MANUFACTURE_NAME, MANUFACTURE_CODE, UNITS_CODE, UNITS_NAME, AMOUNT_PER_PACKAGE, BATCH_NO, VALID_DATE, BARCODE, XBARCODE, JIT_STOCK_SUM_QTY, PICKING_QTY, JIT_STOCK_AVAILABLE_QTY, UNITE_PRICE, STOCK_PRICE, LOCATION_ID, STERILIZED_DATE, REMARKS, DEL_FLAG, CREATE_BY, CREATE_DATE, UPDATE_BY, UPDATE_DATE, UNPACK_YN) values (81338, 'YJ2017050003515', '201705', 11639414, '1000', 100498, '684000000000736', '尿沉管', '003', '包', '12ml', '北京思齐生物技术有限公司', '101675', '060', '支', 200, '20170210', to_date('09-02-2022', 'dd-mm-yyyy'), '48120170000016758', 'FH201704241354481', 1, 0, 1, 80.0000, null, '1001035', null, null, 'N', 'PLDRB', to_date('04-06-2017 18:18:45', 'dd-mm-yyyy hh24:mi:ss'), 'PR_MONTHLY_BALANCE_DEL', to_date('05-06-2017 15:39:29', 'dd-mm-yyyy hh24:mi:ss'), 'N'); ----需要删除 delete from TB_USC_MONTHLY_BAL_DTL_JIT3 where (wh_id,barcode)in( select t.wh_to_id,barcode 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,t.barcode, t.package_units_code ) my_rank from tb_usc_monthly_balance_dtl_flw t where t.account_period='201705' and t.barcode is not null and material_id not in ('684000000000736', '684000000000747', '684000000000938', '684000000000948'))t where my_rank=-1); select t.* from ( select t.flow_id, t.wh_to_id, t.material_id, t.batch_no, t.package_units_code, sum(t.quantity) over(partition by t.wh_to_id, t.material_id,t.batch_no, t.package_units_code ) my_sum, row_number() over(partition by t.wh_to_id, t.material_id,t.batch_no, t.package_units_code order by t.batch_no ) my_rank from tb_usc_monthly_balance_dtl_flw t where t.account_period='201705' and t.barcode is null and material_id not in ('684000000000736', '684000000000747', '684000000000938', '684000000000948')) t where t.my_sum<0 and my_rank=1 ;