[Home] [Help]
PACKAGE BODY: APPS.INVKBCGN
Source
1 PACKAGE BODY INVKBCGN as
2 /* $Header: INVKBCGB.pls 120.3 2005/10/13 19:04:57 dherring noship $ */
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 ) IS
89 v_Retcode Number;
90 CONC_STATUS BOOLEAN;
91 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
92 BEGIN
93
94 -- This statement will help running this program in standalone mode
95 -- For bug 2464024 : Commenting out set_log_file and fnd_file.put_line
96 -- statements since its giving unhandled exception when generating kanban cards.
97 /* Set_Log_File; */
98
99 -- FND_FILE.PUT_NAMES('/sqlcom/log/kb.log', '/sqlcom/log/kb.out', '/sqlcom/log' );
100
101 PUT_LINE('P_ORG_ID='||to_char(X_ORG_ID));
102 PUT_LINE('P_ITEM_LO='''||X_ITEM_LO || '''');
103 PUT_LINE('P_ITEM_HI='''||X_ITEM_HI || '''');
104 PUT_LINE('P_SUBINV='''||X_SUBINV || '''');
105 PUT_LINE('P_LOCATOR_LO='''||X_LOCATOR_LO || '''');
106 PUT_LINE('P_LOCATOR_HI='''||X_LOCATOR_HI || '''');
107 PUT_LINE('P_SOURCE_TYPE='||to_char(X_SOURCE_TYPE));
108 PUT_LINE('P_SUPPLIER_ID='||to_char(X_SUPPLIER_ID));
109 PUT_LINE('P_SUPPLIER_SITE_ID='||to_char(X_SUPPLIER_SITE_ID ));
110 PUT_LINE('P_SOURCING_ORG_ID='||to_char(X_SOURCING_ORG_ID ));
111 PUT_LINE('P_SOURCING_SUBINV='''||X_SOURCING_SUBINV || '''');
112 PUT_LINE('P_SOURCING_LOC_ID='||to_char(X_SOURCING_LOC_ID));
113 PUT_LINE('P_WIP_LINE_ID='||to_char(X_WIP_LINE_ID));
114 PUT_LINE('P_STATUS='||to_char(X_STATUS));
115 PUT_LINE('P_PULL_SEQ_ID='||to_char(X_PULL_SEQ_ID));
116 PUT_LINE('P_PRINT_CARD='||to_char(X_PRINT_KANBAN_CARD));
117 PUT_LINE('P_REPORT_ID='||to_char(X_REPORT_ID));
118 PUT_LINE(' ');
119
120
121
122 if ( X_pull_seq_id IS NOT NULL ) OR (X_REPORT_ID IS NOT NULL )then
123 v_Retcode := resolve_pullseq_with_pull( X_STATUS,
124 X_PULL_SEQ_ID,
125 X_PRINT_KANBAN_CARD,
126 X_REPORT_ID );
127 elsif ( (X_item_lo IS NULL) AND
128 (X_item_hi IS NULL) AND
129 (X_subinv IS NULL) AND
130 (X_locator_lo IS NULL) AND
131 (X_locator_hi IS NULL) AND
132 (X_source_type IS NULL) AND
133 (X_supplier_id IS NULL) AND
134 (X_supplier_site_id IS NULL) AND
135 (X_sourcing_org_id IS NULL) AND
136 (X_sourcing_subinv IS NULL) AND
137 (X_sourcing_loc_id IS NULL) AND
138 (X_wip_line_id IS NULL) ) then
139 v_Retcode := resolve_pullseq_all_null( X_ORG_ID,
140 X_ITEM_LO,
141 X_ITEM_HI,
142 X_SUBINV,
143 X_LOCATOR_LO,
144 X_LOCATOR_HI,
145 X_SOURCE_TYPE,
146 X_SUPPLIER_ID,
147 X_SUPPLIER_SITE_ID,
148 X_SOURCING_ORG_ID,
149 X_SOURCING_SUBINV,
150 X_SOURCING_LOC_ID,
151 X_WIP_LINE_ID,
152 X_STATUS,
153 X_PRINT_KANBAN_CARD );
154 elsif ( (X_locator_lo IS NOT NULL) OR
155 (X_locator_hi IS NOT NULL) ) then
156 v_Retcode := resolve_pullseq_with_loc( X_ORG_ID,
157 X_ITEM_LO,
158 X_ITEM_HI,
159 X_SUBINV,
160 X_LOCATOR_LO,
161 X_LOCATOR_HI,
162 X_SOURCE_TYPE,
163 X_SUPPLIER_ID,
164 X_SUPPLIER_SITE_ID,
165 X_SOURCING_ORG_ID,
166 X_SOURCING_SUBINV,
167 X_SOURCING_LOC_ID,
168 X_WIP_LINE_ID,
169 X_STATUS,
170 X_PRINT_KANBAN_CARD );
171 else
172 v_Retcode := resolve_pullseq_no_loc( X_ORG_ID,
173 X_ITEM_LO,
174 X_ITEM_HI,
175 X_SUBINV,
176 X_LOCATOR_LO,
177 X_LOCATOR_HI,
178 X_SOURCE_TYPE,
179 X_SUPPLIER_ID,
180 X_SUPPLIER_SITE_ID,
181 X_SOURCING_ORG_ID,
182 X_SOURCING_SUBINV,
183 X_SOURCING_LOC_ID,
184 X_WIP_LINE_ID,
185 X_STATUS,
186 X_PRINT_KANBAN_CARD );
187 end if; /** if ( X_pull_seq_id IS NOT NULL ) then **/
188
189
190 if v_Retcode = 1 then
191 RETCODE := v_Retcode;
192 CONC_STATUS :=
193 FND_CONCURRENT.SET_COMPLETION_STATUS('NORMAL',Current_Error_Code);
194 elsif v_Retcode = 3 then
195 RETCODE := v_Retcode;
196 CONC_STATUS :=
197 FND_CONCURRENT.SET_COMPLETION_STATUS('WARNING',Current_Error_Code);
198 else
199 RETCODE := v_Retcode;
200 CONC_STATUS :=
201 FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',Current_Error_Code);
202 end if;
203
204 Exception
205 when others then
206 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
207 THEN
208 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME , 'Create_Kanban_Cards');
209 print_error;
210 END IF;
211 current_error_code := to_char(SQLCODE);
212 RETCODE := 2;
213 CONC_STATUS :=
214 FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',Current_Error_Code);
215 END Create_Kanban_Cards;
216
217
218 FUNCTION resolve_pullseq_all_null(
219 X_ORG_ID IN NUMBER,
220 X_ITEM_LO IN VARCHAR2,
221 X_ITEM_HI IN VARCHAR2,
222 X_SUBINV IN VARCHAR2,
223 X_LOCATOR_LO IN VARCHAR2,
224 X_LOCATOR_HI IN VARCHAR2,
225 X_SOURCE_TYPE IN NUMBER,
226 X_SUPPLIER_ID IN NUMBER,
227 X_SUPPLIER_SITE_ID IN NUMBER,
228 X_SOURCING_ORG_ID IN NUMBER,
229 X_SOURCING_SUBINV IN VARCHAR2,
230 X_SOURCING_LOC_ID IN NUMBER,
231 X_WIP_LINE_ID IN NUMBER,
232 X_STATUS IN NUMBER,
233 X_PRINT_KANBAN_CARD IN NUMBER ) return Number IS
234
235 cursor MKPSC is
236 select
237 pull_sequence_id , organization_id , inventory_item_id ,
238 subinventory_name , locator_id , source_type , supplier_id,
239 supplier_site_id, source_organization_id, source_subinventory,
240 source_locator_id, wip_line_id, kanban_size, number_of_cards,
241 release_kanban_flag
242 from
243 MTL_KANBAN_PULL_SEQUENCES
244 where
245 kanban_plan_id = -1 AND
246 source_type in (1,2,3,4) AND
247 --release_kanban_flag = 1 AND
248 organization_id = X_org_id
249 for update of organization_id NOWAIT;
250
251 KBCC MKPSC%ROWTYPE;
252 Rec BOOLEAN := FALSE;
253 v_success Number := 1;
254 v_report_id Number := NULL;
255 v_org_code VARCHAR2(3);
256
257 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
258 Begin
259 For KBCC in MKPSC LOOP
260 REC := TRUE;
261 card_check_and_create( KBCC.PULL_SEQUENCE_ID,
262 KBCC.ORGANIZATION_ID,
263 KBCC.INVENTORY_ITEM_ID,
264 KBCC.SUBINVENTORY_NAME,
265 KBCC.LOCATOR_ID,
266 KBCC.SOURCE_TYPE,
267 KBCC.KANBAN_SIZE,
268 KBCC.NUMBER_OF_CARDS,
269 KBCC.SUPPLIER_ID,
270 KBCC.SUPPLIER_SITE_ID,
271 KBCC.SOURCE_ORGANIZATION_ID,
272 KBCC.SOURCE_SUBINVENTORY,
273 KBCC.SOURCE_LOCATOR_ID,
274 KBCC.WIP_LINE_ID,
275 X_STATUS,
276 X_PRINT_KANBAN_CARD,
277 kbcc.release_kanban_flag,
278 V_REPORT_ID );
279 END LOOP;
280 current_error_code := to_char(SQLCODE);
281
282 if NOT REC then
283 FND_MESSAGE.set_name('INV', 'INV_NO_PULLSEQ_SELECTED');
284 PUT_LINE( fnd_message.get );
285 current_error_code := to_char(SQLCODE);
286 end if; /* if NOT REC then */
287
288 -- call to report conc pgm report
289 if (X_PRINT_KANBAN_CARD = 1 AND V_REPORT_ID IS NOT NULL ) then
290 print_kanban_report( v_report_id );
291 end if;
292 Commit;
293 return v_success;
294
295 Exception
296 when others then
297 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
298 THEN
299 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME , 'resolve_pullseq_all_null');
300 print_error;
301 END IF;
302 v_success := 2;
303 current_error_code := to_char(SQLCODE);
304 return v_success;
305 END resolve_pullseq_all_null;
306
307
308
309 FUNCTION resolve_pullseq_with_pull(
310 X_STATUS IN NUMBER,
311 X_PULL_SEQ_ID IN NUMBER,
312 X_PRINT_KANBAN_CARD IN NUMBER,
313 X_REPORT_ID IN NUMBER ) return Number IS
314
315 cursor MKPSC is
316 select
317 pull_sequence_id , organization_id , inventory_item_id ,
318 subinventory_name , locator_id , source_type , supplier_id,
319 supplier_site_id, source_organization_id, source_subinventory,
323 MTL_KANBAN_PULL_SEQUENCES
320 source_locator_id, wip_line_id, kanban_size, number_of_cards,
321 release_kanban_flag
322 from
324 where
325 pull_sequence_id = X_pull_seq_id AND
326 source_type in (1,2,3,4) AND
327 --release_kanban_flag = 1 AND
328 x_report_id IS NULL
329 OR (source_type in (1,2,3,4) AND
330 --release_kanban_flag = 1 AND
331 x_report_id IS NOT NULL and
332 pull_sequence_id in (select pull_sequence_id
333 from mtl_kanban_card_print_temp
334 where x_report_id = report_id))
335 for update of organization_id NOWAIT;
336
337 KBCC MKPSC%ROWTYPE;
338 Rec BOOLEAN := FALSE;
339 v_success Number := 1;
340 v_report_id Number := NULL;
341
342 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
343 Begin
344 v_report_id := x_report_id;
345 For KBCC in MKPSC
346 LOOP
347 REC := TRUE;
348
349 card_check_and_create( KBCC.PULL_SEQUENCE_ID, KBCC.ORGANIZATION_ID,
350 KBCC.INVENTORY_ITEM_ID, KBCC.SUBINVENTORY_NAME,
351 KBCC.LOCATOR_ID, KBCC.SOURCE_TYPE,
352 KBCC.KANBAN_SIZE, KBCC.NUMBER_OF_CARDS,
353 KBCC.SUPPLIER_ID, KBCC.SUPPLIER_SITE_ID,
354 KBCC.SOURCE_ORGANIZATION_ID,
355 KBCC.SOURCE_SUBINVENTORY,
356 KBCC.SOURCE_LOCATOR_ID, KBCC.WIP_LINE_ID,
357
358 X_STATUS, X_PRINT_KANBAN_CARD,
359 kbcc.release_kanban_flag,
360 V_REPORT_ID );
361
362 END LOOP;
363 current_error_code := to_char(SQLCODE);
364 if NOT REC then
365 FND_MESSAGE.set_name('INV', 'INV_NO_PULLSEQ_SELECTED');
366 PUT_LINE( fnd_message.get );
367 current_error_code := to_char(SQLCODE);
368 end if;
369
370 -- call to report conc pgm report
371 if (X_PRINT_KANBAN_CARD = 1 AND V_REPORT_ID IS NOT NULL ) then
372 print_kanban_report( v_report_id );
373 end if;
374 Commit;
375 return v_success;
376
377 Exception
378 when others then
379 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
380 THEN
381 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME , 'resolve_pullseq_with_pull');
382 print_error;
383 END IF;
384 v_success := 2;
385 current_error_code := to_char(SQLCODE);
386 return v_success;
387 END resolve_pullseq_with_pull;
388
389
390 FUNCTION resolve_pullseq_with_loc(
391 X_ORG_ID IN NUMBER,
392 X_ITEM_LO IN VARCHAR2,
393 X_ITEM_HI IN VARCHAR2,
394 X_SUBINV IN VARCHAR2,
395 X_LOCATOR_LO IN VARCHAR2,
396 X_LOCATOR_HI IN VARCHAR2,
397 X_SOURCE_TYPE IN NUMBER,
398 X_SUPPLIER_ID IN NUMBER,
399 X_SUPPLIER_SITE_ID IN NUMBER,
400 X_SOURCING_ORG_ID IN NUMBER,
401 X_SOURCING_SUBINV IN VARCHAR2,
402 X_SOURCING_LOC_ID IN NUMBER,
403 X_WIP_LINE_ID IN NUMBER,
404 X_STATUS IN NUMBER,
405 X_PRINT_KANBAN_CARD IN NUMBER ) return Number IS
406
407 Rec BOOLEAN := FALSE;
408 d_sql_p integer := NULL;
409 d_sql_rows_processed integer := NULL;
410 d_sql_stmt varchar2(6000) := NULL;
411 d_sql_stmt1 varchar2(4000) := NULL;
412 d_sql_stmt2 varchar2(6000) := NULL;
413 p_where_itm varchar2(2000) := NULL;
414 p_where_loc varchar2(2000) := NULL;
415 d_pull_seq_id NUMBER;
416 d_org_id NUMBER;
417 d_inv_itm_id NUMBER;
418 d_subinv varchar2(10);
419 d_loc_id NUMBER;
420 d_src_type NUMBER;
421 d_supp_id NUMBER;
422 d_supp_site_id NUMBER;
423 d_src_org_id NUMBER;
424 d_src_subinv varchar2(10);
425 d_src_loc_id NUMBER;
426 d_wip_line_id NUMBER;
427 d_kanban_size NUMBER;
428 d_no_cards NUMBER;
429 d_release_kanban_flag NUMBER;
430 v_success Number := 1;
431 v_report_id Number := NULL;
432 v_org_code VARCHAR2(3);
433 v_item_name mtl_system_items_kfv.concatenated_segments%TYPE;
434 v_loc_name mtl_item_locations_kfv.concatenated_segments%TYPE;
435
436 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
437 Begin
438
439 if ( (X_ITEM_LO IS NULL) AND (X_ITEM_HI IS NULL) ) then
440
441 Begin
442 query_range_loc( X_org_id, X_locator_lo, X_locator_hi, p_where_loc);
443 if ( p_where_loc IS NOT NULL ) then
447 'select pull_sequence_id ,organization_id ,inventory_item_id ,' ||
444 d_sql_p := dbms_sql.open_cursor;
445
446 d_sql_stmt :=
448 ' subinventory_name , locator_id , source_type , supplier_id,' ||
449 ' supplier_site_id, source_organization_id, source_subinventory,' ||
450 ' source_locator_id, wip_line_id, kanban_size, number_of_cards, ' ||
451 ' release_kanban_flag, from MTL_KANBAN_PULL_SEQUENCES where '||
452 ' organization_id = :org_id AND source_type in (1,2,3,4) AND ' ||
453 ' nvl(subinventory_name,''9999'') = ' ||
454 ' nvl(:subinv, nvl(subinventory_name,''9999'')) AND ' ||
455 ' nvl(source_type,-9999) = nvl(:source_type,nvl(source_type,-9999)) AND '||
456 ' nvl(supplier_id,-9999) = nvl(:supplier_id,nvl(supplier_id,-9999)) AND '||
457 ' nvl(supplier_site_id,-9999) = ' ||
458 ' nvl(:supplier_site_id,nvl(supplier_site_id,-9999)) AND ' ||
459 ' nvl(source_organization_id,-9999) = ' ||
460 ' nvl(:sourcing_org_id, nvl(source_organization_id,-9999)) AND ' ||
461 ' nvl(source_subinventory, ''9999'') = ' ||
462 ' nvl(:sourcing_subinv, nvl(source_subinventory,''9999'')) AND ' ||
463 ' nvl(source_locator_id, -9999) = '||
464 ' nvl(:sourcing_loc_id,nvl(source_locator_id,-9999)) AND ' ||
465 ' nvl(wip_line_id, -9999) = '||
466 ' nvl(:line_id,nvl(wip_line_id,-9999)) AND ' ||
467 ' kanban_plan_id = -1 AND ' ||
468 --' release_kanban_flag = 1 AND ' ||
469 ' locator_id in ( select inventory_location_id from mtl_item_locations ' ||
470 ' where ' || p_where_loc ||
471 ' and organization_id = :org_id) '||
472 ' for update of organization_id NOWAIT ';
473
474
475 PUT_LINE(' p_where ='||p_where_loc );
476 PUT_LINE(' d_sql_stmt ='||substr(d_sql_stmt,1,75) );
477 PUT_LINE('Before parsing');
478
479 dbms_sql.parse( d_sql_p, d_sql_stmt , dbms_sql.native );
480
481 PUT_LINE('Parsed The statement in loc');
482
483 dbms_sql.define_column(d_sql_p,1,d_pull_seq_id);
484 dbms_sql.define_column(d_sql_p,2,d_org_id );
485 dbms_sql.define_column(d_sql_p,3,d_inv_itm_id);
486 dbms_sql.define_column(d_sql_p,4,d_subinv,10 );
487 dbms_sql.define_column(d_sql_p,5,d_loc_id);
488 dbms_sql.define_column(d_sql_p,6,d_src_type);
489 dbms_sql.define_column(d_sql_p,7,d_supp_id );
490 dbms_sql.define_column(d_sql_p,8,d_supp_site_id );
491 dbms_sql.define_column(d_sql_p,9,d_src_org_id );
492 dbms_sql.define_column(d_sql_p,10,d_src_subinv,10 );
493 dbms_sql.define_column(d_sql_p,11,d_src_loc_id );
494 dbms_sql.define_column(d_sql_p,12,d_wip_line_id );
495 dbms_sql.define_column(d_sql_p,13,d_kanban_size);
496 dbms_sql.define_column(d_sql_p,14,d_no_cards);
497 dbms_sql.define_column(d_sql_p,15,d_release_kanban_flag);
498
499 PUT_LINE('Defined the cols in locs');
500
501 dbms_sql.bind_variable(d_sql_p,'org_id', X_ORG_ID);
502 dbms_sql.bind_variable(d_sql_p,'subinv', X_subinv);
503 dbms_sql.bind_variable(d_sql_p,'source_type', X_source_type);
504 dbms_sql.bind_variable(d_sql_p,'supplier_id', X_supplier_id);
505 dbms_sql.bind_variable(d_sql_p,'supplier_site_id', X_supplier_site_id);
506 dbms_sql.bind_variable(d_sql_p,'sourcing_org_id', X_sourcing_org_id);
507 dbms_sql.bind_variable(d_sql_p,'sourcing_subinv', X_sourcing_subinv);
508 dbms_sql.bind_variable(d_sql_p,'sourcing_loc_id', X_sourcing_loc_id);
509 dbms_sql.bind_variable(d_sql_p,'line_id', X_wip_line_id);
510
511 PUT_LINE('Bind the vars');
512
513 d_sql_rows_processed := dbms_sql.execute(d_sql_p);
514
515 PUT_LINE('No Rows ='||to_char(d_sql_rows_processed));
516
517 Loop
518 if ( dbms_sql.fetch_rows(d_sql_p) > 0 ) then
519 Rec := TRUE;
520 dbms_sql.column_value(d_sql_p,1, d_pull_seq_id);
521 dbms_sql.column_value(d_sql_p,2, d_org_id);
522 dbms_sql.column_value(d_sql_p,3, d_inv_itm_id);
523 dbms_sql.column_value(d_sql_p,4, d_subinv);
524 dbms_sql.column_value(d_sql_p,5, d_loc_id);
525 dbms_sql.column_value(d_sql_p,6, d_src_type);
526 dbms_sql.column_value(d_sql_p,7, d_supp_id);
527 dbms_sql.column_value(d_sql_p,8, d_supp_site_id);
528 dbms_sql.column_value(d_sql_p,9, d_src_org_id);
529 dbms_sql.column_value(d_sql_p,10, d_src_subinv);
530 dbms_sql.column_value(d_sql_p,11, d_src_loc_id);
531 dbms_sql.column_value(d_sql_p,12, d_wip_line_id);
532 dbms_sql.column_value(d_sql_p,13, d_kanban_size);
533 dbms_sql.column_value(d_sql_p,14, d_no_cards);
534 dbms_sql.column_value(d_sql_p,15,d_release_kanban_flag);
535
536 card_check_and_create( d_pull_seq_id,
537 d_org_id, d_inv_itm_id, d_subinv,
538 d_loc_id, d_src_type,
539 d_kanban_size, d_no_cards,
540 d_supp_id, d_supp_site_id,
541 d_src_org_id, d_src_subinv,
542 d_src_loc_id, d_wip_line_id, X_STATUS,
543 X_PRINT_KANBAN_CARD,
544 d_release_kanban_flag,
545 V_REPORT_ID );
546 else
547 -- No more rows in cursor
548 dbms_sql.close_cursor(d_sql_p);
549 Exit;
550 end if;
551 End loop;
552 current_error_code := to_char(SQLCODE);
553
557 current_error_code := to_char(SQLCODE);
554 if NOT Rec then
555 FND_MESSAGE.set_name('INV', 'INV_NO_PULLSEQ_SELECTED');
556 PUT_LINE( fnd_message.get );
558 end if;
559 if dbms_sql.is_open(d_sql_p) then
560 dbms_sql.close_cursor(d_sql_p);
561 end if;
562 end if;
563 Exception
564 when others then
565 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
566 THEN
567 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME , 'resolve_pullseq_with_loc');
568 print_error;
569 END IF;
570 if dbms_sql.is_open(d_sql_p) then
571 dbms_sql.close_cursor(d_sql_p);
572 end if;
573 v_success := 2;
574 current_error_code := to_char(SQLCODE);
575 end;
576
577 Else
578
579 Begin
580
581 query_range_itm( X_item_lo, X_item_hi, p_where_itm);
582 query_range_loc( X_org_id, X_locator_lo, X_locator_hi, p_where_loc);
583
584 if (( p_where_itm IS NOT NULL ) AND ( p_where_loc IS NOT NULL)) then
585 d_sql_p := dbms_sql.open_cursor;
586 d_sql_stmt :=
587 'select pull_sequence_id ,organization_id ,inventory_item_id ,' ||
588 ' subinventory_name , locator_id , source_type , supplier_id,' ||
589 ' supplier_site_id, source_organization_id, source_subinventory,' ||
590 ' source_locator_id, wip_line_id, kanban_size, number_of_cards, ' ||
591 ' release_kanban_flag from MTL_KANBAN_PULL_SEQUENCES where '||
592 ' organization_id = :org_id AND source_type in (1,2,3,4) AND ' ||
593 'nvl(subinventory_name,''9999'') = ' ||
594 'nvl(:subinv, nvl(subinventory_name,''9999'')) AND ' ||
595 'nvl(source_type,-9999) = nvl(:source_type, nvl(source_type,-9999)) AND '||
596 'nvl(supplier_id,-9999) = nvl(:supplier_id,nvl(supplier_id, -9999)) AND '||
597 'nvl(supplier_site_id,-9999) = ' ||
598 'nvl(:supplier_site_id,nvl(supplier_site_id,-9999)) AND ' ||
599 'nvl(source_organization_id,-9999) = ' ||
600 'nvl(:sourcing_org_id, nvl(source_organization_id,-9999)) AND ' ||
601 'nvl(source_subinventory, ''9999'') = ' ||
602 'nvl(:sourcing_subinv, nvl(source_subinventory,''9999'')) AND ' ||
603 'nvl(source_locator_id, -9999) = '||
604 'nvl(:sourcing_loc_id,nvl(source_locator_id,-9999)) AND ' ||
605 'nvl(wip_line_id, -9999) = '||
606 'nvl(:line_id,nvl(wip_line_id,-9999)) AND ' ||
607 'locator_id in ( select inventory_location_id from mtl_item_locations ' ||
608 'where ' || p_where_loc ||
609 ' and organization_id = :org_id) AND ' ||
610 'kanban_plan_id = -1';
611 -- and || 'release_kanban_flag = 1 '
612
613
614 d_sql_stmt1 := ' AND ' ||
615 'inventory_item_id in ( select inventory_item_id from mtl_system_items ' ||
616 'where ' || p_where_itm || ' and organization_id = :org_id) ' ||
617 ' for update of organization_id NOWAIT ';
618
619 PUT_LINE(' len stmt ='||to_char(length(d_sql_stmt)) );
620 PUT_LINE(' len stmt1 ='||to_char(length(d_sql_stmt1)));
621
622 d_sql_stmt2 := d_sql_stmt || d_sql_stmt1;
623
624 PUT_LINE(' p_where_itm ='||p_where_itm );
625 PUT_LINE(' p_where_loc ='||p_where_loc );
626 PUT_LINE(' d_sql_stmt ='||d_sql_stmt );
627 PUT_LINE(' d_sql_stmt1 ='||d_sql_stmt1 );
628
629 dbms_sql.parse( d_sql_p, d_sql_stmt2, dbms_sql.native );
630
631 PUT_LINE('Parsed The statement in loc-itm');
632
633 dbms_sql.define_column(d_sql_p,1,d_pull_seq_id);
634 dbms_sql.define_column(d_sql_p,2,d_org_id );
635 dbms_sql.define_column(d_sql_p,3,d_inv_itm_id);
636 dbms_sql.define_column(d_sql_p,4,d_subinv,10 );
637 dbms_sql.define_column(d_sql_p,5,d_loc_id);
638 dbms_sql.define_column(d_sql_p,6,d_src_type);
639 dbms_sql.define_column(d_sql_p,7,d_supp_id );
640 dbms_sql.define_column(d_sql_p,8,d_supp_site_id );
641 dbms_sql.define_column(d_sql_p,9,d_src_org_id );
642 dbms_sql.define_column(d_sql_p,10,d_src_subinv,10 );
643 dbms_sql.define_column(d_sql_p,11,d_src_loc_id );
644 dbms_sql.define_column(d_sql_p,12,d_wip_line_id );
645 dbms_sql.define_column(d_sql_p,13,d_kanban_size);
646 dbms_sql.define_column(d_sql_p,14,d_no_cards);
647 dbms_sql.define_column(d_sql_p,15,d_release_kanban_flag);
648 PUT_LINE('Defined the cols in loc-itm');
649
650 dbms_sql.bind_variable(d_sql_p,'org_id', X_ORG_ID);
651 dbms_sql.bind_variable(d_sql_p,'subinv', X_subinv);
652 dbms_sql.bind_variable(d_sql_p,'source_type', X_source_type);
653 dbms_sql.bind_variable(d_sql_p,'supplier_id', X_supplier_id);
654 dbms_sql.bind_variable(d_sql_p,'supplier_site_id', X_supplier_site_id);
655 dbms_sql.bind_variable(d_sql_p,'sourcing_org_id', X_sourcing_org_id);
656 dbms_sql.bind_variable(d_sql_p,'sourcing_subinv', X_sourcing_subinv);
657 dbms_sql.bind_variable(d_sql_p,'sourcing_loc_id', X_sourcing_loc_id);
658 dbms_sql.bind_variable(d_sql_p,'line_id', X_wip_line_id);
659
660 PUT_LINE('Bind the vars ');
661
662 d_sql_rows_processed := dbms_sql.execute(d_sql_p);
663
664 PUT_LINE('No ofRows='||to_char(d_sql_rows_processed));
665
666 Loop
667 if ( dbms_sql.fetch_rows(d_sql_p) > 0 ) then
668 Rec := TRUE;
669 dbms_sql.column_value(d_sql_p,1, d_pull_seq_id);
670 dbms_sql.column_value(d_sql_p,2, d_org_id);
671 dbms_sql.column_value(d_sql_p,3, d_inv_itm_id);
675 dbms_sql.column_value(d_sql_p,7, d_supp_id);
672 dbms_sql.column_value(d_sql_p,4, d_subinv);
673 dbms_sql.column_value(d_sql_p,5, d_loc_id);
674 dbms_sql.column_value(d_sql_p,6, d_src_type);
676 dbms_sql.column_value(d_sql_p,8, d_supp_site_id);
677 dbms_sql.column_value(d_sql_p,9, d_src_org_id);
678 dbms_sql.column_value(d_sql_p,10, d_src_subinv);
679 dbms_sql.column_value(d_sql_p,11, d_src_loc_id);
680 dbms_sql.column_value(d_sql_p,12, d_wip_line_id);
681 dbms_sql.column_value(d_sql_p,13, d_kanban_size);
682 dbms_sql.column_value(d_sql_p,14, d_no_cards);
683 dbms_sql.column_value(d_sql_p,15,d_release_kanban_flag);
684
685 card_check_and_create( d_pull_seq_id,
686 d_org_id, d_inv_itm_id, d_subinv,
687 d_loc_id, d_src_type,
688 d_kanban_size, d_no_cards,
689 d_supp_id, d_supp_site_id,
690 d_src_org_id, d_src_subinv,
691 d_src_loc_id, d_wip_line_id, X_STATUS,
692 X_PRINT_KANBAN_CARD,
693 d_release_kanban_flag,
694 V_REPORT_ID );
695 else
696 -- No more rows in cursor
697 dbms_sql.close_cursor(d_sql_p);
698 Exit;
699 end if;
700 End loop;
701 current_error_code := to_char(SQLCODE);
702
703 if NOT Rec then
704 FND_MESSAGE.set_name('INV', 'INV_NO_PULLSEQ_SELECTED');
705 PUT_LINE( fnd_message.get );
706 current_error_code := to_char(SQLCODE);
707 end if;
708 if dbms_sql.is_open(d_sql_p) then
709 dbms_sql.close_cursor(d_sql_p);
710 end if;
711 end if;
712 Exception
713 when others then
714 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
715 THEN
716 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME , 'resolve_pullseq_with_loc');
717 print_error;
718 END IF;
719 if dbms_sql.is_open(d_sql_p) then
720 dbms_sql.close_cursor(d_sql_p);
721 end if;
722 v_success := 2;
723 current_error_code := to_char(SQLCODE);
724 end;
725
726 end if;
727
728 -- call to report conc pgm report
729 if (X_PRINT_KANBAN_CARD = 1 AND V_REPORT_ID IS NOT NULL ) then
730 print_kanban_report( v_report_id );
731 end if;
732 Commit;
733 return v_success;
734
735 Exception
736 when others then
737 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME , 'resolve_pullseq_with_loc');
738 print_error;
739 v_success := 2;
740 current_error_code := to_char(SQLCODE);
741 return v_success;
742 END resolve_pullseq_with_loc;
743
744
745 FUNCTION resolve_pullseq_no_loc(
746 X_ORG_ID IN NUMBER,
747 X_ITEM_LO IN VARCHAR2,
748 X_ITEM_HI IN VARCHAR2,
749 X_SUBINV IN VARCHAR2,
750 X_LOCATOR_LO IN VARCHAR2,
751 X_LOCATOR_HI IN VARCHAR2,
752 X_SOURCE_TYPE IN NUMBER,
753 X_SUPPLIER_ID IN NUMBER,
754 X_SUPPLIER_SITE_ID IN NUMBER,
755 X_SOURCING_ORG_ID IN NUMBER,
756 X_SOURCING_SUBINV IN VARCHAR2,
757 X_SOURCING_LOC_ID IN NUMBER,
758 X_WIP_LINE_ID IN NUMBER,
759 X_STATUS IN NUMBER,
760 X_PRINT_KANBAN_CARD IN NUMBER ) return Number IS
761
762 cursor MKPSC is
763 select
764 pull_sequence_id , organization_id , inventory_item_id ,
765 subinventory_name , locator_id , source_type , supplier_id,
766 supplier_site_id, source_organization_id, source_subinventory,
767 source_locator_id, wip_line_id, kanban_size, number_of_cards ,
768 release_kanban_flag
769 from
770 MTL_KANBAN_PULL_SEQUENCES
771 where
772 organization_id = X_org_id AND
773 nvl(subinventory_name,'9999') =
774 nvl(X_subinv, nvl(subinventory_name,'9999')) AND
775 nvl(source_type,-9999) = nvl(X_source_type, nvl(source_type,-9999)) AND
776 nvl(supplier_id,-9999) = nvl(X_supplier_id,nvl(supplier_id, -9999)) AND
777 nvl(supplier_site_id,-9999) =
778 nvl(X_supplier_site_id,nvl(supplier_site_id,-9999)) AND
779 nvl(source_organization_id,-9999) =
780 nvl(X_sourcing_org_id, nvl(source_organization_id,-9999)) AND
781 nvl(source_subinventory, '9999') =
782 nvl(X_sourcing_subinv, nvl(source_subinventory,'9999')) AND
783 nvl(source_locator_id, -9999) =
784 nvl(X_sourcing_loc_id,nvl(source_locator_id,-9999)) AND
785 nvl(wip_line_id, -9999) =
786 nvl(X_wip_line_id,nvl(wip_line_id,-9999)) AND
787 kanban_plan_id = -1 AND
788 source_type in (1,2,3,4)
789 --AND release_kanban_flag = 1
790 for update of organization_id NOWAIT;
791
792 KBCC MKPSC%ROWTYPE;
796 d_sql_stmt varchar2(4000) := NULL;
793 Rec BOOLEAN := FALSE;
794 d_sql_p integer := NULL;
795 d_sql_rows_processed integer := NULL;
797 p_where varchar2(2000) := NULL;
798 d_pull_seq_id NUMBER;
799 d_org_id NUMBER;
800 d_inv_itm_id NUMBER;
801 d_subinv varchar2(10);
802 d_loc_id NUMBER;
803 d_src_type NUMBER;
804 d_supp_id NUMBER;
805 d_supp_site_id NUMBER;
806 d_src_org_id NUMBER;
807 d_src_subinv varchar2(10);
808 d_src_loc_id NUMBER;
809 d_wip_line_id NUMBER;
810 d_kanban_size NUMBER;
811 d_no_cards NUMBER;
812 d_release_kanban_flag NUMBER;
813 v_success Number := 1;
814 v_report_id Number := NULL;
815 v_org_code VARCHAR2(3);
816 v_item_name mtl_system_items_kfv.concatenated_segments%TYPE;
817 v_loc_name mtl_item_locations_kfv.concatenated_segments%TYPE;
818
819 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
820 Begin
821 if ( (X_ITEM_LO IS NULL) AND (X_ITEM_HI IS NULL) ) then
822 Begin
823 For KBCC in MKPSC LOOP
824 REC := TRUE;
825 card_check_and_create( KBCC.PULL_SEQUENCE_ID, KBCC.ORGANIZATION_ID,
826 KBCC.INVENTORY_ITEM_ID, KBCC.SUBINVENTORY_NAME,
827 KBCC.LOCATOR_ID, KBCC.SOURCE_TYPE,
828 KBCC.KANBAN_SIZE, KBCC.NUMBER_OF_CARDS,
829 KBCC.SUPPLIER_ID, KBCC.SUPPLIER_SITE_ID,
830 KBCC.SOURCE_ORGANIZATION_ID,
831 KBCC.SOURCE_SUBINVENTORY,
832 KBCC.SOURCE_LOCATOR_ID,
833 KBCC.WIP_LINE_ID, X_STATUS,
834 X_PRINT_KANBAN_CARD,
835 kbcc.release_kanban_flag,V_REPORT_ID );
836 END LOOP;
837 current_error_code := to_char(SQLCODE);
838 if NOT REC then
839 FND_MESSAGE.set_name('INV', 'INV_NO_PULLSEQ_SELECTED');
840 PUT_LINE( fnd_message.get );
841 end if;
842 current_error_code := to_char(SQLCODE);
843 Exception
844 when others then
845 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
846 THEN
847 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME , 'resolve_pullseq_no_loc');
848 print_error;
849 END IF;
850 v_success := 2;
851 current_error_code := to_char(SQLCODE);
852 end;
853
854 Else
855
856 Begin
857
858 query_range_itm( X_item_lo, X_item_hi, p_where);
859 if ( p_where IS NOT NULL ) then
860 d_sql_p := dbms_sql.open_cursor;
861 d_sql_stmt :=
862 'select pull_sequence_id ,organization_id ,inventory_item_id ,' ||
863 ' subinventory_name , locator_id , source_type , supplier_id,' ||
864 ' supplier_site_id, source_organization_id, source_subinventory,' ||
865 ' source_locator_id, wip_line_id, kanban_size, number_of_cards, ' ||
866 ' release_kanban_flag from MTL_KANBAN_PULL_SEQUENCES where '||
867 ' organization_id = :org_id AND source_type in (1,2,3,4) AND ' ||
868 ' nvl(subinventory_name,''9999'') = ' ||
869 ' nvl(:subinv, nvl(subinventory_name,''9999'')) AND ' ||
870 ' nvl(source_type,-9999) = nvl(:source_type,nvl(source_type,-9999)) AND '||
871 ' nvl(supplier_id,-9999) = nvl(:supplier_id,nvl(supplier_id,-9999)) AND '||
872 ' nvl(supplier_site_id,-9999) = ' ||
873 ' nvl(:supplier_site_id,nvl(supplier_site_id,-9999)) AND ' ||
874 ' nvl(source_organization_id,-9999) = ' ||
875 ' nvl(:sourcing_org_id, nvl(source_organization_id,-9999)) AND ' ||
876 ' nvl(source_subinventory, ''9999'') = ' ||
877 ' nvl(:sourcing_subinv, nvl(source_subinventory,''9999'')) AND ' ||
878 ' nvl(source_locator_id, -9999) = '||
879 ' nvl(:sourcing_loc_id,nvl(source_locator_id,-9999)) AND ' ||
880 'nvl(wip_line_id, -9999) = '||
881 'nvl(:line_id,nvl(wip_line_id,-9999)) AND ' ||
882 ' kanban_plan_id = -1 AND ' ||
883 --' release_kanban_flag = 1 AND ' ||
884 ' inventory_item_id in ( select inventory_item_id from mtl_system_items '||
885 ' where ' || p_where || ' and organization_id = :org_id) ' ||
886 ' for update of organization_id NOWAIT ';
887
888
889 PUT_LINE(' p_where ='||p_where );
890 PUT_LINE(' d_sql_stmt ='||d_sql_stmt );
891
892 dbms_sql.parse( d_sql_p, d_sql_stmt , dbms_sql.native );
893
894 PUT_LINE('Parsed The statement in no_loc');
895
896 dbms_sql.define_column(d_sql_p,1,d_pull_seq_id);
897 dbms_sql.define_column(d_sql_p,2,d_org_id );
898 dbms_sql.define_column(d_sql_p,3,d_inv_itm_id);
899 dbms_sql.define_column(d_sql_p,4,d_subinv,10 );
900 dbms_sql.define_column(d_sql_p,5,d_loc_id);
901 dbms_sql.define_column(d_sql_p,6,d_src_type);
902 dbms_sql.define_column(d_sql_p,7,d_supp_id );
903 dbms_sql.define_column(d_sql_p,8,d_supp_site_id );
904 dbms_sql.define_column(d_sql_p,9,d_src_org_id );
905 dbms_sql.define_column(d_sql_p,10,d_src_subinv,10 );
909 dbms_sql.define_column(d_sql_p,14,d_no_cards);
906 dbms_sql.define_column(d_sql_p,11,d_src_loc_id );
907 dbms_sql.define_column(d_sql_p,12,d_wip_line_id );
908 dbms_sql.define_column(d_sql_p,13,d_kanban_size);
910 dbms_sql.define_column(d_sql_p,15,d_release_kanban_flag);
911
912 PUT_LINE('Defined the cols in no_locs');
913
914 dbms_sql.bind_variable(d_sql_p,'org_id', X_ORG_ID);
915 dbms_sql.bind_variable(d_sql_p,'subinv', X_subinv);
916 dbms_sql.bind_variable(d_sql_p,'source_type', X_source_type);
917 dbms_sql.bind_variable(d_sql_p,'supplier_id', X_supplier_id);
918 dbms_sql.bind_variable(d_sql_p,'supplier_site_id', X_supplier_site_id);
919 dbms_sql.bind_variable(d_sql_p,'sourcing_org_id', X_sourcing_org_id);
920 dbms_sql.bind_variable(d_sql_p,'sourcing_subinv', X_sourcing_subinv);
921 dbms_sql.bind_variable(d_sql_p,'sourcing_loc_id', X_sourcing_loc_id);
922 dbms_sql.bind_variable(d_sql_p,'line_id', X_wip_line_id);
923
924 PUT_LINE('Bind the vars in no_locs');
925
926 d_sql_rows_processed := dbms_sql.execute(d_sql_p);
927
928 PUT_LINE('No ofRows ='||to_char(d_sql_rows_processed));
929
930 Loop
931 if ( dbms_sql.fetch_rows(d_sql_p) > 0 ) then
932 Rec := TRUE;
933 dbms_sql.column_value(d_sql_p,1, d_pull_seq_id);
934 dbms_sql.column_value(d_sql_p,2, d_org_id);
935 dbms_sql.column_value(d_sql_p,3, d_inv_itm_id);
936 dbms_sql.column_value(d_sql_p,4, d_subinv);
937 dbms_sql.column_value(d_sql_p,5, d_loc_id);
938 dbms_sql.column_value(d_sql_p,6, d_src_type);
939 dbms_sql.column_value(d_sql_p,7, d_supp_id);
940 dbms_sql.column_value(d_sql_p,8, d_supp_site_id);
941 dbms_sql.column_value(d_sql_p,9, d_src_org_id);
942 dbms_sql.column_value(d_sql_p,10, d_src_subinv);
943 dbms_sql.column_value(d_sql_p,11, d_src_loc_id);
944 dbms_sql.column_value(d_sql_p,12, d_wip_line_id);
945 dbms_sql.column_value(d_sql_p,13, d_kanban_size);
946 dbms_sql.column_value(d_sql_p,14, d_no_cards);
947 dbms_sql.column_value(d_sql_p,15, d_release_kanban_flag);
948
949 card_check_and_create( d_pull_seq_id,
950 d_org_id, d_inv_itm_id, d_subinv,
951 d_loc_id, d_src_type,
952 d_kanban_size, d_no_cards,
953 d_supp_id, d_supp_site_id,
954 d_src_org_id, d_src_subinv,
955 d_src_loc_id, d_wip_line_id, X_STATUS,
956 X_PRINT_KANBAN_CARD,
957 d_release_kanban_flag,
958 V_REPORT_ID );
959 else
960 -- No more rows in cursor
961 dbms_sql.close_cursor(d_sql_p);
962 Exit;
963 end if;
964 End loop;
965 current_error_code := to_char(SQLCODE);
966 if NOT Rec then
967 FND_MESSAGE.set_name('INV', 'INV_NO_PULLSEQ_SELECTED');
968 PUT_LINE( fnd_message.get );
969 end if;
970 current_error_code := to_char(SQLCODE);
971 if dbms_sql.is_open(d_sql_p) then
972 dbms_sql.close_cursor(d_sql_p);
973 end if;
974 end if;
975 Exception
976 when others then
977 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
978 THEN
979 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME , 'resolve_pullseq_no_loc');
980 print_error;
981 END IF;
982 if dbms_sql.is_open(d_sql_p) then
983 dbms_sql.close_cursor(d_sql_p);
984 end if;
985 v_success := 2;
986 current_error_code := to_char(SQLCODE);
987 return v_success;
988 end;
989 end if;
990
991 -- call to report conc pgm report
992 if (X_PRINT_KANBAN_CARD = 1 AND V_REPORT_ID IS NOT NULL ) then
993 print_kanban_report( v_report_id );
994 end if;
995 Commit;
996 return v_success;
997
998 Exception
999 when others then
1000 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME , 'resolve_pullseq_no_loc');
1001 print_error;
1002 v_success := 2;
1003 current_error_code := to_char(SQLCODE);
1004 return v_success;
1005 END resolve_pullseq_no_loc;
1006
1007
1008 PROCEDURE card_check_and_create(
1009 X_PULL_SEQUENCE_ID IN NUMBER,
1010 X_ORG_ID IN NUMBER,
1011 X_ITEM_ID IN NUMBER,
1012 X_SUBINV IN VARCHAR2,
1013 X_LOC_ID IN NUMBER,
1014 X_SOURCE_TYPE IN NUMBER,
1015 X_KANBAN_SIZE IN NUMBER,
1016 X_NO_OF_CARDS IN NUMBER,
1017 X_SUPPLIER_ID IN NUMBER,
1018 X_SUPPLIER_SITE_ID IN NUMBER,
1019 X_SOURCING_ORG_ID IN NUMBER,
1020 X_SOURCING_SUBINV IN VARCHAR2,
1021 X_SOURCING_LOC_ID IN NUMBER,
1022 X_WIP_LINE_ID IN NUMBER,
1023 X_STATUS IN NUMBER,
1024 X_PRINT_KANBAN_CARD IN NUMBER,
1025 p_release_kanban_flag IN NUMBER,
1029 l_pull_seq_rec INV_Kanban_PVT.pull_sequence_rec_type;
1026 X_REPORT_ID IN OUT NOCOPY NUMBER ) IS
1027
1028 l_kanban_card_ids INV_Kanban_PVT.kanban_card_id_tbl_type;
1030 l_return_status varchar2(1) := FND_API.G_RET_STS_SUCCESS;
1031 l_report_id number;
1032 l_org_code VARCHAR2(3) := Null;
1033 l_item_name MTL_SYSTEM_ITEMS_KFV.CONCATENATED_SEGMENTS%TYPE := Null;
1034 l_loc_name MTL_ITEM_LOCATIONS_KFV.CONCATENATED_SEGMENTS%TYPE := Null;
1035
1036 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1037 BEGIN
1038 l_pull_seq_rec.pull_sequence_id := X_pull_sequence_id;
1039 l_pull_seq_rec.organization_id := X_org_id ;
1040 l_pull_seq_rec.inventory_item_id := X_item_id ;
1041 l_pull_seq_rec.subinventory_name := X_subinv;
1042 l_pull_seq_rec.locator_id := X_loc_id;
1043 l_pull_seq_rec.source_type := X_source_type ;
1044 l_pull_seq_rec.Kanban_size := X_kanban_size ;
1045 l_pull_seq_rec.number_of_cards := X_no_of_cards ;
1046 l_pull_seq_rec.supplier_id := X_supplier_id ;
1047 l_pull_seq_rec.supplier_site_id := X_supplier_site_id ;
1048 l_pull_seq_rec.source_organization_id := X_sourcing_org_id ;
1049 l_pull_seq_rec.source_subinventory := X_sourcing_subinv ;
1050 l_pull_seq_rec.source_locator_id := X_sourcing_loc_id ;
1051 l_pull_seq_rec.wip_line_id := X_wip_line_id ;
1052 l_pull_seq_rec.release_kanban_flag := p_release_kanban_flag;
1053
1054 if INV_kanban_PVT.Ok_To_Create_Kanban_Cards(X_Pull_sequence_id ) then
1055 INV_kanban_PVT.create_kanban_cards( l_return_status,
1056 l_kanban_card_ids,
1057 l_pull_seq_rec,
1058 X_STATUS );
1059
1060 if l_return_status = FND_API.G_RET_STS_ERROR then
1061 Raise FND_API.G_EXC_ERROR;
1062 end if;
1063
1064 if l_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
1065 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
1066 end if;
1067
1068 if X_PRINT_KANBAN_CARD = 1 then
1069 if X_REPORT_ID IS NULL then
1070 select MTL_KANBAN_CARD_PRINT_TEMP_S.nextval
1071 into l_report_id from DUAL;
1072 X_REPORT_ID := l_report_id;
1073 end if;
1074 for l_card_count in 1..l_kanban_card_Ids.count
1075 LOOP
1076 insert into MTL_KANBAN_CARD_PRINT_TEMP
1077 (REPORT_ID,KANBAN_CARD_ID)
1078 values ( x_report_id, l_kanban_card_Ids(l_card_count) );
1079 END LOOP;
1080 end if;
1081
1082 if X_loc_id is not NULL then
1083
1084 kb_get_conc_segments(X_org_id, X_loc_id, l_loc_name);
1085 /*
1086 Select concatenated_segments
1087 into l_loc_name
1088 from mtl_item_locations_kfv
1089 where inventory_location_id = X_loc_id and
1090 organization_id = X_org_id;
1091 */
1092 end if;
1093
1094 if X_item_id is not NULL then
1095 Select concatenated_segments
1096 into l_item_name
1097 from mtl_system_items_kfv
1098 where inventory_item_id = X_item_id and
1099 organization_id = X_org_id;
1100 end if;
1101
1102 Select organization_code
1103 into l_org_code
1104 from mtl_parameters
1105 where ORGANIZATION_ID = X_org_id;
1106
1107 FND_MESSAGE.set_name('INV','INV_KANBAN_CARDS_CREATED');
1108 FND_MESSAGE.SET_TOKEN('ORG_CODE',l_org_code);
1109 FND_MESSAGE.SET_TOKEN('ITEM_NAME',l_item_name);
1110 FND_MESSAGE.SET_TOKEN('SUB_CODE',X_subinv);
1111 FND_MESSAGE.SET_TOKEN('LOCATOR_NAME',l_loc_name);
1112 PUT_LINE( fnd_message.get );
1113 else
1114 PUT_LINE( fnd_message.get );
1115 end if;
1116 Exception
1117 when FND_API.G_EXC_ERROR then
1118 print_error;
1119 when FND_API.G_EXC_UNEXPECTED_ERROR then
1120 print_error;
1121 when others then
1122 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME , 'card_check_and_create');
1123 print_error;
1124 END card_check_and_create;
1125
1126
1127 PROCEDURE query_range_loc( X_org_id IN Number,
1128 X_locator_lo IN VARCHAR2,
1129 X_locator_hi IN VARCHAR2,
1130 X_where OUT NOCOPY VARCHAR2 ) IS
1131 v_num NUMBER;
1132 v_delim varchar2(1);
1133 v_append varchar2(1000) := NULL;
1134 v_where varchar2(2000) := NULL;
1135 v_cnt Number := 0;
1136 v_ctr Number;
1137 v_flex_num Number := Null;
1138 v_proj_ref_enabled Number := Null;
1139 Rec Boolean := FALSE;
1140 comma varchar2(1) := '''';
1141
1142 seg_low fnd_flex_ext.SegmentArray;
1143 seg_high fnd_flex_ext.SegmentArray;
1144
1145 Cursor CUR1(v_struct_num Number) is
1146 select a.application_column_name, b.format_type
1147 from FND_ID_FLEX_SEGMENTS_VL a, FND_FLEX_VALUE_SETS b
1148 where a.application_id = 401 and
1149 a.id_flex_code = 'MTLL' and
1153 a.flex_value_set_id = b.flex_value_set_id
1150 a.id_flex_num = v_struct_num and
1151 a.enabled_flag = 'Y' and
1152 a.display_flag = 'Y' and
1154 order by a.segment_num;
1155
1156
1157 --CUR2 CUR1%ROWTYPE;
1158
1159 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1160 BEGIN
1161
1162 Select id_flex_num into v_flex_num
1163 from fnd_id_flex_structures
1164 where id_flex_code = 'MTLL';
1165
1166 Select project_reference_enabled into v_proj_ref_enabled
1167 from MTL_PARAMETERS
1168 where organization_id = X_org_id;
1169
1170 v_delim := fnd_flex_ext.get_delimiter('INV','MTLL', v_flex_num);
1171
1172 v_num := fnd_flex_ext.breakup_segments( X_locator_lo, v_delim, seg_low);
1173 v_num := fnd_flex_ext.breakup_segments( X_locator_hi, v_delim, seg_high);
1174
1175
1176 -- bug 4662395 set the profile mfg_organization_id so
1177 -- the call to MTL_PROJECT_V will return data.
1178
1179 FND_PROFILE.put('MFG_ORGANIZATION_ID',X_org_id);
1180
1181 -- Building the where clause
1182
1183 for CUR2 in CUR1(v_flex_num) Loop
1184 Rec := TRUE;
1185 v_cnt := v_cnt + 1;
1186 if ( (seg_low(v_cnt) IS NOT NULL) OR (seg_high(v_cnt) IS NOT NULL) ) then
1187 if v_where is not null then
1188 v_append := v_where||' and ';
1189 else
1190 v_append := NULL;
1191 end if;
1192
1193 if v_proj_ref_enabled = 1 AND
1194 ( CUR2.APPLICATION_COLUMN_NAME = 'SEGMENT19' OR
1195 CUR2.APPLICATION_COLUMN_NAME = 'SEGMENT20') then
1196 if CUR2.APPLICATION_COLUMN_NAME = 'SEGMENT19' then
1197 v_where := v_append ||' to_number(SEGMENT19) in '||
1198 '(select project_id from mtl_project_v where '||
1199 'project_name >= nvl('||comma||seg_low(v_cnt)||comma||
1200 ', project_name) '|| 'and project_name <= nvl('||
1201 comma||seg_high(v_cnt)||comma||', project_name))';
1202 elsif CUR2.APPLICATION_COLUMN_NAME = 'SEGMENT20' then
1203 v_where := v_append ||' to_number(SEGMENT20) in '||
1204 '(select task_id from mtl_task_v where '||
1205 'project_id = nvl(to_number(SEGMENT19),project_id) and '||
1206 'project_name >= nvl('||comma||seg_low(v_cnt)||comma||
1207 ', project_name) '||
1208 'and project_name <= nvl('||comma||seg_high(v_cnt)||comma||
1209 ',project_name))';
1210 end if;
1211 else
1212
1213 if seg_low(v_cnt) is not null then
1214 if CUR2.FORMAT_TYPE = 'N' then
1215 v_where := v_append||' to_number('||CUR2.APPLICATION_COLUMN_NAME||
1216 ')'||' >= '||seg_low(v_cnt);
1217 else
1218 v_where := v_append||' '||CUR2.APPLICATION_COLUMN_NAME||
1219 ' >= '||comma||seg_low(v_cnt)||comma;
1220 end if;
1221 end if;
1222 if v_where is not null then
1223 v_append := v_where||' and ';
1224 else
1225 v_append := null;
1226 end if;
1227 if seg_high(v_cnt) is not null then
1228 if CUR2.FORMAT_TYPE = 'N' then
1229 v_where := v_append||' to_number('||CUR2.APPLICATION_COLUMN_NAME||
1230 ')'||' <= '||seg_high(v_cnt);
1231 else
1232 v_where := v_append||' '||CUR2.APPLICATION_COLUMN_NAME||
1233 ' <= '||comma||seg_high(v_cnt)||comma;
1234 end if;
1235 end if;
1236 end if;
1237 end if;
1238 end Loop;
1239 X_where := v_where;
1240
1241 if NOT Rec then
1242 FND_MESSAGE.set_name('INV','INV_NO_LOCATOR_SEGMENTS_FOUND');
1243 PUT_LINE( fnd_message.get );
1244 current_error_code := to_char(SQLCODE);
1245 end if;
1246 Exception
1247 when others then
1248 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME , 'query_range_loc');
1249 print_error;
1250 end query_range_loc;
1251
1252
1253
1254 PROCEDURE query_range_itm( X_item_lo IN VARCHAR2,
1255 X_item_hi IN VARCHAR2,
1256 X_where OUT NOCOPY VARCHAR2 ) IS
1257 v_num NUMBER;
1258 v_delim varchar2(1);
1259 v_append varchar2(1000) := NULL;
1260 v_where varchar2(2000) := NULL;
1261 v_cnt Number := 0;
1262 v_ctr Number;
1263 v_flex_num Number := Null;
1264 Rec Boolean := FALSE;
1265 comma varchar2(1) := '''';
1266
1267 seg_low fnd_flex_ext.SegmentArray;
1268 seg_high fnd_flex_ext.SegmentArray;
1269
1270
1271
1272 Cursor CUR1(v_struct_num Number) is
1273 select a.application_column_name, b.format_type
1274 from FND_ID_FLEX_SEGMENTS_VL a, FND_FLEX_VALUE_SETS b
1275 where a.application_id = 401 and
1276 a.id_flex_code = 'MSTK' and
1277 a.id_flex_num = v_struct_num and
1278 a.enabled_flag = 'Y' and
1279 a.display_flag = 'Y' and
1280 a.flex_value_set_id = b.flex_value_set_id
1281 order by a.segment_num;
1282
1283 -- CUR2 CUR1%ROWTYPE;
1284
1288 Select id_flex_num into v_flex_num
1285 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1286 BEGIN
1287
1289 from fnd_id_flex_structures
1290 where id_flex_code = 'MSTK';
1291
1292 v_delim := fnd_flex_ext.get_delimiter('INV','MSTK',v_flex_num);
1293 v_num := fnd_flex_ext.breakup_segments( X_item_lo, v_delim, seg_low);
1294 v_num := fnd_flex_ext.breakup_segments( X_item_hi, v_delim, seg_high);
1295
1296 -- Building the where clause
1297
1298 for CUR2 in CUR1(v_flex_num) Loop
1299 Rec := TRUE;
1300 v_cnt := v_cnt + 1;
1301 if ( (seg_low(v_cnt) IS NOT NULL) OR (seg_high(v_cnt) IS NOT NULL) ) then
1302 if v_where is not null then
1303 v_append := v_where||' and ';
1304 else
1305 v_append := NULL;
1306 end if;
1307 if seg_low(v_cnt) is not null then
1308 if CUR2.FORMAT_TYPE = 'N' then
1309 v_where := v_append||' to_number('||CUR2.APPLICATION_COLUMN_NAME||
1310 ')'||' >= '||seg_low(v_cnt);
1311 else
1312 v_where := v_append||' '||CUR2.APPLICATION_COLUMN_NAME||
1313 ' >= '||comma||seg_low(v_cnt)||comma;
1314 end if;
1315 end if;
1316 if v_where is not null then
1317 v_append := v_where||' and ';
1318 else
1319 v_append := null;
1320 end if;
1321 if seg_high(v_cnt) is not null then
1322 if CUR2.FORMAT_TYPE = 'N' then
1323 v_where := v_append||' to_number('||CUR2.APPLICATION_COLUMN_NAME||
1324 ')'||' <= '||seg_high(v_cnt);
1325 else
1326 v_where := v_append||' '||CUR2.APPLICATION_COLUMN_NAME||
1327 ' <= '||comma||seg_high(v_cnt)||comma;
1328 end if;
1329 end if;
1330 end if;
1331 end Loop;
1332 X_where := v_where;
1333
1334 if NOT Rec then
1335 FND_MESSAGE.set_name('INV','INV_NO_ITEM_SEGMENTS_FOUND');
1336 PUT_LINE( fnd_message.get );
1337 current_error_code := to_char(SQLCODE);
1338 end if;
1339 Exception
1340 when others then
1341 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME , 'query_range_itm');
1342 print_error;
1343 end query_range_itm;
1344
1345
1346 PROCEDURE print_kanban_report( X_REPORT_ID IN NUMBER ) IS
1347 v_req_id NUMBER;
1348 v_sort_by NUMBER := 3;
1349 v_call_from NUMBER := 2;
1350
1351 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1352 BEGIN
1353 v_req_id := fnd_request.submit_request( 'INV',
1354 'INVKBCPR',
1355 NULL,
1356 NULL,
1357 FALSE,
1358 NULL, /* p_org_id */
1359 NULL, /* p_date_created_low */
1360 NULL, /* p_date_created_high */
1361 NULL, /* p_kanban_card_number_low */
1362 NULL, /* p_kanban_card_number_high */
1363 NULL, /* p_item_low */
1364 NULL, /* p_item_high */
1365 NULL, /* p_subinv */
1366 NULL, /* p_locator_low */
1367 NULL, /* p_locator_high */
1368 NULL, /* p_source_type */
1369 NULL, /* p_kanban_card_type */
1370 NULL, /* p_supplier */
1371 NULL, /* p_supplier_site */
1372 NULL, /* p_source_org_id */
1373 NULL, /* p_source_subinv */
1374 NULL, /* p_source_loc_id */
1375 v_sort_by, /* p_sort_by */
1376 v_call_from, /* p_call_from */
1377 NULL, /* p_kanban_card_id */
1378 X_REPORT_ID /* p_report_id */
1379 );
1380
1381 PUT_LINE( fnd_message.get );
1382 if v_req_id = 0 then
1383 delete from MTL_KANBAN_CARD_PRINT_TEMP
1384 where
1385 report_id = X_REPORT_ID;
1386 end if;
1387 END print_kanban_report;
1388
1389
1390 Procedure Print_Error IS
1391 l_count number;
1392 l_msg varchar2(2000);
1393 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1394 begin
1395 Fnd_msg_pub.Count_And_get(p_count => l_count,
1396 p_data => l_msg,
1397 p_encoded => 'F');
1398 if l_count = 0
1399 then
1400 null;
1401 elsif l_count = 1
1402 then
1403 PUT_LINE( l_msg);
1404 else
1405 For I in 1..l_count
1406 loop
1407 l_msg := fnd_msg_pub.get(I,'F');
1408 PUT_LINE( l_msg);
1409 end loop;
1410 PUT_LINE(' ');
1411 end if;
1412 FND_MSG_PUB.initialize;
1413 Exception
1414 when others then
1415 PUT_LINE( SQLERRM );
1416 End Print_Error;
1417
1418
1422 v_loc_str varchar2(2000) := null;
1419 Procedure kb_get_conc_segments( X_org_id IN Number,
1420 X_loc_id IN Number,
1421 X_conc_segs OUT NOCOPY varchar2 ) is
1423 v_proj_name varchar2(50) := null;
1424 v_task_name varchar2(50) := null;
1425 v_append varchar2(1000) := null;
1426 v_parse_str varchar2(3000) := null;
1427 v_num NUMBER;
1428 v_cnt NUMBER := 0;
1429 v_proj_ref_enabled Number := Null;
1430 v_flex_code varchar2(5) := 'MTLL';
1431 v_flex_num Number;
1432 v_seg19_f Boolean := False;
1433 v_seg20_f Boolean := False;
1434 v_delim varchar2(1) := Null;
1435 dsql_cur Number;
1436 rows_processed Number;
1437 str1 varchar2(15) := NULL;
1438 d_data_str varchar2(1000) := null;
1439
1440 Cursor CUR1(flex_code varchar2) is
1441 select a.application_column_name
1442 from FND_ID_FLEX_SEGMENTS_VL a
1443 where a.application_id = 401 and
1444 a.id_flex_code = flex_code and
1445 a.id_flex_num = (select id_flex_num
1446 from fnd_id_flex_structures
1447 where id_flex_code = flex_code) and
1448 a.enabled_flag = 'Y' and
1449 a.display_flag = 'Y'
1450 order by a.segment_num;
1451
1452
1453 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1454 BEGIN
1455
1456 Select id_flex_num into v_flex_num
1457 from fnd_id_flex_structures
1458 where id_flex_code = 'MTLL';
1459
1460 Select project_reference_enabled into v_proj_ref_enabled
1461 from MTL_PARAMETERS
1462 where organization_id = X_org_id;
1463
1464 -- bug 4662395 set the profile mfg_organization_id so
1465 -- the call to MTL_PROJECT_V will return data.
1466
1467 FND_PROFILE.put('MFG_ORGANIZATION_ID',X_org_id);
1468
1469 v_delim := fnd_flex_ext.get_delimiter('INV',v_flex_code, v_flex_num);
1470
1471 str1 := '||'''||v_delim||'''||';
1472
1473 for CUR2 in CUR1(v_flex_code) Loop
1474 if v_proj_ref_enabled = 1 AND
1475 ( CUR2.APPLICATION_COLUMN_NAME = 'SEGMENT19' OR
1476 CUR2.APPLICATION_COLUMN_NAME = 'SEGMENT20') then
1477 if CUR2.APPLICATION_COLUMN_NAME = 'SEGMENT19' then
1478 begin
1479 v_seg19_f := True;
1480 select distinct project_name into v_proj_name
1481 from mtl_project_v where
1482 project_id = ( select nvl(to_number(SEGMENT19), 0)
1483 from mtl_item_locations
1484 where inventory_location_id = X_loc_id and
1485 organization_id = X_org_id );
1486 exception
1487 when others then
1488 v_proj_name := null;
1489 end;
1490 elsif CUR2.APPLICATION_COLUMN_NAME = 'SEGMENT20' then
1491 begin
1492 v_seg20_f := True;
1493 select distinct A.task_name into v_task_name
1494 from mtl_task_v A where
1495 A.task_id = (select nvl(to_number(SEGMENT20), 0)
1496 from mtl_item_locations
1497 where inventory_location_id = X_loc_id and
1498 organization_id = X_org_id ) AND
1499 A.project_id = (select nvl(to_number(SEGMENT19), A.project_id)
1500 from mtl_item_locations
1501 where inventory_location_id = X_loc_id and
1502 organization_id = X_org_id );
1503 exception
1504 when others then
1505 v_task_name := null;
1506 end;
1507 end if;
1508 end if;
1509 end Loop;
1510
1511 for CUR2 in CUR1(v_flex_code) Loop
1512 if v_loc_str is not null then
1513 v_append := v_loc_str||str1;
1514 else
1515 v_append := NULL;
1516 end if;
1517 if ( CUR2.APPLICATION_COLUMN_NAME <> 'SEGMENT19' AND
1518 CUR2.APPLICATION_COLUMN_NAME <> 'SEGMENT20') then
1519 v_loc_str := v_append||CUR2.APPLICATION_COLUMN_NAME;
1520 end if;
1521 end Loop;
1522
1523 if v_loc_str is not null then
1524 v_parse_str := 'select '||v_loc_str||
1525 ' from mtl_item_locations where inventory_location_id = :loc_id '||
1526 ' and organization_id = :org_id';
1527
1528 dsql_cur := dbms_sql.open_cursor;
1529 dbms_sql.parse(dsql_cur,v_parse_str,dbms_sql.native);
1530 dbms_sql.define_column(dsql_cur,1,d_data_str,800);
1531
1532 dbms_sql.bind_variable(dsql_cur,'loc_id',X_loc_id);
1533 dbms_sql.bind_variable(dsql_cur,'org_id',X_org_id);
1534 rows_processed := dbms_sql.execute(dsql_cur);
1535
1536 Loop
1537 if ( dbms_sql.fetch_rows(dsql_cur) > 0 ) then
1538 dbms_sql.column_value(dsql_cur,1,d_data_str);
1539 else
1540 -- No more rows in cursor
1541 dbms_sql.close_cursor(dsql_cur);
1542 Exit;
1543 end if;
1544 End loop;
1545 if dbms_sql.is_open(dsql_cur) then
1546 dbms_sql.close_cursor(dsql_cur);
1547 end if;
1548 end if;
1549
1550 if v_seg19_f and v_seg20_f then
1551 X_conc_segs := d_data_str||v_delim||v_proj_name||v_delim||v_task_name;
1552 elsif v_seg19_f then
1553 X_conc_segs := d_data_str||v_delim||v_proj_name;
1554 elsif v_seg20_f then
1555 X_conc_segs := d_data_str||v_delim||v_task_name;
1556 else
1557 X_conc_segs := d_data_str;
1558 end if;
1559 Exception
1560 when others then
1561 X_conc_segs := NULL;
1562 END kb_get_conc_segments;
1563
1564
1565 END INVKBCGN;