1 PACKAGE BODY CS_SR_RES_CODE_MAPPING_PKG AS
2 /* $Header: csxrscdb.pls 120.0 2005/12/12 16:12:29 smisra noship $ */
3
4 PROCEDURE VALIDATE_RESOLUTION_CODE
5 ( p_api_version IN NUMBER,
6 p_init_msg_list IN VARCHAR2,
7 p_rescode_criteria_rec IN CS_SR_RES_CODE_MAPPING_PKG.rescode_search_rec,
8 p_resolution_code IN VARCHAR2,
9 x_return_status OUT NOCOPY VARCHAR2,
10 x_msg_count OUT NOCOPY NUMBER,
11 x_msg_data OUT NOCOPY VARCHAR2
12 ) IS
13
14 l_api_version CONSTANT NUMBER := 1.0;
15 l_api_name CONSTANT VARCHAR2(30) := 'VALIDATE_RESOLUTION_CODE';
16 l_errname varchar2(60);
17 l_errmsg varchar2(2000);
18 l_errstack varchar2(4000);
19
20 l_resolution_code_meaning VARCHAR2(80);
21 l_resolution_code VARCHAR2(30);
22 l_problem_code VARCHAR2(30);
23 l_incident_type_id NUMBER;
24 l_inventory_item_id NUMBER;
25 l_organization_id NUMBER;
26 l_category_id NUMBER;
27 l_category_set_id NUMBER;
28
29 l_start_date_active DATE;
30 l_end_date_active DATE;
31
32 /* This cursor checks if the resolution code is an active resolution code or not.
33 If it is NOT an active resolution code, we stop validation immeadiately
34 Else, we continue with the rest of the resolution code validation */
35 CURSOR cs_sr_active_rc_csr IS
36 select meaning from
37 cs_lookups cslkup
38 where
39 cslkup.lookup_type='REQUEST_RESOLUTION_CODE' and
40 cslkup.lookup_code = l_resolution_code and
41 (cslkup.start_date_active is null or trunc(cslkup.start_date_active) <= trunc(sysdate)) and
42 (cslkup.end_date_active is null or trunc(cslkup.end_date_active) >= trunc(sysdate));
43
44
45 /* This cursor checks if the resolution code is in the unmapped resolution code list
46 We also make sure that even if a resolution code mapping is found, it has to be
47 an active resolution code mapping. Else, we assume that the resolution code mapping
48 can be disregarded */
49 CURSOR cs_sr_unmapped_rc_csr IS
50 select meaning from
51 cs_lookups cslkup
52 where
53 cslkup.lookup_type='REQUEST_RESOLUTION_CODE' and
54 cslkup.lookup_code = l_resolution_code and
55 not exists
56 ( select 'X' from
57 cs_sr_res_code_mapping_detail cstl
58 where
59 cstl.resolution_code = cslkup.lookup_code and
60 (cstl.map_start_date_active is null or trunc(cstl.map_start_date_active) <= trunc(sysdate)) and
61 (cstl.map_end_date_active is null or trunc(cstl.map_end_date_active) >= trunc(sysdate)) and
62 (cstl.start_date_active is null or trunc(cstl.start_date_active) <= trunc(sysdate)) and
63 (cstl.end_date_active is null or trunc(cstl.end_date_active) >= trunc(sysdate)));
64
65
66 /*
67 The following cursors will check if the resolution code is mapped to one of the following search criteria :
68 1) Service Request Type
69 2) Product
70 3) Product Category (All product categories that belong to the product category set held by the CS_SR_DEFAULT_CATEGORY_SET profile option)
71 4) Problem Code
72 5) Service Request Type + Product
73 6) Service Request Type + All product categories that belong to the product category set held by the CS_SR_DEFAULT_CATEGORY_SET profile option
74 7) Service Request Type + Problem Code
75 8) Product + Problem Code
76 9) Problem Code + All product categories that belong to the product category set held by the CS_SR_DEFAULT_CATEGORY_SET profile option
77 10) Service Request Type + Problem Code + Product
78 11) Service Request Type + Problem Code + All product categories that belong to the product category set held by the CS_SR_DEFAULT_CATEGORY_SET profile option
79 */
80 CURSOR cs_sr_rc_catset_srtype IS
81 select meaning
82 from
83 cs_lookups cslkup
84 where
85 cslkup.lookup_type='REQUEST_RESOLUTION_CODE' and
86 cslkup.lookup_code = l_resolution_code and
87 exists
88 ( select 'X' from
89 cs_sr_res_code_mapping_detail cstl
90 where
91 cstl.resolution_code = cslkup.lookup_code and
92 (cstl.map_start_date_active is null or trunc(cstl.map_start_date_active) <= trunc(sysdate)) and
93 (cstl.map_end_date_active is null or trunc(cstl.map_end_date_active) >= trunc(sysdate)) and
94 (cstl.start_date_active is null or trunc(cstl.start_date_active) <= trunc(sysdate)) and
95 (cstl.end_date_active is null or trunc(cstl.end_date_active) >= trunc(sysdate))
96 and
97 (
98 ( cstl.incident_type_id = l_incident_type_id and
99 cstl.inventory_item_id is null and
100 cstl.category_id is null and
101 cstl.problem_code is null)
102 or
103 ( cstl.incident_type_id = l_incident_type_id and
104 cstl.inventory_item_id = l_inventory_item_id and
105 cstl.organization_id = l_organization_id and
106 cstl.category_id is null and
107 cstl.problem_code is null)
108 or
109 ( cstl.incident_type_id = l_incident_type_id and
110 cstl.inventory_item_id is null and
111 exists (select category_id from mtl_item_categories cmtlc
112 where
113 cmtlc.category_id = cstl.category_id and
114 category_set_id = l_category_set_id and /* value from profile CS_SR_DEFAULT_CATEGORY_SET */
115 cmtlc.inventory_item_id = l_inventory_item_id and
116 cmtlc.organization_id = l_organization_id) and
117 cstl.problem_code is null)
118 or
119 ( cstl.incident_type_id = l_incident_type_id and
120 cstl.inventory_item_id is null and
121 cstl.category_id is null and
122 cstl.problem_code = l_problem_code)
123 or
124 ( cstl.incident_type_id = l_incident_type_id and
125 cstl.problem_code = l_problem_code and
126 cstl.inventory_item_id = l_inventory_item_id and
127 cstl.organization_id = l_organization_id and
128 cstl.category_id is null)
129 or
130 ( cstl.incident_type_id = l_incident_type_id and
131 cstl.problem_code = l_problem_code and
132 exists (select category_id from mtl_item_categories cmtlc
133 where
134 cmtlc.category_id = cstl.category_id and
135 cmtlc.category_set_id = l_category_set_id and /* value from profile CS_SR_DEFAULT_CATEGORY_SET */
136 cmtlc.inventory_item_id = l_inventory_item_id and
137 cmtlc.organization_id = l_organization_id) and
138 cstl.inventory_item_id is null)
139 ) /* end of various combinations of search criteria */
140 ) ; /* end of the exists condition */
141
142
143
144 CURSOR cs_sr_rc_catset_prod IS
145 select meaning
146 from
147 cs_lookups cslkup
148 where
149 cslkup.lookup_type='REQUEST_RESOLUTION_CODE' and
150 cslkup.lookup_code = l_resolution_code and
151 exists
152 ( select 'X' from
153 cs_sr_res_code_mapping_detail cstl
154 where
155 cstl.resolution_code = cslkup.lookup_code and
156 (cstl.map_start_date_active is null or trunc(cstl.map_start_date_active) <= trunc(sysdate)) and
157 (cstl.map_end_date_active is null or trunc(cstl.map_end_date_active) >= trunc(sysdate)) and
158 (cstl.start_date_active is null or trunc(cstl.start_date_active) <= trunc(sysdate)) and
159 (cstl.end_date_active is null or trunc(cstl.end_date_active) >= trunc(sysdate))
160 and
161 (
162 ( cstl.incident_type_id is null and
163 cstl.inventory_item_id = l_inventory_item_id and
164 cstl.organization_id = l_organization_id and
165 cstl.category_id is null and
166 cstl.problem_code is null)
167 or
168 ( cstl.incident_type_id is null and
169 cstl.inventory_item_id = l_inventory_item_id and
170 cstl.organization_id = l_organization_id and
171 cstl.category_id is null and
172 cstl.problem_code = l_problem_code)
173 ) /* end of various combinations of search criteria */
174 ) ; /* end of the exists condition */
175
176
177
178 CURSOR cs_sr_rc_catset_prodcat IS
179 select meaning
180 from
181 cs_lookups cslkup
182 where
183 cslkup.lookup_type='REQUEST_RESOLUTION_CODE' and
184 cslkup.lookup_code = l_resolution_code and
185 exists
186 ( select 'X' from
187 cs_sr_res_code_mapping_detail cstl
188 where
189 cstl.resolution_code = cslkup.lookup_code and
190 (cstl.map_start_date_active is null or trunc(cstl.map_start_date_active) <= trunc(sysdate)) and
191 (cstl.map_end_date_active is null or trunc(cstl.map_end_date_active) >= trunc(sysdate)) and
192 (cstl.start_date_active is null or trunc(cstl.start_date_active) <= trunc(sysdate)) and
193 (cstl.end_date_active is null or trunc(cstl.end_date_active) >= trunc(sysdate))
194 and
195 (
196 ( cstl.incident_type_id is null and
197 cstl.inventory_item_id is null and
198 exists (select category_id from mtl_item_categories cmtlc
199 where
200 cmtlc.category_id = cstl.category_id and
201 cmtlc.category_set_id = l_category_set_id and /* value from profile CS_SR_DEFAULT_CATEGORY_SET */
202 cmtlc.inventory_item_id = l_inventory_item_id and
203 cmtlc.organization_id = l_organization_id) and
204 cstl.problem_code is null)
205 or
206 ( cstl.incident_type_id is null and
207 cstl.inventory_item_id is null and
208 exists (select category_id from mtl_item_categories cmtlc
209 where
210 cmtlc.category_id = cstl.category_id and
211 category_set_id = l_category_set_id and /* value from profile CS_SR_DEFAULT_CATEGORY_SET */
212 cmtlc.inventory_item_id = l_inventory_item_id) and
213 cstl.problem_code = l_problem_code)
214 ) /* end of various combinations of search criteria */
215 ) ; /* end of the exists condition */
216
217
218
219 CURSOR cs_sr_rc_catset_probc IS
220 select meaning
221 from
222 cs_lookups cslkup
223 where
224 cslkup.lookup_type='REQUEST_RESOLUTION_CODE' and
225 cslkup.lookup_code = l_resolution_code and
226 exists
227 ( select 'X' from
228 cs_sr_res_code_mapping_detail cstl
229 where
230 cstl.resolution_code = cslkup.lookup_code and
231 (cstl.map_start_date_active is null or trunc(cstl.map_start_date_active) <= trunc(sysdate)) and
232 (cstl.map_end_date_active is null or trunc(cstl.map_end_date_active) >= trunc(sysdate)) and
233 (cstl.start_date_active is null or trunc(cstl.start_date_active) <= trunc(sysdate)) and
234 (cstl.end_date_active is null or trunc(cstl.end_date_active) >= trunc(sysdate))
235 and
236 (
237 ( cstl.incident_type_id is null and
238 cstl.inventory_item_id is null and
239 cstl.category_id is null and
240 cstl.problem_code = l_problem_code)
241 ) /* end of various combinations of search criteria */
242 ) ; /* end of the exists condition */
243
244
245 /*
246 The following cursors will check if the resolution code is mapped to one of the following search criteria :
247 1) Service Request Type
248 2) Product
249 3) Product Category
250 4) Problem Code
251 5) Service Request Type + Product
252 6) Service Request Type + Product Category
253 7) Service Request Type + Problem Code
254 8) Product + Problem Code
255 9) Problem Code + Product Category
256 10) Service Request Type + Problem Code + Product
257 11) Service Request Type + Problem Code + Product Category
258 */
259
260 CURSOR cs_sr_rc_cat_srtype IS
261 select meaning
262 from
263 cs_lookups cslkup
264 where
265 cslkup.lookup_type='REQUEST_RESOLUTION_CODE' and
266 cslkup.lookup_code = l_resolution_code and
267 exists
268 ( select 'X' from
269 cs_sr_res_code_mapping_detail cstl
270 where
271 cstl.resolution_code = cslkup.lookup_code and
272 (cstl.map_start_date_active is null or trunc(cstl.map_start_date_active) <= trunc(sysdate)) and
273 (cstl.map_end_date_active is null or trunc(cstl.map_end_date_active) >= trunc(sysdate)) and
274 (cstl.start_date_active is null or trunc(cstl.start_date_active) <= trunc(sysdate)) and
275 (cstl.end_date_active is null or trunc(cstl.end_date_active) >= trunc(sysdate))
276 and
277 (
278 ( cstl.incident_type_id = l_incident_type_id and
279 cstl.inventory_item_id is null and
280 cstl.category_id is null and
281 cstl.problem_code is null)
282 or
283 ( cstl.incident_type_id = l_incident_type_id and
284 cstl.inventory_item_id = l_inventory_item_id and
285 cstl.organization_id = l_organization_id and
286 cstl.category_id is null and
287 cstl.problem_code is null)
288 or
289 ( cstl.incident_type_id = l_incident_type_id and
290 cstl.inventory_item_id is null and
291 cstl.category_id = l_category_id and
292 cstl.problem_code is null)
293 or
294 ( cstl.incident_type_id = l_incident_type_id and
295 cstl.inventory_item_id is null and
296 cstl.category_id is null and
297 cstl.problem_code = l_problem_code)
298 or
299 ( cstl.incident_type_id = l_incident_type_id and
300 cstl.problem_code = l_problem_code and
301 cstl.inventory_item_id = l_inventory_item_id and
302 cstl.organization_id = l_organization_id and
303 cstl.category_id is null)
304 or
305 ( cstl.incident_type_id = l_incident_type_id and
306 cstl.problem_code = l_problem_code and
307 cstl.category_id = l_category_id and
308 cstl.inventory_item_id is null)
309 ) /* end of various combinations of search criteria */
310 ) ; /* end of the exists condition */
311
312
313
314 CURSOR cs_sr_rc_cat_prodcat IS
315 select meaning
316 from
317 cs_lookups cslkup
318 where
319 cslkup.lookup_type='REQUEST_RESOLUTION_CODE' and
320 cslkup.lookup_code = l_resolution_code and
321 exists
322 ( select 'X' from
323 cs_sr_res_code_mapping_detail cstl
324 where
325 cstl.resolution_code = cslkup.lookup_code and
326 (cstl.map_start_date_active is null or trunc(cstl.map_start_date_active) <= trunc(sysdate)) and
327 (cstl.map_end_date_active is null or trunc(cstl.map_end_date_active) >= trunc(sysdate)) and
328 (cstl.start_date_active is null or trunc(cstl.start_date_active) <= trunc(sysdate)) and
329 (cstl.end_date_active is null or trunc(cstl.end_date_active) >= trunc(sysdate))
330 and
331 (
332 ( cstl.incident_type_id is null and
333 cstl.inventory_item_id is null and
334 cstl.category_id = l_category_id and
335 cstl.problem_code is null)
336 or
337 ( cstl.incident_type_id is null and
338 cstl.inventory_item_id is null and
339 cstl.category_id = l_category_id and
340 cstl.problem_code = l_problem_code)
341 ) /* end of various combinations of search criteria */
342 ) ; /* end of the exists condition */
343
344
345 BEGIN
346
347 x_return_status := FND_API.G_RET_STS_SUCCESS;
348
349 IF fnd_api.to_boolean (p_init_msg_list)
350 THEN
351 fnd_msg_pub.initialize;
352 END IF;
353
354
355 l_incident_type_id := nvl(p_rescode_criteria_rec.service_request_type_id,0);
356 l_category_id := nvl(p_rescode_criteria_rec.product_category_id,0);
357 l_inventory_item_id := nvl(p_rescode_criteria_rec.inventory_item_id,0);
358 l_organization_id := nvl(p_rescode_criteria_rec.organization_id,0);
359 l_problem_code := nvl(p_rescode_criteria_rec.problem_code,' ');
360 l_resolution_code := p_resolution_code;
361 l_category_set_id := FND_PROFILE.value('CS_SR_DEFAULT_CATEGORY_SET');
362
363
364 /* Roopa - Begin - fix for bug 3335668 */
365 /* Changed the initialized value from 'null' to 0 or ' ' value */
366 IF (l_incident_type_id = FND_API.G_MISS_NUM) THEN
367 l_incident_type_id := 0;
368 END IF;
369 IF (l_category_id = FND_API.G_MISS_NUM) THEN
370 l_category_id := 0;
371 END IF;
372 IF (l_inventory_item_id = FND_API.G_MISS_NUM) THEN
373 l_inventory_item_id := 0;
374 END IF;
375 IF (l_organization_id = FND_API.G_MISS_NUM) THEN
376 l_organization_id := 0;
377 END IF;
378 IF (l_problem_code = FND_API.G_MISS_CHAR) THEN
379 l_problem_code := ' ';
380 END IF;
381 IF (l_category_set_id = FND_API.G_MISS_NUM) THEN
382 l_category_set_id := 0;
383 END IF;
384 IF (l_resolution_code = FND_API.G_MISS_CHAR) THEN
385 RAISE FND_API.G_EXC_ERROR;
386 END IF;
387 /* Roopa - End - fix for bug 3335668 */
388
389 IF (l_inventory_item_id <> 0 and l_organization_id = 0) THEN
390 l_organization_id := FND_PROFILE.value('CS_INV_VALIDATION_ORG');
391 END IF;
392
393 /* Validation Checks on the resolution code:
394 #1) Check if the resolution code exists in the unmapped resolution code list or
395 #2) Check if the resolution code is mapped to any combination of :
396 sr type
397 sr type and product
398 sr type and prod category
399 sr type and problem code
400 sr type, prod and prob code
401 sr type, prod cat and prob code
402 #3) Check if the resolution code is mapped to any combination of :
403 product
404 product and problem code
405 #4) Check if the resolution code is mapped to any combination of :
406 product category
407 product category and problem code
408 #5) Check if the resolution code is mapped to any combination of :
409 problem code
410 #6) Check if the resolution code is mapped to any combination of :
411 sr type
412 sr type and product
413 sr type and prod category
414 sr type and problem code
415 sr type, prod and prob code
416 sr type, prod cat and prob code
417 (where prod category = all product categories that belong to the
418 product category set held in the CS_SR_DEFAULT_CATEGORY_SET profile option
419 #7) Check if the resolution code is mapped to any combination of :
420 product
421 product and problem code
422 #8) Check if the resolution code is mapped to any combination of :
423 product category
424 product category and problem code
425 (where prod category = all product categories that belong to the
426 product category set held in the CS_SR_DEFAULT_CATEGORY_SET profile option
427 #9) Check if the resolution code is mapped to any combination of :
428 problem code
429 #10) Check if the resolution code is an active resolution code or not. Since this is
430 the most basic check, it will be executed before the other validation checks
431 */
432
433 /* Validation Check #10 */
434 OPEN cs_sr_active_rc_csr;
435 FETCH cs_sr_active_rc_csr into l_resolution_code_meaning;
436 IF (cs_sr_active_rc_csr%NOTFOUND) THEN
437 RAISE FND_API.G_EXC_ERROR;
438 END IF;
439
440
441 /* Validation Check #1 */
442 OPEN cs_sr_unmapped_rc_csr;
443 FETCH cs_sr_unmapped_rc_csr INTO l_resolution_code_meaning;
444 IF (cs_sr_unmapped_rc_csr%NOTFOUND) THEN
445 /*
446 Now, check if the product_category_id input parameter contained some value
447
448 If yes,
449 **) open cs_sr_rc_cat_srtype cursor - this cursor checks all mappings containing the
450 sr type and/or prod and/or prod category and/or problem code
451 **) If the above cursor did not find a hit, then, open cs_sr_rc_catset_prod cursor
452 -- this cursor checks mappings containing the product(inventory item)
453 and/or problem code
454 **) If the above cursor did not find a hit, then, open cs_sr_rc_cat_prodcat cursor
455 -- this cursor checks mappings containing the product category
456 and/or problem code
457 **) If the above cursor did not find a hit, then, open cs_sr_rc_catset_probc cursor
458 -- this cursor checks mappings containing the problem code
459 Else,
460 **) open cs_sr_rc_catset_srtype cursor - this cursor checks all mappings containing the
461 sr type and/or prod and/or prod category and/or problem code
462 where prod category = all categories belonging to category set held
463 in CS_SR_DEFAULT_CATEGORY_SET profile option;
464 **) If the above cursor did not find a hit, then, open cs_sr_rc_catset_prod cursor
465 -- this cursor checks mappings containing the product(inventory item)
466 and/or problem code
467 **) If the above cursor did not find a hit, then, open cs_sr_rc_catset_prodcat cursor
468 -- this cursor checks mappings containing the product category
469 and/or problem code
470 where prod category = all categories belonging to category set held
471 in CS_SR_DEFAULT_CATEGORY_SET profile option;
472 **) If the above cursor did not find a hit, then, open cs_sr_rc_catset_probc cursor
473 -- this cursor checks mappings containing the problem code
474 */
475 /* Roopa - Begin - fix for bug 3335668 */
476 /* Replaced the following IF for the commented IF condition */
477 IF (l_category_id <> 0) THEN
478 /*
479 IF (l_category_id is not null OR
480 l_category_id <> FND_API.G_MISS_NUM) THEN
481 */
482 /* Roopa - End - fix for bug 3335668 */
483
484 /* Validation Check #2 */
485 OPEN cs_sr_rc_cat_srtype;
486 FETCH cs_sr_rc_cat_srtype into l_resolution_code_meaning;
487
488 IF (cs_sr_rc_cat_srtype%NOTFOUND) THEN
489 /* Validation Check #3 */
490 OPEN cs_sr_rc_catset_prod;
491 FETCH cs_sr_rc_catset_prod into l_resolution_code_meaning;
492
493 IF (cs_sr_rc_catset_prod%NOTFOUND) THEN
494 /* Validation Check #4 */
495 OPEN cs_sr_rc_cat_prodcat;
496 FETCH cs_sr_rc_cat_prodcat into l_resolution_code_meaning;
497 IF (cs_sr_rc_cat_prodcat%NOTFOUND) THEN
498 /* Validation Check #5 */
499 OPEN cs_sr_rc_catset_probc;
500 FETCH cs_sr_rc_catset_probc into l_resolution_code_meaning;
501 IF (cs_sr_rc_catset_probc%NOTFOUND) THEN
502 RAISE FND_API.G_EXC_ERROR;
503 END IF;
504 END IF;
505 END IF;
506 END IF;
507
508 ELSE
509 /* Validation Check #6 */
510 OPEN cs_sr_rc_catset_srtype;
511 FETCH cs_sr_rc_catset_srtype into l_resolution_code_meaning;
512
513 IF (cs_sr_rc_catset_srtype%NOTFOUND) THEN
514 /* Validation Check #7 */
515 OPEN cs_sr_rc_catset_prod;
516 FETCH cs_sr_rc_catset_prod into l_resolution_code_meaning;
517
518 IF (cs_sr_rc_catset_prod%NOTFOUND) THEN
519 /* Validation Check #8 */
520 OPEN cs_sr_rc_catset_prodcat;
521 FETCH cs_sr_rc_catset_prodcat into l_resolution_code_meaning;
522 IF (cs_sr_rc_catset_prodcat%NOTFOUND) THEN
523 /* Validation Check #9 */
524 OPEN cs_sr_rc_catset_probc;
525 FETCH cs_sr_rc_catset_probc into l_resolution_code_meaning;
526 IF (cs_sr_rc_catset_probc%NOTFOUND) THEN
527 RAISE FND_API.G_EXC_ERROR;
528 END IF;
529 END IF;
530 END IF;
531 END IF;
532 END IF;
533 END IF;
534
535 IF cs_sr_unmapped_rc_csr%isopen THEN
536 CLOSE cs_sr_unmapped_rc_csr;
537 END IF;
538 IF cs_sr_rc_cat_srtype%isopen THEN
539 CLOSE cs_sr_rc_cat_srtype;
540 END IF;
541 IF cs_sr_rc_catset_prod%isopen THEN
542 CLOSE cs_sr_rc_catset_prod;
543 END IF;
544 IF cs_sr_rc_cat_prodcat%isopen THEN
545 CLOSE cs_sr_rc_cat_prodcat;
546 END IF;
547 IF cs_sr_rc_catset_probc%isopen THEN
548 CLOSE cs_sr_rc_catset_probc;
549 END IF;
550 IF cs_sr_rc_catset_srtype%isopen THEN
551 CLOSE cs_sr_rc_catset_srtype;
552 END IF;
553 IF cs_sr_rc_catset_prodcat%isopen THEN
554 CLOSE cs_sr_rc_catset_prodcat;
555 END IF;
556
557
558 EXCEPTION
559 WHEN FND_API.G_EXC_ERROR THEN
560 IF cs_sr_unmapped_rc_csr%isopen THEN
561 CLOSE cs_sr_unmapped_rc_csr;
562 END IF;
563 IF cs_sr_rc_cat_srtype%isopen THEN
564 CLOSE cs_sr_rc_cat_srtype;
565 END IF;
566 IF cs_sr_rc_catset_prod%isopen THEN
567 CLOSE cs_sr_rc_catset_prod;
568 END IF;
569 IF cs_sr_rc_cat_prodcat%isopen THEN
570 CLOSE cs_sr_rc_cat_prodcat;
571 END IF;
572 IF cs_sr_rc_catset_probc%isopen THEN
573 CLOSE cs_sr_rc_catset_probc;
574 END IF;
575 IF cs_sr_rc_catset_srtype%isopen THEN
576 CLOSE cs_sr_rc_catset_srtype;
577 END IF;
578 IF cs_sr_rc_catset_prodcat%isopen THEN
579 CLOSE cs_sr_rc_catset_prodcat;
580 END IF;
581
582 x_return_status := FND_API.G_RET_STS_ERROR;
583 FND_MSG_PUB.Count_And_Get
584 ( p_count => x_msg_count,
585 p_data => x_msg_data
586 );
587 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
588 IF cs_sr_unmapped_rc_csr%isopen THEN
589 CLOSE cs_sr_unmapped_rc_csr;
590 END IF;
591 IF cs_sr_rc_cat_srtype%isopen THEN
592 CLOSE cs_sr_rc_cat_srtype;
593 END IF;
594 IF cs_sr_rc_catset_prod%isopen THEN
595 CLOSE cs_sr_rc_catset_prod;
596 END IF;
597 IF cs_sr_rc_cat_prodcat%isopen THEN
598 CLOSE cs_sr_rc_cat_prodcat;
599 END IF;
600 IF cs_sr_rc_catset_probc%isopen THEN
601 CLOSE cs_sr_rc_catset_probc;
602 END IF;
603 IF cs_sr_rc_catset_srtype%isopen THEN
604 CLOSE cs_sr_rc_catset_srtype;
605 END IF;
606 IF cs_sr_rc_catset_prodcat%isopen THEN
607 CLOSE cs_sr_rc_catset_prodcat;
608 END IF;
609
610 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
611 FND_MSG_PUB.Count_And_Get
612 ( p_count => x_msg_count,
613 p_data => x_msg_data
614 );
615 WHEN OTHERS THEN
616 IF cs_sr_unmapped_rc_csr%isopen THEN
617 CLOSE cs_sr_unmapped_rc_csr;
618 END IF;
619 IF cs_sr_rc_cat_srtype%isopen THEN
620 CLOSE cs_sr_rc_cat_srtype;
621 END IF;
622 IF cs_sr_rc_catset_prod%isopen THEN
623 CLOSE cs_sr_rc_catset_prod;
624 END IF;
625 IF cs_sr_rc_cat_prodcat%isopen THEN
626 CLOSE cs_sr_rc_cat_prodcat;
627 END IF;
628 IF cs_sr_rc_catset_probc%isopen THEN
629 CLOSE cs_sr_rc_catset_probc;
630 END IF;
631 IF cs_sr_rc_catset_srtype%isopen THEN
632 CLOSE cs_sr_rc_catset_srtype;
633 END IF;
634 IF cs_sr_rc_catset_prodcat%isopen THEN
635 CLOSE cs_sr_rc_catset_prodcat;
636 END IF;
637
638 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
639 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
640 FND_MSG_PUB.Add_Exc_Msg('CS_SR_RES_CODE_MAPPING_PKG', l_api_name);
641 END IF;
642 FND_MSG_PUB.Count_And_Get
643 ( p_count => x_msg_count,
644 p_data => x_msg_data
645 );
646 END; -- End of procedure VALIDATE_RESOLUTION_CODE()
647
648
649
650 PROCEDURE CREATE_MAPPING_RULES
651 ( p_api_version IN NUMBER,
652 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
653 p_commit IN VARCHAR2 := FND_API.G_FALSE,
654 p_rescode_map_criteria_rec IN rescode_map_criteria_rec,
655 p_resolution_codes_tbl IN resolution_codes_tbl_type,
656 x_return_status OUT NOCOPY VARCHAR2,
657 x_msg_count OUT NOCOPY NUMBER,
658 x_msg_data OUT NOCOPY VARCHAR2,
659 x_resolution_map_id OUT NOCOPY NUMBER
660 ) IS
661
662 l_api_version CONSTANT NUMBER := 1.0;
663 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_MAPPING_RULES';
664 l_errname varchar2(60);
665 l_errmsg varchar2(2000);
666 l_errstack varchar2(4000);
667 l_msg_count NUMBER;
668 l_return_status VARCHAR2(1);
669
670 l_product_category_set VARCHAR2(240);
671 l_problem_code VARCHAR2(30);
672 l_resolution_code VARCHAR2(30);
673
674 l_service_request_type_id NUMBER;
675 l_inventory_item_id NUMBER;
676 l_organization_id NUMBER;
677 l_product_category_id NUMBER;
678 l_resolution_map_id NUMBER;
679 l_resolution_map_detail_id NUMBER;
680
681 l_start_date_active DATE;
682 l_end_date_active DATE;
683
684 l_current_date DATE :=sysdate;
685 l_created_by NUMBER :=fnd_global.user_id;
686 l_login NUMBER :=fnd_global.login_id;
687 l_row_id VARCHAR2(100);
688 l_temp NUMBER;
689
690 l_res_code_index BINARY_INTEGER;
691
692 CURSOR c_sr_criteria_exists_csr IS
693 SELECT resolution_map_id,start_date_active, end_date_active from cs_sr_res_code_mapping
694 WHERE incident_type_id = l_service_request_type_id
695 AND category_id = l_product_category_id
696 AND inventory_item_id = l_inventory_item_id
697 AND organization_id = l_organization_id
698 AND problem_code = l_resolution_code
699 AND (start_date_active is null or trunc(start_date_active) <= trunc(sysdate)) and
700 (end_date_active is null or trunc(end_date_active) >= trunc(sysdate));
701
702 c_sr_criteria_exists_rec c_sr_criteria_exists_csr%ROWTYPE;
703
704 CURSOR c_sr_prod_cat_valid_csr IS
705 SELECT category_id from mtl_category_set_valid_cats
706 WHERE category_set_id = to_number(l_product_category_set)
707 AND category_id = l_product_category_id;
708
709 CURSOR c_sr_resolution_code_valid_csr IS
710 SELECT lookup_code from cs_lookups
711 WHERE lookup_code = l_resolution_code
712 AND lookup_type = 'REQUEST_RESOLUTION_CODE'and
713 (start_date_active is null or
714 trunc(start_date_active) <= trunc(sysdate)) and
715 (end_date_active is null or
716 trunc(end_date_active) >= trunc(sysdate));
717
718 CURSOR c_sr_res_code_map_exists_csr IS
719 SELECT resolution_map_detail_id,start_date_active, end_date_active from cs_sr_res_code_mapping_detail
720 WHERE resolution_map_id = l_resolution_map_id
721 AND resolution_code = l_resolution_code
722 AND (start_date_active is null or trunc(start_date_active) <= trunc(sysdate)) and
723 (end_date_active is null or trunc(end_date_active) >= trunc(sysdate));
724
725 c_sr_res_code_map_exists_rec c_sr_res_code_map_exists_csr%ROWTYPE;
726
727 BEGIN
728
729 SAVEPOINT create_mapping_rules;
730 x_return_status := fnd_api.g_ret_sts_success;
731
732 IF fnd_api.to_boolean (p_init_msg_list)
733 THEN
734 fnd_msg_pub.initialize;
735 END IF;
736
737 /* First, we create the search criteria
738 Then, we create the resolution code mappings to this search criteria */
739
740 /* The following validation checks will be executed on the search criteria:
741 #1) the product categroy and product cannot be part of the same search criteria
742 #2) all search criteria attributes should NOT be null in the search criteria
743 #3) Organization Id cannot be null if product is not null
744 #4) the search criteria should not already be present in the cs_sr_res_code_mapping table
745 #5) the product category of the new search criteria, if passed, should belong to the default category set
746 whose value is held in the profile option - CS_SR_DEFAULT_CATEGORY_SET
747 #6) the start date of the new search criteria should NOT be greater than the end date
748 */
749
750 /* Validation check #1 */
751 IF ( p_rescode_map_criteria_rec.product_category_id is not null and
752 p_rescode_map_criteria_rec.inventory_item_id is not null) THEN
753 RAISE fnd_api.g_exc_unexpected_error;
754 END IF;
755
756 /* Validation check #2 */
757 IF (p_rescode_map_criteria_rec.product_category_id is null and
758 p_rescode_map_criteria_rec.inventory_item_id is null and
759 p_rescode_map_criteria_rec.service_request_type_id is null and
760 p_rescode_map_criteria_rec.problem_code is null) THEN
761 RAISE fnd_api.g_exc_unexpected_error;
762 END IF;
763
764 /* Validation check #3 */
765 IF (p_rescode_map_criteria_rec.inventory_item_id is not null and
766 p_rescode_map_criteria_rec.organization_id is null) THEN
767 RAISE fnd_api.g_exc_unexpected_error;
768 END IF;
769
770
771 l_start_date_active := nvl(p_rescode_map_criteria_rec.start_date_active, sysdate);
772 l_end_date_active := nvl(p_rescode_map_criteria_rec.end_date_active, sysdate);
773
774 l_product_category_id := nvl(p_rescode_map_criteria_rec.product_category_id,0);
775 l_inventory_item_id := nvl(p_rescode_map_criteria_rec.inventory_item_id,0);
776 l_organization_id := nvl(p_rescode_map_criteria_rec.organization_id,0);
777 l_service_request_type_id := nvl(p_rescode_map_criteria_rec.service_request_type_id,0);
778 l_problem_code := nvl(p_rescode_map_criteria_rec.problem_code,'');
779
780 /* Validation check #4 */
781 OPEN c_sr_criteria_exists_csr;
782 FETCH c_sr_criteria_exists_csr into c_sr_criteria_exists_rec;
783 IF (c_sr_criteria_exists_csr%FOUND) THEN
784 RAISE fnd_api.g_exc_unexpected_error;
785 END IF;
786 CLOSE c_sr_criteria_exists_csr;
787
788 /* Validation check #5 */
789 IF(l_product_category_id <> 0 AND
790 FND_PROFILE.Value('CS_SR_DEFAULT_CATEGORY_SET') is not null) THEN
791 /* If the input category does not belong to the default category set, throw an exception */
792 l_product_category_set := FND_PROFILE.Value('CS_SR_DEFAULT_CATEGORY_SET');
793 OPEN c_sr_prod_cat_valid_csr;
794 FETCH c_sr_prod_cat_valid_csr into l_temp;
795 IF (c_sr_prod_cat_valid_csr%NOTFOUND) THEN
796 RAISE fnd_api.g_exc_unexpected_error;
797 END IF;
798 CLOSE c_sr_prod_cat_valid_csr;
799 END IF;
800
801
802 /* Validation check #6 */
803 /* start date cannot be greater then end date */
804 IF(l_start_date_active is not null AND l_end_date_active is not null AND
805 l_start_date_active >= l_end_date_active) THEN
806 RAISE fnd_api.g_exc_unexpected_error;
807 END IF;
808
809 /* All validations have passed for the search criteria. Hence we can create a search criteria record in
810 cs_sr_res_code_MAPPING table */
811 CS_SR_RESOLUTION_CODE_MAP_PKG.INSERT_ROW (
812 PX_RESOLUTION_MAP_ID => l_resolution_map_id,
813 P_INCIDENT_TYPE_ID => l_service_request_type_id,
814 P_INVENTORY_ITEM_ID => l_inventory_item_id,
815 P_ORGANIZATION_ID => l_organization_id,
816 P_CATEGORY_ID => l_product_category_id,
817 P_PROBLEM_CODE => l_problem_code,
818 P_START_DATE_ACTIVE => l_start_date_active,
819 P_END_DATE_ACTIVE => l_end_date_active,
820 P_OBJECT_VERSION_NUMBER => null,
821 P_ATTRIBUTE1 => null,
822 P_ATTRIBUTE2 => null,
823 P_ATTRIBUTE3 => null,
824 P_ATTRIBUTE4 => null,
825 P_ATTRIBUTE5 => null,
826 P_ATTRIBUTE6 => null,
827 P_ATTRIBUTE7 => null,
828 P_ATTRIBUTE8 => null,
829 P_ATTRIBUTE9 => null,
830 P_ATTRIBUTE10 => null,
831 P_ATTRIBUTE11 => null,
832 P_ATTRIBUTE12 => null,
833 P_ATTRIBUTE13 => null,
834 P_ATTRIBUTE14 => null,
835 P_ATTRIBUTE15 => null,
836 P_ATTRIBUTE_CATEGORY => null,
837 P_CREATION_DATE => l_current_date,
838 P_CREATED_BY => l_created_by,
839 P_LAST_UPDATE_DATE => l_current_date,
840 P_LAST_UPDATED_BY => l_created_by,
841 P_LAST_UPDATE_LOGIN => l_login,
842 X_RETURN_STATUS => l_return_status,
843 X_MSG_COUNT => l_msg_count,
844 X_MSG_DATA => l_errmsg
845 );
846 IF(l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
847 RAISE fnd_api.g_exc_unexpected_error;
848 END IF;
849
850 /* Now, we need to create the actual resolution code -> search criteria mapping details in
851 CS_SR_RES_CODE_MAPPING_DETAIL table */
852
853 /* First, the following validation checks :
854 #1) the resolution code should be a CS lookup code with lookup type = 'REQUEST_RESOLUTION_CODE'
855 #2) the resolution code mapping should not already be present. Though this is creation API, it is
856 still possible that the same resolution code is passed twice in the prob code mapping table parameter
857 #3) the start date of the resolution code mapping should NOT be greater than the mapping end date
858 */
859 l_res_code_index := p_resolution_codes_tbl.FIRST;
860 WHILE l_res_code_index IS NOT NULL LOOP
861
862 IF ((p_resolution_codes_tbl(l_res_code_index).resolution_code IS NOT NULL) AND
863 (p_resolution_codes_tbl(l_res_code_index).resolution_code <> FND_API.G_MISS_CHAR)) THEN
864
865 l_resolution_code := p_resolution_codes_tbl(l_res_code_index).resolution_code;
866 l_start_date_active := p_resolution_codes_tbl(l_res_code_index).start_date_active;
867 l_end_date_active := p_resolution_codes_tbl(l_res_code_index).end_date_active;
868
869 /* Validation check #1 */
870 OPEN c_sr_resolution_code_valid_csr;
871 FETCH c_sr_resolution_code_valid_csr INTO l_resolution_code;
872 IF(c_sr_resolution_code_valid_csr%NOTFOUND) THEN
873 RAISE fnd_api.g_exc_unexpected_error;
874 END IF;
875 CLOSE c_sr_resolution_code_valid_csr;
876
877
878 /* Validation check #2 */
879 OPEN c_sr_res_code_map_exists_csr;
880 FETCH c_sr_res_code_map_exists_csr into c_sr_res_code_map_exists_rec;
881 IF(c_sr_res_code_map_exists_csr%FOUND) THEN
882 RAISE fnd_api.g_exc_unexpected_error;
883 END IF;
884 CLOSE c_sr_res_code_map_exists_csr;
885
886 /* Validation check #3 */
887 /* start date cannot be greater then end date */
888 IF(l_start_date_active is not null AND l_end_date_active is not null AND
889 l_start_date_active >= l_end_date_active) THEN
890 RAISE fnd_api.g_exc_unexpected_error;
891 END IF;
892
893 /* We can create the resolution code mapping in cs_sr_res_code_MAPPING_DETAIL table now */
894 CS_SR_RES_CODE_MAP_DETAIL_PKG.INSERT_ROW (
895 PX_RESOLUTION_MAP_DETAIL_ID => l_resolution_map_detail_id,
896 P_RESOLUTION_MAP_ID => l_resolution_map_id,
897 P_INCIDENT_TYPE_ID => l_service_request_type_id,
898 P_INVENTORY_ITEM_ID => l_inventory_item_id,
899 P_ORGANIZATION_ID => l_organization_id,
900 P_CATEGORY_ID => l_product_category_id,
901 P_PROBLEM_CODE => l_problem_code,
902 P_MAP_START_DATE_ACTIVE => l_start_date_active,
903 P_MAP_END_DATE_ACTIVE => l_end_date_active,
904 P_RESOLUTION_CODE => l_problem_code,
905 P_START_DATE_ACTIVE => l_start_date_active,
906 P_END_DATE_ACTIVE => l_end_date_active,
907 P_OBJECT_VERSION_NUMBER => null,
908 P_ATTRIBUTE1 => null,
909 P_ATTRIBUTE2 => null,
910 P_ATTRIBUTE3 => null,
911 P_ATTRIBUTE4 => null,
912 P_ATTRIBUTE5 => null,
913 P_ATTRIBUTE6 => null,
914 P_ATTRIBUTE7 => null,
915 P_ATTRIBUTE8 => null,
916 P_ATTRIBUTE9 => null,
917 P_ATTRIBUTE10 => null,
918 P_ATTRIBUTE11 => null,
919 P_ATTRIBUTE12 => null,
920 P_ATTRIBUTE13 => null,
921 P_ATTRIBUTE14 => null,
922 P_ATTRIBUTE15 => null,
923 P_ATTRIBUTE_CATEGORY => null,
924 P_CREATION_DATE => l_current_date,
925 P_CREATED_BY => l_created_by,
926 P_LAST_UPDATE_DATE => l_current_date,
927 P_LAST_UPDATED_BY => l_created_by,
928 P_LAST_UPDATE_LOGIN => l_login,
929 X_RETURN_STATUS => l_return_status,
930 X_MSG_COUNT => l_msg_count,
931 X_MSG_DATA => l_errmsg
932 );
933
934 END IF;
935 l_res_code_index := p_resolution_codes_tbl.NEXT(l_res_code_index);
936
937 END LOOP;
938 x_resolution_map_id := l_resolution_map_id;
939
940
941 EXCEPTION
942 WHEN FND_API.G_EXC_ERROR THEN
943 IF c_sr_criteria_exists_csr%isopen THEN
944 CLOSE c_sr_criteria_exists_csr;
945 END IF;
946 IF c_sr_prod_cat_valid_csr%isopen THEN
947 CLOSE c_sr_prod_cat_valid_csr;
948 END IF;
949 IF c_sr_resolution_code_valid_csr%isopen THEN
950 CLOSE c_sr_resolution_code_valid_csr;
951 END IF;
952 IF c_sr_res_code_map_exists_csr%isopen THEN
953 CLOSE c_sr_res_code_map_exists_csr;
954 END IF;
955
956 ROLLBACK TO create_mapping_rules;
957 x_return_status := FND_API.G_RET_STS_ERROR;
958 FND_MSG_PUB.Count_And_Get
959 ( p_count => x_msg_count,
960 p_data => x_msg_data
961 );
962 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
963 IF c_sr_criteria_exists_csr%isopen THEN
964 CLOSE c_sr_criteria_exists_csr;
965 END IF;
966 IF c_sr_prod_cat_valid_csr%isopen THEN
967 CLOSE c_sr_prod_cat_valid_csr;
968 END IF;
969 IF c_sr_resolution_code_valid_csr%isopen THEN
970 CLOSE c_sr_resolution_code_valid_csr;
971 END IF;
972 IF c_sr_res_code_map_exists_csr%isopen THEN
973 CLOSE c_sr_res_code_map_exists_csr;
974 END IF;
975
976 ROLLBACK TO create_mapping_rules;
977 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
978 FND_MSG_PUB.Count_And_Get
979 ( p_count => x_msg_count,
980 p_data => x_msg_data
981 );
982 WHEN OTHERS THEN
983 IF c_sr_criteria_exists_csr%isopen THEN
984 CLOSE c_sr_criteria_exists_csr;
985 END IF;
986 IF c_sr_prod_cat_valid_csr%isopen THEN
987 CLOSE c_sr_prod_cat_valid_csr;
988 END IF;
989 IF c_sr_resolution_code_valid_csr%isopen THEN
990 CLOSE c_sr_resolution_code_valid_csr;
991 END IF;
992 IF c_sr_res_code_map_exists_csr%isopen THEN
993 CLOSE c_sr_res_code_map_exists_csr;
994 END IF;
995
996 ROLLBACK TO create_mapping_rules;
997 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
998 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
999 FND_MSG_PUB.Add_Exc_Msg('CS_SR_RES_CODE_MAPPING_PKG', l_api_name);
1000 END IF;
1001 FND_MSG_PUB.Count_And_Get
1002 ( p_count => x_msg_count,
1003 p_data => x_msg_data
1004 );
1005
1006
1007 END;
1008
1009
1010
1011 PROCEDURE UPDATE_MAPPING_RULES
1012 ( p_api_version IN NUMBER,
1013 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1014 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1015 p_rescode_map_criteria_rec IN rescode_map_criteria_rec,
1016 p_resolution_codes_tbl IN resolution_codes_tbl_type,
1017 x_return_status OUT NOCOPY VARCHAR2,
1018 x_msg_count OUT NOCOPY NUMBER,
1019 x_msg_data OUT NOCOPY VARCHAR2
1020 ) IS
1021
1022 l_api_version CONSTANT NUMBER := 1.0;
1023 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_MAPPING_RULES';
1024 l_errname varchar2(60);
1025 l_errmsg varchar2(2000);
1026 l_errstack varchar2(4000);
1027 l_msg_count NUMBER;
1028 l_return_status VARCHAR2(1);
1029
1030 l_resolution_map_id NUMBER;
1031 l_resolution_map_detail_id NUMBER;
1032 l_resolution_code VARCHAR2(30);
1033 l_problem_code VARCHAR2(30);
1034
1035 l_service_request_type_id NUMBER;
1036 l_inventory_item_id NUMBER;
1037 l_organization_id NUMBER;
1038 l_product_category_id NUMBER;
1039
1040 l_start_date_active DATE;
1041 l_end_date_active DATE;
1042
1043 l_current_date DATE :=sysdate;
1044 l_created_by NUMBER :=fnd_global.user_id;
1045 l_login NUMBER :=fnd_global.login_id;
1046 l_row_id VARCHAR2(100);
1047
1048 l_res_code_index BINARY_INTEGER;
1049
1050
1051 CURSOR cs_sr_resmapid_crit_csr IS
1052 SELECT incident_type_id,category_id,inventory_item_id,problem_code,organization_id
1053 FROM CS_SR_RES_CODE_MAPPING
1054 WHERE resolution_map_id = l_resolution_map_id
1055 AND (start_date_active is null or trunc(start_date_active) <= trunc(sysdate)) and
1056 (end_date_active is null or trunc(end_date_active) >= trunc(sysdate));
1057
1058 cs_sr_resmapid_crit_rec cs_sr_resmapid_crit_csr%ROWTYPE;
1059
1060
1061
1062 CURSOR cs_sr_resmapid_exists_csr IS
1063 SELECT resolution_map_id,incident_type_id,category_id,inventory_item_id,problem_code,organization_id
1064 FROM CS_SR_RES_CODE_MAPPING
1065 WHERE resolution_map_id <> l_resolution_map_id AND
1066 incident_type_id = l_service_request_type_id AND
1067 category_id = l_product_category_id AND
1068 inventory_item_id = l_inventory_item_id AND
1069 organization_id = l_organization_id AND
1070 problem_code = l_problem_code AND
1071 (start_date_active is null or
1072 trunc(start_date_active) <= trunc(sysdate)) and
1073 (end_date_active is null or
1074 trunc(end_date_active) >= trunc(sysdate));
1075
1076
1077 CURSOR cs_sr_res_code_valid_csr IS
1078 SELECT lookup_code from cs_lookups a
1079 WHERE lookup_code = l_resolution_code
1080 AND lookup_type = 'REQUEST_RESOLUTION_CODE' and
1081 (start_date_active is null or
1082 trunc(start_date_active) <= trunc(sysdate)) and
1083 (end_date_active is null or
1084 trunc(end_date_active) >= trunc(sysdate));
1085
1086 CURSOR cs_sr_rescode_mapped_csr IS
1087 SELECT resolution_code,start_date_active,end_date_active from CS_SR_RES_CODE_MAPPING_DETAIL
1088 WHERE resolution_map_id = l_resolution_map_id and
1089 resolution_map_detail_id <> l_resolution_map_detail_id and
1090 resolution_code = l_resolution_code and
1091 (start_date_active is null or
1092 trunc(start_date_active) <= trunc(sysdate)) and
1093 (end_date_active is null or
1094 trunc(end_date_active) >= trunc(sysdate));
1095
1096 cs_sr_rescode_mapped_rec cs_sr_rescode_mapped_csr%ROWTYPE;
1097
1098
1099 BEGIN
1100
1101 SAVEPOINT update_mapping_rules;
1102 x_return_status := fnd_api.g_ret_sts_success;
1103
1104 IF fnd_api.to_boolean (p_init_msg_list) THEN
1105 fnd_msg_pub.initialize;
1106 END IF;
1107
1108 l_resolution_map_id := p_rescode_map_criteria_rec.resolution_map_id;
1109 l_start_date_active := p_rescode_map_criteria_rec.start_date_active;
1110 l_end_date_active := p_rescode_map_criteria_rec.end_date_active;
1111 l_service_request_type_id := nvl(p_rescode_map_criteria_rec.service_request_type_id, 0);
1112 l_product_category_id := nvl(p_rescode_map_criteria_rec.product_category_id, 0);
1113 l_inventory_item_id := nvl(p_rescode_map_criteria_rec.inventory_item_id, 0);
1114 l_organization_id := nvl(p_rescode_map_criteria_rec.organization_id,0);
1115 l_problem_code := nvl(p_rescode_map_criteria_rec.problem_code,'');
1116
1117 /* Validation checks are :
1118 #1) resolution map id should not be null
1119 #2) All search attributes should not be null
1120 #3) product and product category cannot be part of the same search criteria
1121 #4) end date of the search criteria should not be lesser than start date
1122 #5) a search criteria should not exist which is exactly similar to the current search criteria
1123 #6) problem code being mapped to the search criteria should be an active CS lookup code of lookup type = 'REQUEST_RESOLUTION_CODE'
1124 #7) a duplicate resolution code mapping should not already exists for the current search criteria
1125 */
1126
1127
1128 /* Validation check #1 */
1129 IF (l_resolution_map_id is null) THEN
1130 RAISE fnd_api.g_exc_unexpected_error;
1131 END IF;
1132
1133
1134 /* Validation check #2 */
1135 IF (l_service_request_type_id = 0 AND
1136 l_product_category_id = 0 AND
1137 l_inventory_item_id = 0) THEN
1138 RAISE fnd_api.g_exc_unexpected_error;
1139 END IF;
1140
1141 /* Validation check #3 */
1142 IF (l_product_category_id <> 0 AND
1143 l_inventory_item_id <> 0) THEN
1144 RAISE fnd_api.g_exc_unexpected_error;
1145 END IF;
1146
1147
1148 /* Validation check #4 */
1149 IF (l_start_date_active is not null AND
1150 l_end_date_active is not null AND
1151 l_start_date_active >= l_end_date_active) THEN
1152 RAISE fnd_api.g_exc_unexpected_error;
1153 END IF;
1154
1155 /* Validation check #5 */
1156 OPEN cs_sr_resmapid_exists_csr;
1157 IF(cs_sr_resmapid_exists_csr%FOUND) THEN
1158 RAISE fnd_api.g_exc_unexpected_error;
1159 END IF;
1160 CLOSE cs_sr_resmapid_exists_csr;
1161
1162
1163 l_res_code_index := p_resolution_codes_tbl.FIRST;
1164 WHILE l_res_code_index IS NOT NULL LOOP
1165
1166 IF ((p_resolution_codes_tbl(l_res_code_index).resolution_code IS NOT NULL) AND
1167 (p_resolution_codes_tbl(l_res_code_index).resolution_code <> FND_API.G_MISS_CHAR)) THEN
1168
1169 l_resolution_code := p_resolution_codes_tbl(l_res_code_index).resolution_code;
1170 l_start_date_active := p_resolution_codes_tbl(l_res_code_index).start_date_active;
1171 l_end_date_active := p_resolution_codes_tbl(l_res_code_index).end_date_active;
1172 l_resolution_map_detail_id := p_resolution_codes_tbl(l_res_code_index).resolution_map_detail_id;
1173
1174
1175 /* Validation check #6 */
1176 OPEN cs_sr_res_code_valid_csr;
1177 IF(cs_sr_res_code_valid_csr%NOTFOUND) THEN
1178 RAISE fnd_api.g_exc_unexpected_error;
1179 END IF;
1180 CLOSE cs_sr_res_code_valid_csr;
1181
1182 /* Validation check #7 */
1183 OPEN cs_sr_rescode_mapped_csr;
1184 FETCH cs_sr_rescode_mapped_csr into cs_sr_rescode_mapped_rec;
1185 IF(cs_sr_rescode_mapped_csr%FOUND) THEN
1186 RAISE fnd_api.g_exc_unexpected_error;
1187 END IF;
1188 CLOSE cs_sr_rescode_mapped_csr;
1189
1190 END IF;
1191
1192
1193 IF(p_resolution_codes_tbl(l_res_code_index).resolution_map_detail_id is null) THEN
1194
1195 CS_SR_RES_CODE_MAP_DETAIL_PKG.INSERT_ROW (
1196 PX_RESOLUTION_MAP_DETAIL_ID => l_resolution_map_detail_id,
1197 P_RESOLUTION_MAP_ID => l_resolution_map_id,
1198 P_INCIDENT_TYPE_ID => l_service_request_type_id,
1199 P_INVENTORY_ITEM_ID => l_inventory_item_id,
1200 P_ORGANIZATION_ID => l_organization_id,
1201 P_CATEGORY_ID => l_product_category_id,
1202 P_PROBLEM_CODE => l_problem_code,
1203 P_MAP_START_DATE_ACTIVE => l_start_date_active,
1204 P_MAP_END_DATE_ACTIVE => l_end_date_active,
1205 P_RESOLUTION_CODE => l_resolution_code,
1206 P_START_DATE_ACTIVE => l_start_date_active,
1207 P_END_DATE_ACTIVE => l_end_date_active,
1208 P_OBJECT_VERSION_NUMBER => null,
1209 P_ATTRIBUTE1 => null,
1210 P_ATTRIBUTE2 => null,
1211 P_ATTRIBUTE3 => null,
1212 P_ATTRIBUTE4 => null,
1213 P_ATTRIBUTE5 => null,
1214 P_ATTRIBUTE6 => null,
1215 P_ATTRIBUTE7 => null,
1216 P_ATTRIBUTE8 => null,
1217 P_ATTRIBUTE9 => null,
1218 P_ATTRIBUTE10 => null,
1219 P_ATTRIBUTE11 => null,
1220 P_ATTRIBUTE12 => null,
1221 P_ATTRIBUTE13 => null,
1222 P_ATTRIBUTE14 => null,
1223 P_ATTRIBUTE15 => null,
1224 P_ATTRIBUTE_CATEGORY => null,
1225 P_CREATION_DATE => l_current_date,
1226 P_CREATED_BY => l_created_by,
1227 P_LAST_UPDATE_DATE => l_current_date,
1228 P_LAST_UPDATED_BY => l_created_by,
1229 P_LAST_UPDATE_LOGIN => l_login,
1230 X_RETURN_STATUS => l_return_status,
1231 X_MSG_COUNT => l_msg_count,
1232 X_MSG_DATA => l_errmsg
1233 );
1234 ELSE
1235
1236 CS_SR_RES_CODE_MAP_DETAIL_PKG.UPDATE_ROW (
1237 P_RESOLUTION_MAP_DETAIL_ID => l_resolution_map_detail_id,
1238 P_RESOLUTION_MAP_ID => l_resolution_map_id,
1239 P_INCIDENT_TYPE_ID => l_service_request_type_id,
1240 P_INVENTORY_ITEM_ID => l_inventory_item_id,
1241 P_ORGANIZATION_ID => l_organization_id,
1242 P_CATEGORY_ID => l_product_category_id,
1243 P_PROBLEM_CODE => l_problem_code,
1244 P_MAP_START_DATE_ACTIVE => l_start_date_active,
1245 P_MAP_END_DATE_ACTIVE => l_end_date_active,
1246 P_RESOLUTION_CODE => l_resolution_code,
1247 P_START_DATE_ACTIVE => l_start_date_active,
1248 P_END_DATE_ACTIVE => l_end_date_active,
1249 P_OBJECT_VERSION_NUMBER => null,
1250 P_ATTRIBUTE1 => null,
1251 P_ATTRIBUTE2 => null,
1252 P_ATTRIBUTE3 => null,
1253 P_ATTRIBUTE4 => null,
1254 P_ATTRIBUTE5 => null,
1255 P_ATTRIBUTE6 => null,
1256 P_ATTRIBUTE7 => null,
1257 P_ATTRIBUTE8 => null,
1258 P_ATTRIBUTE9 => null,
1259 P_ATTRIBUTE10 => null,
1260 P_ATTRIBUTE11 => null,
1261 P_ATTRIBUTE12 => null,
1262 P_ATTRIBUTE13 => null,
1263 P_ATTRIBUTE14 => null,
1264 P_ATTRIBUTE15 => null,
1265 P_ATTRIBUTE_CATEGORY => null,
1266 P_LAST_UPDATE_DATE => l_current_date,
1267 P_LAST_UPDATED_BY => l_created_by,
1268 P_LAST_UPDATE_LOGIN => l_login,
1269 X_RETURN_STATUS => l_return_status,
1270 X_MSG_COUNT => l_msg_count,
1271 X_MSG_DATA => l_errmsg
1272 );
1273 END IF;
1274 IF(l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1275 RAISE fnd_api.g_exc_unexpected_error;
1276 END IF;
1277
1278 l_res_code_index := p_resolution_codes_tbl.NEXT(l_res_code_index);
1279 END LOOP;
1280
1281 EXCEPTION
1282 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1283 IF cs_sr_resmapid_exists_csr%isopen THEN
1284 CLOSE cs_sr_resmapid_exists_csr;
1285 END IF;
1286 IF cs_sr_res_code_valid_csr%isopen THEN
1287 CLOSE cs_sr_res_code_valid_csr;
1288 END IF;
1289 IF cs_sr_rescode_mapped_csr%isopen THEN
1290 CLOSE cs_sr_rescode_mapped_csr;
1291 END IF;
1292 ROLLBACK TO update_mapping_rules;
1293 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1294 FND_MSG_PUB.Count_And_Get
1295 ( p_count => x_msg_count,
1296 p_data => x_msg_data
1297 );
1298 WHEN OTHERS THEN
1299 IF cs_sr_resmapid_exists_csr%isopen THEN
1300 CLOSE cs_sr_resmapid_exists_csr;
1301 END IF;
1302 IF cs_sr_res_code_valid_csr%isopen THEN
1303 CLOSE cs_sr_res_code_valid_csr;
1304 END IF;
1305 IF cs_sr_rescode_mapped_csr%isopen THEN
1306 CLOSE cs_sr_rescode_mapped_csr;
1307 END IF;
1308 ROLLBACK TO update_mapping_rules;
1309 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1310 FND_MSG_PUB.Count_And_Get
1311 ( p_count => x_msg_count,
1312 p_data => x_msg_data
1313 );
1314
1315 END;
1316
1317
1318 PROCEDURE PROPAGATE_MAP_CRITERIA_DATES
1319 ( p_api_version IN NUMBER,
1320 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1321 x_return_status OUT NOCOPY VARCHAR2,
1322 x_msg_count OUT NOCOPY NUMBER,
1323 x_msg_data OUT NOCOPY VARCHAR2
1324 ) IS
1325
1326 l_api_version CONSTANT NUMBER := 1.0;
1327 l_api_name CONSTANT VARCHAR2(30) := 'PROPAGATE_MAP_CRITERIA_DATES';
1328 l_errname varchar2(60);
1329 l_errmsg varchar2(2000);
1330 l_errstack varchar2(4000);
1331 l_msg_count NUMBER;
1332 l_return_status VARCHAR2(1);
1333
1334 l_resolution_map_id NUMBER;
1335 l_resolution_map_detail_id NUMBER;
1336 l_resolution_code VARCHAR2(30);
1337 l_problem_code VARCHAR2(30);
1338
1339 l_service_request_type_id NUMBER;
1340 l_inventory_item_id NUMBER;
1341 l_organization_id NUMBER;
1342 l_product_category_id NUMBER;
1343
1344 l_start_date_active DATE;
1345 l_end_date_active DATE;
1346
1347 l_current_date DATE :=sysdate;
1348 l_created_by NUMBER :=fnd_global.user_id;
1349 l_login NUMBER :=fnd_global.login_id;
1350 l_row_id VARCHAR2(100);
1351
1352 l_res_code_index BINARY_INTEGER;
1353
1354 CURSOR cs_sr_resmapid_crit_csr IS
1355 SELECT resolution_map_id, start_date_active, end_date_active
1356 FROM CS_SR_RES_CODE_MAPPING;
1357 cs_sr_resmapid_crit_rec cs_sr_resmapid_crit_csr%ROWTYPE;
1358
1359
1360 CURSOR cs_sr_resmapid_rules_csr IS
1361 SELECT resolution_map_id, resolution_map_detail_id,
1362 map_start_date_active, map_end_date_active,
1363 start_date_active, end_date_active,
1364 incident_type_id, inventory_item_id, organization_id,
1365 category_id, problem_code, resolution_code
1366 FROM CS_SR_RES_CODE_MAPPING_DETAIL
1367 WHERE
1368 resolution_map_id = l_resolution_map_id;
1369 cs_sr_resmapid_rules_rec cs_sr_resmapid_rules_csr%ROWTYPE;
1370
1371
1372 BEGIN
1373 SAVEPOINT propagate_map_criteria_dates;
1374 x_return_status := fnd_api.g_ret_sts_success;
1375
1376 IF fnd_api.to_boolean (p_init_msg_list) THEN
1377 fnd_msg_pub.initialize;
1378 END IF;
1379
1380 OPEN cs_sr_resmapid_crit_csr;
1381 LOOP
1382
1383 FETCH cs_sr_resmapid_crit_csr into cs_sr_resmapid_crit_rec;
1384 EXIT WHEN cs_sr_resmapid_crit_csr%NOTFOUND;
1385
1386 l_resolution_map_id := cs_sr_resmapid_crit_rec.resolution_map_id;
1387 OPEN cs_sr_resmapid_rules_csr;
1388 FETCH cs_sr_resmapid_rules_csr INTO cs_sr_resmapid_rules_rec;
1389 CLOSE cs_sr_resmapid_rules_csr;
1390
1391 IF(cs_sr_resmapid_crit_rec.start_date_active is not null AND
1392 nvl(cs_sr_resmapid_rules_rec.map_start_date_active, cs_sr_resmapid_crit_rec.start_date_active+1) <> cs_sr_resmapid_crit_rec.start_date_active) THEN
1393
1394 update CS_SR_RES_CODE_MAPPING_DETAIL
1395 set
1396 map_start_date_active = cs_sr_resmapid_crit_rec.start_date_active,
1397 map_end_date_active = cs_sr_resmapid_crit_rec.end_date_active
1398 where
1399 resolution_map_id = l_resolution_map_id;
1400 ELSIF (cs_sr_resmapid_crit_rec.start_date_active is null AND
1401 cs_sr_resmapid_crit_rec.end_date_active is not null AND
1402 nvl(cs_sr_resmapid_rules_rec.map_end_date_active, cs_sr_resmapid_crit_rec.end_date_active+1) <> cs_sr_resmapid_crit_rec.end_date_active) THEN
1403
1404 update CS_SR_RES_CODE_MAPPING_DETAIL
1405 set
1406 map_start_date_active = cs_sr_resmapid_crit_rec.start_date_active,
1407 map_end_date_active = cs_sr_resmapid_crit_rec.end_date_active
1408 where
1409 resolution_map_id = l_resolution_map_id;
1410
1411 /*
1412 CS_SR_RES_CODE_MAP_DETAIL_PKG.UPDATE_ROW (
1413 P_RESOLUTION_MAP_DETAIL_ID => cs_sr_resmapid_rules_rec.resolution_map_detail_id,
1414 P_RESOLUTION_MAP_ID => l_resolution_map_id,
1415 P_INCIDENT_TYPE_ID => cs_sr_resmapid_rules_rec.incident_type_id,
1416 P_INVENTORY_ITEM_ID => cs_sr_resmapid_rules_rec.inventory_item_id,
1417 P_ORGANIZATION_ID => cs_sr_resmapid_rules_rec.organization_id,
1418 P_CATEGORY_ID => cs_sr_resmapid_rules_rec.category_id,
1419 P_PROBLEM_CODE => cs_sr_resmapid_rules_rec.problem_code,
1420 P_MAP_START_DATE_ACTIVE => cs_sr_resmapid_crit_rec.start_date_active,
1421 P_MAP_END_DATE_ACTIVE => cs_sr_resmapid_crit_rec.end_date_active,
1422 P_RESOLUTION_CODE => cs_sr_resmapid_rules_rec.resolution_code,
1423 P_START_DATE_ACTIVE => cs_sr_resmapid_rules_rec.start_date_active,
1424 P_END_DATE_ACTIVE => cs_sr_resmapid_rules_rec.end_date_active,
1425 P_OBJECT_VERSION_NUMBER => null,
1426 P_ATTRIBUTE1 => null,
1427 P_ATTRIBUTE2 => null,
1428 P_ATTRIBUTE3 => null,
1429 P_ATTRIBUTE4 => null,
1430 P_ATTRIBUTE5 => null,
1431 P_ATTRIBUTE6 => null,
1432 P_ATTRIBUTE7 => null,
1433 P_ATTRIBUTE8 => null,
1434 P_ATTRIBUTE9 => null,
1435 P_ATTRIBUTE10 => null,
1436 P_ATTRIBUTE11 => null,
1437 P_ATTRIBUTE12 => null,
1438 P_ATTRIBUTE13 => null,
1439 P_ATTRIBUTE14 => null,
1440 P_ATTRIBUTE15 => null,
1441 P_ATTRIBUTE_CATEGORY => null,
1442 P_LAST_UPDATE_DATE => l_current_date,
1443 P_LAST_UPDATED_BY => l_created_by,
1444 P_LAST_UPDATE_LOGIN => l_login,
1445 X_RETURN_STATUS => l_return_status,
1446 X_MSG_COUNT => l_msg_count,
1447 X_MSG_DATA => l_errmsg
1448 );
1449
1450 IF(l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1451 RAISE fnd_api.g_exc_unexpected_error;
1452 END IF;
1453 */
1454 END IF;
1455 END LOOP;
1456 CLOSE cs_sr_resmapid_crit_csr;
1457 commit;
1458
1459 EXCEPTION
1460 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1461 IF cs_sr_resmapid_crit_csr%isopen THEN
1462 CLOSE cs_sr_resmapid_crit_csr;
1463 END IF;
1464 IF cs_sr_resmapid_rules_csr%isopen THEN
1465 CLOSE cs_sr_resmapid_rules_csr;
1466 END IF;
1467 ROLLBACK TO propagate_map_criteria_dates;
1468 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1469 FND_MSG_PUB.Count_And_Get
1470 ( p_count => x_msg_count,
1471 p_data => x_msg_data
1472 );
1473 WHEN OTHERS THEN
1474 IF cs_sr_resmapid_crit_csr%isopen THEN
1475 CLOSE cs_sr_resmapid_crit_csr;
1476 END IF;
1477 IF cs_sr_resmapid_rules_csr%isopen THEN
1478 CLOSE cs_sr_resmapid_rules_csr;
1479 END IF;
1480 ROLLBACK TO propagate_map_criteria_dates;
1481 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1482 FND_MSG_PUB.Count_And_Get
1483 ( p_count => x_msg_count,
1484 p_data => x_msg_data
1485 );
1486
1487
1488
1489 END;
1490
1491
1492
1493
1494 -- Enter further code below as specified in the Package spec.
1495 END; -- Package Body CS_SR_RES_CODE_MAPPING_PKG
[Home] [Help]