<?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>