[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
master tables to be updated
either through concurrent job or workflow activities.
*/
procedure initialize_g_table_desc_tbl
IS
i NUMBER := 0;
'select cit.sub_type_id
from
csi_ib_txn_types cit,
csi_source_ib_types cst,
csi_txn_types ctt,
csi_instance_statuses cis
where cst.sub_type_id = cit.sub_type_id
and cst.transaction_type_id = ctt.transaction_type_id
and cit.src_return_reqd = ''N''
and cit.non_src_reference_reqd = ''N''
and ctt.source_transaction_type = ''FIELD_SERVICE_REPORT''
and ctt.source_application_id = 513
and cis.instance_status_id(+) = cit.src_status_id
and cis.terminated_flag(+) <> ''Y''';
'select jom.mapping_id
from jtf_object_mappings jom
WHERE jom.source_object_code IN (''PARTY'', ''TASK'', ''SR'', ''CP'',''OKS_COV_NOTE'',''SD'')
AND NVL(jom.end_date, SYSDATE) >= SYSDATE';
SELECT usr.USER_ID
FROM asg_user_pub_resps pubresp
, asg_user usr
WHERE usr.enabled = 'Y'
AND pubresp.user_name = usr.user_name
AND pubresp.pub_name ='SERVICEP';
SELECT usr.USER_ID
FROM asg_user_pub_resps pubresp
, asg_user usr
WHERE usr.enabled = 'Y'
AND pubresp.user_name = usr.user_name
AND pubresp.pub_name ='SERVICEP'
AND usr.USER_ID = usr.OWNER_ID;
SELECT usr.resource_id
FROM asg_user_pub_resps pubresp
, asg_user usr
WHERE usr.enabled = 'Y'
AND pubresp.user_name = usr.user_name
AND pubresp.pub_name = 'SERVICEP';
SELECT usr.resource_id
FROM asg_user_pub_resps pubresp
, asg_user usr
WHERE usr.enabled = 'Y'
AND pubresp.user_name = usr.user_name
AND pubresp.pub_name = 'SERVICEP'
AND usr.USER_ID = usr.OWNER_ID;
select user_id
from asg_user
where user_name = p_user_name;
select user_id
from asg_user
where user_name = p_user_name;
SELECT RESPONSIBILITY_ID
FROM FND_USER_RESP_GROUPS
WHERE NVL( START_DATE , SYSDATE) <= SYSDATE
AND NVL( END_DATE , SYSDATE) >= SYSDATE
AND USER_ID = p_user_id;
SELECT RULE_ID
FROM JTF_STATE_RESPONSIBILITIES
WHERE RESPONSIBILITY_ID = p_resp_id ;
select resource_id
from jtf_rs_resource_extns
where user_id = p_user_id
AND SYSDATE BETWEEN NVL(start_date_active, SYSDATE)
AND NVL(end_date_active, SYSDATE)
;
select resource_id
from jtf_rs_resource_extns
where user_id = p_user_id
AND SYSDATE BETWEEN NVL(start_date_active, SYSDATE)
AND NVL(end_date_active, SYSDATE)
;
select resource_id
from jtf_rs_resource_extns
where user_id = p_user_id
AND SYSDATE BETWEEN NVL(start_date_active, SYSDATE)
AND NVL(end_date_active, SYSDATE)
;
select resource_id
from jtf_rs_resource_extns
where user_id = p_user_id
AND SYSDATE BETWEEN NVL(start_date_active, SYSDATE)
AND NVL(end_date_active, SYSDATE);
SELECT 1
FROM asg_user_pub_resps pubresp
, asg_user usr
WHERE usr.enabled = 'Y'
AND pubresp.user_name = usr.user_name
AND pubresp.pub_name = 'SERVICEP'
AND usr.resource_id = p_resource_id;
select fnd_user_resp.user_id
from asg_pub_responsibility asg_resp,
asg_pub,
fnd_user_resp_groups fnd_user_resp,
fnd_application fnd_app,
jtf_rs_resource_extns res
where asg_resp.pub_id = asg_pub.pub_id
and asg_pub.name = 'SERVICEP'
and asg_resp.responsibility_id = fnd_user_resp.responsibility_id
and fnd_app.application_id = fnd_user_resp.responsibility_application_id
and fnd_user_resp.user_id = res.user_id
AND SYSDATE BETWEEN nvl(fnd_user_resp.start_date, sysdate) AND nvl(fnd_user_resp.end_date, sysdate)
and fnd_app.application_short_name = 'CSM'
and res.resource_id = p_resource_id;
SELECT 1
FROM asg_user_pub_resps pubresp
, asg_user usr
WHERE usr.enabled = 'Y'
AND pubresp.user_name = usr.user_name
AND pubresp.pub_name = 'SERVICEP'
AND usr.user_id = b_user_id;
select fnd_user_resp.user_id
from asg_pub_responsibility asg_resp,
asg_pub,
fnd_user_resp_groups fnd_user_resp,
fnd_application fnd_app
where asg_resp.pub_id = asg_pub.pub_id
and asg_pub.name = 'SERVICEP'
and asg_resp.responsibility_id = fnd_user_resp.responsibility_id
and fnd_app.application_id = fnd_user_resp.responsibility_application_id
and fnd_app.application_short_name = 'CSM'
and fnd_user_resp.user_id = p_user_id
AND SYSDATE BETWEEN nvl(fnd_user_resp.start_date, sysdate) AND nvl(fnd_user_resp.end_date, sysdate)
;
select usr.USER_ID
FROM asg_user_pub_resps pubresp
, asg_user usr
WHERE usr.LANGUAGE = p_language
AND usr.enabled = 'Y'
AND pubresp.user_name = usr.user_name
AND pubresp.pub_name ='SERVICEP';
select usr.USER_ID
FROM asg_user_pub_resps pubresp
, asg_user usr
WHERE usr.LANGUAGE = p_language
AND usr.enabled = 'Y'
AND pubresp.user_name = usr.user_name
AND pubresp.pub_name ='SERVICEP'
AND usr.USER_ID = usr.OWNER_ID;
select usr.RESOURCE_ID
FROM asg_user_pub_resps pubresp
, asg_user usr
WHERE usr.LANGUAGE = p_language
AND usr.enabled = 'Y'
AND pubresp.user_name = usr.user_name
AND pubresp.pub_name ='SERVICEP';
select usr.RESOURCE_ID
FROM asg_user_pub_resps pubresp
, asg_user usr
WHERE usr.LANGUAGE = p_language
AND usr.enabled = 'Y'
AND pubresp.user_name = usr.user_name
AND pubresp.pub_name ='SERVICEP'
AND usr.USER_ID = usr.OWNER_ID;
SELECT language
FROM asg_user
WHERE user_id = p_user_id;
SELECT user_name
FROM asg_user
WHERE user_id = p_user_id;
OMFS Palm users if the entry gets updated, deleted or inserted in the ACC table
*/
PROCEDURE refresh_app_level_acc (
p_backend_table_name varchar2,
p_primary_key_column varchar2,
p_acc_table_name varchar2,
p_acc_sequence_name varchar2,
p_tl_table_name varchar2,
p_publication_item_name varchar2,
p_access_query varchar2,
p_primary_key_value number)
IS
l_access_id number;
l_last_update_date date;
/********** DELETES *****************/
--Delete deleted entries in backend from the ACC
IF l_primary_key_value IS NULL THEN
-- Mark Dirty 'D' the SDQ
l_dsql :=
' SELECT access_id '
|| ' FROM '
|| l_acc_table_name
|| ' WHERE '
|| l_primary_key_column
|| ' not in ( '
|| l_access_query
|| ' )';
'DELETE FROM '
|| l_acc_table_name
|| ' WHERE access_id '
|| ' IN
( '
|| l_dsql
|| ' )';
END IF; --END l_primary_key_value IS NULL (for delete case)
/********** END DELETES **************/
/******* UPDATES *********/
-- a) Updates to non TL table
l_dsql :=
' SELECT '
|| ' acc.access_id'
|| ' AS ACCESS_ID, b.'
|| l_primary_key_column
|| ' , b.LAST_UPDATE_DATE
FROM '
|| l_backend_table_name
|| ' b ,'
|| l_acc_table_name
|| ' acc
WHERE
b.'
|| l_primary_key_column
|| ' = acc.'
|| l_primary_key_column
|| ' AND
b.LAST_UPDATE_DATE > acc.LAST_UPDATE_DATE'
;
FETCH l_changed_records_cur INTO l_access_id, l_pk_value, l_last_update_date;
l_upd_dsql := 'UPDATE ' || l_acc_table_name
|| ' SET LAST_UPDATE_DATE = (SELECT LAST_UPDATE_DATE FROM '
|| l_backend_table_name
|| ' WHERE ' || l_primary_key_column || ' = ' || l_pk_value
|| '), LAST_UPDATED_BY = fnd_global.user_id WHERE '
|| ' access_id = ' || l_access_id;
' SELECT acc.access_id, '
|| ' b.'
|| l_primary_key_column
|| ' , b.LAST_UPDATE_DATE, b.LANGUAGE
FROM '
|| l_tl_table_name
|| ' b ,'
|| l_acc_table_name
|| ' acc
WHERE
b.'
|| l_primary_key_column
|| ' = acc.'
|| l_primary_key_column
|| ' AND
b.LAST_UPDATE_DATE > acc.LAST_UPDATE_DATE'
;
FETCH l_changed_records_cur INTO l_access_id, l_pk_value, l_last_update_date, l_language;
l_upd_dsql := 'UPDATE ' || l_acc_table_name
|| ' SET LAST_UPDATE_DATE = (SELECT LAST_UPDATE_DATE FROM '
|| l_tl_table_name
|| ' WHERE ' || l_primary_key_column || ' = ' || l_pk_value
|| '), LAST_UPDATED_BY = fnd_global.user_id WHERE '
|| ' access_id = ' || l_access_id;
/******* END UPDATES ****/
/******* INSERTS *******/
--Insert new entries in backend to the SDQ and ACC
-- Mark Dirty 'I' the SDQ
l_dsql :=
' SELECT '
|| l_acc_sequence_name || '.nextval, '
|| l_primary_key_column
|| ' FROM '
|| l_backend_table_name
|| ' WHERE '
|| l_primary_key_column
|| ' in ( '
|| l_access_query
|| ' ) AND '
|| l_primary_key_column
|| ' not in
( SELECT '
|| l_primary_key_column
|| ' FROM '
|| l_acc_table_name
|| ' )';
'INSERT INTO '
|| l_acc_table_name
|| ' ( access_id, '
|| l_primary_key_column
|| ', CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN)
SELECT ' || l_access_id || ', b.'
|| l_primary_key_column
|| ', fnd_global.user_id,
sysdate,
fnd_global.user_id,
NVL(b.LAST_UPDATE_DATE, sysdate),
fnd_global.user_id
FROM '
|| l_backend_table_name
|| ' b '
|| ' WHERE '
|| l_primary_key_column
|| ' = '
|| l_pk_value
|| ' AND '
|| l_primary_key_column
|| ' not in
( SELECT '
|| l_primary_key_column
|| ' FROM '
|| l_acc_table_name
|| ' )';
/********** END INSERTS **************/
END LOOP;
OMFS Palm users if the entry gets updated, deleted or inserted in the ACC table
*/
PROCEDURE refresh_all_app_level_acc(p_status OUT NOCOPY VARCHAR2,
p_message OUT NOCOPY VARCHAR2)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
SELECT 1
FROM jtm_con_request_data
WHERE product_code = 'CSM'
AND package_name = 'CSM_UTIL_PKG'
AND procedure_name = 'REFRESH_ALL_APP_LEVEL_ACC'
FOR UPDATE OF last_run_date NOWAIT
;
UPDATE jtm_con_request_data
SET last_run_date = l_last_run_date
WHERE CURRENT OF l_upd_last_run_date_csr;
applied and needs to be deleted from the in-queue.
***/
PROCEDURE DELETE_RECORD
(
p_user_name IN VARCHAR2,
p_tranid IN NUMBER,
p_seqno IN NUMBER,
p_pk IN VARCHAR2,
p_object_name IN VARCHAR2,
p_pub_name IN VARCHAR2,
p_error_msg OUT NOCOPY VARCHAR2,
x_return_status IN OUT NOCOPY VARCHAR2
) IS
BEGIN
x_return_status := FND_API.G_RET_STS_SUCCESS;
( 'Entering DELETE_RECORD');
asg_apply.delete_row(p_user_name,
p_tranid,
p_pub_name,
p_seqno,
x_return_status);
fnd_msg_pub.Add_Exc_Msg( g_object_name, 'DELETE_RECORD', 'Unknown error');
( 'Leaving DELETE_RECORD');
( 'Exception occurred in DELETE_RECORD:' || ' ' || sqlerrm);
fnd_msg_pub.Add_Exc_Msg( g_object_name, 'DELETE_RECORD', sqlerrm);
( 'Leaving DELETE_RECORD');
END DELETE_RECORD;
/*** defer successful -> reject record only for inserts ***/
IF p_dml_type = 'I' THEN
IF g_debug_level >= CSM_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
log
( 'Rejecting record');
when the PK of the inserted record is created in the API.
We need to remove the local PK from local
***/
PROCEDURE REJECT_RECORD
(
p_user_name IN VARCHAR2,
p_tranid IN NUMBER,
p_seqno IN NUMBER,
p_pk IN VARCHAR2,
p_object_name IN VARCHAR2,
p_pub_name IN VARCHAR2,
p_error_msg IN VARCHAR2,
x_return_status IN OUT NOCOPY VARCHAR2
) IS
BEGIN
x_return_status := FND_API.G_RET_STS_SUCCESS;
SELECT resp.APPLICATION_ID, resp.RESPONSIBILITY_ID
FROM FND_RESPONSIBILITY resp, fnd_application app
WHERE resp.RESPONSIBILITY_KEY = 'OMFS_PALM'
AND SYSDATE BETWEEN nvl(resp.start_date, sysdate) AND nvl(resp.end_date, sysdate)
AND app.application_id = resp.application_id
AND app.application_short_name = 'CSM';
SELECT esc.escalation_level
FROM jtf_tasks_b tasks,
jtf_task_references_vl ref,
jtf_tasks_b esc
WHERE ref.object_id = tasks.task_id
and ref.object_type_code = 'TASK'
and ref.reference_code = 'ESC'
and ref.task_id = esc.task_id
and esc.source_object_type_code = 'ESC'
and tasks.task_id = b_task_id;
SELECT TL.GROUP_NAME
FROM JTF_RS_GROUPS_B B,
JTF_RS_GROUPS_TL TL
WHERE B.GROUP_ID = b_grp_id
AND B.GROUP_ID = TL.GROUP_ID
AND TL.LANGUAGE=b_lang;
SELECT PF.FULL_NAME
FROM JTF_RS_RESOURCE_EXTNS RES,
PER_ALL_PEOPLE_F PF
WHERE RES.resource_id = b_owner_id
AND RES.SOURCE_ID=PF.PERSON_ID;
SELECT UOM_CODE
FROM MTL_UNITS_OF_MEASURE
WHERE UOM_CLASS = c_uom_class
AND base_uom_flag = 'Y';
SELECT conversion_rate
FROM MTL_UOM_CONVERSIONS
WHERE UOM_CLASS = c_uom_class
AND UOM_CODE = c_convert_to_uom
AND inventory_item_id = 0;
SELECT conversion_rate
FROM MTL_UOM_CONVERSIONS
WHERE UOM_CLASS = c_uom_class
AND UOM_CODE = c_convert_to_uom
AND inventory_item_id = 0;
SELECT 1
FROM ASG_USER
WHERE GROUP_ID=b_group_id;
SELECT USER_ID
FROM ASG_USER
WHERE OWNER_ID=USER_ID
AND GROUP_ID=b_group_id;
SELECT USER_ID
FROM ASG_USER
WHERE ROLE_CODE = 'MFS_OWNER'
AND GROUP_ID=b_group_id;*/
SELECT 1
FROM JTF_RS_GROUP_MEMBERS memG
WHERE RESOURCE_ID = b_member
AND EXISTS (SELECT 1 FROM JTF_RS_GROUP_MEMBERS ownG
WHERE ownG.GROUP_ID=memG.GROUP_ID
AND ownG.RESOURCE_ID=b_owner);
SELECT OWNER_ID
FROM ASG_USER
WHERE USER_ID= p_user_id;
SELECT GROUP_NAME--group_desc is a nullable column hence group_name is selected
FROM jtf_rs_groups_tl
WHERE GROUP_ID = b_group_id
AND LANGUAGE = b_language;