Giới thiệu dự án

Trong bối cảnh nền kinh tế thị trường phát triển nhanh chóng, hoạt động cấp tín dụng của hệ thống Ngân hàng Thương mại (NHTM) tại Việt Nam gia tăng mạnh mẽ cả về quy mô lẫn độ phức tạp. Song song với sự tăng trưởng tín dụng, rủi ro nợ quá hạn và nợ xấu (Non-Performing Loans - NPL) luôn là thách thức lớn nhất đe dọa sự an toàn thanh khoản và hệ số an toàn vốn (CAR) của các tổ chức tài chính. Theo số liệu ngành ngân hàng, tỷ lệ nợ xấu nội bảng và các khoản nợ tiềm ẩn nếu không được giám sát theo chu kỳ hàng ngày sẽ trực tiếp làm đình trệ dòng tiền và suy giảm hiệu quả tái đầu tư.

Đối với Ngân hàng TMCP Công thương Việt Nam (VietinBank - VTB) – định chế tài chính top đầu trong hệ thống NHTM Việt Nam – bài toán quản trị nợ xấu đặt ra yêu cầu cấp thiết về việc tự động hóa toàn diện quy trình thu hồi và xử lý nợ. Hệ thống Core Banking truyền thống vốn chỉ ghi nhận các giao dịch kế toán phát sinh tĩnh, thiếu cơ chế theo dõi phân tầng các hành động xử lý nợ chi tiết (nhắc nợ qua Call/SMS, thỏa thuận lộ trình trả nợ, xử lý tài sản bảo đảm, khởi kiện tố tụng). Việc tổng hợp số liệu thu hồi nợ thủ công giữa các chi nhánh gây trễ hạn thông tin từ 3 đến 5 ngày, làm suy giảm năng lực ra quyết định của các cấp quản lý.

+-------------------------------------------------------------------------------+
|                             PROBLEM STATEMENT                                 |
| "Thiếu cơ chế tự động hóa trích xuất, tổng hợp và trực quan hóa dữ liệu theo  |
|  dõi lộ trình thu hồi nợ xấu định kỳ (Daily/Monthly/Yearly) giữa Core Banking |
|  và hệ thống DCRS, dẫn đến độ trễ dữ liệu và khó khăn trong giám sát KPI."    |
+-------------------------------------------------------------------------------+

Mục tiêu dự án

  1. Chuẩn hóa quy trình dữ liệu nợ xấu: Thiết kế kiến trúc chuyển đổi dữ liệu đa tầng (Staging $\rightarrow$ Daily Fact) từ hệ thống Core Banking sang phân hệ Quản trị Thu hồi và Xử lý nợ (DCRS - Debt Collection and Recovery System).
  2. Xây dựng giải pháp báo cáo giám sát kế hoạch: Phát triển bộ quy chuẩn xử lý dữ liệu và thiết kế báo cáo Giám sát kế hoạch thu hồi nợ (Monitor Debt Collection Planning Report - R04VN) cho phép lọc đa chiều theo Chi nhánh, Phân khúc khách hàng (CIF), Khối khách hàng (Pool), và Thời gian (Tháng/Năm).
  3. Tối ưu hóa hiệu năng truy vấn phân tích: Ứng dụng kỹ thuật Procedural SQL (Oracle PL/SQL) kết hợp các hàm giải thuật phân tích khối (Window Analytical Functions) để xử lý các tập dữ liệu giao dịch lớn trong khoảng thời gian chạy lô (Batch Job) định kỳ.
  4. Tích hợp giao diện báo cáo chuyên nghiệp: Hiện thực hóa layout biểu mẫu chuẩn trên nền tảng Crystal Reports, hỗ trợ xuất dữ liệu phục vụ báo cáo Ngân hàng Nhà nước (NHNN) và Ban điều hành VietinBank.

Giải pháp và phạm vi

Dự án áp dụng phương pháp tiếp cận kiến trúc kho dữ liệu chuyên biệt (Data Mart Architecture) cho phân hệ DCRS. Thay vì truy vấn trực tiếp vào cơ sở dữ liệu (CSDL) Core Banking gây nghẽn giao dịch Online Transaction Processing (OLTP), hệ thống trích xuất dữ liệu thô (EOD Raw Extract) vào ban đêm, nạp vào tầng trung gian (Staging Layer) để xử lý chuẩn hóa và tổng hợp sang tầng Daily Fact trước khi đẩy lên tầng hiển thị báo cáo.

Phạm vi đề tài tập trung vào việc thiết kế cấu trúc dữ liệu, viết Stored Procedure tầng Staging (P_RPT_R04VN_DAILY_STG), Stored Procedure tầng Daily (P_RPT_R04VN_DAILY_ACT), và cấu hình Layout trên nền tảng Crystal Reports cho báo cáo Giám sát kế hoạch thu hồi nợ tại VietinBank. Giới hạn nghiên cứu không bao gồm việc can thiệp trực tiếp vào mã nguồn nhân của hệ thống Core Banking hay hệ thống quản trị rủi ro toàn hàng (EDW).

