[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT hsecs FROM v$timer;
INSERT INTO csr_timers_b (seq, NAME, VALUE, meaning, description)
VALUES (p_seq, p_name, (l_hsecs - g_hsecs_old) * 10, 'TIME', p_descr);
* Checks whether the given Territory ID is one among the Selected Territories
* of the given user. If no user is given, then it checks using the signed
* in user (FND_GLOBAL.USER_ID)
*
* @param p_terr_id Territory ID of the Territory to be checked
* @param p_user_id User ID of the user whose list of Territories is used
*/
FUNCTION is_terr_selected( p_terr_id IN NUMBER
, p_user_id IN NUMBER DEFAULT NULL ) RETURN NUMBER
IS
l_selected_terr VARCHAR2(4000) := NULL;
l_selected_terr := get_selected_terr(p_user_id);
IF l_selected_terr IS NULL THEN
l_return_value := -1;
l_return_value := INSTR(',' || l_selected_terr || ',', ',' || TO_CHAR(p_terr_id) || ',');
END is_terr_selected;
FUNCTION get_selected_terr( p_user_id NUMBER DEFAULT NULL ) RETURN VARCHAR2
IS
l_selected_terr VARCHAR2(4000);
l_terr_table := get_selected_terr_table(p_user_id);
IF LENGTH(l_selected_terr || ',' || l_terr_table(i)) > 4000 THEN
RETURN SUBSTR(l_selected_terr,2);
l_selected_terr := l_selected_terr || ',' || l_terr_table(i);
RETURN SUBSTR(l_selected_terr, 2);
END get_selected_terr;
* Gets the List of Territories selected as a PLSQL Table
*
* @param p_user_id User ID of the user to get the list of User's Territories
*/
FUNCTION get_selected_terr_table( p_user_id NUMBER DEFAULT NULL )
RETURN jtf_number_table
IS
l_selected_terr VARCHAR2(4000);
CURSOR c_selected_terr( b_user_id NUMBER ) IS
SELECT terr_id
from csf_user_selected_terrs
WHERE user_id = b_user_id;
SELECT DISTINCT pt.terr_id
FROM csf_plan_terrs pt
, jtf_rs_group_members m
WHERE m.resource_id = b_resource_id
AND NVL(m.delete_flag, 'N') <> 'Y'
AND pt.group_id = m.group_id;
SELECT DISTINCT tq.terr_id
FROM jtf_qual_type_usgs q
, jtf_terr_qtype_usgs_all tq
WHERE q.source_id = -1002
AND tq.qual_type_usg_id = q.qual_type_usg_id;
OPEN c_selected_terr( l_user_id );
FETCH c_selected_terr INTO l_selected_terr;
CLOSE c_selected_terr;
IF l_selected_terr IS NOT NULL THEN
LOOP
i := INSTR(l_selected_terr, ',');
l_terr_table(l_terr_table.LAST) := SUBSTR(l_selected_terr, 1, i-1);
l_selected_terr := SUBSTR(l_selected_terr, i+1);
IF l_selected_terr IS NOT NULL AND LENGTH(TRIM(l_selected_terr)) > 0 THEN
l_terr_table.extend(1);
l_terr_table(l_terr_table.LAST) := l_selected_terr;
END get_selected_terr_table;
* Saves the list of selected territories to the database
*
* @param p_selected_terr List of Territories to be stored
* @param p_user_id User ID of the user for whom we are storing the list
*/
PROCEDURE set_selected_terr( p_selected_terr IN VARCHAR2 DEFAULT NULL
, p_user_id IN NUMBER DEFAULT NULL )
IS
l_user_id NUMBER DEFAULT NULL;
IF p_selected_terr IS NOT NULL THEN
UPDATE csf_user_selected_terrs
SET terr_id = p_selected_terr
, last_updated_by = fnd_global.user_id
, last_update_date = SYSDATE
, last_update_login = fnd_global.login_id
, object_version_number = object_version_number + 1
WHERE user_id = l_user_id;
INSERT INTO csf_user_selected_terrs (
user_id
, terr_id
, last_updated_by
, last_update_date
, last_update_login
, created_by
, creation_date
, object_version_number
) VALUES (
l_user_id
, p_selected_terr
, fnd_global.user_id
, SYSDATE
, fnd_global.login_id
, fnd_global.user_id
, SYSDATE
, 1);
END set_selected_terr;
SELECT select_id
, select_name
, from_table
, where_clause
FROM jtf_objects_vl
WHERE object_code = p_object_type_code;
l_stmt := 'SELECT ' || l_rec.select_name || ' FROM ' || l_rec.from_table || ' WHERE ';
l_stmt := l_stmt || l_rec.select_id || ' = :object_id';
SELECT unit_of_measure_tl
FROM mtl_units_of_measure_vl
WHERE uom_code = p_code;
SELECT 'TRUE'
FROM mtl_units_of_measure_vl
WHERE uom_code = p_value AND(TRUNC(disable_date) >= TRUNC(SYSDATE) OR disable_date IS NULL);
SELECT q.where_clause
, NVL(qe.owner_enabled_flag, 'N') owner_enabled_flag
, ( SELECT COUNT(*)
FROM csf_plan_task_owners
WHERE user_id = fnd_global.user_id
) owners_count
FROM csf_dc_queries_b q
, csf_dc_query_extns qe
WHERE q.query_id = p_query_id
AND qe.user_id(+) = fnd_global.user_id
AND qe.query_id(+) = q.query_id;
IN (SELECT owner_id, owner_type_code
FROM csf_plan_task_owners
WHERE user_id=fnd_global.user_id) ';