<?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
                        b.contract_id,
                        b.contract_number,
                        b.contract_name,
                        b.contract_status,
                        b.contract_status_n,
                        b.bp_id_tenant,
                        b.bp_id_tenant_n,
                        a.cf_item, --现金流项目
                        a.cf_item_desc,
                        a.times,
                        a.due_date, --到期日
                        b.company_id,
                        NVL(a.due_amount,0) due_amount,                                          --到期金额
                        NVL(a.principal,0) principal,                                            --到期本金
                        NVL(a.interest,0) interest,                                              --到期利息
                        (NVL(a.principal,0) - NVL(a.received_principal,0)) unreceived_principal, --未收本金
                        (NVL(a.due_amount,0) - NVL(a.received_amount,0)) unreceived_amount,      --未收金额
                        (NVL(a.interest,0) - NVL(a.received_interest,0)) unreceived_interest,    --未收利息
                        a.last_received_date,                                                    --最后收款日
                        NVL(a.received_amount,0) received_amount,                                --实收金额
                        NVL(a.received_principal,0) received_principal,                          --实收本金
                        NVL(a.received_interest,0) received_interest,                            --实收利息
                        a.overdue_status,                                                        --逾期标志
                        NVL(a.OVERDUE_MAX_DAYS,0) overdue_max_days,                              --逾期天数
                        NVL(
                        (SELECT
                            t.due_amount
                        FROM
                            con_contract_cashflow t
                        WHERE
                            t.contract_id  = a.contract_id AND
                            t.times        = a.times AND
                            t.cf_item      = 9 AND
                            t.cf_direction = 'INFLOW'
                        ),0) penalty,
                        b.price_list,
                        b.price_list_n,
                        b.document_type_n,
                        b.document_type,
                        a.write_off_flag,
                        a.cashflow_id,
                        '144' payment_method_id,
                        '现金' payment_method_display,
                        (select t.invoice_agent_id  from  prj_project t where t.project_id = b.project_id) invoice_agent_id,
                        (select t.invoice_agent_id_n  from  prj_project_lv t where t.project_id = b.project_id) invoice_agent_id_n
                    FROM
                        con_contract_cashflow_all_v a,
                        con_contract_lv b
                    WHERE
                        b.contract_id   = a.contract_id AND
                        b.data_class    = 'NORMAL' AND
                        a.cf_direction ='INFLOW' and
                        a.write_off_flag = 'NOT' and
                        a.cf_item in (1,9)
                        --and b.contract_status not in ('PENDING')
                        and b.contract_status in ('SIGN', 'INCEPT', 'ET')
                    ORDER BY
                        b.contract_number,
                        a.times
                    ) t1 #WHERE_CLAUSE#
            ]]></bm:query-sql>
        </bm:operation>
        <bm:operation name="execute">
            <bm:update-sql><![CDATA[
                BEGIN
                    csh_batch_transaction_pkg.write_off_specified_cashflow(p_cashflow_id =>${@cashflow_id}, 
                    													   p_contract_account_id =>${@contract_account_id},
	                    												   p_write_off_date => to_date(${@write_off_date}, 'YYYY-MM-DD'),
	                    												   p_payment_method_id=>   ${@payment_method_id},
                    													   p_user_id => ${/session/@user_id});
                END;
            ]]></bm:update-sql>
        </bm:operation>
    </bm:operations>
    <bm:fields>
        <bm:field name="invoice_agent_id"/>
        <bm:field name="invoice_agent_id_n"/>
        <bm:field name="payment_method_id"/>
        <bm:field name="payment_method_display"/>
        <!-- <bm:field name="collection_classes"/>
        <bm:field name="collection_classes_desc"/> -->
        <bm:field name="cashflow_id"/>
        <bm:field name="company_id"/>
        <bm:field name="contract_number"/>
        <bm:field name="contract_id"/>
        <bm:field name="contract_name"/>
        <bm:field name="contract_status_n"/>
        <bm:field name="bp_id_tenant_n"/>
        <bm:field name="cf_item"/>
        <bm:field name="bp_id_tenant"/>
        <bm:field name="cf_item_desc"/>
        <bm:field name="times"/>
        <bm:field name="due_amount"/>
        <bm:field name="principal"/>
        <bm:field name="interest"/>
        <bm:field name="unreceived_principal"/>
        <bm:field name="unreceived_amount"/>
        <bm:field name="unreceived_interest"/>
        <bm:field name="received_amount"/>
        <bm:field name="received_principal"/>
        <bm:field name="received_interest"/>
        <bm:field name="overdue_status"/>
        <bm:field name="overdue_max_days"/>
        <bm:field name="penalty"/>
        <bm:field name="contract_status"/>
        <bm:field name="price_list"/>
        <bm:field name="price_list_n"/>
        <bm:field name="document_type_n"/>
        <bm:field name="document_type"/>
        <bm:field name="write_off_flag"/>
        <bm:field name="due_date" databaseType="Date" datatype="java.util.Date"/>
        <bm:field name="last_received_date" databaseType="Date" datatype="java.util.Date"/>
    </bm:fields>
    <bm:query-fields>
        <bm:query-field field="invoice_agent_id" queryOperator="="/>
        <bm:query-field name="contract_number_from" queryExpression="t1.contract_number &gt;= ${@contract_number_from}"/>
        <bm:query-field name="contract_number_to" queryExpression="t1.contract_number &lt;= ${@contract_number_to}"/>
        <bm:query-field field="bp_id_tenant" queryOperator="="/>
        <bm:query-field field="contract_number" queryOperator="like"/>
        <bm:query-field field="cf_item" queryOperator="="/>
        <bm:query-field field="contract_status" queryOperator="="/>
        <bm:query-field field="overdue_status" queryOperator="="/>
        <bm:query-field name="due_date_from" queryExpression="t1.due_date &gt;= to_date(${@due_date_from},&apos;yyyy-mm-dd&apos;)"/>
        <bm:query-field name="due_date_to" queryExpression="t1.due_date &lt;= to_date(${@due_date_to},&apos;yyyy-mm-dd&apos;)"/>
        <bm:query-field name="unreceived_amount_from" queryExpression="t1.unreceived_amount &gt;= ${@unreceived_amount_from}"/>
        <bm:query-field name="unreceived_amount_to" queryExpression="t1.unreceived_amount &lt;= ${@unreceived_amount_to}"/>
        <bm:query-field field="price_list" queryOperator="like"/>
        <bm:query-field field="document_type" queryOperator="="/>
        <bm:query-field name="company_authority" queryExpression="t1.company_id = ${@company_authority} "/>
        <bm:query-field field="write_off_flag" queryExpression="((${@write_off_flag}=&apos;Y&apos; and t1.write_off_flag=&apos;FULL&apos;) or (${@write_off_flag}=&apos;N&apos; and t1.write_off_flag!=&apos;FULL&apos;)) "/>
        <!-- <bm:query-field field="brand_id_n" queryOperator="like"/> -->
        <!-- <bm:query-field field="pattern" queryOperator="like"/> -->
    </bm:query-fields>
</bm:model>
