[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
pv_dc_query := 'select distinct ' ||
' ba_name ' ||
' ,ba_id ' ||
' from ' ||
p_eul || '.' || l_disco_table_version ||'_bas ba, ' ||
p_eul || '.' || l_disco_table_version ||'_ba_obj_links bol ' ||
' where ' ||
' bol.bol_obj_id = :1' ||
' and bol.bol_ba_id = ba.ba_id ';
l_sql:= 'select ba_name, ba_id from ' || p_eul ||'.'|| l_disco_table_version || '_bas ba, ' ||
p_eul ||'.' || l_disco_table_version || '_ba_obj_links bol ' ||
'where ' || p_folder_id || ' = bol.bol_obj_id ' ||
'and bol.bol_ba_id = ba.ba_id ';
insert into bis_search_results ( eul
,eul_id
,folder_name
,ba_id
,business_area
,folder_description
,eul_access
,user_id)
values( p_results_tbl(i).folder_eul
,p_results_tbl(i).folder_id
,p_results_tbl(i).folder_name
,l_ba(j).folder_id
,l_ba(j).folder_name
,p_results_tbl(i).folder_description
,pv_is_accessible
,gv_user_id);
EXECUTE IMMEDIATE 'DELETE FROM bis_search_results';
select FND_GLOBAL.user_id into gv_user_id from dual;
pv_dc_query := 'select ' ||
' count (distinct ap.gba_ba_id) ' ||
' from ' ||
x_eul || '.' || l_disco_table_version ||'_bas ba, ' ||
x_eul || '.' || l_disco_table_version ||'_access_privs ap, ' ||
x_eul || '.' || l_disco_table_version ||'_eul_users eu, ' ||
' fnd_responsibility_vl r, ' ||
' fnd_application a, ' ||
' fnd_user_resp_groups ur, ' ||
' fnd_user u, ' ||
' fnd_security_groups_vl s, ' ||
' fnd_data_group_units dgu, ' ||
' fnd_oracle_userid ou ' ||
' where ' ||
' ba.ba_id = ap.gba_ba_id and ' ||
' ba.ba_id = :1 and ' ||
' ap.ap_eu_id = eu.eu_id and ' ||
' eu.eu_role_flag = 1 and ' ||
' ap.ap_type = ''GBA'' and ' ||
' ur.responsibility_id = TO_NUMBER(SUBSTR(eu.eu_username,2,(INSTR(eu.eu_username,''#'',2)-2))) and ' ||
' ur.responsibility_application_id = TO_NUMBER(SUBSTR(eu.eu_username,INSTR(eu.eu_username,''#'',2)+1)) and ' ||
' u.user_id = :2 and ' ||
' u.user_id = ur.user_id and ' ||
' (sysdate BETWEEN ur.start_date AND NVL(ur.end_date, sysdate)) and ' ||
' r.application_id = ur.responsibility_application_id and ' ||
' r.responsibility_id = ur.responsibility_id and ' ||
' (sysdate BETWEEN r.start_date AND NVL(r.end_date, sysdate)) and ' ||
' r.application_id = a.application_id and ' ||
' ur.security_group_id IN (-1, s.security_group_id) and ' ||
' s.security_group_id >= 0 and ' ||
' r.data_group_id = dgu.data_group_id and ' ||
' r.data_group_application_id = dgu.application_id and ' ||
' dgu.oracle_id = ou.oracle_id';