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