[Home] [Help]
PACKAGE BODY: APPS.MSC_ORG_SECURITY
Source
1 PACKAGE BODY MSC_org_security AS
2 /* $Header: MSCORGSB.pls 120.4 2010/12/21 09:38:45 spinniti ship $ */
3 PROCEDURE set_org_security(
4 p_selected_orgs IN varchar2
5 ,p_resp_id IN varchar2
6 ,p_more_orgs IN NUMBER DEFAULT NULL
7 ) IS
8 i number := 1;
9 v_len number;
10 one_len number;
11 org_id number;
12 inst_id number;
13 org_id1 number;
14 inst_id1 number;
15 org_inst_id varchar2(100); --5515434 bugfix
16 org_assigned number :=0;
17 record_exists number := 0;
18
19 -- the other columns have to be null for the record to be considered a
20 -- org security record
21
22 CURSOR current_orgs (p_resp_id varchar2) is
23 select organization_id, sr_instance_id
24 from msc_selected_orgs_v mso
25 where mso.responsibility_id = p_resp_id;
26
27 CURSOR org_assigned_c(
28 lorg_id number,
29 linst_id number) IS
30 select organization_id
31 from msc_selected_orgs_v mso
32 where mso.responsibility_id = trim(p_resp_id)
33 and mso.organization_id = lorg_id
34 and mso.sr_instance_id = linst_id;
35
36
37 CURSOR record_exists_c(
38 lorg_id1 number,
39 linst_id1 number) IS
40 select 1
41 from msc_org_access
42 where responsibility_id = to_number(trim(p_resp_id))
43 and organization_id = lorg_id1
44 and sr_instance_id = linst_id1;
45 cursor cur_rec_count is
46 select count(*)
47 from msc_org_access
48 where responsibility_id = to_number(trim(p_resp_id))
49 and sysdate <= nvl(effective_to_date,sysdate)
50 ;
51 l_dummy number;
52 BEGIN
53 -- terminate organizations that are in the table but have not been selected
54 -- to be assigned
55 IF p_more_orgs IS NULL or p_more_orgs=-1 THEN -- this parameter will be -1, if the length of p_selected_orgs is < 20000
56 OPEN current_orgs (p_resp_id);
57 LOOP
58 --dbms_output.put_line('starting loop');
59 FETCH current_orgs INTO org_id,
60 inst_id;
61 EXIT WHEN current_orgs%NOTFOUND;
62 --dbms_output.put_line('before creating org_inst');
63 org_inst_id := to_char(org_id) || ':' || to_char(inst_id);
64 --dbms_output.put_line('org_id =' || org_id);
65 --dbms_output.put_line('inst_id =' || inst_id);
66 --dbms_output.put_line('instr value ' ||
67 -- instr(p_selected_orgs, org_inst_id,1));
68 if instr(p_selected_orgs, org_inst_id,1) = 0 then
69 update_row(org_id,
70 inst_id,
71 to_number(p_resp_id),
72 724,
73 sysdate,
74 sysdate-1,
75 'REMOVE');
76 end if;
77 end loop;
78 close current_orgs;
79 end if; -- p_more_orgs is -1
80 --dbms_output.put_line('past current orgs cursor');
81 -- parse the p_selected_orgs, the format is '201:207;201:208;'
82 -- assign orgs that have been selected
83 v_len := length(p_selected_orgs);
84 while v_len > 1 loop
85
86 one_len := instr(p_selected_orgs,';',1,i+1)-
87 instr(p_selected_orgs,';',1 ,i)-1;
88
89 org_inst_id := trim(substr(p_selected_orgs,
90 instr(p_selected_orgs,';',1,i)+1,one_len));
91
92 org_id := to_number(
93 trim(substr(org_inst_id,1,
94 instr(org_inst_id,':',1)-1)));
95 org_id1 := org_id;
96 inst_id := to_number( trim(substr(org_inst_id,instr(
97 org_inst_id,':',1)+1)));
98 inst_id1 := inst_id;
99
100 i := i+1;
101 v_len := v_len - one_len-1;
102 --dbms_output.put_line('p_resp_id' || p_resp_id);
103 --dbms_output.put_line('org_id' || org_id);
104 --dbms_output.put_line('inst_id' || inst_id);
105 OPEN org_assigned_c(org_id, inst_id);
106 FETCH org_assigned_c into org_assigned;
107 --dbms_output.put_line('rule id is ' || org_assigned);
108 if ( org_assigned_c%notfound) then
109 --dbms_output.put_line('org not found ' || org_id || ' '|| inst_id);
110 -- determine if the record exists
111 -- if record exists then update the record
112 -- else insert record
113 OPEN record_exists_c(org_id, inst_id);
114 FETCH record_exists_c into record_exists;
115 --dbms_output.put_line('rule id is ' || org_assigned);
116 if ( record_exists_c%notfound) then
117 --dbms_output.put_line('record not found ' || org_id || ' '||
118 -- inst_id);
119 insert_row(org_id,
120 inst_id,
121 to_number(p_resp_id),
122 724,
123 sysdate,
124 to_date(null)
125 );
126 else
127 --dbms_output.put_line('record found ' || org_id || ' '|| inst_id);
128 update_row( org_id1,
129 inst_id1,
130 to_number(p_resp_id),
131 724,
132 sysdate,
133 to_date(null),
134 'ADD'
135 );
136 end if;
137 CLOSE record_exists_c;
138 --else
139 --dbms_output.put_line('found ' || org_id || ' '|| inst_id);
140 end if;
141 CLOSE org_assigned_c;
142 end loop ;
143 open cur_rec_count;
144 fetch cur_rec_count into l_dummy;
145 close cur_rec_count;
146 -- fnd_log.message(
147 -- need to add exception clause
148 Exception when others then
149 raise_application_error(-20000,sqlerrm||':'||
150 'parameters passed: ' ||' ' ||
151 'p_selected_orgs =>' || p_selected_orgs ||' ' ||
152 'p_resp_id=>' || p_resp_id);
153
154 END set_org_security;
155
156
157 procedure get_resp_id (p_resp IN varchar2,
158 p_resp_id OUT NoCopy varchar2) is
159 -- this procedure gets the responsibility id given the responsibility name
160 -- it returns '-1' if no responsibility exists with the given name in MSC
161 lquery_id number ;
162 cursor resp_c is
163 select resp.responsibility_id
164 from fnd_responsibility_tl resp
165 where resp.responsibility_name = p_resp;
166 begin
167 open resp_c;
168 fetch resp_c into p_resp_id;
169
170 if resp_c%notfound then
171 p_resp_id := '-1';
172 end if;
173 close resp_c;
174 Exception when others then
175 raise_application_error(-20000,sqlerrm||':'||
176 'parameter passed: ' ||' ' ||
177 p_resp);
178 end get_resp_id;
179
180
181 procedure insert_row (p_organization_id in number,
182 p_sr_instance_id in number,
183 p_responsibility_id in number,
184 p_resp_appl_id in number,
185 p_eff_from_date in date,
186 p_eff_to_date in date)
187
188 is
189 v_statement varchar2(4000);
190
191 cursor c_resp_appl_id is
192 select application_id
193 from fnd_responsibility
194 where responsibility_id = p_responsibility_id;
195
196 l_resp_appl_id number;
197 begin
198 open c_resp_appl_id;
199 fetch c_resp_appl_id into l_resp_appl_id;
200 close c_resp_appl_id;
201
202 if (l_resp_appl_id is null) then
203 l_resp_appl_id := p_resp_appl_id;
204 end if;
205
206 v_statement := 'insert into msc_org_access(' ||
207 'ORGANIZATION_ID' ||
208 ',SR_INSTANCE_ID' ||
209 ',RESPONSIBILITY_ID' ||
210 ',RESP_APPLICATION_ID' ||
211 ',EFFECTIVE_FROM_DATE' ||
212 ',EFFECTIVE_TO_DATE' ||
213 ',LAST_UPDATE_DATE' ||
214 ',LAST_UPDATED_BY' ||
215 ',CREATION_DATE' ||
216 ',CREATED_BY' ||
217 ',LAST_UPDATE_LOGIN' ||
218 ')' ||
219 'values ( ' ||
220 ' :1' ||
221 ',:2' ||
222 ',:3' ||
223 ',:4' ||
224 ',:5' ||
225 ',:6' ||
226 ',:7' ||
227 ',:8' ||
228 ',:9' ||
229 ',:10' ||
230 ',:11' ||
231 ')';
232 EXECUTE IMMEDIATE v_statement USING
233 p_organization_id,
234 p_sr_instance_id,
235 p_responsibility_id,
236 l_resp_appl_id,
237 p_eff_from_date,
238 p_eff_to_date,
239 sysdate,
240 fnd_global.user_id,
241 sysdate,
242 fnd_global.user_id,
243 fnd_global.user_id;
244
245 EXCEPTION
246 when others then
247 raise_application_error(-20000,sqlerrm||':'||v_statement ||
248 'p_organization_id' ||p_organization_id ||' ' ||
249 'p_sr_instance_id' ||p_sr_instance_id ||' ' ||
250 'p_responsibility_id' ||p_responsibility_id ||' ' ||
251 'p_resp_application_id' ||p_resp_appl_id ||' ' ||
252 'p_effective_from_date' ||p_eff_from_date ||' ' ||
253 'p_effective_to_date' || p_eff_to_date);
254 end insert_row;
255
256 procedure update_row (p_organization_id in number,
257 p_sr_instance_id in number,
258 p_responsibility_id in number,
259 p_resp_appl_id in number,
260 p_eff_from_date in date,
261 p_eff_to_date in date,
262 p_action varchar2)
263
264 is
265 v_statement varchar2(2000);
266
267 cursor c_resp_appl_id is
268 select application_id
269 from fnd_responsibility
270 where responsibility_id = p_responsibility_id;
271
272 l_resp_appl_id number;
273 begin
274
275 open c_resp_appl_id;
276 fetch c_resp_appl_id into l_resp_appl_id;
277 close c_resp_appl_id;
278
279 if (l_resp_appl_id is null) then
280 l_resp_appl_id := p_resp_appl_id;
281 end if;
282
283 v_statement := 'update msc_org_access set' ;
284 if p_action = 'ADD' then
285 v_statement := v_statement ||' effective_from_date= :1,';
286 end if;
287
288 v_statement := v_statement ||
289 ' effective_to_date= :2,' ||
290 ' LAST_UPDATE_DATE = :3,' ||
291 ' LAST_UPDATED_BY = :4,' ||
292 ' LAST_UPDATE_LOGIN= :5 ' ||
293 'where responsibility_id = :6 and ' ||
294 'organization_id = :7 and ' ||
295 'sr_instance_id = :8 and ' ||
296 'resp_application_id = :9';
297 if p_action='ADD' then
298 EXECUTE IMMEDIATE v_statement USING
299 p_eff_from_date,
300 p_eff_to_date,
301 sysdate,
302 fnd_global.user_id,
303 fnd_global.user_id,
304 p_responsibility_id,
305 p_organization_id,
306 p_sr_instance_id,
307 l_resp_appl_id;
308 else
309 EXECUTE IMMEDIATE v_statement USING
310 p_eff_to_date,
311 sysdate,
312 fnd_global.user_id,
313 fnd_global.user_id,
314 p_responsibility_id,
315 p_organization_id,
316 p_sr_instance_id,
317 l_resp_appl_id;
318 end if;
319 EXCEPTION
320 when others then
321 raise_application_error(-20000,sqlerrm||':'||v_statement||
322 'p_organization_id' ||p_organization_id ||' ' ||
323 'p_sr_instance_id' ||p_sr_instance_id ||' ' ||
324 'p_responsibility_id' ||p_responsibility_id ||' ' ||
325 'p_resp_application_id' ||p_resp_appl_id ||' ' ||
326 'p_effective_from_date' ||p_eff_from_date ||' ' ||
327 'p_effective_to_date' || p_eff_to_date);
328 end update_row;
329
330
331
332 end msc_org_security;