<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: WangYu  
    $Date: 2014-4-25 上午09:30:21  
    $Revision: 1.0  
    $Purpose: 
-->
<bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm" needAccessControl="false">
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[
           select *
  from (select tt.unreceived_sum_amount,
               tt.overdue_sum_amount,
               tt.not_due_sum_amount,
               tt.overdue_amount_1,
               tt.overdue_amount_2,
               tt.overdue_amount_3,
               tt.overdue_amount_left,
               tt.not_due_amount_1,
               tt.not_due_amount_2,
               tt.not_due_amount_3,
               tt.not_due_amount_4,
               tt.not_due_amount_5,
               tt.not_due_amount_6,
               tt.not_due_amount_left,
               tt.not_due_amount_7,
               tt.not_due_amount_8,
               tt.not_due_amount_9,
               tt.not_due_amount_10,
               tt.not_due_amount_11,
               tt.not_due_amount_12,
               tt.not_due_amount_13,
               tt.not_due_amount_14,
               tt.not_due_amount_15,
               tt.not_due_amount_16,
               tt.not_due_amount_17,
               tt.not_due_amount_18,
               tt.not_due_amount_19,
               tt.not_due_amount_20,
               tt.not_due_amount_21,
               tt.not_due_amount_22,
               tt.not_due_amount_23,
               tt.not_due_amount_24,
               tt.not_due_amount_25,
               tt.not_due_amount_26,
               tt.not_due_amount_27,
               tt.not_due_amount_28,
               tt.not_due_amount_29,
               tt.not_due_amount_30,
               tt.not_due_amount_31,
               tt.not_due_amount_32,
               tt.not_due_amount_33,
               tt.not_due_amount_34,
               tt.not_due_amount_35,
               tt.not_due_amount_36,
               tt.not_due_amount_37,
               tt.not_due_amount_38,
               tt.not_due_amount_39,
               tt.not_due_amount_40,
               tt.not_due_amount_41,
               tt.not_due_amount_42,
               tt.not_due_amount_43,
               tt.not_due_amount_44,
               tt.not_due_amount_45,
               tt.not_due_amount_46,
               tt.not_due_amount_47,
               tt.not_due_amount_48,
               tt.not_due_amount_48_left,
               tt.not_due_amount_year_1,
               tt.not_due_amount_year_2,
               tt.not_due_amount_year_3,
               tt.not_due_amount_year_4,
               tt.not_due_amount_year_5,
               tt.not_due_amount_year_5_left,
               m.bp_name bp_id_agent_level1_n,
               tt.account_code account_id_n,
               (select description_text
                  from fnd_descriptions
                 where description_id = g.description_id
                   and language = 'ZHS') account_name,
               tt.agent_bp_id,
                tt.division,
               (decode(tt.division,
                       '10',
                       '日立建机(上海)有限公司',
                       '20',
                       '永立建机（中国）有限公司',
                       '30',
                       '高空作业车厂商',
                       '40',
                       '力至优叉车')) || '/' ||
               (SELECT v.code_value_name
                  FROM sys_code_values_v v
                 WHERE v.code = 'DIVISION'
                   AND v.code_value = tt.division) division_n
          from (select sum(nvl(t.unreceived_sum_amount, 0)) unreceived_sum_amount,
                       sum(nvl(t.overdue_sum_amount, 0)) overdue_sum_amount,
                       sum(nvl(t.not_due_sum_amount, 0)) not_due_sum_amount,
                       sum(nvl(t.overdue_amount_1, 0)) overdue_amount_1,
                       sum(nvl(t.overdue_amount_2, 0)) overdue_amount_2,
                       sum(nvl(t.overdue_amount_3, 0)) overdue_amount_3,
                       sum(nvl(t.overdue_amount_left, 0)) overdue_amount_left,
                       sum(nvl(t.not_due_amount_1, 0)) not_due_amount_1,
                       sum(nvl(t.not_due_amount_2, 0)) not_due_amount_2,
                       sum(nvl(t.not_due_amount_3, 0)) not_due_amount_3,
                       sum(nvl(t.not_due_amount_4, 0)) not_due_amount_4,
                       sum(nvl(t.not_due_amount_5, 0)) not_due_amount_5,
                       sum(nvl(t.not_due_amount_6, 0)) not_due_amount_6,
                       sum(nvl(t.not_due_amount_left, 0)) not_due_amount_left,
                       sum(nvl(t.not_due_amount_7, 0)) not_due_amount_7,
                       sum(nvl(t.not_due_amount_8, 0)) not_due_amount_8,
                       sum(nvl(t.not_due_amount_9, 0)) not_due_amount_9,
                       sum(nvl(t.not_due_amount_10, 0)) not_due_amount_10,
                       sum(nvl(t.not_due_amount_11, 0)) not_due_amount_11,
                       sum(nvl(t.not_due_amount_12, 0)) not_due_amount_12,
                       sum(nvl(t.not_due_amount_13, 0)) not_due_amount_13,
                       sum(nvl(t.not_due_amount_14, 0)) not_due_amount_14,
                       sum(nvl(t.not_due_amount_15, 0)) not_due_amount_15,
                       sum(nvl(t.not_due_amount_16, 0)) not_due_amount_16,
                       sum(nvl(t.not_due_amount_17, 0)) not_due_amount_17,
                       sum(nvl(t.not_due_amount_18, 0)) not_due_amount_18,
                       sum(nvl(t.not_due_amount_19, 0)) not_due_amount_19,
                       sum(nvl(t.not_due_amount_20, 0)) not_due_amount_20,
                       sum(nvl(t.not_due_amount_21, 0)) not_due_amount_21,
                       sum(nvl(t.not_due_amount_22, 0)) not_due_amount_22,
                       sum(nvl(t.not_due_amount_23, 0)) not_due_amount_23,
                       sum(nvl(t.not_due_amount_24, 0)) not_due_amount_24,
                       sum(nvl(t.not_due_amount_25, 0)) not_due_amount_25,
                       sum(nvl(t.not_due_amount_26, 0)) not_due_amount_26,
                       sum(nvl(t.not_due_amount_27, 0)) not_due_amount_27,
                       sum(nvl(t.not_due_amount_28, 0)) not_due_amount_28,
                       sum(nvl(t.not_due_amount_29, 0)) not_due_amount_29,
                       sum(nvl(t.not_due_amount_30, 0)) not_due_amount_30,
                       sum(nvl(t.not_due_amount_31, 0)) not_due_amount_31,
                       sum(nvl(t.not_due_amount_32, 0)) not_due_amount_32,
                       sum(nvl(t.not_due_amount_33, 0)) not_due_amount_33,
                       sum(nvl(t.not_due_amount_34, 0)) not_due_amount_34,
                       sum(nvl(t.not_due_amount_35, 0)) not_due_amount_35,
                       sum(nvl(t.not_due_amount_36, 0)) not_due_amount_36,
                       sum(nvl(t.not_due_amount_37, 0)) not_due_amount_37,
                       sum(nvl(t.not_due_amount_38, 0)) not_due_amount_38,
                       sum(nvl(t.not_due_amount_39, 0)) not_due_amount_39,
                       sum(nvl(t.not_due_amount_40, 0)) not_due_amount_40,
                       sum(nvl(t.not_due_amount_41, 0)) not_due_amount_41,
                       sum(nvl(t.not_due_amount_42, 0)) not_due_amount_42,
                       sum(nvl(t.not_due_amount_43, 0)) not_due_amount_43,
                       sum(nvl(t.not_due_amount_44, 0)) not_due_amount_44,
                       sum(nvl(t.not_due_amount_45, 0)) not_due_amount_45,
                       sum(nvl(t.not_due_amount_46, 0)) not_due_amount_46,
                       sum(nvl(t.not_due_amount_47, 0)) not_due_amount_47,
                       sum(nvl(t.not_due_amount_48, 0)) not_due_amount_48,
                       sum(nvl(t.not_due_amount_48_left, 0)) not_due_amount_48_left,
                       sum(nvl(t.not_due_amount_year_1, 0)) not_due_amount_year_1,
                       sum(nvl(t.not_due_amount_year_2, 0)) not_due_amount_year_2,
                       sum(nvl(t.not_due_amount_year_3, 0)) not_due_amount_year_3,
                       sum(nvl(t.not_due_amount_year_4, 0)) not_due_amount_year_4,
                       sum(nvl(t.not_due_amount_year_5, 0)) not_due_amount_year_5,
                       sum(nvl(t.not_due_amount_year_5_left, 0)) not_due_amount_year_5_left,
                       t.agent_bp_id,
                       t.division,
                       t.account_id,
                       t.account_code
                  from con_cashflow_monthly_temp t
                  group by t.agent_bp_id,
                          t.division,
                          t.account_id,
                          t.account_code) tt,
               hls_bp_master m,
               gld_accounts g
         where m.bp_id = tt.agent_bp_id
           and g.account_id(+) = tt.account_id
        union
        select tt.unreceived_sum_amount,
               tt.overdue_sum_amount,
               tt.not_due_sum_amount,
               tt.overdue_amount_1,
               tt.overdue_amount_2,
               tt.overdue_amount_3,
               tt.overdue_amount_left,
               tt.not_due_amount_1,
               tt.not_due_amount_2,
               tt.not_due_amount_3,
               tt.not_due_amount_4,
               tt.not_due_amount_5,
               tt.not_due_amount_6,
               tt.not_due_amount_left,
               tt.not_due_amount_7,
               tt.not_due_amount_8,
               tt.not_due_amount_9,
               tt.not_due_amount_10,
               tt.not_due_amount_11,
               tt.not_due_amount_12,
               tt.not_due_amount_13,
               tt.not_due_amount_14,
               tt.not_due_amount_15,
               tt.not_due_amount_16,
               tt.not_due_amount_17,
               tt.not_due_amount_18,
               tt.not_due_amount_19,
               tt.not_due_amount_20,
               tt.not_due_amount_21,
               tt.not_due_amount_22,
               tt.not_due_amount_23,
               tt.not_due_amount_24,
               tt.not_due_amount_25,
               tt.not_due_amount_26,
               tt.not_due_amount_27,
               tt.not_due_amount_28,
               tt.not_due_amount_29,
               tt.not_due_amount_30,
               tt.not_due_amount_31,
               tt.not_due_amount_32,
               tt.not_due_amount_33,
               tt.not_due_amount_34,
               tt.not_due_amount_35,
               tt.not_due_amount_36,
               tt.not_due_amount_37,
               tt.not_due_amount_38,
               tt.not_due_amount_39,
               tt.not_due_amount_40,
               tt.not_due_amount_41,
               tt.not_due_amount_42,
               tt.not_due_amount_43,
               tt.not_due_amount_44,
               tt.not_due_amount_45,
               tt.not_due_amount_46,
               tt.not_due_amount_47,
               tt.not_due_amount_48,
               tt.not_due_amount_48_left,
               tt.not_due_amount_year_1,
               tt.not_due_amount_year_2,
               tt.not_due_amount_year_3,
               tt.not_due_amount_year_4,
               tt.not_due_amount_year_5,
               tt.not_due_amount_year_5_left,
               m.bp_name bp_id_agent_level1_n,
               '小计:' account_id_n,
               null account_name,
               tt.agent_bp_id,
                tt.division,
               (decode(tt.division,
                       '10',
                       '日立建机(上海)有限公司',
                       '20',
                       '永立建机（中国）有限公司',
                       '30',
                       '高空作业车厂商',
                       '40',
                       '力至优叉车')) || '/' ||
               (SELECT v.code_value_name
                  FROM sys_code_values_v v
                 WHERE v.code = 'DIVISION'
                   AND v.code_value = tt.division) division_n
          from (select sum(nvl(t.unreceived_sum_amount, 0)) unreceived_sum_amount,
                       sum(nvl(t.overdue_sum_amount, 0)) overdue_sum_amount,
                       sum(nvl(t.not_due_sum_amount, 0)) not_due_sum_amount,
                       sum(nvl(t.overdue_amount_1, 0)) overdue_amount_1,
                       sum(nvl(t.overdue_amount_2, 0)) overdue_amount_2,
                       sum(nvl(t.overdue_amount_3, 0)) overdue_amount_3,
                       sum(nvl(t.overdue_amount_left, 0)) overdue_amount_left,
                       sum(nvl(t.not_due_amount_1, 0)) not_due_amount_1,
                       sum(nvl(t.not_due_amount_2, 0)) not_due_amount_2,
                       sum(nvl(t.not_due_amount_3, 0)) not_due_amount_3,
                       sum(nvl(t.not_due_amount_4, 0)) not_due_amount_4,
                       sum(nvl(t.not_due_amount_5, 0)) not_due_amount_5,
                       sum(nvl(t.not_due_amount_6, 0)) not_due_amount_6,
                       sum(nvl(t.not_due_amount_left, 0)) not_due_amount_left,
                       sum(nvl(t.not_due_amount_7, 0)) not_due_amount_7,
                       sum(nvl(t.not_due_amount_8, 0)) not_due_amount_8,
                       sum(nvl(t.not_due_amount_9, 0)) not_due_amount_9,
                       sum(nvl(t.not_due_amount_10, 0)) not_due_amount_10,
                       sum(nvl(t.not_due_amount_11, 0)) not_due_amount_11,
                       sum(nvl(t.not_due_amount_12, 0)) not_due_amount_12,
                       sum(nvl(t.not_due_amount_13, 0)) not_due_amount_13,
                       sum(nvl(t.not_due_amount_14, 0)) not_due_amount_14,
                       sum(nvl(t.not_due_amount_15, 0)) not_due_amount_15,
                       sum(nvl(t.not_due_amount_16, 0)) not_due_amount_16,
                       sum(nvl(t.not_due_amount_17, 0)) not_due_amount_17,
                       sum(nvl(t.not_due_amount_18, 0)) not_due_amount_18,
                       sum(nvl(t.not_due_amount_19, 0)) not_due_amount_19,
                       sum(nvl(t.not_due_amount_20, 0)) not_due_amount_20,
                       sum(nvl(t.not_due_amount_21, 0)) not_due_amount_21,
                       sum(nvl(t.not_due_amount_22, 0)) not_due_amount_22,
                       sum(nvl(t.not_due_amount_23, 0)) not_due_amount_23,
                       sum(nvl(t.not_due_amount_24, 0)) not_due_amount_24,
                       sum(nvl(t.not_due_amount_25, 0)) not_due_amount_25,
                       sum(nvl(t.not_due_amount_26, 0)) not_due_amount_26,
                       sum(nvl(t.not_due_amount_27, 0)) not_due_amount_27,
                       sum(nvl(t.not_due_amount_28, 0)) not_due_amount_28,
                       sum(nvl(t.not_due_amount_29, 0)) not_due_amount_29,
                       sum(nvl(t.not_due_amount_30, 0)) not_due_amount_30,
                       sum(nvl(t.not_due_amount_31, 0)) not_due_amount_31,
                       sum(nvl(t.not_due_amount_32, 0)) not_due_amount_32,
                       sum(nvl(t.not_due_amount_33, 0)) not_due_amount_33,
                       sum(nvl(t.not_due_amount_34, 0)) not_due_amount_34,
                       sum(nvl(t.not_due_amount_35, 0)) not_due_amount_35,
                       sum(nvl(t.not_due_amount_36, 0)) not_due_amount_36,
                       sum(nvl(t.not_due_amount_37, 0)) not_due_amount_37,
                       sum(nvl(t.not_due_amount_38, 0)) not_due_amount_38,
                       sum(nvl(t.not_due_amount_39, 0)) not_due_amount_39,
                       sum(nvl(t.not_due_amount_40, 0)) not_due_amount_40,
                       sum(nvl(t.not_due_amount_41, 0)) not_due_amount_41,
                       sum(nvl(t.not_due_amount_42, 0)) not_due_amount_42,
                       sum(nvl(t.not_due_amount_43, 0)) not_due_amount_43,
                       sum(nvl(t.not_due_amount_44, 0)) not_due_amount_44,
                       sum(nvl(t.not_due_amount_45, 0)) not_due_amount_45,
                       sum(nvl(t.not_due_amount_46, 0)) not_due_amount_46,
                       sum(nvl(t.not_due_amount_47, 0)) not_due_amount_47,
                       sum(nvl(t.not_due_amount_48, 0)) not_due_amount_48,
                       sum(nvl(t.not_due_amount_48_left, 0)) not_due_amount_48_left,
                       sum(nvl(t.not_due_amount_year_1, 0)) not_due_amount_year_1,
                       sum(nvl(t.not_due_amount_year_2, 0)) not_due_amount_year_2,
                       sum(nvl(t.not_due_amount_year_3, 0)) not_due_amount_year_3,
                       sum(nvl(t.not_due_amount_year_4, 0)) not_due_amount_year_4,
                       sum(nvl(t.not_due_amount_year_5, 0)) not_due_amount_year_5,
                       sum(nvl(t.not_due_amount_year_5_left, 0)) not_due_amount_year_5_left,
                       t.agent_bp_id,
                       t.division
                  from con_cashflow_monthly_temp t
                  group by t.agent_bp_id, t.division) tt,
               hls_bp_master m
         where m.bp_id = tt.agent_bp_id) ttt #WHERE_CLAUSE#
 order by ttt.agent_bp_id,ttt.division, ttt.account_id_n



            ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
    <bm:fields>
        <bm:field name="division_n"/>
        <bm:field name="division"/>
        <bm:field name="bp_id_agent_level1_n"/>
        <bm:field name="account_id_n"/>
        <bm:field name="account_name"/>
        <bm:field name="unreceived_sum_amount"/>
        <bm:field name="overdue_amount_1"/>
        <bm:field name="overdue_amount_2"/>
        <bm:field name="overdue_amount_3"/>
        <bm:field name="overdue_amount_left"/>
        <bm:field name="overdue_sum_amount"/>
        <bm:field name="not_due_sum_amount"/>
        <bm:field name="not_due_amount_1"/>
        <bm:field name="not_due_amount_2"/>
        <bm:field name="not_due_amount_3"/>
        <bm:field name="not_due_amount_4"/>
        <bm:field name="not_due_amount_5"/>
        <bm:field name="not_due_amount_6"/>
        <bm:field name="not_due_amount_left"/>
        <bm:field name="not_due_amount_7"/>
        <bm:field name="not_due_amount_8"/>
        <bm:field name="not_due_amount_9"/>
        <bm:field name="not_due_amount_10"/>
        <bm:field name="not_due_amount_11"/>
        <bm:field name="not_due_amount_12"/>
        <bm:field name="not_due_amount_13"/>
        <bm:field name="not_due_amount_14"/>
        <bm:field name="not_due_amount_15"/>
        <bm:field name="not_due_amount_16"/>
        <bm:field name="not_due_amount_17"/>
        <bm:field name="not_due_amount_18"/>
        <bm:field name="not_due_amount_19"/>
        <bm:field name="not_due_amount_20"/>
        <bm:field name="not_due_amount_21"/>
        <bm:field name="not_due_amount_22"/>
        <bm:field name="not_due_amount_23"/>
        <bm:field name="not_due_amount_24"/>
        <bm:field name="not_due_amount_25"/>
        <bm:field name="not_due_amount_26"/>
        <bm:field name="not_due_amount_27"/>
        <bm:field name="not_due_amount_28"/>
        <bm:field name="not_due_amount_29"/>
        <bm:field name="not_due_amount_30"/>
        <bm:field name="not_due_amount_31"/>
        <bm:field name="not_due_amount_32"/>
        <bm:field name="not_due_amount_33"/>
        <bm:field name="not_due_amount_34"/>
        <bm:field name="not_due_amount_35"/>
        <bm:field name="not_due_amount_36"/>
        <bm:field name="not_due_amount_37"/>
        <bm:field name="not_due_amount_38"/>
        <bm:field name="not_due_amount_39"/>
        <bm:field name="not_due_amount_40"/>
        <bm:field name="not_due_amount_41"/>
        <bm:field name="not_due_amount_42"/>
        <bm:field name="not_due_amount_43"/>
        <bm:field name="not_due_amount_44"/>
        <bm:field name="not_due_amount_45"/>
        <bm:field name="not_due_amount_46"/>
        <bm:field name="not_due_amount_47"/>
        <bm:field name="not_due_amount_48"/>
        <bm:field name="not_due_amount_48_left"/>
        <bm:field name="not_due_amount_year_1"/>
        <bm:field name="not_due_amount_year_2"/>
        <bm:field name="not_due_amount_year_3"/>
        <bm:field name="not_due_amount_year_4"/>
        <bm:field name="not_due_amount_year_5"/>
        <bm:field name="not_due_amount_year_5_left"/>



    </bm:fields>
<!--    <bm:query-fields>-->
<!--        <bm:query-field field="bp_id_tenant_n" queryExpression="t1.bp_id_tenant_n = ${@bp_id_tenant_n}"/>-->
<!--        <bm:query-field field="account_name" queryExpression="t1.account_name = ${@account_name}"/>-->
<!--        <bm:query-field field="account_id_n" queryExpression="t1.account_id_n = ${@account_id_n}"/>-->
<!--    </bm:query-fields>-->

    <bm:query-fields>
        <bm:query-field field="division" queryExpression="ttt.division in (${:@division})"/>
        <bm:query-field name="bp_id" queryExpression="ttt.agent_bp_id in (${:@bp_id})"/>
    </bm:query-fields>
</bm:model>
