CON_CONTRACT_LV.sql 44.8 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 218 219 220 221 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 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 457 458 459 460 461 462 463 464 465 466 467 468 469 470 471 472 473 474 475 476 477 478 479 480 481 482 483 484 485 486 487 488 489 490 491 492 493 494 495 496 497 498 499 500 501 502 503 504 505 506 507 508 509 510 511 512 513 514 515 516 517 518 519 520 521 522 523 524 525 526 527 528 529 530 531 532 533 534 535 536 537 538 539 540 541 542 543 544 545 546 547 548 549 550 551 552 553 554 555 556 557 558 559 560 561 562 563 564 565 566 567 568 569 570 571 572 573 574 575 576 577 578 579 580 581 582 583 584 585 586 587 588 589 590 591 592 593 594 595 596 597 598 599 600 601 602 603 604 605 606 607 608 609 610 611 612 613 614 615 616 617 618 619 620 621 622 623 624 625 626 627 628 629 630 631 632 633 634 635 636 637 638 639 640 641 642 643 644 645 646 647 648 649 650 651 652 653 654 655 656 657 658 659 660 661 662 663 664 665 666 667 668 669 670 671 672 673 674 675 676 677 678 679 680 681 682 683 684 685 686 687 688 689 690 691 692 693 694 695 696 697 698 699 700 701 702 703 704 705 706 707 708 709 710 711 712 713 714 715 716 717 718 719 720 721 722 723 724 725 726 727 728 729 730 731 732 733 734 735 736 737 738 739 740 741 742 743 744 745 746 747 748 749 750 751 752 753 754 755 756 757 758 759 760 761 762 763 764 765 766 767 768 769 770 771 772 773 774 775 776 777 778 779 780 781 782 783 784 785 786 787 788 789 790 791 792 793 794 795 796 797 798 799 800 801 802 803 804 805 806 807 808 809 810 811 812 813 814 815 816 817 818 819 820 821 822 823 824 825 826 827 828 829 830 831 832 833 834 835 836 837 838 839 840 841 842 843 844 845 846 847 848 849 850 851 852 853 854 855 856 857 858 859 860 861 862 863 864 865 866 867 868 869 870 871 872 873 874 875 876 877 878 879 880 881 882 883 884 885 886 887 888 889 890 891 892 893 894 895 896 897 898 899 900 901 902 903 904 905 906 907 908 909 910 911 912 913 914 915 916 917 918 919 920 921 922 923 924 925 926 927 928 929 930 931 932 933 934 935 936 937 938 939 940 941 942 943 944 945 946 947 948 949 950 951 952 953 954 955 956 957 958 959 960 961 962 963 964 965 966 967 968 969 970 971 972 973 974 975 976 977 978 979 980 981 982 983 984 985 986 987 988 989 990 991 992 993 994 995 996 997 998 999 1000 1001 1002 1003 1004 1005 1006 1007 1008 1009 1010 1011 1012 1013 1014 1015 1016 1017 1018 1019 1020 1021 1022 1023 1024 1025 1026 1027 1028 1029 1030 1031 1032 1033 1034 1035 1036 1037 1038 1039 1040 1041 1042 1043 1044 1045 1046 1047 1048 1049 1050 1051 1052 1053 1054 1055 1056 1057 1058 1059 1060 1061 1062 1063 1064 1065 1066 1067 1068 1069 1070 1071 1072 1073 1074 1075 1076 1077 1078 1079 1080 1081 1082 1083 1084 1085 1086 1087 1088 1089 1090 1091 1092 1093 1094 1095 1096 1097 1098 1099 1100 1101 1102 1103
CREATE OR REPLACE VIEW CON_CONTRACT_LV AS
SELECT t1.contract_id,
          t1.calc_session_id,
          t1.contract_number,
          t1.contract_name,
          t1.business_type,
          t1.district,
          (SELECT ROUND (SUM (u.overdue_max_days) / COUNT (*), 2)
             FROM con_contract_cashflow u
            WHERE u.contract_id = t1.contract_id --AND u.write_off_flag = 'FULL'
                                                AND u.overdue_status = 'Y')
             dueday_avg,
          (SELECT SUM (ccc.overdue_amount)
             FROM con_contract_cashflow ccc
            WHERE     ccc.contract_id = t1.contract_id
                  AND ccc.write_off_flag != 'FULL'
                  AND ccc.overdue_status = 'Y'
                  AND ccc.cf_item = 1
                  AND ccc.cf_direction != 'NONCASH')
             sum_due_amount,
          (SELECT (SUM (ccc.due_amount) - SUM (ccc.received_amount))
             FROM con_contract_cashflow ccc
            WHERE     ccc.contract_id = t1.contract_id
                  AND ccc.write_off_flag != 'FULL'
                  AND ccc.cf_item = 9
                  AND ccc.cf_direction != 'NONCASH')
             sum_fx_amount,
          t1.departing_date,
          t1.departed_date,
          t1.depart_status,
          --li.item_frame_number,
          (SELECT v.code_value_name
             FROM sys_code_values_v v
            WHERE     v.code = 'DEPART_STATUS'
                  AND v.code_value = t1.depart_status)
             AS depart_status_n,
          (SELECT h1.code_value_name
             FROM sys_code_values_v h1
            WHERE     h1.code = 'PRJ500N_DISTRICT_AREAS'
                  AND t1.district = h1.code_value)
             district_n,
          (SELECT bt.description
             FROM hls_business_type bt
            WHERE bt.business_type = t1.business_type)
             AS business_type_n,
          t1.document_type,
          (SELECT p.document_type
             FROM prj_project p
            WHERE p.project_id = t1.project_id)
             AS prj_document_type,
          (SELECT p.special_permit_flag
             FROM prj_project p
            WHERE p.project_id = t1.project_id)
             AS special_permit_flag,
          (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.spv_company_id,
          (SELECT c.company_code
             FROM fnd_companies_vl c
            WHERE c.company_id = t1.spv_company_id)
             AS spv_company_code,
          (SELECT c.company_short_name
             FROM fnd_companies_vl c
            WHERE c.company_id = t1.spv_company_id)
             AS spv_company_id_n,
          t1.lease_organization,
          (SELECT o.description
             FROM hls_lease_organization o
            WHERE o.lease_organization = t1.lease_organization)
             AS lease_organization_n,
          t1.lease_channel,
          (SELECT p.lease_channel
             FROM prj_project p
            WHERE p.project_id = t1.project_id)
             AS prj_lease_channel,
          (SELECT ch.description
             FROM hls_lease_channel ch
            WHERE ch.lease_channel = t1.lease_channel)
             AS lease_channel_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.factoring_type,
          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.last_pay_date,
          --t1.lease_end_date,
          t1.lease_end_date,
          t1.lease_times,
          t1.pay_times,
          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,
          --li.deposit_deduction,
          --(SELECT v.code_value_name
          -- FROM sys_code_values_v v
          --  WHERE v.code = 'HLS500_DEPOSIT_DEDUCTION'
          --  AND v.code_value = li.deposit_deduction) AS deposit_deduction_n,
          NVL (t1.residual_value, 0) residual_value,
          t1.residual_ratio,
          t1.balloon,
          t1.balloon_ratio,
          t1.interim_rent_period,
          t1.interim_times,
          t1.interim_rental,
          t1.insurance_fee,
          t1.insurance_rate,
          t1.commission_payable,
          t1.commission_receivable,
          t1.third_party_deposit,                                ---代理商代付客户保证金
          t1.promise_to_pay,
          t1.other_fee,
          t1.other_fee3,
          t1.other_payment,
          t1.rounding_object,
          t1.rounding_method,
          (SELECT v.code_value_name
             FROM sys_code_values_v v
            WHERE     v.code = 'HLS500_CASHFLOW_ACCURATED'
                  AND v.code_value = t1.rounding_method)
             AS rounding_method_n,
          t1.int_rate_fixing_way,
          (SELECT v.code_value_name
             FROM sys_code_values_v v
            WHERE     v.code = 'TRE502_INT_RATE_FIXING_WAY'
                  AND v.code_value = t1.int_rate_fixing_way)
             AS int_rate_fixing_way_n,
          t1.int_rate_fixing_range,
          t1.int_rate_display,
          t1.base_rate_type,
          (SELECT rt.description
             FROM fnd_base_rate_type rt
            WHERE rt.base_rate_type = t1.base_rate_type)
             AS base_rate_type_n,
          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.flt_rate_profile,
          (SELECT description
             FROM con_flt_rate_profile p
            WHERE p.flt_rate_profile = t1.flt_rate_profile)
             AS flt_rate_profile_n,
          t1.flt_rate_adj_method,
          (SELECT v.code_value_name
             FROM sys_code_values_v v
            WHERE     v.code = 'HLS500_FLT_RATE_ADJ_METHOD'
                  AND v.code_value = t1.flt_rate_adj_method)
             AS flt_rate_adj_method_n,
          t1.flt_simulate_step,
          t1.flt_simulate_range,
          t1.flt_unit_adj_amt,
          t1.flt_execute_times_rule,
          (SELECT v.code_value_name
             FROM sys_code_values_v v
            WHERE     v.code = 'HLS_FLT_EXECUTE_TIMES_RULE'
                  AND v.code_value = t1.flt_execute_times_rule)
             AS flt_execute_times_rule_n,
          t1.flt_int_rate_adj_date,
          t1.flt_delay_execute_period,
          t1.flt_annual_adj_times,
          (SELECT v.code_value_name
             FROM sys_code_values_v v
            WHERE     v.code = 'HLS_FLT_ANNUAL_ADJ_TIMES'
                  AND v.code_value = t1.flt_annual_adj_times)
             AS flt_annual_adj_times_n,
          flt_next_adj_date,
          t1.int_rate_precision,
          t1.irr,
          t1.irr_after_tax,
          t1.int_rate_implicit_after_tax,
          t1.irr_reserved1,
          t1.irr_reserved2,
          t1.irr_reserved3,
          t1.pmt,
          t1.pmt_first,
          t1.npv_using_cof,
          t1.annual_mean_rate,
          t1.total_salestax,
          t1.biz_day_convention,
          (SELECT v.code_value_name
             FROM sys_code_values_v v
            WHERE     v.code = 'HLS050_BIZ_DAY_CONVENTION'
                  AND v.code_value = t1.biz_day_convention)
             AS biz_day_convention_n,
          t1.calc_with_residual_value,
          t1.exchange_rate_type,
          (SELECT v.rate_type_desc
             FROM gld_exchange_rate_type_v v
            WHERE v.rate_type_code = t1.exchange_rate_type)
             AS exchange_rate_type_n,
          t1.exchange_rate_quotation,
          t1.exchange_rate,
          t1.penalty_profile,
          t1.grace_period,
          t1.penalty_rate,
          t1.penalty_calc_base,
          t1.penalty_total_base_ratio,
          t1.credit_write_off_order,                                       --?
          t1.fin_income_recognize_method,
          t1.early_termination_profile,
          (SELECT p.description
             FROM con_contract_et_profile p
            WHERE p.et_profile = t1.early_termination_profile AND ROWNUM < 2)
             AS early_termination_profile_n,
          t1.payment_method_id,
          t1.telex_transfer_bank_id,
          t1.tt_bank_branch_name,
          t1.tt_bank_account_num,
          t1.tt_bank_account_name,
          t1.tt_remark,
          t1.direct_debit_bank_id,
          NVL2 ( (SELECT t.bank_short_name value_name
                    FROM csh_bank t
                   WHERE t.bank_id = t1.direct_debit_bank_id),
                (SELECT t.bank_short_name value_name
                   FROM csh_bank t
                  WHERE t.bank_id = t1.direct_debit_bank_id),
                (SELECT t.ebank_name
                   FROM csh_ebank t
                  WHERE t.ebank_id = t1.direct_debit_bank_id))
             direct_debit_bank_id_n,
          t1.dd_bank_branch_name,
          t1.dd_bank_account_num,
          t1.dd_bank_account_name,
          t1.dd_agreement_no,
          t1.dd_agreement_status,
          t1.dd_remark,
          t1.purchase_order_no,
          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.user_status_1,
          t1.user_status_2,
          t1.user_status_3,
          t1.print_status,
          t1.print_times,
          t1.first_print_date,
          t1.first_print_by,
          t1.delivery_status,
          t1.delivery_date,
          t1.billing_method,
          (SELECT bm.description
             FROM con_billing_method bm
            WHERE bm.billing_method = t1.billing_method)
             AS billing_method_n,
          t1.billing_status,
          t1.signing_date,
          t1.original_recall_date,
          t1.lease_card_recall_date,
          t1.early_termination_date,
          t1.termination_date,
          t1.assignment_agreement_no,
          t1.assignment_date,
          t1.btb_payment_date,
          t1.btb_int_rate,
          t1.btb_int_rate_implicit,
          t1.btb_finance_amount,
          t1.btb_net_finance_amount,
          t1.btb_total_repayment,
          t1.btb_total_interest,
          t1.btb_total_fee_pv,
          t1.btb_total_fee,
          t1.btb_finance_ratio,
          t1.btb_interest_margin,
          t1.btb_vat_interest,
          t1.btb_vat_fee,
          t1.btb_interest_after_tax,
          t1.btb_fee_after_tax,
          t1.cdd_list_id,
          t1.hd_user_col_d01,
          t1.hd_user_col_d02,
          t1.hd_user_col_d03,
          t1.hd_user_col_d04,
          t1.hd_user_col_d05,
          t1.hd_user_col_v01,
          t1.hd_user_col_v02,
          t1.hd_user_col_v03,
          t1.hd_user_col_v04,
          t1.hd_user_col_v05,
          t1.hd_user_col_v06,
          t1.hd_user_col_v07,
          t1.hd_user_col_v08,
          t1.hd_user_col_v09,
          t1.hd_user_col_v10,
          t1.hd_user_col_n01,
          t1.hd_user_col_n02,
          t1.hd_user_col_n03,
          t1.hd_user_col_n04,
          t1.hd_user_col_n05,                                   --代理商代付客户保证金比例
          t1.hd_user_col_n06,
          t1.hd_user_col_n07,
          t1.hd_user_col_n08,
          t1.hd_user_col_n09,
          t1.hd_user_col_n10,
          t1.hd_user_col_n11,
          t1.hd_user_col_n12,
          t1.hd_user_col_n13,
          t1.hd_user_col_n14,
          t1.hd_user_col_n15,
          t1.serial_number,
          overdue_status,
          overdue_max_days,
          five_class_code,
          (SELECT r.description
             FROM rsc_five_class_code r
            WHERE t1.five_class_code = r.five_class_code)
             AS five_class_code_n,
          main_business_income,
          main_business_cost,
          financing_cost,
          calc_prompt,
          calc_prompt_msg,
          lease_item_price_agent,
          legal_fee,
          version,
          version_date,
          version_display,
          version_external,
          version_reason,
          version_note,
          t1.created_by,
          t1.creation_date,
          t1.last_updated_by,
          t1.last_update_date,
          t1.search_term_1,
          t1.search_term_2,
          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_start_times,
          t1.ccr_outstanding_times,
          t1.ccr_outstanding_prin_tax_incld,
          t1.ccr_overdue_rental,
          t1.ccr_penalty,
          NVL (t1.ccr_fee, 1000) ccr_fee,
          t1.ccr_finance_amount,
          t1.ccr_pmt,
          t1.ccr_outstanding_prin_ti_total,
          t1.ccr_financing_overdue_rental,
          t1.ccr_period_int_rate_implicit,
          t1.ccr_period_lease_start_date,
          t1.ccr_added_principal,
          t1.ccr_nominal_finance_amount,
          t1.ccr_original_finance_amount,
          t1.con_maintain_flag,
          t1.license_provider_id,
          -- li.license_number,
          (SELECT m.bp_name
             FROM hls_bp_master m
            WHERE m.bp_id = t1.license_provider_id)
             license_provider_id_n,
          (SELECT a.project_number
             FROM prj_project a
            WHERE a.project_id = t1.project_id)
             project_number,
          (SELECT a.document_type
             FROM prj_project a
            WHERE a.project_id = t1.project_id)
             project_document_type,
          (SELECT ccr.document_type
             FROM con_contract_change_req ccr
            WHERE ccr.change_req_id = t1.contract_id)
             change_document_type,
          (SELECT vv.code_value_name AS value_name
             FROM sys_code_values_v vv
            WHERE     vv.code = 'HLS_052_FINANCIAL_SCALE'
                  AND vv.code_value = t1.hd_user_col_v01)
             hd_user_col_v01_n,
          (SELECT bm.bp_class
             FROM hls_bp_master bm
            WHERE bm.bp_id = t1.bp_id_tenant)
             bp_class,
          (SELECT bm.bp_class_n
             FROM hls_bp_master_lv bm
            WHERE bm.bp_id = t1.bp_id_tenant)
             bp_class_n,
          t1.cancel_reason,
          t1.bp_id_vender,
          (SELECT bp_name
             FROM hls_bp_master
            WHERE bp_id = t1.bp_id_vender)
             bp_id_vender_n,
          NULL bp_name_sec_tenant,
          t1.invoice_price,
          t1.gps_number,
          t1.ebank_account_prop,                                     -- 代扣账户属性
          (SELECT v.code_value_name AS value_name
             FROM sys_code_values_v v
            WHERE     v.code = 'EBANK_BANK_PROP'
                  AND v.code_enabled_flag = 'Y'
                  AND v.code_value_enabled_flag = 'Y'
                  AND t1.ebank_account_prop = v.code_value)
             ebank_account_prop_n,
          t1.ccr_outstanding_rental,
          t1.ccr_overdue_prin,
          t1.ccr_overdue_interest,
          (SELECT TO_CHAR (MAX (ccc.due_date), 'dd')
             FROM con_contract_cashflow ccc
            WHERE     ccc.contract_id = t1.contract_id
                  AND ccc.cf_item = '1'
                  AND ccc.cf_type = '1')
             AS pay_date,
          (SELECT MAX (ccc.due_date)
             FROM con_contract_cashflow ccc
            WHERE     ccc.contract_id = t1.contract_id
                  AND ccc.cf_item = '1'
                  AND ccc.cf_type = '1')
             AS last_pay_date,
          /*(SELECT sc.code_value_name
           FROM sys_code_values_v sc
          WHERE sc.code = 'PAY_METHOD'
            AND sc.code_value = (SELECT
                                   FROM hls_product_plan_definition hp,
                                        con_contract_lease_item     ccl
                                  WHERE hp.product_plan_id = ccl.product_plan_id
                                    AND ccl.contract_id = t1.contract_id)) pay_method, -- 方式*/

          t1.car_in_date,                                              --车辆入库日
          t1.car_type,                                                 -- 车辆类型
          (SELECT v.code_value_name value_name
             FROM sys_code_values_v v
            WHERE     v.code = 'CAR_TYPE'
                  AND v.code_enabled_flag = 'Y'
                  AND v.code_value_enabled_flag = 'Y'
                  AND t1.car_type = v.code_value)
             car_type_n,
          t1.reg_status,
          (SELECT v.code_value_name AS value_name
             FROM sys_code_values_v v
            WHERE v.code = 'REG_STATUS' AND t1.reg_status = v.code_value)
             reg_status_n,
          (SELECT DECODE (COUNT (*), 0, 'N', 'Y')
             FROM con_contract_cashflow f
            WHERE f.contract_id = t1.contract_id AND f.overdue_status = 'Y')
             overdue_status_et,
          (SELECT COUNT (*)
             FROM con_contract_cashflow h
            WHERE     h.contract_id = t1.contract_id
                  AND h.write_off_flag = 'FULL'
                  AND h.cf_item = 1)
             received_times,
          NULL version_number,
          (SELECT su.description
             FROM sys_user su
            WHERE su.user_id = t1.owner_user_id)
             owner,                                                   -- 单据所有者
          -- t1.cancel_reason, --合同取消原因
          t1.closed_date,                                            -- 合同取消日期
          t1.derate_amount_total,                                     -- 减免总金额
          t1.bp_id_agent_level1 bp_agent_id,
          (SELECT bm.bp_name
             FROM hls_bp_master bm
            WHERE bm.bp_id = t1.bp_id_agent_level1)
             bp_agent_id_n,
          t1.withhold_way,                                              --代扣方式
          (SELECT v.code_value_name AS value_name
             FROM sys_code_values_v v
            WHERE v.code = 'WITHHOLD_WAYS' AND t1.withhold_way = v.code_value)
             withhold_way_n,
          t1.ebank_id,
          (SELECT b.ebank_name
             FROM csh_ebank b
            WHERE b.ebank_id = t1.ebank_id)
             ebank_id_n,
          t1.ebank_type,
          (SELECT v.code_value_name
             FROM sys_code_values_v v
            WHERE     v.code = 'EBANK_TYPE'
                  AND v.code_value = t1.ebank_type
                  AND v.code_enabled_flag = 'Y'
                  AND v.code_value_enabled_flag = 'Y')
             ebank_type_n,
          /*(select v.code_value_name
           from sys_code_values_v v
          where v.code = 'ACCOUNT_PROP'
            and v.code_value = t1.ebank_account_prop
            and v.code_enabled_flag = 'Y'
            and v.code_value_enabled_flag = 'Y') ebank_account_prop_n,*/
          t1.ebank_feeno,
          t1.ebank_feeno ebank_feeno_n,
          t1.ebank_account_name,
          t1.ebank_account_num,
          t1.ebank_flag,
          t1.ebank_province,
          t1.emer_contacts,                                            --紧急联系人
          t1.emer_phone,                                              --紧急联系电话
          (SELECT fp.province_code
             FROM fnd_province fp
            WHERE fp.province_id = t1.ebank_province)
             ebank_province_d,
          (SELECT fp.description
             FROM fnd_province fp
            WHERE fp.province_id = t1.ebank_province)
             ebank_province_n,
          t1.ebank_city,
          (SELECT fc.description
             FROM fnd_city fc
            WHERE fc.city_id = t1.ebank_city)
             ebank_city_n,
          (SELECT fc.city_code
             FROM fnd_city fc
            WHERE fc.city_id = t1.ebank_city)
             ebank_city_d,
          (SELECT pp.approved_date
             FROM prj_project pp
            WHERE pp.project_id = t1.project_id)
             approved_date,                                        -- 项目创建合同时间
          (SELECT hp.product_name_write
             FROM con_contract_lease_item ccl, hls_product_plan_definition hp
            WHERE     ccl.contract_id = t1.contract_id
                  AND ccl.product_plan_id = hp.product_plan_id)
             product_name_write,                                        --产品方案
          (SELECT ccb.bp_name
             FROM con_contract_bp ccb
            WHERE     t1.contract_id = ccb.contract_id
                  AND ccb.bp_category = 'TENANT')
             bp_name,
          (SELECT a.bp_name_sp
             FROM con_contract_bp a
            WHERE a.contract_id = t1.contract_id AND a.bp_category = 'TENANT')
             bp_name_sp,
          --(select pi.contact_person from prj_project_bp_contact_info pi,prj_project_bp ppb where pi.prj_bp_id = ppb.prj_bp_id and ppb.project_id = t1.project_id)contact_person,
          --(select pi.contact_person_1 from prj_project_bp_contact_info pi,prj_project_bp ppb where pi.prj_bp_id = ppb.prj_bp_id and ppb.project_id = t1.project_id)contact_person_1,
          NULL due_times,
          NULL his_due_times,
          NULL sy_amount,
          --end
          t1.note,
          (SELECT ccb.record_id
             FROM con_contract_bp ccb
            WHERE     t1.contract_id = ccb.contract_id
                  AND ccb.bp_category = 'TENANT')
             content_bp_pk,
          NULL overdue_status_type,
          (SELECT DISTINCT tt.code_value_name
             FROM con_collection t, sys_code_values_v tt
            WHERE     t.contract_id = t1.contract_id
                  AND tt.code_value = t.overdue_status_type
                  AND tt.code = 'OVERDUE_STATUS_TYPE'
                  AND ROWNUM = 1)
             overdue_status_type_n,
          -- li.gps_amount,
          t1.col_law_reason,
          -- li.gps_flag,
          --(select c.code_value_name  from sys_code_values_v c
          -- where c.code = 'YES_NO' and c.code_value = li.gps_flag) gps_flag_n,
          (SELECT NVL (lm.id_card_no, lm.business_license_num)
             FROM hls_bp_master lm
            WHERE lm.bp_id = t1.bp_id_tenant)
             lessee_id,
          -- (select hp.product_name_write from hls_product_plan_definition hp where hp.product_plan_id = li.product_plan_id)PRODUCT_PLAN_ID_n, -- 应ZW要求修改产品名称
          -- li.product_plan_id,
          -- li.DISCOUNT_RATE,
          -- li.DISCOUNT,
          -- li.Gps_Install_Flag,
          --(select scv.code_value_name from sys_code_values_v scv where scv.code = 'YES_NO' and scv.code_value = li.gps_install_flag) gps_install_flag_n,
          -- li.Visit_Flag,
          --(select scv.code_value_name from sys_code_values_v scv where scv.code = 'YES_NO' and scv.code_value = li.VISIT_FLAG) VISIT_FLAG_n,
          --li.Purchase_Tax_Flag,
          -- (SELECT v.code_value_name AS value_name
          -- FROM sys_code_values_v v
          -- WHERE v.code = 'YES_NO'
          -- AND v.code_value = li.PURCHASE_TAX_FLAG) PURCHASE_TAX_FLAG_N,
          -- li.Purchase_Tax,
          --li.License_Fee_Flag,
          --(select scv.code_value_name from sys_code_values_v scv where scv.code = 'YES_NO' and scv.code_value = li.LICENSE_FEE_FLAG) LICENSE_FEE_FLAG_n,
          -- li.Plate_Price,
          --li.Insurance_Flag,
          --(SELECT v.code_value_name AS value_name
          -- FROM sys_code_values_v v
          -- WHERE v.code = 'YES_NO'
          -- AND v.code_value = li.INSURANCE_FLAG) INSURANCE_FLAG_N,
          -- li.Insurance_Amount,
          -- li.Insurance_Purchase,
          --(select scv.code_value_name
          -- from sys_code_values_v scv where scv.code = 'INSURANCE_PURCHASE' and scv.code_value = li.insurance_purchase) insurance_purchase_n,
          -- li.max_discount,
          t1.rebate,
          t1.wfl_instance_id,
          TO_CHAR ( (SELECT zi.creation_date
                       FROM zj_wfl_workflow_instance zi
                      WHERE zi.instance_id = t1.wfl_instance_id),
                   'yyyy-mm-dd hh24:mi:ss')
             submit_date_detail,
          (SELECT pp.fin_manager
             FROM prj_project pp
            WHERE pp.project_id = t1.project_id)
             fin_manager,
          (SELECT ee.name
             FROM exp_employees ee
            WHERE ee.employee_id = (SELECT pp.fin_manager
                                      FROM prj_project pp
                                     WHERE pp.project_id = t1.project_id))
             fin_manager_n,
          NVL (t1.archive_status, '10') archive_status,
          NVL (
             (SELECT v.code_value_name AS value_name
                FROM sys_code_values_v v
               WHERE     v.code = 'ARCHIVE_STATUS'
                     AND v.code_value = t1.archive_status),
             '未归档')
             archive_status_n,
          t1.transfer_flag,
          (SELECT v.code_value_name AS value_name
             FROM sys_code_values_v v
            WHERE v.code = 'YES_NO' AND v.code_value = t1.transfer_flag)
             transfer_flag_n,
          t1.notarial_fee_payable,
          (SELECT p1.product_id
             FROM prj_quotation p1
            WHERE     p1.document_category = 'PROJECT'
                  AND p1.document_id = t1.project_id)
             product_id,
          (SELECT SUM (cf.principal) - SUM (NVL (cf.received_principal, 0))
             FROM con_contract_cashflow cf
            WHERE     cf.contract_id = t1.contract_id
                  AND cf.cf_item = 1
                  AND cf.cf_direction = 'INFLOW')
             outstanding_principal,
          NVL (t1.et_ins_derate_amount, 0) et_ins_derate_amount,
          NVL (t1.et_amount, 0) et_amount,
          NVL (t1.et_total_amt, 0) et_total_amt,
          t1.et_date,
          t1.bisiness_division,
          --add by chenlingfeng
          t1.product_name,
          t1.product_type,
          t1.approve_note,
          t1.direct_lease_charge,
          t1.cus_charge_allocate_flag,
          t1.insurance_pck_flag,
          t1.doc_received_date,
          t1.sign_method,
          (SELECT v.code_value_name
             FROM sys_code_values_v v
            WHERE     v.code = 'SIGNING_MODE_DS'
                  AND v.code_value = t1.sign_method)
             sign_method_n,
          (SELECT hbm.large_area
             FROM hls_bp_master hbm
            WHERE hbm.bp_id = t1.bp_id_tenant)
             large_area,
          t1.submit_date,
          (SELECT pp.submit_date
             FROM prj_project pp
            WHERE pp.project_id = t1.project_id)
             prj_submit_date,
          t1.sales_method,
          (SELECT v.code_value_name AS value_name
             FROM sys_code_values_v v
            WHERE     v.code = 'DS_SALES_METHOD'
                  AND t1.sales_method = v.code_value)
             sales_method_n,
          t1.sales_method_note,
          t1.nature_of_work,
          t1.engineering_area,
          t1.actual_user,
          t1.outsider_work_flag,
          t1.work_income,
          t1.work_term,
          t1.work_settlement_cycle,
          (SELECT v.code_value_name AS value_name
             FROM sys_code_values_v v
            WHERE     v.code = 'WORK_SETTLEMENT_CYCEL'
                  AND t1.work_settlement_method = v.code_value)
             work_settlement_cycle_n,
          t1.work_settlement_method,
          (SELECT v.code_value_name AS value_name
             FROM sys_code_values_v v
            WHERE     v.code = 'WORK_SETTLEMENT_METHOD'
                  AND t1.work_settlement_method = v.code_value)
             work_settlement_method_n,
          t1.signing_people,
          t1.signing_note,
          (SELECT v.code_value_name AS value_name
             FROM sys_code_values_v v
            WHERE     v.code = 'EXECUTION_STATUS_DS'
                  AND t1.execution_status = v.code_value)
             execution_status_n,
          t1.execution_status,
          t1.insurance_method,
          (SELECT v.code_value_name
             FROM sys_code_values_v v
            WHERE     v.code = 'INSURANCE_METHOD'
                  AND v.code_value = t1.insurance_method)
             insurance_method_n,
          NVL (t1.lease_execution_date, TRUNC (SYSDATE)) lease_execution_date,
          --end
          t1.product_code,
          (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 t1.description
             FROM hls_document_type t1
            WHERE t1.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,
          (SELECT cccc.req_date
             FROM con_contract_change_req cccc
            WHERE cccc.change_req_id = t1.contract_id)
             req_status,
          (SELECT cccc.ref_v01
             FROM con_contract_change_req cccc
            WHERE cccc.change_req_id = t1.contract_id)
             ref_v01,                                                    --end
          t1.con_solution,
          (SELECT v.code_value_name
             FROM sys_code_values_v v
            WHERE v.code = 'CON_SOLUTION' AND v.code_value = t1.con_solution)
             con_solution_n,
          t1.terms_of_payment,
          (SELECT v.code_value_name
             FROM sys_code_values_v v
            WHERE     v.code = 'TERMS_OF_PAYMENT'
                  AND v.code_value = t1.terms_of_payment)
             terms_of_payment_n,                                   -------支付方式
          t1.arbitration,
          t1.warranty_standared,
          DECODE (t1.division,
                  '00', '工程机械(中国)有限公司',
                  '10', '工程机械(中国)有限公司',
                  '01', '工程机械(山东)有限公司',
                  '')
             warranty_standared_n,
          t1.warranty_year,
          t1.warranty_hour,
          t1.sub_price_list,
          (SELECT v.code_value_name
             FROM sys_code_values_v v
            WHERE     v.code = 'PRICE_LIST_NAME'
                  AND v.code_value = t1.sub_price_list)
             sub_price_list_n,
          t1.project_plan,
          t1.unit_price,
          t1.quantities,
          t1.out_engineer_or_not,
          (SELECT v.code_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.out_engineer_or_not)
             out_engineer_or_not_n,
          t1.signing_city,
          t1.signing_district,
          (SELECT c.description
             FROM fnd_city c
            WHERE c.city_id = t1.signing_city)
             AS signing_city_n,
          (SELECT d.description
             FROM fnd_district d
            WHERE d.district_id = t1.signing_district)
             AS signing_district_n,
          --add by chenlingfeng
          t1.con_deposit_by_agent,
          NVL (t1.billing_way, 20) billing_way,
          (SELECT v.code_value_name
             FROM sys_code_values_v v
            WHERE     v.code = 'DS_INVOICE_CATEGORY'
                  AND v.code_value = NVL (t1.billing_way, 20))
             billing_way_n,
          NVL (t1.billing_frequency, 10) billing_frequency,
          (SELECT v.code_value_name
             FROM sys_code_values_v v
            WHERE     v.code = 'DS_INVOICE_FREQUENCY'
                  AND v.code_value = NVL (t1.billing_frequency, 10))
             billing_frequency_n,
          t1.first_delay_flag,
          --add by liuhaojie
          t1.information_completed,
          (SELECT v.code_value_name
             FROM sys_code_values_v v
            WHERE     v.code = 'YES_NO'
                  AND v.code_value = t1.information_completed)
             information_completed_n,
          --end
          --add by liyuan
          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,                                      --手续费减免说明
          t1.UPLOAD_DATE,
          --end
          t1.lease_term_month,                                       --租赁期限(月)
          t1.content_type,
          (SELECT v.code_value_name
             FROM sys_code_values_v v
            WHERE     v.code = 'CON1010_CONTENT_TYPE'
                  AND v.code_value = t1.content_type)
             content_type_n,
          (SELECT item_type
             FROM con_contract_lease_item
            WHERE contract_id = t1.contract_id AND equipment_type = 'MAIN')
             item_type,
          (SELECT v.code_value_name
             FROM con_contract_lease_item c, sys_code_values_v v
            WHERE     c.item_type = v.code_value
                  AND v.code = 'DS_ITEM_TYPE'
                  AND contract_id = t1.contract_id
                  AND equipment_type = 'MAIN')
             item_type_n,
          t1.area_distinguish,
          t1.direct_lease_charge_flag,
          t1.loss_sharing_mark,
          t1.loss_sharing_reason,
          (SELECT code_value_name
             FROM sys_code_values_v v
            WHERE     code = 'LOSS_SHARE_REASON'
                  AND v.code_value = t1.loss_sharing_reason)
             loss_sharing_reason_n,
          t1.loss_sharing_notes,
          (SELECT LISTAGG (ct.description, '、')
                     WITHIN GROUP (ORDER BY ct.cf_item)
             FROM hls_cashflow_item ct
            WHERE ct.cf_item IN (SELECT DISTINCT (cc.cf_item)
                                   FROM con_contract_cashflow cc
                                  WHERE     cc.contract_id = t1.contract_id
                                        AND cc.times = 0
                                        AND cc.write_off_flag <> 'FULL'
                                        AND cc.cf_direction = 'INFLOW'))
             cf_count,
          t1.sub_price_list_name,                                      --商品名称2
          NVL (t1.simple_contract, 'N') simple_contract,
          t1.sub_price_list_code,
          (SELECT m.extra_nam
             FROM hls_bp_master m
            WHERE m.bp_id = t1.bp_id_agent_level1)
             agent_extra_nam,
          DECODE ( (SELECT bp.direct_sales_agent_flag
                      FROM hls_bp_master bp
                     WHERE bp.bp_id = t1.bp_id_agent_level1),
                  'Y', t1.lease_mgt_fee,
                  'N', 0,
                  0)
             direct_agent_fee,                                     ---直营代理商手续费
          DECODE ( (SELECT bp.direct_sales_agent_flag
                      FROM hls_bp_master bp
                     WHERE bp.bp_id = t1.bp_id_agent_level1),
                  'N', t1.lease_mgt_fee,
                  NULL, t1.lease_mgt_fee,
                  0)
             agent_fee,                                            --普通的代理商手续费
          t1.contract_sign_date,
          t1.CONTRACT_INCEPTION_DATE,                                 ---合同交付日
          (SELECT m.customer_id
             FROM hls_bp_master m
            WHERE m.bp_id = t1.bp_id_tenant)
             tenant_customer_id,
          (SELECT m.customer_id
             FROM hls_bp_master m
            WHERE m.bp_id = t1.bp_id_agent_level1)
             agent_customer_id,
          t1.special_policy,                                            --特殊政策
          t1.AS_ITEM_SOURCE,                                          --AS物件来源
          t1.DOWN_PAYMENT_STATUS,
          (SELECT v.code_value_name
             FROM sys_code_values_v v
            WHERE     v.code = 'DOWN_PAYMENT_STATUS'
                  AND v.code_value = t1.DOWN_PAYMENT_STATUS)
             DOWN_PAYMENT_STATUS_n,
          (SELECT NVL (SUM (NVL (ccc.received_amount, 0)), 0)
             FROM con_contract_cashflow ccc
            WHERE     ccc.contract_id = t1.contract_id
                  AND ccc.cf_direction = 'INFLOW'
                  AND ccc.cf_status = 'RELEASE'
                  and  ccc.cf_item not in (301,508))
             total_received_amount,
             t1.joint_lease_code,
             t1.joint_lease_cus_code,
             (select bp.bp_name from
             hls_bp_master bp where bp.bp_id= t1.joint_lease_cus_code)joint_lease_cus_code_n,
             t1.joint_lease_code joint_lease_code_n,
             t1.data_miss_flag,
             t1.data_miss_reason,
             (select sum(cwo.write_off_due_amount) from csh_write_off
             cwo where cwo.contract_id = t1.contract_id and cwo.reversed_flag = 'N'
             and cwo.cf_item = 2) write_off_cf_2,
             (select sum(cwo.write_off_due_amount) from csh_write_off
             cwo where cwo.contract_id = t1.contract_id and cwo.reversed_flag = 'N'
             and cwo.cf_item = 51) write_off_cf_51,
             (select sum(cwo.write_off_due_amount) from csh_write_off
             cwo where cwo.contract_id = t1.contract_id and cwo.reversed_flag = 'N'
             and cwo.cf_item = 508) write_off_cf_508,
              (select sum(cwo.write_off_due_amount) from csh_write_off
             cwo where cwo.contract_id = t1.contract_id and cwo.reversed_flag = 'N'
             and cwo.cf_item = 3) write_off_cf_3,
             (select sum(cwo.write_off_due_amount) from csh_write_off
             cwo where cwo.contract_id = t1.contract_id and cwo.reversed_flag = 'N'
             and cwo.cf_item = 301) write_off_cf_301,
            /* (select sum(cwo.write_off_due_amount) from csh_write_off
             cwo where cwo.contract_id = t1.contract_id and cwo.reversed_flag = 'N'
             and cwo.cf_item = 1 and cwo.times
             =(select min(ccc.times)
                          from con_contract_cashflow ccc
                         where ccc.contract_id = t1.contract_id
                           and ccc.cf_status = 'RELEASE'
                           and ccc.cf_direction = 'INFLOW'
                           and ccc.cf_item = 1)) write_off_first_cf_1*/
                (select sum(ccc.due_amount) from con_contract_cashflow
             ccc where ccc.contract_id = t1.contract_id
             AND ccc.cf_direction = 'INFLOW'
              AND ccc.cf_status = 'RELEASE'
             and  ccc.times=1 and ccc.cf_item in(1,102,103,104)
             ) write_off_first_cf_1,--首期租金
             --回购字段 start
             t1.REPURCHASE_CONTRACT_NUMBER,
             t1.REPURCHASE_TYPES,
             (SELECT v.code_value_name
             FROM sys_code_values_v v
            WHERE     v.code = 'REPURCHASE_TYPES'
                  AND v.code_value = t1.REPURCHASE_TYPES) AS REPURCHASE_TYPES_n,
                  t1.OBJECT_HANDINGO,
                  t1.RECOVERY_MATHOD_GOL,
                  t1.SUBJECT_MATTER_RESCISSIO,
                  t1.last_rent_due_date,
                  t1.repurchase_date,
                  t1.paymengt_deadlinedate,
                  t1.depoist_remaining_amount,
                  t1.repurchase_tatal_amount,
                  t1.repurchase_count_amount,
                  t1.repurchase_service_charges,
                  t1.fund_possession_cost,
                  t1.repurchase_interest_rate,
                  t1.depoist_offset_flag,
                  (SELECT v.code_value_name
             FROM sys_code_values_v v
            WHERE     v.code = 'YES_OR_NO'
                  AND v.code_value = t1.depoist_offset_flag) AS depoist_offset_flag_n,
                  t1.payment_frequency,
                  t1.payment_period
                  --回购字段 end
     FROM con_contract t1
     WHERE t1.data_class='NORMAL'
;