vr.sql

select
    *
from
    (
        select pav1.value make1, pav2.value year1, pav3.value model1, pav4.value style1,
        (select value from vehicle_source.product_attribute_value where product_id=p4.product_id and attribute_id=4) ed_style_id,
        (select value from vehicle_source.product_attribute_value where product_id=p3.product_id and attribute_id=3) ed_model_id,
        (select value from vehicle_source.product_attribute_value where product_id=p4.product_id and attribute_id=(select attribute_id from vehicle_source.attribute where lower(trim(attribute_name))='trim level')) trim_level,    
        (select value from vehicle_source.product_attribute_value where product_id=p4.product_id and attribute_id=(select attribute_id from vehicle_source.attribute where lower(trim(attribute_name))='style start date')) style_start_date,    
 
        --used body type features   
        (
        CASE
            --car like vehicles
            WHEN (select value from vehicle_source.product_attribute_value where product_id=p4.product_id and attribute_id=636) = '2dr Hatchback' then 'Car-like'
            WHEN (select value from vehicle_source.product_attribute_value where product_id=p4.product_id and attribute_id=636) = '4dr Hatchback' then 'Car-like'
            WHEN (select value from vehicle_source.product_attribute_value where product_id=p4.product_id and attribute_id=636) = 'Sedan' then 'Car-like'
            WHEN (select value from vehicle_source.product_attribute_value where product_id=p4.product_id and attribute_id=636) = 'Convertible' then 'Car-like'
            WHEN (select value from vehicle_source.product_attribute_value where product_id=p4.product_id and attribute_id=636) = 'Coupe' then 'Car-like'
            --car-truck mix
            WHEN (select value from vehicle_source.product_attribute_value where product_id=p4.product_id and attribute_id=636) = 'Passenger Minivan' then 'Car-Truck Mix'
            WHEN (select value from vehicle_source.product_attribute_value where product_id=p4.product_id and attribute_id=636) = 'Passenger Van' then 'Car-Truck Mix'
            WHEN (select value from vehicle_source.product_attribute_value where product_id=p4.product_id and attribute_id=636) = 'Wagon' then 'Car-Truck Mix'
            WHEN (select value from vehicle_source.product_attribute_value where product_id=p4.product_id and attribute_id=636) = '2dr SUV' then 'Truck-like'
            WHEN (select value from vehicle_source.product_attribute_value where product_id=p4.product_id and attribute_id=636) = '4dr SUV' then 'Truck-like'
            WHEN (select value from vehicle_source.product_attribute_value where product_id=p4.product_id and attribute_id=636) = 'Convertible SUV' then 'Truck-like'
            WHEN (select value from vehicle_source.product_attribute_value where product_id=p4.product_id and attribute_id=636) = 'Cargo Minivan' then 'Truck-like'
            WHEN (select value from vehicle_source.product_attribute_value where product_id=p4.product_id and attribute_id=636) = 'Cargo Van' then 'Truck-like'
            WHEN (select value from vehicle_source.product_attribute_value where product_id=p4.product_id and attribute_id=636) = 'Crew Cab Pickup' then 'Truck-like'
            WHEN (select value from vehicle_source.product_attribute_value where product_id=p4.product_id and attribute_id=636) = 'Extended Cab Pickup' then 'Truck-like'
            WHEN (select value from vehicle_source.product_attribute_value where product_id=p4.product_id and attribute_id=636) = 'Regular Cab Pickup' then 'Truck-like'
            ELSE 'Unknown'
        END
        ) VR_VehicleType,

        (
        CASE
            WHEN (select value from vehicle_source.product_attribute_value where product_id=p4.product_id and attribute_id=448) is not null then 'Crossover'
            WHEN (select value from vehicle_source.product_attribute_value where product_id=p4.product_id and attribute_id=636) = '2dr Hatchback' then 'Coupe'
            WHEN (select value from vehicle_source.product_attribute_value where product_id=p4.product_id and attribute_id=636) = '4dr Hatchback' then 'Sedan'
            WHEN (select value from vehicle_source.product_attribute_value where product_id=p4.product_id and attribute_id=636) = '2dr SUV' then 'SUV'
            WHEN (select value from vehicle_source.product_attribute_value where product_id=p4.product_id and attribute_id=636) = '4dr SUV' then 'SUV'
            WHEN (select value from vehicle_source.product_attribute_value where product_id=p4.product_id and attribute_id=636) = 'Convertible SUV' then 'SUV'
            WHEN (select value from vehicle_source.product_attribute_value where product_id=p4.product_id and attribute_id=636) = 'Cargo Minivan' then 'Van/Minivan'
            WHEN (select value from vehicle_source.product_attribute_value where product_id=p4.product_id and attribute_id=636) = 'Cargo Van' then 'Van/Minivan'
            WHEN (select value from vehicle_source.product_attribute_value where product_id=p4.product_id and attribute_id=636) = 'Passenger Minivan' then 'Van/Minivan'
            WHEN (select value from vehicle_source.product_attribute_value where product_id=p4.product_id and attribute_id=636) = 'Passenger Van' then 'Van/Minivan'
            WHEN (select value from vehicle_source.product_attribute_value where product_id=p4.product_id and attribute_id=636) = 'Crew Cab Pickup' then 'Pickup'
            WHEN (select value from vehicle_source.product_attribute_value where product_id=p4.product_id and attribute_id=636) = 'Extended Cab Pickup' then 'Pickup'
            WHEN (select value from vehicle_source.product_attribute_value where product_id=p4.product_id and attribute_id=636) = 'Regular Cab Pickup' then 'Pickup'
            ELSE (select value from vehicle_source.product_attribute_value where product_id=p4.product_id and attribute_id=636)
        END
        ) VR_BodyType1,

        (select value from vehicle_source.product_attribute_value where product_id=p4.product_id and attribute_id=(select attribute_id from vehicle_source.attribute where lower(trim(attribute_name))='msrp')) MSRP,    

        --Used Performance features
        (select value from vehicle_source.product_attribute_value where product_id=p4.product_id and attribute_id=(select attribute_id from vehicle_source.attribute where lower(trim(attribute_name))='towing capacity')) tow_capacity,    
        (select value from vehicle_source.product_attribute_value where product_id=p4.product_id and attribute_id=(select attribute_id from vehicle_source.attribute where lower(trim(attribute_name))='manufacturer 0-60mph acceleration time (seconds)')) manufacturer_060,    

        (select pav.value from vehicle_source.product_attribute_value pav
            inner join vehicle_source.product_product_map ppm on ppm.product_id=pav.product_id
            where ppm.parent_product_id=p4.product_id and attribute_id in (select attribute_id from vehicle_source.attribute where lower(trim(attribute_name))='horse power')) horse_power,

        (select pav.value from vehicle_source.product_attribute_value pav 
            inner join vehicle_source.product_product_map ppm on ppm.product_id=pav.product_id 
            where ppm.parent_product_id=p4.product_id and attribute_id in (select attribute_id from vehicle_source.attribute where lower(trim(attribute_name))='torque')) torque,

        (select pav.value from vehicle_source.product_attribute_value pav
            inner join vehicle_source.product_product_map ppm on ppm.product_id=pav.product_id  
            where ppm.parent_product_id=p4.product_id and attribute_id in (select attribute_id from vehicle_source.attribute where lower(trim(attribute_name))='size')) engine_size,

        (select pav.value from vehicle_source.product_attribute_value pav
            inner join vehicle_source.product_product_map ppm on ppm.product_id=pav.product_id  
            where ppm.parent_product_id=p4.product_id and attribute_id in (select attribute_id from vehicle_source.attribute where lower(trim(attribute_name))='cylinders')) num_cylinders,

        (select value from vehicle_source.product_attribute_value where product_id=p4.product_id and attribute_id=(select attribute_id from vehicle_source.attribute where lower(trim(attribute_name))='curb weight')) curb_weight,    

        --Used "Drives well in snow" features
        (select value from vehicle_source.product_attribute_value where product_id=p4.product_id and attribute_id=(select attribute_id from vehicle_source.attribute where lower(trim(attribute_name))='driven wheels')) driven_wheels,
        (select value from vehicle_source.product_attribute_value where product_id=p4.product_id and attribute_id=(select attribute_id from vehicle_source.attribute where lower(trim(attribute_name))='antilock braking system')) antilock_braking_system,
        (select value from vehicle_source.product_attribute_value where product_id=p4.product_id and attribute_id=(select attribute_id from vehicle_source.attribute where lower(trim(attribute_name))='traction control')) traction_control,
        (select value from vehicle_source.product_attribute_value where product_id=p4.product_id and attribute_id=(select attribute_id from vehicle_source.attribute where lower(trim(attribute_name))='minimum ground clearance')) min_ground_clearance,

        --Used Safety Features
        (select value from vehicle_source.product_attribute_value where product_id=p4.product_id and attribute_id=(select attribute_id from vehicle_source.attribute where lower(trim(attribute_name))='iihs frontal offset test results')) iihs_frontal_offset_test_resu,
        (select value from vehicle_source.product_attribute_value where product_id=p4.product_id and attribute_id=(select attribute_id from vehicle_source.attribute where lower(trim(attribute_name))='iihs rear crash protection/head restraint ratings')) iihs_rear_crash_protection,
        (select value from vehicle_source.product_attribute_value where product_id=p4.product_id and attribute_id=(select attribute_id from vehicle_source.attribute where lower(trim(attribute_name))='iihs side impact test results')) iihs_side_impact_test_results,
        (select value from vehicle_source.product_attribute_value where product_id=p4.product_id and attribute_id=(select attribute_id from vehicle_source.attribute where lower(trim(attribute_name))='nhtsa frontal driver rating')) nhtsa_frontal_driver_rating,
        (select value from vehicle_source.product_attribute_value where product_id=p4.product_id and attribute_id=(select attribute_id from vehicle_source.attribute where lower(trim(attribute_name))='nhtsa frontal passenger rating')) nhtsa_frontal_passenger_ratin,
        (select value from vehicle_source.product_attribute_value where product_id=p4.product_id and attribute_id=(select attribute_id from vehicle_source.attribute where lower(trim(attribute_name))='nhtsa rollover rating')) nhtsa_rollover_rating,
        (select value from vehicle_source.product_attribute_value where product_id=p4.product_id and attribute_id=(select attribute_id from vehicle_source.attribute where lower(trim(attribute_name))='nhtsa side driver rating')) nhtsa_side_driver_rating,
        (select value from vehicle_source.product_attribute_value where product_id=p4.product_id and attribute_id=(select attribute_id from vehicle_source.attribute where lower(trim(attribute_name))='nhtsa side rear passenger rating')) nhtsa_side_rear_passenger_rat,
        (select value from vehicle_source.product_attribute_value where product_id=p4.product_id and attribute_id=(select attribute_id from vehicle_source.attribute where lower(trim(attribute_name))='xenon headlights')) xenon_headlights,
        (select value from vehicle_source.product_attribute_value where product_id=p4.product_id and attribute_id=(select attribute_id from vehicle_source.attribute where lower(trim(attribute_name))='side airbags')) side_airbags,
        (select value from vehicle_source.product_attribute_value where product_id=p4.product_id and attribute_id=(select attribute_id from vehicle_source.attribute where lower(trim(attribute_name))='head airbags')) head_airbags,
        (select value from vehicle_source.product_attribute_value where product_id=p4.product_id and attribute_id=(select attribute_id from vehicle_source.attribute where lower(trim(attribute_name))='daytime running lights')) daytime_running_lights,
        (select value from vehicle_source.product_attribute_value where product_id=p4.product_id and attribute_id=(select attribute_id from vehicle_source.attribute where lower(trim(attribute_name))='front fog lights')) front_fog_lights,
        (select value from vehicle_source.product_attribute_value where product_id=p4.product_id and attribute_id=(select attribute_id from vehicle_source.attribute where lower(trim(attribute_name))='rolling code security')) rolling_code_security,
        (select value from vehicle_source.product_attribute_value where product_id=p4.product_id and attribute_id=(select attribute_id from vehicle_source.attribute where lower(trim(attribute_name))='passenger airbag deactivation')) passenger_airbag_deactivation,
        (select value from vehicle_source.product_attribute_value where product_id=p4.product_id and attribute_id=(select attribute_id from vehicle_source.attribute where lower(trim(attribute_name))='accident avoidance system')) accident_avoidance_system,
        
        --Fuel economy features start here
        (select value from vehicle_source.product_attribute_value where product_id=p4.product_id and attribute_id=(select attribute_id from vehicle_source.attribute where lower(trim(attribute_name))='epa city mpg')) epa_city_mpg,
        (select value from vehicle_source.product_attribute_value where product_id=p4.product_id and attribute_id=(select attribute_id from vehicle_source.attribute where lower(trim(attribute_name))='epa highway mpg')) epa_highway_mpg,
        (select value from vehicle_source.product_attribute_value where product_id=p4.product_id and attribute_id=(select attribute_id from vehicle_source.attribute where lower(trim(attribute_name))='epa combined mpg')) epa_combined_mpg,
        
        --used comfort attributes
        (select sum(nvl(value,0)) from vehicle_source.product_attribute_value where product_id=p4.product_id and attribute_id in (320,321,322,323,324)) maximum_seating_capacity,
        (select value from vehicle_source.product_attribute_value where product_id=p4.product_id and attribute_id=(select attribute_id from vehicle_source.attribute where lower(trim(attribute_name))='1st row head room')) first_row_head_room,
        (select value from vehicle_source.product_attribute_value where product_id=p4.product_id and attribute_id=(select attribute_id from vehicle_source.attribute where lower(trim(attribute_name))='1st row hip room')) first_row_hip_room,
        (select value from vehicle_source.product_attribute_value where product_id=p4.product_id and attribute_id=(select attribute_id from vehicle_source.attribute where lower(trim(attribute_name))='1st row leg room')) first_row_leg_room,
        (select value from vehicle_source.product_attribute_value where product_id=p4.product_id and attribute_id=(select attribute_id from vehicle_source.attribute where lower(trim(attribute_name))='1st row shoulder room')) first_row_shoulder_room,
        (select value from vehicle_source.product_attribute_value where product_id=p4.product_id and attribute_id=(select attribute_id from vehicle_source.attribute where lower(trim(attribute_name))='2nd row head room')) second_row_head_room,
        (select value from vehicle_source.product_attribute_value where product_id=p4.product_id and attribute_id=(select attribute_id from vehicle_source.attribute where lower(trim(attribute_name))='2nd row hip room')) second_row_hip_room,
        (select value from vehicle_source.product_attribute_value where product_id=p4.product_id and attribute_id=(select attribute_id from vehicle_source.attribute where lower(trim(attribute_name))='2nd row leg room')) second_row_leg_room,
        (select value from vehicle_source.product_attribute_value where product_id=p4.product_id and attribute_id=(select attribute_id from vehicle_source.attribute where lower(trim(attribute_name))='2nd row shoulder room')) second_row_shoulder_room,


        --Used Technology attributes

        --Nav Attributes
        (select value from vehicle_source.product_attribute_value where product_id=p4.product_id and attribute_id=(select attribute_id from vehicle_source.attribute where lower(trim(attribute_name))='compass')) compass,

        (select pav.value from vehicle_source.product_attribute_value pav 
            inner join vehicle_source.product_product_map ppm on ppm.product_id=pav.product_id  
            inner join vehicle_source.product po on po.product_id=ppm.product_id
            where ppm.parent_product_id=p4.product_id and po.product_type_id = 8
            and attribute_id in (select attribute_id from vehicle_source.attribute where lower(trim(attribute_name))='compass') and rownum = 1) compass_opt,

        (select value from vehicle_source.product_attribute_value where product_id=p4.product_id and attribute_id=(select attribute_id from vehicle_source.attribute where lower(trim(attribute_name))='navigation system')) navigation_system,

        (select pav.value from vehicle_source.product_attribute_value pav 
            inner join vehicle_source.product_product_map ppm on ppm.product_id=pav.product_id  
            inner join vehicle_source.product po on po.product_id=ppm.product_id
            where ppm.parent_product_id=p4.product_id and po.product_type_id = 8
            and attribute_id in (select attribute_id from vehicle_source.attribute where lower(trim(attribute_name))='navigation system') and rownum = 1) navigation_system_opt,

        (select value from vehicle_source.product_attribute_value where product_id=p4.product_id and attribute_id=(select attribute_id from vehicle_source.attribute where lower(trim(attribute_name))='real time traffic')) real_time_traffic,

        (select pav.value from vehicle_source.product_attribute_value pav
            inner join vehicle_source.product_product_map ppm on ppm.product_id=pav.product_id  
            inner join vehicle_source.product po on po.product_id=ppm.product_id
            where ppm.parent_product_id=p4.product_id and po.product_type_id = 8
            and attribute_id in (select attribute_id from vehicle_source.attribute where lower(trim(attribute_name))='real time traffic') and rownum = 1) real_time_traffic_opt,

        (select pav.value from vehicle_source.product_attribute_value pav
            inner join vehicle_source.product_product_map ppm on ppm.product_id=pav.product_id  
            inner join vehicle_source.product po on po.product_id=ppm.product_id
            where ppm.parent_product_id=p4.product_id  and po.product_type_id = 16
            and attribute_id in (select attribute_id from vehicle_source.attribute where lower(trim(attribute_name))='sent-to navigation function') and rownum =1) Sent_to_Nav_Function,  

    (select pav.value
     from vehicle_source.product_product_map ppms, vehicle_source.product_product_map ppm, vehicle_source.product p, 
          vehicle_source.product pc, vehicle_source.product_attribute_value pav
     where ppms.product_id = p.product_id
       and p.product_id = ppm.parent_product_id
       and ppm.product_id = pc.product_id
       and pc.product_id = pav.product_id
       and ppms.parent_product_id = p4.product_id                             
       and p.product_type_id = 8
       and pc.product_type_id = 16
       and pav.attribute_id in (select attribute_id from vehicle_source.attribute where lower(trim(attribute_name))='sent-to navigation function') 
       and rownum = 1 
    ) Sent_to_Nav_Function_opt, 

    (select pav.value from vehicle_source.product_attribute_value pav
        inner join vehicle_source.product_product_map ppm on ppm.product_id=pav.product_id  
        inner join vehicle_source.product po on po.product_id=ppm.product_id
        where ppm.parent_product_id=p4.product_id  and po.product_type_id = 16
    and attribute_id in (select attribute_id from vehicle_source.attribute where lower(trim(attribute_name))='traffic information')     
    and rownum =1     
    ) Traffic_Information,  

    (select pav.value
     from vehicle_source.product_product_map ppms, vehicle_source.product_product_map ppm, vehicle_source.product p, 
          vehicle_source.product pc, vehicle_source.product_attribute_value pav
     where ppms.product_id = p.product_id
       and p.product_id = ppm.parent_product_id
       and ppm.product_id = pc.product_id
       and pc.product_id = pav.product_id
       and ppms.parent_product_id = p4.product_id                             
       and p.product_type_id = 8
       and pc.product_type_id = 16
       and pav.attribute_id in (select attribute_id from vehicle_source.attribute where lower(trim(attribute_name))='traffic information') 
       and rownum = 1 
    ) Traffic_Information_opt, 

        (select value from vehicle_source.product_attribute_value where product_id=p4.product_id and attribute_id=(select attribute_id from vehicle_source.attribute where lower(trim(attribute_name))='trip computer')) trip_computer,

    (select pav.value from vehicle_source.product_attribute_value pav
        inner join vehicle_source.product_product_map ppm on ppm.product_id=pav.product_id  
        inner join vehicle_source.product po on po.product_id=ppm.product_id
        where ppm.parent_product_id=p4.product_id 
          and po.product_type_id = 8
          and attribute_id in (select attribute_id from vehicle_source.attribute where lower(trim(attribute_name))='trip computer')          
      and rownum = 1
    ) trip_computer_opt,

    --Phone Attributes
        (select value from vehicle_source.product_attribute_value where product_id=p4.product_id and attribute_id=(select attribute_id from vehicle_source.attribute where lower(trim(attribute_name))='bluetooth')) bluetooth,

    (select pav.value from vehicle_source.product_attribute_value pav
        inner join vehicle_source.product_product_map ppm on ppm.product_id=pav.product_id  
        inner join vehicle_source.product po on po.product_id=ppm.product_id
        where ppm.parent_product_id=p4.product_id 
          and po.product_type_id = 8
          and attribute_id in (select attribute_id from vehicle_source.attribute where lower(trim(attribute_name))='bluetooth')          
      and rownum = 1
    ) bluetooth_opt,

    (select pav.value from vehicle_source.product_attribute_value pav
        inner join vehicle_source.product_product_map ppm on ppm.product_id=pav.product_id  
        inner join vehicle_source.product po on po.product_id=ppm.product_id
        where ppm.parent_product_id=p4.product_id  and po.product_type_id = 11
    and attribute_id in (select attribute_id from vehicle_source.attribute where lower(trim(attribute_name))='usb connection')     
    and rownum =1     
    ) USB_Connection,  

    (select pav.value
     from vehicle_source.product_product_map ppms, vehicle_source.product_product_map ppm, vehicle_source.product p, 
          vehicle_source.product pc, vehicle_source.product_attribute_value pav
     where ppms.product_id = p.product_id
       and p.product_id = ppm.parent_product_id
       and ppm.product_id = pc.product_id
       and pc.product_id = pav.product_id
       and ppms.parent_product_id = p4.product_id                             
       and p.product_type_id = 8
       and pc.product_type_id = 11
       and pav.attribute_id in (select attribute_id from vehicle_source.attribute where lower(trim(attribute_name))='usb connection') 
       and rownum = 1 
    ) USB_Connection_opt, 

    (select pav.value from vehicle_source.product_attribute_value pav
        inner join vehicle_source.product_product_map ppm on ppm.product_id=pav.product_id  
        inner join vehicle_source.product po on po.product_id=ppm.product_id
        where ppm.parent_product_id=p4.product_id  and po.product_type_id = 16
    and attribute_id in (select attribute_id from vehicle_source.attribute where lower(trim(attribute_name))='hands-free calling')     
    and rownum =1     
    ) Hands_Free_Calling,  

    (select pav.value
     from vehicle_source.product_product_map ppms, vehicle_source.product_product_map ppm, vehicle_source.product p, 
          vehicle_source.product pc, vehicle_source.product_attribute_value pav
     where ppms.product_id = p.product_id
       and p.product_id = ppm.parent_product_id
       and ppm.product_id = pc.product_id
       and pc.product_id = pav.product_id
       and ppms.parent_product_id = p4.product_id                             
       and p.product_type_id = 8
       and pc.product_type_id = 16
       and pav.attribute_id in (select attribute_id from vehicle_source.attribute where lower(trim(attribute_name))='hands-free calling') 
       and rownum = 1 
    ) Hands_Free_Calling_opt, 

        (select value from vehicle_source.product_attribute_value where product_id=p4.product_id and attribute_id=(select attribute_id from vehicle_source.attribute where lower(trim(attribute_name))='phone')) phone,

    (select pav.value from vehicle_source.product_attribute_value pav
        inner join vehicle_source.product_product_map ppm on ppm.product_id=pav.product_id  
        inner join vehicle_source.product po on po.product_id=ppm.product_id
        where ppm.parent_product_id=p4.product_id 
          and po.product_type_id = 8
          and attribute_id in (select attribute_id from vehicle_source.attribute where lower(trim(attribute_name))='phone')          
      and rownum = 1
    ) phone_opt,

        (select value from vehicle_source.product_attribute_value where product_id=p4.product_id and attribute_id=(select attribute_id from vehicle_source.attribute where lower(trim(attribute_name))='phone antenna')) phone_antenna,

    (select pav.value from vehicle_source.product_attribute_value pav
        inner join vehicle_source.product_product_map ppm on ppm.product_id=pav.product_id  
        inner join vehicle_source.product po on po.product_id=ppm.product_id
        where ppm.parent_product_id=p4.product_id 
          and po.product_type_id = 8
          and attribute_id in (select attribute_id from vehicle_source.attribute where lower(trim(attribute_name))='phone antenna')          
      and rownum = 1
    ) phone_antenna_opt,

        (select value from vehicle_source.product_attribute_value where product_id=p4.product_id and attribute_id=(select attribute_id from vehicle_source.attribute where lower(trim(attribute_name))='phone controls on steering wheel')) phone_controls_on_wheel,

    (select pav.value from vehicle_source.product_attribute_value pav
        inner join vehicle_source.product_product_map ppm on ppm.product_id=pav.product_id  
        inner join vehicle_source.product po on po.product_id=ppm.product_id
        where ppm.parent_product_id=p4.product_id 
          and po.product_type_id = 8
          and attribute_id in (select attribute_id from vehicle_source.attribute where lower(trim(attribute_name))='phone controls on steering wheel')          
      and rownum = 1
    ) phone_controls_on_wheel_opt,

    (select pav.value from vehicle_source.product_attribute_value pav
        inner join vehicle_source.product_product_map ppm on ppm.product_id=pav.product_id  
        inner join vehicle_source.product po on po.product_id=ppm.product_id
        where ppm.parent_product_id=p4.product_id  and po.product_type_id = 16
    and attribute_id in (select attribute_id from vehicle_source.attribute where lower(trim(attribute_name))='text message/memo display')     
    and rownum =1     
    ) Text_Message_Memo_Display,  

    (select pav.value
     from vehicle_source.product_product_map ppms, vehicle_source.product_product_map ppm, vehicle_source.product p, 
          vehicle_source.product pc, vehicle_source.product_attribute_value pav
     where ppms.product_id = p.product_id
       and p.product_id = ppm.parent_product_id
       and ppm.product_id = pc.product_id
       and pc.product_id = pav.product_id
       and ppms.parent_product_id = p4.product_id                             
       and p.product_type_id = 8
       and pc.product_type_id = 16
       and pav.attribute_id in (select attribute_id from vehicle_source.attribute where lower(trim(attribute_name))='text message/memo display') 
       and rownum = 1 
    ) Text_Message_Memo_Display_opt, 

    (select pav.value from vehicle_source.product_attribute_value pav
        inner join vehicle_source.product_product_map ppm on ppm.product_id=pav.product_id  
        inner join vehicle_source.product po on po.product_id=ppm.product_id
        where ppm.parent_product_id=p4.product_id  and po.product_type_id = 16
    and attribute_id in (select attribute_id from vehicle_source.attribute where lower(trim(attribute_name))='text-to-speech (aka text-to-voice)')     
    and rownum =1     
    ) Text_to_Speech,  

    (select pav.value
     from vehicle_source.product_product_map ppms, vehicle_source.product_product_map ppm, vehicle_source.product p, 
          vehicle_source.product pc, vehicle_source.product_attribute_value pav
     where ppms.product_id = p.product_id
       and p.product_id = ppm.parent_product_id
       and ppm.product_id = pc.product_id
       and pc.product_id = pav.product_id
       and ppms.parent_product_id = p4.product_id                             
       and p.product_type_id = 8
       and pc.product_type_id = 16
       and pav.attribute_id in (select attribute_id from vehicle_source.attribute where lower(trim(attribute_name))='text-to-speech (aka text-to-voice)') 
       and rownum = 1 
    ) Text_to_Speech_opt, 

    --Music Attributes
    (select value from vehicle_source.product_attribute_value where product_id=p4.product_id and attribute_id=(select attribute_id from vehicle_source.attribute where lower(trim(attribute_name))='audio/video remote control')) audio_vid_remote_control,

    (select pav.value from vehicle_source.product_attribute_value pav
        inner join vehicle_source.product_product_map ppm on ppm.product_id=pav.product_id  
        inner join vehicle_source.product po on po.product_id=ppm.product_id
        where ppm.parent_product_id=p4.product_id 
          and po.product_type_id = 8
          and attribute_id in (select attribute_id from vehicle_source.attribute where lower(trim(attribute_name))='audio/video remote control')          
      and rownum = 1
    ) audio_vid_remote_control_opt,

    (select pav.value from vehicle_source.product_attribute_value pav
        inner join vehicle_source.product_product_map ppm on ppm.product_id=pav.product_id  
        inner join vehicle_source.product po on po.product_id=ppm.product_id
        where ppm.parent_product_id=p4.product_id  and po.product_type_id = 11
    and attribute_id in (select attribute_id from vehicle_source.attribute where lower(trim(attribute_name))='dvd-audio')     
    and rownum =1     
    ) DVD_Audio,  

    (select pav.value
     from vehicle_source.product_product_map ppms, vehicle_source.product_product_map ppm, vehicle_source.product p, 
          vehicle_source.product pc, vehicle_source.product_attribute_value pav
     where ppms.product_id = p.product_id
       and p.product_id = ppm.parent_product_id
       and ppm.product_id = pc.product_id
       and pc.product_id = pav.product_id
       and ppms.parent_product_id = p4.product_id                             
       and p.product_type_id = 8
       and pc.product_type_id = 11
       and pav.attribute_id in (select attribute_id from vehicle_source.attribute where lower(trim(attribute_name))='dvd-audio') 
       and rownum = 1 
    ) DVD_Audio_opt, 

        (select value from vehicle_source.product_attribute_value where product_id=p4.product_id and attribute_id=(select attribute_id from vehicle_source.attribute where lower(trim(attribute_name))='audio controls on steering wheel')) audio_controls_on_wheel,

    (select pav.value from vehicle_source.product_attribute_value pav
        inner join vehicle_source.product_product_map ppm on ppm.product_id=pav.product_id  
        inner join vehicle_source.product po on po.product_id=ppm.product_id
        where ppm.parent_product_id=p4.product_id 
          and po.product_type_id = 8
          and attribute_id in (select attribute_id from vehicle_source.attribute where lower(trim(attribute_name))='audio controls on steering wheel')          
      and rownum = 1
    ) audio_controls_on_wheel_opt,

    (select pav.value from vehicle_source.product_attribute_value pav
        inner join vehicle_source.product_product_map ppm on ppm.product_id=pav.product_id  
        inner join vehicle_source.product po on po.product_id=ppm.product_id
        where ppm.parent_product_id=p4.product_id  and po.product_type_id = 11
    and attribute_id in (select attribute_id from vehicle_source.attribute where lower(trim(attribute_name))='audio/video headphone jacks')     
    and rownum =1     
    ) Audio_Video_phone_Jacks,  

    (select pav.value
     from vehicle_source.product_product_map ppms, vehicle_source.product_product_map ppm, vehicle_source.product p, 
          vehicle_source.product pc, vehicle_source.product_attribute_value pav
     where ppms.product_id = p.product_id
       and p.product_id = ppm.parent_product_id
       and ppm.product_id = pc.product_id
       and pc.product_id = pav.product_id
       and ppms.parent_product_id = p4.product_id                             
       and p.product_type_id = 8
       and pc.product_type_id = 11
       and pav.attribute_id in (select attribute_id from vehicle_source.attribute where lower(trim(attribute_name))='audio/video headphone jacks') 
       and rownum = 1 
    ) Audio_Video_phone_Jacks_opt, 

    (select pav.value from vehicle_source.product_attribute_value pav
        inner join vehicle_source.product_product_map ppm on ppm.product_id=pav.product_id  
        inner join vehicle_source.product po on po.product_id=ppm.product_id
        where ppm.parent_product_id=p4.product_id  and po.product_type_id = 11
    and attribute_id in (select attribute_id from vehicle_source.attribute where lower(trim(attribute_name))='hard drive')     
    and rownum =1     
    ) Hard_Drive,  

    (select pav.value
     from vehicle_source.product_product_map ppms, vehicle_source.product_product_map ppm, vehicle_source.product p, 
          vehicle_source.product pc, vehicle_source.product_attribute_value pav
     where ppms.product_id = p.product_id
       and p.product_id = ppm.parent_product_id
       and ppm.product_id = pc.product_id
       and pc.product_id = pav.product_id
       and ppms.parent_product_id = p4.product_id                             
       and p.product_type_id = 8
       and pc.product_type_id = 11
       and pav.attribute_id in (select attribute_id from vehicle_source.attribute where lower(trim(attribute_name))='hard drive') 
       and rownum = 1 
    ) Hard_Drive_opt, 

    (select pav.value from vehicle_source.product_attribute_value pav
        inner join vehicle_source.product_product_map ppm on ppm.product_id=pav.product_id  
        inner join vehicle_source.product po on po.product_id=ppm.product_id
        where ppm.parent_product_id=p4.product_id  and po.product_type_id = 11
    and attribute_id in (select attribute_id from vehicle_source.attribute where lower(trim(attribute_name))='memory card slot')     
    and rownum =1     
    ) Memory_Card_Slot,  

    (select pav.value
     from vehicle_source.product_product_map ppms, vehicle_source.product_product_map ppm, vehicle_source.product p, 
          vehicle_source.product pc, vehicle_source.product_attribute_value pav
     where ppms.product_id = p.product_id
       and p.product_id = ppm.parent_product_id
       and ppm.product_id = pc.product_id
       and pc.product_id = pav.product_id
       and ppms.parent_product_id = p4.product_id                             
       and p.product_type_id = 8
       and pc.product_type_id = 11
       and pav.attribute_id in (select attribute_id from vehicle_source.attribute where lower(trim(attribute_name))='memory card slot') 
       and rownum = 1 
    ) Memory_Card_Slot_opt, 

    (select pav.value from vehicle_source.product_attribute_value pav
        inner join vehicle_source.product_product_map ppm on ppm.product_id=pav.product_id  
        inner join vehicle_source.product po on po.product_id=ppm.product_id
        where ppm.parent_product_id=p4.product_id  and po.product_type_id = 11
    and attribute_id in (select attribute_id from vehicle_source.attribute where lower(trim(attribute_name))='mp3 player')     
    and rownum =1     
    ) MP3_Player,  

    (select pav.value
     from vehicle_source.product_product_map ppms, vehicle_source.product_product_map ppm, vehicle_source.product p, 
          vehicle_source.product pc, vehicle_source.product_attribute_value pav
     where ppms.product_id = p.product_id
       and p.product_id = ppm.parent_product_id
       and ppm.product_id = pc.product_id
       and pc.product_id = pav.product_id
       and ppms.parent_product_id = p4.product_id                             
       and p.product_type_id = 8
       and pc.product_type_id = 11
       and pav.attribute_id in (select attribute_id from vehicle_source.attribute where lower(trim(attribute_name))='mp3 player') 
       and rownum = 1 
    ) MP3_Player_opt, 

    (select pav.value from vehicle_source.product_attribute_value pav
        inner join vehicle_source.product_product_map ppm on ppm.product_id=pav.product_id  
        inner join vehicle_source.product po on po.product_id=ppm.product_id
        where ppm.parent_product_id=p4.product_id  and po.product_type_id = 11
    and attribute_id in (select attribute_id from vehicle_source.attribute where lower(trim(attribute_name))='satellite radio')     
    and rownum =1     
    ) Satellite_Radio,  

    (select pav.value
     from vehicle_source.product_product_map ppms, vehicle_source.product_product_map ppm, vehicle_source.product p, 
          vehicle_source.product pc, vehicle_source.product_attribute_value pav
     where ppms.product_id = p.product_id
       and p.product_id = ppm.parent_product_id
       and ppm.product_id = pc.product_id
       and pc.product_id = pav.product_id
       and ppms.parent_product_id = p4.product_id                             
       and p.product_type_id = 8
       and pc.product_type_id = 11
       and pav.attribute_id in (select attribute_id from vehicle_source.attribute where lower(trim(attribute_name))='satellite radio') 
       and rownum = 1 
    ) Satellite_Radio_opt, 

    (select pav.value from vehicle_source.product_attribute_value pav
        inner join vehicle_source.product_product_map ppm on ppm.product_id=pav.product_id  
        inner join vehicle_source.product po on po.product_id=ppm.product_id
        where ppm.parent_product_id=p4.product_id  and po.product_type_id = 11
    and attribute_id in (select attribute_id from vehicle_source.attribute where lower(trim(attribute_name))='speed sensitive volume control')     
    and rownum =1     
    ) Speed_Sensi_Vol_Control,  

    (select pav.value
     from vehicle_source.product_product_map ppms, vehicle_source.product_product_map ppm, vehicle_source.product p, 
          vehicle_source.product pc, vehicle_source.product_attribute_value pav
     where ppms.product_id = p.product_id
       and p.product_id = ppm.parent_product_id
       and ppm.product_id = pc.product_id
       and pc.product_id = pav.product_id
       and ppms.parent_product_id = p4.product_id                             
       and p.product_type_id = 8
       and pc.product_type_id = 11
       and pav.attribute_id in (select attribute_id from vehicle_source.attribute where lower(trim(attribute_name))='speed sensitive volume control') 
       and rownum = 1 
    ) Speed_Sensi_Vol_Control_opt, 

    --Video attributes
    (select value from vehicle_source.product_attribute_value where product_id=p4.product_id and attribute_id=(select attribute_id from vehicle_source.attribute where lower(trim(attribute_name))='video game system')) video_game_system,

    (select pav.value from vehicle_source.product_attribute_value pav
        inner join vehicle_source.product_product_map ppm on ppm.product_id=pav.product_id  
        inner join vehicle_source.product po on po.product_id=ppm.product_id
        where ppm.parent_product_id=p4.product_id 
          and po.product_type_id = 8
          and attribute_id in (select attribute_id from vehicle_source.attribute where lower(trim(attribute_name))='video game system')          
      and rownum = 1
    ) video_game_system_opt,

        (select value from vehicle_source.product_attribute_value where product_id=p4.product_id and attribute_id=(select attribute_id from vehicle_source.attribute where lower(trim(attribute_name))='video monitor')) video_monitor,

    (select pav.value from vehicle_source.product_attribute_value pav
        inner join vehicle_source.product_product_map ppm on ppm.product_id=pav.product_id  
        inner join vehicle_source.product po on po.product_id=ppm.product_id
        where ppm.parent_product_id=p4.product_id 
          and po.product_type_id = 8
          and attribute_id in (select attribute_id from vehicle_source.attribute where lower(trim(attribute_name))='video monitor')          
      and rownum = 1
    ) video_monitor_opt,

        (select value from vehicle_source.product_attribute_value where product_id=p4.product_id and attribute_id=(select attribute_id from vehicle_source.attribute where lower(trim(attribute_name))='video player')) video_player,

    (select pav.value from vehicle_source.product_attribute_value pav
        inner join vehicle_source.product_product_map ppm on ppm.product_id=pav.product_id  
        inner join vehicle_source.product po on po.product_id=ppm.product_id
        where ppm.parent_product_id=p4.product_id 
          and po.product_type_id = 8
          and attribute_id in (select attribute_id from vehicle_source.attribute where lower(trim(attribute_name))='video player')          
      and rownum = 1
    ) video_player_opt,

    --Safety attributes
    (select pav.value from vehicle_source.product_attribute_value pav
        inner join vehicle_source.product_product_map ppm on ppm.product_id=pav.product_id  
        inner join vehicle_source.product po on po.product_id=ppm.product_id
        where ppm.parent_product_id=p4.product_id  and po.product_type_id = 16
    and attribute_id in (select attribute_id from vehicle_source.attribute where lower(trim(attribute_name))='concierge service')     
    and rownum =1     
    ) Concierge_Service,  

    (select pav.value
     from vehicle_source.product_product_map ppms, vehicle_source.product_product_map ppm, vehicle_source.product p, 
          vehicle_source.product pc, vehicle_source.product_attribute_value pav
     where ppms.product_id = p.product_id
       and p.product_id = ppm.parent_product_id
       and ppm.product_id = pc.product_id
       and pc.product_id = pav.product_id
       and ppms.parent_product_id = p4.product_id                             
       and p.product_type_id = 8
       and pc.product_type_id = 16
       and pav.attribute_id in (select attribute_id from vehicle_source.attribute where lower(trim(attribute_name))='concierge service') 
       and rownum = 1 
    ) Concierge_Service_opt, 

        (select value from vehicle_source.product_attribute_value where product_id=p4.product_id and attribute_id=(select attribute_id from vehicle_source.attribute where lower(trim(attribute_name))='exterior camera')) exterior_camera,

    (select pav.value from vehicle_source.product_attribute_value pav
        inner join vehicle_source.product_product_map ppm on ppm.product_id=pav.product_id  
        inner join vehicle_source.product po on po.product_id=ppm.product_id
        where ppm.parent_product_id=p4.product_id 
          and po.product_type_id = 8
          and attribute_id in (select attribute_id from vehicle_source.attribute where lower(trim(attribute_name))='exterior camera')          
      and rownum = 1
    ) exterior_camera_opt,

        (select value from vehicle_source.product_attribute_value where product_id=p4.product_id and attribute_id=(select attribute_id from vehicle_source.attribute where lower(trim(attribute_name))='head-up display')) heads_up_display,

    (select pav.value from vehicle_source.product_attribute_value pav
        inner join vehicle_source.product_product_map ppm on ppm.product_id=pav.product_id  
        inner join vehicle_source.product po on po.product_id=ppm.product_id
        where ppm.parent_product_id=p4.product_id 
          and po.product_type_id = 8
          and attribute_id in (select attribute_id from vehicle_source.attribute where lower(trim(attribute_name))='head-up display')          
      and rownum = 1
    ) heads_up_display_opt,

        (select value from vehicle_source.product_attribute_value where product_id=p4.product_id and attribute_id=(select attribute_id from vehicle_source.attribute where lower(trim(attribute_name))='night vision enhancement system')) night_vision_system,

    (select pav.value from vehicle_source.product_attribute_value pav
        inner join vehicle_source.product_product_map ppm on ppm.product_id=pav.product_id  
        inner join vehicle_source.product po on po.product_id=ppm.product_id
        where ppm.parent_product_id=p4.product_id 
          and po.product_type_id = 8
          and attribute_id in (select attribute_id from vehicle_source.attribute where lower(trim(attribute_name))='night vision enhancement system')          
      and rownum = 1
    ) night_vision_system_opt,

        (select value from vehicle_source.product_attribute_value where product_id=p4.product_id and attribute_id=(select attribute_id from vehicle_source.attribute where lower(trim(attribute_name))='parking assist')) parking_assist,

    (select pav.value from vehicle_source.product_attribute_value pav
        inner join vehicle_source.product_product_map ppm on ppm.product_id=pav.product_id  
        inner join vehicle_source.product po on po.product_id=ppm.product_id
        where ppm.parent_product_id=p4.product_id 
          and po.product_type_id = 8
          and attribute_id in (select attribute_id from vehicle_source.attribute where lower(trim(attribute_name))='parking assist')          
      and rownum = 1
    ) parking_assist_opt,

        (select value from vehicle_source.product_attribute_value where product_id=p4.product_id and attribute_id=(select attribute_id from vehicle_source.attribute where lower(trim(attribute_name))='parking sensors')) parking_sensors,

    (select pav.value from vehicle_source.product_attribute_value pav
        inner join vehicle_source.product_product_map ppm on ppm.product_id=pav.product_id  
        inner join vehicle_source.product po on po.product_id=ppm.product_id
        where ppm.parent_product_id=p4.product_id 
          and po.product_type_id = 8
          and attribute_id in (select attribute_id from vehicle_source.attribute where lower(trim(attribute_name))='parking sensors')          
      and rownum = 1
    ) parking_sensors_opt,

    (select pav.value from vehicle_source.product_attribute_value pav
        inner join vehicle_source.product_product_map ppm on ppm.product_id=pav.product_id  
        inner join vehicle_source.product po on po.product_id=ppm.product_id
        where ppm.parent_product_id=p4.product_id  and po.product_type_id = 16
    and attribute_id in (select attribute_id from vehicle_source.attribute where lower(trim(attribute_name))='roadside assistance')     
    and rownum =1     
    ) Roadside_Assistance,  

    (select pav.value
     from vehicle_source.product_product_map ppms, vehicle_source.product_product_map ppm, vehicle_source.product p, 
          vehicle_source.product pc, vehicle_source.product_attribute_value pav
     where ppms.product_id = p.product_id
       and p.product_id = ppm.parent_product_id
       and ppm.product_id = pc.product_id
       and pc.product_id = pav.product_id
       and ppms.parent_product_id = p4.product_id                             
       and p.product_type_id = 8
       and pc.product_type_id = 16
       and pav.attribute_id in (select attribute_id from vehicle_source.attribute where lower(trim(attribute_name))='roadside assistance') 
       and rownum = 1 
    ) Roadside_Assistance_opt, 

    (select pav.value from vehicle_source.product_attribute_value pav
        inner join vehicle_source.product_product_map ppm on ppm.product_id=pav.product_id  
        inner join vehicle_source.product po on po.product_id=ppm.product_id
        where ppm.parent_product_id=p4.product_id  and po.product_type_id = 16
    and attribute_id in (select attribute_id from vehicle_source.attribute where lower(trim(attribute_name))='stolen vehicle tracking/assistance')     
    and rownum =1     
    ) Stolen_Veh_Track_Assist,  

    (select pav.value
     from vehicle_source.product_product_map ppms, vehicle_source.product_product_map ppm, vehicle_source.product p, 
          vehicle_source.product pc, vehicle_source.product_attribute_value pav
     where ppms.product_id = p.product_id
       and p.product_id = ppm.parent_product_id
       and ppm.product_id = pc.product_id
       and pc.product_id = pav.product_id
       and ppms.parent_product_id = p4.product_id                             
       and p.product_type_id = 8
       and pc.product_type_id = 16
       and pav.attribute_id in (select attribute_id from vehicle_source.attribute where lower(trim(attribute_name))='stolen vehicle tracking/assistance') 
       and rownum = 1 
    ) Stolen_Veh_Track_Assist_opt, 

    (select pav.value from vehicle_source.product_attribute_value pav
        inner join vehicle_source.product_product_map ppm on ppm.product_id=pav.product_id  
        inner join vehicle_source.product po on po.product_id=ppm.product_id
        where ppm.parent_product_id=p4.product_id  and po.product_type_id = 16
    and attribute_id in (select attribute_id from vehicle_source.attribute where lower(trim(attribute_name))='vehicle locator')     
    and rownum =1     
    ) Vehicle_locator,  

    (select pav.value
     from vehicle_source.product_product_map ppms, vehicle_source.product_product_map ppm, vehicle_source.product p, 
          vehicle_source.product pc, vehicle_source.product_attribute_value pav
     where ppms.product_id = p.product_id
       and p.product_id = ppm.parent_product_id
       and ppm.product_id = pc.product_id
       and pc.product_id = pav.product_id
       and ppms.parent_product_id = p4.product_id                             
       and p.product_type_id = 8
       and pc.product_type_id = 16
       and pav.attribute_id in (select attribute_id from vehicle_source.attribute where lower(trim(attribute_name))='vehicle locator') 
       and rownum = 1 
    ) Vehicle_locator_opt, 

    --used Warranty info starts here
    --basic
    (select pav.value from vehicle_source.product_attribute_value pav
        inner join vehicle_source.product_product_map ppm on ppm.product_id=pav.product_id  
        where ppm.parent_product_id=p4.product_id and attribute_id in (select attribute_id from vehicle_source.attribute where lower(trim(attribute_name))='warranty miles limited/unlimited')
        and pav.product_id = 

          (select pav.product_id
          from vehicle_source.product_attribute_value pav
              inner join vehicle_source.product_product_map ppm on ppm.product_id=pav.product_id  
              where ppm.parent_product_id=p4.product_id and attribute_id in (select attribute_id from vehicle_source.attribute where lower(trim(attribute_name))='warranty type')
              and lower(trim(pav.value)) = 'basic'
           )
        
     ) warr_basic_miles_limit,

    (select pav.value from vehicle_source.product_attribute_value pav
        inner join vehicle_source.product_product_map ppm on ppm.product_id=pav.product_id  
        where ppm.parent_product_id=p4.product_id and attribute_id in (select attribute_id from vehicle_source.attribute where lower(trim(attribute_name))='warranty maximum mileage')
        and pav.product_id = 

          (select pav.product_id
          from vehicle_source.product_attribute_value pav
              inner join vehicle_source.product_product_map ppm on ppm.product_id=pav.product_id  
              where ppm.parent_product_id=p4.product_id and attribute_id in (select attribute_id from vehicle_source.attribute where lower(trim(attribute_name))='warranty type')
              and lower(trim(pav.value)) = 'basic'
           )
        
     ) warr_basic_miles_max,

    (select pav.value from vehicle_source.product_attribute_value pav
        inner join vehicle_source.product_product_map ppm on ppm.product_id=pav.product_id  
        where ppm.parent_product_id=p4.product_id and attribute_id in (select attribute_id from vehicle_source.attribute where lower(trim(attribute_name))='warranty years limited/unlimited')
        and pav.product_id = 

          (select pav.product_id
          from vehicle_source.product_attribute_value pav
              inner join vehicle_source.product_product_map ppm on ppm.product_id=pav.product_id  
              where ppm.parent_product_id=p4.product_id and attribute_id in (select attribute_id from vehicle_source.attribute where lower(trim(attribute_name))='warranty type')
              and lower(trim(pav.value)) = 'basic'
           )
        
     ) warr_basic_years_limit,

    (select pav.value from vehicle_source.product_attribute_value pav
        inner join vehicle_source.product_product_map ppm on ppm.product_id=pav.product_id  
        where ppm.parent_product_id=p4.product_id and attribute_id in (select attribute_id from vehicle_source.attribute where lower(trim(attribute_name))='warranty maximum years')
        and pav.product_id = 

          (select pav.product_id
          from vehicle_source.product_attribute_value pav
              inner join vehicle_source.product_product_map ppm on ppm.product_id=pav.product_id  
              where ppm.parent_product_id=p4.product_id and attribute_id in (select attribute_id from vehicle_source.attribute where lower(trim(attribute_name))='warranty type')
              and lower(trim(pav.value)) = 'basic'
           )
        
     ) warr_basic_years_max,

    --Pricing info starts here
    (select value from vehicle_source.product_attribute_value where product_id=p4.product_id and attribute_id=42) vehicle_tmv, 


typical_options_tmv vehicle_typical_options_tmv
  from  
        vehicle_source.product p1, 
        vehicle_source.product_product_map ppm1, 
        vehicle_source.product_attribute_value pav1,     
        vehicle_source.product p2, 
        vehicle_source.product_product_map ppm2, 
        vehicle_source.product_attribute_value pav2, 
        vehicle_source.product p3, 
        vehicle_source.product_product_map ppm3, 
        vehicle_source.product_attribute_value pav3,     
        vehicle_source.product p4, 
        vehicle_source.product_attribute_value pav4,
        (select ed_style_id, sum(average_tmv) typical_options_tmv
            from (
            select a.ed_style_id, a.option_id, count(a.rowid) num_rows, sum(a.tmv_price) total_tmv, sum(a.tmv_price)/count(a.rowid) average_tmv
            from tco_source.tco_typical_options a, carsprod.style_options_t so, carsprod.options_t o
            where a.option_id   = so.optn_id
             and a.ed_style_id = so.ed_style_id
             and so.optn_id    = o.id
             --and a.ed_style_id = 101204481
             and a.active_flag = 'Y'
             and o.evdb_option_id is not null
             --and a.tmv_price != 0
            group  by a.ed_style_id, a.option_id
            )
            group by ed_style_id
        ) sto                
        
where 
    p1.product_type_id=1  
    and p2.product_type_id=2     
    and p3.product_type_id=3 
    and p4.product_type_id=4 
    and p1.PRODUCT_ID=ppm1.PARENT_PRODUCT_ID 
    and p2.PRODUCT_ID=ppm2.PARENT_PRODUCT_ID 
    and p3.PRODUCT_ID=ppm3.PARENT_PRODUCT_ID 
    and p2.PRODUCT_ID=ppm1.PRODUCT_ID 
    and p3.PRODUCT_ID=ppm2.PRODUCT_ID 
    and p4.PRODUCT_ID=ppm3.PRODUCT_ID 
    and p1.PRODUCT_ID=pav1.PRODUCT_ID 
    and p2.PRODUCT_ID=pav2.PRODUCT_ID 
    and p3.PRODUCT_ID=pav3.PRODUCT_ID 
    and p4.PRODUCT_ID=pav4.PRODUCT_ID 
    and pav1.attribute_id=1 
    and pav2.attribute_id=1 
    and pav3.attribute_id=1 
    and pav4.attribute_id=1
    and p4.product_id = sto.ed_style_id (+)
    and pav2.value >= 2009
          ) big 

order by 1,2,3,4