DBA Data[Home] [Help]

PACKAGE BODY: APPS.HXC_SECURITY

Source


1 PACKAGE BODY hxc_security AS
2 /* $Header: hxcaddsec.pkb 120.7 2007/12/14 14:47:12 mikarthi noship $ */
3 
4 type block_list is table of boolean index by binary_integer;
5 
6 c_sec               VARCHAR2(8) := 'SECURITY';
7 c_sec_offset        NUMBER      := 1000000;
8 g_debug		    BOOLEAN	:= hr_utility.debug_enabled;
9 
10 Function isTimecardForProjects
11            (p_application_set_id in hxc_entity_group_comps.entity_group_id%type)
12            return boolean is
13   cursor c_projects_in_app_set
14            (p_application_set_id in hxc_entity_group_comps.entity_group_id%type)
15            is
16    select 1
17      from hxc_entity_group_comps egc,
18           hxc_time_recipients tr
19     where egc.entity_group_id = p_application_set_id
20       and egc.entity_id = tr.time_recipient_id
21       and tr.name = 'Projects';
22 
23   l_dummy number;
24 
25 Begin
26   open c_projects_in_app_set(p_application_set_id);
27   fetch c_projects_in_app_set into l_dummy;
28   if(c_projects_in_app_set%found) then
29     close c_projects_in_app_set;
30     return true;
31   else
32     close c_projects_in_app_set;
33     return false;
34   end if;
35 -- Should never get here!
36   return true;
37 End isTimecardForProjects;
38 
39 Function checkOrgId
40   (p_blocks   in            hxc_block_table_type,
41    p_org_id   in            number,
42    p_messages in out nocopy hxc_message_table_type)
43   return boolean is
44   l_passed boolean := true;
45 Begin
46 
47   if(  (p_org_id is null)
48        AND
49          (isTimecardForProjects
50            (p_blocks(hxc_timecard_block_utils.find_active_timecard_index(p_blocks)).
51             application_set_id))) then
52     if(p_messages is null) then
53       p_messages := hxc_message_table_type();
54     end if;
55     l_passed := false;
56     p_messages.extend();
57     p_messages(p_messages.last) := hxc_message_type
58       ('HXC_366546_NO_ORG_ID',
59        hxc_timecard.c_error,
60        null,
61        null,
62        hxc_timecard.c_hxc,
63        null,
64        null,
65        null,
66        null,
67        null
68        );
69   end if;
70 
71   return l_passed;
72 
73 End checkOrgId;
74 
75 PROCEDURE obtain_block_list
76            (p_attributes     in            hxc_attribute_table_type
77            ,p_list              out nocopy block_list
78            ,p_count             out nocopy number
79            ,p_next_attribute    out nocopy number
80            ) IS
81 
82 l_index NUMBER;
83 
84 BEGIN
85 
86 l_index := p_attributes.first;
87 p_count := 0;
88 p_list.delete;
89 p_next_attribute := -2;
90 
91 LOOP
92   EXIT WHEN NOT p_attributes.exists(l_index);
93 
94   if(p_attributes(l_index).time_attribute_id <= p_next_attribute) then
95 
96     p_next_attribute := p_attributes(l_index).time_attribute_id -1;
97 
98   end if;
99 
100   if(p_attributes(l_index).attribute_category = c_sec) then
101 
102     p_list(p_attributes(l_index).building_block_id) := TRUE;
103     p_count := p_count +1;
104 
105   end if;
106 
107   l_index := p_attributes.next(l_index);
108 
109 END LOOP;
110 
111 --
112 -- Need to do this because of CLA (etc.) can create attributes after
113 -- we've added the security attributes, and we don't want the ids
114 -- to get mixed up.
115 --
116 p_next_attribute := p_next_attribute - c_sec_offset;
117 
118 END obtain_block_list;
119 
120 PROCEDURE add_attribute
121             (p_attributes         in out nocopy hxc_attribute_table_type
122             ,p_bg_id              in            varchar2
123             ,p_org_id             in            varchar2
124             ,p_user_id            in            number
125             ,p_resp_id            in            number
126             ,p_resp_appl_id       in            number
127             ,p_sec_grp_id         in            number
128             ,p_building_block_id  in            number
129             ,p_building_block_ovn in            number
130             ,p_time_attribute_id  in out nocopy number
131             ) is
132 
133 begin
134 
135 if(p_attributes is null) then
136   p_attributes := hxc_attribute_table_type();
137 end if;
138 
139 p_attributes.extend;
140 
141 p_attributes(p_attributes.last) :=
142     hxc_attribute_type
143       (p_time_attribute_id
144       ,p_building_block_id
145       ,c_sec
146       ,p_org_id
147       ,p_bg_id
148       ,p_user_id
149       ,p_resp_id
150       ,p_resp_appl_id
151       ,p_sec_grp_id
152       ,null
153       ,null
154       ,null
155       ,null
156       ,null
157       ,null
158       ,null
159       ,null
160       ,null
161       ,null
162       ,null
163       ,null
164       ,null
165       ,null
166       ,null
167       ,null
168       ,null
169       ,null
170       ,null
171       ,null
172       ,null
173       ,null
174       ,null
175       ,null
176       ,hxc_timecard_attribute_utils.get_bld_blk_info_type_id(c_sec)
177       ,1
178       ,'Y'
179       ,'N'
180       ,c_sec
181       ,'Y'
182       ,p_building_block_ovn
183       );
184 
185   p_time_attribute_id := p_time_attribute_id -1;
186 
187 end add_attribute;
188 
189 Function index_security_attributes
190            (p_attributes in hxc_attribute_table_type)
191          return block_list is
192 
193 l_attribute_index number;
194 l_sec_list block_list;
195 
196 Begin
197 
198 l_attribute_index := p_attributes.first;
199 Loop
200   Exit when not p_attributes.exists(l_attribute_index);
201   if(p_attributes(l_attribute_index).attribute_category = hxc_timecard.c_security_attribute) then
202     l_sec_list(p_attributes(l_attribute_index).building_block_id) := true;
203   end if;
204   l_attribute_index := p_attributes.next(l_attribute_index);
205 End Loop;
206 
207 return l_sec_list;
208 
209 End index_security_attributes;
210 
211 Function need_to_reattach_security
212            (p_block_id    in number
213            ,p_sec_list    in block_list
214            ) return boolean is
215 
216 Begin
217 
218   if(p_sec_list.exists(p_block_id)) then
219     return false;
220   else
221     return true;
222   end if;
223 
224 End need_to_reattach_security;
225 
226 Procedure reattach_security_attributes
227             (p_blocks     in            hxc_block_table_type
228             ,p_attributes in out nocopy hxc_attribute_table_type
229             ) is
230 
231 cursor c_sec_attribute
232          (p_block_id  in hxc_time_building_blocks.time_building_block_id%type
233          ,p_block_ovn in hxc_time_building_blocks.object_version_number%type
234          ) is
235   select ta.time_attribute_id
236         ,tau.time_building_block_id
237         ,ta.attribute_category
238         ,ta.attribute1
239         ,ta.attribute2
240         ,ta.attribute3
241         ,ta.attribute4
242         ,ta.attribute5
243         ,ta.attribute6
244         ,ta.attribute7
245         ,ta.attribute8
246         ,ta.attribute9
247         ,ta.attribute10
248         ,ta.attribute11
249         ,ta.attribute12
250         ,ta.attribute13
251         ,ta.attribute14
252         ,ta.attribute15
253         ,ta.attribute16
254         ,ta.attribute17
255         ,ta.attribute18
256         ,ta.attribute19
257         ,ta.attribute20
258         ,ta.attribute21
259         ,ta.attribute22
260         ,ta.attribute23
261         ,ta.attribute24
262         ,ta.attribute25
263         ,ta.attribute26
264         ,ta.attribute27
265         ,ta.attribute28
266         ,ta.attribute29
267         ,ta.attribute30
268         ,ta.object_version_number
269         ,tau.time_building_block_ovn
270         ,ta.bld_blk_info_type_id
271         ,bbit.bld_blk_info_type
272    from  hxc_time_attributes ta
273         ,hxc_time_attribute_usages tau
274         ,hxc_bld_blk_info_types bbit
275   where  ta.time_attribute_id = tau.time_attribute_id
276     and  tau.time_building_block_id = p_block_id
277     and  tau.time_building_block_ovn = p_block_ovn
278     and  ta.attribute_category = hxc_timecard.c_security_attribute
279     and  ta.bld_blk_info_type_id = bbit.bld_blk_info_type_id;
280 
281 l_index number;
282 l_attribute_index number;
283 l_sec_attr c_sec_attribute%rowtype;
284 l_sec_list block_list;
285 
286 Begin
287 
288 l_sec_list := index_security_attributes(p_attributes);
289 
290 l_index := p_blocks.first;
291 
292 Loop
293   Exit When Not p_blocks.exists(l_index);
294     if(p_blocks(l_index).new = 'N') then
295       if(need_to_reattach_security(p_blocks(l_index).time_building_block_id,l_sec_list)) then
296         open c_sec_attribute(p_blocks(l_index).time_building_block_id
297                             ,p_blocks(l_index).object_version_number);
298         fetch c_sec_attribute into l_sec_attr;
299         if(c_sec_attribute%found) then
300            l_attribute_index := p_attributes.last + 1;
301            p_attributes.extend;
302            p_attributes(l_attribute_index)
303              := hxc_attribute_type
304                   (l_sec_attr.time_attribute_id,
305                    l_sec_attr.time_building_block_id,
306                    l_sec_attr.attribute_category,
307                    l_sec_attr.attribute1,
308                    l_sec_attr.attribute2,
309                    fnd_global.user_id,
310                    fnd_global.resp_id,
311                    l_sec_attr.attribute5,
312                    l_sec_attr.attribute6,
313                    l_sec_attr.attribute7,
314                    l_sec_attr.attribute8,
315                    l_sec_attr.attribute9,
316                    l_sec_attr.attribute10,
317                    l_sec_attr.attribute11,
318                    l_sec_attr.attribute12,
319                    l_sec_attr.attribute13,
320                    l_sec_attr.attribute14,
321                    l_sec_attr.attribute15,
322                    l_sec_attr.attribute16,
323                    l_sec_attr.attribute17,
324                    l_sec_attr.attribute18,
325                    l_sec_attr.attribute19,
326                    l_sec_attr.attribute20,
327                    l_sec_attr.attribute21,
328                    l_sec_attr.attribute22,
329                    l_sec_attr.attribute23,
330                    l_sec_attr.attribute24,
331                    l_sec_attr.attribute25,
332                    l_sec_attr.attribute26,
333                    l_sec_attr.attribute27,
334                    l_sec_attr.attribute28,
335                    l_sec_attr.attribute29,
336                    l_sec_attr.attribute30,
337                    l_sec_attr.bld_blk_info_type_id,
338                    l_sec_attr.object_version_number,
339                    'N', -- New
340                    'N', -- Changed
341                    l_sec_attr.bld_blk_info_type,
342                    'N', -- Process
343                    l_sec_attr.time_building_block_ovn
344                    );
345         end if;
346         close c_sec_attribute;
347       end if;
348     end if;
349   l_index := p_blocks.next(l_index);
350 End Loop;
351 
352 End reattach_security_attributes;
353 
354 PROCEDURE add_security_attribute
355            (p_blocks         in            hxc_block_table_type,
356             p_attributes     in out nocopy hxc_attribute_table_type,
357             p_timecard_props in            hxc_timecard_prop_table_type,
358             p_messages       in out nocopy hxc_message_table_type
359            ) IS
360 
361   l_list           block_list;
362   l_count          NUMBER;
363   l_next_attribute NUMBER;
364   l_index          NUMBER;
365   l_org_id         fnd_profile_option_values.profile_option_value%type;
366   l_bg_id          fnd_profile_option_values.profile_option_value%type;
367   l_defined        BOOLEAN;
368   l_test           BOOLEAN;
369   l_passed         BOOLEAN;
370   l_user_id        number;
371   l_resp_id        number;
372   l_resp_appl_id   number;
373   l_sec_grp_id     number;
374 
375 BEGIN
376 
377   g_debug:=hr_utility.debug_enabled;
378 
379   reattach_security_attributes
380     (p_blocks     => p_blocks
381     ,p_attributes => p_attributes
382     );
383 
384   obtain_block_list
385     (p_attributes     => p_attributes
386     ,p_list           => l_list
387     ,p_count          => l_count
388     ,p_next_attribute => l_next_attribute
389     );
390 
391   if(l_count <> p_blocks.count) then
392     --
393     -- We need to add to at least sec attr to one block
394     --
395 
396     l_org_id := hxc_timecard_properties.find_property_value
397       (p_timecard_props,
398        'ResourceOrgId',
399        null,
400        null,
401        sysdate
402        );
403 
404     l_passed := checkOrgId(p_blocks,l_org_id,p_messages);
405 
406     if(l_passed) then
407 
408       l_bg_id := fnd_profile.value('PER_BUSINESS_GROUP_ID');
409       l_user_id := fnd_global.user_id;
410       l_resp_id := fnd_global.resp_id;
411       l_resp_appl_id := fnd_global.resp_appl_id;
412       l_sec_grp_id := fnd_global.security_group_id;
413       --
414       -- Loop over the blocks, and when we have one that
415       -- does have a sec attribute, add it.
416       --
417 
418       l_index := p_blocks.first;
419 
420       LOOP
421         EXIT WHEN NOT p_blocks.exists(l_index);
422         if(l_count > 0) then
423           --
424           -- Check to see if this block has a security attribute
425           --
426           BEGIN
427             --
428             -- Check for a block in the structure
429             --
430             l_test := l_list(p_blocks(l_index).time_building_block_id);
431             --
432             -- If we get here, then the block was in the list
433             --
434           EXCEPTION
435             WHEN OTHERS then
436               --
437               -- If we get here, then the block wasn't in the list
438               -- add the attribute
439               --
440               add_attribute
441                 (p_attributes         => p_attributes,
442                  p_bg_id              => l_bg_id,
443                  p_org_id             => l_org_id,
444                  p_user_id            => l_user_id,
445                  p_resp_id            => l_resp_id,
446                  p_resp_appl_id       => l_resp_appl_id,
447                  p_sec_grp_id         => l_sec_grp_id,
448                  p_building_block_id  => p_blocks(l_index).time_building_block_id,
449                  p_building_block_ovn => p_blocks(l_index).object_version_number,
450                  p_time_attribute_id  => l_next_attribute
451                  );
452           END;
453         else
454           --
455           -- No blocks have the sec attr, add to this block anyway
456           --
457           add_attribute
458 		  (p_attributes         => p_attributes,
459 		   p_bg_id              => l_bg_id,
460 		   p_org_id             => l_org_id,
461 		   p_user_id            => l_user_id,
462 		   p_resp_id            => l_resp_id,
463 		   p_resp_appl_id       => l_resp_appl_id,
464 		   p_sec_grp_id         => l_sec_grp_id,
465 		   p_building_block_id  => p_blocks(l_index).time_building_block_id,
466 		   p_building_block_ovn => p_blocks(l_index).object_version_number,
467 		   p_time_attribute_id  => l_next_attribute
468                  );
469 
470         end if;-- Is there a list of blocks that already have the sec attribute
471 
472         l_index := p_blocks.next(l_index);
473 
474       END LOOP;
475 
476     end if; -- Did we pass the org id check?
477 
478   end if; -- Are any blocks missing security attributes?
479 
480 
481 END add_security_attribute;
482 
483 END hxc_security;