Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- CREATE OR REPLACE FUNCTION TVIC.xml_slow_fnc (
- INN_VRM IN CHAR,
- IN_MILEAGE IN NUMBER DEFAULT NULL)
- RETURN XMLTYPE
- AS /*
- */
- strreturn XMLTYPE;
- in_vrm vehicle.vehicle_vrm%TYPE;
- l_vehicle_vin vehicle.vehicle_vin%TYPE;
- l_vehicle_abicode vehicle.vehicle_abicode%TYPE;
- l_abi_string XMLTYPE;
- l_keeper_vrm keeper_simple.keeper_vrm%TYPE;
- l_vehicle_count NUMBER;
- l_uk NUMBER;
- l_uk_date DATE;
- l_ni NUMBER;
- l_ni_date DATE;
- l_vehicle_mvris_code vehicle_simple.mvris_code%TYPE DEFAULT NULL;
- abi_table abi_model_type;
- l_reg_date DATE;
- l_manuf_date DATE;
- l_body_class VARCHAR2 (30);
- v_mileage NUMBER;
- v_plate VARCHAR2 (2);
- v_adjustment_factor NUMBER;
- v_standard_mileage NUMBER;
- v_exception_msg VARCHAR2 (1000);
- l_mc_val_rec tvic.motorcycles_valuations%ROWTYPE;
- l_mvris_code tvic.vehicle_simple.mvris_code%TYPE;
- l_lance VARCHAR2 (1000);
- BEGIN
- in_vrm := REPLACE (inn_vrm, ' ');
- v_mileage := in_mileage;
- SELECT COUNT (*)
- INTO l_uk
- FROM tvic.uk_generic_full_vw
- WHERE vehicle_vrm = in_vrm;
- IF l_uk > 0
- THEN
- SELECT MAX (keeper_v5c_date)
- INTO l_uk_date
- FROM tvic.uk_generic_full_vw
- WHERE vehicle_vrm = in_vrm
- GROUP BY vehicle_vin, keeper_vrm;
- ELSE
- l_uk_date := NULL;
- END IF;
- SELECT COUNT (*)
- INTO l_ni
- FROM tvic.ni_vehicle_simple
- WHERE vehicle_vrm = in_vrm;
- IF l_ni > 0
- THEN
- SELECT keeper_start_date
- INTO l_ni_date
- FROM tvic.ni_vehicle_simple
- WHERE vehicle_vrm = in_vrm;
- ELSE
- l_ni_date := NULL;
- END IF;
- IF l_ni_date > l_uk_date
- OR (l_uk = 0 AND l_ni > 0)
- OR (l_uk_date IS NULL AND l_ni_date IS NOT NULL)
- THEN
- DBMS_OUTPUT.put_line ('ni: ' || in_vrm);
- SELECT VEHICLE_VIN,
- VEHICLE_ABICODE,
- FIRST_REG_DATE_NI,
- MVRIS_CODE,
- CASE
- WHEN VEHICLE_MANUF_DATE IS NULL
- THEN
- CASE
- WHEN FIRST_REG_DATE_NI < FIRST_REG_DATE_GB
- THEN
- FIRST_REG_DATE_NI
- WHEN FIRST_REG_DATE_NI IS NULL
- THEN
- FIRST_REG_DATE_GB
- WHEN FIRST_REG_DATE_GB IS NULL
- THEN
- FIRST_REG_DATE_NI
- ELSE
- FIRST_REG_DATE_GB
- END
- ELSE
- TO_DATE ( (TO_CHAR (VEHICLE_MANUF_DATE) || '-12-31'),
- 'YYYY-MM-DD')
- END
- VEHICLE_MANUF_DATE,
- CBC_CDL_BODY_CLASS
- INTO L_VEHICLE_VIN,
- L_VEHICLE_ABICODE,
- L_REG_DATE,
- L_MVRIS_CODE,
- L_MANUF_DATE,
- L_BODY_CLASS
- FROM TVIC.NI_VEHICLE_SIMPLE, TVIC.CDL_BODY_CLASS
- WHERE VEHICLE_VRM = IN_VRM AND CBC_BODY_CODE(+) = VEHICLE_BODY;
- IF L_BODY_CLASS = 'MOTORCYCLE'
- THEN
- SELECT MV.*
- INTO L_MC_VAL_REC
- FROM TVIC.NI_VEHICLE_SIMPLE V, TVIC.MOTORCYCLES_VALUATIONS MV
- WHERE V.VEHICLE_WHEELPLAN = MV.WHEELPLAN_CODE(+)
- AND V.VEHICLE_MAKE_CODE = MV.MAKE_CODE(+)
- AND V.VEHICLE_MODEL_CODE = MV.MODEL_CODE(+)
- AND VEHICLE_VRM = IN_VRM;
- END IF;
- SELECT PLATE
- INTO V_PLATE
- FROM TVIC.DOM
- WHERE VEHICLE_MANUF_DATE = L_REG_DATE;
- DBMS_OUTPUT.put_line ('body: ' || L_BODY_CLASS);
- IF L_BODY_CLASS = 'MOTORCYCLE'
- THEN
- VALN_MC_MILEAGE_ADJUST_V2_PRC (V_MILEAGE,
- TO_CHAR (L_REG_DATE, 'YYYY'),
- V_PLATE,
- V_ADJUSTMENT_FACTOR,
- V_STANDARD_MILEAGE,
- V_EXCEPTION_MSG);
- ELSE
- DBMS_OUTPUT.put_line ('body12342354234: ' || L_BODY_CLASS);
- VALN_MILEAGE_ADJUST_V2_PRC (V_MILEAGE,
- TO_CHAR (L_REG_DATE, 'YYYY'),
- V_PLATE,
- V_ADJUSTMENT_FACTOR,
- V_STANDARD_MILEAGE,
- V_EXCEPTION_MSG);
- END IF;
- IF l_vehicle_abicode IS NULL
- THEN
- SELECT tvic.xml_iris_abi_model_fnc (in_vrm) INTO abi_table FROM DUAL;
- SELECT XMLAGG (
- XMLELEMENT (
- "ABI_CODE",
- CASE
- WHEN iris_count > 1
- THEN
- XMLFOREST (iris_abi "CODE", iris_model "MODEL")
- ELSE
- XMLFOREST (iris_abi "CODE")
- END))
- INTO l_abi_string
- FROM TABLE (abi_table);
- ELSE
- SELECT abi_object_type (l_vehicle_abicode, NULL, NULL)
- BULK COLLECT INTO abi_table
- FROM DUAL;
- SELECT XMLELEMENT (
- "ABI_CODE",
- XMLFOREST (LPAD (l_vehicle_abicode, 8, '0') "CODE"))
- INTO l_abi_string
- FROM DUAL;
- END IF;
- SELECT XMLCONCAT (
- XMLELEMENT (
- "DATASET",
- (SELECT XMLCONCAT (
- XMLELEMENT (
- "DVLA",
- XMLELEMENT (
- "VEHICLE",
- XMLFOREST (make_desc "MAKE",
- model_desc "MODEL",
- vehicle_desc "VEHICLE_DESC",
- colour_desc "COLOUR")),
- CASE
- WHEN l_body_class = 'MOTORCYCLE'
- THEN
- XMLELEMENT (
- "MOTORCYCLES_VALUATION",
- XMLFOREST (
- l_mc_val_rec.make_desc "MAKE_DESC",
- l_mc_val_rec.model_desc "MODEL_DESC",
- l_mc_val_rec.count_vrm "COUNT_VRM",
- l_mc_val_rec.make_model_code "MAKE_MODEL_CODE",
- l_mc_val_rec.vip_code "VIP_CODE"))
- END),
- CASE
- WHEN l_body_class = 'MOTORCYCLE'
- THEN
- (SELECT XMLAGG (
- XMLELEMENT (
- "VALUATION",
- XMLFOREST (
- pat.make_desc "VIP_MAKE_DESC",
- pat.model_desc "VIP_MODEL_DESC",
- pat.vehicle_desc "VIP_VEHICLE_DESC",
- pat.fuel "VIP_FUEL",
- pat.mt "VIP_MT",
- year_manufactured "VIP_YEAR_MANUFACTURED",
- vip.plate "VIP_PLATE",
- pat.new_sale "VIP_NEW_SALE",
- v_mileage "ACTUAL_MILEAGE",
- XMLFOREST (
- ROUND (
- ( pat.pat_sale
- * ( 1
- + ( pc.rtr
- / 100)))
- * v_adjustment_factor
- / 25)
- * 25 "VIP_RTR",
- ROUND (
- ( pat.pat_sale
- * ( 1
- + ( pc.trade
- / 100)))
- * v_adjustment_factor
- / 25)
- * 25 "VIP_TRADE",
- ROUND (
- ( pat.pat_sale
- * ( 1
- + ( pc.av
- / 100)))
- * v_adjustment_factor
- / 25)
- * 25 "VIP_AVERAGE",
- ROUND (
- ( pat.pat_sale
- * ( 1
- + ( pc.retail
- / 100)))
- * v_adjustment_factor
- / 25)
- * 25 "VIP_RETAIL") "ADJUSTED")))
- FROM tvic.vip_bike_codes vip,
- tvic.dom dom,
- tvic.vip_bike_pat pat,
- tvic.vip_pat_codes_bike pc
- WHERE vip.vipcode =
- l_mc_val_rec.vip_code
- AND pat.vipcode = vip.vipcode
- AND pat.yearcode =
- TO_CHAR (l_reg_date,
- 'YY')
- || dom.plate
- AND pc.yearcode = pat.yearcode
- AND pc.pat_code = pat.pat_code
- AND RTRIM (vip.plate) = dom.plate
- AND l_reg_date =
- dom.vehicle_manuf_date
- AND TO_CHAR (
- vip.year_manufactured) =
- TO_CHAR (l_manuf_date,
- 'YYYY'))
- ELSE
- (SELECT /*+ use_nl(vip) */
- XMLAGG (
- XMLELEMENT (
- "VALUATION",
- XMLFOREST (
- pat.make_desc "VIP_MAKE_DESC",
- pat.model_desc "VIP_MODEL_DESC",
- pat.vehicle_desc "VIP_VEHICLE_DESC",
- pat.fuel "VIP_FUEL",
- pat.mt "VIP_MT",
- year_manufactured "VIP_YEAR_MANUFACTURED",
- vip.plate "VIP_PLATE",
- pat.new_sale "VIP_NEW_SALE",
- v_mileage "ACTUAL_MILEAGE",
- (SELECT XMLAGG (
- XMLELEMENT (
- EVALNAME pat_type,
- ROUND (
- ( pat.pat_sale
- * ( 1
- + ( pat_value
- / 100)))
- * v_adjustment_factor
- / 25)
- * 25))
- FROM ( SELECT *
- FROM (SELECT /*+ use_nl(vip) */
- DECODE (
- pat_type,
- 'CDL CAR FORECOURT', 'CAR_FORECOURT',
- 'CDL PRIVATE CLEAN', 'PRIVATE_CLEAN',
- 'CDL TRADE IN CLEAN', 'TRADE_IN_CLEAN',
- 'CDL AUCTION', 'AUCTION',
- 'VIPDATA RTR', 'VIP_RTR',
- 'VIPDATA TRADE', 'VIP_TRADE',
- 'VIPDATA AVERAGE', 'VIP_AVERAGE',
- 'VIPDATA RETAIL', 'VIP_RETAIL')
- pat_type,
- pat.vipcode
- pat_vipcode,
- pat.vip_type
- pat_viptype,
- pat_value
- FROM tvic.mvris_to_vip_vw m2v,
- tvic.vip_codes_vw vip,
- tvic.dom dom,
- tvic.vip_pat_vw pat,
- tvic.vip_pat_codes pc
- WHERE m2v.mvris_code(+) =
- l_mvris_code
- AND vip.vipcode(+) =
- m2v.vip_code
- AND vip.vip_type =
- m2v.vip_type
- AND pat.vip_type =
- m2v.vip_type
- AND pat.vipcode =
- vip.vipcode
- AND pat.yearcode =
- TO_CHAR (
- l_reg_date,
- 'YY')
- || dom.plate
- AND RTRIM (
- vip.plate) =
- dom.plate
- AND l_reg_date =
- dom.vehicle_manuf_date
- AND TO_CHAR (
- vip.year_manufactured) =
- TO_CHAR (
- l_manuf_date,
- 'YYYY')
- AND pc.yearcode =
- pat.yearcode
- AND pc.pat_code =
- pat.pat_code
- AND pc.pat_type IN
- ('CDL CAR FORECOURT',
- 'CDL PRIVATE CLEAN',
- 'CDL TRADE IN CLEAN',
- 'CDL AUCTION'))
- pc2
- ORDER BY DECODE (
- pc2.pat_type,
- 'VIP_RTR', 1,
- 'VIP_TRADE', 2,
- 'VIP_AVERAGE', 3,
- 'VIP_RETAIL', 4,
- 'CAR_FORECOURT', 5,
- 'PRIVATE_CLEAN', 6,
- 'TRADE_IN_CLEAN', 7,
- 'AUCTION', 8))
- WHERE pat_vipcode =
- pat.vipcode
- AND pat_viptype =
- pat.vip_type) "ADJUSTED")))
- FROM tvic.mvris_to_vip_vw m2v,
- tvic.vip_codes_vw vip,
- tvic.dom dom,
- tvic.vip_pat_vw pat
- WHERE m2v.mvris_code(+) =
- l_mvris_code
- AND vip.vipcode(+) = m2v.vip_code
- AND vip.vip_type = m2v.vip_type
- AND pat.vip_type = m2v.vip_type
- AND pat.vipcode = vip.vipcode
- AND pat.yearcode =
- TO_CHAR (l_reg_date,
- 'YY')
- || dom.plate
- AND RTRIM (vip.plate) = dom.plate
- AND l_reg_date =
- dom.vehicle_manuf_date
- AND TO_CHAR (
- vip.year_manufactured) =
- TO_CHAR (l_manuf_date,
- 'YYYY'))
- END)
- FROM ni_generic_full_vw v
- WHERE ( vehicle_vin = l_vehicle_vin
- OR vehicle_vrm = in_vrm)),
- (SELECT XMLCONCAT (
- XMLELEMENT (
- "MVRIS",
- XMLFOREST (
- mv2.engine_size "ENGINE_SIZE",
- mv2.exact_cc "CC",
- mv2.bhp_count "BHP_COUNT",
- mv2.model_variant_name "MODEL_VARIANT_NAME",
- mv2.door_count "DOOR_COUNT",
- mv2.body_desc "BODY_DESC",
- mv2.cab_type "CAB_TYPE",
- mv2.gearbox_type "GEARBOX_TYPE",
- mv2.number_of_axles "NUMBER_OF_AXLES"),
- XMLELEMENT (
- "MAKE",
- XMLELEMENT ("DESC", mv2.make_desc)),
- XMLELEMENT (
- "MODEL",
- XMLELEMENT ("DESC", mv2.model_desc)),
- XMLELEMENT (
- "BODY",
- XMLELEMENT ("DESC", mv2.body_desc)),
- XMLELEMENT (
- "FUEL",
- XMLELEMENT ("DESC", mv2.fuel_desc))))
- FROM tvic.ni_vehicle_simple mv1,
- tvic.mvris_schedule2 mv2
- WHERE mv1.mvris_code = mv2.mvris_code
- AND mv1.vehicle_vrm = in_vrm
- AND mv2.primary_fuel = 'Y')))
- data_set
- INTO strreturn
- FROM DUAL;
- ELSE
- SELECT COUNT (*)
- INTO L_VEHICLE_COUNT
- FROM TVIC.VEHICLE_SIMPLE
- WHERE VEHICLE_VRM = IN_VRM;
- IF L_VEHICLE_COUNT > 0
- THEN
- SELECT VEHICLE_VIN,
- VEHICLE_ABICODE,
- VEHICLE_REG_DATE,
- MVRIS_CODE,
- VEHICLE_MANUF_DATE,
- CBC_CDL_BODY_CLASS
- INTO L_VEHICLE_VIN,
- L_VEHICLE_ABICODE,
- L_REG_DATE,
- L_MVRIS_CODE,
- L_MANUF_DATE,
- L_BODY_CLASS
- FROM TVIC.VEHICLE_SIMPLE, TVIC.CDL_BODY_CLASS
- WHERE VEHICLE_VRM = IN_VRM AND CBC_BODY_CODE(+) = VEHICLE_BODY;
- DBMS_OUTPUT.put_line ('body: ' || L_BODY_CLASS);
- IF L_BODY_CLASS = 'MOTORCYCLE'
- THEN
- SELECT MV.*
- INTO L_MC_VAL_REC
- FROM TVIC.VEHICLE_SIMPLE V, TVIC.MOTORCYCLES_VALUATIONS MV
- WHERE V.VEHICLE_WHEELPLAN = MV.WHEELPLAN_CODE(+)
- AND SUBSTR (V.VEHICLE_MAKE_CODE, 1, 2) = MV.MAKE_CODE(+)
- AND V.VEHICLE_MODEL_CODE = MV.MODEL_CODE(+)
- AND VEHICLE_VRM = IN_VRM;
- END IF;
- DBMS_OUTPUT.put_line ('body2: ' || L_BODY_CLASS);
- SELECT PLATE
- INTO V_PLATE
- FROM TVIC.DOM
- WHERE VEHICLE_MANUF_DATE = L_REG_DATE;
- IF L_BODY_CLASS = 'MOTORCYCLE'
- THEN
- DBMS_OUTPUT.put_line ('body3: ' || L_BODY_CLASS);
- VALN_MC_MILEAGE_ADJUST_V2_PRC (V_MILEAGE,
- TO_CHAR (L_REG_DATE, 'YYYY'),
- V_PLATE,
- V_ADJUSTMENT_FACTOR,
- V_STANDARD_MILEAGE,
- V_EXCEPTION_MSG);
- ELSE
- DBMS_OUTPUT.put_line ('body4: ' || L_BODY_CLASS);
- VALN_MILEAGE_ADJUST_V2_PRC (V_MILEAGE,
- TO_CHAR (L_REG_DATE, 'YYYY'),
- V_PLATE,
- V_ADJUSTMENT_FACTOR,
- V_STANDARD_MILEAGE,
- V_EXCEPTION_MSG);
- END IF;
- IF l_vehicle_abicode IS NULL
- THEN
- SELECT tvic.xml_iris_abi_model_fnc (in_vrm)
- INTO abi_table
- FROM DUAL;
- SELECT XMLAGG (
- XMLELEMENT (
- "ABI_CODE",
- CASE
- WHEN iris_count > 1
- THEN
- XMLFOREST (iris_abi "CODE",
- iris_model "MODEL")
- ELSE
- XMLFOREST (iris_abi "CODE")
- END))
- INTO l_abi_string
- FROM TABLE (abi_table);
- ELSE
- SELECT abi_object_type (l_vehicle_abicode, NULL, NULL)
- BULK COLLECT INTO abi_table
- FROM DUAL;
- SELECT XMLELEMENT (
- "ABI_CODE",
- XMLFOREST (LPAD (l_vehicle_abicode, 8, '0') "CODE"))
- INTO l_abi_string
- FROM DUAL;
- END IF;
- SELECT XMLCONCAT (
- XMLELEMENT (
- "DATASET",
- (SELECT XMLCONCAT (
- XMLELEMENT (
- "DVLA",
- XMLELEMENT (
- "VEHICLE",
- XMLFOREST (
- make_desc "MAKE",
- model_desc "MODEL",
- vehicle_desc "VEHICLE_DESC",
- colour_desc "COLOUR")),
- CASE
- WHEN l_body_class = 'MOTORCYCLE'
- THEN /* Not required if feed for MC only (vehicle_type MC) */
- XMLELEMENT (
- "MOTORCYCLES_VALUATION",
- XMLFOREST (
- l_mc_val_rec.make_desc "MAKE_DESC",
- l_mc_val_rec.model_desc "MODEL_DESC",
- l_mc_val_rec.count_vrm "COUNT_VRM",
- l_mc_val_rec.make_model_code "MAKE_MODEL_CODE",
- l_mc_val_rec.vip_code "VIP_CODE"))
- END),
- CASE
- WHEN l_body_class = 'MOTORCYCLE'
- THEN
- (SELECT XMLAGG (
- XMLELEMENT (
- "VALUATION",
- XMLFOREST (
- pat.make_desc "VIP_MAKE_DESC",
- pat.model_desc "VIP_MODEL_DESC",
- pat.vehicle_desc "VIP_VEHICLE_DESC",
- pat.fuel "VIP_FUEL",
- pat.mt "VIP_MT",
- year_manufactured "VIP_YEAR_MANUFACTURED",
- vip.plate "VIP_PLATE",
- pat.new_sale "VIP_NEW_SALE",
- v_mileage "ACTUAL_MILEAGE",
- XMLFOREST (
- ROUND (
- ( pat.pat_sale
- * ( 1
- + ( pc.rtr
- / 100)))
- * v_adjustment_factor
- / 25)
- * 25 "VIP_RTR",
- ROUND (
- ( pat.pat_sale
- * ( 1
- + ( pc.trade
- / 100)))
- * v_adjustment_factor
- / 25)
- * 25 "VIP_TRADE",
- ROUND (
- ( pat.pat_sale
- * ( 1
- + ( pc.av
- / 100)))
- * v_adjustment_factor
- / 25)
- * 25 "VIP_AVERAGE",
- ROUND (
- ( pat.pat_sale
- * ( 1
- + ( pc.retail
- / 100)))
- * v_adjustment_factor
- / 25)
- * 25 "VIP_RETAIL") "ADJUSTED")))
- FROM tvic.vip_bike_codes vip,
- tvic.dom dom,
- tvic.vip_bike_pat pat,
- tvic.vip_pat_codes_bike pc
- WHERE vip.vipcode =
- l_mc_val_rec.vip_code
- AND pat.vipcode = vip.vipcode
- AND pat.yearcode =
- TO_CHAR (l_reg_date,
- 'YY')
- || dom.plate
- AND pc.yearcode = pat.yearcode
- AND pc.pat_code = pat.pat_code
- AND RTRIM (vip.plate) =
- dom.plate
- AND l_reg_date =
- dom.vehicle_manuf_date
- AND TO_CHAR (
- vip.year_manufactured) =
- TO_CHAR (l_manuf_date,
- 'YYYY'))
- ELSE
- (SELECT /*+ use_nl(vip) */
- XMLAGG (
- XMLELEMENT (
- "VALUATION",
- XMLFOREST (
- pat.make_desc "VIP_MAKE_DESC",
- pat.model_desc "VIP_MODEL_DESC",
- pat.vehicle_desc "VIP_VEHICLE_DESC",
- pat.fuel "VIP_FUEL",
- pat.mt "VIP_MT",
- year_manufactured "VIP_YEAR_MANUFACTURED",
- vip.plate "VIP_PLATE",
- pat.new_sale "VIP_NEW_SALE",
- v_mileage "ACTUAL_MILEAGE",
- (SELECT XMLAGG (
- XMLELEMENT (
- EVALNAME pat_type,
- ROUND (
- ( pat.pat_sale
- * ( 1
- + ( pat_value
- / 100)))
- * v_adjustment_factor
- / 25)
- * 25))
- FROM ( SELECT *
- FROM (SELECT /*+ use_nl(vip) */
- DECODE (
- pat_type,
- 'CDL CAR FORECOURT', 'CAR_FORECOURT',
- 'CDL PRIVATE CLEAN', 'PRIVATE_CLEAN',
- 'CDL TRADE IN CLEAN', 'TRADE_IN_CLEAN',
- 'CDL AUCTION', 'AUCTION',
- 'VIPDATA RTR', 'VIP_RTR',
- 'VIPDATA TRADE', 'VIP_TRADE',
- 'VIPDATA AVERAGE', 'VIP_AVERAGE',
- 'VIPDATA RETAIL', 'VIP_RETAIL')
- pat_type,
- pat.vipcode
- pat_vipcode,
- pat.vip_type
- pat_viptype,
- pat_value
- FROM tvic.mvris_to_vip_vw m2v,
- tvic.vip_codes_vw vip,
- tvic.dom dom,
- tvic.vip_pat_vw pat,
- tvic.vip_pat_codes pc
- WHERE m2v.mvris_code(+) =
- l_mvris_code
- AND vip.vipcode(+) =
- m2v.vip_code
- AND vip.vip_type =
- m2v.vip_type
- AND pat.vip_type =
- m2v.vip_type
- AND pat.vipcode =
- vip.vipcode
- AND pat.yearcode =
- TO_CHAR (
- l_reg_date,
- 'YY')
- || dom.plate
- AND RTRIM (
- vip.plate) =
- dom.plate
- AND l_reg_date =
- dom.vehicle_manuf_date
- AND TO_CHAR (
- vip.year_manufactured) =
- TO_CHAR (
- l_manuf_date,
- 'YYYY')
- AND pc.yearcode =
- pat.yearcode
- AND pc.pat_code =
- pat.pat_code
- AND pc.pat_type IN
- ('CDL CAR FORECOURT',
- 'CDL PRIVATE CLEAN',
- 'CDL TRADE IN CLEAN',
- 'CDL AUCTION'))
- pc2
- ORDER BY DECODE (
- pc2.pat_type,
- 'VIP_RTR', 1,
- 'VIP_TRADE', 2,
- 'VIP_AVERAGE', 3,
- 'VIP_RETAIL', 4,
- 'CAR_FORECOURT', 5,
- 'PRIVATE_CLEAN', 6,
- 'TRADE_IN_CLEAN', 7,
- 'AUCTION', 8))
- WHERE pat_vipcode =
- pat.vipcode
- AND pat_viptype =
- pat.vip_type) "ADJUSTED")))
- FROM tvic.mvris_to_vip_vw m2v,
- tvic.vip_codes_vw vip,
- tvic.dom dom,
- tvic.vip_pat_vw pat
- WHERE m2v.mvris_code(+) =
- l_mvris_code
- AND vip.vipcode(+) =
- m2v.vip_code
- AND vip.vip_type =
- m2v.vip_type
- AND pat.vip_type =
- m2v.vip_type
- AND pat.vipcode = vip.vipcode
- AND pat.yearcode =
- TO_CHAR (l_reg_date,
- 'YY')
- || dom.plate
- AND RTRIM (vip.plate) =
- dom.plate
- AND l_reg_date =
- dom.vehicle_manuf_date
- AND TO_CHAR (
- vip.year_manufactured) =
- TO_CHAR (l_manuf_date,
- 'YYYY'))
- END)
- FROM uk_generic_full_vw v
- WHERE ( vehicle_vin = l_vehicle_vin
- AND vehicle_vin_marker != 0
- OR vehicle_vrm = in_vrm)),
- (SELECT XMLCONCAT (
- CASE
- WHEN l_vehicle_count = 0
- THEN
- XMLCONCAT (XMLELEMENT ("DVLA", NULL))
- END,
- XMLELEMENT (
- "MVRIS",
- XMLFOREST (
- mv2.engine_size "ENGINE_SIZE",
- mv2.exact_cc "CC",
- mv2.bhp_count "BHP_COUNT",
- mv2.model_variant_name "MODEL_VARIANT_NAME",
- mv2.door_count "DOOR_COUNT",
- mv2.body_desc "BODY_DESC",
- mv2.cab_type "CAB_TYPE",
- mv2.gearbox_type "GEARBOX_TYPE",
- mv2.number_of_axles "NUMBER_OF_AXLES"),
- XMLELEMENT (
- "MAKE",
- XMLELEMENT ("DESC", mv2.make_desc)),
- XMLELEMENT (
- "MODEL",
- XMLELEMENT ("DESC", mv2.model_desc)),
- XMLELEMENT (
- "BODY",
- XMLELEMENT ("DESC", mv2.body_desc)),
- XMLELEMENT (
- "FUEL",
- XMLELEMENT ("DESC", mv2.fuel_desc))))
- FROM tvic.vehicle_simple mv1,
- tvic.mvris_schedule2 mv2
- WHERE mv1.mvris_code = mv2.mvris_code
- AND mv1.vehicle_vrm = in_vrm
- AND mv2.primary_fuel = 'Y')))
- data_set
- INTO strreturn
- FROM DUAL;
- DBMS_OUTPUT.put_line ('uk2: ' || l_body_class);
- ELSE
- DBMS_OUTPUT.put_line ('else: ' || in_vrm);
- SELECT XMLCONCAT (
- XMLELEMENT (
- "DATASET",
- (SELECT XMLCONCAT (
- XMLELEMENT (
- "DVLA",
- XMLELEMENT (
- "VEHICLE",
- XMLFOREST (
- make_desc "MAKE",
- model_desc "MODEL",
- vehicle_desc "VEHICLE_DESC",
- colour_desc "COLOUR")),
- CASE
- WHEN l_body_class = 'MOTORCYCLE'
- THEN
- XMLELEMENT (
- "MOTORCYCLES_VALUATION",
- XMLFOREST (
- l_mc_val_rec.make_desc "MAKE_DESC",
- l_mc_val_rec.model_desc "MODEL_DESC",
- l_mc_val_rec.count_vrm "COUNT_VRM",
- l_mc_val_rec.make_model_code "MAKE_MODEL_CODE",
- l_mc_val_rec.vip_code "VIP_CODE"))
- END),
- CASE
- WHEN l_body_class = 'MOTORCYCLE'
- THEN
- (SELECT XMLAGG (
- XMLELEMENT (
- "VALUATION",
- XMLFOREST (
- pat.make_desc "VIP_MAKE_DESC",
- pat.model_desc "VIP_MODEL_DESC",
- pat.vehicle_desc "VIP_VEHICLE_DESC",
- pat.fuel "VIP_FUEL",
- pat.mt "VIP_MT",
- year_manufactured "VIP_YEAR_MANUFACTURED",
- vip.plate "VIP_PLATE",
- pat.new_sale "VIP_NEW_SALE",
- v_mileage "ACTUAL_MILEAGE",
- XMLFOREST (
- ROUND (
- ( pat.pat_sale
- * ( 1
- + ( pc.rtr
- / 100)))
- * v_adjustment_factor
- / 25)
- * 25 "VIP_RTR",
- ROUND (
- ( pat.pat_sale
- * ( 1
- + ( pc.trade
- / 100)))
- * v_adjustment_factor
- / 25)
- * 25 "VIP_TRADE",
- ROUND (
- ( pat.pat_sale
- * ( 1
- + ( pc.av
- / 100)))
- * v_adjustment_factor
- / 25)
- * 25 "VIP_AVERAGE",
- ROUND (
- ( pat.pat_sale
- * ( 1
- + ( pc.retail
- / 100)))
- * v_adjustment_factor
- / 25)
- * 25 "VIP_RETAIL") "ADJUSTED")))
- FROM tvic.vip_bike_codes vip,
- tvic.dom dom,
- tvic.vip_bike_pat pat,
- tvic.vip_pat_codes_bike pc
- WHERE vip.vipcode =
- l_mc_val_rec.vip_code
- AND pat.vipcode = vip.vipcode
- AND pat.yearcode =
- TO_CHAR (l_reg_date,
- 'YY')
- || dom.plate
- AND pc.yearcode = pat.yearcode
- AND pc.pat_code = pat.pat_code
- AND RTRIM (vip.plate) =
- dom.plate
- AND l_reg_date =
- dom.vehicle_manuf_date
- AND TO_CHAR (
- vip.year_manufactured) =
- TO_CHAR (l_manuf_date,
- 'YYYY'))
- ELSE
- (SELECT /*+ use_nl(vip) */
- XMLAGG (
- XMLELEMENT (
- "VALUATION",
- XMLFOREST (
- pat.make_desc "VIP_MAKE_DESC",
- pat.model_desc "VIP_MODEL_DESC",
- pat.vehicle_desc "VIP_VEHICLE_DESC",
- pat.fuel "VIP_FUEL",
- pat.mt "VIP_MT",
- year_manufactured "VIP_YEAR_MANUFACTURED",
- vip.plate "VIP_PLATE",
- pat.new_sale "VIP_NEW_SALE",
- v_mileage "ACTUAL_MILEAGE",
- (SELECT XMLAGG (
- XMLELEMENT (
- EVALNAME pat_type,
- ROUND (
- ( pat.pat_sale
- * ( 1
- + ( pat_value
- / 100)))
- * v_adjustment_factor
- / 25)
- * 25))
- FROM ( SELECT *
- FROM (SELECT /*+ use_nl(vip) */
- DECODE (
- pat_type,
- 'CDL CAR FORECOURT', 'CAR_FORECOURT',
- 'CDL PRIVATE CLEAN', 'PRIVATE_CLEAN',
- 'CDL TRADE IN CLEAN', 'TRADE_IN_CLEAN',
- 'CDL AUCTION', 'AUCTION',
- 'VIPDATA RTR', 'VIP_RTR',
- 'VIPDATA TRADE', 'VIP_TRADE',
- 'VIPDATA AVERAGE', 'VIP_AVERAGE',
- 'VIPDATA RETAIL', 'VIP_RETAIL')
- pat_type,
- pat.vipcode
- pat_vipcode,
- pat.vip_type
- pat_viptype,
- pat_value
- FROM tvic.mvris_to_vip_vw m2v,
- tvic.vip_codes_vw vip,
- tvic.dom dom,
- tvic.vip_pat_vw pat,
- tvic.vip_pat_codes pc
- WHERE m2v.mvris_code(+) =
- l_mvris_code
- AND vip.vipcode(+) =
- m2v.vip_code
- AND vip.vip_type =
- m2v.vip_type
- AND pat.vip_type =
- m2v.vip_type
- AND pat.vipcode =
- vip.vipcode
- AND pat.yearcode =
- TO_CHAR (
- l_reg_date,
- 'YY')
- || dom.plate
- AND RTRIM (
- vip.plate) =
- dom.plate
- AND l_reg_date =
- dom.vehicle_manuf_date
- AND TO_CHAR (
- vip.year_manufactured) =
- TO_CHAR (
- l_manuf_date,
- 'YYYY')
- AND pc.yearcode =
- pat.yearcode
- AND pc.pat_code =
- pat.pat_code
- AND pc.pat_type IN
- ('CDL CAR FORECOURT',
- 'CDL PRIVATE CLEAN',
- 'CDL TRADE IN CLEAN',
- 'CDL AUCTION'))
- pc2
- ORDER BY DECODE (
- pc2.pat_type,
- 'VIP_RTR', 1,
- 'VIP_TRADE', 2,
- 'VIP_AVERAGE', 3,
- 'VIP_RETAIL', 4,
- 'CAR_FORECOURT', 5,
- 'PRIVATE_CLEAN', 6,
- 'TRADE_IN_CLEAN', 7,
- 'AUCTION', 8))
- WHERE pat_vipcode =
- pat.vipcode
- AND pat_viptype =
- pat.vip_type) "ADJUSTED")))
- FROM tvic.mvris_to_vip_vw m2v,
- tvic.vip_codes_vw vip,
- tvic.dom dom,
- tvic.vip_pat_vw pat
- WHERE m2v.mvris_code(+) =
- l_mvris_code
- AND vip.vipcode(+) =
- m2v.vip_code
- AND vip.vip_type =
- m2v.vip_type
- AND pat.vip_type =
- m2v.vip_type
- AND pat.vipcode = vip.vipcode
- AND pat.yearcode =
- TO_CHAR (l_reg_date,
- 'YY')
- || dom.plate
- AND RTRIM (vip.plate) =
- dom.plate
- AND l_reg_date =
- dom.vehicle_manuf_date
- AND TO_CHAR (
- vip.year_manufactured) =
- TO_CHAR (l_manuf_date,
- 'YYYY'))
- END)
- FROM UK_GENERIC_FULL_VW V
- WHERE ( VEHICLE_VIN = L_VEHICLE_VIN
- AND VEHICLE_VIN_MARKER != 0
- OR VEHICLE_VRM = IN_VRM)),
- (SELECT XMLCONCAT (
- CASE
- WHEN L_VEHICLE_COUNT = 0
- THEN
- XMLCONCAT (XMLELEMENT ("DVLA", NULL))
- END,
- XMLELEMENT (
- "MVRIS",
- XMLFOREST (
- mv2.engine_size "ENGINE_SIZE",
- mv2.exact_cc "CC",
- mv2.bhp_count "BHP_COUNT",
- mv2.model_variant_name "MODEL_VARIANT_NAME",
- mv2.door_count "DOOR_COUNT",
- mv2.body_desc "BODY_DESC",
- mv2.cab_type "CAB_TYPE",
- mv2.gearbox_type "GEARBOX_TYPE",
- mv2.number_of_axles "NUMBER_OF_AXLES"),
- XMLELEMENT (
- "MAKE",
- XMLELEMENT ("DESC", mv2.make_desc)),
- XMLELEMENT (
- "MODEL",
- XMLELEMENT ("DESC", mv2.model_desc)),
- XMLELEMENT (
- "BODY",
- XMLELEMENT ("DESC", mv2.body_desc)),
- XMLELEMENT (
- "FUEL",
- XMLELEMENT ("DESC", mv2.fuel_desc))))
- FROM tvic.mvris_schedule1 mv1,
- tvic.mvris_schedule2 mv2
- WHERE mv1.mvris_code = mv2.mvris_code
- AND mv1.vehicle_vrm = in_vrm
- AND mv2.primary_fuel = 'Y'
- AND (mv1.vehicle_reg_date, mv1.mvris_seq) =
- ( SELECT MAX (vehicle_reg_date),
- MAX (mvris_seq)
- FROM tvic.mvris_schedule1 s1
- WHERE vehicle_vrm = in_vrm
- AND vehicle_reg_date =
- (SELECT MAX (
- vehicle_reg_date)
- FROM tvic.mvris_schedule1
- WHERE vehicle_vrm =
- s1.vehicle_vrm)
- GROUP BY vehicle_reg_date))))
- data_set
- INTO strreturn
- FROM DUAL;
- END IF;
- END IF;
- RETURN strreturn;
- END xml_slow_fnc;
- /
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement