[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| Update_Element_Element
| 19 Jul 05 Matt Kettle Fix for 4464403 - Clone set and set eles
| changed to take Source Set id. Removed
| Get_Previous_Version_id
| 20 Jul 07 ISUGAVAN Bug fix 5947078(FP for Bug 5931800)
| 12 Sep 08 mmaiya Bugfix 7117546 - Unlock Locked Solutions in
| Draft Mode
| 06 May 09 mmaiya 12.1.3 Project: Search within attachments
| 22-July-2011 isugavan Bug 12751807 - UNABLE TO UNLOCK A LOCKED SOLUTION AFTER REJECTION
*=======================================================================*/
PROCEDURE Get_Who(
X_SYSDATE OUT NOCOPY DATE,
X_USER_ID OUT NOCOPY NUMBER,
X_LOGIN_ID OUT NOCOPY NUMBER )
IS
BEGIN
X_SYSDATE := SYSDATE;
SELECT set_number, status, flow_details_id, locked_by
INTO X_SET_NUMBER, X_STATUS, X_FLOW_DETAILS_ID, X_LOCKED_BY
FROM CS_KB_SETS_B
WHERE set_id = p_set_id;
SELECT set_number
INTO l_set_number
FROM CS_KB_SETS_B
WHERE set_id = p_set_id;
SELECT set_id
FROM CS_KB_SETS_B
WHERE set_number = p_set_number
AND latest_version_flag = 'Y';
SELECT MAX(set_id)
INTO l_published_set_id
FROM CS_KB_SETS_B
WHERE set_number = p_set_number
AND status = 'PUB';
SELECT MAX(set_id)
INTO l_obsoleted_set_id
FROM CS_KB_SETS_B
WHERE set_number = p_set_number
AND status = 'OBS';
SELECT name
INTO l_solution_title
FROM CS_KB_SETS_TL
WHERE set_id = p_set_id
AND language = USERENV('LANG');
SELECT locked_by, lock_date
INTO X_LOCKED_BY, X_LOCK_DATE
FROM CS_KB_SETS_B
WHERE set_id = Get_Latest_Version_Id(p_set_number);
UPDATE CS_KB_SETS_B
SET locked_by = P_LOCKED_BY,
lock_date = P_LOCK_DATE
WHERE set_id = P_SET_ID;
SELECT locked_by
INTO l_locked_by
FROM CS_KB_SETS_B
WHERE set_id = P_SET_ID;
SELECT locked_by
INTO l_locked_by
FROM CS_KB_SETS_B
WHERE set_id = P_SET_ID FOR UPDATE;
UPDATE CS_KB_SETS_B
SET locked_by = P_USER_ID
WHERE set_id = P_SET_ID;
UPDATE CS_KB_SETS_B
SET locked_by = NULL
WHERE set_number = p_set_number;
PROCEDURE Update_Status(
P_SET_ID IN NUMBER,
P_STATUS IN VARCHAR2 )
IS
l_set_number VARCHAR2(30);
UPDATE CS_KB_SETS_B
SET status = P_STATUS
WHERE set_id = P_SET_ID;
END Update_Status;
PROCEDURE Update_Status(
P_SET_NUMBER IN VARCHAR2,
P_STATUS IN VARCHAR2 )
IS
l_max_set_id NUMBER;
Update_Status(p_set_id => l_max_set_id,
p_status => p_status);
END Update_Status;
UPDATE CS_KB_SETS_B
SET status = 'OUT',
--viewable_version_flag = 'N'
viewable_version_flag = null
WHERE set_number = P_SET_NUMBER
AND set_id <> P_CURRENT_SET_ID
AND status = 'PUB';
SELECT language,
source_lang,
name,
description,
composite_assoc_index,
composite_assoc_attach_index, --12.1.3
positive_assoc_index,
negative_assoc_index
FROM CS_KB_SETS_TL
WHERE Set_Id = v_set_id;
SELECT * INTO b_rec
FROM CS_KB_SETS_B
WHERE set_id = l_old_set_id;
UPDATE CS_KB_SETS_B
SET LATEST_VERSION_FLAG = null --'N'
WHERE SET_NUMBER = b_rec.set_number
AND SET_ID = l_old_set_id;
INSERT INTO CS_KB_SETS_B (
set_id,
set_number,
set_type_id,
status,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
locked_by,
lock_date,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
flow_details_id,
priority_code,
original_author,
original_author_date,
latest_version_flag,
visibility_id,
USAGE_SCORE,
NORM_USAGE_SCORE
) VALUES (
CS_KB_SETS_S.NEXTVAL,
b_rec.set_number,
b_rec.set_type_id,
p_status,
l_SYSDATE,
l_user_id,
l_SYSDATE,
l_user_id,
l_login_id,
p_locked_by,
l_SYSDATE,
b_rec.attribute_category,
b_rec.attribute1,
b_rec.attribute2,
b_rec.attribute3,
b_rec.attribute4,
b_rec.attribute5,
b_rec.attribute6,
b_rec.attribute7,
b_rec.attribute8,
b_rec.attribute9,
b_rec.attribute10,
b_rec.attribute11,
b_rec.attribute12,
b_rec.attribute13,
b_rec.attribute14,
b_rec.attribute15,
p_flow_details_id,
b_rec.priority_code,
b_rec.original_author,
b_rec.original_author_date,
'Y',
b_rec.visibility_id,
b_rec.usage_score,
b_rec.norm_usage_score
)
RETURNING SET_ID INTO l_new_set_id;
UPDATE CS_KB_SETS_B
SET VIEWABLE_VERSION_FLAG = decode(status, 'PUB','Y',null)
WHERE SET_NUMBER = b_rec.set_number;
UPDATE CS_KB_SETS_B s
SET s.VIEWABLE_VERSION_FLAG = 'Y'
WHERE s.SET_NUMBER = b_rec.set_number
AND s.STATUS <> 'OBS'
AND s.LATEST_VERSION_FLAG = 'Y'
AND NOT EXISTS (SELECT 'x'
FROM CS_KB_SETS_B s3
WHERE s3.set_number = s.set_number
AND s3.STATUS = 'PUB');
INSERT INTO CS_KB_SETS_TL (
set_id,
language,
source_lang,
name,
description,
composite_assoc_index,
composite_assoc_attach_index, --12.1.3
positive_assoc_index,
negative_assoc_index,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login
) VALUES (
l_new_set_id,
tl_rec.language,
tl_rec.source_lang,
tl_rec.name,
tl_rec.description,
tl_rec.composite_assoc_index,
tl_rec.composite_assoc_attach_index, --12.1.3
tl_rec.positive_assoc_index,
tl_rec.negative_assoc_index,
l_SYSDATE,
l_user_id,
l_SYSDATE,
l_user_id,
l_login_id
);
CURSOR GET_ELES_TO_UPDATE (p_set_id IN NUMBER) IS
SELECT distinct element_id
FROM CS_KB_SET_ELES
WHERE set_id = p_set_id;
SELECT count(*)
FROM CS_KB_SET_ELES
WHERE Set_id = p_set_id
AND Element_id = p_ele_id;
FOR eles IN GET_ELES_TO_UPDATE(l_new_set_id) LOOP
is_dup := CS_KB_ELEMENTS_AUDIT_PKG.Is_Element_Created_Dup(eles.element_id);
UPDATE CS_KB_ELEMENTS_B
SET status = 'PUBLISHED'
WHERE element_id = eles.element_id;
UPDATE CS_KB_ELEMENTS_B
SET status = 'OBS'
WHERE element_id = eles.element_id;
UPDATE CS_KB_SET_ELES
SET Element_id = is_dup
WHERE set_id = l_new_set_id
AND element_id = eles.element_id;
DELETE FROM CS_KB_SET_ELES
WHERE set_id = l_new_set_id
AND element_id = eles.element_id;
SELECT COUNT(*) INTO l_count
FROM cs_lookups
WHERE lookup_type = 'CS_KB_INTERNAL_CODES'
AND lookup_code = upper(p_status);
select 'X' from cs_kb_set_types_b
where set_type_id = p_type_id
and trunc(sysdate) between trunc(nvl(start_date_active, sysdate))
and trunc(nvl(end_date_active, sysdate));
SELECT count(*)
FROM CS_KB_VISIBILITIES_B
WHERE Visibility_Id = p_visibility_id
AND sysdate BETWEEN nvl(Start_Date_Active, sysdate-1)
AND nvl(End_Date_Active, sysdate+1);
SELECT COUNT(*) INTO l_count
FROM CS_KB_SET_TYPES_B
WHERE set_type_id = p_set_type_id;
SELECT COUNT(*) INTO l_count
FROM CS_KB_SETS_VL
WHERE name = p_name
AND status = 'PUB';
SELECT CS_KB_SETS_S.NEXTVAL INTO x_set_id FROM DUAL;
SELECT TO_CHAR(CS_KB_SET_NUMBER_S.NEXTVAL) INTO x_set_number FROM DUAL;
SELECT COUNT(set_number) INTO l_count
FROM CS_KB_SETS_B
WHERE set_number = x_set_number;
SELECT TO_CHAR(CS_KB_SET_NUMBER_S.NEXTVAL) INTO x_set_number FROM DUAL;
CS_KB_SETS_PKG.Insert_Row( x_rowid => l_rowid,
x_set_id => x_set_id,
x_set_number => x_set_number,
x_set_type_id => p_set_type_id,
x_set_name => NULL,
x_group_flag => NULL,
x_status => l_status,
x_access_level => null,
x_name => p_name,
x_description => null,
x_creation_date => l_date,
x_created_by => l_created_by,
x_last_update_date => l_date,
x_last_updated_by => l_created_by,
x_last_update_login => l_login,
x_locked_by => l_created_by,
x_lock_date => NULL,
x_attribute_category => p_attribute_category,
x_attribute1 => p_attribute1,
x_attribute2 => p_attribute2,
x_attribute3 => p_attribute3,
x_attribute4 => p_attribute4,
x_attribute5 => p_attribute5,
x_attribute6 => p_attribute6,
x_attribute7 => p_attribute7,
x_attribute8 => p_attribute8,
x_attribute9 => p_attribute9,
x_attribute10 => p_attribute10,
x_attribute11 => p_attribute11,
x_attribute12 => p_attribute12,
x_attribute13 => p_attribute13,
x_attribute14 => p_attribute14,
x_attribute15 => p_attribute15,
x_employee_id => NULL,
x_party_id => NULL,
x_start_active_date => NULL,
x_end_active_date => NULL,
x_priority_code => 4,
x_visibility_id => p_visibility_id );
UPDATE CS_KB_SETS_B
SET LATEST_VERSION_FLAG = null --'N'
WHERE SET_NUMBER = x_set_number
AND SET_ID <> x_set_id;
UPDATE CS_KB_SETS_B
SET VIEWABLE_VERSION_FLAG = decode(status, 'PUB','Y',null) --'N')
WHERE SET_NUMBER = x_set_number;
UPDATE CS_KB_SETS_B s
SET s.VIEWABLE_VERSION_FLAG = 'Y'
WHERE s.SET_NUMBER = x_set_number
AND s.STATUS <> 'OBS'
AND s.latest_version_flag = 'Y'
AND NOT EXISTS (SELECT 'x'
FROM CS_KB_SETS_B s3
WHERE s3.set_number = s.set_number
AND s3.STATUS = 'PUB');
PROCEDURE Update_Solution(
P_SET_ID IN NUMBER,
P_SET_NUMBER IN VARCHAR2,
P_SET_TYPE_ID IN NUMBER,
P_NAME IN VARCHAR2,
P_STATUS IN VARCHAR2,
P_ATTRIBUTE_CATEGORY IN VARCHAR2,
P_ATTRIBUTE1 IN VARCHAR2,
P_ATTRIBUTE2 IN VARCHAR2,
P_ATTRIBUTE3 IN VARCHAR2,
P_ATTRIBUTE4 IN VARCHAR2,
P_ATTRIBUTE5 IN VARCHAR2,
P_ATTRIBUTE6 IN VARCHAR2,
P_ATTRIBUTE7 IN VARCHAR2,
P_ATTRIBUTE8 IN VARCHAR2,
P_ATTRIBUTE9 IN VARCHAR2,
p_attribute10 IN VARCHAR2,
P_ATTRIBUTE11 IN VARCHAR2,
P_ATTRIBUTE12 IN VARCHAR2,
P_ATTRIBUTE13 IN VARCHAR2,
P_ATTRIBUTE14 IN VARCHAR2,
P_ATTRIBUTE15 IN VARCHAR2,
X_RETURN_STATUS OUT NOCOPY VARCHAR2,
X_MSG_DATA OUT NOCOPY VARCHAR2,
X_MSG_COUNT OUT NOCOPY NUMBER,
P_VISIBILITY_ID IN NUMBER )
IS
l_ret NUMBER;
l_updated_by NUMBER;
select 'X' from cs_kb_set_types_b
where set_type_id = p_type_id
and trunc(sysdate) between trunc(nvl(start_date_active, sysdate))
and trunc(nvl(end_date_active, sysdate));
select 'x' from CS_KB_SETS_B
where set_id = p_set_id
and set_type_id = p_type_id;
SELECT count(*)
FROM CS_KB_VISIBILITIES_B
WHERE Visibility_Id = p_visibility_id
AND sysdate BETWEEN nvl(Start_Date_Active, sysdate-1)
AND nvl(End_Date_Active, sysdate+1);
SELECT COUNT(*) INTO l_count
FROM CS_KB_SET_TYPES_B
WHERE set_type_id = p_set_type_id;
SELECT COUNT(*) INTO l_count
FROM CS_KB_SETS_VL
WHERE name = p_name
AND status = 'PUB'
AND set_number <> p_set_number;
l_updated_by := FND_GLOBAL.user_id;
CS_KB_SETS_PKG.Update_Row(
x_set_id => p_set_id,
x_set_number => p_set_number,
x_set_type_id => p_set_type_id,
x_set_name => NULL,
x_group_flag => NULL,
x_status => p_status,
x_access_level => null,
x_name => p_name,
x_description => null,
x_last_update_date => l_date,
x_last_updated_by => l_updated_by,
x_last_update_login => l_login,
x_locked_by => l_locked_by,
x_lock_date => l_lock_date,
x_attribute_category => p_attribute_category,
x_attribute1 => p_attribute1,
x_attribute2 => p_attribute2,
x_attribute3 => p_attribute3,
x_attribute4 => p_attribute4,
x_attribute5 => p_attribute5,
x_attribute6 => p_attribute6,
x_attribute7 => p_attribute7,
x_attribute8 => p_attribute8,
x_attribute9 => p_attribute9,
x_attribute10 => p_attribute10,
x_attribute11 => p_attribute11,
x_attribute12 => p_attribute12,
x_attribute13 => p_attribute13,
x_attribute14 => p_attribute14,
x_attribute15 => p_attribute15,
x_employee_id => null,
x_party_id => null,
x_start_active_date => null,
x_end_active_date => null,
x_priority_code => 4,
x_visibility_id => p_visibility_id );
FND_MESSAGE.set_name('CS', 'CS_KB_C_UPDATE_ERR');
END Update_Solution;
SELECT se.element_id, e.element_number
FROM CS_KB_SET_ELES se,
CS_KB_ELEMENTS_B e
WHERE se.set_id = v_set_id
AND se.element_id = e.element_id
AND e.status <> 'PUBLISHED';
SELECT count(*)
FROM CS_KB_SET_ELES
WHERE set_id = v_set_id
AND element_id = v_dup_element_id;
l_delete_status NUMBER;
UPDATE CS_KB_SET_ELES
SET ELEMENT_ID = l_dup_element_id,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = FND_GLOBAL.user_id,
LAST_UPDATE_LOGIN = FND_GLOBAL.login_id
WHERE set_id = p_set_id
AND element_id = rec.element_id;
l_delete_status := CS_KB_ELEMENTS_AUDIT_PKG.Delete_Element(rec.element_number);
DELETE FROM CS_KB_SET_ELES
WHERE Set_Id = p_set_id
AND element_id = rec.element_id;
SELECT Set_Id
FROM CS_KB_SETS_B
WHERE Set_Number = (SELECT Set_Number
FROM CS_KB_SETS_B
WHERE Set_Id = P_SET_ID)
AND latest_version_flag = 'Y';
SELECT Set_Id
FROM CS_KB_SETS_B
WHERE Set_Number = P_SET_NUMBER
AND latest_version_flag = 'Y';
SELECT Status, locked_by, flow_details_id
FROM CS_KB_SETS_B
WHERE Set_Id = v_set_id;
SELECT Status, locked_by
FROM CS_KB_SETS_B
WHERE Set_Id = v_set_id;
SELECT count(1)
FROM CS_KB_SET_CATEGORIES
WHERE Set_Id = cp_set_id
AND Category_Id = P_DEST_CAT_ID;
SELECT count(1)
FROM CS_KB_SOLN_CATEGORIES_B
WHERE Category_Id = cp_cat_id;
delete from cs_kb_set_categories
where set_id = l_set_id and category_id = P_SRC_CAT_ID;
insert into cs_kb_set_categories
(
set_id,
category_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login
)
values
(
l_set_id,
P_DEST_CAT_ID,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id
);
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'csk.plsql.CS_KB_SECURITY_PVT.Move_Solutions.update_link',
'Invalid link (set_id,category_id): ('||l_set_id||','||l_cat_id||')');
SELECT set_number
INTO l_set_number
FROM cs_kb_sets_b
WHERE set_id = p_set_id;
SELECT
COUNT(*)
INTO
l_pub_count
FROM
cs_kb_sets_b
WHERE
status = 'PUB' AND
set_number = l_set_number;
SELECT status INTO l_current_status FROM cs_kb_sets_b WHERE set_id = p_set_id;
UPDATE CS_KB_SETS_B SET locked_by = - 1 WHERE set_id = p_set_id;
FND_ATTACHED_DOCUMENTS2_PKG.delete_attachments(
x_entity_name => 'CS_KB_SETS_B',
x_pk1_value => TO_CHAR(p_set_id),
x_delete_document_flag => 'Y'
) ;
DELETE FROM CS_KB_SET_CATEGORIES WHERE set_id = p_set_id;
DELETE FROM CS_KB_SET_PRODUCTS WHERE set_id = p_set_id;
DELETE FROM CS_KB_SET_PLATFORMS WHERE set_id = p_set_id;
DELETE FROM CS_KB_SET_LINKS WHERE set_id = p_set_id;
DELETE FROM CS_KB_SET_ELES WHERE set_id = p_set_id;
DELETE FROM CS_KB_SETS_TL WHERE set_id = p_set_id;
UPDATE
CS_KB_SETS_B
SET
latest_version_flag = 'Y'
WHERE
status = 'PUB' AND
set_number = l_set_number;
DELETE FROM CS_KB_SETS_B WHERE set_id = p_set_id;