<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: qwm
    $Date: 2013-5-24 下午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.vender_id,
                       v.other_payment2,
                       'NEW' approval_status,
       v.invoice_uploaded_flag,
       v.invoice_uploaded_flag_n,
       v.bp_id_agent_level1,
       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.contract_name,
       v.project_name,
       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_ID_AGENT_N,
       v.bp_name,
       v.bp_code,
       v.applied_pay_amount,
       --v.residual_amount         AS amount,
       0.00 AS    amount,
       --v.due_amount- NVL(v.applied_pay_amount,0) AS amount,
       v.bp_category_desc,
       v.search_term_1,
       '' bank_full_name, --宏菱需要置为空 bank_key
       v.irr,
       v.price,
       v.factory_price,
        --代理店账户
       (SELECT hbbc.bank_account_name
          FROM hls_bp_master_bank_account hbbc
          WHERE hbbc.bank_account_id=v.bp_bank_account_id ) bp_bank_account_name,
            --代理店账户
       v.bp_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 ) bp_bank_account_num,
         --代理店账号
       (SELECT hbbc.bank_account_num
          FROM hls_bp_master_bank_account hbbc
          WHERE hbbc.bank_account_id=v.bp_bank_account_id ) bp_bank_account_num_n,
       --代理店开户行
     (SELECT hbbc.bank_full_name
          FROM hls_bp_master_bank_account hbbc
          WHERE hbbc.bank_account_id=v.bp_bank_account_id ) bp_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.pattern,
       v.machine_number,
        v.repur_con_amt,
        v.repur_con_ddct_amt,
        v.entrusted_amount,
        v.amount_tenant,
        v.write_off_date,
        v.write_off_amount,
        v.division,
        v.finance_amount
  FROM (SELECT t.contract_id,
                (SELECT hbbc.bank_account_id
                       FROM hls_bp_master_bank_account hbbc
                        WHERE hbbc.bp_id=t.bp_id_agent_level1 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,
               t.contract_number,
               t.contract_name,
               t.document_category,
               t.search_term_1,
               t.bp_id_agent_level1,
               p.project_name,
               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,
               TO_CHAR(f.due_date, 'yyyy-mm-dd') due_date,
               NVL(f.due_amount, 0) - NVL(f.received_amount, 0) residual_amount,
               (select c.bp_id_tenant
                         from con_contract c
                      where c.contract_id=t.contract_id ) bp_id,
               DECODE(f.cf_item,
                      '62',
                      NULL,
                      (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 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_ID_AGENT_N,
               (SELECT hbm.bp_name
                  FROM hls_bp_master hbm
                 WHERE hbm.bp_id = t.bp_id_tenant) bp_id_tenant_n,
               DECODE(f.cf_item,
                      '62',
                      NULL,
                      (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,
               i.price,
               i.factory_price,
               --获得厂商的相关信息
                    (select hli.vender_id
                              from hls_lease_item hli
                             where hli.lease_item_id =
                                   (select lease_item_id
                                      from con_contract_lease_item c
                                     where c.contract_id = t.contract_id)) vender_id,
               --首付款抵充金
               (select nvl(hd.other_payment2,0)
                  from hls_fin_calculator_hd hd
                 where hd.calc_session_id =
                       (select PQ.CALC_SESSION_ID
                          from prj_quotation pq
                         where pq.document_id = (select cc.project_id
                                                   from con_contract cc
                                                  where cc.contract_id =f.contract_id)
                and pq.document_category = 'PROJECT')) other_payment2,
               i.pattern,
               i.machine_number,
               nvl((select sum(due_amount-nvl(received_amount,0)) from con_contract_cashflow where contract_id=t.repur_contract_id and cf_item=250),0)repur_con_amt,
               nvl((select sum(due_amount-nvl(received_amount,0)) from con_contract_cashflow where contract_id=t.repur_contract_id and cf_item=250),0)repur_con_ddct_amt,
               nvl(t.entrusted_amount,0) entrusted_amount,
              decode(t.business_type,'LEASEBACK',(f.due_amount-nvl((select to_number(hd.other_payment2)
                                                                    from hls_fin_calculator_hd hd
                                                                    where hd.calc_session_id = t.calc_session_id),0)-nvl(t.entrusted_amount,0)),'LEASE',0) amount_tenant,
              (select max(cwo.write_off_date) write_off_date
               from csh_write_off cwo
               where cwo.write_off_type='DEPOSIT_ENTRY' and cwo.contract_id=t.contract_id) write_off_date,
        nvl((select  sum(cwo.write_off_due_amount) write_off_amount
                from csh_write_off cwo
                 where cwo.write_off_type='DEPOSIT_ENTRY' and cwo.contract_id=t.contract_id),0) write_off_amount,
                 t.finance_amount,
               t.division
          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
          left outer join prj_project p
            on t.project_id = p.project_id
          left outer join con_contract_lease_item i
            on i.contract_id = t.contract_id
           and i.equipment_type ='MAIN'
         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>
