[Home] [Help]
PACKAGE BODY: APPS.RRS_HIERARCHY_CRUD_PKG
Source
1 PACKAGE BODY RRS_HIERARCHY_CRUD_PKG AS
2 /* $Header: RRSHRCRB.pls 120.1.12010000.15 2010/03/03 01:58:28 pochang noship $ */
3 G_PKG_NAME CONSTANT VARCHAR2(30) := 'RRS_HIERARCHY_CRUD_PKG';
4
5 procedure Update_Hierarchy_Header(
6 p_api_version IN NUMBER DEFAULT 1,
7 p_name IN VARCHAR2,
8 p_new_name IN VARCHAR2 DEFAULT NULL,
9 p_description IN VARCHAR2 DEFAULT NULL,
10 p_purpose_code IN VARCHAR2 DEFAULT NULL,
11 p_start_date IN DATE DEFAULT NULL,
12 p_end_date IN DATE DEFAULT NULL,
13 p_nullify_flag IN VARCHAR2 DEFAULT FND_API.G_FALSE,
14 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
15 x_return_status OUT NOCOPY varchar2,
16 x_msg_count OUT NOCOPY NUMBER,
17 x_msg_data OUT NOCOPY VARCHAR2
18 )IS
19
20 v_hier_id NUMBER;
21 v_root_id NUMBER;
22 v_count NUMBER;
23 v_meaning VARCHAR2(80);
24 v_start_date DATE;
25 v_end_date DATE;
26 BEGIN
27
28 SAVEPOINT Update_Hierarchy_Header;
29
30 IF p_nullify_flag <> FND_API.G_FALSE
31 AND p_nullify_flag <> FND_API.G_TRUE THEN
32 --RRS_INVALID_FLAG
33 FND_MESSAGE.set_name('RRS', 'RRS_INVALID_FLAG');
34 FND_MSG_PUB.add;
35 RAISE FND_API.G_EXC_ERROR;
36 END IF;
37
38 BEGIN
39 SELECT SITE_GROUP_ID
40 INTO v_hier_id
41 FROM RRS_SITE_GROUPS_VL
42 WHERE NAME = p_name;
43 EXCEPTION
44 WHEN NO_DATA_FOUND THEN
45 --RRS_NO_HIER_FOUND
46 FND_MESSAGE.set_name('RRS', 'RRS_NO_HIER_FOUND');
47 FND_MESSAGE.set_token('HIERARCHY_NAME', p_name);
48 FND_MSG_PUB.add;
49 --dbms_output.put_line('invalid hier name: '||p_name);
50 RAISE FND_API.G_EXC_ERROR;
51 END;
52
53 --dbms_output.put_line(v_id);
54 --dbms_output.put_line('valueof this:'||p_description);
55
56 --dbms_output.put_line('purpose code: '||p_purpose_code);
57 IF p_purpose_code IS NOT NULL THEN
58 BEGIN
59 SELECT MEANING
60 INTO v_meaning
61 FROM RRS_LOOKUPS_V
62 WHERE LOOKUP_CODE = p_purpose_code
63 AND LOOKUP_TYPE = 'RRS_HIERARCHY_PURPOSE'
64 AND nvl(enabled_flag, 'Y') = 'Y'
65 AND nvl(start_date_active, sysdate) <= sysdate
66 AND nvl(end_date_active, sysdate) >= sysdate;
67 EXCEPTION
68 WHEN NO_DATA_FOUND THEN
69 --RRS_NO_PURPOSE_FOUND
70 FND_MESSAGE.set_name('RRS', 'RRS_NO_PURPOSE_FOUND');
71 FND_MESSAGE.set_token('PURPOSE_CODE', p_purpose_code);
72 FND_MSG_PUB.add;
73 --dbms_output.put_line('invalid purpose: '||p_purpose_code);
74 RAISE FND_API.G_EXC_ERROR;
75 END;
76 END IF;
77
78 IF p_new_name IS NOT NULL THEN
79 SELECT count(*)
80 INTO v_count
81 FROM RRS_SITE_GROUPS_TL
82 WHERE NAME = p_new_name
83 AND SITE_GROUP_ID <> v_hier_id;
84 IF v_count <> 0 THEN
85 --RRS_HIER_EXISTS
86 FND_MESSAGE.set_name('RRS', 'RRS_HIER_EXISTS');
87 FND_MESSAGE.set_token('HIERARCHY_NAME', p_new_name);
88 FND_MSG_PUB.add;
89 --dbms_output.put_line('invalid hier new name:'|| p_new_name);
90 RAISE FND_API.G_EXC_ERROR;
91 END IF;
92 END IF;
93
94 IF FND_API.To_Boolean(p_nullify_flag) AND p_new_name IS NULL THEN
95 --RRS_NULL_NAME
96 FND_MESSAGE.set_name('RRS', 'RRS_NULL_NAME');
97 FND_MSG_PUB.add;
98 --dbms_output.put_line('new name cannot be null');
99 RAISE FND_API.G_EXC_ERROR;
100 END IF;
101
102 --validate start/end date
103 IF FND_API.To_Boolean(p_nullify_flag) THEN
104 v_start_date := p_start_date;
105 v_end_date := p_end_date;
106 ELSE
107 IF p_start_date IS NULL THEN
108 SELECT START_DATE
109 INTO v_start_date
110 FROM RRS_SITE_GROUPS_B
111 WHERE SITE_GROUP_ID = v_hier_id;
112 ELSE
113 v_start_date := p_start_date;
114 END IF;
115 IF p_end_date IS NULL THEN
116 SELECT END_DATE
117 INTO v_end_date
118 FROM RRS_SITE_GROUPS_B
119 WHERE SITE_GROUP_ID = v_hier_id;
120 ELSE
121 v_end_date := p_end_date;
122 END IF;
123 END IF;
124
125 IF p_start_date IS NOT NULL
126 AND p_start_date < sysdate THEN
127 --RRS_START_DATE_PAST_ERR
128 FND_MESSAGE.set_name('RRS', 'RRS_START_DATE_PAST_ERR');
129 FND_MSG_PUB.add;
130 RAISE FND_API.G_EXC_ERROR;
131 ELSIF p_end_date IS NOT NULL
132 AND p_end_date < sysdate THEN
133 --RRS_END_DATE_PAST_ERR
134 FND_MESSAGE.set_name('RRS', 'RRS_END_DATE_PAST_ERR');
135 FND_MSG_PUB.add;
136 RAISE FND_API.G_EXC_ERROR;
137 ELSIF v_start_date IS NOT NULL
138 AND v_end_date IS NOT NULL
139 AND v_start_date > v_end_date THEN
140 --RRS_INVALID_DATE_RANGE
141 FND_MESSAGE.set_name('RRS', 'RRS_INVALID_DATE_RANGE');
142 FND_MSG_PUB.add;
143 RAISE FND_API.G_EXC_ERROR;
144 END IF;
145
146 --dbms_output.put_line('before update site group');
147
148 UPDATE RRS_SITE_GROUPS_B RSGB
149 SET RSGB.START_DATE = DECODE(p_nullify_flag, FND_API.G_TRUE, p_start_date, NVL(p_start_date, RSGB.START_DATE)),
150 RSGB.END_DATE = DECODE(p_nullify_flag, FND_API.G_TRUE, p_end_date, NVL(p_end_date, RSGB.END_DATE)),
151 RSGB.GROUP_PURPOSE_CODE = DECODE(p_nullify_flag, FND_API.G_TRUE, p_purpose_code, NVL(p_purpose_code, RSGB.GROUP_PURPOSE_CODE))
152 WHERE RSGB.SITE_GROUP_ID = v_hier_id;
153
154 UPDATE RRS_SITE_GROUPS_TL RSGT
155 SET RSGT.DESCRIPTION = DECODE(p_nullify_flag, FND_API.G_TRUE, p_description, NVL(p_description, RSGT.DESCRIPTION)),
156 RSGT.NAME = NVL(p_new_name, RSGT.NAME),
157 RSGT.SOURCE_LANG = userenv('LANG')
158 WHERE RSGT.SITE_GROUP_ID = v_hier_id
159 AND RSGT.LANGUAGE = userenv('LANG');
160
161 /*
162 IF SQL%NOTFOUND THEN
163 RAISE e_update_failed;
164 END IF; */
165
166 SELECT CHILD_MEMBER_ID
167 INTO v_root_id
168 FROM RRS_SITE_GROUP_MEMBERS
169 WHERE SITE_GROUP_ID = v_hier_id
170 AND PARENT_MEMBER_ID = -1;
171
172 --dbms_output.put_line('before update node');
173
174 UPDATE RRS_SITE_GROUP_NODES_TL RSGNT
175 SET RSGNT.NAME = NVL(p_new_name, RSGNT.NAME),
176 RSGNT.SOURCE_LANG = userenv('LANG')
177 WHERE RSGNT.SITE_GROUP_NODE_ID = v_root_id
178 AND RSGNT.LANGUAGE = userenv('LANG');
179
180 /*IF SQL%NOTFOUND THEN
181 RAISE e_update_failed;
182 END IF;*/
183
184 IF FND_API.To_Boolean(p_commit) THEN
185 COMMIT;
186 END IF;
187
188 x_return_status := FND_API.G_RET_STS_SUCCESS;
189
190 EXCEPTION
191 WHEN FND_API.G_EXC_ERROR THEN
192 ROLLBACK TO Update_Hierarchy_Header;
193 x_msg_count := FND_MSG_PUB.Count_Msg;
194 x_return_status := FND_API.G_RET_STS_ERROR;
195 WHEN OTHERS THEN
196 ROLLBACK TO Update_Hierarchy_Header;
197 x_msg_data := G_PKG_NAME || '.Update_Hierarchy_Header:' || SQLERRM;
198 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
199 END Update_Hierarchy_Header;
200
201 procedure Update_Hierarchy_Node(
202 p_api_version IN NUMBER DEFAULT 1,
203 p_number IN VARCHAR2,
204 p_name IN VARCHAR2 DEFAULT NULL,
205 p_description IN VARCHAR2 DEFAULT NULL,
206 p_purpose_code IN VARCHAR2 DEFAULT NULL,
207 p_nullify_flag IN VARCHAR2 DEFAULT FND_API.G_FALSE,
208 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
209 x_return_status OUT NOCOPY varchar2,
210 x_msg_count OUT NOCOPY NUMBER,
211 x_msg_data OUT NOCOPY VARCHAR2
212 ) IS
213
214 v_id NUMBER;
215 v_meaning VARCHAR2(80);
216 v_purpose_code VARCHAR2(30);
217 BEGIN
218
219 SAVEPOINT Update_Hierarchy_Node;
220 --dbms_output.put_line(p_number);
221
222 IF p_nullify_flag <> FND_API.G_FALSE
223 AND p_nullify_flag <> FND_API.G_TRUE THEN
224 --RRS_INVALID_FLAG
225 FND_MESSAGE.set_name('RRS', 'RRS_INVALID_FLAG');
226 FND_MSG_PUB.add;
227 RAISE FND_API.G_EXC_ERROR;
228 END IF;
229
230 BEGIN
231 SELECT SITE_GROUP_NODE_ID, NODE_PURPOSE_CODE
232 INTO v_id, v_purpose_code
233 FROM RRS_SITE_GROUP_NODES_VL RSGNV
234 WHERE RSGNV.NODE_IDENTIFICATION_NUMBER = p_number;
235 EXCEPTION
236 WHEN NO_DATA_FOUND THEN
237 --RRS_NO_NODE_FOUND
238 FND_MESSAGE.set_name('RRS', 'RRS_NO_NODE_FOUND');
239 FND_MESSAGE.set_token('NODE_ID_NUM', p_number);
240 FND_MSG_PUB.add;
241 --dbms_output.put_line('no node found for:'||p_number);
242 RAISE FND_API.G_EXC_ERROR;
243 END;
244
245 IF v_purpose_code = 'ROOT' THEN
246 --RRS_TRANSACT_ROOT
247 FND_MESSAGE.set_name('RRS', 'RRS_TRANSACT_ROOT');
248 FND_MSG_PUB.add;
249 --dbms_output.put_line('cannot transact a root node');
250 RAISE FND_API.G_EXC_ERROR;
251 END IF;
252
253 --dbms_output.put_line(v_id);
254
255 IF p_purpose_code IS NOT NULL THEN
256 BEGIN
257 SELECT MEANING
258 INTO v_meaning
259 FROM RRS_LOOKUPS_V RLV
260 WHERE RLV.LOOKUP_CODE = p_purpose_code
261 AND RLV.LOOKUP_TYPE = 'RRS_NODE_PURPOSE'
262 AND nvl(enabled_flag, 'Y') = 'Y'
263 AND nvl(start_date_active, sysdate) <= sysdate
264 AND nvl(end_date_active, sysdate) >= sysdate;
265 EXCEPTION
266 WHEN NO_DATA_FOUND THEN
267 --RRS_NO_PURPOSE_FOUND
268 FND_MESSAGE.set_name('RRS', 'RRS_NO_PURPOSE_FOUND');
269 FND_MESSAGE.set_token('PURPOSE_CODE', p_purpose_code);
270 FND_MSG_PUB.add;
271 --dbms_output.put_line('no purpose code found '||p_purpose_code);
272 RAISE FND_API.G_EXC_ERROR;
273 END;
274 END IF;
275
276 IF FND_API.To_Boolean(p_nullify_flag) AND p_name IS NULL THEN
277 --RRS_NULL_NAME
278 FND_MESSAGE.set_name('RRS', 'RRS_NULL_NAME');
279 FND_MSG_PUB.add;
280 --dbms_output.put_line('new name cannot be null');
281 RAISE FND_API.G_EXC_ERROR;
282 END IF;
283
284 UPDATE RRS_SITE_GROUP_NODES_TL RSGNT
285 SET RSGNT.DESCRIPTION = DECODE(p_nullify_flag, FND_API.G_TRUE, p_description, NVL(p_description, RSGNT.DESCRIPTION)),
286 RSGNT.NAME = NVL(p_name, RSGNT.NAME),
287 RSGNT.SOURCE_LANG = userenv('LANG')
288 WHERE RSGNT.SITE_GROUP_NODE_ID = v_id
289 AND RSGNT.LANGUAGE = userenv('LANG');
290
291 /*
292 IF SQL%NOTFOUND THEN
293 RAISE e_update_failed;
294 END IF; */
295
296 UPDATE RRS_SITE_GROUP_NODES_B RSGNB
297 SET RSGNB.NODE_PURPOSE_CODE = DECODE(p_nullify_flag, FND_API.G_TRUE, p_purpose_code, NVL(p_purpose_code, RSGNB.NODE_PURPOSE_CODE))
298 WHERE RSGNB.SITE_GROUP_NODE_ID = v_id;
299
300 /*
301 IF SQL%NOTFOUND THEN
302 RAISE e_update_failed;
303 END IF; */
304
305 IF FND_API.To_Boolean(p_commit) THEN
306 COMMIT;
307 END IF;
308
309 x_return_status := FND_API.G_RET_STS_SUCCESS;
310
311 EXCEPTION
312 WHEN FND_API.G_EXC_ERROR THEN
313 ROLLBACK TO Update_Hierarchy_Node;
314 x_msg_count := FND_MSG_PUB.Count_Msg;
315 x_return_status := FND_API.G_RET_STS_ERROR;
316 WHEN OTHERS THEN
317 ROLLBACK TO Update_Hierarchy_Node;
318 x_msg_data := G_PKG_NAME || '.Update_Hierarchy_Node:' || SQLERRM;
319 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
320 END Update_Hierarchy_Node;
321
322 procedure Create_Hierarchy_Node(
323 p_api_version IN NUMBER DEFAULT 1,
324 p_number IN VARCHAR2,
325 p_name IN VARCHAR2 DEFAULT NULL,
326 p_description IN VARCHAR2 DEFAULT NULL,
327 p_purpose_code IN VARCHAR2 DEFAULT NULL,
328 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
329 x_return_status OUT NOCOPY varchar2,
330 x_msg_count OUT NOCOPY NUMBER,
331 x_msg_data OUT NOCOPY VARCHAR2
332 ) IS
333
334 v_id NUMBER;
335 v_count NUMBER;
336 v_meaning VARCHAR2(80);
337 BEGIN
338
339 SAVEPOINT Create_Hierarchy_Node;
340
341 IF p_number IS NULL THEN
342 --RRS_ID_NUMBER_BOTH_NULL
343 FND_MESSAGE.set_name('RRS', 'RRS_ID_NUMBER_BOTH_NULL');
344 FND_MSG_PUB.add;
345 --dbms_output.put_line('Id and number cannot both be NULL');
346 RAISE FND_API.G_EXC_ERROR;
347 END IF;
348
349 SELECT count(*)
350 INTO v_count
351 FROM RRS_SITE_GROUP_NODES_VL RSGNV
352 WHERE RSGNV.NODE_IDENTIFICATION_NUMBER = p_number;
353
354 IF v_count <> 0 THEN
355 --RRS_NODE_EXISTS
356 FND_MESSAGE.set_name('RRS', 'RRS_NODE_EXISTS');
357 FND_MESSAGE.set_token('NODE_ID_NUM', p_number);
358 FND_MSG_PUB.add;
359 --dbms_output.put_line('node already exists '||p_number);
360 RAISE FND_API.G_EXC_ERROR;
361 END IF;
362
363 IF p_purpose_code IS NOT NULL THEN
364 BEGIN
365 SELECT MEANING
366 INTO v_meaning
367 FROM RRS_LOOKUPS_V RLV
368 WHERE RLV.LOOKUP_CODE = p_purpose_code
369 AND RLV.LOOKUP_TYPE = 'RRS_NODE_PURPOSE'
370 AND nvl(enabled_flag, 'Y') = 'Y'
371 AND nvl(start_date_active, sysdate) <= sysdate
372 AND nvl(end_date_active, sysdate) >= sysdate;
373 EXCEPTION
374 WHEN NO_DATA_FOUND THEN
375 --RRS_NO_PURPOSE_FOUND
376 FND_MESSAGE.set_name('RRS', 'RRS_NO_PURPOSE_FOUND');
377 FND_MESSAGE.set_token('PURPOSE_CODE', p_purpose_code);
378 FND_MSG_PUB.add;
379 --dbms_output.put_line('invalid purpose '||p_purpose_code);
380 RAISE FND_API.G_EXC_ERROR;
381 END;
382 END IF;
383
384 --dbms_output.put_line(p_name);
385 IF p_name IS NULL THEN
386 --RRS_NULL_NAME
387 FND_MESSAGE.set_name('RRS', 'RRS_NULL_NAME');
388 FND_MSG_PUB.add;
389 --dbms_output.put_line('null name');
390 RAISE FND_API.G_EXC_ERROR;
391 END IF;
392
393 SELECT RRS_SITES_S.NEXTVAL
394 INTO v_id
395 From dual;
396 --v_id := RRS_SITES_S.NEXTVAL;
397 --dbms_output.put_line(v_id);
398
399 INSERT INTO RRS_SITE_GROUP_NODES_TL
400 (
401 SITE_GROUP_NODE_ID,
402 LANGUAGE,
403 SOURCE_LANG,
404 NAME,
405 DESCRIPTION,
406 CREATED_BY,
407 CREATION_DATE,
408 LAST_UPDATED_BY,
409 LAST_UPDATE_DATE,
410 LAST_UPDATE_LOGIN
411 )
412 SELECT v_id,
413 L.LANGUAGE_CODE,
414 userenv('LANG'),
415 p_name,
416 p_description,
417 fnd_global.user_id,
418 sysdate,
419 fnd_global.user_id,
420 sysdate,
421 fnd_global.user_id
422 FROM FND_LANGUAGES L
423 WHERE L.INSTALLED_FLAG in ('I', 'B');
424
425 /*
426 IF SQL%NOTFOUND THEN
427 RAISE e_insert_failed;
428 END IF;*/
429
430 --dbms_output.put_line(v_id || ' ' || p_purpose_code || ' ' || p_number);
431
432 INSERT INTO RRS_SITE_GROUP_NODES_B
433 (
434 SITE_GROUP_NODE_ID,
435 OBJECT_VERSION_NUMBER,
436 CREATED_BY,
437 CREATION_DATE,
438 LAST_UPDATED_BY,
439 LAST_UPDATE_DATE,
440 LAST_UPDATE_LOGIN,
441 NODE_PURPOSE_CODE,
442 NODE_IDENTIFICATION_NUMBER
443 )
444 VALUES( v_id,
445 1,
446 fnd_global.user_id,
447 sysdate,
448 fnd_global.user_id,
449 sysdate,
450 fnd_global.user_id,
451 p_purpose_code,
452 p_number);
453
454 /*
455 IF SQL%NOTFOUND THEN
456 RAISE e_insert_failed;
457 END IF;*/
458
459 IF FND_API.To_Boolean(p_commit) THEN
460 COMMIT;
461 END IF;
462
463 x_return_status := FND_API.G_RET_STS_SUCCESS;
464 --x_msg_count := FND_MSG_PUB.Count_Msg;
465
466 EXCEPTION
467 WHEN FND_API.G_EXC_ERROR THEN
468 ROLLBACK TO Create_Hierarchy_Node;
469 x_msg_count := FND_MSG_PUB.Count_Msg;
470 x_return_status := FND_API.G_RET_STS_ERROR;
471 WHEN OTHERS THEN
472 ROLLBACK TO Create_Hierarchy_Node;
473 x_msg_data := G_PKG_NAME || '.Create_Hierarchy_Node:' || SQLERRM;
474 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
475 END Create_Hierarchy_Node;
476
477 procedure Create_Hierarchy_Coarse(
478 p_api_version IN NUMBER DEFAULT 1,
479 p_hier_name IN VARCHAR2,
480 p_hier_description IN VARCHAR2 DEFAULT NULL,
481 p_hier_purpose_code IN VARCHAR2 DEFAULT NULL,
482 p_hier_start_date IN DATE DEFAULT NULL,
483 p_hier_end_date IN DATE DEFAULT NULL,
484 p_hier_members_tab IN RRS_HIER_MEMBERS_COARSE_TAB DEFAULT NULL,
485 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
486 x_return_status OUT NOCOPY varchar2,
487 x_msg_count OUT NOCOPY NUMBER,
488 x_msg_data OUT NOCOPY VARCHAR2
489 ) IS
490
491 v_count NUMBER;
492 v_hier_id NUMBER;
493 v_root_id NUMBER;
494 v_hier_version_id NUMBER;
495 v_meaning VARCHAR2(80);
496 BEGIN
497
498 SAVEPOINT Create_Hierarchy_Coarse;
499
500 --check if the hier name is null
501 IF p_hier_name IS NULL THEN
502 --RRS_NULL_NAME
503 FND_MESSAGE.set_name('RRS', 'RRS_NULL_NAME');
504 FND_MSG_PUB.add;
505 RAISE FND_API.G_EXC_ERROR;
506 END IF;
507
508 --check if the hierarchy already exists
509 SELECT COUNT(*)
510 INTO v_count
511 FROM RRS_SITE_GROUPS_TL
512 WHERE NAME = p_hier_name;
513
514 IF v_count <> 0 THEN
515 --RRS_HIER_EXISTS
516 FND_MESSAGE.set_name('RRS', 'RRS_HIER_EXISTS');
517 FND_MESSAGE.set_token('HIERARCHY_NAME', p_hier_name);
518 FND_MSG_PUB.add;
519 --dbms_output.put_line('hier name already exists');
520 RAISE FND_API.G_EXC_ERROR;
521 END IF;
522 --validate the hierarchy purpose code
523 IF p_hier_purpose_code IS NOT NULL THEN
524 BEGIN
525 SELECT MEANING
526 INTO v_meaning
527 FROM RRS_LOOKUPS_V RLV
528 WHERE RLV.LOOKUP_CODE = p_hier_purpose_code
529 AND RLV.LOOKUP_TYPE = 'RRS_HIERARCHY_PURPOSE'
530 AND nvl(enabled_flag, 'Y') = 'Y'
531 AND nvl(start_date_active, sysdate) <= sysdate
532 AND nvl(end_date_active, sysdate) >= sysdate;
533 EXCEPTION
534 WHEN NO_DATA_FOUND THEN
535 --RRS_NO_PURPOSE_FOUND
536 FND_MESSAGE.set_name('RRS', 'RRS_NO_PURPOSE_FOUND');
537 FND_MESSAGE.set_token('PURPOSE_CODE', p_hier_purpose_code);
538 FND_MSG_PUB.add;
539 RAISE FND_API.G_EXC_ERROR;
540 END;
541 END IF;
542
543 --validate start/end date
544 IF p_hier_start_date IS NOT NULL
545 AND p_hier_start_date < sysdate THEN
546 --RRS_START_DATE_PAST_ERR
547 FND_MESSAGE.set_name('RRS', 'RRS_START_DATE_PAST_ERR');
548 FND_MSG_PUB.add;
549 RAISE FND_API.G_EXC_ERROR;
550 ELSIF p_hier_end_date IS NOT NULL
551 AND p_hier_end_date < sysdate THEN
552 --RRS_END_DATE_PAST_ERR
553 FND_MESSAGE.set_name('RRS', 'RRS_END_DATE_PAST_ERR');
554 FND_MSG_PUB.add;
555 RAISE FND_API.G_EXC_ERROR;
556 ELSIF p_hier_start_date IS NOT NULL
557 AND p_hier_end_date IS NOT NULL
558 AND p_hier_start_date > p_hier_end_date THEN
559 --RRS_INVALID_DATE_RANGE
560 FND_MESSAGE.set_name('RRS', 'RRS_INVALID_DATE_RANGE');
561 FND_MSG_PUB.add;
562 RAISE FND_API.G_EXC_ERROR;
563 END IF;
564
565 --retrieve new site group id from the sequence
566 SELECT RRS_SITE_GROUPS_S.NEXTVAL
567 INTO v_hier_id
568 From dual;
569 --v_hier_id := RRS_SITE_GROUPS_S.NEXTVAL;
570 --dbms_output.put_line('hier_id: '||v_hier_id);
571 --insert new row into RRS_SITE_GROUPS_TL table
572 INSERT INTO RRS_SITE_GROUPS_TL
573 (
574 SITE_GROUP_ID,
575 LANGUAGE,
576 SOURCE_LANG,
577 NAME,
578 DESCRIPTION,
579 CREATED_BY,
580 CREATION_DATE,
581 LAST_UPDATED_BY,
582 LAST_UPDATE_DATE,
583 LAST_UPDATE_LOGIN
584 )
585 SELECT v_hier_id,
586 L.LANGUAGE_CODE,
587 userenv('LANG'),
588 p_hier_name,
589 p_hier_description,
590 fnd_global.user_id,
591 sysdate,
592 fnd_global.user_id,
593 sysdate,
594 fnd_global.user_id
595 FROM FND_LANGUAGES L
596 WHERE L.INSTALLED_FLAG in ('I', 'B');
597
598 /*
599 IF SQL%NOTFOUND THEN
600 RAISE e_insert_failed;
601 END IF;*/
602
603 --insert new row into RRS_SITE_GROUPS_B table
604 INSERT INTO RRS_SITE_GROUPS_B
605 (
606 SITE_GROUP_ID,
607 SITE_GROUP_TYPE_CODE,
608 START_DATE,
609 END_DATE,
610 OBJECT_VERSION_NUMBER,
611 CREATED_BY,
612 CREATION_DATE,
613 LAST_UPDATED_BY,
614 LAST_UPDATE_DATE,
615 LAST_UPDATE_LOGIN,
616 GROUP_PURPOSE_CODE
617 )
618 VALUES( v_hier_id,
619 'H',
620 p_hier_start_date,
621 p_hier_end_date,
622 1,
623 fnd_global.user_id,
624 sysdate,
625 fnd_global.user_id,
626 sysdate,
627 fnd_global.user_id,
628 p_hier_purpose_code);
629 /*
630 IF SQL%NOTFOUND THEN
631 RAISE e_insert_failed;
632 END IF;*/
633
634 --retrieve new version id from the sequence
635 SELECT RRS_SITE_GROUP_VERSIONS_S.NEXTVAL
636 INTO v_hier_version_id
637 From dual;
638 --v_hier_version_id := RRS_SITE_GROUP_VERSIONS_S.NEXTVAL;
639 --dbms_output.put_line(v_hier_version_id);
640 --insert new row into RRS_SITE_GROUP_VERSIONS table
641 INSERT INTO RRS_SITE_GROUP_VERSIONS
642 (
643 SITE_GROUP_VERSION_ID,
644 SITE_GROUP_ID,
645 VERSION_NUMBER,
646 SOURCE_VERSION_ID,
647 OBJECT_VERSION_NUMBER,
648 CREATED_BY,
649 CREATION_DATE,
650 LAST_UPDATED_BY,
651 LAST_UPDATE_DATE,
652 LAST_UPDATE_LOGIN
653 )
654 VALUES( v_hier_version_id,
655 v_hier_id,
656 1,
657 v_hier_version_id,
658 1,
659 fnd_global.user_id,
660 sysdate,
661 fnd_global.user_id,
662 sysdate,
663 fnd_global.user_id);
664
665 /*
666 IF SQL%NOTFOUND THEN
667 RAISE e_insert_failed;
668 END IF;*/
669
670 --creat new root node
671 --retrieve new node id from the sequence
672 SELECT RRS_SITES_S.NEXTVAL
673 INTO v_root_id
674 From dual;
675 --v_root_id := RRS_SITES_S.NEXTVAL;
676 --insert new row into RRS_SITE_GROUP_NODES_TL table
677 INSERT INTO RRS_SITE_GROUP_NODES_TL
678 (
679 SITE_GROUP_NODE_ID,
680 LANGUAGE,
681 SOURCE_LANG,
682 NAME,
683 DESCRIPTION,
684 CREATED_BY,
685 CREATION_DATE,
686 LAST_UPDATED_BY,
687 LAST_UPDATE_DATE,
688 LAST_UPDATE_LOGIN
689 )
690 SELECT v_root_id,
691 L.LANGUAGE_CODE,
692 userenv('LANG'),
693 p_hier_name,
694 NULL,
695 fnd_global.user_id,
696 sysdate,
697 fnd_global.user_id,
698 sysdate,
699 fnd_global.user_id
700 FROM FND_LANGUAGES L
701 WHERE L.INSTALLED_FLAG in ('I', 'B');
702 /*
703 IF SQL%NOTFOUND THEN
704 RAISE e_insert_failed;
705 END IF;*/
706
707 --insert new row into RRS_SITE_GROUP_NODES_B table
708 INSERT INTO RRS_SITE_GROUP_NODES_B
709 (
710 SITE_GROUP_NODE_ID,
711 OBJECT_VERSION_NUMBER,
712 CREATED_BY,
713 CREATION_DATE,
714 LAST_UPDATED_BY,
715 LAST_UPDATE_DATE,
716 LAST_UPDATE_LOGIN,
717 NODE_PURPOSE_CODE,
718 NODE_IDENTIFICATION_NUMBER
719 )
720 VALUES( v_root_id,
721 1,
722 fnd_global.user_id,
723 sysdate,
724 fnd_global.user_id,
725 sysdate,
726 fnd_global.user_id,
727 'ROOT',
728 v_root_id);
729 /*
730 IF SQL%NOTFOUND THEN
731 RAISE e_insert_failed;
732 END IF;*/
733
734 Create_Hierarchy_Members(
735 p_hier_version_id => v_hier_version_id,
736 p_hier_id => v_hier_id,
737 p_root_id => v_root_id,
738 p_root_number => null,
739 p_hier_purpose_code => p_hier_purpose_code,
740 p_hier_members_tab => p_hier_members_tab,
741 x_return_status => x_return_status,
742 x_msg_count => x_msg_count,
743 x_msg_data => x_msg_data);
744
745 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
746 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
747 RAISE FND_API.G_EXC_ERROR;
748 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
749 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
750 END IF;
751 END IF;
752
753 IF FND_API.To_Boolean(p_commit) THEN
754 COMMIT;
755 END IF;
756
757 x_return_status := FND_API.G_RET_STS_SUCCESS;
758
759 EXCEPTION
760 WHEN FND_API.G_EXC_ERROR THEN
761 ROLLBACK TO Create_Hierarchy_Coarse;
762 x_msg_count := FND_MSG_PUB.Count_Msg;
763 x_return_status := FND_API.G_RET_STS_ERROR;
764 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
765 ROLLBACK TO Create_Hierarchy_Coarse;
766 x_msg_data := G_PKG_NAME || '.Create_Hierarchy_Coarse:' || x_msg_data;
767 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
768 WHEN OTHERS THEN
769 ROLLBACK TO Create_Hierarchy_Coarse;
770 x_msg_data := G_PKG_NAME || '.Create_Hierarchy_Coarse:' || SQLERRM;
771 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
772 END Create_Hierarchy_Coarse;
773
774 procedure Validate_Rules_For_Members(
775 p_hier_purpose_code IN VARCHAR2,
776 x_return_status OUT NOCOPY VARCHAR2,
777 x_msg_data OUT NOCOPY VARCHAR2
778 )IS
779 CURSOR validate_rules_cursor IS
780 SELECT PARENT_TYPE, PARENT_NUMBER, CHILD_TYPE, CHILD_NUMBER
781 FROM RRS_SITE_GROUP_MEMBERS_TEMP
782 WHERE CHILD_ID NOT IN (
783 SELECT CHILD_ID
784 FROM RRS_SITE_GROUP_MEMBERS_TEMP RSGMT, RRS_GROUP_RULES RGR
785 WHERE RGR.SITE_GROUP_TYPE_CODE = 'H'
786 AND RGR.GROUP_PURPOSE_CODE = p_hier_purpose_code
787 AND RGR.RELATIONSHIP_TYPE = 'PARENT_CHILD'
788 AND RSGMT.PARENT_TYPE = RGR.OBJECT1
789 AND RSGMT.PARENT_PURPOSE_CODE = RGR.CLASSIFICATION_CODE1
790 AND RSGMT.CHILD_TYPE = RGR.OBJECT2
791 AND RSGMT.CHILD_PURPOSE_CODE = RGR.CLASSIFICATION_CODE2);
792
793 v_count NUMBER;
794 v_p_num VARCHAR2(30);
795 v_p_type VARCHAR2(30);
796 v_c_num VARCHAR2(30);
797 v_c_type VARCHAR2(30);
798
799 BEGIN
800 --initialize the return status
801 x_return_status := FND_API.G_RET_STS_SUCCESS;
802
803 --dbms_output.put_line('before RulesFwk');
804 SELECT COUNT(*)
805 INTO v_count
806 FROM (
807 SELECT *
808 FROM RRS_GROUP_RULES RGR
809 WHERE RGR.GROUP_PURPOSE_CODE = p_hier_purpose_code) TMP;
810
811 IF v_count <> 0 THEN
812
813 OPEN validate_rules_cursor;
814 LOOP
815 FETCH validate_rules_cursor INTO v_p_type, v_p_num, v_c_type, v_c_num;
816 EXIT WHEN validate_rules_cursor%NOTFOUND OR validate_rules_cursor%NOTFOUND IS NULL;
817 --RRS_NO_RULE_FOUND
818 FND_MESSAGE.set_name('RRS', 'RRS_NO_RULE_FOUND');
819 FND_MESSAGE.set_token('P_TYPE', v_p_type);
820 FND_MESSAGE.set_token('P_NUM', v_p_num);
821 FND_MESSAGE.set_token('C_TYPE', v_c_type);
822 FND_MESSAGE.set_token('C_NUM', v_c_num);
823 FND_MSG_PUB.add;
824 --dbms_output.put_line('The following member violates the RulesFwk: '||v_p_type||'/'||v_p_num||'/'||v_c_type||'/'||v_c_num);
825 x_return_status := FND_API.G_RET_STS_ERROR;
826 END LOOP;
827 CLOSE validate_rules_cursor;
828
829 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
830 RAISE FND_API.G_EXC_ERROR;
831 END IF;
832
833 END IF;
834
835 x_return_status := FND_API.G_RET_STS_SUCCESS;
836
837 EXCEPTION
838 WHEN FND_API.G_EXC_ERROR THEN
839 x_return_status := FND_API.G_RET_STS_ERROR;
840 WHEN OTHERS THEN
841 x_msg_data := G_PKG_NAME || '.Validate_Rules_For_Members:' || SQLERRM;
842 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
843 END Validate_Rules_For_Members;
844
845 procedure Validate_Rules_For_Child(
846 p_hier_purpose_code IN VARCHAR2,
847 p_parent_id_number IN VARCHAR2,
848 p_parent_object_type IN VARCHAR2,
849 p_parent_purpose_code IN VARCHAR2,
850 p_child_id_number IN VARCHAR2,
851 p_child_object_type IN VARCHAR2,
852 p_child_purpose_code IN VARCHAR2,
853 x_return_status OUT NOCOPY VARCHAR2,
854 x_msg_data OUT NOCOPY VARCHAR2
855 )IS
856
857 v_count NUMBER;
858
859 BEGIN
860
861 --dbms_output.put_line('before RulesFwk');
862 SELECT COUNT(*)
863 INTO v_count
864 FROM (
865 SELECT *
866 FROM RRS_GROUP_RULES RGR
867 WHERE RGR.GROUP_PURPOSE_CODE = p_hier_purpose_code) TMP;
868 IF v_count <> 0 THEN
869 SELECT COUNT(*)
870 INTO v_count
871 FROM RRS_GROUP_RULES RGR
872 WHERE RGR.SITE_GROUP_TYPE_CODE = 'H'
873 AND RGR.GROUP_PURPOSE_CODE = p_hier_purpose_code
874 AND RGR.RELATIONSHIP_TYPE = 'PARENT_CHILD'
875 AND RGR.OBJECT1 = p_parent_object_type
876 AND RGR.CLASSIFICATION_CODE1 = p_parent_purpose_code
877 AND RGR.OBJECT2 = p_child_object_type
878 AND RGR.CLASSIFICATION_CODE2 = p_child_purpose_code;
879 IF v_count = 0 THEN
880 --RRS_NO_RULE_FOUND
881 FND_MESSAGE.set_name('RRS', 'RRS_NO_RULE_FOUND');
882 FND_MESSAGE.set_token('P_TYPE', p_parent_object_type);
883 FND_MESSAGE.set_token('P_NUM', p_parent_id_number);
884 FND_MESSAGE.set_token('C_TYPE', p_child_object_type);
885 FND_MESSAGE.set_token('C_NUM', p_child_id_number);
886 FND_MSG_PUB.add;
887 --dbms_output.put_line('Rules validation failed');
888 RAISE FND_API.G_EXC_ERROR;
889 END IF;
890 END IF;
891
892 x_return_status := FND_API.G_RET_STS_SUCCESS;
893
894 EXCEPTION
895 WHEN FND_API.G_EXC_ERROR THEN
896 x_return_status := FND_API.G_RET_STS_ERROR;
897 WHEN OTHERS THEN
898 x_msg_data := G_PKG_NAME || '.Validate_Rules_For_Child:' || SQLERRM;
899 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
900 END Validate_Rules_For_Child;
901
902 procedure Create_Hierarchy_Members(
903 p_hier_version_id IN NUMBER,
904 p_hier_id IN NUMBER,
905 p_root_id IN NUMBER,
906 p_root_number IN VARCHAR2,
907 p_hier_purpose_code IN VARCHAR2,
908 p_hier_members_tab IN RRS_HIER_MEMBERS_COARSE_TAB,
909 x_return_status OUT NOCOPY varchar2,
910 x_msg_count OUT NOCOPY NUMBER,
911 x_msg_data OUT NOCOPY VARCHAR2
912 )IS
913
914 v_count NUMBER;
915 v_root_id NUMBER;
916 v_root_number VARCHAR(30);
917 v_node_name VARCHAR2(150);
918 v_node_description VARCHAR(2000);
919 v_meaning VARCHAR2(80);
920 v_purpose_code VARCHAR2(30);
921 v_id NUMBER;
922 v_num VARCHAR2(30);
923 v_type VARCHAR2(30);
924 v_p_num VARCHAR2(30);
925 v_p_type VARCHAR2(30);
926 v_c_num VARCHAR2(30);
927 v_c_type VARCHAR2(30);
928
929 CURSOR new_nodes_cursor IS
930 SELECT *
931 FROM RRS_SITE_GROUP_MEMBERS_TEMP
932 WHERE CHILD_TYPE = 'NODE'
933 AND CHILD_ID IS NULL
934 AND CHILD_NUMBER IS NOT NULL
935 AND CHILD_NUMBER NOT IN (
936 SELECT NODE_IDENTIFICATION_NUMBER
937 FROM RRS_SITE_GROUP_NODES_VL
938 WHERE NODE_IDENTIFICATION_NUMBER IS NOT NULL);
939
940 CURSOR update_nodes_cursor IS
941 SELECT *
942 FROM RRS_SITE_GROUP_MEMBERS_TEMP
943 WHERE CHILD_TYPE = 'NODE'
944 AND CHILD_NUMBER IS NOT NULL
945 AND (CHILD_PURPOSE_CODE IS NOT NULL
946 OR CHILD_NODE_NAME IS NOT NULL
947 OR CHILD_NODE_DESCRIPTION IS NOT NULL);
948
949 CURSOR new_members_cursor IS
950 SELECT *
951 FROM RRS_SITE_GROUP_MEMBERS_TEMP;
952
953 CURSOR validate_p_id_num_cursor IS
954 SELECT PARENT_ID, PARENT_NUMBER
955 FROM RRS_SITE_GROUP_MEMBERS_TEMP
956 WHERE (PARENT_ID IS NOT NULL AND PARENT_NUMBER IS NOT NULL)
957 AND ((PARENT_ID, PARENT_NUMBER) NOT IN (SELECT SITE_ID, SITE_IDENTIFICATION_NUMBER FROM RRS_SITES_VL)
958 AND (PARENT_ID, PARENT_NUMBER) NOT IN (SELECT SITE_GROUP_NODE_ID, NODE_IDENTIFICATION_NUMBER FROM RRS_SITE_GROUP_NODES_VL));
959
960 CURSOR validate_c_id_num_cursor IS
961 SELECT CHILD_ID, CHILD_NUMBER
962 FROM RRS_SITE_GROUP_MEMBERS_TEMP
963 WHERE (CHILD_ID IS NOT NULL AND CHILD_NUMBER IS NOT NULL)
964 AND ((CHILD_ID, CHILD_NUMBER) NOT IN (SELECT SITE_ID, SITE_IDENTIFICATION_NUMBER FROM RRS_SITES_VL)
965 AND (CHILD_ID, CHILD_NUMBER) NOT IN (SELECT SITE_GROUP_NODE_ID, NODE_IDENTIFICATION_NUMBER FROM RRS_SITE_GROUP_NODES_VL));
966
967 CURSOR validate_p_id_cursor IS
968 SELECT PARENT_TYPE, PARENT_ID, PARENT_NUMBER
969 FROM RRS_SITE_GROUP_MEMBERS_TEMP RSGMT
970 WHERE PARENT_ID IS NOT NULL
971 AND ((PARENT_TYPE = 'SITE'
972 AND NOT EXISTS (
973 SELECT SITE_ID
974 FROM RRS_SITES_B
975 WHERE SITE_ID = RSGMT.PARENT_ID))
976 OR (PARENT_TYPE = 'NODE'
977 AND NOT EXISTS (
978 SELECT SITE_GROUP_NODE_ID
979 FROM RRS_SITE_GROUP_NODES_B
980 WHERE SITE_GROUP_NODE_ID = RSGMT.PARENT_ID)));
981
982 CURSOR validate_c_id_cursor IS
983 SELECT CHILD_TYPE, CHILD_ID, CHILD_NUMBER
984 FROM RRS_SITE_GROUP_MEMBERS_TEMP RSGMT
985 WHERE CHILD_ID IS NOT NULL
986 AND ((CHILD_TYPE = 'SITE'
987 AND NOT EXISTS (
988 SELECT SITE_ID
989 FROM RRS_SITES_B
990 WHERE SITE_ID = RSGMT.CHILD_ID))
991 OR (CHILD_TYPE = 'NODE'
992 AND NOT EXISTS (
993 SELECT SITE_GROUP_NODE_ID
994 FROM RRS_SITE_GROUP_NODES_B
995 WHERE SITE_GROUP_NODE_ID = RSGMT.CHILD_ID)));
996
997 CURSOR validate_dup_number_cursor IS
998 SELECT CHILD_TYPE, CHILD_NUMBER
999 FROM RRS_SITE_GROUP_MEMBERS_TEMP
1000 GROUP BY CHILD_TYPE, CHILD_NUMBER
1001 HAVING COUNT(*) > 1;
1002
1003 CURSOR validate_num_cursor IS
1004 SELECT DECODE(PARENT_ID, NULL, PARENT_NUMBER, CHILD_NUMBER), DECODE(PARENT_ID, NULL, PARENT_TYPE, CHILD_TYPE)
1005 FROM RRS_SITE_GROUP_MEMBERS_TEMP
1006 WHERE CHILD_ID IS NULL
1007 OR PARENT_ID IS NULL;
1008
1009 CURSOR validate_site_template_cursor IS
1010 SELECT CHILD_NUMBER
1011 FROM RRS_SITE_GROUP_MEMBERS_TEMP RSGMT, RRS_SITES_VL RSV
1012 WHERE RSGMT.CHILD_ID = RSV.SITE_ID
1013 AND RSGMT.CHILD_TYPE = 'SITE'
1014 AND IS_TEMPLATE_FLAG = 'Y';
1015
1016 CURSOR validate_node_name_cursor IS
1017 SELECT RSGNV.NAME
1018 FROM RRS_SITE_GROUP_MEMBERS_TEMP RSGMT, RRS_SITE_GROUP_NODES_VL RSGNV
1019 WHERE RSGMT.CHILD_ID = RSGNV.SITE_GROUP_NODE_ID
1020 GROUP BY RSGMT.PARENT_ID, RSGNV.NAME
1021 HAVING COUNT(*) > 1;
1022
1023 CURSOR validate_p_in_hier_curosr IS
1024 SELECT RSGMT.CHILD_TYPE, RSGMT.CHILD_NUMBER
1025 FROM RRS_SITE_GROUP_MEMBERS_TEMP RSGMT
1026 WHERE RSGMT.PARENT_ID <> p_root_id
1027 AND (RSGMT.PARENT_TYPE, RSGMT.PARENT_NUMBER) NOT IN
1028 (SELECT RSGMT2.CHILD_TYPE, RSGMT2.CHILD_NUMBER
1029 FROM RRS_SITE_GROUP_MEMBERS_TEMP RSGMT2
1030 START WITH RSGMT2.PARENT_ID = p_root_id
1031 CONNECT BY PRIOR RSGMT2.CHILD_ID = RSGMT2.PARENT_ID);
1032
1033 BEGIN
1034
1035 SAVEPOINT Create_Hierarchy_Members;
1036
1037 --initialize the return status
1038 x_return_status := FND_API.G_RET_STS_SUCCESS;
1039
1040 --insert new row into RRS_SITE_GROUP_MEMBERS table
1041 INSERT INTO RRS_SITE_GROUP_MEMBERS
1042 (
1043 SITE_GROUP_VERSION_ID,
1044 SITE_GROUP_ID,
1045 PARENT_MEMBER_ID,
1046 CHILD_MEMBER_ID,
1047 DELETED_FLAG,
1048 OBJECT_VERSION_NUMBER,
1049 CREATED_BY,
1050 CREATION_DATE,
1051 LAST_UPDATED_BY,
1052 LAST_UPDATE_DATE,
1053 LAST_UPDATE_LOGIN,
1054 SEQUENCE_NUMBER
1055 )
1056 VALUES( p_hier_version_id,
1057 p_hier_id,
1058 -1,
1059 p_root_id,
1060 'N',
1061 1,
1062 fnd_global.user_id,
1063 sysdate,
1064 fnd_global.user_id,
1065 sysdate,
1066 fnd_global.user_id,
1067 null);
1068
1069 /*
1070 IF SQL%NOTFOUND THEN
1071 RAISE e_insert_failed;
1072 END IF;*/
1073
1074 IF p_hier_members_tab IS NOT NULL THEN --members list is specify
1075 --delete rows in RRS_SITE_GROUP_MEMBERS_TEMP
1076 DELETE FROM RRS_SITE_GROUP_MEMBERS_TEMP;
1077
1078 FOR i in p_hier_members_tab.FIRST..p_hier_members_tab.LAST LOOP
1079
1080 IF p_hier_members_tab(i).child_object_type <> 'SITE' AND
1081 p_hier_members_tab(i).child_object_type <> 'NODE' THEN
1082 --RRS_INVALID_TYPE
1083 FND_MESSAGE.set_name('RRS', 'RRS_INVALID_TYPE');
1084 FND_MESSAGE.set_token('TYPE', p_hier_members_tab(i).child_object_type);
1085 FND_MSG_PUB.add;
1086 --dbms_output.put_line('invalid transaction type');
1087 RAISE FND_API.G_EXC_ERROR;
1088 END IF;
1089
1090 INSERT INTO RRS_SITE_GROUP_MEMBERS_TEMP
1091 (
1092 PARENT_TYPE,
1093 PARENT_ID,
1094 PARENT_NUMBER,
1095 PARENT_PURPOSE_CODE,
1096 CHILD_TYPE,
1097 CHILD_ID,
1098 CHILD_NUMBER,
1099 CHILD_PURPOSE_CODE,
1100 SEQUENCE_NUMBER,
1101 CHILD_NODE_NAME,
1102 CHILD_NODE_DESCRIPTION
1103 )
1104 VALUES( p_hier_members_tab(i).parent_object_type,
1105 p_hier_members_tab(i).parent_id,
1106 p_hier_members_tab(i).parent_id_number,
1107 NULL,
1108 p_hier_members_tab(i).child_object_type,
1109 p_hier_members_tab(i).child_id,
1110 p_hier_members_tab(i).child_id_number,
1111 p_hier_members_tab(i).child_node_purpose_code,
1112 p_hier_members_tab(i).child_seq_number,
1113 p_hier_members_tab(i).child_node_name,
1114 p_hier_members_tab(i).child_node_description
1115 );
1116 /*
1117 IF SQL%NOTFOUND THEN
1118 RAISE e_insert_failed;
1119 END IF;*/
1120 END LOOP;
1121
1122 --check the number of root and the purpose code of the root node
1123 BEGIN
1124 SELECT RSGMT.CHILD_ID, RSGMT.CHILD_NUMBER
1125 INTO v_root_id, v_root_number
1126 FROM RRS_SITE_GROUP_MEMBERS_TEMP RSGMT
1127 WHERE (RSGMT.PARENT_ID = -1 OR RSGMT.PARENT_TYPE = 'NONE')
1128 AND (RSGMT.CHILD_ID IS NOT NULL OR RSGMT.CHILD_NUMBER IS NOT NULL)
1129 AND RSGMT.CHILD_TYPE = 'NODE';
1130 EXCEPTION
1131 WHEN NO_DATA_FOUND THEN
1132 --RRS_NO_ROOT_FOUND
1133 FND_MESSAGE.set_name('RRS', 'RRS_NO_ROOT_FOUND');
1134 FND_MSG_PUB.add;
1135 --dbms_output.put_line('no root found');
1136 RAISE FND_API.G_EXC_ERROR;
1137 WHEN TOO_MANY_ROWS THEN
1138 --RRS_MANY_ROOTS_FOUND
1139 FND_MESSAGE.set_name('RRS', 'RRS_MANY_ROOTS_FOUND');
1140 FND_MSG_PUB.add;
1141 --dbms_output.put_line('too many roots found');
1142 RAISE FND_API.G_EXC_ERROR;
1143 END;
1144
1145 --validate the root number user input with the one in DB
1146 IF (p_root_number IS NOT NULL AND v_root_number IS NOT NULL)
1147 AND p_root_number <> v_root_number THEN
1148 --RRS_NO_ROOT_FOUND
1149 FND_MESSAGE.set_name('RRS', 'RRS_NO_ROOT_FOUND');
1150 FND_MSG_PUB.add;
1151 --dbms_output.put_line('no root found');
1152 RAISE FND_API.G_EXC_ERROR;
1153 END IF;
1154
1155 IF v_root_id IS NOT NULL AND p_root_id <> v_root_id THEN
1156 --RRS_NO_ROOT_FOUND
1157 FND_MESSAGE.set_name('RRS', 'RRS_NO_ROOT_FOUND');
1158 FND_MSG_PUB.add;
1159 --dbms_output.put_line('no root found');
1160 RAISE FND_API.G_EXC_ERROR;
1161 END IF;
1162
1163 IF p_root_number IS NULL AND v_root_number IS NOT NULL THEN
1164 SELECT COUNT(*)
1165 INTO v_count
1166 FROM RRS_SITE_GROUP_NODES_VL
1167 WHERE NODE_IDENTIFICATION_NUMBER = v_root_number
1168 AND SITE_GROUP_NODE_ID <> p_root_id;
1169
1170 IF v_count <> 0 THEN
1171 --RRS_NODE_EXISTS
1172 FND_MESSAGE.set_name('RRS', 'RRS_NODE_EXISTS');
1173 FND_MESSAGE.set_token('NODE_ID_NUM', v_root_number);
1174 FND_MSG_PUB.add;
1175 --dbms_output.put_line('node number already exists '||v_root_number);
1176 RAISE FND_API.G_EXC_ERROR;
1177 END IF;
1178
1179 --update the root node number
1180 UPDATE RRS_SITE_GROUP_NODES_B
1181 SET NODE_IDENTIFICATION_NUMBER = v_root_number
1182 WHERE SITE_GROUP_NODE_ID = p_root_id;
1183 END IF;
1184
1185 --delete the root node record
1186 DELETE FROM RRS_SITE_GROUP_MEMBERS_TEMP
1187 WHERE (PARENT_ID = -1 OR PARENT_TYPE = 'NONE')
1188 AND (CHILD_ID IS NOT NULL OR CHILD_NUMBER IS NOT NULL)
1189 AND CHILD_TYPE = 'NODE';
1190
1191 --validate id/number
1192 --case1: both null
1193 SELECT COUNT(*)
1194 INTO v_count
1195 FROM RRS_SITE_GROUP_MEMBERS_TEMP
1196 WHERE (PARENT_ID IS NULL AND PARENT_NUMBER IS NULL)
1197 OR (CHILD_ID IS NULL AND CHILD_NUMBER IS NULL);
1198
1199 IF v_count <> 0 THEN
1200 --RRS_ID_NUMBER_BOTH_NULL
1201 FND_MESSAGE.set_name('RRS', 'RRS_ID_NUMBER_BOTH_NULL');
1202 FND_MSG_PUB.add;
1203 --dbms_output.put_line('Id and number cannot both be NULL');
1204 RAISE FND_API.G_EXC_ERROR;
1205 END IF;
1206
1207 --case2: both not null
1208 --dbms_output.put_line('before case2');
1209
1210 OPEN validate_p_id_num_cursor;
1211 LOOP
1212 FETCH validate_p_id_num_cursor INTO v_id, v_num;
1213 EXIT WHEN validate_p_id_num_cursor%NOTFOUND OR validate_p_id_num_cursor%NOTFOUND IS NULL;
1214 --RRS_INVALID_ID_NUMBER_PAIR
1215 FND_MESSAGE.set_name('RRS', 'RRS_INVALID_ID_NUMBER_PAIR');
1216 FND_MESSAGE.set_token('ID', v_id);
1217 FND_MESSAGE.set_token('NUM', v_num);
1218 FND_MSG_PUB.add;
1219 --dbms_output.put_line('Invalid pair of id/number: '||v_id||'/'||v_num);
1220 x_return_status := FND_API.G_RET_STS_ERROR;
1221 END LOOP;
1222 CLOSE validate_p_id_num_cursor;
1223
1224 OPEN validate_c_id_num_cursor;
1225 LOOP
1226 FETCH validate_c_id_num_cursor INTO v_id, v_num;
1227 EXIT WHEN validate_c_id_num_cursor%NOTFOUND OR validate_c_id_num_cursor%NOTFOUND IS NULL;
1228 --RRS_INVALID_ID_NUMBER_PAIR
1229 FND_MESSAGE.set_name('RRS', 'RRS_INVALID_ID_NUMBER_PAIR');
1230 FND_MESSAGE.set_token('ID', v_id);
1231 FND_MESSAGE.set_token('NUM', v_num);
1232 FND_MSG_PUB.add;
1233 --dbms_output.put_line('Invalid pair of id/number: '||v_id||'/'||v_num);
1234 x_return_status := FND_API.G_RET_STS_ERROR;
1235 END LOOP;
1236 CLOSE validate_c_id_num_cursor;
1237
1238 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1239 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1240 RAISE FND_API.G_EXC_ERROR;
1241 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1242 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1243 END IF;
1244 END IF;
1245
1246 --check for non-existing id
1247 --dbms_output.put_line('before check non-existing id');
1248 OPEN validate_p_id_cursor;
1249 LOOP
1250 FETCH validate_p_id_cursor INTO v_type, v_id, v_num;
1251 EXIT WHEN validate_p_id_cursor%NOTFOUND OR validate_p_id_cursor%NOTFOUND IS NULL;
1252 --RRS_NO_ID_FOUND
1253 FND_MESSAGE.set_name('RRS', 'RRS_NO_ID_FOUND');
1254 FND_MESSAGE.set_token('ID', v_id);
1255 FND_MESSAGE.set_token('TYPE', v_type);
1256 FND_MSG_PUB.add;
1257 --dbms_output.put_line('Non-existing type/id: '||v_type||'/'||v_id);
1258 x_return_status := FND_API.G_RET_STS_ERROR;
1259 END LOOP;
1260 CLOSE validate_p_id_cursor;
1261
1262 OPEN validate_c_id_cursor;
1263 LOOP
1264 FETCH validate_c_id_cursor INTO v_type, v_id, v_num;
1265 EXIT WHEN validate_c_id_cursor%NOTFOUND OR validate_c_id_cursor%NOTFOUND IS NULL;
1266 --RRS_NO_ID_FOUND
1267 FND_MESSAGE.set_name('RRS', 'RRS_NO_ID_FOUND');
1268 FND_MESSAGE.set_token('ID', v_id);
1269 FND_MESSAGE.set_token('TYPE', v_type);
1270 FND_MSG_PUB.add;
1271 --dbms_output.put_line('Non-existing type/id: '||v_type||'/'||v_id);
1272 x_return_status := FND_API.G_RET_STS_ERROR;
1273 END LOOP;
1274 CLOSE validate_c_id_cursor;
1275
1276 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1277 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1278 RAISE FND_API.G_EXC_ERROR;
1279 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1280 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1281 END IF;
1282 END IF;
1283
1284 --create non-existing node
1285 --dbms_output.put_line('before creat new node');
1286 FOR node_rec IN new_nodes_cursor LOOP
1287 --dbms_output.put_line('Inside new node');
1288 /*
1289 IF node_rec.CHILD_NODE_NAME IS NULL THEN
1290 --RRS_NULL_NAME
1291 --dbms_output.put_line('New node name cannot be null');
1292 RAISE e_other;
1293 END IF;*/
1294
1295 Create_Hierarchy_Node(
1296 p_number => node_rec.CHILD_NUMBER,
1297 p_name => node_rec.CHILD_NODE_NAME,
1298 p_purpose_code => node_rec.CHILD_PURPOSE_CODE,
1299 p_description => node_rec.CHILD_NODE_DESCRIPTION,
1300 x_return_status => x_return_status,
1301 x_msg_count => x_msg_count,
1302 x_msg_data => x_msg_data);
1303
1304 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1305 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1306 RAISE FND_API.G_EXC_ERROR;
1307 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1308 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1309 END IF;
1310 END IF;
1311
1312 END LOOP;
1313
1314 --case3: either id/number is null
1315 --set the parent_id
1316 --dbms_output.put_line('before case3 update parent id/num');
1317 UPDATE RRS_SITE_GROUP_MEMBERS_TEMP RSGMT
1318 SET PARENT_ID = (
1319 SELECT DECODE(PARENT_TYPE,'SITE', SITE_ID, SITE_GROUP_NODE_ID)
1320 FROM RRS_SITES_VL RSV, RRS_SITE_GROUP_NODES_VL RSGNV, RRS_SITE_GROUP_MEMBERS_TEMP RSGMT2
1321 WHERE RSGMT2.PARENT_NUMBER = RSV.SITE_IDENTIFICATION_NUMBER(+)
1322 AND RSGMT2.PARENT_NUMBER = RSGNV.NODE_IDENTIFICATION_NUMBER(+)
1323 AND RSGMT.ROWID = RSGMT2.ROWID)
1324 WHERE PARENT_ID IS NULL;
1325 --set the parent_number
1326 UPDATE RRS_SITE_GROUP_MEMBERS_TEMP RSGMT
1327 SET PARENT_NUMBER = (
1328 SELECT DECODE(PARENT_TYPE,'SITE', SITE_IDENTIFICATION_NUMBER, NODE_IDENTIFICATION_NUMBER)
1329 FROM RRS_SITES_VL RSV, RRS_SITE_GROUP_NODES_VL RSGNV, RRS_SITE_GROUP_MEMBERS_TEMP RSGMT2
1330 WHERE RSGMT2.PARENT_ID = RSV.SITE_ID(+)
1331 AND RSGMT2.PARENT_ID = RSGNV.SITE_GROUP_NODE_ID(+)
1332 AND RSGMT.ROWID = RSGMT2.ROWID)
1333 WHERE PARENT_NUMBER IS NULL;
1334 --set the child_id
1335 --dbms_output.put_line('before case3 update child id/num');
1336 UPDATE RRS_SITE_GROUP_MEMBERS_TEMP RSGMT
1337 SET CHILD_ID = (
1338 SELECT DECODE(CHILD_TYPE,'SITE', SITE_ID, SITE_GROUP_NODE_ID)
1339 FROM RRS_SITES_VL RSV, RRS_SITE_GROUP_NODES_VL RSGNV, RRS_SITE_GROUP_MEMBERS_TEMP RSGMT2
1340 WHERE RSGMT2.CHILD_NUMBER = RSV.SITE_IDENTIFICATION_NUMBER(+)
1341 AND RSGMT2.CHILD_NUMBER = RSGNV.NODE_IDENTIFICATION_NUMBER(+)
1342 AND RSGMT.ROWID = RSGMT2.ROWID)
1343 WHERE CHILD_ID IS NULL;
1344 --set the child_number
1345 UPDATE RRS_SITE_GROUP_MEMBERS_TEMP RSGMT
1346 SET CHILD_NUMBER = (
1347 SELECT DECODE(CHILD_TYPE,'SITE', SITE_IDENTIFICATION_NUMBER, NODE_IDENTIFICATION_NUMBER)
1348 FROM RRS_SITES_VL RSV, RRS_SITE_GROUP_NODES_VL RSGNV, RRS_SITE_GROUP_MEMBERS_TEMP RSGMT2
1349 WHERE RSGMT2.CHILD_ID = RSV.SITE_ID(+)
1350 AND RSGMT2.CHILD_ID = RSGNV.SITE_GROUP_NODE_ID(+)
1351 AND RSGMT.ROWID = RSGMT2.ROWID)
1352 WHERE CHILD_NUMBER IS NULL;
1353
1354 --check for duplicated site/node number
1355 --dbms_output.put_line('before check duplicated number');
1356 OPEN validate_dup_number_cursor;
1357 LOOP
1358 FETCH validate_dup_number_cursor INTO v_type, v_num;
1359 EXIT WHEN validate_dup_number_cursor%NOTFOUND OR validate_dup_number_cursor%NOTFOUND IS NULL;
1360 --RRS_DUPLICATED_NUMBER
1361 FND_MESSAGE.set_name('RRS', 'RRS_DUPLICATED_NUMBER');
1362 FND_MESSAGE.set_token('NUM', v_num);
1363 FND_MESSAGE.set_token('TYPE', v_type);
1364 FND_MSG_PUB.add;
1365 --dbms_output.put_line('Duplicated type/num: '||v_type||'/'||v_num);
1366 x_return_status := FND_API.G_RET_STS_ERROR;
1367 END LOOP;
1368 CLOSE validate_dup_number_cursor;
1369
1370 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1371 RAISE FND_API.G_EXC_ERROR;
1372 END IF;
1373
1374 --check for invalid site num
1375 --dbms_output.put_line('before check invalid site num');
1376 OPEN validate_num_cursor;
1377 LOOP
1378 FETCH validate_num_cursor INTO v_num, v_type;
1379 EXIT WHEN validate_num_cursor%NOTFOUND OR validate_num_cursor%NOTFOUND IS NULL;
1380 --RRS_NO_NUM_FOUND
1381 FND_MESSAGE.set_name('RRS', 'RRS_NO_NUM_FOUND');
1382 FND_MESSAGE.set_token('NUM', v_num);
1383 FND_MESSAGE.set_token('TYPE', v_type);
1384 FND_MSG_PUB.add;
1385 --dbms_output.put_line('Invalid num: '||v_num);
1386 x_return_status := FND_API.G_RET_STS_ERROR;
1387 END LOOP;
1388 CLOSE validate_num_cursor;
1389
1390 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1391 RAISE FND_API.G_EXC_ERROR;
1392 END IF;
1393
1394 --check for site template
1395 --dbms_output.put_line('before check site template');
1396 OPEN validate_site_template_cursor;
1397 LOOP
1398 FETCH validate_site_template_cursor INTO v_num;
1399 EXIT WHEN validate_site_template_cursor%NOTFOUND OR validate_site_template_cursor%NOTFOUND IS NULL;
1400 --RRS_SITE_TEMPLATE
1401 FND_MESSAGE.set_name('RRS', 'RRS_SITE_TEMPLATE');
1402 FND_MESSAGE.set_token('SITE_ID_NUM', v_num);
1403 FND_MSG_PUB.add;
1404 --dbms_output.put_line('site is a template: '||v_num);
1405 x_return_status := FND_API.G_RET_STS_ERROR;
1406 END LOOP;
1407 CLOSE validate_site_template_cursor;
1408
1409 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1410 RAISE FND_API.G_EXC_ERROR;
1411 END IF;
1412
1413 --check for parent site/node does not exist in the hierarchy
1414 OPEN validate_p_in_hier_curosr;
1415 LOOP
1416 FETCH validate_p_in_hier_curosr INTO v_type, v_num;
1417 EXIT WHEN validate_p_in_hier_curosr%NOTFOUND OR validate_p_in_hier_curosr%NOTFOUND IS NULL;
1418 --RRS_HIER_NOT_CONNECTED
1419 FND_MESSAGE.set_name('RRS', 'RRS_HIER_NOT_CONNECTED');
1420 FND_MESSAGE.set_token('NUM', v_num);
1421 FND_MESSAGE.set_token('TYPE', v_type);
1422 FND_MSG_PUB.add;
1423 --dbms_output.put_line('No parent found type/num: '||v_type||'/'||v_num);
1424 x_return_status := FND_API.G_RET_STS_ERROR;
1425 END LOOP;
1426 CLOSE validate_p_in_hier_curosr;
1427
1428 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1429 RAISE FND_API.G_EXC_ERROR;
1430 END IF;
1431
1432 --check for duplicated node name under a same parent
1433 --dbms_output.put_line('before check duplicated node name');
1434 OPEN validate_node_name_cursor;
1435 LOOP
1436 FETCH validate_node_name_cursor INTO v_node_name;
1437 EXIT WHEN validate_node_name_cursor%NOTFOUND OR validate_node_name_cursor%NOTFOUND IS NULL;
1438 --RRS_DUPLICATED_NODE_NAME
1439 FND_MESSAGE.set_name('RRS', 'RRS_DUPLICATED_NODE_NAME');
1440 FND_MESSAGE.set_token('NODE_NAME', v_node_name);
1441 FND_MSG_PUB.add;
1442 --dbms_output.put_line('Duplicated node name under a same parent: '||v_node_name);
1443 x_return_status := FND_API.G_RET_STS_ERROR;
1444 END LOOP;
1445 CLOSE validate_node_name_cursor;
1446
1447 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1448 RAISE FND_API.G_EXC_ERROR;
1449 END IF;
1450
1451 --update nodes
1452 --dbms_output.put_line('before update nodes');
1453 FOR node_rec IN update_nodes_cursor LOOP
1454 --dbms_output.put_line('Inside update nodes');
1455
1456 Update_Hierarchy_Node(
1457 p_number => node_rec.CHILD_NUMBER,
1458 p_name => node_rec.CHILD_NODE_NAME,
1459 p_purpose_code => node_rec.CHILD_PURPOSE_CODE,
1460 p_description => node_rec.CHILD_NODE_DESCRIPTION,
1461 x_return_status => x_return_status,
1462 x_msg_count => x_msg_count,
1463 x_msg_data => x_msg_data);
1464
1465 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1466 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1467 RAISE FND_API.G_EXC_ERROR;
1468 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1469 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1470 END IF;
1471 END IF;
1472
1473 END LOOP;
1474
1475 --RulesFwk
1476 IF p_hier_purpose_code IS NOT NULL THEN
1477 --default the purpose code for parent
1478 --dbms_output.put_line('before defaulting parent purpose code');
1479 UPDATE RRS_SITE_GROUP_MEMBERS_TEMP RSGMT
1480 SET PARENT_PURPOSE_CODE = (
1481 SELECT NVL(RSU.SITE_USE_TYPE_CODE, RSGNV.NODE_PURPOSE_CODE)
1482 FROM RRS_SITE_GROUP_MEMBERS_TEMP RSGMT2, RRS_SITE_USES RSU, RRS_SITE_GROUP_NODES_VL RSGNV
1483 WHERE RSGMT2.PARENT_ID = RSU.SITE_ID(+)
1484 AND RSU.IS_PRIMARY_FLAG(+) = 'Y'
1485 AND RSGMT2.PARENT_ID = RSGNV.SITE_GROUP_NODE_ID(+)
1486 AND RSGMT.ROWID = RSGMT2.ROWID);
1487
1488 --default the purpose code for child
1489 --dbms_output.put_line('before defaulting child purpose code');
1490 UPDATE RRS_SITE_GROUP_MEMBERS_TEMP RSGMT
1491 SET CHILD_PURPOSE_CODE = (
1492 SELECT NVL(RSU.SITE_USE_TYPE_CODE, RSGNV.NODE_PURPOSE_CODE)
1493 FROM RRS_SITE_GROUP_MEMBERS_TEMP RSGMT2, RRS_SITE_USES RSU, RRS_SITE_GROUP_NODES_VL RSGNV
1494 WHERE RSGMT2.CHILD_ID = RSU.SITE_ID(+)
1495 AND RSU.IS_PRIMARY_FLAG(+) = 'Y'
1496 AND RSGMT2.CHILD_ID = RSGNV.SITE_GROUP_NODE_ID(+)
1497 AND RSU.IS_PRIMARY_FLAG(+) = 'Y'
1498 AND RSGMT.ROWID = RSGMT2.ROWID);
1499
1500 Validate_Rules_For_Members(
1501 p_hier_purpose_code => p_hier_purpose_code,
1502 x_return_status => x_return_status,
1503 x_msg_data => x_msg_data);
1504
1505 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1506 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1507 RAISE FND_API.G_EXC_ERROR;
1508 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1509 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1510 END IF;
1511 END IF;
1512
1513 END IF;
1514
1515 --insert into members table
1516 --dbms_output.put_line('before inserting into members');
1517 FOR mem_rec IN new_members_cursor LOOP
1518 INSERT INTO RRS_SITE_GROUP_MEMBERS
1519 (
1520 SITE_GROUP_VERSION_ID,
1521 SITE_GROUP_ID,
1522 PARENT_MEMBER_ID,
1523 CHILD_MEMBER_ID,
1524 DELETED_FLAG,
1525 OBJECT_VERSION_NUMBER,
1526 CREATED_BY,
1527 CREATION_DATE,
1528 LAST_UPDATED_BY,
1529 LAST_UPDATE_DATE,
1530 LAST_UPDATE_LOGIN,
1531 SEQUENCE_NUMBER
1532 )
1533 VALUES( p_hier_version_id,
1534 p_hier_id,
1535 mem_rec.PARENT_ID,
1536 mem_rec.CHILD_ID,
1537 'N',
1538 1,
1539 fnd_global.user_id,
1540 sysdate,
1541 fnd_global.user_id,
1542 sysdate,
1543 fnd_global.user_id,
1544 mem_rec.SEQUENCE_NUMBER);
1545 END LOOP;
1546 END IF;
1547
1548 x_return_status := FND_API.G_RET_STS_SUCCESS;
1549
1550 EXCEPTION
1551 WHEN FND_API.G_EXC_ERROR THEN
1552 ROLLBACK TO Create_Hierarchy_Members;
1553 x_msg_count := FND_MSG_PUB.Count_Msg;
1554 x_return_status := FND_API.G_RET_STS_ERROR;
1555 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1556 ROLLBACK TO Create_Hierarchy_Members;
1557 x_msg_data := G_PKG_NAME || '.Create_Hierarchy_Members:' || x_msg_data;
1558 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1559 WHEN OTHERS THEN
1560 ROLLBACK TO Create_Hierarchy_Members;
1561 x_msg_data := G_PKG_NAME || '.Create_Hierarchy_Members:' || SQLERRM;
1562 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1563 END Create_Hierarchy_Members;
1564
1565 procedure Update_Hierarchy_Coarse(
1566 p_api_version IN NUMBER DEFAULT 1,
1567 p_hier_name IN VARCHAR2,
1568 p_hier_new_name IN VARCHAR2 DEFAULT NULL,
1569 p_hier_description IN VARCHAR2 DEFAULT NULL,
1570 p_hier_purpose_code IN VARCHAR2 DEFAULT NULL,
1571 p_hier_start_date IN DATE DEFAULT NULL,
1572 p_hier_end_date IN DATE DEFAULT NULL,
1573 p_hier_members_tab IN RRS_HIER_MEMBERS_COARSE_TAB DEFAULT NULL,
1574 p_nullify_flag IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1575 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1576 x_return_status OUT NOCOPY varchar2,
1577 x_msg_count OUT NOCOPY NUMBER,
1578 x_msg_data OUT NOCOPY VARCHAR2
1579 )IS
1580
1581 v_count NUMBER;
1582 v_hier_id NUMBER;
1583 v_hier_version_id NUMBER;
1584 v_root_number VARCHAR2(30);
1585 v_root_id NUMBER;
1586 v_purpose_code VARCHAR2(30);
1587 BEGIN
1588 SAVEPOINT Update_Hierarchy_Coarse;
1589
1590 IF p_nullify_flag <> FND_API.G_FALSE
1591 AND p_nullify_flag <> FND_API.G_TRUE THEN
1592 --RRS_INVALID_FLAG
1593 FND_MESSAGE.set_name('RRS', 'RRS_INVALID_FLAG');
1594 FND_MSG_PUB.add;
1595 RAISE FND_API.G_EXC_ERROR;
1596 END IF;
1597
1598 BEGIN
1599 SELECT SITE_GROUP_ID
1600 INTO v_hier_id
1601 FROM RRS_SITE_GROUPS_VL RSGV
1602 WHERE RSGV.NAME = p_hier_name;
1603 EXCEPTION
1604 WHEN NO_DATA_FOUND THEN
1605 --RRS_NO_HIER_FOUND
1606 FND_MESSAGE.set_name('RRS', 'RRS_NO_HIER_FOUND');
1607 FND_MESSAGE.set_token('HIERARCHY_NAME', p_hier_name);
1608 FND_MSG_PUB.add;
1609 --dbms_output.put_line('invalid hier name');
1610 RAISE FND_API.G_EXC_ERROR;
1611 END;
1612
1613 BEGIN
1614 SELECT RSGM.CHILD_MEMBER_ID, RSGNV.NODE_IDENTIFICATION_NUMBER
1615 INTO v_root_id, v_root_number
1616 FROM RRS_SITE_GROUP_MEMBERS RSGM, RRS_SITE_GROUP_NODES_VL RSGNV
1617 WHERE RSGM.SITE_GROUP_ID = v_hier_id
1618 AND RSGM.PARENT_MEMBER_ID = -1
1619 AND RSGM.CHILD_MEMBER_ID = RSGNV.SITE_GROUP_NODE_ID
1620 AND RSGNV.NODE_PURPOSE_CODE = 'ROOT';
1621 EXCEPTION
1622 WHEN NO_DATA_FOUND THEN
1623 --RRS_NO_ROOT_FOUND
1624 FND_MESSAGE.set_name('RRS', 'RRS_NO_ROOT_FOUND');
1625 FND_MSG_PUB.add;
1626 --dbms_output.put_line('root node not found in members table');
1627 RAISE FND_API.G_EXC_ERROR;
1628 END;
1629
1630 BEGIN
1631 SELECT SITE_GROUP_VERSION_ID
1632 INTO v_hier_version_id
1633 FROM RRS_SITE_GROUP_VERSIONS
1634 WHERE SITE_GROUP_ID = v_hier_id;
1635 EXCEPTION
1636 WHEN NO_DATA_FOUND THEN
1637 --RRS_NO_HIER_VERSION_FOUND
1638 FND_MESSAGE.set_name('RRS', 'RRS_NO_HIER_VERSION_FOUND');
1639 FND_MESSAGE.set_token('HIERARCHY_NAME', p_hier_name);
1640 FND_MSG_PUB.add;
1641 --dbms_output.put_line('hier version id not found');
1642 RAISE FND_API.G_EXC_ERROR;
1643 END;
1644
1645 --default the purpose code when nuulify flag is false and purpose code is not specified
1646 IF NOT FND_API.To_Boolean(p_nullify_flag) AND p_hier_purpose_code IS NULL THEN
1647 SELECT GROUP_PURPOSE_CODE
1648 INTO v_purpose_code
1649 FROM RRS_SITE_GROUPS_B RSGB
1650 WHERE RSGB.SITE_GROUP_ID = v_hier_id;
1651 ELSE
1652 v_purpose_code := p_hier_purpose_code;
1653 END IF;
1654
1655 --dbms_output.put_line(v_hier_version_id||' '||v_hier_id||' '||v_root_id||' '||v_purpose_code);
1656
1657 --dbms_output.put_line('before update hierarchy header');
1658 Update_Hierarchy_Header(
1659 p_name => p_hier_name,
1660 p_new_name => p_hier_new_name,
1661 p_description => p_hier_description,
1662 p_purpose_code => v_purpose_code,
1663 p_start_date => p_hier_start_date,
1664 p_end_date => p_hier_end_date,
1665 p_nullify_flag => p_nullify_flag,
1666 x_return_status => x_return_status,
1667 x_msg_count => x_msg_count,
1668 x_msg_data => x_msg_data);
1669
1670 --dbms_output.put_line(x_return_status);
1671 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1672 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1673 RAISE FND_API.G_EXC_ERROR;
1674 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1675 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1676 END IF;
1677 END IF;
1678
1679 IF p_hier_members_tab IS NOT NULL OR FND_API.To_Boolean(p_nullify_flag) THEN
1680
1681 --dbms_output.put_line('before delete members table');
1682 DELETE FROM RRS_SITE_GROUP_MEMBERS
1683 WHERE SITE_GROUP_ID = v_hier_id;
1684
1685 --dbms_output.put_line('before create hierarchy members');
1686 Create_Hierarchy_Members(
1687 p_hier_version_id => v_hier_version_id,
1688 p_hier_id => v_hier_id,
1689 p_root_number => v_root_number,
1690 p_root_id => v_root_id,
1691 p_hier_purpose_code => v_purpose_code,
1692 p_hier_members_tab => p_hier_members_tab,
1693 x_return_status => x_return_status,
1694 x_msg_count => x_msg_count,
1695 x_msg_data => x_msg_data);
1696
1697 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1698 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1699 RAISE FND_API.G_EXC_ERROR;
1700 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1701 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1702 END IF;
1703 END IF;
1704
1705 END IF;
1706
1707 IF FND_API.To_Boolean(p_commit) THEN
1708 COMMIT;
1709 END IF;
1710
1711 x_return_status := FND_API.G_RET_STS_SUCCESS;
1712
1713 EXCEPTION
1714 WHEN FND_API.G_EXC_ERROR THEN
1715 ROLLBACK TO Update_Hierarchy_Coarse;
1716 x_msg_count := FND_MSG_PUB.Count_Msg;
1717 x_return_status := FND_API.G_RET_STS_ERROR;
1718 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1719 ROLLBACK TO Update_Hierarchy_Coarse;
1720 x_msg_data := G_PKG_NAME || '.Update_Hierarchy_Coarse:' || x_msg_data;
1721 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1722 WHEN OTHERS THEN
1723 ROLLBACK TO Update_Hierarchy_Coarse;
1724 x_msg_data := G_PKG_NAME || '.Update_Hierarchy_Coarse:' || SQLERRM;
1725 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1726 END Update_Hierarchy_Coarse;
1727
1728 procedure Update_Hierarchy_Fine(
1729 p_api_version IN NUMBER DEFAULT 1,
1730 p_hier_members_rec IN RRS_HIER_MEMBERS_FINE_REC,
1731 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1732 x_return_status OUT NOCOPY varchar2,
1733 x_msg_count OUT NOCOPY NUMBER,
1734 x_msg_data OUT NOCOPY VARCHAR2
1735 )IS
1736
1737 v_source_hier_id NUMBER;
1738 v_dest_hier_id NUMBER;
1739 v_dest_hier_version_id NUMBER;
1740 v_dest_hier_purpose_code VARCHAR2(30);
1741 v_child_purpose_code VARCHAR2(30);
1742 v_dest_parent_purpose_code VARCHAR2(30);
1743 v_count NUMBER;
1744 v_child_id NUMBER;
1745 v_dest_parent_id NUMBER;
1746 v_source_parent_id NUMBER;
1747 v_child_name VARCHAR2(30);
1748 v_p_num VARCHAR2(30);
1749 v_p_type VARCHAR2(30);
1750 v_c_num VARCHAR2(30);
1751 v_c_type VARCHAR2(30);
1752 v_flag VARCHAR2(30);
1753
1754 CURSOR parent_child_cursor IS
1755 SELECT DECODE(RSV.SITE_ID, NULL, 'NODE', 'SITE') AS P_TYPE,
1756 RSGM.PARENT_MEMBER_ID AS P_ID,
1757 NVL(RSV.SITE_IDENTIFICATION_NUMBER, RSGNV.NODE_IDENTIFICATION_NUMBER) AS P_NUMBER,
1758 DECODE(RSV.SITE_ID, NULL, RSGNV.NODE_PURPOSE_CODE, RSU.SITE_USE_TYPE_CODE) AS P_PURPOSE_CODE,
1759 DECODE(RSV2.SITE_ID, NULL, 'NODE', 'SITE') AS C_TYPE,
1760 RSGM.CHILD_MEMBER_ID AS C_ID,
1761 NVL(RSV2.SITE_IDENTIFICATION_NUMBER, RSGNV2.NODE_IDENTIFICATION_NUMBER) AS C_NUMBER,
1762 DECODE(RSV2.SITE_ID, NULL, RSGNV2.NODE_PURPOSE_CODE, RSU2.SITE_USE_TYPE_CODE) AS C_PURPOSE_CODE,
1763 RSGM.SEQUENCE_NUMBER AS C_SEQ_NUMBER
1764 FROM RRS_SITE_GROUP_MEMBERS RSGM,
1765 RRS_SITE_GROUP_NODES_VL RSGNV,
1766 RRS_SITES_VL RSV, RRS_SITE_USES RSU,
1767 RRS_SITE_GROUP_NODES_VL RSGNV2,
1768 RRS_SITES_VL RSV2,
1769 RRS_SITE_USES RSU2
1770 WHERE RSGM.PARENT_MEMBER_ID = RSV.SITE_ID(+)
1771 AND RSGM.PARENT_MEMBER_ID = RSU.SITE_ID(+)
1772 AND NVL(RSU.IS_PRIMARY_FLAG, 'Y') = 'Y'
1773 AND RSGM.PARENT_MEMBER_ID = RSGNV.SITE_GROUP_NODE_ID(+)
1774 AND RSGM.CHILD_MEMBER_ID = RSV2.SITE_ID(+)
1775 AND RSGM.CHILD_MEMBER_ID = RSU2.SITE_ID(+)
1776 AND NVL(RSU2.IS_PRIMARY_FLAG, 'Y') = 'Y'
1777 AND RSGM.CHILD_MEMBER_ID = RSGNV2.SITE_GROUP_NODE_ID(+)
1778 START WITH CHILD_MEMBER_ID = v_child_id
1779 AND SITE_GROUP_ID = v_source_hier_id
1780 CONNECT BY PRIOR CHILD_MEMBER_ID = PARENT_MEMBER_ID
1781 AND SITE_GROUP_ID = v_source_hier_id;
1782
1783 CURSOR new_members_cursor IS
1784 SELECT *
1785 FROM RRS_SITE_GROUP_MEMBERS_TEMP;
1786
1787 CURSOR validate_rules_cursor IS
1788 SELECT PARENT_TYPE, PARENT_NUMBER, CHILD_TYPE, CHILD_NUMBER
1789 FROM RRS_SITE_GROUP_MEMBERS_TEMP
1790 WHERE CHILD_ID NOT IN (
1791 SELECT CHILD_ID
1792 FROM RRS_SITE_GROUP_MEMBERS_TEMP RSGMT, RRS_GROUP_RULES RGR
1793 WHERE RGR.SITE_GROUP_TYPE_CODE = 'H'
1794 AND RGR.GROUP_PURPOSE_CODE = v_dest_hier_purpose_code
1795 AND RGR.RELATIONSHIP_TYPE = 'PARENT_CHILD'
1796 AND RSGMT.PARENT_TYPE = RGR.OBJECT1
1797 AND RSGMT.PARENT_PURPOSE_CODE = RGR.CLASSIFICATION_CODE1
1798 AND RSGMT.CHILD_TYPE = RGR.OBJECT2
1799 AND RSGMT.CHILD_PURPOSE_CODE = RGR.CLASSIFICATION_CODE2);
1800
1801 BEGIN
1802 SAVEPOINT Update_Hierarchy_Fine;
1803
1804 --validate dest hier name
1805 --dbms_output.put_line('before validate dest hier name');
1806 --get dest hier id
1807 BEGIN
1808 SELECT SITE_GROUP_ID, GROUP_PURPOSE_CODE
1809 INTO v_dest_hier_id, v_dest_hier_purpose_code
1810 FROM RRS_SITE_GROUPS_VL RSGV
1811 WHERE RSGV.NAME = p_hier_members_rec.dest_hier_name;
1812 EXCEPTION
1813 WHEN NO_DATA_FOUND THEN
1814 --RRS_NO_HIER_FOUND
1815 FND_MESSAGE.set_name('RRS', 'RRS_NO_HIER_FOUND');
1816 FND_MESSAGE.set_token('HIERARCHY_NAME', p_hier_members_rec.dest_hier_name);
1817 FND_MSG_PUB.add;
1818 --dbms_output.put_line('invalid dest hier name: '||p_hier_members_rec.dest_hier_name);
1819 RAISE FND_API.G_EXC_ERROR;
1820 END;
1821 --get dest hier version id
1822 --dbms_output.put_line('before get dest hier version id');
1823 BEGIN
1824 SELECT SITE_GROUP_VERSION_ID
1825 INTO v_dest_hier_version_id
1826 FROM RRS_SITE_GROUP_VERSIONS
1827 WHERE SITE_GROUP_ID = v_dest_hier_id;
1828 EXCEPTION
1829 WHEN NO_DATA_FOUND THEN
1830 --RRS_NO_HIER_VERSION_FOUND
1831 FND_MESSAGE.set_name('RRS', 'RRS_NO_HIER_VERSION_FOUND');
1832 FND_MESSAGE.set_token('HIERARCHY_NAME', p_hier_members_rec.dest_hier_name);
1833 FND_MSG_PUB.add;
1834 --dbms_output.put_line('version id not found: '||p_hier_members_rec.dest_hier_name);
1835 RAISE FND_API.G_EXC_ERROR;
1836 END;
1837
1838 --validate child number
1839 --1. child number cannot be null
1840 --2. cannot transact on root node
1841 --3. site/node must exist
1842 --4. cannot be a site template
1843 --dbms_output.put_line('before validate child number');
1844 IF p_hier_members_rec.child_id_number IS NULL THEN
1845 --RRS_NULL_CHILD_NUMBER
1846 FND_MESSAGE.set_name('RRS', 'RRS_NULL_CHILD_NUMBER');
1847 FND_MSG_PUB.add;
1848 --dbms_output.put_line('child number cannot be null');
1849 RAISE FND_API.G_EXC_ERROR;
1850 END IF;
1851
1852 IF p_hier_members_rec.child_object_type = 'NODE' THEN
1853 BEGIN
1854 SELECT SITE_GROUP_NODE_ID, NODE_PURPOSE_CODE, NAME
1855 INTO v_child_id, v_child_purpose_code, v_child_name
1856 FROM RRS_SITE_GROUP_NODES_VL
1857 WHERE NODE_IDENTIFICATION_NUMBER = p_hier_members_rec.child_id_number;
1858 EXCEPTION
1859 WHEN NO_DATA_FOUND THEN
1860 --RRS_NO_NODE_FOUND
1861 FND_MESSAGE.set_name('RRS', 'RRS_NO_NODE_FOUND');
1862 FND_MESSAGE.set_token('NODE_ID_NUM', p_hier_members_rec.child_id_number);
1863 FND_MSG_PUB.add;
1864 --dbms_output.put_line('invalid node number: '|| p_hier_members_rec.child_id_number);
1865 RAISE FND_API.G_EXC_ERROR;
1866 END;
1867 IF v_child_purpose_code = 'ROOT' THEN
1868 --RRS_TRANSACT_ROOT
1869 FND_MESSAGE.set_name('RRS', 'RRS_TRANSACT_ROOT');
1870 FND_MSG_PUB.add;
1871 --dbms_output.put_line('cannot transact on root node');
1872 RAISE FND_API.G_EXC_ERROR;
1873 END IF;
1874 ELSIF p_hier_members_rec.child_object_type = 'SITE' THEN
1875 BEGIN
1876 SELECT RSV.SITE_ID, RSU.SITE_USE_TYPE_CODE, RSV.IS_TEMPLATE_FLAG
1877 INTO v_child_id, v_child_purpose_code, v_flag
1878 FROM RRS_SITES_VL RSV, RRS_SITE_USES RSU
1879 WHERE RSV.SITE_IDENTIFICATION_NUMBER = p_hier_members_rec.child_id_number
1880 AND RSV.SITE_ID = RSU.SITE_ID(+)
1881 AND RSU.IS_PRIMARY_FLAG(+) = 'Y';
1882 EXCEPTION
1883 WHEN NO_DATA_FOUND THEN
1884 --RRS_NO_SITE_FOUND
1885 FND_MESSAGE.set_name('RRS', 'RRS_NO_SITE_FOUND');
1886 FND_MESSAGE.set_token('SITE_ID_NUM', p_hier_members_rec.child_id_number);
1887 FND_MSG_PUB.add;
1888 --dbms_output.put_line('invalid site number: '|| p_hier_members_rec.child_id_number);
1889 RAISE FND_API.G_EXC_ERROR;
1890 END;
1891 IF v_flag = 'Y' THEN
1892 --RRS_SITE_TEMPLATE
1893 FND_MESSAGE.set_name('RRS', 'RRS_SITE_TEMPLATE');
1894 FND_MESSAGE.set_token('SITE_ID_NUM', p_hier_members_rec.child_id_number);
1895 FND_MSG_PUB.add;
1896 --dbms_output.put_line('site is a template: '||p_hier_members_rec.child_id_number);
1897 RAISE FND_API.G_EXC_ERROR;
1898 END IF;
1899 ELSE
1900 --RRS_INVALID_TYPE
1901 FND_MESSAGE.set_name('RRS', 'RRS_INVALID_TYPE');
1902 FND_MESSAGE.set_token('TYPE', p_hier_members_rec.child_object_type);
1903 FND_MSG_PUB.add;
1904 --dbms_output.put_line('Invalid child type: '||p_hier_members_rec.child_object_type);
1905 RAISE FND_API.G_EXC_ERROR;
1906 END IF;
1907
1908 --get parent id, parent purpose code and source hier id
1909 IF p_hier_members_rec.transaction_type = 'ADD'
1910 OR p_hier_members_rec.transaction_type = 'COPY'
1911 OR p_hier_members_rec.transaction_type = 'MOVE' THEN
1912
1913 --validate dest parent number
1914 --1. parent number cannot be null
1915 --2. site/node must exist
1916 --3. site/node shuld appear in the hierarchy
1917 --dbms_output.put_line('before validate dest parent number');
1918 IF p_hier_members_rec.dest_parent_id_number IS NULL THEN
1919 --RRS_NULL_DEST_PARENT_NUMBER
1920 FND_MESSAGE.set_name('RRS', 'RRS_NULL_DEST_PARENT_NUMBER');
1921 FND_MSG_PUB.add;
1922 --dbms_output.put_line('dest parent number cannot be null');
1923 RAISE FND_API.G_EXC_ERROR;
1924 END IF;
1925
1926 IF p_hier_members_rec.dest_parent_object_type = 'NODE' THEN
1927 BEGIN
1928 SELECT SITE_GROUP_NODE_ID, NODE_PURPOSE_CODE
1929 INTO v_dest_parent_id, v_dest_parent_purpose_code
1930 FROM RRS_SITE_GROUP_NODES_VL RSGNV, RRS_SITE_GROUP_MEMBERS RSGM
1931 WHERE RSGNV.NODE_IDENTIFICATION_NUMBER = p_hier_members_rec.dest_parent_id_number
1932 AND RSGNV.SITE_GROUP_NODE_ID = RSGM.CHILD_MEMBER_ID
1933 AND RSGM.SITE_GROUP_ID = v_dest_hier_id;
1934 EXCEPTION
1935 WHEN NO_DATA_FOUND THEN
1936 --RRS_HIER_NO_NODE_FOUND
1937 FND_MESSAGE.set_name('RRS', 'RRS_HIER_NO_NODE_FOUND');
1938 FND_MESSAGE.set_token('NODE_ID_NUM', p_hier_members_rec.dest_parent_id_number);
1939 FND_MESSAGE.set_token('HIERARCHY_NAME', p_hier_members_rec.dest_hier_name);
1940 FND_MSG_PUB.add;
1941 --dbms_output.put_line('node does not exist or appear in the hierarchy: '|| p_hier_members_rec.dest_parent_id_number);
1942 RAISE FND_API.G_EXC_ERROR;
1943 END;
1944 ELSIF p_hier_members_rec.dest_parent_object_type = 'SITE' THEN
1945 BEGIN
1946 SELECT RSV.SITE_ID, RSU.SITE_USE_TYPE_CODE
1947 INTO v_dest_parent_id, v_dest_parent_purpose_code
1948 FROM RRS_SITES_VL RSV, RRS_SITE_USES RSU, RRS_SITE_GROUP_MEMBERS RSGM
1949 WHERE RSV.SITE_IDENTIFICATION_NUMBER = p_hier_members_rec.dest_parent_id_number
1950 AND RSV.SITE_ID = RSU.SITE_ID(+)
1951 AND RSU.IS_PRIMARY_FLAG(+) = 'Y'
1952 AND RSV.SITE_ID = RSGM.CHILD_MEMBER_ID
1953 AND RSGM.SITE_GROUP_ID = v_dest_hier_id;
1954 EXCEPTION
1955 WHEN NO_DATA_FOUND THEN
1956 --RRS_HIER_NO_SITE_FOUND
1957 FND_MESSAGE.set_name('RRS', 'RRS_HIER_NO_SITE_FOUND');
1958 FND_MESSAGE.set_token('SITE_ID_NUM', p_hier_members_rec.dest_parent_id_number);
1959 FND_MESSAGE.set_token('HIERARCHY_NAME', p_hier_members_rec.dest_hier_name);
1960 FND_MSG_PUB.add;
1961 --dbms_output.put_line('site does not exist or appear in the hierarchy: '|| p_hier_members_rec.dest_parent_id_number);
1962 RAISE FND_API.G_EXC_ERROR;
1963 END;
1964 ELSE
1965 --RRS_INVALID_TYPE
1966 FND_MESSAGE.set_name('RRS', 'RRS_INVALID_TYPE');
1967 FND_MESSAGE.set_token('TYPE', p_hier_members_rec.dest_parent_object_type);
1968 FND_MSG_PUB.add;
1969 --dbms_output.put_line('Invalid parent type: '||p_hier_members_rec.dest_parent_object_type);
1970 RAISE FND_API.G_EXC_ERROR;
1971 END IF;
1972
1973 END IF;
1974
1975 --1. ADD
1976 IF p_hier_members_rec.transaction_type = 'ADD' THEN
1977 --dbms_output.put_line('Transaction ADD');
1978 --child id should not appear in the dest hier
1979 SELECT COUNT(*)
1980 INTO v_count
1981 FROM RRS_SITE_GROUP_MEMBERS
1982 WHERE CHILD_MEMBER_ID = v_child_id
1983 AND SITE_GROUP_ID = v_dest_hier_id;
1984 IF v_count <> 0 THEN
1985 --RRS_HIER_CHILD_EXISTS
1986 FND_MESSAGE.set_name('RRS', 'RRS_HIER_CHILD_EXISTS');
1987 FND_MESSAGE.set_token('NUM', p_hier_members_rec.child_id_number);
1988 FND_MESSAGE.set_token('TYPE', p_hier_members_rec.child_object_type);
1989 FND_MESSAGE.set_token('HIERARCHY_NAME', p_hier_members_rec.dest_hier_name);
1990 FND_MSG_PUB.add;
1991 --dbms_output.put_line('Child already exists in the destination hierarchy: '||p_hier_members_rec.child_id_number);
1992 RAISE FND_API.G_EXC_ERROR;
1993 END IF;
1994
1995 --dbms_output.put_line(v_hier_purpose_code||' '||v_parent_purpose_code||' '||v_child_purpose_code);
1996
1997 --RulesFwk
1998 --dbms_output.put_line('before RulesFwk');
1999 IF v_dest_hier_purpose_code IS NOT NULL THEN
2000
2001 Validate_Rules_For_Child(
2002 p_hier_purpose_code => v_dest_hier_purpose_code,
2003 p_parent_id_number => p_hier_members_rec.dest_parent_id_number,
2004 p_parent_object_type => p_hier_members_rec.dest_parent_object_type,
2005 p_parent_purpose_code => v_dest_parent_purpose_code,
2006 p_child_id_number => p_hier_members_rec.child_id_number,
2007 p_child_object_type => p_hier_members_rec.child_object_type,
2008 p_child_purpose_code => v_child_purpose_code,
2009 x_return_status => x_return_status,
2010 x_msg_data => x_msg_data
2011 );
2012
2013 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2014 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2015 RAISE FND_API.G_EXC_ERROR;
2016 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2017 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2018 END IF;
2019 END IF;
2020
2021 END IF;
2022
2023 --add to members table
2024 --dbms_output.put_line('before add to members table');
2025 INSERT INTO RRS_SITE_GROUP_MEMBERS
2026 (
2027 SITE_GROUP_VERSION_ID,
2028 SITE_GROUP_ID,
2029 PARENT_MEMBER_ID,
2030 CHILD_MEMBER_ID,
2031 DELETED_FLAG,
2032 OBJECT_VERSION_NUMBER,
2033 CREATED_BY,
2034 CREATION_DATE,
2035 LAST_UPDATED_BY,
2036 LAST_UPDATE_DATE,
2037 LAST_UPDATE_LOGIN,
2038 SEQUENCE_NUMBER
2039 )
2040 VALUES( v_dest_hier_version_id,
2041 v_dest_hier_id,
2042 v_dest_parent_id,
2043 v_child_id,
2044 'N',
2045 1,
2046 fnd_global.user_id,
2047 sysdate,
2048 fnd_global.user_id,
2049 sysdate,
2050 fnd_global.user_id,
2051 p_hier_members_rec.child_seq_number);
2052
2053 --2. REMOVE
2054 ELSIF p_hier_members_rec.transaction_type = 'REMOVE' THEN
2055 --dbms_output.put_line('Transaction REMOVE');
2056 --child id should appear in the destination hierarchy
2057 SELECT COUNT(*)
2058 INTO v_count
2059 FROM RRS_SITE_GROUP_MEMBERS
2060 WHERE SITE_GROUP_ID = v_dest_hier_id
2061 AND CHILD_MEMBER_ID = v_child_id;
2062
2063 IF v_count = 0 THEN
2064 --RRS_HIER_NO_CHILD_FOUND
2065 FND_MESSAGE.set_name('RRS', 'RRS_HIER_NO_CHILD_FOUND');
2066 FND_MESSAGE.set_token('NUM', p_hier_members_rec.child_id_number);
2067 FND_MESSAGE.set_token('TYPE', p_hier_members_rec.child_object_type);
2068 FND_MESSAGE.set_token('HIERARCHY_NAME', p_hier_members_rec.dest_hier_name);
2069 FND_MSG_PUB.add;
2070 --dbms_output.put_line('Child does not appear in the destination hierarchy');
2071 RAISE FND_API.G_EXC_ERROR;
2072 END IF;
2073
2074 --remove the subtree in members table
2075 DELETE FROM RRS_SITE_GROUP_MEMBERS
2076 WHERE CHILD_MEMBER_ID IN (
2077 SELECT CHILD_MEMBER_ID
2078 FROM RRS_SITE_GROUP_MEMBERS
2079 START WITH CHILD_MEMBER_ID = v_child_id
2080 AND SITE_GROUP_ID = v_dest_hier_id
2081 CONNECT BY PRIOR CHILD_MEMBER_ID = PARENT_MEMBER_ID
2082 AND SITE_GROUP_ID = v_dest_hier_id)
2083 AND SITE_GROUP_ID = v_dest_hier_id;
2084
2085 --3. COPY 4. MOVE
2086 ELSIF p_hier_members_rec.transaction_type = 'COPY'
2087 OR p_hier_members_rec.transaction_type = 'MOVE' THEN
2088 --dbms_output.put_line('Transaction '||p_hier_members_rec.transaction_type);
2089 --get source hier id
2090 BEGIN
2091 SELECT SITE_GROUP_ID
2092 INTO v_source_hier_id
2093 FROM RRS_SITE_GROUPS_VL
2094 WHERE NAME = p_hier_members_rec.source_hier_name;
2095 EXCEPTION
2096 WHEN NO_DATA_FOUND THEN
2097 --RRS_NO_HIER_FOUND
2098 FND_MESSAGE.set_name('RRS', 'RRS_NO_HIER_FOUND');
2099 FND_MESSAGE.set_token('HIERARCHY_NAME', p_hier_members_rec.source_hier_name);
2100 FND_MSG_PUB.add;
2101 --dbms_output.put_line('invalid source hier name: '||p_hier_members_rec.source_hier_name);
2102 RAISE FND_API.G_EXC_ERROR;
2103 END;
2104
2105 --1. child id must appear in the source hierarchy
2106 --2. get source parent id
2107 BEGIN
2108 SELECT PARENT_MEMBER_ID
2109 INTO v_source_parent_id
2110 FROM RRS_SITE_GROUP_MEMBERS
2111 WHERE SITE_GROUP_ID = v_source_hier_id
2112 AND CHILD_MEMBER_ID = v_child_id;
2113 EXCEPTION
2114 WHEN NO_DATA_FOUND THEN
2115 --RRS_HIER_NO_CHILD_FOUND
2116 FND_MESSAGE.set_name('RRS', 'RRS_HIER_NO_CHILD_FOUND');
2117 FND_MESSAGE.set_token('NUM', p_hier_members_rec.child_id_number);
2118 FND_MESSAGE.set_token('TYPE', p_hier_members_rec.child_object_type);
2119 FND_MESSAGE.set_token('HIERARCHY_NAME', p_hier_members_rec.source_hier_name);
2120 FND_MSG_PUB.add;
2121 --dbms_output.put_line('Child does not appear in the source hierarchy');
2122 RAISE FND_API.G_EXC_ERROR;
2123 END;
2124
2125 --source and dest hier cannot be the same for COPY transaction
2126 IF p_hier_members_rec.transaction_type = 'COPY'
2127 AND v_source_hier_id = v_dest_hier_id THEN
2128 --RRS_HIER_SAME_DEST_SOURCE
2129 FND_MESSAGE.set_name('RRS', 'RRS_HIER_SAME_DEST_SOURCE');
2130 FND_MSG_PUB.add;
2131 --dbms_output.put_line('dest hier cannot be the same as source hier');
2132 RAISE FND_API.G_EXC_ERROR;
2133 END IF;
2134
2135 --cannot move into its subtree for MOVE transaction
2136 IF p_hier_members_rec.transaction_type = 'MOVE' THEN
2137 SELECT COUNT(*)
2138 INTO v_count
2139 FROM (
2140 SELECT CHILD_MEMBER_ID
2141 FROM RRS_SITE_GROUP_MEMBERS
2142 START WITH CHILD_MEMBER_ID = v_child_id
2143 AND SITE_GROUP_ID = v_source_hier_id
2144 CONNECT BY PRIOR CHILD_MEMBER_ID = PARENT_MEMBER_ID
2145 AND SITE_GROUP_ID = v_source_hier_id) TMP
2146 WHERE CHILD_MEMBER_ID = v_dest_parent_id;
2147
2148 IF v_count <> 0 THEN
2149 --RRS_PARENT_DEST_UNDER_SOURCE
2150 FND_MESSAGE.set_name('RRS', 'RRS_PARENT_DEST_UNDER_SOURCE');
2151 FND_MSG_PUB.add;
2152 --dbms_output.put_line('Cannot move a site/node under its child');
2153 RAISE FND_API.G_EXC_ERROR;
2154 END IF;
2155 END IF;
2156
2157 --delete rows in RRS_SITE_GROUP_MEMBERS_TEMP
2158 DELETE FROM RRS_SITE_GROUP_MEMBERS_TEMP;
2159
2160 FOR rec IN parent_child_cursor LOOP
2161 --check only when source and dest hier are different
2162 IF v_source_hier_id <> v_dest_hier_id THEN
2163 --validate child id. Should not appear in dest hier
2164 SELECT COUNT(*)
2165 INTO v_count
2166 FROM RRS_SITE_GROUP_MEMBERS
2167 WHERE CHILD_MEMBER_ID = rec.C_ID
2168 AND SITE_GROUP_ID = v_dest_hier_id;
2169
2170 IF v_count <> 0 THEN
2171 --RRS_HIER_CHILD_EXISTS
2172 FND_MESSAGE.set_name('RRS', 'RRS_HIER_CHILD_EXISTS');
2173 FND_MESSAGE.set_token('NUM', p_hier_members_rec.child_id_number);
2174 FND_MESSAGE.set_token('TYPE', p_hier_members_rec.child_object_type);
2175 FND_MESSAGE.set_token('HIERARCHY_NAME', p_hier_members_rec.dest_hier_name);
2176 FND_MSG_PUB.add;
2177 --dbms_output.put_line('Child already exists in the destination hierarchy ');
2178 RAISE FND_API.G_EXC_ERROR;
2179 END IF;
2180 END IF;
2181
2182 --insert into members temp table
2183 IF rec.C_ID = v_child_id THEN --root node/site of the subtree
2184
2185 --check for duplicated node name under a same parent
2186 IF p_hier_members_rec.child_object_type = 'NODE' THEN
2187 --dbms_output.put_line('before check duplicated node name');
2188 SELECT COUNT(*)
2189 INTO v_count
2190 FROM RRS_SITE_GROUP_MEMBERS RSGM, RRS_SITE_GROUP_NODES_VL RSGNV
2191 WHERE RSGM.SITE_GROUP_ID = v_dest_hier_id
2192 AND RSGM.PARENT_MEMBER_ID = v_dest_parent_id
2193 AND RSGM.CHILD_MEMBER_ID = RSGNV.SITE_GROUP_NODE_ID
2194 AND RSGNV.NAME = v_child_name;
2195
2196 IF v_count <> 0 THEN
2197 --RRS_DUPLICATED_NODE_NAME
2198 FND_MESSAGE.set_name('RRS', 'RRS_DUPLICATED_NODE_NAME');
2199 FND_MESSAGE.set_token('NODE_NAME', v_child_name);
2200 FND_MSG_PUB.add;
2201 --dbms_output.put_line('Duplicated node names under the dest parent');
2202 RAISE FND_API.G_EXC_ERROR;
2203 END IF;
2204 END IF;
2205
2206 --dbms_output.put_line('before insert into temp table 1');
2207 INSERT INTO RRS_SITE_GROUP_MEMBERS_TEMP
2208 (
2209 PARENT_TYPE,
2210 PARENT_ID,
2211 PARENT_NUMBER,
2212 PARENT_PURPOSE_CODE,
2213 CHILD_TYPE,
2214 CHILD_ID,
2215 CHILD_NUMBER,
2216 CHILD_PURPOSE_CODE,
2217 SEQUENCE_NUMBER,
2218 CHILD_NODE_NAME,
2219 CHILD_NODE_DESCRIPTION
2220 )
2221 VALUES( p_hier_members_rec.dest_parent_object_type,
2222 v_dest_parent_id,
2223 p_hier_members_rec.dest_parent_id_number,
2224 v_dest_parent_purpose_code,
2225 p_hier_members_rec.child_object_type,
2226 v_child_id,
2227 p_hier_members_rec.child_id_number,
2228 v_child_purpose_code,
2229 p_hier_members_rec.child_seq_number,
2230 NULL,
2231 NULL
2232 );
2233
2234 ELSE --other node/site in the subtree
2235 --dbms_output.put_line('before insert into temp table 2');
2236 INSERT INTO RRS_SITE_GROUP_MEMBERS_TEMP
2237 (
2238 PARENT_TYPE,
2239 PARENT_ID,
2240 PARENT_NUMBER,
2241 PARENT_PURPOSE_CODE,
2242 CHILD_TYPE,
2243 CHILD_ID,
2244 CHILD_NUMBER,
2245 CHILD_PURPOSE_CODE,
2246 SEQUENCE_NUMBER,
2247 CHILD_NODE_NAME,
2248 CHILD_NODE_DESCRIPTION
2249 )
2250 VALUES( rec.P_TYPE,
2251 rec.P_ID,
2252 rec.P_NUMBER,
2253 rec.P_PURPOSE_CODE,
2254 rec.C_TYPE,
2255 rec.C_ID,
2256 rec.C_NUMBER,
2257 rec.C_PURPOSE_CODE,
2258 rec.C_SEQ_NUMBER,
2259 NULL,
2260 NULL
2261 );
2262 END IF;
2263 END LOOP;
2264
2265 --RulesFwk
2266 --dbms_output.put_line('before RulesFwk');
2267 IF v_dest_hier_purpose_code IS NOT NULL THEN
2268
2269 Validate_Rules_For_Members(
2270 p_hier_purpose_code => v_dest_hier_purpose_code,
2271 x_return_status => x_return_status,
2272 x_msg_data => x_msg_data);
2273
2274 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2275 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2276 RAISE FND_API.G_EXC_ERROR;
2277 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2278 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2279 END IF;
2280 END IF;
2281
2282 END IF;
2283
2284 --remove the subtree in source hier for MOVE transaction
2285 IF p_hier_members_rec.transaction_type = 'MOVE' THEN
2286 --dbms_output.put_line('before remove the subtree in source hier');
2287 DELETE FROM RRS_SITE_GROUP_MEMBERS
2288 WHERE CHILD_MEMBER_ID IN (
2289 SELECT CHILD_MEMBER_ID
2290 FROM RRS_SITE_GROUP_MEMBERS
2291 START WITH CHILD_MEMBER_ID = v_child_id
2292 AND SITE_GROUP_ID = v_source_hier_id
2293 CONNECT BY PRIOR CHILD_MEMBER_ID = PARENT_MEMBER_ID
2294 AND SITE_GROUP_ID = v_source_hier_id)
2295 AND SITE_GROUP_ID = v_source_hier_id;
2296 END IF;
2297
2298 --insert into members table
2299 --dbms_output.put_line('before insert into members table');
2300 FOR rec IN new_members_cursor LOOP
2301 --dbms_output.put_line(''||v_dest_hier_id||'/'||rec.PARENT_ID||'/'||rec.CHILD_ID);
2302 INSERT INTO RRS_SITE_GROUP_MEMBERS
2303 (
2304 SITE_GROUP_VERSION_ID,
2305 SITE_GROUP_ID,
2306 PARENT_MEMBER_ID,
2307 CHILD_MEMBER_ID,
2308 DELETED_FLAG,
2309 OBJECT_VERSION_NUMBER,
2310 CREATED_BY,
2311 CREATION_DATE,
2312 LAST_UPDATED_BY,
2313 LAST_UPDATE_DATE,
2314 LAST_UPDATE_LOGIN,
2315 SEQUENCE_NUMBER
2316 )
2317 VALUES( v_dest_hier_version_id,
2318 v_dest_hier_id,
2319 rec.PARENT_ID,
2320 rec.CHILD_ID,
2321 'N',
2322 1,
2323 fnd_global.user_id,
2324 sysdate,
2325 fnd_global.user_id,
2326 sysdate,
2327 fnd_global.user_id,
2328 rec.SEQUENCE_NUMBER);
2329 END LOOP;
2330
2331 ELSE
2332 --RRS_INVALID_TYPE
2333 FND_MESSAGE.set_name('RRS', 'RRS_INVALID_TYPE');
2334 FND_MESSAGE.set_token('TYPE', p_hier_members_rec.transaction_type);
2335 FND_MSG_PUB.add;
2336 --dbms_output.put_line('invalid transaction type');
2337 RAISE FND_API.G_EXC_ERROR;
2338 END IF;
2339
2340 IF FND_API.To_Boolean(p_commit) THEN
2341 COMMIT;
2342 END IF;
2343
2344 x_return_status := FND_API.G_RET_STS_SUCCESS;
2345
2346 EXCEPTION
2347 WHEN FND_API.G_EXC_ERROR THEN
2348 ROLLBACK TO Update_Hierarchy_Fine;
2349 x_msg_count := FND_MSG_PUB.Count_Msg;
2350 x_return_status := FND_API.G_RET_STS_ERROR;
2351 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2352 ROLLBACK TO Update_Hierarchy_Fine;
2353 x_msg_data := G_PKG_NAME || '.Update_Hierarchy_Fine:' || x_msg_data;
2354 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2355 WHEN OTHERS THEN
2356 ROLLBACK TO Update_Hierarchy_Fine;
2357 x_msg_data := G_PKG_NAME || '.Update_Hierarchy_Fine:' || SQLERRM;
2358 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2359 END Update_Hierarchy_Fine;
2360
2361 -- Hierarchy and Hierarchy Association Validation API
2362 procedure Validate_Hierarchy_Status(
2363 p_hier_id IN VARCHAR2,
2364 x_return_status OUT NOCOPY VARCHAR2,
2365 x_msg_count OUT NOCOPY NUMBER,
2366 x_msg_data OUT NOCOPY VARCHAR2
2367 ) IS
2368
2369 v_hier_purpose_code VARCHAR2(30);
2370
2371 CURSOR parent_child_cursor IS
2372 SELECT DECODE(RSV.SITE_ID, NULL, 'NODE', 'SITE') AS P_TYPE,
2373 RSGM.PARENT_MEMBER_ID AS P_ID,
2374 NVL(RSV.SITE_IDENTIFICATION_NUMBER, RSGNV.NODE_IDENTIFICATION_NUMBER) AS P_NUMBER,
2375 DECODE(RSV.SITE_ID, NULL, RSGNV.NODE_PURPOSE_CODE, RSU.SITE_USE_TYPE_CODE) AS P_PURPOSE_CODE,
2376 DECODE(RSV2.SITE_ID, NULL, 'NODE', 'SITE') AS C_TYPE,
2377 RSGM.CHILD_MEMBER_ID AS C_ID,
2378 NVL(RSV2.SITE_IDENTIFICATION_NUMBER, RSGNV2.NODE_IDENTIFICATION_NUMBER) AS C_NUMBER,
2379 DECODE(RSV2.SITE_ID, NULL, RSGNV2.NODE_PURPOSE_CODE, RSU2.SITE_USE_TYPE_CODE) AS C_PURPOSE_CODE,
2380 RSGM.SEQUENCE_NUMBER AS C_SEQ_NUMBER
2381 FROM RRS_SITE_GROUP_MEMBERS RSGM,
2382 RRS_SITE_GROUP_NODES_VL RSGNV,
2383 RRS_SITES_VL RSV, RRS_SITE_USES RSU,
2384 RRS_SITE_GROUP_NODES_VL RSGNV2,
2385 RRS_SITES_VL RSV2,
2386 RRS_SITE_USES RSU2
2387 WHERE RSGM.PARENT_MEMBER_ID = RSV.SITE_ID(+)
2388 AND RSGM.PARENT_MEMBER_ID = RSU.SITE_ID(+)
2389 AND NVL(RSU.IS_PRIMARY_FLAG, 'Y') = 'Y'
2390 AND RSGM.PARENT_MEMBER_ID = RSGNV.SITE_GROUP_NODE_ID(+)
2391 AND RSGM.CHILD_MEMBER_ID = RSV2.SITE_ID(+)
2392 AND RSGM.CHILD_MEMBER_ID = RSU2.SITE_ID(+)
2393 AND NVL(RSU2.IS_PRIMARY_FLAG, 'Y') = 'Y'
2394 AND RSGM.CHILD_MEMBER_ID = RSGNV2.SITE_GROUP_NODE_ID(+)
2395 START WITH PARENT_MEMBER_ID = -1
2396 AND SITE_GROUP_ID = p_hier_id
2397 CONNECT BY PRIOR CHILD_MEMBER_ID = PARENT_MEMBER_ID
2398 AND SITE_GROUP_ID = p_hier_id;
2399
2400 BEGIN
2401
2402 --get hier purpose code
2403 BEGIN
2404 SELECT GROUP_PURPOSE_CODE
2405 INTO v_hier_purpose_code
2406 FROM RRS_SITE_GROUPS_VL RSGV
2407 WHERE RSGV.SITE_GROUP_ID = p_hier_id;
2408 EXCEPTION
2409 WHEN NO_DATA_FOUND THEN
2410 --RRS_NO_HIER_ID_FOUND
2411 FND_MESSAGE.set_name('RRS', 'RRS_NO_HIER_ID_FOUND');
2412 FND_MESSAGE.set_token('ID', p_hier_id);
2413 FND_MSG_PUB.add;
2414 --dbms_output.put_line('invalid dest hier id: '||p_hier_id);
2415 RAISE FND_API.G_EXC_ERROR;
2416 END;
2417
2418 --RulesFwk
2419 --dbms_output.put_line('before RulesFwk');
2420 IF v_hier_purpose_code IS NOT NULL THEN
2421
2422 --delete rows in RRS_SITE_GROUP_MEMBERS_TEMP
2423 DELETE FROM RRS_SITE_GROUP_MEMBERS_TEMP;
2424
2425 FOR rec IN parent_child_cursor LOOP
2426 IF rec.P_ID <> -1 THEN
2427 --insert into members temp table
2428 --dbms_output.put_line('before insert into temp table 2');
2429 INSERT INTO RRS_SITE_GROUP_MEMBERS_TEMP
2430 (
2431 PARENT_TYPE,
2432 PARENT_ID,
2433 PARENT_NUMBER,
2434 PARENT_PURPOSE_CODE,
2435 CHILD_TYPE,
2436 CHILD_ID,
2437 CHILD_NUMBER,
2438 CHILD_PURPOSE_CODE,
2439 SEQUENCE_NUMBER,
2440 CHILD_NODE_NAME,
2441 CHILD_NODE_DESCRIPTION
2442 )
2443 VALUES( rec.P_TYPE,
2444 rec.P_ID,
2445 rec.P_NUMBER,
2446 rec.P_PURPOSE_CODE,
2447 rec.C_TYPE,
2448 rec.C_ID,
2449 rec.C_NUMBER,
2450 rec.C_PURPOSE_CODE,
2451 rec.C_SEQ_NUMBER,
2452 NULL,
2453 NULL
2454 );
2455 END IF;
2456 END LOOP;
2457
2458 Validate_Rules_For_Members(
2459 p_hier_purpose_code => v_hier_purpose_code,
2460 x_return_status => x_return_status,
2461 x_msg_data => x_msg_data);
2462
2463 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2464 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2465 RAISE FND_API.G_EXC_ERROR;
2466 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2467 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2468 END IF;
2469 END IF;
2470
2471 END IF;
2472
2473 x_return_status := FND_API.G_RET_STS_SUCCESS;
2474
2475 EXCEPTION
2476 WHEN FND_API.G_EXC_ERROR THEN
2477 x_msg_count := FND_MSG_PUB.Count_Msg;
2478 x_return_status := FND_API.G_RET_STS_ERROR;
2479 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2480 x_msg_data := G_PKG_NAME || '.Validate_Hierarchy_Status:' || x_msg_data;
2481 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2482 WHEN OTHERS THEN
2483 x_msg_data := G_PKG_NAME || '.Validate_Hierarchy_Status:' || SQLERRM;
2484 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2485 END Validate_Hierarchy_Status;
2486
2487 procedure Validate_Hierarchy_Association(
2488 p_hier_id IN VARCHAR2,
2489 p_parent_id IN VARCHAR2,
2490 p_parent_object_type IN VARCHAR2,
2491 p_child_id IN VARCHAR2,
2492 p_child_object_type IN VARCHAR2,
2493 x_return_status OUT NOCOPY VARCHAR2,
2494 x_msg_count OUT NOCOPY NUMBER,
2495 x_msg_data OUT NOCOPY VARCHAR2
2496 ) IS
2497
2498 v_parent_id_number VARCHAR2(30);
2499 v_parent_purpose_code VARCHAR2(30);
2500 v_child_id_number VARCHAR2(30);
2501 v_child_purpose_code VARCHAR2(30);
2502 v_hier_purpose_code VARCHAR2(30);
2503 v_hier_name VARCHAR2(30);
2504 v_count NUMBER;
2505
2506 BEGIN
2507
2508 --get hier purpose code
2509 BEGIN
2510 SELECT GROUP_PURPOSE_CODE, NAME
2511 INTO v_hier_purpose_code, v_hier_name
2512 FROM RRS_SITE_GROUPS_VL RSGV
2513 WHERE RSGV.SITE_GROUP_ID = p_hier_id;
2514 EXCEPTION
2515 WHEN NO_DATA_FOUND THEN
2516 --RRS_NO_HIER_ID_FOUND
2517 FND_MESSAGE.set_name('RRS', 'RRS_NO_HIER_ID_FOUND');
2518 FND_MESSAGE.set_token('ID', p_hier_id);
2519 FND_MSG_PUB.add;
2520 --dbms_output.put_line('invalid dest hier id: '||p_hier_id);
2521 RAISE FND_API.G_EXC_ERROR;
2522 END;
2523
2524 --get parent and child info
2525 IF p_parent_object_type = 'SITE' THEN
2526 BEGIN
2527 SELECT RSV.SITE_IDENTIFICATION_NUMBER, RSU.SITE_USE_TYPE_CODE
2528 INTO v_parent_id_number, v_parent_purpose_code
2529 FROM RRS_SITES_VL RSV, RRS_SITE_USES RSU
2530 WHERE RSV.SITE_ID = p_parent_id
2531 AND RSV.SITE_ID = RSU.SITE_ID(+)
2532 AND RSU.IS_PRIMARY_FLAG(+) = 'Y';
2533 EXCEPTION
2534 WHEN NO_DATA_FOUND THEN
2535 --RRS_NO_SITE_FOUND
2536 FND_MESSAGE.set_name('RRS', 'RRS_NO_SITE_FOUND');
2537 FND_MESSAGE.set_token('SITE_ID_NUM', p_parent_id);
2538 FND_MSG_PUB.add;
2539 RAISE FND_API.G_EXC_ERROR;
2540 END;
2541 ELSIF p_parent_object_type = 'NODE' THEN
2542 BEGIN
2543 SELECT NODE_IDENTIFICATION_NUMBER, NODE_PURPOSE_CODE
2544 INTO v_parent_id_number, v_parent_purpose_code
2545 FROM RRS_SITE_GROUP_NODES_VL
2546 WHERE SITE_GROUP_NODE_ID = p_parent_id;
2547 EXCEPTION
2548 WHEN NO_DATA_FOUND THEN
2549 --RRS_NO_NODE_FOUND
2550 FND_MESSAGE.set_name('RRS', 'RRS_NO_NODE_FOUND');
2551 FND_MESSAGE.set_token('NODE_ID_NUM', p_parent_id);
2552 FND_MSG_PUB.add;
2553 RAISE FND_API.G_EXC_ERROR;
2554 END;
2555 ELSE
2556 --RRS_INVALID_TYPE
2557 FND_MESSAGE.set_name('RRS', 'RRS_INVALID_TYPE');
2558 FND_MESSAGE.set_token('TYPE', p_parent_object_type);
2559 FND_MSG_PUB.add;
2560 RAISE FND_API.G_EXC_ERROR;
2561 END IF;
2562
2563 IF p_child_object_type = 'SITE' THEN
2564 BEGIN
2565 SELECT RSV.SITE_IDENTIFICATION_NUMBER, RSU.SITE_USE_TYPE_CODE
2566 INTO v_child_id_number, v_child_purpose_code
2567 FROM RRS_SITES_VL RSV, RRS_SITE_USES RSU
2568 WHERE RSV.SITE_ID = p_child_id
2569 AND RSV.SITE_ID = RSU.SITE_ID(+)
2570 AND RSU.IS_PRIMARY_FLAG(+) = 'Y';
2571 EXCEPTION
2572 WHEN NO_DATA_FOUND THEN
2573 --RRS_NO_SITE_FOUND
2574 FND_MESSAGE.set_name('RRS', 'RRS_NO_SITE_FOUND');
2575 FND_MESSAGE.set_token('SITE_ID_NUM', p_child_id);
2576 FND_MSG_PUB.add;
2577 RAISE FND_API.G_EXC_ERROR;
2578 END;
2579 ELSIF p_child_object_type = 'NODE' THEN
2580 BEGIN
2581 SELECT NODE_IDENTIFICATION_NUMBER, NODE_PURPOSE_CODE
2582 INTO v_child_id_number, v_child_purpose_code
2583 FROM RRS_SITE_GROUP_NODES_VL
2584 WHERE SITE_GROUP_NODE_ID = p_child_id;
2585 EXCEPTION
2586 WHEN NO_DATA_FOUND THEN
2587 --RRS_NO_NODE_FOUND
2588 FND_MESSAGE.set_name('RRS', 'RRS_NO_NODE_FOUND');
2589 FND_MESSAGE.set_token('NODE_ID_NUM', p_child_id);
2590 FND_MSG_PUB.add;
2591 RAISE FND_API.G_EXC_ERROR;
2592 END;
2593 ELSE
2594 --RRS_INVALID_TYPE
2595 FND_MESSAGE.set_name('RRS', 'RRS_INVALID_TYPE');
2596 FND_MESSAGE.set_token('TYPE', p_child_object_type);
2597 FND_MSG_PUB.add;
2598 RAISE FND_API.G_EXC_ERROR;
2599 END IF;
2600
2601 --child id should not appear in the dest hier
2602 SELECT COUNT(*)
2603 INTO v_count
2604 FROM RRS_SITE_GROUP_MEMBERS
2605 WHERE CHILD_MEMBER_ID = p_child_id
2606 AND SITE_GROUP_ID = p_hier_id;
2607 IF v_count <> 0 THEN
2608 --RRS_HIER_CHILD_EXISTS
2609 FND_MESSAGE.set_name('RRS', 'RRS_HIER_CHILD_EXISTS');
2610 FND_MESSAGE.set_token('NUM', v_child_id_number);
2611 FND_MESSAGE.set_token('TYPE', p_child_object_type);
2612 FND_MESSAGE.set_token('HIERARCHY_NAME', v_hier_name);
2613 FND_MSG_PUB.add;
2614 --dbms_output.put_line('Child already exists in the destination hierarchy: '||v_child_id_number);
2615 RAISE FND_API.G_EXC_ERROR;
2616 END IF;
2617
2618 --dbms_output.put_line(v_hier_purpose_code||' '||v_parent_purpose_code||' '||v_child_purpose_code);
2619
2620 --RulesFwk
2621 --dbms_output.put_line('before RulesFwk');
2622 IF v_hier_purpose_code IS NOT NULL THEN
2623
2624 Validate_Rules_For_Child(
2625 p_hier_purpose_code => v_hier_purpose_code,
2626 p_parent_id_number => v_parent_id_number,
2627 p_parent_object_type => p_parent_object_type,
2628 p_parent_purpose_code => v_parent_purpose_code,
2629 p_child_id_number => v_child_id_number,
2630 p_child_object_type => p_child_object_type,
2631 p_child_purpose_code => v_child_purpose_code,
2632 x_return_status => x_return_status,
2633 x_msg_data => x_msg_data
2634 );
2635
2636 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2637 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2638 RAISE FND_API.G_EXC_ERROR;
2639 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2640 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2641 END IF;
2642 END IF;
2643
2644 END IF;
2645
2646 x_return_status := FND_API.G_RET_STS_SUCCESS;
2647
2648 EXCEPTION
2649 WHEN FND_API.G_EXC_ERROR THEN
2650 x_msg_count := FND_MSG_PUB.Count_Msg;
2651 x_return_status := FND_API.G_RET_STS_ERROR;
2652 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2653 x_msg_data := G_PKG_NAME || '.Validate_Hierarchy_Association:' || x_msg_data;
2654 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2655 WHEN OTHERS THEN
2656 x_msg_data := G_PKG_NAME || '.Validate_Hierarchy_Association:' || SQLERRM;
2657 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2658 END Validate_Hierarchy_Association;
2659
2660 -------Testing procedures------
2661 /*
2662 procedure Update_Hierarchy_Header_Test IS
2663 v_name VARCHAR2(30) := 'R0001';
2664 v_new_name VARCHAR2(30) := 'R0001';
2665 v_desc VARCHAR2(30) := 'test desc';
2666 v_purp VARCHAR2(30) := 'OPER';
2667 v_sd DATE := TO_DATE('jan 23 2009', 'MON DD YYYY');
2668 v_ed DATE := TO_DATE('DEC 23 2009', 'MON DD YYYY');
2669 x_return_status VARCHAR2(30);
2670 x_msg_count NUMBER;
2671 x_msg_data VARCHAR2(30);
2672 BEGIN
2673
2674 RRS_HIERARCHY_CRUD_PKG.Update_Hierarchy_Header(
2675 p_name => v_name,
2676 --p_new_name => v_new_name,
2677 p_description => v_desc,
2678 --p_purpose_code => v_purp,
2679 p_start_date => v_sd,
2680 --p_end_date => v_ed,
2681 p_nullify_flag => 'T',
2682 x_return_status => x_return_status,
2683 x_msg_count => x_msg_count,
2684 x_msg_data => x_msg_data
2685 );
2686 --dbms_output.put_line('update hierarchy header: ' || x_return_status);
2687 END Update_Hierarchy_Header_Test;
2688
2689 procedure Update_Hierarchy_Node_Test IS
2690 v_number VARCHAR2(30) := 'temp_11040';
2691 v_name VARCHAR2(30) := 'tempNode@';
2692 v_desc VARCHAR2(30) := 'test node desc';
2693 v_purp VARCHAR2(30) := 'BRKPNT';
2694 x_return_status VARCHAR2(30);
2695 x_msg_count NUMBER;
2696 x_msg_data VARCHAR2(30);
2697 BEGIN
2698
2699 RRS_HIERARCHY_CRUD_PKG.Update_Hierarchy_Node(
2700 p_number => v_number,
2701 --p_name => v_name,
2702 p_description => v_desc,
2703 --p_purpose_code => v_purp
2704 --p_nullify_flag => 'T'
2705 x_return_status => x_return_status,
2706 x_msg_count => x_msg_count,
2707 x_msg_data => x_msg_data
2708 );
2709 END Update_Hierarchy_Node_Test;
2710
2711 procedure Create_Hierarchy_Node_Test IS
2712 v_number VARCHAR2(30) := 'CREATE_TEST';
2713 v_name VARCHAR2(30) := 'newlyCreatedNode';
2714 v_desc VARCHAR2(30) := 'test node desc';
2715 v_purp VARCHAR2(30) := 'BRKPNT';
2716 x_return_status VARCHAR2(30);
2717 x_msg_count NUMBER;
2718 x_msg_data VARCHAR2(30);
2719 BEGIN
2720
2721 RRS_HIERARCHY_CRUD_PKG.Create_Hierarchy_Node(
2722 p_number => v_number,
2723 p_name => v_name,
2724 p_description => v_desc,
2725 p_purpose_code => v_purp,
2726 x_return_status => x_return_status,
2727 x_msg_count => x_msg_count,
2728 x_msg_data => x_msg_data
2729 );
2730 --dbms_output.put_line('msg count: ' || x_msg_count);
2731 END Create_Hierarchy_Node_Test;
2732
2733 procedure Create_Hierarchy_Coarse_Test IS
2734 v_name VARCHAR2(30) := 'R0005';
2735 v_desc VARCHAR2(30) := 'test desc';
2736 v_purp VARCHAR2(30) := 'OPER';
2737 v_sd DATE := TO_DATE('jan 23 2009', 'MON DD YYYY');
2738 v_ed DATE := TO_DATE('DEC 23 2009', 'MON DD YYYY');
2739 v_tab RRS_HIER_MEMBERS_COARSE_TAB;
2740 x_return_status VARCHAR2(30);
2741 x_msg_count NUMBER;
2742 x_msg_data VARCHAR2(30);
2743 BEGIN
2744
2745 v_tab := RRS_HIER_MEMBERS_COARSE_TAB();
2746 v_tab.EXTEND();
2747 v_tab(1) := RRS_HIER_MEMBERS_COARSE_REC('NONE', NULL, 'NONE', 'NODE', NULL, 'ROOT_R0005', 0, 'R0005', NULL, 'ROOT');
2748 v_tab.EXTEND();
2749 v_tab(2) := RRS_HIER_MEMBERS_COARSE_REC('NODE', NULL, 'ROOT_R0005', 'SITE', 10141, NULL, 10, NULL, NULL, NULL);
2750 v_tab.EXTEND();
2751 v_tab(3) := RRS_HIER_MEMBERS_COARSE_REC('NODE', NULL, 'ROOT_R0005', 'NODE', NULL, 'NEW_NODE_R5', 20, 'new node R5', NULL, 'BRKPNT');
2752 v_tab.EXTEND();
2753 v_tab(4) := RRS_HIER_MEMBERS_COARSE_REC('NODE', NULL, 'NEW_NODE_R5', 'NODE', 10005, NULL, 10, 'tempNODE', 'test description', 'BRKPNT');
2754 v_tab.EXTEND();
2755 v_tab(5) := RRS_HIER_MEMBERS_COARSE_REC('NODE', NULL, 'NEW_NODE_R5', 'SITE', NULL, 'MBOX0001', 20, NULL, NULL, NULL);
2756 --v_tab.EXTEND();
2757 --v_tab(6) := RRS_HIER_MEMBERS_COARSE_REC('NODE', NULL, 'temp_11040', 'SITE', NULL, 'MBOX980', 10, NULL, NULL, NULL);
2758
2759 RRS_HIERARCHY_CRUD_PKG.Create_Hierarchy_Coarse(
2760 p_hier_name => v_name,
2761 p_hier_description => v_desc,
2762 --p_hier_purpose_code => v_purp,
2763 p_hier_start_date => v_sd,
2764 p_hier_end_date => v_ed,
2765 p_hier_members_tab => v_tab,
2766 x_return_status => x_return_status,
2767 x_msg_count => x_msg_count,
2768 x_msg_data => x_msg_data
2769 );
2770 --dbms_output.put_line('create hierarchy coarse: ' || x_return_status);
2771 END Create_Hierarchy_Coarse_Test;
2772
2773 procedure Update_Hierarchy_Coarse_Test IS
2774 v_name VARCHAR2(30) := 'Unit Hierarchy';
2775 v_new_name VARCHAR2(30) := 'R0004';
2776 v_desc VARCHAR2(30) := 'TEST ROUTE';
2777 v_purp VARCHAR2(30) := 'OPER2';
2778 v_sd DATE := TO_DATE('jan 24 2009', 'MON DD YYYY');
2779 v_ed DATE := TO_DATE('DEC 23 2009', 'MON DD YYYY');
2780 v_tab RRS_HIER_MEMBERS_COARSE_TAB;
2781 x_return_status VARCHAR2(30);
2782 x_msg_count NUMBER;
2783 x_msg_data VARCHAR2(30);
2784 BEGIN
2785
2786 v_tab := RRS_HIER_MEMBERS_COARSE_TAB();
2787
2788 v_tab.EXTEND();
2789 v_tab(1) := RRS_HIER_MEMBERS_COARSE_REC('NONE', -1, NULL, 'NODE', 10003, NULL, 0, NULL, NULL, 'ROOT');
2790 v_tab.EXTEND();
2791 v_tab(2) := RRS_HIER_MEMBERS_COARSE_REC('NODE', 10003, NULL, 'NODE', 10006, NULL, 0, NULL, NULL, NULL);
2792 v_tab.EXTEND();
2793 v_tab(3) := RRS_HIER_MEMBERS_COARSE_REC('NODE', 10006, NULL, 'SITE', 10002, NULL, 0, NULL, NULL, NULL);
2794 v_tab.EXTEND();
2795 v_tab(4) := RRS_HIER_MEMBERS_COARSE_REC('NODE', 10003, NULL, 'NODE', 10004, NULL, 0, NULL, NULL, NULL);
2796 v_tab.EXTEND();
2797 v_tab(5) := RRS_HIER_MEMBERS_COARSE_REC('NODE', 10004, NULL, 'SITE', 10000, NULL, 0, NULL, NULL, NULL);
2798 v_tab.EXTEND();
2799 v_tab(6) := RRS_HIER_MEMBERS_COARSE_REC('NODE', 10003, NULL, 'NODE', 10005, NULL, 0, NULL, NULL, NULL);
2800
2801 RRS_HIERARCHY_CRUD_PKG.Update_Hierarchy_Coarse(
2802 p_hier_name => v_name,
2803 --p_hier_new_name => v_new_name,
2804 --p_hier_description => v_desc,
2805 --p_hier_purpose_code => v_purp,
2806 --p_hier_start_date => v_sd,
2807 --p_nullify_flag => 'T',
2808 --p_hier_end_date => v_ed,
2809 p_hier_members_tab => v_tab,
2810 x_return_status => x_return_status,
2811 x_msg_count => x_msg_count,
2812 x_msg_data => x_msg_data
2813 );
2814 --dbms_output.put_line('update hierarchy coarse: ' || x_return_status);
2815 END Update_Hierarchy_Coarse_Test;
2816
2817 procedure Update_Hierarchy_Fine_Test IS
2818 x_return_status VARCHAR2(30);
2819 v_rec RRS_HIER_MEMBERS_FINE_REC;
2820 x_msg_count NUMBER;
2821 x_msg_data VARCHAR2(30);
2822 BEGIN
2823
2824 v_rec := RRS_HIER_MEMBERS_FINE_REC('ADD', NULL, 'Unit Hierarchy', NULL, NULL, 'NODE', '10004', 'SITE', 'NH_Template_Site_1', 10);
2825 --v_rec := RRS_HIER_MEMBERS_FINE_REC('ADD', NULL, 'R0001', NULL, NULL, 'NODE', 'NEW_NODE_R1', 'NODE', '10004', 10);
2826 --v_rec := RRS_HIER_MEMBERS_FINE_REC('REMOVE', NULL, 'R0001', NULL, NULL, NULL, NULL, 'SITE', 'MBOX0001', 10);
2827 --v_rec := RRS_HIER_MEMBERS_FINE_REC('REMOVE', NULL, 'R0001', NULL, NULL, NULL, NULL, 'NODE', '10005', 10);
2828 --v_rec := RRS_HIER_MEMBERS_FINE_REC('COPY', 'R0002', 'R0001', NULL, NULL, 'SITE', 'NH_SITE_1','SITE','MBOX0001', 10);
2829 --v_rec := RRS_HIER_MEMBERS_FINE_REC('MOVE', 'R0002', 'R0001', NULL, NULL, 'SITE', 'NH_SITE_1','NODE','NEW_NODE_R2', 10);
2830 --v_rec := RRS_HIER_MEMBERS_FINE_REC('COPY', 'R0001', 'R0002', NULL, NULL, 'SITE', 'MBOX0001','NODE','NEW_NODE_R1', 10);
2831 Update_Hierarchy_Fine(
2832 p_hier_members_rec => v_rec,
2833 x_return_status => x_return_status,
2834 x_msg_count => x_msg_count,
2835 x_msg_data => x_msg_data
2836 );
2837 --dbms_output.put_line('update hierarchy fine: ' || x_return_status);
2838 END Update_Hierarchy_Fine_Test;
2839 */
2840
2841 END RRS_HIERARCHY_CRUD_PKG;
2842
2843