1 package body GMP_PARAMETER_VALUES_PKG as
2 /* $Header: GMPPVALB.pls 115.2 2002/10/25 20:16:48 sgidugu noship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out NOCOPY VARCHAR2,
5 X_PARAMETER_ID in NUMBER,
6 X_PARAMETER_VALUE in VARCHAR2,
7 X_PARAMETER_VALUE_DESCRIPTION in VARCHAR2,
8 X_CREATION_DATE in DATE,
9 X_CREATED_BY in NUMBER,
10 X_LAST_UPDATE_DATE in DATE,
11 X_LAST_UPDATED_BY in NUMBER,
12 X_LAST_UPDATE_LOGIN in NUMBER
13 ) is
14 cursor C is select ROWID from GMP_PARAMETER_VALUES_B
15 where PARAMETER_ID = X_PARAMETER_ID
16 ;
17 begin
18 insert into GMP_PARAMETER_VALUES_B (
19 PARAMETER_ID,
20 PARAMETER_VALUE,
21 CREATION_DATE,
22 CREATED_BY,
23 LAST_UPDATE_DATE,
24 LAST_UPDATED_BY,
25 LAST_UPDATE_LOGIN
26 ) values (
27 X_PARAMETER_ID,
28 X_PARAMETER_VALUE,
29 X_CREATION_DATE,
30 X_CREATED_BY,
31 X_LAST_UPDATE_DATE,
32 X_LAST_UPDATED_BY,
33 X_LAST_UPDATE_LOGIN
34 );
35
36 insert into GMP_PARAMETER_VALUES_TL (
37 LAST_UPDATE_LOGIN,
38 PARAMETER_ID,
39 PARAMETER_VALUE,
40 PARAMETER_VALUE_DESCRIPTION,
41 CREATION_DATE,
42 CREATED_BY,
43 LAST_UPDATE_DATE,
44 LAST_UPDATED_BY,
45 LANGUAGE,
46 SOURCE_LANG
47 ) select
48 X_LAST_UPDATE_LOGIN,
49 X_PARAMETER_ID,
50 X_PARAMETER_VALUE,
51 X_PARAMETER_VALUE_DESCRIPTION,
52 X_CREATION_DATE,
53 X_CREATED_BY,
54 X_LAST_UPDATE_DATE,
55 X_LAST_UPDATED_BY,
56 L.LANGUAGE_CODE,
57 userenv('LANG')
58 from FND_LANGUAGES L
59 where L.INSTALLED_FLAG in ('I', 'B')
60 and not exists
61 (select NULL
62 from GMP_PARAMETER_VALUES_TL T
63 where T.PARAMETER_ID = X_PARAMETER_ID
64 and T.PARAMETER_VALUE = X_PARAMETER_VALUE
65 and T.LANGUAGE = L.LANGUAGE_CODE);
66
67 open c;
68 fetch c into X_ROWID;
69 if (c%notfound) then
70 close c;
71 raise no_data_found;
72 end if;
73 close c;
74
75 end INSERT_ROW;
76
77 procedure LOCK_ROW (
78 X_PARAMETER_ID in NUMBER,
79 X_PARAMETER_VALUE in VARCHAR2,
80 X_PARAMETER_VALUE_DESCRIPTION in VARCHAR2
81 ) is
82 cursor c is select
83 PARAMETER_VALUE
84 from GMP_PARAMETER_VALUES_B
85 where PARAMETER_ID = X_PARAMETER_ID
86 and PARAMETER_VALUE = X_PARAMETER_VALUE
87 for update of PARAMETER_ID nowait;
88 recinfo c%rowtype;
89
90 cursor c1 is select
91 PARAMETER_VALUE_DESCRIPTION,
92 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
93 from GMP_PARAMETER_VALUES_TL
94 where PARAMETER_ID = X_PARAMETER_ID
95 and PARAMETER_VALUE = X_PARAMETER_VALUE
96 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
97 for update of PARAMETER_ID nowait;
98 begin
99 open c;
100 fetch c into recinfo;
101 if (c%notfound) then
102 close c;
103 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
104 app_exception.raise_exception;
105 end if;
106 close c;
107 if ( ((recinfo.PARAMETER_VALUE = X_PARAMETER_VALUE)
108 OR ((recinfo.PARAMETER_VALUE is null) AND (X_PARAMETER_VALUE is null)))
109 ) then
110 null;
111 else
112 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
113 app_exception.raise_exception;
114 end if;
115
116 for tlinfo in c1 loop
117 if (tlinfo.BASELANG = 'Y') then
118 if ( ((tlinfo.PARAMETER_VALUE_DESCRIPTION = X_PARAMETER_VALUE_DESCRIPTION)
119 OR ((tlinfo.PARAMETER_VALUE_DESCRIPTION is null) AND (X_PARAMETER_VALUE_DESCRIPTION is null)))
120 ) then
121 null;
122 else
123 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
124 app_exception.raise_exception;
125 end if;
126 end if;
127 end loop;
128 return;
129 end LOCK_ROW;
130
131 procedure UPDATE_ROW (
132 X_PARAMETER_ID in NUMBER,
133 X_PARAMETER_VALUE in VARCHAR2,
134 X_PARAMETER_VALUE_DESCRIPTION in VARCHAR2,
135 X_LAST_UPDATE_DATE in DATE,
136 X_LAST_UPDATED_BY in NUMBER,
137 X_LAST_UPDATE_LOGIN in NUMBER
138 ) is
139 begin
140 update GMP_PARAMETER_VALUES_B set
141 PARAMETER_VALUE = X_PARAMETER_VALUE,
142 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
143 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
144 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
145 where PARAMETER_ID = X_PARAMETER_ID
146 and PARAMETER_VALUE = X_PARAMETER_VALUE;
147
148 if (sql%notfound) then
149 raise no_data_found;
150 end if;
151
152 update GMP_PARAMETER_VALUES_TL set
153 PARAMETER_VALUE_DESCRIPTION = X_PARAMETER_VALUE_DESCRIPTION,
154 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
155 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
156 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
157 SOURCE_LANG = userenv('LANG')
158 where PARAMETER_ID = X_PARAMETER_ID
159 and PARAMETER_VALUE = X_PARAMETER_VALUE
160 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
161
162 if (sql%notfound) then
163 raise no_data_found;
164 end if;
165 end UPDATE_ROW;
166
167 procedure DELETE_ROW (
168 X_PARAMETER_ID in NUMBER,
169 X_PARAMETER_VALUE in VARCHAR2
170 ) is
171 begin
172 delete from GMP_PARAMETER_VALUES_TL
173 where PARAMETER_ID = X_PARAMETER_ID
174 and PARAMETER_VALUE = X_PARAMETER_VALUE;
175
176 if (sql%notfound) then
177 raise no_data_found;
178 end if;
179
180 delete from GMP_PARAMETER_VALUES_B
181 where PARAMETER_ID = X_PARAMETER_ID
182 and PARAMETER_VALUE = X_PARAMETER_VALUE;
183
184 if (sql%notfound) then
185 raise no_data_found;
186 end if;
187 end DELETE_ROW;
188
189 procedure ADD_LANGUAGE
190 is
191 begin
192 delete from GMP_PARAMETER_VALUES_TL T
193 where not exists
194 (select NULL
195 from GMP_PARAMETER_VALUES_B B
196 where B.PARAMETER_ID = T.PARAMETER_ID
197 and B.PARAMETER_VALUE = T.PARAMETER_VALUE
198 );
199
200 update GMP_PARAMETER_VALUES_TL T set (
201 PARAMETER_VALUE_DESCRIPTION
202 ) = (select
203 B.PARAMETER_VALUE_DESCRIPTION
204 from GMP_PARAMETER_VALUES_TL B
205 where B.PARAMETER_ID = T.PARAMETER_ID
206 and B.PARAMETER_VALUE = T.PARAMETER_VALUE
207 and B.LANGUAGE = T.SOURCE_LANG)
208 where (
209 T.PARAMETER_ID,
210 T.LANGUAGE
211 ) in (select
212 SUBT.PARAMETER_ID,
213 SUBT.LANGUAGE
214 from GMP_PARAMETER_VALUES_TL SUBB, GMP_PARAMETER_VALUES_TL SUBT
215 where SUBB.PARAMETER_ID = SUBT.PARAMETER_ID
216 and SUBB.PARAMETER_VALUE = SUBT.PARAMETER_VALUE
217 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
218 and (SUBB.PARAMETER_VALUE_DESCRIPTION <> SUBT.PARAMETER_VALUE_DESCRIPTION
219 or (SUBB.PARAMETER_VALUE_DESCRIPTION is null and SUBT.PARAMETER_VALUE_DESCRIPTION is not null)
220 or (SUBB.PARAMETER_VALUE_DESCRIPTION is not null and SUBT.PARAMETER_VALUE_DESCRIPTION is null)
221 ));
222
223 insert into GMP_PARAMETER_VALUES_TL (
224 LAST_UPDATE_LOGIN,
225 PARAMETER_ID,
226 PARAMETER_VALUE,
227 PARAMETER_VALUE_DESCRIPTION,
228 CREATION_DATE,
229 CREATED_BY,
230 LAST_UPDATE_DATE,
231 LAST_UPDATED_BY,
232 LANGUAGE,
233 SOURCE_LANG
234 ) select
235 B.LAST_UPDATE_LOGIN,
236 B.PARAMETER_ID,
237 B.PARAMETER_VALUE,
238 B.PARAMETER_VALUE_DESCRIPTION,
239 B.CREATION_DATE,
240 B.CREATED_BY,
241 B.LAST_UPDATE_DATE,
242 B.LAST_UPDATED_BY,
243 L.LANGUAGE_CODE,
244 B.SOURCE_LANG
245 from GMP_PARAMETER_VALUES_TL B, FND_LANGUAGES L
246 where L.INSTALLED_FLAG in ('I', 'B')
247 and B.LANGUAGE = userenv('LANG')
248 and not exists
249 (select NULL
250 from GMP_PARAMETER_VALUES_TL T
251 where T.PARAMETER_ID = B.PARAMETER_ID
252 and T.PARAMETER_VALUE = B.PARAMETER_VALUE
253 and T.LANGUAGE = L.LANGUAGE_CODE);
254 end ADD_LANGUAGE;
255
256 end GMP_PARAMETER_VALUES_PKG;
[Home] [Help]