rd_project_base_info.lwm 3.8 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
<?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
                        tt1.project_id,
                        tt1.project_number,
                        tt2.bp_name,
                        (SELECT description FROM hls_division WHERE division = tt1.division
                        ) division_n,
                        tt3.lease_times,
                        (SELECT DESCRIPTION FROM hls_price_list WHERE price_list = tt3.price_list
                        )price_list_n,
                        tt5.bp_name agent_name,
                        TO_CHAR(tt3.finance_amount,'FM999,999,999.90') finance_amount,
                        tt4.deposit_ratio,
                        tt4.down_payment_ratio,
                        TO_CHAR(tt4.pmt_first,'FM999,999,999.90') pmt_first,
                        (SELECT
                            company_full_name
                        FROM
                            fnd_companies
                        WHERE
                            company_id = tt1.company_id
                        )company_full_name,
                        '' major_brand,
                        '' total_overdue,
                        tt1.bp_class,
                        (SELECT
                            r.score_result
                        FROM
                            fnd_sc_score t,
                            fnd_sc_score_result r,
                            fnd_score_template fst
                        WHERE
                            tt2.project_id          = t.project_id AND
                            tt1.company_id          = t.company_id AND
                            t.score_num             = 1 AND
                            t.sc_score_id           = r.sc_score_id AND
                            
                            r.score_template_id     = fst.score_template_id AND
                            fst.score_template_code = 'TEL_OFFICE_CHECK' AND
                            r.pre_score_result     IS NOT NULL
                        )score_result,
                        (SELECT
                            r.pre_score_result
                        FROM
                            fnd_sc_score t,
                            fnd_sc_score_result r,
                            fnd_score_template fst
                        WHERE
                            tt2.project_id          = t.project_id AND
                            tt1.company_id          = t.company_id AND
                            t.score_num             = 1 AND
                            t.sc_score_id           = r.sc_score_id AND
                           
                            r.score_template_id     = fst.score_template_id AND
                            fst.score_template_code = 'TEL_OFFICE_CHECK'
                        )pre_score_result
                    FROM
                        prj_project tt1,
                        prj_project_bp tt2,
                        prj_quotation tt3,
                        hls_fin_calculator_hd tt4,
                        hls_bp_master tt5
                    WHERE
                        tt1.project_id        = tt2.project_id(+) AND
                        tt2.bp_category(+)    = 'TENANT' AND
                        tt1.project_id        = tt3.document_id AND
                        tt3.document_category = 'PROJECT' AND
                        tt3.calc_session_id   = tt4.calc_session_id AND
                        tt1.invoice_agent_id  = tt5.bp_id(+)
                    ) t1
                WHERE
                    t1.project_id = ${@project_id}
            ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
</bm:model>