近期主要忙的一件事情就是将OA报销单据与用友NC633对接，达到自动生成凭证的效果
当时一想很简单啊，就说OA到NC的数据由我这边写程序生成，结果就苦逼了。。。
先看看MSSQL的存储过程就知道了
日常费用报销
USE [ecology]
GO
/****** Object: StoredProcedure [dbo].[create_voucher_normalcost] Script Date: 2017/2/17 18:12:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[create_voucher_normalcost](@requestid int)
as
DECLARE @vouchertype varchar(10)
DECLARE @oaworkcode int
set @oaworkcode=0
DECLARE @cost_type nvarchar(20)
set @cost_type='RCFYBX'
DECLARE @the_number_str varchar(100)
execute getVbillno @cost_type,@the_number_str output
DECLARE @mx_id int
DECLARE @mx_tax float
DECLARE @mx_realmoney float
DECLARE @add_in_subject nvarchar(20)
DECLARE @dt_sum float
DECLARE @mx_acc_sub_code nvarchar(40)
set @dt_sum=0
DECLARE @total_normal_cost float
DECLARE @total_reception_budget float
DECLARE @total_city_traffic_cost float
DECLARE @actual_pay_num float
DECLARE @write_off_num float
set @actual_pay_num=(select actual_num from formtable_main_5 where requestid=@requestid)
set @write_off_num=(select write_offs_total from formtable_main_5 where requestid=@requestid)
set @vouchertype=(select case actual_num when 0 then '02' else '01' end from formtable_main_5 where requestid=@requestid)
set @total_normal_cost=(select normal_cost from formtable_main_5 where requestid=@requestid)
set @total_reception_budget=(select reception_budget from formtable_main_5 where requestid=@requestid)
set @total_city_traffic_cost =(select city_traffic_cost from formtable_main_5 where requestid=@requestid)
/*1 日常费用报销明细 生成凭证*/
IF((@total_normal_cost is not null) and @total_normal_cost>0)
begin
DECLARE this_cursor Cursor for
select id from formtable_main_5_dt1 where mainid=(select id from formtable_main_5 where requestid=@requestid)
Open this_cursor Fetch Next From this_cursor Into @mx_id
While ( @@Fetch_Status=0 )
begin
set @mx_tax=(select tax from formtable_main_5_dt1 where id=@mx_id)
SET @mx_realmoney=(select real_money from formtable_main_5_dt1 where id=@mx_id)
set @add_in_subject=(select add_in_subject from uf_cost_category where acc_sub_code=@mx_acc_sub_code)
---1、不进成本
if(@add_in_subject is null)
begin
INSERT INTO [oa2nc].[dbo].[bd_voucher]([pk_corp],[pk_group],[pk_org],[billmaker],[billdate],[creator],[creationtime],[glorgbook_code],[glorgbook],[vouchertype],[tallydate]
,[oarequestID],[oaworkcode],[Oacode],[vbillno],[explanation],[accsubjcode],[accsubjname],[currency],[exchange],[baldebitmny],[baldebitlocalmny],[balcreditmny],[balcreditlocalmny],[checktype1]
,[checkcode1],[checkname1],[checktype2],[checkcode2],[checkname2],[checktype3],[checkcode3],[checkname3],[checktype4],[checkcode4],[checkname4],[checktype5],[checkcode5],[checkname5]
,[checktype6],[checkcode6],[checkname6],[vdef1],[vdef2],[vdef3],[vdef4],[vdef5],[ts],[dr],[pk_cashflow],[cashflowflag])
select distinct c.code,'01',c.code,'v-wantao.zhang',convert(varchar(10),getDate(),120),'v-wantao.zhang',convert(varchar(19),getDate(),120),c.accountcode,c.accountname,@vouchertype,convert(varchar(10),getDate(),120),
@requestid,@oaworkcode,doc_number,@the_number_str,g.name+j.name+d.cost_date+d.reason+'产生日常费用'+CAST(CAST(real_money AS DECIMAL(20,5)) AS VARCHAR(20))+'元',e.acc_sub_code,e.acc_sub_name,'CNY',1.00000,real_money,real_money,null,null,case e.sec_acc_dept when 0 then '0001' else null end,
case e.sec_acc_dept when 0 then k.code else null end,case e.sec_acc_dept when 0 then k.name else null end,
case e.sec_acc_staff when 0 then '0002' else null end,case e.sec_acc_staff when 0 then j.code else null end,case e.sec_acc_staff when 0 then j.name else null end,
case e.sec_acc_customer when 0 then '004' else null end,null,null,
case e.sec_acc_supplier when 0 then '004' else null end,null,null,
case when (e.project_code is not null and e.project_code!='') then 'FI03' else null end,e.project_code,h.name,
case e.sec_acc_bank when 0 then '0011' else null end,case e.sec_acc_bank when 0 then (select accnum from [oa2nc].[dbo].v_bankdoc where accname='********银行-活期') else null end,case e.sec_acc_bank when 0 then '********银行-活期' else null end,
null,null,null,null,null,convert(varchar(19),getDate(),120),0,e.cashflow_code,0
from formtable_main_5 a
left join hrmsubcompany b on a.reimbursement_unit=b.id
left join [oa2nc].[dbo].v_orgs c on c.name=b.subcompanyname
left join formtable_main_5_dt1 d on d.mainid=a.id
left join uf_cost_category e on e.id=d.cost_class_02
left join uf_dept_oa2nc f on f.oa_dept=d.cost_dept
left join [oa2nc].[dbo].v_dept g on f.nc_dept=g.pk_dept
left join [oa2nc].[dbo].v_project h on h.code=e.project_code
left join uf_staff_oa2nc i on i.oa_staff=a.applicant_submit
left join [oa2nc].[dbo].v_psndoc j on i.nc_psndoc=j.pk_psndoc left join oa2nc.dbo.v_dept_normal k on g.name=k.name
where a.requestid=@requestid and d.id=@mx_id and k.pk_org=c.pk_org and j.pk_org=c.pk_org
if(@mx_tax>0)
begin
INSERT INTO [oa2nc].[dbo].[bd_voucher]([pk_corp],[pk_group],[pk_org],[billmaker],[billdate],[creator],[creationtime],[glorgbook_code],[glorgbook],[vouchertype],[tallydate]
,[oarequestID],[oaworkcode],[Oacode],[vbillno],[explanation],[accsubjcode],[accsubjname],[currency],[exchange],[baldebitmny],[baldebitlocalmny],[balcreditmny],[balcreditlocalmny],[checktype1]
,[checkcode1],[checkname1],[checktype2],[checkcode2],[checkname2],[checktype3],[checkcode3],[checkname3],[checktype4],[checkcode4],[checkname4],[checktype5],[checkcode5],[checkname5]
,[checktype6],[checkcode6],[checkname6],[vdef1],[vdef2],[vdef3],[vdef4],[vdef5],[ts],[dr],[pk_cashflow],[cashflowflag])
select distinct c.code,'01',c.code,'v-wantao.zhang',convert(varchar(10),getDate(),120),'v-wantao.zhang',convert(varchar(19),getDate(),120),c.accountcode,c.accountname,@vouchertype,convert(varchar(10),getDate(),120),
@requestid,@oaworkcode,doc_number,@the_number_str,g.name+j.name+d.cost_date+d.reason+'产生税额'+CAST(CAST(tax AS DECIMAL(20,5)) AS VARCHAR(20))+'元',t.acc_sub_code,t.acc_sub_name,'CNY',1.00000,d.tax,d.tax,null,null,case t.sec_acc_dept when '部门' then '0001' else null end,
case t.sec_acc_dept when '部门' then k.code else null end,case t.sec_acc_dept when '部门' then k.name else null end,
case t.sec_acc_staff when '员工' then '0002' else null end,case t.sec_acc_staff when '员工' then j.code else null end,case t.sec_acc_staff when '员工' then j.name else null end,
null,null,null,
null,null,null,
case when (e.project_code is not null and e.project_code!='') then 'FI03' else null end,e.project_code,h.name,
case t.sec_acc_bank when 0 then '0011' else null end,case t.sec_acc_bank when 0 then (select accnum from [oa2nc].[dbo].v_bankdoc where accname='********银行-活期') else null end,case e.sec_acc_bank when 0 then '********银行-活期' else null end,
null,null,null,null,null,convert(varchar(19),getDate(),120),0,t.cashflow_code,0
from formtable_main_5 a
left join hrmsubcompany b on a.reimbursement_unit=b.id
left join [oa2nc].[dbo].v_orgs c on c.name=b.subcompanyname
left join formtable_main_5_dt1 d on d.mainid=a.id
left join uf_cost_category e on e.acc_sub_code=@mx_acc_sub_code
left join cost_nc_account t on t.acc_sub_code=e.tax_subject_code
left join uf_dept_oa2nc f on f.oa_dept=d.cost_dept
left join [oa2nc].[dbo].v_dept g on f.nc_dept=g.pk_dept
left join [oa2nc].[dbo].v_project h on h.code=e.project_code
left join uf_staff_oa2nc i on i.oa_staff=a.applicant_submit
left join [oa2nc].[dbo].v_psndoc j on i.nc_psndoc=j.pk_psndoc left join oa2nc.dbo.v_dept_normal k on g.name=k.name
where a.requestid=@requestid and d.id=@mx_id and k.pk_org=c.pk_org and j.pk_org=c.pk_org
end
set @dt_sum=@dt_sum+@mx_realmoney+@mx_tax
end
---2、进成本
if(@add_in_subject is not null)
begin
INSERT INTO [oa2nc].[dbo].[bd_voucher]([pk_corp],[pk_group],[pk_org],[billmaker],[billdate],[creator],[creationtime],[glorgbook_code],[glorgbook],[vouchertype],[tallydate]
,[oarequestID],[oaworkcode],[Oacode],[vbillno],[explanation],[accsubjcode],[accsubjname],[currency],[exchange],[baldebitmny],[baldebitlocalmny],[balcreditmny],[balcreditlocalmny],[checktype1]
,[checkcode1],[checkname1],[checktype2],[checkcode2],[checkname2],[checktype3],[checkcode3],[checkname3],[checktype4],[checkcode4],[checkname4],[checktype5],[checkcode5],[checkname5]
,[checktype6],[checkcode6],[checkname6],[vdef1],[vdef2],[vdef3],[vdef4],[vdef5],[ts],[dr],[pk_cashflow],[cashflowflag])
select distinct c.code,'01',c.code,'v-wantao.zhang',convert(varchar(10),getDate(),120),'v-wantao.zhang',convert(varchar(19),getDate(),120),c.accountcode,c.accountname,@vouchertype,convert(varchar(10),getDate(),120),
@requestid,@oaworkcode,doc_number,@the_number_str,g.name+j.name+d.cost_date+d.reason+'产生日常费用'+CAST(CAST(real_money AS DECIMAL(20,5)) AS VARCHAR(20))+'元',e.acc_sub_code,e.acc_sub_name,'CNY',1.00000,real_money,real_money,null,null,case e.sec_acc_dept when 0 then '0001' else null end,
case e.sec_acc_dept when 0 then k.code else null end,case e.sec_acc_dept when 0 then k.name else null end,
case e.sec_acc_staff when 0 then '0002' else null end,case e.sec_acc_staff when 0 then j.code else null end,case e.sec_acc_staff when 0 then j.name else null end,
case e.sec_acc_customer when 0 then '004' else null end,null,null,
case e.sec_acc_supplier when 0 then '004' else null end,null,null,
case when (e.project_code is not null and e.project_code!='') then 'FI03' else null end,e.project_code,h.name,
case e.sec_acc_bank when 0 then '0011' else null end,case e.sec_acc_bank when 0 then (select accnum from [oa2nc].[dbo].v_bankdoc where accname='********银行-活期') else null end,case e.sec_acc_bank when 0 then '********银行-活期' else null end,
null,null,null,null,null,convert(varchar(19),getDate(),120),0,e.cashflow_code,0
from formtable_main_5 a
left join hrmsubcompany b on a.reimbursement_unit=b.id
left join [oa2nc].[dbo].v_orgs c on c.name=b.subcompanyname
left join formtable_main_5_dt1 d on d.mainid=a.id
left join uf_cost_category e on e.acc_sub_code=@mx_acc_sub_code
left join uf_dept_oa2nc f on f.oa_dept=d.cost_dept
left join [oa2nc].[dbo].v_dept g on f.nc_dept=g.pk_dept
left join [oa2nc].[dbo].v_project h on h.code=e.project_code
left join uf_staff_oa2nc i on i.oa_staff=a.applicant_submit
left join [oa2nc].[dbo].v_psndoc j on i.nc_psndoc=j.pk_psndoc left join oa2nc.dbo.v_dept_normal k on g.name=k.name
where a.requestid=@requestid and d.id=@mx_id and k.pk_org=c.pk_org and j.pk_org=c.pk_org
INSERT INTO [oa2nc].[dbo].[bd_voucher]([pk_corp],[pk_group],[pk_org],[billmaker],[billdate],[creator],[creationtime],[glorgbook_code],[glorgbook],[vouchertype],[tallydate]
,[oarequestID],[oaworkcode],[Oacode],[vbillno],[explanation],[accsubjcode],[accsubjname],[currency],[exchange],[baldebitmny],[baldebitlocalmny],[balcreditmny],[balcreditlocalmny],[checktype1]
,[checkcode1],[checkname1],[checktype2],[checkcode2],[checkname2],[checktype3],[checkcode3],[checkname3],[checktype4],[checkcode4],[checkname4],[checktype5],[checkcode5],[checkname5]
,[checktype6],[checkcode6],[checkname6],[vdef1],[vdef2],[vdef3],[vdef4],[vdef5],[ts],[dr],[pk_cashflow],[cashflowflag])
select distinct c.code,'01',c.code,'v-wantao.zhang',convert(varchar(10),getDate(),120),'v-wantao.zhang',convert(varchar(19),getDate(),120),c.accountcode,c.accountname,@vouchertype,convert(varchar(10),getDate(),120),
@requestid,@oaworkcode,doc_number,@the_number_str,g.name+j.name+d.cost_date+d.reason+'产生日常费用'+CAST(CAST(real_money AS DECIMAL(20,5)) AS VARCHAR(20))+'元',e.add_in_subject_code,e.add_in_subject_name,'CNY',1.00000,real_money,real_money,null,null,case e.sec_acc_dept when 0 then '0001' else null end,
case e.sec_acc_dept when 0 then k.code else null end,case e.sec_acc_dept when 0 then k.name else null end,
case e.sec_acc_staff when 0 then '0002' else null end,case e.sec_acc_staff when 0 then j.code else null end,case e.sec_acc_staff when 0 then j.name else null end,
case e.sec_acc_customer when 0 then '004' else null end,null,null,
case e.sec_acc_supplier when 0 then '004' else null end,null,null,
case when (e.project_code is not null and e.project_code!='') then 'FI03' else null end,e.project_code,h.name,
case e.sec_acc_bank when 0 then '0011' else null end,case e.sec_acc_bank when 0 then (select accnum from [oa2nc].[dbo].v_bankdoc where accname='********银行-活期') else null end,case e.sec_acc_bank when 0 then '********银行-活期' else null end,
null,null,null,null,null,convert(varchar(19),getDate(),120),0,e.cashflow_code,0
from formtable_main_5 a
left join hrmsubcompany b on a.reimbursement_unit=b.id
left join [oa2nc].[dbo].v_orgs c on c.name=b.subcompanyname
left join formtable_main_5_dt1 d on d.mainid=a.id
left join uf_cost_category e on e.id=d.cost_class_02
left join uf_dept_oa2nc f on f.oa_dept=d.cost_dept
left join [oa2nc].[dbo].v_dept g on f.nc_dept=g.pk_dept
left join [oa2nc].[dbo].v_project h on h.code=e.project_code
left join uf_staff_oa2nc i on i.oa_staff=a.applicant_submit
left join [oa2nc].[dbo].v_psndoc j on i.nc_psndoc=j.pk_psndoc left join oa2nc.dbo.v_dept_normal k on g.name=k.name
where a.requestid=@requestid and d.id=@mx_id and k.pk_org=c.pk_org and j.pk_org=c.pk_org
INSERT INTO [oa2nc].[dbo].[bd_voucher]([pk_corp],[pk_group],[pk_org],[billmaker],[billdate],[creator],[creationtime],[glorgbook_code],[glorgbook],[vouchertype],[tallydate]
,[oarequestID],[oaworkcode],[Oacode],[vbillno],[explanation],[accsubjcode],[accsubjname],[currency],[exchange],[baldebitmny],[baldebitlocalmny],[balcreditmny],[balcreditlocalmny],[checktype1]
,[checkcode1],[checkname1],[checktype2],[checkcode2],[checkname2],[checktype3],[checkcode3],[checkname3],[checktype4],[checkcode4],[checkname4],[checktype5],[checkcode5],[checkname5]
,[checktype6],[checkcode6],[checkname6],[vdef1],[vdef2],[vdef3],[vdef4],[vdef5],[ts],[dr],[pk_cashflow],[cashflowflag])
select distinct c.code,'01',c.code,'v-wantao.zhang',convert(varchar(10),getDate(),120),'v-wantao.zhang',convert(varchar(19),getDate(),120),c.accountcode,c.accountname,@vouchertype,convert(varchar(10),getDate(),120),
@requestid,@oaworkcode,doc_number,@the_number_str,g.name+j.name+d.cost_date+d.reason+'产生日常费用'+CAST(CAST(real_money AS DECIMAL(20,5)) AS VARCHAR(20))+'元',e.add_in_subject_code,e.add_in_subject_name,'CNY',1.00000,null,null,real_money,real_money,case e.sec_acc_dept when 0 then '0001' else null end,
case e.sec_acc_dept when 0 then k.code else null end,case e.sec_acc_dept when 0 then k.name else null end,
case e.sec_acc_staff when 0 then '0002' else null end,case e.sec_acc_staff when 0 then j.code else null end,case e.sec_acc_staff when 0 then j.name else null end,
case e.sec_acc_customer when 0 then '004' else null end,null,null,
case e.sec_acc_supplier when 0 then '004' else null end,null,null,
case when (e.project_code is not null and e.project_code!='') then 'FI03' else null end,e.project_code,h.name,
case e.sec_acc_bank when 0 then '0011' else null end,case e.sec_acc_bank when 0 then (select accnum from [oa2nc].[dbo].v_bankdoc where accname='********银行-活期') else null end,case e.sec_acc_bank when 0 then '********银行-活期' else null end,
null,null,null,null,null,convert(varchar(19),getDate(),120),0,e.cashflow_code,0
from formtable_main_5 a
left join hrmsubcompany b on a.reimbursement_unit=b.id
left join [oa2nc].[dbo].v_orgs c on c.name=b.subcompanyname
left join formtable_main_5_dt1 d on d.mainid=a.id
left join uf_cost_category e on e.id=d.cost_class_02
left join uf_dept_oa2nc f on f.oa_dept=d.cost_dept
left join [oa2nc].[dbo].v_dept g on f.nc_dept=g.pk_dept
left join [oa2nc].[dbo].v_project h on h.code=e.project_code
left join uf_staff_oa2nc i on i.oa_staff=a.applicant_submit
left join [oa2nc].[dbo].v_psndoc j on i.nc_psndoc=j.pk_psndoc left join oa2nc.dbo.v_dept_normal k on g.name=k.name
where a.requestid=@requestid and d.id=@mx_id and k.pk_org=c.pk_org and j.pk_org=c.pk_org
if(@mx_tax>0)
begin
INSERT INTO [oa2nc].[dbo].[bd_voucher]([pk_corp],[pk_group],[pk_org],[billmaker],[billdate],[creator],[creationtime],[glorgbook_code],[glorgbook],[vouchertype],[tallydate]
,[oarequestID],[oaworkcode],[Oacode],[vbillno],[explanation],[accsubjcode],[accsubjname],[currency],[exchange],[baldebitmny],[baldebitlocalmny],[balcreditmny],[balcreditlocalmny],[checktype1]
,[checkcode1],[checkname1],[checktype2],[checkcode2],[checkname2],[checktype3],[checkcode3],[checkname3],[checktype4],[checkcode4],[checkname4],[checktype5],[checkcode5],[checkname5]
,[checktype6],[checkcode6],[checkname6],[vdef1],[vdef2],[vdef3],[vdef4],[vdef5],[ts],[dr],[pk_cashflow],[cashflowflag])
select distinct c.code,'01',c.code,'v-wantao.zhang',convert(varchar(10),getDate(),120),'v-wantao.zhang',convert(varchar(19),getDate(),120),c.accountcode,c.accountname,@vouchertype,convert(varchar(10),getDate(),120),
@requestid,@oaworkcode,doc_number,@the_number_str,g.name+j.name+d.cost_date+d.reason+'产生税额'+CAST(CAST(tax AS DECIMAL(20,5)) AS VARCHAR(20))+'元',t.acc_sub_code,t.acc_sub_name,'CNY',1.00000,d.tax,d.tax,null,null,case t.sec_acc_dept when '部门' then '0001' else null end,
case t.sec_acc_dept when '部门' then k.code else null end,case t.sec_acc_dept when '部门' then k.name else null end,
case t.sec_acc_staff when '员工' then '0002' else null end,case t.sec_acc_staff when '员工' then j.code else null end,case t.sec_acc_staff when '员工' then j.name else null end,
null,null,null,
null,null,null,
case when (e.project_code is not null and e.project_code!='') then 'FI03' else null end,e.project_code,h.name,
case t.sec_acc_bank when 0 then '0011' else null end,case t.sec_acc_bank when 0 then (select accnum from [oa2nc].[dbo].v_bankdoc where accname='********银行-活期') else null end,case e.sec_acc_bank when 0 then '********银行-活期' else null end,
null,null,null,null,null,convert(varchar(19),getDate(),120),0,t.cashflow_code,0
from formtable_main_5 a
left join hrmsubcompany b on a.reimbursement_unit=b.id
left join [oa2nc].[dbo].v_orgs c on c.name=b.subcompanyname
left join formtable_main_5_dt1 d on d.mainid=a.id
left join uf_cost_category e on e.id=d.cost_class_02
left join cost_nc_account t on t.acc_sub_code=e.tax_subject_code
left join uf_dept_oa2nc f on f.oa_dept=d.cost_dept
left join [oa2nc].[dbo].v_dept g on f.nc_dept=g.pk_dept
left join [oa2nc].[dbo].v_project h on h.code=e.project_code
left join uf_staff_oa2nc i on i.oa_staff=a.applicant_submit
left join [oa2nc].[dbo].v_psndoc j on i.nc_psndoc=j.pk_psndoc left join oa2nc.dbo.v_dept_normal k on g.name=k.name
where a.requestid=@requestid and d.id=@mx_id and k.pk_org=c.pk_org and j.pk_org=c.pk_org
end
set @dt_sum=@dt_sum+@mx_realmoney+@mx_tax
end
Fetch Next From this_cursor Into @mx_id
end
Close this_cursor
Deallocate this_cursor
end
/*2 市内交通费用明细 生成凭证*/
IF((@total_city_traffic_cost is not null) and @total_city_traffic_cost>0)
begin
DECLARE this_cursor Cursor for
select id from formtable_main_5_dt3 where mainid=(select id from formtable_main_5 where requestid=@requestid)
Open this_cursor Fetch Next From this_cursor Into @mx_id
While ( @@Fetch_Status=0 )
begin
set @mx_tax=(select tax from formtable_main_5_dt3 where id=@mx_id)
SET @mx_realmoney=(select real_money from formtable_main_5_dt3 where id=@mx_id)
set @mx_acc_sub_code=(select '660210' from formtable_main_5_dt3 where id=@mx_id)
set @add_in_subject=(select add_in_subject from uf_cost_category where acc_sub_code=@mx_acc_sub_code)
---1、不进成本
if(@add_in_subject is null)
begin
INSERT INTO [oa2nc].[dbo].[bd_voucher]([pk_corp],[pk_group],[pk_org],[billmaker],[billdate],[creator],[creationtime],[glorgbook_code],[glorgbook],[vouchertype],[tallydate]
,[oarequestID],[oaworkcode],[Oacode],[vbillno],[explanation],[accsubjcode],[accsubjname],[currency],[exchange],[baldebitmny],[baldebitlocalmny],[balcreditmny],[balcreditlocalmny],[checktype1]
,[checkcode1],[checkname1],[checktype2],[checkcode2],[checkname2],[checktype3],[checkcode3],[checkname3],[checktype4],[checkcode4],[checkname4],[checktype5],[checkcode5],[checkname5]
,[checktype6],[checkcode6],[checkname6],[vdef1],[vdef2],[vdef3],[vdef4],[vdef5],[ts],[dr],[pk_cashflow],[cashflowflag])
select distinct c.code,'01',c.code,'v-wantao.zhang',convert(varchar(10),getDate(),120),'v-wantao.zhang',convert(varchar(19),getDate(),120),c.accountcode,c.accountname,@vouchertype,convert(varchar(10),getDate(),120),
@requestid,@oaworkcode,doc_number,@the_number_str,g.name+j.name+d.cost_date+'前往'+d.destination+'产生市内交通费用'+CAST(CAST(real_money AS DECIMAL(20,5)) AS VARCHAR(20))+'元',e.acc_sub_code,e.acc_sub_name,'CNY',1.00000,real_money,real_money,null,null,case e.sec_acc_dept when 0 then '0001' else null end,
case e.sec_acc_dept when 0 then k.code else null end,case e.sec_acc_dept when 0 then k.name else null end,
case e.sec_acc_staff when 0 then '0002' else null end,case e.sec_acc_staff when 0 then j.code else null end,case e.sec_acc_staff when 0 then j.name else null end,
case e.sec_acc_customer when 0 then '004' else null end,null,null,
case e.sec_acc_supplier when 0 then '004' else null end,null,null,
case when (e.project_code is not null and e.project_code!='') then 'FI03' else null end,e.project_code,h.name,
case e.sec_acc_bank when 0 then '0011' else null end,case e.sec_acc_bank when 0 then (select accnum from [oa2nc].[dbo].v_bankdoc where accname='********银行-活期') else null end,case e.sec_acc_bank when 0 then '********银行-活期' else null end,
null,null,null,null,null,convert(varchar(19),getDate(),120),0,e.cashflow_code,0
from formtable_main_5 a
left join hrmsubcompany b on a.reimbursement_unit=b.id
left join [oa2nc].[dbo].v_orgs c on c.name=b.subcompanyname
left join formtable_main_5_dt3 d on d.mainid=a.id
left join uf_cost_category e on e.acc_sub_code=@mx_acc_sub_code
left join uf_dept_oa2nc f on f.oa_dept=d.cost_dept
left join [oa2nc].[dbo].v_dept g on f.nc_dept=g.pk_dept
left join [oa2nc].[dbo].v_project h on h.code=e.project_code
left join uf_staff_oa2nc i on i.oa_staff=a.applicant_submit
left join [oa2nc].[dbo].v_psndoc j on i.nc_psndoc=j.pk_psndoc left join oa2nc.dbo.v_dept_normal k on g.name=k.name
where a.requestid=@requestid and d.id=@mx_id and k.pk_org=c.pk_org and j.pk_org=c.pk_org
if(@mx_tax>0)
begin
INSERT INTO [oa2nc].[dbo].[bd_voucher]([pk_corp],[pk_group],[pk_org],[billmaker],[billdate],[creator],[creationtime],[glorgbook_code],[glorgbook],[vouchertype],[tallydate]
,[oarequestID],[oaworkcode],[Oacode],[vbillno],[explanation],[accsubjcode],[accsubjname],[currency],[exchange],[baldebitmny],[baldebitlocalmny],[balcreditmny],[balcreditlocalmny],[checktype1]
,[checkcode1],[checkname1],[checktype2],[checkcode2],[checkname2],[checktype3],[checkcode3],[checkname3],[checktype4],[checkcode4],[checkname4],[checktype5],[checkcode5],[checkname5]
,[checktype6],[checkcode6],[checkname6],[vdef1],[vdef2],[vdef3],[vdef4],[vdef5],[ts],[dr],[pk_cashflow],[cashflowflag])
select distinct c.code,'01',c.code,'v-wantao.zhang',convert(varchar(10),getDate(),120),'v-wantao.zhang',convert(varchar(19),getDate(),120),c.accountcode,c.accountname,@vouchertype,convert(varchar(10),getDate(),120),
@requestid,@oaworkcode,doc_number,@the_number_str,g.name+j.name+d.cost_date+'前往'+d.destination+'产生税额'+CAST(CAST(tax AS DECIMAL(20,5)) AS VARCHAR(20))+'元',t.acc_sub_code,t.acc_sub_name,'CNY',1.00000,d.tax,d.tax,null,null,case t.sec_acc_dept when '部门' then '0001' else null end,
case t.sec_acc_dept when '部门' then k.code else null end,case t.sec_acc_dept when '部门' then k.name else null end,
case t.sec_acc_staff when '员工' then '0002' else null end,case t.sec_acc_staff when '员工' then j.code else null end,case t.sec_acc_staff when '员工' then j.name else null end,
null,null,null,
null,null,null,
case when (e.project_code is not null and e.project_code!='') then 'FI03' else null end,e.project_code,h.name,
case t.sec_acc_bank when 0 then '0011' else null end,case t.sec_acc_bank when 0 then (select accnum from [oa2nc].[dbo].v_bankdoc where accname='********银行-活期') else null end,case e.sec_acc_bank when 0 then '********银行-活期' else null end,
null,null,null,null,null,convert(varchar(19),getDate(),120),0,t.cashflow_code,0
from formtable_main_5 a
left join hrmsubcompany b on a.reimbursement_unit=b.id
left join [oa2nc].[dbo].v_orgs c on c.name=b.subcompanyname
left join formtable_main_5_dt3 d on d.mainid=a.id
left join uf_cost_category e on e.acc_sub_code=@mx_acc_sub_code
left join cost_nc_account t on t.acc_sub_code=e.tax_subject_code
left join uf_dept_oa2nc f on f.oa_dept=d.cost_dept
left join [oa2nc].[dbo].v_dept g on f.nc_dept=g.pk_dept
left join [oa2nc].[dbo].v_project h on h.code=e.project_code
left join uf_staff_oa2nc i on i.oa_staff=a.applicant_submit
left join [oa2nc].[dbo].v_psndoc j on i.nc_psndoc=j.pk_psndoc left join oa2nc.dbo.v_dept_normal k on g.name=k.name
where a.requestid=@requestid and d.id=@mx_id and k.pk_org=c.pk_org and j.pk_org=c.pk_org
end
set @dt_sum=@dt_sum+@mx_realmoney+@mx_tax
end
---2、进成本
if(@add_in_subject is not null)
begin
INSERT INTO [oa2nc].[dbo].[bd_voucher]([pk_corp],[pk_group],[pk_org],[billmaker],[billdate],[creator],[creationtime],[glorgbook_code],[glorgbook],[vouchertype],[tallydate]
,[oarequestID],[oaworkcode],[Oacode],[vbillno],[explanation],[accsubjcode],[accsubjname],[currency],[exchange],[baldebitmny],[baldebitlocalmny],[balcreditmny],[balcreditlocalmny],[checktype1]
,[checkcode1],[checkname1],[checktype2],[checkcode2],[checkname2],[checktype3],[checkcode3],[checkname3],[checktype4],[checkcode4],[checkname4],[checktype5],[checkcode5],[checkname5]
,[checktype6],[checkcode6],[checkname6],[vdef1],[vdef2],[vdef3],[vdef4],[vdef5],[ts],[dr],[pk_cashflow],[cashflowflag])
select distinct c.code,'01',c.code,'v-wantao.zhang',convert(varchar(10),getDate(),120),'v-wantao.zhang',convert(varchar(19),getDate(),120),c.accountcode,c.accountname,@vouchertype,convert(varchar(10),getDate(),120),
@requestid,@oaworkcode,doc_number,@the_number_str,g.name+j.name+d.cost_date+'前往'+d.destination+'产生市内交通费用'+CAST(CAST(real_money AS DECIMAL(20,5)) AS VARCHAR(20))+'元',e.acc_sub_code,e.acc_sub_name,'CNY',1.00000,real_money,real_money,null,null,case e.sec_acc_dept when 0 then '0001' else null end,
case e.sec_acc_dept when 0 then k.code else null end,case e.sec_acc_dept when 0 then k.name else null end,
case e.sec_acc_staff when 0 then '0002' else null end,case e.sec_acc_staff when 0 then j.code else null end,case e.sec_acc_staff when 0 then j.name else null end,
case e.sec_acc_customer when 0 then '004' else null end,null,null,
case e.sec_acc_supplier when 0 then '004' else null end,null,null,
case when (e.project_code is not null and e.project_code!='') then 'FI03' else null end,e.project_code,h.name,
case e.sec_acc_bank when 0 then '0011' else null end,case e.sec_acc_bank when 0 then (select accnum from [oa2nc].[dbo].v_bankdoc where accname='********银行-活期') else null end,case e.sec_acc_bank when 0 then '********银行-活期' else null end,
null,null,null,null,null,convert(varchar(19),getDate(),120),0,e.cashflow_code,0
from formtable_main_5 a
left join hrmsubcompany b on a.reimbursement_unit=b.id
left join [oa2nc].[dbo].v_orgs c on c.name=b.subcompanyname
left join formtable_main_5_dt3 d on d.mainid=a.id
left join uf_cost_category e on e.acc_sub_code=@mx_acc_sub_code
left join uf_dept_oa2nc f on f.oa_dept=d.cost_dept
left join [oa2nc].[dbo].v_dept g on f.nc_dept=g.pk_dept
left join [oa2nc].[dbo].v_project h on h.code=e.project_code
left join uf_staff_oa2nc i on i.oa_staff=a.applicant_submit
left join [oa2nc].[dbo].v_psndoc j on i.nc_psndoc=j.pk_psndoc left join oa2nc.dbo.v_dept_normal k on g.name=k.name
where a.requestid=@requestid and d.id=@mx_id and k.pk_org=c.pk_org and j.pk_org=c.pk_org
INSERT INTO [oa2nc].[dbo].[bd_voucher]([pk_corp],[pk_group],[pk_org],[billmaker],[billdate],[creator],[creationtime],[glorgbook_code],[glorgbook],[vouchertype],[tallydate]
,[oarequestID],[oaworkcode],[Oacode],[vbillno],[explanation],[accsubjcode],[accsubjname],[currency],[exchange],[baldebitmny],[baldebitlocalmny],[balcreditmny],[balcreditlocalmny],[checktype1]
,[checkcode1],[checkname1],[checktype2],[checkcode2],[checkname2],[checktype3],[checkcode3],[checkname3],[checktype4],[checkcode4],[checkname4],[checktype5],[checkcode5],[checkname5]
,[checktype6],[checkcode6],[checkname6],[vdef1],[vdef2],[vdef3],[vdef4],[vdef5],[ts],[dr],[pk_cashflow],[cashflowflag])
select distinct c.code,'01',c.code,'v-wantao.zhang',convert(varchar(10),getDate(),120),'v-wantao.zhang',convert(varchar(19),getDate(),120),c.accountcode,c.accountname,@vouchertype,convert(varchar(10),getDate(),120),
@requestid,@oaworkcode,doc_number,@the_number_str,g.name+j.name+d.cost_date+'前往'+d.destination+'产生市内交通费用'+CAST(CAST(real_money AS DECIMAL(20,5)) AS VARCHAR(20))+'元',e.add_in_subject_code,e.add_in_subject_name,'CNY',1.00000,real_money,real_money,null,null,case e.sec_acc_dept when 0 then '0001' else null end,
case e.sec_acc_dept when 0 then k.code else null end,case e.sec_acc_dept when 0 then k.name else null end,
case e.sec_acc_staff when 0 then '0002' else null end,case e.sec_acc_staff when 0 then j.code else null end,case e.sec_acc_staff when 0 then j.name else null end,
case e.sec_acc_customer when 0 then '004' else null end,null,null,
case e.sec_acc_supplier when 0 then '004' else null end,null,null,
case when (e.project_code is not null and e.project_code!='') then 'FI03' else null end,e.project_code,h.name,
case e.sec_acc_bank when 0 then '0011' else null end,case e.sec_acc_bank when 0 then (select accnum from [oa2nc].[dbo].v_bankdoc where accname='********银行-活期') else null end,case e.sec_acc_bank when 0 then '********银行-活期' else null end,
null,null,null,null,null,convert(varchar(19),getDate(),120),0,e.cashflow_code,0
from formtable_main_5 a
left join hrmsubcompany b on a.reimbursement_unit=b.id
left join [oa2nc].[dbo].v_orgs c on c.name=b.subcompanyname
left join formtable_main_5_dt3 d on d.mainid=a.id
left join uf_cost_category e on e.acc_sub_code=@mx_acc_sub_code
left join uf_dept_oa2nc f on f.oa_dept=d.cost_dept
left join [oa2nc].[dbo].v_dept g on f.nc_dept=g.pk_dept
left join [oa2nc].[dbo].v_project h on h.code=e.project_code
left join uf_staff_oa2nc i on i.oa_staff=a.applicant_submit
left join [oa2nc].[dbo].v_psndoc j on i.nc_psndoc=j.pk_psndoc left join oa2nc.dbo.v_dept_normal k on g.name=k.name
where a.requestid=@requestid and d.id=@mx_id and k.pk_org=c.pk_org and j.pk_org=c.pk_org
INSERT INTO [oa2nc].[dbo].[bd_voucher]([pk_corp],[pk_group],[pk_org],[billmaker],[billdate],[creator],[creationtime],[glorgbook_code],[glorgbook],[vouchertype],[tallydate]
,[oarequestID],[oaworkcode],[Oacode],[vbillno],[explanation],[accsubjcode],[accsubjname],[currency],[exchange],[baldebitmny],[baldebitlocalmny],[balcreditmny],[balcreditlocalmny],[checktype1]
,[checkcode1],[checkname1],[checktype2],[checkcode2],[checkname2],[checktype3],[checkcode3],[checkname3],[checktype4],[checkcode4],[checkname4],[checktype5],[checkcode5],[checkname5]
,[checktype6],[checkcode6],[checkname6],[vdef1],[vdef2],[vdef3],[vdef4],[vdef5],[ts],[dr],[pk_cashflow],[cashflowflag])
select distinct c.code,'01',c.code,'v-wantao.zhang',convert(varchar(10),getDate(),120),'v-wantao.zhang',convert(varchar(19),getDate(),120),c.accountcode,c.accountname,@vouchertype,convert(varchar(10),getDate(),120),
@requestid,@oaworkcode,doc_number,@the_number_str,g.name+j.name+d.cost_date+'前往'+d.destination+'产生市内交通费用'+CAST(CAST(real_money AS DECIMAL(20,5)) AS VARCHAR(20))+'元',e.add_in_subject_code,e.add_in_subject_name,'CNY',1.00000,null,null,real_money,real_money,case e.sec_acc_dept when 0 then '0001' else null end,
case e.sec_acc_dept when 0 then k.code else null end,case e.sec_acc_dept when 0 then k.name else null end,
case e.sec_acc_staff when 0 then '0002' else null end,case e.sec_acc_staff when 0 then j.code else null end,case e.sec_acc_staff when 0 then j.name else null end,
case e.sec_acc_customer when 0 then '004' else null end,null,null,
case e.sec_acc_supplier when 0 then '004' else null end,null,null,
case when (e.project_code is not null and e.project_code!='') then 'FI03' else null end,e.project_code,h.name,
case e.sec_acc_bank when 0 then '0011' else null end,case e.sec_acc_bank when 0 then (select accnum from [oa2nc].[dbo].v_bankdoc where accname='********银行-活期') else null end,case e.sec_acc_bank when 0 then '********银行-活期' else null end,
null,null,null,null,null,convert(varchar(19),getDate(),120),0,e.cashflow_code,0
from formtable_main_5 a
left join hrmsubcompany b on a.reimbursement_unit=b.id
left join [oa2nc].[dbo].v_orgs c on c.name=b.subcompanyname
left join formtable_main_5_dt3 d on d.mainid=a.id
left join uf_cost_category e on e.acc_sub_code=@mx_acc_sub_code
left join uf_dept_oa2nc f on f.oa_dept=d.cost_dept
left join [oa2nc].[dbo].v_dept g on f.nc_dept=g.pk_dept
left join [oa2nc].[dbo].v_project h on h.code=e.project_code
left join uf_staff_oa2nc i on i.oa_staff=a.applicant_submit
left join [oa2nc].[dbo].v_psndoc j on i.nc_psndoc=j.pk_psndoc left join oa2nc.dbo.v_dept_normal k on g.name=k.name
where a.requestid=@requestid and d.id=@mx_id and k.pk_org=c.pk_org and j.pk_org=c.pk_org
if(@mx_tax>0)
begin
INSERT INTO [oa2nc].[dbo].[bd_voucher]([pk_corp],[pk_group],[pk_org],[billmaker],[billdate],[creator],[creationtime],[glorgbook_code],[glorgbook],[vouchertype],[tallydate]
,[oarequestID],[oaworkcode],[Oacode],[vbillno],[explanation],[accsubjcode],[accsubjname],[currency],[exchange],[baldebitmny],[baldebitlocalmny],[balcreditmny],[balcreditlocalmny],[checktype1]
,[checkcode1],[checkname1],[checktype2],[checkcode2],[checkname2],[checktype3],[checkcode3],[checkname3],[checktype4],[checkcode4],[checkname4],[checktype5],[checkcode5],[checkname5]
,[checktype6],[checkcode6],[checkname6],[vdef1],[vdef2],[vdef3],[vdef4],[vdef5],[ts],[dr],[pk_cashflow],[cashflowflag])
select distinct c.code,'01',c.code,'v-wantao.zhang',convert(varchar(10),getDate(),120),'v-wantao.zhang',convert(varchar(19),getDate(),120),c.accountcode,c.accountname,@vouchertype,convert(varchar(10),getDate(),120),
@requestid,@oaworkcode,doc_number,@the_number_str,g.name+j.name+d.cost_date++'前往'+d.destination+'产生税额'+CAST(CAST(tax AS DECIMAL(20,5)) AS VARCHAR(20))+'元',t.acc_sub_code,t.acc_sub_name,'CNY',1.00000,d.tax,d.tax,null,null,case t.sec_acc_dept when '部门' then '0001' else null end,
case t.sec_acc_dept when '部门' then k.code else null end,case t.sec_acc_dept when '部门' then k.name else null end,
case t.sec_acc_staff when '员工' then '0002' else null end,case t.sec_acc_staff when '员工' then j.code else null end,case t.sec_acc_staff when '员工' then j.name else null end,
null,null,null,
null,null,null,
case when (e.project_code is not null and e.project_code!='') then 'FI03' else null end,e.project_code,h.name,
case t.sec_acc_bank when 0 then '0011' else null end,case t.sec_acc_bank when 0 then (select accnum from [oa2nc].[dbo].v_bankdoc where accname='********银行-活期') else null end,case e.sec_acc_bank when 0 then '********银行-活期' else null end,
null,null,null,null,null,convert(varchar(19),getDate(),120),0,t.cashflow_code,0
from formtable_main_5 a
left join hrmsubcompany b on a.reimbursement_unit=b.id
left join [oa2nc].[dbo].v_orgs c on c.name=b.subcompanyname
left join formtable_main_5_dt3 d on d.mainid=a.id
left join uf_cost_category e on e.acc_sub_code=@mx_acc_sub_code
left join cost_nc_account t on t.acc_sub_code=e.tax_subject_code
left join uf_dept_oa2nc f on f.oa_dept=d.cost_dept
left join [oa2nc].[dbo].v_dept g on f.nc_dept=g.pk_dept
left join [oa2nc].[dbo].v_project h on h.code=e.project_code
left join uf_staff_oa2nc i on i.oa_staff=a.applicant_submit
left join [oa2nc].[dbo].v_psndoc j on i.nc_psndoc=j.pk_psndoc left join oa2nc.dbo.v_dept_normal k on g.name=k.name
where a.requestid=@requestid and d.id=@mx_id and k.pk_org=c.pk_org and j.pk_org=c.pk_org
end
set @dt_sum=@dt_sum+@mx_realmoney+@mx_tax
end
Fetch Next From this_cursor Into @mx_id
end
Close this_cursor
Deallocate this_cursor
end
/*3 业务招待费用明细 生成凭证*/
IF((@total_reception_budget is not null) and @total_reception_budget>0)
begin
DECLARE this_cursor Cursor for
select id from formtable_main_5_dt2 where mainid=(select id from formtable_main_5 where requestid=@requestid)
Open this_cursor Fetch Next From this_cursor Into @mx_id
While ( @@Fetch_Status=0 )
begin
set @mx_tax=(select tax from formtable_main_5_dt2 where id=@mx_id)
SET @mx_realmoney=(select real_money from formtable_main_5_dt2 where id=@mx_id)
set @mx_acc_sub_code=(select case guest_type when 0 then '66020802' when 1 then '66020803' when 2 then '66020801' when 3 then '66020899' end from formtable_main_5_dt2 where id=@mx_id)
set @add_in_subject=(select add_in_subject from uf_cost_category where acc_sub_code=@mx_acc_sub_code)
---1、不进成本
if(@add_in_subject is null)
begin
INSERT INTO [oa2nc].[dbo].[bd_voucher]([pk_corp],[pk_group],[pk_org],[billmaker],[billdate],[creator],[creationtime],[glorgbook_code],[glorgbook],[vouchertype],[tallydate]
,[oarequestID],[oaworkcode],[Oacode],[vbillno],[explanation],[accsubjcode],[accsubjname],[currency],[exchange],[baldebitmny],[baldebitlocalmny],[balcreditmny],[balcreditlocalmny],[checktype1]
,[checkcode1],[checkname1],[checktype2],[checkcode2],[checkname2],[checktype3],[checkcode3],[checkname3],[checktype4],[checkcode4],[checkname4],[checktype5],[checkcode5],[checkname5]
,[checktype6],[checkcode6],[checkname6],[vdef1],[vdef2],[vdef3],[vdef4],[vdef5],[ts],[dr],[pk_cashflow],[cashflowflag])
select distinct c.code,'01',c.code,'v-wantao.zhang',convert(varchar(10),getDate(),120),'v-wantao.zhang',convert(varchar(19),getDate(),120),c.accountcode,c.accountname,@vouchertype,convert(varchar(10),getDate(),120),
@requestid,@oaworkcode,doc_number,@the_number_str,g.name+j.name+d.reception_date+'在'+d.guest_place+'产生业务招待费用'+CAST(CAST(real_money AS DECIMAL(20,5)) AS VARCHAR(20))+'元',e.acc_sub_code,e.acc_sub_name,'CNY',1.00000,real_money,real_money,null,null,case e.sec_acc_dept when 0 then '0001' else null end,
case e.sec_acc_dept when 0 then k.code else null end,case e.sec_acc_dept when 0 then k.name else null end,
case e.sec_acc_staff when 0 then '0002' else null end,case e.sec_acc_staff when 0 then j.code else null end,case e.sec_acc_staff when 0 then j.name else null end,
case e.sec_acc_customer when 0 then '004' else null end,null,null,
case e.sec_acc_supplier when 0 then '004' else null end,null,null,
case when (e.project_code is not null and e.project_code!='') then 'FI03' else null end,e.project_code,h.name,
case e.sec_acc_bank when 0 then '0011' else null end,case e.sec_acc_bank when 0 then (select accnum from [oa2nc].[dbo].v_bankdoc where accname='********银行-活期') else null end,case e.sec_acc_bank when 0 then '********银行-活期' else null end,
null,null,null,null,null,convert(varchar(19),getDate(),120),0,e.cashflow_code,0
from formtable_main_5 a
left join hrmsubcompany b on a.reimbursement_unit=b.id
left join [oa2nc].[dbo].v_orgs c on c.name=b.subcompanyname
left join formtable_main_5_dt2 d on d.mainid=a.id
left join uf_cost_category e on e.acc_sub_code=@mx_acc_sub_code
left join uf_dept_oa2nc f on f.oa_dept=d.cost_dept
left join [oa2nc].[dbo].v_dept g on f.nc_dept=g.pk_dept
left join [oa2nc].[dbo].v_project h on h.code=e.project_code
left join uf_staff_oa2nc i on i.oa_staff=a.applicant_submit
left join [oa2nc].[dbo].v_psndoc j on i.nc_psndoc=j.pk_psndoc left join oa2nc.dbo.v_dept_normal k on g.name=k.name
where a.requestid=@requestid and d.id=@mx_id and k.pk_org=c.pk_org and j.pk_org=c.pk_org
if(@mx_tax>0)
begin
INSERT INTO [oa2nc].[dbo].[bd_voucher]([pk_corp],[pk_group],[pk_org],[billmaker],[billdate],[creator],[creationtime],[glorgbook_code],[glorgbook],[vouchertype],[tallydate]
,[oarequestID],[oaworkcode],[Oacode],[vbillno],[explanation],[accsubjcode],[accsubjname],[currency],[exchange],[baldebitmny],[baldebitlocalmny],[balcreditmny],[balcreditlocalmny],[checktype1]
,[checkcode1],[checkname1],[checktype2],[checkcode2],[checkname2],[checktype3],[checkcode3],[checkname3],[checktype4],[checkcode4],[checkname4],[checktype5],[checkcode5],[checkname5]
,[checktype6],[checkcode6],[checkname6],[vdef1],[vdef2],[vdef3],[vdef4],[vdef5],[ts],[dr],[pk_cashflow],[cashflowflag])
select distinct c.code,'01',c.code,'v-wantao.zhang',convert(varchar(10),getDate(),120),'v-wantao.zhang',convert(varchar(19),getDate(),120),c.accountcode,c.accountname,@vouchertype,convert(varchar(10),getDate(),120),
@requestid,@oaworkcode,doc_number,@the_number_str,g.name+j.name+d.reception_date+'在'+d.guest_place+'产生税额'+CAST(CAST(tax AS DECIMAL(20,5)) AS VARCHAR(20))+'元',t.acc_sub_code,t.acc_sub_name,'CNY',1.00000,d.tax,d.tax,null,null,case t.sec_acc_dept when '部门' then '0001' else null end,
case t.sec_acc_dept when '部门' then k.code else null end,case t.sec_acc_dept when '部门' then k.name else null end,
case t.sec_acc_staff when '员工' then '0002' else null end,case t.sec_acc_staff when '员工' then j.code else null end,case t.sec_acc_staff when '员工' then j.name else null end,
null,null,null,
null,null,null,
case when (e.project_code is not null and e.project_code!='') then 'FI03' else null end,e.project_code,h.name,
case t.sec_acc_bank when 0 then '0011' else null end,case t.sec_acc_bank when 0 then (select accnum from [oa2nc].[dbo].v_bankdoc where accname='********银行-活期') else null end,case e.sec_acc_bank when 0 then '********银行-活期' else null end,
null,null,null,null,null,convert(varchar(19),getDate(),120),0,t.cashflow_code,0
from formtable_main_5 a
left join hrmsubcompany b on a.reimbursement_unit=b.id
left join [oa2nc].[dbo].v_orgs c on c.name=b.subcompanyname
left join formtable_main_5_dt2 d on d.mainid=a.id
left join uf_cost_category e on e.acc_sub_code=@mx_acc_sub_code
left join cost_nc_account t on t.acc_sub_code=e.tax_subject_code
left join uf_dept_oa2nc f on f.oa_dept=d.cost_dept
left join [oa2nc].[dbo].v_dept g on f.nc_dept=g.pk_dept
left join [oa2nc].[dbo].v_project h on h.code=e.project_code
left join uf_staff_oa2nc i on i.oa_staff=a.applicant_submit
left join [oa2nc].[dbo].v_psndoc j on i.nc_psndoc=j.pk_psndoc left join oa2nc.dbo.v_dept_normal k on g.name=k.name
where a.requestid=@requestid and d.id=@mx_id and k.pk_org=c.pk_org and j.pk_org=c.pk_org
end
set @dt_sum=@dt_sum+@mx_realmoney+@mx_tax
end
---2、进成本
if(@add_in_subject is not null)
begin
INSERT INTO [oa2nc].[dbo].[bd_voucher]([pk_corp],[pk_group],[pk_org],[billmaker],[billdate],[creator],[creationtime],[glorgbook_code],[glorgbook],[vouchertype],[tallydate]
,[oarequestID],[oaworkcode],[Oacode],[vbillno],[explanation],[accsubjcode],[accsubjname],[currency],[exchange],[baldebitmny],[baldebitlocalmny],[balcreditmny],[balcreditlocalmny],[checktype1]
,[checkcode1],[checkname1],[checktype2],[checkcode2],[checkname2],[checktype3],[checkcode3],[checkname3],[checktype4],[checkcode4],[checkname4],[checktype5],[checkcode5],[checkname5]
,[checktype6],[checkcode6],[checkname6],[vdef1],[vdef2],[vdef3],[vdef4],[vdef5],[ts],[dr],[pk_cashflow],[cashflowflag])
select distinct c.code,'01',c.code,'v-wantao.zhang',convert(varchar(10),getDate(),120),'v-wantao.zhang',convert(varchar(19),getDate(),120),c.accountcode,c.accountname,@vouchertype,convert(varchar(10),getDate(),120),
@requestid,@oaworkcode,doc_number,@the_number_str,g.name+j.name+d.reception_date+'在'+d.guest_place+'产生业务招待费用'+CAST(CAST(real_money AS DECIMAL(20,5)) AS VARCHAR(20))+'元',e.acc_sub_code,e.acc_sub_name,'CNY',1.00000,real_money,real_money,null,null,case e.sec_acc_dept when 0 then '0001' else null end,
case e.sec_acc_dept when 0 then k.code else null end,case e.sec_acc_dept when 0 then k.name else null end,
case e.sec_acc_staff when 0 then '0002' else null end,case e.sec_acc_staff when 0 then j.code else null end,case e.sec_acc_staff when 0 then j.name else null end,
case e.sec_acc_customer when 0 then '004' else null end,null,null,
case e.sec_acc_supplier when 0 then '004' else null end,null,null,
case when (e.project_code is not null and e.project_code!='') then 'FI03' else null end,e.project_code,h.name,
case e.sec_acc_bank when 0 then '0011' else null end,case e.sec_acc_bank when 0 then (select accnum from [oa2nc].[dbo].v_bankdoc where accname='********银行-活期') else null end,case e.sec_acc_bank when 0 then '********银行-活期' else null end,
null,null,null,null,null,convert(varchar(19),getDate(),120),0,e.cashflow_code,0
from formtable_main_5 a
left join hrmsubcompany b on a.reimbursement_unit=b.id
left join [oa2nc].[dbo].v_orgs c on c.name=b.subcompanyname
left join formtable_main_5_dt2 d on d.mainid=a.id
left join uf_cost_category e on e.acc_sub_code=@mx_acc_sub_code
left join uf_dept_oa2nc f on f.oa_dept=d.cost_dept
left join [oa2nc].[dbo].v_dept g on f.nc_dept=g.pk_dept
left join [oa2nc].[dbo].v_project h on h.code=e.project_code
left join uf_staff_oa2nc i on i.oa_staff=a.applicant_submit
left join [oa2nc].[dbo].v_psndoc j on i.nc_psndoc=j.pk_psndoc left join oa2nc.dbo.v_dept_normal k on g.name=k.name
where a.requestid=@requestid and d.id=@mx_id and k.pk_org=c.pk_org and j.pk_org=c.pk_org
INSERT INTO [oa2nc].[dbo].[bd_voucher]([pk_corp],[pk_group],[pk_org],[billmaker],[billdate],[creator],[creationtime],[glorgbook_code],[glorgbook],[vouchertype],[tallydate]
,[oarequestID],[oaworkcode],[Oacode],[vbillno],[explanation],[accsubjcode],[accsubjname],[currency],[exchange],[baldebitmny],[baldebitlocalmny],[balcreditmny],[balcreditlocalmny],[checktype1]
,[checkcode1],[checkname1],[checktype2],[checkcode2],[checkname2],[checktype3],[checkcode3],[checkname3],[checktype4],[checkcode4],[checkname4],[checktype5],[checkcode5],[checkname5]
,[checktype6],[checkcode6],[checkname6],[vdef1],[vdef2],[vdef3],[vdef4],[vdef5],[ts],[dr],[pk_cashflow],[cashflowflag])
select distinct c.code,'01',c.code,'v-wantao.zhang',convert(varchar(10),getDate(),120),'v-wantao.zhang',convert(varchar(19),getDate(),120),c.accountcode,c.accountname,@vouchertype,convert(varchar(10),getDate(),120),
@requestid,@oaworkcode,doc_number,@the_number_str,g.name+j.name+d.reception_date+'在'+d.guest_place+'产生业务招待费用'+CAST(CAST(real_money AS DECIMAL(20,5)) AS VARCHAR(20))+'元',e.add_in_subject_code,e.add_in_subject_name,'CNY',1.00000,real_money,real_money,null,null,case e.sec_acc_dept when 0 then '0001' else null end,
case e.sec_acc_dept when 0 then k.code else null end,case e.sec_acc_dept when 0 then k.name else null end,
case e.sec_acc_staff when 0 then '0002' else null end,case e.sec_acc_staff when 0 then j.code else null end,case e.sec_acc_staff when 0 then j.name else null end,
case e.sec_acc_customer when 0 then '004' else null end,null,null,
case e.sec_acc_supplier when 0 then '004' else null end,null,null,
case when (e.project_code is not null and e.project_code!='') then 'FI03' else null end,e.project_code,h.name,
case e.sec_acc_bank when 0 then '0011' else null end,case e.sec_acc_bank when 0 then (select accnum from [oa2nc].[dbo].v_bankdoc where accname='********银行-活期') else null end,case e.sec_acc_bank when 0 then '********银行-活期' else null end,
null,null,null,null,null,convert(varchar(19),getDate(),120),0,e.cashflow_code,0
from formtable_main_5 a
left join hrmsubcompany b on a.reimbursement_unit=b.id
left join [oa2nc].[dbo].v_orgs c on c.name=b.subcompanyname
left join formtable_main_5_dt2 d on d.mainid=a.id
left join uf_cost_category e on e.acc_sub_code=@mx_acc_sub_code
left join uf_dept_oa2nc f on f.oa_dept=d.cost_dept
left join [oa2nc].[dbo].v_dept g on f.nc_dept=g.pk_dept
left join [oa2nc].[dbo].v_project h on h.code=e.project_code
left join uf_staff_oa2nc i on i.oa_staff=a.applicant_submit
left join [oa2nc].[dbo].v_psndoc j on i.nc_psndoc=j.pk_psndoc left join oa2nc.dbo.v_dept_normal k on g.name=k.name
where a.requestid=@requestid and d.id=@mx_id and k.pk_org=c.pk_org and j.pk_org=c.pk_org
INSERT INTO [oa2nc].[dbo].[bd_voucher]([pk_corp],[pk_group],[pk_org],[billmaker],[billdate],[creator],[creationtime],[glorgbook_code],[glorgbook],[vouchertype],[tallydate]
,[oarequestID],[oaworkcode],[Oacode],[vbillno],[explanation],[accsubjcode],[accsubjname],[currency],[exchange],[baldebitmny],[baldebitlocalmny],[balcreditmny],[balcreditlocalmny],[checktype1]
,[checkcode1],[checkname1],[checktype2],[checkcode2],[checkname2],[checktype3],[checkcode3],[checkname3],[checktype4],[checkcode4],[checkname4],[checktype5],[checkcode5],[checkname5]
,[checktype6],[checkcode6],[checkname6],[vdef1],[vdef2],[vdef3],[vdef4],[vdef5],[ts],[dr],[pk_cashflow],[cashflowflag])
select distinct c.code,'01',c.code,'v-wantao.zhang',convert(varchar(10),getDate(),120),'v-wantao.zhang',convert(varchar(19),getDate(),120),c.accountcode,c.accountname,@vouchertype,convert(varchar(10),getDate(),120),
@requestid,@oaworkcode,doc_number,@the_number_str,g.name+j.name+d.reception_date+'在'+d.guest_place+'产生业务招待费用'+CAST(CAST(real_money AS DECIMAL(20,5)) AS VARCHAR(20))+'元',e.add_in_subject_code,e.add_in_subject_name,'CNY',1.00000,null,null,real_money,real_money,case e.sec_acc_dept when 0 then '0001' else null end,
case e.sec_acc_dept when 0 then k.code else null end,case e.sec_acc_dept when 0 then k.name else null end,
case e.sec_acc_staff when 0 then '0002' else null end,case e.sec_acc_staff when 0 then j.code else null end,case e.sec_acc_staff when 0 then j.name else null end,
case e.sec_acc_customer when 0 then '004' else null end,null,null,
case e.sec_acc_supplier when 0 then '004' else null end,null,null,
case when (e.project_code is not null and e.project_code!='') then 'FI03' else null end,e.project_code,h.name,
case e.sec_acc_bank when 0 then '0011' else null end,case e.sec_acc_bank when 0 then (select accnum from [oa2nc].[dbo].v_bankdoc where accname='********银行-活期') else null end,case e.sec_acc_bank when 0 then '********银行-活期' else null end,
null,null,null,null,null,convert(varchar(19),getDate(),120),0,e.cashflow_code,0
from formtable_main_5 a
left join hrmsubcompany b on a.reimbursement_unit=b.id
left join [oa2nc].[dbo].v_orgs c on c.name=b.subcompanyname
left join formtable_main_5_dt2 d on d.mainid=a.id
left join uf_cost_category e on e.acc_sub_code=@mx_acc_sub_code
left join uf_dept_oa2nc f on f.oa_dept=d.cost_dept
left join [oa2nc].[dbo].v_dept g on f.nc_dept=g.pk_dept
left join [oa2nc].[dbo].v_project h on h.code=e.project_code
left join uf_staff_oa2nc i on i.oa_staff=a.applicant_submit
left join [oa2nc].[dbo].v_psndoc j on i.nc_psndoc=j.pk_psndoc left join oa2nc.dbo.v_dept_normal k on g.name=k.name
where a.requestid=@requestid and d.id=@mx_id and k.pk_org=c.pk_org and j.pk_org=c.pk_org
if(@mx_tax>0)
begin
INSERT INTO [oa2nc].[dbo].[bd_voucher]([pk_corp],[pk_group],[pk_org],[billmaker],[billdate],[creator],[creationtime],[glorgbook_code],[glorgbook],[vouchertype],[tallydate]
,[oarequestID],[oaworkcode],[Oacode],[vbillno],[explanation],[accsubjcode],[accsubjname],[currency],[exchange],[baldebitmny],[baldebitlocalmny],[balcreditmny],[balcreditlocalmny],[checktype1]
,[checkcode1],[checkname1],[checktype2],[checkcode2],[checkname2],[checktype3],[checkcode3],[checkname3],[checktype4],[checkcode4],[checkname4],[checktype5],[checkcode5],[checkname5]
,[checktype6],[checkcode6],[checkname6],[vdef1],[vdef2],[vdef3],[vdef4],[vdef5],[ts],[dr],[pk_cashflow],[cashflowflag])
select distinct c.code,'01',c.code,'v-wantao.zhang',convert(varchar(10),getDate(),120),'v-wantao.zhang',convert(varchar(19),getDate(),120),c.accountcode,c.accountname,@vouchertype,convert(varchar(10),getDate(),120),
@requestid,@oaworkcode,doc_number,@the_number_str,g.name+j.name+d.reception_date+'在'+d.guest_place+'发生税额'+CAST(CAST(real_money AS DECIMAL(20,5)) AS VARCHAR(20))+'元',t.acc_sub_code,t.acc_sub_name,'CNY',1.00000,d.tax,d.tax,null,null,case t.sec_acc_dept when '部门' then '0001' else null end,
case t.sec_acc_dept when '部门' then k.code else null end,case t.sec_acc_dept when '部门' then k.name else null end,
case t.sec_acc_staff when '员工' then '0002' else null end,case t.sec_acc_staff when '员工' then j.code else null end,case t.sec_acc_staff when '员工' then j.name else null end,
null,null,null,
null,null,null,
case when (e.project_code is not null and e.project_code!='') then 'FI03' else null end,e.project_code,h.name,
case t.sec_acc_bank when 0 then '0011' else null end,case t.sec_acc_bank when 0 then (select accnum from [oa2nc].[dbo].v_bankdoc where accname='********银行-活期') else null end,case e.sec_acc_bank when 0 then '********银行-活期' else null end,
null,null,null,null,null,convert(varchar(19),getDate(),120),0,t.cashflow_code,0
from formtable_main_5 a
left join hrmsubcompany b on a.reimbursement_unit=b.id
left join [oa2nc].[dbo].v_orgs c on c.name=b.subcompanyname
left join formtable_main_5_dt2 d on d.mainid=a.id
left join uf_cost_category e on e.acc_sub_code=@mx_acc_sub_code
left join cost_nc_account t on t.acc_sub_code=e.tax_subject_code
left join uf_dept_oa2nc f on f.oa_dept=d.cost_dept
left join [oa2nc].[dbo].v_dept g on f.nc_dept=g.pk_dept
left join [oa2nc].[dbo].v_project h on h.code=e.project_code
left join uf_staff_oa2nc i on i.oa_staff=a.applicant_submit
left join [oa2nc].[dbo].v_psndoc j on i.nc_psndoc=j.pk_psndoc left join oa2nc.dbo.v_dept_normal k on g.name=k.name
where a.requestid=@requestid and d.id=@mx_id and k.pk_org=c.pk_org and j.pk_org=c.pk_org
end
set @dt_sum=@dt_sum+@mx_realmoney+@mx_tax
end
Fetch Next From this_cursor Into @mx_id
end
Close this_cursor
Deallocate this_cursor
end
INSERT INTO [oa2nc].[dbo].[bd_voucher]([pk_corp],[pk_group],[pk_org],[billmaker],[billdate],[creator],[creationtime],[glorgbook_code],[glorgbook],[vouchertype],[tallydate]
,[oarequestID],[oaworkcode],[Oacode],[vbillno],[explanation],[accsubjcode],[accsubjname],[currency],[exchange],[baldebitmny],[baldebitlocalmny],[balcreditmny],[balcreditlocalmny],[checktype1]
,[checkcode1],[checkname1],[checktype2],[checkcode2],[checkname2],[checktype3],[checkcode3],[checkname3],[checktype4],[checkcode4],[checkname4],[checktype5],[checkcode5],[checkname5]
,[checktype6],[checkcode6],[checkname6],[vdef1],[vdef2],[vdef3],[vdef4],[vdef5],[ts],[dr],[pk_cashflow],[cashflowflag])
select distinct c.code,'01',c.code,'v-wantao.zhang',convert(varchar(10),getDate(),120),'v-wantao.zhang',convert(varchar(19),getDate(),120),c.accountcode,c.accountname,@vouchertype,convert(varchar(10),getDate(),120),
@requestid,@oaworkcode,doc_number,@the_number_str,j.name+'因'+a.applay_reason+'产生日常费用'+CAST(CAST(@dt_sum AS DECIMAL(20,5)) AS VARCHAR(20))+'元',t.acc_sub_code,t.acc_sub_name,'CNY',1.00000,null,null,@dt_sum,@dt_sum,case t.sec_acc_dept when '部门' then '0001' else null end,
case t.sec_acc_dept when '部门' then k.code else null end,case t.sec_acc_dept when '部门' then k.name else null end,
case t.sec_acc_staff when '员工' then '0002' else null end,case t.sec_acc_staff when '员工' then j.code else null end,case t.sec_acc_staff when '员工' then j.name else null end,
null,null,null,
null,null,null,
case when t.project_name is not null then 'FI03' else null end,h.code,h.name,
case when t.sec_acc_bank is not null then '0011' else null end,case when t.sec_acc_bank is not null then (select accnum from [oa2nc].[dbo].v_bankdoc where accname='********银行-活期') else null end,case when t.sec_acc_bank is not null then '********银行-活期' else null end,
null,null,null,null,null,convert(varchar(19),getDate(),120),0,t.cashflow_code,0
from formtable_main_5 a
left join hrmsubcompany b on a.reimbursement_unit=b.id
left join [oa2nc].[dbo].v_orgs c on c.name=b.subcompanyname
left join uf_dept_oa2nc f on f.oa_dept=a.applay_dept
left join [oa2nc].[dbo].v_dept g on f.nc_dept=g.pk_dept
left join cost_nc_account t on t.acc_sub_code='224103'
left join [oa2nc].[dbo].v_project h on h.name=t.project_name
left join uf_staff_oa2nc i on i.oa_staff=a.applicant_submit
left join [oa2nc].[dbo].v_psndoc j on i.nc_psndoc=j.pk_psndoc left join oa2nc.dbo.v_dept_normal k on g.name=k.name
where a.requestid=@requestid and k.pk_org=c.pk_org and j.pk_org=c.pk_org
/*4 针对冲销或者实际报销金额生成凭证 */
/*---当实际报销支付金额大于0*/
if(@actual_pay_num>0)
begin
INSERT INTO [oa2nc].[dbo].[bd_voucher]([pk_corp],[pk_group],[pk_org],[billmaker],[billdate],[creator],[creationtime],[glorgbook_code],[glorgbook],[vouchertype],[tallydate]
,[oarequestID],[oaworkcode],[Oacode],[vbillno],[explanation],[accsubjcode],[accsubjname],[currency],[exchange],[baldebitmny],[baldebitlocalmny],[balcreditmny],[balcreditlocalmny],[checktype1]
,[checkcode1],[checkname1],[checktype2],[checkcode2],[checkname2],[checktype3],[checkcode3],[checkname3],[checktype4],[checkcode4],[checkname4],[checktype5],[checkcode5],[checkname5]
,[checktype6],[checkcode6],[checkname6],[vdef1],[vdef2],[vdef3],[vdef4],[vdef5],[ts],[dr],[pk_cashflow],[cashflowflag])
select distinct c.code,'01',c.code,'v-wantao.zhang',convert(varchar(10),getDate(),120),'v-wantao.zhang',convert(varchar(19),getDate(),120),c.accountcode,c.accountname,@vouchertype,convert(varchar(10),getDate(),120),
@requestid,@oaworkcode,doc_number,@the_number_str,j.name+'因'+a.applay_reason+'产生日常费用'+CAST(CAST(@dt_sum AS DECIMAL(20,5)) AS VARCHAR(20))+'元'+'实际支付'+CAST(CAST(@actual_pay_num AS DECIMAL(20,5)) AS VARCHAR(20))+'元',l.acc_sub_code,l.acc_sub_name,'CNY',1.00000,@actual_pay_num,@actual_pay_num,null,null,
case l.sec_acc_dept when '部门' then '0001' else null end,
case l.sec_acc_dept when '部门' then k.code else null end,case l.sec_acc_dept when '部门' then k.name else null end,
case l.sec_acc_staff when '员工' then '0002' else null end,case l.sec_acc_staff when '员工' then j.code else null end,case l.sec_acc_staff when '员工' then j.name else null end,
null,null,null,
null,null,null,
null,null,null,
null,null,null,
null,null,null,null,null,convert(varchar(19),getDate(),120),0,null,0
from formtable_main_5 a
left join hrmsubcompany b on a.reimbursement_unit=b.id
left join [oa2nc].[dbo].v_orgs c on c.name=b.subcompanyname
left join cost_nc_account l on l.acc_sub_code='224103'
left join uf_dept_oa2nc f on f.oa_dept=a.applay_dept
left join [oa2nc].[dbo].v_dept g on f.nc_dept=g.pk_dept
left join [oa2nc].[dbo].v_project h on h.code=l.project_name
left join uf_staff_oa2nc i on i.oa_staff=a.applicant_submit
left join [oa2nc].[dbo].v_psndoc j on i.nc_psndoc=j.pk_psndoc left join oa2nc.dbo.v_dept_normal k on g.name=k.name
where a.requestid=@requestid and k.pk_org=c.pk_org and j.pk_org=c.pk_org
INSERT INTO [oa2nc].[dbo].[bd_voucher]([pk_corp],[pk_group],[pk_org],[billmaker],[billdate],[creator],[creationtime],[glorgbook_code],[glorgbook],[vouchertype],[tallydate]
,[oarequestID],[oaworkcode],[Oacode],[vbillno],[explanation],[accsubjcode],[accsubjname],[currency],[exchange],[baldebitmny],[baldebitlocalmny],[balcreditmny],[balcreditlocalmny],[checktype1]
,[checkcode1],[checkname1],[checktype2],[checkcode2],[checkname2],[checktype3],[checkcode3],[checkname3],[checktype4],[checkcode4],[checkname4],[checktype5],[checkcode5],[checkname5]
,[checktype6],[checkcode6],[checkname6],[vdef1],[vdef2],[vdef3],[vdef4],[vdef5],[ts],[dr],[pk_cashflow],[cashflowflag])
select distinct c.code,'01',c.code,'v-wantao.zhang',convert(varchar(10),getDate(),120),'v-wantao.zhang',convert(varchar(19),getDate(),120),c.accountcode,c.accountname,@vouchertype,convert(varchar(10),getDate(),120),
@requestid,@oaworkcode,doc_number,@the_number_str,j.name+'因'+a.applay_reason+'产生日常费用'+CAST(CAST(@dt_sum AS DECIMAL(20,5)) AS VARCHAR(20))+'元'+'实际支付'+CAST(CAST(@actual_pay_num AS DECIMAL(20,5)) AS VARCHAR(20))+'元',t.acc_sub_code,t.acc_sub_name,'CNY',1.00000,null,null,@actual_pay_num,@actual_pay_num,
case when t.sec_acc_bank is not null then '0011' else null end,case when t.sec_acc_bank is not null then (select accnum from [oa2nc].[dbo].v_bankdoc where accname='********银行-活期') else null end,case when t.sec_acc_bank is not null then '********银行-活期' else null end,
case t.sec_acc_dept when '部门' then '0001' else null end,
case t.sec_acc_dept when '部门' then k.code else null end,case t.sec_acc_dept when '部门' then k.name else null end,
case t.sec_acc_staff when '员工' then '0002' else null end,case t.sec_acc_staff when '员工' then j.code else null end,case t.sec_acc_staff when '员工' then j.name else null end,
null,null,null,
null,null,null,
case when (t.project_name is not null and t.project_name!='') then 'FI03' else null end,h.code,h.name,
null,null,null,null,null,convert(varchar(19),getDate(),120),0,1124,1
from formtable_main_5 a
left join hrmsubcompany b on a.reimbursement_unit=b.id
left join [oa2nc].[dbo].v_orgs c on c.name=b.subcompanyname
left join uf_dept_oa2nc f on f.oa_dept=a.applay_dept
left join [oa2nc].[dbo].v_dept g on f.nc_dept=g.pk_dept
left join cost_nc_account t on t.acc_sub_code='100202'
left join [oa2nc].[dbo].v_project h on h.name=t.project_name
left join uf_staff_oa2nc i on i.oa_staff=a.applicant_submit
left join [oa2nc].[dbo].v_psndoc j on i.nc_psndoc=j.pk_psndoc left join oa2nc.dbo.v_dept_normal k on g.name=k.name
where a.requestid=@requestid and k.pk_org=c.pk_org and j.pk_org=c.pk_org
end
/*冲销金额大于0或者不为空时--*/
if((@write_off_num is not null) and (@write_off_num>0))
begin
INSERT INTO [oa2nc].[dbo].[bd_voucher]([pk_corp],[pk_group],[pk_org],[billmaker],[billdate],[creator],[creationtime],[glorgbook_code],[glorgbook],[vouchertype],[tallydate]
,[oarequestID],[oaworkcode],[Oacode],[vbillno],[explanation],[accsubjcode],[accsubjname],[currency],[exchange],[baldebitmny],[baldebitlocalmny],[balcreditmny],[balcreditlocalmny],[checktype1]
,[checkcode1],[checkname1],[checktype2],[checkcode2],[checkname2],[checktype3],[checkcode3],[checkname3],[checktype4],[checkcode4],[checkname4],[checktype5],[checkcode5],[checkname5]
,[checktype6],[checkcode6],[checkname6],[vdef1],[vdef2],[vdef3],[vdef4],[vdef5],[ts],[dr],[pk_cashflow],[cashflowflag])
select distinct c.code,'01',c.code,'v-wantao.zhang',convert(varchar(10),getDate(),120),'v-wantao.zhang',convert(varchar(19),getDate(),120),c.accountcode,c.accountname,@vouchertype,convert(varchar(10),getDate(),120),
@requestid,@oaworkcode,doc_number,@the_number_str,j.name+'因'+a.applay_reason+'产生日常费用'+CAST(CAST(@dt_sum AS DECIMAL(20,5)) AS VARCHAR(20))+'元'+'其中冲销'+CAST(CAST(@write_off_num AS DECIMAL(20,5)) AS VARCHAR(20))+'元',l.acc_sub_code,l.acc_sub_name,'CNY',1.00000,@write_off_num,@write_off_num,null,null,
case l.sec_acc_dept when '部门' then '0001' else null end,case l.sec_acc_dept when '部门' then k.code else null end,case l.sec_acc_dept when '部门' then k.name else null end,
case l.sec_acc_staff when '员工' then '0002' else null end,case l.sec_acc_staff when '员工' then j.code else null end,case l.sec_acc_staff when '员工' then j.name else null end,
null,null,null,
null,null,null,
null,null,null,
null,null,null,
null,null,null,null,null,convert(varchar(19),getDate(),120),0,l.cashflow_code,1
from formtable_main_5 a
left join hrmsubcompany b on a.reimbursement_unit=b.id
left join [oa2nc].[dbo].v_orgs c on c.name=b.subcompanyname
left join cost_nc_account l on l.acc_sub_code='224103'
left join uf_dept_oa2nc f on f.oa_dept=a.applay_dept
left join [oa2nc].[dbo].v_dept g on f.nc_dept=g.pk_dept
left join [oa2nc].[dbo].v_project h on h.name=l.project_name
left join uf_staff_oa2nc i on i.oa_staff=a.applicant_submit
left join [oa2nc].[dbo].v_psndoc j on i.nc_psndoc=j.pk_psndoc left join oa2nc.dbo.v_dept_normal k on g.name=k.name
where a.requestid=@requestid and k.pk_org=c.pk_org and j.pk_org=c.pk_org
INSERT INTO [oa2nc].[dbo].[bd_voucher]([pk_corp],[pk_group],[pk_org],[billmaker],[billdate],[creator],[creationtime],[glorgbook_code],[glorgbook],[vouchertype],[tallydate]
,[oarequestID],[oaworkcode],[Oacode],[vbillno],[explanation],[accsubjcode],[accsubjname],[currency],[exchange],[baldebitmny],[baldebitlocalmny],[balcreditmny],[balcreditlocalmny],[checktype1]
,[checkcode1],[checkname1],[checktype2],[checkcode2],[checkname2],[checktype3],[checkcode3],[checkname3],[checktype4],[checkcode4],[checkname4],[checktype5],[checkcode5],[checkname5]
,[checktype6],[checkcode6],[checkname6],[vdef1],[vdef2],[vdef3],[vdef4],[vdef5],[ts],[dr],[pk_cashflow],[cashflowflag])
select distinct c.code,'01',c.code,'v-wantao.zhang',convert(varchar(10),getDate(),120),'v-wantao.zhang',convert(varchar(19),getDate(),120),c.accountcode,c.accountname,@vouchertype,convert(varchar(10),getDate(),120),
@requestid,@oaworkcode,doc_number,@the_number_str,j.name+'因'+a.applay_reason+'产生日常费用'+CAST(CAST(@dt_sum AS DECIMAL(20,5)) AS VARCHAR(20))+'元'+'其中冲销'+CAST(CAST(@write_off_num AS DECIMAL(20,5)) AS VARCHAR(20))+'元',t.acc_sub_code,t.acc_sub_name,'CNY',1.00000,null,null,@write_off_num,@write_off_num,case t.sec_acc_dept when '部门' then '0001' else null end,
case t.sec_acc_dept when '部门' then k.code else null end,case t.sec_acc_dept when '部门' then k.name else null end,
case t.sec_acc_staff when '员工' then '0002' else null end,case t.sec_acc_staff when '员工' then j.code else null end,case t.sec_acc_staff when '员工' then j.name else null end,
null,null,null,
null,null,null,
null,null,null,
case t.sec_acc_bank when '银行' then '0011' else null end,case t.sec_acc_bank when '银行' then (select accnum from [oa2nc].[dbo].v_bankdoc where accname='********银行-活期') else null end,case t.sec_acc_bank when '银行' then '********银行-活期' else null end,
null,null,null,null,null,convert(varchar(19),getDate(),120),0,t.cashflow_code,1
from formtable_main_5 a
left join hrmsubcompany b on a.reimbursement_unit=b.id
left join [oa2nc].[dbo].v_orgs c on c.name=b.subcompanyname
left join cost_nc_account t on t.acc_sub_code='12210204'
left join uf_dept_oa2nc f on f.oa_dept=a.applay_dept
left join [oa2nc].[dbo].v_dept g on f.nc_dept=g.pk_dept
left join [oa2nc].[dbo].v_project h on h.code=t.project_name
left join uf_staff_oa2nc i on i.oa_staff=a.applicant_submit
left join [oa2nc].[dbo].v_psndoc j on i.nc_psndoc=j.pk_psndoc left join oa2nc.dbo.v_dept_normal k on g.name=k.name
where a.requestid=@requestid and k.pk_org=c.pk_org and j.pk_org=c.pk_org
end
GO
差旅费用报销
USE [ecology]
GO
/****** Object: StoredProcedure [dbo].[create_voucher_travel] Script Date: 2017/2/17 18:20:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[create_voucher_travel](@requestid int)
as
DECLARE @vouchertype varchar(10)
DECLARE @oaworkcode int
set @oaworkcode=0
DECLARE @cost_type nvarchar(20)
set @cost_type='CLFYBX'
DECLARE @the_number_str varchar(100)
execute getVbillno @cost_type,@the_number_str output
DECLARE @mx_id int
DECLARE @mx_tax float
DECLARE @mx_realmoney float
DECLARE @add_in_subject nvarchar(20)
DECLARE @dt_sum float
DECLARE @mx_acc_sub_code nvarchar(40)
set @dt_sum=0
DECLARE @total_travel float
DECLARE @total_meal float
DECLARE @total_accom float
DECLARE @total_enter float
DECLARE @actual_pay_num float
DECLARE @write_off_num float
set @actual_pay_num=(select actual_pay_num from formtable_main_22 where requestid=@requestid)
set @write_off_num=(select write_off_num from formtable_main_22 where requestid=@requestid)
set @vouchertype=(select case actual_pay_num when 0 then '02' else '01' end from formtable_main_22 where requestid=@requestid)
set @total_travel=(select total_travel from formtable_main_22 where requestid=@requestid)
set @total_meal=(select total_meal from formtable_main_22 where requestid=@requestid)
set @total_accom=(select total_accom from formtable_main_22 where requestid=@requestid)
set @total_enter=(select total_enter from formtable_main_22 where requestid=@requestid)
/*1 差旅交通明细 生成凭证*/
IF((@total_travel is not null) and @total_travel>0)
begin
DECLARE this_cursor Cursor for
select id from formtable_main_22_dt1 where mainid=(select id from formtable_main_22 where requestid=@requestid)
Open this_cursor Fetch Next From this_cursor Into @mx_id
While ( @@Fetch_Status=0 )
begin
set @mx_tax=(select tax from formtable_main_22_dt1 where id=@mx_id)
SET @mx_realmoney=(select real_money from formtable_main_22_dt1 where id=@mx_id)
set @mx_acc_sub_code=(select case vehicle
when 16 then '66020601'
when 91 then '66020601'
when 17 then '66020602'
when 18 then '66020602'
when 19 then '66020602' end
from formtable_main_22_dt1 where id=@mx_id)
set @add_in_subject=(select add_in_subject from uf_cost_category where acc_sub_code=@mx_acc_sub_code)
---1、不进成本
if(@add_in_subject is null)
begin
INSERT INTO [oa2nc].[dbo].[bd_voucher]([pk_corp],[pk_group],[pk_org],[billmaker],[billdate],[creator],[creationtime],[glorgbook_code],[glorgbook],[vouchertype],[tallydate]
,[oarequestID],[oaworkcode],[Oacode],[vbillno],[explanation],[accsubjcode],[accsubjname],[currency],[exchange],[baldebitmny],[baldebitlocalmny],[balcreditmny],[balcreditlocalmny],[checktype1]
,[checkcode1],[checkname1],[checktype2],[checkcode2],[checkname2],[checktype3],[checkcode3],[checkname3],[checktype4],[checkcode4],[checkname4],[checktype5],[checkcode5],[checkname5]
,[checktype6],[checkcode6],[checkname6],[vdef1],[vdef2],[vdef3],[vdef4],[vdef5],[ts],[dr],[pk_cashflow],[cashflowflag])
select c.code,'01',c.code,'v-wantao.zhang',convert(varchar(10),getDate(),120),'v-wantao.zhang',convert(varchar(19),getDate(),120),c.accountcode,c.accountname,@vouchertype,convert(varchar(10),getDate(),120),
@requestid,@oaworkcode,doc_number,@the_number_str,g.name+j.name+d.cost_date_01+'从'+d.departure_place+'出发到'+d.destination+'产生差旅交通费用'+CAST(CAST(real_money AS DECIMAL(20,5)) AS VARCHAR(20))+'元',e.acc_sub_code,e.acc_sub_name,'CNY',1.00000,real_money,real_money,null,null,case e.sec_acc_dept when 0 then '0001' else null end,
case e.sec_acc_dept when 0 then k.code else null end,case e.sec_acc_dept when 0 then k.name else null end,
case e.sec_acc_staff when 0 then '0002' else null end,case e.sec_acc_staff when 0 then j.code else null end,case e.sec_acc_staff when 0 then j.name else null end,
case e.sec_acc_customer when 0 then '004' else null end,null,null,
case e.sec_acc_supplier when 0 then '004' else null end,null,null,
case when e.project_code is not null then 'FI03' else null end,e.project_code,h.name,
case e.sec_acc_bank when 0 then '0011' else null end,case e.sec_acc_bank when 0 then (select accnum from oa2nc.dbo.v_bankdoc where accname='********银行-活期') else null end,case e.sec_acc_bank when 0 then '********银行-活期' else null end,
null,null,null,null,null,convert(varchar(19),getDate(),120),0,e.cashflow_code,0
from formtable_main_22 a
left join hrmsubcompany b on a.reimbursement_unit=b.id
left join oa2nc.dbo.v_orgs c on c.name=b.subcompanyname
left join formtable_main_22_dt1 d on d.mainid=a.id
left join uf_cost_category e on e.acc_sub_code=@mx_acc_sub_code
left join uf_dept_oa2nc f on f.oa_dept=d.cost_dept
left join oa2nc.dbo.v_dept g on f.nc_dept=g.pk_dept
left join oa2nc.dbo.v_project h on h.code=e.project_code
left join uf_staff_oa2nc i on i.oa_staff=a.applicant_submit
left join [oa2nc].[dbo].v_psndoc j on i.nc_psndoc=j.pk_psndoc left join oa2nc.dbo.v_dept_normal k on g.name=k.name
where a.requestid=@requestid and d.id=@mx_id and k.pk_org=c.pk_org and j.pk_org=c.pk_org
if(@mx_tax>0)
begin
INSERT INTO [oa2nc].[dbo].[bd_voucher]([pk_corp],[pk_group],[pk_org],[billmaker],[billdate],[creator],[creationtime],[glorgbook_code],[glorgbook],[vouchertype],[tallydate]
,[oarequestID],[oaworkcode],[Oacode],[vbillno],[explanation],[accsubjcode],[accsubjname],[currency],[exchange],[baldebitmny],[baldebitlocalmny],[balcreditmny],[balcreditlocalmny],[checktype1]
,[checkcode1],[checkname1],[checktype2],[checkcode2],[checkname2],[checktype3],[checkcode3],[checkname3],[checktype4],[checkcode4],[checkname4],[checktype5],[checkcode5],[checkname5]
,[checktype6],[checkcode6],[checkname6],[vdef1],[vdef2],[vdef3],[vdef4],[vdef5],[ts],[dr],[pk_cashflow],[cashflowflag])
select c.code,'01',c.code,'v-wantao.zhang',convert(varchar(10),getDate(),120),'v-wantao.zhang',convert(varchar(19),getDate(),120),c.accountcode,c.accountname,@vouchertype,convert(varchar(10),getDate(),120),
@requestid,@oaworkcode,doc_number,@the_number_str,g.name+j.name+d.cost_date_01+'从'+d.departure_place+'出发到'+d.destination+'产生差旅费用税额'+CAST(CAST(d.tax AS DECIMAL(20,5)) AS VARCHAR(20))+'元',t.acc_sub_code,t.acc_sub_name,'CNY',1.00000,d.tax,d.tax,null,null,case t.sec_acc_dept when '部门' then '0001' else null end,
case t.sec_acc_dept when '部门' then k.code else null end,case t.sec_acc_dept when '部门' then k.name else null end,
case t.sec_acc_staff when '员工' then '0002' else null end,case t.sec_acc_staff when '员工' then j.code else null end,case t.sec_acc_staff when '员工' then j.name else null end,
null,null,null,
null,null,null,
case when e.project_code is not null then 'FI03' else null end,e.project_code,h.name,
case t.sec_acc_bank when 0 then '0011' else null end,case t.sec_acc_bank when 0 then (select accnum from oa2nc.dbo.v_bankdoc where accname='********银行-活期') else null end,case e.sec_acc_bank when 0 then '********银行-活期' else null end,
null,null,null,null,null,convert(varchar(19),getDate(),120),0,t.cashflow_code,0
from formtable_main_22 a
left join hrmsubcompany b on a.reimbursement_unit=b.id
left join oa2nc.dbo.v_orgs c on c.name=b.subcompanyname
left join formtable_main_22_dt1 d on d.mainid=a.id
left join uf_cost_category e on e.acc_sub_code=@mx_acc_sub_code
left join cost_nc_account t on t.acc_sub_code=e.tax_subject_code
left join uf_dept_oa2nc f on f.oa_dept=d.cost_dept
left join oa2nc.dbo.v_dept g on f.nc_dept=g.pk_dept
left join oa2nc.dbo.v_project h on h.code=e.project_code
left join uf_staff_oa2nc i on i.oa_staff=a.applicant_submit
left join [oa2nc].[dbo].v_psndoc j on i.nc_psndoc=j.pk_psndoc left join oa2nc.dbo.v_dept_normal k on g.name=k.name
where a.requestid=@requestid and d.id=@mx_id and k.pk_org=c.pk_org and j.pk_org=c.pk_org
end
set @dt_sum=@dt_sum+@mx_realmoney+@mx_tax
end
---2、进成本
if(@add_in_subject is not null)
begin
INSERT INTO [oa2nc].[dbo].[bd_voucher]([pk_corp],[pk_group],[pk_org],[billmaker],[billdate],[creator],[creationtime],[glorgbook_code],[glorgbook],[vouchertype],[tallydate]
,[oarequestID],[oaworkcode],[Oacode],[vbillno],[explanation],[accsubjcode],[accsubjname],[currency],[exchange],[baldebitmny],[baldebitlocalmny],[balcreditmny],[balcreditlocalmny],[checktype1]
,[checkcode1],[checkname1],[checktype2],[checkcode2],[checkname2],[checktype3],[checkcode3],[checkname3],[checktype4],[checkcode4],[checkname4],[checktype5],[checkcode5],[checkname5]
,[checktype6],[checkcode6],[checkname6],[vdef1],[vdef2],[vdef3],[vdef4],[vdef5],[ts],[dr],[pk_cashflow],[cashflowflag])
select c.code,'01',c.code,'v-wantao.zhang',convert(varchar(10),getDate(),120),'v-wantao.zhang',convert(varchar(19),getDate(),120),c.accountcode,c.accountname,@vouchertype,convert(varchar(10),getDate(),120),
@requestid,@oaworkcode,doc_number,@the_number_str,g.name+j.name+d.cost_date_01+'从'+d.departure_place+'出发到'+d.destination+'产生差旅费用'+CAST(CAST(real_money AS DECIMAL(20,5)) AS VARCHAR(20))+'元',e.acc_sub_code,e.acc_sub_name,'CNY',1.00000,real_money,real_money,null,null,case e.sec_acc_dept when 0 then '0001' else null end,
case e.sec_acc_dept when 0 then k.code else null end,case e.sec_acc_dept when 0 then k.name else null end,
case e.sec_acc_staff when 0 then '0002' else null end,case e.sec_acc_staff when 0 then j.code else null end,case e.sec_acc_staff when 0 then j.name else null end,
case e.sec_acc_customer when 0 then '004' else null end,null,null,
case e.sec_acc_supplier when 0 then '004' else null end,null,null,
case when e.project_code is not null then 'FI03' else null end,e.project_code,h.name,
case e.sec_acc_bank when 0 then '0011' else null end,case e.sec_acc_bank when 0 then (select accnum from oa2nc.dbo.v_bankdoc where accname='********银行-活期') else null end,case e.sec_acc_bank when 0 then '********银行-活期' else null end,
null,null,null,null,null,convert(varchar(19),getDate(),120),0,e.cashflow_code,0
from formtable_main_22 a
left join hrmsubcompany b on a.reimbursement_unit=b.id
left join oa2nc.dbo.v_orgs c on c.name=b.subcompanyname
left join formtable_main_22_dt1 d on d.mainid=a.id
left join uf_cost_category e on e.acc_sub_code=@mx_acc_sub_code
left join uf_dept_oa2nc f on f.oa_dept=d.cost_dept
left join oa2nc.dbo.v_dept g on f.nc_dept=g.pk_dept
left join oa2nc.dbo.v_project h on h.code=e.project_code
left join uf_staff_oa2nc i on i.oa_staff=a.applicant_submit
left join [oa2nc].[dbo].v_psndoc j on i.nc_psndoc=j.pk_psndoc left join oa2nc.dbo.v_dept_normal k on g.name=k.name
where a.requestid=@requestid and d.id=@mx_id and k.pk_org=c.pk_org and j.pk_org=c.pk_org
INSERT INTO [oa2nc].[dbo].[bd_voucher]([pk_corp],[pk_group],[pk_org],[billmaker],[billdate],[creator],[creationtime],[glorgbook_code],[glorgbook],[vouchertype],[tallydate]
,[oarequestID],[oaworkcode],[Oacode],[vbillno],[explanation],[accsubjcode],[accsubjname],[currency],[exchange],[baldebitmny],[baldebitlocalmny],[balcreditmny],[balcreditlocalmny],[checktype1]
,[checkcode1],[checkname1],[checktype2],[checkcode2],[checkname2],[checktype3],[checkcode3],[checkname3],[checktype4],[checkcode4],[checkname4],[checktype5],[checkcode5],[checkname5]
,[checktype6],[checkcode6],[checkname6],[vdef1],[vdef2],[vdef3],[vdef4],[vdef5],[ts],[dr],[pk_cashflow],[cashflowflag])
select c.code,'01',c.code,'v-wantao.zhang',convert(varchar(10),getDate(),120),'v-wantao.zhang',convert(varchar(19),getDate(),120),c.accountcode,c.accountname,@vouchertype,convert(varchar(10),getDate(),120),
@requestid,@oaworkcode,doc_number,@the_number_str,g.name+j.name+d.cost_date_01+'从'+d.departure_place+'出发到'+d.destination+'产生差旅交通费用'+CAST(CAST(real_money AS DECIMAL(20,5)) AS VARCHAR(20))+'元',e.add_in_subject_code,e.add_in_subject_name,'CNY',1.00000,real_money,real_money,null,null,case e.sec_acc_dept when 0 then '0001' else null end,
case e.sec_acc_dept when 0 then k.code else null end,case e.sec_acc_dept when 0 then k.name else null end,
case e.sec_acc_staff when 0 then '0002' else null end,case e.sec_acc_staff when 0 then j.code else null end,case e.sec_acc_staff when 0 then j.name else null end,
case e.sec_acc_customer when 0 then '004' else null end,null,null,
case e.sec_acc_supplier when 0 then '004' else null end,null,null,
case when e.project_code is not null then 'FI03' else null end,e.project_code,h.name,
case e.sec_acc_bank when 0 then '0011' else null end,case e.sec_acc_bank when 0 then (select accnum from oa2nc.dbo.v_bankdoc where accname='********银行-活期') else null end,case e.sec_acc_bank when 0 then '********银行-活期' else null end,
null,null,null,null,null,convert(varchar(19),getDate(),120),0,e.cashflow_code,0
from formtable_main_22 a
left join hrmsubcompany b on a.reimbursement_unit=b.id
left join oa2nc.dbo.v_orgs c on c.name=b.subcompanyname
left join formtable_main_22_dt1 d on d.mainid=a.id
left join uf_cost_category e on e.acc_sub_code=@mx_acc_sub_code
left join uf_dept_oa2nc f on f.oa_dept=d.cost_dept
left join oa2nc.dbo.v_dept g on f.nc_dept=g.pk_dept
left join oa2nc.dbo.v_project h on h.code=e.project_code
left join uf_staff_oa2nc i on i.oa_staff=a.applicant_submit
left join [oa2nc].[dbo].v_psndoc j on i.nc_psndoc=j.pk_psndoc left join oa2nc.dbo.v_dept_normal k on g.name=k.name
where a.requestid=@requestid and d.id=@mx_id and k.pk_org=c.pk_org and j.pk_org=c.pk_org
INSERT INTO [oa2nc].[dbo].[bd_voucher]([pk_corp],[pk_group],[pk_org],[billmaker],[billdate],[creator],[creationtime],[glorgbook_code],[glorgbook],[vouchertype],[tallydate]
,[oarequestID],[oaworkcode],[Oacode],[vbillno],[explanation],[accsubjcode],[accsubjname],[currency],[exchange],[baldebitmny],[baldebitlocalmny],[balcreditmny],[balcreditlocalmny],[checktype1]
,[checkcode1],[checkname1],[checktype2],[checkcode2],[checkname2],[checktype3],[checkcode3],[checkname3],[checktype4],[checkcode4],[checkname4],[checktype5],[checkcode5],[checkname5]
,[checktype6],[checkcode6],[checkname6],[vdef1],[vdef2],[vdef3],[vdef4],[vdef5],[ts],[dr],[pk_cashflow],[cashflowflag])
select c.code,'01',c.code,'v-wantao.zhang',convert(varchar(10),getDate(),120),'v-wantao.zhang',convert(varchar(19),getDate(),120),c.accountcode,c.accountname,@vouchertype,convert(varchar(10),getDate(),120),
@requestid,@oaworkcode,doc_number,@the_number_str,g.name+j.name+d.cost_date_01+'从'+d.departure_place+'出发到'+d.destination+'产生差旅交通费用'+CAST(CAST(real_money AS DECIMAL(20,5)) AS VARCHAR(20))+'元',e.add_in_subject_code,e.add_in_subject_name,'CNY',1.00000,null,null,real_money,real_money,case e.sec_acc_dept when 0 then '0001' else null end,
case e.sec_acc_dept when 0 then k.code else null end,case e.sec_acc_dept when 0 then k.name else null end,
case e.sec_acc_staff when 0 then '0002' else null end,case e.sec_acc_staff when 0 then j.code else null end,case e.sec_acc_staff when 0 then j.name else null end,
case e.sec_acc_customer when 0 then '004' else null end,null,null,
case e.sec_acc_supplier when 0 then '004' else null end,null,null,
case when e.project_code is not null then 'FI03' else null end,e.project_code,h.name,
case e.sec_acc_bank when 0 then '0011' else null end,case e.sec_acc_bank when 0 then (select accnum from oa2nc.dbo.v_bankdoc where accname='********银行-活期') else null end,case e.sec_acc_bank when 0 then '********银行-活期' else null end,
null,null,null,null,null,convert(varchar(19),getDate(),120),0,e.cashflow_code,0
from formtable_main_22 a
left join hrmsubcompany b on a.reimbursement_unit=b.id
left join oa2nc.dbo.v_orgs c on c.name=b.subcompanyname
left join formtable_main_22_dt1 d on d.mainid=a.id
left join uf_cost_category e on e.acc_sub_code=@mx_acc_sub_code
left join uf_dept_oa2nc f on f.oa_dept=d.cost_dept
left join oa2nc.dbo.v_dept g on f.nc_dept=g.pk_dept
left join oa2nc.dbo.v_project h on h.code=e.project_code
left join uf_staff_oa2nc i on i.oa_staff=a.applicant_submit
left join [oa2nc].[dbo].v_psndoc j on i.nc_psndoc=j.pk_psndoc left join oa2nc.dbo.v_dept_normal k on g.name=k.name
where a.requestid=@requestid and d.id=@mx_id and k.pk_org=c.pk_org and j.pk_org=c.pk_org
if(@mx_tax>0)
begin
INSERT INTO [oa2nc].[dbo].[bd_voucher]([pk_corp],[pk_group],[pk_org],[billmaker],[billdate],[creator],[creationtime],[glorgbook_code],[glorgbook],[vouchertype],[tallydate]
,[oarequestID],[oaworkcode],[Oacode],[vbillno],[explanation],[accsubjcode],[accsubjname],[currency],[exchange],[baldebitmny],[baldebitlocalmny],[balcreditmny],[balcreditlocalmny],[checktype1]
,[checkcode1],[checkname1],[checktype2],[checkcode2],[checkname2],[checktype3],[checkcode3],[checkname3],[checktype4],[checkcode4],[checkname4],[checktype5],[checkcode5],[checkname5]
,[checktype6],[checkcode6],[checkname6],[vdef1],[vdef2],[vdef3],[vdef4],[vdef5],[ts],[dr],[pk_cashflow],[cashflowflag])
select c.code,'01',c.code,'v-wantao.zhang',convert(varchar(10),getDate(),120),'v-wantao.zhang',convert(varchar(19),getDate(),120),c.accountcode,c.accountname,@vouchertype,convert(varchar(10),getDate(),120),
@requestid,@oaworkcode,doc_number,@the_number_str,g.name+j.name+d.cost_date_01+'从'+d.departure_place+'出发到'+d.destination+'产生差旅费用税额'+CAST(CAST(d.tax AS DECIMAL(20,5)) AS VARCHAR(20))+'元',t.acc_sub_code,t.acc_sub_name,'CNY',1.00000,d.tax,d.tax,null,null,case t.sec_acc_dept when '部门' then '0001' else null end,
case t.sec_acc_dept when '部门' then k.code else null end,case t.sec_acc_dept when '部门' then k.name else null end,
case t.sec_acc_staff when '员工' then '0002' else null end,case t.sec_acc_staff when '员工' then j.code else null end,case t.sec_acc_staff when '员工' then j.name else null end,
null,null,null,
null,null,null,
case when e.project_code is not null then 'FI03' else null end,e.project_code,h.name,
case t.sec_acc_bank when 0 then '0011' else null end,case t.sec_acc_bank when 0 then (select accnum from oa2nc.dbo.v_bankdoc where accname='********银行-活期') else null end,case e.sec_acc_bank when 0 then '********银行-活期' else null end,
null,null,null,null,null,convert(varchar(19),getDate(),120),0,t.cashflow_code,0
from formtable_main_22 a
left join hrmsubcompany b on a.reimbursement_unit=b.id
left join oa2nc.dbo.v_orgs c on c.name=b.subcompanyname
left join formtable_main_22_dt1 d on d.mainid=a.id
left join uf_cost_category e on e.acc_sub_code=@mx_acc_sub_code
left join cost_nc_account t on t.acc_sub_code=e.tax_subject_code
left join uf_dept_oa2nc f on f.oa_dept=d.cost_dept
left join oa2nc.dbo.v_dept g on f.nc_dept=g.pk_dept
left join oa2nc.dbo.v_project h on h.code=e.project_code
left join uf_staff_oa2nc i on i.oa_staff=a.applicant_submit
left join [oa2nc].[dbo].v_psndoc j on i.nc_psndoc=j.pk_psndoc left join oa2nc.dbo.v_dept_normal k on g.name=k.name
where a.requestid=@requestid and d.id=@mx_id and k.pk_org=c.pk_org and j.pk_org=c.pk_org
end
set @dt_sum=@dt_sum+@mx_realmoney+@mx_tax
end
Fetch Next From this_cursor Into @mx_id
end
Close this_cursor
Deallocate this_cursor
end
----
/*2 差旅工作餐明细 生成凭证*/
IF((@total_meal is not null) and @total_meal>0)
begin
DECLARE this_cursor Cursor for
select id from formtable_main_22_dt2 where mainid=(select id from formtable_main_22 where requestid=@requestid)
Open this_cursor Fetch Next From this_cursor Into @mx_id
While ( @@Fetch_Status=0 )
begin
set @mx_tax=(select tax from formtable_main_22_dt2 where id=@mx_id)
SET @mx_realmoney=(select real_money from formtable_main_22_dt2 where id=@mx_id)
set @mx_acc_sub_code=(select '66020603' from formtable_main_22_dt2 where id=@mx_id)
set @add_in_subject=(select add_in_subject from uf_cost_category where acc_sub_code=@mx_acc_sub_code)
---1、不进成本
if(@add_in_subject is null)
begin
INSERT INTO [oa2nc].[dbo].[bd_voucher]([pk_corp],[pk_group],[pk_org],[billmaker],[billdate],[creator],[creationtime],[glorgbook_code],[glorgbook],[vouchertype],[tallydate]
,[oarequestID],[oaworkcode],[Oacode],[vbillno],[explanation],[accsubjcode],[accsubjname],[currency],[exchange],[baldebitmny],[baldebitlocalmny],[balcreditmny],[balcreditlocalmny],[checktype1]
,[checkcode1],[checkname1],[checktype2],[checkcode2],[checkname2],[checktype3],[checkcode3],[checkname3],[checktype4],[checkcode4],[checkname4],[checktype5],[checkcode5],[checkname5]
,[checktype6],[checkcode6],[checkname6],[vdef1],[vdef2],[vdef3],[vdef4],[vdef5],[ts],[dr],[pk_cashflow],[cashflowflag])
select c.code,'01',c.code,'v-wantao.zhang',convert(varchar(10),getDate(),120),'v-wantao.zhang',convert(varchar(19),getDate(),120),c.accountcode,c.accountname,@vouchertype,convert(varchar(10),getDate(),120),
@requestid,@oaworkcode,doc_number,@the_number_str,g.name+j.name+d.cost_date_02+'在'+d.cost_palce_02+'产生差旅工作餐费用'+CAST(CAST(real_money AS DECIMAL(20,5)) AS VARCHAR(20))+'元',e.acc_sub_code,e.acc_sub_name,'CNY',1.00000,real_money,real_money,null,null,case e.sec_acc_dept when 0 then '0001' else null end,
case e.sec_acc_dept when 0 then k.code else null end,case e.sec_acc_dept when 0 then k.name else null end,
case e.sec_acc_staff when 0 then '0002' else null end,case e.sec_acc_staff when 0 then j.code else null end,case e.sec_acc_staff when 0 then j.name else null end,
case e.sec_acc_customer when 0 then '004' else null end,null,null,
case e.sec_acc_supplier when 0 then '004' else null end,null,null,
case when e.project_code is not null then 'FI03' else null end,e.project_code,h.name,
case e.sec_acc_bank when 0 then '0011' else null end,case e.sec_acc_bank when 0 then (select accnum from oa2nc.dbo.v_bankdoc where accname='********银行-活期') else null end,case e.sec_acc_bank when 0 then '********银行-活期' else null end,
null,null,null,null,null,convert(varchar(19),getDate(),120),0,e.cashflow_code,0
from formtable_main_22 a
left join hrmsubcompany b on a.reimbursement_unit=b.id
left join oa2nc.dbo.v_orgs c on c.name=b.subcompanyname
left join formtable_main_22_dt2 d on d.mainid=a.id
left join uf_cost_category e on e.acc_sub_code=@mx_acc_sub_code
left join uf_dept_oa2nc f on f.oa_dept=d.cost_dept
left join oa2nc.dbo.v_dept g on f.nc_dept=g.pk_dept
left join oa2nc.dbo.v_project h on h.code=e.project_code
left join uf_staff_oa2nc i on i.oa_staff=a.applicant_submit
left join [oa2nc].[dbo].v_psndoc j on i.nc_psndoc=j.pk_psndoc left join oa2nc.dbo.v_dept_normal k on g.name=k.name
where a.requestid=@requestid and d.id=@mx_id and k.pk_org=c.pk_org and j.pk_org=c.pk_org
if(@mx_tax>0)
begin
INSERT INTO [oa2nc].[dbo].[bd_voucher]([pk_corp],[pk_group],[pk_org],[billmaker],[billdate],[creator],[creationtime],[glorgbook_code],[glorgbook],[vouchertype],[tallydate]
,[oarequestID],[oaworkcode],[Oacode],[vbillno],[explanation],[accsubjcode],[accsubjname],[currency],[exchange],[baldebitmny],[baldebitlocalmny],[balcreditmny],[balcreditlocalmny],[checktype1]
,[checkcode1],[checkname1],[checktype2],[checkcode2],[checkname2],[checktype3],[checkcode3],[checkname3],[checktype4],[checkcode4],[checkname4],[checktype5],[checkcode5],[checkname5]
,[checktype6],[checkcode6],[checkname6],[vdef1],[vdef2],[vdef3],[vdef4],[vdef5],[ts],[dr],[pk_cashflow],[cashflowflag])
select c.code,'01',c.code,'v-wantao.zhang',convert(varchar(10),getDate(),120),'v-wantao.zhang',convert(varchar(19),getDate(),120),c.accountcode,c.accountname,@vouchertype,convert(varchar(10),getDate(),120),
@requestid,@oaworkcode,doc_number,@the_number_str,g.name+j.name+d.cost_date_02+'在'+d.cost_palce_02+'产生差旅工作餐费用税额'+CAST(CAST(d.tax AS DECIMAL(20,5)) AS VARCHAR(20))+'元',t.acc_sub_code,t.acc_sub_name,'CNY',1.00000,d.tax,d.tax,null,null,case t.sec_acc_dept when '部门' then '0001' else null end,
case t.sec_acc_dept when '部门' then k.code else null end,case t.sec_acc_dept when '部门' then k.name else null end,
case t.sec_acc_staff when '员工' then '0002' else null end,case t.sec_acc_staff when '员工' then j.code else null end,case t.sec_acc_staff when '员工' then j.name else null end,
null,null,null,
null,null,null,
case when e.project_code is not null then 'FI03' else null end,e.project_code,h.name,
case t.sec_acc_bank when 0 then '0011' else null end,case t.sec_acc_bank when 0 then (select accnum from oa2nc.dbo.v_bankdoc where accname='********银行-活期') else null end,case e.sec_acc_bank when 0 then '********银行-活期' else null end,
null,null,null,null,null,convert(varchar(19),getDate(),120),0,t.cashflow_code,0
from formtable_main_22 a
left join hrmsubcompany b on a.reimbursement_unit=b.id
left join oa2nc.dbo.v_orgs c on c.name=b.subcompanyname
left join formtable_main_22_dt2 d on d.mainid=a.id
left join uf_cost_category e on e.acc_sub_code=@mx_acc_sub_code
left join cost_nc_account t on t.acc_sub_code=e.tax_subject_code
left join uf_dept_oa2nc f on f.oa_dept=d.cost_dept
left join oa2nc.dbo.v_dept g on f.nc_dept=g.pk_dept
left join oa2nc.dbo.v_project h on h.code=e.project_code
left join uf_staff_oa2nc i on i.oa_staff=a.applicant_submit
left join [oa2nc].[dbo].v_psndoc j on i.nc_psndoc=j.pk_psndoc left join oa2nc.dbo.v_dept_normal k on g.name=k.name
where a.requestid=@requestid and d.id=@mx_id and k.pk_org=c.pk_org and j.pk_org=c.pk_org
end
set @dt_sum=@dt_sum+@mx_realmoney+@mx_tax
end
---2、进成本
if(@add_in_subject is not null)
begin
INSERT INTO [oa2nc].[dbo].[bd_voucher]([pk_corp],[pk_group],[pk_org],[billmaker],[billdate],[creator],[creationtime],[glorgbook_code],[glorgbook],[vouchertype],[tallydate]
,[oarequestID],[oaworkcode],[Oacode],[vbillno],[explanation],[accsubjcode],[accsubjname],[currency],[exchange],[baldebitmny],[baldebitlocalmny],[balcreditmny],[balcreditlocalmny],[checktype1]
,[checkcode1],[checkname1],[checktype2],[checkcode2],[checkname2],[checktype3],[checkcode3],[checkname3],[checktype4],[checkcode4],[checkname4],[checktype5],[checkcode5],[checkname5]
,[checktype6],[checkcode6],[checkname6],[vdef1],[vdef2],[vdef3],[vdef4],[vdef5],[ts],[dr],[pk_cashflow],[cashflowflag])
select c.code,'01',c.code,'v-wantao.zhang',convert(varchar(10),getDate(),120),'v-wantao.zhang',convert(varchar(19),getDate(),120),c.accountcode,c.accountname,@vouchertype,convert(varchar(10),getDate(),120),
@requestid,@oaworkcode,doc_number,@the_number_str,g.name+j.name+d.cost_date_02+'在'+d.cost_palce_02+'产生差旅工作餐费用'+CAST(CAST(real_money AS DECIMAL(20,5)) AS VARCHAR(20))+'元',e.acc_sub_code,e.acc_sub_name,'CNY',1.00000,real_money,real_money,null,null,case e.sec_acc_dept when 0 then '0001' else null end,
case e.sec_acc_dept when 0 then k.code else null end,case e.sec_acc_dept when 0 then k.name else null end,
case e.sec_acc_staff when 0 then '0002' else null end,case e.sec_acc_staff when 0 then j.code else null end,case e.sec_acc_staff when 0 then j.name else null end,
case e.sec_acc_customer when 0 then '004' else null end,null,null,
case e.sec_acc_supplier when 0 then '004' else null end,null,null,
case when e.project_code is not null then 'FI03' else null end,e.project_code,h.name,
case e.sec_acc_bank when 0 then '0011' else null end,case e.sec_acc_bank when 0 then (select accnum from oa2nc.dbo.v_bankdoc where accname='********银行-活期') else null end,case e.sec_acc_bank when 0 then '********银行-活期' else null end,
null,null,null,null,null,convert(varchar(19),getDate(),120),0,e.cashflow_code,0
from formtable_main_22 a
left join hrmsubcompany b on a.reimbursement_unit=b.id
left join oa2nc.dbo.v_orgs c on c.name=b.subcompanyname
left join formtable_main_22_dt2 d on d.mainid=a.id
left join uf_cost_category e on e.acc_sub_code=@mx_acc_sub_code
left join uf_dept_oa2nc f on f.oa_dept=d.cost_dept
left join oa2nc.dbo.v_dept g on f.nc_dept=g.pk_dept
left join oa2nc.dbo.v_project h on h.code=e.project_code
left join uf_staff_oa2nc i on i.oa_staff=a.applicant_submit
left join [oa2nc].[dbo].v_psndoc j on i.nc_psndoc=j.pk_psndoc left join oa2nc.dbo.v_dept_normal k on g.name=k.name
where a.requestid=@requestid and d.id=@mx_id and k.pk_org=c.pk_org and j.pk_org=c.pk_org
INSERT INTO [oa2nc].[dbo].[bd_voucher]([pk_corp],[pk_group],[pk_org],[billmaker],[billdate],[creator],[creationtime],[glorgbook_code],[glorgbook],[vouchertype],[tallydate]
,[oarequestID],[oaworkcode],[Oacode],[vbillno],[explanation],[accsubjcode],[accsubjname],[currency],[exchange],[baldebitmny],[baldebitlocalmny],[balcreditmny],[balcreditlocalmny],[checktype1]
,[checkcode1],[checkname1],[checktype2],[checkcode2],[checkname2],[checktype3],[checkcode3],[checkname3],[checktype4],[checkcode4],[checkname4],[checktype5],[checkcode5],[checkname5]
,[checktype6],[checkcode6],[checkname6],[vdef1],[vdef2],[vdef3],[vdef4],[vdef5],[ts],[dr],[pk_cashflow],[cashflowflag])
select c.code,'01',c.code,'v-wantao.zhang',convert(varchar(10),getDate(),120),'v-wantao.zhang',convert(varchar(19),getDate(),120),c.accountcode,c.accountname,@vouchertype,convert(varchar(10),getDate(),120),
@requestid,@oaworkcode,doc_number,@the_number_str,g.name+j.name+d.cost_date_02+'在'+d.cost_palce_02+'产生差旅工作餐费用'+CAST(CAST(real_money AS DECIMAL(20,5)) AS VARCHAR(20))+'元',e.add_in_subject_code,e.add_in_subject_name,'CNY',1.00000,real_money,real_money,null,null,case e.sec_acc_dept when 0 then '0001' else null end,
case e.sec_acc_dept when 0 then k.code else null end,case e.sec_acc_dept when 0 then k.name else null end,
case e.sec_acc_staff when 0 then '0002' else null end,case e.sec_acc_staff when 0 then j.code else null end,case e.sec_acc_staff when 0 then j.name else null end,
case e.sec_acc_customer when 0 then '004' else null end,null,null,
case e.sec_acc_supplier when 0 then '004' else null end,null,null,
case when e.project_code is not null then 'FI03' else null end,e.project_code,h.name,
case e.sec_acc_bank when 0 then '0011' else null end,case e.sec_acc_bank when 0 then (select accnum from oa2nc.dbo.v_bankdoc where accname='********银行-活期') else null end,case e.sec_acc_bank when 0 then '********银行-活期' else null end,
null,null,null,null,null,convert(varchar(19),getDate(),120),0,e.cashflow_code,0
from formtable_main_22 a
left join hrmsubcompany b on a.reimbursement_unit=b.id
left join oa2nc.dbo.v_orgs c on c.name=b.subcompanyname
left join formtable_main_22_dt2 d on d.mainid=a.id
left join uf_cost_category e on e.acc_sub_code=@mx_acc_sub_code
left join uf_dept_oa2nc f on f.oa_dept=d.cost_dept
left join oa2nc.dbo.v_dept g on f.nc_dept=g.pk_dept
left join oa2nc.dbo.v_project h on h.code=e.project_code
left join uf_staff_oa2nc i on i.oa_staff=a.applicant_submit
left join [oa2nc].[dbo].v_psndoc j on i.nc_psndoc=j.pk_psndoc left join oa2nc.dbo.v_dept_normal k on g.name=k.name
where a.requestid=@requestid and d.id=@mx_id and k.pk_org=c.pk_org and j.pk_org=c.pk_org
INSERT INTO [oa2nc].[dbo].[bd_voucher]([pk_corp],[pk_group],[pk_org],[billmaker],[billdate],[creator],[creationtime],[glorgbook_code],[glorgbook],[vouchertype],[tallydate]
,[oarequestID],[oaworkcode],[Oacode],[vbillno],[explanation],[accsubjcode],[accsubjname],[currency],[exchange],[baldebitmny],[baldebitlocalmny],[balcreditmny],[balcreditlocalmny],[checktype1]
,[checkcode1],[checkname1],[checktype2],[checkcode2],[checkname2],[checktype3],[checkcode3],[checkname3],[checktype4],[checkcode4],[checkname4],[checktype5],[checkcode5],[checkname5]
,[checktype6],[checkcode6],[checkname6],[vdef1],[vdef2],[vdef3],[vdef4],[vdef5],[ts],[dr],[pk_cashflow],[cashflowflag])
select c.code,'01',c.code,'v-wantao.zhang',convert(varchar(10),getDate(),120),'v-wantao.zhang',convert(varchar(19),getDate(),120),c.accountcode,c.accountname,@vouchertype,convert(varchar(10),getDate(),120),
@requestid,@oaworkcode,doc_number,@the_number_str,g.name+j.name+d.cost_date_02+'在'+d.cost_palce_02+'产生差旅工作餐费用'+CAST(CAST(real_money AS DECIMAL(20,5)) AS VARCHAR(20))+'元',e.add_in_subject_code,e.add_in_subject_name,'CNY',1.00000,null,null,real_money,real_money,case e.sec_acc_dept when 0 then '0001' else null end,
case e.sec_acc_dept when 0 then k.code else null end,case e.sec_acc_dept when 0 then k.name else null end,
case e.sec_acc_staff when 0 then '0002' else null end,case e.sec_acc_staff when 0 then j.code else null end,case e.sec_acc_staff when 0 then j.name else null end,
case e.sec_acc_customer when 0 then '004' else null end,null,null,
case e.sec_acc_supplier when 0 then '004' else null end,null,null,
case when e.project_code is not null then 'FI03' else null end,e.project_code,h.name,
case e.sec_acc_bank when 0 then '0011' else null end,case e.sec_acc_bank when 0 then (select accnum from oa2nc.dbo.v_bankdoc where accname='********银行-活期') else null end,case e.sec_acc_bank when 0 then '********银行-活期' else null end,
null,null,null,null,null,convert(varchar(19),getDate(),120),0,e.cashflow_code,0
from formtable_main_22 a
left join hrmsubcompany b on a.reimbursement_unit=b.id
left join oa2nc.dbo.v_orgs c on c.name=b.subcompanyname
left join formtable_main_22_dt2 d on d.mainid=a.id
left join uf_cost_category e on e.acc_sub_code=@mx_acc_sub_code
left join uf_dept_oa2nc f on f.oa_dept=d.cost_dept
left join oa2nc.dbo.v_dept g on f.nc_dept=g.pk_dept
left join oa2nc.dbo.v_project h on h.code=e.project_code
left join uf_staff_oa2nc i on i.oa_staff=a.applicant_submit
left join [oa2nc].[dbo].v_psndoc j on i.nc_psndoc=j.pk_psndoc left join oa2nc.dbo.v_dept_normal k on g.name=k.name
where a.requestid=@requestid and d.id=@mx_id and k.pk_org=c.pk_org and j.pk_org=c.pk_org
if(@mx_tax>0)
begin
INSERT INTO [oa2nc].[dbo].[bd_voucher]([pk_corp],[pk_group],[pk_org],[billmaker],[billdate],[creator],[creationtime],[glorgbook_code],[glorgbook],[vouchertype],[tallydate]
,[oarequestID],[oaworkcode],[Oacode],[vbillno],[explanation],[accsubjcode],[accsubjname],[currency],[exchange],[baldebitmny],[baldebitlocalmny],[balcreditmny],[balcreditlocalmny],[checktype1]
,[checkcode1],[checkname1],[checktype2],[checkcode2],[checkname2],[checktype3],[checkcode3],[checkname3],[checktype4],[checkcode4],[checkname4],[checktype5],[checkcode5],[checkname5]
,[checktype6],[checkcode6],[checkname6],[vdef1],[vdef2],[vdef3],[vdef4],[vdef5],[ts],[dr],[pk_cashflow],[cashflowflag])
select c.code,'01',c.code,'v-wantao.zhang',convert(varchar(10),getDate(),120),'v-wantao.zhang',convert(varchar(19),getDate(),120),c.accountcode,c.accountname,@vouchertype,convert(varchar(10),getDate(),120),
@requestid,@oaworkcode,doc_number,@the_number_str,g.name+j.name+d.cost_date_02+'在'+d.cost_palce_02+'产生差旅工作餐费用税额'+CAST(CAST(d.tax AS DECIMAL(20,5)) AS VARCHAR(20))+'元',t.acc_sub_code,t.acc_sub_name,'CNY',1.00000,d.tax,d.tax,null,null,case t.sec_acc_dept when '部门' then '0001' else null end,
case t.sec_acc_dept when '部门' then k.code else null end,case t.sec_acc_dept when '部门' then k.name else null end,
case t.sec_acc_staff when '员工' then '0002' else null end,case t.sec_acc_staff when '员工' then j.code else null end,case t.sec_acc_staff when '员工' then j.name else null end,
null,null,null,
null,null,null,
case when e.project_code is not null then 'FI03' else null end,e.project_code,h.name,
case t.sec_acc_bank when 0 then '0011' else null end,case t.sec_acc_bank when 0 then (select accnum from oa2nc.dbo.v_bankdoc where accname='********银行-活期') else null end,case e.sec_acc_bank when 0 then '********银行-活期' else null end,
null,null,null,null,null,convert(varchar(19),getDate(),120),0,t.cashflow_code,0
from formtable_main_22 a
left join hrmsubcompany b on a.reimbursement_unit=b.id
left join oa2nc.dbo.v_orgs c on c.name=b.subcompanyname
left join formtable_main_22_dt2 d on d.mainid=a.id
left join uf_cost_category e on e.acc_sub_code=@mx_acc_sub_code
left join cost_nc_account t on t.acc_sub_code=e.tax_subject_code
left join uf_dept_oa2nc f on f.oa_dept=d.cost_dept
left join oa2nc.dbo.v_dept g on f.nc_dept=g.pk_dept
left join oa2nc.dbo.v_project h on h.code=e.project_code
left join uf_staff_oa2nc i on i.oa_staff=a.applicant_submit
left join [oa2nc].[dbo].v_psndoc j on i.nc_psndoc=j.pk_psndoc left join oa2nc.dbo.v_dept_normal k on g.name=k.name
where a.requestid=@requestid and d.id=@mx_id and k.pk_org=c.pk_org and j.pk_org=c.pk_org
end
set @dt_sum=@dt_sum+@mx_realmoney+@mx_tax
end
Fetch Next From this_cursor Into @mx_id
end
Close this_cursor
Deallocate this_cursor
end
/*3 差旅工作 酒店 生成凭证*/
IF((@total_accom is not null) and @total_accom>0)
begin
DECLARE this_cursor Cursor for
select id from formtable_main_22_dt3 where mainid=(select id from formtable_main_22 where requestid=@requestid)
Open this_cursor Fetch Next From this_cursor Into @mx_id
While ( @@Fetch_Status=0 )
begin
set @mx_tax=(select tax from formtable_main_22_dt3 where id=@mx_id)
SET @mx_realmoney=(select real_money from formtable_main_22_dt3 where id=@mx_id)
set @mx_acc_sub_code=(select '66020604' from formtable_main_22_dt3 where id=@mx_id)
set @add_in_subject=(select add_in_subject from uf_cost_category where acc_sub_code=@mx_acc_sub_code)
---1、不进成本
if(@add_in_subject is null)
begin
INSERT INTO [oa2nc].[dbo].[bd_voucher]([pk_corp],[pk_group],[pk_org],[billmaker],[billdate],[creator],[creationtime],[glorgbook_code],[glorgbook],[vouchertype],[tallydate]
,[oarequestID],[oaworkcode],[Oacode],[vbillno],[explanation],[accsubjcode],[accsubjname],[currency],[exchange],[baldebitmny],[baldebitlocalmny],[balcreditmny],[balcreditlocalmny],[checktype1]
,[checkcode1],[checkname1],[checktype2],[checkcode2],[checkname2],[checktype3],[checkcode3],[checkname3],[checktype4],[checkcode4],[checkname4],[checktype5],[checkcode5],[checkname5]
,[checktype6],[checkcode6],[checkname6],[vdef1],[vdef2],[vdef3],[vdef4],[vdef5],[ts],[dr],[pk_cashflow],[cashflowflag])
select c.code,'01',c.code,'v-wantao.zhang',convert(varchar(10),getDate(),120),'v-wantao.zhang',convert(varchar(19),getDate(),120),c.accountcode,c.accountname,@vouchertype,convert(varchar(10),getDate(),120),
@requestid,@oaworkcode,doc_number,@the_number_str,g.name+j.name+d.cost_date_01+'在'+d.cost_palce_02+d.hotel_name+'产生差旅住宿费用'+CAST(CAST(real_money AS DECIMAL(20,5)) AS VARCHAR(20))+'元',e.acc_sub_code,e.acc_sub_name,'CNY',1.00000,real_money,real_money,null,null,case e.sec_acc_dept when 0 then '0001' else null end,
case e.sec_acc_dept when 0 then k.code else null end,case e.sec_acc_dept when 0 then k.name else null end,
case e.sec_acc_staff when 0 then '0002' else null end,case e.sec_acc_staff when 0 then j.code else null end,case e.sec_acc_staff when 0 then j.name else null end,
case e.sec_acc_customer when 0 then '004' else null end,null,null,
case e.sec_acc_supplier when 0 then '004' else null end,null,null,
case when e.project_code is not null then 'FI03' else null end,e.project_code,h.name,
case e.sec_acc_bank when 0 then '0011' else null end,case e.sec_acc_bank when 0 then (select accnum from oa2nc.dbo.v_bankdoc where accname='********银行-活期') else null end,case e.sec_acc_bank when 0 then '********银行-活期' else null end,
null,null,null,null,null,convert(varchar(19),getDate(),120),0,e.cashflow_code,0
from formtable_main_22 a
left join hrmsubcompany b on a.reimbursement_unit=b.id
left join oa2nc.dbo.v_orgs c on c.name=b.subcompanyname
left join formtable_main_22_dt3 d on d.mainid=a.id
left join uf_cost_category e on e.acc_sub_code=@mx_acc_sub_code
left join uf_dept_oa2nc f on f.oa_dept=d.cost_dept
left join oa2nc.dbo.v_dept g on f.nc_dept=g.pk_dept
left join oa2nc.dbo.v_project h on h.code=e.project_code
left join uf_staff_oa2nc i on i.oa_staff=a.applicant_submit
left join [oa2nc].[dbo].v_psndoc j on i.nc_psndoc=j.pk_psndoc left join oa2nc.dbo.v_dept_normal k on g.name=k.name
where a.requestid=@requestid and d.id=@mx_id and k.pk_org=c.pk_org and j.pk_org=c.pk_org
if(@mx_tax>0)
begin
INSERT INTO [oa2nc].[dbo].[bd_voucher]([pk_corp],[pk_group],[pk_org],[billmaker],[billdate],[creator],[creationtime],[glorgbook_code],[glorgbook],[vouchertype],[tallydate]
,[oarequestID],[oaworkcode],[Oacode],[vbillno],[explanation],[accsubjcode],[accsubjname],[currency],[exchange],[baldebitmny],[baldebitlocalmny],[balcreditmny],[balcreditlocalmny],[checktype1]
,[checkcode1],[checkname1],[checktype2],[checkcode2],[checkname2],[checktype3],[checkcode3],[checkname3],[checktype4],[checkcode4],[checkname4],[checktype5],[checkcode5],[checkname5]
,[checktype6],[checkcode6],[checkname6],[vdef1],[vdef2],[vdef3],[vdef4],[vdef5],[ts],[dr],[pk_cashflow],[cashflowflag])
select c.code,'01',c.code,'v-wantao.zhang',convert(varchar(10),getDate(),120),'v-wantao.zhang',convert(varchar(19),getDate(),120),c.accountcode,c.accountname,@vouchertype,convert(varchar(10),getDate(),120),
@requestid,@oaworkcode,doc_number,@the_number_str,g.name+j.name+d.cost_date_01+'在'+d.cost_palce_02+d.hotel_name+'产生差旅住宿费用税额'+CAST(CAST(d.tax AS DECIMAL(20,5)) AS VARCHAR(20))+'元',t.acc_sub_code,t.acc_sub_name,'CNY',1.00000,d.tax,d.tax,null,null,case t.sec_acc_dept when '部门' then '0001' else null end,
case t.sec_acc_dept when '部门' then k.code else null end,case t.sec_acc_dept when '部门' then k.name else null end,
case t.sec_acc_staff when '员工' then '0002' else null end,case t.sec_acc_staff when '员工' then j.code else null end,case t.sec_acc_staff when '员工' then j.name else null end,
null,null,null,
null,null,null,
case when e.project_code is not null then 'FI03' else null end,e.project_code,h.name,
case t.sec_acc_bank when 0 then '0011' else null end,case t.sec_acc_bank when 0 then (select accnum from oa2nc.dbo.v_bankdoc where accname='********银行-活期') else null end,case e.sec_acc_bank when 0 then '********银行-活期' else null end,
null,null,null,null,null,convert(varchar(19),getDate(),120),0,t.cashflow_code,0
from formtable_main_22 a
left join hrmsubcompany b on a.reimbursement_unit=b.id
left join oa2nc.dbo.v_orgs c on c.name=b.subcompanyname
left join formtable_main_22_dt3 d on d.mainid=a.id
left join uf_cost_category e on e.acc_sub_code=@mx_acc_sub_code
left join cost_nc_account t on t.acc_sub_code=e.tax_subject_code
left join uf_dept_oa2nc f on f.oa_dept=d.cost_dept
left join oa2nc.dbo.v_dept g on f.nc_dept=g.pk_dept
left join oa2nc.dbo.v_project h on h.code=e.project_code
left join uf_staff_oa2nc i on i.oa_staff=a.applicant_submit
left join [oa2nc].[dbo].v_psndoc j on i.nc_psndoc=j.pk_psndoc left join oa2nc.dbo.v_dept_normal k on g.name=k.name
where a.requestid=@requestid and d.id=@mx_id and k.pk_org=c.pk_org and j.pk_org=c.pk_org
end
set @dt_sum=@dt_sum+@mx_realmoney+@mx_tax
end
---2、进成本
if(@add_in_subject is not null)
begin
INSERT INTO [oa2nc].[dbo].[bd_voucher]([pk_corp],[pk_group],[pk_org],[billmaker],[billdate],[creator],[creationtime],[glorgbook_code],[glorgbook],[vouchertype],[tallydate]
,[oarequestID],[oaworkcode],[Oacode],[vbillno],[explanation],[accsubjcode],[accsubjname],[currency],[exchange],[baldebitmny],[baldebitlocalmny],[balcreditmny],[balcreditlocalmny],[checktype1]
,[checkcode1],[checkname1],[checktype2],[checkcode2],[checkname2],[checktype3],[checkcode3],[checkname3],[checktype4],[checkcode4],[checkname4],[checktype5],[checkcode5],[checkname5]
,[checktype6],[checkcode6],[checkname6],[vdef1],[vdef2],[vdef3],[vdef4],[vdef5],[ts],[dr],[pk_cashflow],[cashflowflag])
select c.code,'01',c.code,'v-wantao.zhang',convert(varchar(10),getDate(),120),'v-wantao.zhang',convert(varchar(19),getDate(),120),c.accountcode,c.accountname,@vouchertype,convert(varchar(10),getDate(),120),
@requestid,@oaworkcode,doc_number,@the_number_str,g.name+j.name+d.cost_date_01+'在'+d.cost_palce_02+d.hotel_name+'产生差旅住宿费用'+CAST(CAST(real_money AS DECIMAL(20,5)) AS VARCHAR(20))+'元',e.acc_sub_code,e.acc_sub_name,'CNY',1.00000,real_money,real_money,null,null,case e.sec_acc_dept when 0 then '0001' else null end,
case e.sec_acc_dept when 0 then k.code else null end,case e.sec_acc_dept when 0 then k.name else null end,
case e.sec_acc_staff when 0 then '0002' else null end,case e.sec_acc_staff when 0 then j.code else null end,case e.sec_acc_staff when 0 then j.name else null end,
case e.sec_acc_customer when 0 then '004' else null end,null,null,
case e.sec_acc_supplier when 0 then '004' else null end,null,null,
case when e.project_code is not null then 'FI03' else null end,e.project_code,h.name,
case e.sec_acc_bank when 0 then '0011' else null end,case e.sec_acc_bank when 0 then (select accnum from oa2nc.dbo.v_bankdoc where accname='********银行-活期') else null end,case e.sec_acc_bank when 0 then '********银行-活期' else null end,
null,null,null,null,null,convert(varchar(19),getDate(),120),0,e.cashflow_code,0
from formtable_main_22 a
left join hrmsubcompany b on a.reimbursement_unit=b.id
left join oa2nc.dbo.v_orgs c on c.name=b.subcompanyname
left join formtable_main_22_dt3 d on d.mainid=a.id
left join uf_cost_category e on e.acc_sub_code=@mx_acc_sub_code
left join uf_dept_oa2nc f on f.oa_dept=d.cost_dept
left join oa2nc.dbo.v_dept g on f.nc_dept=g.pk_dept
left join oa2nc.dbo.v_project h on h.code=e.project_code
left join uf_staff_oa2nc i on i.oa_staff=a.applicant_submit
left join [oa2nc].[dbo].v_psndoc j on i.nc_psndoc=j.pk_psndoc left join oa2nc.dbo.v_dept_normal k on g.name=k.name
where a.requestid=@requestid and d.id=@mx_id and k.pk_org=c.pk_org and j.pk_org=c.pk_org
INSERT INTO [oa2nc].[dbo].[bd_voucher]([pk_corp],[pk_group],[pk_org],[billmaker],[billdate],[creator],[creationtime],[glorgbook_code],[glorgbook],[vouchertype],[tallydate]
,[oarequestID],[oaworkcode],[Oacode],[vbillno],[explanation],[accsubjcode],[accsubjname],[currency],[exchange],[baldebitmny],[baldebitlocalmny],[balcreditmny],[balcreditlocalmny],[checktype1]
,[checkcode1],[checkname1],[checktype2],[checkcode2],[checkname2],[checktype3],[checkcode3],[checkname3],[checktype4],[checkcode4],[checkname4],[checktype5],[checkcode5],[checkname5]
,[checktype6],[checkcode6],[checkname6],[vdef1],[vdef2],[vdef3],[vdef4],[vdef5],[ts],[dr],[pk_cashflow],[cashflowflag])
select c.code,'01',c.code,'v-wantao.zhang',convert(varchar(10),getDate(),120),'v-wantao.zhang',convert(varchar(19),getDate(),120),c.accountcode,c.accountname,@vouchertype,convert(varchar(10),getDate(),120),
@requestid,@oaworkcode,doc_number,@the_number_str,g.name+j.name+d.cost_date_01+'在'+d.cost_palce_02+d.hotel_name+'产生差旅住宿费用'+CAST(CAST(real_money AS DECIMAL(20,5)) AS VARCHAR(20))+'元',e.add_in_subject_code,e.add_in_subject_name,'CNY',1.00000,real_money,real_money,null,null,case e.sec_acc_dept when 0 then '0001' else null end,
case e.sec_acc_dept when 0 then k.code else null end,case e.sec_acc_dept when 0 then k.name else null end,
case e.sec_acc_staff when 0 then '0002' else null end,case e.sec_acc_staff when 0 then j.code else null end,case e.sec_acc_staff when 0 then j.name else null end,
case e.sec_acc_customer when 0 then '004' else null end,null,null,
case e.sec_acc_supplier when 0 then '004' else null end,null,null,
case when e.project_code is not null then 'FI03' else null end,e.project_code,h.name,
case e.sec_acc_bank when 0 then '0011' else null end,case e.sec_acc_bank when 0 then (select accnum from oa2nc.dbo.v_bankdoc where accname='********银行-活期') else null end,case e.sec_acc_bank when 0 then '********银行-活期' else null end,
null,null,null,null,null,convert(varchar(19),getDate(),120),0,e.cashflow_code,0
from formtable_main_22 a
left join hrmsubcompany b on a.reimbursement_unit=b.id
left join oa2nc.dbo.v_orgs c on c.name=b.subcompanyname
left join formtable_main_22_dt3 d on d.mainid=a.id
left join uf_cost_category e on e.acc_sub_code=@mx_acc_sub_code
left join uf_dept_oa2nc f on f.oa_dept=d.cost_dept
left join oa2nc.dbo.v_dept g on f.nc_dept=g.pk_dept
left join oa2nc.dbo.v_project h on h.code=e.project_code
left join uf_staff_oa2nc i on i.oa_staff=a.applicant_submit
left join [oa2nc].[dbo].v_psndoc j on i.nc_psndoc=j.pk_psndoc left join oa2nc.dbo.v_dept_normal k on g.name=k.name
where a.requestid=@requestid and d.id=@mx_id and k.pk_org=c.pk_org and j.pk_org=c.pk_org
INSERT INTO [oa2nc].[dbo].[bd_voucher]([pk_corp],[pk_group],[pk_org],[billmaker],[billdate],[creator],[creationtime],[glorgbook_code],[glorgbook],[vouchertype],[tallydate]
,[oarequestID],[oaworkcode],[Oacode],[vbillno],[explanation],[accsubjcode],[accsubjname],[currency],[exchange],[baldebitmny],[baldebitlocalmny],[balcreditmny],[balcreditlocalmny],[checktype1]
,[checkcode1],[checkname1],[checktype2],[checkcode2],[checkname2],[checktype3],[checkcode3],[checkname3],[checktype4],[checkcode4],[checkname4],[checktype5],[checkcode5],[checkname5]
,[checktype6],[checkcode6],[checkname6],[vdef1],[vdef2],[vdef3],[vdef4],[vdef5],[ts],[dr],[pk_cashflow],[cashflowflag])
select c.code,'01',c.code,'v-wantao.zhang',convert(varchar(10),getDate(),120),'v-wantao.zhang',convert(varchar(19),getDate(),120),c.accountcode,c.accountname,@vouchertype,convert(varchar(10),getDate(),120),
@requestid,@oaworkcode,doc_number,@the_number_str,g.name+j.name+d.cost_date_01+'在'+d.cost_palce_02+d.hotel_name+'产生差旅住宿费用'+CAST(CAST(real_money AS DECIMAL(20,5)) AS VARCHAR(20))+'元',e.add_in_subject_code,e.add_in_subject_name,'CNY',1.00000,null,null,real_money,real_money,case e.sec_acc_dept when 0 then '0001' else null end,
case e.sec_acc_dept when 0 then k.code else null end,case e.sec_acc_dept when 0 then k.name else null end,
case e.sec_acc_staff when 0 then '0002' else null end,case e.sec_acc_staff when 0 then j.code else null end,case e.sec_acc_staff when 0 then j.name else null end,
case e.sec_acc_customer when 0 then '004' else null end,null,null,
case e.sec_acc_supplier when 0 then '004' else null end,null,null,
case when e.project_code is not null then 'FI03' else null end,e.project_code,h.name,
case e.sec_acc_bank when 0 then '0011' else null end,case e.sec_acc_bank when 0 then (select accnum from oa2nc.dbo.v_bankdoc where accname='********银行-活期') else null end,case e.sec_acc_bank when 0 then '********银行-活期' else null end,
null,null,null,null,null,convert(varchar(19),getDate(),120),0,e.cashflow_code,0
from formtable_main_22 a
left join hrmsubcompany b on a.reimbursement_unit=b.id
left join oa2nc.dbo.v_orgs c on c.name=b.subcompanyname
left join formtable_main_22_dt3 d on d.mainid=a.id
left join uf_cost_category e on e.acc_sub_code=@mx_acc_sub_code
left join uf_dept_oa2nc f on f.oa_dept=d.cost_dept
left join oa2nc.dbo.v_dept g on f.nc_dept=g.pk_dept
left join oa2nc.dbo.v_project h on h.code=e.project_code
left join uf_staff_oa2nc i on i.oa_staff=a.applicant_submit
left join [oa2nc].[dbo].v_psndoc j on i.nc_psndoc=j.pk_psndoc left join oa2nc.dbo.v_dept_normal k on g.name=k.name
where a.requestid=@requestid and d.id=@mx_id and k.pk_org=c.pk_org and j.pk_org=c.pk_org
if(@mx_tax>0)
begin
INSERT INTO [oa2nc].[dbo].[bd_voucher]([pk_corp],[pk_group],[pk_org],[billmaker],[billdate],[creator],[creationtime],[glorgbook_code],[glorgbook],[vouchertype],[tallydate]
,[oarequestID],[oaworkcode],[Oacode],[vbillno],[explanation],[accsubjcode],[accsubjname],[currency],[exchange],[baldebitmny],[baldebitlocalmny],[balcreditmny],[balcreditlocalmny],[checktype1]
,[checkcode1],[checkname1],[checktype2],[checkcode2],[checkname2],[checktype3],[checkcode3],[checkname3],[checktype4],[checkcode4],[checkname4],[checktype5],[checkcode5],[checkname5]
,[checktype6],[checkcode6],[checkname6],[vdef1],[vdef2],[vdef3],[vdef4],[vdef5],[ts],[dr],[pk_cashflow],[cashflowflag])
select c.code,'01',c.code,'v-wantao.zhang',convert(varchar(10),getDate(),120),'v-wantao.zhang',convert(varchar(19),getDate(),120),c.accountcode,c.accountname,@vouchertype,convert(varchar(10),getDate(),120),
@requestid,@oaworkcode,doc_number,@the_number_str,g.name+j.name+d.cost_date_01+'在'+d.cost_palce_02+d.hotel_name+'产生差旅住宿费用税额'+CAST(CAST(d.tax AS DECIMAL(20,5)) AS VARCHAR(20))+'元',t.acc_sub_code,t.acc_sub_name,'CNY',1.00000,d.tax,d.tax,null,null,case t.sec_acc_dept when '部门' then '0001' else null end,
case t.sec_acc_dept when '部门' then k.code else null end,case t.sec_acc_dept when '部门' then k.name else null end,
case t.sec_acc_staff when '员工' then '0002' else null end,case t.sec_acc_staff when '员工' then j.code else null end,case t.sec_acc_staff when '员工' then j.name else null end,
null,null,null,
null,null,null,
case when e.project_code is not null then 'FI03' else null end,e.project_code,h.name,
case t.sec_acc_bank when 0 then '0011' else null end,case t.sec_acc_bank when 0 then (select accnum from oa2nc.dbo.v_bankdoc where accname='********银行-活期') else null end,case e.sec_acc_bank when 0 then '********银行-活期' else null end,
null,null,null,null,null,convert(varchar(19),getDate(),120),0,t.cashflow_code,0
from formtable_main_22 a
left join hrmsubcompany b on a.reimbursement_unit=b.id
left join oa2nc.dbo.v_orgs c on c.name=b.subcompanyname
left join formtable_main_22_dt3 d on d.mainid=a.id
left join uf_cost_category e on e.acc_sub_code=@mx_acc_sub_code
left join cost_nc_account t on t.acc_sub_code=e.tax_subject_code
left join uf_dept_oa2nc f on f.oa_dept=d.cost_dept
left join oa2nc.dbo.v_dept g on f.nc_dept=g.pk_dept
left join oa2nc.dbo.v_project h on h.code=e.project_code
left join uf_staff_oa2nc i on i.oa_staff=a.applicant_submit
left join [oa2nc].[dbo].v_psndoc j on i.nc_psndoc=j.pk_psndoc left join oa2nc.dbo.v_dept_normal k on g.name=k.name
where a.requestid=@requestid and d.id=@mx_id and k.pk_org=c.pk_org and j.pk_org=c.pk_org
end
set @dt_sum=@dt_sum+@mx_realmoney+@mx_tax
end
Fetch Next From this_cursor Into @mx_id
end
Close this_cursor
Deallocate this_cursor
end
/*4 差旅发生的对外招待明细 生成凭证*/
IF((@total_enter is not null) and @total_enter>0)
begin
DECLARE this_cursor Cursor for
select id from formtable_main_22_dt4 where mainid=(select id from formtable_main_22 where requestid=@requestid)
Open this_cursor Fetch Next From this_cursor Into @mx_id
While ( @@Fetch_Status=0 )
begin
set @mx_tax=(select tax from formtable_main_22_dt4 where id=@mx_id)
SET @mx_realmoney=(select real_money from formtable_main_22_dt4 where id=@mx_id)
set @mx_acc_sub_code=(select case guest_type when 0 then '66020802' end from formtable_main_22_dt4 where id=@mx_id)
set @add_in_subject=(select add_in_subject from uf_cost_category where acc_sub_code=@mx_acc_sub_code)
---1、不进成本
if(@add_in_subject is null)
begin
INSERT INTO [oa2nc].[dbo].[bd_voucher]([pk_corp],[pk_group],[pk_org],[billmaker],[billdate],[creator],[creationtime],[glorgbook_code],[glorgbook],[vouchertype],[tallydate]
,[oarequestID],[oaworkcode],[Oacode],[vbillno],[explanation],[accsubjcode],[accsubjname],[currency],[exchange],[baldebitmny],[baldebitlocalmny],[balcreditmny],[balcreditlocalmny],[checktype1]
,[checkcode1],[checkname1],[checktype2],[checkcode2],[checkname2],[checktype3],[checkcode3],[checkname3],[checktype4],[checkcode4],[checkname4],[checktype5],[checkcode5],[checkname5]
,[checktype6],[checkcode6],[checkname6],[vdef1],[vdef2],[vdef3],[vdef4],[vdef5],[ts],[dr],[pk_cashflow],[cashflowflag])
select c.code,'01',c.code,'v-wantao.zhang',convert(varchar(10),getDate(),120),'v-wantao.zhang',convert(varchar(19),getDate(),120),c.accountcode,c.accountname,@vouchertype,convert(varchar(10),getDate(),120),
@requestid,@oaworkcode,doc_number,@the_number_str,g.name+j.name+d.reception_date+'在'+d.guest_place+'产生业务招待费用'+CAST(CAST(real_money AS DECIMAL(20,5)) AS VARCHAR(20))+'元',e.acc_sub_code,e.acc_sub_name,'CNY',1.00000,real_money,real_money,null,null,case e.sec_acc_dept when 0 then '0001' else null end,
case e.sec_acc_dept when 0 then k.code else null end,case e.sec_acc_dept when 0 then k.name else null end,
case e.sec_acc_staff when 0 then '0002' else null end,case e.sec_acc_staff when 0 then j.code else null end,case e.sec_acc_staff when 0 then j.name else null end,
case e.sec_acc_customer when 0 then '004' else null end,null,null,
case e.sec_acc_supplier when 0 then '004' else null end,null,null,
case when e.project_code is not null then 'FI03' else null end,e.project_code,h.name,
case e.sec_acc_bank when 0 then '0011' else null end,case e.sec_acc_bank when 0 then (select accnum from oa2nc.dbo.v_bankdoc where accname='********银行-活期') else null end,case e.sec_acc_bank when 0 then '********银行-活期' else null end,
null,null,null,null,null,convert(varchar(19),getDate(),120),0,e.cashflow_code,0
from formtable_main_22 a
left join hrmsubcompany b on a.reimbursement_unit=b.id
left join oa2nc.dbo.v_orgs c on c.name=b.subcompanyname
left join formtable_main_22_dt4 d on d.mainid=a.id
left join uf_cost_category e on e.acc_sub_code=@mx_acc_sub_code
left join uf_dept_oa2nc f on f.oa_dept=d.cost_dept
left join oa2nc.dbo.v_dept g on f.nc_dept=g.pk_dept
left join oa2nc.dbo.v_project h on h.code=e.project_code
left join uf_staff_oa2nc i on i.oa_staff=a.applicant_submit
left join [oa2nc].[dbo].v_psndoc j on i.nc_psndoc=j.pk_psndoc left join oa2nc.dbo.v_dept_normal k on g.name=k.name
where a.requestid=@requestid and d.id=@mx_id and k.pk_org=c.pk_org and j.pk_org=c.pk_org
if(@mx_tax>0)
begin
INSERT INTO [oa2nc].[dbo].[bd_voucher]([pk_corp],[pk_group],[pk_org],[billmaker],[billdate],[creator],[creationtime],[glorgbook_code],[glorgbook],[vouchertype],[tallydate]
,[oarequestID],[oaworkcode],[Oacode],[vbillno],[explanation],[accsubjcode],[accsubjname],[currency],[exchange],[baldebitmny],[baldebitlocalmny],[balcreditmny],[balcreditlocalmny],[checktype1]
,[checkcode1],[checkname1],[checktype2],[checkcode2],[checkname2],[checktype3],[checkcode3],[checkname3],[checktype4],[checkcode4],[checkname4],[checktype5],[checkcode5],[checkname5]
,[checktype6],[checkcode6],[checkname6],[vdef1],[vdef2],[vdef3],[vdef4],[vdef5],[ts],[dr],[pk_cashflow],[cashflowflag])
select c.code,'01',c.code,'v-wantao.zhang',convert(varchar(10),getDate(),120),'v-wantao.zhang',convert(varchar(19),getDate(),120),c.accountcode,c.accountname,@vouchertype,convert(varchar(10),getDate(),120),
@requestid,@oaworkcode,doc_number,@the_number_str,g.name+j.name+d.reception_date+'在'+d.guest_place+'产生业务招待费用税额'+CAST(CAST(d.tax AS DECIMAL(20,5)) AS VARCHAR(20))+'元',t.acc_sub_code,t.acc_sub_name,'CNY',1.00000,d.tax,d.tax,null,null,case t.sec_acc_dept when '部门' then '0001' else null end,
case t.sec_acc_dept when '部门' then k.code else null end,case t.sec_acc_dept when '部门' then k.name else null end,
case t.sec_acc_staff when '员工' then '0002' else null end,case t.sec_acc_staff when '员工' then j.code else null end,case t.sec_acc_staff when '员工' then j.name else null end,
null,null,null,
null,null,null,
case when e.project_code is not null then 'FI03' else null end,e.project_code,h.name,
case t.sec_acc_bank when 0 then '0011' else null end,case t.sec_acc_bank when 0 then (select accnum from oa2nc.dbo.v_bankdoc where accname='********银行-活期') else null end,case e.sec_acc_bank when 0 then '********银行-活期' else null end,
null,null,null,null,null,convert(varchar(19),getDate(),120),0,t.cashflow_code,0
from formtable_main_22 a
left join hrmsubcompany b on a.reimbursement_unit=b.id
left join oa2nc.dbo.v_orgs c on c.name=b.subcompanyname
left join formtable_main_22_dt4 d on d.mainid=a.id
left join uf_cost_category e on e.acc_sub_code=@mx_acc_sub_code
left join cost_nc_account t on t.acc_sub_code=e.tax_subject_code
left join uf_dept_oa2nc f on f.oa_dept=d.cost_dept
left join oa2nc.dbo.v_dept g on f.nc_dept=g.pk_dept
left join oa2nc.dbo.v_project h on h.code=e.project_code
left join uf_staff_oa2nc i on i.oa_staff=a.applicant_submit
left join [oa2nc].[dbo].v_psndoc j on i.nc_psndoc=j.pk_psndoc left join oa2nc.dbo.v_dept_normal k on g.name=k.name
where a.requestid=@requestid and d.id=@mx_id and k.pk_org=c.pk_org and j.pk_org=c.pk_org
end
set @dt_sum=@dt_sum+@mx_realmoney+@mx_tax
end
---2、进成本
if(@add_in_subject is not null)
begin
INSERT INTO [oa2nc].[dbo].[bd_voucher]([pk_corp],[pk_group],[pk_org],[billmaker],[billdate],[creator],[creationtime],[glorgbook_code],[glorgbook],[vouchertype],[tallydate]
,[oarequestID],[oaworkcode],[Oacode],[vbillno],[explanation],[accsubjcode],[accsubjname],[currency],[exchange],[baldebitmny],[baldebitlocalmny],[balcreditmny],[balcreditlocalmny],[checktype1]
,[checkcode1],[checkname1],[checktype2],[checkcode2],[checkname2],[checktype3],[checkcode3],[checkname3],[checktype4],[checkcode4],[checkname4],[checktype5],[checkcode5],[checkname5]
,[checktype6],[checkcode6],[checkname6],[vdef1],[vdef2],[vdef3],[vdef4],[vdef5],[ts],[dr],[pk_cashflow],[cashflowflag])
select c.code,'01',c.code,'v-wantao.zhang',convert(varchar(10),getDate(),120),'v-wantao.zhang',convert(varchar(19),getDate(),120),c.accountcode,c.accountname,@vouchertype,convert(varchar(10),getDate(),120),
@requestid,@oaworkcode,doc_number,@the_number_str,g.name+j.name+d.reception_date+'在'+d.guest_place+'产生业务招待费用'+CAST(CAST(real_money AS DECIMAL(20,5)) AS VARCHAR(20))+'元',e.acc_sub_code,e.acc_sub_name,'CNY',1.00000,real_money,real_money,null,null,case e.sec_acc_dept when 0 then '0001' else null end,
case e.sec_acc_dept when 0 then k.code else null end,case e.sec_acc_dept when 0 then k.name else null end,
case e.sec_acc_staff when 0 then '0002' else null end,case e.sec_acc_staff when 0 then j.code else null end,case e.sec_acc_staff when 0 then j.name else null end,
case e.sec_acc_customer when 0 then '004' else null end,null,null,
case e.sec_acc_supplier when 0 then '004' else null end,null,null,
case when e.project_code is not null then 'FI03' else null end,e.project_code,h.name,
case e.sec_acc_bank when 0 then '0011' else null end,case e.sec_acc_bank when 0 then (select accnum from oa2nc.dbo.v_bankdoc where accname='********银行-活期') else null end,case e.sec_acc_bank when 0 then '********银行-活期' else null end,
null,null,null,null,null,convert(varchar(19),getDate(),120),0,e.cashflow_code,0
from formtable_main_22 a
left join hrmsubcompany b on a.reimbursement_unit=b.id
left join oa2nc.dbo.v_orgs c on c.name=b.subcompanyname
left join formtable_main_22_dt4 d on d.mainid=a.id
left join uf_cost_category e on e.acc_sub_code=@mx_acc_sub_code
left join uf_dept_oa2nc f on f.oa_dept=d.cost_dept
left join oa2nc.dbo.v_dept g on f.nc_dept=g.pk_dept
left join oa2nc.dbo.v_project h on h.code=e.project_code
left join uf_staff_oa2nc i on i.oa_staff=a.applicant_submit
left join [oa2nc].[dbo].v_psndoc j on i.nc_psndoc=j.pk_psndoc left join oa2nc.dbo.v_dept_normal k on g.name=k.name
where a.requestid=@requestid and d.id=@mx_id and k.pk_org=c.pk_org and j.pk_org=c.pk_org
INSERT INTO [oa2nc].[dbo].[bd_voucher]([pk_corp],[pk_group],[pk_org],[billmaker],[billdate],[creator],[creationtime],[glorgbook_code],[glorgbook],[vouchertype],[tallydate]
,[oarequestID],[oaworkcode],[Oacode],[vbillno],[explanation],[accsubjcode],[accsubjname],[currency],[exchange],[baldebitmny],[baldebitlocalmny],[balcreditmny],[balcreditlocalmny],[checktype1]
,[checkcode1],[checkname1],[checktype2],[checkcode2],[checkname2],[checktype3],[checkcode3],[checkname3],[checktype4],[checkcode4],[checkname4],[checktype5],[checkcode5],[checkname5]
,[checktype6],[checkcode6],[checkname6],[vdef1],[vdef2],[vdef3],[vdef4],[vdef5],[ts],[dr],[pk_cashflow],[cashflowflag])
select c.code,'01',c.code,'v-wantao.zhang',convert(varchar(10),getDate(),120),'v-wantao.zhang',convert(varchar(19),getDate(),120),c.accountcode,c.accountname,@vouchertype,convert(varchar(10),getDate(),120),
@requestid,@oaworkcode,doc_number,@the_number_str,g.name+j.name+d.reception_date+'在'+d.guest_place+'产生业务招待费用'+CAST(CAST(real_money AS DECIMAL(20,5)) AS VARCHAR(20))+'元',e.add_in_subject_code,e.add_in_subject_name,'CNY',1.00000,real_money,real_money,null,null,case e.sec_acc_dept when 0 then '0001' else null end,
case e.sec_acc_dept when 0 then k.code else null end,case e.sec_acc_dept when 0 then k.name else null end,
case e.sec_acc_staff when 0 then '0002' else null end,case e.sec_acc_staff when 0 then j.code else null end,case e.sec_acc_staff when 0 then j.name else null end,
case e.sec_acc_customer when 0 then '004' else null end,null,null,
case e.sec_acc_supplier when 0 then '004' else null end,null,null,
case when e.project_code is not null then 'FI03' else null end,e.project_code,h.name,
case e.sec_acc_bank when 0 then '0011' else null end,case e.sec_acc_bank when 0 then (select accnum from oa2nc.dbo.v_bankdoc where accname='********银行-活期') else null end,case e.sec_acc_bank when 0 then '********银行-活期' else null end,
null,null,null,null,null,convert(varchar(19),getDate(),120),0,e.cashflow_code,0
from formtable_main_22 a
left join hrmsubcompany b on a.reimbursement_unit=b.id
left join oa2nc.dbo.v_orgs c on c.name=b.subcompanyname
left join formtable_main_22_dt4 d on d.mainid=a.id
left join uf_cost_category e on e.acc_sub_code=@mx_acc_sub_code
left join uf_dept_oa2nc f on f.oa_dept=d.cost_dept
left join oa2nc.dbo.v_dept g on f.nc_dept=g.pk_dept
left join oa2nc.dbo.v_project h on h.code=e.project_code
left join uf_staff_oa2nc i on i.oa_staff=a.applicant_submit
left join [oa2nc].[dbo].v_psndoc j on i.nc_psndoc=j.pk_psndoc left join oa2nc.dbo.v_dept_normal k on g.name=k.name
where a.requestid=@requestid and d.id=@mx_id and k.pk_org=c.pk_org and j.pk_org=c.pk_org
INSERT INTO [oa2nc].[dbo].[bd_voucher]([pk_corp],[pk_group],[pk_org],[billmaker],[billdate],[creator],[creationtime],[glorgbook_code],[glorgbook],[vouchertype],[tallydate]
,[oarequestID],[oaworkcode],[Oacode],[vbillno],[explanation],[accsubjcode],[accsubjname],[currency],[exchange],[baldebitmny],[baldebitlocalmny],[balcreditmny],[balcreditlocalmny],[checktype1]
,[checkcode1],[checkname1],[checktype2],[checkcode2],[checkname2],[checktype3],[checkcode3],[checkname3],[checktype4],[checkcode4],[checkname4],[checktype5],[checkcode5],[checkname5]
,[checktype6],[checkcode6],[checkname6],[vdef1],[vdef2],[vdef3],[vdef4],[vdef5],[ts],[dr],[pk_cashflow],[cashflowflag])
select c.code,'01',c.code,'v-wantao.zhang',convert(varchar(10),getDate(),120),'v-wantao.zhang',convert(varchar(19),getDate(),120),c.accountcode,c.accountname,@vouchertype,convert(varchar(10),getDate(),120),
@requestid,@oaworkcode,doc_number,@the_number_str,g.name+j.name+d.reception_date+'在'+d.guest_place+'产生业务招待费用'+CAST(CAST(real_money AS DECIMAL(20,5)) AS VARCHAR(20))+'元',e.add_in_subject_code,e.add_in_subject_name,'CNY',1.00000,null,null,real_money,real_money,case e.sec_acc_dept when 0 then '0001' else null end,
case e.sec_acc_dept when 0 then k.code else null end,case e.sec_acc_dept when 0 then k.name else null end,
case e.sec_acc_staff when 0 then '0002' else null end,case e.sec_acc_staff when 0 then j.code else null end,case e.sec_acc_staff when 0 then j.name else null end,
case e.sec_acc_customer when 0 then '004' else null end,null,null,
case e.sec_acc_supplier when 0 then '004' else null end,null,null,
case when e.project_code is not null then 'FI03' else null end,e.project_code,h.name,
case e.sec_acc_bank when 0 then '0011' else null end,case e.sec_acc_bank when 0 then (select accnum from oa2nc.dbo.v_bankdoc where accname='********银行-活期') else null end,case e.sec_acc_bank when 0 then '********银行-活期' else null end,
null,null,null,null,null,convert(varchar(19),getDate(),120),0,e.cashflow_code,0
from formtable_main_22 a
left join hrmsubcompany b on a.reimbursement_unit=b.id
left join oa2nc.dbo.v_orgs c on c.name=b.subcompanyname
left join formtable_main_22_dt4 d on d.mainid=a.id
left join uf_cost_category e on e.acc_sub_code=@mx_acc_sub_code
left join uf_dept_oa2nc f on f.oa_dept=d.cost_dept
left join oa2nc.dbo.v_dept g on f.nc_dept=g.pk_dept
left join oa2nc.dbo.v_project h on h.code=e.project_code
left join uf_staff_oa2nc i on i.oa_staff=a.applicant_submit
left join [oa2nc].[dbo].v_psndoc j on i.nc_psndoc=j.pk_psndoc left join oa2nc.dbo.v_dept_normal k on g.name=k.name
where a.requestid=@requestid and d.id=@mx_id and k.pk_org=c.pk_org and j.pk_org=c.pk_org
if(@mx_tax>0)
begin
INSERT INTO [oa2nc].[dbo].[bd_voucher]([pk_corp],[pk_group],[pk_org],[billmaker],[billdate],[creator],[creationtime],[glorgbook_code],[glorgbook],[vouchertype],[tallydate]
,[oarequestID],[oaworkcode],[Oacode],[vbillno],[explanation],[accsubjcode],[accsubjname],[currency],[exchange],[baldebitmny],[baldebitlocalmny],[balcreditmny],[balcreditlocalmny],[checktype1]
,[checkcode1],[checkname1],[checktype2],[checkcode2],[checkname2],[checktype3],[checkcode3],[checkname3],[checktype4],[checkcode4],[checkname4],[checktype5],[checkcode5],[checkname5]
,[checktype6],[checkcode6],[checkname6],[vdef1],[vdef2],[vdef3],[vdef4],[vdef5],[ts],[dr],[pk_cashflow],[cashflowflag])
select c.code,'01',c.code,'v-wantao.zhang',convert(varchar(10),getDate(),120),'v-wantao.zhang',convert(varchar(19),getDate(),120),c.accountcode,c.accountname,@vouchertype,convert(varchar(10),getDate(),120),
@requestid,@oaworkcode,doc_number,@the_number_str,g.name+j.name+d.reception_date+'在'+d.guest_place+'产生业务招待费用税额'+CAST(CAST(d.tax AS DECIMAL(20,5)) AS VARCHAR(20))+'元',t.acc_sub_code,t.acc_sub_name,'CNY',1.00000,d.tax,d.tax,null,null,case t.sec_acc_dept when '部门' then '0001' else null end,
case t.sec_acc_dept when '部门' then k.code else null end,case t.sec_acc_dept when '部门' then k.name else null end,
case t.sec_acc_staff when '员工' then '0002' else null end,case t.sec_acc_staff when '员工' then j.code else null end,case t.sec_acc_staff when '员工' then j.name else null end,
null,null,null,
null,null,null,
case when e.project_code is not null then 'FI03' else null end,e.project_code,h.name,
case t.sec_acc_bank when 0 then '0011' else null end,case t.sec_acc_bank when 0 then (select accnum from oa2nc.dbo.v_bankdoc where accname='********银行-活期') else null end,case e.sec_acc_bank when 0 then '********银行-活期' else null end,
null,null,null,null,null,convert(varchar(19),getDate(),120),0,t.cashflow_code,0
from formtable_main_22 a
left join hrmsubcompany b on a.reimbursement_unit=b.id
left join oa2nc.dbo.v_orgs c on c.name=b.subcompanyname
left join formtable_main_22_dt4 d on d.mainid=a.id
left join uf_cost_category e on e.acc_sub_code=@mx_acc_sub_code
left join cost_nc_account t on t.acc_sub_code=e.tax_subject_code
left join uf_dept_oa2nc f on f.oa_dept=d.cost_dept
left join oa2nc.dbo.v_dept g on f.nc_dept=g.pk_dept
left join oa2nc.dbo.v_project h on h.code=e.project_code
left join uf_staff_oa2nc i on i.oa_staff=a.applicant_submit
left join [oa2nc].[dbo].v_psndoc j on i.nc_psndoc=j.pk_psndoc left join oa2nc.dbo.v_dept_normal k on g.name=k.name
where a.requestid=@requestid and d.id=@mx_id and k.pk_org=c.pk_org and j.pk_org=c.pk_org
end
set @dt_sum=@dt_sum+@mx_realmoney+@mx_tax
end
Fetch Next From this_cursor Into @mx_id
end
Close this_cursor
Deallocate this_cursor
end
INSERT INTO [oa2nc].[dbo].[bd_voucher]([pk_corp],[pk_group],[pk_org],[billmaker],[billdate],[creator],[creationtime],[glorgbook_code],[glorgbook],[vouchertype],[tallydate]
,[oarequestID],[oaworkcode],[Oacode],[vbillno],[explanation],[accsubjcode],[accsubjname],[currency],[exchange],[baldebitmny],[baldebitlocalmny],[balcreditmny],[balcreditlocalmny],[checktype1]
,[checkcode1],[checkname1],[checktype2],[checkcode2],[checkname2],[checktype3],[checkcode3],[checkname3],[checktype4],[checkcode4],[checkname4],[checktype5],[checkcode5],[checkname5]
,[checktype6],[checkcode6],[checkname6],[vdef1],[vdef2],[vdef3],[vdef4],[vdef5],[ts],[dr],[pk_cashflow],[cashflowflag])
select c.code,'01',c.code,'v-wantao.zhang',convert(varchar(10),getDate(),120),'v-wantao.zhang',convert(varchar(19),getDate(),120),c.accountcode,c.accountname,@vouchertype,convert(varchar(10),getDate(),120),
@requestid,@oaworkcode,doc_number,@the_number_str,j.name+'因'+a.applay_reason+'出差产生差旅费用'+CAST(CAST(@dt_sum AS DECIMAL(20,5)) AS VARCHAR(20))+'元',t.acc_sub_code,t.acc_sub_name,'CNY',1.00000,null,null,@dt_sum,@dt_sum,case t.sec_acc_dept when '部门' then '0001' else null end,
case t.sec_acc_dept when '部门' then k.code else null end,case t.sec_acc_dept when '部门' then k.name else null end,
case t.sec_acc_staff when '员工' then '0002' else null end,case t.sec_acc_staff when '员工' then j.code else null end,case t.sec_acc_staff when '员工' then j.name else null end,
null,null,null,
null,null,null,
case when t.project_name is not null then 'FI03' else null end,h.code,h.name,
case when t.sec_acc_bank is not null then '0011' else null end,case when t.sec_acc_bank is not null then (select accnum from oa2nc.dbo.v_bankdoc where accname='********银行-活期') else null end,case when t.sec_acc_bank is not null then '********银行-活期' else null end,
null,null,null,null,null,convert(varchar(19),getDate(),120),0,t.cashflow_code,0
from formtable_main_22 a
left join hrmsubcompany b on a.reimbursement_unit=b.id
left join oa2nc.dbo.v_orgs c on c.name=b.subcompanyname
left join uf_dept_oa2nc f on f.oa_dept=a.applay_dept
left join oa2nc.dbo.v_dept g on f.nc_dept=g.pk_dept
left join cost_nc_account t on t.acc_sub_code='224103'
left join oa2nc.dbo.v_project h on h.name=t.project_name
left join uf_staff_oa2nc i on i.oa_staff=a.applicant_submit
left join [oa2nc].[dbo].v_psndoc j on i.nc_psndoc=j.pk_psndoc left join oa2nc.dbo.v_dept_normal k on g.name=k.name
where a.requestid=@requestid and k.pk_org=c.pk_org and j.pk_org=c.pk_org
/*5 针对冲销或者实际报销金额生成凭证 */
/*---当实际报销支付金额大于0*/
if(@actual_pay_num>0)
begin
INSERT INTO [oa2nc].[dbo].[bd_voucher]([pk_corp],[pk_group],[pk_org],[billmaker],[billdate],[creator],[creationtime],[glorgbook_code],[glorgbook],[vouchertype],[tallydate]
,[oarequestID],[oaworkcode],[Oacode],[vbillno],[explanation],[accsubjcode],[accsubjname],[currency],[exchange],[baldebitmny],[baldebitlocalmny],[balcreditmny],[balcreditlocalmny],[checktype1]
,[checkcode1],[checkname1],[checktype2],[checkcode2],[checkname2],[checktype3],[checkcode3],[checkname3],[checktype4],[checkcode4],[checkname4],[checktype5],[checkcode5],[checkname5]
,[checktype6],[checkcode6],[checkname6],[vdef1],[vdef2],[vdef3],[vdef4],[vdef5],[ts],[dr],[pk_cashflow],[cashflowflag])
select c.code,'01',c.code,'v-wantao.zhang',convert(varchar(10),getDate(),120),'v-wantao.zhang',convert(varchar(19),getDate(),120),c.accountcode,c.accountname,@vouchertype,convert(varchar(10),getDate(),120),
@requestid,@oaworkcode,doc_number,@the_number_str,j.name+'因'+a.applay_reason+'出差产生差旅费用'+CAST(CAST(@dt_sum AS DECIMAL(20,5)) AS VARCHAR(20))+'元 实际报销'+CAST(CAST(@actual_pay_num AS DECIMAL(20,5)) AS VARCHAR(20)),q.acc_sub_code,q.acc_sub_name,'CNY',1.00000,@actual_pay_num,@actual_pay_num,null,null,
case q.sec_acc_dept when '部门' then '0001' else null end,
case q.sec_acc_dept when '部门' then k.code else null end,case q.sec_acc_dept when '部门' then k.name else null end,
case q.sec_acc_staff when '员工' then '0002' else null end,case q.sec_acc_staff when '员工' then j.code else null end,case q.sec_acc_staff when '员工' then j.name else null end,
null,null,null,
null,null,null,
null,null,null,
null,null,null,
null,null,null,null,null,convert(varchar(19),getDate(),120),0,null,0
from formtable_main_22 a
left join hrmsubcompany b on a.reimbursement_unit=b.id
left join oa2nc.dbo.v_orgs c on c.name=b.subcompanyname
left join cost_nc_account q on q.acc_sub_code='224103'
left join uf_dept_oa2nc f on f.oa_dept=a.applay_dept
left join oa2nc.dbo.v_dept g on f.nc_dept=g.pk_dept
left join oa2nc.dbo.v_project h on h.code=q.project_name
left join uf_staff_oa2nc i on i.oa_staff=a.applicant_submit
left join [oa2nc].[dbo].v_psndoc j on i.nc_psndoc=j.pk_psndoc left join oa2nc.dbo.v_dept_normal k on g.name=k.name
where a.requestid=@requestid and k.pk_org=c.pk_org and j.pk_org=c.pk_org
INSERT INTO [oa2nc].[dbo].[bd_voucher]([pk_corp],[pk_group],[pk_org],[billmaker],[billdate],[creator],[creationtime],[glorgbook_code],[glorgbook],[vouchertype],[tallydate]
,[oarequestID],[oaworkcode],[Oacode],[vbillno],[explanation],[accsubjcode],[accsubjname],[currency],[exchange],[baldebitmny],[baldebitlocalmny],[balcreditmny],[balcreditlocalmny],[checktype1]
,[checkcode1],[checkname1],[checktype2],[checkcode2],[checkname2],[checktype3],[checkcode3],[checkname3],[checktype4],[checkcode4],[checkname4],[checktype5],[checkcode5],[checkname5]
,[checktype6],[checkcode6],[checkname6],[vdef1],[vdef2],[vdef3],[vdef4],[vdef5],[ts],[dr],[pk_cashflow],[cashflowflag])
select c.code,'01',c.code,'v-wantao.zhang',convert(varchar(10),getDate(),120),'v-wantao.zhang',convert(varchar(19),getDate(),120),c.accountcode,c.accountname,@vouchertype,convert(varchar(10),getDate(),120),
@requestid,@oaworkcode,doc_number,@the_number_str,j.name+'因'+a.applay_reason+'出差产生差旅费用'+CAST(CAST(@dt_sum AS DECIMAL(20,5)) AS VARCHAR(20))+'元 实际报销'+CAST(CAST(@actual_pay_num AS DECIMAL(20,5)) AS VARCHAR(20)),t.acc_sub_code,t.acc_sub_name,'CNY',1.00000,null,null,@actual_pay_num,@actual_pay_num,
case when t.sec_acc_bank is not null then '0011' else null end,case when t.sec_acc_bank is not null then (select accnum from oa2nc.dbo.v_bankdoc where accname='********银行-活期') else null end,case when t.sec_acc_bank is not null then '********银行-活期' else null end,
case t.sec_acc_dept when '部门' then '0001' else null end,
case t.sec_acc_dept when '部门' then k.code else null end,case t.sec_acc_dept when '部门' then k.name else null end,
case t.sec_acc_staff when '员工' then '0002' else null end,case t.sec_acc_staff when '员工' then j.code else null end,case t.sec_acc_staff when '员工' then j.name else null end,
null,null,null,
null,null,null,
case when t.project_name is not null then 'FI03' else null end,h.code,h.name,
null,null,null,null,null,convert(varchar(19),getDate(),120),0,1124,1
from formtable_main_22 a
left join hrmsubcompany b on a.reimbursement_unit=b.id
left join oa2nc.dbo.v_orgs c on c.name=b.subcompanyname
left join uf_dept_oa2nc f on f.oa_dept=a.applay_dept
left join oa2nc.dbo.v_dept g on f.nc_dept=g.pk_dept
left join cost_nc_account t on t.acc_sub_code='100202'
left join oa2nc.dbo.v_project h on h.name=t.project_name
left join uf_staff_oa2nc i on i.oa_staff=a.applicant_submit
left join [oa2nc].[dbo].v_psndoc j on i.nc_psndoc=j.pk_psndoc left join oa2nc.dbo.v_dept_normal k on g.name=k.name
where a.requestid=@requestid and k.pk_org=c.pk_org and j.pk_org=c.pk_org
end
/*冲销金额大于0或者不为空时--*/
if((@write_off_num is not null) and (@write_off_num>0))
begin
INSERT INTO [oa2nc].[dbo].[bd_voucher]([pk_corp],[pk_group],[pk_org],[billmaker],[billdate],[creator],[creationtime],[glorgbook_code],[glorgbook],[vouchertype],[tallydate]
,[oarequestID],[oaworkcode],[Oacode],[vbillno],[explanation],[accsubjcode],[accsubjname],[currency],[exchange],[baldebitmny],[baldebitlocalmny],[balcreditmny],[balcreditlocalmny],[checktype1]
,[checkcode1],[checkname1],[checktype2],[checkcode2],[checkname2],[checktype3],[checkcode3],[checkname3],[checktype4],[checkcode4],[checkname4],[checktype5],[checkcode5],[checkname5]
,[checktype6],[checkcode6],[checkname6],[vdef1],[vdef2],[vdef3],[vdef4],[vdef5],[ts],[dr],[pk_cashflow],[cashflowflag])
select c.code,'01',c.code,'v-wantao.zhang',convert(varchar(10),getDate(),120),'v-wantao.zhang',convert(varchar(19),getDate(),120),c.accountcode,c.accountname,@vouchertype,convert(varchar(10),getDate(),120),
@requestid,@oaworkcode,doc_number,@the_number_str,j.name+'因'+a.applay_reason+'出差产生差旅费用'+CAST(CAST(@dt_sum AS DECIMAL(20,5)) AS VARCHAR(20))+'元 冲销借款'+CAST(CAST(@write_off_num AS DECIMAL(20,5)) AS VARCHAR(20)),q.acc_sub_code,q.acc_sub_name,'CNY',1.00000,@write_off_num,@write_off_num,null,null,
case q.sec_acc_dept when '部门' then '0001' else null end,case q.sec_acc_dept when '部门' then k.code else null end,case q.sec_acc_dept when '部门' then k.name else null end,
case q.sec_acc_staff when '员工' then '0002' else null end,case q.sec_acc_staff when '员工' then j.code else null end,case q.sec_acc_staff when '员工' then j.name else null end,
null,null,null,
null,null,null,
null,null,null,
null,null,null,
null,null,null,null,null,convert(varchar(19),getDate(),120),0,q.cashflow_code,1
from formtable_main_22 a
left join hrmsubcompany b on a.reimbursement_unit=b.id
left join oa2nc.dbo.v_orgs c on c.name=b.subcompanyname
left join cost_nc_account q on q.acc_sub_code='224103'
left join uf_dept_oa2nc f on f.oa_dept=a.applay_dept
left join oa2nc.dbo.v_dept g on f.nc_dept=g.pk_dept
left join oa2nc.dbo.v_project h on h.name=q.project_name
left join uf_staff_oa2nc i on i.oa_staff=a.applicant_submit
left join [oa2nc].[dbo].v_psndoc j on i.nc_psndoc=j.pk_psndoc left join oa2nc.dbo.v_dept_normal k on g.name=k.name
where a.requestid=@requestid and k.pk_org=c.pk_org and j.pk_org=c.pk_org
INSERT INTO [oa2nc].[dbo].[bd_voucher]([pk_corp],[pk_group],[pk_org],[billmaker],[billdate],[creator],[creationtime],[glorgbook_code],[glorgbook],[vouchertype],[tallydate]
,[oarequestID],[oaworkcode],[Oacode],[vbillno],[explanation],[accsubjcode],[accsubjname],[currency],[exchange],[baldebitmny],[baldebitlocalmny],[balcreditmny],[balcreditlocalmny],[checktype1]
,[checkcode1],[checkname1],[checktype2],[checkcode2],[checkname2],[checktype3],[checkcode3],[checkname3],[checktype4],[checkcode4],[checkname4],[checktype5],[checkcode5],[checkname5]
,[checktype6],[checkcode6],[checkname6],[vdef1],[vdef2],[vdef3],[vdef4],[vdef5],[ts],[dr],[pk_cashflow],[cashflowflag])
select c.code,'01',c.code,'v-wantao.zhang',convert(varchar(10),getDate(),120),'v-wantao.zhang',convert(varchar(19),getDate(),120),c.accountcode,c.accountname,@vouchertype,convert(varchar(10),getDate(),120),
@requestid,@oaworkcode,doc_number,@the_number_str,j.name+'因'+a.applay_reason+'出差产生差旅费用'+CAST(CAST(@dt_sum AS DECIMAL(20,5)) AS VARCHAR(20))+'元 实际报销'+CAST(CAST(@write_off_num AS DECIMAL(20,5)) AS VARCHAR(20)),t.acc_sub_code,t.acc_sub_name,'CNY',1.00000,null,null,@write_off_num,@write_off_num,case t.sec_acc_dept when '部门' then '0001' else null end,
case t.sec_acc_dept when '部门' then k.code else null end,case t.sec_acc_dept when '部门' then k.name else null end,
case t.sec_acc_staff when '员工' then '0002' else null end,case t.sec_acc_staff when '员工' then j.code else null end,case t.sec_acc_staff when '员工' then j.name else null end,
null,null,null,
null,null,null,
null,null,null,
case t.sec_acc_bank when '银行' then '0011' else null end,case t.sec_acc_bank when '银行' then (select accnum from oa2nc.dbo.v_bankdoc where accname='********银行-活期') else null end,case t.sec_acc_bank when '银行' then '********银行-活期' else null end,
null,null,null,null,null,convert(varchar(19),getDate(),120),0,t.cashflow_code,1
from formtable_main_22 a
left join hrmsubcompany b on a.reimbursement_unit=b.id
left join oa2nc.dbo.v_orgs c on c.name=b.subcompanyname
left join cost_nc_account t on t.acc_sub_code='12210204'
left join uf_dept_oa2nc f on f.oa_dept=a.applay_dept
left join oa2nc.dbo.v_dept g on f.nc_dept=g.pk_dept
left join oa2nc.dbo.v_project h on h.code=t.project_name
left join uf_staff_oa2nc i on i.oa_staff=a.applicant_submit
left join [oa2nc].[dbo].v_psndoc j on i.nc_psndoc=j.pk_psndoc left join oa2nc.dbo.v_dept_normal k on g.name=k.name
where a.requestid=@requestid and k.pk_org=c.pk_org and j.pk_org=c.pk_org
end
GO
2790
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
