<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: trd
    $Date: 2023-9-26 下午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 *
              from (SELECT  t.contract_id,
                            --con_contract_pkg.get_first_contract_id(t.project_id) contract_id,
                           (select cc.contract_number
                              from con_contract cc
                             where cc.contract_id =
                                   con_contract_pkg.get_first_contract_id(t.project_id)) contract_number,
                           (select cc.search_term_1
                              from con_contract cc
                             where cc.contract_id =
                                   con_contract_pkg.get_first_contract_id(t.project_id)) vender_con_number,
                           (select cc.contract_status
                              from con_contract cc
                             where cc.contract_id =
                                   con_contract_pkg.get_first_contract_id(t.project_id)) contract_status,
                           (select sc.code_value_name
                              from con_contract cc, sys_code_values_v sc
                             where sc.code_value = cc.contract_status
                               and sc.code = 'CON500_CONTRACT_STATUS'
                               and cc.contract_id =
                                   con_contract_pkg.get_first_contract_id(t.project_id)) contract_status_desc,
                           f.cashflow_id,
                           f.times,
                           t.bp_id_tenant,
                           hbm.bp_id tenant_bp_id,
                           h.description cf_item_desc,
                           h.cf_item,
                           f.due_amount,
                           --发票状态
                           acp_invoice_pkg.return_invoice_status(p_contract_id => t.contract_id) invoice_status_n,
                           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,
                           (select pp.project_number
                              from prj_project pp
                             where pp.project_id = t.project_id) project_number,
                           (select bp.bp_name
                              from hls_bp_master bp, prj_project p
                             where bp.bp_id = p.bp_id_tenant
                               and p.project_id = t.project_id) vender_name_n,
                           (select nvl(sum(li.ACTUAL_PRICE), 0)
                              from prj_project_lease_item li
                             where li.project_id = t.project_id) lease_item_amount,
                           (select count(1)
                              from prj_project_lease_item li
                             where li.project_id = t.project_id) lease_item_count,
                           (select to_char(p.ref_d10, 'yyyy-mm-dd')
                              from prj_project p
                             where p.project_id = t.project_id) plan_order_date,
                           (SELECT nvl(sum(nvl(li.ACTUAL_PRICE, 0) *
                                           nvl(li.BALLOON_RATIO, 0)),
                                       0)
                              from prj_project_lease_item li
                             where li.project_id = t.project_id) insurance_fee
                      FROM con_contract_cashflow f,
                           hls_cashflow_item     h,
                           hls_bp_master         hbm,
                           con_contract          t
                     WHERE 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 t.business_type = 'OTHERS'
                       and t.document_type = 'PUR_ORDER'
					   and t.division='95'
					   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 = con_contract_pkg.get_first_contract_id(t.project_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 f.cf_item in (0)) t1
           #WHERE_CLAUSE#
           #ORDER_BY_CLAUSE#

            ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
  <bm:query-fields>
  <bm:query-field name="project_number"
                  queryExpression="t1.project_number like &apos;%&apos;||${@project_number}||&apos;%&apos;"/>
  <bm:query-field name="plan_order_date_from"
                  queryExpression="t1.plan_order_date &gt;= ${@plan_order_date_from}"/>
  <bm:query-field name="plan_order_date_to"
                  queryExpression="t1.plan_order_date &lt;= ${@plan_order_date_to}"/>
  <bm:query-field name="lease_item_amount"
                  queryExpression="t1.due_amount&gt;=${@lease_item_amount}"/>
  <bm:query-field name="bp_id_agent_n" queryExpression="t1.vender_name_n=${@bp_id_agent_n}"/>
  <bm:query-field name="vender_name_n" queryExpression="t1.vender_name_n=${@vender_name_n}"/>
  <bm:query-field name="invoice_status_n"
                             queryExpression="t1.invoice_status_n like &apos;%&apos;||${@invoice_status_n}||&apos;%&apos;"/>
  <bm:query-field name="contract_number" queryExpression="t1.contract_number=${@contract_number}"/>
  </bm:query-fields>
  <bm:data-filters>
    <bm:data-filter name="contract_status" expression="(nvl(t1.contract_status,'NEW') = 'INCEPT')"/>
  </bm:data-filters>
</bm:model>
