1 PACKAGE BODY ieo_svr_values_pvt AS
2 /* $Header: IEOSVRVB.pls 120.1 2005/06/12 01:22:19 appldev $ */
3
4
5 PROCEDURE insert_row(
6 x_rowid IN OUT NOCOPY VARCHAR2
7 , x_value_id NUMBER
8 , x_created_by NUMBER
9 , x_creation_date DATE
10 , x_last_updated_by NUMBER
11 , x_last_update_date DATE
12 , x_last_update_login NUMBER
13 , x_value_index NUMBER
14 , x_param_id NUMBER
15 , x_server_id NUMBER
16 , x_value VARCHAR2
17 ) IS
18 CURSOR l_insert IS
19 SELECT ROWID
20 FROM ieo_svr_values
21 WHERE value_id = x_value_id;
22 BEGIN
23 INSERT INTO ieo_svr_values (
24 value_id
25 , created_by
26 , creation_date
27 , last_updated_by
28 , last_update_date
29 , last_update_login
30 , value_index
31 , param_id
32 , server_id
33 , value
34 ) VALUES (
35 x_value_id
36 , DECODE(x_created_by,FND_API.G_MISS_NUM,NULL,x_created_by)
37 , DECODE(x_creation_date,FND_API.G_MISS_DATE,NULL,x_creation_date)
38 , DECODE(x_last_updated_by,FND_API.G_MISS_NUM,NULL,x_last_updated_by)
39 , DECODE(x_last_update_date,FND_API.G_MISS_DATE,NULL,x_last_update_date)
40 , DECODE(x_last_update_login,FND_API.G_MISS_NUM,NULL,x_last_update_login)
41 , DECODE(x_value_index,FND_API.G_MISS_NUM,NULL,x_value_index)
42 , DECODE(x_param_id,FND_API.G_MISS_NUM,NULL,x_param_id)
43 , DECODE(x_server_id,FND_API.G_MISS_NUM,NULL,x_server_id)
44 , DECODE(x_value,FND_API.G_MISS_CHAR,NULL,x_value)
45 );
46
47 OPEN l_insert;
48 FETCH l_insert INTO x_rowid;
49 IF (l_insert%NOTFOUND) THEN
50 CLOSE l_insert;
51 RAISE NO_DATA_FOUND;
52 END IF;
53 END insert_row;
54
55 PROCEDURE delete_row(
56 x_value_id NUMBER
57 ) IS
58 BEGIN
59 DELETE FROM ieo_svr_values
60 WHERE value_id = x_value_id;
61 IF (SQL%NOTFOUND) THEN
62 RAISE NO_DATA_FOUND;
63 END IF;
64 END delete_row;
65
66 PROCEDURE update_row(
67 x_rowid VARCHAR2
68 , x_value_id NUMBER
69 , x_created_by NUMBER
70 , x_creation_date DATE
71 , x_last_updated_by NUMBER
72 , x_last_update_date DATE
73 , x_last_update_login NUMBER
74 , x_value_index NUMBER
75 , x_param_id NUMBER
76 , x_server_id NUMBER
77 , x_value VARCHAR2
78 ) IS
79 BEGIN
80 UPDATE ieo_svr_values
81 SET
82 value_id=DECODE(x_value_id,FND_API.G_MISS_NUM,NULL,x_value_id)
83 , created_by=DECODE(x_created_by,FND_API.G_MISS_NUM,NULL,x_created_by)
84 , creation_date=DECODE(x_creation_date,FND_API.G_MISS_DATE,NULL,x_creation_date)
85 , last_updated_by=DECODE(x_last_updated_by,FND_API.G_MISS_NUM,NULL,x_last_updated_by)
86 , last_update_date=DECODE(x_last_update_date,FND_API.G_MISS_DATE,NULL,x_last_update_date)
87 , last_update_login=DECODE(x_last_update_login,FND_API.G_MISS_NUM,NULL,x_last_update_login)
88 , value_index=DECODE(x_value_index,FND_API.G_MISS_NUM,NULL,x_value_index)
89 , param_id=DECODE(x_param_id,FND_API.G_MISS_NUM,NULL,x_param_id)
90 , server_id=DECODE(x_server_id,FND_API.G_MISS_NUM,NULL,x_server_id)
91 , value=DECODE(x_value,FND_API.G_MISS_CHAR,NULL,x_value)
92 WHERE ROWID = x_rowid;
93 IF (SQL%NOTFOUND) THEN
94 RAISE NO_DATA_FOUND;
95 END IF;
96 END update_row;
97
98 PROCEDURE lock_row(
99 x_rowid VARCHAR2
100 , x_value_id NUMBER
101 , x_created_by NUMBER
102 , x_creation_date DATE
103 , x_last_updated_by NUMBER
104 , x_last_update_date DATE
105 , x_last_update_login NUMBER
106 , x_value_index NUMBER
107 , x_param_id NUMBER
108 , x_server_id NUMBER
109 , x_value VARCHAR2
110 ) IS
111 CURSOR l_lock IS
112 SELECT *
113 FROM ieo_svr_values
114 WHERE rowid = x_rowid
115 FOR UPDATE OF value_id NOWAIT;
116 l_table_rec l_lock%ROWTYPE;
117 BEGIN
118 OPEN l_lock;
119 FETCH l_lock INTO l_table_rec;
120 IF (l_lock%NOTFOUND) THEN
121 CLOSE l_lock;
122 FND_MESSAGE.SET_NAME('FND','FORM_RECORD_DELETED');
123 APP_EXCEPTION.RAISE_EXCEPTION;
124 END IF;
125 CLOSE l_lock;
126 IF (
127 ((l_table_rec.value_id = x_value_id)
128 OR ((l_table_rec.value_id IS NULL)
129 AND ( x_value_id IS NULL)))
130 AND ((l_table_rec.created_by = x_created_by)
131 OR ((l_table_rec.created_by IS NULL)
132 AND ( x_created_by IS NULL)))
133 AND ((l_table_rec.creation_date = x_creation_date)
134 OR ((l_table_rec.creation_date IS NULL)
135 AND ( x_creation_date IS NULL)))
136 AND ((l_table_rec.last_updated_by = x_last_updated_by)
137 OR ((l_table_rec.last_updated_by IS NULL)
138 AND ( x_last_updated_by IS NULL)))
139 AND ((l_table_rec.last_update_date = x_last_update_date)
140 OR ((l_table_rec.last_update_date IS NULL)
141 AND ( x_last_update_date IS NULL)))
142 AND ((l_table_rec.last_update_login = x_last_update_login)
143 OR ((l_table_rec.last_update_login IS NULL)
144 AND ( x_last_update_login IS NULL)))
145 AND ((l_table_rec.value_index = x_value_index)
146 OR ((l_table_rec.value_index IS NULL)
147 AND ( x_value_index IS NULL)))
148 AND ((l_table_rec.param_id = x_param_id)
149 OR ((l_table_rec.param_id IS NULL)
150 AND ( x_param_id IS NULL)))
151 AND ((l_table_rec.server_id = x_server_id)
152 OR ((l_table_rec.server_id IS NULL)
153 AND ( x_server_id IS NULL)))
154 AND ((l_table_rec.value = x_value)
155 OR ((l_table_rec.value IS NULL)
156 AND ( x_value IS NULL)))
157 ) THEN
158 RETURN;
159 ELSE
160 FND_MESSAGE.SET_NAME('FND','FORM_RECORD_DELETED');
161 APP_EXCEPTION.RAISE_EXCEPTION;
162 END IF;
163 END lock_row;
164 END ieo_svr_values_pvt;