[Home] [Help]
PACKAGE BODY: APPS.RRS_HIERARCHY_INFO
Source
1 PACKAGE BODY RRS_HIERARCHY_INFO as
2 /* $Header: RRSGHDTB.pls 120.5 2011/03/03 21:47:38 sunarang noship $ */
3 ------------------------
4 --Get Hierarchy Header--
5 ------------------------
6
7 Procedure Get_Hierarchy_Header(
8 p_api_version IN number
9 ,p_hier_version_id IN number
10 ,x_hier_header_rec OUT NOCOPY rrs_hier_header_rec
11 ,x_return_status OUT NOCOPY varchar2
12 ,x_msg_count OUT NOCOPY number
13 ,x_msg_data OUT NOCOPY varchar2
14 ) is
15
16 BEGIN
17 IF x_msg_count IS NULL THEN
18 x_msg_count := 0;
19 END IF;
20
21 SELECT rrs_hier_header_rec(
22 RSGB.SITE_GROUP_ID,
23 RSGB.SITE_GROUP_TYPE_CODE,
24 RSGT.NAME,
25 RSGB.GROUP_PURPOSE_CODE,
26 LKUP.MEANING,
27 RSGT.DESCRIPTION,
28 RSGB.START_DATE,
29 RSGB.END_DATE)
30 INTO x_hier_header_rec
31 FROM RRS_SITE_GROUP_VERSIONS RSGV, RRS_SITE_GROUPS_B RSGB, RRS_SITE_GROUPS_TL RSGT, RRS_LOOKUPS_V LKUP
32 WHERE RSGT.LANGUAGE = userenv('LANG')
33 AND RSGV.SITE_GROUP_VERSION_ID = p_hier_version_id
34 AND RSGB.SITE_GROUP_ID = RSGV.SITE_GROUP_ID
35 AND RSGT.SITE_GROUP_ID = RSGB.SITE_GROUP_ID
36 AND 'RRS_HIERARCHY_PURPOSE' = LKUP.LOOKUP_TYPE(+)
37 AND RSGB.GROUP_PURPOSE_CODE = LKUP.LOOKUP_CODE(+);
38
39 x_return_status := G_RET_STS_SUCCESS;
40
41 EXCEPTION
42 WHEN OTHERS THEN
43 x_msg_data := 'RRS_HIERARCHY_INFO.Get_Hierarchy_Header: ' || dbms_utility.format_error_backtrace;
44 fnd_message.set_name('RRS', 'RRS_UNEXPECTED_ERROR');
45 fnd_msg_pub.add;
46 x_msg_count := x_msg_count + 1;
47 x_return_status := G_RET_STS_UNEXP_ERROR;
48 END;
49
50
51 Procedure Get_Hierarchy_Header(
52 p_api_version IN number
53 ,p_hier_id IN number
54 ,p_hier_version_number IN number
55 ,x_hier_header_rec OUT NOCOPY rrs_hier_header_rec
56 ,x_return_status OUT NOCOPY varchar2
57 ,x_msg_count OUT NOCOPY number
58 ,x_msg_data OUT NOCOPY varchar2
59 ) is
60
61 l_hier_version_number number;
62 l_hier_version_id number;
63
64 BEGIN
65 IF x_msg_count IS NULL THEN
66 x_msg_count := 0;
67 END IF;
68
69 l_hier_version_number := p_hier_version_number;
70
71 --if user does not provide version number, api will pick up the latest version number
72 IF l_hier_version_number IS NULL THEN
73 SELECT MAX(VERSION_NUMBER)
74 INTO l_hier_version_number
75 FROM RRS_SITE_GROUP_VERSIONS
76 WHERE SITE_GROUP_ID = p_hier_id;
77 END IF;
78
79 SELECT SITE_GROUP_VERSION_ID
80 INTO l_hier_version_id
81 FROM RRS_SITE_GROUP_VERSIONS
82 WHERE SITE_GROUP_ID = p_hier_id
83 AND VERSION_NUMBER = l_hier_version_number;
84
85 Get_Hierarchy_Header(p_api_version => p_api_version
86 ,p_hier_version_id => l_hier_version_id
87 ,x_hier_header_rec => x_hier_header_rec
88 ,x_return_status => x_return_status
89 ,x_msg_count => x_msg_count
90 ,x_msg_data => x_msg_data);
91 EXCEPTION
92 WHEN OTHERS THEN
93 x_msg_data := 'RRS_HIERARCHY_INFO.Get_Hierarchy_Header: ' || dbms_utility.format_error_backtrace;
94 fnd_message.set_name('RRS', 'RRS_UNEXPECTED_ERROR');
95 fnd_msg_pub.add;
96 x_msg_count := x_msg_count + 1;
97 x_return_status := G_RET_STS_UNEXP_ERROR;
98 END;
99
100
101
102 Procedure Get_Hierarchy_Header(
103 p_api_version IN number
104 ,p_hier_name IN varchar2
105 ,x_hier_header_rec OUT NOCOPY rrs_hier_header_rec
106 ,x_return_status OUT NOCOPY varchar2
107 ,x_msg_count OUT NOCOPY number
108 ,x_msg_data OUT NOCOPY varchar2
109 ) is
113 --need lookup site group purpose meaning
110
111 l_hier_id number;
112
114 BEGIN
115 IF x_msg_count IS NULL THEN
116 x_msg_count := 0;
117 END IF;
118
119 SELECT SITE_GROUP_ID
120 INTO l_hier_id
121 FROM RRS_SITE_GROUPS_VL
122 WHERE NAME = p_hier_name;
123
124 Get_Hierarchy_Header(p_api_version => p_api_version
125 ,p_hier_id => l_hier_id
126 ,p_hier_version_number => NULL
127 ,x_hier_header_rec => x_hier_header_rec
128 ,x_return_status => x_return_status
129 ,x_msg_count => x_msg_count
130 ,x_msg_data => x_msg_data);
131
132
133 EXCEPTION
134 WHEN NO_DATA_FOUND THEN
135 fnd_message.set_name('RRS', 'RRS_NO_HIER_FOUND');
136 fnd_message.set_token('HIERARCHY_NAME', p_hier_name);
137 fnd_msg_pub.add;
138 x_msg_count := x_msg_count + 1;
139 x_return_status := G_RET_STS_ERROR;
140 WHEN OTHERS THEN
141 x_msg_data := 'RRS_HIERARCHY_INFO.Get_Hierarchy_Header: ' || dbms_utility.format_error_backtrace;
142 fnd_message.set_name('RRS', 'RRS_UNEXPECTED_ERROR');
143 fnd_msg_pub.add;
144 x_msg_count := x_msg_count + 1;
145 x_return_status := G_RET_STS_UNEXP_ERROR;
146 END;
147
148
149 -------------------------
150 --Get Hierarchy Members--
151 -------------------------
152
153 -- For bug fix 9011360
154 -- Add SiteGroupId condition to the main SQL query, because the SiteGroupId is the first index column. This will fix the performance issue.
155 -- jijiao 10/13/2009
156 Procedure Get_Hierarchy_Members(
157 p_api_version IN number
158 ,p_hier_version_id IN number
159 ,p_parent_member_type IN varchar2
160 ,p_parent_member_id_num IN varchar2
161 ,x_hier_members_tab OUT NOCOPY rrs_hier_members_tab
162 ,x_return_status OUT NOCOPY varchar2
163 ,x_msg_count OUT NOCOPY number
164 ,x_msg_data OUT NOCOPY varchar2
165 ) is
166
167 l_site_group_id rrs_site_groups_b.site_group_id%TYPE;
168 l_site_group_version_id rrs_site_group_versions.site_group_version_id%TYPE;
169 l_parent_id rrs_site_group_members.parent_member_id%TYPE;
170
171 x_hier_header_rec rrs_hier_header_rec;
172
173
174 BEGIN
175 IF x_msg_count IS NULL THEN
176 x_msg_count := 0;
177 END IF;
178
179 -- query the site group id from version id
180 -- For Bug Fix 9011360 - jijiao 10/13/2009
181 SELECT SITE_GROUP_ID
182 INTO l_site_group_id
183 FROM RRS_SITE_GROUP_VERSIONS
184 WHERE SITE_GROUP_VERSION_ID = p_hier_version_id;
185
186
187 --query the members in the whole hierarchy
188 --parent is the root so parent id is -1
189 IF p_parent_member_type IS NULL AND p_parent_member_id_num IS NULL THEN
190 l_parent_id := -1;
191
192 --query a sub-hierarchy under the speicific parent
193 --query the parent id according to the parent identification number input
194 ELSIF p_parent_member_type IS NOT NULL AND p_parent_member_id_num IS NOT NULL THEN
195 BEGIN
196 IF UPPER(p_parent_member_type) = 'SITE' THEN
197 SELECT SITE_ID
198 INTO l_parent_id
199 FROM RRS_SITES_B
200 WHERE SITE_IDENTIFICATION_NUMBER = p_parent_member_id_num;
201 ELSIF UPPER(p_parent_member_type) = 'NODE' THEN
202 SELECT SITE_GROUP_NODE_ID
203 INTO l_parent_id
204 FROM RRS_SITE_GROUP_NODES_B
205 WHERE NODE_IDENTIFICATION_NUMBER = p_parent_member_id_num;
206 END IF;
207
208 IF l_parent_id IS NULL THEN
209 fnd_message.set_name('RRS', 'RRS_HIER_NO_PARENT_MBR_FOUND');
210 fnd_message.set_token('PARENT_TYPE', p_parent_member_type);
211 fnd_message.set_token('PARENT_ID_NUM', p_parent_member_id_num);
212 fnd_message.set_token('HIERARCHY_NAME', p_hier_version_id);
213 fnd_msg_pub.add;
214 x_msg_count := x_msg_count + 1;
215 x_return_status := G_RET_STS_ERROR;
216 END IF;
217 EXCEPTION
218 WHEN NO_DATA_FOUND THEN
219 fnd_message.set_name('RRS', 'RRS_HIER_NO_PARENT_MBR_FOUND');
220 fnd_message.set_token('PARENT_TYPE', p_parent_member_type);
221 fnd_message.set_token('PARENT_ID_NUM', p_parent_member_id_num);
222 fnd_message.set_token('HIERARCHY_NAME', p_hier_version_id);
223 fnd_msg_pub.add;
224 x_msg_count := x_msg_count + 1;
225 x_return_status := G_RET_STS_ERROR;
226 END;
227 ELSE
228 fnd_message.set_name('RRS', 'RRS_MISS_PARENT_TYPE_OR_ID_NUM');
229 fnd_msg_pub.add;
230 x_msg_count := x_msg_count + 1;
231 x_return_status := G_RET_STS_ERROR;
232 END IF;
233
234 -- For bug fix 9011360
235 -- Add SiteGroupId condition to the main SQL query, because the SiteGroupId is the first index column. This will fix the performance issue.
236 -- jijiao 10/13/2009
237 IF l_parent_id IS NOT NULL THEN
238 SELECT rrs_hier_members_rec(
239 SiteGroupMembers.SITE_GROUP_ID,
240 SiteGroupMembers.SITE_GROUP_VERSION_ID,
241 DECODE(SiteGroupMembers.PARENT_MEMBER_ID, -1, NULL, DECODE(SiteGroupNodes2.SITE_GROUP_NODE_ID, NULL, Sites2.SITE_ID,
242 SiteGroupNodes2.SITE_GROUP_NODE_ID)),
243 DECODE(SiteGroupMembers.PARENT_MEMBER_ID, -1, NULL, DECODE(SiteGroupNodes2.SITE_GROUP_NODE_ID, NULL, Sites2.SITE_IDENTIFICATION_NUMBER,
244 SiteGroupNodes2.NODE_IDENTIFICATION_NUMBER)),
245 DECODE(SiteGroupMembers.PARENT_MEMBER_ID, -1, NULL, DECODE(SiteGroupNodes2.NAME, NULL, Sites2.NAME,SiteGroupNodes2.NAME)),
246 DECODE(SiteGroupMembers.PARENT_MEMBER_ID, -1, NULL, DECODE(SiteGroupNodes2.SITE_GROUP_NODE_ID, NULL, 'SITE', 'NODE')),
247 DECODE(SiteGroupMembers.PARENT_MEMBER_ID, -1, NULL,
251 WHERE RSU.SITE_ID = SiteGroupMembers.PARENT_MEMBER_ID AND RSU.IS_PRIMARY_FLAG = 'Y' AND LKUP.LOOKUP_TYPE = 'PARTY_SITE_USE_CODE' AND RSU.SITE_USE_TYPE_CODE = LKUP.LOOKUP_CODE),
248 DECODE(SiteGroupNodes2.SITE_GROUP_NODE_ID, NULL,
249 (SELECT LKUP.MEANING
250 FROM RRS_SITE_USES RSU, AR_LOOKUPS LKUP
252 (SELECT LKUP.MEANING
253 FROM RRS_SITE_GROUP_NODES_B RSGNB, RRS_LOOKUPS_V LKUP
254 WHERE RSGNB.SITE_GROUP_NODE_ID = SiteGroupMembers.PARENT_MEMBER_ID AND LKUP.LOOKUP_TYPE = 'RRS_NODE_PURPOSE' AND RSGNB.NODE_PURPOSE_CODE = LKUP.LOOKUP_CODE))),
255 DECODE(SiteGroupMembers.PARENT_MEMBER_ID, -1, 'Y', 'N'),
256 DECODE(SiteGroupNodes1.SITE_GROUP_NODE_ID, NULL, Sites1.SITE_ID,
257 SiteGroupNodes1.SITE_GROUP_NODE_ID),
258 DECODE(SiteGroupNodes1.SITE_GROUP_NODE_ID, NULL, Sites1.SITE_IDENTIFICATION_NUMBER,
259 SiteGroupNodes1.NODE_IDENTIFICATION_NUMBER),
260 DECODE(SiteGroupNodes1.NAME, NULL, Sites1.NAME, SiteGroupNodes1.NAME),
261 DECODE(SiteGroupNodes1.SITE_GROUP_NODE_ID, NULL, 'SITE', 'NODE'),
262 DECODE(SiteGroupNodes1.SITE_GROUP_NODE_ID, NULL,
263 (SELECT SITE_USE_TYPE_CODE FROM RRS_SITE_USES WHERE RRS_SITE_USES.SITE_ID = SiteGroupMembers.CHILD_MEMBER_ID AND RRS_SITE_USES.IS_PRIMARY_FLAG = 'Y'),
264 (SELECT NODE_PURPOSE_CODE FROM RRS_SITE_GROUP_NODES_B RSGNB WHERE RSGNB.SITE_GROUP_NODE_ID = SiteGroupMembers.CHILD_MEMBER_ID)),
265 DECODE(SiteGroupNodes1.SITE_GROUP_NODE_ID, NULL,
266 (SELECT LKUP.MEANING
267 FROM RRS_SITE_USES, AR_LOOKUPS LKUP
268 WHERE RRS_SITE_USES.SITE_ID = SiteGroupMembers.CHILD_MEMBER_ID AND RRS_SITE_USES.IS_PRIMARY_FLAG = 'Y' AND LKUP.LOOKUP_TYPE = 'PARTY_SITE_USE_CODE' AND RRS_SITE_USES.SITE_USE_TYPE_CODE = LKUP.LOOKUP_CODE),
269 (SELECT LKUP.MEANING
270 FROM RRS_SITE_GROUP_NODES_B RSGNB, RRS_LOOKUPS_V LKUP
271 WHERE RSGNB.SITE_GROUP_NODE_ID = SiteGroupMembers.CHILD_MEMBER_ID AND RSGNB.NODE_PURPOSE_CODE = LKUP.LOOKUP_CODE AND LKUP.LOOKUP_TYPE = 'RRS_NODE_PURPOSE')),
272 DECODE(SiteGroupNodes1.SITE_GROUP_NODE_ID, NULL, Sites1.DESCRIPTION, SiteGroupNodes1.DESCRIPTION),
273 DECODE(SiteGroupNodes1.SITE_GROUP_NODE_ID, NULL, Sites1.SITE_STATUS_CODE, NULL),
274 DECODE(SiteGroupNodes1.SITE_GROUP_NODE_ID, NULL, (SELECT MEANING FROM RRS_LOOKUPS_V WHERE LOOKUP_TYPE = 'RRS_SITE_STATUS' AND LOOKUP_CODE = Sites1.SITE_STATUS_CODE), NULL),
275 SiteGroupMembers.SEQUENCE_NUMBER,
276 LEVEL
277 )
278 BULK COLLECT
279 INTO x_hier_members_tab
280 FROM RRS_SITE_GROUP_MEMBERS SiteGroupMembers,
281 RRS_SITE_GROUP_NODES_VL SiteGroupNodes1,
282 RRS_SITE_GROUP_NODES_VL SiteGroupNodes2,
283 RRS_SITES_VL Sites1,
284 RRS_SITES_VL Sites2
285 WHERE SiteGroupMembers.CHILD_MEMBER_ID = SiteGroupNodes1.SITE_GROUP_NODE_ID(+)
286 AND SiteGroupMembers.CHILD_MEMBER_ID = Sites1.SITE_ID(+)
287 AND SiteGroupMembers.PARENT_MEMBER_ID = SiteGroupNodes2.SITE_GROUP_NODE_ID(+)
288 AND SiteGroupMembers.PARENT_MEMBER_ID = Sites2.SITE_ID(+)
289 AND SiteGroupMembers.DELETED_FLAG = 'N'
290 START WITH PARENT_MEMBER_ID = l_parent_id AND SiteGroupMembers.SITE_GROUP_ID = l_site_group_id AND SiteGroupMembers.SITE_GROUP_VERSION_ID = p_hier_version_id
291 CONNECT BY PRIOR CHILD_MEMBER_ID = PARENT_MEMBER_ID AND SiteGroupMembers.SITE_GROUP_ID = l_site_group_id AND SiteGroupMembers.SITE_GROUP_VERSION_ID = p_hier_version_id
292 ORDER SIBLINGS BY SEQUENCE_NUMBER;
293
294 IF x_hier_members_tab IS NULL OR x_hier_members_tab.count = 0 THEN
295 fnd_message.set_name('RRS', 'RRS_HIER_NO_CHILD_MBR_FOUND');
296 fnd_msg_pub.add;
297 x_msg_count := x_msg_count + 1;
298 x_return_status := G_RET_STS_ERROR;
299 END IF;
300
301 END IF;
302
303 IF x_msg_count = 0 THEN
304 x_return_status := G_RET_STS_SUCCESS;
305 END IF;
306
307 EXCEPTION
308 WHEN OTHERS THEN
309 x_msg_data := 'RRS_HIERARCHY_INFO.Get_Hierarchy_Members: ' || dbms_utility.format_error_backtrace;
310 fnd_message.set_name('RRS', 'RRS_UNEXPECTED_ERROR');
311 fnd_msg_pub.add;
312 x_msg_count := x_msg_count + 1;
313 x_return_status := G_RET_STS_UNEXP_ERROR;
314
315 END;
316
317
318
319 Procedure Get_Hierarchy_Members(
320 p_api_version IN number
321 ,p_hier_id IN number
322 ,p_hier_version_number IN number
323 ,p_parent_member_type IN varchar2
324 ,p_parent_member_id_num IN varchar2
325 ,x_hier_members_tab OUT NOCOPY rrs_hier_members_tab
326 ,x_return_status OUT NOCOPY varchar2
327 ,x_msg_count OUT NOCOPY number
328 ,x_msg_data OUT NOCOPY varchar2
329 ) is
330
331 l_hier_version_id number;
332 l_parent_id number;
333 l_parent_member_type varchar2(100);
334 l_parent_member_id_number varchar2(100);
335
336 BEGIN
337 IF x_msg_count IS NULL THEN
338 x_msg_count := 0;
339 END IF;
340
341 /* get site_group_version_id
342 if user does not provide version number, we return the latest version*/
343
344 SELECT SITE_GROUP_VERSION_ID
345 INTO l_hier_version_id
346 FROM RRS_SITE_GROUP_VERSIONS
347 WHERE SITE_GROUP_ID = p_hier_id
348 AND VERSION_NUMBER = DECODE(p_hier_version_number, NULL, (SELECT MAX(VERSION_NUMBER)
349 FROM RRS_SITE_GROUP_VERSIONS
350 WHERE SITE_GROUP_ID = p_hier_id),
351 p_hier_version_number);
352
353
354 --query a sub-hierarchy under the speicific parent
355 --query the parent id according to the parent identification number input
356 IF p_parent_member_type IS NOT NULL AND p_parent_member_id_num IS NOT NULL THEN
357 BEGIN
358 IF UPPER(p_parent_member_type) = 'SITE' THEN
359 SELECT SITE_ID
360 INTO l_parent_id
361 FROM RRS_SITES_B
362 WHERE SITE_IDENTIFICATION_NUMBER = p_parent_member_id_num;
363 ELSIF UPPER(p_parent_member_type) = 'NODE' THEN
364 SELECT SITE_GROUP_NODE_ID
365 INTO l_parent_id
366 FROM RRS_SITE_GROUP_NODES_B
367 WHERE NODE_IDENTIFICATION_NUMBER = p_parent_member_id_num;
368 END IF;
369
370 IF l_parent_id IS NULL THEN
371 RAISE e_no_parent_member_found;
372 END IF;
373 EXCEPTION
374 WHEN NO_DATA_FOUND THEN
375 RAISE e_no_parent_member_found;
376 END;
377 END IF;
378
379
380 Get_Hierarchy_Members(p_api_version => p_api_version
381 ,p_hier_version_id => l_hier_version_id
382 ,p_parent_member_type => p_parent_member_type
383 ,p_parent_member_id_num => p_parent_member_id_num
384 ,x_hier_members_tab => x_hier_members_tab
385 ,x_return_status => x_return_status
386 ,x_msg_count => x_msg_count
387 ,x_msg_data => x_msg_data);
388
389 EXCEPTION
390 WHEN e_no_parent_member_found THEN
391 fnd_message.set_name('RRS', 'RRS_HIER_NO_PARENT_MBR_FOUND');
392 fnd_message.set_token('PARENT_TYPE', p_parent_member_type);
393 fnd_message.set_token('PARENT_ID_NUM', p_parent_member_id_num);
394 fnd_message.set_token('HIERARCHY_NAME', p_hier_id);
395 fnd_msg_pub.add;
396 x_msg_count := x_msg_count + 1;
397 x_return_status := G_RET_STS_ERROR;
398
399 WHEN OTHERS THEN
400 x_msg_data := 'RRS_HIERARCHY_INFO.Get_Hierarchy_Members: ' || dbms_utility.format_error_backtrace;
401 fnd_message.set_name('RRS', 'RRS_UNEXPECTED_ERROR');
402 fnd_msg_pub.add;
403 x_msg_count := x_msg_count + 1;
404 x_return_status := G_RET_STS_UNEXP_ERROR;
405
406 END;
407
408
409 Procedure Get_Hierarchy_Members(
410 p_api_version IN number
411 ,p_hier_name IN varchar2
412 ,p_hier_version_number IN number
413 ,p_parent_member_type IN varchar2
414 ,p_parent_member_id_num IN varchar2
415 ,x_hier_members_tab OUT NOCOPY rrs_hier_members_tab
416 ,x_return_status OUT NOCOPY varchar2
417 ,x_msg_count OUT NOCOPY number
418 ,x_msg_data OUT NOCOPY varchar2
419 ) is
420
421 l_hier_id number;
422 l_parent_id rrs_site_group_members.parent_member_id%TYPE;
423
424 BEGIN
425 IF x_msg_count IS NULL THEN
426 x_msg_count := 0;
427 END IF;
428
429 /* get site_group_id from hier name*/
430 BEGIN
431 SELECT SITE_GROUP_ID
432 INTO l_hier_id
433 FROM RRS_SITE_GROUPS_VL
434 WHERE NAME = p_hier_name;
435 EXCEPTION
436 WHEN NO_DATA_FOUND THEN
437 RAISE e_no_hierarchy_found;
438 END;
439
440 --query a sub-hierarchy under the speicific parent
441 --query the parent id according to the parent identification number input
442 IF p_parent_member_type IS NOT NULL AND p_parent_member_id_num IS NOT NULL THEN
443 BEGIN
444 IF UPPER(p_parent_member_type) = 'SITE' THEN
445 SELECT SITE_ID
446 INTO l_parent_id
447 FROM RRS_SITES_B
448 WHERE SITE_IDENTIFICATION_NUMBER = p_parent_member_id_num;
449 ELSIF UPPER(p_parent_member_type) = 'NODE' THEN
450 SELECT SITE_GROUP_NODE_ID
451 INTO l_parent_id
452 FROM RRS_SITE_GROUP_NODES_B
453 WHERE NODE_IDENTIFICATION_NUMBER = p_parent_member_id_num;
454 END IF;
455
456 IF l_parent_id IS NULL THEN
457 RAISE e_no_parent_member_found;
458 END IF;
459 EXCEPTION
460 WHEN NO_DATA_FOUND THEN
461 RAISE e_no_parent_member_found;
462 END;
463 END IF;
464
465
466 Get_Hierarchy_Members(p_api_version => p_api_version
467 ,p_hier_id => l_hier_id
468 ,p_hier_version_number => p_hier_version_number
469 ,p_parent_member_type => p_parent_member_type
470 ,p_parent_member_id_num => p_parent_member_id_num
471 ,x_hier_members_tab => x_hier_members_tab
472 ,x_return_status => x_return_status
473 ,x_msg_count => x_msg_count
474 ,x_msg_data => x_msg_data);
475
476
477 EXCEPTION
478 WHEN e_no_hierarchy_found THEN
479 fnd_message.set_name('RRS', 'RRS_NO_HIER_FOUND');
480 fnd_message.set_token('HIERARCHY_NAME', p_hier_name);
481 fnd_msg_pub.add;
482 x_msg_count := x_msg_count + 1;
483 x_return_status := G_RET_STS_ERROR;
484 WHEN e_no_parent_member_found THEN
485 fnd_message.set_name('RRS', 'RRS_HIER_NO_PARENT_MBR_FOUND');
486 fnd_message.set_token('PARENT_TYPE', p_parent_member_type);
487 fnd_message.set_token('PARENT_ID_NUM', p_parent_member_id_num);
491 x_return_status := G_RET_STS_ERROR;
488 fnd_message.set_token('HIERARCHY_NAME', p_hier_name);
489 fnd_msg_pub.add;
490 x_msg_count := x_msg_count + 1;
492 WHEN OTHERS THEN
493 x_msg_data := 'RRS_HIERARCHY_INFO.Get_Hierarchy_Members: ' || dbms_utility.format_error_backtrace;
494 fnd_message.set_name('RRS', 'RRS_UNEXPECTED_ERROR');
495 fnd_msg_pub.add;
496 x_msg_count := x_msg_count + 1;
497 x_return_status := G_RET_STS_UNEXP_ERROR;
498
499 END;
500
501
502
503 ----------------------------
504 --Get Hierarchy Attributes--
505 ----------------------------
506
507 Procedure Get_Hierarchy_Attributes(
508 p_api_version IN number
509 ,p_hier_version_id IN number
510 ,p_page_name IN varchar2
511 ,x_hier_attr_row_tab OUT NOCOPY ego_user_attr_row_table
512 ,x_hier_attr_data_tab OUT NOCOPY ego_user_attr_data_table
513 ,x_return_status OUT NOCOPY varchar2
514 ,x_msg_count OUT NOCOPY number
515 ,x_msg_data OUT NOCOPY varchar2
516 ) is
517
518 l_api_name CONSTANT VARCHAR2(30) := 'Get_Hierarchy_User_Attributes';
519 l_rrs_entity_name rrs_lookups_v.meaning%Type;
520 l_pk_column_values EGO_COL_NAME_VALUE_PAIR_ARRAY;
521 l_object_name VARCHAR2(30);
522 l_attr_group_type VARCHAR2(30);
523 l_data_level_name VARCHAR2(30);
524 l_page_id NUMBER;
525 l_display_name VARCHAR2(240);
526 l_request_table EGO_ATTR_GROUP_REQUEST_TABLE;
527
528 l_site_group_id NUMBER;
529 l_group_purpose_code VARCHAR2(30);
530
531
532 l_x_attributes_row_table EGO_USER_ATTR_ROW_TABLE;
533 l_x_attributes_data_table EGO_USER_ATTR_DATA_TABLE;
534 l_x_return_status VARCHAR2(1);
535 l_x_errorcode NUMBER;
536 l_x_msg_count NUMBER;
537 l_x_msg_data VARCHAR2(1000);
538
539 Type attr_grp_name_tab IS TABLE OF VARCHAR2(30);
540 l_attr_grp_names attr_grp_name_Tab;
541
542 BEGIN
543 IF x_msg_count IS NULL THEN
544 x_msg_count := 0;
545 END IF;
546
547 /* get site_group_id*/
548 BEGIN
549 SELECT RSG.SITE_GROUP_ID, RSG.GROUP_PURPOSE_CODE
550 INTO l_site_group_id, l_group_purpose_code
551 FROM RRS_SITE_GROUPS_VL RSG, RRS_SITE_GROUP_VERSIONS RSGV
552 WHERE RSG.SITE_GROUP_ID = RSGV.SITE_GROUP_ID
553 AND RSGV.SITE_GROUP_VERSION_ID = p_hier_version_id;
554 EXCEPTION
555 WHEN NO_DATA_FOUND THEN
556 fnd_message.set_name('RRS', 'RRS_NO_HIER_FOUND');
557 fnd_message.set_token('HIERARCHY_NAME', p_hier_version_id);
558 fnd_msg_pub.add;
559 x_msg_count := x_msg_count + 1;
560 x_return_status := G_RET_STS_ERROR;
561 END;
562
563 SELECT MEANING
564 INTO l_rrs_entity_name
565 FROM RRS_LOOKUPS_V
566 WHERE LOOKUP_TYPE = 'RRS_ENTITY'
567 AND LOOKUP_CODE = 'RRS_HIERARCHY';
568
569 /* get attribute groups for the hierarchy and its classification code*/
570 IF p_page_name IS NULL THEN
571
572 SELECT ext.DESCRIPTIVE_FLEX_CONTEXT_CODE
573 BULK COLLECT
574 INTO l_attr_grp_names
575 FROM EGO_OBJ_AG_ASSOCS_B eoab
576 ,FND_OBJECTS fo
577 ,EGO_FND_DSC_FLX_CTX_EXT ext
578 WHERE eoab.OBJECT_ID = fo.OBJECT_ID
579 AND fo.OBJ_NAME = 'RRS_HIERARCHY'
580 AND eoab.ATTR_GROUP_ID = ext.ATTR_GROUP_ID
581 AND eoab.CLASSIFICATION_CODE = l_group_purpose_code
582 AND RRS_SECURITY_PUB.Check_UDA_View_Privilege(
583 p_api_version => 1.0,
584 p_attr_group_id => eoab.ATTR_GROUP_ID,
585 p_object_name => fo.OBJ_NAME,
586 p_object_key => p_hier_version_id) = FND_API.G_TRUE;
587
588 ELSIF p_page_name IS NOT NULL THEN
589
590 BEGIN
591 SELECT PAGE_ID,
592 DISPLAY_NAME
593 INTO l_page_id,
594 l_display_name
595 FROM EGO_PAGES_V
596 WHERE OBJECT_NAME = 'RRS_HIERARCHY'
597 AND DISPLAY_NAME = p_page_name
598 AND CLASSIFICATION_CODE = l_group_purpose_code
599 ORDER BY SEQUENCE;
600
601 EXCEPTION
602 WHEN NO_DATA_FOUND THEN
603 fnd_message.set_name('RRS', 'RRS_NO_PAGE_FOUND');
604 fnd_message.set_token('PAGE_NAME', p_page_name);
605 fnd_message.set_token('CLASSIFICATION_CODE', l_group_purpose_code);
606 fnd_msg_pub.add;
607 x_msg_count := x_msg_count + 1;
608 x_return_status := G_RET_STS_ERROR;
609 END;
610
611 BEGIN
612 SELECT PE.ATTR_GROUP_NAME
613 BULK COLLECT
614 INTO l_attr_grp_names
615 FROM EGO_PAGE_ENTRIES_V PE
616 ,EGO_ATTR_GROUPS_V AG
617 WHERE
618 PE.PAGE_ID = l_page_id
619 AND PE.ATTR_GROUP_NAME = AG.ATTR_GROUP_NAME
620 AND RRS_SECURITY_PUB.Check_UDA_View_Privilege(
621 p_api_version => 1.0,
622 p_attr_group_id => AG.ATTR_GROUP_ID,
623 p_object_name => 'RRS_HIERARCHY',
624 p_object_key => p_hier_version_id) = FND_API.G_TRUE
625 ORDER BY SEQUENCE;
626
627 EXCEPTION
628
629 WHEN NO_DATA_FOUND THEN
630 fnd_message.set_name('RRS', 'RRS_NO_PAGE_ENTRY_FOUND');
631 fnd_message.set_token('PAGE_NAME', p_page_name);
632 fnd_msg_pub.add;
633 x_msg_count := x_msg_count + 1;
634 x_return_status := G_RET_STS_ERROR;
635 END;
636
637 END IF;
638
639 l_pk_column_values := EGO_COL_NAME_VALUE_PAIR_ARRAY(
640 EGO_COL_NAME_VALUE_PAIR_OBJ('SITE_GROUP_VERSION_ID', TO_CHAR(p_hier_version_id)));
641 l_object_name := 'RRS_HIERARCHY';
642 l_attr_group_type := 'RRS_HIERARCHY_GROUP';
643 l_data_level_name := 'HIERARCHY_LEVEL';
644
645 IF l_attr_grp_names IS NOT NULL AND l_attr_grp_names.COUNT > 0 THEN
646 x_hier_attr_row_tab := new EGO_USER_ATTR_ROW_TABLE();
647 x_hier_attr_data_tab := new EGO_USER_ATTR_DATA_TABLE();
648
649 FOR i in l_attr_grp_names.FIRST .. l_attr_grp_names.LAST
650 LOOP
651 l_request_table := new EGO_ATTR_GROUP_REQUEST_TABLE();
652 l_request_table.EXTEND();
653 l_request_table(l_request_table.LAST) := new EGO_ATTR_GROUP_REQUEST_OBJ(
654 NULL -- ATTR_GROUP_ID
655 ,718 -- APPLICATION_ID
656 ,l_attr_group_type -- ATTR_GROUP_TYPE
657 ,l_attr_grp_names(i) -- ATTR_GROUP_NAME
658 ,l_data_level_name -- DATA_LEVEL
659 ,NULL -- DATA_LEVEL_1
660 ,NULL -- DATA_LEVEL_2
661 ,NULL -- DATA_LEVEL_3
662 ,NULL -- DATA_LEVEL_4
663 ,NULL -- DATA_LEVEL_5
664 ,NULL -- ATTR_NAME_LIST
665 );
666 /* call API to get attribute groups and attributes*/
667 EGO_USER_ATTRS_DATA_PUB.Get_User_Attrs_Data(
668 p_api_version => 1.0
669 ,p_object_name => l_object_name
670 ,p_pk_column_name_value_pairs => l_pk_column_values
671 ,p_attr_group_request_table => l_request_table
672 ,p_user_privileges_on_object => NULL
673 ,p_entity_id => NULL
674 ,p_entity_index => NULL
675 ,p_entity_code => NULL
676 ,p_debug_level => 0
677 ,p_init_error_handler => FND_API.G_FALSE
678 ,p_init_fnd_msg_list => FND_API.G_FALSE
679 ,p_add_errors_to_fnd_stack => FND_API.G_FALSE
680 ,p_commit => FND_API.G_FALSE
681 ,x_attributes_row_table => l_x_attributes_row_table
682 ,x_attributes_data_table => l_x_attributes_data_table
683 ,x_return_status => l_x_return_status
684 ,x_errorcode => l_x_errorcode
685 ,x_msg_count => l_x_msg_count
686 ,x_msg_data => l_x_msg_data
687 );
688
689 /*Take the error messages returned from EGO API into our count*/
690 IF l_x_msg_count IS NOT NULL THEN
691 x_msg_count := x_msg_count + l_x_msg_count;
692 END IF;
693
694
695 IF l_x_attributes_row_table IS NOT NULL AND l_x_attributes_row_table.COUNT >0 AND
696 l_x_attributes_data_table IS NOT NULL AND l_x_attributes_data_table.COUNT > 0 THEN
697
698 FOR n in l_x_attributes_row_table.FIRST .. l_x_attributes_row_table.LAST
699 LOOP
700 x_hier_attr_row_tab.EXTEND();
701 x_hier_attr_row_tab(x_hier_attr_row_tab.LAST) := l_x_attributes_row_table(n);
702
703 END LOOP;
704
705 FOR n in l_x_attributes_data_table.FIRST .. l_x_attributes_data_table.LAST
706 LOOP
707 x_hier_attr_data_tab.EXTEND();
708 x_hier_attr_data_tab(x_hier_attr_data_tab.LAST) := l_x_attributes_data_table(n);
709 END LOOP;
710
711 END IF;
712
713 END LOOP;
714
715 END IF;
716
717 IF x_msg_count = 0 THEN
718 x_return_status := G_RET_STS_SUCCESS;
719 END IF;
720
721
722 EXCEPTION
723 WHEN OTHERS THEN
724 x_msg_data := 'RRS_HIERARCHY_INFO.Get_Hierarchy_Attributes: ' || dbms_utility.format_error_backtrace;
725 fnd_message.set_name('RRS', 'RRS_UNEXPECTED_ERROR');
726 fnd_msg_pub.add;
727 x_msg_count := x_msg_count + 1;
728 x_return_status := G_RET_STS_UNEXP_ERROR;
729
730 END;
731
732
733 Procedure Get_Hierarchy_Attributes(
734 p_api_version IN number
735 ,p_hier_id IN number
736 ,p_hier_version_number IN number
737 ,p_page_name IN varchar2
738 ,x_hier_attr_row_tab OUT NOCOPY ego_user_attr_row_table
739 ,x_hier_attr_data_tab OUT NOCOPY ego_user_attr_data_table
740 ,x_return_status OUT NOCOPY varchar2
741 ,x_msg_count OUT NOCOPY number
742 ,x_msg_data OUT NOCOPY varchar2
743 ) is
744
745 l_hier_version_id number;
746
747 BEGIN
748 IF x_msg_count IS NULL THEN
749 x_msg_count := 0;
750 END IF;
751
752 /* get site_group_version_id
753 if user does not provide version number, we return the latest version*/
754
755 SELECT SITE_GROUP_VERSION_ID
756 INTO l_hier_version_id
757 FROM RRS_SITE_GROUP_VERSIONS
758 WHERE SITE_GROUP_ID = p_hier_id
759 AND VERSION_NUMBER = DECODE(p_hier_version_number, NULL, (SELECT MAX(VERSION_NUMBER)
760 FROM RRS_SITE_GROUP_VERSIONS
761 WHERE SITE_GROUP_ID = p_hier_id),
762 p_hier_version_number);
763
764 Get_Hierarchy_Attributes(p_api_version => p_api_version
765 ,p_hier_version_id => l_hier_version_id
766 ,p_page_name => p_page_name
767 ,x_hier_attr_row_tab => x_hier_attr_row_tab
768 ,x_hier_attr_data_tab => x_hier_attr_data_tab
769 ,x_return_status => x_return_status
770 ,x_msg_count => x_msg_count
771 ,x_msg_data => x_msg_data);
772 EXCEPTION
773 WHEN OTHERS THEN
774 x_msg_data := 'RRS_HIERARCHY_INFO.Get_Hierarchy_Attributes: ' || dbms_utility.format_error_backtrace;
775 fnd_message.set_name('RRS', 'RRS_UNEXPECTED_ERROR');
776 fnd_msg_pub.add;
777 x_msg_count := x_msg_count + 1;
778 x_return_status := G_RET_STS_UNEXP_ERROR;
779 END;
780
781
782 Procedure Get_Hierarchy_Attributes(
783 p_api_version IN number
784 ,p_hier_name IN varchar2
785 ,p_hier_version_number IN number
786 ,p_page_name IN varchar2
787 ,x_hier_attr_row_tab OUT NOCOPY ego_user_attr_row_table
788 ,x_hier_attr_data_tab OUT NOCOPY ego_user_attr_data_table
789 ,x_return_status OUT NOCOPY varchar2
790 ,x_msg_count OUT NOCOPY number
791 ,x_msg_data OUT NOCOPY varchar2
792 ) is
793
794 l_hier_id NUMBER;
795
796 BEGIN
797 IF x_msg_count IS NULL THEN
798 x_msg_count := 0;
799 END IF;
800 -- get site_group_id
801 BEGIN
802 SELECT SITE_GROUP_ID
803 INTO l_hier_id
804 FROM RRS_SITE_GROUPS_VL
805 WHERE NAME = p_hier_name;
806 EXCEPTION
807 WHEN NO_DATA_FOUND THEN
808 RAISE e_no_hierarchy_found;
809 END;
810
811 Get_Hierarchy_Attributes(p_api_version => p_api_version
812 ,p_hier_id => l_hier_id
813 ,p_hier_version_number => p_hier_version_number
814 ,p_page_name => p_page_name
815 ,x_hier_attr_row_tab => x_hier_attr_row_tab
816 ,x_hier_attr_data_tab => x_hier_attr_data_tab
817 ,x_return_status => x_return_status
818 ,x_msg_count => x_msg_count
819 ,x_msg_data => x_msg_data);
820
821 EXCEPTION
822 WHEN e_no_hierarchy_found THEN
823 fnd_message.set_name('RRS', 'RRS_NO_HIER_FOUND');
824 fnd_message.set_token('HIERARCHY_NAME', p_hier_name);
825 fnd_msg_pub.add;
826 x_msg_count := x_msg_count + 1;
827 x_return_status := G_RET_STS_ERROR;
828 WHEN OTHERS THEN
829 x_msg_data := 'RRS_HIERARCHY_INFO.Get_Hierarchy_Attributes: ' || dbms_utility.format_error_backtrace;
830 fnd_message.set_name('RRS', 'RRS_UNEXPECTED_ERROR');
831 fnd_msg_pub.add;
832 x_msg_count := x_msg_count + 1;
833 x_return_status := G_RET_STS_UNEXP_ERROR;
834
835 END;
836
837
838 ----------------------------------
839 --Get Hierarchy Complete Details--
840 ----------------------------------
841
842 Procedure Get_Complete_Hierarchy_Details(
843 p_api_version IN number
844 ,p_hier_version_id IN number
845 ,p_page_name IN varchar2
846 ,x_hier_header_rec OUT NOCOPY rrs_hier_header_rec
847 ,x_hier_members_tab OUT NOCOPY rrs_hier_members_tab
848 ,x_hier_attr_row_tab OUT NOCOPY ego_user_attr_row_table
849 ,x_hier_attr_data_tab OUT NOCOPY ego_user_attr_data_table
850 ,x_return_status OUT NOCOPY varchar2
851 ,x_msg_count OUT NOCOPY number
852 ,x_msg_data OUT NOCOPY varchar2
853 ) is
854
855 BEGIN
856
857 Get_Hierarchy_Header(
858 p_api_version => p_api_version
859 ,p_hier_version_id => p_hier_version_id
860 ,x_hier_header_rec => x_hier_header_rec
861 ,x_return_status => x_return_status
862 ,x_msg_count => x_msg_count
863 ,x_msg_data => x_msg_data);
864
865 Get_Hierarchy_Members(
866 p_api_version => p_api_version
867 ,p_hier_version_id => p_hier_version_id
868 ,p_parent_member_type => NULL
869 ,p_parent_member_id_num => NULL
870 ,x_hier_members_tab => x_hier_members_tab
871 ,x_return_status => x_return_status
872 ,x_msg_count => x_msg_count
873 ,x_msg_data => x_msg_data);
874
875 Get_Hierarchy_Attributes(
876 p_api_version => p_api_version
877 ,p_hier_version_id => p_hier_version_id
878 ,p_page_name => p_page_name
879 ,x_hier_attr_row_tab => x_hier_attr_row_tab
880 ,x_hier_attr_data_tab => x_hier_attr_data_tab
881 ,x_return_status => x_return_status
882 ,x_msg_count => x_msg_count
883 ,x_msg_data => x_msg_data);
884
888 p_api_version IN number
885 END;
886
887 Procedure Get_Complete_Hierarchy_Details(
889 ,p_hier_id IN number
890 ,p_hier_version_number IN number
891 ,p_page_name IN varchar2
892 ,x_hier_header_rec OUT NOCOPY rrs_hier_header_rec
893 ,x_hier_members_tab OUT NOCOPY rrs_hier_members_tab
894 ,x_hier_attr_row_tab OUT NOCOPY ego_user_attr_row_table
895 ,x_hier_attr_data_tab OUT NOCOPY ego_user_attr_data_table
896 ,x_return_status OUT NOCOPY varchar2
897 ,x_msg_count OUT NOCOPY number
898 ,x_msg_data OUT NOCOPY varchar2
899 ) is
900
901 BEGIN
902
903 Get_Hierarchy_Header(
904 p_api_version => p_api_version
905 ,p_hier_id => p_hier_id
906 ,p_hier_version_number => p_hier_version_number
907 ,x_hier_header_rec => x_hier_header_rec
908 ,x_return_status => x_return_status
909 ,x_msg_count => x_msg_count
910 ,x_msg_data => x_msg_data);
911
912 Get_Hierarchy_Members(
913 p_api_version => p_api_version
914 ,p_hier_id => p_hier_id
915 ,p_hier_version_number => p_hier_version_number
916 ,p_parent_member_type => NULL
917 ,p_parent_member_id_num => NULL
918 ,x_hier_members_tab => x_hier_members_tab
919 ,x_return_status => x_return_status
920 ,x_msg_count => x_msg_count
921 ,x_msg_data => x_msg_data);
922
923 Get_Hierarchy_Attributes(
924 p_api_version => p_api_version
925 ,p_hier_id => p_hier_id
926 ,p_hier_version_number => p_hier_version_number
927 ,p_page_name => p_page_name
928 ,x_hier_attr_row_tab => x_hier_attr_row_tab
929 ,x_hier_attr_data_tab => x_hier_attr_data_tab
930 ,x_return_status => x_return_status
931 ,x_msg_count => x_msg_count
932 ,x_msg_data => x_msg_data);
933
934 END;
935
936
937 Procedure Get_Complete_Hierarchy_Details(
938 p_api_version IN number
939 ,p_hier_name IN varchar2
940 ,p_hier_version_number IN number
941 ,p_page_name IN varchar2
942 ,x_hier_header_rec OUT NOCOPY rrs_hier_header_rec
943 ,x_hier_members_tab OUT NOCOPY rrs_hier_members_tab
944 ,x_hier_attr_row_tab OUT NOCOPY ego_user_attr_row_table
945 ,x_hier_attr_data_tab OUT NOCOPY ego_user_attr_data_table
946 ,x_return_status OUT NOCOPY varchar2
947 ,x_msg_count OUT NOCOPY number
948 ,x_msg_data OUT NOCOPY varchar2
949 ) is
950
951 BEGIN
952
953 Get_Hierarchy_Header(
954 p_api_version => p_api_version
955 ,p_hier_name => p_hier_name
956 ,x_hier_header_rec => x_hier_header_rec
957 ,x_return_status => x_return_status
958 ,x_msg_count => x_msg_count
959 ,x_msg_data => x_msg_data);
960
961 Get_Hierarchy_Members(
962 p_api_version => p_api_version
963 ,p_hier_name => p_hier_name
964 ,p_hier_version_number => p_hier_version_number
965 ,p_parent_member_type => NULL
966 ,p_parent_member_id_num => NULL
967 ,x_hier_members_tab => x_hier_members_tab
968 ,x_return_status => x_return_status
969 ,x_msg_count => x_msg_count
970 ,x_msg_data => x_msg_data);
971
972 Get_Hierarchy_Attributes(
973 p_api_version => p_api_version
974 ,p_hier_name => p_hier_name
975 ,p_hier_version_number => p_hier_version_number
976 ,p_page_name => p_page_name
977 ,x_hier_attr_row_tab => x_hier_attr_row_tab
978 ,x_hier_attr_data_tab => x_hier_attr_data_tab
979 ,x_return_status => x_return_status
980 ,x_msg_count => x_msg_count
981 ,x_msg_data => x_msg_data);
982
983 END;
984
985
986
987 /**
988 ** Test Methods (FOR DEBUGGING ONLY)
989 **
990 *
991 Procedure Get_Hierarchy_Header_Test
992 is
993
994 x_hier_header_rec rrs_hier_header_rec;
995 --x_error_msg varchar2(255);
996 x_return_status varchar2(20);
997 x_error_msg_count number;
998 x_error_msg varchar2(200);
999
1000 BEGIN
1001
1002 RRS_HIERARCHY_INFO.Get_Hierarchy_Header(p_api_version => '1.0'
1003 ,p_hier_name => 'Unit Hierarchy'
1004 ,x_hier_header_rec => x_hier_header_rec
1005 ,x_return_status => x_return_status
1006 ,x_msg_count => x_error_msg_count
1007 ,x_msg_data => x_error_msg);
1008
1009 dbms_output.put_line(x_return_status);
1010 dbms_output.put_line(x_error_msg_count);
1011 dbms_output.put_line(x_error_msg);
1012
1013 IF(x_hier_header_rec IS NOT NULL) THEN
1014 dbms_output.put_line(chr(13) || chr(10));
1015 dbms_output.put_line('Printing Hierarchy Header Information ');
1016 dbms_output.put_line('============================== ');
1017 dbms_output.put_line('Hierarchy ID: '||chr(9)||chr(9)||chr(9)|| x_hier_header_rec.site_group_id);
1018 dbms_output.put_line('Site Group Type Code: '||chr(9)||chr(9)||chr(9)|| x_hier_header_rec.site_group_type_code);
1019 dbms_output.put_line('Name: '||chr(9)||chr(9)||chr(9)|| x_hier_header_rec.name);
1020 dbms_output.put_line('Hierarchy Purpose Code: '||chr(9)||chr(9)||chr(9)|| x_hier_header_rec.site_group_purpose_code);
1021 dbms_output.put_line('Hierarchy Purpose: '||chr(9)||chr(9)||chr(9)|| x_hier_header_rec.site_group_purpose);
1022 dbms_output.put_line('Description: '||chr(9)||chr(9)||chr(9)|| x_hier_header_rec.description);
1023 dbms_output.put_line('Start Date: '||chr(9)||chr(9)||chr(9)|| x_hier_header_rec.start_date);
1027 END;
1024 dbms_output.put_line('End Date: '||chr(9)||chr(9)||chr(9)|| x_hier_header_rec.end_date);
1025
1026 END IF;
1028
1029
1030 Procedure Get_Hierarchy_Members_Test
1031 is
1032
1033 x_hier_members_tab rrs_hier_members_tab;
1034 x_return_status varchar2(20);
1035 x_error_msg_count number;
1036 x_error_msg varchar2(200);
1037
1038 BEGIN
1039 RRS_HIERARCHY_INFO.Get_Hierarchy_Members(
1040 p_api_version => '1.0'
1041 ,p_hier_name => 'WS Hierarchy1'
1042 ,p_hier_version_number => NULL
1043 ,p_parent_member_type => 'NODE'
1044 ,p_parent_member_id_num => 'WS_NODE1'
1045 ,x_hier_members_tab => x_hier_members_tab
1046 ,x_return_status => x_return_status
1047 ,x_msg_count => x_error_msg_count
1048 ,x_msg_data => x_error_msg);
1049
1050 --dbms_output.put_line(x_hier_members_tab.count);
1051
1052 dbms_output.put_line(x_return_status);
1053 dbms_output.put_line(x_error_msg_count);
1054 dbms_output.put_line(x_error_msg);
1055
1056 IF(x_error_msg_count < 1) THEN
1057 dbms_output.put_line(chr(13) || chr(10));
1058 dbms_output.put_line('Printing Hierarchy Members ');
1059 dbms_output.put_line('============================== ');
1060 FOR i in x_hier_members_tab.FIRST..x_hier_members_tab.LAST LOOP
1061 dbms_output.put_line('Site Group ID: '||chr(9)||chr(9)||chr(9)|| x_hier_members_tab(i).site_group_id);
1062 dbms_output.put_line('Site Group Version ID: '||chr(9)||chr(9)||chr(9)|| x_hier_members_tab(i).site_group_version_id);
1063 dbms_output.put_line('Parent ID: '||chr(9)||chr(9)||chr(9)|| x_hier_members_tab(i).parent_id);
1064 dbms_output.put_line('Parent Identification Number: '||chr(9)||chr(9)||chr(9)|| x_hier_members_tab(i).parent_id_num);
1065 dbms_output.put_line('Parent Name: '||chr(9)||chr(9)||chr(9)|| x_hier_members_tab(i).parent_name);
1066 dbms_output.put_line('Parent Type: '||chr(9)||chr(9)||chr(9)|| x_hier_members_tab(i).parent_type);
1067 dbms_output.put_line('Parent Purpose: '||chr(9)||chr(9)||chr(9)|| x_hier_members_tab(i).parent_purpose);
1068 dbms_output.put_line('Is Root: '||chr(9)||chr(9)||chr(9)|| x_hier_members_tab(i).is_root);
1069 dbms_output.put_line('Child ID: '||chr(9)||chr(9)||chr(9)|| x_hier_members_tab(i).child_id);
1070 dbms_output.put_line('Child Identification Number: '||chr(9)||chr(9)||chr(9)|| x_hier_members_tab(i).child_id_num);
1071 dbms_output.put_line('Child Name: '||chr(9)||chr(9)||chr(9)|| x_hier_members_tab(i).child_name);
1072 dbms_output.put_line('Child Type: '||chr(9)||chr(9)||chr(9)|| x_hier_members_tab(i).child_type);
1073 dbms_output.put_line('Child Purpose Code: '||chr(9)||chr(9)||chr(9)|| x_hier_members_tab(i).child_purpose_code);
1074 dbms_output.put_line('Child Purpose: '||chr(9)||chr(9)||chr(9)|| x_hier_members_tab(i).child_purpose);
1075 dbms_output.put_line('Description: '||chr(9)||chr(9)||chr(9)|| x_hier_members_tab(i).description);
1076 dbms_output.put_line('Status Code: '||chr(9)||chr(9)||chr(9)|| x_hier_members_tab(i).status_code);
1077 dbms_output.put_line('Status: '||chr(9)||chr(9)||chr(9)|| x_hier_members_tab(i).status);
1078 dbms_output.put_line('Child Sequence: '||chr(9)||chr(9)||chr(9)|| x_hier_members_tab(i).child_sequence);
1079 dbms_output.put_line('Depth: '||chr(9)||chr(9)||chr(9)|| x_hier_members_tab(i).depth);
1080 dbms_output.put_line('============================== ');
1081 END LOOP;
1082 END IF;
1083 END;
1084
1085
1086
1087 Procedure Get_Hierarchy_Attributes_Test
1088 is
1089
1090 x_hier_attr_row_tab ego_user_attr_row_table;
1091 x_hier_attr_data_tab ego_user_attr_data_table;
1092 x_return_status varchar2(30);
1093 x_error_msg_count number;
1094 x_error_msg varchar2(200);
1095
1096 row_index number;
1097 data_index number;
1098
1099 BEGIN
1100
1101 RRS_HIERARCHY_INFO.Get_Hierarchy_Attributes(
1102 p_api_version => 1.0
1103 ,p_hier_name => 'Unit Hierarchy'
1104 ,p_hier_version_number => NULL
1105 ,p_page_name => 'Hierarchy UDA Page Sample'
1106 ,x_hier_attr_row_tab => x_hier_attr_row_tab
1107 ,x_hier_attr_data_tab => x_hier_attr_data_tab
1108 ,x_return_status => x_return_status
1109 ,x_msg_count => x_error_msg_count
1110 ,x_msg_data => x_error_msg);
1111
1112 dbms_output.put_line(x_return_status);
1113 dbms_output.put_line(x_error_msg_count);
1114 dbms_output.put_line(x_error_msg);
1115
1116 IF x_hier_attr_row_tab IS NOT NULL AND x_hier_attr_row_tab.COUNT > 0 THEN
1117 FOR row_index in x_hier_attr_row_tab.FIRST .. x_hier_attr_row_tab.LAST
1118 LOOP
1119 DBMS_OUTPUT.PUT_LINE('===========================================');
1120 DBMS_OUTPUT.PUT_LINE('ROW ID: ' ||chr(9)||chr(9)||chr(9)|| x_hier_attr_row_tab(row_index).ROW_IDENTIFIER);
1121 DBMS_OUTPUT.PUT_LINE('ATTR_GROUP_ID: ' ||chr(9)||chr(9)||chr(9)|| x_hier_attr_row_tab(row_index).ATTR_GROUP_ID);
1122 DBMS_OUTPUT.PUT_LINE('ATTR_GROUP_APP_ID: ' ||chr(9)||chr(9)||chr(9)|| x_hier_attr_row_tab(row_index).ATTR_GROUP_APP_ID);
1123 DBMS_OUTPUT.PUT_LINE('ATTR_GROUP_TYPE: ' ||chr(9)||chr(9)||chr(9)|| x_hier_attr_row_tab(row_index).ATTR_GROUP_TYPE);
1124 DBMS_OUTPUT.PUT_LINE('ATTR_GROUP_NAME: ' ||chr(9)||chr(9)||chr(9)|| x_hier_attr_row_tab(row_index).ATTR_GROUP_NAME);
1125 DBMS_OUTPUT.PUT_LINE('DATA_LEVEL: ' ||chr(9)||chr(9)||chr(9)|| x_hier_attr_row_tab(row_index).DATA_LEVEL);
1126
1127 IF x_hier_attr_data_tab IS NOT NULL AND x_hier_attr_data_tab.COUNT > 0 THEN
1128 FOR data_index in x_hier_attr_data_tab.FIRST .. x_hier_attr_data_tab.LAST
1129 LOOP
1130 IF x_hier_attr_data_tab(data_index).ROW_IDENTIFIER <> x_hier_attr_row_tab(row_index).ROW_IDENTIFIER THEN
1131 EXIT;
1132 END IF;
1133
1137 DBMS_OUTPUT.PUT_LINE('ATTR_VALUE_STR: ' ||chr(9)||chr(9)||chr(9)|| x_hier_attr_data_tab(data_index).ATTR_VALUE_STR);
1134 DBMS_OUTPUT.PUT_LINE('-------------------------------------------');
1135 DBMS_OUTPUT.PUT_LINE('ROW ID: ' ||chr(9)||chr(9)||chr(9)|| x_hier_attr_data_tab(data_index).ROW_IDENTIFIER);
1136 DBMS_OUTPUT.PUT_LINE('ATTR_NAME: ' ||chr(9)||chr(9)||chr(9)|| x_hier_attr_data_tab(data_index).ATTR_NAME);
1138 DBMS_OUTPUT.PUT_LINE('ATTR_VALUE_NUM: ' ||chr(9)||chr(9)||chr(9)|| x_hier_attr_data_tab(data_index).ATTR_VALUE_NUM);
1139 DBMS_OUTPUT.PUT_LINE('ATTR_VALUE_DATE: ' ||chr(9)||chr(9)||chr(9)|| x_hier_attr_data_tab(data_index).ATTR_VALUE_DATE);
1140 DBMS_OUTPUT.PUT_LINE('ATTR_DISP_VALUE: ' ||chr(9)||chr(9)||chr(9)|| x_hier_attr_data_tab(data_index).ATTR_DISP_VALUE);
1141 DBMS_OUTPUT.PUT_LINE('ATTR_UNIT_OF_MEASURE: ' ||chr(9)||chr(9)||chr(9)|| x_hier_attr_data_tab(data_index).ATTR_UNIT_OF_MEASURE);
1142
1143 END LOOP;
1144 END IF;
1145 END LOOP;
1146 END IF;
1147
1148
1149 END;
1150
1151
1152 Procedure Get_Comp_Hier_Details_Test
1153 is
1154
1155 x_hier_header_rec rrs_hier_header_rec;
1156 x_hier_members_tab rrs_hier_members_tab;
1157 x_hier_attr_row_tab ego_user_attr_row_table;
1158 x_hier_attr_data_tab ego_user_attr_data_table;
1159 x_return_status varchar2(20);
1160 x_error_msg_count number;
1161 x_error_msg varchar2(200);
1162
1163 BEGIN
1164 RRS_HIERARCHY_INFO.Get_Complete_Hierarchy_Details(
1165 p_api_version => 1.0
1166 ,p_hier_name => 'Unit Hierarchy'
1167 ,p_hier_version_number => 1
1168 ,p_page_name => NULL
1169 ,x_hier_header_rec => x_hier_header_rec
1170 ,x_hier_members_tab => x_hier_members_tab
1171 ,x_hier_attr_row_tab => x_hier_attr_row_tab
1172 ,x_hier_attr_data_tab => x_hier_attr_data_tab
1173 ,x_return_status => x_return_status
1174 ,x_msg_count => x_error_msg_count
1175 ,x_msg_data => x_error_msg);
1176
1177 dbms_output.put_line(x_return_status);
1178 dbms_output.put_line(x_error_msg_count);
1179 dbms_output.put_line(x_error_msg);
1180
1181
1182 IF(x_hier_header_rec IS NOT NULL) THEN
1183 dbms_output.put_line(chr(13) || chr(10));
1184 dbms_output.put_line('Printing Hierarchy Header Information ');
1185 dbms_output.put_line('============================== ');
1186 dbms_output.put_line('Hierarchy ID: '||chr(9)||chr(9)||chr(9)|| x_hier_header_rec.site_group_id);
1187 dbms_output.put_line('Site Group Type Code: '||chr(9)||chr(9)||chr(9)|| x_hier_header_rec.site_group_type_code);
1188 dbms_output.put_line('Name: '||chr(9)||chr(9)||chr(9)|| x_hier_header_rec.name);
1189 dbms_output.put_line('Hierarchy Purpose Code: '||chr(9)||chr(9)||chr(9)|| x_hier_header_rec.site_group_purpose_code);
1190 dbms_output.put_line('Hierarchy Purpose: '||chr(9)||chr(9)||chr(9)|| x_hier_header_rec.site_group_purpose);
1191 dbms_output.put_line('Description: '||chr(9)||chr(9)||chr(9)|| x_hier_header_rec.description);
1192 dbms_output.put_line('Start Date: '||chr(9)||chr(9)||chr(9)|| x_hier_header_rec.start_date);
1193 dbms_output.put_line('End Date: '||chr(9)||chr(9)||chr(9)|| x_hier_header_rec.end_date);
1194
1195 END IF;
1196
1197 dbms_output.put_line(x_return_status);
1198
1199
1200 IF(x_hier_members_tab IS NOT NULL AND x_hier_members_tab.COUNT > 0) THEN
1201 dbms_output.put_line(chr(13) || chr(10));
1202 dbms_output.put_line('Printing Hierarchy Members ');
1203 dbms_output.put_line('============================== ');
1204 FOR i in x_hier_members_tab.FIRST..x_hier_members_tab.LAST LOOP
1205 dbms_output.put_line('Site Group ID: '||chr(9)||chr(9)||chr(9)|| x_hier_members_tab(i).site_group_id);
1206 dbms_output.put_line('Site Group Version ID: '||chr(9)||chr(9)||chr(9)|| x_hier_members_tab(i).site_group_version_id);
1207 dbms_output.put_line('Parent ID: '||chr(9)||chr(9)||chr(9)|| x_hier_members_tab(i).parent_id);
1208 dbms_output.put_line('Parent Identification Number: '||chr(9)||chr(9)||chr(9)|| x_hier_members_tab(i).parent_id_num);
1209 dbms_output.put_line('Parent Name: '||chr(9)||chr(9)||chr(9)|| x_hier_members_tab(i).parent_name);
1210 dbms_output.put_line('Parent Type: '||chr(9)||chr(9)||chr(9)|| x_hier_members_tab(i).parent_type);
1211 dbms_output.put_line('Parent Purpose: '||chr(9)||chr(9)||chr(9)|| x_hier_members_tab(i).parent_purpose);
1212 dbms_output.put_line('Is Root: '||chr(9)||chr(9)||chr(9)|| x_hier_members_tab(i).is_root);
1213 dbms_output.put_line('Child ID: '||chr(9)||chr(9)||chr(9)|| x_hier_members_tab(i).child_id);
1214 dbms_output.put_line('Child Identification Number: '||chr(9)||chr(9)||chr(9)|| x_hier_members_tab(i).child_id_num);
1215 dbms_output.put_line('Child Name: '||chr(9)||chr(9)||chr(9)|| x_hier_members_tab(i).child_name);
1216 dbms_output.put_line('Child Type: '||chr(9)||chr(9)||chr(9)|| x_hier_members_tab(i).child_type);
1217 dbms_output.put_line('Child Purpose Code: '||chr(9)||chr(9)||chr(9)|| x_hier_members_tab(i).child_purpose_code);
1218 dbms_output.put_line('Child Purpose: '||chr(9)||chr(9)||chr(9)|| x_hier_members_tab(i).child_purpose);
1219 dbms_output.put_line('Description: '||chr(9)||chr(9)||chr(9)|| x_hier_members_tab(i).description);
1220 dbms_output.put_line('Status Code: '||chr(9)||chr(9)||chr(9)|| x_hier_members_tab(i).status_code);
1221 dbms_output.put_line('Status: '||chr(9)||chr(9)||chr(9)|| x_hier_members_tab(i).status);
1222 dbms_output.put_line('Child Sequence: '||chr(9)||chr(9)||chr(9)|| x_hier_members_tab(i).child_sequence);
1223 dbms_output.put_line('Depth: '||chr(9)||chr(9)||chr(9)|| x_hier_members_tab(i).depth);
1224 END LOOP;
1225 END IF;
1226
1227 IF x_hier_attr_row_tab IS NOT NULL AND x_hier_attr_row_tab.COUNT > 0 THEN
1228 FOR row_index in x_hier_attr_row_tab.FIRST .. x_hier_attr_row_tab.LAST
1229 LOOP
1230 DBMS_OUTPUT.PUT_LINE('===========================================');
1231 DBMS_OUTPUT.PUT_LINE('ROW ID: ' ||chr(9)||chr(9)||chr(9)|| x_hier_attr_row_tab(row_index).ROW_IDENTIFIER);
1235 DBMS_OUTPUT.PUT_LINE('ATTR_GROUP_NAME: ' ||chr(9)||chr(9)||chr(9)|| x_hier_attr_row_tab(row_index).ATTR_GROUP_NAME);
1232 DBMS_OUTPUT.PUT_LINE('ATTR_GROUP_ID: ' ||chr(9)||chr(9)||chr(9)|| x_hier_attr_row_tab(row_index).ATTR_GROUP_ID);
1233 DBMS_OUTPUT.PUT_LINE('ATTR_GROUP_APP_ID: ' ||chr(9)||chr(9)||chr(9)|| x_hier_attr_row_tab(row_index).ATTR_GROUP_APP_ID);
1234 DBMS_OUTPUT.PUT_LINE('ATTR_GROUP_TYPE: ' ||chr(9)||chr(9)||chr(9)|| x_hier_attr_row_tab(row_index).ATTR_GROUP_TYPE);
1236 DBMS_OUTPUT.PUT_LINE('DATA_LEVEL: ' ||chr(9)||chr(9)||chr(9)|| x_hier_attr_row_tab(row_index).DATA_LEVEL);
1237
1238 IF x_hier_attr_data_tab IS NOT NULL AND x_hier_attr_data_tab.COUNT > 0 THEN
1239 FOR data_index in x_hier_attr_data_tab.FIRST .. x_hier_attr_data_tab.LAST
1240 LOOP
1241 IF x_hier_attr_data_tab(data_index).ROW_IDENTIFIER <> x_hier_attr_row_tab(row_index).ROW_IDENTIFIER THEN
1242 EXIT;
1243 END IF;
1244
1245 DBMS_OUTPUT.PUT_LINE('-------------------------------------------');
1246 DBMS_OUTPUT.PUT_LINE('ROW ID: ' ||chr(9)||chr(9)||chr(9)|| x_hier_attr_data_tab(data_index).ROW_IDENTIFIER);
1247 DBMS_OUTPUT.PUT_LINE('ATTR_NAME: ' ||chr(9)||chr(9)||chr(9)|| x_hier_attr_data_tab(data_index).ATTR_NAME);
1248 DBMS_OUTPUT.PUT_LINE('ATTR_VALUE_STR: ' ||chr(9)||chr(9)||chr(9)|| x_hier_attr_data_tab(data_index).ATTR_VALUE_STR);
1249 DBMS_OUTPUT.PUT_LINE('ATTR_VALUE_NUM: ' ||chr(9)||chr(9)||chr(9)|| x_hier_attr_data_tab(data_index).ATTR_VALUE_NUM);
1250 DBMS_OUTPUT.PUT_LINE('ATTR_VALUE_DATE: ' ||chr(9)||chr(9)||chr(9)|| x_hier_attr_data_tab(data_index).ATTR_VALUE_DATE);
1251 DBMS_OUTPUT.PUT_LINE('ATTR_DISP_VALUE: ' ||chr(9)||chr(9)||chr(9)|| x_hier_attr_data_tab(data_index).ATTR_DISP_VALUE);
1252 DBMS_OUTPUT.PUT_LINE('ATTR_UNIT_OF_MEASURE: ' ||chr(9)||chr(9)||chr(9)|| x_hier_attr_data_tab(data_index).ATTR_UNIT_OF_MEASURE);
1253
1254 END LOOP;
1255 END IF;
1256 END LOOP;
1257 END IF;
1258 END;*/
1259
1260
1261 END RRS_HIERARCHY_INFO;