<?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
  FROM con_contract            cc,
       con_contract_change_req cccr,
       con_contract_cashflow   ccc,
       hls_bp_master           hbm
 WHERE cc.contract_id = cccr.change_req_id
   AND cc.contract_id = ccc.contract_id
   AND cc.data_class = 'CHANGE_REQ'
   AND cccr.req_status = 'APPROVING'
   AND ccc.cf_item = 1
   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
  FROM con_contract            cc,
       con_contract_change_req cccr,
       con_contract_cashflow   ccc,
       hls_bp_master           hbm
 WHERE cc.contract_id = cccr.change_req_id
   AND cc.contract_id = ccc.contract_id
   AND cc.data_class = 'CHANGE_REQ'
   AND cccr.req_status = 'APPROVING'
   AND ccc.cf_item = 1
   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
  FROM con_contract            cc,
       con_contract_change_req cccr,
       con_contract_cashflow   ccc,
       hls_bp_master           hbm
 WHERE cc.contract_id = cccr.change_req_id
   AND cc.contract_id = ccc.contract_id
   AND cc.data_class = 'CHANGE_REQ'
   AND cccr.req_status = 'APPROVING'
   AND ccc.cf_item = 1
   AND cc.bp_id_tenant = hbm.bp_id
   AND TO_CHAR(ccc.due_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 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
                      FROM tre_loan_contract           tlc,
                           tre_loan_contract_change    tlcc,
                           tre_loan_con_repayment_plan rp,
                           hls_bp_master               hbm
                     WHERE tlc.loan_contract_id = tlcc.change_req_id
                       AND tlc.loan_contract_id = rp.loan_contract_id
                       AND rp.repayment_type = 'INTEREST'
                       AND tlcc.req_status = 'APPROVING'
                       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
   FROM tre_loan_contract           tlc,
        tre_loan_contract_change    tlcc,
        tre_loan_con_repayment_plan rp,
        hls_bp_master               hbm
  WHERE tlc.loan_contract_id = tlcc.change_req_id
    AND tlc.loan_contract_id = rp.loan_contract_id
    AND rp.repayment_type = 'INTEREST'
    AND tlcc.req_status = 'APPROVING'
    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
  FROM tre_loan_contract           tlc,
       tre_loan_contract_change    tlcc,
       tre_loan_con_repayment_plan rp,
       hls_bp_master               hbm
 WHERE tlc.loan_contract_id = tlcc.change_req_id
   AND tlc.loan_contract_id = rp.loan_contract_id
   AND rp.repayment_type = 'INTEREST'
   AND tlcc.req_status = 'APPROVING'
   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
  FROM tre_loan_contract           tlc,
       tre_loan_contract_change    tlcc,
       tre_loan_con_repayment_plan rp,
       hls_bp_master               hbm
 WHERE tlc.loan_contract_id = tlcc.change_req_id
   AND tlc.loan_contract_id = rp.loan_contract_id
   AND rp.repayment_type = 'INTEREST'
   AND tlcc.req_status = 'APPROVING'
   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
  FROM tre_loan_contract           tlc,
       tre_loan_contract_change    tlcc,
       tre_loan_con_repayment_plan rp,
       hls_bp_master               hbm
 WHERE tlc.loan_contract_id = tlcc.change_req_id
   AND tlc.loan_contract_id = rp.loan_contract_id
   AND rp.repayment_type = 'INTEREST'
   AND tlcc.req_status = 'APPROVING'
   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 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
  FROM tre_loan_contract           tlc,
       tre_loan_contract_change    tlcc,
       tre_loan_con_repayment_plan rp,
       hls_bp_master               hbm
 WHERE tlc.loan_contract_id = tlcc.change_req_id
   AND tlc.loan_contract_id = rp.loan_contract_id
   AND rp.repayment_type = 'PRINCIPAL'
   AND tlcc.req_status = 'APPROVING'
   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
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
  FROM tre_loan_contract           tlc,
       tre_loan_contract_change    tlcc,
       tre_loan_con_repayment_plan rp,
       hls_bp_master               hbm
 WHERE tlc.loan_contract_id = tlcc.change_req_id
   AND tlc.loan_contract_id = rp.loan_contract_id
   AND rp.repayment_type = 'CHARGE'
   AND tlcc.req_status = 'APPROVING'
   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
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
  FROM tre_loan_contract           tlc,
       tre_loan_contract_change    tlcc,
       tre_loan_con_repayment_plan rp,
       hls_bp_master               hbm
 WHERE tlc.loan_contract_id = tlcc.change_req_id
   AND tlc.loan_contract_id = rp.loan_contract_id
   AND rp.repayment_type = 'CONSULTATION'
   AND tlcc.req_status = 'APPROVING'
   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
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
  FROM tre_loan_contract           tlc,
       tre_loan_contract_change    tlcc,
       tre_loan_con_repayment_plan rp,
       hls_bp_master               hbm
 WHERE tlc.loan_contract_id = tlcc.change_req_id
   AND tlc.loan_contract_id = rp.loan_contract_id
   AND rp.repayment_type = 'ARRANGEMENT'
   AND tlcc.req_status = 'APPROVING'
   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
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
  FROM sdic_other_contract     soc1,
       sdic_other_contract_cf  socc1,
       hn_other_con_change_req r
 WHERE soc1.contract_id = socc1.contract_id
   AND soc1.data_class = 'CHANGE_REQ'
   AND r.new_contract_id = soc1.contract_id
   AND r.wfl_status = 'APPROVING'
   AND soc1.wfl_status = 'APPROVED'
   AND soc1.data_class = 'NORMAL'
   AND TO_CHAR(socc1.due_date, 'YYYYMM') = ${@calc_year} || ${@calc_month}
union
 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
   FROM tre_loan_contract            tlc,
        tre_loan_contract_change     tlcc,
        tre_loan_con_withdrawal_plan wp,
        hls_bp_master                hbm
  WHERE tlc.loan_contract_id = tlcc.change_req_id
    AND tlc.loan_contract_id = wp.loan_contract_id
    AND tlcc.req_status = 'APPROVING'
    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
    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) ||
           (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) ||
           (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) ||
           (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) ||
           (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) ||
           (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 = 3),
               0) + 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) + 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 IN (2, 12, 5)),
                                 0) inflow_amount,
           NVL(ccc.due_amount, 0) outflow_amount
      FROM con_contract            cc,
           con_contract_change_req cccr,
           con_contract_cashflow   ccc,
           hls_bp_master           hbm
     WHERE cc.contract_id = cccr.change_req_id
       AND cc.contract_id = ccc.contract_id
       AND cc.data_class = 'CHANGE_REQ'
       AND cccr.req_status = 'APPROVING'
       AND ccc.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-fields>
    <bm:data-filters>
        <bm:data-filter name="cf_item_n" expression="t1.cf_item_n is not NULL"/>
    </bm:data-filters>
</bm:model>
