总账系统发生的固定资产跨机构调拨等公司间业务,会引起机构的备付金变动,需要进行机构间资金清算。
大总账系统需要将每日涉及机构间清算的数据定时发往核心系统,并将最后一批数据打上最后一批的标识。
核心系统需将每批机构间清算数据的记账状态返回大总账系统。对于核心系统已成功记账的数据,大总账系统将更新对应导出标识,后续批量中将不包含此部分数据;对于核心系统中未成功记账的数据,大总账系统将在下一批量中继续导出。
开发列表
•DEV019_机构间清算数据导入核心系统
CUX:机构间清算数据导入核心系统
本节用于描述有关实现DEV-0199《机构间清算数据导入核心系统》的相关设计细节。
主要表结构
cux_gl_qs_details传输明细表
字段名 | 类型 | 是否为空 | 描述 |
GROUP_ID | NUMBER | Y | 批次id |
JE_HEADER_ID | NUMBER | Y | 凭证头id |
JE_LINE_NUM | NUMBER | Y | 凭证行号 |
CURRENCY_CODE | VARCHAR2(3) | Y | 币种 |
AMOUNT | NUMBER | Y | 贷方金额 |
SEGMENT1 | VARCHAR2(20) | Y |
|
SEGMENT3 | VARCHAR2(20) | Y |
|
SEGMENT7 | VARCHAR2(20) | Y |
|
AMOUNT_BEQ | NUMBER | Y | 本位币借方金额 |
DR_CR_FLAG | VARCHAR2(1) | Y | 借贷标志 0-借,1-贷,借贷相反 |
ERRPR_FLAG | VARCHAR2(1) | Y | 传输错误标志 |
BUSINESS_CODE | VARCHAR2(4) | Y | 业务代号 |
ACCOUNT_SEQUENCE | VARCHAR2(5) | Y | 账户序号 |
TRADE_CODE | VARCHAR2(10) | Y | 摘要代码 |
TRADE_CHANNEL | VARCHAR2(3) | Y | 交易渠道 |
TRADE_USER | VARCHAR2(10) | Y | 交易柜员 |
TRANSFER_AMOUNT | NUMBER | Y | 借贷相反,用DR_CR_FLAG控制 |
VOUCHER_ID | NUMBER | Y | 传票组号 |
VOUCHER_NUM | NUMBER | Y | 组内序号 |
cux_qs_voucher_detail凭证明细
字段名 | 类型 | 是否为空 | 描述 |
INTERFACE_ID | NUMBER | Y |
|
SCHEDULE_ID | NUMBER | Y |
|
UNIQUE_ID | NUMBER | Y |
|
JE_HEADER_ID | NUMBER | Y |
|
JE_LINE_NUM | NUMBER | Y |
|
SEGMENT1 | VARCHAR2(200) | Y |
|
SEGMENT2 | VARCHAR2(200) | Y |
|
SEGMENT3 | VARCHAR2(200) | Y |
|
SEGMENT4 | VARCHAR2(200) | Y |
|
SEGMENT5 | VARCHAR2(200) | Y |
|
SEGMENT6 | VARCHAR2(200) | Y |
|
SEGMENT7 | VARCHAR2(200) | Y |
|
SEGMENT8 | VARCHAR2(200) | Y |
|
SEGMENT9 | VARCHAR2(200) | Y |
|
CODE_COMBINATION_ID | NUMBER | Y |
|
PROCESS_STATUS | VARCHAR2(1) | Y |
|
ERROR_MESSAGE | VARCHAR2(4000) | Y |
|
REQUEST_ID | NUMBER | Y |
|
CREATED_BY | NUMBER |
|
|
CREATION_DATE | DATE |
|
|
LAST_UPDATED_BY | NUMBER |
|
|
LAST_UPDATE_DATE | DATE |
|
|
LAST_UPDATE_LOGIN | NUMBER | Y |
|
ATTRIBUTE1 | VARCHAR2(240) | Y |
|
ATTRIBUTE2 | VARCHAR2(240) | Y |
|
ATTRIBUTE3 | VARCHAR2(240) | Y |
|
ATTRIBUTE4 | VARCHAR2(240) | Y |
|
ATTRIBUTE5 | VARCHAR2(240) | Y |
|
ATTRIBUTE6 | VARCHAR2(240) | Y |
|
ATTRIBUTE7 | VARCHAR2(240) | Y |
|
ATTRIBUTE8 | VARCHAR2(240) | Y |
|
ATTRIBUTE9 | VARCHAR2(240) | Y |
|
ATTRIBUTE10 | VARCHAR2(240) | Y |
|
GROUP_ID | NUMBER | Y |
|
DR_AMOUNT | NUMBER | Y |
|
CR_AMOUNT | NUMBER | Y |
|
cux_gl_qs_summary余额汇总
字段名 | 类型 | 是否为空 | 描述 |
LAST_UPDATE_DATE | DATE |
|
|
LAST_UPDATED_BY | NUMBER(15) |
|
|
CREATION_DATE | DATE |
|
|
CREATED_BY | NUMBER(15) |
|
|
LAST_UPDATE_LOGIN | NUMBER(15) | Y |
|
SEGMENT1 | VARCHAR2(20) |
|
|
SEGMENT3 | VARCHAR2(20) |
|
|
SEGMENT7 | VARCHAR2(20) |
|
|
TOTAL_AMOUNT | NUMBER | Y | 金额 |
TRANSFER_AMOUNT | NUMBER | Y | 传输金额 |
BALANCE | NUMBER | Y | 余额 |
主要逻辑
当每次运行请求的时候cux_to_cbs_07_pkg.get_voucher_detail
去导入凭证明细。再将导入的凭证明细进行轧差根据segment1, segment3, segment7汇总更新到cux_gl_qs_summary中,汇总之后是生成传输明细。
传输明细生成规则,去汇总表中查出segment1= segment7,segment7= segment1这种成对存在的记录然后取其中banlance异号的记录,取出这对记录中banlance最小的。生成传输明细表之后更新对cux_gl_qs_summary
将Total_amount,balance更新。
流程图
流程解释:
2、获取凭证明细:如果请求传入科目则用传入的科目,如果不传则取“CUX_AGENT_CLEAR_ACCOUNT”中科目。
然后凭证科目为指定科目,币种为CNY,凭证状态为已过帐,往来段不为“0”。
FOR cur_clear IN (SELECT *
FROM gl_je_headers gjh, gl_je_lines gjl, gl_code_combinations gcc
WHERE gjh.posted_date > SYSDATE - 90
AND gjh.je_header_id = gjl.je_header_id
AND gjh.currency_code = 'CNY'
AND gjh.status = 'P'
--todo and gjh.ledger_id=
AND gjl.code_combination_id = gcc.code_combination_id
AND gcc.segment3 = cur_account.lookup_code
AND gcc.segment7 <> '0'
AND NOT EXISTS (SELECT 1
FROM cux_qs_voucher_detail ctc
WHERE ctc.je_header_id = gjl.je_header_id
AND ctc.je_line_num = gjl.je_line_num)) LOOP
3.1、更新汇总表(cux_gl_qs_summary):先根据vd.segment1, vd.segment3, vd.segment7唯一性将不 存在汇总表中的插入,segment3是指定的科目。然后更新 UPDATE cux_gl_qs_summary gs
SET last_update_date = SYSDATE,
last_updated_by = g_user_id,
last_update_login = g_login_id,
total_amount = total_amount + c_data.s_dr_amount - c_data.s_cr_amount,
balance = balance + c_data.s_dr_amount - c_data.s_cr_amount
WHERE gs.segment1 = c_data.segment1
AND gs.segment3 = c_data.segment3
AND gs.segment7 = c_data.segment7;
3.2、生成传输明细:选择汇总表中segment1 ,segment7成对存在,并且余额是异号的的两条记录
FOR c_data1 IN (SELECT * FROM cux_gl_qs_summary WHERE balance <> 0) LOOP
FOR c_data2 IN (SELECT *
FROM cux_gl_qs_summary gs
WHERE gs.segment1 = c_data1.segment7
AND gs.segment7 = c_data1.segment1
AND c_data1.balance * balance < 0) LOOP
……
END LOOP;
END LOOP;
3.3、更新借贷标志:amount为负的借贷标志为‘0’,amount为正的借贷标志为‘1’,然后将transfer_amount取 amount取绝对值
3.4、校验传输明细中的科目所对应的业务代号和账户序号是否存在:根据segment3,科目去取业务代号和账户序号,如果不存 在或存在一对多的就打印错误报表。如果验证通过就更新cux_gl_qs_details表中的业务代号和账户序号。
3.8、更新 传票组号,组内序号:传票组号的规则是同一个segment用同一个传票组号,同一个传票组号用不同的组内序号。
传输字段排列说明
序号 | 字段名称 | 字段说明 | 类型 | 是否必输 |
|
|
|
|
|
1 | 传票组号 | 用来标识一组传票,例:1,2,3顺序使用 | NUMBER(10,0) | 是 |
2 | 组内序号 | 行序号,自动流水码 | NUMBER(10,0) | 是 |
3 | 机构代码 | 凭证行账户的平衡段值 | VARCHAR2(4) |
|
4 | 业务代号 | 依据凭证行账户的自然账户段值取值 | VARCHAR2(5) |
|
5 | 账户序号 | 依据业务代号进行取值 | VARCHAR2(5) |
|
6 | 币种 | 依据币种代码转换 | VARCHAR2(2) |
|
7 | 借贷标志 | 0 -借 1 -贷 | VARCHAR2(32) | 是 |
8 | 交易金额 | 按照机构,业务代号,账户序号,币种,借贷方向汇总输入金 额 | NUMBER(17,2) | 是 |
9 | 摘要代码 | 留空 | VARCHAR2(30) |
|
10 | 交易渠道 | 默认05,代表ERP财务系统 | VARCHAR2(3) |
|
11 | 交易柜员 | 由核心分配的虚拟柜员 | VARCHAR2(6) | 暂定为FD_hexin |
1)传票组号:在同一个批量文件中按照机构汇成汇总凭证,不同机构的凭证区分为不同传票,自动从1开始流水。
2)组内序号:在同一组传票下,自动从1开始流水,同一传票下按照业务代号升序排列,同一业务代号按照账户序号升序排列,同一账户序号下按照借贷标志升序排列
3)业务代号:依据科目取值自客户化form“核心系统业务代号维护”
4)账户序号:依据业务代号取自客户化form“核心系统内部账户序号列表”
主要SQL
凭证明细插入
SELECT gjl.je_header_id,
gjl.je_line_num,
gjl.code_combination_id,
gcc.segment1,
gcc.segment2,
gcc.segment3,
gcc.segment4,
gcc.segment5,
gcc.segment6,
gcc.segment7,
gcc.segment8,
gcc.segment9,
nvl(gjl.entered_dr, 0)- nvl(gjl.entered_cr, 0) amount,
decode(sign(nvl(gjl.entered_dr, 0) - nvl(gjl.entered_cr, 0)), -1, '1','0') drcr,
gjl.entered_dr,
gjl.entered_cr
FROM gl_je_headers gjh,gl_je_lines gjl, gl_code_combinations gcc
WHERE gjh.posted_date >SYSDATE - 90
AND gjh.je_header_id =gjl.je_header_id
AND gjh.currency_code ='CNY'
AND gjh.status = 'P'
--todo and gjh.ledger_id=
AND gjl.code_combination_id= gcc.code_combination_id
AND gcc.segment3 =cur_account.lookup_code
AND gcc.segment7 <>'0'
AND NOT EXISTS (SELECT 1
FROMcux_qs_voucher_detail ctc
WHEREctc.je_header_id = gjl.je_header_id
AND ctc.je_line_num =gjl.je_line_num);
汇总
--更新summary主键
FOR c_data IN (SELECT vd.segment1, vd.segment3, vd.segment7
FROMcux_qs_voucher_detail vd
WHEREvd.group_id = g_group_id
AND NOTEXISTS (SELECT 1
FROMcux_gl_qs_summary gs
WHEREgs.segment1 = vd.segment1
ANDgs.segment3 = vd.segment3
ANDgs.segment7 = vd.segment7)
GROUP BYvd.segment1, vd.segment3, vd.segment7) LOOP
INSERT INTOcux_gl_qs_summary
(last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
segment1,
segment3,
segment7,
total_amount,
transfer_amount,
balance)
VALUES
(SYSDATE,
g_user_id,
SYSDATE,
g_user_id,
g_login_id,
c_data.segment1,
c_data.segment3,
c_data.segment7,
0,
0,
0);
END LOOP;
--更新
FOR c_data IN (SELECT SUM(vd.dr_amount) s_dr_amount,
SUM(vd.cr_amount) s_cr_amount,
vd.segment1,
vd.segment3,
vd.segment7
FROMcux_qs_voucher_detail vd
WHEREvd.group_id = g_group_id
GROUP BYvd.segment1, vd.segment3, vd.segment7) LOOP
cux_conc_utl.log_msg('更新之前:' || (c_data.s_dr_amount -c_data.s_cr_amount));
UPDATE cux_gl_qs_summary gs
SET last_update_date = SYSDATE,
last_updated_by = g_user_id,
last_update_login =g_login_id,
total_amount = total_amount + c_data.s_dr_amount -c_data.s_cr_amount,
balance = balance + c_data.s_dr_amount -c_data.s_cr_amount
WHERE gs.segment1 =c_data.segment1
AND gs.segment3 =c_data.segment3
AND gs.segment7 =c_data.segment7;
--cux_conc_utl.log_msg('更新之后:' || total_amount);
-- END IF;
END LOOP;
更新传输明细
FOR c_data1 IN (SELECT * FROM cux_gl_qs_summary WHERE balance<> 0) LOOP
FOR c_data2 IN (SELECT *
FROMcux_gl_qs_summary gs
WHEREgs.segment1 = c_data1.segment7
ANDgs.segment7 = c_data1.segment1
ANDc_data1.balance * balance < 0) LOOP
--异号
IF c_data1.balance *c_data2.balance < 0 THEN
NULL;
ELSE
continue;
END IF;
IF abs(c_data1.balance)> abs(c_data2.balance) THEN
l_tf_amount :=c_data2.balance;
ELSE
l_tf_amount :=c_data1.balance;
END IF;
IF c_data2.total_amount> 0 THEN
l_tf_amount := abs(l_tf_amount)* 1;
ELSE
l_tf_amount :=abs(l_tf_amount) * (-1);
END IF;
INSERT INTOcux_gl_qs_details
(group_id,
-- je_header_id,
--je_line_num,
currency_code,
amount,
segment1,
segment3,
segment7,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login)
VALUES
(g_group_id,
-- c_data2.je_header_id,
-- c_data2,
'CNY',
l_tf_amount,
c_data2.segment1,
c_data2.segment3,
c_data2.segment7,
SYSDATE,
g_user_id,
SYSDATE,
g_user_id,
g_login_id);
END LOOP;
END LOOP;
cux_conc_utl.log_msg('更新借贷标志');
--更新借贷标志
UPDATE cux_gl_qs_details
SET dr_cr_flag = '1',transfer_amount = abs(amount)
WHERE amount > 0
AND group_id =g_group_id;
UPDATE cux_gl_qs_details
SET dr_cr_flag = '0',transfer_amount = abs(amount)
WHERE amount < 0
AND group_id =g_group_id;
cux_conc_utl.log_msg('更新账户序号');
--更新账户序号,
--初始化数组设置account_code
l_index := 1;
FOR c_data IN (SELECTDISTINCT segment3 FROM cux_gl_qs_details gs WHERE gs.group_id = g_group_id)LOOP
l_account(l_index).account_code :=c_data.segment3;
l_index := l_index + 1;
END LOOP;
生成传票组号和组内序号
--更新传票组号,组内序号
FOR c_data IN (SELECTsegment1, segment3, segment7
FROMcux_gl_qs_details
WHEREgroup_id = g_group_id
GROUP BYsegment1, segment3, segment7) LOOP
SELECTcux_gl_core_voucher_id_s.nextval INTO l_voucher_id FROM dual;
cux_conc_utl.log_msg('l_voucher_id=' || l_voucher_id);
UPDATE cux_gl_qs_details
SET voucher_id =l_voucher_id, voucher_num = 1
WHERE segment1 =c_data.segment1
AND segment3 =c_data.segment3
AND segment7 =c_data.segment7
AND group_id =g_group_id;
UPDATE cux_gl_qs_details
SET voucher_id = l_voucher_id, voucher_num= 2
WHERE segment7 =c_data.segment1
AND segment3 =c_data.segment3
AND segment1 =c_data.segment7
AND group_id =g_group_id;
END LOOP;
参数
p_transfer_date
p_account