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