<?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.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.application_type_n,
       v.application_result_n,
       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
  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,
               (SELECT TO_CHAR(SUBSTR(wm_concat(cccr.interface_name), 1, 1000))
                  FROM con_contract_case_record cccr
                 WHERE cccr.document_id = t.contract_id
                   AND cccr.document_table = 'CON_CONTRACT') application_type_n,
               (SELECT TO_CHAR(SUBSTR(wm_concat(cccr.status_n), 1, 1000))
                  FROM con_contract_case_record_lv cccr
                 WHERE cccr.document_id = t.contract_id
                   AND cccr.document_table = 'CON_CONTRACT') application_result_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,

               (CASE
                 WHEN f.cf_item IN (5, 6, 993, 998) THEN
                  (SELECT t.bank_account_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 t.sap_bvtyp = DECODE(t.division,
                                               '10',
                                               'CNYA',
                                               '00',
                                               'CNYA',
                                               '01',
                                               'CNYB')
                      AND rownum = 1)
                 WHEN f.cf_item IN (992, 994, 995, 997) THEN
                  (SELECT t.bank_account_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 t.sap_bvtyp = 'CNYC'
                      AND rownum = 1)
                 ELSE
                  NULL
               END) 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,
               -- (SELECT
               -- vl.price
               -- FROM
               -- hls_lease_products_vl vl
               -- WHERE
               -- vl.lease_products_code =
               -- (SELECT
               -- l.item_frame_number
               -- FROM
               -- con_contract_lease_item l
               -- WHERE
               -- l.contract_id= t.contract_id
               -- )
               -- )price
               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,

               DECODE(t.division, '10', 'CNYA', '00', 'CNYA', '01', 'CNYB') 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 = DECODE(t.division,
                                             '10',
                                             'CNYA',
                                             '00',
                                             'CNYA',
                                             '01',
                                             'CNYB')) sap_bvtyp_n,
               i.pattern,
               i.machine_number
          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 = decode(f.cf_item,
                                         5,
                                         'MAIN',
                                         6,
                                         'MAIN',
                                         992,
                                         'MAIN',
                                         997,
                                         'MAIN',
                                         993,
                                         'ACCESSORY',
                                         998,
                                         'ACCESSORY',
                                         994,
                                         'ACCESSORY',
                                         995,
                                         'ACCESSORY',
                                         '')
         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>
