EAS税务数据修复和常用SQL合辑

栏目:eas cloud知识作者:金蝶来源:金蝶云社区发布:2024-09-22浏览:1

EAS税务数据修复和常用SQL合辑

注意:涉及数据库操作请慎重,一定要备份。

--表名  需替换为具体的表名

--补丁号 需替换为具体的补丁号


1、备份数据表: 

select * into 表名_backup  from 表名; 


2、删除sql脚本执行记录(主要用于研发内部重复打补丁): 

delete FROM T_SYS_PatchSQLScri where FSQLFILENAME like '%补丁号%';


3、查询索引: 

select t.*,i.index_type from user_ind_columns t,user_indexes i where t.index_name = i.index_name and t.table_name = i.table_name and t.table_name = '表名'; 


4、查找约束:

select * from user_constraints t where  t.table_name = '表名';


5、查询数据库字段: 

SELECT TB1.COLUMN_NAME, TB1.DATA_TYPE, TB1.DATA_LENGTH, TB1.DATA_PRECISION, TB1.DATA_SCALE, TB1.NULLABLE, TB1.DATA_DEFAULT, TB2.COMMENTS FROM USER_TAB_COLUMNS TB1, USER_COL_COMMENTS TB2 WHERE TB1.TABLE_NAME = TB2.TABLE_NAME AND TB1.COLUMN_NAME = TB2.COLUMN_NAME AND TB1.TABLE_NAME = '表名';


6、删除重复数据(保留最早的): 

DELETE FROM 表名 WHERE FID NOT IN (SELECT MIN(FCreateTime) FROM 表名 GROUP BY FNUMBER); 


7、创建唯一索引:

alter table 表名 add CONSTRAINT PK_NUMBER_CODE UNIQUE (FNumber); 


8、修复应收单(实际无下游开票单场景)不能下推开票单(替换应收单单头ID为对应的ID):

update T_AR_OtherBill set FIsInvoiced=0,FISMAKEINVOICED =0,FINVOICENO=null, FInvoiceCode=null,FInvoiceDate=null,FInvoicedAmt=null where  fid ='应收单单头ID';


9、排查应收单开票后没有发票代码、号码:

select count(*) from  t_ar_otherbill where FisInvoiced=1 and FINVOICENO is null;


10、修复应收单一键开票后没有发票代码、发票号码:

update t_ar_otherbill t set (FINVOICECODE,FINVOICENO)= (SELECT listagg(im.FINVOICENUMBER ,';') within group (order by im.FINVOICENUMBER) as A,listagg(im.FINVOICENO ,';') within group (order by im.FINVOICENO) as B   FROM t_ar_otherbill src INNER JOIN T_BOT_Relation relation on src.fid = relation.FSRCOBJECTID inner join t_im_makeInvoice im on im.fid=relation.FDESTOBJECTID where src.FID=t.FID group by src.fid) where t.FINVOICENO is null and t.FisInvoiced=1

PS:只需替换XXX为应收单单头id即可,支持多个。在查询分析器执行方言(只限ORACLE数据库,尾部不要加分号--不然会报无效字符)


11、修复应收单到开票申请单到开票后没有发票代码、号码:

