[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
1835: CURSOR c_get_reg_num
1836: IS
1837: SELECT pvr.registration_number
1838: ,pva.default_vehicle
1839: FROM pqp_vehicle_repository_f pvr
1840: ,pqp_vehicle_allocations_f pva
1841: WHERE pva.vehicle_repository_id=pvr.vehicle_repository_id
1842: AND pvr.vehicle_ownership=p_ownership
1843: AND pva.usage_type=p_usage_type
1851:
1852: CURSOR c_chk_active (cp_registration_number VARCHAR2)
1853: IS
1854: SELECT pvr.vehicle_status,'Start_Date' clm_date
1855: FROM pqp_vehicle_repository_f pvr
1856: WHERE pvr.registration_number=cp_registration_number
1857: AND pvr.vehicle_status='I'
1858: AND p_start_date BETWEEN pvr.effective_start_date
1859: AND pvr.effective_end_date
1858: AND p_start_date BETWEEN pvr.effective_start_date
1859: AND pvr.effective_end_date
1860: UNION
1861: SELECT pvr.vehicle_status,'End_Date' clm_date
1862: FROM pqp_vehicle_repository_f pvr
1863: WHERE pvr.registration_number=cp_registration_number
1864: AND pvr.vehicle_status='I'
1865: AND p_end_date BETWEEN pvr.effective_start_date
1866: AND pvr.effective_end_date;
1869: IS
1870: SELECT 'Start_Valid' valid_date
1871: FROM pqp_vehicle_allocations_f pva
1872: ,per_all_assignments_f paa
1873: ,pqp_vehicle_repository_f pvr
1874: WHERE pva.assignment_id=p_assignment_id
1875: AND pvr.registration_number=cp_registration_number
1876: AND pvr.vehicle_repository_id=pva.vehicle_repository_id
1877: AND pva.business_group_id=p_business_group_id
1887: UNION
1888: SELECT 'End_Valid' valid_date
1889: FROM pqp_vehicle_allocations_f pva
1890: ,per_all_assignments_f paa
1891: ,pqp_vehicle_repository_f pvr
1892: WHERE pva.assignment_id=p_assignment_id
1893: AND pvr.registration_number=cp_registration_number
1894: AND pvr.vehicle_repository_id=pva.vehicle_repository_id
1895: AND pva.business_group_id=p_business_group_id
1980: ,pvr.vehicle_ownership
1981: ,pvr.fiscal_ratings
1982: ,pvr.engine_capacity_in_cc
1983: ,pvr.fuel_type
1984: FROM pqp_vehicle_repository_f pvr
1985: WHERE pvr.registration_number = p_registration_number
1986: AND pvr.business_group_id = p_business_group_id
1987: AND p_effective_date BETWEEN pvr.effective_start_date
1988: AND pvr.effective_end_date;
1993: ,pvr.vehicle_ownership
1994: ,pvr.fiscal_ratings
1995: ,pvr.engine_capacity_in_cc
1996: ,pvr.fuel_type
1997: FROM pqp_vehicle_repository_f pvr
1998: ,pqp_vehicle_allocations_f pva
1999: WHERE (pvr.registration_number =p_registration_number
2000: OR p_registration_number IS NULL)
2001: AND pva.assignment_id= p_assignment_id
2171: ,cp_start_date VARCHAR2
2172: )
2173: IS
2174: SELECT 'X' exst
2175: FROM pqp_vehicle_repository_f pvr
2176: WHERE pvr.registration_number=cp_reg_num
2177: AND pvr.business_group_id =cp_business_group_id
2178: AND TO_DATE(cp_start_date,'YYYY/MM/DD') BETWEEN
2179: pvr.effective_start_date AND
2186: )
2187: IS
2188: SELECT DISTINCT pvr.vehicle_ownership ownership
2189: FROM pqp_vehicle_allocations_f pva,
2190: pqp_vehicle_repository_f pvr
2191: WHERE pva.assignment_id =cp_assignment_id
2192: AND pva.business_group_id =cp_business_group_id
2193: AND pva.vehicle_repository_id=pvr.vehicle_repository_id
2194: AND pva.business_group_id=pvr.business_group_id
2592:
2593: CURSOR c_get_meaning_vt
2594: IS
2595: select hl.meaning
2596: from pqp_vehicle_repository_f pvr,
2597: hr_lookups hl
2598: where pvr.registration_number = p_code
2599: and hl.lookup_type = 'PQP_VEHICLE_TYPE'
2600: and hl.enabled_flag = 'Y'