<?xml version="1.0" encoding="UTF-8"?>
<bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm" needAccessControl="false">
    <bm:fields>
        <bm:field name="goods_cd"/>
        <bm:field name="goods_nm"/>
        <bm:field name="goods_type_name"/>
        <bm:field name="column_a"/>
        <bm:field name="column_b"/>
        <bm:field name="column_c"/>
        <bm:field name="column_d"/>
        <bm:field name="column_e"/>
        <bm:field name="column_f"/>
        <bm:field name="column_g"/>
        <bm:field name="payment"/>
        <bm:field name="pay_months" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="machine_types_all"/>
        <bm:field name="available_agnt_all"/>
        <bm:field name="available_period_begin"  databaseType="NUMBER" datatype="java.lang.Long"/>
        <bm:field name="available_period_end"  databaseType="NUMBER" datatype="java.lang.Long"/>
    </bm:fields>
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[
        with tmp1 as
 (select *
    from (select t.goods_cd,
                 t.goods_nm,
                 t.goods_type_name,
                 t.pay_type,
                 t.pay_value,
                 t.payment,
                 t.pay_months,
                 t.machine_types_all,
                 t.available_agnt_all,
                 t.available_period_begin,
                 t.available_period_end
            from t_goods_info t,t_goods_batch tg
            where t.batch_id = tg.batch_id
            and tg.enable_flag = 'Y') t1
  pivot(max(pay_value)
     for pay_type in(1 as column_a,
                    2 as column_b,
                    3 as column_c,
                    4 as column_d,
                    5 as column_e,
                    6 as column_f,
                    7 as column_g)) p)
select goods_cd,
       goods_nm,
       goods_type_name,
       decode(column_a,null,null,to_char(column_a,'FM990.00')||'%') column_a,-- "客户保证金(%)"
       decode(column_b,null,null,to_char(column_b,'FM990.00')||'%') column_b,-- "客户手续费1(%)",
       decode(column_c,null,null,to_char(column_c,'FM990.00')||'%') column_c,-- "利率(%)",
       decode(column_d,null,null,to_char(column_d,'FM990.00')||'%') column_d,-- "代理商手续费率(%)",
       decode(column_e,null,null,to_char(column_e,'FM990.00')||'%') column_e,-- "IRR(%)",
       decode(column_f,null,null,to_char(column_f,'FM990.00')||'%') column_f,-- "客户手续费2(%)",
       decode(column_g,null,null,to_char(column_g,'FM990.00')||'%') column_g,-- "延期利率(%)",
       decode(payment,null,null,to_char(payment,'FM990.00')||'%') payment,
       pay_months,
       machine_types_all,
       available_agnt_all,
       to_number(available_period_begin) available_period_begin,
       to_number(available_period_end) available_period_end
  from tmp1
                #WHERE_CLAUSE#
                #ORDER_BY_CLAUSE#
                ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
    <bm:query-fields>
        <bm:query-field field="goods_type_name" queryOperator="like"/>
        <bm:query-field field="available_agnt_all" queryOperator="like"/>
        <bm:query-field field="machine_types_all" queryOperator="like"/>
        <bm:query-field field="goods_nm" queryOperator="like"/>
        <bm:query-field field="payment" queryOperator="="/>
        <bm:query-field field="pay_months" queryOperator="="/>
    </bm:query-fields>
</bm:model>
