<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: Clocc  
    $Date: 2015-6-10 下午3:57:56  
    $Revision: 1.0  
    $Purpose: 
-->
<bm:model xmlns:f="leaf.database.features" xmlns:bm="http://www.leaf-framework.org/schema/bm">
    <bm:fields>
        <bm:field name="write_off_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="WRITE_OFF_ID" prompt="CSH_WRITE_OFF.WRITE_OFF_ID"/>
        <!-- <bm:field name="write_off_type" databaseType="VARCHAR2" datatype="java.lang.String" lookupCode="CSH513_WRITE_OFF_TYPE" lookupField="write_off_type_name" physicalName="WRITE_OFF_TYPE" prompt="CSH_WRITE_OFF.WRITE_OFF_TYPE"/> -->
        <bm:field name="write_off_type" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="WRITE_OFF_TYPE" prompt="CSH_WRITE_OFF.WRITE_OFF_TYPE"/>
        <bm:field name="write_off_date" physicalName="WRITE_OFF_DATE" prompt="HLS.WRITE_OFF_DATE"/>
        <bm:field name="internal_period_num" databaseType="NUMBER" datatype="java.lang.Long" physicalName="INTERNAL_PERIOD_NUM" prompt="CSH_WRITE_OFF.INTERNAL_PERIOD_NUM"/>
        <bm:field name="period_name" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="PERIOD_NAME" prompt="CSH_WRITE_OFF.PERIOD_NAME"/>
        <bm:field name="csh_transaction_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="CSH_TRANSACTION_ID" prompt="CSH_WRITE_OFF.CSH_TRANSACTION_ID"/>
        <bm:field name="csh_write_off_amount" databaseType="NUMBER" datatype="java.lang.Double" physicalName="CSH_WRITE_OFF_AMOUNT" prompt="HLS.CSH_WRITE_OFF_AMOUNT"/>
        <bm:field name="subsequent_csh_trx_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="SUBSEQUENT_CSH_TRX_ID" prompt="CSH_WRITE_OFF.SUBSEQUENT_CSH_TRX_ID"/>
        <bm:field name="subseq_csh_write_off_amount" databaseType="NUMBER" datatype="java.lang.Double" physicalName="SUBSEQ_CSH_WRITE_OFF_AMOUNT" prompt="CSH_WRITE_OFF.SUBSEQ_CSH_WRITE_OFF_AMOUNT"/>
        <bm:field name="reversed_flag" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="REVERSED_FLAG" prompt="CSH_WRITE_OFF.REVERSED_FLAG"/>
        <bm:field name="reversed_write_off_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="REVERSED_WRITE_OFF_ID" prompt="CSH_WRITE_OFF.REVERSED_WRITE_OFF_ID"/>
        <bm:field name="reversed_date" databaseType="DATE" datatype="java.util.Date" physicalName="REVERSED_DATE" prompt="CSH_WRITE_OFF.REVERSED_DATE"/>
        <bm:field name="cashflow_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="CASHFLOW_ID" prompt="CSH_WRITE_OFF.CASHFLOW_ID"/>
        <bm:field name="contract_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="CONTRACT_ID" prompt="CSH_WRITE_OFF.CONTRACT_ID"/>
        <bm:field name="times" databaseType="NUMBER" datatype="java.lang.Long" physicalName="TIMES" prompt="CSH_WRITE_OFF.TIMES"/>
        <bm:field name="cf_item" databaseType="NUMBER" datatype="java.lang.Long" physicalName="CF_ITEM" prompt="CSH_WRITE_OFF.CF_ITEM"/>
        <bm:field name="cf_type" databaseType="NUMBER" datatype="java.lang.Long" physicalName="CF_TYPE" prompt="CSH_WRITE_OFF.CF_TYPE"/>
        <bm:field name="penalty_calc_date" databaseType="DATE" datatype="java.util.Date" physicalName="PENALTY_CALC_DATE" prompt="CSH_WRITE_OFF.PENALTY_CALC_DATE"/>
        <bm:field name="write_off_due_amount" databaseType="NUMBER" datatype="java.lang.Double" physicalName="WRITE_OFF_DUE_AMOUNT" prompt="HLS.WRITEOFF_DUE_AMOUNT"/>
        <bm:field name="write_off_principal" databaseType="NUMBER" datatype="java.lang.Double" physicalName="WRITE_OFF_PRINCIPAL" prompt="CSH513.WRITE_OFF_PRINCIPAL"/>
        <bm:field name="write_off_interest" databaseType="NUMBER" datatype="java.lang.Double" physicalName="WRITE_OFF_INTEREST" prompt="CSH513.WRITE_OFF_INTEREST"/>
        <bm:field name="description" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="DESCRIPTION" prompt="CSH_WRITE_OFF.DESCRIPTION"/>
        <bm:field name="opposite_doc_category" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="OPPOSITE_DOC_CATEGORY" prompt="CSH_WRITE_OFF.OPPOSITE_DOC_CATEGORY"/>
        <bm:field name="opposite_doc_type" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="OPPOSITE_DOC_TYPE" prompt="CSH_WRITE_OFF.OPPOSITE_DOC_TYPE"/>
        <bm:field name="opposite_doc_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="OPPOSITE_DOC_ID" prompt="CSH_WRITE_OFF.OPPOSITE_DOC_ID"/>
        <bm:field name="opposite_doc_line_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="OPPOSITE_DOC_LINE_ID" prompt="CSH_WRITE_OFF.OPPOSITE_DOC_LINE_ID"/>
        <bm:field name="opposite_doc_detail_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="OPPOSITE_DOC_DETAIL_ID" prompt="CSH_WRITE_OFF.OPPOSITE_DOC_DETAIL_ID"/>
        <bm:field name="opposite_write_off_amount" databaseType="NUMBER" datatype="java.lang.Double" physicalName="OPPOSITE_WRITE_OFF_AMOUNT" prompt="CSH_WRITE_OFF.OPPOSITE_WRITE_OFF_AMOUNT"/>
        <bm:field name="create_je_mothed" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="CREATE_JE_MOTHED" prompt="CSH_WRITE_OFF.CREATE_JE_MOTHED"/>
        <bm:field name="create_je_flag" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="CREATE_JE_FLAG" prompt="CSH_WRITE_OFF.CREATE_JE_FLAG"/>
        <bm:field name="gld_interface_flag" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="GLD_INTERFACE_FLAG" prompt="CSH_WRITE_OFF.GLD_INTERFACE_FLAG"/>
        <!-- <bm:field name="write_off_type_name" forInsert="false" forUpdate="false" prompt="HLS.WRITE_OFF_TYPE"/> -->
        <bm:field name="reversed_flag_desc" forInsert="false" forUpdate="false"/>
        <bm:field name="transaction_date" forInsert="false" forQuery="true" forUpdate="false"/>
        <bm:field name="bank_slip_num" forInsert="false" forUpdate="false"/>
        <bm:field name="contract_number"/>
        <bm:field name="project_name"/>
        <bm:field name="bp_name"/>
        <bm:field name="due_date"/>
        <bm:field name="cf_item_name"/>
        <bm:field name="write_off_type_name"/>
        <bm:field name="company_name"/>
        <bm:field name="division"/>
        <!-- <bm:field name="transaction_date" databaseType="DATE" datatype="java.util.Date" expression="(select transaction_date from CSH_TRANSACTION a where a.TRANSACTION_ID=t1.CSH_TRANSACTION_ID)"  forInsert="false" forUpdate="false" /> -->
    </bm:fields>
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[
                SELECT
                    *
                FROM
                    (SELECT
                        t1.WRITE_OFF_ID,
                        (SELECT
                            s.code_value_name
                        FROM
                            sys_code_values_v s
                        WHERE
                            s.code                    = 'CSH513_WRITE_OFF_TYPE' AND
                            s.code_value              = t1.WRITE_OFF_TYPE AND
                            s.code_enabled_flag       = 'Y' AND
                            s.code_value_enabled_flag = 'Y'
                        ) write_off_type_name,
                        t1.WRITE_OFF_TYPE,
                        TO_CHAR(t1.WRITE_OFF_DATE,'yyyy-mm-dd') write_off_date,
                        t1.INTERNAL_PERIOD_NUM,
                        t1.PERIOD_NAME,
                        t1.CSH_TRANSACTION_ID,
                        t1.CSH_WRITE_OFF_AMOUNT,
                        t1.SUBSEQUENT_CSH_TRX_ID,
                        t1.SUBSEQ_CSH_WRITE_OFF_AMOUNT,
                        t1.REVERSED_FLAG,
                        t1.REVERSED_WRITE_OFF_ID,
                        t1.REVERSED_DATE,
                        t1.CASHFLOW_ID,
                        t1.CONTRACT_ID,
                        t1.TIMES,
                        t1.CF_ITEM,
                        t1.CF_TYPE,
                        t1.PENALTY_CALC_DATE,
                        t1.WRITE_OFF_DUE_AMOUNT,
                        t1.WRITE_OFF_PRINCIPAL,
                        t1.WRITE_OFF_INTEREST,
                        t1.DESCRIPTION,
                        t1.OPPOSITE_DOC_CATEGORY,
                        t1.OPPOSITE_DOC_TYPE,
                        t1.OPPOSITE_DOC_ID,
                        t1.OPPOSITE_DOC_LINE_ID,
                        t1.OPPOSITE_DOC_DETAIL_ID,
                        t1.OPPOSITE_WRITE_OFF_AMOUNT,
                        t1.CREATE_JE_MOTHED,
                        t1.CREATE_JE_FLAG,
                        t1.GLD_INTERFACE_FLAG,
                        (SELECT
                            a.code_value_name
                        FROM
                            sys_code_values_v a
                        WHERE
                            code         = 'HLS_REVERSED_FLAG' AND
                            a.code_value = t1.reversed_flag
                        ) AS reversed_flag_desc,
                        (SELECT
                            TO_CHAR(ct.transaction_date,'yyyy-mm-dd')
                        FROM
                            csh_transaction ct
                        WHERE
                            ct.transaction_id = t1.csh_transaction_id
                        ) AS transaction_date,
                        (SELECT
                            bank_slip_num
                        FROM
                            CSH_TRANSACTION a
                        WHERE
                            a.TRANSACTION_ID = t1.CSH_TRANSACTION_ID
                        ) AS bank_slip_num,
                        (SELECT
                            fc.company_full_name
                        FROM
                            fnd_companies_vl fc
                        WHERE
                            fc.company_id =
                            (SELECT
                                cc.company_id
                            FROM
                                con_contract cc
                            WHERE
                                cc.contract_id = t1.contract_id
                            )
                        ) AS company_name,
                        (SELECT
                            t.division_n
                        FROM
                            con_contract_lv t
                        WHERE
                            t.contract_id = t1.contract_id
                        ) AS division,
                        TO_CHAR(t2.DUE_DATE,'yyyy-mm-dd') DUE_DATE,
                        t2.CONTRACT_NUMBER,
                        t2.PROJECT_NAME,
                        t2.BP_NAME,
                        t3.DESCRIPTION AS cf_type_name,
                        t4.CF_ITEM_DESC AS cf_item_name
                    FROM
                        CSH_WRITE_OFF t1
                    LEFT OUTER JOIN CON_CONTRACT_CASHFLOW_V t2
                    ON
                        t1.CASHFLOW_ID = t2.CASHFLOW_ID
                    LEFT OUTER JOIN HLS_CASHFLOW_TYPE_V t3
                    ON
                        t1.CF_TYPE = t3.CF_TYPE
                    LEFT OUTER JOIN HLS_CASHFLOW_ITEM_V t4
                    ON
                        t1.CF_ITEM = t4.CF_ITEM
                    ORDER BY
                        contract_id,
                        times,
                        cf_item
                    ) tt #WHERE_CLAUSE#
            ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
    <bm:query-fields>
        <bm:query-field name="due_date_from" queryExpression="due_date &gt;= ${@due_date_from}"/>
        <bm:query-field name="due_date_to" queryExpression="due_date &lt;= ${@due_date_to}"/>
        <bm:query-field field="bp_name" queryOperator="="/>
        <bm:query-field name="write_off_type_name" queryExpression="tt.write_off_type_name like ${@write_off_type_name}"/>
        <bm:query-field name="contract_number" queryExpression="contract_number like ${@contract_number}"/>
        <bm:query-field field="write_off_date" queryOperator="="/>
        <bm:query-field field="csh_transaction_id" queryOperator="="/>
        <bm:query-field field="reversed_flag" queryOperator="="/>
        <bm:query-field name="reverse_detail_flag" queryExpression="${@reverse_detail_flag}=&apos;Y&apos; and t1.write_off_type in (&apos;RECEIPT_CREDIT&apos;,&apos;RECEIPT_ADVANCE_RECEIPT&apos;,&apos;ADVANCE_RECEIPT_CREDIT&apos;)"/>
        <!-- <bm:query-field name="bank_slip_num" queryExpression="bank_slip_num like ${@bank_slip_num}"/> -->
        <bm:query-field field="transaction_date" queryOperator="="/>
        <bm:query-field name="bank_slip_num_from" queryExpression="bank_slip_num &gt;= ${@bank_slip_num_from}"/>
        <bm:query-field name="bank_slip_num_to" queryExpression="bank_slip_num &lt;= ${@bank_slip_num_to}"/>
        <bm:query-field name="transaction_date_from" queryExpression="transaction_date &gt;= ${@transaction_date_from}"/>
        <bm:query-field name="transaction_date_to" queryExpression="transaction_date &lt;= ${@transaction_date_to}"/>
        <bm:query-field name="write_off_date_from" queryExpression="write_off_date &gt;= ${@write_off_date_from}"/>
        <bm:query-field name="write_off_date_to" queryExpression="write_off_date &lt;= ${@write_off_date_to}"/>
        <!-- <bm:query-field name="transaction_date_from" queryExpression="transaction_date &gt;= to_date(${@transaction_date_from},&apos;yyyy-mm-dd&apos;)"/>
        <bm:query-field name="transaction_date_to" queryExpression="transaction_date &lt;= to_date(${@transaction_date_to},&apos;yyyy-mm-dd&apos;)"/> -->
    </bm:query-fields>
    <!--     <bm:data-filters >
    
    <bm:data-filter name="query" expression="reversed_flag = 'Y'"   />
    </bm:data-filters> -->
</bm:model>
