[Home] [Help]
PACKAGE BODY: APPS.AHL_MC_NODE_PVT
Source
1 PACKAGE BODY AHL_MC_Node_PVT AS
2 /* $Header: AHLVNODB.pls 120.11.12020000.2 2012/12/07 00:52:09 sareepar 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 -- FP #8410484
2155 IF NOT (l_header_status IN ('DRAFT', 'APPROVAL_REJECTED', 'COMPLETE'))
2156 THEN
2157 FND_MESSAGE.Set_Name('AHL', 'AHL_DI_ASSO_UPDATE_ERROR');
2158 FND_MSG_PUB.ADD;
2159 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
2160 THEN
2161 fnd_log.message
2162 (
2163 fnd_log.level_exception,
2164 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
2165 false
2166 );
2167 END IF;
2168 RAISE FND_API.G_EXC_ERROR;
2169 ELSIF (l_header_status = 'APPROVAL_REJECTED')
2170 THEN
2171 -- 6ii. Set status of MC to DRAFT if APPROVAL_REJECTED
2172 Set_Header_Status(p_node_id);
2173 END IF;
2174
2175 -- Check Error Message stack.
2176 x_msg_count := FND_MSG_PUB.count_msg;
2177 IF x_msg_count > 0 THEN
2178 RAISE FND_API.G_EXC_ERROR;
2179 END IF;
2180
2181 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
2182 THEN
2183 fnd_log.string
2184 (
2185 fnd_log.level_statement,
2186 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
2187 'Node validation successful'
2188 );
2189 END IF;
2190
2191 IF (p_x_documents_tbl.COUNT > 0)
2192 THEN
2193 FOR i IN p_x_documents_tbl.FIRST..p_x_documents_tbl.LAST
2194 LOOP
2195 p_x_documents_tbl(i).aso_object_id := p_node_id;
2196 p_x_documents_tbl(i).aso_object_type_code := 'MC';
2197
2198 -- If revision not chosen, throw error
2199 IF (p_x_documents_tbl(i).REVISION_NO IS NULL AND p_x_documents_tbl(i).dml_operation <> G_DML_DELETE)
2200 THEN
2201 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_DOC_NO_REV');
2202 FND_MESSAGE.Set_Token('DOC', p_x_documents_tbl(i).DOCUMENT_NO);
2203 FND_MSG_PUB.ADD;
2204 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
2205 THEN
2206 fnd_log.message
2207 (
2208 fnd_log.level_exception,
2209 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
2210 false
2211 );
2212 END IF;
2213 END IF;
2214 END LOOP;
2215
2216 -- Check Error Message stack.
2217 x_msg_count := FND_MSG_PUB.count_msg;
2218 IF x_msg_count > 0 THEN
2219 RAISE FND_API.G_EXC_ERROR;
2220 END IF;
2221
2222 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
2223 THEN
2224 fnd_log.string
2225 (
2226 fnd_log.level_statement,
2227 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
2228 'Document association validations successful... Calling AHL_DI_ASSO_DOC_GEN_PUB.PROCESS_ASSOCIATION'
2229 );
2230 END IF;
2231
2232 AHL_DI_ASSO_DOC_GEN_PUB.PROCESS_ASSOCIATION
2233 (
2234 p_api_version => 1.0,
2235 p_init_msg_list => FND_API.G_FALSE,
2236 p_commit => FND_API.G_FALSE,
2237 p_validate_only => FND_API.G_FALSE,
2238 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2239 p_x_association_tbl => p_x_documents_tbl,
2240 p_module_type => 'JSP',
2241 x_return_status => l_return_status,
2242 x_msg_count => l_msg_count,
2243 x_msg_data => l_msg_data
2244 );
2245 END IF;
2246
2247 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
2248 THEN
2249 fnd_log.string
2250 (
2251 fnd_log.level_procedure,
2252 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||'.end',
2253 'At the end of PLSQL procedure'
2254 );
2255 END IF;
2256 -- API body ends here
2257
2258 -- Check Error Message stack.
2259 x_msg_count := FND_MSG_PUB.count_msg;
2260 IF x_msg_count > 0 THEN
2261 RAISE FND_API.G_EXC_ERROR;
2262 END IF;
2263
2264 -- Standard check for p_commit
2265 IF FND_API.TO_BOOLEAN (p_commit)
2266 THEN
2267 COMMIT WORK;
2268 END IF;
2269
2270 -- Standard call to get message count and if count is 1, get message info
2271 FND_MSG_PUB.count_and_get
2272 (
2273 p_count => x_msg_count,
2274 p_data => x_msg_data,
2275 p_encoded => FND_API.G_FALSE
2276 );
2277
2278 EXCEPTION
2279 WHEN FND_API.G_EXC_ERROR THEN
2280 x_return_status := FND_API.G_RET_STS_ERROR;
2281 Rollback to Process_Documents_SP;
2282 FND_MSG_PUB.count_and_get
2283 (
2284 p_count => x_msg_count,
2285 p_data => x_msg_data,
2286 p_encoded => FND_API.G_FALSE
2287 );
2288
2289 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2290 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2291 Rollback to Process_Documents_SP;
2292 FND_MSG_PUB.count_and_get
2293 (
2294 p_count => x_msg_count,
2295 p_data => x_msg_data,
2296 p_encoded => FND_API.G_FALSE
2297 );
2298
2299 WHEN OTHERS THEN
2300 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2301 Rollback to Process_Documents_SP;
2302 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2303 THEN
2304 FND_MSG_PUB.add_exc_msg
2305 (
2306 p_pkg_name => G_PKG_NAME,
2307 p_procedure_name => 'Process_Documents',
2308 p_error_text => SUBSTR(SQLERRM,1,240)
2309 );
2310 END IF;
2311 FND_MSG_PUB.count_and_get
2312 (
2313 p_count => x_msg_count,
2314 p_data => x_msg_data,
2315 p_encoded => FND_API.G_FALSE
2316 );
2317
2318 END Process_Documents;
2319
2320 PROCEDURE Associate_Item_Group
2321 (
2322 p_api_version IN NUMBER,
2323 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2324 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2325 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2326 x_return_status OUT NOCOPY VARCHAR2,
2327 x_msg_count OUT NOCOPY NUMBER,
2328 x_msg_data OUT NOCOPY VARCHAR2,
2329 p_nodes_tbl IN Node_Tbl_Type
2330 )
2331 IS
2332
2333 -- Define cursor get_item_group_det to validate item group exists
2334 CURSOR get_item_group_det
2335 (
2336 p_ig_id in number
2337 )
2338 IS
2339 SELECT type_code, name
2340 FROM ahl_item_groups_b
2341 WHERE item_group_id = p_ig_id;
2342
2343 -- Define get_item_group_id to retrieve item_group_id given name of the item group
2344 -- SATHAPLI::made the item group name join case sensitive
2345 CURSOR get_item_group_csr
2346 (
2347 p_ig_name in VARCHAR2
2348 )
2349 IS
2350 SELECT item_group_id, type_code, name
2351 FROM ahl_item_groups_b
2352 WHERE name = p_ig_name AND
2353 source_item_group_id IS NULL;
2354
2355 -- 1. Define local variables
2356 l_api_name CONSTANT VARCHAR2(30) := 'Associate_Item_Group';
2357 l_api_version CONSTANT NUMBER := 1.0;
2358
2359 l_header_status VARCHAR2(30);
2360 l_item_group_id NUMBER;
2361 l_type_code VARCHAR2(30);
2362 l_item_group_name VARCHAR2(80);
2363
2364 BEGIN
2365
2366 -- Standard start of API savepoint
2367 SAVEPOINT Associate_Item_Group_SP;
2368
2369 -- Standard call to check for call compatibility
2370 IF NOT FND_API.compatible_api_call(l_api_version, p_api_version, l_api_name, G_PKG_NAME)
2371 THEN
2372 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2373 END IF;
2374
2375 -- Initialize message list if p_init_msg_list is set to TRUE
2376 IF FND_API.TO_BOOLEAN(p_init_msg_list)
2377 THEN
2378 FND_MSG_PUB.Initialize;
2379 END IF;
2380
2381 -- Initialize API return status to success
2382 x_return_status := FND_API.G_RET_STS_SUCCESS;
2383
2384 -- API body starts here
2385 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
2386 THEN
2387 fnd_log.string
2388 (
2389 fnd_log.level_procedure,
2390 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||'.begin',
2391 'At the start of PLSQL procedure'
2392 );
2393 END IF;
2394
2395 IF (p_nodes_tbl.COUNT > 0)
2396 THEN
2397 FOR i IN p_nodes_tbl.FIRST..p_nodes_tbl.LAST
2398 LOOP
2399 -- Validate config_status_code of the MC is 'DRAFT' or 'APPROVAL_REJECTED'
2400 l_header_status := Get_MC_Status(p_nodes_tbl(i).relationship_id, null);
2401 IF NOT (l_header_status IN ('DRAFT', 'APPROVAL_REJECTED'))
2402 THEN
2403 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_NODE_STS_INV');
2404 FND_MSG_PUB.ADD;
2405 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
2406 THEN
2407 fnd_log.message
2408 (
2409 fnd_log.level_exception,
2410 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
2411 false
2412 );
2413 END IF;
2414 RAISE FND_API.G_EXC_ERROR;
2415 ELSIF (l_header_status = 'APPROVAL_REJECTED')
2416 THEN
2417 -- Set status of MC to DRAFT if APPROVAL_REJECTED
2418 Set_Header_Status(p_nodes_tbl(i).relationship_id);
2419 END IF;
2420
2421 -- Validate a MC node with relationship_id = p_x_node_rec.relationship_id exists
2422 Validate_Node_Exists(p_nodes_tbl(i).relationship_id, null);
2423
2424 -- Validate p_x_node_rec.item_group_id exists
2425 IF (p_nodes_tbl(i).ITEM_GROUP_ID IS NOT NULL)
2426 THEN
2427 OPEN get_item_group_det (p_nodes_tbl(i).item_group_id);
2428 FETCH get_item_group_det INTO l_type_code,l_item_group_name;
2429 IF (get_item_group_det%NOTFOUND)
2430 THEN
2431 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_ITEMGRP_INVALID');
2432 FND_MESSAGE.Set_Token('ITEM_GRP', p_nodes_tbl(i).item_group_id);
2433 FND_MSG_PUB.ADD;
2434 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
2435 THEN
2436 fnd_log.message
2437 (
2438 fnd_log.level_exception,
2439 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
2440 false
2441 );
2442 END IF;
2443 ELSE
2444 IF l_type_code = 'TRACKED' THEN
2445 l_item_group_id := p_nodes_tbl(i).item_group_id;
2446 ELSE
2447 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_IG_NOT_TRACKED');
2448 FND_MESSAGE.Set_Token('IG_NAME', l_item_group_name);
2449 FND_MSG_PUB.ADD;
2450 END IF;
2451
2452 END IF;
2453 CLOSE get_item_group_det;
2454 ELSIF (p_nodes_tbl(i).item_group_name IS NOT NULL)
2455 THEN
2456 OPEN get_item_group_csr (p_nodes_tbl(i).item_group_name);
2457 FETCH get_item_group_csr INTO l_item_group_id,l_type_code,l_item_group_name;
2458 IF (get_item_group_csr%NOTFOUND)
2459 THEN
2460 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_ITEMGRP_INVALID');
2461 FND_MESSAGE.Set_Token('ITEM_GRP', p_nodes_tbl(i).item_group_name);
2462 FND_MSG_PUB.ADD;
2463 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
2464 THEN
2465 fnd_log.message
2466 (
2467 fnd_log.level_exception,
2468 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
2469 false
2470 );
2471 END IF;
2472 ELSIF l_type_code <> 'TRACKED' THEN
2473 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_IG_NOT_TRACKED');
2474 FND_MESSAGE.Set_Token('IG_NAME', l_item_group_name);
2475 FND_MSG_PUB.ADD;
2476 END IF;
2477 CLOSE get_item_group_csr;
2478 ELSE
2479 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_ITEMGRP_NULL');
2480 FND_MSG_PUB.ADD;
2481 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
2482 THEN
2483 fnd_log.message
2484 (
2485 fnd_log.level_exception,
2486 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
2487 false
2488 );
2489 END IF;
2490 END IF;
2491
2492 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
2493 THEN
2494 fnd_log.string
2495 (
2496 fnd_log.level_statement,
2497 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
2498 'Validation successful'
2499 );
2500 END IF;
2501
2502 -- Check Error Message stack.
2503 x_msg_count := FND_MSG_PUB.count_msg;
2504 IF x_msg_count > 0 THEN
2505 RAISE FND_API.G_EXC_ERROR;
2506 END IF;
2507
2508 UPDATE ahl_mc_relationships
2509 SET item_group_id = l_item_group_id
2510 WHERE relationship_id = p_nodes_tbl(i).relationship_id;
2511
2512 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
2513 THEN
2514 fnd_log.string
2515 (
2516 fnd_log.level_statement,
2517 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
2518 'Updated MC node ['||p_nodes_tbl(i).relationship_id||'] with new item group id ['||l_item_group_id||']'
2519 );
2520 END IF;
2521 END LOOP;
2522 END IF;
2523
2524 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
2525 THEN
2526 fnd_log.string
2527 (
2528 fnd_log.level_procedure,
2529 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||'.end',
2530 'At the end of PLSQL procedure'
2531 );
2532 END IF;
2533 -- API body ends here
2534
2535 -- Check Error Message stack.
2536 x_msg_count := FND_MSG_PUB.count_msg;
2537 IF x_msg_count > 0 THEN
2538 RAISE FND_API.G_EXC_ERROR;
2539 END IF;
2540
2541 -- Standard check for p_commit
2542 IF FND_API.TO_BOOLEAN (p_commit)
2543 THEN
2544 COMMIT WORK;
2545 END IF;
2546
2547 -- Standard call to get message count and if count is 1, get message info
2548 FND_MSG_PUB.count_and_get
2549 (
2550 p_count => x_msg_count,
2551 p_data => x_msg_data,
2552 p_encoded => FND_API.G_FALSE
2553 );
2554
2555 EXCEPTION
2556 WHEN FND_API.G_EXC_ERROR THEN
2557 x_return_status := FND_API.G_RET_STS_ERROR;
2558 Rollback to Associate_Item_Group_SP;
2559 FND_MSG_PUB.count_and_get
2560 (
2561 p_count => x_msg_count,
2562 p_data => x_msg_data,
2563 p_encoded => FND_API.G_FALSE
2564 );
2565
2566 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2567 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2568 Rollback to Associate_Item_Group_SP;
2569 FND_MSG_PUB.count_and_get
2570 (
2571 p_count => x_msg_count,
2572 p_data => x_msg_data,
2573 p_encoded => FND_API.G_FALSE
2574 );
2575
2576 WHEN OTHERS THEN
2577 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2578 Rollback to Associate_Item_Group_SP;
2579 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2580 THEN
2581 FND_MSG_PUB.add_exc_msg
2582 (
2583 p_pkg_name => G_PKG_NAME,
2584 p_procedure_name => 'Associate_Item_Group',
2585 p_error_text => SUBSTR(SQLERRM,1,240)
2586 );
2587 END IF;
2588 FND_MSG_PUB.count_and_get
2589 (
2590 p_count => x_msg_count,
2591 p_data => x_msg_data,
2592 p_encoded => FND_API.G_FALSE
2593 );
2594
2595 END Associate_Item_Group;
2596
2597 ---------------------------
2598 -- Validation procedures --
2599 ---------------------------
2600 PROCEDURE Validate_Node_Exists
2601 (
2602 p_rel_id in number,
2603 p_object_ver_num in number
2604 )
2605 IS
2606
2607 CURSOR check_node_exists
2608 IS
2609 SELECT object_version_number
2610 FROM ahl_mc_relationships
2611 WHERE relationship_id = p_rel_id;
2612
2613 BEGIN
2614
2615 OPEN check_node_exists;
2616 FETCH check_node_exists INTO l_dummy_number;
2617 IF (check_node_exists%NOTFOUND)
2618 THEN
2619 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_NODE_NOT_FOUND');
2620 FND_MSG_PUB.ADD;
2621 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
2622 THEN
2623 fnd_log.message
2624 (
2625 fnd_log.level_exception,
2626 'ahl.plsql.'||G_PKG_NAME||'.Validate_Node_Exists',
2627 false
2628 );
2629 END IF;
2630 CLOSE check_node_exists;
2631 RAISE FND_API.G_EXC_ERROR;
2632 ELSIF (NVL(p_object_ver_num, l_dummy_number) <> l_dummy_number)
2633 THEN
2634 FND_MESSAGE.Set_Name('AHL', 'AHL_COM_RECORD_CHANGED');
2635 FND_MSG_PUB.ADD;
2636 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
2637 THEN
2638 fnd_log.message
2639 (
2640 fnd_log.level_exception,
2641 'ahl.plsql.'||G_PKG_NAME||'.Validate_Node_Exists',
2642 false
2643 );
2644 END IF;
2645 CLOSE check_node_exists;
2646 RAISE FND_API.G_EXC_ERROR;
2647 END IF;
2648 CLOSE check_node_exists;
2649
2650 END Validate_Node_Exists;
2651
2652 PROCEDURE Validate_Node
2653 (
2654 p_x_node_rec in out nocopy Node_Rec_Type
2655 )
2656 IS
2657 -- Define cursor get_node_details to check parent quantity = 1 and not expired
2658 CURSOR get_node_details
2659 IS
2660 SELECT quantity,
2661 active_end_date
2662 FROM ahl_mc_relationships
2663 WHERE relationship_id = p_x_node_rec.parent_relationship_id;
2664
2665 -- Define cursor check_subconfig_assos to check whether the parent node has any subconfig associations
2666 CURSOR check_subconfig_assos
2667 IS
2668 SELECT 'x'
2669 FROM ahl_mc_config_relations
2670 WHERE relationship_id = p_x_node_rec.parent_relationship_id;
2671 -- Since expired subconfig associations can be unexpired, so no need to filter on active_end_date
2672 -- AND G_TRUNC_DATE < trunc(nvl(active_end_date, G_SYSDATE + 1));
2673
2674 -- Define cursor check_dup_pos_ref to check whether the parent node does not have the same position reference
2675 CURSOR check_dup_pos_ref
2676 IS
2677 SELECT 'x'
2678 FROM ahl_mc_relationships
2679 WHERE position_ref_code = p_x_node_rec.position_ref_code AND
2680 parent_relationship_id = p_x_node_rec.parent_relationship_id AND
2681 G_TRUNC_DATE < trunc(nvl(active_end_date, G_SYSDATE + 1)) AND
2682 relationship_id <> nvl(p_x_node_rec.relationship_id, -1);
2683
2684 -- Define cursor check_topnode_exists to check whether a topnode already exists for the MC
2685 CURSOR check_topnode_exists
2686 IS
2687 SELECT 'x'
2688 FROM ahl_mc_relationships
2689 WHERE parent_relationship_id is null AND
2690 mc_header_id = p_x_node_rec.mc_header_id;
2691
2692 -- Define cursor get_item_group_id to retrieve item group id, type and status
2693 -- SATHAPLI::Bug 9089133, 08-Feb-2010, made the item group name join case sensitive
2694 CURSOR get_item_group_id
2695 IS
2696 SELECT item_group_id, type_code, status_code
2697 FROM ahl_item_groups_b
2698 WHERE name = p_x_node_rec.item_group_name AND
2699 source_item_group_id IS NULL;
2700
2701 -- Define cursor check_item_assos_qty to check quantity = 1 for all item associations
2702 -- SATHAPLI::FP OGMA Issue# 105 - Non-Serialized Item Maintenance, 04-Dec-2007
2703 -- As non-serialized items having quantities greater than 1 can be associated to an item group now,
2704 -- this check should not be there to restrict them from being used in MC positions.
2705 /*
2706 CURSOR check_item_assos_qty
2707 IS
2708 SELECT 'x'
2709 FROM ahl_item_associations_b
2710 WHERE item_group_id = p_x_node_rec.item_group_id AND
2711 quantity <> 1;
2712 */
2713
2714 -- Define cursor check_child_exists to check whether the node has any children in which case quantity = 1
2715 CURSOR check_child_exists
2716 IS
2717 SELECT 'x'
2718 FROM ahl_mc_relationships
2719 WHERE parent_relationship_id = p_x_node_rec.relationship_id
2720 AND G_TRUNC_DATE < trunc(nvl(active_end_date, G_SYSDATE + 1));
2721
2722 -- Define cursor check_dup_display_order to check display order duplication
2723 CURSOR check_dup_display_order
2724 IS
2725 SELECT 'x'
2726 FROM ahl_mc_relationships
2727 WHERE display_order = p_x_node_rec.display_order AND
2728 parent_relationship_id = p_x_node_rec.parent_relationship_id AND
2729 G_TRUNC_DATE < trunc(nvl(active_end_date, G_SYSDATE + 1)) AND
2730 relationship_id <> nvl(p_x_node_rec.relationship_id, -1);
2731
2732 -- Define cursor get_node_dates to retrieve start and end date of the node
2733 CURSOR get_node_dates
2734 IS
2735 SELECT active_start_date, active_end_date
2736 FROM ahl_mc_relationships
2737 WHERE relationship_id = p_x_node_rec.relationship_id;
2738
2739 -- Declare local variables
2740 l_qty NUMBER;
2741 l_ret_val BOOLEAN;
2742 l_ig_type VARCHAR2(30);
2743 l_ig_status VARCHAR2(30);
2744 l_start_date DATE;
2745 l_end_date DATE;
2746
2747 BEGIN
2748
2749 -- Validate MC parent node
2750 IF (p_x_node_rec.parent_relationship_id IS NOT NULL)
2751 THEN
2752 OPEN get_node_details;
2753 FETCH get_node_details INTO l_qty, l_end_date;
2754 IF (get_node_details%NOTFOUND)
2755 THEN
2756 -- 2a. Validate that the parent node exists
2757 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_PARENT_INVALID');
2758 FND_MSG_PUB.ADD;
2759 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
2760 THEN
2761 fnd_log.message
2762 (
2763 fnd_log.level_exception,
2764 'ahl.plsql.'||G_PKG_NAME||'.Validate_Node',
2765 false
2766 );
2767 END IF;
2768
2769 CLOSE get_node_details;
2770 RAISE FND_API.G_EXC_ERROR;
2771 ELSE
2772 CLOSE get_node_details;
2773
2774 -- 2c. Validate that the parent node has quantity = 1 [only in this can a child node be added to the parent position]
2775 IF (l_qty <> 1)
2776 THEN
2777 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_PARENT_QTY_INV');
2778 FND_MSG_PUB.ADD;
2779 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
2780 THEN
2781 fnd_log.message
2782 (
2783 fnd_log.level_exception,
2784 'ahl.plsql.'||G_PKG_NAME||'.Validate_Node',
2785 false
2786 );
2787 END IF;
2788 END IF;
2789
2790 -- 2d. Validate for the parent node active_end_date > SYSDATE
2791 IF (trunc(nvl(l_end_date, G_SYSDATE + 1)) <= G_TRUNC_DATE)
2792 THEN
2793 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_PARENT_DATE_INV');
2794 FND_MSG_PUB.ADD;
2795 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
2796 THEN
2797 fnd_log.message
2798 (
2799 fnd_log.level_exception,
2800 'ahl.plsql.'||G_PKG_NAME||'.Validate_Node',
2801 false
2802 );
2803 END IF;
2804 END IF;
2805
2806 -- 2e. Validate that the parent node has no subconfiguration associations
2807 OPEN check_subconfig_assos;
2808 FETCH check_subconfig_assos INTO l_dummy_varchar;
2809 IF (check_subconfig_assos%FOUND)
2810 THEN
2811 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_PARENT_HAS_SUBMC');
2812 FND_MSG_PUB.ADD;
2813 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
2814 THEN
2815 fnd_log.message
2816 (
2817 fnd_log.level_exception,
2818 'ahl.plsql.'||G_PKG_NAME||'.Validate_Node',
2819 false
2820 );
2821 END IF;
2822 END IF;
2823 CLOSE check_subconfig_assos;
2824
2825 -- 2f. Validate that the parent node does not already have any child node with the same position reference code
2826 OPEN check_dup_pos_ref;
2827 FETCH check_dup_pos_ref INTO l_dummy_varchar;
2828 IF (check_dup_pos_ref%FOUND)
2829 THEN
2830 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_PARCHD_INVALID');
2831 FND_MESSAGE.Set_Token('CHILD', p_x_node_rec.position_ref_meaning);
2832 FND_MSG_PUB.ADD;
2833 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
2834 THEN
2835 fnd_log.message
2836 (
2837 fnd_log.level_exception,
2838 'ahl.plsql.'||G_PKG_NAME||'.Validate_Node',
2839 false
2840 );
2841 END IF;
2842 END IF;
2843 CLOSE check_dup_pos_ref;
2844 END IF;
2845 ELSIF (p_x_node_rec.operation_flag = G_DML_CREATE OR p_x_node_rec.operation_flag = G_DML_COPY)
2846 THEN
2847 -- Validate whether a root-node exists already
2848 OPEN check_topnode_exists;
2849 FETCH check_topnode_exists INTO l_dummy_varchar;
2850 IF (check_topnode_exists%FOUND)
2851 THEN
2852 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_PARENT_EXISTS');
2853 FND_MSG_PUB.ADD;
2854 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
2855 THEN
2856 fnd_log.message
2857 (
2858 fnd_log.level_exception,
2859 'ahl.plsql.'||G_PKG_NAME||'.Validate_Node',
2860 false
2861 );
2862 END IF;
2863 CLOSE check_topnode_exists;
2864 RAISE FND_API.G_EXC_ERROR;
2865 END IF;
2866 CLOSE check_topnode_exists;
2867 END IF;
2868
2869 -- Validate position reference
2870 p_x_node_rec.position_ref_meaning := RTRIM(p_x_node_rec.position_ref_meaning);
2871
2872 IF (p_x_node_rec.position_ref_meaning IS NULL)
2873 THEN
2874 -- This is a mandatory field, hence throw error
2875 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_POSREF_NULL');
2876 FND_MSG_PUB.ADD;
2877 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
2878 THEN
2879 fnd_log.message
2880 (
2881 fnd_log.level_exception,
2882 'ahl.plsql.'||G_PKG_NAME||'.Validate_Node',
2883 false
2884 );
2885 END IF;
2886 ELSE
2887 AHL_UTIL_MC_PKG.Convert_To_LookupCode
2888 (
2889 'AHL_POSITION_REFERENCE',
2890 p_x_node_rec.position_ref_meaning,
2891 p_x_node_rec.position_ref_code,
2892 l_ret_val
2893 );
2894
2895 IF NOT (l_ret_val)
2896 THEN
2897 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_POSREF_INVALID');
2898 FND_MESSAGE.Set_Token('POSREF', p_x_node_rec.position_ref_meaning);
2899 FND_MSG_PUB.ADD;
2900 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
2901 THEN
2902 fnd_log.message
2903 (
2904 fnd_log.level_exception,
2905 'ahl.plsql.'||G_PKG_NAME||'.Validate_Node',
2906 false
2907 );
2908 END IF;
2909 END IF;
2910 END IF;
2911
2912 -- Validate position Necessity
2913 IF (p_x_node_rec.position_necessity_code IS NULL)
2914 THEN
2915 -- This is a mandatory field, hence throw error
2916 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_NECESSITY_NULL');
2917 FND_MSG_PUB.ADD;
2918 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
2919 THEN
2920 fnd_log.message
2921 (
2922 fnd_log.level_exception,
2923 'ahl.plsql.'||G_PKG_NAME||'.Validate_Node',
2924 false
2925 );
2926 END IF;
2927 ELSIF NOT (AHL_UTIL_MC_PKG.Validate_Lookup_Code('AHL_POSITION_NECESSITY', p_x_node_rec.position_necessity_code))
2928 THEN
2929 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_NECESSITY_INVALID');
2930 FND_MESSAGE.Set_Token('POSREF', p_x_node_rec.position_ref_meaning);
2931 FND_MESSAGE.Set_Token('CODE', p_x_node_rec.position_necessity_code);
2932 FND_MSG_PUB.ADD;
2933 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
2934 THEN
2935 fnd_log.message
2936 (
2937 fnd_log.level_exception,
2938 'ahl.plsql.'||G_PKG_NAME||'.Validate_Node',
2939 false
2940 );
2941 END IF;
2942 END IF;
2943
2944 -- Validate item group
2945 p_x_node_rec.item_group_name := RTRIM(p_x_node_rec.item_group_name);
2946
2947 IF (p_x_node_rec.item_group_name IS NOT NULL)
2948 THEN
2949 OPEN get_item_group_id;
2950 FETCH get_item_group_id INTO p_x_node_rec.item_group_id, l_ig_type, l_ig_status;
2951 IF (get_item_group_id%NOTFOUND)
2952 THEN
2953 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_ITEMGRP_INVALID');
2954 FND_MESSAGE.Set_Token('ITEM_GRP', p_x_node_rec.item_group_name);
2955 FND_MSG_PUB.ADD;
2956 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
2957 THEN
2958 fnd_log.message
2959 (
2960 fnd_log.level_exception,
2961 'ahl.plsql.'||G_PKG_NAME||'.Validate_Node',
2962 false
2963 );
2964 END IF;
2965 ELSE
2966
2967 -- SATHAPLI::FP OGMA Issue# 105 - Non-Serialized Item Maintenance, 04-Dec-2007
2968 -- As non-serialized items having quantities greater than 1 can be associated to an item group now,
2969 -- this check should not be there to restrict them from being used in MC positions.
2970 /*
2971 -- Validate quantity = 1 for all item associations to the itemgroup
2972 OPEN check_item_assos_qty;
2973 FETCH check_item_assos_qty INTO l_dummy_varchar;
2974 IF (check_item_assos_qty%FOUND)
2975 THEN
2976 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_ITEM_ASSOS_QTY_INV');
2977 FND_MSG_PUB.ADD;
2978 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
2979 THEN
2980 fnd_log.message
2981 (
2982 fnd_log.level_exception,
2983 'ahl.plsql.'||G_PKG_NAME||'.Validate_Node',
2984 false
2985 );
2986 END IF;
2987 END IF;
2988 CLOSE check_item_assos_qty;
2989 */
2990
2991 -- Validate item group is trackable
2992 IF (l_ig_type <> 'TRACKED')
2993 THEN
2994 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_IG_NOT_TRACKED');
2995 FND_MESSAGE.Set_Token('IG_NAME', p_x_node_rec.item_group_name);
2996 FND_MSG_PUB.ADD;
2997 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
2998 THEN
2999 fnd_log.message
3000 (
3001 fnd_log.level_exception,
3002 'ahl.plsql.'||G_PKG_NAME||'.Validate_Node',
3003 false
3004 );
3005 END IF;
3006 END IF;
3007
3008 -- Validate itemgroup status is not REMOVED
3009 IF (l_ig_status = 'REMOVED')
3010 THEN
3011 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_IG_STS_INV');
3012 FND_MESSAGE.Set_Token('IG_NAME', p_x_node_rec.item_group_name);
3013 FND_MSG_PUB.ADD;
3014 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
3015 THEN
3016 fnd_log.message
3017 (
3018 fnd_log.level_exception,
3019 'ahl.plsql.'||G_PKG_NAME||'.Validate_Node',
3020 false
3021 );
3022 END IF;
3023 END IF;
3024 END IF;
3025 CLOSE get_item_group_id;
3026 ELSE
3027 -- Not a mandatory field, hence nullify ID
3028 p_x_node_rec.item_group_id := null;
3029 END IF;
3030
3031 -- Validate quantity
3032 IF (p_x_node_rec.quantity IS NULL)
3033 THEN
3034 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_QUANTITY_NULL');
3035 FND_MESSAGE.Set_Token('POSREF', p_x_node_rec.position_ref_meaning);
3036 FND_MSG_PUB.ADD;
3037 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
3038 THEN
3039 fnd_log.message
3040 (
3041 fnd_log.level_exception,
3042 'ahl.plsql.'||G_PKG_NAME||'.Validate_Node',
3043 false
3044 );
3045 END IF;
3046 ELSIF (p_x_node_rec.quantity <= 0)
3047 THEN
3048 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_QUANTITY_INVALID');
3049 FND_MESSAGE.Set_Token('QTY', p_x_node_rec.quantity);
3050 FND_MESSAGE.Set_Token('POSREF', p_x_node_rec.position_ref_meaning);
3051 FND_MSG_PUB.ADD;
3052 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
3053 THEN
3054 fnd_log.message
3055 (
3056 fnd_log.level_exception,
3057 'ahl.plsql.'||G_PKG_NAME||'.Validate_Node',
3058 false
3059 );
3060 END IF;
3061 ELSIF (p_x_node_rec.quantity > 1 AND p_x_node_rec.relationship_id IS NOT NULL)
3062 THEN
3063 OPEN check_child_exists;
3064 FETCH check_child_exists INTO l_dummy_varchar;
3065 IF (check_child_exists%FOUND)
3066 THEN
3067 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_PAR_QTY_INV');
3068 FND_MESSAGE.Set_Token('POSREF', p_x_node_rec.position_ref_meaning);
3069 FND_MSG_PUB.ADD;
3070 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
3071 THEN
3072 fnd_log.message
3073 (
3074 fnd_log.level_exception,
3075 'ahl.plsql.'||G_PKG_NAME||'.Validate_Node',
3076 false
3077 );
3078 END IF;
3079 END IF;
3080 CLOSE check_child_exists;
3081 END IF;
3082
3083 -- Validate display order
3084 IF (p_x_node_rec.display_order IS NULL)
3085 THEN
3086 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_DISPORD_NULL');
3087 FND_MESSAGE.Set_Token('POSREF', p_x_node_rec.position_ref_meaning);
3088 FND_MSG_PUB.ADD;
3089 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
3090 THEN
3091 fnd_log.message
3092 (
3093 fnd_log.level_exception,
3094 'ahl.plsql.'||G_PKG_NAME||'.Validate_Node',
3095 false
3096 );
3097 END IF;
3098 ELSIF (p_x_node_rec.display_order <= 0)
3099 THEN
3100 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_DISPORD_INVALID');
3101 FND_MESSAGE.Set_Token('DSP', p_x_node_rec.display_order);
3102 FND_MESSAGE.Set_Token('POSREF', p_x_node_rec.position_ref_meaning);
3103 FND_MSG_PUB.ADD;
3104 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
3105 THEN
3106 fnd_log.message
3107 (
3108 fnd_log.level_exception,
3109 'ahl.plsql.'||G_PKG_NAME||'.Validate_Node',
3110 false
3111 );
3112 END IF;
3113 ELSE
3114 -- Validate display_order is not equal to the same for any other node at the same level
3115 OPEN check_dup_display_order;
3116 FETCH check_dup_display_order INTO l_dummy_varchar;
3117 IF (check_dup_display_order%FOUND)
3118 THEN
3119 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_DISPORD_EXISTS');
3120 FND_MESSAGE.Set_Token('DSP', p_x_node_rec.display_order);
3121 FND_MESSAGE.Set_Token('POSREF', p_x_node_rec.position_ref_meaning);
3122 FND_MSG_PUB.ADD;
3123 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
3124 THEN
3125 fnd_log.message
3126 (
3127 fnd_log.level_exception,
3128 'ahl.plsql.'||G_PKG_NAME||'.Validate_Node',
3129 false
3130 );
3131 END IF;
3132 END IF;
3133 CLOSE check_dup_display_order;
3134 END IF;
3135
3136 -- Validate UOM
3137 IF (p_x_node_rec.uom_code IS NULL)
3138 THEN
3139 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_UOM_NULL');
3140 FND_MESSAGE.Set_Token('POSREF', p_x_node_rec.position_ref_meaning);
3141 FND_MSG_PUB.ADD;
3142 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
3143 THEN
3144 fnd_log.message
3145 (
3146 fnd_log.level_exception,
3147 'ahl.plsql.'||G_PKG_NAME||'.Validate_Node',
3148 false
3149 );
3150 END IF;
3151 ELSE
3152 OPEN check_uom_exists(p_x_node_rec.uom_code);
3153 FETCH check_uom_exists INTO l_dummy_varchar;
3154 IF (check_uom_exists%NOTFOUND)
3155 THEN
3156 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_UOM_INVALID');
3157 FND_MESSAGE.Set_Token('POSREF', p_x_node_rec.position_ref_meaning);
3158 FND_MESSAGE.Set_Token('UOM', p_x_node_rec.uom_code);
3159 FND_MSG_PUB.ADD;
3160 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
3161 THEN
3162 fnd_log.message
3163 (
3164 fnd_log.level_exception,
3165 'ahl.plsql.'||G_PKG_NAME||'.Validate_Node',
3166 false
3167 );
3168 END IF;
3169 END IF;
3170 CLOSE check_uom_exists;
3171 END IF;
3172
3173 -- Validate dates
3174 -- ##TAMAL## Date validations may fail in the case of creating a node initiated from a copy_node / copy_mc /
3175 -- create_mc_revision call since expired nodes are to be created, thus do not perform date validations for
3176 -- such a case. For any such copy operation, the p_x_node_rec.operation_flag = G_DML_COPY instead of
3177 -- G_DML_CREATE, and thus date validations may be avoided for such a case.
3178 IF (p_x_node_rec.operation_flag = G_DML_UPDATE)
3179 THEN
3180 OPEN get_node_dates;
3181 FETCH get_node_dates INTO l_start_date, l_end_date;
3182 CLOSE get_node_dates;
3183
3184 IF (G_TRUNC_DATE >= trunc(nvl(l_end_date, G_SYSDATE + 1)))
3185 THEN
3186 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_NODE_DATE_INV');
3187 FND_MSG_PUB.ADD;
3188 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
3189 THEN
3190 fnd_log.message
3191 (
3192 fnd_log.level_exception,
3193 'ahl.plsql.'||G_PKG_NAME||'.Validate_Node',
3194 false
3195 );
3196 END IF;
3197 END IF;
3198
3199 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)
3200 THEN
3201 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_START_DATE_INV');
3202 FND_MSG_PUB.ADD;
3203 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
3204 THEN
3205 fnd_log.message
3206 (
3207 fnd_log.level_exception,
3208 'ahl.plsql.'||G_PKG_NAME||'.Validate_Node',
3209 false
3210 );
3211 END IF;
3212 END IF;
3213
3214 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)
3215 THEN
3216 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_END_DATE_INV');
3217 FND_MSG_PUB.ADD;
3218 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
3219 THEN
3220 fnd_log.message
3221 (
3222 fnd_log.level_exception,
3223 'ahl.plsql.'||G_PKG_NAME||'.Validate_Node',
3224 false
3225 );
3226 END IF;
3227 END IF;
3228 ELSIF (p_x_node_rec.operation_flag = G_DML_CREATE)
3229 THEN
3230 IF (trunc(nvl(p_x_node_rec.active_start_date, G_SYSDATE)) < G_TRUNC_DATE)
3231 THEN
3232 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_START_DATE_INV');
3233 FND_MSG_PUB.ADD;
3234 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
3235 THEN
3236 fnd_log.message
3237 (
3238 fnd_log.level_exception,
3239 'ahl.plsql.'||G_PKG_NAME||'.Validate_Node',
3240 false
3241 );
3242 END IF;
3243 END IF;
3244
3245 IF (trunc(nvl(p_x_node_rec.active_end_date, G_SYSDATE + 1)) <= G_TRUNC_DATE)
3246 THEN
3247 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_END_DATE_INV');
3248 FND_MSG_PUB.ADD;
3249 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
3250 THEN
3251 fnd_log.message
3252 (
3253 fnd_log.level_exception,
3254 'ahl.plsql.'||G_PKG_NAME||'.Validate_Node',
3255 false
3256 );
3257 END IF;
3258 END IF;
3259 END IF;
3260
3261 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)))
3262 THEN
3263 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_START_END_INV');
3264 FND_MSG_PUB.ADD;
3265 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
3266 THEN
3267 fnd_log.message
3268 (
3269 fnd_log.level_exception,
3270 'ahl.plsql.'||G_PKG_NAME||'.Validate_Node',
3271 false
3272 );
3273 END IF;
3274 END IF;
3275
3276 END Validate_Node;
3277
3278 PROCEDURE Validate_Counter_Exists
3279 (
3280 p_ctr_rule_id in number,
3281 p_object_ver_num in number
3282 )
3283 IS
3284
3285 CURSOR check_counter_exists
3286 IS
3287 SELECT object_version_number
3288 FROM ahl_ctr_update_rules
3289 WHERE ctr_update_rule_id = p_ctr_rule_id;
3290
3291 BEGIN
3292
3293 OPEN check_counter_exists;
3294 FETCH check_counter_exists INTO l_dummy_number;
3295 IF (check_counter_exists%NOTFOUND)
3296 THEN
3297 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_CTR_NOT_FOUND');
3298 FND_MSG_PUB.ADD;
3299 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
3300 THEN
3301 fnd_log.message
3302 (
3303 fnd_log.level_exception,
3304 'ahl.plsql.'||G_PKG_NAME||'.Validate_Counter_Exists',
3305 false
3306 );
3307 END IF;
3308 CLOSE check_counter_exists;
3309 RAISE FND_API.G_EXC_ERROR;
3310 ELSIF (NVL(p_object_ver_num, l_dummy_number) <> l_dummy_number)
3311 THEN
3312 FND_MESSAGE.Set_Name('AHL', 'AHL_COM_RECORD_CHANGED');
3313 FND_MSG_PUB.ADD;
3314 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
3315 THEN
3316 fnd_log.message
3317 (
3318 fnd_log.level_exception,
3319 'ahl.plsql.'||G_PKG_NAME||'.Validate_Counter_Exists',
3320 false
3321 );
3322 END IF;
3323 CLOSE check_counter_exists;
3324 RAISE FND_API.G_EXC_ERROR;
3325 END IF;
3326 CLOSE check_counter_exists;
3327
3328 END Validate_Counter_Exists;
3329
3330 PROCEDURE Validate_Counter_Rule
3331 (
3332 p_counter_rule_rec in Counter_Rule_Rec_Type
3333 )
3334 IS
3335 -- Define cursor get_node_posref to read the position reference of the MC node, used for displaying errors
3336 CURSOR get_node_posref
3337 IS
3338 SELECT position_ref_meaning
3339 FROM ahl_mc_relationships_v
3340 WHERE relationship_id = p_counter_rule_rec.relationship_id;
3341
3342 -- Define cursor check_uom_rule to check whether the same combination of UOM and rule
3343 -- already exists for the node or not
3344 CURSOR check_uom_rule
3345 IS
3346 SELECT 'x'
3347 FROM ahl_ctr_update_rules
3348 WHERE relationship_id = p_counter_rule_rec.relationship_id AND
3349 rule_code = p_counter_rule_rec.rule_code AND
3350 uom_code = p_counter_rule_rec.uom_code AND
3351 ctr_update_rule_id <> nvl(p_counter_rule_rec.ctr_update_rule_id, -1);
3352
3353 -- Declare local variables
3354 l_posref_meaning VARCHAR2(80);
3355
3356 BEGIN
3357 OPEN get_node_posref;
3358 FETCH get_node_posref INTO l_posref_meaning;
3359 CLOSE get_node_posref;
3360
3361 -- Validate p_counter_rule_rec.uom_code
3362 IF (p_counter_rule_rec.uom_code IS NULL)
3363 THEN
3364 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_UOM_NULL');
3365 FND_MESSAGE.Set_Token('POSREF', l_posref_meaning);
3366 FND_MSG_PUB.ADD;
3367 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
3368 THEN
3369 fnd_log.message
3370 (
3371 fnd_log.level_exception,
3372 'ahl.plsql.'||G_PKG_NAME||'.Validate_Counter_Rule',
3373 false
3374 );
3375 END IF;
3376 ELSE
3377 OPEN check_uom_exists(p_counter_rule_rec.uom_code);
3378 FETCH check_uom_exists INTO l_dummy_varchar;
3379 IF (check_uom_exists%NOTFOUND)
3380 THEN
3381 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_UOM_INVALID');
3382 FND_MESSAGE.Set_Token('POSREF', l_posref_meaning);
3383 FND_MESSAGE.Set_Token('UOM', p_counter_rule_rec.uom_code);
3384 FND_MSG_PUB.ADD;
3385 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
3386 THEN
3387 fnd_log.message
3388 (
3389 fnd_log.level_exception,
3390 'ahl.plsql.'||G_PKG_NAME||'.Validate_Counter_Rule',
3391 false
3392 );
3393 END IF;
3394 END IF;
3395 CLOSE check_uom_exists;
3396 END IF;
3397
3398 -- Validate p_counter_rule_rec.rule_code
3399 IF (p_counter_rule_rec.rule_code IS NULL)
3400 THEN
3401 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_RCODE_NULL');
3402 FND_MESSAGE.Set_Token('POSREF', l_posref_meaning);
3403 FND_MSG_PUB.ADD;
3404 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
3405 THEN
3406 fnd_log.message
3407 (
3408 fnd_log.level_exception,
3409 'ahl.plsql.'||G_PKG_NAME||'.Validate_Counter_Rule',
3410 false
3411 );
3412 END IF;
3413 ELSIF NOT(AHL_UTIL_MC_PKG.Validate_Lookup_Code('AHL_COUNTER_RULE_TYPE', p_counter_rule_rec.rule_code))
3414 THEN
3415 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_RCODE_INVALID');
3416 FND_MESSAGE.Set_Token('POSREF', l_posref_meaning);
3417 FND_MESSAGE.Set_Token('RULE_CODE', p_counter_rule_rec.rule_meaning);
3418 FND_MSG_PUB.ADD;
3419 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
3420 THEN
3421 fnd_log.message
3422 (
3423 fnd_log.level_exception,
3424 'ahl.plsql.'||G_PKG_NAME||'.Validate_Counter_Rule',
3425 false
3426 );
3427 END IF;
3428 END IF;
3429
3430 -- Validate whether the same combination of UOM and Rule does not exist for this node
3431 OPEN check_uom_rule;
3432 FETCH check_uom_rule INTO l_dummy_varchar;
3433 IF (check_uom_rule%FOUND)
3434 THEN
3435 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_CTRRULE_EXISTS');
3436 FND_MESSAGE.Set_Token('POSREF', l_posref_meaning);
3437 FND_MESSAGE.Set_Token('UOM', p_counter_rule_rec.uom_code);
3438 FND_MESSAGE.Set_Token('RULE', p_counter_rule_rec.rule_code);
3439 FND_MSG_PUB.ADD;
3440 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
3441 THEN
3442 fnd_log.message
3443 (
3444 fnd_log.level_exception,
3445 'ahl.plsql.'||G_PKG_NAME||'.Validate_Counter_Rule',
3446 false
3447 );
3448 END IF;
3449 END IF;
3450 CLOSE check_uom_rule;
3451
3452 -- Validate counter rule ratio is a positive number
3453 IF (nvl(p_counter_rule_rec.ratio, 0) <= 0)
3454 THEN
3455 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_RATIO_INVALID');
3456 FND_MESSAGE.Set_Token('POSREF', l_posref_meaning);
3457 FND_MESSAGE.Set_Token('RATIO', p_counter_rule_rec.ratio);
3458 FND_MSG_PUB.ADD;
3459 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
3460 THEN
3461 fnd_log.message
3462 (
3463 fnd_log.level_exception,
3464 'ahl.plsql.'||G_PKG_NAME||'.Validate_Counter_Rule',
3465 false
3466 );
3467 END IF;
3468 END IF;
3469
3470 END Validate_Counter_Rule;
3471
3472 PROCEDURE Validate_Subconfig_Exists
3473 (
3474 p_submc_assos_id in number,
3475 p_object_ver_num in number
3476 )
3477 IS
3478
3479 CURSOR check_submc_exists
3480 IS
3481 SELECT object_version_number
3482 FROM ahl_mc_config_relations
3483 WHERE mc_config_relation_id = p_submc_assos_id;
3484
3485 BEGIN
3486
3487 OPEN check_submc_exists;
3488 FETCH check_submc_exists INTO l_dummy_number;
3489 IF (check_submc_exists%NOTFOUND)
3490 THEN
3491 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_SUBMC_NOT_FOUND');
3492 FND_MSG_PUB.ADD;
3493 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
3494 THEN
3495 fnd_log.message
3496 (
3497 fnd_log.level_exception,
3498 'ahl.plsql.'||G_PKG_NAME||'.Validate_Subconfig_Exists',
3499 false
3500 );
3501 END IF;
3502 CLOSE check_submc_exists;
3503 RAISE FND_API.G_EXC_ERROR;
3504 ELSIF (NVL(p_object_ver_num, l_dummy_number) <> l_dummy_number)
3505 THEN
3506 FND_MESSAGE.Set_Name('AHL', 'AHL_COM_RECORD_CHANGED');
3507 FND_MSG_PUB.ADD;
3508 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
3509 THEN
3510 fnd_log.message
3511 (
3512 fnd_log.level_exception,
3513 'ahl.plsql.'||G_PKG_NAME||'.Validate_Subconfig_Exists',
3514 false
3515 );
3516 END IF;
3517 CLOSE check_submc_exists;
3518 RAISE FND_API.G_EXC_ERROR;
3519 END IF;
3520 CLOSE check_submc_exists;
3521
3522 END Validate_Subconfig_Exists;
3523
3524
3525 PROCEDURE Validate_priority
3526 (
3527 p_subconfig_tbl in Subconfig_Tbl_Type
3528 ) IS
3529
3530 CURSOR check_priority_dup_exists
3531 IS
3532 SELECT priority
3533 FROM ahl_mc_config_relations
3534 WHERE relationship_id = p_subconfig_tbl(1).relationship_id
3535 group by priority
3536 having count(mc_config_relation_id) > 1;
3537
3538 l_priority NUMBER;
3539
3540 BEGIN
3541
3542
3543 OPEN check_priority_dup_exists;
3544 FETCH check_priority_dup_exists INTO l_priority;
3545 IF (check_priority_dup_exists%FOUND)
3546 THEN
3547 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_PRIORITY_NON_UNIQUE');
3548 FND_MSG_PUB.ADD;
3549 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
3550 THEN
3551 fnd_log.message
3552 (
3553 fnd_log.level_exception,
3554 'ahl.plsql.'||G_PKG_NAME||'.Validate_priority',
3555 true
3556 );
3557 END IF;
3558 CLOSE check_priority_dup_exists;
3559 RAISE FND_API.G_EXC_ERROR;
3560 END IF;
3561
3562 END Validate_priority;
3563
3564 /* Jerry commented out on 08/12/2004 because it is never used
3565 PROCEDURE Check_Cyclic_Rel
3566 (
3567 p_subconfig_id in number,
3568 p_rel_id in number
3569 )
3570 IS
3571
3572 -- Define cursor check_cyclic_rel_csr to establish a parent-child relationship between the MC in question and
3573 -- subconfigs associated with its nodes (down to the last level), then search for the subconfig in question
3574 -- from that list
3575 CURSOR check_cyclic_rel_csr
3576 IS
3577 SELECT 'x'
3578 FROM
3579 (
3580 -- Establish parent-child relationship between subconfiguration associations
3581 -- and the MC to which they are associated
3582 SELECT submc.mc_header_id child, node.mc_header_id parent
3583 FROM ahl_mc_config_relations submc, ahl_mc_relationships node
3584 WHERE submc.relationship_id = node.relationship_id
3585 CONNECT BY node.mc_header_id = PRIOR submc.mc_header_id
3586 START WITH node.mc_header_id = p_subconfig_id
3587 ) submc_tree, ahl_mc_relationships mc_node
3588 WHERE submc_tree.child = mc_node.mc_header_id AND
3589 mc_node.relationship_id = p_rel_id;
3590
3591 -- Define cursor get_node_mc_details to read detail of the MC of a MC node
3592 CURSOR get_node_mc_details
3593 IS
3594 SELECT mch.name
3595 FROM ahl_mc_headers_b mch, ahl_mc_relationships mcr
3596 WHERE mch.mc_header_id = mcr.mc_header_id AND
3597 mcr.relationship_id = p_rel_id;
3598
3599 -- Define cursor get_mc_details to read detail of a MC
3600 CURSOR get_mc_details
3601 IS
3602 SELECT name
3603 FROM ahl_mc_headers_b
3604 WHERE mc_header_id = p_subconfig_id;
3605
3606 -- Define local variables
3607 l_mc_name VARCHAR2(80);
3608 l_submc_name VARCHAR2(80);
3609
3610 BEGIN
3611 OPEN check_cyclic_rel_csr;
3612 FETCH check_cyclic_rel_csr INTO l_dummy_varchar;
3613 IF (check_cyclic_rel_csr%FOUND)
3614 THEN
3615 OPEN get_node_mc_details;
3616 FETCH get_node_mc_details INTO l_mc_name;
3617 CLOSE get_node_mc_details;
3618
3619 OPEN get_mc_details;
3620 FETCH get_mc_details INTO l_submc_name;
3621 CLOSE get_mc_details;
3622
3623 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_CYCLIC_REL_EXIST');
3624 FND_MESSAGE.Set_Token('MC', l_mc_name);
3625 FND_MESSAGE.Set_Token('SUBMC', l_submc_name);
3626 FND_MSG_PUB.ADD;
3627 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
3628 THEN
3629 fnd_log.message
3630 (
3631 fnd_log.level_exception,
3632 'ahl.plsql.'||G_PKG_NAME||'.Check_Cyclic_Rel',
3633 false
3634 );
3635 END IF;
3636 END IF;
3637 CLOSE check_cyclic_rel_csr;
3638
3639 END Check_Cyclic_Rel;
3640 */
3641
3642 -------------------------
3643 -- Non-spec Procedures --
3644 -------------------------
3645 FUNCTION Get_MC_Status
3646 (
3647 p_rel_id in number,
3648 p_mc_header_id in number
3649 )
3650 RETURN VARCHAR2
3651 IS
3652 CURSOR get_mc_status
3653 IS
3654 SELECT config_status_code
3655 FROM ahl_mc_headers_v
3656 WHERE mc_header_id = p_mc_header_id;
3657
3658 CURSOR get_node_mc_status
3659 IS
3660 SELECT mch.config_status_code
3661 FROM ahl_mc_headers_v mch, ahl_mc_relationships mcr
3662 WHERE mch.mc_header_id = mcr.mc_header_id AND
3663 mcr.relationship_id = p_rel_id;
3664
3665 l_status VARCHAR2(30);
3666
3667 BEGIN
3668
3669 IF (p_rel_id IS NOT NULL)
3670 THEN
3671 OPEN get_node_mc_status;
3672 FETCH get_node_mc_status INTO l_status;
3673 IF (get_node_mc_status%NOTFOUND)
3674 THEN
3675 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_NOT_FOUND');
3676 FND_MSG_PUB.ADD;
3677 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
3678 THEN
3679 fnd_log.message
3680 (
3681 fnd_log.level_exception,
3682 'ahl.plsql.'||G_PKG_NAME||'.Get_MC_Status',
3683 false
3684 );
3685 END IF;
3686 CLOSE get_node_mc_status;
3687 RAISE FND_API.G_EXC_ERROR;
3688 END IF;
3689 CLOSE get_node_mc_status;
3690 ELSIF (p_mc_header_id IS NOT NULL)
3691 THEN
3692 OPEN get_mc_status;
3693 FETCH get_mc_status INTO l_status;
3694 IF (get_mc_status%NOTFOUND)
3695 THEN
3696 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_NOT_FOUND');
3697 FND_MSG_PUB.ADD;
3698 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
3699 THEN
3700 fnd_log.message
3701 (
3702 fnd_log.level_exception,
3703 'ahl.plsql.'||G_PKG_NAME||'.Get_MC_Status',
3704 false
3705 );
3706 END IF;
3707 CLOSE get_mc_status;
3708 RAISE FND_API.G_EXC_ERROR;
3709 END IF;
3710 CLOSE get_mc_status;
3711 END IF;
3712
3713 return l_status;
3714
3715 END Get_MC_Status;
3716 -- Returns true if the MC , p_subconfig_id contains the MC p_dest_config_id, as
3717 -- a subconfig some where down the tree.
3718 FUNCTION Cyclic_Relation_Exists
3719 (
3720 p_subconfig_id in number,
3721 p_dest_config_id in number
3722 )
3723 RETURN BOOLEAN
3724 IS
3725 -- Define local variables
3726 TYPE subconfig_Tbl_Type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
3727 l_subconfigs_table subconfig_Tbl_Type;
3728 l_dummy_varchar VARCHAR2(1);
3729 -- cursor to check whether the Cyclic relation really exist.
3730 -- SATHAPLI::Bug 9020738, 25-Mar-2010, re-defined the cursor, as hierarchical query is not needed here.
3731 -- Also, changed IN to EXISTS.
3732 CURSOR CHECK_RELATIONS_CYCLE
3733 IS
3734 /* SELECT 'X' FROM ahl_mc_config_relations
3735 WHERE mc_header_id = p_dest_config_id
3736 AND relationship_id IN
3737 ( SELECT relationship_id from ahl_mc_relationships
3738 WHERE mc_header_id = p_subconfig_id
3739 START WITH parent_relationship_id IS NULL
3740 CONNECT BY parent_relationship_id = prior relationship_id);
3741 */
3742 SELECT 'X'
3743 FROM ahl_mc_config_relations cnr
3744 WHERE cnr.mc_header_id = p_dest_config_id
3745 AND EXISTS
3746 ( SELECT 'X'
3747 FROM ahl_mc_relationships mcr
3748 WHERE mcr.mc_header_id = p_subconfig_id
3749 AND mcr.relationship_id = cnr.relationship_id );
3750
3751 BEGIN
3752 -- check whether cycle is there
3753 OPEN CHECK_RELATIONS_CYCLE;
3754 FETCH CHECK_RELATIONS_CYCLE INTO l_dummy_varchar;
3755 IF (CHECK_RELATIONS_CYCLE%FOUND) THEN
3756 CLOSE CHECK_RELATIONS_CYCLE;
3757 -- Cycle is found
3758 RETURN TRUE;
3759 ELSE
3760 CLOSE CHECK_RELATIONS_CYCLE;
3761 -- get the next level of subconfigs
3762 -- SATHAPLI::Bug 9020738, 25-Mar-2010, re-defined the query, as hierarchical query is not needed here.
3763 -- Also, changed IN to EXISTS.
3764 /* SELECT mc_header_id bulk collect
3765 INTO l_subconfigs_table
3766 FROM ahl_mc_config_relations WHERE relationship_id IN
3767 ( SELECT relationship_id FROM ahl_mc_relationships
3768 WHERE mc_header_id = p_subconfig_id
3769 START WITH parent_relationship_id IS NULL
3770 CONNECT BY parent_relationship_id = prior relationship_id );
3771 */
3772 SELECT mc_header_id BULK COLLECT
3773 INTO l_subconfigs_table
3774 FROM ahl_mc_config_relations cnr
3775 WHERE EXISTS
3776 ( SELECT 'X'
3777 FROM ahl_mc_relationships mcr
3778 WHERE mcr.mc_header_id = p_subconfig_id
3779 AND mcr.relationship_id = cnr.relationship_id );
3780
3781 IF ( l_subconfigs_table.COUNT > 0 ) THEN
3782 FOR i IN l_subconfigs_table.FIRST..l_subconfigs_table.LAST LOOP
3783 IF Cyclic_Relation_Exists(l_subconfigs_table(i),p_dest_config_id) THEN
3784 RETURN TRUE;
3785 END IF;
3786 END LOOP;
3787 END IF;
3788 END IF;
3789 RETURN FALSE;
3790 END Cyclic_Relation_Exists;
3791
3792 PROCEDURE Set_Header_Status
3793 (
3794 p_rel_id IN NUMBER
3795 )
3796 IS
3797
3798 CURSOR get_mc_header_status
3799 (
3800 p_rel_id in number
3801 )
3802 IS
3803 SELECT mch.mc_header_id, mch.config_status_code
3804 FROM ahl_mc_headers_b mch, ahl_mc_relationships mcr
3805 WHERE mch.mc_header_id = mcr.mc_header_id AND
3806 mcr.relationship_id = p_rel_id;
3807
3808 l_mc_header_id NUMBER;
3809 l_status VARCHAR2(30) := 'DRAFT';
3810
3811 BEGIN
3812
3813 OPEN get_mc_header_status(p_rel_id);
3814 FETCH get_mc_header_status INTO l_mc_header_id, l_status;
3815
3816 IF (get_mc_header_status%FOUND)
3817 THEN
3818 IF (l_status = 'APPROVAL_REJECTED')
3819 THEN
3820 UPDATE ahl_mc_headers_b
3821 SET config_status_code = 'DRAFT'
3822 WHERE mc_header_id = l_mc_header_id;
3823 END IF;
3824 END IF;
3825
3826 CLOSE get_mc_header_status;
3827
3828 END Set_Header_Status;
3829
3830 PROCEDURE Create_Counter_Rule
3831 (
3832 p_x_counter_rule_rec IN OUT NOCOPY Counter_Rule_Rec_Type
3833 )
3834 IS
3835 -- Define local variables
3836 l_api_name CONSTANT VARCHAR2(30) := 'Create_Counter_Rule';
3837 l_msg_count NUMBER;
3838
3839 l_posref_meaning VARCHAR2(80);
3840
3841 BEGIN
3842
3843 -- API body starts here
3844 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
3845 THEN
3846 fnd_log.string
3847 (
3848 fnd_log.level_procedure,
3849 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
3850 'At the start of PLSQL procedure'
3851 );
3852 END IF;
3853
3854 -- Validate p_x_counter_rule_rec.relationship_id exists
3855 Validate_Node_Exists(p_x_counter_rule_rec.relationship_id, null);
3856
3857 -- Validate UOM, Rule and Ratio for the counter rule
3858 Validate_Counter_Rule(p_x_counter_rule_rec);
3859
3860 -- Check Error Message stack.
3861 l_msg_count := FND_MSG_PUB.count_msg;
3862 IF l_msg_count > 0 THEN
3863 RAISE FND_API.G_EXC_ERROR;
3864 END IF;
3865
3866 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
3867 THEN
3868 fnd_log.string
3869 (
3870 fnd_log.level_statement,
3871 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
3872 'Counter rule validation successful'
3873 );
3874 END IF;
3875
3876 SELECT ahl_ctr_update_rules_s.nextval INTO p_x_counter_rule_rec.ctr_update_rule_id FROM DUAL;
3877 p_x_counter_rule_rec.object_version_number := 1;
3878 p_x_counter_rule_rec.security_group_id := null;
3879
3880 INSERT INTO AHL_CTR_UPDATE_RULES
3881 (
3882 CTR_UPDATE_RULE_ID,
3883 RELATIONSHIP_ID,
3884 UOM_CODE,
3885 RULE_CODE,
3886 RATIO,
3887 OBJECT_VERSION_NUMBER,
3888 SECURITY_GROUP_ID,
3889 ATTRIBUTE_CATEGORY,
3890 ATTRIBUTE1,
3891 ATTRIBUTE2,
3892 ATTRIBUTE3,
3893 ATTRIBUTE4,
3894 ATTRIBUTE5,
3895 ATTRIBUTE6,
3896 ATTRIBUTE7,
3897 ATTRIBUTE8,
3898 ATTRIBUTE9,
3899 ATTRIBUTE10,
3900 ATTRIBUTE11,
3901 ATTRIBUTE12,
3902 ATTRIBUTE13,
3903 ATTRIBUTE14,
3904 ATTRIBUTE15,
3905 LAST_UPDATE_DATE,
3906 LAST_UPDATED_BY,
3907 CREATION_DATE,
3908 CREATED_BY,
3909 LAST_UPDATE_LOGIN
3910 )
3911 VALUES
3912 (
3913 p_x_counter_rule_rec.ctr_update_rule_id,
3914 p_x_counter_rule_rec.relationship_id,
3915 p_x_counter_rule_rec.uom_code,
3916 p_x_counter_rule_rec.rule_code,
3917 p_x_counter_rule_rec.ratio,
3918 p_x_counter_rule_rec.object_version_number,
3919 p_x_counter_rule_rec.security_group_id,
3920 p_x_counter_rule_rec.ATTRIBUTE_CATEGORY,
3921 p_x_counter_rule_rec.ATTRIBUTE1,
3922 p_x_counter_rule_rec.ATTRIBUTE2,
3923 p_x_counter_rule_rec.ATTRIBUTE3,
3924 p_x_counter_rule_rec.ATTRIBUTE4,
3925 p_x_counter_rule_rec.ATTRIBUTE5,
3926 p_x_counter_rule_rec.ATTRIBUTE6,
3927 p_x_counter_rule_rec.ATTRIBUTE7,
3928 p_x_counter_rule_rec.ATTRIBUTE8,
3929 p_x_counter_rule_rec.ATTRIBUTE9,
3930 p_x_counter_rule_rec.ATTRIBUTE10,
3931 p_x_counter_rule_rec.ATTRIBUTE11,
3932 p_x_counter_rule_rec.ATTRIBUTE12,
3933 p_x_counter_rule_rec.ATTRIBUTE13,
3934 p_x_counter_rule_rec.ATTRIBUTE14,
3935 p_x_counter_rule_rec.ATTRIBUTE15,
3936 G_SYSDATE,
3937 G_USER_ID,
3938 G_SYSDATE,
3939 G_USER_ID,
3940 G_LOGIN_ID
3941 );
3942
3943 -- API body ends here
3944
3945 END Create_Counter_Rule;
3946
3947 PROCEDURE Modify_Counter_Rule
3948 (
3949 p_x_counter_rule_rec IN OUT NOCOPY Counter_Rule_Rec_Type
3950 )
3951 IS
3952
3953 -- Define cursor get_node_posref to read the position reference of the MC node, used for displaying errors
3954 CURSOR get_node_posref
3955 IS
3956 SELECT position_ref_meaning
3957 FROM ahl_mc_relationships_v
3958 WHERE relationship_id = p_x_counter_rule_rec.relationship_id;
3959
3960 -- Define cursor check_uom_rule to check whether the same combination of UOM and rule
3961 -- already exists for the node or not
3962 CURSOR check_uom_rule
3963 IS
3964 SELECT 'x'
3965 FROM ahl_ctr_update_rules
3966 WHERE relationship_id = p_x_counter_rule_rec.relationship_id AND
3967 rule_code = p_x_counter_rule_rec.rule_code AND
3968 uom_code = p_x_counter_rule_rec.uom_code AND
3969 ctr_update_rule_id <> p_x_counter_rule_rec.ctr_update_rule_id;
3970
3971 -- Define local variables
3972 l_api_name CONSTANT VARCHAR2(30) := 'Modify_Counter_Rule';
3973 l_msg_count NUMBER;
3974
3975 l_posref_meaning VARCHAR2(80);
3976
3977 BEGIN
3978
3979 -- API body starts here
3980 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
3981 THEN
3982 fnd_log.string
3983 (
3984 fnd_log.level_procedure,
3985 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||'.begin',
3986 'At the start of PLSQL procedure'
3987 );
3988 END IF;
3989
3990 -- Validate p_x_counter_rule_rec.relationship_id exists
3991 Validate_Node_Exists(p_x_counter_rule_rec.relationship_id, null);
3992
3993 -- Validate p_x_counter_rule_rec.ctr_update_rule_id exists
3994 Validate_Counter_Exists(p_x_counter_rule_rec.ctr_update_rule_id, nvl(p_x_counter_rule_rec.object_version_number, 0));
3995
3996 -- Validate UOM, Rule and Ratio for the counter rule
3997 Validate_Counter_Rule(p_x_counter_rule_rec);
3998
3999 -- Check Error Message stack.
4000 l_msg_count := FND_MSG_PUB.count_msg;
4001 IF l_msg_count > 0 THEN
4002 RAISE FND_API.G_EXC_ERROR;
4003 END IF;
4004
4005 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
4006 THEN
4007 fnd_log.string
4008 (
4009 fnd_log.level_statement,
4010 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
4011 'Counter rule validation successful'
4012 );
4013 END IF;
4014
4015 p_x_counter_rule_rec.object_version_number := p_x_counter_rule_rec.object_version_number + 1;
4016
4017 UPDATE AHL_CTR_UPDATE_RULES
4018 SET RATIO = p_x_counter_rule_rec.RATIO,
4019 RULE_CODE = p_x_counter_rule_rec.RULE_CODE,
4020 OBJECT_VERSION_NUMBER = p_x_counter_rule_rec.OBJECT_VERSION_NUMBER,
4021 SECURITY_GROUP_ID = p_x_counter_rule_rec.SECURITY_GROUP_ID,
4022 ATTRIBUTE_CATEGORY = p_x_counter_rule_rec.ATTRIBUTE_CATEGORY,
4023 ATTRIBUTE1 = p_x_counter_rule_rec.ATTRIBUTE1,
4024 ATTRIBUTE2 = p_x_counter_rule_rec.ATTRIBUTE2,
4025 ATTRIBUTE3 = p_x_counter_rule_rec.ATTRIBUTE3,
4026 ATTRIBUTE4 = p_x_counter_rule_rec.ATTRIBUTE4,
4027 ATTRIBUTE5 = p_x_counter_rule_rec.ATTRIBUTE5,
4028 ATTRIBUTE6 = p_x_counter_rule_rec.ATTRIBUTE6,
4029 ATTRIBUTE7 = p_x_counter_rule_rec.ATTRIBUTE7,
4030 ATTRIBUTE8 = p_x_counter_rule_rec.ATTRIBUTE8,
4031 ATTRIBUTE9 = p_x_counter_rule_rec.ATTRIBUTE9,
4032 ATTRIBUTE10 = p_x_counter_rule_rec.ATTRIBUTE10,
4033 ATTRIBUTE11 = p_x_counter_rule_rec.ATTRIBUTE11,
4034 ATTRIBUTE12 = p_x_counter_rule_rec.ATTRIBUTE12,
4035 ATTRIBUTE13 = p_x_counter_rule_rec.ATTRIBUTE13,
4036 ATTRIBUTE14 = p_x_counter_rule_rec.ATTRIBUTE14,
4037 ATTRIBUTE15 = p_x_counter_rule_rec.ATTRIBUTE15,
4038 LAST_UPDATE_DATE = G_SYSDATE,
4039 LAST_UPDATED_BY = G_USER_ID,
4040 LAST_UPDATE_LOGIN = G_LOGIN_ID
4041 WHERE CTR_UPDATE_RULE_ID = p_x_counter_rule_rec.CTR_UPDATE_RULE_ID;
4042
4043 -- API body ends here
4044
4045 END Modify_Counter_Rule;
4046
4047 PROCEDURE Delete_Counter_Rule
4048 (
4049 p_ctr_update_rule_id IN NUMBER,
4050 p_object_ver_num IN NUMBER
4051 )
4052 IS
4053
4054 -- Declare local variables
4055 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Counter_Rule';
4056
4057 BEGIN
4058
4059 -- API body starts here
4060 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
4061 THEN
4062 fnd_log.string
4063 (
4064 fnd_log.level_procedure,
4065 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||'.begin',
4066 'At the start of PLSQL procedure'
4067 );
4068 END IF;
4069
4070 -- Validate p_ctr_update_rule_id exists
4071 Validate_Counter_Exists(p_ctr_update_rule_id, nvl(p_object_ver_num, 0));
4072
4073 DELETE FROM ahl_ctr_update_rules
4074 WHERE ctr_update_rule_id = p_ctr_update_rule_id;
4075
4076 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
4077 THEN
4078 fnd_log.string
4079 (
4080 fnd_log.level_procedure,
4081 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||'.end',
4082 'At the end of PLSQL procedure'
4083 );
4084 END IF;
4085 -- API body ends here
4086
4087 END Delete_Counter_Rule;
4088
4089 PROCEDURE Attach_Subconfig
4090 (
4091 p_x_subconfig_rec IN OUT NOCOPY Subconfig_Rec_Type
4092 )
4093 IS
4094 -- Define cursor check_submc_exists to check whether the subconfiguration association already exists for this node
4095 CURSOR check_submc_exists
4096 IS
4097 SELECT name
4098 FROM ahl_mc_config_relations submc, ahl_mc_headers_b mch
4099 WHERE submc.mc_header_id = mch.mc_header_id AND
4100 submc.relationship_id = p_x_subconfig_rec.relationship_id AND
4101 submc.mc_header_id = p_x_subconfig_rec.mc_header_id;
4102 -- Since expired subconfig associations can be unexpired, so no need to filter on active_end_date
4103 -- AND G_TRUNC_DATE < trunc(nvl(submc.active_end_date, G_SYSDATE + 1));
4104
4105 -- Define check_root_node to check whether the node to which subconfiguration is being associated is not a topnode of a MC
4106 CURSOR check_root_node
4107 IS
4108 SELECT 'x'
4109 FROM ahl_mc_relationships
4110 WHERE parent_relationship_id is null AND
4111 relationship_id = p_x_subconfig_rec.relationship_id;
4112
4113 -- Define check_leaf_node to check whether the node to which subconfiguration is being associated is a leaf node
4114 CURSOR check_leaf_node
4115 IS
4116 SELECT 'x'
4117 FROM ahl_mc_relationships
4118 WHERE parent_relationship_id = p_x_subconfig_rec.relationship_id AND
4119 G_TRUNC_DATE < trunc(nvl(active_end_date, G_SYSDATE + 1));
4120
4121 -- Define a cursor to get the MC header id , when a relationship id is given
4122 CURSOR get_dest_header_id(p_dest_rel_id in number)
4123 IS
4124 SELECT mc_header_id
4125 FROM ahl_mc_relationships
4126 WHERE relationship_id = p_dest_rel_id;
4127 -- Define cursor get_node_mc_details to read detail of the MC of a MC node
4128 CURSOR get_node_mc_details(p_dest_rel_id in number)
4129 IS
4130 SELECT mch.name
4131 FROM ahl_mc_headers_b mch, ahl_mc_relationships mcr
4132 WHERE mch.mc_header_id = mcr.mc_header_id AND
4133 mcr.relationship_id = p_dest_rel_id;
4134 -- Define cursor get_mc_details to read detail of a MC
4135 CURSOR get_mc_details(p_subconfig_id in number )
4136 IS
4137 SELECT name
4138 FROM ahl_mc_headers_b
4139 WHERE mc_header_id = p_subconfig_id;
4140 -- Declare local variables
4141 l_api_name CONSTANT VARCHAR2(30) := 'Attach_Subconfig';
4142 l_msg_count NUMBER;
4143
4144 l_header_status VARCHAR2(30);
4145 l_mc_name VARCHAR2(80);
4146 -- new local variables declared
4147 l_mc_config_rel_id NUMBER;
4148 l_cyclic_relation_exist BOOLEAN := FALSE;
4149 l_dest_header_id NUMBER;
4150 l_submc_name VARCHAR2(80);
4151
4152 BEGIN
4153
4154 -- API body starts here
4155 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
4156 THEN
4157 fnd_log.string
4158 (
4159 fnd_log.level_procedure,
4160 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||'.begin',
4161 'At the start of PLSQL procedure'
4162 );
4163 END IF;
4164
4165 -- Validate a MC node with relationship_id = p_x_subconfig_rec.relationship_id exists
4166 Validate_Node_Exists(p_x_subconfig_rec.relationship_id, null);
4167
4168 -- Validate the MC node with relationship_id = p_x_subconfig_rec.relationship_id is a leaf node
4169 OPEN check_root_node;
4170 FETCH check_root_node INTO l_dummy_varchar;
4171 IF (check_root_node%FOUND)
4172 THEN
4173 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_NOT_TOP_NODE');
4174 FND_MSG_PUB.ADD;
4175 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
4176 THEN
4177 fnd_log.message
4178 (
4179 fnd_log.level_exception,
4180 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
4181 false
4182 );
4183 END IF;
4184 RAISE FND_API.G_EXC_ERROR;
4185 END IF;
4186 CLOSE check_root_node;
4187
4188 OPEN check_leaf_node;
4189 FETCH check_leaf_node INTO l_dummy_varchar;
4190 IF (check_leaf_node%FOUND)
4191 THEN
4192 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_NOT_LEAF_NODE');
4193 FND_MSG_PUB.ADD;
4194 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
4195 THEN
4196 fnd_log.message
4197 (
4198 fnd_log.level_exception,
4199 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
4200 false
4201 );
4202 END IF;
4203 RAISE FND_API.G_EXC_ERROR;
4204 END IF;
4205 CLOSE check_leaf_node;
4206
4207 -- Validate the MC with mc_header_id = p_x_subconfig_rec.mc_header_id is complete/draft/approval_rejected
4208 l_header_status := Get_MC_Status(null, p_x_subconfig_rec.mc_header_id);
4209 IF (l_header_status NOT IN ('APPROVAL_PENDING', 'COMPLETE', 'DRAFT', 'APPROVAL_REJECTED'))
4210 THEN
4211 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_SUBMC_STS_INV');
4212 FND_MSG_PUB.ADD;
4213 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
4214 THEN
4215 fnd_log.message
4216 (
4217 fnd_log.level_exception,
4218 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
4219 false
4220 );
4221 END IF;
4222 RAISE FND_API.G_EXC_ERROR;
4223 END IF;
4224
4225 -- Validate whether the subconfiguration is not already associated with the MC node
4226 OPEN check_submc_exists;
4227 FETCH check_submc_exists INTO l_mc_name;
4228 IF (check_submc_exists%FOUND)
4229 THEN
4230 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_SUBMC_EXISTS');
4231 FND_MESSAGE.Set_Token('SUBMC', l_mc_name);
4232 FND_MSG_PUB.ADD;
4233 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
4234 THEN
4235 fnd_log.message
4236 (
4237 fnd_log.level_exception,
4238 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
4239 false
4240 );
4241 END IF;
4242
4243 CLOSE check_submc_exists;
4244 RAISE FND_API.G_EXC_ERROR;
4245 END IF;
4246 CLOSE check_submc_exists;
4247
4248 -- anraj changed for fixing the bug # 3696668
4249 -- Check cyclic relationship for subconfig = p_x_subconfig_rec.mc_header_id and node = p_x_subconfig_rec.relationship_id
4250 --Check_Cyclic_Rel(p_x_subconfig_rec.mc_header_id, p_x_subconfig_rec.relationship_id);
4251 OPEN get_dest_header_id(p_x_subconfig_rec.relationship_id);
4252 FETCH get_dest_header_id into l_dest_header_id;
4253 CLOSE get_dest_header_id;
4254 l_cyclic_relation_exist := Cyclic_Relation_Exists(p_x_subconfig_rec.mc_header_id,l_dest_header_id);
4255 IF (l_cyclic_relation_exist) THEN
4256 OPEN get_node_mc_details(p_x_subconfig_rec.relationship_id);
4257 FETCH get_node_mc_details INTO l_mc_name;
4258 CLOSE get_node_mc_details;
4259 OPEN get_mc_details(p_x_subconfig_rec.mc_header_id);
4260 FETCH get_mc_details INTO l_submc_name;
4261 CLOSE get_mc_details;
4262 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_CYCLIC_REL_EXIST');
4263 FND_MESSAGE.Set_Token('MC', l_mc_name);
4264 FND_MESSAGE.Set_Token('SUBMC', l_submc_name);
4265 FND_MSG_PUB.ADD;
4266 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
4267 THEN
4268 fnd_log.message
4269 (
4270 fnd_log.level_exception,
4271 'ahl.plsql.'||G_PKG_NAME||'.Check_Cyclic_Rel',
4272 false
4273 );
4274 END IF;
4275 END IF;
4276
4277
4278 -- Check Error Message stack.
4279 l_msg_count := FND_MSG_PUB.count_msg;
4280 IF l_msg_count > 0 THEN
4281 RAISE FND_API.G_EXC_ERROR;
4282 END IF;
4283
4284 -- Validate dates for the subconfig association
4285 IF (trunc(nvl(p_x_subconfig_rec.active_start_date, G_SYSDATE)) < G_TRUNC_DATE)
4286 THEN
4287 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_START_DATE_INV');
4288 FND_MSG_PUB.ADD;
4289 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
4290 THEN
4291 fnd_log.message
4292 (
4293 fnd_log.level_exception,
4294 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
4295 false
4296 );
4297 END IF;
4298 END IF;
4299
4300 IF (trunc(nvl(p_x_subconfig_rec.active_end_date, G_SYSDATE + 1)) <= G_TRUNC_DATE)
4301 THEN
4302 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_END_DATE_INV');
4303 FND_MSG_PUB.ADD;
4304 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
4305 THEN
4306 fnd_log.message
4307 (
4308 fnd_log.level_exception,
4309 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
4310 false
4311 );
4312 END IF;
4313 END IF;
4314
4315 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)))
4316 THEN
4317 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_START_END_INV');
4318 FND_MSG_PUB.ADD;
4319 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
4320 THEN
4321 fnd_log.message
4322 (
4323 fnd_log.level_exception,
4324 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
4325 false
4326 );
4327 END IF;
4328 END IF;
4329
4330 IF (p_x_subconfig_rec.priority IS NULL)
4331 THEN
4332 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_PRIORITY_SUBMC_NULL');
4333 FND_MESSAGE.Set_Token('SUB_MC',p_x_subconfig_rec.name);
4334 FND_MSG_PUB.ADD;
4335 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
4336 THEN
4337 fnd_log.message
4338 (
4339 fnd_log.level_exception,
4340 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
4341 false
4342 );
4343 END IF;
4344 ELSIF (p_x_subconfig_rec.priority <= 0)
4345 THEN
4346 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_PRIORITY_INVALID_JSP');
4347 FND_MSG_PUB.ADD;
4348 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
4349 THEN
4350 fnd_log.message
4351 (
4352 fnd_log.level_exception,
4353 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
4354 false
4355 );
4356 END IF;
4357 END IF;
4358
4359 -- Check Error Message stack.
4360 l_msg_count := FND_MSG_PUB.count_msg;
4361 IF l_msg_count > 0 THEN
4362 RAISE FND_API.G_EXC_ERROR;
4363 END IF;
4364
4365 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
4366 THEN
4367 fnd_log.string
4368 (
4369 fnd_log.level_statement,
4370 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
4371 'Subconfiguration association validation successful'
4372 );
4373 END IF;
4374
4375 -- Set values for p_x_subconfig_rec
4376 SELECT ahl_mc_config_rel_s.nextval INTO p_x_subconfig_rec.mc_config_relation_id FROM DUAL;
4377 p_x_subconfig_rec.object_version_number := 1;
4378 p_x_subconfig_rec.security_group_id := null;
4379
4380 -- Create association record for destination node
4381 INSERT INTO AHL_MC_CONFIG_RELATIONS
4382 (
4383 MC_CONFIG_RELATION_ID,
4384 RELATIONSHIP_ID,
4385 MC_HEADER_ID,
4386 ACTIVE_START_DATE,
4387 ACTIVE_END_DATE,
4388 LAST_UPDATE_DATE,
4389 LAST_UPDATED_BY,
4390 CREATION_DATE,
4391 CREATED_BY,
4392 LAST_UPDATE_LOGIN,
4393 OBJECT_VERSION_NUMBER,
4394 SECURITY_GROUP_ID,
4395 ATTRIBUTE_CATEGORY,
4396 ATTRIBUTE1,
4397 ATTRIBUTE2,
4398 ATTRIBUTE3,
4399 ATTRIBUTE4,
4400 ATTRIBUTE5,
4401 ATTRIBUTE6,
4402 ATTRIBUTE7,
4403 ATTRIBUTE8,
4404 ATTRIBUTE9,
4405 ATTRIBUTE10,
4406 ATTRIBUTE11,
4407 ATTRIBUTE12,
4408 ATTRIBUTE13,
4409 ATTRIBUTE14,
4410 ATTRIBUTE15,
4411 PRIORITY
4412 )
4413 VALUES
4414 (
4415 p_x_subconfig_rec.MC_CONFIG_RELATION_ID,
4416 p_x_subconfig_rec.RELATIONSHIP_ID,
4417 p_x_subconfig_rec.MC_HEADER_ID,
4418 TRUNC(p_x_subconfig_rec.ACTIVE_START_DATE),
4419 TRUNC(p_x_subconfig_rec.ACTIVE_END_DATE),
4420 G_SYSDATE,
4421 G_USER_ID,
4422 G_SYSDATE,
4423 G_USER_ID,
4424 G_LOGIN_ID,
4425 p_x_subconfig_rec.OBJECT_VERSION_NUMBER,
4426 p_x_subconfig_rec.SECURITY_GROUP_ID,
4427 p_x_subconfig_rec.ATTRIBUTE_CATEGORY,
4428 p_x_subconfig_rec.ATTRIBUTE1,
4429 p_x_subconfig_rec.ATTRIBUTE2,
4430 p_x_subconfig_rec.ATTRIBUTE3,
4431 p_x_subconfig_rec.ATTRIBUTE4,
4432 p_x_subconfig_rec.ATTRIBUTE5,
4433 p_x_subconfig_rec.ATTRIBUTE6,
4434 p_x_subconfig_rec.ATTRIBUTE7,
4435 p_x_subconfig_rec.ATTRIBUTE8,
4436 p_x_subconfig_rec.ATTRIBUTE9,
4437 p_x_subconfig_rec.ATTRIBUTE10,
4438 p_x_subconfig_rec.ATTRIBUTE11,
4439 p_x_subconfig_rec.ATTRIBUTE12,
4440 p_x_subconfig_rec.ATTRIBUTE13,
4441 p_x_subconfig_rec.ATTRIBUTE14,
4442 p_x_subconfig_rec.ATTRIBUTE15,
4443 p_x_subconfig_rec.priority
4444 );
4445
4446 -- API body ends here
4447 END Attach_Subconfig;
4448
4449 PROCEDURE Modify_Subconfig
4450 (
4451 p_x_subconfig_rec IN OUT NOCOPY Subconfig_Rec_Type
4452 )
4453 IS
4454
4455 -- Define cursor get_subconfig_dates to retrieve information about dates
4456 CURSOR get_subconfig_dates
4457 (
4458 p_mc_config_rel_id in number
4459 )
4460 IS
4461 SELECT active_start_date, active_end_date
4462 FROM ahl_mc_config_relations
4463 WHERE mc_config_relation_id = p_mc_config_rel_id;
4464
4465 -- Declare local variables
4466 l_api_name CONSTANT VARCHAR2(30) := 'Modify_Subconfig';
4467 l_msg_count NUMBER;
4468
4469 l_header_status VARCHAR2(30);
4470 l_start_date DATE;
4471 l_end_date DATE;
4472
4473 BEGIN
4474
4475 -- API body starts here
4476 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
4477 THEN
4478 fnd_log.string
4479 (
4480 fnd_log.level_procedure,
4481 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||'.begin',
4482 'At the start of PLSQL procedure'
4483 );
4484 END IF;
4485
4486 -- Validate that the subconfiguration association exists
4487 Validate_Subconfig_Exists(p_x_subconfig_rec.mc_config_relation_id, nvl(p_x_subconfig_rec.object_version_number, 0));
4488
4489 -- Validate a MC node with relationship_id = p_x_subconfig_rec.relationship_id exists
4490 Validate_Node_Exists(p_x_subconfig_rec.relationship_id, null);
4491
4492 -- Validate the MC with mc_header_id = p_x_subconfig_rec.mc_header_id is complete
4493 l_header_status := Get_MC_Status(null, p_x_subconfig_rec.mc_header_id);
4494 IF (l_header_status NOT IN ('APPROVAL_PENDING', 'COMPLETE', 'DRAFT', 'APPROVAL_REJECTED'))
4495 THEN
4496 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_SUBMC_STS_INV');
4497 FND_MSG_PUB.ADD;
4498 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
4499 THEN
4500 fnd_log.message
4501 (
4502 fnd_log.level_exception,
4503 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
4504 false
4505 );
4506 END IF;
4507 RAISE FND_API.G_EXC_ERROR;
4508 END IF;
4509
4510 -- Validate dates for the subconfig association
4511 OPEN get_subconfig_dates(p_x_subconfig_rec.mc_config_relation_id);
4512 FETCH get_subconfig_dates INTO l_start_date, l_end_date;
4513 CLOSE get_subconfig_dates;
4514
4515 /*
4516 -- Should be able to unexpire an expired subconfiguration association
4517 IF (G_TRUNC_DATE >= trunc(nvl(l_end_date, G_SYSDATE + 1)))
4518 THEN
4519 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_SUBMC_DATE_INV');
4520 FND_MSG_PUB.ADD;
4521 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
4522 THEN
4523 fnd_log.message
4524 (
4525 fnd_log.level_exception,
4526 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
4527 false
4528 );
4529 END IF;
4530 END IF;
4531 */
4532
4533 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)
4534 THEN
4535 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_START_DATE_INV');
4536 FND_MSG_PUB.ADD;
4537 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
4538 THEN
4539 fnd_log.message
4540 (
4541 fnd_log.level_exception,
4542 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
4543 false
4544 );
4545 END IF;
4546 END IF;
4547
4548 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)
4549 THEN
4550 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_END_DATE_INV');
4551 FND_MSG_PUB.ADD;
4552 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
4553 THEN
4554 fnd_log.message
4555 (
4556 fnd_log.level_exception,
4557 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
4558 false
4559 );
4560 END IF;
4561 END IF;
4562
4563 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)))
4564 THEN
4565 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_START_END_INV');
4566 FND_MSG_PUB.ADD;
4567 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
4568 THEN
4569 fnd_log.message
4570 (
4571 fnd_log.level_exception,
4572 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
4573 false
4574 );
4575 END IF;
4576 END IF;
4577
4578
4579 IF (p_x_subconfig_rec.priority IS NULL)
4580 THEN
4581 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_PRIORITY_SUBMC_NULL');
4582 FND_MESSAGE.Set_Token('SUB_MC',p_x_subconfig_rec.name);
4583 FND_MSG_PUB.ADD;
4584 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
4585 THEN
4586 fnd_log.message
4587 (
4588 fnd_log.level_exception,
4589 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
4590 false
4591 );
4592 END IF;
4593 ELSIF (p_x_subconfig_rec.priority <= 0)
4594 THEN
4595 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_PRIORITY_INVALID_JSP');
4596 FND_MSG_PUB.ADD;
4597 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
4598 THEN
4599 fnd_log.message
4600 (
4601 fnd_log.level_exception,
4602 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
4603 false
4604 );
4605 END IF;
4606
4607 END IF;
4608
4609 -- Check Error Message stack.
4610 l_msg_count := FND_MSG_PUB.count_msg;
4611 IF l_msg_count > 0 THEN
4612 RAISE FND_API.G_EXC_ERROR;
4613 END IF;
4614
4615 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
4616 THEN
4617 fnd_log.string
4618 (
4619 fnd_log.level_statement,
4620 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
4621 'Subconfiguration association validation successful'
4622 );
4623 END IF;
4624
4625 p_x_subconfig_rec.OBJECT_VERSION_NUMBER := p_x_subconfig_rec.OBJECT_VERSION_NUMBER + 1;
4626
4627 -- Create association record for destination node
4628 UPDATE AHL_MC_CONFIG_RELATIONS
4629 SET PRIORITY = p_x_subconfig_rec.priority,
4630 ACTIVE_START_DATE = p_x_subconfig_rec.ACTIVE_START_DATE,
4631 ACTIVE_END_DATE = p_x_subconfig_rec.ACTIVE_END_DATE,
4632 LAST_UPDATE_DATE = G_SYSDATE,
4633 LAST_UPDATED_BY = G_USER_ID,
4634 LAST_UPDATE_LOGIN = G_LOGIN_ID,
4635 OBJECT_VERSION_NUMBER = p_x_subconfig_rec.OBJECT_VERSION_NUMBER,
4636 SECURITY_GROUP_ID = p_x_subconfig_rec.SECURITY_GROUP_ID,
4637 ATTRIBUTE_CATEGORY = p_x_subconfig_rec.ATTRIBUTE_CATEGORY,
4638 ATTRIBUTE1 = p_x_subconfig_rec.ATTRIBUTE1,
4639 ATTRIBUTE2 = p_x_subconfig_rec.ATTRIBUTE2,
4640 ATTRIBUTE3 = p_x_subconfig_rec.ATTRIBUTE3,
4641 ATTRIBUTE4 = p_x_subconfig_rec.ATTRIBUTE4,
4642 ATTRIBUTE5 = p_x_subconfig_rec.ATTRIBUTE5,
4643 ATTRIBUTE6 = p_x_subconfig_rec.ATTRIBUTE6,
4644 ATTRIBUTE7 = p_x_subconfig_rec.ATTRIBUTE7,
4645 ATTRIBUTE8 = p_x_subconfig_rec.ATTRIBUTE8,
4646 ATTRIBUTE9 = p_x_subconfig_rec.ATTRIBUTE9,
4647 ATTRIBUTE10 = p_x_subconfig_rec.ATTRIBUTE10,
4648 ATTRIBUTE11 = p_x_subconfig_rec.ATTRIBUTE11,
4649 ATTRIBUTE12 = p_x_subconfig_rec.ATTRIBUTE12,
4650 ATTRIBUTE13 = p_x_subconfig_rec.ATTRIBUTE13,
4651 ATTRIBUTE14 = p_x_subconfig_rec.ATTRIBUTE14,
4652 ATTRIBUTE15 = p_x_subconfig_rec.ATTRIBUTE15
4653 WHERE MC_CONFIG_RELATION_ID = p_x_subconfig_rec.MC_CONFIG_RELATION_ID;
4654
4655 -- API body ends here
4656
4657 END Modify_Subconfig;
4658
4659 PROCEDURE Detach_Subconfig
4660 (
4661 p_mc_config_relation_id IN NUMBER,
4662 p_object_ver_num IN NUMBER
4663 )
4664 IS
4665 -- Define local variables
4666 l_api_name CONSTANT VARCHAR2(30) := 'Detach_Subconfig';
4667
4668 BEGIN
4669
4670 -- API body starts here
4671 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
4672 THEN
4673 fnd_log.string
4674 (
4675 fnd_log.level_procedure,
4676 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||'.begin',
4677 'At the start of PLSQL procedure'
4678 );
4679 END IF;
4680
4681 -- Validate p_mc_config_relation_id exists
4682 Validate_Subconfig_Exists(p_mc_config_relation_id, nvl(p_object_ver_num, 0));
4683
4684 DELETE FROM ahl_mc_config_relations
4685 WHERE mc_config_relation_id = p_mc_config_relation_id;
4686
4687 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
4688 THEN
4689 fnd_log.string
4690 (
4691 fnd_log.level_procedure,
4692 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||'.end',
4693 'At the end of PLSQL procedure'
4694 );
4695 END IF;
4696 -- API body ends here
4697
4698 END Detach_Subconfig;
4699
4700 PROCEDURE Copy_Subconfig
4701 (
4702 p_source_rel_id IN NUMBER,
4703 p_dest_rel_id IN NUMBER
4704 )
4705 IS
4706 -- Define a cursor to get the MC header id , when a relationship id is given
4707 CURSOR get_dest_header_id
4708 IS
4709 SELECT mc_header_id
4710 FROM ahl_mc_relationships
4711 WHERE relationship_id = p_dest_rel_id;
4712 -- Define cursor get_node_mc_details to read detail of the MC of a MC node
4713 CURSOR get_node_mc_details
4714 IS
4715 SELECT mch.name
4716 FROM ahl_mc_headers_b mch, ahl_mc_relationships mcr
4717 WHERE mch.mc_header_id = mcr.mc_header_id AND
4718 mcr.relationship_id = p_dest_rel_id;
4719 -- Define cursor get_mc_details to read detail of a MC
4720 CURSOR get_mc_details(p_subconfig_id in number)
4721 IS
4722 SELECT name
4723 FROM ahl_mc_headers_b
4724 WHERE mc_header_id = p_subconfig_id;
4725 -- Define cursor get_subconfigs to read all valid subconfiguration associations with a particular MC node
4726 CURSOR get_subconfigs
4727 IS
4728 SELECT *
4729 FROM ahl_mc_config_relations
4730 WHERE relationship_id = p_source_rel_id;
4731 -- Expired subconfig associations also need to be copied or else copying position paths will fail
4732 -- AND G_TRUNC_DATE < trunc(nvl(active_end_date, G_SYSDATE + 1));
4733
4734 -- Define check_leaf_node to check whether the node to which subconfiguration is being associated is a leaf node
4735 CURSOR check_leaf_node
4736 (
4737 p_rel_id in number
4738 )
4739 IS
4740 SELECT 'x'
4741 FROM ahl_mc_relationships
4742 WHERE parent_relationship_id = p_rel_id AND
4743 G_TRUNC_DATE < trunc(nvl(active_end_date, G_SYSDATE + 1));
4744
4745 -- Declare local variables
4746 l_api_name CONSTANT VARCHAR2(30) := 'Copy_Subconfig';
4747 l_msg_count NUMBER;
4748
4749 l_subconfig_csr_rec get_subconfigs%rowtype;
4750 -- new local variables declared
4751 l_mc_config_rel_id NUMBER;
4752 l_cyclic_relation_exist BOOLEAN := FALSE;
4753 l_dest_header_id NUMBER;
4754 l_mc_name VARCHAR2(80);
4755 l_submc_name VARCHAR2(80);
4756
4757 BEGIN
4758
4759 -- API body starts here
4760 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
4761 THEN
4762 fnd_log.string
4763 (
4764 fnd_log.level_procedure,
4765 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||'.begin',
4766 'At the start of PLSQL procedure'
4767 );
4768 END IF;
4769
4770 -- Validate p_source_rel_id exists
4771 Validate_Node_Exists(p_source_rel_id, null);
4772
4773 -- Validate p_dest_rel_id exists
4774 Validate_Node_Exists(p_dest_rel_id, null);
4775
4776 -- Check Error Message stack.
4777 l_msg_count := FND_MSG_PUB.count_msg;
4778 IF l_msg_count > 0 THEN
4779 RAISE FND_API.G_EXC_ERROR;
4780 END IF;
4781
4782 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
4783 THEN
4784 fnd_log.string
4785 (
4786 fnd_log.level_statement,
4787 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
4788 'Source and destination node validation successful'
4789 );
4790 END IF;
4791
4792 -- Retrieve all subconfigurations associations with p_source_rel_id
4793 OPEN get_subconfigs;
4794 LOOP
4795 FETCH get_subconfigs INTO l_subconfig_csr_rec;
4796 EXIT WHEN get_subconfigs% NOTFOUND;
4797
4798 -- Validte p_dest_rel_id is leaf node
4799 OPEN check_leaf_node(p_dest_rel_id);
4800 FETCH check_leaf_node INTO l_dummy_varchar;
4801 IF (check_leaf_node%FOUND)
4802 THEN
4803 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_NOT_LEAF_NODE');
4804 FND_MSG_PUB.ADD;
4805 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
4806 THEN
4807 fnd_log.message
4808 (
4809 fnd_log.level_exception,
4810 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
4811 false
4812 );
4813 END IF;
4814 RAISE FND_API.G_EXC_ERROR;
4815 END IF;
4816 CLOSE check_leaf_node;
4817
4818 -- anraj changed for fixing the bug # 3696668
4819 -- Check cyclic relationship for this association
4820 -- Check_Cyclic_Rel(l_subconfig_csr_rec.mc_header_id, p_dest_rel_id);
4821 OPEN get_dest_header_id;
4822 FETCH get_dest_header_id into l_dest_header_id;
4823 CLOSE get_dest_header_id;
4824 IF (l_subconfig_csr_rec.mc_header_id = l_dest_header_id) THEN
4825 l_cyclic_relation_exist := TRUE;
4826 ELSE
4827 l_cyclic_relation_exist := Cyclic_Relation_Exists(l_subconfig_csr_rec.mc_header_id,l_dest_header_id);
4828 END IF;
4829 IF (l_cyclic_relation_exist) THEN
4830 OPEN get_node_mc_details;
4831 FETCH get_node_mc_details INTO l_mc_name;
4832 CLOSE get_node_mc_details;
4833
4834 OPEN get_mc_details(l_subconfig_csr_rec.mc_header_id);
4835 FETCH get_mc_details INTO l_submc_name;
4836 CLOSE get_mc_details;
4837 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_CYCLIC_REL_EXIST');
4838 FND_MESSAGE.Set_Token('MC', l_mc_name);
4839 FND_MESSAGE.Set_Token('SUBMC', l_submc_name);
4840 FND_MSG_PUB.ADD;
4841 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
4842 THEN
4843 fnd_log.message
4844 (
4845 fnd_log.level_exception,
4846 'ahl.plsql.'||G_PKG_NAME||'.Check_Cyclic_Rel',
4847 false
4848 );
4849 END IF;
4850 END IF;
4851 -- Check Error Message stack.
4852 l_msg_count := FND_MSG_PUB.count_msg;
4853 IF l_msg_count > 0 THEN
4854 RAISE FND_API.G_EXC_ERROR;
4855 END IF;
4856
4857 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
4858 THEN
4859 fnd_log.string
4860 (
4861 fnd_log.level_statement,
4862 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
4863 'Cyclic relation check for node ['||p_dest_rel_id||'] and subconfiguration ['||l_subconfig_csr_rec.mc_header_id||'] is successful'
4864 );
4865 END IF;
4866
4867 -- Set values for l_subconfig_csr_rec
4868 SELECT ahl_mc_config_rel_s.nextval INTO l_mc_config_rel_id FROM DUAL;
4869
4870 -- Create association record for destination node
4871 INSERT INTO AHL_MC_CONFIG_RELATIONS
4872 (
4873 MC_CONFIG_RELATION_ID,
4874 RELATIONSHIP_ID,
4875 MC_HEADER_ID,
4876 ACTIVE_START_DATE,
4877 ACTIVE_END_DATE,
4878 LAST_UPDATE_DATE,
4879 LAST_UPDATED_BY,
4880 CREATION_DATE,
4881 CREATED_BY,
4882 LAST_UPDATE_LOGIN,
4883 OBJECT_VERSION_NUMBER,
4884 SECURITY_GROUP_ID,
4885 ATTRIBUTE_CATEGORY,
4886 ATTRIBUTE1,
4887 ATTRIBUTE2,
4888 ATTRIBUTE3,
4889 ATTRIBUTE4,
4890 ATTRIBUTE5,
4891 ATTRIBUTE6,
4892 ATTRIBUTE7,
4893 ATTRIBUTE8,
4894 ATTRIBUTE9,
4895 ATTRIBUTE10,
4896 ATTRIBUTE11,
4897 ATTRIBUTE12,
4898 ATTRIBUTE13,
4899 ATTRIBUTE14,
4900 ATTRIBUTE15,
4901 PRIORITY
4902 )
4903 VALUES
4904 (
4905 l_mc_config_rel_id,
4906 p_dest_rel_id,
4907 l_subconfig_csr_rec.MC_HEADER_ID,
4908 TRUNC(l_subconfig_csr_rec.ACTIVE_START_DATE),
4909 TRUNC(l_subconfig_csr_rec.ACTIVE_END_DATE),
4910 G_SYSDATE,
4911 G_USER_ID,
4912 G_SYSDATE,
4913 G_USER_ID,
4914 G_LOGIN_ID,
4915 1,
4916 l_subconfig_csr_rec.SECURITY_GROUP_ID,
4917 l_subconfig_csr_rec.ATTRIBUTE_CATEGORY,
4918 l_subconfig_csr_rec.ATTRIBUTE1,
4919 l_subconfig_csr_rec.ATTRIBUTE2,
4920 l_subconfig_csr_rec.ATTRIBUTE3,
4921 l_subconfig_csr_rec.ATTRIBUTE4,
4922 l_subconfig_csr_rec.ATTRIBUTE5,
4923 l_subconfig_csr_rec.ATTRIBUTE6,
4924 l_subconfig_csr_rec.ATTRIBUTE7,
4925 l_subconfig_csr_rec.ATTRIBUTE8,
4926 l_subconfig_csr_rec.ATTRIBUTE9,
4927 l_subconfig_csr_rec.ATTRIBUTE10,
4928 l_subconfig_csr_rec.ATTRIBUTE11,
4929 l_subconfig_csr_rec.ATTRIBUTE12,
4930 l_subconfig_csr_rec.ATTRIBUTE13,
4931 l_subconfig_csr_rec.ATTRIBUTE14,
4932 l_subconfig_csr_rec.ATTRIBUTE15,
4933 l_subconfig_csr_rec.PRIORITY
4934 );
4935
4936 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
4937 THEN
4938 fnd_log.string
4939 (
4940 fnd_log.level_statement,
4941 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
4942 'Subconfiguration association ['||l_subconfig_csr_rec.mc_config_relation_id||'] copied to ['||l_mc_config_rel_id||']'
4943 );
4944 END IF;
4945 END LOOP;
4946 CLOSE get_subconfigs;
4947
4948 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
4949 THEN
4950 fnd_log.string
4951 (
4952 fnd_log.level_procedure,
4953 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||'.end',
4954 'At the end of PLSQL procedure'
4955 );
4956 END IF;
4957 -- API body ends here
4958
4959 END Copy_Subconfig;
4960
4961 End AHL_MC_Node_PVT;