1 PACKAGE BODY csr_scheduler_pvt AS
2 /* $Header: CSRVSCHB.pls 120.19 2008/05/30 13:29:04 venjayar ship $ */
3
4 g_pkg_name CONSTANT VARCHAR2(30) := 'CSR_SCHEDULER_PVT';
5 g_csr_appl_id CONSTANT NUMBER := 698;
6 g_conc_program_name CONSTANT VARCHAR2(30) := 'SEARCH_AND_SCHEDULE_AUTO';
7 g_likelihood CONSTANT NUMBER := fnd_profile.VALUE('CSF_DEFAULT_SPARES_AVAIL');
8 g_auto_contract_flag CONSTANT VARCHAR2(1) := NVL(fnd_profile.value('CSR:CSR_AUTO_CONTRACTS'), 'N');
9 g_auto_ib_flag CONSTANT VARCHAR2(1) := NVL(fnd_profile.value('CSR:CSR_AUTO_INSTALLED_BASE'), 'N');
10 g_auto_skills_flag CONSTANT VARCHAR2(1) := NVL(fnd_profile.value('CSR:CSR_AUTO_SKILLS'), 'Y');
11 g_auto_terr_flag CONSTANT VARCHAR2(1) := NVL(fnd_profile.value('CSR:CSR_AUTO_TERRITORIES'), 'Y');
12
13 TYPE ref_cursor_type IS REF CURSOR;
14
15
16 FUNCTION valid_argument(p_arg_value VARCHAR2, p_arg_name VARCHAR2, p_api_name VARCHAR2)
17 RETURN BOOLEAN IS
18 BEGIN
19 IF p_arg_value IS NULL THEN
20 fnd_message.set_name('CSR', 'CSR_MANDATORY_FIELD_MISSING');
21 fnd_message.set_token('FIELD', p_arg_name);
22 fnd_message.set_token('TASK', p_api_name);
23 fnd_msg_pub.ADD;
24 RETURN FALSE;
25 END IF;
26 RETURN TRUE;
27 END valid_argument;
28
29
30 /**
31 * Gets the Geocode of a Task.
32 *
33 * The API returns the Geocoded Geometry of the Task if the given Task has an address.
34 * If the Task is not yet Geocoded, then tries to resolve the Address of the Task by
35 * calling Location Finder.
36 *
37 * The Geocode is returned by stamping the values in the respective output parameters
38 * rather than as MDSYS.SDO_GEOMETRY itself because JDBC doesnt support PLSQL Record
39 * Types as of now.
40 *
41 * @param p_api_version API Version (1.0)
42 * @param p_init_msg_list Initialize Message List
43 * @param p_commit Commits the Work
44 * @param x_return_status Return Status of the Procedure.
45 * @param x_msg_count Number of Messages in the Stack.
46 * @param x_msg_data Stack of Error Messages.
47 * @param p_task_id Task ID
48 * @param x_locus_segment_id Segment ID of the Road in the Address's Geocode
49 * @param x_locus_side Side of the Road in the Address's Geocode
50 * @param x_locus_spot Offset in the Road in the Address's Geocode
51 * @param x_locus_lat Longitude of the Location in the Address's Geocode
52 * @param x_locus_lon Latitude of the Location in the Address's Geocode
53 * @param x_should_call_lf Should LF be called on the Address or not
54 * @param x_location_id Location ID of the Address.
55 * @param x_srid Coordinate system id.
56 */
57 PROCEDURE get_geometry(
58 p_api_version IN NUMBER
59 , p_init_msg_list IN VARCHAR2
60 , p_commit IN VARCHAR2
61 , x_return_status OUT NOCOPY VARCHAR2
62 , x_msg_count OUT NOCOPY NUMBER
63 , x_msg_data OUT NOCOPY VARCHAR2
64 , p_task_id IN NUMBER
65 , x_locus_segment_id OUT NOCOPY NUMBER
66 , x_locus_side OUT NOCOPY NUMBER
67 , x_locus_spot OUT NOCOPY NUMBER
68 , x_locus_lat OUT NOCOPY NUMBER
69 , x_locus_lon OUT NOCOPY NUMBER
70 , x_should_call_lf OUT NOCOPY VARCHAR2
71 , x_location_id OUT NOCOPY NUMBER
72 , x_srid OUT NOCOPY NUMBER
73 ) IS
74 l_api_name CONSTANT VARCHAR2(30) := 'GET_GEOMETRY';
75 l_api_version CONSTANT NUMBER := 1.0;
76
77 CURSOR c_address_info IS
78 SELECT l.location_id
79 , l.geometry
80 , l.house_number
81 , l.address1
82 , l.address2
83 , l.address3
84 , l.address4
85 , l.city
86 , l.state
87 , l.postal_code
88 , fnd.territory_short_name country
89 , l.country country_code
90 FROM jtf_tasks_b t
91 , hz_locations l
92 , fnd_territories_vl fnd
93 WHERE t.task_id = p_task_id
94 AND l.location_id = csf_tasks_pub.get_task_location_id(t.task_id, t.address_id, t.location_id)
95 AND fnd.territory_code = l.country;
96
97 l_address_info c_address_info%ROWTYPE;
98 l_locus_valid VARCHAR2(6);
99 BEGIN
100 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
101 RAISE fnd_api.g_exc_unexpected_error;
102 END IF;
103
104 IF fnd_api.to_boolean(p_init_msg_list) THEN
105 fnd_msg_pub.initialize;
106 END IF;
107
108 x_return_status := fnd_api.g_ret_sts_success;
109
110 OPEN c_address_info;
111 FETCH c_address_info INTO l_address_info;
112 CLOSE c_address_info;
113
114 -- The Task has no Address Information.
115 IF l_address_info.location_id IS NULL THEN
116 -- Need to throw with proper error message
117 RAISE fnd_api.g_exc_error;
118 END IF;
119
120 IF l_address_info.geometry IS NULL THEN
121 l_locus_valid := 'FALSE';
122 ELSE
123 csf_locus_pub.verify_locus(
124 p_api_version => 1
125 , x_msg_count => x_msg_count
126 , x_msg_data => x_msg_data
127 , x_return_status => x_return_status
128 , p_locus => l_address_info.geometry
129 , x_result => l_locus_valid
130 );
131 END IF;
132
133 -- Geocode of the Task is invalid. Try to Geocode the Task
134 IF l_locus_valid = 'FALSE' THEN
135 csf_resource_address_pvt.resolve_address(
136 p_api_version => 1
137 , p_init_msg_list => fnd_api.g_false
138 , x_return_status => x_return_status
139 , x_msg_count => x_msg_count
140 , x_msg_data => x_msg_data
141 , p_location_id => l_address_info.location_id
142 , p_building_num => l_address_info.house_number
143 , p_address1 => l_address_info.address1
144 , p_address2 => l_address_info.address2
145 , p_address3 => l_address_info.address3
146 , p_address4 => l_address_info.address4
147 , p_state => l_address_info.state
148 , p_city => l_address_info.city
149 , p_postalcode => l_address_info.postal_code
150 , p_country => l_address_info.country
151 , p_country_code => l_address_info.country_code
152 , x_geometry => l_address_info.geometry
153 );
154 IF x_return_status = fnd_api.g_ret_sts_error THEN
155 RAISE fnd_api.g_exc_error;
156 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
157 RAISE fnd_api.g_exc_unexpected_error;
158 END IF;
159 END IF;
160
161 IF x_return_status = fnd_api.g_ret_sts_success AND l_address_info.geometry IS NOT NULL THEN
162 x_locus_segment_id := csf_locus_pub.get_locus_segmentid(l_address_info.geometry);
163 x_locus_side := csf_locus_pub.get_locus_side(l_address_info.geometry);
164 x_locus_spot := csf_locus_pub.get_locus_spot(l_address_info.geometry);
165 x_locus_lat := csf_locus_pub.get_locus_lat(l_address_info.geometry);
166 x_locus_lon := csf_locus_pub.get_locus_lon(l_address_info.geometry);
167 x_should_call_lf := csf_locus_pub.should_call_lf(l_address_info.geometry);
168 x_location_id := l_address_info.location_id;
169 x_srid := csf_locus_pub.get_locus_srid(l_address_info.geometry);
170
171 IF fnd_api.to_boolean(p_commit) THEN
172 COMMIT;
173 END IF;
174 END IF;
175 EXCEPTION
176 WHEN fnd_api.g_exc_error THEN
177 x_return_status := fnd_api.g_ret_sts_error;
178 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
179 WHEN fnd_api.g_exc_unexpected_error THEN
180 x_return_status := fnd_api.g_ret_sts_unexp_error;
181 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
182 WHEN OTHERS THEN
183 x_return_status := fnd_api.g_ret_sts_unexp_error;
184 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
185 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
186 END IF;
187 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
188 END get_geometry;
189
190 /**
191 * Gets the Server Timezone configured in the E-Business Suite
192 *
193 * Gets the value of the profile SERVER_TIMEZONE_ID. Apparently SERVER_TIMEZONE_ID
194 * has been replaced by the profile SERVER_TIMEZONE. So if the former profile
195 * doesnt return any value, the API will try to return value of SERVER_TIMEZONE_ID.
196 *
197 * If either of the profiles doesnt return any value, then it returns the first
198 * profile satisfying the condition of being in GMT-8 Offset and also having
199 * Day Light Savings as Yes from HZ_TIMEZONES.
200 *
201 * @return Server Timezone ID (or Zero upon any exception).
202 */
203 FUNCTION get_server_timezone RETURN NUMBER IS
204 l_timezone_id NUMBER;
205 BEGIN
206 fnd_profile.get('SERVER_TIMEZONE', l_timezone_id);
207 IF l_timezone_id IS NULL THEN
208 fnd_profile.get('SERVER_TIMEZONE_ID', l_timezone_id);
209 END IF;
210
211 IF l_timezone_id IS NULL THEN
212 SELECT MIN(timezone_id)
213 INTO l_timezone_id
214 FROM hz_timezones
215 WHERE gmt_deviation_hours = -8
216 AND daylight_savings_time_flag = 'Y';
217 END IF;
218
219 RETURN l_timezone_id;
220 EXCEPTION
221 WHEN OTHERS THEN
222 RETURN 0;
223 END;
224
225 /**
226 * Returns the Time Zone Difference between the given Timezone
227 * and the Server Timezone in Half Hour Units
228 *
229 * @param p_timezone_id Timezone Identifier
230 * @return Difference in Half Hour Units
231 */
232 FUNCTION get_server_timezone_offset(p_timezone_id IN NUMBER)
233 RETURN NUMBER IS
234 l_offset_days NUMBER;
235 l_server_date DATE;
236 BEGIN
237 l_server_date := hz_timezone_pub.convert_datetime(
238 p_source_tz_id => p_timezone_id
239 , p_dest_tz_id => get_server_timezone
240 , p_source_day_time => SYSDATE
241 );
242 l_offset_days := l_server_date - SYSDATE;
243 RETURN TRUNC(l_offset_days * 48); -- Output in half hours.
244 END get_server_timezone_offset;
245
246
247 /**
248 * Gets the Timezone corresponding to the given Address.
249 *
250 */
251 FUNCTION get_timezone(p_address_id IN NUMBER) RETURN NUMBER IS
252 CURSOR c_timezone IS
253 SELECT loc.timezone_id
254 FROM hz_party_sites par
255 , hz_locations loc
256 WHERE par.party_site_id = p_address_id
257 AND par.location_id = loc.location_id;
258
259 l_timezone c_timezone%ROWTYPE;
260 l_profile_value VARCHAR2(50);
261 BEGIN
262 IF p_address_id IS NULL THEN
263 RETURN get_server_timezone;
264 ELSE
265 OPEN c_timezone;
266 FETCH c_timezone INTO l_timezone;
267 IF c_timezone%NOTFOUND THEN
268 CLOSE c_timezone;
269 RETURN get_server_timezone;
270 END IF;
271 CLOSE c_timezone;
272 END IF;
273 RETURN l_timezone.timezone_id;
274 END get_timezone;
275
276 /**
277 * Converts the given Time from the given Timezone to Server Timezone.
278 *
279 * Calls GET_SERVER_TIMEZONE to get the Server Timezone Identifier.
280 * Note that only the Time Component of the given Time is taken and not the
281 * Date Component. The returned date has SYSDATE as the Date Component.
282 *
283 * Generally used by WTP to create the Window Slots properly converted from Task
284 * Timezone to Server Timezone.
285 *
286 * @param p_time Time to be converted to Server Timezone
287 * @param p_timezone_id Source Timezone Identifier
288 * @return Time converted to Server Timezone.
289 */
290 FUNCTION convert_timezone(p_time IN DATE, p_timezone_id IN NUMBER)
291 RETURN DATE IS
292 l_return_status VARCHAR2(1);
293 l_msg_count NUMBER;
294 l_msg_data VARCHAR2(2000);
295 l_server_timezone_id NUMBER;
296 l_src_datetime DATE;
297 l_dest_datetime DATE;
298 BEGIN
299 l_src_datetime := TO_DATE(TO_CHAR( TRUNC(SYSDATE), 'DD-MM-YYYY') || TO_CHAR(p_time, 'HH24:MI')
300 , 'DD-MM-YYYY HH24:MI'
301 );
302
303 hz_timezone_pub.get_time(p_api_version => 1
304 , p_init_msg_list => fnd_api.g_false
305 , x_return_status => l_return_status
306 , x_msg_count => l_msg_count
307 , x_msg_data => l_msg_data
308 , p_source_tz_id => p_timezone_id
309 , p_dest_tz_id => get_server_timezone
310 , p_source_day_time => l_src_datetime
311 , x_dest_day_time => l_dest_datetime
312 );
313 l_dest_datetime := TO_DATE( TO_CHAR(TRUNC(SYSDATE), 'DD-MM-YYYY') || TO_CHAR(l_dest_datetime, 'HH24:MI')
314 , 'DD-MM-YYYY HH24:MI'
315 );
316 RETURN l_dest_datetime;
317 END convert_timezone;
318
319 /**
320 * This API Creates a scheduler search request
321
322 * @param p_api_version API Version (1.0)
323 * @param p_init_msg_list Initialize Message List
324 * @param p_commit Commits the Work. This is set as true to allow Scheduler to see the new request created.
325 * @param x_return_status Return Status of the Procedure.
326 * @param x_msg_count Number of Messages in the Stack.
327 * @param x_msg_data Stack of Error Messages.
328 * @param p_task_id The task id for which the search request is being created.
329 * @param The following set of parameters represents the qualifiers of resoucres for the task.
330 1) p_contracts_flag
331 2) p_ib_flag
332 3) p_territory_flag
333 4) p_skill_flag
334 * @param p_resource_tbl The set of resource to be considered for scheduling irrespective of their qualification.
335 * @param p_request_name Type of request to be created
336 One of the following request types are created
337 1) SearchAssistedOptions
338 2) SearchIntelligentOptions
339 3) SearchWTPOptions
340 * @param p_spares_likelihood Spares Likelihood to be used for this request
341 * @param p_route_based_flag Flag to enable route based scheduling for this request
342 * @param p_disabled_access_hours_flag Activate access hours if any assigned for the task.
343 * @param x_request_id Request id of created request
344
345 * The API first finds the set of qualified resoucres for the given task
346 * based on the qualifers given.
347 * It then creates appropriate serach request based on the request name
348 */
349 PROCEDURE create_search_request(
350 p_api_version IN NUMBER
351 , p_init_msg_list IN VARCHAR2
352 , p_commit IN VARCHAR2
353 , x_return_status OUT NOCOPY VARCHAR2
354 , x_msg_count OUT NOCOPY NUMBER
355 , x_msg_data OUT NOCOPY VARCHAR2
356 , p_request_name IN VARCHAR2
357 , p_task_id IN NUMBER
358 , p_contracts_flag IN VARCHAR2
359 , p_ib_flag IN VARCHAR2
360 , p_territory_flag IN VARCHAR2
361 , p_skill_flag IN VARCHAR2
362 , p_resource_id_tbl IN jtf_number_table
363 , p_resource_type_tbl IN jtf_varchar2_table_100
364 , p_spares_likelihood IN NUMBER
365 , p_route_based_flag IN VARCHAR2
366 , p_disabled_access_hours_flag IN VARCHAR2
367 , x_request_id OUT NOCOPY NUMBER
368 ) IS
369 l_api_version CONSTANT NUMBER := 1.0;
370 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_SEARCH_REQUEST';
371
372 l_res_qualifiers csf_resource_pub.resource_qualifier_tbl_type;
373 l_res_tbl csf_requests_pvt.resource_tbl_type;
374 j PLS_INTEGER;
375 k PLS_INTEGER;
376
377 CURSOR c_task_info IS
378 SELECT planned_start_date
379 , planned_end_date
380 , source_object_id
381 FROM jtf_tasks_b
382 WHERE task_id = p_task_id;
383
384 l_task_info c_task_info%ROWTYPE;
385
386 BEGIN
387 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
388 RAISE fnd_api.g_exc_unexpected_error;
389 END IF;
390
391 IF fnd_api.to_boolean(p_init_msg_list) THEN
392 fnd_msg_pub.initialize;
393 END IF;
394
395 x_return_status := fnd_api.g_ret_sts_success;
396
397 -- Find the qualified resoucres for the task based on the given resoucer qualifiers
398
399 OPEN c_task_info;
400 FETCH c_task_info INTO l_task_info;
401 IF c_task_info%NOTFOUND THEN
402 CLOSE c_task_info;
403 RAISE NO_DATA_FOUND;
404 END IF;
405 CLOSE c_task_info;
406
407 -- Validate the Planned Window (same logic as that done in DC)
408 csf_tasks_pub.validate_planned_dates(l_task_info.planned_start_date, l_task_info.planned_end_date);
409
410 csf_resource_pub.get_resources_to_schedule(
411 p_api_version => 1
412 , p_init_msg_list => fnd_api.g_false
413 , x_return_status => x_return_status
414 , x_msg_count => x_msg_count
415 , x_msg_data => x_msg_data
416 , p_scheduling_mode => 'X' -- Auto Assign mode
417 , p_task_id => p_task_id
418 , p_incident_id => l_task_info.source_object_id
419 , p_start => l_task_info.planned_start_date
420 , p_end => l_task_info.planned_end_date
421 , p_contracts_flag => p_contracts_flag
422 , p_ib_flag => p_ib_flag
423 , p_territory_flag => p_territory_flag
424 , p_skill_flag => p_skill_flag
425 , p_res_qualifier_tbl => l_res_qualifiers
426 , p_suggested_res_id_tbl => p_resource_id_tbl
427 , p_suggested_res_type_tbl => p_resource_type_tbl
428 , x_res_tbl => l_res_tbl
429 );
430
431 -- Unless it is an Unexpected Error... continue even with
432 -- "No Resources Found" error. Java will do the proper error handling.
433 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
434 RAISE fnd_api.g_exc_unexpected_error;
435 END IF;
436
437 -- Create Scheduler Search Request based on the given information.
438 csf_requests_pvt.create_scheduler_request(
439 p_api_version => 1
440 , p_init_msg_list => fnd_api.g_false
441 , p_commit => fnd_api.g_false
442 , x_return_status => x_return_status
443 , x_msg_count => x_msg_count
444 , x_msg_data => x_msg_data
445 , p_name => p_request_name
446 , p_object_id => p_task_id
447 , p_spares_likelihood => p_spares_likelihood
448 , p_resource_tbl => l_res_tbl
449 , p_route_based_flag => p_route_based_flag
450 , p_disabled_access_hours_flag => p_disabled_access_hours_flag
451 , x_request_id => x_request_id
452 );
453
454 IF x_return_status = fnd_api.g_ret_sts_error THEN
455 RAISE fnd_api.g_exc_error;
456 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
457 RAISE fnd_api.g_exc_unexpected_error;
458 END IF;
459
460 IF fnd_api.to_boolean(p_commit) THEN
461 COMMIT;
462 END IF;
463
464 EXCEPTION
465 WHEN fnd_api.g_exc_error THEN
466 x_return_status := fnd_api.g_ret_sts_error;
467 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
468 WHEN fnd_api.g_exc_unexpected_error THEN
469 x_return_status := fnd_api.g_ret_sts_unexp_error;
470 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
471 WHEN OTHERS THEN
472 x_return_status := fnd_api.g_ret_sts_unexp_error;
473 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
474 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
475 END IF;
476 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
477 END create_search_request;
478
479 /**
480 * This API creates a request of type 'ScheduleOption'
481
482 * @param p_api_version API Version (1.0)
483 * @param p_init_msg_list Initialize Message List
484 * @param p_commit Commits the Work. This is set as true to allow Scheduler to see the new request created.
485 * @param x_return_status Return Status of the Procedure.
486 * @param x_msg_count Number of Messages in the Stack.
487 * @param x_msg_data Stack of Error Messages.
488 * @param p_plan_option_id The id of the plan option to be scheduled
489 * @param p_target_status_id The status to which the task has to be changed after scheduling
490 * @param p_set_plan_task_confirmed Flag to indicate that customer confirmation has to be considered for the task.
491 * @param x_request_id Request id of created request
492
493 * This API creates a scheduler request of type 'ScheduleOption'
494 * that is used to schedule the given plan option
495 */
496 PROCEDURE create_schedule_option_request(
497 p_api_version IN NUMBER
498 , p_init_msg_list IN VARCHAR2
499 , p_commit IN VARCHAR2
500 , x_return_status OUT NOCOPY VARCHAR2
501 , x_msg_count OUT NOCOPY NUMBER
502 , x_msg_data OUT NOCOPY VARCHAR2
503 , p_plan_option_id IN NUMBER
504 , p_target_status_id IN NUMBER
505 , p_set_plan_task_confirmed IN VARCHAR2
506 , x_request_id OUT NOCOPY NUMBER
507 ) IS
508 l_api_version CONSTANT NUMBER := 1.0;
509 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_SCHEDULE_OPTION_REQUEST';
510 BEGIN
511 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
512 RAISE fnd_api.g_exc_unexpected_error;
513 END IF;
514
515 IF fnd_api.to_boolean(p_init_msg_list) THEN
516 fnd_msg_pub.initialize;
517 END IF;
518
519 x_return_status := fnd_api.g_ret_sts_success;
520
521 csf_requests_pvt.create_scheduler_request(
522 p_api_version => 1
523 , x_return_status => x_return_status
524 , x_msg_count => x_msg_count
525 , x_msg_data => x_msg_data
526 , p_name => 'ScheduleOption'
527 , p_object_id => p_plan_option_id
528 , p_status_id => p_target_status_id
529 , p_set_plan_task_confirmed => p_set_plan_task_confirmed
530 , x_request_id => x_request_id
531 );
532
533 IF x_return_status = fnd_api.g_ret_sts_error THEN
534 RAISE fnd_api.g_exc_error;
535 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
536 RAISE fnd_api.g_exc_unexpected_error;
537 END IF;
538
539 IF fnd_api.to_boolean(p_commit) THEN
540 COMMIT;
541 END IF;
542
543 EXCEPTION
544 WHEN fnd_api.g_exc_error THEN
545 x_return_status := fnd_api.g_ret_sts_error;
546 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
547 WHEN fnd_api.g_exc_unexpected_error THEN
548 x_return_status := fnd_api.g_ret_sts_unexp_error;
549 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
550 WHEN OTHERS THEN
551 x_return_status := fnd_api.g_ret_sts_unexp_error;
552 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
553 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
554 END IF;
555 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
556 END create_schedule_option_request;
557
558 PROCEDURE create_auto_request(
559 x_return_status OUT NOCOPY VARCHAR2
560 , x_msg_count OUT NOCOPY NUMBER
561 , x_msg_data OUT NOCOPY VARCHAR2
562 , p_task_tbl IN csf_requests_pvt.object_tbl_type
563 , p_find_resources IN VARCHAR2 DEFAULT fnd_api.g_true
564 , x_request_id OUT NOCOPY NUMBER
565 ) IS
566 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_AUTO_REQUEST';
567
568 i PLS_INTEGER;
569 l_child_request_id NUMBER;
570 l_res_tbl csf_requests_pvt.resource_tbl_type;
571 l_res_qualifiers csf_resource_pub.resource_qualifier_tbl_type;
572
573 CURSOR c_task_info (p_task_id NUMBER) IS
574 SELECT t.task_id
575 , t.planned_start_date
576 , t.planned_end_date
577 , t.source_object_id
578 FROM jtf_tasks_b t
579 WHERE t.task_id = p_task_id;
580
581 l_task_info c_task_info%ROWTYPE;
582 BEGIN
583 x_return_status := fnd_api.g_ret_sts_success;
584
585
586 IF p_task_tbl IS NULL OR p_task_tbl.COUNT <= 0 THEN
587 x_request_id := -1;
588 RETURN;
589 END IF;
590
591
592 -- Create the SearchAndScheduleAuto Request which will serve as
593 -- the Parent Request for the subsequents requests created for each Task.
594 csf_requests_pvt.create_scheduler_request(
595 p_api_version => 1
596 , p_init_msg_list => fnd_api.g_true
597 , p_commit => fnd_api.g_false
598 , x_return_status => x_return_status
599 , x_msg_count => x_msg_count
600 , x_msg_data => x_msg_data
601 , p_name => 'SearchAndScheduleAuto'
602 , p_object_id => -1
603 , x_request_id => x_request_id
604 );
605
606 IF x_return_status <> fnd_api.g_ret_sts_success THEN
607 RAISE fnd_api.g_exc_error;
608 END IF;
609
610
611 -- For each task, create a SearchAndSchedule Request with Parent
612 -- Request being the Request created above.
613 i := p_task_tbl.FIRST;
614 WHILE i IS NOT NULL LOOP
615 IF p_find_resources = fnd_api.g_true THEN
616
617 OPEN c_task_info(p_task_tbl(i));
618 FETCH c_task_info INTO l_task_info;
619 CLOSE c_task_info;
620
621 -- Validate the Planned Window (same logic as that done in DC)
622 csf_tasks_pub.validate_planned_dates(l_task_info.planned_start_date, l_task_info.planned_end_date);
623
624 -- Get the Resources for this Task.
625 csf_resource_pub.get_resources_to_schedule(
626 p_api_version => 1
627 , p_init_msg_list => fnd_api.g_false
628 , x_return_status => x_return_status
629 , x_msg_count => x_msg_count
630 , x_msg_data => x_msg_data
631 , p_scheduling_mode => 'X' -- Auto Assign mode
632 , p_task_id => p_task_tbl(i)
633 , p_incident_id => l_task_info.source_object_id
634 , p_start => l_task_info.planned_start_date
635 , p_end => l_task_info.planned_end_date
636 , p_contracts_flag => g_auto_contract_flag
637 , p_ib_flag => g_auto_ib_flag
638 , p_territory_flag => g_auto_terr_flag
639 , p_skill_flag => g_auto_skills_flag
640 , p_res_qualifier_tbl => l_res_qualifiers
641 , x_res_tbl => l_res_tbl
642 );
643
644 -- Unless it is an Unexpected Error... continue even with
645 -- "No Resources Found" error. Java will do the proper error handling.
646 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
647 RAISE fnd_api.g_exc_unexpected_error;
648 END IF;
649 END IF;
650
651 csf_requests_pvt.create_scheduler_request(
652 p_api_version => 1
653 , p_init_msg_list => fnd_api.g_false
654 , p_commit => fnd_api.g_false
655 , x_return_status => x_return_status
656 , x_msg_count => x_msg_count
657 , x_msg_data => x_msg_data
658 , p_name => 'SearchAndSchedule'
659 , p_object_id => p_task_tbl(i)
660 , p_spares_likelihood => g_likelihood
661 , p_resource_tbl => l_res_tbl
662 , p_parent_id => x_request_id
663 , x_request_id => l_child_request_id
664 );
665
666 i := p_task_tbl.NEXT(i);
667
668 END LOOP;
669 EXCEPTION
670 WHEN fnd_api.g_exc_error THEN
671 x_return_status := fnd_api.g_ret_sts_error;
672 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
673 WHEN fnd_api.g_exc_unexpected_error THEN
674 x_return_status := fnd_api.g_ret_sts_unexp_error;
675 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
676 WHEN OTHERS THEN
677 x_return_status := fnd_api.g_ret_sts_unexp_error;
678 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
679 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
680 END IF;
681 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
682 END create_auto_request;
683
684 PROCEDURE fill_request_resources(
685 p_api_version IN NUMBER
686 , p_init_msg_list IN VARCHAR2
687 , p_commit IN VARCHAR2
688 , x_return_status OUT NOCOPY VARCHAR2
689 , x_msg_count OUT NOCOPY NUMBER
690 , x_msg_data OUT NOCOPY VARCHAR2
691 , p_request_id IN NUMBER
692 ) IS
693 l_api_version CONSTANT NUMBER := 1.0;
694 l_api_name CONSTANT VARCHAR2(30) := 'FILL_REQUEST_RESOURCES';
695
696 l_res_qualifiers csf_resource_pub.resource_qualifier_tbl_type;
697 l_res_tbl csf_requests_pvt.resource_tbl_type;
698
699 CURSOR c_request_task_list IS
700 SELECT rt.request_task_id
701 , t.task_id
702 , t.source_object_id
703 , t.planned_start_date
704 , t.planned_end_date
705 FROM ( SELECT sched_request_id
706 FROM csf_r_sched_requests
707 WHERE parent_request_id = p_request_id
708 UNION ALL
709 SELECT sched_request_id
710 FROM csf_r_sched_requests
711 WHERE sched_request_id = p_request_id
712 AND NOT EXISTS (SELECT 1
713 FROM csf_r_sched_requests
714 WHERE parent_request_id = p_request_id)
715 ) r
716 , csf_r_request_tasks rt
717 , jtf_tasks_b t
718 WHERE rt.sched_request_id = r.sched_request_id
719 AND NOT EXISTS ( SELECT 1
720 FROM csf_r_resource_results rr
721 WHERE rr.request_task_id = rt.request_task_id
722 )
723 AND t.task_id = rt.task_id;
724
725 BEGIN
726 SAVEPOINT fill_request_resources;
727
728 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
729 RAISE fnd_api.g_exc_unexpected_error;
730 END IF;
731
732 IF fnd_api.to_boolean(p_init_msg_list) THEN
733 fnd_msg_pub.initialize;
734 END IF;
735
736 x_return_status := fnd_api.g_ret_sts_success;
737
738 FOR l_task_rec IN c_request_task_list LOOP
739
740 -- Get the Qualified Resources for this Task.
741 -- Validate the Planned Window (same logic as that done in DC)
742 csf_tasks_pub.validate_planned_dates(l_task_rec.planned_start_date, l_task_rec.planned_end_date);
743
744 -- Get the Resources for this Task.
745 csf_resource_pub.get_resources_to_schedule(
746 p_api_version => 1
747 , p_init_msg_list => fnd_api.g_false
748 , x_return_status => x_return_status
749 , x_msg_count => x_msg_count
750 , x_msg_data => x_msg_data
751 , p_scheduling_mode => 'X' -- Auto Assign mode
752 , p_task_id => l_task_rec.task_id
753 , p_incident_id => l_task_rec.source_object_id
754 , p_start => l_task_rec.planned_start_date
755 , p_end => l_task_rec.planned_end_date
756 , p_contracts_flag => g_auto_contract_flag
757 , p_ib_flag => g_auto_ib_flag
758 , p_territory_flag => g_auto_terr_flag
759 , p_skill_flag => g_auto_skills_flag
760 , p_res_qualifier_tbl => l_res_qualifiers
761 , x_res_tbl => l_res_tbl
762 );
763
764 -- Unless it is an Unexpected Error... continue even with
765 -- "No Resources Found" error. Java will do the proper error handling.
766 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
767 RAISE fnd_api.g_exc_unexpected_error;
768 END IF;
769
770 csf_requests_pvt.create_resource_results(
771 p_api_version => 1
772 , p_init_msg_list => fnd_api.g_false
773 , p_commit => fnd_api.g_false
774 , x_return_status => x_return_status
775 , x_msg_count => x_msg_count
776 , x_msg_data => x_msg_data
777 , p_request_task_id => l_task_rec.request_task_id
778 , p_resource_tbl => l_res_tbl
779 );
780 END LOOP;
781
782 IF fnd_api.to_boolean(p_commit) THEN
783 COMMIT WORK;
784 END IF;
785 EXCEPTION
786 WHEN fnd_api.g_exc_error THEN
787 ROLLBACK TO fill_request_resources;
788 x_return_status := fnd_api.g_ret_sts_error;
789 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
790 WHEN fnd_api.g_exc_unexpected_error THEN
791 ROLLBACK TO fill_request_resources;
792 x_return_status := fnd_api.g_ret_sts_unexp_error;
793 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
794 WHEN OTHERS THEN
795 x_return_status := fnd_api.g_ret_sts_unexp_error;
796 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
797 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
798 END IF;
799 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
800 ROLLBACK TO fill_request_resources;
801 END fill_request_resources;
802
803 PROCEDURE search_and_schedule_auto(
804 p_api_version IN NUMBER
805 , p_init_msg_list IN VARCHAR2
806 , p_commit IN VARCHAR2
807 , x_return_status OUT NOCOPY VARCHAR2
808 , x_msg_count OUT NOCOPY NUMBER
809 , x_msg_data OUT NOCOPY VARCHAR2
810 , p_task_tbl IN csf_requests_pvt.object_tbl_type
811 , x_sched_request_id OUT NOCOPY NUMBER
812 , x_conc_request_id OUT NOCOPY NUMBER
813 ) IS
814 l_api_version CONSTANT NUMBER := 1.0;
815 l_api_name CONSTANT VARCHAR2(30) := 'SEARCH_AND_SCHEDULE_AUTO';
816 BEGIN
817 SAVEPOINT search_and_schedule_auto_pub;
818
819 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
820 RAISE fnd_api.g_exc_unexpected_error;
821 END IF;
822
823 IF fnd_api.to_boolean(p_init_msg_list) THEN
824 fnd_msg_pub.initialize;
825 END IF;
826
827 x_return_status := fnd_api.g_ret_sts_success;
828
829 IF NOT p_task_tbl.LAST > 0 THEN
830 IF NOT valid_argument(NULL, 'task_tbl', l_api_name) THEN
831 RAISE fnd_api.g_exc_error;
832 END IF;
833 END IF;
834
835 -- Create the SearchAndScheduleAuto Request.
836 create_auto_request(
837 x_return_status => x_return_status
838 , x_msg_count => x_msg_count
839 , x_msg_data => x_msg_data
840 , p_task_tbl => p_task_tbl
841 , p_find_resources => fnd_api.g_false
842 , x_request_id => x_sched_request_id
843 );
844
845 IF x_return_status = fnd_api.g_ret_sts_error THEN
846 RAISE fnd_api.g_exc_error;
847 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
848 RAISE fnd_api.g_exc_unexpected_error;
849 END IF;
850
851 -- Submit the Scheduler concurrent program
852 x_conc_request_id := fnd_request.submit_request(
853 application => 'CSR'
854 , program => g_conc_program_name
855 , description => fnd_message.get_string('CSF', 'CSF_R_SEMI_INTERACTIVE_DESC')
856 , sub_request => FALSE
857 , argument1 => TO_CHAR(x_sched_request_id)
858 );
859
860 IF x_conc_request_id = 0 THEN
861 -- FND_REQUEST.SUBMIT_REQUEST should have populated the Message Stack.
862 RAISE fnd_api.g_exc_error;
863 END IF;
864
865 IF fnd_api.to_boolean(p_commit) THEN
866 COMMIT WORK;
867 END IF;
868 EXCEPTION
869 WHEN fnd_api.g_exc_error THEN
870 ROLLBACK TO search_and_schedule_auto_pub;
871 x_return_status := fnd_api.g_ret_sts_error;
872 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
873 WHEN fnd_api.g_exc_unexpected_error THEN
874 ROLLBACK TO search_and_schedule_auto_pub;
875 x_return_status := fnd_api.g_ret_sts_unexp_error;
876 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
877 WHEN OTHERS THEN
878 x_return_status := fnd_api.g_ret_sts_unexp_error;
879 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
880 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
881 END IF;
882 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
883 ROLLBACK TO search_and_schedule_auto_pub;
884 END search_and_schedule_auto;
885
886 PROCEDURE create_auto_request(
887 p_api_version IN NUMBER
888 , p_init_msg_list IN VARCHAR2
889 , p_commit IN VARCHAR2
890 , x_return_status OUT NOCOPY VARCHAR2
891 , x_msg_count OUT NOCOPY NUMBER
892 , x_msg_data OUT NOCOPY VARCHAR2
893 , p_query_id IN NUMBER
894 , x_sched_request_id OUT NOCOPY NUMBER
895 ) IS
896 l_api_version CONSTANT NUMBER := 1.0;
897 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_AUTO_REQUEST';
898
899 l_where csf_dc_queries_b.where_clause%TYPE;
900 l_stmt VARCHAR2(2000);
901 c_task_list ref_cursor_type;
902 l_task_tbl csf_requests_pvt.object_tbl_type;
903 BEGIN
904 SAVEPOINT create_auto_request_pub;
905
906 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
907 RAISE fnd_api.g_exc_error;
908 END IF;
909
910 IF fnd_api.to_boolean(p_init_msg_list) THEN
911 fnd_msg_pub.initialize;
912 END IF;
913
914 IF NOT valid_argument(p_query_id, 'query_id', l_api_name) THEN
915 RAISE fnd_api.g_exc_error;
916 END IF;
917
918 -- Get the WHERE CLAUSE for the Given Query ID.
919
920 l_where := csf_util_pvt.get_query_where(p_query_id);
921
922 IF l_where IS NULL THEN
923 RAISE no_data_found;
924 END IF;
925
926 -- Create the Task List Query using the WHERE CLAUSE
927 l_stmt := 'SELECT task_id FROM csf_ct_tasks WHERE ' || l_where || ' ORDER BY scheduled_start_date NULLS FIRST, planned_end_date, creation_date';
928
929 l_task_tbl := csf_requests_pvt.object_tbl_type();
930
931 -- Fetch the Task List
932 OPEN c_task_list FOR l_stmt;
933 FETCH c_task_list BULK COLLECT INTO l_task_tbl;
934 CLOSE c_task_list;
935
936 -- Create the SearchAndScheduleAuto Request
937 create_auto_request(
938 x_return_status => x_return_status
939 , x_msg_count => x_msg_count
940 , x_msg_data => x_msg_data
941 , p_task_tbl => l_task_tbl
942 , x_request_id => x_sched_request_id
943 );
944
945 IF x_return_status = fnd_api.g_ret_sts_error THEN
946 RAISE fnd_api.g_exc_error;
947 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
948 RAISE fnd_api.g_exc_unexpected_error;
949 END IF;
950
951 IF fnd_api.to_boolean(p_commit) THEN
952 COMMIT WORK;
953 END IF;
954 EXCEPTION
955 WHEN fnd_api.g_exc_error THEN
956 ROLLBACK TO create_auto_request_pub;
957 x_return_status := fnd_api.g_ret_sts_error;
958 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
959 WHEN fnd_api.g_exc_unexpected_error THEN
960 ROLLBACK TO create_auto_request_pub;
961 x_return_status := fnd_api.g_ret_sts_unexp_error;
962 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
963 WHEN OTHERS THEN
964 x_return_status := fnd_api.g_ret_sts_unexp_error;
965 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
966 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
967 END IF;
968 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
969 ROLLBACK TO create_auto_request_pub;
970 END create_auto_request;
971
972 PROCEDURE create_optimize_trips_request(
973 p_api_version IN NUMBER
974 , p_init_msg_list IN VARCHAR2
975 , p_commit IN VARCHAR2
976 , x_return_status OUT NOCOPY VARCHAR2
977 , x_msg_count OUT NOCOPY NUMBER
978 , x_msg_data OUT NOCOPY VARCHAR2
979 , p_terr_id IN NUMBER
980 , p_start_date IN DATE
981 , p_end_date IN DATE
982 , x_sched_request_id OUT NOCOPY NUMBER
983 ) IS
984 l_api_version CONSTANT NUMBER := 1.0;
985 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_OPTIMIZE_TRIPS_REQUEST';
986
987 CURSOR c_resources IS
988 SELECT /*+ cardinality(t, 1) */ DISTINCT tr.resource_id, tr.resource_type
989 FROM jtf_terr_rsc_all tr
990 , TABLE( CAST ( csf_util_pvt.get_selected_terr_table AS jtf_number_table ) ) t
991 WHERE tr.terr_id = t.column_value
992 AND (p_terr_id IS NULL OR tr.terr_id = p_terr_id);
993
994 l_res_id_tbl jtf_number_table;
995 l_res_type_tbl jtf_varchar2_table_100;
996 l_resource_tbl csf_requests_pvt.resource_tbl_type;
997 BEGIN
998 SAVEPOINT create_opt_request_pub;
999
1000 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
1001 RAISE fnd_api.g_exc_error;
1002 END IF;
1003
1004 IF fnd_api.to_boolean(p_init_msg_list) THEN
1005 fnd_msg_pub.initialize;
1006 END IF;
1007
1008 OPEN c_resources;
1009 FETCH c_resources BULK COLLECT INTO l_res_id_tbl, l_res_type_tbl;
1010 CLOSE c_resources;
1011
1012 l_resource_tbl := csf_requests_pvt.resource_tbl_type();
1013 FOR i IN 1..l_res_id_tbl.COUNT LOOP
1014 l_resource_tbl.EXTEND;
1015 l_resource_tbl(i).resource_id := l_res_id_tbl(i);
1016 l_resource_tbl(i).resource_type := l_res_type_tbl(i);
1017 l_resource_tbl(i).planwin_start := p_start_date;
1018 l_resource_tbl(i).planwin_end := p_end_date;
1019 l_resource_tbl(i).planwin_end := p_end_date;
1020 l_resource_tbl(i).preferred_resources_flag := 'N';
1021 END LOOP;
1022
1023 csf_requests_pvt.create_scheduler_request(
1024 p_api_version => 1.0
1025 , x_return_status => x_return_status
1026 , x_msg_count => x_msg_count
1027 , x_msg_data => x_msg_data
1028 , p_name => 'OptimizeAcrossTrips'
1029 , p_object_id => -1
1030 , p_resource_tbl => l_resource_tbl
1031 , x_request_id => x_sched_request_id
1032 );
1033
1034 IF x_return_status = fnd_api.g_ret_sts_error THEN
1035 RAISE fnd_api.g_exc_error;
1036 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
1037 RAISE fnd_api.g_exc_unexpected_error;
1038 END IF;
1039
1040 IF fnd_api.to_boolean(p_commit) THEN
1041 COMMIT WORK;
1042 END IF;
1043 EXCEPTION
1044 WHEN fnd_api.g_exc_error THEN
1045 ROLLBACK TO create_opt_request_pub;
1046 x_return_status := fnd_api.g_ret_sts_error;
1047 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1048 WHEN fnd_api.g_exc_unexpected_error THEN
1049 ROLLBACK TO create_opt_request_pub;
1050 x_return_status := fnd_api.g_ret_sts_unexp_error;
1051 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1052 WHEN OTHERS THEN
1053 x_return_status := fnd_api.g_ret_sts_unexp_error;
1054 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1055 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1056 END IF;
1057 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1058 ROLLBACK TO create_opt_request_pub;
1059 END create_optimize_trips_request;
1060
1061 PROCEDURE get_qualified_resources(
1062 p_api_version IN NUMBER
1063 , p_init_msg_list IN VARCHAR2
1064 , x_return_status OUT NOCOPY VARCHAR2
1065 , x_msg_count OUT NOCOPY NUMBER
1066 , x_msg_data OUT NOCOPY VARCHAR2
1067 , p_task_id_tbl IN jtf_number_table
1068 , p_start_date_tbl IN jtf_date_table
1069 , p_end_date_tbl IN jtf_date_table
1070 , x_task_resources_tbl OUT NOCOPY csf_task_resources_tbl_type
1071 ) IS
1072 l_api_version CONSTANT NUMBER := 1.0;
1073 l_api_name CONSTANT VARCHAR2(30) := 'GET_QUALIFIED_RESOURCES';
1074
1075 CURSOR c_task_info IS
1076 SELECT source_object_id
1077 FROM jtf_tasks_b t
1078 , TABLE( CAST ( p_task_id_tbl AS jtf_number_table ) ) tt
1079 WHERE t.task_id = tt.COLUMN_VALUE;
1080
1081 l_res_qualifiers csf_resource_pub.resource_qualifier_tbl_type;
1082 l_res_tbl jtf_assign_pub.assignresources_tbl_type;
1083 l_res_idx PLS_INTEGER;
1084 l_task_res_tbl csf_resource_tbl;
1085 l_sr_id_tbl jtf_number_table;
1086 l_res_preferred VARCHAR2(1);
1087 l_start_date DATE;
1088 l_end_date DATE;
1089
1090 BEGIN
1091 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
1092 RAISE fnd_api.g_exc_error;
1093 END IF;
1094
1095 IF fnd_api.to_boolean(p_init_msg_list) THEN
1096 fnd_msg_pub.initialize;
1097 END IF;
1098
1099 x_return_status := fnd_api.g_ret_sts_success;
1100
1101 l_task_res_tbl := csf_resource_tbl();
1102 x_task_resources_tbl := csf_task_resources_tbl_type();
1103
1104 OPEN c_task_info;
1105 FETCH c_task_info BULK COLLECT INTO l_sr_id_tbl;
1106 CLOSE c_task_info;
1107
1108 FOR i IN 1..p_task_id_tbl.COUNT LOOP
1109 l_start_date := p_start_date_tbl(i);
1110 l_end_date := p_end_date_tbl(i);
1111
1112 -- Get the Qualified Resources for this Task.
1113 -- Validate the Planned Window (same logic as that done in DC)
1114 csf_tasks_pub.validate_planned_dates(l_start_date, l_end_date);
1115
1116 -- Get the Resources for this Task.
1117 csf_resource_pub.get_resources_to_schedule(
1118 p_api_version => 1.0
1119 , p_init_msg_list => fnd_api.g_false
1120 , x_return_status => x_return_status
1121 , x_msg_count => x_msg_count
1122 , x_msg_data => x_msg_data
1123 , p_scheduling_mode => 'O' -- Optimizer mode
1124 , p_task_id => p_task_id_tbl(i)
1125 , p_incident_id => l_sr_id_tbl(i)
1126 , p_start => l_start_date
1127 , p_end => l_end_date
1128 , p_contracts_flag => g_auto_contract_flag
1129 , p_ib_flag => g_auto_ib_flag
1130 , p_territory_flag => g_auto_terr_flag
1131 , p_skill_flag => g_auto_skills_flag
1132 , p_res_qualifier_tbl => l_res_qualifiers
1133 , x_res_tbl => l_res_tbl
1134 );
1135
1136 l_task_res_tbl.DELETE;
1137 l_res_idx := l_res_tbl.FIRST;
1138 WHILE l_res_idx IS NOT NULL LOOP
1139 l_res_preferred := 'N';
1140 IF l_res_tbl(l_res_idx).preference_type IN ('I', 'C') THEN
1141 l_res_preferred := 'Y';
1142 END IF;
1143 l_task_res_tbl.EXTEND;
1144 l_task_res_tbl(l_task_res_tbl.COUNT) :=
1145 csf_resource(
1146 l_res_preferred
1147 , NULL
1148 , NULL
1149 , l_res_tbl(l_res_idx).resource_id
1150 , l_res_tbl(l_res_idx).resource_type
1151 , NULL
1152 , l_res_tbl(l_res_idx).start_date
1153 , l_res_tbl(l_res_idx).end_date
1154 );
1155
1156 l_res_idx := l_res_tbl.NEXT(l_res_idx);
1157 END LOOP;
1158
1159 x_task_resources_tbl.EXTEND();
1160 x_task_resources_tbl(i) := l_task_res_tbl;
1161 END LOOP;
1162 EXCEPTION
1163 WHEN fnd_api.g_exc_error THEN
1164 x_return_status := fnd_api.g_ret_sts_error;
1165 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1166 WHEN fnd_api.g_exc_unexpected_error THEN
1167 x_return_status := fnd_api.g_ret_sts_unexp_error;
1168 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1169 WHEN OTHERS THEN
1170 x_return_status := fnd_api.g_ret_sts_unexp_error;
1171 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1172 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1173 END IF;
1174 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1175 END get_qualified_resources;
1176
1177 FUNCTION check_material_for_task(p_task_assignment_id IN NUMBER)
1178 RETURN VARCHAR2 IS
1179
1180 l_has_material_transactions varchar2(1);
1181
1182 l_order_id NUMBER;
1183 l_status varchar2(30);
1184
1185 CURSOR c_get_reservations is
1186 SELECT 'Y'
1187 FROM csp_req_line_details crld
1188 , csp_requirement_lines crl
1189 , csp_requirement_headers crh
1190 WHERE crh.task_assignment_id = p_task_assignment_id
1191 AND crl.requirement_header_id = crh.requirement_header_id
1192 AND crld.requirement_line_id = crl.requirement_line_id
1193 AND crld.source_type = 'RES' ;
1194
1195 CURSOR c_get_orders is
1196 SELECT 'Y'
1197 FROM csp_req_line_details crld
1198 , csp_requirement_lines crl
1199 , csp_requirement_headers crh
1200 , oe_order_lines_all oel
1201 , oe_order_headers_all oeh
1202 WHERE crh.task_assignment_id = p_task_assignment_id
1203 AND crl.requirement_header_id = crh.requirement_header_id
1204 AND crld.requirement_line_id = crl.requirement_line_id
1205 AND crld.source_type = 'IO'
1206 AND oel.line_id = crld.source_id
1207 AND oeh.header_id = oel.header_id
1208 AND oeh.flow_status_code <>'CANCELLED'
1209 ORDER BY oeh.header_id;
1210
1211
1212 BEGIN
1213
1214 l_has_material_transactions := 'N';
1215
1216 OPEN c_get_reservations ;
1217 FETCH c_get_reservations INTO l_has_material_transactions;
1218 CLOSE c_get_reservations;
1219
1220 IF l_has_material_transactions <> 'Y' THEN
1221 OPEN c_get_orders;
1222 FETCH c_get_orders INTO l_has_material_transactions;
1223 CLOSE c_get_orders;
1224 END IF;
1225
1226 RETURN l_has_material_transactions;
1227
1228 END check_material_for_task;
1229
1230 /**
1231 * Lock the Trips and the Tasks of the given Resources. It will
1232 * try three times before throwing an exception that LOCK COULD
1233 * NOT BE OBTAINED.
1234 *
1235 * @param p_api_version API Version (1.0)
1236 * @param p_init_msg_list Initialize Message List
1237 * @param x_return_status Return Status of the Procedure.
1238 * @param x_msg_count Number of Messages in the Stack.
1239 * @param x_msg_data Stack of Error Messages.
1240 * @param p_resource_tbl Resources whose Trips and Tasks
1241 * and Tasks needs to be locked.
1242 */
1243 PROCEDURE lock_trips_and_tasks(
1244 p_api_version IN NUMBER
1245 , p_init_msg_list IN VARCHAR2
1246 , x_return_status OUT NOCOPY VARCHAR2
1247 , x_msg_count OUT NOCOPY NUMBER
1248 , x_msg_data OUT NOCOPY VARCHAR2
1249 , p_resource_tbl IN csf_resource_tbl
1250 ) IS
1251 l_api_version CONSTANT NUMBER := 1.0;
1252 l_api_name CONSTANT VARCHAR2(30) := 'LOCK_TRIPS_AND_TASKS';
1253
1254 CURSOR c_trips_and_tasks IS
1255 SELECT t.task_id
1256 , ta.task_assignment_id
1257 , ah.access_hour_id
1258 , oc.object_capacity_id
1259 FROM jtf_tasks_b t
1260 , jtf_task_assignments ta
1261 , jtf_task_statuses_b ts
1262 , csf_access_hours_b ah
1263 , cac_sr_object_capacity oc
1264 , TABLE ( CAST ( p_resource_tbl AS CSF_RESOURCE_TBL ) ) r
1265 WHERE t.task_id = ta.task_id
1266 AND ta.assignment_status_id = ts.task_status_id
1267 AND t.task_id = ah.task_id(+)
1268 AND ta.resource_id = oc.object_id
1269 AND ta.resource_type_code = oc.object_type
1270 AND (
1271 ( ta.object_capacity_id IS NOT NULL
1272 AND ta.object_capacity_id = oc.object_capacity_id
1273 )
1274 OR (
1275 ta.object_capacity_id IS NULL
1276 AND ta.booking_start_date < oc.end_date_time
1277 AND ta.booking_end_date > oc.start_date_time
1278 )
1279 )
1280 AND t.scheduled_end_date >= t.scheduled_start_date
1281 AND (t.deleted_flag = 'N' OR t.deleted_flag IS NULL)
1282 AND (
1283 NVL(ta.actual_start_date, ta.actual_end_date) IS NOT NULL
1284 OR ((ts.cancelled_flag = 'N' OR ts.cancelled_flag IS NULL))
1285 )
1286 AND oc.object_id = r.resource_id
1287 AND oc.object_type = r.resource_type
1288 AND oc.start_date_time <= r.planwin_end
1289 AND oc.end_date_time >= r.planwin_start
1290 FOR UPDATE OF t.task_id, ta.task_assignment_id, ah.access_hour_id, oc.object_capacity_id NOWAIT;
1291
1292 l_objects_locked BOOLEAN;
1293 BEGIN
1294 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
1295 RAISE fnd_api.g_exc_error;
1296 END IF;
1297
1298 IF fnd_api.to_boolean(p_init_msg_list) THEN
1299 fnd_msg_pub.initialize;
1300 END IF;
1301
1302 x_return_status := fnd_api.g_ret_sts_success;
1303
1304 l_objects_locked := FALSE;
1305
1306 FOR i IN 1..3 LOOP
1307 BEGIN
1308 OPEN c_trips_and_tasks;
1309 CLOSE c_trips_and_tasks;
1310
1311 l_objects_locked := TRUE;
1312 EXCEPTION
1313 WHEN OTHERS THEN
1314 IF c_trips_and_tasks%ISOPEN THEN
1315 CLOSE c_trips_and_tasks;
1316 END IF;
1317 END;
1318
1319 EXIT WHEN l_objects_locked;
1320 dbms_lock.sleep( i * 2 );
1321 END LOOP;
1322
1323 IF NOT l_objects_locked THEN
1324 fnd_message.set_name ('CSR', 'CSR_LOCKING_RES_TRIPS_FAILED');
1325 fnd_msg_pub.ADD;
1326 RAISE fnd_api.g_exc_error;
1327 END IF;
1328
1329 EXCEPTION
1330 WHEN fnd_api.g_exc_error THEN
1331 x_return_status := fnd_api.g_ret_sts_error;
1332 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1333 WHEN OTHERS THEN
1334 x_return_status := fnd_api.g_ret_sts_unexp_error;
1335 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1336 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1337 END IF;
1338 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1339 END lock_trips_and_tasks;
1340
1341 END csr_scheduler_pvt;
[Home] [Help]