上线时借款余额初始化功能的标准实施方案-创新互联
业务场景:
创新互联建站凭借在网站建设、网站推广领域领先的技术能力和多年的行业经验,为客户提供超值的营销型网站建设服务,我们始终认为:好的营销型网站就是好的业务员。我们已成功为企业单位、个人等客户提供了网站建设、成都网站建设服务,以良好的商业信誉,完善的服务及深厚的技术力量处于同行领先地位。上线时,客户往往在财务软件中还有个人借款余额,通常希望能导入到费控系统,这样填写报销单时,能够冲销原来手工借支的款项。基于此目的,我们提供了下面的标准实施方案来解决。
1、确保数据库中有借款余额表(TLOAN_INIT)
CREATE TABLE [dbo].[TLOAN_INIT]( [LI_ID] [varchar](50) NOT NULL, [LI_USER] [varchar](50) NOT NULL CONSTRAINT [DF_TLOAN_INIT_LI_USER] DEFAULT (''), [LI_SUBJECT_FEE] [varchar](50) NOT NULL CONSTRAINT [DF_TLOAN_INIT_LI_SUBJECT_FEE] DEFAULT (''), [LI_AMOUNT] [numeric](18, 2) NOT NULL CONSTRAINT [DF_TLOAN_INIT_LI_AMOUNT] DEFAULT ((0)), [LI_REMARK] [varchar](2000) NOT NULL CONSTRAINT [DF_TLOAN_INIT_LI_REMARK] DEFAULT (''), [LI_CREATE_DATETIME] [datetime] NOT NULL CONSTRAINT [DF_TLOAN_INIT_LI_CREATE_DATETIME] DEFAULT (getdate()), [LI_ACTIVE] [char](1) NOT NULL CONSTRAINT [DF_TLOAN_INIT_LI_ACTIVE] DEFAULT ('N'), CONSTRAINT [PK_TLOAN_INIT] PRIMARY KEY CLUSTERED ( [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),处理借款余额表,把数据写入单据主表和明细表,基本原理是虚拟一张借款单出来。
CREATE proc p_uf_InitLoanAmount as begin declare @li_id varchar(50)='' declare @user_id varchar(50)='' declare @fee_id varchar(50)='' declare @amount float=0 declare @remark varchar(50)='' declare @bill_id varchar(50)='' declare @bill_no varchar(50)='' declare @project_id varchar(50)='' declare @i int=0 --创建虚拟单号 set @bill_no='LI'+CAST(DATEPART(year,GETDATE()) AS VARCHAR(50))+'-'+CAST(DATEPART(MONTH,GETDATE()) AS VARCHAR(50))+'-'+CAST(DATEPART(DAY,GETDATE()) AS VARCHAR(50)) +'-'+CAST(DATEPART(HOUR,GETDATE()) AS VARCHAR(50))+'-'+CAST(DATEPART(minute,GETDATE()) AS VARCHAR(50))+'-'+CAST(DATEPART(second,GETDATE()) AS VARCHAR(50)) --循环写入,并改写标志位,避免重复导入 select * from tloan_init DECLARE loan_init_Cursor CURSOR FOR SELECT li_user,li_subject_fee,li_amount,li_remark,li_id ,LI_PROJECT from tloan_init where li_active='N'; OPEN loan_init_Cursor; FETCH NEXT FROM loan_init_Cursor into @user_id,@fee_id,@amount,@remark,@li_id,@project_id; WHILE @@FETCH_STATUS = 0 BEGIN set @i=@i+1 set @bill_id=NEWID() --写入表表 insert into TBILL (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) --写入明细表 insert into TBILL_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) --更新标志位 update tloan_init set li_active='Y' where li_id=@li_id FETCH NEXT FROM loan_init_Cursor into @user_id,@fee_id,@amount,@remark,@li_id,@project_id; END; CLOSE loan_init_Cursor; DEALLOCATE loan_init_Cursor; select CAST(@i as varchar(50))+' 行数据导入完成' end
4、完成,在报销单中即可冲销上面的借款。
另外有需要云服务器可以了解下创新互联scvps.cn,海内外云服务器15元起步,三天无理由+7*72小时售后在线,公司持有idc许可证,提供“云服务器、裸金属服务器、高防服务器、香港服务器、美国服务器、虚拟主机、免备案服务器”等云主机租用服务以及企业上云的综合解决方案,具有“安全稳定、简单易用、服务可用性高、性价比高”等特点与优势,专为企业上云打造定制,能够满足用户丰富、多元化的应用场景需求。
名称栏目:上线时借款余额初始化功能的标准实施方案-创新互联
链接地址:http://myzitong.com/article/gooeg.html