CON_CHANGE_CONTRACT_LV.sql 12 KB
Newer Older
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217
CREATE OR REPLACE VIEW CON_CHANGE_CONTRACT_LV AS
SELECT t1.contract_id,
       t1.calc_session_id,
       (SELECT t.calc_successful FROM hls_fin_calculator_hd t WHERE t.calc_session_id = t1.calc_session_id)calc_successful,
       t1.contract_number,
       t1.contract_name,
       t1.document_type,
       (SELECT dt.description
          FROM hls_document_type dt
         WHERE dt.document_type = t1.document_type) AS document_type_n,
       t1.document_category,
       (SELECT dc.description
          FROM hls_document_category dc
         WHERE dc.document_category = t1.document_category) AS document_category_n,
       t1.project_id,
       (SELECT p.project_number
          FROM prj_project p
         WHERE t1.project_id = p.project_id) AS project_id_c,
       (SELECT p.project_name
          FROM prj_project p
         WHERE t1.project_id = p.project_id) AS project_id_n,
       t1.company_id,
       (SELECT c.company_short_name
          FROM fnd_companies_vl c
         WHERE c.company_id = t1.company_id) AS company_id_n,
       t1.division,
       (SELECT d.description
          FROM hls_division d
         WHERE d.division = t1.division) AS division_n,
       t1.bp_id_tenant,
       (SELECT m.bp_name
          FROM hls_bp_master m
         WHERE m.bp_id = t1.bp_id_tenant) AS bp_id_tenant_n,
       (SELECT ma.bp_name
          FROM hls_bp_master ma
         WHERE ma.bp_id = t1.bp_id_tenant) bp_id_tenant_name,
       t1.bp_id_agent_level1,
       (SELECT m.bp_name
          FROM hls_bp_master m
         WHERE m.bp_id = t1.bp_id_agent_level1) AS bp_id_agent_level1_n,
       t1.bp_id_agent_level2,
       (SELECT m.bp_code
          FROM hls_bp_master m
         WHERE m.bp_id = t1.bp_id_agent_level2) AS bp_id_agent_level2_n,
       t1.bp_id_agent_level3,
       (SELECT m.bp_code
          FROM hls_bp_master m
         WHERE m.bp_id = t1.bp_id_agent_level3) AS bp_id_agent_level3_n,
       t1.owner_user_id,
       (SELECT a.description
          FROM sys_user a
         WHERE a.user_id = t1.owner_user_id) owner_user_id_n,
       t1.employee_id,
       (SELECT e.name
          FROM exp_employees e
         WHERE e.employee_id = t1.employee_id) AS employee_id_n,
       t1.unit_id,
       (SELECT h.bp_name FROM hls_bp_master h WHERE h.bp_id = t1.unit_id) AS unit_id_n,
       t1.employee_id_of_manager,
       (SELECT e.name
          FROM exp_employees e
         WHERE e.employee_id = t1.employee_id_of_manager) AS employee_id_of_manager_n,
       t1.description,
       t1.price_list,
       (SELECT l.description
          FROM hls_price_list l
         WHERE l.price_list = t1.price_list) AS price_list_n,
       t1.calc_method,
       t1.inception_of_lease,
       t1.lease_start_date,
       t1.first_pay_date,
       t1.lease_end_date,
       t1.lease_times,
       t1.pay_times,
       t1.int_rate_display,
       t1.annual_pay_times,
       (SELECT v.code_value_name
          FROM sys_code_values_v v
         WHERE v.code = 'HLS500_ANNUAL_PAY_TIMES'
           AND v.code_value = t1.annual_pay_times) AS annual_pay_times_n,
       t1.lease_term,
       t1.pay_type,
       (SELECT v.code_value_name
          FROM sys_code_values_v v
         WHERE v.code = 'HLS500_PAY_TYPE'
           AND v.code_value = t1.pay_type) AS pay_type_n,
       t1.currency,
       (SELECT g.currency_name
          FROM gld_currency_vl g
         WHERE g.currency_code = t1.currency) AS currency_n,
       t1.currency_precision,
       t1.machinery_amount,
       t1.parts_amount,
       t1.lease_item_amount,
       t1.lease_item_cost,
       t1.down_payment,
       t1.down_payment_ratio,
       t1.finance_amount,
       t1.net_finance_amount,
       t1.total_interest,
       t1.total_rental,
       t1.total_fee,
       t1.contract_amount,
       t1.tax_type_id,
       (SELECT t.description
          FROM fnd_tax_type_codes t
         WHERE t.tax_type_id = t1.tax_type_id) AS tax_type_id_n,
       t1.vat_flag,
       t1.vat_rate,
       t1.vat_input,
       t1.vat_total_interest,
       t1.vat_finance_amount,
       t1.vat_total_rental,
       t1.vat_total_fee,
       t1.net_total_interest,
       t1.net_total_rental,
       t1.net_total_fee,
       t1.net_lease_item_amount,
       nvl(t1.lease_charge, 0) lease_charge,
       t1.lease_charge_ratio,
       t1.lease_mgt_fee,
       t1.lease_mgt_fee_ratio,
       t1.lease_mgt_fee_rule,
       (SELECT v.code_value_name
          FROM sys_code_values_v v
         WHERE v.code = 'HLS500_MGT_FEE_RULE'
           AND v.code_value = t1.lease_mgt_fee_rule) AS lease_mgt_fee_rule_n,
       t1.deposit,
       t1.deposit_ratio,
       nvl(t1.residual_value, 0) residual_value,
       t1.other_fee,
       t1.base_rate,
       t1.int_rate,
       t1.int_rate_implicit,
       t1.int_rate_type,
       (SELECT v.code_value_name
          FROM sys_code_values_v v
         WHERE v.code = 'CON500_INT_RATE_TYPE'
           AND v.code_value = t1.int_rate_type) AS int_rate_type_n,
       t1.contract_status,
       (SELECT v.code_value_name
          FROM sys_code_values_v v
         WHERE v.code = 'CON500_CONTRACT_STATUS'
           AND v.code_value = t1.contract_status) AS contract_status_n,
       t1.created_by,
       t1.creation_date,
       t1.last_updated_by,
       t1.last_update_date,
       t1.data_class,
       (SELECT sc.code_value_name
          FROM sys_code_values_v sc
         WHERE sc.code = 'HLS_DATA_CLASS'
           AND sc.code_value = t1.data_class) data_class_n,
       t1.ccr_penalty,
       nvl(t1.ccr_fee,
           decode((SELECT ccc1.document_type
                    FROM con_contract_change_req ccc1
                   WHERE ccc1.change_req_id = t1.contract_id),
                  'LEASSE_CHAG',
                  '1000',
                  'LEASEHOLD_CHAG',
                  '1000',
                  'CONEXTHAG',
                  '1000',
                  'DEBT_CHAG',
                  '2000',
                  'PART_CHAG',
                  '500',
                  'PAY_CHAG',
                  '0')) ccr_fee,
       --modify by liyuan begin
       (SELECT ccb.score_level
          FROM con_contract_bp ccb
         WHERE t1.contract_id = ccb.contract_id
           AND ccb.bp_category = 'TENANT') score_level,
       (SELECT ccc1.document_type
          FROM con_contract_change_req ccc1
         WHERE ccc1.change_req_id = t1.contract_id) ccr_document_type,
       (SELECT ht.description
          FROM hls_document_type ht
         WHERE ht.document_type =
               (SELECT ccc1.document_type
                  FROM con_contract_change_req ccc1
                 WHERE ccc1.change_req_id = t1.contract_id)) AS ccr_document_type_n, --变更类型
       /*decode((SELECT ccc1.document_type
         FROM con_contract_change_req ccc1
        WHERE ccc1.change_req_id = t1.contract_id),
       'LEASSE_CHAG',
       '1',
       'LEASEHOLD_CHAG',
       '2',
       'CONEXTHAG',
       '3',
       'DEBT_CHAG',
       '4',
       'PART_CHAG',
       '5',
       'PAY_CHAG',
       '6') times,*/
       (SELECT to_date(to_char(cccc.req_date,'yyyy-mm-dd'),'yyyy-mm-dd')
          FROM con_contract_change_req cccc
         WHERE cccc.change_req_id = t1.contract_id) req_date, --变更申请日期
       (SELECT cccc.ref_v01
          FROM con_contract_change_req cccc
         WHERE cccc.change_req_id = t1.contract_id) ref_v01, --变更人
       (SELECT cli.pattern
          FROM con_contract_lease_item cli
         WHERE cli.contract_id = t1.contract_id
           AND cli.equipment_type = 'MAIN') lease_item_id_n, --机型
       (SELECT cli.lease_item_id
          FROM con_contract_lease_item cli
         WHERE cli.contract_id = t1.contract_id
           AND cli.equipment_type = 'MAIN') lease_item_id,
       (SELECT cli.machine_number
          FROM con_contract_lease_item cli
         WHERE cli.contract_id = t1.contract_id
           AND cli.equipment_type = 'MAIN') machine_number, --机号 --end
