1 PACKAGE csf_resource_pub AS
2 /* $Header: CSFPRESS.pls 120.3.12010000.1 2008/07/28 07:37:18 appldev ship $ */
3
4 /**
5 * PLSQL Record Type to contain information about a Single Territory Qualifier.
6 *
7 * Information stored are Qualifier Usage ID, Enabled (or not), Label,
8 * Value, Associated Value (in case of Qualifier having a second value)
9 * and Display Value (where ID's are converted into Names).
10 */
11 TYPE resource_qualifier_rec_type IS RECORD(
12 qual_usg_id NUMBER -- territories qualifier usage ID
13 , use_flag VARCHAR2(1) -- checked/unchecked by user
14 , label VARCHAR2(60) -- qualifier description for the UI
15 , value VARCHAR2(360) -- qualifier value
16 , associated_value VARCHAR2(360) -- value for qualifiers that occur in pairs
17 -- (inventory item id and organization id).
18 , display_value VARCHAR2(360) -- may be filled with a converted value
19 -- (ID to name) when scheduling is being traced
20 );
21
22 /**
23 * PLSQL Table Type to contain information about many Territory Qualifiers
24 * where each element is of type RESOURCE_QUALIFIER_REC_TYPE.
25 */
26 TYPE resource_qualifier_tbl_type IS TABLE OF resource_qualifier_rec_type
27 INDEX BY BINARY_INTEGER;
28
29 /**
30 * PLSQL Record Type to contain information about a Resource's Shift Definitions.
31 *
32 * Information stored are Shift Construct ID, Availability Type (not used), Shift Start
33 * and End Time.
34 */
35 TYPE shift_rec_type IS RECORD(
36 shift_construct_id NUMBER
37 , start_datetime DATE
38 , end_datetime DATE
39 , availability_type VARCHAR2(40)
40 );
41
42 /**
43 * PLSQL Table Type to contain information about many Shift Definitions
44 * where each element is of type SHIFT_REC_TYPE.
45 */
46 TYPE shift_tbl_type IS TABLE OF shift_rec_type
47 INDEX BY BINARY_INTEGER;
48
49 /**
50 * PLSQL Record Type to contain information about a Resource
51 *
52 */
53 TYPE resource_rec_type IS RECORD(
54 resource_id NUMBER
55 , resource_type jtf_objects_b.object_code%TYPE
56 , resource_name jtf_rs_resource_extns_tl.resource_name%TYPE
57 , resource_number jtf_rs_resource_extns.resource_number%TYPE
58 );
59
60 /**
61 * PLSQL Table Type to contain information about many Resources
62 * where each element is of type RESOURCE_REC_TYPE.
63 */
64 TYPE resource_tbl_type IS TABLE OF resource_rec_type;
65
66 /**
67 * Returns the Resource Type Code corresponding to a Resource Category.
68 * <br>
69 * In sync with the code done in JTF_RS_ALL_RESOURCES_VL
70 *
71 * @param p_category Resource Category
72 * @returns Resource Type Code (VARCHAR2).
73 */
74 FUNCTION rs_category_type (p_category VARCHAR2)
75 RETURN VARCHAR2;
76
77 /**
78 * Returns the ID of the Resource tied to the given User (FND User).
79 * <br>
80 * If no User is passed in, then it will take the User who has logged in
81 * (FND_GLOBAL.USER_ID).
82 *
83 * @param p_user_id Identifier to the User desired (Optional)
84 * @returns Resource ID (NUMBER)
85 */
86 FUNCTION resource_id (p_user_id NUMBER DEFAULT NULL)
87 RETURN NUMBER;
88
89 /**
90 * Returns the Resource Type of the Resource tied to the given user. (FND User)
91 * <br>
92 * If no User is passed in, then it will take the User who has logged in
93 * (FND_GLOBAL.USER_ID).
94 *
95 * @param p_user_id Identifier to the User desired (Optional)
96 * @returns Resource Type (VARCHAR2)
97 */
98 FUNCTION resource_type (p_user_id NUMBER DEFAULT NULL)
99 RETURN VARCHAR2;
100
101 /**
102 * Returns the Resource Name given the Resource ID and Type.
103 *
104 * @param p_res_id Resource ID
105 * @param p_res_type Resource Type Code
106 * @returns Resource Name (VARCHAR2)
107 */
108 FUNCTION get_resource_name (p_res_id NUMBER, p_res_type VARCHAR2)
109 RETURN VARCHAR2;
110
111 /**
112 * Returns the Resource Type Name corresponding to the Resource Type Code
113 *
114 * @param p_res_type Resource Type Code
115 * @returns Resource Type Name (VARCHAR2)
116 */
117 FUNCTION get_resource_type_name (p_res_type VARCHAR2)
118 RETURN VARCHAR2;
119
120 /**
121 * Returns the Address of the Party created for the Resource as of the
122 * date passed.
123 *
124 * @param p_res_id Resource ID
125 * @param p_res_type Resource Type Code
126 * @param p_date Active Party Site for the given date
127 *
128 * @returns Party Address of the Resource
129 */
130 FUNCTION get_resource_party_address (
131 p_res_id NUMBER
132 , p_res_type VARCHAR2
133 , p_date DATE
134 )
135 RETURN csf_resource_address_pvt.address_rec_type;
136
137 /**
138 * Returns the Complete Resource Information given the Resource ID and Type.
139 * The returned record includes Resource Number and Resource Name.
140 *
141 * @param p_res_id Resource ID
142 * @param p_res_type Resource Type Code
143 * @returns Resource Information filled in RESOURCE_REC_TYPE
144 */
145 FUNCTION get_resource_info(p_res_id NUMBER, p_res_type VARCHAR2)
146 RETURN resource_rec_type;
147
148 /**
149 * Converts the given Time from Resource Timezone to Server Timezone
150 * or vice versa.
151 * <br>
152 * By default, the given date is assumed to be in Resource Timezone and the
153 * date returned is Server Timezone. Set p_server_to_resource parameter as
154 * 'T' (FND_API.G_TRUE) to make it return the other way round.
155 * <br>
156 * Note that the API doesnt support RS_TEAM or RS_GROUP resources.
157 *
158 * @param p_api_version API Version (1.0)
159 * @param p_init_msg_list Initialize Message List
160 * @param x_return_status Return Status of the Procedure.
161 * @param x_msg_count Number of Messages in the Stack.
162 * @param x_msg_data Stack of Error Messages.
163 * @param p_resource_id Resource ID
164 * @param p_resource_type Resource Type
165 * @param p_datetime Date to be converted
166 * @param p_server_to_resource Server to Resource Timezone
167 */
168 PROCEDURE convert_timezone (
169 p_api_version IN NUMBER
170 , p_init_msg_list IN VARCHAR2 DEFAULT NULL
171 , x_return_status OUT NOCOPY VARCHAR2
172 , x_msg_count OUT NOCOPY NUMBER
173 , x_msg_data OUT NOCOPY VARCHAR2
174 , p_resource_id IN NUMBER
175 , p_resource_type IN VARCHAR2
176 , x_datetime IN OUT NOCOPY DATE
177 , p_server_to_resource IN VARCHAR2 DEFAULT NULL
178 );
179
180 /**
181 * Returns the Qualifier Table having the list of valid Qualifiers
182 * based on the Task Information of the given Task ID.
183 */
184 FUNCTION get_res_qualifier_table(p_task_id NUMBER)
185 RETURN resource_qualifier_tbl_type;
186
187 /**
188 * Converts the given Qualifier Table to Assignment Manager API Record
189 * type.
190 * Assembles the selected Qualifiers for this Task from the Qualifier
191 * Table in to a Record Type understandable by JTF Assignment Manager.
192 * <br>
193 * Uses a Hard Coded Mapping between JTF_SEEDED_QUAL_USGS_V.QUAL_USG_ID
194 * and the fields in JTF_ASSIGN_PUB.JTF_SRV_TASK_REC_TYPE.
195 * <br>
196 * The Task and SR Number must be set by the caller and wont be set by
197 * this API. Moreover Qualifiers of type -1211, -1212 and -1218 have
198 * been disabled and therefore wont be set by this API.
199 *
200 * @param p_table Qualifier Table having the list of Task Qualifiers
201 */
202 FUNCTION get_qualified_task_rec(p_table resource_qualifier_tbl_type)
203 RETURN jtf_assign_pub.jtf_srv_task_rec_type;
204
205 /**
206 * Gets the Qualified Resources for a Task by calling JTF Assignment Manager
207 * and also making use of the Required Skills of the Task if Required to reduce
208 * the Resource List.
209 *
210 * <br>
211 *
212 * The reason for CSF to maintain its own Assignment Manager rather than
213 * completely relying on JTF Assignment Manager has two fold reasons.
214 * <br>
215 * <b>TQ is secondary for JTF Assignment Manager API.</b>
216 * Suppose in Schedule Advise Window, all the Flags are checked... then
217 * JTF Assignment Manager will give preference to Contracts and IB only.
218 * Only when both of returns ZERO resources, then JTF will consider TQ.
219 * But DC expects an intersection of the three results.
220 * Moreover if both Contracts and IB are checked, then JTF will use
221 * the profile "JTFAM: Resource Search Order (JTF_AM_PREF_RES_ORDER)" to
222 * find out which one to return ultimately. If the value CONTRACTS, then
223 * CONTRACTS - Only Contracts is returned. If None, IB is returned.
224 * IB - Only IB is returned. If None, Contracts is returned.
225 * BOTH - Intersection of Contracts and IB Resources are returned.
226 * <br>
227 * <b>JTF doesnt have the concept of Skills. </b>
228 * Resources and Skills is completely a Field Service Functionality. A
229 * Resource can be attached to a Skill with a particular Skill Level.
230 * So can a Task be tied to a Skill with a particular Skill Level. If
231 * Skill based Flag is checked, then the Resource needs to have the same
232 * Skill Set with a Comparable Skill Level as required by the Task.
233 * Comparable Skill Level !!! - What is that ?
234 * The profile "CSF: Skill Level Match (CSF_SKILL_LEVEL_MATCH)" is used
235 * to decide whether the Resource has the Required Skill Level as required
236 * by the Task.
237 * EQUAL TO OR SMALLER THAN - Resource should have a Skill Level equal to
238 * or lesser than that of the Task.
239 * EQUAL TO - Resource should have a Skill Level equal to
240 * that of the Task.
241 * EQUAL TO OR GREATER THAN - Resource should have a Skill Level equal to
242 * or greater than that of the Task.
243 * Note that the Task needs to have Skills. Otherwise the Flag wont be used
244 * at all for getting the Qualified Resources.
245 *
246 * <br>
247 *
248 * Thus CSF Assignment Manager API will call JTF Assignment Manager separately
249 * for Contracts / IB and then for Territory. Do an intersection of the Resources
250 * obtained thru the two calls and pruned by Skill Sets. Note that it gets
251 * Contracts / IB Resources from JTF in one call and so the user should make use
252 * the profile JTF_AM_PREF_RES_ORDER to get intersected results.
253 *
254 * <br>
255 * <b>Still to Implement</b>
256 * CSF Assignment Manager still doesnt pass the parameter P_FILTER_EXCLUDED_RESOURCE
257 * so that JTF Assignment Manager doesnt return Excluded Resources.
258 * CSF Assignment Manager still doesnt pass the parameter P_BUSINESS_PROCESS_ID
259 * so that JTF Assignment Manager returns only those Resources who belong to
260 * Field Service Business Process when Preferred Resources are entered in Contracts.
261 *
262 * @param p_api_version API Version (1.0)
263 * @param p_init_msg_list Initialize Message List
264 * @param x_return_status Return Status of the Procedure.
265 * @param x_msg_count Number of Messages in the Stack.
266 * @param x_msg_data Stack of Error Messages.
267 * @param p_task_id Task Identifier
268 * @param p_incident_id Service Request ID
269 * @param p_task_rec Qualified Task Record (Can be Empty)
270 * @param p_scheduling_mode Scheduling Mode used. (A, I, W, X)
271 * @param p_start Start Date of the Plan Window
272 * @param p_end End Date of the Plan Window
273 * @param p_duration Duration of the Task (Used by JTF to find out Available Resources)
274 * @param p_duration_uom UOM of the above Duration
275 * @param p_contracts_flag Get Contracts Preferred Resources ('Y'/'N')
276 * @param p_ib_flag Get IB Preferred Resources ('Y'/'N')
277 * @param p_territory_flag Get Winning Territory Resources ('Y'/'N')
278 * @param p_skill_flag Get Skilled Resources ('Y'/'N')
279 * @param p_calendar_flag Get only Available Resources. Passed to JTF ('Y'/'N')
280 * @param p_sort_flag Sort the Resources based on their distance from Task ('Y'/'N')
281 * @param p_suggested_res_id_tbl Suggested Resource ID Table
282 * @param p_suggested_res_type_tbl Suggested Resource Type Table
283 * @param x_res_tbl Qualified Resource suitable for Scheduling
284 */
285 PROCEDURE get_resources_to_schedule(
286 p_api_version IN NUMBER
287 , p_init_msg_list IN VARCHAR2 DEFAULT NULL
288 , x_return_status OUT NOCOPY VARCHAR2
289 , x_msg_count OUT NOCOPY NUMBER
290 , x_msg_data OUT NOCOPY VARCHAR2
291 , p_task_id IN NUMBER
292 , p_incident_id IN NUMBER
293 , p_res_qualifier_tbl IN resource_qualifier_tbl_type
294 , p_scheduling_mode IN VARCHAR2
295 , p_start IN DATE
296 , p_end IN DATE
297 , p_duration IN NUMBER DEFAULT NULL
298 , p_duration_uom IN VARCHAR2 DEFAULT NULL
299 , p_contracts_flag IN VARCHAR2 DEFAULT NULL
300 , p_ib_flag IN VARCHAR2 DEFAULT NULL
301 , p_territory_flag IN VARCHAR2 DEFAULT NULL
302 , p_skill_flag IN VARCHAR2 DEFAULT NULL
303 , p_calendar_flag IN VARCHAR2 DEFAULT NULL
304 , p_sort_flag IN VARCHAR2 DEFAULT NULL
305 , p_suggested_res_id_tbl IN jtf_number_table DEFAULT NULL
306 , p_suggested_res_type_tbl IN jtf_varchar2_table_100 DEFAULT NULL
307 , x_res_tbl OUT NOCOPY jtf_assign_pub.assignresources_tbl_type
308 );
309
310 /**
311 * Gets the Qualified Resources for a Task in a format understood by Request Model.
312 * <br>
313 * In turn calls the GET_RESOURCES_TO_SCHEDULE which gets the Resources in JTF
314 * Assignment Manager Format.
315 */
316 PROCEDURE get_resources_to_schedule(
317 p_api_version IN NUMBER
318 , p_init_msg_list IN VARCHAR2 DEFAULT NULL
319 , x_return_status OUT NOCOPY VARCHAR2
320 , x_msg_count OUT NOCOPY NUMBER
321 , x_msg_data OUT NOCOPY VARCHAR2
322 , p_task_id IN NUMBER
323 , p_incident_id IN NUMBER
324 , p_res_qualifier_tbl IN resource_qualifier_tbl_type
325 , p_scheduling_mode IN VARCHAR2
326 , p_start IN DATE
327 , p_end IN DATE
328 , p_duration IN NUMBER DEFAULT NULL
329 , p_duration_uom IN VARCHAR2 DEFAULT NULL
330 , p_contracts_flag IN VARCHAR2 DEFAULT NULL
331 , p_ib_flag IN VARCHAR2 DEFAULT NULL
332 , p_territory_flag IN VARCHAR2 DEFAULT NULL
333 , p_skill_flag IN VARCHAR2 DEFAULT NULL
334 , p_calendar_flag IN VARCHAR2 DEFAULT NULL
335 , p_sort_flag IN VARCHAR2 DEFAULT NULL
336 , p_suggested_res_id_tbl IN jtf_number_table DEFAULT NULL
337 , p_suggested_res_type_tbl IN jtf_varchar2_table_100 DEFAULT NULL
338 , x_res_tbl IN OUT NOCOPY csf_requests_pvt.resource_tbl_type
339 );
340
341 /**
342 * Gets the Shift Definitions of the given Resource between the two given Dates.
343 *
344 * CSF has its own "Get Resource Shifts" API in addition to JTF providing it is
345 * because CSF is still calling JTF Calendar API rather than JTF Calendar 24 API.
346 * Going forward, we should be calling JTF_CALENDAR24_PUB rather than
347 * JTF_CALENDAR_PUB.
348 * Because of this the following Shift Definition is returned as two Shifts.
349 * <br>
350 * Shift Construct #101: Start = 1-JAN-2005 18:00:00 to 2-JAN-2005 07:00:00
351 * is returned as
352 * Shift Record #1
353 * Shift Construct = 101
354 * Shift Date = 1-JAN-2005
355 * Start Time = 18:00
356 * End Time = 23:59
357 *
358 * Shift Record #2
359 * Shift Construct = 101
360 * Shift Date = 2-JAN-2005
361 * Start Time = 00:00
362 * End Time = 07:00
363 * <br>
364 * Note that Shift Record#1 and Shift Record#2 are adjacent in the returned
365 * Shifts Table. Morever both has the same Shift Construct ID and the difference
366 * between End Time of the first record and the start time of the second is
367 * One Minute (1/1440 days).
368 *
369 * This feature is being used by this API to merge those shifts in a single
370 * record structure.
371 *
372 * Note this API requires JTF_CALENDAR_PUB to be of version 115.86 as the issue
373 * with respect to Sorting of Shifts has been fixed in that version.
374 *
375 * @param p_api_version API Version (1.0)
376 * @param p_init_msg_list Initialize Message List
377 * @param x_return_status Return Status of the Procedure.
378 * @param x_msg_count Number of Messages in the Stack.
379 * @param x_msg_data Stack of Error Messages.
380 * @param p_resource_id Resource Identifier for whom Shifts are required.
381 * @param p_resource_type Resource Type of the above Resource.
382 * @param p_start_date Start of the Window between which Shifts are required.
383 * @param p_end_date End of the Window between which Shifts are required.
384 * @param x_shifts Shift Definitions
385 */
386 PROCEDURE get_resource_shifts(
387 p_api_version IN NUMBER
388 , p_init_msg_list IN VARCHAR2 DEFAULT NULL
389 , x_return_status OUT NOCOPY VARCHAR2
390 , x_msg_count OUT NOCOPY NUMBER
391 , x_msg_data OUT NOCOPY VARCHAR2
392 , p_resource_id IN NUMBER
393 , p_resource_type IN VARCHAR2
394 , p_start_date IN DATE
395 , p_end_date IN DATE
396 , x_shifts OUT NOCOPY shift_tbl_type
397 );
398
399 END csf_resource_pub;
[Home] [Help]