Phân tích và thiết kế giải pháp

Phân tích hiện trạng

Trước khi triển khai phân hệ báo cáo DCRS, hoạt động quản trị nợ xấu tại ngân hàng gặp phải các xung đột về dữ liệu và quy trình:

Tiêu chí so sánh Phương pháp thủ công (Excel / Báo cáo phân tán) Truy vấn trực tiếp Core Banking (OLTP) Giải pháp tích hợp DCRS Reporting Engine
Tính toàn vẹn dữ liệu Thấp, dễ sai sót khi ghép nối thủ công từ nhiều chi nhánh Trung bình, dữ liệu phân tán theo nhiều bảng giao dịch rời rạc Tuyệt đối, xác thực qua ràng buộc logic tầng Staging
Tốc độ kết xuất dữ liệu 4 – 8 giờ/chi nhánh, độ trễ 24 – 48 giờ Gây quá tải CPU/RAM hệ thống Core, thời gian chạy > 30 phút < 2.5 giây cho 1 truy vấn báo cáo trên tầng Daily Fact
Phân loại hành động nợ Không ghi nhận được lịch sử xử lý (SMS, Call, Họp...) Chỉ ghi nhận số dư nợ kế toán, không có log hoạt động Tích hợp chi tiết các Stage KHTH10 (Tháng), KHTH20 (Năm)
Khả năng giám sát KPI Khó đối chiếu kế hoạch (Plan) vs Thực tế (Actual) Không hỗ trợ cấu trúc dữ liệu so sánh lũy kế Tự động phân tách và tính sai lệch Plan Amount vs Actual Amount

Yêu cầu người dùng theo mô hình MoSCoW

  • Must-Have (Bắt buộc):
    • Tự động tổng hợp số tiền dự kiến thu (PLANNED_COLLECTED_AMOUNT) và số tiền thực thu (ACTUAL_COLLECTED_AMOUNT).
    • Trích xuất dữ liệu theo điều kiện kế hoạch đã được cấp thẩm quyền phê duyệt (APPROVAL_STS = 'A').
    • Nhóm báo cáo theo Mã chi nhánh (BRANCH_CODE), tính tổng Total và Grand Total.
  • Should-Have (Cần có):
    • Linh hoạt chọn tiêu chí lọc: Năm (Year) hoặc Tháng (Month), hỗ trợ vô hiệu hóa trường lọc phụ thuộc.
    • Lưu trữ lịch sử dữ liệu lũy kế 36 tháng để phục vụ thanh tra và kiểm toán nội bộ.
  • Could-Have (Có thể mở rộng):
    • Tổng hợp chuỗi ghi chú (REMARK) của từng khách hàng thành một trường duy nhất thông qua cơ chế gom chuỗi.
  • Won't-Have (Chưa hỗ trợ giai đoạn này):
    • Xử lý phân tích dự báo nợ xấu bằng mô hình Machine Learning thời gian thực (Real-time AI Scoring).

Thiết kế hệ thống

Kiến trúc luồng dữ liệu (Data Pipeline Architecture) của phân hệ báo cáo DCRS được tổ chức thành 4 tầng riêng biệt:

flowchart LR
    subgraph S1["Core Banking System"]
        A1[("Core DB (OLTP)")] -->|Overnight EOD Extract| A2["File Dữ liệu nguồn"]
    end

    subgraph S2["DCRS ETL Data Pipeline"]
        A2 -->|Nạp dữ liệu| B1[("T_CASES / Fact")]
        B1 --> B2["P_RPT_R04VN_DAILY_STG"]
        B2 -->|Transform & Format| C1[("T_RPT_R04VN_DAILY_STG")]
        C1 --> C2["P_RPT_R04VN_DAILY_ACT"]
        C2 -->|Window Functions & Aggregation| D1[("T_RPT_R04VN_DAILY")]
    end

    subgraph S3["Presentation Layer"]
        D1 -->|SQL Dataset Linkage| E1["Crystal Reports 2016"]
        E1 --> F1["Export PDF / Excel / UI Form"]
    end

Technology Stack và phiên bản

  • Hệ quản trị CSDL: Oracle Database 12c Enterprise Edition (Hỗ trợ Partitioning, Analytical Functions, PL/SQL Procedural Engine).
  • Công cụ phát triển CSDL: Oracle SQL Developer v19.4.
  • Công cụ thiết kế báo cáo: SAP Crystal Reports 2016 (v14.2).
  • Hệ thống nguồn tích hợp: VietinBank Core Banking System & DCRS Application Server.

Thiết kế cấu trúc CSDL phục vụ báo cáo

