[Home] [Help]
PACKAGE BODY: APPS.GR_RISK_SAFETY_PHRASES_PUB
Source
1 PACKAGE BODY GR_RISK_SAFETY_PHRASES_PUB AS
2 /* $Header: GRRISAPB.pls 120.2 2010/11/19 16:24:28 plowe noship $
3 *****************************************************************
4 * *
5 * Package GR_RISK_SAFETY_PHRASES_PUB *
6 * *
7 * Contents RISK_SAFETY_PHRASES *
8 * *
9 * *
10 * Use This is the public layer for the RISK_SAFETY_PHRASES *
11 * API *
12 * *
13 * History *
14 * Written by Raju OPM Unlimited Dev *
15 * Peter Lowe 06/30/08 *
16 * *
17 *****************************************************************
18 */
19
20 -- Global variables
21
22 G_PKG_NAME CONSTANT VARCHAR2(30):='GR_RISK_SAFETY_PHRASES_PUB';
23
24 --Forward declaration.
25 FUNCTION set_debug_flag RETURN VARCHAR2;
26 l_debug VARCHAR2(1) := set_debug_flag;
27
28 FUNCTION set_debug_flag RETURN VARCHAR2 IS
29 l_debug VARCHAR2(1):= 'N';
30 BEGIN
31 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
32 l_debug := 'Y';
33 END IF;
34 l_debug := 'Y';
35 RETURN l_debug;
36 END set_debug_flag;
37
38 PROCEDURE RISK_SAFETY_PHRASES
39 ( p_api_version IN NUMBER
40 , p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE
41 , p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
42 , p_action IN VARCHAR2
43 , p_object IN VARCHAR2
44 , p_phrase_type IN VARCHAR2
45 , p_phrase_code IN VARCHAR2
46 , p_language IN VARCHAR2
47 , p_source_language IN VARCHAR2
48 , p_phrase_text IN VARCHAR2
49 , p_attribute_category IN VARCHAR2
50 , p_attribute1 IN VARCHAR2
51 , p_attribute2 IN VARCHAR2
52 , p_attribute3 IN VARCHAR2
53 , p_attribute4 IN VARCHAR2
54 , p_attribute5 IN VARCHAR2
55 , p_attribute6 IN VARCHAR2
56 , p_attribute7 IN VARCHAR2
57 , p_attribute8 IN VARCHAR2
58 , p_attribute9 IN VARCHAR2
59 , p_attribute10 IN VARCHAR2
60 , p_attribute11 IN VARCHAR2
61 , p_attribute12 IN VARCHAR2
62 , p_attribute13 IN VARCHAR2
63 , p_attribute14 IN VARCHAR2
64 , p_attribute15 IN VARCHAR2
65 , p_attribute16 IN VARCHAR2
66 , p_attribute17 IN VARCHAR2
67 , p_attribute18 IN VARCHAR2
68 , p_attribute19 IN VARCHAR2
69 , p_attribute20 IN VARCHAR2
70 , p_attribute21 IN VARCHAR2
71 , p_attribute22 IN VARCHAR2
72 , p_attribute23 IN VARCHAR2
73 , p_attribute24 IN VARCHAR2
74 , p_attribute25 IN VARCHAR2
75 , p_attribute26 IN VARCHAR2
76 , p_attribute27 IN VARCHAR2
77 , p_attribute28 IN VARCHAR2
78 , p_attribute29 IN VARCHAR2
79 , p_attribute30 IN VARCHAR2
80 , x_return_status OUT NOCOPY VARCHAR2
81 , x_msg_count OUT NOCOPY NUMBER
82 , x_msg_data OUT NOCOPY VARCHAR2) IS
83
84 --local variables
85 l_api_name CONSTANT VARCHAR2 (30) := 'RISK_SAFETY_PHRASES';
86 l_api_version CONSTANT NUMBER := 1.0;
87 l_msg_count NUMBER :=0;
88 l_debug_flag VARCHAR2(1) := 'N';-- set_debug_flag;
89 l_language_code VARCHAR2(4);
90 l_missing_count NUMBER;
91 l_last_update_login NUMBER(15,0) := 0;
92 l_key_exists VARCHAR2(1);
93
94
95 return_status VARCHAR2(1);
96 oracle_error NUMBER;
97 msg_data VARCHAR2(2000);
98 row_id VARCHAR2(18);
99 dummy NUMBER;
100
101 --Exception
102 LBins_err EXCEPTION;
103 LCins_err EXCEPTION;
104 LTadd_err EXCEPTION;
105 LTL_del_err EXCEPTION;
106 LT_EXISTS_ERROR EXCEPTION;
107 ROW_MISSING_ERROR EXCEPTION;
108
109 -- Cursor Definitions
110
111 CURSOR c_get_language IS
112 SELECT lng.language_code
113 FROM fnd_languages lng
114 WHERE lng.language_code = l_language_code;
115 LangRecord c_get_language%ROWTYPE;
116
117 CURSOR Cur_count_language IS
118 SELECT count (language_code)
119 FROM fnd_languages
120 WHERE installed_flag IN ('I', 'B')
121 AND language_code not in
122 (SELECT language
123 FROM gr_safety_phrases_TL
124 WHERE safety_phrase_code = p_phrase_code);
125
126 CURSOR Cur_count_risk_language IS
127 SELECT count (language_code)
128 FROM fnd_languages
129 WHERE installed_flag IN ('I', 'B')
130 AND language_code not in
131 (SELECT language
132 FROM gr_risk_phrases_TL
133 WHERE risk_phrase_code = p_phrase_code);
134
135 CURSOR c_get_safety_phrase_code IS
136 SELECT safety_phrase_code
137 FROM gr_safety_phrases_b
138 WHERE safety_phrase_code = p_phrase_code;
139 safetycode c_get_safety_phrase_code%ROWTYPE;
140
141 CURSOR c_get_safety_phrase_tl IS
142 SELECT 1
143 FROM gr_safety_phrases_TL
144 WHERE safety_phrase_code = p_phrase_code
145 AND language = p_language;
146
147 CURSOR c_get_risk_phrase_code IS
148 SELECT risk_phrase_code
149 FROM gr_risk_phrases_b
150 WHERE risk_phrase_code = p_phrase_code;
151 riskcode c_get_risk_phrase_code%ROWTYPE;
152
153 CURSOR c_get_risk_phrase_tl IS
154 SELECT 1
155 FROM gr_risk_phrases_TL
156 WHERE risk_phrase_code = p_phrase_code
157 AND language = p_language;
158
159 L_MSG_TOKEN VARCHAR2(100);
160 BEGIN
161 /* Standard call to check for call compatibility. */
162
163 IF NOT FND_API.Compatible_API_CALL
164 (l_api_version , p_api_version , l_api_name , G_PKG_NAME) THEN
165 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
166 END IF;
167
168 /* Initialize message list if p_int_msg_list is set TRUE. */
169 IF FND_API.to_boolean(p_init_msg_list) THEN
170 FND_MSG_PUB.Initialize;
171 END IF;
172
173 -- Initialize API return Parameters
174 x_return_status := FND_API.G_RET_STS_SUCCESS;
175
176 /* check mandatory inputs */
177
178 IF p_action is NULL or p_action not in ('I','U','D') then
179 FND_MESSAGE.SET_NAME('GR','GR_INVALID_ACTION');
180 RAISE FND_API.G_EXC_ERROR;
181 END IF;
182
183 IF p_object is NULL or p_object not in ('C','L') then
184 FND_MESSAGE.SET_NAME('GR','GR_INVALID_OBJECT');
185 RAISE FND_API.G_EXC_ERROR;
186 END IF;
187
188 IF p_phrase_type is NULL or p_phrase_type not in ('R','S') then
189 FND_MESSAGE.SET_NAME('GR','GR_INVALID_PHRASE');
190 RAISE FND_API.G_EXC_ERROR;
191 END IF;
192
193 IF p_phrase_code is NULL then
194 FND_MESSAGE.SET_NAME('GMA','SY_FIELDNAME');
195 RAISE FND_API.G_EXC_ERROR;
196 END IF;
197
198 IF p_action = 'I' then
199 -- Language, Source Language and Description values are required
200 -- and an error message will be written to the log file if any of the values are null.
201 IF p_language is NULL or p_source_language is NULL or p_phrase_text is null then
202 FND_MESSAGE.SET_NAME('GMA','SY_FIELDNAME');
203 RAISE FND_API.G_EXC_ERROR;
204 END IF;
205
206 IF p_phrase_type = 'S' THEN
207 IF p_object = 'C' then
208 -- Validate that the value of Safety phrase code does not already exist in the table
209 -- GR_SAFETY_PHRASES_B.If it does, write an error to the log file
210 OPEN c_get_safety_phrase_code;
211 FETCH c_get_safety_phrase_code INTO safetycode;
212 IF c_get_safety_phrase_code%NOTFOUND THEN
213 null;
214 ELSE
215 x_return_status := 'E';
216 l_msg_token := p_phrase_code;
217 CLOSE c_get_safety_phrase_code;
218 RAISE LT_Exists_Error;
219 END IF;
220 CLOSE c_get_safety_phrase_code;
221
222 -- insert a record for GR_SAFETY_PHRASES_B
223 gr_safety_phrases_b_pkg.Insert_Row
224 (p_commit => 'T',
225 p_called_by_form => 'F',
226 p_safety_phrase_code => p_phrase_code,
227 p_additional_text_type => 'N',
228 p_lookup_type => NULL ,
229 p_lookup_code => NULL ,
230 p_attribute_category => p_attribute_category ,
231 p_attribute1 => p_attribute1 ,
232 p_attribute2 => p_attribute2 ,
233 p_attribute3 => p_attribute3 ,
234 p_attribute4 => p_attribute4 ,
235 p_attribute5 => p_attribute5 ,
236 p_attribute6 => p_attribute6 ,
237 p_attribute7 => p_attribute7 ,
238 p_attribute8 => p_attribute8 ,
239 p_attribute9 => p_attribute9 ,
240 p_attribute10 => p_attribute10 ,
241 p_attribute11 => p_attribute11 ,
242 p_attribute12 => p_attribute12 ,
243 p_attribute13 => p_attribute13 ,
244 p_attribute14 => p_attribute14 ,
245 p_attribute15 => p_attribute15 ,
246 p_attribute16 => p_attribute16 ,
247 p_attribute17 => p_attribute17 ,
248 p_attribute18 => p_attribute18 ,
249 p_attribute19 => p_attribute19 ,
250 p_attribute20 => p_attribute20 ,
251 p_attribute21 => p_attribute21 ,
252 p_attribute22 => p_attribute22 ,
253 p_attribute23 => p_attribute23 ,
254 p_attribute24 => p_attribute24 ,
255 p_attribute25 => p_attribute25 ,
256 p_attribute26 => p_attribute26 ,
257 p_attribute27 => p_attribute27 ,
258 p_attribute28 => p_attribute28 ,
259 p_attribute29 => p_attribute29 ,
260 p_attribute30 => p_attribute30 ,
261 p_created_by => FND_GLOBAL.USER_ID,
262 p_creation_date => SYSDATE,
263 p_last_updated_by => FND_GLOBAL.USER_ID,
264 p_last_update_date => SYSDATE,
265 p_last_update_login => l_last_update_login,
266 x_rowid => row_id,
267 x_return_status => return_status,
268 x_oracle_error => oracle_error,
269 x_msg_data => msg_data);
270
271 IF return_status <> 'S' THEN
272 GMD_API_PUB.Log_Message(msg_data);
273 RAISE LBins_err;
274 END IF;
275
276 -- need to add base row for language for GR_SAFETY_PHRASES_TL
277 gr_safety_phrases_tl_pkg.insert_row(p_commit => 'T',
278 p_called_by_form => 'F',
279 p_safety_phrase_code => p_phrase_code,
280 p_language => p_language,
281 p_source_language => p_source_language,
282 p_safety_phrase_description => p_phrase_text,
283 p_created_by => fnd_global.user_id,
284 p_creation_date => sysdate,
285 p_last_updated_by => fnd_global.user_id,
286 p_last_update_date => sysdate,
287 p_last_update_login => l_last_update_login,
288 x_rowid => row_id,
289 x_return_status => return_status,
290 x_oracle_error => oracle_error,
291 x_msg_data => msg_data);
292
293 IF return_status <> 'S' THEN
294 GMD_API_PUB.Log_Message(msg_data);
295 RAISE LCins_err;
296 END IF;
297
298 -- Insert a record into GR_SAFETY_PHRASES_TL for each installed language.
299 OPEN Cur_count_language;
300 FETCH Cur_count_language INTO l_missing_count;
301 CLOSE Cur_count_language;
302 IF l_missing_count > 0 THEN
303 gr_safety_phrases_tl_pkg.Add_Language (p_commit => 'T',
304 p_called_by_form => 'F',
305 p_safety_phrase_code => p_phrase_code,
306 p_language => p_language,
307 x_return_status => return_status,
308 x_oracle_error => oracle_error,
309 x_msg_data => msg_data);
310 IF return_status <> 'S' THEN
311 GMD_API_PUB.Log_Message('GR_SAFET_PHRASE_ADD_LANG_ERROR');
312 FND_MESSAGE.SET_NAME('GR','GR_SAFET_PHRASE_ADD_LANG_ERROR');
313 FND_MESSAGE.SET_TOKEN('CODE',l_msg_token,FALSE);
314 FND_MSG_PUB.ADD;
315 RAISE LTadd_err;
316 END IF;
317 END IF; -- IF l_missing_count > 0 THEN
318
319 ELSIF p_object = 'L' then
320 -- Validate that the value of Safety phrase code does not already exist in the table
321 -- GR_SAFETY_PHRASES_B.If it does, write an error to the log file
322 OPEN c_get_safety_phrase_code;
323 FETCH c_get_safety_phrase_code INTO safetycode;
324 IF c_get_safety_phrase_code%NOTFOUND THEN
325 x_return_status := 'E';
326 l_msg_token := p_phrase_code;
327 CLOSE c_get_safety_phrase_code;
328 RAISE LT_Exists_Error;
329 END IF;
330 CLOSE c_get_safety_phrase_code;
331
332 -- Validate that the value of Language for the specified property does not exist in the table
333 -- GR_SAFETY_PHRASES_TL. if it does, write an error to the log file.
334
335 OPEN c_get_safety_phrase_tl;
336 FETCH c_get_safety_phrase_tl INTO dummy;
337 IF c_get_safety_phrase_tl%FOUND THEN
338 x_return_status := 'E';
339 l_msg_token := p_phrase_code|| ' ' || p_language;
340 CLOSE c_get_safety_phrase_tl;
341 RAISE LT_Exists_Error;
342 END IF;
343 CLOSE c_get_safety_phrase_tl;
344
345 -- The values for Source Language, Language and Description will be written to the GR_SAFETY_PHRASES_TL table;
346 gr_safety_phrases_tl_pkg.insert_row(p_commit => 'T',
347 p_called_by_form => 'F',
348 p_safety_phrase_code => p_phrase_code,
349 p_language => p_language,
350 p_source_language => p_source_language,
351 p_safety_phrase_description => p_phrase_text,
352 p_created_by => fnd_global.user_id,
353 p_creation_date => sysdate,
354 p_last_updated_by => fnd_global.user_id,
355 p_last_update_date => sysdate,
356 p_last_update_login => l_last_update_login,
357 x_rowid => row_id,
358 x_return_status => return_status,
359 x_oracle_error => oracle_error,
360 x_msg_data => msg_data);
361
362 IF return_status <> 'S' THEN
363 GMD_API_PUB.Log_Message(msg_data);
364 RAISE LCins_err;
365 END IF;
366 END IF; -- IF p_object = 'C' then
367
368 ELSIF p_phrase_type = 'R' THEN
369 IF p_object = 'C' then
370 -- Validate that the value of Risk phrase code does not already exist in the table
371 -- GR_RISK_PHRASES_B.If it does, write an error to the log file
372 OPEN c_get_risk_phrase_code;
373 FETCH c_get_risk_phrase_code INTO riskcode;
374 IF c_get_risk_phrase_code%NOTFOUND THEN
375 null;
376 ELSE
377 x_return_status := 'E';
378 l_msg_token := p_phrase_code;
379 CLOSE c_get_risk_phrase_code;
380 RAISE LT_Exists_Error;
381 END IF;
382 CLOSE c_get_risk_phrase_code;
383
384 -- insert a record for GR_RISK_PHRASES_B
385 gr_risk_phrases_b_pkg.Insert_Row
386 (p_commit => 'T',
387 p_called_by_form => 'F',
388 p_risk_phrase_code => p_phrase_code,
389 p_additional_text_indicator => 'N',
390 p_lookup_type => NULL ,
391 p_lookup_code => NULL ,
392 p_attribute_category => p_attribute_category ,
393 p_attribute1 => p_attribute1 ,
394 p_attribute2 => p_attribute2 ,
395 p_attribute3 => p_attribute3 ,
396 p_attribute4 => p_attribute4 ,
397 p_attribute5 => p_attribute5 ,
398 p_attribute6 => p_attribute6 ,
399 p_attribute7 => p_attribute7 ,
400 p_attribute8 => p_attribute8 ,
401 p_attribute9 => p_attribute9 ,
402 p_attribute10 => p_attribute10 ,
403 p_attribute11 => p_attribute11 ,
404 p_attribute12 => p_attribute12 ,
405 p_attribute13 => p_attribute13 ,
406 p_attribute14 => p_attribute14 ,
407 p_attribute15 => p_attribute15 ,
408 p_attribute16 => p_attribute16 ,
409 p_attribute17 => p_attribute17 ,
410 p_attribute18 => p_attribute18 ,
411 p_attribute19 => p_attribute19 ,
412 p_attribute20 => p_attribute20 ,
413 p_attribute21 => p_attribute21 ,
414 p_attribute22 => p_attribute22 ,
415 p_attribute23 => p_attribute23 ,
416 p_attribute24 => p_attribute24 ,
417 p_attribute25 => p_attribute25 ,
418 p_attribute26 => p_attribute26 ,
419 p_attribute27 => p_attribute27 ,
420 p_attribute28 => p_attribute28 ,
421 p_attribute29 => p_attribute29 ,
422 p_attribute30 => p_attribute30 ,
423 p_created_by => FND_GLOBAL.USER_ID,
424 p_creation_date => SYSDATE,
425 p_last_updated_by => FND_GLOBAL.USER_ID,
426 p_last_update_date => SYSDATE,
427 p_last_update_login => l_last_update_login,
428 x_rowid => row_id,
429 x_return_status => return_status,
430 x_oracle_error => oracle_error,
431 x_msg_data => msg_data);
432
433 IF return_status <> 'S' THEN
434 GMD_API_PUB.Log_Message(msg_data);
435 RAISE LBins_err;
436 END IF;
437
438 -- need to add base row for language for GR_RISK_PHRASES_TL
439 gr_risk_phrases_tl_pkg.insert_row(p_commit => 'T',
440 p_called_by_form => 'F',
441 p_risk_phrase_code => p_phrase_code,
442 p_language => p_language,
443 p_source_language => p_source_language,
444 p_risk_description => p_phrase_text,
445 p_created_by => fnd_global.user_id,
446 p_creation_date => sysdate,
447 p_last_updated_by => fnd_global.user_id,
448 p_last_update_date => sysdate,
449 p_last_update_login => l_last_update_login,
450 x_rowid => row_id,
451 x_return_status => return_status,
452 x_oracle_error => oracle_error,
453 x_msg_data => msg_data);
454
455 IF return_status <> 'S' THEN
456 GMD_API_PUB.Log_Message(msg_data);
457 RAISE LCins_err;
458 END IF;
459
460 -- Insert a record into GR_RISK_PHRASES_TL for each installed language.
461 OPEN Cur_count_risk_language;
462 FETCH Cur_count_risk_language INTO l_missing_count;
463 CLOSE Cur_count_risk_language;
464 IF l_missing_count > 0 THEN
465 gr_risk_phrases_tl_pkg.Add_Language (p_commit => 'T',
466 p_called_by_form => 'F',
467 p_risk_phrase_code => p_phrase_code,
468 p_language => p_language,
469 x_return_status => return_status,
470 x_oracle_error => oracle_error,
471 x_msg_data => msg_data);
472 IF return_status <> 'S' THEN
473 GMD_API_PUB.Log_Message('GR_RISK_PHRASE_ADD_LANG_ERROR');
474 FND_MESSAGE.SET_NAME('GR','GR_RISK_PHRASE_ADD_LANG_ERROR');
475 FND_MESSAGE.SET_TOKEN('CODE',l_msg_token,FALSE);
476 FND_MSG_PUB.ADD;
477 RAISE LTadd_err;
478 END IF;
479 END IF; -- IF l_missing_count > 0 THEN
480
481 ELSIF p_object = 'L' then
482 -- Validate that the value of Safety phrase code does not already exist in the table
483 -- GR_RISK_PHRASES_B.If it does, write an error to the log file
484 OPEN c_get_risk_phrase_code;
485 FETCH c_get_risk_phrase_code INTO safetycode;
486 IF c_get_risk_phrase_code%NOTFOUND THEN
487 x_return_status := 'E';
488 l_msg_token := p_phrase_code;
489 CLOSE c_get_risk_phrase_code;
490 RAISE LT_Exists_Error;
491 END IF;
492 CLOSE c_get_risk_phrase_code;
493
494 -- Validate that the value of Language for the specified risk phrase does not exist in the table
495 -- GR_RISK_PHRASES_TL.If it does, write an error to the log file.
496
497 OPEN c_get_risk_phrase_tl;
498 FETCH c_get_risk_phrase_tl INTO dummy;
499 IF c_get_risk_phrase_tl%FOUND THEN
500 x_return_status := 'E';
501 l_msg_token := p_phrase_code|| ' ' || p_language;
502 CLOSE c_get_risk_phrase_tl;
503 RAISE LT_Exists_Error;
504 END IF;
505 CLOSE c_get_risk_phrase_tl;
506
507 -- The values for Source Language, Language and Description will be written to the GR_RISK_PHRASES_TL table;
508 gr_risk_phrases_tl_pkg.insert_row(p_commit => 'T',
509 p_called_by_form => 'F',
510 p_risk_phrase_code => p_phrase_code,
511 p_language => p_language,
512 p_source_language => p_source_language,
513 p_risk_description => p_phrase_text,
514 p_created_by => fnd_global.user_id,
515 p_creation_date => sysdate,
516 p_last_updated_by => fnd_global.user_id,
517 p_last_update_date => sysdate,
518 p_last_update_login => l_last_update_login,
519 x_rowid => row_id,
520 x_return_status => return_status,
521 x_oracle_error => oracle_error,
522 x_msg_data => msg_data);
523
524 IF return_status <> 'S' THEN
525 GMD_API_PUB.Log_Message(msg_data);
526 RAISE LCins_err;
527 END IF;
528 END IF; -- IF p_object = 'C' then
529 END IF; -- IF p_phrase_type = 'S' THEN
530 ELSIF p_action = 'U' then
531 -- Validate that the value of Safety phrase code does not already exist in the table
532 -- GR_SAFETY_PHRASES_B.If it does, write an error to the log file
533 IF p_phrase_type = 'S' THEN
534 OPEN c_get_safety_phrase_code;
535 FETCH c_get_safety_phrase_code INTO safetycode;
536 IF c_get_safety_phrase_code%NOTFOUND THEN
537 x_return_status := 'E';
538 l_msg_token := p_phrase_code;
539 CLOSE c_get_safety_phrase_code;
540 RAISE LT_Exists_Error;
541 END IF;
542 CLOSE c_get_safety_phrase_code;
543 IF p_object = 'C' THEN
544 UPDATE GR_SAFETY_PHRASES_B
545 SET attribute_category = p_attribute_category ,
546 attribute1 = p_attribute1 ,
547 attribute2 = p_attribute2 ,
548 attribute3 = p_attribute3 ,
549 attribute4 = p_attribute4 ,
550 attribute5 = p_attribute5 ,
551 attribute6 = p_attribute6 ,
552 attribute7 = p_attribute7 ,
553 attribute8 = p_attribute8 ,
554 attribute9 = p_attribute9 ,
555 attribute10 = p_attribute10 ,
556 attribute11 = p_attribute11 ,
557 attribute12 = p_attribute12 ,
558 attribute13 = p_attribute13 ,
559 attribute14 = p_attribute14 ,
560 attribute15 = p_attribute15 ,
561 attribute16 = p_attribute16 ,
562 attribute17 = p_attribute17 ,
563 attribute18 = p_attribute18 ,
564 attribute19 = p_attribute19 ,
565 attribute20 = p_attribute20 ,
566 attribute21 = p_attribute21 ,
567 attribute22 = p_attribute22 ,
568 attribute23 = p_attribute23 ,
569 attribute24 = p_attribute24 ,
570 attribute25 = p_attribute25 ,
571 attribute26 = p_attribute26 ,
572 attribute27 = p_attribute27 ,
573 attribute28 = p_attribute28 ,
574 attribute29 = p_attribute29 ,
575 attribute30 = p_attribute30 ,
576 last_updated_by = FND_GLOBAL.USER_ID,
577 last_update_date = SYSDATE,
578 last_update_login = l_last_update_login
579 WHERE safety_phrase_code = p_phrase_code;
580 IF SQL%NOTFOUND THEN
581 RAISE Row_Missing_Error;
582 END IF;
583 ELSIF p_object = 'L' then
584 -- If the value for Language is null or invalid, write an error to the log file.
585 IF p_language is NULL then
586 l_msg_token := l_language_code;
587 RAISE Row_Missing_Error;
588 END IF;
589
590 /*Check the language codes */
591 l_language_code := p_language;
592 OPEN c_get_language;
593 FETCH c_get_language INTO LangRecord;
594 IF c_get_language%NOTFOUND THEN
595 CLOSE c_get_language;
596 l_msg_token := l_language_code;
597 RAISE Row_Missing_Error;
598 END IF;
599 CLOSE c_get_language;
600 -- If the record for the specified safety phrase and language does not exist in the
601 -- GR_SAFETY_PHRASES_TL table,an error will be written to the log file.
602 gr_safety_phrases_tl_pkg.Check_Primary_Key(
603 p_phrase_code,
604 p_language,
605 'F',
606 row_id,
607 l_key_exists);
608
609 IF l_key_exists = 'N' THEN
610 l_msg_token := p_phrase_code|| ' ' || p_language;
611 RAISE Row_Missing_Error;
612 END IF;
613 -- The value for Description will be updated GR_SAFETY_PHRASES_TL table for the specified language.
614 UPDATE GR_SAFETY_PHRASES_TL
615 SET safety_phrase_description = p_phrase_text,
616 source_lang = p_source_language,
617 last_updated_by = FND_GLOBAL.USER_ID,
618 last_update_date = SYSDATE,
619 last_update_login = l_last_update_login
620 WHERE safety_phrase_code = p_phrase_code
621 AND language = p_language;
622 IF SQL%NOTFOUND THEN
623 l_msg_token := p_phrase_code || ' ' || p_language;
624 RAISE Row_Missing_Error;
625 END IF;
626 END IF;
627
628 ELSIF p_phrase_type = 'R' THEN
629 OPEN c_get_risk_phrase_code;
630 FETCH c_get_risk_phrase_code INTO riskcode;
631 IF c_get_risk_phrase_code%NOTFOUND THEN
632 x_return_status := 'E';
633 l_msg_token := p_phrase_code;
634 CLOSE c_get_risk_phrase_code;
635 RAISE LT_Exists_Error;
636 END IF;
637 CLOSE c_get_risk_phrase_code;
638 IF p_object = 'C' THEN
639 UPDATE GR_RISK_PHRASES_B
640 SET attribute_category = p_attribute_category ,
641 attribute1 = p_attribute1 ,
642 attribute2 = p_attribute2 ,
643 attribute3 = p_attribute3 ,
644 attribute4 = p_attribute4 ,
645 attribute5 = p_attribute5 ,
646 attribute6 = p_attribute6 ,
647 attribute7 = p_attribute7 ,
648 attribute8 = p_attribute8 ,
649 attribute9 = p_attribute9 ,
650 attribute10 = p_attribute10 ,
651 attribute11 = p_attribute11 ,
652 attribute12 = p_attribute12 ,
653 attribute13 = p_attribute13 ,
654 attribute14 = p_attribute14 ,
655 attribute15 = p_attribute15 ,
656 attribute16 = p_attribute16 ,
657 attribute17 = p_attribute17 ,
658 attribute18 = p_attribute18 ,
659 attribute19 = p_attribute19 ,
660 attribute20 = p_attribute20 ,
661 attribute21 = p_attribute21 ,
662 attribute22 = p_attribute22 ,
663 attribute23 = p_attribute23 ,
664 attribute24 = p_attribute24 ,
665 attribute25 = p_attribute25 ,
666 attribute26 = p_attribute26 ,
667 attribute27 = p_attribute27 ,
668 attribute28 = p_attribute28 ,
669 attribute29 = p_attribute29 ,
670 attribute30 = p_attribute30 ,
671 last_updated_by = FND_GLOBAL.USER_ID,
672 last_update_date = SYSDATE,
673 last_update_login = l_last_update_login
674 WHERE risk_phrase_code = p_phrase_code;
675 IF SQL%NOTFOUND THEN
676 RAISE Row_Missing_Error;
677 END IF;
678 ELSIF p_object = 'L' then
679 -- If the value for Language is null or invalid, write an error to the log file.
680 IF p_language is NULL then
681 l_msg_token := l_language_code;
682 RAISE Row_Missing_Error;
683 END IF;
684
685 /*Check the language codes */
686 l_language_code := p_language;
687 OPEN c_get_language;
688 FETCH c_get_language INTO LangRecord;
689 IF c_get_language%NOTFOUND THEN
690 CLOSE c_get_language;
691 l_msg_token := l_language_code;
692 RAISE Row_Missing_Error;
693 END IF;
694 CLOSE c_get_language;
695 -- If the record for the specified risk phrase and language does not exist in the
696 -- GR_RISK_PHRASES_TL table,an error will be written to the log file.
697 gr_risk_phrases_tl_pkg.Check_Primary_Key(
698 p_phrase_code,
699 p_language,
700 'F',
701 row_id,
702 l_key_exists);
703
704 IF l_key_exists = 'N' THEN
705 l_msg_token := p_phrase_code|| ' ' || p_language;
706 RAISE Row_Missing_Error;
707 END IF;
708 -- The value for Description will be updated GR_RISK_PHRASES_TL table for the specified language.
709 UPDATE GR_RISK_PHRASES_TL
710 SET risk_description = p_phrase_text,
711 source_lang = p_source_language,
712 last_updated_by = FND_GLOBAL.USER_ID,
713 last_update_date = SYSDATE,
714 last_update_login = l_last_update_login
715 WHERE risk_phrase_code = p_phrase_code
716 AND language = p_language;
717 IF SQL%NOTFOUND THEN
718 l_msg_token := p_phrase_code || ' ' || p_language;
719 RAISE Row_Missing_Error;
720 END IF;
721 END IF;
722 END IF;
723 ELSE -- action is D (delete)
724 IF p_phrase_type = 'S' THEN
725 OPEN c_get_safety_phrase_code;
726 FETCH c_get_safety_phrase_code INTO safetycode;
727 IF c_get_safety_phrase_code%NOTFOUND THEN
728 x_return_status := 'E';
729 l_msg_token := p_phrase_code;
730 CLOSE c_get_safety_phrase_code;
731 RAISE LT_Exists_Error;
732 END IF;
733 CLOSE c_get_safety_phrase_code;
734 IF p_object = 'C' THEN
735 --Delete all the rows in tl and b tables for the passed safety phrase code
736 DELETE FROM gr_safety_phrases_tl
737 WHERE safety_phrase_code = p_phrase_code;
738
739 DELETE FROM gr_safety_phrases_b
740 WHERE safety_phrase_code = p_phrase_code;
741 IF SQL%NOTFOUND THEN
742 l_msg_token := p_phrase_code || ' ' || p_language;
743 RAISE Row_Missing_Error;
744 END IF;
745 ELSIF p_object = 'L' THEN
746 -- If the value for Language is null or invalid, write an error to the log file.
747 IF p_language is NULL then
748 l_msg_token := l_language_code;
749 RAISE Row_Missing_Error;
750 END IF;
751
752 /* Check the language codes */
753 l_language_code := p_language;
754 OPEN c_get_language;
755 FETCH c_get_language INTO LangRecord;
756 IF c_get_language%NOTFOUND THEN
757 CLOSE c_get_language;
758 l_msg_token := l_language_code;
759 RAISE Row_Missing_Error;
760 END IF;
761 CLOSE c_get_language;
762 -- If the record for the specified safety phrase and language does not exist in the
763 -- GR_SAFETY_PHRASES_TL table,an error will be written to the log file.
764 gr_safety_phrases_tl_pkg.Check_Primary_Key(
765 p_phrase_code,
766 p_language,
767 'F',
768 row_id,
769 l_key_exists);
770 IF l_key_exists = 'N' THEN
771 l_msg_token := p_phrase_code|| ' ' || p_language;
772 RAISE Row_Missing_Error;
773 END IF;
774 -- delete form tl table for that language specific row
775
776 DELETE gr_safety_phrases_tl
777 WHERE safety_phrase_code = p_phrase_code
778 and language = p_language;
779 IF SQL%NOTFOUND THEN
780 l_msg_token := p_phrase_code || ' ' || p_language;
781 RAISE Row_Missing_Error;
782 END IF;
783 END IF;
784 ELSIF p_phrase_type = 'R' THEN
785 OPEN c_get_risk_phrase_code;
786 FETCH c_get_risk_phrase_code INTO riskcode;
787 IF c_get_risk_phrase_code%NOTFOUND THEN
788 x_return_status := 'E';
789 l_msg_token := p_phrase_code;
790 CLOSE c_get_risk_phrase_code;
791 RAISE LT_Exists_Error;
792 END IF;
793 CLOSE c_get_risk_phrase_code;
794 IF p_object = 'C' THEN
795 --Delete all the rows in tl and b tables for the passed risk phrase code
796 DELETE FROM gr_risk_phrases_tl
797 WHERE risk_phrase_code = p_phrase_code;
798
799 DELETE FROM gr_risk_phrases_b
800 WHERE risk_phrase_code = p_phrase_code;
801 IF SQL%NOTFOUND THEN
802 l_msg_token := p_phrase_code || ' ' || p_language;
803 RAISE Row_Missing_Error;
804 END IF;
805 ELSIF p_object = 'L' THEN
806 -- If the value for Language is null or invalid, write an error to the log file.
807 IF p_language is NULL then
808 l_msg_token := l_language_code;
809 RAISE Row_Missing_Error;
810 END IF;
811
812 /* Check the language codes */
813 l_language_code := p_language;
814 OPEN c_get_language;
815 FETCH c_get_language INTO LangRecord;
816 IF c_get_language%NOTFOUND THEN
817 CLOSE c_get_language;
818 l_msg_token := l_language_code;
819 RAISE Row_Missing_Error;
820 END IF;
821 CLOSE c_get_language;
822 -- If the record for the specified safety phrase and language does not exist in the
823 -- GR_SAFETY_PHRASES_TL table,an error will be written to the log file.
824 gr_risk_phrases_tl_pkg.Check_Primary_Key(
825 p_phrase_code,
826 p_language,
827 'F',
828 row_id,
829 l_key_exists);
830 IF l_key_exists = 'N' THEN
831 l_msg_token := p_phrase_code|| ' ' || p_language;
832 RAISE Row_Missing_Error;
833 END IF;
834 -- delete form tl table for that language specific row
835
836 DELETE gr_risk_phrases_tl
837 WHERE risk_phrase_code = p_phrase_code
838 and language = p_language;
839 IF SQL%NOTFOUND THEN
840 l_msg_token := p_phrase_code || ' ' || p_language;
841 RAISE Row_Missing_Error;
842 END IF;
843 END IF;
844 END IF;
845 END IF; -- IF p_action = 'I' then
846 IF (X_RETURN_STATUS = 'S') THEN
847 IF (p_commit = FND_API.G_TRUE) THEN
848 COMMIT;
849 END IF;
850 END IF;
851 EXCEPTION
852 WHEN LBins_err THEN
853 x_return_status := FND_API.G_RET_STS_ERROR;
854 -- ROLLBACK TO SAVEPOINT FIELD_NAMES;
855 --x_msg_data := msg_data;
856 FND_MSG_PUB.Count_AND_GET ( p_encoded => FND_API.G_FALSE
857 , p_count => x_msg_count
858 , p_data => x_msg_data);
859
860 WHEN LCins_err THEN
861 x_return_status := FND_API.G_RET_STS_ERROR;
862 -- ROLLBACK TO SAVEPOINT FIELD_NAMES;
863 --x_msg_data := msg_data;
864 FND_MSG_PUB.Count_AND_GET ( p_encoded => FND_API.G_FALSE
865 , p_count => x_msg_count
866 , p_data => x_msg_data);
867
868 WHEN FND_API.G_EXC_ERROR THEN
869 x_return_status := FND_API.G_RET_STS_ERROR;
870 FND_MSG_PUB.Count_AND_GET ( p_encoded => FND_API.G_FALSE
871 , p_count => x_msg_count
872 , p_data => x_msg_data);
873 x_msg_data := FND_MESSAGE.Get;
874
875 WHEN LT_Exists_Error THEN
876 x_return_status := 'E';
877 oracle_error := APP_EXCEPTION.Get_Code;
878 FND_MESSAGE.SET_NAME('GR','GR_RECORD_EXISTS');
879 FND_MESSAGE.SET_TOKEN('CODE',l_msg_token,FALSE);
880 FND_MSG_PUB.ADD;
881 FND_MSG_PUB.Count_AND_GET ( p_encoded => FND_API.G_FALSE
882 , p_count => x_msg_count
883 , p_data => x_msg_data);
884
885 WHEN LTadd_err THEN
886 x_return_status := FND_API.G_RET_STS_ERROR;
887 --ROLLBACK TO SAVEPOINT FIELD_NAME_CLASSES;
888 FND_MSG_PUB.COUNT_AND_GET (P_count => x_msg_count,
889 P_data => x_msg_data);
890
891 WHEN Row_Missing_Error THEN
892 x_return_status := 'E';
893 FND_MESSAGE.SET_NAME('GR','GR_RECORD_NOT_FOUND');
894 FND_MESSAGE.SET_TOKEN('CODE',l_msg_token,FALSE);
895 FND_MSG_PUB.ADD;
896 FND_MSG_PUB.Count_AND_GET ( p_encoded => FND_API.G_FALSE
897 , p_count => x_msg_count
898 , p_data => x_msg_data);
899
900 WHEN OTHERS THEN
901 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
902 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME
903 , l_api_name);
904
905 FND_MSG_PUB.Count_AND_GET ( p_encoded=> FND_API.G_FALSE
906 , p_count => x_msg_count
907 , p_data => x_msg_data);
908
909 END RISK_SAFETY_PHRASES;
910
911 END GR_RISK_SAFETY_PHRASES_PUB;