[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_sql := 'SELECT attribute_name, session_value, session_description'||
' FROM bis_user_attributes'||
' WHERE user_id = :1 AND page_id = :2 AND dimension = :3';
l_sql := 'SELECT attribute_name, session_value, session_description'||
' FROM bis_user_attributes'||
' WHERE user_id = :1 AND page_id = :2 AND attribute_name = :3';
SELECT
count(*)
INTO
l_count
FROM
FND_USER_RESP_GROUPS fnd,
FND_RESPONSIBILITY fr
WHERE
fnd.USER_ID = p_user_id AND
fnd.RESPONSIBILITY_ID = p_resp_id AND
fnd.RESPONSIBILITY_ID = fr.RESPONSIBILITY_ID AND
SYSDATE BETWEEN fr.START_DATE AND NVL(fr.END_DATE, SYSDATE) AND
SYSDATE BETWEEN fnd.START_DATE AND NVL(fnd.END_DATE, SYSDATE);
SELECT
count(*)
INTO
l_count
FROM
BSC_USER_RESPONSIBILITY_V
WHERE
user_id = p_user_id AND
responsibility_id = p_resp_id;
SELECT bsi.file_body
INTO doc
FROM bsc_sys_images bsi, bsc_sys_images_map_vl bsim, bsc_user_tab_access bta
WHERE bsim.image_id = bsi.image_id AND bsim.source_type = 1
AND bsim.source_code = p_tab_code AND bsim.type = p_tab_view
AND bsim.source_code = bta.tab_id AND bta.responsibility_id = p_resp_id;
l_sql := 'SELECT plug_id FROM bsc_user_kpigraph_plugs'||
' WHERE reference_path = :1';
SELECT ICX_PAGE_PLUGS_S.NEXTVAL
INTO l_plug_id
FROM sys.dual;
SELECT
COUNT(*)
INTO
l_count
FROM
bsc_user_kpigraph_plugs
WHERE
-- user_id = p_user_id AND -- BUG 4136961, user level customization is not supported.
reference_path = p_reference_path;
UPDATE
bsc_user_kpigraph_plugs
SET
responsibility_id = p_resp_id,
parameter_string = l_parameters,
last_update_date = SYSDATE,
last_updated_by = p_user_id
WHERE
-- user_id = p_user_id AND -- BUG 4136961, user level customization is not supported.
reference_path = p_reference_path;
INSERT INTO bsc_user_kpigraph_plugs (
user_id,
plug_id,
reference_path,
responsibility_id,
indicator,
parameter_string,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login
) VALUES (
-1, -- BUG 4136961, user level customization is not supported.
l_plug_id,
p_reference_path,
p_resp_id,
0,
l_parameters,
SYSDATE,
p_user_id,
SYSDATE,
p_user_id,
p_user_id
);
UPDATE icx_portlet_customizations
SET title = p_portlet_name, caching_key = to_char(to_number(caching_key)+1)
WHERE reference_path = p_reference_path;
l_sql := 'SELECT plug_id FROM bsc_user_kpigraph_plugs'||
' WHERE reference_path = :1';
SELECT ICX_PAGE_PLUGS_S.NEXTVAL
INTO l_plug_id
FROM sys.dual;
SELECT
COUNT(*)
INTO
l_count
FROM
bsc_user_kpigraph_plugs
WHERE
-- user_id = p_user_id AND -- BUG 4136961, user level customization is not supported.
reference_path = p_reference_path;
UPDATE
bsc_user_kpigraph_plugs
SET
responsibility_id = p_resp_id,
indicator = p_kpi_code,
parameter_string = l_parameters,
last_update_date = SYSDATE,
last_updated_by = p_user_id
WHERE
-- user_id = p_user_id AND -- BUG 4136961, user level customization is not supported.
reference_path = p_reference_path;
INSERT INTO bsc_user_kpigraph_plugs (
user_id,
plug_id,
reference_path,
responsibility_id,
indicator,
parameter_string,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login
) VALUES (
-1, -- BUG 4136961, user level customization is not supported.
l_plug_id,
p_reference_path,
p_resp_id,
p_kpi_code,
l_parameters,
SYSDATE,
p_user_id,
SYSDATE,
p_user_id,
p_user_id
);
UPDATE icx_portlet_customizations
SET plug_id = l_plug_id
WHERE reference_path = p_reference_path;
UPDATE icx_portlet_customizations
SET title = p_portlet_name, caching_key = to_char(to_number(caching_key)+1)
WHERE reference_path = p_reference_path;
p_last_updated_by IN NUMBER,
p_porlet_name IN VARCHAR2,
p_number_array IN BSC_NUM_LIST,
p_o_ret_status OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) IS
l_count NUMBER;
l_sql := 'SELECT plug_id from bsc_user_kpilist_plugs'||
' WHERE reference_path = :1';
SELECT ICX_PAGE_PLUGS_S.NEXTVAL
INTO l_plug_id
FROM sys.dual;
p_details_flag , p_group_flag , p_kpi_measure_details_flag, p_createy_by , p_last_updated_by ,
p_porlet_name , p_number_array , p_o_ret_status );
PROCEDURE checkUpdateCustView(
p_commit IN VARCHAR2,
p_user_id IN VARCHAR2,
p_reference_path IN VARCHAR2,
p_tab_id IN VARCHAR2,
p_view_id IN VARCHAR2,
p_resp_id IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2
) IS
last_update_date_from_designer DATE := null;
last_update_date_from_portlet DATE := null;
SELECT
last_update_date
INTO
last_update_date_from_designer
FROM
BSC_TAB_VIEWS_B
WHERE
tab_id = p_tab_id AND
tab_view_id = p_view_id;
SELECT
last_update_date
INTO
last_update_date_from_portlet
FROM
bsc_user_kpigraph_plugs
WHERE
user_id = p_user_id AND
reference_path = p_reference_path ;
IF(last_update_date_from_designer IS NOT NULL AND last_update_date_from_portlet IS NOT NULL AND p_resp_id IS NOT NULL) THEN
IF(last_update_date_from_designer > last_update_date_from_portlet) THEN
l_parameters := 'pTabId='||p_tab_id||'&'||'pViewId='||p_view_id;
UPDATE
bsc_user_kpigraph_plugs
SET
responsibility_id = p_resp_id,
parameter_string = l_parameters,
last_update_date = SYSDATE,
last_updated_by = p_user_id
WHERE
user_id = p_user_id AND
reference_path = p_reference_path;
UPDATE icx_portlet_customizations
SET caching_key = to_char(to_number(NVL(caching_key, 0))+1)
WHERE reference_path = p_reference_path;
END checkUpdateCustView;