上海光大项目
来自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
系统账号密码:admin/xts9752
内网环境
tomcat服务器 Version 10.173.20.15 9090 C:\server\tomcat_gd oracl数据库 10.173.20.15 1521 orcl TMSVATGD/TMSVATGD
网络拓扑
运维信息
- 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
配置备份
进项发票服务接口V1.0
- 发票查验接口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;