[Home] [Help]
PACKAGE BODY: APPS.INVKBCGN
Source
1 PACKAGE BODY INVKBCGN as
2 /* $Header: INVKBCGB.pls 120.15.12020000.3 2013/02/19 07:27:00 akuppa ship $ */
3
4 -- Global constant holding the package name
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'INVKBCGN';
6
7 -- Global var holding the Current Error code for the error encountered
8 Current_Error_Code Varchar2(20) := NULL;
9
10
11 /*
12 Commented as part of bug fix 2493917.
13 Procedure put_line is used now to write the
14 logs.
15
16 /*
17
18 procedure : Set_Log_File
19 This procedure dynamically set the log and out file directories
20 */
21 /*
22 Procedure Set_Log_File IS
23
24 v_db_name VARCHAR2(100);
25 v_log_name VARCHAR2(100);
26 v_db_name VARCHAR2(100);
27 v_st_position number(3);
28 v_end_position number(3);
29 v_w_position number(3);
30
31 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
32 Begin
33 select INSTR(value,',',1,2),INSTR(value,',',1,3)
34 into v_st_position,v_end_position
35 from v$parameter
36 where upper(name) = 'UTL_FILE_DIR';
37
38 v_w_position := v_end_position - v_st_position - 1;
39
40 select substr(value,v_st_position+1,v_w_position)
41 into v_log_name
42 from v$parameter
43 where upper(name) = 'UTL_FILE_DIR';
44 v_log_name := ltrim(v_log_name);
45 FND_FILE.PUT_NAMES(v_log_name,v_log_name,v_log_name);
46
47 End Set_Log_File;
48 */
49
50 /*
51 This method is added as part of bug fix 2493917.
52 This method will write log messages to log file
53 for both modes, concurrent as well as standalone.
54 Internally it uses INV_LOG_UTIL.TRACE for writting
55 log messages to the log file.
56 */
57 PROCEDURE put_line(msg VARCHAR2) IS
58 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
59 BEGIN
60 IF (l_debug = 1) THEN
61 INV_LOG_UTIL.TRACE(msg,g_pkg_name);
62 END IF;
63 END put_line;
64
65
66 /* Main procedure for kanban card creation */
67
68
69 PROCEDURE Create_Kanban_Cards(
70 ERRBUF OUT NOCOPY VARCHAR2,
71 RETCODE OUT NOCOPY NUMBER,
72 X_ORG_ID IN NUMBER,
73 X_ITEM_LO IN VARCHAR2,
74 X_ITEM_HI IN VARCHAR2,
75 X_SUBINV IN VARCHAR2,
76 X_LOCATOR_LO IN VARCHAR2,
77 X_LOCATOR_HI IN VARCHAR2,
78 X_SOURCE_TYPE IN NUMBER,
79 X_SUPPLIER_ID IN NUMBER,
80 X_SUPPLIER_SITE_ID IN NUMBER,
81 X_SOURCING_ORG_ID IN NUMBER,
82 X_SOURCING_SUBINV IN VARCHAR2,
83 X_SOURCING_LOC_ID IN NUMBER,
84 X_WIP_LINE_ID IN NUMBER,
85 X_STATUS IN NUMBER,
86 X_PULL_SEQ_ID IN NUMBER,
87 X_PRINT_KANBAN_CARD IN NUMBER,
88 X_REPORT_ID IN NUMBER,
89 --ekanban changes by javakat
90 X_ENABLE_EKANBAN IN NUMBER DEFAULT 2,
91 X_ENABLE_DUMMY IN VARCHAR2 DEFAULT NULL,
92 X_CREATE_CARD_FLAG IN NUMBER DEFAULT 1,
93 X_PLAN_CARD_FLAG IN NUMBER DEFAULT 2) IS
94 v_Retcode Number;
95 CONC_STATUS BOOLEAN;
99 BEGIN
96 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
97 L_CREATE_CARD_FLAG Number ;
98 L_PLAN_CARD_FLAG Number ;
100
101 -- This statement will help running this program in standalone mode
102 -- For bug 2464024 : Commenting out set_log_file and fnd_file.put_line
103 -- statements since its giving unhandled exception when generating kanban cards.
104 /* Set_Log_File; */
105
106 -- FND_FILE.PUT_NAMES('/sqlcom/log/kb.log', '/sqlcom/log/kb.out', '/sqlcom/log' );
107 L_CREATE_CARD_FLAG := nvl(X_CREATE_CARD_FLAG,1);
108 L_PLAN_CARD_FLAG := nvl(X_PLAN_CARD_FLAG,2);
109 PUT_LINE('P_ORG_ID='||to_char(X_ORG_ID));
110 PUT_LINE('P_ORG_ID='||(X_ORG_ID));
111 PUT_LINE('P_ITEM_LO='''||X_ITEM_LO || '''');
112 PUT_LINE('P_ITEM_HI='''||X_ITEM_HI || '''');
113 PUT_LINE('P_SUBINV='''||X_SUBINV || '''');
114 PUT_LINE('P_LOCATOR_LO='''||X_LOCATOR_LO || '''');
115 PUT_LINE('P_LOCATOR_HI='''||X_LOCATOR_HI || '''');
116 PUT_LINE('P_SOURCE_TYPE='||to_char(X_SOURCE_TYPE));
117 PUT_LINE('P_SUPPLIER_ID='||to_char(X_SUPPLIER_ID));
118 PUT_LINE('P_SUPPLIER_SITE_ID='||to_char(X_SUPPLIER_SITE_ID ));
119 PUT_LINE('P_SOURCING_ORG_ID='||to_char(X_SOURCING_ORG_ID ));
120 PUT_LINE('P_SOURCING_SUBINV='''||X_SOURCING_SUBINV || '''');
121 PUT_LINE('P_SOURCING_LOC_ID='||to_char(X_SOURCING_LOC_ID));
122 PUT_LINE('P_WIP_LINE_ID='||to_char(X_WIP_LINE_ID));
123 PUT_LINE('P_STATUS='||to_char(X_STATUS));
124 PUT_LINE('P_PULL_SEQ_ID='||to_char(X_PULL_SEQ_ID));
125 PUT_LINE('P_PRINT_CARD='||to_char(X_PRINT_KANBAN_CARD));
126 PUT_LINE('P_REPORT_ID='||to_char(X_REPORT_ID));
127 PUT_LINE('P_ENABLE_EKANBAN='||to_char(X_ENABLE_EKANBAN));
128 PUT_LINE('P_CREATE_CARD_FLAG='||L_CREATE_CARD_FLAG);
129 PUT_LINE('P_PLAN_CARD_FLAG='||L_PLAN_CARD_FLAG);
130 PUT_LINE(' ');
131
132 if ( X_pull_seq_id IS NOT NULL ) OR (X_REPORT_ID IS NOT NULL )then
133 PUT_LINE('Calling resolve_pullseq_with_pull ');
134 v_Retcode := resolve_pullseq_with_pull( X_STATUS,
135 X_PULL_SEQ_ID,
136 X_PRINT_KANBAN_CARD,
137 X_REPORT_ID,
138 L_CREATE_CARD_FLAG ,
139 L_PLAN_CARD_FLAG );
140 elsif ( (X_item_lo IS NULL) AND
141 (X_item_hi IS NULL) AND
142 (X_subinv IS NULL) AND
143 (X_locator_lo IS NULL) AND
144 (X_locator_hi IS NULL) AND
145 (X_source_type IS NULL) AND
146 (X_supplier_id IS NULL) AND
147 (X_supplier_site_id IS NULL) AND
148 (X_sourcing_org_id IS NULL) AND
149 (X_sourcing_subinv IS NULL) AND
150 (X_sourcing_loc_id IS NULL) AND
151 (X_wip_line_id IS NULL) ) then
152 PUT_LINE('Calling resolve_pullseq_all_null ');
153 v_Retcode := resolve_pullseq_all_null( X_ORG_ID,
154 X_ITEM_LO,
155 X_ITEM_HI,
156 X_SUBINV,
157 X_LOCATOR_LO,
158 X_LOCATOR_HI,
159 X_SOURCE_TYPE,
160 X_SUPPLIER_ID,
161 X_SUPPLIER_SITE_ID,
162 X_SOURCING_ORG_ID,
163 X_SOURCING_SUBINV,
164 X_SOURCING_LOC_ID,
165 X_WIP_LINE_ID,
166 X_STATUS,
167 X_PRINT_KANBAN_CARD,
168 L_CREATE_CARD_FLAG ,
169 L_PLAN_CARD_FLAG );
170 elsif ( (X_locator_lo IS NOT NULL) OR
171 (X_locator_hi IS NOT NULL) ) then
172 PUT_LINE('Calling resolve_pullseq_with_loc ');
173 v_Retcode := resolve_pullseq_with_loc( X_ORG_ID,
174 X_ITEM_LO,
175 X_ITEM_HI,
176 X_SUBINV,
177 X_LOCATOR_LO,
178 X_LOCATOR_HI,
179 X_SOURCE_TYPE,
180 X_SUPPLIER_ID,
181 X_SUPPLIER_SITE_ID,
182 X_SOURCING_ORG_ID,
183 X_SOURCING_SUBINV,
184 X_SOURCING_LOC_ID,
185 X_WIP_LINE_ID,
186 X_STATUS,
187 X_PRINT_KANBAN_CARD,
188 L_CREATE_CARD_FLAG ,
189 L_PLAN_CARD_FLAG );
190 else
191 PUT_LINE('Calling resolve_pullseq_no_loc ');
192 v_Retcode := resolve_pullseq_no_loc( X_ORG_ID,
193 X_ITEM_LO,
194 X_ITEM_HI,
195 X_SUBINV,
196 X_LOCATOR_LO,
197 X_LOCATOR_HI,
198 X_SOURCE_TYPE,
199 X_SUPPLIER_ID,
200 X_SUPPLIER_SITE_ID,
201 X_SOURCING_ORG_ID,
202 X_SOURCING_SUBINV,
203 X_SOURCING_LOC_ID,
204 X_WIP_LINE_ID,
208 L_PLAN_CARD_FLAG );
205 X_STATUS,
206 X_PRINT_KANBAN_CARD,
207 L_CREATE_CARD_FLAG ,
209 end if; /** if ( X_pull_seq_id IS NOT NULL ) then **/
210
211
212 if v_Retcode = 1 then
213 RETCODE := v_Retcode;
214 CONC_STATUS :=
215 FND_CONCURRENT.SET_COMPLETION_STATUS('NORMAL',Current_Error_Code);
216 elsif v_Retcode = 3 then
217 RETCODE := v_Retcode;
218 CONC_STATUS :=
219 FND_CONCURRENT.SET_COMPLETION_STATUS('WARNING',Current_Error_Code);
220 else
221 RETCODE := v_Retcode;
222 CONC_STATUS :=
223 FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',Current_Error_Code);
224 end if;
225
226 Exception
227 when others then
228 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
229 THEN
230 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME , 'Create_Kanban_Cards');
231 print_error;
232 END IF;
233 current_error_code := to_char(SQLCODE);
234 RETCODE := 2;
235 CONC_STATUS :=
236 FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',Current_Error_Code);
237 END Create_Kanban_Cards;
238
239
240 FUNCTION resolve_pullseq_all_null(
241 X_ORG_ID IN NUMBER,
242 X_ITEM_LO IN VARCHAR2,
243 X_ITEM_HI IN VARCHAR2,
244 X_SUBINV IN VARCHAR2,
245 X_LOCATOR_LO IN VARCHAR2,
246 X_LOCATOR_HI IN VARCHAR2,
247 X_SOURCE_TYPE IN NUMBER,
248 X_SUPPLIER_ID IN NUMBER,
249 X_SUPPLIER_SITE_ID IN NUMBER,
250 X_SOURCING_ORG_ID IN NUMBER,
251 X_SOURCING_SUBINV IN VARCHAR2,
252 X_SOURCING_LOC_ID IN NUMBER,
253 X_WIP_LINE_ID IN NUMBER,
254 X_STATUS IN NUMBER,
255 X_PRINT_KANBAN_CARD IN NUMBER ,
256 --ekanban changes by javakat
257 X_CREATE_CARD_FLAG IN NUMBER DEFAULT 1,
258 X_PLAN_CARD_FLAG IN NUMBER DEFAULT 2
259 ) return Number IS
260
261 cursor MKPSC is
262 select
263 pull_sequence_id , organization_id , inventory_item_id ,
264 subinventory_name , locator_id , source_type , supplier_id,
265 supplier_site_id, source_organization_id, source_subinventory,
266 source_locator_id, wip_line_id, kanban_size, number_of_cards,
267 release_kanban_flag
268 from
269 MTL_KANBAN_PULL_SEQUENCES
270 where
271 kanban_plan_id = -1 AND
272 source_type in (1,2,3,4) AND
273 --release_kanban_flag = 1 AND
274 organization_id = X_org_id
275 for update of organization_id NOWAIT;
276
277 KBCC MKPSC%ROWTYPE;
278 Rec BOOLEAN := FALSE;
279 v_success Number := 1;
280 v_report_id Number := NULL;
281 v_org_code VARCHAR2(3);
282
283 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
284 Begin
285 For KBCC in MKPSC LOOP
286 REC := TRUE;
287 card_check_and_create( KBCC.PULL_SEQUENCE_ID,
288 KBCC.ORGANIZATION_ID,
289 KBCC.INVENTORY_ITEM_ID,
290 KBCC.SUBINVENTORY_NAME,
291 KBCC.LOCATOR_ID,
292 KBCC.SOURCE_TYPE,
293 KBCC.KANBAN_SIZE,
294 KBCC.NUMBER_OF_CARDS,
295 KBCC.SUPPLIER_ID,
296 KBCC.SUPPLIER_SITE_ID,
297 KBCC.SOURCE_ORGANIZATION_ID,
298 KBCC.SOURCE_SUBINVENTORY,
299 KBCC.SOURCE_LOCATOR_ID,
300 KBCC.WIP_LINE_ID,
301 X_STATUS,
302 X_PRINT_KANBAN_CARD,
303 kbcc.release_kanban_flag,
304 V_REPORT_ID ,
305 X_CREATE_CARD_FLAG,
306 X_PLAN_CARD_FLAG);
307 END LOOP;
308 current_error_code := to_char(SQLCODE);
309
310 if NOT REC then
311 FND_MESSAGE.set_name('INV', 'INV_NO_PULLSEQ_SELECTED');
312 PUT_LINE( fnd_message.get );
313 current_error_code := to_char(SQLCODE);
314 end if; /* if NOT REC then */
315
316 -- call to report conc pgm report
317 if (X_PRINT_KANBAN_CARD = INVKBCGN.flm_ekb_yes AND V_REPORT_ID IS NOT NULL ) then
318 print_kanban_report( v_report_id );
319 end if;
320 Commit;
321 return v_success;
322
323 Exception
324 when others then
325 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
326 THEN
327 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME , 'resolve_pullseq_all_null');
328 print_error;
329 END IF;
330 v_success := 2;
331 current_error_code := to_char(SQLCODE);
332 return v_success;
333 END resolve_pullseq_all_null;
334
335
336
337 FUNCTION resolve_pullseq_with_pull(
338 X_STATUS IN NUMBER,
339 X_PULL_SEQ_ID IN NUMBER,
340 X_PRINT_KANBAN_CARD IN NUMBER,
344 X_PLAN_CARD_FLAG IN NUMBER DEFAULT 2
341 X_REPORT_ID IN NUMBER ,
342 --ekanban changes by javakat
343 X_CREATE_CARD_FLAG IN NUMBER DEFAULT 1,
345 ) return Number IS
346
347 cursor MKPSC is
348 select
349 pull_sequence_id , organization_id , inventory_item_id ,
350 subinventory_name , locator_id , source_type , supplier_id,
351 supplier_site_id, source_organization_id, source_subinventory,
352 source_locator_id, wip_line_id, kanban_size, number_of_cards,
353 release_kanban_flag
354 from
355 MTL_KANBAN_PULL_SEQUENCES
356 where
357 pull_sequence_id = X_pull_seq_id AND
358 source_type in (1,2,3,4) AND
359 --release_kanban_flag = 1 AND
360 x_report_id IS NULL
361 OR (source_type in (1,2,3,4) AND
362 --release_kanban_flag = 1 AND
363 x_report_id IS NOT NULL and
364 pull_sequence_id in (select pull_sequence_id
365 from mtl_kanban_card_print_temp
366 where x_report_id = report_id))
367 for update of organization_id NOWAIT;
368
369 KBCC MKPSC%ROWTYPE;
370 Rec BOOLEAN := FALSE;
371 v_success Number := 1;
372 v_report_id Number := NULL;
373
374 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
375 Begin
376 v_report_id := x_report_id;
377 For KBCC in MKPSC
378 LOOP
379 REC := TRUE;
380
381 card_check_and_create( KBCC.PULL_SEQUENCE_ID, KBCC.ORGANIZATION_ID,
382 KBCC.INVENTORY_ITEM_ID, KBCC.SUBINVENTORY_NAME,
383 KBCC.LOCATOR_ID, KBCC.SOURCE_TYPE,
384 KBCC.KANBAN_SIZE, KBCC.NUMBER_OF_CARDS,
385 KBCC.SUPPLIER_ID, KBCC.SUPPLIER_SITE_ID,
386 KBCC.SOURCE_ORGANIZATION_ID,
387 KBCC.SOURCE_SUBINVENTORY,
388 KBCC.SOURCE_LOCATOR_ID, KBCC.WIP_LINE_ID,
389 X_STATUS, X_PRINT_KANBAN_CARD,
390 kbcc.release_kanban_flag,
391 V_REPORT_ID ,
392 X_CREATE_CARD_FLAG,
393 X_PLAN_CARD_FLAG);
394
395 END LOOP;
396 current_error_code := to_char(SQLCODE);
397 if NOT REC then
398 FND_MESSAGE.set_name('INV', 'INV_NO_PULLSEQ_SELECTED');
399 PUT_LINE( fnd_message.get );
400 current_error_code := to_char(SQLCODE);
401 end if;
402
403 -- call to report conc pgm report
404 if (X_PRINT_KANBAN_CARD = INVKBCGN.flm_ekb_yes AND V_REPORT_ID IS NOT NULL ) then
405 print_kanban_report( v_report_id );
406 end if;
407 Commit;
408 return v_success;
409
410 Exception
411 when others then
412 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
413 THEN
414 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME , 'resolve_pullseq_with_pull');
415 print_error;
416 END IF;
417 v_success := 2;
418 current_error_code := to_char(SQLCODE);
419 return v_success;
420 END resolve_pullseq_with_pull;
421
422
423 FUNCTION resolve_pullseq_with_loc(
424 X_ORG_ID IN NUMBER,
425 X_ITEM_LO IN VARCHAR2,
426 X_ITEM_HI IN VARCHAR2,
427 X_SUBINV IN VARCHAR2,
428 X_LOCATOR_LO IN VARCHAR2,
429 X_LOCATOR_HI IN VARCHAR2,
430 X_SOURCE_TYPE IN NUMBER,
431 X_SUPPLIER_ID IN NUMBER,
432 X_SUPPLIER_SITE_ID IN NUMBER,
433 X_SOURCING_ORG_ID IN NUMBER,
434 X_SOURCING_SUBINV IN VARCHAR2,
435 X_SOURCING_LOC_ID IN NUMBER,
436 X_WIP_LINE_ID IN NUMBER,
437 X_STATUS IN NUMBER,
438 X_PRINT_KANBAN_CARD IN NUMBER ,
439 --ekanban changes by javakat
440 X_CREATE_CARD_FLAG IN NUMBER DEFAULT 1,
441 X_PLAN_CARD_FLAG IN NUMBER DEFAULT 2
442 ) return Number IS
443
444 Rec BOOLEAN := FALSE;
445 d_sql_p integer := NULL;
446 d_sql_rows_processed integer := NULL;
447 d_sql_stmt varchar2(6000) := NULL;
448 d_sql_stmt1 varchar2(4000) := NULL;
449 d_sql_stmt2 varchar2(6000) := NULL;
450 p_where_itm varchar2(2000) := NULL;
451 p_where_loc varchar2(2000) := NULL;
452 d_pull_seq_id NUMBER;
453 d_org_id NUMBER;
454 d_inv_itm_id NUMBER;
455 d_subinv varchar2(10);
456 d_loc_id NUMBER;
457 d_src_type NUMBER;
458 d_supp_id NUMBER;
459 d_supp_site_id NUMBER;
460 d_src_org_id NUMBER;
461 d_src_subinv varchar2(10);
462 d_src_loc_id NUMBER;
463 d_wip_line_id NUMBER;
464 d_kanban_size NUMBER;
465 d_no_cards NUMBER;
466 d_release_kanban_flag NUMBER;
470 v_item_name mtl_system_items_kfv.concatenated_segments%TYPE;
467 v_success Number := 1;
468 v_report_id Number := NULL;
469 v_org_code VARCHAR2(3);
471 v_loc_name mtl_item_locations_kfv.concatenated_segments%TYPE;
472
473 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
474 Begin
475
476 if ( (X_ITEM_LO IS NULL) AND (X_ITEM_HI IS NULL) ) then
477
478 Begin
479 query_range_loc( X_org_id, X_locator_lo, X_locator_hi, p_where_loc);
480 if ( p_where_loc IS NOT NULL ) then
481 d_sql_p := dbms_sql.open_cursor;
482 --Changes for Ekanban Bug 11829018
483 IF NVL(FND_PROFILE.VALUE('FLM_EKB_OPS_FLAG'),0)=INVKBCGN.flm_ekb_yes THEN
484 d_sql_stmt :=
485 'select mkps.pull_sequence_id ,mkps.organization_id ,mkps.inventory_item_id ,' ||
486 ' mkps.subinventory_name , mkps.locator_id , mkps.source_type , mkps.supplier_id,' ||
487 ' mkps.supplier_site_id, mkps.source_organization_id, mkps.source_subinventory,' ||
488 ' mkps.source_locator_id, mkps.wip_line_id, mkps.kanban_size, mkps.number_of_cards, ' ||
489 ' mkps.release_kanban_flag from mtl_kanban_pull_sequences mkps '||
490 ' WHERE '||
491 ' mkps.organization_id = :org_id AND mkps.source_type in (1,2,3,4) AND ' ||
492 ' nvl(mkps.subinventory_name,''9999'') = ' ||
493 ' nvl(:subinv, nvl(mkps.subinventory_name,''9999'')) AND ' ||
494 ' nvl(mkps.source_type,-9999) = nvl(:source_type,nvl(mkps.source_type,-9999)) AND '||
495 ' ( :supplier_id is null or (:supplier_id is not null and ' ||
496 ' exists ( select 1 from mtl_pull_seq_suppliers mpss ' ||
497 ' where mpss.organization_id = mkps.organization_id ' ||
498 ' and mpss.pull_sequence_id = mkps.pull_sequence_id ' ||
499 ' and mpss.supplier_id = :supplier_id ' ||
500 ' and rownum = 1 ' ||
501 ' ) ' ||
502 ' ) ' ||
503 ' ) AND ' ||
504 ' ( :supplier_site_id is null or (:supplier_site_id is not null and ' ||
505 ' exists( select 1 from mtl_pull_seq_suppliers mpss ' ||
506 ' where mpss.organization_id = mkps.organization_id ' ||
507 ' and mpss.pull_sequence_id = mkps.pull_sequence_id ' ||
508 ' and mpss.supplier_id = :supplier_id ' ||
509 ' and mpss.supplier_site_id = :supplier_site_id ' ||
510 ' and rownum = 1 ' ||
511 ' ) ' ||
512 ' ) ' ||
513 ' ) AND ' ||
514 ' nvl(mkps.source_organization_id,-9999) = ' ||
515 ' nvl(:sourcing_org_id, nvl(mkps.source_organization_id,-9999)) AND ' ||
516 ' nvl(mkps.source_subinventory, ''9999'') = ' ||
517 ' nvl(:sourcing_subinv, nvl(mkps.source_subinventory,''9999'')) AND ' ||
518 ' nvl(mkps.source_locator_id, -9999) = '||
519 ' nvl(:sourcing_loc_id,nvl(mkps.source_locator_id,-9999)) AND ' ||
520 ' nvl(mkps.wip_line_id, -9999) = '||
521 ' nvl(:line_id,nvl(mkps.wip_line_id,-9999)) AND ' ||
522 ' mkps.kanban_plan_id = -1 AND ' ||
523 --' mkps.release_kanban_flag = 1 AND ' ||
524 ' mkps.locator_id in ( select inventory_location_id from mtl_item_locations ' ||
525 ' where ' || p_where_loc ||
526 ' and organization_id = :org_id) '||
527 ' for update of mkps.organization_id NOWAIT ' ;
528 ELSE -- old kanban code
529 --removed , after the release_kanban_flag Bug 11829018
530 d_sql_stmt :=
531 'select pull_sequence_id ,organization_id ,inventory_item_id ,' ||
532 ' subinventory_name , locator_id , source_type , supplier_id,' ||
533 ' supplier_site_id, source_organization_id, source_subinventory,' ||
534 ' source_locator_id, wip_line_id, kanban_size, number_of_cards, ' ||
535 ' release_kanban_flag from MTL_KANBAN_PULL_SEQUENCES where '||
536 ' organization_id = :org_id AND source_type in (1,2,3,4) AND ' ||
537 ' nvl(subinventory_name,''9999'') = ' ||
538 ' nvl(:subinv, nvl(subinventory_name,''9999'')) AND ' ||
539 ' nvl(source_type,-9999) = nvl(:source_type,nvl(source_type,-9999)) AND '||
540 ' nvl(supplier_id,-9999) = nvl(:supplier_id,nvl(supplier_id,-9999)) AND '||
541 ' nvl(supplier_site_id,-9999) = ' ||
542 ' nvl(:supplier_site_id,nvl(supplier_site_id,-9999)) AND ' ||
543 ' nvl(source_organization_id,-9999) = ' ||
544 ' nvl(:sourcing_org_id, nvl(source_organization_id,-9999)) AND ' ||
545 ' nvl(source_subinventory, ''9999'') = ' ||
546 ' nvl(:sourcing_subinv, nvl(source_subinventory,''9999'')) AND ' ||
547 ' nvl(source_locator_id, -9999) = '||
548 ' nvl(:sourcing_loc_id,nvl(source_locator_id,-9999)) AND ' ||
549 ' nvl(wip_line_id, -9999) = '||
550 ' nvl(:line_id,nvl(wip_line_id,-9999)) AND ' ||
551 ' kanban_plan_id = -1 AND ' ||
552 --' release_kanban_flag = 1 AND ' ||
553 ' locator_id in ( select inventory_location_id from mtl_item_locations ' ||
554 ' where ' || p_where_loc ||
555 ' and organization_id = :org_id) '||
556 ' for update of organization_id NOWAIT ';
557 END IF;
558
559 PUT_LINE(' p_where ='||p_where_loc );
560 PUT_LINE(' d_sql_stmt ='||substr(d_sql_stmt,1,75) );
564 dbms_sql.parse( d_sql_p, d_sql_stmt , dbms_sql.native );
561 PUT_LINE(' d_sql_stmt ='||d_sql_stmt );
562 PUT_LINE('Before parsing');
563
565
566 PUT_LINE('Parsed The statement in loc');
567
568 dbms_sql.define_column(d_sql_p,1,d_pull_seq_id);
569 dbms_sql.define_column(d_sql_p,2,d_org_id );
570 dbms_sql.define_column(d_sql_p,3,d_inv_itm_id);
571 dbms_sql.define_column(d_sql_p,4,d_subinv,10 );
572 dbms_sql.define_column(d_sql_p,5,d_loc_id);
573 dbms_sql.define_column(d_sql_p,6,d_src_type);
574 dbms_sql.define_column(d_sql_p,7,d_supp_id );
575 dbms_sql.define_column(d_sql_p,8,d_supp_site_id );
576 dbms_sql.define_column(d_sql_p,9,d_src_org_id );
577 dbms_sql.define_column(d_sql_p,10,d_src_subinv,10 );
578 dbms_sql.define_column(d_sql_p,11,d_src_loc_id );
579 dbms_sql.define_column(d_sql_p,12,d_wip_line_id );
580 dbms_sql.define_column(d_sql_p,13,d_kanban_size);
581 dbms_sql.define_column(d_sql_p,14,d_no_cards);
582 dbms_sql.define_column(d_sql_p,15,d_release_kanban_flag);
583
584 PUT_LINE('Defined the cols in locs');
585 dbms_sql.bind_variable(d_sql_p,'org_id', X_ORG_ID);
586 dbms_sql.bind_variable(d_sql_p,'subinv', X_subinv);
587 dbms_sql.bind_variable(d_sql_p,'source_type', X_source_type);
588 dbms_sql.bind_variable(d_sql_p,'supplier_id', X_supplier_id);
589 dbms_sql.bind_variable(d_sql_p,'supplier_site_id', X_supplier_site_id);
590 dbms_sql.bind_variable(d_sql_p,'sourcing_org_id', X_sourcing_org_id);
591 dbms_sql.bind_variable(d_sql_p,'sourcing_subinv', X_sourcing_subinv);
592 dbms_sql.bind_variable(d_sql_p,'sourcing_loc_id', X_sourcing_loc_id);
593 dbms_sql.bind_variable(d_sql_p,'line_id', X_wip_line_id);
594
595 PUT_LINE('Bind the vars');
596
597 d_sql_rows_processed := dbms_sql.execute(d_sql_p);
598
599 PUT_LINE('No Rows ='||to_char(d_sql_rows_processed));
600
601 Loop
602 if ( dbms_sql.fetch_rows(d_sql_p) > 0 ) then
603 Rec := TRUE;
604 dbms_sql.column_value(d_sql_p,1, d_pull_seq_id);
605 dbms_sql.column_value(d_sql_p,2, d_org_id);
606 dbms_sql.column_value(d_sql_p,3, d_inv_itm_id);
607 dbms_sql.column_value(d_sql_p,4, d_subinv);
608 dbms_sql.column_value(d_sql_p,5, d_loc_id);
609 dbms_sql.column_value(d_sql_p,6, d_src_type);
610 dbms_sql.column_value(d_sql_p,7, d_supp_id);
611 dbms_sql.column_value(d_sql_p,8, d_supp_site_id);
612 dbms_sql.column_value(d_sql_p,9, d_src_org_id);
613 dbms_sql.column_value(d_sql_p,10, d_src_subinv);
614 dbms_sql.column_value(d_sql_p,11, d_src_loc_id);
615 dbms_sql.column_value(d_sql_p,12, d_wip_line_id);
616 dbms_sql.column_value(d_sql_p,13, d_kanban_size);
617 dbms_sql.column_value(d_sql_p,14, d_no_cards);
618 dbms_sql.column_value(d_sql_p,15,d_release_kanban_flag);
619
620 card_check_and_create( d_pull_seq_id,
621 d_org_id, d_inv_itm_id, d_subinv,
622 d_loc_id, d_src_type,
623 d_kanban_size, d_no_cards,
624 d_supp_id, d_supp_site_id,
625 d_src_org_id, d_src_subinv,
626 d_src_loc_id, d_wip_line_id, X_STATUS,
627 X_PRINT_KANBAN_CARD,
628 d_release_kanban_flag,
629 V_REPORT_ID ,
630 X_CREATE_CARD_FLAG,
631 X_PLAN_CARD_FLAG);
632 else
633 -- No more rows in cursor
634 dbms_sql.close_cursor(d_sql_p);
635 Exit;
636 end if;
637 End loop;
638 current_error_code := to_char(SQLCODE);
639
640 if NOT Rec then
641 FND_MESSAGE.set_name('INV', 'INV_NO_PULLSEQ_SELECTED');
642 PUT_LINE( fnd_message.get );
643 current_error_code := to_char(SQLCODE);
644 end if;
645 if dbms_sql.is_open(d_sql_p) then
646 dbms_sql.close_cursor(d_sql_p);
647 end if;
648 end if;
649 Exception
650 when others then
651 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
652 THEN
653 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME , 'resolve_pullseq_with_loc');
654 print_error;
655 END IF;
656 if dbms_sql.is_open(d_sql_p) then
657 dbms_sql.close_cursor(d_sql_p);
658 end if;
659 v_success := 2;
660 current_error_code := to_char(SQLCODE);
661 end;
662
663 Else
664
665 Begin
666
667 query_range_itm( X_item_lo, X_item_hi, p_where_itm);
668 query_range_loc( X_org_id, X_locator_lo, X_locator_hi, p_where_loc);
669
670 if (( p_where_itm IS NOT NULL ) AND ( p_where_loc IS NOT NULL)) then
671 d_sql_p := dbms_sql.open_cursor;
672 --Changes for Ekanban Bug 11829018
673 IF NVL(FND_PROFILE.VALUE('FLM_EKB_OPS_FLAG'),0)=INVKBCGN.flm_ekb_yes THEN
674 d_sql_stmt := 'select mkps.pull_sequence_id ,mkps.organization_id ,mkps.inventory_item_id ,' ||
675 ' mkps.subinventory_name , mkps.locator_id , mkps.source_type , mkps.supplier_id,' ||
676 ' mkps.supplier_site_id, mkps.source_organization_id, mkps.source_subinventory,' ||
677 ' mkps.source_locator_id, mkps.wip_line_id, mkps.kanban_size, mkps.number_of_cards, ' ||
678 ' mkps.release_kanban_flag from mtl_kanban_pull_sequences mkps '||
679 ' WHERE '||
680 ' mkps.organization_id = :org_id AND mkps.source_type in (1,2,3,4) AND ' ||
681 ' nvl(mkps.subinventory_name,''9999'') = ' ||
685 ' exists ( select 1 from mtl_pull_seq_suppliers mpss ' ||
682 ' nvl(:subinv, nvl(mkps.subinventory_name,''9999'')) AND ' ||
683 ' nvl(mkps.source_type,-9999) = nvl(:source_type,nvl(mkps.source_type,-9999)) AND '||
684 ' ( :supplier_id is null or (:supplier_id is not null and ' ||
686 ' where mpss.organization_id = mkps.organization_id ' ||
687 ' and mpss.pull_sequence_id = mkps.pull_sequence_id ' ||
688 ' and mpss.supplier_id = :supplier_id ' ||
689 ' and rownum = 1 ' ||
690 ' ) ' ||
691 ' ) ' ||
692 ' ) AND ' ||
693 ' ( :supplier_site_id is null or (:supplier_site_id is not null and ' ||
694 ' exists( select 1 from mtl_pull_seq_suppliers mpss ' ||
695 ' where mpss.organization_id = mkps.organization_id ' ||
696 ' and mpss.pull_sequence_id = mkps.pull_sequence_id ' ||
697 ' and mpss.supplier_id = :supplier_id ' ||
698 ' and mpss.supplier_site_id = :supplier_site_id ' ||
699 ' and rownum = 1 ' ||
700 ' ) ' ||
701 ' ) ' ||
702 ' ) AND ' ||
703 ' nvl(mkps.source_organization_id,-9999) = ' ||
704 ' nvl(:sourcing_org_id, nvl(mkps.source_organization_id,-9999)) AND ' ||
705 ' nvl(mkps.source_subinventory, ''9999'') = ' ||
706 ' nvl(:sourcing_subinv, nvl(mkps.source_subinventory,''9999'')) AND ' ||
707 ' nvl(mkps.source_locator_id, -9999) = '||
708 ' nvl(:sourcing_loc_id,nvl(mkps.source_locator_id,-9999)) AND ' ||
709 ' nvl(mkps.wip_line_id, -9999) = '||
710 ' nvl(:line_id,nvl(mkps.wip_line_id,-9999)) AND ' ||
711 'mkps.locator_id in ( select inventory_location_id from mtl_item_locations ' ||
712 'where ' || p_where_loc ||
713 ' and organization_id = :org_id) AND ' ||
714 'kanban_plan_id = -1';
715 -- and || 'release_kanban_flag = 1 '
716
717 d_sql_stmt1 := ' AND ' ||
718 'mkps.inventory_item_id in ( select inventory_item_id from mtl_system_items ' ||
719 'where ' || p_where_itm || ' and organization_id = :org_id) ' ||
720 ' for update of mkps.organization_id NOWAIT ';
721 ELSE --old code
722
723 d_sql_stmt :=
724 'select pull_sequence_id ,organization_id ,inventory_item_id ,' ||
725 ' subinventory_name , locator_id , source_type , supplier_id,' ||
726 ' supplier_site_id, source_organization_id, source_subinventory,' ||
727 ' source_locator_id, wip_line_id, kanban_size, number_of_cards, ' ||
728 ' release_kanban_flag from MTL_KANBAN_PULL_SEQUENCES where '||
729 ' organization_id = :org_id AND source_type in (1,2,3,4) AND ' ||
730 'nvl(subinventory_name,''9999'') = ' ||
731 'nvl(:subinv, nvl(subinventory_name,''9999'')) AND ' ||
732 'nvl(source_type,-9999) = nvl(:source_type, nvl(source_type,-9999)) AND '||
733 'nvl(supplier_id,-9999) = nvl(:supplier_id,nvl(supplier_id, -9999)) AND '||
734 'nvl(supplier_site_id,-9999) = ' ||
735 'nvl(:supplier_site_id,nvl(supplier_site_id,-9999)) AND ' ||
736 'nvl(source_organization_id,-9999) = ' ||
737 'nvl(:sourcing_org_id, nvl(source_organization_id,-9999)) AND ' ||
738 'nvl(source_subinventory, ''9999'') = ' ||
739 'nvl(:sourcing_subinv, nvl(source_subinventory,''9999'')) AND ' ||
740 'nvl(source_locator_id, -9999) = '||
741 'nvl(:sourcing_loc_id,nvl(source_locator_id,-9999)) AND ' ||
742 'nvl(wip_line_id, -9999) = '||
743 'nvl(:line_id,nvl(wip_line_id,-9999)) AND ' ||
744 'locator_id in ( select inventory_location_id from mtl_item_locations ' ||
745 'where ' || p_where_loc ||
746 ' and organization_id = :org_id) AND ' ||
747 'kanban_plan_id = -1';
748 -- and || 'release_kanban_flag = 1 '
749
750 d_sql_stmt1 := ' AND ' ||
751 'inventory_item_id in ( select inventory_item_id from mtl_system_items ' ||
752 'where ' || p_where_itm || ' and organization_id = :org_id) ' ||
753 ' for update of organization_id NOWAIT ';
754
755 END IF;
756
757
758 PUT_LINE(' len stmt ='||to_char(length(d_sql_stmt)) );
759 PUT_LINE(' len stmt1 ='||to_char(length(d_sql_stmt1)));
760
761 d_sql_stmt2 := d_sql_stmt || d_sql_stmt1;
762
763 PUT_LINE(' p_where_itm ='||p_where_itm );
764 PUT_LINE(' p_where_loc ='||p_where_loc );
765 PUT_LINE(' d_sql_stmt ='||d_sql_stmt );
766 PUT_LINE(' d_sql_stmt1 ='||d_sql_stmt1 );
767
768 dbms_sql.parse( d_sql_p, d_sql_stmt2, dbms_sql.native );
769
770 PUT_LINE('Parsed The statement in loc-itm');
771
772 dbms_sql.define_column(d_sql_p,1,d_pull_seq_id);
773 dbms_sql.define_column(d_sql_p,2,d_org_id );
774 dbms_sql.define_column(d_sql_p,3,d_inv_itm_id);
775 dbms_sql.define_column(d_sql_p,4,d_subinv,10 );
776 dbms_sql.define_column(d_sql_p,5,d_loc_id);
777 dbms_sql.define_column(d_sql_p,6,d_src_type);
778 dbms_sql.define_column(d_sql_p,7,d_supp_id );
779 dbms_sql.define_column(d_sql_p,8,d_supp_site_id );
780 dbms_sql.define_column(d_sql_p,9,d_src_org_id );
781 dbms_sql.define_column(d_sql_p,10,d_src_subinv,10 );
782 dbms_sql.define_column(d_sql_p,11,d_src_loc_id );
783 dbms_sql.define_column(d_sql_p,12,d_wip_line_id );
784 dbms_sql.define_column(d_sql_p,13,d_kanban_size);
788
785 dbms_sql.define_column(d_sql_p,14,d_no_cards);
786 dbms_sql.define_column(d_sql_p,15,d_release_kanban_flag);
787 PUT_LINE('Defined the cols in loc-itm');
789 dbms_sql.bind_variable(d_sql_p,'org_id', X_ORG_ID);
790 dbms_sql.bind_variable(d_sql_p,'subinv', X_subinv);
791 dbms_sql.bind_variable(d_sql_p,'source_type', X_source_type);
792 dbms_sql.bind_variable(d_sql_p,'supplier_id', X_supplier_id);
793 dbms_sql.bind_variable(d_sql_p,'supplier_site_id', X_supplier_site_id);
794 dbms_sql.bind_variable(d_sql_p,'sourcing_org_id', X_sourcing_org_id);
795 dbms_sql.bind_variable(d_sql_p,'sourcing_subinv', X_sourcing_subinv);
796 dbms_sql.bind_variable(d_sql_p,'sourcing_loc_id', X_sourcing_loc_id);
797 dbms_sql.bind_variable(d_sql_p,'line_id', X_wip_line_id);
798
799 PUT_LINE('Bind the vars ');
800
801 d_sql_rows_processed := dbms_sql.execute(d_sql_p);
802
803 PUT_LINE('No ofRows='||to_char(d_sql_rows_processed));
804
805 Loop
806 if ( dbms_sql.fetch_rows(d_sql_p) > 0 ) then
807 Rec := TRUE;
808 dbms_sql.column_value(d_sql_p,1, d_pull_seq_id);
809 dbms_sql.column_value(d_sql_p,2, d_org_id);
810 dbms_sql.column_value(d_sql_p,3, d_inv_itm_id);
811 dbms_sql.column_value(d_sql_p,4, d_subinv);
812 dbms_sql.column_value(d_sql_p,5, d_loc_id);
813 dbms_sql.column_value(d_sql_p,6, d_src_type);
814 dbms_sql.column_value(d_sql_p,7, d_supp_id);
815 dbms_sql.column_value(d_sql_p,8, d_supp_site_id);
816 dbms_sql.column_value(d_sql_p,9, d_src_org_id);
817 dbms_sql.column_value(d_sql_p,10, d_src_subinv);
818 dbms_sql.column_value(d_sql_p,11, d_src_loc_id);
819 dbms_sql.column_value(d_sql_p,12, d_wip_line_id);
820 dbms_sql.column_value(d_sql_p,13, d_kanban_size);
821 dbms_sql.column_value(d_sql_p,14, d_no_cards);
822 dbms_sql.column_value(d_sql_p,15,d_release_kanban_flag);
823
824 card_check_and_create( d_pull_seq_id,
825 d_org_id, d_inv_itm_id, d_subinv,
826 d_loc_id, d_src_type,
827 d_kanban_size, d_no_cards,
828 d_supp_id, d_supp_site_id,
829 d_src_org_id, d_src_subinv,
830 d_src_loc_id, d_wip_line_id, X_STATUS,
831 X_PRINT_KANBAN_CARD,
832 d_release_kanban_flag,
833 V_REPORT_ID ,
834 X_CREATE_CARD_FLAG,
835 X_PLAN_CARD_FLAG);
836 else
837 -- No more rows in cursor
838 dbms_sql.close_cursor(d_sql_p);
839 Exit;
840 end if;
841 End loop;
842 current_error_code := to_char(SQLCODE);
843
844 if NOT Rec then
845 FND_MESSAGE.set_name('INV', 'INV_NO_PULLSEQ_SELECTED');
846 PUT_LINE( fnd_message.get );
847 current_error_code := to_char(SQLCODE);
848 end if;
849 if dbms_sql.is_open(d_sql_p) then
850 dbms_sql.close_cursor(d_sql_p);
851 end if;
852 end if;
853 Exception
854 when others then
855 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
856 THEN
857 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME , 'resolve_pullseq_with_loc');
858 print_error;
859 END IF;
860 if dbms_sql.is_open(d_sql_p) then
861 dbms_sql.close_cursor(d_sql_p);
862 end if;
863 v_success := 2;
864 current_error_code := to_char(SQLCODE);
865 end;
866
867 end if;
868
869 -- call to report conc pgm report
870 if (X_PRINT_KANBAN_CARD = 1 AND V_REPORT_ID IS NOT NULL ) then
871 print_kanban_report( v_report_id );
872 end if;
873 Commit;
874 return v_success;
875
876 Exception
877 when others then
878 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME , 'resolve_pullseq_with_loc');
879 print_error;
880 v_success := 2;
881 current_error_code := to_char(SQLCODE);
882 return v_success;
883 END resolve_pullseq_with_loc;
884
885
886 FUNCTION resolve_pullseq_no_loc(
887 X_ORG_ID IN NUMBER,
888 X_ITEM_LO IN VARCHAR2,
889 X_ITEM_HI IN VARCHAR2,
890 X_SUBINV IN VARCHAR2,
891 X_LOCATOR_LO IN VARCHAR2,
892 X_LOCATOR_HI IN VARCHAR2,
893 X_SOURCE_TYPE IN NUMBER,
894 X_SUPPLIER_ID IN NUMBER,
895 X_SUPPLIER_SITE_ID IN NUMBER,
896 X_SOURCING_ORG_ID IN NUMBER,
897 X_SOURCING_SUBINV IN VARCHAR2,
898 X_SOURCING_LOC_ID IN NUMBER,
899 X_WIP_LINE_ID IN NUMBER,
900 X_STATUS IN NUMBER,
901 X_PRINT_KANBAN_CARD IN NUMBER ,
902 --ekanban changes by javakat
903 X_CREATE_CARD_FLAG IN NUMBER DEFAULT 1,
904 X_PLAN_CARD_FLAG IN NUMBER DEFAULT 2
905 ) return Number IS
906
907 cursor MKPSC is
908 select
909 pull_sequence_id , organization_id , inventory_item_id ,
910 subinventory_name , locator_id , source_type , supplier_id,
914 from
911 supplier_site_id, source_organization_id, source_subinventory,
912 source_locator_id, wip_line_id, kanban_size, number_of_cards ,
913 release_kanban_flag
915 MTL_KANBAN_PULL_SEQUENCES
916 where
917 organization_id = X_org_id AND
918 nvl(subinventory_name,'9999') =
919 nvl(X_subinv, nvl(subinventory_name,'9999')) AND
920 nvl(source_type,-9999) = nvl(X_source_type, nvl(source_type,-9999)) AND
921 nvl(supplier_id,-9999) = nvl(X_supplier_id,nvl(supplier_id, -9999)) AND
922 nvl(supplier_site_id,-9999) =
923 nvl(X_supplier_site_id,nvl(supplier_site_id,-9999)) AND
924 nvl(source_organization_id,-9999) =
925 nvl(X_sourcing_org_id, nvl(source_organization_id,-9999)) AND
926 nvl(source_subinventory, '9999') =
927 nvl(X_sourcing_subinv, nvl(source_subinventory,'9999')) AND
928 nvl(source_locator_id, -9999) =
929 nvl(X_sourcing_loc_id,nvl(source_locator_id,-9999)) AND
930 nvl(wip_line_id, -9999) =
931 nvl(X_wip_line_id,nvl(wip_line_id,-9999)) AND
932 kanban_plan_id = -1 AND
933 source_type in (1,2,3,4)
934 --AND release_kanban_flag = 1
935 for update of organization_id NOWAIT;
936
937 cursor MKPSC_ekanban is
938 select
939 mkps.pull_sequence_id , mkps.organization_id , mkps.inventory_item_id ,
940 mkps.subinventory_name , mkps.locator_id , mkps.source_type , mkps.supplier_id,
941 mkps.supplier_site_id, mkps.source_organization_id, mkps.source_subinventory,
942 mkps.source_locator_id, mkps.wip_line_id, mkps.kanban_size, mkps.number_of_cards ,
943 mkps.release_kanban_flag
944 from
945 MTL_KANBAN_PULL_SEQUENCES mkps
946 where mkps.organization_id = X_org_id AND
947 nvl(mkps.subinventory_name,'9999') = nvl(X_subinv, nvl(mkps.subinventory_name,'9999')) AND
948 nvl(mkps.source_type,-9999) = nvl(X_source_type, nvl(mkps.source_type,-9999)) AND
949 ( X_supplier_id is null or (X_supplier_id is not null and
950 exists ( select 1 from mtl_pull_seq_suppliers mpss
951 where mpss.organization_id = mkps.organization_id
952 and mpss.pull_sequence_id = mkps.pull_sequence_id
953 and mpss.supplier_id = X_supplier_id
954 and rownum = 1
955 )
956 )
957 ) AND
958 ( X_supplier_site_id is null or (X_supplier_site_id is not null and
959 exists( select 1 from mtl_pull_seq_suppliers mpss
960 where mpss.organization_id = mkps.organization_id
961 and mpss.pull_sequence_id = mkps.pull_sequence_id
962 and mpss.supplier_id = X_supplier_id
963 and mpss.supplier_site_id = X_supplier_site_id
964 and rownum = 1
965 )
966 )
967 ) AND
968 nvl(mkps.source_organization_id,-9999) = nvl(X_sourcing_org_id, nvl(mkps.source_organization_id,-9999)) AND
969 nvl(mkps.source_subinventory, '9999') = nvl(X_sourcing_subinv, nvl(mkps.source_subinventory,'9999')) AND
970 nvl(mkps.source_locator_id, -9999) = nvl(X_sourcing_loc_id,nvl(mkps.source_locator_id,-9999)) AND
971 nvl(mkps.wip_line_id, -9999) = nvl(X_wip_line_id,nvl(mkps.wip_line_id,-9999)) AND
972 mkps.kanban_plan_id = -1 AND
973 mkps.source_type in (1,2,3,4)
974 --AND release_kanban_flag = 1
975 for update of mkps.organization_id NOWAIT;
976
977 KBCC MKPSC%ROWTYPE;
978 Rec BOOLEAN := FALSE;
979 d_sql_p integer := NULL;
980 d_sql_rows_processed integer := NULL;
981 d_sql_stmt varchar2(4000) := NULL;
982 p_where varchar2(2000) := NULL;
983 d_pull_seq_id NUMBER;
984 d_org_id NUMBER;
985 d_inv_itm_id NUMBER;
986 d_subinv varchar2(10);
987 d_loc_id NUMBER;
988 d_src_type NUMBER;
989 d_supp_id NUMBER;
990 d_supp_site_id NUMBER;
991 d_src_org_id NUMBER;
992 d_src_subinv varchar2(10);
993 d_src_loc_id NUMBER;
994 d_wip_line_id NUMBER;
995 d_kanban_size NUMBER;
996 d_no_cards NUMBER;
997 d_release_kanban_flag NUMBER;
998 v_success Number := 1;
999 v_report_id Number := NULL;
1000 v_org_code VARCHAR2(3);
1001 v_item_name mtl_system_items_kfv.concatenated_segments%TYPE;
1002 v_loc_name mtl_item_locations_kfv.concatenated_segments%TYPE;
1003
1004 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1005 Begin
1006 if ( (X_ITEM_LO IS NULL) AND (X_ITEM_HI IS NULL) ) then
1007 Begin
1008 --Changes for Ekanban Bug 11829018
1009 PUT_LINE('FLM_EKB_OPS_FLAG '||nvl(fnd_profile.VALUE('FLM_EKB_OPS_FLAG'),0));
1010 IF NVL(FND_PROFILE.VALUE('FLM_EKB_OPS_FLAG'),0)=INVKBCGN.flm_ekb_yes THEN
1011
1012 For KBCC in MKPSC_ekanban LOOP
1013 REC := TRUE;
1014 card_check_and_create( KBCC.PULL_SEQUENCE_ID, KBCC.ORGANIZATION_ID,
1015 KBCC.INVENTORY_ITEM_ID, KBCC.SUBINVENTORY_NAME,
1019 KBCC.SOURCE_ORGANIZATION_ID,
1016 KBCC.LOCATOR_ID, KBCC.SOURCE_TYPE,
1017 KBCC.KANBAN_SIZE, KBCC.NUMBER_OF_CARDS,
1018 KBCC.SUPPLIER_ID, KBCC.SUPPLIER_SITE_ID,
1020 KBCC.SOURCE_SUBINVENTORY,
1021 KBCC.SOURCE_LOCATOR_ID,
1022 KBCC.WIP_LINE_ID, X_STATUS,
1023 X_PRINT_KANBAN_CARD,
1024 kbcc.release_kanban_flag,V_REPORT_ID,
1025 X_CREATE_CARD_FLAG,
1026 X_PLAN_CARD_FLAG);
1027 END LOOP;
1028 ELSE --old code
1029
1030 For KBCC in MKPSC LOOP
1031 REC := TRUE;
1032 card_check_and_create( KBCC.PULL_SEQUENCE_ID, KBCC.ORGANIZATION_ID,
1033 KBCC.INVENTORY_ITEM_ID, KBCC.SUBINVENTORY_NAME,
1034 KBCC.LOCATOR_ID, KBCC.SOURCE_TYPE,
1035 KBCC.KANBAN_SIZE, KBCC.NUMBER_OF_CARDS,
1036 KBCC.SUPPLIER_ID, KBCC.SUPPLIER_SITE_ID,
1037 KBCC.SOURCE_ORGANIZATION_ID,
1038 KBCC.SOURCE_SUBINVENTORY,
1039 KBCC.SOURCE_LOCATOR_ID,
1040 KBCC.WIP_LINE_ID, X_STATUS,
1041 X_PRINT_KANBAN_CARD,
1042 kbcc.release_kanban_flag,V_REPORT_ID,
1043 X_CREATE_CARD_FLAG,
1044 X_PLAN_CARD_FLAG);
1045 END LOOP;
1046 END IF;
1047 current_error_code := to_char(SQLCODE);
1048 if NOT REC then
1049 FND_MESSAGE.set_name('INV', 'INV_NO_PULLSEQ_SELECTED');
1050 PUT_LINE( fnd_message.get );
1051 end if;
1052 current_error_code := to_char(SQLCODE);
1053 Exception
1054 when others then
1055 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1056 THEN
1057 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME , 'resolve_pullseq_no_loc');
1058 print_error;
1059 END IF;
1060 v_success := 2;
1061 current_error_code := to_char(SQLCODE);
1062 end;
1063
1064 Else
1065
1066 Begin
1067
1068 query_range_itm( X_item_lo, X_item_hi, p_where);
1069 if ( p_where IS NOT NULL ) then
1070 d_sql_p := dbms_sql.open_cursor;
1071 --Changes for Ekanban Bug 11829018
1072 IF NVL(FND_PROFILE.VALUE('FLM_EKB_OPS_FLAG'),0)=INVKBCGN.flm_ekb_yes THEN
1073 d_sql_stmt := 'select mkps.pull_sequence_id ,mkps.organization_id ,mkps.inventory_item_id ,' ||
1074 ' mkps.subinventory_name , mkps.locator_id , mkps.source_type , mkps.supplier_id,' ||
1075 ' mkps.supplier_site_id, mkps.source_organization_id, mkps.source_subinventory,' ||
1076 ' mkps.source_locator_id, mkps.wip_line_id, mkps.kanban_size, mkps.number_of_cards, ' ||
1077 ' mkps.release_kanban_flag from mtl_kanban_pull_sequences mkps '||
1078 ' WHERE '||
1079 ' mkps.organization_id = :org_id AND mkps.source_type in (1,2,3,4) AND ' ||
1080 ' nvl(mkps.subinventory_name,''9999'') = ' ||
1081 ' nvl(:subinv, nvl(mkps.subinventory_name,''9999'')) AND ' ||
1082 ' nvl(mkps.source_type,-9999) = nvl(:source_type,nvl(mkps.source_type,-9999)) AND '||
1083 ' ( :supplier_id is null or (:supplier_id is not null and ' ||
1084 ' exists ( select 1 from mtl_pull_seq_suppliers mpss ' ||
1085 ' where mpss.organization_id = mkps.organization_id ' ||
1086 ' and mpss.pull_sequence_id = mkps.pull_sequence_id ' ||
1087 ' and mpss.supplier_id = :supplier_id ' ||
1088 ' and rownum = 1 ' ||
1089 ' ) ' ||
1090 ' ) ' ||
1091 ' ) AND ' ||
1092 ' ( :supplier_site_id is null or (:supplier_site_id is not null and ' ||
1093 ' exists( select 1 from mtl_pull_seq_suppliers mpss ' ||
1094 ' where mpss.organization_id = mkps.organization_id ' ||
1095 ' and mpss.pull_sequence_id = mkps.pull_sequence_id ' ||
1096 ' and mpss.supplier_id = :supplier_id ' ||
1097 ' and mpss.supplier_site_id = :supplier_site_id ' ||
1098 ' and rownum = 1 ' ||
1099 ' ) ' ||
1100 ' ) ' ||
1101 ' ) AND ' ||
1102 ' nvl(mkps.source_organization_id,-9999) = ' ||
1103 ' nvl(:sourcing_org_id, nvl(mkps.source_organization_id,-9999)) AND ' ||
1104 ' nvl(mkps.source_subinventory, ''9999'') = ' ||
1105 ' nvl(:sourcing_subinv, nvl(mkps.source_subinventory,''9999'')) AND ' ||
1106 ' nvl(mkps.source_locator_id, -9999) = '||
1107 ' nvl(:sourcing_loc_id,nvl(mkps.source_locator_id,-9999)) AND ' ||
1108 ' nvl(mkps.wip_line_id, -9999) = '||
1109 ' nvl(:line_id,nvl(mkps.wip_line_id,-9999)) AND ' ||
1110 ' mkps.kanban_plan_id = -1 AND ' ||
1111 --' release_kanban_flag = 1 AND ' ||
1112 ' mkps.inventory_item_id in ( select inventory_item_id from mtl_system_items '||
1113 ' where ' || p_where || ' and organization_id = :org_id) ' ||
1114 ' for update of mkps.organization_id NOWAIT ';
1115 ELSE --old code
1116 d_sql_stmt :=
1117 'select pull_sequence_id ,organization_id ,inventory_item_id ,' ||
1118 ' subinventory_name , locator_id , source_type , supplier_id,' ||
1122 ' organization_id = :org_id AND source_type in (1,2,3,4) AND ' ||
1119 ' supplier_site_id, source_organization_id, source_subinventory,' ||
1120 ' source_locator_id, wip_line_id, kanban_size, number_of_cards, ' ||
1121 ' release_kanban_flag from MTL_KANBAN_PULL_SEQUENCES where '||
1123 ' nvl(subinventory_name,''9999'') = ' ||
1124 ' nvl(:subinv, nvl(subinventory_name,''9999'')) AND ' ||
1125 ' nvl(source_type,-9999) = nvl(:source_type,nvl(source_type,-9999)) AND '||
1126 ' nvl(supplier_id,-9999) = nvl(:supplier_id,nvl(supplier_id,-9999)) AND '||
1127 ' nvl(supplier_site_id,-9999) = ' ||
1128 ' nvl(:supplier_site_id,nvl(supplier_site_id,-9999)) AND ' ||
1129 ' nvl(source_organization_id,-9999) = ' ||
1130 ' nvl(:sourcing_org_id, nvl(source_organization_id,-9999)) AND ' ||
1131 ' nvl(source_subinventory, ''9999'') = ' ||
1132 ' nvl(:sourcing_subinv, nvl(source_subinventory,''9999'')) AND ' ||
1133 ' nvl(source_locator_id, -9999) = '||
1134 ' nvl(:sourcing_loc_id,nvl(source_locator_id,-9999)) AND ' ||
1135 'nvl(wip_line_id, -9999) = '||
1136 'nvl(:line_id,nvl(wip_line_id,-9999)) AND ' ||
1137 ' kanban_plan_id = -1 AND ' ||
1138 --' release_kanban_flag = 1 AND ' ||
1139 ' inventory_item_id in ( select inventory_item_id from mtl_system_items '||
1140 ' where ' || p_where || ' and organization_id = :org_id) ' ||
1141 ' for update of organization_id NOWAIT ';
1142 END IF;
1143
1144 PUT_LINE(' p_where ='||p_where );
1145 PUT_LINE(' d_sql_stmt ='||d_sql_stmt );
1146
1147 dbms_sql.parse( d_sql_p, d_sql_stmt , dbms_sql.native );
1148
1149 PUT_LINE('Parsed The statement in no_loc');
1150
1151 dbms_sql.define_column(d_sql_p,1,d_pull_seq_id);
1152 dbms_sql.define_column(d_sql_p,2,d_org_id );
1153 dbms_sql.define_column(d_sql_p,3,d_inv_itm_id);
1154 dbms_sql.define_column(d_sql_p,4,d_subinv,10 );
1155 dbms_sql.define_column(d_sql_p,5,d_loc_id);
1156 dbms_sql.define_column(d_sql_p,6,d_src_type);
1157 dbms_sql.define_column(d_sql_p,7,d_supp_id );
1158 dbms_sql.define_column(d_sql_p,8,d_supp_site_id );
1159 dbms_sql.define_column(d_sql_p,9,d_src_org_id );
1160 dbms_sql.define_column(d_sql_p,10,d_src_subinv,10 );
1161 dbms_sql.define_column(d_sql_p,11,d_src_loc_id );
1162 dbms_sql.define_column(d_sql_p,12,d_wip_line_id );
1163 dbms_sql.define_column(d_sql_p,13,d_kanban_size);
1164 dbms_sql.define_column(d_sql_p,14,d_no_cards);
1165 dbms_sql.define_column(d_sql_p,15,d_release_kanban_flag);
1166
1167 PUT_LINE('Defined the cols in no_locs');
1168
1169 dbms_sql.bind_variable(d_sql_p,'org_id', X_ORG_ID);
1170 dbms_sql.bind_variable(d_sql_p,'subinv', X_subinv);
1171 dbms_sql.bind_variable(d_sql_p,'source_type', X_source_type);
1172 dbms_sql.bind_variable(d_sql_p,'supplier_id', X_supplier_id);
1173 dbms_sql.bind_variable(d_sql_p,'supplier_site_id', X_supplier_site_id);
1174 dbms_sql.bind_variable(d_sql_p,'sourcing_org_id', X_sourcing_org_id);
1175 dbms_sql.bind_variable(d_sql_p,'sourcing_subinv', X_sourcing_subinv);
1176 dbms_sql.bind_variable(d_sql_p,'sourcing_loc_id', X_sourcing_loc_id);
1177 dbms_sql.bind_variable(d_sql_p,'line_id', X_wip_line_id);
1178
1179 PUT_LINE('Bind the vars in no_locs');
1180
1181 d_sql_rows_processed := dbms_sql.execute(d_sql_p);
1182
1183 PUT_LINE('No ofRows ='||to_char(d_sql_rows_processed));
1184
1185 Loop
1186 if ( dbms_sql.fetch_rows(d_sql_p) > 0 ) then
1187 Rec := TRUE;
1188 dbms_sql.column_value(d_sql_p,1, d_pull_seq_id);
1189 dbms_sql.column_value(d_sql_p,2, d_org_id);
1190 dbms_sql.column_value(d_sql_p,3, d_inv_itm_id);
1191 dbms_sql.column_value(d_sql_p,4, d_subinv);
1192 dbms_sql.column_value(d_sql_p,5, d_loc_id);
1193 dbms_sql.column_value(d_sql_p,6, d_src_type);
1194 dbms_sql.column_value(d_sql_p,7, d_supp_id);
1195 dbms_sql.column_value(d_sql_p,8, d_supp_site_id);
1196 dbms_sql.column_value(d_sql_p,9, d_src_org_id);
1197 dbms_sql.column_value(d_sql_p,10, d_src_subinv);
1198 dbms_sql.column_value(d_sql_p,11, d_src_loc_id);
1199 dbms_sql.column_value(d_sql_p,12, d_wip_line_id);
1200 dbms_sql.column_value(d_sql_p,13, d_kanban_size);
1201 dbms_sql.column_value(d_sql_p,14, d_no_cards);
1202 dbms_sql.column_value(d_sql_p,15, d_release_kanban_flag);
1203
1204 card_check_and_create( d_pull_seq_id,
1205 d_org_id, d_inv_itm_id, d_subinv,
1206 d_loc_id, d_src_type,
1207 d_kanban_size, d_no_cards,
1208 d_supp_id, d_supp_site_id,
1209 d_src_org_id, d_src_subinv,
1210 d_src_loc_id, d_wip_line_id, X_STATUS,
1211 X_PRINT_KANBAN_CARD,
1212 d_release_kanban_flag,
1213 V_REPORT_ID,
1214 X_CREATE_CARD_FLAG,
1215 X_PLAN_CARD_FLAG);
1216 else
1217 -- No more rows in cursor
1218 dbms_sql.close_cursor(d_sql_p);
1219 Exit;
1220 end if;
1221 End loop;
1222 current_error_code := to_char(SQLCODE);
1223 if NOT Rec then
1224 FND_MESSAGE.set_name('INV', 'INV_NO_PULLSEQ_SELECTED');
1225 PUT_LINE( fnd_message.get );
1226 end if;
1227 current_error_code := to_char(SQLCODE);
1228 if dbms_sql.is_open(d_sql_p) then
1229 dbms_sql.close_cursor(d_sql_p);
1230 end if;
1231 end if;
1232 Exception
1233 when others then
1234 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1238 END IF;
1235 THEN
1236 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME , 'resolve_pullseq_no_loc');
1237 print_error;
1239 if dbms_sql.is_open(d_sql_p) then
1240 dbms_sql.close_cursor(d_sql_p);
1241 end if;
1242 v_success := 2;
1243 current_error_code := to_char(SQLCODE);
1244 return v_success;
1245 end;
1246 end if;
1247
1248 -- call to report conc pgm report
1249 if (X_PRINT_KANBAN_CARD = INVKBCGN.flm_ekb_yes AND V_REPORT_ID IS NOT NULL ) then
1250 print_kanban_report( v_report_id );
1251 end if;
1252 Commit;
1253 return v_success;
1254
1255 Exception
1256 when others then
1257 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME , 'resolve_pullseq_no_loc');
1258 print_error;
1259 v_success := 2;
1260 current_error_code := to_char(SQLCODE);
1261 return v_success;
1262 END resolve_pullseq_no_loc;
1263
1264
1265 PROCEDURE card_check_and_create(
1266 X_PULL_SEQUENCE_ID IN NUMBER,
1267 X_ORG_ID IN NUMBER,
1268 X_ITEM_ID IN NUMBER,
1269 X_SUBINV IN VARCHAR2,
1270 X_LOC_ID IN NUMBER,
1271 X_SOURCE_TYPE IN NUMBER,
1272 X_KANBAN_SIZE IN NUMBER,
1273 X_NO_OF_CARDS IN NUMBER,
1274 X_SUPPLIER_ID IN NUMBER,
1275 X_SUPPLIER_SITE_ID IN NUMBER,
1276 X_SOURCING_ORG_ID IN NUMBER,
1277 X_SOURCING_SUBINV IN VARCHAR2,
1278 X_SOURCING_LOC_ID IN NUMBER,
1279 X_WIP_LINE_ID IN NUMBER,
1280 X_STATUS IN NUMBER,
1281 X_PRINT_KANBAN_CARD IN NUMBER,
1282 p_release_kanban_flag IN NUMBER,
1283 X_REPORT_ID IN OUT NOCOPY NUMBER,
1284 --ekanban changes by javakat
1285 X_CREATE_CARD_FLAG IN NUMBER DEFAULT 1,
1286 X_PLAN_CARD_FLAG IN NUMBER DEFAULT 2
1287 ) IS
1288 l_kanban_card_ids INV_Kanban_PVT.kanban_card_id_tbl_type;
1289 l_pull_seq_rec INV_Kanban_PVT.pull_sequence_rec_type;
1290 /*l_kanban_card_rec declared by javakat 11829018
1291 to insert record into mtl_kanban_card_activity*/
1292 l_kanban_card_rec INV_Kanban_PVT.Kanban_Card_Rec_Type;
1293 l_return_status varchar2(1) := FND_API.G_RET_STS_SUCCESS;
1294 l_report_id number;
1295 l_org_code VARCHAR2(3) := Null;
1296 l_item_name MTL_SYSTEM_ITEMS_KFV.CONCATENATED_SEGMENTS%TYPE := Null;
1297 l_loc_name MTL_ITEM_LOCATIONS_KFV.CONCATENATED_SEGMENTS%TYPE := Null;
1298 --added for ekanban by javakat
1299 l_no_of_existing_cards NUMBER;
1300 l_no_of_activenhold_cards NUMBER;
1301 l_create_kanban BOOLEAN DEFAULT FALSE;
1302 l_number_of_cards NUMBER;
1303 l_kanban_size NUMBER;
1304 l_future_no_of_cards NUMBER;
1305 l_future_card_size NUMBER;
1306 l_planning_effectivity DATE;
1307 end_of_code EXCEPTION;
1308 X_ERR_MSG VARCHAR2(1000);
1309 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1310
1311 type t_kanban_card is table of number index by binary_integer;
1312 v_kanban_card t_kanban_card;
1313 l_temp Number;
1314
1315 Cursor get_cards_to_update(p_pull_sequence_id NUMBER)
1316 Is
1317 SELECT kanban_card_id
1318 FROM MTL_KANBAN_CARDS
1319 WHERE card_status =INV_Kanban_PVT.G_Card_Status_Planned
1320 AND pull_sequence_id = p_pull_sequence_id;
1321 BEGIN
1322 l_pull_seq_rec.pull_sequence_id := X_pull_sequence_id;
1323 l_pull_seq_rec.organization_id := X_org_id ;
1324 l_pull_seq_rec.inventory_item_id := X_item_id ;
1325 l_pull_seq_rec.subinventory_name := X_subinv;
1326 l_pull_seq_rec.locator_id := X_loc_id;
1327 l_pull_seq_rec.source_type := X_source_type ;
1328 l_pull_seq_rec.Kanban_size := X_kanban_size ;
1329 l_pull_seq_rec.number_of_cards := X_no_of_cards ;
1330 l_pull_seq_rec.supplier_id := X_supplier_id ;
1331 l_pull_seq_rec.supplier_site_id := X_supplier_site_id ;
1332 l_pull_seq_rec.source_organization_id := X_sourcing_org_id ;
1333 l_pull_seq_rec.source_subinventory := X_sourcing_subinv ;
1334 l_pull_seq_rec.source_locator_id := X_sourcing_loc_id ;
1335 l_pull_seq_rec.wip_line_id := X_wip_line_id ;
1336 l_pull_seq_rec.release_kanban_flag := p_release_kanban_flag;
1337 --ekanban code for X_CREATE_CARD_FLAG by javakat
1338
1339
1340 --Changes made for Generate Kanban Cards by javakat for eKanban
1341 put_line ('X_CREATE_CARD_FLAG -' || x_create_card_flag);
1342 put_line ('X_PLAN_CARD_FLAG -' || X_PLAN_CARD_FLAG);
1343 PUT_LINE('FLM_EKB_OPS_FLAG - '||NVL(FND_PROFILE.VALUE('FLM_EKB_OPS_FLAG'),0));
1344
1345 If NVL(FND_PROFILE.VALUE('FLM_EKB_OPS_FLAG'),0)=INVKBCGN.flm_ekb_yes THEN
1346 PUT_LINE('X_pull_sequence_id- '||X_pull_sequence_id);
1347 -- Step1: Logic is Find the planning_effectivity for pull_sequence_id from MTL_KANBAN_PULL_SEQUENCES
1348 SELECT number_of_cards
1349 , kanban_size
1350 , future_no_of_cards
1351 , future_card_size
1352 , planning_effectivity
1353 INTO l_number_of_cards
1354 , l_kanban_size
1355 , l_future_no_of_cards
1356 , l_future_card_size
1357 , l_planning_effectivity
1358 FROM mtl_kanban_pull_sequences mkps
1359 WHERE pull_sequence_id = X_pull_sequence_id;
1360
1361 PUT_LINE('l_number_of_cards - '||l_number_of_cards);
1362 PUT_LINE('kanban_size - '||l_kanban_size);
1363 PUT_LINE('l_future_no_of_cards - '||l_future_no_of_cards);
1364 PUT_LINE('l_future_card_size - '||l_future_card_size);
1365 PUT_LINE('l_planning_effectivity - '||l_planning_effectivity);
1366
1367 IF ( trunc(l_planning_effectivity) <= trunc(SYSDATE) OR l_planning_effectivity IS NULL ) THEN
1368 IF ( l_future_no_of_cards IS NOT NULL or l_future_card_size IS NOT NULL ) THEN
1369
1370 PUT_LINE('planning_effectivity is less than SYSDATE');
1371
1372 UPDATE mtl_kanban_pull_sequences SET
1373 number_of_cards=nvl(l_future_no_of_cards,number_of_cards)
1374 ,kanban_size=nvl(l_future_card_size,kanban_size)
1375 ,future_no_of_cards=null
1376 ,future_card_size=null
1377 ,planning_effectivity =null
1378 ,last_update_date = sysdate
1379 ,last_updated_by = fnd_global.user_id
1380 WHERE pull_sequence_id = X_pull_sequence_id;
1381
1382 if l_pull_seq_rec.source_type = INV_Kanban_PVT.G_Source_Type_Supplier then
1383 FLM_MULTIPLE_SUPPLIERS.multiple_supplier_kanban_cards( p_pull_seq_id =>X_pull_sequence_id,
1384 x_retcode =>l_return_status ,
1385 x_err_msg => X_ERR_MSG);
1386 end if;
1387
1388 l_number_of_cards:=nvl(l_future_no_of_cards,l_number_of_cards);
1389 l_pull_seq_rec.kanban_size:=nvl(l_future_card_size,l_kanban_size);
1390
1391 END IF;
1392 IF nvl(X_PLAN_CARD_FLAG,INVKBCGN.flm_ekb_no) =INVKBCGN.flm_ekb_yes THEN
1393 SELECT COUNT(*)
1394 INTO l_no_of_existing_cards
1395 FROM MTL_KANBAN_CARDS
1396 WHERE pull_sequence_id = X_Pull_sequence_id
1397 AND (card_status = INV_Kanban_PVT.G_Card_Status_Active or
1398 card_status = INV_Kanban_PVT.G_Card_Status_Hold)
1399 AND max_replenishments is null
1400 AND disable_date is null
1401 AND kanban_card_type = INV_Kanban_Pvt.g_card_type_replenishable;
1402
1403 v_kanban_card.delete;
1404 UPDATE MTL_KANBAN_CARDS SET
1405 card_status=INV_Kanban_PVT.G_Card_Status_Active,
1406 last_update_date = sysdate,
1407 last_updated_by = fnd_global.user_id
1408 WHERE card_status =INV_Kanban_PVT.G_Card_Status_Planned
1409 AND pull_sequence_id = X_pull_sequence_id
1410 AND (max_replenishments is not null or
1411 disable_date is not null or
1412 kanban_card_type <> INV_Kanban_Pvt.g_card_type_replenishable)
1413 returning kanban_card_id bulk collect into v_kanban_card;
1414
1415 l_temp := v_kanban_card.first;
1416 if l_temp is not null then
1417 while l_temp <= v_kanban_card.last loop
1418 l_kanban_card_rec := INV_KanbanCard_PKG.Query_Row( p_Kanban_Card_id => v_kanban_card(l_temp) );
1419 INV_KanbanCard_PKG.Insert_Activity_For_Card(l_Kanban_Card_Rec);
1420 l_temp := v_kanban_card.next(l_temp);
1421 end loop;
1422 end if;
1423
1424 v_kanban_card.delete;
1425 UPDATE MTL_KANBAN_CARDS SET
1426 card_status=INV_Kanban_PVT.G_Card_Status_Active,
1427 last_update_date = sysdate,
1428 last_updated_by = fnd_global.user_id
1429 WHERE card_status =INV_Kanban_PVT.G_Card_Status_Planned
1430 AND pull_sequence_id = X_pull_sequence_id
1431 AND max_replenishments is null
1432 AND disable_date is null
1433 AND kanban_card_type = INV_Kanban_Pvt.g_card_type_replenishable
1434 AND rownum <= (nvl(l_number_of_cards,0)- l_no_of_existing_cards )
1435 returning kanban_card_id bulk collect into v_kanban_card;
1436
1437 l_temp := v_kanban_card.first;
1438 if l_temp is not null then
1439 while l_temp <= v_kanban_card.last loop
1440 l_kanban_card_rec := INV_KanbanCard_PKG.Query_Row( p_Kanban_Card_id => v_kanban_card(l_temp) );
1441 INV_KanbanCard_PKG.Insert_Activity_For_Card(l_Kanban_Card_Rec);
1442 l_temp := v_kanban_card.next(l_temp);
1443 end loop;
1444 end if;
1445 END IF;
1446 END IF;
1447 IF nvl(X_CREATE_CARD_FLAG,INVKBCGN.flm_ekb_yes) =INVKBCGN.flm_ekb_yes THEN
1448 SELECT COUNT(*)
1449 INTO l_no_of_existing_cards
1450 FROM MTL_KANBAN_CARDS
1451 WHERE pull_sequence_id = X_Pull_sequence_id
1452 AND (card_status = INV_Kanban_PVT.G_Card_Status_Active or
1453 card_status = INV_Kanban_PVT.G_Card_Status_Hold)
1454 /*Added these 2 where clauses by javakat to fix 11829018*/
1455 AND max_replenishments is null
1456 AND disable_date is null
1457 AND kanban_card_type = INV_Kanban_Pvt.g_card_type_replenishable;
1458
1459 PUT_LINE('l_no_of_existing_cards - '||l_no_of_existing_cards);
1460 PUT_LINE('l_future_no_of_cards - '||l_future_no_of_cards);
1461 l_pull_seq_rec.number_of_cards := l_number_of_cards-l_no_of_existing_cards ;
1462 l_create_kanban:=TRUE;
1463 ELSE
1464 PUT_LINE('No Cards will be created X_CREATE_CARD_FLAG -'||X_CREATE_CARD_FLAG);
1465 RAISE end_of_code;
1466 END IF;
1467 ELSE --for old code
1468 if INV_kanban_PVT.Ok_To_Create_Kanban_Cards(X_Pull_sequence_id ) then
1469 l_create_kanban:=TRUE;
1470 ELSE
1471 l_create_kanban:=FALSE;
1472 PUT_LINE( fnd_message.get );
1473 END IF;
1474 END IF; --end of ekanban profile code by javakat
1475 /*changes made to the if clause to fix bug 11800873*/
1476 --IF l_create_kanban AND l_pull_seq_rec.number_of_cards >0 THEN
1477 no_of_cards_created:=l_pull_seq_rec.number_of_cards; --Bug 11829018
1478 IF l_create_kanban THEN
1479 PUT_LINE('calling INV_kanban_PVT.create_kanban_cards');
1480 INV_kanban_PVT.create_kanban_cards( l_return_status,
1481 l_kanban_card_ids,
1482 l_pull_seq_rec,
1483 X_STATUS );
1484
1485 if l_return_status = FND_API.G_RET_STS_ERROR then
1486 Raise FND_API.G_EXC_ERROR;
1487 end if;
1488
1489 if l_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
1490 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
1491 end if;
1492
1493 if X_PRINT_KANBAN_CARD = INVKBCGN.flm_ekb_yes then
1494 if X_REPORT_ID IS NULL then
1495 select MTL_KANBAN_CARD_PRINT_TEMP_S.nextval
1496 into l_report_id from DUAL;
1497 X_REPORT_ID := l_report_id;
1498 end if;
1499 for l_card_count in 1..l_kanban_card_Ids.count
1500 LOOP
1501 insert into MTL_KANBAN_CARD_PRINT_TEMP
1502 (REPORT_ID,KANBAN_CARD_ID)
1503 values ( x_report_id, l_kanban_card_Ids(l_card_count) );
1504 END LOOP;
1505 end if;
1506
1507 if X_loc_id is not NULL then
1508
1509 kb_get_conc_segments(X_org_id, X_loc_id, l_loc_name);
1510 /*
1511 Select concatenated_segments
1512 into l_loc_name
1513 from mtl_item_locations_kfv
1514 where inventory_location_id = X_loc_id and
1515 organization_id = X_org_id;
1516 */
1517 end if;
1518
1519 if X_item_id is not NULL then
1520 Select concatenated_segments
1521 into l_item_name
1522 from mtl_system_items_kfv
1523 where inventory_item_id = X_item_id and
1524 organization_id = X_org_id;
1525 end if;
1526
1527 Select organization_code
1528 into l_org_code
1529 from mtl_parameters
1530 where ORGANIZATION_ID = X_org_id;
1531
1532 FND_MESSAGE.set_name('INV','INV_KANBAN_CARDS_CREATED');
1533 FND_MESSAGE.SET_TOKEN('ORG_CODE',l_org_code);
1534 FND_MESSAGE.SET_TOKEN('ITEM_NAME',l_item_name);
1535 FND_MESSAGE.SET_TOKEN('SUB_CODE',X_subinv);
1536 FND_MESSAGE.SET_TOKEN('LOCATOR_NAME',l_loc_name);
1537 PUT_LINE( fnd_message.get );
1538 else
1539 PUT_LINE( fnd_message.get );
1540 end if;
1541 Exception
1542 WHEN end_of_code THEN
1543 NULL;
1544 when FND_API.G_EXC_ERROR then
1545 print_error;
1546 when FND_API.G_EXC_UNEXPECTED_ERROR then
1547 print_error;
1548 when others then
1549 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME , 'card_check_and_create');
1550 print_error;
1551 END card_check_and_create;
1552
1553 PROCEDURE query_range_loc( X_org_id IN Number,
1554 X_locator_lo IN VARCHAR2,
1555 X_locator_hi IN VARCHAR2,
1556 X_where OUT NOCOPY VARCHAR2 ) IS
1557 v_num NUMBER;
1558 v_delim varchar2(1);
1559 v_append varchar2(1000) := NULL;
1560 v_where varchar2(2000) := NULL;
1561 v_cnt Number := 0;
1562 v_ctr Number;
1563 v_flex_num Number := Null;
1564 v_proj_ref_enabled Number := Null;
1565 Rec Boolean := FALSE;
1566 comma varchar2(1) := '''';
1567
1568 seg_low fnd_flex_ext.SegmentArray;
1569 seg_high fnd_flex_ext.SegmentArray;
1570
1571 Cursor CUR1(v_struct_num Number) is
1572 select a.application_column_name, b.format_type
1573 from FND_ID_FLEX_SEGMENTS_VL a, FND_FLEX_VALUE_SETS b
1574 where a.application_id = 401 and
1575 a.id_flex_code = 'MTLL' and
1576 a.id_flex_num = v_struct_num and
1577 a.enabled_flag = 'Y' and
1578 a.display_flag = 'Y' and
1579 a.flex_value_set_id = b.flex_value_set_id
1580 order by a.segment_num;
1581
1582
1583 --CUR2 CUR1%ROWTYPE;
1584
1585 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1586 BEGIN
1587
1588 Select id_flex_num into v_flex_num
1589 from fnd_id_flex_structures
1590 where id_flex_code = 'MTLL';
1591
1592 Select project_reference_enabled into v_proj_ref_enabled
1593 from MTL_PARAMETERS
1594 where organization_id = X_org_id;
1595
1596 v_delim := fnd_flex_ext.get_delimiter('INV','MTLL', v_flex_num);
1597
1598 v_num := fnd_flex_ext.breakup_segments( X_locator_lo, v_delim, seg_low);
1599 v_num := fnd_flex_ext.breakup_segments( X_locator_hi, v_delim, seg_high);
1600
1601
1602 -- bug 4662395 set the profile mfg_organization_id so
1603 -- the call to MTL_PROJECT_V will return data.
1604
1605 FND_PROFILE.put('MFG_ORGANIZATION_ID',X_org_id);
1606
1607 -- Building the where clause
1608
1609 for CUR2 in CUR1(v_flex_num) Loop
1610 Rec := TRUE;
1611 v_cnt := v_cnt + 1;
1612 if ( (seg_low(v_cnt) IS NOT NULL) OR (seg_high(v_cnt) IS NOT NULL) ) then
1613 if v_where is not null then
1614 v_append := v_where||' and ';
1615 else
1616 v_append := NULL;
1617 end if;
1618
1619 if v_proj_ref_enabled = 1 AND
1620 ( CUR2.APPLICATION_COLUMN_NAME = 'SEGMENT19' OR
1621 CUR2.APPLICATION_COLUMN_NAME = 'SEGMENT20') then
1622 if CUR2.APPLICATION_COLUMN_NAME = 'SEGMENT19' then
1623 v_where := v_append ||' to_number(SEGMENT19) in '||
1624 '(select project_id from mtl_project_v where '||
1625 'project_name >= nvl('||comma||seg_low(v_cnt)||comma||
1626 ', project_name) '|| 'and project_name <= nvl('||
1627 comma||seg_high(v_cnt)||comma||', project_name))';
1628 elsif CUR2.APPLICATION_COLUMN_NAME = 'SEGMENT20' then
1629 v_where := v_append ||' to_number(SEGMENT20) in '||
1630 '(select task_id from mtl_task_v where '||
1631 'project_id = nvl(to_number(SEGMENT19),project_id) and '||
1632 'project_name >= nvl('||comma||seg_low(v_cnt)||comma||
1633 ', project_name) '||
1634 'and project_name <= nvl('||comma||seg_high(v_cnt)||comma||
1635 ',project_name))';
1636 end if;
1637 else
1638
1639 if seg_low(v_cnt) is not null then
1640 if CUR2.FORMAT_TYPE = 'N' then
1641 v_where := v_append||' to_number('||CUR2.APPLICATION_COLUMN_NAME||
1642 ')'||' >= '||seg_low(v_cnt);
1643 else
1644 v_where := v_append||' '||CUR2.APPLICATION_COLUMN_NAME||
1645 ' >= '||comma||seg_low(v_cnt)||comma;
1646 end if;
1647 end if;
1648 if v_where is not null then
1649 v_append := v_where||' and ';
1650 else
1651 v_append := null;
1652 end if;
1653 if seg_high(v_cnt) is not null then
1654 if CUR2.FORMAT_TYPE = 'N' then
1655 v_where := v_append||' to_number('||CUR2.APPLICATION_COLUMN_NAME||
1656 ')'||' <= '||seg_high(v_cnt);
1657 else
1658 v_where := v_append||' '||CUR2.APPLICATION_COLUMN_NAME||
1659 ' <= '||comma||seg_high(v_cnt)||comma;
1660 end if;
1661 end if;
1662 end if;
1663 end if;
1664 end Loop;
1665 X_where := v_where;
1666
1667 if NOT Rec then
1668 FND_MESSAGE.set_name('INV','INV_NO_LOCATOR_SEGMENTS_FOUND');
1669 PUT_LINE( fnd_message.get );
1670 current_error_code := to_char(SQLCODE);
1671 end if;
1672 Exception
1673 when others then
1674 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME , 'query_range_loc');
1675 print_error;
1676 end query_range_loc;
1677
1678
1679
1680 PROCEDURE query_range_itm( X_item_lo IN VARCHAR2,
1681 X_item_hi IN VARCHAR2,
1682 X_where OUT NOCOPY VARCHAR2 ) IS
1683 v_num NUMBER;
1684 v_delim varchar2(1);
1685 v_append varchar2(1000) := NULL;
1686 v_where varchar2(2000) := NULL;
1687 v_cnt Number := 0;
1688 v_ctr Number;
1689 v_flex_num Number := Null;
1690 Rec Boolean := FALSE;
1691 comma varchar2(1) := '''';
1692
1693 seg_low fnd_flex_ext.SegmentArray;
1694 seg_high fnd_flex_ext.SegmentArray;
1695
1696
1697
1698 Cursor CUR1(v_struct_num Number) is
1699 select a.application_column_name, b.format_type
1700 from FND_ID_FLEX_SEGMENTS_VL a, FND_FLEX_VALUE_SETS b
1701 where a.application_id = 401 and
1702 a.id_flex_code = 'MSTK' and
1703 a.id_flex_num = v_struct_num and
1704 a.enabled_flag = 'Y' and
1705 a.display_flag = 'Y' and
1706 a.flex_value_set_id = b.flex_value_set_id
1707 order by a.segment_num;
1708
1709 -- CUR2 CUR1%ROWTYPE;
1710
1711 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1712 BEGIN
1713
1714 Select id_flex_num into v_flex_num
1715 from fnd_id_flex_structures
1716 where id_flex_code = 'MSTK';
1717
1718 v_delim := fnd_flex_ext.get_delimiter('INV','MSTK',v_flex_num);
1719 v_num := fnd_flex_ext.breakup_segments( X_item_lo, v_delim, seg_low);
1720 v_num := fnd_flex_ext.breakup_segments( X_item_hi, v_delim, seg_high);
1721
1722 -- Building the where clause
1723
1724 for CUR2 in CUR1(v_flex_num) Loop
1725 Rec := TRUE;
1726 v_cnt := v_cnt + 1;
1727 if ( (seg_low(v_cnt) IS NOT NULL) OR (seg_high(v_cnt) IS NOT NULL) ) then
1728 if v_where is not null then
1729 v_append := v_where||' and ';
1730 else
1731 v_append := NULL;
1732 end if;
1733 if seg_low(v_cnt) is not null then
1734 if CUR2.FORMAT_TYPE = 'N' then
1735 v_where := v_append||' to_number('||CUR2.APPLICATION_COLUMN_NAME||
1736 ')'||' >= '||seg_low(v_cnt);
1737 else
1738 v_where := v_append||' '||CUR2.APPLICATION_COLUMN_NAME||
1739 ' >= '||comma||seg_low(v_cnt)||comma;
1740 end if;
1741 end if;
1742 if v_where is not null then
1743 v_append := v_where||' and ';
1744 else
1745 v_append := null;
1746 end if;
1747 if seg_high(v_cnt) is not null then
1748 if CUR2.FORMAT_TYPE = 'N' then
1749 v_where := v_append||' to_number('||CUR2.APPLICATION_COLUMN_NAME||
1750 ')'||' <= '||seg_high(v_cnt);
1751 else
1752 v_where := v_append||' '||CUR2.APPLICATION_COLUMN_NAME||
1753 ' <= '||comma||seg_high(v_cnt)||comma;
1754 end if;
1755 end if;
1756 end if;
1757 end Loop;
1758 X_where := v_where;
1759
1760 if NOT Rec then
1761 FND_MESSAGE.set_name('INV','INV_NO_ITEM_SEGMENTS_FOUND');
1762 PUT_LINE( fnd_message.get );
1763 current_error_code := to_char(SQLCODE);
1764 end if;
1765 Exception
1766 when others then
1767 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME , 'query_range_itm');
1768 print_error;
1769 end query_range_itm;
1770
1771
1772 PROCEDURE print_kanban_report( X_REPORT_ID IN NUMBER ) IS
1773 v_req_id NUMBER;
1774 v_sort_by NUMBER := 3;
1775 v_call_from NUMBER := 2;
1776
1777 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1778
1779 --declare below variables for ekanban by javakat
1780 v_conc_prog VARCHAR2(15);
1781 l_add_layout BOOLEAN;
1782 l_language VARCHAR2 (10);
1783 l_territory VARCHAR2 (10);
1784 BEGIN
1785 --Changes made for Generate Kanban Cards by javakat
1786 If NVL(FND_PROFILE.VALUE('FLM_EKB_OPS_FLAG'),0)=INVKBCGN.flm_ekb_yes THEN
1787 v_conc_prog:='INVKBCPR_XML';
1788
1789 BEGIN
1790 SELECT LOWER(FL.iso_language),ISO_TERRITORY
1791 INTO l_language,l_territory
1792 FROM NLS_SESSION_PARAMETERS NSP
1793 ,fnd_languages FL
1794 WHERE NSP.parameter = 'NLS_LANGUAGE'
1795 AND NSP.value = FL.nls_language;
1796 EXCEPTION
1797 WHEN NO_DATA_FOUND THEN
1798 FND_MESSAGE.set_name('FLM', 'FLM_SET_NLS_PARAM');
1799 fnd_file.put_line(fnd_file.log, fnd_message.get);
1800 END;
1801
1802 l_add_layout := fnd_request.add_layout ('INV', 'INVKBCPR_XML', l_language, l_territory, 'RTF');
1803 ELSE
1804 v_conc_prog:='INVKBCPR';
1805 END IF;
1806 v_req_id := fnd_request.submit_request( 'INV',
1807 v_conc_prog, --'INVKBCPR',
1808 NULL,
1809 NULL,
1810 FALSE,
1811 NULL, /* p_org_id */
1812 NULL, /* p_date_created_low */
1813 NULL, /* p_date_created_high */
1814 NULL, /* p_kanban_card_number_low */
1815 NULL, /* p_kanban_card_number_high */
1816 NULL, /* p_item_low */
1817 NULL, /* p_item_high */
1818 NULL, /* p_subinv */
1819 NULL, /* p_locator_low */
1820 NULL, /* p_locator_high */
1821 NULL, /* p_source_type */
1822 NULL, /* p_kanban_card_type */
1823 NULL, /* p_supplier */
1824 NULL, /* p_supplier_site */
1825 NULL, /* p_source_org_id */
1826 NULL, /* p_source_subinv */
1830 NULL, /* p_kanban_card_id */
1827 NULL, /* p_source_loc_id */
1828 v_sort_by, /* p_sort_by */
1829 v_call_from, /* p_call_from */
1831 X_REPORT_ID /* p_report_id */
1832 );
1833
1834 PUT_LINE( fnd_message.get );
1835 if v_req_id = 0 then
1836 delete from MTL_KANBAN_CARD_PRINT_TEMP
1837 where
1838 report_id = X_REPORT_ID;
1839 end if;
1840 END print_kanban_report;
1841
1842
1843 Procedure Print_Error IS
1844 l_count number;
1845 l_msg varchar2(2000);
1846 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1847 begin
1848 Fnd_msg_pub.Count_And_get(p_count => l_count,
1849 p_data => l_msg,
1850 p_encoded => 'F');
1851 if l_count = 0
1852 then
1853 null;
1854 elsif l_count = 1
1855 then
1856 PUT_LINE( l_msg);
1857 else
1858 For I in 1..l_count
1859 loop
1860 l_msg := fnd_msg_pub.get(I,'F');
1861 PUT_LINE( l_msg);
1862 end loop;
1863 PUT_LINE(' ');
1864 end if;
1865 FND_MSG_PUB.initialize;
1866 Exception
1867 when others then
1868 PUT_LINE( SQLERRM );
1869 End Print_Error;
1870
1871
1872 Procedure kb_get_conc_segments( X_org_id IN Number,
1873 X_loc_id IN Number,
1874 X_conc_segs OUT NOCOPY varchar2 ) is
1875 v_loc_str varchar2(2000) := null;
1876 v_proj_name varchar2(50) := null;
1877 v_task_name varchar2(50) := null;
1878 v_append varchar2(1000) := null;
1879 v_parse_str varchar2(3000) := null;
1880 v_num NUMBER;
1881 v_cnt NUMBER := 0;
1882 v_proj_ref_enabled Number := Null;
1883 v_flex_code varchar2(5) := 'MTLL';
1884 v_flex_num Number;
1885 v_seg19_f Boolean := False;
1886 v_seg20_f Boolean := False;
1887 v_delim varchar2(1) := Null;
1888 dsql_cur Number;
1889 rows_processed Number;
1890 str1 varchar2(15) := NULL;
1891 d_data_str varchar2(1000) := null;
1892
1893 Cursor CUR1(flex_code varchar2) is
1894 select a.application_column_name
1895 from FND_ID_FLEX_SEGMENTS_VL a
1896 where a.application_id = 401 and
1897 a.id_flex_code = flex_code and
1898 a.id_flex_num = (select id_flex_num
1899 from fnd_id_flex_structures
1900 where id_flex_code = flex_code) and
1901 a.enabled_flag = 'Y' and
1902 a.display_flag = 'Y'
1903 order by a.segment_num;
1904
1905
1906 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1907 BEGIN
1908
1909 Select id_flex_num into v_flex_num
1910 from fnd_id_flex_structures
1911 where id_flex_code = 'MTLL';
1912
1913 Select project_reference_enabled into v_proj_ref_enabled
1914 from MTL_PARAMETERS
1915 where organization_id = X_org_id;
1916
1917 -- bug 4662395 set the profile mfg_organization_id so
1918 -- the call to MTL_PROJECT_V will return data.
1919
1920 FND_PROFILE.put('MFG_ORGANIZATION_ID',X_org_id);
1921
1922 v_delim := fnd_flex_ext.get_delimiter('INV',v_flex_code, v_flex_num);
1923
1924 str1 := '||'''||v_delim||'''||';
1925
1926 for CUR2 in CUR1(v_flex_code) Loop
1927 if v_proj_ref_enabled = 1 AND
1928 ( CUR2.APPLICATION_COLUMN_NAME = 'SEGMENT19' OR
1929 CUR2.APPLICATION_COLUMN_NAME = 'SEGMENT20') then
1930 if CUR2.APPLICATION_COLUMN_NAME = 'SEGMENT19' then
1931 begin
1932 v_seg19_f := True;
1933 select distinct project_name into v_proj_name
1934 from mtl_project_v where
1935 project_id = ( select nvl(to_number(SEGMENT19), 0)
1936 from mtl_item_locations
1937 where inventory_location_id = X_loc_id and
1938 organization_id = X_org_id );
1939 exception
1940 when others then
1941 v_proj_name := null;
1942 end;
1943 elsif CUR2.APPLICATION_COLUMN_NAME = 'SEGMENT20' then
1944 begin
1945 v_seg20_f := True;
1946 select distinct A.task_name into v_task_name
1947 from mtl_task_v A where
1948 A.task_id = (select nvl(to_number(SEGMENT20), 0)
1949 from mtl_item_locations
1950 where inventory_location_id = X_loc_id and
1951 organization_id = X_org_id ) AND
1952 A.project_id = (select nvl(to_number(SEGMENT19), A.project_id)
1953 from mtl_item_locations
1954 where inventory_location_id = X_loc_id and
1955 organization_id = X_org_id );
1956 exception
1957 when others then
1958 v_task_name := null;
1959 end;
1960 end if;
1961 end if;
1962 end Loop;
1963
1964 for CUR2 in CUR1(v_flex_code) Loop
1965 if v_loc_str is not null then
1966 v_append := v_loc_str||str1;
1967 else
1968 v_append := NULL;
1969 end if;
1970 if ( CUR2.APPLICATION_COLUMN_NAME <> 'SEGMENT19' AND
1971 CUR2.APPLICATION_COLUMN_NAME <> 'SEGMENT20') then
1972 v_loc_str := v_append||CUR2.APPLICATION_COLUMN_NAME;
1973 end if;
1974 end Loop;
1975
1976 if v_loc_str is not null then
1977 v_parse_str := 'select '||v_loc_str||
1978 ' from mtl_item_locations where inventory_location_id = :loc_id '||
1979 ' and organization_id = :org_id';
1980
1981 dsql_cur := dbms_sql.open_cursor;
1982 dbms_sql.parse(dsql_cur,v_parse_str,dbms_sql.native);
1983 dbms_sql.define_column(dsql_cur,1,d_data_str,800);
1984
1985 dbms_sql.bind_variable(dsql_cur,'loc_id',X_loc_id);
1986 dbms_sql.bind_variable(dsql_cur,'org_id',X_org_id);
1987 rows_processed := dbms_sql.execute(dsql_cur);
1988
1989 Loop
1990 if ( dbms_sql.fetch_rows(dsql_cur) > 0 ) then
1991 dbms_sql.column_value(dsql_cur,1,d_data_str);
1992 else
1993 -- No more rows in cursor
1994 dbms_sql.close_cursor(dsql_cur);
1995 Exit;
1996 end if;
1997 End loop;
1998 if dbms_sql.is_open(dsql_cur) then
1999 dbms_sql.close_cursor(dsql_cur);
2000 end if;
2001 end if;
2002
2003 if v_seg19_f and v_seg20_f then
2004 X_conc_segs := d_data_str||v_delim||v_proj_name||v_delim||v_task_name;
2005 elsif v_seg19_f then
2006 X_conc_segs := d_data_str||v_delim||v_proj_name;
2007 elsif v_seg20_f then
2008 X_conc_segs := d_data_str||v_delim||v_task_name;
2009 else
2010 X_conc_segs := d_data_str;
2011 end if;
2012 Exception
2013 when others then
2014 X_conc_segs := NULL;
2015 END kb_get_conc_segments;
2016
2017 PROCEDURE Get_Pull_Sequence_Tokens
2018 (p_Pull_Sequence_Id Number,
2019 x_org_code Out NOCOPY varchar2,
2020 x_item_name Out NOCOPY varchar2,
2021 x_subinventory Out NOCOPY varchar2,
2022 x_loc_name Out NOCOPY varchar2)
2023 IS
2024
2025 l_locator_id number;
2026 l_organization_id number;
2027 l_org_code varchar2(3);
2028 l_item_name varchar2(200);
2029 l_loc_name varchar2(200);
2030 l_subinventory varchar2(10);
2031
2032 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2033 Begin
2034 Select concatenated_segments,organization_code,
2035 subinventory_name,locator_id,pull.organization_id
2036 into x_item_name,x_org_code,x_subinventory,l_locator_id,
2037 l_organization_id
2038 from mtl_system_items_kfv a , mtl_parameters b,
2039 mtl_kanban_pull_sequences pull
2040 where a.inventory_item_id = pull.inventory_item_id
2041 and a.organization_id = Pull.organization_id
2042 and b.organization_id = Pull.organization_id
2043 and pull.pull_sequence_id = p_Pull_sequence_id;
2044
2045 if l_locator_id is not null Then
2046
2047 Select concatenated_segments
2048 into x_loc_name
2049 from mtl_item_locations_kfv
2050 where inventory_location_id = l_locator_id
2051 and organization_id = l_organization_id;
2052
2053 end if;
2054 Exception
2055
2056 When Others
2057 Then Null;
2058
2059 End Get_Pull_Sequence_Tokens;
2060
2061 --
2062 --
2063 -- Create_eKanban_Cards : This procedure checks whether kanban cards can
2064 -- be generated for a pull sequences if ekanban profile is enabled.
2065 -- Added this wrapper function by javakat to fix bug 11829018
2066 --
2067 Procedure Create_eKanban_Cards(X_return_status OUT NOCOPY VARCHAR2,
2068 X_return_msg OUT NOCOPY VARCHAR2,
2069 p_Pull_sequence_id IN NUMBER,
2070 p_print_cards IN NUMBER
2071 )
2072 IS
2073 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2074 l_error_msg VARCHAR2(2000);
2075 l_ret_code NUMBER;
2076
2077 l_size Number;
2078 l_future_size Number;
2079 l_plan_eff_date Date;
2080 l_num_card Number;
2081 l_future_num_card Number;
2082 l_item_name varchar2(2000);
2083 l_loc_name varchar2(2000);
2084 l_subinventory varchar2(10);
2085 l_org_code varchar2(3);
2086 BEGIN
2087 PUT_LINE('calling INV_kanban_PVT.Create_eKanban_Cards');
2088 --make a call to Create_Kanban_Cards
2089
2090 select nvl(planning_effectivity,trunc(sysdate)),future_card_size,kanban_size,number_of_cards,future_no_of_cards
2091 into l_plan_eff_date,l_future_size,l_size,l_num_card,l_future_num_card
2092 from mtl_kanban_pull_sequences where pull_sequence_id = p_Pull_sequence_id;
2093
2094 if ( (l_plan_eff_date <= trunc(sysdate) and nvl(l_future_size,nvl(l_size,0)) = 0) or (l_plan_eff_date > trunc(sysdate) and nvl(l_size,0)=0) ) then
2095 Get_Pull_sequence_Tokens(p_Pull_sequence_id,l_org_code,l_item_name,l_subinventory,l_loc_name);
2096 FND_MESSAGE.SET_NAME('INV','INV_CANT_GEN_CRDS_NO_KBN_SIZE');
2097 FND_MESSAGE.SET_TOKEN('ORG_CODE',l_org_code);
2098 FND_MESSAGE.SET_TOKEN('ITEM_NAME',l_item_name);
2099 FND_MESSAGE.SET_TOKEN('SUB_CODE',l_Subinventory);
2100 FND_MESSAGE.SET_TOKEN('LOCATOR_NAME',l_loc_name);
2101 X_return_msg:= fnd_message.get;
2102 X_return_status:='E';
2103 elsif ((l_plan_eff_date <= trunc(sysdate) and nvl(l_future_num_card,nvl(l_num_card,0)) =0) or (l_plan_eff_date > trunc(sysdate) and nvl(l_num_card,0)=0)) then
2104 Get_Pull_sequence_Tokens(p_Pull_sequence_id,l_org_code,l_item_name,l_subinventory,l_loc_name);
2105 FND_MESSAGE.SET_NAME('INV','INV_CANT_GEN_CRDS_NO_NUM_CARDS');
2106 FND_MESSAGE.SET_TOKEN('ORG_CODE',l_org_code);
2107 FND_MESSAGE.SET_TOKEN('ITEM_NAME',l_item_name);
2108 FND_MESSAGE.SET_TOKEN('SUB_CODE',l_Subinventory);
2109 FND_MESSAGE.SET_TOKEN('LOCATOR_NAME',l_loc_name);
2110 X_return_msg:= fnd_message.get;
2111 X_return_status:='E';
2112 else
2113 INVKBCGN.create_kanban_cards(
2114 errbuf => l_error_msg
2115 ,retcode => l_ret_code
2116 ,x_org_id => null
2117 ,x_item_lo => null
2118 ,x_item_hi => null
2119 ,x_subinv => null
2120 ,x_locator_lo => null
2121 ,x_locator_hi => null
2122 ,x_source_type => null
2123 ,x_supplier_id => null
2124 ,x_supplier_site_id => null
2125 ,x_sourcing_org_id => null
2126 ,x_sourcing_subinv => null
2127 ,x_sourcing_loc_id => null
2128 ,x_wip_line_id => null
2129 ,x_status => 1
2130 ,x_pull_seq_id => p_Pull_sequence_id
2131 ,x_print_kanban_card => p_print_cards
2132 ,x_report_id => null
2133 ,x_enable_ekanban => null
2134 ,x_enable_dummy => null
2135 ,x_create_card_flag => 1
2136 ,x_plan_card_flag => 2);
2137
2138 IF TRUNC(l_ret_code )<>1 THEN
2139 X_return_status:='E';
2140 X_return_msg:=l_ret_code;
2141 END IF;
2142
2143 --Read variable no_of_cards_created
2144 If no_of_cards_created >0 AND TRUNC(l_ret_code )=1 THEN
2145 Get_Pull_sequence_Tokens(p_Pull_sequence_id,l_org_code,l_item_name,l_subinventory,l_loc_name);
2146 If p_print_cards = 2 then
2147 FND_MESSAGE.set_name('FLM','FLM_NUM_CARDS_GEN');
2148 else
2149 FND_MESSAGE.set_name('FLM','FLM_NUM_CARDS_GEN_AND_PRINT');
2150 end if;
2151 FND_MESSAGE.SET_TOKEN('NUM',no_of_cards_created);
2152 FND_MESSAGE.SET_TOKEN('ORG_CODE',l_org_code);
2153 FND_MESSAGE.SET_TOKEN('ITEM_NAME',l_item_name);
2154 FND_MESSAGE.SET_TOKEN('SUB_CODE',l_Subinventory);
2155 FND_MESSAGE.SET_TOKEN('LOCATOR_NAME',l_loc_name);
2156 X_return_msg:= fnd_message.get;
2157 X_return_status:='S';
2158 END IF;
2159 --Bug#16058961-Added less than condition
2160 If no_of_cards_created <=0 AND TRUNC(l_ret_code )=1 THEN
2161 Get_Pull_sequence_Tokens(p_Pull_sequence_id,l_org_code,l_item_name,l_subinventory,l_loc_name);
2162 FND_MESSAGE.set_name('FLM','FLM_NO_CARDS_GEN');
2163 FND_MESSAGE.SET_TOKEN('ORG_CODE',l_org_code);
2164 FND_MESSAGE.SET_TOKEN('ITEM_NAME',l_item_name);
2165 FND_MESSAGE.SET_TOKEN('SUB_CODE',l_Subinventory);
2166 FND_MESSAGE.SET_TOKEN('LOCATOR_NAME',l_loc_name);
2167 X_return_msg:= fnd_message.get;
2168 X_return_status:='S';
2169 END IF;
2170 End if;
2171 EXCEPTION
2172 WHEN NO_DATA_FOUND THEN
2173 FND_MESSAGE.set_name('FLM','FLM_GEN_CARD_AFT_PS_SAVE');
2174 X_return_msg:= fnd_message.get;
2175 X_return_status:='E';
2176 WHEN OTHERS THEN
2177 X_return_msg:= 'Unhandled exception while generating cards : '||sqlerrm(sqlcode);
2178 X_return_status:='E';
2179 END Create_eKanban_Cards;
2180
2181 END INVKBCGN;