<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: qwm  
    $Date: 2013-5-23 下午03:42:50  
    $Revision: 1.0  
    $Purpose: 
-->
<bm:model xmlns:s="leaf.plugin.script" 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.*,(select v.code_value_name from sys_code_values_v v where v.code = 'CON_WRITE_OFF_FLAG' and v.code_value = t1.cf_61_write_off_flag) cf_61_write_off_flag_n
  FROM (SELECT t.contract_id,
               t.contract_name,
               f.cashflow_id,
               t.search_term_1,
               f.times,
               t.contract_number,
               t.bp_id_tenant,
               hbm.bp_class,
               hbm.bp_code       bp_id_tenant_c,
               hbm.bp_name       bp_id_tenant_n,
               hbm.bp_id         tenant_bp_id,
               hbm.bp_name       bp_name,
               hbm.bp_code       bp_code,
               h.description     cf_item_desc,
               h.cf_item,
               f.due_amount,
               t.currency,
               /* (SELECT dbms_lob.substr(wmsys.wm_concat(ci.serial_number))
                FROM con_contract_lease_item ci
               WHERE ci.contract_id = t.contract_id
                 AND ci.serial_number IS NOT NULL) serial_number,*/
               to_char(f.due_date, 'yyyy-mm-dd') due_date,
               nvl(f.received_amount, 0) received_amount,
               nvl(f.due_amount, 0) - nvl(f.received_amount, 0) residual_amount,
               (nvl((SELECT SUM(l.amount_paid)
                      FROM csh_payment_req_ln l, csh_payment_req_hd h
                     WHERE l.ref_doc_line_id = f.cashflow_id
                       AND l.payment_req_id = h.payment_req_id
                       AND h.submitted_flag = 'Y'
                       AND h.approval_status = 'APPROVED'
                       AND nvl(h.closed_flag, 'N') = 'Y'),
                    0) +
               nvl((SELECT SUM(l.amount)
                      FROM csh_payment_req_ln l, csh_payment_req_hd h
                     WHERE l.ref_doc_line_id = f.cashflow_id
                       AND l.payment_req_id = h.payment_req_id
                       AND h.submitted_flag = 'Y'
                       AND h.approval_status = 'APPROVED'
                       AND nvl(h.closed_flag, 'N') <> 'Y'),
                    0) +
               nvl((SELECT SUM(l.amount)
                      FROM csh_payment_req_ln l, csh_payment_req_hd h
                     WHERE l.ref_doc_line_id = f.cashflow_id
                       AND l.payment_req_id = h.payment_req_id
                       AND h.submitted_flag = 'Y'
                       AND h.approval_status = 'APPROVING'),
                    0)) applied_pay_amount,
               t.vat_flag,
               (SELECT fc.company_short_name
                  FROM fnd_companies fc
                 WHERE fc.company_id = t.company_id) lessor_name,
               t.business_type con_business_type,
               (SELECT hbt.description
                  FROM hls_business_type hbt
                 WHERE t.business_type = hbt.business_type
                   AND rownum = 1) con_business_type_n,
               /*(SELECT m.taxpayer_type
                  FROM hls_bp_master_lv m
                 WHERE m.bp_id = csh_payment_req_pkg.get_bp_id(t.contract_id)) taxpayer_type,
               (SELECT v.code_value_name AS value_name
                  FROM sys_code_values_v v
                 WHERE v.code = 'HLS211_TAXPAYER_TYPE'
                   AND v.code_enabled_flag = 'Y'
                   AND v.code_value_enabled_flag = 'Y'
                   AND v.code_value =
                       (SELECT m.taxpayer_type
                          FROM hls_bp_master_lv m
                         WHERE m.bp_id =
                               csh_payment_req_pkg.get_bp_id(t.contract_id))) taxpayer_type_n,*/
               /* (SELECT v.code_value_name
                  FROM hls_product_plan_definition hls,
                       con_contract_lease_item     ccl,
                       con_contract                cc,
                       sys_code_values_v           v
                 WHERE ccl.contract_id = t.contract_id
                   AND cc.contract_id = ccl.contract_id
                   AND ccl.product_plan_id = hls.product_plan_id
                   AND cc.data_class = 'NORMAL'
                   AND v.code = 'PRODUCT_CATEGORY'
                   AND v.code_value = hls.product_type) product_type_n,
               (SELECT hls.product_name_write
                  FROM hls_product_plan_definition hls,
                       con_contract                cc,
                       con_contract_lease_item     ccl
                 WHERE ccl.contract_id = t.contract_id
                   AND cc.contract_id = ccl.contract_id
                   AND ccl.product_plan_id = hls.product_plan_id
                   AND cc.data_class = 'NORMAL') product_name_write,*/
               t.cdd_list_id,
               t.division,
               (SELECT h8.description
                  FROM hls_division h8
                 WHERE h8.enabled_flag = 'Y'
                   AND t.division = h8.division) division_n,
               (con_contract_pkg.get_first_contract_day(t.contract_id)) first_contract_day,
               (SELECT decode(COUNT(1), 0, 'N', 'Y')
                  FROM acp_invoice_ln ai
                 WHERE ai.contract_id = t.contract_id) invoice_uploaded_flag,
               (SELECT v.code_value_name AS value_name
                  FROM sys_code_values_v v
                 WHERE v.code = 'YES_NO'
                   AND v.code_enabled_flag = 'Y'
                   AND v.code_value_enabled_flag = 'Y'
                   AND v.code_value =
                       (SELECT decode(COUNT(1), 0, 'N', 'Y')
                          FROM acp_invoice_ln ai
                         WHERE ai.contract_id = t.contract_id)) invoice_uploaded_flag_n,
               i.pattern,
               i.machine_number,
               (SELECT to_char(substr(wm_concat(cccr.interface_name), 1, 1000))
                  FROM con_contract_case_record cccr
                 WHERE cccr.document_id = t.contract_id
                   AND cccr.document_table = 'CON_CONTRACT') interface_name,
               (SELECT to_char(substr(wm_concat(cccr.status_n), 1, 1000))
                  FROM con_contract_case_record_lv cccr
                 WHERE cccr.document_id = t.contract_id
                   AND cccr.document_table = 'CON_CONTRACT') status_n,
                   i.price,
                   i.factory_price,
                nvl(t.first_delay_flag,'N') first_delay_flag,
                (SELECT v.code_value_name AS value_name
                FROM sys_code_values_v v
                WHERE v.code = 'YES_NO'
                AND v.code_enabled_flag = 'Y'
                AND v.code_value_enabled_flag = 'Y'
                AND v.code_value = nvl(t.first_delay_flag,'N')) first_delay_flag_n,
                t.bp_id_agent_level1,
                (SELECT bp.bp_name
                FROM hls_bp_master bp
                WHERE bp.bp_id = t.bp_id_agent_level1) bp_id_agent_level1_n,
                (SELECT bp.extra_nam
                FROM hls_bp_master bp
                WHERE bp.bp_id = t.bp_id_agent_level1) agent_extra_nam,
                nvl((select
                       (case
                         when sum(ccc.due_amount) = sum(ccc.received_amount) then
                          'FULL'
                         when sum(ccc.received_amount) = 0 then
                          'NOT'
                         when sum(ccc.received_amount) > 0 and
                              sum(ccc.due_amount) > sum(ccc.received_amount) then
                          'PARTIAL'
                         else
                          'NOT'
                       end) write_off_flag
                  from con_contract_cashflow ccc
                 where ccc.cf_item = 61
                   and ccc.cf_status = 'RELEASE'
                   and ccc.cf_direction = 'INFLOW'
                   and ccc.contract_id = t.contract_id
                 group by ccc.contract_id),'FULL') cf_61_write_off_flag
          FROM con_contract_cashflow   f,
               hls_cashflow_item       h,
               con_contract_lease_item i,
               hls_bp_master           hbm,
               con_contract            t
         WHERE i.contract_id = t.contract_id
           AND i.enabled_flag = 'Y'
           AND i.equipment_type = 'MAIN'
           AND t.contract_id = f.contract_id
           and t.bp_id_tenant = hbm.bp_id(+)
           AND f.cf_item = h.cf_item(+)
           AND h.cf_direction(+) = 'OUTFLOW'
           AND f.write_off_flag <> 'FULL'
           AND f.cf_status IN ('RELEASE', 'BLOCK')
           AND f.cf_direction = 'OUTFLOW'
           AND (nvl(f.due_amount, 0) -
               nvl((SELECT SUM(l.amount_paid)
                      FROM csh_payment_req_ln l, csh_payment_req_hd h
                     WHERE l.ref_doc_line_id = f.cashflow_id
                       AND l.payment_req_id = h.payment_req_id
                          -- AND
                          --h.submitted_flag        = 'Y' AND
                          -- h.approval_status       = 'APPROVED'
                       AND nvl(h.closed_flag, 'N') = 'Y'),
                    0) -
               nvl((SELECT SUM(l.amount)
                      FROM csh_payment_req_ln l, csh_payment_req_hd h
                     WHERE l.ref_doc_line_id = f.cashflow_id
                       AND l.payment_req_id = h.payment_req_id
                          --AND
                          -- h.submitted_flag         = 'Y' AND
                          --h.approval_status        = 'APPROVED'
                       AND nvl(h.closed_flag, 'N') <> 'Y'),
                    0) -
               nvl((SELECT SUM(l.amount)
                      FROM csh_payment_req_ln l, csh_payment_req_hd h
                     WHERE l.ref_doc_line_id = f.cashflow_id
                       AND l.payment_req_id = h.payment_req_id
                       AND h.approval_status = 'APPROVING'
                    --AND
                    --h.submitted_flag  = 'Y' AND
                    --h.approval_status = 'APPROVING'
                    ),
                    0)) > 0
           AND t.data_class = 'NORMAL'
           AND t.contract_status NOT IN ('NEW','SIGN','CANCEL', 'PENDING', 'CLOSED')
           AND f.cf_item <> 0) t1
           #WHERE_CLAUSE#
           #ORDER_BY_CLAUSE#

            ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
    <bm:fields>
        <bm:field name="contract_number" databaseType="VARCHAR2" datatype="java.lang.String"
                  physicalName="CONTRACT_NUMBER" prompt="HLS.CONTRACT_NUMBER"/>
        <bm:field name="contract_name" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="CONTRACT_NAME"
                  prompt="HLS.CONTRACT_NAME"/>
        <!--<bm:field name="project_name" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="PROJECT_NAME"-->
        <!--prompt="HLS.PROJECT_NAME"/>-->
        <bm:field name="tenant_bp_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="TENANT_BP_ID"/>
        <bm:field name="cf_item_desc" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="CF_ITEM_DESC"
                  prompt="CSH501.CSH_PAYMENT_PRJ"/>
        <bm:field name="due_amount" databaseType="NUMBER" datatype="java.lang.Double" physicalName="DUE_AMOUNT"
                  prompt="CSH501.CSH_PAYMENT_AMOUNT"/>
        <bm:field name="currency" databaseType="CHAR" datatype="java.lang.String" physicalName="CURRENCY"
                  prompt="HLS.CURRENCY"/>
        <bm:field name="due_date" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="DUE_DATE"
                  prompt="CSH501.CSH_DUE_DATE"/>
        <bm:field name="residual_amount" databaseType="NUMBER" datatype="java.lang.Double"
                  physicalName="RESIDUAL_AMOUNT" prompt="CSH501.CSH_RESIDUAL_AMOUNT"/>
        <bm:field name="bp_name" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="bp_name"
        />
        <bm:field name="contract_id" databaseType="NUMBER" datatype="java.lang.Long"/>
        <bm:field name="cashflow_id" databaseType="NUMBER" datatype="java.lang.Long"/>
        <bm:field name="applied_pay_amount" databaseType="NUMBER" datatype="java.lang.Double"/>
        <bm:field name="received_amount" databaseType="NUMBER" datatype="java.lang.Double"/>
        <!--<bm:field name="project_number" databaseType="VARCHAR2" datatype="java.lang.String"-->
        <!--physicalName="PROJECT_NUMBER"/>-->
        <bm:field name="bp_code" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="bp_code"/>
        <bm:field name="times" databaseType="NUMBER" datatype="java.lang.Long" prompt="HLS.PERIOD_NUMBER"/>
        <bm:field name="search_term_1"/>
        <!--<bm:field name="serial_number"/>-->
        <bm:field name="bp_id_tenant_n"/>
        <bm:field name="bp_id_tenant_c"/>
        <bm:field name="vat_flag"/>
        <bm:field name="lessor_name"/>
        <bm:field name="con_business_type"/>
        <bm:field name="con_business_type_n"/>
        <!--<bm:field name="taxpayer_type"/>-->
        <!--<bm:field name="taxpayer_type_n"/>-->
        <!--<bm:field name="product_type_n"/>-->
        <!--<bm:field name="product_name_write"/>-->
        <bm:field name="cdd_list_id"/>
        <bm:field name="division"/>
        <bm:field name="division_n"/>
        <bm:field name="first_contract_day"/>
        <bm:field name="invoice_uploaded_flag_n"/>
        <bm:field name="pattern"/>
        <bm:field name="machine_number"/>
        <bm:field name="interface_name"/>
        <bm:field name="status_n"/>
        <bm:field name="price"/>
        <bm:field name="factory_price"/>
        <bm:field name="first_delay_flag"/>
        <bm:field name="first_delay_flag_n"/>
        <bm:field name="bp_id_agent_level1"/>
        <bm:field name="bp_id_agent_level1_n"/>
        <bm:field name="agent_extra_nam"/>
        <bm:field name="cf_61_write_off_flag"/>

        <bm:field name="cf_61_write_off_flag_n"/>
    </bm:fields>
    <bm:features>
        <s:bm-script><![CDATA[
            var cx = Packages.leaf.javascript.Context.getCurrentContext();
            Packages.leaf.plugin.script.engine.ScriptImportor.defineExternScript(cx, this, $ctx.getData(), "aut_authority_bm_validate.js");
        ]]></s:bm-script>
    </bm:features>
    <bm:query-fields>
        <!--<bm:query-field name="pay_contract_from" datatype="java.lang.String" queryExpression="t1.contract_number&gt;=${@pay_contract_from}"/>-->
        <!--<bm:query-field name="pay_contract_to" datatype="java.lang.String" queryExpression="t1.contract_number&lt;=${@pay_contract_to}"/>-->
        <bm:query-field name="contract_number" datatype="java.lang.String"
                        queryExpression="t1.contract_number like ${@contract_number}"/>
        <bm:query-field name="due_date_from" datatype="java.lang.String"
                        queryexpression="t1.due_date &gt;= ${@due_date_from}"/>
        <bm:query-field name="due_date_to" datatype="java.lang.String"
                        queryexpression="((t1.due_date &lt;= ${@due_date_to}) or (${@outflow_id} is not null)) "/>
        <bm:query-field name="due_amount_from" datatype="java.lang.String"
                        queryExpression="t1.due_amount&gt;=${@due_amount_from}"/>
        <bm:query-field name="due_amount_to" datatype="java.lang.String"
                        queryExpression="t1.due_amount&lt;=${@due_amount_to}"/>
        <bm:query-field name="project_name" datatype="java.lang.String"
                        queryExpression="t1.project_name LIKE ${@project_name}"/>
        <bm:query-field name="bp_code_tenant_from" datatype="java.lang.String"
                        queryExpression="t1.bp_code&gt;=${@bp_code_tenant_from}"/>
        <bm:query-field name="bp_code_tenant_to" datatype="java.lang.String"
                        queryExpression="t1.bp_code&lt;=${@bp_code_tenant_to}"/>
        <bm:query-field name="bp_code_tenant" datatype="java.lang.String"
                        queryExpression="t1.bp_id_tenant_c= ${@bp_code_tenant}"/>
        <bm:query-field name="bp_name_tenant" datatype="java.lang.String"
                        queryExpression="t1.bp_id_tenant_n= ${@bp_name_tenant}"/>
        <bm:query-field name="bp_id_agent_level1" datatype="java.lang.String"
                        queryExpression="t1.bp_id_agent_level1= ${@bp_id_agent_level1}"/>
        <bm:query-field name="bp_id_agent_level1_n" datatype="java.lang.String"
                        queryExpression="t1.bp_id_agent_level1_n= ${@bp_id_agent_level1_n}"/>
        <bm:query-field name="bp_id_tenant" datatype="java.lang.String"
                        queryExpression="t1.bp_id_tenant= ${@bp_id_tenant}"/>
        <bm:query-field name="currency" datatype="java.lang.String" queryExpression="t1.currency = ${@currency}"/>
        <bm:query-field name="cf_item" datatype="java.lang.String" queryExpression="t1.cf_item = ${@cf_item}"/>
        <bm:query-field name="serial_number" queryExpression="t1.serial_number like ${@serial_number}"/>
        <bm:query-field name="project_number_from" datatype="java.lang.String"
                        queryExpression="t1.project_number&gt;=${@project_number_from}"/>
        <bm:query-field name="project_number_to" datatype="java.lang.String"
                        queryExpression="t1.project_number&lt;=${@project_number_to}"/>
        <bm:query-field name="bp_id"
                        queryExpression="exists(select 1 from con_contract_bp ccb where ccb.contract_id=t1.contract_id and ccb.bp_id=${@bp_id})"/>
        <bm:query-field name="vat_flag" queryExpression="t1.vat_flag = ${@vat_flag}"/>
        <bm:query-field name="lessor_name" queryExpression="t1.lessor_name = ${@lessor_name}"/>
        <bm:query-field name="division" queryExpression="t1.division = ${@division}"/>
        <bm:query-field name="times" queryExpression="t1.times = ${@times}"/>
        <bm:query-field name="times_from" datatype="java.lang.String" queryExpression="t1.times&gt;=${@times_from}"/>
        <bm:query-field name="times_to" datatype="java.lang.String" queryExpression="t1.times&lt;=${@times_to}"/>
        <bm:query-field name="outflow_id" queryExpression="t1.cashflow_id = ${@outflow_id}"/>
        <bm:query-field name="invoice_uploaded_flag_n" queryExpression="t1.invoice_uploaded_flag_n = ${@invoice_uploaded_flag_n}"/>
        <bm:query-field name="first_delay_flag_n" queryExpression="t1.first_delay_flag_n = ${@first_delay_flag_n}"/>
        <bm:query-field name="cf_61_write_off_flag" queryExpression="t1.cf_61_write_off_flag = ${@cf_61_write_off_flag}"/>
    </bm:query-fields>
</bm:model>
