[Home] [Help]
PACKAGE BODY: APPS.CS_INCIDENT_STATUSES_PKG
Source
1 package body CS_INCIDENT_STATUSES_PKG as
2 /* $Header: csviistb.pls 120.3.12010000.3 2009/07/27 05:22:40 sshilpam ship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out NOCOPY VARCHAR2,
5 X_INCIDENT_STATUS_ID in NUMBER,
6 X_ATTRIBUTE10 in VARCHAR2,
7 X_ATTRIBUTE11 in VARCHAR2,
8 X_ATTRIBUTE12 in VARCHAR2,
9 X_ATTRIBUTE13 in VARCHAR2,
10 X_ATTRIBUTE14 in VARCHAR2,
11 X_ATTRIBUTE15 in VARCHAR2,
12 X_CONTEXT in VARCHAR2,
13 X_DISALLOW_REQUEST_UPDATE in VARCHAR2,
14 -- X_DISALLOW_NEW_ACTION in VARCHAR2,
15 X_DISALLOW_ACTION_UPDATE in VARCHAR2,
16 X_DISALLOW_NEW_CHARGE in VARCHAR2,
17 X_DISALLOW_AGENT_DISPATCH in VARCHAR2,
18 -- X_DISALLOW_RMA in VARCHAR2,
19 X_DISALLOW_CHARGE_UPDATE in VARCHAR2,
20 X_DISALLOW_PRODUCT_UPDATE in VARCHAR2,
21 X_OBJECT_VERSION_NUMBER in NUMBER,
22 X_INCIDENT_SUBTYPE in VARCHAR2,
23 X_SEEDED_FLAG in VARCHAR2,
24 X_STATUS_CODE in VARCHAR2,
25 X_RESPONDED_FLAG in VARCHAR2,
26 X_RESOLVED_FLAG in VARCHAR2,
27 X_ON_HOLD_FLAG in VARCHAR2,
28 X_STATUS_CLASS_CODE in VARCHAR2,
29 X_CLOSE_FLAG in VARCHAR2,
30 X_VALID_IN_CREATE_FLAG in VARCHAR2,
31 X_DISPLAY_COLOR in VARCHAR2,
32 X_RGB_COLOR in VARCHAR2,
33 X_START_DATE_ACTIVE in DATE,
34 X_END_DATE_ACTIVE in DATE,
35 X_ATTRIBUTE1 in VARCHAR2,
36 X_ATTRIBUTE2 in VARCHAR2,
37 X_ATTRIBUTE3 in VARCHAR2,
38 X_ATTRIBUTE4 in VARCHAR2,
39 X_ATTRIBUTE5 in VARCHAR2,
40 X_ATTRIBUTE6 in VARCHAR2,
41 X_ATTRIBUTE7 in VARCHAR2,
42 X_ATTRIBUTE8 in VARCHAR2,
43 X_ATTRIBUTE9 in VARCHAR2,
44 X_NAME in VARCHAR2,
45 X_DESCRIPTION in VARCHAR2,
46 X_CREATION_DATE in DATE,
47 X_CREATED_BY in NUMBER,
48 X_LAST_UPDATE_DATE in DATE,
49 X_LAST_UPDATED_BY in NUMBER,
50 X_LAST_UPDATE_LOGIN in NUMBER,
51 X_SORT_ORDER in NUMBER,
52 p_PENDING_APPROVAL_FLAG IN VARCHAR2,
53 p_INTERMEDIATE_STATUS_ID IN NUMBER,
54 p_REJECTION_ACTION_STATUS_ID IN NUMBER,
55 p_APPROVAL_ACTION_STATUS_ID IN NUMBER,
56 X_DUP_CHK_FLAG in VARCHAR2
57 ) is
58 cursor C is select ROWID from CS_INCIDENT_STATUSES_B
59 where INCIDENT_STATUS_ID = X_INCIDENT_STATUS_ID
60 ;
61 begin
62 insert into CS_INCIDENT_STATUSES_B (
63 ATTRIBUTE10,
64 ATTRIBUTE11,
65 ATTRIBUTE12,
66 ATTRIBUTE13,
67 ATTRIBUTE14,
68 ATTRIBUTE15,
69 CONTEXT,
70 DISALLOW_REQUEST_UPDATE,
71 -- DISALLOW_NEW_ACTION,
72 DISALLOW_ACTION_UPDATE,
73 DISALLOW_NEW_CHARGE,
74 DISALLOW_AGENT_DISPATCH,
75 -- DISALLOW_RMA,
76 DISALLOW_CHARGE_UPDATE,
77 DISALLOW_PRODUCT_UPDATE,
78 OBJECT_VERSION_NUMBER,
79 INCIDENT_STATUS_ID,
80 INCIDENT_SUBTYPE,
81 SEEDED_FLAG,
82 STATUS_CODE,
83 RESPONDED_FLAG,
84 RESOLVED_FLAG,
85 ON_HOLD_FLAG,
86 STATUS_CLASS_CODE,
87 CLOSE_FLAG,
88 VALID_IN_CREATE_FLAG,
89 DISPLAY_COLOR,
90 RGB_COLOR,
91 START_DATE_ACTIVE,
92 END_DATE_ACTIVE,
93 ATTRIBUTE1,
94 ATTRIBUTE2,
95 ATTRIBUTE3,
96 ATTRIBUTE4,
97 ATTRIBUTE5,
98 ATTRIBUTE6,
99 ATTRIBUTE7,
100 ATTRIBUTE8,
101 ATTRIBUTE9,
102 CREATION_DATE,
103 CREATED_BY,
104 LAST_UPDATE_DATE,
105 LAST_UPDATED_BY,
106 LAST_UPDATE_LOGIN,
107 SORT_ORDER,
108 PENDING_APPROVAL_FLAG ,
109 INTERMEDIATE_STATUS_ID ,
110 REJECTION_ACTION_STATUS_ID ,
111 APPROVAL_ACTION_STATUS_ID ,
112 DUP_CHK_FLAG
113 ) values (
114 X_ATTRIBUTE10,
115 X_ATTRIBUTE11,
116 X_ATTRIBUTE12,
117 X_ATTRIBUTE13,
118 X_ATTRIBUTE14,
119 X_ATTRIBUTE15,
120 X_CONTEXT,
121 X_DISALLOW_REQUEST_UPDATE,
122 -- X_DISALLOW_NEW_ACTION,
123 X_DISALLOW_ACTION_UPDATE,
124 X_DISALLOW_NEW_CHARGE,
125 X_DISALLOW_AGENT_DISPATCH,
126 -- X_DISALLOW_RMA,
127 X_DISALLOW_CHARGE_UPDATE,
128 X_DISALLOW_PRODUCT_UPDATE,
129 X_OBJECT_VERSION_NUMBER,
130 X_INCIDENT_STATUS_ID,
131 X_INCIDENT_SUBTYPE,
132 X_SEEDED_FLAG,
133 X_STATUS_CODE,
134 X_RESPONDED_FLAG,
135 X_RESOLVED_FLAG,
136 X_ON_HOLD_FLAG,
137 X_STATUS_CLASS_CODE,
138 X_CLOSE_FLAG,
139 X_VALID_IN_CREATE_FLAG,
140 X_DISPLAY_COLOR,
141 X_RGB_COLOR,
142 X_START_DATE_ACTIVE,
143 X_END_DATE_ACTIVE,
144 X_ATTRIBUTE1,
145 X_ATTRIBUTE2,
146 X_ATTRIBUTE3,
147 X_ATTRIBUTE4,
148 X_ATTRIBUTE5,
149 X_ATTRIBUTE6,
150 X_ATTRIBUTE7,
151 X_ATTRIBUTE8,
152 X_ATTRIBUTE9,
153 X_CREATION_DATE,
154 X_CREATED_BY,
155 X_LAST_UPDATE_DATE,
156 X_LAST_UPDATED_BY,
157 X_LAST_UPDATE_LOGIN,
158 X_SORT_ORDER,
159 p_PENDING_APPROVAL_FLAG ,
160 p_INTERMEDIATE_STATUS_ID ,
161 p_REJECTION_ACTION_STATUS_ID ,
162 p_APPROVAL_ACTION_STATUS_ID,
163 X_DUP_CHK_FLAG
164 );
165
166 insert into CS_INCIDENT_STATUSES_TL (
167 INCIDENT_STATUS_ID,
168 LAST_UPDATE_DATE,
169 LAST_UPDATED_BY,
170 CREATION_DATE,
171 CREATED_BY,
172 LAST_UPDATE_LOGIN,
173 NAME,
174 DESCRIPTION,
175 LANGUAGE,
176 SOURCE_LANG
177 ) select
178 X_INCIDENT_STATUS_ID,
179 X_LAST_UPDATE_DATE,
180 X_LAST_UPDATED_BY,
181 X_CREATION_DATE,
182 X_CREATED_BY,
183 X_LAST_UPDATE_LOGIN,
184 X_NAME,
185 X_DESCRIPTION,
186 L.LANGUAGE_CODE,
187 userenv('LANG')
188 from FND_LANGUAGES L
189 where L.INSTALLED_FLAG in ('I', 'B')
190 and not exists
191 (select NULL
192 from CS_INCIDENT_STATUSES_TL T
193 where T.INCIDENT_STATUS_ID = X_INCIDENT_STATUS_ID
194 and T.LANGUAGE = L.LANGUAGE_CODE);
195
196 open c;
197 fetch c into X_ROWID;
198 if (c%notfound) then
199 close c;
200 raise no_data_found;
201 end if;
202 close c;
203
204 end INSERT_ROW;
205
206 procedure LOCK_ROW (
207 X_INCIDENT_STATUS_ID in NUMBER,
208 X_ATTRIBUTE10 in VARCHAR2,
209 X_ATTRIBUTE11 in VARCHAR2,
210 X_ATTRIBUTE12 in VARCHAR2,
211 X_ATTRIBUTE13 in VARCHAR2,
212 X_ATTRIBUTE14 in VARCHAR2,
213 X_ATTRIBUTE15 in VARCHAR2,
214 X_CONTEXT in VARCHAR2,
215 X_DISALLOW_REQUEST_UPDATE in VARCHAR2,
216 -- X_DISALLOW_NEW_ACTION in VARCHAR2,
217 X_DISALLOW_ACTION_UPDATE in VARCHAR2,
218 X_DISALLOW_NEW_CHARGE in VARCHAR2,
219 X_DISALLOW_AGENT_DISPATCH in VARCHAR2,
220 --X_DISALLOW_RMA in VARCHAR2,
221 X_DISALLOW_CHARGE_UPDATE in VARCHAR2,
222 X_DISALLOW_PRODUCT_UPDATE in VARCHAR2,
223 X_OBJECT_VERSION_NUMBER in NUMBER,
224 X_INCIDENT_SUBTYPE in VARCHAR2,
225 X_SEEDED_FLAG in VARCHAR2,
226 X_STATUS_CODE in VARCHAR2,
227 X_RESPONDED_FLAG in VARCHAR2,
228 X_RESOLVED_FLAG in VARCHAR2,
229 X_ON_HOLD_FLAG in VARCHAR2,
230 X_STATUS_CLASS_CODE in VARCHAR2,
231 X_CLOSE_FLAG in VARCHAR2,
232 X_VALID_IN_CREATE_FLAG in VARCHAR2,
233 X_DISPLAY_COLOR in VARCHAR2,
234 X_RGB_COLOR in VARCHAR2,
235 X_START_DATE_ACTIVE in DATE,
236 X_END_DATE_ACTIVE in DATE,
237 X_ATTRIBUTE1 in VARCHAR2,
238 X_ATTRIBUTE2 in VARCHAR2,
239 X_ATTRIBUTE3 in VARCHAR2,
240 X_ATTRIBUTE4 in VARCHAR2,
241 X_ATTRIBUTE5 in VARCHAR2,
242 X_ATTRIBUTE6 in VARCHAR2,
243 X_ATTRIBUTE7 in VARCHAR2,
244 X_ATTRIBUTE8 in VARCHAR2,
245 X_ATTRIBUTE9 in VARCHAR2,
246 X_NAME in VARCHAR2,
247 X_DESCRIPTION in VARCHAR2,
248 X_SORT_ORDER in NUMBER,
249 p_PENDING_APPROVAL_FLAG IN VARCHAR2,
250 p_INTERMEDIATE_STATUS_ID IN NUMBER,
251 p_REJECTION_ACTION_STATUS_ID IN NUMBER,
252 p_APPROVAL_ACTION_STATUS_ID IN NUMBER,
253 X_DUP_CHK_FLAG IN VARCHAR2 --shachoud
254 ) is
255 cursor c is select
256 ATTRIBUTE10,
257 ATTRIBUTE11,
258 ATTRIBUTE12,
259 ATTRIBUTE13,
260 ATTRIBUTE14,
261 ATTRIBUTE15,
262 CONTEXT,
263 DISALLOW_REQUEST_UPDATE,
264 -- DISALLOW_NEW_ACTION,
265 DISALLOW_ACTION_UPDATE,
266 DISALLOW_NEW_CHARGE,
267 DISALLOW_AGENT_DISPATCH,
268 -- DISALLOW_RMA,
269 DISALLOW_CHARGE_UPDATE,
270 DISALLOW_PRODUCT_UPDATE,
271 OBJECT_VERSION_NUMBER,
272 INCIDENT_SUBTYPE,
273 SEEDED_FLAG,
274 STATUS_CODE,
275 RESPONDED_FLAG,
276 RESOLVED_FLAG,
277 ON_HOLD_FLAG,
278 STATUS_CLASS_CODE,
279 CLOSE_FLAG,
280 VALID_IN_CREATE_FLAG,
281 DISPLAY_COLOR,
282 RGB_COLOR,
283 START_DATE_ACTIVE,
284 END_DATE_ACTIVE,
285 ATTRIBUTE1,
286 ATTRIBUTE2,
287 ATTRIBUTE3,
288 ATTRIBUTE4,
289 ATTRIBUTE5,
290 ATTRIBUTE6,
291 ATTRIBUTE7,
292 ATTRIBUTE8,
293 ATTRIBUTE9,
294 SORT_ORDER,
295 PENDING_APPROVAL_FLAG ,
296 INTERMEDIATE_STATUS_ID ,
297 REJECTION_ACTION_STATUS_ID ,
298 APPROVAL_ACTION_STATUS_ID,
299 DUP_CHK_FLAG --shachoud
300 from CS_INCIDENT_STATUSES_B
301 where INCIDENT_STATUS_ID = X_INCIDENT_STATUS_ID
302 for update of INCIDENT_STATUS_ID nowait;
303 recinfo c%rowtype;
304
305 cursor c1 is select
306 NAME,
307 DESCRIPTION,
308 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
309 from CS_INCIDENT_STATUSES_TL
310 where INCIDENT_STATUS_ID = X_INCIDENT_STATUS_ID
311 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
312 for update of INCIDENT_STATUS_ID nowait;
313 begin
314 open c;
315 fetch c into recinfo;
316 if (c%notfound) then
317 close c;
318 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
319 app_exception.raise_exception;
320 end if;
321 close c;
322 if ( ((recinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
323 OR ((recinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
324 AND ((recinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
325 OR ((recinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
326 AND ((recinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
327 OR ((recinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
328 AND ((recinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
329 OR ((recinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
330 AND ((recinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
331 OR ((recinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
332 AND ((recinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
333 OR ((recinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
334 AND ((recinfo.CONTEXT = X_CONTEXT)
335 OR ((recinfo.CONTEXT is null) AND (X_CONTEXT is null)))
336 AND ((recinfo.DISALLOW_REQUEST_UPDATE = X_DISALLOW_REQUEST_UPDATE)
337 OR ((recinfo.DISALLOW_REQUEST_UPDATE is null)
338 AND (X_DISALLOW_REQUEST_UPDATE is null)))
339 -- AND ((recinfo.DISALLOW_NEW_ACTION = X_DISALLOW_NEW_ACTION)
340 -- OR ((recinfo.DISALLOW_NEW_ACTION is null)
341 -- AND (X_DISALLOW_NEW_ACTION is null)))
342 AND ((recinfo.DISALLOW_ACTION_UPDATE = X_DISALLOW_ACTION_UPDATE)
343 OR ((recinfo.DISALLOW_ACTION_UPDATE is null)
344 AND (X_DISALLOW_ACTION_UPDATE is null)))
345 AND ((recinfo.DISALLOW_NEW_CHARGE = X_DISALLOW_NEW_CHARGE)
346 OR ((recinfo.DISALLOW_NEW_CHARGE is null)
347 AND (X_DISALLOW_NEW_CHARGE is null)))
348 AND ((recinfo.DISALLOW_AGENT_DISPATCH = X_DISALLOW_AGENT_DISPATCH)
349 OR ((recinfo.DISALLOW_AGENT_DISPATCH is null)
350 AND (X_DISALLOW_AGENT_DISPATCH is null)))
351 --AND ((recinfo.DISALLOW_RMA = X_DISALLOW_RMA)
352 -- OR ((recinfo.DISALLOW_RMA is null)
353 -- AND (X_DISALLOW_RMA is null)))
354 AND ((recinfo.DISALLOW_CHARGE_UPDATE = X_DISALLOW_CHARGE_UPDATE)
355 OR ((recinfo.DISALLOW_CHARGE_UPDATE is null)
356 AND (X_DISALLOW_CHARGE_UPDATE is null)))
357 AND ((recinfo.DISALLOW_PRODUCT_UPDATE = X_DISALLOW_PRODUCT_UPDATE)
358 OR ((recinfo.DISALLOW_PRODUCT_UPDATE is null)
359 AND (X_DISALLOW_PRODUCT_UPDATE is null)))
360 AND (recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
361 AND (recinfo.INCIDENT_SUBTYPE = X_INCIDENT_SUBTYPE)
362 AND ((recinfo.SEEDED_FLAG = X_SEEDED_FLAG)
363 OR ((recinfo.SEEDED_FLAG is null) AND (X_SEEDED_FLAG is null)))
364 AND ((recinfo.STATUS_CODE = X_STATUS_CODE)
365 OR ((recinfo.STATUS_CODE is null) AND (X_STATUS_CODE is null)))
366 AND ((recinfo.CLOSE_FLAG = X_CLOSE_FLAG)
367 OR ((recinfo.CLOSE_FLAG is null) AND (X_CLOSE_FLAG is null)))
368 AND ((recinfo.VALID_IN_CREATE_FLAG = X_VALID_IN_CREATE_FLAG)
372 OR ((recinfo.DISPLAY_COLOR is null) AND (X_DISPLAY_COLOR is null)))
369 OR ((recinfo.VALID_IN_CREATE_FLAG is null)
370 AND (X_VALID_IN_CREATE_FLAG is null)))
371 AND ((recinfo.DISPLAY_COLOR = X_DISPLAY_COLOR)
373 AND ((recinfo.RGB_COLOR = X_RGB_COLOR)
374 OR ((recinfo.RGB_COLOR is null) AND (X_RGB_COLOR is null)))
375 AND ((recinfo.START_DATE_ACTIVE = X_START_DATE_ACTIVE)
376 OR ((recinfo.START_DATE_ACTIVE is null)
377 AND (X_START_DATE_ACTIVE is null)))
378 AND ((recinfo.END_DATE_ACTIVE = X_END_DATE_ACTIVE)
379 OR ((recinfo.END_DATE_ACTIVE is null)
380 AND (X_END_DATE_ACTIVE is null)))
381 AND ((recinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
382 OR ((recinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
383 AND ((recinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
384 OR ((recinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
385 AND ((recinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
386 OR ((recinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
387 AND ((recinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
388 OR ((recinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
389 AND ((recinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
390 OR ((recinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
391 AND ((recinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
392 OR ((recinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
393 AND ((recinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
394 OR ((recinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
395 AND ((recinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
396 OR ((recinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
397 AND ((recinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
398 OR ((recinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
399 AND ((recinfo.SORT_ORDER = X_SORT_ORDER)
400 OR ((recinfo.SORT_ORDER is null) AND (X_SORT_ORDER is null)))
401 AND ((recinfo.PENDING_APPROVAL_FLAG = p_PENDING_APPROVAL_FLAG)
402 OR ((recinfo.PENDING_APPROVAL_FLAG is null)
403 AND (p_PENDING_APPROVAL_FLAG is null)))
404
405 AND ((recinfo.INTERMEDIATE_STATUS_ID = p_INTERMEDIATE_STATUS_ID)
406 OR ((recinfo.INTERMEDIATE_STATUS_ID is null)
407 AND (p_INTERMEDIATE_STATUS_ID is null)))
408
409 AND ((recinfo.REJECTION_ACTION_STATUS_ID = p_REJECTION_ACTION_STATUS_ID)
410 OR ((recinfo.REJECTION_ACTION_STATUS_ID is null)
411 AND (p_REJECTION_ACTION_STATUS_ID is null)))
412
413 AND ((recinfo.APPROVAL_ACTION_STATUS_ID = p_APPROVAL_ACTION_STATUS_ID)
414 OR ((recinfo.APPROVAL_ACTION_STATUS_ID is null)
415 AND (p_APPROVAL_ACTION_STATUS_ID is null)))
416
417 AND ((recinfo.DUP_CHK_FLAG = X_DUP_CHK_FLAG) -- shachoud
418 OR ((recinfo.DUP_CHK_FLAG is null)
419 AND ( X_DUP_CHK_FLAG is null)))
420 ) then
421 null;
422 else
423 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
424 app_exception.raise_exception;
425 end if;
426
427 for tlinfo in c1 loop
428 if (tlinfo.BASELANG = 'Y') then
429 if ( (tlinfo.NAME = X_NAME)
430 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
431 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
432 ) then
433 null;
434 else
435 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
436 app_exception.raise_exception;
437 end if;
438 end if;
439 end loop;
440 return;
441 end LOCK_ROW;
442
443 procedure UPDATE_ROW (
444 X_INCIDENT_STATUS_ID in NUMBER,
445 X_ATTRIBUTE10 in VARCHAR2,
446 X_ATTRIBUTE11 in VARCHAR2,
447 X_ATTRIBUTE12 in VARCHAR2,
448 X_ATTRIBUTE13 in VARCHAR2,
449 X_ATTRIBUTE14 in VARCHAR2,
450 X_ATTRIBUTE15 in VARCHAR2,
451 X_CONTEXT in VARCHAR2,
452 X_DISALLOW_REQUEST_UPDATE in VARCHAR2,
453 -- X_DISALLOW_NEW_ACTION in VARCHAR2,
454 X_DISALLOW_ACTION_UPDATE in VARCHAR2,
455 X_DISALLOW_NEW_CHARGE in VARCHAR2,
456 X_DISALLOW_AGENT_DISPATCH in VARCHAR2,
457 -- X_DISALLOW_RMA in VARCHAR2,
458 X_DISALLOW_CHARGE_UPDATE in VARCHAR2,
459 X_DISALLOW_PRODUCT_UPDATE in VARCHAR2,
460 X_OBJECT_VERSION_NUMBER in NUMBER,
461 X_INCIDENT_SUBTYPE in VARCHAR2,
462 X_SEEDED_FLAG in VARCHAR2,
463 X_STATUS_CODE in VARCHAR2,
464 X_RESPONDED_FLAG in VARCHAR2,
465 X_RESOLVED_FLAG in VARCHAR2,
466 X_ON_HOLD_FLAG in VARCHAR2,
467 X_STATUS_CLASS_CODE in VARCHAR2,
468 X_CLOSE_FLAG in VARCHAR2,
469 X_VALID_IN_CREATE_FLAG in VARCHAR2,
470 X_DISPLAY_COLOR in VARCHAR2,
471 X_RGB_COLOR in VARCHAR2,
472 X_START_DATE_ACTIVE in DATE,
473 X_END_DATE_ACTIVE in DATE,
474 X_ATTRIBUTE1 in VARCHAR2,
475 X_ATTRIBUTE2 in VARCHAR2,
476 X_ATTRIBUTE3 in VARCHAR2,
477 X_ATTRIBUTE4 in VARCHAR2,
478 X_ATTRIBUTE5 in VARCHAR2,
479 X_ATTRIBUTE6 in VARCHAR2,
480 X_ATTRIBUTE7 in VARCHAR2,
481 X_ATTRIBUTE8 in VARCHAR2,
482 X_ATTRIBUTE9 in VARCHAR2,
483 X_NAME in VARCHAR2,
484 X_DESCRIPTION in VARCHAR2,
485 X_LAST_UPDATE_DATE in DATE,
486 X_LAST_UPDATED_BY in NUMBER,
487 X_LAST_UPDATE_LOGIN in NUMBER,
488 X_SORT_ORDER IN NUMBER,
489 p_PENDING_APPROVAL_FLAG IN VARCHAR2,
490 p_INTERMEDIATE_STATUS_ID IN NUMBER,
491 p_REJECTION_ACTION_STATUS_ID IN NUMBER,
492 p_APPROVAL_ACTION_STATUS_ID IN NUMBER,
493 X_DUP_CHK_FLAG IN VARCHAR2 --shachoud
494 ) is
495 begin
496 update CS_INCIDENT_STATUSES_B set
497 ATTRIBUTE10 = X_ATTRIBUTE10,
498 ATTRIBUTE11 = X_ATTRIBUTE11,
499 ATTRIBUTE12 = X_ATTRIBUTE12,
500 ATTRIBUTE13 = X_ATTRIBUTE13,
501 ATTRIBUTE14 = X_ATTRIBUTE14,
502 ATTRIBUTE15 = X_ATTRIBUTE15,
506 DISALLOW_ACTION_UPDATE = X_DISALLOW_ACTION_UPDATE,
503 CONTEXT = X_CONTEXT,
504 DISALLOW_REQUEST_UPDATE = X_DISALLOW_REQUEST_UPDATE,
505 -- DISALLOW_NEW_ACTION = X_DISALLOW_NEW_ACTION,
507 DISALLOW_NEW_CHARGE = X_DISALLOW_NEW_CHARGE,
508 DISALLOW_AGENT_DISPATCH = X_DISALLOW_AGENT_DISPATCH,
509 -- DISALLOW_RMA = X_DISALLOW_RMA,
510 DISALLOW_CHARGE_UPDATE = X_DISALLOW_CHARGE_UPDATE,
511 DISALLOW_PRODUCT_UPDATE = X_DISALLOW_PRODUCT_UPDATE,
512 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
513 INCIDENT_SUBTYPE = X_INCIDENT_SUBTYPE,
514 SEEDED_FLAG = X_SEEDED_FLAG,
515 STATUS_CODE = X_STATUS_CODE,
516 RESPONDED_FLAG=X_RESPONDED_FLAG,
517 RESOLVED_FLAG=X_RESOLVED_FLAG,
518 ON_HOLD_FLAG = X_ON_HOLD_FLAG,
519 STATUS_CLASS_CODE = X_STATUS_CLASS_CODE,
520 CLOSE_FLAG = X_CLOSE_FLAG,
521 VALID_IN_CREATE_FLAG = X_VALID_IN_CREATE_FLAG,
522 DISPLAY_COLOR = X_DISPLAY_COLOR,
523 RGB_COLOR = X_RGB_COLOR,
524 START_DATE_ACTIVE = X_START_DATE_ACTIVE,
525 END_DATE_ACTIVE = X_END_DATE_ACTIVE,
526 ATTRIBUTE1 = X_ATTRIBUTE1,
527 ATTRIBUTE2 = X_ATTRIBUTE2,
528 ATTRIBUTE3 = X_ATTRIBUTE3,
529 ATTRIBUTE4 = X_ATTRIBUTE4,
530 ATTRIBUTE5 = X_ATTRIBUTE5,
531 ATTRIBUTE6 = X_ATTRIBUTE6,
532 ATTRIBUTE7 = X_ATTRIBUTE7,
533 ATTRIBUTE8 = X_ATTRIBUTE8,
534 ATTRIBUTE9 = X_ATTRIBUTE9,
535 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
536 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
537 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
538 SORT_ORDER = X_SORT_ORDER,
539 PENDING_APPROVAL_FLAG = p_PENDING_APPROVAL_FLAG,
540 INTERMEDIATE_STATUS_ID = p_INTERMEDIATE_STATUS_ID,
541 REJECTION_ACTION_STATUS_ID = p_REJECTION_ACTION_STATUS_ID,
542 APPROVAL_ACTION_STATUS_ID = p_APPROVAL_ACTION_STATUS_ID,
543 DUP_CHK_FLAG = X_DUP_CHK_FLAG --shachoud
544 where INCIDENT_STATUS_ID = X_INCIDENT_STATUS_ID;
545
546 if (sql%notfound) then
547 raise no_data_found;
548 end if;
549
550 update CS_INCIDENT_STATUSES_TL set
551 NAME = X_NAME,
552 DESCRIPTION = X_DESCRIPTION,
553 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
554 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
555 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
556 SOURCE_LANG = userenv('LANG')
557 where INCIDENT_STATUS_ID = X_INCIDENT_STATUS_ID
558 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
559
560 if (sql%notfound) then
561 raise no_data_found;
562 end if;
563 end UPDATE_ROW;
564
565 procedure TRANSLATE_ROW (X_INCIDENT_STATUS_ID in number,
566 X_NAME in varchar2,
567 X_DESCRIPTION in varchar2,
568 X_LAST_UPDATE_DATE in date,
569 X_OWNER in varchar2,
570 X_LAST_UPDATE_LOGIN in number)
571 is
572 l_user_id number;
573 begin
574
575 if (X_OWNER = 'SEED') then
576 l_user_id := 1;
577 else
578 l_user_id := 0;
579 end if;
580
581 update cs_incident_statuses_tl set
582 name = nvl(x_name,name),
583 description = nvl(x_description,description),
584 last_update_date = nvl(x_last_update_date,sysdate),
585 last_updated_by = l_user_id,
586 last_update_login = 0,
587 source_lang = userenv('LANG')
588 where incident_status_id = x_incident_status_id
589 and userenv('LANG') in (LANGUAGE,SOURCE_LANG);
590
591 end TRANSLATE_ROW;
592
593 PROCEDURE LOAD_ROW(
594 X_INCIDENT_STATUS_ID in NUMBER,
595 X_ATTRIBUTE10 in VARCHAR2,
596 X_ATTRIBUTE11 in VARCHAR2,
597 X_ATTRIBUTE12 in VARCHAR2,
598 X_ATTRIBUTE13 in VARCHAR2,
599 X_ATTRIBUTE14 in VARCHAR2,
600 X_ATTRIBUTE15 in VARCHAR2,
601 X_CONTEXT in VARCHAR2,
602 X_DISALLOW_REQUEST_UPDATE in VARCHAR2,
603 -- X_DISALLOW_NEW_ACTION in VARCHAR2,
604 X_DISALLOW_ACTION_UPDATE in VARCHAR2,
605 X_DISALLOW_NEW_CHARGE in VARCHAR2,
606 X_DISALLOW_AGENT_DISPATCH in VARCHAR2,
607 -- X_DISALLOW_RMA in VARCHAR2,
608 X_DISALLOW_CHARGE_UPDATE in VARCHAR2,
609 X_DISALLOW_PRODUCT_UPDATE in VARCHAR2,
610 X_OBJECT_VERSION_NUMBER in NUMBER,
611 X_INCIDENT_SUBTYPE in VARCHAR2,
612 -- X_SEEDED_FLAG in VARCHAR2,
613 X_STATUS_CODE in VARCHAR2,
614 X_RESPONDED_FLAG in VARCHAR2,
615 X_RESOLVED_FLAG in VARCHAR2,
616 X_ON_HOLD_FLAG in VARCHAR2,
617 X_STATUS_CLASS_CODE in VARCHAR2,
618 X_CLOSE_FLAG in VARCHAR2,
619 X_VALID_IN_CREATE_FLAG in VARCHAR2,
620 X_DISPLAY_COLOR in VARCHAR2,
621 X_RGB_COLOR in VARCHAR2,
622 X_START_DATE_ACTIVE in DATE,
623 X_END_DATE_ACTIVE in DATE,
624 X_ATTRIBUTE1 in VARCHAR2,
625 X_ATTRIBUTE2 in VARCHAR2,
626 X_ATTRIBUTE3 in VARCHAR2,
627 X_ATTRIBUTE4 in VARCHAR2,
628 X_ATTRIBUTE5 in VARCHAR2,
629 X_ATTRIBUTE6 in VARCHAR2,
630 X_ATTRIBUTE7 in VARCHAR2,
631 X_ATTRIBUTE8 in VARCHAR2,
632 X_ATTRIBUTE9 in VARCHAR2,
633 X_NAME in VARCHAR2,
634 X_DESCRIPTION in VARCHAR2,
635 X_OWNER in VARCHAR2,
636 X_SORT_ORDER IN NUMBER,
637 p_PENDING_APPROVAL_FLAG IN VARCHAR2,
638 p_INTERMEDIATE_STATUS_ID IN NUMBER,
639 p_REJECTION_ACTION_STATUS_ID IN NUMBER,
640 p_APPROVAL_ACTION_STATUS_ID IN NUMBER ,
641 X_DUP_CHK_FLAG IN VARCHAR2 --shachoud
642 )
643 is
644 l_row_id rowid;
645 l_user_id number;
646 l_seeded_flag varchar2(1);
647
648 begin
649
650 if (X_OWNER = 'SEED') then
651 l_user_id := 1;
652 l_seeded_flag := 'Y';
653 else
654 l_user_id := 0;
655 l_seeded_flag := 'N';
656 end if;
657
658 cs_incident_statuses_pkg.update_row(
659 X_INCIDENT_STATUS_ID => x_incident_status_id,
660 X_ATTRIBUTE10 => null,
661 X_ATTRIBUTE11 => null,
665 X_ATTRIBUTE15 => null,
662 X_ATTRIBUTE12 => null,
663 X_ATTRIBUTE13 => null,
664 X_ATTRIBUTE14 => null,
666 X_CONTEXT => X_CONTEXT,
667 X_DISALLOW_REQUEST_UPDATE => X_DISALLOW_REQUEST_UPDATE,
668 X_DISALLOW_ACTION_UPDATE => X_DISALLOW_ACTION_UPDATE,
669 X_DISALLOW_NEW_CHARGE => X_DISALLOW_NEW_CHARGE,
670 X_DISALLOW_AGENT_DISPATCH => X_DISALLOW_AGENT_DISPATCH,
671 X_DISALLOW_CHARGE_UPDATE => X_DISALLOW_CHARGE_UPDATE,
672 X_DISALLOW_PRODUCT_UPDATE => X_DISALLOW_PRODUCT_UPDATE,
673 X_OBJECT_VERSION_NUMBER => X_OBJECT_VERSION_NUMBER,
674 X_INCIDENT_SUBTYPE => X_INCIDENT_SUBTYPE,
675 X_SEEDED_FLAG => l_seeded_flag,
676 X_STATUS_CODE => X_STATUS_CODE,
677 X_RESPONDED_FLAG => X_RESPONDED_FLAG,
678 X_RESOLVED_FLAG => X_RESOLVED_FLAG,
679 X_ON_HOLD_FLAG => X_ON_HOLD_FLAG,
680 X_STATUS_CLASS_CODE => X_STATUS_CLASS_CODE,
681 X_CLOSE_FLAG => X_CLOSE_FLAG,
682 X_VALID_IN_CREATE_FLAG => X_VALID_IN_CREATE_FLAG,
683 X_DISPLAY_COLOR => X_DISPLAY_COLOR,
684 X_RGB_COLOR => X_RGB_COLOR,
685 X_START_DATE_ACTIVE => to_date(X_START_DATE_ACTIVE,'YYYY/MM/DD'),
686 X_END_DATE_ACTIVE => to_date(X_END_DATE_ACTIVE,'YYYY/MM/DD'),
687 X_ATTRIBUTE1 => null,
688 X_ATTRIBUTE2 => null,
689 X_ATTRIBUTE3 => null,
690 X_ATTRIBUTE4 => null,
691 X_ATTRIBUTE5 => null,
692 X_ATTRIBUTE6 => null,
693 X_ATTRIBUTE7 => null,
694 X_ATTRIBUTE8 => null,
695 X_ATTRIBUTE9 => null,
696 X_NAME => X_NAME,
697 X_DESCRIPTION => X_DESCRIPTION,
698 X_LAST_UPDATE_DATE => SYSDATE,
699 X_LAST_UPDATED_BY => l_user_id,
700 X_LAST_UPDATE_LOGIN => 0,
701 X_SORT_ORDER => X_SORT_ORDER,
702 p_PENDING_APPROVAL_FLAG => p_PENDING_APPROVAL_FLAG,
703 p_INTERMEDIATE_STATUS_ID => p_INTERMEDIATE_STATUS_ID,
704 p_REJECTION_ACTION_STATUS_ID => p_REJECTION_ACTION_STATUS_ID,
705 p_APPROVAL_ACTION_STATUS_ID => p_APPROVAL_ACTION_STATUS_ID,
706 X_DUP_CHK_FLAG => X_DUP_CHK_FLAG -- shachoud
707 );
708
709 exception when no_data_found then
710 cs_incident_statuses_pkg.insert_row(
711 X_ROWID => l_row_id,
712 X_INCIDENT_STATUS_ID => x_incident_status_id ,
713 X_ATTRIBUTE10 => null,
714 X_ATTRIBUTE11 => null,
715 X_ATTRIBUTE12 => null,
716 X_ATTRIBUTE13 => null,
717 X_ATTRIBUTE14 => null,
718 X_ATTRIBUTE15 => null,
719 X_CONTEXT => X_CONTEXT,
720 X_DISALLOW_REQUEST_UPDATE => X_DISALLOW_REQUEST_UPDATE,
721 X_DISALLOW_ACTION_UPDATE => X_DISALLOW_ACTION_UPDATE,
722 X_DISALLOW_NEW_CHARGE => X_DISALLOW_NEW_CHARGE,
723 X_DISALLOW_AGENT_DISPATCH => X_DISALLOW_AGENT_DISPATCH,
724 X_DISALLOW_CHARGE_UPDATE => X_DISALLOW_CHARGE_UPDATE,
725 X_DISALLOW_PRODUCT_UPDATE => X_DISALLOW_PRODUCT_UPDATE,
726 X_OBJECT_VERSION_NUMBER => X_OBJECT_VERSION_NUMBER,
727 X_INCIDENT_SUBTYPE => X_INCIDENT_SUBTYPE,
728 X_SEEDED_FLAG => l_seeded_flag,
729 X_STATUS_CODE => X_STATUS_CODE,
730 X_RESPONDED_FLAG => X_RESPONDED_FLAG,
731 X_RESOLVED_FLAG => X_RESOLVED_FLAG,
732 X_ON_HOLD_FLAG => X_ON_HOLD_FLAG,
733 X_STATUS_CLASS_CODE => X_STATUS_CLASS_CODE,
734 X_CLOSE_FLAG => X_CLOSE_FLAG,
735 X_VALID_IN_CREATE_FLAG => X_VALID_IN_CREATE_FLAG,
736 X_DISPLAY_COLOR => X_DISPLAY_COLOR,
737 X_RGB_COLOR => X_RGB_COLOR,
738 X_START_DATE_ACTIVE => to_date(X_START_DATE_ACTIVE,'YYYY/MM/DD'),
739 X_END_DATE_ACTIVE => to_date(X_END_DATE_ACTIVE,'YYYY/MM/DD'),
740 X_ATTRIBUTE1 => null,
741 X_ATTRIBUTE2 => null,
742 X_ATTRIBUTE3 => null,
743 X_ATTRIBUTE4 => null,
744 X_ATTRIBUTE5 => null,
745 X_ATTRIBUTE6 => null,
746 X_ATTRIBUTE7 => null,
747 X_ATTRIBUTE8 => null,
748 X_ATTRIBUTE9 => null,
749 X_NAME => X_NAME,
750 X_DESCRIPTION => X_DESCRIPTION,
751 X_CREATION_DATE => SYSDATE,
752 X_CREATED_BY => l_user_id,
753 X_LAST_UPDATE_DATE => SYSDATE,
754 X_LAST_UPDATED_BY => l_user_id,
755 X_LAST_UPDATE_LOGIN => 0,
756 X_SORT_ORDER => X_SORT_ORDER,
757 p_PENDING_APPROVAL_FLAG => p_PENDING_APPROVAL_FLAG,
758 p_INTERMEDIATE_STATUS_ID => p_INTERMEDIATE_STATUS_ID,
759 p_REJECTION_ACTION_STATUS_ID => p_REJECTION_ACTION_STATUS_ID,
760 p_APPROVAL_ACTION_STATUS_ID => p_APPROVAL_ACTION_STATUS_ID,
761 X_DUP_CHK_FLAG => X_DUP_CHK_FLAG -- shachoud
762
763 );
764 end LOAD_ROW;
765
766 procedure DELETE_ROW (
767 X_INCIDENT_STATUS_ID in NUMBER
768 ) is
769 begin
770 delete from CS_INCIDENT_STATUSES_TL
771 where INCIDENT_STATUS_ID = X_INCIDENT_STATUS_ID;
772
773 if (sql%notfound) then
774 raise no_data_found;
775 end if;
776
777 delete from CS_INCIDENT_STATUSES_B
778 where INCIDENT_STATUS_ID = X_INCIDENT_STATUS_ID;
779
780 if (sql%notfound) then
781 raise no_data_found;
782 end if;
783 end DELETE_ROW;
784
785 procedure ADD_LANGUAGE
786 is
787 begin
788 delete from CS_INCIDENT_STATUSES_TL T
789 where not exists
790 (select NULL
791 from CS_INCIDENT_STATUSES_B B
792 where B.INCIDENT_STATUS_ID = T.INCIDENT_STATUS_ID
793 );
794
795 update CS_INCIDENT_STATUSES_TL T set (
796 NAME,
797 DESCRIPTION
798 ) = (select
799 B.NAME,
800 B.DESCRIPTION
801 from CS_INCIDENT_STATUSES_TL B
802 where B.INCIDENT_STATUS_ID = T.INCIDENT_STATUS_ID
803 and B.LANGUAGE = T.SOURCE_LANG)
804 where (
805 T.INCIDENT_STATUS_ID,
806 T.LANGUAGE
807 ) in (select
808 SUBT.INCIDENT_STATUS_ID,
809 SUBT.LANGUAGE
810 from CS_INCIDENT_STATUSES_TL SUBB, CS_INCIDENT_STATUSES_TL SUBT
811 where SUBB.INCIDENT_STATUS_ID = SUBT.INCIDENT_STATUS_ID
812 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
813 and (SUBB.NAME <> SUBT.NAME
817 ));
814 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
815 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
816 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
818
819 insert into CS_INCIDENT_STATUSES_TL (
820 INCIDENT_STATUS_ID,
821 LAST_UPDATE_DATE,
822 LAST_UPDATED_BY,
823 CREATION_DATE,
824 CREATED_BY,
825 LAST_UPDATE_LOGIN,
826 NAME,
827 DESCRIPTION,
828 LANGUAGE,
829 SOURCE_LANG
830 ) select
831 B.INCIDENT_STATUS_ID,
832 B.LAST_UPDATE_DATE,
833 B.LAST_UPDATED_BY,
834 B.CREATION_DATE,
835 B.CREATED_BY,
836 B.LAST_UPDATE_LOGIN,
837 B.NAME,
838 B.DESCRIPTION,
839 L.LANGUAGE_CODE,
840 B.SOURCE_LANG
841 from CS_INCIDENT_STATUSES_TL B, FND_LANGUAGES L
842 where L.INSTALLED_FLAG in ('I', 'B')
843 and B.LANGUAGE = userenv('LANG')
844 and not exists
845 (select NULL
846 from CS_INCIDENT_STATUSES_TL T
847 where T.INCIDENT_STATUS_ID = B.INCIDENT_STATUS_ID
848 and T.LANGUAGE = L.LANGUAGE_CODE);
849 end ADD_LANGUAGE;
850
851 end CS_INCIDENT_STATUSES_PKG;