<?xml version="1.0" encoding="UTF-8"?>
<bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm" needAccessControl="false">
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[
     SELECT v.*,
       (v.total_principal + v.total_interest) total_amount,
       (v.this_principal + v.this_penalty+v.this_interest) this_total,
       (v.this_add_principal + v.this_add_penalty+v.this_add_interest) this_add_total,
       (v.future_interest+v.future_principal)  future_due_amount

  FROM (select
              (SELECT h.bp_code FROM con_contract p,hls_bp_master h WHERE p.bp_id_tenant=h.bp_id AND p.contract_id=
                  contract_item_query_pkg.get_contract_history_id(p_contract_id => t.contract_id,
                                                                        p_calc_date   => to_date(${@base_date},
                                                                                                 'yyyy-mm-dd'))) bp_code,
                (SELECT h.bp_name FROM con_contract p,hls_bp_master h WHERE p.bp_id_tenant=h.bp_id AND p.contract_id=
                  contract_item_query_pkg.get_contract_history_id(p_contract_id => t.contract_id,
                                                                        p_calc_date   => to_date(${@base_date},
                                                                                                 'yyyy-mm-dd'))) bp_id_tenant_n,
               --合同编号
               t.contract_number,
               t.contract_id,
               ((SELECT SUM(nvl(ccc.principal, 0))
                   FROM con_contract_cashflow ccc
                  WHERE ccc.contract_id =
                        contract_item_query_pkg.get_contract_history_id(p_contract_id => t.contract_id,
                                                                        p_calc_date   => to_date(${@base_date},
                                                                                                 'yyyy-mm-dd'))
                    AND ccc.cf_item IN (1,200,250)
                    AND ccc.cf_status = 'RELEASE') +
               NVL((SELECT nvl(ccc.due_amount, 0)
                   FROM con_contract_cashflow ccc
                  WHERE ccc.contract_id =
                        contract_item_query_pkg.get_contract_history_id(p_contract_id => t.contract_id,
                                                                        p_calc_date   => to_date(${@base_date},
                                                                                                 'yyyy-mm-dd'))
                    AND ccc.cf_item = 2
                    AND ccc.cf_status = 'RELEASE'),0)) total_principal,
               ((SELECT SUM(nvl(ccc.interest, 0))
                   FROM con_contract_cashflow ccc
                  WHERE ccc.contract_id =
                        contract_item_query_pkg.get_contract_history_id(p_contract_id => t.contract_id,
                                                                        p_calc_date   => to_date(${@base_date},
                                                                                                 'yyyy-mm-dd'))
                    AND ccc.cf_item IN (1,200,250)
                    AND ccc.cf_status = 'RELEASE') +
               NVL((SELECT nvl(ccc.due_amount, 0)
                   FROM con_contract_cashflow ccc
                  WHERE ccc.contract_id =
                        contract_item_query_pkg.get_contract_history_id(p_contract_id => t.contract_id,
                                                                        p_calc_date   => to_date(${@base_date},
                                                                                                 'yyyy-mm-dd'))
                    AND ccc.cf_item = 8
                    AND ccc.cf_status = 'RELEASE'),0)) total_interest,
               '0.00' total_other,
               --合同开始日
               t.lease_start_date,
               --合同终了日取留购金
               (SELECT ccc.due_date
                  FROM con_contract_cashflow ccc
                 WHERE ccc.cf_item = 8
                   AND ccc.contract_id =
                       contract_item_query_pkg.get_contract_history_id(p_contract_id => t.contract_id,
                                                                       p_calc_date   => to_date(${@base_date},
                                                                                                'yyyy-mm-dd'))) lease_end_date,
               --当期利息
               nvl((SELECT SUM(nvl(ccc.interest, 0))
                     FROM con_contract_cashflow ccc
                    WHERE ccc.contract_id =
                          contract_item_query_pkg.get_contract_history_id(p_contract_id => t.contract_id,
                                                                          p_calc_date   => to_date(${@base_date},
                                                                                                   'yyyy-mm-dd'))
                      AND cf_item IN(1,200,250)
                      AND ccc.cf_status = 'RELEASE'
                      AND to_char(ccc.due_date, 'yyyymm') =
                          to_char(to_date(${@base_date}, 'yyyy-mm-dd'),
                                  'yyyymm')),
                   0)+
                   NVL((SELECT nvl(ccc.due_amount, 0)
                   FROM con_contract_cashflow ccc
                  WHERE ccc.contract_id =
                        contract_item_query_pkg.get_contract_history_id(p_contract_id => t.contract_id,
                                                                        p_calc_date   => to_date(${@base_date},
                                                                                                 'yyyy-mm-dd'))
                    AND ccc.cf_item = 8
                    AND ccc.cf_status = 'RELEASE'
                    AND to_char(ccc.due_date, 'yyyymm') =
                          to_char(to_date(${@base_date}, 'yyyy-mm-dd'),
                                  'yyyymm')),0) this_interest,
               --当次累计利息
               nvl((SELECT SUM(nvl(ccc.interest, 0))
                     FROM con_contract_cashflow ccc
                    WHERE ccc.contract_id =
                          contract_item_query_pkg.get_contract_history_id(p_contract_id => t.contract_id,
                                                                          p_calc_date   => to_date(${@base_date},
                                                                                                   'yyyy-mm-dd'))
                      AND cf_item IN( 1,200,250)
                      AND ccc.cf_status = 'RELEASE'
                      AND to_char(ccc.due_date, 'yyyymm') <=
                          to_char(to_date(${@base_date}, 'yyyy-mm-dd'),
                                  'yyyymm')),
                   0)+NVL((SELECT nvl(ccc.due_amount, 0)
                   FROM con_contract_cashflow ccc
                  WHERE ccc.contract_id =
                        contract_item_query_pkg.get_contract_history_id(p_contract_id => t.contract_id,
                                                                        p_calc_date   => to_date(${@base_date},
                                                                                                 'yyyy-mm-dd'))
                    AND ccc.cf_item = 8
                    AND ccc.cf_status = 'RELEASE'
                    AND to_char(ccc.due_date, 'yyyymm') <=
                          to_char(to_date(${@base_date}, 'yyyy-mm-dd'),
                                  'yyyymm')),0) this_add_interest,

               --设备本金
               nvl((SELECT SUM(nvl(ccc.principal, 0))
                     FROM con_contract_cashflow ccc
                    WHERE ccc.contract_id =
                          contract_item_query_pkg.get_contract_history_id(p_contract_id => t.contract_id,
                                                                          p_calc_date   => to_date(${@base_date},
                                                                                                   'yyyy-mm-dd'))
                      AND cf_item IN (1,200,250)
                      AND ccc.cf_status = 'RELEASE'
                      AND to_char(ccc.due_date, 'yyyymm') =
                          to_char(to_date(${@base_date}, 'yyyy-mm-dd'),
                                  'yyyymm')),0)+NVL((
                                  SELECT SUM(nvl(ccc.due_amount, 0))
                     FROM con_contract_cashflow ccc
                    WHERE ccc.contract_id =
                          contract_item_query_pkg.get_contract_history_id(p_contract_id => t.contract_id,
                                                                          p_calc_date   => to_date(${@base_date},
                                                                                                   'yyyy-mm-dd'))
                      AND cf_item =2
                      AND ccc.cf_status = 'RELEASE'
                      AND to_char(ccc.due_date, 'yyyymm') =
                          to_char(to_date(${@base_date}, 'yyyy-mm-dd'),'yyyymm')

                                  ),0) this_principal,
               --累计本金
              nvl((SELECT SUM(nvl(ccc.principal, 0))
                     FROM con_contract_cashflow ccc
                    WHERE ccc.contract_id =
                          contract_item_query_pkg.get_contract_history_id(p_contract_id => t.contract_id,
                                                                          p_calc_date   => to_date(${@base_date},
                                                                                                   'yyyy-mm-dd'))
                      AND cf_item IN (1,200,250)
                      AND ccc.cf_status = 'RELEASE'
                      AND to_char(ccc.due_date, 'yyyymm') <=
                          to_char(to_date(${@base_date}, 'yyyy-mm-dd'),
                                  'yyyymm')),0)+NVL((
                                  SELECT SUM(nvl(ccc.due_amount, 0))
                     FROM con_contract_cashflow ccc
                    WHERE ccc.contract_id =
                          contract_item_query_pkg.get_contract_history_id(p_contract_id => t.contract_id,
                                                                          p_calc_date   => to_date(${@base_date},
                                                                                                   'yyyy-mm-dd'))
                      AND cf_item =2
                      AND ccc.cf_status = 'RELEASE'
                      AND to_char(ccc.due_date, 'yyyymm') <=
                          to_char(to_date(${@base_date}, 'yyyy-mm-dd'),'yyyymm')

                                  ),0) this_add_principal,
                   ---当期违约金
               NVL(( select  sum(gw.write_off_amount)
                         from csh_transaction ct,
                              csh_write_off   cw,
                              gld_write_off   gw
                        where ct.transaction_id = cw.csh_transaction_id
                          and cw.write_off_id = gw.csh_write_off_id
                          and gw.cf_item = 9
                          and gw.contract_id = contract_item_query_pkg.get_contract_history_id(p_contract_id => t.contract_id,
                                                                          p_calc_date   => to_date(${@base_date},
                                                                                                   'yyyy-mm-dd'))
                          and nvl(cw.reversed_flag, 'N') = 'N'
                          and nvl(gw.reversed_flag, 'N') = 'N'
                          and ((ct.transaction_type = 'RECEIPT' and
                              to_char(ct.transaction_date,'yyyymm') =
                              to_char(to_date(${@base_date}, 'yyyy-mm-dd'),'yyyymm')) or
                              (ct.transaction_type = 'ADVANCED' and
                              to_char(cw.write_off_date,'yyyymm') =
                              to_char(to_date(${@base_date}, 'yyyy-mm-dd'),'yyyymm')))
                   ),0) this_penalty,
               --累计违约金
                  NVL(( select  sum(gw.write_off_amount)
                         from csh_transaction ct,
                              csh_write_off   cw,
                              gld_write_off   gw
                        where ct.transaction_id = cw.csh_transaction_id
                          and cw.write_off_id = gw.csh_write_off_id
                          and gw.cf_item = 9
                          and gw.contract_id = contract_item_query_pkg.get_contract_history_id(p_contract_id => t.contract_id,
                                                                          p_calc_date   => to_date(${@base_date},
                                                                                                   'yyyy-mm-dd'))
                          and nvl(cw.reversed_flag, 'N') = 'N'
                          and nvl(gw.reversed_flag, 'N') = 'N'
                          and ((ct.transaction_type = 'RECEIPT' and
                              to_char(ct.transaction_date,'yyyymm') <=
                              to_char(to_date(${@base_date}, 'yyyy-mm-dd'),'yyyymm')) or
                              (ct.transaction_type = 'ADVANCED' and
                              to_char(cw.write_off_date,'yyyymm') <=
                               to_char(to_date(${@base_date}, 'yyyy-mm-dd'),'yyyymm')))
                   ),0)this_add_penalty,
               --当期累计
               --累计

               --未执行利息
               '0' unexecute_interest,
               --未来应收利息
                nvl((SELECT SUM(nvl(ccc.interest, 0))
                     FROM con_contract_cashflow ccc
                    WHERE ccc.contract_id =
                          contract_item_query_pkg.get_contract_history_id(p_contract_id => t.contract_id,
                                                                          p_calc_date   => to_date(${@base_date},
                                                                                                   'yyyy-mm-dd'))
                      AND cf_item IN ( 1,200,250)
                      AND ccc.cf_status = 'RELEASE'
                      AND to_char(ccc.due_date, 'yyyymm') >
                          to_char(to_date(${@base_date}, 'yyyy-mm-dd'),
                                  'yyyymm')),
                   0)+NVL((SELECT nvl(ccc.due_amount, 0)
                   FROM con_contract_cashflow ccc
                  WHERE ccc.contract_id =
                        contract_item_query_pkg.get_contract_history_id(p_contract_id => t.contract_id,
                                                                        p_calc_date   => to_date(${@base_date},
                                                                                                 'yyyy-mm-dd'))
                    AND ccc.cf_item = 8
                    AND ccc.cf_status = 'RELEASE'
                    AND to_char(ccc.due_date, 'yyyymm') >
                          to_char(to_date(${@base_date}, 'yyyy-mm-dd'),
                                  'yyyymm')),0) future_interest,
               --未执行本金
               '0' unexecute_principal,
               --未来本金
               NVL((SELECT SUM(nvl(ccc.principal, 0))
                  FROM con_contract_cashflow ccc
                 WHERE ccc.contract_id =
                       contract_item_query_pkg.get_contract_history_id(p_contract_id => t.contract_id,
                                                                       p_calc_date   => to_date(${@base_date},
                                                                                                'yyyy-mm-dd'))
                   AND cf_item IN (1,200,250)
                   AND ccc.cf_status = 'RELEASE'
                   AND to_char(ccc.due_date, 'yyyymm') >
                       to_char(to_date(${@base_date}, 'yyyy-mm-dd'), 'yyyymm')),0) future_principal,
               '0' unexecute_total,
               --未来应收
               '0' this_due_amount,
               -- add by liyuan.chen
               (select hb.bp_code from hls_bp_master hb where hb.bp_id = t.bp_id_agent_level1) as bp_agent_code,
               (select hb.bp_name from hls_bp_master hb where hb.bp_id = t.bp_id_agent_level1) as bp_agent_name,
               --当期利息
               nvl((SELECT SUM(nvl(ccc.net_interest, 0))
                     FROM con_contract_cashflow ccc
                    WHERE ccc.contract_id =
                          contract_item_query_pkg.get_contract_history_id(p_contract_id => t.contract_id,
                                                                          p_calc_date   => to_date(${@base_date},
                                                                                                   'yyyy-mm-dd'))
                      AND cf_item IN(1,200,250)
                      AND ccc.cf_status = 'RELEASE'
                      AND to_char(ccc.due_date, 'yyyymm') =
                          to_char(to_date(${@base_date}, 'yyyy-mm-dd'),
                                  'yyyymm')),
                   0)+
                   NVL((SELECT nvl(ccc.net_due_amount, 0)
                   FROM con_contract_cashflow ccc
                  WHERE ccc.contract_id =
                        contract_item_query_pkg.get_contract_history_id(p_contract_id => t.contract_id,
                                                                        p_calc_date   => to_date(${@base_date},
                                                                                                 'yyyy-mm-dd'))
                    AND ccc.cf_item = 8
                    AND ccc.cf_status = 'RELEASE'
                    AND to_char(ccc.due_date, 'yyyymm') =
                          to_char(to_date(${@base_date}, 'yyyy-mm-dd'),
                                  'yyyymm')),0) net_this_interest,
               --当次累计利息
               nvl((SELECT SUM(nvl(ccc.net_interest, 0))
                     FROM con_contract_cashflow ccc
                    WHERE ccc.contract_id =
                          contract_item_query_pkg.get_contract_history_id(p_contract_id => t.contract_id,
                                                                          p_calc_date   => to_date(${@base_date},
                                                                                                   'yyyy-mm-dd'))
                      AND cf_item IN( 1,200,250)
                      AND ccc.cf_status = 'RELEASE'
                      AND to_char(ccc.due_date, 'yyyymm') <=
                          to_char(to_date(${@base_date}, 'yyyy-mm-dd'),
                                  'yyyymm')),
                   0)+NVL((SELECT nvl(ccc.net_due_amount, 0)
                   FROM con_contract_cashflow ccc
                  WHERE ccc.contract_id =
                        contract_item_query_pkg.get_contract_history_id(p_contract_id => t.contract_id,
                                                                        p_calc_date   => to_date(${@base_date},
                                                                                                 'yyyy-mm-dd'))
                    AND ccc.cf_item = 8
                    AND ccc.cf_status = 'RELEASE'
                    AND to_char(ccc.due_date, 'yyyymm') <=
                          to_char(to_date(${@base_date}, 'yyyy-mm-dd'),
                                  'yyyymm')),0) net_this_add_interest,
                                   --未执行利息
               '0' net_unexecute_interest,
               --未来应收利息
                nvl((SELECT SUM(nvl(ccc.net_interest, 0))
                     FROM con_contract_cashflow ccc
                    WHERE ccc.contract_id =
                          contract_item_query_pkg.get_contract_history_id(p_contract_id => t.contract_id,
                                                                          p_calc_date   => to_date(${@base_date},
                                                                                                   'yyyy-mm-dd'))
                      AND cf_item IN ( 1,200,250)
                      AND ccc.cf_status = 'RELEASE'
                      AND to_char(ccc.due_date, 'yyyymm') >
                          to_char(to_date(${@base_date}, 'yyyy-mm-dd'),
                                  'yyyymm')),
                   0)+NVL((SELECT nvl(ccc.net_due_amount, 0)
                   FROM con_contract_cashflow ccc
                  WHERE ccc.contract_id =
                        contract_item_query_pkg.get_contract_history_id(p_contract_id => t.contract_id,
                                                                        p_calc_date   => to_date(${@base_date},
                                                                                                 'yyyy-mm-dd'))
                    AND ccc.cf_item = 8
                    AND ccc.cf_status = 'RELEASE'
                    AND to_char(ccc.due_date, 'yyyymm') >
                          to_char(to_date(${@base_date}, 'yyyy-mm-dd'),
                                  'yyyymm')),0) net_future_interest,
                                  (Select bt.description
          From hls_business_type bt
         Where bt.business_type = t.business_type)  business_type_n
               
          FROM con_contract t
         WHERE t.data_class = 'NORMAL'
           AND t.contract_status NOT IN('CANCEL', 'NEW', 'INCEPTING', 'INCEPT_RETURN')
           AND trunc(t.lease_start_date) >=
               trunc(to_date(${@due_date_from}, 'yyyy-mm-dd'))
           AND trunc(t.lease_start_date) <=
               trunc(to_date(${@due_date_to}, 'yyyy-mm-dd'))
           And ((${@business_type} is null) or
                         (${@business_type} is not null and
                         t.business_type = ${@business_type}))
          ) v


                ]]>
            </bm:query-sql>
        </bm:operation>
    </bm:operations>
</bm:model>
