<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: chenlingfeng
    $Date: 2019-01-14 16:29:48
    $Revision: 1.0
    $Purpose:
-->
<bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm">
    <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_transaction_pkg.save_receipt_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_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_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>
