<?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 * from (
                SELECT
                    cc.contract_id,
                    cc.calc_session_id,
                    cc.contract_number,
                    cc.contract_name,
                    cc.bp_id_tenant,
                    (SELECT hbm.bp_name FROM hls_bp_master hbm WHERE hbm.bp_id = cc.bp_id_tenant
                    ) bp_name,
                    cc.employee_id,
                    (SELECT ee.name FROM exp_employees ee WHERE ee.employee_id = cc.employee_id
                    ) employee_name
                    ,
                    cc.hn_industry_classification,
       hn_con_unearned_fin_income_pkg.get_internal_period_income(p_contract_id          => cc.contract_id,
                                                                 p_internal_period_from => ${@calc_year}||'01',
                                                                 p_internal_period_to   => ${@calc_year}||'01') month_1_income,
       hn_con_unearned_fin_income_pkg.get_internal_period_income(p_contract_id          => cc.contract_id,
                                                                 p_internal_period_from => ${@calc_year}||'02',
                                                                 p_internal_period_to   => ${@calc_year}||'02') month_2_income,
       hn_con_unearned_fin_income_pkg.get_internal_period_income(p_contract_id          => cc.contract_id,
                                                                 p_internal_period_from => ${@calc_year}||'03',
                                                                 p_internal_period_to   => ${@calc_year}||'03') month_3_income,
       hn_con_unearned_fin_income_pkg.get_internal_period_income(p_contract_id          => cc.contract_id,
                                                                 p_internal_period_from => ${@calc_year}||'04',
                                                                 p_internal_period_to   => ${@calc_year}||'04') month_4_income,
       hn_con_unearned_fin_income_pkg.get_internal_period_income(p_contract_id          => cc.contract_id,
                                                                 p_internal_period_from => ${@calc_year}||'05',
                                                                 p_internal_period_to   => ${@calc_year}||'05') month_5_income,
       hn_con_unearned_fin_income_pkg.get_internal_period_income(p_contract_id          => cc.contract_id,
                                                                 p_internal_period_from => ${@calc_year}||'06',
                                                                 p_internal_period_to   => ${@calc_year}||'06') month_6_income,
       hn_con_unearned_fin_income_pkg.get_internal_period_income(p_contract_id          => cc.contract_id,
                                                                 p_internal_period_from => ${@calc_year}||'07',
                                                                 p_internal_period_to   => ${@calc_year}||'07') month_7_income,
       hn_con_unearned_fin_income_pkg.get_internal_period_income(p_contract_id          => cc.contract_id,
                                                                 p_internal_period_from => ${@calc_year}||'08',
                                                                 p_internal_period_to   => ${@calc_year}||'08') month_8_income,
       hn_con_unearned_fin_income_pkg.get_internal_period_income(p_contract_id          => cc.contract_id,
                                                                 p_internal_period_from => ${@calc_year}||'09',
                                                                 p_internal_period_to   => ${@calc_year}||'09') month_9_income,
       hn_con_unearned_fin_income_pkg.get_internal_period_income(p_contract_id          => cc.contract_id,
                                                                 p_internal_period_from => ${@calc_year}||'10',
                                                                 p_internal_period_to   => ${@calc_year}||'10') month_10_income,
       hn_con_unearned_fin_income_pkg.get_internal_period_income(p_contract_id          => cc.contract_id,
                                                                 p_internal_period_from => ${@calc_year}||'11',
                                                                 p_internal_period_to   => ${@calc_year}||'11') month_11_income,
       hn_con_unearned_fin_income_pkg.get_internal_period_income(p_contract_id          => cc.contract_id,
                                                                 p_internal_period_from => ${@calc_year}||'12',
                                                                 p_internal_period_to   => ${@calc_year}||'12') month_12_income,
       hn_con_unearned_fin_income_pkg.get_internal_period_income(p_contract_id          => cc.contract_id,
                                                                 p_internal_period_from => ${@calc_year}||'01',
                                                                 p_internal_period_to   => ${@calc_year}||'12') year_income
                FROM
                    con_contract cc
                WHERE
                    (cc.data_class            = 'NORMAL' or (${@test_flag} = 'Y' and cc.data_class = 'CHANGE_REQ')) AND
                    cc.hn_adjust_review_flag = 'Y' AND
                    cc.contract_status      <> 'CANCEL') t1
                    #WHERE_CLAUSE#
                ORDER BY
                    t1.contract_number
            ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
    <bm:query-fields>
        <bm:query-field name="contract_number" queryExpression="t1.contract_number like &apos;%&apos;||${@contract_number}||&apos;%&apos;"/>
        <bm:query-field name="contract_name" queryExpression="t1.contract_name like &apos;%&apos;||${@contract_name}||&apos;%&apos;"/>
        <bm:query-field name="bp_name" queryExpression="t1.bp_name like &apos;%&apos;||${@bp_name}||&apos;%&apos;"/>
        <bm:query-field name="hn_industry_classification_desc" queryExpression="t1.hn_industry_classification=${@hn_industry_classification}"/>
        <bm:query-field name="contract_id" queryExpression="t1.contract_id=${@contract_id}"/>
    </bm:query-fields>
</bm:model>
