U8:用友U8导入应付单录入、应收单录入存储过程

 

用友U8导入应收单录入、应付单录入的存储过程,以下SQL脚本整合前几天写的《u8:应付单据录入生成编号 》会更好。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
--  应收单据\应付单据都可以用此程序,但需要注意一下两者编号长度不同.
DECLARE
   c_iface cursor  for
    select row_id, cstatus,cBatchNum from u8api.dbo.ap_vouch_iface avi
    where avi.cstatus is null
     order by row_id ;
declare
   @user_name nvarchar(30) = 'demo', -- 用户名,如: DEMO
   @cLink nvarchar(20), -- 应付票据主键(AP主键): P0 + 年月(4位)+ 3位流水号
   @cVouchID nvarchar(20),
   @auto_ID BIGINT,
    
   -- begin: voucherHistory表
   @AutoId int-- 自动生成
   @CardNumber nvarchar(20),
   @iRDFlagSeed int,
   @cContent nvarchar(50),
   @cContentRule nvarchar(50),
   @cSeed nvarchar(120),
   @cNumber nvarchar(30),
   @bEmpty bit,
   -- end: voucherHistory表
   @cDeptCode nvarchar(50), -- 部门代码
   @cDeptName nvarchar(150),  -- 部门名称
   @row_id int,
   @cStatus nvarchar(20), -- 状态: 空,S,E,F.
    
   @fetch_status int;
declare
   @cBatchNum  nvarchar(30),  -- 导入批次号
   @cPsn_Num   nvarchar(30),  -- 工号 
   @cPsn_Name  nvarchar(50),  -- 员工姓名
   @cDwCode    nvarchar(30),  -- 供应商代码
   @cDwName    nvarchar(250), -- 供应商名称
   @cPayCode   nvarchar(20), -- 付款条件代码
   @cPayName   nvarchar(150); -- 付款条件名称
