机构间清算导入至核心

最新推荐文章于 2022-02-21 18:39:05 发布

原创最新推荐文章于 2022-02-21 18:39:05 发布 · 1.1k 阅读

0 ·

CC 4.0 BY-SA版权

EBS-PLSQL 专栏收录该内容

24 篇文章

订阅专栏

总账系统发生的固定资产跨机构调拨等公司间业务，会引起机构的备付金变动，需要进行机构间资金清算。

大总账系统需要将每日涉及机构间清算的数据定时发往核心系统，并将最后一批数据打上最后一批的标识。

核心系统需将每批机构间清算数据的记账状态返回大总账系统。对于核心系统已成功记账的数据，大总账系统将更新对应导出标识，后续批量中将不包含此部分数据；对于核心系统中未成功记账的数据，大总账系统将在下一批量中继续导出。

开发列表

•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);

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;

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