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

<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,
                (select cc.contract_name
                from con_contract cc
                where cc.contract_number = li.attribute_1) contract_name,
                li.attribute_2,
                li.attribute_3,
                li.attribute_4,
                li.attribute_5,
                (select cc.lease_start_date
                from con_contract cc
                where cc.contract_number = li.attribute_1) lease_start_date,
                li.attribute_6,
                li.attribute_7,
                trim(to_char(round(li.attribute_8,1),'999999999999990.99')) attribute_8,
                trim(to_char(round(li.attribute_8 /
                to_char(last_day(sysdate), 'dd'),
                1),
                '999999999999990.99')) average_hour,
                li.attribute_9,
                li.attribute_10,
                li.attribute_11,
                li.attribute_12
                FROM fnd_interface_headers hd, fnd_interface_lines li
                WHERE hd.header_id = li.header_id
                AND hd.template_code = 'HLS_LEASE_ITEM_GPS_IMPORT'
                AND li.line_number > 0
                AND li.source_table = 'GPS'
                AND HD.CREATED_BY = ${/session/@user_id}
                AND hd.header_id=${/parameter/@header_id}) t1
                order by t1.line_number
                ]]>
            </bm:query-sql>
        </bm:operation>
        <bm:operation name="update">
            <bm:update-sql><![CDATA[
                begin
                hls_lease_item_gps_import_pkg.gps_import_check(p_header_id => ${@header_id},
                p_user_id => ${/session/@user_id});
                end;
                ]]>
            </bm:update-sql>
        </bm:operation>
        <bm:operation name="insert">
            <bm:update-sql><![CDATA[
                BEGIN
                hls_lease_item_gps_import_pkg.gps_import(
                p_contract_number    =>  ${@contract_number},
                p_machine_model      =>  ${@machine_model},
                p_machine_number     =>  ${@machine_number},
                p_interval_date      =>  ${@interval_date},
                p_working_hours_latest=> ${@working_hours_latest},
                p_working_hours_new_m => ${@working_hours_new_m},
                p_working_hours_ave_m => ${@working_hours_ave_m},
                p_machine_location   =>  ${@machine_location},
                p_note               =>  ${@note},
                p_attention          =>  ${@attention},
                p_machine_number_full=> ${@machine_number_full},
                p_user_id            =>  ${/session/@user_id});
                END;
                ]]>
            </bm:update-sql>
        </bm:operation>
    </bm:operations>
</bm:model>
