ast_terminate_situation.lwm 5.7 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 134 135 136 137 138
<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: ZLF  
    $Date: 2015-1-15 下午8:46:58  
    $Revision: 1.0  
    $Purpose: 
-->
<bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm">
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[
                SELECT
                    *
                FROM
                    (SELECT
                        c.contract_id,
                        c.contract_number,
                        c.bp_id_tenant,
                        (SELECT h.bp_name FROM hls_bp_master h WHERE h.bp_id = c.bp_id_tenant
                        ) bp_name,
                        cli.brand_id,
                        (SELECT
                            bv.description
                        FROM
                            hls_car_brands_vl bv
                        WHERE
                            bv.brand_id = cli.brand_id
                        ) brand_des,
                        cli.series_id,
                        (SELECT
                            sv.description
                        FROM
                            hls_car_series_vl sv
                        WHERE
                            sv.series_id = cli.series_id
                        ) series_des,
                        cli.model_id,
                        (SELECT
                            mv.description
                        FROM
                            hls_car_model_vl mv
                        WHERE
                            mv.model_id = cli.model_id
                        ) model_des,
                        cid.item_frame_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
                        ) contract_status_des,
                        (NVL(
                        (SELECT
                            '已结清'
                        FROM
                            dual
                        WHERE
                            NOT EXISTS
                            (SELECT
                                *
                            FROM
                                con_contract_cashflow ccc
                            WHERE
                                ccc.contract_id     =c.contract_id AND
                                ccc.write_off_flag IN ('NOT','PARTIAL')
                            )
                        ),'未结清')) terminate_flag_des,
                        (NVL(
                        (SELECT
                            '过户已完成'
                        FROM
                            dual
                        WHERE
                            EXISTS
                            (SELECT
                                *
                            FROM
                                con_contract_item_detail cd
                            WHERE
                                cd.item_detail_id                  =cid.item_detail_id AND
                                NVL(cd.ownership_transfer_flag,'N')='Y' AND
                                cd.transfer_date                  IS NOT NULL
                            )
                        ),'过户未完成')) transfer_flag_des,
                        (NVL(
                        (SELECT
                            '已解除抵押'
                        FROM
                            dual
                        WHERE
                            EXISTS
                            (SELECT
                                *
                            FROM
                                ast_car_license al
                            WHERE
                                al.item_detail_id                =cid.item_detail_id AND
                                NVL(al.relieve_mortgage_flag,'N')='Y' AND
                                al.relieve_mortgage_date        IS NOT NULL
                            )
                        ),'未解除抵押')) relieve_mortgage_flag_des,
                        (SELECT
                            MAX(ai.insurance_date_to)
                        FROM
                            ast_car_insurance ai
                        WHERE
                            ai.item_detail_id=cid.item_detail_id AND
                            ai.insurance_type='10'
                        ) compulsory_insurance_end_date,
                        (SELECT
                            MAX(ai.insurance_date_to)
                        FROM
                            ast_car_insurance ai
                        WHERE
                            ai.item_detail_id=cid.item_detail_id AND
                            ai.insurance_type='20'
                        ) commercial_insurance_end_date
                    FROM
                        con_contract c,
                        con_contract_lease_item cli,
                        con_contract_item_detail cid
                    WHERE
                        c.contract_id              = cli.contract_id AND
                        cli.contract_lease_item_id = cid.contract_lease_item_id AND
                        c.data_class               = 'NORMAL' AND
                        c.contract_status in ('ET','ETING','TERMINATE')
                    ) t1 #WHERE_CLAUSE#
            ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
    <bm:query-fields>
        <bm:query-field name="contract_number" queryExpression="t1.contract_number like ${@contract_number}"/>
        <bm:query-field name="bp_name" queryExpression="t1.bp_name like ${@bp_name}"/>
    </bm:query-fields>
</bm:model>