<?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,
               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
          FROM fnd_interface_headers hd,
               fnd_interface_lines   li
         WHERE hd.header_id = li.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
           csh_transaction_pkg.save_receipt_import(
            			  p_transaction_id            =>${@transaction_id},
			              p_transaction_num           =>${@transaction_num},
			              p_transaction_category      =>'BUSINESS',
			              p_transaction_type          =>'RECEIPT',
			              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}
            	       );
        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>
