<?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
                        hvccr.change_req_id,
                        hvccr.project_id,
                        pp.virtual_con_number,
                        pp.virtual_con_name,
                        pp.bp_id_tenant,
                        hbm.bp_name bp_id_tenant_n,
                        pp.hn_industry_classification,
                        (SELECT
                            v.code_value_name
                        FROM
                            sys_code_values_v v
                        WHERE
                            v.code       = 'HN_INDUSTRY_CLASSIFICATION' AND
                            v.code_value = pp.hn_industry_classification
                        ) hn_industry_classification_n,
                        pp.employee_id,
                        (SELECT ee.name FROM exp_employees ee WHERE ee.employee_id = pp.employee_id
                        ) employee_id_n,
                        pp.lease_organization,
                        (SELECT
                            hlo.description
                        FROM
                            hls_lease_organization hlo
                        WHERE
                            hlo.lease_organization = pp.lease_organization
                        ) lease_organization_n,
                        hvccr.seal_type,
                        hvccr.seal_number,
                        hvccr.status,
                        (SELECT
                            v.code_value_name
                        FROM
                            sys_code_values_v v
                        WHERE
                            v.code       = 'HN_ATM_COMPLEMENT_STATUS' AND
                            v.code_value = hvccr.status
                        ) status_n,
                        hvccr.req_date,
                        pp.cdd_list_id,
                        (SELECT
                            dt.ref_document_type
                        FROM
                            hls_document_type dt
                        WHERE
                            dt.document_type = pp.document_type
                        ) con_document_type,
                        (SELECT
                            dt1.description
                        FROM
                            hls_document_type dt,
                            hls_document_type dt1
                        WHERE
                            dt.document_type     = pp.document_type AND
                            dt.ref_document_type = dt1.document_type
                        ) con_document_type_n,
                        pp.finance_amount,
                        pp.division,
                        (SELECT hd.description FROM hls_division hd WHERE hd.division = pp.division
                        ) division_n,
                        pp.employee_id_of_manager,
                        (SELECT
                            ee.name
                        FROM
                            exp_employees ee
                        WHERE
                            ee.employee_id = pp.employee_id_of_manager
                        ) employee_id_of_manager_n
                    FROM
                        hn_virtual_con_change_req hvccr,
                        prj_project pp,
                        hls_bp_master hbm
                    WHERE
                        hvccr.project_id = pp.project_id AND
                        pp.bp_id_tenant  = hbm.bp_id
                    ) t1 #WHERE_CLAUSE#
            ]]></bm:query-sql>
        </bm:operation>
        <bm:operation name="insert">
            <bm:update-sql><![CDATA[
            	BEGIN
            	    ${@change_req_id}:=hn_virtual_contract_pkg.create_virtual_con_change_req(p_project_id =>${@project_id}, 
            	                                                                            p_req_date =>to_date(${@req_date},'YYYY-MM-DD'), 
            	                                                                            p_user_id =>${/session/@user_id});
            	END;
        	]]></bm:update-sql>
            <bm:parameters>
                <bm:parameter name="change_req_id" output="true" outputPath="@change_req_id"/>
            </bm:parameters>
        </bm:operation>
        <bm:operation name="update">
            <bm:update-sql><![CDATA[
            	BEGIN
            	   hn_virtual_contract_pkg.update_virtual_con_change_req(p_change_req_id =>${@change_req_id}, 
            	                                                        p_seal_type =>${@seal_type},
            	                                                        p_seal_number =>${@seal_number},
            	                                                        p_user_id =>${/session/@user_id});
            	END;
        	]]></bm:update-sql>
        </bm:operation>
        <bm:operation name="execute">
            <bm:update-sql><![CDATA[
            	BEGIN
            	   hn_virtual_contract_pkg.submit_virtual_con_change_req(p_change_req_id =>${@change_req_id},
                                          p_company_id    =>${/session/@company_id},
                                          p_user_id       =>${/session/@user_id});
            	END;
        	]]></bm:update-sql>
        </bm:operation>
    </bm:operations>
    <bm:query-fields>
        <bm:query-field name="virtual_con_number" queryExpression="t1.virtual_con_number like &apos;%&apos;||${@virtual_con_number}||&apos;%&apos;"/>
        <bm:query-field name="virtual_con_name" queryExpression="t1.virtual_con_name like &apos;%&apos;||${@virtual_con_name}||&apos;%&apos;"/>
        <bm:query-field name="bp_id_tenant_n" queryExpression="t1.bp_id_tenant_n like &apos;%&apos;||${@bp_id_tenant_n}||&apos;%&apos;"/>
        <bm:query-field name="hn_industry_classification" queryExpression="t1.hn_industry_classification=${@hn_industry_classification}"/>
        <bm:query-field name="employee_id" queryExpression="t1.employee_id=${@employee_id}"/>
        <bm:query-field name="lease_organization" queryExpression="t1.lease_organization=${@lease_organization}"/>
        <bm:query-field name="status" queryExpression="t1.status=${@status}"/>
    </bm:query-fields>
</bm:model>