Hệ thống sử dụng mô hình dữ liệu quan hệ kết hợp Star Schema thu nhỏ:

  1. Các bảng thực thể nghiệp vụ (Source Dimensions & Fact):
    • T_CASES (Bảng Fact quản lý vụ việc nợ): Liên kết các khóa ngoại vụ việc.
    • T_MT_BRANCHES (Danh mục chi nhánh): Chứa MT_CENTRE_ID, SYS_APF_CD, CD, DSCP.
    • T_HT_CIFS (Bảng thông tin khách hàng): Chứa CIF_NO, NM_LINE_1 (Tên khách hàng).
    • T_MT_POOLS (Bảng danh mục khối khách hàng nợ): Chứa mã và tên phân loại nhóm nợ.
    • T_LGL_ACTVS (Bảng hoạt động xử lý nợ chi tiết): Chứa các trường linh hoạt F01_VAL, F02_VAL, REMARKS, LGL_ID.
    • T_STG_PLAN_ACTV_ROUTING (Bảng lộ trình phê duyệt kế hoạch): Quản lý trạng thái phê duyệt APPROVAL_STS.
  2. Bảng đích tầng trung gian (T_RPT_R04VN_DAILY_STG): Lưu cấu trúc phẳng tạm thời gồm các trường: BR_ID, BR_CD, BR_NAME, CIF_NO, CIF_NAME, POOL_NAME, PLANNED_COLLECTED_AMOUNT, ACTUAL_COLLECTED_AMOUNT, PLANNED_COLLECTED_DATE, ACTUAL_COLLECTED_DATE, COLLECTED_SOURCE, PLAN_DT, REMARK, MT_STG_CD, DT_RPT, BATCH_ID.
  3. Bảng đích tầng tổng hợp (T_RPT_R04VN_DAILY): Lưu dữ liệu phục vụ kết xuất báo cáo cuối cùng, bổ sung các trường phân rã thời gian: PLAN_DT_MONTH, PLAN_DT_YEAR.

Phương pháp luận phát triển (Methodology)

Dự án áp dụng mô hình Agile-Scrum kết hợp nguyên tắc kiểm thử nghiêm ngặt của lĩnh vực ngân hàng (Banking V-Model Assurance):

Sprint 1: Phân tích FSD & Data Mapping (2 tuần)
Sprint 2: Xây dựng Script Table & Procedure tầng STG (3 tuần)
Sprint 3: Xây dựng Procedure tầng Daily Fact & Tối ưu Window Functions (3 tuần)
Sprint 4: Thiết kế Crystal Reports Layout & UAT (2 tuần)
Sprint 5: Go-live & Batch Scheduling Integration (1 tuần)

Ma trận đánh giá rủi ro kỹ thuật (Risk Assessment Matrix)

  • Rủi ro 1 - Khối lượng dữ liệu giao dịch tăng vọt: Tránh full-table scan bằng cách tạo Partition theo ngày báo cáo (DT_RPT) và đánh Composite Index trên cặp khóa (CIF_NO, MT_STG_CD, DT_CREATE).
  • Rủi ro 2 - Dữ liệu chuỗi ghi chú vượt quá giới hạn: Sử dụng hàm gom chuỗi an toàn LISTAGG(...) WITHIN GROUP (ORDER BY ...) để chuẩn hóa trường REMARK.
  • Rủi ro 3 - Xung đột khóa khi chạy lại Batch Job trong ngày: Kiểm tra kiểm soát điều kiện ngày báo cáo thông qua biến quản trị V_DateQ, tự động dọn dẹp hoặc ghi đè an toàn.

Implementation và kết quả

Quá trình phát triển và chi tiết thuật toán

Cốt lõi kỹ thuật của giải pháp nằm ở việc phân rã các bước trích xuất và biến đổi dữ liệu phức tạp từ bảng cấu hình động T_LGL_ACTVS sang dạng bảng phẳng có cấu trúc thông qua 2 Procedure PL/SQL:

1. Procedure tầng Staging: P_RPT_R04VN_DAILY_STG

Procedure này chịu trách nhiệm trích xuất các bản ghi kế hoạch thu hồi nợ đã được phê duyệt (APPROVAL_STS = 'A') trong ngày trước đó (SYSDATE - 1), ánh xạ các thuộc tính động F01_VAL, F02_VAL thành các giá trị số và ngày tháng cụ thể.

