1 PACKAGE BODY JTF_LOG_TO_PHY_PVT AS
2 /* $Header: JTFVDPOB.pls 120.2 2005/10/25 05:06:45 psanyal ship $ */
3 G_PKG_NAME CONSTANT VARCHAR2(21):= 'JTF_LOG_TO_PHY_PVT';
4 G_FILE_NAME CONSTANT VARCHAR2(12):= 'JTFVDPOB.pls';
5 -- ****************************************************************************
6 -- get physical object given logical id, site id and language code
7 -- ****************************************************************************
8 PROCEDURE get_obj_by_id(
9 p_api_version IN NUMBER,
10 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
11 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
12 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
13 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
14 p_logicalid IN jtf_dsp_obj_lgl_ctnt.item_id%TYPE,
15 p_siteid IN jtf_dsp_lgl_phys_map.msite_id%TYPE,
16 p_langcode IN jtf_dsp_lgl_phys_map.language_code%TYPE,
17 x_filename OUT NOCOPY /* file.sql.39 change */ jtf_amv_attachments.file_name%TYPE,
18 x_description OUT NOCOPY /* file.sql.39 change */ jtf_amv_items_vl.description%TYPE,
19
20 -- added by Guigen Zhang 04-04-2001 16:46
21 x_fileid OUT NOCOPY /* file.sql.39 change */ jtf_amv_attachments.file_id%TYPE) IS
22
23 l_filename jtf_amv_attachments.file_name%TYPE;
24 l_description jtf_amv_items_vl.description%TYPE := 'description';
25
26 -- added by Guigen Zhang 04-04-2001 16:46
27 l_fileid jtf_amv_attachments.file_id%TYPE;
28
29 l_logid jtf_dsp_obj_lgl_ctnt.item_id%TYPE;
30 l_api_name CONSTANT varchar2(30) := 'get_obj_by_id';
31 l_phymed_found boolean := false;
32 BEGIN
33 ---dbms_output.put_line('making api version call');
34 IF NOT FND_API.compatible_api_call(
35 g_api_version,
36 p_api_version,
37 l_api_name,
38 g_pkg_name
39 ) THEN
40 RAISE FND_API.g_exc_unexpected_error;
41 END IF;
42
43 -- Initialize message list if p_init_msg_list is set to TRUE.
44 ---dbms_output.put_line(' -- Initialize message list if p_init_msg_list is set to TRUE.');
45 IF FND_API.to_Boolean(p_init_msg_list) THEN
46 FND_MSG_PUB.initialize;
47 END IF;
48
49 -- Initialize API return status to error, i.e, its not duplicate
50 x_return_status := FND_API.g_ret_sts_success;
51
52 l_logid := p_logicalid;
53
54 BEGIN
55 -- logical media has been found, get the physical media
56 -- first try at the site and language
57 ---dbms_output.put_line(' logical media logical id is ' || l_logid);
58 ---dbms_output.put_line(' try physical at the site and language');
59
60 -- modified by Guigen Zhang 04-04-2001 16:46
61 select a.file_name, b.description, a.file_id
62 into l_filename, l_description, l_fileid
63 from
64 jtf_amv_attachments a,
65 jtf_amv_items_vl b,
66 jtf_dsp_lgl_phys_map c
67 where
68 c.item_id = l_logid and
69 c.msite_id = p_siteid and
70 c.language_code = p_langcode and
71 c.item_id = b.item_id and
72 c.attachment_id = a.attachment_id and
73 a.application_id = 671 and
74 b.application_id = 671;
75 l_phymed_found := true;
76 EXCEPTION
77 WHEN NO_DATA_FOUND THEN
78 NULL;
79 WHEN OTHERS THEN
80 -- TODO put fnd_messages
81 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
82 END;
83
84 if NOT l_phymed_found THEN
85 BEGIN
86 -- try at the ALL site and this language
87 ---dbms_output.put_line(' try at the ALL site and this language');
88
89 -- modified by Guigen Zhang 04-04-2001 16:46
90 select a.file_name, b.description, a.file_id
91 into l_filename, l_description, l_fileid
92 from
93 jtf_amv_attachments a,
94 jtf_amv_items_vl b,
95 jtf_dsp_lgl_phys_map c
96 where
97 c.item_id = l_logid and
98 c.default_site = 'Y' and
99 c.language_code = p_langcode and
100 c.default_language = 'N' and
101 c.attachment_id = a.attachment_id and
102 c.item_id = b.item_id and
103 a.application_id = 671 and
104 b.application_id = 671;
105 l_phymed_found := TRUE;
106 EXCEPTION
107 WHEN NO_DATA_FOUND THEN
108 NULL;
109 WHEN OTHERS THEN
110 -- TODO put fnd_messages
111 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
112 END;
113
114 if NOT l_phymed_found THEN
115 BEGIN
116 -- try at this site and ALL language
117 ---dbms_output.put_line('try at this site and ALL language');
118
119 -- modified by Guigen Zhang 04-04-2001 16:46
120 select a.file_name, b.description, a.file_id
121 into l_filename, l_description, l_fileid
122 from
123 jtf_amv_attachments a,
124 jtf_amv_items_vl b,
125 jtf_dsp_lgl_phys_map c
126 where
127 c.item_id = l_logid and
128 c.msite_id = p_siteid and
129 c.default_site = 'N' and
130 c.default_language = 'Y' and
131 c.attachment_id = a.attachment_id and
132 c.item_id = b.item_id and
133 a.application_id = 671 and
134 b.application_id = 671;
135 l_phymed_found := TRUE;
136 EXCEPTION
137 WHEN NO_DATA_FOUND THEN
138 NULL;
139 WHEN OTHERS THEN
140 -- TODO put fnd_messages
141 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
142 END;
143
144 if NOT l_phymed_found THEN
145 BEGIN
146 -- try at ALL sites and ALL langauge
147 ---dbms_output.put_line('try at ALL sites and ALL langauge');
148 -- modified by Guigen Zhang 04-04-2001 16:46
149 select a.file_name, b.description, a.file_id
150 into l_filename, l_description, l_fileid
151 from
152 jtf_amv_attachments a,
153 jtf_amv_items_vl b,
154 jtf_dsp_lgl_phys_map c
155 where
156 c.item_id = l_logid and
157 c.default_site = 'Y' and
158 c.default_language = 'Y' and
159 c.attachment_id = a.attachment_id and
160 c.item_id = b.item_id and
161 a.application_id = 671 and
162 b.application_id = 671;
163 -- dbms_output.put_line('all combos tried');
164 -- dbms_output.put_line(l_filename);
165 -- dbms_output.put_line(l_description);
166 EXCEPTION
167 WHEN NO_DATA_FOUND THEN
168 x_filename := NULL;
169 x_description := NULL;
170
171 -- added by Guigen Zhang 04-04-2001 16:46
172 x_fileid := NULL;
173
174 RETURN;
175 WHEN OTHERS THEN
176 -- TODO put fnd_messages
177 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
178 END;
179 END IF;
180 END IF;
181 END IF;
182
183 ---dbms_output.put_line('done both log and phy');
184 -- return the filename and description
185 x_filename := l_filename;
186 x_description := l_description;
187
188 -- modified by Guigen Zhang 04-04-2001 16:46
189 x_fileid := l_fileid;
190
191 ---dbms_output.put_line('just before returning');
192 -- RETURN;
193 EXCEPTION
194 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
195 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
196 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
197 p_data => x_msg_data);
198
199 WHEN OTHERS THEN
200 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
201
202 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
203 THEN
204 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
205 END IF;
206
207 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
208 p_data => x_msg_data);
209
210 END get_obj_by_id;
211
212 -- ****************************************************************************
213 -- get physical object given access name, site id and language code
214 -- ****************************************************************************
215 PROCEDURE GET_OBJ_BY_NAME(
216 p_api_version IN NUMBER,
217 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
218 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
219 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
220 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
221 p_access_name IN jtf_amv_items_vl.access_name%TYPE,
222 p_siteid IN jtf_dsp_lgl_phys_map.msite_id%TYPE,
223 p_langcode IN jtf_dsp_lgl_phys_map.language_code%TYPE,
224 x_filename OUT NOCOPY /* file.sql.39 change */ jtf_amv_attachments.file_name%TYPE,
225 x_description OUT NOCOPY /* file.sql.39 change */ jtf_amv_items_vl.description%TYPE,
226
227 -- added by Guigen Zhang 04-04-2001 16:46
228 x_fileid OUT NOCOPY /* file.sql.39 change */ jtf_amv_attachments.file_id%TYPE) IS
229
230 l_filename jtf_amv_attachments.file_name%TYPE;
231 l_description jtf_amv_items_vl.description%TYPE := 'description';
232
233 -- added by Guigen Zhang 04-04-2001 16:46
234 l_fileid jtf_amv_attachments.file_id%TYPE;
235
236 l_accessname jtf_amv_items_vl.access_name%TYPE;
237 l_api_name CONSTANT varchar2(30) := 'get_obj_by_name';
238 l_phymed_found boolean := false;
239 BEGIN
240 ---dbms_output.put_line('making api version call');
241 IF NOT FND_API.compatible_api_call(
242 g_api_version,
243 p_api_version,
244 l_api_name,
245 g_pkg_name
246 ) THEN
247 RAISE FND_API.g_exc_unexpected_error;
248 END IF;
249
250 -- Initialize message list if p_init_msg_list is set to TRUE.
251 ---dbms_output.put_line(' -- Initialize message list if p_init_msg_list is set to TRUE.');
252 IF FND_API.to_Boolean(p_init_msg_list) THEN
253 FND_MSG_PUB.initialize;
254 END IF;
255
256 -- Initialize API return status to error, i.e, its not duplicate
257 x_return_status := FND_API.g_ret_sts_success;
258
259 l_accessname := p_access_name;
260
261 BEGIN
262 -- logical media has been found, get the physical media
263 -- first try at the site and language
264 ---dbms_output.put_line(' logical media logical id is ' || l_accessname);
265 ---dbms_output.put_line(' try physical at the site and language');
266
267 -- modified by Guigen Zhang 04-04-2001 16:46
268 select a.file_name, b.description, a.file_id
269 into l_filename, l_description, l_fileid
270
271 from
272 jtf_amv_attachments a,
273 jtf_amv_items_vl b,
274 jtf_dsp_lgl_phys_map c
275 where
276 b.access_name = l_accessname and
277 c.msite_id = p_siteid and
278 c.language_code = p_langcode and
279 c.item_id = b.item_id and
280 c.attachment_id = a.attachment_id and
281 a.application_id = 671 and
282 b.application_id = 671;
283 l_phymed_found := true;
284 EXCEPTION
285 WHEN NO_DATA_FOUND THEN
286 NULL;
287 WHEN OTHERS THEN
288 -- TODO put fnd_messages
289 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
290 END;
291
292 if NOT l_phymed_found THEN
293 BEGIN
294 -- try at the ALL site and this language
295 ---dbms_output.put_line(' try at the ALL site and this language');
296
297 -- modified by Guigen Zhang 04-04-2001 16:46
298 select a.file_name, b.description, a.file_id
299 into l_filename, l_description, l_fileid
300
301 from
302 jtf_amv_attachments a,
303 jtf_amv_items_vl b,
304 jtf_dsp_lgl_phys_map c
305 where
306 b.access_name = l_accessname and
307 c.default_site = 'Y' and
308 c.language_code = p_langcode and
309 c.default_language = 'N' and
310 c.attachment_id = a.attachment_id and
311 c.item_id = b.item_id and
312 a.application_id = 671 and
313 b.application_id = 671;
314 l_phymed_found := TRUE;
315 EXCEPTION
316 WHEN NO_DATA_FOUND THEN
317 NULL;
318 WHEN OTHERS THEN
319 -- TODO put fnd_messages
320 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
321 END;
322 if NOT l_phymed_found THEN
323 BEGIN
324 -- try at this site and ALL language
325 ---dbms_output.put_line('try at this site and ALL language');
326
327 -- modified by Guigen Zhang 04-04-2001 16:46
328 select a.file_name, b.description, a.file_id
329 into l_filename, l_description, l_fileid
330
331 from
332 jtf_amv_attachments a,
333 jtf_amv_items_vl b,
334 jtf_dsp_lgl_phys_map c
335 where
336 b.access_name = l_accessname and
337 c.msite_id = p_siteid and
338 c.default_site = 'N' and
339 c.default_language = 'Y' and
340 c.attachment_id = a.attachment_id and
341 c.item_id = b.item_id and
342 a.application_id = 671 and
343 b.application_id = 671;
344 l_phymed_found := TRUE;
345 EXCEPTION
346 WHEN NO_DATA_FOUND THEN
347 NULL;
348 WHEN OTHERS THEN
349 -- TODO put fnd_messages
350 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
351 END;
352
353 if NOT l_phymed_found THEN
354 BEGIN
355 -- try at ALL sites and ALL langauge
356 ---dbms_output.put_line('try at ALL sites and ALL langauge');
357
358 -- modified by Guigen Zhang 04-04-2001 16:46
359 select a.file_name, b.description, a.file_id
360 into l_filename, l_description, l_fileid
361
362 from
363 jtf_amv_attachments a,
364 jtf_amv_items_vl b,
365 jtf_dsp_lgl_phys_map c
366 where
367 b.access_name = l_accessname and
368 c.default_site = 'Y' and
369 c.default_language = 'Y' and
370 c.attachment_id = a.attachment_id and
371 c.item_id = b.item_id and
372 a.application_id = 671 and
373 b.application_id = 671;
374 -- dbms_output.put_line('all combos tried');
375 -- dbms_output.put_line(l_filename);
376 -- dbms_output.put_line(l_description);
377 EXCEPTION
378 WHEN NO_DATA_FOUND THEN
379 x_filename := NULL;
380 x_description := NULL;
381
382 -- modified by Guigen Zhang 04-04-2001 16:46
383 x_fileid := NULL;
384
385 RETURN;
386 WHEN OTHERS THEN
387 -- TODO put fnd_messages
388 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
389 END;
390 END IF;
391 END IF;
392 END IF;
393
394 ---dbms_output.put_line('done both log and phy');
395 -- return the filename and description
396 x_filename := l_filename;
397 x_description := l_description;
398
399 -- modified by Guigen Zhang 04-04-2001 16:46
400 x_fileid := l_fileid;
401
402 ---dbms_output.put_line('just before returning');
403 -- RETURN;
404 EXCEPTION
405 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
406 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
407 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
408 p_data => x_msg_data);
409
410 WHEN OTHERS THEN
411 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
412
413 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
414 THEN
415 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
416 END IF;
417
418 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
419 p_data => x_msg_data);
420
421 END GET_OBJ_BY_NAME;
422
423 END JTF_LOG_TO_PHY_PVT;
[Home] [Help]