I need to improve the performance of this procedure. I will appreciate any suggestions.
Procedure GET_USER_ACCESS_TOOBJECT(p
_user_name
_i IN xxccs_caam_object_access_i
nfo.target
_user_id%T
YPE,
p_obj_access_info_tab_i IN xxccs_caam_obj_access_tab_
type) IS
l_flag BOOLEAN := FALSE;
l_user_id fnd_user.user_id%TYPE;
BEGIN
IF p_user_name_i IS NOT NULL THEN
l_user_id := xxccs_caam_appl_pub.get_va
lid_user_i
d(p_user_n
ame_i);
IF l_flag = TRUE THEN
xxccs_fea_utl_log.error('U
nknow User exception while accessing GET_LIST_OBJ_ACCESSIBLE API: ('
||SQLCODE || '): (' || SQLERRM || ')');
xxccs_fea_utl_exception.th
row ('XXCCS_CAAM_UNKNOWN_USER'
,'XXCCS_CA
AM');
ELSIF l_flag = FALSE THEN
IF p_obj_access_info_tab_i.EX
ISTS(1) THEN
FOR i IN p_obj_access_info_tab_i.FI
RST .. p_obj_access_info_tab_i.LA
ST
LOOP
FOR j IN (SELECT xcssam.apllication_short_n
ame,
xcssam.object_number,
xcssam.object_type,
xcssam.target_user_id,
xcssam.grantor_user_id,
xcssam.creator_user_id,
xcssam.owner_user_id,
xcssam.object_privilege,
NULL action,
xcssam.attribute_n1,
xcssam.attribute_n2,
xcssam.attribute_n3,
xcssam.attribute_n4,
xcssam.attribute_n5,
xcssam.attribute_c1,
xcssam.attribute_c2,
xcssam.attribute_c3,
xcssam.attribute_c4,
xcssam.attribute_c5,
xcssam.status,
xcssam.notes,
xcssam.access_start_date,
xcssam.access_end_date
FROM xxccs_caam_object_access_i
nfo xcssam
WHERE xcssam.object_number = p_obj_access_info_tab_i(i)
.object_nu
mber
AND xcssam.object_type = p_obj_access_info_tab_i(i)
.object_ty
pe
AND xcssam.target_user_id = l_user_id
AND NVL(xcssam.access_end_date
,
SYSDATE) >= SYSDATE)
LOOP
dbms_output.put_line('In the Loop for Insert.');
INSERT INTO get_user_access_toobject
VALUES (j.apllication_short_name,
j.object_number,
j.object_type,
j.target_user_id,
j.grantor_user_id,
j.creator_user_id,
j.owner_user_id,
j.object_privilege,
j.action,
j.attribute_n1,
j.attribute_n2,
j.attribute_n3,
j.attribute_n4,
j.attribute_n5,
j.attribute_c1,
j.attribute_c2,
j.attribute_c3,
j.attribute_c4,
j.attribute_c5,
j.status,
j.notes,
j.access_start_date,
j.access_end_date);
END LOOP;
END LOOP;
END IF;
END IF;
ELSE
Start Free Trial