[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT element_id
FROM CS_KB_ELEMENTS_B
WHERE element_number = p_element_number;
SELECT DISTINCT s.set_number
FROM CS_KB_SET_ELES se,
CS_KB_SETS_B s
WHERE se.element_id = c_element_id
AND se.set_id = s.set_id;
SELECT COUNT(se.set_id)
INTO l_count
FROM CS_KB_SET_ELES se,
CS_KB_ELEMENTS_B e
WHERE e.element_number = p_element_number
AND se.element_id = e.element_id;
SELECT B.element_type_id
FROM CS_KB_SET_ELE_TYPES A, CS_KB_ELEMENT_TYPES_VL B
WHERE A.set_type_id = p_set_type_id
AND A.optional_flag = 'N'
AND A.element_type_id = B.element_type_id
AND trunc(sysdate) between trunc(nvl(B.start_date_active, sysdate)) and trunc(nvl(B.end_date_active, sysdate))
ORDER BY A.element_type_order ASC;
SELECT set_type_id
INTO l_set_type_id
FROM CS_KB_SETS_B
WHERE set_id = p_set_id;
SELECT count(*)
INTO l_count
FROM CS_KB_ELEMENTS_B e,
CS_KB_SET_ELES se
WHERE se.set_id = p_set_id
AND se.element_id = e.element_id
AND e.element_type_id = rec.element_type_id;
SELECT name
INTO l_element_type_name
FROM CS_KB_ELEMENT_TYPES_TL
WHERE element_type_id = rec.element_type_id
AND language = USERENV('LANG');
SELECT count(*) INTO l_count
FROM cs_kb_set_ele_types se,
cs_kb_sets_b s,
cs_kb_elements_b e
WHERE se.set_type_id = s.set_type_id
AND se.element_type_id = e.element_type_id
AND s.set_number = p_set_number
AND e.element_number = p_element_number;
SELECT count(*) INTO l_count
FROM CS_KB_SET_ELE_TYPES se,
CS_KB_SETS_B s
WHERE se.set_type_id = s.set_type_id
AND s.set_number = p_set_number
AND se.element_type_id = p_ele_type_id;
SELECT count(*) INTO l_count
FROM CS_KB_SET_ELE_TYPES se,
CS_KB_ELEMENTS_B e
WHERE se.set_type_id = p_set_type_id
AND e.element_number = p_element_number
AND se.element_type_id = e.element_type_id;
SELECT count(*) INTO l_count
FROM CS_KB_SET_ELE_TYPES se
WHERE se.set_type_id = p_set_type_id
AND se.element_type_id = p_ele_type_id;
SELECT element_id
FROM CS_KB_SET_ELES
WHERE set_id = c_sid;
SELECT set_type_id
FROM CS_KB_SETS_B
WHERE set_id = p_set_id;
DELETE FROM CS_KB_SET_ELES
WHERE element_id = p_element_id
AND set_id = p_set_id;
UPDATE CS_KB_SETS_B SET
last_update_date = l_date,
last_updated_by = l_user,
last_update_login = l_login
WHERE set_id = p_set_id;
UPDATE CS_KB_SETS_TL SET
last_update_date = l_date,
last_updated_by = l_user,
last_update_login = l_login
WHERE set_id = p_set_id;
SELECT COUNT(*) INTO l_count
FROM CS_KB_ELEMENTS_B
WHERE element_number = p_element_number;
SELECT count(se.element_id) INTO l_count
FROM CS_KB_SET_ELES se, CS_KB_ELEMENTS_B eb
WHERE se.set_id = p_set_id
AND se.element_id = eb.element_id
AND eb.element_number = p_element_number;
SELECT set_type_id INTO l_set_type_id
FROM CS_KB_SETS_B
WHERE set_id = p_set_id;
SELECT element_type_id INTO l_ele_type_id
FROM CS_KB_ELEMENTS_B
WHERE element_id = l_element_id;
SELECT MAX(element_order) INTO l_order
FROM CS_KB_SET_ELES
WHERE set_id = p_set_id;
INSERT INTO CS_KB_SET_ELES (
set_id, element_id, element_order, assoc_degree,
creation_date, created_by,
last_update_date, last_updated_by, last_update_login)
VALUES(
p_set_id, l_element_id, l_order, p_assoc_degree,
l_date, l_created_by, l_date, l_created_by, l_login);
UPDATE CS_KB_SETS_B SET
last_update_date = l_date,
last_updated_by = l_created_by,
last_update_login = l_login
WHERE set_id = p_set_id;
SELECT set_number
INTO l_set_number
FROM CS_KB_SETS_B
WHERE set_id = p_set_id;
UPDATE CS_KB_SETS_TL SET
last_update_date = l_date,
last_updated_by = l_created_by,
last_update_login = l_login
WHERE set_id = p_set_id;
SELECT element_number INTO l_element_number
FROM CS_KB_ELEMENTS_B
WHERE element_id = l_element_id;
SELECT t.element_type_id
FROM CS_KB_SET_ELE_TYPES t, CS_KB_SETS_B s
WHERE t.set_type_id = s.set_type_id
AND s.set_id = CS_KB_SOLUTION_PVT.Get_Latest_Version_Id(p_set_number)
ORDER BY t.element_type_order;
SELECT se.set_id, se.element_id
FROM CS_KB_SET_ELES se, CS_KB_ELEMENTS_B el
WHERE se.set_id = CS_KB_SOLUTION_PVT.Get_Latest_Version_Id(p_set_number)
AND se.element_id = el.element_id
AND el.element_type_id = ele_type_id
ORDER BY se.element_order;
UPDATE CS_KB_SET_ELES
SET element_order = l_counter
WHERE element_id = rec_i.element_id
AND set_id = rec_i.set_id;
SELECT B.element_id from cs_kb_set_eles A, cs_kb_elements_b B, cs_kb_elements_tl C
WHERE A.set_id = c_set_id
AND B.element_id = A.element_id
AND B.element_id = C.element_id
AND B.status = 'DRAFT'
AND C.language = userenv('LANG');
select set_id
from cs_kb_sets_b
where set_id <> p_max_set_id
and set_number = p_set_number
order by creation_date desc;
UPDATE CS_KB_ELEMENTS_B
SET status = 'OBS'
WHERE element_id = l_prev_ver_elem_ids(j);
SELECT element_id from cs_kb_set_eles
WHERE set_id = c_set_id;
SELECT DISTINCT element_id from cs_kb_set_eles
WHERE set_id in
(select set_id from cs_kb_sets_b where set_number = c_set_number
and set_id < c_set_id);
SELECT count(se.Set_id)
FROM CS_KB_SET_ELES se
WHERE se.ELEMENT_ID = p_element_id
AND EXISTS (Select 'x'
From CS_KB_SETS_B s
WHERE s.Set_id = se.set_id
AND (s.latest_version_flag = 'Y'
OR s.viewable_version_flag = 'Y')
);
SELECT element_id from cs_kb_set_eles
WHERE set_id = c_set_id;
select status INTO l_status
from cs_kb_elements_b
where element_id = p_element_id;
UPDATE CS_KB_ELEMENTS_B
SET status = 'OBS'
WHERE element_id = p_element_id;
UPDATE CS_KB_ELEMENTS_B
SET status = 'OBS'
WHERE element_id = p_element_id;
select notes_detail
into l_clob1
from jtf_notes_tl
where jtf_note_id = p_note_id
and language = USERENV('LANG');
select description
into l_clob2
from cs_kb_elements_tl
where element_id = p_element_id
and language = USERENV('LANG')
for update;
JTF_NOTES_PKG.DELETE_ROW(p_note_id);
SELECT description from cs_kb_elements_tl, cs_kb_set_eles
where cs_kb_set_eles.set_id = c_set_id
AND cs_kb_set_eles.element_id = cs_kb_elements_tl.element_id
and cs_kb_elements_tl.language = USERENV('LANG');
* forwards to Update_Set_With_Validation_2
*/
FUNCTION Update_Set_With_Validation
(
p_api_version in number,
p_init_msg_list in varchar2 := FND_API.G_FALSE,
p_commit in varchar2 := FND_API.G_FALSE,
p_validation_level in number := FND_API.G_VALID_LEVEL_FULL,
x_return_status OUT NOCOPY varchar2,
x_msg_count OUT NOCOPY number,
x_msg_data OUT NOCOPY varchar2,
p_set_number in varchar2,
p_set_type_name in varchar2,
p_set_visibility in varchar2,
p_set_title in varchar2,
p_set_products in JTF_VARCHAR2_TABLE_2000,
p_set_platforms in JTF_VARCHAR2_TABLE_2000,
p_set_categories in JTF_VARCHAR2_TABLE_2000,
p_ele_type_name_tbl in JTF_VARCHAR2_TABLE_2000,
p_ele_dist_tbl in JTF_VARCHAR2_TABLE_2000,
p_ele_content_type_tbl in JTF_VARCHAR2_TABLE_2000,
p_ele_summary_tbl in JTF_VARCHAR2_TABLE_2000,
p_ele_nos_tbl in JTF_VARCHAR2_TABLE_2000,
p_ele_nos_upd_tbl in JTF_VARCHAR2_TABLE_2000,
p_ele_dist_upd_tbl in JTF_VARCHAR2_TABLE_2000,
p_ele_content_type_upd_tbl in JTF_VARCHAR2_TABLE_2000,
p_ele_summary_upd_tbl in JTF_VARCHAR2_TABLE_2000,
p_set_category_last_names in JTF_VARCHAR2_TABLE_2000,
x_created_ele_ids_tbl OUT NOCOPY JTF_NUMBER_TABLE,
x_ele_ids_upd_tbl OUT NOCOPY JTF_NUMBER_TABLE,
x_set_id OUT NOCOPY number) RETURN NUMBER IS
BEGIN
RETURN Update_Set_With_Validation_2(
p_api_version,
p_init_msg_list,
p_commit,
p_validation_level,
x_return_status,
x_msg_count,
x_msg_data,
p_set_number,
p_set_type_name,
p_set_visibility,
p_set_title,
p_set_products,
p_set_platforms,
p_set_categories,
p_ele_type_name_tbl,
p_ele_dist_tbl,
p_ele_content_type_tbl,
p_ele_summary_tbl,
p_ele_nos_tbl,
p_ele_nos_upd_tbl,
p_ele_dist_upd_tbl,
p_ele_content_type_upd_tbl,
p_ele_summary_upd_tbl,
p_set_category_last_names,
x_created_ele_ids_tbl,
x_ele_ids_upd_tbl,
x_set_id,
'>');
END Update_Set_With_validation;
FUNCTION Update_Set_With_Validation_2
(
p_api_version in number,
p_init_msg_list in varchar2 := FND_API.G_FALSE,
p_commit in varchar2 := FND_API.G_FALSE,
p_validation_level in number := FND_API.G_VALID_LEVEL_FULL,
x_return_status OUT NOCOPY varchar2,
x_msg_count OUT NOCOPY number,
x_msg_data OUT NOCOPY varchar2,
p_set_number in varchar2,
p_set_type_name in varchar2,
p_set_visibility in varchar2,
p_set_title in varchar2,
p_set_products in JTF_VARCHAR2_TABLE_2000,
p_set_platforms in JTF_VARCHAR2_TABLE_2000,
p_set_categories in JTF_VARCHAR2_TABLE_2000,
p_ele_type_name_tbl in JTF_VARCHAR2_TABLE_2000,
p_ele_dist_tbl in JTF_VARCHAR2_TABLE_2000,
p_ele_content_type_tbl in JTF_VARCHAR2_TABLE_2000,
p_ele_summary_tbl in JTF_VARCHAR2_TABLE_2000,
p_ele_nos_tbl in JTF_VARCHAR2_TABLE_2000,
p_ele_nos_upd_tbl in JTF_VARCHAR2_TABLE_2000,
p_ele_dist_upd_tbl in JTF_VARCHAR2_TABLE_2000,
p_ele_content_type_upd_tbl in JTF_VARCHAR2_TABLE_2000,
p_ele_summary_upd_tbl in JTF_VARCHAR2_TABLE_2000,
p_set_category_last_names in JTF_VARCHAR2_TABLE_2000,
x_created_ele_ids_tbl OUT NOCOPY JTF_NUMBER_TABLE,
x_ele_ids_upd_tbl OUT NOCOPY JTF_NUMBER_TABLE,
x_set_id OUT NOCOPY number,
p_delim IN VARCHAR2
) RETURN NUMBER IS
l_set_product_segments JTF_VARCHAR2_TABLE_2000;
RETURN Update_Set_With_Validation_3(
p_api_version,
p_init_msg_list,
p_commit,
p_validation_level,
x_return_status,
x_msg_count,
x_msg_data,
p_set_number,
p_set_type_name,
p_set_visibility,
p_set_title,
p_set_products,
l_set_product_segments,
p_set_platforms,
l_set_platform_segments,
p_set_categories,
p_ele_type_name_tbl,
p_ele_dist_tbl,
p_ele_content_type_tbl,
p_ele_summary_tbl,
p_ele_nos_tbl,
p_ele_nos_upd_tbl,
p_ele_dist_upd_tbl,
p_ele_content_type_upd_tbl,
p_ele_summary_upd_tbl,
p_set_category_last_names,
x_created_ele_ids_tbl,
x_ele_ids_upd_tbl,
x_set_id,
'>');
END Update_Set_With_validation_2;
FUNCTION Update_Set_With_Validation_3
(
p_api_version in number,
p_init_msg_list in varchar2 := FND_API.G_FALSE,
p_commit in varchar2 := FND_API.G_FALSE,
p_validation_level in number := FND_API.G_VALID_LEVEL_FULL,
x_return_status OUT NOCOPY varchar2,
x_msg_count OUT NOCOPY number,
x_msg_data OUT NOCOPY varchar2,
p_set_number in varchar2,
p_set_type_name in varchar2,
p_set_visibility in varchar2,
p_set_title in varchar2,
p_set_products in JTF_VARCHAR2_TABLE_2000,
p_set_product_segments in JTF_VARCHAR2_TABLE_2000,
p_set_platforms in JTF_VARCHAR2_TABLE_2000,
p_set_platform_segments in JTF_VARCHAR2_TABLE_2000,
p_set_categories in JTF_VARCHAR2_TABLE_2000,
p_ele_type_name_tbl in JTF_VARCHAR2_TABLE_2000,
p_ele_dist_tbl in JTF_VARCHAR2_TABLE_2000,
p_ele_content_type_tbl in JTF_VARCHAR2_TABLE_2000,
p_ele_summary_tbl in JTF_VARCHAR2_TABLE_2000,
p_ele_nos_tbl in JTF_VARCHAR2_TABLE_2000,
p_ele_nos_upd_tbl in JTF_VARCHAR2_TABLE_2000,
p_ele_dist_upd_tbl in JTF_VARCHAR2_TABLE_2000,
p_ele_content_type_upd_tbl in JTF_VARCHAR2_TABLE_2000,
p_ele_summary_upd_tbl in JTF_VARCHAR2_TABLE_2000,
p_set_category_last_names in JTF_VARCHAR2_TABLE_2000,
x_created_ele_ids_tbl OUT NOCOPY JTF_NUMBER_TABLE,
x_ele_ids_upd_tbl OUT NOCOPY JTF_NUMBER_TABLE,
x_set_id OUT NOCOPY number,
p_delim IN VARCHAR2
) RETURN NUMBER IS
l_set_type_id NUMBER;
select element_id
from cs_kb_set_eles
where set_id = p_set_id;
select product_id, product_org_id
from cs_kb_set_products
where set_id = p_set_id;
select platform_id, platform_org_id
from cs_kb_set_platforms
where set_id = p_set_id;
select category_id
from cs_kb_set_categories
where set_id = p_set_id;
SAVEPOINT Update_Set;
l_return_val := Validate_Set_Type_Name_Update(
x_set_id,
p_set_type_name,
l_set_type_id);
CS_KB_SOLUTION_PVT.Update_Solution
( p_set_id => x_set_id,
p_set_number => p_set_number,
p_set_type_id => l_set_type_id,
p_name => p_set_title,
p_visibility_id => l_set_visibility_id,
p_status => 'PUB',
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_attribute_category => null,
p_attribute1 => null,
p_attribute2 => null,
p_attribute3 => null,
p_attribute4 => null,
p_attribute5 => null,
p_attribute6 => null,
p_attribute7 => null,
p_attribute8 => null,
p_attribute9 => null,
p_attribute10 => null,
p_attribute11 => null,
p_attribute12 => null,
p_attribute13 => null,
p_attribute14 => null,
p_attribute15 => null
);
p_validate_type => 'UPDATE',
p_set_id => x_set_id,
p_given_element_ids => x_ele_ids_upd_tbl,
p_given_ele_nums => p_ele_nos_upd_tbl,
p_given_ele_type_ids => l_ele_type_id_upd_tbl,
p_given_ele_dist_ids => l_ele_dist_id_upd_tbl,
p_given_ele_content_types => l_ele_conttype_codes_upd_tbl,
p_given_ele_summaryies => p_ele_summary_upd_tbl,
p_element_ids => l_element_ids,
p_element_type_ids => l_element_type_ids,
p_element_dist_ids => l_element_dist_ids,
p_element_content_types => l_element_content_type_codes,
p_element_summaries => p_ele_summary_tbl,
p_element_dummy_detail => l_temp_clob,
p_set_product_ids => l_set_product_ids,
p_set_platform_ids => l_set_platform_ids,
p_set_category_ids => l_set_category_ids,
x_created_element_ids => x_created_ele_ids_tbl,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
ROLLBACK TO Update_Set;
ROLLBACK TO Update_Set;
x_msg_data := 'Invalid Solution Number: ' || p_set_number || '. Solutions to be updated should be valid and PUBLISHED.' ;
ROLLBACK TO Update_Set;
ROLLBACK TO Update_Set;
ROLLBACK TO Update_Set;
ROLLBACK TO Update_Set;
x_msg_data := 'Update Solution: ' || SQLERRM ;
END Update_Set_With_validation_3;
l_temp_update_return number;
IF (p_validate_type = 'UPDATE') THEN
l_elmt_status := 'PUBLISHED';
l_temp_update_return := CS_KB_ELEMENTS_AUDIT_PKG.Update_Element_CLOB(
p_element_id => p_given_element_ids(i),
p_element_number => p_given_ele_nums(i),
p_element_type_id => p_given_ele_type_ids(i),
p_name => p_given_ele_summaryies(i),
p_desc => p_element_dummy_detail,
p_access_level => p_given_ele_dist_ids(i),
p_content_type => p_given_ele_content_types(i),
p_status => 'PUBLISHED');
IF (l_temp_update_return < 0) then
raise FND_API.G_EXC_UNEXPECTED_ERROR;
ELSIF (p_validate_type = 'UPDATE') THEN
ROLLBACK TO Update_Set;
ELSIF (p_validate_type = 'UPDATE') THEN
ROLLBACK TO Update_Set;
ELSIF (p_validate_type = 'UPDATE') THEN
ROLLBACK TO Update_Set;
ELSIF (p_validate_type = 'UPDATE') THEN
ROLLBACK TO Update_Set;
ELSIF (p_validate_type = 'UPDATE') THEN
ROLLBACK TO Update_Set;
ELSIF (p_validate_type = 'UPDATE') THEN
ROLLBACK TO Update_Set;
select set_type_id
into x_set_type_id from cs_kb_set_types_tl
where language = userenv('LANG')
and upper(name) = upper(p_set_type_name);
FUNCTION Validate_Set_Type_Name_Update
(
p_set_id IN NUMBER,
p_set_type_name IN VARCHAR2,
x_set_type_id OUT NOCOPY NUMBER
) RETURN NUMBER IS
BEGIN
select sets.set_type_id
into x_set_type_id
from cs_kb_set_types_tl type,
cs_kb_sets_b sets
where type.language = userenv('LANG')
and upper(type.name) = upper(p_set_type_name)
and sets.set_type_id = type.set_type_id
and sets.set_id = p_set_id;
END Validate_Set_Type_Name_Update;
select flow_details_id
from cs_kb_wf_flows_tl flow,
cs_kb_wf_flow_details detail,
cs_lookups lookup
where flow.name = p_flow_name
and flow.language = userenv('LANG')
and flow.flow_id = detail.flow_id
and detail.action = 'PUB'
and detail.step = lookup.lookup_code
and NVL(detail.END_DATE, sysdate) >= sysdate
and lookup.lookup_type = 'CS_KB_STATUS'
and lookup.meaning = p_flow_step;
select set_id
into x_set_id
from cs_kb_sets_b a
where a.set_number = p_set_number
and a.status = 'PUB'
and a.latest_version_flag = 'Y';
select element_type_id
into x_element_type_id from cs_kb_element_types_tl
where language = userenv('LANG')
and upper(name) = upper(p_element_type_name);
select element_type_id
into x_element_type_id
from cs_kb_elements_b
where element_number = p_element_number;
select element_type_id from cs_kb_set_ele_types
where set_type_id = c_set_type_id;
select visibility_id
from cs_kb_visibilities_vl v
where upper(v.name) like upper(c_visibility_name);
select lookup_code into x_access_level_value
from cs_lookups where lookup_type = 'CS_KB_ACCESS_LEVEL'
and upper(meaning) like upper(p_access_level_name);
select lookup_code
INTO p_ele_content_type_code
from cs_lookups
where lookup_type = 'CS_KB_CONTENT_TYPE'
and meaning = p_ele_content_type;
select element_id INTO x_latest_id
from cs_kb_elements_b
where element_number = p_ele_no
and status = 'PUBLISHED';
'SELECT it.inventory_item_id '||
'FROM mtl_system_items_vl it, mtl_item_categories ic '||
'where it.inventory_item_id = ic.inventory_item_id '||
'and it.organization_id = ic.organization_id '||
'and it.organization_id = :l_org_id '||
'and ic.category_set_id = :l_prof_val '||
'and upper(it.description) = upper(:l_name) ';
SELECT it.inventory_item_id
FROM mtl_system_items_vl it, mtl_item_categories ic
where it.inventory_item_id = ic.inventory_item_id
and it.organization_id = ic.organization_id
and it.organization_id = cp_org_id
and ic.category_set_id = cp_prof_val
and upper(it.concatenated_segments) = upper(cp_segments) ;
'SELECT it.inventory_item_id '||
'FROM mtl_system_items_vl it, mtl_item_categories ic '||
'where it.inventory_item_id = ic.inventory_item_id '||
'and it.organization_id = ic.organization_id '||
'and it.organization_id = :l_org_id '||
'and ic.category_set_id = :l_prof_val '||
'and upper(it.description) = upper(:l_name) ';
SELECT it.inventory_item_id
FROM mtl_system_items_vl it, mtl_item_categories ic
where it.inventory_item_id = ic.inventory_item_id
and it.organization_id = ic.organization_id
and it.organization_id = cp_org_id
and ic.category_set_id = cp_prof_val
and upper(it.concatenated_segments) = upper(cp_segments) ;
* concurrent program. Depending on the export mode selected,
* It can export either all published solutions or the
* latest version of all solutions in a particular category.
* Parameters:
* p_category_name - This should be the full textual path
* of the category for which solutions will be exported.
* The individual category names should be separated by
* a '>'. Example: 'Home>Desktop>Monitor'
* p_sol_status - One of 2 mode values: ALL or PUB. This
* determines whether only published solutions are exported
* or the latest version of all non-obsoleted solutions
* are exported.
*/
/*
* forwards to EXPORT_SOLUTIONS_2
*/
PROCEDURE EXPORT_SOLUTIONS
(
errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY NUMBER,
p_category_name IN VARCHAR2,
p_sol_status IN VARCHAR2
) IS
BEGIN
EXPORT_SOLUTIONS_2(errbuf, retcode, p_category_name, p_sol_status, '>');
SELECT P.FULL_NAME
FROM FND_USER fu
,PER_ALL_PEOPLE_F P
WHERE sysdate BETWEEN nvl(fu.start_date, sysdate-1)
AND nvl(fu.end_date, sysdate+1)
AND fu.employee_id = P.person_id
AND TRUNC(SYSDATE) BETWEEN P.EFFECTIVE_START_DATE
AND P.EFFECTIVE_END_DATE
AND fu.User_id = V_USER_ID;
SELECT hp.party_name
FROM hz_parties hp
,fnd_user fu
WHERE hp.party_type = 'PERSON'
AND sysdate BETWEEN nvl(fu.start_date, sysdate-1)
AND nvl(fu.end_date, sysdate+1)
AND fu.customer_id = hp.party_id
AND fu.customer_id is not null
AND fu.employee_id is null
AND fu.User_id = V_USER_ID;
SELECT hp.party_name
from hz_parties hp
,hz_relationships hr
,fnd_user fu
WHERE hr.party_id = fu.customer_id
AND hr.subject_id = hp.party_id
AND hr.relationship_code in ('EMPLOYEE_OF', 'CONTACT_OF')
AND hp.party_type = 'PERSON'
AND hr.subject_table_name = 'HZ_PARTIES'
AND hr.object_table_name = 'HZ_PARTIES'
AND sysdate BETWEEN nvl(fu.start_date, sysdate-1)
AND nvl(fu.end_date, sysdate+1)
AND fu.customer_id is not null
AND fu.employee_id is null
AND fu.User_id = V_USER_ID;
SELECT fu.user_name
FROM FND_USER fu
WHERE fu.User_id = V_USER_ID;
This program updates the usage score based on the usage. It consists of two
sections: 1. update usage scores of those solutions that were published AFTER
the (sysdate - time_span) date, 2. update usage scores of those solutions that
were publishec BEFORE the (sysdate - time_span) date.
In the first section, we compensate the score with an aging factor. The aging
actor is calculated as:
1 - (sysdate - last_update_date)/time_span. The agian factor should only
range from 0 - 1, that is it is 1 if the last update date is the sysdate, and
0 if it is (sysdate - time_span).
In both cases, we only look at those solutions of which the feedback or
linkage were create AFTERthe cut-off date (sysdate - time_span).
*/
PROCEDURE Update_Solution_Usage_Score (
p_commit IN VARCHAR2 := FND_API.G_FALSE)
IS
-- Default time usage to 1 year if the profile is not set.
CURSOR Get_Time_Usage_Csr IS
Select nvl(fnd_profile.value('CS_KB_USAGE_TIME_SPAN'), 365) from dual;
select avg(usage_score)/p_time_usge_span
from cs_kb_sets_b
where status = 'PUB';
select avg(usage_score) - (p_coefficient*stddev(usage_score)/sqrt(count(set_id)))
from cs_kb_sets_b
where status = 'PUB'
and last_update_date > (sysdate - p_time_usage_span);
select avg(usage_score) + (p_coefficient*stddev(usage_score)/sqrt(count(set_id)))
from cs_kb_sets_b
where status = 'PUB'
and last_update_date > (sysdate - p_time_usage_span);
select fnd_profile.value('CS_KB_USAGE_LIMIT_FACTOR') from dual;
SELECT count(set_id)
FROM cs_kb_sets_b
WHERE status = 'PUB'
AND last_update_date > (SYSDATE - p_time_usage_span);
-- 1. Update usage scores of solutions that were published AFTER
-- (sysadate - l_time_usage) based on the used history.
-- 1.1 Get score from used history
update cs_kb_sets_b c set usage_score =
(
select
round(
sum(to_number(cl.meaning)*(1-(sysdate-a.creation_date)/l_time_usage))
+
(l_avg_score * ( 1 - (sysdate - c.last_update_date)/l_time_usage) )
)
from cs_kb_set_used_hists a, cs_lookups cl
where a.set_id = c.set_id
and a.used_type = cl.lookup_code
and cl.lookup_type = 'CS_KB_USAGE_TYPE_WEIGHT'
and a.creation_date >= (sysdate - l_time_usage)
and c.last_update_date > (sysdate - l_time_usage)
and c.status = 'PUB'
group by c.set_id, c.last_update_date
)
where c.status = 'PUB'
and c.last_update_date > (sysdate-l_time_usage)
and exists (
select null
from cs_kb_set_used_hists a, cs_lookups cl
where a.set_id = c.set_id
and a.used_type = cl.lookup_code
and cl.lookup_type = 'CS_KB_USAGE_TYPE_WEIGHT'
and a.creation_date >= (sysdate - l_time_usage)
and c.last_update_date > (sysdate - l_time_usage)
and c.status = 'PUB'
);
--1.2 Update usage scores of solutions that were published AFTER
-- sysdate - l_time_usage based on the solution linkage.
update cs_kb_sets_b c set usage_score =
(
select
round(
sum(to_number(cl.meaning)*(1-(sysdate-a.creation_date)/l_time_usage))
+
(l_avg_score * ( 1 - (sysdate - c.last_update_date)/l_time_usage) )
) + c.usage_score
from cs_kb_set_links a, cs_lookups cl
where a.set_id = c.set_id
and a.link_type = cl.lookup_code
and cl.lookup_type = 'CS_KB_USAGE_TYPE_WEIGHT'
and a.creation_date >= (sysdate - l_time_usage)
and c.last_update_date > (sysdate - l_time_usage)
and c.status = 'PUB'
group by c.set_id, c.last_update_date
)
where c.status = 'PUB'
and c.last_update_date > (sysdate-l_time_usage)
and exists (
select null
from cs_kb_set_links a, cs_lookups cl
where a.set_id = c.set_id
and a.link_type = cl.lookup_code
and cl.lookup_type = 'CS_KB_USAGE_TYPE_WEIGHT'
and a.creation_date >= (sysdate - l_time_usage)
and c.last_update_date > (sysdate - l_time_usage)
and c.status = 'PUB'
);
-- 2. Update usage scores of solutions that were published BEFORE
-- sysdate - l_time_usage based on the used history. Aging factor
-- compensation will not be added in this update.
update cs_kb_sets_b c set usage_score =
(
select round(sum(to_number(cl.meaning)*(1-(sysdate-a.creation_date)/l_time_usage))
)
from cs_kb_set_used_hists a, cs_lookups cl
where a.set_id = c.set_id
and a.used_type = cl.lookup_code
and cl.lookup_type = 'CS_KB_USAGE_TYPE_WEIGHT'
and a.creation_date >= (sysdate - l_time_usage)
and c.last_update_date <= (sysdate - l_time_usage)
and c.status = 'PUB'
group by c.set_id, c.last_update_date
)
where c.status = 'PUB'
and c.last_update_date <= (sysdate-l_time_usage)
and exists (
select null
from cs_kb_set_used_hists a, cs_lookups cl
where a.set_id = c.set_id
and a.used_type = cl.lookup_code
and cl.lookup_type = 'CS_KB_USAGE_TYPE_WEIGHT'
and a.creation_date >= (sysdate - l_time_usage)
and c.last_update_date <= (sysdate - l_time_usage)
and c.status = 'PUB'
);
-- 2.1 Update usage scores of solutions that were published BEFORE
-- sysdate - l_time_usage based on the solution linkage.
update cs_kb_sets_b c set usage_score =
(
select round(sum(to_number(cl.meaning)*(1-(sysdate-a.creation_date)/l_time_usage))
) + c.usage_score
from cs_kb_set_links a, cs_lookups cl
where a.set_id = c.set_id
and a.link_type = cl.lookup_code
and cl.lookup_type = 'CS_KB_USAGE_TYPE_WEIGHT'
and a.creation_date >= (sysdate - l_time_usage)
and c.last_update_date <= (sysdate - l_time_usage)
and c.status = 'PUB'
group by c.set_id, c.last_update_date
)
where c.status = 'PUB'
and c.last_update_date <= (sysdate-l_time_usage)
and exists (
select null
from cs_kb_set_links a, cs_lookups cl
where a.set_id = c.set_id
and a.link_type = cl.lookup_code
and cl.lookup_type = 'CS_KB_USAGE_TYPE_WEIGHT'
and a.creation_date >= (sysdate - l_time_usage)
and c.last_update_date <= (sysdate - l_time_usage)
and c.status = 'PUB'
);
-- Update the normalized usage score column.
Open Get_Coefficient_Csr;
-- update norm_usage_score
If (l_higher - l_lower) <> 0 Then --5705547
update cs_kb_sets_b set norm_usage_score = (
( decode(sign(decode(sign(usage_score - l_lower),
-1, l_lower,
usage_score) - l_higher),
-1, decode(sign(usage_score - l_lower),
-1, l_lower,
usage_score),
l_higher) - l_lower)/(l_higher - l_lower)*100 )
where status = 'PUB';
END Update_Solution_Usage_Score;
select category_id
from cs_kb_soln_categories_b
start with category_id = p_catid
connect by prior parent_category_id = category_id;
select name into l_cat_name
from cs_kb_soln_categories_tl
where language = userenv('LANG') and category_id = category_id_tbl(j);
select category_id from
cs_kb_soln_categories_tl
where upper(name) = upper(p_last_name)
and language = userenv('LANG');
select tl.name
from ( SELECT category_id, level lev
FROM cs_kb_soln_categories_b
START WITH category_id = c_id
CONNECT BY prior parent_category_id = category_id
) b, cs_kb_soln_categories_tl tl
where
b.category_id = tl.category_id
and tl.language = userenv( 'LANG' )
order by b.lev;
* concurrent program. Depending on the export mode selected,
* It can export either all published solutions or the
* latest version of all solutions in a particular category.
* The third parameter is the delimiter, which the concurrent
* program defaults to '>'. However, the user may need to
* specify a different delimiter, if any category has '>' its name
*
* Parameters:
* p_category_name - This should be the full textual path
* of the category for which solutions will be exported.
* The individual category names should be separated by
* the delimiter. Example: 'Home Desktop Monitor'
* p_sol_status - One of 2 mode values: ALL or PUB. This
* determines whether only published solutions are exported
* or the latest version of all non-obsoleted solutions
* are exported.
* delim - The delimiter used
*/
PROCEDURE EXPORT_SOLUTIONS_2
(
errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY NUMBER,
p_category_name IN VARCHAR2,
p_sol_status IN VARCHAR2,
p_delim IN VARCHAR2
) IS
-- Fetch info for the published version of all published solutions
-- in a category
CURSOR get_solution_info_pub(c_category_id IN NUMBER)
IS
select /*+ index(sc) */a.set_id, a.set_number, c.name, v.name, b.name, a.status
from cs_kb_sets_b a, cs_kb_sets_tl b, cs_kb_set_types_vl c,
cs_kb_visibilities_vl v, cs_kb_set_categories sc
where a.set_type_id = c.set_type_id
and a.set_id = sc.set_id and sc.category_id = c_category_id
and a.set_id = b.set_id and b.language = userenv('LANG')
and a.visibility_id = v.visibility_id
and a.status = 'PUB';
select /*+ index(sc) */ a.set_id, a.set_number, c.name, v.name, b.name, a.status
from cs_kb_sets_b a, cs_kb_sets_tl b, cs_kb_set_types_vl c,
cs_kb_visibilities_vl v, cs_kb_set_categories sc
where a.set_type_id = c.set_type_id
and a.set_id = sc.set_id and sc.category_id = c_category_id
and a.set_id = b.set_id and b.language = userenv('LANG')
and a.visibility_id = v.visibility_id
and a.status <> 'OBS'
and a.latest_version_flag = 'Y';
select a.element_number, d.name, e.meaning,
NVL(a.content_type, 'TEXT/HTML'), b.name, b.description, a.status
from cs_kb_elements_b a , cs_kb_elements_tl b, cs_kb_set_eles c,
cs_kb_element_types_vl d, cs_lookups e
where a.element_id = c.element_id and c.set_id = c_set_id
and a.element_id = b.element_id and b.language = userenv('LANG')
and a.element_type_id = d.element_type_id
and a.access_level = e.lookup_code and e.lookup_type = 'CS_KB_ACCESS_LEVEL';
SELECT it.description,it.concatenated_segments
FROM mtl_system_items_vl it, mtl_item_categories ic, cs_kb_set_products a
where it.inventory_item_id = ic.inventory_item_id
and it.organization_id = ic.organization_id
and it.organization_id = cs_std.get_item_valdn_orgzn_id
and ic.category_set_id = fnd_profile.value('CS_KB_PRODUCT_CATEGORY_SET')
and it.inventory_item_id = a.product_id
and a.set_id = c_set_id;
SELECT it.description,it.concatenated_segments
FROM mtl_system_items_vl it, mtl_item_categories ic, cs_kb_set_platforms a
where it.inventory_item_id = ic.inventory_item_id
and it.organization_id = ic.organization_id
and it.organization_id = cs_std.get_item_valdn_orgzn_id
and ic.category_set_id = fnd_profile.value('CS_SR_PLATFORM_CATEGORY_SET')
and it.inventory_item_id = a.platform_id
and a.set_id = c_set_id;
Select a.category_id
from cs_kb_set_categories a
where a.set_id = c_set_id;
SELECT
eb.Element_Number,
replace(
replace(
replace(
replace(
replace(et.description, '&','&'||'amp;' )
SELECT /*+ index(sc) */ count(a.set_number) INTO l_soln_count
from cs_kb_sets_b a, cs_kb_sets_tl b, cs_kb_set_types_vl c,
cs_kb_visibilities_vl v, cs_kb_set_categories sc
where a.set_type_id = c.set_type_id
and a.set_id = sc.set_id and sc.category_id = l_category_id
and a.set_id = b.set_id and b.language = userenv('LANG')
and a.visibility_id = v.visibility_id
and a.status <> 'OBS'
and a.latest_version_flag = 'Y';
SELECT count(a.set_number) INTO l_soln_count
from cs_kb_sets_b a, cs_kb_sets_tl b, cs_kb_set_types_vl c,
cs_kb_visibilities_vl v, cs_kb_set_categories sc
where a.set_type_id = c.set_type_id
and a.set_id = sc.set_id and sc.category_id = l_category_id
and a.set_id = b.set_id and b.language = userenv('LANG')
and a.visibility_id = v.visibility_id
and a.status = 'PUB';
FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' ' || '');
select meaning
INTO l_elmt_content_type
from cs_lookups
where lookup_type = 'CS_KB_CONTENT_TYPE'
and lookup_code = l_elmt_cts(l_elmt_index);
FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' ' || '');