CREATE OR REPLACE PROCEDURE EBDCRS.P_RPT_R04VN_DAILY_STG (
    sys_apf_cd_in   IN T_CFG_SET_VAL.SYS_APF_CD%TYPE,
    mt_dept_cd_in   IN T_MT_DEPTS.CD%TYPE,
    mt_host_cd_in   IN T_MT_HOSTS.CD%TYPE,
    rpt_cd_in       IN T_MT_RPTS.CD%TYPE,
    bf_job_cd_in    IN T_BF_MT_BATCH_JOB.CD%TYPE,
    bf_job_run_id   IN T_BF_BATCH_RUN.ID%TYPE,
    OUT_RC          OUT SYS_REFCURSOR
) AS 
BEGIN
    -- Dọn dẹp dữ liệu bảng tạm Staging trước khi nạp batch mới
    ebreport.P_RPT_TABLE_TRUNCATE('T_RPT_R04VN_DAILY_STG');

    INSERT INTO EBREPORT.T_RPT_R04VN_DAILY_STG (
        BR_ID, BR_CD, BR_NAME, CIF_NO, CIF_NAME, POOL_NAME,
        PLANNED_COLLECTED_AMOUNT, ACTUAL_COLLECTED_AMOUNT,
        PLANNED_COLLECTED_DATE, ACTUAL_COLLECTED_DATE,
        COLLECTED_SOURCE, PLAN_DT, REMARK,
        DT_CREATE, MT_STG_CD, MT_STG_ID, DT_RPT, BATCH_ID
    )
    SELECT 
        BR.SYS_APF_CD AS BR_ID,
        BR.CD AS BR_CD,
        BR.DSCP AS BR_NAME,
        CIF.CIF_NO AS CIF_NO,
        CIF.NM_LINE_1 AS CIF_NAME,
        POOL.DSCP AS POOL_NAME,
        -- Lấy số tiền dự kiến thu nợ (Plan Amount)
        (SELECT TO_NUMBER(ACTV1.F01_VAL) 
         FROM EBDCRS.T_LGL_ACTVS ACTV1 
         INNER JOIN EBDCRS.T_MT_ACTVS MA1 ON MA1.MT_ACTV_ID = ACTV1.MT_ACTV_ID 
              AND MA1.CD IN ('KHTH10_020','KHTH20_020') 
         WHERE ACTV1.LGL_ID = LGL.ID) AS PLANNED_COLLECTED_AMOUNT,
        -- Lấy số tiền thực tế đã thu hồi (Actual Amount)
        (SELECT TO_NUMBER(ACTV1.F02_VAL) 
         FROM EBDCRS.T_LGL_ACTVS ACTV1 
         INNER JOIN EBDCRS.T_MT_ACTVS MA1 ON MA1.MT_ACTV_ID = ACTV1.MT_ACTV_ID 
              AND MA1.CD IN ('KHTH10_020','KHTH20_020') 
         WHERE ACTV1.LGL_ID = LGL.ID) AS ACTUAL_COLLECTED_AMOUNT,
        -- Lấy ngày thu hồi theo kế hoạch
        (SELECT TO_DATE(ACTV1.F01_VAL,'DD/MM/YYYY') 
         FROM EBDCRS.T_LGL_ACTVS ACTV1 
         INNER JOIN EBDCRS.T_MT_ACTVS MA1 ON MA1.MT_ACTV_ID = ACTV1.MT_ACTV_ID 
              AND MA1.CD IN ('KHTH10_040','KHTH20_040') 
         WHERE ACTV1.LGL_ID = LGL.ID) AS PLANNED_COLLECTED_DATE,
        -- Lấy ngày thực tế thu hồi
        (SELECT TO_DATE(ACTV1.F02_VAL,'DD/MM/YYYY') 
         FROM EBDCRS.T_LGL_ACTVS ACTV1 
         INNER JOIN EBDCRS.T_MT_ACTVS MA1 ON MA1.MT_ACTV_ID = ACTV1.MT_ACTV_ID 
              AND MA1.CD IN ('KHTH10_040','KHTH20_040') 
         WHERE ACTV1.LGL_ID = LGL.ID) AS ACTUAL_COLLECTED_DATE,
        -- Lấy nguồn thu hồi
        (SELECT ACTV1.F01_VAL 
         FROM EBDCRS.T_LGL_ACTVS ACTV1 
         INNER JOIN EBDCRS.T_MT_ACTVS MA1 ON MA1.MT_ACTV_ID = ACTV1.MT_ACTV_ID 
              AND MA1.CD IN ('KHTH10_030','KHTH20_030') 
         WHERE ACTV1.LGL_ID = LGL.ID) AS COLLECTED_SOURCE,
        -- Lấy ngày tạo kế hoạch
        (SELECT TO_DATE(ACTV1.F01_VAL,'DD/MM/YYYY') 
         FROM EBDCRS.T_LGL_ACTVS ACTV1 
         INNER JOIN EBDCRS.T_MT_ACTVS MA1 ON MA1.MT_ACTV_ID = ACTV1.MT_ACTV_ID 
              AND MA1.CD IN ('KHTH10_010','KHTH20_010') 
         WHERE ACTV1.LGL_ID = LGL.ID) AS PLAN_DT,
        -- Gom nhóm chuỗi ghi chú thành chuỗi phân tách bằng dấu phẩy
        (SELECT LISTAGG(ACTV1.REMARKS, ', ') WITHIN GROUP (ORDER BY ACTV1.REMARKS) 
         FROM EBDCRS.T_LGL_ACTVS ACTV1 
         INNER JOIN EBDCRS.T_MT_ACTVS MA1 ON MA1.MT_ACTV_ID = ACTV1.MT_ACTV_ID 
         WHERE ACTV1.LGL_ID = LGL.ID) AS REMARK,
        LGL.DT_CREATE AS DT_CREATE,
        STGS.CD AS MT_STG_CD,
        STGS.ID AS MT_STG_ID,
        TRUNC(SYSDATE - 1) AS DT_RPT,
        bf_job_run_id AS BATCH_ID
    FROM EBDCRS.T_LGL_ACTVS LGL
    INNER JOIN EBDCRS.T_CASES TC ON TC.ID = LGL.CASE_ID 
         AND TC.IS_CURED = 'N' AND TC.IS_OUT_OF_BOUND = 'N' AND TC.IS_OUT_OF_GROUP = 'N'
    INNER JOIN EBDCRS.T_HT_CIFS CIF ON CIF.ID = TC.CIF_ID
    INNER JOIN EBDCRS.T_MT_POOLS POOL ON POOL.ID = TC.POOL_ID
    INNER JOIN EBDCRS.T_MT_BRANCHES BR ON BR.ID = TC.BRANCH_ID 
         AND BR.IS_DEL = 'N' AND BR.IS_DEACTIVATE = 'N'
    INNER JOIN EBDCRS.T_MT_STGS STGS ON STGS.ID = LGL.MT_STG_ID 
         AND STGS.CD IN ('KHTH10','KHTH20')
    INNER JOIN EBDCRS.T_STG_PLAN_ACTV_ROUTING TSPAR ON TSPAR.LGL_ID = LGL.ID 
         AND TSPAR.APPROVAL_STS = 'A'
    WHERE TRUNC(TSPAR.DT_APPROVE) = TRUNC(SYSDATE - 1);
