con_contract_excuted.lwm 8.5 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 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171
<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: huangtianyang
    $Date: 2018/9/12 下午7:20
    $Revision: 1.0 
-->
<bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm" needAccessControl="false">
    <bm:operations>
        <bm:operation name="update">
            <bm:update-sql><![CDATA[
begin
  -- 刷新现金流日期
  con_contract_pkg.contract_incept(p_contract_id => ${@contract_id},
                                   p_exchange_rate_quotation => ${@exchange_rate_quotation},
                                   p_exchange_rate_type => ${@exchange_rate_type},
                                   p_exchange_rate => ${@exchange_rate},
                                   p_inception_of_lease => ${@inception_of_lease},
                                   p_base_rate_new => ${@base_rate_new},
                                   p_refresh_cashflow_only_flag => 'Y',
                                   p_user_id => ${/session/@user_id});
end;

        ]]></bm:update-sql>
        </bm:operation>
        <bm:operation name="execute">
            <bm:update-sql><![CDATA[
begin
  ds_document_interface_pkg.update_contract_from_app(p_contract_id => ${@contract_id},
                                 p_user_id => ${/session/@user_id});


end;

        ]]></bm:update-sql>
        </bm:operation>

        <bm:operation name="insert">
            <bm:update-sql><![CDATA[
            begin
                con_contract_cancle_pkg.contract_cancle(
                   p_contract_id => ${@contract_id},
                   p_user_id     => ${/session/@user_id}
                );

            end;

        ]]></bm:update-sql>
        </bm:operation>

        <bm:operation name="query">
            <bm:query-sql><![CDATA[
                 SELECT
                    su.description user_name,
                    su.user_id,
                    t1.contract_id,
                    t1.document_type,
                    t1.document_category,
                    t1.contract_number,
                    t1.bp_id_tenant,
                    t1.bp_name,
                    t2.bp_class,
                    (SELECT
                        v.code_value_name
                    FROM
                        sys_code_values_v v
                    WHERE
                        v.code       = 'HLS211_BP_CLASS' AND
                        v.code_value = t2.bp_class
                    ) AS bp_class_n,
                    t1.finance_amount,
                    t1.business_type,
                    t1.business_type_desc,
                    t1.lease_channel,
                    t1.lease_channel_desc,
                    t1.division,
                    t1.division_desc,
                    t1.contract_status,
                    t1.contract_status_desc,
                    t1.company_id,
                    DECODE(
                    (SELECT
                        COUNT(1) FROM con_contract_cashflow ccc WHERE ccc.contract_id = t1.contract_id AND
                        ccc.cf_item                                                   = 2 AND
                        ccc.cf_direction                                             <> 'NONCASH' AND
                        ccc.write_off_flag                                           IN ('NOT', 'PARTIAL')
                    ), '0', '完全核销', '未完全核销') down_payment_write_off,
                    t1.execution_status,
                    (SELECT
                        v.code_value_name
                    FROM
                        sys_code_values_v v
                    WHERE
                        v.code      = 'EXECUTION_STATUS_DS' AND
                        v.code_value= t1.execution_status
                    ) execution_status_desc,
                    to_char(t1.LEASE_EXECUTION_date,'yyyy-mm-dd') LEASE_EXECUTION_date,
                    t1.LEASE_EXECUTION_FLAG,
                    (SELECT
                        v.code_value_name
                    FROM
                        sys_code_values_v v
                    WHERE
                        v.code      = 'LEASE_EXECUTION_FLAG' AND
                        v.code_value= nvl(t1.LEASE_EXECUTION_FLAG,'N')) LEASE_EXECUTION_FLAG_desc,

                        to_char(cccr.interface_name) case_interface_name,
                        to_char(cccr.status) case_status ,
                        --ds_common_get_data_pkg.get_case_interface_name(p_contract_id=>t1.contract_id) case_interface_name,
                        --ds_common_get_data_pkg.get_case_status(p_contract_id=>t1.contract_id) case_status,


                         ( select li.pattern from  con_contract_lease_item li
                    where li.equipment_type = 'MAIN'
                   and t1.contract_id = li.contract_id) pattern,
                   ( select li.machine_number from  con_contract_lease_item li
                    where li.equipment_type = 'MAIN'
                   and t1.contract_id = li.contract_id) machine_number,
                   ( select to_char(li.factory_date,'yyyy-mm-dd') factory_date from  con_contract_lease_item li
                    where li.equipment_type = 'MAIN'
                   and t1.contract_id = li.contract_id) factory_date,
                    (select m.extra_nam
                    from hls_bp_master m
                    where m.bp_id = t1.bp_id_agent_level1) agent_extra_nam,
                    (select m.bp_name
                    from hls_bp_master m
                    where m.bp_id = t1.bp_id_agent_level1) agent_name,
                    t1.DOWN_PAYMENT_STATUS,
                    t1.DOWN_PAYMENT_STATUS_N,
                    t1.AREA_DISTINGUISH as AREA_DISTINGUISH
                FROM
                    CON_CONTRACT_V t1,
                    hls_bp_master t2,
                    SYS_USER SU,
                    CALL_CONTRACT_DISTRIBUTION_V CCD,
                    (select /*+ no_merge */
                    document_id, wm_concat(status) status,wm_concat(interface_name) interface_name from (SELECT document_id,(
                            SELECT v.code_value_name value_name
                            FROM   sys_code_values_v v
                            WHERE  v.code = 'CASE_STATUS'
                            AND    v.code_value = cccr.status
                           ) status  ,interface_name
                    FROM   con_contract_case_record cccr
                    WHERE  cccr.document_table = 'CON_CONTRACT')
                    group by document_id) cccr
                #WHERE_CLAUSE#
                --ORDER BY t1.contract_number desc
            ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
    <bm:query-fields>
        <bm:query-field name="division" queryExpression="t1.division = ${@division}"/>
        <bm:query-field name="business_type" queryExpression="t1.business_type = ${@business_type}"/>
        <bm:query-field name="lease_channel" queryExpression="t1.lease_channel = ${@lease_channel}"/>
        <bm:query-field name="contract_status" queryExpression="t1.contract_status = ${@contract_status}"/>
        <bm:query-field name="contract_number" queryExpression="t1.contract_number like ${@contract_number}"/>
        <bm:query-field name="contract_name" queryExpression="t1.contract_name like ${@contract_name}"/>
        <bm:query-field name="execution_status" queryExpression="t1.execution_status like ${@execution_status}"/>
        <bm:query-field name="bp_name" queryExpression="t1.bp_name like ${@bp_name}"/>
        <bm:query-field name="user_name" queryExpression="su.description like ${@user_name}"/>
        <bm:query-field name="bp_code" queryExpression="t1.bp_code like ${@bp_code}"/>
        <bm:query-field name="lease_execution_date_from" queryExpression="t1.LEASE_EXECUTION_date &gt;= to_date(${@lease_execution_date_from},'yyyy-mm-dd')"/>
        <bm:query-field name="lease_execution_date_to" queryExpression="t1.LEASE_EXECUTION_date &lt;= to_date(${@lease_execution_date_to},'yyyy-mm-dd')"/>
        <bm:query-field name="bp_id_agent_level1" queryExpression="t1.bp_id_agent_level1 = ${@bp_id_agent_level1}"/>
        <bm:query-field name="down_payment_status" queryExpression="t1.down_payment_status = ${@down_payment_status}"/>
        <bm:query-field name="agent_extra_nam" queryExpression="(select m.extra_nam from hls_bp_master m where m.bp_id = t1.bp_id_agent_level1) like ${@agent_extra_nam}"/>
    </bm:query-fields>
    <bm:data-filters>
        <bm:data-filter name="join"
                        expression=" t1.bp_id_tenant = t2.bp_id(+) AND ccd.contract_id(+) = t1.contract_id and ccd.user_id = su.user_id(+) and ( t1.lease_execution_flag = 'Y' ) AND cccr.document_id(+)=t1.contract_id "/>
    </bm:data-filters>
</bm:model>