[Home] [Help]
PACKAGE BODY: APPS.AMW_PROCESS_OBJECTIVES_B_PKG
Source
1 PACKAGE BODY AMW_PROCESS_OBJECTIVES_B_PKG as
2 /* $Header: amwtprob.pls 115.1 2004/02/06 01:00:12 abedajna noship $ */
3 -- ===============================================================
4 -- Start of Comments
5 -- Package name
6 -- AMW_PROCESS_OBJECTIVES_B_PKG
7 -- Purpose
8 --
9 -- History
10 --
11 -- NOTE
12 --
13 -- End of Comments
14 -- ===============================================================
15
16
17 G_PKG_NAME CONSTANT VARCHAR2(30):= 'AMW_PROCESS_OBJECTIVES_B_PKG';
18 G_FILE_NAME CONSTANT VARCHAR2(12) := 'amwtprob.pls';
19
20
21 ----------------------------------------------------------
22 ---- MEDIA ----
23 ----------------------------------------------------------
24
25 -- ========================================================
26 --
27 -- NAME
28 -- createInsertBody
29 --
30 -- PURPOSE
31 --
32 -- NOTES
33 --
34 -- HISTORY
35 --
36 -- ========================================================
37 PROCEDURE Insert_Row(
38 X_ROWID in out NOCOPY VARCHAR2,
39 x_last_updated_by NUMBER,
40 x_last_update_date DATE,
41 x_created_by NUMBER,
42 x_creation_date DATE,
43 x_last_update_login NUMBER,
44 x_objective_type VARCHAR2,
45 x_start_date DATE,
46 x_end_date DATE,
47 x_attribute_category VARCHAR2,
48 x_attribute1 VARCHAR2,
49 x_attribute2 VARCHAR2,
50 x_attribute3 VARCHAR2,
51 x_attribute4 VARCHAR2,
52 x_attribute5 VARCHAR2,
53 x_attribute6 VARCHAR2,
54 x_attribute7 VARCHAR2,
55 x_attribute8 VARCHAR2,
56 x_attribute9 VARCHAR2,
57 x_attribute10 VARCHAR2,
58 x_attribute11 VARCHAR2,
59 x_attribute12 VARCHAR2,
60 x_attribute13 VARCHAR2,
61 x_attribute14 VARCHAR2,
62 x_attribute15 VARCHAR2,
63 x_security_group_id NUMBER,
64 x_object_version_number NUMBER,
65 x_process_objective_id NUMBER,
66 x_requestor_id NUMBER,
67 X_NAME in VARCHAR2,
68 X_DESCRIPTION in VARCHAR2)
69
70 IS
71 cursor C is select ROWID from AMW_process_objectives_B
72 where process_objective_ID = x_process_objective_id;
73 ---- x_rowid VARCHAR2(30);
74
75
76 BEGIN
77
78
79 INSERT INTO AMW_PROCESS_OBJECTIVES_B(
80 last_updated_by,
81 last_update_date,
82 created_by,
83 creation_date,
84 last_update_login,
85 objective_type,
86 start_date,
87 end_date,
88 attribute_category,
89 attribute1,
90 attribute2,
91 attribute3,
92 attribute4,
93 attribute5,
94 attribute6,
95 attribute7,
96 attribute8,
97 attribute9,
98 attribute10,
99 attribute11,
100 attribute12,
101 attribute13,
102 attribute14,
103 attribute15,
104 security_group_id,
105 object_version_number,
106 process_objective_id,
107 requestor_id
108 ) VALUES (
109 DECODE( x_last_updated_by, FND_API.g_miss_num, NULL, x_last_updated_by),
110 DECODE( x_last_update_date, FND_API.g_miss_date, NULL, x_last_update_date),
111 DECODE( x_created_by, FND_API.g_miss_num, NULL, x_created_by),
112 DECODE( x_creation_date, FND_API.g_miss_date, NULL, x_creation_date),
113 DECODE( x_last_update_login, FND_API.g_miss_num, NULL, x_last_update_login),
114 DECODE( x_objective_type, FND_API.g_miss_char, NULL, x_objective_type),
115 DECODE( x_start_date, FND_API.g_miss_date, NULL, x_start_date),
116 DECODE( x_end_date, FND_API.g_miss_date, NULL, x_end_date),
117 DECODE( x_attribute_category, FND_API.g_miss_char, NULL, x_attribute_category),
118 DECODE( x_attribute1, FND_API.g_miss_char, NULL, x_attribute1),
119 DECODE( x_attribute2, FND_API.g_miss_char, NULL, x_attribute2),
120 DECODE( x_attribute3, FND_API.g_miss_char, NULL, x_attribute3),
121 DECODE( x_attribute4, FND_API.g_miss_char, NULL, x_attribute4),
122 DECODE( x_attribute5, FND_API.g_miss_char, NULL, x_attribute5),
123 DECODE( x_attribute6, FND_API.g_miss_char, NULL, x_attribute6),
124 DECODE( x_attribute7, FND_API.g_miss_char, NULL, x_attribute7),
125 DECODE( x_attribute8, FND_API.g_miss_char, NULL, x_attribute8),
126 DECODE( x_attribute9, FND_API.g_miss_char, NULL, x_attribute9),
127 DECODE( x_attribute10, FND_API.g_miss_char, NULL, x_attribute10),
128 DECODE( x_attribute11, FND_API.g_miss_char, NULL, x_attribute11),
129 DECODE( x_attribute12, FND_API.g_miss_char, NULL, x_attribute12),
130 DECODE( x_attribute13, FND_API.g_miss_char, NULL, x_attribute13),
131 DECODE( x_attribute14, FND_API.g_miss_char, NULL, x_attribute14),
132 DECODE( x_attribute15, FND_API.g_miss_char, NULL, x_attribute15),
133 DECODE( x_security_group_id, FND_API.g_miss_num, NULL, x_security_group_id),
134 DECODE( x_object_version_number, FND_API.g_miss_num, NULL, x_object_version_number),
135 DECODE( x_process_objective_id, FND_API.g_miss_num, NULL, x_process_objective_id),
136 DECODE( x_requestor_id, FND_API.g_miss_num, NULL, x_requestor_id));
137
138 insert into AMW_process_objectives_TL (
139 process_objective_id,
140 NAME,
141 DESCRIPTION,
142 LAST_UPDATE_DATE,
143 LAST_UPDATED_BY,
144 CREATION_DATE,
145 CREATED_BY,
146 LAST_UPDATE_LOGIN,
147 SECURITY_GROUP_ID,
148 LANGUAGE,
149 SOURCE_LANG
150 ) select
151 X_process_objective_ID,
152 X_NAME,
153 X_DESCRIPTION,
154 X_LAST_UPDATE_DATE,
155 X_LAST_UPDATED_BY,
156 X_CREATION_DATE,
157 X_CREATED_BY,
158 X_LAST_UPDATE_LOGIN,
159 X_SECURITY_GROUP_ID,
160 L.LANGUAGE_CODE,
161 userenv('LANG')
162 from FND_LANGUAGES L
163 where L.INSTALLED_FLAG in ('I', 'B')
164 and not exists
165 (select NULL
166 from AMW_process_objectives_TL T
167 where T.process_objective_ID = X_process_objective_ID
168 and T.LANGUAGE = L.LANGUAGE_CODE);
169
170 open c;
171 fetch c into X_ROWID;
172 if (c%notfound) then
173 close c;
174 raise no_data_found;
175 end if;
176 close c;
177
178
179 END Insert_Row;
180
181
182 ----------------------------------------------------------
183 ---- MEDIA ----
184 ----------------------------------------------------------
185
186 -- ========================================================
187 --
188 -- NAME
189 -- createUpdateBody
190 --
191 -- PURPOSE
192 --
193 -- NOTES
194 --
195 -- HISTORY
196 --
197 -- ========================================================
198 PROCEDURE Update_Row(
199 x_last_updated_by NUMBER,
200 x_last_update_date DATE,
201 --x_created_by NUMBER,
202 --x_creation_date DATE,
203 x_last_update_login NUMBER,
204 x_objective_type VARCHAR2,
205 x_start_date DATE,
206 x_end_date DATE,
207 x_attribute_category VARCHAR2,
208 x_attribute1 VARCHAR2,
209 x_attribute2 VARCHAR2,
210 x_attribute3 VARCHAR2,
211 x_attribute4 VARCHAR2,
212 x_attribute5 VARCHAR2,
213 x_attribute6 VARCHAR2,
214 x_attribute7 VARCHAR2,
215 x_attribute8 VARCHAR2,
216 x_attribute9 VARCHAR2,
217 x_attribute10 VARCHAR2,
218 x_attribute11 VARCHAR2,
219 x_attribute12 VARCHAR2,
220 x_attribute13 VARCHAR2,
221 x_attribute14 VARCHAR2,
222 x_attribute15 VARCHAR2,
223 x_security_group_id NUMBER,
224 x_object_version_number NUMBER,
225 x_process_objective_id NUMBER,
226 X_NAME in VARCHAR2,
227 X_DESCRIPTION in VARCHAR2,
228 x_requestor_id NUMBER)
229
230 IS
231 BEGIN
232 Update AMW_PROCESS_OBJECTIVES_B
233 SET
234 last_updated_by = DECODE( x_last_updated_by, FND_API.g_miss_num, last_updated_by, x_last_updated_by),
235 last_update_date = DECODE( x_last_update_date, FND_API.g_miss_date, last_update_date, x_last_update_date),
236 --created_by = DECODE( x_created_by, FND_API.g_miss_num, created_by, x_created_by),
237 --creation_date = DECODE( x_creation_date, FND_API.g_miss_date, creation_date, x_creation_date),
238 last_update_login = DECODE( x_last_update_login, FND_API.g_miss_num, last_update_login, x_last_update_login),
242 attribute_category = DECODE( x_attribute_category, FND_API.g_miss_char, attribute_category, x_attribute_category),
239 objective_type = DECODE( x_objective_type, FND_API.g_miss_char, objective_type, x_objective_type),
240 start_date = DECODE( x_start_date, FND_API.g_miss_date, start_date, x_start_date),
241 end_date = DECODE( x_end_date, FND_API.g_miss_date, end_date, x_end_date),
243 attribute1 = DECODE( x_attribute1, FND_API.g_miss_char, attribute1, x_attribute1),
244 attribute2 = DECODE( x_attribute2, FND_API.g_miss_char, attribute2, x_attribute2),
245 attribute3 = DECODE( x_attribute3, FND_API.g_miss_char, attribute3, x_attribute3),
246 attribute4 = DECODE( x_attribute4, FND_API.g_miss_char, attribute4, x_attribute4),
247 attribute5 = DECODE( x_attribute5, FND_API.g_miss_char, attribute5, x_attribute5),
248 attribute6 = DECODE( x_attribute6, FND_API.g_miss_char, attribute6, x_attribute6),
249 attribute7 = DECODE( x_attribute7, FND_API.g_miss_char, attribute7, x_attribute7),
250 attribute8 = DECODE( x_attribute8, FND_API.g_miss_char, attribute8, x_attribute8),
251 attribute9 = DECODE( x_attribute9, FND_API.g_miss_char, attribute9, x_attribute9),
252 attribute10 = DECODE( x_attribute10, FND_API.g_miss_char, attribute10, x_attribute10),
253 attribute11 = DECODE( x_attribute11, FND_API.g_miss_char, attribute11, x_attribute11),
254 attribute12 = DECODE( x_attribute12, FND_API.g_miss_char, attribute12, x_attribute12),
255 attribute13 = DECODE( x_attribute13, FND_API.g_miss_char, attribute13, x_attribute13),
256 attribute14 = DECODE( x_attribute14, FND_API.g_miss_char, attribute14, x_attribute14),
257 attribute15 = DECODE( x_attribute15, FND_API.g_miss_char, attribute15, x_attribute15),
258 security_group_id = DECODE( x_security_group_id, FND_API.g_miss_num, security_group_id, x_security_group_id),
259 object_version_number = DECODE( x_object_version_number, FND_API.g_miss_num, object_version_number, x_object_version_number)
260 ---process_objective_id = DECODE( x_process_objective_id, FND_API.g_miss_num, process_objective_id, x_process_objective_id)
261 WHERE PROCESS_OBJECTIVE_ID = x_PROCESS_OBJECTIVE_ID;
262 ---AND object_version_number = x_object_version_number;
263
264 IF (SQL%NOTFOUND) THEN
265 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
266 END IF;
267
268 update AMW_PROCESS_OBJECTIVEs_TL set
269 NAME = X_NAME,
270 DESCRIPTION = X_DESCRIPTION,
271 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
272 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
273 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
274 SOURCE_LANG = userenv('LANG')
275 where PROCESS_OBJECTIVE_ID = x_PROCESS_OBJECTIVE_ID
276 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
277
278 if (sql%notfound) then
279 raise FND_API.G_EXC_UNEXPECTED_ERROR;
280 end if;
281 END Update_Row;
282
283
284 ----------------------------------------------------------
285 ---- MEDIA ----
286 ----------------------------------------------------------
287
288 -- ========================================================
289 --
290 -- NAME
291 -- createDeleteBody
292 --
293 -- PURPOSE
294 --
295 -- NOTES
296 --
297 -- HISTORY
298 --
299 -- ========================================================
300 PROCEDURE Delete_Row(
301 x_PROCESS_OBJECTIVE_ID NUMBER)
302 IS
303 BEGIN
304 delete from AMW_process_objectives_TL
305 where process_objective_ID = X_process_objective_ID;
306
307 if (sql%notfound) then
308 raise FND_API.G_EXC_UNEXPECTED_ERROR;
309 end if;
310
311 DELETE FROM AMW_PROCESS_OBJECTIVES_B
312 WHERE PROCESS_OBJECTIVE_ID = x_PROCESS_OBJECTIVE_ID;
313 If (SQL%NOTFOUND) then
314 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
315 End If;
316 END Delete_Row ;
317
318
319
320 ----------------------------------------------------------
321 ---- MEDIA ----
322 ----------------------------------------------------------
323
324 -- ========================================================
325 --
326 -- NAME
327 -- createLockBody
328 --
329 -- PURPOSE
330 --
331 -- NOTES
332 --
333 -- HISTORY
334 --
335 -- ========================================================
336 PROCEDURE Lock_Row(
337 --x_last_updated_by NUMBER,
338 --x_last_update_date DATE,
339 --x_created_by NUMBER,
340 --x_creation_date DATE,
341 --x_last_update_login NUMBER,
342 x_objective_type VARCHAR2,
343 x_start_date DATE,
344 x_end_date DATE,
345 x_attribute_category VARCHAR2,
346 x_attribute1 VARCHAR2,
347 x_attribute2 VARCHAR2,
348 x_attribute3 VARCHAR2,
349 x_attribute4 VARCHAR2,
350 x_attribute5 VARCHAR2,
351 x_attribute6 VARCHAR2,
352 x_attribute7 VARCHAR2,
353 x_attribute8 VARCHAR2,
354 x_attribute9 VARCHAR2,
355 x_attribute10 VARCHAR2,
356 x_attribute11 VARCHAR2,
357 x_attribute12 VARCHAR2,
358 x_attribute13 VARCHAR2,
359 x_attribute14 VARCHAR2,
360 x_attribute15 VARCHAR2,
361 x_security_group_id NUMBER,
365 X_DESCRIPTION in VARCHAR2,
362 x_object_version_number NUMBER,
363 x_process_objective_id NUMBER,
364 X_NAME in VARCHAR2,
366 x_requestor_id NUMBER)
367
368 IS
369 CURSOR C IS
370 SELECT objective_type,
371 start_date,
372 end_date,
373 attribute_category,
374 attribute1,
375 attribute2,
376 attribute3,
377 attribute4,
378 attribute5,
379 attribute6,
380 attribute7,
381 attribute8,
382 attribute9,
383 attribute10,
384 attribute11,
385 attribute12,
386 attribute13,
387 attribute14,
388 attribute15,
389 security_group_id,
390 object_version_number,
391 requestor_id
392 FROM AMW_PROCESS_OBJECTIVES_B
393 WHERE PROCESS_OBJECTIVE_ID = x_PROCESS_OBJECTIVE_ID
394 FOR UPDATE of PROCESS_OBJECTIVE_ID NOWAIT;
395 Recinfo C%ROWTYPE;
396
397 cursor c1 is select
398 NAME,
399 DESCRIPTION,
400 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
401 from AMW_PROCESS_OBJECTIVES_TL
402 where PROCESS_OBJECTIVE_ID = X_PROCESS_OBJECTIVE_ID
403 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
404 for update of PROCESS_OBJECTIVE_ID nowait;
405
406 BEGIN
407 OPEN c;
408 FETCH c INTO Recinfo;
409 If (c%NOTFOUND) then
410 CLOSE c;
411 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
412 APP_EXCEPTION.RAISE_EXCEPTION;
413 END IF;
414 CLOSE C;
415 IF (
416 /**
417 ( Recinfo.last_updated_by = x_last_updated_by)
418 AND ( ( Recinfo.last_update_date = x_last_update_date)
419 OR ( ( Recinfo.last_update_date IS NULL ) AND ( x_last_update_date IS NULL )))
420 AND ( ( Recinfo.created_by = x_created_by)
421 OR ( ( Recinfo.created_by IS NULL ) AND ( x_created_by IS NULL )))
422 AND ( ( Recinfo.creation_date = x_creation_date)
423 OR ( ( Recinfo.creation_date IS NULL ) AND ( x_creation_date IS NULL )))
424 AND ( ( Recinfo.last_update_login = x_last_update_login)
425 OR ( ( Recinfo.last_update_login IS NULL ) AND ( x_last_update_login IS NULL )))
426 AND
427 **/
428 ( ( Recinfo.objective_type = x_objective_type)
429 OR ( ( Recinfo.objective_type IS NULL )
430 AND ( x_objective_type IS NULL )))
431 /**AND ( ( Recinfo.start_date = x_start_date)
432 OR ( ( Recinfo.start_date IS NULL )
433 AND ( x_start_date IS NULL )))
434 AND ( ( Recinfo.end_date = x_end_date)
435 OR ( ( Recinfo.end_date IS NULL )
436 AND ( x_end_date IS NULL )))
437 ***/
438 AND ( ( Recinfo.attribute_category = x_attribute_category)
439 OR ( ( Recinfo.attribute_category IS NULL )
440 AND ( x_attribute_category IS NULL )))
441 AND ( ( Recinfo.attribute1 = x_attribute1)
442 OR ( ( Recinfo.attribute1 IS NULL )
443 AND ( x_attribute1 IS NULL )))
444 AND ( ( Recinfo.attribute2 = x_attribute2)
445 OR ( ( Recinfo.attribute2 IS NULL )
446 AND ( x_attribute2 IS NULL )))
447 AND ( ( Recinfo.attribute3 = x_attribute3)
448 OR ( ( Recinfo.attribute3 IS NULL )
449 AND ( x_attribute3 IS NULL )))
450 AND ( ( Recinfo.attribute4 = x_attribute4)
451 OR ( ( Recinfo.attribute4 IS NULL )
452 AND ( x_attribute4 IS NULL )))
453 AND ( ( Recinfo.attribute5 = x_attribute5)
454 OR ( ( Recinfo.attribute5 IS NULL )
455 AND ( x_attribute5 IS NULL )))
456 AND ( ( Recinfo.attribute6 = x_attribute6)
457 OR ( ( Recinfo.attribute6 IS NULL )
458 AND ( x_attribute6 IS NULL )))
459 AND ( ( Recinfo.attribute7 = x_attribute7)
460 OR ( ( Recinfo.attribute7 IS NULL )
461 AND ( x_attribute7 IS NULL )))
462 AND ( ( Recinfo.attribute8 = x_attribute8)
463 OR ( ( Recinfo.attribute8 IS NULL )
464 AND ( x_attribute8 IS NULL )))
468 AND ( ( Recinfo.attribute10 = x_attribute10)
465 AND ( ( Recinfo.attribute9 = x_attribute9)
466 OR ( ( Recinfo.attribute9 IS NULL )
467 AND ( x_attribute9 IS NULL )))
469 OR ( ( Recinfo.attribute10 IS NULL )
470 AND ( x_attribute10 IS NULL )))
471 AND ( ( Recinfo.attribute11 = x_attribute11)
472 OR ( ( Recinfo.attribute11 IS NULL )
473 AND ( x_attribute11 IS NULL )))
474 AND ( ( Recinfo.attribute12 = x_attribute12)
475 OR ( ( Recinfo.attribute12 IS NULL )
476 AND ( x_attribute12 IS NULL )))
477 AND ( ( Recinfo.attribute13 = x_attribute13)
478 OR ( ( Recinfo.attribute13 IS NULL )
479 AND ( x_attribute13 IS NULL )))
480 AND ( ( Recinfo.attribute14 = x_attribute14)
481 OR ( ( Recinfo.attribute14 IS NULL )
482 AND ( x_attribute14 IS NULL )))
483 AND ( ( Recinfo.attribute15 = x_attribute15)
484 OR ( ( Recinfo.attribute15 IS NULL )
485 AND ( x_attribute15 IS NULL )))
486 AND ( ( Recinfo.security_group_id = x_security_group_id)
487 OR ( ( Recinfo.security_group_id IS NULL )
488 AND ( x_security_group_id IS NULL )))
489 AND ( ( Recinfo.object_version_number = x_object_version_number)
490 OR ( ( Recinfo.object_version_number IS NULL )
491 AND ( x_object_version_number IS NULL )))
492 AND ( ( Recinfo.requestor_id = x_requestor_id)
496 OR ( ( Recinfo.process_objective_id IS NULL )
493 OR ( ( Recinfo.requestor_id IS NULL )
494 AND ( x_requestor_id IS NULL )))
495 /***AND ( ( Recinfo.process_objective_id = x_process_objective_id)
497 AND ( x_process_objective_id IS NULL )))
501 ELSE
498 ***/
499 ) THEN
500 null;
502 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
503 ---fnd_message.set_name('FND', 'OKC_FORM_RECORD_CHANGED');
504 APP_EXCEPTION.RAISE_EXCEPTION;
505 END IF;
506
507 for tlinfo in c1 loop
508 if (tlinfo.BASELANG = 'Y') then
509 if ( (tlinfo.NAME = X_NAME)
510 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
511 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
512 /*** AND ((tlinfo.PHYSICAL_EVIDENCE = X_PHYSICAL_EVIDENCE)
513 OR ((tlinfo.PHYSICAL_EVIDENCE is null) AND (X_PHYSICAL_EVIDENCE is null)))
514 ***/
515 ) then
516 null;
517 else
518 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
519 ---fnd_message.set_name('FND', 'OKC_FORM_RECORD_CHANGED');
525 return;
520 app_exception.raise_exception;
521 end if;
522 end if;
523 end loop;
524
526 END Lock_Row;
527
528 procedure ADD_LANGUAGE
529 is
530 begin
531 delete from AMW_process_objectives_TL T
532 where not exists
533 (select NULL
534 from AMW_process_objectives_B B
535 where B.process_objective_id = T.process_objective_id
536 );
537
538 update AMW_process_objectives_TL T set (
539 NAME,
540 DESCRIPTION) = (select
541 B.NAME,
542 B.DESCRIPTION
543 from AMW_process_objectives_TL B
544 where B.process_objective_id = T.process_objective_id
545 and B.LANGUAGE = T.SOURCE_LANG)
546 where (
547 T.process_objective_id,
548 T.LANGUAGE
549 ) in (select
550 SUBT.process_objective_id,
551 SUBT.LANGUAGE
557 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
552 from AMW_process_objectives_TL SUBB, AMW_process_objectives_TL SUBT
553 where SUBB.process_objective_id = SUBT.process_objective_id
554 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
555 and (SUBB.NAME <> SUBT.NAME
556 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
558 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
559 ));
560
561 insert into AMW_process_objectives_TL (
562 process_objective_id,
563 NAME,
564 DESCRIPTION,
565 LAST_UPDATE_DATE,
566 LAST_UPDATED_BY,
567 CREATION_DATE,
568 CREATED_BY,
569 LAST_UPDATE_LOGIN,
570 SECURITY_GROUP_ID,
571 LANGUAGE,
572 SOURCE_LANG
573 ) select
574 B.process_objective_id,
575 B.NAME,
576 B.DESCRIPTION,
577 B.LAST_UPDATE_DATE,
578 B.LAST_UPDATED_BY,
579 B.CREATION_DATE,
580 B.CREATED_BY,
581 B.LAST_UPDATE_LOGIN,
582 B.SECURITY_GROUP_ID,
583 L.LANGUAGE_CODE,
584 B.SOURCE_LANG
585 from AMW_process_objectives_TL B, FND_LANGUAGES L
586 where L.INSTALLED_FLAG in ('I', 'B')
587 and B.LANGUAGE = userenv('LANG')
588 and not exists
589 (select NULL
590 from AMW_process_objectives_TL T
591 where T.process_objective_id = B.process_objective_id
592 and T.LANGUAGE = L.LANGUAGE_CODE);
593 end ADD_LANGUAGE;
594
595
596
597 procedure delete_proc_obj (
598 p_object_type varchar2,
599 p_pk1 number,
600 p_commit in varchar2 := FND_API.G_FALSE,
601 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
602 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
603 x_return_status out nocopy varchar2,
604 x_msg_count out nocopy number,
605 x_msg_data out nocopy varchar2
606 ) is
607
608 L_API_NAME CONSTANT VARCHAR2(30) := 'delete_proc_obj';
609
610 begin
611 x_return_status := FND_API.G_RET_STS_SUCCESS;
612 IF FND_API.to_Boolean( p_init_msg_list ) THEN
613 FND_MSG_PUB.initialize;
614 END IF;
615 IF FND_GLOBAL.User_Id IS NULL THEN
616 AMW_Utility_PVT.Error_Message(p_message_name => 'USER_PROFILE_MISSING');
617 RAISE FND_API.G_EXC_ERROR;
618 END IF;
619
620 delete from amw_objective_associations where object_type = p_object_type and pk1 = p_pk1;
621
622 exception
623 WHEN FND_API.G_EXC_ERROR THEN
624 ROLLBACK;
625 x_return_status := FND_API.G_RET_STS_ERROR;
626 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,p_count => x_msg_count,p_data => x_msg_data);
627
628 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
629 ROLLBACK;
630 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
631 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,p_count => x_msg_count,p_data => x_msg_data);
632
633 WHEN OTHERS THEN
634 ROLLBACK;
635 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
636 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
637 THEN
638 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,l_api_name);
639 END IF;
640 FND_MSG_PUB.Count_And_Get (p_encoded => FND_API.G_FALSE,p_count => x_msg_count,p_data => x_msg_data);
641
642 end delete_proc_obj;
643
644
645
646 END AMW_PROCESS_OBJECTIVES_B_PKG;