<?xml version="1.0" encoding="UTF-8"?>
<bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm">
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[
            select * from(
                SELECT
                    v.node_sequence_num,
                    v.node_desc,
                    cwo.document_id,
                    v.approver,
                    NVL(v.comment_text, v.action_type_desc) comment_text
                FROM
                    tre_loan_con_write_off cwo,
                    tre_loan_con_batch_interface itfc,
                    zj_wfl_approve_history_v v,
                    zj_wfl_approve_record zr
                WHERE
                    cwo.batch_id      = itfc.batch_id AND
                    v.workflow_code   = 'REPAYMENT_FLOW' AND
                    itfc.instance_id  = v.instance_id AND
                    v.action_type    IS NOT NULL AND
                    v.create_date_fmt >
                    (SELECT
                        NVL(MAX(v.create_date_fmt), to_date('2000-01-01', 'yyyy-mm-dd'))
                    FROM
                        zj_wfl_approve_history_v hv
                    WHERE
                        hv.instance_id = v.instance_id AND
                        hv.action_type = 2
                    ) AND
                    v.record_id     = zr.record_id AND
                    cwo.document_id = ${/parameter/@repayment_plan_id}
                    union
                 SELECT
                    0 node_sequence_num,
                    null node_desc,
                    cwo.document_id,
                   (select su.description from sys_user su where su.user_id = hws.user_id) approver,
                    null comment_text
                FROM
                    tre_loan_con_write_off cwo,
                    tre_loan_con_batch_interface itfc,
                    HN_WFL_SUMMITED_BY hws
                WHERE
                    cwo.batch_id      = itfc.batch_id AND
                    itfc.instance_id  = hws.instance_id AND
                    hws.record_id = (select MAX(hwsb.record_id) from HN_WFL_SUMMITED_BY hwsb where hwsb.instance_id = itfc.instance_id) and
                    cwo.document_id = ${/parameter/@repayment_plan_id}
                    ) t1 #WHERE_CLAUSE#
                    ORDER BY t1.node_sequence_num
            ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
</bm:model>