BEGIN
 
   open  c_iface  ;
   fetch next from c_iface into @row_id,@cStatus,@cBatchNum ;
   set @fetch_status = @@FETCH_STATUS
   while @fetch_status =0
   begin 
    
  -- 第一步: 1.获取 AP主键
  /*
    declare csr_voucherHistory cursor for     
   SELECT TOP 9 autoId,CardNumber,iRdFlagSeed,cContent,
          cContentRule, cSeed,cNumber,bEmpty
    FROM UFDATA_012_2014.dbo.[VoucherHistory] h --记录数: 13605
    where 1=1  --and cSeed like '2023%'
     --and cNumber = '1'
    and CardNumber like 'P0'
    AND h.cSeed = CONVERT(nvarchar(6), getdate(), 112) ;
       
   open csr_voucherHistory
   fetch next from csr_voucherHistory into @autoId,@CardNumber,@iRdFlagSeed,@cContent,
          @cContentRule, @cSeed,@cNumber,@bEmpty
   while @@fetch_status  = 0
   begin
      set @cLink = @CardNumber+ @cSeed +  right('00000000'+@cNumber ,3);
     fetch next from csr_voucherHistory into @autoId,@CardNumber,@iRdFlagSeed,@cContent,
          @cContentRule, @cSeed,@cNumber,@bEmpty
   end;
   close  csr_voucherHistory;
   deallocate csr_voucherHistory    
   PRINT @cLink;
   */
   -- 单据编号: AP: P02307029 ; AR: R023070001
   if  exists(SELECT autoId,CardNumber,iRdFlagSeed,cContent,
          cContentRule, cSeed,cNumber,bEmpty
    FROM UFDATA_012_2014.dbo.[VoucherHistory] h --记录数: 13605
    where 1=1  --and cSeed like '2023%'
     --and cNumber = '1'
    and CardNumber like 'P0'
    and cContent='单据日期'
    AND h.cSeed = CONVERT(nvarchar(6), getdate(), 112) )
    BEGIN
       update h
        set cNumber = CAST(cNumber AS INT) +1
      from UFDATA_012_2014.dbo.[VoucherHistory] h  
    where 1=1 
    and CardNumber like 'P0'
    and cContent='单据日期'
    AND h.cSeed = CONVERT(nvarchar(6), getdate(), 112);
     
    select @cLink = CardNumber+ RIGHT(cSeed,4) +  right('0000000'+ cNumber ,3)  ,
          @cVouchID = RIGHT(cSeed,4) +  right('0000000'+ cNumber ,3)      
      FROM UFDATA_012_2014.dbo.[VoucherHistory] h
    where 1=1 
    and CardNumber like 'P0'
    and cContent='单据日期'
    AND h.cSeed = CONVERT(nvarchar(6), getdate(), 112);
    END 
    else
    BEGIN
       insert into  UFDATA_012_2014.dbo.[VoucherHistory](CardNumber,iRdFlagSeed,cContent,
          cContentRule, cSeed,cNumber,bEmpty)
        values(N'P0',NULL,N'单据日期',N'月',CONVERT(nvarchar(6), getdate(), 112),1,0 )  ;
         
        SET @cLink = N'P0' + CONVERT(nvarchar(4), getdate(), 12) + right('0000001' ,3);
        set @cVouchID = CONVERT(nvarchar(4), getdate(), 12) + right('0000001' ,3) ;
    END;
    PRINT @cLink; 
     
   -- 第一步: 2. 获取自动流水号 AUTO_ID
    SELECT  @auto_ID = max(AUTO_ID)+1      
      FROM UFDATA_012_2014.DBO.Ap_Vouch
      where cLink like 'P0'+ CONVERT(nvarchar(4),GETDATE(), 12)+'%'
      and cPZNum is null;
       
    
   -- 第二步: 验证数据
    
       -- 1.部门代码验证
      --    set @cDeptName = '电线一部';
      select @cDeptName = cDeptName from U8API.dbo.Ap_Vouch_Iface avi
      where row_id = @row_id;
       
        if exists (SELECT TOP 1 cDepCode,cDepName
         FROM ufData_012_2014.dbo.[Department] dp
        where dp.cDepName = @cDeptName )
        begin
        SELECT TOP 1 @cDeptCode= cDepCode
         FROM ufData_012_2014.dbo.[Department] dp
        where dp.cDepName = @cDeptName ;
        end
       else
       begin
          set @cDeptCode = null;
       end;
        
       if @cDeptCode is not null
       begin 
       update avi
         set  cDeptCode= @cDeptCode ,
              dmodifySystime = GETDATE()
         from U8API.dbo.Ap_Vouch_Iface avi
         where avi.row_id = @row_id;
       end  
       else
       begin
        update avi
          set cMsgCode = coalesce(cMsgCode,'') + 'U8-20001;' ,
              cMsg = coalesce(cMsg,'') +'部门名:'+ @cDeptName+ ',无法匹配部门代码.' ,
              dmodifySystime = GETDATE()
         from U8API.dbo.Ap_Vouch_Iface avi
         where avi.row_id = @row_id;
       end;
      -- 2.1  业务员验证
      -- 人员表
      if exists ( select cPersonCode from U8API.dbo.Ap_Vouch_Iface avi_emp
        where row_id = @row_id and cPersonCode is Not null )
        begin
      SELECT top 1 @cPsn_Num =
          cPsn_num
       FROM ufData_012_2014.dbo.[hr_hi_person] emp
       where emp.cPsn_Num = ( select cPersonCode from U8API.dbo.Ap_Vouch_Iface avi_emp
        where row_id = @row_id and cPersonCode is Not null ) ;
     if @cPsn_Num is null
       begin
        update avi
          set cMsgCode = coalesce(cMsgCode,'') + 'U8-20002;' ,
              cMsg = coalesce(cMsg,'') + '工号:'+ @cPsn_Num+ ',无法匹配工号.' ,
              dmodifySystime = GETDATE()
         from U8API.dbo.Ap_Vouch_Iface avi
         where avi.row_id = @row_id;
       end;
      end ;
        
     -- 2.2  业务员验证
      -- 人员表
      if exists (select cPersonName from U8API.dbo.Ap_Vouch_Iface avi_emp
        where row_id = @row_id  and cPersonName is Not null )
        begin
     SELECT top 1 @cPsn_Name =
          cPsn_Name , @cPsn_Num = cPsn_Num
       FROM ufData_012_2014.dbo.[hr_hi_person] emp
       where emp.cPsn_Name = (select cPersonName from U8API.dbo.Ap_Vouch_Iface avi_emp
        where row_id = @row_id  and cPersonName is Not null ) ;
     if @cPsn_Name is null
       begin
        update avi
          set cMsgCode = coalesce(cMsgCode,'') + 'U8-20003;' ,
              cMsg = coalesce(cMsg,'') + '姓名:'+ @cPsn_Name+ ',无法匹配工号.' ,
              dmodifySystime = GETDATE()
         from U8API.dbo.Ap_Vouch_Iface avi
         where avi.row_id = @row_id;
       end
     else
     begin
        update avi
          set avi.cPersonCode = @cPsn_Num,
              dmodifySystime = GETDATE()
          from U8API.dbo.Ap_Vouch_Iface avi
        where row_id = @row_id ;
         
     end;
     end;
     -- 3. 供应商名称验证
      select @cDwName = v.cDwName  from U8API.dbo.Ap_Vouch_Iface v
      where row_id = @row_id
      ;
      if isnull(@cDwName,'')!=''
      select top 1 @cDwCode = pv.cVenCode from UFDATA_012_2014.dbo.Vendor  pv
      where pv.cVenName = @cDwName;
       
      if ISNULL(@cDwCode ,'') != ''
      begin
         update avi
          set avi.cDwCode = @cDwCode,
              dmodifySystime = GETDATE()
          from U8API.dbo.Ap_Vouch_Iface avi
        where row_id = @row_id ;       
       end
     else
     begin     
        update avi
          set cMsgCode = coalesce(cMsgCode,'') + 'U8-20004;' ,
              cMsg = coalesce(cMsg,'') + '供应商名称:'+ @cDwName+ ',无法匹配供应商代码.' ,
              dmodifySystime = GETDATE()
         from U8API.dbo.Ap_Vouch_Iface avi
         where avi.row_id = @row_id;
     end;
        
     --4. 付款条件 验证
     select @cPayName = v.cPayName  from U8API.dbo.Ap_Vouch_Iface v
      where row_id = @row_id ;
       
     select top 1 @cPayCode  = cPayCode
     from UFDATA_012_2014.dbo.PayCondition
     where cPayName = @cPayName;
       
     if isnull(@cPayCode ,'') != ''
     begin
        update avi
          set avi.cPayCode = @cPayCode,
              dmodifySystime = GETDATE()
          from U8API.dbo.Ap_Vouch_Iface avi
        where row_id = @row_id ;
     end
     else
     begin
        update avi
          set cMsgCode = coalesce(cMsgCode,'') + 'U8-20005;' ,
              cMsg = coalesce(cMsg,'') + '付款条件名称:'+ @cDwName+ ',无法匹配付款条件代码.' ,
              dmodifySystime = GETDATE()
         from U8API.dbo.Ap_Vouch_Iface avi
         where avi.row_id = @row_id;
     end;
      
        
     
  -- SELECT @cBatchNum = BatchNum from u8api.dbo.Ap_Vouch_Iface where row_id= @row_id;
    
   -- 第三步: 1.导入应付录入表头
   insert into UFDATA_012_2014.dbo.ap_vouch(cLink,
                cVouchType,
                cVouchID,
                cVouchID1,
                dVouchDate,
                cDwCode,
                cDeptCode,
                cPerson,
                cItem_Class,
                cItemCode,
                cDigest,
                cCode,
                cexch_name,
                iExchRate,
                bd_c,
                iAmount,
                iAmount_f,
                iRAmount,
                iRAmount_f,
                cPayCode,
                cOperator,
                cCheckMan,
                cCoVouchType,
                cDestNo,
                cSrcNo,
                bStartFlag,
                cPZid,
                cFlag,
                cDefine1,
                cDefine2,
                cDefine3,
                cDefine4,
                cDefine5,
                cDefine6,
                cDefine7,
                cDefine8,
                cDefine9,
                cDefine10,
                iAmount_s,
                iRAmount_s,
                VT_ID,
                --Ufts,
                iClosesID,
                iCoClosesID,
                cDefine11,
                cDefine12,
                cDefine13,
                cDefine14,
                cDefine15,
                cDefine16,
                cItemName,
                cGatheringPlan,
                dCreditStart,
                iCreditPeriod,
                dGatheringDate,
                dcreatesystime,
                dverifysystime,
                dmodifysystime,
                cmodifier,
                dmoddate,
                dverifydate,
                Auto_ID,
                cPZNum,
                doutbilldate,
                iPrintCount,
                cPluginsourcetype,
                iPluginsourceautoid,
                cPluginsourceautoid,
                iBusType,
                cagentcuscode,
                cOrderNo,
                cContractType,
                iSource,
                cContractID,
                csysbarcode,
                iDiscountTaxType,
                iTaxRate)
     SELECT @cLink,
            cVouchType,
            @cVouchID,
            cVouchID1,
            convert(date,getdate(),112) as dVouchDate,
            cDwCode,
            cDeptCode,
            cPerson,
            cItem_Class,
            cItemCode,
            cDigest,
            cCode,
            cexch_name,
            iExchRate,
            bd_c,
            iAmount,
            iAmount_f,
            iRAmount,
            iRAmount_f,
            cPayCode,
            @user_name as cOperator,
            null as cCheckMan,
            cCoVouchType,
            cDestNo,
            cSrcNo,
            bStartFlag,
            cPZid,
            cFlag,
            cDefine1,
            cDefine2,
            cDefine3,
            cDefine4,
            cDefine5,
            cDefine6,
            cDefine7,
            cDefine8,
            cDefine9,
            cDefine10,
            iAmount_s,
            iRAmount_s,
            VT_ID,
            --Ufts,
            iClosesID,
            iCoClosesID,
            cDefine11,
            cDefine12,
            cDefine13,
            cDefine14,
            cDefine15,
            cDefine16,
            cItemName,
            cGatheringPlan,
            dCreditStart,
            iCreditPeriod,
            dGatheringDate,
            getdate() as dcreatesystime,
            null as dverifysystime,
            getdate() dmodifysystime,
            @user_name as cmodifier,
            null as dmoddate,
            null dverifydate,
            @Auto_ID,
            cPZNum,
            doutbilldate,
            iPrintCount,
            cPluginsourcetype,
            iPluginsourceautoid,
            cPluginsourceautoid,
            iBusType,
            cagentcuscode,
            cOrderNo,
            cContractType,
            iSource,
            cContractID,
            '||app0|'+@cVouchID csysbarcode,
            iDiscountTaxType,
            iTaxRate
             FROM U8API.DBO.Ap_Vouch_Iface 
            where  -- cLink = 'P02306002'
              cBatchNum = @cBatchNum
             and row_id = @row_id
            ;
   -- 第三步: 2.导入应付录入明细
    INSERT INTO UFDATA_012_2014.DBO.Ap_Vouchs (
            --Auto_ID,
        cLink,
        cDwCode,
        cDeptCode,
        cPerson,
        cItem_Class,
        cItemCode,
        cDigest,
        cCode,
        cexch_name,
        iExchRate,
        bd_c,
        iAmount,
        iAmount_f,
        cItemName,
        iAmt_s,
        cExpCode,
        iTaxRate,
        iTax,
        iNatTax,
        cDefine22,
        cDefine23,
        cDefine24,
        cDefine25,
        cDefine26,
        cDefine27,
        cDefine28,
        cDefine29,
        cDefine30,
        cDefine31,
        cDefine32,
        cDefine33,
        cDefine34,
        cDefine35,
        cDefine36,
        cDefine37,
        iNoTaxAmount_f,
        iNoTaxAmount)
    select --Auto_ID,
        @cLink,
        cDwCode,
        cDeptCode,
        cPerson,
        cItem_Class,
        cItemCode,
        cDigest,
        cCode,
        cexch_name,
        iExchRate,
        bd_c,
        iAmount,
        iAmount_f,
        cItemName,
        iAmt_s,
        cExpCode,
        iTaxRate,
        iTax,
        iNatTax,
        cDefine22,
        cDefine23,
        cDefine24,
        cDefine25,
        cDefine26,
        cDefine27,
        cDefine28,
        cDefine29,
        cDefine30,
        cDefine31,
        cDefine32,
        cDefine33,
        cDefine34,
        cDefine35,
        cDefine36,
        cDefine37,
        iNoTaxAmount_f,
        iNoTaxAmount
         from U8API.dbo.Ap_Vouchs_Iface avi
         where --avi.cLink ='P02306002'
         -- row_id = @row_id   
         cBatchNum = @cBatchNum  
         
           
     fetch next from c_iface into @row_id,@cStatus, @cBatchNum;
     set @fetch_status = @@FETCH_STATUS
   end;
   close  c_iface;
   deallocate c_iface;
         
   /*待解决问题:2023-07-08
     1、接口表 AP_Vouch_iface增加字段: 部门名称、业务员名称(工号)、供应商名称、付款条件名称、项目名称 ,以供用户在EXCEL表上填写名称.       
     2、对增加字段转化代码,写入对应字段:cDeptCode、cPerson、cDwCode、cPayCode、未知
     3、接口表 AP_Vouch_iface增加字段:状态:(初始状态为空, S:表示导入成功, E:有错误, F:导入失败)
   */          
