[Home] [Help]
PACKAGE BODY: APPS.AHL_MC_NODE_PVT
Source
1 PACKAGE BODY AHL_MC_Node_PVT AS
2 /* $Header: AHLVNODB.pls 120.6 2007/12/21 13:33:05 sathapli ship $ */
3
4 G_USER_ID CONSTANT NUMBER := TO_NUMBER(FND_GLOBAL.USER_ID);
5 G_LOGIN_ID CONSTANT NUMBER := TO_NUMBER(FND_GLOBAL.LOGIN_ID);
6 G_SYSDATE CONSTANT DATE := SYSDATE;
7 G_TRUNC_DATE CONSTANT DATE := TRUNC(SYSDATE);
8
9 -------------------
10 -- Common variables
11 -------------------
12 l_dummy_varchar VARCHAR2(1);
13 l_dummy_number NUMBER;
14
15 --------------------
16 -- Common cursors --
17 --------------------
18 CURSOR check_uom_exists
19 (
20 p_uom_code IN VARCHAR2
21 )
22 IS
23 SELECT 'x'
24 FROM MTL_UNITS_OF_MEASURE
25 WHERE uom_code = p_uom_code;
26
27 -------------------------------------
28 -- Validation procedure signatures --
29 -------------------------------------
30 PROCEDURE Validate_Node_Exists
31 (
32 p_rel_id in number,
33 p_object_ver_num in number
34 );
35
36 PROCEDURE Validate_Node
37 (
38 p_x_node_rec in out nocopy Node_Rec_Type
39 );
40
41 PROCEDURE Validate_Counter_Exists
42 (
43 p_ctr_rule_id in number,
44 p_object_ver_num in number
45 );
46
47 PROCEDURE Validate_Counter_Rule
48 (
49 p_counter_rule_rec in Counter_Rule_Rec_Type
50 );
51
52 PROCEDURE Validate_Subconfig_Exists
53 (
54 p_submc_assos_id in number,
55 p_object_ver_num in number
56 );
57
58 PROCEDURE Validate_priority
59 (
60 p_subconfig_tbl in Subconfig_Tbl_Type
61 );
62
63 /* Jerry commented out on 08/12/2004 because it is never used
64 PROCEDURE Check_Cyclic_Rel
65 (
66 p_subconfig_id in number,
67 p_rel_id in number
68 );
69 */
70
71 -----------------------------------
72 -- Non-spec Procedure Signatures --
73 -----------------------------------
74 FUNCTION Get_MC_Status
75 (
76 p_rel_id in number,
77 p_mc_header_id in number
78 )
79 RETURN VARCHAR2;
80 -- check whether cyclic relation exist
81 FUNCTION Cyclic_Relation_Exists
82 (
83 p_subconfig_id in number,
84 p_dest_config_id in number
85 )
86 RETURN BOOLEAN;
87
88 PROCEDURE Set_Header_Status
89 (
90 p_rel_id IN NUMBER
91 );
92
93 PROCEDURE Create_Counter_Rule
94 (
95 p_x_counter_rule_rec IN OUT NOCOPY Counter_Rule_Rec_Type
96 );
97
98 PROCEDURE Modify_Counter_Rule
99 (
100 p_x_counter_rule_rec IN OUT NOCOPY Counter_Rule_Rec_Type
101 );
102
103 PROCEDURE Delete_Counter_Rule
104 (
105 p_ctr_update_rule_id IN NUMBER,
106 p_object_ver_num IN NUMBER
107 );
108
109 PROCEDURE Attach_Subconfig
110 (
111 p_x_subconfig_rec IN OUT NOCOPY Subconfig_Rec_Type
112 );
113
114 PROCEDURE Modify_Subconfig
115 (
116 p_x_subconfig_rec IN OUT NOCOPY Subconfig_Rec_Type
117 );
118
119 PROCEDURE Detach_Subconfig
120 (
121 p_mc_config_relation_id IN NUMBER,
122 p_object_ver_num IN NUMBER
123 );
124
125 PROCEDURE Copy_Subconfig
126 (
127 p_source_rel_id IN NUMBER,
128 p_dest_rel_id IN NUMBER
129 );
130
131 ---------------------
132 -- Spec Procedures --
133 ---------------------
134 PROCEDURE Create_Node
135 (
136 p_api_version IN NUMBER,
137 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
138 p_commit IN VARCHAR2 := FND_API.G_FALSE,
139 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
140 x_return_status OUT NOCOPY VARCHAR2,
141 x_msg_count OUT NOCOPY NUMBER,
142 x_msg_data OUT NOCOPY VARCHAR2,
143 p_x_node_rec IN OUT NOCOPY Node_Rec_Type,
144 p_x_counter_rules_tbl IN OUT NOCOPY Counter_Rules_Tbl_Type,
145 p_x_subconfig_tbl IN OUT NOCOPY SubConfig_Tbl_Type
146 )
147 IS
148
149 -- Define cursor check_dup_poskey to check for duplicate position key within same MC
150 CURSOR check_dup_poskey
151 IS
152 SELECT 'x'
153 FROM ahl_mc_relationships
154 WHERE mc_header_id = p_x_node_rec.mc_header_id AND
155 position_key = p_x_node_rec.position_key;
156 -- Since expired nodes are also copied, so duplicate position key check must happen for expired nodes also
157 -- AND G_TRUNC_DATE < trunc(nvl(p_x_node_rec.active_end_date, G_SYSDATE + 1));
158
159 -- Define local variables
160 l_api_name CONSTANT VARCHAR2(30) := 'Create_Node';
161 l_api_version CONSTANT NUMBER := 1.0;
162 l_return_status VARCHAR2(1);
163 l_msg_count NUMBER;
164 l_msg_data VARCHAR2(2000);
165
166 l_header_status VARCHAR2(30);
167
168 BEGIN
169
170 -- Standard start of API savepoint
171 SAVEPOINT Create_Node_SP;
172
173 -- Standard call to check for call compatibility
174 IF NOT FND_API.compatible_api_call(l_api_version, p_api_version, l_api_name, G_PKG_NAME)
175 THEN
176 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
177 END IF;
178
179 -- Initialize message list if p_init_msg_list is set to TRUE
180 IF FND_API.TO_BOOLEAN(p_init_msg_list)
181 THEN
182 FND_MSG_PUB.Initialize;
183 END IF;
184
185 -- Initialize API return status to success
186 x_return_status := FND_API.G_RET_STS_SUCCESS;
187
188 -- API body starts here
189 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
190 THEN
191 fnd_log.string
192 (
193 fnd_log.level_procedure,
194 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||'.begin',
195 'At the start of PLSQL procedure'
196 );
197 END IF;
198
199 -- 1a. Validate config_status_code of the MC is 'DRAFT' or 'APPROVAL_REJECTED'
200 IF (p_x_node_rec.parent_relationship_id IS NOT NULL)
201 THEN
202 l_header_status := Get_MC_Status(null, p_x_node_rec.mc_header_id);
203 IF NOT (l_header_status IN ('DRAFT', 'APPROVAL_REJECTED'))
204 THEN
205 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_NODE_STS_INV');
206 FND_MSG_PUB.ADD;
207 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
208 THEN
209 fnd_log.message
210 (
211 fnd_log.level_exception,
212 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
213 false
214 );
215 END IF;
216 RAISE FND_API.G_EXC_ERROR;
217 ELSIF (l_header_status = 'APPROVAL_REJECTED')
218 THEN
219 -- 1b. Set status of MC to DRAFT if APPROVAL_REJECTED
220 Set_Header_Status(p_x_node_rec.relationship_id);
221 END IF;
222 END IF;
223 -- 2. For the MC node with relationship_id = p_x_node_rec.parent_relationship_id [parent node]
224 -- 3a. Validate p_x_node_rec.position_ref_code exists
225 -- 3b. Validate p_x_node_rec.position_necessity_code
226 -- 3c. Validate p_x_node_rec.quantity > 0
227 -- 3d. Validate p_x_node_rec.uom_code exists
228 -- 3e. Validate p_x_node_rec.item_group_id exists
229 -- 3f. Validate p_x_node_rec.display_order > 0
230 -- 3g. Validate p_x_node_rec.display_order is not equal to display_order of all nodes at the same level
231 -- Validate dates
232 -- 3m. Validate the item_group does not have any item association with quantity > 1
233 Validate_Node(p_x_node_rec);
234
235 IF (p_x_node_rec.position_key IS NULL)
236 THEN
237 SELECT ahl_mc_rel_pos_key_s.nextval INTO p_x_node_rec.position_key FROM DUAL;
238 ELSE
239 -- 3j.i. Validate p_x_node_rec.position_key is unique within this MC
240 OPEN check_dup_poskey;
241 FETCH check_dup_poskey INTO l_dummy_varchar;
242 IF (check_dup_poskey%FOUND)
243 THEN
244 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_DUP_POSKEY');
245 FND_MESSAGE.Set_Token('POSREF', p_x_node_rec.position_ref_meaning);
246 FND_MSG_PUB.ADD;
247 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
248 THEN
249 fnd_log.message
250 (
251 fnd_log.level_exception,
252 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
253 false
254 );
255 END IF;
256 END IF;
257 CLOSE check_dup_poskey;
258 END IF;
259
260 -- Check Error Message stack.
261 x_msg_count := FND_MSG_PUB.count_msg;
262 IF x_msg_count > 0 THEN
263 RAISE FND_API.G_EXC_ERROR;
264 END IF;
265
266 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
267 THEN
268 fnd_log.string
269 (
270 fnd_log.level_statement,
271 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
272 'Node validation successful'
273 );
274 END IF;
275
276 SELECT ahl_mc_relationships_s.nextval INTO p_x_node_rec.relationship_id FROM DUAL;
277 p_x_node_rec.object_version_number := 1;
278 p_x_node_rec.security_group_id := null;
279
280 -- 4. Insert the node record into AHL_MC_RELATIONSHIPS table
281 INSERT INTO AHL_MC_RELATIONSHIPS
282 (
283 RELATIONSHIP_ID,
284 POSITION_REF_CODE,
285 PARENT_RELATIONSHIP_ID,
286 ITEM_GROUP_ID,
287 UOM_CODE,
288 QUANTITY,
289 DISPLAY_ORDER,
290 POSITION_NECESSITY_CODE,
291 POSITION_KEY,
292 MC_HEADER_ID,
293 ACTIVE_START_DATE,
294 ACTIVE_END_DATE,
295 LAST_UPDATE_DATE,
296 LAST_UPDATED_BY,
297 CREATION_DATE,
298 CREATED_BY,
299 LAST_UPDATE_LOGIN,
300 OBJECT_VERSION_NUMBER,
301 SECURITY_GROUP_ID,
302 ATTRIBUTE_CATEGORY,
303 ATTRIBUTE1,
304 ATTRIBUTE2,
305 ATTRIBUTE3,
306 ATTRIBUTE4,
307 ATTRIBUTE5,
308 ATTRIBUTE6,
309 ATTRIBUTE7,
310 ATTRIBUTE8,
311 ATTRIBUTE9,
312 ATTRIBUTE10,
313 ATTRIBUTE11,
314 ATTRIBUTE12,
315 ATTRIBUTE13,
316 ATTRIBUTE14,
317 ATTRIBUTE15,
318 --R12
319 --priyan MEL-CDL
320 ATA_CODE
321 )
322 VALUES
323 (
324 p_x_node_rec.relationship_id,
325 p_x_node_rec.position_ref_code,
326 p_x_node_rec.parent_relationship_id,
327 p_x_node_rec.item_group_id,
328 p_x_node_rec.uom_code,
329 p_x_node_rec.quantity,
330 p_x_node_rec.display_order,
331 p_x_node_rec.position_necessity_code,
332 p_x_node_rec.position_key,
333 p_x_node_rec.mc_header_id,
334 TRUNC(p_x_node_rec.active_start_date),
335 TRUNC(p_x_node_rec.active_end_date),
336 G_SYSDATE,
337 G_USER_ID,
338 G_SYSDATE,
339 G_USER_ID,
340 G_LOGIN_ID,
341 p_x_node_rec.object_version_number,
342 p_x_node_rec.security_group_id,
343 p_x_node_rec.attribute_category,
344 p_x_node_rec.attribute1,
345 p_x_node_rec.attribute2,
346 p_x_node_rec.attribute3,
347 p_x_node_rec.attribute4,
348 p_x_node_rec.attribute5,
349 p_x_node_rec.attribute6,
350 p_x_node_rec.attribute7,
351 p_x_node_rec.attribute8,
352 p_x_node_rec.attribute9,
353 p_x_node_rec.attribute10,
354 p_x_node_rec.attribute11,
355 p_x_node_rec.attribute12,
356 p_x_node_rec.attribute13,
357 p_x_node_rec.attribute14,
358 p_x_node_rec.attribute15,
359 --R12
360 --priyan MEL-CDL
361 p_x_node_rec.ata_code
362 );
363
364 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
365 THEN
366 fnd_log.string
367 (
368 fnd_log.level_statement,
369 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
370 'Node ['||p_x_node_rec.relationship_id||'] created'
371 );
372 END IF;
373
374 -- 5. Iterate through the counter rules table
375 IF (p_x_counter_rules_tbl.COUNT > 0)
376 THEN
377 FOR i IN p_x_counter_rules_tbl.FIRST..p_x_counter_rules_tbl.LAST
378 LOOP
379 -- 5a.i. Populate the node relationship_id for the counter_rules_tbl records
380 p_x_counter_rules_tbl(i).relationship_id := p_x_node_rec.relationship_id;
381
382 -- 5a.i. Create the position ratio records
383 Create_Counter_Rule
384 (
385 p_x_counter_rules_tbl(i)
386 );
387
388 -- Check Error Message stack.
389 x_msg_count := FND_MSG_PUB.count_msg;
390 IF x_msg_count > 0 THEN
391 RAISE FND_API.G_EXC_ERROR;
392 END IF;
393
394 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
395 THEN
396 fnd_log.string
397 (
398 fnd_log.level_statement,
399 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
400 'Counter rule ['||p_x_counter_rules_tbl(i).ctr_update_rule_id||'] created'
401 );
402 END IF;
403 END LOOP;
404 END IF;
405
406 -- 6. Iterate through the subconfigurations table [This table will in all probability be null according to the
407 -- current design of Create_MC_Revision, Copy_Master_Config and Copy_MC_Nodes, but the following code is needed in
408 -- place to account for the case when the table is not null]
409 IF (p_x_subconfig_tbl.COUNT > 0)
410 THEN
411
412
413 FOR i IN p_x_subconfig_tbl.FIRST..p_x_subconfig_tbl.LAST
414 LOOP
415 -- 5a.i. Populate the node relationship_id for the subconfig_tbl records
416 p_x_subconfig_tbl(i).relationship_id := p_x_node_rec.relationship_id;
417
418 -- 5a.i. Create the subconfiguration records
419 Attach_Subconfig
420 (
421 p_x_subconfig_tbl(i)
422 );
423
424 -- Check Error Message stack.
425 x_msg_count := FND_MSG_PUB.count_msg;
426 IF x_msg_count > 0 THEN
427 RAISE FND_API.G_EXC_ERROR;
428 END IF;
429
430 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
431 THEN
432 fnd_log.string
433 (
434 fnd_log.level_statement,
435 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
436 'Counter rule ['||p_x_subconfig_tbl(i).mc_config_relation_id||'] created'
437 );
438 END IF;
439 END LOOP;
440
441 validate_priority(p_x_subconfig_tbl);
442
443 END IF;
444
445 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
446 THEN
447 fnd_log.string
448 (
449 fnd_log.level_procedure,
450 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||'.end',
451 'At the end of PLSQL procedure'
452 );
453 END IF;
454 -- API body ends here
455
456 -- Check Error Message stack.
457 x_msg_count := FND_MSG_PUB.count_msg;
458
459 IF x_msg_count > 0 THEN
460 RAISE FND_API.G_EXC_ERROR;
461 END IF;
462
463 -- Standard check for p_commit
464 IF FND_API.TO_BOOLEAN (p_commit)
465 THEN
466 COMMIT WORK;
467 END IF;
468
469 -- Standard call to get message count and if count is 1, get message info
470 FND_MSG_PUB.count_and_get
471 (
472 p_count => x_msg_count,
473 p_data => x_msg_data,
474 p_encoded => FND_API.G_FALSE
475 );
476
477 EXCEPTION
478 WHEN FND_API.G_EXC_ERROR THEN
479 x_return_status := FND_API.G_RET_STS_ERROR;
480 Rollback to Create_Node_SP;
481 FND_MSG_PUB.count_and_get
482 (
483 p_count => x_msg_count,
484 p_data => x_msg_data,
485 p_encoded => FND_API.G_FALSE
486 );
487
488 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
489 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
490 Rollback to Create_Node_SP;
491 FND_MSG_PUB.count_and_get
492 (
493 p_count => x_msg_count,
494 p_data => x_msg_data,
495 p_encoded => FND_API.G_FALSE
496 );
497
498 WHEN OTHERS THEN
499 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
500 Rollback to Create_Node_SP;
501 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
502 THEN
503 FND_MSG_PUB.add_exc_msg
504 (
505 p_pkg_name => G_PKG_NAME,
506 p_procedure_name => 'Create_Node',
507 p_error_text => SUBSTR(SQLERRM,1,240)
508 );
509 END IF;
510 FND_MSG_PUB.count_and_get
511 (
512 p_count => x_msg_count,
513 p_data => x_msg_data,
514 p_encoded => FND_API.G_FALSE
515 );
516
517 END Create_Node;
518
519 PROCEDURE Modify_Node
520 (
521 p_api_version IN NUMBER,
522 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
523 p_commit IN VARCHAR2 := FND_API.G_FALSE,
524 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
525 x_return_status OUT NOCOPY VARCHAR2,
526 x_msg_count OUT NOCOPY NUMBER,
527 x_msg_data OUT NOCOPY VARCHAR2,
528 p_x_node_rec IN OUT NOCOPY Node_Rec_Type,
529 p_x_counter_rules_tbl IN OUT NOCOPY Counter_Rules_Tbl_Type,
530 p_x_subconfig_tbl IN OUT NOCOPY SubConfig_Tbl_Type
531 )
532 IS
533
534 -- Define cursor check_poskey_update to verify whether the position key for a MC node is updated
535 CURSOR check_poskey_update
536 IS
537 SELECT 'x'
538 FROM ahl_mc_relationships
539 WHERE relationship_id = p_x_node_rec.relationship_id AND
540 position_key <> p_x_node_rec.position_key;
541
542 -- Define cursor get_node_details to retrieve details of the MC node
543 CURSOR get_node_details
544 IS
545 SELECT active_end_date
546 FROM ahl_mc_relationships
547 WHERE relationship_id = p_x_node_rec.relationship_id;
548
549 --R12
550 --priyan MEL-CDL
551 -- Defind to retrieve the ata codes of all the Nodes that has attached the passed MC top node as subconfig
552 CURSOR get_ata_for_top_node
553 (
554 p_rel_id in number
555 )
556 IS
557 SELECT rel.ata_code, rel.position_ref_meaning , mch.name
558 FROM ahl_mc_relationships_v rel, ahl_mc_headers_b mch
559 WHERE rel.relationship_id IN
560 (
561 SELECT relationship_id
562 FROM ahl_mc_config_relations
563 WHERE mc_header_id IN
564 (
565 SELECT mc_header_id
566 FROM ahl_mc_relationships
567 WHERE relationship_id = p_rel_id
568 )
569 )
570 AND rel.mc_header_id = mch.mc_header_id;
571
572 -- Defined to retrieve the ata codes of subconfigs' root nodes
573 CURSOR get_ata_for_leaf_node
574 (
575 p_rel_id in number
576 )
577 IS
578 SELECT rel.ata_code , mch.name, rel.position_ref_meaning
579 FROM ahl_mc_relationships_v rel, ahl_mc_headers_b mch
580 WHERE rel.mc_header_id IN
581 (
582 SELECT mc_header_id
583 FROM ahl_mc_config_relations
584 WHERE relationship_id = p_rel_id
585 )
586 AND parent_relationship_id IS NULL
587 AND mch.mc_header_id = rel.mc_header_id;
588
589 -- Define check_root_node to check whether the node to topnode of a MC
590 CURSOR check_root_node
591 IS
592 SELECT 'x'
593 FROM ahl_mc_relationships
594 WHERE parent_relationship_id is null AND
595 relationship_id = p_x_node_rec.relationship_id;
596
597 -- Define check_leaf_node to check whether the node a leaf node
598 CURSOR check_leaf_node
599 IS
600 SELECT 'x'
601 FROM ahl_mc_relationships
602 WHERE parent_relationship_id = p_x_node_rec.relationship_id AND
603 G_TRUNC_DATE < trunc(nvl(active_end_date, G_SYSDATE + 1));
604
605 --End priyan Changes
606
607 -- Define local variables
608 l_api_name CONSTANT VARCHAR2(30) := 'Modify_Node';
609 l_api_version CONSTANT NUMBER := 1.0;
610 l_return_status VARCHAR2(1);
611 l_msg_count NUMBER;
612 l_msg_data VARCHAR2(2000);
613
614 l_header_status VARCHAR2(30);
615 l_end_date DATE;
616
617 --R12
618 --priyan MEL-CDL
619 l_get_ata_top_node_rec get_ata_for_top_node%rowtype;
620 l_get_ata_leaf_node_rec get_ata_for_leaf_node%rowtype;
621
622 BEGIN
623
624 -- Standard start of API savepoint
625 SAVEPOINT Modify_Node_SP;
626
627 -- Standard call to check for call compatibility
628 IF NOT FND_API.compatible_api_call(l_api_version, p_api_version, l_api_name, G_PKG_NAME)
629 THEN
630 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
631 END IF;
632
633 -- Initialize message list if p_init_msg_list is set to TRUE
634 IF FND_API.TO_BOOLEAN(p_init_msg_list)
635 THEN
636 FND_MSG_PUB.Initialize;
637 END IF;
638
639 -- Initialize API return status to success
640 x_return_status := FND_API.G_RET_STS_SUCCESS;
641
642 -- API body starts here
643 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
644 THEN
645 fnd_log.string
646 (
647 fnd_log.level_procedure,
648 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||'.begin',
649 ' At the start of PLSQL procedure'
650 );
651 END IF;
652
653 IF (p_x_node_rec.operation_flag = G_DML_UPDATE)
654 THEN
655
656 -- [node is also being modified]
657 -- 1a.i. Validate config_status_code of the MC is 'DRAFT' or 'APPROVAL_REJECTED'
658 l_header_status := Get_MC_Status(null, p_x_node_rec.mc_header_id);
659 IF NOT (l_header_status IN ('DRAFT', 'APPROVAL_REJECTED'))
660 THEN
661 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_NODE_STS_INV');
662 FND_MSG_PUB.ADD;
663 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
664 THEN
665 fnd_log.message
666 (
667 fnd_log.level_exception,
668 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
669 false
670 );
671 END IF;
672 RAISE FND_API.G_EXC_ERROR;
673
674 ELSIF (l_header_status = 'APPROVAL_REJECTED')
675 THEN
676 -- 1a.ii. Set status of MC to DRAFT if APPROVAL_REJECTED
677 Set_Header_Status(p_x_node_rec.relationship_id);
678 END IF;
679
680 -- 1b. For the MC node with relationship_id = p_x_node_rec.parent_relationship_id [parent node]
681 -- 1c.i. Validate a MC node with relationship_id = p_x_node_rec.relationship_id exists
682 -- 1c.iii. Validate p_x_node_rec.position_ref_code exists
683 -- 1c.iv. Validate p_x_node_rec.position_necessity_code exists
684 -- 1c.v. Validate p_x_node_rec.quantity
685 -- 1c.vi. Validate p_x_node_rec.uom_code
686 -- 1c.vii. Validate p_x_node_rec.item_group_id exists
687 -- 1c.viii. Validate p_x_node_rec.display_order > 0 and is not equal to display_order of all nodes at the same level
688 -- Validate dates
689 -- 1c.xvii.2 Validate the item_group does not have any item association with quantity > 1
690 Validate_Node(p_x_node_rec);
691
692 IF (p_x_node_rec.position_key IS NULL)
693 THEN
694 SELECT ahl_mc_rel_pos_key_s.nextval INTO p_x_node_rec.position_key FROM DUAL;
695 ELSE
696 -- 3j.i.Validate p_x_node_rec.position_key is unique within this MC
697 OPEN check_poskey_update;
698 FETCH check_poskey_update INTO l_dummy_varchar;
699 IF (check_poskey_update%FOUND)
700 THEN
701 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_POSKEY_NOUPD');
702 FND_MESSAGE.Set_Token('POSREF', p_x_node_rec.position_ref_meaning);
703 FND_MSG_PUB.ADD;
704
705 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
706 THEN
707 fnd_log.message
708 (
709 fnd_log.level_exception,
710 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
711 false
712 );
713 END IF;
714 END IF;
715 CLOSE check_poskey_update;
716 END IF;
717
718 -- ER #2631303 is not valid since there can be no units created from DRAFT MCs
719
720 -- Check Error Message stack.
721 x_msg_count := FND_MSG_PUB.count_msg;
722 IF x_msg_count > 0 THEN
723 RAISE FND_API.G_EXC_ERROR;
724 END IF;
725
726 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
727 THEN
728 fnd_log.string
729 (
730 fnd_log.level_statement,
731 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
732 'Node validation successful'
733 );
734 END IF;
735
736 p_x_node_rec.object_version_number := p_x_node_rec.object_version_number + 1;
737
738 -- 1d. Update the node record in AHL_MC_RELATIONSHIPS table
739 UPDATE AHL_MC_RELATIONSHIPS
740 SET POSITION_REF_CODE = p_x_node_rec.position_ref_code,
741 ITEM_GROUP_ID = p_x_node_rec.item_group_id,
742 UOM_CODE = p_x_node_rec.uom_code,
743 QUANTITY = p_x_node_rec.quantity,
744 DISPLAY_ORDER = p_x_node_rec.display_order,
745 POSITION_NECESSITY_CODE = p_x_node_rec.position_necessity_code,
746 POSITION_KEY = p_x_node_rec.position_key,
747 ACTIVE_START_DATE = p_x_node_rec.active_start_date,
748 ACTIVE_END_DATE = p_x_node_rec.active_end_date,
749 LAST_UPDATE_DATE = G_SYSDATE,
750 LAST_UPDATED_BY = G_USER_ID,
751 LAST_UPDATE_LOGIN = G_LOGIN_ID,
752 OBJECT_VERSION_NUMBER = p_x_node_rec.object_version_number,
753 SECURITY_GROUP_ID = p_x_node_rec.security_group_id,
754 ATTRIBUTE_CATEGORY = p_x_node_rec.attribute_category,
755 ATTRIBUTE1 = p_x_node_rec.attribute1,
756 ATTRIBUTE2 = p_x_node_rec.attribute2,
757 ATTRIBUTE3 = p_x_node_rec.attribute3,
758 ATTRIBUTE4 = p_x_node_rec.attribute4,
759 ATTRIBUTE5 = p_x_node_rec.attribute5,
760 ATTRIBUTE6 = p_x_node_rec.attribute6,
761 ATTRIBUTE7 = p_x_node_rec.attribute7,
762 ATTRIBUTE8 = p_x_node_rec.attribute8,
763 ATTRIBUTE9 = p_x_node_rec.attribute9,
764 ATTRIBUTE10 = p_x_node_rec.attribute10,
765 ATTRIBUTE11 = p_x_node_rec.attribute11,
766 ATTRIBUTE12 = p_x_node_rec.attribute12,
767 ATTRIBUTE13 = p_x_node_rec.attribute13,
768 ATTRIBUTE14 = p_x_node_rec.attribute14,
769 ATTRIBUTE15 = p_x_node_rec.attribute15,
770 --R12
771 --priyan MEL-CDL
772 ATA_CODE = p_x_node_rec.ata_code
773 WHERE RELATIONSHIP_ID = p_x_node_rec.relationship_id;
774
775
776 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
777 THEN
778 fnd_log.string
779 (
780 fnd_log.level_statement,
781 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
782 'Node ['||p_x_node_rec.relationship_id||'] updated'
783 );
784 END IF;
785
786 ELSE
787 -- [implies the node is not being updated, instead either subconfig associations or position ratios
788 -- are being updated; User may only update the subconfig table in the UI and leave the Node details
789 -- untouched, in this case it is better to call Modify_Node with p_x_node_rec.operation_flag = null
790 -- and p_x_subconfig_tbl <> null]
791
792 -- 2a. Validate node with relationship_id = p_x_node_rec.relationship_id, object_version_number = p_x_node_rec.object_version_number
793 Validate_Node_Exists(p_x_node_rec.relationship_id, null);
794
795 l_header_status := Get_MC_Status(p_x_node_rec.relationship_id, null);
796 IF NOT (l_header_status IN ('DRAFT', 'APPROVAL_REJECTED'))
797 THEN
798 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_NODE_STS_INV');
799 FND_MSG_PUB.ADD;
800 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
801 THEN
802 fnd_log.message
803 (
804 fnd_log.level_exception,
805 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
806 false
807 );
808 END IF;
809 RAISE FND_API.G_EXC_ERROR;
810 ELSIF (l_header_status = 'APPROVAL_REJECTED')
811 THEN
812 -- 1a.ii. Set status of MC to DRAFT if APPROVAL_REJECTED
813 Set_Header_Status(p_x_node_rec.relationship_id);
814 END IF;
815
816 OPEN get_node_details;
817 FETCH get_node_details INTO l_end_date;
818 CLOSE get_node_details;
819
820 -- Validate active_end_date >= sysdate exists
821 IF (trunc(nvl(l_end_date, G_SYSDATE + 1)) <= G_TRUNC_DATE)
822 THEN
823 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_END_DATE_INV');
824 FND_MSG_PUB.ADD;
825 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
826 THEN
827 fnd_log.message
828 (
829 fnd_log.level_exception,
830 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
831 false
832 );
833 END IF;
834 END IF;
835
836 -- Check Error Message stack.
837 x_msg_count := FND_MSG_PUB.count_msg;
838 IF x_msg_count > 0 THEN
839 RAISE FND_API.G_EXC_ERROR;
840 END IF;
841
842 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
843 THEN
844 fnd_log.string
845 (
846 fnd_log.level_statement,
847 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
848 'Node validation successful'
849 );
850 END IF;
851 END IF;
852
853 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
854 THEN
855 fnd_log.string
856 (
857 fnd_log.level_statement,
858 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
859 'Processing counter rules and subconfig table'
860 );
861 END IF;
862
863 -- 3. Iterate through the counter rules table
864 IF (p_x_counter_rules_tbl.COUNT > 0)
865 THEN
866 FOR i IN p_x_counter_rules_tbl.FIRST..p_x_counter_rules_tbl.LAST
867 LOOP
868 -- 5a.i. Populate the node relationship_id for the counter_rules_tbl records
869 p_x_counter_rules_tbl(i).relationship_id := p_x_node_rec.relationship_id;
870
871 IF (p_x_counter_rules_tbl(i).operation_flag = G_DML_CREATE)
872 THEN
873 Create_Counter_Rule
874 (
875 p_x_counter_rules_tbl(i)
876 );
877 ELSIF (p_x_counter_rules_tbl(i).operation_flag = G_DML_DELETE)
878 THEN
879 Delete_Counter_Rule
880 (
881 p_x_counter_rules_tbl(i).ctr_update_rule_id,
882 p_x_counter_rules_tbl(i).object_version_number
883 );
884 ELSE
885 Modify_Counter_Rule
886 (
887 p_x_counter_rules_tbl(i)
888 );
889 END IF;
890
891 -- Check Error Message stack.
892 x_msg_count := FND_MSG_PUB.count_msg;
893 IF x_msg_count > 0 THEN
894 RAISE FND_API.G_EXC_ERROR;
895 END IF;
896 END LOOP;
897 END IF;
898
899 -- 4. Iterate through the subconfigurations table
900 IF (p_x_subconfig_tbl.COUNT > 0)
901 THEN
902 FOR i IN p_x_subconfig_tbl.FIRST..p_x_subconfig_tbl.LAST
903 LOOP
904 -- 5a.i. Populate the node relationship_id for the subconfig_tbl records
905 p_x_subconfig_tbl(i).relationship_id := p_x_node_rec.relationship_id;
906
907 IF (p_x_subconfig_tbl(i).operation_flag = G_DML_CREATE)
908 THEN
909 Attach_Subconfig
910 (
911 p_x_subconfig_tbl(i)
912 );
913 ELSIF (p_x_subconfig_tbl(i).operation_flag = G_DML_DELETE)
914 THEN
915 Detach_Subconfig
916 (
917 p_x_subconfig_tbl(i).mc_config_relation_id,
918 p_x_subconfig_tbl(i).object_version_number
919 );
920 ELSE
921 Modify_Subconfig
922 (
923 p_x_subconfig_tbl(i)
924 );
925 END IF;
926
927 -- Check Error Message stack.
928 x_msg_count := FND_MSG_PUB.count_msg;
929 IF x_msg_count > 0 THEN
930 RAISE FND_API.G_EXC_ERROR;
931 END IF;
932 END LOOP;
933
934 validate_priority(p_x_subconfig_tbl);
935 END IF;
936
937 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
938 THEN
939 fnd_log.string
940 (
941 fnd_log.level_procedure,
942 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||'.end',
943 'At the end of PLSQL procedure'
944 );
945 END IF;
946 -- API body ends here
947
948 -- Check Error Message stack.
949 x_msg_count := FND_MSG_PUB.count_msg;
950 IF x_msg_count > 0 THEN
951 RAISE FND_API.G_EXC_ERROR;
952 END IF;
953
954 --R12
955 --Priyan MEL-CDL
956 -- Call the cursor check_root_node to see if the node being modified is a top node or not .
957 -- If the cursor check_root_node returns true then the cursor get_ata_for_top_node ,is called
958 -- to check if any of the nodes that has the top node's MC attached as a subconfig. If then,the
959 -- corresponding Nodes ATA Code is retreived and is checked with the ATA code of the top node.
960 -- If they are different then a warning message is shown to the user.
961
962 IF (p_x_node_rec.operation_flag = G_DML_UPDATE)
963 THEN
964 OPEN check_root_node;
965 FETCH check_root_node INTO l_dummy_varchar;
966
967 IF (check_root_node%FOUND)
968 THEN
969 OPEN get_ata_for_top_node(p_x_node_rec.relationship_id);
970 LOOP
971 FETCH get_ata_for_top_node INTO l_get_ata_top_node_rec ;
972 EXIT WHEN get_ata_for_top_node%NOTFOUND;
973
974 IF (l_get_ata_top_node_rec.ata_code <> p_x_node_rec.ata_code)
975 THEN
976
977 -- Raise warning msg
978 FND_MESSAGE.set_name('AHL', 'AHL_MC_POS_SUBMC_ATA_NOMATCH');
979 FND_MESSAGE.Set_Token('POS', l_get_ata_top_node_rec.POSITION_REF_MEANING);
980 FND_MESSAGE.Set_Token('MC', l_get_ata_top_node_rec.NAME);
981 FND_MESSAGE.Set_Token('SUBCONFIG', p_x_node_rec.POSITION_REF_MEANING);
982 FND_MSG_PUB.add;
983 END IF;
984 END LOOP;
985
986 CLOSE get_ata_for_top_node;
987 END IF;
988
989 CLOSE check_root_node;
990
991 -- Check if the node is a leaf node by calling the cursor check_leaf_node,
992 -- If then , get all the ata codes of the subconfigs, attached.
993 -- Get the ata code of the top node of the subconfigs and check if the ata codes are the same
994 -- If not then , raise a warning message.
995
996
997 OPEN check_leaf_node;
998 FETCH check_leaf_node INTO l_dummy_varchar;
999
1000 IF (check_leaf_node%NOTFOUND)
1001 THEN
1002
1003 OPEN get_ata_for_leaf_node(p_x_node_rec.relationship_id);
1004 LOOP
1005 FETCH get_ata_for_leaf_node INTO l_get_ata_leaf_node_rec ;
1006 EXIT WHEN get_ata_for_leaf_node%NOTFOUND;
1007
1008 IF (l_get_ata_leaf_node_rec.ata_code <> p_x_node_rec.ata_code)
1009 THEN
1010 -- Raise warning msg
1011 FND_MESSAGE.set_name('AHL', 'AHL_MC_POS_SUBMC_ATA_NOMATCH');
1012 FND_MESSAGE.Set_Token('POS', p_x_node_rec.POSITION_REF_MEANING);
1013 FND_MESSAGE.Set_Token('MC',l_get_ata_leaf_node_rec.POSITION_REF_MEANING );
1014 FND_MESSAGE.Set_Token('SUBCONFIG', l_get_ata_leaf_node_rec.name);
1015 FND_MSG_PUB.add;
1016 END IF;
1017 END LOOP;
1018 CLOSE get_ata_for_leaf_node;
1019 END IF;
1020 CLOSE check_leaf_node;
1021 END IF; -- condition for DML Update
1022
1023 -- Standard check for p_commit
1024 IF FND_API.TO_BOOLEAN (p_commit)
1025 THEN
1026 COMMIT WORK;
1027 END IF;
1028
1029 -- Check Error Message stack.
1030 x_msg_count := FND_MSG_PUB.count_msg;
1031
1032 IF ( x_msg_count > 0 and x_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
1033 RAISE FND_API.G_EXC_ERROR;
1034 END IF;
1035
1036 -- Standard call to get message count and if count is 1, get message info
1037 FND_MSG_PUB.count_and_get
1038 (
1039 p_count => x_msg_count,
1040 p_data => x_msg_data,
1041 p_encoded => FND_API.G_FALSE
1042 );
1043
1044 EXCEPTION
1045 WHEN FND_API.G_EXC_ERROR THEN
1046 x_return_status := FND_API.G_RET_STS_ERROR;
1047 Rollback to Modify_Node_SP;
1048 FND_MSG_PUB.count_and_get
1049 (
1050 p_count => x_msg_count,
1051 p_data => x_msg_data,
1052 p_encoded => FND_API.G_FALSE
1053 );
1054
1055 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1056 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1057 Rollback to Modify_Node_SP;
1058 FND_MSG_PUB.count_and_get
1059 (
1060 p_count => x_msg_count,
1061 p_data => x_msg_data,
1062 p_encoded => FND_API.G_FALSE
1063 );
1064
1065 WHEN OTHERS THEN
1066 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1067 Rollback to Modify_Node_SP;
1068 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1069 THEN
1070 FND_MSG_PUB.add_exc_msg
1071 (
1072 p_pkg_name => G_PKG_NAME,
1073 p_procedure_name => 'Modify_Node',
1074 p_error_text => SUBSTR(SQLERRM,1,240)
1075 );
1076 END IF;
1077 FND_MSG_PUB.count_and_get
1078 (
1079 p_count => x_msg_count,
1080 p_data => x_msg_data,
1081 p_encoded => FND_API.G_FALSE
1082 );
1083
1084 END Modify_Node;
1085
1086 PROCEDURE Delete_Node
1087 (
1088 p_api_version IN NUMBER,
1089 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1090 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1091 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1092 x_return_status OUT NOCOPY VARCHAR2,
1093 x_msg_count OUT NOCOPY NUMBER,
1094 x_msg_data OUT NOCOPY VARCHAR2,
1095 p_node_id IN NUMBER,
1096 p_object_ver_num IN NUMBER
1097 )
1098 IS
1099
1100 -- 1. Define cursor get_mc_tree_csr to read all nodes that are children to a particular MC node
1101 CURSOR get_mc_tree_csr
1102 (
1103 p_rel_id in number
1104 )
1105 IS
1106 SELECT *
1107 FROM ahl_mc_relationships
1108 CONNECT BY parent_relationship_id = PRIOR relationship_id
1109 START WITH relationship_id = p_rel_id
1110 ORDER BY relationship_id DESC;
1111
1112 -- 4. Define local variables
1113 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Node';
1114 l_api_version CONSTANT NUMBER := 1.0;
1115 l_return_status VARCHAR2(1);
1116 l_msg_count NUMBER;
1117 l_msg_data VARCHAR2(2000);
1118
1119 l_node_csr_rec get_mc_tree_csr%rowtype;
1120 l_header_status VARCHAR2(30);
1121
1122 BEGIN
1123
1124 -- Standard start of API savepoint
1125 SAVEPOINT Delete_Node_SP;
1126
1127 -- Standard call to check for call compatibility
1128 IF NOT FND_API.compatible_api_call(l_api_version, p_api_version, l_api_name, G_PKG_NAME)
1129 THEN
1130 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1131 END IF;
1132
1133 -- Initialize message list if p_init_msg_list is set to TRUE
1134 IF FND_API.TO_BOOLEAN(p_init_msg_list)
1135 THEN
1136 FND_MSG_PUB.Initialize;
1137 END IF;
1138
1139 -- Initialize API return status to success
1140 x_return_status := FND_API.G_RET_STS_SUCCESS;
1141
1142 -- API body starts here
1143 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
1144 THEN
1145 fnd_log.string
1146 (
1147 fnd_log.level_procedure,
1148 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||'.begin',
1149 'At the start of PLSQL procedure'
1150 );
1151 END IF;
1152
1153 -- 5. Validate a MC node with relationship_id = p_node_id exists
1154 Validate_Node_Exists (p_node_id, nvl(p_object_ver_num, 0));
1155
1156 l_header_status := Get_MC_Status(p_node_id, null);
1157 -- 6i. Validate that the config_status_code of the MC is 'DRAFT' or 'APPROVAL_REJECTED'
1158 IF NOT (l_header_status IN ('DRAFT', 'APPROVAL_REJECTED'))
1159 THEN
1160 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_NODE_STS_INV');
1161 FND_MSG_PUB.ADD;
1162 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
1163 THEN
1164 fnd_log.message
1165 (
1166 fnd_log.level_exception,
1167 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
1168 false
1169 );
1170 END IF;
1171 RAISE FND_API.G_EXC_ERROR;
1172 ELSIF (l_header_status = 'APPROVAL_REJECTED')
1173 THEN
1174 -- 6ii. Set status of MC to DRAFT if APPROVAL_REJECTED
1175 Set_Header_Status(p_node_id);
1176 END IF;
1177
1178 OPEN get_mc_tree_csr(p_node_id);
1179 LOOP
1180 FETCH get_mc_tree_csr INTO l_node_csr_rec;
1181 EXIT WHEN get_mc_tree_csr%NOTFOUND;
1182
1183 -- ER #2631303 is not valid since there can be no units created from DRAFT MCs
1184
1185 -- 9d. Delete all subconfiguration associations with the current node
1186 DELETE FROM ahl_mc_config_relations
1187 WHERE relationship_id = l_node_csr_rec.relationship_id;
1188
1189 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1190 THEN
1191 fnd_log.string
1192 (
1193 fnd_log.level_statement,
1194 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
1195 'Detached subconfigs for node ['||l_node_csr_rec.relationship_id||']'
1196 );
1197 END IF;
1198
1199 -- 9g. Delete all counter rule associations with the current node
1200 DELETE FROM ahl_ctr_update_rules
1201 WHERE relationship_id = l_node_csr_rec.relationship_id;
1202
1203 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1204 THEN
1205 fnd_log.string
1206 (
1207 fnd_log.level_statement,
1208 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
1209 'Detached counter rules for node ['||l_node_csr_rec.relationship_id||']'
1210 );
1211 END IF;
1212
1213 -- 9h. Delete all document associations to this particular node
1214 AHL_DI_ASSO_DOC_GEN_PVT.DELETE_ALL_ASSOCIATIONS
1215 (
1216 p_api_version => 1.0,
1217 p_init_msg_list => FND_API.G_FALSE,
1218 p_commit => FND_API.G_FALSE,
1219 p_validate_only => FND_API.G_FALSE,
1220 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1221 p_aso_object_type_code => 'MC',
1222 p_aso_object_id => l_node_csr_rec.relationship_id,
1223 x_return_status => l_return_status,
1224 x_msg_count => l_msg_count,
1225 x_msg_data => l_msg_data
1226 );
1227
1228 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1229 THEN
1230 fnd_log.string
1231 (
1232 fnd_log.level_statement,
1233 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
1234 'Deleted document associations for node ['||l_node_csr_rec.relationship_id||']'
1235 );
1236 END IF;
1237
1238 -- 9i. Delete the MC node
1239 DELETE FROM ahl_mc_relationships
1240 WHERE relationship_id = l_node_csr_rec.relationship_id;
1241
1242 END LOOP;
1243
1244 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
1245 THEN
1246 fnd_log.string
1247 (
1248 fnd_log.level_procedure,
1249 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||'.end',
1250 'At the end of PLSQL procedure'
1251 );
1252 END IF;
1253 -- API body ends here
1254
1255 -- Check Error Message stack.
1256 x_msg_count := FND_MSG_PUB.count_msg;
1257 IF x_msg_count > 0 THEN
1258 RAISE FND_API.G_EXC_ERROR;
1259 END IF;
1260
1261 -- Standard check for p_commit
1262 IF FND_API.TO_BOOLEAN (p_commit)
1263 THEN
1264 COMMIT WORK;
1265 END IF;
1266
1267 -- Standard call to get message count and if count is 1, get message info
1268 FND_MSG_PUB.count_and_get
1269 (
1270 p_count => x_msg_count,
1271 p_data => x_msg_data,
1272 p_encoded => FND_API.G_FALSE
1273 );
1274
1275 EXCEPTION
1276 WHEN FND_API.G_EXC_ERROR THEN
1277 x_return_status := FND_API.G_RET_STS_ERROR;
1278 Rollback to Delete_Node_SP;
1279 FND_MSG_PUB.count_and_get
1280 (
1281 p_count => x_msg_count,
1282 p_data => x_msg_data,
1283 p_encoded => FND_API.G_FALSE
1284 );
1285
1286 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1287 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1288 Rollback to Delete_Node_SP;
1289 FND_MSG_PUB.count_and_get
1290 (
1291 p_count => x_msg_count,
1292 p_data => x_msg_data,
1293 p_encoded => FND_API.G_FALSE
1294 );
1295 WHEN OTHERS THEN
1296 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1297 Rollback to Delete_Node_SP;
1298 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1299 THEN
1300 FND_MSG_PUB.add_exc_msg
1301 (
1302 p_pkg_name => G_PKG_NAME,
1303 p_procedure_name => 'Delete_Node',
1304 p_error_text => SUBSTR(SQLERRM,1,240)
1305 );
1306 END IF;
1307 FND_MSG_PUB.count_and_get
1308 (
1309 p_count => x_msg_count,
1310 p_data => x_msg_data,
1311 p_encoded => FND_API.G_FALSE
1312 );
1313
1314 END Delete_Node;
1315
1316 PROCEDURE Copy_Node
1317 (
1318 p_api_version IN NUMBER,
1319 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1320 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1321 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1322 x_return_status OUT NOCOPY VARCHAR2,
1323 x_msg_count OUT NOCOPY NUMBER,
1324 x_msg_data OUT NOCOPY VARCHAR2,
1325 p_parent_rel_id IN NUMBER,
1326 p_parent_obj_ver_num IN NUMBER,
1327 p_x_node_id IN OUT NOCOPY NUMBER,
1328 p_x_node_obj_ver_num IN OUT NOCOPY NUMBER
1329 )
1330 IS
1331
1332 -- Define local variables
1333 l_api_name CONSTANT VARCHAR2(30) := 'Copy_Node';
1334 l_api_version CONSTANT NUMBER := 1.0;
1335 l_return_status VARCHAR2(1);
1336 l_msg_count NUMBER;
1337 l_msg_data VARCHAR2(2000);
1338
1339 l_header_status VARCHAR2(30);
1340
1341 BEGIN
1342
1343 -- Standard start of API savepoint
1344 SAVEPOINT Copy_Node_SP;
1345
1346 -- Standard call to check for call compatibility
1347 IF NOT FND_API.compatible_api_call(l_api_version, p_api_version, l_api_name, G_PKG_NAME)
1348 THEN
1349 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1350 END IF;
1351
1352 -- Initialize message list if p_init_msg_list is set to TRUE
1353 IF FND_API.TO_BOOLEAN(p_init_msg_list)
1354 THEN
1355 FND_MSG_PUB.Initialize;
1356 END IF;
1357
1358 -- Initialize API return status to success
1359 x_return_status := FND_API.G_RET_STS_SUCCESS;
1360
1361 -- API body starts here
1362 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
1363 THEN
1364 fnd_log.string
1365 (
1366 fnd_log.level_procedure,
1367 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||'.begin',
1368 'At the start of PLSQL procedure'
1369 );
1370 END IF;
1371
1372 -- 1. Validate a MC node exists with RELATIONSHIP_ID = p_parent_rel_id
1373 -- 2. Validate p_parent_obj_ver_num for the MC node with RELATIONSHIP_ID = p_parent_rel_id
1374 Validate_Node_Exists(p_parent_rel_id, nvl(p_parent_obj_ver_num, 0));
1375
1376 l_header_status := Get_MC_Status(p_parent_rel_id, null);
1377 -- 3a. Validate that the config_status_code of the MC is 'DRAFT' or 'APPROVAL_REJECTED'
1378 IF NOT (l_header_status IN ('DRAFT', 'APPROVAL_REJECTED'))
1379 THEN
1380 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_NODE_STS_INV');
1381 FND_MSG_PUB.ADD;
1382 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
1383 THEN
1384 fnd_log.message
1385 (
1386 fnd_log.level_exception,
1387 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
1388 false
1389 );
1390 END IF;
1391 RAISE FND_API.G_EXC_ERROR;
1392 ELSIF (l_header_status = 'APPROVAL_REJECTED')
1393 THEN
1394 Set_Header_Status(p_parent_rel_id);
1395 END IF;
1396
1397 -- 4. Validate a MC node exists with RELATIONSHIP_ID = p_x_node_id
1398 -- 5. Validate p_x_node_rec.object_version_number for the MC node with RELATIONSHIP_ID = p_x_node_id
1399 Validate_Node_Exists(p_x_node_id, nvl(p_x_node_obj_ver_num, 0));
1400
1401 -- Check Error Message stack.
1402 x_msg_count := FND_MSG_PUB.count_msg;
1403 IF x_msg_count > 0 THEN
1404 RAISE FND_API.G_EXC_ERROR;
1405 END IF;
1406
1407 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1408 THEN
1409 fnd_log.string
1410 (
1411 fnd_log.level_statement,
1412 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
1413 'Node validation successful... Calling Copy_MC_Nodes'
1414 );
1415 END IF;
1416
1417 -- 6. Call AHL_MC_Node_PVT.Copy_MC_Nodes
1418 Copy_MC_Nodes
1419 (
1420 p_api_version => 1.0,
1421 p_init_msg_list => FND_API.G_FALSE,
1422 p_commit => FND_API.G_FALSE,
1423 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1424 x_return_status => l_return_status,
1425 x_msg_count => l_msg_count,
1426 x_msg_data => l_msg_data,
1427 p_source_rel_id => p_x_node_id,
1428 p_dest_rel_id => p_parent_rel_id,
1429 p_new_rev_flag => FALSE,
1430 p_node_copy => TRUE
1431 );
1432
1433 -- Read the newly created node details into the in/out parameters
1434 BEGIN
1435 SELECT new.relationship_id, new.object_version_number
1436 INTO p_x_node_id, p_x_node_obj_ver_num
1437 FROM ahl_mc_relationships new, ahl_mc_relationships old
1438 WHERE new.position_ref_code = old.position_ref_code AND
1439 old.relationship_id = p_x_node_id AND
1440 new.parent_relationship_id = p_parent_rel_id;
1441 EXCEPTION
1442 WHEN OTHERS THEN
1443 NULL;
1444 END;
1445
1446 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
1447 THEN
1448 fnd_log.string
1449 (
1450 fnd_log.level_procedure,
1451 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||'.end',
1452 'At the end of PLSQL procedure'
1453 );
1454 END IF;
1455 -- API body ends here
1456
1457 -- Check Error Message stack.
1458 x_msg_count := FND_MSG_PUB.count_msg;
1459 IF x_msg_count > 0 THEN
1460 RAISE FND_API.G_EXC_ERROR;
1461 END IF;
1462
1463 -- Standard check for p_commit
1464 IF FND_API.TO_BOOLEAN (p_commit)
1465 THEN
1466 COMMIT WORK;
1467 END IF;
1468
1469 -- Standard call to get message count and if count is 1, get message info
1470 FND_MSG_PUB.count_and_get
1471 (
1472 p_count => x_msg_count,
1473 p_data => x_msg_data,
1474 p_encoded => FND_API.G_FALSE
1475 );
1476
1477 EXCEPTION
1478 WHEN FND_API.G_EXC_ERROR THEN
1479 x_return_status := FND_API.G_RET_STS_ERROR;
1480 Rollback to Copy_Node_SP;
1481 FND_MSG_PUB.count_and_get
1482 (
1483 p_count => x_msg_count,
1484 p_data => x_msg_data,
1485 p_encoded => FND_API.G_FALSE
1486 );
1487
1488 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1489 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1490 Rollback to Copy_Node_SP;
1491 FND_MSG_PUB.count_and_get
1492 (
1493 p_count => x_msg_count,
1494 p_data => x_msg_data,
1495 p_encoded => FND_API.G_FALSE
1496 );
1497
1498 WHEN OTHERS THEN
1499 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1500 Rollback to Copy_Node_SP;
1501 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1502 THEN
1503 FND_MSG_PUB.add_exc_msg
1504 (
1505 p_pkg_name => G_PKG_NAME,
1506 p_procedure_name => 'Copy_Node',
1507 p_error_text => SUBSTR(SQLERRM,1,240)
1508 );
1509 END IF;
1510 FND_MSG_PUB.count_and_get
1511 (
1512 p_count => x_msg_count,
1513 p_data => x_msg_data,
1514 p_encoded => FND_API.G_FALSE
1515 );
1516
1517 END Copy_Node;
1518
1519 PROCEDURE Copy_MC_Nodes
1520 (
1521 p_api_version IN NUMBER,
1522 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1523 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1524 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1525 x_return_status OUT NOCOPY VARCHAR2,
1526 x_msg_count OUT NOCOPY NUMBER,
1527 x_msg_data OUT NOCOPY VARCHAR2,
1528 p_source_rel_id IN NUMBER,
1529 p_dest_rel_id IN NUMBER,
1530 p_new_rev_flag IN BOOLEAN := FALSE,
1531 p_node_copy IN BOOLEAN := FALSE
1532 )
1533 IS
1534
1535 -- 1. Define cursor get_mc_tree_csr to read all nodes that are children to the topnode of a particular MC
1536 -- changed by anraj remove the CONNECT BY PRIOR clause on joins
1537 CURSOR get_mc_tree_csr
1538 (
1539 p_topnode_id in number
1540 )
1541 IS
1542 SELECT *
1543 FROM ahl_mc_relationships
1544 WHERE relationship_id <> p_topnode_id
1545 -- Expired nodes also to be copied or else position path copy will fail
1546 -- AND G_TRUNC_DATE < trunc(nvl(active_end_date, G_SYSDATE + 1))
1547 CONNECT BY parent_relationship_id = PRIOR relationship_id
1548 START WITH relationship_id = p_topnode_id
1549 ORDER BY parent_relationship_id, display_order;
1550
1551 CURSOR get_mc_details_csr
1552 (
1553 p_relationship_id in number
1554 )
1555 IS
1556 select POSITION_REF_MEANING,POSITION_NECESSITY_MEANING,GROUP_NAME,ATA_MEANING -- R12 priyan MEL-CDL
1557 from ahl_mc_relationships_v
1558 where relationship_id = p_relationship_id;
1559
1560 -- 2. Define cursor get_ctr_rule_update_csr to read all counter update rules for a particular MC node
1561 CURSOR get_ctr_rule_update_csr
1562 (
1563 p_rel_id in number
1564 )
1565 IS
1566 SELECT *
1567 FROM ahl_ctr_update_rules
1568 WHERE relationship_id = p_rel_id;
1569
1570 TYPE Number_Tbl_Type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
1571
1572 -- Define get_mc_header_id to read the mc_header_id of the destination MC node
1573 CURSOR get_mc_header_id
1574 IS
1575 SELECT mc_header_id
1576 FROM ahl_mc_relationships
1577 WHERE relationship_id = p_dest_rel_id;
1578
1579 -- Define cursor get_max_dispord to read the maximum of display orders of the children of a MC node with relationship_id = p_rel_id
1580 CURSOR get_max_dispord
1581 (
1582 p_rel_id in number
1583 )
1584 IS
1585 SELECT max(display_order)
1586 FROM ahl_mc_relationships
1587 WHERE parent_relationship_id = p_rel_id;
1588
1589 -- Define cursor get_root_node to read details of the top node (in the case of copy_node)
1590 CURSOR get_root_node
1591 (
1592 p_topnode_id in number
1593 )
1594 IS
1595 SELECT *
1596 FROM ahl_mc_relationships_v
1597 WHERE relationship_id = p_topnode_id;
1598
1599 -- Define local variables
1600 l_api_name CONSTANT VARCHAR2(30) := 'Copy_MC_Nodes';
1601 l_api_version CONSTANT NUMBER := 1.0;
1602 l_return_status VARCHAR2(1);
1603 l_msg_count NUMBER;
1604 l_msg_data VARCHAR2(2000);
1605
1606 l_node_rec Node_Rec_Type;
1607 l_nodes_tbl Node_Tbl_Type;
1608 l_node_csr_rec get_mc_tree_csr%rowtype;
1609
1610 l_root_node_csr_rec ahl_mc_relationships_v%rowtype;
1611
1612
1613
1614 -- declared by anraj to remove the CONNECT BY PRIOR on joins
1615 l_mc_details_rec get_mc_details_csr%rowtype;
1616
1617 l_ctr_rule_rec Counter_Rule_Rec_Type;
1618 l_ctr_rules_tbl Counter_Rules_Tbl_Type;
1619 l_ctr_rule_csr_rec get_ctr_rule_update_csr%rowtype;
1620 l_node_idx NUMBER;
1621 l_ctr_rule_idx NUMBER;
1622 l_old_node_id_tbl Number_Tbl_Type;
1623 l_node_ctr_rules_tbl Counter_Rules_Tbl_Type;
1624 l_subconfig_tbl Subconfig_Tbl_Type;
1625 l_ctr_iterator NUMBER;
1626 l_ret_val BOOLEAN;
1627 l_mc_header_id NUMBER;
1628 l_max_dispord NUMBER;
1629
1630 BEGIN
1631
1632 -- Standard start of API savepoint
1633 SAVEPOINT Copy_MC_Nodes_SP;
1634
1635 -- Standard call to check for call compatibility
1636 IF NOT FND_API.compatible_api_call(l_api_version, p_api_version, l_api_name, G_PKG_NAME)
1637 THEN
1638 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1639 END IF;
1640
1641 -- Initialize message list if p_init_msg_list is set to TRUE
1642 IF FND_API.TO_BOOLEAN(p_init_msg_list)
1643 THEN
1644 FND_MSG_PUB.Initialize;
1645 END IF;
1646
1647 -- Initialize API return status to success
1648 x_return_status := FND_API.G_RET_STS_SUCCESS;
1649
1650 -- API body starts here
1651 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
1652 THEN
1653 fnd_log.string
1654 (
1655 fnd_log.level_procedure,
1656 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||'.begin',
1657 'At the start of PLSQL procedure'
1658 );
1659 END IF;
1660
1661 OPEN get_mc_header_id;
1662 FETCH get_mc_header_id INTO l_mc_header_id;
1663 CLOSE get_mc_header_id;
1664
1665 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1666 THEN
1667 fnd_log.string
1668 (
1669 fnd_log.level_statement,
1670 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
1671 'Starting the copy of the node tree for node ['||p_source_rel_id||'] to node ['||p_dest_rel_id||']'
1672 );
1673 END IF;
1674
1675 l_node_idx := 0;
1676 l_ctr_rule_idx := 0;
1677
1678 IF (p_node_copy = TRUE)
1679 THEN
1680 -- For Copy_Node call for copying an MC into a node
1681 -- ##TAMAL## This can be avoided if the topnode is created in the procedure Copy_Node itself and pass
1682 -- p_node_copy = FALSE to this method, similar to Copy_Master_Config and Create_MC_Revision
1683 OPEN get_root_node (p_source_rel_id);
1684 FETCH get_root_node INTO l_root_node_csr_rec;
1685 CLOSE get_root_node;
1686
1687 IF (l_root_node_csr_rec.relationship_id IS NOT NULL)
1688 THEN
1689 l_node_rec.relationship_id := l_root_node_csr_rec.relationship_id;
1690 l_node_rec.mc_header_id := l_mc_header_id;
1691 l_node_rec.position_key := l_root_node_csr_rec.position_key;
1692 l_node_rec.position_ref_code := l_root_node_csr_rec.position_ref_code;
1693 l_node_rec.position_ref_meaning := l_root_node_csr_rec.position_ref_meaning;
1694 l_node_rec.position_necessity_code := l_root_node_csr_rec.position_necessity_code;
1695 l_node_rec.position_necessity_meaning := l_root_node_csr_rec.position_necessity_meaning;
1696 --R12
1697 --priyan MEL-CDL
1698 l_node_rec.ata_code := l_root_node_csr_rec.ata_code;
1699 l_node_rec.ata_meaning := l_root_node_csr_rec.ata_meaning;
1700 l_node_rec.uom_code := l_root_node_csr_rec.uom_code;
1701 l_node_rec.quantity := l_root_node_csr_rec.quantity;
1702 l_node_rec.parent_relationship_id := l_root_node_csr_rec.parent_relationship_id;
1703 l_node_rec.item_group_id := l_root_node_csr_rec.item_group_id;
1704 l_node_rec.item_group_name := l_root_node_csr_rec.group_name;
1705 l_node_rec.display_order := l_root_node_csr_rec.display_order;
1706 l_node_rec.active_start_date := l_root_node_csr_rec.active_start_date;
1707 l_node_rec.active_end_date := l_root_node_csr_rec.active_end_date;
1708 l_node_rec.object_version_number := 1;
1709 l_node_rec.security_group_id := l_root_node_csr_rec.security_group_id;
1710 l_node_rec.attribute_category := l_root_node_csr_rec.attribute_category;
1711 l_node_rec.attribute1 := l_root_node_csr_rec.attribute1;
1712 l_node_rec.attribute2 := l_root_node_csr_rec.attribute2;
1713 l_node_rec.attribute3 := l_root_node_csr_rec.attribute3;
1714 l_node_rec.attribute4 := l_root_node_csr_rec.attribute4;
1715 l_node_rec.attribute5 := l_root_node_csr_rec.attribute5;
1716 l_node_rec.attribute6 := l_root_node_csr_rec.attribute6;
1717 l_node_rec.attribute7 := l_root_node_csr_rec.attribute7;
1718 l_node_rec.attribute8 := l_root_node_csr_rec.attribute8;
1719 l_node_rec.attribute9 := l_root_node_csr_rec.attribute9;
1720 l_node_rec.attribute10 := l_root_node_csr_rec.attribute10;
1721 l_node_rec.attribute11 := l_root_node_csr_rec.attribute11;
1722 l_node_rec.attribute12 := l_root_node_csr_rec.attribute12;
1723 l_node_rec.attribute13 := l_root_node_csr_rec.attribute13;
1724 l_node_rec.attribute14 := l_root_node_csr_rec.attribute14;
1725 l_node_rec.attribute15 := l_root_node_csr_rec.attribute15;
1726 l_node_rec.operation_flag := G_DML_COPY;
1727 l_node_rec.parent_node_rec_index := null;
1728
1729 l_node_idx := l_node_idx + 1;
1730 l_nodes_tbl(l_node_idx) := l_node_rec;
1731
1732 -- Since this is the topnode of a MC, no counter rules exist
1733 END IF;
1734 END IF;
1735
1736 OPEN get_mc_tree_csr (p_source_rel_id);
1737 LOOP
1738 FETCH get_mc_tree_csr INTO l_node_csr_rec;
1739 EXIT WHEN get_mc_tree_csr%NOTFOUND;
1740
1741 IF (l_node_csr_rec.parent_relationship_id IS NOT NULL)
1742 THEN
1743 -- Since the topnode would already be created in the Create_MC_Revision / Copy_Master_Config call...
1744
1745 OPEN get_mc_details_csr(l_node_csr_rec.relationship_id);
1746 FETCH get_mc_details_csr INTO l_mc_details_rec;
1747 CLOSE get_mc_details_csr;
1748 -- 7a. Read all values from l_node_csr_rec into l_node_rec
1749 l_node_rec.relationship_id := l_node_csr_rec.relationship_id;
1750 l_node_rec.mc_header_id := l_mc_header_id;
1751 l_node_rec.position_key := l_node_csr_rec.position_key;
1752 l_node_rec.position_ref_code := l_node_csr_rec.position_ref_code;
1753 -- changed
1754 l_node_rec.position_ref_meaning := l_mc_details_rec.POSITION_REF_MEANING;
1755 l_node_rec.position_necessity_code := l_node_csr_rec.position_necessity_code;
1756
1757 --R12
1758 --priyan MEL-CDL
1759 l_node_rec.ata_meaning := l_mc_details_rec.ata_meaning;
1760 l_node_rec.ata_code := l_node_csr_rec.ata_code;
1761 -- changed
1762 l_node_rec.position_necessity_meaning := l_mc_details_rec.POSITION_NECESSITY_MEANING;
1763 l_node_rec.uom_code := l_node_csr_rec.uom_code;
1764 l_node_rec.quantity := l_node_csr_rec.quantity;
1765 l_node_rec.parent_relationship_id := l_node_csr_rec.parent_relationship_id;
1766 l_node_rec.item_group_id := l_node_csr_rec.item_group_id;
1767 -- changed
1768 l_node_rec.item_group_name := l_mc_details_rec.GROUP_NAME;
1769 l_node_rec.display_order := l_node_csr_rec.display_order;
1770 l_node_rec.active_start_date := l_node_csr_rec.active_start_date;
1771 l_node_rec.active_end_date := l_node_csr_rec.active_end_date;
1772 l_node_rec.object_version_number := 1;
1773 l_node_rec.security_group_id := l_node_csr_rec.security_group_id;
1774 l_node_rec.attribute_category := l_node_csr_rec.attribute_category;
1775 l_node_rec.attribute1 := l_node_csr_rec.attribute1;
1776 l_node_rec.attribute2 := l_node_csr_rec.attribute2;
1777 l_node_rec.attribute3 := l_node_csr_rec.attribute3;
1778 l_node_rec.attribute4 := l_node_csr_rec.attribute4;
1779 l_node_rec.attribute5 := l_node_csr_rec.attribute5;
1780 l_node_rec.attribute6 := l_node_csr_rec.attribute6;
1781 l_node_rec.attribute7 := l_node_csr_rec.attribute7;
1782 l_node_rec.attribute8 := l_node_csr_rec.attribute8;
1783 l_node_rec.attribute9 := l_node_csr_rec.attribute9;
1784 l_node_rec.attribute10 := l_node_csr_rec.attribute10;
1785 l_node_rec.attribute11 := l_node_csr_rec.attribute11;
1786 l_node_rec.attribute12 := l_node_csr_rec.attribute12;
1787 l_node_rec.attribute13 := l_node_csr_rec.attribute13;
1788 l_node_rec.attribute14 := l_node_csr_rec.attribute14;
1789 l_node_rec.attribute15 := l_node_csr_rec.attribute15;
1790 l_node_rec.operation_flag := G_DML_COPY;
1791 l_node_rec.parent_node_rec_index := null;
1792
1793 l_node_idx := l_node_idx + 1;
1794 l_nodes_tbl(l_node_idx) := l_node_rec;
1795
1796 OPEN get_ctr_rule_update_csr (l_node_csr_rec.relationship_id);
1797 LOOP
1798 FETCH get_ctr_rule_update_csr INTO l_ctr_rule_csr_rec;
1799 EXIT WHEN get_ctr_rule_update_csr%NOTFOUND;
1800
1801 -- 7g.v. Read all values from l_ctr_rule_csr_rec into l_ctr_rule_rec
1802 l_ctr_rule_rec.ctr_update_rule_id := l_ctr_rule_csr_rec.ctr_update_rule_id;
1803 l_ctr_rule_rec.relationship_id := l_ctr_rule_csr_rec.relationship_id;
1804 l_ctr_rule_rec.uom_code := l_ctr_rule_csr_rec.uom_code;
1805 l_ctr_rule_rec.rule_code := l_ctr_rule_csr_rec.rule_code;
1806 AHL_UTIL_MC_PKG.Convert_To_LookupMeaning ('AHL_COUNTER_RULE_TYPE', l_ctr_rule_rec.rule_code, l_ctr_rule_rec.rule_meaning, l_ret_val);
1807 l_ctr_rule_rec.ratio := l_ctr_rule_csr_rec.ratio;
1808 l_ctr_rule_rec.object_version_number := 1;
1809 l_ctr_rule_rec.attribute_category := l_ctr_rule_csr_rec.attribute_category;
1810 l_ctr_rule_rec.attribute1 := l_ctr_rule_csr_rec.attribute1;
1811 l_ctr_rule_rec.attribute2 := l_ctr_rule_csr_rec.attribute2;
1812 l_ctr_rule_rec.attribute3 := l_ctr_rule_csr_rec.attribute3;
1813 l_ctr_rule_rec.attribute4 := l_ctr_rule_csr_rec.attribute4;
1814 l_ctr_rule_rec.attribute5 := l_ctr_rule_csr_rec.attribute5;
1815 l_ctr_rule_rec.attribute6 := l_ctr_rule_csr_rec.attribute6;
1816 l_ctr_rule_rec.attribute7 := l_ctr_rule_csr_rec.attribute7;
1817 l_ctr_rule_rec.attribute8 := l_ctr_rule_csr_rec.attribute8;
1818 l_ctr_rule_rec.attribute9 := l_ctr_rule_csr_rec.attribute9;
1819 l_ctr_rule_rec.attribute10 := l_ctr_rule_csr_rec.attribute10;
1820 l_ctr_rule_rec.attribute11 := l_ctr_rule_csr_rec.attribute11;
1821 l_ctr_rule_rec.attribute12 := l_ctr_rule_csr_rec.attribute12;
1822 l_ctr_rule_rec.attribute13 := l_ctr_rule_csr_rec.attribute13;
1823 l_ctr_rule_rec.attribute14 := l_ctr_rule_csr_rec.attribute14;
1824 l_ctr_rule_rec.attribute15 := l_ctr_rule_csr_rec.attribute15;
1825 l_ctr_rule_rec.operation_flag := G_DML_CREATE;
1826
1827 l_ctr_rule_rec.node_tbl_index := l_node_idx;
1828 l_ctr_rule_idx := l_ctr_rule_idx + 1;
1829 l_ctr_rules_tbl(l_ctr_rule_idx) := l_ctr_rule_rec;
1830 END LOOP;
1831 CLOSE get_ctr_rule_update_csr;
1832 END IF;
1833 END LOOP;
1834 CLOSE get_mc_tree_csr;
1835
1836 IF (l_nodes_tbl.COUNT > 0)
1837 THEN
1838 IF (p_node_copy = TRUE)
1839 THEN
1840 -- Implies copying a MC node to another MC node
1841 l_nodes_tbl(1).parent_relationship_id := p_dest_rel_id;
1842
1843 -- Read max display_order of the children of p_dest_rel_id
1844 OPEN get_max_dispord(p_dest_rel_id);
1845 FETCH get_max_dispord INTO l_max_dispord;
1846 CLOSE get_max_dispord;
1847
1848 l_nodes_tbl(1).display_order := nvl(l_max_dispord, 0) + 1;
1849 END IF;
1850
1851 -- 7j. Iterate through the l_nodes_tbl, to nullify relationship_id and populate parent_node_rec_index
1852 FOR i IN 1..l_nodes_tbl.COUNT
1853 LOOP
1854 FOR j IN i+1..l_nodes_tbl.LAST
1855 LOOP
1856 IF (l_nodes_tbl(j).PARENT_RELATIONSHIP_ID = l_nodes_tbl(i).RELATIONSHIP_ID)
1857 THEN
1858 l_nodes_tbl(j).PARENT_NODE_REC_INDEX := i;
1859 l_nodes_tbl(j).PARENT_RELATIONSHIP_ID := NULL;
1860 END IF;
1861 END LOOP;
1862
1863 -- For documents copy and subconfigurations copy
1864 l_old_node_id_tbl(i) := l_nodes_tbl(i).RELATIONSHIP_ID;
1865 l_nodes_tbl(i).RELATIONSHIP_ID := NULL;
1866 END LOOP;
1867
1868 -- 7l. Iterate through the l_nodes_tbl table to create the nodes and associated counter rules
1869 FOR i IN l_nodes_tbl.FIRST..l_nodes_tbl.LAST
1870 LOOP
1871 IF (p_node_copy = FALSE AND l_nodes_tbl(i).parent_relationship_id = p_source_rel_id)
1872 THEN
1873 -- Implies copying entire MC tree into another with the topnode already created
1874 l_nodes_tbl(i).parent_relationship_id := p_dest_rel_id;
1875 END IF;
1876
1877 l_ctr_iterator := 0;
1878
1879 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1880 THEN
1881 fnd_log.string
1882 (
1883 fnd_log.level_statement,
1884 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
1885 'Creating new node with parent node ['||l_nodes_tbl(i).parent_relationship_id||'], position reference ['||l_nodes_tbl(i).position_ref_code||']'
1886 );
1887 END IF;
1888
1889 -- 7l.iii. Iterate through the l_ctr_rules_table and construct a counter rules table for the particular node in consideration
1890 IF (l_ctr_rules_tbl.COUNT > 0)
1891 THEN
1892 FOR j IN l_ctr_rules_tbl.FIRST..l_ctr_rules_tbl.LAST
1893 LOOP
1894 IF (l_ctr_rules_tbl(j).node_tbl_index = i)
1895 THEN
1896 l_ctr_iterator := l_ctr_iterator + 1;
1897 l_node_ctr_rules_tbl(l_ctr_iterator) := l_ctr_rules_tbl(j);
1898
1899 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1900 THEN
1901 fnd_log.string
1902 (
1903 fnd_log.level_statement,
1904 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
1905 'Creating new counter rule ['||l_node_ctr_rules_tbl(l_ctr_iterator).ctr_update_rule_id||']'
1906 );
1907 END IF;
1908 END IF;
1909 END LOOP;
1910 END IF;
1911
1912 -- 7l.iv. Call AHL_MC_Node_PVT.Create_Node
1913 Create_Node
1914 (
1915 p_api_version => 1.0,
1916 p_init_msg_list => FND_API.G_FALSE,
1917 p_commit => FND_API.G_FALSE,
1918 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1919 x_return_status => l_return_status,
1920 x_msg_count => l_msg_count,
1921 x_msg_data => l_msg_data,
1922 p_x_node_rec => l_nodes_tbl(i),
1923 p_x_counter_rules_tbl => l_node_ctr_rules_tbl,
1924 p_x_subconfig_tbl => l_subconfig_tbl
1925 );
1926
1927 -- Check Error Message stack.
1928 x_msg_count := FND_MSG_PUB.count_msg;
1929 IF x_msg_count > 0 THEN
1930 RAISE FND_API.G_EXC_ERROR;
1931 END IF;
1932
1933 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1934 THEN
1935 fnd_log.string
1936 (
1937 fnd_log.level_statement,
1938 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
1939 'New Node ['||l_nodes_tbl(i).relationship_id||', '||l_nodes_tbl(i).position_ref_code||'] created'
1940 );
1941 END IF;
1942
1943 -- 7l.v. Set parent_relationship_id for all nodes that refer this newly created node as parent
1944 FOR x IN i+1..l_nodes_tbl.COUNT
1945 LOOP
1946 IF (l_nodes_tbl(x).PARENT_NODE_REC_INDEX = i)
1947 THEN
1948 l_nodes_tbl(x).PARENT_RELATIONSHIP_ID := l_nodes_tbl(i).RELATIONSHIP_ID;
1949
1950 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1951 THEN
1952 fnd_log.string
1953 (
1954 fnd_log.level_statement,
1955 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
1956 'Populated node ['||l_nodes_tbl(x).position_ref_code||'] with parent relationship id ['||l_nodes_tbl(i).RELATIONSHIP_ID||']'
1957 );
1958 END IF;
1959 END IF;
1960 END LOOP;
1961 END LOOP;
1962
1963 -- 7m. Iterate through the l_node_tbl table to copy all document and subconfiguration associations
1964 FOR i IN l_nodes_tbl.FIRST..l_nodes_tbl.LAST
1965 LOOP
1966 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1967 THEN
1968 fnd_log.string
1969 (
1970 fnd_log.level_statement,
1971 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
1972 'Document associations for node ['||l_old_node_id_tbl(i)||'] copied to node ['||l_nodes_tbl(i).relationship_id||']'
1973 );
1974 END IF;
1975
1976 -- 7m.i. Call AHL_DI_ASSO_DOC_GEN_PVT.COPY_ASSOCIATION
1977 AHL_DI_ASSO_DOC_GEN_PVT.COPY_ASSOCIATION
1978 (
1979 p_api_version => 1.0,
1980 p_commit => FND_API.G_FALSE,
1981 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1982 p_from_object_id => l_old_node_id_tbl(i),
1983 p_from_object_type => 'MC',
1984 p_to_object_id => l_nodes_tbl(i).relationship_id,
1985 p_to_object_type => 'MC',
1986 x_return_status => l_return_status,
1987 x_msg_count => l_msg_count,
1988 x_msg_data => l_msg_data
1989 );
1990
1991 -- Check Error Message stack.
1992 x_msg_count := FND_MSG_PUB.count_msg;
1993 IF x_msg_count > 0 THEN
1994 RAISE FND_API.G_EXC_ERROR;
1995 END IF;
1996
1997 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1998 THEN
1999 fnd_log.string
2000 (
2001 fnd_log.level_statement,
2002 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
2003 'Subconfig associations for node ['||l_old_node_id_tbl(i)||'] copied to node ['||l_nodes_tbl(i).relationship_id||']'
2004 );
2005 END IF;
2006
2007 -- 7m.ii. Call AHL_MC_SubConfig_PVT.Copy_SubConfig
2008 Copy_SubConfig
2009 (
2010 l_old_node_id_tbl(i),
2011 l_nodes_tbl(i).relationship_id
2012 );
2013
2014 -- Check Error Message stack.
2015 x_msg_count := FND_MSG_PUB.count_msg;
2016 IF x_msg_count > 0 THEN
2017 RAISE FND_API.G_EXC_ERROR;
2018 END IF;
2019 END LOOP;
2020 END IF;
2021
2022 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
2023 THEN
2024 fnd_log.string
2025 (
2026 fnd_log.level_procedure,
2027 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||'.end',
2028 'At the end of PLSQL procedure'
2029 );
2030 END IF;
2031 -- API body ends here
2032
2033 -- Check Error Message stack.
2034 x_msg_count := FND_MSG_PUB.count_msg;
2035 IF x_msg_count > 0 THEN
2036 RAISE FND_API.G_EXC_ERROR;
2037 END IF;
2038
2039 -- Standard check for p_commit
2040 IF FND_API.TO_BOOLEAN (p_commit)
2041 THEN
2042 COMMIT WORK;
2043 END IF;
2044
2045 -- Standard call to get message count and if count is 1, get message info
2046 FND_MSG_PUB.count_and_get
2047 (
2048 p_count => x_msg_count,
2049 p_data => x_msg_data,
2050 p_encoded => FND_API.G_FALSE
2051 );
2052
2053 EXCEPTION
2054 WHEN FND_API.G_EXC_ERROR THEN
2055 x_return_status := FND_API.G_RET_STS_ERROR;
2056 Rollback to Copy_MC_Nodes_SP;
2057 FND_MSG_PUB.count_and_get
2058 (
2059 p_count => x_msg_count,
2060 p_data => x_msg_data,
2061 p_encoded => FND_API.G_FALSE
2062 );
2063
2064 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2065 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2066 Rollback to Copy_MC_Nodes_SP;
2067 FND_MSG_PUB.count_and_get
2068 (
2069 p_count => x_msg_count,
2070 p_data => x_msg_data,
2071 p_encoded => FND_API.G_FALSE
2072 );
2073
2074 WHEN OTHERS THEN
2075 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2076 Rollback to Copy_MC_Nodes_SP;
2077 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2078 THEN
2079 FND_MSG_PUB.add_exc_msg
2080 (
2081 p_pkg_name => G_PKG_NAME,
2082 p_procedure_name => 'Copy_MC_Nodes',
2083 p_error_text => SUBSTR(SQLERRM,1,240)
2084 );
2085 END IF;
2086 FND_MSG_PUB.count_and_get
2087 (
2088 p_count => x_msg_count,
2089 p_data => x_msg_data,
2090 p_encoded => FND_API.G_FALSE
2091 );
2092
2093 END Copy_MC_Nodes;
2094
2095 PROCEDURE Process_Documents
2096 (
2097 p_api_version IN NUMBER,
2098 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2099 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2100 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2101 x_return_status OUT NOCOPY VARCHAR2,
2102 x_msg_count OUT NOCOPY NUMBER,
2103 x_msg_data OUT NOCOPY VARCHAR2,
2104 p_node_id IN NUMBER,
2105 p_x_documents_tbl IN OUT NOCOPY AHL_DI_ASSO_DOC_GEN_PUB.association_tbl
2106 )
2107 IS
2108
2109 -- 1. Define local variables
2110 l_api_name CONSTANT VARCHAR2(30) := 'Process_Documents';
2111 l_api_version CONSTANT NUMBER := 1.0;
2112 l_return_status VARCHAR2(1);
2113 l_msg_count NUMBER;
2114 l_msg_data VARCHAR2(2000);
2115
2116 l_header_status VARCHAR2(30);
2117
2118 BEGIN
2119
2120 -- Standard start of API savepoint
2121 SAVEPOINT Process_Documents_SP;
2122
2123 -- Standard call to check for call compatibility
2124 IF NOT FND_API.compatible_api_call(l_api_version, p_api_version, l_api_name, G_PKG_NAME)
2125 THEN
2126 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2127 END IF;
2128
2129 -- Initialize message list if p_init_msg_list is set to TRUE
2130 IF FND_API.TO_BOOLEAN(p_init_msg_list)
2131 THEN
2132 FND_MSG_PUB.Initialize;
2133 END IF;
2134
2135 -- Initialize API return status to success
2136 x_return_status := FND_API.G_RET_STS_SUCCESS;
2137
2138 -- API body starts here
2139 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
2140 THEN
2141 fnd_log.string
2142 (
2143 fnd_log.level_procedure,
2144 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||'.begin',
2145 'At the start of PLSQL procedure'
2146 );
2147 END IF;
2148
2149 -- 5. Validate a MC node with relationship_id = p_node_id exists
2150 Validate_Node_Exists (p_node_id, null);
2151
2152 l_header_status := Get_MC_Status(p_node_id, null);
2153 -- 6i. Validate that the config_status_code of the MC is 'DRAFT' or 'APPROVAL_REJECTED'
2154 IF NOT (l_header_status IN ('DRAFT', 'APPROVAL_REJECTED'))
2155 THEN
2156 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_DOC_STS_INV');
2157 FND_MSG_PUB.ADD;
2158 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
2159 THEN
2160 fnd_log.message
2161 (
2162 fnd_log.level_exception,
2163 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
2164 false
2165 );
2166 END IF;
2167 RAISE FND_API.G_EXC_ERROR;
2168 ELSIF (l_header_status = 'APPROVAL_REJECTED')
2169 THEN
2170 -- 6ii. Set status of MC to DRAFT if APPROVAL_REJECTED
2171 Set_Header_Status(p_node_id);
2172 END IF;
2173
2174 -- Check Error Message stack.
2175 x_msg_count := FND_MSG_PUB.count_msg;
2176 IF x_msg_count > 0 THEN
2177 RAISE FND_API.G_EXC_ERROR;
2178 END IF;
2179
2180 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
2181 THEN
2182 fnd_log.string
2183 (
2184 fnd_log.level_statement,
2185 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
2186 'Node validation successful'
2187 );
2188 END IF;
2189
2190 IF (p_x_documents_tbl.COUNT > 0)
2191 THEN
2192 FOR i IN p_x_documents_tbl.FIRST..p_x_documents_tbl.LAST
2193 LOOP
2194 p_x_documents_tbl(i).aso_object_id := p_node_id;
2195 p_x_documents_tbl(i).aso_object_type_code := 'MC';
2196
2197 -- If revision not chosen, throw error
2198 IF (p_x_documents_tbl(i).REVISION_NO IS NULL AND p_x_documents_tbl(i).dml_operation <> G_DML_DELETE)
2199 THEN
2200 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_DOC_NO_REV');
2201 FND_MESSAGE.Set_Token('DOC', p_x_documents_tbl(i).DOCUMENT_NO);
2202 FND_MSG_PUB.ADD;
2203 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
2204 THEN
2205 fnd_log.message
2206 (
2207 fnd_log.level_exception,
2208 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
2209 false
2210 );
2211 END IF;
2212 END IF;
2213 END LOOP;
2214
2215 -- Check Error Message stack.
2216 x_msg_count := FND_MSG_PUB.count_msg;
2217 IF x_msg_count > 0 THEN
2218 RAISE FND_API.G_EXC_ERROR;
2219 END IF;
2220
2221 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
2222 THEN
2223 fnd_log.string
2224 (
2225 fnd_log.level_statement,
2226 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
2227 'Document association validations successful... Calling AHL_DI_ASSO_DOC_GEN_PUB.PROCESS_ASSOCIATION'
2228 );
2229 END IF;
2230
2231 AHL_DI_ASSO_DOC_GEN_PUB.PROCESS_ASSOCIATION
2232 (
2233 p_api_version => 1.0,
2234 p_init_msg_list => FND_API.G_FALSE,
2235 p_commit => FND_API.G_FALSE,
2236 p_validate_only => FND_API.G_FALSE,
2237 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2238 p_x_association_tbl => p_x_documents_tbl,
2239 p_module_type => 'JSP',
2240 x_return_status => l_return_status,
2241 x_msg_count => l_msg_count,
2242 x_msg_data => l_msg_data
2243 );
2244 END IF;
2245
2246 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
2247 THEN
2248 fnd_log.string
2249 (
2250 fnd_log.level_procedure,
2251 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||'.end',
2252 'At the end of PLSQL procedure'
2253 );
2254 END IF;
2255 -- API body ends here
2256
2257 -- Check Error Message stack.
2258 x_msg_count := FND_MSG_PUB.count_msg;
2259 IF x_msg_count > 0 THEN
2260 RAISE FND_API.G_EXC_ERROR;
2261 END IF;
2262
2263 -- Standard check for p_commit
2264 IF FND_API.TO_BOOLEAN (p_commit)
2265 THEN
2266 COMMIT WORK;
2267 END IF;
2268
2269 -- Standard call to get message count and if count is 1, get message info
2270 FND_MSG_PUB.count_and_get
2271 (
2272 p_count => x_msg_count,
2273 p_data => x_msg_data,
2274 p_encoded => FND_API.G_FALSE
2275 );
2276
2277 EXCEPTION
2278 WHEN FND_API.G_EXC_ERROR THEN
2279 x_return_status := FND_API.G_RET_STS_ERROR;
2280 Rollback to Process_Documents_SP;
2281 FND_MSG_PUB.count_and_get
2282 (
2283 p_count => x_msg_count,
2284 p_data => x_msg_data,
2285 p_encoded => FND_API.G_FALSE
2286 );
2287
2288 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2289 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2290 Rollback to Process_Documents_SP;
2291 FND_MSG_PUB.count_and_get
2292 (
2293 p_count => x_msg_count,
2294 p_data => x_msg_data,
2295 p_encoded => FND_API.G_FALSE
2296 );
2297
2298 WHEN OTHERS THEN
2299 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2300 Rollback to Process_Documents_SP;
2301 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2302 THEN
2303 FND_MSG_PUB.add_exc_msg
2304 (
2305 p_pkg_name => G_PKG_NAME,
2306 p_procedure_name => 'Process_Documents',
2307 p_error_text => SUBSTR(SQLERRM,1,240)
2308 );
2309 END IF;
2310 FND_MSG_PUB.count_and_get
2311 (
2312 p_count => x_msg_count,
2313 p_data => x_msg_data,
2314 p_encoded => FND_API.G_FALSE
2315 );
2316
2317 END Process_Documents;
2318
2319 PROCEDURE Associate_Item_Group
2320 (
2321 p_api_version IN NUMBER,
2322 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2323 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2324 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2325 x_return_status OUT NOCOPY VARCHAR2,
2326 x_msg_count OUT NOCOPY NUMBER,
2327 x_msg_data OUT NOCOPY VARCHAR2,
2328 p_nodes_tbl IN Node_Tbl_Type
2329 )
2330 IS
2331
2332 -- Define cursor get_item_group_det to validate item group exists
2333 CURSOR get_item_group_det
2334 (
2335 p_ig_id in number
2336 )
2337 IS
2338 SELECT type_code, name
2339 FROM ahl_item_groups_b
2340 WHERE item_group_id = p_ig_id;
2341
2342 -- Define get_item_group_id to retrieve item_group_id given name of the item group
2343 CURSOR get_item_group_csr
2344 (
2345 p_ig_name in VARCHAR2
2346 )
2347 IS
2348 SELECT item_group_id, type_code, name
2349 FROM ahl_item_groups_b
2350 WHERE upper(name) = upper (p_ig_name) AND
2351 source_item_group_id IS NULL;
2352
2353 -- 1. Define local variables
2354 l_api_name CONSTANT VARCHAR2(30) := 'Associate_Item_Group';
2355 l_api_version CONSTANT NUMBER := 1.0;
2356
2357 l_header_status VARCHAR2(30);
2358 l_item_group_id NUMBER;
2359 l_type_code VARCHAR2(30);
2360 l_item_group_name VARCHAR2(80);
2361
2362 BEGIN
2363
2364 -- Standard start of API savepoint
2365 SAVEPOINT Associate_Item_Group_SP;
2366
2367 -- Standard call to check for call compatibility
2368 IF NOT FND_API.compatible_api_call(l_api_version, p_api_version, l_api_name, G_PKG_NAME)
2369 THEN
2370 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2371 END IF;
2372
2373 -- Initialize message list if p_init_msg_list is set to TRUE
2374 IF FND_API.TO_BOOLEAN(p_init_msg_list)
2375 THEN
2376 FND_MSG_PUB.Initialize;
2377 END IF;
2378
2379 -- Initialize API return status to success
2380 x_return_status := FND_API.G_RET_STS_SUCCESS;
2381
2382 -- API body starts here
2383 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
2384 THEN
2385 fnd_log.string
2386 (
2387 fnd_log.level_procedure,
2388 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||'.begin',
2389 'At the start of PLSQL procedure'
2390 );
2391 END IF;
2392
2393 IF (p_nodes_tbl.COUNT > 0)
2394 THEN
2395 FOR i IN p_nodes_tbl.FIRST..p_nodes_tbl.LAST
2396 LOOP
2397 -- Validate config_status_code of the MC is 'DRAFT' or 'APPROVAL_REJECTED'
2398 l_header_status := Get_MC_Status(p_nodes_tbl(i).relationship_id, null);
2399 IF NOT (l_header_status IN ('DRAFT', 'APPROVAL_REJECTED'))
2400 THEN
2401 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_NODE_STS_INV');
2402 FND_MSG_PUB.ADD;
2403 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
2404 THEN
2405 fnd_log.message
2406 (
2407 fnd_log.level_exception,
2408 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
2409 false
2410 );
2411 END IF;
2412 RAISE FND_API.G_EXC_ERROR;
2413 ELSIF (l_header_status = 'APPROVAL_REJECTED')
2414 THEN
2415 -- Set status of MC to DRAFT if APPROVAL_REJECTED
2416 Set_Header_Status(p_nodes_tbl(i).relationship_id);
2417 END IF;
2418
2419 -- Validate a MC node with relationship_id = p_x_node_rec.relationship_id exists
2420 Validate_Node_Exists(p_nodes_tbl(i).relationship_id, null);
2421
2422 -- Validate p_x_node_rec.item_group_id exists
2423 IF (p_nodes_tbl(i).ITEM_GROUP_ID IS NOT NULL)
2424 THEN
2425 OPEN get_item_group_det (p_nodes_tbl(i).item_group_id);
2426 FETCH get_item_group_det INTO l_type_code,l_item_group_name;
2427 IF (get_item_group_det%NOTFOUND)
2428 THEN
2429 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_ITEMGRP_INVALID');
2430 FND_MESSAGE.Set_Token('ITEM_GRP', p_nodes_tbl(i).item_group_id);
2431 FND_MSG_PUB.ADD;
2432 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
2433 THEN
2434 fnd_log.message
2435 (
2436 fnd_log.level_exception,
2437 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
2438 false
2439 );
2440 END IF;
2441 ELSE
2442 IF l_type_code = 'TRACKED' THEN
2443 l_item_group_id := p_nodes_tbl(i).item_group_id;
2444 ELSE
2445 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_IG_NOT_TRACKED');
2446 FND_MESSAGE.Set_Token('IG_NAME', l_item_group_name);
2447 FND_MSG_PUB.ADD;
2448 END IF;
2449
2450 END IF;
2451 CLOSE get_item_group_det;
2452 ELSIF (p_nodes_tbl(i).item_group_name IS NOT NULL)
2453 THEN
2454 OPEN get_item_group_csr (p_nodes_tbl(i).item_group_name);
2455 FETCH get_item_group_csr INTO l_item_group_id,l_type_code,l_item_group_name;
2456 IF (get_item_group_csr%NOTFOUND)
2457 THEN
2458 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_ITEMGRP_INVALID');
2459 FND_MESSAGE.Set_Token('ITEM_GRP', p_nodes_tbl(i).item_group_name);
2460 FND_MSG_PUB.ADD;
2461 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
2462 THEN
2463 fnd_log.message
2464 (
2465 fnd_log.level_exception,
2466 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
2467 false
2468 );
2469 END IF;
2470 ELSIF l_type_code <> 'TRACKED' THEN
2471 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_IG_NOT_TRACKED');
2472 FND_MESSAGE.Set_Token('IG_NAME', l_item_group_name);
2473 FND_MSG_PUB.ADD;
2474 END IF;
2475 CLOSE get_item_group_csr;
2476 ELSE
2477 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_ITEMGRP_NULL');
2478 FND_MSG_PUB.ADD;
2479 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
2480 THEN
2481 fnd_log.message
2482 (
2483 fnd_log.level_exception,
2484 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
2485 false
2486 );
2487 END IF;
2488 END IF;
2489
2490 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
2491 THEN
2492 fnd_log.string
2493 (
2494 fnd_log.level_statement,
2495 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
2496 'Validation successful'
2497 );
2498 END IF;
2499
2500 -- Check Error Message stack.
2501 x_msg_count := FND_MSG_PUB.count_msg;
2502 IF x_msg_count > 0 THEN
2503 RAISE FND_API.G_EXC_ERROR;
2504 END IF;
2505
2506 UPDATE ahl_mc_relationships
2507 SET item_group_id = l_item_group_id
2508 WHERE relationship_id = p_nodes_tbl(i).relationship_id;
2509
2510 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
2511 THEN
2512 fnd_log.string
2513 (
2514 fnd_log.level_statement,
2515 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
2516 'Updated MC node ['||p_nodes_tbl(i).relationship_id||'] with new item group id ['||l_item_group_id||']'
2517 );
2518 END IF;
2519 END LOOP;
2520 END IF;
2521
2522 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
2523 THEN
2524 fnd_log.string
2525 (
2526 fnd_log.level_procedure,
2527 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||'.end',
2528 'At the end of PLSQL procedure'
2529 );
2530 END IF;
2531 -- API body ends here
2532
2533 -- Check Error Message stack.
2534 x_msg_count := FND_MSG_PUB.count_msg;
2535 IF x_msg_count > 0 THEN
2536 RAISE FND_API.G_EXC_ERROR;
2537 END IF;
2538
2539 -- Standard check for p_commit
2540 IF FND_API.TO_BOOLEAN (p_commit)
2541 THEN
2542 COMMIT WORK;
2543 END IF;
2544
2545 -- Standard call to get message count and if count is 1, get message info
2546 FND_MSG_PUB.count_and_get
2547 (
2548 p_count => x_msg_count,
2549 p_data => x_msg_data,
2550 p_encoded => FND_API.G_FALSE
2551 );
2552
2553 EXCEPTION
2554 WHEN FND_API.G_EXC_ERROR THEN
2555 x_return_status := FND_API.G_RET_STS_ERROR;
2556 Rollback to Associate_Item_Group_SP;
2557 FND_MSG_PUB.count_and_get
2558 (
2559 p_count => x_msg_count,
2560 p_data => x_msg_data,
2561 p_encoded => FND_API.G_FALSE
2562 );
2563
2564 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2565 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2566 Rollback to Associate_Item_Group_SP;
2567 FND_MSG_PUB.count_and_get
2568 (
2569 p_count => x_msg_count,
2570 p_data => x_msg_data,
2571 p_encoded => FND_API.G_FALSE
2572 );
2573
2574 WHEN OTHERS THEN
2575 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2576 Rollback to Associate_Item_Group_SP;
2577 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2578 THEN
2579 FND_MSG_PUB.add_exc_msg
2580 (
2581 p_pkg_name => G_PKG_NAME,
2582 p_procedure_name => 'Associate_Item_Group',
2583 p_error_text => SUBSTR(SQLERRM,1,240)
2584 );
2585 END IF;
2586 FND_MSG_PUB.count_and_get
2587 (
2588 p_count => x_msg_count,
2589 p_data => x_msg_data,
2590 p_encoded => FND_API.G_FALSE
2591 );
2592
2593 END Associate_Item_Group;
2594
2595 ---------------------------
2596 -- Validation procedures --
2597 ---------------------------
2598 PROCEDURE Validate_Node_Exists
2599 (
2600 p_rel_id in number,
2601 p_object_ver_num in number
2602 )
2603 IS
2604
2605 CURSOR check_node_exists
2606 IS
2607 SELECT object_version_number
2608 FROM ahl_mc_relationships
2609 WHERE relationship_id = p_rel_id;
2610
2611 BEGIN
2612
2613 OPEN check_node_exists;
2614 FETCH check_node_exists INTO l_dummy_number;
2615 IF (check_node_exists%NOTFOUND)
2616 THEN
2617 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_NODE_NOT_FOUND');
2618 FND_MSG_PUB.ADD;
2619 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
2620 THEN
2621 fnd_log.message
2622 (
2623 fnd_log.level_exception,
2624 'ahl.plsql.'||G_PKG_NAME||'.Validate_Node_Exists',
2625 false
2626 );
2627 END IF;
2628 CLOSE check_node_exists;
2629 RAISE FND_API.G_EXC_ERROR;
2630 ELSIF (NVL(p_object_ver_num, l_dummy_number) <> l_dummy_number)
2631 THEN
2632 FND_MESSAGE.Set_Name('AHL', 'AHL_COM_RECORD_CHANGED');
2633 FND_MSG_PUB.ADD;
2634 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
2635 THEN
2636 fnd_log.message
2637 (
2638 fnd_log.level_exception,
2639 'ahl.plsql.'||G_PKG_NAME||'.Validate_Node_Exists',
2640 false
2641 );
2642 END IF;
2643 CLOSE check_node_exists;
2644 RAISE FND_API.G_EXC_ERROR;
2645 END IF;
2646 CLOSE check_node_exists;
2647
2648 END Validate_Node_Exists;
2649
2650 PROCEDURE Validate_Node
2651 (
2652 p_x_node_rec in out nocopy Node_Rec_Type
2653 )
2654 IS
2655 -- Define cursor get_node_details to check parent quantity = 1 and not expired
2656 CURSOR get_node_details
2657 IS
2658 SELECT quantity,
2659 active_end_date
2660 FROM ahl_mc_relationships
2661 WHERE relationship_id = p_x_node_rec.parent_relationship_id;
2662
2663 -- Define cursor check_subconfig_assos to check whether the parent node has any subconfig associations
2664 CURSOR check_subconfig_assos
2665 IS
2666 SELECT 'x'
2667 FROM ahl_mc_config_relations
2668 WHERE relationship_id = p_x_node_rec.parent_relationship_id;
2669 -- Since expired subconfig associations can be unexpired, so no need to filter on active_end_date
2670 -- AND G_TRUNC_DATE < trunc(nvl(active_end_date, G_SYSDATE + 1));
2671
2672 -- Define cursor check_dup_pos_ref to check whether the parent node does not have the same position reference
2673 CURSOR check_dup_pos_ref
2674 IS
2675 SELECT 'x'
2676 FROM ahl_mc_relationships
2677 WHERE position_ref_code = p_x_node_rec.position_ref_code AND
2678 parent_relationship_id = p_x_node_rec.parent_relationship_id AND
2679 G_TRUNC_DATE < trunc(nvl(active_end_date, G_SYSDATE + 1)) AND
2680 relationship_id <> nvl(p_x_node_rec.relationship_id, -1);
2681
2682 -- Define cursor check_topnode_exists to check whether a topnode already exists for the MC
2683 CURSOR check_topnode_exists
2684 IS
2685 SELECT 'x'
2686 FROM ahl_mc_relationships
2687 WHERE parent_relationship_id is null AND
2688 mc_header_id = p_x_node_rec.mc_header_id;
2689
2690 -- Define cursor get_item_group_id to retrieve item group id, type and status
2691 CURSOR get_item_group_id
2692 IS
2693 SELECT item_group_id, type_code, status_code
2694 FROM ahl_item_groups_b
2695 WHERE upper(name) = upper (p_x_node_rec.item_group_name) AND
2696 source_item_group_id IS NULL;
2697
2698 -- Define cursor check_item_assos_qty to check quantity = 1 for all item associations
2699 -- SATHAPLI::FP OGMA Issue# 105 - Non-Serialized Item Maintenance, 04-Dec-2007
2700 -- As non-serialized items having quantities greater than 1 can be associated to an item group now,
2701 -- this check should not be there to restrict them from being used in MC positions.
2702 /*
2703 CURSOR check_item_assos_qty
2704 IS
2705 SELECT 'x'
2706 FROM ahl_item_associations_b
2707 WHERE item_group_id = p_x_node_rec.item_group_id AND
2708 quantity <> 1;
2709 */
2710
2711 -- Define cursor check_child_exists to check whether the node has any children in which case quantity = 1
2712 CURSOR check_child_exists
2713 IS
2714 SELECT 'x'
2715 FROM ahl_mc_relationships
2716 WHERE parent_relationship_id = p_x_node_rec.relationship_id
2717 AND G_TRUNC_DATE < trunc(nvl(active_end_date, G_SYSDATE + 1));
2718
2719 -- Define cursor check_dup_display_order to check display order duplication
2720 CURSOR check_dup_display_order
2721 IS
2722 SELECT 'x'
2723 FROM ahl_mc_relationships
2724 WHERE display_order = p_x_node_rec.display_order AND
2725 parent_relationship_id = p_x_node_rec.parent_relationship_id AND
2726 G_TRUNC_DATE < trunc(nvl(active_end_date, G_SYSDATE + 1)) AND
2727 relationship_id <> nvl(p_x_node_rec.relationship_id, -1);
2728
2729 -- Define cursor get_node_dates to retrieve start and end date of the node
2730 CURSOR get_node_dates
2731 IS
2732 SELECT active_start_date, active_end_date
2733 FROM ahl_mc_relationships
2734 WHERE relationship_id = p_x_node_rec.relationship_id;
2735
2736 -- Declare local variables
2737 l_qty NUMBER;
2738 l_ret_val BOOLEAN;
2739 l_ig_type VARCHAR2(30);
2740 l_ig_status VARCHAR2(30);
2741 l_start_date DATE;
2742 l_end_date DATE;
2743
2744 BEGIN
2745
2746 -- Validate MC parent node
2747 IF (p_x_node_rec.parent_relationship_id IS NOT NULL)
2748 THEN
2749 OPEN get_node_details;
2750 FETCH get_node_details INTO l_qty, l_end_date;
2751 IF (get_node_details%NOTFOUND)
2752 THEN
2753 -- 2a. Validate that the parent node exists
2754 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_PARENT_INVALID');
2755 FND_MSG_PUB.ADD;
2756 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
2757 THEN
2758 fnd_log.message
2759 (
2760 fnd_log.level_exception,
2761 'ahl.plsql.'||G_PKG_NAME||'.Validate_Node',
2762 false
2763 );
2764 END IF;
2765
2766 CLOSE get_node_details;
2767 RAISE FND_API.G_EXC_ERROR;
2768 ELSE
2769 CLOSE get_node_details;
2770
2771 -- 2c. Validate that the parent node has quantity = 1 [only in this can a child node be added to the parent position]
2772 IF (l_qty <> 1)
2773 THEN
2774 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_PARENT_QTY_INV');
2775 FND_MSG_PUB.ADD;
2776 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
2777 THEN
2778 fnd_log.message
2779 (
2780 fnd_log.level_exception,
2781 'ahl.plsql.'||G_PKG_NAME||'.Validate_Node',
2782 false
2783 );
2784 END IF;
2785 END IF;
2786
2787 -- 2d. Validate for the parent node active_end_date > SYSDATE
2788 IF (trunc(nvl(l_end_date, G_SYSDATE + 1)) <= G_TRUNC_DATE)
2789 THEN
2790 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_PARENT_DATE_INV');
2791 FND_MSG_PUB.ADD;
2792 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
2793 THEN
2794 fnd_log.message
2795 (
2796 fnd_log.level_exception,
2797 'ahl.plsql.'||G_PKG_NAME||'.Validate_Node',
2798 false
2799 );
2800 END IF;
2801 END IF;
2802
2803 -- 2e. Validate that the parent node has no subconfiguration associations
2804 OPEN check_subconfig_assos;
2805 FETCH check_subconfig_assos INTO l_dummy_varchar;
2806 IF (check_subconfig_assos%FOUND)
2807 THEN
2808 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_PARENT_HAS_SUBMC');
2809 FND_MSG_PUB.ADD;
2810 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
2811 THEN
2812 fnd_log.message
2813 (
2814 fnd_log.level_exception,
2815 'ahl.plsql.'||G_PKG_NAME||'.Validate_Node',
2816 false
2817 );
2818 END IF;
2819 END IF;
2820 CLOSE check_subconfig_assos;
2821
2822 -- 2f. Validate that the parent node does not already have any child node with the same position reference code
2823 OPEN check_dup_pos_ref;
2824 FETCH check_dup_pos_ref INTO l_dummy_varchar;
2825 IF (check_dup_pos_ref%FOUND)
2826 THEN
2827 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_PARCHD_INVALID');
2828 FND_MESSAGE.Set_Token('CHILD', p_x_node_rec.position_ref_meaning);
2829 FND_MSG_PUB.ADD;
2830 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
2831 THEN
2832 fnd_log.message
2833 (
2834 fnd_log.level_exception,
2835 'ahl.plsql.'||G_PKG_NAME||'.Validate_Node',
2836 false
2837 );
2838 END IF;
2839 END IF;
2840 CLOSE check_dup_pos_ref;
2841 END IF;
2842 ELSIF (p_x_node_rec.operation_flag = G_DML_CREATE OR p_x_node_rec.operation_flag = G_DML_COPY)
2843 THEN
2844 -- Validate whether a root-node exists already
2845 OPEN check_topnode_exists;
2846 FETCH check_topnode_exists INTO l_dummy_varchar;
2847 IF (check_topnode_exists%FOUND)
2848 THEN
2849 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_PARENT_EXISTS');
2850 FND_MSG_PUB.ADD;
2851 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
2852 THEN
2853 fnd_log.message
2854 (
2855 fnd_log.level_exception,
2856 'ahl.plsql.'||G_PKG_NAME||'.Validate_Node',
2857 false
2858 );
2859 END IF;
2860 CLOSE check_topnode_exists;
2861 RAISE FND_API.G_EXC_ERROR;
2862 END IF;
2863 CLOSE check_topnode_exists;
2864 END IF;
2865
2866 -- Validate position reference
2867 p_x_node_rec.position_ref_meaning := RTRIM(p_x_node_rec.position_ref_meaning);
2868
2869 IF (p_x_node_rec.position_ref_meaning IS NULL)
2870 THEN
2871 -- This is a mandatory field, hence throw error
2872 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_POSREF_NULL');
2873 FND_MSG_PUB.ADD;
2874 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
2875 THEN
2876 fnd_log.message
2877 (
2878 fnd_log.level_exception,
2879 'ahl.plsql.'||G_PKG_NAME||'.Validate_Node',
2880 false
2881 );
2882 END IF;
2883 ELSE
2884 AHL_UTIL_MC_PKG.Convert_To_LookupCode
2885 (
2886 'AHL_POSITION_REFERENCE',
2887 p_x_node_rec.position_ref_meaning,
2888 p_x_node_rec.position_ref_code,
2889 l_ret_val
2890 );
2891
2892 IF NOT (l_ret_val)
2893 THEN
2894 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_POSREF_INVALID');
2895 FND_MESSAGE.Set_Token('POSREF', p_x_node_rec.position_ref_meaning);
2896 FND_MSG_PUB.ADD;
2897 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
2898 THEN
2899 fnd_log.message
2900 (
2901 fnd_log.level_exception,
2902 'ahl.plsql.'||G_PKG_NAME||'.Validate_Node',
2903 false
2904 );
2905 END IF;
2906 END IF;
2907 END IF;
2908
2909 -- Validate position Necessity
2910 IF (p_x_node_rec.position_necessity_code IS NULL)
2911 THEN
2912 -- This is a mandatory field, hence throw error
2913 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_NECESSITY_NULL');
2914 FND_MSG_PUB.ADD;
2915 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
2916 THEN
2917 fnd_log.message
2918 (
2919 fnd_log.level_exception,
2920 'ahl.plsql.'||G_PKG_NAME||'.Validate_Node',
2921 false
2922 );
2923 END IF;
2924 ELSIF NOT (AHL_UTIL_MC_PKG.Validate_Lookup_Code('AHL_POSITION_NECESSITY', p_x_node_rec.position_necessity_code))
2925 THEN
2926 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_NECESSITY_INVALID');
2927 FND_MESSAGE.Set_Token('POSREF', p_x_node_rec.position_ref_meaning);
2928 FND_MESSAGE.Set_Token('CODE', p_x_node_rec.position_necessity_code);
2929 FND_MSG_PUB.ADD;
2930 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
2931 THEN
2932 fnd_log.message
2933 (
2934 fnd_log.level_exception,
2935 'ahl.plsql.'||G_PKG_NAME||'.Validate_Node',
2936 false
2937 );
2938 END IF;
2939 END IF;
2940
2941 -- Validate item group
2942 p_x_node_rec.item_group_name := RTRIM(p_x_node_rec.item_group_name);
2943
2944 IF (p_x_node_rec.item_group_name IS NOT NULL)
2945 THEN
2946 OPEN get_item_group_id;
2947 FETCH get_item_group_id INTO p_x_node_rec.item_group_id, l_ig_type, l_ig_status;
2948 IF (get_item_group_id%NOTFOUND)
2949 THEN
2950 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_ITEMGRP_INVALID');
2951 FND_MESSAGE.Set_Token('ITEM_GRP', p_x_node_rec.item_group_name);
2952 FND_MSG_PUB.ADD;
2953 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
2954 THEN
2955 fnd_log.message
2956 (
2957 fnd_log.level_exception,
2958 'ahl.plsql.'||G_PKG_NAME||'.Validate_Node',
2959 false
2960 );
2961 END IF;
2962 ELSE
2963
2964 -- SATHAPLI::FP OGMA Issue# 105 - Non-Serialized Item Maintenance, 04-Dec-2007
2965 -- As non-serialized items having quantities greater than 1 can be associated to an item group now,
2966 -- this check should not be there to restrict them from being used in MC positions.
2967 /*
2968 -- Validate quantity = 1 for all item associations to the itemgroup
2969 OPEN check_item_assos_qty;
2970 FETCH check_item_assos_qty INTO l_dummy_varchar;
2971 IF (check_item_assos_qty%FOUND)
2972 THEN
2973 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_ITEM_ASSOS_QTY_INV');
2974 FND_MSG_PUB.ADD;
2975 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
2976 THEN
2977 fnd_log.message
2978 (
2979 fnd_log.level_exception,
2980 'ahl.plsql.'||G_PKG_NAME||'.Validate_Node',
2981 false
2982 );
2983 END IF;
2984 END IF;
2985 CLOSE check_item_assos_qty;
2986 */
2987
2988 -- Validate item group is trackable
2989 IF (l_ig_type <> 'TRACKED')
2990 THEN
2991 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_IG_NOT_TRACKED');
2992 FND_MESSAGE.Set_Token('IG_NAME', p_x_node_rec.item_group_name);
2993 FND_MSG_PUB.ADD;
2994 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
2995 THEN
2996 fnd_log.message
2997 (
2998 fnd_log.level_exception,
2999 'ahl.plsql.'||G_PKG_NAME||'.Validate_Node',
3000 false
3001 );
3002 END IF;
3003 END IF;
3004
3005 -- Validate itemgroup status is not REMOVED
3006 IF (l_ig_status = 'REMOVED')
3007 THEN
3008 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_IG_STS_INV');
3009 FND_MESSAGE.Set_Token('IG_NAME', p_x_node_rec.item_group_name);
3010 FND_MSG_PUB.ADD;
3011 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
3012 THEN
3013 fnd_log.message
3014 (
3015 fnd_log.level_exception,
3016 'ahl.plsql.'||G_PKG_NAME||'.Validate_Node',
3017 false
3018 );
3019 END IF;
3020 END IF;
3021 END IF;
3022 CLOSE get_item_group_id;
3023 ELSE
3024 -- Not a mandatory field, hence nullify ID
3025 p_x_node_rec.item_group_id := null;
3026 END IF;
3027
3028 -- Validate quantity
3029 IF (p_x_node_rec.quantity IS NULL)
3030 THEN
3031 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_QUANTITY_NULL');
3032 FND_MESSAGE.Set_Token('POSREF', p_x_node_rec.position_ref_meaning);
3033 FND_MSG_PUB.ADD;
3034 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
3035 THEN
3036 fnd_log.message
3037 (
3038 fnd_log.level_exception,
3039 'ahl.plsql.'||G_PKG_NAME||'.Validate_Node',
3040 false
3041 );
3042 END IF;
3043 ELSIF (p_x_node_rec.quantity <= 0)
3044 THEN
3045 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_QUANTITY_INVALID');
3046 FND_MESSAGE.Set_Token('QTY', p_x_node_rec.quantity);
3047 FND_MESSAGE.Set_Token('POSREF', p_x_node_rec.position_ref_meaning);
3048 FND_MSG_PUB.ADD;
3049 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
3050 THEN
3051 fnd_log.message
3052 (
3053 fnd_log.level_exception,
3054 'ahl.plsql.'||G_PKG_NAME||'.Validate_Node',
3055 false
3056 );
3057 END IF;
3058 ELSIF (p_x_node_rec.quantity > 1 AND p_x_node_rec.relationship_id IS NOT NULL)
3059 THEN
3060 OPEN check_child_exists;
3061 FETCH check_child_exists INTO l_dummy_varchar;
3062 IF (check_child_exists%FOUND)
3063 THEN
3064 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_PAR_QTY_INV');
3065 FND_MESSAGE.Set_Token('POSREF', p_x_node_rec.position_ref_meaning);
3066 FND_MSG_PUB.ADD;
3067 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
3068 THEN
3069 fnd_log.message
3070 (
3071 fnd_log.level_exception,
3072 'ahl.plsql.'||G_PKG_NAME||'.Validate_Node',
3073 false
3074 );
3075 END IF;
3076 END IF;
3077 CLOSE check_child_exists;
3078 END IF;
3079
3080 -- Validate display order
3081 IF (p_x_node_rec.display_order IS NULL)
3082 THEN
3083 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_DISPORD_NULL');
3084 FND_MESSAGE.Set_Token('POSREF', p_x_node_rec.position_ref_meaning);
3085 FND_MSG_PUB.ADD;
3086 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
3087 THEN
3088 fnd_log.message
3089 (
3090 fnd_log.level_exception,
3091 'ahl.plsql.'||G_PKG_NAME||'.Validate_Node',
3092 false
3093 );
3094 END IF;
3095 ELSIF (p_x_node_rec.display_order <= 0)
3096 THEN
3097 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_DISPORD_INVALID');
3098 FND_MESSAGE.Set_Token('DSP', p_x_node_rec.display_order);
3099 FND_MESSAGE.Set_Token('POSREF', p_x_node_rec.position_ref_meaning);
3100 FND_MSG_PUB.ADD;
3101 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
3102 THEN
3103 fnd_log.message
3104 (
3105 fnd_log.level_exception,
3106 'ahl.plsql.'||G_PKG_NAME||'.Validate_Node',
3107 false
3108 );
3109 END IF;
3110 ELSE
3111 -- Validate display_order is not equal to the same for any other node at the same level
3112 OPEN check_dup_display_order;
3113 FETCH check_dup_display_order INTO l_dummy_varchar;
3114 IF (check_dup_display_order%FOUND)
3115 THEN
3116 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_DISPORD_EXISTS');
3117 FND_MESSAGE.Set_Token('DSP', p_x_node_rec.display_order);
3118 FND_MESSAGE.Set_Token('POSREF', p_x_node_rec.position_ref_meaning);
3119 FND_MSG_PUB.ADD;
3120 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
3121 THEN
3122 fnd_log.message
3123 (
3124 fnd_log.level_exception,
3125 'ahl.plsql.'||G_PKG_NAME||'.Validate_Node',
3126 false
3127 );
3128 END IF;
3129 END IF;
3130 CLOSE check_dup_display_order;
3131 END IF;
3132
3133 -- Validate UOM
3134 IF (p_x_node_rec.uom_code IS NULL)
3135 THEN
3136 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_UOM_NULL');
3137 FND_MESSAGE.Set_Token('POSREF', p_x_node_rec.position_ref_meaning);
3138 FND_MSG_PUB.ADD;
3139 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
3140 THEN
3141 fnd_log.message
3142 (
3143 fnd_log.level_exception,
3144 'ahl.plsql.'||G_PKG_NAME||'.Validate_Node',
3145 false
3146 );
3147 END IF;
3148 ELSE
3149 OPEN check_uom_exists(p_x_node_rec.uom_code);
3150 FETCH check_uom_exists INTO l_dummy_varchar;
3151 IF (check_uom_exists%NOTFOUND)
3152 THEN
3153 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_UOM_INVALID');
3154 FND_MESSAGE.Set_Token('POSREF', p_x_node_rec.position_ref_meaning);
3155 FND_MESSAGE.Set_Token('UOM', p_x_node_rec.uom_code);
3156 FND_MSG_PUB.ADD;
3157 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
3158 THEN
3159 fnd_log.message
3160 (
3161 fnd_log.level_exception,
3162 'ahl.plsql.'||G_PKG_NAME||'.Validate_Node',
3163 false
3164 );
3165 END IF;
3166 END IF;
3167 CLOSE check_uom_exists;
3168 END IF;
3169
3170 -- Validate dates
3171 -- ##TAMAL## Date validations may fail in the case of creating a node initiated from a copy_node / copy_mc /
3172 -- create_mc_revision call since expired nodes are to be created, thus do not perform date validations for
3173 -- such a case. For any such copy operation, the p_x_node_rec.operation_flag = G_DML_COPY instead of
3174 -- G_DML_CREATE, and thus date validations may be avoided for such a case.
3175 IF (p_x_node_rec.operation_flag = G_DML_UPDATE)
3176 THEN
3177 OPEN get_node_dates;
3178 FETCH get_node_dates INTO l_start_date, l_end_date;
3179 CLOSE get_node_dates;
3180
3181 IF (G_TRUNC_DATE >= trunc(nvl(l_end_date, G_SYSDATE + 1)))
3182 THEN
3183 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_NODE_DATE_INV');
3184 FND_MSG_PUB.ADD;
3185 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
3186 THEN
3187 fnd_log.message
3188 (
3189 fnd_log.level_exception,
3190 'ahl.plsql.'||G_PKG_NAME||'.Validate_Node',
3191 false
3192 );
3193 END IF;
3194 END IF;
3195
3196 IF (p_x_node_rec.active_start_date IS NOT NULL AND trunc(nvl(l_start_date, G_SYSDATE)) <> trunc(p_x_node_rec.active_start_date) AND trunc(p_x_node_rec.active_start_date) < G_TRUNC_DATE)
3197 THEN
3198 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_START_DATE_INV');
3199 FND_MSG_PUB.ADD;
3200 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
3201 THEN
3202 fnd_log.message
3203 (
3204 fnd_log.level_exception,
3205 'ahl.plsql.'||G_PKG_NAME||'.Validate_Node',
3206 false
3207 );
3208 END IF;
3209 END IF;
3210
3211 IF (trunc(nvl(l_end_date, G_SYSDATE)) <> trunc(nvl(p_x_node_rec.active_end_date, G_SYSDATE)) AND trunc(nvl(p_x_node_rec.active_end_date, G_SYSDATE + 1)) <= G_TRUNC_DATE)
3212 THEN
3213 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_END_DATE_INV');
3214 FND_MSG_PUB.ADD;
3215 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
3216 THEN
3217 fnd_log.message
3218 (
3219 fnd_log.level_exception,
3220 'ahl.plsql.'||G_PKG_NAME||'.Validate_Node',
3221 false
3222 );
3223 END IF;
3224 END IF;
3225 ELSIF (p_x_node_rec.operation_flag = G_DML_CREATE)
3226 THEN
3227 IF (trunc(nvl(p_x_node_rec.active_start_date, G_SYSDATE)) < G_TRUNC_DATE)
3228 THEN
3229 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_START_DATE_INV');
3230 FND_MSG_PUB.ADD;
3231 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
3232 THEN
3233 fnd_log.message
3234 (
3235 fnd_log.level_exception,
3236 'ahl.plsql.'||G_PKG_NAME||'.Validate_Node',
3237 false
3238 );
3239 END IF;
3240 END IF;
3241
3242 IF (trunc(nvl(p_x_node_rec.active_end_date, G_SYSDATE + 1)) <= G_TRUNC_DATE)
3243 THEN
3244 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_END_DATE_INV');
3245 FND_MSG_PUB.ADD;
3246 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
3247 THEN
3248 fnd_log.message
3249 (
3250 fnd_log.level_exception,
3251 'ahl.plsql.'||G_PKG_NAME||'.Validate_Node',
3252 false
3253 );
3254 END IF;
3255 END IF;
3256 END IF;
3257
3258 IF (p_x_node_rec.operation_flag <> G_DML_COPY AND trunc(nvl(p_x_node_rec.active_end_date, nvl(p_x_node_rec.active_start_date, G_SYSDATE) + 1)) <= trunc(nvl(p_x_node_rec.active_start_date, G_SYSDATE)))
3259 THEN
3260 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_START_END_INV');
3261 FND_MSG_PUB.ADD;
3262 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
3263 THEN
3264 fnd_log.message
3265 (
3266 fnd_log.level_exception,
3267 'ahl.plsql.'||G_PKG_NAME||'.Validate_Node',
3268 false
3269 );
3270 END IF;
3271 END IF;
3272
3273 END Validate_Node;
3274
3275 PROCEDURE Validate_Counter_Exists
3276 (
3277 p_ctr_rule_id in number,
3278 p_object_ver_num in number
3279 )
3280 IS
3281
3282 CURSOR check_counter_exists
3283 IS
3284 SELECT object_version_number
3285 FROM ahl_ctr_update_rules
3286 WHERE ctr_update_rule_id = p_ctr_rule_id;
3287
3288 BEGIN
3289
3290 OPEN check_counter_exists;
3291 FETCH check_counter_exists INTO l_dummy_number;
3292 IF (check_counter_exists%NOTFOUND)
3293 THEN
3294 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_CTR_NOT_FOUND');
3295 FND_MSG_PUB.ADD;
3296 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
3297 THEN
3298 fnd_log.message
3299 (
3300 fnd_log.level_exception,
3301 'ahl.plsql.'||G_PKG_NAME||'.Validate_Counter_Exists',
3302 false
3303 );
3304 END IF;
3305 CLOSE check_counter_exists;
3306 RAISE FND_API.G_EXC_ERROR;
3307 ELSIF (NVL(p_object_ver_num, l_dummy_number) <> l_dummy_number)
3308 THEN
3309 FND_MESSAGE.Set_Name('AHL', 'AHL_COM_RECORD_CHANGED');
3310 FND_MSG_PUB.ADD;
3311 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
3312 THEN
3313 fnd_log.message
3314 (
3315 fnd_log.level_exception,
3316 'ahl.plsql.'||G_PKG_NAME||'.Validate_Counter_Exists',
3317 false
3318 );
3319 END IF;
3320 CLOSE check_counter_exists;
3321 RAISE FND_API.G_EXC_ERROR;
3322 END IF;
3323 CLOSE check_counter_exists;
3324
3325 END Validate_Counter_Exists;
3326
3327 PROCEDURE Validate_Counter_Rule
3328 (
3329 p_counter_rule_rec in Counter_Rule_Rec_Type
3330 )
3331 IS
3332 -- Define cursor get_node_posref to read the position reference of the MC node, used for displaying errors
3333 CURSOR get_node_posref
3334 IS
3335 SELECT position_ref_meaning
3336 FROM ahl_mc_relationships_v
3337 WHERE relationship_id = p_counter_rule_rec.relationship_id;
3338
3339 -- Define cursor check_uom_rule to check whether the same combination of UOM and rule
3340 -- already exists for the node or not
3341 CURSOR check_uom_rule
3342 IS
3343 SELECT 'x'
3344 FROM ahl_ctr_update_rules
3345 WHERE relationship_id = p_counter_rule_rec.relationship_id AND
3346 rule_code = p_counter_rule_rec.rule_code AND
3347 uom_code = p_counter_rule_rec.uom_code AND
3348 ctr_update_rule_id <> nvl(p_counter_rule_rec.ctr_update_rule_id, -1);
3349
3350 -- Declare local variables
3351 l_posref_meaning VARCHAR2(80);
3352
3353 BEGIN
3354 OPEN get_node_posref;
3355 FETCH get_node_posref INTO l_posref_meaning;
3356 CLOSE get_node_posref;
3357
3358 -- Validate p_counter_rule_rec.uom_code
3359 IF (p_counter_rule_rec.uom_code IS NULL)
3360 THEN
3361 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_UOM_NULL');
3362 FND_MESSAGE.Set_Token('POSREF', l_posref_meaning);
3363 FND_MSG_PUB.ADD;
3364 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
3365 THEN
3366 fnd_log.message
3367 (
3368 fnd_log.level_exception,
3369 'ahl.plsql.'||G_PKG_NAME||'.Validate_Counter_Rule',
3370 false
3371 );
3372 END IF;
3373 ELSE
3374 OPEN check_uom_exists(p_counter_rule_rec.uom_code);
3375 FETCH check_uom_exists INTO l_dummy_varchar;
3376 IF (check_uom_exists%NOTFOUND)
3377 THEN
3378 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_UOM_INVALID');
3379 FND_MESSAGE.Set_Token('POSREF', l_posref_meaning);
3380 FND_MESSAGE.Set_Token('UOM', p_counter_rule_rec.uom_code);
3381 FND_MSG_PUB.ADD;
3382 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
3383 THEN
3384 fnd_log.message
3385 (
3386 fnd_log.level_exception,
3387 'ahl.plsql.'||G_PKG_NAME||'.Validate_Counter_Rule',
3388 false
3389 );
3390 END IF;
3391 END IF;
3392 CLOSE check_uom_exists;
3393 END IF;
3394
3395 -- Validate p_counter_rule_rec.rule_code
3396 IF (p_counter_rule_rec.rule_code IS NULL)
3397 THEN
3398 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_RCODE_NULL');
3399 FND_MESSAGE.Set_Token('POSREF', l_posref_meaning);
3400 FND_MSG_PUB.ADD;
3401 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
3402 THEN
3403 fnd_log.message
3404 (
3405 fnd_log.level_exception,
3406 'ahl.plsql.'||G_PKG_NAME||'.Validate_Counter_Rule',
3407 false
3408 );
3409 END IF;
3410 ELSIF NOT(AHL_UTIL_MC_PKG.Validate_Lookup_Code('AHL_COUNTER_RULE_TYPE', p_counter_rule_rec.rule_code))
3411 THEN
3412 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_RCODE_INVALID');
3413 FND_MESSAGE.Set_Token('POSREF', l_posref_meaning);
3414 FND_MESSAGE.Set_Token('RULE_CODE', p_counter_rule_rec.rule_meaning);
3415 FND_MSG_PUB.ADD;
3416 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
3417 THEN
3418 fnd_log.message
3419 (
3420 fnd_log.level_exception,
3421 'ahl.plsql.'||G_PKG_NAME||'.Validate_Counter_Rule',
3422 false
3423 );
3424 END IF;
3425 END IF;
3426
3427 -- Validate whether the same combination of UOM and Rule does not exist for this node
3428 OPEN check_uom_rule;
3429 FETCH check_uom_rule INTO l_dummy_varchar;
3430 IF (check_uom_rule%FOUND)
3431 THEN
3432 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_CTRRULE_EXISTS');
3433 FND_MESSAGE.Set_Token('POSREF', l_posref_meaning);
3434 FND_MESSAGE.Set_Token('UOM', p_counter_rule_rec.uom_code);
3435 FND_MESSAGE.Set_Token('RULE', p_counter_rule_rec.rule_code);
3436 FND_MSG_PUB.ADD;
3437 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
3438 THEN
3439 fnd_log.message
3440 (
3441 fnd_log.level_exception,
3442 'ahl.plsql.'||G_PKG_NAME||'.Validate_Counter_Rule',
3443 false
3444 );
3445 END IF;
3446 END IF;
3447 CLOSE check_uom_rule;
3448
3449 -- Validate counter rule ratio is a positive number
3450 IF (nvl(p_counter_rule_rec.ratio, 0) <= 0)
3451 THEN
3452 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_RATIO_INVALID');
3453 FND_MESSAGE.Set_Token('POSREF', l_posref_meaning);
3454 FND_MESSAGE.Set_Token('RATIO', p_counter_rule_rec.ratio);
3455 FND_MSG_PUB.ADD;
3456 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
3457 THEN
3458 fnd_log.message
3459 (
3460 fnd_log.level_exception,
3461 'ahl.plsql.'||G_PKG_NAME||'.Validate_Counter_Rule',
3462 false
3463 );
3464 END IF;
3465 END IF;
3466
3467 END Validate_Counter_Rule;
3468
3469 PROCEDURE Validate_Subconfig_Exists
3470 (
3471 p_submc_assos_id in number,
3472 p_object_ver_num in number
3473 )
3474 IS
3475
3476 CURSOR check_submc_exists
3477 IS
3478 SELECT object_version_number
3479 FROM ahl_mc_config_relations
3480 WHERE mc_config_relation_id = p_submc_assos_id;
3481
3482 BEGIN
3483
3484 OPEN check_submc_exists;
3485 FETCH check_submc_exists INTO l_dummy_number;
3486 IF (check_submc_exists%NOTFOUND)
3487 THEN
3488 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_SUBMC_NOT_FOUND');
3489 FND_MSG_PUB.ADD;
3490 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
3491 THEN
3492 fnd_log.message
3493 (
3494 fnd_log.level_exception,
3495 'ahl.plsql.'||G_PKG_NAME||'.Validate_Subconfig_Exists',
3496 false
3497 );
3498 END IF;
3499 CLOSE check_submc_exists;
3500 RAISE FND_API.G_EXC_ERROR;
3501 ELSIF (NVL(p_object_ver_num, l_dummy_number) <> l_dummy_number)
3502 THEN
3503 FND_MESSAGE.Set_Name('AHL', 'AHL_COM_RECORD_CHANGED');
3504 FND_MSG_PUB.ADD;
3505 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
3506 THEN
3507 fnd_log.message
3508 (
3509 fnd_log.level_exception,
3510 'ahl.plsql.'||G_PKG_NAME||'.Validate_Subconfig_Exists',
3511 false
3512 );
3513 END IF;
3514 CLOSE check_submc_exists;
3515 RAISE FND_API.G_EXC_ERROR;
3516 END IF;
3517 CLOSE check_submc_exists;
3518
3519 END Validate_Subconfig_Exists;
3520
3521
3522 PROCEDURE Validate_priority
3523 (
3524 p_subconfig_tbl in Subconfig_Tbl_Type
3525 ) IS
3526
3527 CURSOR check_priority_dup_exists
3528 IS
3529 SELECT priority
3530 FROM ahl_mc_config_relations
3531 WHERE relationship_id = p_subconfig_tbl(1).relationship_id
3532 group by priority
3533 having count(mc_config_relation_id) > 1;
3534
3535 l_priority NUMBER;
3536
3537 BEGIN
3538
3539
3540 OPEN check_priority_dup_exists;
3541 FETCH check_priority_dup_exists INTO l_priority;
3542 IF (check_priority_dup_exists%FOUND)
3543 THEN
3544 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_PRIORITY_NON_UNIQUE');
3545 FND_MSG_PUB.ADD;
3546 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
3547 THEN
3548 fnd_log.message
3549 (
3550 fnd_log.level_exception,
3551 'ahl.plsql.'||G_PKG_NAME||'.Validate_priority',
3552 true
3553 );
3554 END IF;
3555 CLOSE check_priority_dup_exists;
3556 RAISE FND_API.G_EXC_ERROR;
3557 END IF;
3558
3559 END Validate_priority;
3560
3561 /* Jerry commented out on 08/12/2004 because it is never used
3562 PROCEDURE Check_Cyclic_Rel
3563 (
3564 p_subconfig_id in number,
3565 p_rel_id in number
3566 )
3567 IS
3568
3569 -- Define cursor check_cyclic_rel_csr to establish a parent-child relationship between the MC in question and
3570 -- subconfigs associated with its nodes (down to the last level), then search for the subconfig in question
3571 -- from that list
3572 CURSOR check_cyclic_rel_csr
3573 IS
3574 SELECT 'x'
3575 FROM
3576 (
3577 -- Establish parent-child relationship between subconfiguration associations
3578 -- and the MC to which they are associated
3579 SELECT submc.mc_header_id child, node.mc_header_id parent
3580 FROM ahl_mc_config_relations submc, ahl_mc_relationships node
3581 WHERE submc.relationship_id = node.relationship_id
3582 CONNECT BY node.mc_header_id = PRIOR submc.mc_header_id
3583 START WITH node.mc_header_id = p_subconfig_id
3584 ) submc_tree, ahl_mc_relationships mc_node
3585 WHERE submc_tree.child = mc_node.mc_header_id AND
3586 mc_node.relationship_id = p_rel_id;
3587
3588 -- Define cursor get_node_mc_details to read detail of the MC of a MC node
3589 CURSOR get_node_mc_details
3590 IS
3591 SELECT mch.name
3592 FROM ahl_mc_headers_b mch, ahl_mc_relationships mcr
3593 WHERE mch.mc_header_id = mcr.mc_header_id AND
3594 mcr.relationship_id = p_rel_id;
3595
3596 -- Define cursor get_mc_details to read detail of a MC
3597 CURSOR get_mc_details
3598 IS
3599 SELECT name
3600 FROM ahl_mc_headers_b
3601 WHERE mc_header_id = p_subconfig_id;
3602
3603 -- Define local variables
3604 l_mc_name VARCHAR2(80);
3605 l_submc_name VARCHAR2(80);
3606
3607 BEGIN
3608 OPEN check_cyclic_rel_csr;
3609 FETCH check_cyclic_rel_csr INTO l_dummy_varchar;
3610 IF (check_cyclic_rel_csr%FOUND)
3611 THEN
3612 OPEN get_node_mc_details;
3613 FETCH get_node_mc_details INTO l_mc_name;
3614 CLOSE get_node_mc_details;
3615
3616 OPEN get_mc_details;
3617 FETCH get_mc_details INTO l_submc_name;
3618 CLOSE get_mc_details;
3619
3620 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_CYCLIC_REL_EXIST');
3621 FND_MESSAGE.Set_Token('MC', l_mc_name);
3622 FND_MESSAGE.Set_Token('SUBMC', l_submc_name);
3623 FND_MSG_PUB.ADD;
3624 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
3625 THEN
3626 fnd_log.message
3627 (
3628 fnd_log.level_exception,
3629 'ahl.plsql.'||G_PKG_NAME||'.Check_Cyclic_Rel',
3630 false
3631 );
3632 END IF;
3633 END IF;
3634 CLOSE check_cyclic_rel_csr;
3635
3636 END Check_Cyclic_Rel;
3637 */
3638
3639 -------------------------
3640 -- Non-spec Procedures --
3641 -------------------------
3642 FUNCTION Get_MC_Status
3643 (
3644 p_rel_id in number,
3645 p_mc_header_id in number
3646 )
3647 RETURN VARCHAR2
3648 IS
3649 CURSOR get_mc_status
3650 IS
3651 SELECT config_status_code
3652 FROM ahl_mc_headers_v
3653 WHERE mc_header_id = p_mc_header_id;
3654
3655 CURSOR get_node_mc_status
3656 IS
3657 SELECT mch.config_status_code
3658 FROM ahl_mc_headers_v mch, ahl_mc_relationships mcr
3659 WHERE mch.mc_header_id = mcr.mc_header_id AND
3660 mcr.relationship_id = p_rel_id;
3661
3662 l_status VARCHAR2(30);
3663
3664 BEGIN
3665
3666 IF (p_rel_id IS NOT NULL)
3667 THEN
3668 OPEN get_node_mc_status;
3669 FETCH get_node_mc_status INTO l_status;
3670 IF (get_node_mc_status%NOTFOUND)
3671 THEN
3672 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_NOT_FOUND');
3673 FND_MSG_PUB.ADD;
3674 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
3675 THEN
3676 fnd_log.message
3677 (
3678 fnd_log.level_exception,
3679 'ahl.plsql.'||G_PKG_NAME||'.Get_MC_Status',
3680 false
3681 );
3682 END IF;
3683 CLOSE get_node_mc_status;
3684 RAISE FND_API.G_EXC_ERROR;
3685 END IF;
3686 CLOSE get_node_mc_status;
3687 ELSIF (p_mc_header_id IS NOT NULL)
3688 THEN
3689 OPEN get_mc_status;
3690 FETCH get_mc_status INTO l_status;
3691 IF (get_mc_status%NOTFOUND)
3692 THEN
3693 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_NOT_FOUND');
3694 FND_MSG_PUB.ADD;
3695 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
3696 THEN
3697 fnd_log.message
3698 (
3699 fnd_log.level_exception,
3700 'ahl.plsql.'||G_PKG_NAME||'.Get_MC_Status',
3701 false
3702 );
3703 END IF;
3704 CLOSE get_mc_status;
3705 RAISE FND_API.G_EXC_ERROR;
3706 END IF;
3707 CLOSE get_mc_status;
3708 END IF;
3709
3710 return l_status;
3711
3712 END Get_MC_Status;
3713 -- Returns true if the MC , p_subconfig_id contains the MC p_dest_config_id, as
3714 -- a subconfig some where down the tree.
3715 FUNCTION Cyclic_Relation_Exists
3716 (
3717 p_subconfig_id in number,
3718 p_dest_config_id in number
3719 )
3720 RETURN BOOLEAN
3721 IS
3722 -- Define local variables
3723 TYPE subconfig_Tbl_Type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
3724 l_subconfigs_table subconfig_Tbl_Type;
3725 l_dummy_varchar VARCHAR2(1);
3726 -- cursor to check whether the Cyclic relation really exist.
3727 CURSOR CHECK_RELATIONS_CYCLE
3728 IS
3729 SELECT 'X' FROM ahl_mc_config_relations
3730 WHERE mc_header_id = p_dest_config_id
3731 AND relationship_id IN
3732 ( SELECT relationship_id from ahl_mc_relationships
3733 WHERE mc_header_id = p_subconfig_id
3734 START WITH parent_relationship_id IS NULL
3735 CONNECT BY parent_relationship_id = prior relationship_id);
3736
3737 BEGIN
3738 -- check whether cycle is there
3739 OPEN CHECK_RELATIONS_CYCLE;
3740 FETCH CHECK_RELATIONS_CYCLE INTO l_dummy_varchar;
3741 IF (CHECK_RELATIONS_CYCLE%FOUND) THEN
3742 CLOSE CHECK_RELATIONS_CYCLE;
3743 -- Cycle is found
3744 RETURN TRUE;
3745 ELSE
3746 CLOSE CHECK_RELATIONS_CYCLE;
3747 -- get the next level of subconfigs
3748 SELECT mc_header_id bulk collect
3749 INTO l_subconfigs_table
3750 FROM ahl_mc_config_relations WHERE relationship_id IN
3751 ( SELECT relationship_id FROM ahl_mc_relationships
3752 WHERE mc_header_id = p_subconfig_id
3753 START WITH parent_relationship_id IS NULL
3754 CONNECT BY parent_relationship_id = prior relationship_id );
3755 IF ( l_subconfigs_table.COUNT > 0 ) THEN
3756 FOR i IN l_subconfigs_table.FIRST..l_subconfigs_table.LAST LOOP
3757 IF Cyclic_Relation_Exists(l_subconfigs_table(i),p_dest_config_id) THEN
3758 RETURN TRUE;
3759 END IF;
3760 END LOOP;
3761 END IF;
3762 END IF;
3763 RETURN FALSE;
3764 END Cyclic_Relation_Exists;
3765
3766 PROCEDURE Set_Header_Status
3767 (
3768 p_rel_id IN NUMBER
3769 )
3770 IS
3771
3772 CURSOR get_mc_header_status
3773 (
3774 p_rel_id in number
3775 )
3776 IS
3777 SELECT mch.mc_header_id, mch.config_status_code
3778 FROM ahl_mc_headers_b mch, ahl_mc_relationships mcr
3779 WHERE mch.mc_header_id = mcr.mc_header_id AND
3780 mcr.relationship_id = p_rel_id;
3781
3782 l_mc_header_id NUMBER;
3783 l_status VARCHAR2(30) := 'DRAFT';
3784
3785 BEGIN
3786
3787 OPEN get_mc_header_status(p_rel_id);
3788 FETCH get_mc_header_status INTO l_mc_header_id, l_status;
3789
3790 IF (get_mc_header_status%FOUND)
3791 THEN
3792 IF (l_status = 'APPROVAL_REJECTED')
3793 THEN
3794 UPDATE ahl_mc_headers_b
3795 SET config_status_code = 'DRAFT'
3796 WHERE mc_header_id = l_mc_header_id;
3797 END IF;
3798 END IF;
3799
3800 CLOSE get_mc_header_status;
3801
3802 END Set_Header_Status;
3803
3804 PROCEDURE Create_Counter_Rule
3805 (
3806 p_x_counter_rule_rec IN OUT NOCOPY Counter_Rule_Rec_Type
3807 )
3808 IS
3809 -- Define local variables
3810 l_api_name CONSTANT VARCHAR2(30) := 'Create_Counter_Rule';
3811 l_msg_count NUMBER;
3812
3813 l_posref_meaning VARCHAR2(80);
3814
3815 BEGIN
3816
3817 -- API body starts here
3818 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
3819 THEN
3820 fnd_log.string
3821 (
3822 fnd_log.level_procedure,
3823 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
3824 'At the start of PLSQL procedure'
3825 );
3826 END IF;
3827
3828 -- Validate p_x_counter_rule_rec.relationship_id exists
3829 Validate_Node_Exists(p_x_counter_rule_rec.relationship_id, null);
3830
3831 -- Validate UOM, Rule and Ratio for the counter rule
3832 Validate_Counter_Rule(p_x_counter_rule_rec);
3833
3834 -- Check Error Message stack.
3835 l_msg_count := FND_MSG_PUB.count_msg;
3836 IF l_msg_count > 0 THEN
3837 RAISE FND_API.G_EXC_ERROR;
3838 END IF;
3839
3840 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
3841 THEN
3842 fnd_log.string
3843 (
3844 fnd_log.level_statement,
3845 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
3846 'Counter rule validation successful'
3847 );
3848 END IF;
3849
3850 SELECT ahl_ctr_update_rules_s.nextval INTO p_x_counter_rule_rec.ctr_update_rule_id FROM DUAL;
3851 p_x_counter_rule_rec.object_version_number := 1;
3852 p_x_counter_rule_rec.security_group_id := null;
3853
3854 INSERT INTO AHL_CTR_UPDATE_RULES
3855 (
3856 CTR_UPDATE_RULE_ID,
3857 RELATIONSHIP_ID,
3858 UOM_CODE,
3859 RULE_CODE,
3860 RATIO,
3861 OBJECT_VERSION_NUMBER,
3862 SECURITY_GROUP_ID,
3863 ATTRIBUTE_CATEGORY,
3864 ATTRIBUTE1,
3865 ATTRIBUTE2,
3866 ATTRIBUTE3,
3867 ATTRIBUTE4,
3868 ATTRIBUTE5,
3869 ATTRIBUTE6,
3870 ATTRIBUTE7,
3871 ATTRIBUTE8,
3872 ATTRIBUTE9,
3873 ATTRIBUTE10,
3874 ATTRIBUTE11,
3875 ATTRIBUTE12,
3876 ATTRIBUTE13,
3877 ATTRIBUTE14,
3878 ATTRIBUTE15,
3879 LAST_UPDATE_DATE,
3880 LAST_UPDATED_BY,
3881 CREATION_DATE,
3882 CREATED_BY,
3883 LAST_UPDATE_LOGIN
3884 )
3885 VALUES
3886 (
3887 p_x_counter_rule_rec.ctr_update_rule_id,
3888 p_x_counter_rule_rec.relationship_id,
3889 p_x_counter_rule_rec.uom_code,
3890 p_x_counter_rule_rec.rule_code,
3891 p_x_counter_rule_rec.ratio,
3892 p_x_counter_rule_rec.object_version_number,
3893 p_x_counter_rule_rec.security_group_id,
3894 p_x_counter_rule_rec.ATTRIBUTE_CATEGORY,
3895 p_x_counter_rule_rec.ATTRIBUTE1,
3896 p_x_counter_rule_rec.ATTRIBUTE2,
3897 p_x_counter_rule_rec.ATTRIBUTE3,
3898 p_x_counter_rule_rec.ATTRIBUTE4,
3899 p_x_counter_rule_rec.ATTRIBUTE5,
3900 p_x_counter_rule_rec.ATTRIBUTE6,
3901 p_x_counter_rule_rec.ATTRIBUTE7,
3902 p_x_counter_rule_rec.ATTRIBUTE8,
3903 p_x_counter_rule_rec.ATTRIBUTE9,
3904 p_x_counter_rule_rec.ATTRIBUTE10,
3905 p_x_counter_rule_rec.ATTRIBUTE11,
3906 p_x_counter_rule_rec.ATTRIBUTE12,
3907 p_x_counter_rule_rec.ATTRIBUTE13,
3908 p_x_counter_rule_rec.ATTRIBUTE14,
3909 p_x_counter_rule_rec.ATTRIBUTE15,
3910 G_SYSDATE,
3911 G_USER_ID,
3912 G_SYSDATE,
3913 G_USER_ID,
3914 G_LOGIN_ID
3915 );
3916
3917 -- API body ends here
3918
3919 END Create_Counter_Rule;
3920
3921 PROCEDURE Modify_Counter_Rule
3922 (
3923 p_x_counter_rule_rec IN OUT NOCOPY Counter_Rule_Rec_Type
3924 )
3925 IS
3926
3927 -- Define cursor get_node_posref to read the position reference of the MC node, used for displaying errors
3928 CURSOR get_node_posref
3929 IS
3930 SELECT position_ref_meaning
3931 FROM ahl_mc_relationships_v
3932 WHERE relationship_id = p_x_counter_rule_rec.relationship_id;
3933
3934 -- Define cursor check_uom_rule to check whether the same combination of UOM and rule
3935 -- already exists for the node or not
3936 CURSOR check_uom_rule
3937 IS
3938 SELECT 'x'
3939 FROM ahl_ctr_update_rules
3940 WHERE relationship_id = p_x_counter_rule_rec.relationship_id AND
3941 rule_code = p_x_counter_rule_rec.rule_code AND
3942 uom_code = p_x_counter_rule_rec.uom_code AND
3943 ctr_update_rule_id <> p_x_counter_rule_rec.ctr_update_rule_id;
3944
3945 -- Define local variables
3946 l_api_name CONSTANT VARCHAR2(30) := 'Modify_Counter_Rule';
3947 l_msg_count NUMBER;
3948
3949 l_posref_meaning VARCHAR2(80);
3950
3951 BEGIN
3952
3953 -- API body starts here
3954 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
3955 THEN
3956 fnd_log.string
3957 (
3958 fnd_log.level_procedure,
3959 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||'.begin',
3960 'At the start of PLSQL procedure'
3961 );
3962 END IF;
3963
3964 -- Validate p_x_counter_rule_rec.relationship_id exists
3965 Validate_Node_Exists(p_x_counter_rule_rec.relationship_id, null);
3966
3967 -- Validate p_x_counter_rule_rec.ctr_update_rule_id exists
3968 Validate_Counter_Exists(p_x_counter_rule_rec.ctr_update_rule_id, nvl(p_x_counter_rule_rec.object_version_number, 0));
3969
3970 -- Validate UOM, Rule and Ratio for the counter rule
3971 Validate_Counter_Rule(p_x_counter_rule_rec);
3972
3973 -- Check Error Message stack.
3974 l_msg_count := FND_MSG_PUB.count_msg;
3975 IF l_msg_count > 0 THEN
3976 RAISE FND_API.G_EXC_ERROR;
3977 END IF;
3978
3979 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
3980 THEN
3981 fnd_log.string
3982 (
3983 fnd_log.level_statement,
3984 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
3985 'Counter rule validation successful'
3986 );
3987 END IF;
3988
3989 p_x_counter_rule_rec.object_version_number := p_x_counter_rule_rec.object_version_number + 1;
3990
3991 UPDATE AHL_CTR_UPDATE_RULES
3992 SET RATIO = p_x_counter_rule_rec.RATIO,
3993 RULE_CODE = p_x_counter_rule_rec.RULE_CODE,
3994 OBJECT_VERSION_NUMBER = p_x_counter_rule_rec.OBJECT_VERSION_NUMBER,
3995 SECURITY_GROUP_ID = p_x_counter_rule_rec.SECURITY_GROUP_ID,
3996 ATTRIBUTE_CATEGORY = p_x_counter_rule_rec.ATTRIBUTE_CATEGORY,
3997 ATTRIBUTE1 = p_x_counter_rule_rec.ATTRIBUTE1,
3998 ATTRIBUTE2 = p_x_counter_rule_rec.ATTRIBUTE2,
3999 ATTRIBUTE3 = p_x_counter_rule_rec.ATTRIBUTE3,
4000 ATTRIBUTE4 = p_x_counter_rule_rec.ATTRIBUTE4,
4001 ATTRIBUTE5 = p_x_counter_rule_rec.ATTRIBUTE5,
4002 ATTRIBUTE6 = p_x_counter_rule_rec.ATTRIBUTE6,
4003 ATTRIBUTE7 = p_x_counter_rule_rec.ATTRIBUTE7,
4004 ATTRIBUTE8 = p_x_counter_rule_rec.ATTRIBUTE8,
4005 ATTRIBUTE9 = p_x_counter_rule_rec.ATTRIBUTE9,
4006 ATTRIBUTE10 = p_x_counter_rule_rec.ATTRIBUTE10,
4007 ATTRIBUTE11 = p_x_counter_rule_rec.ATTRIBUTE11,
4008 ATTRIBUTE12 = p_x_counter_rule_rec.ATTRIBUTE12,
4009 ATTRIBUTE13 = p_x_counter_rule_rec.ATTRIBUTE13,
4010 ATTRIBUTE14 = p_x_counter_rule_rec.ATTRIBUTE14,
4011 ATTRIBUTE15 = p_x_counter_rule_rec.ATTRIBUTE15,
4012 LAST_UPDATE_DATE = G_SYSDATE,
4013 LAST_UPDATED_BY = G_USER_ID,
4014 LAST_UPDATE_LOGIN = G_LOGIN_ID
4015 WHERE CTR_UPDATE_RULE_ID = p_x_counter_rule_rec.CTR_UPDATE_RULE_ID;
4016
4017 -- API body ends here
4018
4019 END Modify_Counter_Rule;
4020
4021 PROCEDURE Delete_Counter_Rule
4022 (
4023 p_ctr_update_rule_id IN NUMBER,
4024 p_object_ver_num IN NUMBER
4025 )
4026 IS
4027
4028 -- Declare local variables
4029 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Counter_Rule';
4030
4031 BEGIN
4032
4033 -- API body starts here
4034 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
4035 THEN
4036 fnd_log.string
4037 (
4038 fnd_log.level_procedure,
4039 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||'.begin',
4040 'At the start of PLSQL procedure'
4041 );
4042 END IF;
4043
4044 -- Validate p_ctr_update_rule_id exists
4045 Validate_Counter_Exists(p_ctr_update_rule_id, nvl(p_object_ver_num, 0));
4046
4047 DELETE FROM ahl_ctr_update_rules
4048 WHERE ctr_update_rule_id = p_ctr_update_rule_id;
4049
4050 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
4051 THEN
4052 fnd_log.string
4053 (
4054 fnd_log.level_procedure,
4055 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||'.end',
4056 'At the end of PLSQL procedure'
4057 );
4058 END IF;
4059 -- API body ends here
4060
4061 END Delete_Counter_Rule;
4062
4063 PROCEDURE Attach_Subconfig
4064 (
4065 p_x_subconfig_rec IN OUT NOCOPY Subconfig_Rec_Type
4066 )
4067 IS
4068 -- Define cursor check_submc_exists to check whether the subconfiguration association already exists for this node
4069 CURSOR check_submc_exists
4070 IS
4071 SELECT name
4072 FROM ahl_mc_config_relations submc, ahl_mc_headers_b mch
4073 WHERE submc.mc_header_id = mch.mc_header_id AND
4074 submc.relationship_id = p_x_subconfig_rec.relationship_id AND
4075 submc.mc_header_id = p_x_subconfig_rec.mc_header_id;
4076 -- Since expired subconfig associations can be unexpired, so no need to filter on active_end_date
4077 -- AND G_TRUNC_DATE < trunc(nvl(submc.active_end_date, G_SYSDATE + 1));
4078
4079 -- Define check_root_node to check whether the node to which subconfiguration is being associated is not a topnode of a MC
4080 CURSOR check_root_node
4081 IS
4082 SELECT 'x'
4083 FROM ahl_mc_relationships
4084 WHERE parent_relationship_id is null AND
4085 relationship_id = p_x_subconfig_rec.relationship_id;
4086
4087 -- Define check_leaf_node to check whether the node to which subconfiguration is being associated is a leaf node
4088 CURSOR check_leaf_node
4089 IS
4090 SELECT 'x'
4091 FROM ahl_mc_relationships
4092 WHERE parent_relationship_id = p_x_subconfig_rec.relationship_id AND
4093 G_TRUNC_DATE < trunc(nvl(active_end_date, G_SYSDATE + 1));
4094
4095 -- Define a cursor to get the MC header id , when a relationship id is given
4096 CURSOR get_dest_header_id(p_dest_rel_id in number)
4097 IS
4098 SELECT mc_header_id
4099 FROM ahl_mc_relationships
4100 WHERE relationship_id = p_dest_rel_id;
4101 -- Define cursor get_node_mc_details to read detail of the MC of a MC node
4102 CURSOR get_node_mc_details(p_dest_rel_id in number)
4103 IS
4104 SELECT mch.name
4105 FROM ahl_mc_headers_b mch, ahl_mc_relationships mcr
4106 WHERE mch.mc_header_id = mcr.mc_header_id AND
4107 mcr.relationship_id = p_dest_rel_id;
4108 -- Define cursor get_mc_details to read detail of a MC
4109 CURSOR get_mc_details(p_subconfig_id in number )
4110 IS
4111 SELECT name
4112 FROM ahl_mc_headers_b
4113 WHERE mc_header_id = p_subconfig_id;
4114 -- Declare local variables
4115 l_api_name CONSTANT VARCHAR2(30) := 'Attach_Subconfig';
4116 l_msg_count NUMBER;
4117
4118 l_header_status VARCHAR2(30);
4119 l_mc_name VARCHAR2(80);
4120 -- new local variables declared
4121 l_mc_config_rel_id NUMBER;
4122 l_cyclic_relation_exist BOOLEAN := FALSE;
4123 l_dest_header_id NUMBER;
4124 l_submc_name VARCHAR2(80);
4125
4126 BEGIN
4127
4128 -- API body starts here
4129 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
4130 THEN
4131 fnd_log.string
4132 (
4133 fnd_log.level_procedure,
4134 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||'.begin',
4135 'At the start of PLSQL procedure'
4136 );
4137 END IF;
4138
4139 -- Validate a MC node with relationship_id = p_x_subconfig_rec.relationship_id exists
4140 Validate_Node_Exists(p_x_subconfig_rec.relationship_id, null);
4141
4142 -- Validate the MC node with relationship_id = p_x_subconfig_rec.relationship_id is a leaf node
4143 OPEN check_root_node;
4144 FETCH check_root_node INTO l_dummy_varchar;
4145 IF (check_root_node%FOUND)
4146 THEN
4147 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_NOT_TOP_NODE');
4148 FND_MSG_PUB.ADD;
4149 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
4150 THEN
4151 fnd_log.message
4152 (
4153 fnd_log.level_exception,
4154 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
4155 false
4156 );
4157 END IF;
4158 RAISE FND_API.G_EXC_ERROR;
4159 END IF;
4160 CLOSE check_root_node;
4161
4162 OPEN check_leaf_node;
4163 FETCH check_leaf_node INTO l_dummy_varchar;
4164 IF (check_leaf_node%FOUND)
4165 THEN
4166 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_NOT_LEAF_NODE');
4167 FND_MSG_PUB.ADD;
4168 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
4169 THEN
4170 fnd_log.message
4171 (
4172 fnd_log.level_exception,
4173 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
4174 false
4175 );
4176 END IF;
4177 RAISE FND_API.G_EXC_ERROR;
4178 END IF;
4179 CLOSE check_leaf_node;
4180
4181 -- Validate the MC with mc_header_id = p_x_subconfig_rec.mc_header_id is complete/draft/approval_rejected
4182 l_header_status := Get_MC_Status(null, p_x_subconfig_rec.mc_header_id);
4183 IF (l_header_status NOT IN ('APPROVAL_PENDING', 'COMPLETE', 'DRAFT', 'APPROVAL_REJECTED'))
4184 THEN
4185 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_SUBMC_STS_INV');
4186 FND_MSG_PUB.ADD;
4187 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
4188 THEN
4189 fnd_log.message
4190 (
4191 fnd_log.level_exception,
4192 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
4193 false
4194 );
4195 END IF;
4196 RAISE FND_API.G_EXC_ERROR;
4197 END IF;
4198
4199 -- Validate whether the subconfiguration is not already associated with the MC node
4200 OPEN check_submc_exists;
4201 FETCH check_submc_exists INTO l_mc_name;
4202 IF (check_submc_exists%FOUND)
4203 THEN
4204 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_SUBMC_EXISTS');
4205 FND_MESSAGE.Set_Token('SUBMC', l_mc_name);
4206 FND_MSG_PUB.ADD;
4207 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
4208 THEN
4209 fnd_log.message
4210 (
4211 fnd_log.level_exception,
4212 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
4213 false
4214 );
4215 END IF;
4216
4217 CLOSE check_submc_exists;
4218 RAISE FND_API.G_EXC_ERROR;
4219 END IF;
4220 CLOSE check_submc_exists;
4221
4222 -- anraj changed for fixing the bug # 3696668
4223 -- Check cyclic relationship for subconfig = p_x_subconfig_rec.mc_header_id and node = p_x_subconfig_rec.relationship_id
4224 --Check_Cyclic_Rel(p_x_subconfig_rec.mc_header_id, p_x_subconfig_rec.relationship_id);
4225 OPEN get_dest_header_id(p_x_subconfig_rec.relationship_id);
4226 FETCH get_dest_header_id into l_dest_header_id;
4227 CLOSE get_dest_header_id;
4228 l_cyclic_relation_exist := Cyclic_Relation_Exists(p_x_subconfig_rec.mc_header_id,l_dest_header_id);
4229 IF (l_cyclic_relation_exist) THEN
4230 OPEN get_node_mc_details(p_x_subconfig_rec.relationship_id);
4231 FETCH get_node_mc_details INTO l_mc_name;
4232 CLOSE get_node_mc_details;
4233 OPEN get_mc_details(p_x_subconfig_rec.mc_header_id);
4234 FETCH get_mc_details INTO l_submc_name;
4235 CLOSE get_mc_details;
4236 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_CYCLIC_REL_EXIST');
4237 FND_MESSAGE.Set_Token('MC', l_mc_name);
4238 FND_MESSAGE.Set_Token('SUBMC', l_submc_name);
4239 FND_MSG_PUB.ADD;
4240 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
4241 THEN
4242 fnd_log.message
4243 (
4244 fnd_log.level_exception,
4245 'ahl.plsql.'||G_PKG_NAME||'.Check_Cyclic_Rel',
4246 false
4247 );
4248 END IF;
4249 END IF;
4250
4251
4252 -- Check Error Message stack.
4253 l_msg_count := FND_MSG_PUB.count_msg;
4254 IF l_msg_count > 0 THEN
4255 RAISE FND_API.G_EXC_ERROR;
4256 END IF;
4257
4258 -- Validate dates for the subconfig association
4259 IF (trunc(nvl(p_x_subconfig_rec.active_start_date, G_SYSDATE)) < G_TRUNC_DATE)
4260 THEN
4261 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_START_DATE_INV');
4262 FND_MSG_PUB.ADD;
4263 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
4264 THEN
4265 fnd_log.message
4266 (
4267 fnd_log.level_exception,
4268 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
4269 false
4270 );
4271 END IF;
4272 END IF;
4273
4274 IF (trunc(nvl(p_x_subconfig_rec.active_end_date, G_SYSDATE + 1)) <= G_TRUNC_DATE)
4275 THEN
4276 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_END_DATE_INV');
4277 FND_MSG_PUB.ADD;
4278 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
4279 THEN
4280 fnd_log.message
4281 (
4282 fnd_log.level_exception,
4283 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
4284 false
4285 );
4286 END IF;
4287 END IF;
4288
4289 IF (trunc(nvl(p_x_subconfig_rec.active_end_date, nvl(p_x_subconfig_rec.active_start_date, G_SYSDATE) + 1)) <= trunc(nvl(p_x_subconfig_rec.active_start_date, G_SYSDATE)))
4290 THEN
4291 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_START_END_INV');
4292 FND_MSG_PUB.ADD;
4293 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
4294 THEN
4295 fnd_log.message
4296 (
4297 fnd_log.level_exception,
4298 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
4299 false
4300 );
4301 END IF;
4302 END IF;
4303
4304 IF (p_x_subconfig_rec.priority IS NULL)
4305 THEN
4306 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_PRIORITY_SUBMC_NULL');
4307 FND_MESSAGE.Set_Token('SUB_MC',p_x_subconfig_rec.name);
4308 FND_MSG_PUB.ADD;
4309 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
4310 THEN
4311 fnd_log.message
4312 (
4313 fnd_log.level_exception,
4314 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
4315 false
4316 );
4317 END IF;
4318 ELSIF (p_x_subconfig_rec.priority <= 0)
4319 THEN
4320 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_PRIORITY_INVALID_JSP');
4321 FND_MSG_PUB.ADD;
4322 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
4323 THEN
4324 fnd_log.message
4325 (
4326 fnd_log.level_exception,
4327 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
4328 false
4329 );
4330 END IF;
4331 END IF;
4332
4333 -- Check Error Message stack.
4334 l_msg_count := FND_MSG_PUB.count_msg;
4335 IF l_msg_count > 0 THEN
4336 RAISE FND_API.G_EXC_ERROR;
4337 END IF;
4338
4339 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
4340 THEN
4341 fnd_log.string
4342 (
4343 fnd_log.level_statement,
4344 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
4345 'Subconfiguration association validation successful'
4346 );
4347 END IF;
4348
4349 -- Set values for p_x_subconfig_rec
4350 SELECT ahl_mc_config_rel_s.nextval INTO p_x_subconfig_rec.mc_config_relation_id FROM DUAL;
4351 p_x_subconfig_rec.object_version_number := 1;
4352 p_x_subconfig_rec.security_group_id := null;
4353
4354 -- Create association record for destination node
4355 INSERT INTO AHL_MC_CONFIG_RELATIONS
4356 (
4357 MC_CONFIG_RELATION_ID,
4358 RELATIONSHIP_ID,
4359 MC_HEADER_ID,
4360 ACTIVE_START_DATE,
4361 ACTIVE_END_DATE,
4362 LAST_UPDATE_DATE,
4363 LAST_UPDATED_BY,
4364 CREATION_DATE,
4365 CREATED_BY,
4366 LAST_UPDATE_LOGIN,
4367 OBJECT_VERSION_NUMBER,
4368 SECURITY_GROUP_ID,
4369 ATTRIBUTE_CATEGORY,
4370 ATTRIBUTE1,
4371 ATTRIBUTE2,
4372 ATTRIBUTE3,
4373 ATTRIBUTE4,
4374 ATTRIBUTE5,
4375 ATTRIBUTE6,
4376 ATTRIBUTE7,
4377 ATTRIBUTE8,
4378 ATTRIBUTE9,
4379 ATTRIBUTE10,
4380 ATTRIBUTE11,
4381 ATTRIBUTE12,
4382 ATTRIBUTE13,
4383 ATTRIBUTE14,
4384 ATTRIBUTE15,
4385 PRIORITY
4386 )
4387 VALUES
4388 (
4389 p_x_subconfig_rec.MC_CONFIG_RELATION_ID,
4390 p_x_subconfig_rec.RELATIONSHIP_ID,
4391 p_x_subconfig_rec.MC_HEADER_ID,
4392 TRUNC(p_x_subconfig_rec.ACTIVE_START_DATE),
4393 TRUNC(p_x_subconfig_rec.ACTIVE_END_DATE),
4394 G_SYSDATE,
4395 G_USER_ID,
4396 G_SYSDATE,
4397 G_USER_ID,
4398 G_LOGIN_ID,
4399 p_x_subconfig_rec.OBJECT_VERSION_NUMBER,
4400 p_x_subconfig_rec.SECURITY_GROUP_ID,
4401 p_x_subconfig_rec.ATTRIBUTE_CATEGORY,
4402 p_x_subconfig_rec.ATTRIBUTE1,
4403 p_x_subconfig_rec.ATTRIBUTE2,
4404 p_x_subconfig_rec.ATTRIBUTE3,
4405 p_x_subconfig_rec.ATTRIBUTE4,
4406 p_x_subconfig_rec.ATTRIBUTE5,
4407 p_x_subconfig_rec.ATTRIBUTE6,
4408 p_x_subconfig_rec.ATTRIBUTE7,
4409 p_x_subconfig_rec.ATTRIBUTE8,
4410 p_x_subconfig_rec.ATTRIBUTE9,
4411 p_x_subconfig_rec.ATTRIBUTE10,
4412 p_x_subconfig_rec.ATTRIBUTE11,
4413 p_x_subconfig_rec.ATTRIBUTE12,
4414 p_x_subconfig_rec.ATTRIBUTE13,
4415 p_x_subconfig_rec.ATTRIBUTE14,
4416 p_x_subconfig_rec.ATTRIBUTE15,
4417 p_x_subconfig_rec.priority
4418 );
4419
4420 -- API body ends here
4421 END Attach_Subconfig;
4422
4423 PROCEDURE Modify_Subconfig
4424 (
4425 p_x_subconfig_rec IN OUT NOCOPY Subconfig_Rec_Type
4426 )
4427 IS
4428
4429 -- Define cursor get_subconfig_dates to retrieve information about dates
4430 CURSOR get_subconfig_dates
4431 (
4432 p_mc_config_rel_id in number
4433 )
4434 IS
4435 SELECT active_start_date, active_end_date
4436 FROM ahl_mc_config_relations
4437 WHERE mc_config_relation_id = p_mc_config_rel_id;
4438
4439 -- Declare local variables
4440 l_api_name CONSTANT VARCHAR2(30) := 'Modify_Subconfig';
4441 l_msg_count NUMBER;
4442
4443 l_header_status VARCHAR2(30);
4444 l_start_date DATE;
4445 l_end_date DATE;
4446
4447 BEGIN
4448
4449 -- API body starts here
4450 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
4451 THEN
4452 fnd_log.string
4453 (
4454 fnd_log.level_procedure,
4455 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||'.begin',
4456 'At the start of PLSQL procedure'
4457 );
4458 END IF;
4459
4460 -- Validate that the subconfiguration association exists
4461 Validate_Subconfig_Exists(p_x_subconfig_rec.mc_config_relation_id, nvl(p_x_subconfig_rec.object_version_number, 0));
4462
4463 -- Validate a MC node with relationship_id = p_x_subconfig_rec.relationship_id exists
4464 Validate_Node_Exists(p_x_subconfig_rec.relationship_id, null);
4465
4466 -- Validate the MC with mc_header_id = p_x_subconfig_rec.mc_header_id is complete
4467 l_header_status := Get_MC_Status(null, p_x_subconfig_rec.mc_header_id);
4468 IF (l_header_status NOT IN ('APPROVAL_PENDING', 'COMPLETE', 'DRAFT', 'APPROVAL_REJECTED'))
4469 THEN
4470 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_SUBMC_STS_INV');
4471 FND_MSG_PUB.ADD;
4472 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
4473 THEN
4474 fnd_log.message
4475 (
4476 fnd_log.level_exception,
4477 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
4478 false
4479 );
4480 END IF;
4481 RAISE FND_API.G_EXC_ERROR;
4482 END IF;
4483
4484 -- Validate dates for the subconfig association
4485 OPEN get_subconfig_dates(p_x_subconfig_rec.mc_config_relation_id);
4486 FETCH get_subconfig_dates INTO l_start_date, l_end_date;
4487 CLOSE get_subconfig_dates;
4488
4489 /*
4490 -- Should be able to unexpire an expired subconfiguration association
4491 IF (G_TRUNC_DATE >= trunc(nvl(l_end_date, G_SYSDATE + 1)))
4492 THEN
4493 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_SUBMC_DATE_INV');
4494 FND_MSG_PUB.ADD;
4495 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
4496 THEN
4497 fnd_log.message
4498 (
4499 fnd_log.level_exception,
4500 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
4501 false
4502 );
4503 END IF;
4504 END IF;
4505 */
4506
4507 IF (p_x_subconfig_rec.active_start_date IS NOT NULL AND trunc(nvl(l_start_date, G_SYSDATE)) <> trunc(p_x_subconfig_rec.active_start_date) AND trunc(p_x_subconfig_rec.active_start_date) < G_TRUNC_DATE)
4508 THEN
4509 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_START_DATE_INV');
4510 FND_MSG_PUB.ADD;
4511 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
4512 THEN
4513 fnd_log.message
4514 (
4515 fnd_log.level_exception,
4516 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
4517 false
4518 );
4519 END IF;
4520 END IF;
4521
4522 IF (trunc(nvl(l_end_date, G_SYSDATE)) <> trunc(nvl(p_x_subconfig_rec.active_end_date, G_SYSDATE)) AND trunc(nvl(p_x_subconfig_rec.active_end_date, G_SYSDATE + 1)) <= G_TRUNC_DATE)
4523 THEN
4524 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_END_DATE_INV');
4525 FND_MSG_PUB.ADD;
4526 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
4527 THEN
4528 fnd_log.message
4529 (
4530 fnd_log.level_exception,
4531 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
4532 false
4533 );
4534 END IF;
4535 END IF;
4536
4537 IF (trunc(nvl(p_x_subconfig_rec.active_end_date, nvl(p_x_subconfig_rec.active_start_date, G_SYSDATE) + 1)) <= trunc(nvl(p_x_subconfig_rec.active_start_date, G_SYSDATE)))
4538 THEN
4539 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_START_END_INV');
4540 FND_MSG_PUB.ADD;
4541 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
4542 THEN
4543 fnd_log.message
4544 (
4545 fnd_log.level_exception,
4546 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
4547 false
4548 );
4549 END IF;
4550 END IF;
4551
4552
4553 IF (p_x_subconfig_rec.priority IS NULL)
4554 THEN
4555 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_PRIORITY_SUBMC_NULL');
4556 FND_MESSAGE.Set_Token('SUB_MC',p_x_subconfig_rec.name);
4557 FND_MSG_PUB.ADD;
4558 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
4559 THEN
4560 fnd_log.message
4561 (
4562 fnd_log.level_exception,
4563 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
4564 false
4565 );
4566 END IF;
4567 ELSIF (p_x_subconfig_rec.priority <= 0)
4568 THEN
4569 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_PRIORITY_INVALID_JSP');
4570 FND_MSG_PUB.ADD;
4571 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
4572 THEN
4573 fnd_log.message
4574 (
4575 fnd_log.level_exception,
4576 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
4577 false
4578 );
4579 END IF;
4580
4581 END IF;
4582
4583 -- Check Error Message stack.
4584 l_msg_count := FND_MSG_PUB.count_msg;
4585 IF l_msg_count > 0 THEN
4586 RAISE FND_API.G_EXC_ERROR;
4587 END IF;
4588
4589 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
4590 THEN
4591 fnd_log.string
4592 (
4593 fnd_log.level_statement,
4594 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
4595 'Subconfiguration association validation successful'
4596 );
4597 END IF;
4598
4599 p_x_subconfig_rec.OBJECT_VERSION_NUMBER := p_x_subconfig_rec.OBJECT_VERSION_NUMBER + 1;
4600
4601 -- Create association record for destination node
4602 UPDATE AHL_MC_CONFIG_RELATIONS
4603 SET PRIORITY = p_x_subconfig_rec.priority,
4604 ACTIVE_START_DATE = p_x_subconfig_rec.ACTIVE_START_DATE,
4605 ACTIVE_END_DATE = p_x_subconfig_rec.ACTIVE_END_DATE,
4606 LAST_UPDATE_DATE = G_SYSDATE,
4607 LAST_UPDATED_BY = G_USER_ID,
4608 LAST_UPDATE_LOGIN = G_LOGIN_ID,
4609 OBJECT_VERSION_NUMBER = p_x_subconfig_rec.OBJECT_VERSION_NUMBER,
4610 SECURITY_GROUP_ID = p_x_subconfig_rec.SECURITY_GROUP_ID,
4611 ATTRIBUTE_CATEGORY = p_x_subconfig_rec.ATTRIBUTE_CATEGORY,
4612 ATTRIBUTE1 = p_x_subconfig_rec.ATTRIBUTE1,
4613 ATTRIBUTE2 = p_x_subconfig_rec.ATTRIBUTE2,
4614 ATTRIBUTE3 = p_x_subconfig_rec.ATTRIBUTE3,
4615 ATTRIBUTE4 = p_x_subconfig_rec.ATTRIBUTE4,
4616 ATTRIBUTE5 = p_x_subconfig_rec.ATTRIBUTE5,
4617 ATTRIBUTE6 = p_x_subconfig_rec.ATTRIBUTE6,
4618 ATTRIBUTE7 = p_x_subconfig_rec.ATTRIBUTE7,
4619 ATTRIBUTE8 = p_x_subconfig_rec.ATTRIBUTE8,
4620 ATTRIBUTE9 = p_x_subconfig_rec.ATTRIBUTE9,
4621 ATTRIBUTE10 = p_x_subconfig_rec.ATTRIBUTE10,
4622 ATTRIBUTE11 = p_x_subconfig_rec.ATTRIBUTE11,
4623 ATTRIBUTE12 = p_x_subconfig_rec.ATTRIBUTE12,
4624 ATTRIBUTE13 = p_x_subconfig_rec.ATTRIBUTE13,
4625 ATTRIBUTE14 = p_x_subconfig_rec.ATTRIBUTE14,
4626 ATTRIBUTE15 = p_x_subconfig_rec.ATTRIBUTE15
4627 WHERE MC_CONFIG_RELATION_ID = p_x_subconfig_rec.MC_CONFIG_RELATION_ID;
4628
4629 -- API body ends here
4630
4631 END Modify_Subconfig;
4632
4633 PROCEDURE Detach_Subconfig
4634 (
4635 p_mc_config_relation_id IN NUMBER,
4636 p_object_ver_num IN NUMBER
4637 )
4638 IS
4639 -- Define local variables
4640 l_api_name CONSTANT VARCHAR2(30) := 'Detach_Subconfig';
4641
4642 BEGIN
4643
4644 -- API body starts here
4645 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
4646 THEN
4647 fnd_log.string
4648 (
4649 fnd_log.level_procedure,
4650 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||'.begin',
4651 'At the start of PLSQL procedure'
4652 );
4653 END IF;
4654
4655 -- Validate p_mc_config_relation_id exists
4656 Validate_Subconfig_Exists(p_mc_config_relation_id, nvl(p_object_ver_num, 0));
4657
4658 DELETE FROM ahl_mc_config_relations
4659 WHERE mc_config_relation_id = p_mc_config_relation_id;
4660
4661 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
4662 THEN
4663 fnd_log.string
4664 (
4665 fnd_log.level_procedure,
4666 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||'.end',
4667 'At the end of PLSQL procedure'
4668 );
4669 END IF;
4670 -- API body ends here
4671
4672 END Detach_Subconfig;
4673
4674 PROCEDURE Copy_Subconfig
4675 (
4676 p_source_rel_id IN NUMBER,
4677 p_dest_rel_id IN NUMBER
4678 )
4679 IS
4680 -- Define a cursor to get the MC header id , when a relationship id is given
4681 CURSOR get_dest_header_id
4682 IS
4683 SELECT mc_header_id
4684 FROM ahl_mc_relationships
4685 WHERE relationship_id = p_dest_rel_id;
4686 -- Define cursor get_node_mc_details to read detail of the MC of a MC node
4687 CURSOR get_node_mc_details
4688 IS
4689 SELECT mch.name
4690 FROM ahl_mc_headers_b mch, ahl_mc_relationships mcr
4691 WHERE mch.mc_header_id = mcr.mc_header_id AND
4692 mcr.relationship_id = p_dest_rel_id;
4693 -- Define cursor get_mc_details to read detail of a MC
4694 CURSOR get_mc_details(p_subconfig_id in number)
4695 IS
4696 SELECT name
4697 FROM ahl_mc_headers_b
4698 WHERE mc_header_id = p_subconfig_id;
4699 -- Define cursor get_subconfigs to read all valid subconfiguration associations with a particular MC node
4700 CURSOR get_subconfigs
4701 IS
4702 SELECT *
4703 FROM ahl_mc_config_relations
4704 WHERE relationship_id = p_source_rel_id;
4705 -- Expired subconfig associations also need to be copied or else copying position paths will fail
4706 -- AND G_TRUNC_DATE < trunc(nvl(active_end_date, G_SYSDATE + 1));
4707
4708 -- Define check_leaf_node to check whether the node to which subconfiguration is being associated is a leaf node
4709 CURSOR check_leaf_node
4710 (
4711 p_rel_id in number
4712 )
4713 IS
4714 SELECT 'x'
4715 FROM ahl_mc_relationships
4716 WHERE parent_relationship_id = p_rel_id AND
4717 G_TRUNC_DATE < trunc(nvl(active_end_date, G_SYSDATE + 1));
4718
4719 -- Declare local variables
4720 l_api_name CONSTANT VARCHAR2(30) := 'Copy_Subconfig';
4721 l_msg_count NUMBER;
4722
4723 l_subconfig_csr_rec get_subconfigs%rowtype;
4724 -- new local variables declared
4725 l_mc_config_rel_id NUMBER;
4726 l_cyclic_relation_exist BOOLEAN := FALSE;
4727 l_dest_header_id NUMBER;
4728 l_mc_name VARCHAR2(80);
4729 l_submc_name VARCHAR2(80);
4730
4731 BEGIN
4732
4733 -- API body starts here
4734 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
4735 THEN
4736 fnd_log.string
4737 (
4738 fnd_log.level_procedure,
4739 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||'.begin',
4740 'At the start of PLSQL procedure'
4741 );
4742 END IF;
4743
4744 -- Validate p_source_rel_id exists
4745 Validate_Node_Exists(p_source_rel_id, null);
4746
4747 -- Validate p_dest_rel_id exists
4748 Validate_Node_Exists(p_dest_rel_id, null);
4749
4750 -- Check Error Message stack.
4751 l_msg_count := FND_MSG_PUB.count_msg;
4752 IF l_msg_count > 0 THEN
4753 RAISE FND_API.G_EXC_ERROR;
4754 END IF;
4755
4756 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
4757 THEN
4758 fnd_log.string
4759 (
4760 fnd_log.level_statement,
4761 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
4762 'Source and destination node validation successful'
4763 );
4764 END IF;
4765
4766 -- Retrieve all subconfigurations associations with p_source_rel_id
4767 OPEN get_subconfigs;
4768 LOOP
4769 FETCH get_subconfigs INTO l_subconfig_csr_rec;
4770 EXIT WHEN get_subconfigs% NOTFOUND;
4771
4772 -- Validte p_dest_rel_id is leaf node
4773 OPEN check_leaf_node(p_dest_rel_id);
4774 FETCH check_leaf_node INTO l_dummy_varchar;
4775 IF (check_leaf_node%FOUND)
4776 THEN
4777 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_NOT_LEAF_NODE');
4778 FND_MSG_PUB.ADD;
4779 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
4780 THEN
4781 fnd_log.message
4782 (
4783 fnd_log.level_exception,
4784 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
4785 false
4786 );
4787 END IF;
4788 RAISE FND_API.G_EXC_ERROR;
4789 END IF;
4790 CLOSE check_leaf_node;
4791
4792 -- anraj changed for fixing the bug # 3696668
4793 -- Check cyclic relationship for this association
4794 -- Check_Cyclic_Rel(l_subconfig_csr_rec.mc_header_id, p_dest_rel_id);
4795 OPEN get_dest_header_id;
4796 FETCH get_dest_header_id into l_dest_header_id;
4797 CLOSE get_dest_header_id;
4798 IF (l_subconfig_csr_rec.mc_header_id = l_dest_header_id) THEN
4799 l_cyclic_relation_exist := TRUE;
4800 ELSE
4801 l_cyclic_relation_exist := Cyclic_Relation_Exists(l_subconfig_csr_rec.mc_header_id,l_dest_header_id);
4802 END IF;
4803 IF (l_cyclic_relation_exist) THEN
4804 OPEN get_node_mc_details;
4805 FETCH get_node_mc_details INTO l_mc_name;
4806 CLOSE get_node_mc_details;
4807
4808 OPEN get_mc_details(l_subconfig_csr_rec.mc_header_id);
4809 FETCH get_mc_details INTO l_submc_name;
4810 CLOSE get_mc_details;
4811 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_CYCLIC_REL_EXIST');
4812 FND_MESSAGE.Set_Token('MC', l_mc_name);
4813 FND_MESSAGE.Set_Token('SUBMC', l_submc_name);
4814 FND_MSG_PUB.ADD;
4815 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
4816 THEN
4817 fnd_log.message
4818 (
4819 fnd_log.level_exception,
4820 'ahl.plsql.'||G_PKG_NAME||'.Check_Cyclic_Rel',
4821 false
4822 );
4823 END IF;
4824 END IF;
4825 -- Check Error Message stack.
4826 l_msg_count := FND_MSG_PUB.count_msg;
4827 IF l_msg_count > 0 THEN
4828 RAISE FND_API.G_EXC_ERROR;
4829 END IF;
4830
4831 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
4832 THEN
4833 fnd_log.string
4834 (
4835 fnd_log.level_statement,
4836 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
4837 'Cyclic relation check for node ['||p_dest_rel_id||'] and subconfiguration ['||l_subconfig_csr_rec.mc_header_id||'] is successful'
4838 );
4839 END IF;
4840
4841 -- Set values for l_subconfig_csr_rec
4842 SELECT ahl_mc_config_rel_s.nextval INTO l_mc_config_rel_id FROM DUAL;
4843
4844 -- Create association record for destination node
4845 INSERT INTO AHL_MC_CONFIG_RELATIONS
4846 (
4847 MC_CONFIG_RELATION_ID,
4848 RELATIONSHIP_ID,
4849 MC_HEADER_ID,
4850 ACTIVE_START_DATE,
4851 ACTIVE_END_DATE,
4852 LAST_UPDATE_DATE,
4853 LAST_UPDATED_BY,
4854 CREATION_DATE,
4855 CREATED_BY,
4856 LAST_UPDATE_LOGIN,
4857 OBJECT_VERSION_NUMBER,
4858 SECURITY_GROUP_ID,
4859 ATTRIBUTE_CATEGORY,
4860 ATTRIBUTE1,
4861 ATTRIBUTE2,
4862 ATTRIBUTE3,
4863 ATTRIBUTE4,
4864 ATTRIBUTE5,
4865 ATTRIBUTE6,
4866 ATTRIBUTE7,
4867 ATTRIBUTE8,
4868 ATTRIBUTE9,
4869 ATTRIBUTE10,
4870 ATTRIBUTE11,
4871 ATTRIBUTE12,
4872 ATTRIBUTE13,
4873 ATTRIBUTE14,
4874 ATTRIBUTE15,
4875 PRIORITY
4876 )
4877 VALUES
4878 (
4879 l_mc_config_rel_id,
4880 p_dest_rel_id,
4881 l_subconfig_csr_rec.MC_HEADER_ID,
4882 TRUNC(l_subconfig_csr_rec.ACTIVE_START_DATE),
4883 TRUNC(l_subconfig_csr_rec.ACTIVE_END_DATE),
4884 G_SYSDATE,
4885 G_USER_ID,
4886 G_SYSDATE,
4887 G_USER_ID,
4888 G_LOGIN_ID,
4889 1,
4890 l_subconfig_csr_rec.SECURITY_GROUP_ID,
4891 l_subconfig_csr_rec.ATTRIBUTE_CATEGORY,
4892 l_subconfig_csr_rec.ATTRIBUTE1,
4893 l_subconfig_csr_rec.ATTRIBUTE2,
4894 l_subconfig_csr_rec.ATTRIBUTE3,
4895 l_subconfig_csr_rec.ATTRIBUTE4,
4896 l_subconfig_csr_rec.ATTRIBUTE5,
4897 l_subconfig_csr_rec.ATTRIBUTE6,
4898 l_subconfig_csr_rec.ATTRIBUTE7,
4899 l_subconfig_csr_rec.ATTRIBUTE8,
4900 l_subconfig_csr_rec.ATTRIBUTE9,
4901 l_subconfig_csr_rec.ATTRIBUTE10,
4902 l_subconfig_csr_rec.ATTRIBUTE11,
4903 l_subconfig_csr_rec.ATTRIBUTE12,
4904 l_subconfig_csr_rec.ATTRIBUTE13,
4905 l_subconfig_csr_rec.ATTRIBUTE14,
4906 l_subconfig_csr_rec.ATTRIBUTE15,
4907 l_subconfig_csr_rec.PRIORITY
4908 );
4909
4910 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
4911 THEN
4912 fnd_log.string
4913 (
4914 fnd_log.level_statement,
4915 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
4916 'Subconfiguration association ['||l_subconfig_csr_rec.mc_config_relation_id||'] copied to ['||l_mc_config_rel_id||']'
4917 );
4918 END IF;
4919 END LOOP;
4920 CLOSE get_subconfigs;
4921
4922 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
4923 THEN
4924 fnd_log.string
4925 (
4926 fnd_log.level_procedure,
4927 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||'.end',
4928 'At the end of PLSQL procedure'
4929 );
4930 END IF;
4931 -- API body ends here
4932
4933 END Copy_Subconfig;
4934
4935 End AHL_MC_Node_PVT;