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