[Home] [Help]
PACKAGE BODY: APPS.AHL_MM_RESERVATIONS_PVT
Source
1 PACKAGE BODY AHL_MM_RESERVATIONS_PVT AS
2 /* $Header: AHLVMMRB.pls 120.0.12020000.2 2012/12/14 10:35:47 shnatu noship $ */
3 ------------------------------------
4 -- Common constants and variables --
5 ------------------------------------
6 l_log_current_level NUMBER := fnd_log.g_current_runtime_level;
7 l_log_statement NUMBER := fnd_log.level_statement;
8 l_log_procedure NUMBER := fnd_log.level_procedure;
9 l_log_error NUMBER := fnd_log.level_error;
10 l_log_unexpected NUMBER := fnd_log.level_unexpected;
11
12
13 FUNCTION validate_reservation_id(p_reservation_id IN NUMBER) RETURN NUMBER;
14 PROCEDURE INITIALIZE_CREATE_REC(
15 p_schedule_material_id IN NUMBER,
16 p_x_rsv_rec IN OUT NOCOPY mtl_reservation_rec_type,
17 x_return_status OUT NOCOPY VARCHAR2
18 );
19 PROCEDURE CONVERT_TO_INV_TABLE_TYPE(p_mtl_reservation_rec IN mtl_reservation_rec_type ,
20 p_serial_number_tbl IN serial_number_tbl_type ,
21 x_mtl_reservation_rec OUT NOCOPY inv_reservation_global.mtl_reservation_rec_type,
22 x_serial_number_tbl OUT NOCOPY inv_reservation_global.serial_number_tbl_type);
23 PROCEDURE INITIALIZE_REC_FROM_RESV(
24 p_reservation_id IN NUMBER,
25 p_x_rsv_rec IN OUT NOCOPY mtl_reservation_rec_type) ;
26
27 --Added by debadey
28 PROCEDURE INITIALIZE_WO_UPDATE_REC(
29 p_schedule_material_id IN NUMBER,
30 p_x_rsv_rec IN OUT NOCOPY mtl_reservation_rec_type,
31 x_return_status OUT NOCOPY VARCHAR2
32 );
33
34 PROCEDURE GET_MATCHING_RESERVATION(p_scheduled_material_id IN NUMBER,
35 p_sub_inv_code IN VARCHAR2,
36 p_locator_id IN NUMBER,
37 p_lot_number IN VARCHAR2,
38 p_revision IN VARCHAR2,
39 p_supply_source_type_id IN NUMBER,
40 x_reservation_id OUT NOCOPY NUMBER,
41 x_reservation_quantity OUT NOCOPY NUMBER);
42
43 CURSOR get_mtl_req_dtls_csr (c_scheduled_material_id IN NUMBER) IS
44 SELECT asmt.organization_id, asmt.requested_date, asmt.uom,
45 nvl(asmt.requested_quantity,0) requested_quantity, asmt.inventory_item_id,
46 asmt.object_version_number, (SELECT SUM(mr.primary_reservation_quantity)
47 FROM mtl_reservations MR
48 WHERE mr .demand_source_line_detail = c_scheduled_material_id
49 AND mr.organization_id = asmt.organization_id
50 AND mr.requirement_date = asmt.requested_date
51 AND mr.inventory_item_id = asmt.inventory_item_id
52 AND mr.external_source_code = 'AHL'
53 GROUP BY mr.demand_source_line_detail
54 ) reserved_quantity,
55 nvl(asmt.completed_quantity,0) completed_quantity, asmt.workorder_operation_id, asmt.operation_sequence,
56 aw.wip_entity_id,aw.workorder_id,wdj.project_id, wdj.task_id
57 FROM ahl_schedule_materials asmt, ahl_workorders aw, wip_discrete_jobs wdj
58 WHERE asmt.scheduled_material_id = c_scheduled_material_id
59 AND asmt.visit_task_id = aw.visit_task_id
60 AND aw.wip_entity_id = wdj.wip_entity_id
61 AND asmt.visit_id = aw.visit_id
62 AND aw.status_code in ('1','3') -- 1:Unreleased,3:Released
63 AND asmt.status = 'ACTIVE';
64
65 l_mtl_req_dtls_rec get_mtl_req_dtls_csr%ROWTYPE;
66 ------------------------------------------------------------------------------------------------------------------
67 -- Start of Comments --
68 -- Procedure name : CREATE_RESERVATION
69 -- Type : Private
70 -- Function : Reserves the serial numbers in the p_serial_number_tbl
71 PROCEDURE CREATE_RESERVATION(
72 p_api_version IN NUMBER := 1.0,
73 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
74 p_commit IN VARCHAR2 := FND_API.G_FALSE,
75 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
76 p_module_type IN VARCHAR2,
77 x_return_status OUT NOCOPY VARCHAR2,
78 x_msg_count OUT NOCOPY NUMBER,
79 x_msg_data OUT NOCOPY VARCHAR2,
80 p_rsv_rec IN mtl_reservation_rec_type,
81 p_serial_number_tbl IN serial_number_tbl_type,
82 x_reservation_id OUT NOCOPY NUMBER)
83 IS
84 -- Declare local variables
85 l_api_name CONSTANT VARCHAR2(30) := 'create_reservation';
86 l_api_version CONSTANT NUMBER := 1.0;
87 l_init_msg_list VARCHAR2(1) := 'F';
88 l_return_status VARCHAR2(1);
89 l_msg_count NUMBER;
90 l_msg_data VARCHAR2(2000);
91 l_debug_module CONSTANT VARCHAR2(100) := 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name;
92
93 l_rsv_rec mtl_reservation_rec_type := p_rsv_rec;
94 l_create_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
95 l_serial_number_tbl inv_reservation_global.serial_number_tbl_type;
96 l_x_serial_number_tbl inv_reservation_global.serial_number_tbl_type;
97 l_x_quantity_reserved NUMBER;
98 l_scheduled_material_id NUMBER := p_rsv_rec.demand_source_line_detail;
99 l_x_reservation_id NUMBER;
100
101 BEGIN
102 -- Standard start of API savepoint
103 SAVEPOINT CREATE_RESERVATION_PVT;
104 -- Initialize return status to success before any code logic/validation
105 x_return_status:= FND_API.G_RET_STS_SUCCESS;
106
107
108 -- Standard call to check for call compatibility
109 IF NOT FND_API.COMPATIBLE_API_CALL (l_api_version, p_api_version, l_api_name, G_PKG_NAME)
110 THEN
111 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
112 END IF;
113
114
115 -- Initialize message list if p_init_msg_list = FND_API.G_TRUE
116 IF FND_API.TO_BOOLEAN(p_init_msg_list)
117 THEN
118 FND_MSG_PUB.INITIALIZE;
119 END IF;
120
121 -- Initialize the record to be send to the WMS package
122 Initialize_create_rec(l_scheduled_material_id, -- the schedule material id
123 l_rsv_rec, -- record to be passed ti the WMS packages
124 l_return_status); -- return status
125
126 IF(l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
127 IF (l_log_error >= l_log_current_level) THEN
128 fnd_log.string(
129 fnd_log.level_error,
130 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
131 'Call to Initialize_create_rec returned Unexpected Error');
132 END IF;
133 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
134 END IF;
135
136 --Convert to Inv rec type
137 CONVERT_TO_INV_TABLE_TYPE(p_mtl_reservation_rec => l_rsv_rec,
138 p_serial_number_tbl => p_serial_number_tbl,
139 x_mtl_reservation_rec => l_create_rsv_rec,
140 x_serial_number_tbl => l_serial_number_tbl);
141
142 -- Log API entry point
143 IF (l_log_procedure >= l_log_current_level) THEN
144 fnd_log.string
145 (
146 fnd_log.level_procedure,
147 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||'.begin',
148 'At the start of PL SQL procedure scheduled_material_id : ' || l_scheduled_material_id ||
149 'total serial_number count: '||l_serial_number_tbl.COUNT
150 );
151 END IF;
152
153
154 IF(p_module_type <> G_PKG_NAME ) THEN
155
156
157 -- Get the Material Requirements details
158 OPEN get_mtl_req_dtls_csr(l_scheduled_material_id);
159 FETCH get_mtl_req_dtls_csr INTO l_mtl_req_dtls_rec;
160 IF get_mtl_req_dtls_csr%NOTFOUND THEN
161 FND_MESSAGE.set_name( 'AHL', 'AHL_RSV_INVLD_MAT_REQ' );
162 FND_MSG_PUB.add;
163 -- log the error
164 IF (l_log_error >= l_log_current_level) THEN
165 fnd_log.string
166 (
167 fnd_log.level_error,
168 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
169 'get_mtl_req_dtls_csr, did not fetch any records'
170 );
171 END IF;
172 CLOSE get_mtl_req_dtls_csr;
173 RAISE FND_API.G_EXC_ERROR;
174 END IF; -- Material Requirement details not found
175 CLOSE get_mtl_req_dtls_csr;
176
177 -- Validate whether the sum of already issued + reserved quantities is not more than the initially requested quantity
178 IF l_mtl_req_dtls_rec.completed_quantity +
179 l_mtl_req_dtls_rec.reserved_quantity >= l_mtl_req_dtls_rec.requested_quantity THEN
180 FND_MESSAGE.set_name( 'AHL', 'AHL_RSV_QTY_EXCDS_REQSTD' );
181 FND_MSG_PUB.add;
182 -- log the error
183 IF (l_log_error >= l_log_current_level) THEN
184 fnd_log.string
185 (
186 fnd_log.level_error,
187 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
188 'Already Completed Qty + Reserved Qty is equal or more than Requested Qty '
189 );
190 END IF;
191 RAISE FND_API.G_EXC_ERROR;
192 END IF;
193
194 IF(l_create_rsv_rec.supply_source_type_id = inv_reservation_global.g_source_type_inv) THEN
195
196 -- Validate to make sure that the Serial Number is not empty, when Supply is Inventory.
197 IF (l_serial_number_tbl.COUNT < 1) THEN
198 FND_MESSAGE.set_name( 'AHL', 'AHL_PP_SERIAL_MISSING' );
199 FND_MSG_PUB.add;
200 -- log the error
201 IF (l_log_error >= l_log_current_level) THEN
202 fnd_log.string
203 (
204 fnd_log.level_error,
205 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
206 'Serial Number passed to the API is null'
207 );
208 END IF;
209 RAISE FND_API.G_EXC_ERROR;
210 END IF;
211
212 FOR i IN l_serial_number_tbl.FIRST..l_serial_number_tbl.LAST
213 LOOP
214 l_serial_number_tbl(i).inventory_item_id := l_mtl_req_dtls_rec.inventory_item_id;
215 END LOOP;
216
217 END IF;
218
219 END IF;
220
221
222 IF(l_create_rsv_rec.primary_reservation_quantity = FND_API.G_MISS_NUM) THEN
223 l_create_rsv_rec.primary_reservation_quantity := l_serial_number_tbl.COUNT;
224 END IF;
225
226
227 IF (l_log_statement >= l_log_current_level) THEN
228 fnd_log.string
229 (
230 fnd_log.level_statement,
231 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
232 'Calling WMS api:inv_reservation_pub.create_reservation :'
233 );
234 END IF;
235
236 inv_reservation_pub.create_reservation
237 (
238 p_api_version_number => l_api_version,
239 p_init_msg_lst => l_init_msg_list,
240 x_return_status => l_return_status,
241 x_msg_count => l_msg_count,
242 x_msg_data => l_msg_data,
243 p_rsv_rec => l_create_rsv_rec,
244 p_serial_number => l_serial_number_tbl,
245 x_serial_number => l_x_serial_number_tbl,
246 x_quantity_reserved => l_x_quantity_reserved,
247 x_reservation_id => l_x_reservation_id
248 );
249 x_reservation_id := l_x_reservation_id;
250 IF (l_log_statement >= l_log_current_level) THEN
251 fnd_log.string
252 (
253 fnd_log.level_statement,
254 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
255 'After Calling WMS api: x_reservation_id := '|| l_x_reservation_id || ' and l_return_status = '||l_return_status
256 );
257 END IF;
258 -- Check the error status
259 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
260 -- log the error
261 IF (l_log_error >= l_log_current_level) THEN
262 fnd_log.string
263 (
264 fnd_log.level_error,
265 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
266 'Call to WMS returned Unexpected Error'
267 );
268 END IF;
269 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
270 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
271 -- log the error
272 IF (l_log_error >= l_log_current_level) THEN
273 fnd_log.string
274 (
275 fnd_log.level_error,
276 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
277 'Call to WMS returned Expected Error'
278 );
279 END IF;
280 --RAISE at the end of the API FND_API.G_EXC_ERROR;
281 END IF;
282
283
284 -- Check Error Message stack.
285 x_msg_count := FND_MSG_PUB.count_msg;
286 IF x_msg_count > 0
287 THEN
288 -- log the error
289 IF (l_log_error >= l_log_current_level) THEN
290 fnd_log.string
291 (
292 fnd_log.level_error,
293 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
294 'Call to WMS returned Errors in x_msg_count'
295 );
296 END IF;
297 RAISE FND_API.G_EXC_ERROR;
298 END IF;
299
300 -- Commit if p_commit = FND_API.G_TRUE
301 IF FND_API.TO_BOOLEAN(p_commit)
302 THEN
303 COMMIT WORK;
304 IF (l_log_statement >= l_log_current_level) THEN
305 fnd_log.string
306 (
307 fnd_log.level_statement,
308 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
309 'Committed'
310 );
311 END IF;
312 END IF;
313
314 -- Standard call to get message count and if count is 1, get message info
315 FND_MSG_PUB.count_and_get
316 (
317 p_count => x_msg_count,
318 p_data => x_msg_data,
319 p_encoded => FND_API.G_FALSE
320 );
321
322 -- Log API exit point
323 IF (l_log_procedure >= l_log_current_level)THEN
324 fnd_log.string
325 (
326 fnd_log.level_procedure,
327 l_debug_module||'.end',
328 'At the end of PLSQL procedure'
329 );
330 END IF;
331
332 EXCEPTION
333 WHEN FND_API.G_EXC_ERROR THEN
334 ROLLBACK TO CREATE_RESERVATION_PVT;
335 x_return_status := FND_API.G_RET_STS_ERROR;
336 FND_MSG_PUB.count_and_get
337 (
338 p_count => x_msg_count,
339 p_data => x_msg_data,
340 p_encoded => FND_API.G_FALSE
341 );
342
343 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
344 ROLLBACK TO CREATE_RESERVATION_PVT;
345 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
346 FND_MSG_PUB.count_and_get
347 (
348 p_count => x_msg_count,
349 p_data => x_msg_data,
350 p_encoded => FND_API.G_FALSE
351 );
352
353 WHEN OTHERS THEN
354 ROLLBACK TO CREATE_RESERVATION_PVT;
355 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
356 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
357 THEN
358 FND_MSG_PUB.add_exc_msg
359 (
360 p_pkg_name => G_PKG_NAME,
361 p_procedure_name => 'create_reservation',
362 p_error_text => SUBSTR(SQLERRM,1,240)
363 );
364 END IF;
365 FND_MSG_PUB.count_and_get
366 (
367 p_count => x_msg_count,
368 p_data => x_msg_data,
369 p_encoded => FND_API.G_FALSE
370 );
371 END CREATE_RESERVATION;
372
373 ------------------------------------------------------------------------------------------------------------------
374 -- Declare Procedure --
375 ------------------------------------------------------------------------------------------------------------------
376 -- Start of Comments --
377 -- Procedure name : UPDATE_RESERVATION
378 -- Type : Private
379 -- Function : Updates reservation for serial numbers in the p_serial_number_tbl
380 PROCEDURE UPDATE_RESERVATION(
381 p_api_version IN NUMBER := 1.0,
382 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
383 p_commit IN VARCHAR2 := FND_API.G_TRUE,
384 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
385 p_module_type IN VARCHAR2,
386 x_return_status OUT NOCOPY VARCHAR2,
387 x_msg_count OUT NOCOPY NUMBER,
388 x_msg_data OUT NOCOPY VARCHAR2,
389 p_reservation_id IN NUMBER ,
390 p_rsv_rec IN mtl_reservation_rec_type,
391 p_serial_number_tbl IN serial_number_tbl_type)
392 IS
393 -- Declare local variables
394 l_api_name CONSTANT VARCHAR2(30) := 'update_reservation';
395 l_debug_module CONSTANT VARCHAR2(100) := 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name;
396
397 l_api_version CONSTANT NUMBER := 1.0;
398 l_init_msg_list VARCHAR2(1) := 'F';
399 l_return_status VARCHAR2(1);
400 l_msg_count NUMBER;
401 l_msg_data VARCHAR2(2000);
402
403 CURSOR get_reservation_csr (c_resrv_id NUMBER) IS
404 SELECT mrsv.primary_reservation_quantity,mrsv.inventory_item_id
405 FROM mtl_reservations mrsv
406 WHERE mrsv.reservation_id = c_resrv_id;
407
408 l_from_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
409 l_to_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
410 --dummy variables
411 l_to_serial_number_tbl inv_reservation_global.serial_number_tbl_type;
412 l_from_serial_number_tbl inv_reservation_global.serial_number_tbl_type;
413 l_primary_reservation_quantity NUMBER;
414 l_scheduled_material_id NUMBER;
415 l_inventory_item_id NUMBER;
416
417 BEGIN
418 -- Standard start of API savepoint
419 SAVEPOINT UPDATE_RESERVATION_PVT;
420
421 -- Initialize return status to success before any code logic/validation
422 x_return_status:= FND_API.G_RET_STS_SUCCESS;
423
424 -- Standard call to check for call compatibility
425 IF NOT FND_API.COMPATIBLE_API_CALL (l_api_version, p_api_version, l_api_name, G_PKG_NAME)
426 THEN
427 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
428 END IF;
429
430 -- Initialize message list if p_init_msg_list = FND_API.G_TRUE
431 IF FND_API.TO_BOOLEAN(p_init_msg_list)
432 THEN
433 FND_MSG_PUB.INITIALIZE;
434 END IF;
435
436 -- Log API entry point
437 IF (l_log_procedure >= l_log_current_level)THEN
438 fnd_log.string
439 (
440 fnd_log.level_procedure,
441 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||'.begin',
442 'At the start of PL SQL procedure, p_reservation_id : '|| p_reservation_id
443 );
444 END IF;
445
446 IF(p_module_type <> G_PKG_NAME ) THEN
447 -- Validate the reservation id
448 l_scheduled_material_id := validate_reservation_id(p_reservation_id);
449 IF (l_scheduled_material_id IS NULL) THEN
450 FND_MESSAGE.set_name( 'AHL', 'AHL_LTP_MULTI_ORG' );
451 FND_MSG_PUB.add;
452 -- log the error
453 IF (l_log_error >= l_log_current_level) THEN
454 fnd_log.string
455 (
456 fnd_log.level_error,
457 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||'.begin',
458 'Invalid Reservation ID'
459 );
460 END IF;
461 RAISE FND_API.G_EXC_ERROR;
462 END IF;
463 END IF;
464
465 --Convert to Inv rec type
466 CONVERT_TO_INV_TABLE_TYPE(p_mtl_reservation_rec => p_rsv_rec,
467 p_serial_number_tbl => p_serial_number_tbl,
468 x_mtl_reservation_rec => l_to_rsv_rec,
469 x_serial_number_tbl => l_to_serial_number_tbl);
470
471 --update the reservation quantity
472 IF(l_to_serial_number_tbl.COUNT > 0) THEN
473 OPEN get_reservation_csr(p_reservation_id);
474 FETCH get_reservation_csr into l_primary_reservation_quantity,l_inventory_item_id;
475 CLOSE get_reservation_csr;
476
477 IF (l_log_statement >= l_log_current_level) THEN
478 fnd_log.string
479 (
480 fnd_log.level_statement,
481 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
482 'l_primary_reservation_quantity = '||l_primary_reservation_quantity||
483 'l_to_rsv_rec.primary_reservation_quantity = '||l_to_rsv_rec.primary_reservation_quantity||
484 ' l_to_serial_number_tbl.count = '|| l_to_serial_number_tbl.count
485 );
486 END IF;
487
488 -- Addition of Quantity only when called from Process_Reservation.
489 -- Else expected, the caller should give correct PRIMARY quantity.
490 IF(p_module_type = g_pkg_name ) THEN
491 l_to_rsv_rec.primary_reservation_quantity := l_primary_reservation_quantity + l_to_serial_number_tbl.count;
492 END IF;
493
494 FOR i IN l_to_serial_number_tbl.FIRST..l_to_serial_number_tbl.LAST
495 LOOP
496 l_to_serial_number_tbl(i).inventory_item_id := l_inventory_item_id;
497 END LOOP;
498
499 /* ELSE
500
501 l_to_rsv_rec.primary_reservation_quantity := FND_API.G_MISS_NUM;
502
503 END IF; */
504 END IF;
505
506 l_from_rsv_rec.reservation_id := p_reservation_id;
507 l_to_rsv_rec.reservation_id := p_reservation_id;
508
509 IF (l_log_statement >= l_log_current_level) THEN
510 fnd_log.string
511 (
512 fnd_log.level_statement,
513 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
514 'Calling WMS api:inv_reservation_pub.update_reservation'
515 );
516 END IF;
517
518
519 IF (l_log_statement >= l_log_current_level) THEN
520 fnd_log.string
521 (
522 fnd_log.level_statement,
523 'ahl.plsql.'||G_PKG_NAME||'.'||L_API_NAME,
524 ',l_from_rsv_rec.project_id - ' || L_FROM_RSV_REC.PROJECT_ID ||
525 ', l_from_rsv_rec.task_id - ' || L_FROM_RSV_REC.TASK_ID ||
526 ', l_to_rsv_rec.project_id - ' || L_TO_RSV_REC.PROJECT_ID ||
527 ', l_to_rsv_rec.task_id - ' || l_to_rsv_rec.task_id
528 );
529 END IF;
530
531
532 -- Call WMS Update reservation API
533 inv_reservation_pub.update_reservation
534 (
535 p_api_version_number => l_api_version,
536 p_init_msg_lst => l_init_msg_list,
537 x_return_status => l_return_status,
538 x_msg_count => l_msg_count,
539 x_msg_data => l_msg_data,
540 p_original_rsv_rec => l_from_rsv_rec,
541 p_to_rsv_rec => l_to_rsv_rec,
542 p_original_serial_number => l_from_serial_number_tbl,
543 p_to_serial_number => l_to_serial_number_tbl
544 );
545
546 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
547 -- log the error
548 IF (l_log_error >= l_log_current_level) THEN
549 fnd_log.string
550 (
551 fnd_log.level_error,
552 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
553 'Call to WMS returned Unexpected Error'
554 );
555 END IF;
556 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
557 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
558 -- log the error
559 IF (l_log_error >= l_log_current_level) THEN
560 fnd_log.string
561 (
562 fnd_log.level_error,
563 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
564 'Call to WMS returned Expected Error'
565 );
566 END IF;
567 RAISE FND_API.G_EXC_ERROR;
568 END IF;
569
570 -- Check Error Message stack.
571 x_msg_count := FND_MSG_PUB.count_msg;
572 IF x_msg_count > 0
573 THEN
574 -- log the error
575 IF (l_log_error >= l_log_current_level) THEN
576 fnd_log.string
577 (
578 fnd_log.level_error,
579 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
580 'Call to WMS returned Errors in x_msg_count'
581 );
582 END IF;
583 RAISE FND_API.G_EXC_ERROR;
584 END IF;
585
586 -- Commit if p_commit = FND_API.G_TRUE
587 IF FND_API.TO_BOOLEAN(p_commit)
588 THEN
589 COMMIT WORK;
590 IF (l_log_statement >= l_log_current_level) THEN
591 fnd_log.string
592 (
593 fnd_log.level_statement,
594 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
595 'Committed'
596 );
597 END IF;
598 END IF;
599
600 -- Standard call to get message count and if count is 1, get message info
601 FND_MSG_PUB.count_and_get
602 (
603 p_count => x_msg_count,
604 p_data => x_msg_data,
605 p_encoded => FND_API.G_FALSE
606 );
607
608 -- Log API exit point
609 IF (l_log_procedure >= l_log_current_level)THEN
610 fnd_log.string
611 (
612 fnd_log.level_procedure,
613 l_debug_module||'.end',
614 'At the end of PLSQL procedure'
615 );
616 END IF;
620 x_return_status := FND_API.G_RET_STS_ERROR;
617 EXCEPTION
618 WHEN FND_API.G_EXC_ERROR THEN
619 ROLLBACK TO UPDATE_RESERVATION_PVT;
621 FND_MSG_PUB.count_and_get
622 (
623 p_count => x_msg_count,
624 p_data => x_msg_data,
625 p_encoded => FND_API.G_FALSE
626 );
627
628 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
629 ROLLBACK TO UPDATE_RESERVATION_PVT;
630 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
631 FND_MSG_PUB.count_and_get
632 (
633 p_count => x_msg_count,
634 p_data => x_msg_data,
635 p_encoded => FND_API.G_FALSE
636 );
637
638 WHEN OTHERS THEN
639 ROLLBACK TO UPDATE_RESERVATION_PVT;
640 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
641 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
642 THEN
643 FND_MSG_PUB.add_exc_msg
644 (
645 p_pkg_name => G_PKG_NAME,
646 p_procedure_name => 'update_reservation',
647 p_error_text => SUBSTR(SQLERRM,1,240)
648 );
649 END IF;
650 FND_MSG_PUB.count_and_get
651 (
652 p_count => x_msg_count,
653 p_data => x_msg_data,
654 p_encoded => FND_API.G_FALSE
655 );
656 END UPDATE_RESERVATION;
657
658 ---------------------------------------------------------------------------------------------------------------------
659 -- Declare Procedures --
660 ---------------------------------------------------------------------------------------------------------------------
661 -- Start of Comments --
662 -- Procedure name : DELETE_RESERVATION
663 -- Type : Private
664 -- Function : API to delete all the reservation made for a requirement
665 PROCEDURE DELETE_RESERVATION(
666 p_api_version IN NUMBER := 1.0,
667 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
668 p_commit IN VARCHAR2 := FND_API.G_FALSE,
669 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
670 p_module_type IN VARCHAR2,
671 x_return_status OUT NOCOPY VARCHAR2,
672 x_msg_count OUT NOCOPY NUMBER,
673 x_msg_data OUT NOCOPY VARCHAR2,
674 p_reservation_id IN NUMBER )
675 IS
676
677 -- Declare local variables
678 l_api_name CONSTANT VARCHAR2(30) := 'delete_reservation';
679 l_debug_module CONSTANT VARCHAR2(100) := 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name;
680
681 l_api_version CONSTANT NUMBER := 1.0;
682 l_init_msg_list VARCHAR2(1) := 'F';
683 l_return_status VARCHAR2(1);
684 l_msg_count NUMBER;
685 l_msg_data VARCHAR2(2000);
686
687
688 l_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
689 --dummy
690 l_serial_number_tbl inv_reservation_global.serial_number_tbl_type;
691
692 l_ret_value NUMBER;
693 l_reservation_id NUMBER;
694
695 l_scheduled_material_id NUMBER;
696
697 BEGIN
698 -- Standard start of API savepoint
699 SAVEPOINT DELETE_RESERVATION_PVT;
700
701 -- Initialize return status to success before any code logic/validation
702 x_return_status:= FND_API.G_RET_STS_SUCCESS;
703
704 -- Standard call to check for call compatibility
705 IF NOT FND_API.COMPATIBLE_API_CALL (l_api_version, p_api_version, l_api_name, G_PKG_NAME)
706 THEN
707 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
708 END IF;
709
710 -- Initialize message list if p_init_msg_list = FND_API.G_TRUE
711 IF FND_API.TO_BOOLEAN(p_init_msg_list)
712 THEN
713 FND_MSG_PUB.INITIALIZE;
714 END IF;
715
716 -- Log API entry point
717 IF (l_log_procedure >= l_log_current_level)THEN
718 fnd_log.string
719 (
720 fnd_log.level_procedure,
721 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||'.begin',
722 'At the start of PL SQL procedure '
723 );
724 END IF;
725
726 IF(p_module_type <> G_PKG_NAME ) THEN
727 -- Validate the reservation id
728 l_scheduled_material_id := validate_reservation_id(p_reservation_id);
729 IF (l_scheduled_material_id IS NULL) THEN
730 FND_MESSAGE.set_name( 'AHL', 'AHL_LTP_MULTI_ORG' );
731 FND_MSG_PUB.add;
732 -- log the error
733 IF (l_log_error >= l_log_current_level) THEN
734 fnd_log.string
735 (
736 fnd_log.level_error,
737 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||'.begin',
738 'Invalid Reservation ID'
739 );
740 END IF;
741 RAISE FND_API.G_EXC_ERROR;
742 END IF;
743 END IF;
744
745 -- Assign the reservation id to be deleted
746 l_rsv_rec.reservation_id := p_reservation_id;
747 -- Call the WMS api
748 inv_reservation_pub.delete_reservation
749 (
750 p_api_version_number => l_api_version,
751 p_init_msg_lst => l_init_msg_list,
752 x_return_status => l_return_status,
753 x_msg_count => l_msg_count,
754 x_msg_data => l_msg_data,
755 p_rsv_rec => l_rsv_rec,
756 p_serial_number => l_serial_number_tbl
757 );
758 IF (l_log_statement >= l_log_current_level) THEN
759 fnd_log.string(fnd_log.level_statement, l_debug_module,
760 'Returned from inv_reservation_pub.delete_reservation, l_return_status: ' || l_return_status);
761 END IF;
762 -- Check whether the return status is success, if not raise exception
763 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
764 -- log the error
765 IF (l_log_error >= l_log_current_level) THEN
766 fnd_log.string(fnd_log.level_error, l_debug_module,
767 'inv_reservation_pub.delete_reservation returned FND_API.G_RET_STS_UNEXP_ERROR');
768 END IF;
769 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
770 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
771 -- log the error
772 IF (l_log_error >= l_log_current_level) THEN
773 fnd_log.string(fnd_log.level_error, l_debug_module,
774 'inv_reservation_pub.delete_reservation returned FND_API.G_RET_STS_ERROR');
775 END IF;
776 RAISE FND_API.G_EXC_ERROR;
777 END IF;
778
779 -- Log API exit point
780 IF (l_log_procedure >= l_log_current_level)THEN
781 fnd_log.string
782 (
783 fnd_log.level_procedure,
784 l_debug_module||'.end',
785 'At the end of PLSQL procedure'
786 );
787 END IF;
788
789 -- Check Error Message stack.
790 x_msg_count := FND_MSG_PUB.count_msg;
791 IF x_msg_count > 0
792 THEN
793 RAISE FND_API.G_EXC_ERROR;
794 END IF;
795
796 -- Commit if p_commit = FND_API.G_TRUE
797 IF FND_API.TO_BOOLEAN(p_commit)
798 THEN
799 COMMIT WORK;
800 IF (l_log_statement >= l_log_current_level) THEN
801 fnd_log.string
802 (
803 fnd_log.level_statement,
804 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
805 'delete reservation COMMITTED'
806 );
807 END IF;
808 END IF;
809
810 -- Standard call to get message count and if count is 1, get message info
811 FND_MSG_PUB.count_and_get
812 (
813 p_count => x_msg_count,
814 p_data => x_msg_data,
815 p_encoded => FND_API.G_FALSE
816 );
817
818 EXCEPTION
819 WHEN FND_API.G_EXC_ERROR THEN
820 ROLLBACK TO DELETE_RESERVATION_PVT;
821 x_return_status := FND_API.G_RET_STS_ERROR;
822 FND_MSG_PUB.count_and_get
823 (
824 p_count => x_msg_count,
825 p_data => x_msg_data,
826 p_encoded => FND_API.G_FALSE
827 );
828
829 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
830 ROLLBACK TO DELETE_RESERVATION_PVT;
831 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
832 FND_MSG_PUB.count_and_get
833 (
834 p_count => x_msg_count,
835 p_data => x_msg_data,
836 p_encoded => FND_API.G_FALSE
837 );
838
839 WHEN OTHERS THEN
840 ROLLBACK TO DELETE_RESERVATION_PVT;
841 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
842 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
843 THEN
844 FND_MSG_PUB.add_exc_msg
845 (
846 p_pkg_name => G_PKG_NAME,
847 p_procedure_name => 'delete_reservation',
848 p_error_text => SUBSTR(SQLERRM,1,240)
849 );
850 END IF;
851 FND_MSG_PUB.count_and_get
852 (
853 p_count => x_msg_count,
854 p_data => x_msg_data,
855 p_encoded => FND_API.G_FALSE
856 );
857 END DELETE_RESERVATION;
858
859
860 ---------------------------------------------------------------------------------------------------------------------
861 -- Declare Procedures --
862 ---------------------------------------------------------------------------------------------------------------------
863 -- Start of Comments --
864 -- Procedure name : RELIEVE_RESERVATION
865 -- Type : Private
866 -- Function : API to delete a particular serial number from the reservation.
867 ---------------------------------------------------------------------------------------------------------------------
868 PROCEDURE RELIEVE_RESERVATION(
869 p_api_version IN NUMBER := 1.0,
870 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
871 p_commit IN VARCHAR2 := FND_API.G_FALSE,
872 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
873 p_module_type IN VARCHAR2,
874 x_return_status OUT NOCOPY VARCHAR2,
875 x_msg_count OUT NOCOPY NUMBER,
876 x_msg_data OUT NOCOPY VARCHAR2,
877 p_reservation_id IN NUMBER ,
878 p_serial_number_tbl IN serial_number_tbl_type)
879 IS
880 -- Declare local variables
881 l_api_name CONSTANT VARCHAR2(30) := 'relieve_reservation';
882 l_debug_module CONSTANT VARCHAR2(100) := 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name;
883
884 l_api_version CONSTANT NUMBER := 1.0;
885 l_init_msg_list VARCHAR2(1) := 'F';
886 l_return_status VARCHAR2(1);
887 l_msg_count NUMBER;
888 l_msg_data VARCHAR2(2000);
889 l_commit VARCHAR2(1) := 'F';
890
891 l_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
892 l_serial_number_tbl inv_reservation_global.serial_number_tbl_type;
893 l_reservation_id NUMBER;
894 l_reserved_quantity NUMBER;
895 l_x_primary_relieved_quantity NUMBER;
896 l_x_primary_remain_quantity NUMBER;
897 l_scheduled_material_id NUMBER;
898
899
900 BEGIN
901 -- Standard start of API savepoint
902 SAVEPOINT RELIEVE_RESERVATION_PVT;
903
904 -- Initialize return status to success before any code logic/validation
905 x_return_status:= FND_API.G_RET_STS_SUCCESS;
906
907 -- Standard call to check for call compatibility
908 IF NOT FND_API.COMPATIBLE_API_CALL (l_api_version, p_api_version, l_api_name, G_PKG_NAME)
909 THEN
910 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
911 END IF;
912
913 -- Initialize message list if p_init_msg_list = FND_API.G_TRUE
914 IF FND_API.TO_BOOLEAN(p_init_msg_list)
915 THEN
916 FND_MSG_PUB.INITIALIZE;
917 END IF;
918
919 -- Log API entry point
920 IF (l_log_procedure >= l_log_current_level)THEN
921 fnd_log.string
922 (
923 fnd_log.level_procedure,
924 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||'.begin',
925 'At the start of PL SQL procedure '
926 );
927 END IF;
928
929 --Convert to Inv rec type
930
931 CONVERT_TO_INV_TABLE_TYPE(p_mtl_reservation_rec => null,
932 p_serial_number_tbl => p_serial_number_tbl,
933 x_mtl_reservation_rec => l_rsv_rec, -- dummy
934 x_serial_number_tbl => l_serial_number_tbl);
935
936 IF(p_module_type <> G_PKG_NAME ) THEN
937 -- Validate the reservation id
938 l_scheduled_material_id := validate_reservation_id(p_reservation_id);
939 IF (l_scheduled_material_id IS NULL) THEN
940 FND_MESSAGE.set_name( 'AHL', 'AHL_LTP_MULTI_ORG' );
941 FND_MSG_PUB.add;
942 -- log the error
943 IF (l_log_error >= l_log_current_level) THEN
944 fnd_log.string
945 (
946 fnd_log.level_error,
947 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||'.begin',
948 'Invalid Reservation ID'
949 );
950 END IF;
951 RAISE FND_API.G_EXC_ERROR;
952 END IF;
953
954 -- Get the Material Requirements details
955 OPEN get_mtl_req_dtls_csr(l_scheduled_material_id);
956 FETCH get_mtl_req_dtls_csr INTO l_mtl_req_dtls_rec;
957 -- If the details are not found then raise exception
958 IF get_mtl_req_dtls_csr%NOTFOUND THEN
959 FND_MESSAGE.set_name( 'AHL', 'AHL_RSV_INVLD_MAT_REQ' );
960 FND_MSG_PUB.add;
961 CLOSE get_mtl_req_dtls_csr;
962 RAISE FND_API.G_EXC_ERROR;
963 END IF; -- IF get_mtl_req_dtls_csr%NOTFOUND
964 CLOSE get_mtl_req_dtls_csr;
965
966 -- Validate the Serial Number
967 IF (l_serial_number_tbl.COUNT < 1) THEN
968 FND_MESSAGE.set_name( 'AHL', 'AHL_RSV_INVLD_SL_NUM' );
969 FND_MSG_PUB.add;
970 IF (l_log_error>= l_log_current_level)THEN
971 fnd_log.string
972 (
973 fnd_log.level_error,
974 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
975 'l_serial_number_tbl is null'
976 );
977 END IF;
978 RAISE FND_API.G_EXC_ERROR;
979 END IF; -- IF l_serial_number_tbl IS NULL
980
981 -- For all the serial numbers that need to be reserved
982 FOR i IN l_serial_number_tbl.FIRST..l_serial_number_tbl.LAST
983 LOOP
984 l_serial_number_tbl(i).inventory_item_id := l_mtl_req_dtls_rec.inventory_item_id;
985 IF (l_log_statement >= l_log_current_level) THEN
986 fnd_log.string(fnd_log.level_statement,'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
987 'Serial Number '||i||' to be Deleted: ' || l_serial_number_tbl(i).serial_number);
988 END IF;
989 END LOOP;
990
991 IF (l_log_statement >= l_log_current_level) THEN
992 fnd_log.string(fnd_log.level_statement,'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
996 fnd_log.string(fnd_log.level_statement,'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
993 'l_mtl_req_dtls_rec.inventory_item_id:' ||l_mtl_req_dtls_rec.inventory_item_id );
994 fnd_log.string(fnd_log.level_statement,'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
995 'l_mtl_req_dtls_rec.organization_id:' || l_mtl_req_dtls_rec.organization_id );
997 'l_scheduled_material_id:' || l_scheduled_material_id );
998 END IF;
999 END IF; -- IF(p_module_type <> G_PKG_NAME ) THEN
1000
1001 l_rsv_rec.reservation_id := p_reservation_id;
1002
1003 inv_reservation_pub.relieve_reservation(
1004 p_api_version_number => l_api_version,
1005 p_init_msg_lst => l_init_msg_list,
1006 x_return_status => l_return_status,
1007 x_msg_count => l_msg_count,
1008 x_msg_data => l_msg_data,
1009 p_rsv_rec => l_rsv_rec,
1010 p_primary_relieved_quantity => l_serial_number_tbl.COUNT,
1011 p_relieve_all => fnd_api.g_false,
1012 p_original_serial_number => l_serial_number_tbl,
1013 x_primary_relieved_quantity => l_x_primary_relieved_quantity,
1014 x_primary_remain_quantity => l_x_primary_remain_quantity
1015 );
1016
1017 -- Check for the returned status from these APIs
1018 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1019 IF (l_log_error >= l_log_current_level) THEN
1020 fnd_log.string
1021 (
1022 fnd_log.level_error,
1023 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
1024 'After Relieve/Delete FND_API.G_EXC_UNEXPECTED_ERROR'
1025 );
1026 END IF;
1027 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1028 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
1029 IF (l_log_error >= l_log_current_level) THEN
1030 fnd_log.string
1031 (
1032 fnd_log.level_error,
1033 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
1034 'After Relieve/Delete FND_API.G_EXC_ERROR'
1035 );
1036 END IF;
1037 RAISE FND_API.G_EXC_ERROR;
1038 END IF;
1039
1040 -- Log API exit point
1041 IF (l_log_procedure >= l_log_current_level)THEN
1042 fnd_log.string
1043 (
1044 fnd_log.level_procedure,
1045 l_debug_module||'.end',
1046 'At the end of PLSQL procedure x_primary_relieved_quantity :=' ||l_x_primary_relieved_quantity||' x_primary_remain_quantity:= '||l_x_primary_remain_quantity
1047 );
1048 END IF;
1049
1050 -- Check Error Message stack.
1051 x_msg_count := FND_MSG_PUB.count_msg;
1052 IF x_msg_count > 0
1053 THEN
1054 RAISE FND_API.G_EXC_ERROR;
1055 END IF;
1056
1057 -- Commit if p_commit = FND_API.G_TRUE
1058 IF FND_API.TO_BOOLEAN(p_commit)
1059 THEN
1060 COMMIT WORK;
1061 END IF;
1062
1063 -- Standard call to get message count and if count is 1, get message info
1064 FND_MSG_PUB.count_and_get
1065 (
1066 p_count => x_msg_count,
1067 p_data => x_msg_data,
1068 p_encoded => FND_API.G_FALSE
1069 );
1070
1071 EXCEPTION
1072 WHEN FND_API.G_EXC_ERROR THEN
1073 ROLLBACK TO RELIEVE_RESERVATION_PVT;
1074 x_return_status := FND_API.G_RET_STS_ERROR;
1075 FND_MSG_PUB.count_and_get
1076 (
1077 p_count => x_msg_count,
1078 p_data => x_msg_data,
1079 p_encoded => FND_API.G_FALSE
1080 );
1081
1082 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1083 ROLLBACK TO RELIEVE_RESERVATION_PVT;
1084 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1085 FND_MSG_PUB.count_and_get
1086 (
1087 p_count => x_msg_count,
1088 p_data => x_msg_data,
1089 p_encoded => FND_API.G_FALSE
1090 );
1091
1092 WHEN OTHERS THEN
1093 ROLLBACK TO RELIEVE_RESERVATION_PVT;
1094 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1095 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1096 THEN
1097 FND_MSG_PUB.add_exc_msg
1098 (
1099 p_pkg_name => G_PKG_NAME,
1100 p_procedure_name => 'relieve_reservation',
1101 p_error_text => SUBSTR(SQLERRM,1,240)
1102 );
1103 END IF;
1104 FND_MSG_PUB.count_and_get
1105 (
1106 p_count => x_msg_count,
1107 p_data => x_msg_data,
1108 p_encoded => FND_API.G_FALSE
1109 );
1110 END RELIEVE_RESERVATION;
1111
1112 ---------------------------------------------------------------------------------------------------------------------
1113 -- Declare Procedures --
1114 ---------------------------------------------------------------------------------------------------------------------
1115 -- Start of Comments --
1116 -- Procedure name : TRANSFER_RESERVATION
1117 -- Type : Private
1118 -- Function : API to change the demand/supply source type
1119 PROCEDURE TRANSFER_RESERVATION(
1120 p_api_version IN NUMBER := 1.0,
1121 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1122 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1123 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1124 p_module_type IN VARCHAR2,
1125 x_return_status OUT NOCOPY VARCHAR2,
1126 x_msg_count OUT NOCOPY NUMBER,
1127 x_msg_data OUT NOCOPY VARCHAR2,
1128 p_from_reservation_id IN NUMBER,
1129 p_to_rsv_rec IN mtl_reservation_rec_type,
1130 p_serial_number_tbl IN serial_number_tbl_type,
1131 x_to_reservation_id OUT NOCOPY NUMBER)
1132 IS
1133 -- Declare local variables
1134 l_api_name CONSTANT VARCHAR2(30) := 'transfer_reservation';
1135
1136 l_api_version CONSTANT NUMBER := 1.0;
1137 l_init_msg_list VARCHAR2(1) := 'F';
1138 l_return_status VARCHAR2(1);
1139 l_msg_count NUMBER;
1140 l_msg_data VARCHAR2(2000);
1141
1142 l_debug_module CONSTANT VARCHAR2(100) := 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name;
1143
1144 CURSOR get_subinv_locator_csr(c_rsrv_id NUMBER)
1145 IS
1146 SELECT subinventory_code,locator_id
1147 FROM MTL_RESERVATIONS
1148 WHERE reservation_id = c_rsrv_id;
1149
1150 l_reservation_id NUMBER;
1151 l_wip_entity_id NUMBER;
1152 l_rsv_rec mtl_reservation_rec_type := p_to_rsv_rec;
1153 l_serial_number_tbl serial_number_tbl_type := p_serial_number_tbl;
1154 l_from_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
1155 l_to_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
1156 l_from_serial_number_tbl inv_reservation_global.serial_number_tbl_type ; -- dummy
1157 l_to_serial_number_tbl inv_reservation_global.serial_number_tbl_type ;
1158 l_x_to_reservation_id NUMBER;
1159 l_subinventory_name VARCHAR2(10);
1160 l_locator_id NUMBER;
1161 l_scheduled_material_id NUMBER;
1162
1163
1164 BEGIN
1165 -- Standard start of API savepoint
1166 SAVEPOINT TRANSFER_RESERVATION_PVT;
1167
1168 -- Initialize return status to success before any code logic/validation
1169 x_return_status:= FND_API.G_RET_STS_SUCCESS;
1170
1171 -- Standard call to check for call compatibility
1172 IF NOT FND_API.COMPATIBLE_API_CALL (l_api_version, p_api_version, l_api_name, G_PKG_NAME)
1173 THEN
1174 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1175 END IF;
1176
1177 -- Initialize message list if p_init_msg_list = FND_API.G_TRUE
1178 IF FND_API.TO_BOOLEAN(p_init_msg_list)
1179 THEN
1180 FND_MSG_PUB.INITIALIZE;
1181 END IF;
1182
1183 l_from_rsv_rec.reservation_id := p_from_reservation_id;
1184
1185
1186
1187 -- Log API entry point
1188 IF (l_log_procedure >= l_log_current_level)THEN
1189 fnd_log.string
1190 (
1191 fnd_log.level_procedure,
1192 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||'.begin',
1193 'At the start of PL SQL procedure : demand ASM_ID = '|| l_rsv_rec.demand_source_line_detail|| ' to_reservation_id = '|| l_rsv_rec.reservation_id
1194 );
1195 END IF;
1196
1197
1198 IF(p_module_type <> G_PKG_NAME ) THEN
1199 -- Validate to make sure that the p_schedule_material_id is not empty
1200 IF(l_rsv_rec.demand_source_line_detail IS NULL AND l_rsv_rec.reservation_id IS NULL) THEN
1201 IF (l_log_statement >= l_log_current_level) THEN
1202 fnd_log.string(
1203 fnd_log.level_statement,
1204 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
1205 'p_schedule_material_id is null for demand, p_schedule_material_id:' ||l_rsv_rec.demand_source_line_detail);
1206 END IF;
1207 FND_MESSAGE.set_name( 'AHL', 'AHL_MM_INV_REQ' );
1208 FND_MSG_PUB.add;
1209 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1210 RETURN;
1211 END IF;
1212 -- Validate the reservation id
1213 l_scheduled_material_id := validate_reservation_id(p_from_reservation_id);
1214 IF (l_scheduled_material_id IS NULL) THEN
1215 FND_MESSAGE.set_name( 'AHL', 'AHL_LTP_MULTI_ORG' );
1216 FND_MSG_PUB.add;
1217 -- log the error
1218 IF (l_log_error >= l_log_current_level) THEN
1219 fnd_log.string
1220 (
1221 fnd_log.level_error,
1222 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||'.begin',
1223 'Invalid Reservation ID'
1224 );
1225 END IF;
1226 RAISE FND_API.G_EXC_ERROR;
1227 END IF;
1228
1229 -- Get the Material Requirements details
1230 OPEN get_mtl_req_dtls_csr(l_scheduled_material_id);
1231 FETCH get_mtl_req_dtls_csr INTO l_mtl_req_dtls_rec;
1232 -- If the details are not found then raise exception
1233 IF get_mtl_req_dtls_csr%NOTFOUND THEN
1234 FND_MESSAGE.set_name( 'AHL', 'AHL_RSV_INVLD_MAT_REQ' );
1235 FND_MSG_PUB.add;
1236 CLOSE get_mtl_req_dtls_csr;
1237 RAISE FND_API.G_EXC_ERROR;
1238 END IF; -- IF get_mtl_req_dtls_csr%NOTFOUND
1239 CLOSE get_mtl_req_dtls_csr;
1240
1241 IF (l_serial_number_tbl.COUNT > 0)THEN
1242 -- For all the serial numbers that needs to be reserved
1243 FOR i IN l_serial_number_tbl.FIRST..l_serial_number_tbl.LAST
1244 LOOP
1245 l_serial_number_tbl(i).inventory_item_id := l_mtl_req_dtls_rec.inventory_item_id;
1246 IF (l_log_statement >= l_log_current_level) THEN
1247 fnd_log.string(fnd_log.level_statement,'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
1248 'Serial Number '||i||' to be reserved: ' || l_serial_number_tbl(i).serial_number);
1249 END IF;
1250 END LOOP;
1251 END IF;
1252 END IF;
1253
1254 -- Create new record
1255 IF(l_rsv_rec.reservation_id IS NULL OR l_rsv_rec.reservation_id = FND_API.G_MISS_NUM) THEN
1256 -- Initialize the record to be send to the WMS package
1257 Initialize_create_rec( l_rsv_rec.demand_source_line_detail, -- the schedule material id,
1258 l_rsv_rec, -- record to be passed ti the WMS packages
1259 l_return_status); -- return status
1260
1261
1262 IF(l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
1263 IF (l_log_error >= l_log_current_level) THEN
1264 fnd_log.string(
1265 fnd_log.level_error,
1266 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
1267 'Call to Initialize_create_rec returned Unexpected Error');
1268 END IF;
1269 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1270 END IF;
1271 ELSE -- Fetch rec from reservation
1272
1273 Initialize_rec_from_resv(l_rsv_rec.reservation_id, -- reservation_id
1274 l_rsv_rec); -- resv rec
1275 END IF;
1276
1277 -- Considering l_serial_number_tbl table has all the items to be transferred.
1278 IF(l_rsv_rec.primary_reservation_quantity = FND_API.G_MISS_NUM) THEN
1279 IF(l_serial_number_tbl.COUNT > 0) THEN
1280 l_rsv_rec.primary_reservation_quantity := l_serial_number_tbl.COUNT;
1281 END IF;
1282 END IF;
1283
1284 --rec type
1285 IF (l_log_statement >= l_log_current_level) THEN
1286 fnd_log.string
1287 (
1288 fnd_log.level_statement,'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
1289 'Before Converting'
1290 );
1291 fnd_log.string
1292 (
1293 fnd_log.level_statement,'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
1294 'l_rsv_rec.demand_source_type_id' || l_rsv_rec.demand_source_type_id
1295 );
1296 fnd_log.string
1297 (
1298 fnd_log.level_statement,'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
1299 'l_rsv_rec.reservation_id' || l_rsv_rec.reservation_id
1300 );
1301 fnd_log.string
1302 (
1303 fnd_log.level_statement,'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
1304 'l_rsv_rec.primary_reservation_quantity' || l_rsv_rec.primary_reservation_quantity
1305 );
1306 fnd_log.string
1307 (
1308 fnd_log.level_statement,'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
1309 'l_from_rsv_rec.reservation_id' || l_from_rsv_rec.reservation_id
1310 );
1311 fnd_log.string
1312 (
1313 fnd_log.level_statement,'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
1314 'l_rsv_rec.inventory_item_id' || l_rsv_rec.inventory_item_id
1315 );
1316 fnd_log.string
1317 (
1318 fnd_log.level_statement,'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
1319 'l_rsv_rec.subinventory_code: ' || l_rsv_rec.subinventory_code|| ' and l_rsv_rec.locator_id: '||l_rsv_rec.locator_id
1320 );
1321 END IF;
1322
1323 CONVERT_TO_INV_TABLE_TYPE(p_mtl_reservation_rec => l_rsv_rec,
1324 p_serial_number_tbl => l_serial_number_tbl,
1325 x_mtl_reservation_rec => l_to_rsv_rec,
1326 x_serial_number_tbl => l_to_serial_number_tbl);
1327
1328
1329 IF (l_log_statement >= l_log_current_level) THEN
1330 fnd_log.string
1331 (
1332 fnd_log.level_statement,'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
1333 'Before Calling inv_reservation_pub.transfer_reservation'
1334 );
1335 fnd_log.string
1336 (
1337 fnd_log.level_statement,'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
1338 'l_to_rsv_rec.demand_source_type_id' || l_to_rsv_rec.demand_source_type_id
1339 );
1340 fnd_log.string
1341 (
1342 fnd_log.level_statement,'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
1343 'l_to_rsv_rec.reservation_id' || l_to_rsv_rec.reservation_id
1344 );
1345 fnd_log.string
1346 (
1347 fnd_log.level_statement,'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
1348 'l_to_rsv_rec.demand_source_line_detail' || l_to_rsv_rec.demand_source_line_detail
1349 );
1350 fnd_log.string
1351 (
1352 fnd_log.level_statement,'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
1353 'l_from_rsv_rec.reservation_id' || l_from_rsv_rec.reservation_id
1354 );
1355 fnd_log.string
1356 (
1357 fnd_log.level_statement,'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
1358 'From Rec scheduled_material_id' || l_scheduled_material_id
1359 );
1360 fnd_log.string
1361 (
1362 fnd_log.level_statement,'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
1363 'l_to_rsv_rec.subinventory_code: ' || l_to_rsv_rec.subinventory_code|| ' and l_to_rsv_rec.locator_id: '||l_to_rsv_rec.locator_id
1364 );
1365 END IF;
1366
1367
1368 -- Call the WMS Transfer Reservaion API
1369 inv_reservation_pub.transfer_reservation
1370 (
1371 p_api_version_number => l_api_version,
1372 p_init_msg_lst => l_init_msg_list,
1373 x_return_status => l_return_status,
1374 x_msg_count => l_msg_count,
1375 x_msg_data => l_msg_data,
1376 p_is_transfer_supply => fnd_api.g_false,
1377 p_original_rsv_rec => l_from_rsv_rec,
1378 p_to_rsv_rec => l_to_rsv_rec,
1379 p_original_serial_number=> l_from_serial_number_tbl,
1380 p_to_serial_number => l_to_serial_number_tbl,
1381 x_to_reservation_id => l_x_to_reservation_id
1382 );
1383
1384 -- Check for the returned status from these APIs
1385 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1386 IF (l_log_error >= l_log_current_level) THEN
1387 fnd_log.string
1388 (
1389 fnd_log.level_error,
1390 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
1391 'inv_reservation_pub.transfer_reservation returned FND_API.G_EXC_ERROR'
1392 );
1393 END IF;
1394 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1395 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
1396 IF (l_log_error >= l_log_current_level) THEN
1397 fnd_log.string
1398 (
1399 fnd_log.level_error,
1400 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
1401 'inv_reservation_pub.transfer_reservation returned FND_API.G_RET_STS_ERROR'
1402 );
1403 END IF;
1404 RAISE FND_API.G_EXC_ERROR;
1405 END IF;
1406 l_x_to_reservation_id := x_to_reservation_id;
1407
1408 -- Log API exit point
1409 IF (l_log_procedure >= l_log_current_level)THEN
1410 fnd_log.string
1411 (
1412 fnd_log.level_procedure,
1413 l_debug_module||'.end',
1414 'At the end of PLSQL procedure'
1415 );
1416 END IF;
1417
1418 -- Check Error Message stack.
1419 x_msg_count := FND_MSG_PUB.count_msg;
1420 IF x_msg_count > 0
1421 THEN
1422 RAISE FND_API.G_EXC_ERROR;
1423 END IF;
1424
1425 -- Commit if p_commit = FND_API.G_TRUE
1426 IF FND_API.TO_BOOLEAN(p_commit)
1427 THEN
1428 COMMIT WORK;
1429 END IF;
1430
1431 -- Standard call to get message count and if count is 1, get message info
1432 FND_MSG_PUB.count_and_get
1433 (
1434 p_count => x_msg_count,
1435 p_data => x_msg_data,
1436 p_encoded => FND_API.G_FALSE
1437 );
1438
1439 EXCEPTION
1440 WHEN FND_API.G_EXC_ERROR THEN
1441 ROLLBACK TO TRANSFER_RESERVATION_PVT;
1442 x_return_status := FND_API.G_RET_STS_ERROR;
1443 FND_MSG_PUB.count_and_get
1444 (
1445 p_count => x_msg_count,
1446 p_data => x_msg_data,
1447 p_encoded => FND_API.G_FALSE
1448 );
1449
1450 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1451 ROLLBACK TO TRANSFER_RESERVATION_PVT;
1452 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1453 FND_MSG_PUB.count_and_get
1454 (
1455 p_count => x_msg_count,
1456 p_data => x_msg_data,
1457 p_encoded => FND_API.G_FALSE
1458 );
1459
1460 WHEN OTHERS THEN
1461 ROLLBACK TO TRANSFER_RESERVATION_PVT;
1462 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1463 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1464 THEN
1465 FND_MSG_PUB.add_exc_msg
1466 (
1467 p_pkg_name => G_PKG_NAME,
1468 p_procedure_name => 'transfer_reservation',
1469 p_error_text => SUBSTR(SQLERRM,1,240)
1470 );
1471 END IF;
1472 FND_MSG_PUB.count_and_get
1476 p_encoded => FND_API.G_FALSE
1473 (
1474 p_count => x_msg_count,
1475 p_data => x_msg_data,
1477 );
1478 END TRANSFER_RESERVATION;
1479
1480
1481 ------------------------------------------------------------------------------------------------------------------
1482 -- Start of Comments --
1483 -- Procedure name : PROCESS_RESERVATION
1484 -- Type : Private
1485 -- Function : Reserves the serial numbers in the p_serial_number_tbl
1486 PROCEDURE PROCESS_RESERVATION(
1487 p_api_version IN NUMBER := 1.0,
1488 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1489 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1490 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1491 p_module_type IN VARCHAR2,
1492 x_return_status OUT NOCOPY VARCHAR2,
1493 x_msg_count OUT NOCOPY NUMBER,
1494 x_msg_data OUT NOCOPY VARCHAR2,
1495 p_rsv_rec IN mtl_reservation_rec_type,
1496 p_serial_number_tbl IN serial_number_tbl_type,
1497 x_reservation_id_tbl OUT NOCOPY number_table_type)
1498 IS
1499 -- Declare local variables
1500 l_api_name CONSTANT VARCHAR2(30) := 'PROCESS_RESERVATION';
1501 l_api_version CONSTANT NUMBER := 1.0;
1502 l_init_msg_list VARCHAR2(1) := 'F';
1503 l_return_status VARCHAR2(1);
1504 l_msg_count NUMBER;
1505 l_msg_data VARCHAR2(2000);
1506 l_debug_module CONSTANT VARCHAR2(100) := 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name;
1507
1508
1509 CURSOR get_subinv_locator_csr(c_serial_number IN VARCHAR2,c_inventory_item_id IN NUMBER, c_org_id IN NUMBER)
1510 IS
1511 SELECT current_subinventory_code,current_locator_id,lot_number,revision,reservation_id
1512 FROM mtl_serial_numbers
1513 WHERE serial_number = c_serial_number
1514 AND inventory_item_id = c_inventory_item_id
1515 AND current_organization_id = c_org_id;
1516
1517
1518 l_create_rsv_rec mtl_reservation_rec_type := p_rsv_rec;
1519 l_serial_number_tbl serial_number_tbl_type := p_serial_number_tbl;
1520 l_filter_srl_number_tbl serial_number_tbl_type;
1521 l_x_quantity_reserved NUMBER;
1522 l_scheduled_material_id NUMBER := p_rsv_rec.demand_source_line_detail;
1523 l_sub_inv_code_tbl varchar_table_type;
1524 l_locator_id_tbl number_table_type;
1525 l_revision_tbl varchar_table_type;
1526 l_lot_number_tbl varchar_table_type;
1527 x_reservation_id NUMBER;
1528 l_x_reservation_id NUMBER;
1529 l_reservation_id_tbl number_table_type;
1530 l_index NUMBER;
1531 l_index_tbl number_table_type;
1532 l_sub_inv_code VARCHAR2(10);
1533 l_fetch_sub_inv_code VARCHAR2(10);
1534 l_locator_id NUMBER;
1535 l_fetch_locator_id NUMBER;
1536 l_revision VARCHAR2(3);
1537 l_fetch_revision VARCHAR2(3);
1538 l_lot_number VARCHAR2(80);
1539 l_fetch_lot_number VARCHAR2(80);
1540 l_reservation_id NUMBER;
1541
1542 BEGIN
1543 -- Standard start of API savepoint
1544 SAVEPOINT PROCESS_RESERVATION_PVT;
1545 -- Initialize return status to success before any code logic/validation
1546 x_return_status:= FND_API.G_RET_STS_SUCCESS;
1547
1548
1549 -- Standard call to check for call compatibility
1550 IF NOT FND_API.COMPATIBLE_API_CALL (l_api_version, p_api_version, l_api_name, G_PKG_NAME)
1551 THEN
1552 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1553 END IF;
1554
1555
1556 -- Initialize message list if p_init_msg_list = FND_API.G_TRUE
1557 IF FND_API.TO_BOOLEAN(p_init_msg_list)
1558 THEN
1559 FND_MSG_PUB.INITIALIZE;
1560 END IF;
1561
1562 -- Log API entry point
1563 IF (l_log_procedure >= l_log_current_level) THEN
1564 fnd_log.string
1565 (
1566 fnd_log.level_procedure,
1567 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||'.begin',
1568 'At the start of PL SQL procedure scheduled_material_id : ' || l_scheduled_material_id ||
1569 'total serial_number count: '||p_serial_number_tbl.COUNT
1570 );
1571 END IF;
1572
1573 -- Get the Material Requirements details
1574 OPEN get_mtl_req_dtls_csr(l_scheduled_material_id);
1575 FETCH get_mtl_req_dtls_csr INTO l_mtl_req_dtls_rec;
1576 IF get_mtl_req_dtls_csr%NOTFOUND THEN
1577 FND_MESSAGE.set_name( 'AHL', 'AHL_RSV_INVLD_MAT_REQ' );
1578 FND_MSG_PUB.add;
1579 -- log the error
1580 IF (l_log_error >= l_log_current_level) THEN
1581 fnd_log.string
1582 (
1583 fnd_log.level_error,
1584 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
1585 'get_mtl_req_dtls_csr, did not fetch any records'
1586 );
1587 END IF;
1588 CLOSE get_mtl_req_dtls_csr;
1589 RAISE FND_API.G_EXC_ERROR;
1590 END IF; -- Material Requirement details not found
1591 CLOSE get_mtl_req_dtls_csr;
1592
1593 -- Validate whether the sum of already issued + reserved quantities is not more than the initially requested quantity
1594 IF l_mtl_req_dtls_rec.completed_quantity +
1595 l_mtl_req_dtls_rec.reserved_quantity >= l_mtl_req_dtls_rec.requested_quantity THEN
1596 FND_MESSAGE.set_name( 'AHL', 'AHL_RSV_QTY_EXCDS_REQSTD' );
1597 FND_MSG_PUB.add;
1598 -- log the error
1599 IF (l_log_error >= l_log_current_level) THEN
1600 fnd_log.string
1601 (
1602 fnd_log.level_error,
1603 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
1607 RAISE FND_API.G_EXC_ERROR;
1604 'Already Completed Qty + Reserved Qty is equal or more than Requested Qty '
1605 );
1606 END IF;
1608 END IF;
1609
1610
1611
1612 IF(l_create_rsv_rec.supply_source_type_id = inv_reservation_global.g_source_type_inv) THEN
1613
1614 -- If reservation Id passed, then call Update reservation directly.
1615 -- For the case when we want to update reservation from IR to Inv
1616 IF(l_create_rsv_rec.reservation_id IS NOT NULL AND l_create_rsv_rec.reservation_id <> FND_API.G_MISS_NUM)
1617 THEN
1618 IF (l_log_statement >= l_log_current_level) THEN
1619 fnd_log.string
1620 (
1621 fnd_log.level_statement,
1622 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
1623 'Calling AHL_MM_RESERVATIONS_PVT.UPDATE_RESERVATION : '
1624 );
1625 END IF;
1626
1627 AHL_MM_RESERVATIONS_PVT.UPDATE_RESERVATION
1628 (
1629 p_api_version => l_api_version,
1630 p_init_msg_list => l_init_msg_list,
1631 p_commit => FND_API.G_FALSE,
1632 p_validation_level => FND_API.G_VALID_LEVEL_FULL, -- the validation level
1633 p_module_type => G_PKG_NAME,
1634 x_return_status => l_return_status,
1635 x_msg_count => l_msg_count,
1636 x_msg_data => l_msg_data,
1637 p_reservation_id => l_create_rsv_rec.reservation_id,
1638 p_rsv_rec => l_create_rsv_rec,
1639 p_serial_number_tbl => l_serial_number_tbl
1640 );
1641
1642 IF (l_log_statement >= l_log_current_level) THEN
1643 fnd_log.string
1644 (
1645 fnd_log.level_statement,
1646 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
1647 'After Calling AHL_MM_RESERVATIONS_PVT.UPDATE_RESERVATION: P_RESERVATION_ID := '|| l_x_reservation_id || ' and l_return_status = '||l_return_status
1648 );
1649 END IF;
1650
1651 -- Check the error status
1652 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1653 -- log the error
1654 IF (l_log_error >= l_log_current_level) THEN
1655 fnd_log.string
1656 (
1657 fnd_log.level_error,
1658 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
1659 'Call to AHL_MM_RESERVATIONS_PVT.UPDATE_RESERVATION returned Unexpected Error'
1660 );
1661 END IF;
1662 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1663 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
1664 -- log the error
1665 IF (l_log_error >= l_log_current_level) THEN
1666 fnd_log.string
1667 (
1668 fnd_log.level_error,
1669 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
1670 'Call to AHL_MM_RESERVATIONS_PVT.UPDATE_RESERVATION returned Expected Error'
1671 );
1672 END IF;
1673 --RAISE at the end of the API FND_API.G_EXC_ERROR;
1674 END IF;
1675
1676 ELSE -- Check for Create/Update/Transfer
1677
1678 -- Validate to make sure that the Serial Number is not empty, when Supply is Inventory.
1679 IF (l_serial_number_tbl.COUNT < 1) THEN
1680 FND_MESSAGE.set_name( 'AHL', 'AHL_PP_SERIAL_MISSING' );
1681 FND_MSG_PUB.add;
1682 -- log the error
1683 IF (l_log_error >= l_log_current_level) THEN
1684 fnd_log.string
1685 (
1686 fnd_log.level_error,
1687 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
1688 'Serial Number passed to the API is null'
1689 );
1690 END IF;
1691 RAISE FND_API.G_EXC_ERROR;
1692 END IF;
1693
1694 -- For all the serial numbers that need to be reserved
1695 FOR i IN l_serial_number_tbl.FIRST..l_serial_number_tbl.LAST LOOP
1696 l_serial_number_tbl(i).inventory_item_id := l_mtl_req_dtls_rec.inventory_item_id;
1697
1698 OPEN get_subinv_locator_csr(l_serial_number_tbl(i).serial_number,l_mtl_req_dtls_rec.inventory_item_id,l_mtl_req_dtls_rec.organization_id);
1699 FETCH get_subinv_locator_csr INTO l_sub_inv_code_tbl(i),l_locator_id_tbl(i),l_lot_number_tbl(i),l_revision_tbl(i),l_reservation_id;
1700 CLOSE get_subinv_locator_csr;
1701
1702 -- First check for transfer
1703 IF(l_reservation_id IS NOT NULL) THEN
1704
1705 l_filter_srl_number_tbl(1).serial_number := l_serial_number_tbl(i).serial_number;
1706 l_filter_srl_number_tbl(1).inventory_item_id := l_mtl_req_dtls_rec.inventory_item_id;
1707
1708
1709 AHL_MM_RESERVATIONS_PVT.RELIEVE_RESERVATION(
1710 p_api_version => l_api_version,
1711 p_init_msg_list => l_init_msg_list,
1712 p_commit => FND_API.G_FALSE,
1713 p_validation_level => FND_API.G_VALID_LEVEL_FULL, -- the validation level
1714 p_module_type => G_PKG_NAME,
1715 x_return_status => l_return_status,
1716 x_msg_count => l_msg_count,
1717 X_MSG_DATA => L_MSG_DATA,
1718 p_reservation_id => l_reservation_id,
1719 p_serial_number_tbl => l_filter_srl_number_tbl);
1720
1721 IF (l_log_statement >= l_log_current_level) THEN
1722 fnd_log.string
1723 (
1724 fnd_log.level_statement,
1725 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
1726 'After Calling AHL_MM_RESERVATIONS_PVT.RELIEVE_RESERVATION: and l_return_status = '||l_return_status
1727 );
1728 END IF;
1729
1730 -- Check the error status
1731 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1732 -- log the error
1733 IF (l_log_error >= l_log_current_level) THEN
1734 fnd_log.string
1735 (
1736 fnd_log.level_error,
1737 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
1738 'Call to AHL_MM_RESERVATIONS_PVT.RELIEVE_RESERVATION returned Unexpected Error'
1739 );
1740 END IF;
1741 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1742 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
1743 -- log the error
1744 IF (l_log_error >= l_log_current_level) THEN
1745 fnd_log.string
1746 (
1747 fnd_log.level_error,
1748 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
1749 'Call to AHL_MM_RESERVATIONS_PVT.RELIEVE_RESERVATION returned Expected Error'
1750 );
1751 END IF;
1752 RAISE FND_API.G_EXC_ERROR;
1753 END IF;
1754
1755 END IF;
1756 l_reservation_id := null;
1757 END LOOP;
1758
1759 IF(l_sub_inv_code_tbl.COUNT > 0) THEN
1760 FOR i IN l_sub_inv_code_tbl.FIRST..l_sub_inv_code_tbl.LAST
1761 LOOP
1762 IF(l_sub_inv_code_tbl.EXISTS(i)) THEN
1763 l_sub_inv_code := l_sub_inv_code_tbl(i);
1764 l_locator_id := l_locator_id_tbl(i);
1765 l_lot_number := l_lot_number_tbl(i);
1766 l_revision := l_revision_tbl(i);
1767 IF (l_log_statement >= l_log_current_level) THEN
1768 fnd_log.string
1769 (
1770 fnd_log.level_statement,
1771 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
1772 '1st sub inv : = '|| l_sub_inv_code
1773 );
1774 END IF;
1775 l_index := 1;
1776 FOR j IN i..l_sub_inv_code_tbl.LAST
1777 LOOP
1778
1779 IF(l_sub_inv_code_tbl.EXISTS(j)) THEN
1780 l_fetch_sub_inv_code := l_sub_inv_code_tbl(j);
1781 l_fetch_locator_id := l_locator_id_tbl(j);
1782 l_fetch_lot_number := l_lot_number_tbl(j);
1783 l_fetch_revision := l_revision_tbl(j);
1784
1785 IF (l_sub_inv_code = l_fetch_sub_inv_code ) THEN
1786 IF(nvl(l_locator_id,-1) = nvl(l_fetch_locator_id,-1) AND
1787 nvl(l_lot_number,'@@@') = nvl(l_fetch_lot_number,'@@@') AND
1788 nvl(l_revision,'@@@') = nvl(l_fetch_revision,'@@@')) THEN
1789 l_index_tbl(l_index) := j;
1790 l_index := l_index+1;
1791 END IF;
1792 END IF;
1793 END IF;
1794 l_fetch_sub_inv_code := null;
1795 l_fetch_locator_id := null;
1796 l_fetch_lot_number := null;
1797 l_fetch_revision := null;
1798 END LOOP;
1799 l_index := 1;
1800 FOR k in l_index_tbl.FIRST..l_index_tbl.LAST LOOP
1801 l_filter_srl_number_tbl(l_index).serial_number := l_serial_number_tbl(l_index_tbl(k)).serial_number;
1802 l_filter_srl_number_tbl(l_index).inventory_item_id := l_mtl_req_dtls_rec.inventory_item_id;
1803 l_index := l_index+1;
1804 l_sub_inv_code_tbl.DELETE(l_index_tbl(k));
1805 END LOOP;
1806
1807 IF (l_log_statement >= l_log_current_level) THEN
1808 fnd_log.string
1809 (
1810 fnd_log.level_statement,
1811 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
1812 'For sub inv : = '|| l_sub_inv_code|| ', locator : '||l_locator_id||
1813 ', lot_number : '||l_lot_number||', and l_revision'||l_revision||' => total serial := '||l_filter_srl_number_tbl.COUNT
1814 );
1815 END IF;
1816
1817
1818 GET_MATCHING_RESERVATION(p_scheduled_material_id => l_scheduled_material_id,
1819 p_sub_inv_code => l_sub_inv_code,
1820 p_locator_id => l_locator_id,
1821 p_lot_number => l_lot_number,
1822 p_revision => l_revision,
1823 p_supply_source_type_id => l_create_rsv_rec.supply_source_type_id,
1824 x_reservation_id => l_x_reservation_id,
1825 x_reservation_quantity => l_x_quantity_reserved);
1826
1827 IF(l_x_reservation_id IS NOT NULL) THEN
1828 IF (l_log_statement >= l_log_current_level) THEN
1829 fnd_log.string
1830 (
1831 fnd_log.level_statement,
1832 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
1833 'Calling AHL_MM_RESERVATIONS_PVT.UPDATE_RESERVATION : Supply is Inventory'
1834 );
1835 END IF;
1836 AHL_MM_RESERVATIONS_PVT.UPDATE_RESERVATION(
1837 p_api_version => l_api_version,
1838 p_init_msg_list => l_init_msg_list,
1839 p_commit => FND_API.G_FALSE,
1840 p_validation_level => FND_API.G_VALID_LEVEL_FULL, -- the validation level
1841 p_module_type => G_PKG_NAME,
1842 x_return_status => l_return_status,
1843 x_msg_count => l_msg_count,
1844 X_MSG_DATA => L_MSG_DATA,
1845 P_RESERVATION_ID => l_x_reservation_id,
1846 p_rsv_rec => l_create_rsv_rec,
1847 p_serial_number_tbl => l_filter_srl_number_tbl);
1848
1849 IF (l_log_statement >= l_log_current_level) THEN
1850 fnd_log.string
1851 (
1852 fnd_log.level_statement,
1853 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
1854 'After Calling AHL_MM_RESERVATIONS_PVT.UPDATE_RESERVATION: P_RESERVATION_ID := '|| l_x_reservation_id || ' and l_return_status = '||l_return_status
1855 );
1856 END IF;
1857
1858 -- Check the error status
1859 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1860 -- log the error
1861 IF (l_log_error >= l_log_current_level) THEN
1862 fnd_log.string
1863 (
1864 fnd_log.level_error,
1865 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
1866 'Call to AHL_MM_RESERVATIONS_PVT.UPDATE_RESERVATION returned Unexpected Error'
1867 );
1868 END IF;
1869 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1870 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
1871 -- log the error
1872 IF (l_log_error >= l_log_current_level) THEN
1873 fnd_log.string
1874 (
1875 fnd_log.level_error,
1876 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
1877 'Call to AHL_MM_RESERVATIONS_PVT.UPDATE_RESERVATION returned Expected Error'
1878 );
1879 END IF;
1880 --RAISE at the end of the API FND_API.G_EXC_ERROR;
1881 END IF;
1882 ELSE
1883 IF (l_log_statement >= l_log_current_level) THEN
1884 fnd_log.string
1885 (
1886 fnd_log.level_statement,
1887 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
1891
1888 'Calling AHL_MM_RESERVATIONS_PVT.CREATE_RESERVATION : Supply is Inventory'
1889 );
1890 END IF;
1892 l_create_rsv_rec.subinventory_code := l_sub_inv_code;
1893 l_create_rsv_rec.locator_id := l_locator_id;
1894 l_create_rsv_rec.lot_number := l_lot_number;
1895 l_create_rsv_rec.revision := l_revision;
1896 l_create_rsv_rec.primary_reservation_quantity := l_filter_srl_number_tbl.COUNT;
1897
1898 AHL_MM_RESERVATIONS_PVT.CREATE_RESERVATION(
1899 p_api_version => l_api_version,
1900 p_init_msg_list => l_init_msg_list,
1901 p_commit => FND_API.G_FALSE,
1902 p_validation_level => FND_API.G_VALID_LEVEL_FULL, -- the validation level
1903 p_module_type => G_PKG_NAME,
1904 x_return_status => l_return_status,
1905 x_msg_count => l_msg_count,
1906 X_MSG_DATA => L_MSG_DATA,
1907 P_RSV_REC => l_create_rsv_rec,
1908 p_serial_number_tbl => l_filter_srl_number_tbl,
1909 x_reservation_id => l_x_reservation_id);
1910
1911 IF (l_log_statement >= l_log_current_level) THEN
1912 fnd_log.string
1913 (
1914 fnd_log.level_statement,
1915 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
1916 'After Calling AHL_MM_RESERVATIONS_PVT.CREATE_RESERVATION: x_reservation_id := '|| l_x_reservation_id || ' and l_return_status = '||l_return_status
1917 );
1918 END IF;
1919
1920 -- Check the error status
1921 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1922 -- log the error
1923 IF (l_log_error >= l_log_current_level) THEN
1924 fnd_log.string
1925 (
1926 fnd_log.level_error,
1927 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
1928 'Call to AHL_MM_RESERVATIONS_PVT.CREATE_RESERVATION returned Unexpected Error'
1929 );
1930 END IF;
1931 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1932 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
1933 -- log the error
1934 IF (l_log_error >= l_log_current_level) THEN
1935 fnd_log.string
1936 (
1937 fnd_log.level_error,
1938 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
1939 'Call to AHL_MM_RESERVATIONS_PVT.CREATE_RESERVATION returned Expected Error'
1940 );
1941 END IF;
1942 --RAISE at the end of the API FND_API.G_EXC_ERROR;
1943 END IF;
1944
1945 END IF;
1946
1947 l_filter_srl_number_tbl.delete;
1948 l_index_tbl.delete;
1949 l_index := x_reservation_id_tbl.COUNT;
1950 x_reservation_id_tbl(l_index+1) := l_x_reservation_id;
1951 l_sub_inv_code := null;
1952 l_locator_id := null;
1953 l_lot_number := null;
1954 l_revision := null;
1955 l_x_reservation_id := null;
1956 END IF;
1957 END LOOP;
1958 END IF;
1959 END IF; -- Reservation_id is not null
1960 --------------------------------------------------------
1961 -- PEKAMBAR - Enhanced to support supply source is WIP Entity Type , Internal Requesition(i.e Workorder)
1962 ELSIF(l_create_rsv_rec.supply_source_type_id = inv_reservation_global.g_source_type_wip OR l_create_rsv_rec.supply_source_type_id = inv_reservation_global.g_source_type_internal_req)
1963 THEN
1964 IF (l_log_statement >= l_log_current_level) THEN
1965 IF(l_create_rsv_rec.supply_source_type_id = inv_reservation_global.g_source_type_wip)
1966 THEN
1967 fnd_log.string
1968 (
1969 fnd_log.level_statement,
1970 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
1971 'Supply Source Type is ::Workorder---Source Type Id is::'||l_create_rsv_rec.supply_source_type_id
1972 );
1973 ELSE
1974 fnd_log.string
1975 (
1976 fnd_log.level_statement,
1977 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
1978 'Supply Source Type is ::Internal Requesition---Source Type Id is:: '||l_create_rsv_rec.supply_source_type_id
1979 );
1980 END IF;
1981 END IF;
1982
1983 IF(l_create_rsv_rec.reservation_id IS NOT NULL AND l_create_rsv_rec.reservation_id <> FND_API.G_MISS_NUM)
1984 THEN
1985 IF (l_log_statement >= l_log_current_level)
1986 THEN
1987 fnd_log.string
1988 (
1989 fnd_log.level_statement,
1990 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
1991 'Calling AHL_MM_RESERVATIONS_PVT.UPDATE_RESERVATION : Supply is Workorder or Internal Requesition '
1992 );
1993 END IF;
1994
1995 -- Added code by debadey
1996 -- to initialize for WO-WO reservation updation
1997 -- the demand WO details are updated as per the asm id
1998 -- ***ASSUMPTION*** - this flow will be used only by WO-WO demand updation in a reservation
1999 INITIALIZE_WO_UPDATE_REC(
2000 p_schedule_material_id => l_create_rsv_rec.demand_source_line_detail,
2001 p_x_rsv_rec => l_create_rsv_rec,
2002 x_return_status => l_return_status
2003 );
2004
2005 IF (l_log_statement >= l_log_current_level) THEN
2006 fnd_log.string
2007 (
2008 fnd_log.level_statement,
2009 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
2010 'After Calling INITIALIZE_WO_UPDATE_REC :l_return_status = '||l_return_status||
2011 ' And the project id is '||l_create_rsv_rec.project_id
2012 );
2013 END IF;
2014 -- End of changes by debadey
2015
2016 AHL_MM_RESERVATIONS_PVT.UPDATE_RESERVATION
2017 (
2018 p_api_version => l_api_version,
2019 p_init_msg_list => l_init_msg_list,
2020 p_commit => FND_API.G_FALSE,
2021 p_validation_level => FND_API.G_VALID_LEVEL_FULL, -- the validation level
2022 p_module_type => G_PKG_NAME,
2023 x_return_status => l_return_status,
2024 x_msg_count => l_msg_count,
2025 x_msg_data => l_msg_data,
2026 p_reservation_id => l_create_rsv_rec.reservation_id,
2027 p_rsv_rec => l_create_rsv_rec,
2028 p_serial_number_tbl => l_serial_number_tbl
2029 );
2030
2031 IF (l_log_statement >= l_log_current_level) THEN
2032 fnd_log.string
2033 (
2034 fnd_log.level_statement,
2035 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
2036 'After Calling AHL_MM_RESERVATIONS_PVT.UPDATE_RESERVATION: P_RESERVATION_ID := '|| l_x_reservation_id || ' and l_return_status = '||l_return_status
2037 );
2038 END IF;
2039
2040 -- Check the error status
2041 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2042 -- log the error
2043 IF (l_log_error >= l_log_current_level) THEN
2044 fnd_log.string
2045 (
2046 fnd_log.level_error,
2047 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
2048 'Call to AHL_MM_RESERVATIONS_PVT.UPDATE_RESERVATION returned Unexpected Error'
2049 );
2050 END IF;
2051 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2052 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
2053 -- log the error
2054 IF (l_log_error >= l_log_current_level) THEN
2055 fnd_log.string
2056 (
2057 fnd_log.level_error,
2058 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
2059 'Call to AHL_MM_RESERVATIONS_PVT.UPDATE_RESERVATION returned Expected Error'
2060 );
2061 END IF;
2062 --RAISE at the end of the API FND_API.G_EXC_ERROR;
2063 END IF;
2064
2065 ELSE -- Create
2066
2067 IF (l_log_statement >= l_log_current_level) THEN
2068 fnd_log.string
2069 (
2070 fnd_log.level_statement,
2071 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
2072 'Calling AHL_MM_RESERVATIONS_PVT.CREATE_RESERVATION: Supply is Workorder or Internal Requesition'
2073 );
2074 END IF;
2075 AHL_MM_RESERVATIONS_PVT.CREATE_RESERVATION
2076 (
2077 p_api_version => l_api_version,
2078 p_init_msg_list => l_init_msg_list,
2079 p_commit => FND_API.G_FALSE,
2080 p_validation_level => FND_API.G_VALID_LEVEL_FULL, -- the validation level
2081 p_module_type => G_PKG_NAME,
2082 x_return_status => l_return_status,
2083 x_msg_count => l_msg_count,
2084 x_msg_data => l_msg_data,
2085 p_rsv_rec => l_create_rsv_rec,
2086 p_serial_number_tbl => l_serial_number_tbl,
2090 x_reservation_id_tbl(1) := x_reservation_id;
2087 x_reservation_id => x_reservation_id
2088 );
2089
2091 IF (l_log_statement >= l_log_current_level) THEN
2092 fnd_log.string
2093 (
2094 fnd_log.level_statement,
2095 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
2096 'After AHL_MM_RESERVATIONS_PVT.CREATE_RESERVATION x_reservation_id := '|| x_reservation_id || ' and l_return_status = '||l_return_status
2097 );
2098 END IF;
2099 -- Check the error status
2100 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2101 -- log the error
2102 IF (l_log_error >= l_log_current_level) THEN
2103 fnd_log.string
2104 (
2105 fnd_log.level_error,
2106 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
2107 'Call to AHL_MM_RESERVATIONS_PVT.CREATE_RESERVATION Unexpected Error'
2108 );
2109 END IF;
2110 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2111 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
2112 -- log the error
2113 IF (l_log_error >= l_log_current_level) THEN
2114 fnd_log.string
2115 (
2116 fnd_log.level_error,
2117 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
2118 'Call to AHL_MM_RESERVATIONS_PVT.CREATE_RESERVATION Expected Error'
2119 );
2120 END IF;
2121 --RAISE at the end of the API FND_API.G_EXC_ERROR;
2122 END IF;
2123 END IF;
2124
2125 ELSE -- Inventory, Workorder, IR is not the supply source thorw an error
2126
2127 FND_MESSAGE.set_name( 'AHL', 'AHL_MM_INV_SUP_SRC_TYPE' );
2128 FND_MSG_PUB.add;
2129 -- log the error
2130 IF (l_log_error >= l_log_current_level) THEN
2131 fnd_log.string
2132 (
2133 fnd_log.level_error,
2134 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||'.begin',
2135 'Invalid Supply source'
2136 );
2137 END IF;
2138 RAISE FND_API.G_EXC_ERROR;
2139
2140 END IF; -- supply type inventory => l_create_rsv_rec.supply_source_type_id = inv_reservation_global.g_source_type_inv
2141
2142 -- Check Error Message stack.
2143 x_msg_count := FND_MSG_PUB.count_msg;
2144 IF x_msg_count > 0
2145 THEN
2146 -- log the error
2147 IF (l_log_error >= l_log_current_level) THEN
2148 fnd_log.string
2149 (
2150 fnd_log.level_error,
2151 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
2152 'Call to WMS returned Errors in x_msg_count'
2153 );
2154 END IF;
2155 RAISE FND_API.G_EXC_ERROR;
2156 END IF;
2157
2158 -- Commit if p_commit = FND_API.G_TRUE
2159 IF FND_API.TO_BOOLEAN(p_commit)
2160 THEN
2161 COMMIT WORK;
2162 IF (l_log_statement >= l_log_current_level) THEN
2163 fnd_log.string
2164 (
2165 fnd_log.level_statement,
2166 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
2167 'Committed'
2168 );
2169 END IF;
2170 END IF;
2171
2172 -- Standard call to get message count and if count is 1, get message info
2173 FND_MSG_PUB.count_and_get
2174 (
2175 p_count => x_msg_count,
2176 p_data => x_msg_data,
2177 p_encoded => FND_API.G_FALSE
2178 );
2179
2180 -- Log API exit point
2181 IF (l_log_procedure >= l_log_current_level)THEN
2182 fnd_log.string
2183 (
2184 fnd_log.level_procedure,
2185 l_debug_module||'.end',
2186 'At the end of PLSQL procedure'
2187 );
2188 END IF;
2189
2190 EXCEPTION
2191 WHEN FND_API.G_EXC_ERROR THEN
2192 ROLLBACK TO PROCESS_RESERVATION_PVT;
2193 x_return_status := FND_API.G_RET_STS_ERROR;
2194 FND_MSG_PUB.count_and_get
2195 (
2196 p_count => x_msg_count,
2197 p_data => x_msg_data,
2198 p_encoded => FND_API.G_FALSE
2199 );
2200
2201 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2202 ROLLBACK TO PROCESS_RESERVATION_PVT;
2203 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2204 FND_MSG_PUB.count_and_get
2205 (
2206 p_count => x_msg_count,
2207 p_data => x_msg_data,
2208 p_encoded => FND_API.G_FALSE
2209 );
2210
2211 WHEN OTHERS THEN
2212 ROLLBACK TO PROCESS_RESERVATION_PVT;
2213 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2214 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2215 THEN
2216 FND_MSG_PUB.add_exc_msg
2217 (
2218 p_pkg_name => G_PKG_NAME,
2219 p_procedure_name => 'PROCESS_RESERVATION',
2220 p_error_text => SUBSTR(SQLERRM,1,240)
2221 );
2222 END IF;
2223 FND_MSG_PUB.count_and_get
2224 (
2225 p_count => x_msg_count,
2226 p_data => x_msg_data,
2227 p_encoded => FND_API.G_FALSE
2228 );
2229 END PROCESS_RESERVATION;
2230
2231
2232 ------------------------------------------------------------------------------------------------------------------
2233 -- Start of Comments --
2234 -- Procedure name : CANCEL_RESERVATION
2235 -- Type : Private
2236 -- Function : API to relieve given serial numbers from their respective reservation.
2237 PROCEDURE CANCEL_RESERVATION(
2238 p_api_version IN NUMBER := 1.0,
2242 p_module_type IN VARCHAR2,
2239 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2240 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2241 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2243 x_return_status OUT NOCOPY VARCHAR2,
2244 x_msg_count OUT NOCOPY NUMBER,
2245 x_msg_data OUT NOCOPY VARCHAR2,
2246 p_serial_number_tbl IN serial_number_tbl_type,
2247 p_reservation_id_tbl IN number_table_type)
2248 IS
2249 -- Declare local variables
2250 l_api_name CONSTANT VARCHAR2(30) := 'CANCEL_RESERVATION';
2251 l_api_version CONSTANT NUMBER := 1.0;
2252 l_init_msg_list VARCHAR2(1) := 'F';
2253 l_return_status VARCHAR2(1);
2254 l_msg_count NUMBER;
2255 l_msg_data VARCHAR2(2000);
2256 l_debug_module CONSTANT VARCHAR2(100) := 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name;
2257
2258
2259 CURSOR get_resrv_detials_csr(c_serial_number IN VARCHAR2,c_reservation_id IN NUMBER)
2260 IS
2261 SELECT MR.inventory_item_id,MR.primary_reservation_quantity
2262 FROM mtl_serial_numbers MSN, mtl_reservations MR
2263 WHERE MSN.serial_number = c_serial_number
2264 AND MSN.reservation_id = c_reservation_id
2265 AND MR.reservation_id = MSN.reservation_id
2266 AND MR.inventory_item_id = MSN.inventory_item_id
2267 AND MR.external_source_code = 'AHL';
2268
2269
2270 l_serial_number_tbl serial_number_tbl_type := p_serial_number_tbl;
2271 l_tmp_serial_number_tbl serial_number_tbl_type;
2272 l_reservation_id_tbl number_table_type := p_reservation_id_tbl;
2273
2274 TYPE reserv_serial_tbl_typ IS TABLE OF varchar_table_type INDEX BY BINARY_INTEGER;
2275
2276 l_reserv_serial_tbl reserv_serial_tbl_typ;
2277 l_serial_tbl varchar_table_type;
2278 l_inventory_item_id NUMBER;
2279 l_reservation_id NUMBER;
2280 l_resrv_quantity NUMBER;
2281 l_index NUMBER;
2282
2283 BEGIN
2284 -- Standard start of API savepoint
2285 SAVEPOINT CANCEL_RESERVATION_PVT;
2286 -- Initialize return status to success before any code logic/validation
2287 x_return_status:= FND_API.G_RET_STS_SUCCESS;
2288
2289
2290 -- Standard call to check for call compatibility
2291 IF NOT FND_API.COMPATIBLE_API_CALL (l_api_version, p_api_version, l_api_name, G_PKG_NAME)
2292 THEN
2293 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2294 END IF;
2295
2296
2297 -- Initialize message list if p_init_msg_list = FND_API.G_TRUE
2298 IF FND_API.TO_BOOLEAN(p_init_msg_list)
2299 THEN
2300 FND_MSG_PUB.INITIALIZE;
2301 END IF;
2302
2303 -- Log API entry point
2304 IF (l_log_procedure >= l_log_current_level) THEN
2305 fnd_log.string
2306 (
2307 fnd_log.level_procedure,
2308 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||'.begin',
2309 'At the start of PL SQL procedure, total serial_number count: '||p_serial_number_tbl.COUNT
2310 );
2311 END IF;
2312
2313 /*
2314 -- Validate to make sure that the Serial Number is not empty, when Supply is Inventory.
2315 IF (l_serial_number_tbl.COUNT < 1) THEN
2316 FND_MESSAGE.set_name( 'AHL', 'AHL_PP_SERIAL_MISSING' );
2317 FND_MSG_PUB.add;
2318 -- log the error
2319 IF (l_log_error >= l_log_current_level) THEN
2320 fnd_log.string
2321 (
2322 fnd_log.level_error,
2323 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
2324 'Serial Number passed to the API is null'
2325 );
2326 END IF;
2327 RAISE FND_API.G_EXC_ERROR;
2328 END IF;
2329 */
2330
2331 -- Validate to make sure that the Serial Number is not empty, when Supply is Inventory.
2332 IF (l_reservation_id_tbl.COUNT < 1) THEN
2333 FND_MESSAGE.set_name( 'AHL', 'AHL_LTP_MULTI_ORG' );
2334 FND_MSG_PUB.add;
2335 -- log the error
2336 IF (l_log_error >= l_log_current_level) THEN
2337 fnd_log.string
2338 (
2339 fnd_log.level_error,
2340 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
2341 'reservation table passed to the API is null'
2342 );
2343 END IF;
2344 RAISE FND_API.G_EXC_ERROR;
2345 END IF;
2346
2347
2348 -- For all records in reservation table
2349 FOR i IN l_reservation_id_tbl.FIRST..l_reservation_id_tbl.LAST LOOP
2350 IF (l_log_error >= l_log_current_level) THEN
2351 fnd_log.string
2352 (
2353 fnd_log.level_error,
2354 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
2355 'Inside reservtion table, iteration : '|| i|| ' reservation_id = '|| l_reservation_id_tbl(i)
2356 );
2357 END IF;
2358 IF(l_reserv_serial_tbl.EXISTS(l_reservation_id_tbl(i))) THEN
2359 l_serial_tbl := l_reserv_serial_tbl(l_reservation_id_tbl(i));
2360 l_index := l_serial_tbl.COUNT;
2361 IF (l_log_error >= l_log_current_level) THEN
2362 fnd_log.string
2363 (
2364 fnd_log.level_error,
2365 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
2366 'reservation exists : '|| l_reservation_id_tbl(i) || ' present count := '||l_index||
2367 'serial number := '|| l_serial_number_tbl(i).serial_number
2368 );
2369 END IF;
2370 l_serial_tbl(l_index+1) := l_serial_number_tbl(i).serial_number;
2371 l_reserv_serial_tbl(l_reservation_id_tbl(i)) := l_serial_tbl;
2372
2373 ELSE
2374 IF (l_serial_number_tbl.EXISTS(i)) THEN
2375 -- l_serial_tbl := varchar_table_type(); -- initialize varray
2376 IF (l_log_error >= l_log_current_level) THEN
2377 fnd_log.string
2378 (
2379 fnd_log.level_error,
2380 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
2381 'serial number := '|| l_serial_number_tbl(i).serial_number
2382 );
2383 END IF;
2384 l_serial_tbl(1) := l_serial_number_tbl(i).serial_number;
2385 ELSE
2386 IF (l_log_error >= l_log_current_level) THEN
2387 fnd_log.string
2388 (
2389 fnd_log.level_error,
2390 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
2391 'serial number not passed for this reservation := '||l_reservation_id_tbl(i)
2392 );
2393 END IF;
2394 l_serial_tbl(1) := NULL;
2395 END IF;
2396 l_reserv_serial_tbl(l_reservation_id_tbl(i)) := l_serial_tbl;
2397
2398 END IF;
2399 l_serial_tbl.DELETE;
2400 END LOOP;
2401
2402 -- For all the records, loop through l_reserv_serial_tbl table.
2403 l_reservation_id := l_reserv_serial_tbl.FIRST;
2404 LOOP
2405 EXIT when l_reservation_id IS NULL;
2406
2407 l_serial_tbl := l_reserv_serial_tbl(l_reservation_id);
2408 IF(l_serial_tbl(l_serial_tbl.FIRST) IS NOT NULL) THEN
2409 FOR j IN l_serial_tbl.FIRST..l_serial_tbl.LAST LOOP
2410 OPEN get_resrv_detials_csr(l_serial_tbl(j),l_reservation_id);
2411 FETCH get_resrv_detials_csr INTO l_inventory_item_id,l_resrv_quantity;
2412 IF (get_resrv_detials_csr%NOTFOUND) THEN
2413 FND_MESSAGE.set_name( 'AHL', 'AHL_MM_SRL_RSRV_INVALID' );
2414 FND_MSG_PUB.add;
2415 -- log the error
2416 IF (l_log_error >= l_log_current_level) THEN
2417 fnd_log.string
2418 (
2419 fnd_log.level_error,
2420 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
2421 'Serial number '||l_serial_tbl(j)||' does not match the reservation_id'||l_reservation_id
2422 );
2423 END IF;
2424 CLOSE get_resrv_detials_csr;
2425 RAISE FND_API.G_EXC_ERROR;
2426 END IF;
2427 CLOSE get_resrv_detials_csr;
2428
2429 l_tmp_serial_number_tbl(j).serial_number := l_serial_tbl(j);
2430 l_tmp_serial_number_tbl(j).inventory_item_id := l_inventory_item_id;
2431
2432 END LOOP;
2433
2434 IF (l_log_error >= l_log_current_level) THEN
2435 fnd_log.string
2436 (
2437 fnd_log.level_error,
2438 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
2439 'Resrv id := '|| l_reservation_id || ' serial count = '||l_tmp_serial_number_tbl.COUNT||
2440 ' l_resrved_quantity := '|| l_resrv_quantity
2441 );
2442 END IF;
2443 ELSE
2444 -- No serials passed, delete entire reservation
2445 l_resrv_quantity := l_tmp_serial_number_tbl.COUNT;
2446 IF (l_log_error >= l_log_current_level) THEN
2447 fnd_log.string
2448 (
2449 fnd_log.level_error,
2450 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
2451 'Resrv id := '|| l_reservation_id || ' Serial number not passed '||
2452 ' l_tmp_serial_number_tbl.COUNT := '|| l_resrv_quantity
2453 );
2454 END IF;
2455 END IF;
2456
2457 IF(l_resrv_quantity = l_tmp_serial_number_tbl.COUNT) THEN
2458
2459
2460 AHL_MM_RESERVATIONS_PVT.DELETE_RESERVATION(
2461 p_api_version => l_api_version,
2462 p_init_msg_list => l_init_msg_list,
2463 p_commit => FND_API.G_FALSE,
2464 p_validation_level => FND_API.G_VALID_LEVEL_FULL, -- the validation level
2465 p_module_type => G_PKG_NAME,
2466 x_return_status => l_return_status,
2467 x_msg_count => l_msg_count,
2468 X_MSG_DATA => L_MSG_DATA,
2469 p_reservation_id => l_reservation_id);
2470
2471
2472 ELSE
2473 AHL_MM_RESERVATIONS_PVT.RELIEVE_RESERVATION(
2474 p_api_version => l_api_version,
2475 p_init_msg_list => l_init_msg_list,
2476 p_commit => FND_API.G_FALSE,
2477 p_validation_level => FND_API.G_VALID_LEVEL_FULL, -- the validation level
2478 p_module_type => G_PKG_NAME,
2479 x_return_status => l_return_status,
2480 x_msg_count => l_msg_count,
2481 X_MSG_DATA => L_MSG_DATA,
2482 p_reservation_id => l_reservation_id,
2483 p_serial_number_tbl => l_tmp_serial_number_tbl);
2484
2485 END IF;
2486
2487 IF (l_log_statement >= l_log_current_level) THEN
2488 fnd_log.string
2489 (
2490 fnd_log.level_statement,
2491 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
2492 'After Calling Relieve/Delete reservation for reservation_id := '|| l_reservation_id || ' and l_return_status = '||l_return_status
2493 );
2494 END IF;
2495
2496 -- Check the error status
2497 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2498 -- log the error
2499 IF (l_log_error >= l_log_current_level) THEN
2500 fnd_log.string
2501 (
2502 fnd_log.level_error,
2503 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
2504 'Call to AHL_MM_RESERVATIONS_PVT.Relieve/Delete returned Unexpected Error'
2505 );
2506 END IF;
2507 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2508 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
2509 -- log the error
2510 IF (l_log_error >= l_log_current_level) THEN
2511 fnd_log.string
2512 (
2513 fnd_log.level_error,
2514 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
2515 'Call to AHL_MM_RESERVATIONS_PVT.Relieve/Delete returned Expected Error'
2516 );
2517 END IF;
2518 RAISE FND_API.G_EXC_ERROR;
2519 END IF;
2520
2521 l_serial_tbl.DELETE;
2522 l_inventory_item_id := null;
2523 l_resrv_quantity := null;
2524 l_tmp_serial_number_tbl.DELETE;
2525 l_reservation_id := l_reserv_serial_tbl.NEXT(l_reservation_id);
2526 IF (l_log_statement >= l_log_current_level) THEN
2527 fnd_log.string
2528 (
2529 fnd_log.level_statement,
2530 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
2531 'Next reservation_id := '|| l_reservation_id
2532 );
2533 END IF;
2534 END LOOP;
2535
2536
2537 -- Check Error Message stack.
2538 x_msg_count := FND_MSG_PUB.count_msg;
2539 IF x_msg_count > 0
2540 THEN
2541 -- log the error
2542 IF (l_log_error >= l_log_current_level) THEN
2543 fnd_log.string
2544 (
2545 fnd_log.level_error,
2546 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
2547 'Call to WMS returned Errors in x_msg_count'
2548 );
2549 END IF;
2550 RAISE FND_API.G_EXC_ERROR;
2551 END IF;
2552
2553 -- Commit if p_commit = FND_API.G_TRUE
2554 IF FND_API.TO_BOOLEAN(p_commit)
2555 THEN
2556 COMMIT WORK;
2557 IF (l_log_statement >= l_log_current_level) THEN
2558 fnd_log.string
2559 (
2560 fnd_log.level_statement,
2561 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
2562 'Committed'
2563 );
2564 END IF;
2565 END IF;
2566
2567 -- Standard call to get message count and if count is 1, get message info
2568 FND_MSG_PUB.count_and_get
2569 (
2570 p_count => x_msg_count,
2571 p_data => x_msg_data,
2572 p_encoded => FND_API.G_FALSE
2573 );
2574
2575 -- Log API exit point
2576 IF (l_log_procedure >= l_log_current_level)THEN
2577 fnd_log.string
2578 (
2579 fnd_log.level_procedure,
2580 l_debug_module||'.end',
2581 'At the end of PLSQL procedure'
2582 );
2583 END IF;
2584
2585 EXCEPTION
2586 WHEN FND_API.G_EXC_ERROR THEN
2587 ROLLBACK TO CANCEL_RESERVATION_PVT;
2588 x_return_status := FND_API.G_RET_STS_ERROR;
2589 FND_MSG_PUB.count_and_get
2590 (
2591 p_count => x_msg_count,
2592 p_data => x_msg_data,
2593 p_encoded => FND_API.G_FALSE
2594 );
2595
2596 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2597 ROLLBACK TO CANCEL_RESERVATION_PVT;
2598 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2599 FND_MSG_PUB.count_and_get
2600 (
2601 p_count => x_msg_count,
2602 p_data => x_msg_data,
2603 p_encoded => FND_API.G_FALSE
2604 );
2605
2606 WHEN OTHERS THEN
2607 ROLLBACK TO CANCEL_RESERVATION_PVT;
2608 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2609 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2610 THEN
2611 FND_MSG_PUB.add_exc_msg
2612 (
2613 p_pkg_name => G_PKG_NAME,
2614 p_procedure_name => 'CANCEL_RESERVATION',
2615 p_error_text => SUBSTR(SQLERRM,1,240)
2616 );
2617 END IF;
2618 FND_MSG_PUB.count_and_get
2619 (
2620 p_count => x_msg_count,
2621 p_data => x_msg_data,
2622 p_encoded => FND_API.G_FALSE
2623 );
2624 END CANCEL_RESERVATION;
2625
2626 ---------------------------------------------------------------------------------------------------------------------
2627 -- Declare Procedures --
2628 ---------------------------------------------------------------------------------------------------------------------
2629 -- Start of Comments --
2630 -- Procedure name : INITIALIZE_REC_FROM_RESV
2631 -- Type : Private
2632 -- Function : To initializa the record that is to be passed into WMS api
2633 -- Pre-reqs :
2634 -- Standard IN Parameters :
2635 -- Standard OUT Parameters :
2636 -- INITIALIZE_REC_FROM_RESV Parameters:
2637 -- p_rsv_rec :
2638 -- p_schedule_material_id :
2639 -- p_x_rsv_rec :
2640 -- End of Comments.
2641 ---------------------------------------------------------------------------------------------------------------------
2642 PROCEDURE INITIALIZE_REC_FROM_RESV(
2643 p_reservation_id IN NUMBER,
2644 p_x_rsv_rec IN OUT NOCOPY mtl_reservation_rec_type)
2645 IS
2646
2647 -- Declare local variables
2648 l_api_name CONSTANT VARCHAR2(30) := 'Initialize_rec_from_resv';
2649 l_debug_module CONSTANT VARCHAR2(100) := 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name;
2650
2651 -- Declare cursors
2652 CURSOR get_resrv_detials_csr
2653 IS
2654 SELECT *
2655 FROM mtl_reservations MR
2656 WHERE MR.reservation_id = p_reservation_id
2657 AND MR.external_source_code = 'AHL';
2658
2659 rsv_rec get_resrv_detials_csr%ROWTYPE;
2660
2661 BEGIN
2662
2663 -- Log API exit point
2664 IF (l_log_procedure >= l_log_current_level)THEN
2665 fnd_log.string
2666 (
2667 fnd_log.level_procedure,
2668 l_debug_module||'.start',
2669 'At the start of PLSQL procedure'
2670 );
2671 END IF;
2672
2673 IF (l_log_statement >= l_log_current_level) THEN
2674 fnd_log.string
2675 (
2676 fnd_log.level_statement,
2677 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
2678 'p_reservation_id is: ' || p_reservation_id
2679 );
2680 END IF;
2681
2682 -- Get the material requirement details
2683 OPEN get_resrv_detials_csr;
2684 FETCH get_resrv_detials_csr INTO rsv_rec;
2685 CLOSE get_resrv_detials_csr;
2686
2690 p_x_rsv_rec.demand_source_header_id := rsv_rec.demand_source_header_id;
2687 --Demand Details
2688 p_x_rsv_rec.demand_source_name := 'CMRO'||'.'||'WO:';
2689 p_x_rsv_rec.demand_source_type_id := inv_reservation_global.g_source_type_wip;
2691 p_x_rsv_rec.demand_source_line_id := rsv_rec.demand_source_line_id;
2692 p_x_rsv_rec.demand_source_line_detail := rsv_rec.demand_source_line_detail;
2693 p_x_rsv_rec.requirement_date := rsv_rec.requirement_date;
2694 p_x_rsv_rec.organization_id := rsv_rec.organization_id;
2695 p_x_rsv_rec.primary_uom_code := rsv_rec.primary_uom_code;
2696 p_x_rsv_rec.reservation_uom_code := rsv_rec.primary_uom_code;
2697 p_x_rsv_rec.reservation_quantity := rsv_rec.reservation_quantity;
2698 p_x_rsv_rec.subinventory_code := rsv_rec.subinventory_code;
2699 p_x_rsv_rec.locator_id := rsv_rec.locator_id;
2700 p_x_rsv_rec.revision := rsv_rec.revision ;
2701 p_x_rsv_rec.lot_number := rsv_rec.lot_number ;
2702 p_x_rsv_rec.external_source_code := 'AHL';
2703 p_x_rsv_rec.external_source_line_id := 867;
2704 p_x_rsv_rec.inventory_item_id := rsv_rec.inventory_item_id;
2705 p_x_rsv_rec.serial_number := rsv_rec.serial_number;
2706 P_X_RSV_REC.SHIP_READY_FLAG := RSV_REC.SHIP_READY_FLAG;
2707
2708
2709 IF (l_log_statement >= l_log_current_level) THEN
2710 fnd_log.string
2711 (
2712 fnd_log.level_statement,
2713 'ahl.plsql.'||G_PKG_NAME||'.'||L_API_NAME,
2714 'INITIALIZE_REC_FROM_RESV, P_X_RSV_REC.project_id : ' || P_X_RSV_REC.PROJECT_ID ||
2715 ', P_X_RSV_REC.task_id : ' || P_X_RSV_REC.TASK_ID
2716 );
2717 END IF;
2718
2719 -- Log API exit point
2720 IF (l_log_procedure >= l_log_current_level)THEN
2721 fnd_log.string
2722 (
2723 fnd_log.level_procedure,
2724 l_debug_module||'.end',
2725 'At the end of PLSQL procedure'
2726 );
2727 END IF;
2728 END INITIALIZE_REC_FROM_RESV;
2729
2730 ---------------------------------------------------------------------------------------------------------------------
2731 -- Declare Procedures --
2732 ---------------------------------------------------------------------------------------------------------------------
2733 -- Start of Comments --
2734 -- Procedure name : INITIALIZE_CREATE_REC
2735 -- Type : Private
2736 -- Function : To initializa the record that is to be passed into WMS api
2737 -- Pre-reqs :
2738 -- Standard IN Parameters :
2739 -- Standard OUT Parameters :
2740 -- INITIALIZE_CREATE_REC Parameters:
2741 -- p_rsv_rec :
2742 -- p_schedule_material_id :
2743 -- p_x_rsv_rec :
2744 -- End of Comments.
2745 ---------------------------------------------------------------------------------------------------------------------
2746 PROCEDURE INITIALIZE_CREATE_REC(
2747 p_schedule_material_id IN NUMBER,
2748 p_x_rsv_rec IN OUT NOCOPY mtl_reservation_rec_type,
2749 x_return_status OUT NOCOPY VARCHAR2
2750 )
2751 IS
2752
2753 -- Declare local variables
2754 l_api_name CONSTANT VARCHAR2(30) := 'initialize_create_rec';
2755 l_debug_module CONSTANT VARCHAR2(100) := 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name;
2756
2757 -- Declare cursors
2758
2759 BEGIN
2760
2761 -- Log API exit point
2762 IF (l_log_procedure >= l_log_current_level)THEN
2763 fnd_log.string
2764 (
2765 fnd_log.level_procedure,
2766 l_debug_module||'.start',
2767 'At the start of PLSQL procedure'
2768 );
2769 END IF;
2770 -- Initialize return status to success before any code logic/validation
2771 x_return_status:= FND_API.G_RET_STS_SUCCESS;
2772
2773 IF (l_log_statement >= l_log_current_level) THEN
2774 fnd_log.string
2775 (
2776 fnd_log.level_statement,
2777 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
2778 'p_schedule_material_id is: ' || p_schedule_material_id
2779 );
2780 END IF;
2781
2782 -- Get the material requirement details
2783 OPEN get_mtl_req_dtls_csr (p_schedule_material_id);
2784 FETCH get_mtl_req_dtls_csr INTO l_mtl_req_dtls_rec;
2785 CLOSE get_mtl_req_dtls_csr;
2786
2787 -- Check the WIP entity id for demand_source_header_id
2788 IF(l_mtl_req_dtls_rec.wip_entity_id is NULL) THEN
2789 IF (l_log_statement >= l_log_current_level) THEN
2790 fnd_log.string(
2791 fnd_log.level_statement,
2792 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
2793 'Required demand is not for a workorder/wip_entity_id, p_schedule_material_id:' ||p_schedule_material_id);
2794 END IF;
2795 FND_MESSAGE.set_name( 'AHL', 'AHL_MM_INV_REQ' );
2796 FND_MSG_PUB.add;
2797 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2798 RETURN;
2799 END IF;
2800
2801 IF (l_log_statement >= l_log_current_level) THEN
2802 fnd_log.string
2803 (
2804 fnd_log.level_statement,
2805 'ahl.plsql.'||G_PKG_NAME||'.'||L_API_NAME,
2806 'INITIALIZE_CREATE_REC, l_mtl_req_dtls_rec.project_id : ' || l_mtl_req_dtls_rec.PROJECT_ID ||
2807 ', l_mtl_req_dtls_rec.task_id : ' || l_mtl_req_dtls_rec.TASK_ID
2808 );
2809 END IF;
2810
2811
2812 --Demand Details
2816 p_x_rsv_rec.demand_source_line_id := l_mtl_req_dtls_rec.operation_sequence;
2813 p_x_rsv_rec.demand_source_name := 'CMRO'||'.'||'WO:'||l_mtl_req_dtls_rec.workorder_id; --||'.'||'Wip:header'||'.'||'ASM:detail'
2814 p_x_rsv_rec.demand_source_type_id := inv_reservation_global.g_source_type_wip;
2815 p_x_rsv_rec.demand_source_header_id := l_mtl_req_dtls_rec.wip_entity_id;
2817 p_x_rsv_rec.demand_source_line_detail := p_schedule_material_id;
2818 p_x_rsv_rec.requirement_date := l_mtl_req_dtls_rec.requested_date;
2819 p_x_rsv_rec.organization_id := l_mtl_req_dtls_rec.organization_id;
2820 P_X_RSV_REC.PRIMARY_UOM_CODE := L_MTL_REQ_DTLS_REC.UOM;
2821 P_X_RSV_REC.PROJECT_ID := L_MTL_REQ_DTLS_REC.PROJECT_ID;
2822 P_X_RSV_REC.TASK_ID := L_MTL_REQ_DTLS_REC.TASK_ID;
2823
2824 /* IF(p_x_rsv_rec.primary_reservation_quantity = FND_API.G_MISS_NUM) THEN
2825 p_x_rsv_rec.primary_reservation_quantity := 1;
2826 END IF;
2827 */
2828 p_x_rsv_rec.reservation_uom_code := l_mtl_req_dtls_rec.uom;
2829 p_x_rsv_rec.reservation_uom_id := NULL;
2830 p_x_rsv_rec.reservation_quantity := NULL;
2831 p_x_rsv_rec.demand_ship_date := NULL;
2832 --Supply Details
2833 IF(p_x_rsv_rec.subinventory_code = FND_API.G_MISS_CHAR) THEN
2834 p_x_rsv_rec.subinventory_code := NULL;
2835 END IF;
2836 --Defaulting to inventory reservation. Otherwise, expected to be passed by the caller API.
2837 IF(p_x_rsv_rec.supply_source_type_id IS NULL OR p_x_rsv_rec.supply_source_type_id = FND_API.G_MISS_NUM) THEN
2838 p_x_rsv_rec.supply_source_type_id := inv_reservation_global.g_source_type_inv;
2839 p_x_rsv_rec.supply_source_header_id := NULL;
2840 p_x_rsv_rec.supply_source_line_id := NULL;
2841 p_x_rsv_rec.supply_source_line_detail := NULL;
2842 END IF;
2843
2844 p_x_rsv_rec.supply_source_name := null;
2845
2846 IF(p_x_rsv_rec.supply_source_header_id = FND_API.G_MISS_NUM) THEN
2847 p_x_rsv_rec.supply_source_header_id := NULL;
2848 END IF;
2849 IF(p_x_rsv_rec.supply_source_line_id = FND_API.G_MISS_NUM) THEN
2850 p_x_rsv_rec.supply_source_line_id := NULL;
2851 END IF;
2852 IF(p_x_rsv_rec.supply_source_line_detail = FND_API.G_MISS_NUM) THEN
2853 p_x_rsv_rec.supply_source_line_detail := NULL;
2854 END IF;
2855 IF(p_x_rsv_rec.revision = FND_API.G_MISS_CHAR) THEN
2856 p_x_rsv_rec.revision := NULL;
2857 END IF;
2858 IF(p_x_rsv_rec.subinventory_code = FND_API.G_MISS_CHAR) THEN
2859 p_x_rsv_rec.subinventory_code := NULL;
2860 END IF;
2861 IF(p_x_rsv_rec.locator_id = FND_API.G_MISS_NUM) THEN
2862 p_x_rsv_rec.locator_id := NULL;
2863 END IF;
2864 IF(p_x_rsv_rec.lot_number = FND_API.G_MISS_CHAR) THEN
2865 p_x_rsv_rec.lot_number := NULL;
2866 END IF;
2867 p_x_rsv_rec.supply_receipt_date := NULL;
2868 --Common Detials
2869 p_x_rsv_rec.external_source_code := 'AHL';
2870 p_x_rsv_rec.external_source_line_id := 867;
2871 p_x_rsv_rec.inventory_item_id := l_mtl_req_dtls_rec.inventory_item_id;
2872 p_x_rsv_rec.serial_number := NULL;
2873 p_x_rsv_rec.ship_ready_flag := NULL;
2874 IF(p_x_rsv_rec.reservation_id = FND_API.G_MISS_NUM) THEN
2875 p_x_rsv_rec.reservation_id := NULL;
2876 END IF;
2877 p_x_rsv_rec.primary_uom_id := NULL;
2878 p_x_rsv_rec.autodetail_group_id := NULL;
2879 p_x_rsv_rec.subinventory_id := NULL;
2880 p_x_rsv_rec.lot_number_id := NULL;
2881 p_x_rsv_rec.pick_slip_number := NULL;
2882 p_x_rsv_rec.lpn_id := NULL;
2883 p_x_rsv_rec.demand_source_delivery := NULL;
2884 p_x_rsv_rec.attribute_category := NULL;
2885 p_x_rsv_rec.attribute1 := NULL;
2886 p_x_rsv_rec.attribute2 := NULL;
2887 p_x_rsv_rec.attribute3 := NULL;
2888 p_x_rsv_rec.attribute4 := NULL;
2889 p_x_rsv_rec.attribute5 := NULL;
2890 p_x_rsv_rec.attribute6 := NULL;
2891 p_x_rsv_rec.attribute7 := NULL;
2892 p_x_rsv_rec.attribute8 := NULL;
2893 p_x_rsv_rec.attribute9 := NULL;
2894 p_x_rsv_rec.attribute10 := NULL;
2895 p_x_rsv_rec.attribute11 := NULL;
2896 p_x_rsv_rec.attribute12 := NULL;
2897 p_x_rsv_rec.attribute13 := NULL;
2898 p_x_rsv_rec.attribute14 := NULL;
2899 p_x_rsv_rec.attribute15 := NULL;
2900
2901 -- Log API exit point
2902 IF (l_log_procedure >= l_log_current_level)THEN
2903 fnd_log.string
2904 (
2905 fnd_log.level_procedure,
2906 l_debug_module||'.end',
2907 'At the end of PLSQL procedure'
2908 );
2909 END IF;
2910 END INITIALIZE_CREATE_REC;
2911
2912 FUNCTION validate_reservation_id(p_reservation_id IN NUMBER) RETURN NUMBER
2913 IS
2914
2915 CURSOR get_reservation_csr (c_reservation_id IN NUMBER)
2916 IS
2917 SELECT demand_source_line_detail
2918 FROM mtl_reservations mrsv
2919 WHERE reservation_id = c_reservation_id
2920 AND mrsv.external_source_code = 'AHL';
2921
2922 l_scheduled_material_id NUMBER;
2923
2924 BEGIN
2925
2926 -- Validate the reservation id
2927 OPEN get_reservation_csr(p_reservation_id);
2928 FETCH get_reservation_csr INTO l_scheduled_material_id;
2929 CLOSE get_reservation_csr;
2930
2931 RETURN l_scheduled_material_id;
2932
2933 END validate_reservation_id;
2934
2935
2939 p_lot_number IN VARCHAR2,
2936 PROCEDURE GET_MATCHING_RESERVATION(p_scheduled_material_id IN NUMBER,
2937 p_sub_inv_code IN VARCHAR2,
2938 p_locator_id IN NUMBER,
2940 p_revision IN VARCHAR2,
2941 p_supply_source_type_id IN NUMBER,
2942 x_reservation_id OUT NOCOPY NUMBER,
2943 x_reservation_quantity OUT NOCOPY NUMBER) IS
2944 -- Declare local variables
2945 l_api_name CONSTANT VARCHAR2(30) := 'GET_MATCHING_RESERVATION';
2946 l_debug_module CONSTANT VARCHAR2(100) := 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name;
2947
2948 CURSOR get_reservation_csr IS
2949 SELECT mrsv.reservation_id, mrsv.primary_reservation_quantity
2950 FROM mtl_reservations mrsv, ahl_schedule_materials asmt
2951 WHERE mrsv.demand_source_line_detail = p_scheduled_material_id
2952 AND mrsv.external_source_code = 'AHL'
2953 AND NVL(mrsv.subinventory_code, '@@@') = NVL(p_sub_inv_code, '@@@')
2954 AND NVL(mrsv.locator_id, -99) = NVL(p_locator_id, -99)
2955 AND NVL(mrsv.revision, '@@@') = NVL(p_revision, '@@@')
2956 AND NVL(mrsv.lot_number, '@@@') = NVL(p_lot_number, '@@@')
2957 AND mrsv.demand_source_line_detail = asmt.scheduled_material_id
2958 AND mrsv.organization_id = asmt.organization_id
2959 AND mrsv.requirement_date = asmt.requested_date
2960 AND mrsv.inventory_item_id = asmt.inventory_item_id
2961 AND mrsv.supply_source_type_id = p_supply_source_type_id
2962 AND NVL(asmt.status,'') = 'ACTIVE';
2963 BEGIN
2964
2965 -- Log API exit point
2966 IF (l_log_procedure >= l_log_current_level) THEN
2967 fnd_log.string(fnd_log.level_procedure, l_debug_module||'.start',
2968 'At the start of PLSQL procedure, p_scheduled_material_id = ' || p_scheduled_material_id ||
2969 ', p_sub_inv_code = ' || p_sub_inv_code);
2970 END IF;
2971
2972 OPEN get_reservation_csr;
2973 FETCH get_reservation_csr INTO x_reservation_id, x_reservation_quantity;
2974 CLOSE get_reservation_csr;
2975
2976 -- Log API exit point
2977 IF (l_log_procedure >= l_log_current_level) THEN
2978 fnd_log.string(fnd_log.level_procedure, l_debug_module||'.end',
2979 'At the end of PLSQL procedure, x_reservation_id = ' || x_reservation_id ||
2980 ', x_reservation_quantity = ' || x_reservation_quantity);
2981 END IF;
2982 END GET_MATCHING_RESERVATION;
2983
2984 PROCEDURE CONVERT_TO_INV_TABLE_TYPE(p_mtl_reservation_rec IN mtl_reservation_rec_type ,
2985 p_serial_number_tbl IN serial_number_tbl_type,
2986 x_mtl_reservation_rec OUT NOCOPY inv_reservation_global.mtl_reservation_rec_type,
2987 x_serial_number_tbl OUT NOCOPY inv_reservation_global.serial_number_tbl_type) IS
2988 -- Declare local variables
2989 l_api_name CONSTANT VARCHAR2(30) := 'CONVERT_TO_INV_TABLE_TYPE';
2990 l_debug_module CONSTANT VARCHAR2(100) := 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name;
2991
2992 BEGIN
2993
2994 -- Log API exit point
2995 IF (l_log_procedure >= l_log_current_level) THEN
2996 fnd_log.string(fnd_log.level_procedure, l_debug_module||'.start',
2997 'At the start of PLSQL procedure, p_mtl_reservation_rec = ' ||
2998 ', p_serial_number_tbl = ' || p_serial_number_tbl.COUNT);
2999 END IF;
3000
3001 --Iterate through record and populate inv rec type
3002 IF(p_mtl_reservation_rec.inventory_item_id IS NOT NULL OR p_mtl_reservation_rec.reservation_id IS NOT NULL) THEN
3003
3004 x_mtl_reservation_rec.reservation_id := p_mtl_reservation_rec.reservation_id;
3005 x_mtl_reservation_rec.requirement_date := p_mtl_reservation_rec.requirement_date;
3006 x_mtl_reservation_rec.organization_id := p_mtl_reservation_rec.organization_id;
3007 x_mtl_reservation_rec.inventory_item_id := p_mtl_reservation_rec.inventory_item_id;
3008 x_mtl_reservation_rec.demand_source_type_id := p_mtl_reservation_rec.demand_source_type_id;
3009 x_mtl_reservation_rec.demand_source_name := p_mtl_reservation_rec.demand_source_name;
3010 x_mtl_reservation_rec.demand_source_header_id := p_mtl_reservation_rec.demand_source_header_id;
3011 x_mtl_reservation_rec.demand_source_line_id := p_mtl_reservation_rec.demand_source_line_id;
3012 x_mtl_reservation_rec.demand_source_delivery := p_mtl_reservation_rec.demand_source_delivery;
3013 x_mtl_reservation_rec.primary_uom_code := p_mtl_reservation_rec.primary_uom_code;
3014 x_mtl_reservation_rec.primary_uom_id := p_mtl_reservation_rec.primary_uom_id;
3015 x_mtl_reservation_rec.secondary_uom_code := p_mtl_reservation_rec.secondary_uom_code;
3016 x_mtl_reservation_rec.secondary_uom_id := p_mtl_reservation_rec.secondary_uom_id;
3017 x_mtl_reservation_rec.reservation_uom_code := p_mtl_reservation_rec.reservation_uom_code;
3018 x_mtl_reservation_rec.reservation_uom_id := p_mtl_reservation_rec.reservation_uom_id;
3019 x_mtl_reservation_rec.reservation_quantity := p_mtl_reservation_rec.reservation_quantity;
3020 x_mtl_reservation_rec.primary_reservation_quantity := p_mtl_reservation_rec.primary_reservation_quantity;
3021 x_mtl_reservation_rec.secondary_reservation_quantity := p_mtl_reservation_rec.secondary_reservation_quantity;
3022 x_mtl_reservation_rec.detailed_quantity := p_mtl_reservation_rec.detailed_quantity;
3023 x_mtl_reservation_rec.secondary_detailed_quantity := p_mtl_reservation_rec.secondary_detailed_quantity ;
3027 x_mtl_reservation_rec.supply_source_type_id := p_mtl_reservation_rec.supply_source_type_id;
3024 x_mtl_reservation_rec.autodetail_group_id := p_mtl_reservation_rec.autodetail_group_id;
3025 x_mtl_reservation_rec.external_source_code := p_mtl_reservation_rec.external_source_code;
3026 x_mtl_reservation_rec.external_source_line_id := p_mtl_reservation_rec.external_source_line_id;
3028 x_mtl_reservation_rec.supply_source_header_id := p_mtl_reservation_rec.supply_source_header_id ;
3029 x_mtl_reservation_rec.supply_source_line_id := p_mtl_reservation_rec.supply_source_line_id;
3030 x_mtl_reservation_rec.supply_source_name := p_mtl_reservation_rec.supply_source_name ;
3031 x_mtl_reservation_rec.supply_source_line_detail := p_mtl_reservation_rec.supply_source_line_detail;
3032 x_mtl_reservation_rec.revision := p_mtl_reservation_rec.revision ;
3033 x_mtl_reservation_rec.subinventory_code := p_mtl_reservation_rec.subinventory_code;
3034 x_mtl_reservation_rec.subinventory_id := p_mtl_reservation_rec.subinventory_id;
3035 x_mtl_reservation_rec.locator_id := p_mtl_reservation_rec.locator_id;
3036 x_mtl_reservation_rec.lot_number := p_mtl_reservation_rec.lot_number;
3040 x_mtl_reservation_rec.attribute_category := p_mtl_reservation_rec.attribute_category;
3037 x_mtl_reservation_rec.lot_number_id := p_mtl_reservation_rec.lot_number_id;
3038 x_mtl_reservation_rec.pick_slip_number := p_mtl_reservation_rec.pick_slip_number;
3039 x_mtl_reservation_rec.lpn_id := p_mtl_reservation_rec.lpn_id;
3041 x_mtl_reservation_rec.attribute1 := p_mtl_reservation_rec.attribute1;
3042 x_mtl_reservation_rec.attribute2 := p_mtl_reservation_rec.attribute2;
3043 x_mtl_reservation_rec.attribute3 := p_mtl_reservation_rec.attribute3;
3044 x_mtl_reservation_rec.attribute4 := p_mtl_reservation_rec.attribute4;
3045 x_mtl_reservation_rec.attribute5 := p_mtl_reservation_rec.attribute5;
3046 x_mtl_reservation_rec.attribute6 := p_mtl_reservation_rec.attribute6;
3047 x_mtl_reservation_rec.attribute7 := p_mtl_reservation_rec.attribute7;
3048 x_mtl_reservation_rec.attribute8 := p_mtl_reservation_rec.attribute8;
3049 x_mtl_reservation_rec.attribute9 := p_mtl_reservation_rec.attribute9;
3050 x_mtl_reservation_rec.attribute10 := p_mtl_reservation_rec.attribute10;
3051 x_mtl_reservation_rec.attribute11 := p_mtl_reservation_rec.attribute11;
3052 x_mtl_reservation_rec.attribute12 := p_mtl_reservation_rec.attribute12;
3053 x_mtl_reservation_rec.attribute13 := p_mtl_reservation_rec.attribute13;
3054 x_mtl_reservation_rec.attribute14 := p_mtl_reservation_rec.attribute14;
3055 x_mtl_reservation_rec.attribute15 := p_mtl_reservation_rec.attribute15;
3056 x_mtl_reservation_rec.ship_ready_flag := p_mtl_reservation_rec.ship_ready_flag;
3057 x_mtl_reservation_rec.staged_flag := p_mtl_reservation_rec.staged_flag;
3058 x_mtl_reservation_rec.crossdock_flag := p_mtl_reservation_rec.crossdock_flag;
3059 x_mtl_reservation_rec.crossdock_criteria_id := p_mtl_reservation_rec.crossdock_criteria_id;
3060 x_mtl_reservation_rec.demand_source_line_detail := p_mtl_reservation_rec.demand_source_line_detail;
3061 x_mtl_reservation_rec.serial_reservation_quantity := p_mtl_reservation_rec.serial_reservation_quantity;
3062 x_mtl_reservation_rec.supply_receipt_date := p_mtl_reservation_rec.supply_receipt_date;
3063 x_mtl_reservation_rec.demand_ship_date := p_mtl_reservation_rec.demand_ship_date;
3064 x_mtl_reservation_rec.project_id := p_mtl_reservation_rec.project_id;
3065 x_mtl_reservation_rec.task_id := p_mtl_reservation_rec.task_id;
3066 x_mtl_reservation_rec.orig_supply_source_type_id := p_mtl_reservation_rec.orig_supply_source_type_id;
3067 x_mtl_reservation_rec.orig_supply_source_header_id := p_mtl_reservation_rec.orig_supply_source_header_id;
3068 x_mtl_reservation_rec.orig_supply_source_line_id := p_mtl_reservation_rec.orig_supply_source_line_id;
3069 x_mtl_reservation_rec.orig_supply_source_line_detail := p_mtl_reservation_rec.orig_supply_source_line_detail;
3070 x_mtl_reservation_rec.orig_demand_source_type_id := p_mtl_reservation_rec.orig_demand_source_type_id;
3071 x_mtl_reservation_rec.orig_demand_source_header_id := p_mtl_reservation_rec.orig_demand_source_header_id;
3072 x_mtl_reservation_rec.orig_demand_source_line_id := p_mtl_reservation_rec.orig_demand_source_line_id;
3073 x_mtl_reservation_rec.orig_demand_source_line_detail := p_mtl_reservation_rec.orig_demand_source_line_detail;
3074 x_mtl_reservation_rec.serial_number := p_mtl_reservation_rec.serial_number;
3075
3076 END IF;
3077
3078 IF(p_serial_number_tbl.COUNT > 0) THEN
3079 FOR i IN p_serial_number_tbl.FIRST..p_serial_number_tbl.LAST
3080 LOOP
3081 x_serial_number_tbl(i).inventory_item_id := p_serial_number_tbl(i).inventory_item_id;
3082 x_serial_number_tbl(i).serial_number := p_serial_number_tbl(i).serial_number;
3083 END LOOP;
3084 END IF;
3085 -- Log API exit point
3086 IF (l_log_procedure >= l_log_current_level) THEN
3087 fnd_log.string(fnd_log.level_procedure, l_debug_module||'.end',
3088 'At the end of PLSQL procedure, x_mtl_reservation_rec = ' ||
3089 ', x_serial_number_tbl = ' || x_serial_number_tbl.COUNT);
3090 END IF;
3091 END CONVERT_TO_INV_TABLE_TYPE;
3092
3093
3094 ---------------------------------------------------------------------------------------------------------------------
3095 -- Declare Procedures --
3096 ---------------------------------------------------------------------------------------------------------------------
3097 -- Start of Comments --
3098 -- Procedure name : INITIALIZE_WO_UPDATE_REC
3099 -- Type : Private
3100 -- Function : To initializa the record that is to be passed into WMS Update Reservation API
3101 -- Pre-reqs :
3102 -- Standard IN Parameters :
3103 -- Standard OUT Parameters :
3104 -- INITIALIZE_CREATE_REC Parameters:
3105 -- p_rsv_rec :
3106 -- p_schedule_material_id :
3107 -- p_x_rsv_rec :
3108 -- End of Comments.
3109 ---------------------------------------------------------------------------------------------------------------------
3110 PROCEDURE INITIALIZE_WO_UPDATE_REC(
3111 p_schedule_material_id IN NUMBER,
3112 p_x_rsv_rec IN OUT NOCOPY mtl_reservation_rec_type,
3113 x_return_status OUT NOCOPY VARCHAR2
3114 )
3115 IS
3116
3117 -- Declare local variables
3118 l_api_name CONSTANT VARCHAR2(30) := 'INITIALIZE_WO_UPDATE_REC';
3119 l_debug_module CONSTANT VARCHAR2(100) := 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name;
3120
3121 -- Declare cursors
3122
3123 BEGIN
3124
3125 -- Log API exit point
3126 IF (l_log_procedure >= l_log_current_level)THEN
3127 fnd_log.string
3128 (
3129 fnd_log.level_procedure,
3130 l_debug_module||'.start',
3131 'At the start of PLSQL procedure'
3132 );
3133 END IF;
3134 -- Initialize return status to success before any code logic/validation
3135 x_return_status:= FND_API.G_RET_STS_SUCCESS;
3136
3137 IF (l_log_statement >= l_log_current_level) THEN
3138 fnd_log.string
3139 (
3140 fnd_log.level_statement,
3141 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
3142 'p_schedule_material_id is: ' || p_schedule_material_id
3143 );
3144 END IF;
3145
3146 -- Get the material requirement details
3147 OPEN get_mtl_req_dtls_csr (p_schedule_material_id);
3148 FETCH get_mtl_req_dtls_csr INTO l_mtl_req_dtls_rec;
3149 CLOSE get_mtl_req_dtls_csr;
3150
3151 -- Check the WIP entity id for demand_source_header_id
3152 IF(l_mtl_req_dtls_rec.wip_entity_id is NULL) THEN
3153 IF (l_log_statement >= l_log_current_level) THEN
3154 fnd_log.string(
3155 fnd_log.level_statement,
3159 FND_MESSAGE.set_name( 'AHL', 'AHL_MM_INV_REQ' );
3156 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
3157 'Required demand is not for a workorder/wip_entity_id, p_schedule_material_id:' ||p_schedule_material_id);
3158 END IF;
3160 FND_MSG_PUB.add;
3161 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3162 RETURN;
3163 END IF;
3164
3165 IF (l_log_statement >= l_log_current_level) THEN
3166 fnd_log.string
3167 (
3168 fnd_log.level_statement,
3169 'ahl.plsql.'||G_PKG_NAME||'.'||L_API_NAME,
3170 'INITIALIZE_WO_UPDATE_REC, l_mtl_req_dtls_rec.project_id : ' || l_mtl_req_dtls_rec.PROJECT_ID ||
3171 ', l_mtl_req_dtls_rec.task_id : ' || l_mtl_req_dtls_rec.TASK_ID
3172 );
3173 END IF;
3174
3175 --Demand Details
3176 p_x_rsv_rec.demand_source_name := 'CMRO'||'.'||'WO:'||l_mtl_req_dtls_rec.workorder_id; --||'.'||'Wip:header'||'.'||'ASM:detail'
3177 p_x_rsv_rec.demand_source_type_id := inv_reservation_global.g_source_type_wip;
3178 p_x_rsv_rec.demand_source_header_id := l_mtl_req_dtls_rec.wip_entity_id;
3179 p_x_rsv_rec.demand_source_line_id := l_mtl_req_dtls_rec.operation_sequence;
3180 p_x_rsv_rec.demand_source_line_detail := p_schedule_material_id;
3181 p_x_rsv_rec.requirement_date := l_mtl_req_dtls_rec.requested_date;
3182 p_x_rsv_rec.organization_id := l_mtl_req_dtls_rec.organization_id;
3183 P_X_RSV_REC.PRIMARY_UOM_CODE := L_MTL_REQ_DTLS_REC.UOM;
3184 P_X_RSV_REC.PROJECT_ID := L_MTL_REQ_DTLS_REC.PROJECT_ID;
3185 P_X_RSV_REC.TASK_ID := L_MTL_REQ_DTLS_REC.TASK_ID;
3186 p_x_rsv_rec.primary_reservation_quantity := 1;
3187
3188 /* IF(p_x_rsv_rec.primary_reservation_quantity = FND_API.G_MISS_NUM) THEN
3189 p_x_rsv_rec.primary_reservation_quantity := 1;
3190 END IF;
3191 */
3192 p_x_rsv_rec.reservation_uom_code := l_mtl_req_dtls_rec.uom;
3193 p_x_rsv_rec.reservation_uom_id := NULL;
3194 p_x_rsv_rec.reservation_quantity := NULL;
3195 p_x_rsv_rec.demand_ship_date := NULL;
3196 --Supply Details
3197 IF(p_x_rsv_rec.subinventory_code = FND_API.G_MISS_CHAR) THEN
3198 p_x_rsv_rec.subinventory_code := NULL;
3199 END IF;
3200 --Defaulting to inventory reservation. Otherwise, expected to be passed by the caller API.
3201
3202
3203 /* Removed extra supply details from the record
3204 IF(p_x_rsv_rec.supply_source_type_id IS NULL OR p_x_rsv_rec.supply_source_type_id = FND_API.G_MISS_NUM) THEN
3205 p_x_rsv_rec.supply_source_type_id := inv_reservation_global.g_source_type_inv;
3206 p_x_rsv_rec.supply_source_header_id := NULL;
3207 p_x_rsv_rec.supply_source_line_id := NULL;
3208 p_x_rsv_rec.supply_source_line_detail := NULL;
3209 END IF;
3210
3211 p_x_rsv_rec.supply_source_name := null;
3212
3213 IF(p_x_rsv_rec.supply_source_header_id = FND_API.G_MISS_NUM) THEN
3214 p_x_rsv_rec.supply_source_header_id := NULL;
3215 END IF;
3216 IF(p_x_rsv_rec.supply_source_line_id = FND_API.G_MISS_NUM) THEN
3217 p_x_rsv_rec.supply_source_line_id := NULL;
3218 END IF;
3219 IF(p_x_rsv_rec.supply_source_line_detail = FND_API.G_MISS_NUM) THEN
3220 p_x_rsv_rec.supply_source_line_detail := NULL;
3221 END IF; */
3222 IF(p_x_rsv_rec.revision = FND_API.G_MISS_CHAR) THEN
3223 p_x_rsv_rec.revision := NULL;
3224 END IF;
3225 IF(p_x_rsv_rec.subinventory_code = FND_API.G_MISS_CHAR) THEN
3226 p_x_rsv_rec.subinventory_code := NULL;
3227 END IF;
3228 IF(p_x_rsv_rec.locator_id = FND_API.G_MISS_NUM) THEN
3229 p_x_rsv_rec.locator_id := NULL;
3230 END IF;
3231 IF(p_x_rsv_rec.lot_number = FND_API.G_MISS_CHAR) THEN
3232 p_x_rsv_rec.lot_number := NULL;
3233 END IF;
3234 p_x_rsv_rec.supply_receipt_date := NULL;
3235 --Common Detials
3236 p_x_rsv_rec.external_source_code := 'AHL';
3237 p_x_rsv_rec.external_source_line_id := 867;
3238 p_x_rsv_rec.inventory_item_id := l_mtl_req_dtls_rec.inventory_item_id;
3239 p_x_rsv_rec.serial_number := NULL;
3240 p_x_rsv_rec.ship_ready_flag := NULL;
3241 IF(p_x_rsv_rec.reservation_id = FND_API.G_MISS_NUM) THEN
3242 p_x_rsv_rec.reservation_id := NULL;
3243 END IF;
3244 p_x_rsv_rec.primary_uom_id := NULL;
3245 p_x_rsv_rec.autodetail_group_id := NULL;
3246 p_x_rsv_rec.subinventory_id := NULL;
3247 p_x_rsv_rec.lot_number_id := NULL;
3248 p_x_rsv_rec.pick_slip_number := NULL;
3249 p_x_rsv_rec.lpn_id := NULL;
3250 p_x_rsv_rec.demand_source_delivery := NULL;
3251 p_x_rsv_rec.attribute_category := NULL;
3252 p_x_rsv_rec.attribute1 := NULL;
3253 p_x_rsv_rec.attribute2 := NULL;
3254 p_x_rsv_rec.attribute3 := NULL;
3255 p_x_rsv_rec.attribute4 := NULL;
3256 p_x_rsv_rec.attribute5 := NULL;
3257 p_x_rsv_rec.attribute6 := NULL;
3258 p_x_rsv_rec.attribute7 := NULL;
3259 p_x_rsv_rec.attribute8 := NULL;
3260 p_x_rsv_rec.attribute9 := NULL;
3261 p_x_rsv_rec.attribute10 := NULL;
3262 p_x_rsv_rec.attribute11 := NULL;
3263 p_x_rsv_rec.attribute12 := NULL;
3264 p_x_rsv_rec.attribute13 := NULL;
3265 p_x_rsv_rec.attribute14 := NULL;
3266 p_x_rsv_rec.attribute15 := NULL;
3267
3268 -- Log API exit point
3269 IF (l_log_procedure >= l_log_current_level)THEN
3270 fnd_log.string
3271 (
3272 fnd_log.level_procedure,
3273 l_debug_module||'.end',
3274 'At the end of PLSQL procedure'
3275 );
3276 END IF;
3277 END INITIALIZE_WO_UPDATE_REC;
3278
3279 END AHL_MM_RESERVATIONS_PVT;