<?xml version="1.0" encoding="UTF-8"?>
<bm:model xmlns:f="leaf.database.features" xmlns:bm="http://www.leaf-framework.org/schema/bm">
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[
                SELECT
                    t1.unit_id,
                    t1.lease_organization_n,
                    t1.org_unit_description,
                    t1.total_received_amount,
                    t1.total_due_amount,
                    DECODE(t1.total_received_amount, NULL, NULL, TO_CHAR(ROUND((t1.total_received_amount / t1.total_due_amount * 100), 2), 'FM9990.0099')
                    || '%') ratio
                FROM
                    (SELECT
                        eou.unit_id,
                        (SELECT
                            hlo.description
                        FROM
                            hls_lease_organization hlo
                        WHERE
                            hlo.lease_organization = eou.lease_organization
                        ) AS lease_organization_n,
                        (SELECT
                            description_text
                        FROM
                            fnd_descriptions
                        WHERE
                            description_id = eou.description_id AND
                            Language       = 'ZHS'
                        ) org_unit_description,
                        (SELECT
                            SUM(cwo.write_off_due_amount)
                        FROM
                            con_contract_cashflow ccc,
                            con_contract cc,
                            csh_write_off cwo
                        WHERE
                            ccc.contract_id                     = cc.contract_id AND
                            ccc.cashflow_id                     = cwo.cashflow_id AND
                            ccc.cf_item                        IN (1, 200) AND
                            ccc.cf_direction                    = 'INFLOW' AND
                            TO_CHAR(ccc.due_date, 'yyyy')       = ${@year} AND
                            TO_CHAR(ccc.due_date, 'mm')         = ${@month} AND
                            TO_CHAR(cwo.write_off_date, 'yyyy') = ${@year} AND
                            TO_CHAR(cwo.write_off_date, 'mm')   = ${@month} AND
                            cwo.reversed_flag                   = 'N' AND
                            cc.data_class                       = 'NORMAL' AND
                            cc.unit_id                          = eou.unit_id
                        ) total_received_amount,
                        (SELECT
                            SUM(ccc1.due_amount)
                        FROM
                            con_contract_cashflow ccc1,
                            con_contract cc1
                        WHERE
                            ccc1.contract_id               = cc1.contract_id AND
                            ccc1.cf_item                  IN (1, 200) AND
                            ccc1.cf_direction              = 'INFLOW' AND
                            TO_CHAR(ccc1.due_date, 'yyyy') = ${@year} AND
                            TO_CHAR(ccc1.due_date, 'mm')   = ${@month} AND
                            cc1.contract_status           <> 'NEW' AND
                            ccc1.cf_status                 = 'RELEASE' AND
                            cc1.data_class                 = 'NORMAL' AND
                            cc1.unit_id                    = eou.unit_id
                        ) total_due_amount
                    FROM
                        exp_org_unit eou
                    WHERE
                        eou.enabled_flag = 'Y'
                    ) t1
                WHERE
                    t1.lease_organization_n IS NOT NULL
            ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
</bm:model>
