业务场景:

上线时，客户往往在财务软件中还有个人借款余额，通常希望能导入到费控系统，这样填写报销单时，能够冲销原来手工借支的款项。基于此目的，我们提供了下面的标准实施方案来解决。


1、确保数据库中有借款余额表（TLOAN_INIT）

CREATETABLE[dbo].[TLOAN_INIT]([LI_ID][varchar](50)NOTNULL,[LI_USER][varchar](50)NOTNULLCONSTRAINT[DF_TLOAN_INIT_LI_USER]DEFAULT(''),[LI_SUBJECT_FEE][varchar](50)NOTNULLCONSTRAINT[DF_TLOAN_INIT_LI_SUBJECT_FEE]DEFAULT(''),[LI_AMOUNT][numeric](18,2)NOTNULLCONSTRAINT[DF_TLOAN_INIT_LI_AMOUNT]DEFAULT((0)),[LI_REMARK][varchar](2000)NOTNULLCONSTRAINT[DF_TLOAN_INIT_LI_REMARK]DEFAULT(''),[LI_CREATE_DATETIME][datetime]NOTNULLCONSTRAINT[DF_TLOAN_INIT_LI_CREATE_DATETIME]DEFAULT(getdate()),[LI_ACTIVE][char](1)NOTNULLCONSTRAINT[DF_TLOAN_INIT_LI_ACTIVE]DEFAULT('N'),CONSTRAINT[PK_TLOAN_INIT]PRIMARYKEYCLUSTERED([LI_ID]ASC)WITH(PAD_INDEX=OFF,STATISTICS_NORECOMPUTE=OFF,IGNORE_DUP_KEY=OFF,ALLOW_ROW_LOCKS=ON,ALLOW_PAGE_LOCKS=ON)ON[PRIMARY])ON[PRIMARY]

2、通过自定义扩展页面导入，或者excel提单导入的方式，把客户整理好的借款余额数据，导入到上面的表中


3、执行存储过程（p_uf_InitLoanAmount），处理借款余额表，把数据写入单据主表和明细表，基本原理是虚拟一张借款单出来。

CREATEprocp_uf_InitLoanAmountasbegindeclare@li_idvarchar(50)=''declare@user_idvarchar(50)=''declare@fee_idvarchar(50)=''declare@amountfloat=0declare@remarkvarchar(50)=''declare@bill_idvarchar(50)=''declare@bill_novarchar(50)=''declare@project_idvarchar(50)=''declare@iint=0--创建虚拟单号set@bill_no='LI'+CAST(DATEPART(year,GETDATE())ASVARCHAR(50))+'-'+CAST(DATEPART(MONTH,GETDATE())ASVARCHAR(50))+'-'+CAST(DATEPART(DAY,GETDATE())ASVARCHAR(50))+'-'+CAST(DATEPART(HOUR,GETDATE())ASVARCHAR(50))+'-'+CAST(DATEPART(minute,GETDATE())ASVARCHAR(50))+'-'+CAST(DATEPART(second,GETDATE())ASVARCHAR(50))--循环写入，并改写标志位，避免重复导入select*fromtloan_initDECLAREloan_init_CursorCURSORFORSELECTli_user,li_subject_fee,li_amount,li_remark,li_id,LI_PROJECTfromtloan_initwhereli_active='N';OPENloan_init_Cursor;FETCHNEXTFROMloan_init_Cursorinto@user_id,@fee_id,@amount,@remark,@li_id,@project_id;WHILE@@FETCH_STATUS=0BEGINset@i=@i+1set@bill_id=NEWID()--写入表表insertintoTBILL(BILL_NO,BILL_ID,BILL_TEMPLATE_TYPE,BILL_USER,BILL_AMOUNT,BILL_FLAG,BILL_REMARK,bill_project)values(@bill_no+RIGHT(@bill_id,4),@bill_id,'T-L',@user_id,@amount,'CLOSE',@remark,@project_id)--写入明细表insertintoTBILL_CONTENT(BILLC_BILL_ID,BILLC_SEQ,BILLC_USER,BILLC_SUBJECT_FEE,BILLC_AMOUNT,BILLC_LAMOUNT,BILLC_TYPE,billc_project)values(@bill_id,1,@user_id,@fee_id,@amount,@amount,'临时借款',@project_id)--更新标志位updatetloan_initsetli_active='Y'whereli_id=@li_idFETCHNEXTFROMloan_init_Cursorinto@user_id,@fee_id,@amount,@remark,@li_id,@project_id;END;CLOSEloan_init_Cursor;DEALLOCATEloan_init_Cursor;selectCAST(@iasvarchar(50))+'行数据导入完成'end

4、完成，在报销单中即可冲销上面的借款。