[Home] [Help]
PACKAGE BODY: APPS.FLM_KANBAN_TRANSFER
Source
1 PACKAGE BODY FLM_KANBAN_TRANSFER AS
2 /* $Header: FLMKTRFB.pls 120.5.12020000.2 2012/07/13 10:57:21 sisankar ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(20) := 'FLM_KANBAN_TRANSFER';
5 G_LOG_MODULE CONSTANT VARCHAR2(40) := 'flm.plsql.' || G_PKG_NAME || '.';
6
7
8 /******************************************************************************************
9 *
10 * PROCEDURE
11 * GET_AVAILABLE_TO_TRANSACT
12 *
13 * DESCRIPTION
14 * This procedure returns the Available to Transact Quantity
15 *
16 * PARAMETERS
17 * ==========
18 * NAME TYPE DESCRIPTION
19 * ----------------- -------- ---------------------------------------------
20 * p_inventory_item_id IN This parameter passes the Inventory Item Id.
21 * p_source_organization_id IN This parameter passes the source organization id.
22 * p_source_subinventory IN This parameter passes the source subinventory.
23 * p_source_locator_id IN This parameter passes the source locator id.
24 * x_available_to_transact OUT Returns the Available to Transact Quantity
25 * x_retcode OUT Returns the Return Code of the procedure
26 * x_errmsg OUT Returns the Error Message , if any
27 *
28 *PREREQUISITES
29 * None
30 *
31 * CALLED BY
32 * complete_inter_process
33 *
34 *************************************************************************************************/
35 PROCEDURE get_available_to_transact
36 ( p_inventory_item_id IN NUMBER,
37 p_subinventory_name IN VARCHAR2,
38 p_locator_id IN NUMBER,
39 p_source_organization_id IN NUMBER,
40 p_source_subinventory IN VARCHAR2,
41 p_source_locator_id IN NUMBER,
42 x_available_to_transact OUT NOCOPY NUMBER,
43 x_retcode OUT NOCOPY VARCHAR2,
44 x_errmsg OUT NOCOPY VARCHAR2
45 )
46 IS
47
48 l_api_name CONSTANT VARCHAR2(30) := 'get_available_to_transact';
49 l_log_module CONSTANT VARCHAR2(80) := G_LOG_MODULE || l_api_name;
50
51 l_msg_count NUMBER;
52 l_qoh NUMBER;
53 l_rqoh NUMBER;
54 l_qr NUMBER;
55 l_qs NUMBER;
56 l_att NUMBER;
57 l_atr NUMBER;
58
59 BEGIN
60
61 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
62 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
63 l_log_module || '.begin',
64 'Parameters: ' ||
65 'p_inventory_item_id = ' || p_inventory_item_id || ', ' ||
66 'p_subinventory_name = ' || p_subinventory_name || ', ' ||
67 'p_locator_id = ' || p_locator_id || ', ' ||
68 'p_source_organization_id = ' || p_source_organization_id || ', ' ||
69 'p_source_subinventory = ' || p_source_subinventory || ', ' ||
70 'p_source_locator_id = ' || p_source_locator_id);
71 END IF;
72
73 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
74 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
75 l_log_module || '.query_quantities',
76 'Calling INV_QUANTITY_TREE_PUB.query_quantities');
77 END IF;
78
79 --*****************************************************************************************************
80 -- Calling the API inv_quantity_tree_pub.query_quantities to fetch the Available to Transact Quantity
81 --*****************************************************************************************************
82 INV_QUANTITY_TREE_PUB.CLEAR_QUANTITY_CACHE;
83
84 INV_QUANTITY_TREE_PUB.query_quantities
85 ( p_api_version_number => 1.0 ,
86 p_init_msg_lst => FND_API.g_false,
87 x_return_status => x_retcode,
88 x_msg_count => l_msg_count,
89 x_msg_data => x_errmsg,
90 p_organization_id => p_source_organization_id,
91 p_inventory_item_id => p_inventory_item_id,
92 p_tree_mode => INV_QUANTITY_TREE_PUB.g_transaction_mode,
93 p_is_revision_control => FALSE,
94 p_is_lot_control => FALSE,
95 p_is_serial_control => FALSE,
96 p_revision => NULL,
97 p_lot_number => NULL,
98 p_subinventory_code => p_source_subinventory,
99 p_locator_id => p_source_locator_id,
100 p_onhand_source => 3, -- For all Sub-Inventories
101 p_transfer_subinventory_code => p_subinventory_name,
102 p_transfer_locator_id => p_locator_id,
103 x_qoh => l_qoh,
104 x_rqoh => l_rqoh,
105 x_qr => l_qr,
106 x_qs => l_qs,
107 x_att => l_att,
108 x_atr => l_atr
109 );
110
111 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
112 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
113 l_log_module || '.query_quantities',
114 'After INV_QUANTITY_TREE_PUB.query_quantities: ' ||
115 'x_att = ' || l_att || ', ' ||
116 'x_return_status = ' || x_retcode || ', ' ||
117 'x_msg_count = ' || l_msg_count || ', ' ||
118 'x_msg_data = ' || x_errmsg);
119 END IF;
120
121 IF x_retcode = FND_API.G_RET_STS_SUCCESS THEN
122
123 x_available_to_transact := l_att;
124
125 ELSIF l_msg_count > 1 THEN
126
127 FOR i IN 1..l_msg_count LOOP
128 x_errmsg := x_errmsg || fnd_msg_pub.get(i, FND_API.G_FALSE) || FND_CONST.NEWLINE;
129 END LOOP;
130
131 END IF;
132
133
134 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
135 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
136 l_log_module || '.end',
137 'x_available_to_transact = ' || x_available_to_transact || ', ' ||
138 'x_retcode = ' || x_retcode || ', ' ||
139 'x_errmsg = ' || x_errmsg);
140 END IF;
141
142 END get_available_to_transact;
143
144
145 /**************************************************************************
146 *
147 * PROCEDURE
148 * COMPLETE_INTER_TRANSFER
149 *
150 * DESCRIPTION
151 * This procedure completes the Kanban Inventory Transfer Inter Org Process.
152 *
153 * PARAMETERS
154 * ==========
155 * NAME TYPE DESCRIPTION
156 * ----------------- -------- ---------------------------------------------
157 * p_inter_order_id IN This parameter passes the Internal Sales Order Id
158 * p_inter_line_id IN This Parameter passes the Internal Order Line ID.
159 * p_transfer_qty IN This Parameter passes the Transfer Quantity.
160 * p_kanban_size IN This Parameter passes the Kanban Size.
161 * p_header_id IN This Parameter passes the header id from OE_ORDER_LINES_ALL
162 * p_line_id IN This Parameters passes the line id from OE_ORDER_LINES_ALL
163 * x_errmsg OUT Returns the error message if any.
164 * x_retcode OUT Returns the error code.
165 * Values are: 0 - Normal Completion
166 * 1 - Warning
167 * 2 - Error
168 *
169 *PREREQUISITES
170 * None
171 *
172 * CALLED BY
173 * complete_process
174 *
175 *************************************************************************/
176 PROCEDURE complete_inter_transfer
177 ( p_kanban_card_id IN NUMBER,
178 p_organization_id IN NUMBER,
179 p_inter_order_id IN NUMBER,
180 p_inter_line_id IN NUMBER,
181 p_transfer_qty IN NUMBER,
182 p_kanban_size IN NUMBER,
183 p_header_id IN NUMBER,
184 p_line_id IN NUMBER,
185 x_retcode OUT NOCOPY VARCHAR2,
186 x_errmsg OUT NOCOPY VARCHAR2
187 )
188 IS
189
190 l_api_name CONSTANT VARCHAR2(30) := 'complete_inter_transfer';
191 l_log_module CONSTANT VARCHAR2(80) := G_LOG_MODULE || l_api_name;
192
193
194 CURSOR c_delivery_dtl_ids(p_header_id IN NUMBER,
195 p_line_id IN NUMBER)
196 IS
197 SELECT wdd.delivery_detail_id
198 FROM wsh_delivery_details wdd,
199 wsh_delivery_assignments wda
200 WHERE wdd.source_header_id = p_header_id
201 AND wdd.source_line_id = p_line_id
202 AND wda.delivery_detail_id = wdd.delivery_detail_id
203 AND wda.delivery_id IS NULL;
204
205 CURSOR c_delivery_dtl(p_header_id IN NUMBER,
206 p_line_id IN NUMBER
207 )
208 IS
209 SELECT wdd.delivery_detail_id,
210 wdd.released_status,
211 wdd.organization_id,
212 wdd.original_subinventory,
213 wda.delivery_id,
214 wnd.name delivery_name,
215 wnd.ship_method_code
216 FROM wsh_delivery_details wdd,
217 wsh_delivery_assignments wda,
218 wsh_new_deliveries wnd
219 WHERE wdd.source_header_id = p_header_id
220 AND wdd.source_line_id = p_line_id
221 AND wdd.released_status IN ('B', 'R', 'Y')
222 AND wda.delivery_detail_id = wdd.delivery_detail_id
223 AND wda.delivery_id = wnd.delivery_id;
224
225 CURSOR c_move_order_detail(p_delivery_detail_id IN NUMBER)
226 IS
227 SELECT mtrh.header_id,
228 mtrh.move_order_type,
229 mtrl.line_id
230 FROM mtl_txn_request_headers mtrh,
231 mtl_txn_request_lines mtrl,
232 wsh_delivery_details wdd
233 WHERE mtrh.header_id = mtrl.header_id
234 AND mtrl.line_id = wdd.move_order_line_id
235 AND wdd.delivery_detail_id = p_delivery_detail_id;
236
237 l_delivery_detail_rec c_delivery_dtl%ROWTYPE;
238
239 l_tolerance_param NUMBER;
240 l_ship_tolerance_above NUMBER;
241 l_msg_count NUMBER;
242 l_delivery_ids_tbl WSH_UTIL_CORE.Id_Tab_Type;
243 l_del_rows_tbl WSH_UTIL_CORE.Id_Tab_Type;
244 l_batch_info_rec WSH_PICKING_BATCHES_PUB.Batch_Info_rec;
245 l_source_staging_subinv NUMBER;
246 l_batch_id NUMBER;
247 l_request_id NUMBER;
248 l_move_order_header_id mtl_txn_request_headers.header_id%TYPE;
249 l_move_order_type mtl_txn_request_headers.move_order_type%TYPE;
250 l_move_order_line_id mtl_txn_request_lines.line_id%TYPE;
251 l_trolin_tbl INV_MOVE_ORDER_PUB.Trolin_Tbl_Type;
252 l_trolin_old_tbl INV_MOVE_ORDER_PUB.Trolin_Tbl_Type;
253 l_trolin_out_tbl INV_MOVE_ORDER_PUB.Trolin_Tbl_Type;
254 l_mmtt_tbl INV_MO_LINE_DETAIL_UTIL.G_MMTT_Tbl_Type;
255 l_trip_id wsh_trips.trip_id%TYPE;
256 l_trip_name wsh_trips.name%TYPE;
257 l_cnt NUMBER:=0;
258
259 BEGIN
260
261 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
262 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
263 l_log_module || '.begin',
264 'Parameters: ' ||
265 'p_kanban_card_id = ' || p_kanban_card_id || ', ' ||
266 'p_organization_id = ' || p_organization_id || ', ' ||
267 'p_inter_order_id = ' || p_inter_order_id || ', ' ||
268 'p_inter_line_id = ' || p_inter_line_id || ', ' ||
269 'p_transfer_qty = ' || p_transfer_qty || ', ' ||
270 'p_kanban_size = ' || p_kanban_size);
271 END IF;
272
273 -- Initialize return status to success
274 x_retcode := FND_API.G_RET_STS_SUCCESS;
275
276
277 l_tolerance_param := flm_kanban_config_params.get_tol_kanban_transfer(p_organization_id);
278
279 IF l_tolerance_param = 1 AND p_transfer_qty > p_kanban_size THEN
280
281 -- ********************************************************************************************************************
282 -- Calculating the ln_ship_tolerance_above value to be updated in OE_ORDER_LINES_ALL and WSH_DELIVERY_DETAILS Table
283 -- ********************************************************************************************************************
284
285 l_ship_tolerance_above := (((p_transfer_qty - p_kanban_size) * 100) / p_kanban_size) + 1;
286
287 -- ******************************************************************
288 -- Populating the Order Line information in Line Table Record Type
289 -- ******************************************************************
290
291 UPDATE oe_order_lines_all
292 SET ship_tolerance_above = ROUND(l_ship_tolerance_above)
293 WHERE header_id = p_header_id
294 AND line_id=p_line_id;
295
296 UPDATE wsh_delivery_details wdd1
297 SET wdd1.ship_tolerance_above = ROUND(l_ship_tolerance_above)
298 WHERE wdd1.delivery_detail_id IN
299 (SELECT delivery_detail_id
300 FROM wsh_delivery_details wdd2,
301 oe_order_lines_all oel
302 WHERE wdd2.source_header_id = oel.header_id
303 AND wdd2.source_line_id = oel.line_id
304 AND oel.source_document_id = p_inter_order_id
305 AND oel.source_document_line_id = p_inter_line_id
306 AND oel.header_id = p_header_id
307 AND oel.line_id = p_line_id
308 );
309
310 END IF;
311 OPEN c_delivery_dtl_ids(p_header_id,p_line_id);
312 FETCH c_delivery_dtl_ids BULK COLLECT INTO l_delivery_ids_tbl;
313 CLOSE c_delivery_dtl_ids;
314
315
316 IF l_delivery_ids_tbl.COUNT > 0 THEN
317
318 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
319 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
320 l_log_module || '.autocreate_deliveries',
321 'Calling WSH_DELIVERY_DETAILS_PUB.autocreate_deliveries');
322 END IF;
323
324 --**************************************************************************************************************
325 -- The Autocreate_Deliveries procedure is called to automatically create deliveries for multiple delivery lines.
326 --**************************************************************************************************************
327 WSH_DELIVERY_DETAILS_PUB.autocreate_deliveries
328 ( p_api_version_number => 1.0,
329 p_init_msg_list => FND_API.G_FALSE,
330 p_commit => FND_API.G_FALSE,
331 x_return_status => x_retcode,
332 x_msg_count => l_msg_count,
333 x_msg_data => x_errmsg,
334 p_line_rows => l_delivery_ids_tbl,
335 x_del_rows => l_del_rows_tbl
336 );
337
338
339 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
340 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
341 l_log_module || '.autocreate_deliveries',
342 'After WSH_DELIVERY_DETAILS_PUB.autocreate_deliveries: ' ||
343 'x_return_status = ' || x_retcode || ', ' ||
344 'x_msg_count = ' || l_msg_count || ', ' ||
345 'x_msg_data = ' || x_errmsg);
346
347 END IF;
348
349 IF x_retcode <> FND_API.G_RET_STS_SUCCESS THEN
350 x_retcode := FND_API.G_RET_STS_ERROR;
351
352 IF l_msg_count > 1 THEN
353 FOR i IN 1..l_msg_count LOOP
354 x_errmsg := x_errmsg || fnd_msg_pub.get(i, FND_API.G_FALSE) || FND_CONST.NEWLINE;
355 END LOOP;
356 END IF;
357
358 RETURN;
359 END IF;
360
361 END IF;
362 --************************************************************************
363 -- Opening the delivery details cursor to fetch the delivery ID.
364 --************************************************************************
365 OPEN c_delivery_dtl(p_header_id,p_line_id);
366 LOOP
367 FETCH c_delivery_dtl into l_delivery_detail_rec;
368 EXIT WHEN c_delivery_dtl%NOTFOUND;
369
370 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
371 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
372 l_log_module || '.delivery_detail',
373 'delivery_detail_id = ' || l_delivery_detail_rec.delivery_detail_id || ', ' ||
374 'delivery_id = ' || l_delivery_detail_rec.delivery_id);
375 END IF;
376
377 IF l_delivery_detail_rec.released_status <> 'Y' THEN
378
379 --****************************************************************************************************************
380 -- Calling the Create Batch API by passing the delivery ID which was created using the autocreate_deliveries API
381 -- This API creates a new pick release batch with a unique batch ID that is then used
382 -- by the Release_Batch API to release the batch.
383 --****************************************************************************************************************
384
385 --***********************************************************************************************************************
386 -- Use Source Subinventory as Staging Subinventory for Kanban transfers
387 -- Setting the Default_Stage_Subinventory and Pick_From_Subinventory to original_subinventory. This will ensure that
388 -- the Pick Release process will consider original_subinventory( Source Subinventory) as default staging subinventory.
389 --***********************************************************************************************************************
390
391 l_batch_info_rec.delivery_id := l_delivery_detail_rec.delivery_id;
392 l_batch_info_rec.auto_pick_confirm_flag := 'N';
393
394 l_source_staging_subinv := flm_kanban_config_params.get_source_staging_subinv(l_delivery_detail_rec.organization_id);
395
396 IF l_source_staging_subinv = 1 THEN
397 l_batch_info_rec.organization_id := l_delivery_detail_rec.organization_id;
398 l_batch_info_rec.default_stage_subinventory := l_delivery_detail_rec.original_subinventory;
399 l_batch_info_rec.pick_from_subinventory := l_delivery_detail_rec.original_subinventory;
400 END IF;
401
402 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
403 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
404 l_log_module || '.create_batch',
405 'Calling WSH_PICKING_BATCHES_PUB.create_batch');
406 END IF;
407
408 WSH_PICKING_BATCHES_PUB.create_batch
409 ( p_api_version => 1.0,
410 p_init_msg_list => FND_API.G_FALSE,
411 x_return_status => x_retcode,
412 x_msg_count => l_msg_count,
413 x_msg_data => x_errmsg,
414 p_batch_rec => l_batch_info_rec,
415 x_batch_id => l_batch_id
416 );
417
418 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
419 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
420 l_log_module || '.create_batch',
421 'After WSH_PICKING_BATCHES_PUB.create_batch: ' ||
422 'x_return_status = ' || x_retcode || ', ' ||
423 'x_msg_count = ' || l_msg_count || ', ' ||
424 'x_msg_data = ' || x_errmsg || ', ' ||
425 'x_batch_id = ' || l_batch_id);
426 END IF;
427
428 IF x_retcode <> FND_API.G_RET_STS_SUCCESS THEN
429 x_retcode := FND_API.G_RET_STS_ERROR;
430
431 IF l_msg_count > 1 THEN
432 FOR i IN 1..l_msg_count LOOP
433 x_errmsg := x_errmsg || fnd_msg_pub.get(i, FND_API.G_FALSE) || FND_CONST.NEWLINE;
434 END LOOP;
435 END IF;
436
437 RETURN;
438 END IF;
439
440
441 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
442 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
443 l_log_module || '.release_batch',
444 'Calling WSH_PICKING_BATCHES_PUB.release_batch');
445 END IF;
446
447 -- ***********************************************************************************
448 -- This API enables you to release a pick release batch that was generated by the
449 -- Create_Batch API.
450 -- ***********************************************************************************
451 WSH_PICKING_BATCHES_PUB.release_batch
452 ( p_api_version => 1.0,
453 p_init_msg_list => FND_API.G_FALSE,
454 x_return_status => x_retcode,
455 x_msg_count => l_msg_count,
456 x_msg_data => x_errmsg,
457 p_batch_id => l_batch_id,
458 p_release_mode => 'ONLINE',
459 x_request_id => l_request_id
460 );
461
462 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
463 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
464 l_log_module || '.release_batch',
465 'After WSH_PICKING_BATCHES_PUB.release_batch: ' ||
466 'x_return_status = ' || x_retcode || ', ' ||
467 'x_msg_count = ' || l_msg_count || ', ' ||
468 'x_msg_data = ' || x_errmsg || ', ' ||
469 'x_request_id = ' || l_request_id);
470 END IF;
471
472 IF x_retcode <> FND_API.G_RET_STS_SUCCESS THEN
473 x_retcode := FND_API.G_RET_STS_ERROR;
474 IF l_msg_count > 1 THEN
475 FOR i IN 1..l_msg_count LOOP
476 x_errmsg := x_errmsg || fnd_msg_pub.get(i, FND_API.G_FALSE) || FND_CONST.NEWLINE;
477 END LOOP;
478 END IF;
479
480 RETURN;
481 END IF;
482
483 --***************************************************************************************************************
484 -- The Move order which is created and released using the above APIs , will now be in allocated status
485 -- and can be transacted. The Transaction of Move Order will be done based on the Transfer Quantity passed by the user.
486 -- Step 1. Update the QUANTITY_DETAILED = transfer quantity in MTL_TXN_REQUEST_LINES_TABLE using Process_Move_Order_Line.
487 -- Step 2. Transact the Move Order by using pick_confirm API ( By Updating primary_quantity and transaction_quantity
488 -- with transfer_quantity in mtl_material_transactions_temp )
489 --***************************************************************************************************************
490
491 -- *************************************************************************************************
492 -- Calling the Process_Move_Order_Line API to update the quantity_detailed in Move Order Line Table
493 -- *************************************************************************************************
494
495 OPEN c_move_order_detail(l_delivery_detail_rec.delivery_detail_id);
496 FETCH c_move_order_detail INTO l_move_order_header_id, l_move_order_type, l_move_order_line_id;
497 CLOSE c_move_order_detail;
498
499 l_trolin_tbl(1).line_id := l_move_order_line_id;
500 l_trolin_tbl(1).operation := INV_GLOBALS.G_OPR_UPDATE;
501 l_trolin_tbl(1).header_id := l_move_order_header_id;
502 l_trolin_tbl(1).quantity_detailed := p_transfer_qty;
503
504 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
505 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
506 l_log_module || '.process_move_order_line',
507 'Calling INV_MOVE_ORDER_PUB.process_move_order_line');
508 END IF;
509
510 INV_MOVE_ORDER_PUB.process_move_order_line
511 ( p_api_version_number => 1.0,
512 p_init_msg_list => FND_API.G_FALSE,
513 p_return_values => FND_API.G_FALSE,
514 p_commit => FND_API.G_FALSE,
515 x_return_status => x_retcode,
516 x_msg_count => l_msg_count,
517 x_msg_data => x_errmsg,
518 p_trolin_tbl => l_trolin_tbl,
519 p_trolin_old_tbl => l_trolin_old_tbl,
520 x_trolin_tbl => l_trolin_out_tbl
521 );
522
523 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
524 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
525 l_log_module || '.process_move_order_line',
526 'After INV_MOVE_ORDER_PUB.process_move_order_line: ' ||
527 'x_return_status = ' || x_retcode || ', ' ||
528 'x_msg_count = ' || l_msg_count || ', ' ||
529 'x_msg_data = ' || x_errmsg);
530 END IF;
531
532 IF x_retcode <> FND_API.G_RET_STS_SUCCESS THEN
533 x_retcode := FND_API.G_RET_STS_ERROR;
534
535 IF l_msg_count > 1 THEN
536 FOR i IN 1..l_msg_count LOOP
537 x_errmsg := x_errmsg || fnd_msg_pub.get(i, FND_API.G_FALSE) || FND_CONST.NEWLINE;
538 END LOOP;
539 END IF;
540
541 RETURN;
542 END IF;
543
544 -- **********************************************************************************
545 -- Calling the Pick Confirm API to transact the Move Order.
546 -- **********************************************************************************
547
548 l_mmtt_tbl := inv_mo_line_detail_util.query_rows(l_move_order_line_id);
549 l_mmtt_tbl(1).transaction_quantity := p_transfer_qty;
550 l_mmtt_tbl(1).primary_quantity := p_transfer_qty;
551
552 l_trolin_tbl.DELETE;
553
554 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
555 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
556 l_log_module || '.pick_confirm',
557 'Calling INV_PICK_WAVE_PICK_CONFIRM_PUB.pick_confirm');
558 END IF;
559
560 INV_PICK_WAVE_PICK_CONFIRM_PUB.pick_confirm
561 ( p_api_version_number => 1.0,
562 p_init_msg_list => FND_API.G_FALSE,
563 p_commit => FND_API.G_FALSE,
564 x_return_status => x_retcode,
565 x_msg_count => l_msg_count,
566 x_msg_data => x_errmsg,
567 p_move_order_type => l_move_order_type,
568 p_transaction_mode => 1,
569 p_trolin_tbl => l_trolin_tbl,
570 p_mold_tbl => l_mmtt_tbl,
571 x_mmtt_tbl => l_mmtt_tbl,
572 x_trolin_tbl => l_trolin_tbl,
573 p_transaction_date => SYSDATE
574 );
575
576 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
577 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
578 l_log_module || '.pick_confirm',
579 'After INV_PICK_WAVE_PICK_CONFIRM_PUB.pick_confirm: ' ||
580 'x_return_status = ' || x_retcode || ', ' ||
581 'x_msg_count = ' || l_msg_count || ', ' ||
582 'x_msg_data = ' || x_errmsg);
583 END IF;
584
585 IF x_retcode <> FND_API.G_RET_STS_SUCCESS THEN
586 x_retcode := FND_API.G_RET_STS_ERROR;
587
588 IF l_msg_count > 1 THEN
589 FOR i IN 1..l_msg_count LOOP
590 x_errmsg := x_errmsg || fnd_msg_pub.get(i, FND_API.G_FALSE) || FND_CONST.NEWLINE;
591 END LOOP;
592 END IF;
593 RETURN;
594 END IF;
595
596 END IF; -- l_delivery_detail_rec.released_status
597
598
599 -- ***************************************************************************************
600 -- Calling the WSH_DELIVERIES_PUB.delivery_action to ship confirm the delivery.
601 -- ****************************************************************************************
602
603 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
604 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
605 l_log_module || '.delivery_action',
606 'Calling WSH_DELIVERIES_PUB.delivery_action');
607 END IF;
608
609 WSH_DELIVERIES_PUB.delivery_action
610 ( p_api_version_number => 1.0,
611 p_init_msg_list => FND_API.G_FALSE,
612 x_return_status => x_retcode,
613 x_msg_count => l_msg_count,
614 x_msg_data => x_errmsg,
615 p_action_code => 'CONFIRM',
616 p_delivery_id => l_delivery_detail_rec.delivery_id,
617 p_delivery_name => l_delivery_detail_rec.delivery_name,
618 p_sc_action_flag => 'S',
619 p_sc_intransit_flag => 'Y',
620 p_sc_close_trip_flag => 'Y',
621 p_sc_create_bol_flag => 'N',
622 p_sc_trip_ship_method => l_delivery_detail_rec.ship_method_code,
623 p_sc_actual_dep_date => SYSDATE,
624 p_sc_defer_interface_flag => 'Y',
625 x_trip_id => l_trip_id,
626 x_trip_name => l_trip_name
627 );
628
629 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
630 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
631 l_log_module || '.delivery_action',
632 'After WSH_DELIVERIES_PUB.delivery_action: ' ||
633 'x_return_status = ' || x_retcode || ', ' ||
634 'x_msg_count = ' || l_msg_count || ', ' ||
635 'x_msg_data = ' || x_errmsg || ', ' ||
636 'x_trip_id = ' || l_trip_id || ', ' ||
637 'x_trip_name = ' || l_trip_name);
638 END IF;
639
640 IF x_retcode <> FND_API.G_RET_STS_SUCCESS THEN
641 x_retcode := FND_API.G_RET_STS_ERROR;
642 IF l_msg_count > 1 THEN
643 FOR i IN 1..l_msg_count LOOP
644 x_errmsg := x_errmsg || fnd_msg_pub.get(i, FND_API.G_FALSE) || FND_CONST.NEWLINE;
645 END LOOP;
646 END IF;
647
648 RETURN;
649 END IF;
650
651
652 -- ***************************************************************************************
653 -- Calling the Interface Trip Stops Program.
654 -- ***************************************************************************************
655
656 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
657 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
658 l_log_module || '.interface_all',
659 'Calling WSH_SHIP_CONFIRM_ACTIONS.interface_all');
660 END IF;
661
662 WSH_SHIP_CONFIRM_ACTIONS.interface_all
663 ( errbuf => x_errmsg,
664 retcode => x_retcode,
665 p_mode => 'ALL',
666 p_stop_id => NULL,
667 p_delivery_id => l_delivery_detail_rec.delivery_id,
668 p_log_level => 0,
669 p_batch_id => NULL,
670 p_trip_type => NULL,
671 p_organization_id => NULL,
672 p_stops_per_batch => 1
673 );
674
675 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
676 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
677 l_log_module || '.interface_all',
678 'After WSH_SHIP_CONFIRM_ACTIONS.interface_all: ' ||
679 'retcode = ' || x_retcode || ', ' ||
680 'errbuf = ' || x_errmsg);
681 END IF;
682
683 IF x_retcode <> '0' THEN
684 x_retcode := FND_API.G_RET_STS_ERROR;
685
686 fnd_message.set_name('FLM','FLM_ITS_PROGRAM_FAILURE');
687 x_errmsg := fnd_message.get;
688 RETURN;
689 END IF;
690
691 END LOOP;
692
693
694
695 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
696 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
697 l_log_module || '.interorg_transfer_hook',
698 'Calling FLM_KANBAN_CUSTOM_PKG.interorg_transfer_hook');
699 END IF;
700
701 FLM_KANBAN_CUSTOM_PKG.interorg_transfer_hook
702 ( p_kanban_card_id => p_kanban_card_id,
703 p_inter_order_id => p_inter_order_id,
704 p_inter_line_id => p_inter_line_id,
705 p_transfer_qty => p_transfer_qty,
706 p_kanban_size => p_kanban_size,
707 x_retcode => x_retcode,
708 x_errmsg => x_errmsg
709 );
710
711
712 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
713 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
714 l_log_module || '.end',
715 'x_retcode = ' || x_retcode || ', ' ||
716 'x_errmsg = ' || x_errmsg);
717 END IF;
718
719 END complete_inter_transfer;
720
721
722 /**************************************************************************
723 *
724 * PROCEDURE
725 * COMPLETE_INTRA_TRANSFER
726 *
727 * DESCRIPTION
728 * This procedure is used to complete the KanBan Intra Org transfer
729 *
730 * PARAMETERS
731 * ==========
732 * NAME TYPE DESCRIPTION
733 * ----------------- -------- ---------------------------------------------
734 * p_intra_order_id IN This parameter passes the Move Order number
735 * p_intra_line_id IN This parameter passes the Mover order line number.
736 * p_transfer_qty IN This parameter passes the transfer qty.
737 * p_kanban_size IN This Parameter passes the Kanban Size.
738 * x_retcode OUT Returns the error code.
739 * Values are: 0 - Normal Completion
740 * 1 - Warning
741 * 2 - Error
742 * x_errmsg OUT Returns the error message if any.
743 *
744 *PREREQUISITES
745 * None
746 *
747 * CALLED BY
748 * complete_process
749 *
750 *************************************************************************/
751 PROCEDURE complete_intra_transfer
752 ( p_kanban_card_id IN NUMBER,
753 p_intra_order_id IN NUMBER,
754 p_intra_line_id IN NUMBER,
755 p_transfer_qty IN NUMBER,
756 p_kanban_size IN NUMBER,
757 x_retcode OUT NOCOPY VARCHAR2,
758 x_errmsg OUT NOCOPY VARCHAR2
759 )
760 IS
761
762 l_api_name CONSTANT VARCHAR2(30) := 'complete_intra_transfer';
763 l_log_module CONSTANT VARCHAR2(80) := G_LOG_MODULE || l_api_name;
764
765 -- ***************************************************************************
766 -- Cursor to get the Move Order Details, by passing the Move Order Line ID
767 -- ***************************************************************************
768 CURSOR c_move_order(p_header_id IN NUMBER)
769 IS
770 SELECT request_number,
771 move_order_type
772 FROM mtl_txn_request_headers
773 WHERE header_id = p_header_id;
774
775 CURSOR c_line_status(p_line_id IN NUMBER)
776 IS
777 SELECT line_status
778 FROM mtl_txn_request_lines
779 WHERE line_id = p_line_id;
780
781 CURSOR c_open_lines(p_header_id IN NUMBER)
782 IS
783 SELECT line_id
784 FROM mtl_txn_request_lines
785 WHERE header_id = p_header_id
786 AND line_status not in (5, 6);
787
788 l_request_number mtl_txn_request_headers.request_number%TYPE;
789 l_move_order_type mtl_txn_request_headers.move_order_type%TYPE;
790 l_open_lines c_open_lines%ROWTYPE;
791
792 l_number_of_rows NUMBER;
793 l_detailed_qty NUMBER;
794 l_msg_count NUMBER;
795 l_revision VARCHAR2(3);
796 l_locator_id NUMBER;
797 l_transfer_to_location NUMBER;
798 l_lot_number VARCHAR2(80);
799 l_expiration_date DATE;
800 l_transaction_temp_id NUMBER;
801
802 l_trolin_tbl INV_MOVE_ORDER_PUB.Trolin_Tbl_Type;
803 l_mmtt_tbl INV_MO_LINE_DETAIL_UTIL.G_MMTT_Tbl_Type;
804
805 BEGIN
806
807 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
808 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
809 l_log_module || '.begin',
810 'Parameters: ' ||
811 'p_kanban_card_id = ' || p_kanban_card_id || ', ' ||
812 'p_intra_order_id = ' || p_intra_order_id || ', ' ||
813 'p_intra_line_id = ' || p_intra_line_id || ', ' ||
814 'p_transfer_qty = ' || p_transfer_qty || ', ' ||
815 'p_kanban_size = ' || p_kanban_size);
816 END IF;
817
818 -- Initialize return status to success
819 x_retcode := FND_API.G_RET_STS_SUCCESS;
820
821
822 UPDATE mtl_txn_request_lines
823 SET quantity_detailed = NULL
824 WHERE line_id = p_intra_line_id;
825
826 -- *******************************************************
827 -- Opening the Cursor to fetch the Move Order Details
828 -- *******************************************************
829 OPEN c_move_order(p_intra_order_id);
830 FETCH c_move_order INTO l_request_number, l_move_order_type;
831 CLOSE c_move_order;
832
833
834 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
835 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
836 l_log_module || '.line_details_pub',
837 'Calling INV_REPLENISH_DETAIL_PUB.line_details_pub');
838 END IF;
839
840 -- ****************************************************************************************
841 -- Calling the inv_replenish_detail_pub.line_details_pub API , to replenish the Move Order
842 -- ****************************************************************************************
843 INV_REPLENISH_DETAIL_PUB.line_details_pub
844 ( p_line_id => p_intra_line_id, -- mtl_txn_request_lines.line_id
845 x_number_of_rows => l_number_of_rows,
846 x_detailed_qty => l_detailed_qty,
847 x_return_status => x_retcode,
848 x_msg_count => l_msg_count,
849 x_msg_data => x_errmsg,
850 x_revision => l_revision,
851 x_locator_id => l_locator_id,
852 x_transfer_to_location => l_transfer_to_location,
853 x_lot_number => l_lot_number,
854 x_expiration_date => l_expiration_date,
855 x_transaction_temp_id => l_transaction_temp_id,
856 p_transaction_header_id => p_intra_order_id, -- mtl_txn_request_lines.header_id
857 p_transaction_mode => 1, -- INSERT
858 p_move_order_type => l_move_order_type,
859 p_serial_flag => NULL,
860 p_plan_tasks => FALSE,
861 p_auto_pick_confirm => FALSE,
862 p_commit => FALSE
863 );
864
865 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
866 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
867 l_log_module || '.line_details_pub',
868 'After INV_REPLENISH_DETAIL_PUB.line_details_pub: ' ||
869 'x_return_status = ' || x_retcode || ', ' ||
870 'x_msg_count = ' || l_msg_count || ', ' ||
871 'x_msg_data = ' || x_errmsg);
872 END IF;
873
874 IF x_retcode <> FND_API.G_RET_STS_SUCCESS THEN
875 x_retcode := FND_API.G_RET_STS_ERROR;
876
877 IF l_msg_count > 1 THEN
878 FOR i IN 1..l_msg_count LOOP
879 x_errmsg := x_errmsg || fnd_msg_pub.get(i, FND_API.G_FALSE) || FND_CONST.NEWLINE;
880 END LOOP;
881 END IF;
882
883 fnd_message.set_name('FLM','FLM_REPLENISH_ERR');
884 fnd_message.set_token('ORDER_NUMBER', l_request_number);
885 x_errmsg := x_errmsg || fnd_message.get;
886
887 RETURN;
888 END IF;
889
890
891 -- ****************************************************************************************
892 -- Calling the Pick_Confirm API to pick confirm the Move Order
893 -- ****************************************************************************************
894
895 l_mmtt_tbl := inv_mo_line_detail_util.query_rows(p_intra_line_id);
896 l_mmtt_tbl(1).transaction_quantity := p_transfer_qty;
897 l_mmtt_tbl(1).primary_quantity := p_transfer_qty;
898
899 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
900 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
901 l_log_module || '.pick_confirm',
902 'Calling INV_PICK_WAVE_PICK_CONFIRM_PUB.Pick_Confirm');
903 END IF;
904
905 INV_PICK_WAVE_PICK_CONFIRM_PUB.Pick_Confirm
906 ( p_api_version_number => 1.0,
907 p_init_msg_list => FND_API.G_FALSE,
908 p_commit => FND_API.G_FALSE,
909 x_return_status => x_retcode,
910 x_msg_count => l_msg_count,
911 x_msg_data => x_errmsg,
912 p_move_order_type => l_move_order_type,
913 p_transaction_mode => 1,
914 p_trolin_tbl => l_trolin_tbl,
915 p_mold_tbl => l_mmtt_tbl,
916 x_mmtt_tbl => l_mmtt_tbl,
917 x_trolin_tbl => l_trolin_tbl,
918 p_transaction_date => SYSDATE
919 );
920
921 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
922 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
923 l_log_module || '.pick_confirm',
924 'After INV_PICK_WAVE_PICK_CONFIRM_PUB.Pick_Confirm: ' ||
925 'x_return_status = ' || x_retcode || ', ' ||
926 'x_msg_count = ' || l_msg_count || ', ' ||
927 'x_msg_data = ' || x_errmsg);
928 END IF;
929
930 IF x_retcode <> FND_API.G_RET_STS_SUCCESS THEN
931 x_retcode := FND_API.G_RET_STS_ERROR;
932
933 IF l_msg_count > 1 THEN
934 FOR i IN 1..l_msg_count LOOP
935 x_errmsg := x_errmsg || fnd_msg_pub.get(i, FND_API.G_FALSE) || FND_CONST.NEWLINE;
936 END LOOP;
937 END IF;
938
939 fnd_message.set_name('FLM','FLM_PICK_RELEASE_ERR');
940 fnd_message.set_token('ORDER_NUMBER', l_request_number);
941 x_errmsg := x_errmsg || fnd_message.get;
942
943 RETURN;
944 END IF;
945
946
947 -- ****************************************************************************************
948 -- Updating the Quantity_Detailed Column in Move Order Lines Table with the value present
949 -- in Quantity_Delivered Column as per the standard functionality.
950 -- ****************************************************************************************
951
952 UPDATE mtl_txn_request_lines
953 SET quantity_detailed = quantity_delivered
954 WHERE line_id = p_intra_line_id;
955
956
957 -- ****************************************************************************************
958 -- Calling the inv_mo_admin_pub.close_order to close the Move Order
959 -- if all move order lines have been closed
960 -- ****************************************************************************************
961
962 OPEN c_open_lines(p_intra_order_id);
963 FETCH c_open_lines INTO l_open_lines;
964 CLOSE c_open_lines;
965
966 IF l_open_lines.line_id IS NULL THEN
967
968 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
969 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
970 l_log_module || '.close_order',
971 'Calling INV_MO_ADMIN_PUB.close_order');
972 END IF;
973
974 INV_MO_ADMIN_PUB.close_order
975 ( p_api_version => 1.0,
976 p_init_msg_list => FND_API.G_FALSE,
977 p_commit => FND_API.G_FALSE,
978 p_validation_level => 'F',
979 p_header_id => p_intra_order_id,
980 x_msg_count => l_msg_count,
981 x_msg_data => x_errmsg,
982 x_return_status => x_retcode
983 );
984
985 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
986 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
987 l_log_module || '.close_order',
988 'After INV_MO_ADMIN_PUB.close_order: ' ||
989 'x_return_status = ' || x_retcode || ', ' ||
990 'x_msg_count = ' || l_msg_count || ', ' ||
991 'x_msg_data = ' || x_errmsg);
992 END IF;
993
994 IF x_retcode <> FND_API.G_RET_STS_SUCCESS THEN
995 x_retcode := FND_API.G_RET_STS_ERROR;
996
997 IF l_msg_count > 1 THEN
998 FOR i IN 1..l_msg_count LOOP
999 x_errmsg := x_errmsg || fnd_msg_pub.get(i, FND_API.G_FALSE) || FND_CONST.NEWLINE;
1000 END LOOP;
1001 END IF;
1002
1003 fnd_message.set_name('FLM','FLM_MOVE_ORDER_CLOSE_ERR');
1004 fnd_message.set_token('ORDER_NUMBER', l_request_number);
1005 x_errmsg := x_errmsg || fnd_message.get;
1006
1007 RETURN;
1008 END IF;
1009
1010 END IF; -- l_open_lines
1011
1012
1013 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1014 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1015 l_log_module || '.intraorg_transfer_hook',
1016 'Calling FLM_KANBAN_CUSTOM_PKG.intraorg_transfer_hook');
1017 END IF;
1018
1019 FLM_KANBAN_CUSTOM_PKG.intraorg_transfer_hook
1020 ( p_kanban_card_id => p_kanban_card_id,
1021 p_intra_order_id => p_intra_order_id,
1022 p_intra_line_id => p_intra_line_id,
1023 p_transfer_qty => p_transfer_qty,
1024 p_kanban_size => p_kanban_size,
1025 x_retcode => x_retcode,
1026 x_errmsg => x_errmsg
1027 );
1028
1029
1030 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1031 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1032 l_log_module || '.end',
1033 'x_retcode = ' || x_retcode || ', ' ||
1034 'x_errmsg = ' || x_errmsg);
1035 END IF;
1036
1037 END complete_intra_transfer;
1038
1039
1040 /************************************************************************************************
1041 *
1042 * PROCEDURE
1043 * VALIDATE_DATA
1044 *
1045 * DESCRIPTION
1046 * This procedure validate the input PARAMETERS and select the necessary info
1047 * required for further validations and processing.
1048 *
1049 * PARAMETERS
1050 * ==========
1051 * NAME TYPE DESCRIPTION
1052 * ----------------- -------- ---------------------------------------------
1053 * p_kanban_card_id IN This parameter passes the Kanban Card Id.
1054 * p_transfer_qty IN This parameter passes the actual transfer quantity.
1055 * x_kanban_type OUT Returns the KanBan Type (Inter/Intra)
1056 * x_inter_order_num OUT Returns the Kanban Internal Sales Order Number
1057 * x_inter_order_id OUT Returns the Kanban Internal Sales Order Id
1058 * x_inter_line_id OUT Returns the Kanban Internal Sales Order Line Id
1059 * x_intra_order_num OUT Returns the Kanban move order number
1060 * x_intra_order_id OUT Returns the kanban mover order id
1061 * x_intra_line_id OUT Returns the Kanban Move order line id
1062 * x_header_id OUT Returns the header_id from OE_ORDER_LINES_ALL
1063 * x_line_id OUT Returns the line_id from OE_ORDER_LINES_ALL
1064 * x_kanban_size OUT Returns the kanban size
1065 * x_retcode OUT Returns the error code.
1066 * Values are: S - Normal Completion
1067 * W - Warning
1068 * E - Error
1069 * x_errmsg OUT Returns the error message if any.
1070 *
1071 *PREREQUISITES
1072 * None
1073 *
1074 * CALLED BY
1075 * complete_process
1076 *
1077 **********************************************************************************************/
1078 PROCEDURE validate_data
1079 ( p_kanban_card_id IN NUMBER,
1080 p_transfer_qty IN NUMBER,
1081 x_organization_id OUT NOCOPY NUMBER,
1082 x_source_type OUT NOCOPY NUMBER,
1083 x_kanban_size OUT NOCOPY NUMBER,
1084 x_inter_order_num OUT NOCOPY VARCHAR2,
1085 x_inter_order_id OUT NOCOPY NUMBER,
1086 x_inter_line_id OUT NOCOPY NUMBER,
1087 x_intra_order_num OUT NOCOPY VARCHAR2,
1088 x_intra_order_id OUT NOCOPY NUMBER,
1089 x_intra_line_id OUT NOCOPY NUMBER,
1090 x_header_id OUT NOCOPY NUMBER,
1091 x_line_id OUT NOCOPY NUMBER,
1092 x_retcode OUT NOCOPY VARCHAR2,
1093 x_errmsg OUT NOCOPY VARCHAR2
1094 )
1095 IS
1096
1097 l_api_name CONSTANT VARCHAR2(30) := 'validate_data';
1098 l_log_module CONSTANT VARCHAR2(80) := G_LOG_MODULE || l_api_name;
1099
1100 -- *****************************************************
1101 -- Cursor to fetch the Quantity Onhand
1102 -- *****************************************************
1103 CURSOR c_qty_on_hand(p_kanban_card_id IN NUMBER)
1104 IS
1105 SELECT SUM(moq.transaction_quantity) transaction_quantity
1106 FROM mtl_onhand_quantities moq,
1107 mtl_kanban_cards mkc
1108 WHERE mkc.inventory_item_id = moq.inventory_item_id
1109 AND mkc.SOURCE_organization_id = moq.organization_id
1110 AND mkc.source_subinventory = moq.subinventory_code
1111 AND NVL(mkc.SOURCE_LOCATOR_ID, 1) = NVL(moq.locator_id, 1)
1112 AND mkc.kanban_card_id = p_kanban_card_id;
1113
1114 -- *************************************************************************************
1115 -- Cursor to get the Sales Order Details by passing the kanban_activity_id as parameter
1116 -- *************************************************************************************
1117 CURSOR c_inter_order(p_kanban_card_id IN NUMBER)
1118 IS
1119 SELECT oeh.order_number,
1120 mkca.document_header_id inter_header_id,
1121 mkca.document_detail_id inter_line_id,
1122 wdd.ship_tolerance_above,
1123 oel.flow_status_code flow_status_code,
1124 oel.line_id line_id,
1125 oel.header_id header_id
1126 FROM oe_order_headers_all oeh,
1127 oe_order_lines_all oel ,
1128 wsh_delivery_details wdd,
1129 mtl_kanban_cards mkc,
1130 mtl_kanban_card_activity mkca
1131 WHERE oeh.source_document_id = mkca.document_header_id
1132 AND oeh.header_id = oel.header_id
1133 AND oel.source_document_line_id = mkca.document_detail_id
1134 AND wdd.source_line_id = oel.line_id
1135 AND wdd.source_header_id = oeh.header_id
1136 AND wdd.released_status IN ('B', 'R', 'Y')
1137 AND oeh.flow_status_code = 'BOOKED'
1138 AND oel.flow_status_code = 'AWAITING_SHIPPING'
1139 AND mkca.kanban_card_id = mkc.kanban_card_id
1140 AND mkca.replenishment_cycle_id = mkc.current_replnsh_cycle_id
1141 AND mkc.kanban_card_id = p_kanban_card_id;
1142
1143 -- *************************************************************************************
1144 -- Cursor to get the Move Order Details by passing the kanban_activity_id as parameter
1145 -- *************************************************************************************
1146 CURSOR c_intra_order(p_kanban_card_id IN NUMBER)
1147 IS
1148 SELECT mtrh.request_number,
1149 mtrh.header_id,
1150 mtrl.line_id
1151 FROM mtl_txn_request_headers mtrh,
1152 mtl_txn_request_lines mtrl,
1153 mtl_kanban_cards mkc,
1154 mtl_kanban_card_activity mkca
1155 WHERE mtrh.header_id = mkca.document_header_id
1156 AND mtrl.line_id = mkca.document_detail_id
1157 AND mtrh.header_status = mtrl.line_status
1158 AND mtrh.header_status = 7 -- Pre Approved
1159 AND mkca.kanban_card_id = mkc.kanban_card_id
1160 AND mkca.replenishment_cycle_id = mkc.current_replnsh_cycle_id
1161 AND mkc.kanban_card_id = p_kanban_card_id;
1162
1163 -- *************************************************************************************
1164 -- Cursor to check whether the Kanban Card ID exists
1165 -- *************************************************************************************
1166 CURSOR c_kanban_detail(p_kanban_card_id IN NUMBER)
1167 IS
1168 SELECT mkc.kanban_card_id,
1169 mkc.card_status,
1170 mkc.source_type,
1171 mkc.kanban_size,
1172 mkc.inventory_item_id,
1173 mkc.organization_id,
1174 mkc.subinventory_name,
1175 mkc.locator_id,
1176 mkc.source_organization_id,
1177 mkc.source_subinventory,
1178 mkc.source_locator_id
1179 FROM mtl_kanban_cards mkc
1180 WHERE mkc.kanban_card_id = p_kanban_card_id;
1181
1182 -- Cursor to fetch item details . Added For Bug 12921002.
1183 CURSOR c_item_details(p_item_id IN NUMBER, p_organization_id IN NUMBER)
1184 IS SELECT msi.lot_control_code,
1185 msi.serial_number_control_code
1186 FROM mtl_system_items msi
1187 WHERE msi.inventory_item_id = p_item_id
1188 AND msi.organization_id = p_organization_id;
1189
1190 l_qty_on_hand_rec c_qty_on_hand%ROWTYPE;
1191 l_inter_order_rec c_inter_order%ROWTYPE;
1192 l_intra_order_rec c_intra_order%ROWTYPE;
1193 l_kanban_detail_rec c_kanban_detail%ROWTYPE;
1194
1195 l_tolerance_param NUMBER;
1196 l_tolerance_above_size NUMBER;
1197 l_available_to_transact NUMBER;
1198
1199 l_item_details_rec c_item_details%ROWTYPE;
1200
1201 BEGIN
1202
1203 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1204 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1205 l_log_module || '.begin',
1206 'Parameters: ' ||
1207 'p_kanban_card_id = ' || p_kanban_card_id || ', ' ||
1208 'p_transfer_qty = ' || p_transfer_qty);
1209 END IF;
1210
1211 -- Initialize return status to success
1212 x_retcode := FND_API.G_RET_STS_SUCCESS;
1213
1214
1215 -- Validate kanban card
1216 OPEN c_kanban_detail(p_kanban_card_id);
1217 FETCH c_kanban_detail INTO l_kanban_detail_rec;
1218 CLOSE c_kanban_detail;
1219
1220 IF l_kanban_detail_rec.kanban_card_id IS NULL THEN
1221
1222 x_retcode := FND_API.G_RET_STS_ERROR;
1223
1224 fnd_message.set_name('FLM','FLM_INVALID_KANBAN');
1225 x_errmsg := fnd_message.get||FND_CONST.NEWLINE;
1226
1227 ELSE
1228
1229 x_organization_id := l_kanban_detail_rec.organization_id;
1230 x_source_type := l_kanban_detail_rec.source_type;
1231 x_kanban_size := l_kanban_detail_rec.kanban_size;
1232
1233 END IF;
1234
1235 -- Validate transfer quantity
1236 IF p_transfer_qty IS NULL OR p_transfer_qty <= 0 THEN
1237
1238 x_retcode := FND_API.G_RET_STS_ERROR;
1239
1240 fnd_message.set_name('FLM','FLM_ATTRIBUTE_INVALID');
1241 fnd_message.set_token('ATTRIBUTE', 'Transfer Quantity');
1242 x_errmsg := x_errmsg || fnd_message.get || FND_CONST.NEWLINE;
1243
1244 END IF;
1245
1246 -- Added For Bug 12921002.
1247 OPEN c_item_details(l_kanban_detail_rec.inventory_item_id, l_kanban_detail_rec.organization_id);
1248 FETCH c_item_details INTO l_item_details_rec;
1249 CLOSE c_item_details;
1250
1251 IF l_item_details_rec.lot_control_code <> 1 OR l_item_details_rec.serial_number_control_code <> 1 THEN
1252 x_retcode := FND_API.G_RET_STS_ERROR;
1253 fnd_message.set_name('FLM', 'FLM_EKB_LOT_SERIAL_ERR');
1254 x_errmsg := x_errmsg || fnd_message.get || FND_CONST.NEWLINE;
1255 END IF;
1256
1257 -- Validate tolerance value
1258 l_tolerance_param := flm_kanban_config_params.get_tol_kanban_transfer(l_kanban_detail_rec.organization_id);
1259 IF l_tolerance_param NOT IN (1, 2, 3) THEN
1260
1261 x_retcode := FND_API.G_RET_STS_ERROR;
1262
1263 fnd_message.set_name('FLM','FLM_TOLERANCE_PARAM_ERR');
1264 x_errmsg := x_errmsg || fnd_message.get || FND_CONST.NEWLINE;
1265
1266 END IF;
1267
1268 -- Validate card status for 'Active'
1269 IF l_kanban_detail_rec.card_status <> INV_KANBAN_PVT.G_Card_Status_Active THEN
1270
1271 x_retcode := FND_API.G_RET_STS_ERROR;
1272
1273 fnd_message.set_name('FLM','FLM_INVALID_CARD_STATUS');
1274 x_errmsg := x_errmsg || fnd_message.get || FND_CONST.NEWLINE;
1275
1276 END IF;
1277
1278 -- Validate source type for Inter Org / Intra Org
1279 IF l_kanban_detail_rec.source_type NOT IN (INV_KANBAN_PVT.G_Source_Type_InterOrg, INV_KANBAN_PVT.G_Source_Type_IntraOrg) THEN
1280
1281 x_retcode := FND_API.G_RET_STS_ERROR;
1282
1283 fnd_message.set_name('FLM','FLM_NO_KANBAN_TYPE');
1284 x_errmsg := x_errmsg || fnd_message.get || FND_CONST.NEWLINE;
1285
1286 END IF;
1287
1288 IF x_retcode <> FND_API.G_RET_STS_SUCCESS THEN
1289 RETURN;
1290 END IF;
1291
1292
1293 IF l_kanban_detail_rec.source_type = INV_KANBAN_PVT.G_Source_Type_InterOrg THEN
1294
1295 OPEN c_inter_order(p_kanban_card_id);
1296 FETCH c_inter_order INTO l_inter_order_rec;
1297 CLOSE c_inter_order;
1298
1299 IF l_inter_order_rec.order_number IS NULL THEN
1300
1301 x_retcode := FND_API.G_RET_STS_ERROR;
1302
1303 fnd_message.set_name('FLM','FLM_INTER_ORDERNUM_ERR');
1304 x_errmsg := fnd_message.get;
1305
1306 ELSE
1307
1308 x_inter_order_num := l_inter_order_rec.order_number;
1309 x_inter_order_id := l_inter_order_rec.inter_header_id;
1310 x_inter_line_id := l_inter_order_rec.inter_line_id;
1311 x_header_id :=l_inter_order_rec.header_id;
1312 x_line_id :=l_inter_order_rec.line_id;
1313
1314 IF l_tolerance_param = 2 AND p_transfer_qty <> l_kanban_detail_rec.kanban_size THEN
1315
1316 x_retcode := FND_API.G_RET_STS_ERROR;
1317
1318 fnd_message.set_name('FLM','FLM_SHIPMENT_DISALLOWED');
1319 fnd_message.set_token('ORDER_NUMBER', x_inter_order_num);
1320 fnd_message.set_token('TOLERANCE_QUANTITY', l_kanban_detail_rec.kanban_size);
1321 x_errmsg := fnd_message.get;
1322
1323 ELSIF l_tolerance_param = 3 THEN
1324
1325 IF p_transfer_qty > l_kanban_detail_rec.kanban_size THEN
1326
1327 IF l_inter_order_rec.ship_tolerance_above > 0 THEN
1328 l_tolerance_above_size := l_kanban_detail_rec.kanban_size +
1329 (l_kanban_detail_rec.kanban_size *
1330 l_inter_order_rec.ship_tolerance_above / 100
1331 );
1332 ELSE
1333 l_tolerance_above_size := l_kanban_detail_rec.kanban_size;
1334 END IF;
1335
1336 IF p_transfer_qty > l_tolerance_above_size THEN
1337
1338 x_retcode := FND_API.G_RET_STS_ERROR;
1339
1340 fnd_message.set_name('FLM','FLM_SHIP_TOLERANCE_ERR');
1341 fnd_message.set_token('ORDER_NUMBER', x_inter_order_num);
1342 fnd_message.set_token('TOLERANCE_QUANTITY', l_tolerance_above_size);
1343 x_errmsg := fnd_message.get;
1344
1345 END IF;
1346
1347 END IF; -- p_transfer_qty
1348
1349 END IF; -- l_tolerance_param
1350
1351 END IF;
1352
1353 ELSIF l_kanban_detail_rec.source_type = INV_KANBAN_PVT.G_Source_Type_IntraOrg THEN
1354
1355 OPEN c_intra_order(p_kanban_card_id);
1356 FETCH c_intra_order INTO l_intra_order_rec;
1357 CLOSE c_intra_order;
1358
1359 IF l_intra_order_rec.request_number IS NULL THEN
1360
1361 x_retcode := FND_API.G_RET_STS_ERROR;
1362
1363 fnd_message.set_name('FLM','FLM_INTRA_ORDERNUM_ERR');
1364 x_errmsg := fnd_message.get;
1365
1366 ELSE
1367
1368 x_intra_order_num := l_intra_order_rec.request_number;
1369 x_intra_order_id := l_intra_order_rec.header_id;
1370 x_intra_line_id := l_intra_order_rec.line_id;
1371
1372 IF l_tolerance_param = 2 AND p_transfer_qty <> l_kanban_detail_rec.kanban_size THEN
1373
1374 x_retcode := FND_API.G_RET_STS_ERROR;
1375
1376 fnd_message.set_name('FLM','FLM_SHIPMENT_DISALLOWED');
1377 fnd_message.set_token('ORDER_NUMBER', x_intra_order_num);
1378 fnd_message.set_token('TOLERANCE_QUANTITY', l_kanban_detail_rec.kanban_size);
1379 x_errmsg := fnd_message.get;
1380
1381 END IF;
1382
1383 END IF;
1384
1385 END IF; -- l_kanban_detail_rec.source_type
1386
1387 IF x_retcode <> FND_API.G_RET_STS_SUCCESS THEN
1388 RETURN;
1389 END IF;
1390
1391
1392 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1393 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1394 l_log_module || '.get_available_to_transact',
1395 'Calling get_available_to_transact');
1396 END IF;
1397
1398 -- ***********************************************************************************
1399 -- Call GET_AVAILABLE_TO_TRANSACT procedure to fetch the Avalable to Transact Value
1400 -- ***********************************************************************************
1401 get_available_to_transact(p_inventory_item_id => l_kanban_detail_rec.inventory_item_id,
1402 p_subinventory_name => l_kanban_detail_rec.subinventory_name,
1403 p_locator_id => l_kanban_detail_rec.locator_id,
1404 p_source_organization_id => l_kanban_detail_rec.source_organization_id,
1405 p_source_subinventory => l_kanban_detail_rec.source_subinventory,
1406 p_source_locator_id => l_kanban_detail_rec.source_locator_id,
1407 x_available_to_transact => l_available_to_transact,
1408 x_retcode => x_retcode,
1409 x_errmsg => x_errmsg
1410 );
1411
1412 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1413 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1414 l_log_module || '.get_available_to_transact',
1415 'After get_available_to_transact: ' ||
1416 'x_available_to_transact = ' || l_available_to_transact || ', ' ||
1417 'x_retcode = ' || x_retcode || ', ' ||
1418 'x_errmsg = ' || x_errmsg);
1419 END IF;
1420
1421 -- ***********************************************************************************
1422 -- Check if the Transfer Qty > Available to Transact
1423 -- ***********************************************************************************
1424 IF l_available_to_transact < p_transfer_qty OR l_available_to_transact IS NULL THEN
1425
1426 x_retcode := FND_API.G_RET_STS_ERROR;
1427
1428 fnd_message.set_name('FLM','FLM_TRANS_QTY_ERR');
1429 x_errmsg := x_errmsg || fnd_message.get || FND_CONST.NEWLINE;
1430
1431 END IF;
1432
1433
1434 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1435 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1436 l_log_module || '.end',
1437 'x_organization_id = ' || x_organization_id || ', ' ||
1438 'x_source_type = ' || x_source_type || ', ' ||
1439 'x_kanban_size = ' || x_kanban_size || ', ' ||
1440 'x_inter_order_num = ' || x_inter_order_num || ', ' ||
1441 'x_inter_order_id = ' || x_inter_order_id || ', ' ||
1442 'x_inter_line_id = ' || x_inter_line_id || ', ' ||
1443 'x_intra_order_num = ' || x_intra_order_num || ', ' ||
1444 'x_intra_order_id = ' || x_intra_order_id || ', ' ||
1445 'x_intra_line_id = ' || x_intra_line_id || ', ' ||
1446 'x_retcode = ' || x_retcode || ', ' ||
1447 'x_errmsg = ' || x_errmsg);
1448 END IF;
1449
1450 EXCEPTION
1451 WHEN OTHERS THEN
1452 x_retcode := FND_API.G_RET_STS_UNEXP_ERROR;
1453 x_errmsg := SQLERRM;
1454
1455 END validate_data;
1456
1457
1458 /**************************************************************************************************************
1459 *
1460 * PROCEDURE
1461 * COMPLETE_PROCESS
1462 *
1463 * DESCRIPTION
1464 * This procedure is a main public procedure and responsible to complete the KanBan Intra/Inter Org transfer.
1465 *
1466 * PARAMETERS
1467 * ==========
1468 * NAME TYPE DESCRIPTION
1469 * ----------------- -------- ---------------------------------------------
1470 * p_kanban_id IN This parameter passes the Kanban Card ID
1471 * p_transfer_qty IN This parameter passes the Transfer Quantity.
1472 * x_retcode OUT Returns the error code.
1473 * x_errmsg OUT Returns the error message if any.
1474 *
1475 * PREREQUISITES
1476 * None
1477 *
1478 * CALLED BY
1479 * This procedure is called by eKanban Move Transfer UI.
1480 *
1481 **************************************************************************************************************/
1482 PROCEDURE complete_process
1483 ( p_kanban_id IN NUMBER,
1484 p_transfer_qty IN NUMBER,
1485 p_process_flag IN VARCHAR2,
1486 x_retcode OUT NOCOPY VARCHAR2,
1487 x_errmsg OUT NOCOPY VARCHAR2
1488 )
1489 IS
1490
1491 l_api_name CONSTANT VARCHAR2(30) := 'complete_process';
1492 l_log_module CONSTANT VARCHAR2(80) := G_LOG_MODULE || l_api_name;
1493
1494 l_inter_order_num oe_order_headers_all.order_number%TYPE;
1495 l_inter_order_id oe_order_headers_all.source_document_id%TYPE;
1496 l_inter_line_id oe_order_lines_all.source_document_line_id%TYPE;
1497 l_header_id oe_order_headers_all.header_id%TYPE;
1498 l_line_id oe_order_lines_all.line_id%TYPE;
1499 l_intra_order_num mtl_txn_request_headers.request_number%TYPE;
1500 l_intra_order_id mtl_txn_request_headers.header_id%TYPE;
1501 l_intra_line_id mtl_txn_request_lines.line_id%TYPE;
1502 l_organization_id mtl_kanban_cards.organization_id%TYPE;
1503 l_source_type mtl_kanban_cards.source_type%TYPE;
1504 l_kanban_size mtl_kanban_cards.kanban_size%TYPE;
1505
1506 BEGIN
1507
1508 SAVEPOINT complete_process;
1509
1510 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1511 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1512 l_log_module || '.begin',
1513 'Parameters: ' ||
1514 'p_kanban_id = ' || p_kanban_id || ', ' ||
1515 'p_transfer_qty = ' || p_transfer_qty || ', ' ||
1516 'p_process_flag = ' || p_process_flag);
1517 END IF;
1518
1519
1520 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1521 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1522 l_log_module || '.validate_data',
1523 'Calling validate_data');
1524 END IF;
1525
1526 validate_data(p_kanban_card_id => p_kanban_id,
1527 p_transfer_qty => p_transfer_qty,
1528 x_organization_id => l_organization_id,
1529 x_source_type => l_source_type,
1530 x_kanban_size => l_kanban_size,
1531 x_inter_order_num => l_inter_order_num,
1532 x_inter_order_id => l_inter_order_id,
1533 x_inter_line_id => l_inter_line_id,
1534 x_intra_order_num => l_intra_order_num,
1535 x_intra_order_id => l_intra_order_id,
1536 x_intra_line_id => l_intra_line_id,
1537 x_header_id => l_header_id,
1538 x_line_id => l_line_id,
1539 x_retcode => x_retcode,
1540 x_errmsg => x_errmsg);
1541
1542 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1543 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1544 l_log_module || '.validate_data',
1545 'After validate_data: ' ||
1546 'x_retcode = ' || x_retcode || ', ' ||
1547 'x_errmsg = ' || x_errmsg);
1548 END IF;
1549
1550 IF x_retcode <> FND_API.G_RET_STS_SUCCESS THEN
1551 RETURN;
1552 END IF;
1553
1554 IF p_process_flag = 'Y' THEN
1555
1556 IF l_source_type = INV_KANBAN_PVT.G_Source_Type_InterOrg THEN
1557
1558 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1559 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1560 l_log_module || '.complete_inter_transfer',
1561 'Calling complete_inter_transfer');
1562 END IF;
1563
1564 complete_inter_transfer(p_kanban_card_id => p_kanban_id,
1565 p_organization_id => l_organization_id,
1566 p_inter_order_id => l_inter_order_id,
1567 p_inter_line_id => l_inter_line_id,
1568 p_transfer_qty => p_transfer_qty,
1569 p_kanban_size => l_kanban_size,
1570 p_header_id => l_header_id,
1571 p_line_id => l_line_id,
1572 x_retcode => x_retcode,
1573 x_errmsg => x_errmsg);
1574
1575 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1576 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1577 l_log_module || '.complete_inter_transfer',
1578 'After complete_inter_transfer: ' ||
1579 'x_retcode = ' || x_retcode || ', ' ||
1580 'x_errmsg = ' || x_errmsg);
1581 END IF;
1582
1583 ELSIF l_source_type = INV_KANBAN_PVT.G_Source_Type_IntraOrg THEN
1584
1585 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1586 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1587 l_log_module || '.complete_intra_transfer',
1588 'Calling complete_intra_transfer');
1589 END IF;
1590
1591 complete_intra_transfer(p_kanban_card_id => p_kanban_id,
1592 p_intra_order_id => l_intra_order_id,
1593 p_intra_line_id => l_intra_line_id,
1594 p_transfer_qty => p_transfer_qty,
1595 p_kanban_size => l_kanban_size,
1596 x_retcode => x_retcode,
1597 x_errmsg => x_errmsg);
1598
1599 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1600 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1601 l_log_module || '.complete_intra_transfer',
1602 'After complete_intra_transfer: ' ||
1603 'x_retcode = ' || x_retcode || ', ' ||
1604 'x_errmsg = ' || x_errmsg);
1605 END IF;
1606
1607 END IF; -- l_source_type
1608
1609 END IF; -- p_process_flag
1610
1611 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1612 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1613 l_log_module || '.end',
1614 'x_retcode = ' || x_retcode || ', ' ||
1615 'x_errmsg = ' || x_errmsg);
1616 END IF;
1617
1618 EXCEPTION
1619 WHEN OTHERS THEN
1620 ROLLBACK TO complete_process;
1621 x_retcode := FND_API.G_RET_STS_UNEXP_ERROR;
1622 x_errmsg := SQLERRM;
1623
1624 END complete_process;
1625
1626 END FLM_KANBAN_TRANSFER;