CREATE PROCEDURE p_repo_mach /*用于从日报拷贝数据到备货单变更 张涛 2002-02-04*/
@change_no varchar(20),
@change_date datetime ,
@invoice_no varchar(20) ,
@user_no varchar(10),
@user_name varchar(20),
@department_no varchar(10),
@dept_name varchar(50),
@r varchar(5) out
AS
begin
declare @li_count int
declare @report_no varchar(20)
declare @ll_rowcount int
--begin tran
if exists (select change_no from task_mach where change_no = @change_no)
begin
update task_mach set contract_no = t.contract_no,country_no = t.country_no,consume_country = t.consume_country,co_no = t.co_no ,
trade_mode = t.trade_mode,client_name = t.client_name ,client_no = t.client_no,price_item = t.price_item,payment = t.payment,
exp_currency = t.exp_currency ,exchange_rate = t.exchange_rate,usd_rate = t.usd_rate,comm_amount = t.comm_amount,
add_reduce_money = t.add_reduce_money,invoice_money = t.invoice_money,pay_date = t.pay_date,comm_name = t.comm_name,
total_gross = t.total_gross,total_net = t.total_net,total_volume = t.total_volume,first_ship = t.first_ship,bill_no = t.bill_no,bill_date = t.bill_date,
freight = t.freight,insurance = t.insurance,after_commission = t.after_commission,comm_type = t.comm_type,invoice_date = t.invoice_date
from v_task_machas t
where t.invoice_no = @invoice_no and task_mach.invoice_no = @invoice_no
select @ll_rowcount = @@rowcount
end
else
begin
delete task_coch where change_no = @change_no
delete task_adch where change_no = @change_no
select @li_count = count(invoice_no) + 1 from task_mach group by invoice_no having invoice_no = @invoice_no
select @li_count = isnull(@li_count,1)
insert into task_mach(change_no,change_date,invoice_no,contract_no,country_no,consume_country ,co_no ,trade_mode ,client_name ,client_no,price_item ,payment ,exp_currency ,
exchange_rate ,usd_rate ,comm_amount ,add_reduce_money ,invoice_money ,pay_date ,comm_name ,total_gross,invoice_date,
total_net ,total_volume ,first_ship ,bill_no ,bill_date ,freight ,insurance ,after_commission ,comm_type,change_time,user_no,user_name,department_no,dept_name)
(select @change_no,@change_date,@invoice_no,contract_no,country_no, consume_country, co_no, trade_mode,client_name, client_no,price_item,
payment, exp_currency, exchange_rate, usd_rate, comm_amount, add_reduce_money, invoice_money,pay_date, comm_name,total_gross,invoice_date,
total_net, total_volume, first_ship,bill_no, bill_date, freight, insurance, after_commission, comm_type ,@li_count,@user_no,@user_name,@department_no,@dept_name
from v_task_machas where invoice_no = @invoice_no)
select @ll_rowcount = @@rowcount
end
/*从日报中拷贝变更商品*/
select @report_no = report_no from v_task_machas where invoice_no = @invoice_no
insert into task_coch
(change_no, comm_list_no, goods_no, goods_name_cn, quantity, unit, sell_price, commision,
inner_quantity, inner_unit, piece_count, pack_unit, sum_volume, volume_unit,
sum_gross, sum_net, weight_unit, customs_no, stat_unit, convert_rate, comm_type,
contract_no, con_line_no, cont_type, con_unitprice, comm_way, invo_no,
invo_line_no, pack_gross, pack_net, pack_volume,line_no)
select @change_no,comm_list_no, goods_no, goods_name_cn, quantity, unit, sell_price, commision,
inner_quantity, inner_unit, piece_count, pack_unit, sum_volume, volume_unit,
sum_gross, sum_net, weight_unit, customs_no, stat_unit, convert_rate, comm_type,
contract_no, con_line_no, cont_type, con_unitprice, comm_way, invo_no,
invo_line_no, pack_gross, pack_net, pack_volume,line_no
from v_task_cochas
where report_no = @report_no
/*从日报中拷贝变更加减项 */
insert into task_adch(line_no,add_type,add_explain, add_money,if_add, if_compute,change_no)
select line_no,add_type,add_explain, add_money,if_add, if_compute,@change_no
from task_read
where report_no = @report_no
if @ll_rowcount = 1
begin
select @r = 'ok'
--commit tran
--return
end
else
begin
--rollback tran
select @r = 'error'
--return
end
end
GO
