<?xml version="1.0" encoding="UTF-8"?>

<bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm" needAccessControl="false">
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[
                SELECT t1.*
                FROM (SELECT li.line_id,
                li.error_message,
                li.status,
                li.header_id,
                li.line_number,
                hd.created_by,
                li.source_table,
                '03399500040009165' bank_num,    --update by xsh35973
                '人民币' currency_type,           --update by xsh35973
                li.attribute_1,
                li.attribute_2,
                li.attribute_3,
                li.attribute_4,
                li.attribute_5,
                li.attribute_6,
                li.attribute_7,
                li.attribute_8,
                li.attribute_9,
                li.attribute_10,
                li.attribute_11,
                li.attribute_12,
                li.attribute_13,
                li.attribute_14,
                li.attribute_15,
                li.attribute_16,
                li.attribute_17,
                li.attribute_18,
                li.attribute_19,
                li.attribute_20,
                li.attribute_21,
                li.attribute_22,
                li.attribute_23,
                li.attribute_24,
                li.attribute_25,
                li.attribute_26,
                t.csh_bank_account_num,
                t.transaction_date,
                t.bank_slip_num,
                t.bp_bank_account_num,
                t.bp_bank_account_name,
                t.bp_bank_name,
                t.bp_category,
                t.transaction_amount,
                t.collection_classes,
                (select v.code_value_name from sys_code_values_v v where v.code='DS_COLLECTION_CLASSES'and v.code_value=t.collection_classes)collection_classes_desc,
                t.bp_id ,
                (select bp_name from hls_bp_master where bp_id = t.bp_id) bp_name,
                t.paid_byother_flag,
                decode(t.paid_byother_flag,'T','是','F','否')paid_byother_flag_desc,
                t.csh_bank_account_id bank_account_id,
                t.exists_flag,
                t.temp_id,
                (
                case when
                (select count(v.unit_id) from hls_bp_master v where v.enabled_flag = 'Y' and v.bp_id = t.bp_id) = 1
                then
                (Select u.unit_name From exp_org_unit_v u Where u.unit_id = (select v.unit_id from hls_bp_master v where v.enabled_flag = 'Y' and v.bp_id = t.bp_id))
                else
                ' '
                end
                ) extra_nam,
                (Select u.bp_code From hls_bp_master u Where u.unit_id = (select v.unit_id from hls_bp_master v where v.enabled_flag = 'Y' and v.bp_id = t.bp_id) and u.bp_type = 'AGENT')  bp_code,
                t.guarantor_name_duty,
                decode(t.guarantor_name_duty,'Y','是','N','否') guarantor_name_duty_desc
                FROM fnd_interface_headers hd,
                fnd_interface_lines   li,
                csh_transaction_import_temp t
                WHERE hd.header_id = li.header_id and li.line_id = t.line_id and hd.header_id = t.header_id
                AND hd.template_code = 'CSH_TRX_BANK'
                AND li.line_number >0
                AND HD.CREATED_BY = ${/session/@user_id}
                ) t1
                #WHERE_CLAUSE#
                order by t1.line_number

                ]]></bm:query-sql>
        </bm:operation>
        <bm:operation name="update">
            <bm:update-sql><![CDATA[
                begin
                csh_transaction_pkg.csh_transaction_import_check(
                p_header_id => ${/model/header/record/@header_id},
                p_user_id => ${/session/@user_id}
                );
                end;
                ]]></bm:update-sql>
        </bm:operation>
        <bm:operation name="insert">
            <bm:update-sql><![CDATA[
                begin
                delete from csh_transaction_import_temp where header_id = ${@header_id};
                csh_abc_import_pkg.save_abc_import(
                p_transaction_id =>${@transaction_id},
                p_transaction_num =>${@transaction_num},
                p_transaction_category =>'BUSINESS',
                p_transaction_type =>${@transaction_type},
                p_transaction_date =>to_date(${@transaction_date}, 'yyyy-mm-dd'),
                p_penalty_calc_date =>to_date(${@transaction_date}, 'yyyy-mm-dd'),
                p_bank_slip_num =>${@bank_slip_num},
                p_company_id =>${/session/@company_id},
                p_internal_period_num =>${@internal_period_num},
                p_period_name =>${@period_name},
                p_payment_method_id =>${@payment_method_id},
                p_distribution_set_id =>${@distribution_set_id},
                p_cashflow_amount =>${@transaction_amount},
                p_currency_code =>${@currency_code},
                p_transaction_amount =>${@transaction_amount},
                p_exchange_rate_type =>${@exchange_rate_type},
                p_exchange_rate_quotation =>${@exchange_rate_quotation},
                p_exchange_rate =>${@exchange_rate},
                p_bank_account_id =>${@bank_account_id},
                p_bp_category =>${@bp_category},
                p_bp_id =>${@bp_id},
                p_bp_bank_account_id =>${@bp_bank_account_id},
                p_bp_bank_account_num =>${@bp_bank_account_num},
                p_description =>${@description},
                p_handling_charge =>${@handling_charge},
                p_posted_flag =>nvl(${@posted_flag}, 'N'),
                p_reversed_flag =>nvl(${@reversed_flag}, 'N'),
                p_reversed_date =>to_date(${@reversed_date}, 'yyyy-mm-dd 24hh:mi:ss'),
                p_returned_flag =>nvl(${@returned_flag}, 'NOT'),
                p_returned_amount =>${@returned_amount},
                p_write_off_flag =>nvl(${@write_off_flag}, 'NOT'),
                p_write_off_amount =>${@write_off_amount},
                p_full_write_off_date =>to_date(${@full_write_off_date}, 'yyyy-mm-dd 24hh:mi:ss'),
                p_twin_csh_trx_id =>${@twin_csh_trx_id},
                p_return_from_csh_trx_id =>${@return_from_csh_trx_id},
                p_reversed_csh_trx_id =>${@reversed_csh_trx_id},
                p_source_csh_trx_type =>${@source_csh_trx_type},
                p_source_csh_trx_id =>${@source_csh_trx_id},
                p_source_doc_category =>${@source_doc_category},
                p_source_doc_type =>${@source_doc_type},
                p_source_doc_id =>${@source_doc_id},
                p_source_doc_line_id =>${@source_doc_line_id},
                p_create_je_mothed =>${@create_je_mothed},
                p_create_je_flag =>nvl(${@create_je_flag}, 'N'),
                p_gld_interface_flag =>nvl(${@gld_interface_flag}, 'N'),
                p_user_id =>${/session/@user_id},
                p_ref_contract_id => ${@ref_contract_id},
                p_receipt_type => ${@receipt_type},
                p_csh_bp_name => ${@csh_bp_name},
                p_bp_name => ${@bp_name},
                p_ref_n01 =>${@ref_n01},
                p_bp_bank_name =>${@bp_bank_name},
                p_bp_bank_branch_name =>${@bp_bank_branch_name},
                p_collection_classes =>${@collection_classes},
                p_bp_bank_account_name =>${@bp_bank_account_name},
                p_opposite_band_na =>${@opposite_band_na},
                p_banka =>${@banka},
                p_purpose =>${@purpose},
                p_gsber =>${@gsber},
                p_sus_belnr =>${@sus_belnr},
                p_bp_virtual_bank_account =>${@bp_virtual_bank_account},
                p_zuonr =>${@bank_slip_num},
                p_paid_byother_flag =>${@paid_byother_flag},
                p_ref_v05 =>${@ref_v05},
                p_status =>${@status},
                p_bank_account_num =>${@bank_account_num},
                p_guarantor_name_duty =>${@guarantor_name_duty},
                p_bank_receipt_date  =>${@bank_receipt_date},
                p_paid_byother_flag_desc  =>${@paid_byother_flag_desc},
                p_collection_classes_desc  =>${@collection_classes_desc},
                p_extra_nam  =>${@extra_nam},
                p_import_source => 'EXCEL_ADD'


                );
                end;
                ]]></bm:update-sql>
        </bm:operation>
        <bm:operation name="execute">
            <bm:update-sql><![CDATA[
                begin
                csh_transaction_pkg.get_bank_account_id(p_bank_account_num =>${@bank_account_num},
                p_bp_bank_account_num =>${@bp_bank_account_num},
                p_bank_account_id =>${@bank_account_id},
                p_bp_category =>${@bp_category},
                p_bp_id =>${@bp_id},
                p_csh_bp_name =>${@csh_bp_name},
                p_user_id =>${session/@user_id}
                );
                end;
                ]]></bm:update-sql>
            <bm:parameters>
                <bm:parameter name="bank_account_id" dataType="java.lang.Long" output="true"
                              outputPath="@bank_account_id"/>
                <bm:parameter name="bp_category" dataType="java.lang.String" output="true" outputPath="@bp_category"/>
                <bm:parameter name="bp_id" dataType="java.lang.Long" output="true" outputPath="@bp_id"/>
                <bm:parameter name="csh_bp_name" dataType="java.lang.String" output="true" outputPath="@csh_bp_name"/>
            </bm:parameters>
        </bm:operation>
    </bm:operations>


    <bm:query-fields>
        <bm:query-field name="header_id" queryExpression="t1.header_id=${@header_id}"/>
    </bm:query-fields>
</bm:model>
