[Home] [Help]
PACKAGE BODY: APPS.JTF_OBJECTS_PUB
Source
1 PACKAGE BODY jtf_objects_pub AS
2 /* $Header: jtfptkob.pls 120.2 2005/08/10 20:43:51 akaran ship $ */
3 g_pkg_name VARCHAR2(30) := 'JTF_OBJECTS_PUB';
4
5 TYPE OBJECT_PG_REC IS RECORD
6 (
7 OBJECT_CODE VARCHAR2(30),
8 APPLICATION_ID NUMBER,
9 PG_FUNCTION FND_FORM_FUNCTIONS.FUNCTION_NAME%TYPE,
10 PG_PARAMS VARCHAR2(2000)
11 );
12
13 TYPE OBJECT_PG_TBL IS TABLE OF OBJECT_PG_REC INDEX BY BINARY_INTEGER;
14
15 G_OBJECT_PG_TBL OBJECT_PG_TBL;
16
17
18 FUNCTION jtf_obj_select_stmt (
19 select_id IN jtf_objects_b.select_id%TYPE DEFAULT NULL,
20 select_name IN jtf_objects_b.select_name%TYPE DEFAULT NULL,
21 select_details IN jtf_objects_b.select_details%TYPE DEFAULT NULL,
22 from_table IN jtf_objects_b.from_table%TYPE DEFAULT NULL,
23 where_clause IN jtf_objects_b.where_clause%TYPE DEFAULT NULL,
24 p_inactive_clause IN jtf_objects_b.inactive_clause%TYPE DEFAULT NULL,
25 order_by_clause IN jtf_objects_b.order_by_clause%TYPE DEFAULT NULL
26 )
27 RETURN VARCHAR2
28 IS
29 l_select_id jtf_objects_b.select_id%TYPE := select_id;
30 l_select_name jtf_objects_b.select_name%TYPE := select_name;
31 l_select_details jtf_objects_b.select_details%TYPE
32 := select_details;
33 l_from_table jtf_objects_b.from_table%TYPE := from_table;
34 l_where_clause jtf_objects_b.where_clause%TYPE := where_clause;
35 l_order_by_clause jtf_objects_b.order_by_clause%TYPE
36 := order_by_clause;
37 --l_select_statement VARCHAR2(6000);
38 str VARCHAR2(6000);
39 initialized BOOLEAN := FALSE;
40 BEGIN
41 IF (l_from_table IS NULL)
42 THEN
43 RETURN NULL;
44 END IF;
45
46 IF l_select_id IS NULL
47 THEN
48 IF l_select_name IS NULL
49 THEN
50 IF l_select_details IS NULL
51 THEN
52 RETURN NULL;
53 ELSE
54 str := 'select ' || l_select_details;
55 END IF;
56 ELSE
57 IF l_select_details IS NULL
58 THEN
59 str := 'select ' || l_select_name;
60 ELSE
61 str := 'select ' || l_select_name || ', ' || l_select_details;
62 END IF;
63 END IF;
64 ELSE
65 IF l_select_name IS NULL
66 THEN
67 IF l_select_details IS NULL
68 THEN
69 str := 'select ' || l_select_id;
70 ELSE
71 str := 'select ' || l_select_id || ', ' || l_select_details;
72 END IF;
73 ELSE
74 IF l_select_details IS NULL
75 THEN
76 str := 'select ' || l_select_id || ', ' || l_select_name;
77 ELSE
78 str := 'select ' ||
79 l_select_id ||
80 ', ' ||
81 l_select_name ||
82 ', ' ||
83 l_select_details;
84 END IF;
85 END IF;
86 END IF;
87
88 str := str || ' from ' || l_from_table || ' ';
89
90 IF l_where_clause IS NOT NULL
91 THEN
92 str := str || 'where ' || l_where_clause || ' ';
93 END IF;
94
95 -- Added for Bug# 2557586
96 IF p_inactive_clause IS NOT NULL
97 THEN
98 IF l_where_clause IS NOT NULL
99 THEN
100 str := str || ' and ' || p_inactive_clause;
101 ELSE
102 str := str || ' where ' || p_inactive_clause;
103 END IF;
104 END IF;
105
106 IF l_order_by_clause IS NOT NULL
107 THEN
108 str := str || 'order by ' || l_order_by_clause;
109 END IF;
110 END jtf_obj_select_stmt;
111
112 PROCEDURE check_syntax (
113 p_api_version IN NUMBER,
114 p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false,
115 p_commit IN VARCHAR2 DEFAULT fnd_api.g_false,
116 p_select_id IN jtf_objects_b.select_id%TYPE DEFAULT NULL,
117 p_select_name IN jtf_objects_b.select_name%TYPE DEFAULT NULL,
118 p_select_details IN jtf_objects_b.select_details%TYPE
119 DEFAULT NULL,
120 p_from_table IN jtf_objects_b.from_table%TYPE DEFAULT NULL,
121 p_where_clause IN jtf_objects_b.where_clause%TYPE
122 DEFAULT NULL,
123 p_inactive_clause IN jtf_objects_b.inactive_clause%TYPE DEFAULT NULL, -- Added for Bug# 2557586
124 p_order_by_clause IN jtf_objects_b.order_by_clause%TYPE
125 DEFAULT NULL,
126 x_return_status OUT NOCOPY VARCHAR2,
127 x_msg_count OUT NOCOPY NUMBER,
128 x_msg_data OUT NOCOPY VARCHAR2,
129 x_sql_statement OUT NOCOPY VARCHAR2
130 )
131 IS
132 l_api_name VARCHAR2(240)
133 := 'CHECK_SYNTAX';
134 l_api_version NUMBER := 1.0;
135 l_select_id jtf_objects_b.select_id%TYPE
136 := p_select_id;
137 l_select_name jtf_objects_b.select_name%TYPE
138 := p_select_name;
139 l_select_details jtf_objects_b.select_details%TYPE
140 := p_select_details;
141 l_from_table jtf_objects_b.from_table%TYPE
142 := p_from_table;
143 l_where_clause jtf_objects_b.where_clause%TYPE
144 := p_where_clause;
145 l_order_by_clause jtf_objects_b.order_by_clause%TYPE
146 := p_order_by_clause;
147 --x_sql_statement VARCHAR2(6000);
148 str VARCHAR2(6000);
149 l_select_columns NUMBER := 0;
150 l_dummy_select_id VARCHAR2(2000);
151 l_dummy_select_name VARCHAR2(2000);
152 l_dummy_select_details VARCHAR2(2000);
153 initialized BOOLEAN := FALSE;
154 BEGIN
155 SAVEPOINT check_syntax;
156 x_return_status := fnd_api.g_ret_sts_success;
157
158 IF NOT fnd_api.compatible_api_call (
159 l_api_version,
160 p_api_version,
161 l_api_name,
162 g_pkg_name
163 )
164 THEN
165 RAISE fnd_api.g_exc_unexpected_error;
166 END IF;
167
168 IF fnd_api.to_boolean (p_init_msg_list)
169 THEN
170 fnd_msg_pub.initialize;
171 END IF;
172
173 IF l_from_table IS NULL
174 THEN
175 RETURN;
176 END IF;
177
178 IF l_select_id IS NOT NULL
179 THEN
180 x_sql_statement := ' select ' || l_select_id;
181 initialized := TRUE;
182 l_select_columns := l_select_columns + 1;
183 END IF;
184
185 IF l_select_name IS NOT NULL
186 THEN
187 IF initialized = TRUE
188 THEN
189 x_sql_statement := x_sql_statement || ', ' || l_select_name;
190 initialized := TRUE;
191 l_select_columns := l_select_columns + 1;
192 ELSE
193 x_sql_statement := ' select ' || l_select_name;
194 END IF;
195 END IF;
196
197 IF l_select_details IS NOT NULL
198 THEN
199 IF initialized = TRUE
200 THEN
201 x_sql_statement := x_sql_statement || ', ' || l_select_details;
202 initialized := TRUE;
203 l_select_columns := l_select_columns + 1;
204 ELSE
205 x_sql_statement := ' select ' || l_select_details;
206 END IF;
207 END IF;
208
209 IF initialized = FALSE
210 THEN
211 x_sql_statement := ' select 1 ';
212 END IF;
213
214 IF l_from_table IS NULL
215 THEN
216 RETURN;
217 ELSE
218 x_sql_statement := x_sql_statement || ' from ' || l_from_table;
219 END IF;
220
221 IF l_where_clause IS NOT NULL
222 THEN
223 x_sql_statement := x_sql_statement || ' where ' || l_where_clause;
224 END IF;
225
226 -- Added for Bug# 2557586
227 IF p_inactive_clause IS NOT NULL
228 THEN
229 IF l_where_clause IS NOT NULL
230 THEN
231 x_sql_statement := x_sql_statement || ' and ' || p_inactive_clause;
232 ELSE
233 x_sql_statement := x_sql_statement || ' where ' || p_inactive_clause;
234 END IF;
235 END IF;
236
237 IF l_order_by_clause IS NOT NULL
238 THEN
239 x_sql_statement :=
240 x_sql_statement || ' order by ' || l_order_by_clause;
241 END IF;
242
243 BEGIN
244 IF l_select_columns = 1
245 THEN
246 EXECUTE IMMEDIATE x_sql_statement
247 INTO l_dummy_select_id;
248 END IF;
249
250 IF l_select_columns = 2
251 THEN
252 EXECUTE IMMEDIATE x_sql_statement
253 INTO l_dummy_select_id, l_dummy_select_name;
254 END IF;
255
256 IF l_select_columns = 3
257 THEN
258 EXECUTE IMMEDIATE x_sql_statement
259 INTO l_dummy_select_id, l_dummy_select_name, l_dummy_select_details;
260 END IF;
261
262 IF fnd_api.to_boolean (p_commit)
263 THEN
264 COMMIT WORK;
265 END IF;
266
267 fnd_msg_pub.count_and_get (
268 p_count => x_msg_count,
269 p_data => x_msg_data
270 );
271 EXCEPTION
272 WHEN TOO_MANY_ROWS
273 THEN
274 NULL;
275 WHEN NO_DATA_FOUND
276 THEN
277 NULL;
278 WHEN OTHERS
279 THEN
280 ROLLBACK TO check_syntax;
281 x_return_status := fnd_api.g_ret_sts_unexp_error;
282 fnd_message.set_name ('JTF', 'JTF_OBJECTS_SYNTAX_ERROR');
283 fnd_message.set_token ('P_MESSAGE_TEXT', SQLERRM);
284 fnd_msg_pub.add;
285 fnd_msg_pub.count_and_get (
286 p_count => x_msg_count,
287 p_data => x_msg_data
288 );
289 END;
290 END;
291
292 PROCEDURE initialize_cache
293 IS
294
295 BEGIN
296 G_OBJECT_PG_TBL.DELETE;
297 END initialize_cache;
298
299
300 PROCEDURE get_drilldown_page (
301 p_input_rec IN PG_INPUT_REC,
302 x_pg_function OUT NOCOPY VARCHAR2,
303 x_pg_parameters OUT NOCOPY VARCHAR2
304 ) IS
305
306 CURSOR C_OBJ_PAGE
307 (
308 b_object_code VARCHAR2,
309 b_default_object_code VARCHAR2,
310 b_application_id NUMBER
311 ) IS
312 SELECT 1 AS OBJ_ROW_TYPE
313 , JOPD.PG_REGION_PATH
314 , JOPP.DEST_PARAM
315 , JOPP.SOURCE_PARAM
316 FROM JTF_OBJECTS_B JOB
317 , JTF_OBJECT_PG_DTLS JOPD
318 , JTF_OBJECT_PG_PARAMS JOPP
319 WHERE JOB.OBJECT_CODE = b_object_code
320 AND JOPD.OBJECT_CODE = JOB.OBJECT_CODE
321 AND JOPD.APPLICATION_ID = b_application_id
322 AND JOPD.PAGE_TYPE = 'OA_PAGE'
323 AND JOPP.OBJECT_DTLS_ID = JOPD.OBJECT_DTLS_ID
324 UNION ALL
325 SELECT 2 AS OBJ_ROW_TYPE
326 , JOPD.PG_REGION_PATH
327 , JOPP.DEST_PARAM
328 , JOPP.SOURCE_PARAM
329 FROM JTF_OBJECTS_B JOB
330 , JTF_OBJECT_PG_DTLS JOPD
331 , JTF_OBJECT_PG_PARAMS JOPP
332 WHERE JOB.OBJECT_CODE = b_default_object_code
333 AND JOPD.OBJECT_CODE = JOB.OBJECT_CODE
334 AND JOPD.APPLICATION_ID = JOB.APPLICATION_ID
335 AND JOPD.PAGE_TYPE = 'OA_PAGE'
336 AND JOPP.OBJECT_DTLS_ID = JOPD.OBJECT_DTLS_ID;
337
338 i BINARY_INTEGER;
339 l_object_code VARCHAR2(30);
340 l_default_object_code VARCHAR2(30);
341 l_use_default BOOLEAN;
342
343 BEGIN
344
345 x_pg_function := NULL;
346 x_pg_parameters := NULL;
347 IF ((p_input_rec.ENTITY IS NULL) OR (p_input_rec.OBJECT_CODE IS NULL))
348 THEN
349 RETURN;
350 END IF;
351
352 l_object_code := p_input_rec.OBJECT_CODE;
353 l_default_object_code := p_input_rec.ENTITY;
354 -- For the following two cases the object code could be other than the default
355 -- ones, so reset the object code.
356 IF (p_input_rec.ENTITY = 'TASK')
357 THEN
358 l_object_code := 'TASK';
359 l_default_object_code := 'TASK';
360 ELSIF (p_input_rec.ENTITY = 'APPOINTMENT')
361 THEN
362 l_object_code := 'APPOINTMENT';
363 l_default_object_code := 'APPOINTMENT';
364 END IF;
365
366 IF (G_OBJECT_PG_TBL.COUNT > 0)
367 THEN
368 FOR i IN G_OBJECT_PG_TBL.FIRST..G_OBJECT_PG_TBL.LAST
369 LOOP
370 IF ((l_object_code = G_OBJECT_PG_TBL(i).OBJECT_CODE) AND
371 (FND_GLOBAL.RESP_APPL_ID = G_OBJECT_PG_TBL(i).APPLICATION_ID))
372 THEN
373 x_pg_function := G_OBJECT_PG_TBL(i).PG_FUNCTION;
374 x_pg_parameters := G_OBJECT_PG_TBL(i).PG_PARAMS;
375 RETURN;
376 END IF;
377 END LOOP;
378 END IF;
379
380 -- Nothing so far, so fetch and load
381 i := G_OBJECT_PG_TBL.COUNT + 1;
382 G_OBJECT_PG_TBL(i).OBJECT_CODE := l_object_code;
383 G_OBJECT_PG_TBL(i).APPLICATION_ID := FND_GLOBAL.RESP_APPL_ID;
384 G_OBJECT_PG_TBL(i).PG_FUNCTION := NULL;
385 G_OBJECT_PG_TBL(i).PG_PARAMS := NULL;
386
387 l_use_default := TRUE;
388
389 FOR ref_obj_pg IN C_OBJ_PAGE (l_object_code,l_default_object_code,G_OBJECT_PG_TBL(i).APPLICATION_ID)
390 LOOP
391 IF (l_use_default AND (ref_obj_pg.OBJ_ROW_TYPE = 1))
392 THEN
393 l_use_default := FALSE;
394 END IF;
395 IF ((NOT l_use_default) AND (ref_obj_pg.OBJ_ROW_TYPE = 2))
396 THEN
397 EXIT;
398 END IF;
399 G_OBJECT_PG_TBL(i).PG_FUNCTION := ref_obj_pg.PG_REGION_PATH;
400 IF (G_OBJECT_PG_TBL(i).PG_PARAMS IS NOT NULL)
401 THEN
402 G_OBJECT_PG_TBL(i).PG_PARAMS := G_OBJECT_PG_TBL(i).PG_PARAMS || '&' || ref_obj_pg.DEST_PARAM;
403 ELSE
404 G_OBJECT_PG_TBL(i).PG_PARAMS := ref_obj_pg.DEST_PARAM;
405 END IF;
406 -- now set the value of the parameter
407 IF ((ref_obj_pg.SOURCE_PARAM = 'TaskId') AND (p_input_rec.TASK_ID IS NOT NULL))
408 THEN
409 G_OBJECT_PG_TBL(i).PG_PARAMS := G_OBJECT_PG_TBL(i).PG_PARAMS || '=' || p_input_rec.TASK_ID;
410 ELSIF ((ref_obj_pg.SOURCE_PARAM = 'SourceObjectId') AND (p_input_rec.SOURCE_OBJECT_ID IS NOT NULL))
411 THEN
412 G_OBJECT_PG_TBL(i).PG_PARAMS := G_OBJECT_PG_TBL(i).PG_PARAMS || '=' || p_input_rec.SOURCE_OBJECT_ID;
413 ELSIF ((ref_obj_pg.SOURCE_PARAM = 'TaskAssignmentId') AND (p_input_rec.TASK_ASSIGNMENT_ID IS NOT NULL))
414 THEN
415 G_OBJECT_PG_TBL(i).PG_PARAMS := G_OBJECT_PG_TBL(i).PG_PARAMS || '=' || p_input_rec.TASK_ASSIGNMENT_ID;
416 ELSIF ((ref_obj_pg.SOURCE_PARAM = 'CalItemId') AND (p_input_rec.CAL_ITEM_ID IS NOT NULL))
417 THEN
418 G_OBJECT_PG_TBL(i).PG_PARAMS := G_OBJECT_PG_TBL(i).PG_PARAMS || '=' || p_input_rec.CAL_ITEM_ID;
419 ELSIF ((ref_obj_pg.SOURCE_PARAM = 'ScheduleId') AND (p_input_rec.SCHEDULE_ID IS NOT NULL))
420 THEN
421 G_OBJECT_PG_TBL(i).PG_PARAMS := G_OBJECT_PG_TBL(i).PG_PARAMS || '=' || p_input_rec.SCHEDULE_ID;
422 ELSIF ((ref_obj_pg.SOURCE_PARAM = 'HRCalEventId') AND (p_input_rec.HR_CAL_EVENT_ID IS NOT NULL))
423 THEN
424 G_OBJECT_PG_TBL(i).PG_PARAMS := G_OBJECT_PG_TBL(i).PG_PARAMS || '=' || p_input_rec.HR_CAL_EVENT_ID;
425 ELSIF ((SUBSTR(ref_obj_pg.SOURCE_PARAM,1,1) = '''') AND (SUBSTR(ref_obj_pg.SOURCE_PARAM,LENGTH(ref_obj_pg.SOURCE_PARAM),1) = ''''))
426 THEN
427 G_OBJECT_PG_TBL(i).PG_PARAMS := G_OBJECT_PG_TBL(i).PG_PARAMS || '=' || SUBSTR(ref_obj_pg.SOURCE_PARAM,2,LENGTH(ref_obj_pg.SOURCE_PARAM)-2);
428 END IF;
429 END LOOP;
430
431 x_pg_function := G_OBJECT_PG_TBL(i).PG_FUNCTION;
432 x_pg_parameters := G_OBJECT_PG_TBL(i).PG_PARAMS;
433
434 END get_drilldown_page;
435
436 END jtf_objects_pub;