[Home] [Help]
PACKAGE BODY: APPS.AHL_MC_MASTERCONFIG_PVT
Source
1 PACKAGE BODY AHL_MC_MasterConfig_PVT AS
2 /* $Header: AHLVMCXB.pls 120.5.12020000.2 2012/12/10 13:59:45 shnatu 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 -- Validation procedure signatures --
17 -------------------------------------
18 PROCEDURE Validate_MC_Exists
19 (
20 p_mc_header_id in number,
21 p_object_ver_num in number
22 );
23
24 PROCEDURE Validate_MC_Name
25 (
26 p_x_mc_header_rec in Header_Rec_Type
27 );
28
29 PROCEDURE Validate_MC_Revision
30 (
31 p_x_mc_header_rec in Header_Rec_Type
32 );
33
34 -----------------------------------
35 -- Non-spec Procedure Signatures --
36 -----------------------------------
37 FUNCTION Get_MC_Status
38 (
39 p_mc_header_id in number
40 )
41 RETURN VARCHAR2;
42
43 PROCEDURE Set_Header_Status
44 (
45 p_mc_header_id IN NUMBER
46 );
47
48 PROCEDURE Check_MC_Complete
49 (
50 p_mc_header_id IN NUMBER
51 );
52
53 ---------------------
54 -- Spec Procedures --
55 ---------------------
56 PROCEDURE Create_Master_Config
57 (
58 p_api_version IN NUMBER,
59 p_init_msg_list IN VARCHAR2,
60 p_commit IN VARCHAR2,
61 p_validation_level IN NUMBER,
62 x_return_status OUT NOCOPY VARCHAR2,
63 x_msg_count OUT NOCOPY NUMBER,
64 x_msg_data OUT NOCOPY VARCHAR2,
65 p_x_mc_header_rec IN OUT NOCOPY Header_Rec_Type,
66 p_x_node_rec IN OUT NOCOPY AHL_MC_Node_PVT.Node_Rec_Type
67 )
68 IS
69 -- Define cursor check_other_mc_name_unique to to check uniqueness of MC name across other MCs
70 -- for the case of creating new revision of an MC
71 CURSOR check_other_mc_name_unique
72 (
73 p_mc_name in varchar2,
74 p_mc_id in number
75 )
76 IS
77 SELECT 'x'
78 FROM ahl_mc_headers_b
79 WHERE upper(name) = upper(p_mc_name) AND
80 mc_id <> p_mc_id;
81
82 -- 1. Declare local variables
83 l_api_name CONSTANT VARCHAR2(30) := 'Create_Master_Config';
84 l_api_version CONSTANT NUMBER := 1.0;
85 l_return_status VARCHAR2(1);
86 l_msg_count NUMBER;
87 l_msg_data VARCHAR2(2000);
88
89 l_row_id ROWID;
90 l_counter_rules_tbl AHL_MC_Node_PVT.Counter_Rules_Tbl_Type;
91 l_subconfig_tbl AHL_MC_Node_PVT.Subconfig_Tbl_Type;
92
93 BEGIN
94
95 -- Standard start of API savepoint
96 SAVEPOINT Create_Master_Config_SP;
97
98 -- Standard call to check for call compatibility
99 IF NOT FND_API.compatible_api_call(l_api_version, p_api_version, l_api_name, G_PKG_NAME)
100 THEN
101 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
102 END IF;
103
104 -- Initialize message list if p_init_msg_list is set to TRUE
105 IF FND_API.TO_BOOLEAN(p_init_msg_list)
106 THEN
107 FND_MSG_PUB.Initialize;
108 END IF;
109
110 -- Initialize API return status to success
111 x_return_status := FND_API.G_RET_STS_SUCCESS;
112
113 -- API body starts here
114 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
115 THEN
116 fnd_log.string
117 (
118 fnd_log.level_procedure,
119 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||'.begin',
120 'At the start of PLSQL procedure'
121 );
122 END IF;
123
124 IF (p_x_mc_header_rec.mc_id IS NULL)
125 THEN
126 -- 2. Implies the MC is being created for the first time and a revision of existing MC is not being created
127
128 -- 2a. Validate p_x_mc_header_rec.name is unique
129 Validate_MC_Name(p_x_mc_header_rec);
130 ELSE
131 -- 3. Implies revision of an existing MC is being created
132
133 -- 3a. Validate an MC exists with MC_HEADER_ID = p_x_mc_header_rec.MC_ID
134 Validate_MC_Exists(p_x_mc_header_rec.mc_id, null);
135
136 -- 3b. Validate p_x_mc_header_rec.name is unique across all other MCs
137 -- Confirm user has entered MC Name, since it is mandatory
138 IF (RTRIM(p_x_mc_header_rec.name) IS NULL)
139 THEN
140 FND_MESSAGE.Set_Name('AHL','AHL_MC_NAME_INVALID');
141 FND_MSG_PUB.ADD;
142 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
143 THEN
144 fnd_log.message
145 (
146 fnd_log.level_exception,
147 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
148 false
149 );
150 END IF;
151 ELSE
152 OPEN check_other_mc_name_unique (p_x_mc_header_rec.name, p_x_mc_header_rec.mc_id);
153 FETCH check_other_mc_name_unique INTO l_dummy_varchar;
154 IF (check_other_mc_name_unique%FOUND)
155 THEN
156 FND_MESSAGE.Set_Name('AHL','AHL_MC_RNAME_EXISTS');
157 FND_MSG_PUB.ADD;
158 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
159 THEN
160 fnd_log.message
161 (
162 fnd_log.level_exception,
163 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
164 false
165 );
166 END IF;
167 END IF;
168 CLOSE check_other_mc_name_unique;
169 END IF;
170 END IF;
171
172 -- 3c. Validate p_x_mc_header_rec.revision (unique across all revisions of the same MC + atleast one alphabetic character)
173 Validate_MC_Revision(p_x_mc_header_rec);
174
175 -- 4. Validate p_x_mc_header_rec.config_status_code, should be defaulted to 'DRAFT'
176 IF (p_x_mc_header_rec.config_status_code <> 'DRAFT')
177 THEN
178 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_STATUS_INVALID');
179 FND_MESSAGE.Set_Token('STATUS', p_x_mc_header_rec.config_status_meaning);
180 FND_MSG_PUB.ADD;
181 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
182 THEN
183 fnd_log.message
184 (
185 fnd_log.level_exception,
186 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
187 false
188 );
189 END IF;
190 END IF;
191
192 -- Check Error Message stack.
193 x_msg_count := FND_MSG_PUB.count_msg;
194 IF x_msg_count > 0
195 THEN
196 RAISE FND_API.G_EXC_ERROR;
197 END IF;
198
199 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
200 THEN
201 fnd_log.string
202 (
203 fnd_log.level_statement,
204 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
205 'Header validation successful'
206 );
207 END IF;
208
209 -- 6. Select next value from the AHL_MC_HEADERS_B_S sequence
210 SELECT ahl_mc_headers_b_s.nextval INTO p_x_mc_header_rec.mc_header_id FROM dual;
211
212 -- 7. Set values for p_x_mc_header_rec
213 p_x_mc_header_rec.object_version_number := 1;
214 p_x_mc_header_rec.security_group_id := null;
215
216 IF (p_x_mc_header_rec.version_number IS NULL)
217 THEN
218 p_x_mc_header_rec.version_number := 1;
219 END IF;
220
221 IF (p_x_mc_header_rec.revision IS NULL)
222 THEN
223 p_x_mc_header_rec.revision := to_char(p_x_mc_header_rec.version_number);
224 END IF;
225
226 -- Default mc_id = mc_header_id if null
227 IF (p_x_mc_header_rec.mc_id IS NULL)
228 THEN
229 p_x_mc_header_rec.mc_id := p_x_mc_header_rec.mc_header_id;
230 END IF;
231
232 -- 8. Call AHL_MC_HEADERS_PKG.INSERT_ROW with relevant attribute values
233 AHL_MC_HEADERS_PKG.INSERT_ROW
234 (
235 X_ROWID => l_row_id, -- passed as dummy, cannot pass null
236 X_MC_HEADER_ID => p_x_mc_header_rec.mc_header_id,
237 X_NAME => p_x_mc_header_rec.name,
238 X_MC_ID => p_x_mc_header_rec.mc_id,
239 X_VERSION_NUMBER => p_x_mc_header_rec.version_number,
240 X_REVISION => p_x_mc_header_rec.revision,
241 X_MODEL_CODE => p_x_mc_header_rec.model_code, -- SATHAPLI::Enigma code changes, 26-Aug-2008
242 X_CONFIG_STATUS_CODE => p_x_mc_header_rec.config_status_code,
243 X_OBJECT_VERSION_NUMBER => p_x_mc_header_rec.object_version_number,
244 X_SECURITY_GROUP_ID => p_x_mc_header_rec.security_group_id,
245 X_ATTRIBUTE_CATEGORY => p_x_mc_header_rec.attribute_category,
246 X_ATTRIBUTE1 => p_x_mc_header_rec.attribute1,
247 X_ATTRIBUTE2 => p_x_mc_header_rec.attribute2,
248 X_ATTRIBUTE3 => p_x_mc_header_rec.attribute3,
249 X_ATTRIBUTE4 => p_x_mc_header_rec.attribute4,
250 X_ATTRIBUTE5 => p_x_mc_header_rec.attribute5,
251 X_ATTRIBUTE6 => p_x_mc_header_rec.attribute6,
252 X_ATTRIBUTE7 => p_x_mc_header_rec.attribute7,
253 X_ATTRIBUTE8 => p_x_mc_header_rec.attribute8,
254 X_ATTRIBUTE9 => p_x_mc_header_rec.attribute9,
255 X_ATTRIBUTE10 => p_x_mc_header_rec.attribute10,
256 X_ATTRIBUTE11 => p_x_mc_header_rec.attribute11,
257 X_ATTRIBUTE12 => p_x_mc_header_rec.attribute12,
258 X_ATTRIBUTE13 => p_x_mc_header_rec.attribute13,
259 X_ATTRIBUTE14 => p_x_mc_header_rec.attribute14,
260 X_ATTRIBUTE15 => p_x_mc_header_rec.attribute15,
261 X_DESCRIPTION => p_x_mc_header_rec.description,
262 X_CREATION_DATE => G_SYSDATE,
263 X_CREATED_BY => G_USER_ID,
264 X_LAST_UPDATE_DATE => G_SYSDATE,
265 X_LAST_UPDATED_BY => G_USER_ID,
266 X_LAST_UPDATE_LOGIN => G_LOGIN_ID
267 );
268
269 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
270 THEN
271 fnd_log.string
272 (
273 fnd_log.level_statement,
274 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
275 'Header ['||p_x_mc_header_rec.mc_header_id||'] created'
276 );
277 END IF;
278
279 -- 9. Select next value from the AHL_MC_RELATIONSHIPS_S sequence
280 SELECT ahl_mc_relationships_s.nextval INTO p_x_node_rec.relationship_id FROM dual;
281
282 -- 10. Set values for p_x_node_rec
283 p_x_node_rec.mc_header_id := p_x_mc_header_rec.mc_header_id;
284 p_x_node_rec.parent_relationship_id := null;
285 p_x_node_rec.object_version_number := 1;
286 IF (p_x_node_rec.operation_flag IS NULL)
287 THEN
288 -- This can also be G_DML_COPY, if not defined already default to G_DML_CREATE
289 p_x_node_rec.operation_flag := G_DML_CREATE;
290 END IF;
291
292 IF (p_x_node_rec.position_key IS NULL)
293 THEN
294 SELECT ahl_mc_rel_pos_key_s.nextval INTO p_x_node_rec.position_key FROM dual;
295 END IF;
296
297 IF (p_x_node_rec.position_necessity_code <> 'MANDATORY')
298 THEN
299 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_TOPNODE_NEC_INV');
300 FND_MSG_PUB.ADD;
301 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
302 THEN
303 fnd_log.message
304 (
305 fnd_log.level_exception,
306 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
307 false
308 );
309 END IF;
310 END IF;
311 -- AnRaj: Bug # 5385301, Removed the hardcoded validation for UOM
312 /*
313 IF (p_x_node_rec.uom_code <> 'Ea')
314 THEN
315 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_TOPNODE_UOM_INV');
316 FND_MSG_PUB.ADD;
317 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
318 THEN
319 fnd_log.message
320 (
321 fnd_log.level_exception,
322 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
323 false
324 );
325 END IF;
326 END IF;
327 */
328 IF (p_x_node_rec.quantity <> 1)
329 THEN
330 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_TOPNODE_QTY_INV');
331 FND_MSG_PUB.ADD;
332 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
333 THEN
334 fnd_log.message
335 (
336 fnd_log.level_exception,
337 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
338 false
339 );
340 END IF;
341 END IF;
342
343 IF (p_x_node_rec.display_order <> 1)
344 THEN
345 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_TOPNODE_DSP_INV');
346 FND_MSG_PUB.ADD;
347 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
348 THEN
349 fnd_log.message
350 (
351 fnd_log.level_exception,
352 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
353 false
354 );
355 END IF;
356 END IF;
357
358 -- Check Error Message stack.
359 x_msg_count := FND_MSG_PUB.count_msg;
360 IF x_msg_count > 0
361 THEN
362 RAISE FND_API.G_EXC_ERROR;
363 END IF;
364
365 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
366 THEN
367 fnd_log.string
368 (
369 fnd_log.level_statement,
370 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
371 'Node validation successful... Calling AHL_MC_Node_PVT.Create_Node'
372 );
373 END IF;
374
375 -- 11. Call AHL_MC_NODE_PVT.Create_Node to create MC topnode
376 AHL_MC_Node_PVT.Create_Node
377 (
378 p_api_version => 1.0,
379 p_init_msg_list => FND_API.G_FALSE,
380 p_commit => FND_API.G_FALSE,
381 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
382 x_return_status => l_return_status,
383 x_msg_count => l_msg_count,
384 x_msg_data => l_msg_data,
385 p_x_node_rec => p_x_node_rec,
386 p_x_counter_rules_tbl => l_counter_rules_tbl, -- passed as dummy, cannot pass null
387 p_x_subconfig_tbl => l_subconfig_tbl -- passed as dummy, cannot pass null
388 );
389
390 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
391 THEN
392 fnd_log.string
393 (
394 fnd_log.level_procedure,
395 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||'.end',
396 'At the end of PLSQL procedure'
397 );
398 END IF;
399 -- API body ends here
400
401 -- Check Error Message stack.
402 x_msg_count := FND_MSG_PUB.count_msg;
403 IF x_msg_count > 0
404 THEN
405 RAISE FND_API.G_EXC_ERROR;
406 END IF;
407
408 -- Standard check for p_commit
409 IF FND_API.TO_BOOLEAN (p_commit)
410 THEN
411 COMMIT WORK;
412 END IF;
413
414 -- Standard call to get message count and if count is 1, get message info
415 FND_MSG_PUB.count_and_get
416 (
417 p_count => x_msg_count,
418 p_data => x_msg_data,
419 p_encoded => FND_API.G_FALSE
420 );
421
422 EXCEPTION
423 WHEN FND_API.G_EXC_ERROR THEN
424 x_return_status := FND_API.G_RET_STS_ERROR;
425 Rollback to Create_Master_Config_SP;
426 FND_MSG_PUB.count_and_get
427 (
428 p_count => x_msg_count,
429 p_data => x_msg_data,
430 p_encoded => FND_API.G_FALSE
431 );
432
433 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
434 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
435 Rollback to Create_Master_Config_SP;
436 FND_MSG_PUB.count_and_get
437 (
438 p_count => x_msg_count,
439 p_data => x_msg_data,
440 p_encoded => FND_API.G_FALSE
441 );
442
443 WHEN OTHERS THEN
444 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
445 Rollback to Create_Master_Config_SP;
446 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
447 THEN
448 FND_MSG_PUB.add_exc_msg
449 (
450 p_pkg_name => G_PKG_NAME,
451 p_procedure_name => 'Create_Master_Config',
452 p_error_text => SUBSTR(SQLERRM,1,240)
453 );
454 END IF;
455 FND_MSG_PUB.count_and_get
456 (
457 p_count => x_msg_count,
458 p_data => x_msg_data,
459 p_encoded => FND_API.G_FALSE
460 );
461
462 END Create_Master_Config;
463
464 PROCEDURE Modify_Master_Config
465 (
466 p_api_version IN NUMBER,
467 p_init_msg_list IN VARCHAR2,
468 p_commit IN VARCHAR2,
469 p_validation_level IN NUMBER,
470 x_return_status OUT NOCOPY VARCHAR2,
471 x_msg_count OUT NOCOPY NUMBER,
472 x_msg_data OUT NOCOPY VARCHAR2,
473 p_x_mc_header_rec IN OUT NOCOPY Header_Rec_Type,
474 p_x_node_rec IN OUT NOCOPY AHL_MC_Node_PVT.Node_Rec_Type
475 )
476 IS
477 -- Define local variables
478 l_api_name CONSTANT VARCHAR2(30) := 'Modify_Master_Config';
479 l_api_version CONSTANT NUMBER := 1.0;
480 l_return_status VARCHAR2(1);
481 l_msg_count NUMBER;
482 l_msg_data VARCHAR2(2000);
483
484 l_counter_rules_tbl AHL_MC_Node_PVT.Counter_Rules_Tbl_Type;
485 l_subconfig_tbl AHL_MC_Node_PVT.Subconfig_Tbl_Type;
486 l_header_status VARCHAR2(30);
487
488 BEGIN
489
490 -- Standard start of API savepoint
491 SAVEPOINT Modify_Master_Config_SP;
492
493 -- Standard call to check for call compatibility
494 IF NOT FND_API.compatible_api_call(l_api_version, p_api_version, l_api_name, G_PKG_NAME)
495 THEN
496 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
497 END IF;
498
499 -- Initialize message list if p_init_msg_list is set to TRUE
500 IF FND_API.TO_BOOLEAN(p_init_msg_list)
501 THEN
502 FND_MSG_PUB.Initialize;
503 END IF;
504
505 -- Initialize API return status to success
506 x_return_status := FND_API.G_RET_STS_SUCCESS;
507
508 -- API body starts here
509 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
510 THEN
511 fnd_log.string
512 (
513 fnd_log.level_procedure,
514 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||'.begin',
515 'At the start of PLSQL procedure'
516 );
517 END IF;
518
519 -- 1a. Validate a MC with p_x_mc_header_rec.mc_header_id exists
520 -- 1b. Validate p_x_mc_header_rec.object_version_number
521 Validate_MC_Exists(p_x_mc_header_rec.mc_header_id, nvl(p_x_mc_header_rec.object_version_number, 0));
522
523 -- 1c. Validate p_x_mc_header_rec.config_status_code, should be either DRAFT/ APPROVAL_REJECTED
524 l_header_status := Get_MC_Status(p_x_mc_header_rec.mc_header_id);
525
526 -- Fix for Bug #3523435
527 -- Trying to modify status = COMPLETE without initiating approval
528 IF (p_x_mc_header_rec.config_status_code = 'COMPLETE' AND l_header_status <> 'COMPLETE')
529 THEN
530 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_INIT_APPR_COMP');
531 FND_MESSAGE.Set_Token('MC_NAME', p_x_mc_header_rec.name);
532 FND_MSG_PUB.ADD;
533 RAISE FND_API.G_EXC_ERROR;
534 END IF;
535 -- Fix for Bug #3523435
536
537 IF (l_header_status = 'APPROVAL_REJECTED')
538 THEN
539 p_x_mc_header_rec.config_status_code := 'DRAFT';
540 ELSIF (l_header_status <> 'DRAFT')
541 THEN
542 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_EDIT_STS_INV');
543 FND_MSG_PUB.ADD;
544 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
545 THEN
546 fnd_log.message
547 (
548 fnd_log.level_exception,
549 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
550 false
551 );
552 END IF;
553 RAISE FND_API.G_EXC_ERROR;
554 END IF;
555
556 -- Confirm name of the MC has not changed, since it is the user-enterable unique name
557 Validate_MC_Name(p_x_mc_header_rec);
558
559 IF (p_x_mc_header_rec.mc_id <> p_x_mc_header_rec.mc_header_id)
560 THEN
561 -- 1e.i. Validate an MC exists with MC_HEADER_ID = p_x_mc_header_rec.MC_ID
562 Validate_MC_Exists(p_x_mc_header_rec.mc_id, null);
563 END IF;
564
565 -- 1e.ii. Validate p_x_mc_header_rec.revision (unique across all revisions of the same MC + atleast one alphabetic character)
566 Validate_MC_Revision(p_x_mc_header_rec);
567
568 -- Check Error Message stack.
569 x_msg_count := FND_MSG_PUB.count_msg;
570 IF x_msg_count > 0
571 THEN
572 RAISE FND_API.G_EXC_ERROR;
573 END IF;
574
575 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
576 THEN
577 fnd_log.string
578 (
579 fnd_log.level_statement,
580 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
581 'Header validation successful'
582 );
583 END IF;
584
585 -- Set values for p_x_mc_header_rec
586 p_x_mc_header_rec.object_version_number := p_x_mc_header_rec.object_version_number + 1;
587
588 IF (p_x_mc_header_rec.revision IS NULL AND p_x_mc_header_rec.version_number IS NOT NULL)
589 THEN
590 p_x_mc_header_rec.revision := to_char(p_x_mc_header_rec.version_number);
591 END IF;
592
593 -- 1h. Call AHL_MC_HEADERS_PKG.UPDATE_ROW with relevant attribute values
594 AHL_MC_HEADERS_PKG.UPDATE_ROW
595 (
596 X_MC_HEADER_ID => p_x_mc_header_rec.mc_header_id,
597 X_NAME => p_x_mc_header_rec.name,
598 X_MC_ID => p_x_mc_header_rec.mc_id,
599 X_VERSION_NUMBER => p_x_mc_header_rec.version_number,
600 X_REVISION => p_x_mc_header_rec.revision,
601 X_MODEL_CODE => p_x_mc_header_rec.model_code, -- SATHAPLI::Enigma code changes, 26-Aug-2008
602 X_CONFIG_STATUS_CODE => p_x_mc_header_rec.config_status_code,
603 X_OBJECT_VERSION_NUMBER => p_x_mc_header_rec.object_version_number,
604 X_SECURITY_GROUP_ID => p_x_mc_header_rec.security_group_id,
605 X_ATTRIBUTE_CATEGORY => p_x_mc_header_rec.attribute_category,
606 X_ATTRIBUTE1 => p_x_mc_header_rec.attribute1,
607 X_ATTRIBUTE2 => p_x_mc_header_rec.attribute2,
608 X_ATTRIBUTE3 => p_x_mc_header_rec.attribute3,
609 X_ATTRIBUTE4 => p_x_mc_header_rec.attribute4,
610 X_ATTRIBUTE5 => p_x_mc_header_rec.attribute5,
611 X_ATTRIBUTE6 => p_x_mc_header_rec.attribute6,
612 X_ATTRIBUTE7 => p_x_mc_header_rec.attribute7,
613 X_ATTRIBUTE8 => p_x_mc_header_rec.attribute8,
614 X_ATTRIBUTE9 => p_x_mc_header_rec.attribute9,
615 X_ATTRIBUTE10 => p_x_mc_header_rec.attribute10,
616 X_ATTRIBUTE11 => p_x_mc_header_rec.attribute11,
617 X_ATTRIBUTE12 => p_x_mc_header_rec.attribute12,
618 X_ATTRIBUTE13 => p_x_mc_header_rec.attribute13,
619 X_ATTRIBUTE14 => p_x_mc_header_rec.attribute14,
620 X_ATTRIBUTE15 => p_x_mc_header_rec.attribute15,
621 X_DESCRIPTION => p_x_mc_header_rec.description,
622 X_LAST_UPDATE_DATE => G_SYSDATE,
623 X_LAST_UPDATED_BY => G_USER_ID,
624 X_LAST_UPDATE_LOGIN => G_LOGIN_ID
625 );
626
627 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
628 THEN
629 fnd_log.string
630 (
631 fnd_log.level_statement,
632 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
633 'Header ['||p_x_mc_header_rec.mc_header_id||'] updated'
634 );
635 END IF;
636
637 -- 2b. Validate p_x_node_rec.mc_header_id = p_x_mc_header_rec.mc_header_id
638 IF (p_x_node_rec.mc_header_id <> p_x_mc_header_rec.mc_header_id)
639 THEN
640 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_TOPNODE_NOTFOUND');
641 FND_MSG_PUB.ADD;
642 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
643 THEN
644 fnd_log.message
645 (
646 fnd_log.level_exception,
647 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
648 false
649 );
650 END IF;
651 RAISE FND_API.G_EXC_ERROR;
652 END IF;
653
654 IF (p_x_node_rec.position_necessity_code <> 'MANDATORY')
655 THEN
656 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_TOPNODE_NEC_INV');
657 FND_MSG_PUB.ADD;
658 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
659 THEN
660 fnd_log.message
661 (
662 fnd_log.level_exception,
663 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
664 false
665 );
666 END IF;
667 END IF;
668
669 -- AnRaj: Bug # 5385301, Removed the hardcoded validation for UOM
670 /*
671 IF (p_x_node_rec.uom_code <> 'Ea')
672 THEN
673 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_TOPNODE_UOM_INV');
674 FND_MSG_PUB.ADD;
675 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
676 THEN
677 fnd_log.message
678 (
679 fnd_log.level_exception,
680 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
681 false
682 );
683 END IF;
684 END IF;
685 */
686 IF (p_x_node_rec.quantity <> 1)
687 THEN
688 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_TOPNODE_QTY_INV');
689 FND_MSG_PUB.ADD;
690 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
691 THEN
692 fnd_log.message
693 (
694 fnd_log.level_exception,
695 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
696 false
697 );
698 END IF;
699 END IF;
700
701 IF (p_x_node_rec.display_order <> 1)
702 THEN
703 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_TOPNODE_DSP_INV');
704 FND_MSG_PUB.ADD;
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
716 -- Check Error Message stack.
717 x_msg_count := FND_MSG_PUB.count_msg;
718 IF x_msg_count > 0
719 THEN
720 RAISE FND_API.G_EXC_ERROR;
721 END IF;
722
723 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
724 THEN
725 fnd_log.string
726 (
727 fnd_log.level_statement,
728 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
729 'Node validation successful... Calling AHL_MC_Node_PVT.Modify_Node'
730 );
731 END IF;
732
733 -- Set values for p_x_node_rec
734 p_x_node_rec.mc_header_id := p_x_mc_header_rec.mc_header_id;
735 p_x_node_rec.operation_flag := G_DML_UPDATE;
736
737 -- 2g. Call AHL_MC_NODE_PVT.Modify_Node to modify MC topnode
738 AHL_MC_Node_PVT.Modify_Node
739 (
740 p_api_version => 1.0,
741 p_init_msg_list => FND_API.G_FALSE,
742 p_commit => FND_API.G_FALSE,
743 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
744 x_return_status => l_return_status,
745 x_msg_count => l_msg_count,
746 x_msg_data => l_msg_data,
747 p_x_node_rec => p_x_node_rec,
748 p_x_counter_rules_tbl => l_counter_rules_tbl, -- passed as dummy, cannot pass null
749 p_x_subconfig_tbl => l_subconfig_tbl -- passed as dummy, cannot pass null
750 );
751
752 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
753 THEN
754 fnd_log.string
755 (
756 fnd_log.level_procedure,
757 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||'.end',
758 'At the end of PLSQL procedure'
759 );
760 END IF;
761 -- API body ends here
762
763 -- Check Error Message stack.
764 x_msg_count := FND_MSG_PUB.count_msg;
765
766 --Priyan
767 --R12 MEL/CDL
768 IF ( x_msg_count > 0 AND l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
769 RAISE FND_API.G_EXC_ERROR;
770 END IF;
771
772 -- Standard check for p_commit
773 IF FND_API.TO_BOOLEAN (p_commit)
774 THEN
775 COMMIT WORK;
776 END IF;
777
778 -- Standard call to get message count and if count is 1, get message info
779 FND_MSG_PUB.count_and_get
780 (
781 p_count => x_msg_count,
782 p_data => x_msg_data,
783 p_encoded => FND_API.G_FALSE
784 );
785
786 EXCEPTION
787 WHEN FND_API.G_EXC_ERROR THEN
788 x_return_status := FND_API.G_RET_STS_ERROR;
789 Rollback to Modify_Master_Config_SP;
790 FND_MSG_PUB.count_and_get
791 (
792 p_count => x_msg_count,
793 p_data => x_msg_data,
794 p_encoded => FND_API.G_FALSE
795 );
796
797 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
798 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
799 Rollback to Modify_Master_Config_SP;
800 FND_MSG_PUB.count_and_get
801 (
802 p_count => x_msg_count,
803 p_data => x_msg_data,
804 p_encoded => FND_API.G_FALSE
805 );
806
807 WHEN OTHERS THEN
808 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
809 Rollback to Modify_Master_Config_SP;
810 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
811 THEN
812 FND_MSG_PUB.add_exc_msg
813 (
814 p_pkg_name => G_PKG_NAME,
815 p_procedure_name => 'Modify_Master_Config',
816 p_error_text => SUBSTR(SQLERRM,1,240)
817 );
818 END IF;
819 FND_MSG_PUB.count_and_get
820 (
821 p_count => x_msg_count,
822 p_data => x_msg_data,
823 p_encoded => FND_API.G_FALSE
824 );
825
826 END Modify_Master_Config;
827
828 PROCEDURE Delete_Master_Config
829 (
830 p_api_version IN NUMBER,
831 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
832 p_commit IN VARCHAR2 := FND_API.G_FALSE,
833 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
834 x_return_status OUT NOCOPY VARCHAR2,
835 x_msg_count OUT NOCOPY NUMBER,
836 x_msg_data OUT NOCOPY VARCHAR2,
837 p_mc_header_id IN NUMBER,
838 p_object_ver_num IN NUMBER
839 )
840 IS
841 -- Define cursor check_mc_not_subconfig to verify that MC is not associated as a subconfiguration
842 -- ##TAMAL## -- Need to check for only non-expired subconfiguration associations, but also need to delete
843 -- such associations from the table so that the draft MC can be deleted, else will throw foreign key
844 -- validation error...
845 -- ##TAMAL## -- Need some mechanism to check whether a subconfiguration association is logically expired,
846 -- since any node up to the root node could be expired...
847 CURSOR check_mc_not_subconfig
848 (
849 p_mc_header_id in number
850 )
851 IS
852 SELECT 'x'
853 FROM ahl_mc_config_relations
854 WHERE mc_header_id = p_mc_header_id;
855
856 -- Define get_mc_topnode_details to read the details of the MC topnode
857 CURSOR get_mc_topnode_details
858 (
859 p_mc_header_id in number
860 )
861 IS
862 SELECT relationship_id, object_version_number
863 FROM ahl_mc_relationships
864 WHERE mc_header_id = p_mc_header_id AND
865 parent_relationship_id IS NULL;
866
867 -- Define cursor check_unit_assigned to verify whether there are any units
868 -- associated with the MC
869 CURSOR check_unit_assigned
870 (
871 p_mc_header_id in number
872 )
873 IS
874 SELECT 'x'
875 FROM ahl_unit_config_headers
876 WHERE master_config_id = p_mc_header_id AND
877 trunc(nvl(active_end_date, G_SYSDATE + 1)) > G_TRUNC_DATE;
878
879 /*
880 SELECT 'x'
881 FROM ahl_mc_relationships mcr, ahl_unit_config_headers uch
882 WHERE mcr.mc_header_id = p_mc_header_id AND
883 mcr.parent_relationship_id IS NULL AND
884 uch.master_config_id = mcr.relationship_id AND
885 trunc(nvl(uch.active_end_date, G_SYSDATE + 1)) > G_TRUNC_DATE;
886 */
887
888 -- 1. Declare local variables
889 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Master_Config';
890 l_api_version CONSTANT NUMBER := 1.0;
891 l_return_status VARCHAR2(1);
892 l_msg_count NUMBER;
893 l_msg_data VARCHAR2(2000);
894
895 l_config_status_code VARCHAR2(30);
896 l_active_end_date DATE;
897 l_topnode_rel_id NUMBER;
898 l_topnode_object_ver_num NUMBER;
899
900 BEGIN
901
902 -- Standard start of API savepoint
903 SAVEPOINT Delete_Master_Config_SP;
904
905 -- Standard call to check for call compatibility
906 IF NOT FND_API.compatible_api_call(l_api_version, p_api_version, l_api_name, G_PKG_NAME)
907 THEN
908 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
909 END IF;
910
911 -- Initialize message list if p_init_msg_list is set to TRUE
912 IF FND_API.TO_BOOLEAN(p_init_msg_list)
913 THEN
914 FND_MSG_PUB.Initialize;
915 END IF;
916
917 -- Initialize API return status to success
918 x_return_status := FND_API.G_RET_STS_SUCCESS;
919
920 -- API body starts here
921 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
922 THEN
923 fnd_log.string
924 (
925 fnd_log.level_procedure,
926 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||'.begin',
927 'At the start of PLSQL procedure'
928 );
929 END IF;
930
931 -- 2. Validate a MC with mc_header_id = p_mc_header_id exists, and object_version_number = p_object_ver_num
932 Validate_MC_Exists(p_mc_header_id, nvl(p_object_ver_num, 0));
933
934 -- 3. Validate the MC is not associated as a subconfiguration
935 OPEN check_mc_not_subconfig(p_mc_header_id);
936 FETCH check_mc_not_subconfig INTO l_dummy_varchar;
937 IF (check_mc_not_subconfig%FOUND)
938 THEN
939 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_IS_SUBCONFIG');
940 FND_MSG_PUB.ADD;
941 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
942 THEN
943 fnd_log.message
944 (
945 fnd_log.level_exception,
946 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
947 false
948 );
949 END IF;
950 CLOSE check_mc_not_subconfig;
951 RAISE FND_API.G_EXC_ERROR;
952 END IF;
953 CLOSE check_mc_not_subconfig;
954
955 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
956 THEN
957 fnd_log.string
958 (
959 fnd_log.level_statement,
960 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
961 'Header validation successful'
962 );
963 END IF;
964
965 -- 4. Query for the config_status_code of the MC with mc_header_id = p_mc_header_id
966 l_config_status_code := Get_MC_Status(p_mc_header_id);
967 IF (l_config_status_code IN ('CLOSED', 'EXPIRED'))
968 THEN
969 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_DEL_STS_INV');
970 FND_MSG_PUB.ADD;
971 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
972 THEN
973 fnd_log.message
974 (
975 fnd_log.level_exception,
976 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
977 false
978 );
979 END IF;
980 ELSIF (l_config_status_code IN ('DRAFT', 'APPROVAL_REJECTED'))
981 THEN
982 -- 6. If l_config_status_code = 'DRAFT' or 'APPROVAL_REJECTED' [Delete MC]
983
984 -- 6a. Call AHL_MC_RULE_PVT.Delete_Rules_For_MC to delete all the rules associated with this MC
985 AHL_MC_RULE_PVT.Delete_Rules_For_MC
986 (
987 p_api_version => 1.0,
988 p_init_msg_list => FND_API.G_FALSE,
989 p_commit => FND_API.G_FALSE,
990 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
991 x_return_status => l_return_status,
992 x_msg_count => l_msg_count,
993 x_msg_data => l_msg_data,
994 p_mc_header_id => p_mc_header_id
995 );
996
997 -- Check Error Message stack.
998 x_msg_count := FND_MSG_PUB.count_msg;
999 IF x_msg_count > 0 THEN
1000 RAISE FND_API.G_EXC_ERROR;
1001 END IF;
1002
1003 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1004 THEN
1005 fnd_log.string
1006 (
1007 fnd_log.level_statement,
1008 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
1009 'Deleted rules for MC'
1010 );
1011 END IF;
1012
1013 -- 6b. Call AHL_MC_PATH_POSITION_PVT.Delete_Positions_For_MC to delete all the position path records for this MC
1014 AHL_MC_PATH_POSITION_PVT.Delete_Positions_For_MC
1015 (
1016 p_api_version => 1.0,
1017 p_init_msg_list => FND_API.G_FALSE,
1018 p_commit => FND_API.G_FALSE,
1019 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1020 x_return_status => l_return_status,
1021 x_msg_count => l_msg_count,
1022 x_msg_data => l_msg_data,
1023 p_mc_header_id => p_mc_header_id
1024 );
1025
1026 -- Check Error Message stack.
1027 x_msg_count := FND_MSG_PUB.count_msg;
1028 IF x_msg_count > 0 THEN
1029 RAISE FND_API.G_EXC_ERROR;
1030 END IF;
1031
1032 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1033 THEN
1034 fnd_log.string
1035 (
1036 fnd_log.level_statement,
1037 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
1038 'Deleted position paths for MC'
1039 );
1040 END IF;
1041
1042 -- 5. Query for the topnode of the MC with MC_HEADER_ID = p_mc_header_id
1043 OPEN get_mc_topnode_details(p_mc_header_id);
1044 FETCH get_mc_topnode_details INTO l_topnode_rel_id, l_topnode_object_ver_num;
1045 CLOSE get_mc_topnode_details;
1046
1047 -- 6c. Call AHL_MC_NODE_PVT.Delete_Node to delete the MC tree starting from the topnode
1048 AHL_MC_NODE_PVT.Delete_Node
1049 (
1050 p_api_version => 1.0,
1051 p_init_msg_list => FND_API.G_FALSE,
1052 p_commit => FND_API.G_FALSE,
1053 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1054 x_return_status => l_return_status,
1055 x_msg_count => l_msg_count,
1056 x_msg_data => l_msg_data,
1057 p_node_id => l_topnode_rel_id,
1058 p_object_ver_num => l_topnode_object_ver_num
1059 );
1060
1061 -- Check Error Message stack.
1062 x_msg_count := FND_MSG_PUB.count_msg;
1063 IF x_msg_count > 0 THEN
1064 RAISE FND_API.G_EXC_ERROR;
1065 END IF;
1066
1067 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1068 THEN
1069 fnd_log.string
1070 (
1071 fnd_log.level_statement,
1072 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
1073 'Deleted nodes for MC'
1074 );
1075 END IF;
1076
1077 -- 6d. Call AHL_MC_HEADERS_PKG.DELETE_ROW to delete the MC
1078 AHL_MC_HEADERS_PKG.DELETE_ROW (p_mc_header_id);
1079
1080 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1081 THEN
1082 fnd_log.string
1083 (
1084 fnd_log.level_statement,
1085 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
1086 'Deleted Header'
1087 );
1088 END IF;
1089
1090 ELSIF (l_config_status_code = 'COMPLETE')
1091 THEN
1092 -- 7. If l_config_status_code = 'COMPLETE' [Close MC]
1093
1094 -- 7a. Validate whether there are no units for this MC
1095 OPEN check_unit_assigned(p_mc_header_id);
1096 FETCH check_unit_assigned INTO l_dummy_varchar;
1097 IF (check_unit_assigned%FOUND)
1098 THEN
1099 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_CLOSE_INVALID');
1100 FND_MSG_PUB.ADD;
1101 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
1102 THEN
1103 fnd_log.message
1104 (
1105 fnd_log.level_exception,
1106 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
1107 false
1108 );
1109 END IF;
1110 CLOSE check_unit_assigned;
1111 RAISE FND_API.G_EXC_ERROR;
1112 END IF;
1113 CLOSE check_unit_assigned;
1114
1115 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1116 THEN
1117 fnd_log.string
1118 (
1119 fnd_log.level_statement,
1120 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
1121 'Unit assignment for Header ['||p_mc_header_id||'] validated'
1122 );
1123 END IF;
1124
1125 -- 7b. Update the config_status_code = 'CLOSED' and object_version_number for the MC with MC_HEADER_ID = p_ mc_header_id
1126 UPDATE ahl_mc_headers_b
1127 SET object_version_number = p_object_ver_num + 1,
1128 config_status_code = 'CLOSED',
1129 last_update_date = G_SYSDATE,
1130 last_updated_by = G_USER_ID,
1131 last_update_login = G_LOGIN_ID
1132 WHERE mc_header_id = p_mc_header_id;
1133
1134 -- Query for the topnode of the MC with MC_HEADER_ID = p_mc_header_id
1135 OPEN get_mc_topnode_details(p_mc_header_id);
1136 FETCH get_mc_topnode_details INTO l_topnode_rel_id, l_topnode_object_ver_num;
1137 CLOSE get_mc_topnode_details;
1138
1139 -- 7c. Update the active_end_date of the topnode of the MC with MC_HEADER_ID = p_mc_header_id
1140 UPDATE ahl_mc_relationships
1141 SET active_end_date = G_TRUNC_DATE,
1142 object_version_number = l_topnode_object_ver_num + 1,
1143 last_update_date = G_SYSDATE,
1144 last_updated_by = G_USER_ID,
1145 last_update_login = G_LOGIN_ID
1146 WHERE relationship_id = l_topnode_rel_id;
1147
1148 -- ##TAMAL## -- Should expire all attached subconfiguration associations to the nodes of the MC?
1149 -- Consider the case that a particular draft MC automatically expires based on end-date, there is another draft
1150 -- MC associated with one of the nodes, the latter cannot be deleted ever since it is associated as a
1151 -- subconfig, since potentially the earlier MC can be reopened
1152
1153 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1154 THEN
1155 fnd_log.string
1156 (
1157 fnd_log.level_statement,
1158 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
1159 'Header ['||p_mc_header_id||'] and topnode ['||l_topnode_rel_id||'] closed'
1160 );
1161 END IF;
1162
1163 ELSE
1164 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_DELETE_STS_INV');
1165 FND_MSG_PUB.ADD;
1166 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
1167 THEN
1168 fnd_log.message
1169 (
1170 fnd_log.level_exception,
1171 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
1172 false
1173 );
1174 END IF;
1175 END IF;
1176
1177 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
1178 THEN
1179 fnd_log.string
1180 (
1181 fnd_log.level_procedure,
1182 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||'.end',
1183 'At the end of PLSQL procedure'
1184 );
1185 END IF;
1186 -- API body ends here
1187
1188 -- Check Error Message stack.
1189 x_msg_count := FND_MSG_PUB.count_msg;
1190 IF x_msg_count > 0 THEN
1191 RAISE FND_API.G_EXC_ERROR;
1192 END IF;
1193
1194 -- Standard check for p_commit
1195 IF FND_API.TO_BOOLEAN (p_commit)
1196 THEN
1197 COMMIT WORK;
1198 END IF;
1199
1200 -- Standard call to get message count and if count is 1, get message info
1201 FND_MSG_PUB.count_and_get
1202 (
1203 p_count => x_msg_count,
1204 p_data => x_msg_data,
1205 p_encoded => FND_API.G_FALSE
1206 );
1207
1208 EXCEPTION
1209 WHEN FND_API.G_EXC_ERROR THEN
1210 x_return_status := FND_API.G_RET_STS_ERROR;
1211 Rollback to Delete_Master_Config_SP;
1212 FND_MSG_PUB.count_and_get
1213 (
1214 p_count => x_msg_count,
1215 p_data => x_msg_data,
1216 p_encoded => FND_API.G_FALSE
1217 );
1218
1219 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1220 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1221 Rollback to Delete_Master_Config_SP;
1222 FND_MSG_PUB.count_and_get
1223 (
1224 p_count => x_msg_count,
1225 p_data => x_msg_data,
1226 p_encoded => FND_API.G_FALSE
1227 );
1228
1229 WHEN OTHERS THEN
1230 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1231 Rollback to Delete_Master_Config_SP;
1232 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1233 THEN
1234 FND_MSG_PUB.add_exc_msg
1235 (
1236 p_pkg_name => G_PKG_NAME,
1237 p_procedure_name => 'Delete_Master_Config',
1238 p_error_text => SUBSTR(SQLERRM,1,240)
1239 );
1240 END IF;
1241 FND_MSG_PUB.count_and_get
1242 (
1243 p_count => x_msg_count,
1244 p_data => x_msg_data,
1245 p_encoded => FND_API.G_FALSE
1246 );
1247
1248 END Delete_Master_Config;
1249
1250 PROCEDURE Copy_Master_Config
1251 (
1252 p_api_version IN NUMBER,
1253 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1254 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1255 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1256 x_return_status OUT NOCOPY VARCHAR2,
1257 x_msg_count OUT NOCOPY NUMBER,
1258 x_msg_data OUT NOCOPY VARCHAR2,
1259 p_x_mc_header_rec IN OUT NOCOPY Header_Rec_Type,
1260 p_x_node_rec IN OUT NOCOPY AHL_MC_Node_PVT.Node_Rec_Type
1261 )
1262 IS
1263
1264 -- 1. Define local variables
1265 l_api_name CONSTANT VARCHAR2(30) := 'Copy_Master_Config';
1266 l_api_version CONSTANT NUMBER := 1.0;
1267 l_return_status VARCHAR2(1);
1268 l_msg_count NUMBER;
1269 l_msg_data VARCHAR2(2000);
1270
1271 l_old_mc_header_id NUMBER;
1272 l_old_node_id NUMBER;
1273
1274 BEGIN
1275
1276 -- Standard start of API savepoint
1277 SAVEPOINT Copy_Master_Config_SP;
1278
1279 -- Standard call to check for call compatibility
1280 IF NOT FND_API.compatible_api_call(l_api_version, p_api_version, l_api_name, G_PKG_NAME)
1281 THEN
1282 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1283 END IF;
1284
1285 -- Initialize message list if p_init_msg_list is set to TRUE
1286 IF FND_API.TO_BOOLEAN(p_init_msg_list)
1287 THEN
1288 FND_MSG_PUB.Initialize;
1289 END IF;
1290
1291 -- Initialize API return status to success
1292 x_return_status := FND_API.G_RET_STS_SUCCESS;
1293
1294 -- API body starts here
1295 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
1296 THEN
1297 fnd_log.string
1298 (
1299 fnd_log.level_procedure,
1300 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||'.begin',
1301 'At the start of PLSQL procedure'
1302 );
1303 END IF;
1304
1305 Validate_MC_Exists (p_x_mc_header_rec.mc_header_id, nvl(p_x_mc_header_rec.object_version_number, 0));
1306
1307 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1308 THEN
1309 fnd_log.string
1310 (
1311 fnd_log.level_statement,
1312 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
1313 'Header validation successful'
1314 );
1315 END IF;
1316
1317 l_old_mc_header_id := p_x_mc_header_rec.mc_header_id;
1318 l_old_node_id := p_x_node_rec.relationship_id;
1319
1320 -- 5. Set values for p_x_mc_header_rec
1321 p_x_mc_header_rec.mc_header_id := null;
1322 p_x_mc_header_rec.config_status_code := 'DRAFT';
1323 p_x_mc_header_rec.mc_id := null;
1324 p_x_mc_header_rec.version_number := 1;
1325 p_x_mc_header_rec.operation_flag := G_DML_CREATE;
1326
1327 -- 6. Set values for p_x_node_rec
1328 p_x_node_rec.mc_header_id := null;
1329 p_x_node_rec.parent_relationship_id := null;
1330 p_x_node_rec.operation_flag := G_DML_COPY;
1331
1332 -- 7. Call AHL_MC_MasterConfig_PVT.Create_Master_Config
1333 AHL_MC_MasterConfig_PVT.Create_Master_Config
1334 (
1335 p_api_version => 1.0,
1336 p_init_msg_list => FND_API.G_FALSE,
1337 p_commit => FND_API.G_FALSE,
1338 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1339 x_return_status => l_return_status,
1340 x_msg_count => l_msg_count,
1341 x_msg_data => l_msg_data,
1342 p_x_mc_header_rec => p_x_mc_header_rec,
1343 p_x_node_rec => p_x_node_rec
1344 );
1345
1346 -- Check Error Message stack.
1347 x_msg_count := FND_MSG_PUB.count_msg;
1348 IF x_msg_count > 0 THEN
1349 RAISE FND_API.G_EXC_ERROR;
1350 END IF;
1351
1352 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1353 THEN
1354 fnd_log.string
1355 (
1356 fnd_log.level_statement,
1357 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
1358 'Header ['||p_x_mc_header_rec.mc_header_id||'] and topnode ['||p_x_node_rec.relationship_id||'] copied'
1359 );
1360
1361 fnd_log.string
1362 (
1363 fnd_log.level_statement,
1364 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
1365 'Copying documents from topnode ['||l_old_node_id||'] to topnode ['||p_x_node_rec.relationship_id||']'
1366 );
1367 END IF;
1368
1369 -- 8. Copy all documents associated with the topnode
1370 AHL_DI_ASSO_DOC_GEN_PVT.COPY_ASSOCIATION
1371 (
1372 p_api_version => 1.0,
1373 p_commit => FND_API.G_FALSE,
1374 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1375 p_from_object_id => l_old_node_id,
1376 p_from_object_type => 'MC',
1377 p_to_object_id => p_x_node_rec.relationship_id,
1378 p_to_object_type => 'MC',
1379 x_return_status => l_return_status,
1380 x_msg_count => l_msg_count,
1381 x_msg_data => l_msg_data
1382 );
1383
1384 -- Check Error Message stack.
1385 x_msg_count := FND_MSG_PUB.count_msg;
1386 IF x_msg_count > 0 THEN
1387 RAISE FND_API.G_EXC_ERROR;
1388 END IF;
1389
1390 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1391 THEN
1392 fnd_log.string
1393 (
1394 fnd_log.level_statement,
1395 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
1396 'Copying nodes'
1397 );
1398 END IF;
1399
1400 -- 9. Copy all other child nodes to the topnode of the source MC
1401 AHL_MC_Node_PVT.Copy_MC_Nodes
1402 (
1403 p_api_version => 1.0,
1404 p_init_msg_list => FND_API.G_FALSE,
1405 p_commit => FND_API.G_FALSE,
1406 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1407 x_return_status => l_return_status,
1408 x_msg_count => l_msg_count,
1409 x_msg_data => l_msg_data,
1410 p_source_rel_id => l_old_node_id,
1411 p_dest_rel_id => p_x_node_rec.relationship_id,
1412 p_new_rev_flag => FALSE,
1413 p_node_copy => FALSE
1414 );
1415
1416 -- Check Error Message stack.
1417 x_msg_count := FND_MSG_PUB.count_msg;
1418 IF x_msg_count > 0 THEN
1419 RAISE FND_API.G_EXC_ERROR;
1420 END IF;
1421
1422 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1423 THEN
1424 fnd_log.string
1425 (
1426 fnd_log.level_statement,
1427 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
1428 'Copying position paths'
1429 );
1430 END IF;
1431
1432 -- 10. Copy position paths from the old MC to the new MC
1433 AHL_MC_PATH_POSITION_PVT.Copy_Positions_For_MC
1434 (
1435 p_api_version => 1.0,
1436 p_init_msg_list => FND_API.G_FALSE,
1437 p_commit => FND_API.G_FALSE,
1438 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1439 x_return_status => l_return_status,
1440 x_msg_count => l_msg_count,
1441 x_msg_data => l_msg_data,
1442 p_from_mc_header_id => l_old_mc_header_id,
1443 p_to_mc_header_id => p_x_mc_header_rec.mc_header_id
1444 );
1445
1446 -- Check Error Message stack.
1447 x_msg_count := FND_MSG_PUB.count_msg;
1448 IF x_msg_count > 0 THEN
1449 RAISE FND_API.G_EXC_ERROR;
1450 END IF;
1451
1452 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1453 THEN
1454 fnd_log.string
1455 (
1456 fnd_log.level_statement,
1457 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
1458 'Copying rules'
1459 );
1460 END IF;
1461
1462 -- 11. Copy rules from the old MC to the new MC
1463 AHL_MC_RULE_PVT.Copy_Rules_For_MC
1464 (
1465 p_api_version => 1.0,
1466 p_init_msg_list => FND_API.G_FALSE,
1467 p_commit => FND_API.G_FALSE,
1468 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1469 x_return_status => l_return_status,
1470 x_msg_count => l_msg_count,
1471 x_msg_data => l_msg_data,
1472 p_from_mc_header_id => l_old_mc_header_id,
1473 p_to_mc_header_id => p_x_mc_header_rec.mc_header_id
1474 );
1475
1476 -- Check Error Message stack.
1477 x_msg_count := FND_MSG_PUB.count_msg;
1478 IF x_msg_count > 0 THEN
1479 RAISE FND_API.G_EXC_ERROR;
1480 END IF;
1481
1482 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
1483 THEN
1484 fnd_log.string
1485 (
1486 fnd_log.level_procedure,
1487 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||'.end',
1488 'At the end of PLSQL procedure'
1489 );
1490 END IF;
1491 -- API body ends here
1492
1493 -- Check Error Message stack.
1494 x_msg_count := FND_MSG_PUB.count_msg;
1495 IF x_msg_count > 0 THEN
1496 RAISE FND_API.G_EXC_ERROR;
1497 END IF;
1498
1499 -- Standard check for p_commit
1500 IF FND_API.TO_BOOLEAN (p_commit)
1501 THEN
1502 COMMIT WORK;
1503 END IF;
1504
1505 -- Standard call to get message count and if count is 1, get message info
1506 FND_MSG_PUB.count_and_get
1507 (
1508 p_count => x_msg_count,
1509 p_data => x_msg_data,
1510 p_encoded => FND_API.G_FALSE
1511 );
1512
1513 EXCEPTION
1514 WHEN FND_API.G_EXC_ERROR THEN
1515 x_return_status := FND_API.G_RET_STS_ERROR;
1516 Rollback to Copy_Master_Config_SP;
1517 FND_MSG_PUB.count_and_get
1518 (
1519 p_count => x_msg_count,
1520 p_data => x_msg_data,
1521 p_encoded => FND_API.G_FALSE
1522 );
1523
1524 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1525 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1526 Rollback to Copy_Master_Config_SP;
1527 FND_MSG_PUB.count_and_get
1528 (
1529 p_count => x_msg_count,
1530 p_data => x_msg_data,
1531 p_encoded => FND_API.G_FALSE
1532 );
1533
1534 WHEN OTHERS THEN
1535 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1536 Rollback to Copy_Master_Config_SP;
1537 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1538 THEN
1539 FND_MSG_PUB.add_exc_msg
1540 (
1541 p_pkg_name => G_PKG_NAME,
1542 p_procedure_name => 'Copy_Master_Config',
1543 p_error_text => SUBSTR(SQLERRM,1,240)
1544 );
1545 END IF;
1546 FND_MSG_PUB.count_and_get
1547 (
1548 p_count => x_msg_count,
1549 p_data => x_msg_data,
1550 p_encoded => FND_API.G_FALSE
1551 );
1552
1553 END Copy_Master_Config;
1554
1555 PROCEDURE Create_MC_Revision
1556 (
1557 p_api_version IN NUMBER,
1558 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1559 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1560 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1561 x_return_status OUT NOCOPY VARCHAR2,
1562 x_msg_count OUT NOCOPY NUMBER,
1563 x_msg_data OUT NOCOPY VARCHAR2,
1564 p_x_mc_header_id IN OUT NOCOPY NUMBER,
1565 p_object_ver_num IN NUMBER
1566 )
1567 IS
1568 -- 1. Define get_header_rec_csr to get the details of the header record for a particular MC
1569 CURSOR get_header_rec_csr
1570 (
1571 p_mc_header_id in number
1572 )
1573 IS
1574 SELECT MC_HEADER_ID,
1575 NAME,
1576 MC_ID,
1577 VERSION_NUMBER,
1578 REVISION,
1579 MODEL_CODE, -- SATHAPLI::Enigma code changes, 26-Aug-2008
1580 CONFIG_STATUS_CODE,
1581 SECURITY_GROUP_ID,
1582 ATTRIBUTE_CATEGORY,
1583 ATTRIBUTE1,
1584 ATTRIBUTE2,
1585 ATTRIBUTE3,
1586 ATTRIBUTE4,
1587 ATTRIBUTE5,
1588 ATTRIBUTE6,
1589 ATTRIBUTE7,
1590 ATTRIBUTE8,
1591 ATTRIBUTE9,
1592 ATTRIBUTE10,
1593 ATTRIBUTE11,
1594 ATTRIBUTE12,
1595 ATTRIBUTE13,
1596 ATTRIBUTE14,
1597 ATTRIBUTE15,
1598 DESCRIPTION
1599 FROM AHL_MC_HEADERS_VL
1600 WHERE MC_HEADER_ID = p_mc_header_id;
1601
1602 -- 2. Define get_topnode_rec_csr to get the details of the topnode record for a particular MC
1603 CURSOR get_topnode_rec_csr
1604 (
1605 p_mc_header_id in number
1606 )
1607 IS
1608 SELECT RELATIONSHIP_ID,
1609 POSITION_KEY,
1610 ITEM_GROUP_ID,
1611 GROUP_NAME,
1612 POSITION_REF_CODE,
1613 POSITION_REF_MEANING,
1614 POSITION_NECESSITY_CODE,
1615 POSITION_NECESSITY_MEANING,
1616 -- Priyan : Bug # 5639027
1617 ATA_CODE,
1618 ATA_MEANING,
1619 -- End Priyan : Bug # 5639027
1620 UOM_CODE,
1621 QUANTITY,
1622 DISPLAY_ORDER,
1623 ACTIVE_START_DATE,
1624 ACTIVE_END_DATE,
1625 SECURITY_GROUP_ID,
1626 ATTRIBUTE_CATEGORY,
1627 ATTRIBUTE1,
1628 ATTRIBUTE2,
1629 ATTRIBUTE3,
1630 ATTRIBUTE4,
1631 ATTRIBUTE5,
1632 ATTRIBUTE6,
1633 ATTRIBUTE7,
1634 ATTRIBUTE8,
1635 ATTRIBUTE9,
1636 ATTRIBUTE10,
1637 ATTRIBUTE11,
1638 ATTRIBUTE12,
1639 ATTRIBUTE13,
1640 ATTRIBUTE14,
1641 ATTRIBUTE15
1642 FROM AHL_MC_RELATIONSHIPS_V
1643 WHERE MC_HEADER_ID = p_mc_header_id AND
1644 PARENT_RELATIONSHIP_ID IS NULL;
1645
1646 -- Define cursor check_latest_rev to validate latest revision of MC being used for copying
1647 CURSOR check_latest_rev
1648 (
1649 p_mc_id in number,
1650 p_version_number in number
1651 )
1652 IS
1653 SELECT 'x'
1654 FROM ahl_mc_headers_b
1655 WHERE mc_id = p_mc_id AND
1656 nvl(version_number, 0) > nvl(p_version_number, 0);
1657
1658 -- 2. Define local variables
1659 l_api_name CONSTANT VARCHAR2(30) := 'Create_MC_Revision';
1660 l_api_version CONSTANT NUMBER := 1.0;
1661 l_return_status VARCHAR2(1);
1662 l_msg_count NUMBER;
1663 l_msg_data VARCHAR2(2000);
1664
1665 l_old_mc_header_id NUMBER;
1666 l_header_rec get_header_rec_csr%ROWTYPE;
1667 l_mc_header_rec Header_Rec_Type;
1668 l_old_node_id NUMBER;
1669 l_topnode_rec get_topnode_rec_csr%ROWTYPE;
1670 l_node_rec AHL_MC_Node_PVT.Node_Rec_Type;
1671
1672 BEGIN
1673
1674 -- Standard start of API savepoint
1675 SAVEPOINT Create_MC_Revision_SP;
1676
1677 -- Standard call to check for call compatibility
1678 IF NOT FND_API.compatible_api_call(l_api_version, p_api_version, l_api_name, G_PKG_NAME)
1679 THEN
1680 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1681 END IF;
1682
1683 -- Initialize message list if p_init_msg_list is set to TRUE
1684 IF FND_API.TO_BOOLEAN(p_init_msg_list)
1685 THEN
1686 FND_MSG_PUB.Initialize;
1687 END IF;
1688
1689 -- Initialize API return status to success
1690 x_return_status := FND_API.G_RET_STS_SUCCESS;
1691
1692 -- API body starts here
1693 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
1694 THEN
1695 fnd_log.string
1696 (
1697 fnd_log.level_procedure,
1698 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||'.begin',
1699 'At the start of PLSQL procedure'
1700 );
1701 END IF;
1702
1703 Validate_MC_Exists (p_x_mc_header_id, nvl(p_object_ver_num, 0));
1704
1705 OPEN get_header_rec_csr(p_x_mc_header_id);
1706 FETCH get_header_rec_csr INTO l_header_rec;
1707 CLOSE get_header_rec_csr;
1708
1709 l_old_mc_header_id := p_x_mc_header_id;
1710
1711 -- 7. Validate l_header_rec.config_status_code = 'COMPLETE'
1712 IF (l_header_rec.config_status_code <> 'COMPLETE')
1713 THEN
1714 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_REV_STS_INV');
1715 FND_MSG_PUB.ADD;
1716 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
1717 THEN
1718 fnd_log.message
1719 (
1720 fnd_log.level_exception,
1721 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
1722 false
1723 );
1724 END IF;
1725 END IF;
1726
1727 -- 8. Validate latest revision of MC being used for copying
1728 OPEN check_latest_rev (l_header_rec.mc_id, l_header_rec.version_number);
1729 FETCH check_latest_rev INTO l_dummy_varchar;
1730 IF (check_latest_rev%FOUND)
1731 THEN
1732 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_NOT_LATEST_REV');
1733 FND_MSG_PUB.ADD;
1734 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
1735 THEN
1736 fnd_log.message
1737 (
1738 fnd_log.level_exception,
1739 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
1740 false
1741 );
1742 END IF;
1743 END IF;
1744
1745 -- Check Error Message stack.
1746 x_msg_count := FND_MSG_PUB.count_msg;
1747 IF x_msg_count > 0 THEN
1748 RAISE FND_API.G_EXC_ERROR;
1749 END IF;
1750
1751 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1752 THEN
1753 fnd_log.string
1754 (
1755 fnd_log.level_statement,
1756 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
1757 'Header validation successful'
1758 );
1759 END IF;
1760
1761 -- 9. Set values for l_mc_header_rec
1762 l_mc_header_rec.mc_header_id := null;
1763 l_mc_header_rec.object_version_number := 1;
1764 l_mc_header_rec.mc_id := nvl(l_header_rec.mc_id, l_header_rec.mc_header_id);
1765 l_mc_header_rec.version_number := nvl(l_header_rec.version_number, 0) + 1;
1766 l_mc_header_rec.revision := null;
1767 l_mc_header_rec.model_code := l_header_rec.model_code; -- SATHAPLI::Enigma code changes, 26-Aug-2008
1768 l_mc_header_rec.config_status_code := 'DRAFT';
1769 l_mc_header_rec.name := l_header_rec.name;
1770 l_mc_header_rec.description := l_header_rec.description;
1771 l_mc_header_rec.security_group_id := l_header_rec.security_group_id;
1772 l_mc_header_rec.attribute_category := l_header_rec.attribute_category;
1773 l_mc_header_rec.attribute1 := l_header_rec.attribute1;
1774 l_mc_header_rec.attribute2 := l_header_rec.attribute2;
1775 l_mc_header_rec.attribute3 := l_header_rec.attribute3;
1776 l_mc_header_rec.attribute4 := l_header_rec.attribute4;
1777 l_mc_header_rec.attribute5 := l_header_rec.attribute5;
1778 l_mc_header_rec.attribute6 := l_header_rec.attribute6;
1779 l_mc_header_rec.attribute7 := l_header_rec.attribute7;
1780 l_mc_header_rec.attribute8 := l_header_rec.attribute8;
1781 l_mc_header_rec.attribute9 := l_header_rec.attribute9;
1782 l_mc_header_rec.attribute10 := l_header_rec.attribute10;
1783 l_mc_header_rec.attribute11 := l_header_rec.attribute11;
1784 l_mc_header_rec.attribute12 := l_header_rec.attribute12;
1785 l_mc_header_rec.attribute13 := l_header_rec.attribute13;
1786 l_mc_header_rec.attribute14 := l_header_rec.attribute14;
1787 l_mc_header_rec.attribute15 := l_header_rec.attribute15;
1788 l_mc_header_rec.operation_flag := G_DML_CREATE;
1789
1790 OPEN get_topnode_rec_csr(l_old_mc_header_id);
1791 FETCH get_topnode_rec_csr INTO l_topnode_rec;
1792 CLOSE get_topnode_rec_csr;
1793
1794 l_old_node_id := l_topnode_rec.relationship_id;
1795
1796 -- 13. Set values for l_node_rec
1797 l_node_rec.mc_header_id := null;
1798 l_node_rec.object_version_number := 1;
1799 l_node_rec.parent_relationship_id := null;
1800 l_node_rec.position_key := l_topnode_rec.position_key;
1801 l_node_rec.position_ref_code := l_topnode_rec.position_ref_code;
1802 l_node_rec.position_ref_meaning := l_topnode_rec.position_ref_meaning;
1803 l_node_rec.position_necessity_code := l_topnode_rec.position_necessity_code;
1804 l_node_rec.position_necessity_meaning := l_topnode_rec.position_necessity_meaning;
1805 -- Priyan : Bug # 5639027
1806 l_node_rec.ata_code := l_topnode_rec.ata_code;
1807 l_node_rec.ata_meaning := l_topnode_rec.ata_meaning;
1808 -- End Priyan : Bug # 5639027
1809 l_node_rec.uom_code := l_topnode_rec.uom_code;
1810 l_node_rec.quantity := l_topnode_rec.quantity;
1811 l_node_rec.display_order := l_topnode_rec.display_order;
1812 l_node_rec.item_group_id := l_topnode_rec.item_group_id;
1813 l_node_rec.item_group_name := l_topnode_rec.group_name;
1814 l_node_rec.active_start_date := l_topnode_rec.active_start_date;
1815 l_node_rec.active_end_date := l_topnode_rec.active_end_date;
1816 l_node_rec.security_group_id := l_topnode_rec.security_group_id;
1817 l_node_rec.attribute_category := l_topnode_rec.attribute_category;
1818 l_node_rec.attribute1 := l_topnode_rec.attribute1;
1819 l_node_rec.attribute2 := l_topnode_rec.attribute2;
1820 l_node_rec.attribute3 := l_topnode_rec.attribute3;
1821 l_node_rec.attribute4 := l_topnode_rec.attribute4;
1822 l_node_rec.attribute5 := l_topnode_rec.attribute5;
1823 l_node_rec.attribute6 := l_topnode_rec.attribute6;
1824 l_node_rec.attribute7 := l_topnode_rec.attribute7;
1825 l_node_rec.attribute8 := l_topnode_rec.attribute8;
1826 l_node_rec.attribute9 := l_topnode_rec.attribute9;
1827 l_node_rec.attribute10 := l_topnode_rec.attribute10;
1828 l_node_rec.attribute11 := l_topnode_rec.attribute11;
1829 l_node_rec.attribute12 := l_topnode_rec.attribute12;
1830 l_node_rec.attribute13 := l_topnode_rec.attribute13;
1831 l_node_rec.attribute14 := l_topnode_rec.attribute14;
1832 l_node_rec.attribute15 := l_topnode_rec.attribute15;
1833 l_node_rec.operation_flag := G_DML_COPY;
1834 l_node_rec.parent_node_rec_index := null;
1835
1836 -- 14. Call AHL_MC_MasterConfig_PVT.Create_Master_Config
1837 AHL_MC_MasterConfig_PVT.Create_Master_Config
1838 (
1839 p_api_version => 1.0,
1840 p_init_msg_list => FND_API.G_FALSE,
1841 p_commit => FND_API.G_FALSE,
1842 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1843 x_return_status => l_return_status,
1844 x_msg_count => l_msg_count,
1845 x_msg_data => l_msg_data,
1846 p_x_mc_header_rec => l_mc_header_rec,
1847 p_x_node_rec => l_node_rec
1848 );
1849
1850 -- Check Error Message stack.
1851 x_msg_count := FND_MSG_PUB.count_msg;
1852 IF x_msg_count > 0 THEN
1853 RAISE FND_API.G_EXC_ERROR;
1854 END IF;
1855
1856 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1857 THEN
1858 fnd_log.string
1859 (
1860 fnd_log.level_statement,
1861 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
1862 'Header ['||l_mc_header_rec.mc_header_id||'] and topnode ['||l_node_rec.relationship_id||'] created'
1863 );
1864 END IF;
1865
1866 -- 15. Set p_x_mc_header_id := l_header_rec.mc_header_id, to return the header-id of the created MC
1867 p_x_mc_header_id := l_mc_header_rec.mc_header_id;
1868
1869 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1870 THEN
1871 fnd_log.string
1872 (
1873 fnd_log.level_statement,
1874 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
1875 'Copying documents from topnode ['||l_old_node_id||'] to topnode ['||l_node_rec.relationship_id||']'
1876 );
1877 END IF;
1878
1879 -- 16. Copy all documents associated with the topnode
1880 AHL_DI_ASSO_DOC_GEN_PVT.COPY_ASSOCIATION
1881 (
1882 p_api_version => 1.0,
1883 p_commit => FND_API.G_FALSE,
1884 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1885 p_from_object_id => l_old_node_id,
1886 p_from_object_type => 'MC',
1887 p_to_object_id => l_node_rec.relationship_id,
1888 p_to_object_type => 'MC',
1889 x_return_status => l_return_status,
1890 x_msg_count => l_msg_count,
1891 x_msg_data => l_msg_data
1892 );
1893
1894 -- Check Error Message stack.
1895 x_msg_count := FND_MSG_PUB.count_msg;
1896 IF x_msg_count > 0 THEN
1897 RAISE FND_API.G_EXC_ERROR;
1898 END IF;
1899
1900 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1901 THEN
1902 fnd_log.string
1903 (
1904 fnd_log.level_statement,
1905 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
1906 'Copying nodes'
1907 );
1908 END IF;
1909
1910 -- 17. Copy all other child nodes to the topnode of the source MC
1911 AHL_MC_Node_PVT.Copy_MC_Nodes
1912 (
1913 p_api_version => 1.0,
1914 p_init_msg_list => FND_API.G_FALSE,
1915 p_commit => FND_API.G_FALSE,
1916 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1917 x_return_status => l_return_status,
1918 x_msg_count => l_msg_count,
1919 x_msg_data => l_msg_data,
1920 p_source_rel_id => l_old_node_id,
1921 p_dest_rel_id => l_node_rec.relationship_id,
1922 p_new_rev_flag => FALSE,
1923 p_node_copy => FALSE
1924 );
1925
1926 -- Check Error Message stack.
1927 x_msg_count := FND_MSG_PUB.count_msg;
1928 IF x_msg_count > 0 THEN
1929 RAISE FND_API.G_EXC_ERROR;
1930 END IF;
1931
1932 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1933 THEN
1934 fnd_log.string
1935 (
1936 fnd_log.level_statement,
1937 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
1938 'Copying position paths'
1939 );
1940 END IF;
1941
1942 -- 18. Copy position paths from the old MC to the new MC
1943 AHL_MC_PATH_POSITION_PVT.Copy_Positions_For_MC
1944 (
1945 p_api_version => 1.0,
1946 p_init_msg_list => FND_API.G_FALSE,
1947 p_commit => FND_API.G_FALSE,
1948 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1949 x_return_status => l_return_status,
1950 x_msg_count => l_msg_count,
1951 x_msg_data => l_msg_data,
1952 p_from_mc_header_id => l_old_mc_header_id,
1953 p_to_mc_header_id => p_x_mc_header_id
1954 );
1955
1956 -- Check Error Message stack.
1957 x_msg_count := FND_MSG_PUB.count_msg;
1958 IF x_msg_count > 0 THEN
1959 RAISE FND_API.G_EXC_ERROR;
1960 END IF;
1961
1962 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1963 THEN
1964 fnd_log.string
1965 (
1966 fnd_log.level_statement,
1967 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
1968 'Copying rules'
1969 );
1970 END IF;
1971
1972 -- 12. Copy rules from the old MC to the new MC
1973 AHL_MC_RULE_PVT.Copy_Rules_For_MC
1974 (
1975 p_api_version => 1.0,
1976 p_init_msg_list => FND_API.G_FALSE,
1977 p_commit => FND_API.G_FALSE,
1978 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1979 x_return_status => l_return_status,
1980 x_msg_count => l_msg_count,
1981 x_msg_data => l_msg_data,
1982 p_from_mc_header_id => l_old_mc_header_id,
1983 p_to_mc_header_id => p_x_mc_header_id
1984 );
1985
1986 -- Check Error Message stack.
1987 x_msg_count := FND_MSG_PUB.count_msg;
1988 IF x_msg_count > 0 THEN
1989 RAISE FND_API.G_EXC_ERROR;
1990 END IF;
1991
1992 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
1993 THEN
1994 fnd_log.string
1995 (
1996 fnd_log.level_procedure,
1997 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||'.end',
1998 'At the end of PLSQL procedure'
1999 );
2000 END IF;
2001 -- API body ends here
2002
2003 -- Check Error Message stack.
2004 x_msg_count := FND_MSG_PUB.count_msg;
2005 IF x_msg_count > 0 THEN
2006 RAISE FND_API.G_EXC_ERROR;
2007 END IF;
2008
2009 -- Standard check for p_commit
2010 IF FND_API.TO_BOOLEAN (p_commit)
2011 THEN
2012 COMMIT WORK;
2013 END IF;
2014
2015 -- Standard call to get message count and if count is 1, get message info
2016 FND_MSG_PUB.count_and_get
2017 (
2018 p_count => x_msg_count,
2019 p_data => x_msg_data,
2020 p_encoded => FND_API.G_FALSE
2021 );
2022
2023 EXCEPTION
2024 WHEN FND_API.G_EXC_ERROR THEN
2025 x_return_status := FND_API.G_RET_STS_ERROR;
2026 Rollback to Create_MC_Revision_SP;
2027 FND_MSG_PUB.count_and_get
2028 (
2029 p_count => x_msg_count,
2030 p_data => x_msg_data,
2031 p_encoded => FND_API.G_FALSE
2032 );
2033
2034 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2035 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2036 Rollback to Create_MC_Revision_SP;
2037 FND_MSG_PUB.count_and_get
2038 (
2039 p_count => x_msg_count,
2040 p_data => x_msg_data,
2041 p_encoded => FND_API.G_FALSE
2042 );
2043
2044 WHEN OTHERS THEN
2045 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2046 Rollback to Create_MC_Revision_SP;
2047 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2048 THEN
2049 FND_MSG_PUB.add_exc_msg
2050 (
2051 p_pkg_name => G_PKG_NAME,
2052 p_procedure_name => 'Create_MC_Revision',
2053 p_error_text => SUBSTR(SQLERRM,1,240)
2054 );
2055 END IF;
2056 FND_MSG_PUB.count_and_get
2057 (
2058 p_count => x_msg_count,
2059 p_data => x_msg_data,
2060 p_encoded => FND_API.G_FALSE
2061 );
2062
2063 END Create_MC_Revision;
2064
2065 PROCEDURE Reopen_Master_Config
2066 (
2067 p_api_version IN NUMBER,
2068 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2069 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2070 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2071 x_return_status OUT NOCOPY VARCHAR2,
2072 x_msg_count OUT NOCOPY NUMBER,
2073 x_msg_data OUT NOCOPY VARCHAR2,
2074 p_mc_header_id IN NUMBER,
2075 p_object_ver_num IN NUMBER
2076 )
2077 IS
2078 -- Define cursor get_mc_details to read details of the MC
2079 CURSOR get_mc_details
2080 (
2081 p_mc_header_id in number
2082 )
2083 IS
2084 SELECT mch.config_status_code,
2085 mcr.relationship_id,
2086 mcr.object_version_number,
2087 mcr.item_group_id
2088 FROM ahl_mc_headers_v mch,
2089 ahl_mc_relationships mcr
2090 WHERE mch.mc_header_id = p_mc_header_id AND
2091 mch.mc_header_id = mcr.mc_header_id AND
2092 mcr.parent_relationship_id IS NULL;
2093
2094 -- Define get_mc_topnode_details to read the details of the MC topnode
2095 CURSOR get_mc_topnode_details
2096 (
2097 p_mc_header_id in number
2098 )
2099 IS
2100 SELECT relationship_id, object_version_number
2101 FROM ahl_mc_relationships
2102 WHERE mc_header_id = p_mc_header_id AND
2103 parent_relationship_id IS NULL;
2104
2105 -- Define get_topnode_item_group to retrieve the item group details of the top node
2106 CURSOR get_topnode_itemgroup
2107 (
2108 p_item_group_id in number
2109 )
2110 IS
2111 SELECT status_code,object_version_number
2112 FROM ahl_item_groups_b igp
2113 WHERE item_group_id = p_item_group_id;
2114
2115 -- Define cursor get_mc_status to retrieve the old/actual status of expired MCs
2116 CURSOR get_mc_status
2117 (
2118 p_mc_header_id in number
2119 )
2120 IS
2121 SELECT config_status_code
2122 FROM ahl_mc_headers_b
2123 WHERE mc_header_id = p_mc_header_id;
2124
2125 -- 1. Define local variables
2126 l_api_name CONSTANT VARCHAR2(30) := 'Reopen_Master_Config';
2127 l_api_version CONSTANT NUMBER := 1.0;
2128 l_return_status VARCHAR2(1);
2129 l_msg_count NUMBER;
2130 l_msg_data VARCHAR2(2000);
2131
2132 l_config_status_code VARCHAR2(30);
2133 l_topnode_rel_id NUMBER;
2134 l_topnode_object_ver_num NUMBER;
2135
2136 -- added for item group validation
2137 l_item_group_status VARCHAR2(30);
2138 l_item_group_id NUMBER;
2139 l_igp_object_ver_num NUMBER;
2140
2141 BEGIN
2142
2143 -- Standard start of API savepoint
2144 SAVEPOINT Reopen_Master_Config_SP;
2145
2146 -- Standard call to check for call compatibility
2147 IF NOT FND_API.compatible_api_call(l_api_version, p_api_version, l_api_name, G_PKG_NAME)
2148 THEN
2149 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2150 END IF;
2151
2152 -- Initialize message list if p_init_msg_list is set to TRUE
2153 IF FND_API.TO_BOOLEAN(p_init_msg_list)
2154 THEN
2155 FND_MSG_PUB.Initialize;
2156 END IF;
2157
2158 -- Initialize API return status to success
2159 x_return_status := FND_API.G_RET_STS_SUCCESS;
2160
2161 -- API body starts here
2162 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
2163 THEN
2164 fnd_log.string
2165 (
2166 fnd_log.level_procedure,
2167 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||'.begin',
2168 'At the start of PLSQL procedure'
2169 );
2170 END IF;
2171
2172 -- 2. Validate a MC with mc_header_id = p_mc_header_id exists, and object_version_number = p_object_ver_num
2173 Validate_MC_Exists (p_mc_header_id, nvl(p_object_ver_num, 0));
2174
2175 -- 3. Query for the config_status_code, active_end_date of the MC with mc_header_id = p_mc_header_id
2176 -- Query for the topnode details of the MC
2177 OPEN get_mc_details (p_mc_header_id);
2178 FETCH get_mc_details INTO l_config_status_code, l_topnode_rel_id, l_topnode_object_ver_num,l_item_group_id;
2179 CLOSE get_mc_details;
2180
2181 IF NOT (l_config_status_code IN ('CLOSED', 'EXPIRED'))
2182 THEN
2183 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_REOPEN_INV_MC');
2184 FND_MSG_PUB.ADD;
2185 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
2186 THEN
2187 fnd_log.message
2188 (
2189 fnd_log.level_exception,
2190 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
2191 false
2192 );
2193 END IF;
2194 RAISE FND_API.G_EXC_ERROR;
2195 ELSIF (l_config_status_code = 'CLOSED')
2196 THEN
2197 l_config_status_code := 'COMPLETE';
2198 ELSE
2199 OPEN get_mc_status(p_mc_header_id);
2200 FETCH get_mc_status INTO l_config_status_code;
2201 CLOSE get_mc_status;
2202 END IF;
2203
2204 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
2205 THEN
2206 fnd_log.string
2207 (
2208 fnd_log.level_statement,
2209 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
2210 'Header validation successful'
2211 );
2212 END IF;
2213
2214 -- added by anraj to fix bug number 3908014
2215 -- Check whether the Item Group of the Top node is removed
2216 -- if Removed then Re-open the Item Group.
2217 OPEN get_topnode_itemgroup(l_item_group_id);
2218 FETCH get_topnode_itemgroup INTO l_item_group_status,l_igp_object_ver_num;
2219 IF (get_topnode_itemgroup%NOTFOUND) THEN
2220 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_ITEMGROUP_INVALID');
2221 --FND_MESSAGE.Set_Token('ITEM_GRP', l_item_group_name);
2222 FND_MSG_PUB.ADD;
2223 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
2224 THEN
2225 fnd_log.message
2226 (
2227 fnd_log.level_exception,
2228 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
2229 false
2230 );
2231 END IF;
2232 RAISE FND_API.G_EXC_ERROR;
2233 ELSE
2234 IF(l_item_group_status = 'REMOVED') THEN
2235 UPDATE ahl_item_groups_b
2236 SET status_code ='COMPLETE',
2237 object_version_number = object_version_number +1
2238 WHERE item_group_id = l_item_group_id;
2239 END IF;
2240 END IF;
2241
2242
2243
2244 -- 4. Reopen the MC header
2245 UPDATE ahl_mc_headers_b
2246 SET config_status_code = l_config_status_code,
2247 object_version_number = p_object_ver_num + 1,
2248 last_update_date = G_SYSDATE,
2249 last_updated_by = G_USER_ID,
2250 last_update_login = G_LOGIN_ID
2251 WHERE mc_header_id = p_mc_header_id;
2252
2253 -- 5. Update the topnode of the MC
2254 UPDATE ahl_mc_relationships
2255 SET active_end_date = null,
2256 object_version_number = l_topnode_object_ver_num + 1,
2257 last_update_date = G_SYSDATE,
2258 last_updated_by = G_USER_ID,
2259 last_update_login = G_LOGIN_ID
2260 WHERE relationship_id = l_topnode_rel_id;
2261
2262 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
2263 THEN
2264 fnd_log.string
2265 (
2266 fnd_log.level_statement,
2267 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
2268 'Header ['||p_mc_header_id||'] and topnode ['||l_topnode_rel_id||'] reopened'
2269 );
2270 END IF;
2271
2272 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
2273 THEN
2274 fnd_log.string
2275 (
2276 fnd_log.level_procedure,
2277 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||'.end',
2278 'At the end of PLSQL procedure'
2279 );
2280 END IF;
2281 -- API body ends here
2282
2283 -- Check Error Message stack.
2284 x_msg_count := FND_MSG_PUB.count_msg;
2285 IF x_msg_count > 0 THEN
2286 RAISE FND_API.G_EXC_ERROR;
2287 END IF;
2288
2289 -- Standard check for p_commit
2290 IF FND_API.TO_BOOLEAN (p_commit)
2291 THEN
2292 COMMIT WORK;
2293 END IF;
2294
2295 -- Standard call to get message count and if count is 1, get message info
2296 FND_MSG_PUB.count_and_get
2297 (
2298 p_count => x_msg_count,
2299 p_data => x_msg_data,
2300 p_encoded => FND_API.G_FALSE
2301 );
2302
2303 EXCEPTION
2304 WHEN FND_API.G_EXC_ERROR THEN
2305 x_return_status := FND_API.G_RET_STS_ERROR;
2306 Rollback to Reopen_Master_Config_SP;
2307 FND_MSG_PUB.count_and_get
2308 (
2309 p_count => x_msg_count,
2310 p_data => x_msg_data,
2311 p_encoded => FND_API.G_FALSE
2312 );
2313
2314 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2315 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2316 Rollback to Reopen_Master_Config_SP;
2317 FND_MSG_PUB.count_and_get
2318 (
2319 p_count => x_msg_count,
2320 p_data => x_msg_data,
2321 p_encoded => FND_API.G_FALSE
2322 );
2323
2324 WHEN OTHERS THEN
2325 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2326 Rollback to Reopen_Master_Config_SP;
2327 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2328 THEN
2329 FND_MSG_PUB.add_exc_msg
2330 (
2331 p_pkg_name => G_PKG_NAME,
2332 p_procedure_name => 'Reopen_Master_Config',
2333 p_error_text => SUBSTR(SQLERRM,1,240)
2334 );
2335 END IF;
2336 FND_MSG_PUB.count_and_get
2337 (
2338 p_count => x_msg_count,
2339 p_data => x_msg_data,
2340 p_encoded => FND_API.G_FALSE
2341 );
2342
2343 END Reopen_Master_Config;
2344
2345 PROCEDURE Initiate_MC_Approval
2346 (
2347 p_api_version IN NUMBER,
2348 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2349 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2350 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2351 x_return_status OUT NOCOPY VARCHAR2,
2352 x_msg_count OUT NOCOPY NUMBER,
2353 x_msg_data OUT NOCOPY VARCHAR2,
2354 p_mc_header_id IN NUMBER,
2355 p_object_ver_num IN NUMBER
2356 )
2357 IS
2358
2359 -- 1. Define local variables
2360 l_api_name CONSTANT VARCHAR2(30) := 'Initiate_MC_Approval';
2361 l_api_version CONSTANT NUMBER := 1.0;
2362 l_return_status VARCHAR2(1);
2363 l_msg_count NUMBER;
2364 l_msg_data VARCHAR2(2000);
2365
2366 l_config_status_code VARCHAR2(30);
2367 l_object_version_number NUMBER;
2368 l_active VARCHAR2(1);
2369 l_process_name VARCHAR2(30);
2370 l_item_type VARCHAR2(8);
2371
2372 BEGIN
2373
2374 -- Standard start of API savepoint
2375 SAVEPOINT Initiate_MC_Approval_SP;
2376
2377 -- Standard call to check for call compatibility
2378 IF NOT FND_API.compatible_api_call(l_api_version, p_api_version, l_api_name, G_PKG_NAME)
2379 THEN
2380 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2381 END IF;
2382
2383 -- Initialize message list if p_init_msg_list is set to TRUE
2384 IF FND_API.TO_BOOLEAN(p_init_msg_list)
2385 THEN
2386 FND_MSG_PUB.Initialize;
2387 END IF;
2388
2389 -- Initialize API return status to success
2390 x_return_status := FND_API.G_RET_STS_SUCCESS;
2391
2392 -- API body starts here
2393 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
2394 THEN
2395 fnd_log.string
2396 (
2397 fnd_log.level_procedure,
2398 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||'.begin',
2399 'At the start of PLSQL procedure'
2400 );
2401 END IF;
2402
2403 Validate_MC_Exists(p_mc_header_id, nvl(p_object_ver_num, 0));
2404
2405 -- Check for status = draft / approval rejected
2406 l_config_status_code := Get_MC_Status(p_mc_header_id);
2407 IF NOT (l_config_status_code IN ('DRAFT', 'APPROVAL_REJECTED'))
2408 THEN
2409 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_WF_STS_INVALID');
2410 FND_MSG_PUB.ADD;
2411 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
2412 THEN
2413 fnd_log.message
2414 (
2415 fnd_log.level_exception,
2416 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
2417 false
2418 );
2419 END IF;
2420 RAISE FND_API.G_EXC_ERROR;
2421 END IF;
2422
2423 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
2424 THEN
2425 fnd_log.string
2426 (
2427 fnd_log.level_statement,
2428 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
2429 'Header validation successful'
2430 );
2431 END IF;
2432
2433 -- 4. Call Check_MC_Complete to validate all itemgroup and subconfiguration associations are complete
2434 Check_MC_Complete (p_mc_header_id);
2435
2436 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
2437 THEN
2438 fnd_log.string
2439 (
2440 fnd_log.level_statement,
2441 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
2442 'Check for MC completion is successful'
2443 );
2444 END IF;
2445
2446 -- Check Error Message stack.
2447 x_msg_count := FND_MSG_PUB.count_msg;
2448 IF x_msg_count > 0
2449 THEN
2450 RAISE FND_API.G_EXC_ERROR;
2451 END IF;
2452
2453 -- No need to check for any profile option for enabled/disabled workflow
2454
2455 -- 5a. Retrieve the workflow process name for object 'MC'
2456 ahl_utility_pvt.get_wf_process_name
2457 (
2458 p_object => 'MC',
2459 x_active => l_active,
2460 x_process_name => l_process_name ,
2461 x_item_type => l_item_type,
2462 x_return_status => l_return_status,
2463 x_msg_count => l_msg_count,
2464 x_msg_data => l_msg_data
2465 );
2466
2467 -- Check Error Message stack.
2468 x_msg_count := FND_MSG_PUB.count_msg;
2469 IF x_msg_count > 0 THEN
2470 RAISE FND_API.G_EXC_ERROR;
2471 END IF;
2472
2473 IF (l_active = 'Y')
2474 THEN
2475 -- 5b. If workflow is active
2476 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
2477 THEN
2478 fnd_log.string
2479 (
2480 fnd_log.level_statement,
2481 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
2482 'MC Approval workflow process is active'
2483 );
2484 END IF;
2485
2486 -- 54b.i. Update the status and object_version_number of the MC header record
2487 UPDATE ahl_mc_headers_b
2488 SET config_status_code = 'APPROVAL_PENDING',
2489 object_version_number = p_object_ver_num + 1,
2490 last_update_date = G_SYSDATE,
2491 last_updated_by = G_USER_ID,
2492 last_update_login = G_LOGIN_ID
2493 WHERE mc_header_id = p_mc_header_id AND
2494 object_version_number = p_object_ver_num;
2495
2496 -- 5b.ii. Start the 'MC' approval process for this MC
2497 ahl_generic_aprv_pvt.start_wf_process
2498 (
2499 P_OBJECT => 'MC',
2500 P_ACTIVITY_ID => p_mc_header_id,
2501 P_APPROVAL_TYPE => 'CONCEPT',
2502 P_OBJECT_VERSION_NUMBER => p_object_ver_num + 1,
2503 P_ORIG_STATUS_CODE => 'DRAFT',
2504 P_NEW_STATUS_CODE => 'COMPLETE',
2505 P_REJECT_STATUS_CODE => 'APPROVAL_REJECTED',
2506 P_REQUESTER_USERID => G_USER_ID,
2507 P_NOTES_FROM_REQUESTER => null,
2508 P_WORKFLOWPROCESS => l_process_name,
2509 P_ITEM_TYPE => l_item_type
2510 );
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 'Approval process for MC ['||p_mc_header_id||', '||to_char(p_object_ver_num + 1)||'] has been started'
2519 );
2520 END IF;
2521 ELSE
2522 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
2523 THEN
2524 fnd_log.string
2525 (
2526 fnd_log.level_statement,
2527 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
2528 'MC Approval workflow process is inactive'
2529 );
2530 END IF;
2531
2532 -- If wortkflow process is inactive, then force complete the MC
2533 UPDATE ahl_mc_headers_b
2534 SET config_status_code = 'COMPLETE',
2535 object_version_number = p_object_ver_num + 1,
2536 last_update_date = G_SYSDATE,
2537 last_updated_by = G_USER_ID,
2538 last_update_login = G_LOGIN_ID
2539 WHERE mc_header_id = p_mc_header_id AND
2540 object_version_number = p_object_ver_num;
2541 END IF;
2542
2543 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
2544 THEN
2545 fnd_log.string
2546 (
2547 fnd_log.level_procedure,
2548 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||'.end',
2549 'At the end of PLSQL procedure'
2550 );
2551 END IF;
2552 -- API body ends here
2553
2554 -- Check Error Message stack.
2555 x_msg_count := FND_MSG_PUB.count_msg;
2556 IF x_msg_count > 0 THEN
2557 RAISE FND_API.G_EXC_ERROR;
2558 END IF;
2559
2560 -- Standard check for p_commit
2561 IF FND_API.TO_BOOLEAN (p_commit)
2562 THEN
2563 COMMIT WORK;
2564 END IF;
2565
2566 -- Standard call to get message count and if count is 1, get message info
2567 FND_MSG_PUB.count_and_get
2568 (
2569 p_count => x_msg_count,
2570 p_data => x_msg_data,
2571 p_encoded => FND_API.G_FALSE
2572 );
2573
2574 EXCEPTION
2575 WHEN FND_API.G_EXC_ERROR THEN
2576 x_return_status := FND_API.G_RET_STS_ERROR;
2577 Rollback to Initiate_MC_Approval_SP;
2578 FND_MSG_PUB.count_and_get
2579 (
2580 p_count => x_msg_count,
2581 p_data => x_msg_data,
2582 p_encoded => FND_API.G_FALSE
2583 );
2584
2585 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2586 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2587 Rollback to Initiate_MC_Approval_SP;
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 WHEN OTHERS THEN
2596 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2597 Rollback to Initiate_MC_Approval_SP;
2598 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2599 THEN
2600 FND_MSG_PUB.add_exc_msg
2601 (
2602 p_pkg_name => G_PKG_NAME,
2603 p_procedure_name => 'Initiate_MC_Approval',
2604 p_error_text => SUBSTR(SQLERRM,1,240)
2605 );
2606 END IF;
2607 FND_MSG_PUB.count_and_get
2608 (
2609 p_count => x_msg_count,
2610 p_data => x_msg_data,
2611 p_encoded => FND_API.G_FALSE
2612 );
2613
2614 END Initiate_MC_Approval;
2615
2616 ---------------------------
2617 -- Validation procedures --
2618 ---------------------------
2619 PROCEDURE Validate_MC_Exists
2620 (
2621 p_mc_header_id in number,
2622 p_object_ver_num in number
2623 )
2624 IS
2625
2626 CURSOR check_mc_exists
2627 (
2628 p_mc_header_id in number
2629 )
2630 IS
2631 SELECT object_version_number
2632 FROM ahl_mc_headers_b
2633 WHERE mc_header_id = p_mc_header_id;
2634
2635 BEGIN
2636
2637 OPEN check_mc_exists (p_mc_header_id);
2638 FETCH check_mc_exists INTO l_dummy_number;
2639 IF (check_mc_exists%NOTFOUND)
2640 THEN
2641 FND_MESSAGE.Set_Name('AHL','AHL_MC_NOT_FOUND');
2642 FND_MSG_PUB.ADD;
2643 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
2644 THEN
2645 fnd_log.message
2646 (
2647 fnd_log.level_exception,
2648 'ahl.plsql.'||G_PKG_NAME||'.Validate_MC_Exists',
2649 false
2650 );
2651 END IF;
2652 CLOSE check_mc_exists;
2653 RAISE FND_API.G_EXC_ERROR;
2654 ELSIF (NVL(p_object_ver_num, l_dummy_number) <> l_dummy_number)
2655 THEN
2656 FND_MESSAGE.Set_Name('AHL', 'AHL_COM_RECORD_CHANGED');
2657 FND_MSG_PUB.ADD;
2658 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
2659 THEN
2660 fnd_log.message
2661 (
2662 fnd_log.level_exception,
2663 'ahl.plsql.'||G_PKG_NAME||'.Validate_MC_Exists',
2664 false
2665 );
2666 END IF;
2667 CLOSE check_mc_exists;
2668 RAISE FND_API.G_EXC_ERROR;
2669 END IF;
2670 CLOSE check_mc_exists;
2671
2672 END Validate_MC_Exists;
2673
2674 -- Define procedure Validate_MC_Name to check uniqueness / non-update of the user-entered MC name
2675 PROCEDURE Validate_MC_Name
2676 (
2677 p_x_mc_header_rec in Header_Rec_Type
2678 )
2679 IS
2680
2681 CURSOR check_mc_name_unique
2682 (
2683 p_mc_name in varchar2
2684 )
2685 IS
2686 SELECT 'x'
2687 FROM ahl_mc_headers_b
2688 WHERE upper(name) = upper(p_mc_name);
2689
2690 CURSOR check_mc_name_noupdate
2691 (
2692 p_mc_header_id in number
2693 )
2694 IS
2695 SELECT name
2696 FROM ahl_mc_headers_b
2697 WHERE mc_header_id = p_mc_header_id;
2698
2699 l_dummy_name VARCHAR2(80);
2700 BEGIN
2701
2702 -- Confirm user has entered MC Name, since it is mandatory
2703 IF (RTRIM(p_x_mc_header_rec.name) IS NULL)
2704 THEN
2705 FND_MESSAGE.Set_Name('AHL','AHL_MC_NAME_INVALID');
2706 FND_MSG_PUB.ADD;
2707 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
2708 THEN
2709 fnd_log.message
2710 (
2711 fnd_log.level_exception,
2712 'ahl.plsql.'||G_PKG_NAME||'.Validate_MC_Name',
2713 false
2714 );
2715 END IF;
2716 ELSE
2717 IF (p_x_mc_header_rec.mc_header_id IS NULL)
2718 THEN
2719 -- Implies MC is being created, check name uniqueness
2720 OPEN check_mc_name_unique (p_x_mc_header_rec.name);
2721 FETCH check_mc_name_unique INTO l_dummy_varchar;
2722 IF (check_mc_name_unique%FOUND)
2723 THEN
2724 FND_MESSAGE.Set_Name('AHL','AHL_MC_RNAME_EXISTS');
2725 FND_MSG_PUB.ADD;
2726 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
2727 THEN
2728 fnd_log.message
2729 (
2730 fnd_log.level_exception,
2731 'ahl.plsql.'||G_PKG_NAME||'.Validate_MC_Name',
2732 false
2733 );
2734 END IF;
2735 END IF;
2736 CLOSE check_mc_name_unique;
2737 ELSE
2738 -- Implies MC is being updated, check name is not updated
2739 OPEN check_mc_name_noupdate(p_x_mc_header_rec.mc_header_id);
2740 FETCH check_mc_name_noupdate INTO l_dummy_name;
2741 IF (l_dummy_name <> p_x_mc_header_rec.name)
2742 THEN
2743 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_RNAME_NOUPDATE');
2744 FND_MSG_PUB.ADD;
2745 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
2746 THEN
2747 fnd_log.message
2748 (
2749 fnd_log.level_exception,
2750 'ahl.plsql.'||G_PKG_NAME||'.Validate_MC_Name',
2751 false
2752 );
2753 END IF;
2754 END IF;
2755 CLOSE check_mc_name_noupdate;
2756 END IF;
2757 END IF;
2758
2759 END Validate_MC_Name;
2760
2761 -- Define procedure Validate_MC_Revision to check for uniqueness of the MC revision
2762 PROCEDURE Validate_MC_Revision
2763 (
2764 p_x_mc_header_rec in Header_Rec_Type
2765 )
2766 IS
2767
2768 CURSOR check_mc_revision_unique
2769 (
2770 p_mc_header_id in number,
2771 p_mc_revision in varchar2,
2772 p_mc_id in number
2773 )
2774 IS
2775 SELECT 'x'
2776 FROM ahl_mc_headers_b
2777 WHERE upper(revision) = upper(p_mc_revision) AND
2778 mc_id = p_mc_id and
2779 mc_header_id <> p_mc_header_id;
2780
2781 CURSOR get_mc_revision
2782 (
2783 p_mc_header_id in number
2784 )
2785 IS
2786 SELECT revision
2787 FROM ahl_mc_headers_b
2788 WHERE mc_header_id = p_mc_header_id;
2789
2790 l_ret_val BOOLEAN;
2791 l_dummy_rev VARCHAR2(30);
2792 l_str_len NUMBER;
2793 l_temp_num NUMBER;
2794
2795 BEGIN
2796
2797 IF (RTRIM(p_x_mc_header_rec.revision) IS NOT NULL)
2798 THEN
2799 -- p_x_mc_header_rec.mc_id = p_x_mc_header_rec.mc_header_id for the first draft of the MC
2800 IF (p_x_mc_header_rec.mc_id <> p_x_mc_header_rec.mc_header_id)
2801 THEN
2802 -- Confirm it is unique across all revisions of the same MC
2803 OPEN check_mc_revision_unique (p_x_mc_header_rec.mc_header_id, p_x_mc_header_rec.revision, p_x_mc_header_rec.mc_id);
2804 FETCH check_mc_revision_unique INTO l_dummy_varchar;
2805 IF (check_mc_revision_unique%FOUND)
2806 THEN
2807 FND_MESSAGE.Set_Name('AHL','AHL_MC_REV_EXISTS');
2808 FND_MSG_PUB.ADD;
2809 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
2810 THEN
2811 fnd_log.message
2812 (
2813 fnd_log.level_exception,
2814 'ahl.plsql.'||G_PKG_NAME||'.Validate_MC_Revision',
2815 false
2816 );
2817 END IF;
2818 END IF;
2819 CLOSE check_mc_revision_unique;
2820 END IF;
2821
2822 OPEN get_mc_revision(p_x_mc_header_rec.mc_header_id);
2823 FETCH get_mc_revision INTO l_dummy_rev;
2824 CLOSE get_mc_revision;
2825
2826 -- Confirm that the revision is user-entered and not the same as in the DB (it may be populated
2827 -- through the backend and is typically a number)
2828 IF (nvl(l_dummy_rev, -1) <> p_x_mc_header_rec.revision)
2829 THEN
2830 -- Confirm that the user-entered revision has atleast one alphabetic character
2831 BEGIN
2832 l_str_len := LENGTH(p_x_mc_header_rec.revision);
2833
2834 -- There should be something faster than the following iterative approach
2835 FOR i IN 1..l_str_len
2836 LOOP
2837 SELECT TO_NUMBER(SUBSTR(p_x_mc_header_rec.revision,i,1)) INTO l_temp_num FROM DUAL;
2838 END LOOP;
2839
2840 FND_MESSAGE.Set_Name('AHL','AHL_MC_NO_ALPHA_REV');
2841 FND_MSG_PUB.ADD;
2842 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
2843 THEN
2844 fnd_log.message
2845 (
2846 fnd_log.level_exception,
2847 'ahl.plsql.'||G_PKG_NAME||'.Validate_MC_Revision',
2848 false
2849 );
2850 END IF;
2851
2852 EXCEPTION
2853 WHEN INVALID_NUMBER THEN
2854 NULL;
2855 END;
2856 END IF;
2857 END IF;
2858
2859 END Validate_MC_Revision;
2860
2861 -------------------------
2862 -- Non-spec Procedures --
2863 -------------------------
2864 FUNCTION Get_MC_Status
2865 (
2866 p_mc_header_id in number
2867 )
2868 RETURN VARCHAR2
2869 IS
2870 CURSOR get_mc_status
2871 IS
2872 SELECT config_status_code
2873 FROM ahl_mc_headers_v
2874 WHERE mc_header_id = p_mc_header_id;
2875
2876 l_status VARCHAR2(30);
2877
2878 BEGIN
2879
2880 IF (p_mc_header_id IS NOT NULL)
2881 THEN
2882 OPEN get_mc_status;
2883 FETCH get_mc_status INTO l_status;
2884 IF (get_mc_status%NOTFOUND)
2885 THEN
2886 FND_MESSAGE.Set_Name('AHL','AHL_MC_NOT_FOUND');
2887 FND_MSG_PUB.ADD;
2888 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
2889 THEN
2890 fnd_log.message
2891 (
2892 fnd_log.level_exception,
2893 'ahl.plsql.'||G_PKG_NAME||'.Get_MC_Status',
2894 false
2895 );
2896 END IF;
2897 CLOSE get_mc_status;
2898 RAISE FND_API.G_EXC_ERROR;
2899 END IF;
2900 CLOSE get_mc_status;
2901 END IF;
2902
2903 RETURN l_status;
2904
2905 END Get_MC_Status;
2906
2907 PROCEDURE Set_Header_Status
2908 (
2909 p_mc_header_id IN NUMBER
2910 )
2911 IS
2912
2913 -- 1. Define cursor get_mc_header_status to read status of the MC header
2914 CURSOR get_mc_header_status
2915 (
2916 p_mc_header_id in number
2917 )
2918 IS
2919 SELECT config_status_code
2920 FROM ahl_mc_headers_b
2921 WHERE mc_header_id = p_mc_header_id;
2922
2923 -- 2. Define local variables
2924 l_status VARCHAR2(30) := 'DRAFT';
2925
2926 BEGIN
2927
2928 OPEN get_mc_header_status(p_mc_header_id);
2929 FETCH get_mc_header_status INTO l_status;
2930
2931 -- 5. If (record is found and l_status = 'APPROVAL_REJECTED'), then Update config_status_code = 'DRAFT'
2932 IF (get_mc_header_status%FOUND)
2933 THEN
2934 IF (l_status = 'APPROVAL_REJECTED')
2935 THEN
2936 UPDATE ahl_mc_headers_b
2937 SET config_status_code = 'DRAFT'
2938 WHERE mc_header_id = p_mc_header_id;
2939 END IF;
2940 END IF;
2941
2942 CLOSE get_mc_header_status;
2943
2944 END Set_Header_Status;
2945
2946 PROCEDURE Check_MC_Complete
2947 (
2948 p_mc_header_id IN NUMBER
2949 )
2950 IS
2951
2952 -- 1. Define cursor get_mc_nodes_csr to read all nodes associated with a MC
2953 CURSOR get_mc_nodes_csr
2954 (
2955 p_mc_header_id IN NUMBER
2956 )
2957 IS
2958 SELECT relationship_id, position_ref_meaning, position_necessity_code
2959 FROM ahl_mc_relationships_v
2960 WHERE mc_header_id = p_mc_header_id;
2961
2962 -- 2. Define cursor get_node_subconfigs_csr to read details about MCs associated as subconfigs to a MC node
2963 CURSOR get_node_subconfigs_csr
2964 (
2965 p_relationship_id IN NUMBER
2966 )
2967 IS
2968 SELECT mch.name, mch.config_status_code
2969 FROM ahl_mc_config_relations mccr, ahl_mc_headers_b mch
2970 WHERE mccr.relationship_id = p_relationship_id AND
2971 mccr.mc_header_id = mch.mc_header_id;
2972 -- Since expired subconfig associations can be unexpired, so no need to filter on active_end_date
2973 -- AND trunc(nvl(mccr.active_end_date, G_SYSDATE + 1)) > G_TRUNC_DATE;
2974
2975 -- 3. Define get_node_itemgroups_csr to read details about itemgroups associated with a MC node
2976 CURSOR get_node_itemgroups_csr
2977 (
2978 p_relationship_id IN NUMBER
2979 )
2980 IS
2981 SELECT ig.item_group_id, ig.name, ig.type_code, ig.status_code
2982 FROM ahl_mc_relationships mcr, ahl_item_groups_b ig
2983 WHERE mcr.relationship_id = p_relationship_id AND
2984 ig.item_group_id = mcr.item_group_id;
2985
2986 -- 4. Define local variables
2987 l_relationship_id NUMBER;
2988 l_necessity_code VARCHAR2(30);
2989 l_posref_meaning VARCHAR2(80);
2990 l_mc_name VARCHAR2(80);
2991 l_mc_status VARCHAR2(30);
2992 l_item_group_id NUMBER;
2993 l_item_group_name VARCHAR2(80);
2994 l_item_group_type VARCHAR2(30);
2995 l_item_group_status VARCHAR2(30);
2996 l_has_itemgroup BOOLEAN := TRUE;
2997 l_has_subconfig BOOLEAN := FALSE;
2998 l_return_status VARCHAR2(1);
2999 l_msg_count NUMBER;
3000 l_msg_data VARCHAR2(2000);
3001
3002 BEGIN
3003
3004 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
3005 THEN
3006 fnd_log.string
3007 (
3008 fnd_log.level_procedure,
3009 'ahl.plsql.'||G_PKG_NAME||'.Check_MC_Complete.begin',
3010 'At the start of PLSQL procedure '||G_PKG_NAME||'.Check_MC_Complete'
3011 );
3012 END IF;
3013
3014 OPEN get_mc_nodes_csr (p_mc_header_id);
3015 LOOP
3016 FETCH get_mc_nodes_csr INTO l_relationship_id, l_posref_meaning, l_necessity_code;
3017 EXIT WHEN get_mc_nodes_csr%NOTFOUND;
3018
3019 -- Mark node with no itemgroup association
3020 l_has_itemgroup := FALSE;
3021
3022 OPEN get_node_itemgroups_csr (l_relationship_id);
3023 LOOP
3024 FETCH get_node_itemgroups_csr INTO l_item_group_id, l_item_group_name, l_item_group_type, l_item_group_status;
3025 EXIT WHEN get_node_itemgroups_csr%NOTFOUND;
3026
3027 -- Mark node with itemgroup association
3028 l_has_itemgroup := TRUE;
3029
3030 -- Check itemgroup associated is complete
3031 IF (l_item_group_status <> 'COMPLETE')
3032 THEN
3033 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_IG_NOT_COMP');
3034 FND_MESSAGE.Set_Token('IG_NAME', l_item_group_name);
3035 FND_MESSAGE.Set_Token('POS_REF', l_posref_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||'.Check_MC_Complete',
3043 false
3044 );
3045 END IF;
3046 END IF;
3047 END LOOP;
3048 CLOSE get_node_itemgroups_csr;
3049
3050 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
3051 THEN
3052 fnd_log.string
3053 (
3054 fnd_log.level_statement,
3055 'ahl.plsql.'||G_PKG_NAME||'.Check_MC_Complete',
3056 'Item group validation done for Node ['||l_relationship_id||']'
3057 );
3058 END IF;
3059
3060 -- Mark node with no subconfig association
3061 l_has_subconfig := FALSE;
3062
3063 OPEN get_node_subconfigs_csr (l_relationship_id);
3064 LOOP
3065 FETCH get_node_subconfigs_csr INTO l_mc_name, l_mc_status;
3066 EXIT WHEN get_node_subconfigs_csr%NOTFOUND;
3067
3068 -- Mark node with atleast one subconfig association
3069 l_has_subconfig := TRUE;
3070
3071 -- Check subconfig associated is complete
3072 IF (l_mc_status <> 'COMPLETE')
3073 THEN
3074 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_SUBMC_NOT_COMP');
3075 FND_MESSAGE.Set_Token('MC_NAME', l_mc_name);
3076 FND_MESSAGE.Set_Token('POS_REF', l_posref_meaning);
3077 FND_MSG_PUB.ADD;
3078 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
3079 THEN
3080 fnd_log.message
3081 (
3082 fnd_log.level_exception,
3083 'ahl.plsql.'||G_PKG_NAME||'.Check_MC_Complete',
3084 false
3085 );
3086 END IF;
3087 END IF;
3088 END LOOP;
3089 CLOSE get_node_subconfigs_csr;
3090
3091 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
3092 THEN
3093 fnd_log.string
3094 (
3095 fnd_log.level_statement,
3096 'ahl.plsql.'||G_PKG_NAME||'.Check_MC_Complete',
3097 'Subconfig validation done for Node ['||l_relationship_id||']'
3098 );
3099 END IF;
3100
3101 IF (l_has_itemgroup = FALSE AND l_has_subconfig = FALSE)
3102 THEN
3103 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_NODE_NO_ASSOS');
3104 FND_MESSAGE.Set_Token('POS_REF', l_posref_meaning);
3105 FND_MSG_PUB.ADD;
3106 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
3107 THEN
3108 fnd_log.message
3109 (
3110 fnd_log.level_exception,
3111 'ahl.plsql.'||G_PKG_NAME||'.Check_MC_Complete',
3112 false
3113 );
3114 END IF;
3115 END IF;
3116 END LOOP;
3117 CLOSE get_mc_nodes_csr;
3118
3119 -- SATHAPLI::FP OGMA Issue# 105 - Non-Serialized Item Maintenance, 04-Dec-2007
3120 -- Call the API AHL_MC_RULE_STMT_PVT.validate_quantity_rules_for_mc for Quantity Rule Validation
3121 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3122 fnd_log.string
3123 (
3124 fnd_log.level_statement,
3125 'ahl.plsql.'||G_PKG_NAME||'.Check_MC_Complete',
3126 'Calling API AHL_MC_RULE_STMT_PVT.validate_quantity_rules_for_mc for '||
3127 'Quantity Rule Validation for mc_header_id ['||p_mc_header_id||']'
3128 );
3129 END IF;
3130
3131 -- Do not need to check for the status after API call, as we need only FND_MSG stack's validation errors.
3132 AHL_MC_RULE_STMT_PVT.validate_quantity_rules_for_mc
3133 (
3134 p_api_version => 1.0,
3135 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
3136 p_mc_header_id => p_mc_header_id,
3137 x_return_status => l_return_status,
3138 x_msg_count => l_msg_count,
3139 x_msg_data => l_msg_data
3140 );
3141
3142 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3143 fnd_log.string
3144 (
3145 fnd_log.level_statement,
3146 'ahl.plsql.'||G_PKG_NAME||'.Check_MC_Complete',
3147 'Returned from calling API AHL_MC_RULE_STMT_PVT.validate_quantity_rules_for_mc for '||
3148 'Quantity Rule Validation for mc_header_id ['||p_mc_header_id||']'
3149 );
3150 END IF;
3151
3152 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
3153 THEN
3154 fnd_log.string
3155 (
3156 fnd_log.level_procedure,
3157 'ahl.plsql.'||G_PKG_NAME||'.Check_MC_Complete.end',
3158 'At the end of PLSQL procedure '||G_PKG_NAME||'.Check_MC_Complete'
3159 );
3160 END IF;
3161
3162 END Check_MC_Complete;
3163
3164 End AHL_MC_MasterConfig_PVT;