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