con_collection.lwm

<?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:update-sql><![CDATA[
                SELECT
                    t1.col_id,
                    t1.contract_id,
                    t1.project_id,
                    t1.staff_id,
                    (SELECT
                        dbms_lob.substr(wmsys.wm_concat(ch.COL_PERSON_N))
                    FROM
                        con_collection_call_hd_lv ch
                    WHERE
                        ch.COL_NUMBER = t1.COL_NUMBER AND
                        ch.status    IN ('PHONING','TO_VISIT')
                    ) staff_id_n,
                    t1.assign_type,
                    t1.assign_date,
                    t1.status_id,
                    t1.status_name,
                    t1.overdue_status_type,
                    t1.out_date,
                    t1.data_class,
                    t1.user_col_d01,
                    t1.user_col_d02,
                    t1.user_col_d03,
                    t1.user_col_d04,
                    t1.user_col_d05,
                    t1.user_col_v01,
                    t1.user_col_v02,
                    t1.user_col_v03,
                    t1.user_col_v04,
                    t1.user_col_v05,
                    t1.user_col_n01,
                    t1.user_col_n02,
                    t1.user_col_n03,
                    t1.user_col_n04,
                    t1.user_col_n05,
                    t1.creation_date,
                    t1.created_by,
                    t1.last_update_date,
                    t1.last_updated_by,
                    t1.user_id,
                    t1.type_id,
                    t1.type_code,
                    t1.type_name,
                    t1.group_id,
                    t1.group_code,
                    t1.overdue_status_date,
                    t1.lst_date,
                    t1.lst_reason,
                    t1.document_status,
                    t1.document_number,
                    t1.document_category,
                    t1.document_type,
                    t1.company_id,
                    t1.source_col_id,
                    t1.owner_user_id,
                    t1.wfl_instance_id,
                    t1.car_recived_flag,
                    t1.return_type,
                    t1.bp_id,
                    (SELECT h.bp_code FROM hls_bp_master h WHERE h.bp_id = t1.bp_id
                    ) bp_code,
                    (SELECT h.bp_name FROM hls_bp_master h WHERE h.bp_id = t1.bp_id
                    ) bp_name,
                    (SELECT h.bp_class FROM hls_bp_master_lv h WHERE h.bp_id = t1.bp_id
                    ) bp_class,
                    (SELECT h.bp_class_n FROM hls_bp_master_lv h WHERE h.bp_id = t1.bp_id
                    ) bp_class_n,
                    (SELECT h.bp_type_n FROM hls_bp_master_lv h WHERE h.bp_id = t1.bp_id
                    ) bp_type_n,
                    (SELECT h.liv_province_n FROM hls_bp_master_lv h WHERE h.bp_id = t1.bp_id
                    ) liv_province_n,
                    (SELECT h.liv_city_n FROM hls_bp_master_lv h WHERE h.bp_id = t1.bp_id
                    ) liv_city_n,
                    (SELECT h.phone FROM hls_bp_master_lv h WHERE h.bp_id = t1.bp_id
                    ) phone,
                    t1.status,
                    (SELECT
                        cv.code_value_name value_name
                    FROM
                        sys_code_values_v cv
                    WHERE
                        cv.code                    = 'CON632_COLLECTION_STATUS' AND
                        cv.code_enabled_flag       = 'Y' AND
                        cv.code_value_enabled_flag = 'Y' AND
                        cv.code_value              = t1.status
                    ) status_n,
                    t1.wfl_status,
                    (SELECT
                        MAX(cc.overdue_max_days)
                    FROM
                        con_contract cc
                    WHERE
                        cc.bp_id_tenant = t1.bp_id
                    ) overdue_max_days,
                    (SELECT
                        SUM(cf.overdue_amount)
                    FROM
                        con_contract cc,
                        con_contract_cashflow cf
                    WHERE
                        cc.bp_id_tenant = t1.bp_id AND
                        cc.contract_id  = cf.contract_id AND
                        cf.cf_item      = 1 AND
                        cf.cf_direction = 'INFLOW'
                    ) overdue_amount,
                    (SELECT
                        SUM(NVL(cf.due_amount, 0)) - SUM(NVL(cf.received_amount, 0))
                    FROM
                        con_contract cc,
                        con_contract_cashflow cf
                    WHERE
                        cc.bp_id_tenant = t1.bp_id AND
                        cc.contract_id  = cf.contract_id AND
                        cf.cf_item      = 9 AND
                        cf.cf_direction = 'INFLOW'
                    ) overdue_penalty,
                    t1.visit_person_id,
                    (SELECT e.name FROM exp_employees e WHERE e.employee_id = t1.visit_person_id
                    ) visit_person_id_n,
                    t1.closed_reason,
                    t1.col_number
                FROM
                    con_collection t1
                WHERE
                    t1.col_id = ${@col_id}
            ]]></bm:update-sql>
        </bm:operation>
        <bm:operation name="update">
            <bm:update-sql><![CDATA[
                BEGIN
                    -- Call the procedure
                    con_collection_pkg.close_con_collection(p_col_id => ${@col_id},p_closed_reason => ${@closed_reason}, p_user_id => ${/session/@user_id});
                END;
            ]]></bm:update-sql>
        </bm:operation>
        <bm:operation name="execute">
            <bm:update-sql><![CDATA[
                BEGIN
                    -- Call the procedure
                    con_collection_pkg.transfer_to_visit(p_col_id => ${@col_id},p_visit_person_id => ${@visit_person_id}, p_user_id => ${/session/@user_id});
                END;
            ]]></bm:update-sql>
        </bm:operation>
    </bm:operations>
</bm:model>