[Home] [Help]
PACKAGE BODY: APPS.INV_KANBANCARD_PKG
Source
1 PACKAGE BODY inv_kanbancard_pkg AS
2 /* $Header: INVKCRDB.pls 120.10.12020000.2 2012/07/09 08:07:11 asugandh ship $ */
3 g_pkg_name CONSTANT VARCHAR2(30) := 'INV_KanbanCard_PKG';
4
5 TYPE supply_status_change_tbl_type IS TABLE OF VARCHAR2(1);
6
7 g_true CONSTANT VARCHAR2(1) := fnd_api.g_true;
8 g_false CONSTANT VARCHAR2(1) := fnd_api.g_false;
9 g_supply_status_rows CONSTANT NUMBER := 7;
10 g_supply_status_columns CONSTANT NUMBER := 7;
11 g_supply_status_change_tbl supply_status_change_tbl_type
12 := supply_status_change_tbl_type(
13 g_true
14 , g_true
15 , g_true
16 , g_true
17 , g_false
18 , g_false
19 , g_false
20 , g_false
21 , g_true
22 , g_true
23 , g_true
24 , g_false
25 , g_false
26 , g_false
27 , g_false
28 , g_false
29 , g_true
30 , g_true
31 , g_false
32 , g_false
33 , g_false
34 , g_false
35 , g_true
36 , g_false
37 , g_true
38 , g_true
39 , g_false
40 , g_false
41 , g_false
42 , g_true
43 , g_false
44 , g_false
45 , g_true
46 , g_true
47 , g_true
48 , g_false
49 , g_true
50 , g_false
51 , g_false
52 , g_false
53 , g_true
54 , g_false
55 , g_false
56 , g_true
57 , g_false
58 , g_false
59 , g_false
60 , g_false
61 , g_true
62 );
63
64 PROCEDURE mydebug(msg IN VARCHAR2) IS
65 BEGIN
66 inv_trx_util_pub.TRACE(msg, 'INV_KANBANCARD_PKG', 9);
67 END mydebug;
68
69 FUNCTION check_unique(p_kanban_card_id IN OUT NOCOPY NUMBER, p_organization_id NUMBER, p_kanban_card_number VARCHAR2)
70 RETURN BOOLEAN IS
71 l_dummy VARCHAR2(1);
72 BEGIN
73 SELECT 'X'
74 INTO l_dummy
75 FROM mtl_kanban_cards
76 WHERE organization_id = p_organization_id
77 AND kanban_card_number = p_kanban_card_number
78 AND((p_kanban_card_id IS NULL)
79 OR(kanban_card_id <> p_kanban_card_id));
80
81 RAISE TOO_MANY_ROWS;
82 EXCEPTION
83 WHEN NO_DATA_FOUND THEN
84 RETURN TRUE;
85 WHEN OTHERS THEN
86 fnd_message.set_name('INV', 'INV_KANBAN_CARD_NUM_EXISTS');
87 fnd_message.set_token('CARD_NUMBER', p_kanban_card_number);
88 RETURN FALSE;
89 END check_unique;
90
91 FUNCTION query_row(p_kanban_card_id IN NUMBER)
92 RETURN inv_kanban_pvt.kanban_card_rec_type IS
93 l_kanban_card_rec inv_kanban_pvt.kanban_card_rec_type;
94 BEGIN
95
96 SELECT kanban_card_id
97 , kanban_card_number
98 , pull_sequence_id
99 , inventory_item_id
100 , organization_id
101 , subinventory_name
102 , supply_status
103 , card_status
104 , kanban_card_type
105 , source_type
106 , kanban_size
107 , last_update_date
108 , last_updated_by
109 , creation_date
110 , created_by
111 , locator_id
112 , supplier_id
113 , supplier_site_id
114 , source_organization_id
115 , source_subinventory
116 , source_locator_id
117 , wip_line_id
118 , current_replnsh_cycle_id
119 , ERROR_CODE
120 , last_update_login
121 , last_print_date
122 , attribute_category
123 , attribute1
124 , attribute2
125 , attribute3
126 , attribute4
127 , attribute5
128 , attribute6
129 , attribute7
130 , attribute8
131 , attribute9
132 , attribute10
133 , attribute11
134 , attribute12
135 , attribute13
136 , attribute14
137 , attribute15
138 , request_id
139 , program_application_id
140 , program_id
141 , program_update_date
142 /* eKanban Changes */
143 , replenishment_count
144 , max_replenishments
145 , disable_date
146 , replacement_flag
147 INTO l_kanban_card_rec.kanban_card_id
148 , l_kanban_card_rec.kanban_card_number
149 , l_kanban_card_rec.pull_sequence_id
150 , l_kanban_card_rec.inventory_item_id
151 , l_kanban_card_rec.organization_id
152 , l_kanban_card_rec.subinventory_name
153 , l_kanban_card_rec.supply_status
154 , l_kanban_card_rec.card_status
155 , l_kanban_card_rec.kanban_card_type
156 , l_kanban_card_rec.source_type
157 , l_kanban_card_rec.kanban_size
158 , l_kanban_card_rec.last_update_date
159 , l_kanban_card_rec.last_updated_by
160 , l_kanban_card_rec.creation_date
161 , l_kanban_card_rec.created_by
162 , l_kanban_card_rec.locator_id
163 , l_kanban_card_rec.supplier_id
164 , l_kanban_card_rec.supplier_site_id
165 , l_kanban_card_rec.source_organization_id
166 , l_kanban_card_rec.source_subinventory
167 , l_kanban_card_rec.source_locator_id
168 , l_kanban_card_rec.wip_line_id
169 , l_kanban_card_rec.current_replnsh_cycle_id
170 , l_kanban_card_rec.ERROR_CODE
171 , l_kanban_card_rec.last_update_login
172 , l_kanban_card_rec.last_print_date
173 , l_kanban_card_rec.attribute_category
174 , l_kanban_card_rec.attribute1
175 , l_kanban_card_rec.attribute2
176 , l_kanban_card_rec.attribute3
177 , l_kanban_card_rec.attribute4
178 , l_kanban_card_rec.attribute5
179 , l_kanban_card_rec.attribute6
180 , l_kanban_card_rec.attribute7
181 , l_kanban_card_rec.attribute8
182 , l_kanban_card_rec.attribute9
183 , l_kanban_card_rec.attribute10
184 , l_kanban_card_rec.attribute11
185 , l_kanban_card_rec.attribute12
186 , l_kanban_card_rec.attribute13
187 , l_kanban_card_rec.attribute14
188 , l_kanban_card_rec.attribute15
189 , l_kanban_card_rec.request_id
190 , l_kanban_card_rec.program_application_id
191 , l_kanban_card_rec.program_id
192 , l_kanban_card_rec.program_update_date
193 /* eKanban Changes */
194 , l_kanban_card_rec.replenishment_count
195 , l_kanban_card_rec.max_replenishments
196 , l_kanban_card_rec.disable_date
197 , l_kanban_card_rec.replacement_flag
198 FROM mtl_kanban_cards
199 WHERE kanban_card_id = p_kanban_card_id;
200
201 l_kanban_card_rec.document_type := NULL;
202 l_kanban_card_rec.document_header_id := NULL;
203 l_kanban_card_rec.document_detail_id := NULL;
204 RETURN l_kanban_card_rec;
205 EXCEPTION
206 WHEN OTHERS THEN
207 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
208 fnd_msg_pub.add_exc_msg(g_pkg_name, 'Query_Row');
209 END IF;
210
211 RAISE fnd_api.g_exc_unexpected_error;
212 END query_row;
213
214 FUNCTION cell(row_in NUMBER, col_in NUMBER)
215 RETURN NUMBER IS
216 BEGIN
217 RETURN (col_in - 1) * g_supply_status_rows + row_in;
218 END cell;
219
220 FUNCTION supply_status_change_ok(p_from_supply_status NUMBER, p_to_supply_status NUMBER, p_card_status NUMBER)
221 RETURN BOOLEAN IS
222 l_result BOOLEAN;
223 l_supply_status_from VARCHAR2(30);
224 l_supply_status_to VARCHAR2(30);
225 BEGIN
226 /*
227 If p_card_status = INV_Kanban_PVT.G_Card_Status_Cancel
228 Then
229 FND_MESSAGE.SET_NAME('INV','INV_NO_ACT_ALLOW_CANCEL_CARD');
230 Return False;
231 Elsif p_card_status = INV_Kanban_PVT.G_Card_Status_Hold
232 Then
233 FND_MESSAGE.SET_NAME('INV','INV_NO_ACT_ALLOW_HOLD_CARD');
234 Return False;
235 Else
236 */
237 l_result := fnd_api.to_boolean(g_supply_status_change_tbl(cell(trunc(p_to_supply_status), trunc(p_from_supply_status))));
238
239 IF l_result THEN
240 RETURN(l_result);
241 END IF;
242
243 BEGIN
244 SELECT a.meaning
245 , b.meaning
246 INTO l_supply_status_from
247 , l_supply_status_to
248 FROM mfg_lookups a, mfg_lookups b
249 WHERE a.lookup_type = 'MTL_KANBAN_SUPPLY_STATUS'
250 AND a.lookup_code = p_from_supply_status
251 AND b.lookup_type = 'MTL_KANBAN_SUPPLY_STATUS'
252 AND b.lookup_code = p_to_supply_status;
253
254 fnd_message.set_name('INV', 'INV_SUPPLY_STATUS_NOT_ALLOWED');
255 fnd_message.set_token('SUPPLY_STATUS_FROM', l_supply_status_from);
256 fnd_message.set_token('SUPPLY_STATUS_TO', l_supply_status_to);
257 EXCEPTION
258 WHEN OTHERS THEN
259 NULL;
260 END;
261
262 RETURN(l_result);
263 /* End If; */
264
265 EXCEPTION
266 WHEN OTHERS THEN
267 return false;
268 END supply_status_change_ok;
269
270 --Added method with p_kanban_card_id
271 FUNCTION supply_status_change_ok(p_kanban_card_id NUMBER,p_from_supply_status NUMBER, p_to_supply_status NUMBER, p_card_status NUMBER)
272 RETURN BOOLEAN IS
273 l_result BOOLEAN;
274 l_supply_status_from VARCHAR2(30);
275 l_supply_status_to VARCHAR2(30);
276 BEGIN
277
278 l_result := fnd_api.to_boolean(fnd_api.g_true);
279
280 if trunc(p_from_supply_status) in (1,2,3,4,5,6,7) and
281 trunc(p_to_supply_status) in (1,2,3,4,5,6,7) then
282 l_result := fnd_api.to_boolean(g_supply_status_change_tbl(cell(trunc(p_to_supply_status), trunc(p_from_supply_status))));
283 elsif trunc(p_from_supply_status) = 8 or trunc(p_to_supply_status) = 8 then
284 l_result := fnd_api.to_boolean(fnd_api.g_false);
285 end if;
286
287 if ( p_from_supply_status not in (1,2,3,4,5,6,7,8) or
288 p_to_supply_status not in (1,2,3,4,5,6,7,8)) and l_result then
289 if nvl(fnd_profile.VALUE('FLM_EKB_OPS_FLAG'),0) = 1 then
290 if FLM_KANBAN_CUSTOM_PKG.status_change_hook(p_kanban_card_id,p_from_supply_status,p_to_supply_status) = 1 then
291 l_result := fnd_api.to_boolean(fnd_api.g_true);
292 else
293 l_result := fnd_api.to_boolean(fnd_api.g_false);
294 end if;
295 else
296 l_result := fnd_api.to_boolean(fnd_api.g_false);
297 end if;
298 end if;
299
300 IF l_result THEN
301 RETURN(l_result);
302 END IF;
303
304 RETURN(l_result);
305 /* End If; */
306
307 EXCEPTION
308 WHEN OTHERS THEN
309 return false;
310 END supply_status_change_ok;
311
312 PROCEDURE commit_row IS
313 BEGIN
314 COMMIT;
315 END commit_row;
316
317 PROCEDURE rollback_row IS
318 BEGIN
319 ROLLBACK;
320 END rollback_row;
321
322 FUNCTION changed_row(p_kanban_card_rec inv_kanban_pvt.kanban_card_rec_type, p_old_kanban_card_rec inv_kanban_pvt.kanban_card_rec_type)
323 RETURN BOOLEAN IS
324 l_dupl_inprocess_act BOOLEAN := ( p_kanban_card_rec.supply_status = p_old_kanban_card_rec.supply_status
325 AND p_kanban_card_rec.supply_status = 5
326 AND p_kanban_card_rec.current_replnsh_cycle_id = p_old_kanban_card_rec.current_replnsh_cycle_id
327 );
328 BEGIN
329 -- Bug 3661982 : If the card is in 'In Process' state for a particular replenisment cycle,
330 -- stop insertion into card activity table for another 'In Process' state activity if new
331 -- replenisment cycle is identical to the old one.
332 IF (
333 p_kanban_card_rec.kanban_card_number = p_old_kanban_card_rec.kanban_card_number
334 AND p_kanban_card_rec.pull_sequence_id = p_old_kanban_card_rec.pull_sequence_id
335 AND p_kanban_card_rec.inventory_item_id = p_old_kanban_card_rec.inventory_item_id
336 AND p_kanban_card_rec.organization_id = p_old_kanban_card_rec.organization_id
337 AND p_kanban_card_rec.subinventory_name = p_old_kanban_card_rec.subinventory_name
338 AND(
339 (p_kanban_card_rec.supply_status = p_old_kanban_card_rec.supply_status
340 AND p_kanban_card_rec.supply_status <> 5)
341 OR l_dupl_inprocess_act
342 )
343 AND p_kanban_card_rec.card_status = p_old_kanban_card_rec.card_status
344 AND p_kanban_card_rec.kanban_card_type = p_old_kanban_card_rec.kanban_card_type
345 AND p_kanban_card_rec.source_type = p_old_kanban_card_rec.source_type
346 AND p_kanban_card_rec.kanban_size = p_old_kanban_card_rec.kanban_size
347 AND(
348 (p_kanban_card_rec.last_print_date = p_old_kanban_card_rec.last_print_date)
349 OR(p_kanban_card_rec.last_print_date IS NULL
350 AND p_old_kanban_card_rec.last_print_date IS NULL)
351 )
352 AND(
353 (p_kanban_card_rec.locator_id = p_old_kanban_card_rec.locator_id)
354 OR(p_kanban_card_rec.locator_id IS NULL
355 AND p_old_kanban_card_rec.locator_id IS NULL)
356 )
357 AND(
358 (p_kanban_card_rec.supplier_id = p_old_kanban_card_rec.supplier_id)
359 OR(p_kanban_card_rec.supplier_id IS NULL
360 AND p_old_kanban_card_rec.supplier_id IS NULL)
361 )
362 AND(
363 (p_kanban_card_rec.supplier_site_id = p_old_kanban_card_rec.supplier_site_id)
364 OR(p_kanban_card_rec.supplier_site_id IS NULL
365 AND p_old_kanban_card_rec.supplier_site_id IS NULL)
366 )
367 AND(
368 (p_kanban_card_rec.source_organization_id = p_old_kanban_card_rec.source_organization_id)
369 OR(p_kanban_card_rec.source_organization_id IS NULL
370 AND p_old_kanban_card_rec.source_organization_id IS NULL)
371 )
372 AND(
373 (p_kanban_card_rec.source_subinventory = p_old_kanban_card_rec.source_subinventory)
374 OR(p_kanban_card_rec.source_subinventory IS NULL
375 AND p_old_kanban_card_rec.source_subinventory IS NULL)
376 )
377 AND(
378 (p_kanban_card_rec.source_locator_id = p_old_kanban_card_rec.source_locator_id)
379 OR(p_kanban_card_rec.source_locator_id IS NULL
380 AND p_old_kanban_card_rec.source_locator_id IS NULL)
381 )
382 AND(
383 (p_kanban_card_rec.wip_line_id = p_old_kanban_card_rec.wip_line_id)
384 OR(p_kanban_card_rec.wip_line_id IS NULL
385 AND p_old_kanban_card_rec.wip_line_id IS NULL)
386 )
387 AND(
388 (p_kanban_card_rec.ERROR_CODE = p_old_kanban_card_rec.ERROR_CODE)
389 OR(p_kanban_card_rec.ERROR_CODE IS NULL
390 AND p_old_kanban_card_rec.ERROR_CODE IS NULL)
391 )
392 AND(
393 (p_kanban_card_rec.attribute_category = p_old_kanban_card_rec.attribute_category)
394 OR(p_kanban_card_rec.attribute_category IS NULL
395 AND p_old_kanban_card_rec.attribute_category IS NULL)
396 )
397 AND(
398 (p_kanban_card_rec.attribute1 = p_old_kanban_card_rec.attribute1)
399 OR(p_kanban_card_rec.attribute1 IS NULL
400 AND p_old_kanban_card_rec.attribute1 IS NULL)
401 )
402 AND(
403 (p_kanban_card_rec.attribute2 = p_old_kanban_card_rec.attribute2)
404 OR(p_kanban_card_rec.attribute2 IS NULL
405 AND p_old_kanban_card_rec.attribute2 IS NULL)
406 )
407 AND(
408 (p_kanban_card_rec.attribute3 = p_old_kanban_card_rec.attribute3)
409 OR(p_kanban_card_rec.attribute3 IS NULL
410 AND p_old_kanban_card_rec.attribute3 IS NULL)
411 )
412 AND(
413 (p_kanban_card_rec.attribute4 = p_old_kanban_card_rec.attribute4)
414 OR(p_kanban_card_rec.attribute4 IS NULL
415 AND p_old_kanban_card_rec.attribute4 IS NULL)
416 )
417 AND(
418 (p_kanban_card_rec.attribute5 = p_old_kanban_card_rec.attribute5)
419 OR(p_kanban_card_rec.attribute5 IS NULL
420 AND p_old_kanban_card_rec.attribute5 IS NULL)
421 )
422 AND(
423 (p_kanban_card_rec.attribute6 = p_old_kanban_card_rec.attribute6)
424 OR(p_kanban_card_rec.attribute6 IS NULL
425 AND p_old_kanban_card_rec.attribute6 IS NULL)
426 )
427 AND(
428 (p_kanban_card_rec.attribute7 = p_old_kanban_card_rec.attribute7)
429 OR(p_kanban_card_rec.attribute7 IS NULL
430 AND p_old_kanban_card_rec.attribute7 IS NULL)
431 )
432 AND(
433 (p_kanban_card_rec.attribute8 = p_old_kanban_card_rec.attribute8)
434 OR(p_kanban_card_rec.attribute8 IS NULL
435 AND p_old_kanban_card_rec.attribute8 IS NULL)
436 )
437 AND(
438 (p_kanban_card_rec.attribute9 = p_old_kanban_card_rec.attribute9)
439 OR(p_kanban_card_rec.attribute9 IS NULL
440 AND p_old_kanban_card_rec.attribute9 IS NULL)
441 )
442 AND(
443 (p_kanban_card_rec.attribute10 = p_old_kanban_card_rec.attribute10)
444 OR(p_kanban_card_rec.attribute10 IS NULL
445 AND p_old_kanban_card_rec.attribute10 IS NULL)
446 )
447 AND(
448 (p_kanban_card_rec.attribute11 = p_old_kanban_card_rec.attribute11)
449 OR(p_kanban_card_rec.attribute11 IS NULL
450 AND p_old_kanban_card_rec.attribute11 IS NULL)
451 )
452 AND(
453 (p_kanban_card_rec.attribute12 = p_old_kanban_card_rec.attribute12)
454 OR(p_kanban_card_rec.attribute12 IS NULL
455 AND p_old_kanban_card_rec.attribute12 IS NULL)
456 )
457 AND(
458 (p_kanban_card_rec.attribute13 = p_old_kanban_card_rec.attribute13)
459 OR(p_kanban_card_rec.attribute13 IS NULL
460 AND p_old_kanban_card_rec.attribute13 IS NULL)
461 )
462 AND(
463 (p_kanban_card_rec.attribute14 = p_old_kanban_card_rec.attribute14)
464 OR(p_kanban_card_rec.attribute14 IS NULL
465 AND p_old_kanban_card_rec.attribute14 IS NULL)
466 )
467 AND(
468 (p_kanban_card_rec.attribute15 = p_kanban_card_rec.attribute15)
469 OR(p_kanban_card_rec.attribute15 IS NULL
470 AND p_old_kanban_card_rec.attribute15 IS NULL)
471 )
472 AND((p_kanban_card_rec.document_type IS NULL)
473 OR l_dupl_inprocess_act)
474 AND((p_kanban_card_rec.document_header_id IS NULL)
475 OR l_dupl_inprocess_act)
476 AND((p_kanban_card_rec.document_detail_id IS NULL)
477 OR l_dupl_inprocess_act)
478 AND((p_kanban_card_rec.replenish_quantity IS NULL)
479 OR l_dupl_inprocess_act)
480 ) THEN
481 RETURN FALSE;
482 ELSE
483 RETURN TRUE;
484 END IF;
485 END changed_row;
486
487 PROCEDURE insert_row(
488 x_return_status OUT NOCOPY VARCHAR2
489 , p_kanban_card_id IN OUT NOCOPY NUMBER
490 , p_kanban_card_number IN OUT NOCOPY VARCHAR2
491 , p_pull_sequence_id NUMBER
492 , p_inventory_item_id NUMBER
493 , p_organization_id NUMBER
494 , p_subinventory_name VARCHAR2
495 , p_supply_status IN OUT NOCOPY NUMBER
496 , p_card_status IN OUT NOCOPY NUMBER
497 , p_kanban_card_type NUMBER
498 , p_source_type NUMBER
499 , p_kanban_size NUMBER
500 , p_last_update_date DATE
501 , p_last_updated_by NUMBER
502 , p_creation_date DATE
503 , p_created_by NUMBER
504 , p_last_update_login NUMBER
505 , p_last_print_date DATE
506 , p_locator_id NUMBER
507 , p_supplier_id NUMBER
508 , p_supplier_site_id NUMBER
509 , p_source_organization_id NUMBER
510 , p_source_subinventory VARCHAR2
511 , p_source_locator_id NUMBER
512 , p_wip_line_id NUMBER
513 , p_current_replnsh_cycle_id IN OUT NOCOPY NUMBER
514 , p_document_type NUMBER
515 , p_document_header_id NUMBER
516 , p_document_detail_id NUMBER
517 , p_error_code NUMBER
518 , p_attribute_category VARCHAR2
519 , p_attribute1 VARCHAR2
520 , p_attribute2 VARCHAR2
521 , p_attribute3 VARCHAR2
522 , p_attribute4 VARCHAR2
523 , p_attribute5 VARCHAR2
524 , p_attribute6 VARCHAR2
525 , p_attribute7 VARCHAR2
526 , p_attribute8 VARCHAR2
527 , p_attribute9 VARCHAR2
528 , p_attribute10 VARCHAR2
529 , p_attribute11 VARCHAR2
530 , p_attribute12 VARCHAR2
531 , p_attribute13 VARCHAR2
532 , p_attribute14 VARCHAR2
533 , p_attribute15 VARCHAR2
534 , p_request_id NUMBER
535 , p_program_application_id NUMBER
536 , p_program_id NUMBER
537 , p_program_update_date DATE
538 , p_release_kanban_flag NUMBER
539 ) IS
540 l_kanban_card_rec inv_kanban_pvt.kanban_card_rec_type;
541 l_kanban_card_number_ok BOOLEAN := FALSE;
542 l_dummy VARCHAR2(1);
543 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
544 l_current_replnsh_cycle_id NUMBER;
545 l_card_status NUMBER;
546 l_supply_status NUMBER;
547 BEGIN
548 fnd_msg_pub.initialize;
549 INV_LOG_UTIL.TRACE('561 Before Callin INSERT INTO mtl_kanban_cards','insert_row');
550 WHILE NOT l_kanban_card_number_ok LOOP
551 SELECT mtl_kanban_cards_s.NEXTVAL
552 INTO l_kanban_card_rec.kanban_card_id
553 FROM DUAL;
554
555 IF p_kanban_card_number IS NULL THEN
556 BEGIN
557 SELECT 'X'
558 INTO l_dummy
559 FROM mtl_kanban_cards
560 WHERE kanban_card_number = TO_CHAR(l_kanban_card_rec.kanban_card_id)
561 AND organization_id = p_organization_id;
562 EXCEPTION
563 WHEN NO_DATA_FOUND THEN
564 l_kanban_card_number_ok := TRUE;
565 END;
566 ELSE
567 l_kanban_card_number_ok := TRUE;
568 END IF;
569 END LOOP;
570
571 IF p_kanban_card_number IS NULL THEN
572 if nvl(fnd_profile.VALUE('FLM_EKB_OPS_FLAG'),0) = 1 then
573 l_kanban_card_rec.kanban_card_number := FLM_KANBAN_CUSTOM_PKG.custom_kanban_number(p_pull_sequence_id);
574 -- Also Validate Value from Hook.
575 if l_kanban_card_rec.kanban_card_number is not null then
576 Begin
577 select 1 into l_dummy
578 from mtl_kanban_cards
579 where organization_id = l_kanban_card_rec.organization_id
580 and kanban_card_number = l_kanban_card_rec.kanban_card_number;
581 l_kanban_card_rec.kanban_card_number:=NULL;
582 exception
583 when others then
584 l_kanban_card_rec.kanban_card_number:=NULL;
585 end;
586 end if;
587 end if;
588 --Modified For Bug 12419870.
589 IF l_kanban_card_rec.kanban_card_number IS NULL or l_kanban_card_rec.kanban_card_number = FND_API.G_MISS_CHAR THEN
590 l_kanban_card_rec.kanban_card_number := TO_CHAR(l_kanban_card_rec.kanban_card_id);
591 end if;
592 ELSE
593 l_kanban_card_rec.kanban_card_number := p_kanban_card_number;
594 END IF;
595
596 l_kanban_card_rec.pull_sequence_id := p_pull_sequence_id;
597 l_kanban_card_rec.inventory_item_id := p_inventory_item_id;
598 l_kanban_card_rec.organization_id := p_organization_id;
599 l_kanban_card_rec.subinventory_name := p_subinventory_name;
600 l_kanban_card_rec.supply_status := p_supply_status;
601 l_kanban_card_rec.card_status := p_card_status;
602 l_kanban_card_rec.kanban_card_type := p_kanban_card_type;
603 l_kanban_card_rec.source_type := p_source_type;
604 l_kanban_card_rec.kanban_size := p_kanban_size;
605 l_kanban_card_rec.last_update_date := p_last_update_date;
606 l_kanban_card_rec.last_updated_by := p_last_updated_by;
607 l_kanban_card_rec.creation_date := p_creation_date;
608 l_kanban_card_rec.created_by := p_created_by;
609 l_kanban_card_rec.last_update_login := p_last_update_login;
610 l_kanban_card_rec.last_print_date := p_last_print_date;
611 l_kanban_card_rec.locator_id := p_locator_id;
612 l_kanban_card_rec.supplier_id := p_supplier_id;
613 l_kanban_card_rec.supplier_site_id := p_supplier_site_id;
614 l_kanban_card_rec.source_organization_id := p_source_organization_id;
615 l_kanban_card_rec.source_subinventory := p_source_subinventory;
616 l_kanban_card_rec.source_locator_id := p_source_locator_id;
617 l_kanban_card_rec.wip_line_id := p_wip_line_id;
618 l_kanban_card_rec.current_replnsh_cycle_id := p_current_replnsh_cycle_id;
619 l_kanban_card_rec.document_type := p_document_type;
620 l_kanban_card_rec.document_header_id := p_document_header_id;
621 l_kanban_card_rec.document_detail_id := p_document_detail_id;
622 l_kanban_card_rec.ERROR_CODE := p_error_code;
623 l_kanban_card_rec.attribute_category := p_attribute_category;
624 l_kanban_card_rec.attribute1 := p_attribute1;
625 l_kanban_card_rec.attribute2 := p_attribute2;
626 l_kanban_card_rec.attribute3 := p_attribute3;
627 l_kanban_card_rec.attribute4 := p_attribute4;
628 l_kanban_card_rec.attribute5 := p_attribute5;
629 l_kanban_card_rec.attribute6 := p_attribute6;
630 l_kanban_card_rec.attribute7 := p_attribute7;
631 l_kanban_card_rec.attribute8 := p_attribute8;
632 l_kanban_card_rec.attribute9 := p_attribute9;
633 l_kanban_card_rec.attribute10 := p_attribute10;
634 l_kanban_card_rec.attribute11 := p_attribute11;
635 l_kanban_card_rec.attribute12 := p_attribute12;
636 l_kanban_card_rec.attribute13 := p_attribute13;
637 l_kanban_card_rec.attribute14 := p_attribute14;
638 l_kanban_card_rec.attribute15 := p_attribute15;
639 l_kanban_card_rec.request_id := p_request_id;
640 l_kanban_card_rec.program_application_id := p_program_application_id;
641 l_kanban_card_rec.program_id := p_program_id;
642 l_kanban_card_rec.program_update_date := p_program_update_date;
643
644 INSERT INTO mtl_kanban_cards
645 (
646 kanban_card_id
647 , kanban_card_number
648 , pull_sequence_id
649 , inventory_item_id
650 , organization_id
651 , subinventory_name
652 , supply_status
653 , card_status
654 , kanban_card_type
655 , source_type
656 , kanban_size
657 , last_update_date
658 , last_updated_by
659 , creation_date
660 , created_by
661 , last_update_login
662 , last_print_date
663 , locator_id
664 , supplier_id
665 , supplier_site_id
666 , source_organization_id
667 , source_subinventory
668 , source_locator_id
669 , wip_line_id
670 , current_replnsh_cycle_id
671 , ERROR_CODE
672 , attribute_category
673 , attribute1
674 , attribute2
675 , attribute3
676 , attribute4
677 , attribute5
678 , attribute6
679 , attribute7
680 , attribute8
681 , attribute9
682 , attribute10
683 , attribute11
684 , attribute12
685 , attribute13
686 , attribute14
687 , attribute15
688 , request_id
689 , program_application_id
690 , program_id
691 , program_update_date
692 )
693 VALUES (
694 l_kanban_card_rec.kanban_card_id
695 , l_kanban_card_rec.kanban_card_number
696 , l_kanban_card_rec.pull_sequence_id
697 , l_kanban_card_rec.inventory_item_id
698 , l_kanban_card_rec.organization_id
699 , l_kanban_card_rec.subinventory_name
700 , DECODE(
701 l_kanban_card_rec.supply_status
702 , inv_kanban_pvt.g_supply_status_empty, inv_kanban_pvt.g_supply_status_new
703 , l_kanban_card_rec.supply_status
704 )
705 , l_kanban_card_rec.card_status
706 , l_kanban_card_rec.kanban_card_type
707 , l_kanban_card_rec.source_type
708 , l_kanban_card_rec.kanban_size
709 , l_kanban_card_rec.last_update_date
710 , l_kanban_card_rec.last_updated_by
711 , l_kanban_card_rec.creation_date
712 , l_kanban_card_rec.created_by
713 , l_kanban_card_rec.last_update_login
714 , l_kanban_card_rec.last_print_date
715 , l_kanban_card_rec.locator_id
716 , l_kanban_card_rec.supplier_id
717 , l_kanban_card_rec.supplier_site_id
718 , l_kanban_card_rec.source_organization_id
719 , l_kanban_card_rec.source_subinventory
720 , l_kanban_card_rec.source_locator_id
721 , l_kanban_card_rec.wip_line_id
722 , l_kanban_card_rec.current_replnsh_cycle_id
723 , l_kanban_card_rec.ERROR_CODE
724 , l_kanban_card_rec.attribute_category
725 , l_kanban_card_rec.attribute1
726 , l_kanban_card_rec.attribute2
727 , l_kanban_card_rec.attribute3
728 , l_kanban_card_rec.attribute4
729 , l_kanban_card_rec.attribute5
730 , l_kanban_card_rec.attribute6
731 , l_kanban_card_rec.attribute7
732 , l_kanban_card_rec.attribute8
733 , l_kanban_card_rec.attribute9
734 , l_kanban_card_rec.attribute10
735 , l_kanban_card_rec.attribute11
736 , l_kanban_card_rec.attribute12
737 , l_kanban_card_rec.attribute13
738 , l_kanban_card_rec.attribute14
739 , l_kanban_card_rec.attribute15
740 , l_kanban_card_rec.request_id
741 , l_kanban_card_rec.program_application_id
742 , l_kanban_card_rec.program_id
743 , l_kanban_card_rec.program_update_date
744 );
745
746 IF (
747 (
748 (l_kanban_card_rec.card_status = inv_kanban_pvt.g_card_status_active)
749 AND(l_kanban_card_rec.supply_status = inv_kanban_pvt.g_supply_status_empty)
750 AND(p_release_kanban_flag = 1)
751 )
752 OR(p_release_kanban_flag = 2)
753 ) THEN
754 l_supply_status := l_kanban_card_rec.supply_status;
755 l_card_status := l_kanban_card_rec.card_status;
756 l_current_replnsh_cycle_id := l_kanban_card_rec.current_replnsh_cycle_id;
757 inv_kanbancard_pkg.update_row(
758 x_return_status => l_return_status
759 , p_kanban_card_id => l_kanban_card_rec.kanban_card_id
760 , p_kanban_card_number => l_kanban_card_rec.kanban_card_number
761 , p_pull_sequence_id => l_kanban_card_rec.pull_sequence_id
762 , p_inventory_item_id => l_kanban_card_rec.inventory_item_id
763 , p_organization_id => l_kanban_card_rec.organization_id
764 , p_subinventory_name => l_kanban_card_rec.subinventory_name
765 , p_supply_status => l_supply_status
766 , p_card_status => l_card_status
767 , p_kanban_card_type => l_kanban_card_rec.kanban_card_type
768 , p_source_type => l_kanban_card_rec.source_type
769 , p_kanban_size => l_kanban_card_rec.kanban_size
770 , p_last_update_date => l_kanban_card_rec.last_update_date
771 , p_last_updated_by => l_kanban_card_rec.last_updated_by
772 , p_creation_date => l_kanban_card_rec.creation_date
773 , p_created_by => l_kanban_card_rec.created_by
774 , p_last_update_login => l_kanban_card_rec.last_update_login
775 , p_last_print_date => l_kanban_card_rec.last_print_date
776 , p_locator_id => l_kanban_card_rec.locator_id
777 , p_supplier_id => l_kanban_card_rec.supplier_id
778 , p_supplier_site_id => l_kanban_card_rec.supplier_site_id
779 , p_source_organization_id => l_kanban_card_rec.source_organization_id
780 , p_source_subinventory => l_kanban_card_rec.source_subinventory
781 , p_source_locator_id => l_kanban_card_rec.source_locator_id
782 , p_wip_line_id => l_kanban_card_rec.wip_line_id
783 , p_current_replnsh_cycle_id => l_current_replnsh_cycle_id
784 , p_document_type => l_kanban_card_rec.document_type
785 , p_document_header_id => l_kanban_card_rec.document_header_id
786 , p_document_detail_id => l_kanban_card_rec.document_detail_id
787 , p_error_code => l_kanban_card_rec.ERROR_CODE
788 , p_attribute_category => l_kanban_card_rec.attribute_category
789 , p_attribute1 => l_kanban_card_rec.attribute1
790 , p_attribute2 => l_kanban_card_rec.attribute2
791 , p_attribute3 => l_kanban_card_rec.attribute3
792 , p_attribute4 => l_kanban_card_rec.attribute4
793 , p_attribute5 => l_kanban_card_rec.attribute5
794 , p_attribute6 => l_kanban_card_rec.attribute6
795 , p_attribute7 => l_kanban_card_rec.attribute7
796 , p_attribute8 => l_kanban_card_rec.attribute8
797 , p_attribute9 => l_kanban_card_rec.attribute9
798 , p_attribute10 => l_kanban_card_rec.attribute10
799 , p_attribute11 => l_kanban_card_rec.attribute11
800 , p_attribute12 => l_kanban_card_rec.attribute12
801 , p_attribute13 => l_kanban_card_rec.attribute13
802 , p_attribute14 => l_kanban_card_rec.attribute14
803 , p_attribute15 => l_kanban_card_rec.attribute15
804 );
805 l_kanban_card_rec.supply_status := l_supply_status;
806 l_kanban_card_rec.card_status := l_card_status;
807 l_kanban_card_rec.current_replnsh_cycle_id := l_current_replnsh_cycle_id;
808 ELSE
809 insert_activity_for_card(l_kanban_card_rec);
810 END IF;
811
812 IF l_return_status = fnd_api.g_ret_sts_error THEN
813 RAISE fnd_api.g_exc_error;
814 END IF;
815
816 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
817 RAISE fnd_api.g_exc_unexpected_error;
818 END IF;
819
820 x_return_status := l_return_status;
821 p_kanban_card_number := l_kanban_card_rec.kanban_card_number;
822 p_kanban_card_id := l_kanban_card_rec.kanban_card_id;
823 p_supply_status := l_kanban_card_rec.supply_status;
824 p_card_status := l_kanban_card_rec.card_status;
825 p_current_replnsh_cycle_id := l_kanban_card_rec.current_replnsh_cycle_id;
826 EXCEPTION
827 WHEN fnd_api.g_exc_error THEN
828 x_return_status := fnd_api.g_ret_sts_error;
829 WHEN fnd_api.g_exc_unexpected_error THEN
830 x_return_status := fnd_api.g_ret_sts_unexp_error;
831 WHEN OTHERS THEN
832 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
833 fnd_msg_pub.add_exc_msg(g_pkg_name, 'Insert_Row');
834 END IF;
835
836 x_return_status := fnd_api.g_ret_sts_unexp_error;
837 END insert_row;
838
839 PROCEDURE lock_row(
840 p_kanban_card_id NUMBER
841 , p_kanban_card_number VARCHAR2
842 , p_pull_sequence_id NUMBER
843 , p_inventory_item_id NUMBER
844 , p_organization_id NUMBER
845 , p_subinventory_name VARCHAR2
846 , p_supply_status NUMBER
847 , p_card_status NUMBER
848 , p_kanban_card_type NUMBER
849 , p_source_type NUMBER
850 , p_kanban_size NUMBER
851 , p_last_print_date DATE
852 , p_locator_id NUMBER
853 , p_supplier_id NUMBER
854 , p_supplier_site_id NUMBER
855 , p_source_organization_id NUMBER
856 , p_source_subinventory VARCHAR2
857 , p_source_locator_id NUMBER
858 , p_wip_line_id NUMBER
859 , p_current_replnsh_cycle_id NUMBER
860 , p_error_code NUMBER
861 , p_attribute_category VARCHAR2
862 , p_attribute1 VARCHAR2
863 , p_attribute2 VARCHAR2
864 , p_attribute3 VARCHAR2
865 , p_attribute4 VARCHAR2
866 , p_attribute5 VARCHAR2
867 , p_attribute6 VARCHAR2
868 , p_attribute7 VARCHAR2
869 , p_attribute8 VARCHAR2
870 , p_attribute9 VARCHAR2
871 , p_attribute10 VARCHAR2
872 , p_attribute11 VARCHAR2
873 , p_attribute12 VARCHAR2
874 , p_attribute13 VARCHAR2
875 , p_attribute14 VARCHAR2
876 , p_attribute15 VARCHAR2
877 ) IS
878 CURSOR get_current_row IS
879 SELECT *
880 FROM mtl_kanban_cards
881 WHERE kanban_card_id = p_kanban_card_id
882 FOR UPDATE OF organization_id NOWAIT;
883
884 recinfo mtl_kanban_cards%ROWTYPE;
885 record_changed EXCEPTION;
886 BEGIN
887 OPEN get_current_row;
888 FETCH get_current_row INTO recinfo;
889
890 IF (get_current_row%NOTFOUND) THEN
891 CLOSE get_current_row;
892 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
893 app_exception.raise_exception;
894 END IF;
895
896 CLOSE get_current_row;
897
898 IF NOT(
899 recinfo.kanban_card_number = p_kanban_card_number
900 AND recinfo.pull_sequence_id = p_pull_sequence_id
901 AND recinfo.inventory_item_id = p_inventory_item_id
902 AND recinfo.organization_id = p_organization_id
903 AND recinfo.subinventory_name = p_subinventory_name
904 AND recinfo.supply_status = p_supply_status
905 AND recinfo.card_status = p_card_status
906 AND recinfo.kanban_card_type = p_kanban_card_type
907 AND recinfo.source_type = p_source_type
908 AND recinfo.kanban_size = p_kanban_size
909 AND((recinfo.last_print_date = p_last_print_date)
910 OR(recinfo.last_print_date IS NULL
911 AND p_last_print_date IS NULL))
912 AND((recinfo.locator_id = p_locator_id)
913 OR(recinfo.locator_id IS NULL
914 AND p_locator_id IS NULL))
915 AND((recinfo.supplier_id = p_supplier_id)
916 OR(recinfo.supplier_id IS NULL
917 AND p_supplier_id IS NULL))
918 AND((recinfo.supplier_site_id = p_supplier_site_id)
919 OR(recinfo.supplier_site_id IS NULL
920 AND p_supplier_site_id IS NULL))
921 AND(
922 (recinfo.source_organization_id = p_source_organization_id)
923 OR(recinfo.source_organization_id IS NULL
924 AND p_source_organization_id IS NULL)
925 )
926 AND(
927 (recinfo.source_subinventory = p_source_subinventory)
928 OR(recinfo.source_subinventory IS NULL
929 AND p_source_subinventory IS NULL)
930 )
931 AND((recinfo.source_locator_id = p_source_locator_id)
932 OR(recinfo.source_locator_id IS NULL
933 AND p_source_locator_id IS NULL))
934 AND((recinfo.wip_line_id = p_wip_line_id)
935 OR(recinfo.wip_line_id IS NULL
936 AND p_wip_line_id IS NULL))
937 AND((recinfo.ERROR_CODE = p_error_code)
938 OR(recinfo.ERROR_CODE IS NULL
939 AND p_error_code IS NULL))
940 AND((recinfo.attribute_category = p_attribute_category)
941 OR(recinfo.attribute_category IS NULL
942 AND p_attribute_category IS NULL))
943 ) THEN
944 RAISE record_changed;
945 END IF;
946
947 IF NOT(
948 ((recinfo.attribute1 = p_attribute1)
949 OR((recinfo.attribute1 IS NULL)
950 AND(p_attribute1 IS NULL)))
951 AND((recinfo.attribute2 = p_attribute2)
952 OR((recinfo.attribute2 IS NULL)
953 AND(p_attribute2 IS NULL)))
954 AND((recinfo.attribute3 = p_attribute3)
955 OR((recinfo.attribute3 IS NULL)
956 AND(p_attribute3 IS NULL)))
957 AND((recinfo.attribute4 = p_attribute4)
958 OR((recinfo.attribute4 IS NULL)
959 AND(p_attribute4 IS NULL)))
960 AND((recinfo.attribute5 = p_attribute5)
961 OR((recinfo.attribute5 IS NULL)
962 AND(p_attribute5 IS NULL)))
963 AND((recinfo.attribute6 = p_attribute6)
964 OR((recinfo.attribute6 IS NULL)
965 AND(p_attribute6 IS NULL)))
966 AND((recinfo.attribute7 = p_attribute7)
967 OR((recinfo.attribute7 IS NULL)
968 AND(p_attribute7 IS NULL)))
969 AND((recinfo.attribute8 = p_attribute8)
970 OR((recinfo.attribute8 IS NULL)
971 AND(p_attribute8 IS NULL)))
972 AND((recinfo.attribute9 = p_attribute9)
973 OR((recinfo.attribute9 IS NULL)
974 AND(p_attribute9 IS NULL)))
975 AND((recinfo.attribute10 = p_attribute10)
976 OR((recinfo.attribute10 IS NULL)
977 AND(p_attribute10 IS NULL)))
978 AND((recinfo.attribute11 = p_attribute11)
979 OR((recinfo.attribute11 IS NULL)
980 AND(p_attribute11 IS NULL)))
981 AND((recinfo.attribute12 = p_attribute12)
982 OR((recinfo.attribute12 IS NULL)
983 AND(p_attribute12 IS NULL)))
984 AND((recinfo.attribute13 = p_attribute13)
985 OR((recinfo.attribute13 IS NULL)
986 AND(p_attribute13 IS NULL)))
987 AND((recinfo.attribute14 = p_attribute14)
988 OR((recinfo.attribute14 IS NULL)
989 AND(p_attribute14 IS NULL)))
990 AND((recinfo.attribute15 = p_attribute15)
991 OR((recinfo.attribute15 IS NULL)
992 AND(p_attribute15 IS NULL)))
993 ) THEN
994 RAISE record_changed;
995 END IF;
996 EXCEPTION
997 WHEN record_changed THEN
998 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
999 app_exception.raise_exception;
1000 WHEN OTHERS THEN
1001 RAISE;
1002 END lock_row;
1003
1004
1005 --13683149
1006 Function status_check(from_status_id in number,
1007 to_status in number)
1008 RETURN number IS
1009
1010 x number ;
1011 col1 varchar2(40) := ('2222111');
1012 col2 varchar2(40) := ('1222111');
1013 col3 varchar2(40) := ('1122111');
1014 col4 varchar2(40) := ('1212211');
1015 col5 varchar2(40) := ('1211222');
1016 col6 varchar2(40) := ('1211121');
1017 col7 varchar2(40) := ('1211112');
1018 BEGIN
1019
1020 IF from_status_id = 1 THEN
1021 x := substr(col1,to_status,1);
1022 ELSIF from_status_id = 2 THEN
1023 x := substr(col2,to_status,1);
1024 ELSIF from_status_id= 3 THEN
1025 x := substr(col3,to_status,1);
1026 ELSIF from_status_id= 4 THEN
1027 x := substr(col4,to_status,1);
1028 ELSIF from_status_id = 5 THEN
1029 x := substr(col5,to_status,1);
1030 ELSIF from_status_id= 6 THEN
1031 x := substr(col6,to_status,1);
1032 ELSIF from_status_id= 7 THEN
1033 x := substr(col7,to_status,1);
1034 END IF;
1035 RETURN(x);
1036
1037 END ;
1038
1039 -- Start eKanban Changes.
1040 PROCEDURE update_row(
1041 x_return_status OUT NOCOPY VARCHAR2
1042 , p_kanban_card_id NUMBER
1043 , p_kanban_card_number VARCHAR2
1044 , p_pull_sequence_id NUMBER
1045 , p_inventory_item_id NUMBER
1046 , p_organization_id NUMBER
1047 , p_subinventory_name VARCHAR2
1048 , p_supply_status IN OUT NOCOPY NUMBER
1049 , p_card_status IN OUT NOCOPY NUMBER
1050 , p_kanban_card_type NUMBER
1051 , p_source_type NUMBER
1052 , p_kanban_size NUMBER
1053 , p_last_update_date DATE
1054 , p_last_updated_by NUMBER
1055 , p_creation_date DATE
1056 , p_created_by NUMBER
1057 , p_last_update_login NUMBER
1058 , p_last_print_date DATE
1059 , p_locator_id NUMBER
1060 , p_supplier_id NUMBER
1061 , p_supplier_site_id NUMBER
1062 , p_source_organization_id NUMBER
1063 , p_source_subinventory VARCHAR2
1064 , p_source_locator_id NUMBER
1065 , p_wip_line_id NUMBER
1066 , p_current_replnsh_cycle_id IN OUT NOCOPY NUMBER
1067 , p_document_type NUMBER
1068 , p_document_header_id NUMBER
1069 , p_document_detail_id NUMBER
1070 , p_error_code NUMBER
1071 , p_attribute_category VARCHAR2
1072 , p_attribute1 VARCHAR2
1073 , p_attribute2 VARCHAR2
1074 , p_attribute3 VARCHAR2
1075 , p_attribute4 VARCHAR2
1076 , p_attribute5 VARCHAR2
1077 , p_attribute6 VARCHAR2
1078 , p_attribute7 VARCHAR2
1079 , p_attribute8 VARCHAR2
1080 , p_attribute9 VARCHAR2
1081 , p_attribute10 VARCHAR2
1082 , p_attribute11 VARCHAR2
1083 , p_attribute12 VARCHAR2
1084 , p_attribute13 VARCHAR2
1085 , p_attribute14 VARCHAR2
1086 , p_attribute15 VARCHAR2
1087 , p_lot_item_id NUMBER DEFAULT NULL
1088 , p_lot_number VARCHAR2 DEFAULT NULL
1089 , p_lot_item_revision VARCHAR2 DEFAULT NULL
1090 , p_lot_subinventory_code VARCHAR2 DEFAULT NULL
1091 , p_lot_location_id NUMBER DEFAULT NULL
1092 , p_lot_quantity NUMBER DEFAULT NULL
1093 , p_replenish_quantity NUMBER DEFAULT NULL
1094 , p_need_by_date DATE DEFAULT NULL
1095 , p_source_wip_entity_id NUMBER DEFAULT NULL
1096 --eKanban changes
1097 , p_replenishment_count NUMBER DEFAULT NULL
1098 , p_max_replenishments NUMBER DEFAULT NULL
1099 , p_disable_date DATE DEFAULT NULL
1100 , p_replacement_flag NUMBER DEFAULT NULL
1101 ) IS
1102 l_kanban_card_rec inv_kanban_pvt.kanban_card_rec_type;
1103 l_old_kanban_card_rec inv_kanban_pvt.kanban_card_rec_type;
1104 l_current_replenish_cycle_id NUMBER;
1105 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
1106 l_supply_status NUMBER;
1107 l_consolidation_option NUMBER := null;
1108 l_status_check BOOLEAN; --ekanban changes by javakat
1109 --add for Ekanban business event
1110 l_msg_data VARCHAR2(1000);
1111
1112 BEGIN
1113 fnd_msg_pub.initialize;
1114 mydebug('Inside new eKanban update_row ');
1115 l_old_kanban_card_rec := query_row(p_kanban_card_id => p_kanban_card_id);
1116 l_kanban_card_rec.kanban_card_id := p_kanban_card_id;
1117 l_kanban_card_rec.kanban_card_number := p_kanban_card_number;
1118 l_kanban_card_rec.pull_sequence_id := p_pull_sequence_id;
1119 l_kanban_card_rec.inventory_item_id := p_inventory_item_id;
1120 l_kanban_card_rec.organization_id := p_organization_id;
1121 l_kanban_card_rec.subinventory_name := p_subinventory_name;
1122 l_kanban_card_rec.kanban_card_type := p_kanban_card_type;
1123 l_kanban_card_rec.supply_status := p_supply_status;
1124 --ekanban changes by javakat
1125 /*
1126 IF (l_old_kanban_card_rec.supply_status != p_supply_status) THEN
1127 --Calling the override method javakat
1128 l_status_check:=supply_status_change_ok(p_kanban_card_id ,l_old_kanban_card_rec.supply_status, p_supply_status,l_kanban_card_rec.card_status);
1129 IF l_status_check =FALSE THEN
1130 mydebug('In side the chck l_old_kanban_card_rec.supply_status != p_supply_status ');
1131 RAISE fnd_api.g_exc_error;
1132 END IF;
1133 END IF;
1134 */
1135
1136 IF (l_kanban_card_rec.kanban_card_type = inv_kanban_pvt.g_card_type_nonreplenishable)
1137 AND(trunc(l_kanban_card_rec.supply_status) = inv_kanban_pvt.g_supply_status_full)
1138 AND(trunc(l_old_kanban_card_rec.supply_status) <> trunc(l_kanban_card_rec.supply_status)) THEN
1139 l_kanban_card_rec.card_status := inv_kanban_pvt.g_card_status_hold;
1140 ELSE
1141 l_kanban_card_rec.card_status := p_card_status;
1142 END IF;
1143
1144 l_kanban_card_rec.source_type := p_source_type;
1145 l_kanban_card_rec.kanban_size := p_kanban_size;
1146 l_kanban_card_rec.last_update_date := p_last_update_date;
1147 l_kanban_card_rec.last_updated_by := p_last_updated_by;
1148 l_kanban_card_rec.creation_date := p_creation_date;
1149 l_kanban_card_rec.created_by := p_created_by;
1150 l_kanban_card_rec.last_update_login := p_last_update_login;
1151 l_kanban_card_rec.last_print_date := p_last_print_date;
1152 l_kanban_card_rec.locator_id := p_locator_id;
1153 l_kanban_card_rec.supplier_id := p_supplier_id;
1154 l_kanban_card_rec.supplier_site_id := p_supplier_site_id;
1155 l_kanban_card_rec.source_organization_id := p_source_organization_id;
1156 l_kanban_card_rec.source_subinventory := p_source_subinventory;
1157 l_kanban_card_rec.source_locator_id := p_source_locator_id;
1158 l_kanban_card_rec.wip_line_id := p_wip_line_id;
1159 l_kanban_card_rec.document_type := p_document_type;
1160 l_kanban_card_rec.document_header_id := p_document_header_id;
1161 l_kanban_card_rec.document_detail_id := p_document_detail_id;
1162 --eKanban changes
1163 l_kanban_card_rec.replenishment_count := p_replenishment_count;
1164 l_kanban_card_rec.max_replenishments := p_max_replenishments;
1165 l_kanban_card_rec.disable_date := p_disable_date;
1166 l_kanban_card_rec.replacement_flag := p_replacement_flag;
1167
1168
1169 IF trunc(p_supply_status) = inv_kanban_pvt.g_supply_status_full THEN
1170 l_kanban_card_rec.current_replnsh_cycle_id := NULL;
1171 ELSE
1172 l_kanban_card_rec.current_replnsh_cycle_id := p_current_replnsh_cycle_id;
1173 END IF;
1174
1175 l_kanban_card_rec.ERROR_CODE := p_error_code;
1176 l_kanban_card_rec.attribute_category := p_attribute_category;
1177 l_kanban_card_rec.attribute1 := p_attribute1;
1178 l_kanban_card_rec.attribute2 := p_attribute2;
1179 l_kanban_card_rec.attribute3 := p_attribute3;
1180 l_kanban_card_rec.attribute4 := p_attribute4;
1181 l_kanban_card_rec.attribute5 := p_attribute5;
1182 l_kanban_card_rec.attribute6 := p_attribute6;
1183 l_kanban_card_rec.attribute7 := p_attribute7;
1184 l_kanban_card_rec.attribute8 := p_attribute8;
1185 l_kanban_card_rec.attribute9 := p_attribute9;
1186 l_kanban_card_rec.attribute10 := p_attribute10;
1187 l_kanban_card_rec.attribute11 := p_attribute11;
1188 l_kanban_card_rec.attribute12 := p_attribute12;
1189 l_kanban_card_rec.attribute13 := p_attribute13;
1190 l_kanban_card_rec.attribute14 := p_attribute14;
1191 l_kanban_card_rec.attribute15 := p_attribute15;
1192 l_kanban_card_rec.lot_item_id := p_lot_item_id;
1193 l_kanban_card_rec.lot_number := p_lot_number;
1194 l_kanban_card_rec.lot_subinventory_code := p_lot_subinventory_code;
1195 l_kanban_card_rec.lot_item_revision := p_lot_item_revision;
1196 l_kanban_card_rec.lot_location_id := p_lot_location_id;
1197 l_kanban_card_rec.lot_quantity := p_lot_quantity;
1198 l_kanban_card_rec.replenish_quantity := p_replenish_quantity;
1199 l_kanban_card_rec.need_by_date := p_need_by_date;
1200 l_kanban_card_rec.source_wip_entity_id := p_source_wip_entity_id;
1201
1202 IF l_kanban_card_rec.card_status = inv_kanban_pvt.g_card_status_active
1203 AND trunc(l_kanban_card_rec.supply_status) = inv_kanban_pvt.g_supply_status_empty
1204 AND trunc(l_old_kanban_card_rec.supply_status) <> trunc(l_kanban_card_rec.supply_status)
1205 AND status_check(l_old_kanban_card_rec.supply_status,l_kanban_card_rec.supply_status) = 2 --13683149
1206 THEN
1207 mydebug('calling INV_Kanban_PVT.Check_And_Create_Replenishment');
1208
1209 /* This validation should be done inside check_and_create_replenishment to accomodate consolidation. Bug 12570813.
1210 Begin
1211 select CONSOLIDATION into l_consolidation_option
1212 from MTL_KANBAN_PULL_SEQUENCES
1213 where pull_sequence_id = l_kanban_card_rec.pull_sequence_id;
1214 exception
1215 when no_data_found then
1216 null;
1217 end;
1218
1219 if nvl(l_consolidation_option,INV_Kanban_PVT.G_consolidate_opt_no) = INV_Kanban_PVT.G_consolidate_opt_yes then
1220 l_kanban_card_rec.supply_status := INV_Kanban_PVT.G_Supply_Status_Consolidate;
1221 else */
1222 inv_kanban_pvt.check_and_create_replenishment(
1223 x_return_status => l_return_status
1224 , x_supply_status => l_supply_status
1225 , x_current_replenish_cycle_id => l_current_replenish_cycle_id
1226 , p_kanban_card_rec => l_kanban_card_rec
1227 );
1228
1229 IF l_return_status = fnd_api.g_ret_sts_error THEN
1230 RAISE fnd_api.g_exc_error;
1231 END IF;
1232
1233 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1234 RAISE fnd_api.g_exc_unexpected_error;
1235 END IF;
1236
1237 l_kanban_card_rec.supply_status := l_supply_status;
1238 l_kanban_card_rec.current_replnsh_cycle_id := l_current_replenish_cycle_id;
1239 -- Added for Bug 12570813.
1240 if l_supply_status not in (INV_Kanban_PVT.G_Supply_Status_Wait,INV_Kanban_PVT.G_Supply_Status_Consolidate) then
1241 l_kanban_card_rec.replenishment_count := nvl(p_replenishment_count,0)+1;
1242 end if;
1243 -- end if;
1244 END IF;
1245
1246 UPDATE mtl_kanban_cards
1247 SET pull_sequence_id = l_kanban_card_rec.pull_sequence_id
1248 , inventory_item_id = l_kanban_card_rec.inventory_item_id
1249 , organization_id = l_kanban_card_rec.organization_id
1250 , subinventory_name = l_kanban_card_rec.subinventory_name
1251 , supply_status = l_kanban_card_rec.supply_status
1252 , card_status = l_kanban_card_rec.card_status
1253 , kanban_card_type = l_kanban_card_rec.kanban_card_type
1254 , source_type = l_kanban_card_rec.source_type
1255 , kanban_size = l_kanban_card_rec.kanban_size
1256 , last_update_date = l_kanban_card_rec.last_update_date
1257 , last_updated_by = l_kanban_card_rec.last_updated_by
1258 , creation_date = l_kanban_card_rec.creation_date
1259 , created_by = l_kanban_card_rec.created_by
1260 , last_update_login = l_kanban_card_rec.last_update_login
1261 , last_print_date = l_kanban_card_rec.last_print_date
1262 , locator_id = l_kanban_card_rec.locator_id
1263 , supplier_id = l_kanban_card_rec.supplier_id
1264 , supplier_site_id = l_kanban_card_rec.supplier_site_id
1265 , source_organization_id = l_kanban_card_rec.source_organization_id
1266 , source_subinventory = l_kanban_card_rec.source_subinventory
1267 , source_locator_id = l_kanban_card_rec.source_locator_id
1268 , wip_line_id = l_kanban_card_rec.wip_line_id
1269 , current_replnsh_cycle_id = l_kanban_card_rec.current_replnsh_cycle_id
1270 , ERROR_CODE = l_kanban_card_rec.ERROR_CODE
1271 , attribute_category = l_kanban_card_rec.attribute_category
1272 , attribute1 = l_kanban_card_rec.attribute1
1273 , attribute2 = l_kanban_card_rec.attribute2
1274 , attribute3 = l_kanban_card_rec.attribute3
1275 , attribute4 = l_kanban_card_rec.attribute4
1276 , attribute5 = l_kanban_card_rec.attribute5
1277 , attribute6 = l_kanban_card_rec.attribute6
1278 , attribute7 = l_kanban_card_rec.attribute7
1279 , attribute8 = l_kanban_card_rec.attribute8
1280 , attribute9 = l_kanban_card_rec.attribute9
1281 , attribute10 = l_kanban_card_rec.attribute10
1282 , attribute11 = l_kanban_card_rec.attribute11
1283 , attribute12 = l_kanban_card_rec.attribute12
1284 , attribute13 = l_kanban_card_rec.attribute13
1285 , attribute14 = l_kanban_card_rec.attribute14
1286 , attribute15 = l_kanban_card_rec.attribute15
1287 --eKanban changes
1288 , replenishment_count = l_kanban_card_rec.replenishment_count
1289 , max_replenishments = l_kanban_card_rec.max_replenishments
1290 , disable_date = l_kanban_card_rec.disable_date
1291 , replacement_flag = l_kanban_card_rec.replacement_flag
1292 WHERE kanban_card_id = p_kanban_card_id;
1293
1294 IF (SQL%NOTFOUND) THEN
1295 RAISE NO_DATA_FOUND;
1296 END IF;
1297
1298 IF changed_row(l_kanban_card_rec, l_old_kanban_card_rec) THEN
1299 insert_activity_for_card(l_kanban_card_rec);
1300 -- Bug Fix 4361921
1301 ELSIF l_kanban_card_rec.document_type = INV_KANBAN_PVT.G_Doc_type_lot_job and
1302 l_kanban_card_rec.document_header_id <>
1303 nvl( l_old_kanban_card_rec.document_header_id,-9999) and
1304 trunc(l_kanban_card_rec.supply_status) =
1305 INV_KANBAN_PVT.G_Supply_Status_InProcess THEN
1306 -- Bug Fix: 5344450
1307 -- Added the IF condition
1308 IF (p_replenish_quantity IS NULL) THEN
1309
1310 -- Retained old update statement.
1311 mydebug(' Updating the document header id with :' ||
1312 l_kanban_card_rec.document_header_id);
1313 UPDATE mtl_kanban_card_activity
1314 SET document_header_id = l_kanban_card_rec.document_header_id
1315 WHERE
1316 kanban_card_number = l_kanban_card_rec.kanban_card_number AND
1317 trunc(supply_status) = INV_KANBAN_PVT.G_Supply_Status_InProcess AND
1318 document_type = INV_KANBAN_PVT.G_Doc_type_lot_job AND
1319 replenishment_cycle_id = l_kanban_card_rec.current_replnsh_cycle_id;
1320 ELSE
1321 -- Bug Fix: 5344450
1322 -- Added new update statement to update the kanban_size with p_replenish_quantity
1323 mydebug(' Updating the document header id with :' || l_kanban_card_rec.document_header_id||
1324 ' kanban_size : '||p_replenish_quantity);
1325 UPDATE mtl_kanban_card_activity
1326 SET document_header_id = l_kanban_card_rec.document_header_id,
1327 kanban_size = p_replenish_quantity
1328 WHERE
1329 kanban_card_number = l_kanban_card_rec.kanban_card_number AND
1330 trunc(supply_status) = INV_KANBAN_PVT.G_Supply_Status_InProcess AND
1331 document_type = INV_KANBAN_PVT.G_Doc_type_lot_job AND
1332 replenishment_cycle_id = l_kanban_card_rec.current_replnsh_cycle_id;
1333
1334 END IF;
1335
1336 END IF;
1337
1338 x_return_status := l_return_status;
1339 p_supply_status := l_kanban_card_rec.supply_status;
1340 p_card_status := l_kanban_card_rec.card_status;
1341 p_current_replnsh_cycle_id := l_kanban_card_rec.current_replnsh_cycle_id;
1342 --raise business event for kanbanCardUpdation when ekanban is enabled
1343 if nvl(fnd_profile.VALUE('FLM_EKB_OPS_FLAG'),0) = 1 then
1344 FLM_EKB_BUSINESS_EVENT_PKG.Raise_Kanban_Card_Event(p_kanban_card_id => p_kanban_card_id,
1345 p_txn_type => 2,
1346 x_msg_data =>l_msg_data,
1347 x_return_status =>l_return_status);
1348 end if;
1349
1350 EXCEPTION
1351 WHEN fnd_api.g_exc_error THEN
1352 x_return_status := fnd_api.g_ret_sts_error;
1353 WHEN fnd_api.g_exc_unexpected_error THEN
1354 x_return_status := fnd_api.g_ret_sts_unexp_error;
1355 WHEN OTHERS THEN
1356 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1357 fnd_msg_pub.add_exc_msg(g_pkg_name, 'update_row');
1358 END IF;
1359
1360 x_return_status := fnd_api.g_ret_sts_unexp_error;
1361 END update_row;
1362
1363 PROCEDURE update_row(p_kanban_card_rec inv_kanban_pvt.kanban_card_rec_type) IS
1364 l_return_status VARCHAR2(1);
1365 l_supply_status NUMBER;
1366 l_card_status NUMBER;
1367 l_current_replnsh_cycle_id NUMBER;
1368 BEGIN
1369 fnd_msg_pub.initialize;
1370 mydebug('Inside update_row 1');
1371 l_supply_status := p_kanban_card_rec.supply_status;
1372 l_card_status := p_kanban_card_rec.card_status;
1373 l_current_replnsh_cycle_id := p_kanban_card_rec.current_replnsh_cycle_id;
1374 update_row(
1375 x_return_status => l_return_status
1376 , p_kanban_card_id => p_kanban_card_rec.kanban_card_id
1377 , p_kanban_card_number => p_kanban_card_rec.kanban_card_number
1378 , p_pull_sequence_id => p_kanban_card_rec.pull_sequence_id
1379 , p_inventory_item_id => p_kanban_card_rec.inventory_item_id
1380 , p_organization_id => p_kanban_card_rec.organization_id
1381 , p_subinventory_name => p_kanban_card_rec.subinventory_name
1382 , p_supply_status => l_supply_status
1383 , p_card_status => l_card_status
1384 , p_kanban_card_type => p_kanban_card_rec.kanban_card_type
1385 , p_source_type => p_kanban_card_rec.source_type
1386 , p_kanban_size => p_kanban_card_rec.kanban_size
1387 , p_last_update_date => p_kanban_card_rec.last_update_date
1388 , p_last_updated_by => p_kanban_card_rec.last_updated_by
1389 , p_creation_date => p_kanban_card_rec.creation_date
1390 , p_created_by => p_kanban_card_rec.created_by
1391 , p_last_update_login => p_kanban_card_rec.last_update_login
1392 , p_last_print_date => p_kanban_card_rec.last_print_date
1393 , p_locator_id => p_kanban_card_rec.locator_id
1394 , p_supplier_id => p_kanban_card_rec.supplier_id
1395 , p_supplier_site_id => p_kanban_card_rec.supplier_site_id
1396 , p_source_organization_id => p_kanban_card_rec.source_organization_id
1397 , p_source_subinventory => p_kanban_card_rec.source_subinventory
1398 , p_source_locator_id => p_kanban_card_rec.source_locator_id
1399 , p_wip_line_id => p_kanban_card_rec.wip_line_id
1400 , p_current_replnsh_cycle_id => l_current_replnsh_cycle_id
1401 , p_document_type => p_kanban_card_rec.document_type
1402 , p_document_header_id => p_kanban_card_rec.document_header_id
1403 , p_document_detail_id => p_kanban_card_rec.document_detail_id
1404 , p_error_code => p_kanban_card_rec.ERROR_CODE
1405 , p_attribute_category => p_kanban_card_rec.attribute_category
1406 , p_attribute1 => p_kanban_card_rec.attribute1
1407 , p_attribute2 => p_kanban_card_rec.attribute2
1408 , p_attribute3 => p_kanban_card_rec.attribute3
1409 , p_attribute4 => p_kanban_card_rec.attribute4
1410 , p_attribute5 => p_kanban_card_rec.attribute5
1411 , p_attribute6 => p_kanban_card_rec.attribute6
1412 , p_attribute7 => p_kanban_card_rec.attribute7
1413 , p_attribute8 => p_kanban_card_rec.attribute8
1414 , p_attribute9 => p_kanban_card_rec.attribute9
1415 , p_attribute10 => p_kanban_card_rec.attribute10
1416 , p_attribute11 => p_kanban_card_rec.attribute11
1417 , p_attribute12 => p_kanban_card_rec.attribute12
1418 , p_attribute13 => p_kanban_card_rec.attribute13
1419 , p_attribute14 => p_kanban_card_rec.attribute14
1420 , p_attribute15 => p_kanban_card_rec.attribute15
1421 , p_lot_item_id => p_kanban_card_rec.lot_item_id
1422 , p_lot_number => p_kanban_card_rec.lot_number
1423 , p_lot_subinventory_code => p_kanban_card_rec.lot_subinventory_code
1424 , p_lot_item_revision => p_kanban_card_rec.lot_item_revision
1425 , p_lot_location_id => p_kanban_card_rec.lot_location_id
1426 , p_lot_quantity => p_kanban_card_rec.lot_quantity
1427 , p_replenish_quantity => p_kanban_card_rec.replenish_quantity
1428 , p_need_by_date => p_kanban_card_rec.need_by_date
1429 , p_source_wip_entity_id => p_kanban_card_rec.source_wip_entity_id
1430 --eKanban Changes
1431 , p_replenishment_count => p_kanban_card_rec.replenishment_count
1432 , p_max_replenishments => p_kanban_card_rec.max_replenishments
1433 , p_disable_date => p_kanban_card_rec.disable_date
1434 , p_replacement_flag => p_kanban_card_rec.replacement_flag
1435 );
1436
1437 IF l_return_status = fnd_api.g_ret_sts_error THEN
1438 RAISE fnd_api.g_exc_error;
1439 END IF;
1440
1441 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1442 RAISE fnd_api.g_exc_unexpected_error;
1443 END IF;
1444 EXCEPTION
1445 WHEN fnd_api.g_exc_error THEN
1446 RAISE fnd_api.g_exc_error;
1447 WHEN fnd_api.g_exc_unexpected_error THEN
1448 RAISE fnd_api.g_exc_unexpected_error;
1449 WHEN OTHERS THEN
1450 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1451 fnd_msg_pub.add_exc_msg(g_pkg_name, 'Update_Row');
1452 END IF;
1453
1454 RAISE fnd_api.g_exc_unexpected_error;
1455 END update_row;
1456
1457 PROCEDURE update_card_status(p_kanban_card_rec IN OUT NOCOPY inv_kanban_pvt.kanban_card_rec_type, p_card_status IN NUMBER) IS
1458 l_return_status VARCHAR2(1);
1459 l_card_status NUMBER;
1460 BEGIN
1461 fnd_msg_pub.initialize;
1462 l_card_status := p_card_status;
1463 update_row(
1464 x_return_status => l_return_status
1465 , p_kanban_card_id => p_kanban_card_rec.kanban_card_id
1466 , p_kanban_card_number => p_kanban_card_rec.kanban_card_number
1467 , p_pull_sequence_id => p_kanban_card_rec.pull_sequence_id
1468 , p_inventory_item_id => p_kanban_card_rec.inventory_item_id
1469 , p_organization_id => p_kanban_card_rec.organization_id
1470 , p_subinventory_name => p_kanban_card_rec.subinventory_name
1471 , p_supply_status => p_kanban_card_rec.supply_status
1472 , p_card_status => l_card_status
1473 , p_kanban_card_type => p_kanban_card_rec.kanban_card_type
1474 , p_source_type => p_kanban_card_rec.source_type
1475 , p_kanban_size => p_kanban_card_rec.kanban_size
1476 , p_last_update_date => p_kanban_card_rec.last_update_date
1477 , p_last_updated_by => p_kanban_card_rec.last_updated_by
1478 , p_creation_date => p_kanban_card_rec.creation_date
1479 , p_created_by => p_kanban_card_rec.created_by
1480 , p_last_update_login => p_kanban_card_rec.last_update_login
1481 , p_last_print_date => p_kanban_card_rec.last_print_date
1482 , p_locator_id => p_kanban_card_rec.locator_id
1483 , p_supplier_id => p_kanban_card_rec.supplier_id
1484 , p_supplier_site_id => p_kanban_card_rec.supplier_site_id
1485 , p_source_organization_id => p_kanban_card_rec.source_organization_id
1486 , p_source_subinventory => p_kanban_card_rec.source_subinventory
1487 , p_source_locator_id => p_kanban_card_rec.source_locator_id
1488 , p_wip_line_id => p_kanban_card_rec.wip_line_id
1489 , p_current_replnsh_cycle_id => p_kanban_card_rec.current_replnsh_cycle_id
1490 , p_document_type => p_kanban_card_rec.document_type
1491 , p_document_header_id => p_kanban_card_rec.document_header_id
1492 , p_document_detail_id => p_kanban_card_rec.document_detail_id
1493 , p_error_code => p_kanban_card_rec.ERROR_CODE
1494 , p_attribute_category => p_kanban_card_rec.attribute_category
1495 , p_attribute1 => p_kanban_card_rec.attribute1
1496 , p_attribute2 => p_kanban_card_rec.attribute2
1497 , p_attribute3 => p_kanban_card_rec.attribute3
1498 , p_attribute4 => p_kanban_card_rec.attribute4
1499 , p_attribute5 => p_kanban_card_rec.attribute5
1500 , p_attribute6 => p_kanban_card_rec.attribute6
1501 , p_attribute7 => p_kanban_card_rec.attribute7
1502 , p_attribute8 => p_kanban_card_rec.attribute8
1503 , p_attribute9 => p_kanban_card_rec.attribute9
1504 , p_attribute10 => p_kanban_card_rec.attribute10
1505 , p_attribute11 => p_kanban_card_rec.attribute11
1506 , p_attribute12 => p_kanban_card_rec.attribute12
1507 , p_attribute13 => p_kanban_card_rec.attribute13
1508 , p_attribute14 => p_kanban_card_rec.attribute14
1509 , p_attribute15 => p_kanban_card_rec.attribute15
1510 );
1511
1512 IF l_return_status = fnd_api.g_ret_sts_error THEN
1513 RAISE fnd_api.g_exc_error;
1514 END IF;
1515
1516 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1517 RAISE fnd_api.g_exc_unexpected_error;
1518 END IF;
1519 EXCEPTION
1520 WHEN fnd_api.g_exc_error THEN
1521 RAISE fnd_api.g_exc_error;
1522 WHEN fnd_api.g_exc_unexpected_error THEN
1523 RAISE fnd_api.g_exc_unexpected_error;
1524 WHEN OTHERS THEN
1525 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1526 fnd_msg_pub.add_exc_msg(g_pkg_name, 'Update_Card_Status');
1527 END IF;
1528
1529 RAISE fnd_api.g_exc_unexpected_error;
1530 END update_card_status;
1531
1532 PROCEDURE delete_row(x_return_status OUT NOCOPY VARCHAR2, p_kanban_card_id NUMBER) IS
1533 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
1534 BEGIN
1535 DELETE FROM mtl_kanban_cards
1536 WHERE kanban_card_id = p_kanban_card_id;
1537
1538 IF (SQL%NOTFOUND) THEN
1539 RAISE NO_DATA_FOUND;
1540 END IF;
1541
1542 delete_activity_for_card(p_kanban_card_id);
1543 x_return_status := l_return_status;
1544 EXCEPTION
1545 WHEN fnd_api.g_exc_error THEN
1546 x_return_status := fnd_api.g_ret_sts_error;
1547 WHEN fnd_api.g_exc_unexpected_error THEN
1548 x_return_status := fnd_api.g_ret_sts_unexp_error;
1549 WHEN OTHERS THEN
1550 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1551 fnd_msg_pub.add_exc_msg(g_pkg_name, 'Delete_Row');
1552 END IF;
1553
1554 x_return_status := fnd_api.g_ret_sts_unexp_error;
1555 END delete_row;
1556
1557 PROCEDURE insert_activity_for_card(p_kanban_card_rec IN inv_kanban_pvt.kanban_card_rec_type) IS
1558 BEGIN
1559 INSERT INTO mtl_kanban_card_activity
1560 (
1561 kanban_activity_id
1562 , replenishment_cycle_id
1563 , kanban_card_id
1564 , kanban_card_number
1565 , inventory_item_id
1566 , organization_id
1567 , subinventory_name
1568 , supply_status
1569 , card_status
1570 , kanban_card_type
1571 , source_type
1572 , kanban_size
1573 , last_update_date
1574 , last_updated_by
1575 , creation_date
1576 , created_by
1577 , locator_id
1578 , supplier_id
1579 , supplier_site_id
1580 , source_organization_id
1581 , source_subinventory
1582 , source_locator_id
1583 , wip_line_id
1584 , document_type
1585 , document_header_id
1586 , document_detail_id
1587 , ERROR_CODE
1588 , last_update_login
1589 , last_print_date
1590 , attribute_category
1591 , attribute1
1592 , attribute2
1593 , attribute3
1594 , attribute4
1595 , attribute5
1596 , attribute6
1597 , attribute7
1598 , attribute8
1599 , attribute9
1600 , attribute10
1601 , attribute11
1602 , attribute12
1603 , attribute13
1604 , attribute14
1605 , attribute15
1606 , request_id
1607 , program_application_id
1608 , program_id
1609 , program_update_date
1610 , source_wip_entity_id
1611 )
1612 VALUES (
1613 mtl_kanban_card_activity_s.NEXTVAL
1614 , NVL(p_kanban_card_rec.current_replnsh_cycle_id, -1)
1615 , p_kanban_card_rec.kanban_card_id
1616 , p_kanban_card_rec.kanban_card_number
1617 , p_kanban_card_rec.inventory_item_id
1618 , p_kanban_card_rec.organization_id
1619 , p_kanban_card_rec.subinventory_name
1620 , p_kanban_card_rec.supply_status
1621 , p_kanban_card_rec.card_status
1622 , p_kanban_card_rec.kanban_card_type
1623 , p_kanban_card_rec.source_type
1624 , NVL(p_kanban_card_rec.replenish_quantity, p_kanban_card_rec.kanban_size)
1625 , SYSDATE
1626 , fnd_global.user_id
1627 , SYSDATE
1628 , fnd_global.user_id
1629 , p_kanban_card_rec.locator_id
1630 , p_kanban_card_rec.supplier_id
1631 , p_kanban_card_rec.supplier_site_id
1632 , p_kanban_card_rec.source_organization_id
1633 , p_kanban_card_rec.source_subinventory
1634 , p_kanban_card_rec.source_locator_id
1635 , p_kanban_card_rec.wip_line_id
1636 , p_kanban_card_rec.document_type
1637 , p_kanban_card_rec.document_header_id
1638 , p_kanban_card_rec.document_detail_id
1639 , p_kanban_card_rec.ERROR_CODE
1640 , fnd_global.login_id
1641 , p_kanban_card_rec.last_print_date
1642 , p_kanban_card_rec.attribute_category
1643 , p_kanban_card_rec.attribute1
1644 , p_kanban_card_rec.attribute2
1645 , p_kanban_card_rec.attribute3
1646 , p_kanban_card_rec.attribute4
1647 , p_kanban_card_rec.attribute5
1648 , p_kanban_card_rec.attribute6
1649 , p_kanban_card_rec.attribute7
1650 , p_kanban_card_rec.attribute8
1651 , p_kanban_card_rec.attribute9
1652 , p_kanban_card_rec.attribute10
1653 , p_kanban_card_rec.attribute11
1654 , p_kanban_card_rec.attribute12
1655 , p_kanban_card_rec.attribute13
1656 , p_kanban_card_rec.attribute14
1657 , p_kanban_card_rec.attribute15
1658 , p_kanban_card_rec.request_id
1659 , p_kanban_card_rec.program_application_id
1660 , p_kanban_card_rec.program_id
1661 , p_kanban_card_rec.program_update_date
1662 , p_kanban_card_rec.source_wip_entity_id
1663 );
1664 EXCEPTION
1665 WHEN fnd_api.g_exc_error THEN
1666 RAISE fnd_api.g_exc_error;
1667 WHEN OTHERS THEN
1668 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1669 fnd_msg_pub.add_exc_msg(g_pkg_name, 'Insert_Activity_For_Card');
1670 END IF;
1671
1672 RAISE fnd_api.g_exc_unexpected_error;
1673 END insert_activity_for_card;
1674
1675 PROCEDURE delete_cards_for_pull_seq(p_pull_sequence_id NUMBER) IS
1676 BEGIN
1677 inv_kanbancard_pkg.delete_activity_for_pull_seq(p_pull_sequence_id);
1678
1679 DELETE FROM mtl_kanban_cards
1680 WHERE pull_sequence_id = p_pull_sequence_id;
1681 EXCEPTION
1682 WHEN fnd_api.g_exc_error THEN
1683 RAISE fnd_api.g_exc_error;
1684 WHEN OTHERS THEN
1685 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1686 fnd_msg_pub.add_exc_msg(g_pkg_name, 'Delete_Cards_For_Pull_Seq');
1687 END IF;
1688
1689 RAISE fnd_api.g_exc_unexpected_error;
1690 END delete_cards_for_pull_seq;
1691
1692 PROCEDURE delete_activity_for_card(p_kanban_card_id NUMBER) IS
1693 BEGIN
1694 DELETE FROM mtl_kanban_card_activity
1695 WHERE kanban_card_id = p_kanban_card_id;
1696 EXCEPTION
1697 WHEN fnd_api.g_exc_error THEN
1698 RAISE fnd_api.g_exc_error;
1699 WHEN OTHERS THEN
1700 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1701 fnd_msg_pub.add_exc_msg(g_pkg_name, 'Delete_Activity_For_Card');
1702 END IF;
1703
1704 RAISE fnd_api.g_exc_unexpected_error;
1705 END delete_activity_for_card;
1706
1707 PROCEDURE delete_activity_for_pull_seq(p_pull_sequence_id NUMBER) IS
1708 BEGIN
1709 DELETE FROM mtl_kanban_card_activity act
1710 WHERE EXISTS(SELECT 'x'
1711 FROM mtl_kanban_cards crd
1712 WHERE crd.kanban_card_id = act.kanban_card_id
1713 AND crd.pull_sequence_id = p_pull_sequence_id);
1714 EXCEPTION
1715 WHEN fnd_api.g_exc_error THEN
1716 RAISE fnd_api.g_exc_error;
1717 WHEN OTHERS THEN
1718 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1719 fnd_msg_pub.add_exc_msg(g_pkg_name, 'Delete_Activity_For_Pull_Seq');
1720 END IF;
1721
1722 RAISE fnd_api.g_exc_unexpected_error;
1723 END delete_activity_for_pull_seq;
1724
1725
1726
1727 -- Start eKanban Changes.
1728
1729 PROCEDURE insert_row(
1730 x_return_status OUT NOCOPY VARCHAR2
1731 , p_kanban_card_id IN OUT NOCOPY NUMBER
1732 , p_kanban_card_number IN OUT NOCOPY VARCHAR2
1733 , p_pull_sequence_id NUMBER
1734 , p_inventory_item_id NUMBER
1735 , p_organization_id NUMBER
1736 , p_subinventory_name VARCHAR2
1737 , p_supply_status IN OUT NOCOPY NUMBER
1738 , p_card_status IN OUT NOCOPY NUMBER
1739 , p_kanban_card_type NUMBER
1740 , p_source_type NUMBER
1741 , p_kanban_size NUMBER
1742 , p_last_update_date DATE
1743 , p_last_updated_by NUMBER
1744 , p_creation_date DATE
1745 , p_created_by NUMBER
1746 , p_last_update_login NUMBER
1747 , p_last_print_date DATE
1748 , p_locator_id NUMBER
1749 , p_supplier_id NUMBER
1750 , p_supplier_site_id NUMBER
1751 , p_source_organization_id NUMBER
1752 , p_source_subinventory VARCHAR2
1753 , p_source_locator_id NUMBER
1754 , p_wip_line_id NUMBER
1755 , p_current_replnsh_cycle_id IN OUT NOCOPY NUMBER
1756 , p_document_type NUMBER
1757 , p_document_header_id NUMBER
1758 , p_document_detail_id NUMBER
1759 , p_error_code NUMBER
1760 , p_attribute_category VARCHAR2
1761 , p_attribute1 VARCHAR2
1762 , p_attribute2 VARCHAR2
1763 , p_attribute3 VARCHAR2
1764 , p_attribute4 VARCHAR2
1765 , p_attribute5 VARCHAR2
1766 , p_attribute6 VARCHAR2
1767 , p_attribute7 VARCHAR2
1768 , p_attribute8 VARCHAR2
1769 , p_attribute9 VARCHAR2
1770 , p_attribute10 VARCHAR2
1771 , p_attribute11 VARCHAR2
1772 , p_attribute12 VARCHAR2
1773 , p_attribute13 VARCHAR2
1774 , p_attribute14 VARCHAR2
1775 , p_attribute15 VARCHAR2
1776 , p_request_id NUMBER
1777 , p_program_application_id NUMBER
1778 , p_program_id NUMBER
1779 , p_program_update_date DATE
1780 , p_release_kanban_flag NUMBER DEFAULT NULL
1781 --eKanban changes
1782 , p_replenishment_count NUMBER DEFAULT NULL
1783 , p_max_replenishments NUMBER DEFAULT NULL
1784 , p_disable_date DATE DEFAULT NULL
1785 , p_replacement_flag NUMBER DEFAULT NULL
1786 ) IS
1787 l_kanban_card_rec inv_kanban_pvt.kanban_card_rec_type;
1788 l_kanban_card_number_ok BOOLEAN := FALSE;
1789 l_dummy VARCHAR2(1);
1790 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
1791 l_current_replnsh_cycle_id NUMBER;
1792 l_card_status NUMBER;
1793 l_supply_status NUMBER;
1794 --add for Ekanban busniess event
1795 l_msg_data VARCHAR2(1000);
1796 BEGIN
1797 fnd_msg_pub.initialize;
1798 INV_LOG_UTIL.TRACE('-- Start eKanban Changes insert_row','insert_row');
1799 IF p_kanban_card_id IS NULL THEN
1800 WHILE NOT l_kanban_card_number_ok LOOP
1801 SELECT mtl_kanban_cards_s.NEXTVAL
1802 INTO l_kanban_card_rec.kanban_card_id
1803 FROM DUAL;
1804
1805 IF p_kanban_card_number IS NULL THEN
1806 BEGIN
1807 SELECT 'X'
1808 INTO l_dummy
1809 FROM mtl_kanban_cards
1810 WHERE kanban_card_number = TO_CHAR(l_kanban_card_rec.kanban_card_id)
1811 AND organization_id = p_organization_id;
1812 EXCEPTION
1813 WHEN NO_DATA_FOUND THEN
1814 l_kanban_card_number_ok := TRUE;
1815 END;
1816 ELSE
1817 l_kanban_card_number_ok := TRUE;
1818 END IF;
1819 END LOOP;
1820 ELSE
1821 l_kanban_card_rec.kanban_card_id := p_kanban_card_id;
1822 END IF;
1823
1824 IF p_kanban_card_number IS NULL THEN
1825 if nvl(fnd_profile.VALUE('FLM_EKB_OPS_FLAG'),0) = 1 then
1826
1827 l_kanban_card_rec.kanban_card_number := FLM_KANBAN_CUSTOM_PKG.custom_kanban_number(p_pull_sequence_id);
1828 -- Also Validate Value from Hook.
1829 if l_kanban_card_rec.kanban_card_number is not null then
1830 Begin
1831 select 1 into l_dummy
1832 from mtl_kanban_cards
1833 where organization_id = l_kanban_card_rec.organization_id
1834 and kanban_card_number = l_kanban_card_rec.kanban_card_number;
1835 l_kanban_card_rec.kanban_card_number:=NULL;
1836 exception
1837 when others then
1838 l_kanban_card_rec.kanban_card_number:=NULL;
1839 end;
1840 end if;
1841 end if;
1842 --Modified For Bug 12419870.
1843 IF l_kanban_card_rec.kanban_card_number IS NULL or l_kanban_card_rec.kanban_card_number = FND_API.G_MISS_CHAR THEN
1844 l_kanban_card_rec.kanban_card_number := TO_CHAR(l_kanban_card_rec.kanban_card_id);
1845 end if;
1846
1847 ELSE
1848 l_kanban_card_rec.kanban_card_number := p_kanban_card_number;
1849 END IF;
1850
1851
1852 l_kanban_card_rec.pull_sequence_id := p_pull_sequence_id;
1853 l_kanban_card_rec.inventory_item_id := p_inventory_item_id;
1854 l_kanban_card_rec.organization_id := p_organization_id;
1855 l_kanban_card_rec.subinventory_name := p_subinventory_name;
1856 l_kanban_card_rec.supply_status := p_supply_status;
1857 l_kanban_card_rec.card_status := p_card_status;
1858 l_kanban_card_rec.kanban_card_type := p_kanban_card_type;
1859 l_kanban_card_rec.source_type := p_source_type;
1860 l_kanban_card_rec.kanban_size := p_kanban_size;
1861 l_kanban_card_rec.last_update_date := p_last_update_date;
1862 l_kanban_card_rec.last_updated_by := p_last_updated_by;
1863 l_kanban_card_rec.creation_date := p_creation_date;
1864 l_kanban_card_rec.created_by := p_created_by;
1865 l_kanban_card_rec.last_update_login := p_last_update_login;
1866 l_kanban_card_rec.last_print_date := p_last_print_date;
1867 l_kanban_card_rec.locator_id := p_locator_id;
1868 l_kanban_card_rec.supplier_id := p_supplier_id;
1869 l_kanban_card_rec.supplier_site_id := p_supplier_site_id;
1870 l_kanban_card_rec.source_organization_id := p_source_organization_id;
1871 l_kanban_card_rec.source_subinventory := p_source_subinventory;
1872 l_kanban_card_rec.source_locator_id := p_source_locator_id;
1873 l_kanban_card_rec.wip_line_id := p_wip_line_id;
1874 l_kanban_card_rec.current_replnsh_cycle_id := p_current_replnsh_cycle_id;
1875 l_kanban_card_rec.document_type := p_document_type;
1876 l_kanban_card_rec.document_header_id := p_document_header_id;
1877 l_kanban_card_rec.document_detail_id := p_document_detail_id;
1878 l_kanban_card_rec.ERROR_CODE := p_error_code;
1879 l_kanban_card_rec.attribute_category := p_attribute_category;
1880 l_kanban_card_rec.attribute1 := p_attribute1;
1881 l_kanban_card_rec.attribute2 := p_attribute2;
1882 l_kanban_card_rec.attribute3 := p_attribute3;
1883 l_kanban_card_rec.attribute4 := p_attribute4;
1884 l_kanban_card_rec.attribute5 := p_attribute5;
1885 l_kanban_card_rec.attribute6 := p_attribute6;
1886 l_kanban_card_rec.attribute7 := p_attribute7;
1887 l_kanban_card_rec.attribute8 := p_attribute8;
1888 l_kanban_card_rec.attribute9 := p_attribute9;
1889 l_kanban_card_rec.attribute10 := p_attribute10;
1890 l_kanban_card_rec.attribute11 := p_attribute11;
1891 l_kanban_card_rec.attribute12 := p_attribute12;
1892 l_kanban_card_rec.attribute13 := p_attribute13;
1893 l_kanban_card_rec.attribute14 := p_attribute14;
1894 l_kanban_card_rec.attribute15 := p_attribute15;
1895 l_kanban_card_rec.request_id := p_request_id;
1896 l_kanban_card_rec.program_application_id := p_program_application_id;
1897 l_kanban_card_rec.program_id := p_program_id;
1898 l_kanban_card_rec.program_update_date := p_program_update_date;
1899 --eKanban Changes
1900 l_kanban_card_rec.replenishment_count := p_replenishment_count;
1901 l_kanban_card_rec.max_replenishments := p_max_replenishments;
1902 l_kanban_card_rec.disable_date := p_disable_date;
1903 l_kanban_card_rec.replacement_flag := p_replacement_flag;
1904 --added by javakat
1905 INV_LOG_UTIL.TRACE('Before Callin INSERT INTO mtl_kanban_cards','insert_row');
1906 INV_LOG_UTIL.TRACE('kanban_card_number'||l_kanban_card_rec.kanban_card_number,'insert_row');
1907 INSERT INTO mtl_kanban_cards
1908 (
1909 kanban_card_id
1910 , kanban_card_number
1911 , pull_sequence_id
1912 , inventory_item_id
1913 , organization_id
1914 , subinventory_name
1915 , supply_status
1916 , card_status
1917 , kanban_card_type
1918 , source_type
1919 , kanban_size
1920 , last_update_date
1921 , last_updated_by
1922 , creation_date
1923 , created_by
1924 , last_update_login
1925 , last_print_date
1926 , locator_id
1927 , supplier_id
1928 , supplier_site_id
1929 , source_organization_id
1930 , source_subinventory
1931 , source_locator_id
1932 , wip_line_id
1933 , current_replnsh_cycle_id
1934 , ERROR_CODE
1935 , attribute_category
1936 , attribute1
1937 , attribute2
1938 , attribute3
1939 , attribute4
1940 , attribute5
1941 , attribute6
1942 , attribute7
1943 , attribute8
1944 , attribute9
1945 , attribute10
1946 , attribute11
1947 , attribute12
1948 , attribute13
1949 , attribute14
1950 , attribute15
1951 , request_id
1952 , program_application_id
1953 , program_id
1954 , program_update_date
1955 --eKanban changes
1956 , replenishment_count
1957 , max_replenishments
1958 , disable_date
1959 , replacement_flag
1960 )
1961 VALUES (
1962 l_kanban_card_rec.kanban_card_id
1963 , l_kanban_card_rec.kanban_card_number
1964 , l_kanban_card_rec.pull_sequence_id
1965 , l_kanban_card_rec.inventory_item_id
1966 , l_kanban_card_rec.organization_id
1967 , l_kanban_card_rec.subinventory_name
1968 , DECODE(
1969 trunc(l_kanban_card_rec.supply_status)
1970 , inv_kanban_pvt.g_supply_status_empty, inv_kanban_pvt.g_supply_status_new
1971 , l_kanban_card_rec.supply_status
1972 )
1973 , l_kanban_card_rec.card_status
1974 , l_kanban_card_rec.kanban_card_type
1975 , l_kanban_card_rec.source_type
1976 , l_kanban_card_rec.kanban_size
1977 , l_kanban_card_rec.last_update_date
1978 , l_kanban_card_rec.last_updated_by
1979 , l_kanban_card_rec.creation_date
1980 , l_kanban_card_rec.created_by
1981 , l_kanban_card_rec.last_update_login
1982 , l_kanban_card_rec.last_print_date
1983 , l_kanban_card_rec.locator_id
1984 , l_kanban_card_rec.supplier_id
1985 , l_kanban_card_rec.supplier_site_id
1986 , l_kanban_card_rec.source_organization_id
1987 , l_kanban_card_rec.source_subinventory
1988 , l_kanban_card_rec.source_locator_id
1989 , l_kanban_card_rec.wip_line_id
1990 , l_kanban_card_rec.current_replnsh_cycle_id
1991 , l_kanban_card_rec.ERROR_CODE
1992 , l_kanban_card_rec.attribute_category
1993 , l_kanban_card_rec.attribute1
1994 , l_kanban_card_rec.attribute2
1995 , l_kanban_card_rec.attribute3
1996 , l_kanban_card_rec.attribute4
1997 , l_kanban_card_rec.attribute5
1998 , l_kanban_card_rec.attribute6
1999 , l_kanban_card_rec.attribute7
2000 , l_kanban_card_rec.attribute8
2001 , l_kanban_card_rec.attribute9
2002 , l_kanban_card_rec.attribute10
2003 , l_kanban_card_rec.attribute11
2004 , l_kanban_card_rec.attribute12
2005 , l_kanban_card_rec.attribute13
2006 , l_kanban_card_rec.attribute14
2007 , l_kanban_card_rec.attribute15
2008 , l_kanban_card_rec.request_id
2009 , l_kanban_card_rec.program_application_id
2010 , l_kanban_card_rec.program_id
2011 , l_kanban_card_rec.program_update_date
2012 --eKanban changes
2013 , l_kanban_card_rec.replenishment_count
2014 , l_kanban_card_rec.max_replenishments
2015 , l_kanban_card_rec.disable_date
2016 , l_kanban_card_rec.replacement_flag
2017 );
2018
2019 IF (
2020 (
2021 (l_kanban_card_rec.card_status = inv_kanban_pvt.g_card_status_active)
2022 AND(trunc(l_kanban_card_rec.supply_status) = inv_kanban_pvt.g_supply_status_empty)
2023 AND(p_release_kanban_flag = 1)
2024 )
2025 OR(p_release_kanban_flag = 2)
2026 ) THEN
2027 l_supply_status := l_kanban_card_rec.supply_status;
2028 l_card_status := l_kanban_card_rec.card_status;
2029 l_current_replnsh_cycle_id := l_kanban_card_rec.current_replnsh_cycle_id;
2030 inv_kanbancard_pkg.update_row(
2031 x_return_status => l_return_status
2032 , p_kanban_card_id => l_kanban_card_rec.kanban_card_id
2033 , p_kanban_card_number => l_kanban_card_rec.kanban_card_number
2034 , p_pull_sequence_id => l_kanban_card_rec.pull_sequence_id
2035 , p_inventory_item_id => l_kanban_card_rec.inventory_item_id
2036 , p_organization_id => l_kanban_card_rec.organization_id
2037 , p_subinventory_name => l_kanban_card_rec.subinventory_name
2038 , p_supply_status => l_supply_status
2039 , p_card_status => l_card_status
2040 , p_kanban_card_type => l_kanban_card_rec.kanban_card_type
2041 , p_source_type => l_kanban_card_rec.source_type
2042 , p_kanban_size => l_kanban_card_rec.kanban_size
2043 , p_last_update_date => l_kanban_card_rec.last_update_date
2044 , p_last_updated_by => l_kanban_card_rec.last_updated_by
2045 , p_creation_date => l_kanban_card_rec.creation_date
2046 , p_created_by => l_kanban_card_rec.created_by
2047 , p_last_update_login => l_kanban_card_rec.last_update_login
2048 , p_last_print_date => l_kanban_card_rec.last_print_date
2049 , p_locator_id => l_kanban_card_rec.locator_id
2050 , p_supplier_id => l_kanban_card_rec.supplier_id
2051 , p_supplier_site_id => l_kanban_card_rec.supplier_site_id
2052 , p_source_organization_id => l_kanban_card_rec.source_organization_id
2053 , p_source_subinventory => l_kanban_card_rec.source_subinventory
2054 , p_source_locator_id => l_kanban_card_rec.source_locator_id
2055 , p_wip_line_id => l_kanban_card_rec.wip_line_id
2056 , p_current_replnsh_cycle_id => l_current_replnsh_cycle_id
2057 , p_document_type => l_kanban_card_rec.document_type
2058 , p_document_header_id => l_kanban_card_rec.document_header_id
2059 , p_document_detail_id => l_kanban_card_rec.document_detail_id
2060 , p_error_code => l_kanban_card_rec.ERROR_CODE
2061 , p_attribute_category => l_kanban_card_rec.attribute_category
2062 , p_attribute1 => l_kanban_card_rec.attribute1
2063 , p_attribute2 => l_kanban_card_rec.attribute2
2064 , p_attribute3 => l_kanban_card_rec.attribute3
2065 , p_attribute4 => l_kanban_card_rec.attribute4
2066 , p_attribute5 => l_kanban_card_rec.attribute5
2067 , p_attribute6 => l_kanban_card_rec.attribute6
2068 , p_attribute7 => l_kanban_card_rec.attribute7
2069 , p_attribute8 => l_kanban_card_rec.attribute8
2070 , p_attribute9 => l_kanban_card_rec.attribute9
2071 , p_attribute10 => l_kanban_card_rec.attribute10
2072 , p_attribute11 => l_kanban_card_rec.attribute11
2073 , p_attribute12 => l_kanban_card_rec.attribute12
2074 , p_attribute13 => l_kanban_card_rec.attribute13
2075 , p_attribute14 => l_kanban_card_rec.attribute14
2076 , p_attribute15 => l_kanban_card_rec.attribute15
2077 --eKanban changes
2078 , p_replenishment_count => l_kanban_card_rec.replenishment_count
2079 , p_max_replenishments => l_kanban_card_rec.max_replenishments
2080 , p_disable_date => l_kanban_card_rec.disable_date
2081 , p_replacement_flag => l_kanban_card_rec.replacement_flag
2082 );
2083
2084 l_kanban_card_rec.supply_status := l_supply_status;
2085 l_kanban_card_rec.card_status := l_card_status;
2086 l_kanban_card_rec.current_replnsh_cycle_id := l_current_replnsh_cycle_id;
2087 ELSE
2088 insert_activity_for_card(l_kanban_card_rec);
2089 END IF;
2090
2091 IF l_return_status = fnd_api.g_ret_sts_error THEN
2092 RAISE fnd_api.g_exc_error;
2093 END IF;
2094
2095 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
2096 RAISE fnd_api.g_exc_unexpected_error;
2097 END IF;
2098
2099 x_return_status := l_return_status;
2100 p_kanban_card_number := l_kanban_card_rec.kanban_card_number;
2101 p_kanban_card_id := l_kanban_card_rec.kanban_card_id;
2102 p_supply_status := l_kanban_card_rec.supply_status;
2103 p_card_status := l_kanban_card_rec.card_status;
2104 p_current_replnsh_cycle_id := l_kanban_card_rec.current_replnsh_cycle_id;
2105 --raise business event for kanbanCardCreation when ekanban is enabled
2106 if nvl(fnd_profile.VALUE('FLM_EKB_OPS_FLAG'),0) = 1 then
2107 FLM_EKB_BUSINESS_EVENT_PKG.Raise_Kanban_Card_Event(p_kanban_card_id => l_kanban_card_rec.kanban_card_id,
2108 p_txn_type => 1,
2109 x_msg_data =>l_msg_data,
2110 x_return_status =>l_return_status);
2111 end if;
2112 EXCEPTION
2113 WHEN fnd_api.g_exc_error THEN
2114 x_return_status := fnd_api.g_ret_sts_error;
2115 WHEN fnd_api.g_exc_unexpected_error THEN
2116 x_return_status := fnd_api.g_ret_sts_unexp_error;
2117 WHEN OTHERS THEN
2118 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
2119 fnd_msg_pub.add_exc_msg(g_pkg_name, 'Insert_Row');
2120 END IF;
2121
2122 x_return_status := fnd_api.g_ret_sts_unexp_error;
2123 END insert_row;
2124
2125 PROCEDURE lock_row(
2126 p_kanban_card_id NUMBER
2127 , p_kanban_card_number VARCHAR2
2128 , p_pull_sequence_id NUMBER
2129 , p_inventory_item_id NUMBER
2130 , p_organization_id NUMBER
2131 , p_subinventory_name VARCHAR2
2132 , p_supply_status NUMBER
2133 , p_card_status NUMBER
2134 , p_kanban_card_type NUMBER
2135 , p_source_type NUMBER
2136 , p_kanban_size NUMBER
2137 , p_last_print_date DATE
2138 , p_locator_id NUMBER
2139 , p_supplier_id NUMBER
2140 , p_supplier_site_id NUMBER
2141 , p_source_organization_id NUMBER
2142 , p_source_subinventory VARCHAR2
2143 , p_source_locator_id NUMBER
2144 , p_wip_line_id NUMBER
2145 , p_current_replnsh_cycle_id NUMBER
2146 , p_error_code NUMBER
2147 , p_attribute_category VARCHAR2
2148 , p_attribute1 VARCHAR2
2149 , p_attribute2 VARCHAR2
2150 , p_attribute3 VARCHAR2
2151 , p_attribute4 VARCHAR2
2152 , p_attribute5 VARCHAR2
2153 , p_attribute6 VARCHAR2
2154 , p_attribute7 VARCHAR2
2155 , p_attribute8 VARCHAR2
2156 , p_attribute9 VARCHAR2
2157 , p_attribute10 VARCHAR2
2158 , p_attribute11 VARCHAR2
2159 , p_attribute12 VARCHAR2
2160 , p_attribute13 VARCHAR2
2161 , p_attribute14 VARCHAR2
2162 , p_attribute15 VARCHAR2
2163 , p_replenishment_count NUMBER
2164 , p_max_replenishments NUMBER
2165 , p_disable_date DATE
2166 , p_replacement_flag NUMBER
2167 ) IS
2168 CURSOR get_current_row IS
2169 SELECT *
2170 FROM mtl_kanban_cards
2171 WHERE kanban_card_id = p_kanban_card_id
2172 FOR UPDATE OF organization_id NOWAIT;
2173
2174 recinfo mtl_kanban_cards%ROWTYPE;
2175 record_changed EXCEPTION;
2176 BEGIN
2177 OPEN get_current_row;
2178 FETCH get_current_row INTO recinfo;
2179
2180 IF (get_current_row%NOTFOUND) THEN
2181 CLOSE get_current_row;
2182 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
2183 app_exception.raise_exception;
2184 END IF;
2185
2186 CLOSE get_current_row;
2187
2188 IF NOT(
2189 recinfo.kanban_card_number = p_kanban_card_number
2190 AND recinfo.pull_sequence_id = p_pull_sequence_id
2191 AND recinfo.inventory_item_id = p_inventory_item_id
2192 AND recinfo.organization_id = p_organization_id
2193 AND recinfo.subinventory_name = p_subinventory_name
2194 AND recinfo.supply_status = p_supply_status
2195 AND recinfo.card_status = p_card_status
2196 AND recinfo.kanban_card_type = p_kanban_card_type
2197 AND recinfo.source_type = p_source_type
2198 AND recinfo.kanban_size = p_kanban_size
2199 AND((recinfo.last_print_date = p_last_print_date)
2200 OR(recinfo.last_print_date IS NULL
2201 AND p_last_print_date IS NULL))
2202 AND((recinfo.locator_id = p_locator_id)
2203 OR(recinfo.locator_id IS NULL
2204 AND p_locator_id IS NULL))
2205 AND((recinfo.supplier_id = p_supplier_id)
2206 OR(recinfo.supplier_id IS NULL
2207 AND p_supplier_id IS NULL))
2208 AND((recinfo.supplier_site_id = p_supplier_site_id)
2209 OR(recinfo.supplier_site_id IS NULL
2210 AND p_supplier_site_id IS NULL))
2211 AND(
2212 (recinfo.source_organization_id = p_source_organization_id)
2213 OR(recinfo.source_organization_id IS NULL
2214 AND p_source_organization_id IS NULL)
2215 )
2216 AND(
2217 (recinfo.source_subinventory = p_source_subinventory)
2218 OR(recinfo.source_subinventory IS NULL
2219 AND p_source_subinventory IS NULL)
2220 )
2221 AND((recinfo.source_locator_id = p_source_locator_id)
2222 OR(recinfo.source_locator_id IS NULL
2223 AND p_source_locator_id IS NULL))
2224 AND((recinfo.wip_line_id = p_wip_line_id)
2225 OR(recinfo.wip_line_id IS NULL
2226 AND p_wip_line_id IS NULL))
2227 AND((recinfo.ERROR_CODE = p_error_code)
2228 OR(recinfo.ERROR_CODE IS NULL
2229 AND p_error_code IS NULL))
2230 AND((recinfo.attribute_category = p_attribute_category)
2231 OR(recinfo.attribute_category IS NULL
2232 AND p_attribute_category IS NULL))
2233 AND((recinfo.replenishment_count = p_replenishment_count)
2234 OR(recinfo.replenishment_count IS NULL
2235 AND p_replenishment_count IS NULL))
2236 AND((recinfo.max_replenishments = p_max_replenishments)
2237 OR(recinfo.max_replenishments IS NULL
2238 AND p_max_replenishments IS NULL))
2239 AND((recinfo.disable_date = p_disable_date)
2240 OR(recinfo.disable_date IS NULL
2241 AND p_disable_date IS NULL))
2242 AND((recinfo.replacement_flag = p_replacement_flag)
2243 OR(recinfo.replacement_flag IS NULL
2244 AND p_replacement_flag IS NULL))
2245 ) THEN
2246 RAISE record_changed;
2247 END IF;
2248
2249 IF NOT(
2250 ((recinfo.attribute1 = p_attribute1)
2251 OR((recinfo.attribute1 IS NULL)
2252 AND(p_attribute1 IS NULL)))
2253 AND((recinfo.attribute2 = p_attribute2)
2254 OR((recinfo.attribute2 IS NULL)
2255 AND(p_attribute2 IS NULL)))
2256 AND((recinfo.attribute3 = p_attribute3)
2257 OR((recinfo.attribute3 IS NULL)
2258 AND(p_attribute3 IS NULL)))
2259 AND((recinfo.attribute4 = p_attribute4)
2260 OR((recinfo.attribute4 IS NULL)
2261 AND(p_attribute4 IS NULL)))
2262 AND((recinfo.attribute5 = p_attribute5)
2263 OR((recinfo.attribute5 IS NULL)
2264 AND(p_attribute5 IS NULL)))
2265 AND((recinfo.attribute6 = p_attribute6)
2266 OR((recinfo.attribute6 IS NULL)
2267 AND(p_attribute6 IS NULL)))
2268 AND((recinfo.attribute7 = p_attribute7)
2269 OR((recinfo.attribute7 IS NULL)
2270 AND(p_attribute7 IS NULL)))
2271 AND((recinfo.attribute8 = p_attribute8)
2272 OR((recinfo.attribute8 IS NULL)
2273 AND(p_attribute8 IS NULL)))
2274 AND((recinfo.attribute9 = p_attribute9)
2275 OR((recinfo.attribute9 IS NULL)
2276 AND(p_attribute9 IS NULL)))
2277 AND((recinfo.attribute10 = p_attribute10)
2278 OR((recinfo.attribute10 IS NULL)
2279 AND(p_attribute10 IS NULL)))
2280 AND((recinfo.attribute11 = p_attribute11)
2281 OR((recinfo.attribute11 IS NULL)
2282 AND(p_attribute11 IS NULL)))
2283 AND((recinfo.attribute12 = p_attribute12)
2284 OR((recinfo.attribute12 IS NULL)
2285 AND(p_attribute12 IS NULL)))
2286 AND((recinfo.attribute13 = p_attribute13)
2287 OR((recinfo.attribute13 IS NULL)
2288 AND(p_attribute13 IS NULL)))
2289 AND((recinfo.attribute14 = p_attribute14)
2290 OR((recinfo.attribute14 IS NULL)
2291 AND(p_attribute14 IS NULL)))
2292 AND((recinfo.attribute15 = p_attribute15)
2293 OR((recinfo.attribute15 IS NULL)
2294 AND(p_attribute15 IS NULL)))
2295 ) THEN
2296 RAISE record_changed;
2297 END IF;
2298 EXCEPTION
2299 WHEN record_changed THEN
2300 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
2301 app_exception.raise_exception;
2302 WHEN OTHERS THEN
2303 RAISE;
2304 END lock_row;
2305
2306 PROCEDURE update_row(
2307 x_return_status OUT NOCOPY VARCHAR2
2308 , p_kanban_card_id NUMBER
2309 , p_kanban_card_number VARCHAR2
2310 , p_pull_sequence_id NUMBER
2311 , p_inventory_item_id NUMBER
2312 , p_organization_id NUMBER
2313 , p_subinventory_name VARCHAR2
2314 , p_supply_status IN OUT NOCOPY NUMBER
2315 , p_card_status IN OUT NOCOPY NUMBER
2316 , p_kanban_card_type NUMBER
2317 , p_source_type NUMBER
2318 , p_kanban_size NUMBER
2319 , p_last_update_date DATE
2320 , p_last_updated_by NUMBER
2321 , p_creation_date DATE
2322 , p_created_by NUMBER
2323 , p_last_update_login NUMBER
2324 , p_last_print_date DATE
2325 , p_locator_id NUMBER
2326 , p_supplier_id NUMBER
2327 , p_supplier_site_id NUMBER
2328 , p_source_organization_id NUMBER
2329 , p_source_subinventory VARCHAR2
2330 , p_source_locator_id NUMBER
2331 , p_wip_line_id NUMBER
2332 , p_current_replnsh_cycle_id IN OUT NOCOPY NUMBER
2333 , p_document_type NUMBER
2334 , p_document_header_id NUMBER
2335 , p_document_detail_id NUMBER
2336 , p_error_code NUMBER
2337 , p_attribute_category VARCHAR2
2338 , p_attribute1 VARCHAR2
2339 , p_attribute2 VARCHAR2
2340 , p_attribute3 VARCHAR2
2341 , p_attribute4 VARCHAR2
2342 , p_attribute5 VARCHAR2
2343 , p_attribute6 VARCHAR2
2344 , p_attribute7 VARCHAR2
2345 , p_attribute8 VARCHAR2
2346 , p_attribute9 VARCHAR2
2347 , p_attribute10 VARCHAR2
2348 , p_attribute11 VARCHAR2
2349 , p_attribute12 VARCHAR2
2350 , p_attribute13 VARCHAR2
2351 , p_attribute14 VARCHAR2
2352 , p_attribute15 VARCHAR2
2353 , p_lot_item_id NUMBER DEFAULT NULL
2354 , p_lot_number VARCHAR2 DEFAULT NULL
2355 , p_lot_item_revision VARCHAR2 DEFAULT NULL
2356 , p_lot_subinventory_code VARCHAR2 DEFAULT NULL
2357 , p_lot_location_id NUMBER DEFAULT NULL
2358 , p_lot_quantity NUMBER DEFAULT NULL
2359 , p_replenish_quantity NUMBER DEFAULT NULL
2360 , p_need_by_date DATE DEFAULT NULL
2361 , p_source_wip_entity_id NUMBER DEFAULT NULL
2362 ) IS
2363 l_kanban_card_rec inv_kanban_pvt.kanban_card_rec_type;
2364 l_old_kanban_card_rec inv_kanban_pvt.kanban_card_rec_type;
2365 l_current_replenish_cycle_id NUMBER;
2366 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
2367 l_supply_status NUMBER;
2368 BEGIN
2369 fnd_msg_pub.initialize;
2370 mydebug('Inside update_row 2');
2371 l_old_kanban_card_rec := query_row(p_kanban_card_id => p_kanban_card_id);
2372 l_kanban_card_rec.kanban_card_id := p_kanban_card_id;
2373 l_kanban_card_rec.kanban_card_number := p_kanban_card_number;
2374 l_kanban_card_rec.pull_sequence_id := p_pull_sequence_id;
2375 l_kanban_card_rec.inventory_item_id := p_inventory_item_id;
2376 l_kanban_card_rec.organization_id := p_organization_id;
2377 l_kanban_card_rec.subinventory_name := p_subinventory_name;
2378 l_kanban_card_rec.kanban_card_type := p_kanban_card_type;
2379 l_kanban_card_rec.supply_status := p_supply_status;
2380
2381 IF (l_kanban_card_rec.kanban_card_type = inv_kanban_pvt.g_card_type_nonreplenishable)
2382 AND(l_kanban_card_rec.supply_status = inv_kanban_pvt.g_supply_status_full)
2383 AND(l_old_kanban_card_rec.supply_status <> l_kanban_card_rec.supply_status) THEN
2384 l_kanban_card_rec.card_status := inv_kanban_pvt.g_card_status_hold;
2385 ELSE
2386 l_kanban_card_rec.card_status := p_card_status;
2387 END IF;
2388
2389 l_kanban_card_rec.source_type := p_source_type;
2390 l_kanban_card_rec.kanban_size := p_kanban_size;
2391 l_kanban_card_rec.last_update_date := p_last_update_date;
2392 l_kanban_card_rec.last_updated_by := p_last_updated_by;
2393 l_kanban_card_rec.creation_date := p_creation_date;
2394 l_kanban_card_rec.created_by := p_created_by;
2395 l_kanban_card_rec.last_update_login := p_last_update_login;
2396 l_kanban_card_rec.last_print_date := p_last_print_date;
2397 l_kanban_card_rec.locator_id := p_locator_id;
2398 l_kanban_card_rec.supplier_id := p_supplier_id;
2399 l_kanban_card_rec.supplier_site_id := p_supplier_site_id;
2400 l_kanban_card_rec.source_organization_id := p_source_organization_id;
2401 l_kanban_card_rec.source_subinventory := p_source_subinventory;
2402 l_kanban_card_rec.source_locator_id := p_source_locator_id;
2403 l_kanban_card_rec.wip_line_id := p_wip_line_id;
2404 l_kanban_card_rec.document_type := p_document_type;
2405 l_kanban_card_rec.document_header_id := p_document_header_id;
2406 l_kanban_card_rec.document_detail_id := p_document_detail_id;
2407
2408 IF p_supply_status = inv_kanban_pvt.g_supply_status_full THEN
2409 l_kanban_card_rec.current_replnsh_cycle_id := NULL;
2410 ELSE
2411 l_kanban_card_rec.current_replnsh_cycle_id := p_current_replnsh_cycle_id;
2412 END IF;
2413
2414 l_kanban_card_rec.ERROR_CODE := p_error_code;
2415 l_kanban_card_rec.attribute_category := p_attribute_category;
2416 l_kanban_card_rec.attribute1 := p_attribute1;
2417 l_kanban_card_rec.attribute2 := p_attribute2;
2418 l_kanban_card_rec.attribute3 := p_attribute3;
2419 l_kanban_card_rec.attribute4 := p_attribute4;
2420 l_kanban_card_rec.attribute5 := p_attribute5;
2421 l_kanban_card_rec.attribute6 := p_attribute6;
2422 l_kanban_card_rec.attribute7 := p_attribute7;
2423 l_kanban_card_rec.attribute8 := p_attribute8;
2424 l_kanban_card_rec.attribute9 := p_attribute9;
2425 l_kanban_card_rec.attribute10 := p_attribute10;
2426 l_kanban_card_rec.attribute11 := p_attribute11;
2427 l_kanban_card_rec.attribute12 := p_attribute12;
2428 l_kanban_card_rec.attribute13 := p_attribute13;
2429 l_kanban_card_rec.attribute14 := p_attribute14;
2430 l_kanban_card_rec.attribute15 := p_attribute15;
2431 l_kanban_card_rec.lot_item_id := p_lot_item_id;
2432 l_kanban_card_rec.lot_number := p_lot_number;
2433 l_kanban_card_rec.lot_subinventory_code := p_lot_subinventory_code;
2434 l_kanban_card_rec.lot_item_revision := p_lot_item_revision;
2435 l_kanban_card_rec.lot_location_id := p_lot_location_id;
2436 l_kanban_card_rec.lot_quantity := p_lot_quantity;
2437 l_kanban_card_rec.replenish_quantity := p_replenish_quantity;
2438 l_kanban_card_rec.need_by_date := p_need_by_date;
2439 l_kanban_card_rec.source_wip_entity_id := p_source_wip_entity_id;
2440
2441 IF l_kanban_card_rec.card_status = inv_kanban_pvt.g_card_status_active
2442 AND l_kanban_card_rec.supply_status = inv_kanban_pvt.g_supply_status_empty
2443 AND l_old_kanban_card_rec.supply_status <> l_kanban_card_rec.supply_status THEN
2444 mydebug('calling INV_Kanban_PVT.Check_And_Create_Replenishment');
2445 inv_kanban_pvt.check_and_create_replenishment(
2446 x_return_status => l_return_status
2447 , x_supply_status => l_supply_status
2448 , x_current_replenish_cycle_id => l_current_replenish_cycle_id
2449 , p_kanban_card_rec => l_kanban_card_rec
2450 );
2451
2452 IF l_return_status = fnd_api.g_ret_sts_error THEN
2453 RAISE fnd_api.g_exc_error;
2454 END IF;
2455
2456 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
2457 RAISE fnd_api.g_exc_unexpected_error;
2458 END IF;
2459
2460 l_kanban_card_rec.supply_status := l_supply_status;
2461 l_kanban_card_rec.current_replnsh_cycle_id := l_current_replenish_cycle_id;
2462 END IF;
2463
2464 UPDATE mtl_kanban_cards
2465 SET pull_sequence_id = l_kanban_card_rec.pull_sequence_id
2466 , inventory_item_id = l_kanban_card_rec.inventory_item_id
2467 , organization_id = l_kanban_card_rec.organization_id
2468 , subinventory_name = l_kanban_card_rec.subinventory_name
2469 , supply_status = l_kanban_card_rec.supply_status
2470 , card_status = l_kanban_card_rec.card_status
2471 , kanban_card_type = l_kanban_card_rec.kanban_card_type
2472 , source_type = l_kanban_card_rec.source_type
2473 , kanban_size = l_kanban_card_rec.kanban_size
2474 , last_update_date = l_kanban_card_rec.last_update_date
2475 , last_updated_by = l_kanban_card_rec.last_updated_by
2476 , creation_date = l_kanban_card_rec.creation_date
2477 , created_by = l_kanban_card_rec.created_by
2478 , last_update_login = l_kanban_card_rec.last_update_login
2479 , last_print_date = l_kanban_card_rec.last_print_date
2480 , locator_id = l_kanban_card_rec.locator_id
2481 , supplier_id = l_kanban_card_rec.supplier_id
2482 , supplier_site_id = l_kanban_card_rec.supplier_site_id
2483 , source_organization_id = l_kanban_card_rec.source_organization_id
2484 , source_subinventory = l_kanban_card_rec.source_subinventory
2485 , source_locator_id = l_kanban_card_rec.source_locator_id
2486 , wip_line_id = l_kanban_card_rec.wip_line_id
2487 , current_replnsh_cycle_id = l_kanban_card_rec.current_replnsh_cycle_id
2488 , ERROR_CODE = l_kanban_card_rec.ERROR_CODE
2489 , attribute_category = l_kanban_card_rec.attribute_category
2490 , attribute1 = l_kanban_card_rec.attribute1
2491 , attribute2 = l_kanban_card_rec.attribute2
2492 , attribute3 = l_kanban_card_rec.attribute3
2493 , attribute4 = l_kanban_card_rec.attribute4
2494 , attribute5 = l_kanban_card_rec.attribute5
2495 , attribute6 = l_kanban_card_rec.attribute6
2496 , attribute7 = l_kanban_card_rec.attribute7
2497 , attribute8 = l_kanban_card_rec.attribute8
2498 , attribute9 = l_kanban_card_rec.attribute9
2499 , attribute10 = l_kanban_card_rec.attribute10
2500 , attribute11 = l_kanban_card_rec.attribute11
2501 , attribute12 = l_kanban_card_rec.attribute12
2502 , attribute13 = l_kanban_card_rec.attribute13
2503 , attribute14 = l_kanban_card_rec.attribute14
2504 , attribute15 = l_kanban_card_rec.attribute15
2505 WHERE kanban_card_id = p_kanban_card_id;
2506
2507 IF (SQL%NOTFOUND) THEN
2508 RAISE NO_DATA_FOUND;
2509 END IF;
2510
2511 IF changed_row(l_kanban_card_rec, l_old_kanban_card_rec) THEN
2512 insert_activity_for_card(l_kanban_card_rec);
2513 -- Bug Fix 4361921
2514 ELSIF l_kanban_card_rec.document_type = INV_KANBAN_PVT.G_Doc_type_lot_job and
2515 l_kanban_card_rec.document_header_id <>
2516 nvl( l_old_kanban_card_rec.document_header_id,-9999) and
2517 l_kanban_card_rec.supply_status =
2518 INV_KANBAN_PVT.G_Supply_Status_InProcess THEN
2519 -- Bug Fix: 5344450
2520 -- Added the IF condition
2521 IF (p_replenish_quantity IS NULL) THEN
2522
2523 -- Retained old update statement.
2524 mydebug(' Updating the document header id with :' ||
2525 l_kanban_card_rec.document_header_id);
2526 UPDATE mtl_kanban_card_activity
2527 SET document_header_id = l_kanban_card_rec.document_header_id
2528 WHERE
2529 kanban_card_number = l_kanban_card_rec.kanban_card_number AND
2530 supply_status = INV_KANBAN_PVT.G_Supply_Status_InProcess AND
2531 document_type = INV_KANBAN_PVT.G_Doc_type_lot_job AND
2532 replenishment_cycle_id = l_kanban_card_rec.current_replnsh_cycle_id;
2533 ELSE
2534 -- Bug Fix: 5344450
2535 -- Added new update statement to update the kanban_size with p_replenish_quantity
2536 mydebug(' Updating the document header id with :' || l_kanban_card_rec.document_header_id||
2537 ' kanban_size : '||p_replenish_quantity);
2538 UPDATE mtl_kanban_card_activity
2539 SET document_header_id = l_kanban_card_rec.document_header_id,
2540 kanban_size = p_replenish_quantity
2541 WHERE
2542 kanban_card_number = l_kanban_card_rec.kanban_card_number AND
2543 supply_status = INV_KANBAN_PVT.G_Supply_Status_InProcess AND
2544 document_type = INV_KANBAN_PVT.G_Doc_type_lot_job AND
2545 replenishment_cycle_id = l_kanban_card_rec.current_replnsh_cycle_id;
2546
2547 END IF;
2548
2549 END IF;
2550
2551 x_return_status := l_return_status;
2552 p_supply_status := l_kanban_card_rec.supply_status;
2553 p_card_status := l_kanban_card_rec.card_status;
2554 p_current_replnsh_cycle_id := l_kanban_card_rec.current_replnsh_cycle_id;
2555 EXCEPTION
2556 WHEN fnd_api.g_exc_error THEN
2557 x_return_status := fnd_api.g_ret_sts_error;
2558 WHEN fnd_api.g_exc_unexpected_error THEN
2559 x_return_status := fnd_api.g_ret_sts_unexp_error;
2560 WHEN OTHERS THEN
2561 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
2562 fnd_msg_pub.add_exc_msg(g_pkg_name, 'update_row');
2563 END IF;
2564
2565 x_return_status := fnd_api.g_ret_sts_unexp_error;
2566 END update_row;
2567
2568
2569 END inv_kanbancard_pkg;