[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
p_delete IN VARCHAR2 DEFAULT 'N') IS
l_user_id pls_integer;
SELECT k.RESPONSIBILITY_ID,
bt.TAB_ID,
k.INDICATOR
FROM BSC_USER_KPIGRAPH_PLUGS k,
BSC_TAB_INDICATORS bt
WHERE k.USER_ID = l_user_id
AND k.PLUG_ID = p_plug_id
AND k.INDICATOR = bt.INDICATOR;
SELECT k.INDICATOR
FROM BSC_USER_KPIGRAPH_PLUGS k
WHERE k.USER_ID = l_user_id
AND k.PLUG_ID = p_plug_id;
SELECT bt.TAB_ID
FROM BSC_USER_KPIGRAPH_PLUGS k,
BSC_TAB_INDICATORS bt
WHERE k.USER_ID = l_user_id
AND k.PLUG_ID = p_plug_id
AND k.INDICATOR = bt.INDICATOR;
IF p_delete = 'Y' THEN
--------------------------------------------
-- should clean up bsc_user_kpigraph_plugs
--------------------------------------------
DELETE FROM bsc_user_kpigraph_plugs
WHERE user_id = l_user_id
AND plug_id = p_plug_id;
ELSE -- p_delete = 'N'
--------------------------------------------
-- find the record in BSC_USER_KPIGRAPH_PLUGS
--------------------------------------------
OPEN c_kg_p;
-- no kpi selected, should be empty graph
--------------------------------------------
l_ctm_url:= bsc_portlet_graph.get_customized_kpigraph_url(
p_session_id, p_plug_id, l_tab_id, l_kpi_id,
TRUE, bsc_portlet_util.VALUE_NOT_SET, p_display_name);
END IF; -- p_delete = 'Y'
SELECT bk.INDICATOR
FROM
fnd_user_resp_groups fg,
fnd_responsibility fr,
bsc_user_responsibility_v br,
bsc_user_kpigraph_plugs bp,
bsc_tab_indicators bti,
bsc_user_tab_access bta,
bsc_user_kpi_access bk
WHERE
bp.PLUG_ID = p_plug_id AND
bp.USER_ID = br.USER_ID AND
fg.USER_ID = bp.USER_ID AND
fg.RESPONSIBILITY_ID = bp.RESPONSIBILITY_ID AND
sysdate BETWEEN nvl(fg.START_DATE, sysdate) AND
nvl(fg.END_DATE, sysdate) AND
fr.RESPONSIBILITY_ID = fg.RESPONSIBILITY_ID AND
sysdate BETWEEN nvl(fr.START_DATE, sysdate) AND
nvl(fr.END_DATE, sysdate) AND
bp.RESPONSIBILITY_ID = br.RESPONSIBILITY_ID AND
bp.RESPONSIBILITY_ID = bta.RESPONSIBILITY_ID AND
bp.INDICATOR = bti.INDICATOR AND
bti.TAB_ID = bta.TAB_ID AND
SYSDATE BETWEEN bta.START_DATE AND
NVL(bta.END_DATE, SYSDATE) AND
bp.RESPONSIBILITY_ID = bk.RESPONSIBILITY_ID AND
bp.INDICATOR = bk.INDICATOR AND
SYSDATE BETWEEN NVL(bk.START_DATE(+), SYSDATE) AND
NVL(bk.END_DATE, SYSDATE);
SELECT display_name into l_display_name
FROM icx_page_plugs
WHERE plug_id = p_plug_id;
SELECT k.RESPONSIBILITY_ID,
bt.TAB_ID,
k.INDICATOR
FROM BSC_USER_KPIGRAPH_PLUGS k,
BSC_TAB_INDICATORS bt
WHERE k.USER_ID = l_user_id
AND k.PLUG_ID = p_plug_id
AND k.INDICATOR = bt.INDICATOR;
p_last_updated_by IN NUMBER,
p_porlet_name IN VARCHAR2,
p_o_ret_status OUT NOCOPY NUMBER) RETURN VARCHAR2 IS
insert_err EXCEPTION;
update_err EXCEPTION;
SELECT count(*)
INTO l_count
FROM BSC_USER_KPIGRAPH_PLUGS k
WHERE
k.USER_ID = p_user_id AND
k.PLUG_ID = p_plug_id;
IF (l_count > 0) THEN -- record exists, need to update
UPDATE
BSC_USER_KPIGRAPH_PLUGS
SET
RESPONSIBILITY_ID = p_resp_id,
INDICATOR = p_kpi_id,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = p_last_updated_by
WHERE
USER_ID = p_user_id AND
PLUG_ID = p_plug_id;
RAISE update_err;
ELSE -- record does not exist, insert it
INSERT INTO BSC_USER_KPIGRAPH_PLUGS (
USER_ID, PLUG_ID, RESPONSIBILITY_ID, INDICATOR,
CREATION_DATE, CREATED_BY,
LAST_UPDATE_DATE, LAST_UPDATED_BY,
LAST_UPDATE_LOGIN)
VALUES (
p_user_id, p_plug_id, p_resp_id, p_kpi_id,
SYSDATE, p_createy_by,
SYSDATE, p_last_updated_by,
p_last_updated_by);
RAISE insert_err;
bsc_portlet_util.update_portlet_name(p_user_id, p_plug_id, p_porlet_name);
WHEN insert_err THEN
ROLLBACK;
l_errmesg := 'Error inserting to BSC_USER_KPIGRAPH_PLUGS';
WHEN update_err THEN
ROLLBACK;
SELECT k.RESPONSIBILITY_ID, k.INDICATOR, p.DISPLAY_NAME
FROM bsc_user_kpigraph_plugs k,
icx_page_plugs p
WHERE
p.PLUG_ID = l_plug_id AND
k.PLUG_ID(+)= p.PLUG_ID AND
k.USER_ID(+) = l_user_id;
SELECT bt.TAB_ID
FROM bsc_tab_indicators bt
WHERE
bt.INDICATOR = p_kpi_id;
SELECT PROMPT
FROM fnd_menu_entries_vl fme,
icx_page_plugs ipp
WHERE
ipp.PLUG_ID = l_plug_id AND
fme.menu_id = ipp.menu_id and
fme.ENTRY_SEQUENCE = ipp.ENTRY_SEQUENCE;
insert_err EXCEPTION;
update_err EXCEPTION;
SELECT bsi.FILE_BODY
FROM bsc_kpi_graphs k,
bsc_sys_images bsi
WHERE
k.RESPONSIBILITY_ID = p_resp_id AND
k.INDICATOR = p_kpi_id AND
k.GRAPH_KEY = p_graph_key AND
k.IMAGE_ID = bsi.IMAGE_ID;
insert_err EXCEPTION;
update_err EXCEPTION;
SELECT k.IMAGE_ID, k.GRAPH_KEY
FROM bsc_kpi_graphs k,
bsc_sys_images bsi
WHERE
k.RESPONSIBILITY_ID = p_resp_id AND
k.INDICATOR = p_kpi_id AND
k.IMAGE_ID = bsi.IMAGE_ID;
UPDATE bsc_kpi_graphs
SET
GRAPH_KEY = p_graph_key,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = p_user_id
WHERE
RESPONSIBILITY_ID = p_resp_id AND
INDICATOR = p_kpi_id;
RAISE update_err;
UPDATE bsc_sys_images
SET
FILE_NAME = p_graph_key,
DESCRIPTION = p_graph_key,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = p_user_id
WHERE
IMAGE_ID = p_img_id;
RAISE update_err;
SELECT bsc_sys_image_id_s.NEXTVAL
INTO l_sq_img_id
FROM dual;
INSERT INTO bsc_sys_images(
IMAGE_ID, FILE_NAME, DESCRIPTION, FILE_BODY, WIDTH, HEIGHT,
CREATION_DATE, CREATED_BY,
LAST_UPDATE_DATE, LAST_UPDATED_BY,
LAST_UPDATE_LOGIN)
VALUES (
bsc_sys_image_id_s.NEXTVAL,
p_graph_key, p_graph_key, empty_blob(), l_w, l_h,
SYSDATE, p_user_id,
SYSDATE, p_user_id,
p_user_id)
RETURNING IMAGE_ID INTO p_img_id;
l_errmesg := 'Error inserting into bsc_sys_images. FILE_NAME=' ||
p_graph_key;
RAISE insert_err;
INSERT INTO bsc_kpi_graphs(
RESPONSIBILITY_ID, INDICATOR, GRAPH_KEY, IMAGE_ID,
CREATION_DATE, CREATED_BY,
LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN)
VALUES(
p_resp_id, p_kpi_id, p_graph_key, p_img_id,
SYSDATE, p_user_id,
SYSDATE, p_user_id, p_user_id);
l_errmesg := 'Error inserting into bsc_kpi_graphs. RESPONSIBILITY_ID='|| p_resp_id || ', INDICATOR=' || p_kpi_id || ', GRAPH_KEY=' ||
p_graph_key || ', IMAGE_ID' || p_img_id;
RAISE insert_err;
WHEN insert_err THEN
ROLLBACK;
WHEN update_err THEN
ROLLBACK;