[Home] [Help]
383: SELECT pvaf.element_type_id
384: ,pvaf.rates_table_id
385: ,pvaf.sliding_rates_info sliding_rates
386: FROM pqp_vehicle_allocations_f pvaf,
387: pqp_vehicle_repository_f pvrf
388: WHERE pvaf.assignment_id=cp_assignment_id
389: AND pvaf.business_group_id=cp_business_group_id
390: AND pvrf.vehicle_ownership=cp_ownership_type
391: AND (p_registration_number IS NULL or
762: IS
763: CURSOR c_get_cc
764: IS
765: SELECT pvr.engine_capacity_in_cc engine_capacity
766: FROM pqp_vehicle_repository_f pvr
767: WHERE pvr.business_group_id =p_business_group_id
768: AND pvr.registration_number =p_registration_number
769: AND p_effective_date BETWEEN pvr.effective_start_date
770: AND pvr.effective_end_date
1821: CURSOR c_get_reg_num
1822: IS
1823: SELECT pvr.registration_number
1824: ,pva.default_vehicle
1825: FROM pqp_vehicle_repository_f pvr
1826: ,pqp_vehicle_allocations_f pva
1827: WHERE pva.vehicle_repository_id=pvr.vehicle_repository_id
1828: AND pvr.vehicle_ownership=p_ownership
1829: AND pva.usage_type=p_usage_type
1837:
1838: CURSOR c_chk_active (cp_registration_number VARCHAR2)
1839: IS
1840: SELECT pvr.vehicle_status,'Start_Date' clm_date
1841: FROM pqp_vehicle_repository_f pvr
1842: WHERE pvr.registration_number=cp_registration_number
1843: AND pvr.vehicle_status='I'
1844: AND p_start_date BETWEEN pvr.effective_start_date
1845: AND pvr.effective_end_date
1844: AND p_start_date BETWEEN pvr.effective_start_date
1845: AND pvr.effective_end_date
1846: UNION
1847: SELECT pvr.vehicle_status,'End_Date' clm_date
1848: FROM pqp_vehicle_repository_f pvr
1849: WHERE pvr.registration_number=cp_registration_number
1850: AND pvr.vehicle_status='I'
1851: AND p_end_date BETWEEN pvr.effective_start_date
1852: AND pvr.effective_end_date;
1855: IS
1856: SELECT 'Start_Valid' valid_date
1857: FROM pqp_vehicle_allocations_f pva
1858: ,per_all_assignments_f paa
1859: ,pqp_vehicle_repository_f pvr
1860: WHERE pva.assignment_id=p_assignment_id
1861: AND pvr.registration_number=cp_registration_number
1862: AND pvr.vehicle_repository_id=pva.vehicle_repository_id
1863: AND pva.business_group_id=p_business_group_id
1873: UNION
1874: SELECT 'End_Valid' valid_date
1875: FROM pqp_vehicle_allocations_f pva
1876: ,per_all_assignments_f paa
1877: ,pqp_vehicle_repository_f pvr
1878: WHERE pva.assignment_id=p_assignment_id
1879: AND pvr.registration_number=cp_registration_number
1880: AND pvr.vehicle_repository_id=pva.vehicle_repository_id
1881: AND pva.business_group_id=p_business_group_id
1966: ,pvr.vehicle_ownership
1967: ,pvr.fiscal_ratings
1968: ,pvr.engine_capacity_in_cc
1969: ,pvr.fuel_type
1970: FROM pqp_vehicle_repository_f pvr
1971: WHERE pvr.registration_number = p_registration_number
1972: AND pvr.business_group_id = p_business_group_id
1973: AND p_effective_date BETWEEN pvr.effective_start_date
1974: AND pvr.effective_end_date;
1979: ,pvr.vehicle_ownership
1980: ,pvr.fiscal_ratings
1981: ,pvr.engine_capacity_in_cc
1982: ,pvr.fuel_type
1983: FROM pqp_vehicle_repository_f pvr
1984: ,pqp_vehicle_allocations_f pva
1985: WHERE (pvr.registration_number =p_registration_number
1986: OR p_registration_number IS NULL)
1987: AND pva.assignment_id= p_assignment_id
2157: ,cp_start_date VARCHAR2
2158: )
2159: IS
2160: SELECT 'X' exst
2161: FROM pqp_vehicle_repository_f pvr
2162: WHERE pvr.registration_number=cp_reg_num
2163: AND pvr.business_group_id =cp_business_group_id
2164: AND TO_DATE(cp_start_date,'YYYY/MM/DD') BETWEEN
2165: pvr.effective_start_date AND
2172: )
2173: IS
2174: SELECT DISTINCT pvr.vehicle_ownership ownership
2175: FROM pqp_vehicle_allocations_f pva,
2176: pqp_vehicle_repository_f pvr
2177: WHERE pva.assignment_id =cp_assignment_id
2178: AND pva.business_group_id =cp_business_group_id
2179: AND pva.vehicle_repository_id=pvr.vehicle_repository_id
2180: AND pva.business_group_id=pvr.business_group_id
2578:
2579: CURSOR c_get_meaning_vt
2580: IS
2581: select hl.meaning
2582: from pqp_vehicle_repository_f pvr,
2583: hr_lookups hl
2584: where pvr.registration_number = p_code
2585: and hl.lookup_type = 'PQP_VEHICLE_TYPE'
2586: and hl.enabled_flag = 'Y'