第一步:先反写开票申请单的发票代码、号码(如果没有问题请忽略

update t_im_makeInvoiceReq t set (FINVOICECODE,FINVOICENO)= (SELECT listagg(im.FINVOICENUMBER,';') within group (order by im.FINVOICENUMBER) as A,listagg(im.FINVOICENO ,';') within group (order by im.FINVOICENO) as B   FROM t_im_makeInvoiceReq src inner join t_im_makeInvoice im on im.FSOURCEBILLID =src.fid where src.FID=t.FID  group by src.fid) where t.FINVOICENO is null and  t.FisInvoiced=1


第二步:通过开票申请单反写应收单的发票代码、号码

update t_ar_otherbill t set (FINVOICECODE,FINVOICENO)= (SELECT listagg(imq.FINVOICECode,';') within group (order by imq.FINVOICECode) as A,listagg(imq.FINVOICENO ,';') within group (order by imq.FINVOICENO) as B   FROM t_ar_otherbill src inner join t_im_makeInvoiceReq imq on imq.FSOURCEBILLID =src.fid where src.FID=t.FID  group by src.fid) where t.FINVOICENO is null and  t.FisInvoiced=1

PS:只需替换XXX为应收单单头id即可,支持多个。在查询分析器执行方言(只限ORACLE数据库,尾部不要加分号--不然会报无效字符)


12、应付单打开发票助手方式:

insert into t_dt_dttempparam(FKEY, FVALUE, FDESCRIPTION) values (N'openFpzsWithBrowser', 1, N'是否在浏览器打开发票助手参数');

PS:值为1时代表在WEB浏览器打开页签,非1时代表在GUI嵌套浏览器打开弹窗


13、软证书批量勾选的轮询次数:

update T_DT_DTTempParam set FVALUE=1 where FKEY='CHECK_INVOICE_NUM';  修改轮询次数


14、查找开票单上游的单据:

select * from t_bot_relation where FSRCENTITYID ='412A6F98' and FSRCOBJECTID ='';

PS:最后这个单引号放开票单id


15、应收单开票后,发现开错,但对方已认证,下载购方红字发票后,无法指定应收单(2021年后才支持),从而修复数据:

1、备份应收单

 select * into T_AR_OtherBill_backup0325  from T_AR_OtherBill; 

 2、建立红字开票单与应收单的botp关系

 a、先查找新增botp的id ,替换b中的BOTPID:

select newBOSid('59302EC6') 

 b、替换对应的数据 

 insert into t_bot_relation(FID, FSRCENTITYID, FDESTENTITYID, FSRCOBJECTID, FDESTOBJECTID, FDATE, FOPERATORID, FISEFFECTED, FBOTMAPPINGID, FTYPE) values (N'BOTPID', N'FC910EF3', N'412A6F98', '应收单id', N'红字开票单id', {ts'2021-12-17 11:15:16'}, N'MANUAL', 1, null, 0); 

 3、反写应收单 

update T_AR_OtherBill set FIsInvoiced=0,FINVOICENO='拼接的发票号码', FInvoiceCode='拼接的发票代码',FInvoicedAmt=null where  fid ='应收单单头ID';


16、字段扩容:

IF EXISTS(SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = '表名' AND KSQL_COL_NAME = '旧字段名')

alter table 表名 add 旧字段名_bak NVARCHAR(255);

if exists (select 1 from KSQL_USERCOLUMNS where KSQL_COL_TABNAME = '表名' and KSQL_COL_Name = '旧字段名_bak')

update 表名 set 旧字段名_bak = 旧字段名;

if exists (select 1 from KSQL_USERCOLUMNS where KSQL_COL_TABNAME = '表名' and KSQL_COL_Name = '旧字段名_bak')

alter table 表名 drop default for 旧字段名;

if exists (select 1 from KSQL_USERCOLUMNS where KSQL_COL_TABNAME = '表名' and KSQL_COL_Name = '旧字段名_bak')

update 表名 set 旧字段名=null;

if exists (select 1 from KSQL_USERCOLUMNS where KSQL_COL_TABNAME = '表名' and KSQL_COL_Name = '旧字段名_bak')

alter table 表名 alter column 旧字段名 NVARCHAR(255);

if exists (select 1 from KSQL_USERCOLUMNS where KSQL_COL_TABNAME = '表名' and KSQL_COL_Name = '旧字段名_bak')

update 表名 set 旧字段名 = 旧字段名_bak;

if exists (select * from KSQL_USERCOLUMNS where KSQL_COL_TABNAME = '表名' and KSQL_COL_Name = '旧字段名_bak')

alter table 表名 drop column 旧字段名_bak;


17、开票单的公司和上游开票申请单的不一致,需数据修复(以创建时间为过滤条件,可自行添加)

update t_im_makeInvoice t set (FCOMPANYID) = ( select r.fcompanyId from t_im_makeInvoice i  inner join t_bot_relation b  on i.fid=b.fdestobjectid    inner join t_im_makeInvoiceReq r  on  r.fid=b.FSRCOBJECTID where i.FCREATETIME >{ts'2023-04-09'} ) where exists (select 1 from t_im_makeInvoice i  inner join t_bot_relation b  on i.fid=b.fdestobjectid    inner join t_im_makeInvoiceReq r  on  r.fid=b.FSRCOBJECTID where i.FCREATETIME >{ts'2023-04-09'} )


18、查询费用报销单关联的飞机票、火车票,但在税务全票池没有

select distinct fnumber from T_BC_BizAccountBill bab inner join T_BC_TicketEntry te on bab.fid= te.FBILLID where  te.FTICKETTYPEID in ('AZnYahvdQJiFBhYtbwAzsKYyxOk=','9ds9DhRxQ0iW5sgkZOCewKYyxOk=')  and  bab.fid not in (SELECT FSrcObjectID FROM T_BOT_Relation where FDESTENTITYID in ('D8100831','B1D944EA') and FSRCENTITYID ='4A44F49F');


19、待补充。。。

EAS税务数据修复和常用SQL合辑

注意:涉及数据库操作请慎重,一定要备份。--表名 需替换为具体的表名--补丁号 需替换为具体的补丁号1、备份数据表: select * into 表...
点击下载文档
确认删除?
回到顶部
客服QQ
  • 客服QQ点击这里给我发消息