1 PACKAGE BODY AMS_CPageUtility_PVT AS
2 /* $Header: amsvcpgb.pls 115.28 2002/06/09 17:55:22 pkm ship $ */
3 --
4 --
5 g_pkg_name CONSTANT VARCHAR2(30) := 'AMS_CPageUtility_PVT';
6
7 -- Added the flag below as a workaround for IBC Code to work.
8 -- Currently creating the content items for each section as reusable items.
9 -- Mar 26,2002 : IBC has shifted away from reusable_flag and now we are
10 -- using parent_item_id
11 g_reusable_flag CONSTANT VARCHAR2(1) := FND_API.g_true;
12
13 --
14
15 g_commit_on_lock_unlock CONSTANT VARCHAR2(1) := FND_API.g_false;
16
17 -- Currently we do not send commit as TRUE to lock and unlock calls.
18 --
19
20 g_lock_flag_value CONSTANT VARCHAR2(1) := FND_API.g_false;
21 g_using_locking CONSTANT VARCHAR2(1) := FND_API.g_false;
22
23 -- Currently we are not using the locking mechanism from IBC as the LOCK and UNLOCK
24 -- takes effect only if we commit the transaction, which is not what we want.
25
26 g_wd_restricted_flag_value CONSTANT VARCHAR2(1) := FND_API.g_false;
27
28 -- The wd_restricted_flag='T' means the items is not for runtime,
29 -- only administrators who have access to the directory can manipulate them.
30 -- All items for public view (runtime read) shld set this flag to 'F'.
31
32 --
33 -- Declare private procedure signatures here.
34 --
35 --
36 --------------------------------------------------------------------
37 -- PROCEDURE
38 -- create_citem_for_delv
39 --
40 -- PURPOSE
41 -- Create a Content Item for Deliverable of type Content Page.
42 --
43 -- NOTES
44 -- 1. The required input is as follows:
45 -- content_type_code
46 -- default_display_template_id
47 -- deliverable_id
48 -- association_type_code (to be recorded in ibc_associations table)
49 -- 2. This procedure returns the Content Item ID of the newly created
50 -- Content Item associated with the given deliverable.
51 --
52 -- HISTORY
53 -- 29-JAN-2002 gdeodhar Created.
54 -----------------------------------------------------------------------
55 PROCEDURE create_citem_for_delv(
56 p_content_type_code IN VARCHAR2,
57 p_def_disp_template_id IN NUMBER,
58 p_delv_id IN NUMBER,
59 p_assoc_type_code IN VARCHAR2,
60 p_commit IN VARCHAR2 DEFAULT FND_API.g_false,
61 p_api_version IN NUMBER DEFAULT 1.0,
62 p_api_validation_level IN NUMBER DEFAULT FND_API.g_valid_level_full,
63 x_citem_id OUT NUMBER,
64 x_citem_ver_id OUT NUMBER,
65 x_return_status OUT VARCHAR2,
66 x_msg_count OUT NUMBER,
67 x_msg_data OUT VARCHAR2
68 )
69 IS
70 --
71 -- Declare the local variables and cursors here.
72 --
73 -- Cursor to select the Deliverable Details to record in the Content Item Data.
74 --
75 CURSOR c_delv_details IS
76 SELECT owner_user_id
77 ,actual_avail_from_date
78 ,actual_avail_to_date
79 ,deliverable_name
80 ,description
81 FROM ams_deliverables_vl
82 WHERE deliverable_id = p_delv_id ;
83 --
84 l_owner_res_id NUMBER ;
85 l_start_date DATE ;
86 l_end_date DATE ;
87 l_delv_name VARCHAR2(240) ;
88 l_delv_desc VARCHAR2(4000) ;
89 --
90 l_citem_ver_id NUMBER ;
91 l_citem_id NUMBER ;
92 l_return_status VARCHAR2(1) ;
93 l_msg_count NUMBER ;
94 l_msg_data VARCHAR2(2000) ;
95 --
96 l_obj_ver_num NUMBER ;
97 l_assoc_id NUMBER ;
98 --
99 l_attribute_type_codes JTF_VARCHAR2_TABLE_100 := JTF_VARCHAR2_TABLE_100() ;
100 l_attributes JTF_VARCHAR2_TABLE_4000 := JTF_VARCHAR2_TABLE_4000() ;
101 --
102 l_assoc_type_codes JTF_VARCHAR2_TABLE_100 := JTF_VARCHAR2_TABLE_100() ;
103 l_assoc_objects1 JTF_VARCHAR2_TABLE_300 := JTF_VARCHAR2_TABLE_300() ;
104 l_assoc_objects2 JTF_VARCHAR2_TABLE_300 := JTF_VARCHAR2_TABLE_300() ;
105 l_assoc_objects3 JTF_VARCHAR2_TABLE_300 := JTF_VARCHAR2_TABLE_300() ;
106 l_assoc_objects4 JTF_VARCHAR2_TABLE_300 := JTF_VARCHAR2_TABLE_300() ;
107 l_assoc_objects5 JTF_VARCHAR2_TABLE_300 := JTF_VARCHAR2_TABLE_300() ;
108 --
109 l_init_msg_list VARCHAR2(1) := FND_API.g_true;
110 --
111 l_api_version_number CONSTANT NUMBER := 1.0;
112 l_api_name CONSTANT VARCHAR2(30) := 'create_citem_for_delv';
113 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
114
115 BEGIN
116 --
117 -- Standard Start of API savepoint
118 SAVEPOINT create_citem_for_delv_PVT;
119
120 -- Standard call to check for call compatibility.
121 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
122 p_api_version,
123 l_api_name,
124 G_PKG_NAME)
125 THEN
126 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
127 END IF;
128
129 -- Initialize message list if p_init_msg_list is set to TRUE.
130
131 --l_init_msg_list := p_init_msg_list;
132
133 IF FND_API.to_Boolean( l_init_msg_list )
134 THEN
135 FND_MSG_PUB.initialize;
136 END IF;
137
138 l_init_msg_list := FND_API.g_false;
139
140 -- Debug Message
141 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
142
143 -- Initialize API return status to SUCCESS
144
145 x_return_status := FND_API.G_RET_STS_SUCCESS;
146 l_return_status := FND_API.G_RET_STS_SUCCESS;
147
148 --
149 -- This procedure will create a new Content Item in IBC Schema.
150 -- It will record the Default Display Template ID as the value of one special Attribute
151 -- of the same Content Item.
152 -- It will also create a record in IBC_ASSOCIATIONS table to associate the Deliverable
153 -- and the newly created Content Item.
154 --
155 -- Fetch the Deliverable Details.
156
157 OPEN c_delv_details;
158 FETCH c_delv_details INTO l_owner_res_id, l_start_date, l_end_date, l_delv_name, l_delv_desc;
159 CLOSE c_delv_details;
160 --
161 -- Prepare the Attribute Bundle if necessary.
162 IF p_def_disp_template_id IS NOT NULL
163 THEN
164 -- Call the procedure IBC_CITEM_ADMIN_GRP.set_citem_att_bundle.
165 -- prepare the data for insert.
166 --
167 l_attribute_type_codes.extend();
168 l_attribute_type_codes(1) := G_DEFAULT_DISPLAY_TEMPLATE; -- Should be a CONSTANT in package.
169 --
170 l_attributes.extend();
171 l_attributes(1) := p_def_disp_template_id;
172 --
173 ELSE
174 l_attribute_type_codes := NULL;
175 l_attributes := NULL;
176 END IF;
177
178
179 -- Create a new Content Item in IBC Schema for incoming Content Type.
180
181 -- Call the procedure upsert_item. This method allows creation of Content Item from one
182 -- single API.
183
184 IBC_CITEM_ADMIN_GRP.upsert_item(
185 p_ctype_code => p_content_type_code
186 ,p_citem_name => l_delv_name
187 ,p_citem_description => substr(l_delv_desc,1,2000)
188 ,p_dir_node_id => G_AMS_DIR_NODE_ID
189 ,p_owner_resource_id => l_owner_res_id
190 ,p_owner_resource_type => G_OWNER_RESOURCE_TYPE
191 ,p_reference_code => NULL -- Why is this needed?
192 ,p_trans_required => FND_API.g_false -- This is the default value. For now we do not expose this flag on the UI.
193 ,p_parent_item_id => NULL -- There is no parent for the content item that is associated with the Deliverable.
194 ,p_lock_flag => g_lock_flag_value
195 ,p_wd_restricted => g_wd_restricted_flag_value
196 ,p_start_date => l_start_date
197 ,p_end_date => l_end_date
198 ,p_attach_file_id => NULL
199 ,p_attribute_type_codes => l_attribute_type_codes
200 ,p_attributes => l_attributes
201 ,p_component_citems => NULL
202 ,p_component_atypes => NULL
203 ,p_sort_order => NULL
204 ,p_status => G_CITEM_WIP_STATUS_CODE -- When the Deliverable becomes active, we will go in and approve all the underlying content items.
205 ,p_log_action => FND_API.g_true -- This to be sent as TRUE. It updates the Audit Logs.
206 ,p_commit => FND_API.g_false -- We still have to do some more operations.
207 ,p_api_version_number => p_api_version
208 ,p_init_msg_list => l_init_msg_list
209 ,px_content_item_id => l_citem_id
210 ,px_citem_ver_id => l_citem_ver_id
211 ,px_object_version_number => l_obj_ver_num
212 ,x_return_status => l_return_status
213 ,x_msg_count => l_msg_count
214 ,x_msg_data => l_msg_data
215 );
216
217 AMS_UTILITY_PVT.debug_message('After upsert_item.');
218 AMS_UTILITY_PVT.debug_message('l_citem_id = ' || l_citem_id);
219 AMS_UTILITY_PVT.debug_message('l_citem_ver_id = ' || l_citem_ver_id);
220 AMS_UTILITY_PVT.debug_message('l_obj_ver_num = ' || l_obj_ver_num);
221 AMS_UTILITY_PVT.debug_message('l_return_status = ' || l_return_status);
222
223 IF FND_API.g_ret_sts_success <> l_return_status
224 THEN
225 AMS_Utility_PVT.Error_Message('AMS_ERR_CREATE_CITEM');
226 RAISE FND_API.g_exc_error;
227 END IF;
228
229 l_init_msg_list := FND_API.g_false ; -- This point onwards, we should not initialize the message list.
230
231 -- If the above statement is successful, add the association record in IBC_ASSOCIATIONS.
232 --
233 -- Prepare the data for insert.
234
235 l_assoc_type_codes.extend();
236 l_assoc_type_codes(1) := p_assoc_type_code ;
237
238 l_assoc_objects1.extend();
239 l_assoc_objects1(1) := p_delv_id ;
240
241 l_assoc_objects2.extend();
242 l_assoc_objects2(1) := p_content_type_code ;
243
244 l_assoc_objects3.extend();
245 l_assoc_objects3(1) := p_def_disp_template_id ;
246
247 l_assoc_objects4.extend();
248 l_assoc_objects4(1) := NULL ;
249
250 l_assoc_objects5.extend();
251 l_assoc_objects5(1) := NULL ;
252
253 -- Call the procedure IBC_CITEM_ADMIN_GRP.insert_associations.
254
255 IBC_CITEM_ADMIN_GRP.insert_associations(
256 p_content_item_id => l_citem_id
257 ,p_assoc_type_codes => l_assoc_type_codes
258 ,p_assoc_objects1 => l_assoc_objects1
259 ,p_assoc_objects2 => l_assoc_objects2 -- Denormalized Value stored here. This can be debated. Remove if decided against storing it here.
260 ,p_assoc_objects3 => l_assoc_objects3 -- Denormalized Value stored here. This can be debated. Remove if decided against storing it here.
261 ,p_assoc_objects4 => l_assoc_objects4 -- Null values.
262 ,p_assoc_objects5 => l_assoc_objects5 -- Null values.
263 ,p_commit => FND_API.g_false -- This is the Default.
264 ,p_api_version_number => p_api_version
265 ,p_init_msg_list => l_init_msg_list
266 -- The following are OUT parameters in this procedure.
267 --,x_assoc_id => l_assoc_id
268 -- Jamie applied his package on mapdev01.
269 -- He has changed signature of this method.
270 -- as a result commenting the above line.
271 ,x_return_status => l_return_status
272 ,x_msg_count => l_msg_count
273 ,x_msg_data => l_msg_data
274 );
275 --
276 --
277 AMS_UTILITY_PVT.debug_message('Insert Assoc.');
278 AMS_UTILITY_PVT.debug_message('l_assoc_id = ' || l_assoc_id);
279 AMS_UTILITY_PVT.debug_message('l_citem_ver_id = ' || l_citem_ver_id);
280 AMS_UTILITY_PVT.debug_message('l_obj_ver_num = ' || l_obj_ver_num);
281 AMS_UTILITY_PVT.debug_message('l_return_status = ' || l_return_status);
282
283 IF FND_API.g_ret_sts_success <> l_return_status
284 THEN
285 AMS_Utility_PVT.Error_Message('AMS_ERR_CREATE_CITEM_ASSOC');
286 RAISE FND_API.g_exc_error;
287 END IF;
288 --
289
290 -- We should not approve this item at this time.
291 -- When the associated Deliverable gets the approval and becomes actives, we should
292 -- approve the main content item associated with the deliverable and we should also
293 -- approve the components of that main content item.
294
295 IF g_using_locking = FND_API.g_true
296 THEN
297 --
298 -- At this stage we must UNLOCK this content item as we will soon commit this transaction.
299 -- Call the procedure IBC_CITEM_ADMIN_GRP.unlock_item
300 --
301 IBC_CITEM_ADMIN_GRP.unlock_item(
302 p_content_item_id => l_citem_id
303 ,p_commit => g_commit_on_lock_unlock
304 ,p_api_version_number => p_api_version
305 ,p_init_msg_list => l_init_msg_list
306 ,x_return_status => l_return_status
307 ,x_msg_count => l_msg_count
308 ,x_msg_data => l_msg_data
309 );
310 --
311 AMS_UTILITY_PVT.debug_message('After Unlock.');
312 AMS_UTILITY_PVT.debug_message('l_return_status = ' || l_return_status);
313 --
314 --
315 IF FND_API.g_ret_sts_success <> l_return_status
316 THEN
317 AMS_Utility_PVT.Error_Message('AMS_ERR_UNLOCK_CITEM');
318 RAISE FND_API.g_exc_error;
319 END IF;
320
321 END IF;
322 --
323 -- If we come till here, everything has been done successfully.
324 -- Commit the work and set the output values.
325 --
326 -- Standard check for p_commit
327 IF FND_API.to_Boolean( p_commit )
328 THEN
329 COMMIT WORK;
330 END IF;
331 --
332 --
333 x_citem_id := l_citem_id;
334 x_citem_ver_id := l_citem_ver_id;
335 x_return_status := l_return_status;
336 --
337 -- Standard call to get message count and if count is 1, get message info.
338 FND_MSG_PUB.count_and_get(
339 p_count => x_msg_count,
340 p_data => x_msg_data
341 );
342 --
343 EXCEPTION
344 WHEN FND_API.g_exc_error THEN
345 ROLLBACK TO create_citem_for_delv_PVT;
346 x_return_status := FND_API.G_RET_STS_ERROR;
347 FND_MSG_PUB.count_and_get (
348 p_count => x_msg_count,
349 p_data => x_msg_data,
350 p_encoded => FND_API.G_FALSE
351 );
352 WHEN FND_API.g_exc_unexpected_error THEN
353 ROLLBACK TO create_citem_for_delv_PVT;
354 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
355 FND_MSG_PUB.count_and_get (
356 p_count => x_msg_count,
357 p_data => x_msg_data,
358 p_encoded => FND_API.G_FALSE
359 );
360 WHEN OTHERS THEN
361 ROLLBACK TO create_citem_for_delv_PVT;
362 x_return_status := FND_API.g_ret_sts_unexp_error;
363 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
364 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
365 END IF;
366 FND_MSG_PUB.count_and_get (
367 p_count => x_msg_count,
368 p_data => x_msg_data,
369 p_encoded => FND_API.G_FALSE
370 );
371 --
372 --
373 END create_citem_for_delv;
374 --
375
376
377 -----------------------------------------------------------------------
378 -- PROCEDURE
379 -- approve_citem_for_delv
380 --
381 -- PURPOSE
385 -- 1. The required input is as follows:
382 -- Approve the Content Item associated with the Deliverable of type Content Page.
383 --
384 -- NOTES
386 -- content_type_code
387 -- deliverable_id
388 -- content_item_id
389 -- association_type_code (this is recorded in ibc_associations table)
390 -- 2. This procedure returns the success or failure status
391 --
392 -- COMMENTS added on May 06, 2002.
393 /*
394 Notes for changes in Approve CItem for Delv procedure :
395
396 1. There has to be a validate_citem_for_delv procedure that does the following :
397
398 Goes through all the sections and checks that all the data is OK.
399
400 Rich Content Section :
401
402 1. It checks that there is an attachment created.
403
404 Questions Section :
405
406 1. Dropdown/checkbox/RadioButton/List question :
407 Check that there are some answers.
408
409 Submit Section :
410
411 1. Must be defined if there are questions.
412
413 CP Image Section :
414
415 1. Nothing to check.
416
417 2. If all the validations are done proceed with incomplete definition check.
418
419 3. Incomplete definition must check if there are some sections undefined.
420 If so, this is not really an error, but we must make sure that the mandatory sections are defined.
421 The IBC API approve_item checks this, but it will be good if we check it before hand.
422
423 ______
424
425 Provide a report to the user.
426
427 When user clicks proceed, we must then call :
428
429 1. Complete definition process.
430
431 This will generate the XML for Questions Section if necessary by calling update_questions_section.
432
433 Other completion tasks as identified.
434
435 2. Approve Item call.
436 */
437 -----------------------------------------------------------------------
438 PROCEDURE approve_citem_for_delv(
439 p_content_type_code IN VARCHAR2,
440 p_delv_id IN NUMBER,
441 p_citem_id IN NUMBER,
442 p_assoc_type_code IN VARCHAR2,
443 p_commit IN VARCHAR2 DEFAULT FND_API.g_false,
444 p_api_version IN NUMBER DEFAULT 1.0,
445 p_api_validation_level IN NUMBER DEFAULT FND_API.g_valid_level_full,
446 x_return_status OUT VARCHAR2,
447 x_msg_count OUT NUMBER,
448 x_msg_data OUT VARCHAR2
449 )
450 IS
451 -- Declare the local variables and cursors here.
452 --
453 CURSOR c_delv_details IS
454 SELECT status_code
455 FROM ams_deliverables_vl
456 WHERE deliverable_id = p_delv_id ;
457 --
458 --
459 l_delv_status_code VARCHAR2(30) ;
460 l_return_status VARCHAR2(1) ;
461 l_msg_count NUMBER ;
462 l_msg_data VARCHAR2(2000) ;
463 --
464 l_obj_ver_num NUMBER ;
465 l_citem_id NUMBER ;
466 l_citem_ver_id NUMBER ;
467 l_cpnt_citem_ver_id NUMBER ;
468 l_cpnt_obj_ver_num NUMBER ;
469 --
470 l_content_item_id NUMBER ;
471 --
472 -- Cursor to select the latest citem version for a content item.
473 --
474 CURSOR c_max_version IS
475 SELECT MAX(citem_version_id)
476 FROM ibc_citem_versions_b
477 WHERE content_item_id = l_content_item_id ;
478 --
479 l_status VARCHAR2(30) ;
480 l_attach_file_id NUMBER ;
481 l_attach_file_name VARCHAR2(240) ;
482 l_citem_name VARCHAR2(240) ;
483 l_description VARCHAR2(2000) ;
484 l_attribute_type_codes JTF_VARCHAR2_TABLE_100 ;
485 l_attribute_type_names JTF_VARCHAR2_TABLE_300 ;
486 l_attributes JTF_VARCHAR2_TABLE_4000 ;
487 l_cpnt_citems JTF_NUMBER_TABLE ;
488 l_cpnt_ctypes JTF_VARCHAR2_TABLE_100 ;
489 l_cpnt_attrib_types JTF_VARCHAR2_TABLE_100 ;
490 l_cpnt_citem_names JTF_VARCHAR2_TABLE_300 ;
491 l_cpnt_owner_ids JTF_NUMBER_TABLE ;
492 l_cpnt_owner_types JTF_VARCHAR2_TABLE_100 ;
493 l_cpnt_owner_names JTF_VARCHAR2_TABLE_400 ;
494 l_cpnt_sort_orders JTF_NUMBER_TABLE ;
495 l_object_version_number NUMBER ;
496 --
497 l_api_name CONSTANT VARCHAR2(30) := 'Approve_Citem_For_Delv';
498 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
499 --
500 --
501 l_init_msg_list VARCHAR2(1) := FND_API.g_true;
502 --
503 l_api_version_number CONSTANT NUMBER := 1.0;
504 --
505 BEGIN
506 --
507
508 -- Standard call to check for call compatibility.
509 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
510 p_api_version,
511 l_api_name,
512 G_PKG_NAME)
513 THEN
514 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
515 END IF;
516
517 -- Initialize message list if p_init_msg_list is set to TRUE.
518
519 --l_init_msg_list := p_init_msg_list;
520
521 IF FND_API.to_Boolean( l_init_msg_list )
522 THEN
523 FND_MSG_PUB.initialize;
524 END IF;
525
526 l_init_msg_list := FND_API.g_false;
527
528 -- Debug Message
529 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
530
531 -- Initialize API return status to SUCCESS
532 x_return_status := FND_API.G_RET_STS_SUCCESS;
533 l_return_status := FND_API.G_RET_STS_SUCCESS;
534
535 -- Check if the deliverable is in ACTIVE status first.
539 CLOSE c_delv_details ;
536 -- Fetch the Deliverable Details.
537 OPEN c_delv_details ;
538 FETCH c_delv_details INTO l_delv_status_code ;
540
541 /*
542 IF l_delv_status_code NOT IN ('ACTIVE','AVAILABLE')
543 THEN
544 AMS_Utility_PVT.Error_Message('AMS_ERR_DELV_NOT_ACTIVE');
545 RAISE FND_API.g_exc_error;
546 END IF;
547 */
548 -- Removing the above check for now. Will revist.
549
550 IF g_using_locking = FND_API.g_true
551 THEN
552
553 -- We have to lock the item first.
554 IBC_CITEM_ADMIN_GRP.lock_item(
555 p_content_item_id => p_citem_id
556 ,p_commit => g_commit_on_lock_unlock
557 ,p_api_version_number => p_api_version
558 ,p_init_msg_list => l_init_msg_list
559 ,x_citem_version_id => l_citem_ver_id
560 ,x_object_version_number => l_obj_ver_num
561 ,x_return_status => l_return_status
562 ,x_msg_count => l_msg_count
563 ,x_msg_data => l_msg_data
564 );
565
566 IF FND_API.g_ret_sts_success <> l_return_status
567 THEN
568 AMS_Utility_PVT.Error_Message('AMS_ERR_LOCKING_CITEM');
569 RAISE FND_API.g_exc_error;
570 END IF;
571 ELSE
572
573 -- If we do not use locking mechanism, we will have to get the latest citem version
574 -- for this deliverable at this stage.
575 -- Fetch the latest citem version id.
576
577 l_content_item_id := p_citem_id;
578
579 OPEN c_max_version ;
580 FETCH c_max_version INTO l_citem_ver_id ;
581 CLOSE c_max_version ;
582
583 -- We must also fetch the object version number.
584 l_obj_ver_num := IBC_CITEM_ADMIN_GRP.getObjVerNum( p_citem_id );
585
586 END IF;
587
588 AMS_Utility_PVT.Debug_Message(' l_content_item_id = ' || l_content_item_id ) ;
589 AMS_Utility_PVT.Debug_Message(' l_citem_ver_id = ' || l_citem_ver_id ) ;
590 AMS_Utility_PVT.Debug_Message(' obj ver num = ' || l_obj_ver_num ) ;
591
592 -- Get the components of this content item.
593 -- We must approve the components first.
594
595 -- Call get_content_item_data.
596 get_content_item_data(
597 p_citem_id => p_citem_id
598 ,p_citem_ver_id => l_citem_ver_id
599 ,p_api_version => p_api_version
600 ,x_status => l_status
601 ,x_attach_file_id => l_attach_file_id
602 ,x_attach_file_name => l_attach_file_name
603 ,x_citem_name => l_citem_name
604 ,x_description => l_description
605 ,x_attribute_type_codes => l_attribute_type_codes
606 ,x_attribute_type_names => l_attribute_type_names
607 ,x_attributes => l_attributes
608 ,x_cpnt_citem_ids => l_cpnt_citems
609 ,x_cpnt_ctype_codes => l_cpnt_ctypes
610 ,x_cpnt_attrib_types => l_cpnt_attrib_types
611 ,x_cpnt_citem_names => l_cpnt_citem_names
612 ,x_cpnt_sort_orders => l_cpnt_sort_orders
613 ,x_object_version_number => l_object_version_number
614 ,x_return_status => l_return_status
615 ,x_msg_count => l_msg_count
616 ,x_msg_data => l_msg_data
617 );
618
619 IF FND_API.g_ret_sts_success <> l_return_status
620 THEN
621 AMS_Utility_PVT.Error_Message('AMS_ERR_GET_CITEM');
622 RAISE FND_API.g_exc_error;
623 END IF;
624
625 -- First approve each of the components.
626 IF l_cpnt_citems IS NOT NULL
627 THEN
628 FOR i IN l_cpnt_citems.first .. l_cpnt_citems.last
629 LOOP
630 -- Note that when locking is enabled, we still need not lock the components
631 -- as we have locked the parent content item.
632 -- So obtain the citem version id and object version number for the
633 -- component the usual way.
634
635 l_content_item_id := l_cpnt_citems(i);
636
637 OPEN c_max_version ;
638 FETCH c_max_version INTO l_cpnt_citem_ver_id ;
639 CLOSE c_max_version ;
640
641 -- We must also fetch the object version number.
642 l_cpnt_obj_ver_num := IBC_CITEM_ADMIN_GRP.getObjVerNum( l_content_item_id );
643
644 --The line below does not work.
645 --The l_cpnt_attrib_types is un-initialized.
646 --Must check the original get_item from IBC API to see if that is working.
647 --AMS_Utility_PVT.Debug_Message(' For component ' || l_cpnt_attrib_types(i) );
648 AMS_Utility_PVT.Debug_Message(' l_content_item_id = ' || l_content_item_id );
649 AMS_Utility_PVT.Debug_Message(' l_cpnt_citem_ver_id = ' || l_cpnt_citem_ver_id );
650 AMS_Utility_PVT.Debug_Message(' l_cpnt_obj_ver_num = ' || l_cpnt_obj_ver_num );
651
652 IF l_cpnt_citem_ver_id IS NOT NULL
653 AND
654 l_cpnt_obj_ver_num IS NOT NULL
655 THEN
656
657 -- Approve this component.
658 IBC_CITEM_ADMIN_GRP.approve_item(
659 p_citem_ver_id => l_cpnt_citem_ver_id
660 ,p_commit => FND_API.g_false
661 ,p_api_version_number => p_api_version
662 ,p_init_msg_list => l_init_msg_list
663 ,px_object_version_number => l_cpnt_obj_ver_num
664 ,x_return_status => l_return_status
665 ,x_msg_count => l_msg_count
669 IF FND_API.g_ret_sts_success <> l_return_status
666 ,x_msg_data => l_msg_data
667 );
668
670 THEN
671 AMS_Utility_PVT.Error_Message('AMS_ERR_APPROVE_CITEM');
672 RAISE FND_API.g_exc_error;
673 END IF;
674
675 END IF;
676
677 END LOOP ;
678
679 END IF ;
680
681 -- Now approve the main parent component item.
682 IBC_CITEM_ADMIN_GRP.approve_item(
683 p_citem_ver_id => l_citem_ver_id
684 ,p_commit => FND_API.g_false
685 ,p_api_version_number => p_api_version
686 ,p_init_msg_list => l_init_msg_list
687 ,px_object_version_number => l_obj_ver_num
688 ,x_return_status => l_return_status
689 ,x_msg_count => l_msg_count
690 ,x_msg_data => l_msg_data
691 );
692
693 IF FND_API.g_ret_sts_success <> l_return_status
694 THEN
695 AMS_Utility_PVT.Error_Message('AMS_ERR_APPROVE_CITEM');
696 RAISE FND_API.g_exc_error;
697 END IF;
698
699 IF g_using_locking = FND_API.g_true
700 THEN
701
702 -- unlock the content item.
703 IBC_CITEM_ADMIN_GRP.unlock_item(
704 p_content_item_id => p_citem_id
705 ,p_commit => g_commit_on_lock_unlock
706 ,p_api_version_number => p_api_version
707 ,p_init_msg_list => l_init_msg_list
708 ,x_return_status => l_return_status
709 ,x_msg_count => l_msg_count
710 ,x_msg_data => l_msg_data
711 );
712
713 AMS_UTILITY_PVT.debug_message('After Unlock.');
714 AMS_UTILITY_PVT.debug_message('l_return_status = ' || l_return_status);
715
716 --
717 IF FND_API.g_ret_sts_success <> l_return_status
718 THEN
719 AMS_Utility_PVT.Error_Message('AMS_ERR_UNLOCK_CITEM');
720 RAISE FND_API.g_exc_error;
721 END IF;
722
723 END IF;
724
725
726 -- Commit the work and set the output values.
727
728 -- Standard check for p_commit
729 IF FND_API.to_Boolean( p_commit )
730 THEN
731 COMMIT WORK;
732 END IF;
733
734 x_return_status := l_return_status;
735
736 -- Standard call to get message count and if count is 1, get message info.
737 FND_MSG_PUB.count_and_get(
738 p_count => x_msg_count,
739 p_data => x_msg_data
740 );
741
742 EXCEPTION
743 WHEN FND_API.g_exc_error THEN
744 ROLLBACK ;
745 x_return_status := FND_API.G_RET_STS_ERROR;
746 FND_MSG_PUB.count_and_get (
747 p_count => x_msg_count,
748 p_data => x_msg_data,
749 p_encoded => FND_API.G_FALSE
750 );
751 WHEN FND_API.g_exc_unexpected_error THEN
752 ROLLBACK ;
753 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
754 FND_MSG_PUB.count_and_get (
755 p_count => x_msg_count,
756 p_data => x_msg_data,
757 p_encoded => FND_API.G_FALSE
758 );
762 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
759 WHEN OTHERS THEN
760 ROLLBACK ;
761 x_return_status := FND_API.g_ret_sts_unexp_error;
763 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
764 END IF;
765 FND_MSG_PUB.count_and_get (
766 p_count => x_msg_count,
767 p_data => x_msg_data,
768 p_encoded => FND_API.G_FALSE
769 );
770
771 END approve_citem_for_delv;
772
773
774 --
775 -----------------------------------------------------------------------
776 -- PROCEDURE
777 -- validate_citem_for_delv
778 --
779 -- PURPOSE
780 -- Validate Content Item created for a Deliverable before approval.
781 --
782 -- NOTES
783 -- 1. This procedures validates the contents of a content item associated with
784 -- the given deliverable.
785 -- 2. The procedure logs messages in activity log table.
786 -- 3. procedure returns the fact that it is successful.
787 --
788 -- HISTORY
789 -- 06-MAY-2002 gdeodhar Created as a stub.
790 --
791 -----------------------------------------------------------------------
792 PROCEDURE validate_citem_for_delv(
793 p_content_type_code IN VARCHAR2,
794 p_delv_id IN NUMBER,
795 p_citem_id IN NUMBER,
796 p_citem_version_id IN NUMBER,
797 p_assoc_type_code IN VARCHAR2,
798 p_commit IN VARCHAR2 DEFAULT FND_API.g_false,
799 p_api_version IN NUMBER DEFAULT 1.0,
800 p_api_validation_level IN NUMBER DEFAULT FND_API.g_valid_level_full,
801 x_return_status OUT VARCHAR2,
802 x_msg_count OUT NUMBER,
803 x_msg_data OUT VARCHAR2
804 )
805 IS
806 BEGIN
807 null;
808 END validate_citem_for_delv;
809
810
811
812 --
813 --
814 -----------------------------------------------------------------------
815 -- PROCEDURE
816 -- manage_rich_content
817 --
818 -- PURPOSE
819 -- Manage a Rich Content Item.
820 --
821 -- NOTES
822 -- 1. The required input is as follows:
823 -- Content Type Code for the Section. This must be RICH_CONTENT.
824 -- Content Type Name. (This is the same as Section Name when this item is created in the context of a parent content item).
825 -- Attachment File ID that has the Rich Content Data.
826 -- Attachment File Name.
827 -- Start Date
828 -- End Date
829 -- Owner Resource ID
830 -- Owner Resource Type
831 -- Value for HAS_MERGE_FIELDS
832 -- Value for HAS_PAGE_MERGE_FIELDS
833 -- 2. The optional input is as follows:
834 -- Content Item Id : If given Update is done.
835 -- Content Item Version Id : If given Update is done.
836 -- Description.
837 -- Attribute Type Code for the Section.
838 -- The Content Item Version ID of the Parent Content Item
839 -- The Content Type Code associated with the Parent Content Item.
840 -- If the above two are available, this procedure will create a
841 -- compound relation between the Parent Content Item Version ID and
842 -- the Content Item ID of the newly created Content Item.
843 -- VARCHAR2 Array of Data Source Programmatic Access Codes.
844 -- VARCHAR2 Array of Merge Field names.
845 -- Note that these names contain the Programmatic Access Code for the
846 -- Data Source as well as the Column Name, separated by a period.
847 -- If this Array has data, the SELECT_SQL_QUERY type of Content Items
848 -- will be created for each of Data Sources that appear in the list.
849 -- The MERGE_FIELD Content Items will be created for each of the item
850 -- in the Array.
851 -- Compound relations will be created between the MERGE_FIELD items and
852 -- SELECT_SQL_QUERY items and between SELECT_SQL_QUERY items and the
853 -- newly created RICH_CONTENT item.
854 -- 3. This procedure performs the following steps:
855 -- 1. Create a Basic Content Item for Rich Content with insert_basic_citem
856 -- 2. Add the Meta Data with set_citem_meta.
857 -- 3. Set the Attachment for this Content Item.
858 -- 4. Set the Attribute Bundle for this Content Item.
859 -- Arrive at the value for FUNCTIONAL_TYPE.
860 -- This will consist of the following attributes:
861 -- HAS_MERGE_FIELDS
862 -- HAS_PAGE_MERGE_FIELDS
863 -- FUNCTIONAL_TYPE
867 -- 6. If the Merge Fields List is not empty, do the following:
864 -- 5. If the details of Parent Content Item are available,
865 -- create the compound relation between the parent content item and the
866 -- newly created RICH_CONTENT item.
868 -- Collect all the Merge Fields from one data source together.
869 -- For each such data source, do the following:
870 -- Create MERGE_FIELD Content Item for each Merge Field for this Data Source with an APPROVED status. Use BULK_INSERT.
871 -- Pick up the Field Type from Data Source schema.
872 -- Generate SQL Query for the resolution of these Merge Fields in APPROVED status. Use BULK_INSERT.
873 -- Create the SELECT_SQL_QUERY content item.
874 -- Create Compound Relations between the SELECT_SQL_QUERY and the MERGE_FIELD content items.
875 -- Create Compound Relations between the SELECT_SQL_QUERY items and the RICH_CONTENT content item.
876 -- 4. This procedure returns the fact that it is successful.
877 --
878 -- HISTORY
879 -- 14-FEB-2002 gdeodhar Created.
880 -- 11-MAR-2002 gdeodhar Added Update to the same method.
881 --
882 -----------------------------------------------------------------------
883 PROCEDURE manage_rich_content(
884 p_content_type_code IN VARCHAR2,
885 p_content_item_name IN VARCHAR2,
886 p_description IN VARCHAR2,
887 p_delv_id IN NUMBER,
888 p_attach_file_id IN NUMBER,
889 p_attach_file_name IN VARCHAR2,
890 p_owner_resource_id IN NUMBER,
891 p_owner_resource_type IN VARCHAR2,
892 p_has_merge_fields IN VARCHAR2,
893 p_has_page_merge_fields IN VARCHAR2,
894 p_reusable_flag IN VARCHAR2 DEFAULT FND_API.g_false, -- CHANGE to Y or N when IBC folks change the conventions for varchar2 fields.
895 p_data_source_list IN JTF_VARCHAR2_TABLE_300,
896 p_merge_fields_list IN JTF_VARCHAR2_TABLE_300,
897 p_attribute_type_code IN VARCHAR2,
898 p_parent_citem_id IN NUMBER,
899 p_parent_citem_ver_id IN NUMBER,
900 p_parent_ctype_code IN VARCHAR2,
901 p_commit IN VARCHAR2 DEFAULT FND_API.g_false,
902 p_api_version IN NUMBER DEFAULT 1.0,
903 p_api_validation_level IN NUMBER DEFAULT FND_API.g_valid_level_full,
904 px_citem_id IN OUT NUMBER,
905 px_citem_ver_id IN OUT NUMBER,
906 x_return_status OUT VARCHAR2,
907 x_msg_count OUT NUMBER,
908 x_msg_data OUT VARCHAR2,
909 p_dml_flag IN VARCHAR2,
910 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.g_true
911 )
912 IS
913 --
914 -- Declare the local variables and cursors here.
915 -- Cursor to select the Deliverable Details to record in the Content Item Data.
916 --
917 CURSOR c_delv_details IS
918 SELECT actual_avail_from_date
919 ,actual_avail_to_date
920 FROM ams_deliverables_vl
921 WHERE deliverable_id = p_delv_id ;
922 --
923 l_start_date DATE ;
924 l_end_date DATE ;
925 --
926 l_citem_ver_id NUMBER ;
927 l_citem_id NUMBER ;
928 l_return_status VARCHAR2(1) ;
929 l_msg_count NUMBER ;
930 l_msg_data VARCHAR2(2000) ;
931 --
932 l_obj_ver_num NUMBER ;
933 --
934 l_attribute_type_codes JTF_VARCHAR2_TABLE_100 := JTF_VARCHAR2_TABLE_100() ;
935 l_attributes JTF_VARCHAR2_TABLE_4000 := JTF_VARCHAR2_TABLE_4000() ;
936 l_citem_ids JTF_NUMBER_TABLE := JTF_NUMBER_TABLE() ;
937 l_citem_attrs JTF_VARCHAR2_TABLE_100 := JTF_VARCHAR2_TABLE_100() ;
938 l_dummy_sort_order JTF_NUMBER_TABLE := JTF_NUMBER_TABLE() ;
939 --
940 l_init_msg_list VARCHAR2(1) := FND_API.g_true;
941 --
942 l_api_version_number CONSTANT NUMBER := 1.0;
943 l_api_name CONSTANT VARCHAR2(30) := 'manage_rich_content';
944 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
945 --
946 l_attr_count NUMBER ;
947 l_bind_vars_list JTF_VARCHAR2_TABLE_300 := JTF_VARCHAR2_TABLE_300() ;
948 l_select_sql_statement VARCHAR2(4000) ;
949 l_data_src_type_code VARCHAR2(300) ;
950 BEGIN
951 --
952 -- Standard Start of API savepoint
953 SAVEPOINT manage_rich_content_PVT ;
954
955 -- Standard call to check for call compatibility.
956 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
957 p_api_version,
961 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
958 l_api_name,
959 G_PKG_NAME)
960 THEN
962 END IF;
963
964 -- Initialize message list if p_init_msg_list is set to TRUE.
965
966 l_init_msg_list := p_init_msg_list;
967
968 IF FND_API.to_Boolean( l_init_msg_list )
969 THEN
970 FND_MSG_PUB.initialize;
971 END IF;
972
973 l_init_msg_list := FND_API.g_false;
974
975 -- Debug Message
976 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
977
978 -- Initialize API return status to SUCCESS
979 x_return_status := FND_API.G_RET_STS_SUCCESS;
980 l_return_status := FND_API.G_RET_STS_SUCCESS;
981 --
982 IF p_content_type_code <> G_RICH_CONTENT -- Should be a CONSTANT in the package.
983 THEN
984 AMS_Utility_PVT.Error_Message('AMS_ERR_WRONG_CTYPE_CODE');
985 RAISE FND_API.g_exc_error;
986 END IF;
987 --
988 -- Check if the call is for Create or Update.
989
990 l_citem_id := px_citem_id ;
991 l_citem_ver_id := px_citem_ver_id ;
992
993 AMS_UTILITY_PVT.debug_message( 'l_citem_id = ' || l_citem_id );
994 AMS_UTILITY_PVT.debug_message( 'l_citem_ver_id = ' || l_citem_ver_id );
995 AMS_UTILITY_PVT.debug_message( 'p_dml_flag = ' || p_dml_flag );
996
997 IF p_dml_flag <> 'C'
998 THEN
999 -- check if content item id and content item version id is available.
1000 IF l_citem_id IS NULL
1001 OR
1002 l_citem_ver_id IS NULL
1003 THEN
1004 AMS_Utility_PVT.Error_Message('AMS_ERR_NO_CITEM_OR_VER_ID');
1005 RAISE FND_API.g_exc_error;
1006 END IF;
1007 END IF;
1008
1009 -- For update, ideally we must check if the dates for the parent deliverable
1010 -- have changed and update the dates as well.
1011 -- To be done later.
1012
1013 --
1014 --
1015 -- prepare the data for insert / update
1016
1017 l_attr_count := 0;
1018
1019 IF p_dml_flag <> 'D'
1020 THEN
1021
1022 l_attr_count := l_attr_count + 1;
1023 l_attribute_type_codes.extend();
1024 l_attribute_type_codes(l_attr_count) := G_HAS_MERGE_FIELDS;
1025
1026 l_attributes.extend();
1027 l_attributes(l_attr_count) := p_has_merge_fields;
1028 --
1029 l_attr_count := l_attr_count + 1;
1030 l_attribute_type_codes.extend();
1031 l_attribute_type_codes(l_attr_count) := G_HAS_PAGE_MERGE_FIELDS;
1032 --
1033 l_attributes.extend();
1034 l_attributes(l_attr_count) := p_has_page_merge_fields;
1035 --
1036 -- Check if the Data Source is available.
1037 IF p_data_source_list IS NOT NULL
1038 AND
1039 p_data_source_list(1) IS NOT NULL
1040 THEN
1041
1042 -- We also have to update the ams_list_src_type_usages table.
1043 -- For CREATE, we have to insert the record in the above table.
1044 -- For UPDATE, we have to update the record in the above table.
1045 -- To be coded when the APIs for ams_list_src_type_usages are available.
1046
1047 -- The data source list is not null.
1048 -- Currently we support only one data source per rich content.
1049 l_attr_count := l_attr_count + 1;
1050 l_attribute_type_codes.extend();
1051 l_attribute_type_codes(l_attr_count) := G_DATA_SOURCE;
1052
1053 l_attributes.extend();
1057
1054 l_attributes(l_attr_count) := p_data_source_list(1);
1055
1056 AMS_UTILITY_PVT.debug_message( ' p_data_source_list(1) = ' || p_data_source_list(1) );
1058 -- Check if the Merge Fields are available.
1059 -- populate the attributes for Merge Fields.
1060 IF p_merge_fields_list IS NOT NULL
1061 THEN
1062
1063 FOR i IN p_merge_fields_list.first .. p_merge_fields_list.last
1064 LOOP
1065 l_attr_count := l_attr_count + 1;
1066 l_attribute_type_codes.extend();
1067 l_attribute_type_codes(l_attr_count) := G_MERGE_FIELD;
1068
1069 l_attributes.extend();
1070 l_attributes(l_attr_count) := substr(p_merge_fields_list(i), 1, 30);
1071
1072 -- GDEODHAR : May 06, 2002.
1073 -- Changed the above line to use substr.
1074 -- When the alias name exceeds 30 characters, it gives problem with the
1075 -- SQL statement execution at runtime.
1076 -- Now truncating the alias after 30th character while generating the
1077 -- select SQL statement in generate_select_sql method.
1078 -- Hence the change has to happen here as well.
1079 -- At runtime, there has to be one-to-one correspondance between the
1080 -- select column and the merge field name recorded here.
1081
1082 END LOOP;
1083
1084 END IF;
1085
1086 l_data_src_type_code := p_data_source_list(1);
1087
1088 -- Generate the SQL statement for the Merge Fields.
1089 generate_select_sql(
1090 p_data_source_code => l_data_src_type_code
1091 ,p_data_source_fields_list => p_merge_fields_list
1092 ,p_data_source_field_ids => NULL
1093 ,x_select_sql_statement => l_select_sql_statement
1094 ,x_bind_vars => l_bind_vars_list
1095 ,x_return_status => l_return_status
1096 ,x_msg_count => l_msg_count
1097 ,x_msg_data => l_msg_data
1098 );
1099
1100 IF FND_API.g_ret_sts_success <> l_return_status
1101 THEN
1102 AMS_Utility_PVT.Error_Message('AMS_ERR_CPAGE_GEN_SQL');
1103 RAISE FND_API.g_exc_error;
1104 END IF;
1105
1106 AMS_UTILITY_PVT.debug_message( ' l_select_sql_statement = ' || l_select_sql_statement );
1107
1108 -- Set the attributes for Select SQL Statement and bind variables.
1109 IF l_select_sql_statement IS NOT NULL
1110 THEN
1111 l_attr_count := l_attr_count + 1;
1112 l_attribute_type_codes.extend();
1113 l_attribute_type_codes(l_attr_count) := G_SELECT_SQL_STATEMENT ;
1114
1115 l_attributes.extend();
1116 l_attributes(l_attr_count) := l_select_sql_statement ;
1117
1118 IF l_bind_vars_list IS NOT NULL
1119 THEN
1120 FOR i IN l_bind_vars_list.first .. l_bind_vars_list.last
1121 LOOP
1122 l_attr_count := l_attr_count + 1;
1123 l_attribute_type_codes.extend();
1124 l_attribute_type_codes(l_attr_count) := G_BIND_VAR;
1125
1126 l_attributes.extend();
1127 l_attributes(l_attr_count) := l_bind_vars_list(i);
1128 END LOOP;
1129 END IF;
1130
1131 END IF;
1132
1133 ELSE
1134
1135 -- If this is an update call, check if there was a data source associated
1136 -- with this content item and delete that record from the ams_list_src_type_usages
1137 -- table.
1138 -- To be coded when the APIs for ams_list_src_type_usages are available.
1139
1140 null;
1141
1142 END IF;
1143
1144 -- Arrive at the Functional Type.
1145 -- The Functional Type depends on what Data Source are associated with this content.
1146 -- We will add this functionality later, when we decide something concrete about the
1147 -- Data Sources.
1148 l_attr_count := l_attr_count + 1;
1149 l_attribute_type_codes.extend();
1150 l_attribute_type_codes(l_attr_count) := G_FUNCTIONAL_TYPE ;
1151
1152 l_attributes.extend();
1153 l_attributes(l_attr_count) := G_DEFAULT_FUNCTIONAL_TYPE ;
1154 --
1155
1156 END IF;
1157
1158 IF p_dml_flag = 'C'
1159 THEN
1160 -- Create a new Content Item in IBC Schema for incoming Content Type.
1161
1162 -- Fetch the Deliverable Details.
1163 OPEN c_delv_details;
1164 FETCH c_delv_details INTO l_start_date, l_end_date;
1165 CLOSE c_delv_details;
1166
1170 p_ctype_code => p_content_type_code
1167 -- Call IBC_CITEM_ADMIN_GRP.upsert_item procedure.
1168
1169 IBC_CITEM_ADMIN_GRP.upsert_item(
1171 ,p_citem_name => p_content_item_name
1172 ,p_citem_description => substr(p_description,1,2000)
1173 ,p_dir_node_id => G_AMS_DIR_NODE_ID
1174 ,p_owner_resource_id => p_owner_resource_id
1175 ,p_owner_resource_type => p_owner_resource_type
1176 ,p_reference_code => NULL -- Why is this needed?
1177 ,p_trans_required => FND_API.g_false -- This is the default value. For now we do not expose this flag on the UI.
1178 ,p_parent_item_id => p_parent_citem_id -- Specify the parent content item id. This item is visible only in the context of this parent.
1179 ,p_lock_flag => g_lock_flag_value
1180 ,p_wd_restricted => g_wd_restricted_flag_value
1181 ,p_start_date => l_start_date
1182 ,p_end_date => l_end_date
1183 ,p_attach_file_id => p_attach_file_id -- This procedure picks up the file name from FND_LOBS.
1184 ,p_attribute_type_codes => l_attribute_type_codes
1185 ,p_attributes => l_attributes
1186 ,p_component_citems => NULL
1187 ,p_component_atypes => NULL
1188 ,p_sort_order => NULL
1189 ,p_status => G_CITEM_WIP_STATUS_CODE -- When the Deliverable becomes active, we will go in and approve all the underlying content items.
1190 ,p_log_action => FND_API.g_true -- This to be sent as TRUE. It updates the Audit Logs.
1191 ,p_commit => FND_API.g_false -- We still have to do some more operations.
1192 ,p_api_version_number => p_api_version
1193 ,p_init_msg_list => l_init_msg_list
1194 ,px_content_item_id => l_citem_id
1195 ,px_citem_ver_id => l_citem_ver_id
1196 ,px_object_version_number => l_obj_ver_num
1197 ,x_return_status => l_return_status
1198 ,x_msg_count => l_msg_count
1199 ,x_msg_data => l_msg_data
1200 );
1201
1202 AMS_UTILITY_PVT.debug_message('After upsert_item.');
1203 AMS_UTILITY_PVT.debug_message('l_citem_id = ' || l_citem_id);
1204 AMS_UTILITY_PVT.debug_message('l_citem_ver_id = ' || l_citem_ver_id);
1205 AMS_UTILITY_PVT.debug_message('l_obj_ver_num = ' || l_obj_ver_num);
1206 AMS_UTILITY_PVT.debug_message('l_return_status = ' || l_return_status);
1207
1208 IF FND_API.g_ret_sts_success <> l_return_status
1209 THEN
1210 AMS_Utility_PVT.Error_Message('AMS_ERR_CREATE_CITEM');
1211 RAISE FND_API.g_exc_error;
1212 END IF;
1213
1214 -- There is a bug in IBC Code. The field attachment_attribute_code in the
1215 -- table ibc_citem_versions_tl is getting updated some times. Filed a bug on IBC
1216 -- to track this issue.
1217 -- Bug # is : 2290924.
1218
1219 UPDATE ibc_citem_versions_tl
1220 SET attachment_attribute_code = 'ATTACHMENT' -- hardcoding as ATTACHMENT for Rich Content.
1221 WHERE citem_version_id = l_citem_ver_id ;
1222
1223 -- We do not approve this item at this point.
1224 -- When the Deliverable becomes active, we will approve all underlying content items.
1225 --
1226
1227 -- If the information about the parent content item is available, create the
1228 -- compound relation between the parent content item and the newly created RICH_CONTENT
1229 -- item.
1230 --
1231 IF p_parent_citem_ver_id IS NOT NULL
1232 AND
1233 p_parent_ctype_code IS NOT NULL
1234 AND
1235 p_attribute_type_code IS NOT NULL
1236 THEN
1237 -- prepare the data for insert.
1238 l_citem_attrs.extend();
1239 l_citem_attrs(1) := p_attribute_type_code;
1240 --
1241 l_citem_ids.extend();
1242 l_citem_ids(1) := l_citem_id;
1243 --
1244 l_dummy_sort_order.extend();
1245 l_dummy_sort_order(1) := 1;
1246 --
1247
1248 AMS_Utility_PVT.Debug_Message( ' p_parent_citem_ver_id = ' || p_parent_citem_ver_id );
1249 AMS_Utility_PVT.Debug_Message( ' l_citem_ids(1) = ' || l_citem_ids(1) );
1250 AMS_Utility_PVT.Debug_Message( ' l_citem_attrs(1) = ' || l_citem_attrs(1) );
1251 AMS_Utility_PVT.Debug_Message( ' p_api_version = ' || p_api_version );
1252 AMS_Utility_PVT.Debug_Message( ' l_init_msg_list = ' || l_init_msg_list );
1253
1254 IBC_CITEM_ADMIN_GRP.insert_components(
1258 ,p_sort_order => l_dummy_sort_order -- The NULL does not work. -- The new API is supposed to be able to take NULL for this parameter.
1255 p_citem_ver_id => p_parent_citem_ver_id
1256 ,p_content_item_ids => l_citem_ids
1257 ,p_attribute_type_codes => l_citem_attrs
1259 ,p_commit => FND_API.g_false
1260 ,p_api_version_number => p_api_version
1261 ,p_init_msg_list => l_init_msg_list
1262 ,x_return_status => l_return_status
1263 ,x_msg_count => l_msg_count
1264 ,x_msg_data => l_msg_data
1265 );
1266 END IF;
1267 --
1268 --
1269 IF FND_API.g_ret_sts_success <> l_return_status
1270 THEN
1271 AMS_Utility_PVT.Error_Message('AMS_ERR_ADD_COMPOUND_REL');
1272 RAISE FND_API.g_exc_error;
1273 END IF;
1274
1275 IF g_using_locking = FND_API.g_true
1276 THEN
1277
1278 -- At this stage we must UNLOCK this content item as we will soon commit this transaction.
1279 -- Call the procedure IBC_CITEM_ADMIN_GRP.unlock_content_item
1280 --
1281
1282 IBC_CITEM_ADMIN_GRP.unlock_item(
1283 p_content_item_id => l_citem_id
1284 ,p_commit => g_commit_on_lock_unlock
1285 ,p_api_version_number => p_api_version
1286 ,p_init_msg_list => l_init_msg_list
1287 ,x_return_status => l_return_status
1288 ,x_msg_count => l_msg_count
1289 ,x_msg_data => l_msg_data
1290 );
1291
1292 AMS_UTILITY_PVT.debug_message('After Unlock.');
1293 AMS_UTILITY_PVT.debug_message('l_return_status = ' || l_return_status);
1294
1295 IF FND_API.g_ret_sts_success <> l_return_status
1296 THEN
1297 AMS_Utility_PVT.Error_Message('AMS_ERR_UNLOCK_CITEM');
1298 RAISE FND_API.g_exc_error;
1299 END IF;
1300
1301 END IF;
1302
1303 ELSIF p_dml_flag = 'U'
1304 THEN
1305 -- Update mode.
1306 -- For update we do not allow update of name and description.
1307 -- Only change is to the attribute bundle, and the attachment file.
1308
1309 -- call update_content_item.
1310
1311 update_content_item(
1312 p_citem_id => l_citem_id
1313 ,p_citem_version_id => l_citem_ver_id
1314 ,p_content_type_code => p_content_type_code
1315 ,p_content_item_name => NULL -- We do not allow update on this one yet.
1316 ,p_description => NULL -- We do not allow update on this one yet.
1317 ,p_delv_id => p_delv_id
1318 ,p_attr_types_for_update => l_attribute_type_codes
1319 ,p_attr_values_for_update => l_attributes
1320 ,p_attach_file_id => p_attach_file_id
1321 ,p_attach_file_name => p_attach_file_name
1322 ,p_commit => FND_API.g_false
1323 ,p_api_version => p_api_version
1324 ,p_api_validation_level => p_api_validation_level
1325 ,x_return_status => l_return_status
1326 ,x_msg_count => l_msg_count
1327 ,x_msg_data => l_msg_data
1328 ,p_replace_attr_bundle => FND_API.g_true
1329 );
1330
1331 IF FND_API.g_ret_sts_success <> l_return_status
1332 THEN
1333 AMS_Utility_PVT.Error_Message('AMS_ERR_UPDATE_CITEM');
1334 RAISE FND_API.g_exc_error;
1335 END IF;
1336
1337 END IF;
1338
1339
1340 -- If we come till here, everything has been created successfully.
1341 -- Commit the work and set the output values.
1342 --
1343 -- Standard check for p_commit
1344 IF FND_API.to_Boolean( p_commit )
1345 THEN
1346 COMMIT WORK;
1347 END IF;
1348 --
1349 x_return_status := l_return_status;
1350 px_citem_id := l_citem_id;
1351 px_citem_ver_id := l_citem_ver_id;
1352 --
1353 -- Standard call to get message count and if count is 1, get message info.
1354 FND_MSG_PUB.count_and_get(
1355 p_count => x_msg_count,
1356 p_data => x_msg_data
1357 );
1358 --
1359 EXCEPTION
1360 WHEN FND_API.g_exc_error THEN
1361 ROLLBACK TO manage_rich_content_PVT ;
1362 x_return_status := FND_API.G_RET_STS_ERROR;
1363 FND_MSG_PUB.count_and_get (
1364 p_count => x_msg_count,
1365 p_data => x_msg_data,
1366 p_encoded => FND_API.G_FALSE
1367 );
1368 WHEN FND_API.g_exc_unexpected_error THEN
1369 ROLLBACK TO manage_rich_content_PVT ;
1370 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1371 FND_MSG_PUB.count_and_get (
1372 p_count => x_msg_count,
1373 p_data => x_msg_data,
1374 p_encoded => FND_API.G_FALSE
1375 );
1376 WHEN OTHERS THEN
1377 ROLLBACK TO manage_rich_content_PVT ;
1378 x_return_status := FND_API.g_ret_sts_unexp_error;
1379 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1380 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
1381 END IF;
1382 FND_MSG_PUB.count_and_get (
1383 p_count => x_msg_count,
1384 p_data => x_msg_data,
1385 p_encoded => FND_API.G_FALSE
1386 );
1387 --
1388 --
1389 END manage_rich_content;
1390 --
1391
1392
1396 --
1393 -----------------------------------------------------------------------
1394 -- PROCEDURE
1395 -- manage_toc_section
1397 -- PURPOSE
1398 -- Manage a TOC Section Item.
1399 --
1400 -- NOTES
1401 -- 1. The required input is as follows:
1402 -- Content Type Code for the Section. This must be AMS_TOC.
1403 -- Content Type Name. (This is the same as Section Name when this item is created in the context of a parent content item).
1404 -- Start Date
1405 -- End Date
1406 -- Owner Resource ID
1407 -- Owner Resource Type
1408 -- 2. The optional input is as follows:
1409 -- Content Item Id : If given Update is done.
1410 -- Content Item Version Id : If given Update is done.
1411 -- Description.
1412 -- The Content Item Version ID of the Parent Content Item
1413 -- The Content Type Code associated with the Parent Content Item.
1414 -- If the above two are available, this procedure will create a
1415 -- compound relation between the Parent Content Item Version ID and
1416 -- the Content Item ID of the newly created Content Item.
1417 -- VARCHAR2 caption.
1418 -- VARCHAR2 list style.
1419 -- VARCHAR2 Array of Attribute Type Codes.
1420 -- VARCHAR2 Array of Attribute Values.
1421 -- VARCHAR2 functional type which must be 'NORMAL'
1422 -- 3. This procedure performs the following steps:
1423 -- 1. Create a Basic Content Item for Rich Content with insert_basic_citem
1424 -- 2. Add the Meta Data with set_citem_meta.
1425 -- 4. Set the Attribute Bundle for this Content Item.
1426 -- 5. If the details of Parent Content Item are available,
1427 -- create the compound relation between the parent content item and the
1428 -- newly created RICH_CONTENT item.
1429 -- 4. This procedure returns the fact that it is successful.
1430 --
1431 -- HISTORY
1432 -- 10-APR-2002 asaha Created.
1433 --
1434 -----------------------------------------------------------------------
1435 PROCEDURE manage_toc_section(
1436 p_content_type_code IN VARCHAR2,
1437 p_content_item_name IN VARCHAR2,
1438 p_description IN VARCHAR2,
1439 p_delv_id IN NUMBER,
1440 p_owner_resource_id IN NUMBER,
1441 p_owner_resource_type IN VARCHAR2,
1442 p_reusable_flag IN VARCHAR2 DEFAULT FND_API.g_false, -- CHANGE to Y or N when IBC folks change the conventions for varchar2 fields.
1443 p_attr_types IN JTF_VARCHAR2_TABLE_100 DEFAULT NULL,
1444 p_attr_values IN JTF_VARCHAR2_TABLE_4000 DEFAULT NULL,
1445 p_parent_citem_id IN NUMBER,
1446 p_parent_citem_ver_id IN NUMBER,
1447 p_parent_ctype_code IN VARCHAR2,
1448 p_attribute_type_code IN VARCHAR2,
1449 p_commit IN VARCHAR2 DEFAULT FND_API.g_false,
1450 p_api_version IN NUMBER DEFAULT 1.0,
1451 p_api_validation_level IN NUMBER DEFAULT FND_API.g_valid_level_full,
1452 px_citem_id IN OUT NUMBER,
1453 px_citem_ver_id IN OUT NUMBER,
1454 x_return_status OUT VARCHAR2,
1455 x_msg_count OUT NUMBER,
1456 x_msg_data OUT VARCHAR2,
1457 p_dml_flag IN VARCHAR2,
1458 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.g_true
1459 )
1460 IS
1461 --
1462 -- Declare the local variables and cursors here.
1463 -- Cursor to select the Deliverable Details to record in the Content Item Data.
1464 --
1465 CURSOR c_delv_details IS
1466 SELECT actual_avail_from_date
1467 ,actual_avail_to_date
1468 FROM ams_deliverables_vl
1469 WHERE deliverable_id = p_delv_id ;
1470 --
1471 l_start_date DATE ;
1472 l_end_date DATE ;
1473 --
1474 l_citem_ver_id NUMBER ;
1475 l_citem_id NUMBER ;
1476 l_return_status VARCHAR2(1) ;
1477 l_msg_count NUMBER ;
1478 l_msg_data VARCHAR2(2000) ;
1479 --
1480 l_obj_ver_num NUMBER ;
1481 --
1482 l_citem_ids JTF_NUMBER_TABLE := JTF_NUMBER_TABLE() ;
1483 l_citem_attrs JTF_VARCHAR2_TABLE_100 := JTF_VARCHAR2_TABLE_100() ;
1484 l_dummy_sort_order JTF_NUMBER_TABLE := JTF_NUMBER_TABLE() ;
1485 --
1486 l_init_msg_list VARCHAR2(1) := FND_API.g_true;
1487 --
1488 l_api_version_number CONSTANT NUMBER := 1.0;
1489 l_api_name CONSTANT VARCHAR2(30) := 'manage_toc_section';
1490 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
1491 --
1492 l_attr_count NUMBER ;
1496 SAVEPOINT manage_toc_section_PVT ;
1493 BEGIN
1494 --
1495 -- Standard Start of API savepoint
1497
1498 -- Standard call to check for call compatibility.
1499 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1500 p_api_version,
1501 l_api_name,
1502 G_PKG_NAME)
1503 THEN
1504 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1505 END IF;
1506
1507 -- Initialize message list if p_init_msg_list is set to TRUE.
1508
1509 l_init_msg_list := p_init_msg_list;
1510
1511 IF FND_API.to_Boolean( l_init_msg_list )
1512 THEN
1513 FND_MSG_PUB.initialize;
1514 END IF;
1515
1516 l_init_msg_list := FND_API.g_false;
1517
1518 -- Debug Message
1519 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
1520
1521 -- Initialize API return status to SUCCESS
1522 x_return_status := FND_API.G_RET_STS_SUCCESS;
1523 l_return_status := FND_API.G_RET_STS_SUCCESS;
1524 --
1525 IF p_content_type_code <> G_TOC -- Should be a CONSTANT in the package.
1526 THEN
1527 AMS_Utility_PVT.Error_Message('AMS_ERR_WRONG_CTYPE_CODE');
1528 RAISE FND_API.g_exc_error;
1529 END IF;
1530 --
1531 -- Check if the call is for Create or Update.
1532
1533 l_citem_id := px_citem_id ;
1534 l_citem_ver_id := px_citem_ver_id ;
1535
1536 AMS_UTILITY_PVT.debug_message( 'l_citem_id = ' || l_citem_id );
1537 AMS_UTILITY_PVT.debug_message( 'l_citem_ver_id = ' || l_citem_ver_id );
1538 AMS_UTILITY_PVT.debug_message( 'p_dml_flag = ' || p_dml_flag );
1539
1540 IF p_dml_flag <> 'C'
1541 THEN
1542 -- check if content item id and content item version id is available.
1543 IF l_citem_id IS NULL
1544 OR
1545 l_citem_ver_id IS NULL
1546 THEN
1547 AMS_Utility_PVT.Error_Message('AMS_ERR_NO_CITEM_OR_VER_ID');
1548 RAISE FND_API.g_exc_error;
1549 END IF;
1550 END IF;
1551
1552 -- For update, ideally we must check if the dates for the parent deliverable
1553 -- have changed and update the dates as well.
1554 -- To be done later.
1555
1556 --
1557 --
1558 -- prepare the data for insert / update
1559
1560
1561 IF p_dml_flag = 'C'
1562 THEN
1563 -- Create a new Content Item in IBC Schema for incoming Content Type.
1564
1565 -- Fetch the Deliverable Details.
1566 OPEN c_delv_details;
1567 FETCH c_delv_details INTO l_start_date, l_end_date;
1568 CLOSE c_delv_details;
1569
1570 -- Call IBC_CITEM_ADMIN_GRP.upsert_item procedure.
1571
1572 IBC_CITEM_ADMIN_GRP.upsert_item(
1573 p_ctype_code => p_content_type_code
1574 ,p_citem_name => p_content_item_name
1575 ,p_citem_description => substr(p_description,1,2000)
1576 ,p_dir_node_id => G_AMS_DIR_NODE_ID
1577 ,p_owner_resource_id => p_owner_resource_id
1578 ,p_owner_resource_type => p_owner_resource_type
1579 ,p_reference_code => NULL -- Why is this needed?
1580 ,p_trans_required => FND_API.g_false -- This is the default value. For now we do not expose this flag on the UI.
1581 ,p_parent_item_id => p_parent_citem_id -- Specify the parent content item id. This item is visible only in the context of this parent.
1582 ,p_lock_flag => g_lock_flag_value
1583 ,p_wd_restricted => g_wd_restricted_flag_value
1584 ,p_start_date => l_start_date
1585 ,p_end_date => l_end_date
1586 ,p_attach_file_id => NULL -- This procedure picks up the file name from FND_LOBS.
1587 ,p_attribute_type_codes => p_attr_types
1588 ,p_attributes => p_attr_values
1589 ,p_component_citems => NULL
1590 ,p_component_atypes => NULL
1591 ,p_sort_order => NULL
1592 ,p_status => G_CITEM_WIP_STATUS_CODE -- When the Deliverable becomes active, we will go in and approve all the underlying content items.
1593 ,p_log_action => FND_API.g_true -- This to be sent as TRUE. It updates the Audit Logs.
1594 ,p_commit => FND_API.g_false -- We still have to do some more operations.
1595 ,p_api_version_number => p_api_version
1596 ,p_init_msg_list => l_init_msg_list
1597 ,px_content_item_id => l_citem_id
1598 ,px_citem_ver_id => l_citem_ver_id
1599 ,px_object_version_number => l_obj_ver_num
1600 ,x_return_status => l_return_status
1601 ,x_msg_count => l_msg_count
1602 ,x_msg_data => l_msg_data
1603 );
1604
1605 AMS_UTILITY_PVT.debug_message('After upsert_item.');
1606 AMS_UTILITY_PVT.debug_message('l_citem_id = ' || l_citem_id);
1607 AMS_UTILITY_PVT.debug_message('l_citem_ver_id = ' || l_citem_ver_id);
1608 AMS_UTILITY_PVT.debug_message('l_obj_ver_num = ' || l_obj_ver_num);
1609 AMS_UTILITY_PVT.debug_message('l_return_status = ' || l_return_status);
1610
1611 IF FND_API.g_ret_sts_success <> l_return_status
1612 THEN
1613 AMS_Utility_PVT.Error_Message('AMS_ERR_CREATE_CITEM');
1614 RAISE FND_API.g_exc_error;
1615 END IF;
1616
1617 -- We do not approve this item at this point.
1618 -- When the Deliverable becomes active, we will approve all underlying content items.
1619 --
1620
1624 --
1621 -- If the information about the parent content item is available, create the
1622 -- compound relation between the parent content item and the newly created RICH_CONTENT
1623 -- item.
1625 IF p_parent_citem_ver_id IS NOT NULL
1626 AND
1627 p_parent_ctype_code IS NOT NULL
1628 AND
1629 p_attribute_type_code IS NOT NULL
1630 THEN
1631 -- prepare the data for insert.
1632 l_citem_attrs.extend();
1633 l_citem_attrs(1) := p_attribute_type_code;
1634 --
1635 l_citem_ids.extend();
1636 l_citem_ids(1) := l_citem_id;
1637 --
1638 l_dummy_sort_order.extend();
1639 l_dummy_sort_order(1) := 1;
1640 --
1641
1642 AMS_Utility_PVT.Debug_Message( ' p_parent_citem_ver_id = ' || p_parent_citem_ver_id );
1643 AMS_Utility_PVT.Debug_Message( ' l_citem_ids(1) = ' || l_citem_ids(1) );
1644 AMS_Utility_PVT.Debug_Message( ' l_citem_attrs(1) = ' || l_citem_attrs(1) );
1645 AMS_Utility_PVT.Debug_Message( ' p_api_version = ' || p_api_version );
1646 AMS_Utility_PVT.Debug_Message( ' l_init_msg_list = ' || l_init_msg_list );
1647
1648 IBC_CITEM_ADMIN_GRP.insert_components(
1649 p_citem_ver_id => p_parent_citem_ver_id
1650 ,p_content_item_ids => l_citem_ids
1651 ,p_attribute_type_codes => l_citem_attrs
1652 ,p_sort_order => l_dummy_sort_order -- The NULL does not work. -- The new API is supposed to be able to take NULL for this parameter.
1653 ,p_commit => FND_API.g_false
1654 ,p_api_version_number => p_api_version
1655 ,p_init_msg_list => l_init_msg_list
1656 ,x_return_status => l_return_status
1657 ,x_msg_count => l_msg_count
1658 ,x_msg_data => l_msg_data
1659 );
1660 END IF;
1661 --
1662 --
1663 IF FND_API.g_ret_sts_success <> l_return_status
1664 THEN
1665 AMS_Utility_PVT.Error_Message('AMS_ERR_ADD_COMPOUND_REL');
1666 RAISE FND_API.g_exc_error;
1667 END IF;
1668
1669 IF g_using_locking = FND_API.g_true
1670 THEN
1671
1672 -- At this stage we must UNLOCK this content item as we will soon commit this transaction.
1673 -- Call the procedure IBC_CITEM_ADMIN_GRP.unlock_content_item
1674 --
1675
1676 IBC_CITEM_ADMIN_GRP.unlock_item(
1677 p_content_item_id => l_citem_id
1678 ,p_commit => g_commit_on_lock_unlock
1679 ,p_api_version_number => p_api_version
1680 ,p_init_msg_list => l_init_msg_list
1681 ,x_return_status => l_return_status
1682 ,x_msg_count => l_msg_count
1683 ,x_msg_data => l_msg_data
1684 );
1685
1686 AMS_UTILITY_PVT.debug_message('After Unlock.');
1687 AMS_UTILITY_PVT.debug_message('l_return_status = ' || l_return_status);
1688
1689 IF FND_API.g_ret_sts_success <> l_return_status
1690 THEN
1691 AMS_Utility_PVT.Error_Message('AMS_ERR_UNLOCK_CITEM');
1692 RAISE FND_API.g_exc_error;
1693 END IF;
1694
1695 END IF;
1696
1697 ELSIF p_dml_flag = 'U'
1698 THEN
1699 -- Update mode.
1700 -- For update we do not allow update of name and description.
1701 -- Only change is to the attribute bundle.
1702
1703 -- call update_content_item.
1704
1705 update_content_item(
1706 p_citem_id => l_citem_id
1707 ,p_citem_version_id => l_citem_ver_id
1708 ,p_content_type_code => p_content_type_code
1709 ,p_content_item_name => NULL -- We do not allow update on this one yet.
1710 ,p_description => NULL -- We do not allow update on this one yet.
1711 ,p_delv_id => p_delv_id
1712 ,p_attr_types_for_update => p_attr_types
1713 ,p_attr_values_for_update => p_attr_values
1714 ,p_attach_file_id => NULL
1715 ,p_attach_file_name => NULL
1716 ,p_commit => FND_API.g_false
1717 ,p_api_version => p_api_version
1718 ,p_api_validation_level => p_api_validation_level
1719 ,x_return_status => l_return_status
1720 ,x_msg_count => l_msg_count
1721 ,x_msg_data => l_msg_data
1722 ,p_replace_attr_bundle => FND_API.g_true
1723 );
1724
1725 IF FND_API.g_ret_sts_success <> l_return_status
1726 THEN
1727 AMS_Utility_PVT.Error_Message('AMS_ERR_UPDATE_CITEM');
1728 RAISE FND_API.g_exc_error;
1729 END IF;
1730
1731 END IF;
1732
1733
1734 -- If we come till here, everything has been created successfully.
1735 -- Commit the work and set the output values.
1736 --
1737 -- Standard check for p_commit
1738 IF FND_API.to_Boolean( p_commit )
1739 THEN
1740 COMMIT WORK;
1741 END IF;
1742 --
1743 x_return_status := l_return_status;
1744 px_citem_id := l_citem_id;
1745 px_citem_ver_id := l_citem_ver_id;
1746 --
1747 -- Standard call to get message count and if count is 1, get message info.
1748 FND_MSG_PUB.count_and_get(
1749 p_count => x_msg_count,
1750 p_data => x_msg_data
1751 );
1752 --
1753 EXCEPTION
1754 WHEN FND_API.g_exc_error THEN
1755 ROLLBACK TO manage_toc_section_PVT ;
1759 p_data => x_msg_data,
1756 x_return_status := FND_API.G_RET_STS_ERROR;
1757 FND_MSG_PUB.count_and_get (
1758 p_count => x_msg_count,
1760 p_encoded => FND_API.G_FALSE
1761 );
1762 WHEN FND_API.g_exc_unexpected_error THEN
1763 ROLLBACK TO manage_toc_section_PVT ;
1764 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1765 FND_MSG_PUB.count_and_get (
1766 p_count => x_msg_count,
1767 p_data => x_msg_data,
1768 p_encoded => FND_API.G_FALSE
1769 );
1770 WHEN OTHERS THEN
1771 ROLLBACK TO manage_toc_section_PVT ;
1772 x_return_status := FND_API.g_ret_sts_unexp_error;
1773 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1774 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
1775 END IF;
1776 FND_MSG_PUB.count_and_get (
1777 p_count => x_msg_count,
1778 p_data => x_msg_data,
1779 p_encoded => FND_API.G_FALSE
1780 );
1781 --
1782 --
1783 END manage_toc_section;
1784
1785
1786
1787
1788 --
1789 -----------------------------------------------------------------------
1790 -- PROCEDURE
1791 -- get_rich_content_data
1792 --
1793 -- PURPOSE
1794 -- Get the data from Rich Content Item.
1795 --
1796 -- NOTES
1797 -- 1. The required input is as follows:
1798 -- content_item_id
1799 -- 2. This procedure returns the following data back to the caller.
1800 -- citem_version_id for the content item.
1801 -- attachment_file_id
1802 -- attachment_file_name
1803 -- citem_name
1804 -- attribute_types (array)
1805 -- attribute_values (array)
1806 --
1807 -----------------------------------------------------------------------
1808 PROCEDURE get_rich_content_data(
1809 p_citem_id IN NUMBER,
1810 p_api_version IN NUMBER,
1811 x_citem_ver_id OUT NUMBER,
1812 x_attach_file_id OUT NUMBER,
1813 x_attach_file_name OUT VARCHAR2,
1814 x_citem_name OUT VARCHAR2,
1815 x_attribute_types OUT JTF_VARCHAR2_TABLE_100,
1816 x_attribute_values OUT JTF_VARCHAR2_TABLE_4000,
1817 x_object_version_number OUT NUMBER,
1818 x_return_status OUT VARCHAR2,
1819 x_msg_count OUT NUMBER,
1820 x_msg_data OUT VARCHAR2
1821 )
1822 IS
1823 -- Declare the local variables and cursors here.
1824 -- Cursor to select the latest citem version for a content item.
1825 --
1826 CURSOR c_max_version IS
1827 SELECT MAX(citem_version_id)
1828 FROM ibc_citem_versions_b
1829 WHERE content_item_id = p_citem_id ;
1830 --
1831 l_citem_ver_id NUMBER ;
1832 l_status VARCHAR2(30) ;
1833 --
1834 l_attach_file_id NUMBER ;
1835 l_attach_file_name VARCHAR2(240) ;
1836 l_citem_name VARCHAR2(240) ;
1837 l_description VARCHAR2(2000) ;
1838 l_attribute_type_codes JTF_VARCHAR2_TABLE_100 ;
1839 l_attribute_type_names JTF_VARCHAR2_TABLE_300 ;
1840 l_attributes JTF_VARCHAR2_TABLE_4000 ;
1841 l_cpnt_citems JTF_NUMBER_TABLE ;
1842 l_cpnt_ctypes JTF_VARCHAR2_TABLE_100 ;
1843 l_cpnt_attrib_types JTF_VARCHAR2_TABLE_100 ;
1844 l_cpnt_citem_names JTF_VARCHAR2_TABLE_300 ;
1845 l_cpnt_owner_ids JTF_NUMBER_TABLE ;
1846 l_cpnt_owner_types JTF_VARCHAR2_TABLE_100 ;
1847 l_cpnt_owner_names JTF_VARCHAR2_TABLE_400 ;
1848 l_cpnt_sort_orders JTF_NUMBER_TABLE ;
1849 l_object_version_number NUMBER ;
1850 l_return_status VARCHAR2(1) ;
1851 l_msg_count NUMBER ;
1852 l_msg_data VARCHAR2(2000) ;
1853 --
1854 l_api_name CONSTANT VARCHAR2(30) := 'Get_Rich_Content_Data';
1855 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
1856 --
1857 BEGIN
1858 --
1859 x_return_status := FND_API.g_ret_sts_success;
1860 --
1861 -- Fetch the latest citem version id.
1862 OPEN c_max_version;
1863 FETCH c_max_version INTO l_citem_ver_id;
1864 CLOSE c_max_version;
1865
1866 -- Call get_content_item_data.
1867 get_content_item_data(
1868 p_citem_id => p_citem_id
1869 ,p_citem_ver_id => l_citem_ver_id
1870 ,p_api_version => p_api_version
1871 ,x_status => l_status
1872 ,x_attach_file_id => l_attach_file_id
1873 ,x_attach_file_name => l_attach_file_name
1874 ,x_citem_name => l_citem_name
1878 ,x_attributes => l_attributes
1875 ,x_description => l_description
1876 ,x_attribute_type_codes => l_attribute_type_codes
1877 ,x_attribute_type_names => l_attribute_type_names
1879 ,x_cpnt_citem_ids => l_cpnt_citems
1880 ,x_cpnt_ctype_codes => l_cpnt_ctypes
1881 ,x_cpnt_attrib_types => l_cpnt_attrib_types
1882 ,x_cpnt_citem_names => l_cpnt_citem_names
1883 ,x_cpnt_sort_orders => l_cpnt_sort_orders
1884 ,x_object_version_number => l_object_version_number
1885 ,x_return_status => l_return_status
1886 ,x_msg_count => l_msg_count
1887 ,x_msg_data => l_msg_data
1888 );
1889
1890 IF FND_API.g_ret_sts_success <> l_return_status
1891 THEN
1892 AMS_Utility_PVT.Error_Message('AMS_ERR_GET_RICH_CTNT');
1893 RAISE FND_API.g_exc_error;
1894 END IF;
1895
1896 x_citem_ver_id := l_citem_ver_id;
1897 x_return_status := l_return_status;
1898 x_attach_file_id := l_attach_file_id;
1899 x_attach_file_name := l_attach_file_name;
1900 x_citem_name := l_citem_name;
1901 x_attribute_types := l_attribute_type_codes;
1902 x_attribute_values := l_attributes;
1903 x_object_version_number := l_object_version_number;
1904
1905 -- Standard call to get message count and if count is 1, get message info.
1906 FND_MSG_PUB.count_and_get(
1907 p_count => x_msg_count,
1908 p_data => x_msg_data
1909 );
1910
1911 EXCEPTION
1912 WHEN FND_API.g_exc_error THEN
1913 ROLLBACK;
1914 x_return_status := FND_API.G_RET_STS_ERROR;
1915 FND_MSG_PUB.count_and_get (
1916 p_count => x_msg_count,
1917 p_data => x_msg_data,
1918 p_encoded => FND_API.G_FALSE
1919 );
1920 WHEN FND_API.g_exc_unexpected_error THEN
1921 ROLLBACK;
1922 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1923 FND_MSG_PUB.count_and_get (
1924 p_count => x_msg_count,
1925 p_data => x_msg_data,
1926 p_encoded => FND_API.G_FALSE
1927 );
1928 WHEN OTHERS THEN
1929 ROLLBACK;
1930 x_return_status := FND_API.g_ret_sts_unexp_error;
1931 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1932 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
1933 END IF;
1934 FND_MSG_PUB.count_and_get (
1935 p_count => x_msg_count,
1936 p_data => x_msg_data,
1937 p_encoded => FND_API.G_FALSE
1938 );
1939
1940
1941 END get_rich_content_data;
1942
1943 -----------------------------------------------------------------------
1944 -- PROCEDURE
1945 -- get_content_item_data
1946 --
1947 -- PURPOSE
1948 -- Get the Content Item Details.
1949 --
1950 -- NOTES
1951 -- 1. The required input is as follows:
1952 -- content_item_id
1953 -- content_item_version_id
1954 -- 2. This procedure calls the get_citem from IBC_CITEM_ADMIN_GRP package.
1955 -- It only sends the useful data back to the caller.
1956 --
1957 -----------------------------------------------------------------------
1958 PROCEDURE get_content_item_data(
1959 p_citem_id IN NUMBER,
1960 p_citem_ver_id IN NUMBER,
1961 p_api_version IN NUMBER,
1962 x_status OUT VARCHAR2,
1963 x_attach_file_id OUT NUMBER,
1964 x_attach_file_name OUT VARCHAR2,
1965 x_citem_name OUT VARCHAR2,
1966 x_description OUT VARCHAR2,
1967 x_attribute_type_codes OUT JTF_VARCHAR2_TABLE_100,
1968 x_attribute_type_names OUT JTF_VARCHAR2_TABLE_300,
1969 x_attributes OUT JTF_VARCHAR2_TABLE_4000,
1970 x_cpnt_citem_ids OUT JTF_NUMBER_TABLE,
1971 x_cpnt_ctype_codes OUT JTF_VARCHAR2_TABLE_100,
1972 x_cpnt_attrib_types OUT JTF_VARCHAR2_TABLE_100,
1973 x_cpnt_citem_names OUT JTF_VARCHAR2_TABLE_300,
1974 x_cpnt_sort_orders OUT JTF_NUMBER_TABLE,
1975 x_object_version_number OUT NUMBER,
1976 x_return_status OUT VARCHAR2,
1977 x_msg_count OUT NUMBER,
1978 x_msg_data OUT VARCHAR2
1979 )
1980 IS
1981 --
1982 l_citem_name VARCHAR2(240) ;
1983 l_citem_version NUMBER ;
1984 l_citem_id NUMBER ;
1985 l_dir_node_id NUMBER ;
1986 l_dir_node_name VARCHAR2(240) ;
1987 l_dir_node_code VARCHAR2(240) ;
1988 l_status VARCHAR2(30) ;
1989 l_version_status VARCHAR2(30) ;
1990 l_version_number NUMBER ;
1991 l_citem_description VARCHAR2(2000) ;
1992 l_ctype_code VARCHAR2(30) ;
1993 l_ctype_name VARCHAR2(240) ;
1994 l_start_date DATE ;
1995 l_end_date DATE ;
1996 l_owner_resource_id NUMBER ;
1997 l_owner_resource_type VARCHAR2(30) ;
1998 l_owner_name VARCHAR2(240) ;
1999 l_reference_code VARCHAR2(30) ;
2000 l_trans_required VARCHAR2(1) ;
2001 l_parent_item_id VARCHAR2(240) ;
2002 l_locked_by VARCHAR2(30) ; -- Actually LOCKED_BY in ibc_citems_v is a NUMBER(15) field.
2003 l_wd_restricted VARCHAR2(1) ;
2007 l_created_by NUMBER ;
2004 l_attach_file_id NUMBER ;
2005 l_attach_file_name VARCHAR2(240) ;
2006 l_object_version_number NUMBER ;
2008 l_creation_date DATE ;
2009 l_last_updated_by NUMBER ;
2010 l_last_update_date DATE ;
2011 l_attribute_type_codes JTF_VARCHAR2_TABLE_100 ;
2012 l_attribute_type_names JTF_VARCHAR2_TABLE_300 ;
2013 l_attributes JTF_VARCHAR2_TABLE_4000 ;
2014 l_cpnt_citems JTF_NUMBER_TABLE ;
2015 l_cpnt_ctypes JTF_VARCHAR2_TABLE_100 ;
2016 l_cpnt_attrib_types JTF_VARCHAR2_TABLE_100 ;
2017 l_cpnt_citem_names JTF_VARCHAR2_TABLE_300 ;
2018 l_cpnt_owner_ids JTF_NUMBER_TABLE ;
2019 l_cpnt_owner_types JTF_VARCHAR2_TABLE_100 ;
2020 l_cpnt_owner_names JTF_VARCHAR2_TABLE_400 ;
2021 l_cpnt_sort_orders JTF_NUMBER_TABLE ;
2022 l_return_status VARCHAR2(1) ;
2023 l_msg_count NUMBER ;
2024 l_msg_data VARCHAR2(2000) ;
2025 --
2026 l_api_name CONSTANT VARCHAR2(30) := 'Get_Content_Item_Data';
2027 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
2028 --
2029 BEGIN
2030 --
2031 x_return_status := FND_API.g_ret_sts_success;
2032 AMS_UTILITY_PVT.debug_message(g_pkg_name || '.' || l_api_name || ' Entered the proc.');
2033 l_msg_data := 'Citem id = ' || p_citem_id;
2034 AMS_UTILITY_PVT.debug_message(g_pkg_name || '.' || l_api_name || ' ' || l_msg_data);
2035 l_msg_data := 'Citem Version id = ' || p_citem_ver_id;
2036 AMS_UTILITY_PVT.debug_message(g_pkg_name || '.' || l_api_name || ' ' || l_msg_data);
2037 l_msg_data := 'API Version = ' || p_api_version;
2038 AMS_UTILITY_PVT.debug_message(g_pkg_name || '.' || l_api_name || ' ' || l_msg_data);
2039
2040 --
2041 -- Call Get_item procedure.
2042
2043 IBC_CITEM_ADMIN_GRP.get_item(
2044 p_citem_ver_id => p_citem_ver_id
2045 ,p_init_msg_list => FND_API.g_true
2046 ,p_api_version_number => p_api_version
2047 ,x_content_item_id => l_citem_id
2048 ,x_citem_name => l_citem_name
2049 ,x_citem_version => l_citem_version
2050 ,x_dir_node_id => l_dir_node_id
2051 ,x_dir_node_name => l_dir_node_name
2052 ,x_dir_node_code => l_dir_node_code
2053 ,x_item_status => l_status
2054 ,x_version_status => l_version_status
2055 --,x_version_number => l_version_number
2056 ,x_citem_description => l_citem_description
2057 ,x_ctype_code => l_ctype_code
2058 ,x_ctype_name => l_ctype_name
2059 ,x_start_date => l_start_date
2060 ,x_end_date => l_end_date
2061 ,x_owner_resource_id => l_owner_resource_id
2062 ,x_owner_resource_type => l_owner_resource_type
2063 ,x_reference_code => l_reference_code
2064 ,x_trans_required => l_trans_required
2065 ,x_parent_item_id => l_parent_item_id
2066 ,x_locked_by => l_locked_by
2067 ,x_wd_restricted => l_wd_restricted
2068 ,x_attach_file_id => l_attach_file_id
2069 ,x_attach_file_name => l_attach_file_name
2070 ,x_object_version_number => l_object_version_number
2071 ,x_created_by => l_created_by
2072 ,x_creation_date => l_creation_date
2073 ,x_last_updated_by => l_last_updated_by
2074 ,x_last_update_date => l_last_update_date
2075 ,x_attribute_type_codes => l_attribute_type_codes
2076 ,x_attribute_type_names => l_attribute_type_names
2077 ,x_attributes => l_attributes
2078 ,x_component_citems => l_cpnt_citems
2079 -- Not in the new API ,x_cpnt_ctypes => l_cpnt_ctypes
2080 ,x_component_attrib_types => l_cpnt_attrib_types
2081 ,x_component_citem_names => l_cpnt_citem_names
2082 ,x_component_owner_ids => l_cpnt_owner_ids
2083 ,x_component_owner_types => l_cpnt_owner_types
2084 -- Not in the new API ,x_cpnt_owner_names => l_cpnt_owner_names
2085 ,x_component_sort_orders => l_cpnt_sort_orders
2086 ,x_return_status => l_return_status
2087 ,x_msg_count => l_msg_count
2088 ,x_msg_data => l_msg_data
2089 );
2090
2091 IF FND_API.g_ret_sts_success <> l_return_status
2092 THEN
2093 AMS_Utility_PVT.Error_Message('AMS_ERR_GET_CITEM');
2094 RAISE FND_API.g_exc_error;
2095 END IF;
2096
2097
2098 -- Print the data for debug purposes.
2099 /*
2100 AMS_UTILITY_PVT.debug_message(p_citem_ver_id);
2101 AMS_UTILITY_PVT.debug_message(p_api_version);
2102 AMS_UTILITY_PVT.debug_message(l_citem_id);
2103 AMS_UTILITY_PVT.debug_message(l_citem_name);
2104 AMS_UTILITY_PVT.debug_message(l_citem_version);
2105 AMS_UTILITY_PVT.debug_message(l_dir_node_id);
2106 AMS_UTILITY_PVT.debug_message(l_dir_node_name);
2107 AMS_UTILITY_PVT.debug_message(l_status);
2108 AMS_UTILITY_PVT.debug_message(l_citem_description);
2109 AMS_UTILITY_PVT.debug_message(l_ctype_code);
2110 AMS_UTILITY_PVT.debug_message(l_ctype_name);
2111 AMS_UTILITY_PVT.debug_message(l_start_date);
2112 AMS_UTILITY_PVT.debug_message(l_end_date);
2113 AMS_UTILITY_PVT.debug_message(l_owner_resource_id);
2114 AMS_UTILITY_PVT.debug_message(l_owner_resource_type);
2115 AMS_UTILITY_PVT.debug_message(l_owner_name);
2116 AMS_UTILITY_PVT.debug_message(l_reference_code);
2117 AMS_UTILITY_PVT.debug_message(l_trans_required);
2121 AMS_UTILITY_PVT.debug_message(l_attach_file_id);
2118 AMS_UTILITY_PVT.debug_message(l_reusable_flag);
2119 AMS_UTILITY_PVT.debug_message(l_locked_by);
2120 AMS_UTILITY_PVT.debug_message(l_wd_restricted);
2122 AMS_UTILITY_PVT.debug_message(l_attach_file_name);
2123 AMS_UTILITY_PVT.debug_message(l_object_version_number);
2124 AMS_UTILITY_PVT.debug_message(l_created_by);
2125 AMS_UTILITY_PVT.debug_message(l_creation_date);
2126 AMS_UTILITY_PVT.debug_message(l_last_updated_by);
2127 AMS_UTILITY_PVT.debug_message(l_last_update_date);
2128 if l_attribute_type_codes is null
2129 then
2130 AMS_UTILITY_PVT.debug_message('l_attribute_type_codes is null');
2131 else
2132 AMS_UTILITY_PVT.debug_message('l_attribute_type_codes is not null');
2133 end if;
2134 if l_attribute_type_names is null
2135 then
2136 AMS_UTILITY_PVT.debug_message('l_attribute_type_names is null');
2137 else
2138 AMS_UTILITY_PVT.debug_message('l_attribute_type_names is not null');
2139 end if;
2140 if l_attributes is null
2141 then
2142 AMS_UTILITY_PVT.debug_message('l_attributes is null');
2143 else
2144 AMS_UTILITY_PVT.debug_message('l_attributes is not null');
2145 end if;
2146 if l_cpnt_citems is null
2147 then
2148 AMS_UTILITY_PVT.debug_message('l_cpnt_citems is null');
2149 else
2150 AMS_UTILITY_PVT.debug_message('l_cpnt_citems is not null');
2151 end if;
2152 if l_cpnt_ctypes is null
2153 then
2154 AMS_UTILITY_PVT.debug_message('l_cpnt_ctypes is null');
2155 else
2156 AMS_UTILITY_PVT.debug_message('l_cpnt_ctypes is not null');
2157 end if;
2158 if l_cpnt_attrib_types is null
2159 then
2160 AMS_UTILITY_PVT.debug_message('l_cpnt_attrib_types is null');
2161 else
2162 AMS_UTILITY_PVT.debug_message('l_cpnt_attrib_types is not null');
2163 end if;
2164 if l_cpnt_citem_names is null
2165 then
2166 AMS_UTILITY_PVT.debug_message('l_cpnt_citem_names is null');
2167 else
2168 AMS_UTILITY_PVT.debug_message('l_cpnt_citem_names is not null');
2169 end if;
2170 if l_cpnt_owner_ids is null
2171 then
2172 AMS_UTILITY_PVT.debug_message('l_cpnt_owner_ids is null');
2173 else
2174 AMS_UTILITY_PVT.debug_message('l_cpnt_owner_ids is not null');
2175 end if;
2176 if l_cpnt_owner_ids is null
2177 then
2178 AMS_UTILITY_PVT.debug_message('l_cpnt_owner_types is null');
2179 else
2180 AMS_UTILITY_PVT.debug_message('l_cpnt_owner_types is not null');
2181 end if;
2182 if l_cpnt_owner_names is null
2183 then
2184 AMS_UTILITY_PVT.debug_message('l_cpnt_owner_names is null');
2185 else
2186 AMS_UTILITY_PVT.debug_message('l_cpnt_owner_names is not null');
2187 end if;
2188 if l_cpnt_sort_orders is null
2189 then
2190 AMS_UTILITY_PVT.debug_message('l_cpnt_sort_orders is null');
2191 else
2192 AMS_UTILITY_PVT.debug_message('l_cpnt_sort_orders is not null');
2193 end if;
2194 AMS_UTILITY_PVT.debug_message(l_return_status);
2195 AMS_UTILITY_PVT.debug_message(l_msg_count);
2196 AMS_UTILITY_PVT.debug_message(l_msg_data);
2197 */
2198
2199
2200 x_return_status := l_return_status;
2201 x_status := l_status;
2202 x_attach_file_id := l_attach_file_id;
2203 x_attach_file_name := l_attach_file_name;
2204 x_citem_name := l_citem_name;
2205 x_description := l_citem_description;
2206 x_attribute_type_codes := l_attribute_type_codes;
2207 x_attribute_type_names := l_attribute_type_names;
2208 x_attributes := l_attributes;
2209 x_cpnt_citem_ids := l_cpnt_citems;
2210 x_cpnt_ctype_codes := l_cpnt_ctypes;
2211 x_cpnt_citem_names := l_cpnt_citem_names;
2212 x_cpnt_sort_orders := l_cpnt_sort_orders;
2213 x_object_version_number := l_object_version_number;
2214
2215 -- Standard call to get message count and if count is 1, get message info.
2216
2217 FND_MSG_PUB.count_and_get(
2218 p_count => x_msg_count,
2219 p_data => x_msg_data
2220 );
2221
2222 EXCEPTION
2223 WHEN FND_API.g_exc_error THEN
2224 ROLLBACK;
2225 x_return_status := FND_API.G_RET_STS_ERROR;
2226 FND_MSG_PUB.count_and_get (
2227 p_count => x_msg_count,
2228 p_data => x_msg_data,
2229 p_encoded => FND_API.G_FALSE
2230 );
2231 WHEN FND_API.g_exc_unexpected_error THEN
2232 ROLLBACK;
2233 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2234 FND_MSG_PUB.count_and_get (
2235 p_count => x_msg_count,
2236 p_data => x_msg_data,
2237 p_encoded => FND_API.G_FALSE
2238 );
2239 WHEN OTHERS THEN
2240 ROLLBACK;
2241 x_return_status := FND_API.g_ret_sts_unexp_error;
2242 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
2243 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
2244 END IF;
2245 FND_MSG_PUB.count_and_get (
2246 p_count => x_msg_count,
2247 p_data => x_msg_data,
2248 p_encoded => FND_API.G_FALSE
2249 );
2250
2251
2252 END get_content_item_data;
2253
2254 -----------------------------------------------------------------------
2255 -- PROCEDURE
2256 -- create_cp_image
2257 --
2258 -- PURPOSE
2259 -- Create the CP_IMAGE Content Item.
2260 --
2261 -- NOTES
2262 -- 1. The required input is as follows:
2266 -- Two Arrays : one with all the attribute type codes for CP_IMAGE.
2263 -- Content Type Code for the Section. This must be CP_IMAGE.
2264 -- Content Type Name. (This is the same as Section Name when this item is created in the context of a parent content item).
2265 -- Deliverable ID.
2267 -- second with the corresponding values for CP_IMAGE.
2268 -- 2. The optional input is as follows:
2269 -- Description.
2270 -- Attribute Type Code for the Parent's Section.
2271 -- The Content Item Version ID of the Parent Content Item
2272 -- The Content Type Code associated with the Parent Content Item.
2273 -- If the above two are available, this procedure will create a
2274 -- compound relation between the Parent Content Item Version ID and
2275 -- the Content Item ID of the newly created Content Item.
2276 -- Attachment File Id of the newly uploaded binary file.
2277 -- Attachment File Name for the same.
2278 -- Two Arrays : one with the attribute type codes for IMAGE.
2279 -- second with the corresponding values for IMAGE.
2280 -- If the above four are available, this procedure will create a Content Item
2281 -- of type IMAGE (the OCM's IMAGE) first and use the content_item_id of
2282 -- this content item for CP_IMAGE.
2283 -- If the above two are unavailable, the content_item_id of the IMAGE content item
2284 -- referred to by this CP_IMAGE must be provided.
2285 -- 3. This procedure performs the following steps:
2286 -- 1. Create the IMAGE content item if necessary. It will call the bulk-insert
2287 -- procedure for this task. The IMAGE content item is marked as APPROVED
2288 -- upon creation.
2289 -- 2. Create the CP_IMAGE content item using the bulk-insert call. This item
2290 -- however is not marked as APPROVED.
2291 -- NOTE that the FUNCTIONAL_TYPE for CP_IMAGE items is NORMAL.
2292 -- 3. If the details of Parent Content Item are available,
2293 -- create the compound relation between the parent content item and the
2294 -- newly created CP_IMAGE item.
2295 -- 4. This procedure returns the fact that it is successful.
2296 -- It also returns the citem_id and citem_ver_id for the newly created CP_IMAGE item.
2297 --
2298 -- HISTORY
2299 -- 17-FEB-2002 gdeodhar Created.
2300 --
2301 -----------------------------------------------------------------------
2302 PROCEDURE create_cp_image(
2303 p_content_type_code IN VARCHAR2,
2304 p_content_item_name IN VARCHAR2,
2305 p_description IN VARCHAR2,
2306 p_delv_id IN NUMBER,
2307 p_resource_id IN NUMBER,
2308 p_resource_type IN VARCHAR2,
2309 p_reusable_flag IN VARCHAR2 DEFAULT FND_API.g_true, -- CHANGE to Y or N when IBC folks change the conventions for varchar2 fields.
2310 p_attr_types_cp_image IN JTF_VARCHAR2_TABLE_100 DEFAULT NULL,
2311 p_attr_values_cp_image IN JTF_VARCHAR2_TABLE_4000 DEFAULT NULL,
2312 p_attach_file_id IN NUMBER,
2313 p_attach_file_name IN VARCHAR2,
2314 p_attr_types_image IN JTF_VARCHAR2_TABLE_100 DEFAULT NULL,
2315 p_attr_values_image IN JTF_VARCHAR2_TABLE_4000 DEFAULT NULL,
2316 p_parent_attr_type_code IN VARCHAR2,
2317 p_parent_citem_id IN NUMBER,
2318 p_parent_citem_ver_id IN NUMBER,
2319 p_parent_ctype_code IN VARCHAR2,
2320 p_commit IN VARCHAR2 DEFAULT FND_API.g_false,
2321 p_api_version IN NUMBER DEFAULT 1.0,
2322 p_api_validation_level IN NUMBER DEFAULT FND_API.g_valid_level_full,
2323 x_cp_image_citem_id OUT NUMBER,
2324 x_cp_image_citem_ver_id OUT NUMBER,
2325 x_return_status OUT VARCHAR2,
2326 x_msg_count OUT NUMBER,
2327 x_msg_data OUT VARCHAR2
2328 )
2329 IS
2330 --
2331 -- Declare the local variables and cursors here.
2332 -- Cursor to select the Deliverable Details to record in the Content Item Data.
2333 --
2334 CURSOR c_delv_details IS
2335 SELECT actual_avail_from_date
2336 ,actual_avail_to_date
2337 FROM ams_deliverables_vl
2338 WHERE deliverable_id = p_delv_id ;
2339 --
2340 l_start_date DATE ;
2341 l_end_date DATE ;
2342 --
2343 l_citem_ver_id NUMBER ;
2344 l_citem_id NUMBER ;
2345 l_return_status VARCHAR2(1) ;
2346 l_msg_count NUMBER ;
2347 l_msg_data VARCHAR2(2000) ;
2348 --
2349 l_obj_ver_num NUMBER ;
2350 --
2351 l_image_citem_id NUMBER ;
2352 l_image_citem_ver_id NUMBER ;
2353 l_image_obj_ver_num NUMBER ;
2354 l_created_image VARCHAR2(1) := FND_API.g_false;
2355 --
2356 l_attribute_type_codes JTF_VARCHAR2_TABLE_100 := JTF_VARCHAR2_TABLE_100() ;
2357 l_attributes JTF_VARCHAR2_TABLE_4000 := JTF_VARCHAR2_TABLE_4000() ;
2358 l_citem_ids JTF_NUMBER_TABLE := JTF_NUMBER_TABLE() ;
2359 l_dummy_sort_order JTF_NUMBER_TABLE := JTF_NUMBER_TABLE() ;
2360 l_citem_attrs JTF_VARCHAR2_TABLE_100 := JTF_VARCHAR2_TABLE_100() ;
2364 --
2361 --
2362 l_api_name CONSTANT VARCHAR2(30) := 'Create_CP_Image';
2363 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
2365 l_err_msg VARCHAR2(4000);
2366 l_init_msg_list VARCHAR2(1) := FND_API.g_true;
2367 --
2368 l_api_version_number CONSTANT NUMBER := 1.0;
2369 --
2370 BEGIN
2371 --
2372 -- Standard Start of API savepoint
2373 SAVEPOINT create_cp_image_PVT ;
2374
2375 -- Standard call to check for call compatibility.
2376 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
2377 p_api_version,
2378 l_api_name,
2379 G_PKG_NAME)
2380 THEN
2381 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2382 END IF;
2383
2384 -- Initialize message list if p_init_msg_list is set to TRUE.
2385
2386 --l_init_msg_list := p_init_msg_list;
2387
2388 IF FND_API.to_Boolean( l_init_msg_list )
2389 THEN
2390 FND_MSG_PUB.initialize;
2391 END IF;
2392
2393 l_init_msg_list := FND_API.g_false;
2394
2395 -- Debug Message
2396 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
2397
2398 -- Initialize API return status to SUCCESS
2399 x_return_status := FND_API.G_RET_STS_SUCCESS;
2400 l_return_status := FND_API.G_RET_STS_SUCCESS;
2401 --
2402 -- This procedure will create a new Content Item in IBC Schema.
2403
2404
2405 -- Fetch the Deliverable Details.
2406 OPEN c_delv_details;
2407 FETCH c_delv_details INTO l_start_date, l_end_date;
2408 CLOSE c_delv_details;
2409 --
2410 -- push this data to FND_Messages so that we see it in the JSPs as well.
2411 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name, 'Data for IMAGE');
2412 IF p_attr_types_image IS NOT NULL
2413 AND
2414 p_attr_values_image IS NOT NULL
2415 THEN
2416 FOR i IN p_attr_types_image.first .. p_attr_types_image.last
2417 LOOP
2418 l_err_msg := i || ' : >' || p_attr_types_image(i) || '< : >' || p_attr_values_image(i) || '<';
2419 AMS_UTILITY_PVT.debug_message(l_err_msg);
2420 END LOOP;
2421 END IF;
2422 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name, 'Data for CP_IMAGE');
2423 FOR i IN p_attr_types_cp_image.first .. p_attr_types_cp_image.last
2424 LOOP
2425 l_err_msg := i || ' : >' || p_attr_types_cp_image(i) || '< : >' || p_attr_values_cp_image(i) || '<';
2426 AMS_UTILITY_PVT.debug_message(l_err_msg);
2427 END LOOP;
2428 l_init_msg_list := FND_API.g_false;
2429
2430
2431 -- Check if we need to create the IMAGE content type.
2432 IF p_attach_file_id IS NOT NULL
2433 AND
2434 p_attach_file_name IS NOT NULL
2435 AND
2436 p_attr_types_image IS NOT NULL
2437 AND
2438 p_attr_values_image IS NOT NULL
2439 THEN
2440 -- IMAGE content type has the following attributes:
2441 /*
2442 attr_type data_type min_inst max_inst data_length
2443 ALT_TEXT string 0 1
2444 ATTACHMENT attachment 0 1
2445 DESCRIPTION string 1 1 2000
2446 HEIGHT decimal 0 1
2447 IMAGE_TYPE string 0 1
2448 LINK url 0 1
2449 NAME string 1 1 240
2450 WIDTH decimal 0 1
2451 */
2452 -- Create the content item of type IMAGE.
2453 -- Call bulk_insert procedure.
2454 AMS_UTILITY_PVT.debug_message('file id = ' || p_attach_file_id);
2455 AMS_UTILITY_PVT.debug_message('file name = ' || p_attach_file_name);
2456 AMS_UTILITY_PVT.debug_message('start date = ' || l_start_date);
2457
2458 -- Call the procedure upsert_item. This method allows creation of Content Item from one
2459 -- single API.
2460
2461 -- The new IBC ARU added validations to check if the value of the incoming
2462 -- boolean attributes is T or F. It appears that it cannot be NULL.
2463 -- Added the following piece of code that makes the boolean values as 'F'
2464 -- if incoming values are null.
2465 /*
2466 FOR i IN p_attr_types_image.first .. p_attr_types_image.last
2467 LOOP
2468 -- If the associated value is NULL for the boolean type of attributes,
2469 -- make the value 'F'
2470 -- Currently hardcoding the attribute type codes as we know which attributes
2471 -- of IBC_IMAGE are of type boolean.
2472 IF p_attr_values_image(i) = 'F'
2473 ||
2474 p_attr_values_image(i) = 'T'
2475 THEN
2476 null;
2477 -- do not change the data.
2478 ELSE
2479 -- Check if the attribute is one of the boolean types for IBC_IMAGE and
2480 -- make the value 'F'.
2481 IF p_attr_types_image(i) = ''
2482 END IF;
2483
2484 END LOOP;
2485 */
2486
2487 IBC_CITEM_ADMIN_GRP.upsert_item(
2488 p_ctype_code => G_IBC_IMAGE
2489 ,p_citem_name => p_attach_file_name
2490 ,p_citem_description => p_attach_file_name -- Currently we do not expose the description on the UI, however the IBC_IMAGE type says that DESCRIPTION is a required field. So sending the file name as description.
2494 ,p_reference_code => NULL -- Why is this needed?
2491 ,p_dir_node_id => G_AMS_DIR_NODE_ID
2492 ,p_owner_resource_id => p_resource_id
2493 ,p_owner_resource_type => p_resource_type
2495 ,p_trans_required => FND_API.g_false -- This is the default value. For now we do not expose this flag on the UI.
2496 ,p_parent_item_id => NULL -- There is no parent for the item of type IBC_IMAGE.
2497 ,p_lock_flag => g_lock_flag_value
2498 ,p_wd_restricted => g_wd_restricted_flag_value
2499 ,p_start_date => sysdate -- use the sysdate as the start date for the IMAGE content item.
2500 ,p_end_date => NULL -- Leave the end date as NULL. The idea is to allow the usage of this basic IBC_IMAGE item indefinitely.
2501 ,p_attach_file_id => p_attach_file_id
2502 ,p_attribute_type_codes => p_attr_types_image
2503 ,p_attributes => p_attr_values_image
2504 ,p_component_citems => NULL
2505 ,p_component_atypes => NULL
2506 ,p_sort_order => NULL
2507 ,p_status => G_CITEM_WIP_STATUS_CODE -- Soon after the content item of type IBC_IMAGE is created successfully, we will go ahead and approve this item.
2508 ,p_log_action => FND_API.g_true -- This to be sent as TRUE. It updates the Audit Logs.
2509 ,p_commit => FND_API.g_false -- We still have to do some more operations.
2510 ,p_api_version_number => p_api_version
2511 ,p_init_msg_list => l_init_msg_list
2512 ,px_content_item_id => l_image_citem_id
2513 ,px_citem_ver_id => l_image_citem_ver_id
2514 ,px_object_version_number => l_image_obj_ver_num
2515 ,x_return_status => l_return_status
2516 ,x_msg_count => l_msg_count
2517 ,x_msg_data => l_msg_data
2518
2519 );
2520
2521
2522 AMS_UTILITY_PVT.debug_message('After upsert_item of type IBC_IMAGE');
2523 AMS_UTILITY_PVT.debug_message('l_citem_id = ' || l_image_citem_id );
2524 AMS_UTILITY_PVT.debug_message('l_citem_ver_id = ' || l_image_citem_ver_id );
2525 AMS_UTILITY_PVT.debug_message('l_obj_ver_num = ' || l_image_obj_ver_num );
2526 AMS_UTILITY_PVT.debug_message('l_return_status = ' || l_return_status);
2527
2528 IF FND_API.g_ret_sts_success <> l_return_status
2529 THEN
2530 AMS_Utility_PVT.Error_Message('AMS_ERR_CREATE_IMAGE_ITEM');
2531 RAISE FND_API.g_exc_error;
2532 ELSE
2533 l_created_image := FND_API.g_true;
2534 END IF;
2535
2536 l_init_msg_list := FND_API.g_false;
2537
2538 -- Approve this content item of type IBC_IMAGE.
2539
2540 IBC_CITEM_ADMIN_GRP.approve_item(
2541 p_citem_ver_id => l_image_citem_ver_id
2542 ,p_commit => FND_API.g_false
2543 ,p_api_version_number => p_api_version
2544 ,p_init_msg_list => l_init_msg_list
2545 ,px_object_version_number => l_image_obj_ver_num
2546 ,x_return_status => l_return_status
2547 ,x_msg_count => l_msg_count
2548 ,x_msg_data => l_msg_data
2549 );
2550
2551 IF FND_API.g_ret_sts_success <> l_return_status
2552 THEN
2553 AMS_Utility_PVT.Error_Message('AMS_ERR_APPROVE_CITEM');
2554 RAISE FND_API.g_exc_error;
2555 END IF;
2556
2557 END IF;
2558
2559 -- If we reach till here we can proceed with creation of CP_IMAGE item.
2560
2561 l_attribute_type_codes := p_attr_types_cp_image;
2562 l_attributes := p_attr_values_cp_image;
2563
2564 -- Check if there was a newly created image.
2565 -- If so, we have to set the two attributes to the array of CP_IMAGE attributes.
2566 -- One for attachment_file_id and one for ocm_image_id.
2567 IF l_created_image = FND_API.g_true
2568 THEN
2569 -- Substitute the values for the above two attributes.
2570 FOR i IN l_attribute_type_codes.first .. l_attribute_type_codes.last
2571 LOOP
2572 IF l_attribute_type_codes(i) = 'ATTACHMENT_FILE_ID'
2573 THEN
2574 l_attributes(i) := NULL; -- set it to NULL. The Runtime code picks up the latest file id.
2575 END IF;
2576 IF l_attribute_type_codes(i) = 'OCM_IMAGE_ID'
2577 THEN
2578 l_attributes(i) := l_image_citem_id;
2579 END IF;
2580 END LOOP;
2581 END IF;
2582
2583 -- Create the CP_IMAGE item.
2584 -- Call upsert_item.
2585
2586 -- Call the procedure upsert_item. This method allows creation of Content Item from one
2587 -- single API.
2588
2589 IBC_CITEM_ADMIN_GRP.upsert_item(
2590 p_ctype_code => G_CP_IMAGE
2591 ,p_citem_name => p_content_item_name
2592 ,p_citem_description => NULL -- currently we do not expose the description on the UI.
2593 ,p_dir_node_id => G_AMS_DIR_NODE_ID
2594 ,p_owner_resource_id => p_resource_id
2595 ,p_owner_resource_type => p_resource_type
2596 ,p_reference_code => NULL -- Why is this needed?
2597 ,p_trans_required => FND_API.g_false -- This is the default value. For now we do not expose this flag on the UI.
2598 ,p_parent_item_id => p_parent_citem_id -- Specify the parent content item id. This item is visible only in the context of this parent.
2599 ,p_lock_flag => g_lock_flag_value
2603 ,p_attach_file_id => NULL -- Note that CP_IMAGE item does not have any attachment.
2600 ,p_wd_restricted => g_wd_restricted_flag_value
2601 ,p_start_date => l_start_date
2602 ,p_end_date => l_end_date
2604 ,p_attribute_type_codes => l_attribute_type_codes
2605 ,p_attributes => l_attributes
2606 ,p_component_citems => NULL
2607 ,p_component_atypes => NULL
2608 ,p_sort_order => NULL
2609 ,p_status => G_CITEM_WIP_STATUS_CODE -- We will approve the underlying content items when the deliverable gets approved.
2610 ,p_log_action => FND_API.g_true -- This to be sent as TRUE. It updates the Audit Logs.
2611 ,p_commit => FND_API.g_false -- We still have to do some more operations.
2612 ,p_api_version_number => p_api_version
2613 ,p_init_msg_list => l_init_msg_list
2614 ,px_content_item_id => l_citem_id
2615 ,px_citem_ver_id => l_citem_ver_id
2616 ,px_object_version_number => l_obj_ver_num
2617 ,x_return_status => l_return_status
2618 ,x_msg_count => l_msg_count
2619 ,x_msg_data => l_msg_data
2620
2621 );
2622
2623 AMS_UTILITY_PVT.debug_message('After upsert_item of type CP_IMAGE');
2624 AMS_UTILITY_PVT.debug_message('l_citem_id = ' || l_citem_id );
2625 AMS_UTILITY_PVT.debug_message('l_citem_ver_id = ' || l_citem_ver_id );
2626 AMS_UTILITY_PVT.debug_message('l_obj_ver_num = ' || l_obj_ver_num );
2627 AMS_UTILITY_PVT.debug_message('l_return_status = ' || l_return_status);
2628
2629 IF FND_API.g_ret_sts_success <> l_return_status
2630 THEN
2631 AMS_Utility_PVT.Error_Message('AMS_ERR_CREATE_CP_IMAGE');
2632 RAISE FND_API.g_exc_error;
2633 END IF;
2634
2635 l_init_msg_list := FND_API.g_false;
2636
2637 -- We do not Approve the CP_IMAGE Item at this stage.
2638 -- When the deliverable gets approved and the status becomes active, we will approve all the underlying content items.
2639 --
2640
2641 -- If we reach till here, we have created the CP_IMAGE correctly.
2642 -- Add the Compound Relation with it's parent if necessary.
2643
2644 IF p_parent_citem_ver_id IS NOT NULL
2645 AND
2646 p_parent_ctype_code IS NOT NULL
2647 AND
2648 p_parent_attr_type_code IS NOT NULL
2649 THEN
2650 -- prepare the data for insert.
2651 l_citem_attrs.extend();
2652 l_citem_attrs(1) := p_parent_attr_type_code;
2653 --
2654 l_citem_ids.extend();
2655 l_citem_ids(1) := l_citem_id;
2656 --
2657 l_dummy_sort_order.extend();
2658 l_dummy_sort_order(1) := 1;
2659 --
2660 AMS_UTILITY_PVT.debug_message('parent_citem_ver_id = ' || p_parent_citem_ver_id);
2661 AMS_UTILITY_PVT.debug_message('parent_ctype_code = ' || p_parent_ctype_code);
2662 AMS_UTILITY_PVT.debug_message('citem_attr = ' || l_citem_attrs(1));
2663 AMS_UTILITY_PVT.debug_message('citem_id = ' || l_citem_ids(1));
2664
2665 IBC_CITEM_ADMIN_GRP.insert_components(
2666 p_citem_ver_id => p_parent_citem_ver_id
2667 ,p_content_item_ids => l_citem_ids
2668 ,p_attribute_type_codes => l_citem_attrs
2669 ,p_sort_order => l_dummy_sort_order -- The NULL does not work. -- The new API is supposed to be able to take NULL for this parameter.
2670 ,p_commit => FND_API.g_false
2674 ,x_msg_count => l_msg_count
2671 ,p_api_version_number => p_api_version
2672 ,p_init_msg_list => l_init_msg_list
2673 ,x_return_status => l_return_status
2675 ,x_msg_data => l_msg_data
2676 );
2677
2678 END IF;
2679
2680 IF FND_API.g_ret_sts_success <> l_return_status
2681 THEN
2682 AMS_Utility_PVT.Error_Message('AMS_ERR_ADD_COMPOUND_REL');
2683 RAISE FND_API.g_exc_error;
2684 END IF;
2685
2686 -- If we reach till here, we can unlock the IMAGE item and then
2687 -- unlock the CP_IMAGE item.
2688
2689 IF l_created_image = FND_API.g_true
2690 THEN
2691
2692 IF g_using_locking = FND_API.g_true
2693 THEN
2694
2695 IBC_CITEM_ADMIN_GRP.unlock_item(
2696 p_content_item_id => l_image_citem_id
2697 ,p_commit => g_commit_on_lock_unlock
2698 ,p_api_version_number => p_api_version
2699 ,p_init_msg_list => l_init_msg_list
2700 ,x_return_status => l_return_status
2701 ,x_msg_count => l_msg_count
2702 ,x_msg_data => l_msg_data
2703 );
2704
2705 AMS_UTILITY_PVT.debug_message('After Unlock.');
2706 AMS_UTILITY_PVT.debug_message('l_return_status = ' || l_return_status);
2707
2708 --
2709 IF FND_API.g_ret_sts_success <> l_return_status
2710 THEN
2711 AMS_Utility_PVT.Error_Message('AMS_ERR_UNLOCK_IMAGE');
2712 RAISE FND_API.g_exc_error;
2713 END IF;
2714
2715 END IF;
2716 --
2717 END IF;
2718
2719 IF g_using_locking = FND_API.g_true
2720 THEN
2721
2722 IBC_CITEM_ADMIN_GRP.unlock_item(
2723 p_content_item_id => l_citem_id
2724 ,p_commit => g_commit_on_lock_unlock
2725 ,p_api_version_number => p_api_version
2726 ,p_init_msg_list => l_init_msg_list
2727 ,x_return_status => l_return_status
2728 ,x_msg_count => l_msg_count
2729 ,x_msg_data => l_msg_data
2730 );
2731
2732 AMS_UTILITY_PVT.debug_message('After Unlock.');
2733 AMS_UTILITY_PVT.debug_message('l_return_status = ' || l_return_status);
2734
2735 --
2736 IF FND_API.g_ret_sts_success <> l_return_status
2737 THEN
2738 AMS_Utility_PVT.Error_Message('AMS_ERR_UNLOCK_CP_IMAGE');
2739 RAISE FND_API.g_exc_error;
2740 END IF;
2741
2742 END IF;
2743
2744 -- If we come till here, everything has been created successfully.
2745 -- Commit the work and set the output values.
2746
2747 -- Standard check for p_commit
2748 IF FND_API.to_Boolean( p_commit )
2749 THEN
2750 COMMIT WORK;
2751 END IF;
2752
2753 x_cp_image_citem_id := l_citem_id;
2754 x_cp_image_citem_ver_id := l_citem_ver_id;
2755 x_return_status := l_return_status;
2756
2757 -- Standard call to get message count and if count is 1, get message info.
2758 FND_MSG_PUB.count_and_get(
2759 p_count => x_msg_count,
2760 p_data => x_msg_data
2761 );
2762
2763 EXCEPTION
2764 WHEN FND_API.g_exc_error THEN
2765 ROLLBACK TO create_cp_image_PVT ;
2766 x_return_status := FND_API.G_RET_STS_ERROR;
2767 FND_MSG_PUB.count_and_get (
2768 p_count => x_msg_count,
2769 p_data => x_msg_data,
2770 p_encoded => FND_API.G_FALSE
2771 );
2772 WHEN FND_API.g_exc_unexpected_error THEN
2773 ROLLBACK TO create_cp_image_PVT ;
2777 p_data => x_msg_data,
2774 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2775 FND_MSG_PUB.count_and_get (
2776 p_count => x_msg_count,
2778 p_encoded => FND_API.G_FALSE
2779 );
2780 WHEN OTHERS THEN
2781 ROLLBACK TO create_cp_image_PVT ;
2782 x_return_status := FND_API.g_ret_sts_unexp_error;
2783 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
2784 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
2785 END IF;
2786 FND_MSG_PUB.count_and_get (
2787 p_count => x_msg_count,
2788 p_data => x_msg_data,
2789 p_encoded => FND_API.G_FALSE
2790 );
2791 --
2792 END create_cp_image;
2793 --
2794
2795
2796 -----------------------------------------------------------------------
2797 -- PROCEDURE
2798 -- update_cp_image
2799 --
2800 -- PURPOSE
2801 -- Update the CP_IMAGE Content Item.
2802 --
2803 -- NOTES
2804 -- 1. The required input is as follows:
2805 -- Content Type Code for the Section. This must be CP_IMAGE.
2806 -- Content Item Id for the CP_IMAGE item.
2807 -- Content Item Name. This is same as the Section Name.
2808 -- Content Item Version Id for the CP_IMAGE item.
2809 -- Deliverable ID.
2810 -- Two Arrays : one with all the attribute type codes for CP_IMAGE.
2811 -- second with the corresponding values for CP_IMAGE.
2812 -- 2. The optional input is as follows:
2813 -- Description.
2814 -- Attachment File Id of the newly uploaded binary file.
2815 -- Attachment File Name for the same.
2816 -- Two Arrays : one with the attribute type codes for IMAGE.
2817 -- second with the corresponding values for IMAGE.
2818 -- If the above four are available, this procedure will create a Content Item
2819 -- of type IMAGE (the OCM's IMAGE) first and use the content_item_id of
2820 -- this content item for CP_IMAGE.
2821 -- If the above two are unavailable, the content_item_id of the IMAGE content item
2822 -- referred to by this CP_IMAGE must be provided.
2823 -- 3. This procedure performs the following steps:
2824 -- 1. Create the IMAGE content item if necessary. It will call the bulk-insert
2825 -- procedure for this task. The IMAGE content item is marked as APPROVED
2826 -- upon creation.
2827 -- 2. Update the CP_IMAGE content item using the following calls:
2828 -- set_citem_att_bundle (with all the attributes with the changed values).
2829 -- NOTE that the FUNCTIONAL_TYPE for CP_IMAGE items is NORMAL.
2830 -- NOTE that we will not call the following for now:
2831 -- set_citem_meta will not be called as none of the meta items are exposed
2832 -- in the UI for CP_IMAGE.
2833 -- update_citem_basic will not be called as we do not expose Description
2834 -- in the UI and we do not allow the change of the Name.
2835 -- 4. This procedure returns the fact that it is successful.
2836 --
2837 -- HISTORY
2838 -- 19-FEB-2002 gdeodhar Created.
2839 --
2840 -----------------------------------------------------------------------
2841 PROCEDURE update_cp_image(
2842 p_content_type_code IN VARCHAR2,
2843 p_content_item_name IN VARCHAR2,
2844 p_cp_image_citem_id IN NUMBER,
2845 p_cp_image_citem_ver_id IN NUMBER,
2846 p_delv_id IN NUMBER,
2847 p_resource_id IN NUMBER,
2848 p_resource_type IN VARCHAR2,
2849 p_attr_types_cp_image IN JTF_VARCHAR2_TABLE_100 DEFAULT NULL,
2850 p_attr_values_cp_image IN JTF_VARCHAR2_TABLE_4000 DEFAULT NULL,
2851 p_description IN VARCHAR2,
2852 p_attach_file_id IN NUMBER,
2853 p_attach_file_name IN VARCHAR2,
2854 p_attr_types_image IN JTF_VARCHAR2_TABLE_100 DEFAULT NULL,
2855 p_attr_values_image IN JTF_VARCHAR2_TABLE_4000 DEFAULT NULL,
2856 p_commit IN VARCHAR2 DEFAULT FND_API.g_false,
2857 p_api_version IN NUMBER DEFAULT 1.0,
2858 p_api_validation_level IN NUMBER DEFAULT FND_API.g_valid_level_full,
2859 x_return_status OUT VARCHAR2,
2860 x_msg_count OUT NUMBER,
2861 x_msg_data OUT VARCHAR2
2862 )
2863 IS
2864 --
2865 -- Declare the local variables and cursors here.
2866 --
2867 l_return_status VARCHAR2(1) ;
2868 l_msg_count NUMBER ;
2869 l_msg_data VARCHAR2(2000) ;
2870 --
2871 l_obj_ver_num NUMBER ;
2872 --
2873 l_image_citem_id NUMBER ;
2874 l_image_citem_ver_id NUMBER ;
2875 l_cp_image_citem_ver_id NUMBER ;
2876 l_image_obj_ver_num NUMBER ;
2877 l_created_image VARCHAR2(1) := FND_API.g_false;
2878 --
2879 l_attribute_type_codes JTF_VARCHAR2_TABLE_100 := JTF_VARCHAR2_TABLE_100() ;
2880 l_attributes JTF_VARCHAR2_TABLE_4000 := JTF_VARCHAR2_TABLE_4000() ;
2881 l_citem_ids JTF_NUMBER_TABLE := JTF_NUMBER_TABLE() ;
2882 l_dummy_sort_order JTF_NUMBER_TABLE := JTF_NUMBER_TABLE() ;
2883 l_citem_attrs JTF_VARCHAR2_TABLE_100 := JTF_VARCHAR2_TABLE_100() ;
2884 --
2885 l_api_name CONSTANT VARCHAR2(30) := 'Update_CP_Image';
2886 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
2887 --
2891 --
2888 l_init_msg_list VARCHAR2(1) := FND_API.g_true;
2889 --
2890 l_api_version_number CONSTANT NUMBER := 1.0;
2892 BEGIN
2893 --
2894 -- Standard Start of API savepoint
2895 SAVEPOINT update_cp_image_PVT ;
2896
2897 -- Standard call to check for call compatibility.
2898 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
2899 p_api_version,
2900 l_api_name,
2901 G_PKG_NAME)
2902 THEN
2903 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2904 END IF;
2905
2906 -- Initialize message list if p_init_msg_list is set to TRUE.
2907
2908 --l_init_msg_list := p_init_msg_list;
2909
2910 IF FND_API.to_Boolean( l_init_msg_list )
2911 THEN
2912 FND_MSG_PUB.initialize;
2913 END IF;
2914
2915
2916 l_init_msg_list := FND_API.g_false;
2917
2918
2919 -- Debug Message
2920 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
2921
2922 -- Initialize API return status to SUCCESS
2923 x_return_status := FND_API.G_RET_STS_SUCCESS;
2924 l_return_status := FND_API.G_RET_STS_SUCCESS;
2925 --
2926 --
2927 -- print the contents of the two arrays for Debug.
2928 IF p_attr_types_image IS NOT NULL
2929 AND
2930 p_attr_values_image IS NOT NULL
2931 THEN
2932 FOR i IN p_attr_types_image.first .. p_attr_types_image.last
2933 LOOP
2934 AMS_UTILITY_PVT.debug_message(i || ' : ' || p_attr_types_image(i) || ' : ' || p_attr_values_image(i));
2935 null;
2936 END LOOP;
2937 END IF;
2938 IF p_attr_types_cp_image IS NOT NULL
2939 AND
2940 p_attr_values_cp_image IS NOT NULL
2941 THEN
2942 FOR i IN p_attr_types_cp_image.first .. p_attr_types_cp_image.last
2943 LOOP
2944 AMS_UTILITY_PVT.debug_message(i || ' : ' || p_attr_types_cp_image(i) || ' : ' || p_attr_values_cp_image(i));
2945 null;
2946 END LOOP;
2947 END IF;
2948
2949 -- Check if we need to create the IMAGE content type.
2950 IF p_attach_file_id IS NOT NULL
2951 AND
2952 p_attach_file_name IS NOT NULL
2953 AND
2954 p_attr_types_image IS NOT NULL
2955 AND
2956 p_attr_values_image IS NOT NULL
2957 THEN
2958 -- IMAGE content type has the following attributes:
2959 /*
2960 attr_type data_type min_inst max_inst data_length
2961 ALT_TEXT string 0 1
2962 ATTACHMENT attachment 0 1
2963 DESCRIPTION string 1 1 2000
2964 HEIGHT decimal 0 1
2965 IMAGE_TYPE string 0 1
2966 LINK url 0 1
2967 NAME string 1 1 240
2968 WIDTH decimal 0 1
2969 */
2970 -- Create the content item of type IMAGE.
2974 AMS_UTILITY_PVT.debug_message('file name = ' || p_attach_file_name);
2971 -- Call bulk_insert procedure.
2972 /*
2973 AMS_UTILITY_PVT.debug_message('file id = ' || p_attach_file_id);
2975 */
2976
2977 -- Call the procedure upsert_item. This method allows creation of Content Item from one
2978 -- single API.
2979
2980 IBC_CITEM_ADMIN_GRP.upsert_item(
2981 p_ctype_code => G_IBC_IMAGE
2982 ,p_citem_name => p_attach_file_name
2983 ,p_citem_description => p_attach_file_name -- Currently we do not expose the description on the UI, however the IBC_IMAGE type says that DESCRIPTION is a required field. So sending the file name as description.
2984 ,p_dir_node_id => G_AMS_DIR_NODE_ID
2985 ,p_owner_resource_id => p_resource_id
2986 ,p_owner_resource_type => p_resource_type
2987 ,p_reference_code => NULL -- Why is this needed?
2988 ,p_trans_required => FND_API.g_false -- This is the default value. For now we do not expose this flag on the UI.
2989 ,p_parent_item_id => NULL -- There is no parent for the item of type IBC_IMAGE.
2990 ,p_lock_flag => g_lock_flag_value
2991 ,p_wd_restricted => g_wd_restricted_flag_value
2992 ,p_start_date => sysdate -- use the sysdate as the start date for the IMAGE content item.
2993 ,p_end_date => NULL -- Leave the end date as NULL. The idea is to allow the usage of this basic IBC_IMAGE item indefinitely.
2994 ,p_attach_file_id => p_attach_file_id
2995 ,p_attribute_type_codes => p_attr_types_image
2996 ,p_attributes => p_attr_values_image
2997 ,p_component_citems => NULL
2998 ,p_component_atypes => NULL
2999 ,p_sort_order => NULL
3000 ,p_status => G_CITEM_WIP_STATUS_CODE -- Soon after the content item of type IBC_IMAGE is created successfully, we will go ahead and approve this item.
3001 ,p_log_action => FND_API.g_true -- This to be sent as TRUE. It updates the Audit Logs.
3002 ,p_commit => FND_API.g_false -- We still have to do some more operations.
3003 ,p_api_version_number => p_api_version
3004 ,p_init_msg_list => l_init_msg_list
3005 ,px_content_item_id => l_image_citem_id
3006 ,px_citem_ver_id => l_image_citem_ver_id
3007 ,px_object_version_number => l_image_obj_ver_num
3008 ,x_return_status => l_return_status
3009 ,x_msg_count => l_msg_count
3010 ,x_msg_data => l_msg_data
3011
3012 );
3013
3014 AMS_UTILITY_PVT.debug_message('After upsert_item of type IBC_IMAGE');
3015 AMS_UTILITY_PVT.debug_message('l_citem_id = ' || l_image_citem_id );
3016 AMS_UTILITY_PVT.debug_message('l_citem_ver_id = ' || l_image_citem_ver_id );
3017 AMS_UTILITY_PVT.debug_message('l_obj_ver_num = ' || l_image_obj_ver_num );
3018 AMS_UTILITY_PVT.debug_message('l_return_status = ' || l_return_status);
3019
3020 IF FND_API.g_ret_sts_success <> l_return_status
3021 THEN
3022 AMS_Utility_PVT.Error_Message('AMS_ERR_CREATE_IMAGE_ITEM');
3023 RAISE FND_API.g_exc_error;
3024 ELSE
3025 l_created_image := FND_API.g_true;
3026 END IF;
3027
3028 l_init_msg_list := FND_API.g_false;
3029
3030 -- Approve this content item of type IBC_IMAGE.
3031
3032 IBC_CITEM_ADMIN_GRP.approve_item(
3033 p_citem_ver_id => l_image_citem_ver_id
3034 ,p_commit => FND_API.g_false
3035 ,p_api_version_number => p_api_version
3036 ,p_init_msg_list => l_init_msg_list
3037 ,px_object_version_number => l_image_obj_ver_num
3038 ,x_return_status => l_return_status
3039 ,x_msg_count => l_msg_count
3040 ,x_msg_data => l_msg_data
3041 );
3042
3043 IF FND_API.g_ret_sts_success <> l_return_status
3044 THEN
3045 AMS_Utility_PVT.Error_Message('AMS_ERR_APPROVE_CITEM');
3046 RAISE FND_API.g_exc_error;
3047 END IF;
3048
3049 END IF;
3050
3051 -- If we reach till here we can proceed with update of CP_IMAGE item.
3052
3053 l_attribute_type_codes := p_attr_types_cp_image;
3054 l_attributes := p_attr_values_cp_image;
3055
3056 -- Check if there was a newly created image.
3057 -- If so, we have to set the two attributes to the array of CP_IMAGE attributes.
3058 -- One for attachment_file_id and one for ocm_image_id.
3059 IF l_created_image = FND_API.g_true
3060 THEN
3061 -- Substitute the values for the above two attributes.
3062 FOR i IN l_attribute_type_codes.first .. l_attribute_type_codes.last
3063 LOOP
3064 IF l_attribute_type_codes(i) = 'ATTACHMENT_FILE_ID'
3065 THEN
3069 THEN
3066 l_attributes(i) := NULL; -- set it to NULL. The Runtime code picks up the latest file id.
3067 END IF;
3068 IF l_attribute_type_codes(i) = 'OCM_IMAGE_ID'
3070 l_attributes(i) := l_image_citem_id;
3071 END IF;
3072 END LOOP;
3073 END IF;
3074
3075 -- Update the CP_IMAGE item.
3076
3077 -- Call the procedure IBC_CITEM_ADMIN_GRP.set_attribute_bundle.
3078
3079 IF g_using_locking = FND_API.g_true
3080 THEN
3081
3082 -- Note that we have to lock the item first.
3083 IBC_CITEM_ADMIN_GRP.lock_item(
3084 p_content_item_id => p_cp_image_citem_id
3085 ,p_commit => g_commit_on_lock_unlock
3086 ,p_api_version_number => p_api_version
3087 ,p_init_msg_list => l_init_msg_list
3088 ,x_citem_version_id => l_cp_image_citem_ver_id
3089 ,x_object_version_number => l_obj_ver_num
3090 ,x_return_status => l_return_status
3091 ,x_msg_count => l_msg_count
3092 ,x_msg_data => l_msg_data
3093 );
3094
3095 IF FND_API.g_ret_sts_success <> l_return_status
3096 THEN
3097 AMS_Utility_PVT.Error_Message('AMS_ERR_LOCKING_CP_IMAGE');
3098 RAISE FND_API.g_exc_error;
3099 END IF;
3100
3101 IF l_cp_image_citem_ver_id <> p_cp_image_citem_ver_id
3102 THEN
3103 AMS_Utility_PVT.Error_Message('AMS_ERR_CITEM_VER_MISMATCH');
3104 RAISE FND_API.g_exc_error;
3105 END IF;
3106
3107 ELSE
3108
3109 -- We have to get the object version number separately as we are not using locking.
3110 l_obj_ver_num := IBC_CITEM_ADMIN_GRP.getObjVerNum( p_cp_image_citem_id );
3111
3112 END IF;
3113
3114 AMS_Utility_PVT.Debug_Message(' CP IMage citem version id = ' || p_cp_image_citem_ver_id );
3115 AMS_Utility_PVT.Debug_Message(' obj ver num = ' || l_obj_ver_num );
3116
3117 IBC_CITEM_ADMIN_GRP.set_attribute_bundle(
3118 p_citem_ver_id => p_cp_image_citem_ver_id
3119 ,p_attribute_type_codes => l_attribute_type_codes
3120 ,p_attributes => l_attributes -- This has the changed data if IMAGE was created.
3121 ,p_remove_old => FND_API.g_true -- The procedure sets the p_remove_old value to FND_API.g_true by default as well. Sending it in anyway.
3122 ,p_commit => FND_API.g_false -- This is the Default.
3123 ,p_api_version_number => p_api_version
3124 ,p_init_msg_list => l_init_msg_list
3125 ,px_object_version_number => l_obj_ver_num -- This is an IN/OUT parameter in this procedure.
3126 ,x_return_status => l_return_status
3127 ,x_msg_count => l_msg_count
3128 ,x_msg_data => l_msg_data
3129 );
3130
3131 IF FND_API.g_ret_sts_success <> l_return_status
3132 THEN
3133 AMS_Utility_PVT.Error_Message('AMS_ERR_SET_CITEM_ATTRIB');
3134 AMS_Utility_PVT.Error_Message('AMS_ERR_UPDATE_CP_IMAGE');
3135 RAISE FND_API.g_exc_error;
3136 END IF;
3137
3138 -- If we reach till here, we can unlock the IMAGE item and then
3139 -- unlock the CP_IMAGE item.
3140
3141 IF l_created_image = FND_API.g_true
3142 THEN
3143
3144 IF g_using_locking = FND_API.g_true
3145 THEN
3146
3147 IBC_CITEM_ADMIN_GRP.unlock_item(
3148 p_content_item_id => l_image_citem_id
3149 ,p_commit => g_commit_on_lock_unlock
3150 ,p_api_version_number => p_api_version
3151 ,p_init_msg_list => l_init_msg_list
3152 ,x_return_status => l_return_status
3153 ,x_msg_count => l_msg_count
3154 ,x_msg_data => l_msg_data
3155 );
3156
3157 AMS_UTILITY_PVT.debug_message('After Unlock.');
3158 AMS_UTILITY_PVT.debug_message('l_return_status = ' || l_return_status);
3159
3160 --
3161 IF FND_API.g_ret_sts_success <> l_return_status
3162 THEN
3163 AMS_Utility_PVT.Error_Message('AMS_ERR_UNLOCK_IMAGE');
3164 RAISE FND_API.g_exc_error;
3165 END IF;
3166
3167 END IF;
3168 --
3169 END IF;
3170
3171 IF g_using_locking = FND_API.g_true
3172 THEN
3173
3174 IBC_CITEM_ADMIN_GRP.unlock_item(
3175 p_content_item_id => p_cp_image_citem_id
3176 ,p_commit => g_commit_on_lock_unlock
3180 ,x_msg_count => l_msg_count
3177 ,p_api_version_number => p_api_version
3178 ,p_init_msg_list => l_init_msg_list
3179 ,x_return_status => l_return_status
3181 ,x_msg_data => l_msg_data
3182 );
3183
3184 AMS_UTILITY_PVT.debug_message('After Unlock.');
3185 AMS_UTILITY_PVT.debug_message('l_return_status = ' || l_return_status);
3186
3187 --
3188 IF FND_API.g_ret_sts_success <> l_return_status
3189 THEN
3190 AMS_Utility_PVT.Error_Message('AMS_ERR_UNLOCK_CP_IMAGE');
3191 RAISE FND_API.g_exc_error;
3192 END IF;
3193
3194 END IF;
3195
3196 -- Commit the work and set the output values.
3197
3198 -- Standard check for p_commit
3199 IF FND_API.to_Boolean( p_commit )
3200 THEN
3201 COMMIT WORK;
3202 END IF;
3203
3204 x_return_status := l_return_status;
3205
3206 -- Standard call to get message count and if count is 1, get message info.
3207 FND_MSG_PUB.count_and_get(
3208 p_count => x_msg_count,
3209 p_data => x_msg_data
3210 );
3211
3212 EXCEPTION
3213 WHEN FND_API.g_exc_error THEN
3214 ROLLBACK TO update_cp_image_PVT ;
3215 x_return_status := FND_API.G_RET_STS_ERROR;
3216 FND_MSG_PUB.count_and_get (
3217 p_count => x_msg_count,
3218 p_data => x_msg_data,
3219 p_encoded => FND_API.G_FALSE
3220 );
3221 WHEN FND_API.g_exc_unexpected_error THEN
3222 ROLLBACK TO update_cp_image_PVT;
3223 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3224 FND_MSG_PUB.count_and_get (
3225 p_count => x_msg_count,
3226 p_data => x_msg_data,
3227 p_encoded => FND_API.G_FALSE
3228 );
3229 WHEN OTHERS THEN
3230 ROLLBACK TO update_cp_image_PVT ;
3231 x_return_status := FND_API.g_ret_sts_unexp_error;
3232 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
3233 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
3234 END IF;
3235 FND_MSG_PUB.count_and_get (
3236 p_count => x_msg_count,
3237 p_data => x_msg_data,
3238 p_encoded => FND_API.G_FALSE
3239 );
3240
3241
3242 END update_cp_image;
3243
3244 -----------------------------------------------------------------------
3245 -- PROCEDURE
3246 -- get_content_item_attrs
3247 --
3248 -- PURPOSE
3249 -- Wrapper on IBC_CITEM_ADMIN_GRP.get_attribute_bundle
3250 --
3251 -- NOTES
3252 -- 1. The required input is as follows:
3253 -- content_item_id
3254 -- content_type_code
3255 -- content_item_version_id
3256 -- 2. This procedure calls the get_attribute_bundle from IBC_CITEM_ADMIN_GRP package.
3257 -- It only sends the useful data back to the caller.
3258 --
3259 -----------------------------------------------------------------------
3260 PROCEDURE get_content_item_attrs(
3261 p_citem_id IN NUMBER,
3262 p_ctype_code IN VARCHAR2,
3263 p_citem_ver_id IN NUMBER,
3264 p_attrib_file_id IN NUMBER DEFAULT NULL,
3265 p_api_version IN NUMBER,
3266 p_init_msg_list IN VARCHAR2,
3267 x_attribute_type_codes OUT JTF_VARCHAR2_TABLE_100,
3268 x_attribute_type_names OUT JTF_VARCHAR2_TABLE_300,
3269 x_attributes OUT JTF_VARCHAR2_TABLE_4000,
3270 x_return_status OUT VARCHAR2,
3271 x_msg_count OUT NUMBER,
3272 x_msg_data OUT VARCHAR2
3273 )
3274 IS
3275 --
3276 l_attribute_type_codes JTF_VARCHAR2_TABLE_100 ;
3277 l_attribute_type_names JTF_VARCHAR2_TABLE_300 ;
3278 l_attributes JTF_VARCHAR2_TABLE_4000 ;
3279 l_obj_ver_num NUMBER ;
3280 l_return_status VARCHAR2(1) ;
3281 l_msg_count NUMBER ;
3282 l_msg_data VARCHAR2(2000) ;
3283 --
3284 l_api_name CONSTANT VARCHAR2(30) := 'Get_Content_Item_Attrs';
3285 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
3286 --
3287 BEGIN
3288 --
3289 x_return_status := FND_API.g_ret_sts_success;
3290 --
3291
3292 IBC_CITEM_ADMIN_GRP.get_attribute_bundle(
3293 p_citem_ver_id => p_citem_ver_id
3294 ,p_init_msg_list => p_init_msg_list
3295 ,p_api_version_number => p_api_version
3296 ,x_attribute_type_codes => l_attribute_type_codes
3297 ,x_attribute_type_names => l_attribute_type_names
3298 ,x_attributes => l_attributes
3299 ,x_object_version_number => l_obj_ver_num
3300 ,x_return_status => l_return_status
3301 ,x_msg_count => l_msg_count
3302 ,x_msg_data => l_msg_data
3303 );
3304
3305 IF FND_API.g_ret_sts_success <> l_return_status
3306 THEN
3307 AMS_Utility_PVT.Error_Message('AMS_ERR_GET_CITEM_ATTRS');
3308 RAISE FND_API.g_exc_error;
3309 END IF;
3310
3311 x_return_status := l_return_status;
3312 x_attribute_type_codes := l_attribute_type_codes;
3313 x_attribute_type_names := l_attribute_type_names;
3314 x_attributes := l_attributes;
3315
3316 -- Standard call to get message count and if count is 1, get message info.
3317 FND_MSG_PUB.count_and_get(
3318 p_count => x_msg_count,
3319 p_data => x_msg_data
3320 );
3321
3322 EXCEPTION
3323 WHEN FND_API.g_exc_error THEN
3324 ROLLBACK;
3328 p_data => x_msg_data,
3325 x_return_status := FND_API.G_RET_STS_ERROR;
3326 FND_MSG_PUB.count_and_get (
3327 p_count => x_msg_count,
3329 p_encoded => FND_API.G_FALSE
3330 );
3331 WHEN FND_API.g_exc_unexpected_error THEN
3332 ROLLBACK;
3333 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3334 FND_MSG_PUB.count_and_get (
3335 p_count => x_msg_count,
3336 p_data => x_msg_data,
3337 p_encoded => FND_API.G_FALSE
3338 );
3339 WHEN OTHERS THEN
3340 ROLLBACK;
3341 x_return_status := FND_API.g_ret_sts_unexp_error;
3342 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
3343 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
3344 END IF;
3345 FND_MSG_PUB.count_and_get (
3346 p_count => x_msg_count,
3347 p_data => x_msg_data,
3348 p_encoded => FND_API.G_FALSE
3349 );
3350
3351 END get_content_item_attrs;
3352
3353
3354
3355 -----------------------------------------------------------------------
3356 -- PROCEDURE
3357 -- update_content_item
3358 --
3359 -- PURPOSE
3360 -- Update a Content Item with a generic content type.
3361 -- The Content Type must be provided.
3362 --
3363 -- NOTES
3364 -- 1. The required input is as follows:
3365 -- Content Type Code for the Item.
3366 -- Content Item Id for Item.
3367 -- Content Item Version Id for the Item.
3368 -- Two Arrays : one with data for changed attribute codes.
3369 -- second with the corresponding values.
3370 -- 2. The optional input is as follows:
3371 -- Content Item Name for the Item.
3372 -- Description.
3373 -- Attachment File Id for the attachment.
3374 -- Attachment File Name for the same.
3375 -- 3. This procedure performs the following steps:
3376 -- 1. Lock the Content Item.
3377 -- 2. Get the existing Attribute data for the content item.
3378 -- 3. Set the values of the changed Attributes with the incoming data.
3379 -- 4. Set the Attachment File Id if it has been provided as input.
3380 -- 5. Unlock the Content Item.
3381 -- NOTE that we will not call the following for now:
3382 -- set_citem_meta will not be called as none of the meta items are exposed
3383 -- in the UI for any of the content items.
3384 -- update_citem_basic will not be called as we do not expose Description
3385 -- in the UI and we do not allow the change of the Name.
3386 -- 4. This procedure returns the fact that it is successful.
3387 --
3388 -- HISTORY
3389 -- 24-FEB-2002 gdeodhar Created.
3390 --
3391 -----------------------------------------------------------------------
3392 PROCEDURE update_content_item(
3393 p_citem_id IN NUMBER,
3394 p_citem_version_id IN NUMBER,
3395 p_content_type_code IN VARCHAR2,
3396 p_content_item_name IN VARCHAR2,
3397 p_description IN VARCHAR2,
3398 p_delv_id IN NUMBER,
3399 p_attr_types_for_update IN JTF_VARCHAR2_TABLE_100 DEFAULT NULL,
3400 p_attr_values_for_update IN JTF_VARCHAR2_TABLE_4000 DEFAULT NULL,
3401 p_attach_file_id IN NUMBER DEFAULT NULL,
3402 p_attach_file_name IN VARCHAR2 DEFAULT NULL,
3403 p_commit IN VARCHAR2 DEFAULT FND_API.g_false,
3404 p_api_version IN NUMBER DEFAULT 1.0,
3405 p_api_validation_level IN NUMBER DEFAULT FND_API.g_valid_level_full,
3406 x_return_status OUT VARCHAR2,
3407 x_msg_count OUT NUMBER,
3408 x_msg_data OUT VARCHAR2,
3409 p_replace_attr_bundle IN VARCHAR2 DEFAULT FND_API.g_false
3410 )
3411 IS
3412 --
3413 l_attribute_type_codes JTF_VARCHAR2_TABLE_100 ;
3414 l_attribute_type_names JTF_VARCHAR2_TABLE_300 ;
3415 l_attributes JTF_VARCHAR2_TABLE_4000 ;
3416 l_citem_ver_id NUMBER ;
3417 l_citem_id NUMBER ;
3418 l_return_status VARCHAR2(1) ;
3419 l_msg_count NUMBER ;
3420 l_msg_data VARCHAR2(2000) ;
3421 --
3422 l_obj_ver_num NUMBER ;
3423 --
3424 l_api_name CONSTANT VARCHAR2(30) := 'Update_Content_Item';
3425 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
3426 --
3427 l_status VARCHAR2(30) ;
3428 l_attach_file_id NUMBER ;
3429 l_attach_file_name VARCHAR2(240) ;
3430 l_citem_name VARCHAR2(240) ;
3431 l_description VARCHAR2(2000) ;
3432 l_cpnt_citems JTF_NUMBER_TABLE ;
3433 l_cpnt_ctypes JTF_VARCHAR2_TABLE_100 ;
3434 l_cpnt_attrib_types JTF_VARCHAR2_TABLE_100 ;
3435 l_cpnt_citem_names JTF_VARCHAR2_TABLE_300 ;
3436 l_cpnt_owner_ids JTF_NUMBER_TABLE ;
3437 l_cpnt_owner_types JTF_VARCHAR2_TABLE_100 ;
3438 l_cpnt_owner_names JTF_VARCHAR2_TABLE_400 ;
3439 l_cpnt_sort_orders JTF_NUMBER_TABLE ;
3440 --
3444 --
3441 l_init_msg_list VARCHAR2(1) := FND_API.g_true;
3442 --
3443 l_api_version_number CONSTANT NUMBER := 1.0;
3445 BEGIN
3446 --
3447
3448 -- Standard call to check for call compatibility.
3449 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
3450 p_api_version,
3451 l_api_name,
3452 G_PKG_NAME)
3453 THEN
3454 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3455 END IF;
3456
3457 -- Initialize message list if p_init_msg_list is set to TRUE.
3458
3459 --l_init_msg_list := p_init_msg_list;
3460
3461 IF FND_API.to_Boolean( l_init_msg_list )
3462 THEN
3463 FND_MSG_PUB.initialize;
3464 END IF;
3465
3466 l_init_msg_list := FND_API.g_false;
3467
3468 -- Debug Message
3469 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' start');
3470 AMS_UTILITY_PVT.debug_message('content item id = ' || p_citem_id);
3471
3472 -- Initialize API return status to SUCCESS
3473 x_return_status := FND_API.G_RET_STS_SUCCESS;
3474 l_return_status := FND_API.G_RET_STS_SUCCESS;
3475
3476 IF g_using_locking = FND_API.g_true
3477 THEN
3478
3479 -- We have to lock the item first.
3480 IBC_CITEM_ADMIN_GRP.lock_item(
3481 p_content_item_id => p_citem_id
3482 ,p_commit => g_commit_on_lock_unlock
3483 ,p_api_version_number => p_api_version
3484 ,p_init_msg_list => l_init_msg_list
3485 ,x_citem_version_id => l_citem_ver_id
3486 ,x_object_version_number => l_obj_ver_num
3487 ,x_return_status => l_return_status
3488 ,x_msg_count => l_msg_count
3489 ,x_msg_data => l_msg_data
3490 );
3491
3492 IF FND_API.g_ret_sts_success <> l_return_status
3493 THEN
3494 AMS_Utility_PVT.Error_Message('AMS_ERR_LOCKING_CITEM');
3495 RAISE FND_API.g_exc_error;
3496 END IF;
3497
3498 IF l_citem_ver_id <> p_citem_version_id
3499 THEN
3500 AMS_Utility_PVT.Error_Message('AMS_ERR_CITEM_VER_MISMATCH');
3501 RAISE FND_API.g_exc_error;
3502 END IF;
3503
3504 ELSE
3505
3506 -- We have to get the object version number separately as we are not using locking.
3507 l_obj_ver_num := IBC_CITEM_ADMIN_GRP.getObjVerNum( p_citem_id );
3508
3509 END IF;
3510
3511 AMS_Utility_PVT.Debug_Message(' p_citem_version_id = ' || p_citem_version_id );
3512 AMS_Utility_PVT.Debug_Message(' obj ver num = ' || l_obj_ver_num );
3513
3514 -- Get all the existing basic attributes for the content item if necessary.
3515
3516 -- Check if we just need to replace the attribute bundle.
3517
3518 IF p_replace_attr_bundle = FND_API.g_true
3519 THEN
3520
3521 -- If the attribute bundle has to be totally replaced with the one
3522 -- incoming, do so.
3523 l_attribute_type_codes := p_attr_types_for_update;
3524 l_attributes := p_attr_values_for_update;
3525
3526 ELSE
3527
3528 IF p_attr_types_for_update IS NOT NULL
3529 AND
3530 p_attr_values_for_update IS NOT NULL
3531 THEN
3532
3533 /*
3534 -- This call was giving problems.
3535 -- Using get_content_item_data instead.
3536
3537 -- Call get_content_item_attrs method.
3538
3539 get_content_item_attrs(
3540 p_citem_id => p_citem_id
3541 ,p_ctype_code => p_content_type_code
3542 ,p_citem_ver_id => p_citem_version_id
3543 ,p_attrib_file_id => NULL
3544 ,p_api_version => p_api_version
3545 ,p_init_msg_list => l_init_msg_list
3546 ,x_attribute_type_codes => l_attribute_type_codes
3547 ,x_attribute_type_names => l_attribute_type_names
3548 ,x_attributes => l_attributes
3549 ,x_return_status => l_return_status
3550 ,x_msg_count => l_msg_count
3551 ,x_msg_data => l_msg_data
3552 );
3553
3554 IF FND_API.g_ret_sts_success <> l_return_status
3555 THEN
3556 AMS_Utility_PVT.Error_Message('AMS_ERR_GET_CITEM_ATTRS');
3557 RAISE FND_API.g_exc_error;
3558 END IF;
3559
3560 */
3561
3562 -- call get_content_item_data.
3563 get_content_item_data(
3564 p_citem_id => p_citem_id
3565 ,p_citem_ver_id => p_citem_version_id
3566 ,p_api_version => p_api_version
3567 ,x_status => l_status
3568 ,x_attach_file_id => l_attach_file_id
3569 ,x_attach_file_name => l_attach_file_name
3570 ,x_citem_name => l_citem_name
3571 ,x_description => l_description
3572 ,x_attribute_type_codes => l_attribute_type_codes
3573 ,x_attribute_type_names => l_attribute_type_names
3574 ,x_attributes => l_attributes
3575 ,x_cpnt_citem_ids => l_cpnt_citems
3576 ,x_cpnt_ctype_codes => l_cpnt_ctypes
3577 ,x_cpnt_attrib_types => l_cpnt_attrib_types
3578 ,x_cpnt_citem_names => l_cpnt_citem_names
3579 ,x_cpnt_sort_orders => l_cpnt_sort_orders
3580 ,x_object_version_number => l_obj_ver_num
3584 );
3581 ,x_return_status => l_return_status
3582 ,x_msg_count => l_msg_count
3583 ,x_msg_data => l_msg_data
3585
3586 IF FND_API.g_ret_sts_success <> l_return_status
3587 THEN
3588 AMS_Utility_PVT.Error_Message('AMS_ERR_GET_CITEM');
3589 RAISE FND_API.g_exc_error;
3590 END IF;
3591
3592 IF l_attribute_type_codes IS NOT NULL
3593 AND
3594 l_attributes IS NOT NULL
3595 THEN
3596
3597 -- go through the incoming attributes and get the new values and set the
3598 -- new values in the existing attribute bundle.
3599
3600 FOR i IN l_attribute_type_codes.first .. l_attribute_type_codes.last
3601 LOOP
3602 FOR j IN p_attr_types_for_update.first .. p_attr_types_for_update.last
3603 LOOP
3604 IF l_attribute_type_codes(i) = p_attr_types_for_update(j)
3605 THEN
3606
3607 -- The i-th attribute needs value change.
3608 -- Set the new value.
3609 l_attributes(i) := p_attr_values_for_update(j);
3610
3611 END IF;
3612 END LOOP;
3613 END LOOP;
3614
3615 ELSE
3616 -- If the Content Item does not have any Attributes set so far,
3617 -- we will set the incoming Attribute Bundle as is.
3618
3619 l_attribute_type_codes := p_attr_types_for_update;
3620 l_attributes := p_attr_values_for_update;
3621
3622 END IF;
3623
3624 END IF;
3625
3626 END IF;
3627
3628 IF l_attribute_type_codes IS NOT NULL
3629 AND
3630 l_attributes IS NOT NULL
3631 THEN
3632
3633 -- Set the Attribute Bundle.
3634 IBC_CITEM_ADMIN_GRP.set_attribute_bundle(
3635 p_citem_ver_id => p_citem_version_id
3636 ,p_attribute_type_codes => l_attribute_type_codes
3637 ,p_attributes => l_attributes -- This has the changed data as needed.
3638 ,p_remove_old => FND_API.g_true -- The procedure sets the p_remove_old value to FND_API.g_true by default as well. Sending it in anyway.
3639 ,p_commit => FND_API.g_false -- This is the Default.
3640 ,p_api_version_number => p_api_version
3641 ,p_init_msg_list => l_init_msg_list
3642 ,px_object_version_number => l_obj_ver_num -- This is an IN/OUT parameter in this procedure.
3643 ,x_return_status => l_return_status
3644 ,x_msg_count => l_msg_count
3645 ,x_msg_data => l_msg_data
3646 );
3647
3648 IF FND_API.g_ret_sts_success <> l_return_status
3649 THEN
3650 AMS_Utility_PVT.Error_Message('AMS_ERR_SET_CITEM_ATTRIB');
3651 AMS_Utility_PVT.Error_Message('AMS_ERR_UPDATE_CITEM');
3652 RAISE FND_API.g_exc_error;
3653 END IF;
3654
3655 END IF;
3656
3657 -- Set the attachment is necessary.
3658 IF p_attach_file_id IS NOT NULL
3659 AND
3660 p_attach_file_name IS NOT NULL
3661 THEN
3662 -- Call the procedure IBC_CITEM_ADMIN_GRP.set_attachment.
3663
3664 IBC_CITEM_ADMIN_GRP.set_attachment(
3665 p_citem_ver_id => p_citem_version_id
3666 ,p_attach_file_id => p_attach_file_id
3667 ,p_commit => FND_API.g_false -- This is the Default.
3668 ,p_api_version_number => p_api_version
3669 ,p_init_msg_list => l_init_msg_list
3670 ,px_object_version_number => l_obj_ver_num -- This is the IN OUT Parameter
3671 ,x_return_status => l_return_status
3672 ,x_msg_count => l_msg_count
3673 ,x_msg_data => l_msg_data
3674 );
3675
3676 IF FND_API.g_ret_sts_success <> l_return_status
3677 THEN
3678 AMS_Utility_PVT.Error_Message('AMS_ERR_SET_CITEM_ATTCH');
3679 RAISE FND_API.g_exc_error;
3680 END IF;
3681 END IF;
3682
3683 IF FND_API.g_ret_sts_success <> l_return_status
3684 THEN
3685 AMS_Utility_PVT.Error_Message('AMS_ERR_UPDATE_CITEM');
3686 RAISE FND_API.g_exc_error;
3687 END IF;
3688
3689 AMS_UTILITY_PVT.debug_message('Before Unlock.');
3690 AMS_UTILITY_PVT.debug_message('p_citem_version_id = ' || p_citem_version_id);
3691 AMS_UTILITY_PVT.debug_message('l_obj_ver_num = ' || l_obj_ver_num);
3692
3693 IF g_using_locking = FND_API.g_true
3694 THEN
3695
3696 -- unlock the content item as the update was successful.
3697 IBC_CITEM_ADMIN_GRP.unlock_item(
3698 p_content_item_id => p_citem_id
3699 ,p_commit => g_commit_on_lock_unlock
3700 ,p_api_version_number => p_api_version
3701 ,p_init_msg_list => l_init_msg_list
3702 ,x_return_status => l_return_status
3703 ,x_msg_count => l_msg_count
3704 ,x_msg_data => l_msg_data
3705 );
3706 --
3707 AMS_UTILITY_PVT.debug_message('After Unlock.');
3708 AMS_UTILITY_PVT.debug_message('l_return_status = ' || l_return_status);
3709 --
3710 IF FND_API.g_ret_sts_success <> l_return_status
3711 THEN
3712 AMS_Utility_PVT.Error_Message('AMS_ERR_UNLOCK_CITEM');
3713 RAISE FND_API.g_exc_error;
3714 END IF;
3715
3716 END IF;
3717
3718 -- Commit the work and set the output values.
3719
3723 COMMIT WORK;
3720 -- Standard check for p_commit
3721 IF FND_API.to_Boolean( p_commit )
3722 THEN
3724 END IF;
3725
3726 x_return_status := l_return_status;
3727
3728 -- Standard call to get message count and if count is 1, get message info.
3729 FND_MSG_PUB.count_and_get(
3730 p_count => x_msg_count,
3731 p_data => x_msg_data
3732 );
3733
3734 EXCEPTION
3735 WHEN FND_API.g_exc_error THEN
3736 ROLLBACK ;
3737 x_return_status := FND_API.G_RET_STS_ERROR;
3738 FND_MSG_PUB.count_and_get (
3739 p_count => x_msg_count,
3740 p_data => x_msg_data,
3741 p_encoded => FND_API.G_FALSE
3742 );
3743 WHEN FND_API.g_exc_unexpected_error THEN
3744 ROLLBACK ;
3745 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3746 FND_MSG_PUB.count_and_get (
3747 p_count => x_msg_count,
3748 p_data => x_msg_data,
3749 p_encoded => FND_API.G_FALSE
3750 );
3751 WHEN OTHERS THEN
3752 ROLLBACK ;
3753 x_return_status := FND_API.g_ret_sts_unexp_error;
3754 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
3755 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
3756 END IF;
3757 FND_MSG_PUB.count_and_get (
3758 p_count => x_msg_count,
3759 p_data => x_msg_data,
3760 p_encoded => FND_API.G_FALSE
3761 );
3762
3763 END update_content_item;
3764
3765 -----------------------------------------------------------------------
3766 -- PROCEDURE
3767 -- update_citem_for_delv
3768 --
3769 -- PURPOSE
3770 -- Update the Content Item associated with the Deliverable of type Content Page.
3771 --
3772 -- NOTES
3773 -- 1. The required input is as follows:
3774 -- content_type_code
3775 -- default_display_template_id
3776 -- deliverable_id
3777 -- content_item_id
3778 -- association_type_code (this is recorded in ibc_associations table)
3779 -- 2. This procedure returns the success or failure status
3780 --
3781 -----------------------------------------------------------------------
3782 PROCEDURE update_citem_for_delv(
3783 p_content_type_code IN VARCHAR2,
3784 p_def_disp_template_id IN NUMBER,
3785 p_delv_id IN NUMBER,
3786 p_citem_id IN NUMBER,
3787 p_assoc_type_code IN VARCHAR2,
3788 p_commit IN VARCHAR2 DEFAULT FND_API.g_false,
3789 p_api_version IN NUMBER DEFAULT 1.0,
3790 p_api_validation_level IN NUMBER DEFAULT FND_API.g_valid_level_full,
3791 x_return_status OUT VARCHAR2,
3792 x_msg_count OUT NUMBER,
3793 x_msg_data OUT VARCHAR2
3794 )
3795 IS
3796 --
3797 -- Declare the local variables and cursors here.
3798 --
3799 -- Cursor to select the latest citem version for a content item.
3800 --
3801 CURSOR c_max_version IS
3802 SELECT MAX(citem_version_id)
3803 FROM ibc_citem_versions_b
3804 WHERE content_item_id = p_citem_id ;
3805 --
3806 l_return_status VARCHAR2(1) ;
3807 l_msg_count NUMBER ;
3808 l_msg_data VARCHAR2(2000) ;
3809 --
3810 l_obj_ver_num NUMBER ;
3811 l_citem_ver_id NUMBER ;
3812 --
3813 l_attribute_type_codes JTF_VARCHAR2_TABLE_100 := JTF_VARCHAR2_TABLE_100() ;
3814 l_attributes JTF_VARCHAR2_TABLE_4000 := JTF_VARCHAR2_TABLE_4000() ;
3815 --
3816 l_api_name CONSTANT VARCHAR2(30) := 'Update_Citem_For_Delv';
3817 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
3818 --
3819 --
3820 l_init_msg_list VARCHAR2(1) := FND_API.g_true;
3821 --
3822 l_api_version_number CONSTANT NUMBER := 1.0;
3823 --
3824 BEGIN
3825 --
3826
3827 -- Standard call to check for call compatibility.
3828 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
3829 p_api_version,
3830 l_api_name,
3831 G_PKG_NAME)
3832 THEN
3833 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3834 END IF;
3835
3836 -- Initialize message list if p_init_msg_list is set to TRUE.
3837
3838 --l_init_msg_list := p_init_msg_list;
3839
3840 IF FND_API.to_Boolean( l_init_msg_list )
3841 THEN
3842 FND_MSG_PUB.initialize;
3843 END IF;
3844
3845 l_init_msg_list := FND_API.g_false;
3846
3847 -- Debug Message
3848 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
3849
3850 -- Initialize API return status to SUCCESS
3851 x_return_status := FND_API.G_RET_STS_SUCCESS;
3852 l_return_status := FND_API.G_RET_STS_SUCCESS;
3853
3854 IF g_using_locking = FND_API.g_true
3855 THEN
3856
3857 -- We have to lock the item first.
3858 IBC_CITEM_ADMIN_GRP.lock_item(
3859 p_content_item_id => p_citem_id
3860 ,p_commit => g_commit_on_lock_unlock
3861 ,p_api_version_number => p_api_version
3862 ,p_init_msg_list => l_init_msg_list
3863 ,x_citem_version_id => l_citem_ver_id
3864 ,x_object_version_number => l_obj_ver_num
3868 );
3865 ,x_return_status => l_return_status
3866 ,x_msg_count => l_msg_count
3867 ,x_msg_data => l_msg_data
3869
3870 IF FND_API.g_ret_sts_success <> l_return_status
3871 THEN
3872 AMS_Utility_PVT.Error_Message('AMS_ERR_LOCKING_CITEM');
3873 RAISE FND_API.g_exc_error;
3874 END IF;
3875
3876 ELSE
3877
3878 -- If we do not use locking mechanism, we will have to get the latest citem version
3879 -- for this deliverable at this stage.
3880 -- Fetch the latest citem version id.
3881 OPEN c_max_version;
3882 FETCH c_max_version INTO l_citem_ver_id;
3883 CLOSE c_max_version;
3884
3885 -- We must also fetch the object version number.
3886 l_obj_ver_num := IBC_CITEM_ADMIN_GRP.getObjVerNum( p_citem_id );
3887
3888 END IF;
3889
3890 AMS_Utility_PVT.Debug_Message(' l_citem_ver_id = ' || l_citem_ver_id );
3891 AMS_Utility_PVT.Debug_Message(' obj ver num = ' || l_obj_ver_num );
3892
3893 -- update the data if needed.
3894 IF p_def_disp_template_id IS NOT NULL
3895 THEN
3896 l_attribute_type_codes.extend();
3897 l_attribute_type_codes(1) := G_DEFAULT_DISPLAY_TEMPLATE;
3898
3899 l_attributes.extend();
3900 l_attributes(1) := p_def_disp_template_id;
3901
3902 -- Call update_content_item method.
3903 update_content_item(
3904 p_citem_id => p_citem_id
3905 ,p_citem_version_id => l_citem_ver_id
3906 ,p_content_type_code => p_content_type_code
3907 ,p_content_item_name => NULL
3908 ,p_description => NULL
3909 ,p_delv_id => p_delv_id
3910 ,p_attr_types_for_update => l_attribute_type_codes
3911 ,p_attr_values_for_update => l_attributes
3912 ,p_attach_file_id => NULL
3913 ,p_attach_file_name => NULL
3914 ,p_commit => FND_API.g_false
3915 ,p_api_version => p_api_version
3916 ,p_api_validation_level => p_api_validation_level
3917 ,x_return_status => l_return_status
3918 ,x_msg_count => l_msg_count
3919 ,x_msg_data => l_msg_data
3920 );
3921
3922 IF FND_API.g_ret_sts_success <> l_return_status
3923 THEN
3924 AMS_Utility_PVT.Error_Message('AMS_ERR_UPDATE_CITEM');
3925 RAISE FND_API.g_exc_error;
3926 END IF;
3927
3928 --Change the value of default_display_template in IBC_ASSOCIATIONS table.
3929
3930 UPDATE ibc_associations
3931 SET associated_object_val3 = p_def_disp_template_id
3932 WHERE content_item_id = p_citem_id
3933 AND associated_object_val1 = TO_CHAR(p_delv_id)
3934 AND association_type_code = G_CPAGE_ASSOC_TYPE_CODE;
3935
3936 IF (SQL%NOTFOUND) THEN
3937 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
3938 THEN
3939 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
3940 FND_MSG_PUB.add;
3941 END IF;
3942 RAISE FND_API.g_exc_error;
3943 END IF;
3944
3945 END IF;
3946
3947 IF g_using_locking = FND_API.g_true
3948 THEN
3949
3950 -- unlock the content item.
3951 IBC_CITEM_ADMIN_GRP.unlock_item(
3952 p_content_item_id => p_citem_id
3953 ,p_commit => g_commit_on_lock_unlock
3954 ,p_api_version_number => p_api_version
3955 ,p_init_msg_list => l_init_msg_list
3956 ,x_return_status => l_return_status
3957 ,x_msg_count => l_msg_count
3958 ,x_msg_data => l_msg_data
3959 );
3960
3961 AMS_UTILITY_PVT.debug_message('After Unlock.');
3962 AMS_UTILITY_PVT.debug_message('l_return_status = ' || l_return_status);
3963
3964 --
3965 IF FND_API.g_ret_sts_success <> l_return_status
3966 THEN
3967 AMS_Utility_PVT.Error_Message('AMS_ERR_UNLOCK_CITEM');
3968 RAISE FND_API.g_exc_error;
3969 END IF;
3970
3971 END IF;
3972
3973 -- Commit the work and set the output values.
3974
3975 -- Standard check for p_commit
3976 IF FND_API.to_Boolean( p_commit )
3977 THEN
3978 COMMIT WORK;
3979 END IF;
3980
3981 x_return_status := l_return_status;
3982
3983 -- Standard call to get message count and if count is 1, get message info.
3984 FND_MSG_PUB.count_and_get(
3985 p_count => x_msg_count,
3986 p_data => x_msg_data
3987 );
3988
3989 EXCEPTION
3990 WHEN FND_API.g_exc_error THEN
3991 ROLLBACK ;
3992 x_return_status := FND_API.G_RET_STS_ERROR;
3993 FND_MSG_PUB.count_and_get (
3994 p_count => x_msg_count,
3995 p_data => x_msg_data,
3996 p_encoded => FND_API.G_FALSE
3997 );
3998 WHEN FND_API.g_exc_unexpected_error THEN
3999 ROLLBACK ;
4000 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4001 FND_MSG_PUB.count_and_get (
4002 p_count => x_msg_count,
4003 p_data => x_msg_data,
4004 p_encoded => FND_API.G_FALSE
4005 );
4006 WHEN OTHERS THEN
4007 ROLLBACK ;
4008 x_return_status := FND_API.g_ret_sts_unexp_error;
4009 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
4010 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
4011 END IF;
4015 p_encoded => FND_API.G_FALSE
4012 FND_MSG_PUB.count_and_get (
4013 p_count => x_msg_count,
4014 p_data => x_msg_data,
4016 );
4017
4018 END update_citem_for_delv;
4019
4020 -----------------------------------------------------------------------
4021 -- PROCEDURE
4022 -- create_display_template
4023 --
4024 -- PURPOSE
4025 -- Create a Content Item of type STYLESHEET.
4026 --
4027 -- NOTES
4028 -- 1. The required input is as follows:
4029 -- Content Type Code for which the Stylesheet or Display Template is for.
4030 -- Display Template or Stylesheet Name.
4031 -- Attachment File ID that has the actual Stylesheet or Display Template.
4032 -- Attachment File Name. (This will the one of the uploaded file).
4033 -- Value for DELIVERY_CHANNEL
4034 -- Value for OUTPUT_TYPE
4035 -- 2. The optional input is as follows:
4036 -- Stylesheet Description.
4037 -- 3. This procedure performs the following steps:
4038 -- 1. Create a Content Item of type STYLESHEET using Bulk Insert as an APPROVED item.
4039 -- Set the attribute bundle for the item, with DELIVERY_OPTION and OUTPUT_TYPE.
4040 -- 2. Create an entry in IBC_STYLESHEETS table.
4041 -- 4. This procedure returns the fact that it is successful, it also returns the
4042 -- newly created Display Template ID.
4043 --
4044 -- HISTORY
4045 -- 14-FEB-2002 gdeodhar Created.
4046 --
4047 -----------------------------------------------------------------------
4048
4049 PROCEDURE create_display_template(
4050 p_content_type_code IN VARCHAR2,
4051 p_stylesheet_name IN VARCHAR2,
4052 p_stylesheet_descr IN VARCHAR2 DEFAULT NULL,
4053 p_delivery_channel IN VARCHAR2,
4054 p_output_type IN VARCHAR2,
4055 p_attach_file_id IN NUMBER,
4056 p_attach_file_name IN VARCHAR2,
4057 p_resource_id IN NUMBER,
4058 p_resource_type IN VARCHAR2,
4059 p_commit IN VARCHAR2 DEFAULT FND_API.g_false,
4060 p_api_version IN NUMBER DEFAULT 1.0,
4061 p_api_validation_level IN NUMBER DEFAULT FND_API.g_valid_level_full,
4062 x_citem_id OUT NUMBER,
4063 x_citem_ver_id OUT NUMBER,
4064 x_return_status OUT VARCHAR2,
4065 x_msg_count OUT NUMBER,
4066 x_msg_data OUT VARCHAR2
4067 )
4068 IS
4069 --
4070 -- Declare the local variables and cursors here.
4071 --
4072 l_citem_ver_id NUMBER ;
4073 l_citem_id NUMBER ;
4074 l_return_status VARCHAR2(1) ;
4075 l_msg_count NUMBER ;
4076 l_msg_data VARCHAR2(2000) ;
4077 --
4078 l_obj_ver_num NUMBER ;
4079 --
4080 l_attribute_type_codes JTF_VARCHAR2_TABLE_100 := JTF_VARCHAR2_TABLE_100() ;
4081 l_attributes JTF_VARCHAR2_TABLE_4000 := JTF_VARCHAR2_TABLE_4000() ;
4082 --
4083 l_api_name CONSTANT VARCHAR2(30) := 'Create_Display_Template';
4084 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
4085 --
4086 l_init_msg_list VARCHAR2(1) := FND_API.g_true;
4087 --
4088 l_api_version_number CONSTANT NUMBER := 1.0;
4089 --
4090 BEGIN
4091 --
4092 -- Standard Start of API savepoint
4093 SAVEPOINT create_display_template_PVT ;
4094
4095 -- Standard call to check for call compatibility.
4096 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
4097 p_api_version,
4098 l_api_name,
4099 G_PKG_NAME)
4100 THEN
4101 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4102 END IF;
4103
4104 -- Initialize message list if p_init_msg_list is set to TRUE.
4105
4106 --l_init_msg_list := p_init_msg_list;
4107
4108 IF FND_API.to_Boolean( l_init_msg_list )
4109 THEN
4110 FND_MSG_PUB.initialize;
4111 END IF;
4112
4113 l_init_msg_list := FND_API.g_false;
4114
4115 -- Debug Message
4116 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
4117
4118 -- Initialize API return status to SUCCESS
4119 x_return_status := FND_API.G_RET_STS_SUCCESS;
4120 l_return_status := FND_API.G_RET_STS_SUCCESS;
4121
4122 -- prepare the data for insert.
4123 --
4124 l_attribute_type_codes.extend();
4125 l_attribute_type_codes(1) := G_DELIVERY_CHANNEL ;
4126 --
4127 l_attributes.extend();
4128 l_attributes(1) := p_delivery_channel;
4129 --
4130 l_attribute_type_codes.extend();
4131 l_attribute_type_codes(2) := G_OUTPUT_TYPE ;
4132 --
4133 l_attributes.extend();
4134 l_attributes(2) := p_output_type;
4135 --
4136 -- Call upsert_item.
4137
4138 -- Call IBC_CITEM_ADMIN_GRP.upsert_item procedure.
4139
4140 IBC_CITEM_ADMIN_GRP.upsert_item(
4141 p_ctype_code => G_IBC_STYLESHEET
4142 ,p_citem_name => p_stylesheet_name
4143 ,p_citem_description => substr(p_stylesheet_descr,1,2000)
4144 ,p_dir_node_id => G_AMS_DIR_NODE_ID
4145 ,p_owner_resource_id => p_resource_id
4146 ,p_owner_resource_type => p_resource_type
4150 ,p_lock_flag => g_lock_flag_value
4147 ,p_reference_code => NULL -- Why is this needed?
4148 ,p_trans_required => FND_API.g_false -- This is the default value. For now we do not expose this flag on the UI.
4149 ,p_parent_item_id => NULL -- There is no parent for the item of type IBC_STYLESHEET.
4151 ,p_wd_restricted => g_wd_restricted_flag_value
4152 ,p_start_date => sysdate
4153 ,p_end_date => null
4154 ,p_attach_file_id => p_attach_file_id -- This procedure picks up the file name from FND_LOBS.
4155 ,p_attribute_type_codes => l_attribute_type_codes
4156 ,p_attributes => l_attributes
4157 ,p_component_citems => NULL
4158 ,p_component_atypes => NULL
4159 ,p_sort_order => NULL
4160 ,p_status => G_CITEM_WIP_STATUS_CODE -- We will approve this item as soon as we are done with the creation.
4161 ,p_log_action => FND_API.g_true -- This to be sent as TRUE. It updates the Audit Logs.
4162 ,p_commit => FND_API.g_false -- We still have to do some more operations.
4163 ,p_api_version_number => p_api_version
4164 ,p_init_msg_list => l_init_msg_list
4165 ,px_content_item_id => l_citem_id
4166 ,px_citem_ver_id => l_citem_ver_id
4167 ,px_object_version_number => l_obj_ver_num
4168 ,x_return_status => l_return_status
4169 ,x_msg_count => l_msg_count
4170 ,x_msg_data => l_msg_data
4171 );
4172
4173 AMS_UTILITY_PVT.debug_message('After upsert_item.');
4174 AMS_UTILITY_PVT.debug_message('l_citem_id = ' || l_citem_id);
4175 AMS_UTILITY_PVT.debug_message('l_citem_ver_id = ' || l_citem_ver_id);
4176 AMS_UTILITY_PVT.debug_message('l_obj_ver_num = ' || l_obj_ver_num);
4177 AMS_UTILITY_PVT.debug_message('l_return_status = ' || l_return_status);
4178
4179 IF FND_API.g_ret_sts_success <> l_return_status
4180 THEN
4181 AMS_Utility_PVT.Error_Message('AMS_ERR_CRE_STYLE_CITEM');
4182 RAISE FND_API.g_exc_error;
4183 END IF;
4184
4185 -- Approve the above item.
4186
4187 IBC_CITEM_ADMIN_GRP.approve_item(
4188 p_citem_ver_id => l_citem_ver_id
4189 ,p_commit => FND_API.g_false
4190 ,p_api_version_number => p_api_version
4191 ,p_init_msg_list => l_init_msg_list
4192 ,px_object_version_number => l_obj_ver_num
4193 ,x_return_status => l_return_status
4194 ,x_msg_count => l_msg_count
4195 ,x_msg_data => l_msg_data
4196 );
4197
4198 IF FND_API.g_ret_sts_success <> l_return_status
4199 THEN
4200 AMS_Utility_PVT.Error_Message('AMS_ERR_APPROVE_CITEM');
4201 RAISE FND_API.g_exc_error;
4202 END IF;
4203
4204 IF g_using_locking = FND_API.g_true
4205 THEN
4206
4207 -- Unlock the Item if success.
4208 IBC_CITEM_ADMIN_GRP.unlock_item(
4209 p_content_item_id => l_citem_id
4210 ,p_commit => g_commit_on_lock_unlock
4211 ,p_api_version_number => p_api_version
4212 ,p_init_msg_list => l_init_msg_list
4213 ,x_return_status => l_return_status
4214 ,x_msg_count => l_msg_count
4215 ,x_msg_data => l_msg_data
4216 );
4217
4218 AMS_UTILITY_PVT.debug_message('After Unlock.');
4219 AMS_UTILITY_PVT.debug_message('l_return_status = ' || l_return_status);
4220
4221 --
4222 --
4223 --
4224 IF FND_API.g_ret_sts_success <> l_return_status
4225 THEN
4226 AMS_Utility_PVT.Error_Message('AMS_ERR_UNLOCK_CITEM');
4227 RAISE FND_API.g_exc_error;
4228 END IF;
4229
4230 END IF;
4231
4232 -- Create the association in IBC_STYLESHEETS, if success.
4233 insert into IBC_STYLESHEETS
4234 (
4235 CONTENT_TYPE_CODE
4236 ,CONTENT_ITEM_ID
4237 ,DEFAULT_STYLESHEET_FLAG
4238 ,CREATED_BY
4239 ,CREATION_DATE
4240 ,LAST_UPDATED_BY
4241 ,LAST_UPDATE_DATE
4242 ,LAST_UPDATE_LOGIN
4243 ,OBJECT_VERSION_NUMBER
4244 ,SECURITY_GROUP_ID
4245 )
4246 values
4247 (
4248 p_content_type_code
4249 ,l_citem_id
4250 ,'N'
4251 ,FND_GLOBAL.user_id
4252 ,SYSDATE
4253 ,FND_GLOBAL.user_id
4254 ,SYSDATE
4255 ,FND_GLOBAL.conc_login_id
4256 ,1
4257 ,NULL
4258 );
4259
4260 IF FND_API.g_ret_sts_success <> l_return_status
4261 THEN
4262 AMS_Utility_PVT.Error_Message('AMS_ERR_CREATE_DISP_TMPL');
4263 RAISE FND_API.g_exc_error;
4264 END IF;
4265 --
4266 -- If we come till here, everything has been created successfully.
4267 -- Commit the work and set the output values.
4268 --
4269 -- Standard check for p_commit
4270 IF FND_API.to_Boolean( p_commit )
4271 THEN
4272 COMMIT WORK;
4273 END IF;
4274 --
4275 x_return_status := l_return_status;
4276 x_citem_id := l_citem_id;
4277 x_citem_ver_id := l_citem_ver_id;
4278 --
4279 -- Standard call to get message count and if count is 1, get message info.
4280 FND_MSG_PUB.count_and_get(
4281 p_count => x_msg_count,
4285 EXCEPTION
4282 p_data => x_msg_data
4283 );
4284 --
4286 WHEN FND_API.g_exc_error THEN
4287 ROLLBACK TO create_display_template_PVT ;
4288 x_return_status := FND_API.G_RET_STS_ERROR;
4289 FND_MSG_PUB.count_and_get (
4290 p_count => x_msg_count,
4291 p_data => x_msg_data,
4292 p_encoded => FND_API.G_FALSE
4293 );
4294 WHEN FND_API.g_exc_unexpected_error THEN
4295 ROLLBACK TO create_display_template_PVT ;
4296 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4297 FND_MSG_PUB.count_and_get (
4298 p_count => x_msg_count,
4299 p_data => x_msg_data,
4300 p_encoded => FND_API.G_FALSE
4301 );
4302 WHEN OTHERS THEN
4303 ROLLBACK TO create_display_template_PVT ;
4304 x_return_status := FND_API.g_ret_sts_unexp_error;
4305 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
4306 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
4307 END IF;
4308 FND_MSG_PUB.count_and_get (
4309 p_count => x_msg_count,
4310 p_data => x_msg_data,
4311 p_encoded => FND_API.G_FALSE
4312 );
4313 --
4314 END create_display_template;
4315
4316
4317
4318
4319
4320
4321
4322 -----------------------------------------------------------------------
4323 -- PROCEDURE
4324 -- create_basic_questions_item.
4325 --
4326 -- PURPOSE
4327 -- Create basic content item of type QUESTIONS.
4328 --
4329 -- NOTES
4330 -- 1. The required input is as follows:
4331 -- Content Type code. This must be QUESTIONS.
4332 -- Name of the Questions Section.
4333 -- Owner Resource Id.
4334 -- Resource Type.
4335 -- Content Item Id for the Parent Content Item associated with the parent deliverable.
4336 -- Content Item Version Id for the Parent Content Item.
4337 -- Content Type Code for the Parent Content Item.
4338 -- 2. The optional input is as follows:
4339 -- Description.
4340 -- 3. This procedure performs the following steps:
4341 -- 1. Create a basic Content Item of type QUESTIONS using Bulk Insert.
4342 -- 2. Create compound relation with the parent.
4343 -- 4. This procedure returns the fact that it is successful, it also returns the
4344 -- newly created Content Item Id.
4345 --
4346 -- HISTORY
4347 -- 09-MAR-2002 gdeodhar Created.
4348 --
4349 -----------------------------------------------------------------------
4350
4351 PROCEDURE create_basic_questions_item(
4352 p_content_type_code IN VARCHAR2,
4353 p_content_item_name IN VARCHAR2,
4354 p_description IN VARCHAR2,
4355 p_start_date IN DATE DEFAULT SYSDATE,
4356 p_end_date IN DATE DEFAULT NULL,
4357 p_owner_resource_id IN NUMBER,
4358 p_owner_resource_type IN VARCHAR2,
4359 p_reusable_flag IN VARCHAR2 DEFAULT FND_API.g_false, -- CHANGE to Y or N when IBC folks change the conventions for varchar2 fields.
4360 p_attribute_type_code IN VARCHAR2,
4361 p_parent_citem_id IN NUMBER,
4362 p_parent_citem_ver_id IN NUMBER,
4363 p_parent_ctype_code IN VARCHAR2,
4364 p_commit IN VARCHAR2 DEFAULT FND_API.g_false,
4368 x_citem_ver_id OUT NUMBER,
4365 p_api_version IN NUMBER DEFAULT 1.0,
4366 p_api_validation_level IN NUMBER DEFAULT FND_API.g_valid_level_full,
4367 x_citem_id OUT NUMBER,
4369 x_return_status OUT VARCHAR2,
4370 x_msg_count OUT NUMBER,
4371 x_msg_data OUT VARCHAR2
4372 )
4373 IS
4374 --
4375 -- Declare the local variables and cursors here.
4376 --
4377 l_citem_ver_id NUMBER ;
4378 l_citem_id NUMBER ;
4379 l_return_status VARCHAR2(1) ;
4380 l_msg_count NUMBER ;
4381 l_msg_data VARCHAR2(2000) ;
4382 --
4383 l_obj_ver_num NUMBER ;
4384 --
4385 l_citem_ids JTF_NUMBER_TABLE := JTF_NUMBER_TABLE() ;
4386 l_citem_attrs JTF_VARCHAR2_TABLE_100 := JTF_VARCHAR2_TABLE_100() ;
4387 l_dummy_sort_order JTF_NUMBER_TABLE := JTF_NUMBER_TABLE() ;
4388 --
4389 l_api_name CONSTANT VARCHAR2(30) := 'Create_Basic-Questions_Item';
4390 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
4391 --
4392 l_init_msg_list VARCHAR2(1) := FND_API.g_true;
4393 --
4394 l_api_version_number CONSTANT NUMBER := 1.0;
4395 --
4396 BEGIN
4397 --
4398 -- Standard Start of API savepoint
4399 SAVEPOINT create_basic_ques_item_PVT ;
4400
4401 -- Standard call to check for call compatibility.
4402 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
4403 p_api_version,
4404 l_api_name,
4405 G_PKG_NAME)
4406 THEN
4407 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4408 END IF;
4409
4410 -- Initialize message list if p_init_msg_list is set to TRUE.
4411
4412 --l_init_msg_list := p_init_msg_list;
4413
4414 IF FND_API.to_Boolean( l_init_msg_list )
4415 THEN
4416 FND_MSG_PUB.initialize;
4417 END IF;
4418
4419 l_init_msg_list := FND_API.g_false;
4420
4421 -- Debug Message
4422 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
4423
4424 -- Initialize API return status to SUCCESS
4425 x_return_status := FND_API.G_RET_STS_SUCCESS;
4426 l_return_status := FND_API.G_RET_STS_SUCCESS;
4427 --
4428 IF p_content_type_code <> G_QUESTIONS -- Should be a CONSTANT in the package.
4429 THEN
4430 AMS_Utility_PVT.Error_Message('AMS_ERR_WRONG_CTYPE_CODE');
4431 RAISE FND_API.g_exc_error;
4432 END IF;
4433 --
4434 x_return_status := FND_API.g_ret_sts_success;
4435 --
4436 -- Create a new Content Item in IBC Schema for incoming Content Type.
4437 -- Call upsert_item.
4438
4439 IBC_CITEM_ADMIN_GRP.upsert_item(
4440 p_ctype_code => G_QUESTIONS
4441 ,p_citem_name => p_content_item_name
4442 ,p_citem_description => NULL -- We do not expose the description in the UI for now.
4443 ,p_dir_node_id => G_AMS_DIR_NODE_ID
4444 ,p_owner_resource_id => p_owner_resource_id
4445 ,p_owner_resource_type => p_owner_resource_type
4446 ,p_reference_code => NULL -- Why is this needed?
4447 ,p_trans_required => FND_API.g_false -- This is the default value. For now we do not expose this flag on the UI.
4448 ,p_parent_item_id => p_parent_citem_id -- Specify the parent content item id. This item is visible only in the context of this parent.
4449 ,p_lock_flag => g_lock_flag_value
4450 ,p_wd_restricted => g_wd_restricted_flag_value
4451 ,p_start_date => p_start_date
4452 ,p_end_date => p_end_date
4453 ,p_attach_file_id => NULL -- We do not have any attachment while creating the basic Questions item.
4454 ,p_attribute_type_codes => NULL -- We do not set the attribute bundle while creating the basic Questions item.
4455 ,p_attributes => NULL
4456 ,p_component_citems => NULL
4457 ,p_component_atypes => NULL
4458 ,p_sort_order => NULL
4459 ,p_status => G_CITEM_WIP_STATUS_CODE -- When the Deliverable becomes active, we will go in and approve all the underlying content items.
4460 ,p_log_action => FND_API.g_true -- This to be sent as TRUE. It updates the Audit Logs.
4461 ,p_commit => FND_API.g_false -- We still have to do some more operations.
4462 ,p_api_version_number => p_api_version
4463 ,p_init_msg_list => l_init_msg_list
4464 ,px_content_item_id => l_citem_id
4465 ,px_citem_ver_id => l_citem_ver_id
4466 ,px_object_version_number => l_obj_ver_num
4467 ,x_return_status => l_return_status
4468 ,x_msg_count => l_msg_count
4469 ,x_msg_data => l_msg_data
4470 );
4471
4472 AMS_UTILITY_PVT.debug_message('After upsert_item.');
4473 AMS_UTILITY_PVT.debug_message('l_citem_id = ' || l_citem_id);
4474 AMS_UTILITY_PVT.debug_message('l_citem_ver_id = ' || l_citem_ver_id);
4475 AMS_UTILITY_PVT.debug_message('l_obj_ver_num = ' || l_obj_ver_num);
4476 AMS_UTILITY_PVT.debug_message('l_return_status = ' || l_return_status);
4477
4478 IF FND_API.g_ret_sts_success <> l_return_status
4482 END IF;
4479 THEN
4480 AMS_Utility_PVT.Error_Message('AMS_ERR_CRE_QUES_CITEM');
4481 RAISE FND_API.g_exc_error;
4483
4484 -- Create the Compound Relation with the parent.
4485 IF p_parent_citem_ver_id IS NOT NULL
4486 AND
4487 p_parent_ctype_code IS NOT NULL
4488 AND
4489 p_attribute_type_code IS NOT NULL
4490 THEN
4491 -- prepare the data for insert.
4492 l_citem_attrs.extend();
4493 l_citem_attrs(1) := p_attribute_type_code;
4494 --
4495 l_citem_ids.extend();
4496 l_citem_ids(1) := l_citem_id;
4497 --
4498 l_dummy_sort_order.extend();
4499 l_dummy_sort_order(1) := 1;
4500 --
4501
4502 IBC_CITEM_ADMIN_GRP.insert_components(
4503 p_citem_ver_id => p_parent_citem_ver_id
4504 ,p_content_item_ids => l_citem_ids
4505 ,p_attribute_type_codes => l_citem_attrs
4506 ,p_sort_order => l_dummy_sort_order -- The NULL does not work. -- The new API is supposed to be able to take NULL for this parameter.
4507 ,p_commit => FND_API.g_false
4508 ,p_api_version_number => p_api_version
4509 ,p_init_msg_list => l_init_msg_list
4510 ,x_return_status => l_return_status
4511 ,x_msg_count => l_msg_count
4512 ,x_msg_data => l_msg_data
4513 );
4514
4515 END IF;
4516 --
4517 --
4518 IF FND_API.g_ret_sts_success <> l_return_status
4519 THEN
4520 AMS_Utility_PVT.Error_Message('AMS_ERR_ADD_COMPOUND_REL');
4521 RAISE FND_API.g_exc_error;
4522 END IF;
4523 --
4524 IF g_using_locking = FND_API.g_true
4525 THEN
4526
4527 -- Unlock the content item.
4528
4529 IBC_CITEM_ADMIN_GRP.unlock_item(
4530 p_content_item_id => l_citem_id
4531 ,p_commit => g_commit_on_lock_unlock
4532 ,p_api_version_number => p_api_version
4533 ,p_init_msg_list => l_init_msg_list
4534 ,x_return_status => l_return_status
4535 ,x_msg_count => l_msg_count
4536 ,x_msg_data => l_msg_data
4537 );
4538 --
4539 AMS_UTILITY_PVT.debug_message('After Unlock.');
4540 AMS_UTILITY_PVT.debug_message('l_return_status = ' || l_return_status);
4541
4542 END IF;
4543 --
4544 -- If we come till here, everything has been created successfully.
4545 -- Commit the work and set the output values.
4546 --
4547 -- Standard check for p_commit
4548 IF FND_API.to_Boolean( p_commit )
4549 THEN
4550 COMMIT WORK;
4551 END IF;
4552 --
4553 x_return_status := l_return_status;
4554 x_citem_id := l_citem_id;
4555 x_citem_ver_id := l_citem_ver_id;
4556 --
4557 -- Standard call to get message count and if count is 1, get message info.
4558 FND_MSG_PUB.count_and_get(
4559 p_count => x_msg_count,
4560 p_data => x_msg_data
4561 );
4562 --
4563 EXCEPTION
4564 WHEN FND_API.g_exc_error THEN
4565 ROLLBACK TO create_basic_ques_item_PVT ;
4566 x_return_status := FND_API.G_RET_STS_ERROR;
4567 FND_MSG_PUB.count_and_get (
4568 p_count => x_msg_count,
4569 p_data => x_msg_data,
4570 p_encoded => FND_API.G_FALSE
4571 );
4572 WHEN FND_API.g_exc_unexpected_error THEN
4573 ROLLBACK TO create_basic_ques_item_PVT ;
4574 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4575 FND_MSG_PUB.count_and_get (
4576 p_count => x_msg_count,
4577 p_data => x_msg_data,
4578 p_encoded => FND_API.G_FALSE
4579 );
4580 WHEN OTHERS THEN
4581 ROLLBACK TO create_basic_ques_item_PVT ;
4582 x_return_status := FND_API.g_ret_sts_unexp_error;
4583 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
4584 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
4585 END IF;
4586 FND_MSG_PUB.count_and_get (
4587 p_count => x_msg_count,
4588 p_data => x_msg_data,
4589 p_encoded => FND_API.G_FALSE
4590 );
4591 --
4592 END create_basic_questions_item;
4593
4594
4595
4596
4597
4598
4599 -----------------------------------------------------------------------
4600 -- PROCEDURE
4601 -- generate_select_sql.
4602 --
4603 -- PURPOSE
4604 -- Generate select SQL statement, given a data source and list of fields.
4605 --
4609 -- VARCHAR2 Array with a list of Data Fields in the form of DATA_SRC_TYPE_CODE:FIELD_COLUMN_NAME.
4606 -- NOTES
4607 -- 1. The required input is as follows:
4608 -- Data Source code.
4610 -- 2. The optional input is as follows:
4611 -- NUMBER Array with a list of Data Source Field IDs.
4612 -- 3. This procedure performs the following steps:
4613 -- 1. Referes to the Data Sources schema to get the details of Data Source fields.
4614 -- 2. Generate a Select SQL Statement based on the Data Source and the fields.
4615 -- 4. This procedure returns the generated SQL statement and the list of bind variable names.
4616 --
4617 -- 5. Change made on 20 APR 2002 is as follows:
4618 --
4619 -- Making changes in generate_sql_statement. April 30, 2002.
4620 -- According to Avijit, for Runtime of Questions Section the bind variable name
4621 -- has to be changed based on the Data Source column and field.
4622 -- This change is fine, but it will not work for user defined data sources at all.
4623 -- For address, amsp is good for address-es.
4624 -- For email, fax, phone-s , please put amsctp
4625 -- For company name, please put amsorgp - org party id,
4626 -- For job title, amsrelp - relationshipparty id is good.
4627 --
4628 -- This is what the above translates to :
4629 -- for PERSON_LIST as list_source_type, amsp should be used.
4630 -- for PERSON_PHONE1 to PERSON_PHONE3 and EMAIL and FAX, amscpt (contant points) should be used.
4631 -- When the same page is used in B2C Context, Avijit's runtime uses Person Party Id.
4632 -- When the same page is used in B2B Context, Avijit's runtime uses Relationship Party Id.
4633 -- We are not covering the Business Phones and other details as yet.
4634 -- for ORGANIZATION_LIST as list_source_type, amsorgp should be used. This is Organization Party ID.
4635 -- for ORGANIZATION_CONTACT_LIST as list_source_type, amsrelp should be used. This is Relationship Party ID.
4636 --
4637 -- HISTORY
4638 -- 09-MAR-2002 gdeodhar Created.
4639 -- 30-APR-2002 gdeodhar Modified to set the bind_var as per the List Source Type used.
4640 -- 06-MAY-2002 gdeodhar Changed the convention about the aliases.
4641 -- Earlier it was Data_Source_Code_Name:Field_Column_Name.
4642 -- Truncated that after 30 characters.
4643 -- That does not look good.
4644 -- So changed it such that now it has the following format:
4645 -- Data_Source_Type_Id:Field_Column_Name truncated after 30th character.
4646 --
4647 -----------------------------------------------------------------------
4648
4649 PROCEDURE generate_select_sql(
4650 p_data_source_code IN VARCHAR2,
4651 p_data_source_fields_list IN JTF_VARCHAR2_TABLE_300 DEFAULT NULL,
4652 p_data_source_field_ids IN JTF_NUMBER_TABLE DEFAULT NULL,
4653 x_select_sql_statement OUT VARCHAR2,
4654 x_bind_vars OUT JTF_VARCHAR2_TABLE_300,
4655 x_return_status OUT VARCHAR2,
4656 x_msg_count OUT NUMBER,
4657 x_msg_data OUT VARCHAR2
4658 )
4659 IS
4660 l_select_sql_statement VARCHAR2(4000) := '' ;
4661 l_select_clause VARCHAR2(4000) := '' ;
4662 l_from_clause VARCHAR2(1000) := '' ;
4663 l_where_clause VARCHAR2(1000) := '' ;
4664 l_return_status VARCHAR2(1) := FND_API.g_ret_sts_success ;
4665 l_msg_count NUMBER ;
4666 l_msg_data VARCHAR2(2000) ;
4667
4668 l_data_src_fld_ids JTF_NUMBER_TABLE := JTF_NUMBER_TABLE() ;
4669 l_data_src_fld_cols JTF_VARCHAR2_TABLE_300 := JTF_VARCHAR2_TABLE_300() ;
4670 l_bind_vars JTF_VARCHAR2_TABLE_300 := JTF_VARCHAR2_TABLE_300() ;
4671
4672 l_source_object_name VARCHAR2(240) ;
4673 l_source_object_pk_field VARCHAR2(240) ;
4674 l_master_source_type_flag VARCHAR2(1) ;
4675 l_view_application_id NUMBER ;
4676
4677 l_list_source_field_id NUMBER ;
4678 l_list_source_field VARCHAR2(240) ;
4679 l_source_column_name VARCHAR2(240) ;
4680
4681 l_alias VARCHAR2(240) ;
4682
4683 l_fld_count NUMBER ;
4684
4685 l_data_src_type_id NUMBER ;
4686
4687 CURSOR c_get_list_src_id (p_data_source_code IN VARCHAR2) IS
4688 SELECT list_source_type_id
4689 FROM ams_list_src_types
4690 WHERE source_type_code = p_data_source_code
4691 AND enabled_flag = 'Y';
4692
4693 CURSOR c_get_list_src_data (p_data_source_code IN VARCHAR2) IS
4694 SELECT source_object_name, source_object_pk_field, master_source_type_flag, view_application_id
4695 FROM ams_list_src_types
4696 WHERE source_type_code = p_data_source_code
4697 AND enabled_flag = 'Y';
4698
4699 CURSOR c_get_list_src_fld_data (p_data_source_code IN VARCHAR2, l_list_source_field_id IN NUMBER) IS
4700 SELECT source_column_name
4704 AND enabled_flag = 'Y';
4701 FROM ams_list_src_fields
4702 WHERE de_list_source_type_code = p_data_source_code
4703 AND list_source_field_id = l_list_source_field_id
4705
4706 --
4707 l_api_name CONSTANT VARCHAR2(30) := 'Generate_Select_Sql';
4708 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
4709 --
4710 BEGIN
4711 --
4712 l_return_status := FND_API.g_ret_sts_success;
4713 --
4714 -- Get all the Data Source details.
4715 OPEN c_get_list_src_data (p_data_source_code);
4716 FETCH c_get_list_src_data
4717 INTO l_source_object_name, l_source_object_pk_field, l_master_source_type_flag, l_view_application_id;
4718 CLOSE c_get_list_src_data;
4719
4720 IF l_source_object_name IS NULL
4721 AND
4722 l_source_object_pk_field IS NULL
4723 THEN
4724 AMS_Utility_PVT.Error_Message('AMS_ERR_NO_DATA_SRC');
4725 RAISE FND_API.g_exc_error;
4726 END IF;
4727
4728 --
4729 -- Form the FROM clause and WHERE clause.
4730 --
4731 l_from_clause := 'FROM ' || l_source_object_name;
4732 l_where_clause := 'WHERE ' || l_source_object_pk_field || ' = ?';
4733
4734
4735 -- Check for the Data Source codes and add the bind variable names to the array.
4736 l_bind_vars.extend();
4737 l_bind_vars(1) := '';
4738
4739 /*
4740 l_bind_vars(1) := G_BIND_VAR_AMSP;
4741 -- Hardcoding the bind var as amsp for now.
4742 -- Since we use only TCA based data sources for Merge Fields, it is fine to
4743 -- assume the bind variable as party id for Rich Content with Merge Fields.
4744 -- For the Questions section, we are hardcoding it as well, so the bind variables
4745 -- returned from this method will never be used for Questions section.
4746 -- Revisit this part when we allow User Defined data sources.
4747 */
4748 -- Commented the above piece, we not have the bind variable names based on the Data
4749 -- Source Type.
4750
4751 -- The bind variables for both questions section and rich content section will now
4752 -- depend on the type of Data Source used (B2B or B2C or Contact Points).
4753 -- The party id will always be Person Party Id for B2C.
4754 -- However the party id could be either a Person Party Id or a Relationship Party Id
4755 -- for a B2B scenario.
4756
4757 IF p_data_source_code = G_PEROSN_LIST_DATA_SRC
4758 THEN
4759 -- person party id
4760 l_bind_vars(1) := G_BIND_VAR_AMSP ;
4761 ELSIF p_data_source_code IN ( G_PERSON_PHONE1_DATA_SRC , G_PERSON_PHONE2_DATA_SRC , G_PERSON_PHONE3_DATA_SRC , G_EMAIL_DATA_SRC , G_FAX_DATA_SRC )
4762 THEN
4763 -- contact party id
4764 l_bind_vars(1) := G_BIND_VAR_AMSCTP ;
4765 ELSIF p_data_source_code IN ( G_ORG_LIST_DATA_SRC )
4766 THEN
4767 -- organization party id
4768 l_bind_vars(1) := G_BIND_VAR_AMSORGP ;
4769 ELSIF p_data_source_code IN ( G_ORG_CONTACT_LIST_DATA_SRC )
4770 THEN
4771 -- relationship party id
4772 l_bind_vars(1) := G_BIND_VAR_AMSRELP ;
4773 ELSE
4774 -- For all other, we note it as amsp (person party id).
4775 -- When we restrict the Rich Content as well as Questions setion to show only specific
4776 -- Data Sources for the marketer to choose from, we are fine.
4777 -- When we open it up to all data sources, we will have issues.
4778 l_bind_vars(1) := G_BIND_VAR_AMSP ;
4779 END IF;
4780
4781 /*
4782 IF UPPER(l_source_object_pk_field) = 'PARTY_ID'
4783 THEN
4784 l_bind_vars(1) := G_BIND_VAR_AMSP;
4785 ELSE
4786 AMS_Utility_PVT.Error_Message('AMS_ERR_UNDEF_BIND_VAR');
4787 RAISE FND_API.g_exc_error;
4788 -- For user entered data sources, there has to be a way to define the bind variable
4789 -- name that we can use here.
4790 END IF;
4791 */
4792
4793 -- get the id of the list source type
4794 OPEN c_get_list_src_id (p_data_source_code);
4795 FETCH c_get_list_src_id INTO l_data_src_type_id ;
4796 CLOSE c_get_list_src_id ;
4797
4798 IF l_data_src_type_id IS NULL
4799 THEN
4800 AMS_Utility_PVT.Error_Message('AMS_ERR_NO_DATA_SRC_TYPE');
4801 RAISE FND_API.g_exc_error;
4802 END IF ;
4803
4804 -- For each field id in the incoming array, get the source_column_name
4805 -- if needed and form the select list.
4806
4807 l_fld_count := 0;
4808 l_select_clause := 'SELECT 1';
4809
4810 IF p_data_source_field_ids IS NOT NULL
4811 THEN
4812
4813 FOR i IN p_data_source_field_ids.first .. p_data_source_field_ids.last
4814 LOOP
4815 l_list_source_field_id := p_data_source_field_ids(i);
4819 CLOSE c_get_list_src_fld_data;
4816
4817 OPEN c_get_list_src_fld_data (p_data_source_code, l_list_source_field_id) ;
4818 FETCH c_get_list_src_fld_data INTO l_source_column_name ;
4820
4821 IF l_source_column_name IS NULL
4822 THEN
4823 AMS_Utility_PVT.Error_Message('AMS_ERR_NO_DATA_SRC_FLD');
4824 RAISE FND_API.g_exc_error;
4825 ELSE
4826
4827 -- Form the alias for the Column Name in the select list.
4828 l_fld_count := l_fld_count + 1;
4829
4830 --l_alias := substr((p_data_source_code || ':' || l_source_column_name), 1, 30);
4831 l_alias := substr((to_char(l_data_src_type_id) || ':' || l_source_column_name), 1, 30);
4832
4833 -- Changed the above once again to use the id of the list source type.
4834 -- GDEODHAR : May 06, 2002.
4835 -- Changed the above line to use substr.
4836 -- When the alias name exceeds 30 characters, it gives problem with the
4837 -- SQL statement execution at runtime.
4838 -- Now truncating the alias after 30th character.
4839
4840 l_data_src_fld_cols.extend();
4841 l_data_src_fld_cols(l_fld_count) := l_alias;
4842
4843 l_select_clause := l_select_clause || ' ,' || l_source_column_name || ' "' || l_alias || '"';
4844
4845 END IF;
4846
4847 END LOOP;
4848
4849 ELSE
4850 IF p_data_source_fields_list IS NOT NULL
4851 THEN
4852 FOR i IN p_data_source_fields_list.first .. p_data_source_fields_list.last
4853 LOOP
4854 l_list_source_field := p_data_source_fields_list(i);
4855
4856 -- The format of l_list_source_field is :
4857 -- DATA_SOURCE_TYPE_CODE:SOURCE_COLUMN_NAME.
4858 -- Extract the column name.
4859
4860 l_source_column_name := SUBSTR(l_list_source_field, INSTR(l_list_source_field,':') + 1) ;
4861
4862 IF l_source_column_name = ''
4863 THEN
4864 AMS_Utility_PVT.Error_Message('AMS_ERR_MALFORMED_FLD_NAME');
4865 RAISE FND_API.g_exc_error;
4866 ELSE
4867 l_fld_count := l_fld_count + 1;
4868
4869 l_alias := substr(l_list_source_field, 1, 30) ;
4870
4871 -- Note that this is Rich Content section and the change for using
4872 -- list source type id instead of the name has already happened
4873 -- when this code is invoked. So no need to change anything here.
4874
4875 -- GDEODHAR : May 06, 2002.
4876 -- Changed the above line to use substr.
4877 -- When the alias name exceeds 30 characters, it gives problem with the
4878 -- SQL statement execution at runtime.
4879 -- Now truncating the alias after 30th character.
4880
4881 l_data_src_fld_cols.extend();
4882 l_data_src_fld_cols(l_fld_count) := l_alias;
4883
4884 l_select_clause := l_select_clause || ' ,' || l_source_column_name || ' "' || l_alias || '"';
4885 END IF;
4886
4887 END LOOP;
4888 ELSE
4889 -- Cannot form the select statement, as no fields list available.
4890 AMS_Utility_PVT.Error_Message('AMS_ERR_NO_FLDS_FOR_GEN_SQL');
4891 RAISE FND_API.g_exc_error;
4892 END IF;
4893
4894 END IF;
4895
4896 l_select_sql_statement := l_select_clause || ' ' || l_from_clause || ' ' || l_where_clause;
4897 --
4898 -- There is nothing to commit, as we did not change any data.
4899 --
4900 x_return_status := l_return_status ;
4901 x_select_sql_statement := l_select_sql_statement ;
4902 x_bind_vars := l_bind_vars ;
4903 --
4904 -- Standard call to get message count and if count is 1, get message info.
4905 FND_MSG_PUB.count_and_get(
4906 p_encoded => FND_API.G_FALSE,
4907 p_count => x_msg_count,
4908 p_data => x_msg_data
4909 );
4910 --
4911 EXCEPTION
4912 WHEN FND_API.g_exc_error THEN
4913 ROLLBACK;
4914 x_return_status := FND_API.G_RET_STS_ERROR;
4915 FND_MSG_PUB.count_and_get (
4916 p_count => x_msg_count,
4917 p_data => x_msg_data,
4918 p_encoded => FND_API.G_FALSE
4919 );
4920 WHEN FND_API.g_exc_unexpected_error THEN
4921 ROLLBACK;
4922 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4923 FND_MSG_PUB.count_and_get (
4924 p_count => x_msg_count,
4925 p_data => x_msg_data,
4926 p_encoded => FND_API.G_FALSE
4927 );
4928 WHEN OTHERS THEN
4929 ROLLBACK;
4930 x_return_status := FND_API.g_ret_sts_unexp_error;
4934 FND_MSG_PUB.count_and_get (
4931 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
4932 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
4933 END IF;
4935 p_count => x_msg_count,
4936 p_data => x_msg_data,
4937 p_encoded => FND_API.G_FALSE
4938 );
4939 --
4940 END generate_select_sql;
4941
4942
4943
4944
4945
4946 -----------------------------------------------------------------------
4947 -- PROCEDURE
4948 -- create_submit_section
4949 --
4950 -- PURPOSE
4951 -- Create content item of type SUBMIT_SECTION.
4952 --
4953 -- NOTES
4954 -- 1. The required input is as follows:
4955 -- Content Type code. This must be SUBMIT_SECTION.
4956 -- Name of the Submit Section.
4957 -- Owner Resource Id.
4958 -- Resource Type.
4959 -- Content Item Id for the Parent Content Item associated with the parent deliverable.
4960 -- Content Item Version Id for the Parent Content Item.
4961 -- Content Type Code for the Parent Content Item.
4962 -- 2. The optional input is as follows:
4963 -- Description.
4964 -- 3. This procedure performs the following steps:
4965 -- 1. Create a Content Item of type SUBMIT_SECTION using Bulk Insert.
4966 -- 2. Create compound relation with the parent.
4967 -- 4. This procedure returns the fact that it is successful, it also returns the
4968 -- newly created Content Item Id.
4969 --
4970 -- HISTORY
4971 -- 10-MAR-2002 gdeodhar Created.
4972 --
4973 -----------------------------------------------------------------------
4974
4975 PROCEDURE create_submit_section(
4976 p_delv_id IN NUMBER,
4977 p_content_type_code IN VARCHAR2,
4978 p_content_item_name IN VARCHAR2,
4979 p_description IN VARCHAR2,
4980 p_owner_resource_id IN NUMBER,
4981 p_owner_resource_type IN VARCHAR2,
4982 p_reusable_flag IN VARCHAR2 DEFAULT FND_API.g_false, -- CHANGE to Y or N when IBC folks change the conventions for varchar2 fields.
4983 p_attribute_type_code IN VARCHAR2,
4984 p_parent_citem_id IN NUMBER,
4985 p_parent_citem_ver_id IN NUMBER,
4986 p_parent_ctype_code IN VARCHAR2,
4987 p_ui_control_type IN VARCHAR2 DEFAULT G_DEF_UI_FOR_SUBMIT,
4988 p_button_label IN VARCHAR2,
4989 p_ocm_image_id IN NUMBER DEFAULT NULL,
4990 p_alignment IN VARCHAR2 DEFAULT G_DEF_ALIGN_FOR_SUBMIT,
4991 p_commit IN VARCHAR2 DEFAULT FND_API.g_false,
4992 p_api_version IN NUMBER DEFAULT 1.0,
4993 p_api_validation_level IN NUMBER DEFAULT FND_API.g_valid_level_full,
4994 x_citem_id OUT NUMBER,
4995 x_citem_ver_id OUT NUMBER,
4996 x_return_status OUT VARCHAR2,
4997 x_msg_count OUT NUMBER,
4998 x_msg_data OUT VARCHAR2
4999 )
5000 IS
5001 --
5002 -- Declare the local variables and cursors here.
5003 --
5004 CURSOR c_delv_details IS
5005 SELECT actual_avail_from_date
5006 ,actual_avail_to_date
5007 FROM ams_deliverables_vl
5008 WHERE deliverable_id = p_delv_id ;
5009 --
5010 l_start_date DATE ;
5011 l_end_date DATE ;
5012 --
5013 l_citem_ver_id NUMBER ;
5014 l_citem_id NUMBER ;
5015 l_return_status VARCHAR2(1) ;
5016 l_msg_count NUMBER ;
5017 l_msg_data VARCHAR2(2000) ;
5018 --
5019 l_obj_ver_num NUMBER ;
5020 --
5021 l_parent_obj_ver_num NUMBER ;
5022 l_parent_citem_ver_id NUMBER ;
5023 --
5024 l_attribute_type_codes JTF_VARCHAR2_TABLE_100 := JTF_VARCHAR2_TABLE_100() ;
5025 l_attributes JTF_VARCHAR2_TABLE_4000 := JTF_VARCHAR2_TABLE_4000() ;
5026 --
5027 l_citem_ids JTF_NUMBER_TABLE := JTF_NUMBER_TABLE() ;
5028 l_citem_attrs JTF_VARCHAR2_TABLE_100 := JTF_VARCHAR2_TABLE_100() ;
5029 l_dummy_sort_order JTF_NUMBER_TABLE := JTF_NUMBER_TABLE() ;
5030 --
5031 l_api_name CONSTANT VARCHAR2(30) := 'Create_Submit_Section';
5032 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
5033 --
5034 l_init_msg_list VARCHAR2(1) := FND_API.g_true;
5035 --
5036 l_api_version_number CONSTANT NUMBER := 1.0;
5037 --
5038 BEGIN
5039 --
5040 -- Standard Start of API savepoint
5041 SAVEPOINT create_submit_section_PVT ;
5042
5043 -- Standard call to check for call compatibility.
5044 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
5045 p_api_version,
5046 l_api_name,
5047 G_PKG_NAME)
5048 THEN
5052 -- Initialize message list if p_init_msg_list is set to TRUE.
5049 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5050 END IF;
5051
5053
5054 --l_init_msg_list := p_init_msg_list;
5055
5056 IF FND_API.to_Boolean( l_init_msg_list )
5057 THEN
5058 FND_MSG_PUB.initialize;
5059 END IF;
5060
5061 l_init_msg_list := FND_API.g_false;
5062
5063 -- Debug Message
5064 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
5065
5066 -- Initialize API return status to SUCCESS
5067 x_return_status := FND_API.G_RET_STS_SUCCESS;
5068 l_return_status := FND_API.G_RET_STS_SUCCESS;
5069 --
5070 --
5071 IF p_content_type_code <> G_SUBMIT_SECTION -- Should be a CONSTANT in the package.
5072 THEN
5073 AMS_Utility_PVT.Error_Message('AMS_ERR_WRONG_CTYPE_CODE');
5074 RAISE FND_API.g_exc_error;
5075 END IF;
5076 --
5077 x_return_status := FND_API.g_ret_sts_success;
5078
5079 -- Fetch the Deliverable Details.
5080 OPEN c_delv_details;
5081 FETCH c_delv_details INTO l_start_date, l_end_date;
5082 CLOSE c_delv_details;
5083
5084 -- prepare the data for insert.
5085 --
5086 l_attribute_type_codes.extend() ;
5087 l_attribute_type_codes(1) := G_UI_CONTROL_TYPE ; -- Should be a CONSTANT in package.
5088 --
5089 l_attributes.extend() ;
5090 l_attributes(1) := p_ui_control_type ;
5091 --
5092 l_attribute_type_codes.extend() ;
5093 l_attribute_type_codes(2) := G_BUTTON_LABEL; -- Should be a CONSTANT in package.
5094 --
5095 l_attributes.extend() ;
5096 l_attributes(2) := p_button_label ;
5097 --
5098 l_attribute_type_codes.extend() ;
5099 l_attribute_type_codes(3) := G_OCM_IMAGE_ID ; -- Should be a CONSTANT in package.
5100 --
5101 l_attributes.extend();
5102 l_attributes(3) := p_ocm_image_id ;
5103 --
5104 l_attribute_type_codes.extend() ;
5105 l_attribute_type_codes(4) := G_ALIGNMENT ; -- Should be a CONSTANT in package.
5106 --
5107 l_attributes.extend();
5108 l_attributes(4) := p_alignment ;
5109 --
5110 -- Create a new Content Item in IBC Schema for incoming Content Type.
5111 -- Call upsert_item.
5112
5113 IBC_CITEM_ADMIN_GRP.upsert_item(
5114 p_ctype_code => G_SUBMIT_SECTION
5115 ,p_citem_name => p_content_item_name
5116 ,p_citem_description => NULL -- We do not expose the description in the UI for now.
5117 ,p_dir_node_id => G_AMS_DIR_NODE_ID
5118 ,p_owner_resource_id => p_owner_resource_id
5119 ,p_owner_resource_type => p_owner_resource_type
5120 ,p_reference_code => NULL -- Why is this needed?
5121 ,p_trans_required => FND_API.g_false -- This is the default value. For now we do not expose this flag on the UI.
5122 ,p_parent_item_id => p_parent_citem_id -- Specify the parent content item id. This item is visible only in the context of this parent.
5123 ,p_lock_flag => g_lock_flag_value
5124 ,p_wd_restricted => g_wd_restricted_flag_value
5125 ,p_start_date => l_start_date
5126 ,p_end_date => l_end_date
5127 ,p_attach_file_id => NULL -- We do not have any attachment while creating the basic Questions item.
5128 ,p_attribute_type_codes => l_attribute_type_codes
5129 ,p_attributes => l_attributes
5130 ,p_component_citems => NULL
5131 ,p_component_atypes => NULL
5132 ,p_sort_order => NULL
5133 ,p_status => G_CITEM_WIP_STATUS_CODE -- When the Deliverable becomes active, we will go in and approve all the underlying content items.
5134 ,p_log_action => FND_API.g_true -- This to be sent as TRUE. It updates the Audit Logs.
5135 ,p_commit => FND_API.g_false -- We still have to do some more operations.
5136 ,p_api_version_number => p_api_version
5137 ,p_init_msg_list => l_init_msg_list
5138 ,px_content_item_id => l_citem_id
5139 ,px_citem_ver_id => l_citem_ver_id
5140 ,px_object_version_number => l_obj_ver_num
5141 ,x_return_status => l_return_status
5142 ,x_msg_count => l_msg_count
5143 ,x_msg_data => l_msg_data
5144 );
5145
5146 AMS_UTILITY_PVT.debug_message('After upsert_item.');
5147 AMS_UTILITY_PVT.debug_message('l_citem_id = ' || l_citem_id);
5148 AMS_UTILITY_PVT.debug_message('l_citem_ver_id = ' || l_citem_ver_id);
5149 AMS_UTILITY_PVT.debug_message('l_obj_ver_num = ' || l_obj_ver_num);
5150 AMS_UTILITY_PVT.debug_message('l_return_status = ' || l_return_status);
5151
5152 IF FND_API.g_ret_sts_success <> l_return_status
5153 THEN
5154 AMS_Utility_PVT.Error_Message('AMS_ERR_CRE_SUBMIT_CITEM');
5155 RAISE FND_API.g_exc_error;
5156 END IF;
5157
5158 -- Create the Compound Relation with the parent.
5159 IF p_parent_citem_ver_id IS NOT NULL
5160 AND
5161 p_parent_ctype_code IS NOT NULL
5162 AND
5163 p_attribute_type_code IS NOT NULL
5164 THEN
5165 -- prepare the data for insert.
5166 l_citem_attrs.extend();
5167 l_citem_attrs(1) := p_attribute_type_code;
5168 --
5169 l_citem_ids.extend();
5170 l_citem_ids(1) := l_citem_id;
5171 --
5172 l_dummy_sort_order.extend();
5173 l_dummy_sort_order(1) := 1;
5174 --
5175
5176 IF g_using_locking = FND_API.g_true
5177 THEN
5178
5179 -- We need to lock the content item before we can add components to it.
5180
5184 ,p_api_version_number => p_api_version
5181 IBC_CITEM_ADMIN_GRP.lock_item(
5182 p_content_item_id => p_parent_citem_id
5183 ,p_commit => g_commit_on_lock_unlock
5185 ,p_init_msg_list => l_init_msg_list
5186 ,x_citem_version_id => l_parent_citem_ver_id
5187 ,x_object_version_number => l_parent_obj_ver_num
5188 ,x_return_status => l_return_status
5189 ,x_msg_count => l_msg_count
5190 ,x_msg_data => l_msg_data
5191 );
5192
5193 IF FND_API.g_ret_sts_success <> l_return_status
5194 THEN
5195 AMS_Utility_PVT.Error_Message('AMS_ERR_LOCKING_CITEM');
5196 RAISE FND_API.g_exc_error;
5197 END IF;
5198
5199 IF l_parent_citem_ver_id <> p_parent_citem_ver_id
5200 THEN
5201 AMS_Utility_PVT.Error_Message('AMS_ERR_CITEM_VER_MISMATCH');
5202 RAISE FND_API.g_exc_error;
5203 END IF;
5204
5205 ELSE
5206
5207 -- We have to get the object version number separately as we are not using locking.
5208 l_parent_obj_ver_num := IBC_CITEM_ADMIN_GRP.getObjVerNum( p_parent_citem_id );
5209
5210 END IF;
5211
5212 AMS_Utility_PVT.Debug_Message(' p_parent_citem_ver_id = ' || p_parent_citem_ver_id );
5213 AMS_Utility_PVT.Debug_Message(' obj ver num = ' || l_parent_obj_ver_num );
5214
5215
5216 -- insert compound relations.
5217 -- If we use locking, the parent content item and the children content items
5218 -- must all be locked by the same user in order to get the insert_components
5219 -- work correctly.
5220
5221 IBC_CITEM_ADMIN_GRP.insert_components(
5222 p_citem_ver_id => p_parent_citem_ver_id
5223 ,p_content_item_ids => l_citem_ids
5224 ,p_attribute_type_codes => l_citem_attrs
5225 ,p_sort_order => l_dummy_sort_order -- The NULL does not work. -- The new API is supposed to be able to take NULL for this parameter.
5226 ,p_commit => FND_API.g_false
5227 ,p_api_version_number => p_api_version
5228 ,p_init_msg_list => l_init_msg_list
5229 ,x_return_status => l_return_status
5230 ,x_msg_count => l_msg_count
5231 ,x_msg_data => l_msg_data
5232 );
5233
5234 IF FND_API.g_ret_sts_success <> l_return_status
5235 THEN
5236 AMS_Utility_PVT.Error_Message('AMS_ERR_ADD_COMPOUND_REL');
5237 RAISE FND_API.g_exc_error;
5238 END IF;
5239
5240 IF g_using_locking = FND_API.g_true
5241 THEN
5242
5243 -- Unlock the parent content item.
5244
5245 IBC_CITEM_ADMIN_GRP.unlock_item(
5246 p_content_item_id => p_parent_citem_id
5247 ,p_commit => g_commit_on_lock_unlock
5248 ,p_api_version_number => p_api_version
5249 ,p_init_msg_list => l_init_msg_list
5250 ,x_return_status => l_return_status
5251 ,x_msg_count => l_msg_count
5252 ,x_msg_data => l_msg_data
5253 );
5254
5255 AMS_UTILITY_PVT.debug_message('After Unlock.');
5256 AMS_UTILITY_PVT.debug_message('l_return_status = ' || l_return_status);
5257
5258 IF FND_API.g_ret_sts_success <> l_return_status
5259 THEN
5260 AMS_Utility_PVT.Error_Message('AMS_ERR_UNLOCK_CITEM');
5261 RAISE FND_API.g_exc_error;
5262 END IF;
5263
5264 END IF;
5265
5266 END IF;
5267 --
5268 --
5269 IF g_using_locking = FND_API.g_true
5270 THEN
5271
5272 -- Unlock the Submit Section content item.
5273 IBC_CITEM_ADMIN_GRP.unlock_item(
5274 p_content_item_id => l_citem_id
5275 ,p_commit => g_commit_on_lock_unlock
5276 ,p_api_version_number => p_api_version
5277 ,p_init_msg_list => l_init_msg_list
5278 ,x_return_status => l_return_status
5279 ,x_msg_count => l_msg_count
5280 ,x_msg_data => l_msg_data
5281 );
5282
5283 AMS_UTILITY_PVT.debug_message('After Unlock.');
5284 AMS_UTILITY_PVT.debug_message('l_return_status = ' || l_return_status);
5285
5286 IF FND_API.g_ret_sts_success <> l_return_status
5287 THEN
5288 AMS_Utility_PVT.Error_Message('AMS_ERR_UNLOCK_CITEM');
5289 RAISE FND_API.g_exc_error;
5290 END IF;
5291
5292 END IF;
5293
5294 -- If we come till here, everything has been created successfully.
5295 -- Commit the work and set the output values.
5296 --
5297 -- Standard check for p_commit
5298 IF FND_API.to_Boolean( p_commit )
5299 THEN
5300 COMMIT WORK;
5301 END IF;
5302 --
5303 x_return_status := l_return_status;
5304 x_citem_id := l_citem_id;
5305 x_citem_ver_id := l_citem_ver_id;
5306 --
5307 -- Standard call to get message count and if count is 1, get message info.
5308 FND_MSG_PUB.count_and_get(
5309 p_count => x_msg_count,
5310 p_data => x_msg_data
5311 );
5312 --
5313 EXCEPTION
5314 WHEN FND_API.g_exc_error THEN
5315 ROLLBACK TO create_submit_section_PVT ;
5316 x_return_status := FND_API.G_RET_STS_ERROR;
5317 FND_MSG_PUB.count_and_get (
5318 p_count => x_msg_count,
5319 p_data => x_msg_data,
5320 p_encoded => FND_API.G_FALSE
5324 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5321 );
5322 WHEN FND_API.g_exc_unexpected_error THEN
5323 ROLLBACK TO create_submit_section_PVT ;
5325 FND_MSG_PUB.count_and_get (
5326 p_count => x_msg_count,
5327 p_data => x_msg_data,
5328 p_encoded => FND_API.G_FALSE
5329 );
5330 WHEN OTHERS THEN
5331 ROLLBACK TO create_submit_section_PVT ;
5332 x_return_status := FND_API.g_ret_sts_unexp_error;
5333 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
5334 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
5335 END IF;
5336 FND_MSG_PUB.count_and_get (
5337 p_count => x_msg_count,
5338 p_data => x_msg_data,
5339 p_encoded => FND_API.G_FALSE
5340 );
5341
5342 END create_submit_section;
5343
5344
5345
5346
5347
5348
5349 -----------------------------------------------------------------------
5350 -- PROCEDURE
5351 -- update_submit_section
5352 --
5353 -- PURPOSE
5354 -- Update content item of type SUBMIT_SECTION.
5355 --
5356 -- NOTES
5357 -- 1. The required input is as follows:
5358 -- Content Type code. This must be SUBMIT_SECTION.
5359 -- Name of the Submit Section.
5360 -- Content Item Id for the section.
5361 -- Content Item Version Id for the section.
5362 -- 2. The optional input is as follows:
5363 -- Description.
5364 -- Other data that needs changes.
5365 -- 3. This procedure performs the following steps:
5366 -- 1. Update a Content Item of type SUBMIT_SECTION.
5367 -- 4. This procedure returns the fact that it is successful
5368 --
5369 -- HISTORY
5370 -- 11-MAR-2002 gdeodhar Created.
5371 --
5372 -----------------------------------------------------------------------
5373
5374 PROCEDURE update_submit_section(
5375 p_delv_id IN NUMBER,
5376 p_content_type_code IN VARCHAR2,
5377 p_content_item_name IN VARCHAR2,
5378 p_description IN VARCHAR2,
5379 p_citem_id IN NUMBER,
5380 p_citem_ver_id IN NUMBER,
5381 p_ui_control_type IN VARCHAR2,
5382 p_button_label IN VARCHAR2,
5383 p_ocm_image_id IN NUMBER,
5384 p_alignment IN VARCHAR2,
5385 p_commit IN VARCHAR2 DEFAULT FND_API.g_false,
5386 p_api_version IN NUMBER DEFAULT 1.0,
5387 p_api_validation_level IN NUMBER DEFAULT FND_API.g_valid_level_full,
5388 x_return_status OUT VARCHAR2,
5389 x_msg_count OUT NUMBER,
5390 x_msg_data OUT VARCHAR2
5391 )
5392 IS
5393 --
5394 -- Declare the local variables and cursors here.
5395 --
5396 l_return_status VARCHAR2(1) ;
5397 l_msg_count NUMBER ;
5398 l_msg_data VARCHAR2(2000) ;
5399 --
5400 l_obj_ver_num NUMBER ;
5401 --
5402 l_citem_id NUMBER ;
5403 l_citem_ver_id NUMBER ;
5404 l_obj_ver_num NUMBER ;
5405 --
5406 l_attribute_type_codes JTF_VARCHAR2_TABLE_100 := JTF_VARCHAR2_TABLE_100() ;
5407 l_attributes JTF_VARCHAR2_TABLE_4000 := JTF_VARCHAR2_TABLE_4000() ;
5408 --
5409 l_api_name CONSTANT VARCHAR2(30) := 'Update_Submit_Section';
5410 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
5411 --
5412 l_init_msg_list VARCHAR2(1) := FND_API.g_true;
5413 --
5414 l_attrib_count NUMBER := 0;
5415 --
5416 l_api_version_number CONSTANT NUMBER := 1.0;
5417 --
5418 BEGIN
5419 --
5420
5421 -- Standard call to check for call compatibility.
5422 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
5423 p_api_version,
5424 l_api_name,
5425 G_PKG_NAME)
5426 THEN
5427 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5428 END IF;
5429
5430 -- Initialize message list if p_init_msg_list is set to TRUE.
5431
5432 --l_init_msg_list := p_init_msg_list;
5433
5434 IF FND_API.to_Boolean( l_init_msg_list )
5435 THEN
5436 FND_MSG_PUB.initialize;
5437 END IF;
5438
5439 l_init_msg_list := FND_API.g_false;
5440
5441 -- Debug Message
5442 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
5443
5444 -- Initialize API return status to SUCCESS
5445 x_return_status := FND_API.G_RET_STS_SUCCESS;
5446 l_return_status := FND_API.G_RET_STS_SUCCESS;
5447 --
5448 --
5449 -- Form the arrays with incoming values.
5450 -- Note that at the time of Update all the values are required.
5451 -- So the Calling procedure must make sure that it sends the correct desired
5452 -- values for all the attributes.
5453 -- If incoming values are NULL, they will get updated as NULL.
5454 --
5455 -- UI Control Type
5456
5457 l_attrib_count := l_attrib_count + 1;
5458 l_attribute_type_codes.extend();
5459 l_attribute_type_codes(l_attrib_count) := G_UI_CONTROL_TYPE ;
5460
5461 l_attributes.extend();
5462 l_attributes(l_attrib_count) := p_ui_control_type ;
5463
5464 -- Button Label
5465
5466 l_attrib_count := l_attrib_count + 1;
5467 l_attribute_type_codes.extend();
5468 l_attribute_type_codes(l_attrib_count) := G_BUTTON_LABEL ;
5469
5470 l_attributes.extend();
5471 l_attributes(l_attrib_count) := p_button_label ;
5472
5473 -- OCM Image Id
5474
5478
5475 l_attrib_count := l_attrib_count + 1;
5476 l_attribute_type_codes.extend();
5477 l_attribute_type_codes(l_attrib_count) := G_OCM_IMAGE_ID ;
5479 l_attributes.extend();
5480 l_attributes(l_attrib_count) := p_ocm_image_id ;
5481
5482 -- Alignment
5483
5484 l_attrib_count := l_attrib_count + 1;
5485 l_attribute_type_codes.extend();
5486 l_attribute_type_codes(l_attrib_count) := G_ALIGNMENT ;
5487
5488 l_attributes.extend();
5489 l_attributes(l_attrib_count) := p_alignment ;
5490
5491 -- Call Update_content_item if needed.
5492 --
5493 IF l_attrib_count > 0
5494 THEN
5495
5496 update_content_item(
5497 p_citem_id => p_citem_id
5498 ,p_citem_version_id => p_citem_ver_id
5499 ,p_content_type_code => p_content_type_code
5500 ,p_content_item_name => p_content_item_name
5501 ,p_description => p_description
5502 ,p_delv_id => p_delv_id
5503 ,p_attr_types_for_update => l_attribute_type_codes
5504 ,p_attr_values_for_update => l_attributes
5505 ,p_attach_file_id => NULL
5506 ,p_attach_file_name => NULL
5507 ,p_commit => FND_API.g_false
5508 ,p_api_version => p_api_version
5509 ,p_api_validation_level => p_api_validation_level
5510 ,x_return_status => l_return_status
5511 ,x_msg_count => l_msg_count
5512 ,x_msg_data => l_msg_data
5513 );
5514
5515 END IF;
5516
5517 IF FND_API.g_ret_sts_success <> l_return_status
5518 THEN
5519 AMS_Utility_PVT.Error_Message('AMS_ERR_UPDATE_CITEM');
5520 RAISE FND_API.g_exc_error;
5521 END IF;
5522
5523 -- Commit the work and set the output values.
5524
5525 -- Standard check for p_commit
5526 IF FND_API.to_Boolean( p_commit )
5527 THEN
5528 COMMIT WORK;
5529 END IF;
5530
5531 x_return_status := l_return_status;
5532
5533 -- Standard call to get message count and if count is 1, get message info.
5534 FND_MSG_PUB.count_and_get(
5535 p_count => x_msg_count,
5536 p_data => x_msg_data
5537 );
5538
5539 EXCEPTION
5540 WHEN FND_API.g_exc_error THEN
5541 ROLLBACK ;
5542 x_return_status := FND_API.G_RET_STS_ERROR;
5543 FND_MSG_PUB.count_and_get (
5544 p_count => x_msg_count,
5545 p_data => x_msg_data,
5546 p_encoded => FND_API.G_FALSE
5547 );
5548 WHEN FND_API.g_exc_unexpected_error THEN
5549 ROLLBACK ;
5550 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5551 FND_MSG_PUB.count_and_get (
5552 p_count => x_msg_count,
5553 p_data => x_msg_data,
5554 p_encoded => FND_API.G_FALSE
5555 );
5556 WHEN OTHERS THEN
5557 ROLLBACK ;
5558 x_return_status := FND_API.g_ret_sts_unexp_error;
5559 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
5560 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
5561 END IF;
5562 FND_MSG_PUB.count_and_get (
5563 p_count => x_msg_count,
5564 p_data => x_msg_data,
5565 p_encoded => FND_API.G_FALSE
5566 );
5567
5568 END update_submit_section;
5569
5570
5571 -----------------------------------------------------------------------
5572 -- PROCEDURE
5573 -- update_questions_section
5574 --
5575 -- PURPOSE
5576 -- Update Questions Section Content Item.
5577 --
5578 -- NOTES
5579 -- 1. The required input is as follows:
5580 -- Deliverable ID.
5581 -- Content Item ID for the section.
5582 -- Content Item Version ID for the section.
5583 -- Content Type Code for the Section. This must be QUESTIONS.
5584 -- Content Type Name. (This is the same as Section Name when this item is created in the context of a parent content item).
5585 -- Attachment File ID that has the XML Data.
5586 -- Attachment File Name.
5587 -- 2. The optional input is as follows:
5588 -- Description.
5589 -- 3. This procedure performs the following steps:
5590 -- 1. Arrive at SELECT SQL Statements for each Data Source used in the section.
5591 -- 2. Arrive at the FUNCTIONAL_TYPE value.
5592 -- 3. Set the Attachment for this Content Item.
5593 -- 4. Set the Attribute Bundle for this Content Item.
5594 -- This will consist of the following attributes:
5595 -- SELECT_SQL_STATEMENT (s) : These could be many.
5596 -- FUNCTIONAL_TYPE
5597 -- 5. Delete the Data Source Usages records if already available.
5598 -- 6. Insert the Data Source Usages records for the Data Sources used.
5599 -- 4. This procedure returns the fact that it is successful.
5600 --
5601 -- HISTORY
5602 -- 24-MAR-2002 gdeodhar Created.
5603 --
5604 -----------------------------------------------------------------------
5605 PROCEDURE update_questions_section(
5606 p_delv_id IN NUMBER,
5607 p_section_citem_id IN NUMBER,
5608 p_section_citem_ver_id IN NUMBER,
5609 p_content_type_code IN VARCHAR2,
5610 p_content_item_name IN VARCHAR2,
5611 p_description IN VARCHAR2,
5612 p_attach_file_id IN NUMBER,
5613 p_attach_file_name IN VARCHAR2,
5614 p_commit IN VARCHAR2 DEFAULT FND_API.g_false,
5615 p_api_version IN NUMBER DEFAULT 1.0,
5619 x_msg_count OUT NUMBER,
5616 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.g_true,
5617 p_api_validation_level IN NUMBER DEFAULT FND_API.g_valid_level_full,
5618 x_return_status OUT VARCHAR2,
5620 x_msg_data OUT VARCHAR2
5621 )
5622 IS
5623 --
5624 -- Declare the local variables and cursors here.
5625 -- test cursor for file id.
5626
5627 CURSOR c_fild_id_check IS
5628 SELECT file_id, file_name
5629 FROM fnd_lobs
5630 WHERE file_id = p_attach_file_id ;
5631
5632 l_test_file_id NUMBER ;
5633 l_test_file_name VARCHAR2(250) ;
5634
5635 -- Cursor to select the latest citem version for a content item.
5636 --
5637 CURSOR c_max_version IS
5638 SELECT MAX(citem_version_id)
5639 FROM ibc_citem_versions_b
5640 WHERE content_item_id = p_section_citem_id ;
5641
5642 l_section_citem_ver_id NUMBER ;
5643
5644 -- Cursor to select the Deliverable Details to record in the Content Item Data.
5645 --
5646 CURSOR c_delv_details IS
5647 SELECT actual_avail_from_date
5648 ,actual_avail_to_date
5649 FROM ams_deliverables_vl
5650 WHERE deliverable_id = p_delv_id ;
5651 --
5652 CURSOR c_profile_ques_count IS
5653 SELECT count(1)
5654 FROM ams_cpag_questions_b
5655 WHERE content_item_id = p_section_citem_id
5656 AND profile_field_id IS NOT NULL
5657 AND question_type NOT IN (G_QUESTIONNAIRE, G_SEPARATOR) ;
5658 --
5659 l_profile_ques_count NUMBER ;
5660 l_data_src_type_code VARCHAR2(240) ;
5661 l_data_src_fld_id NUMBER ;
5662 l_fld_cnt NUMBER ;
5663 l_data_src_cnt NUMBER ;
5664 --
5665 CURSOR c_data_src_codes IS
5666 SELECT DISTINCT de_list_source_type_code
5667 FROM ams_list_src_fields
5668 WHERE list_source_field_id IN
5669 (SELECT profile_field_id
5670 FROM ams_cpag_questions_b
5671 WHERE content_item_id = p_section_citem_id
5672 ) ;
5673 --
5674 CURSOR c_data_src_flds IS
5675 SELECT ques.profile_field_id
5676 FROM ams_cpag_questions_b ques, ams_list_src_fields flds
5677 WHERE ques.profile_field_id = flds.list_source_field_id
5678 AND flds.de_list_source_type_code = l_data_src_type_code
5679 AND ques.content_item_id = p_section_citem_id ;
5680 --
5681 CURSOR c_data_src_usage_count IS
5682 SELECT count(1)
5683 FROM ams_list_src_type_usages
5684 WHERE list_src_used_by_type = 'CPAGE'
5685 AND list_src_used_by_id = p_section_citem_id ;
5686
5687 l_data_src_usage_count NUMBER ;
5688
5689 --
5690 l_start_date DATE ;
5691 l_end_date DATE ;
5692 --
5693 l_return_status VARCHAR2(1) ;
5694 l_msg_count NUMBER ;
5695 l_msg_data VARCHAR2(2000) ;
5696 --
5697 l_err_msg VARCHAR2(4000);
5698 --
5699 l_obj_ver_num NUMBER ;
5700 --
5701 l_attribute_type_codes JTF_VARCHAR2_TABLE_100 := JTF_VARCHAR2_TABLE_100() ;
5702 l_attributes JTF_VARCHAR2_TABLE_4000 := JTF_VARCHAR2_TABLE_4000() ;
5703
5704 l_data_src_type_codes JTF_VARCHAR2_TABLE_300 := JTF_VARCHAR2_TABLE_300() ;
5705 --
5706 l_data_src_fld_ids JTF_NUMBER_TABLE ; -- This is initilized below.
5707 --
5708 l_init_msg_list VARCHAR2(1) := FND_API.g_true;
5709 --
5710 l_api_version_number CONSTANT NUMBER := 1.0;
5711 l_api_name CONSTANT VARCHAR2(30) := 'update_questions_section';
5712 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
5713 --
5714 l_attr_count NUMBER ;
5715
5716 l_bind_vars_list JTF_VARCHAR2_TABLE_300 := JTF_VARCHAR2_TABLE_300() ;
5717 l_select_sql_statement VARCHAR2(4000) ;
5718
5719 BEGIN
5720
5721 OPEN c_fild_id_check ;
5722 FETCH c_fild_id_check INTO l_test_file_id, l_test_file_name ;
5723 CLOSE c_fild_id_check ;
5724
5725 -- Debug Message
5726 AMS_UTILITY_PVT.debug_message(' l_test_file_id in update_questions_section = ' || l_test_file_id);
5727 AMS_UTILITY_PVT.debug_message(' l_test_file_name in update_questions_section = ' || l_test_file_name);
5728
5729 -- Standard call to check for call compatibility.
5730 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
5731 p_api_version,
5732 l_api_name,
5733 G_PKG_NAME)
5734 THEN
5735 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5736 END IF;
5737
5738 -- Initialize message list if p_init_msg_list is set to TRUE.
5739
5740 l_init_msg_list := p_init_msg_list;
5741
5742 IF FND_API.to_Boolean( l_init_msg_list )
5743 THEN
5744 FND_MSG_PUB.initialize;
5745 END IF;
5746
5747 l_init_msg_list := FND_API.g_false;
5748
5749 -- Debug Message
5750 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
5751
5752 -- Initialize API return status to SUCCESS
5753 x_return_status := FND_API.G_RET_STS_SUCCESS;
5754 l_return_status := FND_API.G_RET_STS_SUCCESS;
5755 --
5756 IF p_content_type_code <> G_QUESTIONS -- Should be a CONSTANT in the package.
5757 THEN
5758 AMS_Utility_PVT.Error_Message('AMS_ERR_WRONG_CTYPE_CODE');
5759 RAISE FND_API.g_exc_error;
5760 END IF;
5761
5762 -- check the version id.
5763
5764 OPEN c_max_version ;
5765 FETCH c_max_version INTO l_section_citem_ver_id ;
5766 CLOSE c_max_version ;
5767
5768 IF p_section_citem_ver_id IS NOT NULL
5769 THEN
5770 IF p_section_citem_ver_id <> l_section_citem_ver_id
5771 THEN
5772 AMS_Utility_PVT.Error_Message('AMS_ERR_CITEM_VER_MISMATCH');
5773 RAISE FND_API.g_exc_error;
5774 END IF;
5775 END IF;
5776 --
5777 -- This API performs the following tasks.
5778 -- 1. Arrive at SELECT SQL Statements for each Data Source used in the section.
5779 -- 2. Arrive at the FUNCTIONAL_TYPE value.
5780 -- 3. Set the Attachment for this Content Item.
5781 -- 4. Set the Attribute Bundle for this Content Item.
5782 -- This will consist of the following attributes:
5783 -- SELECT_SQL_QUERY (s) : These could be many.
5784 -- BIND_VAR (s) : Starting APR 30, 2002. These could also be many. There has to be one for each select sql statement.
5785 -- FUNCTIONAL_TYPE
5786 -- 5. Delete the Data Source Usages records if already available.
5787 -- 6. Insert the Data Source Usages records for the Data Sources used.
5788
5789 -- initialize attribute count.
5790
5791 l_attr_count := 0;
5792
5793 -- initialize data source count.
5794
5795 l_data_src_cnt := 0;
5796
5797 -- check if there are any questions based on data sources., if so, generate select
5798 -- sql statement and set into attributes.
5799
5800 OPEN c_profile_ques_count ;
5801 FETCH c_profile_ques_count INTO l_profile_ques_count ;
5802 CLOSE c_profile_ques_count ;
5803
5804 IF l_profile_ques_count IS NOT NULL
5805 AND
5806 l_profile_ques_count > 0
5807 THEN
5808
5809 -- Find out all the Data Sources that are used by the questions section.
5810 -- We have the profile_field_id fields in Questions table.
5811 -- select distinct de_list_source_type_code
5812 -- from ams_list_src_fields
5813 -- where list_source_field_id in
5814 -- (select profile_field_id
5815 -- from ams_cpag_questions_b
5816 -- where content_item_id = <incoming section citem id>
5817 -- )
5818
5819 OPEN c_data_src_codes ;
5820 LOOP
5821 FETCH c_data_src_codes INTO l_data_src_type_code ;
5822 EXIT WHEN c_data_src_codes%NOTFOUND ;
5823
5824 l_data_src_type_codes.extend() ;
5825 l_data_src_cnt := l_data_src_cnt + 1 ;
5826 l_data_src_type_codes(l_data_src_cnt) := l_data_src_type_code ;
5827
5828 -- Note that for the new PIN_CODE based data source, we do not
5829 -- need to do select ever, as the user will enter the data.
5830
5831 -- do not generate the select statement
5832 -- if the Data Source is not valid for generating
5833 -- select sql query.
5834
5835 IF l_data_src_type_code NOT IN (G_PIN_CODE_DATA_SRC, G_LEAD_QUAL_DATA_SRC)
5836 THEN
5837
5838 -- For this data source, pick all the questions that fall in this
5839 -- data source and make a list to send to generate_select_sql
5840 -- select ques.profile_field_id
5841 -- from ams_cpag_questions_b ques, ams_list_src_fields flds
5842 -- where ques.profile_field_id = flds.list_source_field_id
5843 -- and flds.de_list_source_type_code = <the data source type code>
5844
5845 l_fld_cnt := 0;
5846 l_data_src_fld_ids := JTF_NUMBER_TABLE();
5847
5848 OPEN c_data_src_flds ;
5849 LOOP
5850 FETCH c_data_src_flds INTO l_data_src_fld_id ;
5851 EXIT WHEN c_data_src_flds%NOTFOUND ;
5852
5853 l_data_src_fld_ids.extend() ;
5854 l_fld_cnt := l_fld_cnt + 1 ;
5855 l_data_src_fld_ids(l_fld_cnt) := l_data_src_fld_id ;
5856
5857 END LOOP ;
5858 CLOSE c_data_src_flds ;
5859
5860 -- Debug Message
5861 AMS_UTILITY_PVT.debug_message('Data Src : ' || l_data_src_type_code || ': # of fields : ' || l_fld_cnt );
5862
5863 IF l_fld_cnt > 0
5864 THEN
5868 p_data_source_code => l_data_src_type_code
5865 -- This means that we have some field ids for this data source.
5866 -- call generate_select_sql.
5867 generate_select_sql(
5869 ,p_data_source_fields_list => NULL
5870 ,p_data_source_field_ids => l_data_src_fld_ids
5871 ,x_select_sql_statement => l_select_sql_statement
5872 ,x_bind_vars => l_bind_vars_list
5873 ,x_return_status => l_return_status
5874 ,x_msg_count => l_msg_count
5875 ,x_msg_data => l_msg_data
5876 );
5877
5878 IF FND_API.g_ret_sts_success <> l_return_status
5879 THEN
5880 AMS_Utility_PVT.Error_Message('AMS_ERR_CPAGE_GEN_SQL');
5881 RAISE FND_API.g_exc_error;
5882 END IF;
5883
5884 AMS_UTILITY_PVT.debug_message( ' l_select_sql_statement = ' || l_select_sql_statement );
5885
5886 -- Set the attributes for Select SQL Statement and bind variables.
5887 IF l_select_sql_statement IS NOT NULL
5888 THEN
5889 l_attr_count := l_attr_count + 1;
5890 l_attribute_type_codes.extend();
5891 l_attribute_type_codes(l_attr_count) := G_SELECT_SQL_QUERY ;
5892
5893 l_attributes.extend();
5894 l_attributes(l_attr_count) := l_select_sql_statement ;
5895
5896 -- Note that for now, we only consider ONE bind variable per
5897 -- select SQL statement.
5898 -- More over, for Questions section, for now, we only have
5899 -- Party ID as the bind variable for all the data sources that
5900 -- can be used for questions section. So even if the Bind Var is
5901 -- repeating here, the bind variable will be the exact same.
5902 -- Check with Avijit and remove Bind Variables from QUESTIONS
5903 -- content type, or just add one bind variable as a hardcoded one
5904 -- for the entire QUESTIONS section.
5905
5906 -- Checked with Avijit and commented the following code on
5907 -- March 27th 2002.
5908 -- For now the Questions Section can have many Select SQL Queries,
5909 -- but has only one bind variable as party id.
5910
5911 IF l_bind_vars_list IS NOT NULL
5912 THEN
5913 FOR i IN l_bind_vars_list.first .. l_bind_vars_list.last
5914 LOOP
5915 l_attr_count := l_attr_count + 1;
5916 l_attribute_type_codes.extend();
5917 l_attribute_type_codes(l_attr_count) := G_BIND_VAR;
5918
5919 l_attributes.extend();
5920 l_attributes(l_attr_count) := l_bind_vars_list(i);
5921 END LOOP;
5922 END IF;
5923
5924 -- Uncommented the above code on April 30, 2002.
5925 -- Since we have to support B2B, B2C etc., there will be
5926 -- different bind variable names for each Select SQL
5927 -- statement.
5928 -- The Questions Content Type has been changed to accommodate
5929 -- many bind variables.
5930
5931 -- Note the the SQL Statement will have the Bind Variable
5932 -- followed by it. So the order will be maintained.
5933
5934 END IF;
5935
5936 END IF;
5937
5938 END IF ;
5939
5940 END LOOP ;
5941 CLOSE c_data_src_codes ;
5942
5943 END IF;
5944
5945 /*
5946 -- Hardcode one bind variable as amsp (Party Id)
5947 l_attr_count := l_attr_count + 1;
5948 l_attribute_type_codes.extend();
5949 l_attribute_type_codes(l_attr_count) := G_BIND_VAR ;
5950
5951 l_attributes.extend();
5952 l_attributes(l_attr_count) := G_BIND_VAR_AMSP ;
5953 */
5954 -- Commented the above code on APRIL 30, 2002. We not have a different bind
5955 -- variable for each Select SQL Statement.
5956
5957 -- At this point, we have all the select sql statements and bind variables generated.
5958
5959 -- Look at the collected data sources and decide on the FUNCTIONAL_TYPE.
5960 -- The possible values are :
5961 -- B2B, B2C, B2B_B2C, QUESTIONNAIRE, NORMAL (This is the default)
5962 -- If there are only B2B data sources associated with this questions section, then
5963 -- the functional type is B2B....and so on.
5964
5965 -- We are not actually using the FUNCTIONAL_TYPE anywhere in the Dialogs and Content
5966 -- Pages at this time.
5967
5968 -- So this part will be coded later. For now FUNCTIONAL_TYPE will be left as NORMAL.
5969
5970 l_attr_count := l_attr_count + 1;
5971 l_attribute_type_codes.extend();
5972 l_attribute_type_codes(l_attr_count) := G_FUNCTIONAL_TYPE ;
5973
5974 l_attributes.extend();
5975 l_attributes(l_attr_count) := G_DEFAULT_FUNCTIONAL_TYPE ;
5976
5977 -- At this point the attribute bundle is read for setting.
5978 -- And the attachment has to be set as well.
5979
5980 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name, 'Data for update');
5981 IF l_attribute_type_codes IS NOT NULL
5982 AND
5983 l_attributes IS NOT NULL
5984 THEN
5985 FOR i IN l_attribute_type_codes.first .. l_attribute_type_codes.last
5986 LOOP
5987 l_err_msg := i || ' : >' || l_attribute_type_codes(i) || '< : >' || l_attributes(i) || '<';
5988 AMS_UTILITY_PVT.debug_message(l_err_msg);
5989 END LOOP;
5990 END IF;
5991
5995 p_citem_id => p_section_citem_id
5992 -- call update_content_item.
5993
5994 update_content_item(
5996 ,p_citem_version_id => l_section_citem_ver_id
5997 ,p_content_type_code => p_content_type_code
5998 ,p_content_item_name => NULL -- We do not allow update on this one yet.
5999 ,p_description => NULL -- We do not allow update on this one yet.
6000 ,p_delv_id => p_delv_id
6001 ,p_attr_types_for_update => l_attribute_type_codes
6002 ,p_attr_values_for_update => l_attributes
6003 ,p_attach_file_id => p_attach_file_id
6004 ,p_attach_file_name => p_attach_file_name
6005 ,p_commit => FND_API.g_false
6006 ,p_api_version => p_api_version
6007 ,p_api_validation_level => p_api_validation_level
6008 ,x_return_status => l_return_status
6009 ,x_msg_count => l_msg_count
6010 ,x_msg_data => l_msg_data
6011 ,p_replace_attr_bundle => FND_API.g_true
6012 );
6013
6014 AMS_UTILITY_PVT.debug_message( ' After update_content_item call. ' );
6015
6016 IF FND_API.g_ret_sts_success <> l_return_status
6017 THEN
6018 AMS_Utility_PVT.Error_Message('AMS_ERR_UPDATE_CITEM');
6019 RAISE FND_API.g_exc_error;
6020 END IF;
6021
6022 -- There is a bug in IBC Code. The field attachment_attribute_code in the
6023 -- table ibc_citem_versions_tl is getting updated some times. Filed a bug on IBC
6024 -- to track this issue.
6025 -- Bug # is : 2290924.
6026
6027 UPDATE ibc_citem_versions_tl
6028 SET attachment_attribute_code = 'QUESTIONS_XML' -- hardcoding as QUESTIONS_XML for Questions Section.
6029 WHERE citem_version_id = l_section_citem_ver_id ;
6030
6031 -- Delete all the records from ams_list_src_type_usages for this content item
6032
6033 OPEN c_data_src_usage_count ;
6034 FETCH c_data_src_usage_count INTO l_data_src_usage_count ;
6035 CLOSE c_data_src_usage_count ;
6036
6037 AMS_UTILITY_PVT.debug_message( ' l_data_src_usage_count = ' || l_data_src_usage_count );
6038
6039 IF l_data_src_usage_count > 0
6040 THEN
6041 DELETE FROM ams_list_src_type_usages
6042 WHERE list_src_used_by_type = 'CPAGE'
6043 AND list_src_used_by_id = p_section_citem_id;
6044 END IF;
6045
6046 AMS_UTILITY_PVT.debug_message( ' l_data_src_usage_count = ' || l_data_src_usage_count );
6047
6048 -- Insert into ams_list_src_type_usages the usage record for all the Data Sources
6049 -- being used by this content item.
6050
6051 IF l_data_src_cnt > 0
6052 THEN
6053
6054 FOR i IN l_data_src_type_codes.first .. l_data_src_type_codes.last
6055 LOOP
6056 AMS_UTILITY_PVT.debug_message( ' There are data sources. ' );
6057
6058 l_data_src_type_code := l_data_src_type_codes(i);
6059
6060 AMS_UTILITY_PVT.debug_message( ' Adding usage record for : l_data_src_type_code = ' || l_data_src_type_code );
6061
6062 INSERT INTO ams_list_src_type_usages
6063 (
6064 list_source_type_usage_id
6065 ,last_update_date
6066 ,last_updated_by
6067 ,creation_date
6068 ,created_by
6069 ,last_update_login
6070 ,object_version_number
6071 ,source_type_code
6072 ,list_header_id
6073 ,list_src_used_by_type
6074 ,list_src_used_by_id
6075 )
6076 select
6077 ams_list_src_type_usages_s.NEXTVAL,
6078 SYSDATE,
6079 FND_GLOBAL.USER_ID,
6080 SYSDATE,
6081 FND_GLOBAL.USER_ID,
6082 FND_GLOBAL.CONC_LOGIN_ID,
6083 1,
6084 substr(l_data_src_type_code, 1, 30),
6085 0, -- sending list_header_id as 0. -- The index AMS.AMS_LIST_SRC_TYPE_USAGES_U2 has to be dropped from the ODF for this to work correctly.
6086 'CPAGE',
6087 p_section_citem_id
6088 from dual
6089 where not exists
6090 ( select 'x'
6091 from ams_list_src_type_usages
6092 where list_header_id = 0
6093 and source_type_code = l_data_src_type_code
6094 and list_src_used_by_type = 'CPAGE'
6095 and list_src_used_by_id = p_section_citem_id
6096 ) ;
6097
6098 END LOOP;
6099 END IF;
6100
6101 -- If we come till here, everything has been updated successfully.
6102 -- Commit the work and set the output values.
6103 --
6104 -- Standard check for p_commit
6105 IF FND_API.to_Boolean( p_commit )
6106 THEN
6107 COMMIT WORK;
6108 END IF;
6109 --
6110 x_return_status := l_return_status ;
6111 --
6112 -- Standard call to get message count and if count is 1, get message info.
6113 FND_MSG_PUB.count_and_get(
6114 p_count => x_msg_count,
6115 p_data => x_msg_data
6116 );
6117 --
6118 EXCEPTION
6119 WHEN FND_API.g_exc_error THEN
6120 ROLLBACK ;
6121 x_return_status := FND_API.G_RET_STS_ERROR;
6122 FND_MSG_PUB.count_and_get (
6123 p_count => x_msg_count,
6124 p_data => x_msg_data,
6125 p_encoded => FND_API.G_FALSE
6126 );
6127 WHEN FND_API.g_exc_unexpected_error THEN
6128 ROLLBACK ;
6129 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6130 FND_MSG_PUB.count_and_get (
6131 p_count => x_msg_count,
6132 p_data => x_msg_data,
6136 ROLLBACK ;
6133 p_encoded => FND_API.G_FALSE
6134 );
6135 WHEN OTHERS THEN
6137 x_return_status := FND_API.g_ret_sts_unexp_error;
6138 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
6139 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
6140 END IF;
6141 FND_MSG_PUB.count_and_get (
6142 p_count => x_msg_count,
6143 p_data => x_msg_data,
6144 p_encoded => FND_API.G_FALSE
6145 );
6146 --
6147
6148 END update_questions_section ;
6149
6150
6151 PROCEDURE erase_blob(
6152 p_file_id IN NUMBER,
6153 x_blob OUT BLOB,
6154 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.g_true,
6155 x_return_status OUT VARCHAR2,
6156 x_msg_count OUT NUMBER,
6157 x_msg_data OUT VARCHAR2
6158 )
6159 IS
6160 l_blob BLOB;
6161 l_length NUMBER;
6162 l_api_name CONSTANT VARCHAR2(30) := 'erase_blob';
6163 BEGIN
6164 AMS_UTILITY_PVT.debug_message('enter erase');
6165
6166 IF FND_API.to_Boolean( p_init_msg_list )
6167 THEN
6168 FND_MSG_PUB.initialize;
6169 END IF;
6170
6171 x_return_status := FND_API.G_RET_STS_SUCCESS;
6172
6173 select file_data into l_blob
6174 from fnd_lobs
6175 where file_id = p_file_id
6176 for update;
6177
6178 l_length := dbms_lob.getlength(l_blob);
6179 AMS_UTILITY_PVT.debug_message('Length of lob erased : '||TO_CHAR(l_length));
6180
6181 dbms_lob.erase(l_blob,l_length,1);
6182 dbms_lob.trim(l_blob,0);
6183
6184 x_blob := l_blob;
6185
6186 AMS_UTILITY_PVT.debug_message('erase successful');
6187
6188 EXCEPTION
6189 WHEN FND_API.g_exc_error THEN
6190 ROLLBACK ;
6191 x_return_status := FND_API.G_RET_STS_ERROR;
6192 FND_MSG_PUB.count_and_get (
6193 p_count => x_msg_count,
6194 p_data => x_msg_data,
6195 p_encoded => FND_API.G_FALSE
6196 );
6197 WHEN FND_API.g_exc_unexpected_error THEN
6198 ROLLBACK ;
6199 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6200 FND_MSG_PUB.count_and_get (
6201 p_count => x_msg_count,
6202 p_data => x_msg_data,
6203 p_encoded => FND_API.G_FALSE
6204 );
6205 WHEN OTHERS THEN
6206 ROLLBACK ;
6207 x_return_status := FND_API.g_ret_sts_unexp_error;
6208 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
6209 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
6210 END IF;
6211 FND_MSG_PUB.count_and_get (
6212 p_count => x_msg_count,
6213 p_data => x_msg_data,
6214 p_encoded => FND_API.G_FALSE
6215 );
6216 END erase_blob;
6217
6218
6219
6220 END AMS_CPageUtility_PVT;
[Home] [Help]