1 PACKAGE csf_trips_pub AS
2 /* $Header: CSFPTRPS.pls 120.3.12010000.1 2008/07/28 07:37:29 appldev ship $ */
3
4 -- Maps to a Trip Record in CAC_SR_OBJECT_CAPACITY Table.
5 TYPE trip_rec_type IS RECORD (
6 trip_id NUMBER
7 , object_version_number NUMBER
8 , resource_type VARCHAR2(30)
9 , resource_id NUMBER
10 , start_date_time DATE
11 , end_date_time DATE
12 , available_hours NUMBER
13 , available_hours_before NUMBER
14 , available_hours_after NUMBER
15 , schedule_detail_id NUMBER
16 , status NUMBER
17 );
18
19 -- A Table of Trips.
20 TYPE trip_tbl_type IS TABLE OF trip_rec_type
21 INDEX BY BINARY_INTEGER;
22
23 g_action_create_trip CONSTANT VARCHAR2(10) := 'ADD';
24 g_action_delete_trip CONSTANT VARCHAR2(10) := 'DELETE';
25 g_action_replace_trip CONSTANT VARCHAR2(10) := 'REPLACE';
26 g_action_fix_trip CONSTANT VARCHAR2(10) := 'FIX';
27 g_action_upgrade_trip CONSTANT VARCHAR2(10) := 'UPGRADE';
28 g_action_close_trip CONSTANT VARCHAR2(10) := 'CLOSE';
29 g_action_block_trip CONSTANT VARCHAR2(10) := 'BLOCK';
30 g_action_unblock_trip CONSTANT VARCHAR2(10) := 'UNBLOCK';
31
32 g_trip_unavailable CONSTANT NUMBER := 0;
33 g_trip_available CONSTANT NUMBER := 1;
34
35 /**
36 * Searches for a Trip existing for the passed selection criteria.
37 * Searches CAC_SR_OBJECT_CAPACITY for the Trips that exists for the Resource
38 * identified by P_RESOURCE_ID before the passed two dates.
39 * <br>
40 * Fills the message stack with the message identified by CSF_NO_TRIPS_FOUND or
41 * CSF_MULTIPLE_TRIPS_FOUND upon encountering Zero Trips or Many Trips
42 * respectively.
43 * <br>
44 * Differs from the overloaded version of FIND_TRIP in the sense that this API
45 * returns the entire Trip Structure.
46 *
47 * @param p_api_version API Version (1.0)
48 * @param p_init_msg_list Initialize Message List
49 * @param x_return_status Return Status of the Procedure.
50 * @param x_msg_data Stack of Error Messages.
51 * @param x_msg_count Number of Messages in the Stack.
52 * @param p_resource_id Resource ID
53 * @param p_resource_type Resource Type
54 * @param p_start_date_time Start Date Time
55 * @param p_end_date_time End Date Time
56 * @param p_overtime_flag Flag to consider Overtime (Optional.. Defaults to FND_API.G_TRUE)
57 * @param x_trip Fetched Trips
58 *
59 * @see find_trips Find many Trips API
60 **/
61 PROCEDURE find_trip(
62 p_api_version IN NUMBER
63 , p_init_msg_list IN VARCHAR2 DEFAULT NULL
64 , x_return_status OUT NOCOPY VARCHAR2
65 , x_msg_data OUT NOCOPY VARCHAR2
66 , x_msg_count OUT NOCOPY NUMBER
67 , p_resource_id IN NUMBER
68 , p_resource_type IN VARCHAR2
69 , p_start_date_time IN DATE
70 , p_end_date_time IN DATE
71 , p_overtime_flag IN VARCHAR2 DEFAULT NULL
72 , x_trip OUT NOCOPY trip_rec_type
73 );
74
75 /**
76 * Searches for a Trip existing for the passed selection criteria.
77 * Searches CAC_SR_OBJECT_CAPACITY for the Trips that exists for the Resource
78 * identified by P_RESOURCE_ID before the passed two dates.
79 * <br>
80 * Fills the message stack with the message identified by CSF_NO_TRIPS_FOUND or
81 * CSF_MULTIPLE_TRIPS_FOUND upon encountering Zero Trips or Many Trips
82 * respectively.
83 * <br>
84 * Differs from the overloaded version of FIND_TRIP in the sense that this API
85 * returns only the Trip ID.
86 *
87 * @param p_api_version API Version (1.0)
88 * @param p_init_msg_list Initialize Message List
89 * @param x_return_status Return Status of the Procedure.
90 * @param x_msg_data Stack of Error Messages.
91 * @param x_msg_count Number of Messages in the Stack.
92 * @param p_resource_id Resource ID
93 * @param p_resource_type Resource Type
94 * @param p_start_date_time Start Date Time
95 * @param p_end_date_time End Date Time
96 * @param p_overtime_flag Flag to consider Overtime (Optional.. Defaults to FND_API.G_TRUE)
97 * @param x_trip_id Fetched Trip ID
98 *
99 * @see find_trips Find many Trips API
100 **/
101 PROCEDURE find_trip(
102 p_api_version IN NUMBER
103 , p_init_msg_list IN VARCHAR2 DEFAULT NULL
104 , x_return_status OUT NOCOPY VARCHAR2
105 , x_msg_data OUT NOCOPY VARCHAR2
106 , x_msg_count OUT NOCOPY NUMBER
107 , p_resource_id IN NUMBER
108 , p_resource_type IN VARCHAR2
109 , p_start_date_time IN DATE
110 , p_end_date_time IN DATE
111 , p_overtime_flag IN VARCHAR2 DEFAULT NULL
112 , x_trip_id OUT NOCOPY NUMBER
113 );
114
115 /**
116 * Searches for all the Trips existing for the passed selection criteria.
117 * Searches CAC_SR_OBJECT_CAPACITY for the Trips that exists between the
118 * passed two dates for each resource in the passed Resource List.
119 * <br>
120 * Doesnt throw any error message.
121 *
122 * @param p_api_version API Version (1.0)
123 * @param p_init_msg_list Initialize Message List
124 * @param x_return_status Return Status of the Procedure.
125 * @param x_msg_data Stack of Error Messages.
126 * @param x_msg_count Number of Messages in the Stack.
127 * @param p_resource_tbl Table of Resources containing Resource ID and Type.
128 * @param p_start_date_time Start Date Time
129 * @param p_end_date_time End Date Time
130 * @param p_overtime_flag Flag to consider Overtime (Optional..Defaults to FND_API.G_TRUE)
131 * @param x_trips Fetched Trips
132 *
133 * @see find_trip Find a Single Trip API
134 **/
135 PROCEDURE find_trips(
136 p_api_version IN NUMBER
137 , p_init_msg_list IN VARCHAR2 DEFAULT NULL
138 , x_return_status OUT NOCOPY VARCHAR2
139 , x_msg_data OUT NOCOPY VARCHAR2
140 , x_msg_count OUT NOCOPY NUMBER
141 , p_resource_tbl IN csf_resource_pub.resource_tbl_type
142 , p_start_date_time IN DATE
143 , p_end_date_time IN DATE
144 , p_overtime_flag IN VARCHAR2 DEFAULT NULL
145 , x_trips OUT NOCOPY trip_tbl_type
146 );
147
148 /**
149 * Creates a Trip for the passed Resource constrained by the two times.
150 * Creates a new Trip for the Resource identified by P_RESOURCE_ID constrained
151 * by the two times Start and End Time.
152 * <br>
153 * This API checks for the existence of any trip which overlaps with the
154 * specified boundary conditions for the Resource. Upon that error, the message
155 * CSF_TRIP_ALREADY_EXISTS is pushed into the message stack.
156 * Moreover it doesnt allow for a trip to be created if there exists some Shift
157 * Tasks in the new Trip interval. Upon that error, the message
158 * CSF_SHIFT_TASKS_EXIST is pushed into the message stack.
159 * <br>
160 * In addition to the above check, it is also mandatory that the total duration
161 * between the two times should be less than or equal to 1 day.
162 * <br>
163 * 1. Moreover, the API automatically creates Shift Tasks 'Departure' and
164 * 'Arrival' at the two end points of the Trip if needed. <br>
165 * 2. The Availability of the Trip is set to the Total Duration between
166 * identified by P_RESOURCE_ID before the passed two dates.<br>
167 * 3. All the Task Assignments which fall within the Trip or overlap with
168 * any one of boundary times will be tied to the Trip automatically.
169 *
170 * @param p_api_version API Version (1.0)
171 * @param p_init_msg_list Initialize Message List
172 * @param p_commit Commits the Database
173 * @param x_return_status Return Status of the Procedure.
174 * @param x_msg_data Stack of Error Messages.
175 * @param x_msg_count Number of Messages in the Stack.
176 * @param p_resource_id Resource ID
177 * @param p_resource_type Resource Type
178 * @param p_start_date_time Start Date Time
179 * @param p_end_date_time End Date Time
180 * @param p_schedule_detail_id (Not Used) Maps to CAC_SR_SCHEDULE_DETAILS
181 * @param p_status Initial Status of the Trip (Unblocked).
182 * @param p_find_tasks Flag to indicate whether Tasks has to be linked
183 * @param x_trip_id Trip ID of the new Trip created.
184 *
185 * @see create_trips Create Multiple Trips API
186 **/
187 PROCEDURE create_trip(
188 p_api_version IN NUMBER
189 , p_init_msg_list IN VARCHAR2 DEFAULT NULL
190 , p_commit IN VARCHAR2 DEFAULT NULL
191 , x_return_status OUT NOCOPY VARCHAR2
192 , x_msg_data OUT NOCOPY VARCHAR2
193 , x_msg_count OUT NOCOPY NUMBER
194 , p_resource_id IN NUMBER
195 , p_resource_type IN VARCHAR2
196 , p_start_date_time IN DATE
197 , p_end_date_time IN DATE
198 , p_schedule_detail_id IN NUMBER DEFAULT NULL
199 , p_status IN NUMBER DEFAULT NULL
200 , p_find_tasks IN VARCHAR2 DEFAULT NULL
201 , x_trip_id OUT NOCOPY NUMBER
202 );
203
204 /**
205 * Deletes the Trip given the Trip ID.
206 *
207 * @param p_api_version API Version (1.0)
208 * @param p_init_msg_list Initialize Message List
209 * @param p_commit Commits the Database
210 * @param x_return_status Return Status of the Procedure.
211 * @param x_msg_data Stack of Error Messages.
212 * @param x_msg_count Number of Messages in the Stack.
213 * @param p_trip_id Trip ID
214 * @param p_object_version_number Trip Object Version Number
215 *
216 * @see update_trip Update Trip API
217 * @see delete_trips Delete Multiple Trips API
218 **/
219 PROCEDURE delete_trip(
220 p_api_version IN NUMBER
221 , p_init_msg_list IN VARCHAR2 DEFAULT NULL
222 , p_commit IN VARCHAR2 DEFAULT NULL
223 , x_return_status OUT NOCOPY VARCHAR2
224 , x_msg_data OUT NOCOPY VARCHAR2
225 , x_msg_count OUT NOCOPY NUMBER
226 , p_trip_id IN NUMBER
227 , p_object_version_number IN NUMBER DEFAULT NULL
228 );
229
230 /**
231 * Updates the Availability of the Trip given the Trip ID.
232 * Updates the Available, Available Before, Available After Hours of the Trip
233 * in the underlying database given the Trip ID.
234 * <br>
235 * The API first validates the passed Available Values. Both Available Hours and
236 * Update Available Hours cant be passed. Only one of them can be passed. If they
237 * make the Availability equal to Shift Length, then the value of Available Hours
238 * Before and After are ignored and they are updated as NULL. <br>
239 *
240 * @param p_api_version API Version (1.0)
241 * @param p_init_msg_list Initialize Message List
242 * @param p_commit Commits the Database
243 * @param x_return_status Return Status of the Procedure.
244 * @param x_msg_data Stack of Error Messages.
245 * @param x_msg_count Number of Messages in the Stack.
246 * @param p_trip_id Trip ID
247 * @param p_object_version_number Trip Object Version Number
248 * @param p_available_hours Available Hours in the Trip
249 * @param p_upd_available_hours Update Available Hours with value.
250 * @param p_available_hours_before Available Hours in the start of Trip.
251 * @param p_available_hours_after Available Hours at the end of Trip.
252 * @param p_status (Not Used) New Status of the Trip.
253 *
254 * @see delete_trip Delete Trip API
255 **/
256 PROCEDURE update_trip(
257 p_api_version IN NUMBER
258 , p_init_msg_list IN VARCHAR2 DEFAULT NULL
259 , p_commit IN VARCHAR2 DEFAULT NULL
260 , x_return_status OUT NOCOPY VARCHAR2
261 , x_msg_data OUT NOCOPY VARCHAR2
262 , x_msg_count OUT NOCOPY NUMBER
263 , p_trip_id IN NUMBER
264 , p_object_version_number IN NUMBER DEFAULT NULL
265 , p_available_hours IN NUMBER DEFAULT NULL
266 , p_upd_available_hours IN NUMBER DEFAULT NULL
267 , p_available_hours_before IN NUMBER DEFAULT NULL
268 , p_available_hours_after IN NUMBER DEFAULT NULL
269 , p_status IN NUMBER DEFAULT NULL
270 );
271
272 /**
273 * Fixes the passed Trip by pulling in all the valid Tasks into the Trip and
274 * removing Tasks which doesnt fall / linking tasks which fall within
275 * the Trip Boundary.
276 * <br>
277 * The API
278 * 1. Task Assignments
279 * Finds all the Task Assignments which doesnt fall within the
280 * Trip Start Time and (Trip End Time + Overtime) and can be linked
281 * to another trip. If so, it removes the task from the Trip.
282 * Then it goes and checks whether there are any valid candidatees
283 * that can be made as part of the Trip.
284 * 2. Shift Tasks
285 * Deletes any Duplicate Shift Task if present. If any of the Shift Task
286 * is not there... it creates one.
287 * 3. Availability
288 * Computes the correct availability of the Trip and updates it.
289 * <br>
290 * During the process of find valid eligible Task Assignments, it doesnt care if the
291 * Task Assignment is already part of another Trip (ie) Trip ID is already stamped in
292 * the Task Assignment. This is because, it is mandatory for two trips not to overlap
293 * anytime.
294 *
295 * @param p_api_version API Version (1.0)
296 * @param p_init_msg_list Initialize Message List
297 * @param p_commit Commits the Database
298 * @param x_return_status Return Status of the Procedure.
299 * @param x_msg_data Stack of Error Messages.
300 * @param x_msg_count Number of Messages in the Stack.
301 * @param p_trip_id Trip ID
302 *
303 **/
304 PROCEDURE fix_trip(
305 p_api_version IN NUMBER
306 , p_init_msg_list IN VARCHAR2 DEFAULT NULL
307 , p_commit IN VARCHAR2 DEFAULT NULL
308 , x_return_status OUT NOCOPY VARCHAR2
309 , x_msg_data OUT NOCOPY VARCHAR2
310 , x_msg_count OUT NOCOPY NUMBER
311 , p_trip_id IN NUMBER
312 , p_object_version_number IN NUMBER DEFAULT NULL
313 );
314
315 /**
316 * Processes the passed action on all the Trips falling between
317 * the two dates passed for the given Resource.
318 * Processes actions for a given Trip or for all Trips identified
319 * by the passed Resource List and the two dates.
320 * <br>
321 * Either P_TRIP_ID has to be passed or the list of Resources along
322 * with Start Date and End Date has to tbe passed for the API to
323 * do something.
324 * <br>
325 * Action to be performed by the API on the Trips is identified
326 * by P_TRIP_ACTION which takes values as given in the following table
327 *
328 * --------------------------------------------------------------
329 * | Operation | Identifier | Value |
330 * --------------------------------------------------------------
331 * | Create Trips | g_action_create_trip | ADD |
332 * | Delete Trips | g_action_delete_trip | DELETE |
333 * | Fix Trips | g_action_fix_trip | FIX |
334 * | Block Trips | g_action_block_trip | BLOCK |
335 * | Unblock Trips | g_action_unblock_trip | UNBLOCK |
336 * | Upgrade Trips | g_action_upgrade_trip | UPGRADE |
337 * | Replace Trips | g_action_replace_trip | REPLACE |
338 * --------------------------------------------------------------
339 *
340 * <br>
341 * In case of Block Trip and Unblock Trip, the tasks forming part of
342 * the Trip will also be affected by the action. The eligible Tasks
343 * are those which doesnt have Actuals entered or which are not
344 * interrupted. The status of the task will move from Planned to
345 * Blocked Planned and Assigned to Blocked Assigned or vice versa depending
346 * on the action of the Trip.
347 * <br>
348 * The actions ADD (Create Trips) and REPLACE (Replace Trips) accept only
349 * one resource currently in the table of resources passed.
350 *
351 * @param p_api_version API Version (1.0)
352 * @param p_init_msg_list Initialize Message List (Optional)
353 * @param p_commit Commits the Database (Optional)
354 * @param x_return_status Return Status of the Procedure.
355 * @param x_msg_data Stack of Error Messages.
356 * @param x_msg_count Number of Messages in the Stack.
357 * @param p_action Action to be performed on the Trip.
358 * @param p_trip_id Trip to be processed. (Optional)
359 * @param p_resource_tbl Table of Resources. (Optional)
360 * @param p_start_date Start Date. (Optional)
361 * @param p_end_date End Date. (Optional)
362 **/
363 PROCEDURE process_action(
364 p_api_version IN NUMBER
365 , p_init_msg_list IN VARCHAR2 DEFAULT NULL
366 , p_commit IN VARCHAR2 DEFAULT NULL
367 , x_return_status OUT NOCOPY VARCHAR2
368 , x_msg_data OUT NOCOPY VARCHAR2
369 , x_msg_count OUT NOCOPY NUMBER
370 , p_action IN VARCHAR2
371 , p_trip_id IN NUMBER DEFAULT NULL
372 , p_resource_tbl IN csf_resource_pub.resource_tbl_type DEFAULT NULL
373 , p_start_date IN DATE DEFAULT NULL
374 , p_end_date IN DATE DEFAULT NULL
375 );
376
377 /**
378 * "Generate Trips Concurrent Program" - Generates the Trip Records for
379 * the Resources under the current User or for the passed Resource.
380 * <br>
381 * This is the Generate Trips concurrent program and the API generates
382 * the Trips records for the passed Resource or for all Resources under
383 * all the Territories of the current User for the timespan given by
384 * the Start and End Dates.
385 * This Concurrent Program can be run in follwing Modes.
386 * 1. Add - Creates new Trips Records. <br>
387 * 2. Delete - Deletes existing Trip Records. <br>
388 * 3. Replace - Deletes existing Trip Records and creates new ones. <br>
389 * 4. Fix - Fixes existing Trip Records to be proper. <br>
390 * 5. Upgrade - Upgrades the current Shift Task Model to Trips Model.
391 * <br>
392 * Default Values: <br>
393 * a. Default Action is ADD (Given in the Concurrent Program Definition). <br>
394 * b. Default Start Date is SYSDATE. <br>
395 * c. Default End Date is (Start Date + Plan Scope). <br>
396 * d. Default Resource is NULL - All Resources under the User. <br>
397 * <br>
398 *
399 * @param errbuf Standard Concurrent Program Output Parameter
400 * @param retcode Standard Concurrent Program Output Parameter
401 * @param p_action Action to be accomplished by the CP.
402 * @param p_start_date Start Date for the Action.
403 * @param p_num_days Number of Days.
404 * @param p_resource_type Resource Type for whom GTR has to be run
405 * @param p_resource_id Resource ID for whom GTR has to be run
406 **/
407 PROCEDURE generate_trips(
408 errbuf OUT NOCOPY VARCHAR2
409 , retcode OUT NOCOPY VARCHAR2
410 , p_action IN VARCHAR2
411 , p_start_date IN VARCHAR2 DEFAULT NULL
412 , p_num_days IN NUMBER DEFAULT NULL
413 , p_resource_type IN VARCHAR2 DEFAULT NULL
414 , p_resource_id IN NUMBER DEFAULT NULL
415 );
416
417 PROCEDURE optimize_across_trips(
418 p_api_version IN NUMBER
419 , p_init_msg_list IN VARCHAR2 DEFAULT NULL
420 , p_commit IN VARCHAR2 DEFAULT NULL
421 , x_return_status OUT NOCOPY VARCHAR2
422 , x_msg_data OUT NOCOPY VARCHAR2
423 , x_msg_count OUT NOCOPY NUMBER
424 , x_conc_request_id OUT NOCOPY NUMBER
425 , p_resource_tbl IN csf_requests_pvt.resource_tbl_type DEFAULT NULL
426 , p_start_date IN DATE DEFAULT NULL
427 , p_end_date IN DATE DEFAULT NULL
428 );
429
430 END csf_trips_pub;
431
[Home] [Help]