[Home] [Help]
PACKAGE BODY: APPS.INV_KANBAN_PVT
Source
1 PACKAGE BODY INV_Kanban_PVT as
2 /* $Header: INVVKBNB.pls 120.11.12010000.2 2008/07/29 13:48:23 ptkumar 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 Begin
727 FND_MSG_PUB.initialize;
728
729 IF p_Kanban_Card_Id is NULL THEN
730 FND_MESSAGE.SET_NAME('INV','INV_ATTRIBUTE_REQUIRED');
731 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','INV_KANBAN_CARD');
732 FND_MSG_PUB.Add;
733 l_return_status := FND_API.G_RET_STS_ERROR;
734 ELSIF p_supply_status is null THEN
735 FND_MESSAGE.SET_NAME('INV','INV_ATTRIBUTE_REQUIRED');
736 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','INV_SUPPLY_STATUS');
737 FND_MSG_PUB.Add;
738 l_return_status := FND_API.G_RET_STS_ERROR;
739 ELSE
740
741 l_kanban_card_rec := INV_KanbanCard_PKG.Query_Row
742 ( p_Kanban_Card_id => p_kanban_Card_Id);
743
744 --Bug 3288422 fix. Preventing replenishment 1) If lock cannot be
745 --acquired on the kanban_card_record 2) if the card is in hold
746 --OR cancel status
747 BEGIN
748 SELECT kanban_card_id
749 INTO l_temp
750 FROM MTL_KANBAN_CARDS
751 WHERE kanban_card_id = p_kanban_Card_Id
752 FOR UPDATE NOWAIT;
753 mydebug('Lock accuired for kanban card');
754 EXCEPTION
755 WHEN OTHERS THEN
756 l_return_status := FND_API.G_RET_STS_ERROR;
757 FND_MESSAGE.SET_NAME('INV','INV_CANNOT_LOCK_KANBAN_CARD');
758 FND_MSG_PUB.ADD;
759 END;
760
761 IF l_return_status = FND_API.g_ret_sts_error THEN
762 NULL;
763 ELSIF (l_kanban_card_rec.card_status = G_Card_Status_Hold ) THEN
764 l_return_status := FND_API.G_RET_STS_ERROR;
765 FND_MESSAGE.SET_NAME('INV','INV_CANNOT_REPL_HOLD_CARD');
766 FND_MSG_PUB.ADD;
767 ELSIF (l_kanban_card_rec.card_status = G_Card_Status_Cancel ) then
768 l_return_status := FND_API.G_RET_STS_ERROR;
769 FND_MESSAGE.SET_NAME('INV','INV_CANNOT_REPL_CANCEL_CARD');
770 FND_MSG_PUB.ADD;
771
772 --Bug 3288422 fix.
773 ELSE
774 IF INV_KanbanCard_PKG.Supply_Status_Change_OK
775 (l_kanban_card_rec.supply_status,
776 p_supply_status,
777 l_kanban_card_rec.card_status)
778 THEN
779 /*Bug 3740514 --If the supply status is Full and the source is Supplier,calling
780 a new procedure update_card_and_card_status to check if the correct
781 Release is being updated.*/
782
783 IF ( p_supply_status IN (INV_Kanban_PVT.G_Supply_Status_InProcess,INV_Kanban_PVT.G_Supply_Status_Full) AND
784 l_Kanban_Card_Rec.source_type = INV_Kanban_PVT.G_Source_Type_Supplier) THEN
785 update_card_and_card_status(
786 p_kanban_card_id => l_kanban_card_rec.kanban_card_id,
787 p_supply_status => p_supply_status,/*4490269*/
788 p_update => l_update);
789 END IF;
790
791 /*Bug 3740514--Only if l_update is TRUE will the kanban card details be updated to the
792 new values.*/
793
794 IF (l_update) THEN
795 mydebug('Supply status change OK');
796 l_kanban_card_rec.supply_status := p_supply_status;
797 l_kanban_card_rec.document_type := p_document_type;
798 l_kanban_card_rec.document_header_id := p_document_header_id;
799 l_kanban_card_rec.document_detail_id := p_document_detail_id;
800 l_kanban_card_rec.last_update_date := SYSDATE;
801 l_kanban_card_rec.last_updated_by := FND_GLOBAL.USER_ID;
802 l_kanban_card_rec.last_update_login := FND_GLOBAL.LOGIN_ID;
803 l_kanban_card_rec.replenish_quantity := p_replenish_quantity;
804 l_kanban_card_rec.need_by_date := p_need_by_date;
805 l_kanban_card_rec.source_wip_entity_id := p_source_wip_entity_id;
806 mydebug('calling INV_KanbanCard_PKG.Update_Row');
807 INV_KanbanCard_PKG.Update_Row(l_kanban_card_rec);
808 END IF;
809 ELSE
810 mydebug('Supply status change not OK');
811 If l_kanban_card_rec.card_status in
812 (INV_Kanban_PVT.G_Card_Status_Cancel,
813 INV_Kanban_PVT.G_Card_Status_Hold)
814 then
815 FND_MESSAGE.SET_TOKEN('CARD_NUMBER',l_kanban_card_rec.kanban_card_number);
816 End If;
817 l_return_status := FND_API.G_RET_STS_ERROR;
818 FND_MSG_PUB.Add;
819 END IF;--IF INV_KanbanCard_PKG.Supply_Status_Change_OK
820 END IF;--IF l_return_status = FND_API.g_ret_sts_error
821 END IF;--IF p_Kanban_Card_Id is NULL THEN
822
823 IF l_return_status = FND_API.g_ret_sts_error THEN
824 Raise FND_API.G_EXC_ERROR;
825 end if;
826
827 x_return_status := l_return_status;
828
829 EXCEPTION
830
831 WHEN FND_API.G_EXC_ERROR THEN
832
833 x_return_status := FND_API.G_RET_STS_ERROR;
834
835 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
836
837 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
838
839 WHEN OTHERS THEN
840
841 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
842 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
843 THEN
844 FND_MSG_PUB.Add_Exc_Msg
845 ( G_PKG_NAME
846 , 'Update_Card_Supply_Status'
847 );
848 END IF;
849
850 End Update_Card_Supply_Status;
851 --
852 --
853 -- Update_Card_Supply_Status : This procedure updates the supply status
854 -- for a kanban card id record.
855 --
856 --
857 PROCEDURE Update_Card_Supply_Status(X_Return_Status Out NOCOPY Varchar2,
858 p_Kanban_card_Id Number,
859 p_Supply_Status Number)
860 IS
861 l_document_type Number;
862 l_document_header_id Number;
863 l_Document_detail_id Number;
864
865 l_quantity_delivered Number;
866 l_quantity Number;
867 l_reference_type_code Number;
868 l_move_order_line_id Number;
869 l_Supply_Status Number;
870
871 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
872 Begin
873 l_Supply_Status := p_Supply_Status;
874
875 -- Bug 3032156. The following IF condition has been added to keep the
876 -- supply status to 'Full' while partially transacting the move order.
877 -- The status changes to 'InProcess if 'INV_FILL_PARTIAL_MOVE_KANBAN'
878 -- profile value is set to 'No' and the IF conditions are satisfied.
879
880 if nvl(fnd_profile.value('INV_FILL_PARTIAL_MOVE_KANBAN'),2) = 2 then
881 -- Bug 2383538 Checking the mtl_txn_request_lines when partially
882 -- transacting the move order to change the supply status to Inprocess
883 Begin
884 select line_id, nvl(quantity,0), nvl(reference_type_code,0)
885 into l_move_order_line_id, l_quantity, l_reference_type_code
886 from mtl_txn_request_lines
887 where reference_type_code = 1
888 and reference_id = p_Kanban_card_Id
889 and line_status in (3,7);
890 if (l_reference_type_code = 1) then
891 select sum(abs(transaction_quantity)) into l_quantity_delivered from
892 mtl_material_transactions where
893 move_order_line_id = l_move_order_line_id and
894 transaction_quantity < 0;
895 if (nvl(l_quantity_delivered,0) < l_quantity) then
896 l_Supply_Status := INV_KANBAN_PVT.G_Supply_Status_InProcess;
897 end if;
898 end if;
899 Exception When Others Then
900 Null;
901 End;
902 End if;
903
904 Update_Card_Supply_Status(X_Return_Status => x_Return_Status,
905 p_kanban_card_Id => p_Kanban_Card_Id,
906 p_Supply_Status => l_Supply_Status,
907 p_Document_type => l_document_type,
908 p_Document_Header_Id => l_document_header_id,
909 p_Document_detail_Id => l_Document_detail_id);
910
911
912 EXCEPTION
913
914 WHEN FND_API.G_EXC_ERROR THEN
915
916 x_return_status := FND_API.G_RET_STS_ERROR;
917
918 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
919
920 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
921
922 WHEN OTHERS THEN
923
924 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
925 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
926 THEN
927 FND_MSG_PUB.Add_Exc_Msg
928 ( G_PKG_NAME
929 , 'Update_Card_Supply_Status'
930 );
931 END IF;
932
933 End Update_Card_Supply_Status;
934 --
935 --
936 -- Update_Card_Supply_Status : This procedure updates the supply status
937 -- for a kanban card id record.
938 --
939 PROCEDURE Update_Card_Supply_Status
940 ( p_api_version_number IN NUMBER
941 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
942 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
943 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
944 ,x_return_status OUT NOCOPY VARCHAR2
945 ,x_msg_count OUT NOCOPY NUMBER
946 ,x_msg_data OUT NOCOPY VARCHAR2
947 ,p_Kanban_Card_Id Number
948 ,p_Supply_Status NUMBER
949 ,p_Document_type IN NUMBER DEFAULT NULL
950 ,p_Document_Header_Id IN NUMBER DEFAULT NULL
951 ,p_Document_detail_Id IN NUMBER DEFAULT NULL
952 ,p_replenish_quantity IN NUMBER DEFAULT NULL
953 ,p_need_by_date IN DATE DEFAULT NULL
954 ,p_source_wip_entity_id IN NUMBER DEFAULT NULL)
955 IS
956
957 l_api_version_number CONSTANT NUMBER := 1.0;
958 l_api_name CONSTANT VARCHAR2(30):= 'Update_Card_Supply_Status';
959 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
960 l_document_type Number;
961 l_document_header_id Number;
962 l_document_detail_id Number;
963
964 l_msg_data VARCHAR2(255);
965 l_msg_count NUMBER := NULL;
966
967 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
968 Begin
969
970 -- Standard Start of API savepoint
971
972 SAVEPOINT KANBAN_PVT;
973 mydebug('Inside Update_Card_Supply_Status 1');
974 -- Standard call to check for call compatibility
975
976 IF NOT FND_API.Compatible_API_Call
977 ( l_api_version_number
978 , p_api_version_number
979 , l_api_name
980 , G_PKG_NAME
981 )
982 THEN
983 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
984 END IF;
985
986 -- Initialize message list.
987
988 IF FND_API.to_Boolean(p_init_msg_list) THEN
989 FND_MSG_PUB.initialize;
990 END IF;
991
992 mydebug('Calling Update_Card_Supply_Status 2');
993 Update_Card_Supply_Status(X_Return_Status => l_Return_Status,
994 p_kanban_card_Id => p_Kanban_Card_Id,
995 p_Supply_Status => p_Supply_Status,
996 p_Document_type => l_document_type,
997 p_Document_Header_Id => l_document_header_id,
998 p_Document_detail_Id => l_document_detail_id,
999 p_replenish_quantity => p_replenish_quantity,
1000 p_need_by_date => p_need_by_date,
1001 p_source_wip_entity_id => p_source_wip_entity_id);
1002
1003 x_return_status := l_return_status;
1004
1005 -- Standard check of p_commit.
1006
1007 IF FND_API.To_Boolean( p_commit ) THEN
1008 COMMIT WORK;
1009 END IF;
1010
1011 -- Standard call to get message count and if count is 1, get message info.
1012 FND_MSG_PUB.Count_And_Get
1013 ( p_count => x_msg_count ,
1014 p_data => x_msg_data
1015 );
1016
1017 EXCEPTION
1018
1019 WHEN FND_API.G_EXC_ERROR THEN
1020 ROLLBACK TO KANBAN_PVT;
1021 x_return_status := FND_API.G_RET_STS_ERROR ;
1022 FND_MSG_PUB.Count_And_Get
1023 ( p_count => x_msg_count ,
1024 p_data => x_msg_data
1025 );
1026 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1027 ROLLBACK TO KANBAN_PVT;
1028 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1029 FND_MSG_PUB.Count_And_Get
1030 ( p_count => x_msg_count ,
1031 p_data => x_msg_data
1032 );
1033
1034 WHEN OTHERS THEN
1035 ROLLBACK TO KANBAN_PVT;
1036 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1037 IF FND_MSG_PUB.Check_Msg_Level
1038 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1039 THEN
1040 FND_MSG_PUB.Add_Exc_Msg
1041 ( G_PKG_NAME ,
1042 l_api_name
1043 );
1044 END IF;
1045 FND_MSG_PUB.Count_And_Get
1046 ( p_count => x_msg_count ,
1047 p_data => x_msg_data
1048 );
1049
1050 End Update_Card_Supply_Status;
1051 --
1052 --
1053 -- This package is for those cards which do not have Document_detail_Id.
1054 --
1055 --
1056 PROCEDURE Update_Card_Supply_Status(X_Return_Status Out NOCOPY Varchar2,
1057 p_Kanban_Card_Id Number,
1058 p_Supply_Status Number,
1059 p_Document_type Number,
1060 p_Document_Header_Id Number)
1061 IS
1062 l_Document_detail_id Number := FND_API.G_MISS_NUM;
1063
1064 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1065 Begin
1066
1067 Update_Card_Supply_Status(X_Return_Status => x_Return_Status,
1068 p_kanban_card_Id => p_Kanban_Card_Id,
1069 p_Supply_Status => p_Supply_Status,
1070 p_Document_type => p_document_type,
1071 p_Document_Header_Id => p_document_header_id,
1072 p_Document_detail_Id => l_Document_detail_id);
1073
1074
1075 EXCEPTION
1076
1077 WHEN FND_API.G_EXC_ERROR THEN
1078
1079 x_return_status := FND_API.G_RET_STS_ERROR;
1080
1081 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1082
1083 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1084
1085 WHEN OTHERS THEN
1086
1087 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1088 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1089 THEN
1090 FND_MSG_PUB.Add_Exc_Msg
1091 ( G_PKG_NAME
1092 , 'Update_Card_Supply_Status'
1093 );
1094 END IF;
1095
1096 End Update_Card_Supply_Status;
1097 --
1098 --
1099 -- Valid_Kanban_Cards_Exist : This procedure checks whether "Active" or "Hold"
1100 -- Kanban cards exists for a pull sequence.
1101 --
1102 FUNCTION Valid_Kanban_Cards_Exist(p_Pull_sequence_id number)
1103 Return Boolean
1104 Is
1105 l_dummy varchar2(1);
1106
1107 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1108 BEGIN
1109
1110 Select 'x'
1111 INTO l_dummy
1112 FROM MTL_KANBAN_CARDS
1113 WHERE pull_sequence_id = p_pull_sequence_id
1114 AND (card_status = INV_Kanban_PVT.G_Card_Status_Active or
1115 card_status = INV_Kanban_PVT.G_Card_Status_Hold);
1116 Raise Too_Many_Rows;
1117
1118 Exception
1119 When No_data_found Then
1120 return FALSE;
1121
1122 When Too_many_rows Then
1123 return TRUE;
1124 When Others Then
1125
1126 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1127 THEN
1128 FND_MSG_PUB.Add_Exc_Msg
1129 ( G_PKG_NAME
1130 , 'Valid_kanban_Cards_exist'
1131 );
1132 END IF;
1133
1134 END Valid_kanban_Cards_exist;
1135 --
1136 -- Diff_Qty_Kanban_Cards_Exist : Check the existence of valid kanban cards for
1137 -- a pull sequence, which have the same Point of Supply
1138 -- but different quantity
1139 --
1140 -- For bug 5334353, changed return type of function to number
1141 -- Retunns 0 : When card with supply status Wait or Inprocess exists
1142 -- Returns 1 : When card with diff qty exists
1143 -- Returns 2: When both 0 and 1 conditions does not satisfy
1144 FUNCTION Diff_Qty_Kanban_Cards_Exist(
1145 p_pull_sequence_id number,
1146 p_source_type number,
1147 p_supplier_id number,
1148 p_supplier_site_id number,
1149 p_source_organization_id number,
1150 p_source_subinventory varchar2,
1151 p_source_locator_id number,
1152 p_wip_line_id number,
1153 p_kanban_size number)
1154 Return Number
1155 Is
1156 l_dummy varchar2(1);
1157 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1158 BEGIN
1159 Select 'x'
1160 INTO l_dummy
1161 FROM MTL_KANBAN_CARDS
1162 WHERE pull_sequence_id = p_pull_sequence_id
1163 AND (card_status = INV_Kanban_PVT.G_Card_Status_Active or
1164 card_status = INV_Kanban_PVT.G_Card_Status_Hold)
1165 -- AND nvl(p_kanban_size,-1) > 0
1166 -- AND kanban_size <> nvl(p_kanban_size,-1)
1167 AND source_type = nvl(p_source_type,-1)
1168 AND (((source_type = 1 or source_type = 3)
1169 and nvl(source_organization_id,-1) = nvl(p_source_organization_id,-1)
1170 and nvl(source_subinventory,'#?#?') = nvl(p_source_subinventory,'#?#?')
1171 and nvl(source_locator_id,-1) = nvl(p_source_locator_id,-1))
1172 OR
1173 (source_type = 2
1174 and ((nvl(supplier_id,-1) = nvl(p_supplier_id,-1)
1175 and nvl(supplier_site_id,-1) = nvl(p_supplier_site_id,-1))
1176 or p_supplier_id is null))
1177 OR
1178 (source_type = 4
1179 and (nvl(wip_line_id,-1) = nvl(p_wip_line_id,-1)
1180 or p_wip_line_id is null))
1181 )
1182 AND supply_status in (3,5) --sbitra
1183 ;
1184 Raise Too_Many_Rows;
1185 Exception
1186 When No_data_found Then
1187 begin
1188 Select 'x'
1189 INTO l_dummy
1190 FROM MTL_KANBAN_CARDS
1191 WHERE pull_sequence_id = p_pull_sequence_id
1192 AND (card_status = INV_Kanban_PVT.G_Card_Status_Active or
1193 card_status = INV_Kanban_PVT.G_Card_Status_Hold)
1194 AND nvl(p_kanban_size,-1) > 0
1195 AND kanban_size <> nvl(p_kanban_size,-1)
1196 AND source_type = nvl(p_source_type,-1)
1197 AND (((source_type = 1 or source_type = 3)
1198 and nvl(source_organization_id,-1) = nvl(p_source_organization_id,-1)
1199 and nvl(source_subinventory,'#?#?') = nvl(p_source_subinventory,'#?#?')
1200 and nvl(source_locator_id,-1) = nvl(p_source_locator_id,-1))
1201 OR
1202 (source_type = 2
1203 and ((nvl(supplier_id,-1) = nvl(p_supplier_id,-1)
1204 and nvl(supplier_site_id,-1) = nvl(p_supplier_site_id,-1))
1205 or p_supplier_id is null))
1206 OR
1207 (source_type = 4
1208 and (nvl(wip_line_id,-1) = nvl(p_wip_line_id,-1)
1209 or p_wip_line_id is null))
1210 )
1211 ;
1212 Raise Too_Many_Rows;
1213 exception
1214 when no_data_found then
1215 return 2; -----No cards with status wait/inprocess and diff qty
1216 when too_many_rows then
1217 return 1; -----Cards exists with diff qty
1218 end;
1219
1220 When Too_many_rows Then
1221 return 0; -------Cards with status wait/inprocess exists
1222 When Others Then
1223 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1224 THEN
1225 FND_MSG_PUB.Add_Exc_Msg
1226 ( G_PKG_NAME
1227 , 'Diff_Qty_Kanban_Cards_Exist'
1228 );
1229 END IF;
1230
1231 END Diff_Qty_Kanban_Cards_Exist;
1232 --
1233 --
1234 -- Ok_To_Create_Kanban_Cards : This procedure checks whether kanban cards can
1235 -- be generated for a pull sequences.
1236 --
1237 --
1238 FUNCTION Ok_To_Create_Kanban_Cards(p_Pull_sequence_id number)
1239 Return Boolean
1240 IS
1241
1242 l_org_code varchar2(3);
1243 l_item_name varchar2(100);
1244 l_subinventory varchar2(10);
1245 l_loc_name varchar2(100);
1246
1247 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1248 BEGIN
1249
1250 if Valid_Kanban_Cards_exist(p_pull_sequence_id)
1251 then
1252 Get_Pull_sequence_Tokens(p_pull_sequence_id,l_org_code,
1253 l_item_name,l_subinventory,l_loc_name);
1254 FND_MESSAGE.SET_NAME('INV','INV_CANT_GEN_CRDS_CARDS_EXIST');
1255 FND_MESSAGE.SET_TOKEN('ORG_CODE',l_org_code);
1256 FND_MESSAGE.SET_TOKEN('ITEM_NAME',l_item_name);
1257 FND_MESSAGE.SET_TOKEN('SUB_CODE',l_subinventory);
1258 FND_MESSAGE.SET_TOKEN('LOCATOR_NAME',l_loc_name);
1259 return false;
1260 end if;
1261 return TRUE;
1262
1263 END Ok_To_Create_Kanban_Cards;
1264 --
1265 --
1266 -- Ok_To_Delete_Pull_Sequence : This procedure checks whether a pull
1267 -- sequence can be deleted.
1268 --
1269 --
1270 FUNCTION Ok_To_Delete_Pull_Sequence(p_Pull_sequence_id number)
1271 RETURN BOOLEAN
1272 IS
1273
1274 l_org_code varchar2(3);
1275 l_item_name varchar2(100);
1276 l_subinventory varchar2(10);
1277 l_loc_name varchar2(100);
1278
1279 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1280 BEGIN
1281 if Valid_Kanban_Cards_exist(p_pull_sequence_id)
1282 then
1283 Get_Pull_sequence_Tokens(p_pull_sequence_id,l_org_code,
1284 l_item_name,l_subinventory,l_loc_name);
1285 FND_MESSAGE.SET_NAME('INV','INV_CANNOT_DELETE_PULLSEQ');
1286 FND_MESSAGE.SET_TOKEN('ORG_CODE',l_org_code);
1287 FND_MESSAGE.SET_TOKEN('ITEM_NAME',l_item_name);
1288 FND_MESSAGE.SET_TOKEN('SUB_CODE',l_subinventory);
1289 FND_MESSAGE.SET_TOKEN('LOCATOR_NAME',l_loc_name);
1290 return false;
1291 end if;
1292 return TRUE;
1293
1294 END Ok_To_Delete_Pull_sequence;
1295
1296 --
1297 --
1298 -- Get_Kanban_Tokens : This procedure gets the names required to
1299 -- build the message for Kanban Validation.
1300 --
1301 PROCEDURE Get_Kanban_Tokens
1302 ( p_kanban_id Number,
1303 p_org_id Number,
1304 p_item_id Number,
1305 p_loc_id Number,
1306 x_org_code Out NOCOPY varchar2,
1307 x_item_name Out NOCOPY varchar2,
1308 x_loc_name Out NOCOPY varchar2,
1309 x_kanban_num Out NOCOPY varchar2 )
1310 IS
1311
1312 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1313 Begin
1314 Select concatenated_segments,organization_code,kanban_card_number
1315 into x_item_name,x_org_code,x_kanban_num
1316 from mtl_system_items_kfv a , mtl_parameters b, mtl_kanban_cards card
1317 where a.inventory_item_id = p_item_id
1318 and a.organization_id = p_org_id
1319 and b.organization_id = p_org_id
1320 and card.kanban_card_id = p_kanban_id;
1321
1322 if ( nvl(p_loc_id,0) <> 0 ) Then
1323
1324 Select concatenated_segments
1325 into x_loc_name
1326 from mtl_item_locations_kfv
1327 where inventory_location_id = p_loc_id
1328 and organization_id = p_loc_id;
1329 end if;
1330 Exception
1331
1332 When Others Then
1333 Null;
1334
1335 End Get_kanban_Tokens;
1336
1337 --
1338 -- Valid_Production_Kanban_Card : This function will check the validity of
1339 -- of a production kanban Card.
1340 --
1341 FUNCTION Valid_Production_Kanban_Card( p_wip_entity_id number,
1342 p_org_id number,
1343 p_kanban_id number,
1344 p_inv_item_id number,
1345 p_subinventory varchar2,
1346 p_locator_id number )
1347 Return Boolean IS
1348 l_kanban_card_id number;
1349 l_dummy varchar2(1);
1350 l_proceed varchar2(1);
1351 l_subinventory varchar2(10);
1352 l_loc_id number;
1353 l_item_id number;
1354 l_org_id number;
1355 l_source_type number;
1356 l_supply_status number;
1357 x_org_code varchar2(3);
1358 x_item_name varchar2(200);
1359 x_loc_name varchar2(200);
1360 x_kanban_num varchar2(30);
1361
1362 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1363 BEGIN
1364 begin
1365 select subinventory_name, nvl(locator_id,0), inventory_item_id,
1366 organization_id, source_type, supply_status
1367 into l_subinventory, l_loc_id, l_item_id,
1368 l_org_id, l_source_type, l_supply_status
1369 from mtl_kanban_cards
1370 where kanban_card_id = p_kanban_id;
1371 exception
1372 When NO_DATA_FOUND then
1373 FND_MESSAGE.SET_NAME('INV','INV_KANBAN_CARD_NOT_FOUND');
1374 FND_MESSAGE.SET_TOKEN('CARDID',to_char(p_kanban_id) );
1375 RETURN FALSE;
1376 When OTHERS then
1377 FND_MSG_PUB.Add_Exc_Msg
1378 ( G_PKG_NAME
1379 , 'Valid_Production_Kanban_Card'
1380 );
1381 RETURN FALSE;
1382 end;
1383
1384 Get_Kanban_Tokens(p_kanban_id , p_org_id , p_inv_item_id ,
1385 p_locator_id , x_org_code , x_item_name ,
1386 x_loc_name , x_kanban_num );
1387
1388 if ( (l_item_id = p_inv_item_id) AND (l_org_id = p_org_id) ) then
1389 l_proceed := 'Y';
1390 else
1391 FND_MESSAGE.SET_NAME('INV','INV_KANBAN_INVALID_ITEM_ORG');
1392 FND_MESSAGE.SET_TOKEN('CARD_NUM',x_kanban_num );
1393 FND_MESSAGE.SET_TOKEN('ORG_CODE',x_org_code );
1394 FND_MESSAGE.SET_TOKEN('ITEM',x_item_name );
1395 RETURN FALSE;
1396 end if;
1397 if ( ( l_subinventory = p_subinventory ) AND
1398 ( l_loc_id = nvl(p_locator_id,l_loc_id)) ) then
1399 l_proceed := 'Y';
1400 else
1401 FND_MESSAGE.SET_NAME('INV','INV_KANBAN_INVALID_CMPL_DEST');
1402 FND_MESSAGE.SET_TOKEN('CARD_NUM',x_kanban_num );
1403 FND_MESSAGE.SET_TOKEN('SUB',p_subinventory);
1404 FND_MESSAGE.SET_TOKEN('LOC',x_loc_name);
1405 RETURN FALSE;
1406 end if;
1407 if ( l_source_type = INV_KANBAN_PVT.G_Source_Type_Production ) then
1408 l_proceed := 'Y';
1409 else
1410 FND_MESSAGE.SET_NAME('INV','INV_KANBAN_NOT_PRODUCTION');
1411 FND_MESSAGE.SET_TOKEN('CARD_NUM',x_kanban_num );
1412 RETURN FALSE;
1413 end if;
1414 if ( l_supply_status in (INV_KANBAN_PVT.G_Supply_Status_Empty,
1415 INV_KANBAN_PVT.G_Supply_Status_InProcess ) ) then
1416 Return TRUE;
1417 else
1418 begin
1419 select 'x' into l_dummy
1420 from mtl_kanban_card_activity
1421 where kanban_card_id = p_kanban_id
1422 and organization_id = p_org_id
1423 and document_header_id = p_wip_entity_id
1424 and source_type = INV_KANBAN_PVT.G_Source_Type_Production
1425 and supply_status = INV_KANBAN_PVT.G_Supply_Status_Full;
1426
1427 Raise Too_many_rows;
1428
1429 exception
1430 When No_data_found Then
1431 FND_MESSAGE.SET_NAME('INV','INV_KANBAN_INVALID_SUP_STATUS');
1432 FND_MESSAGE.SET_TOKEN('CARD_NUM',x_kanban_num );
1433 return FALSE;
1434 When Too_many_rows Then
1435 return TRUE;
1436 When Others Then
1437 FND_MSG_PUB.Add_Exc_Msg
1438 ( G_PKG_NAME
1439 , 'Valid_Prod_kanban_Card'
1440 );
1441 return FALSE;
1442 end ;
1443 end if;
1444
1445 END Valid_Production_Kanban_Card;
1446
1447 --
1448 --
1449 -- Delete_Kanban_Cards : This procedure deletes kanban cards for
1450 -- a pull sequence.
1451 --
1452 --
1453 PROCEDURE Delete_Kanban_Cards(p_Pull_sequence_id number)
1454 IS
1455 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1456 BEGIN
1457
1458 INV_KanbanCard_PKG.Delete_Cards_For_Pull_Seq(p_pull_sequence_id);
1459
1460 END Delete_Kanban_Cards;
1461 --
1462 --
1463 -- Create_Kanban_Cards : This procedure generates kanban cards for
1464 -- a pull sequence.
1465 --
1466 --
1467 PROCEDURE Create_Kanban_Cards
1468 ( X_return_status OUT NOCOPY VARCHAR2,
1469 X_Kanban_Card_Ids OUT NOCOPY INV_Kanban_PVT.Kanban_Card_Id_Tbl_Type,
1470 P_Pull_Sequence_Rec INV_Kanban_PVT.Pull_Sequence_Rec_Type,
1471 p_Supply_Status NUMBER
1472 )
1473 IS
1474 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
1475 l_kanban_Card_tbl INV_Kanban_PVT.Kanban_Card_Id_Tbl_Type;
1476 l_Kanban_Card_Id number;
1477 l_Kanban_Card_Number number;
1478 l_supply_status Number;
1479 l_Card_status Number;
1480 l_Current_Replnsh_Cycle_Id Number;
1481 l_card_count number := 0;
1482 l_item_name varchar2(2000);
1483 l_loc_name varchar2(2000);
1484 l_subinventory varchar2(10);
1485 l_org_code varchar2(3);
1486
1487 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1488 BEGIN
1489
1490 FND_MSG_PUB.initialize;
1491 l_kanban_Card_tbl.Delete;
1492
1493
1494
1495
1496 IF(p_pull_sequence_rec.release_kanban_flag =2) THEN
1497 l_card_status := INV_Kanban_Pvt.G_Card_Status_Hold;
1498 l_supply_status := inv_kanban_pvt.g_supply_status_full;
1499 ELSE
1500 l_card_status := INV_Kanban_Pvt.G_Card_Status_Active;
1501 l_supply_status := p_Supply_Status;
1502 END IF ;
1503
1504 l_Current_Replnsh_Cycle_Id := null;
1505
1506 if nvl(P_Pull_Sequence_Rec.NUMBER_OF_CARDS,0) <= 0
1507 then
1508
1509 Get_Pull_sequence_Tokens(p_pull_sequence_rec.pull_sequence_id,l_org_code,
1510 l_item_name,l_subinventory,l_loc_name);
1511 FND_MESSAGE.SET_NAME('INV','INV_CANT_GEN_CRDS_NO_NUM_CARDS');
1512 FND_MESSAGE.SET_TOKEN('ORG_CODE',l_org_code);
1513 FND_MESSAGE.SET_TOKEN('ITEM_NAME',l_item_name);
1514 FND_MESSAGE.SET_TOKEN('SUB_CODE',l_Subinventory);
1515 FND_MESSAGE.SET_TOKEN('LOCATOR_NAME',l_loc_name);
1516 FND_MSG_PUB.Add;
1517
1518 l_return_status := FND_API.G_RET_STS_ERROR;
1519
1520 end if;
1521
1522 if nvl(P_Pull_Sequence_Rec.Kanban_Size,0) <= 0
1523 then
1524 if l_org_code is null
1525 then
1526 Get_Pull_sequence_Tokens(p_pull_sequence_rec.pull_sequence_id,l_org_code,
1527 l_item_name,l_subinventory,l_loc_name);
1528 end if;
1529 FND_MESSAGE.SET_NAME('INV','INV_CANT_GEN_CRDS_NO_KBN_SIZE');
1530 FND_MESSAGE.SET_TOKEN('ORG_CODE',l_org_code);
1531 FND_MESSAGE.SET_TOKEN('ITEM_NAME',l_item_name);
1532 FND_MESSAGE.SET_TOKEN('SUB_CODE',l_Subinventory);
1533 FND_MESSAGE.SET_TOKEN('LOCATOR_NAME',l_loc_name);
1534 FND_MSG_PUB.Add;
1535 l_return_status := FND_API.G_RET_STS_ERROR;
1536
1537 end if;
1538
1539 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1540 RAISE FND_API.G_EXC_ERROR;
1541
1542 END IF;
1543
1544 --Existing kanban cards need to be cancelled only when creating a
1545 --regular replenishable card
1546 IF p_pull_sequence_rec.kanban_card_type <>
1547 INV_Kanban_Pvt.g_card_type_nonreplenishable then
1548 -- we should not delete old kanban cards but change their status
1549 -- for historical purposes.
1550 --Delete_Kanban_Cards(p_Pull_Sequence_Rec.Pull_Sequence_Id);
1551 update_kanban_card_status
1552 (p_card_status => g_card_status_cancel,
1553 p_pull_sequence_id => p_Pull_Sequence_Rec.pull_sequence_id);
1554 X_Kanban_Card_Ids := l_kanban_Card_tbl;
1555 END IF;
1556
1557
1558 for l_card_count in 1..p_Pull_Sequence_Rec.Number_Of_Cards
1559 loop
1560
1561 l_Kanban_Card_Id := NULL;
1562 l_Kanban_Card_Number := NULL;
1563
1564 INV_KanbanCard_PKG.Insert_Row(
1565 X_Return_Status => l_Return_Status,
1566 P_Kanban_Card_Id => l_Kanban_Card_Id,
1567 P_Kanban_Card_Number => l_Kanban_Card_Number,
1568 P_Pull_Sequence_Id => p_Pull_Sequence_Rec.Pull_Sequence_Id,
1569 P_Inventory_item_id => p_Pull_Sequence_Rec.Inventory_item_id,
1570 P_Organization_id => p_Pull_Sequence_Rec.Organization_id,
1571 P_Subinventory_name => p_Pull_Sequence_Rec.Subinventory_name,
1572 P_Supply_Status => l_Supply_Status,
1573 P_Card_Status => l_Card_Status,
1574 P_Kanban_Card_Type => Nvl(p_pull_sequence_rec.kanban_card_type,INV_Kanban_Pvt.g_card_type_replenishable),
1575 P_Source_type => p_Pull_Sequence_Rec.Source_type,
1576 P_Kanban_size => nvl(p_Pull_Sequence_Rec.Kanban_size,0),
1577 P_Last_Update_Date => SYSDATE,
1578 P_Last_Updated_By => FND_GLOBAL.USER_ID,
1579 P_Creation_Date => SYSDATE,
1580 P_Created_By => FND_GLOBAL.USER_ID,
1581 P_Last_Update_Login => FND_GLOBAL.LOGIN_ID,
1582 P_Last_Print_Date => NULL,
1583 P_Locator_id => p_Pull_Sequence_Rec.Locator_id,
1584 P_Supplier_id => p_Pull_Sequence_Rec.Supplier_id,
1585 P_Supplier_site_id => p_Pull_Sequence_Rec.Supplier_site_id,
1586 P_Source_Organization_id => p_Pull_Sequence_Rec.Source_Organization_id,
1587 P_Source_Subinventory => p_Pull_Sequence_Rec.Source_Subinventory,
1588 P_Source_Locator_id => p_Pull_Sequence_Rec.Source_Locator_id,
1589 P_wip_line_id => p_Pull_Sequence_Rec.wip_line_id,
1590 P_Current_Replnsh_Cycle_Id=> l_Current_Replnsh_Cycle_Id,
1591 P_document_type => NULL,
1592 P_document_header_id => NULL,
1593 P_document_detail_id => NULL,
1594 P_error_code => NULL,
1595 P_Attribute_Category => NULL,
1596 P_Attribute1 => NULL,
1597 P_Attribute2 => NULL,
1598 P_Attribute3 => NULL,
1599 P_Attribute4 => NULL,
1600 P_Attribute5 => NULL,
1601 P_Attribute6 => NULL,
1602 P_Attribute7 => NULL,
1603 P_Attribute8 => NULL,
1604 P_Attribute9 => NULL,
1605 P_Attribute10 => NULL,
1606 P_Attribute11 => NULL,
1607 P_Attribute12 => NULL,
1608 P_Attribute13 => NULL,
1609 P_Attribute14 => NULL,
1610 P_Attribute15 => NULL,
1611 P_Request_Id => NULL,
1612 P_Program_application_Id => NULL,
1613 P_Program_Id => NULL,
1614 P_Program_Update_date => NULL,
1615 p_release_kanban_flag => p_Pull_Sequence_Rec.release_kanban_flag);
1616
1617 if l_return_status = FND_API.G_RET_STS_ERROR
1618 Then
1619 Raise FND_API.G_EXC_ERROR;
1620 End if;
1621
1622 if l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
1623 Then
1624 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
1625 End If;
1626
1627 X_Kanban_Card_Ids(l_card_count) := l_Kanban_Card_Id;
1628
1629 end loop;
1630
1631 x_return_status := l_return_status;
1632
1633 EXCEPTION
1634
1635 WHEN FND_API.G_EXC_ERROR THEN
1636
1637 x_return_status := FND_API.G_RET_STS_ERROR;
1638
1639 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1640
1641 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1642
1643 WHEN OTHERS THEN
1644
1645 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1646 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1647 THEN
1648 FND_MSG_PUB.Add_Exc_Msg
1649 ( G_PKG_NAME
1650 , 'Create_Kanban_Cards'
1651 );
1652 END IF;
1653
1654 END Create_Kanban_Cards;
1655
1656 --
1657 -- Get_Next_Replenish_Cycle_Id() : This function will generate and return
1658 -- replenish_cycle_id
1659 --
1660
1661 FUNCTION Get_Next_Replenish_Cycle_Id
1662 Return Number
1663 Is
1664 l_next_replenish_cycle_Id Number;
1665
1666 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1667 Begin
1668
1669 Select MTL_KANBAN_REPLENISH_CYCLE_S.NextVal
1670 Into l_next_replenish_cycle_Id
1671 From Dual;
1672 Return(l_next_replenish_cycle_Id);
1673
1674 End Get_Next_Replenish_Cycle_Id;
1675
1676 --
1677 --
1678 -- Create_Requisition : This would create Internal/PO requisition
1679 -- for a kanban card.
1680 --
1681 Procedure Create_Requisition( p_buyer_id IN NUMBER,
1682 p_interface_source_code IN VARCHAR2,
1683 p_requisition_type IN VARCHAR2,
1684 p_approval IN VARCHAR2,
1685 p_source_type_code IN VARCHAR2,
1686 p_kanban_card_rec_tbl IN Kanban_Card_Tbl_Type,
1687 p_destination_type_code IN VARCHAR2,
1688 p_deliver_location_id IN NUMBER,
1689 p_revision IN VARCHAR2,
1690 p_item_description IN VARCHAR2,
1691 p_primary_uom_code IN VARCHAR2,
1692 p_need_by_date IN DATE,
1693 p_charge_account_id IN NUMBER,
1694 p_accrual_account_id IN NUMBER,
1695 p_invoice_var_account_id IN NUMBER,
1696 p_budget_account_id IN NUMBER,
1697 p_autosource_flag IN VARCHAR2,
1698 p_po_org_id IN NUMBER ) IS
1699
1700 l_project_id NUMBER :=null;
1701 l_task_id NUMBER := null;
1702 l_project_reference_enabled NUMBER;
1703 l_project_accounting_context VARCHAR2(30);
1704 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1705 begin
1706
1707 For l_order_count in 1..p_Kanban_Card_Rec_Tbl.Count
1708 Loop
1709
1710 -- Bug 1924497
1711
1712 l_project_accounting_context := null;
1713
1714 SELECT NVL(project_reference_enabled,2)
1715 INTO l_project_reference_enabled
1716 FROM mtl_parameters
1717 WHERE organization_id = p_kanban_card_rec_tbl(1).organization_id;
1718
1719 IF (l_project_reference_enabled = 1)THEN
1720 IF (p_kanban_card_rec_tbl(1).locator_id IS NOT NULL)THEN
1721 SELECT project_id
1722 INTO l_project_id
1723 FROM mtl_item_locations
1724 WHERE inventory_location_id = p_kanban_card_rec_tbl(1).locator_id
1725 AND organization_id = p_kanban_card_rec_tbl(1).organization_id;
1726 END IF;
1727 IF (l_project_id IS NOT NULL)THEN
1728 l_project_accounting_context := 'Y';
1729 SELECT task_id
1730 INTO l_task_id
1731 FROM mtl_item_locations
1732 WHERE NVL(project_id,-999) = NVL(l_project_id, -111)
1733 AND inventory_location_id =p_kanban_card_rec_tbl(1).locator_id
1734 AND organization_id = p_kanban_card_rec_tbl(1).organization_id;
1735 END IF;
1736 END IF;
1737
1738 mydebug('GB:Need by date ' || TO_CHAR((trunc(p_need_by_date) + 1 - (1/(24*60*60))),'DD-MON-YYYY HH24:MI:SS'));
1739
1740 insert into po_requisitions_interface_all
1741 (
1742 LAST_UPDATE_DATE,
1743 LAST_UPDATED_BY,
1744 CREATION_DATE,
1745 CREATED_BY,
1746 PREPARER_ID,
1747 INTERFACE_SOURCE_CODE,
1748 REQUISITION_TYPE,
1749 AUTHORIZATION_STATUS,
1750 SOURCE_TYPE_CODE,
1751 SOURCE_ORGANIZATION_ID,
1752 SOURCE_SUBINVENTORY,
1753 DESTINATION_ORGANIZATION_ID,
1754 DESTINATION_SUBINVENTORY,
1755 DELIVER_TO_REQUESTOR_ID,
1756 DESTINATION_TYPE_CODE,
1757 DELIVER_TO_LOCATION_ID,
1758 ITEM_ID,
1759 ITEM_REVISION,
1760 ITEM_DESCRIPTION,
1761 UOM_CODE,
1762 QUANTITY,
1763 NEED_BY_DATE,
1764 GL_DATE,
1765 CHARGE_ACCOUNT_ID,
1766 ACCRUAL_ACCOUNT_ID,
1767 VARIANCE_ACCOUNT_ID,
1768 BUDGET_ACCOUNT_ID,
1769 AUTOSOURCE_FLAG,
1770 ORG_ID,
1771 SUGGESTED_VENDOR_ID,
1772 SUGGESTED_VENDOR_SITE_ID,
1773 -- SUGGESTED_BUYER_ID, /* Bug 1456782 */
1774 Kanban_card_Id,
1775 Batch_Id,
1776 PROJECT_ID,
1777 TASK_ID,
1778 PROJECT_ACCOUNTING_CONTEXT
1779 )
1780 Values
1781 (
1782 sysdate,
1783 FND_GLOBAL.USER_ID,
1784 sysdate,
1785 FND_GLOBAL.USER_ID,
1786 p_buyer_Id,
1787 p_interface_Source_Code,
1788 p_requisition_type,
1789 p_approval,
1790 p_source_type_code,
1791 p_kanban_card_Rec_Tbl(1).Source_organization_Id,
1792 p_kanban_card_Rec_Tbl(1).Source_Subinventory,
1793 p_kanban_card_Rec_Tbl(1).organization_Id,
1794 p_kanban_card_Rec_Tbl(1).Subinventory_Name,
1795 p_buyer_Id,
1796 p_destination_type_code,
1797 p_deliver_location_id,
1798 p_kanban_card_Rec_Tbl(1).Inventory_Item_Id,
1799 p_revision,
1800 p_Item_description,
1801 p_Primary_uom_Code,
1802 p_kanban_card_rec_tbl(l_order_count).kanban_size,
1803 (trunc(p_need_by_date) + 1 - (1/(24*60*60))),
1804 SYSDATE,
1805 p_Charge_Account_Id,
1806 p_Accrual_Account_Id,
1807 p_Invoice_Var_Account_Id,
1808 p_Budget_Account_Id,
1809 p_autosource_flag,
1810 p_po_org_id,
1811 p_kanban_card_Rec_Tbl(1).Supplier_ID,
1812 p_kanban_card_Rec_Tbl(1).Supplier_Site_ID,
1813 -- p_Buyer_ID, /* Bug 1456782 */
1814 p_kanban_card_rec_tbl(l_order_count).kanban_card_id,
1815 p_kanban_card_rec_tbl(1).current_replnsh_cycle_id,
1816 l_project_id,
1817 l_task_id,
1818 l_project_accounting_context
1819 );
1820 end loop;
1821
1822
1823 /*
1824 Insert into po_requisition_interface_all with
1825
1826 Org_Id Operating Unit
1827 Preparer ID Buyer ID
1828 Item_Id Inventory_item_id
1829 Item_Description item_description
1830 Accrual_account_id Org level ap_accrual_account
1831 Authorization_status 'APPROVED'
1832 Autosource_Flag 'Y'
1833 Budget_Account_id Encumbrance_account Item Sub level/Sub level/Item level/Org level
1834 Charge_Account_Id For inventory_asset_flag='Y' use sub level/org level
1835 material_account else use sub level/item level/org level expense
1836 account
1837 Variance_Account_Id Org level - invoice_price_variance_account
1838 Created_By Userid
1839 Created_date Sysdate
1840 Last_Updated_By Userid
1841 Last_Update_Date Sysdate
1842 Default_to_location_Id Default location for the org in HR_LOCATIONS
1843 that has a customer in po_assosiation_locations
1844 Deliver_to_requestor_ID Buyer id of item
1845 Destination_Organization_id Org_id
1846 Destination_Subinventory Subinventory
1847 Destination_type_code 'INVENTORY'
1848 Quantity Order Quantity
1849 Requisition_Type 'INTERNAL'/'PURCHASE'
1850 Source Organization Id Source Org
1851 Source Subinventory Source Sub
1852 Source Type Code 'INVENTORY'/'VENDOR'
1853 GL_date sysdate
1854 Interface_source_code 'INV'
1855 UOM_CODE Primary UOM
1856 Requisition_type 'INTERNAL'/'PURCHASE'
1857 Suggested_vendor_id Supplier_ID
1858 Suggested_vendor_site Supplier_Site_Id
1859 Suggested_Buyer_Id Buyer_Id
1860 */
1861
1862 EXCEPTION
1863
1864 WHEN FND_API.G_EXC_ERROR THEN
1865
1866 Raise FND_API.G_EXC_ERROR;
1867
1868 WHEN OTHERS THEN
1869 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1870 THEN
1871 FND_MSG_PUB.Add_Exc_Msg
1872 ( G_PKG_NAME
1873 , 'Create_Requisition'
1874 );
1875 END IF;
1876
1877 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
1878
1879 end Create_Requisition;
1880
1881 --
1882 -- Create_Move_Order : This procedure would create a transfer order for
1883 -- for kanban card with source type Intra Org.
1884 --
1885
1886 Procedure Create_Transfer_Order(
1887 p_kanban_card_rec_tbl IN OUT NOCOPY Kanban_Card_Tbl_Type,
1888 p_need_by_date IN DATE,
1889 p_primary_uom_code IN VARCHAR2 ) IS
1890
1891 l_x_trohdr_rec INV_Move_Order_PUB.Trohdr_Rec_Type;
1892 l_x_trolin_tbl INV_Move_Order_PUB.Trolin_Tbl_Type;
1893 l_trohdr_rec INV_Move_Order_PUB.Trohdr_Rec_Type;
1894 l_trolin_tbl INV_Move_Order_PUB.Trolin_Tbl_Type;
1895 l_return_status VARCHAR2(1):= FND_API.G_RET_STS_SUCCESS;
1896 l_msg_count NUMBER;
1897 l_msg_data VARCHAR2(240);
1898 msg VARCHAR2(2000);
1899 l_header_id Number := FND_API.G_MISS_NUM;
1900 l_line_num Number := 0;
1901 l_item_locator_control_code NUMBER;
1902 l_from_sub_locator_type NUMBER;
1903 l_to_sub_locator_type NUMBER;
1904 l_org_locator_control_code NUMBER;
1905 l_auto_allocate_flag NUMBER; --Added for 3905884
1906 l_mo_request_number VARCHAR2(30); --Added for 3905884
1907
1908 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1909 begin
1910
1911 l_trohdr_rec.created_by := FND_GLOBAL.USER_ID;
1912 l_trohdr_rec.creation_date := sysdate;
1913 l_trohdr_rec.date_required := p_need_by_date;
1914 l_trohdr_rec.from_subinventory_code := p_kanban_card_rec_tbl(1).source_subinventory;
1915 -- l_trohdr_rec.header_id := l_header_id;
1916 l_trohdr_rec.header_status := INV_Globals.G_TO_STATUS_PREAPPROVED;
1917 l_trohdr_rec.last_updated_by := FND_GLOBAL.USER_ID;
1918 l_trohdr_rec.last_update_date := sysdate;
1919 l_trohdr_rec.last_update_login := FND_GLOBAL.LOGIN_ID;
1920 l_trohdr_rec.organization_id := p_kanban_card_rec_tbl(1).organization_id;
1921 -- l_trohdr_rec.request_number := to_char(l_header_id);
1922 l_trohdr_rec.status_date := sysdate;
1923 l_trohdr_rec.to_subinventory_code := p_kanban_card_rec_tbl(1).subinventory_name;
1924 l_trohdr_rec.transaction_type_id := INV_GLOBALS.G_TYPE_TRANSFER_ORDER_SUBXFR;
1925 l_trohdr_rec.move_order_type := INV_GLOBALS.G_MOVE_ORDER_REPLENISHMENT;
1926 l_trohdr_rec.db_flag := FND_API.G_TRUE;
1927 l_trohdr_rec.operation := INV_GLOBALS.G_OPR_CREATE;
1928
1929 select location_control_code
1930 into l_item_locator_control_code
1931 from mtl_system_items
1932 where organization_id = p_kanban_card_rec_tbl(1).organization_id
1933 and inventory_item_id = p_kanban_card_rec_tbl(1).inventory_item_id;
1934
1935 select locator_type
1936 into l_from_sub_locator_type
1937 from mtl_secondary_inventories
1938 where organization_id = p_kanban_card_rec_tbl(1).organization_id
1939 and secondary_inventory_name = p_kanban_card_rec_tbl(1).source_subinventory;
1940
1941 select locator_type
1942 into l_to_sub_locator_type
1943 from mtl_secondary_inventories
1944 where organization_id = p_kanban_card_rec_tbl(1).organization_id
1945 and secondary_inventory_name = p_kanban_card_rec_tbl(1).subinventory_name;
1946
1947 select stock_locator_control_code
1948 into l_org_locator_control_code
1949 from mtl_parameters
1950 where organization_id = p_kanban_card_rec_tbl(1).organization_id;
1951
1952 if l_org_locator_control_code = 1 then
1953 p_kanban_card_rec_tbl(1).source_locator_id := null;
1954 p_kanban_card_rec_tbl(1).locator_id := null;
1955 elsif l_org_locator_control_code = 4 then
1956 if l_from_sub_locator_type = 1 then
1957 p_kanban_card_rec_tbl(1).source_locator_id := null;
1958 elsif l_from_sub_locator_type = 5 then
1959 if l_item_locator_control_code = 1 then
1960 p_kanban_card_rec_tbl(1).source_locator_id := null;
1961 end if;
1962 end if;
1963 if l_to_sub_locator_type = 1 then
1964 p_kanban_card_rec_tbl(1).locator_id := null;
1965 elsif l_to_sub_locator_type = 5 then
1966 if l_item_locator_control_code = 1 then
1967 p_kanban_card_rec_tbl(1).locator_id := null;
1968 end if;
1969 end if;
1970 end if;
1971
1972 -- Bug 1673809
1973 /*
1974 if( l_item_locator_control_code = 1 OR l_from_sub_locator_type = 1 ) then
1975 p_kanban_card_rec_tbl(1).source_locator_id := null;
1976 end if;
1977
1978 if( l_item_locator_control_code = 1 OR l_to_sub_locator_type = 1) then
1979 p_kanban_card_rec_tbl(1).locator_id := null;
1980 end if;
1981 */
1982
1983
1984 For l_order_count in 1..p_Kanban_Card_Rec_Tbl.Count Loop
1985 l_line_num := l_line_num + 1;
1986 l_trolin_tbl(l_order_count).created_by := FND_GLOBAL.USER_ID;
1987 l_trolin_tbl(l_order_count).creation_date := sysdate;
1988 l_trolin_tbl(l_order_count).date_required := p_need_by_date;
1989 l_trolin_tbl(l_order_count).from_locator_id := p_kanban_card_rec_tbl(1).source_locator_id;
1990 l_trolin_tbl(l_order_count).from_subinventory_code := p_kanban_card_rec_tbl(1).source_subinventory;
1991 l_trolin_tbl(l_order_count).inventory_item_id := p_kanban_card_rec_tbl(1).inventory_item_id;
1992 l_trolin_tbl(l_order_count).last_updated_by := FND_GLOBAL.USER_ID;
1993 l_trolin_tbl(l_order_count).last_update_date := sysdate;
1994 l_trolin_tbl(l_order_count).last_update_login := FND_GLOBAL.LOGIN_ID;
1995 l_trolin_tbl(l_order_count).line_id := FND_API.G_MISS_NUM;
1996 l_trolin_tbl(l_order_count).line_number := l_line_num;
1997 l_trolin_tbl(l_order_count).line_status := INV_Globals.G_TO_STATUS_PREAPPROVED;
1998 l_trolin_tbl(l_order_count).organization_id := p_kanban_card_rec_tbl(1).organization_id;
1999 l_trolin_tbl(l_order_count).quantity := p_kanban_card_rec_tbl(l_order_count).kanban_size;
2000 l_trolin_tbl(l_order_count).reference_id := p_kanban_card_rec_tbl(l_order_count).kanban_card_id;
2001 l_trolin_tbl(l_order_count).reference_type_code := INV_Transfer_Order_PVT.G_Ref_Type_Kanban;
2002 l_trolin_tbl(l_order_count).status_date := sysdate;
2003 l_trolin_tbl(l_order_count).to_locator_id := p_kanban_card_rec_tbl(1).locator_id;
2004 -- By kkoothan for Bug Fix:2340651.
2005 BEGIN
2006 SELECT project_id,task_id
2007 INTO l_trolin_tbl(l_order_count).project_id,
2008 l_trolin_tbl(l_order_count).task_id
2009 FROM mtl_item_locations
2010 WHERE inventory_location_id = p_kanban_card_rec_tbl(1).source_locator_id and organization_id = p_kanban_card_rec_tbl(1).organization_id;
2011 EXCEPTION
2012 WHEN no_data_found THEN
2013 NULL;
2014 END;
2015 -- End of Bug Fix:2340651.
2016
2017 l_trolin_tbl(l_order_count).to_subinventory_code:= p_kanban_card_rec_tbl(1).subinventory_name;
2018 l_trolin_tbl(l_order_count).uom_code := p_primary_uom_code;
2019 l_trolin_tbl(l_order_count).transaction_type_id := INV_GLOBALS.G_TYPE_TRANSFER_ORDER_SUBXFR;
2020 l_trolin_tbl(l_order_count).db_flag := FND_API.G_TRUE;
2021 l_trolin_tbl(l_order_count).operation := INV_GLOBALS.G_OPR_CREATE;
2022 END LOOP;
2023
2024 INV_Transfer_Order_PVT.Process_Transfer_Order
2025 ( p_api_version_number => 1.0 ,
2026 p_init_msg_list => FND_API.G_TRUE,
2027 x_return_status => l_return_status,
2028 x_msg_count => l_msg_count,
2029 x_msg_data => l_msg_data,
2030 p_trohdr_rec => l_trohdr_rec,
2031 p_trolin_tbl => l_trolin_tbl,
2032 x_trohdr_rec => l_x_trohdr_rec,
2033 x_trolin_tbl => l_x_trolin_tbl
2034 );
2035
2036 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2037 FND_MSG_PUB.Add_Exc_Msg
2038 ( G_PKG_NAME
2039 , 'Create_transfer_order'
2040 );
2041 RAISE FND_API.G_EXC_ERROR;
2042 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
2043 FND_MSG_PUB.Add_Exc_Msg
2044 ( G_PKG_NAME
2045 , 'Create_transfer_order'
2046 );
2047 RAISE FND_API.G_EXC_ERROR;
2048 END IF;
2049
2050 For I in 1 .. l_x_trolin_tbl.count
2051 Loop
2052 p_kanban_card_rec_tbl(I).document_header_id := l_x_trolin_tbl(I).header_id;
2053 p_kanban_card_rec_tbl(I).document_detail_id := l_x_trolin_tbl(I).Line_id;
2054 p_kanban_card_rec_tbl(I).document_type := 4;
2055 End Loop;
2056
2057 /*Fix for 3905884
2058 IF Auto_Allocate_Flag= 1 (Yes) , allocate move order */
2059 BEGIN
2060 SELECT MKP.auto_allocate_flag INTO l_auto_Allocate_flag
2061 FROM Mtl_Kanban_Pull_Sequences MKP
2062 WHERE MKP.pull_sequence_id=p_kanban_card_rec_tbl(1).pull_sequence_id;
2063 EXCEPTION
2064 WHEN OTHERS THEN
2065 l_auto_Allocate_flag := 0;
2066 END;
2067
2068 IF l_auto_allocate_flag = 1 THEN
2069 Auto_Allocate_Kanban(p_kanban_card_rec_tbl(1).document_header_id,l_return_status, l_msg_count,l_msg_data);
2070
2071 SELECT MTRH.request_number INTO l_mo_request_number
2072 FROM Mtl_Txn_Request_Headers MTRH
2073 WHERE MTRH.Header_id = p_kanban_card_rec_tbl(1).document_header_id;
2074
2075 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
2076 FND_MESSAGE.SET_NAME('INV','INV_KANBAN_MO_ALLOC_SUCCESS');
2077 FND_MESSAGE.SET_TOKEN('MOVE_ORDER',l_mo_request_number);
2078 FND_MSG_PUB.Add;
2079 ELSE
2080 FND_MESSAGE.SET_NAME('INV','INV_MO_ALLOC_FAIL');
2081 FND_MESSAGE.SET_TOKEN('MOVE_ORDER',l_mo_request_number);
2082 FND_MSG_PUB.Add;
2083 END IF;
2084
2085 END IF;
2086 /* End of fix for 3905884*/
2087
2088 EXCEPTION
2089 WHEN FND_API.G_EXC_ERROR THEN
2090 Raise FND_API.G_EXC_ERROR;
2091
2092 WHEN OTHERS THEN
2093 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2094 FND_MSG_PUB.Add_Exc_Msg
2095 ( G_PKG_NAME
2096 , 'Create_Transfer_Order'
2097 );
2098 END IF;
2099 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
2100 end Create_Transfer_Order;
2101
2102 --
2103 -- Launch_MLP() : This program will launch the WIP Mass load program to
2104 -- to upload the data from WIP_JOB_SCHEDULE_INTERFACE
2105 -- table.
2106 --
2107
2108 Function Launch_MLP(p_group_id IN Number) return BOOLEAN IS
2109
2110 v_req_id NUMBER;
2111
2112 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2113 BEGIN
2114
2115 v_req_id := FND_REQUEST.SUBMIT_REQUEST( 'WIP', 'WICMLP',
2116 NULL, NULL, FALSE,
2117 TO_CHAR(p_group_id), /* grp id*/
2118 '3', /* validation lvl */
2119 '2' ); /* print report */
2120 commit;
2121
2122 if v_req_id > 0 then
2123 return TRUE;
2124 else
2125 Raise FND_API.G_EXC_UNEXPECTED_ERROR ;
2126 end if;
2127
2128 exception
2129 WHEN OTHERS THEN
2130 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2131 THEN
2132 FND_MSG_PUB.Add_Exc_Msg
2133 ( G_PKG_NAME
2134 , 'Launch_MLP'
2135 );
2136 END IF;
2137 Return FALSE;
2138
2139 end Launch_MLP;
2140
2141 --
2142 -- Create_Wip_Discrete() :This procedure would create a WIP Discrete Job for a
2143 -- kanban card.
2144 --
2145
2146 Procedure Create_Wip_Discrete(
2147 p_kanban_card_rec_tbl IN OUT NOCOPY Kanban_Card_Tbl_Type,
2148 p_fixed_lead_time IN NUMBER,
2149 p_var_lead_time IN NUMBER) IS
2150 l_group_id Number;
2151 v_launch Boolean := TRUE;
2152 l_project_id NUMBER :=null;
2153 l_task_id NUMBER := null;
2154 l_project_reference_enabled NUMBER;
2155 l_first_unit_start_date DATE;
2156 l_last_unit_completion_date DATE;
2157 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2158 begin
2159
2160 For l_order_count in 1..p_Kanban_Card_Rec_Tbl.Count
2161 LOOP
2162
2163 SELECT NVL(project_reference_enabled,2)
2164 INTO l_project_reference_enabled
2165 FROM mtl_parameters
2166 WHERE organization_id = p_kanban_card_rec_tbl(1).organization_id;
2167
2168 IF (l_project_reference_enabled = 1)THEN
2169 IF (p_kanban_card_rec_tbl(1).locator_id IS NOT NULL)THEN
2170 SELECT project_id
2171 INTO l_project_id
2172 FROM mtl_item_locations
2173 WHERE inventory_location_id = p_kanban_card_rec_tbl(1).locator_id
2174 AND organization_id = p_kanban_card_rec_tbl(1).organization_id;
2175 END IF;
2176 IF (l_project_id IS NOT NULL)THEN
2177 SELECT task_id
2178 INTO l_task_id
2179 FROM mtl_item_locations
2180 WHERE NVL(project_id,-999) = NVL(l_project_id, -111)
2181 AND inventory_location_id =p_kanban_card_rec_tbl(1).locator_id
2182 AND organization_id = p_kanban_card_rec_tbl(1).organization_id;
2183 END IF;
2184 END IF;
2185 --3100874 Outbound Flow Sequencing
2186 --if the need_by_date is passed completion date should be set to the
2187 --value passed, otherwise just set the start date to sysdate
2188 IF p_kanban_card_rec_tbl(l_order_count).need_by_date IS NOT NULL THEN
2189 l_first_unit_start_date := NULL;
2190 l_last_unit_completion_date :=
2191 p_kanban_card_rec_tbl(l_order_count).need_by_date;
2192 ELSE
2193 l_first_unit_start_date := Sysdate;
2194 l_last_unit_completion_date := NULL;
2195 END IF;
2196
2197
2198 Insert into WIP_JOB_SCHEDULE_INTERFACE
2199 (LAST_UPDATE_DATE,
2200 LAST_UPDATED_BY,
2201 CREATION_DATE,
2202 CREATED_BY,
2203 GROUP_ID,
2204 PROCESS_PHASE,
2205 PROCESS_STATUS,
2206 SOURCE_CODE,
2207 ORGANIZATION_ID,
2208 LOAD_TYPE,
2209 FIRST_UNIT_START_DATE,
2210 LAST_UNIT_COMPLETION_DATE,
2211 PRIMARY_ITEM_ID,
2212 START_QUANTITY,
2213 STATUS_TYPE,
2214 LINE_ID,
2215 kanban_card_id,
2216 project_id,
2217 task_id
2218 )
2219 values
2220 (
2221 sysdate ,
2222 FND_GLOBAL.USER_ID,
2223 sysdate ,
2224 FND_GLOBAL.USER_ID,
2225 p_kanban_card_rec_tbl(1).current_replnsh_cycle_id,
2226 2,
2227 1,
2228 'INV',
2229 p_kanban_card_rec_tbl(1).organization_id,
2230 1, /* Discrete job */
2231 l_first_unit_start_date,
2232 l_last_unit_completion_date,
2233 p_kanban_card_rec_tbl(1).inventory_item_id,
2234 p_kanban_card_rec_tbl(l_order_count).Kanban_size,
2235 3,
2236 p_kanban_card_rec_tbl(1).wip_line_id,
2237 p_kanban_card_rec_tbl(l_order_count).kanban_card_id,
2238 l_project_id,
2239 l_task_id
2240 );
2241 l_project_id := NULL;
2242 l_task_id := NULL;
2243 end loop;
2244 l_group_id := p_kanban_card_rec_tbl(1).current_replnsh_cycle_id;
2245 v_launch := Launch_MLP( l_group_id );
2246 if ( Not v_launch ) then
2247 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
2248 end if;
2249
2250 EXCEPTION
2251
2252 WHEN FND_API.G_EXC_ERROR THEN
2253
2254 Raise FND_API.G_EXC_ERROR;
2255
2256 WHEN OTHERS THEN
2257 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2258 THEN
2259 FND_MSG_PUB.Add_Exc_Msg
2260 ( G_PKG_NAME
2261 , 'Create_Wip_Discrete'
2262 );
2263 END IF;
2264
2265 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
2266 end Create_Wip_Discrete;
2267
2268 --
2269 -- Create_Rep_Schedule() : This procedure would Create a WIP Repetetive
2270 -- schedule.
2271 --
2272 Procedure Create_Rep_Schedule(
2273 p_kanban_card_rec_tbl IN OUT NOCOPY Kanban_Card_Tbl_Type,
2274 p_fixed_lead_time IN NUMBER,
2275 p_var_lead_time IN NUMBER ) IS
2276
2277 rep_sched_exist varchar2(1) := 'N';
2278 total_qty Number := 0;
2279 processing_days Number := 0;
2280 line_rate Number := 0;
2281 l_group_id Number;
2282 v_launch Boolean := TRUE;
2283 l_project_id number := NULL;
2284 l_task_id NUMBER :=NULL;
2285 l_project_reference_enabled NUMBER;
2286 l_first_unit_start_date DATE;
2287 l_last_unit_completion_date DATE;
2288 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2289 BEGIN
2290
2291 begin
2292 select MAXIMUM_RATE into line_rate
2293 from WIP_LINES
2294 where
2295 LINE_ID = p_kanban_card_rec_tbl(1).wip_line_id AND
2296 organization_id = p_kanban_card_rec_tbl(1).organization_id;
2297 exception
2298 When others then
2299 FND_MSG_PUB.Add_Exc_Msg
2300 ( G_PKG_NAME
2301 , 'Create Rep Sched|Wip Line Not Defined'
2302 );
2303 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2304 end;
2305
2306 /*
2307 begin
2308 select 'Y' into rep_sched_exist
2309 from WIP_JOB_SCHEDULE_INTERFACE
2310 where
2311 primary_item_id = p_kanban_card_rec_tbl(1).inventory_item_id AND
2312 organization_id = p_kanban_card_rec_tbl(1).organization_id AND
2313 line_id = p_kanban_card_rec_tbl(1).wip_line_id AND
2314 load_type = 2 AND
2315 process_phase <> 4 AND
2316 to_date(creation_date,'DD-MON-RR') = to_date(SYSDATE,'DD-MON-RR')
2317 For Update of start_quantity NOWAIT;
2318 Raise TOO_MANY_ROWS;
2319 exception
2320 When NO_DATA_FOUND then
2321 rep_sched_exist := 'N';
2322 When TOO_MANY_ROWS then
2323 rep_sched_exist := 'Y';
2324 When others then
2325 FND_MSG_PUB.Add_Exc_Msg
2326 ( G_PKG_NAME
2327 , 'Create Rep Sched|checking existing Schedules'
2328 );
2329 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2330 end;
2331
2332 If ( rep_sched_exist = 'Y' ) then
2333 For l_order_count in 1..p_kanban_card_rec_tbl.Count
2334 Loop
2335 total_qty := total_qty + p_kanban_card_rec_tbl(l_order_count).Kanban_size;
2336 End Loop;
2337 Else
2338 */
2339
2340 For l_order_count in 1..p_kanban_card_rec_tbl.Count
2341 Loop
2342 if ( l_order_count = 1 ) THEN
2343
2344 SELECT NVL(project_reference_enabled,2)
2345 INTO l_project_reference_enabled
2346 FROM mtl_parameters
2347 WHERE organization_id = p_kanban_card_rec_tbl(1).organization_id;
2348
2349 IF (l_project_reference_enabled = 1)THEN
2350 IF (p_kanban_card_rec_tbl(1).locator_id IS NOT NULL)THEN
2351 SELECT project_id
2352 INTO l_project_id
2353 FROM mtl_item_locations
2354 WHERE inventory_location_id = p_kanban_card_rec_tbl(1).locator_id
2355 AND organization_id = p_kanban_card_rec_tbl(1).organization_id;
2356 END IF;
2357 IF (l_project_id IS NOT NULL)THEN
2358 SELECT task_id
2359 INTO l_task_id
2360 FROM mtl_item_locations
2361 WHERE NVL(project_id,-999) = NVL(l_project_id, -111)
2362 AND inventory_location_id =p_kanban_card_rec_tbl(1).locator_id
2363 AND organization_id = p_kanban_card_rec_tbl(1).organization_id;
2364 END IF;
2365 END IF;
2366 --3100874 Outbound Flow Sequencing
2367 --if the need_by_date is passed completion date should be set to the
2368 --value passed, otherwise just set the start date to sysdate
2369 IF p_kanban_card_rec_tbl(l_order_count).need_by_date IS NOT NULL THEN
2370 l_first_unit_start_date := NULL;
2371 l_last_unit_completion_date :=
2372 p_kanban_card_rec_tbl(l_order_count).need_by_date;
2373 ELSE
2374 l_first_unit_start_date := Sysdate;
2375 l_last_unit_completion_date := NULL;
2376 END IF;
2377 Insert into WIP_JOB_SCHEDULE_INTERFACE
2378 ( LAST_UPDATE_DATE,
2379 LAST_UPDATED_BY,
2380 CREATION_DATE,
2381 CREATED_BY,
2382 GROUP_ID,
2383 PROCESS_PHASE,
2384 PROCESS_STATUS,
2385 SOURCE_CODE,
2386 ORGANIZATION_ID,
2387 LOAD_TYPE,
2388 FIRST_UNIT_START_DATE,
2389 -- FIRST_UNIT_COMPLETION_DATE,
2390 LAST_UNIT_COMPLETION_DATE,
2391 DAILY_PRODUCTION_RATE,
2392 PROCESSING_WORK_DAYS,
2393 PRIMARY_ITEM_ID,
2394 START_QUANTITY,
2395 STATUS_TYPE,
2396 line_id,
2397 project_id,
2398 task_id
2399 )
2400 values
2401 (
2402 SYSDATE ,
2403 FND_GLOBAL.USER_ID,
2404 SYSDATE ,
2405 FND_GLOBAL.USER_ID,
2406 p_kanban_card_rec_tbl(1).current_replnsh_cycle_id,
2407 2,
2408 1,
2409 'INV',
2410 p_kanban_card_rec_tbl(1).organization_id,
2411 2, /* Rep schedule */
2412 l_first_unit_start_date,
2413 l_last_unit_completion_date,
2414 -- SYSDATE,
2415 -- SYSDATE+(p_fixed_lead_time +
2416 -- (p_var_lead_time*p_kanban_card_rec_tbl(l_order_count).kanban_size)),
2417 line_rate,
2418 p_kanban_card_rec_tbl(l_order_count).Kanban_size / line_rate ,
2419 p_kanban_card_rec_tbl(1).inventory_item_id,
2420 p_kanban_card_rec_tbl(l_order_count).Kanban_size,
2421 1,
2422 p_kanban_card_rec_tbl(1).wip_line_id,
2423 l_project_id,
2424 l_task_id
2425 );
2426 else
2427 total_qty := total_qty + p_kanban_card_rec_tbl(l_order_count).Kanban_size;
2428 rep_sched_exist := 'Y';
2429 end if;
2430 End loop;
2431 -- End if;
2432
2433 p_kanban_card_rec_tbl(1).document_type := 6;
2434 if ( rep_sched_exist = 'Y' ) then
2435 Update WIP_JOB_SCHEDULE_INTERFACE
2436 set START_QUANTITY = START_QUANTITY + total_qty ,
2437 -- LAST_UNIT_COMPLETION_DATE = SYSDATE + (p_fixed_lead_time +
2438 -- p_var_lead_time*(START_QUANTITY + total_qty)),
2439 PROCESSING_WORK_DAYS = (START_QUANTITY + total_qty)/ line_rate,
2440 GROUP_ID = p_kanban_card_rec_tbl(1).current_replnsh_cycle_id
2441 where
2442 primary_item_id = p_kanban_card_rec_tbl(1).inventory_item_id AND
2443 organization_id = p_kanban_card_rec_tbl(1).organization_id AND
2444 line_id = p_kanban_card_rec_tbl(1).wip_line_id AND
2445 load_type = 2 AND
2446 process_phase = 2 AND
2447 group_id = p_kanban_card_rec_tbl(1).current_replnsh_cycle_id;
2448 end if;
2449
2450 l_group_id := p_kanban_card_rec_tbl(1).current_replnsh_cycle_id;
2451 v_launch := Launch_MLP( l_group_id );
2452 if ( Not v_launch ) then
2453 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
2454 end if;
2455
2456 EXCEPTION
2457
2458 WHEN FND_API.G_EXC_ERROR THEN
2459 Raise FND_API.G_EXC_ERROR;
2460
2461 WHEN OTHERS THEN
2462 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2463 THEN
2464 FND_MSG_PUB.Add_Exc_Msg
2465 ( G_PKG_NAME
2466 , 'Create_Rep_Schedule'
2467 );
2468 END IF;
2469 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
2470
2471 END Create_Rep_Schedule;
2472
2473 --
2474 -- Create_Flow_schedule : This procedure would create a Wip
2475 -- Flow schedule.
2476 --
2477
2478 Procedure Create_Flow_Schedule(
2479 p_kanban_card_rec_tbl IN Out NOCOPY Kanban_Card_Tbl_Type,
2480 p_fixed_lead_time IN NUMBER,
2481 p_var_lead_time IN NUMBER ) IS
2482
2483 l_flow_schedule_rec MRP_Flow_Schedule_PUB.Flow_Schedule_Rec_Type;
2484 l_x_flow_schedule_rec MRP_Flow_Schedule_PUB.Flow_Schedule_Rec_Type;
2485 l_x_flow_schedule_val_rec MRP_Flow_Schedule_PUB.Flow_Schedule_Val_Rec_Type;
2486 l_return_status VARCHAR2(1):= FND_API.G_RET_STS_SUCCESS;
2487 l_msg_count NUMBER;
2488 l_msg_data VARCHAR2(240);
2489 msg VARCHAR2(2000);
2490
2491 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2492 BEGIN
2493
2494 For l_order_count in 1..p_kanban_card_rec_tbl.Count
2495 Loop
2496
2497 l_flow_schedule_rec.created_by := FND_GLOBAL.USER_ID;
2498 l_flow_schedule_rec.creation_date := sysdate;
2499 l_flow_schedule_rec.last_updated_by := FND_GLOBAL.USER_ID;
2500 l_flow_schedule_rec.last_update_date := sysdate;
2501 l_flow_schedule_rec.line_id :=
2502 p_kanban_card_rec_tbl(1).wip_line_id;
2503 l_flow_schedule_rec.organization_id :=
2504 p_kanban_card_rec_tbl(1).organization_id;
2505 l_flow_schedule_rec.planned_quantity :=
2506 p_kanban_card_rec_tbl(l_order_count).kanban_size;
2507 l_flow_schedule_rec.primary_item_id :=
2508 p_kanban_card_rec_tbl(1).inventory_item_id;
2509 l_flow_schedule_rec.completion_subinventory :=
2510 p_kanban_card_rec_tbl(1).subinventory_name;
2511 l_flow_schedule_rec.completion_locator_id :=
2512 p_kanban_card_rec_tbl(1).locator_id;
2513
2514 IF p_kanban_card_rec_tbl(l_order_count).need_by_date IS NOT NULL THEN
2515 l_flow_schedule_rec.scheduled_start_date := NULL;
2516 l_flow_schedule_rec.scheduled_completion_date :=
2517 p_kanban_card_rec_tbl(l_order_count).need_by_date;
2518
2519 ELSE
2520
2521 l_flow_schedule_rec.scheduled_start_date := SYSDATE;
2522 -- l_flow_schedule_rec.scheduled_completion_date :=
2523 -- SYSDATE+(p_fixed_lead_time+
2524 -- (p_var_lead_time*p_kanban_card_rec_tbl(l_order_count).kanban_size));
2525 l_flow_schedule_rec.scheduled_completion_date :=
2526 MRP_LINE_SCHEDULE_ALGORITHM.calculate_completion_time
2527 (p_kanban_card_rec_tbl(1).organization_id,
2528 p_kanban_card_rec_tbl(1).inventory_item_id,
2529 p_kanban_card_rec_tbl(l_order_count).kanban_size,
2530 p_kanban_card_rec_tbl(1).wip_line_id,
2531 SYSDATE);
2532 END IF;
2533
2534
2535 -- l_flow_schedule_rec.schedule_group_id :=
2536 -- p_kanban_card_rec_tbl(1).current_replnsh_cycle_id;
2537 -- l_flow_schedule_rec.scheduled_by := FND_API.G_MISS_NUM;
2538 l_flow_schedule_rec.kanban_card_id :=
2539 p_kanban_card_rec_tbl(l_order_count).kanban_card_id;
2540 l_flow_schedule_rec.operation := MRP_GLOBALS.G_OPR_CREATE;
2541
2542 /* Requested by Liye Ma to add a new parameter p_explode_bom to this call
2543 to fix Flow Schedule Report bug 2147361
2544 Dependency: The signature change is in MRPPWFSS.pls 115.13 */
2545
2546 MRP_Flow_Schedule_PUB.Process_Flow_Schedule
2547 (
2548 p_api_version_number => 1.0,
2549 p_init_msg_list => FND_API.G_TRUE,
2550 x_return_status => l_return_status,
2551 x_msg_count => l_msg_count,
2552 x_msg_data => l_msg_data,
2553 p_flow_schedule_rec => l_flow_schedule_rec,
2554 x_flow_schedule_rec => l_x_flow_schedule_rec,
2555 x_flow_schedule_val_rec => l_x_flow_schedule_val_rec,
2556 p_explode_bom => 'Y'
2557 );
2558
2559 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2560 RAISE FND_API.G_EXC_ERROR;
2561 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
2562 RAISE FND_API.G_EXC_ERROR;
2563 END IF;
2564
2565 p_kanban_card_rec_tbl(l_order_count).document_header_id :=
2566 l_x_flow_schedule_rec.wip_entity_id;
2567 p_kanban_card_rec_tbl(l_order_count).document_type := 7;
2568
2569 end loop;
2570
2571 EXCEPTION
2572
2573 WHEN FND_API.G_EXC_ERROR THEN
2574
2575 Raise FND_API.G_EXC_ERROR;
2576
2577 WHEN OTHERS THEN
2578 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2579 THEN
2580 FND_MSG_PUB.Add_Exc_Msg
2581 ( G_PKG_NAME
2582 , 'Create_Flow_Schedule'
2583 );
2584 END IF;
2585
2586 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
2587 end Create_Flow_Schedule;
2588
2589
2590
2591 --
2592 -- Create_lot_based_job : This procedure would create a osfm
2593 -- lot based job
2594 --
2595
2596 Procedure Create_lot_based_job(
2597 p_kanban_card_rec_tbl IN Out NOCOPY Kanban_Card_Tbl_Type,
2598 p_fixed_lead_time IN NUMBER,
2599 p_var_lead_time IN NUMBER ) IS
2600
2601 l_return_status VARCHAR2(1):= FND_API.G_RET_STS_SUCCESS;
2602 l_msg_count NUMBER;
2603 l_msg_data VARCHAR2(240);
2604 msg VARCHAR2(2000);
2605 l_header_id NUMBER := NULL;
2606 l_reqid NUMBER := NULL;
2607 l_group_id NUMBER := NULL;
2608 l_mode_flag NUMBER := NULL;
2609 l_job_name VARCHAR2(255);
2610 l_first_unit_start_date DATE;
2611 l_last_unit_completion_date DATE;
2612 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2613 -- Following variable is added as a part of bug fix for bug # 3301126
2614 l_scheduling_method NUMBER := 2;
2615
2616 BEGIN
2617
2618 -- Following IF_ELSE block is a part of bug fix for bug # 3301126
2619 IF(to_number(NVL(FND_PROFILE.VALUE('WSM_CREATE_LBJ_COPY_ROUTING'),0)) = 1 ) THEN
2620 l_scheduling_method := 1;
2621 ELSE
2622 l_scheduling_method := 2;
2623 END IF;
2624
2625 For l_order_count in 1..p_kanban_card_rec_tbl.Count
2626
2627 LOOP
2628
2629 select wsm_lot_sm_ifc_header_s.nextval
2630 into l_header_id
2631 from dual;
2632
2633
2634
2635 select wsm_lot_job_interface_s.NEXTVAL
2636 into l_group_id
2637 from dual;
2638
2639 IF p_kanban_card_rec_tbl(l_order_count).lot_number IS NULL THEN
2640 l_mode_flag := 1;
2641 ELSE
2642 l_mode_flag := 2;
2643 END IF;
2644
2645 select FND_Profile.value('WIP_JOB_PREFIX')||wip_job_number_s.nextval
2646 INTO l_job_name
2647 from dual;
2648 --3100874 Outbound Flow Sequencing
2649 --if the need_by_date is passed completion date should be set to the
2650 --value passed, otherwise just set the start date to sysdate
2651 IF p_kanban_card_rec_tbl(l_order_count).need_by_date IS NOT NULL THEN
2652 l_first_unit_start_date := NULL;
2653 l_last_unit_completion_date :=
2654 p_kanban_card_rec_tbl(l_order_count).need_by_date;
2655 ELSE
2656 l_first_unit_start_date := SYSDATE;
2657 l_last_unit_completion_date := NULL;
2658 END IF;
2659
2660
2661 INSERT INTO WSM_LOT_JOB_INTERFACE
2662 (mode_flag,
2663 last_update_date,
2664 last_updated_by,
2665 creation_date,
2666 created_by,
2667 last_update_login,
2668 group_id,
2669 source_line_id,
2670 organization_id,
2671 load_type,
2672 status_type,
2673 primary_item_id,
2674 job_name,
2675 start_Quantity,
2676 process_Status,
2677 first_unit_start_date,
2678 last_unit_completion_date,
2679 scheduling_method,
2680 completion_subinventory,
2681 completion_locator_id,
2682 class_code,
2683 description,
2684 bom_revision_date,
2685 routing_revision_date,
2686 header_id,
2687 kanban_card_id)
2688 VALUES
2689 (l_mode_flag,
2690 sysdate,
2691 fnd_global.user_id,
2692 sysdate,
2693 fnd_global.user_id,
2694 fnd_global.login_id,
2695 l_group_id,
2696 Decode(l_mode_flag, 1,null,l_header_id),
2697 p_kanban_card_rec_tbl(1).organization_id,
2698 5, --job creation
2699 3, --1:unreleased, 3: released
2700 p_kanban_card_rec_tbl(1).inventory_item_id,
2701 l_job_name,
2702 Nvl(p_kanban_card_rec_tbl(l_order_count).replenish_quantity,p_kanban_card_rec_tbl(l_order_count).kanban_size),
2703 1,
2704 l_first_unit_start_date,
2705 l_last_unit_completion_date,
2706 l_scheduling_method,
2707 p_kanban_card_rec_tbl(1).subinventory_name,
2708 p_kanban_card_rec_tbl(1).locator_id,
2709 '',
2710 null,
2711 '',
2712 '',
2713 l_header_id,
2714 p_kanban_card_rec_tbl(l_order_count).kanban_card_id);
2715
2716
2717 IF p_kanban_card_rec_tbl(l_order_count).lot_number IS NOT NULL THEN
2718 insert into wsm_starting_lots_interface
2719 (header_id,
2720 lot_number,
2721 inventory_item_id,
2722 revision,
2723 organization_id,
2724 quantity,
2725 subinventory_code,
2726 locator_id,
2727 last_update_date,
2728 last_updated_by,
2729 creation_date,
2730 created_by,
2731 last_update_login )
2732 values
2733 ( l_header_id,
2734 p_kanban_card_rec_tbl(l_order_count).lot_number,
2735 p_kanban_card_rec_tbl(l_order_count).lot_item_id,
2736 p_kanban_card_rec_tbl(l_order_count).lot_item_revision,
2737 p_kanban_card_rec_tbl(l_order_count).organization_id,
2738 p_kanban_card_rec_tbl(l_order_count).lot_quantity,
2739 p_kanban_card_rec_tbl(l_order_count).lot_subinventory_code,
2740 p_kanban_card_rec_tbl(l_order_count).lot_location_id ,
2741 sysdate,
2742 fnd_global.user_id,
2743 sysdate,
2744 fnd_global.user_id,
2745 fnd_global.login_id);
2746
2747
2748 END IF;
2749 p_kanban_card_rec_tbl(l_order_count).document_header_id := null;
2750 p_kanban_card_rec_tbl(l_order_count).document_type := 8;
2751
2752
2753 l_reqid := FND_REQUEST.SUBMIT_REQUEST (
2754 application => 'WSM',
2755 program => 'WSMPLBJI',
2756 sub_request => FALSE,
2757 argument1 => l_group_id);
2758 if ( l_reqid <= 0 ) then
2759 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
2760 end if;
2761 end loop;
2762
2763 EXCEPTION
2764
2765 WHEN FND_API.G_EXC_ERROR THEN
2766
2767 Raise FND_API.G_EXC_ERROR;
2768
2769 WHEN OTHERS THEN
2770 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2771 THEN
2772 FND_MSG_PUB.Add_Exc_Msg
2773 ( G_PKG_NAME
2774 , 'Create_lot_based_job'
2775 );
2776 END IF;
2777
2778 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
2779 end Create_lot_based_job;
2780
2781
2782
2783 --
2784 -- Create_Wip_Job() : This procedure will decide about the creation
2785 -- of a WIP replnsh mode.
2786 --
2787 Procedure Create_Wip_Job( p_kanban_card_rec_tbl IN OUT NOCOPY Kanban_Card_Tbl_Type,
2788 p_need_by_date IN DATE ,
2789 x_card_supply_status IN OUT NOCOPY Number ) IS
2790
2791 v_rep_flag varchar2(1);
2792 v_fixed_lead_time number;
2793 v_var_lead_time number;
2794 v_cfm_flag number;
2795 v_priority number;
2796 v_wip_line_id number := NULL;
2797 l_is_lot_control VARCHAR2(1) := NULL;
2798 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2799 BEGIN
2800
2801 begin
2802 select nvl(repetitive_planning_flag,'N'), nvl(fixed_lead_time,0),
2803 nvl(variable_lead_time,0)
2804 into v_rep_flag, v_fixed_lead_time, v_var_lead_time
2805 from MTL_SYSTEM_ITEMS_KFV
2806 where
2807 inventory_item_id = p_kanban_card_rec_tbl(1).inventory_item_id AND
2808 organization_id = p_kanban_card_rec_tbl(1).organization_id;
2809 exception
2810 when others then
2811 FND_MSG_PUB.Add_Exc_Msg
2812 ( G_PKG_NAME
2813 , 'Ist SQL stmt'
2814 );
2815 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2816 end;
2817 IF p_Kanban_Card_Rec_Tbl(1).wip_line_id is NULL then
2818 begin
2819 select nvl(cfm_routing_flag,0),line_id into v_cfm_flag, v_wip_line_id
2820 from BOM_OPERATIONAL_ROUTINGS
2821 where
2822 assembly_item_id = p_kanban_card_rec_tbl(1).inventory_item_id AND
2823 organization_id = p_kanban_card_rec_tbl(1).organization_id AND
2824 alternate_routing_designator is NULL;
2825 exception
2826 when NO_DATA_FOUND then
2827 v_cfm_flag := 2;
2828 when others then
2829 FND_MSG_PUB.Add_Exc_Msg
2830 ( G_PKG_NAME
2831 , 'wip line id IS NULL'
2832 );
2833 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2834 end;
2835
2836
2837 if v_cfm_flag = 1 THEN
2838
2839
2840 p_Kanban_Card_Rec_Tbl(1).wip_line_id := v_wip_line_id;
2841 create_flow_schedule(p_kanban_card_rec_tbl, v_fixed_lead_time,
2842 v_var_lead_time );
2843 x_card_supply_status := INV_Kanban_PVT.G_Supply_Status_InProcess;
2844 elsif (v_cfm_flag = 3) AND (wsmpvers.get_osfm_release_version > '110508')
2845 THEN
2846
2847 BEGIN
2848 SELECT 'Y' INTO l_is_lot_control
2849 FROM dual WHERE exists
2850 (SELECT 1 FROM mtl_system_items
2851 WHERE
2852 organization_id = p_kanban_card_rec_tbl(1).organization_id
2853 AND inventory_item_id = p_kanban_card_rec_tbl(1).inventory_item_id
2854 AND lot_control_code = 2);
2855 EXCEPTION
2856 WHEN OTHERS THEN
2857 l_is_lot_control := 'N';
2858 END;
2859
2860 IF l_is_lot_control = 'Y' then
2861
2862 create_lot_based_job(p_kanban_card_rec_tbl, v_fixed_lead_time,
2863 v_var_lead_time );
2864 x_card_supply_status := INV_Kanban_PVT.G_Supply_Status_InProcess;
2865
2866 END IF;
2867
2868 /* Code modification for 2186198 */
2869 elsif v_rep_flag = 'Y' THEN
2870
2871 BEGIN
2872 select line_id
2873 into v_wip_line_id
2874 from wip_repetitive_items
2875 where load_distribution_priority =
2876 (select min(load_distribution_priority)
2877 from wip_repetitive_items
2878 where organization_id = p_kanban_card_rec_tbl(1).organization_id
2879 and primary_item_id = p_kanban_card_rec_tbl(1).inventory_item_id
2880 group by organization_id,primary_item_id)
2881 and organization_id = p_kanban_card_rec_tbl(1).organization_id
2882 and primary_item_id = p_kanban_card_rec_tbl(1).inventory_item_id
2883 and rownum < 2;
2884 p_Kanban_Card_Rec_Tbl(1).wip_line_id := v_wip_line_id;
2885 exception
2886 when NO_DATA_FOUND then
2887 FND_MESSAGE.SET_NAME('INV','INV_ATTRIBUTE_REQUIRED');
2888 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','INV_WIP_LINE');
2889 FND_MSG_PUB.Add;
2890 Raise FND_API.G_EXC_ERROR;
2891 when others then
2892 FND_MSG_PUB.Add_Exc_Msg
2893 (G_PKG_NAME ,'wip line id IS NULL');
2894 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2895 end ;
2896 create_rep_schedule(p_kanban_card_rec_tbl, v_fixed_lead_time,
2897 v_var_lead_time );
2898 x_card_supply_status := INV_Kanban_PVT.G_Supply_Status_InProcess;
2899
2900 else
2901 Create_Wip_Discrete(p_kanban_card_rec_tbl, v_fixed_lead_time,
2902 v_var_lead_time);
2903 x_card_supply_status := INV_Kanban_PVT.G_Supply_Status_Empty;
2904
2905 end if;
2906
2907 ELSE /* wip line id IS not NULL */
2908
2909 begin
2910 select nvl(cfm_routing_flag,0) into v_cfm_flag
2911 from BOM_OPERATIONAL_ROUTINGS
2912 where
2913 assembly_item_id = p_kanban_card_rec_tbl(1).inventory_item_id AND
2914 organization_id = p_kanban_card_rec_tbl(1).organization_id AND
2915 line_id = p_kanban_card_rec_tbl(1).wip_line_id AND
2916 nvl(priority,0) = ( select min(nvl(priority,0))
2917 from bom_operational_routings
2918 where
2919 assembly_item_id = p_kanban_card_rec_tbl(1).inventory_item_id AND
2920 organization_id = p_kanban_card_rec_tbl(1).organization_id AND
2921 line_id = p_kanban_card_rec_tbl(1).wip_line_id ) AND
2922 rownum < 2 ;
2923 exception
2924 when NO_DATA_FOUND then
2925 v_cfm_flag := 2;
2926 when others then
2927 FND_MSG_PUB.Add_Exc_Msg
2928 ( G_PKG_NAME
2929 , 'wip line id IS not NULL'
2930 );
2931 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2932 end;
2933 IF v_cfm_flag = 1 THEN
2934
2935 create_flow_schedule(p_kanban_card_rec_tbl, v_fixed_lead_time,
2936 v_var_lead_time );
2937 x_card_supply_status := INV_Kanban_PVT.G_Supply_Status_InProcess;
2938
2939 elsif (v_cfm_flag = 3) AND (wsmpvers.get_osfm_release_version > '110508') THEN
2940
2941 BEGIN
2942 SELECT 'Y' INTO l_is_lot_control
2943 FROM dual WHERE exists
2944 (SELECT 1 FROM mtl_system_items
2945 WHERE
2946 organization_id = p_kanban_card_rec_tbl(1).organization_id
2947 AND inventory_item_id = p_kanban_card_rec_tbl(1).inventory_item_id
2948 AND lot_control_code = 2);
2949 EXCEPTION
2950 WHEN OTHERS THEN
2951 l_is_lot_control := 'N';
2952 END;
2953
2954 IF l_is_lot_control = 'Y' then
2955
2956 create_lot_based_job(p_kanban_card_rec_tbl, v_fixed_lead_time,
2957 v_var_lead_time );
2958 x_card_supply_status := INV_Kanban_PVT.G_Supply_Status_InProcess;
2959
2960 END IF;
2961
2962
2963 elsif v_rep_flag = 'Y' THEN
2964
2965 create_rep_schedule(p_kanban_card_rec_tbl, v_fixed_lead_time,
2966 v_var_lead_time );
2967 x_card_supply_status := INV_Kanban_PVT.G_Supply_Status_InProcess;
2968 ELSE
2969
2970 Create_Wip_Discrete(p_kanban_card_rec_tbl, v_fixed_lead_time,
2971 v_var_lead_time);
2972 x_card_supply_status := INV_Kanban_PVT.G_Supply_Status_Empty;
2973 end if;
2974
2975 END IF;
2976
2977 EXCEPTION
2978
2979 WHEN FND_API.G_EXC_ERROR THEN
2980
2981 Raise FND_API.G_EXC_ERROR;
2982
2983 WHEN OTHERS THEN
2984 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2985 THEN
2986 FND_MSG_PUB.Add_Exc_Msg
2987 ( G_PKG_NAME
2988 , 'Create_Wip_Job'
2989 );
2990 END IF;
2991
2992 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
2993 end Create_Wip_Job;
2994
2995 --
2996 --
2997 -- Create Replenishment: This procedure would create kick off the replenishment
2998 -- cycle for the kanban cards.
2999 --
3000 PROCEDURE Create_Replenishment
3001 (p_Kanban_Card_Rec_Tbl In Out NOCOPY Kanban_Card_Tbl_Type,
3002 p_lead_time Number,
3003 x_card_supply_status Out NOCOPY Number)
3004 IS
3005
3006 l_Item_Description Varchar2(240);
3007 l_Source_type_code Varchar2(30);
3008 l_Requisition_type Varchar2(30);
3009 l_Primary_Uom_Code Varchar2(3);
3010 l_deliver_location_Id Number;
3011 l_Buyer_Id Number;
3012 l_Encumb_Account_Id Number;
3013 l_Charge_Account_Id Number;
3014 l_Budget_Account_Id Number;
3015 l_Accrual_Account_Id Number;
3016 l_Invoice_Var_Account_Id Number;
3017 l_Inventory_Asset_Flag Varchar2(1);
3018 l_Interface_source_code Varchar2(30) := 'INV';
3019 l_Destination_type_code Varchar2(30) := 'INVENTORY';
3020 l_Approval Varchar2(30) := 'APPROVED';
3021 l_Autosource_Flag Varchar2(1) := 'P';
3022 l_need_by_date Date;
3023 l_need_by_time Number;
3024 l_PreProcess_lead_Time Number;
3025 l_Process_lead_Time Number;
3026 l_PostProcess_lead_Time Number;
3027 l_Encumb_Flag Varchar2(1);
3028 l_PO_Org_Id Number := null;
3029 l_sql_stmt_no Number;
3030 l_Revision Varchar2(3);
3031 l_Revision_qty_control_code Number;
3032 l_Kanban_Card_Rec_Tbl Kanban_Card_Tbl_Type;
3033 p_card_supply_status Number;
3034 revision_profile Number;
3035
3036 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3037 Begin
3038 mydebug('Inside create_replenishment');
3039
3040 l_Kanban_Card_Rec_Tbl := p_Kanban_Card_Rec_Tbl;
3041
3042 l_sql_stmt_no := 10;
3043 -- Bug # 5568749, Removed Buyer_Id from the select statement
3044 Select msi.Description,Primary_Uom_Code,Inventory_Asset_Flag,
3045 nvl(mss.ENCUMBRANCE_ACCOUNT,
3046 nvl(msi.Encumbrance_Account,Org.Encumbrance_Account)),
3047 decode(msi.inventory_asset_flag, 'Y', mss.material_account,
3048 nvl(mss.expense_account,nvl(msi.expense_account,org.expense_account))),
3049 Org.Ap_accrual_account,
3050 Org.invoice_price_var_account,
3051 nvl(mss.preprocessing_lead_time,nvl(msi.preprocessing_lead_time,0)),
3052 nvl(mss.processing_lead_time,nvl(msi.full_lead_time,0)),
3053 nvl(mss.postprocessing_lead_time,nvl(msi.postprocessing_lead_time,0)),
3054 msi.revision_qty_control_code
3055 Into l_Item_Description,
3056 l_Primary_Uom_Code,
3057 l_Inventory_Asset_Flag,
3058 l_Encumb_Account_Id,
3059 l_Charge_Account_Id,
3060 l_Accrual_Account_Id,
3061 l_Invoice_Var_Account_Id,
3062 l_PreProcess_lead_Time,
3063 l_Process_lead_Time,
3064 l_PostProcess_lead_Time,
3065 l_Revision_qty_control_code
3066 From Mtl_System_Items msi,
3067 mtl_Parameters org,
3068 mtl_secondary_inventories mss
3069 Where Msi.Organization_Id = l_kanban_card_Rec_Tbl(1).Organization_id
3070 And Msi.Inventory_Item_Id = l_kanban_card_Rec_Tbl(1).Inventory_Item_Id
3071 And org.Organization_Id = l_kanban_card_Rec_Tbl(1).Organization_Id
3072 And mss.Organization_id = l_kanban_card_Rec_Tbl(1).Organization_id
3073 And mss.secondary_inventory_name = l_kanban_card_Rec_Tbl(1).Subinventory_Name;
3074
3075 Begin
3076 Select nvl(ENCUMBRANCE_ACCOUNT,l_Encumb_Account_Id),
3077 nvl(preprocessing_lead_time,l_PreProcess_lead_Time)
3078 + nvl(processing_lead_time,l_Process_lead_Time)
3079 + nvl(postprocessing_lead_time,l_PostProcess_lead_Time)
3080 Into l_budget_Account_Id,
3081 l_need_by_time
3082 From mtl_item_sub_inventories
3083 Where Organization_id = l_kanban_card_Rec_Tbl(1).Organization_id
3084 And Inventory_Item_Id = l_kanban_card_Rec_Tbl(1).Inventory_Item_Id
3085 And secondary_inventory = l_kanban_card_Rec_Tbl(1).Subinventory_Name;
3086 Exception
3087 When No_data_found
3088 Then
3089 l_need_by_time := l_PreProcess_lead_Time
3090 + l_Process_lead_Time
3091 + l_PostProcess_lead_Time;
3092 l_budget_Account_Id := l_Encumb_Account_Id;
3093 End;
3094
3095 l_sql_stmt_no := 20;
3096 select nvl(f.req_encumbrance_flag,'N'),o.operating_unit
3097 into l_encumb_flag,l_po_org_Id
3098 from financials_system_params_all f,
3099 org_organization_definitions o
3100 where o.organization_id = l_kanban_card_Rec_Tbl(1).Organization_id
3101 And nvl(f.org_id,-99) = nvl(o.operating_unit,-99);
3102
3103 IF l_kanban_card_Rec_Tbl(1).need_by_date IS NOT NULL THEN
3104 l_need_by_date := l_kanban_card_Rec_Tbl(1).need_by_date;
3105 ELSE
3106 l_sql_stmt_no := 30;
3107 select c1.calendar_date
3108 into l_need_by_date
3109 from mtl_parameters o,
3110 bom_calendar_dates c1,
3111 bom_calendar_dates c
3112 where o.organization_id = l_kanban_card_Rec_Tbl(1).Organization_id
3113 and c1.calendar_code = c.calendar_code
3114 and c1.exception_set_id = c.exception_set_id
3115 and c1.seq_num = (c.next_seq_num + trunc(nvl(p_lead_time,l_need_by_time)))
3116 and c.calendar_code = o.CALENDAR_CODE
3117 and c.exception_set_id = o.CALENDAR_EXCEPTION_SET_ID
3118 and c.calendar_date = trunc(sysdate);
3119 END IF;
3120 if l_kanban_card_Rec_Tbl(1).source_Type =
3121 INV_Kanban_PVT.G_Source_Type_InterOrg
3122 Then
3123
3124 -- MOAC: Replaced the po_location_associations
3125 -- view with a _ALL table.
3126 l_sql_stmt_no := 40;
3127 -- Bug Fix 5185446 : Added distinct
3128 select distinct org.location_id
3129 into l_deliver_location_id
3130 from hr_organization_units org,
3131 hr_locations loc,
3132 po_location_associations_all pla
3133 where org.organization_id =
3134 l_kanban_card_Rec_Tbl(1).Organization_id
3135 and org.location_id = loc.location_id
3136 and pla.location_id = loc.location_id;
3137
3138 Elsif l_kanban_card_Rec_Tbl(1).source_Type =
3139 INV_Kanban_PVT.G_Source_Type_Supplier
3140 Then
3141
3142 l_sql_stmt_no := 40;
3143 select org.location_id
3144 into l_deliver_location_id
3145 from hr_organization_units org,
3146 hr_locations loc
3147 where org.organization_id =
3148 l_kanban_card_Rec_Tbl(1).Organization_id
3149 and org.location_id = loc.location_id;
3150
3151 end if;
3152
3153 /* Bug 971203. Do not check for revision control code.Get the value from the
3154 profile and if the profile is Yes, then get revision */
3155
3156 revision_profile := fnd_profile.value('INV_PURCHASING_BY_REVISION') ;
3157 if revision_profile = 1 then
3158
3159 l_sql_stmt_no := 50;
3160
3161 select MAX(revision)
3162 into l_revision
3163 from mtl_item_revisions mir
3164 where inventory_item_id = l_kanban_card_Rec_Tbl(1).Inventory_Item_Id
3165 and organization_id = l_kanban_card_Rec_Tbl(1).organization_Id
3166 and effectivity_date < SYSDATE
3167 and implementation_date is not null /* Added for bug 7110794 */
3168 and effectivity_date =
3169 (
3170 select MAX(effectivity_date)
3171 from mtl_item_revisions mir1
3172 where mir1.inventory_item_id = mir.inventory_item_id
3173 and mir1.organization_id = mir.organization_id
3174 and implementation_date is not null /* Added for bug 7110794 */
3175 and effectivity_date < SYSDATE
3176 );
3177
3178 end if;
3179
3180
3181 l_sql_stmt_no := 60;
3182
3183 select employee_id
3184 into l_buyer_id
3185 from fnd_user
3186 where user_id = FND_GLOBAL.USER_ID;
3187
3188
3189
3190 /* Need to error */
3191 /*
3192 if (charge_acct is NULL) or
3193 (accru_acct is NULL) or
3194 (ipv_acct is NULL) or
3195 ((encum_flag <> 'N') and (budget_acct is null)) then
3196 select meaning into msg
3197 from mfg_lookups
3198 where lookup_type = 'INV_MMX_RPT_MSGS'
3199 and lookup_code = 1;
3200
3201 return(msg);
3202 -- return ('Unable to generate requisition');
3203 end if;
3204 */
3205
3206 If l_kanban_card_Rec_Tbl(1).source_type = INV_Kanban_PVT.G_Source_Type_InterOrg
3207 then
3208 l_source_type_code := 'INVENTORY';
3209 l_Requisition_type := 'INTERNAL';
3210 mydebug('create requisition INVENTORY INTERNAL');
3211 Create_Requisition( l_buyer_id, l_interface_source_code,
3212 l_requisition_type, l_approval,
3213 l_source_type_code, l_kanban_card_rec_tbl,
3214 l_destination_type_code, l_deliver_location_id,
3215 l_revision, l_item_description,
3216 l_primary_uom_code, l_need_by_date,
3217 l_charge_account_id, l_accrual_account_id,
3218 l_invoice_var_account_id, l_budget_account_id,
3219 l_autosource_flag, l_po_org_id );
3220 x_card_supply_status := INV_Kanban_PVT.G_Supply_Status_Empty;
3221 elsIf l_kanban_card_Rec_Tbl(1).source_type = INV_Kanban_PVT.G_Source_Type_Supplier
3222 Then
3223 l_source_type_code := 'VENDOR';
3224 l_Requisition_type := 'PURCHASE';
3225 mydebug('create requisition VENDOR PURCHASE');
3226 Create_Requisition( l_buyer_id, l_interface_source_code,
3227 l_requisition_type, l_approval,
3228 l_source_type_code, l_kanban_card_rec_tbl,
3229 l_destination_type_code, l_deliver_location_id,
3230 l_revision, l_item_description,
3231 l_primary_uom_code, l_need_by_date,
3232 l_charge_account_id, l_accrual_account_id,
3233 l_invoice_var_account_id, l_budget_account_id,
3234 l_autosource_flag, l_po_org_id );
3235 x_card_supply_status := INV_Kanban_PVT.G_Supply_Status_Empty;
3236 elsIf l_kanban_card_Rec_Tbl(1).source_type =
3237 INV_Kanban_PVT.G_Source_Type_IntraOrg
3238 Then
3239 l_source_type_code := 'INVENTORY';
3240 l_Requisition_type := 'TRANSFER';
3241 mydebug('create transfer order INVENTORY TRANSFER');
3242 Create_Transfer_Order(p_kanban_card_rec_tbl,l_need_by_date,l_Primary_Uom_Code);
3243 x_card_supply_status := INV_Kanban_PVT.G_Supply_Status_InProcess;
3244
3245 elsIf l_kanban_card_Rec_Tbl(1).source_type =
3246 INV_Kanban_PVT.G_Source_Type_Production
3247 Then
3248 l_source_type_code := 'PRODUCTION';
3249 l_Requisition_type := 'MAKE';
3250 mydebug('create wip job PRODUCTION MAKE ');
3251 Create_Wip_Job(p_kanban_card_rec_tbl, l_need_by_date,
3252 p_card_supply_status);
3253 x_card_supply_status := p_card_supply_status;
3254 else
3255 Return;
3256 end if;
3257
3258 EXCEPTION
3259
3260 WHEN NO_data_FOUND Then
3261
3262 If l_sql_stmt_no = 10
3263 Then
3264 FND_MESSAGE.SET_NAME('INV','INV-NO ITEM RECORD');
3265 Elsif l_sql_stmt_no = 20
3266 then
3267 FND_MESSAGE.SET_NAME('INV','INV-NO ORG INFORMATION');
3268 Elsif l_sql_stmt_no = 30
3269 Then
3270 FND_MESSAGE.SET_NAME('INV','INV-NO CALENDAR DATE');
3271 Elsif l_sql_stmt_no = 40
3272 Then
3273 FND_MESSAGE.SET_NAME('INV','INV_DEFAULT_DELIVERY_LOC_REQD');
3274 Elsif l_sql_stmt_no = 50
3275 Then
3276 FND_MESSAGE.SET_NAME('INV','INV_INT_REVCODE');
3277 Elsif l_sql_stmt_no = 60
3278 Then
3279 FND_MESSAGE.SET_NAME('FND','CONC-FDWHOAMI INVALID USERID');
3280 FND_MESSAGE.SET_TOKEN('USERID',to_char(FND_GLOBAL.USER_ID));
3281 End If;
3282 FND_MSG_PUB.Add;
3283 Raise FND_API.G_EXC_ERROR;
3284
3285 WHEN FND_API.G_EXC_ERROR THEN
3286
3287 Raise FND_API.G_EXC_ERROR;
3288
3289 WHEN OTHERS THEN
3290 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3291 THEN
3292 FND_MSG_PUB.Add_Exc_Msg
3293 ( G_PKG_NAME
3294 , 'Create_Replenishment'
3295 );
3296 END IF;
3297
3298 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
3299
3300 End Create_Replenishment;
3301
3302 --
3303 -- Check_And_Create_Replenishment() : This procedure will check whether it is
3304 -- ok to start replenishment cycle for a card.
3305 --
3306
3307 PROCEDURE Check_And_Create_Replenishment
3308 (x_return_status Out NOCOPY Varchar2,
3309 X_Supply_Status Out NOCOPY Number,
3310 X_Current_Replenish_Cycle_Id Out NOCOPY Number,
3311 P_Kanban_Card_Rec In Out NOCOPY INV_Kanban_PVT.Kanban_Card_Rec_Type)
3312 IS
3313
3314 l_Pull_Sequence_Rec Mtl_Kanban_Pull_Sequences%RowType;
3315 l_Wait_Kanban_card_Tbl Kanban_Card_Tbl_Type;
3316 l_Kanban_Card_Rec INV_Kanban_PVT.Kanban_Card_Rec_Type;
3317 l_Wait_Kanban_Size Number := 0;
3318 l_Card_Count Number := 0;
3319 l_Order_Count Number := 0;
3320 l_Current_replenish_cycle_Id Number;
3321 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
3322 l_Kanban_Card_tbl Kanban_Card_Tbl_Type;
3323 p_card_supply_status Number;
3324
3325
3326 Cursor Get_Cards_On_Wait
3327 Is
3328 Select Kanban_card_Id,kanban_size,
3329 NULL lot_item_id,null lot_number,NULL lot_item_revision,
3330 NULL lot_subinventory_code,NULL lot_location_id,NULL lot_quantity,
3331 NULL replenish_quantity
3332 From Mtl_Kanban_Cards
3333 Where Pull_Sequence_Id = p_Kanban_Card_Rec.Pull_Sequence_Id
3334 -- And Card_Status = INV_Kanban_PVT.G_Card_Status_Active
3335 And Supply_Status = INV_Kanban_PVT.G_Supply_Status_Wait
3336 And Nvl(Supplier_Id,-1) = Nvl(p_Kanban_Card_Rec.Supplier_Id,-1)
3337 And Nvl(Supplier_Site_Id,-1) =
3338 Nvl(p_Kanban_Card_Rec.Supplier_Site_Id,-1)
3339 And Nvl(Source_Organization_Id,-1) =
3340 Nvl(p_Kanban_Card_Rec.Source_Organization_Id,-1)
3341 And Nvl(Source_Subinventory,'#?#') =
3342 Nvl(p_Kanban_Card_Rec.Source_Subinventory,'#?#')
3343 And Nvl(Source_Locator_Id,-1) =
3344 Nvl(p_Kanban_Card_Rec.Source_Locator_Id,-1)
3345 And Nvl(wip_line_id,-1) =
3346 Nvl(p_Kanban_Card_Rec.wip_line_id,-1)
3347 -- Following condition added as a bugfix for bug#3389681 to prevent consideration of
3348 -- current card if it is in wait status as it will be considered twice.
3349 And Kanban_card_Id <> p_Kanban_Card_Rec.Kanban_card_Id
3350 For Update Of Supply_Status NoWait;
3351
3352 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3353 Begin
3354
3355 l_Kanban_Card_tbl.delete;
3356 l_Wait_Kanban_card_Tbl.Delete;
3357
3358 l_Order_Count := 1;
3359
3360 l_Kanban_Card_tbl(l_Order_Count) := p_kanban_card_rec;
3361
3362 If P_Kanban_Card_rec.Pull_sequence_Id = INV_Kanban_PVT.G_No_Pull_Sequence
3363 OR (P_Kanban_Card_Rec.kanban_card_type = INV_Kanban_PVT.G_Card_Type_NonReplenishable)
3364 Then
3365 l_Current_Replenish_Cycle_Id := Get_Next_Replenish_Cycle_Id;
3366 l_Kanban_Card_tbl(l_Order_Count).current_replnsh_cycle_id :=
3367 l_Current_Replenish_Cycle_Id;
3368 Create_Replenishment(l_Kanban_Card_tbl,null, p_card_supply_status);
3369 X_Supply_Status := p_card_supply_status;
3370 X_Current_Replenish_Cycle_Id := l_Current_Replenish_Cycle_Id;
3371 else
3372 Select *
3373 Into l_Pull_Sequence_Rec
3374 From Mtl_Kanban_Pull_Sequences
3375 Where Pull_Sequence_Id = P_Kanban_Card_Rec.Pull_Sequence_Id
3376 For Update Of Minimum_Order_Quantity NOWait;
3377
3378 If nvl(l_Pull_Sequence_Rec.Minimum_Order_Quantity,0) = 0
3379 Then
3380 l_Current_Replenish_Cycle_Id := Get_Next_Replenish_Cycle_Id;
3381 l_Kanban_Card_tbl(l_Order_Count).current_replnsh_cycle_id := l_Current_Replenish_Cycle_Id;
3382 Create_Replenishment(l_Kanban_Card_tbl,l_pull_sequence_rec.replenishment_lead_time, p_card_supply_status);
3383 X_Supply_Status := p_card_supply_status;
3384 X_Current_Replenish_Cycle_Id := l_Current_Replenish_Cycle_Id;
3385
3386 Elsif (P_Kanban_Card_Rec.Kanban_Size >=
3387 l_Pull_Sequence_Rec.minimum_order_quantity)
3388 OR (P_Kanban_Card_Rec.lot_number IS NOT NULL)
3389 Then
3390
3391 l_Current_Replenish_Cycle_Id := Get_Next_Replenish_Cycle_Id;
3392 l_Kanban_Card_tbl(l_Order_Count).current_replnsh_cycle_id :=
3393 l_Current_Replenish_Cycle_Id;
3394 Create_Replenishment(l_Kanban_Card_tbl,l_pull_sequence_rec.replenishment_lead_time, p_card_supply_status);
3395 X_Supply_Status := p_card_supply_status;
3396 X_Current_Replenish_Cycle_Id := l_Current_Replenish_Cycle_Id;
3397
3398 Else
3399
3400 For l_kanban_card in Get_Cards_On_Wait
3401 Loop
3402
3403 l_card_count := l_card_count + 1;
3404 l_Wait_Kanban_card_Tbl(l_card_count).Kanban_card_Id := l_kanban_card.Kanban_Card_Id;
3405 l_Wait_Kanban_card_Tbl(l_card_count).Kanban_Size := l_kanban_card.Kanban_Size;
3406 l_Wait_Kanban_Size := l_Wait_Kanban_Size + l_kanban_card.Kanban_Size;
3407
3408 l_Wait_Kanban_card_Tbl(l_card_count).lot_item_id := l_kanban_card.lot_item_id;
3409 l_Wait_Kanban_card_Tbl(l_card_count).lot_number := l_kanban_card.lot_number;
3410 l_Wait_Kanban_card_Tbl(l_card_count).lot_item_revision := l_kanban_card.lot_item_revision;
3411 l_Wait_Kanban_card_Tbl(l_card_count).lot_subinventory_code := l_kanban_card.lot_subinventory_code;
3412 l_Wait_Kanban_card_Tbl(l_card_count).lot_location_id := l_kanban_card.lot_location_id;
3413 l_Wait_Kanban_card_Tbl(l_card_count).lot_quantity := l_kanban_card.lot_quantity;
3414 l_Wait_Kanban_card_Tbl(l_card_count).replenish_quantity := l_kanban_card.replenish_quantity;
3415
3416 End Loop;
3417
3418 if (l_Wait_Kanban_Size + p_kanban_Card_rec.kanban_Size) >=
3419 l_Pull_Sequence_Rec.Minimum_Order_Quantity
3420 Then
3421
3422 l_Current_replenish_Cycle_Id := Get_Next_Replenish_Cycle_Id;
3423 For l_card_Count in 1..l_Wait_Kanban_card_Tbl.Count
3424 Loop
3425
3426 l_order_count := l_order_count + 1;
3427 l_Kanban_card_Tbl(l_Order_Count).Kanban_card_Id :=
3428 l_Wait_Kanban_card_Tbl(l_Card_Count).Kanban_Card_Id;
3429 l_Kanban_card_Tbl(l_Order_Count).Kanban_Size :=
3430 l_Wait_Kanban_card_Tbl(l_Card_Count).Kanban_Size;
3431
3432 End Loop;
3433
3434 l_Kanban_Card_tbl(1).current_replnsh_cycle_id :=
3435 l_Current_Replenish_Cycle_Id;
3436 create_Replenishment(l_Kanban_card_Tbl,l_pull_sequence_rec.replenishment_lead_time, p_card_supply_status);
3437 X_Supply_Status := p_card_supply_status;
3438 X_Current_Replenish_Cycle_Id := l_Current_Replenish_Cycle_Id;
3439
3440 For l_card_Count in 2..l_Kanban_card_Tbl.Count
3441 Loop
3442 Update Mtl_Kanban_Cards
3443 Set Supply_Status = p_card_supply_status,
3444 Current_Replnsh_Cycle_Id = l_Current_Replenish_Cycle_Id,
3445 Last_Update_Date = SYSDATE,
3446 Last_Updated_By = FND_GLOBAL.USER_ID
3447 Where Kanban_Card_Id = l_Kanban_card_Tbl(l_Card_Count).Kanban_card_Id;
3448
3449 l_Kanban_Card_Rec.Kanban_card_Id :=
3450 l_Kanban_card_Tbl(l_Card_Count).Kanban_card_Id;
3451 l_kanban_card_rec :=
3452 INV_KanbanCard_PKG.Query_Row( p_Kanban_Card_id =>
3453 l_Kanban_card_Tbl(l_Card_Count).Kanban_card_Id);
3454 l_kanban_card_rec.document_type :=
3455 l_Kanban_card_Tbl(l_Card_Count).document_type;
3456 l_kanban_card_rec.document_header_id :=
3457 l_Kanban_card_Tbl(l_Card_Count).document_header_id;
3458 l_kanban_card_rec.document_detail_id :=
3459 l_Kanban_card_Tbl(l_Card_Count).document_detail_id;
3460
3461 INV_KanbanCard_PKG.Insert_Activity_For_Card(l_Kanban_Card_Rec);
3462
3463 End Loop;
3464 Else
3465
3466 X_Supply_Status := INV_Kanban_PVT.G_Supply_Status_Wait;
3467 X_Current_Replenish_Cycle_Id := Null;
3468
3469 End If;
3470 End If;
3471 End If;
3472 x_return_status := l_return_status;
3473
3474 p_kanban_card_rec.document_type :=
3475 l_Kanban_card_Tbl(1).document_type;
3476 p_kanban_card_rec.document_header_id :=
3477 l_Kanban_card_Tbl(1).document_header_id;
3478 p_kanban_card_rec.document_detail_id :=
3479 l_Kanban_card_Tbl(1).document_detail_id;
3480
3481 EXCEPTION
3482
3483 WHEN FND_API.G_EXC_ERROR THEN
3484
3485 x_return_status := FND_API.G_RET_STS_ERROR;
3486
3487 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3488
3489 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3490
3491 WHEN OTHERS THEN
3492
3493 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3494
3495 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3496 THEN
3497 FND_MSG_PUB.Add_Exc_Msg
3498 ( G_PKG_NAME
3499 , 'Check_And_Create_Replenishment'
3500 );
3501 END IF;
3502
3503 End Check_And_Create_Replenishment;
3504
3505 Procedure test IS
3506 i NUMBER := 0;
3507 l_pull_seq_id_tbl INV_kanban_PVT.pull_sequence_id_Tbl_Type;
3508 l_return_status VARCHAR2(1):= FND_API.G_RET_STS_SUCCESS;
3509 l_operation_tbl INV_Kanban_PVT.operation_tbl_type;/*This new local var has been added to
3510 keep in synch with the call to procedure update_pull_sequence_tbl */
3511 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3512 begin
3513
3514 For pull_seq_rec IN (select pull_sequence_id from MTL_KANBAN_PULL_SEQUENCES
3515 Where Kanban_plan_id = -1 ) LOOP
3516 i := i + 1;
3517 l_pull_seq_id_tbl(i) := pull_seq_rec.pull_sequence_id ;
3518 l_operation_tbl(i) := 0; --Storing 0 for update
3519 END LOOP;
3520
3521 update_pull_sequence_tbl( l_return_status, l_pull_seq_id_tbl, 'Y',l_operation_tbl );
3522
3523 IF l_return_status IN ( FND_API.G_RET_STS_ERROR,
3524 FND_API.G_RET_STS_UNEXP_ERROR) THEN
3525 RAISE FND_API.G_EXC_ERROR;
3526 END IF;
3527
3528 EXCEPTION
3529
3530 WHEN FND_API.G_EXC_ERROR THEN
3531 Raise FND_API.G_EXC_ERROR;
3532
3533 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3534 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
3535
3536 WHEN OTHERS THEN
3537
3538 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3539 THEN
3540 FND_MSG_PUB.Add_Exc_Msg
3541 ( G_PKG_NAME
3542 , 'test'
3543 );
3544 END IF;
3545
3546 END test;
3547
3548 PROCEDURE update_kanban_card_status
3549 (p_Card_Status IN Number,
3550 p_pull_sequence_id IN Number)
3551
3552 IS
3553 l_kanban_card_rec INV_Kanban_PVT.kanban_card_rec_type;
3554 CURSOR get_kanban_card_ids IS
3555 SELECT kanban_card_id
3556 FROM mtl_kanban_cards
3557 WHERE pull_sequence_id = p_pull_sequence_id ;
3558
3559 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3560 BEGIN
3561
3562 for kanban_cards_ids in get_kanban_card_ids
3563 LOOP
3564 l_kanban_card_rec := inv_kanbancard_pkg.query_row(kanban_cards_ids.kanban_card_id);
3565 inv_kanbancard_pkg.update_card_status
3566 (p_kanban_card_rec =>l_kanban_card_rec,
3567 p_card_status => p_card_status);
3568 END LOOP;
3569
3570 END update_kanban_card_status;
3571
3572 PROCEDURE return_att_quantity(p_org_id IN NUMBER,
3573 p_item_id IN NUMBER,
3574 p_rev IN VARCHAR2,
3575 p_lot_no IN VARCHAR2,
3576 p_subinv IN VARCHAR2,
3577 p_locator_id IN NUMBER,
3578 x_qoh OUT NOCOPY NUMBER,
3579 x_atr OUT NOCOPY NUMBER,
3580 x_att OUT NOCOPY NUMBER,
3581 x_err_code OUT NOCOPY NUMBER,
3582 x_err_msg OUT NOCOPY VARCHAR2)
3583 IS
3584 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3585 BEGIN
3586
3587 wsmputil.return_att_quantity(p_org_id => p_org_id
3588 ,p_item_id => p_item_id
3589 ,p_rev => p_rev
3590 ,p_lot_no => p_lot_no
3591 ,p_subinv => p_subinv
3592 ,p_locator_id => p_locator_id
3593 ,p_qoh => x_qoh
3594 ,p_atr => x_atr
3595 ,p_att => x_att
3596 ,p_err_code => x_err_code
3597 ,p_err_msg => x_err_msg);
3598
3599 EXCEPTION
3600 WHEN OTHERS THEN
3601 x_qoh := NULL;
3602 x_atr := NULL;
3603 x_att := NULL;
3604 x_err_code := -1;
3605 x_err_msg := Substr(Sqlerrm,1,255);
3606
3607 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3608 THEN
3609 FND_MSG_PUB.Add_Exc_Msg
3610 ( G_PKG_NAME
3611 ,'return_att_quantity'
3612 );
3613 END IF;
3614
3615 END return_att_quantity;
3616
3617 PROCEDURE get_max_kanban_asmbly_qty
3618 ( p_bill_seq_id IN NUMBER,
3619 P_COMPONENT_ITEM_ID IN NUMBER,
3620 P_BOM_REVISION_DATE IN DATE,
3621 P_START_SEQ_NUM IN NUMBER,
3622 P_AVAILABLE_QTY IN NUMBER,
3623 X_MAX_ASMBLY_QTY OUT NOCOPY NUMBER,
3624 X_ERROR_CODE OUT NOCOPY NUMBER,
3625 X_error_msg OUT NOCOPY VARCHAR2)
3626 IS
3627
3628 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3629 BEGIN
3630
3631 wsmputil.get_max_kanban_asmbly_qty
3632 ( P_BILL_SEQ_ID => p_bill_seq_id,
3633 P_COMPONENT_ITEM_ID => p_component_item_id,
3634 P_BOM_REVISION_DATE => Nvl(p_bom_revision_date,Sysdate),
3635 P_START_SEQ_NUM => p_start_seq_num,
3636 P_AVAILABLE_QTY => p_available_qty,
3637 P_MAX_ASMBLY_QTY => x_max_asmbly_qty,
3638 P_ERROR_CODE => x_error_code,
3639 p_error_msg => x_error_msg);
3640 EXCEPTION
3641 WHEN OTHERS THEN
3642 x_max_asmbly_qty := NULL;
3643 x_error_code := -1;
3644 x_error_msg := Substr(Sqlerrm,1,255);
3645
3646 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3647 THEN
3648 FND_MSG_PUB.Add_Exc_Msg
3649 ( G_PKG_NAME
3650 ,'return_max_kanban_asmbly_qty'
3651 );
3652 END IF;
3653
3654 END get_max_kanban_asmbly_qty;
3655
3656 FUNCTION eligible_for_lbj
3657 (p_organization_id IN NUMBER,
3658 p_inventory_item_id IN NUMBER,
3659 p_source_type_id IN NUMBER,
3660 p_kanban_card_id IN NUMBER)
3661 RETURN VARCHAR2 IS
3662 l_source_type_id NUMBER := p_source_type_id;
3663 l_rep_flag VARCHAR2(1) := NULL;
3664 l_lot_control NUMBER := NULL;
3665 l_cfm_flag NUMBER := NULL;
3666 l_assembly_item_id NUMBER := p_inventory_item_id;
3667 l_organization_id NUMBER := p_organization_id;
3668 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3669 BEGIN
3670 --fnd_message.debug('Inside eligible');
3671
3672 IF wsmpvers.get_osfm_release_version <= '110508' THEN
3673 RETURN 'N';
3674 ELSIF l_source_type_id = inv_kanban_pvt.g_source_type_production THEN
3675 --Source type production
3676 BEGIN
3677 select
3678 Nvl(repetitive_planning_flag,'N'), lot_control_code
3679 into
3680 l_rep_flag, l_lot_control
3681 from MTL_SYSTEM_ITEMS
3682 where
3683 inventory_item_id = l_assembly_item_id AND
3684 organization_id = l_organization_id;
3685 EXCEPTION
3686 when others THEN
3687 RAISE fnd_api.g_exc_unexpected_error;
3688 end;
3689
3690 --lot controlled
3691 IF l_rep_flag = 'N' AND l_lot_control = 2 THEN
3692
3693 BEGIN
3694 SELECT nvl(cfm_routing_flag,0)
3695 into l_cfm_flag
3696 from BOM_OPERATIONAL_ROUTINGS
3697 where
3698 assembly_item_id = l_assembly_item_id AND
3699 organization_id = l_organization_id AND
3700 alternate_routing_designator is NULL;
3701 EXCEPTION
3702 when no_data_found THEN
3703 l_cfm_flag := 2;
3704 WHEN OTHERS THEN
3705 RAISE fnd_api.g_exc_unexpected_error;
3706 END;
3707
3708
3709
3710 IF l_cfm_flag = 3 THEN
3711 -- network routing hence return true
3712 RETURN 'Y';
3713 ELSE --Bug# 3249105
3714 RETURN 'N';
3715 END IF;
3716
3717 ELSE --rep_flag = 'Y' or not lot controlled
3718 RETURN 'N';
3719 END IF;
3720
3721 ELSE --source_type <> production
3722 RETURN 'N';
3723 END IF;
3724
3725 --fnd_message.debug(' end ');
3726
3727 EXCEPTION
3728 WHEN OTHERS THEN
3729 RETURN 'N';
3730 END eligible_for_lbj;
3731
3732 PROCEDURE GET_KANBAN_REC_GRP_INFO
3733 (p_organization_id IN NUMBER,
3734 p_kanban_assembly_id IN NUMBER,
3735 p_rtg_rev_date IN DATE,
3736 x_bom_seq_id OUT NOCOPY NUMBER,
3737 x_start_seq_num OUT NOCOPY NUMBER,
3738 X_error_code OUT NOCOPY NUMBER,
3739 X_error_msg OUT NOCOPY VARCHAR2) IS
3740 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3741 BEGIN
3742 wsmputil.GET_KANBAN_REC_GRP_INFO(p_organization_id => p_organization_id,
3743 p_kanban_assembly_id => p_kanban_assembly_id,
3744 p_rtg_rev_date => p_rtg_rev_date,
3745 p_bom_seq_id => x_bom_seq_id,
3746 p_start_seq_num => x_start_seq_num,
3747 p_error_code => x_error_code,
3748 p_error_msg => x_error_msg);
3749
3750 EXCEPTION
3751 WHEN OTHERS THEN
3752 x_error_code := -1;
3753 x_error_msg := Substr(Sqlerrm,1,255);
3754
3755 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3756 THEN
3757 FND_MSG_PUB.Add_Exc_Msg
3758 ( G_PKG_NAME
3759 ,'GET_KANBAN_REC_GRP_INFO'
3760 );
3761 END IF;
3762
3763 END get_kanban_rec_grp_info;
3764
3765
3766 /*Bug 3740514--New procedure to check if the card status should be updated.*/
3767
3768 PROCEDURE update_card_and_card_status(p_kanban_card_id IN NUMBER, p_supply_status IN NUMBER, p_update OUT NOCOPY BOOLEAN) IS
3769
3770 CURSOR mtl_kca IS
3771 SELECT replenishment_cycle_id
3772 , document_header_id
3773 , document_detail_id
3774 FROM mtl_kanban_card_activity
3775 WHERE kanban_card_id = p_kanban_card_id
3776 AND document_header_id IS NOT NULL
3777 ORDER BY kanban_activity_id DESC;
3778
3779
3780 -- MOAC: Changed po_distributions to po_distributions_all.
3781
3782 CURSOR po_dist(po_rel_id NUMBER, po_dist_id NUMBER) IS
3783 SELECT NVL(quantity_delivered, 0)
3784 FROM po_distributions_all
3785 WHERE po_release_id = po_rel_id
3786 AND po_distribution_id = po_dist_id;
3787
3788 l_rep_cycl_id NUMBER;
3789 l_crd_doc_hdr_id NUMBER;
3790 l_crd_doc_det_id NUMBER;
3791 l_max_rep_id NUMBER;
3792 l_del_qty NUMBER := -33;
3793 l_doc_type_id NUMBER;
3794 l_max_req NUMBER;/*Bug#4490269*/
3795 l_req NUMBER;/*Bug#4490269*/
3796
3797
3798 BEGIN
3799
3800 p_update := TRUE; -- By Default update the kanban card and kanban card activity
3801
3802 -- Bug 3987589; Added the AND condition 'AND document_type <> fnd_api.g_miss_num' and the exception block
3803 IF p_supply_status = INV_Kanban_PVT.G_Supply_Status_Full THEN /*Bug 4490269*/
3804
3805 BEGIN
3806 SELECT document_type
3807 , replenishment_cycle_id
3808 INTO l_doc_type_id
3809 , l_max_rep_id
3810 FROM mtl_kanban_card_activity
3811 WHERE kanban_card_id = p_kanban_card_id
3812 AND document_type IS NOT NULL
3813 AND document_type <> fnd_api.g_miss_num
3814 AND replenishment_cycle_id = (SELECT MAX(replenishment_cycle_id)
3815 FROM mtl_kanban_card_activity
3816 WHERE kanban_card_id = p_kanban_card_id);
3817 EXCEPTION
3818 WHEN TOO_MANY_ROWS THEN
3819 mydebug('Multiple document types and maximum replenishment cycle id returned for kanban card Id ' || p_kanban_card_id||' ;Hence disallowing Supply status update');
3820 p_update := FALSE;
3821 RETURN;
3822 END;
3823
3824 mydebug('Document type, maximum Replenishment cycle Id for kanban card Id ' || p_kanban_card_id || ': ' || l_doc_type_id || ', '
3825 || l_max_rep_id);
3826
3827 /* Only if the document type is Blanket Release, then continue */
3828
3829 IF (l_doc_type_id IN (inv_kanban_pvt.g_doc_type_release,INV_kanban_PVT.G_Doc_type_PO)) THEN
3830 OPEN mtl_kca;
3831
3832 FETCH mtl_kca
3833 INTO l_rep_cycl_id
3834 , l_crd_doc_hdr_id
3835 , l_crd_doc_det_id;
3836
3837 CLOSE mtl_kca;
3838
3839 IF (l_rep_cycl_id IS NOT NULL
3840 AND l_crd_doc_hdr_id IS NOT NULL
3841 AND l_crd_doc_det_id IS NOT NULL) THEN
3842 IF l_rep_cycl_id = l_max_rep_id THEN
3843 OPEN po_dist(l_crd_doc_hdr_id, l_crd_doc_det_id);
3844
3845 FETCH po_dist
3846 INTO l_del_qty;
3847
3848 CLOSE po_dist;
3849 ELSE
3850 p_update := FALSE;
3851 END IF;
3852
3853 /* If the delivered quantity is 0, then Correction/Return/Receipt/Receiving Transaction of some other
3854 Release is trying to update the card and card activity status, which should not be allowed */
3855 IF (l_del_qty = 0) THEN
3856 p_update := FALSE;
3857 END IF;
3858 END IF; -- if l_rep_cycl_id IS NOT NULL ....
3859 END IF; -- if l_doc_type_id = INV_kanban_PVT.G_Doc_type_Release
3860
3861 /*Bug#4490268--If InProcess, then need to check for any pending requsitions/PO/Release*/
3862 ELSIF p_supply_status = INV_Kanban_PVT.G_Supply_Status_InProcess THEN
3863
3864 SELECT max(requisition_line_id) into l_max_req
3865 FROM po_requisition_lines
3866 WHERE kanban_card_id = p_kanban_card_id;
3867
3868 BEGIN
3869 SELECT 1 INTO l_req
3870 FROM po_requisitions_interface
3871 WHERE kanban_card_id = p_kanban_card_id;
3872 EXCEPTION
3873 WHEN NO_DATA_FOUND THEN
3874 BEGIN
3875 SELECT 1 INTO l_req
3876 FROM po_requisition_lines
3877 WHERE kanban_card_id = p_kanban_card_id
3878 AND requisition_line_id = l_max_req
3879 AND line_location_id IS NULL;
3880 EXCEPTION
3881 WHEN NO_DATA_FOUND THEN
3882 BEGIN
3883 SELECT 1 INTO l_req
3884 FROM po_requisition_lines prl1
3885 WHERE prl1.kanban_card_id = p_kanban_card_id
3886 AND prl1.requisition_line_id = l_max_req
3887 AND prl1.line_location_id IS NOT NULL
3888 AND (EXISTS (SELECT '1' FROM po_headers poh
3889 WHERE EXISTS ( SELECT '1' FROM po_line_locations pll
3890 WHERE pll.line_location_id = prl1.line_location_id
3891 AND pll.po_header_id = poh.po_header_id
3892 AND nvl(poh.authorization_status,'%%') <> 'APPROVED'))
3893 OR EXISTS (SELECT '1' FROM po_releases pr
3894 WHERE EXISTS ( SELECT '1' FROM po_line_locations pll
3895 WHERE pll.line_location_id = prl1.line_location_id
3896 AND pll.po_release_id = pr.po_release_id
3897 AND nvl(pr.authorization_status,'%%') <> 'APPROVED')));
3898 EXCEPTION
3899 WHEN NO_DATA_FOUND THEN
3900 l_req := 0;
3901 END;
3902 END;
3903 END;
3904
3905 IF l_req = 1 THEN
3906 p_update := FALSE;
3907 END IF;
3908 END IF;
3909
3910 END update_card_and_card_status;
3911
3912
3913 /* The following procedure Auto_Allocate_Kanban is added for 3905884.
3914 This procedure automatically allocates the move order created for a Kanaban
3915 Replenishment if the auto_allocate flag is set to "Yes" */
3916
3917 PROCEDURE Auto_Allocate_Kanban (
3918 p_mo_header_id IN NUMBER ,
3919 x_return_status OUT NOCOPY VARCHAR2 ,
3920 x_msg_count OUT NOCOPY NUMBER ,
3921 x_msg_data OUT NOCOPY VARCHAR2
3922 ) IS
3923
3924 l_txn_header_id NUMBER;
3925 l_txn_temp_id NUMBER;
3926 l_number_of_rows NUMBER := 0;
3927 l_detailed_quantity NUMBER := 0;
3928 l_revision VARCHAR2(100) := NULL;
3929 l_from_locator_id NUMBER := 0;
3930 l_to_locator_id NUMBER := 0;
3931 -- Increased lot size to 80 Char - Mercy Thomas - B4625329
3932 l_lot_number VARCHAR2(80);
3933 l_expiration_date DATE;
3934 l_serial_control_code VARCHAR2(1) ;
3935 l_move_order_type NUMBER := INV_GLOBALS.G_MOVE_ORDER_REPLENISHMENT;
3936 l_failed_lines NUMBER := 0;
3937 l_return_status VARCHAR2(1);
3938 l_msg_count NUMBER ;
3939 l_msg_data VARCHAR2(2000);
3940 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3941
3942
3943 /*Cursor to get mo line informations */
3944 CURSOR mo_lines_cur IS
3945 SELECT MTRL.line_id , MTRL.inventory_item_id,MTRL.organization_id,MTRL.quantity
3946 FROM MTL_TXN_REQUEST_LINES MTRL
3947 WHERE MTRL.header_id = p_mo_header_id;
3948
3949 l_mo_line_rec mo_lines_cur%ROWTYPE;
3950 BEGIN
3951 IF (l_debug = 1 ) THEN
3952 inv_pick_wave_pick_confirm_pub.tracelog('In Auto_Allocate_Kanban ...','INV_KANBAN_PVT');
3953 END IF;
3954
3955 OPEN mo_lines_cur;
3956
3957 LOOP
3958 FETCH mo_lines_cur INTO l_mo_line_rec;
3959 EXIT WHEN mo_lines_cur%NOTFOUND;
3960
3961 /*Get the next header id*/
3962 SELECT mtl_material_transactions_s.NEXTVAL
3963 INTO l_txn_header_id
3964 FROM DUAL;
3965
3966 /* Check whether item is serial controlled or not */
3967 SELECT DECODE(serial_number_control_code,1,'F','T')
3968 INTO l_serial_control_code
3969 FROM mtl_system_items
3970 WHERE inventory_item_id = l_mo_line_rec.inventory_item_id
3971 AND organization_id = l_mo_line_rec.organization_id;
3972
3973 INV_Replenish_Detail_PUB.Line_Details_PUB (
3974 p_line_id => l_mo_line_rec.line_id,
3975 x_number_of_rows => l_number_of_rows,
3976 x_detailed_qty => l_detailed_quantity,
3977 x_return_status => x_return_status,
3978 x_msg_count => l_msg_count,
3979 x_msg_data => l_msg_data ,
3980 x_revision => l_revision,
3981 x_locator_id => l_from_locator_id ,
3982 x_transfer_to_location => l_to_locator_id,
3983 x_lot_number => l_lot_number,
3984 x_expiration_date => l_expiration_date,
3985 x_transaction_temp_id => l_txn_temp_id,
3986 p_transaction_header_id=> l_txn_header_id,
3987 p_transaction_mode => NULL ,
3988 p_move_order_type => l_move_order_type,
3989 p_serial_flag => l_serial_control_code,
3990 p_plan_tasks => FALSE ,
3991 p_commit => TRUE
3992 );
3993
3994 update mtl_txn_request_lines
3995 set quantity_detailed = l_detailed_quantity
3996 where line_id=l_mo_line_rec.line_id;
3997
3998 IF (l_debug = 1 ) THEN
3999 inv_pick_wave_pick_confirm_pub.tracelog('In Auto_Allocate_Kanban : the line '||l_mo_line_rec.line_id ||' return status :'||
4000 x_return_status || 'number_of_rows:' || l_number_of_rows ||' detailed_qty:'||l_detailed_quantity ||
4001 ' revision:'||l_revision||' from_locator_id:'||l_from_locator_id||' to_location:'||l_to_locator_id ||
4002 'lot_number:' || l_lot_number || ' transaction_temp_id' || l_txn_header_id ,'INV_KANBAN_PVT');
4003 END IF;
4004
4005 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS
4006 OR l_mo_line_rec.quantity <> l_detailed_quantity )
4007 THEN
4008 l_failed_lines := l_failed_lines + 1; --count the unallocated lines.
4009 END IF;
4010 END LOOP;
4011 CLOSE mo_lines_cur;
4012
4013 IF l_failed_lines > 0 THEN
4014 x_return_status := FND_API.G_RET_STS_ERROR;
4015 ELSE
4016 x_return_status := FND_API.G_RET_STS_SUCCESS;
4017 END IF;
4018
4019 IF (l_debug = 1 ) THEN
4020 inv_pick_wave_pick_confirm_pub.tracelog('In Auto_Allocate_Kanban : return status :'||x_return_status||' msg:'||x_msg_data,'INV_KANBAN_PVT');
4021 END IF;
4022 EXCEPTION
4023 WHEN OTHERS THEN
4024 IF (l_debug = 1 ) THEN
4025 inv_pick_wave_pick_confirm_pub.tracelog('In Auto_Allocate_Kanban : Exception : When Others','INV_KANBAN_PVT');
4026 x_return_status := FND_API.G_RET_STS_ERROR;
4027 END IF;
4028 END Auto_Allocate_Kanban;
4029
4030
4031 END INV_Kanban_PVT;