[Home] [Help]
1016: AND l_ext_start_date < fnd_date.canonical_to_date('2003/04/06') -- BUG 3431106 Using canonical_to_date function
1017: UNION
1018: SELECT 'Y' vehicle_exist_flag
1019: FROM pqp_vehicle_allocations_f paaf,
1020: pqp_vehicle_repository_f pvd
1021: WHERE paaf.assignment_id = p_asg_id
1022: AND paaf.effective_start_date <= l_ext_end_date
1023: AND paaf.effective_end_date >= l_ext_start_date
1024: AND paaf.vehicle_repository_id IS NOT NULL
1674: --
1675: CURSOR get_eff_start_date IS
1676: SELECT greatest(paaf.effective_start_date, l_ext_start_date) effective_start_date
1677: FROM pqp_vehicle_allocations_f paaf,
1678: pqp_vehicle_repository_f pvd
1679: WHERE paaf.assignment_id = p_assignment_id
1680: AND paaf.effective_start_date <= l_ext_end_date
1681: AND paaf.effective_end_date >= l_ext_start_date
1682: AND paaf.vehicle_repository_id is NOT NULL
1718: --
1719: CURSOR get_ben_details IS
1720: SELECT paaf.effective_end_date effective_end_date
1721: FROM pqp_vehicle_allocations_f paaf,
1722: pqp_vehicle_repository_f pvd
1723: WHERE paaf.assignment_id = p_assignment_id
1724: AND paaf.effective_start_date <= l_ext_end_date
1725: AND paaf.effective_end_date >= l_ext_start_date
1726: AND paaf.vehicle_repository_id is NOT NULL
1764: --
1765: CURSOR get_vin IS
1766: SELECT pvd.registration_number
1767: FROM pqp_vehicle_allocations_f paaf,
1768: pqp_vehicle_repository_f pvd
1769: WHERE paaf.assignment_id = p_assignment_id -- comes from context
1770: AND pvd.vehicle_repository_id = paaf.vehicle_repository_id
1771: AND pvd.vehicle_type = 'C'
1772: AND pvd.vehicle_ownership = 'C'
1807: --
1808: CURSOR get_make IS
1809: SELECT pvd.make
1810: FROM pqp_vehicle_allocations_f paaf,
1811: pqp_vehicle_repository_f pvd
1812: WHERE paaf.assignment_id = p_assignment_id -- comes from context
1813: AND pvd.vehicle_repository_id = paaf.vehicle_repository_id
1814: AND pvd.vehicle_type = 'C'
1815: AND pvd.vehicle_ownership = 'C'
1849: --
1850: CURSOR get_model IS
1851: SELECT pvd.model
1852: FROM pqp_vehicle_allocations_f paaf,
1853: pqp_vehicle_repository_f pvd
1854: WHERE paaf.assignment_id = p_assignment_id -- comes from context
1855: AND pvd.vehicle_repository_id = paaf.vehicle_repository_id
1856: AND pvd.vehicle_type = 'C'
1857: AND pvd.vehicle_ownership = 'C'
1891: --
1892: CURSOR get_dfr IS
1893: SELECT pvd.initial_registration
1894: FROM pqp_vehicle_allocations_f paaf,
1895: pqp_vehicle_repository_f pvd
1896: WHERE paaf.assignment_id = p_assignment_id -- comes from context
1897: AND pvd.vehicle_repository_id = paaf.vehicle_repository_id
1898: AND pvd.vehicle_type = 'C'
1899: AND pvd.vehicle_ownership = 'C'
1933: --
1934: CURSOR get_price IS
1935: SELECT decode(nvl(pvd.market_value_classic_car,0), 0, list_price, pvd.market_value_classic_car)+ nvl(pvd.accessory_value_at_startdate,0)
1936: FROM pqp_vehicle_allocations_f paaf,
1937: pqp_vehicle_repository_f pvd
1938: WHERE paaf.assignment_id = p_assignment_id -- comes from context
1939: AND pvd.vehicle_repository_id = paaf.vehicle_repository_id
1940: AND pvd.vehicle_type = 'C'
1941: AND pvd.vehicle_ownership = 'C'
1978: --
1979: CURSOR get_fuel_type IS
1980: SELECT hl.description -- pvd.fuel_type
1981: FROM pqp_vehicle_allocations_f paaf,
1982: pqp_vehicle_repository_f pvd,
1983: hr_lookups hl
1984: WHERE paaf.assignment_id = p_assignment_id -- comes from context
1985: AND pvd.vehicle_repository_id = paaf.vehicle_repository_id
1986: AND pvd.vehicle_type = 'C'
2039: --
2040: CURSOR get_co2_emi IS
2041: SELECT pvd.fiscal_ratings
2042: FROM pqp_vehicle_allocations_f paaf,
2043: pqp_vehicle_repository_f pvd
2044: WHERE paaf.assignment_id = p_assignment_id -- comes from context
2045: AND pvd.vehicle_repository_id = paaf.vehicle_repository_id
2046: AND pvd.vehicle_type = 'C'
2047: AND pvd.vehicle_ownership = 'C'
2085: --old line SELECT decode(nvl(pvd.market_value_classic_car,0), 0, pvd.accessory_value_at_startdate, 0)
2086: SELECT decode(nvl(pvd.market_value_classic_car,0), 0, pvd.accessory_value_added_later, 0)
2087: --bug 7306948 end
2088: FROM pqp_vehicle_allocations_f paaf,
2089: pqp_vehicle_repository_f pvd
2090: WHERE paaf.assignment_id = p_assignment_id -- comes from context
2091: AND pvd.vehicle_repository_id = paaf.vehicle_repository_id
2092: AND pvd.vehicle_type = 'C'
2093: AND pvd.vehicle_ownership = 'C'
2127: --
2128: CURSOR get_cap_contrib IS
2129: SELECT paaf.capital_contribution
2130: FROM pqp_vehicle_allocations_f paaf,
2131: pqp_vehicle_repository_f pvd
2132: WHERE paaf.assignment_id = p_assignment_id -- comes from context
2133: AND paaf.usage_type = 'P'
2134: AND pvd.vehicle_repository_id = paaf.vehicle_repository_id
2135: AND pvd.vehicle_type = 'C'
2170: --
2171: CURSOR get_pri_contrib IS
2172: SELECT paaf.private_contribution
2173: FROM pqp_vehicle_allocations_f paaf,
2174: pqp_vehicle_repository_f pvd
2175: WHERE paaf.assignment_id = p_assignment_id -- comes from context
2176: AND paaf.usage_type = 'P'
2177: AND pvd.vehicle_repository_id = paaf.vehicle_repository_id
2178: AND pvd.vehicle_type = 'C'
2212: --
2213: CURSOR get_engine_cap IS
2214: SELECT pvd.engine_capacity_in_cc
2215: FROM pqp_vehicle_allocations_f paaf,
2216: pqp_vehicle_repository_f pvd
2217: WHERE paaf.assignment_id = p_assignment_id -- comes from context
2218: AND pvd.vehicle_repository_id = paaf.vehicle_repository_id
2219: AND pvd.vehicle_type = 'C'
2220: AND pvd.vehicle_ownership = 'C'
2254: --
2255: CURSOR get_fuel_benefit IS
2256: SELECT substr(paaf.fuel_benefit, 1, 1)
2257: FROM pqp_vehicle_allocations_f paaf,
2258: pqp_vehicle_repository_f pvd
2259: WHERE paaf.assignment_id = p_assignment_id -- comes from context
2260: AND paaf.usage_type = 'P'
2261: AND pvd.vehicle_repository_id = paaf.vehicle_repository_id
2262: AND pvd.vehicle_type = 'C'
2295: --
2296: CURSOR get_fuel_reinstated IS
2297: SELECT substr(paaf.fuel_benefit,1,1) fuel_benefit
2298: FROM pqp_vehicle_allocations_f paaf,
2299: pqp_vehicle_repository_f pvd
2300: WHERE paaf.assignment_id = p_assignment_id -- comes from context
2301: AND paaf.usage_type = 'P'
2302: AND pvd.vehicle_repository_id = paaf.vehicle_repository_id
2303: AND pvd.vehicle_type = 'C'
2347: --
2348: CURSOR get_date_free_fuel_withdrawn IS
2349: SELECT substr(paaf.fuel_benefit,1,1)fuel_benefit,paaf.effective_end_date effective_end_date
2350: FROM pqp_vehicle_allocations_f paaf,
2351: pqp_vehicle_repository_f pvd
2352: WHERE paaf.assignment_id = p_assignment_id -- comes from context
2353: AND paaf.usage_type = 'P'
2354: AND pvd.vehicle_repository_id = paaf.vehicle_repository_id
2355: AND pvd.vehicle_type = 'C'
2401: --
2402: CURSOR get_date_free_fuel IS
2403: SELECT paaf.fuel_benefit
2404: FROM pqp_vehicle_allocations_f paaf,
2405: pqp_vehicle_repository_f pvd
2406: WHERE paaf.assignment_id = p_assignment_id -- comes from context
2407: AND paaf.usage_type = 'P'
2408: AND pvd.vehicle_repository_id = paaf.vehicle_repository_id
2409: AND pvd.vehicle_type = 'C'
2457: --
2458: CURSOR get_fuel_reinstated IS
2459: SELECT substr(paaf.fuel_benefit,1,1) fuel_benefit
2460: FROM pqp_vehicle_allocations_f paaf,
2461: pqp_vehicle_repository_f pvd
2462: WHERE paaf.assignment_id = p_assignment_id -- comes from context
2463: AND paaf.usage_type = 'P'
2464: AND pvd.vehicle_repository_id = paaf.vehicle_repository_id
2465: AND pvd.vehicle_type = 'C'
2511: --
2512: CURSOR get_date_free_fuel_withdrawn IS
2513: SELECT substr(paaf.fuel_benefit,1,1)fuel_benefit,paaf.effective_end_date effective_end_date
2514: FROM pqp_vehicle_allocations_f paaf,
2515: pqp_vehicle_repository_f pvd
2516: WHERE paaf.assignment_id = p_assignment_id -- comes from context
2517: AND paaf.usage_type = 'P'
2518: AND pvd.vehicle_repository_id = paaf.vehicle_repository_id
2519: AND pvd.vehicle_type = 'C'
2567: --
2568: CURSOR get_date_free_fuel IS
2569: SELECT paaf.fuel_benefit
2570: FROM pqp_vehicle_allocations_f paaf,
2571: pqp_vehicle_repository_f pvd
2572: WHERE paaf.assignment_id = p_assignment_id -- comes from context
2573: AND paaf.usage_type = 'P'
2574: AND pvd.vehicle_repository_id = paaf.vehicle_repository_id
2575: AND pvd.vehicle_type = 'C'
2639: decode(nvl(market_value_classic_car,0), 0, accessory_value_added_later, 0) optional_accessory,
2640: --bug 7306948 end
2641: accessory_value_added_later,
2642: engine_capacity_in_cc
2643: FROM pqp_vehicle_repository_f pvd,
2644: hr_lookups h1, --Bug fix 3799560
2645: hr_lookups h2
2646: WHERE vehicle_repository_id = p_vehicle_repository_id
2647: AND fuel_type = h1.lookup_code
2809: SELECT greatest(paaf.effective_start_date, l_ext_start_date) effective_start_date,
2810: least(paaf.effective_end_date, l_ext_end_date) effective_end_date,
2811: paaf.fuel_benefit fuel_benefit,paaf.vehicle_repository_id vehicle_repository_id,paaf.vehicle_allocation_id vehicle_allocation_id
2812: FROM pqp_vehicle_allocations_f paaf,
2813: pqp_vehicle_repository_f pvd
2814: WHERE paaf.assignment_id = p_asg_id
2815: AND paaf.effective_start_date <= l_ext_end_date
2816: AND paaf.effective_end_date >= l_ext_start_date
2817: AND paaf.vehicle_repository_id is NOT NULL
2952: SELECT greatest(paaf.effective_start_date, l_ext_start_date) effective_start_date,
2953: least(paaf.effective_end_date, l_ext_end_date) effective_end_date,
2954: paaf.fuel_benefit fuel_benefit,paaf.vehicle_repository_id vehicle_repository_id, paaf.vehicle_allocation_id vehicle_allocation_id
2955: FROM pqp_vehicle_allocations_f paaf,
2956: pqp_vehicle_repository_f pvd
2957: WHERE paaf.assignment_id = p_asg_id
2958: AND paaf.effective_start_date <= l_ext_end_date
2959: AND paaf.effective_end_date >= l_ext_start_date
2960: AND paaf.vehicle_repository_id is NOT NULL