1 PACKAGE BODY csf_resource_pub AS
2 /* $Header: CSFPRESB.pls 120.20.12010000.3 2008/10/31 13:47:05 lokumar ship $ */
3
4 g_pkg_name CONSTANT VARCHAR2(30) := 'CSF_RESOURCE_PUB';
5 g_assign_doc_type CONSTANT VARCHAR2(2) := 'SR';
6 g_earth_radius CONSTANT NUMBER := 6378137;
7 g_pi CONSTANT NUMBER := 2 * ACOS(0);
8
9 TYPE qualifier_info_rec_type IS RECORD(
10 qual_usg_id jtf_seeded_qual_usgs_v.qual_usg_id%TYPE
11 , label jtf_seeded_qual_usgs_v.seeded_qual_name%TYPE
12 );
13
14 TYPE qualifier_info_tbl_type IS TABLE OF qualifier_info_rec_type
15 INDEX BY BINARY_INTEGER;
16
17 TYPE varchar2_tbl_type IS TABLE OF VARCHAR2(32)
18 INDEX BY BINARY_INTEGER;
19
20 g_assign_errors varchar2_tbl_type;
21 g_all_qualifiers qualifier_info_tbl_type;
22
23 TYPE res_type_name_rec_type IS RECORD(
24 resource_type_code jtf_objects_tl.object_code%TYPE
25 , resource_type_name jtf_objects_tl.NAME%TYPE
26 );
27
28 TYPE res_type_name_tbl_type IS
29 TABLE OF res_type_name_rec_type;
30
31 g_res_type_name_tbl res_type_name_tbl_type;
32
33 /**
34 * PLSQL Record Type to contain information about a Resource
35 * along with his Address. Note that this couldnt be put
36 * as an attribute as part of RESOURCE_REC_TYPE as Forms
37 * cant use Records which have Complex Data Types as attributes
38 */
39 TYPE resource_cache_rec_type IS RECORD(
40 resource_id NUMBER
41 , resource_type jtf_objects_b.object_code%TYPE
42 , resource_name jtf_rs_resource_extns_tl.resource_name%TYPE
43 , resource_number jtf_rs_resource_extns.resource_number%TYPE
44 , address csf_resource_address_pvt.address_rec_type
45 );
46
47 /**
48 * PLSQL Index By Table Type to contain information about many Resources
49 * where each element is of type RESOURCE_CACHE_REC_TYPE.
50 */
51 TYPE resource_cache_tbl_type IS TABLE OF resource_cache_rec_type
52 INDEX BY BINARY_INTEGER;
53
54 g_res_info_cache resource_cache_tbl_type;
55
56
57 /**
58 * Initializes the Table of possible Error Messages that can be
59 * encountered during Resource Selection Process.
60 * <br>
61 * Index to the table G_ASSIGN_ERRORS represent the flags that have
62 * been set to select the resources - namely Skills (S), Territories (T)
63 * Installed Base (I) and Contracts (B). When a Flag is set to 'Y', the
64 * corresponding bit is set to 1. Otherwise it is 0.
65 * <br>
66 * Examples:
67 * S T I C
68 * - - - -
69 * 0 0 0 0 No selection criteria
70 * 0 0 0 1 Only contracts
71 * 0 0 1 0 Only installed base
72 */
73 PROCEDURE init_assign_errors IS
74 BEGIN
75 g_assign_errors(0) := 'CSF_NO_RES_SEL_CRIT'; -- 0000
76 g_assign_errors(1) := 'CSF_NO_CONTRACT_RES'; -- 0001
77 g_assign_errors(2) := 'CSF_NO_IB_RES'; -- 0010
78 g_assign_errors(3) := 'CSF_NO_CONTRACT_IB_RES'; -- 0011
79 g_assign_errors(4) := 'CSF_NO_TERR_RES'; -- 0100
80 g_assign_errors(5) := 'CSF_TERR_CONTRACT_RES'; -- 0101
81 g_assign_errors(6) := 'CSF_NO_TERR_IB_RES'; -- 0110
82 g_assign_errors(8) := 'CSF_NO_SKILLED_RES'; -- 1000
83 g_assign_errors(9) := 'CSF_NO_CONTR_SKILL_RES'; -- 1001
84 g_assign_errors(10) := 'CSF_NO_IB_SKILL_RES'; -- 1010
85 g_assign_errors(11) := 'CSF_NO_CONTR_IB_SKILL_RES'; -- 1011
86 g_assign_errors(12) := 'CSF_NO_TERR_SKILL_RES'; -- 1100
87 g_assign_errors(13) := 'CSF_NO_TERR_CONTRACT_SKILL_RES'; -- 1101
88 g_assign_errors(14) := 'CSF_NO_TERR_IB_SKILL_RES'; -- 1110
89 g_assign_errors(15) := 'CSF_NO_TERR_CONTRACT_IB_RES'; -- 1111
90 END init_assign_errors;
91
92 /**
93 * Gets the Task Information in the format as desired by JTF Assignment
94 * Manager API.
95 *
96 * @param p_task_id Task Identifier
97 * @returns Task Record (JTF_ASSIGN_PUB.JTF_SRV_TASK_REC_TYPE).
98 */
99 FUNCTION get_srv_task_rec(p_task_id IN NUMBER)
100 RETURN jtf_assign_pub.jtf_srv_task_rec_type IS
101 l_rec jtf_assign_pub.jtf_srv_task_rec_type;
102 l_contract_service_id NUMBER;
103
104 -- Task, SR, Party and Address Information
105 CURSOR c_rec IS
106 SELECT tb.task_id task_id
107 , ib.incident_id service_request_id
108 , ib.customer_id party_id
109 , lo.country
110 , tb.address_id party_site_id
111 , lo.city
112 , lo.postal_code
113 , lo.state
114 , lo.county
115 , pa.party_name comp_name_range
116 , lo.province
117 , pa.employees_total num_of_employees
118 , tb.task_type_id
119 , tb.task_status_id
120 , tb.task_priority_id
121 , ib.incident_type_id
122 , ib.incident_severity_id
123 , ib.incident_urgency_id
124 , ib.problem_code
125 , ib.incident_status_id
126 , ib.platform_id
127 , ib.site_id support_site_id
128 , ib.customer_site_id
129 , it.sr_creation_channel
130 , ib.inventory_item_id
131 , ib.problem_code squal_char12
132 , ib.comm_pref_code squal_char13
133 , ib.platform_id squal_num12
134 , ib.inv_platform_org_id squal_num13
135 , ib.category_id squal_num14
136 , ib.inventory_item_id squal_num15
137 , ib.inv_organization_id squal_num16
138 , ib.owner_group_id squal_num17
139 , ib.language_id squal_num30
140 , ib.contract_service_id
141 FROM jtf_tasks_b tb
142 , cs_incidents_all_b ib
143 , cs_incidents_all_tl it
144 , hz_locations lo
145 , hz_parties pa
146 WHERE tb.task_id = p_task_id
147 AND tb.source_object_type_code = 'SR'
148 AND tb.source_object_id = ib.incident_id
149 AND tb.source_object_id = it.incident_id
150 AND it.LANGUAGE = USERENV('lang')
151 AND lo.location_id = csf_tasks_pub.get_task_location_id(tb.task_id, tb.address_id, tb.location_id)
152 AND ib.customer_id = pa.party_id(+);
153
154 -- Phone Area Code
155 CURSOR c_contact_point(b_party_id NUMBER) IS
156 SELECT phone_area_code
157 FROM hz_contact_points
158 WHERE owner_table_id = b_party_id
159 AND owner_table_name = 'HZ_PARTIES'
160 AND contact_point_type = 'PHONE'
161 AND primary_flag = 'Y';
162
163 -- Service Item ID and Organization ID
164 CURSOR c_contract(b_contract_service_id NUMBER) IS
165 SELECT TO_NUMBER(object1_id1) item_id
166 , TO_NUMBER(object1_id2) org_id
167 FROM okc_k_items
168 WHERE cle_id = b_contract_service_id;
169
170 -- Contact VIP code
171 CURSOR c_class_code(b_party_id NUMBER) IS
172 SELECT class_code
173 FROM hz_code_assignments
174 WHERE owner_table_name = 'HZ_PARTIES'
175 AND owner_table_id = b_party_id;
176 BEGIN
177 OPEN c_rec;
178
179 FETCH c_rec
180 INTO l_rec.task_id
181 , l_rec.service_request_id
182 , l_rec.party_id
183 , l_rec.country
184 , l_rec.party_site_id
185 , l_rec.city
186 , l_rec.postal_code
187 , l_rec.state
188 , l_rec.county
189 , l_rec.comp_name_range
190 , l_rec.province
191 , l_rec.num_of_employees
192 , l_rec.task_type_id
193 , l_rec.task_status_id
194 , l_rec.task_priority_id
195 , l_rec.incident_type_id
196 , l_rec.incident_severity_id
197 , l_rec.incident_urgency_id
198 , l_rec.problem_code
199 , l_rec.incident_status_id
200 , l_rec.platform_id
201 , l_rec.support_site_id
202 , l_rec.customer_site_id
203 , l_rec.sr_creation_channel
204 , l_rec.inventory_item_id
205 , l_rec.squal_char12
206 , l_rec.squal_char13
207 , l_rec.squal_num12
208 , l_rec.squal_num13
209 , l_rec.squal_num14
210 , l_rec.squal_num15
211 , l_rec.squal_num16
212 , l_rec.squal_num17
213 , l_rec.squal_num30
214 , l_contract_service_id;
215
216 IF c_rec%FOUND THEN
217 IF l_rec.party_id IS NOT NULL THEN
218 -- Contact Phone Area Code
219 OPEN c_contact_point(l_rec.party_id);
220 FETCH c_contact_point INTO l_rec.area_code;
221 CLOSE c_contact_point;
222
223 -- contact VIP code
224 OPEN c_class_code(l_rec.party_id);
225 FETCH c_class_code INTO l_rec.squal_char11;
226 CLOSE c_class_code;
227 END IF;
228
229 IF l_contract_service_id IS NOT NULL THEN
230 -- Service item item_id and org_id
231 OPEN c_contract(l_contract_service_id);
232 FETCH c_contract INTO l_rec.squal_num18, l_rec.squal_num19;
233 CLOSE c_contract;
234 END IF;
235 ELSE
236 -- fill in only the task_id
237 l_rec.task_id := p_task_id;
238 END IF;
239
240 CLOSE c_rec;
241
242 RETURN l_rec;
243 END get_srv_task_rec;
244
245 /**
246 * Gets the Seeded Enabled Qualifier Names (and if required Labels) and
247 * populates the table G_ALL_QUALIFIERS.
248 */
249 PROCEDURE get_all_qualifiers IS
250 k PLS_INTEGER;
251 CURSOR c_desc IS
252 SELECT qual_usg_id qual_usg_id
253 , seeded_qual_name label
254 FROM jty_all_enabled_attributes_v
255 WHERE source_id = -1002
256 AND qual_type_id IN(-1002, -1005, -1006)
257 ORDER BY UPPER(seeded_qual_name);
258 l_rec qualifier_info_rec_type;
259 BEGIN
260 g_all_qualifiers.DELETE;
261 OPEN c_desc;
262 LOOP
263 FETCH c_desc INTO l_rec;
264 EXIT WHEN c_desc%NOTFOUND;
265 g_all_qualifiers(c_desc%ROWCOUNT) := l_rec;
266 END LOOP;
267 CLOSE c_desc;
268 END get_all_qualifiers;
269
270 /**
271 * Returns the Display Value for the Qualifier.
272 *
273 * If Tracing is not enabled, then it returns only Value concatenated
274 * with the Associated Value. If Tracing is enabled, it uses the SQL
275 * associated with the Qualifier and gets the Name / Description for
276 * the Qualifier (ID to Name Conversion).
277 *
278 * @param p_index Index to the Global All Qualifiers Table
279 * @param p_value Value for the Qualifier
280 * @param p_associated_value Associated Value for the Qualifier
281 */
282 FUNCTION get_display_value(
283 p_index IN PLS_INTEGER
284 , p_value IN VARCHAR2
285 , p_associated_value IN VARCHAR2
286 )
287 RETURN VARCHAR2 IS
288 l_tmp VARCHAR2(4000);
289 l_value VARCHAR2(360);
290 BEGIN
291 -- for the default case use the provided values
292 IF p_associated_value IS NULL THEN
293 l_value := p_value;
294 ELSE
295 l_value := SUBSTR(p_value || '/' || p_associated_value, 1, 360);
296 END IF;
297 RETURN l_value;
298 END get_display_value;
299
300 PROCEDURE set_generic_planwindow (
301 p_res_tbl IN OUT NOCOPY jtf_assign_pub.assignresources_tbl_type
302 , p_start DATE
303 , p_end DATE
304 ) IS
305 i PLS_INTEGER;
306 BEGIN
307 i := p_res_tbl.FIRST;
308 WHILE i IS NOT NULL LOOP
309 p_res_tbl(i).start_date := p_start;
310 p_res_tbl(i).end_date := p_end;
311 i := p_res_tbl.NEXT(i);
312 END LOOP;
313 END set_generic_planwindow;
314
315 /**
316 * Adds the Qualifier identified by the Index to the global Task Qualifer Table with
317 * the value taken from the given Task Record and only when the value is Not Null.
318 * <br>
319 * Uses a Hard Coded Mapping between JTF_SEEDED_QUAL_USGS_V.QUAL_USG_ID
320 * and the fields in JTF_ASSIGN_PUB.JTF_SRV_TASK_REC_TYPE.
321 * <br>
322 * Qualifiers of type -1211, -1212 and -1218 have been disabled and
323 * therefore wont be set by this API.
324 *
325 * @param p_qualifiers Qualifier Table storing the Qualifiers
326 * @param p_index Index to the Global All Qualifiers Table
327 * @param p_task_rec Task Record containing the required Information
328 */
329 PROCEDURE add_qualifier(
330 p_qualifier_tbl IN OUT NOCOPY resource_qualifier_tbl_type
331 , p_index IN PLS_INTEGER
332 , p_task_rec IN jtf_assign_pub.jtf_srv_task_rec_type
333 ) IS
334 l_value VARCHAR2(360);
335 l_associated_value VARCHAR2(360);
336 i PLS_INTEGER;
337 BEGIN
338 IF g_all_qualifiers(p_index).qual_usg_id = -1037 THEN
339 l_value := p_task_rec.party_id;
340 ELSIF g_all_qualifiers(p_index).qual_usg_id = -1038 THEN
341 l_value := p_task_rec.country;
342 ELSIF g_all_qualifiers(p_index).qual_usg_id = -1039 THEN
343 l_value := p_task_rec.party_site_id;
344 ELSIF g_all_qualifiers(p_index).qual_usg_id = -1040 THEN
345 l_value := p_task_rec.city;
346 ELSIF g_all_qualifiers(p_index).qual_usg_id = -1041 THEN
347 l_value := p_task_rec.postal_code;
348 ELSIF g_all_qualifiers(p_index).qual_usg_id = -1042 THEN
349 l_value := p_task_rec.state;
350 ELSIF g_all_qualifiers(p_index).qual_usg_id = -1043 THEN
351 l_value := p_task_rec.area_code;
352 ELSIF g_all_qualifiers(p_index).qual_usg_id = -1044 THEN
353 l_value := p_task_rec.county;
354 ELSIF g_all_qualifiers(p_index).qual_usg_id = -1045 THEN
355 l_value := p_task_rec.comp_name_range;
356 ELSIF g_all_qualifiers(p_index).qual_usg_id = -1046 THEN
357 l_value := p_task_rec.province;
358 ELSIF g_all_qualifiers(p_index).qual_usg_id = -1047 THEN
359 l_value := p_task_rec.num_of_employees;
360 ELSIF g_all_qualifiers(p_index).qual_usg_id = -1060 THEN
361 l_value := p_task_rec.task_type_id;
362 ELSIF g_all_qualifiers(p_index).qual_usg_id = -1061 THEN
363 l_value := p_task_rec.task_status_id;
364 ELSIF g_all_qualifiers(p_index).qual_usg_id = -1062 THEN
365 l_value := p_task_rec.task_priority_id;
366 ELSIF g_all_qualifiers(p_index).qual_usg_id = -1048 THEN
367 l_value := p_task_rec.incident_type_id;
368 ELSIF g_all_qualifiers(p_index).qual_usg_id = -1049 THEN
369 l_value := p_task_rec.incident_severity_id;
370 ELSIF g_all_qualifiers(p_index).qual_usg_id = -1050 THEN
371 l_value := p_task_rec.incident_urgency_id;
372 ELSIF g_all_qualifiers(p_index).qual_usg_id = -1051 THEN
373 l_value := p_task_rec.problem_code;
374 ELSIF g_all_qualifiers(p_index).qual_usg_id = -1091 THEN
375 l_value := p_task_rec.incident_status_id;
376 ELSIF g_all_qualifiers(p_index).qual_usg_id = -1092 THEN
377 l_value := p_task_rec.platform_id;
378 ELSIF g_all_qualifiers(p_index).qual_usg_id = -1093 THEN
379 l_value := p_task_rec.support_site_id;
380 ELSIF g_all_qualifiers(p_index).qual_usg_id = -1094 THEN
381 l_value := p_task_rec.customer_site_id;
382 ELSIF g_all_qualifiers(p_index).qual_usg_id = -1095 THEN
383 l_value := p_task_rec.sr_creation_channel;
384 ELSIF g_all_qualifiers(p_index).qual_usg_id = -1096 THEN
385 l_value := p_task_rec.inventory_item_id;
386 ELSIF g_all_qualifiers(p_index).qual_usg_id = -1210 THEN
387 l_value := p_task_rec.squal_num14;
388 ELSIF g_all_qualifiers(p_index).qual_usg_id = -1216 THEN
389 l_value := p_task_rec.squal_num18;
390 l_associated_value := p_task_rec.squal_num19;
391 ELSIF g_all_qualifiers(p_index).qual_usg_id = -1213 THEN
392 l_value := p_task_rec.squal_num30;
393 ELSIF g_all_qualifiers(p_index).qual_usg_id = -1215 THEN
394 l_value := p_task_rec.squal_char11;
395 ELSIF g_all_qualifiers(p_index).qual_usg_id = -1214 THEN
396 l_value := p_task_rec.squal_char13;
397 ELSIF g_all_qualifiers(p_index).qual_usg_id = -1217 THEN
398 l_value := p_task_rec.squal_num17;
399 END IF;
400
401 IF l_value IS NOT NULL THEN
402 i := p_qualifier_tbl.COUNT + 1;
403 p_qualifier_tbl(i).qual_usg_id := g_all_qualifiers(p_index).qual_usg_id;
404 p_qualifier_tbl(i).label := g_all_qualifiers(p_index).label;
405 p_qualifier_tbl(i).use_flag := 'Y';
406 p_qualifier_tbl(i).value := l_value;
407 p_qualifier_tbl(i).associated_value := l_associated_value;
408 p_qualifier_tbl(i).display_value := get_display_value(p_index, l_value, l_associated_value);
409 END IF;
410 END add_qualifier;
411
412 /**
413 * Returns the Qualifier Table having the list of valid Qualifiers
414 * based on the Task Information of the given Task ID.
415 */
416 FUNCTION get_res_qualifier_table(p_task_id NUMBER)
417 RETURN resource_qualifier_tbl_type IS
418 m PLS_INTEGER;
419 l_task_rec jtf_assign_pub.jtf_srv_task_rec_type;
420 l_qualifier_tbl resource_qualifier_tbl_type;
421 BEGIN
422 -- Fetch all the Enabled Qualifiers
423 IF g_all_qualifiers.COUNT = 0 THEN
424 get_all_qualifiers;
425 END IF;
426
427 -- Get the Task Information
428 l_task_rec := get_srv_task_rec(p_task_id);
429
430 -- Loop thru the Qualifiers and add the valid Qualifier alone to the Table
431 m := g_all_qualifiers.FIRST;
432 WHILE m IS NOT NULL LOOP
433 add_qualifier(l_qualifier_tbl, m, l_task_rec);
434 m := g_all_qualifiers.NEXT(m);
435 END LOOP;
436
437 RETURN l_qualifier_tbl;
438 END get_res_qualifier_table;
439
440 /**
441 * Converts the given Qualifier Table to Assignment Manager API Record
442 * type.
443 * Assembles the selected Qualifiers for this Task from the Qualifier
444 * Table in to a Record Type understandable by JTF Assignment Manager.
445 * <br>
446 * Uses a Hard Coded Mapping between JTF_SEEDED_QUAL_USGS_V.QUAL_USG_ID
447 * and the fields in JTF_ASSIGN_PUB.JTF_SRV_TASK_REC_TYPE.
448 * <br>
449 * The Task and SR Number must be set by the caller and wont be set by
450 * this API. Moreover Qualifiers of type -1211, -1212 and -1218 have
451 * been disabled and therefore wont be set by this API.
452 *
453 * @param p_table Qualifier Table having the list of Task Qualifiers
454 */
455 FUNCTION get_qualified_task_rec(p_table resource_qualifier_tbl_type)
456 RETURN jtf_assign_pub.jtf_srv_task_rec_type IS
457 k PLS_INTEGER;
458 task_rec jtf_assign_pub.jtf_srv_task_rec_type;
459 BEGIN
460 k := p_table.FIRST;
461 WHILE k IS NOT NULL LOOP
462 IF p_table(k).use_flag = 'Y' THEN
463 IF p_table(k).qual_usg_id = -1037 THEN
464 task_rec.party_id := p_table(k).VALUE;
465 ELSIF p_table(k).qual_usg_id = -1038 THEN
466 task_rec.country := p_table(k).VALUE;
467 ELSIF p_table(k).qual_usg_id = -1039 THEN
468 task_rec.party_site_id := p_table(k).VALUE;
469 ELSIF p_table(k).qual_usg_id = -1040 THEN
470 task_rec.city := p_table(k).VALUE;
471 ELSIF p_table(k).qual_usg_id = -1041 THEN
472 task_rec.postal_code := p_table(k).VALUE;
473 ELSIF p_table(k).qual_usg_id = -1042 THEN
474 task_rec.state := p_table(k).VALUE;
475 ELSIF p_table(k).qual_usg_id = -1043 THEN
476 task_rec.area_code := p_table(k).VALUE;
477 ELSIF p_table(k).qual_usg_id = -1044 THEN
478 task_rec.county := p_table(k).VALUE;
479 ELSIF p_table(k).qual_usg_id = -1045 THEN
480 task_rec.comp_name_range := p_table(k).VALUE;
481 ELSIF p_table(k).qual_usg_id = -1046 THEN
482 task_rec.province := p_table(k).VALUE;
483 ELSIF p_table(k).qual_usg_id = -1047 THEN
484 task_rec.num_of_employees := p_table(k).VALUE;
485 ELSIF p_table(k).qual_usg_id = -1060 THEN
486 task_rec.task_type_id := p_table(k).VALUE;
487 ELSIF p_table(k).qual_usg_id = -1061 THEN
488 task_rec.task_status_id := p_table(k).VALUE;
489 ELSIF p_table(k).qual_usg_id = -1062 THEN
490 task_rec.task_priority_id := p_table(k).VALUE;
491 ELSIF p_table(k).qual_usg_id = -1048 THEN
492 task_rec.incident_type_id := p_table(k).VALUE;
493 ELSIF p_table(k).qual_usg_id = -1049 THEN
494 task_rec.incident_severity_id := p_table(k).VALUE;
495 ELSIF p_table(k).qual_usg_id = -1050 THEN
496 task_rec.incident_urgency_id := p_table(k).VALUE;
497 ELSIF p_table(k).qual_usg_id = -1051 THEN
498 task_rec.problem_code := p_table(k).VALUE;
499 ELSIF p_table(k).qual_usg_id = -1091 THEN
500 task_rec.incident_status_id := p_table(k).VALUE;
501 ELSIF p_table(k).qual_usg_id = -1092 THEN
502 task_rec.platform_id := p_table(k).VALUE;
503 ELSIF p_table(k).qual_usg_id = -1093 THEN
504 task_rec.support_site_id := p_table(k).VALUE;
505 ELSIF p_table(k).qual_usg_id = -1094 THEN
506 task_rec.customer_site_id := p_table(k).VALUE;
507 ELSIF p_table(k).qual_usg_id = -1095 THEN
508 task_rec.sr_creation_channel := p_table(k).VALUE;
509 ELSIF p_table(k).qual_usg_id = -1096 THEN
510 task_rec.inventory_item_id := p_table(k).VALUE;
511 ELSIF p_table(k).qual_usg_id = -1210 THEN
512 task_rec.squal_num14 := p_table(k).VALUE;
513 ELSIF p_table(k).qual_usg_id = -1217 THEN
514 task_rec.squal_num17 := p_table(k).VALUE;
515 ELSIF p_table(k).qual_usg_id = -1216 THEN
516 task_rec.squal_num18 := p_table(k).VALUE;
517 task_rec.squal_num19 := p_table(k).associated_value;
518 ELSIF p_table(k).qual_usg_id = -1213 THEN
519 task_rec.squal_num30 := p_table(k).VALUE;
520 ELSIF p_table(k).qual_usg_id = -1215 THEN
521 task_rec.squal_char11 := p_table(k).VALUE;
522 ELSIF p_table(k).qual_usg_id = -1214 THEN
523 task_rec.squal_char13 := p_table(k).VALUE;
524 END IF;
525 END IF;
526 k := p_table.NEXT(k);
527 END LOOP;
528 RETURN task_rec;
529 END get_qualified_task_rec;
530
531 /**
532 * Intersect the Resource results found in two tables and returns a
533 * Table having only those Resources found in both the Tables.
534 *
535 * The parameters P_START and P_END signify the Plan Window for which
536 * Resources are desired. If either of the input tables doesnt have
537 * the Plan Window stamped for any resource, these two dates will be used
538 * in place of them. After this action, the most restrictive Plan Window
539 * among the two windows is used as the Plan Window of the output table.
540 * <br>
541 * Side Effect of the API. Second Table may have entries deleted after
542 * the operation.
543 *
544 * @param p_res_1_tbl Resource Table 1
545 * @param p_res_2_tbl Resource Table 2
546 * @param p_start Start Date of the Window
547 * @param p_end End Date of the Window
548 * @return Common Resource Table (JTF_ASSIGN_PUB.ASSIGNRESOURCES_TBL_TYPE)
549 */
550 FUNCTION intersect_results(
551 p_res_1_tbl IN OUT NOCOPY jtf_assign_pub.assignresources_tbl_type
552 , p_res_2_tbl IN OUT NOCOPY jtf_assign_pub.assignresources_tbl_type
553 , p_start DATE
554 , p_end DATE
555 ) RETURN jtf_assign_pub.assignresources_tbl_type IS
556 i PLS_INTEGER;
557 j PLS_INTEGER;
558 k PLS_INTEGER;
559 l_res_tbl jtf_assign_pub.assignresources_tbl_type;
560 BEGIN
561 k := 0;
562 i := p_res_1_tbl.FIRST;
563 WHILE i IS NOT NULL LOOP
564 j := p_res_2_tbl.FIRST;
565 WHILE j IS NOT NULL LOOP
566 EXIT WHEN p_res_2_tbl(j).resource_id = p_res_1_tbl(i).resource_id
567 AND p_res_2_tbl(j).resource_type = p_res_1_tbl(i).resource_type;
568 j := p_res_2_tbl.NEXT(j);
569 END LOOP;
570
571 -- We have an intersection between first table and second. Add to the output table
572 IF j IS NOT NULL THEN
573 k := k + 1;
574 l_res_tbl(k) := p_res_1_tbl(i);
575 l_res_tbl(k).start_date := GREATEST( NVL(p_res_1_tbl(i).start_date, p_start)
576 , NVL(p_res_2_tbl(j).start_date, p_start)
577 );
578 l_res_tbl(k).end_date := LEAST( NVL(p_res_1_tbl(i).end_date, p_end)
579 , NVL(p_res_2_tbl(j).end_date, p_end)
580 );
581 p_res_2_tbl.DELETE(j); -- So that Table is smaller for other iterations.
582 END IF;
583 i := p_res_1_tbl.NEXT(i);
584 END LOOP;
585 RETURN l_res_tbl;
586 END intersect_results;
587
588 /**
589 * Returns the Skilled Resources for a Task overlapping the given
590 * Plan Window.
591 * <br>
592 * This API will return the list of all Skilled Resources for the Task
593 * with the activity date lying between the passed Plan Window. There
594 * will be an individual plan window (Adapted) for each resource.
595 * <br>
596 * If Resource ID is passed, then the API returns only one record
597 * corresponding to the passed Resource if he has the Skill Active
598 * during the given Times as desired by the Task.
599 * <br>
600 * The API makes use of the profile CSF_SKILL_LEVEL_MATCH to determine
601 * whether the Resource has a Skill Level comparable with the Task requirements
602 * as dictated by the profile.
603 * Usage of the Profile is as follows
604 * 1 - EQUAL TO or SMALLER THAN
605 * 2 - EQUAL TO --> (Default Value)
606 * 3 - EQUAL TO or GREATER THAN
607 * <br>
608 * Note that the API is made a PROCEDURE from its initial version of being a
609 * FUNCTION so that we can use the NOCOPY Compiler Directive and avoid the
610 * expensive Table Copy Happening during each call.
611 *
612 * @param p_task_id Task ID of the Task to be considered
613 * @param p_start Start Date Time of the Window
614 * @param p_end End Date Time of the Window
615 * @param p_resource_id Resource ID (Optional)
616 * @param p_resource_type Resource Type (Optional)
617 * @param x_skilled_res_tbl Skilled Resource Table
618 */
619 PROCEDURE get_skilled_resources(
620 p_task_id IN NUMBER
621 , p_start IN DATE
622 , p_end IN DATE
623 , p_resource_id IN NUMBER DEFAULT NULL
624 , p_resource_type IN VARCHAR2 DEFAULT NULL
625 , x_skilled_res_tbl OUT NOCOPY jtf_assign_pub.assignresources_tbl_type
626 ) IS
627 l_levelmatch CONSTANT NUMBER := NVL(fnd_profile.VALUE('CSF_SKILL_LEVEL_MATCH'), 2);
628 j PLS_INTEGER;
629
630 CURSOR c_resource_plan_window IS
631 SELECT rs.resource_id
632 , rs.resource_type
633 , rs.winstart
634 , rs.winend
635 , rs.amount
636 FROM (SELECT rs.resource_id
637 , rs.resource_type
638 , GREATEST(MAX(rs.start_date_active), NVL(MAX(ss.start_date_active), p_start)
639 , p_start) winstart
640 , LEAST(
641 NVL(MIN(rs.end_date_active + 1), p_end)
642 , NVL(MIN(ss.end_date_active + 1), p_end)
643 , p_end
644 ) winend
645 , COUNT(*) amount
646 FROM csf_resource_skills_b rs
647 , csf_required_skills_b ts
648 , csf_skill_levels_b rsl
649 , csf_skill_levels_b tsl
650 , csf_skills_b ss
651 WHERE DECODE(
652 SIGN(rsl.step_value - tsl.step_value)
653 , -1, DECODE(l_levelmatch, 1, 'Y', 'N')
654 , 0, 'Y'
655 , 1, DECODE(l_levelmatch, 3, 'Y', 'N')
656 ) = 'Y'
657 AND rsl.skill_level_id = rs.skill_level_id
658 AND tsl.skill_level_id = ts.skill_level_id
659 AND ts.skill_id = rs.skill_id
660 AND ts.skill_type_id = rs.skill_type_id
661 AND TRUNC(rs.start_date_active) < p_end
662 AND (TRUNC(rs.end_date_active + 1) > p_start OR rs.end_date_active IS NULL)
663 AND (rs.resource_id = p_resource_id OR p_resource_id IS NULL)
664 AND (rs.resource_type = p_resource_type OR p_resource_type IS NULL)
665 AND NVL(ts.disabled_flag, 'N') <> 'Y'
666 AND ts.has_skill_type = 'TASK'
667 AND ts.has_skill_id = p_task_id
668 AND ss.skill_id(+) = rs.skill_id
669 AND (
670 (
671 rs.skill_type_id <> 2
672 AND TRUNC(ss.start_date_active) < p_end
673 AND (TRUNC(ss.end_date_active + 1) > p_start OR ss.end_date_active IS NULL)
674 )
675 OR EXISTS(SELECT 1
676 FROM mtl_system_items_kfv msi
677 WHERE msi.inventory_item_id = ts.skill_id)
678 )
679 GROUP BY rs.resource_id, rs.resource_type) rs
680 , (SELECT COUNT(*) amount
681 FROM csf_required_skills_b
682 WHERE NVL(disabled_flag, 'N') <> 'Y' AND has_skill_type = 'TASK'
683 AND has_skill_id = p_task_id) ts
684 WHERE rs.amount = ts.amount AND rs.winstart < rs.winend;
685 BEGIN
686 x_skilled_res_tbl.DELETE;
687 j := 0;
688 FOR i IN c_resource_plan_window LOOP
689 j := j + 1;
690 x_skilled_res_tbl(j).resource_id := i.resource_id;
691 x_skilled_res_tbl(j).resource_type := i.resource_type;
692 x_skilled_res_tbl(j).start_date := i.winstart;
693 x_skilled_res_tbl(j).end_date := i.winend;
694 x_skilled_res_tbl(j).terr_id := -1;
695 x_skilled_res_tbl(j).terr_rank := -1;
696 END LOOP;
697 END get_skilled_resources;
698
699 FUNCTION geo_distance(p_lon1 NUMBER, p_lat1 NUMBER, p_lon2 NUMBER, p_lat2 NUMBER)
700 RETURN NUMBER IS
701 l_north NUMBER;
702 l_east NUMBER;
703 BEGIN
704 l_north := ( (p_lat2*g_pi/180.0) - (p_lat1*g_pi/180.0) ) * g_earth_radius;
705 l_east := ( ( (p_lon2*g_pi/180.0) -(p_lon1*g_pi/180.0) ) * COS(p_lat2* g_pi/180.0) )
706 * g_earth_radius;
707 RETURN SQRT ( (l_north * l_north) + (l_east * l_east) );
708 END geo_distance;
709
710 /**
711 * Sorts the Resources in the given list based on their distance to the given
712 * Task from their Home Address.
713 *
714 * If the Task doesnt have a valid Geometry, then the API doesnt do anything. It
715 * merely returns the original list of resources without sorting.
716 * <br>
717 * Each Resource in the given list is picked up iteratively and its Geo-Distance
718 * from the Task is computed using the Geometry of the Task and that of the
719 * Resource Home Address (Location Finder will be invoked if necessary).
720 *
721 * @param p_unsorted_res_tbl List of UnSorted Resources
722 * @param p_task_id Task ID of the Task to be performed
723 * @param p_start Start of the Window to get that Period's Address
724 * @param p_end End of the Window to get that Period's Address
725 * @returns Sorted Resources List (JTF_ASSIGN_PUB.ASSIGNRESOURCES_TBL_TYPE)
726 */
727 FUNCTION sort_resource_by_distance(
728 p_unsorted_res_tbl jtf_assign_pub.assignresources_tbl_type
729 , p_task_id NUMBER
730 , p_start DATE
731 , p_end DATE
732 )
733 RETURN jtf_assign_pub.assignresources_tbl_type IS
734 l_return_status VARCHAR2(1);
735 l_msg_count NUMBER;
736 l_msg_data VARCHAR2(2000);
737 l_sorted_res_tbl jtf_assign_pub.assignresources_tbl_type;
738 l_res_dist_tbl csf_resource_tbl;
739 i PLS_INTEGER;
740 j PLS_INTEGER;
741 l_pref_res_cnt PLS_INTEGER;
742 l_address csf_resource_address_pvt.address_rec_type;
743 l_geometry MDSYS.SDO_GEOMETRY;
744 l_task_lat NUMBER;
745 l_task_lon NUMBER;
746 l_res_lat NUMBER;
747 l_res_lon NUMBER;
748 l_valid_geo VARCHAR2(5);
749 l_distance NUMBER;
750
751 CURSOR c_task_geometry IS
752 SELECT l.geometry
753 FROM jtf_tasks_b t, hz_locations l
754 WHERE t.task_id = p_task_id
755 AND l.location_id = csf_tasks_pub.get_task_location_id(t.task_id, t.address_id, t.location_id);
756
757 CURSOR c_sorted_resources IS
758 SELECT resource_index
759 , distance
760 FROM TABLE(CAST(l_res_dist_tbl AS csf_resource_tbl) )
761 ORDER BY preferred_resource_flag desc, distance, resource_index;
762 BEGIN
763 -- Validate the Geometry of the Task.
764 -- If Task has no or invalid geometry, no need to sort by distance at all
765 OPEN c_task_geometry;
766 FETCH c_task_geometry INTO l_geometry;
767 CLOSE c_task_geometry;
768
769 IF l_geometry IS NULL THEN
770 RETURN p_unsorted_res_tbl;
771 END IF;
772 csf_locus_pub.verify_locus(
773 p_api_version => 1.0
774 , x_msg_count => l_msg_count
775 , x_msg_data => l_msg_data
776 , x_return_status => l_return_status
777 , p_locus => l_geometry
778 , x_result => l_valid_geo
779 );
780
781 IF l_valid_geo = 'FALSE' THEN
782 RETURN p_unsorted_res_tbl;
783 END IF;
784
785 l_task_lon := l_geometry.sdo_ordinates(1);
786 l_task_lat := l_geometry.sdo_ordinates(2);
787 l_res_dist_tbl := csf_resource_tbl();
788 i := p_unsorted_res_tbl.FIRST;
789 l_pref_res_cnt := 1;
790 WHILE i IS NOT NULL LOOP
791 l_address := get_resource_party_address(
792 p_res_id => p_unsorted_res_tbl(i).resource_id
793 , p_res_type => p_unsorted_res_tbl(i).resource_type
794 , p_date => p_start
795 );
796 l_distance := csf_util_pvt.get_miss_num;
797
798 -- Fetch the Geometry corresponding to the Address
799 IF l_address.geometry IS NOT NULL THEN
800 csf_locus_pub.verify_locus(p_api_version => 1.0
801 , p_locus => l_address.geometry
802 , x_msg_count => l_msg_count
803 , x_msg_data => l_msg_data
804 , x_return_status => l_return_status
805 , x_result => l_valid_geo
806 );
807 -- Compute the Distance between the Resource and the Task
808 IF l_valid_geo = 'TRUE' THEN
809 l_res_lon := l_address.geometry.sdo_ordinates(1);
810 l_res_lat := l_address.geometry.sdo_ordinates(2);
811 l_distance := geo_distance(l_task_lon, l_task_lat, l_res_lon, l_res_lat);
812 END IF;
813 END IF;
814 l_res_dist_tbl.EXTEND;
815
816 IF p_unsorted_res_tbl(i).preference_type = 'I' OR p_unsorted_res_tbl(i).preference_type = 'C' THEN
817 l_res_dist_tbl(i) := csf_resource('Y', i, l_distance,null,null,null,null,null);
818 ELSE
819 l_res_dist_tbl(i) := csf_resource('N', i, l_distance,null,null,null,null,null);
820 END IF;
821
822 i := p_unsorted_res_tbl.NEXT(i);
823 END LOOP;
824
825 i := 0;
826 FOR v_resource IN c_sorted_resources LOOP
827 i := i + 1;
828 l_sorted_res_tbl(i) := p_unsorted_res_tbl(v_resource.resource_index);
829 END LOOP;
830 RETURN l_sorted_res_tbl;
831 EXCEPTION
832 WHEN OTHERS THEN
833 RETURN p_unsorted_res_tbl;
834 END sort_resource_by_distance;
835
836 /**
837 * Reduce the number of Resources passed to a maximum value as determined by
838 * the profile "CSR: Maximum number of Resources".
839 *
840 * The API doesnt delete resources as such. It first prunes the resource list
841 * by considering only those Resources who have valid Shift Definitions between
842 * the given dates and then give the top N resources. Note that it will be
843 * better if the Resources are already sorted in the order of their preference.
844 *
845 * @param p_res_tbl List of Sorted Resources
846 * @param p_start Start of the Window for Valid Shifts Consideration
847 * @param p_end End of the Window for Valid Shifts Consideration
848 * @returns Top N Resources as determined by the MAXNRSELECTEDRES profile.
849 */
850 FUNCTION reduce_resource_list(
851 p_res_tbl IN OUT NOCOPY jtf_assign_pub.assignresources_tbl_type
852 , p_start DATE
853 , p_end DATE
854 )
855 RETURN jtf_assign_pub.assignresources_tbl_type IS
856 l_max_resources NUMBER;
857 l_return_status VARCHAR2(1);
858 l_msg_count NUMBER;
859 l_msg_data VARCHAR2(2000);
860 l_shift_tbl jtf_calendar_pub.shift_tbl_type;
861 l_out_tbl jtf_assign_pub.assignresources_tbl_type;
862 i PLS_INTEGER;
863 j PLS_INTEGER;
864 cnt PLS_INTEGER;
865 BEGIN
866 -- Get the maximum number of allowed Resources
867 l_max_resources := fnd_profile.VALUE('MAXNRSELECTEDRES');
868
869 -- Validate retrieved maximum value
870 IF NVL(l_max_resources, 0) <= 0 THEN
871 l_max_resources := p_res_tbl.COUNT;
872 END IF;
873
874 i := p_res_tbl.FIRST;
875 j := 0;
876 WHILE i IS NOT NULL LOOP
877 cnt:= p_res_tbl.NEXT(i);
878 WHILE cnt <= p_res_tbl.LAST LOOP
879 IF p_res_tbl(i).resource_id = p_res_tbl(cnt).resource_id AND
880 p_res_tbl(i).resource_type = p_res_tbl(cnt).resource_type THEN
881 p_res_tbl.delete(cnt);
882 END IF;
883 cnt:= p_res_tbl.next(cnt);
884 END LOOP;
885
886 jtf_calendar_pub.get_resource_shifts(
887 p_api_version => 1
888 , p_init_msg_list => fnd_api.g_true
889 , x_return_status => l_return_status
890 , x_msg_count => l_msg_count
891 , x_msg_data => l_msg_data
892 , p_resource_id => p_res_tbl(i).resource_id
893 , p_resource_type => p_res_tbl(i).resource_type
894 , p_start_date => p_start
895 , p_end_date => p_end
896 , x_shift => l_shift_tbl
897 );
898 IF l_return_status = fnd_api.g_ret_sts_success OR l_shift_tbl.COUNT > 0 THEN
899 j := j + 1;
900 l_out_tbl(j) := p_res_tbl(i);
901 EXIT WHEN j = l_max_resources;
902 END IF;
903 i := p_res_tbl.NEXT(i);
904 END LOOP;
905 RETURN l_out_tbl;
906 EXCEPTION
907 WHEN OTHERS THEN
908 RETURN p_res_tbl;
909 END reduce_resource_list;
910
911 /**
912 * Returns the Error Message that is encountered during Resource Selection
913 * process based on the Flags passed. Each Flag corresponds to a But in the
914 * Error Message Table. See INIT_ASSIGN_ERRORS for more information.
915 *
916 * @param p_flags Flags used to select Resources for the Task
917 * @return Name of the Error Message encountered.
918 */
919 FUNCTION get_assign_error_msg(p_flags IN NUMBER)
920 RETURN VARCHAR2 IS
921 l_msg VARCHAR2(100);
922 BEGIN
923 l_msg := 'CSF_R_NO_RES_FOR_TASK';
924 IF p_flags IS NOT NULL AND g_assign_errors.EXISTS(p_flags) THEN
925 l_msg := g_assign_errors(p_flags);
926 END IF;
927 RETURN l_msg;
928 END get_assign_error_msg;
929
930 /**
931 * Adds the suggested resources to the end of the given resource table if
932 * the resource is not already present in the resource table.
933 * @param p_res_tbl Resource Table and where suggested res will be added
934 * @param p_suggested_res_id_tbl Suggested Resource ID Table
935 * @param p_suggested_res_type_tbl Suggested Resource Type Table
936 */
937 PROCEDURE add_suggested_resources(
938 p_res_tbl IN OUT NOCOPY jtf_assign_pub.assignresources_tbl_type
939 , p_suggested_res_id_tbl IN jtf_number_table
940 , p_suggested_res_type_tbl IN jtf_varchar2_table_100
941 , p_start_date IN DATE
942 , p_end_date IN DATE
943 ) IS
944 i PLS_INTEGER;
945 j PLS_INTEGER;
946 l_res_found BOOLEAN;
947 BEGIN
948 -- Validate the Inputs.
949 IF p_suggested_res_id_tbl IS NULL
950 OR p_suggested_res_type_tbl IS NULL
951 OR p_suggested_res_id_tbl.COUNT <= 0
952 OR p_suggested_res_type_tbl.COUNT <> p_suggested_res_id_tbl.COUNT
953 THEN
954 RETURN;
955 END IF;
956
957 -- Add each Suggested Resource Info to the Resource List if its not already present.
958 j := p_suggested_res_id_tbl.FIRST;
959 WHILE j IS NOT NULL LOOP
960
961 -- Check whether the Resource list already has the Suggested Resource.
962 l_res_found := FALSE;
963 i := p_res_tbl.FIRST;
964 WHILE i IS NOT NULL LOOP
965 l_res_found := p_res_tbl(i).resource_id = p_suggested_res_id_tbl(j)
966 AND p_res_tbl(i).resource_type = p_suggested_res_type_tbl(j);
967
968 EXIT WHEN l_res_found;
969 i := p_res_tbl.NEXT(i);
970 END LOOP;
971
972 IF NOT l_res_found THEN
973 i := NVL(p_res_tbl.LAST, 0) + 1;
974
975 p_res_tbl(i).resource_id := p_suggested_res_id_tbl(j);
976 p_res_tbl(i).resource_type := p_suggested_res_type_tbl(j);
977 p_res_tbl(i).start_date := p_start_date;
978 p_res_tbl(i).end_date := p_end_date;
979 p_res_tbl(i).terr_id := -1;
980 p_res_tbl(i).terr_rank := -1;
981 END IF;
982
983 j := p_suggested_res_id_tbl.NEXT(j);
984 END LOOP;
985 END add_suggested_resources;
986
987
988 /**
989 * Gets the Qualified Resources for a Task by calling JTF Assignment Manager
990 * and also making use of the Required Skills of the Task if Required to reduce
991 * the Resource List.
992 *
993 * <br>
994 *
995 * The reason for CSF to maintain its own Assignment Manager rather than
996 * completely relying on JTF Assignment Manager has two fold reasons.
997 *
998 * <br>
999 *
1000 * TQ is secondary for JTF Assignment Manager API.
1001 * Suppose in Schedule Advise Window, all the Flags are checked... then
1002 * JTF Assignment Manager will give preference to Contracts and IB only.
1003 * Only when both of returns ZERO resources, then JTF will consider TQ.
1004 * But DC expects an intersection of the three results.
1005 * Moreover if both Contracts and IB are checked, then JTF will use
1006 * the profile "JTFAM: Resource Search Order (JTF_AM_PREF_RES_ORDER)" to
1007 * find out which one to return ultimately. If the value CONTRACTS, then
1008 * CONTRACTS - Only Contracts is returned. If None, IB is returned.
1009 * IB - Only IB is returned. If None, Contracts is returned.
1010 * BOTH - Intersection of Contracts and IB Resources are returned.
1011 *
1012 * <br>
1013 *
1014 * JTF doesnt know "ABC of Skills"
1015 * Resources and Skills is completely a Field Service Functionality. A
1016 * Resource can be attached to a Skill with a particular Skill Level.
1017 * So can a Task be tied to a Skill with a particular Skill Level. If
1018 * Skill based Flag is checked, then the Resource needs to have the same
1019 * Skill Set with a Comparable Skill Level as required by the Task.
1020 * Comparable Skill Level !!! - What is that ?
1021 * The profile "CSF: Skill Level Match (CSF_SKILL_LEVEL_MATCH)" is used
1022 * to decide whether the Resource has the Required Skill Level as required
1023 * by the Task.
1024 * EQUAL TO OR SMALLER THAN - Resource should have a Skill Level equal to
1025 * or lesser than that of the Task.
1026 * EQUAL TO - Resource should have a Skill Level equal to
1027 * that of the Task.
1028 * EQUAL TO OR GREATER THAN - Resource should have a Skill Level equal to
1029 * or greater than that of the Task.
1030 * Note that the Task needs to have Skills. Otherwise the Flag wont be used
1031 * at all for getting the Qualified Resources.
1032 *
1033 * <br>
1034 *
1035 * Thus CSF Assignment Manager API will call JTF Assignment Manager separately
1036 * for Contracts / IB and then for Territory. Do an intersection of the Resources
1037 * obtained thru the two calls and pruned by Skill Sets. Note that it gets
1038 * Contracts / IB Resources from JTF in one call and so the user should make use
1039 * the profile JTF_AM_PREF_RES_ORDER to get intersected results.
1040 *
1041 * <br>
1042 *
1043 * CSF Assignment Manager still doesnt pass the parameter P_FILTER_EXCLUDED_RESOURCE
1044 * so that JTF Assignment Manager doesnt return Excluded Resources.
1045 * CSF Assignment Manager still doesnt pass the parameter P_BUSINESS_PROCESS_ID
1046 * so that JTF Assignment Manager returns only those Resources who belong to
1047 * Field Service Business Process when Preferred Resources are entered in Contracts.
1048 *
1049 * @param p_api_version API Version (1.0)
1050 * @param p_init_msg_list Initialize Message List
1051 * @param x_return_status Return Status of the Procedure.
1052 * @param x_msg_count Number of Messages in the Stack.
1053 * @param x_msg_data Stack of Error Messages.
1054 * @param p_task_id Task Idenfifier
1055 * @param p_task_rec Qualified Task Record
1056 * @param p_scheduling_mode Scheduling Mode used. (A, I, W)
1057 * @param p_start Start Date of the Plan Window
1058 * @param p_end End Date of the Plan Window
1059 * @param p_duration Duration of the Task (Used by JTF to find out Available Resources)
1060 * @param p_duration_uom UOM of the above Duration
1061 * @param p_contracts_flag Get Contracts Preferred Resources ('Y'/'N')
1062 * @param p_ib_flag Get IB Preferred Resources ('Y'/'N')
1063 * @param p_territory_flag Get Winning Territory Resources ('Y'/'N')
1064 * @param p_skill_flag Get Skilled Resources ('Y'/'N')
1065 * @param p_calendar_flag Get only Available Resources. Passed to JTF ('Y'/'N')
1066 * @param p_sort_flag Sort the Resources based on their distance from Task ('Y'/'N')
1067 * @param p_suggested_res_id_tbl Suggested Resource ID Table
1068 * @param p_suggested_res_type_tbl Suggested Resource Type Table
1069 * @param x_res_tbl Qualified Resource suitable for Scheduling
1070 */
1071 PROCEDURE get_resources_to_schedule(
1072 p_api_version IN NUMBER
1073 , p_init_msg_list IN VARCHAR2
1074 , x_return_status OUT NOCOPY VARCHAR2
1075 , x_msg_count OUT NOCOPY NUMBER
1076 , x_msg_data OUT NOCOPY VARCHAR2
1077 , p_task_id IN NUMBER
1078 , p_incident_id IN NUMBER
1079 , p_res_qualifier_tbl IN resource_qualifier_tbl_type
1080 , p_scheduling_mode IN VARCHAR2
1081 , p_start IN DATE
1082 , p_end IN DATE
1083 , p_duration IN NUMBER
1084 , p_duration_uom IN VARCHAR2
1085 , p_contracts_flag IN VARCHAR2
1086 , p_ib_flag IN VARCHAR2
1087 , p_territory_flag IN VARCHAR2
1088 , p_skill_flag IN VARCHAR2
1089 , p_calendar_flag IN VARCHAR2
1090 , p_sort_flag IN VARCHAR2
1091 , p_suggested_res_id_tbl IN jtf_number_table
1092 , p_suggested_res_type_tbl IN jtf_varchar2_table_100
1093 , x_res_tbl OUT NOCOPY jtf_assign_pub.assignresources_tbl_type
1094 ) IS
1095 l_api_name CONSTANT VARCHAR2(30) := 'GET_RESOURCES_TO_SCHEDULE';
1096 l_api_version CONSTANT NUMBER := 1.0;
1097
1098 l_task_has_skill_id NUMBER;
1099 l_business_process_id NUMBER;
1100 l_contracts_flag VARCHAR2(1);
1101 l_ib_flag VARCHAR2(1);
1102 l_territory_flag VARCHAR2(1);
1103 l_skills_flag VARCHAR2(1);
1104 l_has_suggested_res BOOLEAN;
1105 l_continue_search BOOLEAN;
1106 l_sr_task_rec jtf_assign_pub.jtf_srv_task_rec_type;
1107
1108 l_contracts_ib_res_tbl jtf_assign_pub.assignresources_tbl_type;
1109 l_territory_res_tbl jtf_assign_pub.assignresources_tbl_type;
1110 l_skilled_res_tbl jtf_assign_pub.assignresources_tbl_type;
1111 l_stic NUMBER;
1112
1113 e_no_res EXCEPTION;
1114
1115 -- Cursor to determine if the Task has any required skills
1116 -- and returns the has_skill_id
1117 CURSOR c_task_skills IS
1118 SELECT has_skill_id
1119 FROM csf_required_skills_b,jtf_tasks_b t
1120 WHERE t.task_id = p_task_id
1121 AND has_skill_type = 'TASK'
1122 AND has_skill_id = nvl(t.parent_task_id, t.task_id)
1123 AND NVL(disabled_flag, 'N') <> 'Y'
1124 AND NVL(t.deleted_flag, 'N') <> 'Y';
1125
1126 --Introduced by lokumar for bug#7340932
1127 CURSOR c_business_process IS
1128 select iv.business_process_id
1129 from jtf_tasks_b t, cs_incidents_all i, cs_incident_types_vl iv
1130 where t.task_id = p_task_id
1131 and t.source_object_id = i.incident_id
1132 and i.incident_type_id = iv.incident_type_id;
1133
1134
1135 BEGIN
1136 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
1137 RAISE fnd_api.g_exc_unexpected_error;
1138 END IF;
1139
1140 IF fnd_api.to_boolean(p_init_msg_list) THEN
1141 fnd_msg_pub.initialize;
1142 END IF;
1143
1144 x_return_status := fnd_api.g_ret_sts_success;
1145
1146 IF csf_util_pvt.g_timing_activated THEN
1147 csf_util_pvt.add_timer(105, 'Get resources to schedule (init)', 0, NULL);
1148 END IF;
1149
1150 l_contracts_flag := NVL(p_contracts_flag, 'N');
1151 l_ib_flag := NVL(p_ib_flag, 'N');
1152 l_territory_flag := NVL(p_territory_flag, 'N');
1153 l_skills_flag := NVL(p_skill_flag, 'N');
1154 l_stic := 0;
1155 l_has_suggested_res := p_suggested_res_id_tbl IS NOT NULL AND p_suggested_res_id_tbl.COUNT > 0;
1156
1157 -- Check that at least one flag has been set.
1158 IF l_contracts_flag = 'N'
1159 AND l_ib_flag = 'N'
1160 AND l_territory_flag = 'N'
1161 AND l_skills_flag = 'N'
1162 AND NOT l_has_suggested_res
1163 THEN
1164 RAISE e_no_res;
1165 END IF;
1166
1167 -- start with an empty list
1168 x_res_tbl.DELETE;
1169
1170 -- Find out whether the Task has any Required Skills Attached.
1171 IF l_skills_flag = 'Y' THEN
1172 OPEN c_task_skills;
1173 FETCH c_task_skills INTO l_task_has_skill_id;
1174 CLOSE c_task_skills;
1175
1176 IF l_task_has_skill_id IS NULL THEN
1177 -- Task has no Skills attached. Turn off Skills Flag.
1178 l_skills_flag := 'N';
1179 -- If none of the other Flags are set we have an error situation
1180 IF l_contracts_flag = 'N' AND l_ib_flag = 'N' AND l_territory_flag = 'N' AND NOT l_has_suggested_res THEN
1181 fnd_message.set_name('CSF', 'CSF_NO_TASK_SKILL_RES');
1182 fnd_msg_pub.ADD;
1183 RAISE fnd_api.g_exc_error;
1184 END IF;
1185 END IF;
1186 END IF;
1187
1188 -- Convert the given Task ID or the Qualifier Table to SR Task Record
1189 IF l_contracts_flag = 'Y' OR l_ib_flag = 'Y' OR l_territory_flag = 'Y' THEN
1190 IF p_res_qualifier_tbl.COUNT > 0 THEN
1191 IF csf_util_pvt.g_timing_activated THEN
1192 csf_util_pvt.add_timer(106, 'convert territory qualifiers to record', 0, NULL);
1193 END IF;
1194 l_sr_task_rec := get_qualified_task_rec(p_res_qualifier_tbl);
1195 IF csf_util_pvt.g_timing_activated THEN
1196 csf_util_pvt.add_timer(106, 'convert territory qualifiers to record', 1, NULL);
1197 END IF;
1198 ELSE
1199 IF csf_util_pvt.g_timing_activated THEN
1200 csf_util_pvt.add_timer(107, 'get territory qualifiers for task', 0, NULL);
1201 END IF;
1202 l_sr_task_rec := get_qualified_task_rec(get_res_qualifier_table(p_task_id));
1203 IF csf_util_pvt.g_timing_activated THEN
1204 csf_util_pvt.add_timer(107, 'get territory qualifiers for task', 1, NULL);
1205 END IF;
1206 END IF;
1207 l_sr_task_rec.task_id := p_task_id;
1208 l_sr_task_rec.service_request_id := p_incident_id;
1209 END IF;
1210
1211 IF csf_util_pvt.g_timing_activated THEN
1212 csf_util_pvt.add_timer(105, 'get resources to schedule (init)', 1, NULL);
1213 END IF;
1214
1215 l_continue_search := TRUE;
1216
1217 -- Retrieve the Resources defined in Contracts / IB
1218 IF l_contracts_flag = 'Y' OR l_ib_flag = 'Y' THEN
1219 IF csf_util_pvt.g_timing_activated THEN
1220 csf_util_pvt.add_timer(108, 'Get contract/IB resources', 0, NULL);
1221 END IF;
1222
1223 IF l_contracts_flag = 'Y' THEN
1224 -- Added by lokumar for bug#7340932
1225 --business process id is used only when contracts are selected
1226 OPEN c_business_process;
1227 FETCH c_business_process INTO l_business_process_id;
1228 CLOSE c_business_process;
1229
1230 l_stic := l_stic + 1;
1231
1232 END IF;
1233
1234 IF l_ib_flag = 'Y' THEN
1235 l_stic := l_stic + 2;
1236 END IF;
1237
1238 jtf_assign_pub.get_assign_resources(
1239 p_api_version => 1.0
1240 , p_init_msg_list => fnd_api.g_false
1241 , p_commit => fnd_api.g_false
1242 , x_return_status => x_return_status
1243 , x_msg_count => x_msg_count
1244 , x_msg_data => x_msg_data
1245 , p_sr_task_rec => l_sr_task_rec
1246 , p_contracts_preferred_engineer => l_contracts_flag
1247 , p_ib_preferred_engineer => l_ib_flag
1248 , p_territory_flag => 'N'
1249 , p_effort_duration => p_duration
1250 , p_effort_uom => p_duration_uom
1251 , p_start_date => p_start
1252 , p_end_date => p_end
1253 , p_auto_select_flag => 'N'
1254 , p_calendar_flag => NVL(p_calendar_flag, 'N')
1255 , p_calendar_check => 'N'
1256 , p_calling_doc_id => l_sr_task_rec.service_request_id
1257 , p_calling_doc_type => g_assign_doc_type
1258 , p_business_process_id => l_business_process_id
1259 , x_assign_resources_tbl => l_contracts_ib_res_tbl
1260 );
1261
1262 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
1263 RAISE fnd_api.g_exc_unexpected_error;
1264 END IF;
1265
1266 IF l_contracts_ib_res_tbl.COUNT = 0 THEN
1267 l_continue_search := FALSE;
1268 END IF;
1269
1270 -- Since JTF Assignment Manager doesnt return End Windows properly,
1271 -- setting them to the given Plan Window.
1272 set_generic_planwindow(l_contracts_ib_res_tbl, p_start, p_end);
1273
1274 IF csf_util_pvt.g_timing_activated THEN
1275 csf_util_pvt.add_timer(108, 'get contract/IB resources', 1, NULL);
1276 END IF;
1277 END IF;
1278
1279 -- Retrieve Resources from Territory
1280 IF l_territory_flag = 'Y' AND l_continue_search THEN
1281 IF csf_util_pvt.g_timing_activated THEN
1282 csf_util_pvt.add_timer(109, 'get territory resources', 0, NULL);
1283 END IF;
1284
1285 l_stic := l_stic + 4;
1286
1287 jtf_assign_pub.get_assign_resources(
1288 p_api_version => 1.0
1289 , p_init_msg_list => fnd_api.g_false
1290 , p_commit => fnd_api.g_false
1291 , x_return_status => x_return_status
1292 , x_msg_count => x_msg_count
1293 , x_msg_data => x_msg_data
1294 , p_sr_task_rec => l_sr_task_rec
1295 , p_contracts_preferred_engineer => 'N'
1296 , p_ib_preferred_engineer => 'N'
1297 , p_territory_flag => 'Y'
1298 , p_effort_duration => p_duration
1299 , p_effort_uom => p_duration_uom
1300 , p_start_date => p_start
1301 , p_end_date => p_end
1302 , p_auto_select_flag => 'N'
1303 , p_calendar_flag => NVL(p_calendar_flag, 'N')
1304 , p_calendar_check => 'N'
1305 , p_calling_doc_id => l_sr_task_rec.service_request_id
1306 , p_calling_doc_type => g_assign_doc_type
1307 , x_assign_resources_tbl => l_territory_res_tbl
1308 );
1309 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
1310 RAISE fnd_api.g_exc_unexpected_error;
1311 END IF;
1312
1313 IF l_territory_res_tbl.COUNT = 0 THEN
1314 l_continue_search := FALSE;
1315 END IF;
1316
1317 -- Since JTF Assignment Manager doesnt return End Windows properly,
1318 -- setting them to the given Plan Window.
1319 set_generic_planwindow(l_territory_res_tbl, p_start, p_end);
1320
1321 IF csf_util_pvt.g_timing_activated THEN
1322 csf_util_pvt.add_timer(109, 'get territory resources', 1, NULL);
1323 END IF;
1324 END IF;
1325
1326 -- Retrieve the Skilled Resources for the Task
1327 IF l_skills_flag = 'Y' AND l_continue_search THEN
1328 l_stic := l_stic + 8;
1329 get_skilled_resources(
1330 p_task_id => l_task_has_skill_id
1331 , p_start => p_start
1332 , p_end => p_end
1333 , x_skilled_res_tbl => l_skilled_res_tbl
1334 );
1335 IF l_skilled_res_tbl.COUNT = 0 THEN
1336 l_continue_search := FALSE;
1337 END IF;
1338 END IF;
1339
1340 -- Intersect the results obtained from the three Qualifiers.
1341 IF l_continue_search THEN
1342 IF l_stic > 0 AND l_stic <= 3 THEN --> Only Contracts / IB was chosen
1343 x_res_tbl := l_contracts_ib_res_tbl;
1344 ELSIF l_stic = 4 THEN --> Only Territory was chosen
1345 x_res_tbl := l_territory_res_tbl;
1346 ELSIF l_stic = 8 THEN --> Only Skills were chosen
1347 x_res_tbl := l_skilled_res_tbl;
1348 ELSIF l_stic <= 7 THEN --> Both Contracts/IB and Terr were chosen
1349 x_res_tbl := intersect_results(l_contracts_ib_res_tbl, l_territory_res_tbl, p_start, p_end);
1350 ELSIF l_stic <= 11 THEN --> Both Contracts/IB and Skills where chosen
1351 x_res_tbl := intersect_results(l_contracts_ib_res_tbl, l_skilled_res_tbl, p_start, p_end);
1352 ELSIF l_stic <= 12 THEN --> Both Terr and SKills were chosen
1353 x_res_tbl := intersect_results(l_territory_res_tbl, l_skilled_res_tbl, p_start, p_end);
1354 ELSIF l_stic <= 15 THEN --> All the Flags were chosen
1355 x_res_tbl := intersect_results(l_contracts_ib_res_tbl, l_territory_res_tbl, p_start, p_end);
1356 x_res_tbl := intersect_results(x_res_tbl, l_skilled_res_tbl, p_start, p_end);
1357 END IF;
1358 END IF;
1359
1360 -- Add the suggested resources at the end (if the resource is not already there)
1361 IF l_has_suggested_res THEN
1362 add_suggested_resources(
1363 p_res_tbl => x_res_tbl
1364 , p_suggested_res_id_tbl => p_suggested_res_id_tbl
1365 , p_suggested_res_type_tbl => p_suggested_res_type_tbl
1366 , p_start_date => p_start
1367 , p_end_date => p_end
1368 );
1369 END IF;
1370
1371 -- make sure there were results
1372 IF x_res_tbl.COUNT = 0 THEN
1373 RAISE e_no_res;
1374 END IF;
1375
1376 IF p_scheduling_mode <> 'A' THEN
1377 -- Sort the Resources by their distance to the Task.
1378 IF csf_util_pvt.g_timing_activated THEN
1379 csf_util_pvt.add_timer(113, 'sort resources list', 0, NULL);
1380 END IF;
1381
1382 IF x_res_tbl.COUNT > 1 AND NVL(p_sort_flag, 'Y') = 'Y' THEN
1383 x_res_tbl := sort_resource_by_distance(x_res_tbl, p_task_id, p_start, p_end);
1384 END IF;
1385
1386 IF csf_util_pvt.g_timing_activated THEN
1387 csf_util_pvt.add_timer(113, 'sort resources list', 1, NULL);
1388 END IF;
1389
1390 END IF;
1391
1392
1393 -- The number of Resources are restricted from the Sorted List.
1394 IF csf_util_pvt.g_timing_activated THEN
1395 csf_util_pvt.add_timer(114, 'reduce sorted resources list', 0, NULL);
1396 END IF;
1397
1398 x_res_tbl := reduce_resource_list(x_res_tbl, p_start, p_end);
1399
1400 IF csf_util_pvt.g_timing_activated THEN
1401 csf_util_pvt.add_timer(114, 'reduce sorted resources list', 1, NULL);
1402 END IF;
1403 EXCEPTION
1404 WHEN e_no_res THEN
1405 x_return_status := fnd_api.g_ret_sts_error;
1406 fnd_message.set_name('CSF', get_assign_error_msg(l_stic));
1407 fnd_msg_pub.ADD;
1408 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1409 WHEN fnd_api.g_exc_error THEN
1410 x_return_status := fnd_api.g_ret_sts_error;
1411 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1412 WHEN fnd_api.g_exc_unexpected_error THEN
1413 x_return_status := fnd_api.g_ret_sts_unexp_error;
1414 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1415 WHEN OTHERS THEN
1416 x_return_status := fnd_api.g_ret_sts_unexp_error;
1417 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1418 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1419 END IF;
1420 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1421 END get_resources_to_schedule;
1422
1423 /**
1424 * Gets the Qualified Resources for a Task in a format understood by Request Model.
1425 * <br>
1426 * In turn calls the GET_RESOURCES_TO_SCHEDULE which gets the Resources in JTF
1427 * Assignment Manager Format.
1428 */
1429 PROCEDURE get_resources_to_schedule(
1430 p_api_version IN NUMBER
1431 , p_init_msg_list IN VARCHAR2 DEFAULT NULL
1432 , x_return_status OUT NOCOPY VARCHAR2
1433 , x_msg_count OUT NOCOPY NUMBER
1434 , x_msg_data OUT NOCOPY VARCHAR2
1435 , p_task_id IN NUMBER
1436 , p_incident_id IN NUMBER
1437 , p_res_qualifier_tbl IN resource_qualifier_tbl_type
1438 , p_scheduling_mode IN VARCHAR2
1439 , p_start IN DATE
1440 , p_end IN DATE
1441 , p_duration IN NUMBER DEFAULT NULL
1442 , p_duration_uom IN VARCHAR2 DEFAULT NULL
1443 , p_contracts_flag IN VARCHAR2 DEFAULT NULL
1444 , p_ib_flag IN VARCHAR2 DEFAULT NULL
1445 , p_territory_flag IN VARCHAR2 DEFAULT NULL
1446 , p_skill_flag IN VARCHAR2 DEFAULT NULL
1447 , p_calendar_flag IN VARCHAR2 DEFAULT NULL
1448 , p_sort_flag IN VARCHAR2 DEFAULT NULL
1449 , p_suggested_res_id_tbl IN jtf_number_table DEFAULT NULL
1450 , p_suggested_res_type_tbl IN jtf_varchar2_table_100 DEFAULT NULL
1451 , x_res_tbl IN OUT NOCOPY csf_requests_pvt.resource_tbl_type
1452 ) IS
1453 l_api_name CONSTANT VARCHAR2(30) := 'GET_RESOURCES_TO_SCHEDULE(2)';
1454 l_api_version CONSTANT NUMBER := 1.0;
1455
1456 l_assign_resource_tbl jtf_assign_pub.assignresources_tbl_type;
1457 j PLS_INTEGER;
1458 k PLS_INTEGER;
1459 BEGIN
1460 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
1461 RAISE fnd_api.g_exc_unexpected_error;
1462 END IF;
1463
1464 IF fnd_api.to_boolean(p_init_msg_list) THEN
1465 fnd_msg_pub.initialize;
1466 END IF;
1467
1468 x_return_status := fnd_api.g_ret_sts_success;
1469
1470 get_resources_to_schedule(
1471 p_api_version => 1
1472 , p_init_msg_list => fnd_api.g_false
1473 , x_return_status => x_return_status
1474 , x_msg_count => x_msg_count
1475 , x_msg_data => x_msg_data
1476 , p_task_id => p_task_id
1477 , p_incident_id => p_incident_id
1478 , p_res_qualifier_tbl => p_res_qualifier_tbl
1479 , p_scheduling_mode => p_scheduling_mode
1480 , p_start => p_start
1481 , p_end => p_end
1482 , p_duration => p_duration
1483 , p_duration_uom => p_duration_uom
1484 , p_contracts_flag => p_contracts_flag
1485 , p_ib_flag => p_ib_flag
1486 , p_territory_flag => p_territory_flag
1487 , p_skill_flag => p_skill_flag
1488 , p_calendar_flag => p_calendar_flag
1489 , p_sort_flag => p_sort_flag
1490 , p_suggested_res_id_tbl => p_suggested_res_id_tbl
1491 , p_suggested_res_type_tbl => p_suggested_res_type_tbl
1492 , x_res_tbl => l_assign_resource_tbl
1493 );
1494
1495 IF x_res_tbl IS NULL THEN
1496 x_res_tbl := csf_requests_pvt.resource_tbl_type();
1497 END IF;
1498
1499 --start with an empty table so that previous value are deleted
1500 x_res_tbl.delete;
1501
1502 -- if qualified resoucres are found, add them to the output list
1503 IF x_return_status = fnd_api.g_ret_sts_success AND l_assign_resource_tbl.COUNT > 0 THEN
1504
1505 j := l_assign_resource_tbl.FIRST;
1506 WHILE j IS NOT NULL LOOP
1507 x_res_tbl.EXTEND;
1508 k := x_res_tbl.LAST;
1509 x_res_tbl(k).resource_id := l_assign_resource_tbl(j).resource_id;
1510 x_res_tbl(k).resource_type := l_assign_resource_tbl(j).resource_type;
1511 x_res_tbl(k).planwin_start := l_assign_resource_tbl(j).start_date;
1512 x_res_tbl(k).planwin_end := l_assign_resource_tbl(j).end_date;
1513 x_res_tbl(k).territory_id := l_assign_resource_tbl(j).terr_id;
1514 IF l_assign_resource_tbl(j).terr_rank = jtf_assign_pub.am_miss_num THEN
1515 x_res_tbl(k).territory_rank := NULL;
1516 ELSE
1517 x_res_tbl(k).territory_rank := l_assign_resource_tbl(j).terr_rank;
1518 END IF;
1519 IF l_assign_resource_tbl(j).preference_type = 'I' OR l_assign_resource_tbl(j).preference_type = 'C' THEN
1520 x_res_tbl(k).preferred_resources_flag := 'Y';
1521 ELSE
1522 x_res_tbl(k).preferred_resources_flag := 'N';
1523 END IF;
1524
1525 j := l_assign_resource_tbl.NEXT(j);
1526 END LOOP;
1527 END IF;
1528 EXCEPTION
1529 WHEN fnd_api.g_exc_error THEN
1530 x_return_status := fnd_api.g_ret_sts_error;
1531 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1532 WHEN fnd_api.g_exc_unexpected_error THEN
1533 x_return_status := fnd_api.g_ret_sts_unexp_error;
1534 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1535 WHEN OTHERS THEN
1536 x_return_status := fnd_api.g_ret_sts_unexp_error;
1537 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1538 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1539 END IF;
1540 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1541 END get_resources_to_schedule;
1542
1543
1544 /**
1545 * Returns the Resource Type Code corresponding to a Resource Category.
1546 * <br>
1547 * In sync with the code done in JTF_RS_ALL_RESOURCES_VL
1548 *
1549 * @param p_category Resource Category
1550 * @returns Resource Type Code (VARCHAR2).
1551 */
1552 FUNCTION rs_category_type(p_category VARCHAR2)
1553 RETURN VARCHAR2 IS
1554 BEGIN
1555 IF p_category = 'EMPLOYEE' THEN
1556 RETURN 'RS_EMPLOYEE';
1557 ELSIF p_category = 'PARTNER' THEN
1558 RETURN 'RS_PARTNER';
1559 ELSIF p_category = 'SUPPLIER_CONTACT' THEN
1560 RETURN 'RS_SUPPLIER';
1561 ELSIF p_category = 'PARTY' THEN
1562 RETURN 'RS_PARTY';
1563 ELSIF p_category = 'OTHER' THEN
1564 RETURN 'RS_OTHER';
1565 ELSE
1566 RETURN NULL;
1567 END IF;
1568 END rs_category_type;
1569
1570 /**
1571 * Returns the ID of the Resource tied to the given User (FND User).
1572 * <br>
1573 * If no User is passed in, then it will take the User who has logged in
1574 * (FND_GLOBAL.USER_ID).
1575 *
1576 * @param p_user_id Identifier to the User desired (Optional)
1577 * @returns Resource ID (NUMBER)
1578 */
1579 FUNCTION resource_id(p_user_id NUMBER DEFAULT NULL)
1580 RETURN NUMBER IS
1581 CURSOR c_resource IS
1582 SELECT resource_id
1583 FROM jtf_rs_resource_extns
1584 WHERE user_id = NVL(p_user_id, fnd_global.user_id);
1585 l_id NUMBER := NULL;
1586 BEGIN
1587 OPEN c_resource;
1588 FETCH c_resource INTO l_id;
1589 CLOSE c_resource;
1590
1591 RETURN l_id;
1592 END resource_id;
1593
1594 /**
1595 * Returns the Resource Type of the Resource tied to the given user. (FND User)
1596 * <br>
1597 * If no User is passed in, then it will take the User who has logged in
1598 * (FND_GLOBAL.USER_ID).
1599 *
1600 * @param p_user_id Identifier to the User desired (Optional)
1601 * @returns Resource Type (VARCHAR2)
1602 */
1603 FUNCTION resource_type(p_user_id NUMBER DEFAULT NULL)
1604 RETURN VARCHAR2 IS
1605 CURSOR c_resource_type IS
1606 SELECT category
1607 FROM jtf_rs_resource_extns
1608 WHERE user_id = NVL(p_user_id, fnd_global.user_id);
1609 l_type jtf_rs_resource_extns.category%TYPE;
1610 BEGIN
1611 OPEN c_resource_type;
1612 FETCH c_resource_type INTO l_type;
1613 CLOSE c_resource_type;
1614
1615 RETURN rs_category_type(l_type);
1616 END resource_type;
1617
1618 FUNCTION get_resource_from_cache(
1619 p_res_id NUMBER
1620 , p_res_type VARCHAR2
1621 , p_get_address BOOLEAN DEFAULT FALSE
1622 , p_date DATE DEFAULT NULL
1623 )
1624 RETURN resource_cache_rec_type IS
1625
1626 l_return_status VARCHAR2(1);
1627 l_msg_data VARCHAR2(2000);
1628 l_msg_count NUMBER;
1629 l_found BOOLEAN;
1630 l_res_cache_info resource_cache_rec_type;
1631
1632 CURSOR c_normal_resource IS
1633 SELECT resource_id
1634 , p_res_type resource_type
1635 , resource_name
1636 , resource_number
1637 FROM jtf_rs_resource_extns_vl
1638 WHERE resource_id = p_res_id;
1639
1640 CURSOR c_group_resource IS
1641 SELECT group_id resource_id
1642 , 'RS_GROUP' resource_type
1643 , group_name resource_name
1644 , group_number resource_number
1645 FROM jtf_rs_groups_vl
1646 WHERE group_id = p_res_id;
1647
1648 CURSOR c_team_resource IS
1649 SELECT team_id resource_id
1650 , 'RS_TEAM' resource_type
1651 , team_name resource_name
1652 , team_number resource_number
1653 FROM jtf_rs_teams_vl
1654 WHERE team_id = p_res_id;
1655
1656 l_resource c_normal_resource%ROWTYPE;
1657 BEGIN
1658 IF p_res_id IS NULL OR p_res_type IS NULL THEN
1659 RETURN NULL;
1660 END IF;
1661
1662 -- Check whether the Resource exists in the Cache
1663 l_found := FALSE;
1664 IF g_res_info_cache.EXISTS(p_res_id) THEN
1665 l_res_cache_info := g_res_info_cache(p_res_id);
1666 l_found := l_res_cache_info.resource_type = p_res_type;
1667 END IF;
1668
1669 IF NOT l_found THEN
1670 IF p_res_type = 'RS_GROUP' THEN
1671 OPEN c_group_resource;
1672 FETCH c_group_resource INTO l_resource;
1673 CLOSE c_group_resource;
1674 ELSIF p_res_type = 'RS_TEAM' THEN
1675 OPEN c_team_resource;
1676 FETCH c_team_resource INTO l_resource;
1677 CLOSE c_team_resource;
1678 ELSE
1679 OPEN c_normal_resource;
1680 FETCH c_normal_resource INTO l_resource;
1681 CLOSE c_normal_resource;
1682 END IF;
1683
1684 -- Populate the Resource Cache Record
1685 l_res_cache_info.resource_id := l_resource.resource_id;
1686 l_res_cache_info.resource_type := l_resource.resource_type;
1687 l_res_cache_info.resource_name := l_resource.resource_name;
1688 l_res_cache_info.resource_number := l_resource.resource_number;
1689 END IF;
1690
1691 IF p_get_address THEN
1692 -- Check the validity of the Address in the Cache for the date
1693 IF l_res_cache_info.address.party_site_id IS NULL
1694 OR TRUNC(p_date) < l_res_cache_info.address.start_date_active
1695 OR TRUNC(p_date) > NVL(l_res_cache_info.address.end_date_active, p_date + 1)
1696 THEN
1697 csf_resource_address_pvt.get_resource_address(
1698 p_api_version => 1.0
1699 , x_return_status => l_return_status
1700 , x_msg_count => l_msg_count
1701 , x_msg_data => l_msg_data
1702 , p_resource_id => p_res_id
1703 , p_resource_type => p_res_type
1704 , p_date => p_date
1705 , x_address_rec => l_res_cache_info.address
1706 );
1707
1708 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1709 RETURN NULL;
1710 END IF;
1711
1712 l_found := FALSE;
1713 END IF;
1714 END IF;
1715
1716 -- Cache the Resource Information for future use.
1717 IF NOT l_found THEN
1718 g_res_info_cache(p_res_id) := l_res_cache_info;
1719 END IF;
1720
1721 RETURN l_res_cache_info;
1722 END get_resource_from_cache;
1723
1724 /**
1725 * Returns the Resource Name given the Resource ID and Type.
1726 *
1727 * @param p_res_id Resource ID
1728 * @param p_res_type Resource Type Code
1729 * @returns Resource Name (VARCHAR2)
1730 */
1731 FUNCTION get_resource_name(p_res_id NUMBER, p_res_type VARCHAR2)
1732 RETURN VARCHAR2 IS
1733 BEGIN
1734 RETURN get_resource_from_cache(p_res_id, p_res_type).resource_name;
1735 END get_resource_name;
1736
1737 /**
1738 * Returns the Complete Resource Information given the Resource ID and Type.
1739 * The returned record includes Resource Number and Resource Name.
1740 *
1741 * @param p_res_id Resource ID
1742 * @param p_res_type Resource Type Code
1743 * @returns Resource Information filled in RESOURCE_REC_TYPE
1744 */
1745 FUNCTION get_resource_info(p_res_id NUMBER, p_res_type VARCHAR2)
1746 RETURN resource_rec_type IS
1747 l_res_cache_info resource_cache_rec_type;
1748 l_res_info resource_rec_type;
1749 BEGIN
1750 l_res_cache_info := get_resource_from_cache(p_res_id, p_res_type);
1751
1752 l_res_info.resource_id := l_res_cache_info.resource_id;
1753 l_res_info.resource_type := l_res_cache_info.resource_type;
1754 l_res_info.resource_name := l_res_cache_info.resource_name;
1755 l_res_info.resource_number := l_res_cache_info.resource_number;
1756
1757 RETURN l_res_info;
1758 END get_resource_info;
1759
1760 /**
1761 * Returns the Address of the Party created for the Resource as of the
1762 * date passed.
1763 *
1764 * @param p_res_id Resource ID
1765 * @param p_res_type Resource Type Code
1766 * @param p_date Active Party Site for the given date
1767 *
1768 * @returns Party Address of the Resource
1769 */
1770 FUNCTION get_resource_party_address (
1771 p_res_id NUMBER
1772 , p_res_type VARCHAR2
1773 , p_date DATE
1774 )
1775 RETURN csf_resource_address_pvt.address_rec_type IS
1776 BEGIN
1777 RETURN get_resource_from_cache(p_res_id, p_res_type, TRUE, p_date).address;
1778 END get_resource_party_address;
1779
1780 /**
1781 * Returns the Resource Type Name corresponding to the Resource Type Code
1782 *
1783 * @param p_res_type Resource Type Code
1784 * @returns Resource Type Name (VARCHAR2)
1785 */
1786 FUNCTION get_resource_type_name(p_res_type VARCHAR2)
1787 RETURN VARCHAR2 IS
1788 i PLS_INTEGER;
1789
1790 CURSOR c_resource_type_names IS
1791 SELECT o.object_code code, o.name
1792 FROM jtf_object_usages u
1793 , jtf_objects_tl o
1794 WHERE u.object_user_code = 'RESOURCE_TYPES'
1795 AND o.object_code = u.object_code
1796 AND o.language = userenv('LANG');
1797 BEGIN
1798 IF g_res_type_name_tbl IS NULL THEN
1799 g_res_type_name_tbl := res_type_name_tbl_type();
1800 FOR v_resource_type_name IN c_resource_type_names LOOP
1801 g_res_type_name_tbl.extend();
1802 i := g_res_type_name_tbl.LAST;
1803 g_res_type_name_tbl(i).resource_type_code := v_resource_type_name.code;
1804 g_res_type_name_tbl(i).resource_type_name := v_resource_type_name.name;
1805 END LOOP;
1806 END IF;
1807
1808 FOR i IN 1..g_res_type_name_tbl.COUNT LOOP
1809 IF g_res_type_name_tbl(i).resource_type_code = p_res_type THEN
1810 RETURN g_res_type_name_tbl(i).resource_type_name;
1811 END IF;
1812 END LOOP;
1813 RETURN p_res_type;
1814 END get_resource_type_name;
1815
1816 /**
1817 * Converts the given Time from Resource Timezone to Server Timezone
1818 * or vice versa.
1819 * <br>
1820 * By default, the given date is assumed to be in Resource Timezone and the
1821 * date returned is Server Timezone. Set p_server_to_resource parameter as
1822 * 'T' (FND_API.G_TRUE) to make it return the other way round.
1823 * <br>
1824 * Note that the API doesnt support RS_TEAM or RS_GROUP resources.
1825 *
1826 * @param p_api_version API Version (1.0)
1827 * @param p_init_msg_list Initialize Message List
1828 * @param x_return_status Return Status of the Procedure.
1829 * @param x_msg_count Number of Messages in the Stack.
1830 * @param x_msg_data Stack of Error Messages.
1831 * @param p_resource_id Resource ID
1832 * @param p_resource_type Resource Type
1833 * @param p_datetime Date to be converted
1834 * @param p_server_to_resource Server to Resource Timezone
1835 */
1836 PROCEDURE convert_timezone(
1837 p_api_version IN NUMBER
1838 , p_init_msg_list IN VARCHAR2
1839 , x_return_status OUT NOCOPY VARCHAR2
1840 , x_msg_count OUT NOCOPY NUMBER
1841 , x_msg_data OUT NOCOPY VARCHAR2
1842 , p_resource_id IN NUMBER
1843 , p_resource_type IN VARCHAR2
1844 , x_datetime IN OUT NOCOPY DATE
1845 , p_server_to_resource IN VARCHAR2
1846 ) IS
1847 l_api_name CONSTANT VARCHAR2(30) := 'CONVERT_TIMEZONE';
1848 l_api_version CONSTANT NUMBER := 1.0;
1849 l_source_tz_id NUMBER;
1850 l_dest_tz_id NUMBER;
1851 l_temp_tz_id NUMBER;
1852 CURSOR c_resource IS
1853 SELECT time_zone
1854 FROM jtf_rs_resource_extns
1855 WHERE resource_id = p_resource_id
1856 AND p_resource_type NOT IN('RS_GROUP', 'RS_TEAM');
1857 BEGIN
1858 -- Standard call to check for call compatibility
1859 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, 'CSF_TASKS_PUB') THEN
1860 RAISE fnd_api.g_exc_unexpected_error;
1861 END IF;
1862
1863 -- Initialize message list if p_init_msg_list is set to TRUE
1864 IF fnd_api.to_boolean(p_init_msg_list) THEN
1865 fnd_msg_pub.initialize;
1866 END IF;
1867
1868 -- Initialize API return status to success
1869 x_return_status := fnd_api.g_ret_sts_success;
1870
1871 -- Start actual processing
1872 fnd_profile.get('SERVER_TIMEZONE_ID', l_dest_tz_id);
1873
1874 OPEN c_resource;
1875 FETCH c_resource INTO l_source_tz_id;
1876 CLOSE c_resource;
1877
1878 -- Reverse conversion when requested
1879 IF fnd_api.to_boolean(p_server_to_resource) THEN
1880 l_temp_tz_id := l_source_tz_id;
1881 l_source_tz_id := l_dest_tz_id;
1882 l_dest_tz_id := l_source_tz_id;
1883 END IF;
1884
1885 -- Only try conversion when source and destination timezones are found
1886 IF l_source_tz_id IS NOT NULL AND l_dest_tz_id IS NOT NULL THEN
1887 hz_timezone_pub.get_time(
1888 p_api_version => 1.0
1889 , p_init_msg_list => fnd_api.g_false
1890 , p_source_tz_id => l_source_tz_id
1891 , p_dest_tz_id => l_dest_tz_id
1892 , p_source_day_time => x_datetime
1893 , x_dest_day_time => x_datetime
1894 , x_return_status => x_return_status
1895 , x_msg_count => x_msg_count
1896 , x_msg_data => x_msg_data
1897 );
1898 END IF;
1899
1900 EXCEPTION
1901 WHEN fnd_api.g_exc_error THEN
1902 x_return_status := fnd_api.g_ret_sts_error;
1903 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1904 WHEN fnd_api.g_exc_unexpected_error THEN
1905 x_return_status := fnd_api.g_ret_sts_unexp_error;
1906 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1907 WHEN OTHERS THEN
1908 x_return_status := fnd_api.g_ret_sts_unexp_error;
1909 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1910 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1911 END IF;
1912 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1913 END convert_timezone;
1914
1915 /**
1916 * Gets the Shift Definitions of the given Resource between the two given Dates.
1917 *
1918 * CSF has its own "Get Resource Shifts" API in addition to JTF providing it is
1919 * because CSF is still calling JTF Calendar API rather than JTF Calendar 24 API.
1920 * Going forward, we should be calling JTF_CALENDAR24_PUB rather than
1921 * JTF_CALENDAR_PUB.
1922 * Because of this the following Shift Definition is returned as two Shifts.
1923 * <br>
1924 * Shift Construct #101: Start = 1-JAN-2005 18:00:00 to 2-JAN-2005 07:00:00
1925 * is returned as
1926 * Shift Record #1
1927 * Shift Construct = 101
1928 * Shift Date = 1-JAN-2005
1929 * Start Time = 18:00
1930 * End Time = 23:59
1931 *
1932 * Shift Record #2
1933 * Shift Construct = 101
1934 * Shift Date = 2-JAN-2005
1935 * Start Time = 00:00
1936 * End Time = 07:00
1937 * <br>
1938 * Note that Shift Record#1 and Shift Record#2 are adjacent in the returned
1939 * Shifts Table. Morever both has the same Shift Construct ID and the difference
1940 * between End Time of the first record and the start time of the second is
1941 * One Minute (1/1440 days).
1942 *
1943 * This feature is being used by this API to merge those shifts in a single
1944 * record structure.
1945 *
1946 * @param p_api_version API Version (1.0)
1947 * @param p_init_msg_list Initialize Message List
1948 * @param x_return_status Return Status of the Procedure.
1949 * @param x_msg_count Number of Messages in the Stack.
1950 * @param x_msg_data Stack of Error Messages.
1951 * @param p_resource_id Resource Identifier for whom Shifts are required.
1952 * @param p_resource_type Resource Type of the above Resource.
1953 * @param p_start_date Start of the Window between which Shifts are required.
1954 * @param p_end_date End of the Window between which Shifts are required.
1955 * @param x_shifts Shift Definitions
1956 */
1957 PROCEDURE get_resource_shifts(
1958 p_api_version IN NUMBER
1959 , p_init_msg_list IN VARCHAR2
1960 , x_return_status OUT NOCOPY VARCHAR2
1961 , x_msg_count OUT NOCOPY NUMBER
1962 , x_msg_data OUT NOCOPY VARCHAR2
1963 , p_resource_id IN NUMBER
1964 , p_resource_type IN VARCHAR2
1965 , p_start_date IN DATE
1966 , p_end_date IN DATE
1967 , x_shifts OUT NOCOPY shift_tbl_type
1968 ) IS
1969 l_api_name CONSTANT VARCHAR2(30) := 'GET_RESOURCE_SHIFTS';
1970 l_api_version CONSTANT NUMBER := 1.0;
1971
1972 l_multiday_shifts jtf_calendar_pub.shift_tbl_type;
1973 l_time DATE;
1974 l_shift_starttime DATE;
1975 l_shift_endtime DATE;
1976 i PLS_INTEGER; -- Iterator for JTF's Shift Table
1977 j PLS_INTEGER; -- Iterator for CSF's Shift Table
1978 BEGIN
1979 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
1980 RAISE fnd_api.g_exc_unexpected_error;
1981 END IF;
1982
1983 IF fnd_api.to_boolean(p_init_msg_list) THEN
1984 fnd_msg_pub.initialize;
1985 END IF;
1986
1987 x_return_status := fnd_api.g_ret_sts_success;
1988
1989 jtf_calendar_pub.get_resource_shifts(
1990 p_api_version => 1.0
1991 , p_resource_id => p_resource_id
1992 , p_resource_type => p_resource_type
1993 , p_start_date => p_start_date
1994 , p_end_date => p_end_date
1995 , x_return_status => x_return_status
1996 , x_msg_count => x_msg_count
1997 , x_msg_data => x_msg_data
1998 , x_shift => l_multiday_shifts
1999 );
2000
2001 IF x_return_status <> fnd_api.g_ret_sts_success OR l_multiday_shifts.COUNT = 0 THEN
2002 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
2003 RAISE fnd_api.g_exc_unexpected_error;
2004 END IF;
2005 RAISE fnd_api.g_exc_error;
2006 END IF;
2007
2008 i := l_multiday_shifts.FIRST;
2009 j := 0;
2010 WHILE i IS NOT NULL LOOP
2011 l_time := to_date(l_multiday_shifts(i).start_time, 'HH24:MI');
2012 l_shift_starttime := l_multiday_shifts(i).shift_date + (l_time - trunc(l_time));
2013 l_time := to_date(l_multiday_shifts(i).end_time, 'HH24:MI');
2014 l_shift_endtime := l_multiday_shifts(i).shift_date + (l_time - trunc(l_time));
2015
2016 IF l_shift_endtime > p_start_date AND l_shift_starttime < p_end_date THEN
2017 -- Check whether the previous shift is same as the current one with a Minute Difference
2018 IF ( x_shifts.EXISTS(j)
2019 AND x_shifts(j).shift_construct_id = l_multiday_shifts(i).shift_construct_id
2020 AND (x_shifts(j).end_datetime + 1/1440) = l_shift_starttime )
2021 THEN
2022 -- Its the same shift but crossing the 24 Hour Boundary. Merge them.
2023 x_shifts(j).end_datetime := l_shift_endtime;
2024 ELSE
2025 j := j+1;
2026 x_shifts(j).shift_construct_id := l_multiday_shifts(i).shift_construct_id;
2027 x_shifts(j).availability_type := l_multiday_shifts(i).availability_type;
2028 x_shifts(j).start_datetime := l_shift_starttime;
2029 x_shifts(j).end_datetime := l_shift_endtime;
2030 END IF;
2031 END IF;
2032
2033 i := l_multiday_shifts.NEXT(i);
2034 END LOOP;
2035
2036 EXCEPTION
2037 WHEN fnd_api.g_exc_error THEN
2038 x_return_status := fnd_api.g_ret_sts_error;
2039 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2040 WHEN fnd_api.g_exc_unexpected_error THEN
2041 x_return_status := fnd_api.g_ret_sts_unexp_error;
2042 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2043 WHEN OTHERS THEN
2044 x_return_status := fnd_api.g_ret_sts_unexp_error;
2045 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
2046 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
2047 END IF;
2048 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2049 END get_resource_shifts;
2050
2051
2052 BEGIN
2053 init_assign_errors;
2054 END csf_resource_pub;
[Home] [Help]