4 -- Common constants and variables --
1 PACKAGE BODY AHL_RSV_RESERVATIONS_PVT AS
2 /* $Header: AHLVRSVB.pls 120.17.12020000.2 2012/12/07 14:05:10 sareepar ship $ */
3 ------------------------------------
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 -- Helper procedure added by skpathak on 12-NOV-2008 for bug 7241925
13 -- Gets the reservation (if any) that matches the scheduled_material_id+serial_number
14 -- If p_match_serial is 'Y', also checks if the serial is already included in the reservation
15 PROCEDURE GET_MATCHING_RESERVATION(p_scheduled_material_id IN NUMBER,
16 p_serial_number IN VARCHAR2,
17 p_match_serial IN VARCHAR2 DEFAULT 'N',
18 x_reservation_id OUT NOCOPY NUMBER,
19 x_reservation_quantity OUT NOCOPY NUMBER);
20
21
22 PROCEDURE INITIALIZE_CREATE_REC(
23 p_schedule_material_id IN NUMBER,
24 p_serial_number IN VARCHAR2,
25 x_rsv_rec OUT NOCOPY inv_reservation_global.mtl_reservation_rec_type,
26 x_return_status OUT NOCOPY VARCHAR2
27 );
28
29 ------------------------------------------------------------------------------------------------------------------
30 -- Start of Comments --
31 -- Procedure name : CREATE_RESERVATION
32 -- Type : Private
33 -- Function : Reserves the serial numbers in the p_serial_number_tbl
34 -- Pre-reqs :
35 -- Standard IN Parameters :
39 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
36 -- p_api_version IN NUMBER Required
37 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
38 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
40 -- p_module_type IN VARCHAR2 Default NULL
41 -- Standard OUT Parameters :
42 -- x_return_status OUT VARCHAR2 Required
43 -- x_msg_count OUT NUMBER Required
44 -- x_msg_data OUT VARCHAR2 Required
45
46 --
47 -- CREATE_RESERVATION Parameters:
48 -- p_scheduled_material_id : The Schedule Material Id
49 -- p_serial_number_tbl : The table of Serial Numbers to be reserved
50 -- End of Comments.
51 ------------------------------------------------------------------------------------------------------------------
52 PROCEDURE CREATE_RESERVATION(
53 p_api_version IN NUMBER,
54 p_init_msg_list IN VARCHAR2,
55 p_commit IN VARCHAR2,
56 p_validation_level IN NUMBER,
57 p_module_type IN VARCHAR2,
58 x_return_status OUT NOCOPY VARCHAR2,
59 x_msg_count OUT NOCOPY NUMBER,
60 x_msg_data OUT NOCOPY VARCHAR2,
61 p_scheduled_material_id IN NUMBER,
62 p_serial_number_tbl IN serial_number_tbl_type
63 )
64 IS
65 -- Declare local variables
66 l_api_name CONSTANT VARCHAR2(30) := 'create_reservation';
67 l_api_version CONSTANT NUMBER := 1.0;
68 l_init_msg_list VARCHAR2(1) := 'F';
69 l_return_status VARCHAR2(1);
70 l_msg_count NUMBER;
71 l_msg_data VARCHAR2(2000);
72 l_debug_module CONSTANT VARCHAR2(100) := 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name;
73
74 l_sn_number NUMBER := 0; -- Number of serial numbers to be reserved
75
76
77 CURSOR get_mtl_req_dtls_csr (c_scheduled_material_id IN NUMBER) IS
78 SELECT asmt.organization_id, asmt.requested_date, asmt.uom,
79 nvl(asmt.requested_quantity,0) requested_quantity, asmt.inventory_item_id,
80 asmt.object_version_number, nvl(asmt.reserved_quantity,0) reserved_quantity,
81 nvl(asmt.completed_quantity,0) completed_quantity
82 FROM ahl_material_requirements_v asmt
83 WHERE asmt.schedule_material_id = c_scheduled_material_id;
84
85 l_mtl_req_dtls_rec get_mtl_req_dtls_csr%ROWTYPE;
86
87 l_create_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
88 l_serial_number_tbl inv_reservation_global.serial_number_tbl_type;
89
90 -- To fetch the instance details when you have the inventory item id, serial number and the organization id
91 -- inventory item id is from the Ahl Schedule Materials table
92 -- organization id from the Ahl Schedule Materials table
93 CURSOR get_instance_dtls_csr (c_inventory_itme_id IN NUMBER,
94 c_serial_number IN VARCHAR2,
95 c_organization_id IN NUMBER)
96 IS
97 SELECT csi.instance_id,
98 msn.serial_number,
99 csi.inv_subinventory_name subinventory_code
100 FROM csi_item_instances csi,
101 mtl_serial_numbers msn
102 WHERE trunc(sysdate) >= trunc(nvl(CSI.active_start_date,sysdate))
103 AND trunc(sysdate) < trunc(nvl(CSI.active_end_date,sysdate+1))
104 AND msn.current_status = 3 -- inventory
105 AND msn.reservation_id is null
106 AND (msn.group_mark_id is null or msn.group_mark_id = -1)
107 AND csi.inventory_item_id = c_inventory_itme_id
108 AND csi.serial_number = c_serial_number
109 AND csi.last_vld_organization_id = c_organization_id
110 AND csi.inventory_item_id = msn.inventory_item_id
111 AND csi.serial_number = msn.serial_number;
112
113 l_instance_details_rec get_instance_dtls_csr%ROWTYPE;
114
115 -- Cursor get_reservation_csr removed by skpathak on 12-NOV-2008 for bug 7241925
116 -- in favor of call to the new helper procedure GET_MATCHING_RESERVATION
117 /**
118 CURSOR get_reservation_csr (c_scheduled_material_id IN NUMBER, c_subinventory_code IN VARCHAR2)
119 IS
120 SELECT mrsv.reservation_id, mrsv.primary_reservation_quantity
121 FROM mtl_reservations mrsv,ahl_schedule_materials asmt
122 WHERE mrsv.demand_source_line_detail = c_scheduled_material_id
123 AND external_source_code = 'AHL'
124 AND subinventory_code = c_subinventory_code
125 AND mrsv.demand_source_line_detail = asmt.scheduled_material_id
126 AND mrsv.organization_id = asmt.organization_id
127 AND mrsv.requirement_date = asmt.requested_date
128 AND mrsv.inventory_item_id = asmt.inventory_item_id;
129 **/
130
131 l_reservation_id NUMBER;
132 l_reserved_quantity NUMBER;
133
134
135 l_x_serial_number_tbl inv_reservation_global.serial_number_tbl_type;
136 l_x_quantity_reserved NUMBER;
137 l_x_reservation_id NUMBER;
138
139 -- for updating the reservations
140 l_from_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
141 l_to_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
142 l_to_serial_number_tbl inv_reservation_global.serial_number_tbl_type;
143
144 BEGIN
145 -- Standard start of API savepoint
146 SAVEPOINT CREATE_RESERVATION_PVT;
147 -- Initialize return status to success before any code logic/validation
148 x_return_status:= FND_API.G_RET_STS_SUCCESS;
149
150
151 -- Standard call to check for call compatibility
155 END IF;
152 IF NOT FND_API.COMPATIBLE_API_CALL (l_api_version, p_api_version, l_api_name, G_PKG_NAME)
153 THEN
154 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
156
157
158 -- Initialize message list if p_init_msg_list = FND_API.G_TRUE
159 IF FND_API.TO_BOOLEAN(p_init_msg_list)
160 THEN
161 FND_MSG_PUB.INITIALIZE;
162 END IF;
163
164 -- Log API entry point
165 IF (l_log_procedure >= l_log_current_level) THEN
166 fnd_log.string
167 (
168 fnd_log.level_procedure,
169 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||'.begin',
170 'At the start of PL SQL procedure '
171 );
172 END IF;
173
174
175 -- Validate to make sure that the Serial Number table is not empty
176 l_sn_number := p_serial_number_tbl.COUNT;
177 IF l_sn_number = 0 THEN
178 FND_MESSAGE.set_name( 'AHL', 'AHL_RSV_SNQTY_GTR_ZRO' );
179 FND_MSG_PUB.add;
180 -- log the error
181 IF (l_log_error >= l_log_current_level) THEN
182 fnd_log.string
183 (
184 fnd_log.level_error,
185 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
186 'Number of Serial Numbers to be reserved equal to ZERO'
187 );
188 END IF;
189 RAISE FND_API.G_EXC_ERROR;
190 END IF; -- l_sn_number = 0
191
192 -- Get the Material Requirements details
193 OPEN get_mtl_req_dtls_csr(p_scheduled_material_id);
194 FETCH get_mtl_req_dtls_csr INTO l_mtl_req_dtls_rec;
195 IF get_mtl_req_dtls_csr%NOTFOUND THEN
196 FND_MESSAGE.set_name( 'AHL', 'AHL_RSV_INVLD_MAT_REQ' );
197 FND_MSG_PUB.add;
198 -- log the error
199 IF (l_log_error >= l_log_current_level) THEN
200 fnd_log.string
201 (
202 fnd_log.level_error,
203 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
204 'get_mtl_req_dtls_csr, did not fetch any records'
205 );
206 END IF;
207 CLOSE get_mtl_req_dtls_csr;
208 RAISE FND_API.G_EXC_ERROR;
209 END IF; -- Material Requirement details not found
210 CLOSE get_mtl_req_dtls_csr;
211
212
213 -- Validate whether the sum of serial numbers to reserve + already issued + reserved quantities
214 -- is not more than the initially requested quantity
215 IF l_sn_number + l_mtl_req_dtls_rec.completed_quantity +
216 l_mtl_req_dtls_rec.reserved_quantity > l_mtl_req_dtls_rec.requested_quantity THEN
217 FND_MESSAGE.set_name( 'AHL', 'AHL_RSV_QTY_EXCDS_REQSTD' );
218 FND_MSG_PUB.add;
219 -- log the error
220 IF (l_log_error >= l_log_current_level) THEN
221 fnd_log.string
222 (
223 fnd_log.level_error,
224 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
225 'Number of serial numbers + Completed Qty + Reserved Qty is more than Requested Qty '
226 );
227 END IF;
228 RAISE FND_API.G_EXC_ERROR;
229 END IF; -- sum of number of serial nos to be reserved ,completed qty and reserved qty is more than requested qty
230
231 -- For all the serial numbers that need to be reserved
232 FOR i IN p_serial_number_tbl.FIRST..p_serial_number_tbl.LAST
233 LOOP
234 -- Initialize the record to be send to the WMS package
235 Initialize_create_rec( p_scheduled_material_id, -- the schedule material id
236 p_serial_number_tbl(i).serial_number, -- the serial number
237 l_create_rsv_rec, -- record to be passed ti the WMS packages
238 l_return_status); -- return status
239 -- get the instance id,serial number and the subinventory code
240 OPEN get_instance_dtls_csr( l_mtl_req_dtls_rec.inventory_item_id,
241 p_serial_number_tbl(i).serial_number,
242 l_mtl_req_dtls_rec.organization_id);
243 FETCH get_instance_dtls_csr INTO l_instance_details_rec;
244 IF get_instance_dtls_csr%NOTFOUND THEN
245 FND_MESSAGE.set_name( 'AHL', 'AHL_RSV_INVLD_SL_NUM' );
246 FND_MESSAGE.Set_Token('SERIALNUMBER',p_serial_number_tbl(i).serial_number);
247 FND_MSG_PUB.add;
248 -- log the error
249 IF (l_log_error >= l_log_current_level) THEN
250 fnd_log.string
251 (
252 fnd_log.level_error,
253 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
254 'get_instance_dtls_csr, did not fetch any records'
255 );
256 END IF;
257 CLOSE get_instance_dtls_csr;
258 RAISE FND_API.G_EXC_ERROR;
259 END IF; -- get_instance_dtls_csr%NOTFOUND
260 CLOSE get_instance_dtls_csr;
261
262 l_reservation_id := null;
263 l_reserved_quantity := null;
264
265 -- Get the reservation id and the quantity already reserved
266 -- Changed by skpathak on 12-NOV-2008 for bug 7241925
267 -- Can update a reservation to add one more serial only if all of these match:
268 -- Org, Item, Subinventory, Locator, Revision, Lot and LPN
269 /**
270 OPEN get_reservation_csr(p_scheduled_material_id,l_instance_details_rec.subinventory_code) ;
271 FETCH get_reservation_csr INTO l_reservation_id,l_x_quantity_reserved;
272 CLOSE get_reservation_csr;
273 **/
274 GET_MATCHING_RESERVATION(p_scheduled_material_id => p_scheduled_material_id,
275 p_serial_number => p_serial_number_tbl(i).serial_number,
276 p_match_serial => 'N',
280 IF (l_log_statement >= l_log_current_level) THEN
277 x_reservation_id => l_reservation_id,
278 x_reservation_quantity => l_x_quantity_reserved);
279
281 fnd_log.string(fnd_log.level_statement, l_debug_module,
282 'GET_MATCHING_RESERVATION returned l_reservation_id = ' || l_reservation_id ||
283 ', l_x_quantity_reserved = ' || l_x_quantity_reserved);
284 END IF;
285 -- End Changes by skpathak on 12-NOV-2008 for bug 7241925
286
287 -- Call WMS API to create reservation if none has been created for this material requirement
288 -- in a particular subinventory, otherwise
289 -- update the current reservation by adding more serial number.
290
291 IF l_reservation_id IS NULL THEN
292 -- populate p_rsv_rec
293 l_create_rsv_rec.primary_reservation_quantity := 1;
294 l_create_rsv_rec.subinventory_code := l_instance_details_rec.subinventory_code;
295 l_serial_number_tbl(1).serial_number := p_serial_number_tbl(i).serial_number;
296 -- Added by jaramana on June 29, 2005
297 l_serial_number_tbl(1).inventory_item_id := l_mtl_req_dtls_rec.inventory_item_id;
298
299 IF (l_log_statement >= l_log_current_level) THEN
300 fnd_log.string
301 (
302 fnd_log.level_statement,
303 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
304 'Calling WMS api:inv_reservation_pub.create_reservation'
305 );
306 END IF;
307
308 inv_reservation_pub.create_reservation
309 (
310 p_api_version_number => l_api_version,
311 p_init_msg_lst => l_init_msg_list,
312 x_return_status => l_return_status,
313 x_msg_count => l_msg_count,
314 x_msg_data => l_msg_data,
315 p_rsv_rec => l_create_rsv_rec,
316 p_serial_number => l_serial_number_tbl,
317 x_serial_number => l_x_serial_number_tbl,
318 x_quantity_reserved => l_x_quantity_reserved,
319 x_reservation_id => l_x_reservation_id
320 );
321 ELSE -- l_reservation_id IS NOT NULL
322 -- populate p_original_rsv_rec
323 l_from_rsv_rec.reservation_id := l_reservation_id;
324 -- populate p_rsv_rec
325 l_to_rsv_rec.reservation_id := l_reservation_id;
326 l_to_rsv_rec.primary_reservation_quantity := l_x_quantity_reserved +1;
327 l_to_serial_number_tbl(1).serial_number := p_serial_number_tbl(i).serial_number;
328 l_to_serial_number_tbl(1).inventory_item_id := l_mtl_req_dtls_rec.inventory_item_id;
329
330 -- initialize the table
331 l_serial_number_tbl.DELETE;
332
333 IF (l_log_statement >= l_log_current_level) THEN
334 fnd_log.string
335 (
336 fnd_log.level_statement,
337 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
338 'Calling WMS api:inv_reservation_pub.update_reservation'
339 );
340 END IF;
341 inv_reservation_pub.update_reservation
342 (
343 p_api_version_number => l_api_version,
344 p_init_msg_lst => l_init_msg_list,
345 x_return_status => l_return_status,
346 x_msg_count => l_msg_count,
347 x_msg_data => l_msg_data,
348 p_original_rsv_rec => l_from_rsv_rec,
349 p_to_rsv_rec => l_to_rsv_rec,
350 p_original_serial_number => l_serial_number_tbl,
351 p_to_serial_number => l_to_serial_number_tbl
352 );
353 END IF; -- IF l_reservation_id IS NULL
354
355 -- Check the error status
356 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
357 -- log the error
358 IF (l_log_error >= l_log_current_level) THEN
359 fnd_log.string
360 (
361 fnd_log.level_error,
362 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
363 'Call to WMS returned Unexpected Error'
364 );
365 END IF;
366 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
367 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
368 -- log the error
369 IF (l_log_error >= l_log_current_level) THEN
370 fnd_log.string
371 (
372 fnd_log.level_error,
373 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
374 'Call to WMS returned Expected Error'
375 );
376 END IF;
377 RAISE FND_API.G_EXC_ERROR;
378 END IF;
379 END LOOP; -- FOR i IN p_serial_number_tbl.FIRST..p_serial_number_tbl.LAST
380
381 -- Check Error Message stack.
382 x_msg_count := FND_MSG_PUB.count_msg;
383 IF x_msg_count > 0
384 THEN
385 -- log the error
386 IF (l_log_error >= l_log_current_level) THEN
387 fnd_log.string
388 (
389 fnd_log.level_error,
390 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
391 'Call to WMS returned Errors in x_msg_count'
392 );
393 END IF;
394 RAISE FND_API.G_EXC_ERROR;
395 END IF;
396
397 -- Commit if p_commit = FND_API.G_TRUE
398 IF FND_API.TO_BOOLEAN(p_commit)
399 THEN
400 COMMIT WORK;
401 IF (l_log_statement >= l_log_current_level) THEN
402 fnd_log.string
403 (
404 fnd_log.level_statement,
405 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
409 END IF;
406 'Committed'
407 );
408 END IF;
410
411 -- Standard call to get message count and if count is 1, get message info
412 FND_MSG_PUB.count_and_get
413 (
414 p_count => x_msg_count,
415 p_data => x_msg_data,
416 p_encoded => FND_API.G_FALSE
417 );
418
419 -- Log API exit point
420 IF (l_log_procedure >= l_log_current_level)THEN
421 fnd_log.string
422 (
423 fnd_log.level_procedure,
424 l_debug_module||'.end',
425 'At the end of PLSQL procedure'
426 );
427 END IF;
428
429 EXCEPTION
430 WHEN FND_API.G_EXC_ERROR THEN
431 ROLLBACK TO CREATE_RESERVATION_PVT;
432 x_return_status := FND_API.G_RET_STS_ERROR;
433 FND_MSG_PUB.count_and_get
434 (
435 p_count => x_msg_count,
436 p_data => x_msg_data,
437 p_encoded => FND_API.G_FALSE
438 );
439
440 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
441 ROLLBACK TO CREATE_RESERVATION_PVT;
442 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
443 FND_MSG_PUB.count_and_get
444 (
445 p_count => x_msg_count,
446 p_data => x_msg_data,
447 p_encoded => FND_API.G_FALSE
448 );
449
450 WHEN OTHERS THEN
451 ROLLBACK TO CREATE_RESERVATION_PVT;
452 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
453 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
454 THEN
455 FND_MSG_PUB.add_exc_msg
456 (
457 p_pkg_name => G_PKG_NAME,
458 p_procedure_name => 'create_reservation',
459 p_error_text => SUBSTR(SQLERRM,1,240)
460 );
461 END IF;
462 FND_MSG_PUB.count_and_get
463 (
464 p_count => x_msg_count,
465 p_data => x_msg_data,
466 p_encoded => FND_API.G_FALSE
467 );
468 END CREATE_RESERVATION;
469
470 ------------------------------------------------------------------------------------------------------------------
471 -- Declare Procedure --
472 ------------------------------------------------------------------------------------------------------------------
473 -- Start of Comments --
474 -- Procedure name : UPDATE_RESERVATION
475 -- Type : Private
476 -- Function : Updates reservation for serial numbers in the p_serial_number_tbl
477 -- Pre-reqs :
478 -- Standard IN Parameters :
479 -- p_api_version IN NUMBER Required
480 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
481 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
482 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
483 -- p_module_type IN VARCHAR2 Default NULL
484 -- Standard OUT Parameters :
485 -- x_return_status OUT VARCHAR2 Required
486 -- x_msg_count OUT NUMBER Required
487 -- x_msg_data OUT VARCHAR2 Required
488
489 --
490 -- CREATE_RESERVATION Parameters:
491 -- p_scheduled_material_id : The Schedule Material Id
492 -- p_serial_number_tbl : The table of Serial Numbers to be reserved
493 -- End of Comments.
494 ------------------------------------------------------------------------------------------------------------------
495 PROCEDURE UPDATE_RESERVATION(
496 p_api_version IN NUMBER,
497 p_init_msg_list IN VARCHAR2,
498 p_commit IN VARCHAR2,
499 p_validation_level IN NUMBER,
500 p_module_type IN VARCHAR2,
501 x_return_status OUT NOCOPY VARCHAR2,
502 x_msg_count OUT NOCOPY NUMBER,
503 x_msg_data OUT NOCOPY VARCHAR2,
504 p_scheduled_material_id IN NUMBER ,
505 p_requested_date IN DATE)
506 IS
507 -- Declare local variables
508 l_api_name CONSTANT VARCHAR2(30) := 'update_reservation';
509 l_debug_module CONSTANT VARCHAR2(100) := 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name;
510
511 l_api_version CONSTANT NUMBER := 1.0;
512 l_init_msg_list VARCHAR2(1) := 'F';
513 l_return_status VARCHAR2(1);
514 l_msg_count NUMBER;
515 l_msg_data VARCHAR2(2000);
516
517
518 l_requested_date DATE;
519 l_reservation_id NUMBER;
520 l_x_quantity_reserved NUMBER := NULL;
521 l_from_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
522 l_to_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
523 l_x_serial_number_tbl inv_reservation_global.serial_number_tbl_type;
524 l_to_serial_number_tbl inv_reservation_global.serial_number_tbl_type;
525 l_from_serial_number_tbl inv_reservation_global.serial_number_tbl_type;
526
527 -- Variables to check the log level according to the coding standards
528 l_dbg_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
529 l_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
530
531
532 -- Declare cursors
533 CURSOR get_mtl_req_dtls_csr (c_scheduled_material_id IN NUMBER)
534 IS
535 SELECT asmt.requested_date
536 FROM ahl_schedule_materials asmt
537 WHERE asmt.scheduled_material_id = c_scheduled_material_id;
538
539 CURSOR get_reservation_csr (c_scheduled_material_id IN NUMBER)
540 IS
544 AND mrsv.demand_source_line_detail = asmt.scheduled_material_id
541 SELECT reservation_id
542 FROM mtl_reservations mrsv,ahl_schedule_materials asmt
543 WHERE mrsv.demand_source_line_detail = c_scheduled_material_id
545 AND mrsv.organization_id = asmt.organization_id
546 AND mrsv.requirement_date = asmt.requested_date
547 AND mrsv.inventory_item_id = asmt.inventory_item_id
548 AND mrsv.external_source_code = 'AHL';
549
550 BEGIN
551 -- Standard start of API savepoint
552 SAVEPOINT UPDATE_RESERVATION_PVT;
553
554 -- Initialize return status to success before any code logic/validation
555 x_return_status:= FND_API.G_RET_STS_SUCCESS;
556
557 -- Standard call to check for call compatibility
558 IF NOT FND_API.COMPATIBLE_API_CALL (l_api_version, p_api_version, l_api_name, G_PKG_NAME)
559 THEN
560 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
561 END IF;
562
563 -- Initialize message list if p_init_msg_list = FND_API.G_TRUE
564 IF FND_API.TO_BOOLEAN(p_init_msg_list)
565 THEN
566 FND_MSG_PUB.INITIALIZE;
567 END IF;
568
569 -- Log API entry point
570 IF (l_log_procedure >= l_log_current_level)THEN
571 fnd_log.string
572 (
573 fnd_log.level_procedure,
574 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||'.begin',
575 'At the start of PL SQL procedure '
576 );
577 END IF;
578
579
580 -- Validate the schedule material id
581 IF p_scheduled_material_id IS NULL THEN
582 FND_MESSAGE.set_name( 'AHL', 'AHL_RSV_INVLD_MAT_REQ' );
583 FND_MSG_PUB.add;
584 -- log the error
585 IF (l_log_error >= l_log_current_level) THEN
586 fnd_log.string
587 (
588 fnd_log.level_error,
589 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||'.begin',
590 'schedule material id is null'
591 );
592 END IF;
593 RAISE FND_API.G_EXC_ERROR;
594 END IF; -- IF p_scheduled_material_id IS NULL
595
596 -- Validate the schedule material id
597 OPEN get_mtl_req_dtls_csr(p_scheduled_material_id);
598 FETCH get_mtl_req_dtls_csr INTO l_requested_date;
599 IF get_mtl_req_dtls_csr%NOTFOUND THEN
600 FND_MESSAGE.set_name( 'AHL', 'AHL_RSV_INVLD_MAT_REQ' );
601 FND_MSG_PUB.add;
602 -- log the error
603 IF (l_log_error >= l_log_current_level) THEN
604 fnd_log.string
605 (
606 fnd_log.level_error,
607 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||'.begin',
608 'Invalid Material Requirement ID'
609 );
610 END IF;
611 CLOSE get_mtl_req_dtls_csr;
612 RAISE FND_API.G_EXC_ERROR;
613 END IF; -- IF get_mtl_req_dtls_csr%NOTFOUND
614 CLOSE get_mtl_req_dtls_csr;
615
616 -- if the requested date is null, throw error
617 IF p_requested_date IS NULL THEN
618 FND_MESSAGE.set_name( 'AHL', 'AHL_RSV_INVLD_REQ_DATE' );
619 FND_MSG_PUB.add;
620 -- log the error
621 IF (l_log_error >= l_log_current_level) THEN
622 fnd_log.string
623 (
624 fnd_log.level_error,
625 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||'.begin',
626 'Requested Date is null'
627 );
628 END IF;
629 RAISE FND_API.G_EXC_ERROR;
630 END IF; --IF p_requested_date IS NULL
631
632 -- if the dates are the same, no need to do anything
633 IF p_requested_date = l_requested_date THEN
634 IF (l_log_statement >= l_log_current_level) THEN
635 fnd_log.string
636 (
637 fnd_log.level_statement,
638 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||'.begin',
639 'No change in dates, returning to caller'
640 );
641 END IF;
642 RETURN;
643 END IF; --IF p_requested_date = l_requested_date
644
645 -- get all the reservations for this scheduled material id
646 OPEN get_reservation_csr(p_scheduled_material_id);
647 LOOP
648 FETCH get_reservation_csr INTO l_reservation_id;
649 EXIT WHEN get_reservation_csr%NOTFOUND;
650
651 l_from_rsv_rec.reservation_id := l_reservation_id;
652 l_to_rsv_rec.reservation_id := l_reservation_id;
653 l_to_rsv_rec.requirement_date:= p_requested_date;
654
655 /*
656 l_to_serial_number_tbl(1).serial_number := p_serial_number_tbl(i).serial_number;
657 l_to_serial_number_tbl(1).inventory_item_id := l_mtl_req_dtls_rec.inventory_item;
658 */
659 IF (l_log_statement >= l_log_current_level) THEN
660 fnd_log.string
661 (
662 fnd_log.level_statement,
663 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
664 'Calling WMS api:inv_reservation_pub.update_reservation'
665 );
666 END IF;
667 -- Call WMS Update reservation API
668 inv_reservation_pub.update_reservation
669 (
670 p_api_version_number => l_api_version,
671 p_init_msg_lst => l_init_msg_list,
672 x_return_status => l_return_status,
673 x_msg_count => l_msg_count,
674 x_msg_data => l_msg_data,
675 p_original_rsv_rec => l_from_rsv_rec,
676 p_to_rsv_rec => l_to_rsv_rec,
677 p_original_serial_number => l_from_serial_number_tbl,
678 p_to_serial_number => l_to_serial_number_tbl
679 );
680
681 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
682 -- log the error
686 fnd_log.level_error,
683 IF (l_log_error >= l_log_current_level) THEN
684 fnd_log.string
685 (
687 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
688 'Call to WMS returned Unexpected Error'
689 );
690 END IF;
691 CLOSE get_reservation_csr;
692 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
693 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
694 -- log the error
695 IF (l_log_error >= l_log_current_level) THEN
696 fnd_log.string
697 (
698 fnd_log.level_error,
699 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
700 'Call to WMS returned Expected Error'
701 );
702 END IF;
703 CLOSE get_reservation_csr;
704 RAISE FND_API.G_EXC_ERROR;
705 END IF;
706 END LOOP; -- All the reservations for this material requiement id
707
708 -- Check Error Message stack.
709 x_msg_count := FND_MSG_PUB.count_msg;
710 IF x_msg_count > 0
711 THEN
712 -- log the error
713 IF (l_log_error >= l_log_current_level) THEN
714 fnd_log.string
715 (
716 fnd_log.level_error,
717 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
718 'Call to WMS returned Errors in x_msg_count'
719 );
720 END IF;
721 RAISE FND_API.G_EXC_ERROR;
722 END IF;
723
724 -- Commit if p_commit = FND_API.G_TRUE
725 IF FND_API.TO_BOOLEAN(p_commit)
726 THEN
727 COMMIT WORK;
728 IF (l_log_statement >= l_log_current_level) THEN
729 fnd_log.string
730 (
731 fnd_log.level_statement,
732 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
733 'Committed'
734 );
735 END IF;
736 END IF;
737
738 -- Standard call to get message count and if count is 1, get message info
739 FND_MSG_PUB.count_and_get
740 (
741 p_count => x_msg_count,
742 p_data => x_msg_data,
743 p_encoded => FND_API.G_FALSE
744 );
745
746 -- Log API exit point
747 IF (l_log_procedure >= l_log_current_level)THEN
748 fnd_log.string
749 (
750 fnd_log.level_procedure,
751 l_debug_module||'.end',
752 'At the end of PLSQL procedure'
753 );
754 END IF;
755 EXCEPTION
756 WHEN FND_API.G_EXC_ERROR THEN
757 ROLLBACK TO UPDATE_RESERVATION_PVT;
758 x_return_status := FND_API.G_RET_STS_ERROR;
759 FND_MSG_PUB.count_and_get
760 (
761 p_count => x_msg_count,
762 p_data => x_msg_data,
763 p_encoded => FND_API.G_FALSE
764 );
765
766 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
767 ROLLBACK TO UPDATE_RESERVATION_PVT;
768 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
769 FND_MSG_PUB.count_and_get
770 (
771 p_count => x_msg_count,
772 p_data => x_msg_data,
773 p_encoded => FND_API.G_FALSE
774 );
775
776 WHEN OTHERS THEN
777 ROLLBACK TO UPDATE_RESERVATION_PVT;
778 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
779 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
780 THEN
781 FND_MSG_PUB.add_exc_msg
782 (
783 p_pkg_name => G_PKG_NAME,
784 p_procedure_name => 'update_reservation',
785 p_error_text => SUBSTR(SQLERRM,1,240)
786 );
787 END IF;
788 FND_MSG_PUB.count_and_get
789 (
790 p_count => x_msg_count,
791 p_data => x_msg_data,
792 p_encoded => FND_API.G_FALSE
793 );
794 END UPDATE_RESERVATION;
795
796 ---------------------------------------------------------------------------------------------------------------------
797 -- Declare Procedures --
798 ---------------------------------------------------------------------------------------------------------------------
799 -- Start of Comments --
800 -- Procedure name : DELETE_RESERVATION
801 -- Type : Private
802 -- Function : API to delete all the reservation made for a requirement
803 -- Pre-reqs :
804 -- Standard IN Parameters :
805 -- p_api_version IN NUMBER Required
806 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
807 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
808 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
809 -- p_module_type IN VARCHAR2 Default NULL
810 -- Standard OUT Parameters :
811 -- x_return_status OUT VARCHAR2 Required
812 -- x_msg_count OUT NUMBER Required
813 -- x_msg_data OUT VARCHAR2 Required
814
815 --
816 -- DELETE_RESERVATION Parameters:
817 -- p_scheduled_material_id : The Schedule Material Id
818 -- p_sub_inventory_code : If not null then only reservations from this subinventory will be deleted, if null all the reservations will be deleted.
819 -- End of Comments.
820 ---------------------------------------------------------------------------------------------------------------------
821 PROCEDURE DELETE_RESERVATION(
822 p_api_version IN NUMBER := 1.0,
823 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
824 p_commit IN VARCHAR2 := FND_API.G_FALSE,
828 x_msg_count OUT NOCOPY NUMBER,
825 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
826 p_module_type IN VARCHAR2,
827 x_return_status OUT NOCOPY VARCHAR2,
829 x_msg_data OUT NOCOPY VARCHAR2,
830 p_scheduled_material_id IN NUMBER ,
831 p_sub_inventory_code IN VARCHAR2 := NULL,
832 p_serial_number IN VARCHAR2 := NULL
833 )
834 IS
835
836 -- Declare local variables
837 l_api_name CONSTANT VARCHAR2(30) := 'delete_reservation';
838 l_debug_module CONSTANT VARCHAR2(100) := 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name;
839
840 l_api_version CONSTANT NUMBER := 1.0;
841 l_init_msg_list VARCHAR2(1) := 'F';
842 l_return_status VARCHAR2(1);
843 l_msg_count NUMBER;
844 l_msg_data VARCHAR2(2000);
845
846
847 l_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
848 l_serial_number_tbl inv_reservation_global.serial_number_tbl_type;
849 l_total_relieved_qty NUMBER :=0;
850
851 l_ret_value NUMBER;
852 l_reservation_id NUMBER;
853
854 -- Variables to check the log level according to the coding standards
855 l_dbg_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
856 l_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
857
858 l_temp NUMBER;
859
860
861 -- Declare cursors
862 /*CURSOR get_mtl_req_dtls_csr (c_scheduled_material_id IN NUMBER)
863 IS
864 SELECT 1
865 FROM ahl_schedule_materials asmt
866 WHERE asmt.scheduled_material_id = c_scheduled_material_id;
867 */
868
869 -- AnRaj: Added a join with ahl_schedule_materials and further where conditions to remove the FTS
870 -- on mtl_reservations
871 CURSOR get_reservation_csr (c_scheduled_material_id IN NUMBER, c_subinventory_code IN VARCHAR2)
872 IS
873 SELECT reservation_id
874 FROM mtl_reservations mrsv,ahl_schedule_materials asmt
875 WHERE mrsv.demand_source_line_detail = c_scheduled_material_id
876 AND mrsv.external_source_code = 'AHL'
877 AND (c_subinventory_code IS NULL OR mrsv.subinventory_code = c_subinventory_code)
878 AND mrsv.demand_source_line_detail = asmt.scheduled_material_id
879 AND mrsv.organization_id = asmt.organization_id
880 AND mrsv.requirement_date = asmt.requested_date
881 AND mrsv.inventory_item_id = asmt.inventory_item_id;
882 BEGIN
883 -- Standard start of API savepoint
884 SAVEPOINT DELETE_RESERVATION_PVT;
885
886 -- Initialize return status to success before any code logic/validation
887 x_return_status:= FND_API.G_RET_STS_SUCCESS;
888
889 -- Standard call to check for call compatibility
890 IF NOT FND_API.COMPATIBLE_API_CALL (l_api_version, p_api_version, l_api_name, G_PKG_NAME)
891 THEN
892 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
893 END IF;
894
895 -- Initialize message list if p_init_msg_list = FND_API.G_TRUE
896 IF FND_API.TO_BOOLEAN(p_init_msg_list)
897 THEN
898 FND_MSG_PUB.INITIALIZE;
899 END IF;
900
901 -- Log API entry point
902 IF (l_log_procedure >= l_log_current_level)THEN
903 fnd_log.string
904 (
905 fnd_log.level_procedure,
906 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||'.begin',
907 'At the start of PL SQL procedure '
908 );
909 END IF;
910
911 -- Validate the schedule material id and p_sub_inventory_code
912 IF p_scheduled_material_id IS NULL THEN
913 FND_MESSAGE.set_name( 'AHL', 'AHL_RSV_INVLD_MAT_REQ' );
914 FND_MSG_PUB.add;
915 RAISE FND_API.G_EXC_ERROR;
916 END IF;
917
918 -- log the p_scheduled_material_id and the p_sub_inventory_code
919 IF (l_log_statement >= l_log_current_level) THEN
920 fnd_log.string
921 (
922 fnd_log.level_statement,
923 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
924 'p_scheduled_material_id' || p_scheduled_material_id
925 );
926 fnd_log.string
927 (
928 fnd_log.level_statement,
929 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
930 'p_sub_inventory_code' || p_sub_inventory_code
931 );
932 END IF;
933
934 -- validate whehther the scheduled material id is valid
935 BEGIN
936 SELECT 1
937 INTO l_temp
938 FROM ahl_schedule_materials
939 WHERE scheduled_material_id = p_scheduled_material_id;
940 EXCEPTION
941 WHEN NO_DATA_FOUND THEN
942 FND_MESSAGE.Set_Name('AHL','AHL_RSV_INVLD_MAT_REQ');
943 FND_MSG_PUB.ADD;
944 -- log the error
945 IF (l_log_error >= l_log_current_level) THEN
946 fnd_log.string
947 (
948 fnd_log.level_error,
949 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
950 'Invalid Material Requirement ID'
951 );
952 END IF;
953 RAISE FND_API.G_EXC_ERROR;
954 END;
955
956 -- Begin Changes by skpathak on 12-NOV-2008 for bug 7241925
957 IF (p_serial_number IS NOT NULL) THEN
958 -- Delete based on the serial number
959 GET_MATCHING_RESERVATION(p_scheduled_material_id => p_scheduled_material_id,
960 p_serial_number => p_serial_number,
961 p_match_serial => 'Y', -- Match reservation with serial
965 IF (l_log_statement >= l_log_current_level) THEN
962 x_reservation_id => l_reservation_id,
963 x_reservation_quantity => l_temp);
964
966 fnd_log.string(fnd_log.level_statement, l_debug_module,
967 'GET_MATCHING_RESERVATION returned l_reservation_id = ' || l_reservation_id);
968 END IF;
969
970 IF (l_reservation_id IS NOT NULL) THEN
971 IF (l_log_statement >= l_log_current_level) THEN
972 fnd_log.string(fnd_log.level_statement, l_debug_module,
973 'About to Call inv_reservation_pub.delete_reservation with l_reservation_id: ' || l_reservation_id);
974 END IF;
975
976 -- Assign the reservation id to be deleted
977 l_rsv_rec.reservation_id := l_reservation_id;
978 -- Call the WMS api
979 inv_reservation_pub.delete_reservation
980 (
981 p_api_version_number => l_api_version,
982 p_init_msg_lst => l_init_msg_list,
983 x_return_status => l_return_status,
984 x_msg_count => l_msg_count,
985 x_msg_data => l_msg_data,
986 p_rsv_rec => l_rsv_rec,
987 p_serial_number => l_serial_number_tbl
988 );
989 IF (l_log_statement >= l_log_current_level) THEN
990 fnd_log.string(fnd_log.level_statement, l_debug_module,
991 'Returned from inv_reservation_pub.delete_reservation, l_return_status: ' || l_return_status);
992 END IF;
993 -- Check whether the return status is success, if not raise exception
994 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
995 -- log the error
996 IF (l_log_error >= l_log_current_level) THEN
997 fnd_log.string(fnd_log.level_error, l_debug_module,
998 'inv_reservation_pub.delete_reservation returned FND_API.G_RET_STS_UNEXP_ERROR');
999 END IF;
1000 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1001 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
1002 -- log the error
1003 IF (l_log_error >= l_log_current_level) THEN
1004 fnd_log.string(fnd_log.level_error, l_debug_module,
1005 'inv_reservation_pub.delete_reservation returned FND_API.G_RET_STS_ERROR');
1006 END IF;
1007 RAISE FND_API.G_EXC_ERROR;
1008 END IF;
1009 END IF; -- l_reservation_id IS NOT NULL
1010 ELSE
1011 -- Delete Based on p_scheduled_material_id and p_sub_inventory_code
1012 OPEN get_reservation_csr(p_scheduled_material_id,p_sub_inventory_code);
1013 LOOP
1014 -- For each of the reservation id associated with this material requirement call the Delete api
1015 FETCH get_reservation_csr INTO l_reservation_id;
1016 EXIT WHEN get_reservation_csr%NOTFOUND;
1017 -- logging
1018 IF (l_log_statement >= l_log_current_level) THEN
1019 fnd_log.string
1020 (
1021 fnd_log.level_statement,
1022 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
1023 'Going to Call inv_reservation_pub.delete_reservation l_reservation_id :' || l_reservation_id
1024 );
1025 END IF;
1026
1027 -- Assign the reservation id to be deleted
1028 l_rsv_rec.reservation_id := l_reservation_id;
1029 -- Call the WMS api
1030 inv_reservation_pub.delete_reservation
1031 (
1032 p_api_version_number => l_api_version,
1033 p_init_msg_lst => l_init_msg_list,
1034 x_return_status => l_return_status,
1035 x_msg_count => l_msg_count,
1036 x_msg_data => l_msg_data,
1037 p_rsv_rec => l_rsv_rec,
1038 p_serial_number => l_serial_number_tbl
1039 );
1040 IF (l_log_statement >= l_log_current_level) THEN
1041 fnd_log.string
1042 (
1043 fnd_log.level_statement,
1044 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
1045 'After call inv_reservation_pub.delete_reservation,l_return_status :' || l_return_status
1046 );
1047 END IF;
1048 -- Check whether the return status is success, if not raise exception
1049 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1050 -- log the error
1051 IF (l_log_error >= l_log_current_level) THEN
1052 fnd_log.string
1053 (
1054 fnd_log.level_error,
1055 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
1056 'inv_reservation_pub.delete_reservation returned FND_API.G_RET_STS_UNEXP_ERROR'
1057 );
1058 END IF;
1059 CLOSE get_reservation_csr;
1060 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1061 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
1062 -- log the error
1063 IF (l_log_error >= l_log_current_level) THEN
1064 fnd_log.string
1065 (
1069 );
1066 fnd_log.level_error,
1067 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
1068 'inv_reservation_pub.delete_reservation returned FND_API.G_RET_STS_ERROR'
1070 END IF;
1071 CLOSE get_reservation_csr;
1072 RAISE FND_API.G_EXC_ERROR;
1073 END IF;
1074 END LOOP; -- All the reservations for this material req id, sub inventory pair
1075 END IF;
1076
1077 -- Log API exit point
1078 IF (l_log_procedure >= l_log_current_level)THEN
1079 fnd_log.string
1080 (
1081 fnd_log.level_procedure,
1082 l_debug_module||'.end',
1083 'At the end of PLSQL procedure'
1084 );
1085 END IF;
1086
1087 -- Check Error Message stack.
1088 x_msg_count := FND_MSG_PUB.count_msg;
1089 IF x_msg_count > 0
1090 THEN
1091 RAISE FND_API.G_EXC_ERROR;
1092 END IF;
1093
1094 -- Commit if p_commit = FND_API.G_TRUE
1095 IF FND_API.TO_BOOLEAN(p_commit)
1096 THEN
1097 COMMIT WORK;
1098 IF (l_log_statement >= l_log_current_level) THEN
1099 fnd_log.string
1100 (
1101 fnd_log.level_statement,
1102 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
1103 'delete reservation COMMITTED'
1104 );
1105 END IF;
1106 END IF;
1107
1108 -- Standard call to get message count and if count is 1, get message info
1109 FND_MSG_PUB.count_and_get
1110 (
1111 p_count => x_msg_count,
1112 p_data => x_msg_data,
1113 p_encoded => FND_API.G_FALSE
1114 );
1115
1116 EXCEPTION
1117 WHEN FND_API.G_EXC_ERROR THEN
1118 ROLLBACK TO DELETE_RESERVATION_PVT;
1119 x_return_status := FND_API.G_RET_STS_ERROR;
1120 FND_MSG_PUB.count_and_get
1121 (
1122 p_count => x_msg_count,
1123 p_data => x_msg_data,
1124 p_encoded => FND_API.G_FALSE
1125 );
1126
1127 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1128 ROLLBACK TO DELETE_RESERVATION_PVT;
1129 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1130 FND_MSG_PUB.count_and_get
1131 (
1132 p_count => x_msg_count,
1133 p_data => x_msg_data,
1134 p_encoded => FND_API.G_FALSE
1135 );
1136
1137 WHEN OTHERS THEN
1138 ROLLBACK TO DELETE_RESERVATION_PVT;
1139 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1140 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1141 THEN
1142 FND_MSG_PUB.add_exc_msg
1143 (
1144 p_pkg_name => G_PKG_NAME,
1145 p_procedure_name => 'delete_reservation',
1146 p_error_text => SUBSTR(SQLERRM,1,240)
1147 );
1148 END IF;
1149 FND_MSG_PUB.count_and_get
1150 (
1151 p_count => x_msg_count,
1152 p_data => x_msg_data,
1153 p_encoded => FND_API.G_FALSE
1154 );
1155 END DELETE_RESERVATION;
1156
1157 ---------------------------------------------------------------------------------------------------------------------
1158 -- Declare Procedures --
1159 ---------------------------------------------------------------------------------------------------------------------
1160 -- Start of Comments --
1161 -- Procedure name : RELIEVE_RESERVATION
1162 -- Type : Private
1163 -- Function : API to delete the reservation made for a particular serial number
1164 -- Pre-reqs :
1165 -- Standard IN Parameters :
1166 -- p_api_version IN NUMBER Required
1167 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
1168 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
1169 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
1170 -- p_module_type IN VARCHAR2 Default NULL
1171 -- Standard OUT Parameters :
1172 -- x_return_status OUT VARCHAR2 Required
1173 -- x_msg_count OUT NUMBER Required
1174 -- x_msg_data OUT VARCHAR2 Required
1175
1176 --
1177 -- RELIEVE_RESERVATION Parameters:
1178 -- p_scheduled_material_id : The Schedule Material Id
1179 -- p_serial_number : The Serial number whose reservation has to be deleted
1180 -- End of Comments.
1181 ---------------------------------------------------------------------------------------------------------------------
1182 PROCEDURE RELIEVE_RESERVATION(
1183 p_api_version IN NUMBER := 1.0,
1184 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1185 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1186 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1187 p_module_type IN VARCHAR2,
1188 x_return_status OUT NOCOPY VARCHAR2,
1189 x_msg_count OUT NOCOPY NUMBER,
1190 x_msg_data OUT NOCOPY VARCHAR2,
1191 p_scheduled_material_id IN NUMBER ,
1192 p_serial_number IN VARCHAR2)
1193 IS
1194 -- Declare local variables
1195 l_api_name CONSTANT VARCHAR2(30) := 'relieve_reservation';
1199 l_init_msg_list VARCHAR2(1) := 'F';
1196 l_debug_module CONSTANT VARCHAR2(100) := 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name;
1197
1198 l_api_version CONSTANT NUMBER := 1.0;
1200 l_return_status VARCHAR2(1);
1201 l_msg_count NUMBER;
1202 l_msg_data VARCHAR2(2000);
1203 l_commit VARCHAR2(1) := 'F';
1204
1205 l_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
1206 l_serial_number_tbl inv_reservation_global.serial_number_tbl_type;
1207 l_reservation_id NUMBER;
1208 l_reserved_quantity NUMBER;
1209 l_x_primary_relieved_quantity NUMBER;
1210 l_x_primary_remain_quantity NUMBER;
1211
1212 -- Variables to check the log level according to the coding standards
1213 l_dbg_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1214 l_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
1215
1216 -- Declare Cursors
1217 CURSOR get_mtl_req_dtls_csr (c_scheduled_material_id IN NUMBER)
1218 IS
1219 SELECT asmt.organization_id,
1220 asmt.requested_date,
1221 asmt.uom,
1222 asmt.requested_quantity,
1223 asmt.inventory_item_id,
1227 WHERE asmt.status = 'ACTIVE'
1224 asmt.object_version_number
1225 FROM ahl_schedule_materials asmt,
1226 ahl_visit_tasks_b avtl
1228 AND asmt.requested_quantity <>0
1229 AND asmt.scheduled_material_id = c_scheduled_material_id
1230 AND asmt.visit_task_id = avtl.visit_task_id
1231 AND ( avtl.status_code='PLANNING'
1232 OR
1233 ( avtl.status_code='RELEASED'
1234 AND
1235 EXISTS ( SELECT awo.visit_task_id
1236 FROM ahl_workorders awo
1237 WHERE avtl.visit_task_id = awo.visit_task_id
1238 AND (awo.status_code = '1' OR awo.status_code='3') -- 1:Unreleased,3:Released
1239 )
1240 )
1241 );
1242 l_get_mtl_req_dtls_rec get_mtl_req_dtls_csr%ROWTYPE;
1243
1244 -- Cursor get_reservation_csr removed by skpathak on 12-NOV-2008 for bug 7241925
1245 -- in favor of call to the new helper procedure GET_MATCHING_RESERVATION
1246 /**
1247 CURSOR get_reservation_csr (c_scheduled_material_id IN NUMBER, c_SUBINVENTORY_CODE IN VARCHAR2)
1248 IS
1249 SELECT reservation_id, primary_reservation_quantity
1250 FROM mtl_reservations mrsv,ahl_schedule_materials asmt
1251 WHERE mrsv.demand_source_line_detail = c_scheduled_material_id
1252 AND mrsv.external_source_code = 'AHL'
1253 AND mrsv.SUBINVENTORY_CODE = c_SUBINVENTORY_CODE
1254 AND mrsv.demand_source_line_detail = asmt.scheduled_material_id
1255 AND mrsv.organization_id = asmt.organization_id
1256 AND mrsv.requirement_date = asmt.requested_date
1257 AND mrsv.inventory_item_id = asmt.inventory_item_id;
1258 **/
1259 CURSOR get_instance_dtls_csr (c_inventory_itme_id IN NUMBER, c_serial_number IN VARCHAR2, c_organization_id IN NUMBER)
1260 IS
1261 SELECT csi.instance_id, msn.serial_number,csi.inv_subinventory_name subinventory_code
1262 FROM csi_item_instances csi,mtl_serial_numbers msn
1263 WHERE csi.inventory_item_id = c_inventory_itme_id
1264 AND csi.serial_number = c_serial_number
1265 AND csi.last_vld_organization_id = c_organization_id
1266 AND csi.inventory_item_id = msn.inventory_item_id;
1267
1268 l_get_instance_dtls_rec get_instance_dtls_csr%ROWTYPE;
1269
1270 BEGIN
1271 -- Standard start of API savepoint
1272 SAVEPOINT RELIEVE_RESERVATION_PVT;
1273
1274 -- Initialize return status to success before any code logic/validation
1275 x_return_status:= FND_API.G_RET_STS_SUCCESS;
1276
1277 -- Standard call to check for call compatibility
1278 IF NOT FND_API.COMPATIBLE_API_CALL (l_api_version, p_api_version, l_api_name, G_PKG_NAME)
1279 THEN
1280 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1281 END IF;
1282
1286 FND_MSG_PUB.INITIALIZE;
1283 -- Initialize message list if p_init_msg_list = FND_API.G_TRUE
1284 IF FND_API.TO_BOOLEAN(p_init_msg_list)
1285 THEN
1287 END IF;
1288
1289 -- Log API entry point
1290 IF (l_log_procedure >= l_log_current_level)THEN
1291 fnd_log.string
1292 (
1293 fnd_log.level_procedure,
1294 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||'.begin',
1295 'At the start of PL SQL procedure '
1296 );
1297 END IF;
1298
1299 -- Validate the schedule material id
1300 IF p_scheduled_material_id IS NULL THEN
1301 FND_MESSAGE.set_name( 'AHL', 'AHL_RSV_INVLD_MAT_REQ' );
1302 FND_MSG_PUB.add;
1303 -- log the error
1304 IF (l_log_error >= l_log_current_level) THEN
1305 fnd_log.string
1306 (
1307 fnd_log.level_error,
1308 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
1309 'AHL_RSV_INVLD_MAT_REQ: FND_API.G_EXC_ERROR'
1310 );
1311 END IF;
1312 RAISE FND_API.G_EXC_ERROR;
1313 END IF; -- IF p_scheduled_material_id IS NULL
1314
1315 -- Get the Material Requirements details
1316 OPEN get_mtl_req_dtls_csr(p_scheduled_material_id);
1317 FETCH get_mtl_req_dtls_csr INTO l_get_mtl_req_dtls_rec;
1318 -- If the details are not found then raise exception
1319 IF get_mtl_req_dtls_csr%NOTFOUND THEN
1320 FND_MESSAGE.set_name( 'AHL', 'AHL_RSV_INVLD_MAT_REQ' );
1321 FND_MSG_PUB.add;
1322 CLOSE get_mtl_req_dtls_csr;
1323 RAISE FND_API.G_EXC_ERROR;
1324 END IF; -- IF get_mtl_req_dtls_csr%NOTFOUND
1325 CLOSE get_mtl_req_dtls_csr;
1326
1327 -- Validate the Serial Number
1328 IF p_serial_number IS NULL THEN
1329 FND_MESSAGE.set_name( 'AHL', 'AHL_RSV_INVLD_SL_NUM' );
1330 FND_MSG_PUB.add;
1331 IF (l_log_error>= l_log_current_level)THEN
1332 fnd_log.string
1333 (
1334 fnd_log.level_error,
1335 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
1336 'p_serial_number is null'
1337 );
1338 END IF;
1339 RAISE FND_API.G_EXC_ERROR;
1340 END IF; -- IF p_serial_number IS NULL
1341
1342 -- Get the details of the item instance
1343 OPEN get_instance_dtls_csr(l_get_mtl_req_dtls_rec.inventory_item_id,p_serial_number,l_get_mtl_req_dtls_rec.organization_id);
1344 FETCH get_instance_dtls_csr INTO l_get_instance_dtls_rec;
1345 IF get_instance_dtls_csr%NOTFOUND THEN
1346 FND_MESSAGE.set_name( 'AHL', 'AHL_RSV_INVLD_SL_NUM' );
1347 FND_MSG_PUB.add;
1348 CLOSE get_instance_dtls_csr;
1349 RAISE FND_API.G_EXC_ERROR;
1350 END IF; -- IF get_instance_dtls_csr%NOTFOUND
1351 CLOSE get_instance_dtls_csr;
1352
1353 IF (l_log_statement >= l_log_current_level) THEN
1354 fnd_log.string(fnd_log.level_statement,'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
1355 'Serial Number to be Deleted:' || p_serial_number );
1356 fnd_log.string(fnd_log.level_statement,'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
1357 'l_get_mtl_req_dtls_rec.inventory_item_id:' ||l_get_mtl_req_dtls_rec.inventory_item_id );
1358 fnd_log.string(fnd_log.level_statement,'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
1359 'l_get_mtl_req_dtls_rec.organization_id:' || l_get_mtl_req_dtls_rec.organization_id );
1360 fnd_log.string(fnd_log.level_statement,'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
1361 'p_scheduled_material_id:' || p_scheduled_material_id );
1365
1362 END IF;
1363
1364
1366 -- Get the reservation details, that is id and the reserved quantity
1367 -- Changed by skpathak on 12-NOV-2008 for bug 7241925
1368 /**
1369 OPEN get_reservation_csr(p_scheduled_material_id,l_get_instance_dtls_rec.subinventory_code);
1370 FETCH get_reservation_csr INTO l_reservation_id,l_reserved_quantity;
1371 -- If no reservations are found , then exit as nothing needs to be done
1372 IF get_reservation_csr%NOTFOUND THEN
1373 CLOSE get_reservation_csr;
1374 RETURN;
1375 END IF; -- IF get_reservation_csr%NOTFOUND
1376 CLOSE get_reservation_csr;
1377 **/
1378 GET_MATCHING_RESERVATION(p_scheduled_material_id => p_scheduled_material_id,
1379 p_serial_number => p_serial_number,
1380 p_match_serial => 'Y', -- Match reservation by serial
1381 x_reservation_id => l_reservation_id,
1382 x_reservation_quantity => l_reserved_quantity);
1383
1384 IF (l_log_statement >= l_log_current_level) THEN
1385 fnd_log.string(fnd_log.level_statement, l_debug_module,
1386 'GET_MATCHING_RESERVATION returned l_reservation_id = ' || l_reservation_id ||
1387 ', l_reserved_quantity = ' || l_reserved_quantity);
1388 END IF;
1389 -- End Changes by skpathak on 12-NOV-2008 for bug 7241925
1390
1391 -- If there is only one reserved item then DELETE_RESERVATION api has to be invoked
1392 IF l_reserved_quantity = 1 THEN
1393 delete_reservation(
1394 p_api_version => l_api_version,
1395 p_init_msg_list => l_init_msg_list,
1396 p_commit => l_commit,
1400 x_msg_count => l_msg_count,
1397 p_validation_level => FND_API.G_VALID_LEVEL_FULL, -- the validation level
1398 p_module_type => p_module_type,
1399 x_return_status => l_return_status,
1401 x_msg_data => l_msg_data,
1402 p_scheduled_material_id => p_scheduled_material_id,
1403 -- Begin Changes by skpathak on 12-NOV-2008 for bug 7241925
1404 /**
1405 p_sub_inventory_code => l_get_instance_dtls_rec.subinventory_code
1406 **/
1407 p_sub_inventory_code => null,
1408 p_serial_number => p_serial_number
1409 -- End Changes by skpathak on 12-NOV-2008 for bug 7241925
1410 );
1411 ELSIF l_reserved_quantity > 1 THEN
1412
1413 -- Initialize the record to be send to the WMS package
1414 /*Initialize_create_rec( p_scheduled_material_id, -- the schedule material id
1415 p_serial_number, -- the serial number
1416 l_rsv_rec, -- record to be passed ti the WMS packages
1417 l_return_status); -- return status
1418 */
1419 -- If there are more than one item reserved, then RELIEVE_RESERVATION has to be called
1420 l_rsv_rec.reservation_id := l_reservation_id;
1421 l_serial_number_tbl(1).serial_number := p_serial_number;
1422 l_serial_number_tbl(1).inventory_item_id := l_get_mtl_req_dtls_rec.inventory_item_id;
1423
1424 inv_reservation_pub.relieve_reservation(
1425 p_api_version_number => l_api_version,
1426 p_init_msg_lst => l_init_msg_list,
1427 x_return_status => l_return_status,
1428 x_msg_count => l_msg_count,
1429 x_msg_data => l_msg_data,
1430 p_rsv_rec => l_rsv_rec,
1431 p_primary_relieved_quantity => 1,
1432 p_relieve_all => fnd_api.g_false,
1433 p_original_serial_number => l_serial_number_tbl,
1434 x_primary_relieved_quantity => l_x_primary_relieved_quantity,
1435 x_primary_remain_quantity => l_x_primary_remain_quantity
1436 );
1437 END IF; -- IF l_reserved_quantity = 1
1438
1439 -- Check for the returned status from these APIs
1440 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1441 IF (l_log_error >= l_log_current_level) THEN
1442 fnd_log.string
1443 (
1444 fnd_log.level_error,
1445 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
1446 'After Relieve/Delete FND_API.G_EXC_UNEXPECTED_ERROR'
1447 );
1448 END IF;
1449 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1450 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
1451 IF (l_log_error >= l_log_current_level) THEN
1452 fnd_log.string
1453 (
1454 fnd_log.level_error,
1455 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
1456 'After Relieve/Delete FND_API.G_EXC_ERROR'
1457 );
1458 END IF;
1459 RAISE FND_API.G_EXC_ERROR;
1460 END IF;
1461
1462 -- Log API exit point
1463 IF (l_log_procedure >= l_log_current_level)THEN
1464 fnd_log.string
1465 (
1466 fnd_log.level_procedure,
1467 l_debug_module||'.end',
1468 'At the end of PLSQL procedure'
1469 );
1470 END IF;
1471
1472 -- Check Error Message stack.
1473 x_msg_count := FND_MSG_PUB.count_msg;
1474 IF x_msg_count > 0
1475 THEN
1476 RAISE FND_API.G_EXC_ERROR;
1477 END IF;
1478
1479 -- Commit if p_commit = FND_API.G_TRUE
1480 IF FND_API.TO_BOOLEAN(p_commit)
1481 THEN
1482 COMMIT WORK;
1483 END IF;
1484
1485 -- Standard call to get message count and if count is 1, get message info
1486 FND_MSG_PUB.count_and_get
1487 (
1488 p_count => x_msg_count,
1489 p_data => x_msg_data,
1490 p_encoded => FND_API.G_FALSE
1491 );
1492
1493 EXCEPTION
1494 WHEN FND_API.G_EXC_ERROR THEN
1495 ROLLBACK TO RELIEVE_RESERVATION_PVT;
1496 x_return_status := FND_API.G_RET_STS_ERROR;
1497 FND_MSG_PUB.count_and_get
1498 (
1499 p_count => x_msg_count,
1500 p_data => x_msg_data,
1501 p_encoded => FND_API.G_FALSE
1502 );
1503
1504 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1505 ROLLBACK TO RELIEVE_RESERVATION_PVT;
1506 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1507 FND_MSG_PUB.count_and_get
1508 (
1509 p_count => x_msg_count,
1510 p_data => x_msg_data,
1511 p_encoded => FND_API.G_FALSE
1512 );
1513
1514 WHEN OTHERS THEN
1515 ROLLBACK TO RELIEVE_RESERVATION_PVT;
1516 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1517 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1518 THEN
1519 FND_MSG_PUB.add_exc_msg
1520 (
1521 p_pkg_name => G_PKG_NAME,
1522 p_procedure_name => 'relieve_reservation',
1523 p_error_text => SUBSTR(SQLERRM,1,240)
1524 );
1525 END IF;
1526 FND_MSG_PUB.count_and_get
1527 (
1528 p_count => x_msg_count,
1529 p_data => x_msg_data,
1533
1530 p_encoded => FND_API.G_FALSE
1531 );
1532 END RELIEVE_RESERVATION;
1534 ---------------------------------------------------------------------------------------------------------------------
1535 -- Declare Procedures --
1536 ---------------------------------------------------------------------------------------------------------------------
1537 -- Start of Comments --
1538 -- Procedure name : TRANSFER_RESERVATION
1539 -- Type : Private
1540 -- Function : API to change the demand source type, called when pushed to production
1541 -- Pre-reqs :
1542 -- Standard IN Parameters :
1543 -- p_api_version IN NUMBER Required
1544 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
1545 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
1546 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
1547 -- p_module_type IN VARCHAR2 Default NULL
1548 -- Standard OUT Parameters :
1549 -- x_return_status OUT VARCHAR2 Required
1550 -- x_msg_count OUT NUMBER Required
1551 -- x_msg_data OUT VARCHAR2 Required
1552
1553 --
1554 -- TRANSFER_RESERVATION Parameters:
1555 -- p_visit_id : The id of the visit for which the reservations need to be transferred.
1556 -- End of Comments.
1557 ---------------------------------------------------------------------------------------------------------------------
1558 PROCEDURE TRANSFER_RESERVATION(
1559 p_api_version IN NUMBER := 1.0,
1560 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1561 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1562 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1563 p_module_type IN VARCHAR2,
1564 x_return_status OUT NOCOPY VARCHAR2,
1565 x_msg_count OUT NOCOPY NUMBER,
1566 x_msg_data OUT NOCOPY VARCHAR2,
1567 p_visit_id IN NUMBER)
1568 IS
1569 -- Declare local variables
1570 l_api_name CONSTANT VARCHAR2(30) := 'transfer_reservation';
1571
1572 l_api_version CONSTANT NUMBER := 1.0;
1573 l_init_msg_list VARCHAR2(1) := 'F';
1574 l_return_status VARCHAR2(1);
1575 l_msg_count NUMBER;
1576 l_msg_data VARCHAR2(2000);
1577
1578 l_debug_module CONSTANT VARCHAR2(100) := 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name;
1579
1580 l_reservation_id NUMBER;
1581 l_wip_entity_id NUMBER;
1582 l_from_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
1583 l_to_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
1584 l_from_serial_number_tbl inv_reservation_global.serial_number_tbl_type;
1585 l_to_serial_number_tbl inv_reservation_global.serial_number_tbl_type;
1586 l_x_to_reservation_id NUMBER;
1587
1588 -- Variables to check the log level according to the coding standards
1589 l_dbg_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1590 l_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
1591
1592 -- Declare Cursors and local record types
1593 CURSOR get_mtl_req_dtls_csr (c_visit_id IN NUMBER)
1594 IS
1595 SELECT mat.visit_task_id, mat.scheduled_material_id, mat.workorder_operation_id, mat.operation_sequence
1596 FROM ahl_schedule_materials mat,
1597 ahl_visit_tasks_b vt
1598 WHERE mat.status = 'ACTIVE'
1599 AND mat.requested_quantity <>0
1600 AND vt.status_code = 'PLANNING'
1601 AND vt.visit_task_id = mat.visit_task_id
1602 AND vt.visit_id = c_visit_ID;
1603 l_get_mtl_req_dtls_rec get_mtl_req_dtls_csr%ROWTYPE;
1604
1605 -- AnRaj: Added a join with ahl_schedule_materials and further where conditions to remove the FTS
1606 CURSOR get_reservation_csr (c_scheduled_material_id IN NUMBER)
1607 IS
1608 SELECT reservation_id
1609 FROM mtl_reservations mrsv,ahl_schedule_materials asmt
1610 WHERE demand_source_line_detail = c_scheduled_material_id
1611 AND external_source_code = 'AHL'
1612 AND mrsv.demand_source_line_detail = asmt.scheduled_material_id
1613 AND mrsv.organization_id = asmt.organization_id
1614 AND mrsv.requirement_date = asmt.requested_date
1615 AND mrsv.inventory_item_id = asmt.inventory_item_id;
1616
1617 CURSOR get_wip_dtls_csr (c_visit_task_ID IN NUMBER)
1618 IS
1619 SELECT aw.wip_entity_id
1620 FROM ahl_workorders aw
1621 WHERE aw.status_code in ('1','3') -- 1:Unreleased,3:Released
1622 AND aw.visit_task_id = c_visit_task_id;
1623
1624 BEGIN
1625 -- Standard start of API savepoint
1626 SAVEPOINT TRANSFER_RESERVATION_PVT;
1627
1628 -- Initialize return status to success before any code logic/validation
1629 x_return_status:= FND_API.G_RET_STS_SUCCESS;
1630
1631 -- Standard call to check for call compatibility
1632 IF NOT FND_API.COMPATIBLE_API_CALL (l_api_version, p_api_version, l_api_name, G_PKG_NAME)
1633 THEN
1634 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1635 END IF;
1636
1637 -- Initialize message list if p_init_msg_list = FND_API.G_TRUE
1638 IF FND_API.TO_BOOLEAN(p_init_msg_list)
1639 THEN
1640 FND_MSG_PUB.INITIALIZE;
1641 END IF;
1642
1643 -- Log API entry point
1644 IF (l_log_procedure >= l_log_current_level)THEN
1645 fnd_log.string
1646 (
1647 fnd_log.level_procedure,
1651 END IF;
1648 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||'.begin',
1649 'At the start of PL SQL procedure '
1650 );
1652
1653
1654 -- Get all the material requirements for this visit
1655 OPEN get_mtl_req_dtls_csr(p_visit_id);
1656 LOOP
1657 -- For each material requirement
1658 FETCH get_mtl_req_dtls_csr INTO l_get_mtl_req_dtls_rec;
1659 EXIT WHEN get_mtl_req_dtls_csr%NOTFOUND;
1660 -- Get the all the reservations made for this material requirement
1661 OPEN get_reservation_csr(l_get_mtl_req_dtls_rec.scheduled_material_id);
1662 LOOP
1663 -- For each reservation id
1664 FETCH get_reservation_csr INTO l_reservation_id;
1665 EXIT WHEN get_reservation_csr%NOTFOUND;
1666 l_from_rsv_rec.reservation_id := l_reservation_id;
1667 -- Get the WIP entity ID
1668 OPEN get_wip_dtls_csr(l_get_mtl_req_dtls_rec.visit_task_id);
1669 FETCH get_wip_dtls_csr INTO l_wip_entity_id;
1670 CLOSE get_wip_dtls_csr;
1671
1672 l_to_rsv_rec.demand_source_type_id := inv_reservation_global.g_source_type_wip;
1673 l_to_rsv_rec.demand_source_header_id := l_wip_entity_id;
1674 l_to_rsv_rec.demand_source_line_id := l_get_mtl_req_dtls_rec.operation_sequence;
1675
1676 l_to_rsv_rec.demand_source_line_detail := l_get_mtl_req_dtls_rec.scheduled_material_id;
1677 l_from_rsv_rec.demand_source_line_detail := l_get_mtl_req_dtls_rec.scheduled_material_id;
1678
1679 IF (l_log_statement >= l_log_current_level) THEN
1680 fnd_log.string
1681 (
1682 fnd_log.level_statement,'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
1683 'Before Calling inv_reservation_pub.transfer_reservation'
1684 );
1685 fnd_log.string
1686 (
1687 fnd_log.level_statement,'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
1688 'l_to_rsv_rec.demand_source_type_id' || l_to_rsv_rec.demand_source_type_id
1689 );
1690 fnd_log.string
1691 (
1692 fnd_log.level_statement,'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
1693 'l_to_rsv_rec.demand_source_header_id' || l_to_rsv_rec.demand_source_header_id
1694 );
1695 fnd_log.string
1696 (
1697 fnd_log.level_statement,'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
1698 'l_to_rsv_rec.demand_source_line_detail' || l_to_rsv_rec.demand_source_line_detail
1699 );
1700 fnd_log.string
1701 (
1702 fnd_log.level_statement,'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
1703 'l_from_rsv_rec.reservation_id' || l_from_rsv_rec.reservation_id
1704 );
1705 fnd_log.string
1706 (
1707 fnd_log.level_statement,'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
1708 'l_to_serial_number_tbl.COUNT' || l_to_serial_number_tbl.COUNT
1709 );
1710
1711 END IF;
1712
1713 -- Call the WMS Transfer Reservaion API
1714 inv_reservation_pub.transfer_reservation
1715 (
1716 p_api_version_number => l_api_version,
1717 p_init_msg_lst => l_init_msg_list,
1718 x_return_status => l_return_status,
1719 x_msg_count => l_msg_count,
1720 x_msg_data => l_msg_data,
1721 p_is_transfer_supply => fnd_api.g_false,
1722 p_original_rsv_rec => l_from_rsv_rec,
1723 p_to_rsv_rec => l_to_rsv_rec,
1724 p_original_serial_number=> l_from_serial_number_tbl,
1725 p_to_serial_number => l_to_serial_number_tbl,
1726 x_to_reservation_id => l_x_to_reservation_id
1727 );
1728
1729 -- Check for the returned status from these APIs
1730 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1731 CLOSE get_reservation_csr;
1732 CLOSE get_mtl_req_dtls_csr;
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 'inv_reservation_pub.transfer_reservation returned FND_API.G_EXC_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 CLOSE get_reservation_csr;
1744 CLOSE get_mtl_req_dtls_csr;
1745 IF (l_log_error >= l_log_current_level) THEN
1746 fnd_log.string
1747 (
1748 fnd_log.level_error,
1749 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
1750 'inv_reservation_pub.transfer_reservation returned FND_API.G_RET_STS_ERROR'
1751 );
1752 END IF;
1753 RAISE FND_API.G_EXC_ERROR;
1754 END IF;
1755 END LOOP ; -- get_reservation_csr,for all the reservations for this material req
1756 CLOSE get_reservation_csr;
1757 END LOOP; --get_mtl_req_dtls_csr, for all the mat reqs of this visit
1758 CLOSE get_mtl_req_dtls_csr;
1759
1760 -- Log API exit point
1761 IF (l_log_procedure >= l_log_current_level)THEN
1762 fnd_log.string
1763 (
1764 fnd_log.level_procedure,
1765 l_debug_module||'.end',
1766 'At the end of PLSQL procedure'
1767 );
1768 END IF;
1769
1770 -- Check Error Message stack.
1771 x_msg_count := FND_MSG_PUB.count_msg;
1772 IF x_msg_count > 0
1773 THEN
1774 RAISE FND_API.G_EXC_ERROR;
1775 END IF;
1776
1777 -- Commit if p_commit = FND_API.G_TRUE
1778 IF FND_API.TO_BOOLEAN(p_commit)
1779 THEN
1780 COMMIT WORK;
1781 END IF;
1782
1783 -- Standard call to get message count and if count is 1, get message info
1784 FND_MSG_PUB.count_and_get
1785 (
1786 p_count => x_msg_count,
1787 p_data => x_msg_data,
1788 p_encoded => FND_API.G_FALSE
1789 );
1790
1791 EXCEPTION
1792 WHEN FND_API.G_EXC_ERROR THEN
1793 ROLLBACK TO TRANSFER_RESERVATION_PVT;
1794 x_return_status := FND_API.G_RET_STS_ERROR;
1795 FND_MSG_PUB.count_and_get
1796 (
1797 p_count => x_msg_count,
1798 p_data => x_msg_data,
1799 p_encoded => FND_API.G_FALSE
1800 );
1801
1802 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1803 ROLLBACK TO TRANSFER_RESERVATION_PVT;
1804 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1805 FND_MSG_PUB.count_and_get
1806 (
1807 p_count => x_msg_count,
1808 p_data => x_msg_data,
1809 p_encoded => FND_API.G_FALSE
1810 );
1811
1812 WHEN OTHERS THEN
1813 ROLLBACK TO TRANSFER_RESERVATION_PVT;
1814 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1815 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1816 THEN
1817 FND_MSG_PUB.add_exc_msg
1818 (
1819 p_pkg_name => G_PKG_NAME,
1823 END IF;
1820 p_procedure_name => 'relieve_reservation',
1821 p_error_text => SUBSTR(SQLERRM,1,240)
1822 );
1824 FND_MSG_PUB.count_and_get
1825 (
1826 p_count => x_msg_count,
1827 p_data => x_msg_data,
1828 p_encoded => FND_API.G_FALSE
1829 );
1830 END TRANSFER_RESERVATION;
1831
1832
1833 ---------------------------------------------------------------------------------------------------------------------
1834 -- Declare Procedures --
1835 ---------------------------------------------------------------------------------------------------------------------
1836 -- Start of Comments --
1837 -- Procedure name : UPDATE_VISIT_RESERVATIONS
1838 -- Type : Private
1839 -- Function : API to update all the reservations for s particular visit
1840 -- Pre-reqs :
1841 -- Standard IN Parameters :
1842 -- Standard OUT Parameters :
1843 -- x_return_status OUT VARCHAR2 Required
1844 --
1845 -- UPDATE_VISIT_RESERVATIONS Parameters:
1846 -- p_visit_id : The id of the visit for which the reservations need to be transferred.
1847 -- This method is invoked from AHL_LTP_REQST_MATRL_PVT.MODIFY_VISIT_RESERVATIONS
1848 -- After a task is deleted, to reschedule the reservations as task times might have changed
1849 -- End of Comments.
1850 ---------------------------------------------------------------------------------------------------------------------
1851 PROCEDURE UPDATE_VISIT_RESERVATIONS(
1852 x_return_status OUT NOCOPY VARCHAR2,
1853 p_visit_id IN NUMBER)
1854 IS
1855 -- Declare local variables
1856 l_api_name CONSTANT VARCHAR2(30) := 'update_visit_reservations';
1857 l_debug_module CONSTANT VARCHAR2(100) := 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name;
1858 l_api_version CONSTANT NUMBER := 1.0;
1859 l_init_msg_list VARCHAR2(1) := 'F';
1860 l_return_status VARCHAR2(1);
1861 l_msg_count NUMBER;
1862 l_msg_data VARCHAR2(2000);
1863
1864 l_from_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
1865 l_to_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
1866 l_from_serial_number_tbl inv_reservation_global.serial_number_tbl_type;
1867 l_to_serial_number_tbl inv_reservation_global.serial_number_tbl_type;
1868 l_x_serial_number_tbl inv_reservation_global.serial_number_tbl_type;
1869 l_x_quantity_reserved NUMBER;
1870
1871 -- Declare cursors and record variables
1872 -- Get the all the reservation related information using the schedule material id
1873 CURSOR get_upd_rsv_csr (c_visit_id IN NUMBER)
1874 IS
1875 SELECT mrsv.reservation_id, mrsv.demand_source_header_id, mrsv.demand_source_line_id, mrsv.inventory_item_id,mrsv.organization_id
1876 FROM ahl_schedule_materials asmt,
1877 ahl_visit_tasks_b vt,
1878 mtl_reservations mrsv
1879 WHERE vt.status_code = 'PLANNING'
1880 AND vt.visit_task_id = asmt.visit_task_id
1881 AND vt.visit_id = c_visit_id
1882 AND mrsv.external_source_code = 'AHL'
1883 AND mrsv.demand_source_line_detail = asmt.scheduled_material_id
1884 AND mrsv.organization_id = asmt.organization_id
1885 AND mrsv.requirement_date = asmt.requested_date
1886 AND mrsv.inventory_item_id = asmt.inventory_item_id;
1887 l_get_upd_rsv_rec get_upd_rsv_csr%ROWTYPE;
1888
1892 SELECT scheduled_material_id, requested_date
1889 -- get the material requiremnt id and the date
1890 CURSOR get_mtl_req_id_csr (c_visit_task_id IN NUMBER, c_rt_oper_material_id IN NUMBER, c_inventory_item_id IN NUMBER)
1891 IS
1893 FROM ahl_schedule_materials
1894 WHERE visit_task_id = c_visit_task_id
1895 AND rt_oper_material_id = c_rt_oper_material_id
1896 AND inventory_item_id = c_inventory_item_id
1897 AND status = 'ACTIVE';
1898 l_get_mtl_req_id_rec get_mtl_req_id_csr%ROWTYPE;
1899
1900 -- For getting the Serial numbers
1901 TYPE serial_num_type IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
1902 serial_num_tbl serial_num_type;
1903
1904 -- local variables
1905 l_reservation_id NUMBER;
1906 l_inventory_item_id NUMBER;
1907 l_temp_id NUMBER;
1908
1909 BEGIN
1910 -- Standard start of API savepoint
1911 SAVEPOINT UPDATE_VISIT_RESERVATIONS_PVT;
1912
1913 -- Initialize return status to success before any code logic/validation
1914 x_return_status:= FND_API.G_RET_STS_SUCCESS;
1915
1916 -- Log API entry point
1917 IF (l_log_procedure >= l_log_current_level)THEN
1918 fnd_log.string
1919 (
1920 fnd_log.level_procedure,
1921 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||'.begin',
1922 'At the start of PL SQL procedure '
1923 );
1924 END IF;
1925
1926 -- Get all the material requirements with reservations Created
1927 -- for this visit, there can be any number of material requirements
1928 OPEN get_upd_rsv_csr (p_visit_id);
1929 LOOP
1930 FETCH get_upd_rsv_csr INTO l_get_upd_rsv_rec;
1931 EXIT WHEN get_upd_rsv_csr%NOTFOUND;
1932 -- Find out the new material requirements for this reservation
1933
1934 l_reservation_id := l_get_upd_rsv_rec.reservation_id;
1935 IF (l_log_statement >= l_log_current_level) THEN
1936 fnd_log.string
1937 (
1938 fnd_log.level_statement,
1939 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
1940 'Reservation ID to be updated:' || l_reservation_id
1941 );
1942 END IF;
1943
1944 l_inventory_item_id := l_get_upd_rsv_rec.inventory_item_id;
1945 IF (l_log_statement >= l_log_current_level) THEN
1946 fnd_log.string
1947 (
1948 fnd_log.level_statement,
1949 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
1950 'Inventory Item ID: ' || l_inventory_item_id
1951 );
1952 END IF;
1953 IF (l_log_statement >= l_log_current_level) THEN
1954 fnd_log.string
1955 (
1956 fnd_log.level_statement,
1957 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
1958 'demand_source_header_id: ' || l_get_upd_rsv_rec.demand_source_header_id
1959 );
1960 END IF;
1961 IF (l_log_statement >= l_log_current_level) THEN
1962 fnd_log.string
1963 (
1964 fnd_log.level_statement,
1965 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
1966 'demand_source_line_id: ' || l_get_upd_rsv_rec.demand_source_line_id
1967 );
1968 END IF;
1969
1970 -- For each material requirement id, get the schedule material id and the requested date
1971 OPEN get_mtl_req_id_csr(l_get_upd_rsv_rec.demand_source_header_id,l_get_upd_rsv_rec.demand_source_line_id,l_get_upd_rsv_rec.inventory_item_id);
1972 FETCH get_mtl_req_id_csr INTO l_get_mtl_req_id_rec;
1973 IF get_mtl_req_id_csr%NOTFOUND THEN
1974 FND_MESSAGE.set_name( 'AHL', 'AHL_RSV_UPD_VST_RSV_FAIL' );
1975 FND_MSG_PUB.add;
1976 -- log the error
1977 IF (l_log_error >= l_log_current_level) THEN
1978 fnd_log.string
1979 (
1980 fnd_log.level_error,
1981 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||'.begin',
1982 'No record found in ahl schedule materials for get_mtl_req_id_csr'
1983 );
1984 END IF;
1985 CLOSE get_upd_rsv_csr;
1986 CLOSE get_mtl_req_id_csr;
1987 RAISE FND_API.G_EXC_ERROR;
1988 END IF; -- IF get_mtl_req_id_csr%NOTFOUND
1989 CLOSE get_mtl_req_id_csr;
1990
1991 IF (l_log_statement >= l_log_current_level) THEN
1992 fnd_log.string
1993 (
1994 fnd_log.level_statement,
1995 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
1996 'l_get_mtl_req_id_rec.scheduled_material_id: ' || l_get_mtl_req_id_rec.scheduled_material_id
1997 );
1998 END IF;
1999
2000 -- Get all the Serial Numbers reserved for this material req
2001 BEGIN
2002 SELECT serial_number
2003 BULK COLLECT INTO serial_num_tbl
2004 FROM mtl_serial_numbers
2005 WHERE reservation_id = l_reservation_id
2006 AND INVENTORY_ITEM_ID = l_get_upd_rsv_rec.inventory_item_id
2007 AND CURRENT_ORGANIZATION_ID = l_get_upd_rsv_rec.organization_id;
2008 EXCEPTION
2009 WHEN NO_DATA_FOUND THEN
2010 -- log the error
2011 IF (l_log_statement >= l_log_current_level) THEN
2012 fnd_log.string
2013 (
2014 fnd_log.level_statement,
2015 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||'.begin',
2016 'No Serial Numbers reseved for Reservation ID:' || l_reservation_id
2017 );
2018 END IF;
2019 END;
2020
2021 IF serial_num_tbl.count > 0 THEN
2022 -- Initialize the record to be send to the WMS package
2023 Initialize_create_rec( l_get_mtl_req_id_rec.scheduled_material_id, -- the schedule material id
2024 serial_num_tbl(1), -- the serial number
2025 l_to_rsv_rec, -- record to be passed to the WMS packages
2026 l_return_status); -- return status
2027
2028 IF (l_log_statement >= l_log_current_level) THEN
2029 fnd_log.string
2030 (
2031 fnd_log.level_statement,
2032 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
2033 'Organizzation ID is: ' || l_to_rsv_rec.organization_id
2034 );
2035 END IF;
2036
2037 l_to_rsv_rec.primary_reservation_quantity := serial_num_tbl.count;
2038 IF (l_log_statement >= l_log_current_level) THEN
2039 fnd_log.string
2040 (
2041 fnd_log.level_statement,
2042 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
2043 'Number of Serial Numbers to be updated: ' || l_to_rsv_rec.primary_reservation_quantity
2044 );
2045 END IF;
2046 -- commented out, as serial numbers as not required to be passed as we are updating only the date
2047 /* FOR I in serial_num_tbl.first..serial_num_tbl.last
2048 LOOP
2049 l_to_serial_number_tbl(I).serial_number := serial_num_tbl(I);
2050 l_to_serial_number_tbl(I).inventory_item_id := l_inventory_item_id;
2051 END LOOP;*/
2052 END IF;
2053
2054 -- update reservation with new material requirement and requested date
2055 l_from_rsv_rec.reservation_id := l_get_upd_rsv_rec.reservation_id;
2056 l_to_rsv_rec.reservation_id := l_get_upd_rsv_rec.reservation_id;
2057 l_to_rsv_rec.requirement_date := l_get_mtl_req_id_rec.requested_date;
2058 l_to_rsv_rec.demand_source_line_detail := l_get_mtl_req_id_rec.scheduled_material_id;
2059
2060 -- Call WMS Update reservation API
2061 inv_reservation_pub.update_reservation
2062 (
2063 p_api_version_number => l_api_version,
2064 p_init_msg_lst => l_init_msg_list,
2065 x_return_status => l_return_status,
2066 x_msg_count => l_msg_count,
2067 x_msg_data => l_msg_data,
2068 p_original_rsv_rec => l_from_rsv_rec,
2069 p_to_rsv_rec => l_to_rsv_rec,
2070 p_original_serial_number => l_from_serial_number_tbl,
2071 p_to_serial_number => l_to_serial_number_tbl--,
2072 );
2073
2074 -- Check for the returned status from these APIs
2075 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2076 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2077 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2078 -- log the error
2079 IF (l_log_statement >= l_log_current_level) THEN
2080 fnd_log.string
2081 (
2082 fnd_log.level_statement,
2083 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
2084 'inv_reservation_pub.update_reservation returned UNEXPECTED ERROR'
2085 );
2086 END IF;
2087 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
2088 --RAISE FND_API.G_EXC_ERROR;
2089 x_return_status := FND_API.G_RET_STS_ERROR;
2090 -- log the error
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 'inv_reservation_pub.update_reservation returned EXPECTED ERROR'
2097 );
2098 END IF;
2099 END IF;
2100 END LOOP;
2101 CLOSE get_upd_rsv_csr;
2102
2103 -- Log API exit point
2104 IF (l_log_procedure >= l_log_current_level)THEN
2105 fnd_log.string
2106 (
2107 fnd_log.level_procedure,
2108 l_debug_module||'.end',
2109 'At the end of PLSQL procedure'
2110 );
2111 END IF;
2112 END UPDATE_VISIT_RESERVATIONS;
2113
2114 ---------------------------------------------------------------------------------------------------------------------
2115 -- Declare Procedures --
2116 ---------------------------------------------------------------------------------------------------------------------
2117 -- Start of Comments --
2118 -- Procedure name : DELETE_VISIT_RESERVATIONS
2119 -- Type : Private
2120 -- Function : API to delete all the reservations for s particular visit
2121 -- Pre-reqs :
2122 -- Standard IN Parameters :
2123 -- Standard OUT Parameters :
2124 -- x_return_status OUT VARCHAR2 Required
2125 --
2126 -- DELETE_VISIT_RESERVATIONS Parameters:
2127 -- p_visit_id : The visit id for which the reservations need to be deleted
2128 -- This procedure is called in AHL_LTP_REQST_MATRL_PVT.Unschedule_Visit_Materials
2129 -- When a task is deleted, to remove the reservations related to that task.
2130 -- End of Comments.
2131 ---------------------------------------------------------------------------------------------------------------------
2132 PROCEDURE DELETE_VISIT_RESERVATIONS(
2133 x_return_status OUT NOCOPY VARCHAR2,
2134 p_visit_id IN NUMBER)
2135 IS
2136 -- Declare local variables
2137 l_api_name CONSTANT VARCHAR2(30) := 'delete_visit_reservations';
2138 l_debug_module CONSTANT VARCHAR2(100) := 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name;
2139 l_api_version CONSTANT NUMBER := 1.0;
2140 l_init_msg_list VARCHAR2(1) := 'F';
2141 l_return_status VARCHAR2(1);
2142 l_msg_count NUMBER;
2143 l_msg_data VARCHAR2(2000);
2144
2145 l_from_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
2146 l_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
2147 l_to_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
2148 l_from_serial_number_tbl inv_reservation_global.serial_number_tbl_type;
2149 l_to_serial_number_tbl inv_reservation_global.serial_number_tbl_type;
2150 l_x_serial_number_tbl inv_reservation_global.serial_number_tbl_type;
2151 l_serial_number_tbl inv_reservation_global.serial_number_tbl_type;
2152 l_x_quantity_reserved NUMBER;
2153
2154 -- Variables to check the log level according to the coding standards
2155 l_dbg_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
2156 l_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
2157
2158 l_reservation_id NUMBER:= null;
2159
2160 --Declare Cursors
2161 --
2162 CURSOR get_del_rsv_csr (c_visit_id IN NUMBER)
2163 IS
2164 SELECT mrsv.reservation_id
2165 FROM ahl_schedule_materials asmt,
2166 ahl_visit_tasks_b avt,
2167 mtl_reservations mrsv
2168 WHERE avt.status_code in ( 'PLANNING','DELETED')
2169 AND avt.visit_task_id = asmt.visit_task_id
2170 AND avt.visit_id = c_visit_ID
2171 AND avt.visit_id = asmt.visit_id
2172 AND mrsv.external_source_code = 'AHL'
2173 AND mrsv.demand_source_line_detail = asmt.scheduled_material_id
2174 AND mrsv.organization_id = asmt.organization_id
2175 AND mrsv.requirement_date = asmt.requested_date
2176 AND mrsv.inventory_item_id = asmt.inventory_item_id;
2177
2178 BEGIN
2179 -- Standard start of API savepoint
2180 SAVEPOINT DELETE_VISIT_RESERVATIONS_PVT;
2181
2182 -- Initialize return status to success before any code logic/validation
2183 x_return_status:= FND_API.G_RET_STS_SUCCESS;
2184
2185 -- Initialize message list if p_init_msg_list = FND_API.G_TRUE
2186 /* IF FND_API.TO_BOOLEAN(p_init_msg_list)
2187 THEN
2188 FND_MSG_PUB.INITIALIZE;
2189 END IF;
2190 */
2191
2192 -- Log API entry point
2193 IF (l_log_procedure >= l_log_current_level)THEN
2194 fnd_log.string
2195 (
2196 fnd_log.level_procedure,
2197 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||'.begin',
2198 'At the start of PL SQL procedure '
2199 );
2200 END IF;
2201
2202
2203 OPEN get_del_rsv_csr (p_visit_id);
2204 LOOP
2205 FETCH get_del_rsv_csr INTO l_reservation_id;
2206 EXIT WHEN get_del_rsv_csr%NOTFOUND;
2207 l_rsv_rec.reservation_id := l_reservation_id;
2208 -- Call WMS delete reservation API
2209 inv_reservation_pub.delete_reservation
2210 (
2211 p_api_version_number => l_api_version,
2212 p_init_msg_lst => l_init_msg_list,
2213 x_return_status => l_return_status,
2214 x_msg_count => l_msg_count,
2215 x_msg_data => l_msg_data,
2216 p_rsv_rec => l_rsv_rec,
2217 p_serial_number => l_serial_number_tbl
2218 );
2219 -- Check for the returned status from these APIs
2220 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2221 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2222 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2223 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
2224 --RAISE FND_API.G_EXC_ERROR;
2225 x_return_status := FND_API.G_RET_STS_ERROR;
2226 END IF;
2227 END LOOP;
2228 CLOSE get_del_rsv_csr;
2229
2230 -- Log API exit point
2231 IF (l_log_procedure >= l_log_current_level)THEN
2232 fnd_log.string
2233 (
2234 fnd_log.level_procedure,
2235 l_debug_module||'.end',
2236 'At the end of PLSQL procedure'
2237 );
2238 END IF;
2239 END DELETE_VISIT_RESERVATIONS;
2240
2241
2242 ---------------------------------------------------------------------------------------------------------------------
2243 -- Declare Procedures --
2244 ---------------------------------------------------------------------------------------------------------------------
2245 -- Start of Comments --
2246 -- Procedure name : INITIALIZE_CREATE_REC
2247 -- Type : Private
2248 -- Function : To initializa the record that is to be passed into WMS api
2249 -- Pre-reqs :
2250 -- Standard IN Parameters :
2251 -- Standard OUT Parameters :
2252 -- INITIALIZE_CREATE_REC Parameters:
2253 -- p_rsv_rec :
2254 -- p_schedule_material_id :
2255 -- x_rsv_rec :
2256 -- End of Comments.
2257 ---------------------------------------------------------------------------------------------------------------------
2258 PROCEDURE INITIALIZE_CREATE_REC(
2259 p_schedule_material_id IN NUMBER,
2260 p_serial_number IN VARCHAR2,
2261 x_rsv_rec OUT NOCOPY inv_reservation_global.mtl_reservation_rec_type,
2262 x_return_status OUT NOCOPY VARCHAR2
2263 )
2264 IS
2265
2266 -- Declare local variables
2267 l_api_name CONSTANT VARCHAR2(30) := 'initialize_create_rec';
2268 l_debug_module CONSTANT VARCHAR2(100) := 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name;
2269 -- Variables to check the log level according to the coding standards
2270 l_dbg_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
2271 l_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
2272
2273 -- Declare cursors
2274 -- Cursor to get the Material Requirement Details
2275 CURSOR get_mtl_req_dtls_csr (c_scheduled_material_id IN NUMBER)
2276 IS
2277 SELECT mat.scheduled_material_id , mat.organization_id,
2278 mat.requested_date, mat.uom, mat.inventory_item_id,
2279 mat. workorder_operation_id, vt.status_code task_status_code,
2280 vt.visit_task_number, v.visit_number, mat.operation_sequence,
2281 mat.visit_task_id, mat.rt_oper_material_id
2282 FROM ahl_schedule_materials mat,
2283 ahl_visits_b v,
2284 ahl_visit_tasks_b vt
2285 WHERE vt.visit_task_id = mat.visit_task_id
2286 AND vt.visit_id = v.visit_id
2287 AND mat.scheduled_material_id = c_scheduled_material_id;
2288 l_get_mtl_req_dtls_rec get_mtl_req_dtls_csr%ROWTYPE;
2289
2290 -- Cursor to get the WIP details
2291 CURSOR get_wip_dtls_csr (c_visit_task_ID IN NUMBER)
2292 IS
2293 SELECT aw.wip_entity_id
2294 FROM ahl_workorders aw
2295 WHERE aw.status_code in ('1','3') -- 1:Unreleased,3:Released
2296 AND aw.visit_task_id = c_visit_task_id;
2297 l_get_wip_dtls_rec get_wip_dtls_csr%ROWTYPE;
2298
2299 -- Cursor to get the subinventory and the locator information
2300 -- AnRaj modified by selecting 2 more fiels, bug#4756288
2301 CURSOR get_subinv_locator(c_serial_number IN VARCHAR2,c_inventory_item_id IN NUMBER)
2302 IS
2303 SELECT inv_subinventory_name,inv_locator_id,inventory_revision,lot_number
2304 FROM csi_item_instances
2305 WHERE serial_number = c_serial_number
2306 AND inventory_item_id = c_inventory_item_id;
2307
2308 l_subinventory_name csi_item_instances.inv_subinventory_name%TYPE;
2309 l_inv_locator_id NUMBER;
2310 -- AnRaj added 2 more variable, bug#4756288
2311 l_revision csi_item_instances.inventory_revision%TYPE;
2312 l_lot_number csi_item_instances.serial_number%TYPE;
2313
2314 BEGIN
2315
2316 -- Log API exit point
2317 IF (l_log_procedure >= l_log_current_level)THEN
2318 fnd_log.string
2319 (
2320 fnd_log.level_procedure,
2321 l_debug_module||'.start',
2322 'At the start of PLSQL procedure'
2323 );
2324 END IF;
2325
2326 -- Get the material requirement details
2327
2328
2329 IF (l_log_statement >= l_log_current_level) THEN
2330 fnd_log.string
2331 (
2332 fnd_log.level_statement,
2333 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
2334 'p_schedule_material_id in get_mtl_req_dtls_csr is:' || p_schedule_material_id
2335 );
2336 END IF;
2337
2338 OPEN get_mtl_req_dtls_csr (p_schedule_material_id);
2339 FETCH get_mtl_req_dtls_csr INTO l_get_mtl_req_dtls_rec;
2340 CLOSE get_mtl_req_dtls_csr;
2341
2342 IF (l_log_statement >= l_log_current_level) THEN
2343 fnd_log.string
2344 (
2345 fnd_log.level_statement,
2346 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
2347 'Fetched Value of l_get_mtl_req_dtls_rec.organization_id is:' || l_get_mtl_req_dtls_rec.organization_id
2348 );
2349 END IF;
2350
2351
2352
2353 IF (l_log_statement >= l_log_current_level) THEN
2354 fnd_log.string
2355 (
2356 fnd_log.level_statement,
2357 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
2358 'p_serial_number in get_subinv_locator is: ' || p_serial_number
2359 );
2360 END IF;
2361 IF (l_log_statement >= l_log_current_level) THEN
2362 fnd_log.string
2363 (
2364 fnd_log.level_statement,
2365 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
2366 'l_get_mtl_req_dtls_rec.inventory_item_id in get_subinv_locator is ' || l_get_mtl_req_dtls_rec.inventory_item_id
2367 );
2368 END IF;
2369 -- Get the subinventory name and the locator id
2370 OPEN get_subinv_locator(p_serial_number,l_get_mtl_req_dtls_rec.inventory_item_id);
2371 FETCH get_subinv_locator INTO l_subinventory_name,l_inv_locator_id,l_revision,l_lot_number;
2372 CLOSE get_subinv_locator;
2373
2374 x_rsv_rec.reservation_id := NULL;
2375 x_rsv_rec.requirement_date := l_get_mtl_req_dtls_rec.requested_date;
2376 x_rsv_rec.organization_id := l_get_mtl_req_dtls_rec.organization_id;
2377 x_rsv_rec.inventory_item_id := l_get_mtl_req_dtls_rec.inventory_item_id;
2378 x_rsv_rec.demand_source_name := 'CMRO'||'.'|| l_get_mtl_req_dtls_rec.visit_number ||'.'||l_get_mtl_req_dtls_rec.visit_task_number;
2379 x_rsv_rec.demand_source_line_detail := l_get_mtl_req_dtls_rec.scheduled_material_id;
2380 x_rsv_rec.primary_uom_code := l_get_mtl_req_dtls_rec.uom;
2381 x_rsv_rec.primary_uom_id := NULL;
2382 x_rsv_rec.reservation_uom_code := NULL;
2383 x_rsv_rec.reservation_uom_id := NULL;
2384 x_rsv_rec.reservation_quantity := NULL;
2385 x_rsv_rec.primary_reservation_quantity := NULL;
2386 x_rsv_rec.autodetail_group_id := NULL;
2387 x_rsv_rec.external_source_code := 'AHL';
2388 x_rsv_rec.external_source_line_id := NULL;
2389 x_rsv_rec.supply_source_type_id := inv_reservation_global.g_source_type_inv;
2390 x_rsv_rec.supply_source_header_id := NULL;
2391 x_rsv_rec.supply_source_line_id := NULL;
2392 x_rsv_rec.supply_source_name := NULL;
2393 x_rsv_rec.supply_source_line_detail := NULL;
2394 x_rsv_rec.revision := l_revision;
2395 x_rsv_rec.subinventory_code := l_subinventory_name;
2396 x_rsv_rec.subinventory_id := NULL;
2397
2398 -- AnRaj modified, bug#4756288
2399 x_rsv_rec.locator_id := l_inv_locator_id;
2400 x_rsv_rec.lot_number := l_lot_number;
2401 -- end bug#4756288
2402
2403 x_rsv_rec.lot_number_id := NULL;
2404 x_rsv_rec.pick_slip_number := NULL;
2405 x_rsv_rec.lpn_id := NULL;
2406 -- Added later
2407 x_rsv_rec.ship_ready_flag := NULL;
2408 x_rsv_rec.demand_source_delivery := NULL;
2409
2410 x_rsv_rec.attribute_category := NULL;
2411 x_rsv_rec.attribute1 := NULL;
2412 x_rsv_rec.attribute2 := NULL;
2413 x_rsv_rec.attribute3 := NULL;
2414 x_rsv_rec.attribute4 := NULL;
2415 x_rsv_rec.attribute5 := NULL;
2416 x_rsv_rec.attribute6 := NULL;
2417 x_rsv_rec.attribute7 := NULL;
2418 x_rsv_rec.attribute8 := NULL;
2419 x_rsv_rec.attribute9 := NULL;
2420 x_rsv_rec.attribute10 := NULL;
2421 x_rsv_rec.attribute11 := NULL;
2422 x_rsv_rec.attribute12 := NULL;
2423 x_rsv_rec.attribute13 := NULL;
2424 x_rsv_rec.attribute14 := NULL;
2425 x_rsv_rec.attribute15 := NULL;
2426
2427 IF l_get_mtl_req_dtls_rec.task_status_code = 'RELEASED' THEN
2428 -- If the task is in 'released' then get the WIP entity id for demand_source_header_id
2429 OPEN get_wip_dtls_csr (l_get_mtl_req_dtls_rec.visit_task_id);
2430 FETCH get_wip_dtls_csr INTO l_get_wip_dtls_rec;
2431 CLOSE get_wip_dtls_csr;
2432 x_rsv_rec.demand_source_type_id := inv_reservation_global.g_source_type_wip;
2433 x_rsv_rec.demand_source_header_id := l_get_wip_dtls_rec.wip_entity_id;
2434 x_rsv_rec.demand_source_line_id := l_get_mtl_req_dtls_rec.operation_sequence;
2435 ELSIF l_get_mtl_req_dtls_rec.task_status_code = 'PLANNING' THEN
2436 -- If the task is in 'planning' then get the WIP entity id for demand_source_header_id
2437 x_rsv_rec.demand_source_type_id := inv_reservation_global.g_source_type_inv;
2438 x_rsv_rec.demand_source_header_id := l_get_mtl_req_dtls_rec.Visit_Task_ID;
2439 x_rsv_rec.demand_source_line_id := l_get_mtl_req_dtls_rec.rt_oper_material_id;
2440 ELSE
2441 FND_MESSAGE.set_name( 'AHL', 'AHL_RSV_ONLY_PLN_SPF_TSK' );
2442 FND_MSG_PUB.ADD;
2443 --RAISE FND_API.G_EXC_ERROR;
2444 x_return_status := FND_API.G_RET_STS_ERROR;
2445 END IF; -- l_get_mtl_req_dtls_rec.task_status_code = 'RELEASED'
2446
2447 -- Log API exit point
2448 IF (l_log_procedure >= l_log_current_level)THEN
2449 fnd_log.string
2450 (
2451 fnd_log.level_procedure,
2452 l_debug_module||'.end',
2453 'At the end of PLSQL procedure'
2454 );
2455 END IF;
2456 END INITIALIZE_CREATE_REC;
2457
2458
2459 ---------------------------------------------------------------------------------------------------------------------
2460 -- Declare Procedures --
2461 ---------------------------------------------------------------------------------------------------------------------
2462 -- Start of Comments --
2463 -- Procedure name : TRANSFER_RESERVATION_MATRL_REQR
2464 -- Type : Private
2465 -- Function : API to transfer the reservations from one material requirement to another
2466 -- Pre-reqs :
2467 -- Standard IN Parameters :
2468 -- p_api_version IN NUMBER Required
2469 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
2470 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
2471 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
2472 -- p_module_type IN VARCHAR2 Default NULL
2473 -- Standard OUT Parameters :
2474 -- x_return_status OUT VARCHAR2 Required
2475 -- x_msg_count OUT NUMBER Required
2476 -- x_msg_data OUT VARCHAR2 Required
2477
2478 --
2479 -- TRANSFER_RESERVATION Parameters:
2480 -- p_visit_id : The id of the visit for which the reservations need to be transferred.
2481 -- p_visit_task_id : The of the Visit task
2482 -- p_from_mat_req_id : The material requirement id of the from record
2483 -- p_to_mat_req_id : The material requirement id of the to record
2484
2485 -- End of Comments.
2486 ---------------------------------------------------------------------------------------------------------------------
2487 PROCEDURE TRNSFR_RSRV_FOR_MATRL_REQR(
2488 p_api_version IN NUMBER := 1.0,
2489 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2490 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2491 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2492 p_module_type IN VARCHAR2,
2493 x_return_status OUT NOCOPY VARCHAR2,
2494 x_msg_count OUT NOCOPY NUMBER,
2495 x_msg_data OUT NOCOPY VARCHAR2,
2496 p_visit_task_id IN NUMBER,
2497 p_from_mat_req_id IN NUMBER,
2498 p_to_mat_req_id IN NUMBER
2499 )
2500 IS
2501 -- Declare local variables
2502 l_api_name CONSTANT VARCHAR2(30) := 'TRNSFR_RSRV_FOR_MATRL_REQR';
2503
2504 l_api_version CONSTANT NUMBER := 1.0;
2505 l_init_msg_list VARCHAR2(1) := 'F';
2506 l_return_status VARCHAR2(1);
2507 l_msg_count NUMBER;
2508 l_msg_data VARCHAR2(2000);
2509
2510 l_debug_module CONSTANT VARCHAR2(100) := 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name;
2511
2512 l_reservation_id NUMBER;
2513 l_wip_entity_id NUMBER;
2514 l_from_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
2515 l_to_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
2516 l_from_serial_number_tbl inv_reservation_global.serial_number_tbl_type;
2517 l_to_serial_number_tbl inv_reservation_global.serial_number_tbl_type;
2518 l_x_to_reservation_id NUMBER;
2519
2520 -- Variables to check the log level according to the coding standards
2521 l_dbg_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
2522 l_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
2523
2524 BEGIN
2525 -- Standard start of API savepoint
2526 SAVEPOINT TRNSFR_RSRV_FOR_MATRL_REQR_PVT;
2527
2528 -- Initialize return status to success before any code logic/validation
2529 x_return_status:= FND_API.G_RET_STS_SUCCESS;
2530
2531 -- Standard call to check for call compatibility
2532 IF NOT FND_API.COMPATIBLE_API_CALL (l_api_version, p_api_version, l_api_name, G_PKG_NAME)
2533 THEN
2537 -- Initialize message list if p_init_msg_list = FND_API.G_TRUE
2534 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2535 END IF;
2536
2538 IF FND_API.TO_BOOLEAN(p_init_msg_list)
2539 THEN
2540 FND_MSG_PUB.INITIALIZE;
2541 END IF;
2542
2543 -- Log API entry point
2544 IF (l_log_procedure >= l_log_current_level)THEN
2545 fnd_log.string
2546 (
2547 fnd_log.level_procedure,
2548 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||'.begin',
2549 'At the start of PL SQL procedure '
2550 );
2551 END IF;
2552
2553 l_from_rsv_rec.demand_source_line_detail := p_from_mat_req_id;
2554 l_to_rsv_rec.demand_source_type_id := inv_reservation_global.g_source_type_inv;
2555 l_to_rsv_rec.demand_source_header_id := p_visit_task_id;
2556 l_to_rsv_rec.demand_source_line_detail := p_to_mat_req_id;
2557
2558 IF (l_log_statement >= l_log_current_level)THEN
2559 fnd_log.string
2560 (fnd_log.level_statement,'ahl.plsql.'||g_pkg_name||'.'||l_api_name,'Calling inv_reservation_pub.transfer_reservation');
2561 END IF;
2562
2563 inv_reservation_pub.transfer_reservation
2564 (
2565 p_api_version_number => l_api_version,
2566 p_init_msg_lst => l_init_msg_list,
2567 x_return_status => l_return_status,
2568 x_msg_count => l_msg_count,
2569 x_msg_data => l_msg_data,
2570 p_is_transfer_supply => fnd_api.g_false,
2571 p_original_rsv_rec => l_from_rsv_rec,
2572 p_to_rsv_rec => l_to_rsv_rec,
2573 p_original_serial_number=> l_from_serial_number_tbl,
2574 p_to_serial_number => l_to_serial_number_tbl,
2575 x_to_reservation_id => l_x_to_reservation_id
2576 );
2577
2578 -- Check for the returned status from these APIs
2579 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2580 IF (l_log_error >= l_log_current_level) THEN
2581 fnd_log.string
2582 (
2583 fnd_log.level_error,
2584 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
2588 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2585 'inv_reservation_pub.transfer_reservation returned FND_API.G_EXC_ERROR'
2586 );
2587 END IF;
2589 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
2590 IF (l_log_error >= l_log_current_level) THEN
2591 fnd_log.string
2592 (
2593 fnd_log.level_error,
2594 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
2595 'inv_reservation_pub.transfer_reservation returned FND_API.G_RET_STS_ERROR'
2596 );
2597 END IF;
2598 RAISE FND_API.G_EXC_ERROR;
2599 END IF;
2600
2601 -- Log API exit point
2602 IF (l_log_procedure >= l_log_current_level)THEN
2603 fnd_log.string
2604 (
2605 fnd_log.level_procedure,
2606 l_debug_module||'.end',
2607 'At the end of PLSQL procedure'
2608 );
2609 END IF;
2610
2611 -- Check Error Message stack.
2612 x_msg_count := FND_MSG_PUB.count_msg;
2613 IF x_msg_count > 0
2614 THEN
2615 RAISE FND_API.G_EXC_ERROR;
2616 END IF;
2617
2618 -- Commit if p_commit = FND_API.G_TRUE
2619 IF FND_API.TO_BOOLEAN(p_commit)
2620 THEN
2621 COMMIT WORK;
2622 END IF;
2623
2624 -- Standard call to get message count and if count is 1, get message info
2625 FND_MSG_PUB.count_and_get
2626 (
2627 p_count => x_msg_count,
2628 p_data => x_msg_data,
2629 p_encoded => FND_API.G_FALSE
2630 );
2631
2632 EXCEPTION
2633 WHEN FND_API.G_EXC_ERROR THEN
2634 ROLLBACK TO TRNSFR_RSRV_FOR_MATRL_REQR_PVT;
2635 x_return_status := FND_API.G_RET_STS_ERROR;
2636 FND_MSG_PUB.count_and_get
2637 (
2638 p_count => x_msg_count,
2639 p_data => x_msg_data,
2640 p_encoded => FND_API.G_FALSE
2641 );
2642
2643 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2644 ROLLBACK TO TRNSFR_RSRV_FOR_MATRL_REQR_PVT;
2645 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2646 FND_MSG_PUB.count_and_get
2647 (
2648 p_count => x_msg_count,
2649 p_data => x_msg_data,
2650 p_encoded => FND_API.G_FALSE
2651 );
2652
2653 WHEN OTHERS THEN
2654 ROLLBACK TO TRNSFR_RSRV_FOR_MATRL_REQR_PVT;
2655 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2656 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2657 THEN
2658 FND_MSG_PUB.add_exc_msg
2659 (
2660 p_pkg_name => G_PKG_NAME,
2661 p_procedure_name => 'TRANSFER_RESERVATION_MATRL_REQR_PVT',
2662 p_error_text => SUBSTR(SQLERRM,1,240)
2663 );
2664 END IF;
2665 FND_MSG_PUB.count_and_get
2666 (
2667 p_count => x_msg_count,
2668 p_data => x_msg_data,
2669 p_encoded => FND_API.G_FALSE
2670 );
2671 END TRNSFR_RSRV_FOR_MATRL_REQR;
2672
2673 -- Helper procedure added by skpathak on 12-NOV-2008 for bug 7241925
2674 -- Gets the reservation (if any) that matches the scheduled_material_id+serial_number
2675 -- If p_match_serial is 'Y', also checks if the serial is already included in the reservation
2676
2677 PROCEDURE GET_MATCHING_RESERVATION(p_scheduled_material_id IN NUMBER,
2678 p_serial_number IN VARCHAR2,
2679 p_match_serial IN VARCHAR2 DEFAULT 'N',
2680 x_reservation_id OUT NOCOPY NUMBER,
2681 x_reservation_quantity OUT NOCOPY NUMBER) IS
2682 -- Declare local variables
2683 l_api_name CONSTANT VARCHAR2(30) := 'GET_MATCHING_RESERVATION';
2684 l_debug_module CONSTANT VARCHAR2(100) := 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name;
2685
2686 CURSOR get_reservation_csr IS
2687 SELECT mrsv.reservation_id, mrsv.primary_reservation_quantity
2688 FROM mtl_reservations mrsv, ahl_schedule_materials asmt, mtl_serial_numbers msn
2689 WHERE mrsv.demand_source_line_detail = p_scheduled_material_id
2690 AND mrsv.external_source_code = 'AHL'
2691 AND msn.serial_number = p_serial_number
2692 AND mrsv.organization_id = msn.current_organization_id
2693 AND mrsv.inventory_item_id = msn.inventory_item_id
2694 AND NVL(mrsv.subinventory_code, '@@@') = NVL(msn.current_subinventory_code, '@@@')
2695 AND NVL(mrsv.locator_id, -99) = NVL(msn.current_locator_id, -99)
2696 AND NVL(mrsv.revision, '@@@') = NVL(msn.revision, '@@@')
2697 AND NVL(mrsv.lot_number, '@@@') = NVL(msn.lot_number, '@@@')
2698 AND NVL(mrsv.lpn_id, -99) = NVL(msn.lpn_id, -99)
2699 AND ((p_match_serial = 'N') OR (mrsv.reservation_id = msn.reservation_id))
2700 AND mrsv.demand_source_line_detail = asmt.scheduled_material_id
2701 AND mrsv.organization_id = asmt.organization_id
2702 AND mrsv.requirement_date = asmt.requested_date
2703 AND mrsv.inventory_item_id = asmt.inventory_item_id;
2704 BEGIN
2705
2706 -- Log API exit point
2707 IF (l_log_procedure >= l_log_current_level) THEN
2708 fnd_log.string(fnd_log.level_procedure, l_debug_module||'.start',
2709 'At the start of PLSQL procedure, p_scheduled_material_id = ' || p_scheduled_material_id ||
2710 ', p_serial_number = ' || p_serial_number);
2711 END IF;
2712
2713 OPEN get_reservation_csr;
2714 FETCH get_reservation_csr INTO x_reservation_id, x_reservation_quantity;
2715 CLOSE get_reservation_csr;
2716
2717 -- Log API exit point
2718 IF (l_log_procedure >= l_log_current_level) THEN
2719 fnd_log.string(fnd_log.level_procedure, l_debug_module||'.end',
2720 'At the end of PLSQL procedure, x_reservation_id = ' || x_reservation_id ||
2721 ', x_reservation_quantity = ' || x_reservation_quantity);
2722 END IF;
2723 END GET_MATCHING_RESERVATION;
2724
2725 END AHL_RSV_RESERVATIONS_PVT;
[Home] [Help]