sdic_contract_info.lwm 3.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
<?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
                    pp.virtual_con_number contract_number,
                    hbm.bp_name,
                    pp.signing_date,
                    extract(YEAR FROM pp.signing_date) sign_year,
                    extract(MONTH FROM pp.signing_date) sign_month,
                    extract(DAY FROM pp.signing_date) sign_day,
                    extract(YEAR FROM sysdate) year_now,
                    extract(MONTH FROM sysdate) month_now,
                    extract(DAY FROM sysdate) day_now,
                    --add by liuguocai
                    (
                    SELECT
                        cv.bank_account_name
                    FROM
                        sdic_batch_csh_accounts t,
                        con_contract_cashflow ccc,
                        con_contract ct ,
                        csh_bank_account_v cv
                    WHERE
                        ccc.cashflow_id   = t.cashflow_id AND
                        t.session_id      = ${/session/@session_id} AND
                        rownum            =1 AND
                        ccc.contract_id   =ct.contract_id AND
                        ct.bank_account_id=cv.bank_account_id
                    )bank_account_name,
                    (SELECT
                        cv.bank_branch_name
                    FROM
                        sdic_batch_csh_accounts t,
                        con_contract_cashflow ccc,
                        con_contract ct ,
                        csh_bank_account_v cv
                    WHERE
                        ccc.cashflow_id   = t.cashflow_id AND
                        t.session_id      = ${/session/@session_id} AND
                        rownum            =1 AND
                        ccc.contract_id   =ct.contract_id AND
                        ct.bank_account_id=cv.bank_account_id
                    )bank_branch_name,
                    (SELECT
                        cv.bank_account_num
                    FROM
                        sdic_batch_csh_accounts t,
                        con_contract_cashflow ccc,
                        con_contract ct ,
                        csh_bank_account_v cv
                    WHERE
                        ccc.cashflow_id   = t.cashflow_id AND
                        t.session_id      = ${/session/@session_id} AND
                        rownum            =1 AND
                        ccc.contract_id   =ct.contract_id AND
                        ct.bank_account_id=cv.bank_account_id
                    )bank_account_num
                FROM
                    prj_project pp,
                    hls_bp_master hbm
                WHERE
                    pp.bp_id_tenant = hbm.bp_id AND
                    pp.data_class   = 'VIRTUAL_CON' AND
                    pp.project_id   =
                    (SELECT
                        distinct cc.project_id
                    FROM
                        con_contract cc
                    WHERE
                        cc.data_class   = 'NORMAL' AND
                        cc.contract_id IN
                        (SELECT
                            ccc.contract_id
                        FROM
                            sdic_batch_csh_accounts t,
                            con_contract_cashflow ccc
                        WHERE
                            ccc.cashflow_id = t.cashflow_id AND
                            t.session_id    = ${/session/@session_id}
                        )
                    )
            ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
</bm:model>