1 PACKAGE AMS_CPageUtility_PVT AUTHID CURRENT_USER AS
2 /* $Header: amsvcpgs.pls 115.24 2002/06/09 17:55:24 pkm ship $ */
3
4 -- Define Constants used by the package.
5 G_AMS_DIR_NODE_ID CONSTANT NUMBER := 3 ; -- Changed on Mar 06 as the new directory node for CPAGE.
6 G_CPAGE_ASSOC_TYPE_CODE CONSTANT VARCHAR2(30) := 'CONTENT_FOR_OMO_CPAGE' ;
7 G_OWNER_RESOURCE_TYPE CONSTANT VARCHAR2(30) := 'RS_EMPLOYEE' ;
8 G_RICH_CONTENT CONSTANT VARCHAR2(30) := 'AMS_RICH_CONTENT' ;
9 G_HAS_MERGE_FIELDS CONSTANT VARCHAR2(30) := 'HAS_MERGE_FIELDS' ;
10 G_HAS_PAGE_MERGE_FIELDS CONSTANT VARCHAR2(30) := 'HAS_PAGE_MERGE_FIELDS' ;
11 G_FUNCTIONAL_TYPE CONSTANT VARCHAR2(30) := 'FUNCTIONAL_TYPE' ;
12 G_DATA_SOURCE CONSTANT VARCHAR2(30) := 'DATA_SOURCE' ;
13 G_MERGE_FIELD CONSTANT VARCHAR2(30) := 'MERGE_FIELD' ;
14 G_SELECT_SQL_QUERY CONSTANT VARCHAR2(30) := 'SELECT_SQL_QUERY' ; --used in AMS_QUESTIONS
15 G_SELECT_SQL_STATEMENT CONSTANT VARCHAR2(30) := 'SELECT_SQL_STATEMENT' ; --used in AMS_RICH_CONTENT
16 G_BIND_VAR CONSTANT VARCHAR2(30) := 'BIND_VAR' ;
17 G_DEFAULT_FUNCTIONAL_TYPE CONSTANT VARCHAR2(30) := 'NORMAL' ;
18 G_CP_IMAGE CONSTANT VARCHAR2(30) := 'AMS_CP_IMAGE' ;
19 G_QUESTIONS CONSTANT VARCHAR2(30) := 'AMS_QUESTIONS' ;
20 G_SUBMIT_SECTION CONSTANT VARCHAR2(30) := 'AMS_SUBMIT_SECTION' ;
21 G_DEF_UI_FOR_SUBMIT CONSTANT VARCHAR2(30) := 'BUTTON' ;
22 G_DEF_ALIGN_FOR_SUBMIT CONSTANT VARCHAR2(30) := 'CENTER' ;
23 G_IBC_IMAGE CONSTANT VARCHAR2(30) := 'IBC_IMAGE' ;
24 G_IBC_STYLESHEET CONSTANT VARCHAR2(30) := 'IBC_STYLESHEET' ;
25 G_CITEM_APPROVED_STATUS_CODE CONSTANT VARCHAR2(30) := IBC_UTILITIES_PUB.G_STV_APPROVED ;
26 G_CITEM_WIP_STATUS_CODE CONSTANT VARCHAR2(30) := IBC_UTILITIES_PUB.G_STV_WORK_IN_PROGRESS ;
27 G_DEFAULT_DISPLAY_TEMPLATE CONSTANT VARCHAR2(40) := 'DEFAULT_DISPLAY_TEMPLATE' ;
28 G_DELIVERY_CHANNEL CONSTANT VARCHAR2(30) := 'DELIVERY_CHANNEL' ;
29 G_OUTPUT_TYPE CONSTANT VARCHAR2(30) := 'OUTPUT_TYPE' ;
30 G_UI_CONTROL_TYPE CONSTANT VARCHAR2(30) := 'UI_CONTROL_TYPE' ;
31 G_OCM_IMAGE_ID CONSTANT VARCHAR2(30) := 'OCM_IMAGE_ID' ;
32 G_BUTTON_LABEL CONSTANT VARCHAR2(30) := 'BUTTON_LABEL' ;
33 G_ALIGNMENT CONSTANT VARCHAR2(30) := 'ALIGNMENT' ;
34 -- Bind Variables
35 G_BIND_VAR_AMSP CONSTANT VARCHAR2(30) := 'amsp';
36 G_BIND_VAR_AMSSC CONSTANT VARCHAR2(30) := 'amssc';
37 G_BIND_VAR_AMSRELP CONSTANT VARCHAR2(30) := 'amsrelp';
38 G_BIND_VAR_AMSORGP CONSTANT VARCHAR2(30) := 'amsorgp';
39 G_BIND_VAR_AMSADDRESSP CONSTANT VARCHAR2(30) := 'amsaddressp';
40 G_BIND_VAR_AMSCTP CONSTANT VARCHAR2(30) := 'amsctp';
41 G_BIND_VAR_AMSORGCT CONSTANT VARCHAR2(30) := 'amsorgct';
42 -- Data Sources
43 G_PEROSN_LIST_DATA_SRC CONSTANT VARCHAR2(30) := 'PERSON_LIST';
44 G_PERSON_PHONE1_DATA_SRC CONSTANT VARCHAR2(30) := 'PERSON_PHONE1';
45 G_PERSON_PHONE2_DATA_SRC CONSTANT VARCHAR2(30) := 'PERSON_PHONE2';
46 G_PERSON_PHONE3_DATA_SRC CONSTANT VARCHAR2(30) := 'PERSON_PHONE3';
47 G_EMAIL_DATA_SRC CONSTANT VARCHAR2(30) := 'EMAIL';
48 G_FAX_DATA_SRC CONSTANT VARCHAR2(30) := 'FAX';
49 G_ORG_LIST_DATA_SRC CONSTANT VARCHAR2(30) := 'ORGANIZATION_LIST';
50 G_ORG_CONTACT_LIST_DATA_SRC CONSTANT VARCHAR2(30) := 'ORGANIZATION_CONTACT_LIST';
51 G_PIN_CODE_DATA_SRC CONSTANT VARCHAR2(30) := 'OMO_PIN_CODE';
52 G_LEAD_QUAL_DATA_SRC CONSTANT VARCHAR2(30) := 'OMO_LEAD_QUALIFIER';
53 /*
54 Making changes in generate_sql_statement. April 30, 2002.
55 According to Avijit, for Runtime of Questions Section the bind variable name
56 has to be changed based on the Data Source column and field.
57 This change is fine, but it will not work for user defined data sources at all.
58 For address, amsp is good for address-es.
59 For email, fax, phone-s , please put amsctp
60 For company name, please put amsorgp - org party id,
61 For job title, amsrelp - relationshipparty id is good.
62 --
63 This is what the above translates to :
64 for PERSON_LIST as list_source_type, amsp should be used.
65 for PERSON_PHONE1 to PERSON_PHONE3 and EMAIL and FAX, amscpt (contant points) should be used.
66 When the same page is used in B2C Context, Avijit's runtime uses Person Party Id.
67 When the same page is used in B2B Context, Avijit's runtime uses Relationship Party Id.
68 We are not covering the Business Phones and other details as yet.
69 for ORGANIZATION_LIST as list_source_type, amsorgp should be used. This is Organization Party ID.
70 for ORGANIZATION_CONTACT_LIST as list_source_type, amsrelp should be used. This is Relationship Party ID.
71 */
72 G_QUESTIONNAIRE CONSTANT VARCHAR2(30) := 'QUESTIONNAIRE';
73 G_SEPARATOR CONSTANT VARCHAR2(30) := 'SEPARATOR';
74 G_TOC CONSTANT VARCHAR2(30) := 'AMS_TOC';
75
76 /*
77 Test script:
78 declare
79 --Make sure that ibc_association_types_b and tl table have valid data.
80 delvId number(15) := 10061;
81 cTypeCode varchar2(30) := 'TEST_COMPOUND_1';
82 defDispTempId number(15) := null;
83 assocTypeCode varchar2(30) := 'CONTENT_FOR_OMO_CPAGE';
84 commitFlag varchar2(1) := 'F';
85 apiVer number(10) := 1.0;
86 valLevel number(15) := 100;
87 cItemId number(15);
88 cItemVerId number(15);
89 status varchar2(1);
90 msgCnt number(10);
91 msgData varchar2(2000);
92 begin
93 AMS_CPageUtility_PVT.create_citem_for_delv(
94 p_content_type_code => cTypeCode
95 ,p_def_disp_template_id => defDispTempId
96 ,p_delv_id => delvId
97 ,p_assoc_type_code => assocTypeCode
98 ,p_commit => commitFlag
99 ,p_api_version => apiVer
100 ,p_api_validation_level => valLevel
101 ,x_citem_id => cItemId
102 ,x_citem_ver_id => cItemVerId
103 ,x_return_status => status
104 ,x_msg_count => msgCnt
105 ,x_msg_data => msgData
106 );
107 dbms_output.put_line('status = ' || status);
108 dbms_output.put_line('citemid = ' || cItemId);
109 dbms_output.put_line('citemverid = ' || cItemVerId);
110 end;
111 */
112 -----------------------------------------------------------------------
113 -- PROCEDURE
114 -- create_citem_for_delv
115 --
116 -- PURPOSE
117 -- Create a Content Item for Deliverable of type Content Page.
118 --
119 -- NOTES
120 -- 1. The required input is as follows:
121 -- content_type_code
122 -- default_display_template_id
123 -- deliverable_id
124 -- association_type_code (to be recorded in ibc_associations table)
125 -- 2. This procedure returns the Content Item ID of the newly created
126 -- Content Item associated with the given deliverable.
127 --
128 -----------------------------------------------------------------------
129 PROCEDURE create_citem_for_delv(
130 p_content_type_code IN VARCHAR2,
131 p_def_disp_template_id IN NUMBER,
132 p_delv_id IN NUMBER,
133 p_assoc_type_code IN VARCHAR2,
134 p_commit IN VARCHAR2 DEFAULT FND_API.g_false,
135 p_api_version IN NUMBER DEFAULT 1.0,
136 p_api_validation_level IN NUMBER DEFAULT FND_API.g_valid_level_full,
137 x_citem_id OUT NUMBER,
138 x_citem_ver_id OUT NUMBER,
139 x_return_status OUT VARCHAR2,
140 x_msg_count OUT NUMBER,
141 x_msg_data OUT VARCHAR2
142 );
143
144 -----------------------------------------------------------------------
145 -- PROCEDURE
146 -- approve_citem_for_delv
147 --
148 -- PURPOSE
149 -- Approve the Content Item associated with the Deliverable of type Content Page.
150 --
151 -- NOTES
152 -- 1. The required input is as follows:
153 -- content_type_code
154 -- deliverable_id
155 -- content_item_id
156 -- association_type_code (this is recorded in ibc_associations table)
157 -- 2. This procedure returns the success or failure status
158 --
159 -----------------------------------------------------------------------
160 PROCEDURE approve_citem_for_delv(
161 p_content_type_code IN VARCHAR2,
162 p_delv_id IN NUMBER,
163 p_citem_id IN NUMBER,
164 p_assoc_type_code IN VARCHAR2,
165 p_commit IN VARCHAR2 DEFAULT FND_API.g_false,
166 p_api_version IN NUMBER DEFAULT 1.0,
167 p_api_validation_level IN NUMBER DEFAULT FND_API.g_valid_level_full,
168 x_return_status OUT VARCHAR2,
169 x_msg_count OUT NUMBER,
170 x_msg_data OUT VARCHAR2
171 );
172
173
174 -----------------------------------------------------------------------
175 -- PROCEDURE
176 -- update_citem_for_delv
177 --
178 -- PURPOSE
179 -- Update the Content Item associated with the Deliverable of type Content Page.
180 --
181 -- NOTES
182 -- 1. The required input is as follows:
183 -- content_type_code
184 -- default_display_template_id
185 -- deliverable_id
186 -- content_item_id
187 -- association_type_code (this is recorded in ibc_associations table)
188 -- 2. This procedure returns the success or failure status
189 --
190 -----------------------------------------------------------------------
191 PROCEDURE update_citem_for_delv(
192 p_content_type_code IN VARCHAR2,
193 p_def_disp_template_id IN NUMBER,
194 p_delv_id IN NUMBER,
195 p_citem_id IN NUMBER,
196 p_assoc_type_code IN VARCHAR2,
197 p_commit IN VARCHAR2 DEFAULT FND_API.g_false,
198 p_api_version IN NUMBER DEFAULT 1.0,
199 p_api_validation_level IN NUMBER DEFAULT FND_API.g_valid_level_full,
200 x_return_status OUT VARCHAR2,
201 x_msg_count OUT NUMBER,
202 x_msg_data OUT VARCHAR2
203 );
204
205
206 -----------------------------------------------------------------------
207 -- PROCEDURE
208 -- manage_rich_content
209 --
210 -- PURPOSE
211 -- Manage a Rich Content Item.
212 --
213 -- NOTES
214 -- 1. The required input is as follows:
215 -- Content Type Code for the Section. This must be RICH_CONTENT.
216 -- Content Type Name. (This is the same as Section Name when this item is created in the context of a parent content item).
217 -- Attachment File ID that has the Rich Content Data.
218 -- Attachment File Name.
219 -- Start Date
220 -- End Date
221 -- Owner Resource ID
222 -- Owner Resource Type
223 -- Value for HAS_MERGE_FIELDS
224 -- Value for HAS_PAGE_MERGE_FIELDS
225 -- 2. The optional input is as follows:
226 -- Content Item Id : If given Update is done.
227 -- Content Item Version Id : If given Update is done.
228 -- Description.
229 -- Attribute Type Code for the Section.
230 -- The Content Item Version ID of the Parent Content Item
231 -- The Content Type Code associated with the Parent Content Item.
232 -- If the above two are available, this procedure will create a
233 -- compound relation between the Parent Content Item Version ID and
234 -- the Content Item ID of the newly created Content Item.
235 -- VARCHAR2 Array of Data Source Programmatic Access Codes.
236 -- VARCHAR2 Array of Merge Field names.
237 -- Note that these names contain the Programmatic Access Code for the
238 -- Data Source as well as the Column Name, separated by a period.
239 -- If this Array has data, the SELECT_SQL_QUERY type of Content Items
240 -- will be created for each of Data Sources that appear in the list.
241 -- The MERGE_FIELD Content Items will be created for each of the item
242 -- in the Array.
243 -- Compound relations will be created between the MERGE_FIELD items and
244 -- SELECT_SQL_QUERY items and between SELECT_SQL_QUERY items and the
245 -- newly created RICH_CONTENT item.
246 -- 3. This procedure performs the following steps:
247 -- 1. Create a Basic Content Item for Rich Content with insert_basic_citem
248 -- 2. Add the Meta Data with set_citem_meta.
249 -- 3. Set the Attachment for this Content Item.
250 -- 4. Set the Attribute Bundle for this Content Item.
251 -- Arrive at the value for FUNCTIONAL_TYPE.
252 -- This will consist of the following attributes:
253 -- HAS_MERGE_FIELDS
254 -- HAS_PAGE_MERGE_FIELDS
255 -- FUNCTIONAL_TYPE
256 -- 5. If the details of Parent Content Item are available,
257 -- create the compound relation between the parent content item and the
258 -- newly created RICH_CONTENT item.
259 -- 6. If the Merge Fields List is not empty, do the following:
260 -- Collect all the Merge Fields from one data source together.
261 -- For each such data source, do the following:
262 -- Create MERGE_FIELD Content Item for each Merge Field for this Data Source with an APPROVED status. Use BULK_INSERT.
263 -- Pick up the Field Type from Data Source schema.
264 -- Generate SQL Query for the resolution of these Merge Fields in APPROVED status. Use BULK_INSERT.
265 -- Create the SELECT_SQL_QUERY content item.
266 -- Create Compound Relations between the SELECT_SQL_QUERY and the MERGE_FIELD content items.
267 -- Create Compound Relations between the SELECT_SQL_QUERY items and the RICH_CONTENT content item.
268 -- 4. This procedure returns the fact that it is successful.
269 --
270 -- HISTORY
271 -- 14-FEB-2002 gdeodhar Created.
272 -- 11-MAR-2002 gdeodhar Added Update to the same method.
273 --
274 -----------------------------------------------------------------------
275 PROCEDURE manage_rich_content(
276 p_content_type_code IN VARCHAR2,
277 p_content_item_name IN VARCHAR2,
278 p_description IN VARCHAR2,
279 p_delv_id IN NUMBER,
280 p_attach_file_id IN NUMBER,
281 p_attach_file_name IN VARCHAR2,
282 p_owner_resource_id IN NUMBER,
283 p_owner_resource_type IN VARCHAR2,
284 p_has_merge_fields IN VARCHAR2,
285 p_has_page_merge_fields IN VARCHAR2,
286 p_reusable_flag IN VARCHAR2 DEFAULT FND_API.g_false, -- CHANGE to Y or N when IBC folks change the conventions for varchar2 fields.
287 p_data_source_list IN JTF_VARCHAR2_TABLE_300,
288 p_merge_fields_list IN JTF_VARCHAR2_TABLE_300,
289 p_attribute_type_code IN VARCHAR2,
290 p_parent_citem_id IN NUMBER,
291 p_parent_citem_ver_id IN NUMBER,
292 p_parent_ctype_code IN VARCHAR2,
293 p_commit IN VARCHAR2 DEFAULT FND_API.g_false,
294 p_api_version IN NUMBER DEFAULT 1.0,
295 p_api_validation_level IN NUMBER DEFAULT FND_API.g_valid_level_full,
296 px_citem_id IN OUT NUMBER,
297 px_citem_ver_id IN OUT NUMBER,
298 x_return_status OUT VARCHAR2,
299 x_msg_count OUT NUMBER,
300 x_msg_data OUT VARCHAR2,
301 p_dml_flag IN VARCHAR2,
302 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.g_true
303 );
304
305
306 -----------------------------------------------------------------------
307 -- PROCEDURE
308 -- manage_toc_section
309 --
310 -- PURPOSE
311 -- Manage a TOC Section Item.
312 --
313 -- NOTES
314 -- 1. The required input is as follows:
315 -- Content Type Code for the Section. This must be AMS_TOC.
316 -- Content Type Name. (This is the same as Section Name when this item is created in the context of a parent content item).
317 -- Start Date
318 -- End Date
319 -- Owner Resource ID
320 -- Owner Resource Type
321 -- 2. The optional input is as follows:
322 -- Content Item Id : If given Update is done.
323 -- Content Item Version Id : If given Update is done.
324 -- Description.
325 -- The Content Item Version ID of the Parent Content Item
326 -- The Content Type Code associated with the Parent Content Item.
327 -- If the above two are available, this procedure will create a
328 -- compound relation between the Parent Content Item Version ID and
329 -- the Content Item ID of the newly created Content Item.
330 -- VARCHAR2 caption.
331 -- VARCHAR2 list style.
332 -- VARCHAR2 Array of Attribute Type Codes.
333 -- VARCHAR2 Array of Attribute Values.
334 -- Attachment File ID that has TOC XML Data for runtime.
335 -- Attachment File Name.
336 -- 3. This procedure performs the following steps:
337 -- 1. Create a Basic Content Item for Rich Content with insert_basic_citem
338 -- 2. Add the Meta Data with set_citem_meta.
339 -- 3. Set the Attachment for this Content Item.
340 -- 4. Set the Attribute Bundle for this Content Item.
344 -- 4. This procedure returns the fact that it is successful.
341 -- 5. If the details of Parent Content Item are available,
342 -- create the compound relation between the parent content item and the
343 -- newly created TOC item.
345 --
346 -- HISTORY
347 -- 10-APR-2002 asaha Created.
348 --
349 -----------------------------------------------------------------------
350 PROCEDURE manage_toc_section(
351 p_content_type_code IN VARCHAR2,
352 p_content_item_name IN VARCHAR2,
353 p_description IN VARCHAR2,
354 p_delv_id IN NUMBER,
355 p_owner_resource_id IN NUMBER,
356 p_owner_resource_type IN VARCHAR2,
357 p_reusable_flag IN VARCHAR2 DEFAULT FND_API.g_false, -- CHANGE to Y or N when IBC folks change the conventions for varchar2 fields.
358 p_attr_types IN JTF_VARCHAR2_TABLE_100 DEFAULT NULL,
359 p_attr_values IN JTF_VARCHAR2_TABLE_4000 DEFAULT NULL,
360 p_parent_citem_id IN NUMBER,
361 p_parent_citem_ver_id IN NUMBER,
362 p_parent_ctype_code IN VARCHAR2,
363 p_attribute_type_code IN VARCHAR2,
364 p_commit IN VARCHAR2 DEFAULT FND_API.g_false,
365 p_api_version IN NUMBER DEFAULT 1.0,
366 p_api_validation_level IN NUMBER DEFAULT FND_API.g_valid_level_full,
367 px_citem_id IN OUT NUMBER,
368 px_citem_ver_id IN OUT NUMBER,
369 x_return_status OUT VARCHAR2,
370 x_msg_count OUT NUMBER,
371 x_msg_data OUT VARCHAR2,
372 p_dml_flag IN VARCHAR2,
373 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.g_true
374 );
375
376 -----------------------------------------------------------------------
377 -- PROCEDURE
378 -- update_questions_section
379 --
380 -- PURPOSE
381 -- Update Questions Section Content Item.
382 --
383 -- NOTES
384 -- 1. The required input is as follows:
385 -- Deliverable ID.
386 -- Content Item ID for the section.
387 -- Content Item Version ID for the section.
388 -- Content Type Code for the Section. This must be QUESTIONS.
389 -- Content Type Name. (This is the same as Section Name when this item is created in the context of a parent content item).
390 -- Attachment File ID that has the XML Data.
391 -- Attachment File Name.
392 -- 2. The optional input is as follows:
393 -- Description.
394 -- 3. This procedure performs the following steps:
395 -- 1. Arrive at SELECT SQL Statements for each Data Source used in the section.
396 -- 2. Arrive at the FUNCTIONAL_TYPE value.
397 -- 3. Set the Attachment for this Content Item.
398 -- 4. Set the Attribute Bundle for this Content Item.
399 -- This will consist of the following attributes:
400 -- SELECT_SQL_STATEMENT (s) : These could be many.
401 -- FUNCTIONAL_TYPE
402 -- 5. Delete the Data Source Usages records if already available.
403 -- 6. Insert the Data Source Usages records for the Data Sources used.
404 -- 4. This procedure returns the fact that it is successful.
405 --
406 -- HISTORY
407 -- 24-MAR-2002 gdeodhar Created.
408 --
409 -----------------------------------------------------------------------
410 PROCEDURE update_questions_section(
411 p_delv_id IN NUMBER,
412 p_section_citem_id IN NUMBER,
413 p_section_citem_ver_id IN NUMBER,
414 p_content_type_code IN VARCHAR2,
415 p_content_item_name IN VARCHAR2,
416 p_description IN VARCHAR2,
417 p_attach_file_id IN NUMBER,
418 p_attach_file_name IN VARCHAR2,
419 p_commit IN VARCHAR2 DEFAULT FND_API.g_false,
420 p_api_version IN NUMBER DEFAULT 1.0,
421 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.g_true,
422 p_api_validation_level IN NUMBER DEFAULT FND_API.g_valid_level_full,
423 x_return_status OUT VARCHAR2,
424 x_msg_count OUT NUMBER,
425 x_msg_data OUT VARCHAR2
426 );
427
428 -----------------------------------------------------------------------
429 -- PROCEDURE
430 -- get_rich_content_data
431 --
432 -- PURPOSE
433 -- Get the data from Rich Content Item.
434 --
435 -- NOTES
436 -- 1. The required input is as follows:
437 -- content_item_id
438 -- 2. This procedure returns the following data back to the caller.
439 -- citem_version_id for the content item.
440 -- attachment_file_id
441 -- attachment_file_name
442 -- citem_name
443 -- attribute_types (array)
444 -- attribute_values (array)
445 --
446 -----------------------------------------------------------------------
447 PROCEDURE get_rich_content_data(
448 p_citem_id IN NUMBER,
449 p_api_version IN NUMBER,
450 x_citem_ver_id OUT NUMBER,
451 x_attach_file_id OUT NUMBER,
455 x_attribute_values OUT JTF_VARCHAR2_TABLE_4000,
452 x_attach_file_name OUT VARCHAR2,
453 x_citem_name OUT VARCHAR2,
454 x_attribute_types OUT JTF_VARCHAR2_TABLE_100,
456 x_object_version_number OUT NUMBER,
457 x_return_status OUT VARCHAR2,
458 x_msg_count OUT NUMBER,
459 x_msg_data OUT VARCHAR2
460 );
461
462 -----------------------------------------------------------------------
463 -- PROCEDURE
464 -- get_content_item_data
465 --
466 -- PURPOSE
467 -- Get the Content Item Details. Wrapper on IBC_CITEM_ADMIN_GRP.get_citem
468 --
469 -- NOTES
470 -- 1. The required input is as follows:
471 -- content_item_id
472 -- content_item_version_id
473 -- 2. This procedure calls the get_citem from IBC_CITEM_ADMIN_GRP package.
474 -- It only sends the useful data back to the caller.
475 --
476 -----------------------------------------------------------------------
477 PROCEDURE get_content_item_data(
478 p_citem_id IN NUMBER,
479 p_citem_ver_id IN NUMBER,
480 p_api_version IN NUMBER,
481 x_status OUT VARCHAR2,
482 x_attach_file_id OUT NUMBER,
483 x_attach_file_name OUT VARCHAR2,
484 x_citem_name OUT VARCHAR2,
485 x_description OUT VARCHAR2,
486 x_attribute_type_codes OUT JTF_VARCHAR2_TABLE_100,
487 x_attribute_type_names OUT JTF_VARCHAR2_TABLE_300,
488 x_attributes OUT JTF_VARCHAR2_TABLE_4000,
489 x_cpnt_citem_ids OUT JTF_NUMBER_TABLE,
490 x_cpnt_ctype_codes OUT JTF_VARCHAR2_TABLE_100,
491 x_cpnt_attrib_types OUT JTF_VARCHAR2_TABLE_100,
492 x_cpnt_citem_names OUT JTF_VARCHAR2_TABLE_300,
493 x_cpnt_sort_orders OUT JTF_NUMBER_TABLE,
494 x_object_version_number OUT NUMBER,
495 x_return_status OUT VARCHAR2,
496 x_msg_count OUT NUMBER,
497 x_msg_data OUT VARCHAR2
498 );
499
500 -----------------------------------------------------------------------
501 -- PROCEDURE
502 -- get_content_item_attrs
503 --
504 -- PURPOSE
505 -- Wrapper on IBC_CITEM_ADMIN_GRP.get_attribute_bundle
506 --
507 -- NOTES
508 -- 1. The required input is as follows:
509 -- content_item_id
510 -- content_type_code
511 -- content_item_version_id
512 -- 2. This procedure calls the get_attribute_bundle from IBC_CITEM_ADMIN_GRP package.
513 -- It only sends the useful data back to the caller.
514 --
515 -----------------------------------------------------------------------
516 PROCEDURE get_content_item_attrs(
517 p_citem_id IN NUMBER,
518 p_ctype_code IN VARCHAR2,
519 p_citem_ver_id IN NUMBER,
520 p_attrib_file_id IN NUMBER DEFAULT NULL,
521 p_api_version IN NUMBER,
522 p_init_msg_list IN VARCHAR2,
523 x_attribute_type_codes OUT JTF_VARCHAR2_TABLE_100,
524 x_attribute_type_names OUT JTF_VARCHAR2_TABLE_300,
525 x_attributes OUT JTF_VARCHAR2_TABLE_4000,
526 x_return_status OUT VARCHAR2,
527 x_msg_count OUT NUMBER,
528 x_msg_data OUT VARCHAR2
529 );
530
531 -----------------------------------------------------------------------
532 -- PROCEDURE
533 -- create_cp_image
534 --
535 -- PURPOSE
536 -- Create the CP_IMAGE Content Item.
537 --
538 -- NOTES
539 -- 1. The required input is as follows:
540 -- Content Type Code for the Section. This must be CP_IMAGE.
541 -- Content Type Name. (This is the same as Section Name when this item is created in the context of a parent content item).
542 -- Deliverable ID.
543 -- Two Arrays : one with all the attribute type codes for CP_IMAGE.
544 -- second with the corresponding values for CP_IMAGE.
545 -- 2. The optional input is as follows:
546 -- Description.
547 -- Attribute Type Code for the Parent's Section.
548 -- The Content Item Version ID of the Parent Content Item
549 -- The Content Type Code associated with the Parent Content Item.
550 -- If the above two are available, this procedure will create a
551 -- compound relation between the Parent Content Item Version ID and
552 -- the Content Item ID of the newly created Content Item.
553 -- Attachment File Id of the newly uploaded binary file.
554 -- Attachment File Name for the same.
558 -- of type IMAGE (the OCM's IMAGE) first and use the content_item_id of
555 -- Two Arrays : one with the attribute type codes for IMAGE.
556 -- second with the corresponding values for IMAGE.
557 -- If the above four are available, this procedure will create a Content Item
559 -- this content item for CP_IMAGE.
560 -- If the above two are unavailable, the content_item_id of the IMAGE content item
561 -- referred to by this CP_IMAGE must be provided.
562 -- 3. This procedure performs the following steps:
563 -- 1. Create the IMAGE content item if necessary. It will call the bulk-insert
564 -- procedure for this task. The IMAGE content item is marked as APPROVED
565 -- upon creation.
566 -- 2. Create the CP_IMAGE content item using the bulk-insert call. This item
567 -- however is not marked as APPROVED.
568 -- NOTE that the FUNCTIONAL_TYPE for CP_IMAGE items is NORMAL.
569 -- 3. If the details of Parent Content Item are available,
570 -- create the compound relation between the parent content item and the
571 -- newly created CP_IMAGE item.
572 -- 4. This procedure returns the fact that it is successful.
573 -- It also returns the citem_id and citem_ver_id for the newly created CP_IMAGE item.
574 --
575 -- HISTORY
576 -- 17-FEB-2002 gdeodhar Created.
577 --
578 -----------------------------------------------------------------------
579 PROCEDURE create_cp_image(
580 p_content_type_code IN VARCHAR2,
581 p_content_item_name IN VARCHAR2,
582 p_description IN VARCHAR2,
583 p_delv_id IN NUMBER,
584 p_resource_id IN NUMBER,
585 p_resource_type IN VARCHAR2,
586 p_reusable_flag IN VARCHAR2 DEFAULT FND_API.g_true, -- CHANGE to Y or N when IBC folks change the conventions for varchar2 fields.
587 p_attr_types_cp_image IN JTF_VARCHAR2_TABLE_100 DEFAULT NULL,
588 p_attr_values_cp_image IN JTF_VARCHAR2_TABLE_4000 DEFAULT NULL,
589 p_attach_file_id IN NUMBER,
590 p_attach_file_name IN VARCHAR2,
591 p_attr_types_image IN JTF_VARCHAR2_TABLE_100 DEFAULT NULL,
592 p_attr_values_image IN JTF_VARCHAR2_TABLE_4000 DEFAULT NULL,
593 p_parent_attr_type_code IN VARCHAR2,
594 p_parent_citem_id IN NUMBER,
595 p_parent_citem_ver_id IN NUMBER,
596 p_parent_ctype_code IN VARCHAR2,
597 p_commit IN VARCHAR2 DEFAULT FND_API.g_false,
598 p_api_version IN NUMBER DEFAULT 1.0,
599 p_api_validation_level IN NUMBER DEFAULT FND_API.g_valid_level_full,
600 x_cp_image_citem_id OUT NUMBER,
601 x_cp_image_citem_ver_id OUT NUMBER,
602 x_return_status OUT VARCHAR2,
603 x_msg_count OUT NUMBER,
604 x_msg_data OUT VARCHAR2
605 );
606
607 -----------------------------------------------------------------------
608 -- PROCEDURE
609 -- update_cp_image
610 --
611 -- PURPOSE
612 -- Update the CP_IMAGE Content Item.
613 --
614 -- NOTES
615 -- 1. The required input is as follows:
616 -- Content Type Code for the Section. This must be CP_IMAGE.
617 -- Content Item Id for the CP_IMAGE item.
618 -- Content Item Name. This is same as the Section Name.
619 -- Content Item Version Id for the CP_IMAGE item.
620 -- Deliverable ID.
621 -- Two Arrays : one with all the attribute type codes for CP_IMAGE.
622 -- second with the corresponding values for CP_IMAGE.
623 -- 2. The optional input is as follows:
624 -- Description.
625 -- Attachment File Id of the newly uploaded binary file.
626 -- Attachment File Name for the same.
627 -- Two Arrays : one with the attribute type codes for IMAGE.
628 -- second with the corresponding values for IMAGE.
629 -- If the above four are available, this procedure will create a Content Item
630 -- of type IMAGE (the OCM's IMAGE) first and use the content_item_id of
631 -- this content item for CP_IMAGE.
632 -- If the above two are unavailable, the content_item_id of the IMAGE content item
633 -- referred to by this CP_IMAGE must be provided.
634 -- 3. This procedure performs the following steps:
635 -- 1. Create the IMAGE content item if necessary. It will call the bulk-insert
636 -- procedure for this task. The IMAGE content item is marked as APPROVED
637 -- upon creation.
638 -- 2. Update the CP_IMAGE content item using the following calls:
639 -- set_citem_att_bundle (with all the attributes with the changed values).
640 -- NOTE that the FUNCTIONAL_TYPE for CP_IMAGE items is NORMAL.
641 -- NOTE that we will not call the following for now:
642 -- set_citem_meta will not be called as none of the meta items are exposed
643 -- in the UI for CP_IMAGE.
644 -- update_citem_basic will not be called as we do not expose Description
645 -- in the UI and we do not allow the change of the Name.
646 -- 4. This procedure returns the fact that it is successful.
647 --
648 -- HISTORY
649 -- 19-FEB-2002 gdeodhar Created.
650 --
651 -----------------------------------------------------------------------
652 PROCEDURE update_cp_image(
653 p_content_type_code IN VARCHAR2,
654 p_content_item_name IN VARCHAR2,
655 p_cp_image_citem_id IN NUMBER,
656 p_cp_image_citem_ver_id IN NUMBER,
657 p_delv_id IN NUMBER,
658 p_resource_id IN NUMBER,
659 p_resource_type IN VARCHAR2,
660 p_attr_types_cp_image IN JTF_VARCHAR2_TABLE_100 DEFAULT NULL,
661 p_attr_values_cp_image IN JTF_VARCHAR2_TABLE_4000 DEFAULT NULL,
665 p_attr_types_image IN JTF_VARCHAR2_TABLE_100 DEFAULT NULL,
662 p_description IN VARCHAR2,
663 p_attach_file_id IN NUMBER,
664 p_attach_file_name IN VARCHAR2,
666 p_attr_values_image IN JTF_VARCHAR2_TABLE_4000 DEFAULT NULL,
667 p_commit IN VARCHAR2 DEFAULT FND_API.g_false,
668 p_api_version IN NUMBER DEFAULT 1.0,
669 p_api_validation_level IN NUMBER DEFAULT FND_API.g_valid_level_full,
670 x_return_status OUT VARCHAR2,
671 x_msg_count OUT NUMBER,
672 x_msg_data OUT VARCHAR2
673 );
674
675
676 -----------------------------------------------------------------------
677 -- PROCEDURE
678 -- update_content_item
679 --
680 -- PURPOSE
681 -- Update a Content Item with a generic content type.
682 -- The Content Type must be provided.
683 --
684 -- NOTES
685 -- 1. The required input is as follows:
686 -- Content Type Code for the Item.
687 -- Content Item Id for Item.
688 -- Content Item Version Id for the Item.
689 -- Two Arrays : one with data for changed attribute codes.
690 -- second with the corresponding values.
691 -- 2. The optional input is as follows:
692 -- Content Item Name for the Item.
693 -- Description.
694 -- Attachment File Id for the attachment.
695 -- Attachment File Name for the same.
696 -- 3. This procedure performs the following steps:
697 -- 1. Lock the Content Item.
698 -- 2. Get the existing Attribute data for the content item.
699 -- 3. Set the values of the changed Attributes with the incoming data.
700 -- 4. Set the Attachment File Id if it has been provided as input.
701 -- NOTE that we will not call the following for now:
702 -- set_citem_meta will not be called as none of the meta items are exposed
703 -- in the UI for any of the content items.
704 -- update_citem_basic will not be called as we do not expose Description
705 -- in the UI and we do not allow the change of the Name.
706 -- 4. This procedure returns the fact that it is successful.
707 --
708 -- HISTORY
709 -- 24-FEB-2002 gdeodhar Created.
710 --
711 -----------------------------------------------------------------------
712 PROCEDURE update_content_item(
713 p_citem_id IN NUMBER,
714 p_citem_version_id IN NUMBER,
715 p_content_type_code IN VARCHAR2,
716 p_content_item_name IN VARCHAR2,
717 p_description IN VARCHAR2,
718 p_delv_id IN NUMBER,
719 p_attr_types_for_update IN JTF_VARCHAR2_TABLE_100 DEFAULT NULL,
720 p_attr_values_for_update IN JTF_VARCHAR2_TABLE_4000 DEFAULT NULL,
721 p_attach_file_id IN NUMBER DEFAULT NULL,
722 p_attach_file_name IN VARCHAR2 DEFAULT NULL,
723 p_commit IN VARCHAR2 DEFAULT FND_API.g_false,
724 p_api_version IN NUMBER DEFAULT 1.0,
725 p_api_validation_level IN NUMBER DEFAULT FND_API.g_valid_level_full,
726 x_return_status OUT VARCHAR2,
727 x_msg_count OUT NUMBER,
728 x_msg_data OUT VARCHAR2,
729 p_replace_attr_bundle IN VARCHAR2 DEFAULT FND_API.g_false
730 );
731
732 -----------------------------------------------------------------------
733 -- PROCEDURE
734 -- create_display_template
735 --
736 -- PURPOSE
737 -- Create a Content Item of type STYLESHEET.
738 --
739 -- NOTES
740 -- 1. The required input is as follows:
741 -- Content Type Code for which the Stylesheet or Display Template is for.
742 -- Display Template or Stylesheet Name.
743 -- Attachment File ID that has the actual Stylesheet or Display Template.
744 -- Attachment File Name. (This will the one of the uploaded file).
745 -- Value for DELIVERY_CHANNEL
746 -- Value for OUTPUT_TYPE
747 -- 2. The optional input is as follows:
748 -- Stylesheet Description.
749 -- 3. This procedure performs the following steps:
750 -- 1. Create a Content Item of type STYLESHEET using Bulk Insert as an APPROVED item.
751 -- Set the attribute bundle for the item, with DELIVERY_OPTION and OUTPUT_TYPE.
752 -- 2. Create an entry in IBC_STYLESHEETS table.
753 -- 4. This procedure returns the fact that it is successful, it also returns the
754 -- newly created Display Template ID.
755 --
756 -- HISTORY
757 -- 04-MAR-2002 gdeodhar Created.
758 --
759 -----------------------------------------------------------------------
760
761 PROCEDURE create_display_template(
762 p_content_type_code IN VARCHAR2,
763 p_stylesheet_name IN VARCHAR2,
764 p_stylesheet_descr IN VARCHAR2 DEFAULT NULL,
765 p_delivery_channel IN VARCHAR2,
766 p_output_type IN VARCHAR2,
767 p_attach_file_id IN NUMBER,
768 p_attach_file_name IN VARCHAR2,
769 p_resource_id IN NUMBER,
770 p_resource_type IN VARCHAR2,
771 p_commit IN VARCHAR2 DEFAULT FND_API.g_false,
772 p_api_version IN NUMBER DEFAULT 1.0,
773 p_api_validation_level IN NUMBER DEFAULT FND_API.g_valid_level_full,
774 x_citem_id OUT NUMBER,
775 x_citem_ver_id OUT NUMBER,
776 x_return_status OUT VARCHAR2,
777 x_msg_count OUT NUMBER,
778 x_msg_data OUT VARCHAR2
779 );
780
781
785 --
782 -----------------------------------------------------------------------
783 -- PROCEDURE
784 -- create_basic_questions_item.
786 -- PURPOSE
787 -- Create basic content item of type QUESTIONS.
788 --
789 -- NOTES
790 -- 1. The required input is as follows:
791 -- Content Type code. This must be QUESTIONS.
792 -- Name of the Questions Section.
793 -- Owner Resource Id.
794 -- Resource Type.
795 -- Content Item Id for the Parent Content Item associated with the parent deliverable.
796 -- Content Item Version Id for the Parent Content Item.
797 -- Content Type Code for the Parent Content Item.
798 -- 2. The optional input is as follows:
799 -- Description.
800 -- 3. This procedure performs the following steps:
801 -- 1. Create a basic Content Item of type QUESTIONS using Bulk Insert.
802 -- 2. Create compound relation with the parent.
803 -- 4. This procedure returns the fact that it is successful, it also returns the
804 -- newly created Content Item Id.
805 --
806 -- HISTORY
807 -- 09-MAR-2002 gdeodhar Created.
808 --
809 -----------------------------------------------------------------------
810
811 PROCEDURE create_basic_questions_item(
812 p_content_type_code IN VARCHAR2,
813 p_content_item_name IN VARCHAR2,
814 p_description IN VARCHAR2,
815 p_start_date IN DATE DEFAULT SYSDATE,
816 p_end_date IN DATE DEFAULT NULL,
817 p_owner_resource_id IN NUMBER,
818 p_owner_resource_type IN VARCHAR2,
819 p_reusable_flag IN VARCHAR2 DEFAULT FND_API.g_false, -- CHANGE to Y or N when IBC folks change the conventions for varchar2 fields.
820 p_attribute_type_code IN VARCHAR2,
821 p_parent_citem_id IN NUMBER,
822 p_parent_citem_ver_id IN NUMBER,
823 p_parent_ctype_code IN VARCHAR2,
824 p_commit IN VARCHAR2 DEFAULT FND_API.g_false,
825 p_api_version IN NUMBER DEFAULT 1.0,
826 p_api_validation_level IN NUMBER DEFAULT FND_API.g_valid_level_full,
827 x_citem_id OUT NUMBER,
828 x_citem_ver_id OUT NUMBER,
829 x_return_status OUT VARCHAR2,
830 x_msg_count OUT NUMBER,
831 x_msg_data OUT VARCHAR2
832 );
833
834
835
836 -----------------------------------------------------------------------
837 -- PROCEDURE
838 -- generate_select_sql.
839 --
840 -- PURPOSE
841 -- Generate select SQL statement, given a data source and list of fields.
842 --
843 -- NOTES
844 -- 1. The required input is as follows:
845 -- Data Source code.
846 -- VARCHAR2 Array with a list of Data Fields in the form of DATA_SRC_TYPE_CODE:FIELD_COLUMN_NAME.
847 -- 2. The optional input is as follows:
848 -- NUMBER Array with a list of Data Source Field IDs.
849 -- 3. This procedure performs the following steps:
850 -- 1. Referes to the Data Sources schema to get the details of Data Source fields.
851 -- 2. Generate a Select SQL Statement based on the Data Source and the fields.
852 -- 4. This procedure returns the generated SQL statement and the list of bind variable names.
853 --
854 -- HISTORY
855 -- 09-MAR-2002 gdeodhar Created.
856 --
857 -----------------------------------------------------------------------
858
859 PROCEDURE generate_select_sql(
860 p_data_source_code IN VARCHAR2,
861 p_data_source_fields_list IN JTF_VARCHAR2_TABLE_300 DEFAULT NULL,
862 p_data_source_field_ids IN JTF_NUMBER_TABLE DEFAULT NULL,
863 x_select_sql_statement OUT VARCHAR2,
864 x_bind_vars OUT JTF_VARCHAR2_TABLE_300,
865 x_return_status OUT VARCHAR2,
866 x_msg_count OUT NUMBER,
867 x_msg_data OUT VARCHAR2
868 );
869
870
871 -----------------------------------------------------------------------
872 -- PROCEDURE
873 -- create_submit_section
874 --
875 -- PURPOSE
876 -- Create content item of type SUBMIT_SECTION.
877 --
878 -- NOTES
879 -- 1. The required input is as follows:
880 -- Content Type code. This must be SUBMIT_SECTION.
881 -- Name of the Submit Section.
882 -- Owner Resource Id.
883 -- Resource Type.
884 -- Content Item Id for the Parent Content Item associated with the parent deliverable.
885 -- Content Item Version Id for the Parent Content Item.
886 -- Content Type Code for the Parent Content Item.
887 -- 2. The optional input is as follows:
888 -- Description.
889 -- 3. This procedure performs the following steps:
890 -- 1. Create a Content Item of type SUBMIT_SECTION using Bulk Insert.
891 -- 2. Create compound relation with the parent.
892 -- 4. This procedure returns the fact that it is successful, it also returns the
893 -- newly created Content Item Id.
894 --
895 -- HISTORY
896 -- 10-MAR-2002 gdeodhar Created.
897 --
898 -----------------------------------------------------------------------
899
900 PROCEDURE create_submit_section(
901 p_delv_id IN NUMBER,
902 p_content_type_code IN VARCHAR2,
903 p_content_item_name IN VARCHAR2,
904 p_description IN VARCHAR2,
905 p_owner_resource_id IN NUMBER,
906 p_owner_resource_type IN VARCHAR2,
907 p_reusable_flag IN VARCHAR2 DEFAULT FND_API.g_false, -- CHANGE to Y or N when IBC folks change the conventions for varchar2 fields.
908 p_attribute_type_code IN VARCHAR2,
909 p_parent_citem_id IN NUMBER,
910 p_parent_citem_ver_id IN NUMBER,
914 p_ocm_image_id IN NUMBER DEFAULT NULL,
911 p_parent_ctype_code IN VARCHAR2,
912 p_ui_control_type IN VARCHAR2 DEFAULT G_DEF_UI_FOR_SUBMIT,
913 p_button_label IN VARCHAR2,
915 p_alignment IN VARCHAR2 DEFAULT G_DEF_ALIGN_FOR_SUBMIT,
916 p_commit IN VARCHAR2 DEFAULT FND_API.g_false,
917 p_api_version IN NUMBER DEFAULT 1.0,
918 p_api_validation_level IN NUMBER DEFAULT FND_API.g_valid_level_full,
919 x_citem_id OUT NUMBER,
920 x_citem_ver_id OUT NUMBER,
921 x_return_status OUT VARCHAR2,
922 x_msg_count OUT NUMBER,
923 x_msg_data OUT VARCHAR2
924 );
925
926
927 -----------------------------------------------------------------------
928 -- PROCEDURE
929 -- update_submit_section
930 --
931 -- PURPOSE
932 -- Update content item of type SUBMIT_SECTION.
933 --
934 -- NOTES
935 -- 1. The required input is as follows:
936 -- Content Type code. This must be SUBMIT_SECTION.
937 -- Name of the Submit Section.
938 -- Content Item Id for the section.
939 -- Content Item Version Id for the section.
940 -- 2. The optional input is as follows:
941 -- Description.
942 -- Other data that needs changes.
943 -- 3. This procedure performs the following steps:
944 -- 1. Update a Content Item of type SUBMIT_SECTION.
945 -- 4. This procedure returns the fact that it is successful
946 --
947 -- HISTORY
948 -- 11-MAR-2002 gdeodhar Created.
949 --
950 -----------------------------------------------------------------------
951
952 PROCEDURE update_submit_section(
953 p_delv_id IN NUMBER,
954 p_content_type_code IN VARCHAR2,
955 p_content_item_name IN VARCHAR2,
956 p_description IN VARCHAR2,
957 p_citem_id IN NUMBER,
958 p_citem_ver_id IN NUMBER,
959 p_ui_control_type IN VARCHAR2,
960 p_button_label IN VARCHAR2,
961 p_ocm_image_id IN NUMBER,
962 p_alignment IN VARCHAR2,
963 p_commit IN VARCHAR2 DEFAULT FND_API.g_false,
964 p_api_version IN NUMBER DEFAULT 1.0,
965 p_api_validation_level IN NUMBER DEFAULT FND_API.g_valid_level_full,
966 x_return_status OUT VARCHAR2,
967 x_msg_count OUT NUMBER,
968 x_msg_data OUT VARCHAR2
969 );
970
971
972 -----------------------------------------------------------------------
973 -- PROCEDURE
974 -- erase_blob
975 --
976 -- PURPOSE
977 -- erases blob.
978 -- HISTORY
979 -- 14-MAY-2002 asaha Created.
980 --
981 -----------------------------------------------------------------------
982 PROCEDURE erase_blob(
983 p_file_id IN NUMBER,
984 x_blob OUT BLOB,
985 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.g_true,
986 x_return_status OUT VARCHAR2,
987 x_msg_count OUT NUMBER,
988 x_msg_data OUT VARCHAR2
989 );
990
991 END AMS_CPageUtility_PVT;
[Home] [Help]