<?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
       /*  (select v.code_value_name
           from sys_code_values_v v
          where v.code = 'VENDER_NAME'
            and v.code_value = t1.vender_name) vender_name_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,
                --开票状态
                f.billing_status,
                 (SELECT code_value_name
                     FROM sys_code_values_v scv
                    WHERE scv.code = 'CON_BILLING_STATUS'
                       AND code_value= f.billing_status
                      AND scv.code_value_enabled_flag = 'Y'
                      AND scv.code_enabled_flag = 'Y') billing_status_n,

                --发票状态
               acp_invoice_pkg.return_invoice_status(p_contract_id=>t.contract_id) invoice_status_n,
                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,
                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,
                --add by shen
                (select bp_name
                   from hls_bp_master
                  where hls_bp_master.bp_id = t.bp_id_agent_level1) bp_id_agent_n,
                --查找厂商
                (SELECT bp_name FROM hls_bp_master h WHERE h.bp_id=hli.vender_id) vender_name_n,
                hli.vender_name,
                t.business_type,
                 (SELECT h.description
          FROM hls_business_type h
         WHERE h.business_type = t.business_type) AS business_type_n,
          f.due_amount-nvl(t.down_payment,0)-nvl(t.entrusted_amount,0) amount_tenant
           FROM con_contract_cashflow   f,
                hls_cashflow_item       h,
                con_contract_lease_item i,
                hls_lease_item          hli,
                hls_bp_master           hbm,
                con_contract            t
          WHERE i.contract_id = t.contract_id
            AND i.lease_item_id = hli.lease_item_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 NOT EXISTS (
               SELECT 1
                 FROM csh_payment_req_ln l,csh_payment_req_hd hd
                 WHERE l.ref_doc_line_id=f.cashflow_id
                  AND l.ref_doc_category='CONTRACT'
                  and l.payment_req_id=hd.payment_req_id
                  and hd.approval_status in ('NEW','REJECT','APPROVED','APPROVING','RETURN')
            )
            AND t.data_class = 'NORMAL'
       AND ('Y' = 'Y' AND EXISTS
        (SELECT 1
           FROM aut_trx_user_authorize a1, aut_owner_user_authorize a2
          WHERE a1.trx_category = 'CONTRACT'
            AND a1.trx_id = t.contract_id
            AND trunc(SYSDATE) BETWEEN a1.start_date AND
                nvl(a1.end_date, trunc(SYSDATE))
            AND a1.user_id = a2.owner_user_id
            AND a1.trx_category = a2.trx_category
            AND a2.authorized_user_id = ${/session/@user_id}
            AND trunc(SYSDATE) BETWEEN a2.start_date AND
                nvl(a2.end_date, trunc(SYSDATE))))
           AND t.contract_status  IN
              ('INCEPT')
              and t.sign_contract_status in ('SIGN','SIGNING')
                --测试用，cf_itme应当为0
            AND f.cf_item in (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="cdd_list_id"/>
        <bm:field name="bp_id_agent_n"/>
        <bm:field name="bp_id_tenant"/>
<!--        <bm:field name="vender_name"/>-->
        <bm:field name="vender_name_n"/>
        <bm:field name="division"/>
        <bm:field name="division_n"/>
        <bm:field name="business_type"/>
        <bm:field name="business_type_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:field name="billing_status_n"/>
        <bm:field name="invoice_status_n"/>
        <bm:field name="amount_tenant"/>
<!--        <bm:field name="bp_bank_account_id"/>-->
<!--        <bm:field name="vender_bank_account_id"/>-->
<!--        <bm:field name="vender_name"/>-->
<!--        <bm:field name="VENDER_BANK_ACCOUNT_NUMBER"/>-->
<!--        <bm:field name="bp_bank_account_name"/>-->
<!--        <bm:field name="bp_bank_account_num"/>-->
    </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:data-filters>
<!--        <bm:data-filter name="enabled_flag" enforceOperations="query" expression="(t1.enabled_flag=&apos;Y&apos;)"/>-->
    </bm:data-filters>
    <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"
                        queryExpression="t1.contract_number like &apos;%&apos;||${@contract_number}||&apos;%&apos;"/>
        <bm:query-field name="due_date_from"
                        queryExpression="t1.due_date &gt;= ${@due_date_from}"/>
        <bm:query-field name="due_date_to"
                        queryExpression="((t1.due_date &lt;= ${@due_date_to}) or (${@outflow_id} is not null)) "/>
        <bm:query-field name="due_amount_from"
                        queryExpression="t1.due_amount&gt;=${@due_amount_from}"/>
        <bm:query-field name="due_amount_to"
                        queryExpression="t1.due_amount&lt;=${@due_amount_to}"/>
        <bm:query-field name="project_name"
                        queryExpression="t1.project_name LIKE ${@project_name}"/>
        <bm:query-field name="bp_code_tenant_from"
                        queryExpression="t1.bp_code&gt;=${@bp_code_tenant_from}"/>
        <bm:query-field name="bp_code_tenant_to"
                        queryExpression="t1.bp_code&lt;=${@bp_code_tenant_to}"/>
        <bm:query-field name="bp_code_tenant"
                        queryExpression="t1.bp_id_tenant_c= ${@bp_code_tenant}"/>
        <bm:query-field name="bp_name_tenant"
                        queryExpression="t1.bp_id_tenant_n= ${@bp_name_tenant}"/>
        <bm:query-field name="bp_id_agent_level1"
                        queryExpression="t1.bp_id_agent_level1= ${@bp_id_agent_level1}"/>
        <bm:query-field name="bp_id_agent_level1_n"
                        queryExpression="t1.bp_id_agent_level1_n= ${@bp_id_agent_level1_n}"/>
        <bm:query-field name="bp_id_tenant"
                        queryExpression="t1.bp_id_tenant= ${@bp_id_tenant}"/>
        <bm:query-field name="currency" queryExpression="t1.currency = ${@currency}"/>
        <bm:query-field name="cf_item"  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"
                        queryExpression="t1.project_number&gt;=${@project_number_from}"/>
        <bm:query-field name="project_number_to"
                        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" queryExpression="t1.times&gt;=${@times_from}"/>
        <bm:query-field name="times_to" 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-field name="vender_name" queryExpression="t1.vender_name=${@vender_name}"/>
        <bm:query-field name="vender_name_n" queryExpression="t1.vender_name_n=${@vender_name_n}"/>
        <bm:query-field name="business_type" queryExpression="t1.business_type=${@business_type}"/>
    </bm:query-fields>
</bm:model>
