1 PACKAGE BODY IBE_PHY_MEDIA_PVT AS
2 /* $Header: IBEVDPMB.pls 115.1 2002/12/14 07:52:56 schak ship $ */
3 G_PKG_NAME CONSTANT VARCHAR2(21):= 'IBE_PHY_MEDIA_PVT';
4 G_FILE_NAME CONSTANT VARCHAR2(12):= 'IBEVDPMB.pls';
5 -- ****************************************************************************
6 -- get media given pid, catrgory id, dc id, site id
7 -- ****************************************************************************
8 PROCEDURE get_media (
9 p_api_version IN NUMBER,
10 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
11 x_return_status OUT NOCOPY VARCHAR2,
12 x_msg_count OUT NOCOPY NUMBER,
13 x_msg_data OUT NOCOPY VARCHAR2,
14 p_pid IN ibe_dsp_obj_lgl_ctnt.object_id%TYPE,
15 p_catgid IN ibe_dsp_obj_lgl_ctnt.object_id%TYPE,
16 p_dcname IN ibe_dsp_context_b.access_name%TYPE,
17 p_siteid IN ibe_dsp_lgl_phys_map.msite_id%TYPE,
18 p_langcode IN ibe_dsp_lgl_phys_map.language_code%TYPE,
19 x_filename OUT NOCOPY jtf_amv_attachments.file_name%TYPE,
20 x_description OUT NOCOPY jtf_amv_items_vl.description%TYPE,
21
22 -- added by Guigen Zhang 04-04-2001 16:46
23 x_fileid OUT NOCOPY jtf_amv_attachments.file_id%TYPE) IS
24
25
26 l_filename jtf_amv_attachments.file_name%TYPE;
27 l_description jtf_amv_items_vl.description%TYPE := 'description';
28
29 -- modified by Guigen Zhang 04-04-2001 16:46
30 l_fileid jtf_amv_attachments.file_id%TYPE;
31
32 l_logid ibe_dsp_obj_lgl_ctnt.item_id%TYPE;
33 l_api_name CONSTANT varchar2(30) := 'get_media';
34 l_logmed_found boolean := false;
35 l_phymed_found boolean := false;
36 BEGIN
37 ---dbms_output.put_line('making api version call');
38 IF NOT FND_API.compatible_api_call(
39 g_api_version,
40 p_api_version,
41 l_api_name,
42 g_pkg_name
43 ) THEN
44 RAISE FND_API.g_exc_unexpected_error;
45 END IF;
46
47 -- Initialize message list if p_init_msg_list is set to TRUE.
48 ---dbms_output.put_line(' -- Initialize message list if p_init_msg_list is set to TRUE.');
49 IF FND_API.to_Boolean(p_init_msg_list) THEN
50 FND_MSG_PUB.initialize;
51 END IF;
52
53 -- Initialize API return status to error, i.e, its not duplicate
54 x_return_status := FND_API.g_ret_sts_success;
55
56 BEGIN
57 -- First determine the logical media
58 ---dbms_output.put_line(' try at pid and dc level');
59 -- try at pid and dc level
60
61 select a.item_id
62 into l_logid
63 from
64 ibe_dsp_obj_lgl_ctnt a,
65 ibe_dsp_context_b b
66 where
67 a.object_id = p_pid and
68 a.context_id = b.context_id and
69 b.access_name = p_dcname and
70 a.object_type = 'I' ;
71 l_logmed_found := true;
72 EXCEPTION
73 WHEN NO_DATA_FOUND THEN
74 NULL;
75 WHEN OTHERS THEN
76 -- TODO put fnd_messages
77 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
78 END;
79
80 if NOT l_logmed_found THEN
81 BEGIN
82 -- try at catg and dc level
83 ---dbms_output.put_line(' try at catg and dc level');
84 select a.item_id
85 into l_logid
86 from
87 ibe_dsp_obj_lgl_ctnt a,
88 ibe_dsp_context_b b
89 where
90 a.object_id = p_catgid and
91 a.context_id = b.context_id and
92 b.access_name = p_dcname and
93 a.object_type = 'C' ;
94 l_logmed_found := true;
95 EXCEPTION
96 WHEN NO_DATA_FOUND THEN
97 NULL;
98 WHEN OTHERS THEN
99 -- TODO put fnd_messages
100 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
101 END;
102
103 if NOT l_logmed_found THEN
104 BEGIN
105 -- try at the dc level
106 ---dbms_output.put_line(' try at the dc level');
107 select item_id
108 into l_logid
109 from
110 ibe_dsp_context_b
111 where
112 access_name = p_dcname;
113 EXCEPTION
114 WHEN NO_DATA_FOUND THEN
115 x_filename := NULL;
116 x_description := NULL;
117
118 -- modified by Guigen Zhang 04-04-2001 16:46
119 x_fileid := NULL;
120
121 RETURN;
122 WHEN OTHERS THEN
123 -- TODO put fnd_messages
124 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
125 END;
126 END IF;
127 END IF;
128
129
130 BEGIN
131 -- logical media has been found, get the physical media
132 -- first try at the site and language
133 ---dbms_output.put_line(' logical media logical id is ' || l_logid);
134 ---dbms_output.put_line(' logical media has been found, try physical at the site and language');
135
136 -- modified by Guigen Zhang 04-04-2001 16:46
137 select a.file_name, b.description, a.file_id
138 into l_filename, l_description, l_fileid
139
140 from
141 jtf_amv_attachments a,
142 jtf_amv_items_vl b,
143 ibe_dsp_lgl_phys_map c
144 where
145 c.item_id = l_logid and
146 c.msite_id = p_siteid and
147 c.language_code = p_langcode and
148 c.item_id = b.item_id and
149 c.attachment_id = a.attachment_id and
150 a.application_id = 671 and
151 b.application_id = 671;
152 l_phymed_found := true;
153 EXCEPTION
154 WHEN NO_DATA_FOUND THEN
155 NULL;
156 WHEN OTHERS THEN
157 -- TODO put fnd_messages
158 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
159 END;
160
161 if NOT l_phymed_found THEN
162 BEGIN
163 -- try at the ALL site and this language
164 ---dbms_output.put_line(' try at the ALL site and this language');
165
166 -- modified by Guigen Zhang 04-04-2001 16:46
167 select a.file_name, b.description, a.file_id
168 into l_filename, l_description, l_fileid
169
170 from
171 jtf_amv_attachments a,
172 jtf_amv_items_vl b,
173 ibe_dsp_lgl_phys_map c
174 where
175 c.item_id = l_logid and
176 c.default_site = 'Y' and
177 c.language_code = p_langcode and
178 c.default_language = 'N' and
179 c.attachment_id = a.attachment_id and
180 c.item_id = b.item_id and
181 a.application_id = 671 and
182 b.application_id = 671;
183 l_phymed_found := TRUE;
184 EXCEPTION
185 WHEN NO_DATA_FOUND THEN
186 NULL;
187 WHEN OTHERS THEN
188 -- TODO put fnd_messages
189 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
190 END;
191
192 if NOT l_phymed_found THEN
193 BEGIN
194 -- try at this site and ALL language
195 ---dbms_output.put_line('try at this site and ALL language');
196
197 -- modified by Guigen Zhang 04-04-2001 16:46
198 select a.file_name, b.description, a.file_id
199 into l_filename, l_description, l_fileid
200
201 from
202 jtf_amv_attachments a,
203 jtf_amv_items_vl b,
204 ibe_dsp_lgl_phys_map c
205 where
206 c.item_id = l_logid and
207 c.msite_id = p_siteid and
208 c.default_site = 'N' and
209 c.default_language = 'Y' and
210 c.attachment_id = a.attachment_id and
211 c.item_id = b.item_id and
212 a.application_id = 671 and
213 b.application_id = 671;
214 l_phymed_found := TRUE;
215 EXCEPTION
216 WHEN NO_DATA_FOUND THEN
217 NULL;
218 WHEN OTHERS THEN
219 -- TODO put fnd_messages
220 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
221 END;
222
223 if NOT l_phymed_found THEN
224 BEGIN
225 -- try at ALL sites and ALL langauge
226 ---dbms_output.put_line('try at ALL sites and ALL langauge');
227
228 -- modified by Guigen Zhang 04-04-2001 16:46
229 select a.file_name, b.description, a.file_id
230 into l_filename, l_description, l_fileid
231
232 from
233 jtf_amv_attachments a,
234 jtf_amv_items_vl b,
235 ibe_dsp_lgl_phys_map c
236 where
237 c.item_id = l_logid and
238 c.default_site = 'Y' and
239 c.default_language = 'Y' and
240 c.attachment_id = a.attachment_id and
241 c.item_id = b.item_id and
242 a.application_id = 671 and
243 b.application_id = 671;
244 -- dbms_output.put_line('all combos tried');
245 -- dbms_output.put_line(l_filename);
246 -- dbms_output.put_line(l_description);
247 EXCEPTION
248 WHEN NO_DATA_FOUND THEN
249 x_filename := NULL;
250 x_description := NULL;
251
252 -- modified by Guigen Zhang 04-04-2001 16:46
253 x_fileid := NULL;
254
255 RETURN;
256 WHEN OTHERS THEN
257 -- TODO put fnd_messages
258 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
259 END;
260 END IF;
261 END IF;
262 END IF;
263
264 ---dbms_output.put_line('done both log and phy');
265 -- return the filename and description
266 x_filename := l_filename;
267 x_description := l_description;
268
269 -- modified by Guigen Zhang 04-04-2001 16:46
270 x_fileid := l_fileid;
271
272 ---dbms_output.put_line('just before returning');
273 -- RETURN;
274 EXCEPTION
275 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
276 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
277 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
278 p_data => x_msg_data);
279
280 WHEN OTHERS THEN
281 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
282
283 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
284 THEN
285 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
286 END IF;
287
288 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
289 p_data => x_msg_data);
290
291 END get_media;
292
293 END IBE_PHY_MEDIA_PVT ;
[Home] [Help]