1 PACKAGE BODY GMD_FOR_SEC1 AS
2 /* $Header: GMDFSPRB.pls 120.1 2005/07/26 14:02:58 txdaniel noship $ */
3
4 PROCEDURE sec_prof_form(
5 p_api_version IN NUMBER,
6 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
7 before_sec_prof_rec IN gmd_security_profiles%ROWTYPE,
8 sec_prof_rec IN gmd_security_profiles%ROWTYPE,
9 p_formula_id IN NUMBER,
10 p_db_action_ind IN VARCHAR2,
11 x_return_status OUT NOCOPY VARCHAR2,
12 x_msg_count OUT NOCOPY NUMBER,
13 x_msg_data OUT NOCOPY VARCHAR2,
14 x_return_code OUT NOCOPY NUMBER
15 )
16 /********************************************************************************************************************************
17 Change History
18 Who When What
19 nsrivast 16-JAN-2004 Replace the existing code with code to reflect the new behavior w.r.t. bug 3344335.
20 The procedure has been modified so that it no longer inserts records
21 to GMD_FORMULA_SECURITY table if the assignment type is 'AUTOMATIC'.
22
23 When called from Security Profile form (p_formula_id is NULL)
24 1) INSERT :- New record is added in GMD_SECURITY_PROFILES
25 2) DELETE :- Delete records from GMD_SECURITY_PROFILES and Delete corresponding records
26 from GMD_FORMULA_SECURITY if the assignment type is 'MANUAL'
27 3) UPDATE :- Update records in GMD_SECURITY_PROFILES
28 Delete the records from GMD_FORMULA_SECURITY if the previous assignment type was 'MANUAL'
29
30 When called from the Formula form for a specific formula (p_formula_id is NOT NULL)
31 1) INSERT :- Create records in GMD_FORMULA_SECURITY one for each security profile, with
32 'Manual' assign type, associated with the Organisation with which the formula is associated.
33 2) DELETE :- Delete the records in GMD_FORMULA_SECURITY associated with that Formula_id
34 3) UPDATE :- Update the columns in GMD_FORMULA_SECURITY for the formula id.
35 ************************************************************************************************************************************/
36 IS
37
38
39 --
40 -- Cursor cur_form_1 is used when inserting record into GMD_FORMULA_SECURITY wity assigned type Manual for a specific Formula
41 --
42
43 CURSOR cur_form_1 IS
44 SELECT formula_id,
45 owner_organization_id,
46 last_updated_by FROM fm_form_mst_b
47 WHERE formula_id = p_formula_id
48 AND owner_organization_id = sec_prof_rec.organization_id;
49
50 get_rec_1 cur_form_1%ROWTYPE ;
51 x_resp_id NUMBER;
52 x_user_id NUMBER;
53 v_resp_id NUMBER;
54 BEGIN
55
56 x_return_status := 'S';
57 SELECT fnd_global.resp_id INTO x_resp_id FROM dual;
58 SELECT fnd_global.user_id INTO x_user_id FROM dual;
59
60 SELECT responsibility_id INTO v_resp_id
61 FROM fnd_responsibility
62 WHERE responsibility_key = 'GMD_SECURITY_PROFILE_MGR';
63
64 fnd_global.apps_initialize (2060, v_resp_id , 552);
65
66 --
67 -- GMD_SECURITY_PROFILE table updated when called from GMDFMSCP form , Formula Id is always NULL
68 --
69 IF NVL(p_formula_id,0) = 0
70 THEN
71 --Creates new records in GMD_SECURITY_PROFILES table when db action is insert.
72 IF p_db_action_ind = 'I'
73 THEN
74 INSERT INTO GMD_SECURITY_PROFILES (
75 security_profile_id,
76 object_type,
77 organization_id,
78 other_organization_id,
79 user_id,
80 responsibility_id,
81 access_type_ind,
82 assign_method_ind,
83 created_by,
84 creation_date,
85 last_update_date,
86 last_updated_by,
87 last_update_login )
88 VALUES (
89 sec_prof_rec.security_profile_id,
90 'F',
91 sec_prof_rec.organization_id,
92 sec_prof_rec.other_organization_id,
93 sec_prof_rec.user_id,
94 sec_prof_rec.responsibility_id,
95 sec_prof_rec.access_type_ind,
96 sec_prof_rec.assign_method_ind,
97 sec_prof_rec.created_by,
98 sec_prof_rec.creation_date,
99 sec_prof_rec.last_update_date,
100 sec_prof_rec.last_updated_by,
101 sec_prof_rec.last_update_login);
102 ELSE
103 -- Delete the records from fromula security if the old assignment type was 'MANUAL'
104 -- and action is delete/update
105 IF before_sec_prof_rec.assign_method_ind='M' THEN
106 DELETE FROM GMD_FORMULA_SECURITY
107 WHERE organization_id = before_sec_prof_rec.organization_id
108 AND NVL(user_id,-1) = NVL(before_sec_prof_rec.user_id,-1)
109 AND NVL(responsibility_id,0) = NVL(before_sec_prof_rec.responsibility_id,0)
110 AND NVL(other_organization_id, -1) = NVL(before_sec_prof_rec.other_organization_id, -1) ;
111 END IF;
112
113 -- If db action is delete then delete all old profiles from Security Profile table.
114 IF p_db_action_ind = 'D' THEN
115 DELETE FROM GMD_SECURITY_PROFILES
116 WHERE organization_id = sec_prof_rec.organization_id
117 AND NVL(user_id,-1) = NVL(sec_prof_rec.user_id,-1)
118 AND NVL(responsibility_id,0) = NVL(sec_prof_rec.responsibility_id,0)
119 AND NVL(other_organization_id, -1) = NVL(sec_prof_rec.other_organization_id, -1);
120
121 -- Update the Security Profile table with new data when db action is Update.
122 ELSE
123 UPDATE GMD_SECURITY_PROFILES
124 SET organization_id = sec_prof_rec.organization_id,
125 other_organization_id = sec_prof_rec.other_organization_id,
126 user_id = sec_prof_rec.user_id,
127 responsibility_id = sec_prof_rec.responsibility_id,
128 access_type_ind = sec_prof_rec.access_type_ind,
129 assign_method_ind = sec_prof_rec.assign_method_ind,
130 last_update_date = SYSDATE,
131 last_updated_by = sec_prof_rec.last_updated_by
132 WHERE organization_id = before_sec_prof_rec.organization_id
133 AND NVL(user_id,-1) = NVL(before_sec_prof_rec.user_id,-1)
134 AND NVL(responsibility_id,0) = NVL(before_sec_prof_rec.responsibility_id,0)
135 AND NVL(other_organization_id, -1) = NVL(before_sec_prof_rec.other_organization_id, -1);
136 END IF ;
137 END IF;
138
139 --Called from Manual Entry on formula for a specific formula.
140 ELSE
141 --Creates new records in GMD_FORMULA_SECURITY table when db action is insert.
142 IF p_db_action_ind = 'I' THEN
143 OPEN cur_form_1 ;
144 FETCH cur_form_1 INTO get_rec_1;
145 IF cur_form_1%FOUND THEN
146 INSERT INTO GMD_FORMULA_SECURITY (
147 formula_security_id,
148 formula_id,
149 access_type_ind,
150 organization_id,
151 user_id,
152 responsibility_id,
153 other_organization_id,
154 created_by,
155 creation_date,
156 last_update_date,
157 last_updated_by)
158 VALUES (
159 gmd_formula_security_id_s.NEXTVAL,
160 get_rec_1.formula_id,
161 sec_prof_rec.access_type_ind,
162 get_rec_1.owner_organization_id,
163 sec_prof_rec.user_id,
164 sec_prof_rec.responsibility_id,
165 sec_prof_rec.other_organization_id,
166 get_rec_1.last_updated_by,
167 SYSDATE,
168 SYSDATE,
169 get_rec_1.last_updated_by);
170 END IF;
171 CLOSE cur_form_1;
172 -- If db action is delete then delete all old profiles from Formula Security table.
173 ELSIF p_db_action_ind = 'D' THEN
174 DELETE FROM GMD_FORMULA_SECURITY
175 WHERE organization_id = sec_prof_rec.organization_id
176 AND NVL(user_id,-1) = NVL(sec_prof_rec.user_id,-1)
177 AND NVL(responsibility_id,0) = NVL(sec_prof_rec.responsibility_id,0)
178 AND NVL(other_organization_id, -1) = NVL(sec_prof_rec.other_organization_id, -1)
179 AND formula_id = p_formula_id;
180 ELSE -- Update the Formula Security table with new data when db action is Update.
181 UPDATE GMD_FORMULA_SECURITY
182 SET organization_id = sec_prof_rec.organization_id,
183 user_id = sec_prof_rec.user_id,
184 responsibility_id = sec_prof_rec.responsibility_id,
185 other_organization_id = sec_prof_rec.other_organization_id,
186 last_update_date = SYSDATE,
187 last_updated_by = sec_prof_rec.last_updated_by
188 WHERE organization_id = before_sec_prof_rec.organization_id
189 AND NVL(user_id,-1) = NVL(before_sec_prof_rec.user_id,-1)
190 AND NVL(responsibility_id,0) = NVL(before_sec_prof_rec.responsibility_id,0)
191 AND NVL(other_organization_id, -1) = NVL(before_sec_prof_rec.other_organization_id, -1)
192 AND formula_id = p_formula_id ;
193 END IF;
194 END IF;
195 fnd_global.apps_initialize (x_user_id, x_resp_id , 552);
196 /*standard call to get msge cnt, and if cnt is 1, get mesg info*/
197 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
198
199 EXCEPTION
200 WHEN FND_API.G_EXC_ERROR THEN
201 fnd_global.apps_initialize (x_user_id, x_resp_id , 552);
202 X_return_code := SQLCODE;
203 x_return_status := FND_API.G_RET_STS_ERROR;
204 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
205
206 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
207 fnd_global.apps_initialize (x_user_id, x_resp_id , 552);
208 X_return_code := SQLCODE;
209 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
210 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
211
212 WHEN OTHERS THEN
213 fnd_global.apps_initialize (x_user_id, x_resp_id , 552);
214 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
215 FND_MESSAGE.SET_NAME('GMD', 'GMD_UNEXPECTED_ERROR');
216 FND_MESSAGE.SET_TOKEN('ERROR', SQLERRM);
217 FND_MSG_PUB.ADD;
218 FND_MSG_PUB.COUNT_AND_GET (P_count => x_msg_count,
219 P_data => x_msg_data);
220 END sec_prof_form ;
221 END GMD_FOR_SEC1;
222
[Home] [Help]