1 package body WSH_CC_PARAMETER_SETUPS_PKG as
2 /* $Header: WSHCCTHB.pls 115.2 2002/06/03 12:30:23 pkm ship $ */
3 -- Global constant holding the package name
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'WSH_CC_PARAMETER_SETUPS_PKG';
5
6 /*----------------------------------------------------------*/
7 /* Insert_Row Procedure */
8 /*----------------------------------------------------------*/
9 /* --
10 -- Purpose
11 -- Insert a row into WSH_CC_PARAMETER_SETUPS_B entity
12 -- Insert a row into WSH_CC_PARAMETER_SETUPS_TL entity
13 -- Input Parameters
14 -- p_api_version
15 -- API version number (current version is 1.0)
16 -- p_init_msg_list (optional, default FND_API.G_FALSE)
17 -- Valid values: FND_API.G_FALSE or FND_API.G_TRUE.
18 -- if set to FND_API.G_TRUE
19 -- initialize error message list
20 -- if set to FND_API.G_FALSE - not initialize error
21 -- message list
22 -- p_commit (optional, default FND_API.G_FALSE)
23 -- whether or not to commit the changes to database
24 --
25 -- Input parameters for clear cross parameter setups information
26 -- P_PARAMETER_ID Unique sequence generated parameter ID
27 -- P_PARAMETER_NAME Parameter Name (Internally identified)
28 -- P_VALUE User Defined Value for the Parameter.
29 -- P_DEFAULT_VALUE System defined Seeded Value for the Parameter.
30 -- P_USER_SETTABLE User can Override the default.
31 -- P_USER_PARAMETER_NAME User Parameter name
32 -- P_DESCRIPTION Brief Description of the Parameter.
33 --
34 -- Output Parameters
35 -- x_return_status
36 -- if the process succeeds, the value is
37 -- fnd_api.g_ret_sts_success;
38 -- if there is an expected error, the value is
39 -- fnd_api.g_ret_sts_error;
40 -- if there is an unexpected error, the value is
41 -- fnd_api.g_ret_sts_unexp_error;
42 -- x_msg_count
43 -- if there is one or more errors, the number of error messages
44 -- in the buffer
45 -- x_msg_data
46 -- if there is one and only one error, the error message
47 -- (See fnd_api package for more details about the above output parameters)
48 -- p_PARAMETER_ID - Clear Cross Parameter Id ( PK)
49 --*/
50
51
52 procedure INSERT_ROW (
53 p_api_version IN NUMBER ,
54 p_init_msg_list IN VARCHAR2 := fnd_api.g_false ,
55 p_commit IN VARCHAR2 := fnd_api.g_false ,
56 x_return_status OUT VARCHAR2 ,
57 x_msg_count OUT NUMBER ,
58 x_msg_data OUT VARCHAR2 ,
59 P_PARAMETER_ID OUT NUMBER ,
60 P_PARAMETER_NAME IN VARCHAR2 ,
61 P_VALUE IN VARCHAR2 ,
62 P_DEFAULT_VALUE IN VARCHAR2 ,
63 P_USER_SETTABLE IN VARCHAR2 ,
64 P_USER_PARAMETER_NAME IN VARCHAR2 ,
65 P_DESCRIPTION IN VARCHAR2
66 ) is
67
68 l_parameter_id NUMBER;
69 l_api_name CONSTANT VARCHAR2(30) := 'Insert_Row';
70 l_api_version number := 1.0;
71
72 begin
73
74 --dbms_output.put_line('begin api');
75 -- Standard Start of API savepoint
76 SAVEPOINT WSH_CC_PARAMETER_SETUPS_PKG;
77 -- Standard call to check for call compatibility.
78 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
79 p_api_version ,
80 l_api_name ,
81 G_PKG_NAME )
82 THEN
83 FND_MESSAGE.SET_NAME('WSH', 'WSH_INCOMPATIBLE_API_CALL');
84 FND_MSG_PUB.ADD;
85 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
86 END IF;
87 -- Initialize message list if p_init_msg_list is set to TRUE.
88 IF FND_API.to_Boolean( p_init_msg_list ) THEN
89 FND_MSG_PUB.initialize;
90 END IF;
91 -- Initialize API return status to success
92 x_return_status := FND_API.G_RET_STS_SUCCESS;
93 --dbms_output.put_line('begin api-2');
94
95 SELECT WSH_CC_PARAMETER_SETUPS_S.NEXTVAL into l_parameter_id FROM dual;
96
97 insert into WSH_CC_PARAMETER_SETUPS_B (
98 PARAMETER_ID,
99 PARAMETER_NAME,
100 VALUE,
101 DEFAULT_VALUE,
102 USER_SETTABLE,
103 CREATION_DATE,
104 CREATED_BY,
105 LAST_UPDATE_DATE,
106 LAST_UPDATED_BY,
107 LAST_UPDATE_LOGIN
108 )
109 values (
110 l_PARAMETER_ID,
111 P_PARAMETER_NAME,
112 P_VALUE,
113 P_DEFAULT_VALUE,
114 P_USER_SETTABLE,
115 sysdate,
116 FND_GLOBAL.user_id,
117 sysdate,
118 FND_GLOBAL.user_id,
119 FND_GLOBAL.login_id
120 );
121
122 IF SQL%NOTFOUND THEN
123 FND_MESSAGE.SET_NAME('WSH', 'WSH_INSERT_FAILED');
124 FND_MSG_PUB.ADD;
125 x_return_status := FND_API.G_RET_STS_ERROR;
126 RAISE FND_API.G_EXC_ERROR ;
127 END IF;
128 --dbms_output.put_line('Seq Id got it '||l_parameter_ID||'success');
129 x_return_status := fnd_api.g_ret_sts_success;
130 p_parameter_id := l_parameter_id;
131 -- End of API body
132 -- Standard check of p_commit.
133 IF FND_API.To_Boolean( p_commit ) THEN
134 COMMIT WORK;
135 END IF;
136 -- Standard call to get message count and if count is 1,
137 -- get message info.
138 FND_MSG_PUB.Count_And_Get
139 ( p_count => x_msg_count,
140 p_data => x_msg_data
141 );
142
143
144 insert into WSH_CC_PARAMETER_SETUPS_TL (
145 PARAMETER_ID,
146 USER_PARAMETER_NAME,
147 DESCRIPTION,
148 LAST_UPDATE_DATE,
149 LAST_UPDATED_BY,
150 CREATION_DATE,
151 CREATED_BY,
152 LAST_UPDATE_LOGIN,
153 LANGUAGE,
154 SOURCE_LANG
155 ) select
156 l_PARAMETER_ID,
157 P_USER_PARAMETER_NAME,
158 P_DESCRIPTION,
159 sysdate,
160 FND_GLOBAL.user_id,
161 sysdate,
162 FND_GLOBAL.user_id,
163 FND_GLOBAL.login_id,
164 L.LANGUAGE_CODE,
165 userenv('LANG')
166 from FND_LANGUAGES L
167 where L.INSTALLED_FLAG in ('I', 'B')
168 and not exists
169 (select NULL
170 from WSH_CC_PARAMETER_SETUPS_TL T
171 where T.PARAMETER_ID = l_PARAMETER_ID
172 and T.LANGUAGE = L.LANGUAGE_CODE);
173
174 IF SQL%NOTFOUND THEN
175 FND_MESSAGE.SET_NAME('WSH', 'WSH_INSERT_FAILED');
176 FND_MSG_PUB.ADD;
177 x_return_status := FND_API.G_RET_STS_ERROR;
178 RAISE FND_API.G_EXC_ERROR ;
179 END IF;
180 --dbms_output.put_line('Seq Id got it '||l_parameter_ID||'success');
181 x_return_status := fnd_api.g_ret_sts_success;
182 p_parameter_id := l_parameter_id;
183 -- End of API body
184 -- Standard check of p_commit.
185 IF FND_API.To_Boolean( p_commit ) THEN
186 COMMIT WORK;
187 END IF;
188 -- Standard call to get message count and if count is 1,
189 -- get message info.
190 FND_MSG_PUB.Count_And_Get
191 ( p_count => x_msg_count,
192 p_data => x_msg_data
193 );
194
195 EXCEPTION
196 WHEN FND_API.G_EXC_ERROR THEN
197 ROLLBACK TO WSH_CC_PARAMETER_SETUPS_PKG;
198 x_return_status := FND_API.G_RET_STS_ERROR;
199 FND_MSG_PUB.Count_And_Get
200 ( p_count => x_msg_count,
201 p_data => x_msg_data
202 );
203 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
204 ROLLBACK TO WSH_CC_PARAMETER_SETUPS_PKG;
205 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
206 FND_MSG_PUB.Count_And_Get
207 ( p_count => x_msg_count,
208 p_data => x_msg_data
209 );
210 WHEN OTHERS THEN
211 ROLLBACK TO WSH_CC_PARAMETER_SETUPS_PKG;
212 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
213 IF FND_MSG_PUB.Check_Msg_Level
214 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
215 THEN
216 FND_MSG_PUB.Add_Exc_Msg
217 ( G_PKG_NAME ,
218 l_api_name
219 );
220 END IF;
221 FND_MSG_PUB.Count_And_Get
222 ( p_count => x_msg_count,
223 p_data => x_msg_data
224 );
225
226 end INSERT_ROW;
227
228 /*----------------------------------------------------------*/
229 /* Lock_Row Procedure */
230 /*----------------------------------------------------------*/
231 /* --
232 -- Purpose
233 -- Check Lock a row of WSH_CC_PARAMETER_SETUPS entity
234 -- for the given parameter id
235 --
236 -- Input Parameters
237 -- p_api_version
238 -- API version number (current version is 1.0)
239 -- p_init_msg_list (optional, default FND_API.G_FALSE)
240 -- Valid values: FND_API.G_FALSE or FND_API.G_TRUE.
241 -- if set to FND_API.G_TRUE
242 -- initialize error message list
243 -- if set to FND_API.G_FALSE - not initialize error
244 -- message list
245 -- p_commit (optional, default FND_API.G_FALSE)
246 -- whether or not to commit the changes to database
247 --
248 -- Input parameters for clear cross parameter setups information
249 -- P_PARAMETER_ID Unique sequence generated parameter ID
250 -- P_PARAMETER_NAME Parameter Name (Internally identified)
251 -- P_VALUE User Defined Value for the Parameter.
252 -- P_DEFAULT_VALUE System defined Seeded Value for the Parameter.
253 -- P_USER_SETTABLE User can Override the default.
254 -- P_USER_PARAMETER_NAME User Parameter name
255 -- P_DESCRIPTION Brief Description of the Parameter.
256 -- Output Parameters
257 -- x_return_status
258 -- if the process succeeds, the value is
259 -- fnd_api.g_ret_sts_success;
260 -- if there is an expected error, the value is
261 -- fnd_api.g_ret_sts_error;
262 -- if there is an unexpected error, the value is
263 -- fnd_api.g_ret_sts_unexp_error;
264 -- x_msg_count
265 -- if there is one or more errors, the number of error messages
266 -- in the buffer
267 -- x_msg_data
268 -- if there is one and only one error, the error message
269 -- (See fnd_api package for more details about the above output parameters)
270
271 --*/
272 procedure LOCK_ROW (
273 p_api_version IN NUMBER ,
274 p_init_msg_list IN VARCHAR2 := fnd_api.g_false ,
275 p_commit IN VARCHAR2 := fnd_api.g_false ,
276 x_return_status OUT VARCHAR2 ,
277 x_msg_count OUT NUMBER ,
278 x_msg_data OUT VARCHAR2 ,
279 P_PARAMETER_ID IN NUMBER ,
280 P_PARAMETER_NAME IN VARCHAR2 ,
281 P_VALUE IN VARCHAR2 ,
282 P_DEFAULT_VALUE IN VARCHAR2 ,
283 P_USER_SETTABLE IN VARCHAR2 ,
284 P_USER_PARAMETER_NAME IN VARCHAR2 ,
285 P_DESCRIPTION IN VARCHAR2
286 ) is
287
288 cursor c is select
289 PARAMETER_NAME,
290 VALUE,
291 DEFAULT_VALUE,
292 USER_SETTABLE
293 from WSH_CC_PARAMETER_SETUPS_B
294 where PARAMETER_ID = P_PARAMETER_ID
295 for update of PARAMETER_ID nowait;
296 recinfo c%rowtype;
297
298 l_api_name CONSTANT VARCHAR2(30) := 'Lock_Row' ;
299 l_api_version number := 1.0;
300
301
302 cursor c1 is select
303 USER_PARAMETER_NAME,
304 DESCRIPTION,
305 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
306 from WSH_CC_PARAMETER_SETUPS_TL
307 where PARAMETER_ID = P_PARAMETER_ID
308 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
309 for update of PARAMETER_ID nowait;
310
311 begin
312
313 --dbms_output.put_line('begin');
314 -- Standard Start of API savepoint
315 SAVEPOINT WSH_CC_PARAMETER_SETUPS_PKG;
316 -- Standard call to check for call compatibility.
317 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
318 p_api_version ,
319 l_api_name ,
320 G_PKG_NAME )
321 THEN
322 FND_MESSAGE.SET_NAME('WSH', 'WSH_INCOMPATIBLE_API_CALL');
323 FND_MSG_PUB.ADD;
324 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
325 END IF;
326 -- Initialize message list if p_init_msg_list is set to TRUE.
327 IF FND_API.to_Boolean( p_init_msg_list ) THEN
328 FND_MSG_PUB.initialize;
329 END IF;
330 -- Initialize API return status to success
331 x_return_status := FND_API.G_RET_STS_SUCCESS;
332
333 -- Check Lock a row of wsh_cc_parameter_setups
334
335 open c;
336 fetch c into recinfo;
337 if (c%notfound) then
338 close c;
339 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
340 app_exception.raise_exception;
341 end if;
342 close c;
343 if ( ((recinfo.PARAMETER_NAME = p_PARAMETER_NAME)
344 OR ((recinfo.PARAMETER_NAME is null) AND (p_PARAMETER_NAME is null)))
345 AND ((recinfo.VALUE = p_VALUE)
346 OR ((recinfo.VALUE is null) AND (p_VALUE is null)))
347 AND ((recinfo.DEFAULT_VALUE = p_DEFAULT_VALUE)
348 OR ((recinfo.DEFAULT_VALUE is null) AND (p_DEFAULT_VALUE is null)))
349 AND ((recinfo.USER_SETTABLE = p_USER_SETTABLE)
350 OR ((recinfo.USER_SETTABLE is null) AND (p_USER_SETTABLE is null)))
351 ) then
352 x_return_status := FND_API.G_RET_STS_SUCCESS;
353 else
354 x_return_status := FND_API.G_RET_STS_ERROR;
355 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
356 app_exception.raise_exception;
357 end if;
358
359 for tlinfo in c1 loop
360 if (tlinfo.BASELANG = 'Y') then
361 if ( ((tlinfo.USER_PARAMETER_NAME = p_USER_PARAMETER_NAME)
362 OR ((tlinfo.USER_PARAMETER_NAME is null) AND (p_USER_PARAMETER_NAME is null)))
363 AND ((tlinfo.DESCRIPTION = p_DESCRIPTION)
364 OR ((tlinfo.DESCRIPTION is null) AND (p_DESCRIPTION is null)))
365 ) then
366 x_return_status := FND_API.G_RET_STS_SUCCESS;
367 else
368 x_return_status := FND_API.G_RET_STS_ERROR;
369 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
370 app_exception.raise_exception;
371 end if;
372 end if;
373 end loop;
374 EXCEPTION
375 WHEN others THEN
376 IF (c%ISOPEN) then
377 close c;
378 End if;
379 IF (c1%ISOPEN) then
380 close c1;
381 End if;
382 end LOCK_ROW;
383
384 /*----------------------------------------------------------*/
385 /* Update_Row Procedure */
386 /*----------------------------------------------------------*/
387 /* --
388 -- Purpose
389 -- Update a row into WSH_CC_parameter_setups_b entity
390 -- Update a row into WSH_CC_parameter_setups_tl entity
391 -- for the given parameter id
392 --
393 -- Input Parameters
394 -- p_api_version
395 -- API version number (current version is 1.0)
396 -- p_init_msg_list (optional, default FND_API.G_FALSE)
397 -- Valid values: FND_API.G_FALSE or FND_API.G_TRUE.
398 -- if set to FND_API.G_TRUE
399 -- initialize error message list
400 -- if set to FND_API.G_FALSE - not initialize error
401 -- message list
402 -- p_commit (optional, default FND_API.G_FALSE)
406 -- P_PARAMETER_ID Unique sequence generated parameter ID
403 -- whether or not to commit the changes to database
404 --
405 -- Input parameters for clear cross parameter setups informations
407 -- P_PARAMETER_NAME Parameter Name (Internally identified)
408 -- P_VALUE User Defined Value for the Parameter.
409 -- P_DEFAULT_VALUE System defined Seeded Value for the Parameter.
410 -- P_USER_SETTABLE User can Override the default.
411 -- P_USER_PARAMETER_NAME User Parameter name
412 -- P_DESCRIPTION Brief Description of the Parameter.
413 --
414 -- Output Parameters
415 -- x_return_status
416 -- if the process succeeds, the value is
417 -- fnd_api.g_ret_sts_success;
418 -- if there is an expected error, the value is
419 -- fnd_api.g_ret_sts_error;
420 -- if there is an unexpected error, the value is
421 -- fnd_api.g_ret_sts_unexp_error;
422 -- x_msg_count
423 -- if there is one or more errors, the number of error messages
424 -- in the buffer
425 -- x_msg_data
426 -- if there is one and only one error, the error message
427 -- (See fnd_api package for more details about the above output parameters)
428
429 --*/
430
431 procedure UPDATE_ROW (
432 p_api_version IN NUMBER ,
433 p_init_msg_list IN VARCHAR2 := fnd_api.g_false ,
434 p_commit IN VARCHAR2 := fnd_api.g_false ,
435 x_return_status OUT VARCHAR2 ,
436 x_msg_count OUT NUMBER ,
437 x_msg_data OUT VARCHAR2 ,
438 P_PARAMETER_ID IN NUMBER ,
439 P_PARAMETER_NAME IN VARCHAR2 ,
440 P_VALUE IN VARCHAR2 ,
441 P_DEFAULT_VALUE IN VARCHAR2 ,
442 P_USER_SETTABLE IN VARCHAR2 ,
443 P_USER_PARAMETER_NAME IN VARCHAR2 ,
444 P_DESCRIPTION IN VARCHAR2
445 ) is
446
447 l_api_name CONSTANT VARCHAR2(30) := 'Update_Row' ;
448 l_api_version number := 1.0;
449
450 begin
451
452 --dbms_output.put_line('begin');
453 -- Standard Start of API savepoint
454 SAVEPOINT WSH_CC_PARAMETER_SETUPS_PKG;
455 -- Standard call to check for call compatibility.
456 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
457 p_api_version ,
458 l_api_name ,
459 G_PKG_NAME )
460 THEN
461 FND_MESSAGE.SET_NAME('WSH', 'WSH_INCOMPATIBLE_API_CALL');
462 FND_MSG_PUB.ADD;
463 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
464 END IF;
465 -- Initialize message list if p_init_msg_list is set to TRUE.
466 IF FND_API.to_Boolean( p_init_msg_list ) THEN
467 FND_MSG_PUB.initialize;
468 END IF;
469 -- Initialize API return status to success
470 x_return_status := FND_API.G_RET_STS_SUCCESS;
471
472 -- Update a row into wsh_cc_parameter_setups entity with all detail information
473 -- for the given parameter id
474
475 update WSH_CC_PARAMETER_SETUPS_B set
476 PARAMETER_NAME = P_PARAMETER_NAME,
477 VALUE = P_VALUE,
478 DEFAULT_VALUE = P_DEFAULT_VALUE,
479 USER_SETTABLE = P_USER_SETTABLE,
480 LAST_UPDATE_DATE = sysdate,
481 LAST_UPDATED_BY = FND_GLOBAL.user_id,
482 LAST_UPDATE_LOGIN = FND_GLOBAL.login_id
483 where PARAMETER_ID = P_PARAMETER_ID;
484
485 IF SQL%NOTFOUND THEN
486 FND_MESSAGE.SET_NAME('WSH', 'WSH_UPDATE_FAILED');
487 FND_MSG_PUB.ADD;
488 x_return_status := FND_API.G_RET_STS_ERROR;
489 RAISE FND_API.G_EXC_ERROR ;
490 END IF;
491 --dbms_output.put_line('begin-5');
492 x_return_status := fnd_api.g_ret_sts_success;
493
494 -- End of API body
495 -- Standard check of p_commit.
496 IF FND_API.To_Boolean( p_commit ) THEN
497 COMMIT WORK;
498 END IF;
499 -- Standard call to get message count and if count is 1,
500 -- get message info.
501 FND_MSG_PUB.Count_And_Get
502 ( p_count => x_msg_count,
503 p_data => x_msg_data
504 );
505
506 update WSH_CC_PARAMETER_SETUPS_TL set
507 USER_PARAMETER_NAME = P_USER_PARAMETER_NAME,
508 DESCRIPTION = P_DESCRIPTION,
509 SOURCE_LANG = userenv('LANG')
510 where PARAMETER_ID = P_PARAMETER_ID
511 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
512
513 IF SQL%NOTFOUND THEN
514 FND_MESSAGE.SET_NAME('WSH', 'WSH_UPDATE_FAILED');
515 FND_MSG_PUB.ADD;
516 x_return_status := FND_API.G_RET_STS_ERROR;
517 RAISE FND_API.G_EXC_ERROR ;
518 END IF;
519 --dbms_output.put_line('begin-5');
520 x_return_status := fnd_api.g_ret_sts_success;
521
522 -- End of API body
523 -- Standard check of p_commit.
524 IF FND_API.To_Boolean( p_commit ) THEN
525 COMMIT WORK;
526 END IF;
527 -- Standard call to get message count and if count is 1,
528 -- get message info.
529 FND_MSG_PUB.Count_And_Get
530 ( p_count => x_msg_count,
531 p_data => x_msg_data
532 );
533
534 EXCEPTION
535 WHEN FND_API.G_EXC_ERROR THEN
536 --dbms_output.put_line(sqlerrm);
537 ROLLBACK TO WSH_CC_PARAMETER_SETUPS_PKG;
538 x_return_status := FND_API.G_RET_STS_ERROR;
542 );
539 FND_MSG_PUB.Count_And_Get
540 ( p_count => x_msg_count,
541 p_data => x_msg_data
543 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
544 --dbms_output.put_line(sqlerrm);
545 ROLLBACK TO WSH_CC_PARAMETER_SETUPS_PKG;
546 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
547 FND_MSG_PUB.Count_And_Get
548 ( p_count => x_msg_count,
549 p_data => x_msg_data
550 );
551 WHEN OTHERS THEN
552 --dbms_output.put_line(sqlerrm);
553 ROLLBACK TO WSH_CC_PARAMETER_SETUPS_PKG;
554 x_return_status := 'W';
555 IF FND_MSG_PUB.Check_Msg_Level
556 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
557 THEN
558 FND_MSG_PUB.Add_Exc_Msg
559 ( G_PKG_NAME ,
560 l_api_name
561 );
562 END IF;
563 FND_MSG_PUB.Count_And_Get
564 ( p_count => x_msg_count,
565 p_data => x_msg_data
566 );
567
568 end UPDATE_ROW;
569
570 /*----------------------------------------------------------*/
571 /* Delete_Row Procedure */
572 /*----------------------------------------------------------*/
573 /* --
574 -- Purpose
575 -- Delete a row from WSH_CC_PARAMETER_SETUPS_B entity
576 -- Delete a row from WSH_CC_PARAMETER_SETUPS_TL entity
577 -- for the given parameter id
578 --
579 -- Input Parameters
580 -- p_api_version
581 -- API version number (current version is 1.0)
582 -- p_init_msg_list (optional, default FND_API.G_FALSE)
583 -- Valid values: FND_API.G_FALSE or FND_API.G_TRUE.
584 -- if set to FND_API.G_TRUE
585 -- initialize error message list
586 -- if set to FND_API.G_FALSE - not initialize error
587 -- message list
588 -- p_commit (optional, default FND_API.G_FALSE)
589 -- whether or not to commit the changes to database
590 --
591 -- Input parameters for clear cross parameters informations
592 -- p_PARAMETER_ID -- parameter id
593 --
594 --
595 -- Output Parameters
596 -- x_return_status
597 -- if the process succeeds, the value is
598 -- fnd_api.g_ret_sts_success;
599 -- if there is an expected error, the value is
600 -- fnd_api.g_ret_sts_error;
601 -- if there is an unexpected error, the value is
602 -- fnd_api.g_ret_sts_unexp_error;
603 -- x_msg_count
604 -- if there is one or more errors, the number of error messages
605 -- in the buffer
606 -- x_msg_data
607 -- if there is one and only one error, the error message
608 -- (See fnd_api package for more details about the above output parameters)
609
610 --*/
611 procedure DELETE_ROW (
612 p_api_version IN NUMBER ,
613 p_init_msg_list IN VARCHAR2 := fnd_api.g_false ,
614 p_commit IN VARCHAR2 := fnd_api.g_false ,
615 x_return_status OUT VARCHAR2 ,
616 x_msg_count OUT NUMBER ,
617 x_msg_data OUT VARCHAR2 ,
618 P_PARAMETER_ID IN NUMBER
619 ) is
620
621 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Row' ;
622 l_api_version number := 1.0;
623
624 begin
625
626 --dbms_output.put_line('begin');
627 -- Standard Start of API savepoint
628 SAVEPOINT WSH_CC_PARAMETER_SETUPS_PKG;
629 -- Standard call to check for call compatibility.
630 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
631 p_api_version ,
632 l_api_name ,
633 G_PKG_NAME )
634 THEN
635 FND_MESSAGE.SET_NAME('WSH', 'WSH_INCOMPATIBLE_API_CALL');
636 FND_MSG_PUB.ADD;
637 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
638 END IF;
639 -- Initialize message list if p_init_msg_list is set to TRUE.
640 IF FND_API.to_Boolean( p_init_msg_list ) THEN
641 FND_MSG_PUB.initialize;
642 END IF;
643 -- Initialize API return status to success
644 x_return_status := FND_API.G_RET_STS_SUCCESS;
645
646 -- Delete a row from wsh_cc_parameter_setups entity
647 -- for the given parameter id
648
649
650 delete from WSH_CC_PARAMETER_SETUPS_TL
651 where PARAMETER_ID = P_PARAMETER_ID;
652
653 IF SQL%NOTFOUND THEN
654 FND_MESSAGE.SET_NAME('WSH', 'WSH_DELETE_FAILED');
655 FND_MSG_PUB.ADD;
656 x_return_status := FND_API.G_RET_STS_ERROR;
657 RAISE FND_API.G_EXC_ERROR ;
658 END IF;
659 --dbms_output.put_line('begin-5');
660 x_return_status := fnd_api.g_ret_sts_success;
661
662 -- End of API body
663 -- Standard check of p_commit.
664 IF FND_API.To_Boolean( p_commit ) THEN
665 COMMIT WORK;
666 END IF;
667 -- Standard call to get message count and if count is 1,
668 -- get message info.
669 FND_MSG_PUB.Count_And_Get
670 ( p_count => x_msg_count,
671 p_data => x_msg_data
672 );
673
674 delete from WSH_CC_PARAMETER_SETUPS_B
678 FND_MESSAGE.SET_NAME('WSH', 'WSH_DELETE_FAILED');
675 where PARAMETER_ID = p_PARAMETER_ID;
676
677 IF SQL%NOTFOUND THEN
679 FND_MSG_PUB.ADD;
680 x_return_status := FND_API.G_RET_STS_ERROR;
681 RAISE FND_API.G_EXC_ERROR ;
682 END IF;
683 --dbms_output.put_line('begin-5');
684 x_return_status := fnd_api.g_ret_sts_success;
685
686 -- End of API body
687 -- Standard check of p_commit.
688 IF FND_API.To_Boolean( p_commit ) THEN
689 COMMIT WORK;
690 END IF;
691 -- Standard call to get message count and if count is 1,
692 -- get message info.
693 FND_MSG_PUB.Count_And_Get
694 ( p_count => x_msg_count,
695 p_data => x_msg_data
696 );
697
698 EXCEPTION
699 WHEN FND_API.G_EXC_ERROR THEN
700 --dbms_output.put_line(sqlerrm);
701 ROLLBACK TO WSH_CC_PARAMETER_SETUPS_PKG;
702 x_return_status := FND_API.G_RET_STS_ERROR;
703 FND_MSG_PUB.Count_And_Get
704 ( p_count => x_msg_count,
705 p_data => x_msg_data
706 );
707 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
708 --dbms_output.put_line(sqlerrm);
709 ROLLBACK TO WSH_CC_PARAMETER_SETUPS_PKG;
710 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
711 FND_MSG_PUB.Count_And_Get
712 ( p_count => x_msg_count,
713 p_data => x_msg_data
714 );
715 WHEN OTHERS THEN
716 --dbms_output.put_line(sqlerrm);
717 ROLLBACK TO WSH_CC_PARAMETER_SETUPS_PKG;
718 x_return_status := 'W';
719 IF FND_MSG_PUB.Check_Msg_Level
720 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
721 THEN
722 FND_MSG_PUB.Add_Exc_Msg
723 ( G_PKG_NAME ,
724 l_api_name
725 );
726 END IF;
727 FND_MSG_PUB.Count_And_Get
728 ( p_count => x_msg_count,
729 p_data => x_msg_data
730 );
731
732 end DELETE_ROW;
733
734 /*----------------------------------------------------------*/
735 /* Add_Language Procedure */
736 /*----------------------------------------------------------*/
737 procedure ADD_LANGUAGE
738 is
739 begin
740 delete from WSH_CC_PARAMETER_SETUPS_TL T
741 where not exists
742 (select NULL
743 from WSH_CC_PARAMETER_SETUPS_B B
744 where B.PARAMETER_ID = T.PARAMETER_ID
745 );
746
747 update WSH_CC_PARAMETER_SETUPS_TL T set (
748 USER_PARAMETER_NAME,
749 DESCRIPTION
750 ) = (select
751 B.USER_PARAMETER_NAME,
752 B.DESCRIPTION
753 from WSH_CC_PARAMETER_SETUPS_TL B
754 where B.PARAMETER_ID = T.PARAMETER_ID
755 and B.LANGUAGE = T.SOURCE_LANG)
756 where (
757 T.PARAMETER_ID,
758 T.LANGUAGE
759 ) in (select
760 SUBT.PARAMETER_ID,
761 SUBT.LANGUAGE
762 from WSH_CC_PARAMETER_SETUPS_TL SUBB, WSH_CC_PARAMETER_SETUPS_TL SUBT
763 where SUBB.PARAMETER_ID = SUBT.PARAMETER_ID
764 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
765 and (SUBB.USER_PARAMETER_NAME <> SUBT.USER_PARAMETER_NAME
766 or (SUBB.USER_PARAMETER_NAME is null and SUBT.USER_PARAMETER_NAME is not null)
767 or (SUBB.USER_PARAMETER_NAME is not null and SUBT.USER_PARAMETER_NAME is null)
768 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
769 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
770 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
771 ));
772
773 insert into WSH_CC_PARAMETER_SETUPS_TL (
774 PARAMETER_ID,
775 USER_PARAMETER_NAME,
776 DESCRIPTION,
777 LAST_UPDATE_DATE,
778 LAST_UPDATED_BY,
779 CREATION_DATE,
780 CREATED_BY,
781 LAST_UPDATE_LOGIN,
782 LANGUAGE,
783 SOURCE_LANG
784 ) select
785 B.PARAMETER_ID,
786 B.USER_PARAMETER_NAME,
787 B.DESCRIPTION,
788 B.LAST_UPDATE_DATE,
789 B.LAST_UPDATED_BY,
790 B.CREATION_DATE,
791 B.CREATED_BY,
792 B.LAST_UPDATE_LOGIN,
793 L.LANGUAGE_CODE,
794 B.SOURCE_LANG
795 from WSH_CC_PARAMETER_SETUPS_TL B, FND_LANGUAGES L
796 where L.INSTALLED_FLAG in ('I', 'B')
797 and B.LANGUAGE = userenv('LANG')
798 and not exists
799 (select NULL
800 from WSH_CC_PARAMETER_SETUPS_TL T
801 where T.PARAMETER_ID = B.PARAMETER_ID
802 and T.LANGUAGE = L.LANGUAGE_CODE);
803 end ADD_LANGUAGE;
804
805 /*----------------------------------------------------------*/
806 /* Translate_Row Procedure */
807 /*----------------------------------------------------------*/
808 PROCEDURE translate_row
809 (
810 x_parameter_id IN VARCHAR2 ,
811 x_owner IN VARCHAR2 ,
812 x_user_parameter_name IN VARCHAR2 ,
813 x_description IN VARCHAR2
814 ) IS
815 BEGIN
816 UPDATE wsh_cc_parameter_setups_tl SET
817 user_parameter_name = x_user_parameter_name,
818 description = x_description,
819 last_update_date = sysdate,
820 last_updated_by = Decode(x_owner, 'SEED', 1, 0),
821 last_update_login = 0,
822 source_lang = userenv('LANG')
823 WHERE parameter_id = fnd_number.canonical_to_number(x_parameter_id)
824 AND userenv('LANG') IN (language, source_lang);
828 /* Load_Row Procedure */
825 END translate_row;
826
827 /*----------------------------------------------------------*/
829 /*----------------------------------------------------------*/
830 PROCEDURE load_row
831 (
832 x_parameter_id IN VARCHAR2 ,
833 x_owner IN VARCHAR2 ,
834 x_parameter_name IN VARCHAR2 ,
835 x_user_parameter_name IN VARCHAR2 ,
836 x_value IN VARCHAR2 ,
837 x_user_settable IN VARCHAR2 ,
838 x_default_value IN VARCHAR2 ,
839 x_description IN VARCHAR2
840 ) IS
841
842 BEGIN
843 DECLARE
844 l_parameter_id NUMBER;
845 l_user_id NUMBER := 0;
846 l_row_id VARCHAR2(64);
847 l_sysdate DATE;
848 BEGIN
849 IF (x_owner = 'SEED') THEN
850 l_user_id := 1;
851 END IF;
852 --
853 SELECT Sysdate INTO l_sysdate FROM dual;
854 l_parameter_id := fnd_number.canonical_to_number(x_parameter_id);
855
856
857 update WSH_CC_PARAMETER_SETUPS_B set
858 PARAMETER_NAME = x_PARAMETER_NAME,
859 VALUE = x_VALUE,
860 DEFAULT_VALUE = x_DEFAULT_VALUE,
861 USER_SETTABLE = x_USER_SETTABLE,
862 LAST_UPDATE_DATE = l_sysdate,
863 LAST_UPDATED_BY = l_user_id,
864 LAST_UPDATE_LOGIN = 0
865 where PARAMETER_ID = l_PARAMETER_ID;
866
867 update WSH_CC_PARAMETER_SETUPS_TL set
868 USER_PARAMETER_NAME = x_USER_PARAMETER_NAME,
869 DESCRIPTION = x_DESCRIPTION,
870 SOURCE_LANG = userenv('LANG')
871 where PARAMETER_ID = l_PARAMETER_ID
872 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
873
874 IF SQL%NOTFOUND THEN
875
876 insert into WSH_CC_PARAMETER_SETUPS_B (
877 PARAMETER_ID,
878 PARAMETER_NAME,
879 VALUE,
880 DEFAULT_VALUE,
881 USER_SETTABLE,
882 CREATION_DATE,
883 CREATED_BY,
884 LAST_UPDATE_DATE,
885 LAST_UPDATED_BY,
886 LAST_UPDATE_LOGIN
887 )
888 values (
889 l_PARAMETER_ID,
890 x_PARAMETER_NAME,
891 x_VALUE,
892 x_DEFAULT_VALUE,
893 x_USER_SETTABLE,
894 l_sysdate,
895 l_user_id,
896 l_sysdate,
897 l_user_id,
898 0
899 );
900
901 insert into WSH_CC_PARAMETER_SETUPS_TL (
902 PARAMETER_ID,
903 USER_PARAMETER_NAME,
904 DESCRIPTION,
905 LAST_UPDATE_DATE,
906 LAST_UPDATED_BY,
907 CREATION_DATE,
908 CREATED_BY,
909 LAST_UPDATE_LOGIN,
910 LANGUAGE,
911 SOURCE_LANG
912 ) select
913 l_PARAMETER_ID,
914 x_USER_PARAMETER_NAME,
915 x_DESCRIPTION,
916 l_sysdate,
917 l_user_id,
918 l_sysdate,
919 l_user_id,
920 0,
921 L.LANGUAGE_CODE,
922 userenv('LANG')
923 from FND_LANGUAGES L
924 where L.INSTALLED_FLAG in ('I', 'B')
925 and not exists
926 (select NULL
927 from WSH_CC_PARAMETER_SETUPS_TL T
928 where T.PARAMETER_ID = l_PARAMETER_ID
929 and T.LANGUAGE = L.LANGUAGE_CODE);
930
931 END IF;
932 END;
933 commit;
934 END load_row;
935
936 end WSH_CC_PARAMETER_SETUPS_PKG;
[Home] [Help]