上海光大项目

来自ling
跳转至: 导航搜索

文档

文件:光大证券 德勤进项功能与用友网报集成讨论 20180925.docx 文件:光大证券-功能需求说明书- 进项接口规范V1.0 1101.docx 文件:光大证券-项目需求变更说明书20180718.docx 文件:进项接口整理版本V1.7.pdf

svn

https://cngdctax-svn01.atrapa.deloitte.com/svn/tmsvatsrc/sourcecode/branches/gd

相关环境信息

静安区新闸路1508号18楼 计财部

PRD环境 中间那台跳板机.administrator/332211

Guangda.physical.PNG

系统账号密码:admin/xts9752

更多参考项目部署#上海光大nginx安装方案

内网环境

tomcat服务器 	Version 	10.173.20.15	9090	C:\server\tomcat_gd	 
oracl数据库 10.173.20.15	1521	orcl TMSVATGD/TMSVATGD

网络拓扑

Guangda-network.PNG

运维信息

  • nginx 10.0.52.136 10.0.52.137
  • tomcat 10.0.62.126 10.0.62.127
  • db 10.0.26.126
  • 所有linux的用户名密码为root/Windows2008@ebscn
  • 查看磁盘情况 df -lh
  • 可以通过中间那台电脑中linux工具连接以上服务器

db运维

登录db服务器

登录10.0.62.126或10.0.62.127

ssh 10.0.26.126

密码

Windows2008@ebscn

切换oracle用户

su - oracle #不能没有-否者不能执行sqlplus

sqlplus登录

sqlplus tmsvat/TmsVat2016

执行sql

select * from .....

数据备份

如果没有directory,有请忽略,链接sqlplus后执行以下命令

create directory vat as '/home/oracle/vat';
mkdir -p /home/oracle/vat

数据备份

expdp tmsvat/TmsVat2016@VAT directory=vat schemas=tmsvat dumpfile=tmsvat.dmp logfile=tmsvat.log exclude=TABLE:\"LIKE \'GD_%\'\"

将备份数据从db服务器拿到本地

sftp 10.0.26.126
输入密码
get /home/oracle/vat/tmsvat.dmp

光大证券-项目需求变更说明书20180718

文件:光大证券-项目需求变更说明书20180718.docx

  • GD_USER_TAX_INFO_N GD_USER_BASIC_INFO_N--> TMS_MD_CUSTOMER
  • GD_CUACCT_N -->TMS_MD_CUST_BANK_ACCOUNTS

配置备份

Shanghaiguangda1.jpg Shanghaiguangda2.jpg Shanghaiguangda3.jpg Shanghaiguangda4.jpg Shanghaiguangda5.jpg Shanghaiguangda6.jpg Shanghaiguangda7.jpg

进项发票服务接口V1.0

文件:进项发票服务接口V1.0.pdf

  • 发票查验接口1023
  • 发票查询接口1024
  • 发票认证接口1025
  • 认证结果查询1026
  • 抵账数据查询接口1029
  • 销项开票接口2021
  • 销项开票查询2022

用友接口

[‎26/‎09/‎2018 14:31] Wang, Xun (CN - Shanghai): 对了,光大那边,我们要给用友提供一个接口规范,能不能基于原来的一个什么out表,把他做为头表,扩展一下相应的字段,然后再编一个子表,推送发票行信息。简单在Excel列一列发给我和mindy?

网报接口

SuitDrvatInvoiceWithYYJob#execute
call gd_input_vat_trans.do_gd_all(?, ?)
TmsDrvatInTrxLCommonController
PurchinvoiceProcess
  • tmsDrvatInTrxLCommon.jsp
  • PurchinvoiceProcess
  • TmsDrvatInTrxLCommonController


  • deloitte.tms.vat.inf.citic.jar
  • deloitte.tms.vat.core.jar

SC_OUTTAX

rename SC_OUTTAX to SC_OUTTAX_barkup

