<?xml version="1.0" encoding="UTF-8"?>
<bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm">
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[
                SELECT
                    *
                FROM
                    (
                    SELECT cc.contract_id document_id,
       ccc.due_date,
       TO_CHAR(ccc.due_date, 'YYYY-MM-DD')  due_date_day,
       cc.contract_number document_number,
       cc.contract_name document_name,
       hbm.bp_name,
       cc.lease_item_amount contract_amount,
       (SELECT hci.description
          FROM hls_cashflow_item hci
         WHERE hci.cf_item = 100) cf_item_n,
       NVL(ccc.principal, 0) inflow_amount,
       NULL outflow_amount,
       NVL((SELECT '抵质押中'
             FROM dual
            WHERE EXISTS (SELECT 1
                     FROM bgfl_tre_factoring_contract btfc
                    WHERE btfc.contract_id = cc.contract_id
                      AND btfc.times_from <= ccc.times
                      AND btfc.times_to >= ccc.times)),
           '未抵质押') factoring_flag_n,
       NVL((SELECT 'Y'
             FROM dual
            WHERE EXISTS
            (SELECT 1
                     FROM hn_abs_contract hac, con_contract_cashflow ccca
                    WHERE hac.contract_id = cc.contract_id
                      AND ccca.contract_id = cc.contract_id
                      AND ccca.times >= hac.start_times
                      AND ccca.times <= hac.end_times
                      AND TO_CHAR(ccca.due_date, 'YYYYMM') = ${@calc_year}
                        || ${@calc_month})),
           'N') status, --是否做过融资保理质押
       (SELECT cba.bank_account_num
          FROM hn_con_bank_account hcba, csh_bank_account cba
         WHERE hcba.contract_id = cc.contract_id
           AND hcba.valid_from <= ccc.due_date
           AND hcba.valid_to > ccc.due_date
           AND hcba.bank_account_id = cba.bank_account_id) bank_account_num,
       (SELECT cbb.bank_branch_name
          FROM hn_con_bank_account hcba,
               csh_bank_account    cba,
               csh_bank_branch     cbb
         WHERE hcba.contract_id = cc.contract_id
           AND hcba.valid_from <= ccc.due_date
           AND hcba.valid_to > ccc.due_date
           AND hcba.bank_account_id = cba.bank_account_id
           AND cba.bank_branch_id = cbb.bank_branch_id) bank_branch_name
  FROM con_contract cc, con_contract_cashflow ccc, hls_bp_master hbm
 WHERE cc.contract_status <> 'CANCEL'
   AND cc.contract_id = ccc.contract_id
   AND cc.data_class = 'NORMAL'
   AND ccc.cf_item = 1
   AND EXISTS (SELECT 1
          FROM csh_write_off cwo
         WHERE cwo.contract_id = cc.contract_id
           AND cwo.reversed_flag = 'N'
           AND cwo.write_off_type = 'PAYMENT_DEBT'
           AND cwo.cf_item = 0)
   AND cc.bp_id_tenant = hbm.bp_id
   AND TO_CHAR(ccc.due_date, 'YYYYMM') = ${@calc_year}
                        || ${@calc_month}
   union
   SELECT cc.contract_id document_id,
       ccc.due_date,
       TO_CHAR(ccc.due_date, 'YYYY-MM-DD')  due_date_day,
       cc.contract_number document_number,
       cc.contract_name document_name,
       hbm.bp_name,
       cc.lease_item_amount contract_amount,
       (SELECT hci.description
          FROM hls_cashflow_item hci
         WHERE hci.cf_item = 101) cf_item_n,
       NVL(ccc.interest, 0) inflow_amount,
       NULL outflow_amount,
       NVL((SELECT '抵质押中'
             FROM dual
            WHERE EXISTS (SELECT 1
                     FROM bgfl_tre_factoring_contract btfc
                    WHERE btfc.contract_id = cc.contract_id
                      AND btfc.times_from <= ccc.times
                      AND btfc.times_to >= ccc.times)),
           '未抵质押') factoring_flag_n,
       NVL((SELECT 'Y'
             FROM dual
            WHERE EXISTS
            (SELECT 1
                     FROM hn_abs_contract hac, con_contract_cashflow ccca
                    WHERE hac.contract_id = cc.contract_id
                      AND ccca.contract_id = cc.contract_id
                      AND ccca.times >= hac.start_times
                      AND ccca.times <= hac.end_times
                      AND TO_CHAR(ccca.due_date, 'YYYYMM') = ${@calc_year}
                        || ${@calc_month})),
           'N') status, --是否做过融资保理质押
       (SELECT cba.bank_account_num
          FROM hn_con_bank_account hcba, csh_bank_account cba
         WHERE hcba.contract_id = cc.contract_id
           AND hcba.valid_from <= ccc.due_date
           AND hcba.valid_to > ccc.due_date
           AND hcba.bank_account_id = cba.bank_account_id) bank_account_num,
       (SELECT cbb.bank_branch_name
          FROM hn_con_bank_account hcba,
               csh_bank_account    cba,
               csh_bank_branch     cbb
         WHERE hcba.contract_id = cc.contract_id
           AND hcba.valid_from <= ccc.due_date
           AND hcba.valid_to > ccc.due_date
           AND hcba.bank_account_id = cba.bank_account_id
           AND cba.bank_branch_id = cbb.bank_branch_id) bank_branch_name
  FROM con_contract cc, con_contract_cashflow ccc, hls_bp_master hbm
 WHERE cc.contract_status <> 'CANCEL'
   AND cc.contract_id = ccc.contract_id
   AND cc.data_class = 'NORMAL'
   AND ccc.cf_item = 1
   AND EXISTS (SELECT 1
          FROM csh_write_off cwo
         WHERE cwo.contract_id = cc.contract_id
           AND cwo.reversed_flag = 'N'
           AND cwo.write_off_type = 'PAYMENT_DEBT'
           AND cwo.cf_item = 0)
   AND cc.bp_id_tenant = hbm.bp_id
   AND TO_CHAR(ccc.due_date, 'YYYYMM') = ${@calc_year}
                        || ${@calc_month}
   union
   SELECT cc.contract_id document_id,
       ccc.due_date,
       TO_CHAR(ccc.due_date, 'YYYY-MM-DD')  due_date_day,
       cc.contract_number document_number,
       cc.contract_name document_name,
       hbm.bp_name,
       cc.lease_item_amount contract_amount,
       (SELECT hcit.description
          FROM con_contract_cashflow ccct, hls_cashflow_item hcit
         WHERE ccct.contract_id = ccc.contract_id
           AND ccct.times = ccc.times
           AND ccct.cf_item = 3
           AND ccct.cf_item = hcit.cf_item) cf_item_n,
       NVL((SELECT cccl.due_amount
             FROM con_contract_cashflow cccl
            WHERE cccl.contract_id = cc.contract_id
              AND cccl.times = ccc.times
              AND cccl.cf_item = 3),
           0)  inflow_amount,
       NULL outflow_amount,
       NVL((SELECT '抵质押中'
             FROM dual
            WHERE EXISTS (SELECT 1
                     FROM bgfl_tre_factoring_contract btfc
                    WHERE btfc.contract_id = cc.contract_id
                      AND btfc.times_from <= ccc.times
                      AND btfc.times_to >= ccc.times)),
           '未抵质押') factoring_flag_n,
       NVL((SELECT 'Y'
             FROM dual
            WHERE EXISTS
            (SELECT 1
                     FROM hn_abs_contract hac, con_contract_cashflow ccca
                    WHERE hac.contract_id = cc.contract_id
                      AND ccca.contract_id = cc.contract_id
                      AND ccca.times >= hac.start_times
                      AND ccca.times <= hac.end_times
                      AND TO_CHAR(ccca.due_date, 'YYYYMM') = ${@calc_year}
                        || ${@calc_month})),
           'N') status, --是否做过融资保理质押
       (SELECT cba.bank_account_num
          FROM hn_con_bank_account hcba, csh_bank_account cba
         WHERE hcba.contract_id = cc.contract_id
           AND hcba.valid_from <= ccc.due_date
           AND hcba.valid_to > ccc.due_date
           AND hcba.bank_account_id = cba.bank_account_id) bank_account_num,
       (SELECT cbb.bank_branch_name
          FROM hn_con_bank_account hcba,
               csh_bank_account    cba,
               csh_bank_branch     cbb
         WHERE hcba.contract_id = cc.contract_id
           AND hcba.valid_from <= ccc.due_date
           AND hcba.valid_to > ccc.due_date
           AND hcba.bank_account_id = cba.bank_account_id
           AND cba.bank_branch_id = cbb.bank_branch_id) bank_branch_name
  FROM con_contract cc, con_contract_cashflow ccc, hls_bp_master hbm
 WHERE cc.contract_status <> 'CANCEL'
   AND cc.contract_id = ccc.contract_id
   AND cc.data_class = 'NORMAL'
   AND ccc.cf_item = 1
   AND EXISTS (SELECT 1
          FROM csh_write_off cwo
         WHERE cwo.contract_id = cc.contract_id
           AND cwo.reversed_flag = 'N'
           AND cwo.write_off_type = 'PAYMENT_DEBT'
           AND cwo.cf_item = 0)
   AND cc.bp_id_tenant = hbm.bp_id
   AND TO_CHAR(ccc.due_date, 'YYYYMM') = ${@calc_year}
                        || ${@calc_month}
   union
   SELECT cc.contract_id document_id,
       ccc.due_date,
       TO_CHAR(ccc.due_date, 'YYYY-MM-DD')  due_date_day,
       cc.contract_number document_number,
       cc.contract_name document_name,
       hbm.bp_name,
       cc.lease_item_amount contract_amount,
       (SELECT hcit.description
          FROM con_contract_cashflow ccct, hls_cashflow_item hcit
         WHERE ccct.contract_id = ccc.contract_id
           AND ccct.times = ccc.times
           AND ccct.cf_item = 15
           AND ccct.cf_item = hcit.cf_item) cf_item_n,
       NVL((SELECT cccl.due_amount
                      FROM con_contract_cashflow cccl
                     WHERE cccl.contract_id = cc.contract_id
                       AND cccl.times = ccc.times
                       AND cccl.cf_item = 15),
                    0) inflow_amount,
       NULL outflow_amount,
       NVL((SELECT '抵质押中'
             FROM dual
            WHERE EXISTS (SELECT 1
                     FROM bgfl_tre_factoring_contract btfc
                    WHERE btfc.contract_id = cc.contract_id
                      AND btfc.times_from <= ccc.times
                      AND btfc.times_to >= ccc.times)),
           '未抵质押') factoring_flag_n,
       NVL((SELECT 'Y'
             FROM dual
            WHERE EXISTS
            (SELECT 1
                     FROM hn_abs_contract hac, con_contract_cashflow ccca
                    WHERE hac.contract_id = cc.contract_id
                      AND ccca.contract_id = cc.contract_id
                      AND ccca.times >= hac.start_times
                      AND ccca.times <= hac.end_times
                      AND TO_CHAR(ccca.due_date, 'YYYYMM') =${@calc_year}
                        || ${@calc_month})),
           'N') status, --是否做过融资保理质押
       (SELECT cba.bank_account_num
          FROM hn_con_bank_account hcba, csh_bank_account cba
         WHERE hcba.contract_id = cc.contract_id
           AND hcba.valid_from <= ccc.due_date
           AND hcba.valid_to > ccc.due_date
           AND hcba.bank_account_id = cba.bank_account_id) bank_account_num,
       (SELECT cbb.bank_branch_name
          FROM hn_con_bank_account hcba,
               csh_bank_account    cba,
               csh_bank_branch     cbb
         WHERE hcba.contract_id = cc.contract_id
           AND hcba.valid_from <= ccc.due_date
           AND hcba.valid_to > ccc.due_date
           AND hcba.bank_account_id = cba.bank_account_id
           AND cba.bank_branch_id = cbb.bank_branch_id) bank_branch_name
  FROM con_contract cc, con_contract_cashflow ccc, hls_bp_master hbm
 WHERE cc.contract_status <> 'CANCEL'
   AND cc.contract_id = ccc.contract_id
   AND cc.data_class = 'NORMAL'
   AND ccc.cf_item = 1
   AND EXISTS (SELECT 1
          FROM csh_write_off cwo
         WHERE cwo.contract_id = cc.contract_id
           AND cwo.reversed_flag = 'N'
           AND cwo.write_off_type = 'PAYMENT_DEBT'
           AND cwo.cf_item = 0)
   AND cc.bp_id_tenant = hbm.bp_id
   AND TO_CHAR(ccc.due_date, 'YYYYMM') = ${@calc_year}
                        || ${@calc_month}
   union
   --2
     SELECT tlc.loan_contract_id document_id,
            rp.repayment_date due_date,
            TO_CHAR(rp.repayment_date, 'YYYY-MM-DD')  due_date_day,
            tlc.loan_contract_number document_number,
            tlc.loan_contract_name document_name,
            hbm.bp_name,
            (SELECT wp.amount
               FROM tre_loan_con_withdrawal_plan wp
              WHERE wp.quotation_id = rp.quotation_id
                AND wp.loan_contract_id = tlc.loan_contract_id) contract_amount,
            (SELECT v.code_value_name
               FROM sys_code_values_v v
              WHERE v.code = 'TRE_LOAN_REPAYMENT_TYPE'
                AND v.code_value = rp.repayment_type) cf_item_n,
            NULL inflow_amount,
            NVL(rp.amount, 0) outflow_amount,
            NULL factoring_flag_n,
            'N' status, --是否做过融资保理质押
            (SELECT cba.bank_account_num
               FROM tre_loan_con_write_off tlcwo, csh_bank_account cba
              WHERE tlcwo.loan_contract_id = tlc.loan_contract_id
                AND tlcwo.quotation_id = rp.quotation_id
                AND tlcwo.document_id = rp.repayment_plan_id
                AND tlcwo.bank_account_id = cba.bank_account_id) bank_account_num,
            (SELECT cbb.bank_branch_name
               FROM tre_loan_con_write_off tlcwo,
                    csh_bank_account       cba,
                    csh_bank_branch        cbb
              WHERE tlcwo.loan_contract_id = tlc.loan_contract_id
                AND tlcwo.quotation_id = rp.quotation_id
                AND tlcwo.document_id = rp.repayment_plan_id
                AND tlcwo.bank_account_id = cba.bank_account_id
                AND cba.bank_branch_id = cbb.bank_branch_id) bank_branch_name
       FROM tre_loan_contract           tlc,
            tre_loan_con_repayment_plan rp,
            hls_bp_master               hbm
      WHERE tlc.data_class = 'NORMAL'
        AND tlc.loan_contract_id = rp.loan_contract_id
        AND rp.repayment_type = 'INTEREST'
        AND rp.quotation_id NOT IN
            (SELECT pq.quotation_id
               FROM prj_quotation pq
              WHERE pq.document_category = 'LOAN_CONTRACT'
                AND pq.document_id = tlc.loan_contract_id
                AND NVL(pq.main_contract_flag, 'N') = 'Y')
        AND tlc.loan_con_bp = hbm.bp_id
        AND TO_CHAR(rp.repayment_date, 'YYYYMM') = ${@calc_year}
                        || ${@calc_month}
      union
       SELECT tlc.loan_contract_id document_id,
              rp.repayment_date due_date,
              TO_CHAR(rp.repayment_date, 'YYYY-MM-DD')  due_date_day,
              tlc.loan_contract_number document_number,
              tlc.loan_contract_name document_name,
              hbm.bp_name,
              (SELECT wp.amount
                 FROM tre_loan_con_withdrawal_plan wp
                WHERE wp.quotation_id = rp.quotation_id
                  AND wp.loan_contract_id = tlc.loan_contract_id) contract_amount,
              (SELECT rptv.code_value_name
                 FROM tre_loan_con_repayment_plan rpt,
                      sys_code_values_v           rptv
                WHERE rpt.loan_contract_id = tlc.loan_contract_id
                  AND rpt.quotation_id = rp.quotation_id
                  AND rpt.times = rp.times
                  AND rpt.repayment_type = 'PRINCIPAL'
                  AND rpt.repayment_date = rp.repayment_date
                  AND rptv.code = 'TRE_LOAN_REPAYMENT_TYPE'
                  AND rptv.code_value = rpt.repayment_type) cf_item_n,
              NULL inflow_amount,
              NVL((SELECT rpt.amount
                    FROM tre_loan_con_repayment_plan rpt
                   WHERE rpt.loan_contract_id = tlc.loan_contract_id
                     AND rpt.quotation_id = rp.quotation_id
                     AND rpt.times = rp.times
                     AND rpt.repayment_type = 'PRINCIPAL'
                     AND rpt.repayment_date = rp.repayment_date),
                  0)  outflow_amount,
              NULL factoring_flag_n,
              'N' status, --是否做过融资保理质押
              (SELECT cba.bank_account_num
                 FROM tre_loan_con_write_off tlcwo, csh_bank_account cba
                WHERE tlcwo.loan_contract_id = tlc.loan_contract_id
                  AND tlcwo.quotation_id = rp.quotation_id
                  AND tlcwo.document_id = rp.repayment_plan_id
                  AND tlcwo.bank_account_id = cba.bank_account_id) bank_account_num,
              (SELECT cbb.bank_branch_name
                 FROM tre_loan_con_write_off tlcwo,
                      csh_bank_account       cba,
                      csh_bank_branch        cbb
                WHERE tlcwo.loan_contract_id = tlc.loan_contract_id
                  AND tlcwo.quotation_id = rp.quotation_id
                  AND tlcwo.document_id = rp.repayment_plan_id
                  AND tlcwo.bank_account_id = cba.bank_account_id
                  AND cba.bank_branch_id = cbb.bank_branch_id) bank_branch_name
         FROM tre_loan_contract           tlc,
              tre_loan_con_repayment_plan rp,
              hls_bp_master               hbm
        WHERE tlc.data_class = 'NORMAL'
          AND tlc.loan_contract_id = rp.loan_contract_id
          AND rp.repayment_type = 'INTEREST'
          AND rp.quotation_id NOT IN
              (SELECT pq.quotation_id
                 FROM prj_quotation pq
                WHERE pq.document_category = 'LOAN_CONTRACT'
                  AND pq.document_id = tlc.loan_contract_id
                  AND NVL(pq.main_contract_flag, 'N') = 'Y')
          AND tlc.loan_con_bp = hbm.bp_id
          AND TO_CHAR(rp.repayment_date, 'YYYYMM') = ${@calc_year}
                        || ${@calc_month}
         union
          SELECT tlc.loan_contract_id document_id,
                 rp.repayment_date due_date,
                 TO_CHAR(rp.repayment_date, 'YYYY-MM-DD')  due_date_day,
                 tlc.loan_contract_number document_number,
                 tlc.loan_contract_name document_name,
                 hbm.bp_name,
                 (SELECT wp.amount
                    FROM tre_loan_con_withdrawal_plan wp
                   WHERE wp.quotation_id = rp.quotation_id
                     AND wp.loan_contract_id = tlc.loan_contract_id) contract_amount,
                 (SELECT rptv.code_value_name
                    FROM tre_loan_con_repayment_plan rpt,
                         sys_code_values_v           rptv
                   WHERE rpt.loan_contract_id = tlc.loan_contract_id
                     AND rpt.quotation_id = rp.quotation_id
                     AND rpt.times = rp.times
                     AND rpt.repayment_type = 'CHARGE'
                     AND rpt.repayment_date = rp.repayment_date
                     AND rptv.code = 'TRE_LOAN_REPAYMENT_TYPE'
                     AND rptv.code_value = rpt.repayment_type)cf_item_n,
                 NULL inflow_amount,
                 NVL((SELECT rpt.amount
                       FROM tre_loan_con_repayment_plan rpt
                      WHERE rpt.loan_contract_id = tlc.loan_contract_id
                        AND rpt.quotation_id = rp.quotation_id
                        AND rpt.times = rp.times
                        AND rpt.repayment_type = 'CHARGE'
                        AND rpt.repayment_date = rp.repayment_date),
                     0) outflow_amount,
                 NULL factoring_flag_n,
                 'N' status, --是否做过融资保理质押
                 (SELECT cba.bank_account_num
                    FROM tre_loan_con_write_off tlcwo, csh_bank_account cba
                   WHERE tlcwo.loan_contract_id = tlc.loan_contract_id
                     AND tlcwo.quotation_id = rp.quotation_id
                     AND tlcwo.document_id = rp.repayment_plan_id
                     AND tlcwo.bank_account_id = cba.bank_account_id) bank_account_num,
                 (SELECT cbb.bank_branch_name
                    FROM tre_loan_con_write_off tlcwo,
                         csh_bank_account       cba,
                         csh_bank_branch        cbb
                   WHERE tlcwo.loan_contract_id = tlc.loan_contract_id
                     AND tlcwo.quotation_id = rp.quotation_id
                     AND tlcwo.document_id = rp.repayment_plan_id
                     AND tlcwo.bank_account_id = cba.bank_account_id
                     AND cba.bank_branch_id = cbb.bank_branch_id) bank_branch_name
            FROM tre_loan_contract           tlc,
                 tre_loan_con_repayment_plan rp,
                 hls_bp_master               hbm
           WHERE tlc.data_class = 'NORMAL'
             AND tlc.loan_contract_id = rp.loan_contract_id
             AND rp.repayment_type = 'INTEREST'
             AND rp.quotation_id NOT IN
                 (SELECT pq.quotation_id
                    FROM prj_quotation pq
                   WHERE pq.document_category = 'LOAN_CONTRACT'
                     AND pq.document_id = tlc.loan_contract_id
                     AND NVL(pq.main_contract_flag, 'N') = 'Y')
             AND tlc.loan_con_bp = hbm.bp_id
             AND TO_CHAR(rp.repayment_date, 'YYYYMM') = ${@calc_year}
                        || ${@calc_month}
            union
             SELECT tlc.loan_contract_id document_id,
                    rp.repayment_date due_date,
                    TO_CHAR(rp.repayment_date,'YYYY-MM-DD')  due_date_day,
                    tlc.loan_contract_number document_number,
                    tlc.loan_contract_name document_name,
                    hbm.bp_name,
                    (SELECT wp.amount
                       FROM tre_loan_con_withdrawal_plan wp
                      WHERE wp.quotation_id = rp.quotation_id
                        AND wp.loan_contract_id = tlc.loan_contract_id) contract_amount,
                    (SELECT rptv.code_value_name
                       FROM tre_loan_con_repayment_plan rpt,
                            sys_code_values_v           rptv
                      WHERE rpt.loan_contract_id = tlc.loan_contract_id
                        AND rpt.quotation_id = rp.quotation_id
                        AND rpt.times = rp.times
                        AND rpt.repayment_type = 'CONSULTATION'
                        AND rpt.repayment_date = rp.repayment_date
                        AND rptv.code = 'TRE_LOAN_REPAYMENT_TYPE'
                        AND rptv.code_value = rpt.repayment_type) cf_item_n,
                    NULL inflow_amount,
                    NVL((SELECT rpt.amount
                          FROM tre_loan_con_repayment_plan rpt
                         WHERE rpt.loan_contract_id = tlc.loan_contract_id
                           AND rpt.quotation_id = rp.quotation_id
                           AND rpt.times = rp.times
                           AND rpt.repayment_type = 'CONSULTATION'
                           AND rpt.repayment_date = rp.repayment_date),
                        0) outflow_amount,
                    NULL factoring_flag_n,
                    'N' status, --是否做过融资保理质押
                    (SELECT cba.bank_account_num
                       FROM tre_loan_con_write_off tlcwo,
                            csh_bank_account       cba
                      WHERE tlcwo.loan_contract_id = tlc.loan_contract_id
                        AND tlcwo.quotation_id = rp.quotation_id
                        AND tlcwo.document_id = rp.repayment_plan_id
                        AND tlcwo.bank_account_id = cba.bank_account_id) bank_account_num,
                    (SELECT cbb.bank_branch_name
                       FROM tre_loan_con_write_off tlcwo,
                            csh_bank_account       cba,
                            csh_bank_branch        cbb
                      WHERE tlcwo.loan_contract_id = tlc.loan_contract_id
                        AND tlcwo.quotation_id = rp.quotation_id
                        AND tlcwo.document_id = rp.repayment_plan_id
                        AND tlcwo.bank_account_id = cba.bank_account_id
                        AND cba.bank_branch_id = cbb.bank_branch_id) bank_branch_name
               FROM tre_loan_contract           tlc,
                    tre_loan_con_repayment_plan rp,
                    hls_bp_master               hbm
              WHERE tlc.data_class = 'NORMAL'
                AND tlc.loan_contract_id = rp.loan_contract_id
                AND rp.repayment_type = 'INTEREST'
                AND rp.quotation_id NOT IN
                    (SELECT pq.quotation_id
                       FROM prj_quotation pq
                      WHERE pq.document_category = 'LOAN_CONTRACT'
                        AND pq.document_id = tlc.loan_contract_id
                        AND NVL(pq.main_contract_flag, 'N') = 'Y')
                AND tlc.loan_con_bp = hbm.bp_id
                AND TO_CHAR(rp.repayment_date, 'YYYYMM') = ${@calc_year}
                        || ${@calc_month}
              union
               SELECT tlc.loan_contract_id document_id,
                      rp.repayment_date due_date,
                      TO_CHAR(rp.repayment_date,'YYYY-MM-DD')  due_date_day,
                      tlc.loan_contract_number document_number,
                      tlc.loan_contract_name document_name,
                      hbm.bp_name,
                      (SELECT wp.amount
                         FROM tre_loan_con_withdrawal_plan wp
                        WHERE wp.quotation_id = rp.quotation_id
                          AND wp.loan_contract_id = tlc.loan_contract_id) contract_amount,
                      (SELECT rptv.code_value_name
                         FROM tre_loan_con_repayment_plan rpt,
                              sys_code_values_v           rptv
                        WHERE rpt.loan_contract_id = tlc.loan_contract_id
                          AND rpt.quotation_id = rp.quotation_id
                          AND rpt.times = rp.times
                          AND rpt.repayment_type = 'ARRANGEMENT'
                          AND rpt.repayment_date = rp.repayment_date
                          AND rptv.code = 'TRE_LOAN_REPAYMENT_TYPE'
                          AND rptv.code_value = rpt.repayment_type) cf_item_n,
                      NULL inflow_amount,
                      NVL((SELECT rpt.amount
                            FROM tre_loan_con_repayment_plan rpt
                           WHERE rpt.loan_contract_id =
                                 tlc.loan_contract_id
                             AND rpt.quotation_id = rp.quotation_id
                             AND rpt.times = rp.times
                             AND rpt.repayment_type = 'ARRANGEMENT'
                             AND rpt.repayment_date = rp.repayment_date),
                          0) outflow_amount,
                      NULL factoring_flag_n,
                      'N' status, --是否做过融资保理质押
                      (SELECT cba.bank_account_num
                         FROM tre_loan_con_write_off tlcwo,
                              csh_bank_account       cba
                        WHERE tlcwo.loan_contract_id = tlc.loan_contract_id
                          AND tlcwo.quotation_id = rp.quotation_id
                          AND tlcwo.document_id = rp.repayment_plan_id
                          AND tlcwo.bank_account_id = cba.bank_account_id) bank_account_num,
                      (SELECT cbb.bank_branch_name
                         FROM tre_loan_con_write_off tlcwo,
                              csh_bank_account       cba,
                              csh_bank_branch        cbb
                        WHERE tlcwo.loan_contract_id = tlc.loan_contract_id
                          AND tlcwo.quotation_id = rp.quotation_id
                          AND tlcwo.document_id = rp.repayment_plan_id
                          AND tlcwo.bank_account_id = cba.bank_account_id
                          AND cba.bank_branch_id = cbb.bank_branch_id) bank_branch_name
                 FROM tre_loan_contract           tlc,
                      tre_loan_con_repayment_plan rp,
                      hls_bp_master               hbm
                WHERE tlc.data_class = 'NORMAL'
                  AND tlc.loan_contract_id = rp.loan_contract_id
                  AND rp.repayment_type = 'INTEREST'
                  AND rp.quotation_id NOT IN
                      (SELECT pq.quotation_id
                         FROM prj_quotation pq
                        WHERE pq.document_category = 'LOAN_CONTRACT'
                          AND pq.document_id = tlc.loan_contract_id
                          AND NVL(pq.main_contract_flag, 'N') = 'Y')
                  AND tlc.loan_con_bp = hbm.bp_id
                  AND TO_CHAR(rp.repayment_date, 'YYYYMM') = ${@calc_year}
                        || ${@calc_month}
               union
               --3
               SELECT tlc.loan_contract_id document_id,
                      rp.repayment_date due_date,
                      TO_CHAR(rp.repayment_date, 'YYYY-MM-DD')  due_date_day,
                      tlc.loan_contract_number document_number,
                      tlc.loan_contract_name document_name,
                      hbm.bp_name,
                      (SELECT wp.amount
                         FROM tre_loan_con_withdrawal_plan wp
                        WHERE wp.quotation_id = rp.quotation_id
                          AND wp.loan_contract_id = tlc.loan_contract_id) contract_amount,
                      (SELECT v.code_value_name
                         FROM sys_code_values_v v
                        WHERE v.code = 'TRE_LOAN_REPAYMENT_TYPE'
                          AND v.code_value = rp.repayment_type) cf_item_n,
                      NULL inflow_amount,
                      NVL(rp.amount, 0) outflow_amount,
                      NULL factoring_flag_n,
                      'N' status, --是否做过融资保理质押
                      (SELECT cba.bank_account_num
                         FROM tre_loan_con_write_off tlcwo,
                              csh_bank_account       cba
                        WHERE tlcwo.loan_contract_id = tlc.loan_contract_id
                          AND tlcwo.quotation_id = rp.quotation_id
                          AND tlcwo.document_id = rp.repayment_plan_id
                          AND tlcwo.bank_account_id = cba.bank_account_id) bank_account_num,
                      (SELECT cbb.bank_branch_name
                         FROM tre_loan_con_write_off tlcwo,
                              csh_bank_account       cba,
                              csh_bank_branch        cbb
                        WHERE tlcwo.loan_contract_id = tlc.loan_contract_id
                          AND tlcwo.quotation_id = rp.quotation_id
                          AND tlcwo.document_id = rp.repayment_plan_id
                          AND tlcwo.bank_account_id = cba.bank_account_id
                          AND cba.bank_branch_id = cbb.bank_branch_id) bank_branch_name
                 FROM tre_loan_contract           tlc,
                      tre_loan_con_repayment_plan rp,
                      hls_bp_master               hbm
                WHERE tlc.data_class = 'NORMAL'
                  AND tlc.loan_contract_id = rp.loan_contract_id
                  AND rp.repayment_type = 'PRINCIPAL'
                  AND rp.quotation_id NOT IN
                      (SELECT pq.quotation_id
                         FROM prj_quotation pq
                        WHERE pq.document_category = 'LOAN_CONTRACT'
                          AND pq.document_id = tlc.loan_contract_id
                          AND NVL(pq.main_contract_flag, 'N') = 'Y')
                  AND tlc.loan_con_bp = hbm.bp_id
                  AND rp.times NOT IN
                      (SELECT prp.times
                         FROM tre_loan_con_repayment_plan prp
                        WHERE prp.loan_contract_id = tlc.loan_contract_id
                          AND prp.times = rp.times
                          AND prp.quotation_id = rp.quotation_id
                          AND prp.repayment_type = 'INTEREST'
                          AND prp.repayment_date = rp.repayment_date)
                  AND TO_CHAR(rp.repayment_date, 'YYYYMM') =${@calc_year}
                        || ${@calc_month}
                union
                --4
                SELECT tlc.loan_contract_id document_id,
                       rp.repayment_date due_date,
                       TO_CHAR(rp.repayment_date, 'YYYY-MM-DD')  due_date_day,
                       tlc.loan_contract_number document_number,
                       tlc.loan_contract_name document_name,
                       hbm.bp_name,
                       (SELECT wp.amount
                          FROM tre_loan_con_withdrawal_plan wp
                         WHERE wp.quotation_id = rp.quotation_id
                           AND wp.loan_contract_id = tlc.loan_contract_id) contract_amount,
                       (SELECT v.code_value_name
                          FROM sys_code_values_v v
                         WHERE v.code = 'TRE_LOAN_REPAYMENT_TYPE'
                           AND v.code_value = rp.repayment_type) cf_item_n,
                       NULL inflow_amount,
                       NVL(rp.amount, 0) outflow_amount,
                       NULL factoring_flag_n,
                       'N' status, --是否做过融资保理质押
                       (SELECT cba.bank_account_num
                          FROM tre_loan_con_write_off tlcwo,
                               csh_bank_account       cba
                         WHERE tlcwo.loan_contract_id = tlc.loan_contract_id
                           AND tlcwo.quotation_id = rp.quotation_id
                           AND tlcwo.document_id = rp.repayment_plan_id
                           AND tlcwo.bank_account_id = cba.bank_account_id) bank_account_num,
                       (SELECT cbb.bank_branch_name
                          FROM tre_loan_con_write_off tlcwo,
                               csh_bank_account       cba,
                               csh_bank_branch        cbb
                         WHERE tlcwo.loan_contract_id = tlc.loan_contract_id
                           AND tlcwo.quotation_id = rp.quotation_id
                           AND tlcwo.document_id = rp.repayment_plan_id
                           AND tlcwo.bank_account_id = cba.bank_account_id
                           AND cba.bank_branch_id = cbb.bank_branch_id) bank_branch_name
                  FROM tre_loan_contract           tlc,
                       tre_loan_con_repayment_plan rp,
                       hls_bp_master               hbm
                 WHERE tlc.data_class = 'NORMAL'
                   AND tlc.loan_contract_id = rp.loan_contract_id
                   AND rp.repayment_type = 'CHARGE'
                   AND rp.quotation_id NOT IN
                       (SELECT pq.quotation_id
                          FROM prj_quotation pq
                         WHERE pq.document_category = 'LOAN_CONTRACT'
                           AND pq.document_id = tlc.loan_contract_id
                           AND NVL(pq.main_contract_flag, 'N') = 'Y')
                   AND tlc.loan_con_bp = hbm.bp_id
                   AND rp.times NOT IN
                       (SELECT prp.times
                          FROM tre_loan_con_repayment_plan prp
                         WHERE prp.loan_contract_id = tlc.loan_contract_id
                           AND prp.times = rp.times
                           AND prp.quotation_id = rp.quotation_id
                           AND prp.repayment_type = 'INTEREST'
                           AND prp.repayment_date = rp.repayment_date)
                   AND TO_CHAR(rp.repayment_date, 'YYYYMM') = ${@calc_year}
                        || ${@calc_month}
                   union
                   --5
                   SELECT
                        tlc.loan_contract_id document_id,
                        rp.repayment_date due_date,
                        TO_CHAR(rp.repayment_date, 'YYYY-MM-DD')
                         due_date_day,
                        tlc.loan_contract_number document_number,
                        tlc.loan_contract_name document_name,
                        hbm.bp_name,
                        (SELECT
                            wp.amount
                        FROM
                            tre_loan_con_withdrawal_plan wp
                        WHERE
                            wp.quotation_id     = rp.quotation_id AND
                            wp.loan_contract_id = tlc.loan_contract_id
                        ) contract_amount,
                        (SELECT
                            v.code_value_name
                        FROM
                            sys_code_values_v v
                        WHERE
                            v.code       = 'TRE_LOAN_REPAYMENT_TYPE' AND
                            v.code_value = rp.repayment_type
                        ) cf_item_n,
                        NULL inflow_amount,
                        NVL(rp.amount, 0) outflow_amount,
                        NULL factoring_flag_n,
                        'N' status,--是否做过融资保理质押
                        (SELECT
                            cba.bank_account_num
                        FROM
                            tre_loan_con_write_off tlcwo,
                            csh_bank_account cba
                        WHERE
                            tlcwo.loan_contract_id=tlc.loan_contract_id AND
                            tlcwo.quotation_id    =rp.quotation_id AND
                            tlcwo.document_id     = rp.repayment_plan_id AND
                            tlcwo.bank_account_id = cba.bank_account_id
                        ) bank_account_num,
                        (SELECT
                            cbb.bank_branch_name
                        FROM
                            tre_loan_con_write_off tlcwo,
                            csh_bank_account cba,
                            csh_bank_branch cbb
                        WHERE
                            tlcwo.loan_contract_id=tlc.loan_contract_id AND
                            tlcwo.quotation_id    =rp.quotation_id AND
                            tlcwo.document_id     = rp.repayment_plan_id AND
                            tlcwo.bank_account_id = cba.bank_account_id AND
                            cba.bank_branch_id    =cbb.bank_branch_id
                        ) bank_branch_name
                    FROM
                        tre_loan_contract tlc,
                        tre_loan_con_repayment_plan rp,
                        hls_bp_master hbm
                    WHERE
                        tlc.data_class       ='NORMAL' AND
                        tlc.loan_contract_id = rp.loan_contract_id AND
                        rp.repayment_type    = 'CONSULTATION' AND
                        rp.quotation_id NOT IN
                        (SELECT
                            pq.quotation_id
                        FROM
                            prj_quotation pq
                        WHERE
                            pq.document_category            = 'LOAN_CONTRACT' AND
                            pq.document_id                  = tlc.loan_contract_id AND
                            NVL(pq.main_contract_flag, 'N') = 'Y'
                        ) AND
                        tlc.loan_con_bp = hbm.bp_id AND
                        rp.times NOT   IN
                        (SELECT
                            prp.times
                        FROM
                            tre_loan_con_repayment_plan prp
                        WHERE
                            prp.loan_contract_id = tlc.loan_contract_id AND
                            prp.times            = rp.times AND
                            prp.quotation_id     = rp.quotation_id AND
                            prp.repayment_type   = 'INTEREST' AND
                            prp.repayment_date   = rp.repayment_date
                        ) AND
                        TO_CHAR(rp.repayment_date, 'YYYYMM') = ${@calc_year}
                        ||${@calc_month}
                      union
                      --6
                      SELECT
                        tlc.loan_contract_id document_id,
                        rp.repayment_date due_date,
                        TO_CHAR(rp.repayment_date, 'YYYY-MM-DD')
                         due_date_day,
                        tlc.loan_contract_number document_number,
                        tlc.loan_contract_name document_name,
                        hbm.bp_name,
                        (SELECT
                            wp.amount
                        FROM
                            tre_loan_con_withdrawal_plan wp
                        WHERE
                            wp.quotation_id     = rp.quotation_id AND
                            wp.loan_contract_id = tlc.loan_contract_id
                        ) contract_amount,
                        (SELECT
                            v.code_value_name
                        FROM
                            sys_code_values_v v
                        WHERE
                            v.code       = 'TRE_LOAN_REPAYMENT_TYPE' AND
                            v.code_value = rp.repayment_type
                        ) cf_item_n,
                        NULL inflow_amount,
                        NVL(rp.amount, 0) outflow_amount,
                        NULL factoring_flag_n,
                        'N' status,--是否做过融资保理质押
                        (SELECT
                            cba.bank_account_num
                        FROM
                            tre_loan_con_write_off tlcwo,
                            csh_bank_account cba
                        WHERE
                            tlcwo.loan_contract_id=tlc.loan_contract_id AND
                            tlcwo.quotation_id    =rp.quotation_id AND
                            tlcwo.document_id     = rp.repayment_plan_id AND
                            tlcwo.bank_account_id = cba.bank_account_id
                        ) bank_account_num,
                        (SELECT
                            cbb.bank_branch_name
                        FROM
                            tre_loan_con_write_off tlcwo,
                            csh_bank_account cba,
                            csh_bank_branch cbb
                        WHERE
                            tlcwo.loan_contract_id=tlc.loan_contract_id AND
                            tlcwo.quotation_id    =rp.quotation_id AND
                            tlcwo.document_id     = rp.repayment_plan_id AND
                            tlcwo.bank_account_id = cba.bank_account_id AND
                            cba.bank_branch_id    =cbb.bank_branch_id
                        ) bank_branch_name
                    FROM
                        tre_loan_contract tlc,
                        tre_loan_con_repayment_plan rp,
                        hls_bp_master hbm
                    WHERE
                        tlc.data_class       ='NORMAL' AND
                        tlc.loan_contract_id = rp.loan_contract_id AND
                        rp.repayment_type    = 'ARRANGEMENT' AND
                        rp.quotation_id NOT IN
                        (SELECT
                            pq.quotation_id
                        FROM
                            prj_quotation pq
                        WHERE
                            pq.document_category            = 'LOAN_CONTRACT' AND
                            pq.document_id                  = tlc.loan_contract_id AND
                            NVL(pq.main_contract_flag, 'N') = 'Y'
                        ) AND
                        tlc.loan_con_bp = hbm.bp_id AND
                        rp.times NOT   IN
                        (SELECT
                            prp.times
                        FROM
                            tre_loan_con_repayment_plan prp
                        WHERE
                            prp.loan_contract_id = tlc.loan_contract_id AND
                            prp.times            = rp.times AND
                            prp.quotation_id     = rp.quotation_id AND
                            prp.repayment_type   = 'INTEREST' AND
                            prp.repayment_date   = rp.repayment_date
                        ) AND
                        TO_CHAR(rp.repayment_date, 'YYYYMM') = ${@calc_year}
                        ||${@calc_month}
                      union
                      --7
                      SELECT
                        soc1.contract_id document_id,
                        socc1.due_date due_date,
                        TO_CHAR(socc1.due_date, 'YYYY-MM-DD')
                         due_date_day,
                        soc1.contract_number document_number,
                        soc1.contract_name document_name,
                        (SELECT
                            hbm1.bp_name
                        FROM
                            sdic_other_contract_bp socb1,
                            hls_bp_master hbm1
                        WHERE
                            socb1.primary_flag = 'Y' AND
                            hbm1.bp_id         = socb1.bp_id AND
                            socb1.contract_id  = soc1.contract_id AND
                            rownum             = 1
                        ) bp_name,
                        soc1.contract_amount contract_amount,
                        socc1.cf_description cf_item_n,
                        DECODE(NVL(socc1.cf_direction,'INFLOW'), 'INFLOW', socc1.due_amount, NULL) inflow_amount,
                        DECODE(NVL(socc1.cf_direction,'INFLOW'), 'OUTFLOW', socc1.due_amount, NULL) outflow_amount,
                        '未抵质押' factoring_flag_n,
                        'N' status,
                        (SELECT
                            v.bank_account_num
                        FROM
                            csh_bank_account_v v
                        WHERE
                            v.bank_account_id = soc1.bank_account_id
                        ) bank_account_num,
                        (SELECT
                            v.bank_branch_name
                        FROM
                            csh_bank_account_v v
                        WHERE
                            v.bank_account_id = soc1.bank_account_id
                        ) bank_branch_name
                    FROM
                        sdic_other_contract soc1,
                        sdic_other_contract_cf socc1
                    WHERE
                        soc1.contract_id                  = socc1.contract_id AND
                        soc1.wfl_status                   = 'APPROVED' AND
                        soc1.data_class                   = 'NORMAL' AND
                        TO_CHAR(socc1.due_date, 'YYYYMM') = ${@calc_year}
                        || ${@calc_month}
                    union
                    --8
   select tlc.loan_contract_id document_id,
          wp.widthdrawal_date due_date,
          to_char(wp.widthdrawal_date, 'YYYY-MM-DD')  due_date_day,
          tlc.loan_contract_number document_number,
          tlc.loan_contract_name document_name,
          hbm.bp_name,
          
          (select t1.amount
             from tre_loan_con_withdrawal_plan t1
            where t1.quotation_id = wp.quotation_id
              and t1.loan_contract_id = tlc.loan_contract_id) contract_amount,
          '提款' cf_item_n,
          nvl(wp.amount, 0) inflow_amount,
          null outflow_amount,
          null factoring_flag_n,
          'N' status, --是否做过融资保理质押
          (select cba.bank_account_num
             from tre_loan_con_write_off tlcwo, csh_bank_account cba
            where tlcwo.loan_contract_id = tlc.loan_contract_id
              and tlcwo.quotation_id = wp.quotation_id
              and tlcwo.document_id = wp.widthdrawal_plan_id
              and tlcwo.bank_account_id = cba.bank_account_id) bank_account_num,
          (select cbb.bank_branch_name
             from tre_loan_con_write_off tlcwo,
                  csh_bank_account       cba,
                  csh_bank_branch        cbb
            where tlcwo.loan_contract_id = tlc.loan_contract_id
              and tlcwo.quotation_id = wp.quotation_id
              and tlcwo.document_id = wp.widthdrawal_plan_id
              and tlcwo.bank_account_id = cba.bank_account_id
              and cba.bank_branch_id = cbb.bank_branch_id) bank_branch_name
     from tre_loan_contract            tlc,
          tre_loan_con_withdrawal_plan wp,
          hls_bp_master                hbm
    where tlc.data_class = 'NORMAL'
      and tlc.loan_contract_id = wp.loan_contract_id
      and wp.quotation_id not in
          (select pq.quotation_id
             from prj_quotation pq
            where pq.document_category = 'LOAN_CONTRACT'
              and pq.document_id = tlc.loan_contract_id
              and nvl(pq.main_contract_flag, 'N') = 'Y')
      and tlc.loan_con_bp = hbm.bp_id
         
      and to_char(wp.widthdrawal_date, 'YYYYMM')
         
          = ${@calc_year} || ${@calc_month}
          union
          --9
           SELECT cc.contract_id document_id,
                  ccc.due_date,
                  TO_CHAR(ccc.due_date, 'YYYY-MM-DD')  due_date_day,
                  cc.contract_number document_number,
                  cc.contract_name document_name,
                  hbm.bp_name,
                  cc.lease_item_amount contract_amount,
                  (SELECT hci.description
                     FROM hls_cashflow_item hci
                    WHERE hci.cf_item = ccc.cf_item) cf_item_n,
                  NVL((SELECT cccl.due_amount
                        FROM con_contract_cashflow cccl
                       WHERE cccl.contract_id = cc.contract_id
                         AND cccl.times = ccc.times
                         AND cccl.cf_item = 3),
                      0) inflow_amount,
                  NVL(ccc.due_amount, 0) outflow_amount,
                  NVL((SELECT '抵质押中'
                        FROM dual
                       WHERE EXISTS
                       (SELECT 1
                                FROM bgfl_tre_factoring_contract btfc
                               WHERE btfc.contract_id = cc.contract_id
                                 AND btfc.times_from <= ccc.times
                                 AND btfc.times_to >= ccc.times)),
                      '未抵质押') factoring_flag_n,
                  NVL((SELECT 'Y'
                        FROM dual
                       WHERE EXISTS
                       (SELECT 1
                                FROM hn_abs_contract       hac,
                                     con_contract_cashflow ccca
                               WHERE hac.contract_id = cc.contract_id
                                 AND ccca.contract_id = cc.contract_id
                                 AND ccca.times >= hac.start_times
                                 AND ccca.times <= hac.end_times
                                 AND TO_CHAR(ccca.due_date, 'YYYYMM') =
                                     ${@calc_year} || ${@calc_month})),
                      'N') status, --是否做过融资保理质押
                  (SELECT cba.bank_account_num
                     FROM hn_con_bank_account hcba, csh_bank_account cba
                    WHERE hcba.contract_id = cc.contract_id
                      AND hcba.valid_from <= ccc.due_date
                      AND hcba.valid_to > ccc.due_date
                      AND hcba.bank_account_id = cba.bank_account_id) bank_account_num,
                  (SELECT cbb.bank_branch_name
                     FROM hn_con_bank_account hcba,
                          csh_bank_account    cba,
                          csh_bank_branch     cbb
                    WHERE hcba.contract_id = cc.contract_id
                      AND hcba.valid_from <= ccc.due_date
                      AND hcba.valid_to > ccc.due_date
                      AND hcba.bank_account_id = cba.bank_account_id
                      AND cba.bank_branch_id = cbb.bank_branch_id) bank_branch_name
             FROM con_contract          cc,
                  con_contract_cashflow ccc,
                  hls_bp_master         hbm
            WHERE cc.contract_status <> 'CANCEL'
              AND cc.contract_id = ccc.contract_id
              AND cc.data_class = 'NORMAL'
              AND ccc.cf_item = 0
              AND EXISTS (SELECT 1
                     FROM csh_write_off cwo
                    WHERE cwo.contract_id = cc.contract_id
                      AND cwo.reversed_flag = 'N'
                      AND cwo.write_off_type = 'PAYMENT_DEBT'
                      AND cwo.cf_item = 0)
              AND cc.bp_id_tenant = hbm.bp_id
              AND TO_CHAR(ccc.due_date, 'YYYYMM') =
                  ${@calc_year} || ${@calc_month}
            union
             SELECT cc.contract_id document_id,
                    ccc.due_date,
                    TO_CHAR(ccc.due_date, 'YYYY-MM-DD')  due_date_day,
                    cc.contract_number document_number,
                    cc.contract_name document_name,
                    hbm.bp_name,
                    cc.lease_item_amount contract_amount,
                    (SELECT hcit.description
                       FROM con_contract_cashflow ccct,
                            hls_cashflow_item     hcit
                      WHERE ccct.contract_id = ccc.contract_id
                        AND ccct.times = ccc.times
                        AND ccct.cf_item = 3
                        AND ccct.cf_item = hcit.cf_item) cf_item_n,
                    NVL((SELECT cccl.due_amount
                          FROM con_contract_cashflow cccl
                         WHERE cccl.contract_id = cc.contract_id
                           AND cccl.times = ccc.times
                           AND cccl.cf_item = 3),
                        0)  inflow_amount,
                    NVL(ccc.due_amount, 0) outflow_amount,
                    NVL((SELECT '抵质押中'
                          FROM dual
                         WHERE EXISTS
                         (SELECT 1
                                  FROM bgfl_tre_factoring_contract btfc
                                 WHERE btfc.contract_id = cc.contract_id
                                   AND btfc.times_from <= ccc.times
                                   AND btfc.times_to >= ccc.times)),
                        '未抵质押') factoring_flag_n,
                    NVL((SELECT 'Y'
                          FROM dual
                         WHERE EXISTS
                         (SELECT 1
                                  FROM hn_abs_contract       hac,
                                       con_contract_cashflow ccca
                                 WHERE hac.contract_id = cc.contract_id
                                   AND ccca.contract_id = cc.contract_id
                                   AND ccca.times >= hac.start_times
                                   AND ccca.times <= hac.end_times
                                   AND TO_CHAR(ccca.due_date, 'YYYYMM') =
                                       ${@calc_year} || ${@calc_month})),
                        'N') status, --是否做过融资保理质押
                    (SELECT cba.bank_account_num
                       FROM hn_con_bank_account hcba, csh_bank_account cba
                      WHERE hcba.contract_id = cc.contract_id
                        AND hcba.valid_from <= ccc.due_date
                        AND hcba.valid_to > ccc.due_date
                        AND hcba.bank_account_id = cba.bank_account_id) bank_account_num,
                    (SELECT cbb.bank_branch_name
                       FROM hn_con_bank_account hcba,
                            csh_bank_account    cba,
                            csh_bank_branch     cbb
                      WHERE hcba.contract_id = cc.contract_id
                        AND hcba.valid_from <= ccc.due_date
                        AND hcba.valid_to > ccc.due_date
                        AND hcba.bank_account_id = cba.bank_account_id
                        AND cba.bank_branch_id = cbb.bank_branch_id) bank_branch_name
               FROM con_contract          cc,
                    con_contract_cashflow ccc,
                    hls_bp_master         hbm
              WHERE cc.contract_status <> 'CANCEL'
                AND cc.contract_id = ccc.contract_id
                AND cc.data_class = 'NORMAL'
                AND ccc.cf_item = 0
                AND EXISTS (SELECT 1
                       FROM csh_write_off cwo
                      WHERE cwo.contract_id = cc.contract_id
                        AND cwo.reversed_flag = 'N'
                        AND cwo.write_off_type = 'PAYMENT_DEBT'
                        AND cwo.cf_item = 0)
                AND cc.bp_id_tenant = hbm.bp_id
                AND TO_CHAR(ccc.due_date, 'YYYYMM') =
                    ${@calc_year} || ${@calc_month}
              union
               SELECT cc.contract_id document_id,
                      ccc.due_date,
                      TO_CHAR(ccc.due_date, 'YYYY-MM-DD')  due_date_day,
                      cc.contract_number document_number,
                      cc.contract_name document_name,
                      hbm.bp_name,
                      cc.lease_item_amount contract_amount,
                      (SELECT hcit.description
                         FROM con_contract_cashflow ccct,
                              hls_cashflow_item     hcit
                        WHERE ccct.contract_id = ccc.contract_id
                          AND ccct.times = ccc.times
                          AND ccct.cf_item = 2
                          AND ccct.cf_item = hcit.cf_item) cf_item_n,
                      NVL((SELECT cccl.due_amount
                            FROM con_contract_cashflow cccl
                           WHERE cccl.contract_id = cc.contract_id
                             AND cccl.times = ccc.times
                             AND cccl.cf_item =2),
                          0) inflow_amount,
                      NVL(ccc.due_amount, 0) outflow_amount,
                      NVL((SELECT '抵质押中'
                            FROM dual
                           WHERE EXISTS
                           (SELECT 1
                                    FROM bgfl_tre_factoring_contract btfc
                                   WHERE btfc.contract_id = cc.contract_id
                                     AND btfc.times_from <= ccc.times
                                     AND btfc.times_to >= ccc.times)),
                          '未抵质押') factoring_flag_n,
                      NVL((SELECT 'Y'
                            FROM dual
                           WHERE EXISTS
                           (SELECT 1
                                    FROM hn_abs_contract       hac,
                                         con_contract_cashflow ccca
                                   WHERE hac.contract_id = cc.contract_id
                                     AND ccca.contract_id = cc.contract_id
                                     AND ccca.times >= hac.start_times
                                     AND ccca.times <= hac.end_times
                                     AND TO_CHAR(ccca.due_date, 'YYYYMM') =
                                         ${@calc_year} || ${@calc_month})),
                          'N') status, --是否做过融资保理质押
                      (SELECT cba.bank_account_num
                         FROM hn_con_bank_account hcba, csh_bank_account cba
                        WHERE hcba.contract_id = cc.contract_id
                          AND hcba.valid_from <= ccc.due_date
                          AND hcba.valid_to > ccc.due_date
                          AND hcba.bank_account_id = cba.bank_account_id) bank_account_num,
                      (SELECT cbb.bank_branch_name
                         FROM hn_con_bank_account hcba,
                              csh_bank_account    cba,
                              csh_bank_branch     cbb
                        WHERE hcba.contract_id = cc.contract_id
                          AND hcba.valid_from <= ccc.due_date
                          AND hcba.valid_to > ccc.due_date
                          AND hcba.bank_account_id = cba.bank_account_id
                          AND cba.bank_branch_id = cbb.bank_branch_id) bank_branch_name
                 FROM con_contract          cc,
                      con_contract_cashflow ccc,
                      hls_bp_master         hbm
                WHERE cc.contract_status <> 'CANCEL'
                  AND cc.contract_id = ccc.contract_id
                  AND cc.data_class = 'NORMAL'
                  AND ccc.cf_item = 0
                  AND EXISTS
                (SELECT 1
                         FROM csh_write_off cwo
                        WHERE cwo.contract_id = cc.contract_id
                          AND cwo.reversed_flag = 'N'
                          AND cwo.write_off_type = 'PAYMENT_DEBT'
                          AND cwo.cf_item = 0)
                  AND cc.bp_id_tenant = hbm.bp_id
                  AND TO_CHAR(ccc.due_date, 'YYYYMM') =
                      ${@calc_year} || ${@calc_month}
               union
                SELECT cc.contract_id document_id,
                       ccc.due_date,
                       TO_CHAR(ccc.due_date, 'YYYY-MM-DD')  due_date_day,
                       cc.contract_number document_number,
                       cc.contract_name document_name,
                       hbm.bp_name,
                       cc.lease_item_amount contract_amount,
                       (SELECT '+' || hcit.description
                          FROM con_contract_cashflow ccct,
                               hls_cashflow_item     hcit
                         WHERE ccct.contract_id = ccc.contract_id
                           AND ccct.times = ccc.times
                           AND ccct.cf_item = 5
                           AND ccct.cf_item = hcit.cf_item) cf_item_n,
                       NVL((SELECT cccl.due_amount
                             FROM con_contract_cashflow cccl
                            WHERE cccl.contract_id = cc.contract_id
                              AND cccl.times = ccc.times
                              AND cccl.cf_item = 5),
                           0) inflow_amount,
                       NVL(ccc.due_amount, 0) outflow_amount,
                       NVL((SELECT '抵质押中'
                             FROM dual
                            WHERE EXISTS
                            (SELECT 1
                                     FROM bgfl_tre_factoring_contract btfc
                                    WHERE btfc.contract_id = cc.contract_id
                                      AND btfc.times_from <= ccc.times
                                      AND btfc.times_to >= ccc.times)),
                           '未抵质押') factoring_flag_n,
                       NVL((SELECT 'Y'
                             FROM dual
                            WHERE EXISTS
                            (SELECT 1
                                     FROM hn_abs_contract       hac,
                                          con_contract_cashflow ccca
                                    WHERE hac.contract_id = cc.contract_id
                                      AND ccca.contract_id = cc.contract_id
                                      AND ccca.times >= hac.start_times
                                      AND ccca.times <= hac.end_times
                                      AND TO_CHAR(ccca.due_date, 'YYYYMM') =
                                          ${@calc_year} || ${@calc_month})),
                           'N') status, --是否做过融资保理质押
                       (SELECT cba.bank_account_num
                          FROM hn_con_bank_account hcba,
                               csh_bank_account    cba
                         WHERE hcba.contract_id = cc.contract_id
                           AND hcba.valid_from <= ccc.due_date
                           AND hcba.valid_to > ccc.due_date
                           AND hcba.bank_account_id = cba.bank_account_id) bank_account_num,
                       (SELECT cbb.bank_branch_name
                          FROM hn_con_bank_account hcba,
                               csh_bank_account    cba,
                               csh_bank_branch     cbb
                         WHERE hcba.contract_id = cc.contract_id
                           AND hcba.valid_from <= ccc.due_date
                           AND hcba.valid_to > ccc.due_date
                           AND hcba.bank_account_id = cba.bank_account_id
                           AND cba.bank_branch_id = cbb.bank_branch_id) bank_branch_name
                  FROM con_contract          cc,
                       con_contract_cashflow ccc,
                       hls_bp_master         hbm
                 WHERE cc.contract_status <> 'CANCEL'
                   AND cc.contract_id = ccc.contract_id
                   AND cc.data_class = 'NORMAL'
                   AND ccc.cf_item = 0
                   AND EXISTS
                 (SELECT 1
                          FROM csh_write_off cwo
                         WHERE cwo.contract_id = cc.contract_id
                           AND cwo.reversed_flag = 'N'
                           AND cwo.write_off_type = 'PAYMENT_DEBT'
                           AND cwo.cf_item = 0)
                   AND cc.bp_id_tenant = hbm.bp_id
                   AND TO_CHAR(ccc.due_date, 'YYYYMM') =
                       ${@calc_year} || ${@calc_month}
                 union
                  SELECT
                        cc.contract_id document_id,
                        ccc.due_date,
                        TO_CHAR(ccc.due_date, 'YYYY-MM-DD')
                         due_date_day,
                        cc.contract_number document_number,
                        cc.contract_name document_name,
                        hbm.bp_name,
                        cc.lease_item_amount contract_amount,
                        (SELECT
                             hcit.description
                        FROM
                            con_contract_cashflow ccct,
                            hls_cashflow_item hcit
                        WHERE
                            ccct.contract_id = ccc.contract_id AND
                            ccct.times       = ccc.times AND
                            ccct.cf_item     = 12 AND
                            ccct.cf_item     = hcit.cf_item
                        )
                         cf_item_n,
                         NVL(
                        (SELECT
                            cccl.due_amount
                        FROM
                            con_contract_cashflow cccl
                        WHERE
                            cccl.contract_id = cc.contract_id AND
                            cccl.times       = ccc.times AND
                            cccl.cf_item = 12
                        ), 0) inflow_amount,
                        NVL(ccc.due_amount, 0) outflow_amount,
                        NVL(
                        (SELECT
                            '抵质押中'
                        FROM
                            dual
                        WHERE
                            EXISTS
                            (SELECT
                                1
                            FROM
                                bgfl_tre_factoring_contract btfc
                            WHERE
                                btfc.contract_id = cc.contract_id AND
                                btfc.times_from <= ccc.times AND
                                btfc.times_to   >= ccc.times
                            )
                        ), '未抵质押') factoring_flag_n,
                        NVL(
                        (SELECT
                            'Y'
                        FROM
                            dual
                        WHERE
                            EXISTS
                            (SELECT
                                1
                            FROM
                                hn_abs_contract hac,
                                con_contract_cashflow ccca
                            WHERE
                                hac.contract_id                  = cc.contract_id AND
                                ccca.contract_id                 = cc.contract_id AND
                                ccca.times                      >= hac.start_times AND
                                ccca.times                      <= hac.end_times AND
                                TO_CHAR(ccca.due_date, 'YYYYMM') = ${@calc_year}
                                ||${@calc_month}
                            )
                        ),'N') status,--是否做过融资保理质押
                        (SELECT
                            cba.bank_account_num
                        FROM
                            hn_con_bank_account hcba,
                            csh_bank_account cba
                        WHERE
                            hcba.contract_id     = cc.contract_id AND
                            hcba.valid_from     <= ccc.due_date AND
                            hcba.valid_to        > ccc.due_date AND
                            hcba.bank_account_id = cba.bank_account_id
                        ) bank_account_num,
                        (SELECT
                            cbb.bank_branch_name
                        FROM
                            hn_con_bank_account hcba,
                            csh_bank_account cba,
                            csh_bank_branch cbb
                        WHERE
                            hcba.contract_id     = cc.contract_id AND
                            hcba.valid_from     <= ccc.due_date AND
                            hcba.valid_to        > ccc.due_date AND
                            hcba.bank_account_id = cba.bank_account_id AND
                            cba.bank_branch_id   = cbb.bank_branch_id
                        ) bank_branch_name
                    FROM
                        con_contract cc,
                        con_contract_cashflow ccc,
                        hls_bp_master hbm
                    WHERE
                        cc.contract_status <> 'CANCEL' AND
                        cc.contract_id      = ccc.contract_id AND
                        cc.data_class       = 'NORMAL' AND
                        ccc.cf_item         = 0 AND
                        EXISTS
                        (SELECT
                            1
                        FROM
                            csh_write_off cwo
                        WHERE
                            cwo.contract_id    = cc.contract_id AND
                            cwo.reversed_flag  = 'N' AND
                            cwo.write_off_type = 'PAYMENT_DEBT' AND
                            cwo.cf_item        =0
                        ) AND
                        cc.bp_id_tenant                 = hbm.bp_id AND
                        TO_CHAR(ccc.due_date, 'YYYYMM') = ${@calc_year}
                        ||${@calc_month}
                      union
                       SELECT
                        cc.contract_id document_id,
                        ccc.due_date,
                        TO_CHAR(ccc.due_date, 'YYYY-MM-DD')
                        due_date_day,
                        cc.contract_number document_number,
                        cc.contract_name document_name,
                        hbm.bp_name,
                        cc.lease_item_amount contract_amount,
                        (SELECT
                            '+'
                            || hcit.description
                        FROM
                            con_contract_cashflow ccct,
                            hls_cashflow_item hcit
                        WHERE
                            ccct.contract_id = ccc.contract_id AND
                            ccct.times       = ccc.times AND
                            ccct.cf_item     = 15 AND
                            ccct.cf_item     = hcit.cf_item
                        ) cf_item_n,
                         NVL(
                        (SELECT
                            cccl.due_amount
                        FROM
                            con_contract_cashflow cccl
                        WHERE
                            cccl.contract_id = cc.contract_id AND
                            cccl.times       = ccc.times AND
                            cccl.cf_item     = 15
                        ), 0) inflow_amount,
                        NVL(ccc.due_amount, 0) outflow_amount,
                        NVL(
                        (SELECT
                            '抵质押中'
                        FROM
                            dual
                        WHERE
                            EXISTS
                            (SELECT
                                1
                            FROM
                                bgfl_tre_factoring_contract btfc
                            WHERE
                                btfc.contract_id = cc.contract_id AND
                                btfc.times_from <= ccc.times AND
                                btfc.times_to   >= ccc.times
                            )
                        ), '未抵质押') factoring_flag_n,
                        NVL(
                        (SELECT
                            'Y'
                        FROM
                            dual
                        WHERE
                            EXISTS
                            (SELECT
                                1
                            FROM
                                hn_abs_contract hac,
                                con_contract_cashflow ccca
                            WHERE
                                hac.contract_id                  = cc.contract_id AND
                                ccca.contract_id                 = cc.contract_id AND
                                ccca.times                      >= hac.start_times AND
                                ccca.times                      <= hac.end_times AND
                                TO_CHAR(ccca.due_date, 'YYYYMM') = ${@calc_year}
                                ||${@calc_month}
                            )
                        ),'N') status,--是否做过融资保理质押
                        (SELECT
                            cba.bank_account_num
                        FROM
                            hn_con_bank_account hcba,
                            csh_bank_account cba
                        WHERE
                            hcba.contract_id     = cc.contract_id AND
                            hcba.valid_from     <= ccc.due_date AND
                            hcba.valid_to        > ccc.due_date AND
                            hcba.bank_account_id = cba.bank_account_id
                        ) bank_account_num,
                        (SELECT
                            cbb.bank_branch_name
                        FROM
                            hn_con_bank_account hcba,
                            csh_bank_account cba,
                            csh_bank_branch cbb
                        WHERE
                            hcba.contract_id     = cc.contract_id AND
                            hcba.valid_from     <= ccc.due_date AND
                            hcba.valid_to        > ccc.due_date AND
                            hcba.bank_account_id = cba.bank_account_id AND
                            cba.bank_branch_id   = cbb.bank_branch_id
                        ) bank_branch_name
                    FROM
                        con_contract cc,
                        con_contract_cashflow ccc,
                        hls_bp_master hbm
                    WHERE
                        cc.contract_status <> 'CANCEL' AND
                        cc.contract_id      = ccc.contract_id AND
                        cc.data_class       = 'NORMAL' AND
                        ccc.cf_item         = 0 AND
                        EXISTS
                        (SELECT
                            1
                        FROM
                            csh_write_off cwo
                        WHERE
                            cwo.contract_id    = cc.contract_id AND
                            cwo.reversed_flag  = 'N' AND
                            cwo.write_off_type = 'PAYMENT_DEBT' AND
                            cwo.cf_item        =0
                        ) AND
                        cc.bp_id_tenant                 = hbm.bp_id AND
                        TO_CHAR(ccc.due_date, 'YYYYMM') = ${@calc_year}
                        ||${@calc_month}
                    ) t1 #WHERE_CLAUSE#
                ORDER BY
                    t1.due_date,
                    t1.document_number
            ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
    <bm:query-fields>
        <bm:query-field name="document_number" queryExpression="t1.document_number like &apos;%&apos;||${@document_number}||&apos;%&apos;"/>
        <bm:query-field name="document_name" queryExpression="t1.document_name like &apos;%&apos;||${@document_name}||&apos;%&apos;"/>
        <bm:query-field name="status_n" queryExpression="t1.status like &apos;%&apos;||${@status}||&apos;%&apos;"/>
    </bm:query-fields>
    <bm:data-filters>
        <bm:data-filter name="cf_item_n" expression="t1.cf_item_n is not NULL"/>
    </bm:data-filters>
</bm:model>
