[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
MAX(action)
INTO
returnValue
FROM
cs_kb_wf_flow_details
WHERE
flow_details_id = g_flow_details_id;
SELECT
MAX(MEANING)
INTO
returnValue
FROM
CS_LOOKUPS
WHERE
lookup_code = g_action
AND
lookup_type = 'CS_KB_INTERNAL_CODES';
SELECT
MAX(flow_id)
INTO
returnValue
FROM
cs_kb_wf_flow_details
WHERE
flow_details_id = g_flow_details_id;
SELECT
MAX(DECODE(B.STATUS,'NOT',LU2.MEANING,LU1.MEANING))
INTO
returnValue
FROM
cs_kb_sets_b B,
cs_kb_wf_flow_details D,
CS_LOOKUPS LU1,
CS_LOOKUPS LU2
WHERE
B.flow_details_id = D.flow_details_id (+)
AND
B.status = LU1.lookup_code
AND
D.STEP = LU2.lookup_code(+)
AND
LU1.lookup_type = 'CS_KB_INTERNAL_CODES'
AND
LU2.lookup_type(+) = 'CS_KB_STATUS'
AND
B.set_id = g_setId;
SELECT
MIN(GROUP_ID)
INTO
return_number
FROM
CS_KB_WF_FLOW_DETAILS
WHERE
FLOW_DETAILS_ID = g_flow_details_id;
SELECT category_id
FROM CS_KB_SET_CATEGORIES
WHERE set_id = c_set_id;
CURSOR prods IS SELECT
product_id, product_org_id
FROM
cs_kb_set_products
WHERE
set_id = c_set_id;
SELECT
min(fnd_user.user_id)
INTO
m_temp
FROM
fnd_user,
jtf_rs_resource_extns,
jtf_rs_group_members
WHERE
jtf_rs_group_members.resource_id=jtf_rs_resource_extns.resource_id
AND
fnd_user.user_id=m_user_id
AND
jtf_rs_group_members.group_id=m_group_id
AND jtf_rs_group_members.DELETE_FLAG <>'Y'
AND jtf_rs_resource_extns.START_DATE_ACTIVE <= sysdate
AND NVL(jtf_rs_resource_extns.END_DATE_ACTIVE, sysdate) >= sysdate
AND (
(jtf_rs_resource_extns.source_id = fnd_user.employee_id
AND
jtf_rs_resource_extns.category ='EMPLOYEE')
OR
(jtf_rs_resource_extns.source_id = fnd_user.customer_id
AND
jtf_rs_resource_extns.category ='PARTY'));
select step into x_step_code
from cs_kb_wf_flow_details
where flow_details_id = p_flow_details_id;
SELECT MAX(MEANING)
INTO x_step_meaning
FROM CS_LOOKUPS
WHERE lookup_code = x_step_code
AND lookup_type = 'CS_KB_STATUS';
UPDATE CS_KB_WF_FLOW_DETAILS
SET end_date = SYSDATE-1,
last_updated_by = uid,
last_update_date = dt
WHERE
flow_details_id = p_flow_details_id;
SELECT count(*)
FROM CS_KB_WF_FLOWS_B
WHERE flow_id = flowId;
SELECT count(*)
FROM fnd_profile_options o,
fnd_profile_option_values ov
WHERE o.profile_option_name = 'CS_KB_DEFAULT_FLOW'
AND o.profile_option_id = ov.profile_option_id
AND ov.PROFILE_OPTION_VALUE = to_char(flowId)
AND ov.application_id = 170;
UPDATE CS_KB_WF_FLOWS_B
SET end_date = dt,
last_updated_by = uid,
last_update_date = dt
WHERE
flow_id = p_flow_id;
SELECT count(*)
FROM CS_KB_WF_FLOWS_B
WHERE flow_id = flowId;
UPDATE CS_KB_WF_FLOWS_B
SET end_date = null,
last_updated_by = uid,
last_update_date = dt
WHERE flow_id = p_flow_id;
SELECT g.GROUP_ID,
g.GROUP_NAME
FROM JTF_RS_GROUPS_VL g,
JTF_RS_GROUP_USAGES u
WHERE g.GROUP_ID = u.GROUP_ID
AND u.USAGE = 'ISUPPORT'
AND NVL(g.END_DATE_ACTIVE, sysdate) >= sysdate
ORDER BY g.GROUP_NAME;
SELECT
FD.FLOW_DETAILS_ID,
FD.ORDER_NUM,
FD.STEP,
FD.GROUP_ID,
FD.ACTION
FROM
CS_KB_WF_FLOW_DETAILS FD
WHERE
FLOW_ID = p_flow_id
AND
(BEGIN_DATE <= SYSDATE OR BEGIN_DATE IS NULL)
AND
(END_DATE >= SYSDATE OR END_DATE IS NULL)
ORDER BY
ORDER_NUM ASC;
select b.flow_id, t.name
from cs_kb_wf_flows_b b,
cs_kb_wf_flows_tl t
where b.flow_id = t.flow_id
and t.language = userenv('LANG')
and exists (select flow_id from cs_kb_wf_flow_details d
where d.flow_id = b.flow_id
and sysdate between nvl(d.begin_date, sysdate-1)
and nvl(d.end_date, sysdate+1)
)
and sysdate < nvl(end_date, sysdate+1)
ORDER BY t.NAME ASC;
SELECT DISTINCT details2.GROUP_ID
FROM cs_kb_wf_flow_details details1,
cs_kb_wf_flow_details details2,
cs_kb_wf_flows_b flows,
cs_kb_sets_b sets
WHERE
sets.SET_NUMBER = p_set_number
AND sets.FLOW_DETAILS_ID = details1.FLOW_DETAILS_ID
AND details1.FLOW_ID = flows.FLOW_ID
AND flows.FLOW_ID = details2.FLOW_ID;
SQL1 VARCHAR2(1000) := 'SELECT DISTINCT CS_LOOKUPS.LOOKUP_CODE, CS_LOOKUPS.MEANING FROM CS_LOOKUPS ';
select order_num
into x_order_num
from CS_KB_WF_FLOW_DETAILS
where FLOW_DETAILS_ID = p_flow_details_id;
select MIN(order_num)
into x_next_order_num
from CS_KB_WF_FLOW_DETAILS
where flow_id = x_flow_id
and (BEGIN_DATE <= SYSDATE OR BEGIN_DATE IS NULL)
and (END_DATE >= SYSDATE OR END_DATE IS NULL)
and order_num > x_order_num;
select flow_details_id
into p_next_details_id
from CS_KB_WF_FLOW_DETAILS
where flow_id = x_flow_id
and (BEGIN_DATE <= SYSDATE OR BEGIN_DATE IS NULL)
and (END_DATE >= SYSDATE OR END_DATE IS NULL)
and order_num = x_next_order_num;
SELECT Flow_Details_Id
FROM CS_KB_WF_FLOW_DETAILS
WHERE Flow_id = P_FLOW_ID
AND sysdate between nvl(begin_date, sysdate-1)
and nvl(end_date, sysdate+1)
AND Order_Num = ( SELECT min(order_num)
FROM CS_KB_WF_FLOW_DETAILS
WHERE Flow_id = P_FLOW_ID
AND sysdate between nvl(begin_date, sysdate-1)
and nvl(end_date, sysdate+1) );
SELECT order_num
FROM CS_KB_WF_FLOW_DETAILS
WHERE FLOW_DETAILS_ID = P_CURRENT_FLOW_DETAILS_ID
AND (BEGIN_DATE <= SYSDATE OR BEGIN_DATE IS NULL)
AND (END_DATE >= SYSDATE OR END_DATE IS NULL);
select order_num
into x_order_num
from CS_KB_WF_FLOW_DETAILS
where FLOW_DETAILS_ID = p_flow_details_id;
select MAX(order_num)
into x_prev_order_num
from CS_KB_WF_FLOW_DETAILS
where flow_id = x_flow_id
and (BEGIN_DATE <= SYSDATE OR BEGIN_DATE IS NULL)
and (END_DATE >= SYSDATE OR END_DATE IS NULL)
and order_num < x_order_num;
select flow_details_id
into p_next_details_id
from CS_KB_WF_FLOW_DETAILS
where flow_id = x_flow_id
and order_num = x_prev_order_num;
/**************************** INSERT DETAIL **********************/
-- This procedure provides the ability to add a new flow
--
-- VARIABLES
-- p_flow_id
-- p_order_num
-- p_step
-- p_group_id
-- p_action
-- p_flow_details_id: flow_details_id or -1 if failed.
/*******************************************************************/
PROCEDURE Insert_Detail(
p_flow_id IN NUMBER,
p_order_num IN NUMBER,
p_step IN VARCHAR2,
p_group_id IN NUMBER,
p_action IN VARCHAR2,
p_flow_details_id OUT NOCOPY NUMBER
)
IS
--temp vars
uid NUMBER := fnd_global.user_id;
SELECT
cs_kb_wf_flow_details_s.NextVal
INTO
p_flow_details_id
FROM
DUAL;
INSERT INTO CS_KB_WF_FLOW_DETAILS(flow_details_id,
flow_id,
step,
order_num,
action,
group_id,
created_by,
creation_date,
last_updated_by,
last_update_date)
VALUES(p_flow_details_id,
p_flow_id,
p_step,
p_order_num,
p_action,
p_group_id,
uid,
dt,
uid,
dt);
END Insert_Detail;
/**************************** INSERT FLOW *************************/
-- This procedure provides the ability to add a new flow
--
-- VARIABLES
-- p_flow_name (name to display)
-- p_flow_id (the new id, -1 if already taken or other error)
-- bug 1966494: -3 for duplicated flow name
/*******************************************************************/
PROCEDURE Insert_Flow(
p_flow_name IN VARCHAR2,
p_flow_id OUT NOCOPY NUMBER
) IS
--temp vars
uid NUMBER := fnd_global.user_id;
SELECT count(1)
INTO x_count
FROM CS_KB_WF_FLOWS_B b, CS_KB_WF_FLOWS_TL tl
WHERE b.FLOW_ID = tl.FLOW_ID
AND tl.NAME = p_flow_name
AND tl.LANGUAGE = FND_GLOBAL.CURRENT_LANGUAGE;
SELECT
cs_kb_wf_flows_s.NextVal
INTO
p_flow_id
FROM
DUAL;
INSERT INTO CS_KB_WF_FLOWS_B(flow_id,created_by,creation_date,
last_updated_by,last_update_date)
VALUES(p_flow_id,uid,dt,uid,dt);
INSERT INTO CS_KB_WF_FLOWS_TL (flow_id,
name,
creation_date,
created_by,
last_update_date,
last_update_login,
last_updated_by,
language,
source_lang)
SELECT p_flow_id,
p_flow_name,
dt,
uid,
dt,
uid,
uid,
l.language_code,
USERENV('LANG')
FROM fnd_languages l
WHERE l.installed_flag IN ('I', 'B')
AND NOT EXISTS
(SELECT NULL
FROM CS_KB_WF_FLOWS_TL t
WHERE t.flow_id = p_flow_id
AND t.language = l.language_code);
END Insert_Flow;
-- set locked by last updater
-- 7117561
SELECT created_by
INTO x_original_author_id
FROM cs_kb_sets_b
WHERE set_id = ( SELECT MAX(set_id) -- Bug fix: 7159784 - made it max(set_id) to get the last updater
FROM cs_kb_sets_b
WHERE set_number = p_set_number
AND status = 'SAV' --Bugfix7228667 - Added the Status to change the Locked by
) ;
/*select user_name
into x_author
from fnd_user
where user_id = x_original_author_id;*/
SELECT DISTINCT user_id, user_name
FROM
(
SELECT user_id, user_name
FROM cs_kb_sets_b a, fnd_user b
WHERE set_id IN (
SELECT MIN(set_id)
FROM cs_kb_sets_b
WHERE set_number = p_set_number
)
AND a.created_by = b.user_id
UNION
SELECT user_id, user_name
FROM cs_kb_sets_b a, fnd_user b
WHERE set_id IN (
SELECT MAX(set_id) --Bug fix:7159784 - made it max(set_id) to get the last updater
FROM cs_kb_sets_b
WHERE set_number = p_set_number AND
status = 'SAV' --Bug fix:7228667
)
AND a.created_by = b.user_id
)
)
LOOP
IF (CS_KB_SECURITY_PVT.IS_COMPLETE_SOLUTION_VISIBLE(x_original_author_id,set_id)= 'TRUE') THEN
Create_Reject_Process(set_id,p_set_number, solution_title, get_user.user_name);
SELECT GROUP_NAME
FROM JTF_RS_GROUPS_VL
WHERE GROUP_ID = v_group_id;
SELECT DISTINCT
fnd_user.user_name,
fnd_user.user_id
FROM fnd_user,
jtf_rs_resource_extns,
jtf_rs_group_members
WHERE jtf_rs_group_members.resource_id=jtf_rs_resource_extns.resource_id
AND jtf_rs_group_members.group_id = v_group_id
AND jtf_rs_group_members.DELETE_FLAG <> 'Y'
AND jtf_rs_resource_extns.START_DATE_ACTIVE <= sysdate
AND NVL(jtf_rs_resource_extns.END_DATE_ACTIVE, sysdate) >= sysdate
AND( ( jtf_rs_resource_extns.source_id = fnd_user.employee_id
AND jtf_rs_resource_extns.category = 'EMPLOYEE' )
OR (jtf_rs_resource_extns.source_id = fnd_user.customer_id
AND jtf_rs_resource_extns.category = 'PARTY' )
);
/**************************** UPDATE DETAIL *************************/
-- This procedure provides the ability to add a new flow
--
-- VARIABLES
-- p_flow_details_id - provided when a list of details is requested
-- p_order_num
-- p_step
-- p_group_id
-- p_action
-- p_result: p_flow_details_id = completed successfully, 0 = completed but nothing was
-- updated, -1 = error
/*******************************************************************/
PROCEDURE Update_Detail(
p_flow_details_id IN NUMBER,
p_order_num IN NUMBER,
p_step IN VARCHAR2,
p_group_id IN NUMBER,
p_action IN VARCHAR2,
p_result OUT NOCOPY NUMBER
)
IS
uid NUMBER := fnd_global.user_id;
SELECT
MIN(flow_details_id)
INTO
temp
FROM
cs_kb_wf_flow_details
WHERE
flow_details_id = p_flow_details_id;
UPDATE CS_KB_WF_FLOW_DETAILS
SET order_num = p_order_num,
step = p_step,
group_id = p_group_id,
action = p_action,
last_updated_by = uid,
last_update_date = dt
WHERE
flow_details_id = p_flow_details_id;
END Update_Detail;
/**************************** UPDATE DETAIL ADMIN ******************/
-- This procedure is a wrapper for both update and insert procedures
-- to be used by the admin pages.
--
-- VARIABLES
-- p_flow_details_id - provided when a list of details is requested
-- p_order_num
-- p_step
-- p_group_id
-- p_action
-- p_result: flow_details_id OR -1 = error
/*******************************************************************/
PROCEDURE Update_Detail_Admin(
p_flow_details_id IN NUMBER,
p_flow_id IN NUMBER,
p_order_num IN NUMBER,
p_step IN VARCHAR2,
p_group_id IN NUMBER,
p_action IN VARCHAR2,
p_flag IN VARCHAR2,
p_result OUT NOCOPY NUMBER
) IS
result NUMBER;
IF (p_flag = 'DELETE') THEN
Expire_Detail(p_flow_details_id, result);
Insert_Detail(p_flow_id, p_order_num, p_step, p_group_id, p_action, result);
Update_Detail(p_flow_details_id, p_order_num, p_step, p_group_id, p_action, result);
END Update_Detail_Admin;
/**************************** UPDATE FLOW *************************/
-- This procedure provides the ability to add a new flow
--
-- VARIABLES
-- p_flow_id
-- p_flow_name (name to display)
-- p_result: 1 = completed successfully, 0 = completed but nothing was
-- updated, -1 = error, -3=duplicated file name
/*******************************************************************/
PROCEDURE Update_Flow(
p_flow_id IN NUMBER,
p_flow_name IN VARCHAR2,
p_result OUT NOCOPY NUMBER
) IS
uid NUMBER := fnd_global.user_id;
SELECT count(1)
INTO x_count
FROM CS_KB_WF_FLOWS_B b, CS_KB_WF_FLOWS_TL tl
WHERE b.FLOW_ID = tl.FLOW_ID
AND b.FLOW_ID <> p_flow_id
AND tl.NAME = p_flow_name
AND tl.LANGUAGE = FND_GLOBAL.CURRENT_LANGUAGE;
SELECT
MIN(flow_id)
INTO
temp
FROM
cs_kb_wf_flows_tl
WHERE
flow_id = p_flow_id;
UPDATE CS_KB_WF_FLOWS_TL
SET name = p_flow_name,
last_updated_by = uid,
last_update_date = dt,
source_lang = USERENV('LANG')
WHERE
flow_id = p_flow_id
AND USERENV('LANG') IN (language, source_lang);
UPDATE CS_KB_WF_FLOWS_B
SET last_updated_by = uid,
last_update_date = dt
WHERE
flow_id = p_flow_id;
END Update_Flow;
/**************************** UPDATE FLOW ADMIN ********************/
-- This procedure is a wrapper of both insert and update functionality
-- designed for the admin pages
--
-- VARIABLES
-- p_flow_id
-- p_flow_name (name to display)
-- p_result: flow_id OR -1 = error, OR -3 = duplicated flow name
/*******************************************************************/
PROCEDURE Update_Flow_Admin(
p_flow_id IN NUMBER,
p_flow_name IN VARCHAR2,
p_result OUT NOCOPY NUMBER
)
IS
result NUMBER;
Insert_Flow(p_flow_name, result);
Update_Flow(p_flow_id, p_flow_name, result);
END Update_Flow_admin;
DELETE FROM CS_KB_WF_FLOWS_TL t
WHERE NOT EXISTS
(SELECT NULL
FROM CS_KB_WF_FLOWS_B b
WHERE b.flow_id = t.flow_id
);
UPDATE CS_KB_WF_FLOWS_TL T SET (
name,
description
) = (SELECT
b.name,
b.description
FROM CS_KB_WF_FLOWS_TL b
WHERE b.flow_id = t.flow_id
AND b.language = t.source_lang)
WHERE (
t.flow_id,
t.language
) IN (SELECT
subt.flow_id,
subt.language
FROM CS_KB_WF_FLOWS_TL subb, CS_KB_WF_FLOWS_TL subt
WHERE subb.flow_id = subt.flow_id
AND subb.language = subt.source_lang
AND (subb.name <> subt.name
OR (subb.name IS NULL AND subt.name IS NOT NULL)
OR (subb.name IS not NULL AND subt.name IS NULL)
OR subb.description <> subt.description
OR (subb.description IS NULL AND subt.description IS NOT NULL)
OR (subb.description IS NOT NULL AND subt.description IS NULL)
));
INSERT INTO CS_KB_WF_FLOWS_TL (
flow_id,
name,
description,
creation_date,
created_by,
last_update_date,
last_update_login,
last_updated_by,
language,
source_lang
) SELECT
b.flow_id,
b.name,
b.description,
b.creation_date,
b.created_by,
b.last_update_date,
b.last_update_login,
b.last_updated_by,
l.language_code,
b.source_lang
FROM CS_KB_WF_FLOWS_TL b, fnd_languages l
WHERE l.installed_flag IN ('I', 'B')
AND b.language = USERENV('LANG')
AND NOT EXISTS
(SELECT NULL
FROM CS_KB_WF_FLOWS_TL t
WHERE t.flow_id = b.flow_id
AND t.language = l.language_code);