[Home] [Help]
PACKAGE BODY: APPS.CS_INCIDENT_TYPES_PKG
Source
1 package body CS_INCIDENT_TYPES_PKG as
2 /* $Header: csviditb.pls 120.4 2006/06/28 01:33:33 klou ship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out NOCOPY VARCHAR2,
5 X_INCIDENT_TYPE_ID in NUMBER,
6 X_ATTRIBUTE11 in VARCHAR2,
7 X_ATTRIBUTE12 in VARCHAR2,
8 X_ATTRIBUTE13 in VARCHAR2,
9 X_ATTRIBUTE14 in VARCHAR2,
10 X_ATTRIBUTE15 in VARCHAR2,
11 X_CONTEXT in VARCHAR2,
12 X_WORKFLOW in VARCHAR2,
13 X_WEB_ENTRY_FLAG in VARCHAR2,
14 -- X_WEB_WORKFLOW in VARCHAR2,
15 X_BUSINESS_PROCESS_ID in NUMBER,
16 X_TASK_WORKFLOW in VARCHAR2,
17 X_WEIGHT in NUMBER,
18 X_OBJECT_VERSION_NUMBER in NUMBER,
19 -- X_WEB_IMAGE_FILENAME in VARCHAR2,
20 X_END_DATE_ACTIVE in DATE,
21 X_ATTRIBUTE1 in VARCHAR2,
22 X_ATTRIBUTE2 in VARCHAR2,
23 X_ATTRIBUTE3 in VARCHAR2,
24 X_ATTRIBUTE4 in VARCHAR2,
25 X_ATTRIBUTE5 in VARCHAR2,
26 X_ATTRIBUTE6 in VARCHAR2,
27 X_ATTRIBUTE7 in VARCHAR2,
28 X_ATTRIBUTE8 in VARCHAR2,
29 X_ATTRIBUTE9 in VARCHAR2,
30 X_ATTRIBUTE10 in VARCHAR2,
31 X_INCIDENT_SUBTYPE in VARCHAR2,
32 X_SEEDED_FLAG in VARCHAR2,
33 X_PARENT_INCIDENT_TYPE_ID in NUMBER,
34 X_START_DATE_ACTIVE in DATE,
35 X_NAME in VARCHAR2,
36 X_DESCRIPTION in VARCHAR2,
37 X_ABORT_WORKFLOW_CLOSE_FLAG in VARCHAR2,
38 X_AUTOLAUNCH_WORKFLOW_FLAG in VARCHAR2,
39 X_CREATION_DATE in DATE,
40 X_CREATED_BY in NUMBER,
41 X_LAST_UPDATE_DATE in DATE,
42 X_LAST_UPDATED_BY in NUMBER,
43 X_LAST_UPDATE_LOGIN in NUMBER,
44 X_STATUS_GROUP_ID in NUMBER,
45 -- for cmro_eam
46 X_CMRO_FLAG in VARCHAR2,
47 X_MAINTENANCE_FLAG in VARCHAR2,
48 X_IMAGE_FILE_NAME in VARCHAR2,
49 p_DETAILED_ERECORD_REQ_FLAG IN VARCHAR2
50 ) is
51 cursor C is select ROWID from CS_INCIDENT_TYPES_B
52 where INCIDENT_TYPE_ID = X_INCIDENT_TYPE_ID
53 ;
54
55
56 -- for security
57
58 cursor c_type_resp_csr is
59 select * from cs_service_responsibility where access_type='ALL';
60
61 cursor c_type_agent_csr IS
62 select * from cs_service_responsibility
63 where business_usage='AGENT' and access_type='ALL';
64
65 c_type_agent_rec c_type_agent_csr%ROWTYPE;
66
67 -- end
68
69 begin
70
71
72 insert into CS_INCIDENT_TYPES_B (
73 ATTRIBUTE11,
74 ATTRIBUTE12,
75 ATTRIBUTE13,
76 ATTRIBUTE14,
77 ATTRIBUTE15,
78 CONTEXT,
79 WORKFLOW,
80 WEB_ENTRY_FLAG,
81 -- WEB_WORKFLOW,
82 BUSINESS_PROCESS_ID,
83 TASK_WORKFLOW,
84 WEIGHT,
85 OBJECT_VERSION_NUMBER,
86 -- WEB_IMAGE_FILENAME,
87 END_DATE_ACTIVE,
88 ATTRIBUTE1,
89 ATTRIBUTE2,
90 ATTRIBUTE3,
91 ATTRIBUTE4,
92 ATTRIBUTE5,
93 ATTRIBUTE6,
94 ATTRIBUTE7,
95 ATTRIBUTE8,
96 ATTRIBUTE9,
97 ATTRIBUTE10,
98 INCIDENT_TYPE_ID,
99 INCIDENT_SUBTYPE,
100 SEEDED_FLAG,
101 PARENT_INCIDENT_TYPE_ID,
102 START_DATE_ACTIVE,
103 ABORT_WORKFLOW_CLOSE_FLAG,
104 AUTOLAUNCH_WORKFLOW_FLAG,
105 CREATION_DATE,
106 CREATED_BY,
107 LAST_UPDATE_DATE,
108 LAST_UPDATED_BY,
109 LAST_UPDATE_LOGIN,
110 STATUS_GROUP_ID,
111 -- for cmro_eam
112 CMRO_FLAG,
113 MAINTENANCE_FLAG,
114 IMAGE_FILE_NAME,
115 DETAILED_ERECORD_REQ_FLAG
116 ) values (
117 X_ATTRIBUTE11,
118 X_ATTRIBUTE12,
119 X_ATTRIBUTE13,
120 X_ATTRIBUTE14,
121 X_ATTRIBUTE15,
122 X_CONTEXT,
123 X_WORKFLOW,
124 X_WEB_ENTRY_FLAG,
125 -- X_WEB_WORKFLOW,
126 X_BUSINESS_PROCESS_ID,
127 X_TASK_WORKFLOW,
128 X_WEIGHT,
129 X_OBJECT_VERSION_NUMBER,
130 -- X_WEB_IMAGE_FILENAME,
131 X_END_DATE_ACTIVE,
132 X_ATTRIBUTE1,
133 X_ATTRIBUTE2,
134 X_ATTRIBUTE3,
135 X_ATTRIBUTE4,
136 X_ATTRIBUTE5,
137 X_ATTRIBUTE6,
138 X_ATTRIBUTE7,
139 X_ATTRIBUTE8,
140 X_ATTRIBUTE9,
141 X_ATTRIBUTE10,
142 X_INCIDENT_TYPE_ID,
143 X_INCIDENT_SUBTYPE,
144 X_SEEDED_FLAG,
145 X_PARENT_INCIDENT_TYPE_ID,
146 X_START_DATE_ACTIVE,
147 X_ABORT_WORKFLOW_CLOSE_FLAG,
148 X_AUTOLAUNCH_WORKFLOW_FLAG,
149 X_CREATION_DATE,
150 X_CREATED_BY,
151 X_LAST_UPDATE_DATE,
152 X_LAST_UPDATED_BY,
153 X_LAST_UPDATE_LOGIN,
154 X_STATUS_GROUP_ID,
155 -- for cmro_eam
156 X_CMRO_FLAG,
157 X_MAINTENANCE_FLAG,
158 X_IMAGE_FILE_NAME,
159 P_DETAILED_ERECORD_REQ_FLAG
160 );
161
162 insert into CS_INCIDENT_TYPES_TL (
163 NAME,
164 INCIDENT_TYPE_ID,
165 DESCRIPTION,
166 CREATED_BY,
167 CREATION_DATE,
168 LAST_UPDATED_BY,
169 LAST_UPDATE_DATE,
170 LAST_UPDATE_LOGIN,
171 LANGUAGE,
172 SOURCE_LANG
173 ) select
174 X_NAME,
175 X_INCIDENT_TYPE_ID,
176 X_DESCRIPTION,
177 X_CREATED_BY,
178 X_CREATION_DATE,
179 X_LAST_UPDATED_BY,
180 X_LAST_UPDATE_DATE,
181 X_LAST_UPDATE_LOGIN,
182 L.LANGUAGE_CODE,
183 userenv('LANG')
184 from FND_LANGUAGES L
185 where L.INSTALLED_FLAG in ('I', 'B')
186 and not exists
187 (select NULL
188 from CS_INCIDENT_TYPES_TL T
189 where T.INCIDENT_TYPE_ID = X_INCIDENT_TYPE_ID
190 and T.LANGUAGE = L.LANGUAGE_CODE);
191
192 open c;
193 fetch c into X_ROWID;
194 if (c%notfound) then
195 close c;
196 raise no_data_found;
197 end if;
198 close c;
199
200 -- start for security
201
202 if (nvl(X_WEB_ENTRY_FLAG,'N') = 'N') then
203 for c_type_agent_rec IN c_type_agent_csr
204 loop
205 insert into cs_sr_type_mapping
206 (
207 INCIDENT_TYPE_ID,
208 RESPONSIBILITY_ID,
209 APPLICATION_ID,
210 BUSINESS_USAGE,
211 START_DATE,
212 END_DATE,
213 CREATED_BY,
214 CREATION_DATE,
215 LAST_UPDATED_BY,
216 LAST_UPDATE_DATE,
217 LAST_UPDATE_LOGIN,
218 SEEDED_FLAG,
219 OBJECT_VERSION_NUMBER
220 )values
221 (
222 X_INCIDENT_TYPE_ID,
223 c_type_agent_rec.responsibility_id,
224 c_type_agent_rec.application_id,
225 c_type_agent_rec.business_usage,
226 X_START_DATE_ACTIVE,
227 X_END_DATE_ACTIVE,
228 X_CREATED_BY,
229 X_CREATION_DATE,
230 X_LAST_UPDATED_BY,
231 X_LAST_UPDATE_DATE,
232 X_LAST_UPDATE_LOGIN,
233 X_SEEDED_FLAG,
234 1
235 );
236 end loop;
237 elsif (nvl(X_WEB_ENTRY_FLAG,'N') = 'Y') THEN
238 for c_type_resp_rec IN c_type_resp_csr
239 loop
240 insert into cs_sr_type_mapping
241 (
242 INCIDENT_TYPE_ID,
243 RESPONSIBILITY_ID,
244 APPLICATION_ID,
245 BUSINESS_USAGE,
246 START_DATE,
247 END_DATE,
248 CREATED_BY,
249 CREATION_DATE,
250 LAST_UPDATED_BY,
251 LAST_UPDATE_DATE,
252 LAST_UPDATE_LOGIN,
253 SEEDED_FLAG,
254 OBJECT_VERSION_NUMBER
255 )values
256 (
257 X_INCIDENT_TYPE_ID,
258 c_type_resp_rec.responsibility_id,
259 c_type_resp_rec.application_id,
260 c_type_resp_rec.business_usage,
261 X_START_DATE_ACTIVE,
262 X_END_DATE_ACTIVE,
263 X_CREATED_BY,
264 X_CREATION_DATE,
265 X_LAST_UPDATED_BY,
266 X_LAST_UPDATE_DATE,
267 X_LAST_UPDATE_LOGIN,
268 X_SEEDED_FLAG,
269 1
270 );
271 end loop;
272 end if;
273
274 -- end for security
275
276
277
278 end INSERT_ROW;
279
280 procedure LOCK_ROW (
281 X_INCIDENT_TYPE_ID in NUMBER,
282 X_ATTRIBUTE11 in VARCHAR2,
283 X_ATTRIBUTE12 in VARCHAR2,
284 X_ATTRIBUTE13 in VARCHAR2,
285 X_ATTRIBUTE14 in VARCHAR2,
286 X_ATTRIBUTE15 in VARCHAR2,
287 X_CONTEXT in VARCHAR2,
288 X_WORKFLOW in VARCHAR2,
289 X_WEB_ENTRY_FLAG in VARCHAR2,
290 -- X_WEB_WORKFLOW in VARCHAR2,
291 X_BUSINESS_PROCESS_ID in NUMBER,
292 X_TASK_WORKFLOW in VARCHAR2,
293 X_WEIGHT in NUMBER,
294 X_OBJECT_VERSION_NUMBER in NUMBER,
295 -- X_WEB_IMAGE_FILENAME in VARCHAR2,
296 X_END_DATE_ACTIVE in DATE,
297 X_ATTRIBUTE1 in VARCHAR2,
298 X_ATTRIBUTE2 in VARCHAR2,
299 X_ATTRIBUTE3 in VARCHAR2,
300 X_ATTRIBUTE4 in VARCHAR2,
301 X_ATTRIBUTE5 in VARCHAR2,
302 X_ATTRIBUTE6 in VARCHAR2,
303 X_ATTRIBUTE7 in VARCHAR2,
304 X_ATTRIBUTE8 in VARCHAR2,
305 X_ATTRIBUTE9 in VARCHAR2,
306 X_ATTRIBUTE10 in VARCHAR2,
307 X_INCIDENT_SUBTYPE in VARCHAR2,
308 X_SEEDED_FLAG in VARCHAR2,
309 X_PARENT_INCIDENT_TYPE_ID in NUMBER,
310 X_START_DATE_ACTIVE in DATE,
311 X_NAME in VARCHAR2,
312 X_DESCRIPTION in VARCHAR2,
313 X_ABORT_WORKFLOW_CLOSE_FLAG in VARCHAR2,
314 X_AUTOLAUNCH_WORKFLOW_FLAG in VARCHAR2,
315 X_STATUS_GROUP_ID in NUMBER,
316 -- for cmro_eam
317 X_CMRO_FLAG in VARCHAR2,
318 X_MAINTENANCE_FLAG in VARCHAR2,
319 X_IMAGE_FILE_NAME in VARCHAR2,
320 p_DETAILED_ERECORD_REQ_FLAG IN VARCHAR2
321 ) is
322 cursor c is select
323 ATTRIBUTE11,
324 ATTRIBUTE12,
325 ATTRIBUTE13,
326 ATTRIBUTE14,
327 ATTRIBUTE15,
328 CONTEXT,
329 WORKFLOW,
330 WEB_ENTRY_FLAG,
331 -- WEB_WORKFLOW,
332 BUSINESS_PROCESS_ID,
333 TASK_WORKFLOW,
334 WEIGHT,
335 OBJECT_VERSION_NUMBER,
336 -- WEB_IMAGE_FILENAME,
337 END_DATE_ACTIVE,
338 ATTRIBUTE1,
339 ATTRIBUTE2,
340 ATTRIBUTE3,
341 ATTRIBUTE4,
342 ATTRIBUTE5,
343 ATTRIBUTE6,
344 ATTRIBUTE7,
345 ATTRIBUTE8,
346 ATTRIBUTE9,
347 ATTRIBUTE10,
348 INCIDENT_SUBTYPE,
349 SEEDED_FLAG,
350 PARENT_INCIDENT_TYPE_ID,
351 START_DATE_ACTIVE,
352 ABORT_WORKFLOW_CLOSE_FLAG,
353 AUTOLAUNCH_WORKFLOW_FLAG,
354 STATUS_GROUP_ID,
355 -- for cmro_eam
356 CMRO_FLAG ,
357 MAINTENANCE_FLAG,
358 IMAGE_FILE_NAME,
359 DETAILED_ERECORD_REQ_FLAG
360 from CS_INCIDENT_TYPES_B
361 where INCIDENT_TYPE_ID = X_INCIDENT_TYPE_ID
362 for update of INCIDENT_TYPE_ID nowait;
363 recinfo c%rowtype;
364
365 cursor c1 is select
366 NAME,
367 DESCRIPTION,
368 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
369 from CS_INCIDENT_TYPES_TL
370 where INCIDENT_TYPE_ID = X_INCIDENT_TYPE_ID
371 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
372 for update of INCIDENT_TYPE_ID nowait;
373 begin
374 open c;
375 fetch c into recinfo;
376 if (c%notfound) then
377 close c;
378 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
379 app_exception.raise_exception;
380 end if;
381 close c;
382 if ( ((recinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
383 OR ((recinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
384 AND ((recinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
385 OR ((recinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
386 AND ((recinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
387 OR ((recinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
388 AND ((recinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
389 OR ((recinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
390 AND ((recinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
391 OR ((recinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
392 AND ((recinfo.CONTEXT = X_CONTEXT)
393 OR ((recinfo.CONTEXT is null) AND (X_CONTEXT is null)))
394 AND ((recinfo.WORKFLOW = X_WORKFLOW)
395 OR ((recinfo.WORKFLOW is null) AND (X_WORKFLOW is null)))
396 AND ((recinfo.WEB_ENTRY_FLAG = X_WEB_ENTRY_FLAG)
397 OR ((recinfo.WEB_ENTRY_FLAG is null) AND (X_WEB_ENTRY_FLAG is null)))
398 -- AND ((recinfo.WEB_WORKFLOW = X_WEB_WORKFLOW)
399 -- OR ((recinfo.WEB_WORKFLOW is null) AND (X_WEB_WORKFLOW is null)))
400 AND ((recinfo.BUSINESS_PROCESS_ID = X_BUSINESS_PROCESS_ID)
401 OR ((recinfo.BUSINESS_PROCESS_ID is null)
402 AND (X_BUSINESS_PROCESS_ID is null)))
403 AND ((recinfo.TASK_WORKFLOW = X_TASK_WORKFLOW)
404 OR ((recinfo.TASK_WORKFLOW is null)
405 AND (X_TASK_WORKFLOW is null)))
406 AND ((recinfo.WEIGHT = X_WEIGHT)
407 OR ((recinfo.WEIGHT is null) AND (X_WEIGHT is null)))
408 AND (recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
409 -- AND ((recinfo.WEB_IMAGE_FILENAME = X_WEB_IMAGE_FILENAME)
410 -- OR ((recinfo.WEB_IMAGE_FILENAME is null)
411 -- AND (X_WEB_IMAGE_FILENAME is null)))
412 AND ((recinfo.END_DATE_ACTIVE = X_END_DATE_ACTIVE)
413 OR ((recinfo.END_DATE_ACTIVE is null)
414 AND (X_END_DATE_ACTIVE is null)))
415 AND ((recinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
416 OR ((recinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
417 AND ((recinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
418 OR ((recinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
419 AND ((recinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
420 OR ((recinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
421 AND ((recinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
422 OR ((recinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
423 AND ((recinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
424 OR ((recinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
425 AND ((recinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
426 OR ((recinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
427 AND ((recinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
428 OR ((recinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
429 AND ((recinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
430 OR ((recinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
431 AND ((recinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
432 OR ((recinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
433 AND ((recinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
434 OR ((recinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
435 AND (recinfo.INCIDENT_SUBTYPE = X_INCIDENT_SUBTYPE)
436 AND ((recinfo.SEEDED_FLAG = X_SEEDED_FLAG)
437 OR ((recinfo.SEEDED_FLAG is null) AND (X_SEEDED_FLAG is null)))
438 AND ((recinfo.PARENT_INCIDENT_TYPE_ID = X_PARENT_INCIDENT_TYPE_ID)
439 OR ((recinfo.PARENT_INCIDENT_TYPE_ID is null)
440 AND (X_PARENT_INCIDENT_TYPE_ID is null)))
441 AND ((recinfo.START_DATE_ACTIVE = X_START_DATE_ACTIVE)
445 OR ((recinfo.ABORT_WORKFLOW_CLOSE_FLAG is null)
442 OR ((recinfo.START_DATE_ACTIVE is null)
443 AND (X_START_DATE_ACTIVE is null)))
444 AND ((recinfo.ABORT_WORKFLOW_CLOSE_FLAG = X_ABORT_WORKFLOW_CLOSE_FLAG)
446 AND (X_ABORT_WORKFLOW_CLOSE_FLAG is null)))
447 AND ((recinfo.AUTOLAUNCH_WORKFLOW_FLAG = X_AUTOLAUNCH_WORKFLOW_FLAG)
448 OR ((recinfo.AUTOLAUNCH_WORKFLOW_FLAG is null)
449 AND (X_AUTOLAUNCH_WORKFLOW_FLAG is null)))
450 AND ((recinfo.STATUS_GROUP_ID = X_STATUS_GROUP_ID)
451 OR ((recinfo.STATUS_GROUP_ID is null)
452 AND (X_STATUS_GROUP_ID is null)))
453 -- for cmro_eam
454 AND ((recinfo.CMRO_FLAG = X_CMRO_FLAG)
455 OR ((recinfo.CMRO_FLAG is null) AND (X_CMRO_FLAG is null)))
456 AND ((recinfo.MAINTENANCE_FLAG = X_MAINTENANCE_FLAG)
457 OR ((recinfo.MAINTENANCE_FLAG is null)
458 AND (X_MAINTENANCE_FLAG is null)))
459 AND ((recinfo.IMAGE_FILE_NAME = X_IMAGE_FILE_NAME)
460 OR ((recinfo.IMAGE_FILE_NAME is null)
461 AND (X_IMAGE_FILE_NAME is null)))
462 -- end for cmro_eam
463 AND ((recinfo.DETAILED_ERECORD_REQ_FLAG = p_DETAILED_ERECORD_REQ_FLAG)
464 OR ((recinfo.DETAILED_ERECORD_REQ_FLAG is null)
465 AND (p_DETAILED_ERECORD_REQ_FLAG is null)))
466 ) then
467 null;
468 else
469 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
470 app_exception.raise_exception;
471 end if;
472
473 for tlinfo in c1 loop
474 if (tlinfo.BASELANG = 'Y') then
475 if ( (tlinfo.NAME = X_NAME)
476 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
477 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
478 ) then
479 null;
480 else
481 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
482 app_exception.raise_exception;
483 end if;
484 end if;
485 end loop;
486 return;
487 end LOCK_ROW;
488
489 /*======================================================================+
490 ==
491 == Procedure name : UPDATE_ROW
492 == Modification History:
493 ==
494 == Date Name Desc
495 == ---------- --------- ---------------------------------------------
496 == 12-Jan-2006 PRAYADUR FP Bug 4916688 Added a sql query to check whether
497 == the record already exists in cs_sr_type_mapping or not,
498 == if not then inserting the record else updating the table.
499 ========================================================================*/
500
501
502 procedure UPDATE_ROW (
503 X_INCIDENT_TYPE_ID in NUMBER,
504 X_ATTRIBUTE11 in VARCHAR2,
505 X_ATTRIBUTE12 in VARCHAR2,
506 X_ATTRIBUTE13 in VARCHAR2,
507 X_ATTRIBUTE14 in VARCHAR2,
508 X_ATTRIBUTE15 in VARCHAR2,
509 X_CONTEXT in VARCHAR2,
510 X_WORKFLOW in VARCHAR2,
511 X_WEB_ENTRY_FLAG in VARCHAR2,
512 -- X_WEB_WORKFLOW in VARCHAR2,
513 X_BUSINESS_PROCESS_ID in NUMBER,
514 X_TASK_WORKFLOW in VARCHAR2,
515 X_WEIGHT in NUMBER,
516 X_OBJECT_VERSION_NUMBER in NUMBER,
517 -- X_WEB_IMAGE_FILENAME in VARCHAR2,
518 X_END_DATE_ACTIVE in DATE,
519 X_ATTRIBUTE1 in VARCHAR2,
520 X_ATTRIBUTE2 in VARCHAR2,
521 X_ATTRIBUTE3 in VARCHAR2,
522 X_ATTRIBUTE4 in VARCHAR2,
523 X_ATTRIBUTE5 in VARCHAR2,
524 X_ATTRIBUTE6 in VARCHAR2,
525 X_ATTRIBUTE7 in VARCHAR2,
526 X_ATTRIBUTE8 in VARCHAR2,
527 X_ATTRIBUTE9 in VARCHAR2,
528 X_ATTRIBUTE10 in VARCHAR2,
529 X_INCIDENT_SUBTYPE in VARCHAR2,
530 X_SEEDED_FLAG in VARCHAR2,
531 X_PARENT_INCIDENT_TYPE_ID in NUMBER,
532 X_START_DATE_ACTIVE in DATE,
533 X_NAME in VARCHAR2,
534 X_DESCRIPTION in VARCHAR2,
535 X_ABORT_WORKFLOW_CLOSE_FLAG in VARCHAR2,
536 X_AUTOLAUNCH_WORKFLOW_FLAG in VARCHAR2,
537 X_LAST_UPDATE_DATE in DATE,
538 X_LAST_UPDATED_BY in NUMBER,
539 X_LAST_UPDATE_LOGIN in NUMBER,
540 X_STATUS_GROUP_ID in NUMBER,
541 -- for cmro_eam
542 X_CMRO_FLAG in VARCHAR2,
543 X_MAINTENANCE_FLAG in VARCHAR2,
544 X_IMAGE_FILE_NAME in VARCHAR2,
545 p_DETAILED_ERECORD_REQ_FLAG IN VARCHAR2
546 ) is
547
548 -- start for security
549 l_old_srtype_rec CS_INCIDENT_TYPES_B%ROWTYPE;
550 mapping_exist number;
551
552 cursor c_create_map_csr IS
553 select *
554 from cs_service_responsibility
555 where business_usage='SELF_SERVICE'
556 and access_type='ALL';
557
558 -- end for security
559 begin
560
561 select *
562 into l_old_srtype_rec
563 from cs_incident_types_b
564 where incident_type_id = X_INCIDENT_TYPE_ID;
565
566
567 update CS_INCIDENT_TYPES_B set
568 ATTRIBUTE11 = X_ATTRIBUTE11,
569 ATTRIBUTE12 = X_ATTRIBUTE12,
570 ATTRIBUTE13 = X_ATTRIBUTE13,
571 ATTRIBUTE14 = X_ATTRIBUTE14,
572 ATTRIBUTE15 = X_ATTRIBUTE15,
573 CONTEXT = X_CONTEXT,
574 WORKFLOW = X_WORKFLOW,
575 WEB_ENTRY_FLAG = X_WEB_ENTRY_FLAG,
576 -- WEB_WORKFLOW = X_WEB_WORKFLOW,
577 BUSINESS_PROCESS_ID = X_BUSINESS_PROCESS_ID,
578 TASK_WORKFLOW = X_TASK_WORKFLOW,
579 WEIGHT = X_WEIGHT,
580 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
581 -- WEB_IMAGE_FILENAME = X_WEB_IMAGE_FILENAME,
585 ATTRIBUTE3 = X_ATTRIBUTE3,
582 END_DATE_ACTIVE = X_END_DATE_ACTIVE,
583 ATTRIBUTE1 = X_ATTRIBUTE1,
584 ATTRIBUTE2 = X_ATTRIBUTE2,
586 ATTRIBUTE4 = X_ATTRIBUTE4,
587 ATTRIBUTE5 = X_ATTRIBUTE5,
588 ATTRIBUTE6 = X_ATTRIBUTE6,
589 ATTRIBUTE7 = X_ATTRIBUTE7,
590 ATTRIBUTE8 = X_ATTRIBUTE8,
591 ATTRIBUTE9 = X_ATTRIBUTE9,
592 ATTRIBUTE10 = X_ATTRIBUTE10,
593 INCIDENT_SUBTYPE = X_INCIDENT_SUBTYPE,
594 SEEDED_FLAG = X_SEEDED_FLAG,
595 PARENT_INCIDENT_TYPE_ID = X_PARENT_INCIDENT_TYPE_ID,
596 START_DATE_ACTIVE = X_START_DATE_ACTIVE,
597 ABORT_WORKFLOW_CLOSE_FLAG = X_ABORT_WORKFLOW_CLOSE_FLAG,
598 AUTOLAUNCH_WORKFLOW_FLAG = X_AUTOLAUNCH_WORKFLOW_FLAG,
599 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
600 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
601 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
602 STATUS_GROUP_ID = X_STATUS_GROUP_ID,
603 -- for cmro_eam
604 CMRO_FLAG = X_CMRO_FLAG,
605 MAINTENANCE_FLAG = X_MAINTENANCE_FLAG,
606 IMAGE_FILE_NAME = X_IMAGE_FILE_NAME,
607 -- end for cmro_eam
608 DETAILED_ERECORD_REQ_FLAG = p_DETAILED_ERECORD_REQ_FLAG
609 where INCIDENT_TYPE_ID = X_INCIDENT_TYPE_ID;
610
611 if (sql%notfound) then
612 raise no_data_found;
613 end if;
614
615 update CS_INCIDENT_TYPES_TL set
616 NAME = X_NAME,
617 DESCRIPTION = X_DESCRIPTION,
618 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
619 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
620 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
621 SOURCE_LANG = userenv('LANG')
622 where INCIDENT_TYPE_ID = X_INCIDENT_TYPE_ID
623 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
624
625 if (sql%notfound) then
626 raise no_data_found;
627 end if;
628
629 -- start for security
630
631 if (nvl(l_old_srtype_rec.web_entry_flag,'N') <> nvl(X_WEB_ENTRY_FLAG,'N')
632 and nvl(l_old_srtype_rec.web_entry_flag,'N') = 'N'
633 and nvl(X_WEB_ENTRY_FLAG,'N') = 'Y')
634 then
635 for c1_rec IN c_create_map_csr
636 loop
637
638 SELECT count(*) into mapping_exist FROM cs_sr_type_mapping
639 WHERE INCIDENT_TYPE_ID=X_INCIDENT_TYPE_ID and RESPONSIBILITY_ID=c1_rec.responsibility_id
640 and APPLICATION_ID=c1_rec.application_id;
641
642 if mapping_exist=0 then
643 insert into cs_sr_type_mapping
644 (
645 INCIDENT_TYPE_ID,
646 RESPONSIBILITY_ID,
647 APPLICATION_ID,
648 BUSINESS_USAGE,
649 START_DATE,
650 END_DATE,
651 CREATED_BY,
652 CREATION_DATE,
653 LAST_UPDATED_BY,
654 LAST_UPDATE_DATE,
655 LAST_UPDATE_LOGIN,
656 SEEDED_FLAG,
657 OBJECT_VERSION_NUMBER
658 )values
659 (
660 X_INCIDENT_TYPE_ID,
661 c1_rec.responsibility_id,
662 c1_rec.application_id,
663 c1_rec.business_usage,
664 SYSDATE,
665 X_END_DATE_ACTIVE,
666 X_LAST_UPDATED_BY,
667 SYSDATE,
668 X_LAST_UPDATED_BY,
669 SYSDATE,
670 X_LAST_UPDATE_LOGIN,
671 l_old_srtype_rec.SEEDED_FLAG,
672 1
673 );
674 else
675
676 update cs_sr_type_mapping
677 set
678 START_DATE=SYSDATE,LAST_UPDATED_BY=X_LAST_UPDATED_BY,
679 LAST_UPDATE_DATE=SYSDATE,LAST_UPDATE_LOGIN=X_LAST_UPDATE_LOGIN,
680 OBJECT_VERSION_NUMBER=OBJECT_VERSION_NUMBER+1
681 where INCIDENT_TYPE_ID=X_INCIDENT_TYPE_ID and
682 RESPONSIBILITY_ID=c1_rec.responsibility_id and
683 APPLICATION_ID=c1_rec.application_id;
684 end if;
685 end loop;
686
687 elsif (nvl(l_old_srtype_rec.web_entry_flag,'N')
688 <> nvl(X_WEB_ENTRY_FLAG,'N') and
689 nvl(l_old_srtype_rec.web_entry_flag,'N') = 'Y' and
690 nvl(X_WEB_ENTRY_FLAG,'N') = 'N')
691 then
692 for c1_rec IN c_create_map_csr
693 loop
694 UPDATE cs_sr_type_mapping cst
695 SET end_date = sysdate
696 WHERE cst.incident_type_id = X_INCIDENT_TYPE_ID
697 AND cst.responsibility_id = c1_rec.responsibility_id;
698 end loop;
699 end if;
700 -- end for security
701
702
703 end UPDATE_ROW;
704
705 procedure TRANSLATE_ROW ( X_INCIDENT_TYPE_ID in number,
706 X_NAME in varchar2,
707 X_DESCRIPTION in varchar2,
708 X_LAST_UPDATE_DATE in date,
709 X_LAST_UPDATE_LOGIN in number,
710 X_OWNER in varchar2)
711 is
712
713 l_user_id number;
714
715 begin
716
717 if X_OWNER = 'SEED' then
718 l_user_id := 1;
719 else
720 l_user_id := 0;
721 end if;
722
723 update cs_incident_types_tl set
724 name = nvl(x_name,name),
725 description = nvl(x_description, description), -- 5353154, if null set it description
726 last_update_date = nvl(x_last_update_date,sysdate),
727 last_updated_by = l_user_id,
728 last_update_login = 0,
729 source_lang = userenv('LANG')
730 where incident_type_id = x_incident_type_id
734
731 and userenv('LANG') in (LANGUAGE,SOURCE_LANG);
732
733 end TRANSLATE_ROW;
735 procedure LOAD_ROW (
736 X_INCIDENT_TYPE_ID in number ,
737 X_START_DATE_ACTIVE in date,
738 X_END_DATE_ACTIVE in date,
739 X_SEEDED_FLAG in varchar2,
740 X_ATTRIBUTE1 in varchar2,
741 X_ATTRIBUTE2 in varchar2,
742 X_ATTRIBUTE3 in varchar2,
743 X_ATTRIBUTE4 in varchar2,
744 X_ATTRIBUTE5 in varchar2,
745 X_ATTRIBUTE6 in varchar2,
746 X_ATTRIBUTE7 in varchar2,
747 X_ATTRIBUTE8 in varchar2,
748 X_ATTRIBUTE9 in varchar2,
749 X_ATTRIBUTE10 in varchar2,
750 X_ATTRIBUTE11 in varchar2,
751 X_ATTRIBUTE12 in varchar2,
752 X_ATTRIBUTE13 in varchar2,
753 X_ATTRIBUTE14 in varchar2,
754 X_ATTRIBUTE15 in varchar2,
755 X_CONTEXT in varchar2,
756 X_INCIDENT_SUBTYPE in varchar2,
757 X_PARENT_INCIDENT_TYPE_ID in number ,
758 X_WORKFLOW in varchar2,
759 X_WEB_ENTRY_FLAG in varchar2,
760 X_BUSINESS_PROCESS_ID in number,
761 X_TASK_WORKFLOW in varchar2,
762 X_WEIGHT in number,
763 X_ABORT_WORKFLOW_CLOSE_FLAG in varchar2,
764 X_AUTOLAUNCH_WORKFLOW_FLAG in varchar2,
765 X_OBJECT_VERSION_NUMBER in number,
766 X_NAME in varchar2,
767 X_DESCRIPTION in varchar2,
768 X_OWNER in varchar2,
769 X_STATUS_GROUP_ID in NUMBER,
770 -- for cmro_eam
771 X_CMRO_FLAG in VARCHAR2,
772 X_MAINTENANCE_FLAG in VARCHAR2,
773 X_IMAGE_FILE_NAME in VARCHAR2,
774 p_DETAILED_ERECORD_REQ_FLAG in VARCHAR2)
775 is
776 l_row_id rowid;
777 l_user_id number;
778 l_seeded_flag varchar2(1);
779
780 begin
781
782 if ( x_owner = 'SEED') then
783 l_seeded_flag := 'Y';
784 l_user_id := 1;
785 else
786 l_seeded_flag := 'N';
787 l_user_id := 0;
788 end if;
789
790 cs_incident_types_pkg.update_row(
791 X_INCIDENT_TYPE_ID => x_incident_type_id,
792 X_ATTRIBUTE11 => null,
793 X_ATTRIBUTE12 => null,
794 X_ATTRIBUTE13 => null,
795 X_ATTRIBUTE14 => null,
796 X_ATTRIBUTE15 => null,
797 X_CONTEXT => x_context,
798 X_WORKFLOW => x_workflow,
799 X_WEB_ENTRY_FLAG => x_web_entry_flag,
800 -- X_WEB_WORKFLOW => x_web_workflow,
801 X_BUSINESS_PROCESS_ID => x_business_process_id,
802 X_TASK_WORKFLOW => x_task_workflow,
803 X_WEIGHT => x_weight,
804 X_OBJECT_VERSION_NUMBER => x_object_version_number,
805 -- X_WEB_IMAGE_FILENAME => x_web_image_filename,
806 X_END_DATE_ACTIVE => x_end_date_active,
807 X_ATTRIBUTE1 => null,
808 X_ATTRIBUTE2 => null,
809 X_ATTRIBUTE3 => null,
810 X_ATTRIBUTE4 => null,
811 X_ATTRIBUTE5 => null,
812 X_ATTRIBUTE6 => null,
813 X_ATTRIBUTE7 => null,
814 X_ATTRIBUTE8 => null,
815 X_ATTRIBUTE9 => null,
816 X_ATTRIBUTE10 => null,
817 X_INCIDENT_SUBTYPE => x_incident_subtype,
818 X_SEEDED_FLAG => l_seeded_flag,
819 X_PARENT_INCIDENT_TYPE_ID => x_parent_incident_type_id,
820 X_START_DATE_ACTIVE => x_start_date_active,
821 X_NAME => x_name,
822 X_DESCRIPTION => x_description,
823 X_ABORT_WORKFLOW_CLOSE_FLAG => x_abort_workflow_close_flag,
824 X_AUTOLAUNCH_WORKFLOW_FLAG => x_autolaunch_workflow_flag,
825 X_LAST_UPDATE_DATE => sysdate,
826 X_LAST_UPDATED_BY => l_user_id,
827 X_LAST_UPDATE_LOGIN => 0,
828 X_STATUS_GROUP_ID => x_status_group_id,
829 -- for cmro_eam
830 X_CMRO_FLAG => x_cmro_flag,
831 X_MAINTENANCE_FLAG => x_maintenance_flag,
832 X_IMAGE_FILE_NAME => x_image_file_name,
833 -- end for cmro_eam
834 p_DETAILED_ERECORD_REQ_FLAG => p_DETAILED_ERECORD_REQ_FLAG
835 );
836
837 exception when no_data_found then
838 cs_incident_types_pkg.insert_row(
839 X_ROWID => l_row_id,
840 X_INCIDENT_TYPE_ID => x_incident_type_id,
841 X_ATTRIBUTE11 => null,
842 X_ATTRIBUTE12 => null,
843 X_ATTRIBUTE13 => null,
844 X_ATTRIBUTE14 => null,
845 X_ATTRIBUTE15 => null,
846 X_CONTEXT => x_context,
847 X_WORKFLOW => x_workflow,
848 X_WEB_ENTRY_FLAG => x_web_entry_flag,
849 -- X_WEB_WORKFLOW => null,
850 X_BUSINESS_PROCESS_ID => x_business_process_id,
851 X_TASK_WORKFLOW => x_task_workflow,
852 X_WEIGHT => x_weight,
853 X_OBJECT_VERSION_NUMBER => x_object_version_number,
854 -- X_WEB_IMAGE_FILENAME => x_web_image_filename,
855 X_END_DATE_ACTIVE => x_end_date_active,
856 X_ATTRIBUTE1 => null,
857 X_ATTRIBUTE2 => null,
858 X_ATTRIBUTE3 => null,
859 X_ATTRIBUTE4 => null,
860 X_ATTRIBUTE5 => null,
861 X_ATTRIBUTE6 => null,
862 X_ATTRIBUTE7 => null,
863 X_ATTRIBUTE8 => null,
864 X_ATTRIBUTE9 => null,
865 X_ATTRIBUTE10 => null,
866 X_INCIDENT_SUBTYPE => x_incident_subtype,
867 X_SEEDED_FLAG => l_seeded_flag,
868 X_PARENT_INCIDENT_TYPE_ID => x_parent_incident_type_id,
869 X_START_DATE_ACTIVE => x_start_date_active,
870 X_NAME => x_name,
871 X_DESCRIPTION => x_description,
872 X_ABORT_WORKFLOW_CLOSE_FLAG => x_abort_workflow_close_flag,
873 X_AUTOLAUNCH_WORKFLOW_FLAG => x_autolaunch_workflow_flag,
874 X_CREATION_DATE => SYSDATE,
875 X_CREATED_BY => l_user_id,
876 X_LAST_UPDATE_DATE => SYSDATE,
877 X_LAST_UPDATED_BY => l_user_id,
878 X_LAST_UPDATE_LOGIN => 0,
879 X_STATUS_GROUP_ID => x_status_group_id,
880 -- for cmro_eam
881 X_CMRO_FLAG => x_cmro_flag,
882 X_MAINTENANCE_FLAG => x_maintenance_flag,
883 X_IMAGE_FILE_NAME => x_image_file_name,
884 -- end for cmro_eam
888 end LOAD_ROW;
885 p_DETAILED_ERECORD_REQ_FLAG => p_DETAILED_ERECORD_REQ_FLAG
886 );
887
889
890 procedure DELETE_ROW (
891 X_INCIDENT_TYPE_ID in NUMBER
892 ) is
893 begin
894 delete from CS_INCIDENT_TYPES_TL
895 where INCIDENT_TYPE_ID = X_INCIDENT_TYPE_ID;
896
897 if (sql%notfound) then
898 raise no_data_found;
899 end if;
900
901 delete from CS_INCIDENT_TYPES_B
902 where INCIDENT_TYPE_ID = X_INCIDENT_TYPE_ID;
903
904 if (sql%notfound) then
905 raise no_data_found;
906 end if;
907 end DELETE_ROW;
908
909 procedure ADD_LANGUAGE
910 is
911 begin
912 delete from CS_INCIDENT_TYPES_TL T
913 where not exists
914 (select NULL
915 from CS_INCIDENT_TYPES_B B
916 where B.INCIDENT_TYPE_ID = T.INCIDENT_TYPE_ID
917 );
918
919 update CS_INCIDENT_TYPES_TL T set (
920 NAME,
921 DESCRIPTION
922 ) = (select
923 B.NAME,
924 B.DESCRIPTION
925 from CS_INCIDENT_TYPES_TL B
926 where B.INCIDENT_TYPE_ID = T.INCIDENT_TYPE_ID
927 and B.LANGUAGE = T.SOURCE_LANG)
928 where (
929 T.INCIDENT_TYPE_ID,
930 T.LANGUAGE
931 ) in (select
932 SUBT.INCIDENT_TYPE_ID,
933 SUBT.LANGUAGE
934 from CS_INCIDENT_TYPES_TL SUBB, CS_INCIDENT_TYPES_TL SUBT
935 where SUBB.INCIDENT_TYPE_ID = SUBT.INCIDENT_TYPE_ID
936 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
937 and (SUBB.NAME <> SUBT.NAME
938 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
939 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
940 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
941 ));
942
943 insert into CS_INCIDENT_TYPES_TL (
944 NAME,
945 INCIDENT_TYPE_ID,
946 DESCRIPTION,
947 CREATED_BY,
948 CREATION_DATE,
949 LAST_UPDATED_BY,
950 LAST_UPDATE_DATE,
951 LAST_UPDATE_LOGIN,
952 LANGUAGE,
953 SOURCE_LANG
954 ) select
955 B.NAME,
956 B.INCIDENT_TYPE_ID,
957 B.DESCRIPTION,
958 B.CREATED_BY,
959 B.CREATION_DATE,
960 B.LAST_UPDATED_BY,
961 B.LAST_UPDATE_DATE,
962 B.LAST_UPDATE_LOGIN,
963 L.LANGUAGE_CODE,
964 B.SOURCE_LANG
965 from CS_INCIDENT_TYPES_TL B, FND_LANGUAGES L
966 where L.INSTALLED_FLAG in ('I', 'B')
967 and B.LANGUAGE = userenv('LANG')
968 and not exists
969 (select NULL
970 from CS_INCIDENT_TYPES_TL T
971 where T.INCIDENT_TYPE_ID = B.INCIDENT_TYPE_ID
972 and T.LANGUAGE = L.LANGUAGE_CODE);
973 end ADD_LANGUAGE;
974
975 end CS_INCIDENT_TYPES_PKG;