eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.IMC_OBJECT_METADATA_PUB

Source


1 PACKAGE BODY IMC_OBJECT_METADATA_PUB AS
2 /* $Header: imcomdb.pls 115.4 2002/11/12 21:53:08 tsli noship $ */
3 
4 /*=======================================================================*/
5 
6 FUNCTION Object_Metadata_Exists (
7   p_object_type			IN IMC_OBJECT_METADATA.object_type%TYPE
8 ) RETURN VARCHAR2 AS
9 
10   l_dummy	NUMBER;
11 
12 BEGIN
13 
14   /* Required Param Validation */
15   IF p_object_type IS NULL THEN
16     /* object type is invalid */
17     FND_MESSAGE.SET_NAME('IMC', g_invalid_object_type);
18     FND_MSG_PUB.ADD;
19     RAISE FND_API.G_EXC_ERROR;
20   END IF;
21 
22   SELECT 1
23   INTO l_dummy
24   FROM IMC_OBJECT_METADATA
25   WHERE OBJECT_TYPE = p_object_type
26   AND ROWNUM = 1;
27 
28   RETURN 'Y';
29 
30 EXCEPTION
31   WHEN NO_DATA_FOUND THEN
32     RETURN 'N';
33 
34   WHEN FND_API.G_EXC_ERROR THEN
35      RETURN FND_API.G_RET_STS_ERROR;
36 
37   WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
38      RETURN FND_API.G_RET_STS_UNEXP_ERROR;
39 
40   WHEN OTHERS THEN
41      FND_MESSAGE.SET_NAME('IMC', g_metadata_api_others_ex);
42      FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
43      FND_MSG_PUB.ADD;
44      RETURN FND_API.G_RET_STS_UNEXP_ERROR;
45 
46 END Object_Metadata_Exists;
47 
48 /*=======================================================================*/
49 
50 PROCEDURE Update_Record (
51   p_object_type		IN IMC_OBJECT_METADATA.object_type%TYPE,
52   p_description		IN IMC_OBJECT_METADATA.description%TYPE,
53   p_function_name	IN IMC_OBJECT_METADATA.function_name%TYPE,
54   p_parameter_name	IN IMC_OBJECT_METADATA.parameter_name%TYPE,
55   p_enabled		IN IMC_OBJECT_METADATA.enabled%TYPE,
56   p_application_id	IN IMC_OBJECT_METADATA.application_id%TYPE,
57   p_additional_value1	IN IMC_OBJECT_METADATA.additional_value1%TYPE,
58   p_additional_value2	IN IMC_OBJECT_METADATA.additional_value2%TYPE,
59   p_additional_value3	IN IMC_OBJECT_METADATA.additional_value3%TYPE,
60   p_additional_value4	IN IMC_OBJECT_METADATA.additional_value4%TYPE,
61   p_additional_value5	IN IMC_OBJECT_METADATA.additional_value5%TYPE,
62   p_created_by		IN IMC_OBJECT_METADATA.created_by%TYPE,
63   p_creation_date	IN IMC_OBJECT_METADATA.creation_date%TYPE,
64   p_last_updated_by	IN IMC_OBJECT_METADATA.last_updated_by%TYPE,
65   p_last_update_date	IN IMC_OBJECT_METADATA.last_update_date%TYPE,
66   p_last_update_login	IN IMC_OBJECT_METADATA.last_update_login%TYPE
67 ) AS
68 
69 BEGIN
70 
71   UPDATE IMC_OBJECT_METADATA SET
72     DESCRIPTION = p_description,
73     FUNCTION_NAME = p_function_name,
74     PARAMETER_NAME = p_parameter_name,
75     ENABLED = p_enabled,
76     APPLICATION_ID = DECODE(p_application_id, FND_API.G_MISS_NUM, NULL, p_application_id),
77     ADDITIONAL_VALUE1 = DECODE(p_additional_value1, FND_API.G_MISS_CHAR, NULL, p_additional_value1),
78     ADDITIONAL_VALUE2 = DECODE(p_additional_value2, FND_API.G_MISS_CHAR, NULL, p_additional_value2),
79     ADDITIONAL_VALUE3 = DECODE(p_additional_value3, FND_API.G_MISS_CHAR, NULL, p_additional_value3),
80     ADDITIONAL_VALUE4 = DECODE(p_additional_value4, FND_API.G_MISS_CHAR, NULL, p_additional_value4),
81     ADDITIONAL_VALUE5 = DECODE(p_additional_value5, FND_API.G_MISS_CHAR, NULL, p_additional_value5),
82     CREATED_BY = p_created_by,
83     CREATION_DATE = p_creation_date,
84     LAST_UPDATED_BY = p_last_updated_by,
85     LAST_UPDATE_DATE = p_last_update_date,
86     LAST_UPDATE_LOGIN = p_last_update_login
87   WHERE OBJECT_TYPE = p_object_type;
88 
89   IF SQL%NOTFOUND THEN
90     RAISE NO_DATA_FOUND;
91   END IF;
92 
93   COMMIT;
94 
95 END Update_Record;
96 
97 /*=======================================================================*/
98 
99 PROCEDURE Insert_Record (
100   p_metadata_id		IN IMC_OBJECT_METADATA.metadata_id%TYPE,
101   p_object_type		IN IMC_OBJECT_METADATA.object_type%TYPE,
102   p_description		IN IMC_OBJECT_METADATA.description%TYPE,
103   p_function_name	IN IMC_OBJECT_METADATA.function_name%TYPE,
104   p_parameter_name	IN IMC_OBJECT_METADATA.parameter_name%TYPE,
105   p_enabled		IN IMC_OBJECT_METADATA.enabled%TYPE,
106   p_application_id	IN IMC_OBJECT_METADATA.application_id%TYPE,
107   p_additional_value1	IN IMC_OBJECT_METADATA.additional_value1%TYPE,
108   p_additional_value2	IN IMC_OBJECT_METADATA.additional_value2%TYPE,
109   p_additional_value3	IN IMC_OBJECT_METADATA.additional_value3%TYPE,
110   p_additional_value4	IN IMC_OBJECT_METADATA.additional_value4%TYPE,
111   p_additional_value5	IN IMC_OBJECT_METADATA.additional_value5%TYPE,
112   p_created_by		IN IMC_OBJECT_METADATA.created_by%TYPE,
113   p_creation_date	IN IMC_OBJECT_METADATA.creation_date%TYPE,
114   p_last_updated_by	IN IMC_OBJECT_METADATA.last_updated_by%TYPE,
115   p_last_update_date	IN IMC_OBJECT_METADATA.last_update_date%TYPE,
116   p_last_update_login	IN IMC_OBJECT_METADATA.last_update_login%TYPE
117 ) AS
118 
119 BEGIN
120 
121   INSERT INTO IMC_OBJECT_METADATA (
122     METADATA_ID,
123     OBJECT_TYPE,
124     DESCRIPTION,
125     FUNCTION_NAME,
126     PARAMETER_NAME,
127     ENABLED,
128     APPLICATION_ID,
129     ADDITIONAL_VALUE1,
130     ADDITIONAL_VALUE2,
131     ADDITIONAL_VALUE3,
132     ADDITIONAL_VALUE4,
133     ADDITIONAL_VALUE5,
134     CREATED_BY,
135     CREATION_DATE,
136     LAST_UPDATED_BY,
137     LAST_UPDATE_DATE,
138     LAST_UPDATE_LOGIN
139   ) VALUES (
140     p_metadata_id,
141     p_object_type,
142     p_description,
143     p_function_name,
144     p_parameter_name,
145     p_enabled,
146     DECODE(p_application_id, FND_API.G_MISS_NUM, NULL, p_application_id),
147     DECODE(p_additional_value1, FND_API.G_MISS_CHAR, NULL, p_additional_value1),
148     DECODE(p_additional_value2, FND_API.G_MISS_CHAR, NULL, p_additional_value2),
149     DECODE(p_additional_value3, FND_API.G_MISS_CHAR, NULL, p_additional_value3),
150     DECODE(p_additional_value4, FND_API.G_MISS_CHAR, NULL, p_additional_value4),
151     DECODE(p_additional_value5, FND_API.G_MISS_CHAR, NULL, p_additional_value5),
152     p_created_by,
153     p_creation_date,
154     p_last_updated_by,
155     p_last_update_date,
156     p_last_update_login
157   );
158 
159   COMMIT;
160 
161 END Insert_Record;
162 
163 /*=======================================================================*/
164 
165 PROCEDURE Add_Object_Metadata (
166   p_object_type			IN IMC_OBJECT_METADATA.object_type%TYPE,
167   p_description			IN IMC_OBJECT_METADATA.description%TYPE,
168   p_function_name		IN IMC_OBJECT_METADATA.function_name%TYPE,
169   p_parameter_name		IN IMC_OBJECT_METADATA.parameter_name%TYPE,
170   p_enabled			IN IMC_OBJECT_METADATA.enabled%TYPE,
171   p_application_id		IN IMC_OBJECT_METADATA.application_id%TYPE,
172   p_additional_value1		IN IMC_OBJECT_METADATA.additional_value1%TYPE,
173   p_additional_value2		IN IMC_OBJECT_METADATA.additional_value2%TYPE,
174   p_additional_value3		IN IMC_OBJECT_METADATA.additional_value3%TYPE,
175   p_additional_value4		IN IMC_OBJECT_METADATA.additional_value4%TYPE,
176   p_additional_value5		IN IMC_OBJECT_METADATA.additional_value5%TYPE,
177   x_return_status		OUT NOCOPY VARCHAR2,
178   x_msg_count			OUT NOCOPY VARCHAR2,
179   x_msg_data			OUT NOCOPY VARCHAR2
180 ) AS
181 
182   l_metadata_id		IMC_OBJECT_METADATA.metadata_id%TYPE;
183   l_last_update_login	IMC_OBJECT_METADATA.last_update_login%TYPE;
184 
185 BEGIN
186 
187   /* init last_update_login */
188   IF (FND_GLOBAL.conc_login_id = -1) OR (FND_GLOBAL.conc_login_id IS NULL) THEN
189     l_last_update_login := FND_GLOBAL.login_id;
190   ELSE
191     l_last_update_login := FND_GLOBAL.conc_login_id;
192   END IF;
193 
194   IF Object_Metadata_Exists(p_object_type) = 'Y' THEN
195     Update_Record (
196       p_object_type,
197       p_description,
198       p_function_name,
199       p_parameter_name,
200       nvl(p_enabled, 'Y'),
201       p_application_id,
202       p_additional_value1,
203       p_additional_value2,
204       p_additional_value3,
205       p_additional_value4,
206       p_additional_value5,
207       nvl(FND_GLOBAL.user_id, -1), /* Created by */
208       SYSDATE, /* Creation date */
209       nvl(FND_GLOBAL.user_id, -1), /* Last updated by */
210       SYSDATE, /* Last update date */
211       l_last_update_login /* Last update login */
212     );
213   ELSE
214     /* init metadata id */
215     SELECT IMC_OBJECT_METADATA_S.NEXTVAL INTO l_metadata_id FROM DUAL;
216 
217     Insert_Record (
218       l_metadata_id,
219       p_object_type,
220       p_description,
221       p_function_name,
222       p_parameter_name,
223       nvl(p_enabled, 'Y'),
224       p_application_id,
225       p_additional_value1,
226       p_additional_value2,
227       p_additional_value3,
228       p_additional_value4,
229       p_additional_value5,
230       nvl(FND_GLOBAL.user_id, -1), /* Created by */
231       SYSDATE, /* Creation date */
232       nvl(FND_GLOBAL.user_id, -1), /* Last updated by */
233       SYSDATE, /* Last update date */
234       l_last_update_login /* Last update login */
235     );
236   END IF;
237 
238   x_return_status := FND_API.G_RET_STS_SUCCESS;
239 
240 EXCEPTION
241   WHEN FND_API.G_EXC_ERROR THEN
242     x_return_status := FND_API.G_RET_STS_ERROR;
243     FND_MSG_PUB.Count_And_Get (
244       p_encoded => FND_API.G_FALSE,
245       p_count => x_msg_count,
246       p_data => x_msg_data
247     );
248   WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
249     x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
250     FND_MSG_PUB.Count_And_Get (
251       p_encoded => FND_API.G_FALSE,
252       p_count => x_msg_count,
253       p_data => x_msg_data
254     );
255   WHEN OTHERS THEN
256     x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
257     FND_MSG_PUB.Count_And_Get (
258       p_encoded => FND_API.G_FALSE,
259       p_count => x_msg_count,
260       p_data => x_msg_data
261     );
262 
263 END Add_Object_Metadata;
264 
265 /*=======================================================================*/
266 
267 PROCEDURE Remove_Object_Metadata (
268   p_object_type			IN IMC_OBJECT_METADATA.object_type%TYPE,
269   x_return_status		OUT NOCOPY VARCHAR2,
270   x_msg_count			OUT NOCOPY VARCHAR2,
271   x_msg_data			OUT NOCOPY VARCHAR2
272 ) AS
273 
274 BEGIN
275 
276   DELETE FROM IMC_OBJECT_METADATA
277   WHERE OBJECT_TYPE = p_object_type;
278 
279   IF SQL%NOTFOUND THEN
280     RAISE NO_DATA_FOUND;
281   END IF;
282 
283   x_return_status := FND_API.G_RET_STS_SUCCESS;
284 
285 EXCEPTION
286   WHEN NO_DATA_FOUND THEN
287     x_return_status := FND_API.G_RET_STS_SUCCESS;
288     FND_MESSAGE.SET_NAME('IMC', g_no_metadata_for_obj_type);
289     FND_MSG_PUB.ADD;
290     FND_MSG_PUB.Count_And_Get (
291       p_encoded => FND_API.G_FALSE,
292       p_count => x_msg_count,
293       p_data => x_msg_data
294     );
295   WHEN OTHERS THEN
296     x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
297     FND_MSG_PUB.Count_And_Get (
298       p_encoded => FND_API.G_FALSE,
299       p_count => x_msg_count,
300       p_data => x_msg_data
301     );
302 
303 END Remove_Object_Metadata;
304 
305 /*=======================================================================*/
306 
307 PROCEDURE Get_Object_Metadata (
308   p_object_type                 IN IMC_OBJECT_METADATA.object_type%TYPE,
309   x_metadata_info		OUT NOCOPY ref_cursor_obj_metadata,
310   x_return_status		OUT NOCOPY VARCHAR2,
311   x_msg_count			OUT NOCOPY VARCHAR2,
312   x_msg_data			OUT NOCOPY VARCHAR2
313 ) AS
314 
315   l_query	VARCHAR2(1000);
316 
317 BEGIN
318 
319   l_query := 'SELECT object_type, description, function_name, parameter_name, enabled, application_id, additional_value1, additional_value2, additional_value3, additional_value4, additional_value5 ';
320   l_query := l_query || 'FROM IMC_OBJECT_METADATA ';
321 
322   IF p_object_type IS NOT NULL THEN
323     l_query := l_query || 'WHERE OBJECT_TYPE = ''' || p_object_type || '''';
324   ELSE
325     l_query := l_query || 'ORDER BY OBJECT_TYPE';
326   END IF;
327 
328   OPEN x_metadata_info FOR l_query;
329 
330   IF x_metadata_info%NOTFOUND THEN
331     RAISE NO_DATA_FOUND;
332   END IF;
333 
334   x_return_status := FND_API.G_RET_STS_SUCCESS;
335 
336 EXCEPTION
337   WHEN NO_DATA_FOUND THEN
338     x_return_status := FND_API.G_RET_STS_ERROR;
339     FND_MESSAGE.SET_NAME('IMC', g_no_metadata_for_obj_type);
340     FND_MSG_PUB.ADD;
341     FND_MSG_PUB.Count_And_Get (
342       p_encoded => FND_API.G_FALSE,
343       p_count => x_msg_count,
344       p_data => x_msg_data
345     );
346   WHEN FND_API.G_EXC_ERROR THEN
347     x_return_status := FND_API.G_RET_STS_ERROR;
348     FND_MSG_PUB.Count_And_Get (
349       p_encoded => FND_API.G_FALSE,
350       p_count => x_msg_count,
351       p_data => x_msg_data
352     );
353   WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
354     x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
355     FND_MSG_PUB.Count_And_Get (
356       p_encoded => FND_API.G_FALSE,
357       p_count => x_msg_count,
358       p_data => x_msg_data
359     );
360   WHEN OTHERS THEN
361     x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
362     FND_MSG_PUB.Count_And_Get (
363       p_encoded => FND_API.G_FALSE,
364       p_count => x_msg_count,
365       p_data => x_msg_data
366     );
367 
368 END Get_Object_Metadata;
369 
370 /*=======================================================================*/
371 
372 FUNCTION Get_Function_Name (
373   p_object_type			IN IMC_OBJECT_METADATA.object_type%TYPE
374 ) RETURN IMC_OBJECT_METADATA.function_name%TYPE AS
375 
376   l_return_val	IMC_OBJECT_METADATA.function_name%TYPE;
377 
378 BEGIN
379 
380   IF p_object_type IS NULL THEN
381     RETURN NULL;
382   ELSE
383     SELECT function_name
384     INTO l_return_val
385     FROM IMC_OBJECT_METADATA
386     WHERE OBJECT_TYPE = p_object_type
387     AND ROWNUM = 1;
388   END IF;
389 
390   RETURN l_return_val;
391 
392 EXCEPTION
393   WHEN NO_DATA_FOUND THEN
394     RETURN NULL;
395 
396 END Get_Function_Name;
397 
398 /*=======================================================================*/
399 
400 FUNCTION Get_Parameter_Name (
401   p_object_type			IN IMC_OBJECT_METADATA.object_type%TYPE
402 ) RETURN IMC_OBJECT_METADATA.parameter_name%TYPE AS
403 
404   l_return_val	IMC_OBJECT_METADATA.parameter_name%TYPE;
405 
406 BEGIN
407 
408   IF p_object_type IS NULL THEN
409     RETURN NULL;
410   ELSE
411     SELECT parameter_name
412     INTO l_return_val
413     FROM IMC_OBJECT_METADATA
414     WHERE OBJECT_TYPE = p_object_type
415     AND ROWNUM = 1;
416   END IF;
417 
418   RETURN l_return_val;
419 
420 EXCEPTION
421   WHEN NO_DATA_FOUND THEN
422     RETURN NULL;
423 
424 END Get_Parameter_Name;
425 
426 /*=======================================================================*/
427 
428 FUNCTION Get_Additional_Value (
429   p_object_type			IN IMC_OBJECT_METADATA.object_type%TYPE,
430   p_index			IN NUMBER
431 ) RETURN VARCHAR2 AS
432 
433   l_return_val	VARCHAR2(150); /* Type of all additional value columns */
434   l_cursorId	INTEGER;
435   l_column_name	VARCHAR2(30);
436   l_query	VARCHAR2(1000);
437   l_dummy	INTEGER;
438 
439 BEGIN
440 
441   IF p_object_type IS NULL THEN
442     RETURN NULL;
443   ELSE
444     -- TO-DO: Execute query, return column value.
445     l_cursorId := DBMS_SQL.OPEN_CURSOR;
446     l_column_name := 'ADDITIONAL_VALUE' || p_index;
447     l_query := 'SELECT ' || l_column_name || ' ' ||
448                'FROM IMC_OBJECT_METADATA ' ||
449                'WHERE OBJECT_TYPE = ''' || p_object_type || ''' ' ||
450                'AND ROWNUM = 1';
451     DBMS_SQL.PARSE(l_cursorId, l_query, DBMS_SQL.V7);
452     DBMS_SQL.DEFINE_COLUMN(l_cursorId, 1, l_return_val, 150);
453 
454     l_dummy := DBMS_SQL.EXECUTE(l_cursorId);
455 
456     LOOP
457       IF DBMS_SQL.FETCH_ROWS(l_cursorId) = 0 THEN
458         RAISE NO_DATA_FOUND;
459       ELSE
460         DBMS_SQL.COLUMN_VALUE(l_cursorId, 1, l_return_val);
461         DBMS_SQL.CLOSE_CURSOR(l_cursorId);
462         RETURN l_return_val;
463       END IF;
464     END LOOP;
465 
466   END IF;
467 
468   RETURN l_return_val;
469 
470 EXCEPTION
471   WHEN NO_DATA_FOUND THEN
472     DBMS_SQL.CLOSE_CURSOR(l_cursorId);
473     RETURN NULL;
474   WHEN OTHERS THEN
475     DBMS_SQL.CLOSE_CURSOR(l_cursorId);
476     RETURN NULL;
477 
478 END Get_Additional_Value;
479 
480 /*=======================================================================*/
481 
482 FUNCTION Get_File_Name (
483   p_object_type			IN IMC_OBJECT_METADATA.object_type%TYPE
484 ) RETURN VARCHAR2 AS
485 
486   l_function_name	IMC_OBJECT_METADATA.function_name%TYPE;
487   l_return_val		VARCHAR2(30);
488 
489 BEGIN
490 
491   IF p_object_type IS NULL THEN
492     RETURN NULL;
493   ELSE
494     SELECT FUNCTION_NAME
495     INTO l_function_name
496     FROM IMC_OBJECT_METADATA
497     WHERE OBJECT_TYPE = p_object_type
498     AND ROWNUM = 1;
499   END IF;
500 
501   SELECT WEB_HTML_CALL
502   INTO l_return_val
503   FROM FND_FORM_FUNCTIONS
504   WHERE FUNCTION_NAME = l_function_name;
505 
506   RETURN l_return_val;
507 
508 EXCEPTION
509   WHEN NO_DATA_FOUND THEN
510     RETURN NULL;
511 
512 END Get_File_Name;
513 
514 END IMC_OBJECT_METADATA_PUB;