[Home] [Help]
PACKAGE BODY: APPS.FND_OAM_DSCFG_PROPERTIES_PKG
Source
1 PACKAGE BODY FND_OAM_DSCFG_PROPERTIES_PKG as
2 /* $Header: AFOAMDSCPROPB.pls 120.2 2005/12/19 09:42 ilawler noship $ */
3
4 ----------------------------------------
5 -- Private Body Constants
6 ----------------------------------------
7 PKG_NAME CONSTANT VARCHAR2(30) := 'DSCFG_PROPERTIES_PKG.';
8
9 --stateless, only contains a table handler to insert new properties
10
11 ----------------------------------------
12 -- Public/Private Procedures/Functions
13 ----------------------------------------
14
15 -- Public
16 PROCEDURE ADD_PROPERTY(p_parent_type IN VARCHAR2,
17 p_parent_id IN NUMBER,
18 p_property_name IN VARCHAR2,
19 p_datatype IN VARCHAR2,
20 p_canonical_value IN VARCHAR2,
21 x_property_id OUT NOCOPY NUMBER)
22 IS
23 l_ctxt VARCHAR2(60) := PKG_NAME||'ADD_PROPERTY';
24
25 l_property_id NUMBER;
26 BEGIN
27 fnd_oam_debug.log(2, l_ctxt, 'ENTER');
28
29 --make sure a configuration instance is initialized, we don't need it but
30 --we need this as a security harness check.
31 IF NOT FND_OAM_DSCFG_INSTANCES_PKG.IS_INITIALIZED THEN
32 RAISE NO_DATA_FOUND;
33 END IF;
34
35 --do the insert
36 INSERT INTO fnd_oam_dscfg_properties (PROPERTY_ID,
37 PARENT_TYPE,
38 PARENT_ID,
39 PROPERTY_NAME,
40 DATATYPE,
41 CANONICAL_VALUE,
42 CREATED_BY,
43 CREATION_DATE,
44 LAST_UPDATED_BY,
45 LAST_UPDATE_DATE,
46 LAST_UPDATE_LOGIN)
47 VALUES (FND_OAM_DSCFG_PROPERTIES_S.NEXTVAL,
48 p_parent_type,
49 p_parent_id,
50 p_property_name,
51 p_datatype,
52 p_canonical_value,
53 FND_GLOBAL.USER_ID,
54 SYSDATE,
55 FND_GLOBAL.USER_ID,
56 SYSDATE,
57 FND_GLOBAL.USER_ID)
58 RETURNING PROPERTY_ID INTO l_property_id;
59
60 x_property_id := l_property_id;
61
62 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
63 EXCEPTION
64 WHEN NO_DATA_FOUND THEN
65 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
66 RAISE;
67 WHEN OTHERS THEN
68 fnd_oam_debug.log(6, l_ctxt, 'Unexpected Error: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))');
69 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
70 RAISE;
71 END;
72
73 -- Public
74 PROCEDURE ADD_PROPERTY(p_parent_type IN VARCHAR2,
75 p_parent_id IN NUMBER,
76 p_property_name IN VARCHAR2,
77 p_varchar2_value IN VARCHAR2,
78 x_property_id OUT NOCOPY NUMBER)
79 IS
80 BEGIN
81 ADD_PROPERTY(p_parent_type,
82 p_parent_id,
83 p_property_name,
84 FND_OAM_DSCFG_API_PKG.G_DATATYPE_VARCHAR2,
85 p_varchar2_value,
86 x_property_id);
87 END;
88
89 -- Public
90 PROCEDURE ADD_PROPERTY(p_parent_type IN VARCHAR2,
91 p_parent_id IN NUMBER,
92 p_property_name IN VARCHAR2,
93 p_number_value IN NUMBER,
94 x_property_id OUT NOCOPY NUMBER)
95 IS
96 BEGIN
97 ADD_PROPERTY(p_parent_type,
98 p_parent_id,
99 p_property_name,
100 FND_OAM_DSCFG_API_PKG.G_DATATYPE_NUMBER,
101 FND_OAM_DSCFG_UTILS_PKG.NUMBER_TO_CANONICAL(p_number_value),
102 x_property_id);
103 END;
104
105
106 -- Public
107 PROCEDURE ADD_PROPERTY(p_parent_type IN VARCHAR2,
108 p_parent_id IN NUMBER,
109 p_property_name IN VARCHAR2,
110 p_date_value IN DATE,
111 x_property_id OUT NOCOPY NUMBER)
112 IS
113 BEGIN
114 ADD_PROPERTY(p_parent_type,
115 p_parent_id,
116 p_property_name,
117 FND_OAM_DSCFG_API_PKG.G_DATATYPE_DATE,
118 FND_OAM_DSCFG_UTILS_PKG.DATE_TO_CANONICAL(p_date_value),
119 x_property_id);
120 END;
121
122 -- Public
123 PROCEDURE ADD_PROPERTY(p_parent_type IN VARCHAR2,
124 p_parent_id IN NUMBER,
125 p_property_name IN VARCHAR2,
126 p_boolean_value IN BOOLEAN,
127 x_property_id OUT NOCOPY NUMBER)
128 IS
129 BEGIN
130 ADD_PROPERTY(p_parent_type,
131 p_parent_id,
132 p_property_name,
133 FND_OAM_DSCFG_API_PKG.G_DATATYPE_BOOLEAN,
134 FND_OAM_DSCFG_UTILS_PKG.BOOLEAN_TO_CANONICAL(p_boolean_value),
135 x_property_id);
136 END;
137
138 -- Public
139 PROCEDURE ADD_PROPERTY_ROWID(p_parent_type IN VARCHAR2,
140 p_parent_id IN NUMBER,
141 p_property_name IN VARCHAR2,
142 p_rowid_value IN ROWID,
143 x_property_id OUT NOCOPY NUMBER)
144 IS
145 BEGIN
146 ADD_PROPERTY(p_parent_type,
147 p_parent_id,
148 p_property_name,
149 FND_OAM_DSCFG_API_PKG.G_DATATYPE_ROWID,
150 TO_CHAR(p_rowid_value),
151 x_property_id);
152 END;
153
154 -- Public
155 PROCEDURE ADD_PROPERTY_RAW(p_parent_type IN VARCHAR2,
156 p_parent_id IN NUMBER,
157 p_property_name IN VARCHAR2,
158 p_raw_value IN RAW,
159 x_property_id OUT NOCOPY NUMBER)
160 IS
161 BEGIN
162 ADD_PROPERTY(p_parent_type,
163 p_parent_id,
164 p_property_name,
165 FND_OAM_DSCFG_API_PKG.G_DATATYPE_RAW,
166 TO_CHAR(p_raw_value),
167 x_property_id);
168 END;
169
170 -- Public
171 PROCEDURE GET_PROPERTY_CANONICAL_VALUE(p_parent_type IN VARCHAR2,
172 p_parent_id IN NUMBER,
173 p_property_name IN VARCHAR2,
174 x_canonical_value OUT NOCOPY VARCHAR2)
175 IS
176 l_ctxt VARCHAR2(60) := PKG_NAME||'GET_PROPERTY_CANONICAL_VALUE';
177
178 BEGIN
179 fnd_oam_debug.log(2, l_ctxt, 'ENTER');
180
181 SELECT canonical_value
182 INTO x_canonical_value
183 FROM fnd_oam_dscfg_properties
184 WHERE parent_type = p_parent_type
185 AND parent_id = p_parent_id
186 AND property_name = p_property_name;
187
188 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
189 EXCEPTION
190 WHEN NO_DATA_FOUND THEN
191 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
192 RAISE;
193 WHEN TOO_MANY_ROWS THEN
194 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
195 RAISE;
196 WHEN OTHERS THEN
197 fnd_oam_debug.log(6, l_ctxt, 'Unexpected Error: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))');
198 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
199 RAISE;
200 END;
201
202 -- Public
203 PROCEDURE GET_PROPERTY_VALUE(p_parent_type IN VARCHAR2,
204 p_parent_id IN NUMBER,
205 p_property_name IN VARCHAR2,
206 x_varchar2_value OUT NOCOPY VARCHAR2)
207 IS
208 BEGIN
209 GET_PROPERTY_CANONICAL_VALUE(p_parent_type,
210 p_parent_id,
211 p_property_name,
212 x_varchar2_value);
213 END;
214
215 -- Convenience wrapper on generic GET_PROPERTY_CANONICAL_VALUE for datatype NUMBER
216 PROCEDURE GET_PROPERTY_VALUE(p_parent_type IN VARCHAR2,
217 p_parent_id IN NUMBER,
218 p_property_name IN VARCHAR2,
219 x_number_value OUT NOCOPY NUMBER)
220 IS
221 l_canonical_value VARCHAR2(4000);
222 BEGIN
223 GET_PROPERTY_CANONICAL_VALUE(p_parent_type,
224 p_parent_id,
225 p_property_name,
226 l_canonical_value);
227 x_number_value := FND_OAM_DSCFG_UTILS_PKG.CANONICAL_TO_NUMBER(l_canonical_value);
228 END;
229
230
231 -- Convenience wrapper on generic GET_PROPERTY_CANONICAL_VALUE for datatype DATE
232 PROCEDURE GET_PROPERTY_VALUE(p_parent_type IN VARCHAR2,
233 p_parent_id IN NUMBER,
234 p_property_name IN VARCHAR2,
235 x_date_value OUT NOCOPY DATE)
236 IS
237 l_canonical_value VARCHAR2(4000);
238 BEGIN
239 GET_PROPERTY_CANONICAL_VALUE(p_parent_type,
240 p_parent_id,
241 p_property_name,
242 l_canonical_value);
243 x_date_value := FND_OAM_DSCFG_UTILS_PKG.CANONICAL_TO_DATE(l_canonical_value);
244 END;
245
246 -- Public
247 PROCEDURE SET_OR_ADD_PROPERTY(p_parent_type IN VARCHAR2,
248 p_parent_id IN NUMBER,
249 p_property_name IN VARCHAR2,
250 p_datatype IN VARCHAR2,
251 p_canonical_value IN VARCHAR2,
252 x_property_id OUT NOCOPY NUMBER)
253 IS
254 l_ctxt VARCHAR2(60) := PKG_NAME||'SET_OR_ADD_PROPERTY';
255
256 l_property_id NUMBER;
257 BEGIN
258 fnd_oam_debug.log(2, l_ctxt, 'ENTER');
259
260 --make sure a configuration instance is initialized, we don't need it but
261 --we need this as a security harness check.
262 IF NOT FND_OAM_DSCFG_INSTANCES_PKG.IS_INITIALIZED THEN
263 RAISE NO_DATA_FOUND;
264 END IF;
265
266 --see if the property's present
267 BEGIN
268 SELECT property_id
269 INTO l_property_id
270 FROM fnd_oam_dscfg_properties
271 WHERE parent_type = p_parent_type
272 AND parent_id = p_parent_id
273 AND property_name = p_property_name;
274
275 --if we didn't throw an exception, we found a single row we can update
276 --don't allow changing the datatype
277 UPDATE fnd_oam_dscfg_properties
278 SET canonical_value = p_canonical_value,
279 last_updated_by = FND_GLOBAL.USER_ID,
280 last_update_date = SYSDATE,
281 last_update_login = FND_GLOBAL.USER_ID
282 WHERE property_id = l_property_id;
283 x_property_id := l_property_id;
284
285 EXCEPTION
286 WHEN NO_DATA_FOUND THEN
287 --do an add
288 ADD_PROPERTY(p_parent_type,
289 p_parent_id,
290 p_property_name,
291 p_datatype,
292 p_canonical_value,
293 x_property_id);
294 END;
295
296 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
297 EXCEPTION
298 WHEN NO_DATA_FOUND THEN
299 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
300 RAISE;
301 WHEN TOO_MANY_ROWS THEN
302 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
303 RAISE;
304 WHEN OTHERS THEN
305 fnd_oam_debug.log(6, l_ctxt, 'Unexpected Error: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))');
306 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
307 RAISE;
308 END;
309
310 -- Public
311 PROCEDURE SET_OR_ADD_PROPERTY(p_parent_type IN VARCHAR2,
312 p_parent_id IN NUMBER,
313 p_property_name IN VARCHAR2,
314 p_varchar2_value IN VARCHAR2,
315 x_property_id OUT NOCOPY NUMBER)
316 IS
317 BEGIN
318 SET_OR_ADD_PROPERTY(p_parent_type,
319 p_parent_id,
320 p_property_name,
321 FND_OAM_DSCFG_API_PKG.G_DATATYPE_VARCHAR2,
322 p_varchar2_value,
323 x_property_id);
324 END;
325
326 -- Public
327 PROCEDURE SET_OR_ADD_PROPERTY(p_parent_type IN VARCHAR2,
328 p_parent_id IN NUMBER,
329 p_property_name IN VARCHAR2,
330 p_number_value IN NUMBER,
331 x_property_id OUT NOCOPY NUMBER)
332 IS
333 BEGIN
334 SET_OR_ADD_PROPERTY(p_parent_type,
335 p_parent_id,
336 p_property_name,
337 FND_OAM_DSCFG_API_PKG.G_DATATYPE_NUMBER,
338 FND_OAM_DSCFG_UTILS_PKG.NUMBER_TO_CANONICAL(p_number_value),
339 x_property_id);
340 END;
341
342 -- Public
343 PROCEDURE SET_OR_ADD_PROPERTY(p_parent_type IN VARCHAR2,
344 p_parent_id IN NUMBER,
345 p_property_name IN VARCHAR2,
346 p_date_value IN DATE,
347 x_property_id OUT NOCOPY NUMBER)
348 IS
349 BEGIN
350 SET_OR_ADD_PROPERTY(p_parent_type,
351 p_parent_id,
352 p_property_name,
353 FND_OAM_DSCFG_API_PKG.G_DATATYPE_DATE,
354 FND_OAM_DSCFG_UTILS_PKG.DATE_TO_CANONICAL(p_date_value),
355 x_property_id);
356 END;
357
358 -- Public
359 FUNCTION DELETE_PROPERTIES(p_parent_type IN VARCHAR2,
360 p_parent_id IN NUMBER)
361 RETURN BOOLEAN
362 IS
363 l_ctxt VARCHAR2(60) := PKG_NAME||'DELETE_PROPERTIES';
364
365 BEGIN
366 fnd_oam_debug.log(2, l_ctxt, 'ENTER');
367
368 --just delete the property
369 DELETE FROM fnd_oam_dscfg_properties
370 WHERE parent_type = p_parent_type
371 AND parent_id = p_parent_id;
372 fnd_oam_debug.log(1, l_ctxt, 'Deleted '||SQL%ROWCOUNT||' properties.');
373
374 --success
375 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
376 RETURN TRUE;
377 EXCEPTION
378 WHEN OTHERS THEN
379 fnd_oam_debug.log(6, l_ctxt, 'Unexpected Error: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))');
380 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
381 RETURN FALSE;
382 END;
383
384
385 -- Public
386 FUNCTION DELETE_PROPERTY(p_property_id IN NUMBER)
387 RETURN BOOLEAN
388 IS
389 l_ctxt VARCHAR2(60) := PKG_NAME||'DELETE_PROPERTY';
390
391 BEGIN
392 fnd_oam_debug.log(2, l_ctxt, 'ENTER');
393
394 --just delete the property
395 DELETE FROM fnd_oam_dscfg_properties
396 WHERE property_id = p_property_id;
397
398 --success
399 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
400 RETURN TRUE;
401 EXCEPTION
402 WHEN OTHERS THEN
403 fnd_oam_debug.log(6, l_ctxt, 'Unexpected Error: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))');
404 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
405 RETURN FALSE;
406 END;
407
408 END FND_OAM_DSCFG_PROPERTIES_PKG;