<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: trd
    $Date: 2023-9-28 下午03:50:31
    $Revision: 1.0  
    $Purpose: 
-->
<bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm">
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[
         select rownum, v.*
           from (select
                          (select cc.contract_number
                                   from con_contract cc
                                  where cc.contract_id =
                                        con_contract_pkg.get_first_contract_id(c.project_id)) contract_number,
                         (select cc.search_term_1
                           from con_contract cc
                          where cc.contract_id =
                                con_contract_pkg.get_first_contract_id(c.project_id)) vender_contract_number,
                        (select pp.project_number from prj_project pp where pp.project_id=c.project_id) project_number,
                        c.contract_id,
                        c.bp_id_tenant,
                        c.project_id,
                          (select b.bp_name
                           from con_contract_bp b
                          where b.contract_id = c.contract_id
                            and b.bp_category = 'TENANT') bp_id_tenant_n,
                        hbm.bp_name,
                         (select cph.payment_req_number
                           from csh_payment_req_hd cph
                          where cpr.payment_req_id = cph.payment_req_id) payment_req_number,
                        cpr.ref_doc_line_id cashflow_id,
                        (select times
                           from con_contract_cashflow ccc
                          where ccc.cashflow_id = cpr.ref_doc_line_id) times,
                        (select ccc.due_amount
                           from con_contract_cashflow ccc
                          where ccc.cashflow_id = cpr.ref_doc_line_id) due_amount,
                        (select description
                           from hls_cashflow_item hci, con_contract_cashflow ccc
                          where hci.cf_item = ccc.cf_item
                            and hci.cf_type = ccc.cf_type
                            and ccc.cashflow_id = cpr.ref_doc_line_id) CF_DESCRIPTION,
                        cpr.vender_name vender_name,
                        cpr.vender_bank_account_id,
                        cpr.vender_bank_account_number vender_bank_account_number,
                        cpr.vender_bank_account_number vender_bank_account_number_n,
                        cpr.vender_bank_account_name vender_bank_account_name,
						cpr.vender_bank_full_name    vender_bank_full_name,
                        cpr.amount,
                        c.lease_item_amount,
                        --发票状态
                       acp_invoice_pkg.return_invoice_status(p_contract_id => c.contract_id) invoice_status_n,
                      (SELECT gc.currency_name
                           FROM gld_currency_v gc
                          WHERE gc.currency_code = cpr.currency_code) currency_name,
                        cpr.payment_req_ln_id,
                        cpr.payment_req_id,
                        cpr.payment_req_ln_type,
                        cpr.ref_doc_id,
                        cpr.ref_doc_line_id,
                        cpr.ref_doc_category,
                        c.division,
                       (select pp.ref_d10 from prj_project pp where pp.project_id=c.project_id) due_date,
                        (select count(*)
                        from prj_project_lease_item pp
                       where pp.project_id=c.project_id
                        ) lease_item_count,
                      (select nvl(sum(nvl(pp.ACTUAL_PRICE, 0) * nvl(pp.BALLOON_RATIO,0)),0)
                            from prj_project_lease_item pp
                           where pp.project_id=c.project_id
                           ) insurance_fee,
                       cpr.insurance_fee_ddct
                   from csh_payment_req_ln      cpr,
                        con_contract            c,
                        hls_bp_master           hbm,
                        con_contract_cashflow   ccc
                  where cpr.ref_doc_category = 'CONTRACT'
                     and c.contract_id = cpr.ref_doc_id
                    and hbm.bp_id = c.bp_id_tenant
                    AND ccc.contract_id = c.contract_id
                    AND ccc.cf_item = 0) v
            #WHERE_CLAUSE#
                ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
    <bm:data-filters>
        <bm:data-filter enforceOperations="query" expression="v.payment_req_id =${@payment_req_id}"/>
    </bm:data-filters>
 </bm:model>
