[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
The procedure has been modified so that it no longer inserts records
to GMD_FORMULA_SECURITY table if the assignment type is 'AUTOMATIC'.
When called from Security Profile form (p_formula_id is NULL)
1) INSERT :- New record is added in GMD_SECURITY_PROFILES
2) DELETE :- Delete records from GMD_SECURITY_PROFILES and Delete corresponding records
from GMD_FORMULA_SECURITY if the assignment type is 'MANUAL'
3) UPDATE :- Update records in GMD_SECURITY_PROFILES
Delete the records from GMD_FORMULA_SECURITY if the previous assignment type was 'MANUAL'
When called from the Formula form for a specific formula (p_formula_id is NOT NULL)
1) INSERT :- Create records in GMD_FORMULA_SECURITY one for each security profile, with
'Manual' assign type, associated with the Organisation with which the formula is associated.
2) DELETE :- Delete the records in GMD_FORMULA_SECURITY associated with that Formula_id
3) UPDATE :- Update the columns in GMD_FORMULA_SECURITY for the formula id.
************************************************************************************************************************************/
IS
--
-- Cursor cur_form_1 is used when inserting record into GMD_FORMULA_SECURITY wity assigned type Manual for a specific Formula
--
CURSOR cur_form_1 IS
SELECT formula_id,
owner_organization_id,
last_updated_by FROM fm_form_mst_b
WHERE formula_id = p_formula_id
AND owner_organization_id = sec_prof_rec.organization_id;
SELECT fnd_global.resp_id INTO x_resp_id FROM dual;
SELECT fnd_global.user_id INTO x_user_id FROM dual;
SELECT responsibility_id INTO v_resp_id
FROM fnd_responsibility
WHERE responsibility_key = 'GMD_SECURITY_PROFILE_MGR';
INSERT INTO GMD_SECURITY_PROFILES (
security_profile_id,
object_type,
organization_id,
other_organization_id,
user_id,
responsibility_id,
access_type_ind,
assign_method_ind,
created_by,
creation_date,
last_update_date,
last_updated_by,
last_update_login )
VALUES (
sec_prof_rec.security_profile_id,
'F',
sec_prof_rec.organization_id,
sec_prof_rec.other_organization_id,
sec_prof_rec.user_id,
sec_prof_rec.responsibility_id,
sec_prof_rec.access_type_ind,
sec_prof_rec.assign_method_ind,
sec_prof_rec.created_by,
sec_prof_rec.creation_date,
sec_prof_rec.last_update_date,
sec_prof_rec.last_updated_by,
sec_prof_rec.last_update_login);
DELETE FROM GMD_FORMULA_SECURITY
WHERE organization_id = before_sec_prof_rec.organization_id
AND NVL(user_id,-1) = NVL(before_sec_prof_rec.user_id,-1)
AND NVL(responsibility_id,0) = NVL(before_sec_prof_rec.responsibility_id,0)
AND NVL(other_organization_id, -1) = NVL(before_sec_prof_rec.other_organization_id, -1) ;
DELETE FROM GMD_SECURITY_PROFILES
WHERE organization_id = sec_prof_rec.organization_id
AND NVL(user_id,-1) = NVL(sec_prof_rec.user_id,-1)
AND NVL(responsibility_id,0) = NVL(sec_prof_rec.responsibility_id,0)
AND NVL(other_organization_id, -1) = NVL(sec_prof_rec.other_organization_id, -1);
UPDATE GMD_SECURITY_PROFILES
SET organization_id = sec_prof_rec.organization_id,
other_organization_id = sec_prof_rec.other_organization_id,
user_id = sec_prof_rec.user_id,
responsibility_id = sec_prof_rec.responsibility_id,
access_type_ind = sec_prof_rec.access_type_ind,
assign_method_ind = sec_prof_rec.assign_method_ind,
last_update_date = SYSDATE,
last_updated_by = sec_prof_rec.last_updated_by
WHERE organization_id = before_sec_prof_rec.organization_id
AND NVL(user_id,-1) = NVL(before_sec_prof_rec.user_id,-1)
AND NVL(responsibility_id,0) = NVL(before_sec_prof_rec.responsibility_id,0)
AND NVL(other_organization_id, -1) = NVL(before_sec_prof_rec.other_organization_id, -1);
INSERT INTO GMD_FORMULA_SECURITY (
formula_security_id,
formula_id,
access_type_ind,
organization_id,
user_id,
responsibility_id,
other_organization_id,
created_by,
creation_date,
last_update_date,
last_updated_by)
VALUES (
gmd_formula_security_id_s.NEXTVAL,
get_rec_1.formula_id,
sec_prof_rec.access_type_ind,
get_rec_1.owner_organization_id,
sec_prof_rec.user_id,
sec_prof_rec.responsibility_id,
sec_prof_rec.other_organization_id,
get_rec_1.last_updated_by,
SYSDATE,
SYSDATE,
get_rec_1.last_updated_by);
DELETE FROM GMD_FORMULA_SECURITY
WHERE organization_id = sec_prof_rec.organization_id
AND NVL(user_id,-1) = NVL(sec_prof_rec.user_id,-1)
AND NVL(responsibility_id,0) = NVL(sec_prof_rec.responsibility_id,0)
AND NVL(other_organization_id, -1) = NVL(sec_prof_rec.other_organization_id, -1)
AND formula_id = p_formula_id;
ELSE -- Update the Formula Security table with new data when db action is Update.
UPDATE GMD_FORMULA_SECURITY
SET organization_id = sec_prof_rec.organization_id,
user_id = sec_prof_rec.user_id,
responsibility_id = sec_prof_rec.responsibility_id,
other_organization_id = sec_prof_rec.other_organization_id,
last_update_date = SYSDATE,
last_updated_by = sec_prof_rec.last_updated_by
WHERE organization_id = before_sec_prof_rec.organization_id
AND NVL(user_id,-1) = NVL(before_sec_prof_rec.user_id,-1)
AND NVL(responsibility_id,0) = NVL(before_sec_prof_rec.responsibility_id,0)
AND NVL(other_organization_id, -1) = NVL(before_sec_prof_rec.other_organization_id, -1)
AND formula_id = p_formula_id ;