[Home] [Help]
PACKAGE BODY: APPS.FLM_KANBAN_PUB
Source
1 PACKAGE BODY FLM_KANBAN_PUB AS
2 /* $Header: FLMKBNPB.pls 120.16.12020000.3 2012/08/09 12:19:56 akuppa ship $ */
3
4 --global variables
5 g_user_id number := fnd_global.user_id;
6 g_user_login_id number := fnd_global.login_id;
7 g_last_update_date date := sysdate;
8 g_creation_date date := sysdate;
9
10 G_PKG_NAME CONSTANT VARCHAR2(30) := 'FLM_KANBAN_PUB';
11
12
13 PROCEDURE mydebug(msg IN VARCHAR2) IS
14 BEGIN
15 inv_log_util.trace(msg, 'FLM_KANBAN_PUB', 9);
16 END mydebug;
17
18 -- private funtion to get pull sequence id
19 FUNCTION get_pull_sequence_id (p_invitem_id IN mtl_kanban_pull_sequences.inventory_item_id%TYPE
20 ,p_org_id IN mtl_kanban_pull_sequences.organization_id%TYPE
21 ,p_kan_plan_id IN mtl_kanban_pull_sequences.kanban_plan_id%TYPE
22 ,p_subinv_name IN mtl_kanban_pull_sequences.subinventory_name%TYPE
23 ,p_loc_id IN mtl_kanban_pull_sequences.locator_id%TYPE )
24 RETURN NUMBER
25 IS
26 CURSOR c_pull_seq_id
27 IS
28 SELECT mkps.pull_sequence_id
29 FROM mtl_kanban_pull_sequences mkps
30 WHERE mkps.inventory_item_id = p_invitem_id
31 AND mkps.organization_id = p_org_id
32 AND mkps.kanban_plan_id = NVL(p_kan_plan_id,mkps.kanban_plan_id)
33 AND TRIM ( mkps.subinventory_name ) = TRIM( p_subinv_name )
34 AND NVL ( mkps.locator_id,-1 ) = NVL ( p_loc_id,-1 );
35
36 l_pull_seq_id mtl_kanban_pull_sequences.pull_sequence_id%type;
37 BEGIN
38 OPEN c_pull_seq_id;
39 FETCH c_pull_seq_id INTO l_pull_seq_id;
40 CLOSE c_pull_seq_id;
41
42 return l_pull_seq_id;
43 END get_pull_sequence_id;
44
45 --Private function to validate pull sequence id
46 function is_valid_pull_sequence_id(p_pull_seq_id IN NUMBER)
47 return boolean
48 is
49 l_exist number;
50 begin
51 select 1
52 into l_exist
53 from mtl_kanban_pull_sequences
54 where pull_sequence_id = p_pull_seq_id;
55
56 return TRUE;
57 exception
58 when no_data_found then
59 return false;
60 when others then
61 return false;
62 end is_valid_pull_sequence_id;
63
64 --private function to validate kanban card
65 function is_valid_kanban_card(p_pull_sequence_id IN NUMBER
66 ,p_kanban_card_number IN VARCHAR2
67 ,p_kanban_card_id IN NUMBER
68 )
69 return boolean
70 is
71 l_card_id number;
72 l_card_number varchar2(200);
73 begin
74 select kanban_card_id
75 ,kanban_card_number
76 into l_card_id
77 ,l_card_number
78 from mtl_kanban_cards
79 where pull_sequence_id = p_pull_sequence_id
80 and kanban_card_id = NVL(p_kanban_card_id,kanban_card_id)
81 and kanban_card_number = NVL(p_kanban_card_number,kanban_card_number);
82
83 return TRUE;
84 exception
85 when no_data_found then
86 return FALSE;
87 when too_many_rows then
88 return TRUE;
89 when others then
90 return FALSE;
91 end is_valid_kanban_card;
92
93
94 -- private function to validate lookup code
95
96 function is_lookup_code_valid(p_lookup_type IN fnd_lookup_types.lookup_type%TYPE
97 ,p_lookup_code IN fnd_lookup_values.lookup_code%TYPE)
98 return boolean
99 is
100 l_exist number;
101 begin
102 SELECT 1
103 INTO l_exist
104 FROM fnd_lookup_types FLT
105 , fnd_lookup_values FLV
106 WHERE FLT.lookup_type = p_lookup_type
107 AND FLV.lookup_type = FLT.lookup_type
108 AND FLV.lookup_code = p_lookup_code
109 AND FLV.language = UserEnv('LANG');
110
111 return true;
112 exception
113 when no_data_found then
114 return false;
115 when others then
116 return false;
117 end is_lookup_code_valid;
118
119 -- private function to validate inventory item id
120 function is_inv_item_id_valid(p_inv_item_id in mtl_kanban_pull_sequences.inventory_item_id%TYPE
121 ,p_org_id in mtl_kanban_pull_sequences.organization_id%TYPE)
122 return boolean
123 is
124 l_exist number;
125 begin
126 SELECT 1
127 INTO l_exist
128 FROM mtl_system_items
129 WHERE inventory_item_id = p_inv_item_id
130 AND organization_id = p_org_id;
131
132 return true;
133 exception
134 when no_data_found then
135 return false;
136 when others then
137 return false;
138 end is_inv_item_id_valid;
139
140 -- private function to validate locator_id
141 function is_locator_id_valid(p_subinv_name in mtl_item_locations.subinventory_code%TYPE
142 ,p_locator_id in mtl_item_locations.inventory_location_id%TYPE
143 ,p_org_id in mtl_item_locations.organization_id%TYPE)
144 return boolean
145 is
146 l_exist number;
147 begin
148 SELECT 1
149 INTO l_exist
150 FROM mtl_item_locations mil
151 WHERE mil.subinventory_code = p_subinv_name
152 AND mil.inventory_location_id = p_locator_id
153 AND mil.organization_id = p_org_id;
154
155 return true;
156 exception
157 when others then
158 return false;
159 end is_locator_id_valid;
160
161 FUNCTION get_locator_control
162 ( p_inventory_item_id IN NUMBER,
163 p_organization_id IN NUMBER,
164 p_subinventory_name IN VARCHAR2
165 )
166 RETURN NUMBER
167 IS
168
169 CURSOR c_locator_control
170 IS
171 SELECT mp.stock_locator_control_code,
172 mss.locator_type,
173 msi.location_control_code
174 FROM mtl_parameters mp,
175 mtl_secondary_inventories mss,
176 mtl_system_items_b msi
177 WHERE mp.organization_id = p_organization_id
178 AND mss.secondary_inventory_name = p_subinventory_name
179 AND mss.organization_id = p_organization_id
180 AND msi.inventory_item_id = p_inventory_item_id
181 AND msi.organization_id = p_organization_id;
182
183 l_org_control NUMBER;
184 l_sub_control NUMBER;
185 l_item_control NUMBER;
186 l_locator_control NUMBER;
187
188 BEGIN
189
190 OPEN c_locator_control;
191 FETCH c_locator_control INTO l_org_control, l_sub_control, l_item_control;
192 CLOSE c_locator_control;
193
194 IF l_org_control = 4 THEN
195 IF l_sub_control = 5 THEN
196 l_locator_control := NVL(l_item_control, l_sub_control);
197 ELSE
198 l_locator_control := l_sub_control;
199 END IF;
200 ELSE
201 l_locator_control := l_org_control;
202 END IF;
203
204 RETURN l_locator_control;
205
206 END get_locator_control;
207
208 --------------------------
209 --Insert Supplier row
210 --------------------------
211 PROCEDURE insert_supplier_row(x_return_status OUT NOCOPY Varchar2,
212 p_pull_sequence_id NUMBER,
213 p_organization_id NUMBER,
214 p_supplier_id NUMBER,
215 p_supplier_site_id NUMBER,
216 p_sourcing_percentage NUMBER,
217 p_last_update_date DATE,
218 p_last_updated_by NUMBER,
219 p_creation_date DATE,
220 p_created_by NUMBER,
221 p_last_update_login NUMBER)
222 IS
223 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
224
225 BEGIN
226 FND_MSG_PUB.Initialize;
227
228 mydebug('Inserting into MTL_PULL_SEQ_SUPPLIERS table.');
229
230 INSERT INTO MTL_PULL_SEQ_SUPPLIERS
231 (pull_sequence_id,
232 organization_id,
233 supplier_id,
234 supplier_site_id,
235 sourcing_percentage,
236 last_update_date,
237 last_updated_by,
238 creation_date,
239 created_by,
240 last_update_login)
241 VALUES
242 (p_pull_sequence_id,
243 p_organization_id,
244 p_supplier_id,
245 p_supplier_site_id,
246 p_sourcing_percentage,
247 p_last_update_date,
248 p_last_updated_by,
249 p_creation_date,
250 p_created_by,
251 p_last_update_login);
252
253 x_return_status := l_return_status;
254 EXCEPTION
255 WHEN FND_API.G_EXC_ERROR THEN
256
257 x_return_status := FND_API.G_RET_STS_ERROR;
258
259 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
260
261 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
262
263 WHEN OTHERS THEN
264 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
265 THEN
266 FND_MSG_PUB.Add_Exc_Msg
267 ( G_PKG_NAME
268 , 'insert_supplier_row'
269 );
270 END IF;
271
272 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
273
274 END insert_supplier_row;
275
276 PROCEDURE insert_supplier_row(p_supplier_rec IN MTL_PULL_SEQ_SUPPLIERS%ROWTYPE)
277 IS
278 l_supplier_rec MTL_PULL_SEQ_SUPPLIERS%ROWTYPE := p_supplier_rec;
279 l_return_status VARCHAR2(1);
280 BEGIN
281 FND_MSG_PUB.Initialize;
282
283 mydebug('In procedure insert_supplier_row.');
284
285 insert_supplier_row(
286 x_return_status => l_return_status,
287 p_pull_sequence_id => l_supplier_rec.pull_sequence_id,
288 p_organization_id => l_supplier_rec.organization_id,
289 p_supplier_id => l_supplier_rec.supplier_id,
290 p_supplier_site_id => l_supplier_rec.supplier_site_id,
291 p_sourcing_percentage => l_supplier_rec.sourcing_percentage,
292 p_last_update_date => l_supplier_rec.last_update_date,
293 p_last_updated_by => l_supplier_rec.last_updated_by,
294 p_creation_date => l_supplier_rec.creation_date,
295 p_created_by => l_supplier_rec.created_by,
296 p_last_update_login => l_supplier_rec.last_update_login);
297
298 if l_return_status = FND_API.G_RET_STS_ERROR
299 Then
300 Raise FND_API.G_EXC_ERROR;
301 End if;
302
303 if l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
304 Then
305 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
306 End If;
307
308 EXCEPTION
309 WHEN FND_API.G_EXC_ERROR THEN
310
311 Raise FND_API.G_EXC_ERROR;
312
313 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
314
315 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
316
317 WHEN OTHERS THEN
318
319 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
320 THEN
321 FND_MSG_PUB.Add_Exc_Msg
322 ( G_PKG_NAME
323 , 'insert_supplier_row'
324 );
325 END IF;
326
327 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
328
329 END insert_supplier_row;
330
331 --------------------------
332 --Update Supplier row
333 --------------------------
334
335 PROCEDURE update_supplier_row(x_return_status OUT NOCOPY Varchar2,
336 p_pull_sequence_id NUMBER,
337 p_supplier_id NUMBER,
338 p_supplier_site_id NUMBER,
339 p_sourcing_percentage NUMBER,
340 p_last_update_date DATE,
341 p_last_updated_by NUMBER,
342 p_last_update_login NUMBER)
343 IS
344 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
345 BEGIN
346 FND_MSG_PUB.Initialize;
347
348 mydebug('Updating MTL_PULL_SEQ_SUPPLIERS table.');
349 -- Fix bug 12419603. Should allow user to update supplier site, only supplier should NOT be updated
350 -- because supplier_id is the primary key of the MTL_PULL_SEQ_SUPPLIERS table
351 UPDATE MTL_PULL_SEQ_SUPPLIERS
352 SET supplier_site_id = p_supplier_site_id,
353 sourcing_percentage = p_sourcing_percentage,
354 last_update_date = p_last_update_date,
355 last_updated_by = p_last_updated_by,
356 last_update_login = p_last_update_login
357 WHERE pull_sequence_id = p_pull_sequence_id
358 AND supplier_id = p_supplier_id;
359 -- AND nvl(supplier_site_id,-1) = nvl(p_supplier_site_id,-1);
360
361 if (SQL%NOTFOUND) then
362 Raise NO_DATA_FOUND;
363 end if;
364 x_return_status := l_return_status;
365
366 EXCEPTION
367
368 WHEN FND_API.G_EXC_ERROR THEN
369
370 x_return_status := FND_API.G_RET_STS_ERROR;
371
372 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
373
374 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
375
376 WHEN OTHERS THEN
377
378 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
379 THEN
380 FND_MSG_PUB.Add_Exc_Msg
381 ( G_PKG_NAME
382 , 'update_supplier_row'
383 );
384 END IF;
385
386 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
387
388 END update_supplier_row;
389
390 PROCEDURE update_supplier_row(p_supplier_rec IN MTL_PULL_SEQ_SUPPLIERS%ROWTYPE)
391 IS
392 l_supplier_rec MTL_PULL_SEQ_SUPPLIERS%ROWTYPE := p_supplier_rec;
393 l_return_status VARCHAR2(1);
394 BEGIN
395 FND_MSG_PUB.Initialize;
396
397 mydebug('In procedure update_supplier_row.');
398
399 update_supplier_row(x_return_status => l_return_status,
400 p_pull_sequence_id => l_supplier_rec.pull_sequence_id,
401 p_supplier_id => l_supplier_rec.supplier_id,
402 p_supplier_site_id => l_supplier_rec.supplier_site_id,
403 p_sourcing_percentage => l_supplier_rec.sourcing_percentage,
404 p_last_update_date => l_supplier_rec.last_update_date,
405 p_last_updated_by => l_supplier_rec.last_updated_by,
406 p_last_update_login => l_supplier_rec.last_update_login);
407
408 if l_return_status = FND_API.G_RET_STS_ERROR
409 Then
410 Raise FND_API.G_EXC_ERROR;
411 End if;
412
413 if l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
414 Then
415 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
416 End If;
417
418 EXCEPTION
419
420 WHEN FND_API.G_EXC_ERROR THEN
421
422 Raise FND_API.G_EXC_ERROR;
423
424 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
425
426 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
427
428 WHEN OTHERS THEN
429
430 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
431 THEN
432 FND_MSG_PUB.Add_Exc_Msg
433 ( G_PKG_NAME
434 , 'update_supplier_row'
435 );
436 END IF;
437 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
438 END update_supplier_row;
439
440 --------------------------
441 --Delete Supplier row
442 --------------------------
443
444 PROCEDURE delete_supplier_row(x_return_status OUT NOCOPY VARCHAR2,
445 p_pull_sequence_id NUMBER,
446 p_supplier_id NUMBER)
447 IS
448 l_return_status varchar2(1) := FND_API.G_RET_STS_ERROR;
449 BEGIN
450 FND_MSG_PUB.Initialize;
451
452 mydebug('In procedure delete_supplier_row.');
453
454 DELETE FROM MTL_PULL_SEQ_SUPPLIERS
455 WHERE pull_sequence_id = p_pull_sequence_id
456 AND supplier_id = p_supplier_id;
457
458 if (SQL%NOTFOUND) then
459 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
460 else
461 l_return_status := FND_API.G_RET_STS_SUCCESS;
462 end if;
463
464 x_return_status := l_return_status;
465
466 EXCEPTION
467
468 WHEN OTHERS THEN
469
470 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
471 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
472 THEN
473 FND_MSG_PUB.Add_Exc_Msg
474 ( G_PKG_NAME
475 , 'delete_supplier_row'
476 );
477 END IF;
478
479 END delete_supplier_row;
480
481 --------------------------------------------
482 -- Validate Pull Sequence - Private procedure
483 --------------------------------------------
484 procedure validate_pull_sequence(p_pull_seq_rec IN OUT NOCOPY flm_ekanban_pub.pull_sequence_rec_type
485 ,p_transaction_type NUMBER
486 ,x_ret_status OUT NOCOPY VARCHAR2
487 ,x_error_msg OUT NOCOPY VARCHAR2)
488 is
489 /*-- cursor to get pull seq data
490 CURSOR c_pull_seq_data(p_invitem_id IN mtl_kanban_pull_sequences.inventory_item_id%TYPE
491 ,p_org_id IN mtl_kanban_pull_sequences.organization_id%TYPE
492 ,p_kan_plan_id IN mtl_kanban_pull_sequences.kanban_plan_id%TYPE
493 ,p_sub_name IN mtl_kanban_pull_sequences.subinventory_name%TYPE
494 ,p_loc_id IN mtl_kanban_pull_sequences.locator_id%TYPE)
495 IS
496 SELECT mkps.inventory_item_id
497 ,mkps.organization_id
498 ,mkps.kanban_plan_id
499 ,mkps.subinventory_name
500 ,mkps.locator_id
501 -- ,mkps.supplier_id
502 -- ,mkps.supplier_site_id
503 ,mkps.source_organization_id
504 ,mkps.source_subinventory
505 ,mkps.source_locator_id
506 ,mkps.source_type
507 ,mkps.kanban_size
508 ,mkps.calculate_kanban_flag
509 ,mkps.number_of_cards
510 ,mkps.release_kanban_flag
511 FROM mtl_kanban_pull_sequences mkps
512 WHERE mkps.inventory_item_id = p_invitem_id
513 AND mkps.organization_id = p_org_id
514 AND mkps.kanban_plan_id = p_kan_plan_id
515 AND trim ( mkps.subinventory_name ) = trim( p_sub_name )
516 AND nvl ( mkps.locator_id,-1 ) = nvl ( p_loc_id,-1 );*/
517
518
519 -- cursor to validate inventory item
520 CURSOR c_inv_item(p_inventory_item_id number
521 ,p_organization_id number)
522 IS
523 SELECT msi.inventory_item_id
524 ,msi.planning_make_buy_code
525 ,msi.purchasing_enabled_flag
526 ,msi.internal_order_enabled_flag
527 ,msi.mtl_transactions_enabled_flag
528 ,msi.purchasing_item_flag
529 ,msi.reservable_type
530 ,msi.shippable_item_flag
531 ,msi.returnable_flag
532 ,msi.so_transactions_flag
533 ,msi.restrict_subinventories_code
534 ,msi.effectivity_control
535 ,msi.inventory_item_flag
536 ,msi.stock_enabled_flag
537 ,msi.inventory_item_status_code
538 FROM mtl_system_items msi
539 WHERE msi.inventory_item_id = p_inventory_item_id
540 AND msi.organization_id = p_organization_id;
541
542 -- cursor to validate subinventory
543 CURSOR c_subinv(p_subinv_name IN mtl_secondary_inventories.secondary_inventory_name%TYPE
544 ,p_org_id IN mtl_secondary_inventories.organization_id%TYPE)
545 IS
546 SELECT misi.secondary_inventory_name
547 FROM mtl_secondary_inventories misi
548 WHERE misi.secondary_inventory_name = p_subinv_name
549 AND ( misi.disable_date > SYSDATE
550 OR misi.disable_date IS NULL )
551 AND misi.organization_id = p_org_id;
552
553 -- cursor to validate number of cards/kanban size
554 CURSOR c_pull_seq_calc (p_kanban_size IN mtl_kanban_pull_sequences.kanban_size%TYPE
555 ,p_calculate_kanban_flag IN mtl_kanban_pull_sequences.calculate_kanban_flag%TYPE
556 ,p_number_of_cards IN mtl_kanban_pull_sequences.number_of_cards%TYPE
557 ,p_release_kanban_flag IN mtl_kanban_pull_sequences.release_kanban_flag%TYPE
558 ,p_pull_sequence_id IN mtl_kanban_pull_sequences.pull_sequence_id%TYPE
559 ,p_source_subinventory IN mtl_kanban_pull_sequences.source_subinventory%TYPE)
560 IS
561 SELECT NVL(p_kanban_size, mkps.kanban_size) kanban_size
562 ,NVL(p_calculate_kanban_flag, mkps.calculate_kanban_flag) calculate_kanban_flag
563 ,NVL(p_number_of_cards, mkps.number_of_cards) number_of_cards
564 ,NVL(p_release_kanban_flag, mkps.release_kanban_flag) release_kanban_flag
565 ,NVL(p_source_subinventory, mkps.source_subinventory) source_subinventory
566 ,mkps.source_organization_id
567 ,mkps.source_locator_id
568 FROM mtl_kanban_pull_sequences mkps
569 WHERE mkps.pull_sequence_id = p_pull_sequence_id;
570
571
572 lr_inv_item c_inv_item%rowtype;
573 --lr_pull_seq_data c_pull_seq_data%rowtype;
574 lr_pull_seq_calc c_pull_seq_calc%rowtype;
575
576 l_ret_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
577 l_org_code varchar2(3);
578 l_item_name varchar2(200);
579 l_calculate_kanban_flag number;
580 l_loc_name varchar2(200);
581 l_transaction_type number;
582 l_source_type number;
583 l_exists number;
584 l_pullseq_id number;
585
586 l_subinventory VARCHAR2(10);
587 l_source_subinventory VARCHAR2(10);
588 l_locator_control NUMBER;
589 l_source_locator_control NUMBER;
590 l_kanban_size Number;
591 l_no_of_cards Number;
592 begin
593
594 mydebug('In procedure validate_pull_sequence.');
595
596 -- verify whether all required fields are present
597
598 mydebug('Validating all required fields.');
599 -- Transaction type - pull seq to insert/update/delete
600 if p_transaction_type is null then
601 FND_MESSAGE.SET_NAME('FLM','FLM_ATTRIBUTE_REQUIRED');
602 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Transaction Type');
603 x_error_msg := x_error_msg||FND_CONST.NEWLINE||FND_MESSAGE.GET;
604 elsif p_transaction_type NOT IN (FLM_KANBAN_MASSLOAD.kanban_add,
605 FLM_KANBAN_MASSLOAD.kanban_change,
606 FLM_KANBAN_MASSLOAD.kanban_delete)
607 then
608 FND_MESSAGE.SET_NAME('FLM','FLM_ATTRIBUTE_INVALID');
609 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Transaction Type');
610 x_error_msg := x_error_msg||FND_CONST.NEWLINE||FND_MESSAGE.GET;
611 else
612 l_transaction_type := p_transaction_type;
613 end if;
614
615 -- validate organization
616 if p_pull_seq_rec.organization_id is null then
617 if p_pull_seq_rec.organization_code is not null then
618 p_pull_seq_rec.organization_id := default_org_id(p_pull_seq_rec.organization_code);
619
620 if p_pull_seq_rec.organization_id is null then
621 FND_MESSAGE.SET_NAME('FLM','FLM_ATTRIBUTE_INVALID');
622 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Organization Code');
623 x_error_msg := x_error_msg||FND_CONST.NEWLINE||FND_MESSAGE.GET;
624 end if;
625 end if;
626 elsif is_org_id_invalid(p_pull_seq_rec.organization_id) then
627 FND_MESSAGE.SET_NAME('FLM','FLM_ATTRIBUTE_INVALID');
628 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Organization');
629 x_error_msg := x_error_msg||FND_CONST.NEWLINE||FND_MESSAGE.GET;
630 end if;
631
632 -- Validate inventory item id
633 if p_pull_seq_rec.inventory_item_id is null then
634 if p_pull_seq_rec.concatenated_segments is not null then
635 p_pull_seq_rec.inventory_item_id := default_inv_item_id(p_org_id => p_pull_seq_rec.organization_id
636 ,p_conc_segments => p_pull_seq_rec.concatenated_segments);
637
638 if p_pull_seq_rec.inventory_item_id is null then
639 FND_MESSAGE.SET_NAME('FLM','FLM_ATTRIBUTE_INVALID');
640 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Concatenated segments/Item Name');
641 x_error_msg := x_error_msg||FND_CONST.NEWLINE||FND_MESSAGE.GET;
642 end if;
643 end if;
644 elsif not is_inv_item_id_valid(p_inv_item_id => p_pull_seq_rec.inventory_item_id
645 ,p_org_id => p_pull_seq_rec.organization_id)
646 then
647 FND_MESSAGE.SET_NAME('FLM','FLM_ATTRIBUTE_INVALID');
648 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Inventory Item');
649 x_error_msg := x_error_msg||FND_CONST.NEWLINE||FND_MESSAGE.GET;
650 end if;
651
652 -- Validate pull sequence
653 IF l_transaction_type = FLM_KANBAN_MASSLOAD.kanban_add THEN
654
655 l_pullseq_id := get_pull_sequence_id(p_pull_seq_rec.inventory_item_id,
656 p_pull_seq_rec.organization_id,
657 FLM_KANBAN_MASSLOAD.G_PRODUCTION_KANBAN,
658 p_pull_seq_rec.subinventory_name,
659 p_pull_seq_rec.locator_id);
660
661 -- if pull sequence already exists or pull_sequence_id is already used
662 IF l_pullseq_id IS NOT NULL OR is_valid_pull_sequence_id(p_pull_seq_rec.pull_sequence_id) THEN
663
664 Get_Pull_Sequence_Tokens
665 ( p_pull_sequence_id => NVL(l_pullseq_id, p_pull_seq_rec.pull_sequence_id),
666 p_organization_id => p_pull_seq_rec.organization_id,
667 p_inventory_item_id => p_pull_seq_rec.inventory_item_id,
668 p_locator_id => p_pull_seq_rec.locator_id,
669 x_org_code => l_org_code,
670 x_item_name => l_item_name,
671 x_subinventory => l_subinventory,
672 x_loc_name => l_loc_name
673 );
674
675 FND_MESSAGE.SET_NAME('INV', 'INV_PULLSEQ_EXISTS');
676 FND_MESSAGE.SET_TOKEN('ORG_CODE', l_org_code);
677 FND_MESSAGE.SET_TOKEN('ITEM_NAME', l_item_name );
678 FND_MESSAGE.SET_TOKEN('SUB_CODE', NVL(l_subinventory, p_pull_seq_rec.subinventory_name));
679 FND_MESSAGE.SET_TOKEN('LOCATOR_NAME', l_loc_name);
680 x_error_msg := x_error_msg||FND_CONST.NEWLINE||FND_MESSAGE.GET;
681
682 END IF;
683
684 ELSIF l_transaction_type IN (FLM_KANBAN_MASSLOAD.kanban_change,
685 FLM_KANBAN_MASSLOAD.kanban_delete) THEN
686
687 IF p_pull_seq_rec.pull_sequence_id IS NULL THEN
688 l_pullseq_id := get_pull_sequence_id(p_pull_seq_rec.inventory_item_id,
689 p_pull_seq_rec.organization_id,
690 FLM_KANBAN_MASSLOAD.G_PRODUCTION_KANBAN,
691 p_pull_seq_rec.subinventory_name,
692 p_pull_seq_rec.locator_id);
693 p_pull_seq_rec.pull_sequence_id := l_pullseq_id;
694 ELSIF is_valid_pull_sequence_id(p_pull_seq_rec.pull_sequence_id) THEN
695 l_pullseq_id := p_pull_seq_rec.pull_sequence_id;
696 END IF;
697
698 IF l_pullseq_id IS NULL THEN
699
700 Get_Pull_Sequence_Tokens
701 ( p_pull_sequence_id => NULL,
702 p_organization_id => p_pull_seq_rec.organization_id,
703 p_inventory_item_id => p_pull_seq_rec.inventory_item_id,
704 p_locator_id => p_pull_seq_rec.locator_id,
705 x_org_code => l_org_code,
706 x_item_name => l_item_name,
707 x_subinventory => l_subinventory,
708 x_loc_name => l_loc_name
709 );
710
711 FND_MESSAGE.SET_NAME('FLM', 'FLM_PULLSEQ_NOT_EXISTS');
712 FND_MESSAGE.SET_TOKEN('ORG_CODE', l_org_code);
713 FND_MESSAGE.SET_TOKEN('ITEM_NAME', l_item_name );
714 FND_MESSAGE.SET_TOKEN('SUB_CODE', NVL(l_subinventory, p_pull_seq_rec.subinventory_name));
715 FND_MESSAGE.SET_TOKEN('LOCATOR_NAME', l_loc_name);
716 x_error_msg := x_error_msg||FND_CONST.NEWLINE||FND_MESSAGE.GET;
717
718 END IF;
719
720 END IF;
721
722 -- Default pull sequence values while updating..
723
724 if l_transaction_type = FLM_KANBAN_MASSLOAD.kanban_change and l_pullseq_id is not null then
725
726 SELECT
727 mkps.pull_sequence_id
728 ,mkps.creation_date
729 ,mkps.created_by
730 ,mkps.inventory_item_id
731 ,mkps.organization_id
732 ,mkps.subinventory_name
733 ,mkps.locator_id
734 ,decode(p_pull_seq_rec.source_type,null,mkps.source_type,FND_API.G_MISS_NUM,null,p_pull_seq_rec.source_type)
735 ,decode(p_pull_seq_rec.supplier_id,null,mkps.supplier_id,FND_API.G_MISS_NUM,null,p_pull_seq_rec.supplier_id)
736 ,decode(p_pull_seq_rec.supplier_site_id,null,mkps.supplier_site_id,FND_API.G_MISS_NUM,null,p_pull_seq_rec.supplier_site_id)
737 ,decode(p_pull_seq_rec.source_organization_id,null,mkps.source_organization_id,FND_API.G_MISS_NUM,null,p_pull_seq_rec.source_organization_id)
738 ,decode(p_pull_seq_rec.source_subinventory,null,mkps.source_subinventory,FND_API.G_MISS_CHAR,null,p_pull_seq_rec.source_subinventory)
739 ,decode(p_pull_seq_rec.source_locator_id,null,mkps.source_locator_id,FND_API.G_MISS_NUM,null,p_pull_seq_rec.source_locator_id)
740 ,decode(p_pull_seq_rec.wip_line_id,null,mkps.wip_line_id,FND_API.G_MISS_NUM,null,p_pull_seq_rec.wip_line_id)
741 ,decode(p_pull_seq_rec.replenishment_lead_time,null,mkps.replenishment_lead_time,FND_API.G_MISS_NUM,null,p_pull_seq_rec.replenishment_lead_time)
742 ,decode(p_pull_seq_rec.calculate_kanban_flag,null,mkps.calculate_kanban_flag,FND_API.G_MISS_NUM,null,p_pull_seq_rec.calculate_kanban_flag)
743 ,decode(p_pull_seq_rec.kanban_size,null,mkps.kanban_size,FND_API.G_MISS_NUM,null,p_pull_seq_rec.kanban_size)
744 ,decode(p_pull_seq_rec.fixed_lot_multiplier,null,mkps.fixed_lot_multiplier,FND_API.G_MISS_NUM,null,p_pull_seq_rec.fixed_lot_multiplier)
745 ,decode(p_pull_seq_rec.safety_stock_days,null,mkps.safety_stock_days,FND_API.G_MISS_NUM,null,p_pull_seq_rec.safety_stock_days)
746 ,decode(p_pull_seq_rec.number_of_cards,null,mkps.number_of_cards,FND_API.G_MISS_NUM,null,p_pull_seq_rec.number_of_cards)
747 ,decode(p_pull_seq_rec.minimum_order_quantity,null,mkps.minimum_order_quantity,FND_API.G_MISS_NUM,null,p_pull_seq_rec.minimum_order_quantity)
748 ,decode(p_pull_seq_rec.aggregation_type,null,mkps.aggregation_type,FND_API.G_MISS_NUM,null,p_pull_seq_rec.aggregation_type)
749 ,decode(p_pull_seq_rec.allocation_percent,null,mkps.allocation_percent,FND_API.G_MISS_NUM,null,p_pull_seq_rec.allocation_percent)
750 ,decode(p_pull_seq_rec.release_kanban_flag,null,mkps.release_kanban_flag,FND_API.G_MISS_NUM,null,p_pull_seq_rec.release_kanban_flag)
751 ,decode(p_pull_seq_rec.attribute_category,null,mkps.attribute_category,FND_API.G_MISS_CHAR,null,p_pull_seq_rec.attribute_category)
752 ,decode(p_pull_seq_rec.attribute1,null,mkps.attribute1,FND_API.G_MISS_CHAR,null,p_pull_seq_rec.attribute1)
753 ,decode(p_pull_seq_rec.attribute2,null,mkps.attribute2,FND_API.G_MISS_CHAR,null,p_pull_seq_rec.attribute2)
754 ,decode(p_pull_seq_rec.attribute3,null,mkps.attribute3,FND_API.G_MISS_CHAR,null,p_pull_seq_rec.attribute3)
755 ,decode(p_pull_seq_rec.attribute4,null,mkps.attribute4,FND_API.G_MISS_CHAR,null,p_pull_seq_rec.attribute4)
756 ,decode(p_pull_seq_rec.attribute5,null,mkps.attribute5,FND_API.G_MISS_CHAR,null,p_pull_seq_rec.attribute5)
757 ,decode(p_pull_seq_rec.attribute6,null,mkps.attribute6,FND_API.G_MISS_CHAR,null,p_pull_seq_rec.attribute6)
758 ,decode(p_pull_seq_rec.attribute7,null,mkps.attribute7,FND_API.G_MISS_CHAR,null,p_pull_seq_rec.attribute7)
759 ,decode(p_pull_seq_rec.attribute8,null,mkps.attribute8,FND_API.G_MISS_CHAR,null,p_pull_seq_rec.attribute8)
760 ,decode(p_pull_seq_rec.attribute9,null,mkps.attribute9,FND_API.G_MISS_CHAR,null,p_pull_seq_rec.attribute9)
761 ,decode(p_pull_seq_rec.attribute10,null,mkps.attribute10,FND_API.G_MISS_CHAR,null,p_pull_seq_rec.attribute10)
762 ,decode(p_pull_seq_rec.attribute11,null,mkps.attribute11,FND_API.G_MISS_CHAR,null,p_pull_seq_rec.attribute11)
763 ,decode(p_pull_seq_rec.attribute12,null,mkps.attribute12,FND_API.G_MISS_CHAR,null,p_pull_seq_rec.attribute12)
764 ,decode(p_pull_seq_rec.attribute13,null,mkps.attribute13,FND_API.G_MISS_CHAR,null,p_pull_seq_rec.attribute13)
765 ,decode(p_pull_seq_rec.attribute14,null,mkps.attribute14,FND_API.G_MISS_CHAR,null,p_pull_seq_rec.attribute14)
766 ,decode(p_pull_seq_rec.attribute15,null,mkps.attribute15,FND_API.G_MISS_CHAR,null,p_pull_seq_rec.attribute15)
767 ,decode(p_pull_seq_rec.auto_request,null,mkps.auto_request,FND_API.G_MISS_CHAR,null,p_pull_seq_rec.auto_request)
768 ,decode(p_pull_seq_rec.auto_allocate_flag,null,mkps.auto_allocate_flag,FND_API.G_MISS_CHAR,null,p_pull_seq_rec.auto_allocate_flag)
769 ,decode(p_pull_seq_rec.replenishment_type,null,mkps.replenishment_type,FND_API.G_MISS_NUM,null,p_pull_seq_rec.replenishment_type)
770 ,decode(p_pull_seq_rec.consolidation,null,mkps.consolidation,FND_API.G_MISS_NUM,null,p_pull_seq_rec.consolidation)
771 ,decode(p_pull_seq_rec.consolidation_group,null,mkps.consolidation_group,FND_API.G_MISS_CHAR,null,p_pull_seq_rec.consolidation_group)
772 ,decode(p_pull_seq_rec.future_card_size,null,mkps.future_card_size,FND_API.G_MISS_NUM,null,p_pull_seq_rec.future_card_size)
773 ,decode(p_pull_seq_rec.future_no_of_cards,null,mkps.future_no_of_cards,FND_API.G_MISS_NUM,null,p_pull_seq_rec.future_no_of_cards)
774 ,decode(p_pull_seq_rec.planning_effectivity,null,mkps.planning_effectivity,FND_API.G_MISS_DATE,null,p_pull_seq_rec.planning_effectivity)
775 ,decode(p_pull_seq_rec.avg_dependent_demand,null,mkps.avg_dependent_demand,FND_API.G_MISS_NUM,null,p_pull_seq_rec.avg_dependent_demand)
776 ,decode(p_pull_seq_rec.avg_independent_demand,null,mkps.avg_independent_demand,FND_API.G_MISS_NUM,null,p_pull_seq_rec.avg_independent_demand)
777 ,mkps.kanban_size
778 ,mkps.number_of_cards
779 INTO
780 p_pull_seq_rec.pull_sequence_id
781 ,p_pull_seq_rec.creation_date
782 ,p_pull_seq_rec.created_by
783 ,p_pull_seq_rec.inventory_item_id
784 ,p_pull_seq_rec.organization_id
785 ,p_pull_seq_rec.subinventory_name
786 ,p_pull_seq_rec.locator_id
787 ,p_pull_seq_rec.source_type
788 ,p_pull_seq_rec.supplier_id
789 ,p_pull_seq_rec.supplier_site_id
790 ,p_pull_seq_rec.source_organization_id
791 ,p_pull_seq_rec.source_subinventory
792 ,p_pull_seq_rec.source_locator_id
793 ,p_pull_seq_rec.wip_line_id
794 ,p_pull_seq_rec.replenishment_lead_time
795 ,p_pull_seq_rec.calculate_kanban_flag
796 ,p_pull_seq_rec.kanban_size
797 ,p_pull_seq_rec.fixed_lot_multiplier
798 ,p_pull_seq_rec.safety_stock_days
799 ,p_pull_seq_rec.number_of_cards
800 ,p_pull_seq_rec.minimum_order_quantity
801 ,p_pull_seq_rec.aggregation_type
802 ,p_pull_seq_rec.allocation_percent
803 ,p_pull_seq_rec.release_kanban_flag
804 ,p_pull_seq_rec.attribute_category
805 ,p_pull_seq_rec.attribute1
806 ,p_pull_seq_rec.attribute2
807 ,p_pull_seq_rec.attribute3
808 ,p_pull_seq_rec.attribute4
809 ,p_pull_seq_rec.attribute5
810 ,p_pull_seq_rec.attribute6
811 ,p_pull_seq_rec.attribute7
812 ,p_pull_seq_rec.attribute8
813 ,p_pull_seq_rec.attribute9
814 ,p_pull_seq_rec.attribute10
815 ,p_pull_seq_rec.attribute11
816 ,p_pull_seq_rec.attribute12
817 ,p_pull_seq_rec.attribute13
818 ,p_pull_seq_rec.attribute14
819 ,p_pull_seq_rec.attribute15
820 ,p_pull_seq_rec.auto_request
821 ,p_pull_seq_rec.auto_allocate_flag
822 ,p_pull_seq_rec.replenishment_type
823 ,p_pull_seq_rec.consolidation
824 ,p_pull_seq_rec.consolidation_group
825 ,p_pull_seq_rec.future_card_size
826 ,p_pull_seq_rec.future_no_of_cards
827 ,p_pull_seq_rec.planning_effectivity
828 ,p_pull_seq_rec.avg_dependent_demand
829 ,p_pull_seq_rec.avg_independent_demand
830 ,l_kanban_size
831 ,l_no_of_cards
832 FROM mtl_kanban_pull_sequences mkps
833 WHERE pull_sequence_id = l_pullseq_id;
834
835 end if;
836
837 mydebug('Validating mandatory fields.');
838
839 if l_transaction_type = FLM_KANBAN_MASSLOAD.kanban_add then
840
841 -- validate organization
842 IF p_pull_seq_rec.organization_id IS NULL AND
843 p_pull_seq_rec.organization_code IS NULL
844 THEN
845 FND_MESSAGE.SET_NAME('FLM','FLM_ATTRIBUTE_REQUIRED');
846 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Organization Code');
847 x_error_msg := x_error_msg||FND_CONST.NEWLINE||FND_MESSAGE.GET;
848 END IF;
849
850 -- Inventory Item
851 IF p_pull_seq_rec.inventory_item_id IS NULL AND
852 p_pull_seq_rec.concatenated_segments IS NULL
853 THEN
854 FND_MESSAGE.SET_NAME('FLM','FLM_ATTRIBUTE_REQUIRED');
855 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Concatenated segments/Item Name');
856 x_error_msg := x_error_msg||FND_CONST.NEWLINE||FND_MESSAGE.GET;
857 END IF;
858
859 OPEN c_inv_item(p_pull_seq_rec.inventory_item_id
860 ,p_pull_seq_rec.organization_id);
861 FETCH c_inv_item into lr_inv_item;
862
863 IF lr_inv_item.inventory_item_flag = 'N' THEN
864 FND_MESSAGE.SET_NAME('FLM','FLM_ITEM_FLAG');
865 x_error_msg := x_error_msg||FND_CONST.NEWLINE||FND_MESSAGE.GET;
866 END IF;
867
868 IF lr_inv_item.effectivity_control = 2 THEN
869 FND_MESSAGE.SET_NAME('INV','INV_NO_EFFECTIVITY_CONTROL');
870 x_error_msg := x_error_msg||FND_CONST.NEWLINE||FND_MESSAGE.GET;
871 END IF;
872
873 IF lr_inv_item.stock_enabled_flag = 'N' THEN
874 FND_MESSAGE.SET_NAME('INV','INV_NOT_STOCK_ENABLED');
875 x_error_msg := x_error_msg||FND_CONST.NEWLINE||FND_MESSAGE.GET;
876 END IF;
877
878 IF lr_inv_item.mtl_transactions_enabled_flag = 'N' THEN
879 FND_MESSAGE.SET_NAME('FLM','FLM_TRX_ENABLED_FLAG');
880 x_error_msg := x_error_msg||FND_CONST.NEWLINE||FND_MESSAGE.GET;
881 END IF;
882
883 IF lr_inv_item.inventory_item_status_code <> 'Active' THEN
884 FND_MESSAGE.SET_NAME('FLM','FLM_ITEM_STATUS_CODE');
885 x_error_msg := x_error_msg||FND_CONST.NEWLINE||FND_MESSAGE.GET;
886 END IF;
887
888 CLOSE c_inv_item;
889
890 -- Subinventory name
891
892 OPEN c_subinv(p_subinv_name => p_pull_seq_rec.subinventory_name
893 ,p_org_id => p_pull_seq_rec.organization_id);
894 FETCH c_subinv INTO l_subinventory;
895 CLOSE c_subinv;
896
897 if l_subinventory IS NULL THEN
898 FND_MESSAGE.SET_NAME('FLM','FLM_ATTRIBUTE_INVALID');
899 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Subinventory');
900 x_error_msg := x_error_msg||FND_CONST.NEWLINE||FND_MESSAGE.GET;
901 end if;
902
903 l_locator_control := get_locator_control(p_pull_seq_rec.inventory_item_id,
904 p_pull_seq_rec.organization_id,
905 p_pull_seq_rec.subinventory_name);
906
907 -- Validate locator
908 IF l_locator_control <> FLM_KANBAN_MASSLOAD.LOCATOR_NONE AND
909 p_pull_seq_rec.locator_id IS NULL
910 THEN
911 FND_MESSAGE.SET_NAME('FLM','FLM_ATTRIBUTE_REQUIRED');
912 FND_MESSAGE.SET_TOKEN('ATTRIBUTE', 'Locator');
913 x_error_msg := x_error_msg||FND_CONST.NEWLINE||FND_MESSAGE.GET;
914 END IF;
915
916 IF p_pull_seq_rec.locator_id IS NOT NULL AND
917 NOT is_locator_id_valid(p_subinv_name => p_pull_seq_rec.subinventory_name,
918 p_locator_id => p_pull_seq_rec.locator_id,
919 p_org_id => p_pull_seq_rec.organization_id)
920 THEN
921 FND_MESSAGE.SET_NAME('FLM','FLM_ATTRIBUTE_INVALID');
922 FND_MESSAGE.SET_TOKEN('ATTRIBUTE', 'Locator');
923 x_error_msg := x_error_msg||FND_CONST.NEWLINE||FND_MESSAGE.GET;
924 END IF;
925
926 end if; -- l_transaction_type = kanban_add
927
928 -- Validate the Source Type - Inter Org, Intra Org, Supplier, Production
929
930 /*if l_transaction_type = FLM_KANBAN_MASSLOAD.kanban_change then
931 open c_pull_seq_data(p_invitem_id => p_pull_seq_rec.inventory_item_id
932 ,p_org_id => p_pull_seq_rec.organization_id
933 ,p_kan_plan_id => FLM_KANBAN_MASSLOAD.G_PRODUCTION_KANBAN
934 ,p_sub_name => p_pull_seq_rec.subinventory_name
935 ,p_loc_id => p_pull_seq_rec.locator_id );
936
937 fetch c_pull_seq_data into lr_pull_seq_data;
938 close c_pull_seq_data;
939 end if;*/
940
941 /*if is_lookup_code_valid(p_lookup_type => 'MTL_KANBAN_SOURCE_TYPE'
942 ,p_lookup_code => p_pull_seq_rec.source_type)
943 then
944 l_source_type := p_pull_seq_rec.source_type;
945 end if;
946
947 if l_transaction_type = FLM_KANBAN_MASSLOAD.kanban_change and
948 p_pull_seq_rec.source_type is null
949 then
950 l_source_type := lr_pull_seq_data.source_type;
951 end if;*/
952
953
954 if l_transaction_type IN (FLM_KANBAN_MASSLOAD.kanban_add,FLM_KANBAN_MASSLOAD.kanban_change) then
955
956 -- Validate the Source Type - Inter Org, Intra Org, Supplier, Production
957 if is_lookup_code_valid(p_lookup_type => 'MTL_KANBAN_SOURCE_TYPE'
958 ,p_lookup_code => p_pull_seq_rec.source_type)
959 then
960 l_source_type := p_pull_seq_rec.source_type;
961 end if;
962
963 OPEN c_inv_item(p_pull_seq_rec.inventory_item_id
964 ,p_pull_seq_rec.organization_id);
965 FETCH c_inv_item into lr_inv_item;
966
967 if lr_inv_item.inventory_item_id is not null then
968
969 if l_source_type = inv_kanban_pvt.G_Source_Type_InterOrg then
970 --item is not inter org enabled and inter org source type selected
971 IF (NVL(lr_inv_item.internal_order_enabled_flag, 'N' ) <> 'Y' ) THEN
972 FND_MESSAGE.SET_NAME ( 'INV' , 'INV_ENTER_INTORD_ITEM' );
973 x_error_msg := x_error_msg||FND_CONST.NEWLINE||FND_MESSAGE.GET;
974 END IF;
975
976 IF (lr_inv_item.shippable_item_flag = 'N' ) THEN
977 FND_MESSAGE.SET_NAME ( 'FLM' , 'FLM_SHIPPABLE_ITEM' );
978 x_error_msg := x_error_msg||FND_CONST.NEWLINE||FND_MESSAGE.GET;
979 END IF;
980
981 /* IF (lr_inv_item.returnable_flag = 'N' ) THEN
982 FND_MESSAGE.SET_NAME ( 'FLM' , 'FLM_RETURNABLE_ITEM' );
983 x_error_msg := x_error_msg||FND_CONST.NEWLINE||FND_MESSAGE.GET;
984 END IF;
985 Bug#13929621- Returnable items should be allowed for interorg transfer
986 */
987
988 IF (lr_inv_item.so_transactions_flag = 'N' ) THEN
989 FND_MESSAGE.SET_NAME ( 'FLM' , 'FLM_INTERORG_ITEM' );
990 x_error_msg := x_error_msg||FND_CONST.NEWLINE||FND_MESSAGE.GET;
991 END IF;
992
993 elsif l_source_type = inv_kanban_pvt.G_Source_Type_Supplier then
994 --if item is not purchase enabled and Supplier source type selected
995 IF (NVL(lr_inv_item.purchasing_enabled_flag, 'N' ) <> 'Y') THEN
996 FND_MESSAGE.SET_NAME ( 'FLM' , 'FLM_PURCHASE_ENABLED_ITEM' );
997 x_error_msg := x_error_msg||FND_CONST.NEWLINE||FND_MESSAGE.GET;
998 END IF;
999
1000 IF (NVL(lr_inv_item.purchasing_item_flag, 'N' ) <> 'Y') THEN
1001 FND_MESSAGE.SET_NAME ( 'INV' , 'INV_ENTER_PURCHASE_ITEM' );
1002 x_error_msg := x_error_msg||FND_CONST.NEWLINE||FND_MESSAGE.GET;
1003 END IF;
1004
1005 elsif l_source_type = inv_kanban_pvt.G_Source_Type_IntraOrg then
1006 --if the item is not transactable and source_type 'Intra-Org' is selected
1007 IF (NVL(lr_inv_item.mtl_transactions_enabled_flag, 'N' ) <> 'Y') THEN
1008 FND_MESSAGE.SET_NAME ( 'INV' , 'INV_ENTER_TRANSACT_ITEM' );
1009 x_error_msg := x_error_msg||FND_CONST.NEWLINE||FND_MESSAGE.GET;
1010 END IF;
1011
1012 elsif l_source_type = inv_kanban_pvt.G_Source_Type_Production then
1013 --if item is not production enabled and production source type selected
1014 IF (NVL(lr_inv_item.planning_make_buy_code, 0 ) <> 1 ) THEN
1015 FND_MESSAGE.SET_NAME ( 'INV' , 'INV_ENTER_MAKE_ITEM' );
1016 x_error_msg := x_error_msg||FND_CONST.NEWLINE||FND_MESSAGE.GET;
1017 END IF;
1018
1019 else
1020 FND_MESSAGE.SET_NAME ('INV' ,'INV-BAD SOURCE TYPE');
1021 x_error_msg := x_error_msg||FND_CONST.NEWLINE||FND_MESSAGE.GET;
1022 end if;
1023
1024 -- checking if item is a reservable item
1025 IF (lr_inv_item.reservable_type = 2 AND l_source_type IN (inv_kanban_pvt.G_Source_Type_InterOrg
1026 ,inv_kanban_pvt.G_Source_Type_IntraOrg))
1027 THEN
1028 FND_MESSAGE.SET_NAME ('FLM' , 'FLM_NOT_RESERVABLE_ITEM');
1029 x_error_msg := x_error_msg||FND_CONST.NEWLINE||FND_MESSAGE.GET;
1030 END IF;
1031 CLOSE c_inv_item;
1032
1033 end if;
1034
1035 -- Validate source subinventory and source org id
1036 -- if source type is inter or intra org
1037 if l_source_type IN (INV_Kanban_PVT.G_Source_type_IntraOrg
1038 ,INV_Kanban_PVT.G_Source_Type_InterOrg)
1039 then
1040
1041 /*if p_pull_seq_rec.source_organization_id is null
1042 then
1043 FND_MESSAGE.SET_NAME('FLM','FLM_ATTRIBUTE_REQUIRED');
1044 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Source Organization Id');
1045 x_error_msg := x_error_msg||FND_CONST.NEWLINE||FND_MESSAGE.GET;
1046 else*/
1047
1048 --Default Source org id as org id if intra org
1049 if l_source_type = INV_Kanban_PVT.G_Source_type_IntraOrg then
1050 p_pull_seq_rec.source_organization_id := p_pull_seq_rec.organization_id;
1051 end if;
1052
1053 if p_pull_seq_rec.source_organization_id is not null then
1054 if p_pull_seq_rec.source_subinventory is null
1055 then
1056 FND_MESSAGE.SET_NAME('FLM','FLM_ATTRIBUTE_REQUIRED');
1057 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Source Subinventory',TRUE);
1058 x_error_msg := x_error_msg||FND_CONST.NEWLINE||FND_MESSAGE.GET;
1059 else
1060 -- validate source subinventory
1061 OPEN c_subinv(p_subinv_name => p_pull_seq_rec.source_subinventory
1062 ,p_org_id => p_pull_seq_rec.source_organization_id);
1063 FETCH c_subinv INTO l_source_subinventory;
1064 CLOSE c_subinv;
1065
1066 if l_source_subinventory IS NULL THEN
1067 FND_MESSAGE.SET_NAME('FLM','FLM_ATTRIBUTE_INVALID');
1068 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Source Subinventory');
1069 x_error_msg := x_error_msg||FND_CONST.NEWLINE||FND_MESSAGE.GET;
1070 end if;
1071 end if;
1072 else -- source org_id is null and source type Inter org
1073 p_pull_seq_rec.source_subinventory := null;
1074 p_pull_seq_rec.source_locator_id := null;
1075 end if;
1076
1077 l_source_locator_control := get_locator_control(p_pull_seq_rec.inventory_item_id,
1078 p_pull_seq_rec.source_organization_id,
1079 p_pull_seq_rec.source_subinventory);
1080
1081 -- Validate source locator
1082 IF l_source_locator_control <> FLM_KANBAN_MASSLOAD.LOCATOR_NONE AND
1083 p_pull_seq_rec.source_locator_id IS NULL
1084 THEN
1085 FND_MESSAGE.SET_NAME('FLM','FLM_ATTRIBUTE_REQUIRED');
1086 FND_MESSAGE.SET_TOKEN('ATTRIBUTE', 'Source Locator');
1087 x_error_msg := x_error_msg||FND_CONST.NEWLINE||FND_MESSAGE.GET;
1088 END IF;
1089
1090 IF p_pull_seq_rec.source_locator_id IS NOT NULL AND
1091 NOT is_locator_id_valid(p_subinv_name => p_pull_seq_rec.source_subinventory,
1092 p_locator_id => p_pull_seq_rec.source_locator_id,
1093 p_org_id => p_pull_seq_rec.source_organization_id)
1094 THEN
1095 FND_MESSAGE.SET_NAME('FLM','FLM_ATTRIBUTE_INVALID');
1096 FND_MESSAGE.SET_TOKEN('ATTRIBUTE', 'Source Locator');
1097 x_error_msg := x_error_msg||FND_CONST.NEWLINE||FND_MESSAGE.GET;
1098 END IF;
1099
1100 IF p_pull_seq_rec.organization_id = p_pull_seq_rec.source_organization_id AND
1101 p_pull_seq_rec.subinventory_name = p_pull_seq_rec.source_subinventory AND
1102 NVL(p_pull_seq_rec.locator_id, -1) = NVL(p_pull_seq_rec.source_locator_id, -1)
1103 THEN
1104 Get_Pull_Sequence_Tokens
1105 ( p_Pull_Sequence_Id => NULL,
1106 p_organization_id => p_pull_seq_rec.organization_id,
1107 p_inventory_item_id => p_pull_seq_rec.inventory_item_id,
1108 p_locator_id => p_pull_seq_rec.locator_id,
1109 x_org_code => l_org_code,
1110 x_item_name => l_item_name,
1111 x_subinventory => l_subinventory,
1112 x_loc_name => l_loc_name
1113 );
1114
1115 FND_MESSAGE.SET_NAME('INV', 'INV_CANNOT_CREATE_PULLSEQ');
1116 FND_MESSAGE.SET_TOKEN('SUB_CODE', p_pull_seq_rec.subinventory_name);
1117 FND_MESSAGE.SET_TOKEN('LOC_NAME', l_loc_name );
1118 x_error_msg := x_error_msg||FND_CONST.NEWLINE||FND_MESSAGE.GET;
1119 END IF;
1120
1121 end if;
1122
1123 /* The entire supplier defaulting logic is INCORRECT,
1124 During update via interface, if only supplier name is populated and
1125 if the existing pull sequence has the supplier, new supplier
1126 id will not be defaulted because there is a logic in the above code that
1127 defaulted the supplier id from existing pull sequence if supplier id
1128 is null in interface. Not fixing it currently, because
1129 the code change might be a lot and we are almost at the end of development
1130 cycle. In addition, the data model for supplier table is WRONG and update
1131 will not work because if supplier_id is the PRIMARY KEY in the table,
1132 there won't be a way to update supplier of existing pull sequence*/
1133
1134 -- Supplier defaulting for pull sequence level
1135 if l_source_type = inv_kanban_pvt.G_Source_Type_Supplier then
1136
1137 -- check if multiple suppliers exists for pull seq
1138 -- and updating supplier details at pull seq level
1139 if l_transaction_type = FLM_KANBAN_MASSLOAD.kanban_change then
1140 DECLARE
1141 l_count number;
1142 BEGIN
1143 SELECT COUNT(*)
1144 INTO l_count
1145 FROM MTL_PULL_SEQ_SUPPLIERS
1146 WHERE pull_sequence_id = p_pull_seq_rec.pull_sequence_id;
1147
1148 IF l_count > 0 THEN
1149 p_pull_seq_rec.supplier_id := null;
1150 p_pull_seq_rec.supplier_name := null;
1151 p_pull_seq_rec.supplier_site_id := null;
1152 p_pull_seq_rec.supplier_site_code := null;
1153 END IF;
1154 END;
1155 end if;
1156
1157 if p_pull_seq_rec.supplier_id is null then
1158 if p_pull_seq_rec.supplier_name is not null then
1159 p_pull_seq_rec.supplier_id := default_supplier_id(p_supplier_name => p_pull_seq_rec.supplier_name);
1160
1161 if p_pull_seq_rec.supplier_id is null then
1162 FND_MESSAGE.SET_NAME('FLM','FLM_ATTRIBUTE_INVALID');
1163 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Supplier Name');
1164 x_error_msg := x_error_msg||FND_CONST.NEWLINE||FND_MESSAGE.GET;
1165 end if;
1166 end if;
1167 else -- supplier id is not null
1168 if is_supplier_id_invalid(p_pull_seq_rec.supplier_id) then
1169 FND_MESSAGE.SET_NAME('FLM','FLM_ATTRIBUTE_INVALID');
1170 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Supplier');
1171 x_error_msg := x_error_msg||FND_CONST.NEWLINE||FND_MESSAGE.GET;
1172 end if;
1173 end if;
1174
1175 if p_pull_seq_rec.supplier_site_id is null then
1176 if p_pull_seq_rec.supplier_site_code is not null then
1177 p_pull_seq_rec.supplier_site_id :=
1178 default_supplier_site_id(p_supplier_id => p_pull_seq_rec.supplier_id
1179 ,p_supplier_site_code => p_pull_seq_rec.supplier_site_code
1180 ,p_org_id => p_pull_seq_rec.organization_id);
1181
1182 if p_pull_seq_rec.supplier_site_id is null then
1183 FND_MESSAGE.SET_NAME('FLM','FLM_ATTRIBUTE_INVALID');
1184 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Supplier Site Code');
1185 x_error_msg := x_error_msg||FND_CONST.NEWLINE||FND_MESSAGE.GET;
1186 end if;
1187 end if;
1188 else -- supplier site id is not null
1189 if is_supplier_site_id_invalid(p_supplier_id => p_pull_seq_rec.supplier_id
1190 ,p_supplier_site_id => p_pull_seq_rec.supplier_site_id
1191 ,p_org_id =>p_pull_seq_rec.organization_id)
1192 then
1193 FND_MESSAGE.SET_NAME('FLM','FLM_ATTRIBUTE_INVALID');
1194 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Supplier Site');
1195 x_error_msg := x_error_msg||FND_CONST.NEWLINE||FND_MESSAGE.GET;
1196 end if;
1197 end if;
1198
1199 end if;
1200
1201 -- Validate wip line id only if source type is production
1202 if l_source_type = inv_kanban_pvt.G_Source_Type_Production then
1203 -- Fix bug 12433772, change the defaulting logic for wip_line_id
1204 /* Fix bug 12886421 , we need to call default_wip_line_id to bring up the correct line id even p_pull_seq_rec.wip_line_id is not null
1205 because p_pull_seq_rec.wip_line_id will always be defaulted to mkps.wip_line_id before come into here*/
1206 if p_pull_seq_rec.wip_line_code is not null then
1207 p_pull_seq_rec.wip_line_id := default_wip_line_id(p_wip_line_code => p_pull_seq_rec.wip_line_code
1208 ,p_org_id => p_pull_seq_rec.organization_id);
1209 end if;
1210
1211 if p_pull_seq_rec.wip_line_id is null then
1212 -- Fix bug 12651974, wip_line_id must not be null if wip_line_code is not null
1213 -- wip_line_id can be null for production pull sequence given that it is not
1214 -- mandatory for production pull sequence to have wip line. In addition,
1215 -- during pull sequence update, p_pull_seq_rec.wip_line_code is set to FND_API.G_MISS_CHAR
1216 -- when it is null
1217 if p_pull_seq_rec.wip_line_code <> FND_API.G_MISS_CHAR and p_pull_seq_rec.wip_line_code is not null then
1218 FND_MESSAGE.SET_NAME('FLM','FLM_ATTRIBUTE_INVALID');
1219 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Wip Line Code');
1220 x_error_msg := x_error_msg||FND_CONST.NEWLINE||FND_MESSAGE.GET;
1221 end if;
1222 else -- wip line id is not null
1223 if is_wip_line_id_invalid(p_pull_seq_rec.wip_line_id) then
1224 FND_MESSAGE.SET_NAME('FLM','FLM_ATTRIBUTE_INVALID');
1225 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Wip Line');
1226 x_error_msg := x_error_msg||FND_CONST.NEWLINE||FND_MESSAGE.GET;
1227 end if;
1228 end if;
1229 end if;
1230
1231 --Minimum order quantity
1232 if sign(p_pull_seq_rec.minimum_order_quantity) = -1 then
1233 FND_MESSAGE.SET_NAME('FLM','FLM_ATTRIBUTE_POSITIVE');
1234 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Minimum order quantity');
1235 x_error_msg := x_error_msg||FND_CONST.NEWLINE||FND_MESSAGE.GET;
1236 end if;
1237
1238 --Allocation percent
1239 if p_pull_seq_rec.allocation_percent IS NOT NULL THEN
1240 if p_pull_seq_rec.allocation_percent NOT BETWEEN 0 and 100 then
1241 FND_MESSAGE.SET_NAME('FLM','FLM_INVALID_PERCENT_VALUE');
1242 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Allocation Percent');
1243 x_error_msg := x_error_msg||FND_CONST.NEWLINE||FND_MESSAGE.GET;
1244 end if;
1245 end if;
1246
1247 -- Validation for positive numbers
1248 -- Kanban size
1249
1250 mydebug('Validation of fields for positive number.');
1251
1252 if sign(p_pull_seq_rec.kanban_size) = -1 then
1253 FND_MESSAGE.SET_NAME('FLM','FLM_ATTRIBUTE_POSITIVE');
1254 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Kanban size');
1255 x_error_msg := x_error_msg||FND_CONST.NEWLINE||FND_MESSAGE.GET;
1256 end if;
1257
1258 -- Number of cards
1259 if sign(p_pull_seq_rec.number_of_cards) = -1 then
1260 FND_MESSAGE.SET_NAME('FLM','FLM_ATTRIBUTE_POSITIVE');
1261 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Number of cards');
1262 x_error_msg := x_error_msg||FND_CONST.NEWLINE||FND_MESSAGE.GET;
1263 end if;
1264
1265 if TRUNC(p_pull_seq_rec.number_of_cards) <> p_pull_seq_rec.number_of_cards then
1266 FND_MESSAGE.SET_NAME('FLM','FLM_ATTRIBUTE_INVALID');
1267 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Number of cards');
1268 x_error_msg := x_error_msg||FND_CONST.NEWLINE||FND_MESSAGE.GET;
1269 end if;
1270
1271 -- Replenishment lead time
1272 if sign(p_pull_seq_rec.replenishment_lead_time) = -1 then
1273 FND_MESSAGE.SET_NAME('FLM','FLM_ATTRIBUTE_POSITIVE');
1274 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Replenishment Lead time');
1275 x_error_msg := x_error_msg||FND_CONST.NEWLINE||FND_MESSAGE.GET;
1276 end if;
1277
1278 -- Fixed lot multiplier
1279 if sign(p_pull_seq_rec.fixed_lot_multiplier) = -1 then
1280 FND_MESSAGE.SET_NAME('FLM','FLM_ATTRIBUTE_POSITIVE');
1281 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Fixed lot multiplier');
1282 x_error_msg := x_error_msg||FND_CONST.NEWLINE||FND_MESSAGE.GET;
1283 end if;
1284
1285 -- Safety stock days
1286 if sign(p_pull_seq_rec.safety_stock_days) = -1 then
1287 FND_MESSAGE.SET_NAME('FLM','FLM_ATTRIBUTE_POSITIVE');
1288 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Safety stock days');
1289 x_error_msg := x_error_msg||FND_CONST.NEWLINE||FND_MESSAGE.GET;
1290 end if;
1291
1292 -- Future cards size
1293 if nvl(p_pull_seq_rec.future_card_size,1) <=0 then
1294 FND_MESSAGE.SET_NAME('FLM','FLM_ATTRIBUTE_INVALID');
1295 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Future cards size');
1296 x_error_msg := x_error_msg||FND_CONST.NEWLINE||FND_MESSAGE.GET;
1297 end if;
1298
1299 -- Future no of cards
1300 if nvl(p_pull_seq_rec.future_no_of_cards,1) <=0 then
1301 FND_MESSAGE.SET_NAME('FLM','FLM_ATTRIBUTE_INVALID');
1302 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Future no of cards');
1303 x_error_msg := x_error_msg||FND_CONST.NEWLINE||FND_MESSAGE.GET;
1304 end if;
1305
1306 mydebug('Validation of additonal fields.');
1307
1308 -- Planning effectivity. Changes for Bug 12615810.
1309 if trunc(nvl(p_pull_seq_rec.planning_effectivity,sysdate+2)) <= trunc(SYSDATE) and l_transaction_type = FLM_KANBAN_MASSLOAD.kanban_add then
1310 FND_MESSAGE.SET_NAME('FLM','FLM_ATTRIBUTE_INVALID');
1311 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Planning effectivity');
1312 x_error_msg := x_error_msg||FND_CONST.NEWLINE||FND_MESSAGE.GET;
1313 end if;
1314
1315 -- Make Future effective current if it's currently effective. Changes for Bug 12615810.
1316 if trunc(nvl(p_pull_seq_rec.planning_effectivity,sysdate+2)) <= trunc(SYSDATE) and l_transaction_type = FLM_KANBAN_MASSLOAD.kanban_change then
1317 p_pull_seq_rec.planning_effectivity := null;
1318 if nvl(p_pull_seq_rec.number_of_cards,-1) = nvl(l_no_of_cards,-1) then
1319 p_pull_seq_rec.number_of_cards := nvl(p_pull_seq_rec.future_no_of_cards,p_pull_seq_rec.number_of_cards);
1320 end if;
1321 p_pull_seq_rec.future_no_of_cards := null;
1322 if nvl(p_pull_seq_rec.kanban_size,-1) = nvl(l_kanban_size,-1) then
1323 p_pull_seq_rec.kanban_size := nvl(p_pull_seq_rec.future_card_size,p_pull_seq_rec.kanban_size);
1324 end if;
1325 p_pull_seq_rec.future_card_size := null;
1326 end if;
1327
1328 -- Auto allocate flag
1329 if p_pull_seq_rec.auto_allocate_flag is not null then
1330 if p_pull_seq_rec.auto_allocate_flag NOT IN (1,2) then
1331 FND_MESSAGE.SET_NAME('FLM','FLM_ATTRIBUTE_INVALID');
1332 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Auto Allocate Flag');
1333 x_error_msg := x_error_msg||FND_CONST.NEWLINE||FND_MESSAGE.GET;
1334 end if;
1335 end if;
1336
1337 -- Release kanban flag
1338 if p_pull_seq_rec.release_kanban_flag is null then
1339 p_pull_seq_rec.release_kanban_flag := 1;
1340 else
1341 if p_pull_seq_rec.release_kanban_flag not in (1,2) then
1342 FND_MESSAGE.SET_NAME('FLM','FLM_ATTRIBUTE_INVALID');
1343 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Release Kanban Flag');
1344 x_error_msg := x_error_msg||FND_CONST.NEWLINE||FND_MESSAGE.GET;
1345 end if;
1346 end if;
1347
1348 -- Auto Request
1349 if p_pull_seq_rec.auto_request is not null then
1350 if p_pull_seq_rec.auto_request not in ('Y','N') then
1351 FND_MESSAGE.SET_NAME('FLM','FLM_ATTRIBUTE_INVALID');
1352 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Auto Request');
1353 x_error_msg := x_error_msg||FND_CONST.NEWLINE||FND_MESSAGE.GET;
1354 end if;
1355 end if;
1356
1357 -- Replenishment Type
1358 if p_pull_seq_rec.replenishment_type is null then
1359 p_pull_seq_rec.replenishment_type := FLM_KANBAN_MASSLOAD.PHYSICAL;
1360 else
1361 --if not is_lookup_code_valid(p_lookup_type => 'MTL_KANBAN_REPLENISHMENT_TYPE'
1362 -- ,p_lookup_code => p_pull_seq_rec.replenishment_type)
1363 if p_pull_seq_rec.replenishment_type NOT IN (FLM_KANBAN_MASSLOAD.PHYSICAL
1364 ,FLM_KANBAN_MASSLOAD.LOGICAL)
1365 then
1366 FND_MESSAGE.SET_NAME('FLM','FLM_ATTRIBUTE_INVALID');
1367 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Replenishment Type');
1368 x_error_msg := x_error_msg||FND_CONST.NEWLINE||FND_MESSAGE.GET;
1369
1370 end if;
1371 end if;
1372
1373 -- Consolidation
1374 if p_pull_seq_rec.consolidation is null then
1375 p_pull_seq_rec.consolidation := FLM_KANBAN_MASSLOAD.CONSOLIDATION_NO;
1376 elsif p_pull_seq_rec.consolidation NOT IN (FLM_KANBAN_MASSLOAD.CONSOLIDATION_YES,
1377 FLM_KANBAN_MASSLOAD.CONSOLIDATION_NO)
1378 then
1379 FND_MESSAGE.SET_NAME('FLM','FLM_ATTRIBUTE_INVALID');
1380 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Consolidation');
1381 x_error_msg := x_error_msg||FND_CONST.NEWLINE||FND_MESSAGE.GET;
1382 end if;
1383
1384 if l_source_type = inv_kanban_pvt.G_Source_Type_Production and
1385 p_pull_seq_rec.consolidation = FLM_KANBAN_MASSLOAD.CONSOLIDATION_YES then
1386 FND_MESSAGE.SET_NAME('FLM','FLM_ATTRIBUTE_INVALID');
1387 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Consolidation');
1388 x_error_msg := x_error_msg||FND_CONST.NEWLINE||FND_MESSAGE.GET;
1389 end if;
1390 -----------------------------------------------------------------------
1391 -- Validation to see that number of cards are not null if
1392 -- calculate size is selected or that kanban size is not null if calculate
1393 -- cards has been entered.
1394 -----------------------------------------------------------------------
1395
1396 if l_source_type IN (INV_Kanban_PVT.G_Source_Type_Supplier
1397 ,INV_Kanban_PVT.G_Source_Type_Production)
1398 then
1399 l_source_subinventory := p_pull_seq_rec.source_subinventory;
1400 end if;
1401
1402 if l_transaction_type = FLM_KANBAN_MASSLOAD.kanban_change and
1403 l_pullseq_id IS NOT NULL
1404 then
1405 OPEN c_pull_seq_calc(p_pull_seq_rec.kanban_size
1406 ,l_calculate_kanban_flag
1407 ,p_pull_seq_rec.number_of_cards
1408 ,p_pull_seq_rec.release_kanban_flag
1409 ,l_pullseq_id
1410 ,l_source_subinventory);
1411
1412 FETCH c_pull_seq_calc INTO lr_pull_seq_calc;
1413 CLOSE c_pull_seq_calc;
1414 end if;
1415
1416 if p_pull_seq_rec.calculate_kanban_flag IS NOT NULL and
1417 l_transaction_type in (FLM_KANBAN_MASSLOAD.kanban_add,FLM_KANBAN_MASSLOAD.kanban_change)
1418 then
1419 if p_pull_seq_rec.calculate_kanban_flag IN (1, 2, 3) then
1420 l_calculate_kanban_flag := p_pull_seq_rec.calculate_kanban_flag;
1421 else
1422 FND_MESSAGE.SET_NAME('FLM','FLM_ATTRIBUTE_INVALID');
1423 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Calculate Kanban Flag');
1424 x_error_msg := x_error_msg||FND_CONST.NEWLINE||FND_MESSAGE.GET;
1425 end if;
1426
1427 if l_transaction_type = FLM_KANBAN_MASSLOAD.kanban_add then
1428 lr_pull_seq_calc.kanban_size := p_pull_seq_rec.kanban_size;
1429 lr_pull_seq_calc.calculate_kanban_flag:= l_calculate_kanban_flag;
1430 lr_pull_seq_calc.number_of_cards := p_pull_seq_rec.number_of_cards;
1431 lr_pull_seq_calc.release_kanban_flag := p_pull_seq_rec.release_kanban_flag;
1432 end if;
1433
1434 if (lr_pull_seq_calc.calculate_kanban_flag = 1 and
1435 lr_pull_seq_calc.number_of_cards IS NULL and
1436 lr_pull_seq_calc.release_kanban_flag = 1)
1437 then
1438 FND_MESSAGE.SET_NAME ('FLM', 'FLM_ATTRIBUTE_REQUIRED');
1439 FND_MESSAGE.SET_TOKEN ('ATTRIBUTE', 'NUMBER_OF_CARDS');
1440 x_error_msg := x_error_msg||FND_CONST.NEWLINE||FND_MESSAGE.GET;
1441 elsif (lr_pull_seq_calc.calculate_kanban_flag = 2 and
1442 lr_pull_seq_calc.kanban_size IS NULL and
1443 lr_pull_seq_calc.release_kanban_flag = 1) then
1444 FND_MESSAGE.SET_NAME ('FLM', 'FLM_ATTRIBUTE_REQUIRED');
1445 FND_MESSAGE.SET_TOKEN ('ATTRIBUTE', 'KANBAN_SIZE');
1446 x_error_msg := x_error_msg||FND_CONST.NEWLINE||FND_MESSAGE.GET;
1447 end if;
1448 elsif (l_transaction_type = FLM_KANBAN_MASSLOAD.kanban_add and
1449 p_pull_seq_rec.calculate_kanban_flag IS NULL) then
1450 if (lr_pull_seq_calc.kanban_size IS NULL and
1451 lr_pull_seq_calc.number_of_cards IS NULL) then
1452 l_calculate_kanban_flag := 3;
1453 elsif lr_pull_seq_calc.kanban_size IS NULL then
1454 l_calculate_kanban_flag := 1;
1455 else
1456 l_calculate_kanban_flag := 2;
1457 end if;
1458 p_pull_seq_rec.calculate_kanban_flag := l_calculate_kanban_flag;
1459 end if;
1460
1461 mydebug('Default fields to null based on source type.');
1462 ------------------------------------------------------------
1463 -- Fields not allowed for insert/update based on source type
1464 ------------------------------------------------------------
1465 if l_source_type = INV_Kanban_PVT.G_Source_Type_InterOrg then
1466 p_pull_seq_rec.auto_allocate_flag := null;
1467 p_pull_seq_rec.supplier_id := null;
1468 p_pull_seq_rec.supplier_site_id := null;
1469 p_pull_seq_rec.wip_line_id := null;
1470 elsif l_source_type = INV_Kanban_PVT.G_Source_Type_Supplier then
1471 p_pull_seq_rec.auto_allocate_flag := null;
1472 p_pull_seq_rec.source_organization_id := null;
1473 p_pull_seq_rec.source_subinventory := null;
1474 p_pull_seq_rec.source_locator_id := null;
1475 p_pull_seq_rec.wip_line_id := null;
1476 elsif l_source_type = INV_Kanban_PVT.G_Source_Type_IntraOrg then
1477 p_pull_seq_rec.supplier_id := null;
1478 p_pull_seq_rec.supplier_site_id := null;
1479 p_pull_seq_rec.wip_line_id := null;
1480 elsif l_source_type = INV_Kanban_PVT.G_Source_Type_Production then
1481 p_pull_seq_rec.auto_allocate_flag := null;
1482 p_pull_seq_rec.supplier_id := null;
1483 p_pull_seq_rec.supplier_site_id := null;
1484 p_pull_seq_rec.source_organization_id := null;
1485 p_pull_seq_rec.source_subinventory := null;
1486 p_pull_seq_rec.source_locator_id := null;
1487 end if;
1488 end if; -- l_transaction_type in add or change
1489
1490 if x_error_msg is NOT NULL THEN
1491 x_ret_status := FND_API.G_RET_STS_ERROR;
1492 else
1493 x_ret_status := l_ret_status;
1494 end if;
1495
1496 mydebug('Exiting validate_pull_sequence procedure.');
1497 EXCEPTION
1498 WHEN OTHERS THEN
1499 x_ret_status := FND_API.G_RET_STS_ERROR;
1500 x_error_msg := 'Error in validate_pull_sequence procedure:' ||FND_CONST.NEWLINE||SQLERRM;
1501 end validate_pull_sequence;
1502
1503 -----------------------
1504 --procedure process_ps
1505 -----------------------
1506 PROCEDURE process_ps(p_pull_sequence_tbl IN OUT NOCOPY flm_kanban_massload.pull_seq_tbl_type
1507 ,p_supplier_tbl IN OUT NOCOPY flm_kanban_massload.kanban_supp_tbl_type
1508 ,x_ret_status OUT NOCOPY VARCHAR2
1509 )
1510 IS
1511
1512 r_pull_seq_rec flm_ekanban_pub.pull_sequence_rec_type;
1513 r_supplier_rec flm_ekanban_pub.kanban_supplier_rec_type;
1514 t_supplier_tbl flm_ekanban_pub.kanban_supplier_tbl_type;
1515 l_transaction_type NUMBER;
1516 l_ret_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
1517 l_error_msg VARCHAR2(4000);
1518 l_counter NUMBER := 0;
1519 l_count NUMBER := 0;
1520 l_supp_tab_count NUMBER;
1521
1522
1523 BEGIN
1524 mydebug('In process_ps procedure.');
1525
1526 l_supp_tab_count := p_supplier_tbl.count;
1527
1528 FOR i IN p_pull_sequence_tbl.first..p_pull_sequence_tbl.last LOOP
1529
1530 mydebug('Assigning values to record type.');
1531 --copy the values from pull seq interface tbl type to actual pull seq rec type
1532 r_pull_seq_rec.pull_sequence_id := p_pull_sequence_tbl(i).pull_sequence_id;
1533 r_pull_seq_rec.generate_cards := p_pull_sequence_tbl(i).generate_cards;
1534 r_pull_seq_rec.last_update_date := p_pull_sequence_tbl(i).last_update_date;
1535 r_pull_seq_rec.last_updated_by := p_pull_sequence_tbl(i).last_updated_by;
1536 r_pull_seq_rec.creation_date := p_pull_sequence_tbl(i).creation_date;
1537 r_pull_seq_rec.created_by := p_pull_sequence_tbl(i).created_by;
1538 r_pull_seq_rec.last_update_login := p_pull_sequence_tbl(i).last_update_login;
1539 r_pull_seq_rec.inventory_item_id := p_pull_sequence_tbl(i).inventory_item_id;
1540 r_pull_seq_rec.concatenated_segments := p_pull_sequence_tbl(i).concatenated_segments;
1541 r_pull_seq_rec.organization_code := p_pull_sequence_tbl(i).organization_code;
1542 r_pull_seq_rec.organization_id := p_pull_sequence_tbl(i).organization_id;
1543 r_pull_seq_rec.subinventory_name := p_pull_sequence_tbl(i).subinventory_name;
1544 r_pull_seq_rec.source_type := p_pull_sequence_tbl(i).source_type;
1545 r_pull_seq_rec.locator_id := p_pull_sequence_tbl(i).locator_id;
1546 r_pull_seq_rec.supplier_id := p_pull_sequence_tbl(i).supplier_id;
1547 r_pull_seq_rec.supplier_name := p_pull_sequence_tbl(i).supplier_name;
1548 r_pull_seq_rec.supplier_site_id := p_pull_sequence_tbl(i).supplier_site_id;
1549 r_pull_seq_rec.supplier_site_code := p_pull_sequence_tbl(i).supplier_site_code;
1550 r_pull_seq_rec.source_organization_id := p_pull_sequence_tbl(i).source_organization_id;
1551 r_pull_seq_rec.source_subinventory := p_pull_sequence_tbl(i).source_subinventory;
1552 r_pull_seq_rec.source_locator_id := p_pull_sequence_tbl(i).source_locator_id;
1553 r_pull_seq_rec.wip_line_code := p_pull_sequence_tbl(i).wip_line_code;
1554 r_pull_seq_rec.wip_line_id := p_pull_sequence_tbl(i).wip_line_id;
1555 r_pull_seq_rec.replenishment_lead_time := p_pull_sequence_tbl(i).replenishment_lead_time;
1556 r_pull_seq_rec.calculate_kanban_flag := p_pull_sequence_tbl(i).calculate_kanban_flag;
1557 r_pull_seq_rec.kanban_size := p_pull_sequence_tbl(i).kanban_size;
1558 r_pull_seq_rec.fixed_lot_multiplier := p_pull_sequence_tbl(i).fixed_lot_multiplier;
1559 r_pull_seq_rec.safety_stock_days := p_pull_sequence_tbl(i).safety_stock_days;
1560 r_pull_seq_rec.number_of_cards := p_pull_sequence_tbl(i).number_of_cards;
1561 r_pull_seq_rec.minimum_order_quantity := p_pull_sequence_tbl(i).minimum_order_quantity;
1562 r_pull_seq_rec.aggregation_type := p_pull_sequence_tbl(i).aggregation_type;
1563 r_pull_seq_rec.allocation_percent := p_pull_sequence_tbl(i).allocation_percent;
1564 r_pull_seq_rec.release_kanban_flag := p_pull_sequence_tbl(i).release_kanban_flag;
1565 r_pull_seq_rec.attribute_category := p_pull_sequence_tbl(i).attribute_category;
1566 r_pull_seq_rec.attribute1 := p_pull_sequence_tbl(i).attribute1;
1567 r_pull_seq_rec.attribute2 := p_pull_sequence_tbl(i).attribute2;
1568 r_pull_seq_rec.attribute3 := p_pull_sequence_tbl(i).attribute3;
1569 r_pull_seq_rec.attribute4 := p_pull_sequence_tbl(i).attribute4;
1570 r_pull_seq_rec.attribute5 := p_pull_sequence_tbl(i).attribute5;
1571 r_pull_seq_rec.attribute6 := p_pull_sequence_tbl(i).attribute6;
1572 r_pull_seq_rec.attribute7 := p_pull_sequence_tbl(i).attribute7;
1573 r_pull_seq_rec.attribute8 := p_pull_sequence_tbl(i).attribute8;
1574 r_pull_seq_rec.attribute9 := p_pull_sequence_tbl(i).attribute9;
1575 r_pull_seq_rec.attribute10 := p_pull_sequence_tbl(i).attribute10;
1576 r_pull_seq_rec.attribute11 := p_pull_sequence_tbl(i).attribute11;
1577 r_pull_seq_rec.attribute12 := p_pull_sequence_tbl(i).attribute12;
1578 r_pull_seq_rec.attribute13 := p_pull_sequence_tbl(i).attribute13;
1579 r_pull_seq_rec.attribute14 := p_pull_sequence_tbl(i).attribute14;
1580 r_pull_seq_rec.attribute15 := p_pull_sequence_tbl(i).attribute15;
1581 r_pull_seq_rec.auto_request := p_pull_sequence_tbl(i).auto_request;
1582 r_pull_seq_rec.auto_allocate_flag := p_pull_sequence_tbl(i).auto_allocate_flag;
1583 r_pull_seq_rec.replenishment_type := p_pull_sequence_tbl(i).replenishment_type;
1584 r_pull_seq_rec.consolidation := p_pull_sequence_tbl(i).consolidation;
1585 r_pull_seq_rec.consolidation_group := p_pull_sequence_tbl(i).consolidation_group;
1586 r_pull_seq_rec.future_card_size := null;
1587 r_pull_seq_rec.future_no_of_cards := null;
1588 r_pull_seq_rec.planning_effectivity := null;
1589 r_pull_seq_rec.avg_dependent_demand := p_pull_sequence_tbl(i).avg_dependent_demand;
1590 r_pull_seq_rec.avg_independent_demand := p_pull_sequence_tbl(i).avg_independent_demand;
1591
1592 l_transaction_type := p_pull_sequence_tbl(i).transaction_type;
1593
1594 l_counter := 1;
1595 t_supplier_tbl.DELETE;
1596
1597 --copy the values from supplier interface tbl type to supplier tbl type
1598 if l_supp_tab_count > 0 then
1599 FOR j IN 1..l_supp_tab_count LOOP
1600
1601 if p_pull_sequence_tbl(i).interface_id = p_supplier_tbl(j).parent_interface_id then
1602 r_supplier_rec.pull_sequence_id := null;
1603 r_supplier_rec.supplier_id := p_supplier_tbl(j).supplier_id;
1604 r_supplier_rec.supplier_name := p_supplier_tbl(j).supplier_name;
1605 r_supplier_rec.supplier_site_id := p_supplier_tbl(j).supplier_site_id;
1606 r_supplier_rec.supplier_site_code := p_supplier_tbl(j).supplier_site_code;
1607 r_supplier_rec.sourcing_percentage := p_supplier_tbl(j).sourcing_percentage;
1608 r_supplier_rec.transaction_type := p_supplier_tbl(j).transaction_type;
1609
1610 t_supplier_tbl(l_counter) := r_supplier_rec;
1611 r_supplier_rec := null;
1612 l_counter := l_counter + 1;
1613
1614 end if;
1615
1616 END LOOP;
1617 end if;
1618
1619 mydebug('Calling process_pull_sequence procedure.');
1620 -- call the public API
1621 process_pull_sequence(p_pull_sequence_rec => r_pull_seq_rec
1622 ,p_supplier_tbl => t_supplier_tbl
1623 ,p_transaction_type => l_transaction_type
1624 ,p_commit_flag => 'N'
1625 ,x_ret_status => l_ret_status
1626 ,x_error_msg => l_error_msg);
1627
1628 if l_ret_status = FND_API.G_RET_STS_SUCCESS then
1629 p_pull_sequence_tbl(i).pull_sequence_id := r_pull_seq_rec.pull_sequence_id;
1630 p_pull_sequence_tbl(i).process_status := FLM_KANBAN_MASSLOAD.COMPLETED;
1631 fnd_file.put_line(fnd_file.log,' Pull Sequence details with Interface Id : '|| p_pull_sequence_tbl(i).interface_id ||
1632 ' was processed successfully . ');
1633 /* elsif l_ret_status = 'W' then
1634 p_pull_sequence_tbl(i).process_status := FLM_KANBAN_MASSLOAD.WARNING;
1635 p_pull_sequence_tbl(i).error_text := l_error_msg;*/
1636 elsif l_ret_status = FND_API.G_RET_STS_ERROR then
1637 p_pull_sequence_tbl(i).process_status := FLM_KANBAN_MASSLOAD.ERROR;
1638 p_pull_sequence_tbl(i).error_text := l_error_msg;
1639 fnd_file.put_line(fnd_file.log,' Pull Sequence details with Interface Id : '|| p_pull_sequence_tbl(i).interface_id ||
1640 ' failed due to following : '|| l_error_msg );
1641 else
1642 p_pull_sequence_tbl(i).process_status := FLM_KANBAN_MASSLOAD.ERROR;
1643 p_pull_sequence_tbl(i).error_text := l_error_msg;
1644 fnd_file.put_line(fnd_file.log,' Pull Sequence details with Interface Id : '|| p_pull_sequence_tbl(i).interface_id ||
1645 ' failed due to following : '|| l_error_msg );
1646 end if;
1647
1648 IF l_ret_status <> FND_API.G_RET_STS_SUCCESS THEN
1649 l_count := l_count+1 ;
1650 END IF;
1651
1652 END LOOP;
1653
1654 IF l_count > 0 THEN
1655 x_ret_status := FND_API.G_RET_STS_ERROR;
1656 ELSE
1657 x_ret_status := FND_API.G_RET_STS_SUCCESS;
1658 END IF;
1659
1660 mydebug('Exiting process_ps procedure.');
1661 EXCEPTION
1662 WHEN OTHERS THEN
1663 x_ret_status := FND_API.G_RET_STS_ERROR;
1664
1665 END process_ps;
1666
1667 -- Private procedure
1668 --------------------------------------------------------------------
1669 -- PROCEDURE
1670 -- validate_kanban_cards
1671 --
1672 -- PURPOSE
1673 -- validate Kanban Card Details
1674 --------------------------------------------------------------------
1675 PROCEDURE validate_kanban_cards (p_kanban_card_rec IN OUT NOCOPY flm_ekanban_pub.kanban_card_rec_type
1676 ,p_transaction_type IN NUMBER
1677 ,p_release_kanban_flag OUT NOCOPY NUMBER
1678 ,p_error_text IN OUT NOCOPY VARCHAR2
1679 ,x_ret_status OUT NOCOPY VARCHAR2
1680 )
1681 IS
1682 -- cursor to validate inventory item
1683 CURSOR c_inv_item (p_inventory_item_id NUMBER,
1684 p_organization_id NUMBER
1685 )
1686 IS
1687 SELECT msi.inventory_item_id
1688 ,msi.planning_make_buy_code
1689 ,msi.purchasing_enabled_flag
1690 ,msi.internal_order_enabled_flag
1691 ,msi.mtl_transactions_enabled_flag
1692 ,msi.purchasing_item_flag
1693 ,msi.reservable_type
1694 ,msi.shippable_item_flag
1695 ,msi.returnable_flag
1696 ,msi.so_transactions_flag
1697 ,msi.restrict_subinventories_code
1698 ,msi.effectivity_control
1699 ,msi.inventory_item_flag
1700 ,msi.stock_enabled_flag
1701 ,msi.inventory_item_status_code
1702 FROM mtl_system_items msi
1703 WHERE msi.inventory_item_id = p_inventory_item_id
1704 AND msi.organization_id = p_organization_id;
1705
1706 -- cursor to validate subinventory
1707 CURSOR c_subinv (p_subinv_name IN mtl_secondary_inventories.secondary_inventory_name%TYPE,
1708 p_org_id IN mtl_secondary_inventories.organization_id%TYPE
1709 )
1710 IS
1711 SELECT misi.secondary_inventory_name
1712 FROM mtl_secondary_inventories misi
1713 WHERE misi.secondary_inventory_name = p_subinv_name
1714 AND (misi.disable_date > SYSDATE
1715 OR misi.disable_date IS NULL)
1716 AND misi.organization_id = p_org_id;
1717
1718 --cursor to fetch pull seq information
1719 CURSOR c_pullseq_info (p_pullseq_id IN NUMBER)
1720 IS
1721 SELECT MKPS.organization_id
1722 ,MKPS.inventory_item_id
1723 ,MKPS.subinventory_name
1724 ,MKPS.locator_id
1725 ,MKPS.source_type
1726 ,MKPS.kanban_size
1727 ,MKPS.supplier_id
1728 ,MKPS.supplier_site_id
1729 ,MKPS.source_organization_id
1730 ,MKPS.source_subinventory
1731 ,MKPS.source_locator_id
1732 ,MKPS.wip_line_id
1733 ,MKPS.number_of_cards
1734 ,MKPS.future_no_of_cards
1735 ,MKPS.release_kanban_flag
1736 FROM mtl_kanban_pull_sequences MKPS
1737 WHERE pull_sequence_id = p_pullseq_id;
1738
1739 --Cursor to Kanban Card count
1740 CURSOR c_cards_cnt(p_pull_sequence_id IN NUMBER)
1741 IS
1742 SELECT COUNT(MKC.kanban_card_id)
1743 FROM MTL_KANBAN_CARDS MKC
1744 WHERE MKC.pull_sequence_id = p_pull_sequence_id
1745 AND MKC.card_status IN (INV_Kanban_PVT.G_Card_Status_Active, INV_Kanban_PVT.G_Card_Status_Hold);
1746
1747 --Cursor to validate Kanban Card Id
1748 CURSOR c_card_exist(p_kanban_card_id IN NUMBER)
1749 IS
1750 SELECT kanban_card_id
1751 ,card_status
1752 ,supply_status
1753 FROM MTL_KANBAN_CARDS MTC
1754 WHERE MTC.kanban_card_id = p_kanban_card_id;
1755
1756 -- Cursor to get the card count
1757
1758
1759 lr_inv_item c_inv_item%ROWTYPE;
1760 lr_pullseq_info c_pullseq_info%ROWTYPE;
1761
1762 r_kanban_card_rec flm_ekanban_pub.kanban_card_rec_type;
1763
1764 l_org_code VARCHAR2 (3);
1765 l_item_name VARCHAR2 (200);
1766 l_calculate_kanban_flag NUMBER;
1767 l_loc_name VARCHAR2 (200);
1768 l_card_id NUMBER;
1769 l_card_sts NUMBER;
1770 l_supply_sts NUMBEr;
1771 l_transaction_type NUMBER;
1772 l_source_type NUMBER;
1773 l_exists NUMBER;
1774 l_max_replenishments NUMBER := 0;
1775 l_rem_cards NUMBER;
1776 l_cards_count NUMBER;
1777 l_return_status VARCHAR2 (1);
1778 l_error_msg_code VARCHAR2 (2000);
1779
1780 l_subinventory VARCHAR2(10);
1781 l_source_subinventory VARCHAR2(10);
1782 l_locator_control NUMBER;
1783 l_source_locator_control NUMBER;
1784
1785 BEGIN
1786 mydebug('In Procedure validate_kanban_cards');
1787 r_kanban_card_rec := p_kanban_card_rec;
1788 --Validate Transaction Type
1789 IF p_transaction_type IS NULL THEN
1790 fnd_message.set_name('FLM','FLM_ATTRIBUTE_REQUIRED');
1791 fnd_message.set_token('ATTRIBUTE','Transaction Type');
1792 p_error_text := p_error_text || FND_CONST.NEWLINE || FND_MESSAGE.GET;
1793 ELSIF p_transaction_type NOT IN (FLM_KANBAN_MASSLOAD.kanban_add,
1794 FLM_KANBAN_MASSLOAD.kanban_change,
1795 FLM_KANBAN_MASSLOAD.kanban_delete)
1796 THEN
1797 fnd_message.set_name ('FLM', 'FLM_ATTRIBUTE_INVALID');
1798 fnd_message.set_token ('ATTRIBUTE', 'Transaction Type');
1799 p_error_text := p_error_text || FND_CONST.NEWLINE || FND_MESSAGE.GET;
1800 ELSE
1801 l_transaction_type := p_transaction_type;
1802 END IF;
1803
1804 -- Fix bug 12430551 validate Org, Item, Subinventory, Locator only if pull_sequence_id is null
1805 IF r_kanban_card_rec.pull_sequence_id IS NULL THEN
1806 /*******************************************/
1807 --Validate Org, Item, Subinventory, Locator
1808 /*******************************************/
1809 -- validate organization
1810 IF r_kanban_card_rec.organization_id IS NULL THEN
1811 IF r_kanban_card_rec.organization_code IS NULL THEN
1812 fnd_message.set_name ('FLM', 'FLM_ATTRIBUTE_REQUIRED');
1813 fnd_message.set_token ('ATTRIBUTE', 'Organization');
1814 p_error_text := p_error_text || FND_CONST.NEWLINE || FND_MESSAGE.GET;
1815 ELSE
1816 r_kanban_card_rec.organization_id := FLM_KANBAN_PUB.default_org_id(r_kanban_card_rec.organization_code);
1817 IF r_kanban_card_rec.organization_id IS NULL THEN
1818 fnd_message.set_name ('FLM', 'FLM_ATTRIBUTE_INVALID');
1819 fnd_message.set_token ('ATTRIBUTE', 'Organization Code');
1820 p_error_text := p_error_text || FND_CONST.NEWLINE || FND_MESSAGE.GET;
1821 END IF;
1822 END IF;
1823 ELSE
1824 IF FLM_KANBAN_PUB.is_org_id_invalid (r_kanban_card_rec.organization_id) THEN
1825 fnd_message.set_name ('FLM', 'FLM_ATTRIBUTE_INVALID');
1826 fnd_message.set_token ('ATTRIBUTE', 'Organization Id');
1827 p_error_text := p_error_text || FND_CONST.NEWLINE || FND_MESSAGE.GET;
1828 END IF;
1829 END IF;
1830
1831 -- Inventory Item
1832 IF r_kanban_card_rec.inventory_item_id IS NULL THEN
1833 IF r_kanban_card_rec.inventory_item_code IS NULL THEN
1834 fnd_message.set_name ('FLM', 'FLM_ATTRIBUTE_REQUIRED');
1835 fnd_message.set_token ('ATTRIBUTE', 'Inventory Item');
1836 p_error_text := p_error_text || FND_CONST.NEWLINE || FND_MESSAGE.GET;
1837 ELSE
1838 r_kanban_card_rec.inventory_item_id := FLM_KANBAN_PUB.default_inv_item_id(p_org_id => r_kanban_card_rec.organization_id
1839 ,p_conc_segments => r_kanban_card_rec.inventory_item_code);
1840 IF r_kanban_card_rec.inventory_item_id IS NULL THEN
1841 fnd_message.set_name ('FLM', 'FLM_ATTRIBUTE_INVALID');
1842 fnd_message.set_token ('ATTRIBUTE', 'Inventory Item Name');
1843 p_error_text := p_error_text || FND_CONST.NEWLINE || FND_MESSAGE.GET;
1844 END IF;
1845 END IF;
1846 ELSE
1847 IF is_inv_item_id_valid(p_inv_item_id => r_kanban_card_rec.inventory_item_id
1848 ,p_org_id => r_kanban_card_rec.organization_id
1849 )
1850 THEN
1851 OPEN c_inv_item (r_kanban_card_rec.inventory_item_id,
1852 r_kanban_card_rec.organization_id
1853 );
1854 FETCH c_inv_item INTO lr_inv_item;
1855
1856 IF lr_inv_item.inventory_item_flag = 'N' THEN
1857 fnd_message.set_name ('FLM', 'FLM_ITEM_FLAG');
1858 p_error_text := p_error_text || FND_CONST.NEWLINE || FND_MESSAGE.GET;
1859 END IF;
1860
1861 IF lr_inv_item.effectivity_control = 2 THEN
1862 fnd_message.set_name ('INV', 'INV_NO_EFFECTIVITY_CONTROL');
1863 p_error_text := p_error_text || FND_CONST.NEWLINE || FND_MESSAGE.GET;
1864 END IF;
1865
1866 IF lr_inv_item.stock_enabled_flag = 'N' THEN
1867 fnd_message.set_name ('INV', 'INV_NOT_STOCK_ENABLED');
1868 p_error_text := p_error_text || FND_CONST.NEWLINE || FND_MESSAGE.GET;
1869 END IF;
1870
1871 IF lr_inv_item.mtl_transactions_enabled_flag = 'N' THEN
1872 fnd_message.set_name ('FLM', 'FLM_TRX_ENABLED_FLAG');
1873 p_error_text := p_error_text || FND_CONST.NEWLINE || FND_MESSAGE.GET;
1874 END IF;
1875
1876 IF lr_inv_item.inventory_item_status_code <> 'Active' THEN
1877 fnd_message.set_name ('FLM', 'FLM_ITEM_STATUS_CODE');
1878 p_error_text := p_error_text || FND_CONST.NEWLINE || FND_MESSAGE.GET;
1879 END IF;
1880
1881 CLOSE c_inv_item;
1882 ELSE
1883 fnd_message.set_name ('FLM', 'FLM_ATTRIBUTE_INVALID');
1884 fnd_message.set_token ('ATTRIBUTE', 'Inventory Item Id');
1885 p_error_text := p_error_text || FND_CONST.NEWLINE || FND_MESSAGE.GET;
1886 END IF;
1887 END IF;
1888
1889 -- Subinventory name
1890 IF r_kanban_card_rec.subinventory_name IS NULL THEN
1891 fnd_message.set_name ('FLM', 'FLM_ATTRIBUTE_REQUIRED');
1892 fnd_message.set_token ('ATTRIBUTE', 'SubInventory Name');
1893 p_error_text := p_error_text || FND_CONST.NEWLINE || FND_MESSAGE.GET;
1894 ELSE
1895 OPEN c_subinv(p_subinv_name => r_kanban_card_rec.subinventory_name,
1896 p_org_id => r_kanban_card_rec.organization_id
1897 );
1898
1899 FETCH c_subinv INTO l_subinventory;
1900 CLOSE c_subinv;
1901
1902 IF l_subinventory IS NULL THEN
1903 fnd_message.set_name ('FLM', 'FLM_ATTRIBUTE_INVALID');
1904 fnd_message.set_token ('ATTRIBUTE', 'Subinventory Name');
1905 p_error_text := p_error_text || FND_CONST.NEWLINE || FND_MESSAGE.GET;
1906 END IF;
1907
1908 l_locator_control := get_locator_control(r_kanban_card_rec.inventory_item_id,
1909 r_kanban_card_rec.organization_id,
1910 r_kanban_card_rec.subinventory_name);
1911
1912 -- Validate locator_id
1913 IF l_locator_control <> FLM_KANBAN_MASSLOAD.locator_none THEN
1914 -- mandatory if subinventory is locator controlled
1915 IF r_kanban_card_rec.locator_id IS NULL THEN
1916 fnd_message.set_name ('FLM', 'FLM_ATTRIBUTE_REQUIRED');
1917 fnd_message.set_token ('ATTRIBUTE', 'Locator Id');
1918 p_error_text := p_error_text || FND_CONST.NEWLINE || FND_MESSAGE.GET;
1919
1920 ELSE
1921 IF NOT is_locator_id_valid(p_subinv_name => r_kanban_card_rec.subinventory_name
1922 ,p_locator_id => r_kanban_card_rec.locator_id
1923 ,p_org_id => r_kanban_card_rec.organization_id
1924 )
1925 THEN
1926 fnd_message.set_name ('FLM', 'FLM_ATTRIBUTE_INVALID');
1927 fnd_message.set_token ('ATTRIBUTE', 'Locator Id');
1928 p_error_text := p_error_text || FND_CONST.NEWLINE || FND_MESSAGE.GET;
1929 END IF;
1930 END IF;
1931 ELSE -- subinventory is not locator controlled
1932 IF r_kanban_card_rec.locator_id IS NOT NULL THEN
1933 r_kanban_card_rec.locator_id := NULL;
1934 fnd_message.set_name ('FLM', 'FLM_ATTRIBUTE_IGNORED');
1935 fnd_message.set_token ('ATTRIBUTE', 'Locator Id');
1936 mydebug(fnd_message.get);
1937 END IF;
1938 END IF;
1939
1940 END IF; --End Subinventory Name
1941 END IF;
1942
1943 --Fix bug 12430551
1944 IF l_transaction_type = FLM_KANBAN_MASSLOAD.KANBAN_ADD THEN
1945 IF r_kanban_card_rec.pull_sequence_id IS NULL THEN
1946 /************************/
1947 --Derive Pull sequence id
1948 /************************/
1949 IF r_kanban_card_rec.inventory_item_id IS NOT NULL
1950 AND r_kanban_card_rec.organization_id IS NOT NULL
1951 AND (r_kanban_card_rec.subinventory_name IS NOT NULL
1952 OR r_kanban_card_rec.locator_id IS NOT NULL ) THEN
1953 r_kanban_card_rec.pull_sequence_id := get_pull_sequence_id(p_invitem_id => r_kanban_card_rec.inventory_item_id
1954 ,p_org_id => r_kanban_card_rec.organization_id
1955 ,p_kan_plan_id => NULL
1956 ,p_subinv_name => r_kanban_card_rec.subinventory_name
1957 ,p_loc_id => r_kanban_card_rec.locator_id
1958 );
1959 --If the pull sequence id exists fetch the attribute values from pull sequence
1960 --Else consider the card as Adhoc card and validate the required attributes
1961 IF r_kanban_card_rec.pull_sequence_id IS NULL THEN
1962 fnd_message.set_name ('INV','INV_NO_PULLSEQ_EXISTS');
1963 fnd_message.set_token ('ORG_CODE', r_kanban_card_rec.organization_id);
1964 fnd_message.set_token ('ITEM_NAME', r_kanban_card_rec.inventory_item_id);
1965 fnd_message.set_token ('SUB_CODE', r_kanban_card_rec.subinventory_name);
1966 fnd_message.set_token ('LOCATOR_NAME', r_kanban_card_rec.locator_id);
1967 p_error_text := p_error_text || FND_CONST.NEWLINE || FND_MESSAGE.GET;
1968 END IF;
1969 END IF;
1970 END IF;-- pull seq id null
1971
1972 IF r_kanban_card_rec.pull_sequence_id IS NOT NULL THEN
1973 IF is_valid_pull_sequence_id(r_kanban_card_rec.pull_sequence_id) THEN
1974
1975 OPEN c_pullseq_info(r_kanban_card_rec.pull_sequence_id);
1976 FETCH c_pullseq_info INTO lr_pullseq_info;
1977 CLOSE c_pullseq_info;
1978
1979 r_kanban_card_rec.organization_id := lr_pullseq_info.organization_id;
1980 r_kanban_card_rec.inventory_item_id := lr_pullseq_info.inventory_item_id;
1981 r_kanban_card_rec.subinventory_name := lr_pullseq_info.subinventory_name;
1982 r_kanban_card_rec.source_type := lr_pullseq_info.source_type;
1983 r_kanban_card_rec.locator_id := lr_pullseq_info.locator_id;
1984 p_release_kanban_flag := lr_pullseq_info.release_kanban_flag;
1985
1986 OPEN c_cards_cnt(r_kanban_card_rec.pull_sequence_id);
1987 FETCH c_cards_cnt INTO l_cards_count;
1988 CLOSE c_cards_cnt;
1989
1990 /*uncomment the below once the planning_effectivityh column added to pull sequence */
1991 -- IF lr_pullseq_info.planning_effectivity <= SYSDATE THEN
1992 -- l_rem_cards := lr_pullseq_info.future_no__of_cards - l_cards_count;
1993 -- ELSE
1994 -- l_rem_cards := lr_pullseq_info.number_of_cards - l_cards_count;
1995 -- END IF;
1996
1997 /* Bug 11829013 - this validation is not needed when manually creating kanban cards
1998 IF l_rem_cards <= 0 THEN
1999 fnd_message.set_name ('FLM', 'FLM_NO_OF_CARDS_EXIST');
2000 fnd_message.set_token ('ATTRIBUTE', r_kanban_card_rec.pull_sequence_id);
2001 p_error_text := p_error_text || FND_CONST.NEWLINE || FND_MESSAGE.GET;
2002 END IF;
2003 */
2004
2005 --Validate Kanban Size
2006 IF lr_pullseq_info.kanban_size IS NOT NULL THEN
2007 r_kanban_card_rec.kanban_size := lr_pullseq_info.kanban_size;
2008 ELSIF r_kanban_card_rec.kanban_size IS NOT NULL THEN
2009 IF r_kanban_card_rec.kanban_size <= 0 THEN
2010 fnd_message.set_name ('FLM', 'FLM_ATTRIBUTE_INVALID');
2011 fnd_message.set_token ('ATTRIBUTE', 'Kanban Size');
2012 p_error_text := p_error_text || FND_CONST.NEWLINE || FND_MESSAGE.GET;
2013 END IF;
2014 ELSE
2015 fnd_message.set_name ('FLM', 'FLM_ATTRIBUTE_REQUIRED');
2016 fnd_message.set_token ('ATTRIBUTE', 'Kanban Size');
2017 p_error_text := p_error_text || FND_CONST.NEWLINE || FND_MESSAGE.GET;
2018 END IF;
2019
2020 --Validate kanban card number
2021 IF r_kanban_card_rec.kanban_card_number IS NOT NULL THEN
2022 IF is_valid_kanban_card(r_kanban_card_rec.pull_sequence_id
2023 ,r_kanban_card_rec.kanban_card_number
2024 ,NULL) THEN
2025 fnd_message.set_name ('FLM', 'FLM_ATTRIBUTE_EXISTS'); --Attribute already exists
2026 fnd_message.set_token ('ATTRIBUTE', 'Kanban Card Number');
2027 p_error_text := p_error_text || FND_CONST.NEWLINE || FND_MESSAGE.GET;
2028 END IF;
2029 END IF;
2030
2031 --Validate kanban card id
2032 IF r_kanban_card_rec.kanban_card_id IS NOT NULL THEN
2033 IF is_valid_kanban_card(r_kanban_card_rec.pull_sequence_id
2034 ,NULL
2035 ,r_kanban_card_rec.kanban_card_id) THEN
2036 fnd_message.set_name ('FLM', 'FLM_ATTRIBUTE_EXISTS'); --Attribute already exists
2037 fnd_message.set_token ('ATTRIBUTE', 'Kanban Card Id');
2038 p_error_text := p_error_text || FND_CONST.NEWLINE || FND_MESSAGE.GET;
2039 END IF;
2040 END IF;
2041
2042 --Validate Kanban Card Type- Replenishable or Non Replenishable
2043 IF r_kanban_card_rec.kanban_card_type IS NOT NULL THEN
2044 IF NOT is_lookup_code_valid(p_lookup_type => 'MTL_KANBAN_CARD_TYPE'
2045 ,p_lookup_code => r_kanban_card_rec.kanban_card_type
2046 )
2047 THEN
2048 /*
2049 fnd_message.set_name ('FLM','FLM_ATTRIBUTE_INVALID');
2050 fnd_message.set_token ('ATTRIBUTE', 'Kanban Card Type');
2051 p_error_text := p_error_text || FND_CONST.NEWLINE || FND_MESSAGE.GET;
2052 */
2053 r_kanban_card_rec.kanban_card_type := 1; -- Default to Replenishable Kanban Card.
2054 END IF;
2055 ELSE
2056 r_kanban_card_rec.kanban_card_type := 1; -- Default to Replenishable Kanban Card.
2057 END IF;
2058
2059 -- Validate supply status
2060 IF r_kanban_card_rec.supply_status IS NOT NULL AND
2061 r_kanban_card_rec.supply_status NOT IN (INV_Kanban_PVT.G_Supply_Status_New,INV_Kanban_PVT.G_Supply_Status_Full,INV_Kanban_PVT.G_Supply_Status_Empty)
2062 THEN
2063 fnd_message.set_name ('FLM','FLM_ATTRIBUTE_INVALID');
2064 fnd_message.set_token ('ATTRIBUTE', 'Supply Status');
2065 p_error_text := p_error_text || FND_CONST.NEWLINE || FND_MESSAGE.GET;
2066 END IF;
2067
2068 IF r_kanban_card_rec.supply_status IS NULL THEN
2069 r_kanban_card_rec.supply_status := INV_Kanban_PVT.G_Supply_Status_New;
2070 END IF;
2071
2072 -- Validate Card status
2073 IF r_kanban_card_rec.card_status IS NOT NULL AND
2074 r_kanban_card_rec.card_status = INV_Kanban_PVT.G_Card_Status_Cancel THEN
2075 fnd_message.set_name ('FLM','FLM_ATTRIBUTE_INVALID');
2076 fnd_message.set_token ('ATTRIBUTE', 'Card Status');
2077 p_error_text := p_error_text || FND_CONST.NEWLINE || FND_MESSAGE.GET;
2078 END IF;
2079
2080 IF r_kanban_card_rec.card_status IS NULL THEN
2081 r_kanban_card_rec.card_status := INV_Kanban_PVT.G_Card_Status_Active;
2082 END IF;
2083
2084 IF r_kanban_card_rec.supplier_id IS NULL THEN
2085 r_kanban_card_rec.supplier_id := lr_pullseq_info.supplier_id;
2086 END IF;
2087
2088 IF r_kanban_card_rec.supplier_site_id IS NULL THEN
2089 r_kanban_card_rec.supplier_site_id := lr_pullseq_info.supplier_site_id;
2090 END IF;
2091
2092 IF r_kanban_card_rec.source_organization_id IS NULL THEN
2093 r_kanban_card_rec.source_organization_id := lr_pullseq_info.source_organization_id;
2094 END IF;
2095
2096 IF r_kanban_card_rec.source_subinventory IS NULL THEN
2097 r_kanban_card_rec.source_subinventory := lr_pullseq_info.source_subinventory;
2098 END IF;
2099
2100 IF r_kanban_card_rec.source_locator_id IS NULL THEN
2101 r_kanban_card_rec.source_locator_id := lr_pullseq_info.source_locator_id;
2102 END IF;
2103
2104 IF r_kanban_card_rec.wip_line_id IS NULL THEN
2105 r_kanban_card_rec.wip_line_id := lr_pullseq_info.wip_line_id;
2106 END IF;
2107 ELSE
2108 fnd_message.set_name ('FLM', 'FLM_ATTRIBUTE_INVALID');
2109 fnd_message.set_token ('ATTRIBUTE', 'Pull Sequence Id');
2110 p_error_text := p_error_text || FND_CONST.NEWLINE || FND_MESSAGE.GET;
2111 END IF;--valid pull seq id
2112 END IF; --pull seq id not null
2113
2114 -- Dont Allow user to enter following values during card creation
2115 r_kanban_card_rec.last_print_date := null;
2116 r_kanban_card_rec.replenishment_count := 0;
2117 r_kanban_card_rec.current_replnsh_cycle_id := null;
2118
2119 END IF; --For p_transaction_type ADD
2120
2121 IF l_transaction_type = FLM_KANBAN_MASSLOAD.KANBAN_CHANGE OR
2122 l_transaction_type = FLM_KANBAN_MASSLOAD.KANBAN_DELETE THEN
2123
2124 --Fix bug 12430551, derive pull sequence id if it's null
2125 IF r_kanban_card_rec.pull_sequence_id IS NULL THEN
2126 r_kanban_card_rec.pull_sequence_id := get_pull_sequence_id(p_invitem_id => r_kanban_card_rec.inventory_item_id
2127 ,p_org_id => r_kanban_card_rec.organization_id
2128 ,p_kan_plan_id => NULL
2129 ,p_subinv_name => r_kanban_card_rec.subinventory_name
2130 ,p_loc_id => r_kanban_card_rec.locator_id
2131 );
2132 END IF;
2133 IF r_kanban_card_rec.kanban_card_id IS NULL THEN
2134 IF r_kanban_card_rec.kanban_card_number IS NULL THEN
2135 fnd_message.set_name ('FLM', 'FLM_ATTRIBUTE_REQUIRED');
2136 fnd_message.set_token ('ATTRIBUTE', 'Kanban Card');
2137 p_error_text := p_error_text || FND_CONST.NEWLINE || FND_MESSAGE.GET;
2138 ELSE
2139 IF r_kanban_card_rec.pull_sequence_id IS NOT NULL THEN
2140 r_kanban_card_rec.kanban_card_id := FLM_KANBAN_PUB.default_kanban_card_id(p_pull_sequence_id => r_kanban_card_rec.pull_sequence_id
2141 ,p_kanban_card_number => r_kanban_card_rec.kanban_card_number);
2142 IF r_kanban_card_rec.kanban_card_id IS NULL THEN
2143 fnd_message.set_name ('FLM', 'FLM_ATTRIBUTE_INVALID');
2144 fnd_message.set_token ('ATTRIBUTE', 'Pull Sequence Id / Kanban card Number');
2145 p_error_text := p_error_text || FND_CONST.NEWLINE || FND_MESSAGE.GET;
2146 -- Fix bug 12426260 and 12424326, get the value of l_card_id,l_card_sts,l_supply_sts as it will be use for validations
2147 ELSE -- if r_kanban_card_rec.kanban_card_id IS not NULL
2148 OPEN c_card_exist(r_kanban_card_rec.kanban_card_id);
2149 FETCH c_card_exist INTO l_card_id,l_card_sts,l_supply_sts;
2150 CLOSE c_card_exist;
2151 END IF;
2152 ELSE
2153 fnd_message.set_name ('FLM', 'FLM_ATTRIBUTE_REQUIRED');
2154 fnd_message.set_token ('ATTRIBUTE', 'Pull Sequence Id for Kanban card Number');
2155 p_error_text := p_error_text || FND_CONST.NEWLINE || FND_MESSAGE.GET;
2156 END IF;
2157 END IF;
2158 ELSE
2159 l_card_id := 0;
2160 OPEN c_card_exist(r_kanban_card_rec.kanban_card_id);
2161 FETCH c_card_exist INTO l_card_id,l_card_sts,l_supply_sts;
2162 CLOSE c_card_exist;
2163 IF l_card_id = 0 OR l_card_id IS NULL THEN
2164 fnd_message.set_name ('FLM','FLM_ATTRIBUTE_INVALID');
2165 fnd_message.set_token ('ATTRIBUTE', 'Kanban Card Id');
2166 p_error_text := p_error_text || FND_CONST.NEWLINE || FND_MESSAGE.GET;
2167 END IF;
2168 END IF;
2169 END IF;
2170
2171 --Fix bug 12426260, user can only delete card in cancel status
2172 IF l_transaction_type = FLM_KANBAN_MASSLOAD.KANBAN_DELETE THEN
2173 /*IF l_supply_sts <> INV_Kanban_PVT.G_Supply_Status_New THEN
2174 fnd_message.set_name ('FLM','FLM_CANNOT_DELETE_CARD');
2175 p_error_text := p_error_text || FND_CONST.NEWLINE || FND_MESSAGE.GET;
2176 END IF;*/
2177 IF l_card_sts <> INV_Kanban_PVT.G_Card_Status_Cancel THEN
2178 fnd_message.set_name ('FLM','FLM_CANNOT_DELETE_CARD');
2179 p_error_text := p_error_text || FND_CONST.NEWLINE || FND_MESSAGE.GET;
2180 END IF;
2181 END IF;
2182
2183 IF l_transaction_type = FLM_KANBAN_MASSLOAD.KANBAN_CHANGE THEN
2184
2185 IF l_card_id IS NOT NULL AND l_card_id <> 0 THEN
2186 -- Default kanban card values while updating..
2187 SELECT MKC.kanban_card_id
2188 ,MKC.kanban_card_number
2189 ,MKC.pull_sequence_id
2190 ,MKC.inventory_item_id
2191 ,MKC.organization_id
2192 ,MKC.subinventory_name
2193 ,MKC.locator_id
2194 ,MKC.kanban_card_type
2195 ,MKC.kanban_size
2196 ,MKC.source_type
2197 ,DECODE(r_kanban_card_rec.supply_status,NULL,MKC.supply_status,FND_API.G_MISS_NUM,NULL,r_kanban_card_rec.supply_status)
2198 ,DECODE(r_kanban_card_rec.card_status,NULL,MKC.card_status,FND_API.G_MISS_NUM,NULL,r_kanban_card_rec.card_status)
2199 ,MKC.creation_date
2200 ,MKC.created_by
2201 ,DECODE(r_kanban_card_rec.supplier_id,NULL,MKC.supplier_id,FND_API.G_MISS_NUM,NULL,r_kanban_card_rec.supplier_id)
2202 ,DECODE(r_kanban_card_rec.supplier_site_id,NULL,MKC.supplier_site_id,FND_API.G_MISS_NUM,NULL,r_kanban_card_rec.supplier_site_id)
2203 ,DECODE(r_kanban_card_rec.source_organization_id,NULL,MKC.source_organization_id,FND_API.G_MISS_NUM,NULL,r_kanban_card_rec.source_organization_id)
2204 ,DECODE(r_kanban_card_rec.source_subinventory,NULL,MKC.source_subinventory,FND_API.G_MISS_CHAR,NULL,r_kanban_card_rec.source_subinventory)
2205 ,DECODE(r_kanban_card_rec.source_locator_id,NULL,MKC.source_locator_id,FND_API.G_MISS_NUM,NULL,r_kanban_card_rec.source_locator_id)
2206 ,MKC.current_replnsh_cycle_id
2207 ,DECODE(r_kanban_card_rec.kanban_error_code,NULL,MKC.error_code,FND_API.G_MISS_NUM,NULL,r_kanban_card_rec.kanban_error_code)
2208 ,DECODE(r_kanban_card_rec.wip_line_id,NULL,MKC.wip_line_id,FND_API.G_MISS_NUM,NULL,r_kanban_card_rec.wip_line_id)
2209 ,MKC.replenishment_count
2210 ,DECODE(r_kanban_card_rec.max_replenishments,NULL,MKC.max_replenishments,FND_API.G_MISS_NUM,NULL,r_kanban_card_rec.max_replenishments)
2211 ,DECODE(r_kanban_card_rec.disable_date,NULL,MKC.disable_date,FND_API.G_MISS_DATE,NULL,r_kanban_card_rec.disable_date)
2212 ,DECODE(r_kanban_card_rec.replacement_flag,NULL,MKC.replacement_flag,FND_API.G_MISS_NUM,NULL,r_kanban_card_rec.replacement_flag)
2213 ,DECODE(r_kanban_card_rec.attribute_category,NULL,MKC.attribute_category,FND_API.G_MISS_CHAR,NULL,r_kanban_card_rec.attribute_category)
2214 ,DECODE(r_kanban_card_rec.attribute1,NULL,MKC.attribute1,FND_API.G_MISS_CHAR,NULL,r_kanban_card_rec.attribute1)
2215 ,DECODE(r_kanban_card_rec.attribute2,NULL,MKC.attribute2,FND_API.G_MISS_CHAR,NULL,r_kanban_card_rec.attribute2)
2216 ,DECODE(r_kanban_card_rec.attribute3,NULL,MKC.attribute3,FND_API.G_MISS_CHAR,NULL,r_kanban_card_rec.attribute3)
2217 ,DECODE(r_kanban_card_rec.attribute4,NULL,MKC.attribute4,FND_API.G_MISS_CHAR,NULL,r_kanban_card_rec.attribute4)
2218 ,DECODE(r_kanban_card_rec.attribute5,NULL,MKC.attribute5,FND_API.G_MISS_CHAR,NULL,r_kanban_card_rec.attribute5)
2219 ,DECODE(r_kanban_card_rec.attribute6,NULL,MKC.attribute6,FND_API.G_MISS_CHAR,NULL,r_kanban_card_rec.attribute6)
2220 ,DECODE(r_kanban_card_rec.attribute7,NULL,MKC.attribute7,FND_API.G_MISS_CHAR,NULL,r_kanban_card_rec.attribute7)
2221 ,DECODE(r_kanban_card_rec.attribute8,NULL,MKC.attribute8,FND_API.G_MISS_CHAR,NULL,r_kanban_card_rec.attribute8)
2222 ,DECODE(r_kanban_card_rec.attribute9,NULL,MKC.attribute9,FND_API.G_MISS_CHAR,NULL,r_kanban_card_rec.attribute9)
2223 ,DECODE(r_kanban_card_rec.attribute10,NULL,MKC.attribute10,FND_API.G_MISS_CHAR,NULL,r_kanban_card_rec.attribute10)
2224 ,DECODE(r_kanban_card_rec.attribute11,NULL,MKC.attribute11,FND_API.G_MISS_CHAR,NULL,r_kanban_card_rec.attribute11)
2225 ,DECODE(r_kanban_card_rec.attribute12,NULL,MKC.attribute12,FND_API.G_MISS_CHAR,NULL,r_kanban_card_rec.attribute12)
2226 ,DECODE(r_kanban_card_rec.attribute13,NULL,MKC.attribute13,FND_API.G_MISS_CHAR,NULL,r_kanban_card_rec.attribute13)
2227 ,DECODE(r_kanban_card_rec.attribute14,NULL,MKC.attribute14,FND_API.G_MISS_CHAR,NULL,r_kanban_card_rec.attribute14)
2228 ,DECODE(r_kanban_card_rec.attribute15,NULL,MKC.attribute15,FND_API.G_MISS_CHAR,NULL,r_kanban_card_rec.attribute15)
2229 ,MKC.last_print_date
2230 INTO r_kanban_card_rec.kanban_card_id
2231 ,r_kanban_card_rec.kanban_card_number
2232 ,r_kanban_card_rec.pull_sequence_id
2233 ,r_kanban_card_rec.inventory_item_id
2234 ,r_kanban_card_rec.organization_id
2235 ,r_kanban_card_rec.subinventory_name
2236 ,r_kanban_card_rec.locator_id
2237 ,r_kanban_card_rec.kanban_card_type
2238 ,r_kanban_card_rec.kanban_size
2239 ,r_kanban_card_rec.source_type
2240 ,r_kanban_card_rec.supply_status
2241 ,r_kanban_card_rec.card_status
2242 ,r_kanban_card_rec.creation_date
2243 ,r_kanban_card_rec.created_by
2244 ,r_kanban_card_rec.supplier_id
2245 ,r_kanban_card_rec.supplier_site_id
2246 ,r_kanban_card_rec.source_organization_id
2247 ,r_kanban_card_rec.source_subinventory
2248 ,r_kanban_card_rec.source_locator_id
2249 ,r_kanban_card_rec.current_replnsh_cycle_id
2250 ,r_kanban_card_rec.kanban_error_code
2251 ,r_kanban_card_rec.wip_line_id
2252 ,r_kanban_card_rec.replenishment_count
2253 ,r_kanban_card_rec.max_replenishments
2254 ,r_kanban_card_rec.disable_date
2255 ,r_kanban_card_rec.replacement_flag
2256 ,r_kanban_card_rec.attribute_category
2257 ,r_kanban_card_rec.attribute1
2258 ,r_kanban_card_rec.attribute2
2259 ,r_kanban_card_rec.attribute3
2260 ,r_kanban_card_rec.attribute4
2261 ,r_kanban_card_rec.attribute5
2262 ,r_kanban_card_rec.attribute6
2263 ,r_kanban_card_rec.attribute7
2264 ,r_kanban_card_rec.attribute8
2265 ,r_kanban_card_rec.attribute9
2266 ,r_kanban_card_rec.attribute10
2267 ,r_kanban_card_rec.attribute11
2268 ,r_kanban_card_rec.attribute12
2269 ,r_kanban_card_rec.attribute13
2270 ,r_kanban_card_rec.attribute14
2271 ,r_kanban_card_rec.attribute15
2272 ,r_kanban_card_rec.last_print_date
2273 FROM MTL_KANBAN_CARDS MKC
2274 WHERE MKC.kanban_card_id = l_card_id;
2275 END IF;
2276
2277 IF l_card_sts = INV_Kanban_PVT.G_Card_Status_Cancel THEN
2278 fnd_message.set_name ('FLM','FLM_CANNOT_UPDATE_CARD');
2279 p_error_text := p_error_text || FND_CONST.NEWLINE || FND_MESSAGE.GET;
2280 END IF;
2281
2282 -- Validate supply status
2283 IF r_kanban_card_rec.supply_status IS NOT NULL THEN
2284 IF NOT is_lookup_code_valid(p_lookup_type => 'MTL_KANBAN_SUPPLY_STATUS'
2285 ,p_lookup_code => r_kanban_card_rec.supply_status
2286 )
2287 THEN
2288 fnd_message.set_name ('FLM','FLM_ATTRIBUTE_INVALID');
2289 fnd_message.set_token ('ATTRIBUTE', 'Supply Status');
2290 p_error_text := p_error_text || FND_CONST.NEWLINE || FND_MESSAGE.GET;
2291 END IF;
2292 END IF;
2293
2294 -- Validate Card status
2295 IF r_kanban_card_rec.card_status IS NOT NULL THEN
2296 IF NOT is_lookup_code_valid(p_lookup_type => 'MTL_KANBAN_CARD_STATUS'
2297 ,p_lookup_code => r_kanban_card_rec.card_status
2298 )
2299 THEN
2300 fnd_message.set_name ('FLM','FLM_ATTRIBUTE_INVALID');
2301 fnd_message.set_token ('ATTRIBUTE', 'Card Status');
2302 p_error_text := p_error_text || FND_CONST.NEWLINE || FND_MESSAGE.GET;
2303 END IF;
2304 END IF;
2305 END IF;
2306
2307
2308 IF l_transaction_type = FLM_KANBAN_MASSLOAD.KANBAN_ADD
2309 OR l_transaction_type = FLM_KANBAN_MASSLOAD.KANBAN_CHANGE THEN
2310
2311 IF l_transaction_type = FLM_KANBAN_MASSLOAD.KANBAN_CHANGE THEN
2312 lr_pullseq_info.supplier_id := NULL;
2313 lr_pullseq_info.supplier_site_id := NULL;
2314 lr_pullseq_info.wip_line_id := NULL;
2315 lr_pullseq_info.source_organization_id := NULL;
2316 lr_pullseq_info.source_subinventory := NULL;
2317 lr_pullseq_info.source_locator_id := NULL;
2318 END IF;
2319
2320 IF nvl(r_kanban_card_rec.kanban_size,0) <= 0 THEN
2321 fnd_message.set_name ('FLM','FLM_ATTRIBUTE_INVALID');
2322 fnd_message.set_token ('ATTRIBUTE', 'Kanban Size');
2323 p_error_text := p_error_text || FND_CONST.NEWLINE || FND_MESSAGE.GET;
2324 END IF;
2325
2326 -- Validate Error Code
2327 IF r_kanban_card_rec.kanban_error_code IS NOT NULL THEN
2328 IF NOT is_lookup_code_valid(p_lookup_type => 'MTL_KANBAN_ERROR_CODE'
2329 ,p_lookup_code => r_kanban_card_rec.kanban_error_code
2330 )
2331 THEN
2332 fnd_message.set_name ('FLM','FLM_ATTRIBUTE_INVALID');
2333 fnd_message.set_token ('ATTRIBUTE', 'Error Code');
2334 p_error_text := p_error_text || FND_CONST.NEWLINE || FND_MESSAGE.GET;
2335 END IF;
2336 END IF;
2337
2338 IF r_kanban_card_rec.disable_date IS NOT NULL THEN
2339 IF r_kanban_card_rec.disable_date <= SYSDATE THEN
2340 fnd_message.set_name ('FLM','FLM_ATTRIBUTE_INVALID');
2341 fnd_message.set_token ('ATTRIBUTE', 'Disable Date');
2342 p_error_text := p_error_text || FND_CONST.NEWLINE || FND_MESSAGE.GET;
2343 END IF;
2344 END IF;
2345
2346 -- Validate Max Replenishments
2347 IF r_kanban_card_rec.max_replenishments IS NOT NULL THEN
2348 IF l_transaction_type = FLM_KANBAN_MASSLOAD.KANBAN_CHANGE
2349 AND r_kanban_card_rec.kanban_card_id IS NOT NULL THEN
2350 -- Consider replenishments done instead of status changes.
2351 SELECT count(distinct replenishment_cycle_id)
2352 INTO l_max_replenishments
2353 FROM mtl_kanban_card_activity
2354 WHERE kanban_card_id = r_kanban_card_rec.kanban_card_id and
2355 replenishment_cycle_id <> -1;
2356 ELSE
2357 l_max_replenishments := 0;
2358 END IF;
2359
2360 IF NVL(r_kanban_card_rec.max_replenishments,0) <= l_max_replenishments THEN
2361 fnd_message.set_name ('FLM','FLM_ATTRIBUTE_INVALID');
2362 fnd_message.set_token ('ATTRIBUTE', 'Max Replenishments');
2363 p_error_text := p_error_text || FND_CONST.NEWLINE || FND_MESSAGE.GET;
2364 END IF;
2365 END IF;
2366
2367 IF r_kanban_card_rec.source_type = INV_Kanban_PVT.G_Source_Type_Supplier THEN
2368 --Validate Supplier
2369 IF lr_pullseq_info.supplier_id IS NULL THEN
2370 IF r_kanban_card_rec.supplier_id IS NULL THEN
2371 IF r_kanban_card_rec.supplier_name IS NOT NULL THEN
2372 r_kanban_card_rec.supplier_id := FLM_KANBAN_PUB.default_supplier_id(p_supplier_name => r_kanban_card_rec.supplier_name);
2373 END IF;
2374 ELSE
2375 IF is_supplier_id_invalid(p_supplier_id => r_kanban_card_rec.supplier_id) THEN
2376 fnd_message.set_name ('FLM','FLM_ATTRIBUTE_INVALID');
2377 fnd_message.set_token ('ATTRIBUTE', 'Supplier Id');
2378 p_error_text := p_error_text || FND_CONST.NEWLINE || FND_MESSAGE.GET;
2379 END IF;
2380 END IF;
2381
2382 IF r_kanban_card_rec.supplier_id IS NOT NULL THEN
2383 --Validate Supplier Site
2384 IF r_kanban_card_rec.supplier_site_id IS NULL THEN
2385 IF r_kanban_card_rec.supplier_site_code IS NULL THEN
2386 fnd_message.set_name ('FLM', 'FLM_ATTRIBUTE_REQUIRED');
2387 fnd_message.set_token ('ATTRIBUTE', 'Supplier Site');
2388 p_error_text := p_error_text || FND_CONST.NEWLINE || FND_MESSAGE.GET;
2389 ELSE
2390 r_kanban_card_rec.supplier_site_id := FLM_KANBAN_PUB.default_supplier_site_id(p_supplier_id => r_kanban_card_rec.supplier_id
2391 ,p_supplier_site_code => r_kanban_card_rec.supplier_site_code
2392 ,p_org_id => r_kanban_card_rec.organization_id
2393 );
2394 END IF;
2395 ELSE
2396 IF is_supplier_site_id_invalid(p_supplier_id => r_kanban_card_rec.supplier_id
2397 ,p_supplier_site_id => r_kanban_card_rec.supplier_site_id
2398 ,p_org_id => r_kanban_card_rec.organization_id) THEN
2399 fnd_message.set_name ('FLM', 'FLM_ATTRIBUTE_REQUIRED');
2400 fnd_message.set_token ('ATTRIBUTE', 'Supplier Site Id');
2401 p_error_text := p_error_text || FND_CONST.NEWLINE || FND_MESSAGE.GET;
2402 END IF;
2403 END IF; -- end validate Supplier Site
2404 END IF;
2405 END IF;-- end if for validate suppler
2406 ELSE -- source type is not suplier
2407 IF r_kanban_card_rec.supplier_id IS NOT NULL
2408 AND r_kanban_card_rec.supplier_site_id IS NOT NULL THEN
2409 r_kanban_card_rec.supplier_id := NULL;
2410 r_kanban_card_rec.supplier_site_id := NULL;
2411 fnd_message.set_name ('FLM', 'FLM_ATTRIBUTE_IGNORED');
2412 fnd_message.set_token ('ATTRIBUTE', 'Supplier and Site');
2413 mydebug(fnd_message.get);
2414 END IF;
2415 END IF;
2416
2417 -- Validate source subinventory and source org id
2418 -- if source type is inter or intra org
2419 IF r_kanban_card_rec.source_type IN (inv_kanban_pvt.g_source_type_intraorg
2420 ,inv_kanban_pvt.g_source_type_interorg)
2421 THEN
2422 IF r_kanban_card_rec.source_organization_id IS NOT NULL AND lr_pullseq_info.source_organization_id IS NULL THEN
2423 IF FLM_KANBAN_PUB.is_org_id_invalid (r_kanban_card_rec.source_organization_id) THEN
2424 fnd_message.set_name ('FLM', 'FLM_ATTRIBUTE_INVALID');
2425 fnd_message.set_token ('ATTRIBUTE', 'Source Organization Id');
2426 p_error_text := p_error_text || FND_CONST.NEWLINE || FND_MESSAGE.GET;
2427 ELSE
2428 IF r_kanban_card_rec.source_subinventory IS NULL THEN
2429 fnd_message.set_name ('FLM', 'FLM_ATTRIBUTE_REQUIRED');
2430 fnd_message.set_token ('ATTRIBUTE','Source Subinventory');
2431 p_error_text := p_error_text || fnd_const.NEWLINE || fnd_message.get;
2432 ELSE
2433 -- validate source subinventory
2434 OPEN c_subinv(p_subinv_name => r_kanban_card_rec.source_subinventory
2435 ,p_org_id => r_kanban_card_rec.source_organization_id
2436 );
2437 FETCH c_subinv INTO l_source_subinventory;
2438 CLOSE c_subinv;
2439
2440 IF l_source_subinventory IS NULL THEN
2441 fnd_message.set_name ('FLM', 'FLM_ATTRIBUTE_INVALID');
2442 fnd_message.set_token ('ATTRIBUTE', 'Source Subinventory');
2443 p_error_text := p_error_text || fnd_const.NEWLINE || fnd_message.get;
2444 END IF;
2445 END IF;
2446 END IF;
2447 END IF;
2448
2449 l_source_locator_control := get_locator_control(r_kanban_card_rec.inventory_item_id,
2450 r_kanban_card_rec.source_organization_id,
2451 r_kanban_card_rec.source_subinventory);
2452
2453 -- Validate source locator_id
2454 IF l_source_locator_control <> FLM_KANBAN_MASSLOAD.LOCATOR_NONE THEN
2455 -- mandatory if source subinventory is locator controlled
2456 IF r_kanban_card_rec.source_locator_id IS NOT NULL AND lr_pullseq_info.source_organization_id IS NULL
2457 THEN
2458 IF NOT is_locator_id_valid (p_subinv_name => r_kanban_card_rec.source_subinventory
2459 ,p_locator_id => r_kanban_card_rec.source_locator_id
2460 ,p_org_id => r_kanban_card_rec.source_organization_id
2461 )
2462 THEN
2463 fnd_message.set_name ('FLM','FLM_ATTRIBUTE_INVALID');
2464 fnd_message.set_token ('ATTRIBUTE', 'Source Locator Id');
2465 p_error_text := p_error_text || fnd_const.NEWLINE || fnd_message.get;
2466 END IF;
2467 END IF;
2468 END IF;
2469 ELSE
2470 IF r_kanban_card_rec.source_organization_id IS NOT NULL
2471 OR r_kanban_card_rec.source_subinventory IS NOT NULL
2472 OR r_kanban_card_rec.source_locator_id IS NOT NULL THEN
2473 r_kanban_card_rec.source_organization_id := NULL;
2474 r_kanban_card_rec.source_subinventory := NULL;
2475 r_kanban_card_rec.source_locator_id := NULL;
2476 fnd_message.set_name ('FLM', 'FLM_ATTRIBUTE_IGNORED');
2477 fnd_message.set_token ('ATTRIBUTE', 'Source Org Subinv Loc');
2478 mydebug(fnd_message.get);
2479 END IF;
2480 END IF;--end if for source type
2481 -- Validate wip line id only if source type is production
2482 IF r_kanban_card_rec.source_type = inv_kanban_pvt.g_source_type_production THEN
2483 IF r_kanban_card_rec.wip_line_id IS NULL AND lr_pullseq_info.wip_line_id IS NULL THEN
2484 IF r_kanban_card_rec.wip_line_code IS NOT NULL AND r_kanban_card_rec.wip_line_code <> FND_API.G_MISS_CHAR THEN
2485 r_kanban_card_rec.wip_line_id := FLM_KANBAN_PUB.default_wip_line_id(p_wip_line_code => r_kanban_card_rec.wip_line_code
2486 ,p_org_id => r_kanban_card_rec.organization_id
2487 );
2488 IF r_kanban_card_rec.wip_line_id IS NULL THEN
2489 fnd_message.set_name ('FLM', 'FLM_ATTRIBUTE_INVALID');
2490 fnd_message.set_token ('ATTRIBUTE', 'WIP Line Code');
2491 p_error_text := p_error_text || fnd_const.NEWLINE || fnd_message.get;
2492 END IF;
2493 END IF;
2494 ELSE
2495 IF is_wip_line_id_invalid(p_wip_line_id => r_kanban_card_rec.wip_line_id) THEN
2496 fnd_message.set_name ('FLM', 'FLM_ATTRIBUTE_INVALID');
2497 fnd_message.set_token ('ATTRIBUTE', 'WIP Line Id');
2498 p_error_text := p_error_text || fnd_const.NEWLINE || fnd_message.get;
2499 END IF;
2500 END IF;
2501 ELSE
2502 IF r_kanban_card_rec.wip_line_id IS NOT NULL THEN
2503 r_kanban_card_rec.wip_line_id := NULL;
2504 fnd_message.set_name ('FLM', 'FLM_ATTRIBUTE_IGNORED');
2505 fnd_message.set_token ('ATTRIBUTE', 'WIP Line Id');
2506 mydebug(fnd_message.get);
2507 END IF;
2508 END IF;
2509 /**********************************/
2510 --Validation for additional columns
2511 /**********************************/
2512 --Validation for Temporary cards
2513
2514 IF r_kanban_card_rec.max_replenishments IS NOT NULL AND
2515 r_kanban_card_rec.disable_date IS NOT NULL THEN
2516 fnd_message.set_name ('FLM','FLM_NOT_BOTH_ATTRIBUTES');
2517 fnd_message.set_token ('ATTRIBUTE1', 'Disable Date');
2518 fnd_message.set_token ('ATTRIBUTE2', 'Max Replenishments');
2519 p_error_text := p_error_text || FND_CONST.NEWLINE || FND_MESSAGE.GET;
2520
2521 ELSIF r_kanban_card_rec.max_replenishments IS NOT NULL
2522 OR r_kanban_card_rec.disable_date IS NOT NULL THEN
2523 IF r_kanban_card_rec.disable_date IS NOT NULL
2524 AND r_kanban_card_rec.disable_date <= SYSDATE THEN
2525 fnd_message.set_name ('FLM','FLM_ATTRIBUTE_INVALID');
2526 fnd_message.set_token ('ATTRIBUTE', 'Disable Date');
2527 p_error_text := p_error_text || FND_CONST.NEWLINE || FND_MESSAGE.GET;
2528 END IF;
2529
2530 IF r_kanban_card_rec.max_replenishments IS NOT NULL
2531 AND r_kanban_card_rec.max_replenishments <= 0 THEN
2532 fnd_message.set_name ('FLM','FLM_ATTRIBUTE_INVALID');
2533 fnd_message.set_token ('ATTRIBUTE', 'Max Replenishments');
2534 p_error_text := p_error_text || FND_CONST.NEWLINE || FND_MESSAGE.GET;
2535 END IF;
2536 END IF;
2537
2538 END IF;
2539 IF p_error_text IS NOT NULL THEN -- Validation failed
2540 x_ret_status := FND_API.G_RET_STS_ERROR;
2541 ELSE
2542 x_ret_status := FND_API.G_RET_STS_SUCCESS;
2543 END IF;
2544 p_kanban_card_rec := r_kanban_card_rec;
2545 mydebug('End Procedure validate_kanban_cards');
2546 EXCEPTION
2547 WHEN OTHERS THEN
2548 p_kanban_card_rec := r_kanban_card_rec;
2549 x_ret_status := FND_API.G_RET_STS_ERROR;
2550 p_error_text := p_error_text || SQLERRM;
2551 END validate_kanban_cards;
2552
2553 -----------------------------------
2554 --Procedure to Process Kanban Cards
2555 -----------------------------------
2556 PROCEDURE process_kanban_cards(p_kanban_card_rec IN OUT NOCOPY flm_ekanban_pub.kanban_card_rec_type
2557 ,p_release_kanban_flag IN NUMBER
2558 ,p_transaction_type IN NUMBER
2559 ,x_ret_status OUT NOCOPY VARCHAR2
2560 ,x_err_msg OUT NOCOPY VARCHAR2
2561 )
2562 IS
2563 --r_card_tab_rec inv_kanban_pvt.kanban_card_rec_type;
2564 r_kanban_card_rec flm_ekanban_pub.kanban_card_rec_type;
2565 l_kanban_card_ids INV_Kanban_PVT.kanban_card_id_tbl_type;
2566 -- l_pull_seq_rec INV_Kanban_PVT.pull_sequence_rec_type; This is not required
2567 l_report_id NUMBER := NULL;
2568 l_no_of_cards NUMBER := 1;
2569 l_release_kanban_flag NUMBER;
2570 l_print_kanban_card NUMBER := 0;
2571 l_supply_status NUMBER;
2572 l_card_status NUMBER;
2573 l_supplier_id NUMBER;
2574 l_supp_site_id NUMBER;
2575 l_ret_status VARCHAR2(10):=FND_API.G_RET_STS_SUCCESS;
2576 l_ret_msg_code NUMBER;
2577 l_error_msg VARCHAR2(2000);
2578 l_count NUMBER;
2579 l_Current_Replnsh_Cycle_Id Number := null;
2580 l_temp number;
2581 BEGIN
2582 mydebug('In Procedure process_kanban_cards');
2583 r_kanban_card_rec := p_kanban_card_rec;
2584 l_release_kanban_flag := p_release_kanban_flag;
2585 l_supplier_id := NULL;
2586 l_supp_site_id := NULL;
2587 IF p_transaction_type = FLM_KANBAN_MASSLOAD.KANBAN_ADD THEN
2588 mydebug('Transaction Type ADD');
2589
2590 -- To fetch supplier information
2591 mydebug('Calling mtl_multiple_suppliers.get_supplier');
2592
2593 FLM_MULTIPLE_SUPPLIERS.GET_SUPPLIER(p_pull_seq_id => r_kanban_card_rec.pull_sequence_id,
2594 p_org_id => r_kanban_card_rec.organization_id,
2595 p_cardstatus => r_kanban_card_rec.card_status,
2596 x_supplier_id => l_supplier_id,
2597 x_supplier_site_id => l_supp_site_id,
2598 x_retcode => l_ret_status,
2599 x_err_msg => l_error_msg
2600 );
2601 IF l_ret_status <> FND_API.G_RET_STS_SUCCESS THEN
2602 Raise FND_API.G_EXC_ERROR;
2603 END IF;
2604
2605
2606 IF r_kanban_card_rec.supply_status IS NULL THEN
2607 r_kanban_card_rec.supply_status := INV_Kanban_PVT.G_Supply_Status_New;
2608 END IF;
2609
2610 -- This is not required
2611 /*
2612 l_pull_seq_rec.pull_sequence_id := r_kanban_card_rec.pull_sequence_id;
2613 l_pull_seq_rec.organization_id := r_kanban_card_rec.organization_id;
2614 l_pull_seq_rec.inventory_item_id := r_kanban_card_rec.inventory_item_id;
2615 l_pull_seq_rec.subinventory_name := r_kanban_card_rec.subinventory_name;
2616 l_pull_seq_rec.locator_id := r_kanban_card_rec.locator_id;
2617 l_pull_seq_rec.source_type := r_kanban_card_rec.source_type;
2618 l_pull_seq_rec.Kanban_size := r_kanban_card_rec.kanban_size;
2619 l_pull_seq_rec.number_of_cards := 1;
2620 l_pull_seq_rec.supplier_id := NVL(l_supplier_id, r_kanban_card_rec.supplier_id) ;
2621 l_pull_seq_rec.supplier_site_id := NVL(l_supp_site_id,r_kanban_card_rec.supplier_site_id);
2622 l_pull_seq_rec.source_organization_id := r_kanban_card_rec.source_organization_id ;
2623 l_pull_seq_rec.source_subinventory := r_kanban_card_rec.source_subinventory ;
2624 l_pull_seq_rec.source_locator_id := r_kanban_card_rec.source_locator_id ;
2625 l_pull_seq_rec.wip_line_id := r_kanban_card_rec.wip_line_id ;
2626 l_pull_seq_rec.release_kanban_flag := l_release_kanban_flag;
2627 */
2628
2629 mydebug('Calling INVKBCGN.card_check_and_create');
2630 -- This API doesn't allow us to create cards in Plan Status from Planning. Also card doesn't get created in 'Onhold' status.
2631 /*
2632 INV_kanban_PVT.create_kanban_cards(l_ret_status,
2633 l_kanban_card_ids,
2634 l_pull_seq_rec,
2635 r_kanban_card_rec.supply_status,
2636 r_kanban_card_rec.kanban_card_id,
2637 r_kanban_card_rec.kanban_card_number,
2638 r_kanban_card_rec.replenishment_count,
2639 r_kanban_card_rec.max_replenishments,
2640 r_kanban_card_rec.disable_date,
2641 r_kanban_card_rec.replacement_flag
2642 );
2643 */
2644
2645 IF l_release_kanban_flag = 2 THEN
2646 r_kanban_card_rec.card_status := INV_Kanban_Pvt.G_Card_Status_Hold;
2647 r_kanban_card_rec.supply_status := inv_kanban_pvt.g_supply_status_full;
2648 END IF;
2649
2650 -- Default it from Custom Hook
2651 if r_kanban_card_rec.kanban_card_number is null then
2652 r_kanban_card_rec.kanban_card_number := FLM_KANBAN_CUSTOM_PKG.custom_kanban_number(r_kanban_card_rec.pull_sequence_id);
2653 end if;
2654 -- Also Validate VAlue from Hook.
2655 if r_kanban_card_rec.kanban_card_number is not null then
2656 Begin
2657 select 1 into l_temp
2658 from mtl_kanban_cards
2659 where organization_id = r_kanban_card_rec.organization_id
2660 and kanban_card_number = r_kanban_card_rec.kanban_card_number;
2661
2662 fnd_message.set_name ('FLM', 'FLM_ATTRIBUTE_EXISTS');
2663 fnd_message.set_token ('ATTRIBUTE', 'Kanban Card Number');
2664 l_error_msg := FND_MESSAGE.GET;
2665 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
2666 exception
2667 when others then
2668 null;
2669 end;
2670
2671 end if;
2672
2673 INV_KanbanCard_PKG.Insert_Row(
2674 X_Return_Status => l_ret_status,
2675 P_Kanban_Card_Id => r_kanban_card_rec.kanban_card_id,
2676 P_Kanban_Card_Number => r_kanban_card_rec.kanban_card_number,
2677 P_Pull_Sequence_Id => r_kanban_card_rec.pull_sequence_id,
2678 P_Inventory_item_id => r_kanban_card_rec.inventory_item_id,
2679 P_Organization_id => r_kanban_card_rec.organization_id,
2680 P_Subinventory_name => r_kanban_card_rec.subinventory_name,
2681 P_Supply_Status => r_kanban_card_rec.supply_status,
2682 P_Card_Status => r_kanban_card_rec.card_status,
2683 P_Kanban_Card_Type => Nvl(r_kanban_card_rec.kanban_card_type,INV_Kanban_Pvt.g_card_type_replenishable),
2684 P_Source_type => r_kanban_card_rec.Source_type,
2685 P_Kanban_size => nvl(r_kanban_card_rec.Kanban_size,0),
2686 P_Last_Update_Date => SYSDATE,
2687 P_Last_Updated_By => FND_GLOBAL.USER_ID,
2688 P_Creation_Date => SYSDATE,
2689 P_Created_By => FND_GLOBAL.USER_ID,
2690 P_Last_Update_Login => FND_GLOBAL.LOGIN_ID,
2691 P_Last_Print_Date => NULL,
2692 P_Locator_id => r_kanban_card_rec.Locator_id,
2693 P_Supplier_id => l_supplier_id,
2694 P_Supplier_site_id => l_supp_site_id,
2695 P_Source_Organization_id => r_kanban_card_rec.Source_Organization_id,
2696 P_Source_Subinventory => r_kanban_card_rec.Source_Subinventory,
2697 P_Source_Locator_id => r_kanban_card_rec.Source_Locator_id,
2698 P_wip_line_id => r_kanban_card_rec.wip_line_id,
2699 P_Current_Replnsh_Cycle_Id=> l_Current_Replnsh_Cycle_Id,
2700 P_document_type => NULL,
2701 P_document_header_id => NULL,
2702 P_document_detail_id => NULL,
2703 P_error_code => null,
2704 P_Attribute_Category => r_kanban_card_rec.attribute_category ,
2705 P_Attribute1 => r_kanban_card_rec.attribute1 ,
2706 P_Attribute2 => r_kanban_card_rec.attribute2 ,
2707 P_Attribute3 => r_kanban_card_rec.attribute3 ,
2708 P_Attribute4 => r_kanban_card_rec.attribute4 ,
2709 P_Attribute5 => r_kanban_card_rec.attribute5 ,
2710 P_Attribute6 => r_kanban_card_rec.attribute6 ,
2711 P_Attribute7 => r_kanban_card_rec.attribute7 ,
2712 P_Attribute8 => r_kanban_card_rec.attribute8 ,
2713 P_Attribute9 => r_kanban_card_rec.attribute9 ,
2714 P_Attribute10 => r_kanban_card_rec.attribute10,
2715 P_Attribute11 => r_kanban_card_rec.attribute11,
2716 P_Attribute12 => r_kanban_card_rec.attribute12,
2717 P_Attribute13 => r_kanban_card_rec.attribute13,
2718 P_Attribute14 => r_kanban_card_rec.attribute14,
2719 P_Attribute15 => r_kanban_card_rec.attribute15,
2720 P_Request_Id => NULL,
2721 P_Program_application_Id => NULL,
2722 P_Program_Id => NULL,
2723 P_Program_Update_date => NULL,
2724 p_release_kanban_flag => l_release_kanban_flag,
2725 --eKanban Changes
2726 p_replenishment_count => r_kanban_card_rec.replenishment_count,
2727 p_max_replenishments => r_kanban_card_rec.max_replenishments,
2728 p_disable_date => r_kanban_card_rec.disable_date,
2729 p_replacement_flag => r_kanban_card_rec.replacement_flag);
2730
2731
2732
2733 IF l_ret_status = FND_API.G_RET_STS_ERROR THEN
2734 Raise FND_API.G_EXC_ERROR;
2735 END IF;
2736 IF l_ret_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2737 mydebug('Unexpected error after INV_kanban_PVT.create_kanban_cards');
2738 l_error_msg := 'Unexpected error after INV_kanban_PVT.create_kanban_cards ';
2739 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
2740 END IF;
2741 -- r_kanban_card_rec.kanban_card_id := l_kanban_card_ids(1);
2742
2743 ELSIF p_transaction_type = FLM_KANBAN_MASSLOAD.KANBAN_CHANGE THEN
2744 -- Check for the card status change/ supply status change/ supplier info/ error code
2745 mydebug('Transaction Type UPDATE');
2746 SELECT MTC.supply_status
2747 ,MTC.card_status
2748 INTO l_supply_status, l_card_status
2749 FROM MTL_KANBAN_CARDS MTC
2750 WHERE MTC.kanban_card_id = r_kanban_card_rec.kanban_card_id;
2751
2752 IF (SQL%NOTFOUND) THEN
2753 l_error_msg := 'Eror while fetching Card Details ';
2754 Raise FND_API.G_EXC_ERROR;
2755 END IF;
2756
2757 mydebug('Calling FLM_KANBANCARD_PUB.UPDATE_CARD');
2758 FLM_KANBANCARD_PUB.UPDATE_CARD(p_kanban_card_id => r_kanban_card_rec.kanban_card_id,
2759 p_kanban_card_number => r_kanban_card_rec.kanban_card_number,
2760 p_pull_sequence_id => r_kanban_card_rec.pull_sequence_id,
2761 p_inventory_item_id => r_kanban_card_rec.inventory_item_id,
2762 p_organization_id => r_kanban_card_rec.organization_id,
2763 p_subinventory_name => r_kanban_card_rec.subinventory_name,
2764 p_supply_status => l_supply_status,
2765 p_supply_status_to => NVL(r_kanban_card_rec.supply_status,l_supply_status),
2766 p_card_status => l_card_status,
2767 p_card_status_to => NVL(r_kanban_card_rec.card_status,l_card_status),
2768 p_kanban_card_type => r_kanban_card_rec.kanban_card_type,
2769 p_source_type => r_kanban_card_rec.source_type,
2770 p_kanban_size => r_kanban_card_rec.kanban_size,
2771 p_last_update_date => r_kanban_card_rec.last_update_date,
2772 p_last_updated_by => r_kanban_card_rec.last_update_by,
2773 p_creation_date => r_kanban_card_rec.creation_date,
2774 p_created_by => r_kanban_card_rec.created_by,
2775 p_locator_id => r_kanban_card_rec.locator_id,
2776 p_supplier_id => r_kanban_card_rec.supplier_id,
2777 p_supplier_site_id => r_kanban_card_rec.supplier_site_id,
2778 p_source_organization_id => r_kanban_card_rec.source_organization_id,
2779 p_source_subinventory => r_kanban_card_rec.source_subinventory,
2780 p_source_locator_id => r_kanban_card_rec.source_locator_id,
2781 p_wip_line_id => r_kanban_card_rec.wip_line_id,
2782 p_current_replnsh_cycle_id => r_kanban_card_rec.current_replnsh_cycle_id,
2783 p_document_type => NULL,
2784 p_document_header_id => NULL,
2785 p_document_detail_id => NULL,
2786 p_error_code => r_kanban_card_rec.kanban_error_code,
2787 p_last_update_login => r_kanban_card_rec.last_update_login,
2788 p_last_print_date => r_kanban_card_rec.last_print_date,
2789 p_attribute_category => r_kanban_card_rec.attribute_category,
2790 p_attribute1 => r_kanban_card_rec.attribute1,
2791 p_attribute2 => r_kanban_card_rec.attribute2,
2792 p_attribute3 => r_kanban_card_rec.attribute3,
2793 p_attribute4 => r_kanban_card_rec.attribute4,
2794 p_attribute5 => r_kanban_card_rec.attribute5,
2795 p_attribute6 => r_kanban_card_rec.attribute6,
2796 p_attribute7 => r_kanban_card_rec.attribute7,
2797 p_attribute8 => r_kanban_card_rec.attribute8,
2798 p_attribute9 => r_kanban_card_rec.attribute9,
2799 p_attribute10 => r_kanban_card_rec.attribute10,
2800 p_attribute11 => r_kanban_card_rec.attribute11,
2801 p_attribute12 => r_kanban_card_rec.attribute12,
2802 p_attribute13 => r_kanban_card_rec.attribute13,
2803 p_attribute14 => r_kanban_card_rec.attribute14,
2804 p_attribute15 => r_kanban_card_rec.attribute15,
2805 p_request_id => r_kanban_card_rec.request_id,
2806 p_program_application_id => r_kanban_card_rec.program_application_id,
2807 p_program_id => r_kanban_card_rec.program_id,
2808 p_program_update_date => r_kanban_card_rec.program_update_date,
2809 p_lot_item_id => NULL,
2810 p_lot_number => NULL,
2811 p_lot_item_revision => NULL,
2812 p_lot_subinventory_code => NULL,
2813 p_lot_location_id => NULL,
2814 p_lot_quantity => NULL,
2815 p_replenish_quantity => NULL,
2816 p_need_by_date => NULL,
2817 p_source_wip_entity_id => NULL,
2818 p_replenishment_count => r_kanban_card_rec.replenishment_count,
2819 p_max_replenishments => r_kanban_card_rec.max_replenishments,
2820 p_disable_date => r_kanban_card_rec.disable_date,
2821 p_replacement_flag => r_kanban_card_rec.replacement_flag,
2822 P_VERIFY => 'N',
2823 X_RETCODE => l_ret_status,
2824 X_RET_MSG_CODE => l_ret_msg_code,
2825 X_ERR_MSG => l_error_msg);
2826 IF l_ret_status <> FND_API.G_RET_STS_SUCCESS THEN
2827 x_err_msg := l_error_msg;
2828 END IF;
2829
2830 ELSIF p_transaction_type = FLM_KANBAN_MASSLOAD.KANBAN_DELETE THEN
2831 mydebug('Transaction Type DELETE');
2832 INV_KanbanCard_PKG.delete_row(x_return_status => l_ret_status,
2833 p_kanban_card_id => r_kanban_card_rec.kanban_card_id);
2834 IF l_ret_status = FND_API.G_RET_STS_ERROR THEN
2835 x_err_msg := ' Error While Deleting the kanban card.'||SQLERRM;
2836 END IF;
2837 END IF;
2838 p_kanban_card_rec := r_kanban_card_rec;
2839 x_ret_status := l_ret_status;
2840 EXCEPTION
2841 WHEN FND_API.G_EXC_ERROR THEN
2842 p_kanban_card_rec := r_kanban_card_rec;
2843 x_ret_status := FND_API.G_RET_STS_ERROR;
2844 x_err_msg := 'error in process_kanban_cards procedure '||l_error_msg||SQLERRM;
2845 invkbcgn.print_error;
2846 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2847 p_kanban_card_rec := r_kanban_card_rec;
2848 x_ret_status := FND_API.G_RET_STS_UNEXP_ERROR;
2849 x_err_msg := ' Error in process_kanban_cards procedure '||l_error_msg||SQLERRM;
2850 invkbcgn.print_error;
2851 WHEN OTHERS THEN
2852 p_kanban_card_rec := r_kanban_card_rec;
2853 x_err_msg := 'Unexpected error in process_kanban_cards procedure '||SQLERRM;
2854 x_ret_status := FND_API.G_RET_STS_UNEXP_ERROR;
2855 invkbcgn.print_error;
2856 END process_kanban_cards;
2857
2858 ------------------------------------------------------------------------
2859 --Procedure to load Cards, which calls validate and process kanban cards
2860 ------------------------------------------------------------------------
2861 PROCEDURE process_cards( p_pull_sequence_id IN NUMBER DEFAULT NULL
2862 ,p_kanban_card_id IN OUT NOCOPY NUMBER
2863 ,p_kanban_card_number IN VARCHAR2 DEFAULT NULL
2864 ,p_inv_item_code IN VARCHAR2 DEFAULT NULL
2865 ,p_inventory_item_id IN NUMBER DEFAULT NULL
2866 ,p_organization_code IN VARCHAR2 DEFAULT NULL
2867 ,p_organization_id IN NUMBER DEFAULT NULL
2868 ,p_subinventory_name IN VARCHAR2 DEFAULT NULL
2869 ,p_supply_status IN NUMBER DEFAULT NULL
2870 ,p_card_status IN NUMBER DEFAULT NULL
2871 ,p_kanban_card_type IN NUMBER DEFAULT NULL
2872 ,p_source_type IN NUMBER DEFAULT NULL
2873 ,p_kanban_size IN NUMBER DEFAULT NULL
2874 ,p_locator_id IN NUMBER DEFAULT NULL
2875 ,p_supplier_id IN NUMBER DEFAULT NULL
2876 ,p_supplier_name IN VARCHAR2 DEFAULT NULL
2877 ,p_supplier_site_id IN NUMBER DEFAULT NULL
2878 ,p_supplier_site_code IN VARCHAR2 DEFAULT NULL
2879 ,p_source_organization_id IN NUMBER DEFAULT NULL
2880 ,p_source_subinventory IN VARCHAR2 DEFAULT NULL
2881 ,p_source_locator_id IN NUMBER DEFAULT NULL
2882 ,p_current_replnsh_cycle_id IN NUMBER DEFAULT NULL
2883 ,p_kanban_error_code IN NUMBER DEFAULT NULL
2884 ,p_wip_line_code IN VARCHAR2 DEFAULT NULL
2885 ,p_wip_line_id IN NUMBER DEFAULT NULL
2886 ,p_attribute_category IN VARCHAR2 DEFAULT NULL
2887 ,p_attribute1 IN VARCHAR2 DEFAULT NULL
2888 ,p_attribute2 IN VARCHAR2 DEFAULT NULL
2889 ,p_attribute3 IN VARCHAR2 DEFAULT NULL
2890 ,p_attribute4 IN VARCHAR2 DEFAULT NULL
2891 ,p_attribute5 IN VARCHAR2 DEFAULT NULL
2892 ,p_attribute6 IN VARCHAR2 DEFAULT NULL
2893 ,p_attribute7 IN VARCHAR2 DEFAULT NULL
2894 ,p_attribute8 IN VARCHAR2 DEFAULT NULL
2895 ,p_attribute9 IN VARCHAR2 DEFAULT NULL
2896 ,p_attribute10 IN VARCHAR2 DEFAULT NULL
2897 ,p_attribute11 IN VARCHAR2 DEFAULT NULL
2898 ,p_attribute12 IN VARCHAR2 DEFAULT NULL
2899 ,p_attribute13 IN VARCHAR2 DEFAULT NULL
2900 ,p_attribute14 IN VARCHAR2 DEFAULT NULL
2901 ,p_attribute15 IN VARCHAR2 DEFAULT NULL
2902 ,p_last_print_date IN DATE DEFAULT NULL
2903 ,p_last_update_date IN DATE DEFAULT NULL
2904 ,p_last_update_by IN NUMBER DEFAULT NULL
2905 ,p_creation_date IN DATE DEFAULT NULL
2906 ,p_created_by IN NUMBER DEFAULT NULL
2907 ,p_last_update_login IN NUMBER DEFAULT NULL
2908 ,p_request_id IN NUMBER DEFAULT NULL
2909 ,p_program_application_id IN NUMBER DEFAULT NULL
2910 ,p_program_id IN NUMBER DEFAULT NULL
2911 ,p_program_update_date IN DATE DEFAULT NULL
2912 ,p_replenishment_count IN NUMBER DEFAULT NULL
2913 ,p_max_replenishments IN NUMBER DEFAULT NULL
2914 ,p_disable_date IN DATE DEFAULT NULL
2915 ,p_replacement_flag IN NUMBER DEFAULT NULL
2916 ,p_transaction_type IN NUMBER
2917 ,x_ret_status OUT NOCOPY VARCHAR2
2918 ,x_err_msg OUT NOCOPY VARCHAR2
2919 )
2920 IS
2921 r_kanban_card_rec flm_ekanban_pub.kanban_card_rec_type;
2922 l_release_kanban_flag NUMBER;
2923 l_ret_status VARCHAR2(1):= FND_API.G_RET_STS_SUCCESS;
2924 l_err_msg VARCHAR2(2000);
2925 BEGIN
2926 mydebug('In Procedure FLM_KANBAN_PUB.process_cards');
2927 mydebug('Assigning values to record type variable ');
2928
2929 r_kanban_card_rec.pull_sequence_id := p_pull_sequence_id;
2930 r_kanban_card_rec.kanban_card_id := p_kanban_card_id;
2931 r_kanban_card_rec.kanban_card_number := p_kanban_card_number;
2932 r_kanban_card_rec.inventory_item_code := p_inv_item_code;
2933 r_kanban_card_rec.inventory_item_id := p_inventory_item_id;
2934 r_kanban_card_rec.organization_code := p_organization_code;
2935 r_kanban_card_rec.organization_id := p_organization_id;
2936 r_kanban_card_rec.subinventory_name := p_subinventory_name;
2937 r_kanban_card_rec.supply_status := p_supply_status;
2938 r_kanban_card_rec.card_status := p_card_status;
2939 r_kanban_card_rec.kanban_card_type := p_kanban_card_type;
2940 r_kanban_card_rec.source_type := p_source_type;
2941 r_kanban_card_rec.kanban_size := p_kanban_size;
2942 r_kanban_card_rec.locator_id := p_locator_id;
2943 r_kanban_card_rec.supplier_id := p_supplier_id;
2944 r_kanban_card_rec.supplier_name := p_supplier_name;
2945 r_kanban_card_rec.supplier_site_id := p_supplier_site_id;
2946 r_kanban_card_rec.supplier_site_code := p_supplier_site_code;
2947 r_kanban_card_rec.source_organization_id := p_source_organization_id;
2948 r_kanban_card_rec.source_subinventory := p_source_subinventory;
2949 r_kanban_card_rec.source_locator_id := p_source_locator_id;
2950 r_kanban_card_rec.current_replnsh_cycle_id := p_current_replnsh_cycle_id;
2951 r_kanban_card_rec.kanban_error_code := p_kanban_error_code;
2952 r_kanban_card_rec.wip_line_code := p_wip_line_code;
2953 r_kanban_card_rec.wip_line_id := p_wip_line_id;
2954 r_kanban_card_rec.attribute_category := p_attribute_category;
2955 r_kanban_card_rec.attribute1 := p_attribute1;
2956 r_kanban_card_rec.attribute2 := p_attribute2;
2957 r_kanban_card_rec.attribute3 := p_attribute3;
2958 r_kanban_card_rec.attribute4 := p_attribute4;
2959 r_kanban_card_rec.attribute5 := p_attribute5;
2960 r_kanban_card_rec.attribute6 := p_attribute6;
2961 r_kanban_card_rec.attribute7 := p_attribute7;
2962 r_kanban_card_rec.attribute8 := p_attribute8;
2963 r_kanban_card_rec.attribute9 := p_attribute9;
2964 r_kanban_card_rec.attribute10 := p_attribute10;
2965 r_kanban_card_rec.attribute11 := p_attribute11;
2966 r_kanban_card_rec.attribute12 := p_attribute12;
2967 r_kanban_card_rec.attribute13 := p_attribute13;
2968 r_kanban_card_rec.attribute14 := p_attribute14;
2969 r_kanban_card_rec.attribute15 := p_attribute15;
2970 r_kanban_card_rec.last_print_date := p_last_print_date;
2971 r_kanban_card_rec.last_update_date := g_last_update_date;
2972 r_kanban_card_rec.last_update_by := g_user_id;
2973 r_kanban_card_rec.creation_date := g_creation_date;
2974 r_kanban_card_rec.created_by := g_user_id;
2975 r_kanban_card_rec.last_update_login := g_user_login_id;
2976 r_kanban_card_rec.request_id := p_request_id;
2977 r_kanban_card_rec.program_application_id := p_program_application_id;
2978 r_kanban_card_rec.program_id := p_program_id;
2979 r_kanban_card_rec.program_update_date := p_program_update_date;
2980 r_kanban_card_rec.replenishment_count := p_replenishment_count;
2981 r_kanban_card_rec.max_replenishments := p_max_replenishments;
2982 r_kanban_card_rec.disable_date := p_disable_date;
2983 r_kanban_card_rec.replacement_flag := p_replacement_flag;
2984
2985 mydebug('Calling validate_kanban_cards');
2986 --Validate Kanban Cards
2987 validate_kanban_cards(p_kanban_card_rec => r_kanban_card_rec
2988 ,p_transaction_type => p_transaction_type
2989 ,p_release_kanban_flag => l_release_kanban_flag
2990 ,p_error_text => l_err_msg
2991 ,x_ret_status => l_ret_status
2992 );
2993 IF l_ret_status = FND_API.G_RET_STS_ERROR THEN
2994 x_ret_status := l_ret_status;
2995 x_err_msg := l_err_msg;
2996 return;
2997 ELSE
2998 mydebug('Calling process_kanban_cards');
2999 --Process Kanban Cards
3000 process_kanban_cards(p_kanban_card_rec => r_kanban_card_rec
3001 ,p_release_kanban_flag => l_release_kanban_flag
3002 ,p_transaction_type => p_transaction_type
3003 ,x_ret_status => l_ret_status
3004 ,x_err_msg => l_err_msg
3005 );
3006 IF l_ret_status IN (FND_API.G_RET_STS_ERROR,FND_API.G_RET_STS_UNEXP_ERROR) THEN
3007 x_ret_status := l_ret_status;
3008 x_err_msg := l_err_msg;
3009 return;
3010 ELSIF l_ret_status = 'W' THEN /*Bug#14378439: Setting x_err_mesg to error message, but ensuring that no return happens*/
3011 x_ret_status := l_ret_status;
3012 x_err_msg := l_err_msg;
3013 END IF;
3014 -- Added for Bug 12425069.
3015 if p_transaction_type = FLM_KANBAN_MASSLOAD.kanban_change then
3016 if nvl(p_kanban_size,r_kanban_card_rec.kanban_size) <> r_kanban_card_rec.kanban_size then
3017 l_ret_status := 'W';
3018 fnd_message.set_name ('FLM', 'FLM_ATTRIBUTE_IGNORED');
3019 fnd_message.set_token ('ATTRIBUTE', 'Kanban Size');
3020 x_err_msg := x_err_msg || FND_CONST.NEWLINE || FND_MESSAGE.GET;
3021 end if;
3022 if nvl(p_source_type,r_kanban_card_rec.source_type) <> r_kanban_card_rec.source_type then
3023 l_ret_status := 'W';
3024 fnd_message.set_name ('FLM', 'FLM_ATTRIBUTE_IGNORED');
3025 fnd_message.set_token ('ATTRIBUTE', 'Source Type');
3026 x_err_msg := x_err_msg || FND_CONST.NEWLINE || FND_MESSAGE.GET;
3027 end if;
3028 if nvl(p_kanban_card_number,r_kanban_card_rec.kanban_card_number) <> r_kanban_card_rec.kanban_card_number then
3029 l_ret_status := 'W';
3030 fnd_message.set_name ('FLM', 'FLM_ATTRIBUTE_IGNORED');
3031 fnd_message.set_token ('ATTRIBUTE', 'Kanban Card Number');
3032 x_err_msg := x_err_msg || FND_CONST.NEWLINE || FND_MESSAGE.GET;
3033 end if;
3034 end if;
3035 END IF;
3036 p_kanban_card_id := r_kanban_card_rec.kanban_card_id;
3037 x_ret_status := l_ret_status;
3038 EXCEPTION
3039 WHEN OTHERS THEN
3040 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3041 THEN
3042 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME
3043 ,'process_cards'
3044 );
3045 END IF;
3046 x_ret_status := FND_API.G_RET_STS_UNEXP_ERROR;
3047 END process_cards;
3048
3049 PROCEDURE process_cards(p_kanban_card_rec IN OUT NOCOPY flm_ekanban_pub.kanban_card_rec_type
3050 ,p_transaction_type IN NUMBER
3051 ,p_commit_flag IN VARCHAR2 DEFAULT 'N'
3052 ,x_ret_status OUT NOCOPY VARCHAR2
3053 ,x_err_msg OUT NOCOPY VARCHAR2
3054 )
3055 IS
3056 l_ret_status VARCHAR2(1);
3057 l_err_msg VARCHAR2(2000);
3058 BEGIN
3059
3060 mydebug('Calling process_cards Procedure');
3061
3062 process_cards( p_pull_sequence_id => p_kanban_card_rec.pull_sequence_id
3063 ,p_kanban_card_id => p_kanban_card_rec.kanban_card_id
3064 ,p_kanban_card_number => p_kanban_card_rec.kanban_card_number
3065 ,p_inv_item_code => p_kanban_card_rec.inventory_item_code
3066 ,p_inventory_item_id => p_kanban_card_rec.inventory_item_id
3067 ,p_organization_code => p_kanban_card_rec.organization_code
3068 ,p_organization_id => p_kanban_card_rec.organization_id
3069 ,p_subinventory_name => p_kanban_card_rec.subinventory_name
3070 ,p_supply_status => p_kanban_card_rec.supply_status
3071 ,p_card_status => p_kanban_card_rec.card_status
3072 ,p_kanban_card_type => p_kanban_card_rec.kanban_card_type
3073 ,p_source_type => p_kanban_card_rec.source_type
3074 ,p_kanban_size => p_kanban_card_rec.kanban_size
3075 ,p_locator_id => p_kanban_card_rec.locator_id
3076 ,p_supplier_id => p_kanban_card_rec.supplier_id
3077 ,p_supplier_name => p_kanban_card_rec.supplier_name
3078 ,p_supplier_site_id => p_kanban_card_rec.supplier_site_id
3079 ,p_supplier_site_code => p_kanban_card_rec.supplier_site_code
3080 ,p_source_organization_id => p_kanban_card_rec.source_organization_id
3081 ,p_source_subinventory => p_kanban_card_rec.source_subinventory
3082 ,p_source_locator_id => p_kanban_card_rec.source_locator_id
3083 ,p_current_replnsh_cycle_id => p_kanban_card_rec.current_replnsh_cycle_id
3084 ,p_kanban_error_code => p_kanban_card_rec.kanban_error_code
3085 ,p_wip_line_code => p_kanban_card_rec.wip_line_code
3086 ,p_wip_line_id => p_kanban_card_rec.wip_line_id
3087 ,p_attribute_category => p_kanban_card_rec.attribute_category
3088 ,p_attribute1 => p_kanban_card_rec.attribute1
3089 ,p_attribute2 => p_kanban_card_rec.attribute2
3090 ,p_attribute3 => p_kanban_card_rec.attribute3
3091 ,p_attribute4 => p_kanban_card_rec.attribute4
3092 ,p_attribute5 => p_kanban_card_rec.attribute5
3093 ,p_attribute6 => p_kanban_card_rec.attribute6
3094 ,p_attribute7 => p_kanban_card_rec.attribute7
3095 ,p_attribute8 => p_kanban_card_rec.attribute8
3096 ,p_attribute9 => p_kanban_card_rec.attribute9
3097 ,p_attribute10 => p_kanban_card_rec.attribute10
3098 ,p_attribute11 => p_kanban_card_rec.attribute11
3099 ,p_attribute12 => p_kanban_card_rec.attribute12
3100 ,p_attribute13 => p_kanban_card_rec.attribute13
3101 ,p_attribute14 => p_kanban_card_rec.attribute14
3102 ,p_attribute15 => p_kanban_card_rec.attribute15
3103 ,p_last_print_date => p_kanban_card_rec.last_print_date
3104 ,p_last_update_date => p_kanban_card_rec.last_update_date
3105 ,p_last_update_by => p_kanban_card_rec.last_update_by
3106 ,p_creation_date => p_kanban_card_rec.creation_date
3107 ,p_created_by => p_kanban_card_rec.created_by
3108 ,p_last_update_login => p_kanban_card_rec.last_update_login
3109 ,p_request_id => p_kanban_card_rec.request_id
3110 ,p_program_application_id => p_kanban_card_rec.program_application_id
3111 ,p_program_id => p_kanban_card_rec.program_id
3112 ,p_program_update_date => p_kanban_card_rec.program_update_date
3113 ,p_replenishment_count => p_kanban_card_rec.replenishment_count
3114 ,p_max_replenishments => p_kanban_card_rec.max_replenishments
3115 ,p_disable_date => p_kanban_card_rec.disable_date
3116 ,p_replacement_flag => p_kanban_card_rec.replacement_flag
3117 ,p_transaction_type => p_transaction_type
3118 ,x_ret_status => l_ret_status
3119 ,x_err_msg => l_err_msg
3120 );
3121 x_ret_status := l_ret_status;
3122 x_err_msg := l_err_msg;
3123 IF p_commit_flag = 'Y' THEN
3124 COMMIT;
3125 END IF;
3126 END process_cards;
3127
3128 ---------------------------------
3129 --Procedure to load Kanban Cards
3130 ---------------------------------
3131 PROCEDURE process_kc(p_card_int_tbl IN OUT NOCOPY flm_kanban_massload.card_int_tbl_type
3132 ,x_ret_status OUT NOCOPY VARCHAR2
3133 )
3134 IS
3135 r_kanban_card_rec flm_ekanban_pub.kanban_card_rec_type;
3136 l_ret_status VARCHAR2(1);
3137 l_err_msg VARCHAR2(2000);
3138 l_count NUMBER := 0;
3139 BEGIN
3140 mydebug('In Procedure FLM_KANBAN_PUB.process_kc');
3141
3142 FOR i IN p_card_int_tbl.first..p_card_int_tbl.last
3143 LOOP
3144
3145 r_kanban_card_rec.pull_sequence_id := p_card_int_tbl(i).pull_sequence_id;
3146 r_kanban_card_rec.kanban_card_id := p_card_int_tbl(i).kanban_card_id;
3147 r_kanban_card_rec.kanban_card_number := p_card_int_tbl(i).kanban_card_number;
3148 r_kanban_card_rec.inventory_item_code := p_card_int_tbl(i).inventory_item_code;
3149 r_kanban_card_rec.inventory_item_id := p_card_int_tbl(i).inventory_item_id;
3150 r_kanban_card_rec.organization_code := p_card_int_tbl(i).organization_code;
3151 r_kanban_card_rec.organization_id := p_card_int_tbl(i).organization_id;
3152 r_kanban_card_rec.subinventory_name := p_card_int_tbl(i).subinventory_name;
3153 r_kanban_card_rec.supply_status := p_card_int_tbl(i).supply_status;
3154 r_kanban_card_rec.card_status := p_card_int_tbl(i).card_status;
3155 r_kanban_card_rec.kanban_card_type := p_card_int_tbl(i).kanban_card_type;
3156 r_kanban_card_rec.source_type := p_card_int_tbl(i).source_type;
3157 r_kanban_card_rec.kanban_size := p_card_int_tbl(i).kanban_size;
3158 r_kanban_card_rec.locator_id := p_card_int_tbl(i).locator_id;
3159 r_kanban_card_rec.supplier_id := p_card_int_tbl(i).supplier_id;
3160 r_kanban_card_rec.supplier_name := p_card_int_tbl(i).supplier_name;
3161 r_kanban_card_rec.supplier_site_id := p_card_int_tbl(i).supplier_site_id;
3162 r_kanban_card_rec.supplier_site_code := p_card_int_tbl(i).supplier_site_code;
3163 r_kanban_card_rec.source_organization_id := p_card_int_tbl(i).source_organization_id;
3164 r_kanban_card_rec.source_subinventory := p_card_int_tbl(i).source_subinventory;
3165 r_kanban_card_rec.source_locator_id := p_card_int_tbl(i).source_locator_id;
3166 r_kanban_card_rec.kanban_error_code := p_card_int_tbl(i).kanban_error_code;
3167 r_kanban_card_rec.wip_line_code := p_card_int_tbl(i).wip_line_code;
3168 r_kanban_card_rec.wip_line_id := p_card_int_tbl(i).wip_line_id;
3169 r_kanban_card_rec.attribute_category := p_card_int_tbl(i).attribute_category;
3170 r_kanban_card_rec.attribute1 := p_card_int_tbl(i).attribute1;
3171 r_kanban_card_rec.attribute2 := p_card_int_tbl(i).attribute2;
3172 r_kanban_card_rec.attribute3 := p_card_int_tbl(i).attribute3;
3173 r_kanban_card_rec.attribute4 := p_card_int_tbl(i).attribute4;
3174 r_kanban_card_rec.attribute5 := p_card_int_tbl(i).attribute5;
3175 r_kanban_card_rec.attribute6 := p_card_int_tbl(i).attribute6;
3176 r_kanban_card_rec.attribute7 := p_card_int_tbl(i).attribute7;
3177 r_kanban_card_rec.attribute8 := p_card_int_tbl(i).attribute8;
3178 r_kanban_card_rec.attribute9 := p_card_int_tbl(i).attribute9;
3179 r_kanban_card_rec.attribute10 := p_card_int_tbl(i).attribute10;
3180 r_kanban_card_rec.attribute11 := p_card_int_tbl(i).attribute11;
3181 r_kanban_card_rec.attribute12 := p_card_int_tbl(i).attribute12;
3182 r_kanban_card_rec.attribute13 := p_card_int_tbl(i).attribute13;
3183 r_kanban_card_rec.attribute14 := p_card_int_tbl(i).attribute14;
3184 r_kanban_card_rec.attribute15 := p_card_int_tbl(i).attribute15;
3185 r_kanban_card_rec.last_print_date := p_card_int_tbl(i).last_print_date;
3186 r_kanban_card_rec.last_update_date := p_card_int_tbl(i).last_update_date;
3187 r_kanban_card_rec.last_update_by := p_card_int_tbl(i).last_updated_by;
3188 r_kanban_card_rec.creation_date := p_card_int_tbl(i).creation_date;
3189 r_kanban_card_rec.created_by := p_card_int_tbl(i).created_by;
3190 r_kanban_card_rec.last_update_login := p_card_int_tbl(i).last_update_login;
3191 r_kanban_card_rec.request_id := p_card_int_tbl(i).request_id;
3192 r_kanban_card_rec.program_application_id := p_card_int_tbl(i).program_application_id;
3193 r_kanban_card_rec.program_id := p_card_int_tbl(i).program_id;
3194 r_kanban_card_rec.program_update_date := p_card_int_tbl(i).program_update_date;
3195 r_kanban_card_rec.replenishment_count := p_card_int_tbl(i).replenishment_count;
3196 r_kanban_card_rec.max_replenishments := p_card_int_tbl(i).max_replenishments;
3197 r_kanban_card_rec.disable_date := p_card_int_tbl(i).disable_date;
3198 r_kanban_card_rec.replacement_flag := p_card_int_tbl(i).replacement_flag;
3199
3200 mydebug('Calling process_cards Procedure');
3201 process_cards(p_kanban_card_rec => r_kanban_card_rec
3202 ,p_transaction_type => p_card_int_tbl(i).transaction_type
3203 ,p_commit_flag => 'N'
3204 ,x_ret_status => l_ret_status
3205 ,x_err_msg => l_err_msg
3206 );
3207
3208 IF l_ret_status = FND_API.G_RET_STS_ERROR THEN
3209 p_card_int_tbl(i).process_status := FLM_KANBAN_MASSLOAD.ERROR;
3210 p_card_int_tbl(i).error_text := l_err_msg;
3211 fnd_file.put_line(fnd_file.log,' Kanban card details with Interface Id : '|| p_card_int_tbl(i).interface_id ||
3212 ' failed due to following : '|| l_err_msg );
3213 ELSIF l_ret_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3214 p_card_int_tbl(i).process_status := FLM_KANBAN_MASSLOAD.ERROR;
3215 p_card_int_tbl(i).error_text := l_err_msg ;
3216 fnd_file.put_line(fnd_file.log,' Kanban card details with Interface Id : '|| p_card_int_tbl(i).interface_id ||
3217 ' failed due to following : '|| l_err_msg );
3218 ELSIF l_ret_status = FND_API.G_RET_STS_SUCCESS THEN
3219 p_card_int_tbl(i).process_status := FLM_KANBAN_MASSLOAD.COMPLETED;
3220 p_card_int_tbl(i).kanban_card_id := r_kanban_card_rec.kanban_card_id;
3221 mydebug('Kanban Card Id : '|| p_card_int_tbl(i).kanban_card_id);
3222 fnd_file.put_line(fnd_file.log,' Kanban card details with Interface Id : '|| p_card_int_tbl(i).interface_id ||
3223 ' was processed successfully. ');
3224 ELSIF l_ret_status = 'W' THEN
3225 p_card_int_tbl(i).process_status := FLM_KANBAN_MASSLOAD.WARNING;
3226 p_card_int_tbl(i).kanban_card_id := r_kanban_card_rec.kanban_card_id;
3227 fnd_file.put_line(fnd_file.log,' Kanban card details with Interface Id : '|| p_card_int_tbl(i).interface_id ||
3228 ' had following warnings : '|| l_err_msg );
3229 END IF;
3230 IF l_ret_status not in (FND_API.G_RET_STS_SUCCESS,'W') THEN
3231 l_count := l_count + 1;
3232 END IF;
3233 END LOOP;
3234 IF l_count > 0 THEN
3235 x_ret_status := FND_API.G_RET_STS_ERROR;
3236 ELSE
3237 x_ret_status := FND_API.G_RET_STS_SUCCESS;
3238 END IF;
3239 mydebug('END process_kc Procedure');
3240
3241 EXCEPTION
3242 WHEN OTHERS THEN
3243 x_ret_status := FND_API.G_RET_STS_ERROR;
3244 END process_kc;
3245
3246 --------------------------------------------
3247 -- Pull sequence UI to call this procedure
3248 --------------------------------------------
3249 PROCEDURE process_pull_sequence
3250 (p_pull_sequence_id IN OUT NOCOPY NUMBER
3251 ,p_generate_cards IN VARCHAR2 DEFAULT 'N' --PARAMETER is not required..need to remove
3252 ,p_last_update_date IN DATE DEFAULT NULL
3253 ,p_last_updated_by IN NUMBER DEFAULT NULL
3254 ,p_creation_date IN DATE DEFAULT NULL
3255 ,p_created_by IN NUMBER DEFAULT NULL
3256 ,p_last_update_login IN NUMBER DEFAULT NULL
3257 ,p_inventory_item_id IN NUMBER DEFAULT NULL
3258 ,p_concatenated_segments IN VARCHAR2 DEFAULT NULL
3259 ,p_organization_code IN VARCHAR2 DEFAULT NULL
3260 ,p_organization_id IN NUMBER DEFAULT NULL
3261 ,p_subinventory_name IN VARCHAR2 DEFAULT NULL
3262 ,p_source_type IN NUMBER DEFAULT NULL
3263 ,p_locator_id IN NUMBER DEFAULT NULL
3264 ,p_supplier_id IN NUMBER DEFAULT NULL
3265 ,p_supplier_name IN VARCHAR2 DEFAULT NULL
3266 ,p_supplier_site_id IN NUMBER DEFAULT NULL
3267 ,p_supplier_site_code IN VARCHAR2 DEFAULT NULL
3268 ,p_source_organization_id IN NUMBER DEFAULT NULL
3269 ,p_source_subinventory IN VARCHAR2 DEFAULT NULL
3270 ,p_source_locator_id IN NUMBER DEFAULT NULL
3271 ,p_wip_line_code IN VARCHAR2 DEFAULT NULL
3272 ,p_wip_line_id IN NUMBER DEFAULT NULL
3273 ,p_replenishment_lead_time IN NUMBER DEFAULT NULL
3274 ,p_calculate_kanban_flag IN NUMBER DEFAULT NULL
3275 ,p_kanban_size IN NUMBER DEFAULT NULL
3276 ,p_fixed_lot_multiplier IN NUMBER DEFAULT NULL
3277 ,p_safety_stock_days IN NUMBER DEFAULT NULL
3278 ,p_number_of_cards IN NUMBER DEFAULT NULL
3279 ,p_minimum_order_quantity IN NUMBER DEFAULT NULL
3280 ,p_aggregation_type IN NUMBER DEFAULT NULL
3281 ,p_allocation_percent IN NUMBER DEFAULT NULL
3282 ,p_release_kanban_flag IN NUMBER DEFAULT NULL
3283 ,p_attribute_category IN VARCHAR2 DEFAULT NULL
3284 ,p_attribute1 IN VARCHAR2 DEFAULT NULL
3285 ,p_attribute2 IN VARCHAR2 DEFAULT NULL
3286 ,p_attribute3 IN VARCHAR2 DEFAULT NULL
3287 ,p_attribute4 IN VARCHAR2 DEFAULT NULL
3288 ,p_attribute5 IN VARCHAR2 DEFAULT NULL
3289 ,p_attribute6 IN VARCHAR2 DEFAULT NULL
3290 ,p_attribute7 IN VARCHAR2 DEFAULT NULL
3291 ,p_attribute8 IN VARCHAR2 DEFAULT NULL
3292 ,p_attribute9 IN VARCHAR2 DEFAULT NULL
3293 ,p_attribute10 IN VARCHAR2 DEFAULT NULL
3294 ,p_attribute11 IN VARCHAR2 DEFAULT NULL
3295 ,p_attribute12 IN VARCHAR2 DEFAULT NULL
3296 ,p_attribute13 IN VARCHAR2 DEFAULT NULL
3297 ,p_attribute14 IN VARCHAR2 DEFAULT NULL
3298 ,p_attribute15 IN VARCHAR2 DEFAULT NULL
3299 ,p_auto_request IN VARCHAR2 DEFAULT NULL
3300 ,p_auto_allocate_flag IN VARCHAR2 DEFAULT NULL
3301 ,p_replenishment_type IN NUMBER DEFAULT NULL
3302 ,p_consolidation IN NUMBER DEFAULT NULL
3303 ,p_consolidation_group IN VARCHAR2 DEFAULT NULL
3304 ,p_future_card_size IN NUMBER DEFAULT NULL
3305 ,p_future_no_of_cards IN NUMBER DEFAULT NULL
3306 ,p_planning_effectivity IN DATE DEFAULT NULL
3307 ,p_avg_dependent_demand IN NUMBER DEFAULT NULL
3308 ,p_avg_independent_demand IN NUMBER DEFAULT NULL
3309 ,p_transaction_type IN NUMBER
3310 ,x_ret_status OUT NOCOPY VARCHAR2
3311 ,x_error_msg OUT NOCOPY VARCHAR2)
3312 IS
3313 r_pull_seq_rec flm_ekanban_pub.pull_sequence_rec_type;
3314 l_Pull_sequence_rec INV_Kanban_PVT.Pull_Sequence_Rec_Type;
3315 l_ret_status VARCHAR2(1);
3316 l_error_msg VARCHAR2(4000);
3317 BEGIN
3318
3319 mydebug('In process_pull_sequence procedure to validate and process pull sequences.');
3320
3321 FND_MSG_PUB.initialize;
3322
3323 mydebug('Assigning parameter values to record type.');
3324 -- Assign parameter valuses to record type
3325 r_pull_seq_rec.pull_sequence_id := p_pull_sequence_id;
3326 r_pull_seq_rec.generate_cards := p_generate_cards;
3327 r_pull_seq_rec.last_update_date := g_last_update_date;
3328 r_pull_seq_rec.last_updated_by := g_user_id;
3329 r_pull_seq_rec.creation_date := g_creation_date;
3330 r_pull_seq_rec.created_by := g_user_id;
3331 r_pull_seq_rec.last_update_login := g_user_login_id;
3332 r_pull_seq_rec.inventory_item_id := p_inventory_item_id;
3333 r_pull_seq_rec.concatenated_segments := p_concatenated_segments;
3334 r_pull_seq_rec.organization_code := p_organization_code;
3335 r_pull_seq_rec.organization_id := p_organization_id;
3336 r_pull_seq_rec.subinventory_name := p_subinventory_name;
3337 r_pull_seq_rec.source_type := p_source_type;
3338 r_pull_seq_rec.locator_id := p_locator_id;
3339 r_pull_seq_rec.supplier_id := p_supplier_id;
3340 r_pull_seq_rec.supplier_name := p_supplier_name;
3341 r_pull_seq_rec.supplier_site_id := p_supplier_site_id;
3342 r_pull_seq_rec.supplier_site_code := p_supplier_site_code;
3343 r_pull_seq_rec.source_organization_id := p_source_organization_id;
3344 r_pull_seq_rec.source_subinventory := p_source_subinventory;
3345 r_pull_seq_rec.source_locator_id := p_source_locator_id;
3346 r_pull_seq_rec.wip_line_code := p_wip_line_code;
3347 r_pull_seq_rec.wip_line_id := p_wip_line_id;
3348 r_pull_seq_rec.replenishment_lead_time := p_replenishment_lead_time;
3349 r_pull_seq_rec.calculate_kanban_flag := p_calculate_kanban_flag;
3350 r_pull_seq_rec.kanban_size := p_kanban_size;
3351 r_pull_seq_rec.fixed_lot_multiplier := p_fixed_lot_multiplier;
3352 r_pull_seq_rec.safety_stock_days := p_safety_stock_days;
3353 r_pull_seq_rec.number_of_cards := p_number_of_cards;
3354 r_pull_seq_rec.minimum_order_quantity := p_minimum_order_quantity;
3355 r_pull_seq_rec.aggregation_type := p_aggregation_type;
3356 r_pull_seq_rec.allocation_percent := p_allocation_percent;
3357 r_pull_seq_rec.release_kanban_flag := p_release_kanban_flag;
3358 r_pull_seq_rec.attribute_category := p_attribute_category;
3359 r_pull_seq_rec.attribute1 := p_attribute1;
3360 r_pull_seq_rec.attribute2 := p_attribute2;
3361 r_pull_seq_rec.attribute3 := p_attribute3;
3362 r_pull_seq_rec.attribute4 := p_attribute4;
3363 r_pull_seq_rec.attribute5 := p_attribute5;
3364 r_pull_seq_rec.attribute6 := p_attribute6;
3365 r_pull_seq_rec.attribute7 := p_attribute7;
3366 r_pull_seq_rec.attribute8 := p_attribute8;
3367 r_pull_seq_rec.attribute9 := p_attribute9;
3368 r_pull_seq_rec.attribute10 := p_attribute10;
3369 r_pull_seq_rec.attribute11 := p_attribute11;
3370 r_pull_seq_rec.attribute12 := p_attribute12;
3371 r_pull_seq_rec.attribute13 := p_attribute13;
3372 r_pull_seq_rec.attribute14 := p_attribute14;
3373 r_pull_seq_rec.attribute15 := p_attribute15;
3374 r_pull_seq_rec.auto_request := p_auto_request;
3375 r_pull_seq_rec.auto_allocate_flag := p_auto_allocate_flag;
3376 r_pull_seq_rec.replenishment_type := p_replenishment_type;
3377 r_pull_seq_rec.consolidation := p_consolidation;
3378 r_pull_seq_rec.consolidation_group := p_consolidation_group;
3379 r_pull_seq_rec.future_card_size := p_future_card_size;
3380 r_pull_seq_rec.future_no_of_cards := p_future_no_of_cards;
3381 r_pull_seq_rec.planning_effectivity := p_planning_effectivity;
3382 r_pull_seq_rec.avg_dependent_demand := p_avg_dependent_demand;
3383 r_pull_seq_rec.avg_independent_demand := p_avg_independent_demand;
3384
3385 mydebug('Calling validate_pull_sequence procedure.');
3386
3387 -- call validate_pull_sequence procedure to validate pull sequence record
3388 validate_pull_sequence(p_pull_seq_rec => r_pull_seq_rec
3389 ,p_transaction_type => p_transaction_type
3390 ,x_ret_status => l_ret_status
3391 ,x_error_msg => l_error_msg);
3392 mydebug('l_ret_status='||l_ret_status);
3393
3394 -- Insert/Update/Delete pull sequences
3395 if l_ret_status = FND_API.G_RET_STS_SUCCESS then
3396
3397 mydebug('Assigning validated record values.');
3398 --Assign the validated record
3399 l_pull_sequence_rec.pull_sequence_id := r_pull_seq_rec.pull_sequence_id;
3400 l_pull_sequence_rec.inventory_item_id := r_pull_seq_rec.inventory_item_id;
3401 l_pull_sequence_rec.organization_id := r_pull_seq_rec.organization_id;
3402 l_pull_sequence_rec.subinventory_name := r_pull_seq_rec.subinventory_name;
3403 l_pull_sequence_rec.Kanban_plan_id := FLM_KANBAN_MASSLOAD.G_PRODUCTION_KANBAN;
3404 l_pull_sequence_rec.source_type := r_pull_seq_rec.source_type;
3405 l_pull_sequence_rec.last_update_date := r_pull_seq_rec.last_update_date;
3406 l_pull_sequence_rec.last_updated_by := r_pull_seq_rec.last_updated_by;
3407 l_pull_sequence_rec.creation_date := r_pull_seq_rec.creation_date;
3408 l_pull_sequence_rec.created_by := r_pull_seq_rec.created_by;
3409 l_pull_sequence_rec.locator_id := r_pull_seq_rec.locator_id;
3410 l_pull_sequence_rec.supplier_id := r_pull_seq_rec.supplier_id;
3411 l_pull_sequence_rec.supplier_site_id := r_pull_seq_rec.supplier_site_id;
3412 l_pull_sequence_rec.source_organization_id := r_pull_seq_rec.source_organization_id;
3413 l_pull_sequence_rec.source_subinventory := r_pull_seq_rec.source_subinventory;
3414 l_pull_sequence_rec.source_locator_id := r_pull_seq_rec.source_locator_id;
3415 l_pull_sequence_rec.wip_line_id := r_pull_seq_rec.wip_line_id;
3416 l_pull_sequence_rec.replenishment_lead_time := r_pull_seq_rec.replenishment_lead_time;
3417 l_pull_sequence_rec.calculate_kanban_flag := r_pull_seq_rec.calculate_kanban_flag;
3418 l_pull_sequence_rec.kanban_size := r_pull_seq_rec.kanban_size;
3419 l_pull_sequence_rec.fixed_lot_multiplier := r_pull_seq_rec.fixed_lot_multiplier;
3420 l_pull_sequence_rec.safety_stock_days := r_pull_seq_rec.safety_stock_days;
3421 l_pull_sequence_rec.number_of_cards := r_pull_seq_rec.number_of_cards;
3422 l_pull_sequence_rec.minimum_order_quantity := r_pull_seq_rec.minimum_order_quantity;
3423 l_pull_sequence_rec.aggregation_type := r_pull_seq_rec.aggregation_type;
3424 l_pull_sequence_rec.allocation_percent := r_pull_seq_rec.allocation_percent;
3425 l_pull_sequence_rec.last_update_login := r_pull_seq_rec.last_update_login;
3426 l_pull_sequence_rec.updated_flag := null;
3427 l_pull_sequence_rec.attribute_category := r_pull_seq_rec.attribute_category;
3428 l_pull_sequence_rec.attribute1 := r_pull_seq_rec.attribute1;
3429 l_pull_sequence_rec.attribute2 := r_pull_seq_rec.attribute2;
3430 l_pull_sequence_rec.attribute3 := r_pull_seq_rec.attribute3;
3431 l_pull_sequence_rec.attribute4 := r_pull_seq_rec.attribute4;
3432 l_pull_sequence_rec.attribute5 := r_pull_seq_rec.attribute5;
3433 l_pull_sequence_rec.attribute6 := r_pull_seq_rec.attribute6;
3434 l_pull_sequence_rec.attribute7 := r_pull_seq_rec.attribute7;
3435 l_pull_sequence_rec.attribute8 := r_pull_seq_rec.attribute8;
3436 l_pull_sequence_rec.attribute9 := r_pull_seq_rec.attribute9;
3437 l_pull_sequence_rec.attribute10 := r_pull_seq_rec.attribute10;
3438 l_pull_sequence_rec.attribute11 := r_pull_seq_rec.attribute11;
3439 l_pull_sequence_rec.attribute12 := r_pull_seq_rec.attribute12;
3440 l_pull_sequence_rec.attribute13 := r_pull_seq_rec.attribute13;
3441 l_pull_sequence_rec.attribute14 := r_pull_seq_rec.attribute14;
3442 l_pull_sequence_rec.attribute15 := r_pull_seq_rec.attribute15;
3443 l_pull_sequence_rec.request_id := null;
3444 l_pull_sequence_rec.program_application_id := null;
3445 l_pull_sequence_rec.program_id := null;
3446 l_pull_sequence_rec.program_update_date := null;
3447 l_pull_sequence_rec.release_kanban_flag := r_pull_seq_rec.release_kanban_flag;
3448 l_pull_sequence_rec.point_of_use_x := null;
3449 l_pull_sequence_rec.point_of_use_y := null;
3450 l_pull_sequence_rec.point_of_supply_x := null;
3451 l_pull_sequence_rec.point_of_supply_y := null;
3452 l_pull_sequence_rec.planning_update_status := null;
3453 l_pull_sequence_rec.auto_request := r_pull_seq_rec.auto_request;
3454 l_pull_sequence_rec.kanban_card_type := null;
3455 l_pull_sequence_rec.auto_allocate_flag := r_pull_seq_rec.auto_allocate_flag;
3456 l_pull_sequence_rec.replenishment_type := r_pull_seq_rec.replenishment_type;
3457 l_pull_sequence_rec.future_card_size := r_pull_seq_rec.future_card_size;
3458 l_pull_sequence_rec.future_no_of_cards := r_pull_seq_rec.future_no_of_cards;
3459 l_pull_sequence_rec.planning_effectivity := r_pull_seq_rec.planning_effectivity;
3460 l_pull_sequence_rec.consolidation := r_pull_seq_rec.consolidation;
3461 l_pull_sequence_rec.consolidation_group := r_pull_seq_rec.consolidation_group;
3462 l_pull_sequence_rec.avg_dependent_demand := r_pull_seq_rec.avg_dependent_demand;
3463 l_pull_sequence_rec.avg_independent_demand := r_pull_seq_rec.avg_independent_demand;
3464
3465 IF FND_GLOBAL.CONC_REQUEST_ID > 0 THEN
3466 l_pull_sequence_rec.request_id := FND_GLOBAL.CONC_REQUEST_ID;
3467 l_pull_sequence_rec.program_application_id := FND_GLOBAL.PROG_APPL_ID;
3468 l_pull_sequence_rec.program_id := FND_GLOBAL.CONC_PROGRAM_ID;
3469 l_pull_sequence_rec.program_update_date := SYSDATE;
3470 END IF;
3471
3472 if p_transaction_type = FLM_KANBAN_MASSLOAD.KANBAN_ADD then
3473
3474 -- insert pull sequence
3475 --INV_PullSequence_PKG.Insert_Row(p_pull_sequence_rec => l_pull_sequence_rec);
3476 mydebug('Calling INV_PullSequence_PKG.Insert_Row procedure.');
3477
3478 INV_PullSequence_PKG.Insert_Row(
3479 x_return_status =>l_ret_status,
3480 p_pull_sequence_id =>l_pull_sequence_rec.pull_sequence_id,
3481 p_Inventory_item_id =>l_pull_sequence_rec.Inventory_item_id,
3482 p_Organization_id =>l_pull_sequence_rec.Organization_id,
3483 p_Subinventory_name =>l_pull_sequence_rec.Subinventory_name,
3484 p_Kanban_Plan_id =>l_pull_sequence_rec.Kanban_Plan_id,
3485 p_Source_type =>l_pull_sequence_rec.Source_type,
3486 p_Last_Update_Date =>l_pull_sequence_rec.Last_Update_Date,
3487 p_Last_Updated_By =>l_pull_sequence_rec.Last_Updated_By,
3488 p_Creation_Date =>l_pull_sequence_rec.Creation_Date,
3489 p_Created_By =>l_pull_sequence_rec.Created_By,
3490 p_Last_Update_Login =>l_pull_sequence_rec.Last_Update_Login,
3491 p_Locator_id =>l_pull_sequence_rec.Locator_id,
3492 p_Supplier_id =>l_pull_sequence_rec.Supplier_id,
3493 p_Supplier_site_id =>l_pull_sequence_rec.Supplier_site_id,
3494 p_Source_Organization_id =>l_pull_sequence_rec.Source_Organization_id,
3495 p_Source_Subinventory =>l_pull_sequence_rec.Source_Subinventory,
3496 p_Source_Locator_id =>l_pull_sequence_rec.Source_Locator_id,
3497 p_Wip_Line_id =>l_Pull_Sequence_Rec.Wip_Line_id,
3498 p_Release_Kanban_Flag =>l_pull_sequence_rec.Release_Kanban_Flag,
3499 p_Calculate_Kanban_Flag =>l_pull_sequence_rec.Calculate_Kanban_Flag,
3500 p_Kanban_size =>l_pull_sequence_rec.Kanban_size,
3501 p_Number_of_cards =>l_pull_sequence_rec.Number_of_cards,
3502 p_Minimum_order_quantity =>l_pull_sequence_rec.Minimum_order_quantity,
3503 p_Aggregation_type =>l_pull_sequence_rec.Aggregation_type,
3504 p_Allocation_Percent =>l_pull_sequence_rec.Allocation_Percent,
3505 p_Replenishment_lead_time =>l_pull_sequence_rec.Replenishment_lead_time,
3506 p_Fixed_Lot_multiplier =>l_pull_sequence_rec.Fixed_Lot_multiplier,
3507 p_Safety_Stock_Days =>l_pull_sequence_rec.Safety_Stock_Days,
3508 p_Updated_Flag =>l_pull_sequence_rec.Updated_Flag,
3509 p_Attribute_Category =>l_pull_sequence_rec.Attribute_Category,
3510 p_Attribute1 =>l_pull_sequence_rec.Attribute1,
3511 p_Attribute2 =>l_pull_sequence_rec.Attribute2,
3512 p_Attribute3 =>l_pull_sequence_rec.Attribute3,
3513 p_Attribute4 =>l_pull_sequence_rec.Attribute4,
3514 p_Attribute5 =>l_pull_sequence_rec.Attribute5,
3515 p_Attribute6 =>l_pull_sequence_rec.Attribute6,
3516 p_Attribute7 =>l_pull_sequence_rec.Attribute7,
3517 p_Attribute8 =>l_pull_sequence_rec.Attribute8,
3518 p_Attribute9 =>l_pull_sequence_rec.Attribute9,
3519 p_Attribute10 =>l_pull_sequence_rec.Attribute10,
3520 p_Attribute11 =>l_pull_sequence_rec.Attribute11,
3521 p_Attribute12 =>l_pull_sequence_rec.Attribute12,
3522 p_Attribute13 =>l_pull_sequence_rec.Attribute13,
3523 p_Attribute14 =>l_pull_sequence_rec.Attribute14,
3524 p_Attribute15 =>l_pull_sequence_rec.Attribute15,
3525 p_Request_Id =>l_pull_sequence_rec.Request_Id,
3526 p_Program_application_Id =>l_pull_sequence_rec.Program_application_Id,
3527 p_Program_Id =>l_pull_sequence_rec.Program_Id,
3528 p_Program_Update_date =>l_pull_sequence_rec.Program_Update_date,
3529 p_point_of_use_x =>l_pull_sequence_rec.point_of_use_x,
3530 p_point_of_use_y =>l_pull_sequence_rec.point_of_use_y,
3531 p_point_of_supply_x =>l_pull_sequence_rec.point_of_supply_x,
3532 p_point_of_supply_y =>l_pull_sequence_rec.point_of_supply_y,
3533 p_planning_update_status =>l_pull_sequence_rec.planning_update_status,
3534 p_auto_request =>l_pull_sequence_rec.auto_request,
3535 p_Auto_Allocate_Flag =>l_pull_sequence_rec.Auto_Allocate_Flag,
3536 p_replenishment_type =>l_pull_sequence_rec.replenishment_type,
3537 p_future_card_size =>l_pull_sequence_rec.future_card_size,
3538 p_future_no_of_cards =>l_pull_sequence_rec.future_no_of_cards,
3539 p_planning_effectivity =>l_pull_sequence_rec.planning_effectivity,
3540 p_consolidation =>l_pull_sequence_rec.consolidation,
3541 p_consolidation_group =>l_pull_sequence_rec.consolidation_group,
3542 p_avg_dependent_demand =>l_pull_sequence_rec.avg_dependent_demand,
3543 p_avg_independent_demand =>l_pull_sequence_rec.avg_independent_demand);
3544
3545 mydebug('l_ret_status='||l_ret_status);
3546
3547 IF l_ret_status = FND_API.G_RET_STS_ERROR THEN
3548 l_error_msg := 'Error in INV_PullSequence_PKG.Insert_Row';
3549 ELSIF l_ret_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3550 l_error_msg := FND_MSG_PUB.Get(FND_MSG_PUB.G_LAST, FND_API.G_FALSE);
3551 END IF;
3552
3553 elsif p_transaction_type = FLM_KANBAN_MASSLOAD.KANBAN_CHANGE then
3554 -- update pull sequence
3555 --INV_PullSequence_PKG.Update_Row(p_pull_sequence_rec => l_pull_sequence_rec);
3556 mydebug('Calling INV_PullSequence_PKG.Update_Row procedure.');
3557
3558 INV_PullSequence_PKG.Update_Row(
3559 x_return_status =>l_ret_status,
3560 p_pull_sequence_id =>l_pull_sequence_rec.pull_sequence_id,
3561 p_Inventory_item_id =>l_pull_sequence_rec.Inventory_item_id,
3562 p_Organization_id =>l_pull_sequence_rec.Organization_id,
3563 p_Subinventory_name =>l_pull_sequence_rec.Subinventory_name,
3564 p_Kanban_Plan_id =>l_pull_sequence_rec.Kanban_Plan_id,
3565 p_Source_type =>l_pull_sequence_rec.Source_type,
3566 p_Last_Update_Date =>l_pull_sequence_rec.Last_Update_Date,
3567 p_Last_Updated_By =>l_pull_sequence_rec.Last_Updated_By,
3568 p_Creation_Date =>l_pull_sequence_rec.Creation_Date,
3569 p_Created_By =>l_pull_sequence_rec.Created_By,
3570 p_Last_Update_Login =>l_pull_sequence_rec.Last_Update_Login,
3571 p_Locator_id =>l_pull_sequence_rec.Locator_id,
3572 p_Supplier_id =>l_pull_sequence_rec.Supplier_id,
3573 p_Supplier_site_id =>l_pull_sequence_rec.Supplier_site_id,
3574 p_Source_Organization_id =>l_pull_sequence_rec.Source_Organization_id,
3575 p_Source_Subinventory =>l_pull_sequence_rec.Source_Subinventory,
3576 p_Source_Locator_id =>l_pull_sequence_rec.Source_Locator_id,
3577 p_Wip_Line_id =>l_Pull_Sequence_Rec.Wip_Line_id,
3578 p_Release_Kanban_Flag =>l_pull_sequence_rec.Release_Kanban_Flag,
3579 p_Calculate_Kanban_Flag =>l_pull_sequence_rec.Calculate_Kanban_Flag,
3580 p_Kanban_size =>l_pull_sequence_rec.Kanban_size,
3581 p_Number_of_cards =>l_pull_sequence_rec.Number_of_cards,
3582 p_Minimum_order_quantity =>l_pull_sequence_rec.Minimum_order_quantity,
3583 p_Aggregation_type =>l_pull_sequence_rec.Aggregation_type,
3584 p_Allocation_Percent =>l_pull_sequence_rec.Allocation_Percent,
3585 p_Replenishment_lead_time =>l_pull_sequence_rec.Replenishment_lead_time,
3586 p_Fixed_Lot_multiplier =>l_pull_sequence_rec.Fixed_Lot_multiplier,
3587 p_Safety_Stock_Days =>l_pull_sequence_rec.Safety_Stock_Days,
3588 p_Updated_Flag =>l_pull_sequence_rec.Updated_Flag,
3589 p_Attribute_Category =>l_pull_sequence_rec.Attribute_Category,
3590 p_Attribute1 =>l_pull_sequence_rec.Attribute1,
3591 p_Attribute2 =>l_pull_sequence_rec.Attribute2,
3592 p_Attribute3 =>l_pull_sequence_rec.Attribute3,
3593 p_Attribute4 =>l_pull_sequence_rec.Attribute4,
3594 p_Attribute5 =>l_pull_sequence_rec.Attribute5,
3595 p_Attribute6 =>l_pull_sequence_rec.Attribute6,
3596 p_Attribute7 =>l_pull_sequence_rec.Attribute7,
3597 p_Attribute8 =>l_pull_sequence_rec.Attribute8,
3598 p_Attribute9 =>l_pull_sequence_rec.Attribute9,
3599 p_Attribute10 =>l_pull_sequence_rec.Attribute10,
3600 p_Attribute11 =>l_pull_sequence_rec.Attribute11,
3601 p_Attribute12 =>l_pull_sequence_rec.Attribute12,
3602 p_Attribute13 =>l_pull_sequence_rec.Attribute13,
3603 p_Attribute14 =>l_pull_sequence_rec.Attribute14,
3604 p_Attribute15 =>l_pull_sequence_rec.Attribute15,
3605 p_point_of_use_x =>l_pull_sequence_rec.point_of_use_x,
3606 p_point_of_use_y =>l_pull_sequence_rec.point_of_use_y,
3607 p_point_of_supply_x =>l_pull_sequence_rec.point_of_supply_x,
3608 p_point_of_supply_y =>l_pull_sequence_rec.point_of_supply_y,
3609 p_planning_update_status =>l_pull_sequence_rec.planning_update_status,
3610 p_auto_request =>l_pull_sequence_rec.auto_request,
3611 p_Auto_Allocate_Flag =>l_pull_sequence_rec.Auto_Allocate_Flag,
3612 p_replenishment_type =>l_pull_sequence_rec.replenishment_type,
3613 p_future_card_size =>l_pull_sequence_rec.future_card_size,
3614 p_future_no_of_cards =>l_pull_sequence_rec.future_no_of_cards,
3615 p_planning_effectivity =>l_pull_sequence_rec.planning_effectivity,
3616 p_consolidation =>l_pull_sequence_rec.consolidation,
3617 p_consolidation_group =>l_pull_sequence_rec.consolidation_group,
3618 p_avg_dependent_demand =>l_pull_sequence_rec.avg_dependent_demand,
3619 p_avg_independent_demand =>l_pull_sequence_rec.avg_independent_demand);
3620
3621 mydebug('l_ret_status='||l_ret_status);
3622
3623 IF l_ret_status = FND_API.G_RET_STS_SUCCESS THEN
3624 -- Set replacement flag on cards when pull seq is updated.
3625 UPDATE MTL_KANBAN_CARDS
3626 SET replacement_flag = 1
3627 WHERE source_type <> l_pull_sequence_rec.source_type
3628 AND source_subinventory <> l_pull_sequence_rec.source_subinventory
3629 AND source_locator_id <> l_pull_sequence_rec.source_locator_id
3630 AND wip_line_id <> l_pull_sequence_rec.wip_line_id
3631 AND pull_sequence_id = l_pull_sequence_rec.pull_sequence_id;
3632
3633 ELSIF l_ret_status = FND_API.G_RET_STS_ERROR THEN
3634 l_error_msg := 'Error in INV_PullSequence_PKG.Update_Row';
3635 ELSE
3636 l_error_msg := FND_MSG_PUB.Get(FND_MSG_PUB.G_LAST, FND_API.G_FALSE);
3637 END IF;
3638
3639 elsif p_transaction_type = FLM_KANBAN_MASSLOAD.KANBAN_DELETE then
3640 -- delete pull sequence
3641 mydebug('Calling INV_PullSequence_PKG.Delete_Row procedure.');
3642
3643 INV_PullSequence_PKG.Delete_Row(x_return_status => l_ret_status
3644 ,p_Pull_sequence_Id => l_pull_sequence_rec.pull_sequence_id);
3645
3646 mydebug('l_ret_status='||l_ret_status);
3647
3648 IF l_ret_status = FND_API.G_RET_STS_ERROR THEN
3649 l_error_msg := fnd_message.get;
3650 ELSIF l_ret_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3651 l_error_msg := FND_MSG_PUB.Get(FND_MSG_PUB.G_LAST, FND_API.G_FALSE);
3652 END IF;
3653
3654 end if;
3655
3656 -- Return the pull sequence value
3657 p_pull_sequence_id := l_pull_sequence_rec.pull_sequence_id;
3658
3659 end if;
3660
3661 x_ret_status := l_ret_status;
3662 x_error_msg := l_error_msg;
3663
3664 mydebug('Exiting process_pull_sequence procedure');
3665
3666 EXCEPTION
3667
3668 WHEN OTHERS THEN
3669
3670 x_ret_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3671 x_error_msg := 'Unexpected error in process_pull_sequence procedure:'||SQLERRM;
3672 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3673 THEN
3674 FND_MSG_PUB.Add_Exc_Msg
3675 ( G_PKG_NAME
3676 , 'process_pull_sequence'
3677 );
3678 END IF;
3679 END process_pull_sequence;
3680
3681 ------------------------------
3682 -- Validate multiple suppliers
3683 ------------------------------
3684 PROCEDURE validate_suppliers(p_supplier_rec IN OUT NOCOPY flm_ekanban_pub.kanban_supplier_rec_type
3685 ,p_organization_id IN NUMBER
3686 ,x_ret_status OUT NOCOPY VARCHAR2
3687 ,x_error_msg OUT NOCOPY VARCHAR2)
3688 IS
3689 l_ret_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
3690 l_transaction_type NUMBER;
3691 l_pullseq_id NUMBER;
3692 l_supplier_count Number; --Bug 12533212
3693 l_sourcing_percentage NUMBER; --Bug 12419603
3694 BEGIN
3695
3696 mydebug('In validate_suppliers procedure');
3697
3698 -- Transaction type - supplier to insert/update/delete
3699 if p_supplier_rec.transaction_type is null then
3700 FND_MESSAGE.SET_NAME('FLM','FLM_ATTRIBUTE_REQUIRED');
3701 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Supplier Transaction Type');
3702 x_error_msg := x_error_msg||FND_CONST.NEWLINE||FND_MESSAGE.GET;
3703 elsif p_supplier_rec.transaction_type NOT IN (FLM_KANBAN_MASSLOAD.kanban_add,
3704 FLM_KANBAN_MASSLOAD.kanban_change,
3705 FLM_KANBAN_MASSLOAD.kanban_delete)
3706 then
3707 FND_MESSAGE.SET_NAME('FLM','FLM_ATTRIBUTE_INVALID');
3708 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Supplier Transaction Type');
3709 x_error_msg := x_error_msg||FND_CONST.NEWLINE||FND_MESSAGE.GET;
3710 else
3711 l_transaction_type := p_supplier_rec.transaction_type;
3712 end if;
3713
3714 --Validate pull sequence id
3715
3716 if p_supplier_rec.pull_sequence_id is null then
3717 FND_MESSAGE.SET_NAME('FLM','FLM_ATTRIBUTE_REQUIRED');
3718 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Pull Sequence Id for Supplier');
3719 x_error_msg := x_error_msg||FND_CONST.NEWLINE||FND_MESSAGE.GET;
3720 else
3721 if is_valid_pull_sequence_id(p_supplier_rec.pull_sequence_id) then
3722 l_pullseq_id := p_supplier_rec.pull_sequence_id;
3723 else
3724 FND_MESSAGE.SET_NAME('FLM','FLM_ATTRIBUTE_INVALID');
3725 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Pull Sequence Id for Supplier');
3726 x_error_msg := x_error_msg||FND_CONST.NEWLINE||FND_MESSAGE.GET;
3727 end if;
3728 end if;
3729
3730 --Validate supplier_id
3731 if p_supplier_rec.supplier_id is null then
3732 if p_supplier_rec.supplier_name is null then
3733 FND_MESSAGE.SET_NAME('FLM','FLM_ATTRIBUTE_REQUIRED');
3734 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Supplier');
3735 x_error_msg := x_error_msg||FND_CONST.NEWLINE||FND_MESSAGE.GET;
3736 else
3737 p_supplier_rec.supplier_id := default_supplier_id(p_supplier_name => p_supplier_rec.supplier_name);
3738 if p_supplier_rec.supplier_id is null then
3739 FND_MESSAGE.SET_NAME('FLM','FLM_ATTRIBUTE_INVALID');
3740 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Supplier Name');
3741 x_error_msg := x_error_msg||FND_CONST.NEWLINE||FND_MESSAGE.GET;
3742 end if;
3743 end if;
3744 else -- supplier id is not null
3745 if is_supplier_id_invalid(p_supplier_rec.supplier_id) then
3746 FND_MESSAGE.SET_NAME('FLM','FLM_ATTRIBUTE_INVALID');
3747 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Supplier');
3748 x_error_msg := x_error_msg||FND_CONST.NEWLINE||FND_MESSAGE.GET;
3749 end if;
3750 end if;
3751
3752 if l_transaction_type IN (FLM_KANBAN_MASSLOAD.KANBAN_ADD
3753 ,FLM_KANBAN_MASSLOAD.KANBAN_CHANGE) then
3754 --Validate supplier_site_id
3755 if p_supplier_rec.supplier_site_id is null then
3756 if p_supplier_rec.supplier_site_code is not null then
3757 p_supplier_rec.supplier_site_id :=
3758 default_supplier_site_id(p_supplier_id => p_supplier_rec.supplier_id
3759 ,p_supplier_site_code => p_supplier_rec.supplier_site_code
3760 ,p_org_id => p_organization_id);
3761 if p_supplier_rec.supplier_site_id is null then
3762 FND_MESSAGE.SET_NAME('FLM','FLM_ATTRIBUTE_INVALID');
3763 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Supplier Code');
3764 x_error_msg := x_error_msg||FND_CONST.NEWLINE||FND_MESSAGE.GET;
3765 end if;
3766 end if;
3767 else -- supplier site id is not null
3768 if is_supplier_site_id_invalid(p_supplier_id => p_supplier_rec.supplier_id
3769 ,p_supplier_site_id => p_supplier_rec.supplier_site_id
3770 ,p_org_id => p_organization_id)
3771 then
3772 FND_MESSAGE.SET_NAME('FLM','FLM_ATTRIBUTE_INVALID');
3773 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Supplier Site');
3774 x_error_msg := x_error_msg||FND_CONST.NEWLINE||FND_MESSAGE.GET;
3775 end if;
3776 end if;
3777
3778 --Unique constaint for pull_sequence_id and supplier_id combination; Added for Bug 12533212
3779 if l_transaction_type IN (FLM_KANBAN_MASSLOAD.KANBAN_ADD) then
3780 select count(*)
3781 into l_supplier_count
3782 from mtl_pull_seq_suppliers
3783 where pull_sequence_id = p_supplier_rec.pull_sequence_id
3784 and supplier_id = p_supplier_rec.supplier_id;
3785
3786 if(l_supplier_count > 0) then
3787 mydebug('Duplicate supplier is trying to inserted for current pull sequence');
3788 FND_MESSAGE.SET_NAME('FLM','FLM_ATTRIBUTE_INVALID');
3789 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Supplier');
3790 x_error_msg := x_error_msg||FND_CONST.NEWLINE||FND_MESSAGE.GET;
3791 end if;
3792 end if;
3793
3794 if l_transaction_type = FLM_KANBAN_MASSLOAD.KANBAN_CHANGE then
3795 begin
3796 --Fix bug 12419603, should only check mtl_pull_seq_suppliers based on
3797 --supplier_id during update of pull seq since supplier_id is the primary key
3798 --of mtl_pull_seq_suppliers table
3799 select 1 into l_supplier_count
3800 from mtl_pull_seq_suppliers
3801 where pull_sequence_id = p_supplier_rec.pull_sequence_id
3802 and supplier_id = p_supplier_rec.supplier_id;
3803 --and nvl(supplier_site_id,-1) = nvl(p_supplier_rec.supplier_site_id,-1);
3804 exception
3805 when others then
3806 FND_MESSAGE.SET_NAME('FLM','FLM_ATTRIBUTE_INVALID');
3807 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Supplier/Supplier Site');
3808 x_error_msg := x_error_msg||FND_CONST.NEWLINE||FND_MESSAGE.GET;
3809 end;
3810 end if;
3811
3812 --Sourcing Percentage
3813 IF p_supplier_rec.sourcing_percentage IS NULL THEN
3814 -- During update null means retain old value.
3815 if l_transaction_type = FLM_KANBAN_MASSLOAD.KANBAN_ADD then
3816 FND_MESSAGE.SET_NAME('FLM','FLM_ATTRIBUTE_REQUIRED');
3817 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Sourcing Percentage');
3818 x_error_msg := x_error_msg||FND_CONST.NEWLINE||FND_MESSAGE.GET;
3819 end if;
3820 -- Fix bug 12419603, derive sourcing_percentage if it's not provided during update
3821 if l_transaction_type = FLM_KANBAN_MASSLOAD.KANBAN_CHANGE then
3822 select sourcing_percentage into l_sourcing_percentage
3823 from mtl_pull_seq_suppliers
3824 where pull_sequence_id = p_supplier_rec.pull_sequence_id
3825 and supplier_id = p_supplier_rec.supplier_id;
3826 p_supplier_rec.sourcing_percentage := l_sourcing_percentage;
3827 end if;
3828 ELSIF (p_supplier_rec.sourcing_percentage <=0 or p_supplier_rec.sourcing_percentage >100) THEN
3829 FND_MESSAGE.SET_NAME('FLM','FLM_INVALID_PERCENT_VALUE');
3830 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Supplier Sourcing Percentage');
3831 x_error_msg := x_error_msg||FND_CONST.NEWLINE||FND_MESSAGE.GET;
3832 END IF;
3833
3834
3835 end if; -- l_transaction_type IN add or change
3836
3837 if x_error_msg is NOT NULL THEN
3838 x_ret_status := FND_API.G_RET_STS_ERROR;
3839 else
3840 x_ret_status := l_ret_status;
3841 end if;
3842 mydebug('Exiting validate_suppliers procedure');
3843 EXCEPTION
3844 WHEN OTHERS THEN
3845 x_ret_status := FND_API.G_RET_STS_ERROR;
3846 x_error_msg := 'Error in validate_suppliers procedure:' ||FND_CONST.NEWLINE||SQLERRM;
3847 END validate_suppliers;
3848
3849 --------------------------------------------
3850 -- procedure for pull sequence public API
3851 --------------------------------------------
3852 PROCEDURE process_pull_sequence(p_pull_sequence_rec IN OUT NOCOPY flm_ekanban_pub.pull_sequence_rec_type
3853 ,p_supplier_tbl IN OUT NOCOPY flm_ekanban_pub.kanban_supplier_tbl_type
3854 ,p_transaction_type IN NUMBER
3855 ,p_commit_flag IN VARCHAR2 DEFAULT 'N'
3856 ,x_ret_status OUT NOCOPY VARCHAR2
3857 ,x_error_msg OUT NOCOPY VARCHAR2)
3858 IS
3859 l_ret_code NUMBER;
3860 l_ret_status VARCHAR2(1);
3861 l_error_msg VARCHAR2(4000);
3862 l_last_update_date DATE;
3863 l_last_updated_by NUMBER;
3864 l_creation_date DATE;
3865 l_created_by NUMBER;
3866 l_last_update_login NUMBER;
3867 l_supplier_count NUMBER;
3868 l_generate_cards VARCHAR2(1);
3869 l_organization_id NUMBER;
3870 l_source_type NUMBER; --Fix bug 12402610
3871 BEGIN
3872 mydebug('In process_pull_sequence Public API procedure');
3873 SAVEPOINT A;
3874
3875 --WHO columns
3876 l_last_update_date := g_last_update_date;
3877 l_last_updated_by := g_user_id;
3878 l_creation_date := g_creation_date;
3879 l_created_by := g_user_id;
3880 l_last_update_login := g_user_login_id;
3881
3882 -- Default generate cards to N if null
3883 l_generate_cards := NVL(p_pull_sequence_rec.generate_cards,'N');
3884
3885 l_supplier_count := p_supplier_tbl.count;
3886
3887 mydebug('Calling process_pull_sequence procedure.');
3888 -- call process_pull_sequence to validate and process
3889 process_pull_sequence
3890 (p_pull_sequence_id => p_pull_sequence_rec.pull_sequence_id
3891 ,p_generate_cards => p_pull_sequence_rec.generate_cards
3892 ,p_last_update_date => p_pull_sequence_rec.last_update_date
3893 ,p_last_updated_by => p_pull_sequence_rec.last_updated_by
3894 ,p_creation_date => p_pull_sequence_rec.creation_date
3895 ,p_created_by => p_pull_sequence_rec.created_by
3896 ,p_last_update_login => p_pull_sequence_rec.last_update_login
3897 ,p_inventory_item_id => p_pull_sequence_rec.inventory_item_id
3898 ,p_concatenated_segments => p_pull_sequence_rec.concatenated_segments
3899 ,p_organization_code => p_pull_sequence_rec.organization_code
3900 ,p_organization_id => p_pull_sequence_rec.organization_id
3901 ,p_subinventory_name => p_pull_sequence_rec.subinventory_name
3902 ,p_source_type => p_pull_sequence_rec.source_type
3903 ,p_locator_id => p_pull_sequence_rec.locator_id
3904 ,p_supplier_id => p_pull_sequence_rec.supplier_id
3905 ,p_supplier_name => p_pull_sequence_rec.supplier_name
3906 ,p_supplier_site_id => p_pull_sequence_rec.supplier_site_id
3907 ,p_supplier_site_code => p_pull_sequence_rec.supplier_site_code
3908 ,p_source_organization_id => p_pull_sequence_rec.source_organization_id
3909 ,p_source_subinventory => p_pull_sequence_rec.source_subinventory
3910 ,p_source_locator_id => p_pull_sequence_rec.source_locator_id
3911 ,p_wip_line_code => p_pull_sequence_rec.wip_line_code
3912 ,p_wip_line_id => p_pull_sequence_rec.wip_line_id
3913 ,p_replenishment_lead_time => p_pull_sequence_rec.replenishment_lead_time
3914 ,p_calculate_kanban_flag => p_pull_sequence_rec.calculate_kanban_flag
3915 ,p_kanban_size => p_pull_sequence_rec.kanban_size
3916 ,p_fixed_lot_multiplier => p_pull_sequence_rec.fixed_lot_multiplier
3917 ,p_safety_stock_days => p_pull_sequence_rec.safety_stock_days
3918 ,p_number_of_cards => p_pull_sequence_rec.number_of_cards
3919 ,p_minimum_order_quantity => p_pull_sequence_rec.minimum_order_quantity
3920 ,p_aggregation_type => p_pull_sequence_rec.aggregation_type
3921 ,p_allocation_percent => p_pull_sequence_rec.allocation_percent
3922 ,p_release_kanban_flag => p_pull_sequence_rec.release_kanban_flag
3923 ,p_attribute_category => p_pull_sequence_rec.attribute_category
3924 ,p_attribute1 => p_pull_sequence_rec.attribute1
3925 ,p_attribute2 => p_pull_sequence_rec.attribute2
3926 ,p_attribute3 => p_pull_sequence_rec.attribute3
3927 ,p_attribute4 => p_pull_sequence_rec.attribute4
3928 ,p_attribute5 => p_pull_sequence_rec.attribute5
3929 ,p_attribute6 => p_pull_sequence_rec.attribute6
3930 ,p_attribute7 => p_pull_sequence_rec.attribute7
3931 ,p_attribute8 => p_pull_sequence_rec.attribute8
3932 ,p_attribute9 => p_pull_sequence_rec.attribute9
3933 ,p_attribute10 => p_pull_sequence_rec.attribute10
3934 ,p_attribute11 => p_pull_sequence_rec.attribute11
3935 ,p_attribute12 => p_pull_sequence_rec.attribute12
3936 ,p_attribute13 => p_pull_sequence_rec.attribute13
3937 ,p_attribute14 => p_pull_sequence_rec.attribute14
3938 ,p_attribute15 => p_pull_sequence_rec.attribute15
3939 ,p_auto_request => p_pull_sequence_rec.auto_request
3940 ,p_auto_allocate_flag => p_pull_sequence_rec.auto_allocate_flag
3941 ,p_replenishment_type => p_pull_sequence_rec.replenishment_type
3942 ,p_consolidation => p_pull_sequence_rec.consolidation
3943 ,p_consolidation_group => p_pull_sequence_rec.consolidation_group
3944 ,p_future_card_size => p_pull_sequence_rec.future_card_size
3945 ,p_future_no_of_cards => p_pull_sequence_rec.future_no_of_cards
3946 ,p_planning_effectivity => p_pull_sequence_rec.planning_effectivity
3947 ,p_avg_dependent_demand => p_pull_sequence_rec.avg_dependent_demand
3948 ,p_avg_independent_demand => p_pull_sequence_rec.avg_independent_demand
3949 ,p_transaction_type => p_transaction_type
3950 ,x_ret_status => l_ret_status
3951 ,x_error_msg => l_error_msg);
3952
3953 if l_ret_status = FND_API.G_RET_STS_SUCCESS and
3954 p_transaction_type IN (FLM_KANBAN_MASSLOAD.KANBAN_ADD,FLM_KANBAN_MASSLOAD.KANBAN_CHANGE)
3955 then
3956 -- Fix bug 12402610
3957 -- when updating pull sequence, if source_type is null, derive the source_type from mtl_kanban_pull_sequences table
3958 if (p_transaction_type = FLM_KANBAN_MASSLOAD.KANBAN_CHANGE and p_pull_sequence_rec.source_type is null) then
3959 select mkps.source_type
3960 into l_source_type
3961 from mtl_kanban_pull_sequences mkps
3962 WHERE pull_sequence_id = p_pull_sequence_rec.pull_sequence_id;
3963 p_pull_sequence_rec.source_type := l_source_type;
3964 end if;
3965
3966 if p_pull_sequence_rec.source_type = inv_kanban_pvt.G_Source_Type_Supplier then
3967
3968 -- call process_suppliers procedure to Process multiple suppliers
3969 FOR i IN 1..l_supplier_count LOOP
3970
3971 if p_supplier_tbl(i).pull_sequence_id is null then
3972 p_supplier_tbl(i).pull_sequence_id := p_pull_sequence_rec.pull_sequence_id;
3973 end if;
3974
3975 -- organization id/code has been validated in process_pull_sequence,
3976 -- so just just need to retrieve the organization_id here
3977 IF p_pull_sequence_rec.organization_id IS NULL THEN
3978 l_organization_id := default_org_id(p_pull_sequence_rec.organization_code);
3979 ELSE
3980 l_organization_id := p_pull_sequence_rec.organization_id;
3981 END IF;
3982
3983 mydebug('Calling process_suppliers procedure.');
3984
3985 process_suppliers(p_pull_sequence_id => p_supplier_tbl(i).pull_sequence_id
3986 ,p_supplier_id => p_supplier_tbl(i).supplier_id
3987 ,p_supplier_name => p_supplier_tbl(i).supplier_name
3988 ,p_supplier_site_id => p_supplier_tbl(i).supplier_site_id
3989 ,p_supplier_site_code => p_supplier_tbl(i).supplier_site_code
3990 ,p_organization_id => l_organization_id
3991 ,p_sourcing_percentage => p_supplier_tbl(i).sourcing_percentage
3992 ,p_last_update_date => l_last_update_date
3993 ,p_last_updated_by => l_last_updated_by
3994 ,p_creation_date => l_creation_date
3995 ,p_created_by => l_created_by
3996 ,p_last_update_login => l_last_update_login
3997 ,p_transaction_type => p_supplier_tbl(i).transaction_type
3998 ,x_ret_status => l_ret_status
3999 ,x_error_msg => l_error_msg);
4000
4001 if l_ret_status = FND_API.G_RET_STS_ERROR then
4002 x_ret_status := l_ret_status;
4003 x_error_msg := l_error_msg;
4004 ROLLBACK TO SAVEPOINT A;
4005 exit;
4006 end if;
4007
4008 END LOOP;
4009
4010 if l_ret_status = FND_API.G_RET_STS_SUCCESS then
4011 mydebug('Calling FLM_MULTIPLE_SUPPLIERS.multiple_supplier_kanban_cards procedure.');
4012 --multiple supplier API..
4013 FLM_MULTIPLE_SUPPLIERS.multiple_supplier_kanban_cards(p_pull_seq_id => p_pull_sequence_rec.pull_sequence_id,
4014 x_retcode => l_ret_status,
4015 x_err_msg => l_error_msg);
4016 end if;
4017
4018 mydebug('l_ret_status='||l_ret_status);
4019 mydebug('l_error_msg='||l_error_msg);
4020
4021 -- Nullify supplier details at pull sequence if multiple suppliers are used
4022 if l_ret_status = FND_API.G_RET_STS_SUCCESS then
4023 UPDATE mtl_kanban_pull_sequences
4024 SET supplier_id = null
4025 ,supplier_site_id = null
4026 WHERE pull_sequence_id = p_pull_sequence_rec.pull_sequence_id;
4027 end if;
4028
4029 elsif p_pull_sequence_rec.source_type IN (inv_kanban_pvt.G_Source_Type_InterOrg,
4030 inv_kanban_pvt.G_Source_Type_IntraOrg,
4031 inv_kanban_pvt.G_Source_Type_Production) then
4032 if l_supplier_count > 0 then
4033 l_ret_status := FND_API.G_RET_STS_ERROR;
4034 FND_MESSAGE.SET_NAME('FLM','FLM_NO_SUPPLIERS_REQUIRED');
4035 l_error_msg := x_error_msg || FND_CONST.NEWLINE||FND_MESSAGE.GET;
4036 end if;
4037
4038 end if;
4039 end if;
4040
4041 -- call api to generate cards
4042 IF l_generate_cards = 'Y' and l_ret_status = FND_API.G_RET_STS_SUCCESS THEN
4043 mydebug('Calling INVKBCGN.create_kanban_cards procedure.');
4044
4045 INVKBCGN.create_kanban_cards(
4046 errbuf => l_error_msg
4047 ,retcode => l_ret_code
4048 ,x_org_id => null
4049 ,x_item_lo => null
4050 ,x_item_hi => null
4051 ,x_subinv => null
4052 ,x_locator_lo => null
4053 ,x_locator_hi => null
4054 ,x_source_type => null
4055 ,x_supplier_id => null
4056 ,x_supplier_site_id => null
4057 ,x_sourcing_org_id => null
4058 ,x_sourcing_subinv => null
4059 ,x_sourcing_loc_id => null
4060 ,x_wip_line_id => null
4061 ,x_status => 1
4062 ,x_pull_seq_id => p_pull_sequence_rec.pull_sequence_id
4063 ,x_print_kanban_card => null
4064 ,x_report_id => null
4065 ,x_enable_ekanban => null
4066 ,x_enable_dummy => null
4067 ,x_create_card_flag => 1
4068 ,x_plan_card_flag => 2);
4069
4070 mydebug('l_ret_status='||l_ret_status);
4071 mydebug('l_error_msg='||l_error_msg);
4072
4073 -- set l_ret_status based on l_ret_code of INVKBCGN.create_kanban_cards
4074 IF l_ret_code <> 1 THEN
4075 l_ret_status := FND_API.G_RET_STS_ERROR;
4076 ELSE
4077 l_ret_status := FND_API.G_RET_STS_SUCCESS;
4078 END IF;
4079
4080 END IF;
4081
4082 if l_ret_status = FND_API.G_RET_STS_ERROR then
4083 ROLLBACK TO SAVEPOINT A;
4084 end if;
4085
4086 x_ret_status := l_ret_status;
4087 x_error_msg := l_error_msg;
4088
4089 if p_commit_flag = 'Y' and l_ret_status = FND_API.G_RET_STS_SUCCESS then
4090 COMMIT;
4091 end if;
4092
4093 EXCEPTION
4094 WHEN OTHERS THEN
4095 x_ret_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4096 x_error_msg := 'Unexpected error in process_pull_sequence Public API procedure:'||SQLERRM;
4097 mydebug(x_error_msg);
4098 ROLLBACK TO SAVEPOINT A;
4099 END process_pull_sequence;
4100
4101 -- procedure for multiple suppliers processing
4102 -- pull sequence UI to call this
4103 PROCEDURE process_suppliers(p_pull_sequence_id IN OUT NOCOPY NUMBER
4104 ,p_supplier_id IN NUMBER DEFAULT NULL
4105 ,p_supplier_name IN VARCHAR2 DEFAULT NULL
4106 ,p_supplier_site_id IN NUMBER DEFAULT NULL
4107 ,p_supplier_site_code IN VARCHAR2 DEFAULT NULL
4108 ,p_organization_id IN NUMBER DEFAULT NULL
4109 ,p_sourcing_percentage IN NUMBER DEFAULT NULL
4110 ,p_last_update_date IN DATE DEFAULT NULL
4111 ,p_last_updated_by IN NUMBER DEFAULT NULL
4112 ,p_creation_date IN DATE DEFAULT NULL
4113 ,p_created_by IN NUMBER DEFAULT NULL
4114 ,p_last_update_login IN NUMBER DEFAULT NULL
4115 ,p_transaction_type IN NUMBER
4116 ,x_ret_status OUT NOCOPY VARCHAR2
4117 ,x_error_msg OUT NOCOPY VARCHAR2)
4118 IS
4119 r_supplier_rec flm_ekanban_pub.kanban_supplier_rec_type;
4120 l_ret_status VARCHAR2(1);
4121 l_error_msg VARCHAR2(4000);
4122 l_last_update_date DATE;
4123 l_last_updated_by NUMBER;
4124 l_creation_date DATE;
4125 l_created_by NUMBER;
4126 l_last_update_login NUMBER;
4127
4128 v_supplier_rec MTL_PULL_SEQ_SUPPLIERS%ROWTYPE;
4129
4130 BEGIN
4131
4132 mydebug('In process_suppliers procedure.');
4133
4134 FND_MSG_PUB.initialize;
4135
4136 --WHO columns
4137 l_last_update_date := g_last_update_date;
4138 l_last_updated_by := g_user_id;
4139 l_creation_date := g_creation_date;
4140 l_created_by := g_user_id;
4141 l_last_update_login := g_user_login_id;
4142
4143 mydebug('Assigning parameter values to supplier record.');
4144 --Assign parameter values to supplier rec
4145 r_supplier_rec.pull_sequence_id := p_pull_sequence_id;
4146 r_supplier_rec.organization_id := p_organization_id;
4147 r_supplier_rec.supplier_id := p_supplier_id;
4148 r_supplier_rec.supplier_name := p_supplier_name;
4149 r_supplier_rec.supplier_site_id := p_supplier_site_id;
4150 r_supplier_rec.supplier_site_code := p_supplier_site_code;
4151 r_supplier_rec.sourcing_percentage := p_sourcing_percentage;
4152 r_supplier_rec.transaction_type := p_transaction_type;
4153
4154 mydebug('Calling validate_suppliers procedure. ');
4155 -- call validate_suppliers procedure
4156 validate_suppliers(p_supplier_rec => r_supplier_rec
4157 ,p_organization_id => p_organization_id
4158 ,x_ret_status => l_ret_status
4159 ,x_error_msg => l_error_msg);
4160 mydebug('l_ret_status='||l_ret_status);
4161 mydebug('l_error_msg='||l_error_msg);
4162
4163 -- Inser/Update/Delete supplier
4164 if l_ret_status = FND_API.G_RET_STS_SUCCESS then
4165
4166 v_supplier_rec.pull_sequence_id := r_supplier_rec.pull_sequence_id;
4167 v_supplier_rec.organization_id := r_supplier_rec.organization_id;
4168 v_supplier_rec.supplier_id := r_supplier_rec.supplier_id;
4169 v_supplier_rec.supplier_site_id := r_supplier_rec.supplier_site_id;
4170 v_supplier_rec.sourcing_percentage := r_supplier_rec.sourcing_percentage;
4171 v_supplier_rec.last_update_date := l_last_update_date;
4172 v_supplier_rec.last_updated_by := l_last_updated_by;
4173 v_supplier_rec.creation_date := l_creation_date;
4174 v_supplier_rec.created_by := l_created_by;
4175 v_supplier_rec.last_update_login := l_last_update_login;
4176
4177 if p_transaction_type = FLM_KANBAN_MASSLOAD.KANBAN_ADD then
4178 -- call insert_supplier_row
4179 mydebug('Calling insert_supplier_row procedure. ');
4180 insert_supplier_row(p_supplier_rec => v_supplier_rec);
4181 elsif p_transaction_type = FLM_KANBAN_MASSLOAD.KANBAN_CHANGE then
4182 -- call update_supplier_row
4183 mydebug('Calling update_supplier_row procedure. ');
4184 update_supplier_row(p_supplier_rec => v_supplier_rec);
4185 elsif p_transaction_type = FLM_KANBAN_MASSLOAD.KANBAN_DELETE then
4186 -- call delete_supplier_row
4187 mydebug('Calling delete_supplier_row procedure. ');
4188 --Fix bug 12419603, pass v_supplier_rec.supplier_id instead of p_supplier_id
4189 --because v_supplier_rec.supplier_id will be defaulted by calling validate_suppliers
4190 --if p_supplier_id is null and p_supplier_name is not null
4191 delete_supplier_row(x_return_status => l_ret_status
4192 ,p_pull_sequence_id => p_pull_sequence_id
4193 ,p_supplier_id => v_supplier_rec.supplier_id); --Fix bug 12419603
4194
4195 end if;
4196
4197 IF l_ret_status = FND_API.G_RET_STS_ERROR THEN
4198 Raise FND_API.G_EXC_ERROR;
4199 END IF;
4200
4201 IF l_ret_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
4202 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
4203 END IF;
4204 end if;
4205
4206 x_ret_status := l_ret_status;
4207 x_error_msg := l_error_msg;
4208 EXCEPTION
4209
4210 WHEN FND_API.G_EXC_ERROR THEN
4211
4212 x_ret_status := FND_API.G_RET_STS_ERROR;
4213 x_error_msg := 'Execution error in process_suppliers procedure:'||SQLERRM;
4214 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4215
4216 x_ret_status := FND_API.G_RET_STS_UNEXP_ERROR;
4217 x_error_msg := 'Unexpected error in process_suppliers procedure:'||SQLERRM;
4218 WHEN OTHERS THEN
4219
4220 x_ret_status := FND_API.G_RET_STS_UNEXP_ERROR;
4221 x_error_msg := 'Unexpected error in process_suppliers procedure:'||SQLERRM;
4222 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4223 THEN
4224 FND_MSG_PUB.Add_Exc_Msg
4225 ( G_PKG_NAME
4226 , 'process_suppliers'
4227 );
4228 END IF;
4229 END process_suppliers;
4230
4231 ----------------------------------------------------------
4232 -- Procedure process_kanban
4233 -- This procedure called from flm_kanban_massload package
4234 ----------------------------------------------------------
4235 PROCEDURE process_kanban
4236 ( p_pull_sequence_tbl IN OUT NOCOPY flm_kanban_massload.pull_seq_tbl_type,
4237 p_supplier_tbl IN OUT NOCOPY flm_kanban_massload.kanban_supp_tbl_type,
4238 p_kanban_card_tbl IN OUT NOCOPY flm_kanban_massload.card_int_tbl_type,
4239 x_ret_status OUT NOCOPY VARCHAR2
4240 )
4241 IS
4242
4243 l_ret_status1 VARCHAR2(1);
4244 l_ret_status2 VARCHAR2(1);
4245
4246 BEGIN
4247
4248 -- process pull sequence
4249 IF p_pull_sequence_tbl.COUNT > 0 THEN
4250 process_ps(p_pull_sequence_tbl, p_supplier_tbl, l_ret_status1);
4251 END IF;
4252
4253 -- process cards procedure
4254 IF p_kanban_card_tbl.COUNT > 0 THEN
4255 process_kc(p_kanban_card_tbl, l_ret_status2);
4256 END IF;
4257
4258 IF l_ret_status1 = FND_API.G_RET_STS_ERROR OR l_ret_status2 = FND_API.G_RET_STS_ERROR THEN
4259 x_ret_status := FND_API.G_RET_STS_ERROR;
4260 ELSE
4261 x_ret_status := FND_API.G_RET_STS_SUCCESS;
4262 END IF;
4263
4264 END process_kanban;
4265
4266 -- Get_Pull_Sequence_Tokens : This procedure gets the names required to
4267 -- build the message for a pull sequence
4268 --
4269 PROCEDURE Get_Pull_Sequence_Tokens
4270 (p_Pull_Sequence_Id Number,
4271 p_organization_id number,
4272 p_inventory_item_id number,
4273 p_locator_id number,
4274 x_org_code Out NOCOPY varchar2,
4275 x_item_name Out NOCOPY varchar2,
4276 x_subinventory Out NOCOPY varchar2,
4277 x_loc_name Out NOCOPY varchar2)
4278
4279 IS
4280
4281 l_locator_id number;
4282 l_organization_id number;
4283
4284 Begin
4285
4286 if p_pull_sequence_id is not null then
4287
4288 Select concatenated_segments,organization_code,
4289 subinventory_name,locator_id,pull.organization_id
4290 into x_item_name,x_org_code,x_subinventory,l_locator_id,
4291 l_organization_id
4292 from mtl_system_items_kfv a , mtl_parameters b,
4293 mtl_kanban_pull_sequences pull
4294 where a.inventory_item_id = pull.inventory_item_id
4295 and a.organization_id = Pull.organization_id
4296 and b.organization_id = Pull.organization_id
4297 and pull.pull_sequence_id = p_Pull_sequence_id;
4298
4299 if l_locator_id is not null Then
4300
4301 Select concatenated_segments
4302 into x_loc_name
4303 from mtl_item_locations_kfv
4304 where inventory_location_id = l_locator_id
4305 and organization_id = l_organization_id;
4306
4307 end if;
4308 else
4309 Select concatenated_segments,organization_code
4310 into x_item_name,x_org_code
4311 from mtl_system_items_kfv a , mtl_parameters b
4312 where a.inventory_item_id =
4313 p_inventory_item_id
4314 and a.organization_id =
4315 p_organization_id
4316 and b.organization_id =
4317 p_organization_id;
4318
4319 if p_locator_id is not null Then
4320
4321 Select concatenated_segments
4322 into x_loc_name
4323 from mtl_item_locations_kfv
4324 where inventory_location_id = p_locator_id
4325 and organization_id = p_organization_id;
4326
4327 end if;
4328
4329 end if;
4330 Exception
4331
4332 When Others
4333 Then Null;
4334
4335 End Get_Pull_Sequence_Tokens;
4336
4337 function default_org_id(p_org_code in varchar2)
4338 return number is
4339 l_org_id number;
4340 begin
4341 select organization_id
4342 into l_org_id
4343 from mtl_parameters
4344 where organization_code = p_org_code;
4345 return l_org_id;
4346 exception
4347 when others then
4348 return null;
4349 end default_org_id;
4350
4351 function is_org_id_invalid(p_org_id number)
4352 return boolean
4353 is
4354 l_count number;
4355 begin
4356 select count(organization_id)
4357 into l_count
4358 from mtl_parameters
4359 where organization_id = p_org_id;
4360
4361 if l_count = 1 then
4362 return false;
4363 else
4364 return true;
4365 end if;
4366 end is_org_id_invalid;
4367
4368 function default_wip_line_id(p_wip_line_code in varchar2
4369 ,p_org_id in number)
4370 return number is
4371 l_wip_line_id number;
4372 begin
4373 select line_id
4374 into l_wip_line_id
4375 from wip_lines_val_v
4376 where line_code = p_wip_line_code
4377 and organization_id = p_org_id;
4378 return l_wip_line_id;
4379 exception
4380 when others then
4381 return null;
4382 end default_wip_line_id;
4383
4384 function is_wip_line_id_invalid(p_wip_line_id number)
4385 return boolean
4386 is
4387 l_count number;
4388 begin
4389 select count(1)
4390 into l_count
4391 from wip_lines_val_v
4392 where line_id = p_wip_line_id;
4393
4394 if l_count = 1 then
4395 return false;
4396 else
4397 return true;
4398 end if;
4399 end is_wip_line_id_invalid;
4400
4401 function default_supplier_id(p_supplier_name in varchar2)
4402 return number is
4403 l_supplier_id number;
4404 begin
4405 SELECT vendor_id
4406 INTO l_supplier_id
4407 FROM PO_SUPPLIERS_VAL_V
4408 WHERE vendor_name = p_supplier_name;
4409
4410 return l_supplier_id;
4411 exception
4412 when others then
4413 return null;
4414 end default_supplier_id;
4415
4416 function is_supplier_id_invalid(p_supplier_id number)
4417 return boolean
4418 is
4419 l_count number;
4420 begin
4421 SELECT count(vendor_id)
4422 into l_count
4423 from PO_SUPPLIERS_VAL_V
4424 where vendor_id = p_supplier_id;
4425
4426 if l_count = 1 then
4427 return false;
4428 else
4429 return true;
4430 end if;
4431
4432 end is_supplier_id_invalid;
4433
4434 function default_supplier_site_id(p_supplier_id in number
4435 ,p_supplier_site_code in varchar2
4436 ,p_org_id in number)
4437 return number is
4438 l_supplier_site_id number;
4439 begin
4440 SELECT vendor_site_id
4441 INTO l_supplier_site_id
4442 FROM MTL_SUPPLIER_SITES_V
4443 WHERE vendor_site_code = p_supplier_site_code
4444 AND vendor_id = p_supplier_id
4445 AND organization_id = p_org_id;
4446
4447 return l_supplier_site_id;
4448 exception
4449 when others then
4450 return null;
4451 end default_supplier_site_id;
4452
4453 function is_supplier_site_id_invalid(p_supplier_id in number
4454 ,p_supplier_site_id in number
4455 ,p_org_id in number)
4456 return boolean
4457 is
4458 l_count number;
4459 begin
4460 SELECT count(vendor_site_id)
4461 INTO l_count
4462 FROM MTL_SUPPLIER_SITES_V
4463 WHERE vendor_site_id = p_supplier_site_id
4464 AND vendor_id = p_supplier_id
4465 AND organization_id = p_org_id;
4466
4467 if l_count = 1 then
4468 return false;
4469 else
4470 return true;
4471 end if;
4472 end is_supplier_site_id_invalid;
4473
4474
4475 function default_inv_item_id(p_org_id in number
4476 ,p_conc_segments in varchar2)
4477 return number
4478 is
4479 l_inv_item_id number;
4480 begin
4481 SELECT mpk.inventory_item_id
4482 INTO l_inv_item_id
4483 FROM mtl_system_items_kfv mpk
4484 WHERE mpk.organization_id = p_org_id
4485 AND UPPER(mpk.concatenated_segments) = UPPER (p_conc_segments);
4486
4487 return l_inv_item_id;
4488 exception
4489 when others then
4490 return null;
4491 end default_inv_item_id;
4492
4493 function default_kanban_card_id(p_pull_sequence_id in number
4494 ,p_kanban_card_number in varchar2)
4495 return number
4496 is
4497 l_kanban_card_id number;
4498 begin
4499 SELECT kanban_card_id
4500 INTO l_kanban_card_id
4501 FROM MTL_KANBAN_CARDS
4502 WHERE pull_sequence_id = p_pull_sequence_id
4503 AND kanban_card_number = p_kanban_card_number;
4504
4505 RETURN l_kanban_card_id;
4506 exception
4507 when others then
4508 return null;
4509 end default_kanban_card_id;
4510
4511 END FLM_KANBAN_PUB;