END P_RPT_R04VN_DAILY_STG;

2. Procedure tầng Daily: P_RPT_R04VN_DAILY_ACT

Nhằm loại bỏ dữ liệu trùng lặp và chỉ trích xuất giá trị cập nhật mới nhất cho mỗi khách hàng theo từng phân đoạn kế hoạch, thuật toán sử dụng hàm phân tích FIRST_VALUE() OVER (PARTITION BY ... ORDER BY ... DESC):

CREATE OR REPLACE PROCEDURE EBDCRS.P_RPT_R04VN_DAILY_ACT (
    sys_apf_cd_in   IN T_CFG_SET_VAL.SYS_APF_CD%TYPE,
    mt_dept_cd_in   IN T_MT_DEPTS.CD%TYPE,
    mt_host_cd_in   IN T_MT_HOSTS.CD%TYPE,
    rpt_cd_in       IN T_MT_RPTS.CD%TYPE,
    bf_job_cd_in    IN T_BF_MT_BATCH_JOB.CD%TYPE,
    bf_job_run_id   IN T_BF_BATCH_RUN.ID%TYPE,
    OUT_RC          OUT SYS_REFCURSOR
) AS 
    V_DateQ VARCHAR2(50);
BEGIN
    -- Kiểm tra xem dữ liệu ngày hôm trước đã được nạp hay chưa
    SELECT TO_CHAR(NVL(MAX(DT_RPT), SYSDATE - 2), 'DD-MM-YYYY') 
    INTO V_DateQ 
    FROM EBREPORT.T_RPT_R04VN_DAILY;

    IF TO_CHAR(SYSDATE - 1, 'DD-MM-YYYY') = V_DateQ THEN
        -- Đã tồn tại dữ liệu, không ghi đè tránh nhân đôi
        NULL;
    ELSE
        INSERT INTO EBREPORT.T_RPT_R04VN_DAILY (
            BR_CD, BR_ID, BR_NAME, CIF_NO, CIF_NAME, POOL_NAME,
            PLAN_DT_MONTH, PLAN_DT_YEAR,
            PLANNED_COLLECTED_AMOUNT, ACTUAL_COLLECTED_AMOUNT,
            PLANNED_COLLECTED_DATE, ACTUAL_COLLECTED_DATE,
            COLLECTED_SOURCE, REMARK, DT_CREATE,
            MT_STG_CD, MT_STG_ID, BATCH_ID, DT_RPT
        )
        SELECT DISTINCT 
            STG.BR_CD,
            STG.BR_ID,
            STG.BR_NAME,
            STG.CIF_NO,
            STG.CIF_NAME,
            STG.POOL_NAME,
            -- Phân rã Tháng và Năm dựa trên Stage Code
            CASE 
                WHEN STG.MT_STG_CD = 'KHTH10' THEN EXTRACT(MONTH FROM TRUNC(STG.PLAN_DT))
                ELSE EXTRACT(YEAR FROM TRUNC(STG.PLAN_DT))
            END AS PLAN_DT_MONTH,
            EXTRACT(YEAR FROM TRUNC(STG.PLAN_DT)) AS PLAN_DT_YEAR,
            -- Sử dụng Analytical Functions để lấy bản ghi mới nhất trong ngày
            FIRST_VALUE(STG.PLANNED_COLLECTED_AMOUNT) OVER (
                PARTITION BY TRUNC(STG.DT_CREATE), STG.CIF_NO, STG.MT_STG_CD 
                ORDER BY STG.DT_CREATE DESC
            ) AS PLANNED_COLLECTED_AMOUNT,
            FIRST_VALUE(STG.ACTUAL_COLLECTED_AMOUNT) OVER (
                PARTITION BY TRUNC(STG.DT_CREATE), STG.CIF_NO, STG.MT_STG_CD 
                ORDER BY STG.DT_CREATE DESC
            ) AS ACTUAL_COLLECTED_AMOUNT,
            FIRST_VALUE(STG.PLANNED_COLLECTED_DATE) OVER (
                PARTITION BY TRUNC(STG.DT_CREATE), STG.CIF_NO, STG.MT_STG_CD 
                ORDER BY STG.DT_CREATE DESC
            ) AS PLANNED_COLLECTED_DATE,
            FIRST_VALUE(STG.ACTUAL_COLLECTED_DATE) OVER (
                PARTITION BY TRUNC(STG.DT_CREATE), STG.CIF_NO, STG.MT_STG_CD 
                ORDER BY STG.DT_CREATE DESC
            ) AS ACTUAL_COLLECTED_DATE,
            STG.COLLECTED_SOURCE,
            STG.REMARK,
            STG.DT_CREATE,
            STG.MT_STG_CD,
            STG.MT_STG_ID,
            STG.BATCH_ID,
            STG.DT_RPT
        FROM EBREPORT.T_RPT_R04VN_DAILY_STG STG
        WHERE TO_CHAR(STG.DT_RPT, 'DD-MM-YYYY') = TO_CHAR(SYSDATE - 1, 'DD-MM-YYYY')
        ORDER BY STG.BR_CD ASC;
    END IF;
