[Home] [Help]
27: OR va.effective_end_date BETWEEN g_start_date AND g_end_date)
28: OR (g_start_date BETWEEN va.effective_start_date AND va.effective_end_date
29: AND g_end_date BETWEEN va.effective_start_date AND va.effective_end_date) )
30: AND 'C' = (SELECT vr.vehicle_type
31: FROM pqp_vehicle_repository_f vr
32: WHERE vr.vehicle_repository_id =
33: va.vehicle_repository_id
34: AND vr.vehicle_ownership = 'C'
35: AND va.effective_start_date
44: FROM pay_all_payrolls_f papf,
45: hr_soft_coding_keyflex sck,
46: per_all_assignments_f paaf,
47: per_all_assignments_f paaf2,
48: Pqp_vehicle_repository_f vr,
49: Pqp_vehicle_allocations_f va
50: WHERE paaf2.assignment_id = p_assignment_id
51: AND paaf2.person_id = paaf.person_id
52: AND p_eff_date
84: --
85: CURSOR c_primary_car(p_assignment_id IN NUMBER, p_eff_date IN DATE) IS
86: SELECT va.vehicle_allocation_id,
87: vr.vehicle_repository_id
88: FROM Pqp_vehicle_repository_f vr,
89: Pqp_vehicle_allocations_f va
90: WHERE va.assignment_id = p_assignment_id
91: AND p_eff_date
92: BETWEEN va.effective_start_date
111: vr.model,
112: vr.engine_capacity_in_cc,
113: va.effective_end_date
114: FROM Pqp_vehicle_allocations_f va,
115: pqp_vehicle_repository_f vr
116: WHERE va.assignment_id = p_assignment_id
117: AND va.effective_end_date
118: BETWEEN (p_new_car_start_date - 30)
119: AND (p_new_car_start_date - 1)
144: vr.model,
145: vr.engine_capacity_in_cc,
146: va.effective_end_date
147: FROM Pqp_vehicle_allocations_f va,
148: pqp_vehicle_repository_f vr
149: WHERE va.assignment_id = p_assignment_id
150: AND va.effective_start_date
151: BETWEEN (p_withdrawn_car_end_date + 1)
152: AND least((p_withdrawn_car_end_date + 30), g_end_date)
173: p_vehicle_repository_id IN NUMBER) IS
174: SELECT effective_start_date,
175: effective_end_date,
176: h1.description fuel_type
177: FROM pqp_vehicle_repository_f vr1,
178: hr_lookups h1
179: WHERE vr1.vehicle_repository_id = p_vehicle_repository_id
180: AND vr1.effective_start_date BETWEEN g_start_date AND g_end_date
181: AND vr1.fuel_type = h1.lookup_code
182: AND h1.lookup_type = 'PQP_FUEL_TYPE'
183: AND h1.enabled_flag = 'Y'
184: AND trunc(sysdate) BETWEEN trunc(nvl(h1.start_date_active, sysdate-1)) AND trunc(nvl(h1.end_date_active,sysdate+1))
185: AND EXISTS (SELECT 1
186: FROM pqp_vehicle_repository_f vr2,
187: hr_lookups h2
188: WHERE vr2.vehicle_repository_id =
189: p_vehicle_repository_id
190: AND vr2.effective_end_date =
216: p_vehicle_repository_id IN NUMBER) IS
217: SELECT effective_start_date,
218: effective_end_date,
219: h1.description fuel_type
220: FROM pqp_vehicle_repository_f vr1,
221: hr_lookups h1
222: WHERE vr1.vehicle_repository_id = p_vehicle_repository_id
223: AND vr1.effective_start_date BETWEEN g_start_date AND g_end_date
224: AND vr1.fuel_type = h1.lookup_code
225: AND h1.lookup_type = 'PQP_FUEL_TYPE'
226: AND h1.enabled_flag = 'Y'
227: AND trunc(sysdate) BETWEEN trunc(nvl(h1.start_date_active, sysdate-1)) AND trunc(nvl(h1.end_date_active,sysdate+1))
228: AND EXISTS (SELECT 1
229: FROM pqp_vehicle_repository_f vr2
230: WHERE vr2.vehicle_repository_id =
231: p_vehicle_repository_id
232: AND vr2.effective_end_date =
233: vr1.effective_start_date-1
240: va.vehicle_repository_id,
241: va.usage_type usage_type,
242: va.effective_start_date,
243: va.effective_end_date
244: FROM Pqp_vehicle_repository_f vr,
245: Pqp_vehicle_allocations_f va
246: WHERE va.assignment_id = p_assignment_id
247: AND p_eff_date BETWEEN va.effective_start_date AND va.effective_end_date
248: AND va.vehicle_repository_id = vr.vehicle_repository_id
848: FROM hr_soft_coding_keyflex flex,
849: per_all_assignments_f asg,
850: pay_payrolls_f ppf,
851: pqp_vehicle_allocations_f va,
852: pqp_vehicle_repository_f vr
853: WHERE asg.person_id BETWEEN stperson AND endperson
854: AND asg.business_group_id = g_business_group_id
855: AND asg.payroll_id = nvl(g_payroll_id,asg.payroll_id)
856: AND asg.payroll_id = ppf.payroll_id
1313: FROM hr_soft_coding_keyflex flex,
1314: per_all_assignments_f asg,
1315: pay_payrolls_f ppf,
1316: pqp_vehicle_allocations_f va,
1317: pqp_vehicle_repository_f vr
1318: WHERE asg.person_id BETWEEN stperson AND endperson
1319: AND asg.business_group_id = g_business_group_id
1320: AND asg.payroll_id = nvl(g_payroll_id,asg.payroll_id)
1321: AND asg.payroll_id = ppf.payroll_id
1847: FROM hr_soft_coding_keyflex flex,
1848: per_all_assignments_f asg,
1849: pay_payrolls_f ppf,
1850: pqp_vehicle_allocations_f va,
1851: pqp_vehicle_repository_f vr
1852: WHERE asg.person_id BETWEEN stperson AND endperson
1853: AND asg.business_group_id = g_business_group_id
1854: AND asg.payroll_id = nvl(g_payroll_id,asg.payroll_id)
1855: AND asg.payroll_id = ppf.payroll_id
2387: FROM hr_soft_coding_keyflex flex,
2388: per_all_assignments_f asg,
2389: pay_payrolls_f ppf,
2390: pqp_vehicle_allocations_f va,
2391: pqp_vehicle_repository_f vr
2392: WHERE asg.person_id BETWEEN stperson AND endperson
2393: AND asg.business_group_id = g_business_group_id
2394: AND asg.payroll_id = nvl(g_payroll_id,asg.payroll_id)
2395: AND asg.payroll_id = ppf.payroll_id
2996: fiscal_ratings_uom,
2997: shared_vehicle,
2998: vehicle_status,
2999: taxation_method
3000: FROM pqp_vehicle_repository_f,
3001: hr_lookups h1
3002: WHERE vehicle_repository_id = p_vehicle_repository_id
3003: AND l_eff_date BETWEEN effective_start_date AND effective_end_Date
3004: AND fuel_type = h1.lookup_code