contract_settlement.lwm 6.73 KB
Newer Older
Spencer Chang's avatar
Spencer Chang committed
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133
<?xml version="1.0" encoding="UTF-8"?>
<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 t.contract_id,
                           t.contract_number,
                           t.contract_name,
                           b.bp_code,
                           b.bp_name,
                           (SELECT
                                hls.bp_code
                           FROM
                                hls_bp_master hls
                           WHERE
                                hls.bp_id=t.bp_id_agent_level1
                           )bp_agent_code,
                           (SELECT
                                hls.bp_name
                           FROM
                                hls_bp_master hls
                           WHERE
                                hls.bp_id=t.bp_id_agent_level1
                           )bp_agent,
                           (SELECT
                                h8.description
                           FROM
                                hls_division h8
                           WHERE
                                h8.enabled_flag = 'Y' AND
                                p.division      = h8.division
                           ) division_n,
                            t.lease_execution_date,
                            (SELECT
                                sc.code_value_name
                            FROM
                                sys_code_values_v sc
                            WHERE
                                sc.code = 'CON500_CONTRACT_STATUS'
                            AND
                                t.contract_status = sc.code_value
                            ) contract_status_n,
                            t.lease_channel,
                            (SELECT hl.description
                                FROM hls_lease_channel hl
                                where hl.enabled_flag = 'Y'
                                and hl.lease_channel = t.lease_channel) lease_channel_n,
                            t.contract_status,
                            t.et_date,
                            t.loss_sharing_flag,
                            (select  v.code_value_name as value_name
                              from sys_code_values_v v
                             where v.code = 'YES_NO'
                               and v.code_enabled_flag = 'Y'
                               and v.code_value_enabled_flag = 'Y'
                               and v.code_value =t.loss_sharing_flag
                               ) loss_sharing_flag_n,
                               t.guarantor_info
                    FROM
                          hls_bp_master b,
                          con_contract t,
                          prj_project p
                    WHERE
                          t.bp_id_tenant = b.bp_id(+)
                    AND
                          t.project_id   = p.project_id(+)
                    AND
                          (SELECT
                                sc.code_value
                          FROM
                                sys_code_values_v sc
                          WHERE
                                sc.code = 'CON500_CONTRACT_STATUS'
                          AND
                                 t.contract_status = sc.code_value
                          )  in ('TERMINATE','LOSSSHARE_TERMINATE','AGENT_TERMINATE','INCEPT','ET','VIRTUAL_TERMINATE')
                    AND   t.data_class ='NORMAL'

                    ) v #WHERE_CLAUSE#
                    #ORDER_BY_CLAUSE#
            ]]></bm:query-sql>
        </bm:operation>
         <bm:operation name="update">
             <bm:update-sql><![CDATA[
                begin
                    con_contract_settlement_pkg.update_status(
                    p_contract_id      =>${@contract_id},
                    p_contract_status  =>${@contract_status},
                    p_user_id        =>${/session/@user_id}
                );
                end;
             ]]></bm:update-sql>
         </bm:operation>
    </bm:operations>
    <bm:fields>
            <bm:field name="contract_id"/>
            <bm:field name="contract_number"/>
            <bm:field name="contract_name"/>
            <bm:field name="bp_code"/>
            <bm:field name="bp_name"/>
            <bm:field name="bp_agent_code"/>
            <bm:field name="bp_agent"/>
            <bm:field name="division_n"/>
            <bm:field name="lease_execution_date" databaseType="DATE" datatype="java.util.Date" />
            <bm:field name="contract_status"/>
            <bm:field name="contract_status_n"/>
            <bm:field name="lease_channel"/>
            <bm:field name="lease_channel_n"/>
            <bm:field name="et_date" databaseType="DATE" datatype="java.util.Date"/>
            <bm:field name="loss_sharing_flag"/>
            <bm:field name="loss_sharing_flag_n"/>
            <bm:field name="guarantor_info"/>
        </bm:fields>
    <bm:query-fields>
        <bm:query-field name="lease_channel" queryExpression="v.lease_channel = ${@lease_channel}"/>
        <bm:query-field name="contract_number"  queryexpression="v.contract_number like &apos;%&apos; || ${@contract_number} || &apos;%&apos;"/>
        <bm:query-field name="contract_name" queryexpression="v.contract_name like &apos;%&apos; || ${@contract_name} || &apos;%&apos;"/>
        <bm:query-field name="bp_code"  queryexpression="v.bp_code like &apos;%&apos; || ${@bp_code} || &apos;%&apos;"/>
        <bm:query-field name="bp_name"  queryexpression="v.bp_name = ${@bp_name}"/>
        <bm:query-field name="bp_agent_code"  queryexpression="v.bp_agent_code like &apos;%&apos; || ${@bp_agent_code} || &apos;%&apos;"/>
        <bm:query-field name="bp_agent"  queryexpression="v.bp_agent like &apos;%&apos; || ${@bp_agent} || &apos;%&apos;"/>
        <bm:query-field name="division_n"  queryexpression="v.division_n = ${@division_n}"/>
        <bm:query-field name="contract_status"  queryexpression="v.contract_status = ${@contract_status}"/>
        <bm:query-field name="lease_execution_date_from"  queryExpression="to_char(lease_execution_date,&apos;YYYY-MM-DD&apos;)&gt;=${@lease_execution_date_from}"/>
        <bm:query-field name="lease_execution_date_to"  queryExpression="to_char(lease_execution_date,&apos;YYYY-MM-DD&apos;)&lt;=${@lease_execution_date_to}"/>
        <bm:query-field name="et_date_from"  queryExpression="to_char(et_date,&apos;YYYY-MM-DD&apos;)&gt;=${@et_date_from}"/>
        <bm:query-field name="et_date_to"  queryExpression="to_char(et_date,&apos;YYYY-MM-DD&apos;)&lt;=${@et_date_to}"/>
    </bm:query-fields>
</bm:model>