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