<?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 t.unreceived_sum_amount,
               t.overdue_sum_amount,
               not_due_sum_amount,
               t.overdue_amount_1,
               t.overdue_amount_2,
               t.overdue_amount_3,
               t.overdue_amount_left,
               t.not_due_amount_1,
               t.not_due_amount_2,
               t.not_due_amount_3,
               t.not_due_amount_4,
               t.not_due_amount_5,
               t.not_due_amount_6,
               t.not_due_amount_left,
               t.not_due_amount_7,
               t.not_due_amount_8,
               t.not_due_amount_9,
               t.not_due_amount_10,
               t.not_due_amount_11,
               t.not_due_amount_12,
               t.not_due_amount_13,
               t.not_due_amount_14,
               t.not_due_amount_15,
               t.not_due_amount_16,
               t.not_due_amount_17,
               t.not_due_amount_18,
               t.not_due_amount_19,
               t.not_due_amount_20,
               t.not_due_amount_21,
               t.not_due_amount_22,
               t.not_due_amount_23,
               t.not_due_amount_24,
               t.not_due_amount_25,
               t.not_due_amount_26,
               t.not_due_amount_27,
               t.not_due_amount_28,
               t.not_due_amount_29,
               t.not_due_amount_30,
               t.not_due_amount_31,
               t.not_due_amount_32,
               t.not_due_amount_33,
               t.not_due_amount_34,
               t.not_due_amount_35,
               t.not_due_amount_36,
               t.not_due_amount_37,
               t.not_due_amount_38,
               t.not_due_amount_39,
               t.not_due_amount_40,
               t.not_due_amount_41,
               t.not_due_amount_42,
               t.not_due_amount_43,
               t.not_due_amount_44,
               t.not_due_amount_45,
               t.not_due_amount_46,
               t.not_due_amount_47,
               t.not_due_amount_48,
			   t.not_due_amount_49,
               t.not_due_amount_50,
               t.not_due_amount_51,
               t.not_due_amount_52,
               t.not_due_amount_53,
               t.not_due_amount_54,
               t.not_due_amount_55,
               t.not_due_amount_56,
               t.not_due_amount_57,
               t.not_due_amount_58,
               t.not_due_amount_59,
               t.not_due_amount_60,
			   t.not_due_amount_61,
               t.not_due_amount_62,
               t.not_due_amount_63,
               t.not_due_amount_64,
               t.not_due_amount_65,
               t.not_due_amount_66,
               t.not_due_amount_67,
               t.not_due_amount_68,
               t.not_due_amount_69,
               t.not_due_amount_70,
               t.not_due_amount_48_left,
               t.not_due_amount_year_1,
               t.not_due_amount_year_2,
               t.not_due_amount_year_3,
               t.not_due_amount_year_4,
               t.not_due_amount_year_5,
               t.not_due_amount_year_5_left,
               m.bp_name bp_id_agent_level1_n,
               (select m.bp_code from hls_bp_master m where m.bp_id = t.bp_id) bp_code,
               (select contract_number
                  from con_contract
                 where contract_id = t.contract_id) contract_number,
                 (Select bt.description
          From hls_business_type bt,con_contract ct
         Where bt.business_type = ct.business_type
         and ct.contract_id = t.contract_id) As business_type_n,
               t.account_code account_id_n,
               (select description_text
                  from fnd_descriptions
                 where description_id = g.description_id
                   and language = 'ZHS') account_name,
               t.contract_id,
			   (SELECT bpv.bp_name
                  FROM hls_bp_master_lv bpv,con_contract cc
                 WHERE bpv.bp_id = cc.bp_id_tenant
                 and cc.contract_id = t.contract_id
                 and rownum = 1) bp_id_telnet_n,
               t.bp_id,
               t.agent_bp_id,
               ((select v.bp_name from hls_bp_master v where v.bp_id = t.vender_id)||'/'||(SELECT v.code_value_name
             FROM sys_code_values_v v
            WHERE v.code = 'DIVISION' AND v.code_value = t.division))division_n,t.division
          from con_cashflow_monthly_temp t, hls_bp_master m, gld_accounts g
         where m.bp_id = t.agent_bp_id
           and g.account_id(+) = t.account_id
         union
        select tt.unreceived_sum_amount,
               tt.overdue_sum_amount,
               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_49,
               tt.not_due_amount_50,
               tt.not_due_amount_51,
               tt.not_due_amount_52,
               tt.not_due_amount_53,
               tt.not_due_amount_54,
               tt.not_due_amount_55,
               tt.not_due_amount_56,
               tt.not_due_amount_57,
               tt.not_due_amount_58,
               tt.not_due_amount_59,
               tt.not_due_amount_60,
               tt.not_due_amount_61,
               tt.not_due_amount_62,
               tt.not_due_amount_63,
               tt.not_due_amount_64,
               tt.not_due_amount_65,
               tt.not_due_amount_66,
               tt.not_due_amount_67,
               tt.not_due_amount_68,
               tt.not_due_amount_69,
               tt.not_due_amount_70,
               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,
               (select m.bp_code
                  from hls_bp_master m
                 where m.bp_id = tt.bp_id) bp_code,
               (select contract_number
                  from con_contract
                 where contract_id = tt.contract_id) contract_number,
                  (Select bt.description
          From hls_business_type bt,con_contract ct
         Where bt.business_type = ct.business_type
         and ct.contract_id = tt.contract_id) As business_type_n,
               '小计:' account_id_n,
               null account_name,
               tt.contract_id,
			   (SELECT bpv.bp_name
                  FROM hls_bp_master_lv bpv,con_contract cc
                 WHERE bpv.bp_id = cc.bp_id_tenant
                 and cc.contract_id = tt.contract_id
                 and rownum = 1) bp_id_telnet_n,
               tt.bp_id,
               tt.agent_bp_id,
               ((select v.bp_name from hls_bp_master v where v.bp_id = tt.vender_id)||'/'||(SELECT v.code_value_name
             FROM sys_code_values_v v
            WHERE v.code = 'DIVISION' AND v.code_value = tt.division))division_n,tt.division
          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_49, 0)) not_due_amount_49,
                       sum(nvl(t.not_due_amount_50, 0)) not_due_amount_50,
                       sum(nvl(t.not_due_amount_51, 0)) not_due_amount_51,
                       sum(nvl(t.not_due_amount_52, 0)) not_due_amount_52,
                       sum(nvl(t.not_due_amount_53, 0)) not_due_amount_53,
                       sum(nvl(t.not_due_amount_54, 0)) not_due_amount_54,
                       sum(nvl(t.not_due_amount_55, 0)) not_due_amount_55,
                       sum(nvl(t.not_due_amount_56, 0)) not_due_amount_56,
                       sum(nvl(t.not_due_amount_57, 0)) not_due_amount_57,
                       sum(nvl(t.not_due_amount_58, 0)) not_due_amount_58,
                       sum(nvl(t.not_due_amount_59, 0)) not_due_amount_59,
                       sum(nvl(t.not_due_amount_60, 0)) not_due_amount_60,
                       sum(nvl(t.not_due_amount_61, 0)) not_due_amount_61,
                       sum(nvl(t.not_due_amount_62, 0)) not_due_amount_62,
                       sum(nvl(t.not_due_amount_63, 0)) not_due_amount_63,
                       sum(nvl(t.not_due_amount_64, 0)) not_due_amount_64,
                       sum(nvl(t.not_due_amount_65, 0)) not_due_amount_65,
                       sum(nvl(t.not_due_amount_66, 0)) not_due_amount_66,
                       sum(nvl(t.not_due_amount_67, 0)) not_due_amount_67,
                       sum(nvl(t.not_due_amount_68, 0)) not_due_amount_68,
                       sum(nvl(t.not_due_amount_69, 0)) not_due_amount_69,
                       sum(nvl(t.not_due_amount_70, 0)) not_due_amount_70,
                       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.contract_id,
                       t.bp_id,
                       t.agent_bp_id,
                       t.vender_id,
                       t.division
                  from con_cashflow_monthly_temp t
                 group by t.contract_id, t.bp_id, t.agent_bp_id,t.vender_id,t.division) tt,
               hls_bp_master m
         where m.bp_id = tt.agent_bp_id) ttt #WHERE_CLAUSE#
 order by ttt.contract_id, ttt.account_id_n



            ]]></bm:query-sql>
        </bm:operation>
        <bm:operation name="update">
            <bm:update-sql><![CDATA[
		        begin
		           con_cashflow_monthly_pkg.insert_con_cash_temp(
		           p_session_id   =>${/session/@session_id},
                                 p_due_date     =>to_date(${@due_date},'yyyy-mm-dd'),
                                 p_user_id      =>${/session/@user_id},
                                 p_business_type =>${@business_type},
                                 p_overday1     =>${@overday1},
                                 p_overday2     =>${@overday2},
                                 p_overday3     =>${@overday3},
                                 p_not_overday1 =>${@not_overday1},
                                 p_not_overday2 =>${@not_overday2},
                                 p_not_overday3 =>${@not_overday3},
                                 p_not_overday4 =>${@not_overday4},
                                 p_not_overday5 =>${@not_overday5},
                                 p_not_overday6 =>${@not_overday6});
		        end;
        ]]></bm:update-sql>
        </bm:operation>
    </bm:operations>
    <bm:fields>
        <bm:field name="division_n"/>
        <bm:field name="division"/>
        <bm:field name="business_type_n"/>
        <bm:field name="contract_id"/>
        <bm:field name="bp_code"/>
        <bm:field name="contract_number"/>
        <bm:field name="bp_id_telnet_n"/>
        <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_49"/>
        <bm:field name="not_due_amount_50"/>
        <bm:field name="not_due_amount_51"/>
        <bm:field name="not_due_amount_52"/>
        <bm:field name="not_due_amount_53"/>
        <bm:field name="not_due_amount_54"/>
        <bm:field name="not_due_amount_55"/>
        <bm:field name="not_due_amount_56"/>
        <bm:field name="not_due_amount_57"/>
        <bm:field name="not_due_amount_58"/>
        <bm:field name="not_due_amount_59"/>
        <bm:field name="not_due_amount_60"/>
        <bm:field name="not_due_amount_61"/>
        <bm:field name="not_due_amount_62"/>
        <bm:field name="not_due_amount_63"/>
        <bm:field name="not_due_amount_64"/>
        <bm:field name="not_due_amount_65"/>
        <bm:field name="not_due_amount_66"/>
        <bm:field name="not_due_amount_67"/>
        <bm:field name="not_due_amount_68"/>
        <bm:field name="not_due_amount_69"/>
        <bm:field name="not_due_amount_70"/>
        <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-field name="bp_id_agent_level1_id" queryExpression="to_char(ttt.agent_bp_id) in (select regexp_substr(${@bp_id},
                              '[^;]+',
                              1,
                              level)
           from dual
         connect by regexp_substr(${@bp_id},
                                  '[^;]+',
                                  1,
                                  level) is not null)"/>
    </bm:query-fields>
</bm:model>
