1 PACKAGE csr_scheduler_pvt AUTHID CURRENT_USER AS
2 /* $Header: CSRVSCHS.pls 120.16 2011/05/02 10:44:32 anangupt ship $ */
3
4 /**
5 * Gets the Geocode of a Task.
6 *
7 * The API returns the Geocoded Geometry of the Task if the given Task has an address.
8 * If the Task is not yet Geocoded, then tries to resolve the Address of the Task by
9 * calling Location Finder.
10 *
11 * The Geocode is returned by stamping the values in the respective output parameters
12 * rather than as MDSYS.SDO_GEOMETRY itself because JDBC doesnt support PLSQL Record
13 * Types as of now.
14 *
15 * @param p_api_version API Version (1.0)
16 * @param p_init_msg_list Initialize Message List
17 * @param p_commit Commits the Work
18 * @param x_return_status Return Status of the Procedure.
19 * @param x_msg_count Number of Messages in the Stack.
20 * @param x_msg_data Stack of Error Messages.
21 * @param p_task_id Task ID
22 * @param x_locus_segment_id Segment ID of the Road in the Address's Geocode
23 * @param x_locus_side Side of the Road in the Address's Geocode
24 * @param x_locus_spot Offset in the Road in the Address's Geocode
25 * @param x_locus_lat Longitude of the Location in the Address's Geocode
26 * @param x_locus_lon Latitude of the Location in the Address's Geocode
27 * @param x_should_call_lf Should LF be called on the Address or not
28 * @param x_location_id Location ID of the Address.
29 * @param x_srid Coordinate system id.
30 * @param x_country_code Country Code of the Address
31 */
32 PROCEDURE get_geometry(
33 p_api_version IN NUMBER
34 , p_init_msg_list IN VARCHAR2
35 , p_commit IN VARCHAR2
36 , x_return_status OUT NOCOPY VARCHAR2
37 , x_msg_count OUT NOCOPY NUMBER
38 , x_msg_data OUT NOCOPY VARCHAR2
39 , p_task_id IN NUMBER
40 , x_locus_segment_id OUT NOCOPY NUMBER
41 , x_locus_side OUT NOCOPY NUMBER
42 , x_locus_spot OUT NOCOPY NUMBER
43 , x_locus_lat OUT NOCOPY NUMBER
44 , x_locus_lon OUT NOCOPY NUMBER
45 , x_should_call_lf OUT NOCOPY VARCHAR2
46 , x_location_id OUT NOCOPY NUMBER
47 , x_srid OUT NOCOPY NUMBER
48 , x_country_code OUT NOCOPY VARCHAR2
49 );
50
51 /**
52 * Converts the given Time from the given Timezone to Server Timezone.
53 *
54 * Calls GET_SERVER_TIMEZONE to get the Server Timezone Identifier.
55 * Note that only the Time Component of the given Time is taken and not the
56 * Date Component. The returned date has SYSDATE as the Date Component.
57 *
58 * Generally used by WTP to create the Window Slots properly converted from Task
59 * Timezone to Server Timezone.
60 *
61 * @param p_time Time to be converted to Server Timezone
62 * @param p_timezone_id Source Timezone Identifier
63 * @return Time converted to Server Timezone.
64 */
65 FUNCTION convert_timezone(p_time IN DATE, p_timezone_id IN NUMBER)
66 RETURN DATE;
67
68 /**
69 * Returns the Time Zone Difference between the given Timezone
70 * and the Server Timezone in Half Hour Units
71 *
72 * @param p_timezone_id Timezone Identifier
73 * @return Difference in Half Hour Units
74 */
75 FUNCTION get_server_timezone_offset(p_timezone_id IN NUMBER)
76 RETURN NUMBER;
77
78
79 /**
80 * Gets the Timezone corresponding to the given Address.
81 *
82 * If no timezone is found for the Address or the Address is invalid,
83 * then the API returns Server Timezone as defined by GET_SERVER_TIMEZONE.
84 *
85 * @return Timezone corresponding to the given Address.
86 */
87 FUNCTION get_timezone(p_address_id IN NUMBER)
88 RETURN NUMBER;
89
90 /**
91 * Gets the Server Timezone configured in the E-Business Suite
92 *
93 * Gets the value of the profile SERVER_TIMEZONE_ID. Apparently SERVER_TIMEZONE_ID
94 * has been replaced by the profile SERVER_TIMEZONE. So if the former profile
95 * doesnt return any value, the API will try to return value of SERVER_TIMEZONE_ID.
96 *
97 * If either of the profiles doesnt return any value, then it returns the first
98 * profile satisfying the condition of being in GMT-8 Offset and also having
99 * Day Light Savings as Yes from HZ_TIMEZONES.
100 *
101 * @return Server Timezone ID (or Zero upon any exception).
102 */
103 FUNCTION get_server_timezone
104 RETURN NUMBER;
105
106 /**
107 * This API Creates a scheduler search request
108
109 * @param p_api_version API Version (1.0)
110 * @param p_init_msg_list Initialize Message List
111 * @param p_commit Commits the Work. This is set as true to allow Scheduler to see the new request created.
112 * @param x_return_status Return Status of the Procedure.
113 * @param x_msg_count Number of Messages in the Stack.
114 * @param x_msg_data Stack of Error Messages.
115 * @param p_task_id The task id for which the search request is being created.
116 * @param The following set of parameters represents the qualifiers of resoucres for the task.
117 1) p_contracts_flag
118 2) p_ib_flag
119 3) p_territory_flag
120 4) p_skill_flag
121 * @param p_resource_tbl The set of resource to be considered for scheduling irrespective of their qualification.
122 * @param p_request_name Type of request to be created
123 One of the following request types are created
124 1) SearchAssistedOptions
125 2) SearchIntelligentOptions
126 3) SearchWTPOptions
127 * @param p_spares_likelihood Spares Likelihood to be used for this request
128 * @param p_route_based_flag Flag to enable route based scheduling for this request
129 * @param p_disabled_access_hours_flag Activate access hours if any assigned for the task.
130 * @param x_request_id Request id of created request
131
132 * The API first finds the set of qualified resoucres for the given task
133 * based on the qualifers given.
134 * It then creates appropriate serach request based on the request name
135 */
136 PROCEDURE create_search_request(
137 p_api_version IN NUMBER
138 , p_init_msg_list IN VARCHAR2
139 , p_commit IN VARCHAR2
140 , x_return_status OUT NOCOPY VARCHAR2
141 , x_msg_count OUT NOCOPY NUMBER
142 , x_msg_data OUT NOCOPY VARCHAR2
143 , p_request_name IN VARCHAR2
144 , p_task_id IN NUMBER
145 , p_contracts_flag IN VARCHAR2
146 , p_ib_flag IN VARCHAR2
147 , p_territory_flag IN VARCHAR2
148 , p_skill_flag IN VARCHAR2
149 , p_resource_id_tbl IN jtf_number_table
150 , p_resource_type_tbl IN jtf_varchar2_table_100
151 , p_spares_mandatory IN VARCHAR2
152 , p_spares_source IN VARCHAR2
153 , p_consider_standby_shifts IN VARCHAR2
154 , p_route_based_flag IN VARCHAR2
155 , p_disabled_access_hours_flag IN VARCHAR2
156 , x_request_id OUT NOCOPY NUMBER
157 );
158
159 /**
160 * This API creates a request of type 'ScheduleOption'
161
162 * @param p_api_version API Version (1.0)
163 * @param p_init_msg_list Initialize Message List
164 * @param p_commit Commits the Work. This is set as true to allow Scheduler to see the new request created.
165 * @param x_return_status Return Status of the Procedure.
166 * @param x_msg_count Number of Messages in the Stack.
167 * @param x_msg_data Stack of Error Messages.
168 * @param p_plan_option_id The id of the plan option to be scheduled
169 * @param p_target_status_id The status to which the task has to be changed after scheduling
170 * @param p_set_plan_task_confirmed Flag to indicate that customer confirmation has to be considered for the task.
171 * @param x_request_id Request id of created request
172
173 * This API creates a scheduler request of type 'ScheduleOption'
174 * that is used to schedule the given plan option
175 */
176 PROCEDURE create_schedule_option_request(
177 p_api_version IN NUMBER
178 , p_init_msg_list IN VARCHAR2
179 , p_commit IN VARCHAR2
180 , x_return_status OUT NOCOPY VARCHAR2
181 , x_msg_count OUT NOCOPY NUMBER
182 , x_msg_data OUT NOCOPY VARCHAR2
183 , p_plan_option_id IN NUMBER
184 , p_target_status_id IN NUMBER
185 , p_set_plan_task_confirmed IN VARCHAR2
186 , x_request_id OUT NOCOPY NUMBER
187 );
188
189 /**
190 * Fills the Qualified Resources for all the Tasks associated with the
191 * given Request.
192 * <br>
193 * Note that Auto-Schedule Request submitted from Dispatch Center will
194 * create a SearchAndScheduleAuto Request containing only the Task
195 * Information and not the Resource Information to improve the performance.
196 * Scheduler will call this API to fill the tasks with Qualified Resources
197 * before proceeding with Scheduling Operation.
198 *
199 * @param p_api_version API Version (1.0)
200 * @param p_init_msg_list Initialize Message List
201 * @param x_return_status Return Status of the Procedure.
202 * @param x_msg_count Number of Messages in the Stack.
203 * @param x_msg_data Stack of Error Messages.
204 * @param p_request_id Request ID whose Tasks should be processed.
205 * @param x_task_num_tbl Table of Task Numbers for which Resource API errored out.
206 * @param x_msg_tbl Table of Error Messsages returned by Resource API.
207 * @param x_task_id_tbl Table of Task IDs for which Resource API errored out.
208 */
209 PROCEDURE fill_request_resources(
210 p_api_version IN NUMBER
211 , p_init_msg_list IN VARCHAR2 DEFAULT NULL
212 , p_commit IN VARCHAR2 DEFAULT NULL
213 , x_return_status OUT NOCOPY VARCHAR2
214 , x_msg_count OUT NOCOPY NUMBER
215 , x_msg_data OUT NOCOPY VARCHAR2
216 , p_request_id IN NUMBER
217 , x_task_num_tbl OUT NOCOPY JTF_NUMBER_TABLE
218 , x_msg_tbl OUT NOCOPY JTF_VARCHAR2_TABLE_100
219 , x_task_id_tbl OUT NOCOPY JTF_NUMBER_TABLE
220 );
221
222 /**
223 * Search Plan Options for the given list of To-Be-Planned Tasks and
224 * automatically Schedule the Best Option for each task.
225 * <br>
226 * This is generally used by Semi-Interative Scheduling wherein the End User
227 * selects a list of Tasks to be processed in Task List of Dispatch Center.
228 * Ultimately the API requires the List of Tasks to be processed.
229 *
230 * @param p_api_version API Version (1.0)
231 * @param p_init_msg_list Initialize Message List
232 * @param p_commit Commit the Work.
233 * @param x_return_status Return Status of the Procedure.
234 * @param x_msg_count Number of Messages in the Stack.
235 * @param x_msg_data Stack of Error Messages.
236 * @param p_task_tbl List of Tasks to be processed
237 * @param x_sched_request_id Scheduler Request ID (CSF_R_SCHED_REQUEST)
238 * @param x_conc_request_id Concurrent Request ID (FND_CONCURRENT_REQUESTS)
239 */
240 PROCEDURE search_and_schedule_auto(
241 p_api_version IN NUMBER
242 , p_init_msg_list IN VARCHAR2 DEFAULT NULL
243 , p_commit IN VARCHAR2 DEFAULT NULL
244 , x_return_status OUT NOCOPY VARCHAR2
245 , x_msg_count OUT NOCOPY NUMBER
246 , x_msg_data OUT NOCOPY VARCHAR2
247 , p_task_tbl IN csf_requests_pvt.object_tbl_type
248 , x_sched_request_id OUT NOCOPY NUMBER
249 , x_conc_request_id OUT NOCOPY NUMBER
250 );
251
252 /**
253 * Creates a Scheduler Request for the given Query so that Autonomous Scheduler
254 * can process the Tasks as stored in the created request.
255 *
256 * Retrives the list of valid Tasks to be processed using the given Query ID
257 * and creates a parent Scheduler Request (A) with name "SearchAndScheduleAuto" and
258 * creates a Scheduler Request "SearchAndSchedule" for each Task in the above Task
259 * Table with Parent Request ID as (A). Morever the table CSF_R_RESOURCE_RESULTS
260 * will be populated by the valid Resource Candidates suitable to perform the
261 * Task as determined by CSR_SCHEDULER_PVT.GET_ASSIGN_TASK_RESOURCES.
262 * <br>
263 * Note that the returned Scheduler Request ID is of the parent SearchAndScheduleAuto
264 * Request (A) and not that the individual child requests SearchAndSchedule.
265 *
266 * @param p_api_version API Version (1.0)
267 * @param p_init_msg_list Initialize Message List
268 * @param p_commit Commit the Work.
269 * @param x_return_status Return Status of the Procedure.
270 * @param x_msg_count Number of Messages in the Stack.
271 * @param x_msg_data Stack of Error Messages.
272 * @param p_task_tbl List of Tasks to be processed
273 * @param x_sched_request_id Scheduler Request ID (CSF_R_SCHED_REQUEST)
274 */
275 PROCEDURE create_auto_request(
276 p_api_version IN NUMBER
280 , x_msg_count OUT NOCOPY NUMBER
277 , p_init_msg_list IN VARCHAR2 DEFAULT NULL
278 , p_commit IN VARCHAR2 DEFAULT NULL
279 , x_return_status OUT NOCOPY VARCHAR2
281 , x_msg_data OUT NOCOPY VARCHAR2
282 , p_query_id IN NUMBER
283 , x_sched_request_id OUT NOCOPY NUMBER
284 );
285
286 /**
287 * For a given Task Assignment, this API checks whether there exists Material
288 * Transactions (Reservations or Orders) created against it.
289 *
290 * @param p_task_assignment_id Task Assignment Id to be checked for
291 *
292 * Returns Y in case there exists Transactions. N otherwise.
293 */
294 FUNCTION check_material_for_task(p_task_assignment_id IN NUMBER)
295 RETURN VARCHAR2;
296
297 /**
298 * Creates a Scheduler Request for the given Territory so that
299 * Autonomous Scheduler can optimize across all the trips
300 * belonging to the resources attached to the territory.
301 *
302 * In case the caller wants to restrict the optimization to only
303 * one Territory, then the parameter P_TERR_ID can be populated
304 * with the relevant Territory Id. If not, the caller can send
305 * the value as NULL so that all the Dispatcher Selected
306 * Territories will be used.
307 * <br>
308 * All the Trips within the given timeframe (P_START_DATE
309 * and P_END_DATE) belonging to the Resources attached to the
310 * Territories will be processed.
311 * <br>
312 * A Parent Request OptimizeTrips will be created and a single
313 * Child Request OptimizeTrip will be created with all the
314 * Resource Information.
315 *
316 * @param p_api_version API Version (1.0)
317 * @param p_init_msg_list Initialize Message List
318 * @param p_commit Commit the Work.
319 * @param x_return_status Return Status of the Procedure.
320 * @param x_msg_count Number of Messages in the Stack.
321 * @param x_msg_data Stack of Error Messages.
322 * @param p_terr_id Territory Id in case only a
323 * particular Territory needs
324 * to be processed.
325 * @param p_start_date Start Date of the timeframe
326 * @param p_end_date End Date of the timeframe
327 * @param x_sched_request_id Scheduler Request ID (CSF_R_SCHED_REQUEST)
328 */
329 PROCEDURE create_optimize_trips_request(
330 p_api_version IN NUMBER
331 , p_init_msg_list IN VARCHAR2 DEFAULT NULL
332 , p_commit IN VARCHAR2 DEFAULT NULL
333 , x_return_status OUT NOCOPY VARCHAR2
334 , x_msg_count OUT NOCOPY NUMBER
335 , x_msg_data OUT NOCOPY VARCHAR2
336 , p_terr_id IN NUMBER DEFAULT NULL
337 , p_start_date IN DATE
338 , p_end_date IN DATE
339 , x_sched_request_id OUT NOCOPY NUMBER
340 );
341
342 /**
343 * Gets the Qualified Resources for all the Tasks as given in the input task list.
344 *
345 * @param p_api_version API Version (1.0)
346 * @param p_init_msg_list Initialize Message List
347 * @param x_return_status Return Status of the Procedure.
348 * @param x_msg_count Number of Messages in the Stack.
349 * @param x_msg_data Stack of Error Messages.
350 * @param p_task_id_tbl Table of Task Identifiers
351 * @param p_start_date_tbl Table of Start Date of Activity for the Tasks
352 * @param p_end_date_tbl Table of End Date of Activity for the Tasks
353 * @param x_resources_tbl Table of Resources for each
354 * task. Each entry is a table
355 * by itself.
356 */
357 PROCEDURE get_qualified_resources(
358 p_api_version IN NUMBER
359 , p_init_msg_list IN VARCHAR2
360 , x_return_status OUT NOCOPY VARCHAR2
361 , x_msg_count OUT NOCOPY NUMBER
362 , x_msg_data OUT NOCOPY VARCHAR2
363 , p_task_id_tbl IN jtf_number_table
364 , p_start_date_tbl IN jtf_date_table
365 , p_end_date_tbl IN jtf_date_table
366 , x_task_resources_tbl OUT NOCOPY csf_task_resources_tbl_type
367 );
368
369 /**
370 * Lock the Trips and the Tasks of the given Resources. It will
371 * try three times before throwing an exception that LOCK COULD
372 * NOT BE OBTAINED.
373 *
374 * @param p_api_version API Version (1.0)
375 * @param p_init_msg_list Initialize Message List
376 * @param x_return_status Return Status of the Procedure.
377 * @param x_msg_count Number of Messages in the Stack.
378 * @param x_msg_data Stack of Error Messages.
379 * @param p_resource_tbl Resources whose Trips and Tasks
380 * and Tasks needs to be locked.
381 */
382 PROCEDURE lock_trips_and_tasks(
383 p_api_version IN NUMBER
384 , p_init_msg_list IN VARCHAR2
385 , x_return_status OUT NOCOPY VARCHAR2
386 , x_msg_count OUT NOCOPY NUMBER
387 , x_msg_data OUT NOCOPY VARCHAR2
388 , p_resource_tbl IN csf_resource_tbl
389 );
390
391 FUNCTION get_third_party_res_role(
392 p_resource_id IN NUMBER
393 , p_resource_type IN VARCHAR2
394 ) RETURN VARCHAR2;
395
396
397 END csr_scheduler_pvt;
[Home] [Help]