<?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" xmlns:f="leaf.database.features" alias="t1"
          baseTable="CSH_PAYMENT_REQ_LN" needAccessControl="false">
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[
           SELECT v.contract_id,
                  v.project_id,
                 'NEW' approval_status,
                 v.invoice_uploaded_flag,
                 v.invoice_uploaded_flag_n,
                 v.lease_item_count lease_item_count,
                 v.times,
                 v.cashflow_id,
                 v.cashflow_id             ref_doc_line_id,
                 v.contract_id             ref_doc_id,
                 v.document_category       ref_doc_category,
                 v.contract_number,
				 v.vender_contract_number,
                 v.project_number,
                 v.insurance_fee,
                 v.tenant_bp_id,
                 v.tenant_bp_id bp_id_tenant,
                 v.bp_id_tenant_n,
                 v.cf_description,
                 v.due_amount,
                 v.currency_code,
                 v.currency_name,
                 v.due_date,
                 v.residual_amount,
                 v.bp_id,
                 v.bp_code                 bp_id_n,
                 v.bp_name,
                 v.bp_code,
                 v.applied_pay_amount,
                 v.amount  amount,
                 v.insurance_fee_ddct insurance_fee_ddct,
                 v.bp_category_desc,
                 '' bank_full_name, --宏菱需要置为空 bank_key
                 v.irr,
                  --厂商账户
                 (SELECT hbbc.bank_account_name
                    FROM hls_bp_master_bank_account hbbc
                    WHERE hbbc.bank_account_id=v.bp_bank_account_id ) vender_bank_account_name,
                  v.bp_bank_account_id vender_bank_account_id,
                 --厂商账号
                 (SELECT hbbc.bank_account_num
                    FROM hls_bp_master_bank_account hbbc
                    WHERE hbbc.bank_account_id=v.bp_bank_account_id ) vender_bank_account_number,
                   --厂商账号
                 (SELECT hbbc.bank_account_num
                    FROM hls_bp_master_bank_account hbbc
                    WHERE hbbc.bank_account_id=v.bp_bank_account_id ) vender_bank_account_number_n,
			     (SELECT hbbc.bank_full_name
                                     FROM hls_bp_master_bank_account hbbc
                                     WHERE hbbc.bank_account_id=v.bp_bank_account_id ) vender_bank_full_name,
                 (SELECT hb.sap_bvtyp
                    FROM hls_bp_master_bank_account hb
                   WHERE hb.bank_account_id = v.bp_bank_account_id) sap_bvtyp,
                 (SELECT v.code_value_name
                    FROM sys_code_values_v v
                   WHERE v.code = 'HLS_SAP_BVTYP'
                     AND v.code_enabled_flag = 'Y'
                     AND v.code_value_enabled_flag = 'Y'
                     AND v.code_value =
                         (SELECT hb.sap_bvtyp
                            FROM hls_bp_master_bank_account hb
                           WHERE hb.bank_account_id = v.bp_bank_account_id)) sap_bvtyp_n,
                   v.division,
          		v.finance_amount,
          		v.lease_item_amount
             FROM (SELECT
                          t.contract_id,
                          --con_contract_pkg.get_first_contract_id(t.project_id) contract_id,
                          t.project_id,
                          (SELECT hbbc.bank_account_id
                                 FROM hls_bp_master_bank_account hbbc
                                  WHERE hbbc.bp_id=t.bp_id_tenant AND ROWNUM=1)bp_bank_account_id,
                         (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,
                         f.times,
                         f.cashflow_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_contract_number,
                         t.document_category,
                         (select pp.project_number from prj_project pp where pp.project_id=t.project_id) project_number,
                         t.bp_id_tenant tenant_bp_id,
                         h.description cf_description,
                         f.due_amount,
                         t.currency currency_code,
                         '' bp_bank_account_id_n,
                         (SELECT t.bank_branch_name
                            FROM hls_bp_master_bank_account_v t
                           WHERE t.bp_id =
                                 NVL(${@payment_bp_id},
                                     csh_payment_req_pkg.get_bp_id(t.contract_id))
                             AND t.enabled_flag = 'Y'
                             AND rownum = 1) bank_full_name,
                         (SELECT gc.currency_name
                            FROM gld_currency_v gc
                           WHERE gc.currency_code = t.currency) currency_name,
                          (select pp.ref_d10 from prj_project pp where pp.project_id=t.project_id) due_date,
                         NVL(f.due_amount, 0) - NVL(f.received_amount, 0) residual_amount,
                         --本次申请金额默认为采购总额-厂商质保金
                         NVL(f.due_amount, 0)-
                         (select nvl(sum(nvl(pp.ACTUAL_PRICE, 0) * nvl(pp.BALLOON_RATIO,0)),0)
                               from prj_project_lease_item pp
                              where pp.project_id=t.project_id
                              ) amount,
                         --默认抵扣金额：厂商质保金
                         (select nvl(sum(nvl(pp.ACTUAL_PRICE, 0) * nvl(pp.BALLOON_RATIO,0)),0)
                                                        from prj_project_lease_item pp
                                                       where pp.project_id=t.project_id
                                                       ) insurance_fee_ddct,
                          (select c.bp_id_tenant
                                   from con_contract c
                                where c.contract_id=t.contract_id ) bp_id,
                             (SELECT b.bp_name
                                   FROM hls_bp_master b
                                  WHERE b.bp_id =
                                        NVL(${@payment_bp_id},
                                            csh_payment_req_pkg.get_bp_id(t.contract_id))) bp_name,
                           (SELECT hbm.bp_name
                            FROM hls_bp_master hbm
                           WHERE hbm.bp_id = t.bp_id_tenant) bp_id_tenant_n,
                        (SELECT b.bp_code
                           FROM hls_bp_master b
                          WHERE b.bp_id =
                                NVL(${@payment_bp_id},
                                    csh_payment_req_pkg.get_bp_id(t.contract_id))) bp_code,
                         (SELECT b.bp_category_name
                            FROM hls_bp_master_v b
                           WHERE b.bp_id =
                                 NVL(${@payment_bp_id},
                                     csh_payment_req_pkg.get_bp_id(t.contract_id))) bp_category_desc,
                         (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.irr,
                       t.division,
          			   t.finance_amount,
          			   t.lease_item_amount,
          			   (select count(*)
                                 from prj_project_lease_item pp
                                where pp.project_id=t.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=t.project_id
                                                        ) insurance_fee
          			   FROM con_contract_cashflow f
                    join con_contract t
                      on t.contract_id = f.contract_id
                    join hls_cashflow_item h
                      on f.cf_item = h.cf_item
                      where EXISTS (SELECT 1
                            FROM CSH_PAYMENT_CASHFLOW_ID_TEMP tmp
                           WHERE tmp.session_id = ${/session/@session_id}
                             AND tmp.cashflow_id = f.cashflow_id)) v
                             #WHERE_CLAUSE#
                ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
</bm:model>
