1 PACKAGE BODY AHL_INV_RESERVATIONS_GRP AS
2 /* $Header: AHLGRSVB.pls 120.9 2005/12/08 02:33 anraj noship $ */
3
4 ------------------------------------
5 -- Common constants and variables --
6 ------------------------------------
7 l_log_current_level NUMBER := fnd_log.g_current_runtime_level;
8 l_log_statement NUMBER := fnd_log.level_statement;
9 l_log_procedure NUMBER := fnd_log.level_procedure;
10 l_log_error NUMBER := fnd_log.level_error;
11 l_log_unexpected NUMBER := fnd_log.level_unexpected;
12
13
14 -- The purpose of this API is to get the final availability of the document line for which the reservation is being
15 -- created/ modified.This procedure will be called by the inventory APIs to get the expected availability at the
16 -- document level. The reason being that the actual ordered/receipt quantity on the document may not reflect the
17 -- expected quantity that is pending. Reservation API needs to know the final availability so that the ATR (available
18 -- to reserve) can be calculated.
19 PROCEDURE get_available_supply_demand (
20 p_api_version_number IN NUMBER
21 , p_init_msg_lst IN VARCHAR2
22 , x_return_status OUT NOCOPY VARCHAR2
23 , x_msg_count OUT NOCOPY NUMBER
24 , x_msg_data OUT NOCOPY VARCHAR2
25 , p_organization_id IN NUMBER
26 , p_item_id IN NUMBER
27 , p_revision IN VARCHAR2
28 , p_lot_number IN VARCHAR2
29 , p_subinventory_code IN VARCHAR2
30 , p_locator_id IN NUMBER
31 , p_supply_demand_code IN NUMBER
32 , p_supply_demand_type_id IN NUMBER
33 , p_supply_demand_header_id IN NUMBER
34 , p_supply_demand_line_id IN NUMBER
35 , p_supply_demand_line_detail IN NUMBER
36 , p_lpn_id IN NUMBER
37 , p_project_id IN NUMBER
38 , p_task_id IN NUMBER
39 , x_available_quantity OUT NOCOPY NUMBER
40 , x_source_uom_code OUT NOCOPY VARCHAR2
41 , x_source_primary_uom_code OUT NOCOPY VARCHAR2
42 )
43 IS
44 -- Variables for logging
45 l_log_current_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
46 l_log_procedure NUMBER := FND_LOG.LEVEL_PROCEDURE;
47 -- Declare local variables
48 l_api_name CONSTANT VARCHAR2(30) := 'get_available_supply_demand';
49 l_api_version CONSTANT NUMBER := 1.0;
50 l_init_msg_list VARCHAR2(1) := 'F';
51 l_return_status VARCHAR2(1);
52 l_msg_count NUMBER;
53 l_msg_data VARCHAR2(2000);
54 l_debug_module CONSTANT VARCHAR2(100) := 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name;
55
56 -- CURSOR to get the requested quanity and the compelted quantity
57 CURSOR get_mtl_dtls_cur (c_scheduled_material_id IN NUMBER)
58 IS
59 SELECT requested_quantity,
60 NVL(completed_quantity, 0) completed_quantity
61 FROM ahl_schedule_materials
62 WHERE scheduled_material_id = c_scheduled_material_id;
63
64 -- cursor to get the reserved quantity from the WMS tables
65 CURSOR get_rsvd_qty_csr(c_scheduled_material_id IN NUMBER)
66 IS
67 SELECT SUM(primary_reservation_quantity) reserved_quantity
68 FROM mtl_reservations mrsv,ahl_schedule_materials asmt
69 WHERE mrsv.demand_source_line_detail = c_scheduled_material_id
70 AND mrsv.external_source_code = 'AHL'
71 AND mrsv.demand_source_line_detail = asmt.scheduled_material_id
72 AND mrsv.organization_id = asmt.organization_id
73 AND mrsv.requirement_date = asmt.requested_date
74 AND mrsv.inventory_item_id = asmt.inventory_item_id
75 GROUP BY mrsv.demand_source_line_detail;
76
77 -- local variables to be used
78 l_reserved_quantity NUMBER := null;
79 l_requested_quantity NUMBER := null;
80 l_completed_quantity NUMBER := null;
81
82 BEGIN
83 -- Standard start of API savepoint
84 SAVEPOINT GET_AVAILABLE_SUPP_DMND_GRP;
85
86 -- Initialize return status to success before any code logic/validation
87 x_return_status := FND_API.G_RET_STS_SUCCESS;
88
89 -- Standard call to check for call compatibility
90 IF NOT FND_API.COMPATIBLE_API_CALL (l_api_version, p_api_version_number, l_api_name, G_PKG_NAME)
91 THEN
92 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
93 END IF;
94
95 -- Initialize message list if p_init_msg_list = FND_API.G_TRUE
96 IF FND_API.TO_BOOLEAN(p_init_msg_lst)
97 THEN
98 FND_MSG_PUB.INITIALIZE;
99 END IF;
100
101 -- Log API entry point
102 IF (l_log_procedure >= l_log_current_level)THEN
103 fnd_log.string
104 (
105 fnd_log.level_procedure,
106 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||'.begin',
107 'At the start of PL SQL procedure '
108 );
109 END IF;
110
111 -- Validate the Supply Demand Code
112 -- 1 is 'SUPPLY' and 2 is 'DEMAND' as per WMS TDD
113 IF p_supply_demand_code <> 2 THEN
114 FND_MESSAGE.set_name( 'AHL', 'AHL_RSV_AGAINST_DMND_ONLY' );
115 -- The reservations should be against demand, not supply.
116 FND_MSG_PUB.add;
117 -- log the error
118 IF (l_log_statement >= l_log_current_level)THEN
119 fnd_log.string
120 (
121 fnd_log.level_statement,
122 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
123 'The reservations should be against demand, not supply.'
124 );
125 END IF;
126 RAISE FND_API.G_EXC_ERROR;
127 END IF;
128
129 -- Validate the Supply Demand line Detail
130 IF p_supply_demand_line_detail IS NULL OR p_supply_demand_line_detail = fnd_api.g_miss_num THEN
131 FND_MESSAGE.set_name( 'AHL', 'AHL_RSV_DMND_LINE_DTL_MAND' );
132 FND_MSG_PUB.add;
133 -- Demand Line Detail is required for CMRO reservations
134 -- log the error
135 IF (l_log_statement >= l_log_current_level)THEN
136 fnd_log.string
137 (
138 fnd_log.level_statement,
139 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
140 'Demand Line Detail is required for CMRO reservations'
141 );
142 END IF;
143 RAISE FND_API.G_EXC_ERROR;
144 END IF;
145
146 -- Get the Reserved Quantity
147 OPEN get_rsvd_qty_csr(p_supply_demand_line_detail);
148 FETCH get_rsvd_qty_csr INTO l_reserved_quantity;
149 CLOSE get_rsvd_qty_csr;
150 -- if no items have been reserved, set it to 0
151 IF l_reserved_quantity IS NULL THEN
152 l_reserved_quantity := 0;
153 END IF;
154
155 -- Get the requested and completed quantity
156 OPEN get_mtl_dtls_cur(p_supply_demand_line_detail);
157 FETCH get_mtl_dtls_cur INTO l_requested_quantity,l_completed_quantity;
158 CLOSE get_mtl_dtls_cur;
159
160 -- Get the available quantity to reserve
161 -- Available quantity to reserve will be the requested Quantity minus the quanity alreadu issued
162 -- minus the quantity that is reserved
163 --x_available_quantity := l_requested_quantity - l_completed_quantity - l_reserved_quantity;
164
165 -- AnRaj: Modified code, we are not subtracting reserved quantity from CMRO because this is being done in WMS also
166 x_available_quantity := l_requested_quantity - l_completed_quantity ;
167
168 IF (l_log_statement >= l_log_current_level)THEN
169 fnd_log.string
170 (
171 fnd_log.level_statement,
172 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
173 'x_available_quantity' || x_available_quantity
174 );
175 END IF;
176
177 -- End logging
178 IF (l_log_procedure >= l_log_current_level)THEN
179 fnd_log.string
180 (
181 fnd_log.level_procedure,
182 l_debug_module||'.end',
183 'At the end of PLSQL procedure'
184 );
185 END IF;
186
187 -- Check Error Message stack.
188 x_msg_count := FND_MSG_PUB.count_msg;
189 IF x_msg_count > 0
190 THEN
191 RAISE FND_API.G_EXC_ERROR;
192 END IF;
193
194 -- Standard call to get message count and if count is 1, get message info
195 FND_MSG_PUB.count_and_get
196 (
197 p_count => x_msg_count,
198 p_data => x_msg_data,
199 p_encoded => FND_API.G_FALSE
200 );
201
202 EXCEPTION
203 WHEN FND_API.G_EXC_ERROR THEN
204 x_return_status := FND_API.G_RET_STS_ERROR;
205 FND_MSG_PUB.count_and_get
206 (
207 p_count => x_msg_count,
208 p_data => x_msg_data,
209 p_encoded => FND_API.G_FALSE
210 );
211
212 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
213 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
214 FND_MSG_PUB.count_and_get
215 (
216 p_count => x_msg_count,
217 p_data => x_msg_data,
218 p_encoded => FND_API.G_FALSE
219 );
220
221 WHEN OTHERS THEN
222 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
223 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
224 THEN
225 FND_MSG_PUB.add_exc_msg
226 (
227 p_pkg_name => G_PKG_NAME,
228 p_procedure_name => 'get_available_supply_demand',
229 p_error_text => SUBSTR(SQLERRM,1,240)
230 );
231 END IF;
232 FND_MSG_PUB.count_and_get
233 (
234 p_count => x_msg_count,
235 p_data => x_msg_data,
236 p_encoded => FND_API.G_FALSE
237 );
238 END get_available_supply_demand;
239
240 -- The purpose of this API is to validate whether a supply or a demand line for which the reservation is being
241 -- created/ modified is a valid document line. This procedure will be called by the inventory APIs to validate a
242 -- supply or a demand document, if the supply/demand document line is non-inventory
243 PROCEDURE validate_supply_demand (
244 p_api_version_number IN NUMBER
245 , p_init_msg_lst IN VARCHAR2
246 , x_return_status OUT NOCOPY VARCHAR2
247 , x_msg_count OUT NOCOPY NUMBER
248 , x_msg_data OUT NOCOPY VARCHAR2
249 , p_organization_id IN NUMBER
250 , p_item_id IN NUMBER
251 , p_supply_demand_code IN NUMBER
252 , p_supply_demand_type_id IN NUMBER
253 , p_supply_demand_header_id IN NUMBER
254 , p_supply_demand_line_id IN NUMBER
255 , p_supply_demand_line_detail IN NUMBER
256 , p_demand_ship_date IN DATE
257 , p_expected_receipt_date IN DATE
258 , x_valid_status OUT NOCOPY VARCHAR2
259 )
260 IS
261 -- Declare local variables
262 l_api_name CONSTANT VARCHAR2(30) := 'validate_supply_demand';
263 l_api_version CONSTANT NUMBER := 1.0;
264 l_init_msg_list VARCHAR2(1) := 'F';
265 l_return_status VARCHAR2(1);
266 l_msg_count NUMBER;
267 l_msg_data VARCHAR2(2000);
268 l_debug_module CONSTANT VARCHAR2(100) := 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name;
269
270 -- Validating the details if it is of type INV
271 CURSOR get_mtl_dtls_inv_cur ( c_scheduled_material_id IN NUMBER,
272 c_visit_task_id IN NUMBER,
273 c_rt_oper_material_id IN NUMBER,
274 c_organization_id IN NUMBER,
275 c_item_id IN NUMBER)
276 IS
277 SELECT scheduled_material_id
278 FROM ahl_schedule_materials
279 WHERE scheduled_material_id = c_SCHEDULED_MATERIAL_ID
280 AND organization_id = c_organization_ID
281 AND inventory_item_id = c_item_ID
282 AND visit_task_id = c_visit_task_ID
283 AND rt_oper_material_id = c_rt_oper_material_id
284 AND status = 'ACTIVE'
285 AND requested_quantity <>0
286 AND EXISTS ( SELECT 1
287 FROM ahl_visit_tasks_b vt
288 WHERE vt.status_code = 'PLANNING'
289 AND vt.visit_task_id = c_visit_task_id);
290 -- Validating the details if it is of type WIP
291 CURSOR get_mtl_dtls_wip_cur ( c_scheduled_material_id IN NUMBER,
292 c_wip_entity_id IN NUMBER,
293 c_oper_seq_num IN NUMBER,
294 c_organization_id IN NUMBER,
295 c_item_id IN NUMBER)
296 IS
297 SELECT scheduled_material_id
298 FROM ahl_schedule_materials
299 WHERE scheduled_material_id = c_scheduled_material_id
300 AND organization_id = c_organization_id
301 AND inventory_item_id = c_item_id
302 AND Operation_sequence = c_oper_seq_num
303 AND status = 'ACTIVE'
304 AND requested_quantity <>0
305 AND visit_task_id = ( SELECT aw.visit_task_id
306 FROM ahl_visit_tasks_b vt, ahl_workorders aw
307 WHERE vt.status_code IN ('PLANNING','RELEASED')
308 AND aw.wip_entity_id = c_wip_entity_id
309 AND aw.status_code in ('1','3')
310 AND aw.visit_task_id= vt.visit_task_id );
311 -- local variables
312 l_scheduled_material_id NUMBER := null;
313 BEGIN
314 -- Standard start of API savepoint
315 SAVEPOINT VALIDATE_SUPPLY_DEMAND_GRP;
316
317 -- Initialize return status to success before any code logic/validation
318 x_return_status:= FND_API.G_RET_STS_SUCCESS;
319
320 -- Standard call to check for call compatibility
321 IF NOT FND_API.COMPATIBLE_API_CALL (l_api_version, p_api_version_number, l_api_name, G_PKG_NAME)
322 THEN
323 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
324 END IF;
325
329 FND_MSG_PUB.INITIALIZE;
326 -- Initialize message list if p_init_msg_list = FND_API.G_TRUE
327 IF FND_API.TO_BOOLEAN(p_init_msg_lst)
328 THEN
330 END IF;
331 -- Log API entry point
332 IF (l_log_procedure >= l_log_current_level)THEN
333 fnd_log.string
334 (
335 fnd_log.level_procedure,
336 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||'.begin',
337 'At the start of PL SQL procedure '
338 );
339 END IF;
340
341 -- log all the input parameters
342 IF (l_log_statement >= l_log_current_level)THEN
343 fnd_log.string
344 (
345 fnd_log.level_statement,'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
346 'p_organization_id-->' || p_organization_id
347 );
348 fnd_log.string
349 (
350 fnd_log.level_statement,'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
351 'p_item_id-->' || p_item_id
352 );
353 fnd_log.string
354 (
355 fnd_log.level_statement,'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
356 'p_supply_demand_code-->' || p_supply_demand_code
357 );
358 fnd_log.string
359 (
360 fnd_log.level_statement,'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
361 'p_supply_demand_type_id-->' || p_supply_demand_type_id
362 );
363 fnd_log.string
364 (
365 fnd_log.level_statement,'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
366 'p_supply_demand_header_id-->' || p_supply_demand_header_id
367 );
368 fnd_log.string
369 (
370 fnd_log.level_statement,'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
371 'p_supply_demand_line_id-->' || p_supply_demand_line_id
372 );
373 fnd_log.string
374 (
375 fnd_log.level_statement,'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
376 'p_supply_demand_line_detail-->' || p_supply_demand_line_detail
377 );
378 fnd_log.string
379 (
380 fnd_log.level_statement,'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
381 'p_demand_ship_date-->' || p_demand_ship_date
382 );
383 fnd_log.string
384 (
385 fnd_log.level_statement,'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
386 'p_expected_receipt_date-->' || p_expected_receipt_date
387 );
388 END IF;
389
390 -- Validate the Supply Demand Code
391 -- 1 : 'SUPPLY' 2 : 'DEMAND'.
392 IF p_supply_demand_code <> 2 THEN
393 FND_MESSAGE.set_name( 'AHL', 'AHL_RSV_AGAINST_DMND_ONLY' );
394 FND_MSG_PUB.add;
395 --The reservations should be against demand, not supply.
396 -- log the error
397 IF (l_log_statement >= l_log_current_level)THEN
398 fnd_log.string
399 (
400 fnd_log.level_statement,
401 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
402 'The reservations should be against demand, not supply.'
403 );
404 END IF;
405 RAISE FND_API.G_EXC_ERROR;
406 END IF;
407
408 -- p_supply_demand_header_id null check
409 IF p_supply_demand_header_id IS NULL OR p_supply_demand_header_id = fnd_api.g_miss_num THEN
410 FND_MESSAGE.set_name( 'AHL', 'AHL_RSV_DMND_HDR_MAND' );
411 FND_MSG_PUB.add;
412 -- Demand header is required for CMRO reservations
413 -- log the error
414 IF (l_log_statement >= l_log_current_level)THEN
415 fnd_log.string
416 (
417 fnd_log.level_statement,
418 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
419 'Demand header is required for CMRO reservations'
420 );
421 END IF;
422 RAISE FND_API.G_EXC_ERROR;
423 END IF;
424
425 -- p_supply_demand_line_id null check
426 IF p_supply_demand_line_id IS NULL OR p_supply_demand_line_id = fnd_api.g_miss_num THEN
427 FND_MESSAGE.set_name( 'AHL', 'AHL_RSV_DMND_LINE_MAND' );
428 FND_MSG_PUB.add;
429 -- Demand Line is required for CMRO reservations
430 -- log the error
431 IF (l_log_statement >= l_log_current_level)THEN
432 fnd_log.string
433 (
434 fnd_log.level_statement,
435 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
436 'Demand Line is required for CMRO reservations'
437 );
438 END IF;
439 RAISE FND_API.G_EXC_ERROR;
440 END IF;
441
442 -- p_supply_demand_line_detail null check
443 IF p_supply_demand_line_detail IS NULL OR p_supply_demand_line_detail = fnd_api.g_miss_num THEN
444 FND_MESSAGE.set_name( 'AHL', 'AHL_RSV_DMND_LINE_DTL_MAND' );
445 FND_MSG_PUB.add;
446 -- Demand Line Detail is required for CMRO reservations
447 -- log the error
448 IF (l_log_statement >= l_log_current_level)THEN
449 fnd_log.string
450 (
451 fnd_log.level_statement,
452 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
453 'Demand Line Detail is required for CMRO reservations'
454 );
455 END IF;
456 RAISE FND_API.G_EXC_ERROR;
457 END IF;
458
459 -- p_supply_demand_line_detail null check
460 IF p_organization_id IS NULL OR p_organization_id = fnd_api.g_miss_num THEN
461 FND_MESSAGE.set_name( 'AHL', 'AHL_RSV_ORG_MAND_VLD_SUP_DMND' );
462 FND_MSG_PUB.add;
463 -- Organization is mandatory in validate_supply_demand.
464 -- log the error
465 IF (l_log_statement >= l_log_current_level)THEN
466 fnd_log.string
467 (
468 fnd_log.level_statement,
472 END IF;
469 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
470 'Organization is mandatory in validate_supply_demand.'
471 );
473 RAISE FND_API.G_EXC_ERROR;
474 END IF;
475
476
477 -- p_supply_demand_line_detail null check
478 IF p_item_id IS NULL OR p_item_id = fnd_api.g_miss_num THEN
479 FND_MESSAGE.set_name( 'AHL', 'AHL_RSV_ITM_MAND_VLD_SUP_DMND' );
480 FND_MSG_PUB.add;
481 -- Item ID is mandatory in validate_supply_demand.
482 -- log the error
483 IF (l_log_statement >= l_log_current_level)THEN
484 fnd_log.string
485 (
486 fnd_log.level_statement,
490 END IF;
487 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
488 'Item ID is mandatory in validate_supply_demand.'
489 );
491 RAISE FND_API.G_EXC_ERROR;
492 END IF;
493
494 -- Validate the details by seeing whether they exist
495 IF p_supply_demand_type_id = inv_reservation_global.g_source_type_inv THEN
496 OPEN get_mtl_dtls_inv_cur( p_supply_demand_line_detail,
497 p_supply_demand_header_id,
498 p_supply_demand_line_id,
499 p_organization_id,
500 p_item_id);
501 FETCH get_mtl_dtls_inv_cur INTO l_scheduled_material_id;
502 CLOSE get_mtl_dtls_inv_cur;
503 ELSIF p_supply_demand_type_id = inv_reservation_global.g_source_type_wip THEN
504 OPEN get_mtl_dtls_wip_cur ( p_supply_demand_line_detail,
505 p_supply_demand_header_id,
506 p_supply_demand_line_id,
507 p_organization_id,
508 p_item_id);
509 FETCH get_mtl_dtls_wip_cur INTO l_scheduled_material_id;
510 CLOSE get_mtl_dtls_wip_cur;
511 ELSE
512 FND_MESSAGE.set_name( 'AHL', 'AHL_RSV_SUP_DEM_TYPE_INVLD' );
513 FND_MSG_PUB.add;
514 IF (l_log_statement >= l_log_current_level)THEN
515 fnd_log.string
516 (
517 fnd_log.level_statement,
518 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
519 'Wrong value for Supply Demand Type.'
520 );
521 END IF;
522 RAISE FND_API.G_EXC_ERROR;
523 END IF;
524
525 -- set the return value.
526 IF l_scheduled_material_id is null THEN
527 x_valid_status := 'N';
528 ELSE
529 x_valid_status := 'Y';
530 END IF;
531 IF (l_log_statement >= l_log_current_level)THEN
532 fnd_log.string
533 (
534 fnd_log.level_statement,
535 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
536 'Return Value: x_valid_status' || x_valid_status
537 );
538 END IF;
539
540
541 -- End logging
542 IF (l_log_procedure >= l_log_current_level)THEN
543 fnd_log.string
544 (
545 fnd_log.level_procedure,
546 l_debug_module||'.end',
547 'At the end of PLSQL procedure'
548 );
549 END IF;
550
554 THEN
551 -- Check Error Message stack.
552 x_msg_count := FND_MSG_PUB.count_msg;
553 IF x_msg_count > 0
555 RAISE FND_API.G_EXC_ERROR;
556 END IF;
557
558 -- Standard call to get message count and if count is 1, get message info
559 FND_MSG_PUB.count_and_get
560 (
561 p_count => x_msg_count,
562 p_data => x_msg_data,
563 p_encoded => FND_API.G_FALSE
564 );
565
566 EXCEPTION
567 WHEN FND_API.G_EXC_ERROR THEN
568 x_return_status := FND_API.G_RET_STS_ERROR;
569 FND_MSG_PUB.count_and_get
570 (
571 p_count => x_msg_count,
572 p_data => x_msg_data,
573 p_encoded => FND_API.G_FALSE
574 );
575
576 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
577 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
578 FND_MSG_PUB.count_and_get
579 (
580 p_count => x_msg_count,
581 p_data => x_msg_data,
582 p_encoded => FND_API.G_FALSE
583 );
584
585 WHEN OTHERS THEN
586 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
587 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
588 THEN
589 FND_MSG_PUB.add_exc_msg
590 (
591 p_pkg_name => G_PKG_NAME,
592 p_procedure_name => 'get_available_supply_demand',
593 p_error_text => SUBSTR(SQLERRM,1,240)
594 );
595 END IF;
596 FND_MSG_PUB.count_and_get
597 (
598 p_count => x_msg_count,
599 p_data => x_msg_data,
600 p_encoded => FND_API.G_FALSE
601 );
602 END validate_supply_demand;
603
604 END AHL_INV_RESERVATIONS_GRP;