[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select employee_id, customer_id, supplier_id
into l_creby_empid, l_creby_custid, l_creby_supid
from fnd_user
where user_id = l_current_user_id;
select resource_id, resource_number
into x_resource_id, x_resource_number
from jtf_rs_resource_extns
where source_id = l_creby_source_id
and category = l_creby_category
and ( (end_date_active is null) or (end_date_active > sysdate) );
select count(*) into x_cnt
from fnd_user
where employee_id = p_source_id
and ( ( end_date is null) or (end_date > sysdate) ) ;
select max(user_id) into x_cnt
from fnd_user
where customer_id = p_source_id
and ( ( end_date is null) or (end_date > sysdate) ) ;
select max(user_id) into x_cnt
from fnd_user
where supplier_id = p_source_id
and ( ( end_date is null) or (end_date > sysdate) ) ;
select max(user_id) into x_user_id
from fnd_user
where employee_id = p_source_id;
select max(user_id) into x_user_id
from fnd_user
where customer_id = p_source_id;
select max(user_id) into x_user_id
from fnd_user
where supplier_id = p_source_id;
SELECT UPPER(p_sql_text) into l_q_str from DUAL;
SELECT INSTR (l_q_str, 'FROM', 1, 1) INTO l_num FROM DUAL;
SELECT SUBSTR (l_q_str, 1, l_num) INTO l_substring FROM DUAL;
SELECT INSTR (l_substring, 'PARTY_ID', 1, 1) INTO l_party_num FROM DUAL;
SELECT INSTR (l_substring, 'EMPLOYEE_ID', 1, 1) INTO l_emp_num FROM DUAL;
Procedure Insert_Temp_Table (p_sql_text varchar2, p_category varchar2)
IS
cid INTEGER;
SELECT UPPER(p_sql_text) into l_q_str from DUAL;
SELECT INSTR (l_q_str, 'FROM', 1, 1) INTO l_num FROM DUAL;
SELECT SUBSTR (l_q_str, 1, l_num-1) INTO l_first_substring FROM DUAL;
SELECT length (l_q_str) INTO l_length FROM DUAL;
SELECT SUBSTR (p_sql_text, l_num, l_length) INTO l_last_substring FROM DUAL;
l_ins_qry := 'insert into ibu_usergroups_temp(source_id, category,creation_date, created_by, last_update_date, last_updated_by ) ';
Log_Message(' Insert Temp table query = { ' || l_ins_qry || ' } ');
dbms_sql.parse(cid, 'delete from ibu_usergroups_temp', dbms_sql.v7);
Log_Message(' Insert Temp table rows deleted = ' || l_rows_processed);
Log_Message(' Insert Temp table rows inserted = ' || l_rows_processed);
select count(*) into l_cnt from ibu_usergroups_temp;
Log_Message('Error in Insert_Temp_Table' ||TO_CHAR(SQLCODE)||': '||SQLERRM );
Procedure Update_Category_SourceID (p_category varchar2)
Is
l_emp_id fnd_user.employee_id%TYPE;
select source_id
from ibu_usergroups_temp ;
select max(employee_id) into l_emp_id
from fnd_user A
Where A.customer_id = l_source_rec.source_id
and A.employee_id is not null
and exists ( select person_id
from per_workforce_current_x
Where person_id = A.employee_id)
and ( (A.end_date is null) OR (A.end_date > sysdate) );
Update ibu_usergroups_temp
Set Category = 'EMPLOYEE', Source_id = l_emp_id
Where source_id = l_source_rec.source_id;
Log_Message('Error in Update_Category_SourceID' ||TO_CHAR(SQLCODE)||': '||SQLERRM );
select
B.group_id,
ATL.group_name || '(' || A.group_number || ')' group_name,
B.group_number,
A.start_date_active,
A.end_date_active,
B.exclusive_flag,
BTL.group_desc,
B.object_version_number,
A.sql_text
from
jtf_rs_dynamic_groups_b A,
jtf_rs_dynamic_groups_tl ATL,
jtf_rs_groups_b B,
jtf_rs_groups_tl BTL
Where
B.group_id = BTL.group_id
and
A.group_id = ATL.group_id
and
BTL.language = userenv('LANG')
and
ATL.language = BTL.language
and
BTL.GROUP_NAME = ATL.group_name || '(' || A.group_number || ')'
and
nvl(a.end_date_active,trunc(sysdate)) >= trunc(sysdate)
and
A.Usage = 'ISUPPORT' ;
select A.group_member_id,
B.resource_id,
B.resource_number ,
B.source_id,
A.object_version_number
from jtf_rs_group_members_vl A, jtf_rs_resource_extns B
where group_id = p_group_id
and A.resource_id = B.resource_id
and A.delete_flag = 'N'
and A.resource_id not in ( select resource_id
from jtf_rs_resource_extns C, ibu_usergroups_temp D
where C.source_id = D.source_id
and C.category = D.category);
select source_id, category
from ibu_usergroups_temp A
where not exists (select source_id
from jtf_rs_resource_extns
where source_id = A.source_id
and category = A.category
and ( (end_date_active is null ) OR (end_date_active > sysdate) )
);
select B.resource_id, B.resource_number
from ibu_usergroups_temp A, jtf_rs_resource_extns B
where A.source_id = B.source_id
and A.category = B.category
and not exists ( select resource_id
from jtf_rs_group_members
where resource_id = B.resource_id
and group_id = p_group_id
and delete_flag = 'N')
and ( ( B.end_date_active is null ) or (B.end_date_active > sysdate) ) ;
SELECT date_start
FROM per_periods_of_service
WHERE person_id = c_person_id
ORDER BY date_start asc;
SELECT full_name
FROM per_all_people_f
WHERE person_id = c_person_id
ORDER BY effective_start_date desc;
Log_Message( '%%%Start Update Processing for Group Name = ' || l_group_name || '%%%');
/* call update role_relations */
select role_relate_id, object_version_number
into l_role_relate_id, l_object_version_number
from jtf_rs_role_relations
where role_resource_type = 'RS_GROUP'
and role_resource_id = l_group_id;
jtf_rs_role_relate_pub.UPDATE_RESOURCE_ROLE_RELATE (
P_API_VERSION => 1.0,
P_INIT_MSG_LIST => fnd_api.g_false,
P_COMMIT => FND_API.G_FALSE,
P_ROLE_RELATE_ID => l_role_relate_id,
P_START_DATE_ACTIVE => l_start_date_active,
P_END_DATE_ACTIVE => l_end_date_active,
P_OBJECT_VERSION_NUM => l_object_version_number,
X_RETURN_STATUS => l_return_status,
X_MSG_COUNT => l_msg_count,
X_MSG_DATA => l_msg_data
);
Status_Log_Message('Return status of JTF_RS_ROLE_RELATE_PUB.UPDATE_RESOURCE_ROLE_RELATE api ',
l_return_status , l_msg_count , l_msg_data );
/* Update Resource Group.*/
JTF_RS_GROUPS_PUB.UPDATE_RESOURCE_GROUP(
P_API_VERSION => 1,
P_INIT_MSG_LIST => FND_API.G_FALSE,
P_COMMIT => FND_API.G_FALSE,
P_GROUP_ID => l_group_id,
P_GROUP_NUMBER => l_group_number,
P_GROUP_NAME => l_group_name,
P_GROUP_DESC => l_group_desc,
P_EXCLUSIVE_FLAG => l_exclusive_flag,
P_EMAIL_ADDRESS => NULL,
P_START_DATE_ACTIVE => l_start_date_active,
P_END_DATE_ACTIVE => l_end_date_active,
P_ACCOUNTING_CODE => NULL,
P_OBJECT_VERSION_NUM => l_obj_grp_version_num,
X_RETURN_STATUS => l_return_status,
X_MSG_COUNT => l_msg_count,
X_MSG_DATA => l_msg_data
);
Status_Log_Message('Return status of JTF_RS_GROUPS_PUB.UPDATE_RESOURCE_GROUP api ',
l_return_status , l_msg_count , l_msg_data );
/* Insert into temporary table, results of user group 'dynamic' query. */
insert_temp_table(l_sql_text, l_category);
/* In case of customer user group, Some of the customers could be valid employees, so update the category column to 'EMPLOYEE' for those customers, source_id column to employee_id */
Update_Category_SourceId(l_category);
/* Delete existing members in the group who should not be part of this user group. Reason : because of change in the user group query */
For l_del_mem_rec IN l_del_mem_csr(l_group_id) Loop
JTF_RS_GROUP_MEMBERS_PUB.delete_resource_group_members
(P_API_VERSION => 1,
P_INIT_MSG_LIST =>FND_API.G_FALSE,
P_COMMIT =>FND_API.G_FALSE,
P_GROUP_ID =>l_upd_rec.group_id ,
P_GROUP_NUMBER => l_upd_rec.group_number,
P_RESOURCE_ID => l_del_mem_rec.resource_id,
P_RESOURCE_NUMBER => l_del_mem_rec.resource_number,
P_OBJECT_VERSION_NUM => l_del_mem_rec.object_version_number,
X_RETURN_STATUS => l_return_status,
X_MSG_COUNT => l_msg_count,
X_MSG_DATA => l_msg_data
);
Status_Log_Message('Return status of JTF_RS_GROUP_MEMBERS_PUB.delete_resource_group_members api ' ||
' l_mem_id = ' || to_char(l_del_mem_rec.group_member_id) || ' ',
l_return_status , l_msg_count , l_msg_data );
select count(*) into l_resource_cnt
from jtf_rs_resource_extns
where source_id = l_cre_res_rec.source_id
and category = l_cre_res_rec.category;
SELECT party_name,creation_date
INTO l_source_name, l_res_start_date_active
FROM hz_parties
WHERE party_id = l_cre_res_rec.source_id;
select count(*)
into l_dbl_res_cnt
from jtf_rs_resource_extns
where source_id = l_cre_res_rec.source_id
and category = l_cre_res_rec.category
and ( (end_date_active is null) or (end_date_active > sysdate) );
select resource_id, resource_number, object_version_number, end_date_active
into l_resource_id, l_resource_number , l_object_version_number, l_res_end_date_active
from jtf_rs_resource_extns
where source_id = l_cre_res_rec.source_id
and category = l_cre_res_rec.category
and resource_id = (select max(resource_id)
from jtf_rs_resource_extns
Where source_id = l_cre_res_rec.source_id
and category = l_cre_res_rec.category);
Log_Message( '%%%End Update Processing for Group Name = ' || l_group_name || '%%%');
Log_Message( '%%%Not a Valid SQL : Update Processing for Group Name = ' || l_group_name || '%%%');
select A.group_id, A.group_name, A.group_number,
A.group_name || '(' || A.group_number || ')' static_group_name,
A.start_date_active, A.end_date_active,
A.group_desc,
A.sql_text,
A.created_by
from jtf_rs_dynamic_groups_vl A
Where not exists ( Select B.group_name
from jtf_rs_groups_vl B
Where B.group_name = A.group_name || '(' || A.group_number || ')' )
and ( ( A.end_date_active is null ) or ( A.end_date_active > sysdate ) )
and A.Usage = 'ISUPPORT';
select source_id, category
from ibu_usergroups_temp ;
SELECT date_start
FROM per_periods_of_service
WHERE person_id = c_person_id
ORDER BY date_start asc;
SELECT full_name
FROM per_all_people_f
WHERE person_id = c_person_id
ORDER BY effective_start_date desc;
/* Insert into temporary table, results of user group 'dynamic' query. */
insert_temp_table(l_cre_rec.sql_text, l_category);
/* In case of customer user group, Some of the customers could be valid employees, so update the category column to 'EMPLOYEE' for those customers, source_id column to employee_id */
Update_Category_SourceId(l_category);
SELECT count(*) INTO l_res_cnt
FROM jtf_rs_resource_extns a
WHERE a.source_id = l_cre_mem_rec.source_id
and category = l_cre_mem_rec.category;
SELECT party_name,creation_date
INTO l_source_name, l_res_start_date_active
FROM hz_parties
WHERE party_id = l_cre_mem_rec.source_id;
select count(*)
into l_dbl_res_cnt
from jtf_rs_resource_extns
where source_id = l_cre_mem_rec.source_id
and category = l_cre_mem_rec.category
and ( (end_date_active is null ) OR (end_date_active > sysdate) );
select resource_id, resource_number, object_version_number, end_date_active
into l_resource_id, l_resource_number, l_object_version_number , l_res_end_date_active
from jtf_rs_resource_extns
where source_id = l_cre_mem_rec.source_id
and category = l_cre_mem_rec.category
and resource_id = (select max(resource_id)
from jtf_rs_resource_extns
Where source_id = l_cre_mem_rec.source_id
and category = l_cre_mem_rec.category);
log_message ( 'select resource_number, end_date_active, start_date ' ||
' from jtf_rs_resource_extns ' ||
' where source_id = ? ' ||
' and category_id = ? ' );
select resource_id, resource_number, object_version_number, end_date_active
into l_resource_id, l_resource_number, l_object_version_number , l_res_end_date_active
from jtf_rs_resource_extns
where source_id = l_cre_mem_rec.source_id
and category = l_cre_mem_rec.category
and ( (end_date_active is null ) OR (end_date_active > sysdate) );
select count(*) into l_temp_cnt
from jtf_rs_group_members
Where group_id = l_group_id
and resource_id = l_resource_id
and delete_flag = 'N';
select A.group_id dyn_group_id,
A.group_name dyn_group_name,
A.group_number dyn_group_number,
B.group_id static_group_id,
B.group_name static_group_name,
B.group_number static_group_number,
A.start_date_active, A.end_date_active, A.sql_text,
B.exclusive_flag, B.group_desc, B.object_version_number
from jtf_rs_dynamic_groups_vl A, jtf_rs_groups_vl B
Where A.group_name || '(' || A.group_number || ')' = B.GROUP_NAME
and ( ( B.end_date_active is null ) OR ( B.end_date_active > sysdate ) )
and ( ( A.end_date_active is not null ) AND ( A.end_date_active <= sysdate ) )
and A.usage = 'ISUPPORT';
select channel_id
from amv_c_channels_vl
where channel_name = p_group_name;
select B.group_member_id,
A.group_id,
A.group_number,
B.object_version_number,
B.resource_id,
C.resource_number
from jtf_rs_groups_vl A, jtf_rs_group_members B, jtf_rs_resource_extns C
Where A.group_id = p_group_id
and A.group_id = B.group_id
and B.resource_id = C.resource_id
and B.delete_flag = 'N' ;
Log_Message( '%%%Start Delete Processing for Group Name = ' || l_del_rec.static_group_name || '%%%');
/* call the delete channel api of MES */
For l_del_chnl_rec IN l_del_chnl_csr(l_del_rec.static_group_name) Loop
AMV_CHANNEL_GRP.DELETE_CHANNEL( P_API_VERSION => 1.0,
P_INIT_MSG_LIST => FND_API.G_FALSE,
P_COMMIT => FND_API.G_FALSE,
P_VALIDATION_LEVEL =>FND_API.G_VALID_LEVEL_FULL,
P_CHECK_LOGIN_USER =>FND_API.G_FALSE,
P_CHANNEL_ID => l_del_chnl_rec.channel_id,
X_RETURN_STATUS => l_return_status,
X_MSG_COUNT => l_msg_count,
X_MSG_DATA =>l_msg_data
);
Status_Log_Message('Return status of AMV_CHANNEL_GRP.DELETE_CHANNEL api ' || ' l_del_chnl_rec.channel_id = ' ||
to_char(l_del_chnl_rec.channel_id) || ' ',
l_return_status , l_msg_count , l_msg_data );
/* call update role_relations */
Begin
select role_relate_id, object_version_number
into l_role_relate_id, l_object_version_number
from jtf_rs_role_relations
where role_resource_type = 'RS_GROUP'
and role_resource_id = l_del_rec.static_group_id;
jtf_rs_role_relate_pub.UPDATE_RESOURCE_ROLE_RELATE( P_API_VERSION => 1.0,
P_INIT_MSG_LIST => fnd_api.g_false,
P_COMMIT => FND_API.G_FALSE,
P_ROLE_RELATE_ID => l_role_relate_id,
P_START_DATE_ACTIVE => l_del_rec.start_date_active,
P_END_DATE_ACTIVE => l_del_rec.end_date_active,
P_OBJECT_VERSION_NUM => l_object_version_number,
X_RETURN_STATUS => l_return_status,
X_MSG_COUNT => l_msg_count,
X_MSG_DATA => l_msg_data
);
Status_Log_Message('Return status of jtf_rs_role_relate_pub.update_resource_role_relate api ',
l_return_status , l_msg_count , l_msg_data );
/* call update resource group */
jtf_rs_groups_pub.update_resource_group
(P_API_VERSION => 1,
P_INIT_MSG_LIST => fnd_api.g_false,
P_COMMIT => FND_API.G_FALSE,
P_GROUP_ID => l_del_rec.static_group_id,
P_GROUP_NUMBER => l_del_rec.static_group_number,
P_GROUP_NAME => l_del_rec.static_group_name,
P_GROUP_DESC => l_del_rec.group_desc,
P_EXCLUSIVE_FLAG => l_del_rec.exclusive_flag,
P_START_DATE_ACTIVE => l_del_rec.start_date_active,
P_END_DATE_ACTIVE => l_del_rec.end_date_active,
P_ACCOUNTING_CODE => NULL,
P_OBJECT_VERSION_NUM => l_del_rec.object_version_number,
X_RETURN_STATUS => l_return_status,
X_MSG_COUNT => l_msg_count,
X_MSG_DATA => l_msg_data
);
Status_Log_Message('Return status of JTF_RS_GROUPS_PUB.UPDATE_RESOURCE_GROUP api ',
l_return_status , l_msg_count , l_msg_data );
jtf_rs_group_members_pub.delete_resource_group_members(P_API_VERSION => 1.0,
P_INIT_MSG_LIST => FND_API.G_FALSE,
P_COMMIT => FND_API.G_FALSE,
P_GROUP_ID => l_del_mem_rec.group_id,
P_GROUP_NUMBER => l_del_mem_rec.group_number,
P_RESOURCE_ID => l_del_mem_rec.resource_id,
P_RESOURCE_NUMBER => l_del_mem_rec.resource_number,
P_OBJECT_VERSION_NUM => l_del_mem_rec.object_version_number,
X_RETURN_STATUS => l_return_status,
X_MSG_COUNT => l_msg_count,
X_MSG_DATA => l_msg_data
);
Status_Log_Message('Return status of JTF_RS_GROUP_MEMBERS_PUB.delete_resource_group_members api ' ||
' l_mem_id = ' || to_char(l_del_mem_rec.group_member_id) || ' ',
l_return_status , l_msg_count , l_msg_data );
Log_Message( '%%%End Delete Processing for Group Name = ' || l_del_rec.static_group_name || '%%%');
SELECT date_start
FROM per_periods_of_service
WHERE person_id = c_person_id
ORDER BY date_start asc;
SELECT full_name
FROM per_all_people_f
WHERE person_id = c_person_id
ORDER BY effective_start_date desc;
select employee_id, customer_id, supplier_id into l_creby_empid, l_creby_custid, l_creby_supid
from fnd_user
where user_id = l_current_user_id;
select count(*) into l_creby_res_cnt
from jtf_rs_resource_extns
where source_id = l_creby_source_id
and category = l_creby_category;
SELECT party_name,creation_date
INTO l_source_name, l_res_start_date_active
FROM hz_parties
WHERE party_id = l_creby_custid;
SELECT POC.LAST_NAME || ' , ' || POC.MIDDLE_NAME ||' '||
POC.FIRST_NAME|| ' - '|| POV.VENDOR_NAME, nvl(POC.CREATION_DATE,sysdate)
INTO l_source_name, l_res_start_date_active
FROM PO_VENDOR_CONTACTS POC,
PO_VENDOR_SITES_ALL POS,
PO_VENDORS POV
WHERE POC.VENDOR_CONTACT_ID = l_creby_supid
AND POC.VENDOR_SITE_ID = POS.VENDOR_SITE_ID
AND POS.VENDOR_ID = POV.VENDOR_ID;
select count(*)
into l_dbl_res_cnt
from jtf_rs_resource_extns
where source_id = l_creby_source_id
and category = l_creby_category
and ( (end_date_active is null) or (end_date_active > sysdate) );
log_message ( 'select resource_number, end_date_active, start_date ' ||
' from jtf_rs_resource_extns ' ||
' where source_id = ' || l_creby_source_id ||
' and category = ' || '''' || l_creby_category || '''' );
log_message ( 'select resource_number, end_date_active, start_date ' ||
' from jtf_rs_resource_extns ' ||
' where source_id = ' || l_creby_source_id ||
' and category = ' || '''' || l_creby_category || '''' );
select resource_id, resource_number
into x_resource_id, x_resource_number
from jtf_rs_resource_extns
where source_id = l_creby_source_id
and category = l_creby_category
and ( (end_date_active is null) or (end_date_active > sysdate) );
/* MES Delete Channel API expects sysadmin to have MES_ADMIN, MES_SETUP_CHANNEL role_codes */
Log_Message ('Assigning MES_ADMIN, MES_SETUP_CHANNEL roles to current login user. Ignore exceptions raised by these APIs');