prj_quotation_new_lv.sql 5.68 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
create or replace view prj_quotation_new_lv as
select "QUOTATION_ID",
       t1.document_category,
       t1.calc_session_id,
       "DOCUMENT_ID",
       t1.lease_start_date,
       t1.price_list,
       (select l.description as value_name
          from hls_price_list l
         where l.price_list = t1.price_list) price_list_n,
       t1.lease_times,
       t1.annual_pay_times,
       "DOWN_PAYMENT",
       t1.down_payment_ratio,
       t1.residual_value,
       t1.guide_price,
       t1.invoice_price,
       t1.purchase_tax,
       t1.insurance_price,
       t1.plate_price,
       "LEASE_CHARGE",
       "LEASE_CHARGE_RATIO",
       "MAIN_CONTRACT_FLAG",
       "DOCUMENT_NUMBER",
       "LEASE_ITEM_AMOUNT",
       "REVERSE_FLAG",
       "WFL_STATUS",
       t1.wfl_instance_id,
       "REVIEW_FLAG",
       "DOCUMENT_STATUS",
       "REVIEW_BY",
       "REVERSE_DATE",
       "RENT_TYPE",
       "RENT_DATE",
       "RENT_DAY",
       "INSURANCE_FEE",
       "BP_ID_INSURE",
       "INSURE_TYPE",
       "CREATED_CONTRACT_FLAG",
       "HD_USER_COL_V09",
       "HD_USER_COL_N06",
       "HD_USER_COL_N15",
       "HD_USER_COL_N14",
       "HD_USER_COL_N13",
       "DEPOSIT_DEDUCTION",
       "TOTAL_SALESTAX",
       "HD_USER_COL_N08",
       "INT_RATE_DISPLAY",
       "PAY_TYPE",
       "HD_USER_COL_N02",
       "HD_USER_COL_N07",
       "LEASE_ITEM_PRICE_AGENT",
       "GPS_INSTALL",
       "HD_USER_COL_N05",
       "GPS_FEE_PAY",
       "GPS_FEE",
       "INVESTIGATION_FEE",
       "FIRST_INSURANCE_FEE",
       "OTHER_FEE",
       "LEASE_TERM",
       "HD_USER_COL_V01",
       "HD_USER_COL_N12",
       "HD_USER_COL_N04",
       "BTB_TOTAL_FEE_PV",
       "BTB_INTEREST_MARGIN",
       "DEPOSIT_RATIO",
       "NOTARIAL_FEE_PAYABLE",
       "OTHER_FEE3",
       t1.btb_fee_after_tax,
       (select h1.code_value_name value_code
          from sys_code_values_v h1
         where h1.code = 'HLS500_ANNUAL_PAY_TIMES'
           and t1.annual_pay_times = h1.code_value) annual_pay_times_n,
       -- (select h1.code_value_name value_code
       --    from sys_code_values_v h1
       --   where h1.code = 'HLS500_ANNUAL_PAY_TIMES'
       --     and t1.btb_fee_after_tax = h1.code_value) btb_fee_after_tax_n,
       (select h1.code_value_name value_code
          from sys_code_values_v h1
         where h1.code = 'HLS500_ANNUAL_PAY_TIMES'
           and h1.code_value = t1.btb_fee_after_tax) btb_fee_after_tax_n,
       (select h1.code_value_name value_code
          from sys_code_values_v h1
         where h1.code = 'HLS_052_FINANCIAL_SCALE'
           and t1.hd_user_col_v01 = h1.code_value) hd_user_col_v01_n,
       /*  decode(t1.lease_times, 12, 12, 24, 24, 36, 36, 48, 48) lease_times_n,*/
       (select h1.code_value_name value_code
          from sys_code_values_v h1
         where h1.code = 'TIMES'
           and t1.lease_times = h1.code_value) lease_times_n,
       a.project_id,
       t1.product_id,
       (select t2.special_note
          from hls_product_plan_definition t2
         where t2.product_plan_id = t1.product_id) product_id_n,
       (select t2.if_self_definition
          from hls_product_plan_definition t2
         where t2.product_plan_id = t1.product_id) if_self_definition, --是否自定义产品
       (select decode(t2.if_self_definition, 'N', '否', 'Y', '是', '否')
          from hls_product_plan_definition t2
         where t2.product_plan_id = t1.product_id) if_self_definition_n, --是否自定义产品
       t1.base_rate t1.base_rate_type,
       (select d.description
          from fnd_base_rate_type d
         where d.base_rate_type = t1.base_rate_type) base_rate_type_n,
       t1.currency,
       t1.btb_net_finance_amount,
       t1.btb_finance_amount,
       (select hd.deposit
          from hls_fin_calculator_hd hd
         where hd.calc_session_id = t1.calc_session_id) deposit,
       (select hd.irr
          from hls_fin_calculator_hd hd
         where hd.calc_session_id = t1.calc_session_id) irr,
       t1.third_party_deposit,
       (select b.code_value_name value_name
          from sys_code_values_v b
         where b.code = 'HLS500_DEPOSIT_DEDUCTION'
           and b.code_value = t1.deposit_deduction) deposit_deduction_n,
       a.business_type,
       t1.commission_receivable,
       t1.finance_amount,
       t1.other_payment3,
       t1.btb_total_fee,
       t1.hd_user_col_v03,
       t1.hd_user_col_v05,
       (select b.code_value_name
          from sys_codes a, sys_code_values_vl b
         where b.code_id = a.code_id
           and a.code = 'MORTGAGE_TIME'
           and b.code_value = t1.hd_user_col_v05) hd_user_col_v05_n,
       nvl(t1.promise_to_pay, 0) promise_to_pay,
       decode(t1.price_list, 'DR_PRICE_25', 'Y', 'N') quote_rent,
       --add by chenlingfeng
       t1.pmt_first,
       t1.direct_lease_charge,
       t1.cus_charge_allocate_flag,
       t1.insurance_pck_flag,
       t1.product_name,
       t1.product_type,
       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,
       (select h.price_list from hls_fin_calculator_hd h where h.calc_session_id= t1.calc_session_id) cal_hd_price_list,
       t1.sub_price_list,--商品名2type
        (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.lease_mgt_fee_ratio,--代理商手续费比例
        --add  by  lijingjing
        t1.other_fee2,
        t1.total_rental,
         t1.hd_user_col_n11
       --  t1.lease_start_date


  from prj_quotation t1, prj_project a
 where t1.document_id = a.project_id
   and t1.document_category = 'PROJECT'
;