create table SC_OUTTAX as select
                                           INVOICE_TRX_L_ID INVOCEID ,
                                           INVOICE_CODE as INVOCECODE	,--发票代码
                                           INVOICE_NUMBER as INVOCENUM		,--发票号码
                                           INVOICING_DATE as INVOCEDATE		,--开票日期
                                           entered_amount as TDEF2		,--金额(不含税)
                                           vat_amount as TDEF1		,--税额
                                           vender_name as VENDERNAME		,--销货单位名称
                                           ATTRIBUTE9 as INVOCESTATUS		,--匹配状态
                                           INVOICING_DATE as TIMESTAMP		,--时间戳
                                           PURCHASER_NAME as PURCHASERNAME		,--购方名称
                                           PURCHASER_TAX_NO as PURCHASERTAXNUM		,--购方税号
                                           REMARKS as INVOICECOMMENT		,--备注
                                           VENDER_REGISTRATION_NUMBER as SALESTAXNUM		,--销方税号
                                           INVOICESTATUS as INVOICESTATUS		,--发票取数接口发票状态
                                           WXUSERNAME as WXUSERNAME		,--公众号用户名
                                           DEDUCTION_PERIOD as DEDUCTION_PERIOD		,--认证期间
                                           INVOICETYPE as INVOICETYPE		--发票类型
                                    from TMS_DRVAT_INVOICE_TRX_L

ALTER TABLE SC_OUTTAX ADD IMPORT_FLAG varchar2(150) NULL;
ALTER TABLE SC_OUTTAX ADD FREE1 varchar2(150) NULL;
ALTER TABLE SC_OUTTAX ADD FREE2 varchar2(150) NULL;
ALTER TABLE SC_OUTTAX ADD FREE3 varchar2(150) NULL;
ALTER TABLE SC_OUTTAX ADD FREE4 varchar2(150) NULL;
ALTER TABLE SC_OUTTAX ADD FREE5 varchar2(150) NULL;

create or replace view SC_OUTTAX as select
                                           INVOICE_TRX_L_ID INVOCEID ,
                                           INVOICE_CODE as INVOCECODE	,--发票代码
                                           INVOICE_NUMBER as INVOCENUM		,--发票号码
                                           INVOICING_DATE as INVOCEDATE		,--开票日期
                                           entered_amount as TDEF2		,--金额(不含税)
                                           vat_amount as TDEF1		,--税额
                                           vender_name as VENDERNAME		,--销货单位名称
                                           ATTRIBUTE9 as INVOCESTATUS		,--匹配状态
                                           NULL as IMPORT_FLAG		,--读取标识
                                           NULL as FREE1		,--自由项1
                                           NULL as FREE2		,--自由项2
                                           NULL as FREE3		,--自由项3
                                           NULL as FREE4		,--自由项4
                                           NULL as FREE5		,--自由项5
                                           INVOICING_DATE as TIMESTAMP		,--时间戳
                                           PURCHASER_NAME as PURCHASERNAME		,--购方名称
                                           PURCHASER_TAX_NO as PURCHASERTAXNUM		,--购方税号
                                           REMARKS as INVOICECOMMENT		,--备注
                                           VENDER_REGISTRATION_NUMBER as SALESTAXNUM		,--销方税号
                                           INVOICESTATUS as INVOICESTATUS		,--发票取数接口发票状态
                                           WXUSERNAME as WXUSERNAME		,--公众号用户名
                                           DEDUCTION_PERIOD as DEDUCTION_PERIOD		,--认证期间
                                           INVOICETYPE as INVOICETYPE		--发票类型
from TMS_DRVAT_INVOICE_TRX_L

SC_OUTTAX_DETAIL

create or replace view SC_OUTTAX_DETAIL as select
                                                  d.INVOICE_TRX_L_ID as INVOCEID ,
                                                  d.INVOICE_TRX_D_ID as INVOCELINEID ,
l.INVOICE_CODE as INVOCECODE ,
l.INVOICE_NUMBER as INVOCENUM,
d.ROW_NO ROWNUMBER,
d.INVENTORY_ITEM_DESCRIPTON COMMODITYNAME ,
d.INVENTORY_ITEM_MODELS SPECIFICATIONMODEL  ,
d.INVENTORY_ITEM_QTY UNITNUMBER,
d.PRICE_OF_UNIT UNITPRICE,
d.VAT_AMOUNT TAXAMOUNT  ,
                                                  d.TAX_RATE TAXRATE  ,
