机构间清算导入至核心



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

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

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

 

 

开发列表

•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