218 219 220 221
       t1.ccr_lease_rental,
       t1.ccr_lease_interest,
       t1.ccr_lease_principal,
       t1.ccr_lease_penalty,
222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294
       t1.ccr_reason,
       t1.ccr_opinion,
       nvl(t1.hd_user_col_v04, 'DELAY') hd_user_col_v04, --冬雨季延期方式
       t1.ccr_start_times,
       t1.interim_times, --延期期数
       t1.ccr_outstanding_prin_tax_incld, --剩余本金
       t1.ccr_finance_amount,
       t1.interim_rental,
       t1.ccr_outstanding_times,
       t1.ccr_dept,
       t1.btb_payment_date, --变更基准日期
       t1.btb_finance_amount,
       t1.ccr_overdue_rental, --变更逾期租金
       t1.HD_USER_COL_V06, --罚息是否参与债权再调整
       (select v.code_value_name as value_name
          from sys_code_values_v v
         where v.code_enabled_flag = 'Y'
           and v.code_value_enabled_flag = 'Y'
           and v.code = 'YES_NO'
           and v.code_value = t1.HD_USER_COL_V06) HD_USER_COL_V06_n,
       t1.ccr_overdue_prin, --逾期本金
       t1.CCR_OVERDUE_INTEREST, --变更逾期利息
       t1.CCR_OUTSTANDING_PRIN_TI_TOTAL, --未到期本金
       t1. CCR_DUE_AMOUNT, --变更罚息总金额
       t1. CCR_DUE_RATIO, --变更收取比例
       t1.CCR_DUE_CHARGE, --变更收取罚息金额
       t1.BTB_INTEREST_MARGIN, --债权再调整变更不收本金期数
       t1.bp_character, --承租人性格
       (SELECT v.code_value_name
          FROM sys_code_values_v v
         WHERE v.code_value = t1.bp_character
         and v.code='BP_CHARACTER') bp_character_n,
       t1.bp_engineer_source, --工程来源
       (SELECT v.code_value_name
          FROM sys_code_values_v v
         WHERE v.code_value = t1.bp_engineer_source
         and v.code='BP_ENGINEER_SOURCE') bp_engineer_source_n,
       t1.bp_credit_conscious, --信用意识
       (SELECT v.code_value_name
          FROM sys_code_values_v v
         WHERE v.code_value = t1.bp_credit_conscious
         and v.code='BP_CREDIT_CONSCIOUS') bp_credit_conscious_n,
       t1.bp_income_situation, --资产与收入情况
       t1.bp_engineer_discrip, --工程情况描述
       t1.bp_risk, --有何风险
       t1.bp_evaluate, --整体风险
       t1.fee_waiver, --是否手续费减免
       t1.fee_waiver_reason,--手续费减免说明
       --租金計劃變更 start
           (SELECT c.lease_times FROM con_contract c,con_contract_change_req cr WHERE c.contract_id = cr.contract_id
           AND cr.change_req_id = t1.contract_id)TIMES_S,
           (SELECT c.irr FROM con_contract c ,con_contract_change_req cr WHERE c.contract_id = cr.contract_id
           AND cr.change_req_id = t1.contract_id)IRR_S,
           (SELECT c.int_rate_display FROM con_contract c,con_contract_change_req cr WHERE c.contract_id = cr.contract_id
           AND cr.change_req_id = t1.contract_id)RATE_S,
           t1.lease_times TIMES_C,
           t1.irr IRR_C,
           t1.int_rate_display RATE_C,
           --租金計劃變更 end
           --回款賬戶變更 start
           (SELECT v.code_value_name FROM con_contract c,con_contract_change_req cr,sys_code_values_v v WHERE c.contract_id = cr.contract_id
           AND v.code_value = c.pay_method
           AND cr.change_req_id = t1.contract_id)PAYMENT_METHOD_ID_S,	--原支付方式
            (SELECT c.BANK_BRANCH_NAME FROM con_contract c,con_contract_change_req cr WHERE c.contract_id = cr.contract_id
           AND cr.change_req_id = t1.contract_id)BANK_FULL_NAME_S,	--原扣款银行
           (SELECT c.BANK_ACCOUNT_NAME FROM con_contract c,con_contract_change_req cr WHERE c.contract_id = cr.contract_id
           AND cr.change_req_id = t1.contract_id)BANK_ACCOUNT_NAME_S,	--原扣款账户名
           (SELECT c.BANK_BRANCH_NUM FROM con_contract c,con_contract_change_req cr WHERE c.contract_id = cr.contract_id
           AND cr.change_req_id = t1.contract_id)BANK_ACCOUNT_NUM_S,	--原扣款账号
           t1.pay_method,
           (SELECT v.code_value_name
          FROM sys_code_values_v v
         WHERE v.code_value = t1.pay_method
295 296 297
         and v.code='PAY_METHODS') PAY_METHOD_N,	--现支付方式
           t1.BANK_BRANCH_NAME ,	--现扣款银行
           t1.BANK_ACCOUNT_NAME ,	--现扣款账户名
298 299
           t1.BANK_BRANCH_NUM,	--现扣款账号
           t1.ccr_lease_date
300 301 302
           --回款賬戶變更 end
  FROM con_contract t1
;