[Home] [Help]
PACKAGE BODY: APPS.INV_KANBAN_PVT
Source
1 PACKAGE BODY INV_Kanban_PVT as
2 /* $Header: INVVKBNB.pls 120.27.12020000.2 2012/06/27 14:23:26 akuppa ship $ */
3
4 -- Global constant holding the package name
5
6 G_PKG_NAME CONSTANT VARCHAR2(30) := 'INV_KANBAN_PVT';
7 TYPE Kanban_Card_Tbl_Type IS TABLE OF INV_Kanban_PVT.Kanban_Card_Rec_Type
8 INDEX BY BINARY_INTEGER;
9
10
11 PROCEDURE mydebug(msg IN VARCHAR2) IS
12 BEGIN
13 inv_log_util.trace(msg, 'INV_KANBAN_PVT', 9);
14 END mydebug;
15
16
17 --
18 --
19 --
20 -- Get_Constants : This procedure returns the server side global variables.
21 -- Client side can have access to these global variables by
22 -- calling this procedure.
23 --
24 Procedure Get_Constants
25 (X_Ret_Success Out NOCOPY Varchar2,
26 X_Ret_Error Out NOCOPY Varchar2 ,
27 X_Ret_Unexp_Error Out NOCOPY Varchar2 ,
28 X_Current_Plan Out NOCOPY Number,
29 X_Source_Type_InterOrg Out NOCOPY Number,
30 X_Source_Type_Supplier Out NOCOPY Number,
31 X_Source_Type_IntraOrg Out NOCOPY Number,
32 X_Source_Type_Production Out NOCOPY Number,
33 X_Card_Type_Replenishable Out NOCOPY Number,
34 X_Card_Type_NonReplenishable Out NOCOPY Number,
35 X_Card_Status_Active Out NOCOPY Number,
36 X_Card_Status_Hold Out NOCOPY Number,
37 X_Card_Status_Cancel Out NOCOPY Number,
38 X_No_Pull_sequence Out NOCOPY Number,
39 X_Doc_Type_Po Out NOCOPY Number,
40 X_Doc_Type_Release Out NOCOPY Number,
41 X_Doc_Type_Internal_Req Out NOCOPY Number)
42 IS
43 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
44 Begin
45
46 X_Ret_Success := FND_API.G_RET_STS_SUCCESS;
47 X_Ret_Error := FND_API.G_RET_STS_ERROR;
48 X_Ret_Unexp_Error := FND_API.G_RET_STS_UNEXP_ERROR;
49 X_Current_Plan := INV_Kanban_PVT.G_Current_Plan;
50 X_Source_Type_InterOrg := INV_Kanban_PVT.G_Source_Type_InterOrg;
51 X_Source_Type_Supplier := INV_Kanban_PVT.G_Source_Type_Supplier;
52 X_Source_Type_IntraOrg := INV_Kanban_PVT.G_Source_Type_IntraOrg;
53 X_Source_Type_Production := INV_Kanban_PVT.G_Source_Type_Production;
54 X_Card_Type_Replenishable := INV_Kanban_PVT.G_Card_Type_Replenishable;
55 X_Card_Type_NonReplenishable := INV_Kanban_PVT.G_Card_Type_NonReplenishable;
56 X_Card_Status_Active := INV_Kanban_PVT.G_Card_Status_Active;
57 X_Card_Status_Hold := INV_Kanban_PVT.G_Card_Status_Hold;
58 X_Card_Status_Cancel := INV_Kanban_PVT.G_Card_Status_Cancel;
59 X_No_Pull_sequence := INV_Kanban_PVT.G_No_Pull_sequence;
60 X_Doc_Type_Po := INV_Kanban_PVT.G_Doc_Type_Po;
61 X_Doc_Type_Release := INV_Kanban_PVT.G_Doc_Type_Release;
62 X_Doc_Type_Internal_Req := INV_Kanban_PVT.G_Doc_Type_Internal_Req;
63
64 End Get_Constants;
65 --
66 --
67 -- Get_Pull_Sequence_Tokens : This procedure gets the names required to
68 -- build the message for a pull sequence
69 --
70 PROCEDURE Get_Pull_Sequence_Tokens
71 (p_Pull_Sequence_Id Number,
72 x_org_code Out NOCOPY varchar2,
73 x_item_name Out NOCOPY varchar2,
74 x_subinventory Out NOCOPY varchar2,
75 x_loc_name Out NOCOPY varchar2)
76 IS
77
78 l_locator_id number;
79 l_organization_id number;
80 l_org_code varchar2(3);
81 l_item_name varchar2(200);
82 l_loc_name varchar2(200);
83 l_subinventory varchar2(10);
84
85 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
86 Begin
87 Select concatenated_segments,organization_code,
88 subinventory_name,locator_id,pull.organization_id
89 into x_item_name,x_org_code,x_subinventory,l_locator_id,
90 l_organization_id
91 from mtl_system_items_kfv a , mtl_parameters b,
92 mtl_kanban_pull_sequences pull
93 where a.inventory_item_id = pull.inventory_item_id
94 and a.organization_id = Pull.organization_id
95 and b.organization_id = Pull.organization_id
96 and pull.pull_sequence_id = p_Pull_sequence_id;
97
98 if l_locator_id is not null Then
99
100 Select concatenated_segments
101 into x_loc_name
102 from mtl_item_locations_kfv
103 where inventory_location_id = l_locator_id
104 and organization_id = l_organization_id;
105
106 end if;
107 Exception
108
109 When Others
110 Then Null;
111
112 End Get_Pull_Sequence_Tokens;
113
114 --
115 --
116 -- Delete_Pull_Sequence : This procedure deletes all pull sequences for
117 -- a given plan.
118 --
119 --
120 PROCEDURE Delete_Pull_Sequence
121 (x_return_status Out NOCOPY Varchar2,
122 p_kanban_plan_id Number)
123
124 IS
125
126 Cursor Get_Pull_Sequences IS
127 Select pull_sequence_id
128 From mtl_kanban_pull_sequences
129 Where kanban_plan_id = p_kanban_plan_id;
130
131 l_return_status Varchar2(1) := FND_API.G_RET_STS_SUCCESS;
132
133 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
134 Begin
135 If p_kanban_plan_id = INV_kanban_PVT.G_Current_Plan
136 then
137 For pull_sequences in get_pull_sequences
138 loop
139 If Ok_To_Delete_Pull_Sequence(pull_sequences.pull_sequence_id)
140 then
141 INV_PullSequence_Pkg.delete_Row(l_return_status,pull_sequences.pull_sequence_id);
142 Else
143 Raise FND_API.G_EXC_ERROR;
144 end if;
145 end loop;
146 Else
147 Delete from Mtl_kanban_pull_sequences
148 Where kanban_plan_id = p_kanban_plan_id;
149 end if;
150 x_return_status := l_return_status;
151
152 Exception
153
154 WHEN FND_API.G_EXC_ERROR THEN
155
156 x_return_status := FND_API.G_RET_STS_ERROR;
157
158 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
159
160 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
161
162 WHEN OTHERS THEN
163
164 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
165 THEN
166 FND_MSG_PUB.Add_Exc_Msg
167 ( G_PKG_NAME
168 , 'Validate_Pull_Sequence'
169 );
170 END IF;
171 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
172
173 End Delete_Pull_Sequence;
174 --
175 --
176 -- Validate_Pull_Sequence : This procedure verifies whether all required
177 -- fields are present.
178 --
179 --
180 PROCEDURE Validate_Pull_Sequence
181 (p_Pull_Sequence_Rec INV_Kanban_PVT.Pull_sequence_Rec_Type)
182 IS
183 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
184 Begin
185 if p_Pull_Sequence_Rec.Organization_Id is null
186 then
187 FND_MESSAGE.SET_NAME('INV','INV_ORG_REQUIRED');
188 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Organization');
189 FND_MSG_PUB.Add;
190 Raise FND_API.G_EXC_ERROR;
191 End if;
192
193 if p_Pull_Sequence_Rec.inventory_item_id is null
194 then
195 FND_MESSAGE.SET_NAME('INV','INV_ITEM_REQUIRED');
196 FND_MSG_PUB.Add;
197 Raise FND_API.G_EXC_ERROR;
198 end if;
199
200 if p_Pull_Sequence_Rec.subinventory_name is null
201 then
202 FND_MESSAGE.SET_NAME('INV','INV_SUBINV_REQUIRED');
203 FND_MSG_PUB.Add;
204 Raise FND_API.G_EXC_ERROR;
205 end if;
206
207 if p_Pull_Sequence_Rec.source_type is null
208 then
209 FND_MESSAGE.SET_NAME('INV','INV_ATTRIBUTE_REQUIRED');
210 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','INV_SOURCE_TYPE',TRUE);
211 FND_MSG_PUB.Add;
212 Raise FND_API.G_EXC_ERROR;
213 end if;
214
215 if p_Pull_Sequence_Rec.kanban_plan_id is null
216 then
217 FND_MESSAGE.SET_NAME('INV','INV_ATTRIBUTE_REQUIRED');
218 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','INV_KANBAN_PLAN',TRUE);
219 FND_MSG_PUB.Add;
220 Raise FND_API.G_EXC_ERROR;
221 end if;
222
223 if p_Pull_Sequence_Rec.source_type = INV_Kanban_PVT.G_Source_type_IntraOrg
224 And p_Pull_Sequence_Rec.source_subinventory is null
225 then
226 FND_MESSAGE.SET_NAME('INV','INV_ATTRIBUTE_REQUIRED');
227 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','INV_SOURCE_SUBINV',TRUE);
228 FND_MSG_PUB.Add;
229 Raise FND_API.G_EXC_ERROR;
230 end if;
231
232 /*Code modification for bug2186198*/
233 /*if p_Pull_Sequence_Rec.source_type = INV_Kanban_PVT.G_Source_type_Production
234 And p_Pull_Sequence_Rec.wip_line_id is null
235 then
236 FND_MESSAGE.SET_NAME('INV','INV_ATTRIBUTE_REQUIRED');
237 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','INV_WIP_LINE');
238 FND_MSG_PUB.Add;
239 Raise FND_API.G_EXC_ERROR;
240 end if; */
241
242
243 EXCEPTION
244
245 WHEN FND_API.G_EXC_ERROR THEN
246
247 Raise FND_API.G_EXC_ERROR;
248
249 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
250
251 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
252
253 WHEN OTHERS THEN
254
255 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
256 THEN
257 FND_MSG_PUB.Add_Exc_Msg
258 ( G_PKG_NAME
259 , 'Validate_Pull_Sequence'
260 );
261 END IF;
262 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
263
264 End Validate_Pull_Sequence;
265 --
266 --
267 -- Insert_Pull_Sequence : This procedure inserts record in
268 -- MTL_KANBAN_PULL_SEQUENCES with data from the record.
269 --
270 --
271 PROCEDURE Insert_Pull_Sequence
272 (x_return_status Out NOCOPY Varchar2,
273 p_Pull_Sequence_Rec INV_Kanban_PVT.Pull_sequence_Rec_Type)
274 IS
275 l_Pull_Sequence_Rec INV_Kanban_PVT.Pull_sequence_Rec_Type;
276 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
277 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
278 Begin
279 FND_MSG_PUB.initialize;
280 l_Pull_Sequence_Rec := INV_PullSequence_PKG.Convert_Miss_To_Null
281 (p_Pull_Sequence_Rec);
282
283 Validate_Pull_sequence(l_pull_sequence_rec);
284
285 l_pull_sequence_rec.Creation_Date := SYSDATE;
286 l_pull_sequence_rec.Created_By := FND_GLOBAL.USER_ID;
287 l_pull_sequence_rec.last_update_date := SYSDATE;
288 l_pull_sequence_rec.last_updated_by := FND_GLOBAL.USER_ID;
289 l_pull_sequence_rec.last_update_login := FND_GLOBAL.LOGIN_ID;
290
291 INV_PullSequence_PKG.Insert_Row(l_pull_sequence_rec);
292
293 x_return_status := l_return_status;
294
295 EXCEPTION
296
297 WHEN FND_API.G_EXC_ERROR THEN
298
299 x_return_status := FND_API.G_RET_STS_ERROR;
300
301 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
302
303 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
304
305 WHEN OTHERS THEN
306
307 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
308 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
309 THEN
310 FND_MSG_PUB.Add_Exc_Msg
311 ( G_PKG_NAME
312 , 'Insert_Pull_sequence'
313 );
314 END IF;
315
316 End Insert_Pull_sequence;
317 --
318 --
319 -- Update_Pull_Sequence : This procedure Updates the record in
320 -- MTL_KANBAN_PULL_SEQUENCES with data from the
321 -- record.
322 --
323 --
324 PROCEDURE Update_Pull_sequence
325 (x_return_status Out NOCOPY Varchar2,
326 x_Pull_Sequence_Rec IN OUT NOCOPY INV_Kanban_PVT.Pull_sequence_Rec_Type)
327 IS
328 l_Pull_Sequence_Rec INV_Kanban_PVT.Pull_Sequence_Rec_Type;
329 l_Old_Pull_Sequence_Rec INV_Kanban_PVT.Pull_Sequence_Rec_Type;
330 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
331 l_org_code VARCHAR2(3);
332 l_item_name VARCHAR2(30);
333 l_loc_name VARCHAR2(30);
334
335
336 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
337 Begin
338 FND_MSG_PUB.initialize;
339 l_Pull_Sequence_Rec := x_Pull_Sequence_Rec;
340 l_Pull_Sequence_Rec.pull_sequence_id := Null;
341
342 if (l_pull_Sequence_Rec.pull_sequence_Id is null or
343 l_pull_Sequence_Rec.pull_sequence_Id = FND_API.G_MISS_NUM) And
344 (l_pull_Sequence_Rec.Kanban_Plan_Id is null or
345 l_pull_Sequence_Rec.Kanban_Plan_Id = FND_API.G_MISS_NUM or
346 l_pull_Sequence_Rec.Organization_Id is null or
347 l_pull_Sequence_Rec.Organization_Id = FND_API.G_MISS_NUM or
348 l_pull_Sequence_Rec.Inventory_Item_Id is null or
349 l_pull_Sequence_Rec.Inventory_Item_Id = FND_API.G_MISS_NUM or
350 l_pull_Sequence_Rec.Subinventory_Name is null or
351 l_pull_Sequence_Rec.Subinventory_Name = FND_API.G_MISS_CHAR )
352 then
353 FND_MESSAGE.SET_NAME('INV','INV_ATTRIBUTE_REQUIRED');
354 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','INV_PULL_SEQUENCE');
355 FND_MSG_PUB.Add;
356 l_return_status := FND_API.G_RET_STS_ERROR;
357
358 elsif (l_pull_Sequence_Rec.pull_sequence_Id is null or
359 l_pull_Sequence_Rec.pull_sequence_Id = FND_API.G_MISS_NUM)
360 then
361 Begin
362 Select Pull_Sequence_Id
363 Into l_Pull_Sequence_Rec.Pull_Sequence_Id
364 From Mtl_Kanban_Pull_Sequences
365 Where Kanban_Plan_Id = l_pull_Sequence_Rec.Kanban_plan_id
366 And Organization_Id = l_pull_Sequence_Rec.Organization_Id
367 And Inventory_Item_Id = l_pull_Sequence_Rec.Inventory_Item_Id
368 And Subinventory_Name = l_pull_Sequence_Rec.Subinventory_Name
369 And Nvl(Locator_Id,-1) = Nvl(l_pull_Sequence_Rec.Locator_Id,-1);
370 Exception
371 When No_data_Found
372 Then
373
374 Select concatenated_segments,organization_code
375 into l_item_name,l_org_code
376 from mtl_system_items_kfv a , mtl_parameters b
377 where a.inventory_item_id =
378 l_Pull_Sequence_Rec.inventory_item_id
379 and a.organization_id =
380 l_Pull_Sequence_Rec.organization_id
381 and b.organization_id =
382 l_Pull_Sequence_Rec.organization_id;
383
384 if l_Pull_Sequence_Rec.locator_id is not null Then
385
386 Select concatenated_segments
387 into l_loc_name
388 from mtl_item_locations_kfv
389 where inventory_location_id =
390 l_Pull_Sequence_Rec.locator_id
391 and organization_id =
392 l_Pull_Sequence_Rec.organization_id;
393
394 end if;
395
396 FND_MESSAGE.SET_NAME('INV','INV_NO_PULLSEQ_EXISTS');
397 FND_MESSAGE.SET_TOKEN('ORG_CODE',l_org_code);
398 FND_MESSAGE.SET_TOKEN('ITEM_NAME',l_item_name);
399 FND_MESSAGE.SET_TOKEN('SUB_CODE',l_Pull_Sequence_Rec.Subinventory_Name);
400 FND_MESSAGE.SET_TOKEN('LOCATOR_NAME',l_loc_name);
401 FND_MSG_PUB.Add;
402 l_return_status := FND_API.G_RET_STS_ERROR;
403 When Others
404 Then
405 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
406 End;
407 end if;
408
409 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
410
411 RAISE FND_API.G_EXC_ERROR;
412
413 END IF;
414
415 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
416
417 RAISE FND_API.G_EXC_ERROR;
418
419 END IF;
420 -- dbms_output.put_line('pull:'||to_char(l_pull_sequence_rec.pull_sequence_id));
421
422 l_old_pull_sequence_rec := INV_PullSequence_PKG.Query_Row (p_pull_sequence_id =>
423 l_pull_sequence_rec.pull_sequence_id);
424 l_pull_sequence_rec := INV_PullSequence_Pkg.Complete_Record
425 ( p_pull_sequence_rec => l_pull_sequence_rec
426 , p_old_pull_sequence_rec => l_old_pull_sequence_rec
427 );
428 Validate_Pull_sequence(l_Pull_sequence_rec);
429
430 l_pull_sequence_rec.last_update_date := SYSDATE;
431 l_pull_sequence_rec.last_updated_by := FND_GLOBAL.USER_ID;
432 l_pull_sequence_rec.last_update_login := FND_GLOBAL.LOGIN_ID;
433
434 INV_PullSequence_PKG.Update_Row(l_pull_sequence_Rec);
435
436 x_return_status := l_return_status;
437
438 x_pull_sequence_rec := l_Pull_Sequence_Rec;
439
440 EXCEPTION
441
442 WHEN FND_API.G_EXC_ERROR THEN
443
444 x_return_status := FND_API.G_RET_STS_ERROR;
445
446 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
447
448 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
449
450 WHEN OTHERS THEN
451
452 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
453 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
454 THEN
455 FND_MSG_PUB.Add_Exc_Msg
456 ( G_PKG_NAME
457 , 'Update_Pull_sequence'
458 );
459 END IF;
460
461 End Update_Pull_sequence;
462 --
463 --Update_Pull_Sequence_Tbl : This procedure Updates the records in
464 -- MTL_KANBAN_PULL_SEQUENCES with data from the
465 -- table of pull sequences id's passed to it.
466 -- It generates and prints kanban cards as well.
467 ------------------------------------------------------------------------------
468 -- PROCEDURE : UPDATE_PULL_SEQUENCE_TBL
469 ------------------------------------------------------------------------------
470 PROCEDURE Update_Pull_sequence_Tbl (x_return_status Out NOCOPY Varchar2,
471 p_Pull_Sequence_tbl INV_Kanban_PVT.Pull_sequence_Id_Tbl_Type,
472 x_update_flag Varchar2,
473 p_operation_tbl INV_Kanban_PVT.operation_tbl_type := G_operation_tbl)
474 IS
475 l_record_count NUMBER := 0;
476 l_PullSeqTable INV_Kanban_PVT.Pull_Sequence_id_Tbl_Type;
477 l_report_id NUMBER := 0;
478 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
479 v_req_id NUMBER;
480 v_generate_cards BOOLEAN := FALSE;
481 l_Pull_sequence_rec INV_Kanban_PVT.Pull_sequence_Rec_Type;
482
483
484 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
485
486 l_operation_tbl INV_Kanban_PVT.operation_tbl_type; --Operation to be performed update/insert
487 l_report_generate_id NUMBER := 0; --Report id for generating cards
488 l_report_print_id NUMBER := 0; --Report id for printing cards
489 l_count_generate NUMBER := 0; --Count of number of cards to be generated
490 l_count_print NUMBER := 0; -- Count of number of cards to be printed
491 CURSOR C2 IS SELECT mtl_kanban_pull_sequences_s.nextval FROM sys.dual;
492 --Get new pull_sequence_id during insert.
493
494 Begin
495 FND_MSG_PUB.initialize;
496 l_PullSeqTable := p_Pull_Sequence_tbl;
497 l_operation_tbl:= p_operation_tbl;
498
499 SELECT MTL_KANBAN_CARD_PRINT_TEMP_S.nextval
500 INTO l_report_generate_id from DUAL;
501
502 SELECT MTL_KANBAN_CARD_PRINT_TEMP_S.nextval
503 INTO l_report_print_id from DUAL;
504
505 FOR l_record_count in 1 ..l_PullSeqtable.Count LOOP
506 l_pull_sequence_rec := INV_PullSequence_PKG.Query_Row
507 ( p_pull_sequence_id =>
508 l_PullSeqtable(l_record_count));
509 l_pull_sequence_rec.kanban_plan_id := -1;
510 IF X_UPDATE_FLAG = 'Y' THEN
511 v_generate_cards := TRUE;
512 IF(l_pull_sequence_rec.planning_update_status = 1) THEN
513 l_pull_sequence_rec.planning_update_status := NULL ;
514 UPDATE mtl_kanban_pull_sequences
515 SET planning_update_status = NULL
516 WHERE pull_sequence_id = l_pull_sequence_rec.pull_sequence_id;
517 END IF;
518
519 IF (l_operation_tbl.COUNT > 0) THEN
520 IF(l_operation_tbl(l_record_count) = 0 ) THEN
521 INV_Kanban_PVT.update_pull_sequence(
522 l_return_status,
523 l_Pull_sequence_rec);
524 ELSE
525 OPEN C2;
526 FETCH C2 INTO l_Pull_Sequence_Rec.pull_sequence_id;
527 CLOSE C2;
528
529 INV_Kanban_PVT.insert_pull_sequence(
530 l_return_status,
531 l_Pull_sequence_rec);
532 END IF;
533
534 ELSE
535 -- Existing Functionality
536 INV_Kanban_PVT.update_pull_sequence(
537 l_return_status,
538 l_Pull_sequence_rec);
539
540 END IF; -- end of l_operation_tbl.COUNT > 0 if loop
541
542 -- Check for errors and take action
543 IF l_return_status IN ( FND_API.G_RET_STS_ERROR,
544 FND_API.G_RET_STS_UNEXP_ERROR) THEN
545 RAISE FND_API.G_EXC_ERROR;
546 END IF;
547 ELSE
548 IF INV_kanban_PVT.Ok_To_Create_Kanban_Cards( p_pull_sequence_id =>
549 l_PullSeqtable(l_record_count) ) then
550 v_generate_cards := TRUE;
551 ELSE
552 v_generate_cards := FALSE;
553 END IF;
554 END IF;
555
556 -- Two counter variables l_count_generate and l_count_print are defined.
557 -- Depending upon the value of v_generate_cards, the respective counter variable will be
558 -- incremented each time we loop through the PullSeqTable.
559 IF(v_generate_cards) THEN
560 l_report_id := l_report_generate_id;
561 l_count_generate := l_count_generate + 1;
562 ELSE
563 l_report_id := l_report_print_id;
564 l_count_print := l_count_print +1;
565 END IF;
566
567 insert into mtl_kanban_card_print_temp(
568 report_id,
569 kanban_card_id,
570 pull_sequence_id)
571 values (
572 l_report_id,
573 -1,
574 l_Pull_sequence_rec.pull_sequence_id
575 );
576
577
578 IF X_UPDATE_FLAG = 'Y' THEN
579 -- we should not delete old kanban cards but change their status
580 -- for historical purposes.
581
582 update_kanban_card_status
583 (p_card_status => g_card_status_cancel,
584 p_pull_sequence_id => l_Pull_Sequence_Rec.pull_sequence_id);
585 END IF;
586 --
587
588
589 END LOOP;
590
591 -- Instead of v_generate_cards, cards will be generated if l_count_generate > 0
592 IF( l_count_generate > 0) THEN
593 v_req_id := fnd_request.submit_request(
594 'INV',
595 'INVKBCGN',
596 NULL,
597 NULL,
598 FALSE,
599 NULL,
600 NULL,
601 NULL,
602 NULL,
603 NULL,
604 NULL,
605 NULL,
606 NULL,
607 NULL,
608 NULL,
609 NULL,
610 NULL,
611 NULL,
612 1,
613 NULL,
614 1,
615 l_report_id );
616
617 IF ( NVL(v_req_id, 0) <> 0) THEN
618 COMMIT;
619 FND_MESSAGE.SET_NAME ('INV', 'INV_PROCESS');
620 Fnd_message.set_token('REQUEST_ID',to_char(v_req_id), FALSE);
621 fnd_message.set_token('PROCESS','INV_GENERATE_KANBAN_CARDS', TRUE);
622 FND_MSG_PUB.Add;
623 ELSE
624 fnd_message.set_name('INV','INV_PROCESS_FAILED');
625 fnd_message.set_token('PROCESS', 'INV_GENERATE_KANBAN_CARDS', TRUE);
626 FND_MSG_PUB.Add;
627 RAISE FND_API.G_EXC_ERROR;
628 END IF;
629 END IF;
630
631 -- Cards will be printed if variable l_count_print > 0
632 IF (l_count_print >0 ) THEN
633 v_req_id := fnd_request.submit_request( 'INV',
634 'INVKBCPR',
635 NULL,
636 NULL,
637 FALSE,
638 NULL, /* p_org_id */
639 NULL, /* p_date_created_low */
640 NULL, /* p_date_created_high */
641 NULL, /* p_kanban_card_number_low */
642 NULL, /* p_kanban_card_number_high */
643 NULL, /* p_item_low */
644 NULL, /* p_item_high */
645 NULL, /* p_subinv */
646 NULL, /* p_locator_low */
647 NULL, /* p_locator_high */
648 NULL, /* p_source_type */
649 NULL, /* p_kanban_card_type */
650 NULL, /* p_supplier */
651 NULL, /* p_supplier_site */
652 NULL, /* p_source_org_id */
653 NULL, /* p_source_subinv */
654 NULL, /* p_source_loc_id */
655 3, /* p_sort_by */
656 2, /* p_call_from */
657 NULL, /* p_kanban_card_id */
658 l_report_id /* p_report_id */
659 );
660
661 IF ( NVL(v_req_id, 0) <> 0) THEN
662 COMMIT;
663 FND_MESSAGE.SET_NAME ('INV', 'INV_PROCESS');
664 Fnd_message.set_token('REQUEST_ID',to_char(v_req_id), FALSE);
665 fnd_message.set_token('PROCESS', 'INV_PRINT_KANBAN_CARDS', TRUE);
666 FND_MSG_PUB.Add;
667 ELSE
668 -- delete from MTL_KANBAN_CARD_PRINT_TEMP
669 -- where
670 -- report_id = l_report_id;
671 -- COMMIT;
672 fnd_message.set_name('INV','INV_PROCESS_FAILED');
673 fnd_message.set_token('PROCESS', 'INV_PRINT_KANBAN_CARDS', TRUE);
674 FND_MSG_PUB.Add;
675 RAISE FND_API.G_EXC_ERROR;
676 END IF;
677 END IF;
678 x_return_status := l_return_status;
679
680 EXCEPTION
681 -- CHECK the code in all the exception sections
682 WHEN FND_API.G_EXC_ERROR THEN
683 x_return_status := FND_API.G_RET_STS_ERROR;
684
685 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
686 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
687
688 WHEN OTHERS THEN
689 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
690 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
691 THEN
692 FND_MSG_PUB.Add_Exc_Msg
693 ( G_PKG_NAME
694 , 'Update_Pull_sequence'
695 );
696 END IF;
697 End Update_Pull_sequence_tbl;
698
699 --
700 --
701 --
702 -- Update_Card_Supply_Status : This procedure updates the supply status
703 -- for a kanban card id record. If the supply
704 -- status is Inprocess the document details
705 -- are also captured.
706 --
707 --
708 PROCEDURE Update_Card_Supply_Status(X_Return_Status Out NOCOPY Varchar2,
709 p_Kanban_Card_Id Number,
710 p_Supply_Status Number,
711 p_Document_type Number,
712 p_Document_Header_Id Number,
713 p_Document_detail_Id NUMBER,
714 p_replenish_quantity NUMBER,
715 p_need_by_date DATE,
716 p_source_wip_entity_id NUMBER)
717
718 IS
719 l_kanban_card_rec INV_Kanban_PVT.Kanban_Card_Rec_Type;
720 l_return_status Varchar2(1) := FND_API.G_RET_STS_SUCCESS;
721 l_supply_status_from Varchar2(30);
722 l_supply_status_to Varchar2(30);
723 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
724 l_temp NUMBER := NULL;
725 l_update Boolean := TRUE; -- For Bug 3740514
726 l_return_msg varchar2(200);
727 l_return_msg_code number;
728 Begin
729 -- added for eKanban project.
730 mydebug('In INV_KANBAN_PVT.Update_Card_Supply_Status api - 5');
731 if ( nvl(fnd_profile.VALUE('FLM_EKB_OPS_FLAG'),0) = 1 AND p_Supply_Status = INV_KANBAN_PVT.G_Supply_Status_Full ) then
732 mydebug('calling flm_kanbancard_pub.change_card_status_full api: replenish_quantity='||p_replenish_quantity||', p_Supply_Status='||p_Supply_Status);
733 FLM_KANBANCARD_PUB.CHANGE_CARD_STATUS_FULL(p_Kanban_Card_Id => p_Kanban_Card_Id,
734 p_replenish_quantity => p_replenish_quantity,
735 X_ERR_MSG => l_return_msg,
736 X_RET_MSG_CODE => l_return_msg_code,
737 X_RETCODE => l_return_status);
738 -- end - eKanban project
739 -- continue with existing code if eKanban profile is not enabled.
740 else
741 FND_MSG_PUB.initialize;
742
743 IF p_Kanban_Card_Id is NULL THEN
744 FND_MESSAGE.SET_NAME('INV','INV_ATTRIBUTE_REQUIRED');
745 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','INV_KANBAN_CARD');
746 FND_MSG_PUB.Add;
747 l_return_status := FND_API.G_RET_STS_ERROR;
748 ELSIF p_supply_status is null THEN
749 FND_MESSAGE.SET_NAME('INV','INV_ATTRIBUTE_REQUIRED');
750 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','INV_SUPPLY_STATUS');
751 FND_MSG_PUB.Add;
752 l_return_status := FND_API.G_RET_STS_ERROR;
753 ELSE
754
755 l_kanban_card_rec := INV_KanbanCard_PKG.Query_Row
756 ( p_Kanban_Card_id => p_kanban_Card_Id);
757
758 --Bug 3288422 fix. Preventing replenishment 1) If lock cannot be
759 --acquired on the kanban_card_record 2) if the card is in hold
760 --OR cancel status
761 BEGIN
762 SELECT kanban_card_id
763 INTO l_temp
764 FROM MTL_KANBAN_CARDS
765 WHERE kanban_card_id = p_kanban_Card_Id
766 FOR UPDATE NOWAIT;
767 mydebug('Lock accuired for kanban card');
768 EXCEPTION
769 WHEN OTHERS THEN
770 l_return_status := FND_API.G_RET_STS_ERROR;
771 FND_MESSAGE.SET_NAME('INV','INV_CANNOT_LOCK_KANBAN_CARD');
772 FND_MSG_PUB.ADD;
773 END;
774
775 IF l_return_status = FND_API.g_ret_sts_error THEN
776 NULL;
777 ELSIF (l_kanban_card_rec.card_status = G_Card_Status_Hold ) THEN
778 l_return_status := FND_API.G_RET_STS_ERROR;
779 FND_MESSAGE.SET_NAME('INV','INV_CANNOT_REPL_HOLD_CARD');
780 FND_MSG_PUB.ADD;
781 ELSIF (l_kanban_card_rec.card_status = G_Card_Status_Cancel ) then
782 l_return_status := FND_API.G_RET_STS_ERROR;
783 FND_MESSAGE.SET_NAME('INV','INV_CANNOT_REPL_CANCEL_CARD');
784 FND_MSG_PUB.ADD;
785 ELSIF (l_kanban_card_rec.card_status = G_Card_Status_Planned ) then
786 l_return_status := FND_API.G_RET_STS_ERROR;
787 FND_MESSAGE.SET_NAME('INV','INV_CANNOT_REPL_CANCEL_CARD');
788 FND_MSG_PUB.ADD;
789 --Bug 3288422 fix.
790 ELSE
791 IF INV_KanbanCard_PKG.Supply_Status_Change_OK
792 (trunc(l_kanban_card_rec.supply_status),
793 p_supply_status,
794 l_kanban_card_rec.card_status)
795 THEN
796 /*Bug 3740514 --If the supply status is Full and the source is Supplier,calling
797 a new procedure update_card_and_card_status to check if the correct
798 Release is being updated.*/
799
800 IF ( p_supply_status IN (INV_Kanban_PVT.G_Supply_Status_InProcess,INV_Kanban_PVT.G_Supply_Status_Full) AND
801 l_Kanban_Card_Rec.source_type = INV_Kanban_PVT.G_Source_Type_Supplier) THEN
802 update_card_and_card_status(
803 p_kanban_card_id => l_kanban_card_rec.kanban_card_id,
804 p_supply_status => p_supply_status,/*4490269*/
805 p_document_header_id => p_document_header_id, /*Bug#7133795*/
806 p_document_detail_id => p_document_detail_id, /*Bug#7133795*/
807 p_update => l_update);
808 END IF;
809
810 /*Bug 3740514--Only if l_update is TRUE will the kanban card details be updated to the
811 new values.*/
812
813 IF (l_update) THEN
814 mydebug('Supply status change OK');
815 l_kanban_card_rec.supply_status := p_supply_status;
816 l_kanban_card_rec.document_type := p_document_type;
817 l_kanban_card_rec.document_header_id := p_document_header_id;
818 l_kanban_card_rec.document_detail_id := p_document_detail_id;
819 l_kanban_card_rec.last_update_date := SYSDATE;
820 l_kanban_card_rec.last_updated_by := FND_GLOBAL.USER_ID;
821 l_kanban_card_rec.last_update_login := FND_GLOBAL.LOGIN_ID;
822 l_kanban_card_rec.replenish_quantity := p_replenish_quantity;
823 l_kanban_card_rec.need_by_date := p_need_by_date;
824 l_kanban_card_rec.source_wip_entity_id := p_source_wip_entity_id;
825 mydebug('calling INV_KanbanCard_PKG.Update_Row');
826 INV_KanbanCard_PKG.Update_Row(l_kanban_card_rec);
827 END IF;
828 ELSE
829 mydebug('Supply status change not OK');
830 If l_kanban_card_rec.card_status in
831 (INV_Kanban_PVT.G_Card_Status_Cancel,
832 INV_Kanban_PVT.G_Card_Status_Hold,INV_Kanban_PVT.G_Card_Status_Planned)
833 then
834 FND_MESSAGE.SET_TOKEN('CARD_NUMBER',l_kanban_card_rec.kanban_card_number);
835 End If;
836 l_return_status := FND_API.G_RET_STS_ERROR;
837 FND_MSG_PUB.Add;
838 END IF;--IF INV_KanbanCard_PKG.Supply_Status_Change_OK
839 END IF;--IF l_return_status = FND_API.g_ret_sts_error
840 END IF;--IF p_Kanban_Card_Id is NULL THEN
841
842 IF l_return_status = FND_API.g_ret_sts_error THEN
843 Raise FND_API.G_EXC_ERROR;
844 end if;
845
846 end if;
847 x_return_status := l_return_status;
848
849 EXCEPTION
850
851 WHEN FND_API.G_EXC_ERROR THEN
852
853 x_return_status := FND_API.G_RET_STS_ERROR;
854
855 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
856
857 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
858
859 WHEN OTHERS THEN
860
861 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
862 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
863 THEN
864 FND_MSG_PUB.Add_Exc_Msg
865 ( G_PKG_NAME
866 , 'Update_Card_Supply_Status'
867 );
868 END IF;
869
870 End Update_Card_Supply_Status;
871 --
872 --
873 -- Update_Card_Supply_Status : This procedure updates the supply status
874 -- for a kanban card id record.
875 --
876 --
877 PROCEDURE Update_Card_Supply_Status(X_Return_Status Out NOCOPY Varchar2,
878 p_Kanban_card_Id Number,
879 p_Supply_Status Number)
880 IS
881 l_document_type Number;
882 l_document_header_id Number;
883 l_Document_detail_id Number;
884
885 l_quantity_delivered Number;
886 l_quantity Number;
887 l_reference_type_code Number;
888 l_move_order_line_id Number;
889 l_Supply_Status Number;
890
891 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
892 Begin
893 mydebug('In INV_KANBAN_PVT.Update_Card_Supply_Status api - 4');
894 l_Supply_Status := p_Supply_Status;
895
896 -- Bug 3032156. The following IF condition has been added to keep the
897 -- supply status to 'Full' while partially transacting the move order.
898 -- The status changes to 'InProcess if 'INV_FILL_PARTIAL_MOVE_KANBAN'
899 -- profile value is set to 'No' and the IF conditions are satisfied.
900
901 if nvl(fnd_profile.value('INV_FILL_PARTIAL_MOVE_KANBAN'),2) = 2 then
902 -- Bug 2383538 Checking the mtl_txn_request_lines when partially
903 -- transacting the move order to change the supply status to Inprocess
904 Begin
905 select line_id, nvl(quantity,0), nvl(reference_type_code,0)
906 into l_move_order_line_id, l_quantity, l_reference_type_code
907 from mtl_txn_request_lines
908 where reference_type_code = 1
909 and reference_id = p_Kanban_card_Id
910 and line_status in (3,7);
911 if (l_reference_type_code = 1) then
912 select sum(abs(transaction_quantity)) into l_quantity_delivered from
913 mtl_material_transactions where
914 move_order_line_id = l_move_order_line_id and
915 transaction_quantity < 0;
916 if (nvl(l_quantity_delivered,0) < l_quantity) then
917 l_Supply_Status := INV_KANBAN_PVT.G_Supply_Status_InProcess;
918 end if;
919 end if;
920 Exception When Others Then
921 Null;
922 End;
923 End if;
924
925 Update_Card_Supply_Status(X_Return_Status => x_Return_Status,
926 p_kanban_card_Id => p_Kanban_Card_Id,
927 p_Supply_Status => l_Supply_Status,
928 p_Document_type => l_document_type,
929 p_Document_Header_Id => l_document_header_id,
930 p_Document_detail_Id => l_Document_detail_id);
931
932
933 EXCEPTION
934
935 WHEN FND_API.G_EXC_ERROR THEN
936
937 x_return_status := FND_API.G_RET_STS_ERROR;
938
939 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
940
941 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
942
943 WHEN OTHERS THEN
944
945 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
946 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
947 THEN
948 FND_MSG_PUB.Add_Exc_Msg
949 ( G_PKG_NAME
950 , 'Update_Card_Supply_Status'
951 );
952 END IF;
953
954 End Update_Card_Supply_Status;
955 --
956 --
957 -- Update_Card_Supply_Status : This procedure updates the supply status
958 -- for a kanban card id record.
959 --
960 PROCEDURE Update_Card_Supply_Status
961 ( p_api_version_number IN NUMBER
962 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
963 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
964 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
965 ,x_return_status OUT NOCOPY VARCHAR2
966 ,x_msg_count OUT NOCOPY NUMBER
967 ,x_msg_data OUT NOCOPY VARCHAR2
968 ,p_Kanban_Card_Id Number
969 ,p_Supply_Status NUMBER
970 ,p_Document_type IN NUMBER DEFAULT NULL
971 ,p_Document_Header_Id IN NUMBER DEFAULT NULL
972 ,p_Document_detail_Id IN NUMBER DEFAULT NULL
973 ,p_replenish_quantity IN NUMBER DEFAULT NULL
974 ,p_need_by_date IN DATE DEFAULT NULL
975 ,p_source_wip_entity_id IN NUMBER DEFAULT NULL)
976 IS
977
978 l_api_version_number CONSTANT NUMBER := 1.0;
979 l_api_name CONSTANT VARCHAR2(30):= 'Update_Card_Supply_Status';
980 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
981 l_document_type Number;
982 l_document_header_id Number;
983 l_document_detail_id Number;
984
985 l_msg_data VARCHAR2(255);
986 l_msg_count NUMBER := NULL;
987
988 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
989 Begin
990 mydebug('In INV_KANBAN_PVT.Update_Card_Supply_Status api - 3');
991 -- Standard Start of API savepoint
992
993 SAVEPOINT KANBAN_PVT;
994 mydebug('Inside Update_Card_Supply_Status 1');
995 -- Standard call to check for call compatibility
996
997 IF NOT FND_API.Compatible_API_Call
998 ( l_api_version_number
999 , p_api_version_number
1000 , l_api_name
1001 , G_PKG_NAME
1002 )
1003 THEN
1004 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1005 END IF;
1006
1007 -- Initialize message list.
1008
1009 IF FND_API.to_Boolean(p_init_msg_list) THEN
1010 FND_MSG_PUB.initialize;
1011 END IF;
1012
1013 mydebug('Calling Update_Card_Supply_Status 2');
1014 Update_Card_Supply_Status(X_Return_Status => l_Return_Status,
1015 p_kanban_card_Id => p_Kanban_Card_Id,
1016 p_Supply_Status => p_Supply_Status,
1017 p_Document_type => l_document_type,
1018 p_Document_Header_Id => l_document_header_id,
1019 p_Document_detail_Id => l_document_detail_id,
1020 p_replenish_quantity => p_replenish_quantity,
1021 p_need_by_date => p_need_by_date,
1022 p_source_wip_entity_id => p_source_wip_entity_id);
1023
1024 x_return_status := l_return_status;
1025
1026 -- Standard check of p_commit.
1027
1028 IF FND_API.To_Boolean( p_commit ) THEN
1029 COMMIT WORK;
1030 END IF;
1031
1032 -- Standard call to get message count and if count is 1, get message info.
1033 FND_MSG_PUB.Count_And_Get
1034 ( p_count => x_msg_count ,
1035 p_data => x_msg_data
1036 );
1037
1038 EXCEPTION
1039
1040 WHEN FND_API.G_EXC_ERROR THEN
1041 ROLLBACK TO KANBAN_PVT;
1042 x_return_status := FND_API.G_RET_STS_ERROR ;
1043 FND_MSG_PUB.Count_And_Get
1044 ( p_count => x_msg_count ,
1045 p_data => x_msg_data
1046 );
1047 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1048 ROLLBACK TO KANBAN_PVT;
1049 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1050 FND_MSG_PUB.Count_And_Get
1051 ( p_count => x_msg_count ,
1052 p_data => x_msg_data
1053 );
1054
1055 WHEN OTHERS THEN
1056 ROLLBACK TO KANBAN_PVT;
1057 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1058 IF FND_MSG_PUB.Check_Msg_Level
1059 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1060 THEN
1061 FND_MSG_PUB.Add_Exc_Msg
1062 ( G_PKG_NAME ,
1063 l_api_name
1064 );
1065 END IF;
1066 FND_MSG_PUB.Count_And_Get
1067 ( p_count => x_msg_count ,
1068 p_data => x_msg_data
1069 );
1070
1071 End Update_Card_Supply_Status;
1072 --
1073 --
1074 -- This package is for those cards which do not have Document_detail_Id.
1075 --
1076 --
1077 PROCEDURE Update_Card_Supply_Status(X_Return_Status Out NOCOPY Varchar2,
1078 p_Kanban_Card_Id Number,
1079 p_Supply_Status Number,
1080 p_Document_type Number,
1081 p_Document_Header_Id Number)
1082 IS
1083 l_Document_detail_id Number := FND_API.G_MISS_NUM;
1084
1085 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1086 Begin
1087 mydebug('In INV_KANBAN_PVT.Update_Card_Supply_Status api - 2');
1088 Update_Card_Supply_Status(X_Return_Status => x_Return_Status,
1089 p_kanban_card_Id => p_Kanban_Card_Id,
1090 p_Supply_Status => p_Supply_Status,
1091 p_Document_type => p_document_type,
1092 p_Document_Header_Id => p_document_header_id,
1093 p_Document_detail_Id => l_Document_detail_id);
1094
1095
1096 EXCEPTION
1097
1098 WHEN FND_API.G_EXC_ERROR THEN
1099
1100 x_return_status := FND_API.G_RET_STS_ERROR;
1101
1102 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1103
1104 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1105
1106 WHEN OTHERS THEN
1107
1108 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1109 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1110 THEN
1111 FND_MSG_PUB.Add_Exc_Msg
1112 ( G_PKG_NAME
1113 , 'Update_Card_Supply_Status'
1114 );
1115 END IF;
1116
1117 End Update_Card_Supply_Status;
1118 --
1119 --
1120 -- Valid_Kanban_Cards_Exist : This procedure checks whether "Active" or "Hold"
1121 -- Kanban cards exists for a pull sequence.
1122 --
1123 FUNCTION Valid_Kanban_Cards_Exist(p_Pull_sequence_id number)
1124 Return Boolean
1125 Is
1126 l_dummy varchar2(1);
1127
1128 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1129 BEGIN
1130
1131 Select 'x'
1132 INTO l_dummy
1133 FROM MTL_KANBAN_CARDS
1134 WHERE pull_sequence_id = p_pull_sequence_id
1135 AND (card_status = INV_Kanban_PVT.G_Card_Status_Active or
1136 card_status = INV_Kanban_PVT.G_Card_Status_Hold);
1137 Raise Too_Many_Rows;
1138
1139 Exception
1140 When No_data_found Then
1141 return FALSE;
1142
1143 When Too_many_rows Then
1144 return TRUE;
1145 When Others Then
1146
1147 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1148 THEN
1149 FND_MSG_PUB.Add_Exc_Msg
1150 ( G_PKG_NAME
1151 , 'Valid_kanban_Cards_exist'
1152 );
1153 END IF;
1154
1155 END Valid_kanban_Cards_exist;
1156 --
1157 -- Diff_Qty_Kanban_Cards_Exist : Check the existence of valid kanban cards for
1158 -- a pull sequence, which have the same Point of Supply
1159 -- but different quantity
1160 --
1161 -- For bug 5334353, changed return type of function to number
1162 -- Retunns 0 : When card with supply status Wait or Inprocess exists
1163 -- Returns 1 : When card with diff qty exists
1164 -- Returns 2: When both 0 and 1 conditions does not satisfy
1165 FUNCTION Diff_Qty_Kanban_Cards_Exist(
1166 p_pull_sequence_id number,
1167 p_source_type number,
1168 p_supplier_id number,
1169 p_supplier_site_id number,
1170 p_source_organization_id number,
1171 p_source_subinventory varchar2,
1172 p_source_locator_id number,
1173 p_wip_line_id number,
1174 p_kanban_size number)
1175 Return Number
1176 Is
1177 l_dummy varchar2(1);
1178 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1179 BEGIN
1180 Select 'x'
1181 INTO l_dummy
1182 FROM MTL_KANBAN_CARDS
1183 WHERE pull_sequence_id = p_pull_sequence_id
1184 AND (card_status = INV_Kanban_PVT.G_Card_Status_Active or
1185 card_status = INV_Kanban_PVT.G_Card_Status_Hold)
1186 -- AND nvl(p_kanban_size,-1) > 0
1187 -- AND kanban_size <> nvl(p_kanban_size,-1)
1188 AND source_type = nvl(p_source_type,-1)
1189 AND (((source_type = 1 or source_type = 3)
1190 and nvl(source_organization_id,-1) = nvl(p_source_organization_id,-1)
1191 and nvl(source_subinventory,'#?#?') = nvl(p_source_subinventory,'#?#?')
1192 and nvl(source_locator_id,-1) = nvl(p_source_locator_id,-1))
1193 OR
1194 (source_type = 2
1195 and ((nvl(supplier_id,-1) = nvl(p_supplier_id,-1)
1196 and nvl(supplier_site_id,-1) = nvl(p_supplier_site_id,-1))
1197 or p_supplier_id is null))
1198 OR
1199 (source_type = 4
1200 and (nvl(wip_line_id,-1) = nvl(p_wip_line_id,-1)
1201 or p_wip_line_id is null))
1202 )
1203 AND supply_status in (3,5) --sbitra
1204 ;
1205 Raise Too_Many_Rows;
1206 Exception
1207 When No_data_found Then
1208 begin
1209 Select 'x'
1210 INTO l_dummy
1211 FROM MTL_KANBAN_CARDS
1212 WHERE pull_sequence_id = p_pull_sequence_id
1213 AND (card_status = INV_Kanban_PVT.G_Card_Status_Active or
1214 card_status = INV_Kanban_PVT.G_Card_Status_Hold)
1215 AND nvl(p_kanban_size,-1) > 0
1216 AND kanban_size <> nvl(p_kanban_size,-1)
1217 AND source_type = nvl(p_source_type,-1)
1218 AND (((source_type = 1 or source_type = 3)
1219 and nvl(source_organization_id,-1) = nvl(p_source_organization_id,-1)
1220 and nvl(source_subinventory,'#?#?') = nvl(p_source_subinventory,'#?#?')
1221 and nvl(source_locator_id,-1) = nvl(p_source_locator_id,-1))
1222 OR
1223 (source_type = 2
1224 and ((nvl(supplier_id,-1) = nvl(p_supplier_id,-1)
1225 and nvl(supplier_site_id,-1) = nvl(p_supplier_site_id,-1))
1226 or p_supplier_id is null))
1227 OR
1228 (source_type = 4
1229 and (nvl(wip_line_id,-1) = nvl(p_wip_line_id,-1)
1230 or p_wip_line_id is null))
1231 )
1232 ;
1233 Raise Too_Many_Rows;
1234 exception
1235 when no_data_found then
1236 return 2; -----No cards with status wait/inprocess and diff qty
1237 when too_many_rows then
1238 return 1; -----Cards exists with diff qty
1239 end;
1240
1241 When Too_many_rows Then
1242 return 0; -------Cards with status wait/inprocess exists
1243 When Others Then
1244 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1245 THEN
1246 FND_MSG_PUB.Add_Exc_Msg
1247 ( G_PKG_NAME
1248 , 'Diff_Qty_Kanban_Cards_Exist'
1249 );
1250 END IF;
1251
1252 END Diff_Qty_Kanban_Cards_Exist;
1253 --
1254 --
1255 -- Ok_To_Create_Kanban_Cards : This procedure checks whether kanban cards can
1256 -- be generated for a pull sequences.
1257 --
1258 --
1259 FUNCTION Ok_To_Create_Kanban_Cards(p_Pull_sequence_id number)
1260 Return Boolean
1261 IS
1262
1263 l_org_code varchar2(3);
1264 l_item_name varchar2(100);
1265 l_subinventory varchar2(10);
1266 l_loc_name varchar2(100);
1267
1268 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1269 BEGIN
1270
1271 if Valid_Kanban_Cards_exist(p_pull_sequence_id)
1272 then
1273 Get_Pull_sequence_Tokens(p_pull_sequence_id,l_org_code,
1274 l_item_name,l_subinventory,l_loc_name);
1275 FND_MESSAGE.SET_NAME('INV','INV_CANT_GEN_CRDS_CARDS_EXIST');
1276 FND_MESSAGE.SET_TOKEN('ORG_CODE',l_org_code);
1277 FND_MESSAGE.SET_TOKEN('ITEM_NAME',l_item_name);
1278 FND_MESSAGE.SET_TOKEN('SUB_CODE',l_subinventory);
1279 FND_MESSAGE.SET_TOKEN('LOCATOR_NAME',l_loc_name);
1280 return false;
1281 end if;
1282 return TRUE;
1283
1284 END Ok_To_Create_Kanban_Cards;
1285 --
1286 --
1287 -- Ok_To_Delete_Pull_Sequence : This procedure checks whether a pull
1288 -- sequence can be deleted.
1289 --
1290 --
1291 FUNCTION Ok_To_Delete_Pull_Sequence(p_Pull_sequence_id number)
1292 RETURN BOOLEAN
1293 IS
1294
1295 l_org_code varchar2(3);
1296 l_item_name varchar2(100);
1297 l_subinventory varchar2(10);
1298 l_loc_name varchar2(100);
1299
1300 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1301 BEGIN
1302 if Valid_Kanban_Cards_exist(p_pull_sequence_id)
1303 then
1304 Get_Pull_sequence_Tokens(p_pull_sequence_id,l_org_code,
1305 l_item_name,l_subinventory,l_loc_name);
1306 FND_MESSAGE.SET_NAME('INV','INV_CANNOT_DELETE_PULLSEQ');
1307 FND_MESSAGE.SET_TOKEN('ORG_CODE',l_org_code);
1308 FND_MESSAGE.SET_TOKEN('ITEM_NAME',l_item_name);
1309 FND_MESSAGE.SET_TOKEN('SUB_CODE',l_subinventory);
1310 FND_MESSAGE.SET_TOKEN('LOCATOR_NAME',l_loc_name);
1311 return false;
1312 end if;
1313 return TRUE;
1314
1315 END Ok_To_Delete_Pull_sequence;
1316
1317 --
1318 --
1319 -- Get_Kanban_Tokens : This procedure gets the names required to
1320 -- build the message for Kanban Validation.
1321 --
1322 PROCEDURE Get_Kanban_Tokens
1323 ( p_kanban_id Number,
1324 p_org_id Number,
1325 p_item_id Number,
1326 p_loc_id Number,
1327 x_org_code Out NOCOPY varchar2,
1328 x_item_name Out NOCOPY varchar2,
1329 x_loc_name Out NOCOPY varchar2,
1330 x_kanban_num Out NOCOPY varchar2 )
1331 IS
1332
1333 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1334 Begin
1335 Select concatenated_segments,organization_code,kanban_card_number
1336 into x_item_name,x_org_code,x_kanban_num
1337 from mtl_system_items_kfv a , mtl_parameters b, mtl_kanban_cards card
1338 where a.inventory_item_id = p_item_id
1339 and a.organization_id = p_org_id
1340 and b.organization_id = p_org_id
1341 and card.kanban_card_id = p_kanban_id;
1342
1343 if ( nvl(p_loc_id,0) <> 0 ) Then
1344
1345 Select concatenated_segments
1346 into x_loc_name
1347 from mtl_item_locations_kfv
1348 where inventory_location_id = p_loc_id
1349 and organization_id = p_loc_id;
1350 end if;
1351 Exception
1352
1353 When Others Then
1354 Null;
1355
1356 End Get_kanban_Tokens;
1357
1358 --
1359 -- Valid_Production_Kanban_Card : This function will check the validity of
1360 -- of a production kanban Card.
1361 --
1362 FUNCTION Valid_Production_Kanban_Card( p_wip_entity_id number,
1363 p_org_id number,
1364 p_kanban_id number,
1365 p_inv_item_id number,
1366 p_subinventory varchar2,
1367 p_locator_id number )
1368 Return Boolean IS
1369 l_kanban_card_id number;
1370 l_dummy varchar2(1);
1371 l_proceed varchar2(1);
1372 l_subinventory varchar2(10);
1373 l_loc_id number;
1374 l_item_id number;
1375 l_org_id number;
1376 l_source_type number;
1377 l_supply_status number;
1378 x_org_code varchar2(3);
1379 x_item_name varchar2(200);
1380 x_loc_name varchar2(200);
1381 x_kanban_num varchar2(30);
1382
1383 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1384 BEGIN
1385 begin
1386 select subinventory_name, nvl(locator_id,0), inventory_item_id,
1387 organization_id, source_type, supply_status
1388 into l_subinventory, l_loc_id, l_item_id,
1389 l_org_id, l_source_type, l_supply_status
1390 from mtl_kanban_cards
1391 where kanban_card_id = p_kanban_id;
1392 exception
1393 When NO_DATA_FOUND then
1394 FND_MESSAGE.SET_NAME('INV','INV_KANBAN_CARD_NOT_FOUND');
1395 FND_MESSAGE.SET_TOKEN('CARDID',to_char(p_kanban_id) );
1396 RETURN FALSE;
1397 When OTHERS then
1398 FND_MSG_PUB.Add_Exc_Msg
1399 ( G_PKG_NAME
1400 , 'Valid_Production_Kanban_Card'
1401 );
1402 RETURN FALSE;
1403 end;
1404
1405 Get_Kanban_Tokens(p_kanban_id , p_org_id , p_inv_item_id ,
1406 p_locator_id , x_org_code , x_item_name ,
1407 x_loc_name , x_kanban_num );
1408
1409 if ( (l_item_id = p_inv_item_id) AND (l_org_id = p_org_id) ) then
1410 l_proceed := 'Y';
1411 else
1412 FND_MESSAGE.SET_NAME('INV','INV_KANBAN_INVALID_ITEM_ORG');
1413 FND_MESSAGE.SET_TOKEN('CARD_NUM',x_kanban_num );
1414 FND_MESSAGE.SET_TOKEN('ORG_CODE',x_org_code );
1415 FND_MESSAGE.SET_TOKEN('ITEM',x_item_name );
1416 RETURN FALSE;
1417 end if;
1418 if ( ( l_subinventory = p_subinventory ) AND
1419 ( l_loc_id = nvl(p_locator_id,l_loc_id)) ) then
1420 l_proceed := 'Y';
1421 else
1422 FND_MESSAGE.SET_NAME('INV','INV_KANBAN_INVALID_CMPL_DEST');
1423 FND_MESSAGE.SET_TOKEN('CARD_NUM',x_kanban_num );
1424 FND_MESSAGE.SET_TOKEN('SUB',p_subinventory);
1425 FND_MESSAGE.SET_TOKEN('LOC',x_loc_name);
1426 RETURN FALSE;
1427 end if;
1428 if ( l_source_type = INV_KANBAN_PVT.G_Source_Type_Production ) then
1429 l_proceed := 'Y';
1430 else
1431 FND_MESSAGE.SET_NAME('INV','INV_KANBAN_NOT_PRODUCTION');
1432 FND_MESSAGE.SET_TOKEN('CARD_NUM',x_kanban_num );
1433 RETURN FALSE;
1434 end if;
1435 if ( l_supply_status in (INV_KANBAN_PVT.G_Supply_Status_Empty,
1436 INV_KANBAN_PVT.G_Supply_Status_InProcess ) ) then
1437 Return TRUE;
1438 else
1439 begin
1440 select 'x' into l_dummy
1441 from mtl_kanban_card_activity
1442 where kanban_card_id = p_kanban_id
1443 and organization_id = p_org_id
1444 and document_header_id = p_wip_entity_id
1445 and source_type = INV_KANBAN_PVT.G_Source_Type_Production
1446 and supply_status = INV_KANBAN_PVT.G_Supply_Status_Full;
1447
1448 Raise Too_many_rows;
1449
1450 exception
1451 When No_data_found Then
1452 FND_MESSAGE.SET_NAME('INV','INV_KANBAN_INVALID_SUP_STATUS');
1453 FND_MESSAGE.SET_TOKEN('CARD_NUM',x_kanban_num );
1454 return FALSE;
1455 When Too_many_rows Then
1456 return TRUE;
1457 When Others Then
1458 FND_MSG_PUB.Add_Exc_Msg
1459 ( G_PKG_NAME
1460 , 'Valid_Prod_kanban_Card'
1461 );
1462 return FALSE;
1463 end ;
1464 end if;
1465
1466 END Valid_Production_Kanban_Card;
1467
1468 --
1469 --
1470 -- Delete_Kanban_Cards : This procedure deletes kanban cards for
1471 -- a pull sequence.
1472 --
1473 --
1474 PROCEDURE Delete_Kanban_Cards(p_Pull_sequence_id number)
1475 IS
1476 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1477 BEGIN
1478
1479 INV_KanbanCard_PKG.Delete_Cards_For_Pull_Seq(p_pull_sequence_id);
1480
1481 END Delete_Kanban_Cards;
1482 --
1483 --
1484 -- Create_Kanban_Cards : This procedure generates kanban cards for
1485 -- a pull sequence.
1486 --
1487 --
1488 PROCEDURE Create_Kanban_Cards
1489 ( X_return_status OUT NOCOPY VARCHAR2,
1490 X_Kanban_Card_Ids OUT NOCOPY INV_Kanban_PVT.Kanban_Card_Id_Tbl_Type,
1491 P_Pull_Sequence_Rec INV_Kanban_PVT.Pull_Sequence_Rec_Type,
1492 p_Supply_Status NUMBER
1493 )
1494 IS
1495 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
1496 l_kanban_Card_tbl INV_Kanban_PVT.Kanban_Card_Id_Tbl_Type;
1497 l_Kanban_Card_Id number;
1498 l_Kanban_Card_Number varchar2(200);
1499 l_supply_status Number;
1500 l_Card_status Number;
1501 l_Current_Replnsh_Cycle_Id Number;
1502 l_card_count number := 0;
1503 l_item_name varchar2(2000);
1504 l_loc_name varchar2(2000);
1505 l_subinventory varchar2(10);
1506 l_org_code varchar2(3);
1507 l_error_msg VARCHAR2(4000);
1508 /*declared by javakat to fix 11800873 from number to varcharto default supplier and supplier_site_id*/
1509 l_supplier_id NUMBER;
1510 l_supp_site_id NUMBER;
1511 l_temp NUMBER;
1512 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1513 BEGIN
1514
1515 FND_MSG_PUB.initialize;
1516 l_kanban_Card_tbl.Delete;
1517
1518
1519
1520
1521 IF(p_pull_sequence_rec.release_kanban_flag =2) THEN
1522 l_card_status := INV_Kanban_Pvt.G_Card_Status_Hold;
1523 l_supply_status := inv_kanban_pvt.g_supply_status_full;
1524 ELSE
1525 l_card_status := INV_Kanban_Pvt.G_Card_Status_Active;
1526 l_supply_status := p_Supply_Status;
1527 END IF ;
1528
1529 l_Current_Replnsh_Cycle_Id := null;
1530
1531 if nvl(P_Pull_Sequence_Rec.NUMBER_OF_CARDS,0) <= 0
1532 then
1533
1534 Get_Pull_sequence_Tokens(p_pull_sequence_rec.pull_sequence_id,l_org_code,
1535 l_item_name,l_subinventory,l_loc_name);
1536 FND_MESSAGE.SET_NAME('INV','INV_CANT_GEN_CRDS_NO_NUM_CARDS');
1537 FND_MESSAGE.SET_TOKEN('ORG_CODE',l_org_code);
1538 FND_MESSAGE.SET_TOKEN('ITEM_NAME',l_item_name);
1539 FND_MESSAGE.SET_TOKEN('SUB_CODE',l_Subinventory);
1540 FND_MESSAGE.SET_TOKEN('LOCATOR_NAME',l_loc_name);
1541 FND_MSG_PUB.Add;
1542
1543 l_return_status := FND_API.G_RET_STS_ERROR;
1544
1545 end if;
1546
1547 if nvl(P_Pull_Sequence_Rec.Kanban_Size,0) <= 0
1548 then
1549 if l_org_code is null
1550 then
1551 Get_Pull_sequence_Tokens(p_pull_sequence_rec.pull_sequence_id,l_org_code,
1552 l_item_name,l_subinventory,l_loc_name);
1553 end if;
1554 FND_MESSAGE.SET_NAME('INV','INV_CANT_GEN_CRDS_NO_KBN_SIZE');
1555 FND_MESSAGE.SET_TOKEN('ORG_CODE',l_org_code);
1556 FND_MESSAGE.SET_TOKEN('ITEM_NAME',l_item_name);
1557 FND_MESSAGE.SET_TOKEN('SUB_CODE',l_Subinventory);
1558 FND_MESSAGE.SET_TOKEN('LOCATOR_NAME',l_loc_name);
1559 FND_MSG_PUB.Add;
1560 l_return_status := FND_API.G_RET_STS_ERROR;
1561
1562 end if;
1563
1564 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1565 RAISE FND_API.G_EXC_ERROR;
1566
1567 END IF;
1568
1569 --Existing kanban cards need to be cancelled only when creating a
1570 --regular replenishable card
1571 if ( nvl(fnd_profile.VALUE('FLM_EKB_OPS_FLAG'),0) = 0 )then
1572 IF p_pull_sequence_rec.kanban_card_type <>
1573 INV_Kanban_Pvt.g_card_type_nonreplenishable then
1574 -- we should not delete old kanban cards but change their status
1575 -- for historical purposes.
1576 --Delete_Kanban_Cards(p_Pull_Sequence_Rec.Pull_Sequence_Id);
1577 update_kanban_card_status
1578 (p_card_status => g_card_status_cancel,
1579 p_pull_sequence_id => p_Pull_Sequence_Rec.pull_sequence_id);
1580 X_Kanban_Card_Ids := l_kanban_Card_tbl;
1581 END IF;
1582 end if;
1583
1584 for l_card_count in 1..p_Pull_Sequence_Rec.Number_Of_Cards
1585 loop
1586
1587 l_Kanban_Card_Id := NULL;
1588 l_Kanban_Card_Number := NULL;
1589 /*added by javakat to fix 11800873 */
1590
1591 If NVL(FND_PROFILE.VALUE('FLM_EKB_OPS_FLAG'),0)=INVKBCGN.flm_ekb_yes THEN
1592 IF (l_debug = 1 ) THEN
1593 INV_LOG_UTIL.TRACE('FLM: Enable E-Kanban is yes','INV_KANBAN_PVT.Create_Kanban_Cards');
1594 INV_LOG_UTIL.TRACE('Calling FLM_MULTIPLE_SUPPLIERS.GET_SUPPLIER','INV_KANBAN_PVT.Create_Kanban_Cards');
1595 END IF;
1596 FLM_MULTIPLE_SUPPLIERS.GET_SUPPLIER(p_pull_seq_id => p_Pull_Sequence_Rec.Pull_Sequence_Id,
1597 p_org_id => p_Pull_Sequence_Rec.Organization_id,
1598 p_cardstatus => l_Card_Status,
1599 x_supplier_id => l_supplier_id,
1600 x_supplier_site_id => l_supp_site_id,
1601 x_retcode => l_return_status,
1602 x_err_msg => l_error_msg
1603 );
1604
1605 -- Default it from Custom Hook
1606 if l_Kanban_Card_Number is null then
1607 IF (l_debug = 1 ) THEN
1608 INV_LOG_UTIL.TRACE('Calling FLM_KANBAN_CUSTOM_PKG.custom_kanban_number','INV_KANBAN_PVT.Create_Kanban_Cards');
1609 END IF;
1610 l_Kanban_Card_Number := FLM_KANBAN_CUSTOM_PKG.custom_kanban_number(p_Pull_Sequence_Rec.Pull_Sequence_Id);
1611 if l_Kanban_Card_Number IS NOT NULL THEN
1612 Begin
1613 IF (l_debug = 1 ) THEN
1614 INV_LOG_UTIL.TRACE('validating kanban_number','INV_KANBAN_PVT.Create_Kanban_Cards');
1615 END IF;
1616 select 1 into l_temp
1617 from mtl_kanban_cards
1618 where organization_id = p_Pull_Sequence_Rec.Organization_id
1619 and kanban_card_number = l_Kanban_Card_Number;
1620 fnd_message.set_name ('FLM', 'FLM_ATTRIBUTE_EXISTS');
1621 fnd_message.set_token ('ATTRIBUTE', 'Kanban Card Number'||l_Kanban_Card_Number);
1622 l_error_msg := FND_MESSAGE.GET;
1623 FND_MSG_PUB.Add_Exc_Msg ( 'INV_Kanban_PVT',l_error_msg );
1624 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
1625 exception
1626 when others then
1627 null;
1628 end;
1629
1630 end if;
1631 end if;
1632 else
1633 l_supplier_id:= p_Pull_Sequence_Rec.Supplier_id;
1634 l_supp_site_id:= p_Pull_Sequence_Rec.Supplier_site_id;
1635 end if;
1636 INV_KanbanCard_PKG.Insert_Row(
1637 X_Return_Status => l_Return_Status,
1638 P_Kanban_Card_Id => l_Kanban_Card_Id,
1639 P_Kanban_Card_Number => l_Kanban_Card_Number,
1640 P_Pull_Sequence_Id => p_Pull_Sequence_Rec.Pull_Sequence_Id,
1641 P_Inventory_item_id => p_Pull_Sequence_Rec.Inventory_item_id,
1642 P_Organization_id => p_Pull_Sequence_Rec.Organization_id,
1643 P_Subinventory_name => p_Pull_Sequence_Rec.Subinventory_name,
1644 P_Supply_Status => l_Supply_Status,
1645 P_Card_Status => l_Card_Status,
1646 P_Kanban_Card_Type => Nvl(p_pull_sequence_rec.kanban_card_type,INV_Kanban_Pvt.g_card_type_replenishable),
1647 P_Source_type => p_Pull_Sequence_Rec.Source_type,
1648 P_Kanban_size => nvl(p_Pull_Sequence_Rec.Kanban_size,0),
1649 P_Last_Update_Date => SYSDATE,
1650 P_Last_Updated_By => FND_GLOBAL.USER_ID,
1651 P_Creation_Date => SYSDATE,
1652 P_Created_By => FND_GLOBAL.USER_ID,
1653 P_Last_Update_Login => FND_GLOBAL.LOGIN_ID,
1654 P_Last_Print_Date => NULL,
1655 P_Locator_id => p_Pull_Sequence_Rec.Locator_id,
1656 P_Supplier_id => l_supplier_id,
1657 P_Supplier_site_id => l_supp_site_id,
1658 P_Source_Organization_id => p_Pull_Sequence_Rec.Source_Organization_id,
1659 P_Source_Subinventory => p_Pull_Sequence_Rec.Source_Subinventory,
1660 P_Source_Locator_id => p_Pull_Sequence_Rec.Source_Locator_id,
1661 P_wip_line_id => p_Pull_Sequence_Rec.wip_line_id,
1662 P_Current_Replnsh_Cycle_Id=> l_Current_Replnsh_Cycle_Id,
1663 P_document_type => NULL,
1664 P_document_header_id => NULL,
1665 P_document_detail_id => NULL,
1666 P_error_code => NULL,
1667 P_Attribute_Category => NULL,
1668 P_Attribute1 => NULL,
1669 P_Attribute2 => NULL,
1670 P_Attribute3 => NULL,
1671 P_Attribute4 => NULL,
1672 P_Attribute5 => NULL,
1673 P_Attribute6 => NULL,
1674 P_Attribute7 => NULL,
1675 P_Attribute8 => NULL,
1676 P_Attribute9 => NULL,
1677 P_Attribute10 => NULL,
1678 P_Attribute11 => NULL,
1679 P_Attribute12 => NULL,
1680 P_Attribute13 => NULL,
1681 P_Attribute14 => NULL,
1682 P_Attribute15 => NULL,
1683 P_Request_Id => NULL,
1684 P_Program_application_Id => NULL,
1685 P_Program_Id => NULL,
1686 P_Program_Update_date => NULL,
1687 p_release_kanban_flag => p_Pull_Sequence_Rec.release_kanban_flag);
1688
1689 if l_return_status = FND_API.G_RET_STS_ERROR
1690 Then
1691 Raise FND_API.G_EXC_ERROR;
1692 End if;
1693
1694 if l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
1695 Then
1696 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
1697 End If;
1698
1699 X_Kanban_Card_Ids(l_card_count) := l_Kanban_Card_Id;
1700
1701 end loop;
1702
1703 x_return_status := l_return_status;
1704
1705 EXCEPTION
1706
1707 WHEN FND_API.G_EXC_ERROR THEN
1708
1709 x_return_status := FND_API.G_RET_STS_ERROR;
1710
1711 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1712
1713 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1714
1715 WHEN OTHERS THEN
1716
1717 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1718 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1719 THEN
1720 FND_MSG_PUB.Add_Exc_Msg
1721 ( G_PKG_NAME
1722 , 'Create_Kanban_Cards'
1723 );
1724 END IF;
1725
1726 END Create_Kanban_Cards;
1727
1728 --
1729 --
1730 -- eKanban Changes
1731 --
1732 --
1733 PROCEDURE Create_Kanban_Cards
1734 ( X_return_status OUT NOCOPY VARCHAR2,
1735 X_Kanban_Card_Ids OUT NOCOPY INV_Kanban_PVT.Kanban_Card_Id_Tbl_Type,
1736 P_Pull_Sequence_Rec INV_Kanban_PVT.Pull_Sequence_Rec_Type,
1737 p_Supply_Status NUMBER,
1738 --eKanban Changes
1739 p_kanban_card_id NUMBER,
1740 p_kanban_card_number VARCHAR2,
1741 p_replenishment_count NUMBER,
1742 p_max_replenishments NUMBER,
1743 p_disable_date DATE,
1744 p_replacement_flag NUMBER
1745 )
1746 IS
1747 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
1748 l_kanban_Card_tbl INV_Kanban_PVT.Kanban_Card_Id_Tbl_Type;
1749 l_Kanban_Card_Id number;
1750 l_Kanban_Card_Number varchar2(200);
1751 l_supply_status Number;
1752 l_Card_status Number;
1753 l_Current_Replnsh_Cycle_Id Number;
1754 l_card_count number := 0;
1755 l_item_name varchar2(2000);
1756 l_loc_name varchar2(2000);
1757 l_subinventory varchar2(10);
1758 l_org_code varchar2(3);
1759
1760 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1761 BEGIN
1762
1763 FND_MSG_PUB.initialize;
1764 l_kanban_Card_tbl.Delete;
1765
1766 IF(p_pull_sequence_rec.release_kanban_flag =2) THEN
1767 l_card_status := INV_Kanban_Pvt.G_Card_Status_Hold;
1768 l_supply_status := inv_kanban_pvt.g_supply_status_full;
1769 ELSE
1770 l_card_status := INV_Kanban_Pvt.G_Card_Status_Active;
1771 l_supply_status := p_Supply_Status;
1772 END IF ;
1773
1774 l_Current_Replnsh_Cycle_Id := null;
1775
1776 if nvl(P_Pull_Sequence_Rec.NUMBER_OF_CARDS,0) <= 0
1777 then
1778
1779 Get_Pull_sequence_Tokens(p_pull_sequence_rec.pull_sequence_id,l_org_code,
1780 l_item_name,l_subinventory,l_loc_name);
1781 FND_MESSAGE.SET_NAME('INV','INV_CANT_GEN_CRDS_NO_NUM_CARDS');
1782 FND_MESSAGE.SET_TOKEN('ORG_CODE',l_org_code);
1783 FND_MESSAGE.SET_TOKEN('ITEM_NAME',l_item_name);
1784 FND_MESSAGE.SET_TOKEN('SUB_CODE',l_Subinventory);
1785 FND_MESSAGE.SET_TOKEN('LOCATOR_NAME',l_loc_name);
1786 FND_MSG_PUB.Add;
1787
1788 l_return_status := FND_API.G_RET_STS_ERROR;
1789
1790 end if;
1791
1792 if nvl(P_Pull_Sequence_Rec.Kanban_Size,0) <= 0
1793 then
1794 if l_org_code is null
1795 then
1796 Get_Pull_sequence_Tokens(p_pull_sequence_rec.pull_sequence_id,l_org_code,
1797 l_item_name,l_subinventory,l_loc_name);
1798 end if;
1799 FND_MESSAGE.SET_NAME('INV','INV_CANT_GEN_CRDS_NO_KBN_SIZE');
1800 FND_MESSAGE.SET_TOKEN('ORG_CODE',l_org_code);
1801 FND_MESSAGE.SET_TOKEN('ITEM_NAME',l_item_name);
1802 FND_MESSAGE.SET_TOKEN('SUB_CODE',l_Subinventory);
1803 FND_MESSAGE.SET_TOKEN('LOCATOR_NAME',l_loc_name);
1804 FND_MSG_PUB.Add;
1805 l_return_status := FND_API.G_RET_STS_ERROR;
1806
1807 end if;
1808
1809 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1810 RAISE FND_API.G_EXC_ERROR;
1811
1812 END IF;
1813
1814 --Existing kanban cards need to be cancelled only when creating a
1815 --regular replenishable card
1816 -- Do not cancel cards in eKanban.
1817 if ( nvl(fnd_profile.VALUE('FLM_EKB_OPS_FLAG'),0) = 0) then
1818 IF p_pull_sequence_rec.kanban_card_type <>
1819 INV_Kanban_Pvt.g_card_type_nonreplenishable then
1820 -- we should not delete old kanban cards but change their status
1821 -- for historical purposes.
1822 --Delete_Kanban_Cards(p_Pull_Sequence_Rec.Pull_Sequence_Id);
1823 update_kanban_card_status
1824 (p_card_status => g_card_status_cancel,
1825 p_pull_sequence_id => p_Pull_Sequence_Rec.pull_sequence_id);
1826 X_Kanban_Card_Ids := l_kanban_Card_tbl;
1827 END IF;
1828 end if;
1829
1830 for l_card_count in 1..p_Pull_Sequence_Rec.Number_Of_Cards
1831 loop
1832 l_Kanban_Card_Id := p_kanban_card_id;
1833 l_Kanban_Card_Number := p_kanban_card_number;
1834
1835 INV_KanbanCard_PKG.Insert_Row(
1836 X_Return_Status => l_Return_Status,
1837 P_Kanban_Card_Id => l_Kanban_Card_Id,
1838 P_Kanban_Card_Number => l_Kanban_Card_Number,
1839 P_Pull_Sequence_Id => p_Pull_Sequence_Rec.Pull_Sequence_Id,
1840 P_Inventory_item_id => p_Pull_Sequence_Rec.Inventory_item_id,
1841 P_Organization_id => p_Pull_Sequence_Rec.Organization_id,
1842 P_Subinventory_name => p_Pull_Sequence_Rec.Subinventory_name,
1843 P_Supply_Status => l_Supply_Status,
1844 P_Card_Status => l_Card_Status,
1845 P_Kanban_Card_Type => Nvl(p_pull_sequence_rec.kanban_card_type,INV_Kanban_Pvt.g_card_type_replenishable),
1846 P_Source_type => p_Pull_Sequence_Rec.Source_type,
1847 P_Kanban_size => nvl(p_Pull_Sequence_Rec.Kanban_size,0),
1848 P_Last_Update_Date => SYSDATE,
1849 P_Last_Updated_By => FND_GLOBAL.USER_ID,
1850 P_Creation_Date => SYSDATE,
1851 P_Created_By => FND_GLOBAL.USER_ID,
1852 P_Last_Update_Login => FND_GLOBAL.LOGIN_ID,
1853 P_Last_Print_Date => NULL,
1854 P_Locator_id => p_Pull_Sequence_Rec.Locator_id,
1855 P_Supplier_id => p_Pull_Sequence_Rec.Supplier_id,
1856 P_Supplier_site_id => p_Pull_Sequence_Rec.Supplier_site_id,
1857 P_Source_Organization_id => p_Pull_Sequence_Rec.Source_Organization_id,
1858 P_Source_Subinventory => p_Pull_Sequence_Rec.Source_Subinventory,
1859 P_Source_Locator_id => p_Pull_Sequence_Rec.Source_Locator_id,
1860 P_wip_line_id => p_Pull_Sequence_Rec.wip_line_id,
1861 P_Current_Replnsh_Cycle_Id=> l_Current_Replnsh_Cycle_Id,
1862 P_document_type => NULL,
1863 P_document_header_id => NULL,
1864 P_document_detail_id => NULL,
1865 P_error_code => NULL,
1866 P_Attribute_Category => NULL,
1867 P_Attribute1 => NULL,
1868 P_Attribute2 => NULL,
1869 P_Attribute3 => NULL,
1870 P_Attribute4 => NULL,
1871 P_Attribute5 => NULL,
1872 P_Attribute6 => NULL,
1873 P_Attribute7 => NULL,
1874 P_Attribute8 => NULL,
1875 P_Attribute9 => NULL,
1876 P_Attribute10 => NULL,
1877 P_Attribute11 => NULL,
1878 P_Attribute12 => NULL,
1879 P_Attribute13 => NULL,
1880 P_Attribute14 => NULL,
1881 P_Attribute15 => NULL,
1882 P_Request_Id => NULL,
1883 P_Program_application_Id => NULL,
1884 P_Program_Id => NULL,
1885 P_Program_Update_date => NULL,
1886 p_release_kanban_flag => p_Pull_Sequence_Rec.release_kanban_flag,
1887 --eKanban Changes
1888 p_replenishment_count => p_replenishment_count,
1889 p_max_replenishments => p_max_replenishments,
1890 p_disable_date => p_disable_date,
1891 p_replacement_flag => p_replacement_flag);
1892
1893 if l_return_status = FND_API.G_RET_STS_ERROR
1894 Then
1895 Raise FND_API.G_EXC_ERROR;
1896 End if;
1897
1898 if l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
1899 Then
1900 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
1901 End If;
1902
1903 X_Kanban_Card_Ids(l_card_count) := l_Kanban_Card_Id;
1904
1905 end loop;
1906
1907 x_return_status := l_return_status;
1908
1909 EXCEPTION
1910
1911 WHEN FND_API.G_EXC_ERROR THEN
1912
1913 x_return_status := FND_API.G_RET_STS_ERROR;
1914
1915 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1916
1917 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1918
1919 WHEN OTHERS THEN
1920
1921 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1922 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1923 THEN
1924 FND_MSG_PUB.Add_Exc_Msg
1925 ( G_PKG_NAME
1926 , 'Create_Kanban_Cards'
1927 );
1928 END IF;
1929
1930 END Create_Kanban_Cards;
1931
1932 --
1933 -- Get_Next_Replenish_Cycle_Id() : This function will generate and return
1934 -- replenish_cycle_id
1935 --
1936
1937 FUNCTION Get_Next_Replenish_Cycle_Id
1938 Return Number
1939 Is
1940 l_next_replenish_cycle_Id Number;
1941
1942 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1943 Begin
1944
1945 Select MTL_KANBAN_REPLENISH_CYCLE_S.NextVal
1946 Into l_next_replenish_cycle_Id
1947 From Dual;
1948 Return(l_next_replenish_cycle_Id);
1949
1950 End Get_Next_Replenish_Cycle_Id;
1951
1952 --
1953 --
1954 -- Create_Requisition : This would create Internal/PO requisition
1955 -- for a kanban card.
1956 --
1957 Procedure Create_Requisition( p_buyer_id IN NUMBER,
1958 p_interface_source_code IN VARCHAR2,
1959 p_requisition_type IN VARCHAR2,
1960 p_approval IN VARCHAR2,
1961 p_source_type_code IN VARCHAR2,
1962 p_kanban_card_rec_tbl IN Kanban_Card_Tbl_Type,
1963 p_destination_type_code IN VARCHAR2,
1964 p_deliver_location_id IN NUMBER,
1965 p_revision IN VARCHAR2,
1966 p_item_description IN VARCHAR2,
1967 p_primary_uom_code IN VARCHAR2,
1968 p_need_by_date IN DATE,
1969 p_charge_account_id IN NUMBER,
1970 p_accrual_account_id IN NUMBER,
1971 p_invoice_var_account_id IN NUMBER,
1972 p_budget_account_id IN NUMBER,
1973 p_autosource_flag IN VARCHAR2,
1974 p_po_org_id IN NUMBER ) IS
1975
1976 l_project_id NUMBER :=null;
1977 l_task_id NUMBER := null;
1978 l_project_reference_enabled NUMBER;
1979 l_project_accounting_context VARCHAR2(30);
1980 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1981 l_group_code varchar2(500) := null;
1982 l_consolidation_option Number := 1;
1983 begin
1984
1985 For l_order_count in 1..p_Kanban_Card_Rec_Tbl.Count
1986 Loop
1987
1988 -- Bug 1924497
1989
1990 l_project_accounting_context := null;
1991
1992 SELECT NVL(project_reference_enabled,2)
1993 INTO l_project_reference_enabled
1994 FROM mtl_parameters
1995 WHERE organization_id = p_kanban_card_rec_tbl(1).organization_id;
1996
1997 IF (l_project_reference_enabled = 1)THEN
1998 IF (p_kanban_card_rec_tbl(1).locator_id IS NOT NULL)THEN
1999 SELECT project_id
2000 INTO l_project_id
2001 FROM mtl_item_locations
2002 WHERE inventory_location_id = p_kanban_card_rec_tbl(1).locator_id
2003 AND organization_id = p_kanban_card_rec_tbl(1).organization_id;
2004 END IF;
2005 IF (l_project_id IS NOT NULL)THEN
2006 l_project_accounting_context := 'Y';
2007 SELECT task_id
2008 INTO l_task_id
2009 FROM mtl_item_locations
2010 WHERE NVL(project_id,-999) = NVL(l_project_id, -111)
2011 AND inventory_location_id =p_kanban_card_rec_tbl(1).locator_id
2012 AND organization_id = p_kanban_card_rec_tbl(1).organization_id;
2013 END IF;
2014 END IF;
2015
2016 -- eKanban Changes.
2017 if ( nvl(fnd_profile.VALUE('FLM_EKB_OPS_FLAG'),0) = 1) then
2018 if p_kanban_card_rec_tbl(1).pull_sequence_id is not null then
2019 select CONSOLIDATION into l_consolidation_option
2020 from MTL_KANBAN_PULL_SEQUENCES
2021 where pull_sequence_id =p_kanban_card_rec_tbl(1).pull_sequence_id;
2022 if l_consolidation_option <> G_consolidate_opt_yes then
2023 l_group_code := p_kanban_card_rec_tbl(1).pull_sequence_id||
2024 p_kanban_card_rec_tbl(1).kanban_card_id||
2025 p_kanban_card_rec_tbl(1).organization_id||
2026 p_kanban_card_rec_tbl(1).current_replnsh_cycle_id;
2027 end if;
2028 end if;
2029 end if;
2030
2031
2032 mydebug('GB:Need by date ' || TO_CHAR((trunc(p_need_by_date) + 1 - (1/(24*60*60))),'DD-MON-YYYY HH24:MI:SS'));
2033
2034 -- eKanban Changes.
2035 insert into po_requisitions_interface_all
2036 (
2037 LAST_UPDATE_DATE,
2038 LAST_UPDATED_BY,
2039 CREATION_DATE,
2040 CREATED_BY,
2041 PREPARER_ID,
2042 INTERFACE_SOURCE_CODE,
2043 REQUISITION_TYPE,
2044 AUTHORIZATION_STATUS,
2045 SOURCE_TYPE_CODE,
2046 SOURCE_ORGANIZATION_ID,
2047 SOURCE_SUBINVENTORY,
2048 DESTINATION_ORGANIZATION_ID,
2049 DESTINATION_SUBINVENTORY,
2050 DELIVER_TO_REQUESTOR_ID,
2051 DESTINATION_TYPE_CODE,
2052 DELIVER_TO_LOCATION_ID,
2053 ITEM_ID,
2054 ITEM_REVISION,
2055 ITEM_DESCRIPTION,
2056 UOM_CODE,
2057 QUANTITY,
2058 NEED_BY_DATE,
2059 GL_DATE,
2060 CHARGE_ACCOUNT_ID,
2061 ACCRUAL_ACCOUNT_ID,
2062 VARIANCE_ACCOUNT_ID,
2063 BUDGET_ACCOUNT_ID,
2064 AUTOSOURCE_FLAG,
2065 ORG_ID,
2066 SUGGESTED_VENDOR_ID,
2067 SUGGESTED_VENDOR_SITE_ID,
2068 -- SUGGESTED_BUYER_ID, /* Bug 1456782 */
2069 Kanban_card_Id,
2070 Batch_Id,
2071 PROJECT_ID,
2072 TASK_ID,
2073 PROJECT_ACCOUNTING_CONTEXT,
2074 GROUP_CODE
2075 )
2076 Values
2077 (
2078 sysdate,
2079 FND_GLOBAL.USER_ID,
2080 sysdate,
2081 FND_GLOBAL.USER_ID,
2082 p_buyer_Id,
2083 p_interface_Source_Code,
2084 p_requisition_type,
2085 p_approval,
2086 p_source_type_code,
2087 p_kanban_card_Rec_Tbl(1).Source_organization_Id,
2088 p_kanban_card_Rec_Tbl(1).Source_Subinventory,
2089 p_kanban_card_Rec_Tbl(1).organization_Id,
2090 p_kanban_card_Rec_Tbl(1).Subinventory_Name,
2091 p_buyer_Id,
2092 p_destination_type_code,
2093 p_deliver_location_id,
2094 p_kanban_card_Rec_Tbl(1).Inventory_Item_Id,
2095 p_revision,
2096 p_Item_description,
2097 p_Primary_uom_Code,
2098 p_kanban_card_rec_tbl(l_order_count).kanban_size,
2099 (trunc(p_need_by_date) + 1 - (1/(24*60*60))),
2100 SYSDATE,
2101 p_Charge_Account_Id,
2102 p_Accrual_Account_Id,
2103 p_Invoice_Var_Account_Id,
2104 p_Budget_Account_Id,
2105 p_autosource_flag,
2106 p_po_org_id,
2107 p_kanban_card_Rec_Tbl(1).Supplier_ID,
2108 p_kanban_card_Rec_Tbl(1).Supplier_Site_ID,
2109 -- p_Buyer_ID, /* Bug 1456782 */
2110 p_kanban_card_rec_tbl(l_order_count).kanban_card_id,
2111 p_kanban_card_rec_tbl(1).current_replnsh_cycle_id,
2112 l_project_id,
2113 l_task_id,
2114 l_project_accounting_context,
2115 l_group_code
2116 );
2117 end loop;
2118
2119
2120 /*
2121 Insert into po_requisition_interface_all with
2122
2123 Org_Id Operating Unit
2124 Preparer ID Buyer ID
2125 Item_Id Inventory_item_id
2126 Item_Description item_description
2127 Accrual_account_id Org level ap_accrual_account
2128 Authorization_status 'APPROVED'
2129 Autosource_Flag 'Y'
2130 Budget_Account_id Encumbrance_account Item Sub level/Sub level/Item level/Org level
2131 Charge_Account_Id For inventory_asset_flag='Y' use sub level/org level
2132 material_account else use sub level/item level/org level expense
2133 account
2134 Variance_Account_Id Org level - invoice_price_variance_account
2135 Created_By Userid
2136 Created_date Sysdate
2137 Last_Updated_By Userid
2138 Last_Update_Date Sysdate
2139 Default_to_location_Id Default location for the org in HR_LOCATIONS
2140 that has a customer in po_assosiation_locations
2141 Deliver_to_requestor_ID Buyer id of item
2142 Destination_Organization_id Org_id
2143 Destination_Subinventory Subinventory
2144 Destination_type_code 'INVENTORY'
2145 Quantity Order Quantity
2146 Requisition_Type 'INTERNAL'/'PURCHASE'
2147 Source Organization Id Source Org
2148 Source Subinventory Source Sub
2149 Source Type Code 'INVENTORY'/'VENDOR'
2150 GL_date sysdate
2151 Interface_source_code 'INV'
2152 UOM_CODE Primary UOM
2153 Requisition_type 'INTERNAL'/'PURCHASE'
2154 Suggested_vendor_id Supplier_ID
2155 Suggested_vendor_site Supplier_Site_Id
2156 Suggested_Buyer_Id Buyer_Id
2157 */
2158
2159 EXCEPTION
2160
2161 WHEN FND_API.G_EXC_ERROR THEN
2162
2163 Raise FND_API.G_EXC_ERROR;
2164
2165 WHEN OTHERS THEN
2166 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2167 THEN
2168 FND_MSG_PUB.Add_Exc_Msg
2169 ( G_PKG_NAME
2170 , 'Create_Requisition'
2171 );
2172 END IF;
2173
2174 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
2175
2176 end Create_Requisition;
2177
2178 --
2179 -- Create_Move_Order : This procedure would create a transfer order for
2180 -- for kanban card with source type Intra Org.
2181 --
2182
2183 Procedure Create_Transfer_Order(
2184 p_kanban_card_rec_tbl IN OUT NOCOPY Kanban_Card_Tbl_Type,
2185 p_need_by_date IN DATE,
2186 p_primary_uom_code IN VARCHAR2 ) IS
2187
2188 l_x_trohdr_rec INV_Move_Order_PUB.Trohdr_Rec_Type;
2189 l_x_trolin_tbl INV_Move_Order_PUB.Trolin_Tbl_Type;
2190 l_trohdr_rec INV_Move_Order_PUB.Trohdr_Rec_Type;
2191 l_trolin_tbl INV_Move_Order_PUB.Trolin_Tbl_Type;
2192 l_return_status VARCHAR2(1):= FND_API.G_RET_STS_SUCCESS;
2193 l_msg_count NUMBER;
2194 l_msg_data VARCHAR2(240);
2195 msg VARCHAR2(2000);
2196 l_header_id Number := FND_API.G_MISS_NUM;
2197 l_line_num Number := 0;
2198 l_item_locator_control_code NUMBER;
2199 l_from_sub_locator_type NUMBER;
2200 l_to_sub_locator_type NUMBER;
2201 l_org_locator_control_code NUMBER;
2202 l_auto_allocate_flag NUMBER; --Added for 3905884
2203 l_mo_request_number VARCHAR2(30); --Added for 3905884
2204 l_secondary_uom_code VARCHAR2(3);
2205 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2206 l_proc_name CONSTANT VARCHAR2(30) := 'Create_Transfer_Order';
2207 l_secondary_qty NUMBER;
2208 begin
2209
2210 l_trohdr_rec.created_by := FND_GLOBAL.USER_ID;
2211 l_trohdr_rec.creation_date := sysdate;
2212 l_trohdr_rec.date_required := p_need_by_date;
2213 l_trohdr_rec.from_subinventory_code := p_kanban_card_rec_tbl(1).source_subinventory;
2214 -- l_trohdr_rec.header_id := l_header_id;
2215 l_trohdr_rec.header_status := INV_Globals.G_TO_STATUS_PREAPPROVED;
2216 l_trohdr_rec.last_updated_by := FND_GLOBAL.USER_ID;
2217 l_trohdr_rec.last_update_date := sysdate;
2218 l_trohdr_rec.last_update_login := FND_GLOBAL.LOGIN_ID;
2219 l_trohdr_rec.organization_id := p_kanban_card_rec_tbl(1).organization_id;
2220 -- l_trohdr_rec.request_number := to_char(l_header_id);
2221 l_trohdr_rec.status_date := sysdate;
2222 l_trohdr_rec.to_subinventory_code := p_kanban_card_rec_tbl(1).subinventory_name;
2223 l_trohdr_rec.transaction_type_id := INV_GLOBALS.G_TYPE_TRANSFER_ORDER_SUBXFR;
2224 l_trohdr_rec.move_order_type := INV_GLOBALS.G_MOVE_ORDER_REPLENISHMENT;
2225 l_trohdr_rec.db_flag := FND_API.G_TRUE;
2226 l_trohdr_rec.operation := INV_GLOBALS.G_OPR_CREATE;
2227
2228 select location_control_code,secondary_uom_code
2229 into l_item_locator_control_code,l_secondary_uom_code
2230 from mtl_system_items
2231 where organization_id = p_kanban_card_rec_tbl(1).organization_id
2232 and inventory_item_id = p_kanban_card_rec_tbl(1).inventory_item_id;
2233
2234 select locator_type
2235 into l_from_sub_locator_type
2236 from mtl_secondary_inventories
2237 where organization_id = p_kanban_card_rec_tbl(1).organization_id
2238 and secondary_inventory_name = p_kanban_card_rec_tbl(1).source_subinventory;
2239
2240 select locator_type
2241 into l_to_sub_locator_type
2242 from mtl_secondary_inventories
2243 where organization_id = p_kanban_card_rec_tbl(1).organization_id
2244 and secondary_inventory_name = p_kanban_card_rec_tbl(1).subinventory_name;
2245
2246 select stock_locator_control_code
2247 into l_org_locator_control_code
2248 from mtl_parameters
2249 where organization_id = p_kanban_card_rec_tbl(1).organization_id;
2250
2251 if l_org_locator_control_code = 1 then
2252 p_kanban_card_rec_tbl(1).source_locator_id := null;
2253 p_kanban_card_rec_tbl(1).locator_id := null;
2254 elsif l_org_locator_control_code = 4 then
2255 if l_from_sub_locator_type = 1 then
2256 p_kanban_card_rec_tbl(1).source_locator_id := null;
2257 elsif l_from_sub_locator_type = 5 then
2258 if l_item_locator_control_code = 1 then
2259 p_kanban_card_rec_tbl(1).source_locator_id := null;
2260 end if;
2261 end if;
2262 if l_to_sub_locator_type = 1 then
2263 p_kanban_card_rec_tbl(1).locator_id := null;
2264 elsif l_to_sub_locator_type = 5 then
2265 if l_item_locator_control_code = 1 then
2266 p_kanban_card_rec_tbl(1).locator_id := null;
2267 end if;
2268 end if;
2269 end if;
2270
2271 -- Bug 1673809
2272 /*
2273 if( l_item_locator_control_code = 1 OR l_from_sub_locator_type = 1 ) then
2274 p_kanban_card_rec_tbl(1).source_locator_id := null;
2275 end if;
2276
2277 if( l_item_locator_control_code = 1 OR l_to_sub_locator_type = 1) then
2278 p_kanban_card_rec_tbl(1).locator_id := null;
2279 end if;
2280 */
2281
2282
2283
2284 For l_order_count in 1..p_Kanban_Card_Rec_Tbl.Count Loop
2285 l_line_num := l_line_num + 1;
2286 l_trolin_tbl(l_order_count).created_by := FND_GLOBAL.USER_ID;
2287 l_trolin_tbl(l_order_count).creation_date := sysdate;
2288 l_trolin_tbl(l_order_count).date_required := p_need_by_date;
2289 l_trolin_tbl(l_order_count).from_locator_id := p_kanban_card_rec_tbl(1).source_locator_id;
2290 l_trolin_tbl(l_order_count).from_subinventory_code := p_kanban_card_rec_tbl(1).source_subinventory;
2291 l_trolin_tbl(l_order_count).inventory_item_id := p_kanban_card_rec_tbl(1).inventory_item_id;
2292 l_trolin_tbl(l_order_count).last_updated_by := FND_GLOBAL.USER_ID;
2293 l_trolin_tbl(l_order_count).last_update_date := sysdate;
2294 l_trolin_tbl(l_order_count).last_update_login := FND_GLOBAL.LOGIN_ID;
2295 l_trolin_tbl(l_order_count).line_id := FND_API.G_MISS_NUM;
2296 l_trolin_tbl(l_order_count).line_number := l_line_num;
2297 l_trolin_tbl(l_order_count).line_status := INV_Globals.G_TO_STATUS_PREAPPROVED;
2298 l_trolin_tbl(l_order_count).organization_id := p_kanban_card_rec_tbl(1).organization_id;
2299 l_trolin_tbl(l_order_count).quantity := p_kanban_card_rec_tbl(l_order_count).kanban_size;
2300 l_trolin_tbl(l_order_count).reference_id := p_kanban_card_rec_tbl(l_order_count).kanban_card_id;
2301 l_trolin_tbl(l_order_count).reference_type_code := INV_Transfer_Order_PVT.G_Ref_Type_Kanban;
2302 l_trolin_tbl(l_order_count).status_date := sysdate;
2303 l_trolin_tbl(l_order_count).to_locator_id := p_kanban_card_rec_tbl(1).locator_id;
2304 -- By kkoothan for Bug Fix:2340651.
2305 BEGIN
2306 SELECT project_id,task_id
2307 INTO l_trolin_tbl(l_order_count).project_id,
2308 l_trolin_tbl(l_order_count).task_id
2309 FROM mtl_item_locations
2310 WHERE inventory_location_id = p_kanban_card_rec_tbl(1).source_locator_id and organization_id = p_kanban_card_rec_tbl(1).organization_id;
2311 EXCEPTION
2312 WHEN no_data_found THEN
2313 NULL;
2314 END;
2315 -- End of Bug Fix:2340651.
2316
2317
2318
2319 /* bug4004567 The secondary quantity is calculated here for kanban replenishment while move order creation */
2320 IF l_secondary_uom_code IS NOT NULL THEN
2321 l_secondary_qty := inv_convert.inv_um_convert
2322 (item_id => p_kanban_card_rec_tbl(1).inventory_item_id
2323 ,precision => 5
2324 ,from_quantity => p_kanban_card_rec_tbl(1).kanban_size
2325 ,from_unit => p_primary_uom_code
2326 ,to_unit => l_secondary_uom_code
2327 ,from_name => NULL
2328 ,to_name => NULL);
2329 /* UOM conversion failure check */
2330 IF l_secondary_qty < 0 THEN
2331 mydebug('Uom Conversion Failed for Creating Transfer Order:'||p_kanban_card_rec_tbl(1).inventory_item_id|| ', ' ||p_kanban_card_rec_tbl(1).organization_id||l_proc_name);
2332 RAISE FND_API.g_exc_error;
2333 END IF ;
2334 ELSE
2335 l_secondary_uom_code := NULL ;
2336 l_secondary_qty := NULL ;
2337 END IF;
2338 /* bug4004567 */
2339
2340
2341 l_trolin_tbl(l_order_count).to_subinventory_code:= p_kanban_card_rec_tbl(1).subinventory_name;
2342 l_trolin_tbl(l_order_count).uom_code := p_primary_uom_code;
2343 l_trolin_tbl(l_order_count).transaction_type_id := INV_GLOBALS.G_TYPE_TRANSFER_ORDER_SUBXFR;
2344 l_trolin_tbl(l_order_count).db_flag := FND_API.G_TRUE;
2345 l_trolin_tbl(l_order_count).operation := INV_GLOBALS.G_OPR_CREATE;
2346 l_trolin_tbl(l_order_count).secondary_quantity := l_secondary_qty;
2347 l_trolin_tbl(l_order_count).secondary_uom := l_secondary_uom_code;
2348
2349 END LOOP;
2350
2351 INV_Transfer_Order_PVT.Process_Transfer_Order
2352 ( p_api_version_number => 1.0 ,
2353 p_init_msg_list => FND_API.G_TRUE,
2354 x_return_status => l_return_status,
2355 x_msg_count => l_msg_count,
2356 x_msg_data => l_msg_data,
2357 p_trohdr_rec => l_trohdr_rec,
2358 p_trolin_tbl => l_trolin_tbl,
2359 x_trohdr_rec => l_x_trohdr_rec,
2360 x_trolin_tbl => l_x_trolin_tbl
2361 );
2362
2363 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2364 FND_MSG_PUB.Add_Exc_Msg
2365 ( G_PKG_NAME
2366 , 'Create_transfer_order'
2367 );
2368 RAISE FND_API.G_EXC_ERROR;
2369 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
2370 FND_MSG_PUB.Add_Exc_Msg
2371 ( G_PKG_NAME
2372 , 'Create_transfer_order'
2373 );
2374 RAISE FND_API.G_EXC_ERROR;
2375 END IF;
2376
2377 For I in 1 .. l_x_trolin_tbl.count
2378 Loop
2379 p_kanban_card_rec_tbl(I).document_header_id := l_x_trolin_tbl(I).header_id;
2380 p_kanban_card_rec_tbl(I).document_detail_id := l_x_trolin_tbl(I).Line_id;
2381 p_kanban_card_rec_tbl(I).document_type := 4;
2382 End Loop;
2383
2384 /*Fix for 3905884
2385 IF Auto_Allocate_Flag= 1 (Yes) , allocate move order */
2386 BEGIN
2387 SELECT MKP.auto_allocate_flag INTO l_auto_Allocate_flag
2388 FROM Mtl_Kanban_Pull_Sequences MKP
2389 WHERE MKP.pull_sequence_id=p_kanban_card_rec_tbl(1).pull_sequence_id;
2390 EXCEPTION
2391 WHEN OTHERS THEN
2392 l_auto_Allocate_flag := 0;
2393 END;
2394
2395 IF l_auto_allocate_flag = 1 THEN
2396 Auto_Allocate_Kanban(p_kanban_card_rec_tbl(1).document_header_id,l_return_status, l_msg_count,l_msg_data);
2397
2398 SELECT MTRH.request_number INTO l_mo_request_number
2399 FROM Mtl_Txn_Request_Headers MTRH
2400 WHERE MTRH.Header_id = p_kanban_card_rec_tbl(1).document_header_id;
2401
2402 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
2403 FND_MESSAGE.SET_NAME('INV','INV_KANBAN_MO_ALLOC_SUCCESS');
2404 FND_MESSAGE.SET_TOKEN('MOVE_ORDER',l_mo_request_number);
2405 FND_MSG_PUB.Add;
2406 ELSE
2407 FND_MESSAGE.SET_NAME('INV','INV_MO_ALLOC_FAIL');
2408 FND_MESSAGE.SET_TOKEN('MOVE_ORDER',l_mo_request_number);
2409 FND_MSG_PUB.Add;
2410 END IF;
2411
2412 END IF;
2413 /* End of fix for 3905884*/
2414
2415 EXCEPTION
2416 WHEN FND_API.G_EXC_ERROR THEN
2417 Raise FND_API.G_EXC_ERROR;
2418
2419 WHEN OTHERS THEN
2420 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2421 FND_MSG_PUB.Add_Exc_Msg
2422 ( G_PKG_NAME
2423 , 'Create_Transfer_Order'
2424 );
2425 END IF;
2426 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
2427 end Create_Transfer_Order;
2428
2429 --
2430 -- Launch_MLP() : This program will launch the WIP Mass load program to
2431 -- to upload the data from WIP_JOB_SCHEDULE_INTERFACE
2432 -- table.
2433 --
2434
2435 Function Launch_MLP(p_group_id IN Number) return BOOLEAN IS
2436
2437 v_req_id NUMBER;
2438
2439 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2440 BEGIN
2441
2442 v_req_id := FND_REQUEST.SUBMIT_REQUEST( 'WIP', 'WICMLP',
2443 NULL, NULL, FALSE,
2444 TO_CHAR(p_group_id), /* grp id*/
2445 '3', /* validation lvl */
2446 '2' ); /* print report */
2447 commit;
2448
2449 if v_req_id > 0 then
2450 return TRUE;
2451 else
2452 Raise FND_API.G_EXC_UNEXPECTED_ERROR ;
2453 end if;
2454
2455 exception
2456 WHEN OTHERS THEN
2457 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2458 THEN
2459 FND_MSG_PUB.Add_Exc_Msg
2460 ( G_PKG_NAME
2461 , 'Launch_MLP'
2462 );
2463 END IF;
2464 Return FALSE;
2465
2466 end Launch_MLP;
2467
2468 --
2469 -- Create_Wip_Discrete() :This procedure would create a WIP Discrete Job for a
2470 -- kanban card.
2471 --
2472
2473 Procedure Create_Wip_Discrete(
2474 p_kanban_card_rec_tbl IN OUT NOCOPY Kanban_Card_Tbl_Type,
2475 p_fixed_lead_time IN NUMBER,
2476 p_var_lead_time IN NUMBER) IS
2477 l_group_id Number;
2478 v_launch Boolean := TRUE;
2479 l_project_id NUMBER :=null;
2480 l_task_id NUMBER := null;
2481 l_project_reference_enabled NUMBER;
2482 l_first_unit_start_date DATE;
2483 l_last_unit_completion_date DATE;
2484 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2485 l_status_type number;
2486 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2487 l_pre_proc_lead_time NUMBER; --Bug# 11717156
2488 begin
2489
2490
2491
2492
2493
2494
2495 For l_order_count in 1..p_Kanban_Card_Rec_Tbl.Count
2496 LOOP
2497 --eKanban Changes
2498 if nvl(fnd_profile.VALUE('FLM_EKB_OPS_FLAG'),0) = 1 then
2499 l_status_type := nvl(flm_kanban_config_params.get_dj_status_replenish(p_kanban_card_rec_tbl(1).organization_id),3);
2500 else
2501 l_status_type := 3;
2502 end if;
2503
2504 SELECT NVL(project_reference_enabled,2)
2505 INTO l_project_reference_enabled
2506 FROM mtl_parameters
2507 WHERE organization_id = p_kanban_card_rec_tbl(1).organization_id;
2508
2509 IF (l_project_reference_enabled = 1)THEN
2510 IF (p_kanban_card_rec_tbl(1).locator_id IS NOT NULL)THEN
2511 SELECT project_id
2512 INTO l_project_id
2513 FROM mtl_item_locations
2514 WHERE inventory_location_id = p_kanban_card_rec_tbl(1).locator_id
2515 AND organization_id = p_kanban_card_rec_tbl(1).organization_id;
2516 END IF;
2517 IF (l_project_id IS NOT NULL)THEN
2518 SELECT task_id
2519 INTO l_task_id
2520 FROM mtl_item_locations
2521 WHERE NVL(project_id,-999) = NVL(l_project_id, -111)
2522 AND inventory_location_id =p_kanban_card_rec_tbl(1).locator_id
2523 AND organization_id = p_kanban_card_rec_tbl(1).organization_id;
2524 END IF;
2525 END IF;
2526 --3100874 Outbound Flow Sequencing
2527 --if the need_by_date is passed completion date should be set to the
2528 --value passed, otherwise just set the start date to sysdate
2529 IF p_kanban_card_rec_tbl(l_order_count).need_by_date IS NOT NULL THEN
2530 l_first_unit_start_date := NULL;
2531 l_last_unit_completion_date :=
2532 p_kanban_card_rec_tbl(l_order_count).need_by_date;
2533 ELSE
2534 /* For bug 7721127 Start */
2535 -- l_first_unit_start_date := Sysdate;
2536 /* Bug 9437363. Changed the parameter (l_order_count) for record count to 1 */
2537 /* Commented below code for Bug# 11717156 and rewrote to consider the bom calander while calculating the start date of discrete job*/
2538 /* l_first_unit_start_date := Sysdate +
2539 get_preprocessing_lead_time(p_kanban_card_rec_tbl(1).organization_id , p_kanban_card_rec_tbl(1).inventory_item_id); */
2540 /* End of Bug 9437363 */
2541 /* For bug 7721127 Start */
2542
2543 /* Bug# 11717156: Start */
2544 l_pre_proc_lead_time := get_preprocessing_lead_time(p_kanban_card_rec_tbl(1).organization_id , p_kanban_card_rec_tbl(1).inventory_item_id);
2545 BEGIN
2546 SELECT c1.calendar_date
2547 INTO l_first_unit_start_date
2548 FROM mtl_parameters o ,
2549 bom_calendar_dates c1,
2550 bom_calendar_dates c
2551 WHERE o.organization_id = p_kanban_card_rec_tbl(1).organization_id
2552 AND c1.calendar_code = c.calendar_code
2553 AND c1.exception_set_id = c.exception_set_id
2554 AND c1.seq_num = (c.next_seq_num + TRUNC(l_pre_proc_lead_time))
2555 AND c.calendar_code = o.CALENDAR_CODE
2556 AND c.exception_set_id = o.CALENDAR_EXCEPTION_SET_ID
2557 AND c.calendar_date = TRUNC(SYSDATE);
2558 EXCEPTION
2559 WHEN OTHERS THEN
2560 l_first_unit_start_date := Sysdate + l_pre_proc_lead_time ;
2561 mydebug('Could not calulate the next working date from bom calendar');
2562 END;
2563 /* Bug# 11717156: End */
2564
2565 l_last_unit_completion_date := NULL;
2566 END IF;
2567
2568 Insert into WIP_JOB_SCHEDULE_INTERFACE
2569 (LAST_UPDATE_DATE,
2570 LAST_UPDATED_BY,
2571 CREATION_DATE,
2572 CREATED_BY,
2573 GROUP_ID,
2574 PROCESS_PHASE,
2575 PROCESS_STATUS,
2576 SOURCE_CODE,
2577 ORGANIZATION_ID,
2578 LOAD_TYPE,
2579 FIRST_UNIT_START_DATE,
2580 LAST_UNIT_COMPLETION_DATE,
2581 PRIMARY_ITEM_ID,
2582 START_QUANTITY,
2583 STATUS_TYPE,
2584 LINE_ID,
2585 kanban_card_id,
2586 project_id,
2587 task_id
2588 )
2589 values
2590 (
2591 sysdate ,
2592 FND_GLOBAL.USER_ID,
2593 sysdate ,
2594 FND_GLOBAL.USER_ID,
2595 p_kanban_card_rec_tbl(1).current_replnsh_cycle_id,
2596 2,
2597 1,
2598 'INV',
2599 p_kanban_card_rec_tbl(1).organization_id,
2600 1, /* Discrete job */
2601 l_first_unit_start_date,
2602 l_last_unit_completion_date,
2603 p_kanban_card_rec_tbl(1).inventory_item_id,
2604 p_kanban_card_rec_tbl(l_order_count).Kanban_size,
2605 l_status_type,
2606 p_kanban_card_rec_tbl(1).wip_line_id,
2607 p_kanban_card_rec_tbl(l_order_count).kanban_card_id,
2608 l_project_id,
2609 l_task_id
2610 );
2611 l_project_id := NULL;
2612 l_task_id := NULL;
2613 end loop;
2614 l_group_id := p_kanban_card_rec_tbl(1).current_replnsh_cycle_id;
2615 v_launch := Launch_MLP( l_group_id );
2616 if ( Not v_launch ) then
2617 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
2618 end if;
2619
2620 EXCEPTION
2621
2622 WHEN FND_API.G_EXC_ERROR THEN
2623
2624 Raise FND_API.G_EXC_ERROR;
2625
2626 WHEN OTHERS THEN
2627 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2628 THEN
2629 FND_MSG_PUB.Add_Exc_Msg
2630 ( G_PKG_NAME
2631 , 'Create_Wip_Discrete'
2632 );
2633 END IF;
2634
2635 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
2636 end Create_Wip_Discrete;
2637
2638 --
2639 -- Create_Rep_Schedule() : This procedure would Create a WIP Repetetive
2640 -- schedule.
2641 --
2642 Procedure Create_Rep_Schedule(
2643 p_kanban_card_rec_tbl IN OUT NOCOPY Kanban_Card_Tbl_Type,
2644 p_fixed_lead_time IN NUMBER,
2645 p_var_lead_time IN NUMBER ) IS
2646
2647 rep_sched_exist varchar2(1) := 'N';
2648 total_qty Number := 0;
2649 processing_days Number := 0;
2650 line_rate Number := 0;
2651 l_group_id Number;
2652 v_launch Boolean := TRUE;
2653 l_project_id number := NULL;
2654 l_task_id NUMBER :=NULL;
2655 l_project_reference_enabled NUMBER;
2656 l_first_unit_start_date DATE;
2657 l_last_unit_completion_date DATE;
2658 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2659 BEGIN
2660
2661 begin
2662 select MAXIMUM_RATE into line_rate
2663 from WIP_LINES
2664 where
2665 LINE_ID = p_kanban_card_rec_tbl(1).wip_line_id AND
2666 organization_id = p_kanban_card_rec_tbl(1).organization_id;
2667 exception
2668 When others then
2669 FND_MSG_PUB.Add_Exc_Msg
2670 ( G_PKG_NAME
2671 , 'Create Rep Sched|Wip Line Not Defined'
2672 );
2673 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2674 end;
2675
2676 /*
2677 begin
2678 select 'Y' into rep_sched_exist
2679 from WIP_JOB_SCHEDULE_INTERFACE
2680 where
2681 primary_item_id = p_kanban_card_rec_tbl(1).inventory_item_id AND
2682 organization_id = p_kanban_card_rec_tbl(1).organization_id AND
2683 line_id = p_kanban_card_rec_tbl(1).wip_line_id AND
2684 load_type = 2 AND
2685 process_phase <> 4 AND
2686 to_date(creation_date,'DD-MON-RR') = to_date(SYSDATE,'DD-MON-RR')
2687 For Update of start_quantity NOWAIT;
2688 Raise TOO_MANY_ROWS;
2689 exception
2690 When NO_DATA_FOUND then
2691 rep_sched_exist := 'N';
2692 When TOO_MANY_ROWS then
2693 rep_sched_exist := 'Y';
2694 When others then
2695 FND_MSG_PUB.Add_Exc_Msg
2696 ( G_PKG_NAME
2697 , 'Create Rep Sched|checking existing Schedules'
2698 );
2699 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2700 end;
2701
2702 If ( rep_sched_exist = 'Y' ) then
2703 For l_order_count in 1..p_kanban_card_rec_tbl.Count
2704 Loop
2705 total_qty := total_qty + p_kanban_card_rec_tbl(l_order_count).Kanban_size;
2706 End Loop;
2707 Else
2708 */
2709
2710 For l_order_count in 1..p_kanban_card_rec_tbl.Count
2711 Loop
2712 if ( l_order_count = 1 ) THEN
2713
2714 SELECT NVL(project_reference_enabled,2)
2715 INTO l_project_reference_enabled
2716 FROM mtl_parameters
2717 WHERE organization_id = p_kanban_card_rec_tbl(1).organization_id;
2718
2719 IF (l_project_reference_enabled = 1)THEN
2720 IF (p_kanban_card_rec_tbl(1).locator_id IS NOT NULL)THEN
2721 SELECT project_id
2722 INTO l_project_id
2723 FROM mtl_item_locations
2724 WHERE inventory_location_id = p_kanban_card_rec_tbl(1).locator_id
2725 AND organization_id = p_kanban_card_rec_tbl(1).organization_id;
2726 END IF;
2727 IF (l_project_id IS NOT NULL)THEN
2728 SELECT task_id
2729 INTO l_task_id
2730 FROM mtl_item_locations
2731 WHERE NVL(project_id,-999) = NVL(l_project_id, -111)
2732 AND inventory_location_id =p_kanban_card_rec_tbl(1).locator_id
2733 AND organization_id = p_kanban_card_rec_tbl(1).organization_id;
2734 END IF;
2735 END IF;
2736 --3100874 Outbound Flow Sequencing
2737 --if the need_by_date is passed completion date should be set to the
2738 --value passed, otherwise just set the start date to sysdate
2739 IF p_kanban_card_rec_tbl(l_order_count).need_by_date IS NOT NULL THEN
2740 l_first_unit_start_date := NULL;
2741 l_last_unit_completion_date :=
2742 p_kanban_card_rec_tbl(l_order_count).need_by_date;
2743 ELSE
2744 /* For bug 7721127 Start */
2745 -- l_first_unit_start_date := Sysdate;
2746 /* Bug 9437363. Changed the parameter (l_order_count) for record count to 1 */
2747 l_first_unit_start_date := Sysdate +
2748 get_preprocessing_lead_time(p_kanban_card_rec_tbl(1).organization_id , p_kanban_card_rec_tbl(1).inventory_item_id);
2749 /* End of Bug 9437363 */
2750 /* For bug 7721127 Start */
2751 l_last_unit_completion_date := NULL;
2752 END IF;
2753 Insert into WIP_JOB_SCHEDULE_INTERFACE
2754 ( LAST_UPDATE_DATE,
2755 LAST_UPDATED_BY,
2756 CREATION_DATE,
2757 CREATED_BY,
2758 GROUP_ID,
2759 PROCESS_PHASE,
2760 PROCESS_STATUS,
2761 SOURCE_CODE,
2762 ORGANIZATION_ID,
2763 LOAD_TYPE,
2764 FIRST_UNIT_START_DATE,
2765 -- FIRST_UNIT_COMPLETION_DATE,
2766 LAST_UNIT_COMPLETION_DATE,
2767 DAILY_PRODUCTION_RATE,
2768 PROCESSING_WORK_DAYS,
2769 PRIMARY_ITEM_ID,
2770 START_QUANTITY,
2771 STATUS_TYPE,
2772 line_id,
2773 project_id,
2774 task_id
2775 )
2776 values
2777 (
2778 SYSDATE ,
2779 FND_GLOBAL.USER_ID,
2780 SYSDATE ,
2781 FND_GLOBAL.USER_ID,
2782 p_kanban_card_rec_tbl(1).current_replnsh_cycle_id,
2783 2,
2784 1,
2785 'INV',
2786 p_kanban_card_rec_tbl(1).organization_id,
2787 2, /* Rep schedule */
2788 l_first_unit_start_date,
2789 l_last_unit_completion_date,
2790 -- SYSDATE,
2791 -- SYSDATE+(p_fixed_lead_time +
2792 -- (p_var_lead_time*p_kanban_card_rec_tbl(l_order_count).kanban_size)),
2793 line_rate,
2794 p_kanban_card_rec_tbl(l_order_count).Kanban_size / line_rate ,
2795 p_kanban_card_rec_tbl(1).inventory_item_id,
2796 p_kanban_card_rec_tbl(l_order_count).Kanban_size,
2797 1,
2798 p_kanban_card_rec_tbl(1).wip_line_id,
2799 l_project_id,
2800 l_task_id
2801 );
2802 else
2803 total_qty := total_qty + p_kanban_card_rec_tbl(l_order_count).Kanban_size;
2804 rep_sched_exist := 'Y';
2805 end if;
2806 End loop;
2807 -- End if;
2808
2809 p_kanban_card_rec_tbl(1).document_type := 6;
2810 if ( rep_sched_exist = 'Y' ) then
2811 Update WIP_JOB_SCHEDULE_INTERFACE
2812 set START_QUANTITY = START_QUANTITY + total_qty ,
2813 -- LAST_UNIT_COMPLETION_DATE = SYSDATE + (p_fixed_lead_time +
2814 -- p_var_lead_time*(START_QUANTITY + total_qty)),
2815 PROCESSING_WORK_DAYS = (START_QUANTITY + total_qty)/ line_rate,
2816 GROUP_ID = p_kanban_card_rec_tbl(1).current_replnsh_cycle_id
2817 where
2818 primary_item_id = p_kanban_card_rec_tbl(1).inventory_item_id AND
2819 organization_id = p_kanban_card_rec_tbl(1).organization_id AND
2820 line_id = p_kanban_card_rec_tbl(1).wip_line_id AND
2821 load_type = 2 AND
2822 process_phase = 2 AND
2823 group_id = p_kanban_card_rec_tbl(1).current_replnsh_cycle_id;
2824 end if;
2825
2826 l_group_id := p_kanban_card_rec_tbl(1).current_replnsh_cycle_id;
2827 v_launch := Launch_MLP( l_group_id );
2828 if ( Not v_launch ) then
2829 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
2830 end if;
2831
2832 EXCEPTION
2833
2834 WHEN FND_API.G_EXC_ERROR THEN
2835 Raise FND_API.G_EXC_ERROR;
2836
2837 WHEN OTHERS THEN
2838 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2839 THEN
2840 FND_MSG_PUB.Add_Exc_Msg
2841 ( G_PKG_NAME
2842 , 'Create_Rep_Schedule'
2843 );
2844 END IF;
2845 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
2846
2847 END Create_Rep_Schedule;
2848
2849 --
2850 -- Create_Flow_schedule : This procedure would create a Wip
2851 -- Flow schedule.
2852 --
2853
2854 Procedure Create_Flow_Schedule(
2855 p_kanban_card_rec_tbl IN Out NOCOPY Kanban_Card_Tbl_Type,
2856 p_fixed_lead_time IN NUMBER,
2857 p_var_lead_time IN NUMBER ) IS
2858
2859 l_flow_schedule_rec MRP_Flow_Schedule_PUB.Flow_Schedule_Rec_Type;
2860 l_x_flow_schedule_rec MRP_Flow_Schedule_PUB.Flow_Schedule_Rec_Type;
2861 l_x_flow_schedule_val_rec MRP_Flow_Schedule_PUB.Flow_Schedule_Val_Rec_Type;
2862 l_return_status VARCHAR2(1):= FND_API.G_RET_STS_SUCCESS;
2863 l_msg_count NUMBER;
2864 l_msg_data VARCHAR2(240);
2865 msg VARCHAR2(2000);
2866
2867 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2868 BEGIN
2869
2870 For l_order_count in 1..p_kanban_card_rec_tbl.Count
2871 Loop
2872
2873 l_flow_schedule_rec.created_by := FND_GLOBAL.USER_ID;
2874 l_flow_schedule_rec.creation_date := sysdate;
2875 l_flow_schedule_rec.last_updated_by := FND_GLOBAL.USER_ID;
2876 l_flow_schedule_rec.last_update_date := sysdate;
2877 l_flow_schedule_rec.line_id :=
2878 p_kanban_card_rec_tbl(1).wip_line_id;
2879 l_flow_schedule_rec.organization_id :=
2880 p_kanban_card_rec_tbl(1).organization_id;
2881 l_flow_schedule_rec.planned_quantity :=
2882 p_kanban_card_rec_tbl(l_order_count).kanban_size;
2883 l_flow_schedule_rec.primary_item_id :=
2884 p_kanban_card_rec_tbl(1).inventory_item_id;
2885 l_flow_schedule_rec.completion_subinventory :=
2886 p_kanban_card_rec_tbl(1).subinventory_name;
2887 l_flow_schedule_rec.completion_locator_id :=
2888 p_kanban_card_rec_tbl(1).locator_id;
2889
2890 IF p_kanban_card_rec_tbl(l_order_count).need_by_date IS NOT NULL THEN
2891 l_flow_schedule_rec.scheduled_start_date := NULL;
2892 l_flow_schedule_rec.scheduled_completion_date :=
2893 p_kanban_card_rec_tbl(l_order_count).need_by_date;
2894
2895 ELSE
2896
2897 /* l_flow_schedule_rec.scheduled_start_date := SYSDATE;
2898 -- l_flow_schedule_rec.scheduled_completion_date :=
2899 -- SYSDATE+(p_fixed_lead_time+
2900 -- (p_var_lead_time*p_kanban_card_rec_tbl(l_order_count).kanban_size)); */
2901 /* For bug 7721127 Start */
2902 /* Bug 9437363. Changed the parameter (l_order_count) for record count to 1 */
2903 l_flow_schedule_rec.scheduled_start_date := Sysdate +
2904 get_preprocessing_lead_time(p_kanban_card_rec_tbl(1).organization_id , p_kanban_card_rec_tbl(1).inventory_item_id);
2905 /* End of Bug 9437363 */
2906 /* For bug 7721127 Start */
2907 l_flow_schedule_rec.scheduled_completion_date :=
2908 MRP_LINE_SCHEDULE_ALGORITHM.calculate_completion_time
2909 (p_kanban_card_rec_tbl(1).organization_id,
2910 p_kanban_card_rec_tbl(1).inventory_item_id,
2911 p_kanban_card_rec_tbl(l_order_count).kanban_size,
2912 p_kanban_card_rec_tbl(1).wip_line_id,
2913 l_flow_schedule_rec.scheduled_start_date); -- Bug # 8583249 :Instead of sysdate,passing the start date calculated for fix done for bug 7721127
2914 END IF;
2915
2916
2917 -- l_flow_schedule_rec.schedule_group_id :=
2918 -- p_kanban_card_rec_tbl(1).current_replnsh_cycle_id;
2919 -- l_flow_schedule_rec.scheduled_by := FND_API.G_MISS_NUM;
2920 l_flow_schedule_rec.kanban_card_id :=
2921 p_kanban_card_rec_tbl(l_order_count).kanban_card_id;
2922 l_flow_schedule_rec.operation := MRP_GLOBALS.G_OPR_CREATE;
2923
2924 /* Requested by Liye Ma to add a new parameter p_explode_bom to this call
2925 to fix Flow Schedule Report bug 2147361
2926 Dependency: The signature change is in MRPPWFSS.pls 115.13 */
2927
2928 MRP_Flow_Schedule_PUB.Process_Flow_Schedule
2929 (
2930 p_api_version_number => 1.0,
2931 p_init_msg_list => FND_API.G_TRUE,
2932 x_return_status => l_return_status,
2933 x_msg_count => l_msg_count,
2934 x_msg_data => l_msg_data,
2935 p_flow_schedule_rec => l_flow_schedule_rec,
2936 x_flow_schedule_rec => l_x_flow_schedule_rec,
2937 x_flow_schedule_val_rec => l_x_flow_schedule_val_rec,
2938 p_explode_bom => 'Y'
2939 );
2940
2941 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2942 RAISE FND_API.G_EXC_ERROR;
2943 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
2944 RAISE FND_API.G_EXC_ERROR;
2945 END IF;
2946
2947 p_kanban_card_rec_tbl(l_order_count).document_header_id :=
2948 l_x_flow_schedule_rec.wip_entity_id;
2949 p_kanban_card_rec_tbl(l_order_count).document_type := 7;
2950
2951 end loop;
2952
2953 EXCEPTION
2954
2955 WHEN FND_API.G_EXC_ERROR THEN
2956
2957 Raise FND_API.G_EXC_ERROR;
2958
2959 WHEN OTHERS THEN
2960 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2961 THEN
2962 FND_MSG_PUB.Add_Exc_Msg
2963 ( G_PKG_NAME
2964 , 'Create_Flow_Schedule'
2965 );
2966 END IF;
2967
2968 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
2969 end Create_Flow_Schedule;
2970
2971
2972
2973 --
2974 -- Create_lot_based_job : This procedure would create a osfm
2975 -- lot based job
2976 --
2977
2978 Procedure Create_lot_based_job(
2979 p_kanban_card_rec_tbl IN Out NOCOPY Kanban_Card_Tbl_Type,
2980 p_fixed_lead_time IN NUMBER,
2981 p_var_lead_time IN NUMBER ) IS
2982
2983 l_return_status VARCHAR2(1):= FND_API.G_RET_STS_SUCCESS;
2984 l_msg_count NUMBER;
2985 l_msg_data VARCHAR2(240);
2986 msg VARCHAR2(2000);
2987 l_header_id NUMBER := NULL;
2988 l_reqid NUMBER := NULL;
2989 l_group_id NUMBER := NULL;
2990 l_mode_flag NUMBER := NULL;
2991 l_job_name VARCHAR2(255);
2992 l_first_unit_start_date DATE;
2993 l_last_unit_completion_date DATE;
2994 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2995 -- Following variable is added as a part of bug fix for bug # 3301126
2996 l_scheduling_method NUMBER := 2;
2997 l_status_type Number := 3;
2998
2999 BEGIN
3000
3001 if nvl(fnd_profile.VALUE('FLM_EKB_OPS_FLAG'),0) = 1 then
3002 l_status_type := nvl(flm_kanban_config_params.get_dj_status_replenish(p_kanban_card_rec_tbl(1).organization_id),3);
3003 else
3004 l_status_type := 3;
3005 end if;
3006
3007 -- Following IF_ELSE block is a part of bug fix for bug # 3301126
3008 IF(to_number(NVL(FND_PROFILE.VALUE('WSM_CREATE_LBJ_COPY_ROUTING'),0)) = 1 ) THEN
3009 l_scheduling_method := 1;
3010 ELSE
3011 l_scheduling_method := 2;
3012 END IF;
3013
3014 For l_order_count in 1..p_kanban_card_rec_tbl.Count
3015
3016 LOOP
3017
3018 select wsm_lot_sm_ifc_header_s.nextval
3019 into l_header_id
3020 from dual;
3021
3022
3023
3024 select wsm_lot_job_interface_s.NEXTVAL
3025 into l_group_id
3026 from dual;
3027
3028 IF p_kanban_card_rec_tbl(l_order_count).lot_number IS NULL THEN
3029 l_mode_flag := 1;
3030 ELSE
3031 l_mode_flag := 2;
3032 END IF;
3033
3034 select FND_Profile.value('WIP_JOB_PREFIX')||wip_job_number_s.nextval
3035 INTO l_job_name
3036 from dual;
3037 --3100874 Outbound Flow Sequencing
3038 --if the need_by_date is passed completion date should be set to the
3039 --value passed, otherwise just set the start date to sysdate
3040 IF p_kanban_card_rec_tbl(l_order_count).need_by_date IS NOT NULL THEN
3041 l_first_unit_start_date := NULL;
3042 l_last_unit_completion_date :=
3043 p_kanban_card_rec_tbl(l_order_count).need_by_date;
3044 ELSE
3045 /* For bug 7721127 Start */
3046 -- l_first_unit_start_date := Sysdate;
3047 /* Bug 9437363. Changed the parameter (l_order_count) for record count to 1 */
3048 l_first_unit_start_date := Sysdate +
3049 get_preprocessing_lead_time(p_kanban_card_rec_tbl(1).organization_id , p_kanban_card_rec_tbl(1).inventory_item_id);
3050 /* End of Bug 9437363 */
3051 /* For bug 7721127 Start */
3052 l_last_unit_completion_date := NULL;
3053 END IF;
3054
3055
3056 INSERT INTO WSM_LOT_JOB_INTERFACE
3057 (mode_flag,
3058 last_update_date,
3059 last_updated_by,
3060 creation_date,
3061 created_by,
3062 last_update_login,
3063 group_id,
3064 source_line_id,
3065 organization_id,
3066 load_type,
3067 status_type,
3068 primary_item_id,
3069 job_name,
3070 start_Quantity,
3071 process_Status,
3072 first_unit_start_date,
3073 last_unit_completion_date,
3074 scheduling_method,
3075 completion_subinventory,
3076 completion_locator_id,
3077 class_code,
3078 description,
3079 bom_revision_date,
3080 routing_revision_date,
3081 header_id,
3082 kanban_card_id)
3083 VALUES
3084 (l_mode_flag,
3085 sysdate,
3086 fnd_global.user_id,
3087 sysdate,
3088 fnd_global.user_id,
3089 fnd_global.login_id,
3090 l_group_id,
3091 Decode(l_mode_flag, 1,null,l_header_id),
3092 p_kanban_card_rec_tbl(1).organization_id,
3093 5, --job creation
3094 l_status_type, --1:unreleased, 3: released
3095 p_kanban_card_rec_tbl(1).inventory_item_id,
3096 l_job_name,
3097 Nvl(p_kanban_card_rec_tbl(l_order_count).replenish_quantity,p_kanban_card_rec_tbl(l_order_count).kanban_size),
3098 1,
3099 l_first_unit_start_date,
3100 l_last_unit_completion_date,
3101 l_scheduling_method,
3102 p_kanban_card_rec_tbl(1).subinventory_name,
3103 p_kanban_card_rec_tbl(1).locator_id,
3104 '',
3105 null,
3106 '',
3107 '',
3108 l_header_id,
3109 p_kanban_card_rec_tbl(l_order_count).kanban_card_id);
3110
3111 If l_status_type = 3 then
3112 IF p_kanban_card_rec_tbl(l_order_count).lot_number IS NOT NULL THEN
3113 insert into wsm_starting_lots_interface
3114 (header_id,
3115 lot_number,
3116 inventory_item_id,
3117 revision,
3118 organization_id,
3119 quantity,
3120 subinventory_code,
3121 locator_id,
3122 last_update_date,
3123 last_updated_by,
3124 creation_date,
3125 created_by,
3126 last_update_login )
3127 values
3128 ( l_header_id,
3129 p_kanban_card_rec_tbl(l_order_count).lot_number,
3130 p_kanban_card_rec_tbl(l_order_count).lot_item_id,
3131 p_kanban_card_rec_tbl(l_order_count).lot_item_revision,
3132 p_kanban_card_rec_tbl(l_order_count).organization_id,
3133 p_kanban_card_rec_tbl(l_order_count).lot_quantity,
3134 p_kanban_card_rec_tbl(l_order_count).lot_subinventory_code,
3135 p_kanban_card_rec_tbl(l_order_count).lot_location_id ,
3136 sysdate,
3137 fnd_global.user_id,
3138 sysdate,
3139 fnd_global.user_id,
3140 fnd_global.login_id);
3141
3142
3143 END IF;
3144 end if;
3145 p_kanban_card_rec_tbl(l_order_count).document_header_id := null;
3146 p_kanban_card_rec_tbl(l_order_count).document_type := 8;
3147
3148
3149 l_reqid := FND_REQUEST.SUBMIT_REQUEST (
3150 application => 'WSM',
3151 program => 'WSMPLBJI',
3152 sub_request => FALSE,
3153 argument1 => l_group_id);
3154 if ( l_reqid <= 0 ) then
3155 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
3156 end if;
3157 end loop;
3158
3159 EXCEPTION
3160
3161 WHEN FND_API.G_EXC_ERROR THEN
3162
3163 Raise FND_API.G_EXC_ERROR;
3164
3165 WHEN OTHERS THEN
3166 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3167 THEN
3168 FND_MSG_PUB.Add_Exc_Msg
3169 ( G_PKG_NAME
3170 , 'Create_lot_based_job'
3171 );
3172 END IF;
3173
3174 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
3175 end Create_lot_based_job;
3176
3177
3178
3179 --
3180 -- Create_Wip_Job() : This procedure will decide about the creation
3181 -- of a WIP replnsh mode.
3182 --
3183 Procedure Create_Wip_Job( p_kanban_card_rec_tbl IN OUT NOCOPY Kanban_Card_Tbl_Type,
3184 p_need_by_date IN DATE ,
3185 x_card_supply_status IN OUT NOCOPY Number ) IS
3186
3187 v_rep_flag varchar2(1);
3188 v_fixed_lead_time number;
3189 v_var_lead_time number;
3190 v_cfm_flag number;
3191 v_priority number;
3192 v_wip_line_id number := NULL;
3193 l_is_lot_control VARCHAR2(1) := NULL;
3194 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3195 BEGIN
3196
3197 begin
3198 select nvl(repetitive_planning_flag,'N'), nvl(fixed_lead_time,0),
3199 nvl(variable_lead_time,0)
3200 into v_rep_flag, v_fixed_lead_time, v_var_lead_time
3201 from MTL_SYSTEM_ITEMS_KFV
3202 where
3203 inventory_item_id = p_kanban_card_rec_tbl(1).inventory_item_id AND
3204 organization_id = p_kanban_card_rec_tbl(1).organization_id;
3205 exception
3206 when others then
3207 FND_MSG_PUB.Add_Exc_Msg
3208 ( G_PKG_NAME
3209 , 'Ist SQL stmt'
3210 );
3211 raise FND_API.G_EXC_UNEXPECTED_ERROR;
3212 end;
3213 IF p_Kanban_Card_Rec_Tbl(1).wip_line_id is NULL then
3214 begin
3215 select nvl(cfm_routing_flag,0),line_id into v_cfm_flag, v_wip_line_id
3216 from BOM_OPERATIONAL_ROUTINGS
3217 where
3218 assembly_item_id = p_kanban_card_rec_tbl(1).inventory_item_id AND
3219 organization_id = p_kanban_card_rec_tbl(1).organization_id AND
3220 alternate_routing_designator is NULL;
3221 exception
3222 when NO_DATA_FOUND then
3223 v_cfm_flag := 2;
3224 when others then
3225 FND_MSG_PUB.Add_Exc_Msg
3226 ( G_PKG_NAME
3227 , 'wip line id IS NULL'
3228 );
3229 raise FND_API.G_EXC_UNEXPECTED_ERROR;
3230 end;
3231
3232
3233 if v_cfm_flag = 1 THEN
3234
3235
3236 p_Kanban_Card_Rec_Tbl(1).wip_line_id := v_wip_line_id;
3237 create_flow_schedule(p_kanban_card_rec_tbl, v_fixed_lead_time,
3238 v_var_lead_time );
3239 x_card_supply_status := INV_Kanban_PVT.G_Supply_Status_InProcess;
3240 elsif (v_cfm_flag = 3) AND (wsmpvers.get_osfm_release_version > '110508')
3241 THEN
3242
3243 BEGIN
3244 SELECT 'Y' INTO l_is_lot_control
3245 FROM dual WHERE exists
3246 (SELECT 1 FROM mtl_system_items
3247 WHERE
3248 organization_id = p_kanban_card_rec_tbl(1).organization_id
3249 AND inventory_item_id = p_kanban_card_rec_tbl(1).inventory_item_id
3250 AND lot_control_code = 2);
3251 EXCEPTION
3252 WHEN OTHERS THEN
3253 l_is_lot_control := 'N';
3254 END;
3255
3256 IF l_is_lot_control = 'Y' then
3257
3258 create_lot_based_job(p_kanban_card_rec_tbl, v_fixed_lead_time,
3259 v_var_lead_time );
3260 x_card_supply_status := INV_Kanban_PVT.G_Supply_Status_InProcess;
3261
3262 END IF;
3263
3264 /* Code modification for 2186198 */
3265 elsif v_rep_flag = 'Y' THEN
3266
3267 BEGIN
3268 select line_id
3269 into v_wip_line_id
3270 from wip_repetitive_items
3271 where load_distribution_priority =
3272 (select min(load_distribution_priority)
3273 from wip_repetitive_items
3274 where organization_id = p_kanban_card_rec_tbl(1).organization_id
3275 and primary_item_id = p_kanban_card_rec_tbl(1).inventory_item_id
3276 group by organization_id,primary_item_id)
3277 and organization_id = p_kanban_card_rec_tbl(1).organization_id
3278 and primary_item_id = p_kanban_card_rec_tbl(1).inventory_item_id
3279 and rownum < 2;
3280 p_Kanban_Card_Rec_Tbl(1).wip_line_id := v_wip_line_id;
3281 exception
3282 when NO_DATA_FOUND then
3283 FND_MESSAGE.SET_NAME('INV','INV_ATTRIBUTE_REQUIRED');
3284 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','INV_WIP_LINE');
3285 FND_MSG_PUB.Add;
3286 Raise FND_API.G_EXC_ERROR;
3287 when others then
3288 FND_MSG_PUB.Add_Exc_Msg
3289 (G_PKG_NAME ,'wip line id IS NULL');
3290 raise FND_API.G_EXC_UNEXPECTED_ERROR;
3291 end ;
3292 create_rep_schedule(p_kanban_card_rec_tbl, v_fixed_lead_time,
3293 v_var_lead_time );
3294 x_card_supply_status := INV_Kanban_PVT.G_Supply_Status_InProcess;
3295
3296 else
3297 Create_Wip_Discrete(p_kanban_card_rec_tbl, v_fixed_lead_time,
3298 v_var_lead_time);
3299 x_card_supply_status := INV_Kanban_PVT.G_Supply_Status_Empty;
3300
3301 end if;
3302
3303 ELSE /* wip line id IS not NULL */
3304
3305 begin
3306 select nvl(cfm_routing_flag,0) into v_cfm_flag
3307 from BOM_OPERATIONAL_ROUTINGS
3308 where
3309 assembly_item_id = p_kanban_card_rec_tbl(1).inventory_item_id AND
3310 organization_id = p_kanban_card_rec_tbl(1).organization_id AND
3311 line_id = p_kanban_card_rec_tbl(1).wip_line_id AND
3312 nvl(priority,0) = ( select min(nvl(priority,0))
3313 from bom_operational_routings
3314 where
3315 assembly_item_id = p_kanban_card_rec_tbl(1).inventory_item_id AND
3316 organization_id = p_kanban_card_rec_tbl(1).organization_id AND
3317 line_id = p_kanban_card_rec_tbl(1).wip_line_id ) AND
3318 rownum < 2 ;
3319 exception
3320 when NO_DATA_FOUND then
3321 v_cfm_flag := 2;
3322 when others then
3323 FND_MSG_PUB.Add_Exc_Msg
3324 ( G_PKG_NAME
3325 , 'wip line id IS not NULL'
3326 );
3327 raise FND_API.G_EXC_UNEXPECTED_ERROR;
3328 end;
3329 IF v_cfm_flag = 1 THEN
3330
3331 create_flow_schedule(p_kanban_card_rec_tbl, v_fixed_lead_time,
3332 v_var_lead_time );
3333 x_card_supply_status := INV_Kanban_PVT.G_Supply_Status_InProcess;
3334
3335 elsif (v_cfm_flag = 3) AND (wsmpvers.get_osfm_release_version > '110508') THEN
3336
3337 BEGIN
3338 SELECT 'Y' INTO l_is_lot_control
3339 FROM dual WHERE exists
3340 (SELECT 1 FROM mtl_system_items
3341 WHERE
3342 organization_id = p_kanban_card_rec_tbl(1).organization_id
3343 AND inventory_item_id = p_kanban_card_rec_tbl(1).inventory_item_id
3344 AND lot_control_code = 2);
3345 EXCEPTION
3346 WHEN OTHERS THEN
3347 l_is_lot_control := 'N';
3348 END;
3349
3350 IF l_is_lot_control = 'Y' then
3351
3352 create_lot_based_job(p_kanban_card_rec_tbl, v_fixed_lead_time,
3353 v_var_lead_time );
3354 x_card_supply_status := INV_Kanban_PVT.G_Supply_Status_InProcess;
3355
3356 END IF;
3357
3358
3359 elsif v_rep_flag = 'Y' THEN
3360
3361 create_rep_schedule(p_kanban_card_rec_tbl, v_fixed_lead_time,
3362 v_var_lead_time );
3363 x_card_supply_status := INV_Kanban_PVT.G_Supply_Status_InProcess;
3364 ELSE
3365
3366 Create_Wip_Discrete(p_kanban_card_rec_tbl, v_fixed_lead_time,
3367 v_var_lead_time);
3368 x_card_supply_status := INV_Kanban_PVT.G_Supply_Status_Empty;
3369 end if;
3370
3371 END IF;
3372
3373 EXCEPTION
3374
3375 WHEN FND_API.G_EXC_ERROR THEN
3376
3377 Raise FND_API.G_EXC_ERROR;
3378
3379 WHEN OTHERS THEN
3380 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3381 THEN
3382 FND_MSG_PUB.Add_Exc_Msg
3383 ( G_PKG_NAME
3384 , 'Create_Wip_Job'
3385 );
3386 END IF;
3387
3388 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
3389 end Create_Wip_Job;
3390
3391 --
3392 --
3393 -- Create Replenishment: This procedure would create kick off the replenishment
3394 -- cycle for the kanban cards.
3395 --
3396 PROCEDURE Create_Replenishment
3397 (p_Kanban_Card_Rec_Tbl In Out NOCOPY Kanban_Card_Tbl_Type,
3398 p_lead_time Number,
3399 x_card_supply_status Out NOCOPY Number)
3400 IS
3401
3402 l_Item_Description Varchar2(240);
3403 l_Source_type_code Varchar2(30);
3404 l_Requisition_type Varchar2(30);
3405 l_Primary_Uom_Code Varchar2(3);
3406 l_deliver_location_Id Number;
3407 l_Buyer_Id Number;
3408 l_Encumb_Account_Id Number;
3409 l_Charge_Account_Id Number;
3410 l_Budget_Account_Id Number;
3411 l_Accrual_Account_Id Number;
3412 l_Invoice_Var_Account_Id Number;
3413 l_Inventory_Asset_Flag Varchar2(1);
3414 l_Interface_source_code Varchar2(30) := 'INV';
3415 l_Destination_type_code Varchar2(30) := 'INVENTORY';
3416 l_Approval Varchar2(30) := 'APPROVED';
3417 l_Autosource_Flag Varchar2(1) := 'P';
3418 l_need_by_date Date;
3419 l_need_by_time Number;
3420 l_PreProcess_lead_Time Number;
3421 l_Process_lead_Time Number;
3422 l_PostProcess_lead_Time Number;
3423 l_Encumb_Flag Varchar2(1);
3424 l_PO_Org_Id Number := null;
3425 l_sql_stmt_no Number;
3426 l_Revision Varchar2(3);
3427 l_Revision_qty_control_code Number;
3428 l_Kanban_Card_Rec_Tbl Kanban_Card_Tbl_Type;
3429 p_card_supply_status Number;
3430 revision_profile Number;
3431
3432 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3433 l_consolidation_option NUMBER := null;
3434 Begin
3435 mydebug('Inside create_replenishment');
3436
3437 l_Kanban_Card_Rec_Tbl := p_Kanban_Card_Rec_Tbl;
3438
3439 l_sql_stmt_no := 5;
3440
3441 l_sql_stmt_no := 10;
3442 -- Bug # 5568749, Removed Buyer_Id from the select statement
3443 Select msi.Description,Primary_Uom_Code,Inventory_Asset_Flag,
3444 nvl(mss.ENCUMBRANCE_ACCOUNT,
3445 nvl(msi.Encumbrance_Account,Org.Encumbrance_Account)),
3446 decode(msi.inventory_asset_flag, 'Y', mss.material_account,
3447 nvl(mss.expense_account,nvl(msi.expense_account,org.expense_account))),
3448 Org.Ap_accrual_account,
3449 Org.invoice_price_var_account,
3450 nvl(mss.preprocessing_lead_time,nvl(msi.preprocessing_lead_time,0)),
3451 nvl(mss.processing_lead_time,nvl(msi.full_lead_time,0)),
3452 nvl(mss.postprocessing_lead_time,nvl(msi.postprocessing_lead_time,0)),
3453 msi.revision_qty_control_code
3454 Into l_Item_Description,
3455 l_Primary_Uom_Code,
3456 l_Inventory_Asset_Flag,
3457 l_Encumb_Account_Id,
3458 l_Charge_Account_Id,
3459 l_Accrual_Account_Id,
3460 l_Invoice_Var_Account_Id,
3461 l_PreProcess_lead_Time,
3462 l_Process_lead_Time,
3463 l_PostProcess_lead_Time,
3464 l_Revision_qty_control_code
3465 From Mtl_System_Items msi,
3466 mtl_Parameters org,
3467 mtl_secondary_inventories mss
3468 Where Msi.Organization_Id = l_kanban_card_Rec_Tbl(1).Organization_id
3469 And Msi.Inventory_Item_Id = l_kanban_card_Rec_Tbl(1).Inventory_Item_Id
3470 And org.Organization_Id = l_kanban_card_Rec_Tbl(1).Organization_Id
3471 And mss.Organization_id = l_kanban_card_Rec_Tbl(1).Organization_id
3472 And mss.secondary_inventory_name = l_kanban_card_Rec_Tbl(1).Subinventory_Name;
3473
3474 Begin
3475 Select nvl(ENCUMBRANCE_ACCOUNT,l_Encumb_Account_Id),
3476 nvl(preprocessing_lead_time,l_PreProcess_lead_Time)
3477 + nvl(processing_lead_time,l_Process_lead_Time)
3478 + nvl(postprocessing_lead_time,l_PostProcess_lead_Time)
3479 Into l_budget_Account_Id,
3480 l_need_by_time
3481 From mtl_item_sub_inventories
3482 Where Organization_id = l_kanban_card_Rec_Tbl(1).Organization_id
3483 And Inventory_Item_Id = l_kanban_card_Rec_Tbl(1).Inventory_Item_Id
3484 And secondary_inventory = l_kanban_card_Rec_Tbl(1).Subinventory_Name;
3485 Exception
3486 When No_data_found
3487 Then
3488 l_need_by_time := l_PreProcess_lead_Time
3489 + l_Process_lead_Time
3490 + l_PostProcess_lead_Time;
3491 l_budget_Account_Id := l_Encumb_Account_Id;
3492 End;
3493
3494 l_sql_stmt_no := 20;
3495 select nvl(f.req_encumbrance_flag,'N'),o.operating_unit
3496 into l_encumb_flag,l_po_org_Id
3497 from financials_system_params_all f,
3498 org_organization_definitions o
3499 where o.organization_id = l_kanban_card_Rec_Tbl(1).Organization_id
3500 And nvl(f.org_id,-99) = nvl(o.operating_unit,-99);
3501
3502 IF l_kanban_card_Rec_Tbl(1).need_by_date IS NOT NULL THEN
3503 l_need_by_date := l_kanban_card_Rec_Tbl(1).need_by_date;
3504 ELSE
3505 l_sql_stmt_no := 30;
3506 select c1.calendar_date
3507 into l_need_by_date
3508 from mtl_parameters o,
3509 bom_calendar_dates c1,
3510 bom_calendar_dates c
3511 where o.organization_id = l_kanban_card_Rec_Tbl(1).Organization_id
3512 and c1.calendar_code = c.calendar_code
3513 and c1.exception_set_id = c.exception_set_id
3514 and c1.seq_num = (c.next_seq_num + trunc(nvl(p_lead_time,l_need_by_time)))
3515 and c.calendar_code = o.CALENDAR_CODE
3516 and c.exception_set_id = o.CALENDAR_EXCEPTION_SET_ID
3517 and c.calendar_date = trunc(sysdate);
3518 END IF;
3519 if l_kanban_card_Rec_Tbl(1).source_Type =
3520 INV_Kanban_PVT.G_Source_Type_InterOrg
3521 Then
3522
3523 -- MOAC: Replaced the po_location_associations
3524 -- view with a _ALL table.
3525 l_sql_stmt_no := 40;
3526 -- Bug Fix 5185446 : Added distinct
3527 select distinct org.location_id
3528 into l_deliver_location_id
3529 from hr_organization_units org,
3530 hr_locations loc,
3531 po_location_associations_all pla
3532 where org.organization_id =
3533 l_kanban_card_Rec_Tbl(1).Organization_id
3534 and org.location_id = loc.location_id
3535 and pla.location_id = loc.location_id;
3536
3537 Elsif l_kanban_card_Rec_Tbl(1).source_Type =
3538 INV_Kanban_PVT.G_Source_Type_Supplier
3539 Then
3540
3541 l_sql_stmt_no := 40;
3542 select org.location_id
3543 into l_deliver_location_id
3544 from hr_organization_units org,
3545 hr_locations loc
3546 where org.organization_id =
3547 l_kanban_card_Rec_Tbl(1).Organization_id
3548 and org.location_id = loc.location_id;
3549
3550 end if;
3551
3552 /* Bug 971203. Do not check for revision control code.Get the value from the
3553 profile and if the profile is Yes, then get revision */
3554
3555 revision_profile := fnd_profile.value('INV_PURCHASING_BY_REVISION') ;
3556 if revision_profile = 1 then
3557
3558 l_sql_stmt_no := 50;
3559
3560 select MAX(revision)
3561 into l_revision
3562 from mtl_item_revisions mir
3563 where inventory_item_id = l_kanban_card_Rec_Tbl(1).Inventory_Item_Id
3564 and organization_id = l_kanban_card_Rec_Tbl(1).organization_Id
3565 and effectivity_date < SYSDATE
3566 and implementation_date is not null /* Added for bug 7110794 */
3567 and effectivity_date =
3568 (
3569 select MAX(effectivity_date)
3570 from mtl_item_revisions mir1
3571 where mir1.inventory_item_id = mir.inventory_item_id
3572 and mir1.organization_id = mir.organization_id
3573 and implementation_date is not null /* Added for bug 7110794 */
3574 and effectivity_date < SYSDATE
3575 );
3576
3577 end if;
3578
3579
3580 l_sql_stmt_no := 60;
3581
3582 select employee_id
3583 into l_buyer_id
3584 from fnd_user
3585 where user_id = FND_GLOBAL.USER_ID;
3586
3587
3588
3589 /* Need to error */
3590 /*
3591 if (charge_acct is NULL) or
3592 (accru_acct is NULL) or
3593 (ipv_acct is NULL) or
3594 ((encum_flag <> 'N') and (budget_acct is null)) then
3595 select meaning into msg
3596 from mfg_lookups
3597 where lookup_type = 'INV_MMX_RPT_MSGS'
3598 and lookup_code = 1;
3599
3600 return(msg);
3601 -- return ('Unable to generate requisition');
3602 end if;
3603 */
3604
3605 If l_kanban_card_Rec_Tbl(1).source_type = INV_Kanban_PVT.G_Source_Type_InterOrg
3606 then
3607 l_source_type_code := 'INVENTORY';
3608 l_Requisition_type := 'INTERNAL';
3609 mydebug('create requisition INVENTORY INTERNAL');
3610 Create_Requisition( l_buyer_id, l_interface_source_code,
3611 l_requisition_type, l_approval,
3612 l_source_type_code, l_kanban_card_rec_tbl,
3613 l_destination_type_code, l_deliver_location_id,
3614 l_revision, l_item_description,
3615 l_primary_uom_code, l_need_by_date,
3616 l_charge_account_id, l_accrual_account_id,
3617 l_invoice_var_account_id, l_budget_account_id,
3618 l_autosource_flag, l_po_org_id );
3619 x_card_supply_status := INV_Kanban_PVT.G_Supply_Status_Empty;
3620 elsIf l_kanban_card_Rec_Tbl(1).source_type = INV_Kanban_PVT.G_Source_Type_Supplier
3621 Then
3622 l_source_type_code := 'VENDOR';
3623 l_Requisition_type := 'PURCHASE';
3624 mydebug('create requisition VENDOR PURCHASE');
3625 Create_Requisition( l_buyer_id, l_interface_source_code,
3626 l_requisition_type, l_approval,
3627 l_source_type_code, l_kanban_card_rec_tbl,
3628 l_destination_type_code, l_deliver_location_id,
3629 l_revision, l_item_description,
3630 l_primary_uom_code, l_need_by_date,
3631 l_charge_account_id, l_accrual_account_id,
3632 l_invoice_var_account_id, l_budget_account_id,
3633 l_autosource_flag, l_po_org_id );
3634 x_card_supply_status := INV_Kanban_PVT.G_Supply_Status_Empty;
3635 elsIf l_kanban_card_Rec_Tbl(1).source_type =
3636 INV_Kanban_PVT.G_Source_Type_IntraOrg
3637 Then
3638 l_source_type_code := 'INVENTORY';
3639 l_Requisition_type := 'TRANSFER';
3640 mydebug('create transfer order INVENTORY TRANSFER');
3641 Create_Transfer_Order(p_kanban_card_rec_tbl,l_need_by_date,l_Primary_Uom_Code);
3642 x_card_supply_status := INV_Kanban_PVT.G_Supply_Status_InProcess;
3643
3644 elsIf l_kanban_card_Rec_Tbl(1).source_type =
3645 INV_Kanban_PVT.G_Source_Type_Production
3646 Then
3647 l_source_type_code := 'PRODUCTION';
3648 l_Requisition_type := 'MAKE';
3649 mydebug('create wip job PRODUCTION MAKE ');
3650 Create_Wip_Job(p_kanban_card_rec_tbl, l_need_by_date,
3651 p_card_supply_status);
3652 x_card_supply_status := p_card_supply_status;
3653 else
3654 Return;
3655 end if;
3656
3657 EXCEPTION
3658
3659 WHEN NO_data_FOUND Then
3660
3661 If l_sql_stmt_no = 10
3662 Then
3663 FND_MESSAGE.SET_NAME('INV','INV-NO ITEM RECORD');
3664 Elsif l_sql_stmt_no = 20
3665 then
3666 FND_MESSAGE.SET_NAME('INV','INV-NO ORG INFORMATION');
3667 Elsif l_sql_stmt_no = 30
3668 Then
3669 FND_MESSAGE.SET_NAME('INV','INV-NO CALENDAR DATE');
3670 Elsif l_sql_stmt_no = 40
3671 Then
3672 FND_MESSAGE.SET_NAME('INV','INV_DEFAULT_DELIVERY_LOC_REQD');
3673 Elsif l_sql_stmt_no = 50
3674 Then
3675 FND_MESSAGE.SET_NAME('INV','INV_INT_REVCODE');
3676 Elsif l_sql_stmt_no = 60
3677 Then
3678 FND_MESSAGE.SET_NAME('FND','CONC-FDWHOAMI INVALID USERID');
3679 FND_MESSAGE.SET_TOKEN('USERID',to_char(FND_GLOBAL.USER_ID));
3680 End If;
3681 FND_MSG_PUB.Add;
3682 Raise FND_API.G_EXC_ERROR;
3683
3684 WHEN FND_API.G_EXC_ERROR THEN
3685
3686 Raise FND_API.G_EXC_ERROR;
3687
3688 WHEN OTHERS THEN
3689 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3690 THEN
3691 FND_MSG_PUB.Add_Exc_Msg
3692 ( G_PKG_NAME
3693 , 'Create_Replenishment'
3694 );
3695 END IF;
3696
3697 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
3698
3699 End Create_Replenishment;
3700
3701 --
3702 -- Check_And_Create_Replenishment() : This procedure will check whether it is
3703 -- ok to start replenishment cycle for a card.
3704 --
3705
3706 PROCEDURE Check_And_Create_Replenishment
3707 (x_return_status Out NOCOPY Varchar2,
3708 X_Supply_Status Out NOCOPY Number,
3709 X_Current_Replenish_Cycle_Id Out NOCOPY Number,
3710 P_Kanban_Card_Rec In Out NOCOPY INV_Kanban_PVT.Kanban_Card_Rec_Type)
3711 IS
3712
3713 l_Pull_Sequence_Rec Mtl_Kanban_Pull_Sequences%RowType;
3714 l_Wait_Kanban_card_Tbl Kanban_Card_Tbl_Type;
3715 l_Kanban_Card_Rec INV_Kanban_PVT.Kanban_Card_Rec_Type;
3716 l_Wait_Kanban_Size Number := 0;
3717 l_Card_Count Number := 0;
3718 l_Order_Count Number := 0;
3719 l_Current_replenish_cycle_Id Number;
3720 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
3721 l_Kanban_Card_tbl Kanban_Card_Tbl_Type;
3722 p_card_supply_status Number;
3723
3724
3725 Cursor Get_Cards_On_Wait
3726 Is
3727 Select Kanban_card_Id,kanban_size,
3728 NULL lot_item_id,null lot_number,NULL lot_item_revision,
3729 NULL lot_subinventory_code,NULL lot_location_id,NULL lot_quantity,
3730 NULL replenish_quantity
3731 From Mtl_Kanban_Cards
3732 Where Pull_Sequence_Id = p_Kanban_Card_Rec.Pull_Sequence_Id
3733 -- And Card_Status = INV_Kanban_PVT.G_Card_Status_Active
3734 And Supply_Status = INV_Kanban_PVT.G_Supply_Status_Wait
3735 And Nvl(Supplier_Id,-1) = Nvl(p_Kanban_Card_Rec.Supplier_Id,-1)
3736 And Nvl(Supplier_Site_Id,-1) = Nvl(p_Kanban_Card_Rec.Supplier_Site_Id,-1)
3737 And Nvl(Source_Organization_Id,-1) = Nvl(p_Kanban_Card_Rec.Source_Organization_Id,-1)
3738 And Nvl(Source_Subinventory,'#?#') = Nvl(p_Kanban_Card_Rec.Source_Subinventory,'#?#')
3739 And Nvl(Source_Locator_Id,-1) = Nvl(p_Kanban_Card_Rec.Source_Locator_Id,-1)
3740 And Nvl(wip_line_id,-1) = Nvl(p_Kanban_Card_Rec.wip_line_id,-1)
3741
3742 -- Following condition added as a bugfix for bug#3389681 to prevent consideration of
3743 -- current card if it is in wait status as it will be considered twice.
3744 And Kanban_card_Id <> p_Kanban_Card_Rec.Kanban_card_Id
3745 For Update Of Supply_Status NoWait;
3746
3747 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3748 Begin
3749
3750 l_Kanban_Card_tbl.delete;
3751 l_Wait_Kanban_card_Tbl.Delete;
3752
3753 l_Order_Count := 1;
3754
3755 l_Kanban_Card_tbl(l_Order_Count) := p_kanban_card_rec;
3756
3757 If P_Kanban_Card_rec.Pull_sequence_Id = INV_Kanban_PVT.G_No_Pull_Sequence
3758 OR (P_Kanban_Card_Rec.kanban_card_type = INV_Kanban_PVT.G_Card_Type_NonReplenishable) Then
3759 l_Current_Replenish_Cycle_Id := Get_Next_Replenish_Cycle_Id;
3760 l_Kanban_Card_tbl(l_Order_Count).current_replnsh_cycle_id := l_Current_Replenish_Cycle_Id;
3761 Create_Replenishment(l_Kanban_Card_tbl,null, p_card_supply_status);
3762 X_Supply_Status := p_card_supply_status;
3763 X_Current_Replenish_Cycle_Id := l_Current_Replenish_Cycle_Id;
3764 else
3765 Select *
3766 Into l_Pull_Sequence_Rec
3767 From Mtl_Kanban_Pull_Sequences
3768 Where Pull_Sequence_Id = P_Kanban_Card_Rec.Pull_Sequence_Id
3769 For Update Of Minimum_Order_Quantity NOWait;
3770
3771 If nvl(l_Pull_Sequence_Rec.Minimum_Order_Quantity,0) = 0 Then
3772 -- eKanban Changes
3773 if nvl(l_Pull_Sequence_Rec.consolidation,INV_Kanban_PVT.G_consolidate_opt_no) <> INV_Kanban_PVT.G_consolidate_opt_yes then
3774 l_Current_Replenish_Cycle_Id := Get_Next_Replenish_Cycle_Id;
3775 l_Kanban_Card_tbl(l_Order_Count).current_replnsh_cycle_id := l_Current_Replenish_Cycle_Id;
3776 Create_Replenishment(l_Kanban_Card_tbl,l_pull_sequence_rec.replenishment_lead_time, p_card_supply_status);
3777 X_Supply_Status := p_card_supply_status;
3778 X_Current_Replenish_Cycle_Id := l_Current_Replenish_Cycle_Id;
3779 else
3780 X_Supply_Status := INV_Kanban_PVT.G_Supply_Status_Consolidate;
3781 X_Current_Replenish_Cycle_Id := Null;
3782 end if;
3783 Elsif (P_Kanban_Card_Rec.Kanban_Size >= l_Pull_Sequence_Rec.minimum_order_quantity)
3784 OR (P_Kanban_Card_Rec.lot_number IS NOT NULL)
3785 Then
3786 -- eKanban Changes
3787 if nvl(l_Pull_Sequence_Rec.consolidation,INV_Kanban_PVT.G_consolidate_opt_no) <> INV_Kanban_PVT.G_consolidate_opt_yes then
3788 l_Current_Replenish_Cycle_Id := Get_Next_Replenish_Cycle_Id;
3789 l_Kanban_Card_tbl(l_Order_Count).current_replnsh_cycle_id := l_Current_Replenish_Cycle_Id;
3790 Create_Replenishment(l_Kanban_Card_tbl,l_pull_sequence_rec.replenishment_lead_time, p_card_supply_status);
3791 X_Supply_Status := p_card_supply_status;
3792 X_Current_Replenish_Cycle_Id := l_Current_Replenish_Cycle_Id;
3793 else
3794 X_Supply_Status := INV_Kanban_PVT.G_Supply_Status_Consolidate;
3795 X_Current_Replenish_Cycle_Id := Null;
3796 end if;
3797 Else
3798
3799 For l_kanban_card in Get_Cards_On_Wait
3800 Loop
3801 l_card_count := l_card_count + 1;
3802 l_Wait_Kanban_card_Tbl(l_card_count).Kanban_card_Id := l_kanban_card.Kanban_Card_Id;
3803 l_Wait_Kanban_card_Tbl(l_card_count).Kanban_Size := l_kanban_card.Kanban_Size;
3804 l_Wait_Kanban_Size := l_Wait_Kanban_Size + l_kanban_card.Kanban_Size;
3805
3806 l_Wait_Kanban_card_Tbl(l_card_count).lot_item_id := l_kanban_card.lot_item_id;
3807 l_Wait_Kanban_card_Tbl(l_card_count).lot_number := l_kanban_card.lot_number;
3808 l_Wait_Kanban_card_Tbl(l_card_count).lot_item_revision := l_kanban_card.lot_item_revision;
3809 l_Wait_Kanban_card_Tbl(l_card_count).lot_subinventory_code := l_kanban_card.lot_subinventory_code;
3810 l_Wait_Kanban_card_Tbl(l_card_count).lot_location_id := l_kanban_card.lot_location_id;
3811 l_Wait_Kanban_card_Tbl(l_card_count).lot_quantity := l_kanban_card.lot_quantity;
3812 l_Wait_Kanban_card_Tbl(l_card_count).replenish_quantity := l_kanban_card.replenish_quantity;
3813 End Loop;
3814
3815 if (l_Wait_Kanban_Size + p_kanban_Card_rec.kanban_Size) >= l_Pull_Sequence_Rec.Minimum_Order_Quantity Then
3816
3817 -- eKanban Changes
3818 if nvl(l_Pull_Sequence_Rec.consolidation,INV_Kanban_PVT.G_consolidate_opt_no) <> INV_Kanban_PVT.G_consolidate_opt_yes then
3819 l_Current_replenish_Cycle_Id := Get_Next_Replenish_Cycle_Id;
3820 For l_card_Count in 1..l_Wait_Kanban_card_Tbl.Count
3821 Loop
3822
3823 l_order_count := l_order_count + 1;
3824 l_Kanban_card_Tbl(l_Order_Count).Kanban_card_Id := l_Wait_Kanban_card_Tbl(l_Card_Count).Kanban_Card_Id;
3825 l_Kanban_card_Tbl(l_Order_Count).Kanban_Size := l_Wait_Kanban_card_Tbl(l_Card_Count).Kanban_Size;
3826 End Loop;
3827
3828 l_Kanban_Card_tbl(1).current_replnsh_cycle_id := l_Current_Replenish_Cycle_Id;
3829 create_Replenishment(l_Kanban_card_Tbl,l_pull_sequence_rec.replenishment_lead_time, p_card_supply_status);
3830 X_Supply_Status := p_card_supply_status;
3831 X_Current_Replenish_Cycle_Id := l_Current_Replenish_Cycle_Id;
3832
3833 For l_card_Count in 2..l_Kanban_card_Tbl.Count
3834 Loop
3835 Update Mtl_Kanban_Cards
3836 Set Supply_Status = p_card_supply_status,
3837 Current_Replnsh_Cycle_Id = l_Current_Replenish_Cycle_Id,
3838 replenishment_count = nvl(replenishment_count,0)+1,
3839 Last_Update_Date = SYSDATE,
3840 Last_Updated_By = FND_GLOBAL.USER_ID
3841 Where Kanban_Card_Id = l_Kanban_card_Tbl(l_Card_Count).Kanban_card_Id;
3842
3843 l_Kanban_Card_Rec.Kanban_card_Id := l_Kanban_card_Tbl(l_Card_Count).Kanban_card_Id;
3844 l_kanban_card_rec := INV_KanbanCard_PKG.Query_Row( p_Kanban_Card_id => l_Kanban_card_Tbl(l_Card_Count).Kanban_card_Id);
3845 l_kanban_card_rec.document_type := l_Kanban_card_Tbl(l_Card_Count).document_type;
3846 l_kanban_card_rec.document_header_id := l_Kanban_card_Tbl(l_Card_Count).document_header_id;
3847 l_kanban_card_rec.document_detail_id := l_Kanban_card_Tbl(l_Card_Count).document_detail_id;
3848
3849 INV_KanbanCard_PKG.Insert_Activity_For_Card(l_Kanban_Card_Rec);
3850 End Loop;
3851 else
3852 For l_card_Count in 1..l_Wait_Kanban_card_Tbl.Count Loop
3853 l_order_count := l_order_count + 1;
3854 l_Kanban_card_Tbl(l_Order_Count).Kanban_card_Id := l_Wait_Kanban_card_Tbl(l_Card_Count).Kanban_Card_Id;
3855 l_Kanban_card_Tbl(l_Order_Count).Kanban_Size := l_Wait_Kanban_card_Tbl(l_Card_Count).Kanban_Size;
3856 End Loop;
3857
3858 For l_card_Count in 2..l_Kanban_card_Tbl.Count Loop
3859 Update Mtl_Kanban_Cards
3860 Set Supply_Status = INV_Kanban_PVT.G_Supply_Status_Consolidate,
3861 Last_Update_Date = SYSDATE,
3862 Last_Updated_By = FND_GLOBAL.USER_ID
3863 Where Kanban_Card_Id = l_Kanban_card_Tbl(l_Card_Count).Kanban_card_Id;
3864 l_kanban_card_rec := INV_KanbanCard_PKG.Query_Row( p_Kanban_Card_id => l_Kanban_card_Tbl(l_Card_Count).Kanban_card_Id);
3865 INV_KanbanCard_PKG.Insert_Activity_For_Card(l_Kanban_Card_Rec);
3866 End Loop;
3867 X_Supply_Status := INV_Kanban_PVT.G_Supply_Status_Consolidate;
3868 X_Current_Replenish_Cycle_Id := Null;
3869 end if;
3870 Else
3871 X_Supply_Status := INV_Kanban_PVT.G_Supply_Status_Wait;
3872 X_Current_Replenish_Cycle_Id := Null;
3873 End If;
3874 End If;
3875 End If;
3876 x_return_status := l_return_status;
3877
3878 p_kanban_card_rec.document_type := l_Kanban_card_Tbl(1).document_type;
3879 p_kanban_card_rec.document_header_id := l_Kanban_card_Tbl(1).document_header_id;
3880 p_kanban_card_rec.document_detail_id := l_Kanban_card_Tbl(1).document_detail_id;
3881
3882 EXCEPTION
3883
3884 WHEN FND_API.G_EXC_ERROR THEN
3885
3886 x_return_status := FND_API.G_RET_STS_ERROR;
3887
3888 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3889
3890 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3891
3892 WHEN OTHERS THEN
3893
3894 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3895
3896 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3897 THEN
3898 FND_MSG_PUB.Add_Exc_Msg
3899 ( G_PKG_NAME
3900 , 'Check_And_Create_Replenishment'
3901 );
3902 END IF;
3903
3904 End Check_And_Create_Replenishment;
3905
3906 Procedure test IS
3907 i NUMBER := 0;
3908 l_pull_seq_id_tbl INV_kanban_PVT.pull_sequence_id_Tbl_Type;
3909 l_return_status VARCHAR2(1):= FND_API.G_RET_STS_SUCCESS;
3910 l_operation_tbl INV_Kanban_PVT.operation_tbl_type;/*This new local var has been added to
3911 keep in synch with the call to procedure update_pull_sequence_tbl */
3912 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3913 begin
3914
3915 For pull_seq_rec IN (select pull_sequence_id from MTL_KANBAN_PULL_SEQUENCES
3916 Where Kanban_plan_id = -1 ) LOOP
3917 i := i + 1;
3918 l_pull_seq_id_tbl(i) := pull_seq_rec.pull_sequence_id ;
3919 l_operation_tbl(i) := 0; --Storing 0 for update
3920 END LOOP;
3921
3922 update_pull_sequence_tbl( l_return_status, l_pull_seq_id_tbl, 'Y',l_operation_tbl );
3923
3924 IF l_return_status IN ( FND_API.G_RET_STS_ERROR,
3925 FND_API.G_RET_STS_UNEXP_ERROR) THEN
3926 RAISE FND_API.G_EXC_ERROR;
3927 END IF;
3928
3929 EXCEPTION
3930
3931 WHEN FND_API.G_EXC_ERROR THEN
3932 Raise FND_API.G_EXC_ERROR;
3933
3934 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3935 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
3936
3937 WHEN OTHERS THEN
3938
3939 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3940 THEN
3941 FND_MSG_PUB.Add_Exc_Msg
3942 ( G_PKG_NAME
3943 , 'test'
3944 );
3945 END IF;
3946
3947 END test;
3948
3949 PROCEDURE update_kanban_card_status
3950 (p_Card_Status IN Number,
3951 p_pull_sequence_id IN Number)
3952
3953 IS
3954 l_kanban_card_rec INV_Kanban_PVT.kanban_card_rec_type;
3955 CURSOR get_kanban_card_ids IS
3956 SELECT kanban_card_id
3957 FROM mtl_kanban_cards
3958 WHERE pull_sequence_id = p_pull_sequence_id ;
3959
3960 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3961 BEGIN
3962
3963 for kanban_cards_ids in get_kanban_card_ids
3964 LOOP
3965 l_kanban_card_rec := inv_kanbancard_pkg.query_row(kanban_cards_ids.kanban_card_id);
3966 inv_kanbancard_pkg.update_card_status
3967 (p_kanban_card_rec =>l_kanban_card_rec,
3968 p_card_status => p_card_status);
3969 END LOOP;
3970
3971 END update_kanban_card_status;
3972
3973 PROCEDURE return_att_quantity(p_org_id IN NUMBER,
3974 p_item_id IN NUMBER,
3975 p_rev IN VARCHAR2,
3976 p_lot_no IN VARCHAR2,
3977 p_subinv IN VARCHAR2,
3978 p_locator_id IN NUMBER,
3979 x_qoh OUT NOCOPY NUMBER,
3980 x_atr OUT NOCOPY NUMBER,
3981 x_att OUT NOCOPY NUMBER,
3982 x_err_code OUT NOCOPY NUMBER,
3983 x_err_msg OUT NOCOPY VARCHAR2)
3984 IS
3985 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3986 BEGIN
3987
3988 wsmputil.return_att_quantity(p_org_id => p_org_id
3989 ,p_item_id => p_item_id
3990 ,p_rev => p_rev
3991 ,p_lot_no => p_lot_no
3992 ,p_subinv => p_subinv
3993 ,p_locator_id => p_locator_id
3994 ,p_qoh => x_qoh
3995 ,p_atr => x_atr
3996 ,p_att => x_att
3997 ,p_err_code => x_err_code
3998 ,p_err_msg => x_err_msg);
3999
4000 EXCEPTION
4001 WHEN OTHERS THEN
4002 x_qoh := NULL;
4003 x_atr := NULL;
4004 x_att := NULL;
4005 x_err_code := -1;
4006 x_err_msg := Substr(Sqlerrm,1,255);
4007
4008 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4009 THEN
4010 FND_MSG_PUB.Add_Exc_Msg
4011 ( G_PKG_NAME
4012 ,'return_att_quantity'
4013 );
4014 END IF;
4015
4016 END return_att_quantity;
4017
4018 PROCEDURE get_max_kanban_asmbly_qty
4019 ( p_bill_seq_id IN NUMBER,
4020 P_COMPONENT_ITEM_ID IN NUMBER,
4021 P_BOM_REVISION_DATE IN DATE,
4022 P_START_SEQ_NUM IN NUMBER,
4023 P_AVAILABLE_QTY IN NUMBER,
4024 X_MAX_ASMBLY_QTY OUT NOCOPY NUMBER,
4025 X_ERROR_CODE OUT NOCOPY NUMBER,
4026 X_error_msg OUT NOCOPY VARCHAR2)
4027 IS
4028
4029 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
4030 BEGIN
4031
4032 wsmputil.get_max_kanban_asmbly_qty
4033 ( P_BILL_SEQ_ID => p_bill_seq_id,
4034 P_COMPONENT_ITEM_ID => p_component_item_id,
4035 P_BOM_REVISION_DATE => Nvl(p_bom_revision_date,Sysdate),
4036 P_START_SEQ_NUM => p_start_seq_num,
4037 P_AVAILABLE_QTY => p_available_qty,
4038 P_MAX_ASMBLY_QTY => x_max_asmbly_qty,
4039 P_ERROR_CODE => x_error_code,
4040 p_error_msg => x_error_msg);
4041 EXCEPTION
4042 WHEN OTHERS THEN
4043 x_max_asmbly_qty := NULL;
4044 x_error_code := -1;
4045 x_error_msg := Substr(Sqlerrm,1,255);
4046
4047 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4048 THEN
4049 FND_MSG_PUB.Add_Exc_Msg
4050 ( G_PKG_NAME
4051 ,'return_max_kanban_asmbly_qty'
4052 );
4053 END IF;
4054
4055 END get_max_kanban_asmbly_qty;
4056
4057 FUNCTION eligible_for_lbj
4058 (p_organization_id IN NUMBER,
4059 p_inventory_item_id IN NUMBER,
4060 p_source_type_id IN NUMBER,
4061 p_kanban_card_id IN NUMBER)
4062 RETURN VARCHAR2 IS
4063 l_source_type_id NUMBER := p_source_type_id;
4064 l_rep_flag VARCHAR2(1) := NULL;
4065 l_lot_control NUMBER := NULL;
4066 l_cfm_flag NUMBER := NULL;
4067 l_assembly_item_id NUMBER := p_inventory_item_id;
4068 l_organization_id NUMBER := p_organization_id;
4069 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
4070 BEGIN
4071 --fnd_message.debug('Inside eligible');
4072
4073 IF wsmpvers.get_osfm_release_version <= '110508' THEN
4074 RETURN 'N';
4075 ELSIF l_source_type_id = inv_kanban_pvt.g_source_type_production THEN
4076 --Source type production
4077 BEGIN
4078 select
4079 Nvl(repetitive_planning_flag,'N'), lot_control_code
4080 into
4081 l_rep_flag, l_lot_control
4082 from MTL_SYSTEM_ITEMS
4083 where
4084 inventory_item_id = l_assembly_item_id AND
4085 organization_id = l_organization_id;
4086 EXCEPTION
4087 when others THEN
4088 RAISE fnd_api.g_exc_unexpected_error;
4089 end;
4090
4091 --lot controlled
4092 IF l_rep_flag = 'N' AND l_lot_control = 2 THEN
4093
4094 BEGIN
4095 SELECT nvl(cfm_routing_flag,0)
4096 into l_cfm_flag
4097 from BOM_OPERATIONAL_ROUTINGS
4098 where
4099 assembly_item_id = l_assembly_item_id AND
4100 organization_id = l_organization_id AND
4101 alternate_routing_designator is NULL;
4102 EXCEPTION
4103 when no_data_found THEN
4104 l_cfm_flag := 2;
4105 WHEN OTHERS THEN
4106 RAISE fnd_api.g_exc_unexpected_error;
4107 END;
4108
4109
4110
4111 IF l_cfm_flag = 3 THEN
4112 -- network routing hence return true
4113 RETURN 'Y';
4114 ELSE --Bug# 3249105
4115 RETURN 'N';
4116 END IF;
4117
4118 ELSE --rep_flag = 'Y' or not lot controlled
4119 RETURN 'N';
4120 END IF;
4121
4122 ELSE --source_type <> production
4123 RETURN 'N';
4124 END IF;
4125
4126 --fnd_message.debug(' end ');
4127
4128 EXCEPTION
4129 WHEN OTHERS THEN
4130 RETURN 'N';
4131 END eligible_for_lbj;
4132
4133 PROCEDURE GET_KANBAN_REC_GRP_INFO
4134 (p_organization_id IN NUMBER,
4135 p_kanban_assembly_id IN NUMBER,
4136 p_rtg_rev_date IN DATE,
4137 x_bom_seq_id OUT NOCOPY NUMBER,
4138 x_start_seq_num OUT NOCOPY NUMBER,
4139 X_error_code OUT NOCOPY NUMBER,
4140 X_error_msg OUT NOCOPY VARCHAR2) IS
4141 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
4142 BEGIN
4143 wsmputil.GET_KANBAN_REC_GRP_INFO(p_organization_id => p_organization_id,
4144 p_kanban_assembly_id => p_kanban_assembly_id,
4145 p_rtg_rev_date => p_rtg_rev_date,
4146 p_bom_seq_id => x_bom_seq_id,
4147 p_start_seq_num => x_start_seq_num,
4148 p_error_code => x_error_code,
4149 p_error_msg => x_error_msg);
4150
4151 EXCEPTION
4152 WHEN OTHERS THEN
4153 x_error_code := -1;
4154 x_error_msg := Substr(Sqlerrm,1,255);
4155
4156 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4157 THEN
4158 FND_MSG_PUB.Add_Exc_Msg
4159 ( G_PKG_NAME
4160 ,'GET_KANBAN_REC_GRP_INFO'
4161 );
4162 END IF;
4163
4164 END get_kanban_rec_grp_info;
4165
4166
4167 /*Bug 3740514--New procedure to check if the card status should be updated.*/
4168
4169 /*Added arguements in the procedure for bug 7133795 */
4170 PROCEDURE update_card_and_card_status(p_kanban_card_id IN NUMBER, p_supply_status IN NUMBER, p_document_detail_Id IN NUMBER, p_document_header_id IN NUMBER, p_update OUT NOCOPY BOOLEAN) IS
4171
4172 CURSOR mtl_kca IS
4173 SELECT replenishment_cycle_id
4174 , document_header_id
4175 , document_detail_id
4176 FROM mtl_kanban_card_activity
4177 WHERE kanban_card_id = p_kanban_card_id
4178 AND document_header_id IS NOT NULL
4179 ORDER BY kanban_activity_id DESC;
4180
4181
4182 -- MOAC: Changed po_distributions to po_distributions_all.
4183
4184 CURSOR po_dist(po_rel_id NUMBER, po_dist_id NUMBER) IS
4185 SELECT NVL(quantity_delivered, 0)
4186 FROM po_distributions_all
4187 WHERE po_release_id = po_rel_id
4188 AND po_distribution_id = po_dist_id;
4189
4190 l_rep_cycl_id NUMBER;
4191 l_crd_doc_hdr_id NUMBER;
4192 l_crd_doc_det_id NUMBER;
4193 l_max_rep_id NUMBER;
4194 l_del_qty NUMBER := -33;
4195 l_doc_type_id NUMBER;
4196 -- l_max_req NUMBER;/*Bug#4490269*/ /* Bug 7133795 */
4197 l_req NUMBER;/*Bug#4490269*/
4198
4199
4200 BEGIN
4201
4202 p_update := TRUE; -- By Default update the kanban card and kanban card activity
4203
4204 -- Bug 3987589; Added the AND condition 'AND document_type <> fnd_api.g_miss_num' and the exception block
4205 IF p_supply_status = INV_Kanban_PVT.G_Supply_Status_Full THEN /*Bug 4490269*/
4206
4207 BEGIN
4208 SELECT document_type
4209 , replenishment_cycle_id
4210 INTO l_doc_type_id
4211 , l_max_rep_id
4212 FROM mtl_kanban_card_activity
4213 WHERE kanban_card_id = p_kanban_card_id
4214 AND document_type IS NOT NULL
4215 AND document_type <> fnd_api.g_miss_num
4216 AND replenishment_cycle_id = (SELECT MAX(replenishment_cycle_id)
4217 FROM mtl_kanban_card_activity
4218 WHERE kanban_card_id = p_kanban_card_id);
4219 EXCEPTION
4220 WHEN TOO_MANY_ROWS THEN
4221 mydebug('Multiple document types and maximum replenishment cycle id returned for kanban card Id ' || p_kanban_card_id||' ;Hence disallowing Supply status update');
4222 p_update := FALSE;
4223 RETURN;
4224 END;
4225
4226 mydebug('Document type, maximum Replenishment cycle Id for kanban card Id ' || p_kanban_card_id || ': ' || l_doc_type_id || ', '
4227 || l_max_rep_id);
4228
4229 /* Only if the document type is Blanket Release, then continue */
4230
4231 IF (l_doc_type_id IN (inv_kanban_pvt.g_doc_type_release,INV_kanban_PVT.G_Doc_type_PO)) THEN
4232 OPEN mtl_kca;
4233
4234 FETCH mtl_kca
4235 INTO l_rep_cycl_id
4236 , l_crd_doc_hdr_id
4237 , l_crd_doc_det_id;
4238
4239 CLOSE mtl_kca;
4240
4241 IF (l_rep_cycl_id IS NOT NULL
4242 AND l_crd_doc_hdr_id IS NOT NULL
4243 AND l_crd_doc_det_id IS NOT NULL) THEN
4244 IF l_rep_cycl_id = l_max_rep_id THEN
4245 OPEN po_dist(l_crd_doc_hdr_id, l_crd_doc_det_id);
4246
4247 FETCH po_dist
4248 INTO l_del_qty;
4249
4250 CLOSE po_dist;
4251 ELSE
4252 p_update := FALSE;
4253 END IF;
4254
4255 /* If the delivered quantity is 0, then Correction/Return/Receipt/Receiving Transaction of some other
4256 Release is trying to update the card and card activity status, which should not be allowed */
4257 IF (l_del_qty = 0) THEN
4258 p_update := FALSE;
4259 END IF;
4260 END IF; -- if l_rep_cycl_id IS NOT NULL ....
4261 END IF; -- if l_doc_type_id = INV_kanban_PVT.G_Doc_type_Release
4262
4263 /*Bug#4490268--If InProcess, then need to check for any pending requsitions/PO/Release*/
4264 ELSIF p_supply_status = INV_Kanban_PVT.G_Supply_Status_InProcess THEN
4265
4266 -- Commented the below code for the bug # 7133795
4267 /* SELECT max(requisition_line_id) into l_max_req
4268 FROM po_requisition_lines
4269 WHERE kanban_card_id = p_kanban_card_id;
4270
4271 BEGIN
4272 SELECT 1 INTO l_req
4273 FROM po_requisitions_interface
4274 WHERE kanban_card_id = p_kanban_card_id;
4275 EXCEPTION
4276 WHEN NO_DATA_FOUND THEN
4277 BEGIN
4278 SELECT 1 INTO l_req
4279 FROM po_requisition_lines
4280 WHERE kanban_card_id = p_kanban_card_id
4281 AND requisition_line_id = l_max_req
4282 AND line_location_id IS NULL;
4283 EXCEPTION
4284 WHEN NO_DATA_FOUND THEN
4285 BEGIN
4286 SELECT 1 INTO l_req
4287 FROM po_requisition_lines prl1
4288 WHERE prl1.kanban_card_id = p_kanban_card_id
4289 AND prl1.requisition_line_id = l_max_req
4290 AND prl1.line_location_id IS NOT NULL
4291 AND (EXISTS (SELECT '1' FROM po_headers poh
4292 WHERE EXISTS ( SELECT '1' FROM po_line_locations pll
4293 WHERE pll.line_location_id = prl1.line_location_id
4294 AND pll.po_header_id = poh.po_header_id
4295 AND nvl(poh.authorization_status,'%%') <> 'APPROVED'))
4296 OR EXISTS (SELECT '1' FROM po_releases pr
4297 WHERE EXISTS ( SELECT '1' FROM po_line_locations pll
4298 WHERE pll.line_location_id = prl1.line_location_id
4299 AND pll.po_release_id = pr.po_release_id
4300 AND nvl(pr.authorization_status,'%%') <> 'APPROVED')));
4301 EXCEPTION
4302 WHEN NO_DATA_FOUND THEN
4303 l_req := 0;
4304 END;
4305 END;
4306 END; */ -- Added the below code for the bug # 7133795
4307
4308 IF ( p_document_detail_Id IS NOT NULL AND p_document_header_id IS NOT NULL) THEN
4309 BEGIN
4310 SELECT 1 INTO l_req
4311 FROM mtl_kanban_card_activity
4312 WHERE kanban_card_id = p_kanban_card_id AND
4313 document_detail_Id = p_document_detail_Id AND
4314 Document_header_id = p_Document_header_id;
4315 EXCEPTION
4316 WHEN NO_DATA_FOUND THEN
4317 l_req := 0;
4318 END;
4319 END IF;
4320
4321 IF l_req = 1 THEN
4322 p_update := FALSE;
4323 END IF;
4324 -- End of changes for bug # 7133795
4325 END IF;
4326
4327 END update_card_and_card_status;
4328
4329
4330 /* The following procedure Auto_Allocate_Kanban is added for 3905884.
4331 This procedure automatically allocates the move order created for a Kanaban
4332 Replenishment if the auto_allocate flag is set to "Yes" */
4333
4334 PROCEDURE Auto_Allocate_Kanban (
4335 p_mo_header_id IN NUMBER ,
4336 x_return_status OUT NOCOPY VARCHAR2 ,
4337 x_msg_count OUT NOCOPY NUMBER ,
4338 x_msg_data OUT NOCOPY VARCHAR2
4339 ) IS
4340
4341 l_txn_header_id NUMBER;
4342 l_txn_temp_id NUMBER;
4343 l_number_of_rows NUMBER := 0;
4344 l_detailed_quantity NUMBER := 0;
4345 l_revision VARCHAR2(100) := NULL;
4346 l_from_locator_id NUMBER := 0;
4347 l_to_locator_id NUMBER := 0;
4348 -- Increased lot size to 80 Char - Mercy Thomas - B4625329
4349 l_lot_number VARCHAR2(80);
4350 l_expiration_date DATE;
4351 l_serial_control_code VARCHAR2(1) ;
4352 l_move_order_type NUMBER := INV_GLOBALS.G_MOVE_ORDER_REPLENISHMENT;
4353 l_failed_lines NUMBER := 0;
4354 l_return_status VARCHAR2(1);
4355 l_msg_count NUMBER ;
4356 l_msg_data VARCHAR2(2000);
4357 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
4358
4359
4360 /*Cursor to get mo line informations */
4361 CURSOR mo_lines_cur IS
4362 SELECT MTRL.line_id , MTRL.inventory_item_id,MTRL.organization_id,MTRL.quantity
4363 FROM MTL_TXN_REQUEST_LINES MTRL
4364 WHERE MTRL.header_id = p_mo_header_id;
4365
4366 l_mo_line_rec mo_lines_cur%ROWTYPE;
4367 BEGIN
4368 IF (l_debug = 1 ) THEN
4369 inv_pick_wave_pick_confirm_pub.tracelog('In Auto_Allocate_Kanban ...','INV_KANBAN_PVT');
4370 END IF;
4371
4372 OPEN mo_lines_cur;
4373
4374 LOOP
4375 FETCH mo_lines_cur INTO l_mo_line_rec;
4376 EXIT WHEN mo_lines_cur%NOTFOUND;
4377
4378 /*Get the next header id*/
4379 SELECT mtl_material_transactions_s.NEXTVAL
4380 INTO l_txn_header_id
4381 FROM DUAL;
4382
4383 /* Check whether item is serial controlled or not */
4384 SELECT DECODE(serial_number_control_code,1,'F','T')
4385 INTO l_serial_control_code
4386 FROM mtl_system_items
4387 WHERE inventory_item_id = l_mo_line_rec.inventory_item_id
4388 AND organization_id = l_mo_line_rec.organization_id;
4389
4390 INV_Replenish_Detail_PUB.Line_Details_PUB (
4391 p_line_id => l_mo_line_rec.line_id,
4392 x_number_of_rows => l_number_of_rows,
4393 x_detailed_qty => l_detailed_quantity,
4394 x_return_status => x_return_status,
4395 x_msg_count => l_msg_count,
4396 x_msg_data => l_msg_data ,
4397 x_revision => l_revision,
4398 x_locator_id => l_from_locator_id ,
4399 x_transfer_to_location => l_to_locator_id,
4400 x_lot_number => l_lot_number,
4401 x_expiration_date => l_expiration_date,
4402 x_transaction_temp_id => l_txn_temp_id,
4403 p_transaction_header_id=> l_txn_header_id,
4404 p_transaction_mode => NULL ,
4405 p_move_order_type => l_move_order_type,
4406 p_serial_flag => l_serial_control_code,
4407 p_plan_tasks => FALSE ,
4408 p_commit => TRUE
4409 );
4410
4411 update mtl_txn_request_lines
4412 set quantity_detailed = l_detailed_quantity
4413 where line_id=l_mo_line_rec.line_id;
4414
4415 IF (l_debug = 1 ) THEN
4416 inv_pick_wave_pick_confirm_pub.tracelog('In Auto_Allocate_Kanban : the line '||l_mo_line_rec.line_id ||' return status :'||
4417 x_return_status || 'number_of_rows:' || l_number_of_rows ||' detailed_qty:'||l_detailed_quantity ||
4418 ' revision:'||l_revision||' from_locator_id:'||l_from_locator_id||' to_location:'||l_to_locator_id ||
4419 'lot_number:' || l_lot_number || ' transaction_temp_id' || l_txn_header_id ,'INV_KANBAN_PVT');
4420 END IF;
4421
4422 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS
4423 OR l_mo_line_rec.quantity <> l_detailed_quantity )
4424 THEN
4425 l_failed_lines := l_failed_lines + 1; --count the unallocated lines.
4426 END IF;
4427 END LOOP;
4428 CLOSE mo_lines_cur;
4429
4430 IF l_failed_lines > 0 THEN
4431 x_return_status := FND_API.G_RET_STS_ERROR;
4432 ELSE
4433 x_return_status := FND_API.G_RET_STS_SUCCESS;
4434 END IF;
4435
4436 IF (l_debug = 1 ) THEN
4437 inv_pick_wave_pick_confirm_pub.tracelog('In Auto_Allocate_Kanban : return status :'||x_return_status||' msg:'||x_msg_data,'INV_KANBAN_PVT');
4438 END IF;
4439 EXCEPTION
4440 WHEN OTHERS THEN
4441 IF (l_debug = 1 ) THEN
4442 inv_pick_wave_pick_confirm_pub.tracelog('In Auto_Allocate_Kanban : Exception : When Others','INV_KANBAN_PVT');
4443 x_return_status := FND_API.G_RET_STS_ERROR;
4444 END IF;
4445 END Auto_Allocate_Kanban;
4446
4447 /* Added below function for bug 7721127 */
4448 FUNCTION get_preprocessing_lead_time( p_organization_id IN NUMBER,
4449 p_inventory_item_id IN NUMBER)
4450 RETURN NUMBER
4451 IS
4452 l_preprocessing_lead_time NUMBER;
4453 BEGIN
4454
4455 SELECT NVL(preprocessing_lead_time,0)
4456 INTO l_preprocessing_lead_time
4457 FROM mtl_system_items_b
4458 WHERE inventory_item_id = p_inventory_item_id
4459 AND organization_id = p_organization_id ;
4460
4461 mydebug('In get_preprocessing_lead_time : l_preprocessing_lead_time :'||l_preprocessing_lead_time);
4462
4463 RETURN l_preprocessing_lead_time ;
4464 EXCEPTION
4465 WHEN OTHERS THEN
4466 mydebug('In get_preprocessing_lead_time : Exception : When Others'|| sqlerrm);
4467 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
4468 END get_preprocessing_lead_time;
4469
4470
4471 END INV_Kanban_PVT;