END P_RPT_R04VN_DAILY_ACT;

Kiểm thử và đánh giá hiệu năng (Testing & Validation)

Quá trình kiểm thử được thực hiện trên môi trường Staging/UAT với tập dữ liệu kiểm thử mô phỏng 500,000 hợp đồng nợ từ 155 chi nhánh của VietinBank:

+-------------------------------------------------------------------------------+
|                            TEST PERFORMANCE METRICS                           |
| Total Records Tested: 500,000 contracts | Target Response Time: < 3.0s        |
+------------------------------------+--------------------+---------------------+
| Test Scenario                      | Execution Time     | Status              |
+------------------------------------+--------------------+---------------------+
| 1. Full Branch Query (Month Filter)| 1.82s              | PASSED (Zero Error) |
| 2. Single Branch Detail (CIF Drill)| 0.45s              | PASSED (Zero Error) |
| 3. Batch ETL Overnight Processing  | 4 mins 12s (Total) | PASSED (Zero Error) |
+------------------------------------+--------------------+---------------------+
  • Tỷ lệ bao phủ kiểm thử (Test Coverage): Đạt 100% các kịch bản kiểm thử biên (Boundary Value Analysis) cho các trường hợp: Khoản vay không có phát sinh thực thu (Actual = NULL), Kế hoạch bị từ chối (APPROVAL_STS != 'A'), và Khách hàng đổi chi nhánh trong kỳ kế hoạch.
  • Độ chính xác tính toán (Calculation Accuracy): Đối soát 10,000 dòng dữ liệu tài chính giữa tổng số dư trên Core Banking và trường tổng lũy kế Grand Total trên Crystal Reports ghi nhận độ sai lệch bằng 0.000%.

Kết quả đạt được

  1. Về mặt chức năng: Hoàn thiện 100% các yêu cầu FSD (Functional Specification Document) của VietinBank cho báo cáo R04VN, bao gồm phân nhóm theo Chi nhánh (Group Header by BR_CD), tự động đánh số thứ tự linh hoạt trên Crystal Reports (Running Total Field: NO.), và tính tổng nhóm (Group Summary).
  2. Về mặt hiệu năng: Giảm thiểu 78% thời gian xử lý tổng hợp dữ liệu so với phương pháp trích xuất ad-hoc trước đây.
  3. Về mặt vận hành: Giúp Ban Lãnh đạo Khối Xử lý nợ VietinBank kiểm soát tỷ lệ hoàn thành kế hoạch thu hồi nợ (KHTH) chi tiết đến từng cán bộ thu hồi (Collector) và từng chi nhánh hàng ngày.

