PRJ_PROJECT_LEASE_ITEM_HEAD_LV.sql 3.18 KB
Newer Older
lijingjing's avatar
lijingjing committed
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
CREATE OR REPLACE VIEW PRJ_PROJECT_LEASE_ITEM_HEAD_LV AS
select t.vehicle_class,
       t.Lease_Item_Seq,
       t.Brand_Id,
       t.Series_Id,
       t.Truck_Class,
       t.Price,
       t.Quantity,
       t.Item_Engine_Number,
       t.Horsepower,
       t.NOTICE_NUMBER,
       t.Gps_Flag,
       (SELECT a.description value_name
          FROM hls_car_brands_vl a
         WHERE t.brand_id = a.brand_id
           AND a.enabled_flag = 'Y') brand_id_n,
       (SELECT a.description
          FROM hls_car_series_vl a
         WHERE a.series_id = t.series_id) series_id_n,
       (select code_value_name
          from sys_code_values_v scv
         where scv.code = 'TRUCK_CLASS'
           and scv.code_value = t.Truck_Class) Truck_Class_n,
       (t.price * t.quantity) TOTAL_AMOUNT,
       t.project_id,
       t.project_lease_item_id,
       t.lease_item_id,
       (select i.full_name from hls_lease_item i where i.lease_item_id = t.lease_item_id) lease_item_id_n,
        t.Vehicles_Number,
       (select COUNT(1)
          from CON_CONTRACT_LEASE_ITEM CI
         where CI.PROJECT_LEASE_ITEM_ID = T.PROJECT_LEASE_ITEM_ID) CONFIRM_NUMBER,
        t.insurance_price,
        t.gps_amount,
				t.Purchase_Tax,
        t.model_id,
        (SELECT a.description
          FROM hls_car_model_vl a
         WHERE a.model_id = t.model_id) model_id_n,
        t.guide_price,
        t.VEHICLE_TYPE,
        (select a.code_value_name
          from sys_code_values_v a
         where a.code = 'HLS_VEHICLE_TYPE'
         and a.code_value=t.vehicle_type) vehicle_type_n,
         --add by chenlingfeng
         t.ton_code,
         t.short_name,
         t.full_name,
         t.pattern,
         (select v.code_value_name as value_name
          from sys_code_values_v v
         where v.code = 'DS_TON_NAME'
           and t.ton_code = v.code_value) ton_code_n,
         t.ITEM_TYPE  ,--物件类型
          (select v.code_value_name as value_name
          from sys_code_values_v v
         where v.code = 'DS_ITEM_TYPE'
           and t.ITEM_TYPE = v.code_value) ITEM_TYPE_n,
         t.machine_number ,--机号
         t.equipment_type,
          (select v.code_value_name as value_name
          from sys_code_values_v v
         where v.code = 'EQUIPMENT_TYPE'
           and t.EQUIPMENT_TYPE = v.code_value) EQUIPMENT_TYPE_n,
         t.FACTORY_price,
         t.DELIVERY_DATE,
         t.sale_date,
         --add  by  lijingjing
            t.division,

        t.machine_model,
         t.equipments,
             t.accessories,
       t.origin_place,
       t.machine_model_class,
         (select v.code_value_name as value_name
          from sys_code_values_v v
         where v.code = 'MACHINE_MODEL_CLASS'
           and t.machine_model_class = v.code_value) machine_model_class_n,
            (select v.code_value_name as value_name
          from sys_code_values_v v
         where v.code = 'ORIGIN_PLACE'
           and t.origin_place = v.code_value) origin_place_n,
            (select v.code_value_name as value_name
          from sys_code_values_v v
         where v.code = 'DIVISION'
           and t.division = v.code_value)  division_n,
           t.full_name   full_name_n

         --end
  from prj_project_lease_item t;