[Home] [Help]
PACKAGE BODY: APPS.INV_KANBANCARD_PKG
Source
1 PACKAGE BODY inv_kanbancard_pkg AS
2 /* $Header: INVKCRDB.pls 120.5 2006/08/09 11:44:02 ankulkar noship $ */
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 SELECT kanban_card_id
96 , kanban_card_number
97 , pull_sequence_id
98 , inventory_item_id
99 , organization_id
100 , subinventory_name
101 , supply_status
102 , card_status
103 , kanban_card_type
104 , source_type
105 , kanban_size
106 , last_update_date
107 , last_updated_by
108 , creation_date
109 , created_by
110 , locator_id
111 , supplier_id
112 , supplier_site_id
113 , source_organization_id
114 , source_subinventory
115 , source_locator_id
116 , wip_line_id
117 , current_replnsh_cycle_id
118 , ERROR_CODE
119 , last_update_login
120 , last_print_date
121 , attribute_category
122 , attribute1
123 , attribute2
124 , attribute3
125 , attribute4
126 , attribute5
127 , attribute6
128 , attribute7
129 , attribute8
130 , attribute9
131 , attribute10
132 , attribute11
133 , attribute12
134 , attribute13
135 , attribute14
136 , attribute15
137 , request_id
138 , program_application_id
139 , program_id
140 , program_update_date
141 INTO l_kanban_card_rec.kanban_card_id
142 , l_kanban_card_rec.kanban_card_number
143 , l_kanban_card_rec.pull_sequence_id
144 , l_kanban_card_rec.inventory_item_id
145 , l_kanban_card_rec.organization_id
146 , l_kanban_card_rec.subinventory_name
147 , l_kanban_card_rec.supply_status
148 , l_kanban_card_rec.card_status
149 , l_kanban_card_rec.kanban_card_type
150 , l_kanban_card_rec.source_type
151 , l_kanban_card_rec.kanban_size
152 , l_kanban_card_rec.last_update_date
153 , l_kanban_card_rec.last_updated_by
154 , l_kanban_card_rec.creation_date
155 , l_kanban_card_rec.created_by
156 , l_kanban_card_rec.locator_id
157 , l_kanban_card_rec.supplier_id
158 , l_kanban_card_rec.supplier_site_id
159 , l_kanban_card_rec.source_organization_id
160 , l_kanban_card_rec.source_subinventory
161 , l_kanban_card_rec.source_locator_id
162 , l_kanban_card_rec.wip_line_id
163 , l_kanban_card_rec.current_replnsh_cycle_id
164 , l_kanban_card_rec.ERROR_CODE
165 , l_kanban_card_rec.last_update_login
166 , l_kanban_card_rec.last_print_date
167 , l_kanban_card_rec.attribute_category
168 , l_kanban_card_rec.attribute1
169 , l_kanban_card_rec.attribute2
170 , l_kanban_card_rec.attribute3
171 , l_kanban_card_rec.attribute4
172 , l_kanban_card_rec.attribute5
173 , l_kanban_card_rec.attribute6
174 , l_kanban_card_rec.attribute7
175 , l_kanban_card_rec.attribute8
176 , l_kanban_card_rec.attribute9
177 , l_kanban_card_rec.attribute10
178 , l_kanban_card_rec.attribute11
179 , l_kanban_card_rec.attribute12
180 , l_kanban_card_rec.attribute13
181 , l_kanban_card_rec.attribute14
182 , l_kanban_card_rec.attribute15
183 , l_kanban_card_rec.request_id
184 , l_kanban_card_rec.program_application_id
185 , l_kanban_card_rec.program_id
186 , l_kanban_card_rec.program_update_date
187 FROM mtl_kanban_cards
188 WHERE kanban_card_id = p_kanban_card_id;
189
190 l_kanban_card_rec.document_type := NULL;
191 l_kanban_card_rec.document_header_id := NULL;
192 l_kanban_card_rec.document_detail_id := NULL;
193 RETURN l_kanban_card_rec;
194 EXCEPTION
195 WHEN OTHERS THEN
196 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
197 fnd_msg_pub.add_exc_msg(g_pkg_name, 'Query_Row');
198 END IF;
199
200 RAISE fnd_api.g_exc_unexpected_error;
201 END query_row;
202
203 FUNCTION cell(row_in NUMBER, col_in NUMBER)
204 RETURN NUMBER IS
205 BEGIN
206 RETURN (col_in - 1) * g_supply_status_rows + row_in;
207 END cell;
208
209 FUNCTION supply_status_change_ok(p_from_supply_status NUMBER, p_to_supply_status NUMBER, p_card_status NUMBER)
210 RETURN BOOLEAN IS
211 l_result BOOLEAN;
212 l_supply_status_from VARCHAR2(30);
213 l_supply_status_to VARCHAR2(30);
214 BEGIN
215 /*
216 If p_card_status = INV_Kanban_PVT.G_Card_Status_Cancel
217 Then
218 FND_MESSAGE.SET_NAME('INV','INV_NO_ACT_ALLOW_CANCEL_CARD');
219 Return False;
220 Elsif p_card_status = INV_Kanban_PVT.G_Card_Status_Hold
221 Then
222 FND_MESSAGE.SET_NAME('INV','INV_NO_ACT_ALLOW_HOLD_CARD');
223 Return False;
224 Else
225 */
226 l_result := fnd_api.to_boolean(g_supply_status_change_tbl(cell(p_to_supply_status, p_from_supply_status)));
227
228 IF l_result THEN
229 RETURN(l_result);
230 END IF;
231
232 BEGIN
233 SELECT a.meaning
234 , b.meaning
235 INTO l_supply_status_from
236 , l_supply_status_to
237 FROM mfg_lookups a, mfg_lookups b
238 WHERE a.lookup_type = 'MTL_KANBAN_SUPPLY_STATUS'
239 AND a.lookup_code = p_from_supply_status
240 AND b.lookup_type = 'MTL_KANBAN_SUPPLY_STATUS'
241 AND b.lookup_code = p_to_supply_status;
242
243 fnd_message.set_name('INV', 'INV_SUPPLY_STATUS_NOT_ALLOWED');
244 fnd_message.set_token('SUPPLY_STATUS_FROM', l_supply_status_from);
245 fnd_message.set_token('SUPPLY_STATUS_TO', l_supply_status_to);
246 EXCEPTION
247 WHEN OTHERS THEN
248 NULL;
249 END;
250
251 RETURN(l_result);
252 /* End If; */
253 END supply_status_change_ok;
254
255 PROCEDURE commit_row IS
256 BEGIN
257 COMMIT;
258 END commit_row;
259
260 PROCEDURE rollback_row IS
261 BEGIN
262 ROLLBACK;
263 END rollback_row;
264
265 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)
266 RETURN BOOLEAN IS
267 l_dupl_inprocess_act BOOLEAN := ( p_kanban_card_rec.supply_status = p_old_kanban_card_rec.supply_status
268 AND p_kanban_card_rec.supply_status = 5
269 AND p_kanban_card_rec.current_replnsh_cycle_id = p_old_kanban_card_rec.current_replnsh_cycle_id
270 );
271 BEGIN
272 -- Bug 3661982 : If the card is in 'In Process' state for a particular replenisment cycle,
273 -- stop insertion into card activity table for another 'In Process' state activity if new
274 -- replenisment cycle is identical to the old one.
275 IF (
276 p_kanban_card_rec.kanban_card_number = p_old_kanban_card_rec.kanban_card_number
277 AND p_kanban_card_rec.pull_sequence_id = p_old_kanban_card_rec.pull_sequence_id
278 AND p_kanban_card_rec.inventory_item_id = p_old_kanban_card_rec.inventory_item_id
279 AND p_kanban_card_rec.organization_id = p_old_kanban_card_rec.organization_id
280 AND p_kanban_card_rec.subinventory_name = p_old_kanban_card_rec.subinventory_name
281 AND(
282 (p_kanban_card_rec.supply_status = p_old_kanban_card_rec.supply_status
283 AND p_kanban_card_rec.supply_status <> 5)
284 OR l_dupl_inprocess_act
285 )
286 AND p_kanban_card_rec.card_status = p_old_kanban_card_rec.card_status
287 AND p_kanban_card_rec.kanban_card_type = p_old_kanban_card_rec.kanban_card_type
288 AND p_kanban_card_rec.source_type = p_old_kanban_card_rec.source_type
289 AND p_kanban_card_rec.kanban_size = p_old_kanban_card_rec.kanban_size
290 AND(
291 (p_kanban_card_rec.last_print_date = p_old_kanban_card_rec.last_print_date)
292 OR(p_kanban_card_rec.last_print_date IS NULL
293 AND p_old_kanban_card_rec.last_print_date IS NULL)
294 )
295 AND(
296 (p_kanban_card_rec.locator_id = p_old_kanban_card_rec.locator_id)
297 OR(p_kanban_card_rec.locator_id IS NULL
298 AND p_old_kanban_card_rec.locator_id IS NULL)
299 )
300 AND(
301 (p_kanban_card_rec.supplier_id = p_old_kanban_card_rec.supplier_id)
302 OR(p_kanban_card_rec.supplier_id IS NULL
303 AND p_old_kanban_card_rec.supplier_id IS NULL)
304 )
305 AND(
306 (p_kanban_card_rec.supplier_site_id = p_old_kanban_card_rec.supplier_site_id)
307 OR(p_kanban_card_rec.supplier_site_id IS NULL
308 AND p_old_kanban_card_rec.supplier_site_id IS NULL)
309 )
310 AND(
311 (p_kanban_card_rec.source_organization_id = p_old_kanban_card_rec.source_organization_id)
312 OR(p_kanban_card_rec.source_organization_id IS NULL
313 AND p_old_kanban_card_rec.source_organization_id IS NULL)
314 )
315 AND(
316 (p_kanban_card_rec.source_subinventory = p_old_kanban_card_rec.source_subinventory)
317 OR(p_kanban_card_rec.source_subinventory IS NULL
318 AND p_old_kanban_card_rec.source_subinventory IS NULL)
319 )
320 AND(
321 (p_kanban_card_rec.source_locator_id = p_old_kanban_card_rec.source_locator_id)
322 OR(p_kanban_card_rec.source_locator_id IS NULL
323 AND p_old_kanban_card_rec.source_locator_id IS NULL)
324 )
325 AND(
326 (p_kanban_card_rec.wip_line_id = p_old_kanban_card_rec.wip_line_id)
327 OR(p_kanban_card_rec.wip_line_id IS NULL
328 AND p_old_kanban_card_rec.wip_line_id IS NULL)
329 )
330 AND(
331 (p_kanban_card_rec.ERROR_CODE = p_old_kanban_card_rec.ERROR_CODE)
332 OR(p_kanban_card_rec.ERROR_CODE IS NULL
333 AND p_old_kanban_card_rec.ERROR_CODE IS NULL)
334 )
335 AND(
336 (p_kanban_card_rec.attribute_category = p_old_kanban_card_rec.attribute_category)
337 OR(p_kanban_card_rec.attribute_category IS NULL
338 AND p_old_kanban_card_rec.attribute_category IS NULL)
339 )
340 AND(
341 (p_kanban_card_rec.attribute1 = p_old_kanban_card_rec.attribute1)
342 OR(p_kanban_card_rec.attribute1 IS NULL
343 AND p_old_kanban_card_rec.attribute1 IS NULL)
344 )
345 AND(
346 (p_kanban_card_rec.attribute2 = p_old_kanban_card_rec.attribute2)
347 OR(p_kanban_card_rec.attribute2 IS NULL
348 AND p_old_kanban_card_rec.attribute2 IS NULL)
349 )
350 AND(
351 (p_kanban_card_rec.attribute3 = p_old_kanban_card_rec.attribute3)
352 OR(p_kanban_card_rec.attribute3 IS NULL
353 AND p_old_kanban_card_rec.attribute3 IS NULL)
354 )
355 AND(
356 (p_kanban_card_rec.attribute4 = p_old_kanban_card_rec.attribute4)
357 OR(p_kanban_card_rec.attribute4 IS NULL
358 AND p_old_kanban_card_rec.attribute4 IS NULL)
359 )
360 AND(
361 (p_kanban_card_rec.attribute5 = p_old_kanban_card_rec.attribute5)
362 OR(p_kanban_card_rec.attribute5 IS NULL
363 AND p_old_kanban_card_rec.attribute5 IS NULL)
364 )
365 AND(
366 (p_kanban_card_rec.attribute6 = p_old_kanban_card_rec.attribute6)
367 OR(p_kanban_card_rec.attribute6 IS NULL
368 AND p_old_kanban_card_rec.attribute6 IS NULL)
369 )
370 AND(
371 (p_kanban_card_rec.attribute7 = p_old_kanban_card_rec.attribute7)
372 OR(p_kanban_card_rec.attribute7 IS NULL
373 AND p_old_kanban_card_rec.attribute7 IS NULL)
374 )
375 AND(
376 (p_kanban_card_rec.attribute8 = p_old_kanban_card_rec.attribute8)
377 OR(p_kanban_card_rec.attribute8 IS NULL
378 AND p_old_kanban_card_rec.attribute8 IS NULL)
379 )
380 AND(
381 (p_kanban_card_rec.attribute9 = p_old_kanban_card_rec.attribute9)
382 OR(p_kanban_card_rec.attribute9 IS NULL
383 AND p_old_kanban_card_rec.attribute9 IS NULL)
384 )
385 AND(
386 (p_kanban_card_rec.attribute10 = p_old_kanban_card_rec.attribute10)
387 OR(p_kanban_card_rec.attribute10 IS NULL
388 AND p_old_kanban_card_rec.attribute10 IS NULL)
389 )
390 AND(
391 (p_kanban_card_rec.attribute11 = p_old_kanban_card_rec.attribute11)
392 OR(p_kanban_card_rec.attribute11 IS NULL
393 AND p_old_kanban_card_rec.attribute11 IS NULL)
394 )
395 AND(
396 (p_kanban_card_rec.attribute12 = p_old_kanban_card_rec.attribute12)
397 OR(p_kanban_card_rec.attribute12 IS NULL
398 AND p_old_kanban_card_rec.attribute12 IS NULL)
402 OR(p_kanban_card_rec.attribute13 IS NULL
399 )
400 AND(
401 (p_kanban_card_rec.attribute13 = p_old_kanban_card_rec.attribute13)
403 AND p_old_kanban_card_rec.attribute13 IS NULL)
404 )
405 AND(
406 (p_kanban_card_rec.attribute14 = p_old_kanban_card_rec.attribute14)
407 OR(p_kanban_card_rec.attribute14 IS NULL
408 AND p_old_kanban_card_rec.attribute14 IS NULL)
409 )
410 AND(
411 (p_kanban_card_rec.attribute15 = p_kanban_card_rec.attribute15)
412 OR(p_kanban_card_rec.attribute15 IS NULL
413 AND p_old_kanban_card_rec.attribute15 IS NULL)
414 )
415 AND((p_kanban_card_rec.document_type IS NULL)
416 OR l_dupl_inprocess_act)
417 AND((p_kanban_card_rec.document_header_id IS NULL)
418 OR l_dupl_inprocess_act)
419 AND((p_kanban_card_rec.document_detail_id IS NULL)
420 OR l_dupl_inprocess_act)
421 AND((p_kanban_card_rec.replenish_quantity IS NULL)
422 OR l_dupl_inprocess_act)
423 ) THEN
424 RETURN FALSE;
425 ELSE
426 RETURN TRUE;
427 END IF;
428 END changed_row;
429
430 PROCEDURE insert_row(
431 x_return_status OUT NOCOPY VARCHAR2
432 , p_kanban_card_id IN OUT NOCOPY NUMBER
433 , p_kanban_card_number IN OUT NOCOPY VARCHAR2
434 , p_pull_sequence_id NUMBER
435 , p_inventory_item_id NUMBER
436 , p_organization_id NUMBER
437 , p_subinventory_name VARCHAR2
438 , p_supply_status IN OUT NOCOPY NUMBER
439 , p_card_status IN OUT NOCOPY NUMBER
440 , p_kanban_card_type NUMBER
441 , p_source_type NUMBER
442 , p_kanban_size NUMBER
443 , p_last_update_date DATE
444 , p_last_updated_by NUMBER
445 , p_creation_date DATE
446 , p_created_by NUMBER
447 , p_last_update_login NUMBER
448 , p_last_print_date DATE
449 , p_locator_id NUMBER
450 , p_supplier_id NUMBER
451 , p_supplier_site_id NUMBER
452 , p_source_organization_id NUMBER
453 , p_source_subinventory VARCHAR2
454 , p_source_locator_id NUMBER
455 , p_wip_line_id NUMBER
456 , p_current_replnsh_cycle_id IN OUT NOCOPY NUMBER
457 , p_document_type NUMBER
458 , p_document_header_id NUMBER
459 , p_document_detail_id NUMBER
460 , p_error_code NUMBER
461 , p_attribute_category VARCHAR2
462 , p_attribute1 VARCHAR2
463 , p_attribute2 VARCHAR2
464 , p_attribute3 VARCHAR2
465 , p_attribute4 VARCHAR2
466 , p_attribute5 VARCHAR2
467 , p_attribute6 VARCHAR2
468 , p_attribute7 VARCHAR2
469 , p_attribute8 VARCHAR2
470 , p_attribute9 VARCHAR2
471 , p_attribute10 VARCHAR2
472 , p_attribute11 VARCHAR2
473 , p_attribute12 VARCHAR2
474 , p_attribute13 VARCHAR2
475 , p_attribute14 VARCHAR2
476 , p_attribute15 VARCHAR2
477 , p_request_id NUMBER
478 , p_program_application_id NUMBER
479 , p_program_id NUMBER
480 , p_program_update_date DATE
481 , p_release_kanban_flag NUMBER
482 ) IS
483 l_kanban_card_rec inv_kanban_pvt.kanban_card_rec_type;
484 l_kanban_card_number_ok BOOLEAN := FALSE;
485 l_dummy VARCHAR2(1);
486 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
487 l_current_replnsh_cycle_id NUMBER;
488 l_card_status NUMBER;
489 l_supply_status NUMBER;
490 BEGIN
491 fnd_msg_pub.initialize;
492
493 WHILE NOT l_kanban_card_number_ok LOOP
494 SELECT mtl_kanban_cards_s.NEXTVAL
495 INTO l_kanban_card_rec.kanban_card_id
496 FROM DUAL;
497
498 IF p_kanban_card_number IS NULL THEN
499 BEGIN
500 SELECT 'X'
501 INTO l_dummy
502 FROM mtl_kanban_cards
503 WHERE kanban_card_number = TO_CHAR(l_kanban_card_rec.kanban_card_id)
504 AND organization_id = p_organization_id;
505 EXCEPTION
506 WHEN NO_DATA_FOUND THEN
507 l_kanban_card_number_ok := TRUE;
508 END;
509 ELSE
510 l_kanban_card_number_ok := TRUE;
511 END IF;
512 END LOOP;
513
514 IF p_kanban_card_number IS NULL THEN
515 l_kanban_card_rec.kanban_card_number := TO_CHAR(l_kanban_card_rec.kanban_card_id);
516 ELSE
517 l_kanban_card_rec.kanban_card_number := p_kanban_card_number;
521 l_kanban_card_rec.inventory_item_id := p_inventory_item_id;
518 END IF;
519
520 l_kanban_card_rec.pull_sequence_id := p_pull_sequence_id;
522 l_kanban_card_rec.organization_id := p_organization_id;
523 l_kanban_card_rec.subinventory_name := p_subinventory_name;
524 l_kanban_card_rec.supply_status := p_supply_status;
525 l_kanban_card_rec.card_status := p_card_status;
526 l_kanban_card_rec.kanban_card_type := p_kanban_card_type;
527 l_kanban_card_rec.source_type := p_source_type;
528 l_kanban_card_rec.kanban_size := p_kanban_size;
529 l_kanban_card_rec.last_update_date := p_last_update_date;
530 l_kanban_card_rec.last_updated_by := p_last_updated_by;
531 l_kanban_card_rec.creation_date := p_creation_date;
532 l_kanban_card_rec.created_by := p_created_by;
533 l_kanban_card_rec.last_update_login := p_last_update_login;
534 l_kanban_card_rec.last_print_date := p_last_print_date;
535 l_kanban_card_rec.locator_id := p_locator_id;
536 l_kanban_card_rec.supplier_id := p_supplier_id;
537 l_kanban_card_rec.supplier_site_id := p_supplier_site_id;
538 l_kanban_card_rec.source_organization_id := p_source_organization_id;
539 l_kanban_card_rec.source_subinventory := p_source_subinventory;
540 l_kanban_card_rec.source_locator_id := p_source_locator_id;
541 l_kanban_card_rec.wip_line_id := p_wip_line_id;
542 l_kanban_card_rec.current_replnsh_cycle_id := p_current_replnsh_cycle_id;
543 l_kanban_card_rec.document_type := p_document_type;
544 l_kanban_card_rec.document_header_id := p_document_header_id;
545 l_kanban_card_rec.document_detail_id := p_document_detail_id;
546 l_kanban_card_rec.ERROR_CODE := p_error_code;
547 l_kanban_card_rec.attribute_category := p_attribute_category;
548 l_kanban_card_rec.attribute1 := p_attribute1;
549 l_kanban_card_rec.attribute2 := p_attribute2;
550 l_kanban_card_rec.attribute3 := p_attribute3;
551 l_kanban_card_rec.attribute4 := p_attribute4;
552 l_kanban_card_rec.attribute5 := p_attribute5;
553 l_kanban_card_rec.attribute6 := p_attribute6;
554 l_kanban_card_rec.attribute7 := p_attribute7;
555 l_kanban_card_rec.attribute8 := p_attribute8;
556 l_kanban_card_rec.attribute9 := p_attribute9;
557 l_kanban_card_rec.attribute10 := p_attribute10;
558 l_kanban_card_rec.attribute11 := p_attribute11;
559 l_kanban_card_rec.attribute12 := p_attribute12;
560 l_kanban_card_rec.attribute13 := p_attribute13;
561 l_kanban_card_rec.attribute14 := p_attribute14;
562 l_kanban_card_rec.attribute15 := p_attribute15;
563 l_kanban_card_rec.request_id := p_request_id;
564 l_kanban_card_rec.program_application_id := p_program_application_id;
565 l_kanban_card_rec.program_id := p_program_id;
566 l_kanban_card_rec.program_update_date := p_program_update_date;
567
568 INSERT INTO mtl_kanban_cards
569 (
570 kanban_card_id
571 , kanban_card_number
572 , pull_sequence_id
573 , inventory_item_id
574 , organization_id
575 , subinventory_name
576 , supply_status
577 , card_status
578 , kanban_card_type
579 , source_type
580 , kanban_size
581 , last_update_date
582 , last_updated_by
583 , creation_date
584 , created_by
585 , last_update_login
586 , last_print_date
587 , locator_id
588 , supplier_id
589 , supplier_site_id
590 , source_organization_id
591 , source_subinventory
592 , source_locator_id
593 , wip_line_id
594 , current_replnsh_cycle_id
595 , ERROR_CODE
596 , attribute_category
597 , attribute1
598 , attribute2
599 , attribute3
600 , attribute4
601 , attribute5
602 , attribute6
603 , attribute7
604 , attribute8
605 , attribute9
606 , attribute10
607 , attribute11
608 , attribute12
609 , attribute13
610 , attribute14
611 , attribute15
612 , request_id
613 , program_application_id
614 , program_id
615 , program_update_date
616 )
617 VALUES (
618 l_kanban_card_rec.kanban_card_id
619 , l_kanban_card_rec.kanban_card_number
620 , l_kanban_card_rec.pull_sequence_id
621 , l_kanban_card_rec.inventory_item_id
622 , l_kanban_card_rec.organization_id
623 , l_kanban_card_rec.subinventory_name
624 , DECODE(
625 l_kanban_card_rec.supply_status
626 , inv_kanban_pvt.g_supply_status_empty, inv_kanban_pvt.g_supply_status_new
630 , l_kanban_card_rec.kanban_card_type
627 , l_kanban_card_rec.supply_status
628 )
629 , l_kanban_card_rec.card_status
631 , l_kanban_card_rec.source_type
632 , l_kanban_card_rec.kanban_size
633 , l_kanban_card_rec.last_update_date
634 , l_kanban_card_rec.last_updated_by
635 , l_kanban_card_rec.creation_date
636 , l_kanban_card_rec.created_by
637 , l_kanban_card_rec.last_update_login
638 , l_kanban_card_rec.last_print_date
639 , l_kanban_card_rec.locator_id
640 , l_kanban_card_rec.supplier_id
641 , l_kanban_card_rec.supplier_site_id
642 , l_kanban_card_rec.source_organization_id
643 , l_kanban_card_rec.source_subinventory
644 , l_kanban_card_rec.source_locator_id
645 , l_kanban_card_rec.wip_line_id
646 , l_kanban_card_rec.current_replnsh_cycle_id
647 , l_kanban_card_rec.ERROR_CODE
648 , l_kanban_card_rec.attribute_category
649 , l_kanban_card_rec.attribute1
650 , l_kanban_card_rec.attribute2
651 , l_kanban_card_rec.attribute3
652 , l_kanban_card_rec.attribute4
653 , l_kanban_card_rec.attribute5
654 , l_kanban_card_rec.attribute6
655 , l_kanban_card_rec.attribute7
656 , l_kanban_card_rec.attribute8
657 , l_kanban_card_rec.attribute9
658 , l_kanban_card_rec.attribute10
659 , l_kanban_card_rec.attribute11
660 , l_kanban_card_rec.attribute12
661 , l_kanban_card_rec.attribute13
662 , l_kanban_card_rec.attribute14
663 , l_kanban_card_rec.attribute15
664 , l_kanban_card_rec.request_id
665 , l_kanban_card_rec.program_application_id
666 , l_kanban_card_rec.program_id
667 , l_kanban_card_rec.program_update_date
668 );
669
670 IF (
671 (
672 (l_kanban_card_rec.card_status = inv_kanban_pvt.g_card_status_active)
673 AND(l_kanban_card_rec.supply_status = inv_kanban_pvt.g_supply_status_empty)
674 AND(p_release_kanban_flag = 1)
675 )
676 OR(p_release_kanban_flag = 2)
677 ) THEN
678 l_supply_status := l_kanban_card_rec.supply_status;
679 l_card_status := l_kanban_card_rec.card_status;
680 l_current_replnsh_cycle_id := l_kanban_card_rec.current_replnsh_cycle_id;
681 inv_kanbancard_pkg.update_row(
682 x_return_status => l_return_status
683 , p_kanban_card_id => l_kanban_card_rec.kanban_card_id
684 , p_kanban_card_number => l_kanban_card_rec.kanban_card_number
685 , p_pull_sequence_id => l_kanban_card_rec.pull_sequence_id
686 , p_inventory_item_id => l_kanban_card_rec.inventory_item_id
687 , p_organization_id => l_kanban_card_rec.organization_id
688 , p_subinventory_name => l_kanban_card_rec.subinventory_name
689 , p_supply_status => l_supply_status
690 , p_card_status => l_card_status
691 , p_kanban_card_type => l_kanban_card_rec.kanban_card_type
692 , p_source_type => l_kanban_card_rec.source_type
693 , p_kanban_size => l_kanban_card_rec.kanban_size
694 , p_last_update_date => l_kanban_card_rec.last_update_date
695 , p_last_updated_by => l_kanban_card_rec.last_updated_by
696 , p_creation_date => l_kanban_card_rec.creation_date
697 , p_created_by => l_kanban_card_rec.created_by
698 , p_last_update_login => l_kanban_card_rec.last_update_login
699 , p_last_print_date => l_kanban_card_rec.last_print_date
700 , p_locator_id => l_kanban_card_rec.locator_id
701 , p_supplier_id => l_kanban_card_rec.supplier_id
702 , p_supplier_site_id => l_kanban_card_rec.supplier_site_id
703 , p_source_organization_id => l_kanban_card_rec.source_organization_id
704 , p_source_subinventory => l_kanban_card_rec.source_subinventory
705 , p_source_locator_id => l_kanban_card_rec.source_locator_id
706 , p_wip_line_id => l_kanban_card_rec.wip_line_id
707 , p_current_replnsh_cycle_id => l_current_replnsh_cycle_id
708 , p_document_type => l_kanban_card_rec.document_type
709 , p_document_header_id => l_kanban_card_rec.document_header_id
710 , p_document_detail_id => l_kanban_card_rec.document_detail_id
711 , p_error_code => l_kanban_card_rec.ERROR_CODE
712 , p_attribute_category => l_kanban_card_rec.attribute_category
713 , p_attribute1 => l_kanban_card_rec.attribute1
714 , p_attribute2 => l_kanban_card_rec.attribute2
715 , p_attribute3 => l_kanban_card_rec.attribute3
716 , p_attribute4 => l_kanban_card_rec.attribute4
717 , p_attribute5 => l_kanban_card_rec.attribute5
718 , p_attribute6 => l_kanban_card_rec.attribute6
719 , p_attribute7 => l_kanban_card_rec.attribute7
723 , p_attribute11 => l_kanban_card_rec.attribute11
720 , p_attribute8 => l_kanban_card_rec.attribute8
721 , p_attribute9 => l_kanban_card_rec.attribute9
722 , p_attribute10 => l_kanban_card_rec.attribute10
724 , p_attribute12 => l_kanban_card_rec.attribute12
725 , p_attribute13 => l_kanban_card_rec.attribute13
726 , p_attribute14 => l_kanban_card_rec.attribute14
727 , p_attribute15 => l_kanban_card_rec.attribute15
728 );
729 l_kanban_card_rec.supply_status := l_supply_status;
730 l_kanban_card_rec.card_status := l_card_status;
731 l_kanban_card_rec.current_replnsh_cycle_id := l_current_replnsh_cycle_id;
732 ELSE
733 insert_activity_for_card(l_kanban_card_rec);
734 END IF;
735
736 IF l_return_status = fnd_api.g_ret_sts_error THEN
737 RAISE fnd_api.g_exc_error;
738 END IF;
739
740 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
741 RAISE fnd_api.g_exc_unexpected_error;
742 END IF;
743
744 x_return_status := l_return_status;
745 p_kanban_card_number := l_kanban_card_rec.kanban_card_number;
746 p_kanban_card_id := l_kanban_card_rec.kanban_card_id;
747 p_supply_status := l_kanban_card_rec.supply_status;
748 p_card_status := l_kanban_card_rec.card_status;
749 p_current_replnsh_cycle_id := l_kanban_card_rec.current_replnsh_cycle_id;
750 EXCEPTION
751 WHEN fnd_api.g_exc_error THEN
752 x_return_status := fnd_api.g_ret_sts_error;
753 WHEN fnd_api.g_exc_unexpected_error THEN
754 x_return_status := fnd_api.g_ret_sts_unexp_error;
755 WHEN OTHERS THEN
756 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
757 fnd_msg_pub.add_exc_msg(g_pkg_name, 'Insert_Row');
758 END IF;
759
760 x_return_status := fnd_api.g_ret_sts_unexp_error;
761 END insert_row;
762
763 PROCEDURE lock_row(
764 p_kanban_card_id NUMBER
765 , p_kanban_card_number VARCHAR2
766 , p_pull_sequence_id NUMBER
767 , p_inventory_item_id NUMBER
768 , p_organization_id NUMBER
769 , p_subinventory_name VARCHAR2
770 , p_supply_status NUMBER
771 , p_card_status NUMBER
772 , p_kanban_card_type NUMBER
773 , p_source_type NUMBER
774 , p_kanban_size NUMBER
775 , p_last_print_date DATE
776 , p_locator_id NUMBER
777 , p_supplier_id NUMBER
778 , p_supplier_site_id NUMBER
779 , p_source_organization_id NUMBER
780 , p_source_subinventory VARCHAR2
781 , p_source_locator_id NUMBER
782 , p_wip_line_id NUMBER
783 , p_current_replnsh_cycle_id NUMBER
784 , p_error_code NUMBER
785 , p_attribute_category VARCHAR2
786 , p_attribute1 VARCHAR2
787 , p_attribute2 VARCHAR2
788 , p_attribute3 VARCHAR2
789 , p_attribute4 VARCHAR2
790 , p_attribute5 VARCHAR2
791 , p_attribute6 VARCHAR2
792 , p_attribute7 VARCHAR2
793 , p_attribute8 VARCHAR2
794 , p_attribute9 VARCHAR2
795 , p_attribute10 VARCHAR2
796 , p_attribute11 VARCHAR2
797 , p_attribute12 VARCHAR2
798 , p_attribute13 VARCHAR2
799 , p_attribute14 VARCHAR2
800 , p_attribute15 VARCHAR2
801 ) IS
802 CURSOR get_current_row IS
803 SELECT *
804 FROM mtl_kanban_cards
805 WHERE kanban_card_id = p_kanban_card_id
806 FOR UPDATE OF organization_id NOWAIT;
807
808 recinfo mtl_kanban_cards%ROWTYPE;
809 record_changed EXCEPTION;
810 BEGIN
811 OPEN get_current_row;
812 FETCH get_current_row INTO recinfo;
813
814 IF (get_current_row%NOTFOUND) THEN
815 CLOSE get_current_row;
816 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
817 app_exception.raise_exception;
818 END IF;
819
820 CLOSE get_current_row;
821
822 IF NOT(
823 recinfo.kanban_card_number = p_kanban_card_number
824 AND recinfo.pull_sequence_id = p_pull_sequence_id
825 AND recinfo.inventory_item_id = p_inventory_item_id
826 AND recinfo.organization_id = p_organization_id
827 AND recinfo.subinventory_name = p_subinventory_name
828 AND recinfo.supply_status = p_supply_status
829 AND recinfo.card_status = p_card_status
830 AND recinfo.kanban_card_type = p_kanban_card_type
831 AND recinfo.source_type = p_source_type
832 AND recinfo.kanban_size = p_kanban_size
833 AND((recinfo.last_print_date = p_last_print_date)
834 OR(recinfo.last_print_date IS NULL
835 AND p_last_print_date IS NULL))
836 AND((recinfo.locator_id = p_locator_id)
837 OR(recinfo.locator_id IS NULL
838 AND p_locator_id IS NULL))
839 AND((recinfo.supplier_id = p_supplier_id)
840 OR(recinfo.supplier_id IS NULL
844 AND p_supplier_site_id IS NULL))
841 AND p_supplier_id IS NULL))
842 AND((recinfo.supplier_site_id = p_supplier_site_id)
843 OR(recinfo.supplier_site_id IS NULL
845 AND(
846 (recinfo.source_organization_id = p_source_organization_id)
847 OR(recinfo.source_organization_id IS NULL
848 AND p_source_organization_id IS NULL)
849 )
850 AND(
851 (recinfo.source_subinventory = p_source_subinventory)
852 OR(recinfo.source_subinventory IS NULL
853 AND p_source_subinventory IS NULL)
854 )
855 AND((recinfo.source_locator_id = p_source_locator_id)
856 OR(recinfo.source_locator_id IS NULL
857 AND p_source_locator_id IS NULL))
858 AND((recinfo.wip_line_id = p_wip_line_id)
859 OR(recinfo.wip_line_id IS NULL
860 AND p_wip_line_id IS NULL))
861 AND((recinfo.ERROR_CODE = p_error_code)
862 OR(recinfo.ERROR_CODE IS NULL
863 AND p_error_code IS NULL))
864 AND((recinfo.attribute_category = p_attribute_category)
865 OR(recinfo.attribute_category IS NULL
866 AND p_attribute_category IS NULL))
867 ) THEN
868 RAISE record_changed;
869 END IF;
870
871 IF NOT(
872 ((recinfo.attribute1 = p_attribute1)
873 OR((recinfo.attribute1 IS NULL)
874 AND(p_attribute1 IS NULL)))
875 AND((recinfo.attribute2 = p_attribute2)
876 OR((recinfo.attribute2 IS NULL)
877 AND(p_attribute2 IS NULL)))
878 AND((recinfo.attribute3 = p_attribute3)
879 OR((recinfo.attribute3 IS NULL)
880 AND(p_attribute3 IS NULL)))
881 AND((recinfo.attribute4 = p_attribute4)
882 OR((recinfo.attribute4 IS NULL)
883 AND(p_attribute4 IS NULL)))
884 AND((recinfo.attribute5 = p_attribute5)
885 OR((recinfo.attribute5 IS NULL)
886 AND(p_attribute5 IS NULL)))
887 AND((recinfo.attribute6 = p_attribute6)
888 OR((recinfo.attribute6 IS NULL)
889 AND(p_attribute6 IS NULL)))
890 AND((recinfo.attribute7 = p_attribute7)
891 OR((recinfo.attribute7 IS NULL)
892 AND(p_attribute7 IS NULL)))
893 AND((recinfo.attribute8 = p_attribute8)
894 OR((recinfo.attribute8 IS NULL)
895 AND(p_attribute8 IS NULL)))
896 AND((recinfo.attribute9 = p_attribute9)
897 OR((recinfo.attribute9 IS NULL)
898 AND(p_attribute9 IS NULL)))
899 AND((recinfo.attribute10 = p_attribute10)
900 OR((recinfo.attribute10 IS NULL)
901 AND(p_attribute10 IS NULL)))
902 AND((recinfo.attribute11 = p_attribute11)
903 OR((recinfo.attribute11 IS NULL)
904 AND(p_attribute11 IS NULL)))
905 AND((recinfo.attribute12 = p_attribute12)
906 OR((recinfo.attribute12 IS NULL)
907 AND(p_attribute12 IS NULL)))
908 AND((recinfo.attribute13 = p_attribute13)
909 OR((recinfo.attribute13 IS NULL)
910 AND(p_attribute13 IS NULL)))
911 AND((recinfo.attribute14 = p_attribute14)
912 OR((recinfo.attribute14 IS NULL)
913 AND(p_attribute14 IS NULL)))
914 AND((recinfo.attribute15 = p_attribute15)
915 OR((recinfo.attribute15 IS NULL)
916 AND(p_attribute15 IS NULL)))
917 ) THEN
918 RAISE record_changed;
919 END IF;
920 EXCEPTION
921 WHEN record_changed THEN
922 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
923 app_exception.raise_exception;
924 WHEN OTHERS THEN
925 RAISE;
926 END lock_row;
927
928 PROCEDURE update_row(
929 x_return_status OUT NOCOPY VARCHAR2
930 , p_kanban_card_id NUMBER
931 , p_kanban_card_number VARCHAR2
932 , p_pull_sequence_id NUMBER
933 , p_inventory_item_id NUMBER
934 , p_organization_id NUMBER
935 , p_subinventory_name VARCHAR2
936 , p_supply_status IN OUT NOCOPY NUMBER
937 , p_card_status IN OUT NOCOPY NUMBER
938 , p_kanban_card_type NUMBER
939 , p_source_type NUMBER
940 , p_kanban_size NUMBER
941 , p_last_update_date DATE
942 , p_last_updated_by NUMBER
943 , p_creation_date DATE
944 , p_created_by NUMBER
945 , p_last_update_login NUMBER
946 , p_last_print_date DATE
947 , p_locator_id NUMBER
948 , p_supplier_id NUMBER
949 , p_supplier_site_id NUMBER
950 , p_source_organization_id NUMBER
951 , p_source_subinventory VARCHAR2
952 , p_source_locator_id NUMBER
953 , p_wip_line_id NUMBER
954 , p_current_replnsh_cycle_id IN OUT NOCOPY NUMBER
958 , p_error_code NUMBER
955 , p_document_type NUMBER
956 , p_document_header_id NUMBER
957 , p_document_detail_id NUMBER
959 , p_attribute_category VARCHAR2
960 , p_attribute1 VARCHAR2
961 , p_attribute2 VARCHAR2
962 , p_attribute3 VARCHAR2
963 , p_attribute4 VARCHAR2
964 , p_attribute5 VARCHAR2
965 , p_attribute6 VARCHAR2
966 , p_attribute7 VARCHAR2
967 , p_attribute8 VARCHAR2
968 , p_attribute9 VARCHAR2
969 , p_attribute10 VARCHAR2
970 , p_attribute11 VARCHAR2
971 , p_attribute12 VARCHAR2
972 , p_attribute13 VARCHAR2
973 , p_attribute14 VARCHAR2
974 , p_attribute15 VARCHAR2
975 , p_lot_item_id NUMBER DEFAULT NULL
976 , p_lot_number VARCHAR2 DEFAULT NULL
977 , p_lot_item_revision VARCHAR2 DEFAULT NULL
978 , p_lot_subinventory_code VARCHAR2 DEFAULT NULL
979 , p_lot_location_id NUMBER DEFAULT NULL
980 , p_lot_quantity NUMBER DEFAULT NULL
981 , p_replenish_quantity NUMBER DEFAULT NULL
982 , p_need_by_date DATE DEFAULT NULL
983 , p_source_wip_entity_id NUMBER DEFAULT NULL
984 ) IS
985 l_kanban_card_rec inv_kanban_pvt.kanban_card_rec_type;
986 l_old_kanban_card_rec inv_kanban_pvt.kanban_card_rec_type;
987 l_current_replenish_cycle_id NUMBER;
988 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
989 l_supply_status NUMBER;
990 BEGIN
991 fnd_msg_pub.initialize;
992 mydebug('Inside update_row 2');
993 l_old_kanban_card_rec := query_row(p_kanban_card_id => p_kanban_card_id);
994 l_kanban_card_rec.kanban_card_id := p_kanban_card_id;
995 l_kanban_card_rec.kanban_card_number := p_kanban_card_number;
996 l_kanban_card_rec.pull_sequence_id := p_pull_sequence_id;
997 l_kanban_card_rec.inventory_item_id := p_inventory_item_id;
998 l_kanban_card_rec.organization_id := p_organization_id;
999 l_kanban_card_rec.subinventory_name := p_subinventory_name;
1000 l_kanban_card_rec.kanban_card_type := p_kanban_card_type;
1001 l_kanban_card_rec.supply_status := p_supply_status;
1002
1003 IF (l_kanban_card_rec.kanban_card_type = inv_kanban_pvt.g_card_type_nonreplenishable)
1004 AND(l_kanban_card_rec.supply_status = inv_kanban_pvt.g_supply_status_full)
1005 AND(l_old_kanban_card_rec.supply_status <> l_kanban_card_rec.supply_status) THEN
1006 l_kanban_card_rec.card_status := inv_kanban_pvt.g_card_status_hold;
1007 ELSE
1008 l_kanban_card_rec.card_status := p_card_status;
1009 END IF;
1010
1011 l_kanban_card_rec.source_type := p_source_type;
1012 l_kanban_card_rec.kanban_size := p_kanban_size;
1013 l_kanban_card_rec.last_update_date := p_last_update_date;
1014 l_kanban_card_rec.last_updated_by := p_last_updated_by;
1015 l_kanban_card_rec.creation_date := p_creation_date;
1016 l_kanban_card_rec.created_by := p_created_by;
1017 l_kanban_card_rec.last_update_login := p_last_update_login;
1018 l_kanban_card_rec.last_print_date := p_last_print_date;
1019 l_kanban_card_rec.locator_id := p_locator_id;
1020 l_kanban_card_rec.supplier_id := p_supplier_id;
1021 l_kanban_card_rec.supplier_site_id := p_supplier_site_id;
1022 l_kanban_card_rec.source_organization_id := p_source_organization_id;
1023 l_kanban_card_rec.source_subinventory := p_source_subinventory;
1024 l_kanban_card_rec.source_locator_id := p_source_locator_id;
1025 l_kanban_card_rec.wip_line_id := p_wip_line_id;
1026 l_kanban_card_rec.document_type := p_document_type;
1027 l_kanban_card_rec.document_header_id := p_document_header_id;
1028 l_kanban_card_rec.document_detail_id := p_document_detail_id;
1029
1030 IF p_supply_status = inv_kanban_pvt.g_supply_status_full THEN
1031 l_kanban_card_rec.current_replnsh_cycle_id := NULL;
1032 ELSE
1033 l_kanban_card_rec.current_replnsh_cycle_id := p_current_replnsh_cycle_id;
1034 END IF;
1035
1036 l_kanban_card_rec.ERROR_CODE := p_error_code;
1037 l_kanban_card_rec.attribute_category := p_attribute_category;
1038 l_kanban_card_rec.attribute1 := p_attribute1;
1039 l_kanban_card_rec.attribute2 := p_attribute2;
1040 l_kanban_card_rec.attribute3 := p_attribute3;
1041 l_kanban_card_rec.attribute4 := p_attribute4;
1042 l_kanban_card_rec.attribute5 := p_attribute5;
1043 l_kanban_card_rec.attribute6 := p_attribute6;
1044 l_kanban_card_rec.attribute7 := p_attribute7;
1045 l_kanban_card_rec.attribute8 := p_attribute8;
1046 l_kanban_card_rec.attribute9 := p_attribute9;
1047 l_kanban_card_rec.attribute10 := p_attribute10;
1048 l_kanban_card_rec.attribute11 := p_attribute11;
1052 l_kanban_card_rec.attribute15 := p_attribute15;
1049 l_kanban_card_rec.attribute12 := p_attribute12;
1050 l_kanban_card_rec.attribute13 := p_attribute13;
1051 l_kanban_card_rec.attribute14 := p_attribute14;
1053 l_kanban_card_rec.lot_item_id := p_lot_item_id;
1054 l_kanban_card_rec.lot_number := p_lot_number;
1055 l_kanban_card_rec.lot_subinventory_code := p_lot_subinventory_code;
1056 l_kanban_card_rec.lot_item_revision := p_lot_item_revision;
1057 l_kanban_card_rec.lot_location_id := p_lot_location_id;
1058 l_kanban_card_rec.lot_quantity := p_lot_quantity;
1059 l_kanban_card_rec.replenish_quantity := p_replenish_quantity;
1060 l_kanban_card_rec.need_by_date := p_need_by_date;
1061 l_kanban_card_rec.source_wip_entity_id := p_source_wip_entity_id;
1062
1063 IF l_kanban_card_rec.card_status = inv_kanban_pvt.g_card_status_active
1064 AND l_kanban_card_rec.supply_status = inv_kanban_pvt.g_supply_status_empty
1065 AND l_old_kanban_card_rec.supply_status <> l_kanban_card_rec.supply_status THEN
1066 mydebug('calling INV_Kanban_PVT.Check_And_Create_Replenishment');
1067 inv_kanban_pvt.check_and_create_replenishment(
1068 x_return_status => l_return_status
1069 , x_supply_status => l_supply_status
1070 , x_current_replenish_cycle_id => l_current_replenish_cycle_id
1071 , p_kanban_card_rec => l_kanban_card_rec
1072 );
1073
1074 IF l_return_status = fnd_api.g_ret_sts_error THEN
1075 RAISE fnd_api.g_exc_error;
1076 END IF;
1077
1078 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1079 RAISE fnd_api.g_exc_unexpected_error;
1080 END IF;
1081
1082 l_kanban_card_rec.supply_status := l_supply_status;
1083 l_kanban_card_rec.current_replnsh_cycle_id := l_current_replenish_cycle_id;
1084 END IF;
1085
1086 UPDATE mtl_kanban_cards
1087 SET pull_sequence_id = l_kanban_card_rec.pull_sequence_id
1088 , inventory_item_id = l_kanban_card_rec.inventory_item_id
1089 , organization_id = l_kanban_card_rec.organization_id
1090 , subinventory_name = l_kanban_card_rec.subinventory_name
1091 , supply_status = l_kanban_card_rec.supply_status
1092 , card_status = l_kanban_card_rec.card_status
1093 , kanban_card_type = l_kanban_card_rec.kanban_card_type
1094 , source_type = l_kanban_card_rec.source_type
1095 , kanban_size = l_kanban_card_rec.kanban_size
1096 , last_update_date = l_kanban_card_rec.last_update_date
1097 , last_updated_by = l_kanban_card_rec.last_updated_by
1098 , creation_date = l_kanban_card_rec.creation_date
1099 , created_by = l_kanban_card_rec.created_by
1100 , last_update_login = l_kanban_card_rec.last_update_login
1101 , last_print_date = l_kanban_card_rec.last_print_date
1102 , locator_id = l_kanban_card_rec.locator_id
1103 , supplier_id = l_kanban_card_rec.supplier_id
1104 , supplier_site_id = l_kanban_card_rec.supplier_site_id
1105 , source_organization_id = l_kanban_card_rec.source_organization_id
1106 , source_subinventory = l_kanban_card_rec.source_subinventory
1107 , source_locator_id = l_kanban_card_rec.source_locator_id
1108 , wip_line_id = l_kanban_card_rec.wip_line_id
1109 , current_replnsh_cycle_id = l_kanban_card_rec.current_replnsh_cycle_id
1110 , ERROR_CODE = l_kanban_card_rec.ERROR_CODE
1111 , attribute_category = l_kanban_card_rec.attribute_category
1112 , attribute1 = l_kanban_card_rec.attribute1
1113 , attribute2 = l_kanban_card_rec.attribute2
1114 , attribute3 = l_kanban_card_rec.attribute3
1115 , attribute4 = l_kanban_card_rec.attribute4
1116 , attribute5 = l_kanban_card_rec.attribute5
1117 , attribute6 = l_kanban_card_rec.attribute6
1118 , attribute7 = l_kanban_card_rec.attribute7
1119 , attribute8 = l_kanban_card_rec.attribute8
1120 , attribute9 = l_kanban_card_rec.attribute9
1121 , attribute10 = l_kanban_card_rec.attribute10
1122 , attribute11 = l_kanban_card_rec.attribute11
1123 , attribute12 = l_kanban_card_rec.attribute12
1124 , attribute13 = l_kanban_card_rec.attribute13
1125 , attribute14 = l_kanban_card_rec.attribute14
1126 , attribute15 = l_kanban_card_rec.attribute15
1127 WHERE kanban_card_id = p_kanban_card_id;
1128
1129 IF (SQL%NOTFOUND) THEN
1130 RAISE NO_DATA_FOUND;
1131 END IF;
1132
1133 IF changed_row(l_kanban_card_rec, l_old_kanban_card_rec) THEN
1134 insert_activity_for_card(l_kanban_card_rec);
1135 -- Bug Fix 4361921
1136 ELSIF l_kanban_card_rec.document_type = INV_KANBAN_PVT.G_Doc_type_lot_job and
1137 l_kanban_card_rec.document_header_id <>
1138 nvl( l_old_kanban_card_rec.document_header_id,-9999) and
1139 l_kanban_card_rec.supply_status =
1140 INV_KANBAN_PVT.G_Supply_Status_InProcess THEN
1141 -- Bug Fix: 5344450
1142 -- Added the IF condition
1143 IF (p_replenish_quantity IS NULL) THEN
1144
1145 -- Retained old update statement.
1146 mydebug(' Updating the document header id with :' ||
1147 l_kanban_card_rec.document_header_id);
1148 UPDATE mtl_kanban_card_activity
1149 SET document_header_id = l_kanban_card_rec.document_header_id
1153 document_type = INV_KANBAN_PVT.G_Doc_type_lot_job AND
1150 WHERE
1151 kanban_card_number = l_kanban_card_rec.kanban_card_number AND
1152 supply_status = INV_KANBAN_PVT.G_Supply_Status_InProcess AND
1154 replenishment_cycle_id = l_kanban_card_rec.current_replnsh_cycle_id;
1155 ELSE
1156 -- Bug Fix: 5344450
1157 -- Added new update statement to update the kanban_size with p_replenish_quantity
1158 mydebug(' Updating the document header id with :' || l_kanban_card_rec.document_header_id||
1159 ' kanban_size : '||p_replenish_quantity);
1160 UPDATE mtl_kanban_card_activity
1161 SET document_header_id = l_kanban_card_rec.document_header_id,
1162 kanban_size = p_replenish_quantity
1163 WHERE
1164 kanban_card_number = l_kanban_card_rec.kanban_card_number AND
1165 supply_status = INV_KANBAN_PVT.G_Supply_Status_InProcess AND
1166 document_type = INV_KANBAN_PVT.G_Doc_type_lot_job AND
1167 replenishment_cycle_id = l_kanban_card_rec.current_replnsh_cycle_id;
1168
1169 END IF;
1170
1171 END IF;
1172
1173 x_return_status := l_return_status;
1174 p_supply_status := l_kanban_card_rec.supply_status;
1175 p_card_status := l_kanban_card_rec.card_status;
1176 p_current_replnsh_cycle_id := l_kanban_card_rec.current_replnsh_cycle_id;
1177 EXCEPTION
1178 WHEN fnd_api.g_exc_error THEN
1179 x_return_status := fnd_api.g_ret_sts_error;
1180 WHEN fnd_api.g_exc_unexpected_error THEN
1181 x_return_status := fnd_api.g_ret_sts_unexp_error;
1182 WHEN OTHERS THEN
1183 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1184 fnd_msg_pub.add_exc_msg(g_pkg_name, 'update_row');
1185 END IF;
1186
1187 x_return_status := fnd_api.g_ret_sts_unexp_error;
1188 END update_row;
1189
1190 PROCEDURE update_row(p_kanban_card_rec inv_kanban_pvt.kanban_card_rec_type) IS
1191 l_return_status VARCHAR2(1);
1192 l_supply_status NUMBER;
1193 l_card_status NUMBER;
1194 l_current_replnsh_cycle_id NUMBER;
1195 BEGIN
1196 fnd_msg_pub.initialize;
1197 mydebug('Inside update_row 1');
1198 l_supply_status := p_kanban_card_rec.supply_status;
1199 l_card_status := p_kanban_card_rec.card_status;
1200 l_current_replnsh_cycle_id := p_kanban_card_rec.current_replnsh_cycle_id;
1201 update_row(
1202 x_return_status => l_return_status
1203 , p_kanban_card_id => p_kanban_card_rec.kanban_card_id
1204 , p_kanban_card_number => p_kanban_card_rec.kanban_card_number
1205 , p_pull_sequence_id => p_kanban_card_rec.pull_sequence_id
1206 , p_inventory_item_id => p_kanban_card_rec.inventory_item_id
1207 , p_organization_id => p_kanban_card_rec.organization_id
1208 , p_subinventory_name => p_kanban_card_rec.subinventory_name
1209 , p_supply_status => l_supply_status
1210 , p_card_status => l_card_status
1211 , p_kanban_card_type => p_kanban_card_rec.kanban_card_type
1212 , p_source_type => p_kanban_card_rec.source_type
1213 , p_kanban_size => p_kanban_card_rec.kanban_size
1214 , p_last_update_date => p_kanban_card_rec.last_update_date
1215 , p_last_updated_by => p_kanban_card_rec.last_updated_by
1216 , p_creation_date => p_kanban_card_rec.creation_date
1217 , p_created_by => p_kanban_card_rec.created_by
1218 , p_last_update_login => p_kanban_card_rec.last_update_login
1219 , p_last_print_date => p_kanban_card_rec.last_print_date
1220 , p_locator_id => p_kanban_card_rec.locator_id
1221 , p_supplier_id => p_kanban_card_rec.supplier_id
1222 , p_supplier_site_id => p_kanban_card_rec.supplier_site_id
1223 , p_source_organization_id => p_kanban_card_rec.source_organization_id
1224 , p_source_subinventory => p_kanban_card_rec.source_subinventory
1225 , p_source_locator_id => p_kanban_card_rec.source_locator_id
1226 , p_wip_line_id => p_kanban_card_rec.wip_line_id
1227 , p_current_replnsh_cycle_id => l_current_replnsh_cycle_id
1228 , p_document_type => p_kanban_card_rec.document_type
1229 , p_document_header_id => p_kanban_card_rec.document_header_id
1230 , p_document_detail_id => p_kanban_card_rec.document_detail_id
1231 , p_error_code => p_kanban_card_rec.ERROR_CODE
1232 , p_attribute_category => p_kanban_card_rec.attribute_category
1233 , p_attribute1 => p_kanban_card_rec.attribute1
1234 , p_attribute2 => p_kanban_card_rec.attribute2
1235 , p_attribute3 => p_kanban_card_rec.attribute3
1236 , p_attribute4 => p_kanban_card_rec.attribute4
1237 , p_attribute5 => p_kanban_card_rec.attribute5
1238 , p_attribute6 => p_kanban_card_rec.attribute6
1239 , p_attribute7 => p_kanban_card_rec.attribute7
1240 , p_attribute8 => p_kanban_card_rec.attribute8
1241 , p_attribute9 => p_kanban_card_rec.attribute9
1242 , p_attribute10 => p_kanban_card_rec.attribute10
1243 , p_attribute11 => p_kanban_card_rec.attribute11
1244 , p_attribute12 => p_kanban_card_rec.attribute12
1248 , p_lot_item_id => p_kanban_card_rec.lot_item_id
1245 , p_attribute13 => p_kanban_card_rec.attribute13
1246 , p_attribute14 => p_kanban_card_rec.attribute14
1247 , p_attribute15 => p_kanban_card_rec.attribute15
1249 , p_lot_number => p_kanban_card_rec.lot_number
1250 , p_lot_subinventory_code => p_kanban_card_rec.lot_subinventory_code
1251 , p_lot_item_revision => p_kanban_card_rec.lot_item_revision
1252 , p_lot_location_id => p_kanban_card_rec.lot_location_id
1253 , p_lot_quantity => p_kanban_card_rec.lot_quantity
1254 , p_replenish_quantity => p_kanban_card_rec.replenish_quantity
1255 , p_need_by_date => p_kanban_card_rec.need_by_date
1256 , p_source_wip_entity_id => p_kanban_card_rec.source_wip_entity_id
1257 );
1258
1259 IF l_return_status = fnd_api.g_ret_sts_error THEN
1260 RAISE fnd_api.g_exc_error;
1261 END IF;
1262
1263 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1264 RAISE fnd_api.g_exc_unexpected_error;
1265 END IF;
1266 EXCEPTION
1267 WHEN fnd_api.g_exc_error THEN
1268 RAISE fnd_api.g_exc_error;
1269 WHEN fnd_api.g_exc_unexpected_error THEN
1270 RAISE fnd_api.g_exc_unexpected_error;
1271 WHEN OTHERS THEN
1272 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1273 fnd_msg_pub.add_exc_msg(g_pkg_name, 'Update_Row');
1274 END IF;
1275
1276 RAISE fnd_api.g_exc_unexpected_error;
1277 END update_row;
1278
1279 PROCEDURE update_card_status(p_kanban_card_rec IN OUT NOCOPY inv_kanban_pvt.kanban_card_rec_type, p_card_status IN NUMBER) IS
1280 l_return_status VARCHAR2(1);
1281 l_card_status NUMBER;
1282 BEGIN
1283 fnd_msg_pub.initialize;
1284 l_card_status := p_card_status;
1285 update_row(
1286 x_return_status => l_return_status
1287 , p_kanban_card_id => p_kanban_card_rec.kanban_card_id
1288 , p_kanban_card_number => p_kanban_card_rec.kanban_card_number
1289 , p_pull_sequence_id => p_kanban_card_rec.pull_sequence_id
1290 , p_inventory_item_id => p_kanban_card_rec.inventory_item_id
1291 , p_organization_id => p_kanban_card_rec.organization_id
1292 , p_subinventory_name => p_kanban_card_rec.subinventory_name
1293 , p_supply_status => p_kanban_card_rec.supply_status
1294 , p_card_status => l_card_status
1295 , p_kanban_card_type => p_kanban_card_rec.kanban_card_type
1296 , p_source_type => p_kanban_card_rec.source_type
1297 , p_kanban_size => p_kanban_card_rec.kanban_size
1298 , p_last_update_date => p_kanban_card_rec.last_update_date
1299 , p_last_updated_by => p_kanban_card_rec.last_updated_by
1300 , p_creation_date => p_kanban_card_rec.creation_date
1301 , p_created_by => p_kanban_card_rec.created_by
1302 , p_last_update_login => p_kanban_card_rec.last_update_login
1303 , p_last_print_date => p_kanban_card_rec.last_print_date
1304 , p_locator_id => p_kanban_card_rec.locator_id
1305 , p_supplier_id => p_kanban_card_rec.supplier_id
1306 , p_supplier_site_id => p_kanban_card_rec.supplier_site_id
1307 , p_source_organization_id => p_kanban_card_rec.source_organization_id
1308 , p_source_subinventory => p_kanban_card_rec.source_subinventory
1309 , p_source_locator_id => p_kanban_card_rec.source_locator_id
1310 , p_wip_line_id => p_kanban_card_rec.wip_line_id
1311 , p_current_replnsh_cycle_id => p_kanban_card_rec.current_replnsh_cycle_id
1312 , p_document_type => p_kanban_card_rec.document_type
1313 , p_document_header_id => p_kanban_card_rec.document_header_id
1314 , p_document_detail_id => p_kanban_card_rec.document_detail_id
1315 , p_error_code => p_kanban_card_rec.ERROR_CODE
1316 , p_attribute_category => p_kanban_card_rec.attribute_category
1317 , p_attribute1 => p_kanban_card_rec.attribute1
1318 , p_attribute2 => p_kanban_card_rec.attribute2
1319 , p_attribute3 => p_kanban_card_rec.attribute3
1320 , p_attribute4 => p_kanban_card_rec.attribute4
1321 , p_attribute5 => p_kanban_card_rec.attribute5
1322 , p_attribute6 => p_kanban_card_rec.attribute6
1323 , p_attribute7 => p_kanban_card_rec.attribute7
1324 , p_attribute8 => p_kanban_card_rec.attribute8
1325 , p_attribute9 => p_kanban_card_rec.attribute9
1326 , p_attribute10 => p_kanban_card_rec.attribute10
1327 , p_attribute11 => p_kanban_card_rec.attribute11
1328 , p_attribute12 => p_kanban_card_rec.attribute12
1329 , p_attribute13 => p_kanban_card_rec.attribute13
1330 , p_attribute14 => p_kanban_card_rec.attribute14
1331 , p_attribute15 => p_kanban_card_rec.attribute15
1332 );
1333
1334 IF l_return_status = fnd_api.g_ret_sts_error THEN
1335 RAISE fnd_api.g_exc_error;
1336 END IF;
1337
1338 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1339 RAISE fnd_api.g_exc_unexpected_error;
1343 RAISE fnd_api.g_exc_error;
1340 END IF;
1341 EXCEPTION
1342 WHEN fnd_api.g_exc_error THEN
1344 WHEN fnd_api.g_exc_unexpected_error THEN
1345 RAISE fnd_api.g_exc_unexpected_error;
1346 WHEN OTHERS THEN
1347 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1348 fnd_msg_pub.add_exc_msg(g_pkg_name, 'Update_Card_Status');
1349 END IF;
1350
1351 RAISE fnd_api.g_exc_unexpected_error;
1352 END update_card_status;
1353
1354 PROCEDURE delete_row(x_return_status OUT NOCOPY VARCHAR2, p_kanban_card_id NUMBER) IS
1355 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
1356 BEGIN
1357 DELETE FROM mtl_kanban_cards
1358 WHERE kanban_card_id = p_kanban_card_id;
1359
1360 IF (SQL%NOTFOUND) THEN
1361 RAISE NO_DATA_FOUND;
1362 END IF;
1363
1364 delete_activity_for_card(p_kanban_card_id);
1365 x_return_status := l_return_status;
1366 EXCEPTION
1367 WHEN fnd_api.g_exc_error THEN
1368 x_return_status := fnd_api.g_ret_sts_error;
1369 WHEN fnd_api.g_exc_unexpected_error THEN
1370 x_return_status := fnd_api.g_ret_sts_unexp_error;
1371 WHEN OTHERS THEN
1372 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1373 fnd_msg_pub.add_exc_msg(g_pkg_name, 'Delete_Row');
1374 END IF;
1375
1376 x_return_status := fnd_api.g_ret_sts_unexp_error;
1377 END delete_row;
1378
1379 PROCEDURE insert_activity_for_card(p_kanban_card_rec IN inv_kanban_pvt.kanban_card_rec_type) IS
1380 BEGIN
1381 INSERT INTO mtl_kanban_card_activity
1382 (
1383 kanban_activity_id
1384 , replenishment_cycle_id
1385 , kanban_card_id
1386 , kanban_card_number
1387 , inventory_item_id
1388 , organization_id
1389 , subinventory_name
1390 , supply_status
1391 , card_status
1392 , kanban_card_type
1393 , source_type
1394 , kanban_size
1395 , last_update_date
1396 , last_updated_by
1397 , creation_date
1398 , created_by
1399 , locator_id
1400 , supplier_id
1401 , supplier_site_id
1402 , source_organization_id
1403 , source_subinventory
1404 , source_locator_id
1405 , wip_line_id
1406 , document_type
1407 , document_header_id
1408 , document_detail_id
1409 , ERROR_CODE
1410 , last_update_login
1411 , last_print_date
1412 , attribute_category
1413 , attribute1
1414 , attribute2
1415 , attribute3
1416 , attribute4
1417 , attribute5
1418 , attribute6
1419 , attribute7
1420 , attribute8
1421 , attribute9
1422 , attribute10
1423 , attribute11
1424 , attribute12
1425 , attribute13
1426 , attribute14
1427 , attribute15
1428 , request_id
1429 , program_application_id
1430 , program_id
1431 , program_update_date
1432 , source_wip_entity_id
1433 )
1434 VALUES (
1435 mtl_kanban_card_activity_s.NEXTVAL
1436 , NVL(p_kanban_card_rec.current_replnsh_cycle_id, -1)
1437 , p_kanban_card_rec.kanban_card_id
1438 , p_kanban_card_rec.kanban_card_number
1439 , p_kanban_card_rec.inventory_item_id
1440 , p_kanban_card_rec.organization_id
1441 , p_kanban_card_rec.subinventory_name
1442 , p_kanban_card_rec.supply_status
1443 , p_kanban_card_rec.card_status
1444 , p_kanban_card_rec.kanban_card_type
1445 , p_kanban_card_rec.source_type
1446 , NVL(p_kanban_card_rec.replenish_quantity, p_kanban_card_rec.kanban_size)
1447 , SYSDATE
1448 , fnd_global.user_id
1449 , SYSDATE
1450 , fnd_global.user_id
1451 , p_kanban_card_rec.locator_id
1452 , p_kanban_card_rec.supplier_id
1453 , p_kanban_card_rec.supplier_site_id
1454 , p_kanban_card_rec.source_organization_id
1455 , p_kanban_card_rec.source_subinventory
1456 , p_kanban_card_rec.source_locator_id
1457 , p_kanban_card_rec.wip_line_id
1458 , p_kanban_card_rec.document_type
1459 , p_kanban_card_rec.document_header_id
1460 , p_kanban_card_rec.document_detail_id
1461 , p_kanban_card_rec.ERROR_CODE
1462 , fnd_global.login_id
1463 , p_kanban_card_rec.last_print_date
1464 , p_kanban_card_rec.attribute_category
1465 , p_kanban_card_rec.attribute1
1466 , p_kanban_card_rec.attribute2
1467 , p_kanban_card_rec.attribute3
1468 , p_kanban_card_rec.attribute4
1469 , p_kanban_card_rec.attribute5
1470 , p_kanban_card_rec.attribute6
1471 , p_kanban_card_rec.attribute7
1472 , p_kanban_card_rec.attribute8
1473 , p_kanban_card_rec.attribute9
1474 , p_kanban_card_rec.attribute10
1475 , p_kanban_card_rec.attribute11
1476 , p_kanban_card_rec.attribute12
1477 , p_kanban_card_rec.attribute13
1478 , p_kanban_card_rec.attribute14
1479 , p_kanban_card_rec.attribute15
1480 , p_kanban_card_rec.request_id
1481 , p_kanban_card_rec.program_application_id
1482 , p_kanban_card_rec.program_id
1483 , p_kanban_card_rec.program_update_date
1484 , p_kanban_card_rec.source_wip_entity_id
1485 );
1486 EXCEPTION
1487 WHEN fnd_api.g_exc_error THEN
1488 RAISE fnd_api.g_exc_error;
1489 WHEN OTHERS THEN
1490 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1491 fnd_msg_pub.add_exc_msg(g_pkg_name, 'Insert_Activity_For_Card');
1492 END IF;
1493
1494 RAISE fnd_api.g_exc_unexpected_error;
1495 END insert_activity_for_card;
1496
1497 PROCEDURE delete_cards_for_pull_seq(p_pull_sequence_id NUMBER) IS
1498 BEGIN
1499 inv_kanbancard_pkg.delete_activity_for_pull_seq(p_pull_sequence_id);
1500
1501 DELETE FROM mtl_kanban_cards
1502 WHERE pull_sequence_id = p_pull_sequence_id;
1503 EXCEPTION
1504 WHEN fnd_api.g_exc_error THEN
1505 RAISE fnd_api.g_exc_error;
1506 WHEN OTHERS THEN
1507 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1508 fnd_msg_pub.add_exc_msg(g_pkg_name, 'Delete_Cards_For_Pull_Seq');
1509 END IF;
1510
1511 RAISE fnd_api.g_exc_unexpected_error;
1512 END delete_cards_for_pull_seq;
1513
1514 PROCEDURE delete_activity_for_card(p_kanban_card_id NUMBER) IS
1515 BEGIN
1516 DELETE FROM mtl_kanban_card_activity
1517 WHERE kanban_card_id = p_kanban_card_id;
1518 EXCEPTION
1519 WHEN fnd_api.g_exc_error THEN
1520 RAISE fnd_api.g_exc_error;
1521 WHEN OTHERS THEN
1522 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1523 fnd_msg_pub.add_exc_msg(g_pkg_name, 'Delete_Activity_For_Card');
1524 END IF;
1525
1526 RAISE fnd_api.g_exc_unexpected_error;
1527 END delete_activity_for_card;
1528
1529 PROCEDURE delete_activity_for_pull_seq(p_pull_sequence_id NUMBER) IS
1530 BEGIN
1531 DELETE FROM mtl_kanban_card_activity act
1532 WHERE EXISTS(SELECT 'x'
1533 FROM mtl_kanban_cards crd
1534 WHERE crd.kanban_card_id = act.kanban_card_id
1535 AND crd.pull_sequence_id = p_pull_sequence_id);
1536 EXCEPTION
1537 WHEN fnd_api.g_exc_error THEN
1538 RAISE fnd_api.g_exc_error;
1539 WHEN OTHERS THEN
1540 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1541 fnd_msg_pub.add_exc_msg(g_pkg_name, 'Delete_Activity_For_Pull_Seq');
1542 END IF;
1543
1544 RAISE fnd_api.g_exc_unexpected_error;
1545 END delete_activity_for_pull_seq;
1546 END inv_kanbancard_pkg;