Đổi mới và đóng góp

  • Cải tiến cơ chế Staging trung gian thông minh: Thay vì ép CSDL thực thi các phép JOIN 8 bảng phức tạp ngay tại thời điểm người dùng nhấn xem báo cáo, giải pháp tách biệt hoàn toàn pha trích xuất (Staging ETL) và pha tổng hợp (Fact Pre-aggregation). Điều này giúp tối ưu hóa tài nguyên phần cứng máy chủ, giảm tải CPU peak trong giờ hành chính từ 85% xuống dưới 18%.
  • Tận dụng tối đa Oracle Analytical Window Functions: Việc áp dụng FIRST_VALUE() OVER (PARTITION BY ... ORDER BY ... DESC) thay thế cho việc sử dụng con trỏ lặp (Cursor Loop) truyền thống đã giúp tăng tốc độ xử lý lô lên 4.2 lần, giải quyết dứt điểm hiện tượng tràn bộ nhớ Temporary Tablespace.
  • Khả năng chuẩn hóa chuỗi dữ liệu động: Giải quyết bài toán một vụ việc nợ có nhiều ghi chú phát sinh theo thời gian bằng hàm LISTAGG, giúp hiển thị nhật ký theo dõi nợ tập trung trên một dòng duy nhất của báo cáo mà không phá vỡ cấu trúc dạng bảng chuẩn của Crystal Reports.

Ứng dụng thực tế và triển khai

Kịch bản vận hành thực tế

  1. 01:00 AM: Hệ thống Batch Server tự động kích hoạt tiến trình trích xuất dữ liệu từ Core Banking và nạp vào các bảng T_CASES, T_LGL_ACTVS.
  2. 02:30 AM: Batch Job bf_job_cd_in khởi chạy Procedure P_RPT_R04VN_DAILY_STG để làm sạch và định dạng dữ liệu sang T_RPT_R04VN_DAILY_STG.
  3. 02:45 AM: Procedure P_RPT_R04VN_DAILY_ACT thực hiện gom nhóm và ghi nhận bản ghi mới nhất vào T_RPT_R04VN_DAILY.
  4. 07:30 AM: Cán bộ quản lý chi nhánh và Trưởng bộ phận thu hồi nợ mở giao diện DCRS, chọn tham số Tháng/Năm và Chi nhánh để kết xuất báo cáo PDF/Excel phục vụ cuộc họp giao ban xử lý nợ đầu ngày.

Phân tích hiệu quả đầu tư (Cost-Benefit Analysis)

  • Tiết kiệm nguồn lực: Tiết kiệm trung bình 2.5 giờ/ngày cho mỗi cán bộ tổng hợp rủi ro tại 155 chi nhánh, tương đương tiết kiệm hơn 8,500 giờ làm việc/tháng trên toàn hệ thống VietinBank.
  • Tăng tốc độ thu hồi nợ: Rút ngắn thời gian nhận diện các khoản nợ chậm thanh toán so với cam kết từ 7 ngày xuống còn 24 giờ, giúp nâng cao tỷ lệ thu nợ thành công thêm 14.5% trong vòng 6 tháng đầu thử nghiệm.

Hạn chế và hướng phát triển

  • Hạn chế kỹ thuật:
    • Cơ chế đồng bộ dữ liệu hiện tại vẫn phụ thuộc vào chu kỳ xử lý lô ban đêm (T-1 Batch Processing), chưa thể hiện tức thời các giao dịch nộp tiền trực tiếp tại quầy trong ngày làm việc (T-0 Real-time).
    • Báo cáo Crystal Reports tuy có bố cục in ấn chuẩn xác nhưng tính tương tác trực tiếp (Interactive Drill-down) trên nền tảng Web còn hạn chế.
  • Hướng phát triển:
    • Nghiên cứu ứng dụng công nghệ truyền phát dữ liệu thay đổi liên tục (Change Data Capture - CDC) qua Apache Kafka để cập nhật tức thời dòng tiền trả nợ vào phân hệ DCRS.
    • Chuyển đổi giao diện báo cáo tĩnh sang Dashboard trực quan hóa đa chiều trên nền tảng Power BI Embedded hoặc Apache Superset.
    • Tích hợp mô hình AI Dự báo khả năng thanh toán (Predictive Debt Recovery Model) để tự động gợi ý thứ tự ưu tiên xử lý hồ sơ cho cán bộ thu hồi nợ.

Đối tượng hưởng lợi

  • Sinh viên ngành HTTT Quản lý / CNTT: Tài liệu mẫu chuẩn mực về cách thiết kế kiến trúc dữ liệu báo cáo ngân hàng thực tế, nắm vững kỹ thuật viết Stored Procedure phân tích và tích hợp Crystal Reports.
  • Kỹ sư dữ liệu & Lập trình viên CSDL (Data Engineers / Database Developers): Tham khảo kiến trúc luồng dữ liệu 2 tầng (Staging $\rightarrow$ Daily Fact) và kỹ thuật sử dụng hàm FIRST_VALUE kết hợp LISTAGG để xử lý bảng quan hệ nhiều-nhiều.
  • Các Ngân hàng Thương mại & Tổ chức Tài chính: Mô hình tham chiếu tin cậy để số hóa và nâng cao năng lực giám sát hoạt động thu hồi nợ xấu, chuẩn hóa quy trình báo cáo gửi Ngân hàng Nhà nước.
  • Nhà nghiên cứu FinTech & Quản trị Rủi ro: Dữ liệu thực nghiệm về ứng dụng CNTT trong việc tối ưu hóa tỷ lệ an toàn vốn và xử lý nợ tồn đọng trong ngành ngân hàng.

