<?xml version="1.0" encoding="UTF-8"?>
<bm:model xmlns:f="leaf.database.features" xmlns:bm="http://www.leaf-framework.org/schema/bm" alias="t1" baseTable="RD_EBANK_SPD_TXDS71_DOC">
    <bm:fields>
        <bm:field name="document_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="DOCUMENT_ID" prompt="HLS_EBANK_CCB_DOCUMENT.DOCUMENT_ID"/>
        <bm:field name="document_number" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="DOCUMENT_NUMBER" prompt="HLS_EBANK_CCB_DOCUMENT.DOCUMENT_NUMBER"/>
        <bm:field name="seq_no" databaseType="NUMBER" datatype="java.lang.Long" physicalName="SEQ_NO" prompt="HLS_EBANK_CCB_DOCUMENT.SEQ_NO"/>
        <bm:field name="company_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="COMPANY_ID" prompt="HLS_EBANK_CCB_DOCUMENT.COMPANY_ID"/>
        <bm:field name="contract_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="CONTRACT_ID" prompt="HLS_EBANK_CCB_DOCUMENT.CONTRACT_ID"/>
        <bm:field name="contract_number" expression="(select contract_number from con_contract where contract_id = t1.contract_id)" forInsert="false" forUpdate="false"/>
        <bm:field name="cashflow_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="CASHFLOW_ID" prompt="HLS_EBANK_CCB_DOCUMENT.CASHFLOW_ID"/>
        <bm:field name="receivable_amount" databaseType="NUMBER" datatype="java.lang.Double" physicalName="RECEIVABLE_AMOUNT" prompt="HLS_EBANK_CCB_DOCUMENT.RECEIVABLE_AMOUNT"/>
        <bm:field name="real_revenue" databaseType="NUMBER" datatype="java.lang.Double" physicalName="REAL_REVENUE" prompt="HLS_EBANK_CCB_DOCUMENT.REAL_REVENUE"/>
        <bm:field name="currency_code" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="CURRENCY_CODE" prompt="HLS_EBANK_CCB_DOCUMENT.CURRENCY_CODE"/>
        <bm:field name="transaction_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="TRANSACTION_ID" prompt="HLS_EBANK_CCB_DOCUMENT.TRANSACTION_ID"/>
        <bm:field name="customer_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="CUSTOMER_ID" prompt="HLS_EBANK_CCB_DOCUMENT.CUSTOMER_ID"/>
        <bm:field name="customer_name" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="CUSTOMER_NAME" prompt="HLS_EBANK_CCB_DOCUMENT.CUSTOMER_NAME"/>
        <bm:field name="cus_bank_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="CUS_BANK_ID" prompt="HLS_EBANK_CCB_DOCUMENT.CUS_BANK_ID"/>
        <bm:field name="cus_bank_id_n" expression="(NVL((select a.notes FROM csh_to_ebank_area a where a.ebank_id = t1.cus_bank_id AND a.ebank_num = t1.cus_bank_code),(SELECT eb.ebank_name FROM csh_ebank eb WHERE eb.ebank_id=t1.cus_bank_id AND eb.ebank_code = t1.cus_bank_code)))" forInsert="false" forUpdate="false"/>
        <bm:field name="cus_bank_account" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="CUS_BANK_ACCOUNT" prompt="HLS_EBANK_CCB_DOCUMENT.CUS_BANK_ACCOUNT"/>
        <bm:field name="cus_bank_acc_name" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="CUS_BANK_ACC_NAME" prompt="HLS_EBANK_CCB_DOCUMENT.CUS_BANK_ACC_NAME"/>
        <bm:field name="cus_id_phone" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="CUS_ID_PHONE" prompt="HLS_EBANK_CCB_DOCUMENT.CUS_ID_PHONE"/>
        <bm:field name="status" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="STATUS" prompt="HLS_EBANK_CCB_DOCUMENT.STATUS"/>
        <bm:field name="status_desc" databaseType="VARCHAR2" datatype="java.lang.String"/>
        <bm:field name="new_date" databaseType="DATE" datatype="java.util.Date" physicalName="NEW_DATE" prompt="HLS_EBANK_CCB_DOCUMENT.NEW_DATE"/>
        <bm:field name="deal_date" databaseType="DATE" datatype="java.util.Date" physicalName="DEAL_DATE" prompt="HLS_EBANK_CCB_DOCUMENT.DEAL_DATE"/>
        <bm:field name="success_date" databaseType="DATE" datatype="java.util.Date" physicalName="SUCCESS_DATE" prompt="HLS_EBANK_CCB_DOCUMENT.SUCCESS_DATE"/>
        <bm:field name="fail_date" databaseType="DATE" datatype="java.util.Date" physicalName="FAIL_DATE" prompt="HLS_EBANK_CCB_DOCUMENT.FAIL_DATE"/>
        <bm:field name="source_type" databaseType="VARCHAR2" datatype="java.lang.String"/>
        <bm:field name="source_type_desc" databaseType="VARCHAR2" datatype="java.lang.String"/>
        <bm:field name="note" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="NOTE" prompt="HLS_EBANK_CCB_DOCUMENT.NOTE"/>
        <bm:field name="ref_v1" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="REF_V1" prompt="HLS_EBANK_CCB_DOCUMENT.REF_V1"/>
        <bm:field name="ref_v2" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="REF_V2" prompt="HLS_EBANK_CCB_DOCUMENT.REF_V2"/>
        <bm:field name="ref_v3" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="REF_V3" prompt="HLS_EBANK_CCB_DOCUMENT.REF_V3"/>
        <bm:field name="ref_n1" databaseType="NUMBER" datatype="java.lang.Long" physicalName="REF_N1" prompt="HLS_EBANK_CCB_DOCUMENT.REF_N1"/>
        <bm:field name="ref_n2" databaseType="NUMBER" datatype="java.lang.Long" physicalName="REF_N2" prompt="HLS_EBANK_CCB_DOCUMENT.REF_N2"/>
        <bm:field name="ref_n3" databaseType="NUMBER" datatype="java.lang.Long" physicalName="REF_N3" prompt="HLS_EBANK_CCB_DOCUMENT.REF_N3"/>
        <bm:field name="times" databaseType="NUMBER" datatype="java.lang.Long" physicalName="TIMES" prompt="HLS_EBANK_CCB_DOCUMENT.TIMES"/>
        <bm:field name="status_date" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="STATUS_DATE" prompt="HLS_EBANK_CCB_DOCUMENT.STATUS_DATE"/>
        <bm:field name="csh_trans_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="CSH_TRANS_ID" prompt="HLS_EBANK_CCB_DOCUMENT.CSH_TRANS_ID"/>
        <bm:field name="csh_trans_flag" databaseType="VARCHAR2" datatype="java.lang.String"/>
        <bm:field name="csh_trans_flag_desc" databaseType="VARCHAR2" datatype="java.lang.String"/>
        <bm:field name="csh_trans_num" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="CSH_TRANS_NUM" prompt="HLS_EBANK_CCB_DOCUMENT.CSH_TRANS_NUM"/>
        <bm:field name="transaction_number" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="TRANSACTION_NUMBER" prompt="HLS_EBANK_CCB_DOCUMENT.TRANSACTION_NUMBER"/>
        <bm:field name="due_date" databaseType="DATE" datatype="java.util.Date" physicalName="DUE_DATE" prompt="HLS_EBANK_CCB_DOCUMENT.DUE_DATE"/>
        <bm:field name="error_msg" databaseType="VARCHAR2" datatype="java.lang.String"/>
    </bm:fields>
    <bm:features>
        <f:standard-who/>
    </bm:features>
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[
                SELECT
                    *
                FROM
                    (SELECT
                        t1.document_id,
                        t1.document_number,
                        t1.seq_no,
                        t1.company_id,
                        t1.contract_id,
                        (SELECT contract_number FROM con_contract WHERE contract_id = t1.contract_id
                        ) AS contract_number,
                        t1.cashflow_id,
                        (select cf.due_date from con_contract_cashflow cf where cf.cashflow_id = t1.cashflow_id) due_date,
                        t1.receivable_amount,
                        t1.real_revenue,
                        t1.currency_code,
                        t1.transaction_id,
                        (select tx.transaction_number from rd_ebank_spd_trans_txds71 tx where tx.transaction_id = t1.transaction_id) transaction_number,
                        t1.customer_id,
                        t1.customer_name,
                        t1.cus_bank_id,
                        NVL((select a.notes FROM csh_to_ebank_area a where a.ebank_id = t1.cus_bank_id AND a.ebank_num = t1.cus_bank_code),(SELECT eb.ebank_name FROM csh_ebank eb WHERE eb.ebank_id=t1.cus_bank_id AND eb.ebank_code  = t1.cus_bank_code)) AS cus_bank_id_n,
                        t1.cus_bank_account,
                        t1.cus_bank_acc_name,
                        t1.cus_id_phone,
                        t1.status,
                        (SELECT
                            v.code_value_name
                        FROM
                            sys_code_values_v v
                        WHERE
                            v.code                    = 'RD_EBANK_SPD_DOCUMENT_STATUS' AND
                            v.code_enabled_flag       = 'Y' AND
                            v.code_value_enabled_flag = 'Y' AND
                            v.code_value              = t1.status
                        ) status_desc,
                        t1.new_date,
                        t1.deal_date,
                        t1.success_date,
                        t1.fail_date,
                        rd_ebank_spd_doc_pkg.get_document_date(t1.document_id) status_date,
                        t1.source_type,
                        (SELECT
                            i.description
                        FROM
                            hls_cashflow_item i
                        WHERE
                            i.cf_item = t1.source_type
                        ) source_type_desc,
                        t1.note,
                        nvl(t1.error_msg, (select t.return_msg from rd_ebank_spd_trans_txds71 t where t.transaction_id = t1.transaction_id)) error_msg,
                        t1.ref_v1,
                        t1.ref_v2,
                        t1.ref_v3,
                        t1.ref_n1,
                        t1.ref_n2,
                        t1.ref_n3,
                        t1.times,
                        t1.csh_trans_id,
                        t1.csh_trans_flag,
                        (SELECT
                            v.code_value_name
                        FROM
                            sys_code_values_v v
                        WHERE
                            v.code                    = 'YES_NO' AND
                            v.code_enabled_flag       = 'Y' AND
                            v.code_value_enabled_flag = 'Y' AND
                            v.code_value              = t1.csh_trans_flag
                        ) csh_trans_flag_desc,
                        (SELECT
                            t.transaction_num
                        FROM
                            csh_transaction t
                        WHERE
                            t.transaction_id = t1.csh_trans_id
                        ) csh_trans_num
                    FROM
                        rd_ebank_spd_txds71_doc t1
                    )v #WHERE_CLAUSE#
                    order by v.contract_number,v.times
            ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
    <bm:query-fields>
        <bm:query-field name="document_number" queryExpression="v.document_number like ${@document_number}"/>
        <bm:query-field name="contract_number" queryExpression="v.contract_number like ${@contract_number}"/>
        <bm:query-field name="customer_name" queryExpression="v.customer_name like ${@customer_name}"/>
        <bm:query-field name="cus_bank_account" queryExpression="v.cus_bank_account like ${@cus_bank_account}"/>
        <bm:query-field name="status_date_from" queryExpression="to_date(v.status_date, &apos;yyyy-mm-dd hh24-mi-ss&apos;) &gt;= to_date(${@status_date_from}, &apos;yyyy-mm-dd hh24-mi-ss&apos;)"/>
        <bm:query-field name="status_date_to" queryExpression="to_date(v.status_date, &apos;yyyy-mm-dd hh24-mi-ss&apos;) &lt;= to_date(${@status_date_to}, &apos;yyyy-mm-dd hh24:mi:ss&apos;)"/>
        <bm:query-field name="status" queryExpression="(v.status = ${@status})"/>
        <bm:query-field name="csh_trans_flag" queryExpression="(nvl(v.csh_trans_flag,&apos;N&apos;) = ${@csh_trans_flag} or nvl(${@csh_trans_flag},&apos;ALL&apos;) = &apos;ALL&apos;)"/>
        <bm:query-field name="transaction_number_from" queryExpression="${@transaction_number_from} &lt;= v.transaction_number"/>
        <bm:query-field name="transaction_number_to" queryExpression="${@transaction_number_to} &gt;= v.transaction_number"/>
        <!-- <bm:query-field name="cf_item_1_flag" queryExpression="((${@cf_item_1_flag} = &apos;Y&apos; and v.source_type = &apos;1&apos;) or (${@cf_item_1_flag} = &apos;N&apos; and v.source_type != &apos;1&apos;))"/>
        <bm:query-field name="cf_item_9_flag" queryExpression="((${@cf_item_9_flag} = &apos;Y&apos; and v.source_type = &apos;9&apos;) or (${@cf_item_9_flag} = &apos;N&apos; and v.source_type != &apos;9&apos;))"/> -->
        <bm:query-field name="cf_item_flag" queryExpression="(nvl(${@cf_item_flag}, &apos;N&apos;) = &apos;Y&apos; and (v.source_type in (decode(${@cf_item_1_flag}, &apos;Y&apos;, 1, -1), nvl(${@cf_type_x1}, -1), nvl(${@cf_type_x2}, -1), nvl(${@cf_type_x3}, -1), decode(${@cf_item_9_flag}, &apos;Y&apos;, 9, -1))))"/>
    </bm:query-fields>
</bm:model>
