1 PACKAGE BODY CSF_RESOURCE_PUB AS
2 /* $Header: CSFPRESB.pls 120.58 2011/04/26 10:09:38 vakulkar 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 g_res_add_prof VARCHAR2(200);
9 g_debug VARCHAR2(1):= NVL(fnd_profile.value('AFLOG_ENABLED'), 'N');
10
11 g_debug_level NUMBER := NVL(fnd_profile.value_specific('AFLOG_LEVEL'), fnd_log.level_event);
12
13
14 TYPE qualifier_info_rec_type IS RECORD(
15 qual_usg_id jtf_seeded_qual_usgs_v.qual_usg_id%TYPE
16 , label jtf_seeded_qual_usgs_v.seeded_qual_name%TYPE
17 );
18
19 TYPE qualifier_info_tbl_type IS TABLE OF qualifier_info_rec_type
20 INDEX BY BINARY_INTEGER;
21
22 TYPE varchar2_tbl_type IS TABLE OF VARCHAR2(32)
23 INDEX BY BINARY_INTEGER;
24
25 g_assign_errors varchar2_tbl_type;
26 g_all_qualifiers qualifier_info_tbl_type;
27
28 TYPE res_type_name_rec_type IS RECORD(
29 resource_type_code jtf_objects_tl.object_code%TYPE
30 , resource_type_name jtf_objects_tl.NAME%TYPE
31 );
32
33 TYPE res_type_name_tbl_type IS
34 TABLE OF res_type_name_rec_type;
35
36 g_res_type_name_tbl res_type_name_tbl_type;
37
38 /**
39 * PLSQL Record Type to contain information about a Resource
40 * along with his Address. Note that this couldnt be put
41 * as an attribute as part of RESOURCE_REC_TYPE as Forms
42 * cant use Records which have Complex Data Types as attributes
43 */
44 TYPE resource_cache_rec_type IS RECORD(
45 resource_id NUMBER
46 , resource_type jtf_objects_b.object_code%TYPE
47 , resource_name jtf_rs_resource_extns_tl.resource_name%TYPE
48 , resource_number jtf_rs_resource_extns.resource_number%TYPE
49 , address csf_resource_address_pvt.address_rec_type
50 );
51
52 /**
53 * PLSQL Index By Table Type to contain information about many Resources
54 * where each element is of type RESOURCE_CACHE_REC_TYPE.
55 */
56 TYPE resource_cache_tbl_type IS TABLE OF resource_cache_rec_type
57 INDEX BY BINARY_INTEGER;
58
59 g_res_info_cache resource_cache_tbl_type;
60
61 PROCEDURE debug(p_level NUMBER, p_module VARCHAR2, p_message VARCHAR2) IS
62 BEGIN
63 IF g_debug = 'Y' AND p_level >= g_debug_level THEN
64 fnd_log.string(p_level, 'csf.plsql.CSF_RESOURCE_PUB.' || p_module, p_message);
65 END IF;
66 END debug;
67
68 /**
69 * Initializes the Table of possible Error Messages that can be
70 * encountered during Resource Selection Process.
71 * <br>
72 * Index to the table G_ASSIGN_ERRORS represent the flags that have
73 * been set to select the resources - namely Skills (S), Territories (T)
74 * Installed Base (I) and Contracts (B). When a Flag is set to 'Y', the
75 * corresponding bit is set to 1. Otherwise it is 0.
76 * <br>
77 * Examples:
78 * S T I C
79 * - - - -
80 * 0 0 0 0 No selection criteria
81 * 0 0 0 1 Only contracts
82 * 0 0 1 0 Only installed base
83 */
84 PROCEDURE init_assign_errors IS
85 BEGIN
86 g_assign_errors(0) := 'CSF_NO_RES_SEL_CRIT'; -- 0000
87 g_assign_errors(1) := 'CSF_NO_CONTRACT_RES'; -- 0001
88 g_assign_errors(2) := 'CSF_NO_IB_RES'; -- 0010
89 g_assign_errors(3) := 'CSF_NO_CONTRACT_IB_RES'; -- 0011
90 g_assign_errors(4) := 'CSF_NO_TERR_RES'; -- 0100
91 g_assign_errors(5) := 'CSF_TERR_CONTRACT_RES'; -- 0101
92 g_assign_errors(6) := 'CSF_NO_TERR_IB_RES'; -- 0110
93 g_assign_errors(8) := 'CSF_NO_SKILLED_RES'; -- 1000
94 g_assign_errors(9) := 'CSF_NO_CONTR_SKILL_RES'; -- 1001
95 g_assign_errors(10) := 'CSF_NO_IB_SKILL_RES'; -- 1010
96 g_assign_errors(11) := 'CSF_NO_CONTR_IB_SKILL_RES'; -- 1011
97 g_assign_errors(12) := 'CSF_NO_TERR_SKILL_RES'; -- 1100
98 g_assign_errors(13) := 'CSF_NO_TERR_CONTRACT_SKILL_RES'; -- 1101
99 g_assign_errors(14) := 'CSF_NO_TERR_IB_SKILL_RES'; -- 1110
100 g_assign_errors(15) := 'CSF_NO_TERR_CONTRACT_IB_RES'; -- 1111
101 END init_assign_errors;
102
103 /**
104 * Gets the Task Information in the format as desired by JTF Assignment
105 * Manager API.
106 *
107 * @param p_task_id Task Identifier
108 * @returns Task Record (JTF_ASSIGN_PUB.JTF_SRV_TASK_REC_TYPE).
109 */
110 FUNCTION get_srv_task_rec(p_task_id IN NUMBER)
111 RETURN jtf_assign_pub.jtf_srv_task_rec_type IS
112 l_rec jtf_assign_pub.jtf_srv_task_rec_type;
113 l_contract_service_id NUMBER;
114 l_planned_start_date date;
115 -- Task, SR, Party and Address Information
116 CURSOR c_rec IS
117 SELECT tb.task_id task_id
118 , ib.incident_id service_request_id
119 , ib.customer_id party_id
120 , lo.country
121 , tb.address_id party_site_id
122 , lo.city
123 , lo.postal_code
124 , lo.state
125 , lo.county
126 , pa.party_name comp_name_range
127 , lo.province
128 , pa.employees_total num_of_employees
129 , tb.task_type_id
130 , tb.task_status_id
131 , tb.task_priority_id
132 , ib.incident_type_id
133 , ib.incident_severity_id
134 , ib.incident_urgency_id
135 , ib.problem_code
136 , ib.incident_status_id
137 , ib.platform_id
138 , ib.site_id support_site_id
139 , ib.customer_site_id
140 , ib.sr_creation_channel
141 , ib.inventory_item_id
142 , ib.problem_code squal_char12
143 , ib.comm_pref_code squal_char13
144 , ib.platform_id squal_num12
145 , ib.inv_platform_org_id squal_num13
146 , ib.category_id squal_num14
147 , ib.inventory_item_id squal_num15
148 , ib.org_id squal_16 -- THIS CODE WAS COMMENTED BY VAKULKAR AS TERRITORY POPULATE VALUE3_ID AS ORG_ID
149 -- 1ST VALUE WILL BE 1373( Inv.Items - COMPUTER.DESKTOP - Desktop Computers)
150 -- 2ND VALUE WILL BE 8980 (FS54888 - Sentinel Standard Desktop - FS Series)
151 -- 3RD VALUE IS 204 -- THAT IS WHAT TERRITORY IS CREATED FOR
152 -- AND WE ARE PASSING ib.inv_organization_id IN squal_num16 WHICH HAS VALUE 207
153 -- SO IT DOES NOT QUALIFY TERRITORY. SO now i am passsign org_id as squal_num16
154 , ib.owner_group_id squal_num17
155 , ib.language_id squal_num30
156 , ib.contract_service_id
157 , TB.PLANNED_START_DATE
158 , ib.system_id
159 , ib.cust_pref_lang_code
160 FROM jtf_tasks_b tb
161 , cs_incidents_all_b ib
162 , cs_incidents_all_tl it
163 , hz_locations lo
164 , hz_parties pa
165 WHERE tb.task_id = p_task_id
166 AND tb.source_object_type_code = 'SR'
167 AND tb.source_object_id = ib.incident_id
168 AND tb.source_object_id = it.incident_id
169 AND it.LANGUAGE = USERENV('lang')
170 AND lo.location_id = csf_tasks_pub.get_task_location_id(tb.task_id, tb.address_id, tb.location_id)
171 AND ib.customer_id = pa.party_id(+);
172
173 -- Phone Area Code
174 CURSOR c_contact_point(b_party_id NUMBER) IS
175 SELECT phone_area_code
176 FROM hz_contact_points
177 WHERE owner_table_id = b_party_id
178 AND owner_table_name = 'HZ_PARTIES'
179 AND contact_point_type = 'PHONE'
180 AND primary_flag = 'Y';
181
182 -- Service Item ID and Organization ID
183 CURSOR c_contract(b_contract_service_id NUMBER) IS
184 SELECT TO_NUMBER(object1_id1) item_id
185 , TO_NUMBER(object1_id2) org_id
186 FROM okc_k_items
187 WHERE cle_id = b_contract_service_id;
188
189 -- Contact VIP code
190 CURSOR c_class_code(b_party_id NUMBER) IS
191 SELECT class_code
192 FROM hz_code_assignments
193 WHERE owner_table_name = 'HZ_PARTIES'
194 AND owner_table_id = b_party_id;
195 BEGIN
196 OPEN c_rec;
197
198 FETCH c_rec
199 INTO l_rec.task_id
200 , l_rec.service_request_id
201 , l_rec.party_id
202 , l_rec.country
203 , l_rec.party_site_id
204 , l_rec.city
205 , l_rec.postal_code
206 , l_rec.state
207 , l_rec.county
208 , l_rec.comp_name_range
209 , l_rec.province
210 , l_rec.num_of_employees
211 , l_rec.task_type_id
212 , l_rec.task_status_id
213 , l_rec.task_priority_id
214 , l_rec.incident_type_id
215 , l_rec.incident_severity_id
216 , l_rec.incident_urgency_id
217 , l_rec.problem_code
218 , l_rec.incident_status_id
219 , l_rec.platform_id
220 , l_rec.support_site_id
221 , l_rec.customer_site_id
222 , l_rec.sr_creation_channel
223 , l_rec.inventory_item_id
224 , l_rec.squal_char12
225 , l_rec.squal_char13
226 , l_rec.squal_num12
227 , l_rec.squal_num13
228 , l_rec.squal_num14
229 , l_rec.squal_num15
230 , l_rec.squal_num16
231 , l_rec.squal_num17
232 , l_rec.squal_num30
233 , l_contract_service_id
234 , L_PLANNED_START_DATE
235 , l_rec.SQUAL_NUM60
236 , l_rec.SQUAL_CHAR20;
237
238 IF c_rec%FOUND THEN
239 IF l_rec.party_id IS NOT NULL THEN
240 -- Contact Phone Area Code
241 OPEN c_contact_point(l_rec.party_id);
242 FETCH c_contact_point INTO l_rec.area_code;
243 CLOSE c_contact_point;
244
245 -- contact VIP code
246 OPEN c_class_code(l_rec.party_id);
247 FETCH c_class_code INTO l_rec.squal_char11;
248 CLOSE c_class_code;
249 END IF;
250
251 IF l_contract_service_id IS NOT NULL THEN
252 -- Service item item_id and org_id
253 OPEN c_contract(l_contract_service_id);
254 FETCH c_contract INTO l_rec.squal_num18, l_rec.squal_num19;
255 CLOSE c_contract;
256 END IF;
257 ELSE
258 -- fill in only the task_id
259 l_rec.task_id := p_task_id;
260 END IF;
261
262 CLOSE c_rec;
263
264 if l_planned_start_date < sysdate
265 then
266 l_planned_start_date := sysdate;
267 end if;
268
269 l_rec.time_of_day := to_char(l_planned_start_date,'HH24:MI');
270 l_rec.day_of_week := to_char(l_planned_start_date,'D');
271
272 RETURN l_rec;
273 END get_srv_task_rec;
274
275 /**
276 * Gets the Seeded Enabled Qualifier Names (and if required Labels) and
277 * populates the table G_ALL_QUALIFIERS.
278 */
279 PROCEDURE get_all_qualifiers IS
280 k PLS_INTEGER;
281 CURSOR c_desc IS
282 SELECT qual_usg_id qual_usg_id
283 , seeded_qual_name label
284 FROM jty_all_enabled_attributes_v
285 WHERE source_id = -1002
286 AND qual_type_id IN(-1002, -1005, -1006)
287 ORDER BY UPPER(seeded_qual_name);
288 l_rec qualifier_info_rec_type;
289 BEGIN
290 g_all_qualifiers.DELETE;
291 OPEN c_desc;
292 LOOP
293 FETCH c_desc INTO l_rec;
294 EXIT WHEN c_desc%NOTFOUND;
295 g_all_qualifiers(c_desc%ROWCOUNT) := l_rec;
296 END LOOP;
297 CLOSE c_desc;
298 END get_all_qualifiers;
299
300 /**
301 * Returns the Display Value for the Qualifier.
302 *
303 * If Tracing is not enabled, then it returns only Value concatenated
304 * with the Associated Value. If Tracing is enabled, it uses the SQL
305 * associated with the Qualifier and gets the Name / Description for
306 * the Qualifier (ID to Name Conversion).
307 *
308 * @param p_index Index to the Global All Qualifiers Table
309 * @param p_value Value for the Qualifier
310 * @param p_associated_value Associated Value for the Qualifier
311 */
312 FUNCTION get_display_value(
313 p_index IN PLS_INTEGER
314 , p_value IN VARCHAR2
315 , p_associated_value IN VARCHAR2
316 )
317 RETURN VARCHAR2 IS
318 l_tmp VARCHAR2(4000);
319 l_value VARCHAR2(360);
320 BEGIN
321 -- for the default case use the provided values
322 IF p_associated_value IS NULL THEN
323 l_value := p_value;
324 ELSE
325 l_value := SUBSTR(p_value || '/' || p_associated_value, 1, 360);
326 END IF;
327 RETURN l_value;
328 END get_display_value;
329
330 PROCEDURE set_generic_planwindow (
331 p_res_tbl IN OUT NOCOPY jtf_assign_pub.assignresources_tbl_type
332 , p_start DATE
333 , p_end DATE
334 ) IS
335 i PLS_INTEGER;
336 BEGIN
337 i := p_res_tbl.FIRST;
338 WHILE i IS NOT NULL LOOP
339 p_res_tbl(i).start_date := p_start;
340 p_res_tbl(i).end_date := p_end;
341 i := p_res_tbl.NEXT(i);
342 END LOOP;
343 END set_generic_planwindow;
344
345 /**
346 * Adds the Qualifier identified by the Index to the global Task Qualifer Table with
347 * the value taken from the given Task Record and only when the value is Not Null.
348 * <br>
349 * Uses a Hard Coded Mapping between JTF_SEEDED_QUAL_USGS_V.QUAL_USG_ID
350 * and the fields in JTF_ASSIGN_PUB.JTF_SRV_TASK_REC_TYPE.
351 * <br>
352 * Qualifiers of type -1211, -1212 and -1218 have been disabled and
353 * therefore wont be set by this API.
354 *
355 * @param p_qualifiers Qualifier Table storing the Qualifiers
356 * @param p_index Index to the Global All Qualifiers Table
357 * @param p_task_rec Task Record containing the required Information
358 */
359 PROCEDURE add_qualifier(
360 p_qualifier_tbl IN OUT NOCOPY resource_qualifier_tbl_type
361 , p_index IN PLS_INTEGER
362 , p_task_rec IN jtf_assign_pub.jtf_srv_task_rec_type
363 ) IS
364 l_value VARCHAR2(360);
365 l_associated_value VARCHAR2(360);
366 i PLS_INTEGER;
367 BEGIN
368 IF g_all_qualifiers(p_index).qual_usg_id = -1037 THEN
369 l_value := p_task_rec.party_id;
370 ELSIF g_all_qualifiers(p_index).qual_usg_id = -1038 THEN
371 l_value := p_task_rec.country;
372 ELSIF g_all_qualifiers(p_index).qual_usg_id = -1039 THEN
373 l_value := p_task_rec.party_site_id;
374 ELSIF g_all_qualifiers(p_index).qual_usg_id = -1040 THEN
375 l_value := p_task_rec.city;
376 ELSIF g_all_qualifiers(p_index).qual_usg_id = -1041 THEN
377 l_value := p_task_rec.postal_code;
378 ELSIF g_all_qualifiers(p_index).qual_usg_id = -1042 THEN
379 l_value := p_task_rec.state;
380 ELSIF g_all_qualifiers(p_index).qual_usg_id = -1043 THEN
381 l_value := p_task_rec.area_code;
382 ELSIF g_all_qualifiers(p_index).qual_usg_id = -1044 THEN
383 l_value := p_task_rec.county;
384 ELSIF g_all_qualifiers(p_index).qual_usg_id = -1045 THEN
385 l_value := p_task_rec.comp_name_range;
386 ELSIF g_all_qualifiers(p_index).qual_usg_id = -1046 THEN
387 l_value := p_task_rec.province;
388 ELSIF g_all_qualifiers(p_index).qual_usg_id = -1047 THEN
389 l_value := p_task_rec.num_of_employees;
390 ELSIF g_all_qualifiers(p_index).qual_usg_id = -1060 THEN
391 l_value := p_task_rec.task_type_id;
392 ELSIF g_all_qualifiers(p_index).qual_usg_id = -1061 THEN
393 l_value := p_task_rec.task_status_id;
394 ELSIF g_all_qualifiers(p_index).qual_usg_id = -1062 THEN
395 l_value := p_task_rec.task_priority_id;
396 ELSIF g_all_qualifiers(p_index).qual_usg_id = -1048 THEN
397 l_value := p_task_rec.incident_type_id;
398 ELSIF g_all_qualifiers(p_index).qual_usg_id = -1049 THEN
399 l_value := p_task_rec.incident_severity_id;
400 ELSIF g_all_qualifiers(p_index).qual_usg_id = -1050 THEN
401 l_value := p_task_rec.incident_urgency_id;
402 ELSIF g_all_qualifiers(p_index).qual_usg_id = -1051 THEN
403 l_value := p_task_rec.problem_code;
404 ELSIF g_all_qualifiers(p_index).qual_usg_id = -1091 THEN
405 l_value := p_task_rec.incident_status_id;
406 ELSIF g_all_qualifiers(p_index).qual_usg_id = -1092 THEN
407 l_value := p_task_rec.platform_id;
408 ELSIF g_all_qualifiers(p_index).qual_usg_id = -1093 THEN
409 l_value := p_task_rec.support_site_id;
410 ELSIF g_all_qualifiers(p_index).qual_usg_id = -1094 THEN
411 l_value := p_task_rec.customer_site_id;
412 ELSIF g_all_qualifiers(p_index).qual_usg_id = -1095 THEN
413 l_value := p_task_rec.sr_creation_channel;
414 ELSIF g_all_qualifiers(p_index).qual_usg_id = -1096 THEN
415 l_value := p_task_rec.inventory_item_id;
416 ELSIF g_all_qualifiers(p_index).qual_usg_id = -1210 THEN
417 l_value := p_task_rec.squal_num14;
418 l_associated_value :=p_task_rec.squal_num15||'-'||p_task_rec.squal_num16;
419 ELSIF g_all_qualifiers(p_index).qual_usg_id = -1216 THEN
420 l_value := p_task_rec.squal_num18;
421 l_associated_value := p_task_rec.squal_num19;
422 ELSIF g_all_qualifiers(p_index).qual_usg_id = -1213 THEN
423 l_value := p_task_rec.squal_char20;
424 ELSIF g_all_qualifiers(p_index).qual_usg_id = -1215 THEN
425 l_value := p_task_rec.squal_char11;
426 ELSIF g_all_qualifiers(p_index).qual_usg_id = -1214 THEN
427 l_value := p_task_rec.squal_char13;
428 ELSIF g_all_qualifiers(p_index).qual_usg_id = -1217 THEN
429 l_value := p_task_rec.squal_num17;
430 ELSIF g_all_qualifiers(p_index).qual_usg_id = -1734 THEN
431 l_value := p_task_Rec.day_of_week;
432 ELSIF g_all_qualifiers(p_index).qual_usg_id = -1744 THEN
433 L_VALUE := P_TASK_REC.TIME_OF_DAY;
434 ELSIF G_ALL_QUALIFIERS(P_INDEX).QUAL_USG_ID = -1206 THEN
435 l_value := p_task_Rec.SQUAL_NUM60;
436 ELSIF G_ALL_QUALIFIERS(P_INDEX).QUAL_USG_ID = -1218 THEN
437 l_value := p_task_Rec.SQUAL_NUM15;
438 l_associated_value :=p_task_rec.squal_num16;
439 END IF;
440
441 IF l_value IS NOT NULL THEN
442 i := p_qualifier_tbl.COUNT + 1;
443 p_qualifier_tbl(i).qual_usg_id := g_all_qualifiers(p_index).qual_usg_id;
444 p_qualifier_tbl(i).label := g_all_qualifiers(p_index).label;
445 p_qualifier_tbl(i).use_flag := 'Y';
446 p_qualifier_tbl(i).value := l_value;
447 p_qualifier_tbl(i).associated_value := l_associated_value;
448 p_qualifier_tbl(i).display_value := get_display_value(p_index, l_value, l_associated_value);
449 END IF;
450 END add_qualifier;
451
452 /**
453 * Returns the Qualifier Table having the list of valid Qualifiers
454 * based on the Task Information of the given Task ID.
455 */
456 FUNCTION get_res_qualifier_table(p_task_id NUMBER)
457 RETURN resource_qualifier_tbl_type IS
458 m PLS_INTEGER;
459 l_task_rec jtf_assign_pub.jtf_srv_task_rec_type;
460 l_qualifier_tbl resource_qualifier_tbl_type;
461 BEGIN
462 -- Fetch all the Enabled Qualifiers
463 IF g_all_qualifiers.COUNT = 0 THEN
464 get_all_qualifiers;
465 END IF;
466
467 -- Get the Task Information
468 l_task_rec := get_srv_task_rec(p_task_id);
469
470 -- Loop thru the Qualifiers and add the valid Qualifier alone to the Table
471 m := g_all_qualifiers.FIRST;
472 WHILE m IS NOT NULL LOOP
473 add_qualifier(l_qualifier_tbl, m, l_task_rec);
474 m := g_all_qualifiers.NEXT(m);
475 END LOOP;
476
477 RETURN l_qualifier_tbl;
478 END get_res_qualifier_table;
479
480 /**
481 * Converts the given Qualifier Table to Assignment Manager API Record
482 * type.
483 * Assembles the selected Qualifiers for this Task from the Qualifier
484 * Table in to a Record Type understandable by JTF Assignment Manager.
485 * <br>
486 * Uses a Hard Coded Mapping between JTF_SEEDED_QUAL_USGS_V.QUAL_USG_ID
487 * and the fields in JTF_ASSIGN_PUB.JTF_SRV_TASK_REC_TYPE.
488 * <br>
489 * The Task and SR Number must be set by the caller and wont be set by
490 * this API. Moreover Qualifiers of type -1211, -1212 and -1218 have
491 * been disabled and therefore wont be set by this API.
492 *
493 * @param p_table Qualifier Table having the list of Task Qualifiers
494 */
495 FUNCTION get_qualified_task_rec(p_table resource_qualifier_tbl_type)
496 RETURN jtf_assign_pub.jtf_srv_task_rec_type IS
497 k PLS_INTEGER;
498 task_rec jtf_assign_pub.jtf_srv_task_rec_type;
499 BEGIN
500 k := p_table.FIRST;
501 WHILE k IS NOT NULL LOOP
502 IF p_table(k).use_flag = 'Y' THEN
503 IF p_table(k).qual_usg_id = -1037 THEN
504 task_rec.party_id := p_table(k).VALUE;
505 ELSIF p_table(k).qual_usg_id = -1038 THEN
506 task_rec.country := p_table(k).VALUE;
507 ELSIF p_table(k).qual_usg_id = -1039 THEN
508 task_rec.party_site_id := p_table(k).VALUE;
509 ELSIF p_table(k).qual_usg_id = -1040 THEN
510 task_rec.city := p_table(k).VALUE;
511 ELSIF p_table(k).qual_usg_id = -1041 THEN
512 task_rec.postal_code := p_table(k).VALUE;
513 ELSIF p_table(k).qual_usg_id = -1042 THEN
514 task_rec.state := p_table(k).VALUE;
515 ELSIF p_table(k).qual_usg_id = -1043 THEN
516 task_rec.area_code := p_table(k).VALUE;
517 ELSIF p_table(k).qual_usg_id = -1044 THEN
518 task_rec.county := p_table(k).VALUE;
519 ELSIF p_table(k).qual_usg_id = -1045 THEN
520 task_rec.comp_name_range := p_table(k).VALUE;
521 ELSIF p_table(k).qual_usg_id = -1046 THEN
522 task_rec.province := p_table(k).VALUE;
523 ELSIF p_table(k).qual_usg_id = -1047 THEN
524 task_rec.num_of_employees := p_table(k).VALUE;
525 ELSIF p_table(k).qual_usg_id = -1060 THEN
526 task_rec.task_type_id := p_table(k).VALUE;
527 ELSIF p_table(k).qual_usg_id = -1061 THEN
528 task_rec.task_status_id := p_table(k).VALUE;
529 ELSIF p_table(k).qual_usg_id = -1062 THEN
530 task_rec.task_priority_id := p_table(k).VALUE;
531 ELSIF p_table(k).qual_usg_id = -1048 THEN
532 task_rec.incident_type_id := p_table(k).VALUE;
533 ELSIF p_table(k).qual_usg_id = -1049 THEN
534 task_rec.incident_severity_id := p_table(k).VALUE;
535 ELSIF p_table(k).qual_usg_id = -1050 THEN
536 task_rec.incident_urgency_id := p_table(k).VALUE;
537 ELSIF p_table(k).qual_usg_id = -1051 THEN
538 task_rec.problem_code := p_table(k).VALUE;
539 ELSIF p_table(k).qual_usg_id = -1091 THEN
540 task_rec.incident_status_id := p_table(k).VALUE;
541 ELSIF p_table(k).qual_usg_id = -1092 THEN
542 task_rec.platform_id := p_table(k).VALUE;
543 ELSIF p_table(k).qual_usg_id = -1093 THEN
544 task_rec.support_site_id := p_table(k).VALUE;
545 ELSIF p_table(k).qual_usg_id = -1094 THEN
546 task_rec.customer_site_id := p_table(k).VALUE;
547 ELSIF p_table(k).qual_usg_id = -1095 THEN
548 task_rec.sr_creation_channel := p_table(k).VALUE;
549 ELSIF p_table(k).qual_usg_id = -1096 THEN
550 task_rec.inventory_item_id := p_table(k).VALUE;
551 ELSIF p_table(k).qual_usg_id = -1210 THEN
552 task_rec.squal_num14 := p_table(k).VALUE;
553 task_rec.squal_num15 := SUBSTR(p_table(k).associated_value,1,INSTR(p_table(k).associated_value,'-')-1);
554 if task_rec.squal_num15 is not null
555 then
556 task_rec.squal_num16 := SUBSTR(p_table(k).associated_value,INSTR(p_table(k).associated_value,'-')+1,LENGTH(p_table(k).associated_value));
557 end if;
558 ELSIF p_table(k).qual_usg_id = -1217 THEN
559 task_rec.squal_num17 := p_table(k).VALUE;
560 ELSIF p_table(k).qual_usg_id = -1216 THEN
561 task_rec.squal_num18 := p_table(k).VALUE;
562 task_rec.squal_num19 := p_table(k).associated_value;
563 ELSIF p_table(k).qual_usg_id = -1213 THEN
564 task_rec.squal_char20 := p_table(k).VALUE;
565 ELSIF p_table(k).qual_usg_id = -1215 THEN
566 task_rec.squal_char11 := p_table(k).VALUE;
567 ELSIF p_table(k).qual_usg_id = -1214 THEN
568 task_rec.squal_char13 := p_table(k).VALUE;
569 ELSIF p_table(k).qual_usg_id = -1734 THEN
570 task_rec.DAY_OF_WEEK := p_table(k).VALUE;
571 ELSIF p_table(k).qual_usg_id = -1744 THEN
572 TASK_REC.TIME_OF_DAY := P_TABLE(K).VALUE;
573 ELSIF p_table(k).qual_usg_id = -1206 THEN
574 task_rec.squal_num60 := p_table(k).VALUE;
575 ELSIF p_table(k).qual_usg_id = -1218 THEN
576 task_rec.squal_num15 := p_table(k).VALUE;
577 task_rec.squal_num16 := p_table(k).associated_value;
578
579 END IF;
580 END IF;
581 k := p_table.NEXT(k);
582 END LOOP;
583 RETURN task_rec;
584 END get_qualified_task_rec;
585
586 /**
587 * Intersect the Resource results found in two tables and returns a
588 * Table having only those Resources found in both the Tables.
589 *
590 * The parameters P_START and P_END signify the Plan Window for which
591 * Resources are desired. If either of the input tables doesnt have
592 * the Plan Window stamped for any resource, these two dates will be used
593 * in place of them. After this action, the most restrictive Plan Window
594 * among the two windows is used as the Plan Window of the output table.
595 * <br>
596 * Side Effect of the API. Second Table may have entries deleted after
597 * the operation.
598 *
599 * @param p_res_1_tbl Resource Table 1
600 * @param p_res_2_tbl Resource Table 2
601 * @param p_start Start Date of the Window
602 * @param p_end End Date of the Window
603 * @return Common Resource Table (JTF_ASSIGN_PUB.ASSIGNRESOURCES_TBL_TYPE)
604 */
605 FUNCTION intersect_results(
606 p_res_1_tbl IN OUT NOCOPY jtf_assign_pub.assignresources_tbl_type
607 , p_res_2_tbl IN OUT NOCOPY jtf_assign_pub.assignresources_tbl_type
608 , p_start DATE
609 , p_end DATE
610 ) RETURN jtf_assign_pub.assignresources_tbl_type IS
611 i PLS_INTEGER;
612 j PLS_INTEGER;
613 k PLS_INTEGER;
614 l_res_tbl jtf_assign_pub.assignresources_tbl_type;
615 BEGIN
616 k := 0;
617 i := p_res_1_tbl.FIRST;
618 WHILE i IS NOT NULL LOOP
619 j := p_res_2_tbl.FIRST;
620 WHILE j IS NOT NULL LOOP
621 EXIT WHEN p_res_2_tbl(j).resource_id = p_res_1_tbl(i).resource_id
622 AND p_res_2_tbl(j).resource_type = p_res_1_tbl(i).resource_type;
623 j := p_res_2_tbl.NEXT(j);
624 END LOOP;
625
626 -- We have an intersection between first table and second. Add to the output table
627 IF j IS NOT NULL THEN
628 k := k + 1;
629 l_res_tbl(k) := p_res_1_tbl(i);
630 l_res_tbl(k).start_date := GREATEST( NVL(p_res_1_tbl(i).start_date, p_start)
631 , NVL(p_res_2_tbl(j).start_date, p_start)
632 );
633 l_res_tbl(k).end_date := LEAST( NVL(p_res_1_tbl(i).end_date, p_end)
634 , NVL(p_res_2_tbl(j).end_date, p_end)
635 );
636
637 IF p_res_2_tbl(j).skill_level IS NOT NULL THEN
638 l_res_tbl(k).skill_level := p_res_2_tbl(j).skill_level;
639 END IF;
640
641 IF p_res_2_tbl(j).TERR_ID IS NOT NULL AND p_res_2_tbl(j).TERR_ID <> -1 THEN
642 l_res_tbl(k).TERR_ID := p_res_2_tbl(j).TERR_ID;
643 END IF;
644
645 IF p_res_2_tbl(j).TERR_RANK IS NOT NULL AND p_res_2_tbl(j).TERR_RANK <> -1 THEN
646 l_res_tbl(k).TERR_RANK := p_res_2_tbl(j).TERR_RANK;
647 END IF;
648
649 p_res_2_tbl.DELETE(j); -- So that Table is smaller for other iterations.
650
651 END IF;
652 i := p_res_1_tbl.NEXT(i);
653 END LOOP;
654 RETURN l_res_tbl;
655 END intersect_results;
656
657 /**
658 * Union the Resource results found in two tables and returns a
659 * Table having all those Resources found in both the Tables.
660 *
661 * In case of duplicate rows, row from 'p_res_1_tbl' will override.
662 *
663 * Side Effect of the API. Second Table may have entries deleted after
664 * the operation.
665 *
666 * @param p_res_1_tbl Resource Table 1
667 * @param p_res_2_tbl Resource Table 2
668 * @return Common Resource Table (JTF_ASSIGN_PUB.ASSIGNRESOURCES_TBL_TYPE)
669 */
670 FUNCTION union_results(
671 p_res_1_tbl IN OUT NOCOPY jtf_assign_pub.assignresources_tbl_type
672 , p_res_2_tbl IN OUT NOCOPY jtf_assign_pub.assignresources_tbl_type
673 ) RETURN jtf_assign_pub.assignresources_tbl_type IS
674 i PLS_INTEGER;
675 j PLS_INTEGER;
676 k PLS_INTEGER;
677 l_res_tbl jtf_assign_pub.assignresources_tbl_type;
678 BEGIN
679 k := p_res_1_tbl.LAST;
680 i := p_res_2_tbl.FIRST;
681
682 IF p_res_1_tbl.COUNT > 0 AND p_res_2_tbl.COUNT < 1
683 THEN
684 RETURN p_res_1_tbl;
685 END IF;
686
687 IF p_res_2_tbl.COUNT > 0 AND p_res_1_tbl.COUNT < 1
688 THEN
689 RETURN p_res_2_tbl;
690 END IF;
691
692 IF p_res_2_tbl.COUNT = 0 AND p_res_1_tbl.COUNT = 0
693 THEN
694 RETURN l_res_tbl;
695 END IF;
696
697 WHILE i IS NOT NULL LOOP
698 j := p_res_1_tbl.FIRST;
699 WHILE j IS NOT NULL LOOP
700 EXIT WHEN p_res_2_tbl(i).resource_id = p_res_1_tbl(j).resource_id
701 AND p_res_2_tbl(i).resource_type = p_res_1_tbl(j).resource_type;
702
703 j := p_res_1_tbl.NEXT(j);
704 END LOOP;
705
706 IF j IS NULL THEN
707 p_res_1_tbl(k+1) := p_res_2_tbl(i);
708 k := p_res_1_tbl.LAST;
709 END IF;
710
711
712 i := p_res_2_tbl.NEXT(i);
713 END LOOP;
714 RETURN p_res_1_tbl;
715 END union_results;
716
717
718 /**
719 * Returns the Skilled Resources for a Task overlapping the given
720 * Plan Window.
721 * <br>
722 * This API will return the list of all Skilled Resources for the Task
723 * with the activity date lying between the passed Plan Window. There
724 * will be an individual plan window (Adapted) for each resource.
725 * <br>
726 * If Resource ID is passed, then the API returns only one record
727 * corresponding to the passed Resource if he has the Skill Active
728 * during the given Times as desired by the Task.
729 * <br>
730 * The API makes use of the profile CSF_SKILL_LEVEL_MATCH to determine
731 * whether the Resource has a Skill Level comparable with the Task requirements
732 * as dictated by the profile.
733 * Usage of the Profile is as follows
734 * 1 - EQUAL TO or SMALLER THAN
735 * 2 - EQUAL TO --> (Default Value)
736 * 3 - EQUAL TO or GREATER THAN
737 * <br>
738 * Note that the API is made a PROCEDURE from its initial version of being a
739 * FUNCTION so that we can use the NOCOPY Compiler Directive and avoid the
740 * expensive Table Copy Happening during each call.
741 *
742 * @param p_task_id Task ID of the Task to be considered
743 * @param p_start Start Date Time of the Window
744 * @param p_end End Date Time of the Window
745 * @param p_resource_id Resource ID (Optional)
746 * @param p_resource_type Resource Type (Optional)
747 * @param x_skilled_res_tbl Skilled Resource Table
748 */
749 PROCEDURE get_skilled_resources(
750 p_task_id IN NUMBER
751 , p_start IN DATE
752 , p_end IN DATE
753 , p_resource_id IN NUMBER DEFAULT NULL
754 , p_resource_type IN VARCHAR2 DEFAULT NULL
755 , x_skilled_res_tbl OUT NOCOPY jtf_assign_pub.assignresources_tbl_type
756 ) IS
757 l_levelmatch CONSTANT NUMBER := NVL(fnd_profile.VALUE('CSF_SKILL_LEVEL_MATCH'), 2);
758 j PLS_INTEGER;
759
760 CURSOR c_resource_plan_window IS
761 SELECT rs.resource_id
762 , rs.resource_type
763 , rs.winstart
764 , rs.winend
765 , rs.count_of_matching_skills
766 , rs.skill_level
767 FROM (SELECT rs.resource_id
768 , rs.resource_type
769 , GREATEST(
770 MAX(rs.start_date_active)
771 , NVL(MAX(ss.start_date_active), p_start)
772 , p_start
773 ) winstart
774 , LEAST(
775 NVL(MIN(rs.end_date_active + 1), p_end)
776 , NVL(MIN(ss.end_date_active + 1), p_end)
777 , p_end
778 ) winend
779 , COUNT(*) count_of_matching_skills
780 , SUM( 1/rsl.step_value ) skill_level
781 FROM csf_resource_skills_b rs
782 , csf_required_skills_b ts
783 , csf_skill_levels_b rsl
784 , csf_skill_levels_b tsl
785 , csf_skills_b ss
786 WHERE DECODE(
787 SIGN(rsl.step_value - tsl.step_value)
788 , -1, DECODE(l_levelmatch, 1, 'Y', 'N')
789 , 0, 'Y'
790 , 1, DECODE(l_levelmatch, 3, 'Y', 'N')
791 ) = 'Y'
792 AND rsl.skill_level_id = rs.skill_level_id
793 AND tsl.skill_level_id = ts.skill_level_id
794 AND TRUNC(rs.start_date_active) < p_end
795 AND (TRUNC(rs.end_date_active + 1) > p_start OR rs.end_date_active IS NULL)
796 AND (rs.resource_id = p_resource_id OR p_resource_id IS NULL)
797 AND (rs.resource_type = p_resource_type OR p_resource_type IS NULL)
798 AND NVL(ts.disabled_flag, 'N') <> 'Y'
799 AND ts.has_skill_type = 'TASK'
800 AND ts.has_skill_id = p_task_id
801 AND ss.skill_id(+) = rs.skill_id
802 AND (
803 ts.skill_type_id NOT IN (2, 3)
804 AND rs.skill_id = ts.skill_id
805 AND ts.skill_type_id = rs.skill_type_id
806 AND TRUNC(ss.start_date_active) < SYSDATE
807 AND TRUNC(NVL(ss.end_date_active, SYSDATE) + 1) > SYSDATE
808 OR ts.skill_type_id = 2
809 AND rs.skill_id = ts.skill_id
810 AND ts.skill_type_id = rs.skill_type_id
811 AND EXISTS (SELECT 1 FROM mtl_system_items_kfv msi WHERE msi.inventory_item_id = rs.skill_id)
812 OR ts.skill_type_id = 3
813 AND rs.skill_id = ts.skill_id
814 AND ts.skill_type_id = rs.skill_type_id
815 AND EXISTS (SELECT 1
816 FROM mtl_item_categories mic
817 WHERE mic.category_id = rs.skill_id
818 AND category_set_id = fnd_profile.VALUE('CS_SR_DEFAULT_CATEGORY_SET'))
819 /* OR ts.skill_type_id = 2
820 AND rs.skill_type_id = 3
821 AND NOT EXISTS (SELECT 1
822 FROM csf_required_skills_b ts2
823 , mtl_item_categories mic
824 WHERE NVL(ts2.disabled_flag, 'N') <> 'Y'
825 AND ts2.has_skill_type = ts.has_skill_type
826 AND ts2.has_skill_id = ts.has_skill_id
827 AND ts2.required_skill_id <> ts.required_skill_id
828 AND ts2.skill_type_id = 3
829 AND mic.inventory_item_id = ts.skill_id
830 AND mic.category_set_id = fnd_profile.VALUE('CS_SR_DEFAULT_CATEGORY_SET')
831 AND mic.category_id = ts2.skill_id)
832 AND NOT EXISTS (SELECT 1
833 FROM csf_resource_skills_b rs2
834 WHERE TRUNC(rs2.start_date_active) < p_end
835 AND (TRUNC(rs2.end_date_active + 1) > p_start OR rs2.end_date_active IS NULL)
836 AND rs2.resource_id = rs.resource_id
837 AND rs2.skill_id = ts.skill_id
838 AND rs2.skill_type_id = ts.skill_type_id)
839 AND EXISTS (SELECT category_id
840 FROM mtl_item_categories mic
841 WHERE mic.inventory_item_id = ts.skill_id
842 AND mic.category_set_id = fnd_profile.VALUE('CS_SR_DEFAULT_CATEGORY_SET')
843 AND mic.category_id = rs.skill_id) */
844 )
845 GROUP BY rs.resource_id, rs.resource_type) rs
846 , (
847 SELECT COUNT(*) count_of_req_skills
848 FROM csf_required_skills_b
849 WHERE NVL(disabled_flag, 'N') <> 'Y'
850 AND has_skill_type = 'TASK'
851 AND has_skill_id = p_task_id
852 ) ts
853 WHERE rs.count_of_matching_skills = ts.count_of_req_skills
854 AND rs.winstart < rs.winend;
855
856
857 BEGIN
858 x_skilled_res_tbl.DELETE;
859 j := 0;
860 FOR i IN c_resource_plan_window LOOP
861 j := j + 1;
862 x_skilled_res_tbl(j).resource_id := i.resource_id;
863 x_skilled_res_tbl(j).resource_type := i.resource_type;
864 x_skilled_res_tbl(j).start_date := i.winstart;
865 x_skilled_res_tbl(j).end_date := i.winend;
866 x_skilled_res_tbl(j).terr_id := -1;
867 x_skilled_res_tbl(j).terr_rank := -1;
868 x_skilled_res_tbl(j).skill_level := i.skill_level;
869 x_skilled_res_tbl(j).preference_type := 'SK';
870 END LOOP;
871 END get_skilled_resources;
872
873 /**
874 * Sorts the Resources in the given list based on their distance to the given
875 * Task from their Home Address.
876 *
877 * If the Task doesnt have a valid Geometry, then the API doesnt do anything. It
878 * merely returns the original list of resources without sorting.
879 * <br>
880 * Each Resource in the given list is picked up iteratively and its Geo-Distance
881 * from the Task is computed using the Geometry of the Task and that of the
882 * Resource Home Address (Location Finder will be invoked if necessary).
883 *
884 * @param p_unsorted_res_tbl List of UnSorted Resources
885 * @param p_task_id Task ID of the Task to be performed
886 * @param p_start Start of the Window to get that Period's Address
887 * @param p_end End of the Window to get that Period's Address
888 * @returns Sorted Resources List (JTF_ASSIGN_PUB.ASSIGNRESOURCES_TBL_TYPE)
889 */
890 FUNCTION sort_resource_by_distance(
891 p_unsorted_res_tbl jtf_assign_pub.assignresources_tbl_type
892 , p_task_id NUMBER
893 , p_start DATE
894 , p_end DATE
895 )
896 RETURN jtf_assign_pub.assignresources_tbl_type IS
897 l_return_status VARCHAR2(1);
898 l_msg_count NUMBER;
899 l_msg_data VARCHAR2(2000);
900 l_sorted_res_tbl jtf_assign_pub.assignresources_tbl_type;
901 l_res_dist_tbl csf_resource_tbl;
902 i PLS_INTEGER;
903 j PLS_INTEGER;
904 l_pref_res_cnt PLS_INTEGER;
905 l_address csf_resource_address_pvt.address_rec_type;
906 l_geometry MDSYS.SDO_GEOMETRY;
907 l_task_lat NUMBER;
908 l_task_lon NUMBER;
909 l_res_lat NUMBER;
910 l_res_lon NUMBER;
911 l_res_position MDSYS.SDO_POINT_TYPE;
912 l_valid_geo VARCHAR2(5);
913 l_distance NUMBER;
914
915 CURSOR c_task_geometry IS
916 SELECT l.geometry
917 FROM jtf_tasks_b t, hz_locations l
918 WHERE t.task_id = p_task_id
919 AND l.location_id = csf_tasks_pub.get_task_location_id(t.task_id, t.address_id, t.location_id);
920
921 CURSOR c_sorted_resources IS
922 SELECT resource_index
923 , distance
924 FROM TABLE(CAST(l_res_dist_tbl AS csf_resource_tbl) )
925 ORDER BY preferred_resource_flag desc, distance, resource_index;
926 BEGIN
927 -- Validate the Geometry of the Task.
928 -- If Task has no or invalid geometry, no need to sort by distance at all
929 OPEN c_task_geometry;
930 FETCH c_task_geometry INTO l_geometry;
931 CLOSE c_task_geometry;
932
933 IF l_geometry IS NULL THEN
934 RETURN p_unsorted_res_tbl;
935 END IF;
936
937 csf_locus_pub.verify_locus(
938 p_api_version => 1.0
939 , x_msg_count => l_msg_count
940 , x_msg_data => l_msg_data
941 , x_return_status => l_return_status
942 , p_locus => l_geometry
943 , x_result => l_valid_geo
944 );
945
946 IF l_valid_geo = 'FALSE' THEN
947 RETURN p_unsorted_res_tbl;
948 END IF;
949
950 IF l_geometry.sdo_elem_info IS NOT NULL
951 AND l_geometry.sdo_ordinates IS NOT NULL
952 THEN
953 l_task_lon := ROUND(l_geometry.sdo_ordinates(1), 8);
954 l_task_lat := ROUND(l_geometry.sdo_ordinates(2), 8);
955 ELSIF l_geometry.sdo_point IS NOT NULL
956 THEN
957 l_task_lon := ROUND(l_geometry.sdo_point.x, 8);
958 l_task_lat := ROUND(l_geometry.sdo_point.y, 8);
959 ELSE
960 l_task_lon := -9999;
961 l_task_lat := -9999;
962 END IF;
963
964 l_res_dist_tbl := csf_resource_tbl();
965 i := p_unsorted_res_tbl.FIRST;
966 l_pref_res_cnt := 1;
967 WHILE i IS NOT NULL LOOP
968 l_res_position := get_location(p_unsorted_res_tbl(i).resource_id, p_unsorted_res_tbl(i).resource_type, p_start);
969
970 IF l_res_position IS NOT NULL AND l_res_position.x <> -9999 AND l_res_position.y <> -9999 THEN
971 l_distance := geo_distance(l_task_lon, l_task_lat, l_res_position.x, l_res_position.y);
972 ELSE
973 l_distance := fnd_api.g_miss_num;
974 END IF;
975
976 l_res_dist_tbl.EXTEND;
977 l_res_dist_tbl(i) :=
978 csf_resource(
979 'N'
980 , i
981 , l_distance
982 , NULL
983 , NULL
984 , NULL
985 , NULL
986 , NULL
987 );
988
989 IF p_unsorted_res_tbl(i).preference_type = 'I' OR p_unsorted_res_tbl(i).preference_type = 'C' THEN
990 l_res_dist_tbl(i).preferred_resource_flag := 'Y';
991 END IF;
992
993 i := p_unsorted_res_tbl.NEXT(i);
994 END LOOP;
995
996 i := 0;
997 FOR v_resource IN c_sorted_resources LOOP
998 i := i + 1;
999 l_sorted_res_tbl(i) := p_unsorted_res_tbl(v_resource.resource_index);
1000 END LOOP;
1001 RETURN l_sorted_res_tbl;
1002 EXCEPTION
1003 WHEN OTHERS THEN
1004 RETURN p_unsorted_res_tbl;
1005 END sort_resource_by_distance;
1006
1007 /**
1008 * Reduce the number of Resources passed to a maximum value as determined by
1009 * the profile "CSR: Maximum number of Resources".
1010 *
1011 * The API doesnt delete resources as such. It first prunes the resource list
1012 * by considering only those Resources who have valid Shift Definitions between
1013 * the given dates and then give the top N resources. Note that it will be
1014 * better if the Resources are already sorted in the order of their preference.
1015 *
1016 * @param p_res_tbl List of Sorted Resources
1017 * @param p_start Start of the Window for Valid Shifts Consideration
1018 * @param p_end End of the Window for Valid Shifts Consideration
1019 * @returns Top N Resources as determined by the MAXNRSELECTEDRES profile.
1020 */
1021 FUNCTION reduce_resource_list(
1022 p_res_tbl IN OUT NOCOPY jtf_assign_pub.assignresources_tbl_type
1023 , p_start DATE
1024 , p_end DATE
1025 )
1026 RETURN jtf_assign_pub.assignresources_tbl_type IS
1027 l_max_resources NUMBER;
1028 l_return_status VARCHAR2(1);
1029 l_msg_count NUMBER;
1030 l_msg_data VARCHAR2(2000);
1031 l_shift_tbl jtf_calendar_pub.shift_tbl_type;
1032 l_out_tbl jtf_assign_pub.assignresources_tbl_type;
1033 i PLS_INTEGER;
1034 j PLS_INTEGER;
1035 cnt PLS_INTEGER;
1036 BEGIN
1037 -- Get the maximum number of allowed Resources
1038 -- Modified to get the profile value from Scheduler API
1039 l_max_resources := csr_scheduler_pub.get_sch_parameter_value('spMaxResources');
1040
1041 -- Validate retrieved maximum value
1042 IF NVL(l_max_resources, 0) <= 0 THEN
1043 l_max_resources := p_res_tbl.COUNT;
1044 END IF;
1045
1046 i := p_res_tbl.FIRST;
1047 j := 0;
1048
1049
1050 WHILE i IS NOT NULL LOOP
1051 cnt:= p_res_tbl.NEXT(i);
1052 WHILE cnt <= p_res_tbl.LAST LOOP
1053 IF p_res_tbl(i).resource_id = p_res_tbl(cnt).resource_id AND
1054 p_res_tbl(i).resource_type = p_res_tbl(cnt).resource_type THEN
1055 p_res_tbl.delete(cnt);
1056 END IF;
1057 cnt:= p_res_tbl.next(cnt);
1058 END LOOP;
1059
1060 jtf_calendar_pub.get_resource_shifts(
1061 p_api_version => 1
1062 , p_init_msg_list => fnd_api.g_true
1063 , x_return_status => l_return_status
1064 , x_msg_count => l_msg_count
1065 , x_msg_data => l_msg_data
1066 , p_resource_id => p_res_tbl(i).resource_id
1067 , p_resource_type => p_res_tbl(i).resource_type
1068 , p_start_date => p_start
1069 , p_end_date => p_end
1070 , x_shift => l_shift_tbl
1071 );
1072
1073
1074 IF l_return_status = fnd_api.g_ret_sts_success OR l_shift_tbl.COUNT > 0 THEN
1075 j := j + 1;
1076 l_out_tbl(j) := p_res_tbl(i);
1077 EXIT WHEN j = l_max_resources;
1078 END IF;
1079 i := p_res_tbl.NEXT(i);
1080 END LOOP;
1081
1082
1083 RETURN l_out_tbl;
1084 EXCEPTION
1085 WHEN OTHERS THEN
1086 RETURN p_res_tbl;
1087 END reduce_resource_list;
1088
1089 /**
1090 * Returns the Error Message that is encountered during Resource Selection
1091 * process based on the Flags passed. Each Flag corresponds to a But in the
1092 * Error Message Table. See INIT_ASSIGN_ERRORS for more information.
1093 *
1094 * @param p_flags Flags used to select Resources for the Task
1095 * @return Name of the Error Message encountered.
1096 */
1097 FUNCTION get_assign_error_msg(p_flags IN NUMBER)
1098 RETURN VARCHAR2 IS
1099 l_msg VARCHAR2(100);
1100 BEGIN
1101 l_msg := 'CSF_R_NO_RES_FOR_TASK';
1102 IF p_flags IS NOT NULL AND g_assign_errors.EXISTS(p_flags) THEN
1103 l_msg := g_assign_errors(p_flags);
1104 END IF;
1105 RETURN l_msg;
1106 END get_assign_error_msg;
1107
1108 /**
1109 * Adds the suggested resources to the end of the given resource table if
1110 * the resource is not already present in the resource table.
1111 * @param p_res_tbl Resource Table and where suggested res will be added
1112 * @param p_suggested_res_id_tbl Suggested Resource ID Table
1113 * @param p_suggested_res_type_tbl Suggested Resource Type Table
1114 */
1115 PROCEDURE add_suggested_resources(
1116 p_res_tbl IN OUT NOCOPY jtf_assign_pub.assignresources_tbl_type
1117 , p_suggested_res_id_tbl IN jtf_number_table
1118 , p_suggested_res_type_tbl IN jtf_varchar2_table_100
1119 , p_start_date IN DATE
1120 , p_end_date IN DATE
1121 ) IS
1122 i PLS_INTEGER;
1123 j PLS_INTEGER;
1124 l_res_found BOOLEAN;
1125 BEGIN
1126 -- Validate the Inputs.
1127 IF p_suggested_res_id_tbl IS NULL
1128 OR p_suggested_res_type_tbl IS NULL
1129 OR p_suggested_res_id_tbl.COUNT <= 0
1130 OR p_suggested_res_type_tbl.COUNT <> p_suggested_res_id_tbl.COUNT
1131 THEN
1132 RETURN;
1133 END IF;
1134
1135 -- Add each Suggested Resource Info to the Resource List if its not already present.
1136 j := p_suggested_res_id_tbl.FIRST;
1137 WHILE j IS NOT NULL LOOP
1138
1139 -- Check whether the Resource list already has the Suggested Resource.
1140 l_res_found := FALSE;
1141 i := p_res_tbl.FIRST;
1142 WHILE i IS NOT NULL LOOP
1143 l_res_found := p_res_tbl(i).resource_id = p_suggested_res_id_tbl(j)
1144 AND p_res_tbl(i).resource_type = p_suggested_res_type_tbl(j);
1145
1146 EXIT WHEN l_res_found;
1147 i := p_res_tbl.NEXT(i);
1148 END LOOP;
1149
1150 IF NOT l_res_found THEN
1151 i := NVL(p_res_tbl.LAST, 0) + 1;
1152
1153 p_res_tbl(i).resource_id := p_suggested_res_id_tbl(j);
1154 p_res_tbl(i).resource_type := p_suggested_res_type_tbl(j);
1155 p_res_tbl(i).start_date := p_start_date;
1156 p_res_tbl(i).end_date := p_end_date;
1157 p_res_tbl(i).terr_id := -1;
1158 p_res_tbl(i).terr_rank := -1;
1159 END IF;
1160
1161 j := p_suggested_res_id_tbl.NEXT(j);
1162 END LOOP;
1163 END add_suggested_resources;
1164
1165
1166 /**
1167 * Gets the Qualified Resources for a Task by calling JTF Assignment Manager
1168 * and also making use of the Required Skills of the Task if Required to reduce
1169 * the Resource List.
1170 *
1171 * <br>
1172 *
1173 * The reason for CSF to maintain its own Assignment Manager rather than
1174 * completely relying on JTF Assignment Manager has two fold reasons.
1175 *
1176 * <br>
1177 *
1178 * TQ is secondary for JTF Assignment Manager API.
1179 * Suppose in Schedule Advise Window, all the Flags are checked... then
1180 * JTF Assignment Manager will give preference to Contracts and IB only.
1181 * Only when both of returns ZERO resources, then JTF will consider TQ.
1182 * But DC expects an intersection of the three results.
1183 * Moreover if both Contracts and IB are checked, then JTF will use
1184 * the profile "JTFAM: Resource Search Order (JTF_AM_PREF_RES_ORDER)" to
1185 * find out which one to return ultimately. If the value CONTRACTS, then
1186 * CONTRACTS - Only Contracts is returned. If None, IB is returned.
1187 * IB - Only IB is returned. If None, Contracts is returned.
1188 * BOTH - Intersection of Contracts and IB Resources are returned.
1189 *
1190 * <br>
1191 *
1192 * JTF doesnt know "ABC of Skills"
1193 * Resources and Skills is completely a Field Service Functionality. A
1194 * Resource can be attached to a Skill with a particular Skill Level.
1195 * So can a Task be tied to a Skill with a particular Skill Level. If
1196 * Skill based Flag is checked, then the Resource needs to have the same
1197 * Skill Set with a Comparable Skill Level as required by the Task.
1198 * Comparable Skill Level !!! - What is that ?
1199 * The profile "CSF: Skill Level Match (CSF_SKILL_LEVEL_MATCH)" is used
1200 * to decide whether the Resource has the Required Skill Level as required
1201 * by the Task.
1202 * EQUAL TO OR SMALLER THAN - Resource should have a Skill Level equal to
1203 * or lesser than that of the Task.
1204 * EQUAL TO - Resource should have a Skill Level equal to
1205 * that of the Task.
1206 * EQUAL TO OR GREATER THAN - Resource should have a Skill Level equal to
1207 * or greater than that of the Task.
1208 * Note that the Task needs to have Skills. Otherwise the Flag wont be used
1209 * at all for getting the Qualified Resources.
1210 *
1211 * <br>
1212 *
1213 * Thus CSF Assignment Manager API will call JTF Assignment Manager separately
1214 * for Contracts / IB and then for Territory. Do an intersection of the Resources
1215 * obtained thru the two calls and pruned by Skill Sets. Note that it gets
1216 * Contracts / IB Resources from JTF in one call and so the user should make use
1217 * the profile JTF_AM_PREF_RES_ORDER to get intersected results.
1218 *
1219 * <br>
1220 *
1221 * CSF Assignment Manager still doesnt pass the parameter P_FILTER_EXCLUDED_RESOURCE
1222 * so that JTF Assignment Manager doesnt return Excluded Resources.
1223 * CSF Assignment Manager still doesnt pass the parameter P_BUSINESS_PROCESS_ID
1224 * so that JTF Assignment Manager returns only those Resources who belong to
1225 * Field Service Business Process when Preferred Resources are entered in Contracts.
1226 *
1227 * @param p_api_version API Version (1.0)
1228 * @param p_init_msg_list Initialize Message List
1229 * @param x_return_status Return Status of the Procedure.
1230 * @param x_msg_count Number of Messages in the Stack.
1231 * @param x_msg_data Stack of Error Messages.
1232 * @param p_task_id Task Idenfifier
1233 * @param p_task_rec Qualified Task Record
1234 * @param p_scheduling_mode Scheduling Mode used. (A, I, W)
1235 * @param p_start Start Date of the Plan Window
1236 * @param p_end End Date of the Plan Window
1237 * @param p_duration Duration of the Task (Used by JTF to find out Available Resources)
1238 * @param p_duration_uom UOM of the above Duration
1239 * @param p_contracts_flag Get Contracts Preferred Resources ('Y'/'N')
1240 * @param p_ib_flag Get IB Preferred Resources ('Y'/'N')
1241 * @param p_territory_flag Get Winning Territory Resources ('Y'/'N')
1242 * @param p_skill_flag Get Skilled Resources ('Y'/'N')
1243 * @param p_calendar_flag Get only Available Resources. Passed to JTF ('Y'/'N')
1244 * @param p_sort_flag Sort the Resources based on their distance from Task ('Y'/'N')
1245 * @param p_suggested_res_id_tbl Suggested Resource ID Table
1246 * @param p_suggested_res_type_tbl Suggested Resource Type Table
1247 * @param x_res_tbl Qualified Resource suitable for Scheduling
1248 */
1249 PROCEDURE get_resources_to_schedule(
1250 p_api_version IN NUMBER
1251 , p_init_msg_list IN VARCHAR2
1252 , x_return_status OUT NOCOPY VARCHAR2
1253 , x_msg_count OUT NOCOPY NUMBER
1254 , x_msg_data OUT NOCOPY VARCHAR2
1255 , p_task_id IN NUMBER
1256 , p_incident_id IN NUMBER
1257 , p_res_qualifier_tbl IN resource_qualifier_tbl_type
1258 , p_scheduling_mode IN VARCHAR2
1259 , p_start IN DATE
1260 , p_end IN DATE
1261 , p_duration IN NUMBER
1262 , p_duration_uom IN VARCHAR2
1263 , p_contracts_flag IN VARCHAR2
1264 , p_ib_flag IN VARCHAR2
1265 , p_territory_flag IN VARCHAR2
1266 , p_skill_flag IN VARCHAR2
1267 , p_calendar_flag IN VARCHAR2
1268 , p_sort_flag IN VARCHAR2
1269 , p_suggested_res_id_tbl IN jtf_number_table
1270 , p_suggested_res_type_tbl IN jtf_varchar2_table_100
1271 , x_res_tbl OUT NOCOPY jtf_assign_pub.assignresources_tbl_type
1272 ) IS
1273 l_api_name CONSTANT VARCHAR2(30) := 'GET_RESOURCES_TO_SCHEDULE';
1274 l_api_version CONSTANT NUMBER := 1.0;
1275
1276 l_end_date DATE;
1277 l_task_has_skill_id NUMBER;
1278 l_business_process_id NUMBER;
1279 l_contracts_flag VARCHAR2(1);
1280 l_ib_flag VARCHAR2(1);
1281 l_territory_flag VARCHAR2(1);
1282 l_skills_flag VARCHAR2(1);
1283 l_has_suggested_res BOOLEAN;
1284 l_continue_search BOOLEAN;
1285 l_cont_ib_res_found BOOLEAN := TRUE;
1286 l_terr_res_found BOOLEAN := TRUE;
1287 l_sr_task_rec jtf_assign_pub.jtf_srv_task_rec_type;
1288
1289 l_contracts_ib_res_tbl jtf_assign_pub.assignresources_tbl_type;
1290 l_territory_res_tbl jtf_assign_pub.assignresources_tbl_type;
1291 l_skilled_res_tbl jtf_assign_pub.assignresources_tbl_type;
1292 l_stic NUMBER;
1293
1294 e_no_res EXCEPTION;
1295
1296 -- Cursor to determine if the Task has any required skills
1297 -- and returns the has_skill_id
1298 CURSOR c_task_skills IS
1299 SELECT has_skill_id
1300 FROM csf_required_skills_b,jtf_tasks_b t
1301 WHERE t.task_id = p_task_id
1302 AND has_skill_type = 'TASK'
1303 AND has_skill_id = nvl(t.parent_task_id, t.task_id)
1304 AND NVL(disabled_flag, 'N') <> 'Y'
1305 AND NVL(t.deleted_flag, 'N') <> 'Y';
1306
1307 --Introduced by lokumar for bug#7340932
1308 CURSOR c_business_process IS
1309 select iv.business_process_id
1310 from jtf_tasks_b t, cs_incidents_all i, cs_incident_types_vl iv
1311 where t.task_id = p_task_id
1312 and t.source_object_id = i.incident_id
1313 and i.incident_type_id = iv.incident_type_id;
1314
1315
1316 BEGIN
1317
1318 debug( fnd_log.level_statement, l_api_name, 'Get Resources To Schedule API Started');
1319
1320 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
1321 RAISE fnd_api.g_exc_unexpected_error;
1322 END IF;
1323
1324 IF fnd_api.to_boolean(p_init_msg_list) THEN
1325 fnd_msg_pub.initialize;
1326 END IF;
1327
1328 x_return_status := fnd_api.g_ret_sts_success;
1329
1330 IF csf_util_pvt.g_timing_activated THEN
1331 csf_util_pvt.add_timer(105, 'Get resources to schedule (init)', 0, NULL);
1332 END IF;
1333
1334
1335 debug( fnd_log.level_statement, l_api_name, ' Task ID:' || p_task_id
1336 || ' Incident Id:' || p_incident_id
1337 || ' Scheduling Mode:' || p_scheduling_mode
1338 || ' Start Date:' || to_char(p_start, 'DD-MON-YYYY HH24:MI:SS')
1339 || ' End Date:' || to_char(p_end , 'DD-MON-YYYY HH24:MI:SS')
1340 || ' Calendar Flag:' || p_calendar_flag
1341 || ' Sort Flag:' || p_sort_flag
1342 || ' Contracts Flag:' || p_contracts_flag
1343 || ' IB Flag:' || p_ib_flag
1344 || ' Terr Flag:' || p_territory_flag
1345 || ' Skills Flag:' || p_skill_flag
1346 );
1347
1348 l_contracts_flag := NVL(p_contracts_flag, 'N');
1349 l_ib_flag := NVL(p_ib_flag, 'N');
1350 l_territory_flag := NVL(p_territory_flag, 'N');
1351 l_skills_flag := NVL(p_skill_flag, 'N');
1352 l_stic := 0;
1353 l_has_suggested_res := p_suggested_res_id_tbl IS NOT NULL AND p_suggested_res_id_tbl.COUNT > 0;
1354
1355 -- Check that at least one flag has been set.
1356 IF l_contracts_flag = 'N'
1357 AND l_ib_flag = 'N'
1358 AND l_territory_flag = 'N'
1359 AND l_skills_flag = 'N'
1360 AND NOT l_has_suggested_res
1361 THEN
1362 debug( fnd_log.level_statement, l_api_name, 'No Resource Selection Criteria is specified');
1363 RAISE e_no_res;
1364 END IF;
1365
1366 -- start with an empty list
1367 x_res_tbl.DELETE;
1368
1369 -- Find out whether the Task has any Required Skills Attached.
1370 IF l_skills_flag = 'Y' THEN
1371 debug( fnd_log.level_statement, l_api_name, 'Resource Selection is based on Skills..finding out the skills');
1372 OPEN c_task_skills;
1373 FETCH c_task_skills INTO l_task_has_skill_id;
1374 CLOSE c_task_skills;
1375
1376 debug( fnd_log.level_statement, l_api_name, 'The Skill ID:' || l_task_has_skill_id);
1377 IF l_task_has_skill_id IS NULL THEN
1378 -- Task has no Skills attached. Turn off Skills Flag.
1379 debug( fnd_log.level_statement, l_api_name, 'Task has no skills attached..turning off Skills qualifiers');
1380 l_skills_flag := 'N';
1381 -- If none of the other Flags are set we have an error situation
1382 IF l_contracts_flag = 'N' AND l_ib_flag = 'N' AND l_territory_flag = 'N' AND NOT l_has_suggested_res THEN
1383 fnd_message.set_name('CSF', 'CSF_NO_TASK_SKILL_RES');
1384 fnd_msg_pub.ADD;
1385 RAISE fnd_api.g_exc_error;
1386 END IF;
1387 END IF;
1388 END IF;
1389
1390 -- Convert the given Task ID or the Qualifier Table to SR Task Record
1391 IF l_contracts_flag = 'Y' OR l_ib_flag = 'Y' OR l_territory_flag = 'Y' THEN
1392 debug( fnd_log.level_statement, l_api_name, 'The input resource qualifier table count:'||p_res_qualifier_tbl.COUNT);
1393 IF p_res_qualifier_tbl.COUNT > 0 THEN
1394 IF csf_util_pvt.g_timing_activated THEN
1395 csf_util_pvt.add_timer(106, 'convert territory qualifiers to record', 0, NULL);
1396 END IF;
1397 l_sr_task_rec := get_qualified_task_rec(p_res_qualifier_tbl);
1398 IF csf_util_pvt.g_timing_activated THEN
1399 csf_util_pvt.add_timer(106, 'convert territory qualifiers to record', 1, NULL);
1400 END IF;
1401 ELSE
1402 IF csf_util_pvt.g_timing_activated THEN
1403 csf_util_pvt.add_timer(107, 'get territory qualifiers for task', 0, NULL);
1404 END IF;
1405 l_sr_task_rec := get_qualified_task_rec(get_res_qualifier_table(p_task_id));
1406 IF csf_util_pvt.g_timing_activated THEN
1407 csf_util_pvt.add_timer(107, 'get territory qualifiers for task', 1, NULL);
1408 END IF;
1409 END IF;
1410 l_sr_task_rec.task_id := p_task_id;
1411 l_sr_task_rec.service_request_id := p_incident_id;
1412 END IF;
1413
1414 debug( fnd_log.level_statement, l_api_name, ' TASK_ID:' || l_sr_task_rec.task_id
1415 || ' SERVICE_REQUEST_ID:' || l_sr_task_rec.SERVICE_REQUEST_ID
1416 || ' PARTY_ID:' || l_sr_task_rec.PARTY_ID
1417 || ' COUNTRY:' || l_sr_task_rec.COUNTRY
1418 || ' PARTY_SITE_ID:' || l_sr_task_rec.PARTY_SITE_ID
1419 || ' CITY:' || l_sr_task_rec.CITY
1420 || ' POSTAL_CODE:' || l_sr_task_rec.POSTAL_CODE
1421 || ' STATE:' || l_sr_task_rec.STATE
1422 || ' AREA_CODE:' || l_sr_task_rec.AREA_CODE
1423 || ' COUNTY:' || l_sr_task_rec.COUNTY
1424 || ' COMP_NAME_RANGE:' || l_sr_task_rec.COMP_NAME_RANGE
1425 || ' PROVINCE:' || l_sr_task_rec.PROVINCE
1426 || ' NUM_OF_EMPLOYEES:' || l_sr_task_rec.NUM_OF_EMPLOYEES
1427 || ' TASK_TYPE_ID:' || l_sr_task_rec.TASK_TYPE_ID
1428 || ' TASK_STATUS_ID:' || l_sr_task_rec.TASK_STATUS_ID
1429 || ' TASK_PRIORITY_ID:' || l_sr_task_rec.TASK_PRIORITY_ID
1430 || ' INCIDENT_TYPE_ID:' || l_sr_task_rec.INCIDENT_TYPE_ID
1431 || ' INCIDENT_SEVERITY_ID:'||l_sr_task_rec.INCIDENT_SEVERITY_ID
1432 || ' INCIDENT_URGENCY_ID:'|| l_sr_task_rec.INCIDENT_URGENCY_ID
1433 || ' PROBLEM_CODE:' || l_sr_task_rec.PROBLEM_CODE
1434 || ' INCIDENT_STATUS_ID:' || l_sr_task_rec.INCIDENT_STATUS_ID
1435 || ' SUPPORT_SITE_ID:' || l_sr_task_rec.SUPPORT_SITE_ID
1436 || ' CUSTOMER_SITE_ID:' || l_sr_task_rec.CUSTOMER_SITE_ID
1437 || ' INVENTORY_ITEM_ID:' || l_sr_task_rec.INVENTORY_ITEM_ID
1438 || ' DAY_OF_WEEK:' || l_sr_task_rec.DAY_OF_WEEK
1439 || ' TIME_OF_DAY:' || l_sr_task_rec.TIME_OF_DAY
1440 || ' ORGANIZATION_ID:' || l_sr_task_rec.ORGANIZATION_ID
1441 );
1442 IF csf_util_pvt.g_timing_activated THEN
1443 csf_util_pvt.add_timer(105, 'get resources to schedule (init)', 1, NULL);
1444 END IF;
1445
1446 l_continue_search := TRUE;
1447
1448
1449 IF csr_scheduler_pub.get_sch_parameter_value('spEnforcePlanWindow') = 'NONE' THEN
1450 l_end_date := p_end + NVL(csr_scheduler_pub.get_sch_parameter_value('spPlanScope'), 5)/5;
1451 debug( fnd_log.level_statement, l_api_name, ' Enforce None; End Date:'|| to_char(l_end_date, 'DD-MON-YYYY HH24:MI:SS'));
1452 ELSE
1453 l_end_date := p_end;
1454 debug( fnd_log.level_statement, l_api_name, ' Enforce is Not None; End Date:'|| to_char(l_end_date, 'DD-MON-YYYY HH24:MI:SS'));
1455 END IF;
1456
1457 debug( fnd_log.level_statement, l_api_name, 'Resource Search Window Start:'||to_char(p_start, 'DD-MON-YYYY HH24:MI:SS')
1458 || 'Resource Search Window End:' ||to_char(l_end_date, 'DD-MON-YYYY HH24:MI:SS')
1459 );
1460
1461 -- Retrieve the Resources defined in Contracts / IB
1462 IF l_contracts_flag = 'Y' OR l_ib_flag = 'Y' THEN
1463 IF csf_util_pvt.g_timing_activated THEN
1464 csf_util_pvt.add_timer(108, 'Get contract/IB resources', 0, NULL);
1465 END IF;
1466
1467 IF l_contracts_flag = 'Y' THEN
1468 -- Added by lokumar for bug#7340932
1469 --business process id is used only when contracts are selected
1470 OPEN c_business_process;
1471 FETCH c_business_process INTO l_business_process_id;
1472 CLOSE c_business_process;
1473
1474 debug( fnd_log.level_statement, l_api_name, 'The Business Process ID used for fetching Contracts Pref Resources:' || l_business_process_id);
1475
1476 l_stic := l_stic + 1;
1477
1478 END IF;
1479
1480 IF l_ib_flag = 'Y' THEN
1481 l_stic := l_stic + 2;
1482 END IF;
1483
1484 jtf_assign_pub.get_assign_resources(
1485 p_api_version => 1.0
1486 , p_init_msg_list => fnd_api.g_false
1487 , p_commit => fnd_api.g_false
1488 , x_return_status => x_return_status
1489 , x_msg_count => x_msg_count
1490 , x_msg_data => x_msg_data
1491 , p_sr_task_rec => l_sr_task_rec
1492 , p_contracts_preferred_engineer => l_contracts_flag
1493 , p_ib_preferred_engineer => l_ib_flag
1494 , p_territory_flag => 'N'
1495 , p_effort_duration => p_duration
1496 , p_effort_uom => p_duration_uom
1497 , p_start_date => p_start
1498 , p_end_date => l_end_date
1499 , p_auto_select_flag => 'N'
1500 , p_calendar_flag => NVL(p_calendar_flag, 'N')
1501 , p_calendar_check => 'N'
1502 , p_calling_doc_id => l_sr_task_rec.service_request_id
1503 , p_calling_doc_type => g_assign_doc_type
1504 , p_business_process_id => l_business_process_id
1505 , x_assign_resources_tbl => l_contracts_ib_res_tbl
1506 );
1507
1508 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
1509 RAISE fnd_api.g_exc_unexpected_error;
1510 END IF;
1511
1512 debug( fnd_log.level_statement, l_api_name, 'The Number of Resources found using Contracts/IB:' || l_contracts_ib_res_tbl.COUNT);
1513
1514 IF l_contracts_ib_res_tbl.COUNT = 0 THEN
1515 l_cont_ib_res_found := FALSE;
1516 END IF;
1517
1518 FOR i in 1..l_contracts_ib_res_tbl.COUNT
1519 LOOP
1520 debug( fnd_log.level_statement, l_api_name, 'Obtained C/IB resource:' || l_contracts_ib_res_tbl(i).resource_id || l_contracts_ib_res_tbl(i).resource_type );
1521 debug( fnd_log.level_statement, l_api_name, 'Resource Start:'||to_char(l_contracts_ib_res_tbl(i).start_date, 'DD-MON-YYYY HH24:MI:SS')
1522 || 'Resource End:' ||to_char(l_contracts_ib_res_tbl(i).end_date, 'DD-MON-YYYY HH24:MI:SS')
1523 );
1524 END LOOP;
1525
1526 -- Since JTF Assignment Manager doesnt return End Windows properly,
1527 -- setting them to the given Plan Window.
1528 set_generic_planwindow(l_contracts_ib_res_tbl, p_start, l_end_date);
1529
1530 IF csf_util_pvt.g_timing_activated THEN
1531 csf_util_pvt.add_timer(108, 'get contract/IB resources', 1, NULL);
1532 END IF;
1533 END IF;
1534
1535 -- Retrieve Resources from Territory
1536 IF l_territory_flag = 'Y' AND l_continue_search THEN
1537 IF csf_util_pvt.g_timing_activated THEN
1538 csf_util_pvt.add_timer(109, 'get territory resources', 0, NULL);
1539 END IF;
1540
1541 l_stic := l_stic + 4;
1542
1543
1544 jtf_assign_pub.get_assign_resources(
1545 p_api_version => 1.0
1546 , p_init_msg_list => fnd_api.g_false
1547 , p_commit => fnd_api.g_false
1548 , x_return_status => x_return_status
1549 , x_msg_count => x_msg_count
1550 , x_msg_data => x_msg_data
1551 , p_sr_task_rec => l_sr_task_rec
1552 , p_contracts_preferred_engineer => 'N'
1553 , p_ib_preferred_engineer => 'N'
1554 , p_territory_flag => 'Y'
1555 , p_effort_duration => p_duration
1556 , p_effort_uom => p_duration_uom
1557 , p_start_date => p_start
1558 , p_end_date => l_end_date
1559 , p_auto_select_flag => 'N'
1560 , p_calendar_flag => NVL(p_calendar_flag, 'N')
1561 , p_calendar_check => 'N'
1562 , p_calling_doc_id => l_sr_task_rec.service_request_id
1563 , p_calling_doc_type => g_assign_doc_type
1564 , x_assign_resources_tbl => l_territory_res_tbl
1565 );
1566
1567
1568 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
1569 RAISE fnd_api.g_exc_unexpected_error;
1570 END IF;
1571
1572 debug( fnd_log.level_statement, l_api_name, 'The Number of Resources found using Territories:' || l_territory_res_tbl.COUNT);
1573
1574 IF l_territory_res_tbl.COUNT = 0 THEN
1575 l_terr_res_found := FALSE;
1576 END IF;
1577
1578 FOR i in 1..l_territory_res_tbl.COUNT
1579 LOOP
1580 debug( fnd_log.level_statement, l_api_name, 'Obtained Terr resource:' || l_territory_res_tbl(i).resource_id || l_territory_res_tbl(i).resource_type );
1581 debug( fnd_log.level_statement, l_api_name, 'Resource Start:'||to_char(l_territory_res_tbl(i).start_date, 'DD-MON-YYYY HH24:MI:SS')
1582 || 'Resource End:' ||to_char(l_territory_res_tbl(i).end_date, 'DD-MON-YYYY HH24:MI:SS')
1583 );
1584 END LOOP;
1585
1586
1587 -- Since JTF Assignment Manager doesnt return End Windows properly,
1588 -- setting them to the given Plan Window.
1589 set_generic_planwindow(l_territory_res_tbl, p_start, l_end_date);
1590
1591 IF csf_util_pvt.g_timing_activated THEN
1592 csf_util_pvt.add_timer(109, 'get territory resources', 1, NULL);
1593 END IF;
1594 END IF;
1595
1596 -- This is done for two cases: a) when res found in TQ/IB/Cont or b) when TQ/IB/Cont
1597 -- was not used as the resource selection criteria
1598 IF NOT l_terr_res_found AND NOT l_cont_ib_res_found
1599 THEN
1600 debug( fnd_log.level_statement, l_api_name, 'Search will not be continued');
1601 l_continue_search := FALSE;
1602 END IF;
1603
1604 -- Retrieve the Skilled Resources for the Task
1605 IF l_skills_flag = 'Y' AND l_continue_search THEN
1606 debug( fnd_log.level_statement, l_api_name, 'Retrieving Skilled Resources for the Task');
1607 l_stic := l_stic + 8;
1608 get_skilled_resources(
1609 p_task_id => l_task_has_skill_id
1610 , p_start => p_start
1611 , p_end => l_end_date
1612 , x_skilled_res_tbl => l_skilled_res_tbl
1613 );
1614 debug( fnd_log.level_statement, l_api_name, 'Retrieving Skilled Resources for the Task');
1615 IF l_skilled_res_tbl.COUNT = 0 THEN
1616 debug( fnd_log.level_statement, l_api_name, 'No Resources Found using Skills Qualifiers');
1617 l_continue_search := FALSE;
1618 END IF;
1619
1620 FOR i in 1..l_skilled_res_tbl.COUNT
1621 LOOP
1622 debug( fnd_log.level_statement, l_api_name, 'Obtained skills resource:' || l_skilled_res_tbl(i).resource_id || l_skilled_res_tbl(i).resource_type );
1623 END LOOP;
1624
1625 END IF;
1626
1627 debug( fnd_log.level_statement, l_api_name, 'l_stic value:' || l_stic);
1628
1629 -- Intersect the results obtained from the three Qualifiers.
1630 IF l_continue_search THEN
1631 IF l_stic > 0 AND l_stic <= 3 THEN --> Only Contracts / IB was chosen
1632 debug( fnd_log.level_statement, l_api_name, 'Only Contracts / IB was chosen');
1633 x_res_tbl := l_contracts_ib_res_tbl;
1634 ELSIF l_stic = 4 THEN --> Only Territory was chosen
1635 debug( fnd_log.level_statement, l_api_name, 'Only Territory was chosen');
1636 x_res_tbl := l_territory_res_tbl;
1637 ELSIF l_stic = 8 THEN --> Only Skills were chosen
1638 debug( fnd_log.level_statement, l_api_name, 'Only Skills were chosen');
1639 x_res_tbl := l_skilled_res_tbl;
1640 ELSIF l_stic <= 7 THEN --> Both Contracts/IB and Terr were chosen
1641 debug( fnd_log.level_statement, l_api_name, 'Both Contracts/IB and Terr were chosen');
1642 x_res_tbl := union_results(l_contracts_ib_res_tbl, l_territory_res_tbl);
1643 ELSIF l_stic <= 11 THEN --> Both Contracts/IB and Skills where chosen
1644 debug( fnd_log.level_statement, l_api_name, 'Both Contracts/IB and Skills where chosen');
1645 x_res_tbl := intersect_results(l_contracts_ib_res_tbl, l_skilled_res_tbl, p_start, l_end_date);
1646 ELSIF l_stic <= 12 THEN --> Both Terr and SKills were chosen
1647 debug( fnd_log.level_statement, l_api_name, 'Both Terr and SKills were chosen');
1648 x_res_tbl := intersect_results(l_territory_res_tbl, l_skilled_res_tbl, p_start, l_end_date);
1649 ELSIF l_stic <= 15 THEN --> All the Flags were chosen
1650 debug( fnd_log.level_statement, l_api_name, 'All the Flags were chosen');
1651 x_res_tbl := union_results(l_contracts_ib_res_tbl, l_territory_res_tbl);
1652 x_res_tbl := intersect_results(x_res_tbl, l_skilled_res_tbl, p_start, l_end_date);
1653 END IF;
1654 END IF;
1655
1656 -- Add the suggested resources at the end (if the resource is not already there)
1657 IF l_has_suggested_res THEN
1658 debug( fnd_log.level_statement, l_api_name, 'Adding the Suggested Resource');
1659 add_suggested_resources(
1660 p_res_tbl => x_res_tbl
1661 , p_suggested_res_id_tbl => p_suggested_res_id_tbl
1662 , p_suggested_res_type_tbl => p_suggested_res_type_tbl
1663 , p_start_date => p_start
1664 , p_end_date => l_end_date
1665 );
1666 END IF;
1667
1668 -- make sure there were results
1669 IF x_res_tbl.COUNT = 0 THEN
1670 debug( fnd_log.level_statement, l_api_name, 'No Resources Found; Raising Exception!');
1671 RAISE e_no_res;
1672 END IF;
1673
1674 IF p_scheduling_mode <> 'A' THEN
1675 debug( fnd_log.level_statement, l_api_name, 'Non-Assisted Mode; Sorting by distance');
1676 -- Sort the Resources by their distance to the Task.
1677 IF csf_util_pvt.g_timing_activated THEN
1678 csf_util_pvt.add_timer(113, 'sort resources list', 0, NULL);
1679 END IF;
1680
1681 IF x_res_tbl.COUNT > 1 AND NVL(p_sort_flag, 'Y') = 'Y' THEN
1682 x_res_tbl := sort_resource_by_distance(x_res_tbl, p_task_id, p_start, l_end_date);
1683 END IF;
1684
1685 FOR i in 1..x_res_tbl.COUNT
1686 LOOP
1687 debug( fnd_log.level_statement, l_api_name, 'After Sorting resource:' || x_res_tbl(i).resource_id || x_res_tbl(i).resource_type );
1688 END LOOP;
1689
1690 IF csf_util_pvt.g_timing_activated THEN
1691 csf_util_pvt.add_timer(113, 'sort resources list', 1, NULL);
1692 END IF;
1693
1694 END IF;
1695
1696
1697 -- The number of Resources are restricted from the Sorted List.
1698 IF csf_util_pvt.g_timing_activated THEN
1699 csf_util_pvt.add_timer(114, 'reduce sorted resources list', 0, NULL);
1700 END IF;
1701
1702 x_res_tbl := reduce_resource_list(x_res_tbl, p_start, l_end_date);
1703
1704 FOR i in 1..x_res_tbl.COUNT
1705 LOOP
1706 debug( fnd_log.level_statement, l_api_name, 'After Calendar Check:' || x_res_tbl(i).resource_id || x_res_tbl(i).resource_type );
1707 debug( fnd_log.level_statement, l_api_name, 'x_Resource Start:'||to_char(x_res_tbl(i).start_date, 'DD-MON-YYYY HH24:MI:SS')
1708 || 'x_Resource End:' ||to_char(x_res_tbl(i).end_date, 'DD-MON-YYYY HH24:MI:SS')
1709 );
1710 END LOOP;
1711
1712 IF csf_util_pvt.g_timing_activated THEN
1713 csf_util_pvt.add_timer(114, 'reduce sorted resources list', 1, NULL);
1714 END IF;
1715 EXCEPTION
1716 WHEN e_no_res THEN
1717 x_return_status := fnd_api.g_ret_sts_error;
1718 fnd_message.set_name('CSF', get_assign_error_msg(l_stic));
1719 fnd_msg_pub.ADD;
1720 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1721 WHEN fnd_api.g_exc_error THEN
1722 x_return_status := fnd_api.g_ret_sts_error;
1723 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1724 WHEN fnd_api.g_exc_unexpected_error THEN
1725 x_return_status := fnd_api.g_ret_sts_unexp_error;
1726 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1727 WHEN OTHERS THEN
1728 x_return_status := fnd_api.g_ret_sts_unexp_error;
1729 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1730 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1731 END IF;
1732 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1733 END get_resources_to_schedule;
1734
1735 /**
1736 * Gets the Qualified Resources for a Task in a format understood by Request Model.
1737 * <br>
1738 * In turn calls the GET_RESOURCES_TO_SCHEDULE which gets the Resources in JTF
1739 * Assignment Manager Format.
1740 */
1741 PROCEDURE get_resources_to_schedule(
1742 p_api_version IN NUMBER
1743 , p_init_msg_list IN VARCHAR2 DEFAULT NULL
1744 , x_return_status OUT NOCOPY VARCHAR2
1745 , x_msg_count OUT NOCOPY NUMBER
1746 , x_msg_data OUT NOCOPY VARCHAR2
1747 , p_task_id IN NUMBER
1748 , p_incident_id IN NUMBER
1749 , p_res_qualifier_tbl IN resource_qualifier_tbl_type
1750 , p_scheduling_mode IN VARCHAR2
1751 , p_start IN DATE
1752 , p_end IN DATE
1753 , p_duration IN NUMBER DEFAULT NULL
1754 , p_duration_uom IN VARCHAR2 DEFAULT NULL
1755 , p_contracts_flag IN VARCHAR2 DEFAULT NULL
1756 , p_ib_flag IN VARCHAR2 DEFAULT NULL
1757 , p_territory_flag IN VARCHAR2 DEFAULT NULL
1758 , p_skill_flag IN VARCHAR2 DEFAULT NULL
1759 , p_calendar_flag IN VARCHAR2 DEFAULT NULL
1760 , p_sort_flag IN VARCHAR2 DEFAULT NULL
1761 , p_suggested_res_id_tbl IN jtf_number_table DEFAULT NULL
1762 , p_suggested_res_type_tbl IN jtf_varchar2_table_100 DEFAULT NULL
1763 , x_res_tbl IN OUT NOCOPY csf_requests_pvt.resource_tbl_type
1764 ) IS
1765 l_api_name CONSTANT VARCHAR2(30) := 'GET_RESOURCES_TO_SCHEDULE(2)';
1766 l_api_version CONSTANT NUMBER := 1.0;
1767
1768 l_assign_resource_tbl jtf_assign_pub.assignresources_tbl_type;
1769 j PLS_INTEGER;
1770 k PLS_INTEGER;
1771 BEGIN
1772 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
1773 RAISE fnd_api.g_exc_unexpected_error;
1774 END IF;
1775
1776 IF fnd_api.to_boolean(p_init_msg_list) THEN
1777 fnd_msg_pub.initialize;
1778 END IF;
1779
1780 x_return_status := fnd_api.g_ret_sts_success;
1781
1782
1783
1784
1785 get_resources_to_schedule(
1786 p_api_version => 1
1787 , p_init_msg_list => fnd_api.g_false
1788 , x_return_status => x_return_status
1789 , x_msg_count => x_msg_count
1790 , x_msg_data => x_msg_data
1791 , p_task_id => p_task_id
1792 , p_incident_id => p_incident_id
1793 , p_res_qualifier_tbl => p_res_qualifier_tbl
1794 , p_scheduling_mode => p_scheduling_mode
1795 , p_start => p_start
1796 , p_end => p_end
1797 , p_duration => p_duration
1798 , p_duration_uom => p_duration_uom
1799 , p_contracts_flag => p_contracts_flag
1800 , p_ib_flag => p_ib_flag
1801 , p_territory_flag => p_territory_flag
1802 , p_skill_flag => p_skill_flag
1803 , p_calendar_flag => p_calendar_flag
1804 , p_sort_flag => p_sort_flag
1805 , p_suggested_res_id_tbl => p_suggested_res_id_tbl
1806 , p_suggested_res_type_tbl => p_suggested_res_type_tbl
1807 , x_res_tbl => l_assign_resource_tbl
1808 );
1809
1810
1811 IF x_res_tbl IS NULL THEN
1812 x_res_tbl := csf_requests_pvt.resource_tbl_type();
1813 END IF;
1814
1815 --start with an empty table so that previous value are deleted
1816 x_res_tbl.delete;
1817
1818 -- if qualified resoucres are found, add them to the output list
1819 IF x_return_status = fnd_api.g_ret_sts_success AND l_assign_resource_tbl.COUNT > 0 THEN
1820
1821 j := l_assign_resource_tbl.FIRST;
1822 WHILE j IS NOT NULL LOOP
1823 x_res_tbl.EXTEND;
1824 k := x_res_tbl.LAST;
1825 x_res_tbl(k).resource_id := l_assign_resource_tbl(j).resource_id;
1826 x_res_tbl(k).resource_type := l_assign_resource_tbl(j).resource_type;
1827 x_res_tbl(k).planwin_start := l_assign_resource_tbl(j).start_date;
1828 x_res_tbl(k).planwin_end := l_assign_resource_tbl(j).end_date;
1829 x_res_tbl(k).territory_id := l_assign_resource_tbl(j).terr_id;
1830
1831 IF l_assign_resource_tbl(j).terr_rank = jtf_assign_pub.am_miss_num THEN
1832 x_res_tbl(k).territory_rank := NULL;
1833 ELSE
1834 x_res_tbl(k).territory_rank := l_assign_resource_tbl(j).terr_rank;
1835 END IF;
1836 IF l_assign_resource_tbl(j).preference_type = 'I' OR l_assign_resource_tbl(j).preference_type = 'C' THEN
1837 x_res_tbl(k).preferred_resources_flag := 'Y';
1838 ELSE
1839 x_res_tbl(k).preferred_resources_flag := 'N';
1840 END IF;
1841
1842 IF l_assign_resource_tbl(j).preference_type = 'C' THEN
1843 x_res_tbl(k).resource_source := 'CNT';
1844 ELSIF l_assign_resource_tbl(j).preference_type = 'I' THEN
1845 x_res_tbl(k).resource_source := 'IB';
1846 ELSIF l_assign_resource_tbl(j).preference_type = 'SK' THEN
1847 x_res_tbl(k).resource_source := 'SK';
1848 ELSIF l_assign_resource_tbl(j).terr_id IS NOT NULL AND l_assign_resource_tbl(j).terr_id <> -1 THEN
1849 x_res_tbl(k).resource_source := 'TER';
1850 ELSE
1851 x_res_tbl(k).resource_source := 'RS';
1852 END IF;
1853
1854 x_res_tbl(k).skill_level := l_assign_resource_tbl(j).skill_level;
1855
1856 j := l_assign_resource_tbl.NEXT(j);
1857 END LOOP;
1858 END IF;
1859
1860
1861 EXCEPTION
1862 WHEN fnd_api.g_exc_error THEN
1863 x_return_status := fnd_api.g_ret_sts_error;
1864 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1865 WHEN fnd_api.g_exc_unexpected_error THEN
1866 x_return_status := fnd_api.g_ret_sts_unexp_error;
1867 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1868 WHEN OTHERS THEN
1869 x_return_status := fnd_api.g_ret_sts_unexp_error;
1870 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1871 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1872 END IF;
1873 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1874 END get_resources_to_schedule;
1875
1876 PROCEDURE get_resources_to_schedule(
1877 p_api_version IN NUMBER
1878 , p_init_msg_list IN VARCHAR2 DEFAULT NULL
1879 , x_return_status OUT NOCOPY VARCHAR2
1880 , x_msg_count OUT NOCOPY NUMBER
1881 , x_msg_data OUT NOCOPY VARCHAR2
1882 , p_task_id IN NUMBER
1883 , p_contracts_flag IN VARCHAR2 DEFAULT NULL
1884 , p_ib_flag IN VARCHAR2 DEFAULT NULL
1885 , p_territory_flag IN VARCHAR2 DEFAULT NULL
1886 , p_skill_flag IN VARCHAR2 DEFAULT NULL
1887 , x_res_tbl OUT NOCOPY csf_resource_tbl
1888 ) IS
1889 l_api_name CONSTANT VARCHAR2(30) := 'GET_RESOURCES_TO_SCHEDULE(3)';
1890 l_api_version CONSTANT NUMBER := 1.0;
1891
1892 l_res_qualifiers resource_qualifier_tbl_type;
1893 l_assign_resource_tbl jtf_assign_pub.assignresources_tbl_type;
1894 j PLS_INTEGER;
1895 k PLS_INTEGER;
1896 --
1897 CURSOR c_task_info IS
1898 SELECT source_object_id
1899 , planned_start_date
1900 , planned_end_date
1901 , planned_effort
1902 , planned_effort_uom
1903 FROM jtf_tasks_b t
1904 WHERE t.task_id = p_task_id;
1905 l_task_info c_task_info%ROWTYPE;
1906 --
1907 CURSOR c_resource_info IS
1908 SELECT /*+ CARDINALITY(tr, 1) */
1909 r.resource_name
1910 , t.name terr_name
1911 , tr.resource_index
1912 FROM jtf_rs_all_resources_vl r
1913 , jtf_terr_all t
1914 , TABLE( CAST( x_res_tbl AS csf_resource_tbl ) ) tr
1915 WHERE r.resource_id = tr.resource_id
1916 AND r.resource_type = tr.resource_type
1917 AND t.terr_id (+) = tr.terr_id;
1918 BEGIN
1919 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
1920 RAISE fnd_api.g_exc_unexpected_error;
1921 END IF;
1922
1923 IF fnd_api.to_boolean(p_init_msg_list) THEN
1924 fnd_msg_pub.initialize;
1925 END IF;
1926
1927 x_return_status := fnd_api.g_ret_sts_success;
1928
1929 OPEN c_task_info;
1930 FETCH c_task_info INTO l_task_info;
1931 CLOSE c_task_info;
1932
1933 get_resources_to_schedule(
1934 p_api_version => 1
1935 , p_init_msg_list => fnd_api.g_false
1936 , x_return_status => x_return_status
1937 , x_msg_count => x_msg_count
1938 , x_msg_data => x_msg_data
1939 , p_task_id => p_task_id
1940 , p_incident_id => l_task_info.source_object_id
1941 , p_res_qualifier_tbl => l_res_qualifiers
1942 , p_scheduling_mode => 'X'
1943 , p_start => l_task_info.planned_start_date
1944 , p_end => l_task_info.planned_end_date
1945 , p_duration => l_task_info.planned_effort
1946 , p_duration_uom => l_task_info.planned_effort_uom
1947 , p_contracts_flag => p_contracts_flag
1948 , p_ib_flag => p_ib_flag
1949 , p_territory_flag => p_territory_flag
1950 , p_skill_flag => p_skill_flag
1951 , x_res_tbl => l_assign_resource_tbl
1952 );
1953
1954
1955
1956
1957 IF x_res_tbl IS NULL THEN
1958 x_res_tbl := csf_resource_tbl();
1959 END IF;
1960
1961 --start with an empty table so that previous value are deleted
1962 x_res_tbl.delete;
1963
1964 -- if qualified resoucres are found, add them to the output list
1965 IF x_return_status = fnd_api.g_ret_sts_success AND l_assign_resource_tbl.COUNT > 0 THEN
1966
1967 j := l_assign_resource_tbl.FIRST;
1968 WHILE j IS NOT NULL LOOP
1969 x_res_tbl.EXTEND;
1970 k := x_res_tbl.LAST;
1971 x_res_tbl(k) :=
1972 csf_resource(
1973 k
1974 , l_assign_resource_tbl(j).resource_id
1975 , l_assign_resource_tbl(j).resource_type
1976 , NULL
1977 , l_assign_resource_tbl(j).resource_source
1978 , l_assign_resource_tbl(j).terr_id
1979 , l_assign_resource_tbl(j).terr_name
1980 , l_assign_resource_tbl(j).terr_rank
1981 , 'N'
1982 , NULL
1983 , l_assign_resource_tbl(j).start_date
1984 , l_assign_resource_tbl(j).end_date
1985 );
1986
1987 IF l_assign_resource_tbl(j).terr_rank = jtf_assign_pub.am_miss_num THEN
1988 x_res_tbl(k).terr_rank := NULL;
1989 END IF;
1990
1991 IF l_assign_resource_tbl(j).preference_type = 'I' THEN
1992 x_res_tbl(k).resource_source := 'IB';
1993 x_res_tbl(k).preferred_resource_flag := 'Y';
1994 ELSIF l_assign_resource_tbl(j).preference_type = 'C' THEN
1995 x_res_tbl(k).resource_source := 'CNT';
1996 x_res_tbl(k).preferred_resource_flag := 'Y';
1997 ELSIF l_assign_resource_tbl(j).preference_type = 'SK' THEN
1998 x_res_tbl(k).resource_source := 'SK';
1999 ELSIF l_assign_resource_tbl(j).terr_id IS NOT NULL AND l_assign_resource_tbl(j).terr_id <> -1 THEN
2000 x_res_tbl(k).resource_source := 'TER';
2001 ELSE
2002 x_res_tbl(k).resource_source := 'RS';
2003 END IF;
2004
2005 j := l_assign_resource_tbl.NEXT(j);
2006 END LOOP;
2007
2008 FOR v_resource_info IN c_resource_info LOOP
2009 x_res_tbl(v_resource_info.resource_index).resource_name := v_resource_info.resource_name;
2010 x_res_tbl(v_resource_info.resource_index).terr_name := v_resource_info.terr_name;
2011 END LOOP;
2012 END IF;
2013 EXCEPTION
2014 WHEN fnd_api.g_exc_error THEN
2015 x_return_status := fnd_api.g_ret_sts_error;
2016 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2017 WHEN fnd_api.g_exc_unexpected_error THEN
2018 x_return_status := fnd_api.g_ret_sts_unexp_error;
2019 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2020 WHEN OTHERS THEN
2021 x_return_status := fnd_api.g_ret_sts_unexp_error;
2022 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
2023 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
2024 END IF;
2025 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2026 END get_resources_to_schedule;
2027
2028 FUNCTION get_resources_to_schedule_pvt(
2029 p_task_id IN NUMBER
2030 , p_contracts_flag IN VARCHAR2 DEFAULT NULL
2031 , p_ib_flag IN VARCHAR2 DEFAULT NULL
2032 , p_territory_flag IN VARCHAR2 DEFAULT NULL
2033 , p_skill_flag IN VARCHAR2 DEFAULT NULL
2034 ) RETURN csf_resource_tbl IS
2035 PRAGMA autonomous_transaction;
2036 --
2037 l_return_status VARCHAR2(1);
2038 l_msg_data VARCHAR2(2000);
2039 l_msg_count NUMBER;
2040 l_res_tbl csf_resource_tbl;
2041 BEGIN
2042 get_resources_to_schedule(
2043 p_api_version => 1.0
2044 , p_init_msg_list => fnd_api.g_true
2045 , x_return_status => l_return_status
2046 , x_msg_count => l_msg_count
2047 , x_msg_data => l_msg_data
2048 , p_task_id => p_task_id
2049 , p_contracts_flag => p_contracts_flag
2050 , p_ib_flag => p_ib_flag
2051 , p_territory_flag => p_territory_flag
2052 , p_skill_flag => p_skill_flag
2053 , x_res_tbl => l_res_tbl
2054 );
2055
2056 ROLLBACK;
2057
2058 RETURN l_res_tbl;
2059 END get_resources_to_schedule_pvt;
2060
2061 FUNCTION get_resources_to_schedule(
2062 p_task_id IN NUMBER
2063 , p_contracts_flag IN VARCHAR2 DEFAULT NULL
2064 , p_ib_flag IN VARCHAR2 DEFAULT NULL
2065 , p_territory_flag IN VARCHAR2 DEFAULT NULL
2066 , p_skill_flag IN VARCHAR2 DEFAULT NULL
2067 )
2068 RETURN csf_resource_tbl IS
2069 l_res_tbl csf_resource_tbl;
2070 BEGIN
2071 l_res_tbl :=
2072 get_resources_to_schedule_pvt(
2073 p_task_id => p_task_id
2074 , p_contracts_flag => csr_scheduler_pub.get_sch_parameter_value('spPickContractResources')
2075 , p_ib_flag => csr_scheduler_pub.get_sch_parameter_value('spPickIbResources')
2076 , p_territory_flag => csr_scheduler_pub.get_sch_parameter_value('spPickTerritoryResources')
2077 , p_skill_flag => csr_scheduler_pub.get_sch_parameter_value('spPickSkilledResources')
2078 );
2079
2080 RETURN l_res_tbl;
2081 END get_resources_to_schedule;
2082
2083
2084 /**
2085 * Returns the Resource Type Code corresponding to a Resource Category.
2086 * <br>
2087 * In sync with the code done in JTF_RS_ALL_RESOURCES_VL
2088 *
2089 * @param p_category Resource Category
2090 * @returns Resource Type Code (VARCHAR2).
2091 */
2092 FUNCTION rs_category_type(p_category VARCHAR2)
2093 RETURN VARCHAR2 IS
2094 BEGIN
2095 IF p_category = 'EMPLOYEE' THEN
2096 RETURN 'RS_EMPLOYEE';
2097 ELSIF p_category = 'PARTNER' THEN
2098 RETURN 'RS_PARTNER';
2099 ELSIF p_category = 'SUPPLIER_CONTACT' THEN
2100 RETURN 'RS_SUPPLIER';
2101 ELSIF p_category = 'PARTY' THEN
2102 RETURN 'RS_PARTY';
2103 ELSIF p_category = 'OTHER' THEN
2104 RETURN 'RS_OTHER';
2105 ELSE
2106 RETURN NULL;
2107 END IF;
2108 END rs_category_type;
2109
2110 /**
2111 * Returns the ID of the Resource tied to the given User (FND User).
2112 * <br>
2113 * If no User is passed in, then it will take the User who has logged in
2114 * (FND_GLOBAL.USER_ID).
2115 *
2116 * @param p_user_id Identifier to the User desired (Optional)
2117 * @returns Resource ID (NUMBER)
2118 */
2119 FUNCTION resource_id(p_user_id NUMBER DEFAULT NULL)
2120 RETURN NUMBER IS
2121 CURSOR c_resource IS
2122 SELECT resource_id
2123 FROM jtf_rs_resource_extns
2124 WHERE user_id = NVL(p_user_id, fnd_global.user_id);
2125 l_id NUMBER := NULL;
2126 BEGIN
2127 OPEN c_resource;
2128 FETCH c_resource INTO l_id;
2129 CLOSE c_resource;
2130
2131 RETURN l_id;
2132 END resource_id;
2133
2134 /**
2135 * Returns the Resource Type of the Resource tied to the given user. (FND User)
2136 * <br>
2137 * If no User is passed in, then it will take the User who has logged in
2138 * (FND_GLOBAL.USER_ID).
2139 *
2140 * @param p_user_id Identifier to the User desired (Optional)
2141 * @returns Resource Type (VARCHAR2)
2142 */
2143 FUNCTION resource_type(p_user_id NUMBER DEFAULT NULL)
2144 RETURN VARCHAR2 IS
2145 CURSOR c_resource_type IS
2146 SELECT category
2147 FROM jtf_rs_resource_extns
2148 WHERE user_id = NVL(p_user_id, fnd_global.user_id);
2149 l_type jtf_rs_resource_extns.category%TYPE;
2150 BEGIN
2151 OPEN c_resource_type;
2152 FETCH c_resource_type INTO l_type;
2153 CLOSE c_resource_type;
2154
2155 RETURN rs_category_type(l_type);
2156 END resource_type;
2157
2158 FUNCTION get_resource_from_cache(
2159 p_res_id NUMBER
2160 , p_res_type VARCHAR2
2161 , p_get_address BOOLEAN DEFAULT FALSE
2162 , p_date DATE DEFAULT NULL
2163 )
2164 RETURN resource_cache_rec_type IS
2165
2166 l_return_status VARCHAR2(1);
2167 l_msg_data VARCHAR2(2000);
2168 l_msg_count NUMBER;
2169 l_found BOOLEAN;
2170 l_res_cache_info resource_cache_rec_type;
2171
2172 CURSOR c_normal_resource IS
2173 SELECT resource_id
2174 , p_res_type resource_type
2175 , resource_name
2176 , resource_number
2177 FROM jtf_rs_resource_extns_vl
2178 WHERE resource_id = p_res_id;
2179
2180 CURSOR c_group_resource IS
2181 SELECT group_id resource_id
2182 , 'RS_GROUP' resource_type
2183 , group_name resource_name
2184 , group_number resource_number
2185 FROM jtf_rs_groups_vl
2186 WHERE group_id = p_res_id;
2187
2188 CURSOR c_team_resource IS
2189 SELECT team_id resource_id
2190 , 'RS_TEAM' resource_type
2191 , team_name resource_name
2192 , team_number resource_number
2193 FROM jtf_rs_teams_vl
2194 WHERE team_id = p_res_id;
2195
2196 l_resource c_normal_resource%ROWTYPE;
2197 BEGIN
2198 IF p_res_id IS NULL OR p_res_type IS NULL THEN
2199 RETURN NULL;
2200 END IF;
2201
2202 -- Check whether the Resource exists in the Cache
2203 l_found := FALSE;
2204 IF g_res_info_cache.EXISTS(p_res_id) THEN
2205 l_res_cache_info := g_res_info_cache(p_res_id);
2206 l_found := l_res_cache_info.resource_type = p_res_type;
2207 END IF;
2208
2209 IF NOT l_found THEN
2210 IF p_res_type = 'RS_GROUP' THEN
2211 OPEN c_group_resource;
2212 FETCH c_group_resource INTO l_resource;
2213 CLOSE c_group_resource;
2214 ELSIF p_res_type = 'RS_TEAM' THEN
2215 OPEN c_team_resource;
2216 FETCH c_team_resource INTO l_resource;
2217 CLOSE c_team_resource;
2218 ELSE
2219 OPEN c_normal_resource;
2220 FETCH c_normal_resource INTO l_resource;
2221 CLOSE c_normal_resource;
2222 END IF;
2223
2224 -- Populate the Resource Cache Record
2225 l_res_cache_info.resource_id := l_resource.resource_id;
2226 l_res_cache_info.resource_type := l_resource.resource_type;
2227 l_res_cache_info.resource_name := l_resource.resource_name;
2228 l_res_cache_info.resource_number := l_resource.resource_number;
2229 END IF;
2230
2231 IF p_get_address THEN
2232 -- Check the validity of the Address in the Cache for the date
2233 IF l_res_cache_info.address.party_site_id IS NULL
2234 OR TRUNC(p_date) < l_res_cache_info.address.start_date_active
2235 OR TRUNC(p_date) > NVL(l_res_cache_info.address.end_date_active, p_date + 1)
2236 THEN
2237 csf_resource_address_pvt.get_resource_address(
2238 p_api_version => 1.0
2239 , x_return_status => l_return_status
2240 , x_msg_count => l_msg_count
2241 , x_msg_data => l_msg_data
2242 , p_resource_id => p_res_id
2243 , p_resource_type => p_res_type
2244 , p_date => p_date
2245 , p_res_shift_add => g_res_add_prof
2246 , x_address_rec => l_res_cache_info.address
2247 );
2248
2249 IF l_return_status <> fnd_api.g_ret_sts_success THEN
2250 RETURN NULL;
2251 END IF;
2252
2253 l_found := FALSE;
2254 END IF;
2255 END IF;
2256
2257 -- Cache the Resource Information for future use.
2258 IF NOT l_found THEN
2259 g_res_info_cache(p_res_id) := l_res_cache_info;
2260 END IF;
2261
2262 RETURN l_res_cache_info;
2263 END get_resource_from_cache;
2264
2265 /**
2266 * Returns the Resource Name given the Resource ID and Type.
2267 *
2268 * @param p_res_id Resource ID
2269 * @param p_res_type Resource Type Code
2270 * @returns Resource Name (VARCHAR2)
2271 */
2272 FUNCTION get_resource_name(p_res_id NUMBER, p_res_type VARCHAR2)
2273 RETURN VARCHAR2 IS
2274 BEGIN
2275 RETURN get_resource_from_cache(p_res_id, p_res_type).resource_name;
2276 END get_resource_name;
2277
2278 /**
2279 * Returns the Complete Resource Information given the Resource ID and Type.
2280 * The returned record includes Resource Number and Resource Name.
2281 *
2282 * @param p_res_id Resource ID
2283 * @param p_res_type Resource Type Code
2284 * @returns Resource Information filled in RESOURCE_REC_TYPE
2285 */
2286 FUNCTION get_resource_info(p_res_id NUMBER, p_res_type VARCHAR2)
2287 RETURN resource_rec_type IS
2288 l_res_cache_info resource_cache_rec_type;
2289 l_res_info resource_rec_type;
2290 BEGIN
2291 l_res_cache_info := get_resource_from_cache(p_res_id, p_res_type);
2292
2293 l_res_info.resource_id := l_res_cache_info.resource_id;
2294 l_res_info.resource_type := l_res_cache_info.resource_type;
2295 l_res_info.resource_name := l_res_cache_info.resource_name;
2296 l_res_info.resource_number := l_res_cache_info.resource_number;
2297
2298 RETURN l_res_info;
2299 END get_resource_info;
2300
2301 /**
2302 * Returns the Address of the Party created for the Resource as of the
2303 * date passed.
2304 *
2305 * @param p_res_id Resource ID
2306 * @param p_res_type Resource Type Code
2307 * @param p_date Active Party Site for the given date
2308 *
2309 * @returns Party Address of the Resource
2310 */
2311 FUNCTION get_resource_party_address (
2312 p_res_id NUMBER
2313 , p_res_type VARCHAR2
2314 , p_date DATE
2315 , p_res_shift_add VARCHAR2 DEFAULT NULL
2316 )
2317 RETURN csf_resource_address_pvt.address_rec_type IS
2318 BEGIN
2319 G_RES_ADD_PROF := p_res_shift_add;
2320
2321 RETURN get_resource_from_cache(p_res_id, p_res_type, TRUE, p_date).address;
2322 END get_resource_party_address;
2323
2324 /**
2325 * Returns the Resource Type Name corresponding to the Resource Type Code
2326 *
2327 * @param p_res_type Resource Type Code
2328 * @returns Resource Type Name (VARCHAR2)
2329 */
2330 FUNCTION get_resource_type_name(p_res_type VARCHAR2)
2331 RETURN VARCHAR2 IS
2332 i PLS_INTEGER;
2333
2334 CURSOR c_resource_type_names IS
2335 SELECT o.object_code code, o.name
2336 FROM jtf_object_usages u
2337 , jtf_objects_tl o
2338 WHERE u.object_user_code = 'RESOURCE_TYPES'
2339 AND o.object_code = u.object_code
2340 AND o.language = userenv('LANG');
2341 BEGIN
2342 IF g_res_type_name_tbl IS NULL THEN
2343 g_res_type_name_tbl := res_type_name_tbl_type();
2344 FOR v_resource_type_name IN c_resource_type_names LOOP
2345 g_res_type_name_tbl.extend();
2346 i := g_res_type_name_tbl.LAST;
2347 g_res_type_name_tbl(i).resource_type_code := v_resource_type_name.code;
2348 g_res_type_name_tbl(i).resource_type_name := v_resource_type_name.name;
2349 END LOOP;
2350 END IF;
2351
2352 FOR i IN 1..g_res_type_name_tbl.COUNT LOOP
2353 IF g_res_type_name_tbl(i).resource_type_code = p_res_type THEN
2354 RETURN g_res_type_name_tbl(i).resource_type_name;
2355 END IF;
2356 END LOOP;
2357 RETURN p_res_type;
2358 END get_resource_type_name;
2359
2360 /**
2361 * Converts the given Time from Resource Timezone to Server Timezone
2362 * or vice versa.
2363 * <br>
2364 * By default, the given date is assumed to be in Resource Timezone and the
2365 * date returned is Server Timezone. Set p_server_to_resource parameter as
2366 * 'T' (FND_API.G_TRUE) to make it return the other way round.
2367 * <br>
2368 * Note that the API doesnt support RS_TEAM or RS_GROUP resources.
2369 *
2370 * @param p_api_version API Version (1.0)
2371 * @param p_init_msg_list Initialize Message List
2372 * @param x_return_status Return Status of the Procedure.
2373 * @param x_msg_count Number of Messages in the Stack.
2374 * @param x_msg_data Stack of Error Messages.
2375 * @param p_resource_id Resource ID
2376 * @param p_resource_type Resource Type
2377 * @param p_datetime Date to be converted
2378 * @param p_server_to_resource Server to Resource Timezone
2379 */
2380 PROCEDURE convert_timezone(
2381 p_api_version IN NUMBER
2382 , p_init_msg_list IN VARCHAR2
2383 , x_return_status OUT NOCOPY VARCHAR2
2384 , x_msg_count OUT NOCOPY NUMBER
2385 , x_msg_data OUT NOCOPY VARCHAR2
2386 , p_resource_id IN NUMBER
2387 , p_resource_type IN VARCHAR2
2388 , x_datetime IN OUT NOCOPY DATE
2389 , p_server_to_resource IN VARCHAR2
2390 ) IS
2391 l_api_name CONSTANT VARCHAR2(30) := 'CONVERT_TIMEZONE';
2392 l_api_version CONSTANT NUMBER := 1.0;
2393 l_source_tz_id NUMBER;
2394 l_dest_tz_id NUMBER;
2395 l_temp_tz_id NUMBER;
2396 CURSOR c_resource IS
2397 SELECT time_zone
2398 FROM jtf_rs_resource_extns
2399 WHERE resource_id = p_resource_id
2400 AND p_resource_type NOT IN('RS_GROUP', 'RS_TEAM');
2401 BEGIN
2402 -- Standard call to check for call compatibility
2403 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, 'CSF_TASKS_PUB') THEN
2404 RAISE fnd_api.g_exc_unexpected_error;
2405 END IF;
2406
2407 -- Initialize message list if p_init_msg_list is set to TRUE
2408 IF fnd_api.to_boolean(p_init_msg_list) THEN
2409 fnd_msg_pub.initialize;
2410 END IF;
2411
2412 -- Initialize API return status to success
2413 x_return_status := fnd_api.g_ret_sts_success;
2414
2415 -- Start actual processing
2416 fnd_profile.get('SERVER_TIMEZONE_ID', l_dest_tz_id);
2417
2418 OPEN c_resource;
2419 FETCH c_resource INTO l_source_tz_id;
2420 CLOSE c_resource;
2421
2422 -- Reverse conversion when requested
2423 IF fnd_api.to_boolean(p_server_to_resource) THEN
2424 l_temp_tz_id := l_source_tz_id;
2425 l_source_tz_id := l_dest_tz_id;
2426 l_dest_tz_id := l_source_tz_id;
2427 END IF;
2428
2429 -- Only try conversion when source and destination timezones are found
2430 IF l_source_tz_id IS NOT NULL AND l_dest_tz_id IS NOT NULL THEN
2431 hz_timezone_pub.get_time(
2432 p_api_version => 1.0
2433 , p_init_msg_list => fnd_api.g_false
2434 , p_source_tz_id => l_source_tz_id
2435 , p_dest_tz_id => l_dest_tz_id
2436 , p_source_day_time => x_datetime
2437 , x_dest_day_time => x_datetime
2438 , x_return_status => x_return_status
2439 , x_msg_count => x_msg_count
2440 , x_msg_data => x_msg_data
2441 );
2442 END IF;
2443
2444 EXCEPTION
2445 WHEN fnd_api.g_exc_error THEN
2446 x_return_status := fnd_api.g_ret_sts_error;
2447 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2448 WHEN fnd_api.g_exc_unexpected_error THEN
2449 x_return_status := fnd_api.g_ret_sts_unexp_error;
2450 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2451 WHEN OTHERS THEN
2452 x_return_status := fnd_api.g_ret_sts_unexp_error;
2453 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
2454 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
2455 END IF;
2456 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2457 END convert_timezone;
2458
2459 /**
2460 * Gets the Shift Definitions of the given Resource between the two given Dates.
2461 *
2462 * CSF has its own "Get Resource Shifts" API in addition to JTF providing it is
2463 * because CSF is still calling JTF Calendar API rather than JTF Calendar 24 API.
2464 * Going forward, we should be calling JTF_CALENDAR24_PUB rather than
2465 * JTF_CALENDAR_PUB.
2466 * Because of this the following Shift Definition is returned as two Shifts.
2467 * <br>
2468 * Shift Construct #101: Start = 1-JAN-2005 18:00:00 to 2-JAN-2005 07:00:00
2469 * is returned as
2470 * Shift Record #1
2471 * Shift Construct = 101
2472 * Shift Date = 1-JAN-2005
2473 * Start Time = 18:00
2474 * End Time = 23:59
2475 *
2476 * Shift Record #2
2477 * Shift Construct = 101
2478 * Shift Date = 2-JAN-2005
2479 * Start Time = 00:00
2480 * End Time = 07:00
2481 * <br>
2482 * Note that Shift Record#1 and Shift Record#2 are adjacent in the returned
2483 * Shifts Table. Morever both has the same Shift Construct ID and the difference
2484 * between End Time of the first record and the start time of the second is
2485 * One Minute (1/1440 days).
2486 *
2487 * This feature is being used by this API to merge those shifts in a single
2488 * record structure.
2489 *
2490 * @param p_api_version API Version (1.0)
2491 * @param p_init_msg_list Initialize Message List
2492 * @param x_return_status Return Status of the Procedure.
2493 * @param x_msg_count Number of Messages in the Stack.
2494 * @param x_msg_data Stack of Error Messages.
2495 * @param p_resource_id Resource Identifier for whom Shifts are required.
2496 * @param p_resource_type Resource Type of the above Resource.
2497 * @param p_start_date Start of the Window between which Shifts are required.
2498 * @param p_end_date End of the Window between which Shifts are required.
2499 * @param x_shifts Shift Definitions
2500 */
2501 PROCEDURE get_resource_shifts(
2502 p_api_version IN NUMBER
2503 , p_init_msg_list IN VARCHAR2
2504 , x_return_status OUT NOCOPY VARCHAR2
2505 , x_msg_count OUT NOCOPY NUMBER
2506 , x_msg_data OUT NOCOPY VARCHAR2
2507 , p_resource_id IN NUMBER
2508 , p_resource_type IN VARCHAR2
2509 , p_start_date IN DATE
2510 , p_end_date IN DATE
2511 , p_shift_type IN VARCHAR2 DEFAULT NULL
2512 , x_shifts OUT NOCOPY shift_tbl_type
2513 ) IS
2514 l_api_name CONSTANT VARCHAR2(30) := 'GET_RESOURCE_SHIFTS';
2515 l_api_version CONSTANT NUMBER := 1.0;
2516
2517 l_multiday_shifts jtf_calendar_pub.shift_tbl_type;
2518 l_time DATE;
2519 l_shift_starttime DATE;
2520 l_shift_endtime DATE;
2521 i PLS_INTEGER; -- Iterator for JTF's Shift Table
2522 j PLS_INTEGER; -- Iterator for CSF's Shift Table
2523
2524 BEGIN
2525 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
2526 RAISE fnd_api.g_exc_unexpected_error;
2527 END IF;
2528
2529 IF fnd_api.to_boolean(p_init_msg_list) THEN
2530 fnd_msg_pub.initialize;
2531 END IF;
2532
2533 x_return_status := fnd_api.g_ret_sts_success;
2534
2535 jtf_calendar_pub.get_resource_shifts(
2536 p_api_version => 1.0
2537 , p_resource_id => p_resource_id
2538 , p_resource_type => p_resource_type
2539 , p_start_date => p_start_date
2540 , p_end_date => p_end_date
2541 , x_return_status => x_return_status
2542 , x_msg_count => x_msg_count
2543 , x_msg_data => x_msg_data
2544 , x_shift => l_multiday_shifts
2545 );
2546
2547 IF x_return_status <> fnd_api.g_ret_sts_success OR l_multiday_shifts.COUNT = 0 THEN
2548 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
2549 RAISE fnd_api.g_exc_unexpected_error;
2550 END IF;
2551 RAISE fnd_api.g_exc_error;
2552 END IF;
2553
2554 i := l_multiday_shifts.FIRST;
2555 j := 0;
2556 WHILE i IS NOT NULL LOOP
2557 l_time := to_date(l_multiday_shifts(i).start_time, 'HH24:MI');
2558 l_shift_starttime := l_multiday_shifts(i).shift_date + (l_time - trunc(l_time));
2559 l_time := to_date(l_multiday_shifts(i).end_time, 'HH24:MI');
2560 l_shift_endtime := l_multiday_shifts(i).shift_date + (l_time - trunc(l_time));
2561
2562
2563
2564 IF (l_multiday_shifts(i).availability_type = p_shift_type OR P_SHIFT_TYPE IS NULL) THEN
2565 -- Check whether the previous shift is same as the current one with a Minute Difference
2566 IF ( x_shifts.EXISTS(j)
2567 AND x_shifts(j).shift_construct_id = l_multiday_shifts(i).shift_construct_id
2568 AND (x_shifts(j).end_datetime + 1/1440) = l_shift_starttime )
2569 THEN
2570 -- Its the same shift but crossing the 24 Hour Boundary. Merge them.
2571 x_shifts(j).end_datetime := l_shift_endtime;
2572 ELSE
2573 IF l_shift_endtime > p_start_date AND l_shift_starttime < p_end_date THEN
2574 j := j+1;
2575 x_shifts(j).shift_construct_id := l_multiday_shifts(i).shift_construct_id;
2576 x_shifts(j).availability_type := l_multiday_shifts(i).availability_type;
2577 x_shifts(j).start_datetime := l_shift_starttime;
2578 x_shifts(j).end_datetime := l_shift_endtime;
2579 END IF;
2580 END IF;
2581 END IF;
2582
2583 i := l_multiday_shifts.NEXT(i);
2584 END LOOP;
2585
2586 EXCEPTION
2587 WHEN fnd_api.g_exc_error THEN
2588 x_return_status := fnd_api.g_ret_sts_error;
2589 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2590 WHEN fnd_api.g_exc_unexpected_error THEN
2591 x_return_status := fnd_api.g_ret_sts_unexp_error;
2592 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2593 WHEN OTHERS THEN
2594 x_return_status := fnd_api.g_ret_sts_unexp_error;
2595 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
2596 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
2597 END IF;
2598 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2599 END get_resource_shifts;
2600
2601 PROCEDURE get_location(
2602 x_return_status OUT NOCOPY VARCHAR2
2603 , x_msg_count OUT NOCOPY NUMBER
2604 , x_msg_data OUT NOCOPY VARCHAR2
2605 , p_resource_id IN NUMBER
2606 , p_resource_type IN VARCHAR2
2607 , p_date IN DATE DEFAULT SYSDATE
2608 , x_creation_date OUT NOCOPY DATE
2609 , x_feed_time OUT NOCOPY DATE
2610 , x_status_code OUT NOCOPY VARCHAR2
2611 , x_latitude OUT NOCOPY NUMBER
2612 , x_longitude OUT NOCOPY NUMBER
2613 , x_speed OUT NOCOPY NUMBER
2614 , x_direction OUT NOCOPY VARCHAR2
2615 , x_parked_time OUT NOCOPY NUMBER
2616 , x_address OUT NOCOPY VARCHAR2
2617 , x_device_tag OUT NOCOPY VARCHAR2
2618 , x_status_code_meaning OUT NOCOPY VARCHAR2
2619 ) IS
2620 l_address csf_resource_address_pvt.address_rec_type;
2621 l_geometry MDSYS.SDO_GEOMETRY;
2622 l_res_lat NUMBER;
2623 l_res_lon NUMBER;
2624 l_valid_geo VARCHAR2(5);
2625 l_mapping_count PLS_INTEGER := 0;
2626 --
2627 CURSOR c_status_lookup IS
2628 SELECT NVL(meaning, 'AT_HOME')
2629 FROM fnd_lookups
2630 WHERE lookup_type = 'CSF_GPS_DEVICE_STATUSES'
2631 AND lookup_code = 'AT_HOME';
2632 CURSOR c_device_res_map_exists IS
2633 SELECT count(*)
2634 FROM csf_gps_device_assignments a
2635 WHERE a.resource_id = p_resource_id
2636 AND a.resource_type = p_resource_type
2637 AND NVL(p_date, SYSDATE) BETWEEN a.start_date_active AND NVL(a.end_date_active, SYSDATE + 1);
2638 BEGIN
2639 x_return_status := fnd_api.g_ret_sts_success;
2640
2641 OPEN c_device_res_map_exists;
2642 FETCH c_device_res_map_exists INTO l_mapping_count;
2643 CLOSE c_device_res_map_exists;
2644
2645 IF p_date > SYSDATE OR NVL(csf_gps_pub.is_gps_enabled, 'N') <> 'Y' OR l_mapping_count = 0 THEN
2646 l_address :=
2647 get_resource_party_address(
2648 p_res_id => p_resource_id
2649 , p_res_type => p_resource_type
2650 , p_date => p_date
2651 );
2652
2653 -- This is wrong. It should follow the Country specific Formatting rules
2654 x_address := l_address.street || ', ' || l_address.city || ', ' || l_address.state || ', ' || l_address.postal_code || ', ' || l_address.country;
2655 x_status_code := 'AT_HOME';
2656 x_device_tag :=
2657 csf_gps_pub.get_gps_label(
2658 p_resource_id => p_resource_id
2659 , p_resource_type => p_resource_type
2660 , p_date => p_date
2661 );
2662 OPEN c_status_lookup;
2663 FETCH c_status_lookup INTO x_status_code_meaning;
2664 CLOSE c_status_lookup;
2665
2666 -- Fetch the Geometry corresponding to the Address
2667 IF l_address.geometry IS NOT NULL THEN
2668 csf_locus_pub.verify_locus(
2669 p_api_version => 1.0
2670 , p_locus => l_address.geometry
2671 , x_msg_count => x_msg_count
2672 , x_msg_data => x_msg_data
2673 , x_return_status => x_return_status
2674 , x_result => l_valid_geo
2675 );
2676
2677 IF l_valid_geo = 'TRUE' THEN
2678 IF l_address.geometry.sdo_elem_info IS NOT NULL
2679 AND l_address.geometry.sdo_ordinates IS NOT NULL
2680 THEN
2681 x_longitude := ROUND(l_address.geometry.sdo_ordinates(1), 8);
2682 x_latitude := ROUND(l_address.geometry.sdo_ordinates(2), 8);
2683 ELSIF l_address.geometry.sdo_point IS NOT NULL
2684 THEN
2685 x_longitude := ROUND(l_address.geometry.sdo_point.x, 8);
2686 x_latitude := ROUND(l_address.geometry.sdo_point.y, 8);
2687 ELSE
2688 x_longitude := -9999;
2689 x_latitude := -9999;
2690 END IF;
2691 ELSE
2692 x_longitude := -9999;
2693 x_latitude := -9999;
2694 END IF;
2695 ELSE
2696 x_longitude := -9999;
2697 x_latitude := -9999;
2698 END IF;
2699 x_status_code_meaning := NULL;
2700 ELSE
2701 csf_gps_pub.get_location(
2702 p_resource_id => p_resource_id
2703 , p_resource_type => p_resource_type
2704 , p_date => p_date
2705 , x_feed_time => x_feed_time
2706 , x_status_code => x_status_code
2707 , x_latitude => x_latitude
2708 , x_longitude => x_longitude
2709 , x_speed => x_speed
2710 , x_direction => x_direction
2711 , x_parked_time => x_parked_time
2712 , x_address => x_address
2713 , x_creation_date => x_creation_date
2714 , x_device_tag => x_device_tag
2715 , x_status_code_meaning => x_status_code_meaning
2716 );
2717 END IF;
2718 EXCEPTION
2719 WHEN OTHERS THEN
2720 x_return_status := fnd_api.g_ret_sts_unexp_error;
2721 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
2722 fnd_msg_pub.add_exc_msg(g_pkg_name, 'GET_LOCATION');
2723 END IF;
2724 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2725 END get_location;
2726
2727 PROCEDURE get_location(
2728 x_return_status OUT NOCOPY VARCHAR2
2729 , x_msg_count OUT NOCOPY NUMBER
2730 , x_msg_data OUT NOCOPY VARCHAR2
2731 , p_resource_id IN NUMBER
2732 , p_resource_type IN VARCHAR2
2733 , p_date IN DATE DEFAULT SYSDATE
2734 , x_latitude OUT NOCOPY NUMBER
2735 , x_longitude OUT NOCOPY NUMBER
2736 , x_address OUT NOCOPY VARCHAR2
2737 , x_status_meaning OUT NOCOPY VARCHAR2
2738 , x_device_tag OUT NOCOPY VARCHAR2
2739 ) IS
2740 l_feed_time csf_gps_location_feeds.vendor_feed_time%TYPE;
2741 l_status_code csf_gps_location_feeds.status%TYPE;
2742 l_speed csf_gps_location_feeds.speed%TYPE;
2743 l_direction csf_gps_location_feeds.direction%TYPE;
2744 l_parked_time csf_gps_location_feeds.parked_time%TYPE;
2745 l_creation_date csf_gps_location_feeds.creation_date%TYPE;
2746 BEGIN
2747 get_location(
2748 x_msg_count => x_msg_count
2749 , x_msg_data => x_msg_data
2750 , x_return_status => x_return_status
2751 , p_resource_id => p_resource_id
2752 , p_resource_type => p_resource_type
2753 , p_date => p_date
2754 , x_feed_time => l_feed_time
2755 , x_status_code => l_status_code
2756 , x_latitude => x_latitude
2757 , x_longitude => x_longitude
2758 , x_speed => l_speed
2759 , x_direction => l_direction
2760 , x_parked_time => l_parked_time
2761 , x_address => x_address
2762 , x_creation_date => l_creation_date
2763 , x_device_tag => x_device_tag
2764 , x_status_code_meaning => x_status_meaning
2765 );
2766 END get_location;
2767
2768 FUNCTION get_location (
2769 p_resource_id IN NUMBER
2770 , p_resource_type IN VARCHAR2
2771 , p_date IN DATE DEFAULT SYSDATE
2772 ) RETURN MDSYS.SDO_POINT_TYPE IS
2773 l_return_status VARCHAR2(1);
2774 l_msg_data VARCHAR2(2000);
2775 l_msg_count NUMBER;
2776 l_feed_time csf_gps_location_feeds.vendor_feed_time%TYPE;
2777 l_status_code csf_gps_location_feeds.status%TYPE;
2778 l_latitude csf_gps_location_feeds.latitude%TYPE;
2779 l_longitude csf_gps_location_feeds.longitude%TYPE;
2780 l_speed csf_gps_location_feeds.speed%TYPE;
2781 l_direction csf_gps_location_feeds.direction%TYPE;
2782 l_parked_time csf_gps_location_feeds.parked_time%TYPE;
2783 l_address csf_gps_location_feeds.address%TYPE;
2784 l_creation_date csf_gps_location_feeds.creation_date%TYPE;
2785 l_device_tag csf_gps_devices.device_tag%TYPE;
2786 l_status_code_meaning fnd_lookups.meaning%TYPE;
2787 BEGIN
2788 get_location(
2789 x_msg_count => l_msg_count
2790 , x_msg_data => l_msg_data
2791 , x_return_status => l_return_status
2792 , p_resource_id => p_resource_id
2793 , p_resource_type => p_resource_type
2794 , p_date => p_date
2795 , x_feed_time => l_feed_time
2796 , x_status_code => l_status_code
2797 , x_latitude => l_latitude
2798 , x_longitude => l_longitude
2799 , x_speed => l_speed
2800 , x_direction => l_direction
2801 , x_parked_time => l_parked_time
2802 , x_address => l_address
2803 , x_creation_date => l_creation_date
2804 , x_device_tag => l_device_tag
2805 , x_status_code_meaning => l_status_code_meaning
2806 );
2807 RETURN MDSYS.SDO_POINT_TYPE(l_longitude, l_latitude, 0);
2808 END get_location;
2809
2810 FUNCTION get_location_attributes(
2811 p_resource_id IN NUMBER
2812 , p_resource_type IN VARCHAR2
2813 , p_date IN DATE DEFAULT SYSDATE
2814 )
2815 RETURN VARCHAR2 IS
2816 l_return_status VARCHAR2(1);
2817 l_msg_data VARCHAR2(2000);
2818 l_msg_count NUMBER;
2819 l_feed_time csf_gps_location_feeds.vendor_feed_time%TYPE;
2820 l_status_code csf_gps_location_feeds.status%TYPE;
2821 l_latitude csf_gps_location_feeds.latitude%TYPE;
2822 l_longitude csf_gps_location_feeds.longitude%TYPE;
2823 l_speed csf_gps_location_feeds.speed%TYPE;
2824 l_direction csf_gps_location_feeds.direction%TYPE;
2825 l_parked_time csf_gps_location_feeds.parked_time%TYPE;
2826 l_address csf_gps_location_feeds.address%TYPE;
2827 l_creation_date csf_gps_location_feeds.creation_date%TYPE;
2828 l_device_tag csf_gps_devices.device_tag%TYPE;
2829 l_status_code_meaning fnd_lookups.meaning%TYPE;
2830 BEGIN
2831 get_location(
2832 x_msg_count => l_msg_count
2833 , x_msg_data => l_msg_data
2834 , x_return_status => l_return_status
2835 , p_resource_id => p_resource_id
2836 , p_resource_type => p_resource_type
2837 , p_date => p_date
2838 , x_feed_time => l_feed_time
2839 , x_status_code => l_status_code
2840 , x_latitude => l_latitude
2841 , x_longitude => l_longitude
2842 , x_speed => l_speed
2843 , x_direction => l_direction
2844 , x_parked_time => l_parked_time
2845 , x_address => l_address
2846 , x_creation_date => l_creation_date
2847 , x_device_tag => l_device_tag
2848 , x_status_code_meaning => l_status_code_meaning
2849 );
2850
2851 RETURN l_feed_time || '@@'
2852 || l_status_code || '@@'
2853 || l_latitude || '@@'
2854 || l_longitude || '@@'
2855 || l_speed || '@@'
2856 || l_direction || '@@'
2857 || l_parked_time || '@@'
2858 || l_address || '@@'
2859 || l_creation_date || '@@'
2860 || l_device_tag || '@@'
2861 || l_status_code_meaning || '@@' ||'END';
2862 END get_location_attributes;
2863
2864 FUNCTION geo_distance(p_lon1 NUMBER, p_lat1 NUMBER, p_lon2 NUMBER, p_lat2 NUMBER)
2865 RETURN NUMBER IS
2866 l_north NUMBER;
2867 l_east NUMBER;
2868 BEGIN
2869 l_north := ( (p_lat2*g_pi/180.0) - (p_lat1*g_pi/180.0) ) * g_earth_radius;
2870 l_east := ( ( (p_lon2*g_pi/180.0) -(p_lon1*g_pi/180.0) ) * COS(p_lat2* g_pi/180.0) )
2871 * g_earth_radius;
2872 RETURN SQRT ( (l_north * l_north) + (l_east * l_east) );
2873 END geo_distance;
2874
2875 FUNCTION get_third_party_role(
2876 p_resource_id IN NUMBER
2877 , p_resource_type IN VARCHAR2
2878 ) RETURN VARCHAR2 IS
2879
2880 CURSOR c_roles IS
2881 SELECT jrb.role_code
2882 , jrr.role_resource_type
2883 FROM jtf_rs_role_relations jrr
2884 , jtf_rs_roles_b jrb
2885 WHERE jrr.role_id = jrb.role_id
2886 AND jrr.role_resource_id = p_resource_id
2887 AND jrb.role_type_code = 'CSF_THIRD_PARTY'
2888 AND jrr.role_resource_type = p_resource_type
2889 AND ( jrr.start_date_active IS NULL or trunc(jrr.start_date_active) <= sysdate )
2890 AND ( jrr.end_date_active IS NULL or trunc(jrr.end_date_active) >= sysdate )
2891 AND NVL( jrr.delete_flag, 'N') = 'N'
2892 ORDER BY 1;
2893
2894 l_role VARCHAR2(30) := NULL;
2895 l_type VARCHAR2(30) := NULL;
2896
2897 BEGIN
2898 IF ( p_resource_id IS NOT NULL and p_resource_type IS NOT NULL ) THEN
2899 OPEN c_roles;
2900 LOOP
2901 FETCH c_roles INTO l_role, l_type;
2902 EXIT WHEN c_roles%NOTFOUND;
2903 -- A Group Resource with TPS is eligible for third party scheduling
2904 -- Any other type of resource with TPS is considered as Internal Resource
2905 IF l_role = 'CSF_THIRD_PARTY_SERVICE_PROVID' AND l_type = 'RS_GROUP'
2906 THEN
2907 RETURN l_role;
2908 END IF;
2909 IF l_role = 'CSF_THIRD_PARTY_TECHNICIAN'
2910 THEN
2911 RETURN l_role;
2912 END IF;
2913 END LOOP;
2914 RETURN l_role;
2915 END IF;
2916 RETURN NULL;
2917 END get_third_party_role;
2918
2919 BEGIN
2920 init_assign_errors;
2921 END csf_resource_pub;
[Home] [Help]