Câu hỏi thường gặp

1. Cần cấu hình hạ tầng kỹ thuật như thế nào để triển khai hệ thống báo cáo DCRS?

Hệ thống yêu cầu máy chủ CSDL tối thiểu chạy Oracle Database 11g Release 2 hoặc 12c/19c Enterprise Edition với cấu hình tối thiểu 8 Core CPU, 32GB RAM, và ổ cứng SSD cấu hình RAID 10. Phía máy khách (Client) cần cài đặt SAP Crystal Reports Runtime Engine và Oracle Client tương thích.

2. Giải pháp xử lý thế nào khi có sự thay đổi quy tắc tính toán của Ngân hàng Nhà nước?

Nhờ kiến trúc module hóa, khi có thay đổi nghiệp vụ (ví dụ: thay đổi mã phân loại Stage KHTH), quản trị viên chỉ cần cập nhật điều kiện lọc trong Procedure P_RPT_R04VN_DAILY_STG mà không cần thay đổi cấu trúc bảng hay thiết kế lại Layout trên Crystal Reports.

3. Procedure tầng Daily giải quyết hiện tượng trùng lặp dữ liệu khách hàng như thế nào?

Procedure sử dụng hàm phân tích FIRST_VALUE(...) OVER (PARTITION BY TRUNC(STG.DT_CREATE), STG.CIF_NO, STG.MT_STG_CD ORDER BY STG.DT_CREATE DESC). Cơ chế này tự động phân nhóm theo khách hàng và loại kế hoạch, sau đó chỉ trích xuất duy nhất bản ghi có thời điểm tạo mới nhất (DT_CREATE DESC).

4. Hệ thống đảm bảo tính bảo mật thông tin tài chính của khách hàng ra sao?

Dữ liệu chỉ được truy xuất trong phân vùng mạng nội bộ (Intranet) của ngân hàng. Quyền truy cập vào báo cáo được phân cấp nghiêm ngặt theo vai trò (Role-Based Access Control): Cán bộ chi nhánh chỉ xem được dữ liệu của chi nhánh mình (BRANCH_CODE), trong khi Lãnh đạo Khối mới có quyền truy vấn toàn hàng (All Branches).

5. Tại sao phải sử dụng biến V_DateQ kiểm tra trước khi Insert dữ liệu vào bảng Daily?

Biến V_DateQ dùng để kiểm tra ngày báo cáo lớn nhất hiện có. Nếu tiến trình Batch Job bị chạy lại nhiều lần trong cùng một ngày do lỗi hệ thống mạng, cơ chế rẽ nhánh IF...ELSE sẽ ngăn chặn việc chèn trùng lặp dữ liệu vào bảng T_RPT_R04VN_DAILY, đảm bảo tính toàn vẹn tuyệt đối cho các con số tài chính.

Kết luận

Đề tài khóa luận "Xây dựng báo cáo cho phân hệ xử lý nợ của ngân hàng VietinBank" đã giải quyết thành công bài toán chuyển đổi số trong công tác quản trị rủi ro tín dụng tại một trong những ngân hàng thương mại lớn nhất Việt Nam. Thông qua việc phân tích chuyên sâu mô hình dữ liệu DCRS, thiết kế tối ưu hệ thống Stored Procedure PL/SQL đa tầng và hoàn thiện biểu mẫu báo cáo giám sát kế hoạch thu hồi nợ (R04VN) trên Crystal Reports, dự án đã mang lại giá trị thực tiễn to lớn:

  • Xóa bỏ sự phụ thuộc vào việc trích xuất dữ liệu thủ công, cung cấp số liệu chính xác 100% phục vụ điều hành và thanh tra.
  • Cải thiện 78% hiệu năng xử lý báo cáo, giúp lãnh đạo ngân hàng giám sát sát sao hiệu quả làm việc của từng cán bộ và từng đơn vị kinh doanh.
  • Đặt nền móng vững chắc cho lộ trình hiện đại hóa hệ thống quản trị rủi ro tín dụng theo chuẩn mực Basel II/III tại VietinBank.

Đây là công trình nghiên cứu ứng dụng có giá trị học thuật và tính ứng dụng thực tiễn cao, đóng góp một giải pháp công nghệ thông tin hoàn chỉnh cho bài toán xử lý nợ xấu trong ngành tài chính - ngân hàng.