[Home] [Help]
PACKAGE BODY: APPS.AHL_UC_VALIDATION_PUB
Source
1 PACKAGE BODY AHL_UC_VALIDATION_PUB AS
2 /* $Header: AHLPUCVB.pls 120.2 2007/12/21 12:42:10 sathapli ship $ */
3
4
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AHL_UC_VALIDATION_PUB';
6
7 -------------------------------------------------------------------------------
8 -- Start of Comments --
9 -- Procedure name : Validate_Completeness
10 -- Type : Private
11 -- Function : Validates the unit's completeness and checks for ALL validations.
12 -- Pre-reqs :
13 -- Parameters :
14 --
15 -- Validate_Completeness Parameters:
16 -- p_unit_header_id IN NUMBER Required.
17 -- The header identifier of the Unit Configuration
18 -- x_error_tbl OUT NOCOPY Error_Tbl_Type Required
19 -- A table listing all the Errors.
20 --
21 -- End of Comments.
22 -------------------------------------------------------------------------------
23 PROCEDURE Validate_Completeness (
24 p_api_version IN NUMBER,
25 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
26 p_commit IN VARCHAR2 := FND_API.G_FALSE,
27 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
28 x_return_status OUT NOCOPY VARCHAR2,
29 x_msg_count OUT NOCOPY NUMBER,
30 x_msg_data OUT NOCOPY VARCHAR2,
31 p_unit_header_id IN NUMBER,
32 x_error_tbl OUT NOCOPY Error_Tbl_Type)
33 IS
34 --
35 CURSOR validate_uc_header_id_csr (c_unit_header_id IN NUMBER) IS
36 SELECT unit_config_status_code
37 FROM ahl_unit_config_headers
38 WHERE unit_config_header_id = c_unit_header_id
39 AND trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
40 AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
41 FOR UPDATE OF unit_config_status_code NOWAIT;
42 --
43 l_api_version CONSTANT NUMBER := 1.0;
44 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Completeness';
45
46 l_uc_status_code ahl_unit_config_headers.unit_config_status_code%TYPE;
47 l_error_table Error_Tbl_Type;
48 l_evaluation_status VARCHAR2(1);
49 --
50 BEGIN
51
52 -- Standard start of API savepoint
53 SAVEPOINT Validate_Completeness;
54
55 -- Standard call to check for call compatibility
56 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name,
57 G_PKG_NAME) THEN
58 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
59 END IF;
60
61 -- Initialize message list if p_init_msg_list is set to TRUE
62 IF FND_API.To_Boolean(p_init_msg_list) THEN
63 FND_MSG_PUB.Initialize;
64 END IF;
65
66 -- Initialize API return status to success
67 x_return_status := FND_API.G_RET_STS_SUCCESS;
68
69 IF (p_unit_header_id IS NULL OR p_unit_header_id = FND_API.G_MISS_NUM) THEN
70 FND_MESSAGE.set_name('AHL', 'AHL_COM_INVALID_PROCEDURE_CALL');
71 FND_MESSAGE.set_token('PROCEDURE', G_PKG_NAME);
72 FND_MSG_PUB.add;
73 RAISE FND_API.G_EXC_ERROR;
74 END IF;
75
76 -- ACL :: Changes for R12
77 IF (ahl_util_uc_pkg.IS_UNIT_QUARANTINED(p_unit_header_id => p_unit_header_id , p_instance_id => null) = FND_API.G_TRUE) THEN
78 FND_MESSAGE.set_name( 'AHL','AHL_UC_INVALID_Q_ACTION' );
79 FND_MSG_PUB.add;
80 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
81 END IF;
82
83 --List Extra nodes
84 AHL_UC_POS_NECES_PVT.list_extra_nodes(
85 p_api_version => 1.0,
86 p_init_msg_list => p_init_msg_list,
87 p_validation_level => p_validation_level,
88 p_uc_header_id => p_unit_header_id,
89 p_csi_instance_id => null,
90 x_evaluation_status => l_evaluation_status,
91 p_x_error_table => l_error_table,
92 x_return_status => x_return_status,
93 x_msg_count => x_msg_count,
94 x_msg_data => x_msg_data
95 );
96
97 --List Missing Positions
98 AHL_UC_POS_NECES_PVT.list_missing_positions(
99 p_api_version => 1.0,
100 p_init_msg_list => p_init_msg_list,
101 p_validation_level => p_validation_level,
102 p_uc_header_id => p_unit_header_id,
103 p_csi_instance_id => null,
104 x_evaluation_status => l_evaluation_status,
105 p_x_error_table => l_error_table,
106 x_return_status => x_return_status,
107 x_msg_count => x_msg_count,
108 x_msg_data => x_msg_data
109 );
110
111
112 --Validate Rules
113 AHL_MC_RULE_ENGINE_PVT.Validate_Rules_For_Unit (
114 p_api_version => 1.0,
115 p_init_msg_list => p_init_msg_list,
116 p_validation_level => p_validation_level,
117 x_return_status => x_return_status,
118 x_msg_count => x_msg_count,
119 x_msg_data => x_msg_data,
120 p_unit_header_id => p_unit_header_id,
121 p_rule_type => 'MANDATORY',
122 p_check_subconfig_flag => FND_API.G_TRUE,
123 p_x_error_tbl => l_error_table,
124 x_evaluation_status => l_evaluation_status
125 );
126
127 -- SATHAPLI::FP OGMA Issue# 105 - Non-Serialized Item Maintenance, 05-Dec-2007
128 -- Perform Quantity Validations
129 AHL_UC_POS_NECES_PVT.Validate_Position_Quantities (
130 p_api_version => 1.0,
131 p_init_msg_list => p_init_msg_list,
132 p_validation_level => p_validation_level,
133 p_uc_header_id => p_unit_header_id,
134 p_csi_instance_id => null,
135 x_evaluation_status => l_evaluation_status,
136 p_x_error_table => l_error_table,
137 x_return_status => x_return_status,
138 x_msg_count => x_msg_count,
139 x_msg_data => x_msg_data
140 );
141
142 -- Check Error Message stack.
143 x_msg_count := FND_MSG_PUB.count_msg;
144 IF x_msg_count > 0 THEN
145 RAISE FND_API.G_EXC_ERROR;
146 END IF;
147
148 --validate the uc header id
149 OPEN validate_uc_header_id_csr(p_unit_header_id);
150 FETCH validate_uc_header_id_csr INTO l_uc_status_code;
151 IF (validate_uc_header_id_csr%NOTFOUND) THEN
152 FND_MESSAGE.Set_Name('AHL','AHL_UC_HEADER_ID_INVALID');
153 FND_MESSAGE.Set_Token('UC_HEADER_ID', p_unit_header_id);
154 FND_MSG_PUB.ADD;
155 CLOSE validate_uc_header_id_csr;
156 RAISE FND_API.G_EXC_ERROR;
157 END IF;
158 CLOSE validate_uc_header_id_csr;
159
160 IF (l_uc_status_code = 'INCOMPLETE' AND
161 l_error_table.COUNT = 0) THEN
162 UPDATE AHL_UNIT_CONFIG_HEADERS
163 SET UNIT_CONFIG_STATUS_CODE = 'COMPLETE',
164 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER+1,
165 LAST_UPDATE_DATE = sysdate,
166 LAST_UPDATED_BY = fnd_global.USER_ID,
167 LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID
168 WHERE unit_config_header_id = p_unit_header_id;
169 ELSIF (l_error_table.COUNT >0 AND
170 l_uc_status_code = 'COMPLETE') THEN
171 UPDATE AHL_UNIT_CONFIG_HEADERS
172 SET UNIT_CONFIG_STATUS_CODE = 'INCOMPLETE',
173 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER+1,
174 LAST_UPDATE_DATE = sysdate,
175 LAST_UPDATED_BY = fnd_global.USER_ID,
176 LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID
177 WHERE unit_config_header_id = p_unit_header_id;
178 END IF;
179
180 --Setting output parameters
181 x_error_tbl := l_error_table;
182
183 -- Standard check of p_commit
184 IF FND_API.TO_BOOLEAN(p_commit) THEN
185 COMMIT WORK;
186 END IF;
187
188 -- Standard call to get message count and if count is 1, get message info
189 FND_MSG_PUB.Count_And_Get
190 ( p_count => x_msg_count,
191 p_data => x_msg_data,
192 p_encoded => fnd_api.g_false
193 );
194
195 EXCEPTION
196 WHEN FND_API.G_EXC_ERROR THEN
197 x_return_status := FND_API.G_RET_STS_ERROR;
198 Rollback to Validate_Completeness;
199 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
200 p_data => x_msg_data,
201 p_encoded => fnd_api.g_false);
202 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
203 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
204 Rollback to Validate_Completeness;
205 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
206 p_data => x_msg_data,
207 p_encoded => fnd_api.g_false);
208 WHEN OTHERS THEN
209 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
210 Rollback to Validate_Completeness;
211 FND_MSG_PUB.add_exc_msg( p_pkg_name => G_PKG_NAME,
212 p_procedure_name => l_api_name,
213 p_error_text => SQLERRM);
214
215 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
216 p_data => x_msg_data,
217 p_encoded => fnd_api.g_false);
218
219 END Validate_Completeness;
220
221
222 --------------------------------
223 -- Start of Comments --
224 -- Procedure name : Validate_Complete_For_Pos
225 -- Type : Private
226 -- Function : Validates the unit's completeness and checks for ALL validations.
227 -- Pre-reqs :
228 -- Parameters :
229 --
230 -- Validate_Complete_For_Pos Parameters:
231 -- p_unit_header_id IN NUMBER Required.
232 -- p_csi_instance_id IN NUMBER Required.
233 -- x_error_tbl OUT NOCOPY AHL_MC_VALIDATION_PUB.error_tbl_Type Required
234 --
235 -- End of Comments.
236
237 PROCEDURE Validate_Complete_For_Pos (
238 p_api_version IN NUMBER,
239 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
240 p_commit IN VARCHAR2 := FND_API.G_FALSE,
241 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
242 x_return_status OUT NOCOPY VARCHAR2,
243 x_msg_count OUT NOCOPY NUMBER,
244 x_msg_data OUT NOCOPY VARCHAR2,
245 p_csi_instance_id IN NUMBER,
246 x_error_tbl OUT NOCOPY Error_Tbl_Type)
247 IS
248 --
249 l_api_version CONSTANT NUMBER := 1.0;
250 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Complete_For_Pos';
251 l_error_table Error_Tbl_Type;
252 l_evaluation_status VARCHAR2(1);
253 --
254 BEGIN
255
256 -- Standard start of API savepoint
257 SAVEPOINT Validate_Complete_For_Pos;
258
259 -- Standard call to check for call compatibility
260 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name,
261 G_PKG_NAME) THEN
262 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
263 END IF;
264
265 -- Initialize message list if p_init_msg_list is set to TRUE
266 IF FND_API.To_Boolean(p_init_msg_list) THEN
267 FND_MSG_PUB.Initialize;
268 END IF;
269
270 -- Initialize API return status to success
271 x_return_status := FND_API.G_RET_STS_SUCCESS;
272
273 --List Extra nodes
274 AHL_UC_POS_NECES_PVT.list_extra_nodes(
275 p_api_version => 1.0,
276 p_init_msg_list => p_init_msg_list,
277 p_validation_level => p_validation_level,
278 p_uc_header_id => null,
279 p_csi_instance_id => p_csi_instance_id,
280 x_evaluation_status => l_evaluation_status,
281 p_x_error_table => l_error_table,
282 x_return_status => x_return_status,
283 x_msg_count => x_msg_count,
284 x_msg_data => x_msg_data
285 );
286
287 --List Missing Positions
288 AHL_UC_POS_NECES_PVT.list_missing_positions(
289 p_api_version => 1.0,
290 p_init_msg_list => p_init_msg_list,
291 p_validation_level => p_validation_level,
292 p_uc_header_id => null,
293 p_csi_instance_id => p_csi_instance_id,
294 x_evaluation_status => l_evaluation_status,
295 p_x_error_table => l_error_table,
296 x_return_status => x_return_status,
297 x_msg_count => x_msg_count,
298 x_msg_data => x_msg_data
299 );
300
301
302 --Validate Rules
303 AHL_MC_RULE_ENGINE_PVT.Validate_Rules_For_Position (
304 p_api_version => 1.0,
305 p_init_msg_list => p_init_msg_list,
306 p_validation_level => p_validation_level,
307 x_return_status => x_return_status,
308 x_msg_count => x_msg_count,
309 x_msg_data => x_msg_data,
310 p_item_instance_id => p_csi_instance_id,
311 p_rule_type => 'MANDATORY',
312 p_x_error_tbl => l_error_table,
313 x_evaluation_status => l_evaluation_status
314 );
315
316 -- SATHAPLI::FP OGMA Issue# 105 - Non-Serialized Item Maintenance, 05-Dec-2007
317 -- Perform Quantity Validations
318 AHL_UC_POS_NECES_PVT.Validate_Position_Quantities (
319 p_api_version => 1.0,
320 p_init_msg_list => p_init_msg_list,
321 p_validation_level => p_validation_level,
322 p_uc_header_id => null,
323 p_csi_instance_id => p_csi_instance_id,
324 x_evaluation_status => l_evaluation_status,
325 p_x_error_table => l_error_table,
326 x_return_status => x_return_status,
327 x_msg_count => x_msg_count,
328 x_msg_data => x_msg_data
329 );
330
331 -- Check Error Message stack.
332 x_msg_count := FND_MSG_PUB.count_msg;
333 IF x_msg_count > 0 THEN
334 RAISE FND_API.G_EXC_ERROR;
335 END IF;
336
337 --Setting output parameters
338 x_error_tbl := l_error_table;
339
340 -- Standard check of p_commit
341 IF FND_API.TO_BOOLEAN(p_commit) THEN
342 COMMIT WORK;
343 END IF;
344
345 -- Standard call to get message count and if count is 1, get message info
346 FND_MSG_PUB.Count_And_Get
347 ( p_count => x_msg_count,
348 p_data => x_msg_data,
349 p_encoded => fnd_api.g_false
350 );
351
352 EXCEPTION
353 WHEN FND_API.G_EXC_ERROR THEN
354 x_return_status := FND_API.G_RET_STS_ERROR;
355 Rollback to Validate_Complete_For_Pos;
356 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
357 p_data => x_msg_data,
358 p_encoded => fnd_api.g_false);
359 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
360 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
361 Rollback to Validate_Complete_For_Pos;
362 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
363 p_data => x_msg_data,
364 p_encoded => fnd_api.g_false);
365 WHEN OTHERS THEN
366 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
367 Rollback to Validate_Complete_For_Pos;
368 FND_MSG_PUB.add_exc_msg( p_pkg_name => G_PKG_NAME,
369 p_procedure_name => l_api_name,
370 p_error_text => SQLERRM);
371
372 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
373 p_data => x_msg_data,
374 p_encoded => fnd_api.g_false);
375 END Validate_Complete_For_Pos;
376
377
378 -------------------------------------------------------------------------------
379 -- Start of Comments --
380 -- Procedure name : Check_Completeness
381 -- Type : Private
382 -- Function : Check the unit's completeness
383 -- Complete/Incomplete status if current status is complete or incomplete..
384 -- Pre-reqs :
385 -- Parameters :
386 --
387 -- Check_Completeness Parameters:
388 -- p_unit_header_id IN NUMBER Required.
389 -- The header identifier of the Unit Configuration
390 -- x_evaluation_status OUT NOCOPY VARCHAR2
391 -- The evaluation status of the Unit Configutation.Returns a FND_API.G_TRUE or FND_API.G_FALSE
392 -- End of Comments.
393 -------------------------------------------------------------------------------
394 PROCEDURE Check_Completeness (
395 p_api_version IN NUMBER,
396 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
397 p_commit IN VARCHAR2 := FND_API.G_FALSE,
398 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
399 x_return_status OUT NOCOPY VARCHAR2,
400 x_msg_count OUT NOCOPY NUMBER,
401 x_msg_data OUT NOCOPY VARCHAR2,
402 p_unit_header_id IN NUMBER,
403 x_evaluation_status OUT NOCOPY VARCHAR2)
404 IS
405 --
406 l_api_version CONSTANT NUMBER := 1.0;
407 l_api_name CONSTANT VARCHAR2(30) := 'Check_Completeness';
408 --
409 BEGIN
410
411 -- Standard start of API savepoint
412 SAVEPOINT Check_Completeness;
413
414 -- Standard call to check for call compatibility
415 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name,
416 G_PKG_NAME) THEN
417 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
418 END IF;
419
420 -- Initialize message list if p_init_msg_list is set to TRUE
421 IF FND_API.To_Boolean(p_init_msg_list) THEN
422 FND_MSG_PUB.Initialize;
423 END IF;
424
425 -- Initialize API return status to success
426 x_return_status := FND_API.G_RET_STS_SUCCESS;
427 x_evaluation_status := 'U';
428
429 IF (p_unit_header_id IS NULL OR p_unit_header_id = FND_API.G_MISS_NUM) THEN
430 FND_MESSAGE.set_name('AHL', 'AHL_COM_INVALID_PROCEDURE_CALL');
431 FND_MESSAGE.set_token('PROCEDURE', G_PKG_NAME);
432 FND_MSG_PUB.add;
433 RAISE FND_API.G_EXC_ERROR;
434 END IF;
435
436 --Check for Extra nodes
437 AHL_UC_POS_NECES_PVT.check_extra_nodes(
438 p_api_version => 1.0,
439 p_init_msg_list => p_init_msg_list,
440 p_validation_level => p_validation_level,
441 p_uc_header_id => p_unit_header_id,
442 x_evaluation_status => x_evaluation_status,
443 x_return_status => x_return_status,
444 x_msg_count => x_msg_count,
445 x_msg_data => x_msg_data
446 );
447 -- Check Error Message stack.
448 x_msg_count := FND_MSG_PUB.count_msg;
449 IF x_msg_count > 0 THEN
450 RAISE FND_API.G_EXC_ERROR;
451 END IF;
452
453
454 IF ( x_evaluation_status = FND_API.G_TRUE ) THEN
455 --Check for Missing Positions
456 AHL_UC_POS_NECES_PVT.check_missing_positions(
457 p_api_version => 1.0,
458 p_init_msg_list => p_init_msg_list,
459 p_validation_level => p_validation_level,
460 p_uc_header_id => p_unit_header_id,
461 x_evaluation_status => x_evaluation_status,
462 x_return_status => x_return_status,
463 x_msg_count => x_msg_count,
464 x_msg_data => x_msg_data
465 );
466 -- Check Error Message stack.
467 x_msg_count := FND_MSG_PUB.count_msg;
468 IF x_msg_count > 0 THEN
469 RAISE FND_API.G_EXC_ERROR;
470 END IF;
471
472 END IF;
473
474 IF ( x_evaluation_status = FND_API.G_TRUE ) THEN
475 --Check for Rules
476 AHL_MC_RULE_ENGINE_PVT.Check_Rules_For_Unit (
477 p_api_version => 1.0,
478 p_init_msg_list => p_init_msg_list,
479 p_validation_level => p_validation_level,
480 p_unit_header_id => p_unit_header_id,
481 p_rule_type => 'MANDATORY',
482 p_check_subconfig_flag => FND_API.G_TRUE,
483 x_evaluation_status => x_evaluation_status,
484 x_return_status => x_return_status,
485 x_msg_count => x_msg_count,
486 x_msg_data => x_msg_data
487 );
488 -- Check Error Message stack.
489 x_msg_count := FND_MSG_PUB.count_msg;
490 IF x_msg_count > 0 THEN
491 RAISE FND_API.G_EXC_ERROR;
492 END IF;
493
494 -- SATHAPLI::FP OGMA Issue# 105 - Non-Serialized Item Maintenance, 05-Dec-2007
495 IF (x_evaluation_status <> 'F') THEN
496 -- Perform Quantity Checks
497 AHL_UC_POS_NECES_PVT.Check_Position_Quantities (
498 p_api_version => 1.0,
499 p_init_msg_list => p_init_msg_list,
500 p_validation_level => p_validation_level,
501 p_uc_header_id => p_unit_header_id,
502 x_evaluation_status => x_evaluation_status,
503 x_return_status => x_return_status,
504 x_msg_count => x_msg_count,
505 x_msg_data => x_msg_data
506 );
507
508 -- Check Error Message stack.
509 x_msg_count := FND_MSG_PUB.count_msg;
510 IF x_msg_count > 0 THEN
511 RAISE FND_API.G_EXC_ERROR;
512 END IF;
513 END IF;
514
515 END IF;
516
517 -- Standard check of p_commit
518 IF FND_API.TO_BOOLEAN(p_commit) THEN
519 COMMIT WORK;
520 END IF;
521
522 -- Standard call to get message count and if count is 1, get message info
523 FND_MSG_PUB.Count_And_Get
524 ( p_count => x_msg_count,
525 p_data => x_msg_data,
526 p_encoded => fnd_api.g_false
527 );
528
529 EXCEPTION
530 WHEN FND_API.G_EXC_ERROR THEN
531 x_return_status := FND_API.G_RET_STS_ERROR;
532 Rollback to Check_Completeness;
533 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
534 p_data => x_msg_data,
535 p_encoded => fnd_api.g_false);
536
537 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
538 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
539 Rollback to Check_Completeness;
540 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
541 p_data => x_msg_data,
542 p_encoded => fnd_api.g_false);
543
544 WHEN OTHERS THEN
545 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
546 Rollback to Check_Completeness;
547 FND_MSG_PUB.add_exc_msg( p_pkg_name => G_PKG_NAME,
548 p_procedure_name => l_api_name,
549 p_error_text => SQLERRM);
550
551 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
552 p_data => x_msg_data,
553 p_encoded => fnd_api.g_false);
554
555 END Check_Completeness;
556
557
558 END AHL_UC_VALIDATION_PUB;