<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: syj $
    $Date: 2018年11月7日15:38:12 $
    $Revision: 1.1 $
    $Purpose: 印花税
-->


<bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm" needAccessControl="false">
    <bm:operations>
        <bm:operation name="execute">
            <bm:parameters>
                <bm:parameter name="return_value" dataType="java.lang.String" input="false" inputPath="@return_value"
                              output="true" outputPath="/parameter/@return_value"/>
                <bm:parameter name="company_id" dataType="java.lang.Long" input="true" output="false"/>
                <bm:parameter name="internal_period_num" dataType="java.lang.Long" input="true" output="false"/>
                <bm:parameter name="user_id" dataType="java.lang.Long" input="true" output="false"/>
            </bm:parameters>
            <bm:update-sql><![CDATA[
            	declare
 					v_return_value varchar2(30);
                begin
                    ${@return_value} := hls_monthend_pkg.stamp_duty_je
                    (
                        p_company_id=>${/session/@company_id},
                        p_internal_period_num=>${@internal_period_num},
                        p_user_id=>${/session/@user_id}
                    );
                end;]]></bm:update-sql>
        </bm:operation>
        <bm:operation name="update">
            <bm:parameters>
                <bm:parameter name="return_value" dataType="java.lang.String" input="false" inputPath="@return_value"
                              output="true" outputPath="/parameter/@return_value"/>
                <bm:parameter name="company_id" dataType="java.lang.Long" input="true" output="false"/>
                <bm:parameter name="internal_period_num" dataType="java.lang.Long" input="true" output="false"/>
                <bm:parameter name="user_id" dataType="java.lang.Long" input="true" output="false"/>
            </bm:parameters>
            <bm:update-sql><![CDATA[
            	declare
 					v_return_value varchar2(30);
                begin
                    ${@return_value} := hls_monthend_pkg.stamp_duty_tre_je
                    (
                        p_company_id=>${/session/@company_id},
                        p_internal_period_num=>${@internal_period_num},
                        p_user_id=>${/session/@user_id}
                    );
                end;]]></bm:update-sql>
        </bm:operation>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[

            SELECT rownum,
                   ${@internal_period_num} AS internal_period_num,
                   (SELECT t.description
                      FROM hls_business_type t
                     WHERE t.business_type = c.business_type) business_type_desc,
                   (SELECT m.bp_name
                      FROM hls_bp_master m
                     WHERE m.bp_id = c.bp_id_agent_level1) bp_agent,
                   (SELECT m.bp_name FROM hls_bp_master m WHERE m.bp_id = c.bp_id_tenant) bp_tenant,
                   c.contract_id,
                   c.contract_number,
                   c.contract_name,
                   c.contract_status,
                   (SELECT v.code_value_name
                      FROM sys_code_values_v v
                     WHERE v.code = 'CON500_CONTRACT_STATUS'
                       AND v.code_value = c.contract_status) contract_status_desc,
                   to_char(c.lease_execution_date, 'yyyy-mm-dd') lease_start_date,
                   c.lease_term_month,

                   to_char((SELECT wm_concat(i.full_name)
                             FROM con_contract_lease_item i
                            WHERE i.contract_id = c.contract_id)) AS lease_item_name,
                   to_char((SELECT wm_concat(i.pattern)
                             FROM con_contract_lease_item i
                            WHERE i.contract_id = c.contract_id)) AS pattern,

                   to_char((SELECT wm_concat(i.machine_number)
                             FROM con_contract_lease_item i
                            WHERE i.contract_id = c.contract_id)) AS machine_number,

                   to_char((SELECT wm_concat(i.serial_number)
                             FROM con_contract_lease_item i
                            WHERE i.contract_id = c.contract_id)) AS serial_number,
                   c.lease_item_amount,
                   (c.finance_amount + c.total_interest) finance_amount,

                   decode(c.business_type,
                          'LEASEBACK',
                          0,
                          round(c.lease_item_amount * 0.03 / 100, 2)) stamp_duty_1,
                   '0.03%' stamp_duty_rate_1,
                   round((c.finance_amount + c.total_interest) * 0.005 / 100, 2) stamp_duty_2,
                   '0.005%' stamp_duty_rate_2,
                   decode(c.business_type,
                          'LEASEBACK',
                          0,
                          round(c.lease_item_amount * 0.03 / 100, 2)) +
                   round((c.finance_amount + c.total_interest) * 0.005 / 100, 2) stamp_duty_total

              FROM con_contract c
             WHERE --nvl(c.stamp_duty_je_flag, 'N') = 'N' AND
                c.contract_status IN ('INCEPT',
                                         'TERMINATE',
                                         'ET',
                                         'AGENT_TERMINATE',
                                         'LOSSSHARE_TERMINATE',
                                         'VIRTUAL_TERMINATE')
               AND c.data_class = 'NORMAL'
               AND to_char(c.lease_execution_date, 'yyyymm') = ${@internal_period_num}

             UNION

             SELECT rownum,
                   ${@internal_period_num} AS internal_period_num,
                   (SELECT t.description
                      FROM hls_business_type t
                     WHERE t.business_type = c.business_type) business_type_desc,
                   (SELECT m.bp_name
                      FROM hls_bp_master m
                     WHERE m.bp_id = c.bp_id_agent_level1) bp_agent,
                   (SELECT m.bp_name FROM hls_bp_master m WHERE m.bp_id = c.bp_id_tenant) bp_tenant,
                   c.contract_id,
                   c.contract_number,
                   c.contract_name,
                   c.contract_status,
                   (SELECT v.code_value_name
                      FROM sys_code_values_v v
                     WHERE v.code = 'CON500_CONTRACT_STATUS'
                       AND v.code_value = c.contract_status) contract_status_desc,
                   to_char(c.lease_execution_date, 'yyyy-mm-dd') lease_start_date,
                   c.lease_term_month,

                   to_char((SELECT wm_concat(i.full_name)
                             FROM con_contract_lease_item i
                            WHERE i.contract_id = c.contract_id)) AS lease_item_name,
                   to_char((SELECT wm_concat(i.pattern)
                             FROM con_contract_lease_item i
                            WHERE i.contract_id = c.contract_id)) AS pattern,

                   to_char((SELECT wm_concat(i.machine_number)
                             FROM con_contract_lease_item i
                            WHERE i.contract_id = c.contract_id)) AS machine_number,

                   to_char((SELECT wm_concat(i.serial_number)
                             FROM con_contract_lease_item i
                            WHERE i.contract_id = c.contract_id)) AS serial_number,
                   c.lease_item_amount,
                   (c.finance_amount + c.total_interest) finance_amount,

                   -1 *decode(c.business_type,
                          'LEASEBACK',
                          0,
                          round(c.lease_item_amount * 0.03 / 100, 2)) stamp_duty_1,
                   '0.03%' stamp_duty_rate_1,
                   -1 *round((c.finance_amount + c.total_interest) * 0.005 / 100, 2) stamp_duty_2,
                   '0.005%' stamp_duty_rate_2,
                   -1 *decode(c.business_type,
                          'LEASEBACK',
                          0,
                          round(c.lease_item_amount * 0.03 / 100, 2)) -
                   round((c.finance_amount + c.total_interest) * 0.005 / 100, 2) stamp_duty_total

              FROM con_contract c
             WHERE --nvl(c.stamp_duty_je_flag, 'N') = 'N' AND
               c.contract_status IN ('CANCEL')
               AND c.data_class = 'NORMAL'
               and to_char(c.cancel_date, 'yyyymm') = ${@internal_period_num}
               AND to_char(c.lease_execution_date, 'yyyymm') < ${@internal_period_num}

            ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
</bm:model>
