[Home] [Help]
PACKAGE BODY: APPS.WSM_RESERVATIONS_PVT
Source
1 package body WSM_RESERVATIONS_PVT as
2 /* $Header: WSMVRSVB.pls 120.16 2006/06/21 10:17:28 sisankar noship $ */
3
4 /* Package name */
5 g_pkg_name VARCHAR2(20) := 'WSM_RESERVATIONS_PVT';
6
7 type t_wsm_rsv_v_tbl_type is table of wsm_reservations_v%rowtype index by binary_integer;
8 type t_wsm_reservations is table of wsm_reservations%rowtype index by binary_integer;
9 --type r_mtl_rsv_rec_type is record of mtl_reservations%rowtype;
10 --MP: Sale order changes
11 l_mtl_rsv_rec inv_reservation_global.mtl_maintain_rsv_rec_type;
12
13 g_log_level_unexpected NUMBER := FND_LOG.LEVEL_UNEXPECTED ;
14 g_log_level_error number := FND_LOG.LEVEL_ERROR ;
15 g_log_level_exception number := FND_LOG.LEVEL_EXCEPTION ;
16 g_log_level_event number := FND_LOG.LEVEL_EVENT ;
17 g_log_level_procedure number := FND_LOG.LEVEL_PROCEDURE ;
18 g_log_level_statement number := FND_LOG.LEVEL_STATEMENT ;
19
20 g_msg_lvl_unexp_error NUMBER := FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ;
21 g_msg_lvl_error NUMBER := FND_MSG_PUB.G_MSG_LVL_ERROR ;
22 g_msg_lvl_success NUMBER := FND_MSG_PUB.G_MSG_LVL_SUCCESS ;
23 g_msg_lvl_debug_high NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH ;
24 g_msg_lvl_debug_medium NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM ;
25 g_msg_lvl_debug_low NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW ;
26
27 g_ret_success VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
28 g_ret_error VARCHAR2(1) := FND_API.G_RET_STS_ERROR;
29 g_ret_unexpected VARCHAR2(1) := FND_API.G_RET_STS_UNEXP_ERROR;
30
31
32 Procedure modify_reservations_wlt ( p_txn_header IN WSM_WIP_LOT_TXN_PVT.WLTX_TRANSACTIONS_REC_TYPE,
33 p_starting_jobs_tbl IN WSM_WIP_LOT_TXN_PVT.WLTX_STARTING_JOBS_TBL_TYPE,
34 p_resulting_jobs_tbl IN WSM_WIP_LOT_TXN_PVT.WLTX_RESULTING_JOBS_TBL_TYPE,
35 p_rep_job_index IN NUMBER,
36 p_sj_also_rj_index IN NUMBER,
37 x_return_status OUT NOCOPY VARCHAR2,
38 x_msg_count OUT NOCOPY NUMBER,
39 x_msg_data OUT NOCOPY VARCHAR2 ) IS
40 l_rsv_old inv_reservation_global.mtl_reservation_rec_type;
41 l_rsv_new inv_reservation_global.mtl_reservation_rec_type;
42 --l_mtl_rsv_rec inv_reservations_global.mtl_rsv_tbl_type;
43 l_wsm_rsv_v_tbl t_wsm_rsv_v_tbl_type;
44 l_dummy_sn inv_reservation_global.serial_number_tbl_type;
45 l_new_rsv_id NUMBER;
46 l_rsv_exists boolean;
47 l_rsvd_qty number;
48 l_rj_index number;
49
50 /* Status variables */
51 l_return_status VARCHAR2(1);
52 l_msg_count NUMBER;
53 l_msg_data VARCHAR2(2000);
54
55 -- Logging variables.....
56 l_msg_tokens WSM_Log_PVT.token_rec_tbl;
57 l_log_level number := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
58
59 l_stmt_num NUMBER;
60 l_module CONSTANT VARCHAR2(100) := 'wsm.plsql.WSM_RESERVATIONS_PVT.modify_reservations_wlt';
61 l_param_tbl WSM_Log_PVT.param_tbl_type;
62
63 l_msg_index number;
64 l_quantity_modified NUMBER;
65 l_expected_quantity_uom VARCHAR2(3);
66 -- Logging variables...
67
68 begin
69
70 l_wsm_rsv_v_tbl.delete;
71
72 -- Have a starting point --
73 savepoint start_modify_rsv_wlt;
74
75 l_stmt_num := 10;
76 /* Initialize API return status to success */
77 x_return_status := FND_API.G_RET_STS_SUCCESS;
78
79 --MO_GLOBAL.SET_POLICY_CONTEXT ('S', p_txn_header.organization_id);
80
81 --First query to check if any reservations asscoiated with the starting job from the calling procedure itself--
82 --now the reservation processing begins
83 l_rsv_old.supply_source_header_id := p_starting_jobs_tbl(p_rep_job_index).wip_entity_id;
84 l_rsv_old.inventory_item_id := p_starting_jobs_tbl(p_rep_job_index).primary_item_id;
85 l_rsv_old.organization_id := p_starting_jobs_tbl(p_rep_job_index).organization_id;
86 l_rsv_old.supply_source_type_id := 5; --MP Sale order changes
87
88 If p_txn_header.transaction_type_id = WSMPCNST.UPDATE_ASSEMBLY then
89 --Write to WIE 'Note: Starting job is reserved against sales order(s). Update Assembly transaction
90 --will result in deletion of the starting job reservations
91 --Write warning into concurrent log
92 fnd_message.set_name('WSM','WSM_RSV_UPD_ASSY');
93 l_msg_data := fnd_message.get;
94 fnd_file.put_line(fnd_file.log,l_msg_data);
95
96 l_return_status := FND_API.G_RET_STS_SUCCESS;
97 l_msg_count := 0;
98 l_msg_data := null;
99
100 l_msg_index := fnd_msg_pub.count_msg;
101
102 --MP Delete Changes Start
103 BEGIN
104 select *
105 bulk collect into l_wsm_rsv_v_tbl
106 from wsm_reservations_v
107 where wip_entity_id = p_starting_jobs_tbl(p_rep_job_index).wip_entity_id;
108 EXCEPTION
109 when no_data_found then
110 return;
111 END;
112 --MP Delete Changes End
113 If l_wsm_rsv_v_tbl.count > 0 then --MP Delete Changes
114 For i in l_wsm_rsv_v_tbl.first .. l_wsm_rsv_v_tbl.last loop --MP Delete Changes
115 l_rsv_old.reservation_id := l_wsm_rsv_v_tbl(i).reservation_id;
116 --log proc entry
117 inv_reservation_pub.delete_reservation
118 (
119 p_api_version_number => 1.0
120 , p_init_msg_lst => fnd_api.g_true
121 , x_return_status => l_return_status
122 , x_msg_count => l_msg_count
123 , x_msg_data => l_msg_data
124 , p_rsv_rec => l_rsv_old
125 , p_serial_number => l_dummy_sn
126 );
127
128 --proc exit
129 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
130 /*API failed*/
131 WSM_log_PVT.update_errtbl(l_msg_index,l_msg_count);
132 --log that rsv api failed
133 IF (G_LOG_LEVEL_STATEMENT >= l_log_level) THEN
134 l_msg_tokens.delete;
135 WSM_log_PVT.logMessage (p_module_name => l_module ,
136 p_msg_text => 'inv_reservation_pub.delete_reservation failed',
137 p_stmt_num => l_stmt_num ,
138 p_msg_tokens => l_msg_tokens ,
139 p_fnd_log_level => G_LOG_LEVEL_STATEMENT ,
140 p_run_log_level => l_log_level
141 );
142 END IF;
143
144 IF l_return_status = fnd_api.g_ret_sts_error THEN
145 RAISE FND_API.G_EXC_ERROR;
146 ELSE
147 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
148 END IF;
149 end if;
150 end loop; --MP Delete Changes
151 end if; --MP Delete Changes :End of check on l_wsm_rsv_v_tbl.count > 0
152 Elsif p_txn_header.transaction_type_id = WSMPCNST.SPLIT then
153 --First query to check if any reservations asscoiated with the starting job in the calling pgm itself--
154 --Check if parent job is resulting job.
155 --If yes, then check if there is update of assembly.
156 --If yes, then write to WIE 'Note: Starting job is reserved against sales order(s).Update Assembly transaction will result in deletion of the starting job reservations'
157 --and call inv_reservation_pub.delete_reservation
158 --Else, compare starting job's net qty with net qty of same job in resulting jobs.
159 --Write to WIE 'Note: New net quantity is less than the starting job's reserved quantity.This transaction will result in reduction in reserved quantity' and Call reduce_reservations( );
160 --populate l_rsv_old record with the reservation details of the SJ.
161 --populate l_mtl_rsv record with the RJ details for Supply source info and new quantity info.
162 --if parent job is not a resulting job, then write to WIE 'Note: Starting job isnot a resulting job.The reservations against sales order(s) for this job will be deleted..
163
164 If (p_sj_also_rj_index is not null) then
165
166 If p_resulting_jobs_tbl(p_sj_also_rj_index).split_has_update_assy = 1 then
167 --Write warning into concurrent log
168 fnd_message.set_name('WSM','WSM_RSV_UPD_ASSY');
169 l_msg_data := fnd_message.get;
170 fnd_file.put_line(fnd_file.log,l_msg_data);
171
172 l_return_status := FND_API.G_RET_STS_SUCCESS;
173 l_msg_count := 0;
174 l_msg_data := null;
175
176 l_msg_index := fnd_msg_pub.count_msg;
177
178 --log proc entry
179 --MP Delete Changes Start
180 BEGIN
181 select *
182 bulk collect into l_wsm_rsv_v_tbl
183 from wsm_reservations_v
184 where wip_entity_id = p_starting_jobs_tbl(p_rep_job_index).wip_entity_id;
185 EXCEPTION
186 when no_data_found then
187 return;
188 END;
189 --MP Delete Changes End
190 --MP Delete Changes
191 If l_wsm_rsv_v_tbl.count > 0 then --MP Delete Changes
192 For i in l_wsm_rsv_v_tbl.first .. l_wsm_rsv_v_tbl.last loop
193 l_rsv_old.reservation_id := l_wsm_rsv_v_tbl(i).reservation_id;
194 inv_reservation_pub.delete_reservation
195 (
196 p_api_version_number => 1.0
197 , p_init_msg_lst => fnd_api.g_true
198 , x_return_status => l_return_status
199 , x_msg_count => l_msg_count
200 , x_msg_data => l_msg_data
201 , p_rsv_rec => l_rsv_old
202 , p_serial_number => l_dummy_sn
203 );
204
205 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
206 --API failed--
207 WSM_log_PVT.update_errtbl(l_msg_index,l_msg_count);
208 --log that rsv api failed
209 IF (G_LOG_LEVEL_STATEMENT >= l_log_level) THEN
210 l_msg_tokens.delete;
211 WSM_log_PVT.logMessage (p_module_name => l_module ,
212 p_msg_text => 'inv_reservation_pub.delete_reservation failed',
213 p_stmt_num => l_stmt_num ,
214 p_msg_tokens => l_msg_tokens ,
215 p_fnd_log_level => G_LOG_LEVEL_STATEMENT ,
216 p_run_log_level => l_log_level
217 );
218 END IF;
219
220 IF l_return_status = fnd_api.g_ret_sts_error THEN
221 RAISE fnd_api.g_exc_error;
222 ELSE
223 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
224 END IF;
225 end if;
226 end loop; --MP delete reservations loop
227 end if; --MP Delete Changes :End of check on l_wsm_rsv_v_tbl.count > 0
228 --Elsif (p_starting_jobs_tbl(p_starting_jobs_tbl.first).net_quantity > p_resulting_jobs_tbl(p_sj_also_rj_index).net_quantity) then
229 ELSE
230 l_rsvd_qty := Wsm_reservations_pvt.check_reservation_quantity(
231 p_wip_entity_id => p_resulting_jobs_tbl(p_sj_also_rj_index).wip_entity_id,
232 P_org_id => p_starting_jobs_tbl(p_rep_job_index).organization_id,
233 P_inventory_item_id => p_starting_jobs_tbl(p_rep_job_index).primary_item_id
234 );
235 IF l_rsvd_qty > p_resulting_jobs_tbl(p_sj_also_rj_index).net_quantity then
236 --Write warning into concurrent log
237 fnd_message.set_name('WSM','WSM_RSV_SPLIT');
238 l_msg_data := fnd_message.get;
239 fnd_file.put_line(fnd_file.log,l_msg_data);
240
241 l_mtl_rsv_rec.action := 0;
242 l_mtl_rsv_rec.organization_id := p_starting_jobs_tbl(p_starting_jobs_tbl.first).organization_id;
243 l_mtl_rsv_rec.inventory_item_id := p_starting_jobs_tbl(p_starting_jobs_tbl.first).primary_item_id;
244 l_mtl_rsv_rec.supply_source_type_id := 5;
245 l_mtl_rsv_rec.supply_source_header_id := p_starting_jobs_tbl(p_starting_jobs_tbl.first).wip_entity_id;
246 l_mtl_rsv_rec.supply_source_line_id := null;
247 l_mtl_rsv_rec.expected_quantity := p_resulting_jobs_tbl(p_sj_also_rj_index).net_quantity;
248
249 select primary_uom_code
250 into l_expected_quantity_uom --l_mtl_rsv_rec.expected_quantity_uom
251 from mtl_system_items
252 where inventory_item_id =l_mtl_rsv_rec.inventory_item_id-- p_starting_jobs_tbl(p_starting_jobs_tbl.first).primary_item_id
253 and organization_id = l_mtl_rsv_rec.organization_id; --p_starting_jobs_tbl(p_starting_jobs_tbl.first).organization_id;
254
255
256 l_mtl_rsv_rec.expected_quantity_uom := l_expected_quantity_uom;
257
258 l_return_status := FND_API.G_RET_STS_SUCCESS;
259 l_msg_count := 0;
260 l_msg_data := null;
261
262 l_msg_index := fnd_msg_pub.count_msg;
263 --log proc entry
264 inv_maintain_reservation_pub.reduce_reservation
265 (
266 x_return_status => l_return_status
267 , x_msg_count => l_msg_count
268 , x_msg_data => l_msg_data
269 , x_quantity_modified => l_quantity_modified
270 , p_api_version_number => 1.0
271 , p_init_msg_lst => fnd_api.g_false
272 , p_mtl_maintain_rsv_rec => l_mtl_rsv_rec
273 , p_delete_flag => 'N'
274 , p_sort_by_criteria => null
275 );
276
277 IF l_return_status <> FND_API.G_RET_STS_SUCCESS then
278 --API failed--
279 WSM_log_PVT.update_errtbl(l_msg_index,l_msg_count);
280 --log that rsv api failed
281 IF (G_LOG_LEVEL_STATEMENT >= l_log_level) THEN
282 l_msg_tokens.delete;
283 WSM_log_PVT.logMessage (p_module_name => l_module ,
284 p_msg_text => 'inv_reservation_pub.reduce_reservation failed',
285 p_stmt_num => l_stmt_num ,
286 p_msg_tokens => l_msg_tokens ,
287 p_fnd_log_level => G_LOG_LEVEL_STATEMENT ,
288 p_run_log_level => l_log_level
289 );
290 END IF;
291 IF l_return_status = fnd_api.g_ret_sts_error THEN
292 RAISE fnd_api.g_exc_error;
293 ELSE
294 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
295 END IF;
296 END IF;
297 END IF;
298 End if;
299 Else --parent job not resulting job
300 --Write warning into concurrent log
301 fnd_message.set_name('WSM','WSM_RSV_SPLIT');
302 l_msg_data := fnd_message.get;
303 fnd_file.put_line(fnd_file.log,l_msg_data);
304
305 l_return_status := FND_API.G_RET_STS_SUCCESS;
306 l_msg_count := 0;
307 l_msg_data := null;
308
309 l_msg_index := fnd_msg_pub.count_msg;
310 --log proc entry
311 --MP Delete Changes Start
312 BEGIN
313 select *
314 bulk collect into l_wsm_rsv_v_tbl
315 from wsm_reservations_v
316 where wip_entity_id = p_starting_jobs_tbl(p_rep_job_index).wip_entity_id;
317 EXCEPTION
318 when no_data_found then
319 return;
320 END;
321 --MP Delete Changes End
322 --MP Delete Changes
323 If l_wsm_rsv_v_tbl.count > 0 then --MP Delete Changes
324 For i in l_wsm_rsv_v_tbl.first .. l_wsm_rsv_v_tbl.last loop
325 l_rsv_old.reservation_id := l_wsm_rsv_v_tbl(i).reservation_id;
326 --log proc entry
327 inv_reservation_pub.delete_reservation
328 (
329 p_api_version_number => 1.0
330 , p_init_msg_lst => fnd_api.g_true
331 , x_return_status => l_return_status
332 , x_msg_count => l_msg_count
333 , x_msg_data => l_msg_data
334 , p_rsv_rec => l_rsv_old
335 , p_serial_number => l_dummy_sn
336 );
337 --log return
338
339 IF l_return_status <> FND_API.G_RET_STS_SUCCESS then
340 --API failed--
341 WSM_log_PVT.update_errtbl(l_msg_index,l_msg_count);
342 --log that rsv api failed
343 IF (G_LOG_LEVEL_STATEMENT >= l_log_level) THEN
344 l_msg_tokens.delete;
345 WSM_log_PVT.logMessage (p_module_name => l_module ,
346 p_msg_text => 'inv_reservation_pub.delete_reservation failed',
347 p_stmt_num => l_stmt_num ,
348 p_msg_tokens => l_msg_tokens ,
349 p_fnd_log_level => G_LOG_LEVEL_STATEMENT ,
350 p_run_log_level => l_log_level
351 );
352 END IF;
353 IF l_return_status = fnd_api.g_ret_sts_error THEN
354 RAISE fnd_api.g_exc_error;
355 ELSE
356 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
357 END IF;
358 END IF;
359 end loop; --MP delete reservations loop
360 end if; --MP Delete Changes :End of check on l_wsm_rsv_v_tbl.count > 0
361
362 End if;
363 Elsif p_txn_header.transaction_type_id = WSMPCNST.MERGE then
364 l_rj_index := p_resulting_jobs_tbl.first;
365 -- Check if parent rep job is resulting job.
366 --If yes, for each job having assembly same as resulting job and reservation exists, call transfer_reservation ( ) to tranfser reservation to the resulting job.
367 --Else, for each job having assembly same as resulting job and reservation exists, call transfer_reservation ( ) to transfer reservations to new resulting job.
368 --If any starting job has reservation and assembly is different from rep-job,
369 --write to WIE 'Note: One or more Starting jobs with an assembly different from that of the representative job are reserved against sales order.
370 --Merge transaction will result in deletion of the reservations of these starting jobs.'
371 update wip_discrete_jobs
372 set net_quantity = start_quantity
373 where wip_entity_id = p_resulting_jobs_tbl(l_rj_index).wip_entity_id;
374
375 For l_counter in p_starting_jobs_tbl.first.. p_starting_jobs_tbl.last loop
376
377 --If p_sj_also_rj_index is null OR p_sj_also_rj_index <> l_counter then
378
379
380 If p_sj_also_rj_index is null OR p_rep_job_index <> l_counter then
381
382 l_rsv_exists := check_reservation_exists(p_wip_entity_id => p_starting_jobs_tbl(l_counter).wip_entity_id,
383 p_org_id => p_starting_jobs_tbl(l_counter).organization_id ,
384 p_inventory_item_id => p_starting_jobs_tbl(l_counter).primary_item_id
385 );
386
387 If l_rsv_exists then
388
389 If p_starting_jobs_tbl(l_counter).primary_item_id = p_starting_jobs_tbl(p_rep_job_index).primary_item_id then
390
391 --collect the SO rsv for the SJ from wsm_rsv_v
392 BEGIN
393 select *
394 bulk collect into l_wsm_rsv_v_tbl
395 from wsm_reservations_v
396 where wip_entity_id = p_starting_jobs_tbl(l_counter).wip_entity_id;
397 EXCEPTION
398 when no_data_found then
399 null;
400 END;
401
402 if l_wsm_rsv_v_tbl.count>0 then
403 --For each SO rsvn in wsm_reservations_v for p_starting_jobs_tbl (l_counter) loop
404 For i in l_wsm_rsv_v_tbl.first .. l_wsm_rsv_v_tbl.last loop
405
406 --old reservation info populated
407 l_rsv_old.reservation_id := l_wsm_rsv_v_tbl(i).reservation_id;
408 --these might not be needed as reservation_id itself would be sufficient--
409 l_rsv_old.supply_source_header_id := p_starting_jobs_tbl(l_counter).wip_entity_id;
410 l_rsv_old.supply_source_type_id := inv_reservation_global.g_source_type_wip;
411 l_rsv_old.inventory_item_id := p_starting_jobs_tbl(l_counter).primary_item_id;
412 l_rsv_old.organization_id := p_starting_jobs_tbl(l_counter).organization_id;
413
414 --Transfer to resulting job;
415 --l_rj_index := p_resulting_jobs_tbl.first;
416 l_rsv_new.supply_source_header_id := p_resulting_jobs_tbl(l_rj_index).wip_entity_id;
417 l_rsv_new.supply_source_type_id := inv_reservation_global.g_source_type_wip;
418
419 l_return_status := FND_API.G_RET_STS_SUCCESS;
420 l_msg_count := 0;
421 l_msg_data := null;
422
423 l_msg_index := fnd_msg_pub.count_msg;
424
425 inv_reservation_pub.transfer_reservation(
426 p_api_version_number => 1.0
427 , p_init_msg_lst => fnd_api.g_true
428 , x_return_status => l_return_status
429 , x_msg_count => l_msg_count
430 , x_msg_data => l_msg_data
431 , p_is_transfer_supply => fnd_api.g_true
432 , p_original_rsv_rec => l_rsv_old --SJ details
433 , p_to_rsv_rec => l_rsv_new --RJ details
434 , p_original_serial_number => l_dummy_sn -- no serial contorl
435 , p_to_serial_number => l_dummy_sn -- no serial control
436 , p_validation_flag => fnd_api.g_true
437 , x_to_reservation_id => l_new_rsv_id
438 );
439
440 --proc exit
441 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
442 --API failed--
443 WSM_log_PVT.update_errtbl(l_msg_index,l_msg_count);
444 --log that rsv api failed
445 IF (G_LOG_LEVEL_STATEMENT >= l_log_level) THEN
446 l_msg_tokens.delete;
447 WSM_log_PVT.logMessage (p_module_name => l_module ,
448 p_msg_text => 'inv_reservation_pub.transfer_reservation failed',
449 p_stmt_num => l_stmt_num ,
450 p_msg_tokens => l_msg_tokens ,
451 p_fnd_log_level => G_LOG_LEVEL_STATEMENT ,
452 p_run_log_level => l_log_level
453 );
454 END IF;
455 IF l_return_status = fnd_api.g_ret_sts_error THEN
456 RAISE FND_API.G_EXC_ERROR;
457 ELSE
458 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
459 END IF;
460 end if;
461
462 End loop;
463 end if;
464 Else
465 --Write warning into concurrent log
466 fnd_message.set_name('WSM','WSM_RSV_MERGE');
467 l_msg_data := fnd_message.get;
468 fnd_file.put_line(fnd_file.log,l_msg_data);
469
470 l_rsv_old.supply_source_header_id := p_starting_jobs_tbl(l_counter).wip_entity_id;
471 l_rsv_old.inventory_item_id := p_starting_jobs_tbl(l_counter).primary_item_id;
472 l_rsv_old.organization_id := p_starting_jobs_tbl(l_counter).organization_id;
473 l_rsv_old.supply_source_type_id := 5;
474
475 l_return_status := FND_API.G_RET_STS_SUCCESS;
476 l_msg_count := 0;
477 l_msg_data := null;
478
479 l_msg_index := fnd_msg_pub.count_msg;
480 --log proc entry
481 --MP Delete Changes Start
482 BEGIN
483 select *
484 bulk collect into l_wsm_rsv_v_tbl
485 from wsm_reservations_v
486 where wip_entity_id = p_starting_jobs_tbl(l_counter).wip_entity_id;
487 EXCEPTION
488 when no_data_found then
489 return;
490 END;
491 --MP Delete Changes End
492 --MP Delete Changes
493 If l_wsm_rsv_v_tbl.count > 0 then --MP Delete Changes
494 For i in l_wsm_rsv_v_tbl.first .. l_wsm_rsv_v_tbl.last loop
495 l_rsv_old.reservation_id := l_wsm_rsv_v_tbl(i).reservation_id;
496 inv_reservation_pub.Delete_reservation (
497 p_api_version_number => 1.0
498 , p_init_msg_lst => fnd_api.g_true
499 , x_return_status => l_return_status
500 , x_msg_count => l_msg_count
501 , x_msg_data => l_msg_data
502 , p_rsv_rec => l_rsv_old
503 , p_serial_number => l_dummy_sn
504 );
505
506 --proc exit
507 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
508 --API failed--
509 WSM_log_PVT.update_errtbl(l_msg_index,l_msg_count);
510 --log that rsv api failed
511 IF (G_LOG_LEVEL_STATEMENT >= l_log_level) THEN
512 l_msg_tokens.delete;
513 WSM_log_PVT.logMessage (p_module_name => l_module ,
514 p_msg_text => 'inv_reservation_pub.reduce_reservation failed',
515 p_stmt_num => l_stmt_num ,
516 p_msg_tokens => l_msg_tokens ,
517 p_fnd_log_level => G_LOG_LEVEL_STATEMENT ,
518 p_run_log_level => l_log_level
519 );
520 END IF;
521 IF l_return_status = fnd_api.g_ret_sts_error THEN
522 RAISE FND_API.G_EXC_ERROR;
523 ELSE
524 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
525 END IF;
526 end if;
527 end loop; --MP delete reservations loop
528 end if; --MP Delete Changes :End of check on l_wsm_rsv_v_tbl.count > 0
529
530 End if;
531 End if;
532 End if;
533 End loop;
534
535 update wip_discrete_jobs
536 set net_quantity = p_resulting_jobs_tbl(l_rj_index).net_quantity
537 where wip_entity_id = p_resulting_jobs_tbl(l_rj_index).wip_entity_id;
538
539 l_rsvd_qty := check_reservation_quantity(
540 p_wip_entity_id => p_resulting_jobs_tbl(l_rj_index).wip_entity_id,
541 p_org_id => p_resulting_jobs_tbl(l_rj_index).organization_id ,
542 p_inventory_item_id => p_resulting_jobs_tbl(l_rj_index).primary_item_id
543 );
544 if l_rsvd_qty > p_resulting_jobs_tbl(l_rj_index).net_quantity THEN
545 l_return_status := FND_API.G_RET_STS_SUCCESS;
546 l_msg_count := 0;
547 l_msg_data := null;
548 WSM_RESERVATIONS_PVT.Modify_reservations_jobupdate(
549 p_wip_entity_id => p_resulting_jobs_tbl(l_rj_index).wip_entity_id,
550 P_old_net_qty => l_rsvd_qty, --p_resulting_jobs_tbl(l_rj_index).net_quantity,
551 P_new_net_qty => p_resulting_jobs_tbl(l_rj_index).net_quantity,
552 P_inventory_item_id => p_resulting_jobs_tbl(l_rj_index).primary_item_id,
553 P_org_id => p_txn_header.organization_id,
554 P_status_type => p_resulting_jobs_tbl(l_rj_index).status_type,
555 x_return_status => l_return_status,
556 x_msg_count => l_msg_count,
557 x_msg_data => l_msg_data
558 ); --this is to handle the change in net qty if any.
559 if l_return_status <> fnd_api.g_ret_sts_success then
560 -- error out...
561
562 if( g_log_level_statement >= l_log_level ) then
563
564 l_msg_tokens.delete;
565 WSM_log_PVT.logMessage(p_module_name => l_module ,
566 p_msg_text => 'WSM_RESERVATIONS_PVT.Modify_reservations_jobupdate failed:'||l_msg_data,
567 p_stmt_num => l_stmt_num ,
568 p_msg_tokens => l_msg_tokens,
569 p_fnd_msg_level => G_MSG_LVL_ERROR ,
570 p_fnd_log_level => G_LOG_LEVEL_ERROR ,
571 p_run_log_level => l_log_level
572 );
573 END IF;
574 RAISE FND_API.G_EXC_ERROR;
575 end if;
576 END IF; --Check on reserved quantity > net_quantity
577 end if;
578
579 -- Standard call to get message count and if count is 1, get message info.
580 FND_MSG_PUB.Count_And_Get
581 ( p_encoded => 'F' ,
582 p_count => x_msg_count ,
583 p_data => x_msg_data
584 );
585
586 x_return_status:= FND_API.G_RET_STS_SUCCESS;
587 EXCEPTION
588 WHEN FND_API.G_EXC_ERROR THEN
589
590 ROLLBACK TO start_modify_rsv_wlt;
591 x_return_status := G_RET_ERROR;
592 FND_MSG_PUB.Count_And_Get ( p_encoded => 'F' ,
593 p_count => x_msg_count ,
594 p_data => x_msg_data
595 );
596
597 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
598
599 ROLLBACK TO start_modify_rsv_wlt;
600 x_return_status := G_RET_UNEXPECTED;
601
602 FND_MSG_PUB.Count_And_Get ( p_encoded => 'F' ,
603 p_count => x_msg_count ,
604 p_data => x_msg_data
605 );
606 WHEN OTHERS THEN
607
608 ROLLBACK TO start_modify_rsv_wlt;
609 x_return_status := G_RET_UNEXPECTED;
610
611 IF (G_LOG_LEVEL_UNEXPECTED >= l_log_level) OR
612 (FND_MSG_PUB.check_msg_level(G_MSG_LVL_UNEXP_ERROR))
613 THEN
614 WSM_log_PVT.handle_others( p_module_name => l_module ,
615 p_stmt_num => l_stmt_num ,
616 p_fnd_log_level => G_LOG_LEVEL_UNEXPECTED ,
617 p_run_log_level => l_log_level
618 );
619 END IF;
620
621 FND_MSG_PUB.Count_And_Get ( p_encoded => 'F' ,
622 p_count => x_msg_count ,
623 p_data => x_msg_data
624 );
625 end;
626
627 Procedure modify_reservations_move ( p_wip_entity_id IN NUMBER,
628 P_inventory_item_id IN NUMBER,
629 P_org_id IN NUMBER,
630 P_txn_type IN NUMBER,
631 P_net_qty IN NUMBER,
632 x_return_status OUT NOCOPY VARCHAR2,
633 x_msg_count OUT NOCOPY NUMBER,
634 x_msg_data OUT NOCOPY VARCHAR2)
635 is
636 l_rsv_old inv_reservation_global.mtl_reservation_rec_type;
637 l_rsv_new inv_reservation_global.mtl_reservation_rec_type;
638 l_dummy_sn inv_reservation_global.serial_number_tbl_type;
639 --l_mtl_rsv_rec inv_reservations_global.mtl_rsv_tbl_type;
640 l_wsm_rsvn_tbl t_wsm_reservations;
641
642 l_new_rsv_id NUMBER;
643 l_quantity_modified NUMBER;
644
645 /* Status variables */
646 l_return_status VARCHAR2(1);
647 l_msg_count NUMBER;
648 l_msg_data VARCHAR2(2000);
649
650 l_rsvd_qty NUMBER := -1;
651 l_reservation_quantity NUMBER := -1;
652
653 l_status_type NUMBER; -- Added for bug 5286219
654
655 -- Logging variables.....
656 l_msg_tokens WSM_Log_PVT.token_rec_tbl;
657 l_log_level number := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
658
659 l_stmt_num NUMBER;
660 l_module CONSTANT VARCHAR2(100) := 'wsm.plsql.WSM_ITEM_PVT.ProcA';
661 l_param_tbl WSM_Log_PVT.param_tbl_type;
662
663 l_msg_index number;
664 -- Logging variables...
665
666
667 begin
668 /*First query to check if any reservations asscoiated with the starting job in the calling pgm itself by calling the function check_reservation_exists*/
669 /*Call this proc only if txn_type not in (1,4),ie,normal move or undo. */
670 l_wsm_rsvn_tbl.delete;
671
672 /* Have a starting point*/
673 savepoint start_modify_rsv_move;
674
675 l_stmt_num := 10;
676 /* Initialize API return status to success */
677 x_return_status := FND_API.G_RET_STS_SUCCESS;
678
679 /*have this call wherever wsm_reservations_v is used*/
680 --MO_GLOBAL.SET_POLICY_CONTEXT ('S', p_org_id);
681
682 l_rsv_new.supply_source_header_id := p_wip_entity_id;
683 --l_rsv_new.inventory_item_id :=p_inventory_item_id;
684 --l_rsv_new.organization_id := p_org_id;
685
686 If p_txn_type = 2 then --completion txn
687
688 --Insert the reservations for this job in the WSM_RESERVATIONS table.Before that check if the reserved qty against the job is more than what exactly the job has at completion.If that is the case,reduce reservations associated with the job.
689
690 l_rsvd_qty := check_reservation_quantity(p_wip_entity_id => p_wip_entity_id,
691 P_org_id => p_org_id,
692 P_inventory_item_id => p_inventory_item_id
693 ) ;
694 If l_rsvd_qty > p_net_qty then
695 fnd_message.set_name('WSM','WSM_NET_QTY_LOW');
696 l_msg_data := fnd_message.get;
697 fnd_file.put_line(fnd_file.log,l_msg_data);
698
699 l_mtl_rsv_rec.action := 0;
700 l_mtl_rsv_rec.organization_id := p_org_id;
701 l_mtl_rsv_rec.inventory_item_id := p_inventory_item_id;
702 l_mtl_rsv_rec.supply_source_type_id := 5;
703 l_mtl_rsv_rec.supply_source_header_id := p_wip_entity_id;
704 l_mtl_rsv_rec.supply_source_line_id := null;
705 l_mtl_rsv_rec.expected_quantity := p_net_qty;
706
707 select primary_uom_code
708 into l_mtl_rsv_rec.expected_quantity_uom
709 from mtl_system_items
710 where inventory_item_id =l_mtl_rsv_rec.inventory_item_id
711 and organization_id = l_mtl_rsv_rec.organization_id;
712
713 l_return_status := FND_API.G_RET_STS_SUCCESS;
714 l_msg_count := 0;
715 l_msg_data := null;
716
717 l_msg_index := fnd_msg_pub.count_msg;
718
719 --inv_reservation_pub.reduce_reservations (
720 -- x_return_status => l_return_status
721 -- , x_msg_count =>l_msg_count
722 -- , x_msg_data =>l_msg_data
723 -- , x_quantity_modified => l_quantity_modified
724 -- , p_api_version_number =>1.0
725 -- , p_init_msg_lst =>fnd_api.g_false
726 -- , p_mtl_rsv_rec =>l_mtl_rsv_rec
727 -- , delete_flag =>'N'
728 -- );
729 --
730 --proc exit
731 inv_maintain_reservation_pub.reduce_reservation
732 (
733 x_return_status => l_return_status
734 , x_msg_count => l_msg_count
735 , x_msg_data => l_msg_data
736 , x_quantity_modified => l_quantity_modified
737 , p_api_version_number => 1.0
738 , p_init_msg_lst => fnd_api.g_false
739 , p_mtl_maintain_rsv_rec => l_mtl_rsv_rec
740 , p_delete_flag => 'N'
741 , p_sort_by_criteria => null
742 );
743 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
744 /*API failed*/
745 WSM_log_PVT.update_errtbl(l_msg_index,l_msg_count);
746 --log that rsv api failed
747 IF (G_LOG_LEVEL_STATEMENT >= l_log_level) THEN
748 l_msg_tokens.delete;
749 WSM_log_PVT.logMessage (p_module_name => l_module ,
750 p_msg_text => 'inv_reservation_pub.reduce_reservation failed',
751 p_stmt_num => l_stmt_num ,
752 p_msg_tokens => l_msg_tokens ,
753 p_fnd_log_level => G_LOG_LEVEL_STATEMENT ,
754 p_run_log_level => l_log_level
755 );
756 END IF;
757 IF l_return_status = fnd_api.g_ret_sts_error THEN
758 RAISE FND_API.G_EXC_ERROR;
759 ELSE
760 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
761 END IF;
762 end if;
763
764 End if;
765
766 INSERT INTO wsm_reservations (
767 Wip_entity_id,
768 Reservation_id,
769 Demand_source_header_id,
770 Demand_source_line_id,
771 Reserved_qty)
772 (select wip_entity_id,
773 reservation_id,
774 demand_source_header_id,
775 demand_source_line_id,
776 primary_quantity
777 from wsm_reservations_v
778 where wip_entity_id= p_wip_entity_id
779 and organization_id = p_org_id
780 and inventory_item_id = p_inventory_item_id
781 );
782
783 Elsif p_txn_type = 3 then --return transaction
784
785 --Check if entries exist for this job in WSM_RESERVATIONS table. If yes, go to nxt step. Else, return.
786 BEGIN
787 select *
788 bulk collect into l_wsm_rsvn_tbl
789 from wsm_reservations
790 where wip_entity_id = p_wip_entity_id;
791
792 EXCEPTION
793 when no_data_found then
794 return;
795 END;
796 --If (entry exists in WSM_RESERVATIONS for this job) then
797 If l_wsm_rsvn_tbl.count > 0 then
798
799 --Check if demand exists by querying the mtl_reservations table. If yes, then query the corresponding demand and supply details from history table and call create_reservations( ) to create them again against the job.
800
801 -- Added for Bug 5286219. Temporarily changing the status type of the job to
802 -- Released status so that reservation can be transferred from inventory back to LBJ.
803
804 BEGIN
805 select status_type
806 into l_status_type
807 from wip_discrete_jobs
808 where wip_entity_id = p_wip_entity_id
809 and organization_id = p_org_id;
810
811 update wip_discrete_jobs
812 set status_type = 3
813 where wip_entity_id = p_wip_entity_id
814 and organization_id = p_org_id;
815 EXCEPTION
816 when no_data_found then
817 return;
818 END;
819
820 --For each SO reservation against the job in WSM_RESERVATIONS loop
821 For j in l_wsm_rsvn_tbl.first .. l_wsm_rsvn_tbl.last loop
822
823 BEGIN
824 select primary_reservation_quantity
825 into l_reservation_quantity
826 from mtl_reservations
827 where reservation_id = l_wsm_rsvn_tbl(j).reservation_id;
828 EXCEPTION
829 when no_data_found then
830 l_reservation_quantity := -1;
831 END;
832
833 If l_reservation_quantity <> -1 then
834 --Transfer_reservation to job A with l_rsv_new.reservation_qty = min (mtl_reservations.reservation_qty, wsm_reservations.reserved_qty)
835 --Call the API as below.
836
837 --populate l_rsv_old record with reservation details against the inventory for this SO from mtl_reservations.
838 l_rsv_old.reservation_id := l_wsm_rsvn_tbl(j).reservation_id;
839
840 --populate l_rsv_new record with changed reservation details against this job for this SO from wsm_reservations.
841 l_rsv_new.supply_source_header_id := p_wip_entity_id;
842 l_rsv_new.supply_source_type_id := 5;
843 l_rsv_new.subinventory_code := null;
844 l_rsv_new.locator_id := null;
845 l_rsv_new.lot_number := NULL;
846 l_rsv_new.primary_reservation_quantity :=l_wsm_rsvn_tbl(j).Reserved_qty;
847 IF l_reservation_quantity < l_rsv_new.primary_reservation_quantity THEN
848 l_rsv_new.primary_reservation_quantity := l_reservation_quantity;
849 END IF;
850
851 l_return_status := FND_API.G_RET_STS_SUCCESS;
852 l_msg_count := 0;
853 l_msg_data := null;
854
855 l_msg_index := fnd_msg_pub.count_msg;
856
857 inv_reservation_pub.transfer_reservation( p_api_version_number => 1.0
858 , p_init_msg_lst => fnd_api.g_true
859 , x_return_status => l_return_status
860 , x_msg_count => l_msg_count
861 , x_msg_data => l_msg_data
862 , p_is_transfer_supply => fnd_api.g_true
863 , p_original_rsv_rec => l_rsv_old
864 , p_to_rsv_rec => l_rsv_new
865 , p_original_serial_number => l_dummy_sn -- no serial contorl
866 , p_to_serial_number => l_dummy_sn -- no serial control
867 , p_validation_flag => fnd_api.g_true
868 , x_to_reservation_id => l_new_rsv_id
869 );
870
871 --proc exit
872 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
873 /*API failed*/
874 WSM_log_PVT.update_errtbl(l_msg_index,l_msg_count);
875 --log that rsv api failed
876 IF (G_LOG_LEVEL_STATEMENT >= l_log_level) THEN
877 l_msg_tokens.delete;
878 WSM_log_PVT.logMessage (p_module_name => l_module ,
879 p_msg_text => 'inv_reservation_pub.transfer_reservation failed',
880 p_stmt_num => l_stmt_num ,
881 p_msg_tokens => l_msg_tokens ,
882 p_fnd_log_level => G_LOG_LEVEL_STATEMENT ,
883 p_run_log_level => l_log_level
884 );
885 END IF;
886 IF l_return_status = fnd_api.g_ret_sts_error THEN
887 RAISE FND_API.G_EXC_ERROR;
888 ELSE
889 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
890 END IF;
891 END IF;
892
893 END IF;
894 END LOOP;
895
896 -- Added for Bug 5286219. Restoring back the status type of the job to original
897 -- status before it was updated to Released for trf the reservation.
898
899 update wip_discrete_jobs
900 set status_type = l_status_type
901 where wip_entity_id = p_wip_entity_id
902 and organization_id = p_org_id;
903
904 end if;
905 /*now delete the rows corresponding to this job in wsm_reservations*/
906 delete from wsm_reservations
907 where wip_entity_id = p_wip_entity_id;
908 end if;
909
910 -- Standard call to get message count and if count is 1, get message info.
911 FND_MSG_PUB.Count_And_Get
912 ( p_encoded => 'F' ,
913 p_count => x_msg_count ,
914 p_data => x_msg_data
915 );
916
917 x_return_status:= FND_API.G_RET_STS_SUCCESS;
918 EXCEPTION
919 WHEN FND_API.G_EXC_ERROR THEN
920
921 ROLLBACK TO start_modify_rsv_move;
922 x_return_status := G_RET_ERROR;
923 FND_MSG_PUB.Count_And_Get ( p_encoded => 'F' ,
924 p_count => x_msg_count ,
925 p_data => x_msg_data
926 );
927
928 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
929
930 ROLLBACK TO start_modify_rsv_move;
931 x_return_status := G_RET_UNEXPECTED;
932
933 FND_MSG_PUB.Count_And_Get ( p_encoded => 'F' ,
934 p_count => x_msg_count ,
935 p_data => x_msg_data
936 );
937 WHEN OTHERS THEN
938
939 ROLLBACK TO start_modify_rsv_move;
940 x_return_status := G_RET_UNEXPECTED;
941
942 IF (G_LOG_LEVEL_UNEXPECTED >= l_log_level) OR
943 (FND_MSG_PUB.check_msg_level(G_MSG_LVL_UNEXP_ERROR))
944 THEN
945 WSM_log_PVT.handle_others( p_module_name => l_module ,
946 p_stmt_num => l_stmt_num ,
947 p_fnd_log_level => G_LOG_LEVEL_UNEXPECTED ,
948 p_run_log_level => l_log_level
949 );
950 END IF;
951
952 FND_MSG_PUB.Count_And_Get ( p_encoded => 'F' ,
953 p_count => x_msg_count ,
954 p_data => x_msg_data
955 );
956 end;
957
958 Procedure modify_reservations_jobupdate (p_wip_entity_id IN NUMBER,
959 P_old_net_qty IN NUMBER ,
960 P_new_net_qty IN NUMBER,
961 P_inventory_item_id IN NUMBER,
962 P_org_id IN NUMBER,
963 P_status_type IN NUMBER,
964 x_return_status OUT NOCOPY VARCHAR2,
965 x_msg_count OUT NOCOPY NUMBER,
966 x_msg_data OUT NOCOPY VARCHAR2)
967 is
968 l_rsv inv_reservation_global.mtl_reservation_rec_type;
969 l_dummy_sn inv_reservation_global.serial_number_tbl_type;
970 --l_mtl_rsv_rec inv_reservations_global.mtl_rsv_tbl_type;
971
972 l_rsv_id NUMBER;
973 l_rsv_exists number := 0;
974 l_expected_quantity_uom varchar2(3);
975 l_quantity_modified number;
976
977 /* Status variables */
978 l_return_status VARCHAR2(1);
979 l_msg_count NUMBER;
980 l_msg_data VARCHAR2(2000);
981
982 -- Logging variables.....
983 l_msg_tokens WSM_Log_PVT.token_rec_tbl;
984 l_log_level number := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
985
986 l_stmt_num NUMBER;
987 l_module CONSTANT VARCHAR2(100) := 'wsm.plsql.WSM_RESERVATIONS_PVT.modify_reservations_jobupdate';
988 l_param_tbl WSM_Log_PVT.param_tbl_type;
989
990 l_msg_index number;
991 -- Logging variables...
992
993
994 begin
995 /*First query to check if any reservations asscoiated with the starting job in the calling pgm itself*/
996
997 /* Have a starting point*/
998 savepoint start_modify_rsv_jobupdate;
999
1000 l_stmt_num := 10;
1001 /* Initialize API return status to success */
1002 x_return_status := FND_API.G_RET_STS_SUCCESS;
1003
1004 /*have this call wherever wsm_reservations_v is used*/
1005 --MO_GLOBAL.SET_POLICY_CONTEXT ('S', p_org_id);
1006
1007 l_rsv.supply_source_header_id := p_wip_entity_id;
1008 l_rsv.inventory_item_id :=p_inventory_item_id;
1009 l_rsv.organization_id := p_org_id;
1010 l_rsv.supply_source_type_id := 5;
1011 -- If condition Modified for bug 5290496
1012 If p_new_net_qty < p_old_net_qty and p_status_type <> 7 and p_new_net_qty <>0 then
1013 -- In case of interface,Write to WIE 'Note: Available quantity of the job is below the net quantity.'
1014 fnd_message.set_name('WSM','WSM_NET_QTY_LOW');
1015 l_msg_data := fnd_message.get;
1016 fnd_file.put_line(fnd_file.log,l_msg_data);
1017
1018 --l_mtl_rsv_rec.action := 1;
1019 --l_mtl_rsv_rec.organization_id := p_org_id;
1020 --l_mtl_rsv_rec.inventory_item_id := p_inventory_item_id;
1021 --l_mtl_rsv_rec.supply_source_type_id := 5;
1022 --l_mtl_rsv_rec.supply_source_header_id := p_wip_entity_id;
1023 --l_mtl_rsv_rec.supply_source_line_id := null;
1024 --l_mtl_rsv_rec.expected_quantity := p_new_net_qty;
1025
1026 l_return_status := FND_API.G_RET_STS_SUCCESS;
1027 l_msg_count := 0;
1028 l_msg_data := null;
1029
1030 l_msg_index := fnd_msg_pub.count_msg;
1031
1032 --inv_reservation_pub.reduce_reservations (
1033 -- x_return_status => l_return_status
1034 -- , x_msg_count =>l_msg_count
1035 -- , x_msg_data =>l_msg_data
1036 -- , x_quantity_modified => l_quantity_modified
1037 -- , p_api_version_number =>1.0
1038 -- , p_init_msg_lst =>fnd_api.g_false
1039 -- , p_mtl_rsv_rec =>l_mtl_rsv_rec
1040 -- , delete_flag => 'N'
1041 -- );
1042 --proc exit
1043
1044 l_mtl_rsv_rec.action := 0;
1045 l_mtl_rsv_rec.organization_id := p_org_id;
1046 l_mtl_rsv_rec.inventory_item_id := p_inventory_item_id;
1047 l_mtl_rsv_rec.supply_source_type_id := 5;
1048 l_mtl_rsv_rec.supply_source_header_id := p_wip_entity_id;
1049 l_mtl_rsv_rec.supply_source_line_id := null;
1050 l_mtl_rsv_rec.expected_quantity := p_new_net_qty;
1051
1052 select primary_uom_code
1053 into l_expected_quantity_uom --l_mtl_rsv_rec.expected_quantity_uom
1054 from mtl_system_items
1055 where inventory_item_id =l_mtl_rsv_rec.inventory_item_id
1056 and organization_id = l_mtl_rsv_rec.organization_id;
1057
1058
1059 l_mtl_rsv_rec.expected_quantity_uom := l_expected_quantity_uom;
1060
1061 l_return_status := FND_API.G_RET_STS_SUCCESS;
1062 l_msg_count := 0;
1063 l_msg_data := null;
1064
1065 l_msg_index := fnd_msg_pub.count_msg;
1066 inv_maintain_reservation_pub.reduce_reservation
1067 (
1068 x_return_status => l_return_status
1069 , x_msg_count => l_msg_count
1070 , x_msg_data => l_msg_data
1071 , x_quantity_modified => l_quantity_modified
1072 , p_api_version_number => 1.0
1073 , p_init_msg_lst => fnd_api.g_false
1074 , p_mtl_maintain_rsv_rec => l_mtl_rsv_rec
1075 , p_delete_flag => 'N'
1076 , p_sort_by_criteria => null
1077 );
1078 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1079 /*API failed*/
1080 WSM_log_PVT.update_errtbl(l_msg_index,l_msg_count);
1081 --log that rsv api failed
1082 IF (G_LOG_LEVEL_STATEMENT >= l_log_level) THEN
1083 l_msg_tokens.delete;
1084 WSM_log_PVT.logMessage (p_module_name => l_module ,
1085 p_msg_text => 'inv_reservation_pub.reduce_reservation failed',
1086 p_stmt_num => l_stmt_num ,
1087 p_msg_tokens => l_msg_tokens ,
1088 p_fnd_log_level => G_LOG_LEVEL_STATEMENT ,
1089 p_run_log_level => l_log_level
1090 );
1091 END IF;
1092 IF l_return_status = fnd_api.g_ret_sts_error THEN
1093 RAISE FND_API.G_EXC_ERROR;
1094 ELSE
1095 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1096 END IF;
1097 end if;
1098 Elsif p_status_type = 7 then
1099 --In case of interface, Write to WIE 'Note: Job is reserved against sales order(s). Cancelling the job will result in deletion of Job's reservations.'
1100 fnd_message.set_name('WSM','WSM_RSV_JOB_CANCEL');
1101 l_msg_data := fnd_message.get;
1102 fnd_file.put_line(fnd_file.log,l_msg_data);
1103
1104 l_return_status := FND_API.G_RET_STS_SUCCESS;
1105 l_msg_count := 0;
1106 l_msg_data := null;
1107
1108 l_msg_index := fnd_msg_pub.count_msg;
1109
1110 --log proc entry
1111
1112 -- START: Adding for bug fix 5347562.
1113 -- For deleting reservations we need to use reduce reservation with p_delete_flag = 'Y'
1114
1115 l_mtl_rsv_rec.action := 0;
1116 l_mtl_rsv_rec.organization_id := p_org_id;
1117 l_mtl_rsv_rec.inventory_item_id := p_inventory_item_id;
1118 l_mtl_rsv_rec.supply_source_type_id := 5;
1119 l_mtl_rsv_rec.supply_source_header_id := p_wip_entity_id;
1120 l_mtl_rsv_rec.supply_source_line_id := null;
1121 l_mtl_rsv_rec.expected_quantity := p_new_net_qty;
1122
1123 inv_maintain_reservation_pub.reduce_reservation
1124 (
1125 x_return_status => l_return_status
1126 , x_msg_count => l_msg_count
1127 , x_msg_data => l_msg_data
1128 , x_quantity_modified => l_quantity_modified
1129 , p_api_version_number => 1.0
1130 , p_init_msg_lst => fnd_api.g_false
1131 , p_mtl_maintain_rsv_rec => l_mtl_rsv_rec
1132 , p_delete_flag => 'Y'
1133 , p_sort_by_criteria => null
1134 );
1135 -- END: Adding for bug fix 5347562.
1136 -- Commenting call to delete_reservation API for bug 5347562.
1137 /* inv_reservation_pub.delete_reservation
1138 (
1139 p_api_version_number => 1.0
1140 , p_init_msg_lst => fnd_api.g_true
1141 , x_return_status => l_return_status
1142 , x_msg_count => l_msg_count
1143 , x_msg_data => l_msg_data
1144 , p_rsv_rec => l_rsv
1145 , p_serial_number => l_dummy_sn
1146 ); */
1147
1148 --proc exit
1149 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1150 /*API failed*/
1151 WSM_log_PVT.update_errtbl(l_msg_index,l_msg_count);
1152 --log that rsv api failed
1153 IF (G_LOG_LEVEL_STATEMENT >= l_log_level) THEN
1154 l_msg_tokens.delete;
1155 WSM_log_PVT.logMessage (p_module_name => l_module ,
1156 p_msg_text => 'inv_maintain_reservation_pub.reduce_reservation',
1157 p_stmt_num => l_stmt_num ,
1158 p_msg_tokens => l_msg_tokens ,
1159 p_fnd_log_level => G_LOG_LEVEL_STATEMENT ,
1160 p_run_log_level => l_log_level
1161 );
1162 END IF;
1163 IF l_return_status = fnd_api.g_ret_sts_error THEN
1164 RAISE FND_API.G_EXC_ERROR;
1165 ELSE
1166 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1167 END IF;
1168 end if;
1169 elsif p_new_net_qty =0 and p_status_type <> 7 then -- Added for bug 5290496: START
1170 -- In case of interface,Write to WIE 'Note: Available quantity of the job is below the net quantity.'
1171 fnd_message.set_name('WSM','WSM_NET_QTY_LOW');
1172 l_msg_data := fnd_message.get;
1173 fnd_file.put_line(fnd_file.log,l_msg_data);
1174 l_return_status := FND_API.G_RET_STS_SUCCESS;
1175 l_msg_count := 0;
1176 l_msg_data := null;
1177
1178 l_msg_index := fnd_msg_pub.count_msg;
1179 -- Commenting call to delete_reservation API for bug 5347562.
1180 /*
1181 inv_reservation_pub.delete_reservation
1182 (
1183 p_api_version_number => 1.0
1184 , p_init_msg_lst => fnd_api.g_true
1185 , x_return_status => l_return_status
1186 , x_msg_count => l_msg_count
1187 , x_msg_data => l_msg_data
1188 , p_rsv_rec => l_rsv
1189 , p_serial_number => l_dummy_sn
1190 ); */
1191
1192 -- START: Adding for bug fix 5347562.
1193 -- For deleting reservations we need to use reduce reservation with p_delete_flag = 'Y'
1194
1195 l_mtl_rsv_rec.action := 0;
1196 l_mtl_rsv_rec.organization_id := p_org_id;
1197 l_mtl_rsv_rec.inventory_item_id := p_inventory_item_id;
1198 l_mtl_rsv_rec.supply_source_type_id := 5;
1199 l_mtl_rsv_rec.supply_source_header_id := p_wip_entity_id;
1200 l_mtl_rsv_rec.supply_source_line_id := null;
1201 l_mtl_rsv_rec.expected_quantity := p_new_net_qty;
1202
1203 inv_maintain_reservation_pub.reduce_reservation
1204 (
1205 x_return_status => l_return_status
1206 , x_msg_count => l_msg_count
1207 , x_msg_data => l_msg_data
1208 , x_quantity_modified => l_quantity_modified
1209 , p_api_version_number => 1.0
1210 , p_init_msg_lst => fnd_api.g_false
1211 , p_mtl_maintain_rsv_rec => l_mtl_rsv_rec
1212 , p_delete_flag => 'Y'
1213 , p_sort_by_criteria => null
1214 );
1215 -- END: Adding for bug fix 5347562.
1216 if l_return_status <> FND_API.G_RET_STS_SUCCESS then /*API failed*/
1217 WSM_log_PVT.update_errtbl(l_msg_index,l_msg_count);
1218 --log that rsv api failed
1219 IF (G_LOG_LEVEL_STATEMENT >= l_log_level) THEN
1220 l_msg_tokens.delete;
1221 WSM_log_PVT.logMessage (p_module_name => l_module ,
1222 p_msg_text => 'inv_maintain_reservation_pub.reduce_reservation',
1223 p_stmt_num => l_stmt_num ,
1224 p_msg_tokens => l_msg_tokens ,
1225 p_fnd_log_level => G_LOG_LEVEL_STATEMENT ,
1226 p_run_log_level => l_log_level
1227 );
1228 END IF;
1229 IF l_return_status = fnd_api.g_ret_sts_error THEN
1230 RAISE FND_API.G_EXC_ERROR;
1231 ELSE
1232 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1233 END IF;
1234 end if; -- Added for bug 5290496: END
1235 end if;
1236
1237
1238 x_return_status:= FND_API.G_RET_STS_SUCCESS;
1239 -- Standard call to get message count and if count is 1, get message info.
1240 FND_MSG_PUB.Count_And_Get
1241 ( p_encoded => 'F' ,
1242 p_count => x_msg_count ,
1243 p_data => x_msg_data
1244 );
1245
1246 x_return_status:= FND_API.G_RET_STS_SUCCESS;
1247 EXCEPTION
1248 WHEN FND_API.G_EXC_ERROR THEN
1249
1250 ROLLBACK TO start_modify_rsv_jobupdate;
1251 x_return_status := G_RET_ERROR;
1252 FND_MSG_PUB.Count_And_Get ( p_encoded => 'F' ,
1253 p_count => x_msg_count ,
1254 p_data => x_msg_data
1255 );
1256
1257 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1258
1259 ROLLBACK TO start_modify_rsv_jobupdate;
1260 x_return_status := G_RET_UNEXPECTED;
1261
1262 FND_MSG_PUB.Count_And_Get ( p_encoded => 'F' ,
1263 p_count => x_msg_count ,
1264 p_data => x_msg_data
1265 );
1266 WHEN OTHERS THEN
1267
1268 ROLLBACK TO start_modify_rsv_jobupdate;
1269 x_return_status := G_RET_UNEXPECTED;
1270
1271 IF (G_LOG_LEVEL_UNEXPECTED >= l_log_level) OR
1272 (FND_MSG_PUB.check_msg_level(G_MSG_LVL_UNEXP_ERROR))
1273 THEN
1274 WSM_log_PVT.handle_others( p_module_name => l_module ,
1275 p_stmt_num => l_stmt_num ,
1276 p_fnd_log_level => G_LOG_LEVEL_UNEXPECTED ,
1277 p_run_log_level => l_log_level
1278 );
1279 END IF;
1280
1281 FND_MSG_PUB.Count_And_Get ( p_encoded => 'F' ,
1282 p_count => x_msg_count ,
1283 p_data => x_msg_data
1284 );
1285 end;
1286
1287 Function check_reservation_quantity (p_wip_entity_id IN NUMBER,
1288 P_org_id IN NUMBER,
1289 P_inventory_item_id IN NUMBER
1290 )
1291 Return NUMBER
1292 is
1293 L_rsvd_qty number :=0;
1294
1295 BEGIN
1296 /*have this call wherever wsm_reservations_v is used*/
1297 --MO_GLOBAL.SET_POLICY_CONTEXT ('S', p_org_id);
1298 BEGIN
1299
1300 -- modified the SQL to have primary quantity instead of reservation qty.
1301 Select sum (primary_quantity)
1302 into l_rsvd_qty
1303 from wsm_reservations_v
1304 where wip_entity_id = p_wip_entity_id
1305 and organization_id = p_org_id
1306 and inventory_item_id = p_inventory_item_id;
1307 EXCEPTION
1308 when no_data_found then
1309 Return L_rsvd_qty;
1310 END;
1311
1312 Return l_rsvd_qty;
1313 end check_reservation_quantity;
1314
1315
1316 Function check_reservation_exists (p_wip_entity_id IN NUMBER,
1317 P_org_id IN NUMBER,
1318 P_inventory_item_id IN NUMBER
1319 )
1320 Return BOOLEAN
1321 is
1322
1323 l_rsv_exists number := 0 ;
1324
1325 BEGIN
1326 /*have this call wherever wsm_reservations_v is used*/
1327 --MO_GLOBAL.SET_POLICY_CONTEXT ('S', p_org_id);
1328 BEGIN
1329 select 1 into l_rsv_exists
1330 from wsm_reservations_v
1331 where wip_entity_id = p_wip_entity_id
1332 and organization_id = p_org_id
1333 and inventory_item_id = p_inventory_item_id
1334 and rownum = 1;
1335 EXCEPTION
1336 when no_data_found then
1337 Return false;
1338 END;
1339
1340 If l_rsv_exists = 1 then
1341 Return true;
1342 Else
1343 Return false;
1344 end if;
1345 end check_reservation_exists;
1346
1347 end WSM_RESERVATIONS_PVT;