<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: syj $
    $Date: 2019年1月16日17:10:09 $
    $Revision: 1.1 $
    $Purpose:
-->
<bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm" needAccessControl="false">
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[
           select * from (  SELECT
                   c.contract_id,
                   c.contract_number,
                   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
                        ) AS contract_status_n,
                   c.business_type,
                   (SELECT hbt.description
                      FROM hls_business_type hbt
                     WHERE hbt.business_type = c.business_type) business_type_n,
                   c.division,
                   (SELECT d.description
                      FROM hls_division d
                     WHERE d.division = c.division) division_n,
                   to_char(c.lease_execution_date,'yyyy-mm-dd')lease_execution_date,
                   to_char(c.lease_end_date,'yyyy-mm-dd')lease_end_date,
                   (SELECT m.bp_name FROM hls_bp_master m WHERE m.bp_id = c.bp_id_tenant) bp_name,

                   (nvl((SELECT decode(c.hd_user_col_v05, 'Y', 0, 1) *
                        SUM(nvl(cc.due_amount,0))
                   FROM con_contract_cashflow cc
                  WHERE cc.contract_id = c.contract_id
                    AND cc.cf_item = 3
                    AND cc.cf_status = 'RELEASE'),0) +
                 nvl((SELECT SUM(nvl(cc.due_amount,0))
                       FROM con_contract_cashflow cc
                      WHERE cc.contract_id = c.contract_id
                        AND cc.cf_item = 301
                        AND cc.cf_status = 'RELEASE'),0)) as lease_charge,

                  decode(c.contract_status,
                        'INCEPT',
                        i.unearned_finance_income,
                        (SELECT SUM(ii.unearned_finance_income)
                           FROM con_unearned_finance_income ii
                          WHERE ii.contract_id = c.contract_id
                            AND ii.internal_period_num >= ${@internal_period_num}
                            AND ii.cf_item = 3)) AS net_unearned_finance_income,

                 round((decode(c.contract_status,
                               'INCEPT',
                               i.unearned_finance_income,
                               (SELECT SUM(ii.unearned_finance_income)
                                  FROM con_unearned_finance_income ii
                                 WHERE ii.contract_id = c.contract_id
                                   AND ii.internal_period_num >= ${@internal_period_num}
                                   AND ii.cf_item = 3)) *
                       (SELECT CASE
                                WHEN t.inception_of_lease >=
                                     to_date('20190401', 'yyyymmdd') THEN
                                 0.13
                                WHEN t.inception_of_lease >=
                                     to_date('20180501', 'yyyymmdd') AND
                                     t.inception_of_lease <
                                     to_date('20190401', 'yyyymmdd') THEN
                                 0.16
                                ELSE
                                 0.17
                              END
                           FROM con_contract t
                          WHERE t.contract_id = i.contract_id)),
                       2) AS vat_unearned_finance_income,

                 round(((decode(c.contract_status,
                                'INCEPT',
                                i.unearned_finance_income,
                                (SELECT SUM(ii.unearned_finance_income)
                                   FROM con_unearned_finance_income ii
                                  WHERE ii.contract_id = c.contract_id
                                    AND ii.internal_period_num >= ${@internal_period_num}
                                    AND ii.cf_item = 3)) *
                       (SELECT CASE
                                WHEN t.inception_of_lease >=
                                     to_date('20190401', 'yyyymmdd') THEN
                                 0.13
                                WHEN t.inception_of_lease >=
                                     to_date('20180501', 'yyyymmdd') AND
                                     t.inception_of_lease <
                                     to_date('20190401', 'yyyymmdd') THEN
                                 0.16
                                ELSE
                                 0.17
                              END
                            FROM con_contract t
                           WHERE t.contract_id = i.contract_id)) +
                       decode(c.contract_status,
                               'INCEPT',
                               i.unearned_finance_income,
                               (SELECT SUM(ii.unearned_finance_income)
                                  FROM con_unearned_finance_income ii
                                 WHERE ii.contract_id = c.contract_id
                                   AND ii.internal_period_num >= ${@internal_period_num}
                                   AND ii.cf_item = 3))),
                       2) AS unearned_finance_income,

                   nvl((SELECT SUM(nvl(ii.unearned_finance_income,0))
                                  FROM con_unearned_finance_income ii
                                 WHERE ii.contract_id = c.contract_id
                                   AND ii.internal_period_num <= ${@internal_period_num}
                                   AND ii.cf_item = 3),0)total_finance_income,

                   nvl((SELECT SUM(nvl(ii.unearned_finance_income,0))
                                  FROM con_unearned_finance_income ii
                                 WHERE ii.contract_id = c.contract_id
                                   AND ii.internal_period_num > ${@internal_period_num}
                                   AND ii.cf_item = 3),0)surplus_finance_income


              FROM con_unearned_finance_income i, con_contract c
             WHERE i.contract_id = c.contract_id
               AND i.internal_period_num = ${@internal_period_num}
               AND c.data_class = 'NORMAL'
               AND (c.contract_status = 'INCEPT' OR
               (c.contract_status = 'ET' AND EXISTS
                (SELECT 1
                    FROM con_contract_et_hd h
                   WHERE h.contract_id = c.contract_id
                     AND h.status = 'APPROVED'
                     AND to_char(h.termination_date, 'yyyymm') = ${@internal_period_num})
                ) OR (c.contract_status = 'TERMINATE' AND to_char(c.et_date, 'yyyymm') = ${@internal_period_num} ) )
               AND i.cf_item =3)t
              #WHERE_CLAUSE#

            ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
    <bm:query-fields>
        <bm:query-field name="contract_number" queryExpression="t.contract_number = ${@contract_number}"/>
    </bm:query-fields>
</bm:model>
