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