d.ACCTD_AMOUNT_CR NETAMOUNT ,
null FREE1  ,
                                                  null FREE2  ,
                                                  null FREE3  ,
                                                  null FREE4  ,
                                                  null FREE5  ,
d.LAST_UPDATE_DATE TIMESTAMP  
                                    from TMS_DRVAT_INVOICE_TRX_D d,TMS_DRVAT_INVOICE_TRX_L l where l.INVOICE_TRX_L_ID=d.INVOICE_TRX_L_ID

gd_input_vat_trans

CREATE OR REPLACE PACKAGE BODY gd_input_vat_trans IS

  ----------------------------------------------------------------
  -- 统一调用入口
  ------------------------------------------------------------------
  PROCEDURE do_gd_all(p_status OUT INT, --0成功,负数失败
                      p_errmsg OUT VARCHAR2) IS
  
    v_status INT;
    v_errmsg VARCHAR2(500);
  
  BEGIN
  
    p_status := 0;
  
    --底账回传
    gd_input_vat_trans.do_gd_sc_inttax(v_status, v_errmsg);
    IF v_status != 0 THEN
      p_status := -1;
      p_errmsg := p_errmsg || v_errmsg;
    END IF;
  
    --日志
    INSERT INTO gd_tb_data_sync_h
      (data_sync_h_id,
       stg_trx_h_id,
       file_source,
       file_name,
       status,
       sync_date,
       occur_time,
       deleted_flag,
       distribution)
      SELECT sys_guid() AS data_sync_h_id,
             'SC_INTTAX' || to_char(SYSDATE, 'YYYYMMDD') AS stg_trx_h_id,
             '进项底账回传' AS file_source,
             'SC_OUTTAX' AS file_name,
             v_status AS status,
             to_date(to_char(SYSDATE, 'YYYYMMDD'), 'YYYYMMDD') AS sync_date,
             SYSDATE AS occur_time,
             '1' AS deleted_flag,
             v_errmsg AS distribution
        FROM dual;
    COMMIT;
  
    --底账推送
    gd_input_vat_trans.do_gd_sc_outtax(v_status, v_errmsg);
    IF v_status != 0 THEN
      p_status := -1;
      p_errmsg := p_errmsg || v_errmsg;
    END IF;
  
    --日志
    INSERT INTO gd_tb_data_sync_h
      (data_sync_h_id,
       stg_trx_h_id,
       file_source,
       file_name,
       status,
       sync_date,
       occur_time,
       deleted_flag,
       distribution)
      SELECT sys_guid() AS data_sync_h_id,
             'SC_OUTTAX' || to_char(SYSDATE, 'YYYYMMDD') AS stg_trx_h_id,
             '进项底账推送' AS file_source,
             'SC_OUTTAX' AS file_name,
             v_status AS status,
             to_date(to_char(SYSDATE, 'YYYYMMDD'), 'YYYYMMDD') AS sync_date,
             SYSDATE AS occur_time,
             '1' AS deleted_flag,
             v_errmsg AS distribution
        FROM dual;
    COMMIT;
  
    IF p_status = 0 THEN
      p_errmsg := '光大证券电子底账信息同步成功';
    END IF;
  
  EXCEPTION
  
    WHEN OTHERS THEN
      p_status := SQLCODE;
      p_errmsg := SQLERRM;
    
  END;

  ----------------------------------------------------------------
  -- 推送/回传分别处理
  ----------------------------------------------------------------

  -- 01 SC_INTTAX数据回传/匹配
  PROCEDURE do_gd_sc_inttax(p_status OUT INT, p_errmsg OUT VARCHAR2) IS
  
  BEGIN
  
    -------------------------------------------
    --读取SC_INTTAX的记录,存至D表
    -------------------------------------------
  
    EXECUTE IMMEDIATE 'TRUNCATE TABLE tms_drvat_invoice_trx_d_temp';
  
    --按TS插入发票明细
    INSERT INTO tms_drvat_invoice_trx_d_temp
      (invoice_trx_d_id,
       invoice_category,
       invoice_source,
       invoice_code,
       invoice_number,
       tax_rate,
       acctd_amount_cr,
       vat_amount,
       invoice_item_type,
       attribute1,
       attribute2,
       attribute3,
       attribute4)
      SELECT substr(t.invocelineid || '-0000000000000000000000000', 1, 36) AS invoice_trx_d_id,
             t.invocetype AS invoice_category,
             t.invocesource AS invoice_source,
             t.invocecode AS invoice_code,
             (CASE
               WHEN t.invocetype = '4' THEN
                t.free3
               ELSE
                t.invocenum
             END) AS invoice_number,
             t.taxrate AS tax_rate,
             t.tdef2 AS acctd_amount_cr,
             t.tdef1 AS vat_amount,
             t.tdef4 AS invoice_item_type,
             substr(t.free1, 1, length(t.free1) - 5) AS attribute1,
             to_char(to_timestamp(t.ts, 'yyyy-mm-dd,hh24:mi:ss'), 'YYYYMM') AS attribute2, --抵扣期间
             t.ts AS attribute3,
             t.free2 AS attribute4 --审批人
        FROM sc_inttax t
       WHERE substr(ts, 1, 7) = to_char(SYSDATE, 'YYYY-MM')
          OR substr(ts, 1, 7) = to_char(add_months(SYSDATE, -1), 'YYYY-MM');
  
    UPDATE tms_drvat_invoice_trx_d_temp
       SET expense_claim_form_num = 'YY',
           archive_base_date      = SYSDATE,
           biz_org_code           = '110101',
           created_by             = 'admin',
           creation_date          = SYSDATE,
           last_updated_by        = 'admin',
           last_update_date       = SYSDATE,
           record_version         = '1',
           deleted_flag           = '1',
           data_owner_code        = '110101',
           company_id             = 'INITCOMPANYID00000000000000000000000',
           operation_org_code     = '110101';
  
    --按发票代码、发票号码、时间戳分组,仅取时间戳最新的数据
    UPDATE tms_drvat_invoice_trx_d_temp
       SET attribute10 = '0', attribute9 = NULL;
  
    UPDATE tms_drvat_invoice_trx_d_temp t1
       SET t1.attribute9 =
           (SELECT t2.attribute3
              FROM (SELECT invoice_code,
                           invoice_number,
                           MAX(attribute3) AS attribute3,
                           invoice_code || invoice_number AS keyid
                      FROM tms_drvat_invoice_trx_d_temp t2
                     GROUP BY invoice_code, invoice_number) t2
             WHERE t2.keyid = t1.invoice_code || t1.invoice_number);
  
    --误差不超过10秒
    UPDATE tms_drvat_invoice_trx_d_temp
       SET attribute10 = '1'
     WHERE abs((to_date(attribute3, 'yyyy-mm-dd,hh24:mi:ss') -
               to_date(attribute9, 'yyyy-mm-dd,hh24:mi:ss')) * 86400) <= 10
       AND to_char(to_date(attribute3, 'yyyy-mm-dd,hh24:mi:ss'), 'YYYYMMDD') =
           to_char(to_date(attribute9, 'yyyy-mm-dd,hh24:mi:ss'), 'YYYYMMDD');
  
    --删除用友推送的发票明细
    DELETE FROM tms_drvat_invoice_trx_d
     WHERE (attribute2 = to_char(SYSDATE, 'YYYYMM') OR
           attribute2 = to_char(add_months(SYSDATE, -1), 'YYYYMM'))
       AND expense_claim_form_num = 'YY';
  
    --删除用友回传发票手工录入的发票明细(先手工匹配,后用友回传)
    UPDATE tms_drvat_invoice_trx_d
       SET deleted_flag  = '0',
           deleted_by    = 'SYSTEM',
           deletion_date = SYSDATE
     WHERE invoice_code || invoice_number IN
           (SELECT invoice_code || invoice_number
              FROM tms_drvat_invoice_trx_d_temp)
       AND expense_claim_form_num IS NULL
       AND deleted_flag = '1';
  
    --本月/上月用友推送的发票明细插入正式表
    INSERT INTO tms_drvat_invoice_trx_d
      (invoice_trx_d_id,
       invoice_trx_l_id,
       expense_claim_form_num,
       invoice_category,
       invoice_source,
       invoice_code,
       invoice_number,
       tax_rate,
       acctd_amount_cr,
       vat_amount,
       invoice_item_type,
       attribute1,
       attribute2,
       attribute3,
       attribute4,
       archive_base_date,
       biz_org_code,
       created_by,
       creation_date,
       last_updated_by,
       last_update_date,
       record_version,
       deleted_flag,
       data_owner_code,
       company_id,
       operation_org_code)
      SELECT invoice_trx_d_id,
             invoice_trx_l_id,
             expense_claim_form_num,
             invoice_category,
             invoice_source,
             invoice_code,
             invoice_number,
             tax_rate,
             acctd_amount_cr,
             vat_amount,
             invoice_item_type,
             attribute1,
             attribute2,
             attribute3,
             attribute4,
             archive_base_date,
             biz_org_code,
             created_by,
             creation_date,
             last_updated_by,
             last_update_date,
             record_version,
             deleted_flag,
             data_owner_code,
             company_id,
             operation_org_code
        FROM tms_drvat_invoice_trx_d_temp
       WHERE attribute10 = '1';
  
    COMMIT;
  
    -------------------------------------------
    --更新L表
    -------------------------------------------
  
    EXECUTE IMMEDIATE 'TRUNCATE TABLE tms_drvat_invoice_trx_d_temp';
  
    --按发票代码、发票号码、发票类型、发票来源汇总金额、税额
    INSERT INTO tms_drvat_invoice_trx_d_temp
      (invoice_trx_d_id,
       invoice_code,
       invoice_number,
       invoice_category,
       invoice_source,
       acctd_amount_cr,
       vat_amount,
       attribute1,
       attribute2,
       attribute4)
      SELECT substr(invoice_code || invoice_number || attribute2 ||
                    invoice_category || invoice_source || '-' || attribute1 ||
                    '-00000000000000000000000000000000000',
                    1,
                    36) AS invoice_trx_d_id,
             invoice_code,
             invoice_number,
             invoice_category,
             invoice_source,
             SUM(acctd_amount_cr) AS acctd_amount_cr,
             SUM(vat_amount) AS vat_amount,
             attribute1,
             attribute2,
             MAX(attribute4)
        FROM tms_drvat_invoice_trx_d
       WHERE (substr(attribute3, 1, 7) = to_char(SYSDATE, 'YYYY-MM') OR
             substr(attribute3, 1, 7) =
             to_char(add_months(SYSDATE, -1), 'YYYY-MM'))
       GROUP BY invoice_code,
                invoice_number,
                invoice_category,
                invoice_source,
                attribute1,
                attribute2;
  
    --L表存在的发票更新用友账套、用友金额、用友税额
    UPDATE tms_drvat_invoice_trx_l t1
       SET (t1.attribute1,
            t1.attribute2,
            t1.attribute3,
            t1.attribute6,
            t1.attribute8,
            t1.attribute10) =
           (SELECT t2.attribute1      AS attribute1,
                   t2.acctd_amount_cr AS attribute2,
                   t2.vat_amount      AS attribute3,
                   t2.attribute4      AS attribute6,
                   t2.invoice_source  AS attribute8,
                   t2.attribute2      AS attribute10
              FROM tms_drvat_invoice_trx_d_temp t2
             WHERE t1.invoice_code || t1.invoice_number =
                   t2.invoice_code || t2.invoice_number)
     WHERE t1.invoice_code || t1.invoice_number IN
           (SELECT invoice_code || invoice_number
              FROM tms_drvat_invoice_trx_d_temp);
  
    UPDATE tms_drvat_invoice_trx_l t1
       SET t1.attribute9 = '已匹配'
     WHERE t1.attribute8 = '1'
       AND t1.attribute10 IS NOT NULL;
  
    UPDATE tms_drvat_invoice_trx_l t1
       SET t1.attribute9 = '扫描认证'
     WHERE t1.attribute8 = '2'
       AND t1.attribute10 IS NOT NULL;
  
    --L表不存在的发票新增
    --假定同一发票发票类型、发票来源相同,如果有问题(用友控制不严格),后续再调整
  
    --插入L表
    INSERT INTO tms_drvat_invoice_trx_l t1
      (t1.invoice_trx_l_id,
       t1.invoice_code,
       t1.invoice_number,
       t1.entered_amount,
       t1.vat_amount,
       t1.status,
       t1.attribute1,
       t1.attribute2,
       t1.attribute3,
       t1.attribute6,
       t1.attribute8,
       t1.attribute9,
       t1.attribute10,
       t1.archive_base_date,
       t1.biz_org_code,
       t1.created_by,
       t1.creation_date,
       t1.last_updated_by,
       t1.last_update_date,
       t1.record_version,
       t1.deleted_flag,
       t1.data_owner_code,
       t1.company_id,
       t1.operation_org_code,
       t1.invoice_category)
      SELECT t2.invoice_trx_d_id AS invoice_trx_l_id,
             t2.invoice_code AS invoice_code,
             t2.invoice_number AS invoice_number,
             t2.acctd_amount_cr AS entered_amount,
             t2.vat_amount AS vat_amount,
             '正常' AS status,
             t2.attribute1 AS attribute1,
             t2.acctd_amount_cr AS attribute2,
             t2.vat_amount AS attribute3,
             t2.attribute4 AS attribute6,
             invoice_source AS attribute8,
             '扫描认证' AS attribute9,
             t2.attribute2 AS attribute10,
             SYSDATE AS archive_base_date,
             '110101' AS biz_org_code,
             'SYSTEM' AS created_by,
             SYSDATE AS creation_date,
             'SYSTEM' AS last_updated_by,
             SYSDATE AS last_update_date,
             '1' AS record_version,
             '1' AS deleted_flag,
             '110101' AS data_owner_code,
             'INITCOMPANYID00000000000000000000000' AS company_id,
             '110101' AS operation_org_code,
             t2.invoice_category
        FROM tms_drvat_invoice_trx_d_temp t2
       WHERE t2.invoice_code || t2.invoice_number NOT IN
             (SELECT invoice_code || invoice_number
                FROM tms_drvat_invoice_trx_l);
  
    --最后更新人
    UPDATE tms_drvat_invoice_trx_l t1
       SET t1.last_updated_by =
           (SELECT t2.username
              FROM base_user t2
             WHERE t1.attribute6 = t2.cname
               AND t2.deleted_flag = '1'
               AND t2.enabled = '1'
               AND rownum = 1)
     WHERE t1.attribute6 IS NOT NULL
       AND (SELECT t2.username
              FROM base_user t2
             WHERE t1.attribute6 = t2.cname
               AND t2.deleted_flag = '1'
               AND t2.enabled = '1'
               AND rownum = 1) IS NOT NULL;
  
    --更新LEGAL_ENTITY_ID、组织名称、比较金额、税额
    UPDATE tms_drvat_invoice_trx_l t1
       SET t1.legal_entity_id =
           (SELECT t2.legal_entity_id
              FROM tms_md_legal_entity t2
             WHERE legal_entity_code = t1.attribute1
               AND t2.deleted_flag = '1'
               AND t2.enabled_flag = '0')
     WHERE t1.legal_entity_id IS NULL;
  
    UPDATE tms_drvat_invoice_trx_l t1
       SET t1.attribute5 =
           (SELECT t2.org_name
              FROM base_org t2
             WHERE org_code = t1.attribute1
               AND t2.deleted_flag = '1')
     WHERE t1.attribute5 IS NULL;
  
    --更新D表的发票L ID
    UPDATE tms_drvat_invoice_trx_d t1
       SET t1.invoice_trx_l_id =
           (SELECT t2.invoice_trx_l_id
              FROM tms_drvat_invoice_trx_l t2
             WHERE t1.invoice_code || t1.invoice_number =
                   t2.invoice_code || t2.invoice_number
               AND t2.deleted_flag = '1')
     WHERE t1.expense_claim_form_num = 'YY'
       AND t1.invoice_trx_l_id IS NULL;
  
    UPDATE tms_drvat_invoice_trx_l SET attribute4 = NULL;
  
    UPDATE tms_drvat_invoice_trx_l t1
       SET attribute4 = attribute4 || '纳税主体' || attribute1 || '未配置;'
     WHERE legal_entity_id IS NULL;
  
    UPDATE tms_drvat_invoice_trx_l t1
       SET attribute4 = attribute4 || '组织' || attribute1 || '未配置;'
     WHERE attribute5 IS NULL;
  
    UPDATE tms_drvat_invoice_trx_l t1
       SET t1.attribute4 = t1.attribute4 || '用友ORG:' || t1.attribute1 || ';'
     WHERE t1.attribute1 != (SELECT t2.legal_entity_code
                               FROM tms_md_legal_entity t2
                              WHERE t1.legal_entity_id = t2.legal_entity_id
                                AND t2.deleted_flag = '1'
                                AND t2.enabled_flag = '0');
  
    UPDATE tms_drvat_invoice_trx_l t1
       SET t1.attribute4 = t1.attribute4 || '用友金额:' || t1.attribute2 || ';'
     WHERE t1.attribute2 != t1.entered_amount;
  
    UPDATE tms_drvat_invoice_trx_l t1
       SET t1.attribute4 = t1.attribute4 || '用友税额:' || t1.attribute3 || ';'
     WHERE t1.attribute3 != t1.vat_amount;
  
    UPDATE tms_drvat_invoice_trx_l t1
       SET t1.attribute4 = t1.attribute4 || '发票类别为空;'
     WHERE EXISTS (SELECT t2.invoice_item_type
              FROM tms_drvat_invoice_trx_d t2
             WHERE t2.invoice_trx_l_id = t1.invoice_trx_l_id
               AND t2.expense_claim_form_num = 'YY'
               AND t2.deleted_flag = '1'
               AND (t2.invoice_item_type IS NULL OR
                   TRIM(t2.invoice_item_type) = ''));
  
    COMMIT;
  
    p_status := SQLCODE;
    p_errmsg := SQLERRM;
  
  EXCEPTION
    WHEN OTHERS THEN
      p_status := SQLCODE;
      p_errmsg := SQLERRM;
      ROLLBACK;
    
  END;

  -- 02 SC_OUTTAX数据推送
  PROCEDURE do_gd_sc_outtax(p_status OUT INT, --0成功,负数失败
                            p_errmsg OUT VARCHAR2) IS
  
  BEGIN
  
    --向SC_OUTTAX中插入新增发票
    INSERT INTO sc_outtax
      (invoceid,
       invocecode,
       invocenum,
       invocedate,
       tdef2,
       tdef1,
       vendername)
      SELECT substr(t1.invoice_code || t1.invoice_number ||
                    '000000000000000000000000000000000000',
                    1,
                    20),
             t1.invoice_code,
             t1.invoice_number,
             to_timestamp(to_char(t1.invoicing_date, 'YYYY-MM-DD'),
                          'YYYY-MM-DD'),
             t1.entered_amount,
             t1.vat_amount,
             t1.vender_name
        FROM tms_drvat_invoice_trx_l t1
       WHERE t1.status = '正常'
         AND t1.attribute9 = '未匹配'
         AND t1.deleted_flag = '1'
         AND t1.invoice_code || t1.invoice_number NOT IN
             (SELECT invocecode || invocenum FROM sc_outtax)
         AND (to_char(t1.creation_date, 'YYYYMM') =
             to_char(SYSDATE, 'YYYYMM') OR
             to_char(t1.creation_date, 'YYYYMM') =
             to_char(add_months(SYSDATE, -1), 'YYYYMM'));
  
    --更新FREE1字段(如果用户导入底账时选错组织,批量删除后重新导入会导致组织不一致)
    UPDATE sc_outtax t1
       SET t1.free1 =
           (SELECT t2.legal_entity_code || '-0002'
              FROM tms_md_legal_entity t2, tms_drvat_invoice_trx_l t3
             WHERE t2.legal_entity_id = t3.legal_entity_id
               AND t3.deleted_flag = '1'
               AND t1.invocecode || t1.invocenum =
                   t3.invoice_code || t3.invoice_number
               AND t2.deleted_flag = '1'
               AND t2.enabled_flag = '0');
  
    COMMIT;
  
    p_status := SQLCODE;
    p_errmsg := SQLERRM;
  
  EXCEPTION
    WHEN OTHERS THEN
      p_status := SQLCODE;
      p_errmsg := SQLERRM;
      ROLLBACK;
    
  END;

END gd_input_vat_trans;