<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: xuls  
    $Date: 2017-1-4 下午03:42:50  
    $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
                    v.*
                FROM
                    (SELECT
                        rank() over(ORDER BY h.payment_req_number DESC) rk,
                        h.payment_req_number,
                        h.payment_req_id,
                        h.created_by,
                        (SELECT su.description FROM sys_user su WHERE su.user_id = h.created_by
                        ) req_man,
                        (SELECT
                            eou.description
                        FROM
                            sys_user su,
                            exp_employees ee,
                            exp_employee_assigns ea,
                            exp_org_position_vl eop,
                            exp_org_unit_vl eou
                        WHERE
                            su.user_id               = h.created_by AND
                            su.employee_id           = ee.employee_id AND
                            ee.employee_id           = ea.employee_id AND
                            ea.primary_position_flag = 'Y' AND
                            eop.position_id          = ea.position_id AND
                            eop.unit_id              = eou.unit_id
                        ) unit_name,
                        TO_CHAR(
                        (SELECT
                            SUM(NVL(rl.amount, 0) - NVL(
                            (SELECT
                                SUM(amount)
                            FROM
                                csh_payment_req_ln_ddct ld
                            WHERE
                                ld.payment_req_ln_id = rl.payment_req_ln_id
                            ), 0))
                        FROM
                            csh_payment_req_ln rl
                        WHERE
                            rl.payment_req_id = h.payment_req_id
                        ), 'FM999,999,999,990.00') sum_act_amount,
                        (SELECT
                            SUM(DECODE(rl.amount_paid, '0', NULL, rl.amount_paid) - NVL(
                            (SELECT
                                SUM(amount)
                            FROM
                                csh_payment_req_ln_ddct ld
                            WHERE
                                ld.payment_req_ln_id = rl.payment_req_ln_id
                            ), 0))
                        FROM
                            csh_payment_req_ln rl
                        WHERE
                            rl.payment_req_id = h.payment_req_id
                        ) sum_amount_paid,
                        (SELECT
                            bm.bp_name
                        FROM
                            csh_payment_req_ln l,
                            con_contract c,
                            hls_bp_master bm
                        WHERE
                            l.payment_req_id = h.payment_req_id AND
                            l.ref_doc_id     = c.contract_id AND
                            rownum           = 1 AND
                            bm.bp_id         = c.bp_id_tenant
                        ) bp_name,
                        h.bp_bank_account_name,
                        h.bp_bank_account_num,
                        NVL(h.bank_full_name,
                        (SELECT
                            hbmba.bank_branch_name
                        FROM
                            hls_bp_master_bank_account hbmba
                        WHERE
                            hbmba.bank_account_id = h.bp_bank_account_id
                        )) bp_branch_name,
                        fnd_convert_to_chinese_pkg.amount_convert_to_chinese(
                        (SELECT
                            SUM(NVL(rl.amount, 0) - NVL(
                            (SELECT
                                SUM(amount)
                            FROM
                                csh_payment_req_ln_ddct ld
                            WHERE
                                ld.payment_req_ln_id = rl.payment_req_ln_id
                            ), 0))
                        FROM
                            csh_payment_req_ln rl
                        WHERE
                            rl.payment_req_id = h.payment_req_id
                        )) sum_act_amount_big,
                        (SELECT
                            li.item_frame_number
                        FROM
                            csh_payment_req_ln l,
                            con_contract_lease_item li
                        WHERE
                            l.payment_req_id = h.payment_req_id AND
                            l.ref_doc_id     = li.contract_id AND
                            rownum           = 1
                        ) item_frame_number,
                        '电汇' payment_method_id_n,
                        (SELECT
                            c.lease_start_date
                        FROM
                            csh_payment_req_ln l,
                            con_contract c
                        WHERE
                            l.payment_req_id = h.payment_req_id AND
                            l.ref_doc_id     = c.contract_id AND
                            rownum           = 1
                        ) lease_start_date,
                        (SELECT
                            c.contract_number
                        FROM
                            csh_payment_req_ln l,
                            con_contract c
                        WHERE
                            l.payment_req_id = h.payment_req_id AND
                            l.ref_doc_id     = c.contract_id AND
                            rownum           = 1
                        ) contract_number,
                        (SELECT
                            cc.division_n
                        FROM
                            csh_payment_req_ln l,
                            con_contract_lv cc
                        WHERE
                            l.payment_req_id = h.payment_req_id AND
                            l.ref_doc_id     = cc.contract_id AND
                            rownum           = 1
                        ) division_n,
                        (SELECT
                            cc.product_name
                        FROM
                            csh_payment_req_ln l,
                            con_contract_lv cc
                        WHERE
                            l.payment_req_id = h.payment_req_id AND
                            l.ref_doc_id     = cc.contract_id AND
                            rownum           = 1
                        ) product_id_n,
                        h.wfl_instance_id,
                        TO_CHAR(
                        (SELECT
                            NVL(SUM(NVL(c.finance_amount, 0)), 0)
                        FROM
                            con_contract c
                        WHERE
                            c.data_class       = 'NORMAL' AND
                            c.contract_status <> 'CANCEL' AND
                            c.contract_id      =
                            (SELECT DISTINCT
                                t.ref_doc_id
                            FROM
                                csh_payment_req_ln t
                            WHERE
                                t.payment_req_id = h.payment_req_id
                            )
                        ), 'FM999,999,999,990.00') AS finance_amount, --融资额
                        TO_CHAR(
                        (SELECT
                            NVL(SUM(NVL(c.due_amount, 0)), 0)
                        FROM
                            con_contract_cashflow c
                        WHERE
                            c.cf_item     = 34 AND
                            c.contract_id =
                            (SELECT DISTINCT
                                t.ref_doc_id
                            FROM
                                csh_payment_req_ln t
                            WHERE
                                t.payment_req_id = h.payment_req_id
                            ) AND
                            c.cf_status = 'RELEASE'
                        ), 'FM999,999,999,990.00') AS consulting_fee, --咨询费
                        TO_CHAR(
                        (SELECT
                            NVL(SUM(NVL(c.due_amount, 0)), 0)
                        FROM
                            con_contract_cashflow c
                        WHERE
                            c.cf_item     = 51 AND
                            c.contract_id =
                            (SELECT DISTINCT
                                t.ref_doc_id
                            FROM
                                csh_payment_req_ln t
                            WHERE
                                t.payment_req_id = h.payment_req_id
                            ) AND
                            c.cf_status = 'RELEASE'
                        ), 'FM999,999,999,990.00') AS bond, --保证金
                        TO_CHAR(
                        (SELECT
                            NVL(SUM(NVL(c.due_amount, 0)), 0)
                        FROM
                            con_contract_cashflow c
                        WHERE
                            c.cf_item     = 3 AND
                            c.contract_id =
                            (SELECT DISTINCT
                                t.ref_doc_id
                            FROM
                                csh_payment_req_ln t
                            WHERE
                                t.payment_req_id = h.payment_req_id
                            ) AND
                            c.cf_status = 'RELEASE'
                        ), 'FM999,999,999,990.00') AS service_charge, --手续费
                        TO_CHAR(
                        (SELECT
                            NVL(SUM(NVL(c.due_amount, 0)), 0)
                        FROM
                            con_contract_cashflow c
                        WHERE
                            c.cf_item     = 81 AND
                            c.contract_id =
                            (SELECT DISTINCT
                                t.ref_doc_id
                            FROM
                                csh_payment_req_ln t
                            WHERE
                                t.payment_req_id = h.payment_req_id
                            ) AND
                            c.cf_status = 'RELEASE'
                        ), 'FM999,999,999,990.00') AS gps_fee, --GPS费-支付
                        (SELECT
                            NVL(ar.comment_text_out, '同意!')
                        FROM
                            zj_wfl_approve_record ar,
                            zj_wfl_workflow_node wn
                        WHERE
                            ar.instance_id = h.wfl_instance_id AND
                            ar.node_id     = wn.node_id AND
                            wn.node_desc   = '放款初审岗审批' AND
                            rownum         = 1
                        ) comment_text_out1,
                        (SELECT
                            NVL(ar.comment_text_out, '同意!')
                        FROM
                            zj_wfl_approve_record ar,
                            zj_wfl_workflow_node wn
                        WHERE
                            ar.instance_id = h.wfl_instance_id AND
                            ar.node_id     = wn.node_id AND
                            wn.node_desc   = '保险确认节点' AND
                            rownum         = 1
                        ) comment_text_out2,
                        (SELECT
                            NVL(ar.comment_text_out, '同意!')
                        FROM
                            zj_wfl_approve_record ar,
                            zj_wfl_workflow_node wn
                        WHERE
                            ar.instance_id = h.wfl_instance_id AND
                            ar.node_id     = wn.node_id AND
                            wn.node_desc   = 'GPS确认节点' AND
                            rownum         = 1
                        ) comment_text_out3,
                        (SELECT
                            to_char(ar.creation_date,'yyyy-mm-dd hh24:mi:ss')
                        FROM
                            zj_wfl_approve_record ar,
                            zj_wfl_workflow_node wn
                        WHERE
                            ar.instance_id = h.wfl_instance_id AND
                            ar.node_id     = wn.node_id AND
                            wn.node_desc   = '放款初审岗审批' AND
                            rownum         = 1
                        ) date1,
                        (SELECT
                            to_char(ar.creation_date,'yyyy-mm-dd hh24:mi:ss')
                        FROM
                            zj_wfl_approve_record ar,
                            zj_wfl_workflow_node wn
                        WHERE
                            ar.instance_id = h.wfl_instance_id AND
                            ar.node_id     = wn.node_id AND
                            wn.node_desc   = '保险确认节点' AND
                            rownum         = 1
                        ) date2,
                        (SELECT
                            to_char(ar.creation_date,'yyyy-mm-dd hh24:mi:ss')
                        FROM
                            zj_wfl_approve_record ar,
                            zj_wfl_workflow_node wn
                        WHERE
                            ar.instance_id = h.wfl_instance_id AND
                            ar.node_id     = wn.node_id AND
                            wn.node_desc   = 'GPS确认节点' AND
                            rownum         = 1
                        ) date3,
                        (SELECT
                            ee.name
                        FROM
                            zj_wfl_approve_record ar,
                            zj_wfl_workflow_node wn,
                            sys_user su,
                            exp_employees ee
                        WHERE
                            ar.instance_id = h.wfl_instance_id AND
                            ar.node_id     = wn.node_id AND
                            wn.node_desc   = '放款初审岗审批' AND
                            ar.created_by  = su.user_id AND
                            ee.employee_id = su.employee_id AND
                            rownum         = 1
                        ) user1,
                        (SELECT
                            ee.name
                        FROM
                            zj_wfl_approve_record ar,
                            zj_wfl_workflow_node wn,
                            sys_user su,
                            exp_employees ee
                        WHERE
                            ar.instance_id = h.wfl_instance_id AND
                            ar.node_id     = wn.node_id AND
                            wn.node_desc   = '保险确认节点' AND
                            ar.created_by  = su.user_id AND
                            ee.employee_id = su.employee_id AND
                            rownum         = 1
                        ) user2,
                        (SELECT
                            ee.name
                        FROM
                            zj_wfl_approve_record ar,
                            zj_wfl_workflow_node wn,
                            sys_user su,
                            exp_employees ee
                        WHERE
                            ar.instance_id = h.wfl_instance_id AND
                            ar.node_id     = wn.node_id AND
                            wn.node_desc   = 'GPS确认节点' AND
                            ar.created_by  = su.user_id AND
                            ee.employee_id = su.employee_id AND
                            rownum         = 1
                        ) user3,
                        (SELECT
                            zv.approver
                        FROM
                            zj_wfl_approve_history_v zv
                        WHERE
                            zv.instance_id       = h.wfl_instance_id AND
                            zv.node_sequence_num = 20 AND
                            zv.action_type       = 1 AND
                            zv.create_date_fmt   =
                            (SELECT
                                MAX(z.create_date_fmt)
                            FROM
                                zj_wfl_approve_history_v z
                            WHERE
                                z.instance_id       = zv.instance_id AND
                                z.node_sequence_num = 20
                            )
                        ) approver20,
                        (SELECT
                            zv.comment_text_out
                        FROM
                            zj_wfl_approve_history_v zv
                        WHERE
                            zv.instance_id       = h.wfl_instance_id AND
                            zv.node_sequence_num = 20 AND
                            zv.action_type       = 1 AND
                            zv.create_date_fmt   =
                            (SELECT
                                MAX(z.create_date_fmt)
                            FROM
                                zj_wfl_approve_history_v z
                            WHERE
                                z.instance_id       = zv.instance_id AND
                                z.node_sequence_num = 20
                            )
                        ) comment_text_out20,
                        (SELECT
                            zv.create_date_fmt
                        FROM
                            zj_wfl_approve_history_v zv
                        WHERE
                            zv.instance_id       = h.wfl_instance_id AND
                            zv.node_sequence_num = 20 AND
                            zv.action_type       = 1 AND
                            zv.create_date_fmt   =
                            (SELECT
                                MAX(z.create_date_fmt)
                            FROM
                                zj_wfl_approve_history_v z
                            WHERE
                                z.instance_id       = zv.instance_id AND
                                z.node_sequence_num = 20
                            )
                        ) create_date_fmt20,
                        (SELECT
                            zv.approver
                        FROM
                            zj_wfl_approve_history_v zv
                        WHERE
                            zv.instance_id       = h.wfl_instance_id AND
                            zv.node_sequence_num = 30 AND
                            zv.action_type       = 1 AND
                            zv.create_date_fmt   =
                            (SELECT
                                MAX(z.create_date_fmt)
                            FROM
                                zj_wfl_approve_history_v z
                            WHERE
                                z.instance_id       = zv.instance_id AND
                                z.node_sequence_num = 30
                            )
                        ) approver30,
                        (SELECT
                            zv.comment_text_out
                        FROM
                            zj_wfl_approve_history_v zv
                        WHERE
                            zv.instance_id       = h.wfl_instance_id AND
                            zv.node_sequence_num = 30 AND
                            zv.action_type       = 1 AND
                            zv.create_date_fmt   =
                            (SELECT
                                MAX(z.create_date_fmt)
                            FROM
                                zj_wfl_approve_history_v z
                            WHERE
                                z.instance_id       = zv.instance_id AND
                                z.node_sequence_num = 30
                            )
                        ) comment_text_out30,
                        (SELECT
                            zv.create_date_fmt
                        FROM
                            zj_wfl_approve_history_v zv
                        WHERE
                            zv.instance_id       = h.wfl_instance_id AND
                            zv.node_sequence_num = 30 AND
                            zv.action_type       = 1 AND
                            zv.create_date_fmt   =
                            (SELECT
                                MAX(z.create_date_fmt)
                            FROM
                                zj_wfl_approve_history_v z
                            WHERE
                                z.instance_id       = zv.instance_id AND
                                z.node_sequence_num = 30
                            )
                        ) create_date_fmt30,
                        (SELECT
                            zv.approver
                        FROM
                            zj_wfl_approve_history_v zv
                        WHERE
                            zv.instance_id       = h.wfl_instance_id AND
                            zv.node_sequence_num = 40 AND
                            zv.action_type       = 1 AND
                            zv.create_date_fmt   =
                            (SELECT
                                MAX(z.create_date_fmt)
                            FROM
                                zj_wfl_approve_history_v z
                            WHERE
                                z.instance_id       = zv.instance_id AND
                                z.node_sequence_num = 40
                            )
                        ) approver40,
                        (SELECT
                            zv.comment_text_out
                        FROM
                            zj_wfl_approve_history_v zv
                        WHERE
                            zv.instance_id       = h.wfl_instance_id AND
                            zv.node_sequence_num = 40 AND
                            zv.action_type       = 1 AND
                            zv.create_date_fmt   =
                            (SELECT
                                MAX(z.create_date_fmt)
                            FROM
                                zj_wfl_approve_history_v z
                            WHERE
                                z.instance_id       = zv.instance_id AND
                                z.node_sequence_num = 40
                            )
                        ) comment_text_out40,
                        (SELECT
                            zv.create_date_fmt
                        FROM
                            zj_wfl_approve_history_v zv
                        WHERE
                            zv.instance_id       = h.wfl_instance_id AND
                            zv.node_sequence_num = 40 AND
                            zv.action_type       = 1 AND
                            zv.create_date_fmt   =
                            (SELECT
                                MAX(z.create_date_fmt)
                            FROM
                                zj_wfl_approve_history_v z
                            WHERE
                                z.instance_id       = zv.instance_id AND
                                z.node_sequence_num = 40
                            )
                        ) create_date_fmt40
                    FROM
                        csh_payment_req_hd h
                    WHERE
                        h.payment_req_id IN (${:@payment_req_id})
                    ) v #WHERE_CLAUSE#
                ORDER BY
                    v.rk
            ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
</bm:model>
