1 PACKAGE BODY IBE_DELIVERABLE_EXPIMP_PVT AS
2 /* $Header: IBEVDEIB.pls 120.1 2007/10/17 10:33:42 scnagara ship $ */
3 /*======================================================================+
4 | Copyright (c) 2001 Oracle Corporation, Redwood Shores, CA, USA |
5 | All rights reserved. |
6 +=======================================================================+
7 | FILENAME |
8 | IBEVDEIB.pls |
9 | |
10 | DESCRIPTION |
11 | procedures for export import of deliverables |
12 | |
13 | HISTORY |
14 | 08/26/2003 ABHANDAR Created |
15 | 03/23/2005 RGUPTA Added p_enable_debug param |
16 | 17/10/2007 SCNAGARA Fix for Bug#5882497 |
17 +=======================================================================*/
18 ----added 07/29/03 by abhandar for template export and import----
19 --******************************************************************************
20 -- Procedure to import templates
21 -- Seeded templates and it's mappings are not modifiable during import.
22 -- The non seeded ones can be modified during import
23 -- If the site code is null, the mappings are not imported( error logged in the error log)
24 --
25 -- User may modify the following fields in the input xml file -->
26 --
27 --1) ProgrammaticAccessName(jtf_amv_items_b.access_name) --> Create a new template with this acces name
28 --2) Name(jtf_amv_items_tl.item_name)--> Update the existing template Name value to reflect the new value
29 --3) Description(jtf_amv_items_tl.description)-->Update the existing template Description value to reflect the new value
30 --4) Keywords(jtf_amv_items_keywords.keywords)-->pdate the existing template Keywords value to reflect the new value
31 --5) Seed flag--> No effect field is only for informational purpose
32 --6) Applicable_to(jtf_amv_items_b.applicable_to)-->Update the existing template 'applicable to' value to reflect the new value
33 --7) Seed map--> No effect, field is only for informational purpose
34 --8) Site code--> Create a new mapping for the template with this site id
35 --9) Site name--> No effect, field is only for informational purpose
36 --10)Language--> Create a new mapping for the template with this lang code
37 --11)File name--> Update the filename in the jtf_amv_attachments table
38 --12)Def site--> Create a new mapping for the template with this flag
39 --13)Def lang--> Create a new mapping for the template with this flag
40 --*******************************************************************************
41
42 PROCEDURE save_template_mapping(
43 p_api_version IN NUMBER,
44 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
45 p_commit IN VARCHAR2 := FND_API.g_false,
46 x_return_status OUT NOCOPY VARCHAR2,
47 x_msg_count OUT NOCOPY VARCHAR2,
48 x_msg_data OUT NOCOPY VARCHAR2,
49 x_error_num IN OUT NOCOPY NUMBER,
50 p_error_limit IN NUMBER,
51 p_access_name IN VARCHAR2,
52 p_item_name IN VARCHAR2,
53 p_description IN VARCHAR2,
54 p_applicable_to IN VARCHAR2,
55 p_keywords IN VARCHAR2,
56 p_minisite_ids IN JTF_NUMBER_TABLE,
57 p_language_codes IN JTF_VARCHAR2_TABLE_100,
58 p_default_sites IN JTF_VARCHAR2_TABLE_100,
59 p_default_languages IN JTF_VARCHAR2_TABLE_100,
60 p_file_names IN JTF_VARCHAR2_TABLE_100,
61 p_enable_debug IN VARCHAR2)
62 IS
63 L_API_NAME CONSTANT VARCHAR2(30) := 'save_template_mapping';
64 L_API_VERSION CONSTANT NUMBER := 1.0;
65
66 l_appl_id NUMBER := 671;
67 l_attachment_used_by VARCHAR2(30):='ITEM';
68
69 l_return_status VARCHAR2(1);
70 l_msg_count NUMBER;
71 l_msg_data VARCHAR2(2000);
72 l_true VARCHAR2(1);
73
74 l_mode VARCHAR2(20) := 'EXECUTION';
75 l_status VARCHAR2(4);
76 l_debugMsgBuf VARCHAR(2000);
77 l_next_val NUMBER;
78
79 CURSOR c_lgl_phys_map_seq IS
80 SELECT IBE_DSP_LGL_PHYS_MAP_S1.NEXTVAL
81 FROM DUAL;
82
83 CURSOR c_get_template_csr(c_access_name VARCHAR2) IS
84 SELECT item_id,object_version_number
85 FROM jtf_amv_items_b
86 WHERE access_name = c_access_name
87 AND application_id = 671
88 AND deliverable_type_code = 'TEMPLATE';
89
90 -- cursor to get the map id and attachment id.
91 CURSOR c_get_map_csr(c_item_id VARCHAR2,
92 c_site_id NUMBER,
93 c_lang_code VARCHAR2,
94 c_default_site VARCHAR2,
95 c_default_lang VARCHAR2) IS
96 SELECT lgl_phys_map_id, attachment_id
97 FROM ibe_dsp_lgl_phys_map
98 WHERE item_id = c_item_id
99 AND msite_id = c_site_id
100 AND language_code = c_lang_code
101 AND default_site = c_default_site
102 AND default_language = c_default_lang;
103
104 -- cursor to load the attachment info
105 CURSOR c_get_attachment_csr(c_attachment_id NUMBER) IS
106 SELECT
107 attachment_id, -- Bug#5882497, scnagara
108 object_version_number,
109 attachment_used_by,
110 enabled_flag,
111 can_fulfill_electronic_flag,
112 file_id,
113 file_extension,
114 keywords,
115 send_for_preview_flag,
116 attachment_type,
117 language_code,
118 application_id,
119 description
120 FROM JTF_AMV_ATTACHMENTS
121 WHERE attachment_id=c_attachment_id;
122
123 -- Bug5882497
124 CURSOR c_get_attachment_id_csr(c_item_id ibe_dsp_lgl_phys_map.item_id%type,
125 c_file_name jtf_amv_attachments.file_name%type) IS
126 SELECT
127 ibemap.attachment_id
128 FROM
129 jtf_amv_attachments jtfach,ibe_dsp_lgl_phys_map ibemap
130 WHERE
131 ibemap.attachment_id = jtfach.attachment_id
132 and ibemap.item_id = c_item_id
133 and jtfach.file_name = c_file_name
134 group by ibemap.attachment_id;
135
136 l_object_ver_num NUMBER;
137 l_item_id NUMBER;
138 l_lgl_phys_map_id NUMBER;
139 l_attachment_id NUMBER;
140 l_deliverable_rec IBE_DELIVERABLE_GRP.DELIVERABLE_REC_TYPE;
141 l_attachment_rec IBE_ATTACHMENT_GRP.ATTACHMENT_REC_TYPE;
142
143 BEGIN
144 l_true:=FND_API.g_true;
145
146 -- begin log message initialization
147
148 FND_GLOBAL.APPS_INITIALIZE(5,20420,1);
149 FND_PROFILE.Put('AFLOG_ENABLED', 'Y');
150 FND_PROFILE.Put('AFLOG_LEVEL',FND_LOG.LEVEL_EVENT);
151 FND_LOG_REPOSITORY.Init;
152
153 IF (p_enable_debug = 'Y') THEN
154 FND_FILE.PUT_LINE(FND_FILE.LOG, '*****Template import Starts-'||to_char(sysdate,'MM/DD/RRRR HH24:MI:SS'));
155 END IF;
156
157 -- Standard Start of API savepoint
158 SAVEPOINT save_template_mapping_pvt;
159 -- Standard call to check for call compatibility.
160 IF NOT FND_API.Compatible_API_Call(l_api_version,
161 p_api_version,
162 l_api_name,
163 g_pkg_name) THEN
164 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
165 END IF;
166 -- Initialize message list if p_init_msg_list is set to TRUE.
167 IF FND_API.To_Boolean(p_init_msg_list) THEN
168 FND_Msg_Pub.initialize;
169 END IF;
170 -- Initialize API return status to success
171 x_return_status := FND_API.G_RET_STS_SUCCESS;
172 -- API body
173 -- Check if the template exists or not
174 IF (p_enable_debug = 'Y') THEN
175 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Opening cursor c_get_template_csr');
176 END IF;
177 OPEN c_get_template_csr(p_access_name);
178 FETCH c_get_template_csr INTO l_item_id ,l_object_ver_num;
179 IF (c_get_template_csr%NOTFOUND) THEN
180 IF (p_enable_debug = 'Y') THEN
181 FND_FILE.PUT_LINE(FND_FILE.LOG, 'l_item_id is null');
182 END IF;
183 l_item_id := NULL;
184 END IF;
185 CLOSE c_get_template_csr;
186 l_deliverable_rec.item_type :='TEMPLATE';
187 l_deliverable_rec.access_name := p_access_name;
188 l_deliverable_rec.display_name := p_item_name;
189 l_deliverable_rec.description := p_description;
190 l_deliverable_rec.item_applicable_to := p_applicable_to;
191 l_deliverable_rec.keywords := p_keywords;
192 l_deliverable_rec.deliverable_id := l_item_id;
193 l_deliverable_rec.object_version_number:=l_object_ver_num;
194
195 IF (p_enable_debug = 'Y') THEN
196 FND_FILE.PUT_LINE(FND_FILE.LOG,
197 'Step 1 : item_id='||l_item_id||
198 ':access_name='||p_access_name||
199 ':display_name='||p_item_name||
200 ':description='||p_description||
201 ':applicable_to='||p_applicable_to||
202 'p_keywords='||p_keywords||
203 ':object ver num='||l_object_ver_num);
204 END IF;
205
206 -- Check if the existing template is seed or not. Seed templates are not modifiable
207 -- For non-seed template, create a new template / update the existing template.
208
209 IF (l_item_id IS NULL) OR (l_item_id >=10000) THEN
210
211 IF (p_enable_debug = 'Y') THEN
212 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Before calling IBE_Deliverable_Grp.save_deliverable');
213 END IF;
214
215 IBE_Deliverable_GRP.save_deliverable(
216 p_api_version => 1.0,
217 p_init_msg_list => FND_API.g_false,
218 p_commit => FND_API.g_false,
219 x_return_status => l_return_status,
220 x_msg_count => l_msg_count,
221 x_msg_data => l_msg_data,
222 p_deliverable_rec => l_deliverable_rec);
223
224 IF (p_enable_debug = 'Y') THEN
225 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Step 2 :After item save :return status='||l_return_status);
226 END IF;
227 l_item_id := l_deliverable_rec.deliverable_id;
228 IF (p_enable_debug = 'Y') THEN
229 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Step 2a :l_item_id='||l_item_id);
230 END IF;
231
232 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
233 RAISE FND_API.G_EXC_ERROR;
234 END IF;
235 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
236 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
237 END IF;
238
239 END IF;
240
241 IF (p_enable_debug = 'Y') THEN
242 FND_FILE.PUT_LINE(FND_FILE.LOG, 'step2b : number of array count ='||p_minisite_ids.COUNT);
243 END IF;
244 -- Process the template mappings
245 FOR l_i IN 1..p_minisite_ids.COUNT LOOP
246 -- Check if the mapping exists or not
247 IF (p_enable_debug = 'Y') THEN
248 FND_FILE.PUT_LINE(FND_FILE.LOG,
249 'Step 3 : itemid='||l_item_id||
250 ':access_name='||p_access_name||
251 'msite_id='||p_minisite_ids(l_i)||
252 'langcode='||p_language_codes(l_i)||
253 'p_default_sites='||p_default_sites(l_i)||
254 'p_default_lang='||p_default_languages(l_i));
255 END IF;
256
257 OPEN c_get_map_csr(l_item_id, p_minisite_ids(l_i),
258 TRIM(p_language_codes(l_i)),
259 TRIM(p_default_sites(l_i)),
260 TRIM(p_default_languages(l_i)));
261
262 FETCH c_get_map_csr INTO l_lgl_phys_map_id, l_attachment_id;
263
264 IF (p_enable_debug = 'Y') THEN
265 FND_FILE.PUT_LINE(FND_FILE.LOG,
266 'Step4 : lgl_phys_map_id='||l_lgl_phys_map_id||
267 ':l_attachment_id='||l_attachment_id);
268 END IF;
269
270
271 IF (c_get_map_csr%NOTFOUND) THEN
272 l_lgl_phys_map_id := NULL;
273 l_attachment_id := NULL;
274 END IF;
275
276 CLOSE c_get_map_csr;
277
278 -- New mapping: Create new attachment and mapping
279 -- Non seed existing mapping: Update attachment file name
280 IF (l_lgl_phys_map_id IS NULL) OR (l_lgl_phys_map_id >= 10000) THEN
281
282 l_attachment_rec.attachment_id := l_attachment_id;
283 l_attachment_rec.file_name := p_file_names(l_i);
284 l_attachment_rec.deliverable_id :=l_item_id;
285 l_attachment_rec.attachment_used_by:=l_attachment_used_by;
286 l_attachment_rec.application_id:=l_appl_id;
287
288 /* Bug#5882497, scnagara
289 a) The attachment_id present for another site which corresponds to the file
290 is fetched from the IBE_DSP_LGL_PHYS_MAP and JTF_AMV_ATTACHMENTS tables.
291 b) The attachment_id is also passed to the IBE_Attachment_GRP.save_attachment
292 through the variable of type IBE_ATTACHMENT_GRP.ATTACHMENT_REC_TYPE.
293 */
294
295 IF (p_enable_debug = 'Y') THEN
296 FND_FILE.PUT_LINE(FND_FILE.LOG,
297 'Item id is '||l_item_id);
298 FND_FILE.PUT_LINE(FND_FILE.LOG,
299 'File name is '|| p_file_names(l_i) );
300 END IF;
301
302 OPEN c_get_attachment_id_csr(l_attachment_rec.deliverable_id,l_attachment_rec.file_name);
303 FETCH c_get_attachment_id_csr INTO l_attachment_id;
304 CLOSE c_get_attachment_id_csr;
305
306 IF (p_enable_debug = 'Y') THEN
307 FND_FILE.PUT_LINE(FND_FILE.LOG,'The Attachment id is '|| l_attachment_id);
308 END IF;
309
310 -- retrieve the existing attachment details if attachment id not null
311 IF (l_attachment_id IS NOT NULL AND l_attachment_id >0) then
312
313 OPEN c_get_attachment_csr(l_attachment_id);
314 -- Bug#5882497, scnagara
315 -- The attachment_id is fetched into l_attachment_rec.attachment_id
316 FETCH c_get_attachment_csr INTO
317 l_attachment_rec.attachment_id,
318 l_attachment_rec.object_version_number,
319 l_attachment_rec.attachment_used_by,
320 l_attachment_rec.enabled_flag,
321 l_attachment_rec.can_fulfill_electronic_flag,
322 l_attachment_rec.file_id,
323 l_attachment_rec.file_extension,
324 l_attachment_rec.keywords,
325 l_attachment_rec.send_for_preview_flag,
326 l_attachment_rec.attachment_type,
327 l_attachment_rec.language_code,
328 l_attachment_rec.application_id,
329 l_attachment_rec.description;
330
331
332 IF (p_enable_debug = 'Y') THEN
333 FND_FILE.PUT_LINE(FND_FILE.LOG,
334 'Step4a :object version number ='||l_attachment_rec.object_version_number);
335 END IF;
336 CLOSE c_get_attachment_csr;
337 END IF;
338
339 IF (p_enable_debug = 'Y') THEN
340 FND_FILE.PUT_LINE(FND_FILE.LOG,
341 'Step 5 :Attachment id='||l_attachment_id||
342 ':file_names='||l_attachment_rec.file_name);
343 END IF;
344
345
346 IBE_Attachment_GRP.save_attachment(
347 p_api_version => 1.0,
348 p_init_msg_list => FND_API.g_false,
349 p_commit => FND_API.g_false,
350 x_return_status => l_return_status,
351 x_msg_count => l_msg_count,
352 x_msg_data => l_msg_data,
353 p_attachment_rec => l_attachment_rec);
354
355
356 IF (p_enable_debug = 'Y') THEN
357 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Step 6 :after save atachment return status='||l_return_status);
358 END IF;
359
360 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
361 RAISE FND_API.G_EXC_ERROR;
362 END IF;
363 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
364 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
365 END IF;
366
367 l_attachment_id := l_attachment_rec.attachment_id;
368 IF (p_enable_debug = 'Y') THEN
369 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Step 6a :The attachment id now is='||l_attachment_id);
370 END IF;
371
372 IF (l_lgl_phys_map_id IS NULL) THEN
373
374 IF (p_enable_debug = 'Y') THEN
375 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Step 7: inserting into ibe_dsp_lgl_phys_map');
376 END IF;
377 -- OPEN c_lgl_phys_map_seq;
378 -- FETCH c_lgl_phys_map_seq into l_next_val;
379 -- close c_lgl_phys_map_seq;
380
381 IF (p_enable_debug = 'Y') THEN
382 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Step 7a :value of next sequence='||l_next_val);
383 END IF;
384
385 INSERT INTO IBE_DSP_LGL_PHYS_MAP(lgl_phys_map_id, item_id,
386 msite_id, language_code, default_site, default_language,
387 attachment_id, content_item_key,object_version_number,
388 created_by,creation_date,last_updated_by,last_update_date)
389 VALUES (IBE_DSP_LGL_PHYS_MAP_S1.NEXTVAL, l_item_id,
390 p_minisite_ids(l_i), p_language_codes(l_i), p_default_sites(l_i),
391 p_default_languages(l_i), l_attachment_id, NULL,
392 1,FND_GLOBAL.user_id,sysdate,FND_GLOBAL.user_id,sysdate);
393
394 IF (p_enable_debug = 'Y') THEN
395 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Step 8 :after insertion into ibe_sp_lgl_phys_map');
396 END IF;
397
398
399 END IF;
400 END IF;
401
402 END LOOP;
403 IF (p_enable_debug = 'Y') THEN
404 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Step 9: out of the mappings loop');
405 END IF;
406 IF (p_commit = l_true) THEN
407 COMMIT;
408 END IF;
409 EXCEPTION
410 WHEN FND_API.G_EXC_ERROR THEN
411 --x_error_num := x_error_num + 1;
412 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
413 ibe_util.debug('Expected error in IBE_DELIVERABLE_GRP.save_template_mappping');
414 END IF;
415 ROLLBACK TO save_template_mapping_pvt;
416 x_return_status := FND_API.G_RET_STS_ERROR;
417 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
418 p_count => x_msg_count ,
419 p_data => x_msg_data);
420 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
421 --x_error_num := x_error_num + 1;
422 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
423 ibe_util.debug('Unexpected error in IBE_DELIVERABLE_GRP.save_template_mappping');
424 END IF;
425 ROLLBACK TO save_template_mapping_pvt;
426 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
427 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
428 p_count => x_msg_count ,
429 p_data => x_msg_data);
430 WHEN OTHERS THEN
431 ROLLBACK TO save_template_mapping_pvt;
432 -- x_error_num := x_error_num + 1;
433 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
434 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
435 ibe_util.debug('Unknown error in IBE_DELIVERABLE_GRP.save_template_mappping');
436 END IF;
437 IF FND_Msg_Pub.Check_Msg_Level( FND_Msg_Pub.G_MSG_LVL_UNEXP_ERROR ) THEN
438 FND_Msg_Pub.Add_Exc_Msg(G_PKG_NAME,
439 L_API_NAME);
440 END IF;
441 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
442 p_count => x_msg_count ,
443 p_data => x_msg_data);
444 END save_template_mapping;
445
446 ------------------end added by abhandar --------------------------
447
448
449
450 END IBE_DELIVERABLE_EXPIMP_PVT;
[Home] [Help]