END;
    
  -- select CONVERT(nvarchar(6), getdate(), 112)
/* 
-- 前置环境:先创建数据库U8API,再在U8API库中创建两个接口表Ap_Vouch_Iface、Ap_Vouchs_Iface.
-- 应付录入头表接口表
alter table U8API.dbo.Ap_Vouch_Iface add cDeptName nvarchar(150); -- 部门名称
alter table U8API.dbo.Ap_Vouch_Iface add cPersonCode nvarchar(150); -- 业务员名称(工号)
alter table U8API.dbo.Ap_Vouch_Iface add cPersonName nvarchar(150); --业务员名称(姓名)
alter table U8API.dbo.Ap_Vouch_Iface add cDwName nvarchar(150); -- 供应商名称
alter table U8API.dbo.Ap_Vouch_Iface add cPayName nvarchar(150); -- 付款条件名称
alter table U8API.dbo.Ap_Vouch_Iface add cStatus nvarchar(20); -- 状态:(初始状态为空, S:表示导入成功, E:有错误, F:导入失败)
alter table U8API.dbo.Ap_Vouch_Iface add cMsgCode nvarchar(150); -- 错误代码
alter table U8API.dbo.Ap_Vouch_Iface add cMsg nvarchar(250); -- 错误信息
alter table U8API.dbo.Ap_Vouch_Iface add row_id int identity(1,1) not null; -- 自动流水号
alter table U8API.dbo.Ap_Vouch_Iface add cBatchNum  nvarchar(30);  -- 导入的批次号,建议一张AP,一个号,
alter table U8API.dbo.Ap_Vouch_Iface add cTrxNum nvarchar(50);  -- 发票编号,用户可以要求指定生成的应付发票编号.
alter table U8API.dbo.Ap_Vouch_Iface add debitCredit nvarchar(20); -- 借贷方向(值范围:借:1,贷:0)
*/
 
/*
方向,对应科目,币种,汇率,原币金额,本币金额,部门,业务员,项目,摘要
-- 应付录入明细表接口表
--alter table U8API.dbo.Ap_Vouchs_Iface add row_id int identity(1,1) not null; -- 自动流水号 (已存在 AUTO_ID字段)
alter table U8API.dbo.Ap_Vouchs_Iface add cBatchNum  nvarchar(30);  -- 导入的批次号,建议一张AP,一个号,
alter table U8API.dbo.Ap_Vouchs_Iface add debitCredit nvarchar(20); -- 借贷方向(值范围:借:1,贷:0)
*/

  

posted @   samrv  阅读(460)  评论(0编辑  收藏  举报
相关博文:
阅读排行:
· PowerShell开发游戏 · 打蜜蜂
· 在鹅厂做java开发是什么体验
· 百万级群聊的设计实践
· WPF到Web的无缝过渡:英雄联盟客户端的OpenSilver迁移实战
· 永远不要相信用户的输入:从 SQL 注入攻防看输入验证的重要性
历史上的今天:
2022-08-03 EBS:请求运行出错
点击右上角即可分享
微信分享提示