[Home] [Help]
PACKAGE BODY: APPS.HXC_PUBLIC_TEMP_GROUP_COMP_API
Source
1 Package Body hxc_public_temp_group_comp_api as
2 /* $Header: hxcptgapi.pkb 120.2 2006/02/16 02:03:16 mbhammar noship $ */
3 --
4 -- Package Variables
5 --
6 g_package VARCHAR2(31) := 'hxc_public_temp_group_comp_api';
7
8 g_entity_type VARCHAR2(21) := 'PUBLIC_TEMPLATE_GROUP';
9
10 g_field_separator VARCHAR2(1) := '|';
11
12 g_max_group_retrieve NUMBER(2) := 11;
13
14
15 -- --------------------------------------------------------------
16 -- |-------------<Insert Of Public Template Group>---------------|
17 -- --------------------------------------------------------------
18 PROCEDURE get_entity_group_id(
19 p_name IN VARCHAR2
20 ,p_entity_type IN VARCHAR2
21 ,p_entity_group_id OUT NOCOPY NUMBER
22 ,p_description IN VARCHAR2
23 ,p_business_group_id IN NUMBER
24 ,p_legislation_code IN VARCHAR2
25 )
26 IS
27 CURSOR csr_group_name_exists IS
28 SELECT 'error'
29 FROM dual
30 WHERE EXISTS (
31 SELECT 'x'
32 FROM hxc_entity_groups heg
33 WHERE heg.name = p_name
34 and heg.entity_type = 'PUBLIC_TEMPLATE_GROUP'
35 and heg.business_group_id = p_business_group_id);
36
37 l_object_version_number HXC_ENTITY_GROUPS.OBJECT_VERSION_NUMBER%TYPE;
38 l_error VARCHAR2(5) := NULL;
39
40 BEGIN
41 OPEN csr_group_name_exists;
42 FETCH csr_group_name_exists INTO l_error;
43 CLOSE csr_group_name_exists;
44
45 IF l_error IS NOT NULL
46 THEN
47 p_entity_group_id := -1 ;
48 ELSE
49 hxc_heg_ins.ins
50 (p_name => p_name
51 ,p_entity_type => p_entity_type
52 ,p_entity_group_id => p_entity_group_id
53 ,p_object_version_number => l_object_version_number
54 ,p_description => p_description
55 ,p_business_group_id => p_business_group_id
56 ,p_legislation_code => p_legislation_code
57 );
58 END IF;
59 END get_entity_group_id;
60
61
62 -- --------------------------------------------------------------
63 -- |-------------<Insert Of Public Template Group Comps>---------|
64 -- --------------------------------------------------------------
65
66 PROCEDURE insert_public_temp_grp_comp(
67 p_entity_group_id IN NUMBER
68 ,p_entity_id IN NUMBER
69 ,p_attribute1 IN VARCHAR2
70 ,p_attribute_category IN VARCHAR2
71 )
72 IS
73 l_object_version_number HXC_ENTITY_GROUP_COMPS.OBJECT_VERSION_NUMBER%TYPE;
74 l_entity_group_comp_id HXC_ENTITY_GROUP_COMPS.ENTITY_GROUP_COMP_ID%TYPE;
75 l_attribute2 HXC_ENTITY_GROUP_COMPS.ATTRIBUTE2%TYPE;
76 l_attribute3 HXC_ENTITY_GROUP_COMPS.ATTRIBUTE3%TYPE;
77 l_attribute4 HXC_ENTITY_GROUP_COMPS.ATTRIBUTE4%TYPE;
78 l_attribute5 HXC_ENTITY_GROUP_COMPS.ATTRIBUTE5%TYPE;
79 l_attribute6 HXC_ENTITY_GROUP_COMPS.ATTRIBUTE6%TYPE;
80 l_attribute7 HXC_ENTITY_GROUP_COMPS.ATTRIBUTE7%TYPE;
81 l_attribute8 HXC_ENTITY_GROUP_COMPS.ATTRIBUTE8%TYPE;
82 l_attribute9 HXC_ENTITY_GROUP_COMPS.ATTRIBUTE9%TYPE;
83 l_attribute10 HXC_ENTITY_GROUP_COMPS.ATTRIBUTE10%TYPE;
84 l_attribute11 HXC_ENTITY_GROUP_COMPS.ATTRIBUTE11%TYPE;
85 l_attribute12 HXC_ENTITY_GROUP_COMPS.ATTRIBUTE12%TYPE;
86 l_attribute13 HXC_ENTITY_GROUP_COMPS.ATTRIBUTE13%TYPE;
87 l_attribute14 HXC_ENTITY_GROUP_COMPS.ATTRIBUTE14%TYPE;
88 l_attribute15 HXC_ENTITY_GROUP_COMPS.ATTRIBUTE15%TYPE;
89 l_attribute16 HXC_ENTITY_GROUP_COMPS.ATTRIBUTE16%TYPE;
90 l_attribute17 HXC_ENTITY_GROUP_COMPS.ATTRIBUTE17%TYPE;
91 l_attribute18 HXC_ENTITY_GROUP_COMPS.ATTRIBUTE18%TYPE;
92 l_attribute19 HXC_ENTITY_GROUP_COMPS.ATTRIBUTE19%TYPE;
93 l_attribute20 HXC_ENTITY_GROUP_COMPS.ATTRIBUTE20%TYPE;
94 l_attribute21 HXC_ENTITY_GROUP_COMPS.ATTRIBUTE21%TYPE;
95 l_attribute22 HXC_ENTITY_GROUP_COMPS.ATTRIBUTE22%TYPE;
96 l_attribute23 HXC_ENTITY_GROUP_COMPS.ATTRIBUTE23%TYPE;
97 l_attribute24 HXC_ENTITY_GROUP_COMPS.ATTRIBUTE24%TYPE;
98 l_attribute25 HXC_ENTITY_GROUP_COMPS.ATTRIBUTE25%TYPE;
99 l_attribute26 HXC_ENTITY_GROUP_COMPS.ATTRIBUTE26%TYPE;
100 l_attribute27 HXC_ENTITY_GROUP_COMPS.ATTRIBUTE27%TYPE;
101 l_attribute28 HXC_ENTITY_GROUP_COMPS.ATTRIBUTE28%TYPE;
102 l_attribute29 HXC_ENTITY_GROUP_COMPS.ATTRIBUTE29%TYPE;
103 l_attribute30 HXC_ENTITY_GROUP_COMPS.ATTRIBUTE30%TYPE;
104
105 CURSOR csr_entity_comp_exists IS
106 SELECT 'error'
107 FROM dual
108 WHERE EXISTS (
109 SELECT 'x'
110 FROM hxc_entity_group_comps egc
111 WHERE egc.entity_group_id = p_entity_group_id
112 AND egc.entity_type = 'PUBLIC_TEMPLATE_GROUP'
113 AND egc.entity_id = p_entity_id );
114
115 CURSOR csr_exists_dynamic_comps IS
116 SELECT 'error'
117 FROM dual
118 WHERE EXISTS (
119 SELECT 'x'
120 FROM hxc_entity_group_comps egc
121 WHERE egc.entity_group_id = p_entity_group_id
122 AND egc.entity_type = 'PUBLIC_TEMPLATE_GROUP'
123 AND egc.attribute1 = p_attribute1 );
124
125
126 l_error VARCHAR2(5) := NULL;
127
128 BEGIN
129
130 IF (p_entity_id = -1 )
131 THEN
132 OPEN csr_exists_dynamic_comps;
133 FETCH csr_exists_dynamic_comps INTO l_error;
134 CLOSE csr_exists_dynamic_comps;
135
136 ELSE
137 OPEN csr_entity_comp_exists;
138 FETCH csr_entity_comp_exists INTO l_error;
139 CLOSE csr_entity_comp_exists;
140 END IF;
141
142 IF l_error IS NULL
143 THEN
144 hxc_egc_ins.ins
145 (p_effective_date => sysdate
146 ,p_entity_group_id => p_entity_group_id
147 ,p_entity_id => p_entity_id
148 ,p_entity_type => 'PUBLIC_TEMPLATE_GROUP'
149 ,p_attribute_category => p_attribute_category
150 ,p_attribute1 => p_attribute1
151 ,p_attribute2 => l_attribute2
152 ,p_attribute3 => l_attribute3
153 ,p_attribute4 => l_attribute4
154 ,p_attribute5 => l_attribute5
155 ,p_attribute6 => l_attribute6
156 ,p_attribute7 => l_attribute7
157 ,p_attribute8 => l_attribute8
158 ,p_attribute9 => l_attribute9
159 ,p_attribute10 => l_attribute10
160 ,p_attribute11 => l_attribute11
161 ,p_attribute12 => l_attribute12
162 ,p_attribute13 => l_attribute13
163 ,p_attribute14 => l_attribute14
164 ,p_attribute15 => l_attribute15
165 ,p_attribute16 => l_attribute16
166 ,p_attribute17 => l_attribute17
167 ,p_attribute18 => l_attribute18
168 ,p_attribute19 => l_attribute19
169 ,p_attribute20 => l_attribute20
170 ,p_attribute21 => l_attribute21
171 ,p_attribute22 => l_attribute22
172 ,p_attribute23 => l_attribute23
173 ,p_attribute24 => l_attribute24
174 ,p_attribute25 => l_attribute25
175 ,p_attribute26 => l_attribute26
176 ,p_attribute27 => l_attribute27
177 ,p_attribute28 => l_attribute28
178 ,p_attribute29 => l_attribute29
179 ,p_attribute30 => l_attribute30
180 ,p_entity_group_comp_id => l_entity_group_comp_id
181 ,p_object_version_number => l_object_version_number
182 ,p_called_from_form => null
183 );
184 END IF;
185 END insert_public_temp_grp_comp;
186
187
188 -- --------------------------------------------------------------
189 -- |-------------<Delete Of Public Template Group >-------------|
190 -- --------------------------------------------------------------
191
192 PROCEDURE del_entity_group_rec(
193 p_entity_group_id IN NUMBER,
194 p_business_group_id IN NUMBER,
195 p_attached_pref_name OUT NOCOPY VARCHAR2
196 )
197 IS
198 l_object_version_number HXC_ENTITY_GROUPS.OBJECT_VERSION_NUMBER%TYPE;
199 l_attached_pref_name VARCHAR2(325) := null;
200
201 CURSOR csr_get_ovn IS
202 SELECT
203 object_version_number
204 FROM hxc_entity_groups heg
205 WHERE entity_group_id = p_entity_group_id
206 AND heg.business_group_id = p_business_group_id;
207
208 BEGIN
209
210 l_attached_pref_name := public_temp_group_list(
211 p_entity_group_id ,
212 p_business_group_id
213 );
214 p_attached_pref_name := l_attached_pref_name;
215
216 IF(l_attached_pref_name IS NULL)
217 THEN
218 OPEN csr_get_ovn;
219 FETCH csr_get_ovn INTO l_object_version_number;
220 CLOSE csr_get_ovn;
221
222 hxc_heg_del.del
223 (p_entity_group_id => p_entity_group_id
224 ,p_object_version_number => l_object_version_number
225 );
226 END IF;
227 END del_entity_group_rec;
228
229
230 -- --------------------------------------------------------------
231 -- |-------------<Delete Of Public Template Group Comps>--------|
232 -- --------------------------------------------------------------
233
234 PROCEDURE del_entity_group_comp_rec(
235 p_entity_group_id IN NUMBER
236 ,p_entity_id IN VARCHAR2
237 )
238 IS
239 l_object_version_number HXC_ENTITY_GROUP_COMPS.OBJECT_VERSION_NUMBER%TYPE;
240 l_entity_group_comp_id HXC_ENTITY_GROUP_COMPS.ENTITY_GROUP_COMP_ID%TYPE;
241 l_template_code VARCHAR2(5);
242
243 CURSOR csr_get_entity_comp_id IS
244 SELECT
245 object_version_number,
246 entity_group_comp_id
247 FROM
248 hxc_entity_group_comps egc
249 WHERE
250 entity_group_id = p_entity_group_id
251 AND entity_id = TO_NUMBER(p_entity_id) ;
252
253 CURSOR csr_get_dynamic_entity_comp_id IS
254 SELECT
255 object_version_number,
256 entity_group_comp_id
257 FROM
258 hxc_entity_group_comps egc
259 WHERE
260 entity_group_id = p_entity_group_id
261 AND attribute1 = p_entity_id ;
262
263 BEGIN
264
265 l_template_code := substr(p_entity_id,1,4);
266
267 IF((l_template_code = 'APP|') OR (l_template_code = 'SYS|'))
268 THEN
269 OPEN csr_get_dynamic_entity_comp_id;
270 FETCH csr_get_dynamic_entity_comp_id INTO l_object_version_number, l_entity_group_comp_id;
271 CLOSE csr_get_dynamic_entity_comp_id;
272
273 ELSE
274 OPEN csr_get_entity_comp_id;
275 FETCH csr_get_entity_comp_id INTO l_object_version_number, l_entity_group_comp_id;
276 CLOSE csr_get_entity_comp_id;
277 END IF;
278
279 IF l_entity_group_comp_id IS NOT NULL
280 THEN
281 hxc_egc_del.del
282 (p_entity_group_comp_id => l_entity_group_comp_id
283 ,p_object_version_number => l_object_version_number
284 );
285 END IF;
286 END del_entity_group_comp_rec;
287
288
289 -- --------------------------------------------------------------
290 -- |------------<Update Of Public Template Group Comps>---------|
291 -- --------------------------------------------------------------
292
293 PROCEDURE update_public_temp_grp_comp(
294 p_entity_group_id IN NUMBER
295 ,p_entity_id IN HXC_TEMPLATE_ID_TABLE
296 )
297 IS
298 l_count NUMBER;
299 l_entity_id VARCHAR2(50);
300
301 BEGIN
302 l_count:=p_entity_id.first;
303
304 LOOP EXIT WHEN NOT p_entity_id.EXISTS(l_count) ;
305
306 l_entity_id := p_entity_id(l_count).entity_id;
307 del_entity_group_comp_rec(p_entity_group_id,l_entity_id);
308 l_count:=p_entity_id.NEXT(l_count);
309 END LOOP;
310 END update_public_temp_grp_comp;
311
312
313 -- --------------------------------------------------------------
314 -- |-------------<Update Of Public Template Group >-------------|
315 -- --------------------------------------------------------------
316
317 PROCEDURE update_entity_group_rec(
318 p_entity_group_id IN OUT NOCOPY NUMBER
319 ,p_name IN VARCHAR2
320 ,p_description IN VARCHAR2
321 )
322 IS
323 l_object_version_number HXC_ENTITY_GROUPS.OBJECT_VERSION_NUMBER%TYPE;
324 l_business_group_id HXC_ENTITY_GROUPS.BUSINESS_GROUP_ID%TYPE;
325 l_legislation_code HXC_ENTITY_GROUPS.LEGISLATION_CODE%TYPE;
326 l_name HXC_ENTITY_GROUPS.NAME%TYPE;
327
328 CURSOR csr_get_entity_detail IS
329 SELECT
330 object_version_number
331 ,business_group_id
332 ,legislation_code
333 ,name
334 FROM
335 hxc_entity_groups heg
336 WHERE
337 entity_group_id = p_entity_group_id;
338
339 CURSOR csr_exist_template_name(l_business_group_id in HXC_ENTITY_GROUPS.LEGISLATION_CODE%TYPE) IS
340 SELECT 'error'
341 FROM dual
342 WHERE EXISTS (
343 SELECT 'x'
344 FROM hxc_entity_groups heg
345 WHERE heg.name = p_name
346 and heg.entity_type = 'PUBLIC_TEMPLATE_GROUP'
347 and heg.business_group_id = l_business_group_id);
348
349 l_error VARCHAR2(5) := NULL;
350
351
352 BEGIN
353
354 OPEN csr_get_entity_detail;
355 FETCH csr_get_entity_detail INTO l_object_version_number, l_business_group_id, l_legislation_code, l_name ;
356 CLOSE csr_get_entity_detail;
357
358 IF p_name <> l_name
359 THEN
360 OPEN csr_exist_template_name(l_business_group_id);
361 FETCH csr_exist_template_name into l_error;
362 CLOSE csr_exist_template_name;
363 END IF;
364
365 IF l_error IS NOT NULL
366 THEN
367 p_entity_group_id := -1 ;
368 ELSE
369 hxc_heg_upd.upd
370 (p_entity_group_id => p_entity_group_id
371 ,p_object_version_number => l_object_version_number
372 ,p_name => p_name
373 ,p_entity_type => 'PUBLIC_TEMPLATE_GROUP'
374 ,p_description => p_description
375 ,p_business_group_id => l_business_group_id
376 ,p_legislation_code => l_legislation_code
377 );
378 END IF;
379
380 END update_entity_group_rec;
381
382 -- --------------------------------------------------------------
383 -- |----------<Create API Of Public Template Group Comp >-------|
384 -- --------------------------------------------------------------
385
386 PROCEDURE create_public_temp_grp_comp(
387 p_entity_group_id IN NUMBER
388 ,p_entity_id IN HXC_TEMPLATE_ID_TABLE
389 )
390 IS
391 l_error VARCHAR2(5) := NULL;
392 l_count NUMBER;
393 l_entity_id HXC_ENTITY_GROUP_COMPS.ENTITY_ID%TYPE;
394 l_template_id VARCHAR2(50);
395 l_template_code VARCHAR2(10);
396 BEGIN
397
398 l_count:=p_entity_id.first;
399 LOOP EXIT WHEN NOT p_entity_id.EXISTS(l_count) ;
400
401 l_template_id := p_entity_id(l_count).entity_id;
402 l_template_code := substr(l_template_id,1,4);
403
404 IF((l_template_code = 'APP|') OR (l_template_code = 'SYS|'))
405 THEN
406 insert_public_temp_grp_comp(p_entity_group_id, -1, l_template_id, l_template_id);
407 ELSE
408 l_entity_id := TO_NUMBER(l_template_id);
409 insert_public_temp_grp_comp(p_entity_group_id, l_entity_id, null, 'PUBLIC_TEMPLATE');
410 END IF;
411
412 l_count:=p_entity_id.NEXT(l_count);
413
414 END LOOP;
415 END create_public_temp_grp_comp;
416
417 -- --------------------------------------------------------------------------
418 -- |----------< Listing the Preferences which are attached to Group >-------|
419 -- --------------------------------------------------------------------------
420
421 FUNCTION public_temp_group_list(
422 p_public_template_group_id IN NUMBER,
423 p_business_group_id IN NUMBER
424 )
425 RETURN VARCHAR2
426 IS
427 l_public_temp_group_list VARCHAR2(1000) := NULL;
428 l_temp_pref_name HXC_PREF_HIERARCHIES_V.PREF_HIERARCHY%TYPE;
429 -- l_public_template_group_id VARCHAR2(10);
430
431
432 CURSOR csr_public_temp_group_pref IS
433 SELECT
434 name PREF_HIERARCHY
435 FROM
436 HXC_PREF_HIERARCHIES
437 WHERE
438 attribute_category = 'TC_W_PUBLIC_TEMPLATE'
439 AND business_group_id = p_business_group_id
440 AND
441 (attribute1 = p_public_template_group_id OR
442 attribute2 = p_public_template_group_id OR
443 attribute3 = p_public_template_group_id OR
444 attribute4 = p_public_template_group_id OR
445 attribute5 = p_public_template_group_id OR
446 attribute6 = p_public_template_group_id OR
447 attribute7 = p_public_template_group_id OR
448 attribute8 = p_public_template_group_id OR
449 attribute9 = p_public_template_group_id OR
450 attribute10 =p_public_template_group_id)
451 AND ROWNUM < g_max_group_retrieve ;
452
453 BEGIN
454
455 OPEN csr_public_temp_group_pref;
456 LOOP
457 FETCH csr_public_temp_group_pref INTO l_temp_pref_name;
458 EXIT WHEN csr_public_temp_group_pref%NOTFOUND;
459
460
461 l_public_temp_group_list := l_public_temp_group_list||
462 l_temp_pref_name||g_field_separator;
463
464 END LOOP;
465 CLOSE csr_public_temp_group_pref;
466
467
468 RETURN substr(l_public_temp_group_list,1,length(l_public_temp_group_list)-1);
469 END public_temp_group_list;
470
471 -- ----------------------------------------------------------------------------------
472 -- |----------< Checks whether deletion of public template is allowed >-------|
473 -- ----------------------------------------------------------------------------------
474
475 FUNCTION can_delete_public_template (p_template_id in hxc_time_building_blocks.time_building_block_id%type
476 ) RETURN VARCHAR2 IS
477
478 CURSOR cur_attached_public_temp_grps(p_template_id in hxc_time_building_blocks.time_building_block_id%type)
479 IS
480 SELECT heg.NAME FROM hxc_entity_groups heg ,
481 hxc_entity_group_comps hegc
482 WHERE
483 heg.ENTITY_TYPE = 'PUBLIC_TEMPLATE_GROUP'
484 and heg.entity_group_id =hegc.entity_group_id
485 and hegc.entity_id = p_template_id and rownum<g_max_group_retrieve;
486
487 l_template_grp_name varchar2(150);
488 l_attached_groups VARCHAR2(1500);
489 BEGIN
490
491 l_attached_groups := NULL;
492
493 open cur_attached_public_temp_grps(p_template_id);
494 loop
495 fetch cur_attached_public_temp_grps into l_template_grp_name;
496 exit when cur_attached_public_temp_grps%notfound;
497 IF l_attached_groups IS NULL THEN
498 l_attached_groups := l_template_grp_name;
499 ELSE
500 l_attached_groups := l_attached_groups||', '||l_template_grp_name;
501 END IF;
502 end loop;
503
504 return l_attached_groups;
505
506 END can_delete_public_template;
507
508 END hxc_public_temp_group_comp_api;