<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: Calvin 
    $Date:  2018年03月21日14:16:46
    $Revision: 1.0  
    $Purpose: 每月消费报告
-->
<bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm">
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[
                SELECT
                    *
                FROM
                    (SELECT
                        '20'
                        || SUBSTR(regexp_substr(t.transamt, '[^;]+', 1, rownum), 1, instr(regexp_substr(t.transamt, '[^;]+', 1, rownum), '_', -1) - 1) MONTH,
                        SUBSTR(regexp_substr(t.transamt, '[^;]+', 1, rownum), instr(regexp_substr(t.transamt, '[^;]+', 1, rownum), '_', -1) + 1) transamt,
                        SUBSTR(regexp_substr(t.amtrank, '[^;]+', 1, rownum), instr(regexp_substr(t.amtrank, '[^;]+', 1, rownum), '_', -1) + 1) amtrank,
                        SUBSTR(regexp_substr(t.transcnt, '[^;]+', 1, rownum), instr(regexp_substr(t.transcnt, '[^;]+', 1, rownum), '_', -1) + 1) transcnt,
                        SUBSTR(regexp_substr(t.cntrank, '[^;]+', 1, rownum), instr(regexp_substr(t.cntrank, '[^;]+', 1, rownum), '_', -1) + 1) cntrank,
                        SUBSTR(regexp_substr(t.cashamt, '[^;]+', 1, rownum), instr(regexp_substr(t.cashamt, '[^;]+', 1, rownum), '_', -1) + 1) cashamt,
                        SUBSTR(regexp_substr(t.cashcnt, '[^;]+', 1, rownum), instr(regexp_substr(t.cashcnt, '[^;]+', 1, rownum), '_', -1) + 1) cashcnt
                    FROM
                        (select * from CCX_INDEXMONTHCONSUMES ci where ci.chk_list_id = ${@chk_list_id}) t CONNECT BY rownum <= 12
                    ) t1
            ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
</bm:model>
