1 PACKAGE BODY AHL_INV_RESERVATIONS_GRP AS
2 /* $Header: AHLGRSVB.pls 120.9.12020000.2 2012/12/06 22:28:15 sareepar ship $ */
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 --sukhwsin::Marshling enhancements - starts
78 --cursor to get workorder quantity. For workorder, quantity will always be 1.
79 --It also validates workorder's RTS flag, WO state, and reservation for this WO as valid supply in mtl_reservation table.
80 CURSOR get_workorder_quantity(c_wip_entity_id IN NUMBER, c_source_type IN NUMBER) IS
81 SELECT 1
82 FROM
83 ahl_workorders wo, ahl_visit_tasks_b vts
84 WHERE
85 wo.wip_entity_id = c_wip_entity_id
86 AND wo.status_code NOT IN (7, 22, 17, 12, 18, 21, 4, 5) -- cancelled, deleted, draft, closed,Deffered,Pending Defer/Cancel Approval, Complete, Complete No-charge
87 AND vts.visit_task_id = wo.visit_task_id
88 AND vts.return_to_supply_flag = 'Y'
89 /* commented out by debadey for WO-WO reservation updation validation
90 AND NOT EXISTS (SELECT 'X' FROM mtl_reservations
91 WHERE external_source_code = 'AHL'
92 AND supply_source_type_id = c_source_type
93 AND supply_source_header_id = c_wip_entity_id)*/;
94
95 --local variable declaration for Marshling enhancements.
96 l_wo_quantity NUMBER := null;
97 --sukhwsin::Marshling enhancements - ends
98 -- local variables to be used
99 l_reserved_quantity NUMBER := null;
100 l_requested_quantity NUMBER := null;
101 l_completed_quantity NUMBER := null;
102
103 BEGIN
104 -- Standard start of API savepoint
105 SAVEPOINT GET_AVAILABLE_SUPP_DMND_GRP;
106
107 -- Initialize return status to success before any code logic/validation
108 x_return_status := FND_API.G_RET_STS_SUCCESS;
109
110 -- Standard call to check for call compatibility
111 IF NOT FND_API.COMPATIBLE_API_CALL (l_api_version, p_api_version_number, l_api_name, G_PKG_NAME)
112 THEN
113 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
114 END IF;
115
116 -- Initialize message list if p_init_msg_list = FND_API.G_TRUE
117 IF FND_API.TO_BOOLEAN(p_init_msg_lst)
118 THEN
119 FND_MSG_PUB.INITIALIZE;
120 END IF;
121 l_msg_count := FND_MSG_PUB.count_msg;
122 -- Log API entry point
123 IF (l_log_procedure >= l_log_current_level)THEN
124 fnd_log.string
125 (
126 fnd_log.level_procedure,
127 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||'.begin',
128 'At the start of PL SQL procedure '
129 );
130 END IF;
131
132 -- Validate the Supply Demand Code
133 -- 1 is 'SUPPLY' and 2 is 'DEMAND' as per WMS TDD
134 --sukhwsin::Marshling enhancement changes- starts
135 --Changed validation logic for supply_demand_code to include supply i.e. 2 also
136 --IF p_supply_demand_code <> 2 THEN
137 IF (p_supply_demand_code NOT IN (1, 2)) THEN
138 --FND_MESSAGE.set_name( 'AHL', 'AHL_RSV_AGAINST_DMND_ONLY' );
139 FND_MESSAGE.set_name( 'AHL', 'AHL_RSV_INVLD_SUP_DMD' );
140 -- Invalid value for supply demand code.
141 FND_MSG_PUB.add;
142 -- log the error
143 IF (l_log_statement >= l_log_current_level)THEN
144 fnd_log.string
145 (
146 fnd_log.level_statement,
147 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
148 --'The reservations should be against demand, not supply.'
149 'Invalid value for supply demand code.'
150 );
151 END IF;
152 RAISE FND_API.G_EXC_ERROR;
153 END IF;
154
155 IF (p_supply_demand_code = 2) THEN
156 --Check available quantity for demand
157 -- Validate the Supply Demand line Detail
158 IF p_supply_demand_line_detail IS NULL OR p_supply_demand_line_detail = fnd_api.g_miss_num THEN
159 FND_MESSAGE.set_name( 'AHL', 'AHL_RSV_DMND_LINE_DTL_MAND' );
160 FND_MSG_PUB.add;
161 -- Demand Line Detail is required for CMRO reservations
162 -- log the error
163 IF (l_log_statement >= l_log_current_level)THEN
164 fnd_log.string
165 (
166 fnd_log.level_statement,
167 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
168 'Demand Line Detail is required for CMRO reservations'
169 );
170 END IF;
171 RAISE FND_API.G_EXC_ERROR;
172 END IF;
173
174 -- Get the Reserved Quantity
175 OPEN get_rsvd_qty_csr(p_supply_demand_line_detail);
176 FETCH get_rsvd_qty_csr INTO l_reserved_quantity;
177 CLOSE get_rsvd_qty_csr;
178 -- if no items have been reserved, set it to 0
179 IF l_reserved_quantity IS NULL THEN
180 l_reserved_quantity := 0;
181 END IF;
182
183 -- Get the requested and completed quantity
184 OPEN get_mtl_dtls_cur(p_supply_demand_line_detail);
185 FETCH get_mtl_dtls_cur INTO l_requested_quantity,l_completed_quantity;
186 CLOSE get_mtl_dtls_cur;
187
188 -- Get the available quantity to reserve
189 -- Available quantity to reserve will be the requested Quantity minus the quanity alreadu issued
190 -- minus the quantity that is reserved
191 --x_available_quantity := l_requested_quantity - l_completed_quantity - l_reserved_quantity;
192
193 -- AnRaj: Modified code, we are not subtracting reserved quantity from CMRO because this is being done in WMS also
194 x_available_quantity := l_requested_quantity - l_completed_quantity ;
195
196 IF (l_log_statement >= l_log_current_level)THEN
197 fnd_log.string
198 (
199 fnd_log.level_statement,
200 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
201 'x_available_quantity' || x_available_quantity
202 );
203 END IF;
204
205 ELSIF (p_supply_demand_code = 1) THEN
206 --Check available quantity for supply
207 -- p_supply_demand_header_id null check
208 IF p_supply_demand_header_id IS NULL OR p_supply_demand_header_id = fnd_api.g_miss_num THEN
209 FND_MESSAGE.set_name( 'AHL', 'AHL_RSV_DMND_HDR_MAND' );
210 FND_MSG_PUB.add;
211 -- Demand header is required for CMRO reservations
212 -- log the error
213 IF (l_log_statement >= l_log_current_level)THEN
214 fnd_log.string
215 (
216 fnd_log.level_statement,
217 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
218 'Demand header is required for CMRO reservations'
219 );
220 END IF;
221 -- End logging
222 RAISE FND_API.G_EXC_ERROR;
223 END IF;
224
225 IF (p_supply_demand_type_id = inv_reservation_global.g_source_type_wip) THEN
226 --Check job existance only if supply_demand_type_id is of WIP as we are checking for WO supply only.
227 OPEN get_workorder_quantity(p_supply_demand_header_id,
228 inv_reservation_global.g_source_type_wip);
229 FETCH get_workorder_quantity INTO l_wo_quantity;
230 CLOSE get_workorder_quantity;
231 ELSE
232 --If Supply demand type is not equal to WIP then raise error, as this supply check is only applicable on WIP supply.
233 FND_MESSAGE.set_name( 'AHL', 'AHL_RSV_SUP_DEM_TYPE_INVLD' );
234 FND_MSG_PUB.add;
235 IF (l_log_statement >= l_log_current_level)THEN
236 fnd_log.string
237 (
238 fnd_log.level_statement,
239 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
240 'Wrong value for Supply Demand Type.'
241 );
242 END IF;
243 RAISE FND_API.G_EXC_ERROR;
244 END IF;
245
246 --Populate return variable
247 IF (l_wo_quantity IS NULL) THEN
248 x_available_quantity :=0;
249 ELSE
250 x_available_quantity :=1;
251 END IF;
252
253 --Start Logging
254 IF (l_log_statement >= l_log_current_level)THEN
255 fnd_log.string
256 (
257 fnd_log.level_statement,
258 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
262 --End Logging
259 'x_available_quantity' || x_available_quantity
260 );
261 END IF;
263
264 END IF; -- p_supply_demand_code check
265
266 IF (l_log_procedure >= l_log_current_level)THEN
267 fnd_log.string
268 (
269 fnd_log.level_procedure,
270 l_debug_module||'.end',
271 'At the end of PLSQL procedure'
272 );
273 END IF;
274
275 -- Check Error Message stack.
276 x_msg_count := FND_MSG_PUB.count_msg;
277 IF x_msg_count > l_msg_count
278 THEN
279 RAISE FND_API.G_EXC_ERROR;
280 END IF;
281
282 -- Standard call to get message count and if count is 1, get message info
283 FND_MSG_PUB.count_and_get
284 (
285 p_count => x_msg_count,
286 p_data => x_msg_data,
287 p_encoded => FND_API.G_FALSE
288 );
289
290 EXCEPTION
291 WHEN FND_API.G_EXC_ERROR THEN
292 x_return_status := FND_API.G_RET_STS_ERROR;
293 FND_MSG_PUB.count_and_get
294 (
295 p_count => x_msg_count,
296 p_data => x_msg_data,
297 p_encoded => FND_API.G_FALSE
298 );
299
300 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
301 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
302 FND_MSG_PUB.count_and_get
303 (
304 p_count => x_msg_count,
305 p_data => x_msg_data,
306 p_encoded => FND_API.G_FALSE
307 );
308
309 WHEN OTHERS THEN
310 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
311 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
312 THEN
313 FND_MSG_PUB.add_exc_msg
314 (
315 p_pkg_name => G_PKG_NAME,
316 p_procedure_name => 'get_available_supply_demand',
317 p_error_text => SUBSTR(SQLERRM,1,240)
318 );
319 END IF;
320 FND_MSG_PUB.count_and_get
321 (
322 p_count => x_msg_count,
323 p_data => x_msg_data,
324 p_encoded => FND_API.G_FALSE
325 );
326 END get_available_supply_demand;
327
328 -- The purpose of this API is to validate whether a supply or a demand line for which the reservation is being
329 -- created/ modified is a valid document line. This procedure will be called by the inventory APIs to validate a
330 -- supply or a demand document, if the supply/demand document line is non-inventory
331 PROCEDURE validate_supply_demand (
332 p_api_version_number IN NUMBER
333 , p_init_msg_lst IN VARCHAR2
334 , x_return_status OUT NOCOPY VARCHAR2
335 , x_msg_count OUT NOCOPY NUMBER
336 , x_msg_data OUT NOCOPY VARCHAR2
337 , p_organization_id IN NUMBER
338 , p_item_id IN NUMBER
339 , p_supply_demand_code IN NUMBER
340 , p_supply_demand_type_id IN NUMBER
341 , p_supply_demand_header_id IN NUMBER
342 , p_supply_demand_line_id IN NUMBER
343 , p_supply_demand_line_detail IN NUMBER
344 , p_demand_ship_date IN DATE
345 , p_expected_receipt_date IN DATE
346 , x_valid_status OUT NOCOPY VARCHAR2
347 )
348 IS
349 -- Declare local variables
350 l_api_name CONSTANT VARCHAR2(30) := 'validate_supply_demand';
351 l_api_version CONSTANT NUMBER := 1.0;
352 l_init_msg_list VARCHAR2(1) := 'F';
353 l_return_status VARCHAR2(1);
354 l_msg_count NUMBER;
355 l_msg_data VARCHAR2(2000);
356 l_debug_module CONSTANT VARCHAR2(100) := 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name;
357
358 -- Validating the details if it is of type INV
359 CURSOR get_mtl_dtls_inv_cur ( c_scheduled_material_id IN NUMBER,
360 c_visit_task_id IN NUMBER,
361 c_rt_oper_material_id IN NUMBER,
362 c_organization_id IN NUMBER,
363 c_item_id IN NUMBER)
364 IS
365 SELECT scheduled_material_id
366 FROM ahl_schedule_materials
367 WHERE scheduled_material_id = c_SCHEDULED_MATERIAL_ID
368 AND organization_id = c_organization_ID
369 AND inventory_item_id = c_item_ID
370 AND visit_task_id = c_visit_task_ID
371 AND rt_oper_material_id = c_rt_oper_material_id
372 AND status = 'ACTIVE'
373 AND requested_quantity <>0
377 AND vt.visit_task_id = c_visit_task_id);
374 AND EXISTS ( SELECT 1
375 FROM ahl_visit_tasks_b vt
376 WHERE vt.status_code = 'PLANNING'
378 -- Validating the details if it is of type WIP
379 CURSOR get_mtl_dtls_wip_cur ( c_scheduled_material_id IN NUMBER,
380 c_wip_entity_id IN NUMBER,
381 c_oper_seq_num IN NUMBER,
382 c_organization_id IN NUMBER,
383 c_item_id IN NUMBER)
384 IS
385 SELECT scheduled_material_id
386 FROM ahl_schedule_materials
387 WHERE scheduled_material_id = c_scheduled_material_id
388 AND organization_id = c_organization_id
389 AND inventory_item_id = c_item_id
390 AND Operation_sequence = c_oper_seq_num
391 AND status = 'ACTIVE'
392 AND requested_quantity <>0
393 AND visit_task_id = ( SELECT aw.visit_task_id
394 FROM ahl_visit_tasks_b vt, ahl_workorders aw
395 WHERE vt.status_code IN ('PLANNING','RELEASED')
396 AND aw.wip_entity_id = c_wip_entity_id
397 AND aw.status_code in ('1','3')
398 AND aw.visit_task_id= vt.visit_task_id );
399
400 --sukhwsin::Marshling enhancements - changes starts
401 /* This cursor validate wip_job against
402 -> particular item and organization.
403 -> job status must not be cancelled, deleted, draft, closed.
404 -> job must be RTS job.
405 -> no reservation exists for the job as a valid supply in mtl_reservation table.
406 */
407 CURSOR validate_wip_supply(c_wip_entity_id IN NUMBER, c_item_id IN NUMBER, c_organization_id IN NUMBER, c_source_type IN NUMBER) IS
408 SELECT 'X'
409 FROM
410 ahl_workorders wo, ahl_visit_tasks_b vts, wip_discrete_jobs wdj
411 WHERE
412 wdj.wip_entity_id = c_wip_entity_id
413 AND wo.wip_entity_id = wdj.wip_entity_id
414 AND wo.status_code NOT IN (7, 22, 17, 12, 18, 21, 4, 5) -- cancelled, deleted, draft, closed,Deffered,Pending Defer/Cancel Approval, Complete, Complete No-charge
415 AND vts.visit_task_id = wo.visit_task_id
416 AND wdj.rebuild_item_id = c_item_id
417 AND vts.return_to_supply_flag = 'Y'
418 AND wdj.organization_id = c_organization_id
419 /* Commented out for WO-WO validation by debadey as per instructions from Balaji
420 AND NOT EXISTS (SELECT 'X' FROM mtl_reservations
421 WHERE external_source_code = 'AHL'
422 AND supply_source_type_id = c_source_type
423 AND supply_source_header_id = c_wip_entity_id)*/;
424 --sukhwsin::Marshling enhancements - changes ends
425 -- local variables
426 l_scheduled_material_id NUMBER := null;
427 --sukhwsin::Marshling enhancements - added local variable to hold job status check
428 l_job_exists VARCHAR2(1) := null;
429 BEGIN
430 -- Standard start of API savepoint
431 SAVEPOINT VALIDATE_SUPPLY_DEMAND_GRP;
432
433 -- Initialize return status to success before any code logic/validation
434 x_return_status:= FND_API.G_RET_STS_SUCCESS;
435
436 -- Standard call to check for call compatibility
437 IF NOT FND_API.COMPATIBLE_API_CALL (l_api_version, p_api_version_number, l_api_name, G_PKG_NAME)
438 THEN
439 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
440 END IF;
441
442 -- Initialize message list if p_init_msg_list = FND_API.G_TRUE
443 IF FND_API.TO_BOOLEAN(p_init_msg_lst)
444 THEN
445 FND_MSG_PUB.INITIALIZE;
446 END IF;
447 l_msg_count := FND_MSG_PUB.count_msg;
448 -- Log API entry point
449 IF (l_log_procedure >= l_log_current_level)THEN
450 fnd_log.string
451 (
452 fnd_log.level_procedure,
453 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||'.begin',
454 'At the start of PL SQL procedure '
455 );
456 END IF;
457
458 -- log all the input parameters
459 IF (l_log_statement >= l_log_current_level)THEN
460 fnd_log.string
461 (
462 fnd_log.level_statement,'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
463 'p_organization_id-->' || p_organization_id
464 );
465 fnd_log.string
466 (
467 fnd_log.level_statement,'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
468 'p_item_id-->' || p_item_id
469 );
470 fnd_log.string
471 (
472 fnd_log.level_statement,'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
473 'p_supply_demand_code-->' || p_supply_demand_code
474 );
475 fnd_log.string
476 (
477 fnd_log.level_statement,'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
478 'p_supply_demand_type_id-->' || p_supply_demand_type_id
479 );
480 fnd_log.string
481 (
482 fnd_log.level_statement,'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
483 'p_supply_demand_header_id-->' || p_supply_demand_header_id
484 );
485 fnd_log.string
486 (
487 fnd_log.level_statement,'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
488 'p_supply_demand_line_id-->' || p_supply_demand_line_id
489 );
490 fnd_log.string
491 (
492 fnd_log.level_statement,'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
493 'p_supply_demand_line_detail-->' || p_supply_demand_line_detail
494 );
495 fnd_log.string
496 (
497 fnd_log.level_statement,'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
498 'p_demand_ship_date-->' || p_demand_ship_date
499 );
503 'p_expected_receipt_date-->' || p_expected_receipt_date
500 fnd_log.string
501 (
502 fnd_log.level_statement,'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
504 );
505 END IF;
506
507 --sukhwsin::Marshling enhancements - changes starts
508 --changed validation logic for p_supply_demand_code to include validation for supply workorders.
509 -- Validate the Supply Demand Code
510 -- 1 : 'SUPPLY' 2 : 'DEMAND'.
511 --IF p_supply_demand_code <> 2 THEN
512 IF (p_supply_demand_code NOT IN (1, 2)) THEN
513 FND_MESSAGE.set_name( 'AHL', 'AHL_RSV_INVLD_SUP_DMD' );
514 FND_MSG_PUB.add;
515 --Invalid value for supply demand code.
516 -- log the error
517 IF (l_log_statement >= l_log_current_level)THEN
518 fnd_log.string
519 (
520 fnd_log.level_statement,
521 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
522 --'The reservations should be against demand, not supply.'
523 'Invalid value for supply demand code.'
524 );
525 END IF;
526 RAISE FND_API.G_EXC_ERROR;
527 END IF;
528
529 --sukhwsin::Marshling enhancements - moved validations for common parameters on above and specific validations inside particular branch.
530 --common mandatory parameters for supply and demand are p_supply_demand_header_id, p_organization_id, p_item_id
531 -- p_supply_demand_header_id null check
532 IF p_supply_demand_header_id IS NULL OR p_supply_demand_header_id = fnd_api.g_miss_num THEN
533 FND_MESSAGE.set_name( 'AHL', 'AHL_RSV_DMND_HDR_MAND' );
534 FND_MSG_PUB.add;
535 -- Demand header is required for CMRO reservations
536 -- log the error
537 IF (l_log_statement >= l_log_current_level)THEN
538 fnd_log.string
539 (
540 fnd_log.level_statement,
541 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
542 'Demand header is required for CMRO reservations'
543 );
544 END IF;
545 RAISE FND_API.G_EXC_ERROR;
546 END IF;
547
548 -- p_organization_id null check
549 IF p_organization_id IS NULL OR p_organization_id = fnd_api.g_miss_num THEN
550 FND_MESSAGE.set_name( 'AHL', 'AHL_RSV_ORG_MAND_VLD_SUP_DMND' );
551 FND_MSG_PUB.add;
552 -- Organization is mandatory in validate_supply_demand.
553 -- log the error
554 IF (l_log_statement >= l_log_current_level)THEN
555 fnd_log.string
556 (
557 fnd_log.level_statement,
558 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
559 'Organization is mandatory in validate_supply_demand.'
560 );
561 END IF;
562 RAISE FND_API.G_EXC_ERROR;
563 END IF;
564
565
566 -- p_item_id null check
567 IF p_item_id IS NULL OR p_item_id = fnd_api.g_miss_num THEN
568 FND_MESSAGE.set_name( 'AHL', 'AHL_RSV_ITM_MAND_VLD_SUP_DMND' );
569 FND_MSG_PUB.add;
570 -- Item ID is mandatory in validate_supply_demand.
571 -- log the error
572 IF (l_log_statement >= l_log_current_level)THEN
573 fnd_log.string
574 (
575 fnd_log.level_statement,
576 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
577 'Item ID is mandatory in validate_supply_demand.'
578 );
579 END IF;
580 RAISE FND_API.G_EXC_ERROR;
581 END IF;
582
583 IF (p_supply_demand_code = 2 ) THEN
584 --Validations for demand.
585 -- p_supply_demand_line_id null check
586 IF p_supply_demand_line_id IS NULL OR p_supply_demand_line_id = fnd_api.g_miss_num THEN
587 FND_MESSAGE.set_name( 'AHL', 'AHL_RSV_DMND_LINE_MAND' );
588 FND_MSG_PUB.add;
589 -- Demand Line is required for CMRO reservations
590 -- log the error
591 IF (l_log_statement >= l_log_current_level)THEN
592 fnd_log.string
593 (
594 fnd_log.level_statement,
595 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
596 'Demand Line is required for CMRO reservations'
597 );
598 END IF;
599 RAISE FND_API.G_EXC_ERROR;
600 END IF;
601
602 -- p_supply_demand_line_detail null check
603 IF p_supply_demand_line_detail IS NULL OR p_supply_demand_line_detail = fnd_api.g_miss_num THEN
604 FND_MESSAGE.set_name( 'AHL', 'AHL_RSV_DMND_LINE_DTL_MAND' );
605 FND_MSG_PUB.add;
606 -- Demand Line Detail is required for CMRO reservations
607 -- log the error
608 IF (l_log_statement >= l_log_current_level)THEN
609 fnd_log.string
610 (
611 fnd_log.level_statement,
612 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
613 'Demand Line Detail is required for CMRO reservations'
614 );
615 END IF;
616 RAISE FND_API.G_EXC_ERROR;
617 END IF;
618
619 -- Validate the details by seeing whether they exist
620 IF p_supply_demand_type_id = inv_reservation_global.g_source_type_inv THEN
621 OPEN get_mtl_dtls_inv_cur( p_supply_demand_line_detail,
622 p_supply_demand_header_id,
623 p_supply_demand_line_id,
624 p_organization_id,
625 p_item_id);
626 FETCH get_mtl_dtls_inv_cur INTO l_scheduled_material_id;
627 CLOSE get_mtl_dtls_inv_cur;
628 ELSIF p_supply_demand_type_id = inv_reservation_global.g_source_type_wip THEN
629 OPEN get_mtl_dtls_wip_cur ( p_supply_demand_line_detail,
630 p_supply_demand_header_id,
634 FETCH get_mtl_dtls_wip_cur INTO l_scheduled_material_id;
631 p_supply_demand_line_id,
632 p_organization_id,
633 p_item_id);
635 CLOSE get_mtl_dtls_wip_cur;
636 ELSE
637 FND_MESSAGE.set_name( 'AHL', 'AHL_RSV_SUP_DEM_TYPE_INVLD' );
638 FND_MSG_PUB.add;
639 IF (l_log_statement >= l_log_current_level)THEN
640 fnd_log.string
641 (
642 fnd_log.level_statement,
643 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
644 'Wrong value for Supply Demand Type.'
645 );
646 END IF;
647 RAISE FND_API.G_EXC_ERROR;
648 END IF;
649
650 -- set the return value.
651 IF l_scheduled_material_id is null THEN
652 x_valid_status := 'N';
653 ELSE
654 x_valid_status := 'Y';
655 END IF;
656
657 ELSIF (p_supply_demand_code = 1 ) THEN
658 --Validation for Supply.
659 IF (p_supply_demand_type_id = inv_reservation_global.g_source_type_wip) THEN
660 --Check job existance only if supply_demand_type_id is of WIP as we are checking for WO supply only.
661 OPEN validate_wip_supply(p_supply_demand_header_id,
662 p_item_id,
663 p_organization_id,
664 inv_reservation_global.g_source_type_wip);
665 FETCH validate_wip_supply INTO l_job_exists;
666 CLOSE validate_wip_supply;
667 ELSE
668 --If Supply demand type is not equal to WIP then raise error, as this supply check is only applicable on WIP supply.
669 FND_MESSAGE.set_name( 'AHL', 'AHL_RSV_SUP_DEM_TYPE_INVLD' );
670 FND_MSG_PUB.add;
671 IF (l_log_statement >= l_log_current_level)THEN
672 fnd_log.string
673 (
674 fnd_log.level_statement,
675 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
676 'Wrong value for Supply Demand Type.'
677 );
678 END IF;
679 RAISE FND_API.G_EXC_ERROR;
680 END IF;
681
682 IF (l_job_exists IS NULL) THEN
683 x_valid_status := 'N';
684 ELSE
685 x_valid_status := 'Y';
686 END IF;
687
688 END IF; -- end if for p_supply_demand_code check
689 --sukhwsin::Marshling enhancements - changes ends
690 IF (l_log_statement >= l_log_current_level)THEN
691 fnd_log.string
692 (
693 fnd_log.level_statement,
694 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
695 'Return Value: x_valid_status' || x_valid_status
696 );
697 END IF;
698
699
700 -- End logging
701 IF (l_log_procedure >= l_log_current_level)THEN
702 fnd_log.string
703 (
704 fnd_log.level_procedure,
705 l_debug_module||'.end',
706 'At the end of PLSQL procedure'
707 );
708 END IF;
709
710 -- Check Error Message stack.
711 x_msg_count := FND_MSG_PUB.count_msg;
712 IF x_msg_count > l_msg_count
713 THEN
714 RAISE FND_API.G_EXC_ERROR;
715 END IF;
716
720 p_count => x_msg_count,
717 -- Standard call to get message count and if count is 1, get message info
718 FND_MSG_PUB.count_and_get
719 (
721 p_data => x_msg_data,
722 p_encoded => FND_API.G_FALSE
723 );
724 IF (l_log_procedure >= l_log_current_level)THEN
725 fnd_log.string
726 (
727 fnd_log.level_procedure,
728 l_debug_module||'.end',
729 'At the last line PLSQL procedure, return status := '|| x_return_status ||
730 ' and x_valid_status = ' || x_valid_status
731 );
732 END IF;
733 EXCEPTION
734 WHEN FND_API.G_EXC_ERROR THEN
735 x_return_status := FND_API.G_RET_STS_ERROR;
736 FND_MSG_PUB.count_and_get
737 (
738 p_count => x_msg_count,
739 p_data => x_msg_data,
740 p_encoded => FND_API.G_FALSE
741 );
742
743 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
744 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
745 FND_MSG_PUB.count_and_get
746 (
747 p_count => x_msg_count,
748 p_data => x_msg_data,
749 p_encoded => FND_API.G_FALSE
750 );
751
752 WHEN OTHERS THEN
753 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
754 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
755 THEN
756 FND_MSG_PUB.add_exc_msg
757 (
758 p_pkg_name => G_PKG_NAME,
759 p_procedure_name => 'get_available_supply_demand',
760 p_error_text => SUBSTR(SQLERRM,1,240)
761 );
762 END IF;
763 FND_MSG_PUB.count_and_get
764 (
765 p_count => x_msg_count,
766 p_data => x_msg_data,
767 p_encoded => FND_API.G_FALSE
768 );
769 END validate_supply_demand;
770
771 END AHL_INV_RESERVATIONS_GRP;
[Home] [Help]