[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT owner_user_id
,actual_avail_from_date
,actual_avail_to_date
,deliverable_name
,description
FROM ams_deliverables_vl
WHERE deliverable_id = p_delv_id ;
,p_log_action => FND_API.g_true -- This to be sent as TRUE. It updates the Audit Logs.
,p_commit => FND_API.g_false -- We still have to do some more operations.
,p_api_version_number => p_api_version
,p_init_msg_list => l_init_msg_list
,px_content_item_id => l_citem_id
,px_citem_ver_id => l_citem_ver_id
,px_object_version_number => l_obj_ver_num
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
IBC_CITEM_ADMIN_GRP.insert_associations(
p_content_item_id => l_citem_id
,p_assoc_type_codes => l_assoc_type_codes
,p_assoc_objects1 => l_assoc_objects1
,p_assoc_objects2 => l_assoc_objects2 -- Denormalized Value stored here. This can be debated. Remove if decided against storing it here.
,p_assoc_objects3 => l_assoc_objects3 -- Denormalized Value stored here. This can be debated. Remove if decided against storing it here.
,p_assoc_objects4 => l_assoc_objects4 -- Null values.
,p_assoc_objects5 => l_assoc_objects5 -- Null values.
,p_commit => FND_API.g_false -- This is the Default.
,p_api_version_number => p_api_version
,p_init_msg_list => l_init_msg_list
-- The following are OUT parameters in this procedure.
--,x_assoc_id => l_assoc_id
-- Jamie applied his package on mapdev01.
-- He has changed signature of this method.
-- as a result commenting the above line.
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
AMS_UTILITY_PVT.debug_message('Insert Assoc.');
This will generate the XML for Questions Section if necessary by calling update_questions_section.
Other completion tasks as identified.
2. Approve Item call.
*/
-----------------------------------------------------------------------
PROCEDURE approve_citem_for_delv(
p_content_type_code IN VARCHAR2,
p_delv_id IN NUMBER,
p_citem_id IN NUMBER,
p_assoc_type_code IN VARCHAR2,
p_commit IN VARCHAR2 DEFAULT FND_API.g_false,
p_api_version IN NUMBER DEFAULT 1.0,
p_api_validation_level IN NUMBER DEFAULT FND_API.g_valid_level_full,
x_return_status OUT VARCHAR2,
x_msg_count OUT NUMBER,
x_msg_data OUT VARCHAR2
)
IS
-- Declare the local variables and cursors here.
--
CURSOR c_delv_details IS
SELECT status_code
FROM ams_deliverables_vl
WHERE deliverable_id = p_delv_id ;
SELECT MAX(citem_version_id)
FROM ibc_citem_versions_b
WHERE content_item_id = l_content_item_id ;
SELECT actual_avail_from_date
,actual_avail_to_date
FROM ams_deliverables_vl
WHERE deliverable_id = p_delv_id ;
l_select_sql_statement VARCHAR2(4000) ;
generate_select_sql(
p_data_source_code => l_data_src_type_code
,p_data_source_fields_list => p_merge_fields_list
,p_data_source_field_ids => NULL
,x_select_sql_statement => l_select_sql_statement
,x_bind_vars => l_bind_vars_list
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
AMS_UTILITY_PVT.debug_message( ' l_select_sql_statement = ' || l_select_sql_statement );
IF l_select_sql_statement IS NOT NULL
THEN
l_attr_count := l_attr_count + 1;
l_attribute_type_codes(l_attr_count) := G_SELECT_SQL_STATEMENT ;
l_attributes(l_attr_count) := l_select_sql_statement ;
,p_log_action => FND_API.g_true -- This to be sent as TRUE. It updates the Audit Logs.
,p_commit => FND_API.g_false -- We still have to do some more operations.
,p_api_version_number => p_api_version
,p_init_msg_list => l_init_msg_list
,px_content_item_id => l_citem_id
,px_citem_ver_id => l_citem_ver_id
,px_object_version_number => l_obj_ver_num
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
UPDATE ibc_citem_versions_tl
SET attachment_attribute_code = 'ATTACHMENT' -- hardcoding as ATTACHMENT for Rich Content.
WHERE citem_version_id = l_citem_ver_id ;
IBC_CITEM_ADMIN_GRP.insert_components(
p_citem_ver_id => p_parent_citem_ver_id
,p_content_item_ids => l_citem_ids
,p_attribute_type_codes => l_citem_attrs
,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.
,p_commit => FND_API.g_false
,p_api_version_number => p_api_version
,p_init_msg_list => l_init_msg_list
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
update_content_item(
p_citem_id => l_citem_id
,p_citem_version_id => l_citem_ver_id
,p_content_type_code => p_content_type_code
,p_content_item_name => NULL -- We do not allow update on this one yet.
,p_description => NULL -- We do not allow update on this one yet.
,p_delv_id => p_delv_id
,p_attr_types_for_update => l_attribute_type_codes
,p_attr_values_for_update => l_attributes
,p_attach_file_id => p_attach_file_id
,p_attach_file_name => p_attach_file_name
,p_commit => FND_API.g_false
,p_api_version => p_api_version
,p_api_validation_level => p_api_validation_level
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,p_replace_attr_bundle => FND_API.g_true
);
AMS_Utility_PVT.Error_Message('AMS_ERR_UPDATE_CITEM');
SELECT actual_avail_from_date
,actual_avail_to_date
FROM ams_deliverables_vl
WHERE deliverable_id = p_delv_id ;
,p_log_action => FND_API.g_true -- This to be sent as TRUE. It updates the Audit Logs.
,p_commit => FND_API.g_false -- We still have to do some more operations.
,p_api_version_number => p_api_version
,p_init_msg_list => l_init_msg_list
,px_content_item_id => l_citem_id
,px_citem_ver_id => l_citem_ver_id
,px_object_version_number => l_obj_ver_num
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
IBC_CITEM_ADMIN_GRP.insert_components(
p_citem_ver_id => p_parent_citem_ver_id
,p_content_item_ids => l_citem_ids
,p_attribute_type_codes => l_citem_attrs
,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.
,p_commit => FND_API.g_false
,p_api_version_number => p_api_version
,p_init_msg_list => l_init_msg_list
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
update_content_item(
p_citem_id => l_citem_id
,p_citem_version_id => l_citem_ver_id
,p_content_type_code => p_content_type_code
,p_content_item_name => NULL -- We do not allow update on this one yet.
,p_description => NULL -- We do not allow update on this one yet.
,p_delv_id => p_delv_id
,p_attr_types_for_update => p_attr_types
,p_attr_values_for_update => p_attr_values
,p_attach_file_id => NULL
,p_attach_file_name => NULL
,p_commit => FND_API.g_false
,p_api_version => p_api_version
,p_api_validation_level => p_api_validation_level
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,p_replace_attr_bundle => FND_API.g_true
);
AMS_Utility_PVT.Error_Message('AMS_ERR_UPDATE_CITEM');
SELECT MAX(citem_version_id)
FROM ibc_citem_versions_b
WHERE content_item_id = p_citem_id ;
l_last_updated_by NUMBER ;
l_last_update_date DATE ;
,x_last_updated_by => l_last_updated_by
,x_last_update_date => l_last_update_date
,x_attribute_type_codes => l_attribute_type_codes
,x_attribute_type_names => l_attribute_type_names
,x_attributes => l_attributes
,x_component_citems => l_cpnt_citems
-- Not in the new API ,x_cpnt_ctypes => l_cpnt_ctypes
,x_component_attrib_types => l_cpnt_attrib_types
,x_component_citem_names => l_cpnt_citem_names
,x_component_owner_ids => l_cpnt_owner_ids
,x_component_owner_types => l_cpnt_owner_types
-- Not in the new API ,x_cpnt_owner_names => l_cpnt_owner_names
,x_component_sort_orders => l_cpnt_sort_orders
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
AMS_UTILITY_PVT.debug_message(l_last_updated_by);
AMS_UTILITY_PVT.debug_message(l_last_update_date);
SELECT actual_avail_from_date
,actual_avail_to_date
FROM ams_deliverables_vl
WHERE deliverable_id = p_delv_id ;
,p_log_action => FND_API.g_true -- This to be sent as TRUE. It updates the Audit Logs.
,p_commit => FND_API.g_false -- We still have to do some more operations.
,p_api_version_number => p_api_version
,p_init_msg_list => l_init_msg_list
,px_content_item_id => l_image_citem_id
,px_citem_ver_id => l_image_citem_ver_id
,px_object_version_number => l_image_obj_ver_num
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
,p_log_action => FND_API.g_true -- This to be sent as TRUE. It updates the Audit Logs.
,p_commit => FND_API.g_false -- We still have to do some more operations.
,p_api_version_number => p_api_version
,p_init_msg_list => l_init_msg_list
,px_content_item_id => l_citem_id
,px_citem_ver_id => l_citem_ver_id
,px_object_version_number => l_obj_ver_num
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
IBC_CITEM_ADMIN_GRP.insert_components(
p_citem_ver_id => p_parent_citem_ver_id
,p_content_item_ids => l_citem_ids
,p_attribute_type_codes => l_citem_attrs
,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.
,p_commit => FND_API.g_false
,p_api_version_number => p_api_version
,p_init_msg_list => l_init_msg_list
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
PROCEDURE update_cp_image(
p_content_type_code IN VARCHAR2,
p_content_item_name IN VARCHAR2,
p_cp_image_citem_id IN NUMBER,
p_cp_image_citem_ver_id IN NUMBER,
p_delv_id IN NUMBER,
p_resource_id IN NUMBER,
p_resource_type IN VARCHAR2,
p_attr_types_cp_image IN JTF_VARCHAR2_TABLE_100 DEFAULT NULL,
p_attr_values_cp_image IN JTF_VARCHAR2_TABLE_4000 DEFAULT NULL,
p_description IN VARCHAR2,
p_attach_file_id IN NUMBER,
p_attach_file_name IN VARCHAR2,
p_attr_types_image IN JTF_VARCHAR2_TABLE_100 DEFAULT NULL,
p_attr_values_image IN JTF_VARCHAR2_TABLE_4000 DEFAULT NULL,
p_commit IN VARCHAR2 DEFAULT FND_API.g_false,
p_api_version IN NUMBER DEFAULT 1.0,
p_api_validation_level IN NUMBER DEFAULT FND_API.g_valid_level_full,
x_return_status OUT VARCHAR2,
x_msg_count OUT NUMBER,
x_msg_data OUT VARCHAR2
)
IS
--
-- Declare the local variables and cursors here.
--
l_return_status VARCHAR2(1) ;
l_api_name CONSTANT VARCHAR2(30) := 'Update_CP_Image';
SAVEPOINT update_cp_image_PVT ;
,p_log_action => FND_API.g_true -- This to be sent as TRUE. It updates the Audit Logs.
,p_commit => FND_API.g_false -- We still have to do some more operations.
,p_api_version_number => p_api_version
,p_init_msg_list => l_init_msg_list
,px_content_item_id => l_image_citem_id
,px_citem_ver_id => l_image_citem_ver_id
,px_object_version_number => l_image_obj_ver_num
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
AMS_Utility_PVT.Error_Message('AMS_ERR_UPDATE_CP_IMAGE');
ROLLBACK TO update_cp_image_PVT ;
ROLLBACK TO update_cp_image_PVT;
ROLLBACK TO update_cp_image_PVT ;
END update_cp_image;
PROCEDURE update_content_item(
p_citem_id IN NUMBER,
p_citem_version_id IN NUMBER,
p_content_type_code IN VARCHAR2,
p_content_item_name IN VARCHAR2,
p_description IN VARCHAR2,
p_delv_id IN NUMBER,
p_attr_types_for_update IN JTF_VARCHAR2_TABLE_100 DEFAULT NULL,
p_attr_values_for_update IN JTF_VARCHAR2_TABLE_4000 DEFAULT NULL,
p_attach_file_id IN NUMBER DEFAULT NULL,
p_attach_file_name IN VARCHAR2 DEFAULT NULL,
p_commit IN VARCHAR2 DEFAULT FND_API.g_false,
p_api_version IN NUMBER DEFAULT 1.0,
p_api_validation_level IN NUMBER DEFAULT FND_API.g_valid_level_full,
x_return_status OUT VARCHAR2,
x_msg_count OUT NUMBER,
x_msg_data OUT VARCHAR2,
p_replace_attr_bundle IN VARCHAR2 DEFAULT FND_API.g_false
)
IS
--
l_attribute_type_codes JTF_VARCHAR2_TABLE_100 ;
l_api_name CONSTANT VARCHAR2(30) := 'Update_Content_Item';
l_attribute_type_codes := p_attr_types_for_update;
l_attributes := p_attr_values_for_update;
IF p_attr_types_for_update IS NOT NULL
AND
p_attr_values_for_update IS NOT NULL
THEN
/*
-- This call was giving problems.
-- Using get_content_item_data instead.
-- Call get_content_item_attrs method.
get_content_item_attrs(
p_citem_id => p_citem_id
,p_ctype_code => p_content_type_code
,p_citem_ver_id => p_citem_version_id
,p_attrib_file_id => NULL
,p_api_version => p_api_version
,p_init_msg_list => l_init_msg_list
,x_attribute_type_codes => l_attribute_type_codes
,x_attribute_type_names => l_attribute_type_names
,x_attributes => l_attributes
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
FOR j IN p_attr_types_for_update.first .. p_attr_types_for_update.last
LOOP
IF l_attribute_type_codes(i) = p_attr_types_for_update(j)
THEN
-- The i-th attribute needs value change.
-- Set the new value.
l_attributes(i) := p_attr_values_for_update(j);
l_attribute_type_codes := p_attr_types_for_update;
l_attributes := p_attr_values_for_update;
AMS_Utility_PVT.Error_Message('AMS_ERR_UPDATE_CITEM');
AMS_Utility_PVT.Error_Message('AMS_ERR_UPDATE_CITEM');
END update_content_item;
PROCEDURE update_citem_for_delv(
p_content_type_code IN VARCHAR2,
p_def_disp_template_id IN NUMBER,
p_delv_id IN NUMBER,
p_citem_id IN NUMBER,
p_assoc_type_code IN VARCHAR2,
p_commit IN VARCHAR2 DEFAULT FND_API.g_false,
p_api_version IN NUMBER DEFAULT 1.0,
p_api_validation_level IN NUMBER DEFAULT FND_API.g_valid_level_full,
x_return_status OUT VARCHAR2,
x_msg_count OUT NUMBER,
x_msg_data OUT VARCHAR2
)
IS
--
-- Declare the local variables and cursors here.
--
-- Cursor to select the latest citem version for a content item.
--
CURSOR c_max_version IS
SELECT MAX(citem_version_id)
FROM ibc_citem_versions_b
WHERE content_item_id = p_citem_id ;
l_api_name CONSTANT VARCHAR2(30) := 'Update_Citem_For_Delv';
update_content_item(
p_citem_id => p_citem_id
,p_citem_version_id => l_citem_ver_id
,p_content_type_code => p_content_type_code
,p_content_item_name => NULL
,p_description => NULL
,p_delv_id => p_delv_id
,p_attr_types_for_update => l_attribute_type_codes
,p_attr_values_for_update => l_attributes
,p_attach_file_id => NULL
,p_attach_file_name => NULL
,p_commit => FND_API.g_false
,p_api_version => p_api_version
,p_api_validation_level => p_api_validation_level
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
AMS_Utility_PVT.Error_Message('AMS_ERR_UPDATE_CITEM');
UPDATE ibc_associations
SET associated_object_val3 = p_def_disp_template_id
WHERE content_item_id = p_citem_id
AND associated_object_val1 = TO_CHAR(p_delv_id)
AND association_type_code = G_CPAGE_ASSOC_TYPE_CODE;
END update_citem_for_delv;
,p_log_action => FND_API.g_true -- This to be sent as TRUE. It updates the Audit Logs.
,p_commit => FND_API.g_false -- We still have to do some more operations.
,p_api_version_number => p_api_version
,p_init_msg_list => l_init_msg_list
,px_content_item_id => l_citem_id
,px_citem_ver_id => l_citem_ver_id
,px_object_version_number => l_obj_ver_num
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
insert into IBC_STYLESHEETS
(
CONTENT_TYPE_CODE
,CONTENT_ITEM_ID
,DEFAULT_STYLESHEET_FLAG
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
,OBJECT_VERSION_NUMBER
,SECURITY_GROUP_ID
)
values
(
p_content_type_code
,l_citem_id
,'N'
,FND_GLOBAL.user_id
,SYSDATE
,FND_GLOBAL.user_id
,SYSDATE
,FND_GLOBAL.conc_login_id
,1
,NULL
);
,p_log_action => FND_API.g_true -- This to be sent as TRUE. It updates the Audit Logs.
,p_commit => FND_API.g_false -- We still have to do some more operations.
,p_api_version_number => p_api_version
,p_init_msg_list => l_init_msg_list
,px_content_item_id => l_citem_id
,px_citem_ver_id => l_citem_ver_id
,px_object_version_number => l_obj_ver_num
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
IBC_CITEM_ADMIN_GRP.insert_components(
p_citem_ver_id => p_parent_citem_ver_id
,p_content_item_ids => l_citem_ids
,p_attribute_type_codes => l_citem_attrs
,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.
,p_commit => FND_API.g_false
,p_api_version_number => p_api_version
,p_init_msg_list => l_init_msg_list
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
PROCEDURE generate_select_sql(
p_data_source_code IN VARCHAR2,
p_data_source_fields_list IN JTF_VARCHAR2_TABLE_300 DEFAULT NULL,
p_data_source_field_ids IN JTF_NUMBER_TABLE DEFAULT NULL,
x_select_sql_statement OUT VARCHAR2,
x_bind_vars OUT JTF_VARCHAR2_TABLE_300,
x_return_status OUT VARCHAR2,
x_msg_count OUT NUMBER,
x_msg_data OUT VARCHAR2
)
IS
l_select_sql_statement VARCHAR2(4000) := '' ;
l_select_clause VARCHAR2(4000) := '' ;
SELECT list_source_type_id
FROM ams_list_src_types
WHERE source_type_code = p_data_source_code
AND enabled_flag = 'Y';
SELECT source_object_name, source_object_pk_field, master_source_type_flag, view_application_id
FROM ams_list_src_types
WHERE source_type_code = p_data_source_code
AND enabled_flag = 'Y';
SELECT source_column_name
FROM ams_list_src_fields
WHERE de_list_source_type_code = p_data_source_code
AND list_source_field_id = l_list_source_field_id
AND enabled_flag = 'Y';
l_api_name CONSTANT VARCHAR2(30) := 'Generate_Select_Sql';
l_select_clause := 'SELECT 1';
l_select_clause := l_select_clause || ' ,' || l_source_column_name || ' "' || l_alias || '"';
l_select_clause := l_select_clause || ' ,' || l_source_column_name || ' "' || l_alias || '"';
l_select_sql_statement := l_select_clause || ' ' || l_from_clause || ' ' || l_where_clause;
x_select_sql_statement := l_select_sql_statement ;
END generate_select_sql;
SELECT actual_avail_from_date
,actual_avail_to_date
FROM ams_deliverables_vl
WHERE deliverable_id = p_delv_id ;
,p_log_action => FND_API.g_true -- This to be sent as TRUE. It updates the Audit Logs.
,p_commit => FND_API.g_false -- We still have to do some more operations.
,p_api_version_number => p_api_version
,p_init_msg_list => l_init_msg_list
,px_content_item_id => l_citem_id
,px_citem_ver_id => l_citem_ver_id
,px_object_version_number => l_obj_ver_num
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
IBC_CITEM_ADMIN_GRP.insert_components(
p_citem_ver_id => p_parent_citem_ver_id
,p_content_item_ids => l_citem_ids
,p_attribute_type_codes => l_citem_attrs
,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.
,p_commit => FND_API.g_false
,p_api_version_number => p_api_version
,p_init_msg_list => l_init_msg_list
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
PROCEDURE update_submit_section(
p_delv_id IN NUMBER,
p_content_type_code IN VARCHAR2,
p_content_item_name IN VARCHAR2,
p_description IN VARCHAR2,
p_citem_id IN NUMBER,
p_citem_ver_id IN NUMBER,
p_ui_control_type IN VARCHAR2,
p_button_label IN VARCHAR2,
p_ocm_image_id IN NUMBER,
p_alignment IN VARCHAR2,
p_commit IN VARCHAR2 DEFAULT FND_API.g_false,
p_api_version IN NUMBER DEFAULT 1.0,
p_api_validation_level IN NUMBER DEFAULT FND_API.g_valid_level_full,
x_return_status OUT VARCHAR2,
x_msg_count OUT NUMBER,
x_msg_data OUT VARCHAR2
)
IS
--
-- Declare the local variables and cursors here.
--
l_return_status VARCHAR2(1) ;
l_api_name CONSTANT VARCHAR2(30) := 'Update_Submit_Section';
update_content_item(
p_citem_id => p_citem_id
,p_citem_version_id => p_citem_ver_id
,p_content_type_code => p_content_type_code
,p_content_item_name => p_content_item_name
,p_description => p_description
,p_delv_id => p_delv_id
,p_attr_types_for_update => l_attribute_type_codes
,p_attr_values_for_update => l_attributes
,p_attach_file_id => NULL
,p_attach_file_name => NULL
,p_commit => FND_API.g_false
,p_api_version => p_api_version
,p_api_validation_level => p_api_validation_level
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
AMS_Utility_PVT.Error_Message('AMS_ERR_UPDATE_CITEM');
END update_submit_section;
PROCEDURE update_questions_section(
p_delv_id IN NUMBER,
p_section_citem_id IN NUMBER,
p_section_citem_ver_id IN NUMBER,
p_content_type_code IN VARCHAR2,
p_content_item_name IN VARCHAR2,
p_description IN VARCHAR2,
p_attach_file_id IN NUMBER,
p_attach_file_name IN VARCHAR2,
p_commit IN VARCHAR2 DEFAULT FND_API.g_false,
p_api_version IN NUMBER DEFAULT 1.0,
p_init_msg_list IN VARCHAR2 DEFAULT FND_API.g_true,
p_api_validation_level IN NUMBER DEFAULT FND_API.g_valid_level_full,
x_return_status OUT VARCHAR2,
x_msg_count OUT NUMBER,
x_msg_data OUT VARCHAR2
)
IS
--
-- Declare the local variables and cursors here.
-- test cursor for file id.
CURSOR c_fild_id_check IS
SELECT file_id, file_name
FROM fnd_lobs
WHERE file_id = p_attach_file_id ;
SELECT MAX(citem_version_id)
FROM ibc_citem_versions_b
WHERE content_item_id = p_section_citem_id ;
SELECT actual_avail_from_date
,actual_avail_to_date
FROM ams_deliverables_vl
WHERE deliverable_id = p_delv_id ;
SELECT count(1)
FROM ams_cpag_questions_b
WHERE content_item_id = p_section_citem_id
AND profile_field_id IS NOT NULL
AND question_type NOT IN (G_QUESTIONNAIRE, G_SEPARATOR) ;
SELECT DISTINCT de_list_source_type_code
FROM ams_list_src_fields
WHERE list_source_field_id IN
(SELECT profile_field_id
FROM ams_cpag_questions_b
WHERE content_item_id = p_section_citem_id
) ;
SELECT ques.profile_field_id
FROM ams_cpag_questions_b ques, ams_list_src_fields flds
WHERE ques.profile_field_id = flds.list_source_field_id
AND flds.de_list_source_type_code = l_data_src_type_code
AND ques.content_item_id = p_section_citem_id ;
SELECT count(1)
FROM ams_list_src_type_usages
WHERE list_src_used_by_type = 'CPAGE'
AND list_src_used_by_id = p_section_citem_id ;
l_api_name CONSTANT VARCHAR2(30) := 'update_questions_section';
l_select_sql_statement VARCHAR2(4000) ;
AMS_UTILITY_PVT.debug_message(' l_test_file_id in update_questions_section = ' || l_test_file_id);
AMS_UTILITY_PVT.debug_message(' l_test_file_name in update_questions_section = ' || l_test_file_name);
generate_select_sql(
p_data_source_code => l_data_src_type_code
,p_data_source_fields_list => NULL
,p_data_source_field_ids => l_data_src_fld_ids
,x_select_sql_statement => l_select_sql_statement
,x_bind_vars => l_bind_vars_list
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
AMS_UTILITY_PVT.debug_message( ' l_select_sql_statement = ' || l_select_sql_statement );
IF l_select_sql_statement IS NOT NULL
THEN
l_attr_count := l_attr_count + 1;
l_attribute_type_codes(l_attr_count) := G_SELECT_SQL_QUERY ;
l_attributes(l_attr_count) := l_select_sql_statement ;
FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name, 'Data for update');
update_content_item(
p_citem_id => p_section_citem_id
,p_citem_version_id => l_section_citem_ver_id
,p_content_type_code => p_content_type_code
,p_content_item_name => NULL -- We do not allow update on this one yet.
,p_description => NULL -- We do not allow update on this one yet.
,p_delv_id => p_delv_id
,p_attr_types_for_update => l_attribute_type_codes
,p_attr_values_for_update => l_attributes
,p_attach_file_id => p_attach_file_id
,p_attach_file_name => p_attach_file_name
,p_commit => FND_API.g_false
,p_api_version => p_api_version
,p_api_validation_level => p_api_validation_level
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,p_replace_attr_bundle => FND_API.g_true
);
AMS_UTILITY_PVT.debug_message( ' After update_content_item call. ' );
AMS_Utility_PVT.Error_Message('AMS_ERR_UPDATE_CITEM');
UPDATE ibc_citem_versions_tl
SET attachment_attribute_code = 'QUESTIONS_XML' -- hardcoding as QUESTIONS_XML for Questions Section.
WHERE citem_version_id = l_section_citem_ver_id ;
DELETE FROM ams_list_src_type_usages
WHERE list_src_used_by_type = 'CPAGE'
AND list_src_used_by_id = p_section_citem_id;
INSERT INTO ams_list_src_type_usages
(
list_source_type_usage_id
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
,object_version_number
,source_type_code
,list_header_id
,list_src_used_by_type
,list_src_used_by_id
)
select
ams_list_src_type_usages_s.NEXTVAL,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
FND_GLOBAL.CONC_LOGIN_ID,
1,
substr(l_data_src_type_code, 1, 30),
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.
'CPAGE',
p_section_citem_id
from dual
where not exists
( select 'x'
from ams_list_src_type_usages
where list_header_id = 0
and source_type_code = l_data_src_type_code
and list_src_used_by_type = 'CPAGE'
and list_src_used_by_id = p_section_citem_id
) ;
END update_questions_section ;
select file_data into l_blob
from fnd_lobs
where file_id = p_file_id
for update;