<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: lz
    $Date: 2024年05月14日 11:18:01
    $Revision: 1.0
    $Purpose:
-->
<bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm" needAccessControl="false">
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[
select p.project_id,
       p.project_number,
       --p.project_type,
       p.division,
       c.contract_id,
       c.contract_number,
       (select h.bp_name from hls_bp_master h where h.bp_id = p.bp_id_tenant) tenant_name,
       (select h.bp_name
          from hls_bp_master h
         where h.bp_id = p.INVOICE_AGENT_ID) agent_name,
       (SELECT v.code_value_name
          FROM sys_code_values_v v
         WHERE v.code = 'PRJ501_PRJ_STATUS'
           AND v.code_value = p.project_status) project_status_n,
       (SELECT v.code_value_name
          FROM sys_code_values_v v
         WHERE v.code = 'CON500_CONTRACT_STATUS'
           AND v.code_value = c.contract_status) contract_status_n,
       nvl(pi.machine_number, pi.machine_number_70) machine_number,
       pi.project_lease_item_id
  from prj_project p, prj_project_lease_item pi, con_contract c
 where p.project_id = pi.project_id
   and p.project_id = c.project_id(+)
   and p.project_id <> ${@project_id}
   and c.data_class(+) = 'NORMAL'
   and c.contract_status(+) not in ('CANCEL', 'REPUR', 'ET', 'TERMINATE')
   And p.project_status <> 'CLOSED'
   And nvl(p.secondary_lease, 'NO') = 'NO'

   and (nvl(pi.machine_number, pi.machine_number_70) in
       (select nvl(ppl.asset_num,
                    nvl(ppi.machine_number, ppi.machine_number_70))
           from prj_project                 pp,
                prj_project_lease_item      ppi,
                prj_project_lease_item_list ppl
          where pp.project_id = ppi.project_id
            and instr(nvl(pi.machine_number, pi.machine_number_70), '附件') = 0
            and pp.project_id = ${@project_id}
            and ppi.project_lease_item_id = ppl.project_lease_item_id(+)) or
       exists
        (select 1
           from con_contract_lease_item cl
          where cl.contract_id = c.contract_id
            and instr(nvl(cl.machine_number, cl.machine_number_70), '附件') = 0
            and nvl(cl.machine_number, cl.machine_number_70) in
                (select nvl(ppl.asset_num,
                            nvl(ppi.machine_number, ppi.machine_number_70))
                   from prj_project                 pp,
                        prj_project_lease_item      ppi,
                        prj_project_lease_item_list ppl
                  where pp.project_id = ppi.project_id
                    and pp.project_id = ${@project_id}
                    and ppi.project_lease_item_id =
                        ppl.project_lease_item_id(+))) or exists
        (select 1
           from prj_project_lease_item_list ppl
          where ppl.project_lease_item_id = pi.project_lease_item_id
            and instr(ppl.asset_num, '附件') = 0
            and ppl.asset_num in
                (select nvl(ppl.asset_num,
                            nvl(ppi.machine_number, ppi.machine_number_70))
                   from prj_project                 pp,
                        prj_project_lease_item      ppi,
                        prj_project_lease_item_list ppl
                  where pp.project_id = ppi.project_id
                    and pp.project_id = ${@project_id}
                    and ppi.project_lease_item_id =
                        ppl.project_lease_item_id(+))))


                ]]>
            </bm:query-sql>
        </bm:operation>
        <bm:operation name="execute">
            <bm:parameters>
                <bm:parameter name="repeat_flag" dataType="java.lang.String" input="false" output="true"/>
                <bm:parameter name="project_id" dataType="java.lang.Long" input="true" output="false" required="true"/>
            </bm:parameters>
            <bm:update-sql><![CDATA[
                begin
                ${@repeat_flag} := prj_project_check_pkg.check_prj_machine_number(${@project_id});
                end;]]>
            </bm:update-sql>
        </bm:operation>
    </bm:operations>
</bm:model>
