1 PACKAGE BODY INVPUOPI AS
2 /* $Header: INVPUP1B.pls 120.9.12020000.5 2013/01/23 08:31:14 lmai ship $ */
3
4 l_application_id number(10) := 401;
5 l_id_flex_code varchar2(4) := 'MSTK';
6 l_enabled_flag varchar2(1) := 'Y';
7 l_id_flex_num number(15) := 101;
8
9 /*
10 ** Parse the input item number, assign segment values
11 ** into SEGMENT columns in MTL_SYSTEM_ITEMS_INTERFACE
12 */
13 ---Start: Bug Fix 3051653
14 G_SEGMENT_DELIMITER VARCHAR2(10) := NULL;
15 G_SEGMENTS_INUSE NUMBER(10) := NULL;
16 G_SEGMENT_STRING VARCHAR2(500) := NULL;
17 G_DYNAMIC_UPDATE VARCHAR2(1000):= NULL;
18 --Start Bug: 4654433
19 G_NUM_OF_SEGMENTS NUMBER := NULL;
20 TYPE G_SEGMENTS_USED_TYPE IS TABLE OF NUMBER
21 INDEX BY PLS_INTEGER;
22 G_SEGMENTS_USED G_SEGMENTS_USED_TYPE;
23 G_SEGMENT_NUMS_USED G_SEGMENTS_USED_TYPE;
24 G_MIN_SEGMENT FND_ID_FLEX_SEGMENTS.application_column_name%TYPE;
25 G_MIN_SEG_NUM NUMBER;
26 G_CHECK_SEG_NUM NUMBER;
27 --End Bug: 4654433
28
29 FUNCTION mtl_pr_parse_item_segments
30 (p_row_id in rowid
31 ,item_number out NOCOPY VARCHAR2
32 ,item_id out NOCOPY NUMBER
33 ,err_text out NOCOPY varchar2
34 ) RETURN INTEGER IS
35
36 l_segment_name VARCHAR2(500);
37
38 BEGIN
39
40
41 IF G_SEGMENT_STRING IS NULL THEN
42 FOR i in G_SEGMENT_NUMS_USED.FIRST..G_SEGMENT_NUMS_USED.LAST LOOP
43 BEGIN
44 SELECT FS.application_column_name
45 INTO l_segment_name
46 FROM FND_ID_FLEX_SEGMENTS FS
47 WHERE FS.SEGMENT_NUM = G_SEGMENT_NUMS_USED(i)
48 AND FS.ID_FLEX_CODE = l_id_flex_code
49 AND FS.ID_FLEX_NUM = l_id_flex_num
50 AND FS.ENABLED_FLAG = l_enabled_flag
51 AND FS.APPLICATION_ID = l_application_id;
52 EXCEPTION
53 WHEN NO_DATA_FOUND THEN
54 l_segment_name := NULL;
55 WHEN OTHERS THEN
56 raise_application_error(-20001, SQLERRM);
57 END;
58
59 IF l_segment_name IS NOT NULL THEN
60 IF G_SEGMENT_STRING IS NULL THEN
61 G_SEGMENT_STRING := l_segment_name;
62 ELSE
63 G_SEGMENT_STRING := G_SEGMENT_STRING||'||'''||G_SEGMENT_DELIMITER||'''||'||l_segment_name;
64 END IF;
65 END IF;
66 END LOOP;
67 END IF;
68
69
70 IF G_DYNAMIC_UPDATE IS NULL THEN
71
72 G_DYNAMIC_UPDATE := ' UPDATE mtl_system_items_interface'||
73 ' SET item_number = '||G_SEGMENT_STRING||
74 ' WHERE ROWID = :p_row_id '||
75 ' RETURNING inventory_item_id, item_number INTO :item_id, :item_number';
76 END IF;
77
78 EXECUTE IMMEDIATE G_DYNAMIC_UPDATE
79 USING p_row_id, OUT item_id, OUT item_number;
80
81
82 RETURN 0;
83
84 EXCEPTION
85 WHEN OTHERS THEN
86 err_text := substr('INVPUOPI.parse_item:' || SQLERRM ,1, 240);
87 return(SQLCODE);
88 END mtl_pr_parse_item_segments;
89 ---End: Bug Fix 3051653
90
91 --Start :New overloaded procedure mtl_pr_parse_item_number
92 PROCEDURE mtl_pr_parse_item_number(
93 p_item_number IN VARCHAR2
94 ,p_segment1 OUT NOCOPY VARCHAR2
95 ,p_segment2 OUT NOCOPY VARCHAR2
96 ,p_segment3 OUT NOCOPY VARCHAR2
97 ,p_segment4 OUT NOCOPY VARCHAR2
98 ,p_segment5 OUT NOCOPY VARCHAR2
102 ,p_segment9 OUT NOCOPY VARCHAR2
99 ,p_segment6 OUT NOCOPY VARCHAR2
100 ,p_segment7 OUT NOCOPY VARCHAR2
101 ,p_segment8 OUT NOCOPY VARCHAR2
103 ,p_segment10 OUT NOCOPY VARCHAR2
104 ,p_segment11 OUT NOCOPY VARCHAR2
105 ,p_segment12 OUT NOCOPY VARCHAR2
106 ,p_segment13 OUT NOCOPY VARCHAR2
107 ,p_segment14 OUT NOCOPY VARCHAR2
108 ,p_segment15 OUT NOCOPY VARCHAR2
109 ,p_segment16 OUT NOCOPY VARCHAR2
110 ,p_segment17 OUT NOCOPY VARCHAR2
111 ,p_segment18 OUT NOCOPY VARCHAR2
112 ,p_segment19 OUT NOCOPY VARCHAR2
113 ,p_segment20 OUT NOCOPY VARCHAR2
114 ,x_err_text OUT NOCOPY VARCHAR2) IS
115
116 type segvalueType is table of varchar2(40) index by binary_integer;
117 type tmpvalueType is table of varchar2(40) index by binary_integer;
118 seg_value segvalueType;
119 tmp_value tmpvalueType;
120 segment_name varchar2(30);
121 segment_num varchar2(30);
122 return_status number;
123 pos number;
124 ind number;
125 l_count_slash number;
126
127 BEGIN
128
129
130
131 x_err_text := NULL;
132
133 for n in 1..20 loop
134 seg_value(n) := NULL;
135 tmp_value(n) := NULL;
136 end loop;
137
138 return_status := 1;
139 pos := 1;
140 ind := 1;
141 while (return_status > 0 AND ind < G_SEGMENTS_INUSE) loop
142 return_status := INSTR(p_item_number,G_SEGMENT_DELIMITER,pos);
143
144 if (return_status > 0) then
145 tmp_value(ind) := substr(p_item_number,pos,return_status - pos);
146 pos := return_status + length(G_SEGMENT_DELIMITER);
147 ind := ind +1;
148 end if;
149 end loop;
150 tmp_value(ind) := substr(p_item_number,pos);
151
152 --Start :3632767 Code to suport \. in item number
153 --tmp_value contains values from item number seperated at deliminator
154 --Ex: Item Number : A.B then tmp_value(1)=A and tmp_value(2)=B this seperation is done by above code.
155 --We need to check if each string in tmp_value ends with odd number of \'s then
156 --append this with value with next row and move all the remaining rows by one up.
157 --Ex :tmp_value(1) = A\\\ tmp_value(2) = B tmp_value(3) = C
158 --Then tmp_value(1) = A\\\.B tmp_value(2) = C tmp_value(3) = tmp_value(4)..and so on.
159
160 FOR i IN 1..(tmp_value.COUNT-1) LOOP
161 pos := i+1;
162 WHILE tmp_value(i) IS NOT NULL
163 AND tmp_value(pos) IS NOT NULL
164 AND (INSTR(tmp_value(i),'\',-1) = LENGTH(tmp_value(i)))
165 LOOP
166
167 l_count_slash := 0;
168 FOR j IN REVERSE 1..LENGTH(tmp_value(i)) LOOP
169 IF SUBSTR(tmp_value(i),J,1) = '\' THEN
170 l_count_slash := l_count_slash + 1;
171 ELSE
172 EXIT;
173 END IF;
174 END LOOP;
175
176 IF MOD(l_count_slash,2) <>0 THEN
177 IF G_SEGMENTS_INUSE = 1 THEN
178 tmp_value(i) := tmp_value(i) ||G_SEGMENT_DELIMITER|| tmp_value(pos);
179 ELSE
180 tmp_value(i) := SUBSTR(tmp_value(i),1,LENGTH(tmp_value(i))-1) ||G_SEGMENT_DELIMITER|| tmp_value(pos);
181 END IF;
182 FOR j in (i+1)..tmp_value.COUNT-1 LOOP
183 tmp_value(j) := tmp_value(j+1);
184 END LOOP;
185 ELSE
186 EXIT;
187 END IF; --MOD(l_count_slash,2) <>0
188
189 END LOOP;
190 END LOOP;
191
192 --Remove the escape character \
193 FOR i IN 1..tmp_value.COUNT LOOP
194 tmp_value(i) := REPLACE(tmp_value(i),'\\','\');
195 END LOOP;
196 --End :3632767 Code to suport \. in item number
197 --Bug: 4654433
198 pos := 1 ;
199 FOR n in G_SEGMENTS_USED.FIRST..G_SEGMENTS_USED.LAST LOOP
200 seg_value(G_SEGMENTS_USED(n)) := tmp_value(pos);
201 pos := pos + 1 ;
202 END LOOP;
203
204 --Start :3632767 Code to suport \. in item number
205 -- Bug: 5160315 Changing value of ind so that remaining segments are
206 -- stored in last enabled segment
207 ind := G_SEGMENTS_USED(G_SEGMENTS_USED.LAST);
208 FOR i IN pos ..tmp_value.count LOOP
209 IF tmp_value(i) IS NOT NULL THEN
210 seg_value(ind) := seg_value(ind) ||G_SEGMENT_DELIMITER||tmp_value(i);
211 END IF;
212 END LOOP;
213 --End :3632767 Code to suport \. in item number
214
215 p_segment1 := seg_value(1);
216 p_segment2 := seg_value(2);
217 p_segment3 := seg_value(3);
218 p_segment4 := seg_value(4);
219 p_segment5 := seg_value(5);
220 p_segment6 := seg_value(6);
221 p_segment7 := seg_value(7);
222 p_segment8 := seg_value(8);
223 p_segment9 := seg_value(9);
224 p_segment10 := seg_value(10);
225 p_segment11 := seg_value(11);
226 p_segment12 := seg_value(12);
227 p_segment13 := seg_value(13);
228 p_segment14 := seg_value(14);
229 p_segment15 := seg_value(15);
230 p_segment16 := seg_value(16);
231 p_segment17 := seg_value(17);
232 p_segment18 := seg_value(18);
233 p_segment19 := seg_value(19);
234 p_segment20 := seg_value(20);
235 x_err_text := NULL;
236 EXCEPTION
237 WHEN OTHERS THEN
238 x_err_text := substr('INVPUOPI.parse_item:' || SQLERRM ,1, 240);
239 END mtl_pr_parse_item_number;
240 --End :New overloaded procedure mtl_pr_parse_item_number
241
242 FUNCTION mtl_pr_parse_item_number
243 (
247 org_id number,
244 item_number varchar2,
245 item_id number,
246 trans_id number,
248 err_text out NOCOPY varchar2,
249 p_rowid rowid
250 )
251 RETURN INTEGER
252 IS
253 type segvalueType is table of varchar2(40)
254 index by binary_integer;
255 type tmpvalueType is table of varchar2(40)
256 index by binary_integer;
257 seg_value segvalueType;
258 tmp_value tmpvalueType;
259 delimiter varchar2(10);
260 segvalue_tmp varchar2(40);
261 segment_name varchar2(30);
262 segment_num varchar2(30);
263 max_segment number;
264 return_status number;
265 pos number;
266 ind number;
267 l_count_slash number;
268 BEGIN
269 err_text := NULL;
270
271 /*
272 ** initialize table values
273 */
274 for n in 1..20 loop
275 seg_value(n) := NULL;
276 tmp_value(n) := NULL;
277 end loop;
278
279 /*
280 ** get the delimeter and max_segment
281 */
282 --Bug: 4654433
283 delimiter := G_SEGMENT_DELIMITER;
284 max_segment := G_SEGMENTS_INUSE;
285 /*
286 ** seperate input name into segments
287 */
288 return_status := 1;
289 pos := 1;
290 ind := 1;
291 while (return_status > 0 AND ind < max_segment) loop
292 return_status := INSTR(item_number,delimiter,pos);
293 if (return_status > 0) then
294 tmp_value(ind) := substr(item_number,pos,
295 return_status - pos);
296 pos := return_status + length(delimiter);
297 ind := ind +1;
298 end if;
299
300 end loop;
301 tmp_value(ind) := substr(item_number,pos);
302
303 --Start :3632767 Code to suport \. in item number
304 --tmp_value contains values from item number seperated at deliminator
305 --Ex: Item Number : A.B then tmp_value(1)=A and tmp_value(2)=B this seperation is done by above code.
306 --We need to check if each string in tmp_value ends with odd number of \'s then
307 --append this with value with next row and move all the remaining rows by one up.
308 --Ex :tmp_value(1) = A\\\ tmp_value(2) = B tmp_value(3) = C
309 --Then tmp_value(1) = A\\\.B tmp_value(2) = C tmp_value(3) = tmp_value(4)..and so on.
310
311 FOR i IN 1..(tmp_value.COUNT-1) LOOP
312 pos := i+1;
313
314 WHILE tmp_value(i) IS NOT NULL
315 AND tmp_value(pos) IS NOT NULL
316 AND (INSTR(tmp_value(i),'\',-1) = LENGTH(tmp_value(i)))
317 LOOP
318
319 l_count_slash := 0;
320 FOR j IN REVERSE 1..LENGTH(tmp_value(i)) LOOP
321 IF SUBSTR(tmp_value(i),J,1) = '\' THEN
322 l_count_slash := l_count_slash + 1;
323 ELSE
324 EXIT;
325 END IF;
326 END LOOP;
327
328 IF MOD(l_count_slash,2) <>0 THEN
329 IF G_SEGMENTS_INUSE = 1 THEN
330 tmp_value(i) := tmp_value(i) ||G_SEGMENT_DELIMITER|| tmp_value(pos);
331 ELSE
332 tmp_value(i) := SUBSTR(tmp_value(i),1,LENGTH(tmp_value(i))-1) ||G_SEGMENT_DELIMITER|| tmp_value(pos);
333 END IF;
334 FOR j in (i+1)..tmp_value.COUNT-1 LOOP
335 tmp_value(j) := tmp_value(j+1);
336 END LOOP;
337 ELSE
338 EXIT;
339 END IF;
340 END LOOP;
341 END LOOP;
342
343 FOR i IN 1..tmp_value.COUNT LOOP
344 tmp_value(i) := REPLACE(tmp_value(i),'\\','\');
345 END LOOP;
346 --End :3632767 Code to suport \. in item number
347
348 /*
349 ** assign the seperated segments into proper SEGMENT columns
350 ** commented below seg_value(ind) := tmp_value(n);
351 ** If 1st enabled segment is say SEGMENT10 with SEGMENT NUM 15
352 ** and item number like '06231'
353 ** Below logic would fail , as the while loop above will store
354 ** '06231' in tmp_value(1) and above commented st. would look for
355 ** a value in tmp_value(15).
356 */
357 --Bug: 4654433
358 pos := 1 ;
359 for n in G_SEGMENTS_USED.FIRST..G_SEGMENTS_USED.LAST loop
360 seg_value(G_SEGMENTS_USED(n)) := tmp_value(pos);
361 pos := pos + 1 ;
362 end loop;
363
364 --Start :3632767 Code to suport \. in item number
365 -- Bug: 5160315 Changing value of ind so that remaining segments are
366 -- stored in last enabled segment
367 ind := G_SEGMENTS_USED(G_SEGMENTS_USED.LAST);
368 FOR i IN pos ..tmp_value.count LOOP
369 IF tmp_value(i) IS NOT NULL THEN
370 seg_value(ind) := seg_value(ind) ||delimiter||tmp_value(i);
371 END IF;
372 END LOOP;
373 --End :3632767 Code to suport \. in item number
374
375 /*
376 ** update segment values in MTL_SYSTEM_ITEMS_INTERFACE
377 */
378 update MTL_SYSTEM_ITEMS_INTERFACE
379 set segment1 = DECODE(seg_value(1),NULL,segment1,seg_value(1)),
380 segment2 = DECODE(seg_value(2),NULL,segment2,seg_value(2)),
381 segment3 = DECODE(seg_value(3),NULL,segment3,seg_value(3)),
382 segment4 = DECODE(seg_value(4),NULL,segment4,seg_value(4)),
383 segment5 = DECODE(seg_value(5),NULL,segment5,seg_value(5)),
384 segment6 = DECODE(seg_value(6),NULL,segment6,seg_value(6)),
385 segment7 = DECODE(seg_value(7),NULL,segment7,seg_value(7)),
386 segment8 = DECODE(seg_value(8),NULL,segment8,seg_value(8)),
387 segment9 = DECODE(seg_value(9),NULL,segment9,seg_value(9)),
388 segment10 = DECODE(seg_value(10),NULL,segment10,seg_value(10)),
389 segment11 = DECODE(seg_value(11),NULL,segment11,seg_value(11)),
393 segment15 = DECODE(seg_value(15),NULL,segment15,seg_value(15)),
390 segment12 = DECODE(seg_value(12),NULL,segment12,seg_value(12)),
391 segment13 = DECODE(seg_value(13),NULL,segment13,seg_value(13)),
392 segment14 = DECODE(seg_value(14),NULL,segment14,seg_value(14)),
394 segment16 = DECODE(seg_value(16),NULL,segment16,seg_value(16)),
395 segment17 = DECODE(seg_value(17),NULL,segment17,seg_value(17)),
396 segment18 = DECODE(seg_value(18),NULL,segment18,seg_value(18)),
397 segment19 = DECODE(seg_value(19),NULL,segment19,seg_value(19)),
398 segment20 = DECODE(seg_value(20),NULL,segment20,seg_value(20))
399 Where rowid = p_rowid ;
400 /*08/18/97 where transaction_id = trans_id; */
401 /* and organization_id = org_id; */
402
403 /*SETID logic probably not needed here*/
404
405 RETURN (0);
406
407 EXCEPTION
408
409 WHEN OTHERS THEN
410 err_text := substr('INVPUOPI.parse_item:' || SQLERRM ,1, 240);
411 return(SQLCODE);
412
413 END mtl_pr_parse_item_number;
414
415 /*
416 ** Parse flex name with the flex code, return the flex id
417 */
418
419 FUNCTION mtl_pr_parse_flex_name
420 (
421 org_id number,
422 flex_code varchar2,
423 flex_name varchar2,
424 flex_id in out NOCOPY number,
425 set_id number,
426 err_text out NOCOPY varchar2,
427 structure_id number default -1 /*Fix for bug 8288281*/
428 )
429 RETURN INTEGER
430 IS
431 type segvalueType is table of varchar2(40)
432 index by binary_integer;
433 type tmpvalueType is table of varchar2(40)
434 index by binary_integer;
435 seg_value segvalueType;
436 tmp_value tmpvalueType;
437 delimiter varchar2(10);
438 segvalue_tmp varchar2(40);
439 segment_name varchar2(30);
440 segment_num varchar2(30);
441 max_segment number;
442 return_status number;
443 ret_code number;
444 statement_num number;
445 struct_id number;
446 pos number;
447 ind number;
448 l_count_slash number;
449
450 l_inv_debug_level NUMBER := INVPUTLI.get_debug_level; --Bug: 4667452
451 BEGIN
452
453 err_text := NULL;
454 statement_num := 1;
455
456 /* get the structure first */
457 if flex_code = 'MCAT' then
458 select structure_id
459 into struct_id
460 from mtl_category_sets_b
461 where category_set_id = set_id;
462 else
463 struct_id := 101;
464 end if;
465
466 /* Search for the item id at production item table */
467 if flex_code = 'MSTK' then
468 statement_num := 2;
469 ret_code := INVPUOPI.mtl_pr_trans_prod_item (
470 flex_name,
471 org_id,
472 flex_id,
473 err_text);
474 if (ret_code <= 0 ) then
475 --11/05/97 if (ret_code < 0 ) then
476 return(ret_code);
477 end if;
478 end if;
479
480 /* We are going to search the interface table now */
481 /* init table values */
482 for n in 1..20 loop
483 seg_value(n) := NULL;
484 tmp_value(n) := NULL;
485 end loop;
486
487 /* get the delimeter and max_segment */
488 --Bug: 4654433
489 /*Changes done for bug 8288281. Here the delimiter and the num of segments of the flexflield should be retrieved for the
490 flex code being passed and shouldnt be hard coded from System Items flexfield*/
491 IF structure_id = -1 THEN
492 statement_num := 3;
493 delimiter := G_SEGMENT_DELIMITER;
494
495 statement_num := 4;
496 max_segment := G_SEGMENTS_INUSE;
497 ELSE
498
499 statement_num := 3.1;
500
501 SELECT FT.concatenated_segment_delimiter
502 INTO delimiter
503 FROM fnd_id_flex_structures FT
504 WHERE FT.id_flex_code = flex_code
505 AND FT.APPLICATION_ID = l_application_id
506 AND FT.ID_FLEX_NUM = structure_id;
507
508 statement_num := 4.1;
509
510 SELECT max(FS.segment_num)
511 INTO max_segment
512 FROM FND_ID_FLEX_SEGMENTS FS
513 WHERE FS.APPLICATION_ID = l_application_id
514 AND FS.id_flex_code = flex_code
515 AND FS.ENABLED_FLAG = 'Y'
516 AND FS.id_flex_num = structure_id;
517
518 END IF;
519 /*End of changes*/
520
521 /* seperate input name into segments */
522 pos := 1;
523 ind := 1;
524 return_status := 1;
525 statement_num := 5;
526 while (return_status > 0 AND ind < max_segment) loop
527 return_status := INSTR(flex_name,delimiter,pos);
528 if (return_status > 0) then
529 tmp_value(ind) := substr(flex_name,pos,
530 return_status - pos);
531 pos := return_status + length(delimiter);
532 ind := ind +1;
533 end if;
534 end loop;
535 tmp_value(ind) := substr(flex_name,pos);
536
537 --Start :3632767 Code to suport \. in item number
538 --tmp_value contains values from item number seperated at deliminator
539 --Ex: Item Number : A.B then tmp_value(1)=A and tmp_value(2)=B this seperation is done by above code.
540 --We need to check if each string in tmp_value ends with odd number of \'s then
541 --append this with value with next row and move all the remaining rows by one up.
542 --Ex :tmp_value(1) = A\\\ tmp_value(2) = B tmp_value(3) = C
543 --Then tmp_value(1) = A\\\.B tmp_value(2) = C tmp_value(3) = tmp_value(4)..and so on.
544
545 IF flex_code = 'MSTK' THEN
546 FOR i IN 1..(tmp_value.COUNT-1) LOOP
547 pos := i+1;
548
549 WHILE tmp_value(i) IS NOT NULL
553
550 AND tmp_value(pos) IS NOT NULL
551 AND (INSTR(tmp_value(i),'\',-1) = LENGTH(tmp_value(i)))
552 LOOP
554 l_count_slash := 0;
555 FOR j IN REVERSE 1..LENGTH(tmp_value(i)) LOOP
556 IF SUBSTR(tmp_value(i),J,1) = '\' THEN
557 l_count_slash := l_count_slash + 1;
558 ELSE
559 EXIT;
560 END IF;
561 END LOOP;
562
563 IF MOD(l_count_slash,2) <>0 THEN
564 IF G_SEGMENTS_INUSE = 1 THEN
565 tmp_value(i) := tmp_value(i) ||G_SEGMENT_DELIMITER|| tmp_value(pos);
566 ELSE
567 tmp_value(i) := SUBSTR(tmp_value(i),1,LENGTH(tmp_value(i))-1) ||G_SEGMENT_DELIMITER|| tmp_value(pos);
568 END IF;
569 FOR j in (i+1)..tmp_value.COUNT-1 LOOP
570 tmp_value(j) := tmp_value(j+1);
571 END LOOP;
572 ELSE
573 EXIT;
574 END IF;
575 END LOOP;
576 END LOOP;
577
578 FOR i IN 1..tmp_value.COUNT LOOP
579 tmp_value(i) := REPLACE(tmp_value(i),'\\','\');
580 END LOOP;
581 END IF;
582 --End :3632767 Code to suport \. in item number
583
584 /* assign the seperated segments into proper SEGMENT columns */
585 --Bug: 4654433
586 statement_num := 6;
587 /* Bug 8613428 added If condition to reinitialize G_SEGMENTS_USED with category KFF values.
588 Earlier it had system Items KFF values. */
589 IF structure_id = -1 THEN
590 pos := 1 ;
591 for n in G_SEGMENTS_USED.FIRST..G_SEGMENTS_USED.LAST loop
592 seg_value(G_SEGMENTS_USED(n)) := tmp_value(pos);
593 pos := pos + 1;
594 end loop;
595 ELSE
596 SELECT to_number(substr(application_column_name,8)), segment_num BULK COLLECT INTO
597 G_SEGMENTS_USED, G_SEGMENT_NUMS_USED
598 FROM FND_ID_FLEX_SEGMENTS
599 WHERE APPLICATION_ID = l_application_id
600 AND id_flex_code = flex_code
601 AND ENABLED_FLAG = 'Y'
602 AND id_flex_num = structure_id
603 ORDER BY segment_num;
604
605 pos := 1 ;
606 for n in G_SEGMENTS_USED.FIRST..G_SEGMENTS_USED.LAST loop
607 seg_value(G_SEGMENTS_USED(n)) := tmp_value(pos);
608 pos := pos + 1;
609 end loop;
610 END IF;
611
612 --Start :3632767 Code to suport \. in item number
613 IF flex_code = 'MSTK' THEN
614 FOR i IN pos ..tmp_value.count LOOP
615 IF tmp_value(i) IS NOT NULL THEN
616 seg_value(ind) := seg_value(ind) ||delimiter||tmp_value(i);
617 END IF;
618 END LOOP;
619 END IF;
620 --End :3632767 Code to suport \. in item number
621
622 /* search for the matched segment values record */
623
624 /*
625 ** since we do not support dynamic SQL at this moment, so
626 ** we search for different table, based on the flex code
627 */
628
629 statement_num := 7;
630
631 if flex_code = 'MSTK' then
632 IF l_inv_debug_level IN(101, 102) THEN
633 INVPUTLI.info('INVPUP1.mtl_pr_parse_flex_name :verifying in MSII table' ||flex_name);
634 END IF;
635
636 select INVENTORY_ITEM_ID
637 into flex_id
638 from MTL_SYSTEM_ITEMS_INTERFACE
639 where ORGANIZATION_ID = org_id
640 and NVL(segment1,' ') = DECODE(seg_value(1),NULL,' ',seg_value(1))
641 and NVL(segment2,' ') = DECODE(seg_value(2),NULL,' ',seg_value(2))
642 and NVL(segment3,' ') = DECODE(seg_value(3),NULL,' ',seg_value(3))
643 and NVL(segment4,' ') = DECODE(seg_value(4),NULL,' ',seg_value(4))
644 and NVL(segment5,' ') = DECODE(seg_value(5),NULL,' ',seg_value(5))
645 and NVL(segment6,' ') = DECODE(seg_value(6),NULL,' ',seg_value(6))
646 and NVL(segment7,' ') = DECODE(seg_value(7),NULL,' ',seg_value(7))
647 and NVL(segment8,' ') = DECODE(seg_value(8),NULL,' ',seg_value(8))
648 and NVL(segment9,' ') = DECODE(seg_value(9),NULL,' ',seg_value(9))
649 and NVL(segment10,' ') = DECODE(seg_value(10),NULL,' ',seg_value(10))
650 and NVL(segment11,' ') = DECODE(seg_value(11),NULL,' ',seg_value(11))
651 and NVL(segment12,' ') = DECODE(seg_value(12),NULL,' ',seg_value(12))
652 and NVL(segment13,' ') = DECODE(seg_value(13),NULL,' ',seg_value(13))
653 and NVL(segment14,' ') = DECODE(seg_value(14),NULL,' ',seg_value(14))
654 and NVL(segment15,' ') = DECODE(seg_value(15),NULL,' ',seg_value(15))
655 and NVL(segment16,' ') = DECODE(seg_value(16),NULL,' ',seg_value(16))
656 and NVL(segment17,' ') = DECODE(seg_value(17),NULL,' ',seg_value(17))
657 and NVL(segment18,' ') = DECODE(seg_value(18),NULL,' ',seg_value(18))
658 and NVL(segment19,' ') = DECODE(seg_value(19),NULL,' ',seg_value(19))
659 and NVL(segment20,' ') = DECODE(seg_value(20),NULL,' ',seg_value(20))
660 --Bug: 6192567 process_flag = 1
661 and process_flag IN (1,2) and inventory_item_id is NOT NULL
662 and rownum = 1; --Bug:3340808,3531430
663
664 else if flex_code = 'MTLL' then
665 select INVENTORY_LOCATION_ID
666 into flex_id
667 from MTL_ITEM_LOCATIONS
668 where ORGANIZATION_ID = org_id
669 and NVL(segment1,' ') = DECODE(seg_value(1),NULL,' ',seg_value(1))
670 and NVL(segment2,' ') = DECODE(seg_value(2),NULL,' ',seg_value(2))
671 and NVL(segment3,' ') = DECODE(seg_value(3),NULL,' ',seg_value(3))
672 and NVL(segment4,' ') = DECODE(seg_value(4),NULL,' ',seg_value(4))
673 and NVL(segment5,' ') = DECODE(seg_value(5),NULL,' ',seg_value(5))
674 and NVL(segment6,' ') = DECODE(seg_value(6),NULL,' ',seg_value(6))
675 and NVL(segment7,' ') = DECODE(seg_value(7),NULL,' ',seg_value(7))
676 and NVL(segment8,' ') = DECODE(seg_value(8),NULL,' ',seg_value(8))
677 and NVL(segment9,' ') = DECODE(seg_value(9),NULL,' ',seg_value(9))
681 and NVL(segment13,' ') = DECODE(seg_value(13),NULL,' ',seg_value(13))
678 and NVL(segment10,' ') = DECODE(seg_value(10),NULL,' ',seg_value(10))
679 and NVL(segment11,' ') = DECODE(seg_value(11),NULL,' ',seg_value(11))
680 and NVL(segment12,' ') = DECODE(seg_value(12),NULL,' ',seg_value(12))
682 and NVL(segment14,' ') = DECODE(seg_value(14),NULL,' ',seg_value(14))
683 and NVL(segment15,' ') = DECODE(seg_value(15),NULL,' ',seg_value(15))
684 and NVL(segment16,' ') = DECODE(seg_value(16),NULL,' ',seg_value(16))
685 and NVL(segment17,' ') = DECODE(seg_value(17),NULL,' ',seg_value(17))
686 and NVL(segment18,' ') = DECODE(seg_value(18),NULL,' ',seg_value(18))
687 and NVL(segment19,' ') = DECODE(seg_value(19),NULL,' ',seg_value(19))
688 and NVL(segment20,' ') = DECODE(seg_value(20),NULL,' ',seg_value(20));
689
690 else if flex_code = 'MCAT' then
691 select CATEGORY_ID
692 into flex_id
693 from mtl_categories_b
694 where structure_id = struct_id
695 and NVL(segment1,' ') = DECODE(seg_value(1),NULL,' ',seg_value(1))
696 and NVL(segment2,' ') = DECODE(seg_value(2),NULL,' ',seg_value(2))
697 and NVL(segment3,' ') = DECODE(seg_value(3),NULL,' ',seg_value(3))
698 and NVL(segment4,' ') = DECODE(seg_value(4),NULL,' ',seg_value(4))
699 and NVL(segment5,' ') = DECODE(seg_value(5),NULL,' ',seg_value(5))
700 and NVL(segment6,' ') = DECODE(seg_value(6),NULL,' ',seg_value(6))
701 and NVL(segment7,' ') = DECODE(seg_value(7),NULL,' ',seg_value(7))
702 and NVL(segment8,' ') = DECODE(seg_value(8),NULL,' ',seg_value(8))
703 and NVL(segment9,' ') = DECODE(seg_value(9),NULL,' ',seg_value(9))
704 and NVL(segment10,' ') = DECODE(seg_value(10),NULL,' ',seg_value(10))
705 and NVL(segment11,' ') = DECODE(seg_value(11),NULL,' ',seg_value(11))
706 and NVL(segment12,' ') = DECODE(seg_value(12),NULL,' ',seg_value(12))
707 and NVL(segment13,' ') = DECODE(seg_value(13),NULL,' ',seg_value(13))
708 and NVL(segment14,' ') = DECODE(seg_value(14),NULL,' ',seg_value(14))
709 and NVL(segment15,' ') = DECODE(seg_value(15),NULL,' ',seg_value(15))
710 and NVL(segment16,' ') = DECODE(seg_value(16),NULL,' ',seg_value(16))
711 and NVL(segment17,' ') = DECODE(seg_value(17),NULL,' ',seg_value(17))
712 and NVL(segment18,' ') = DECODE(seg_value(18),NULL,' ',seg_value(18))
713 and NVL(segment19,' ') = DECODE(seg_value(19),NULL,' ',seg_value(19))
714 and NVL(segment20,' ') = DECODE(seg_value(20),NULL,' ',seg_value(20));
715
716 end if; /* MCAT */
717 end if; /* MTLL */
718 end if; /* MSTK */
719
720 RETURN (0);
721
722 EXCEPTION
723
724 WHEN others THEN
725 flex_id := NULL;
726 err_text := SUBSTRB('INVPUOPI.flex_name' || statement_num || SQLERRM, 1,240);
727 IF l_inv_debug_level IN(101, 102) THEN
728 INVPUTLI.info( err_text);
729 END IF;
730
731 RETURN (SQLCODE);
732
733 END mtl_pr_parse_flex_name;
734
735 /*
736 ** Search for the matched item number in the MSTK key flexfield view
737 ** and return the item id, if found.
738 */
739
740 FUNCTION mtl_pr_trans_prod_item
741 (
742 item_number_in varchar2,
743 org_id number,
744 item_id_out out NOCOPY number,
745 err_text out NOCOPY varchar2
746 )
747 RETURN INTEGER
748 IS
749 delimiter varchar2(10);
750 min_segment varchar2(32);
751 seg1 varchar2(40);
752 dummy number;
753 num_of_segments number;
754 min_seg_num number;
755 check_seg_num number;
756 BEGIN
757 --Bug: 4654433
758 delimiter := G_SEGMENT_DELIMITER;
759 num_of_segments := G_NUM_OF_SEGMENTS;
760
761 select min(FS.application_column_name),min(FS.segment_num)
762 into min_segment,min_seg_num
763 from FND_ID_FLEX_SEGMENTS FS
764 where FS.APPLICATION_ID = l_application_id
765 and FS.id_flex_code = l_id_flex_code
766 and FS.ENABLED_FLAG = l_enabled_flag
767 and FS.id_flex_num = l_id_flex_num;
768
769 /* Start of Bugfix 4082723 Anmurali */
770 BEGIN
771 select FS.segment_num into check_seg_num
772 from FND_ID_FLEX_SEGMENTS FS
773 where FS.APPLICATION_ID = l_application_id
774 and FS.id_flex_code = l_id_flex_code
775 and FS.ENABLED_FLAG = l_enabled_flag
776 and FS.id_flex_num = l_id_flex_num
777 and FS.application_column_name = 'SEGMENT1';
778 EXCEPTION
779
780 WHEN NO_DATA_FOUND THEN
781 check_seg_num := null;
782 END;
783
784
785
786 if (min_segment = 'SEGMENT1' and check_seg_num = min_seg_num) then
787 /*
788 ** this is being done because we assume most customers will define
789 ** flex combination using atleast segment1. Atleast they will benefit
790 ** from the index on org_id+segment1. Else the statemnt does a range
791 ** scan based on org id (since the index on segment1 is suppressed as
792 ** item_number is a concatenation of columns) and is not selective enough
793 **
794 ** 31-MAY-95: Added new if clause to not look at delimiter if
795 ** num_of_segments = 1; Fix for bug 285002
796 ** This is to avoid tripping over in the case where there is only one
797 ** segment and that segment value contains the delimiter character
798 */
799 if (num_of_segments > 1) then
800 dummy := instr(item_number_in, delimiter);
801 if (dummy = 0) then
802 seg1 := item_number_in;
803 else
804 seg1 := substr(item_number_in, 1, dummy - 1);
805 end if;
806 elsif (num_of_segments = 1) then
807 seg1 := item_number_in;
808 end if;
809 /* If condition added for bug 2935221
813 if (seg1 is null) then
810 For customers who have segment1 as null, the use of segment1 doesn't
811 return the inventroy_item_id. This is taken care of in the if-else condition
812 */
814 select inventory_item_id
815 into item_id_out
816 from mtl_system_items_b_kfv
817 where organization_id = org_id
818 and concatenated_segments = item_number_in;
819 else
820 select inventory_item_id
821 into item_id_out
822 from mtl_system_items_b_kfv
823 where segment1 = seg1
824 and organization_id = org_id
825 and concatenated_segments = item_number_in;
826 end if;
827 else
828
829 select inventory_item_id
830 into item_id_out
831 from mtl_system_items_b_kfv
832 where organization_id = org_id
833 and concatenated_segments = item_number_in;
834
835 end if;
836
837 RETURN (0);
838
839 EXCEPTION
840
841 WHEN OTHERS THEN
842 item_id_out := NULL;
843 err_text := substr('INVPUOPI.trans_item: ' || SQLERRM, 1,240);
844 RETURN (SQLCODE);
845
846 END mtl_pr_trans_prod_item;
847
848
849 FUNCTION mtl_pr_trans_org_id
850 (
851 org_code varchar2,
852 org_id out NOCOPY number,
853 err_text out NOCOPY varchar2
854 )
855 RETURN INTEGER
856 is
857 begin
858
859 --4932347: Using MTL_PARAMETERS instead of org_org
860 select organization_id
861 into org_id
862 from mtl_parameters
863 where organization_code = org_code;
864
865 return(0);
866
867 exception
868
869 WHEN OTHERS THEN
870 org_id := NULL;
871 err_text := substr('INVPUOPI.trans_org: ' || SQLERRM , 1, 240);
872 return(SQLCODE);
873
874 end mtl_pr_trans_org_id;
875
876
877 FUNCTION mtl_pr_trans_template_id
878 (
879 templ_name varchar2,
880 templ_id out NOCOPY number,
881 err_text out NOCOPY varchar2
882 )
883 RETURN INTEGER
884 IS
885 BEGIN
886 select template_id
887 into templ_id
888 from mtl_item_templates
889 where template_name = templ_name;
890
891 return(0);
892
893 exception
894
895 WHEN OTHERS THEN
896 templ_id := NULL;
897 err_text := substr('INVPUOPI.parse_template: ' || SQLERRM, 1, 240);
898 return(SQLCODE);
899
900 END mtl_pr_trans_template_id;
901
902
903 FUNCTION mtl_log_interface_err
904 (
905 org_id number,
906 user_id number,
907 login_id number,
908 prog_appid number,
909 prog_id number,
910 req_id number,
911 trans_id number,
912 error_text varchar2,
913 p_column_name VARCHAR2 := NULL,
914 tbl_name varchar2,
915 msg_name varchar2,
916 err_text OUT NOCOPY VARCHAR2
917 )
918 RETURN INTEGER
919 IS
920 dumm_status number;
921 translated_text fnd_new_messages.message_text%TYPE; --3699144
922 l_sysdate DATE := SYSDATE;
923 l_inv_debug_level NUMBER := INVPUTLI.get_debug_level; --Bug: 4667452
924 BEGIN
925
926 IF (msg_name like 'INV_IOI_ERR_%') OR (msg_name = 'INV_ICOI_ERROR') --changed by 11894684, will pass specific message name start with INV_IOI_ERR_ instead of error name INV_IOI_ERR
927 OR (msg_name like 'BOM%') OR (msg_name like 'CST%')
928 OR (msg_name like 'INV_CAT_SET_NO_DEFAULT_CAT')THEN
929 translated_text := error_text;
930 ELSE
931 dumm_status := INVUPD2B.get_message(msg_name, translated_text);
932 END IF;
933
934 IF l_inv_debug_level IN(101, 102) THEN
935 INVPUTLI.info('INVPUOPI.mtl_log_interface_err: msg_name = '||msg_name);
936 END IF;
937
938 -- Bug 4052362 To display the entire error message - Anmurali
939
940 INSERT INTO mtl_interface_errors
941 (
942 TRANSACTION_ID,
943 UNIQUE_ID,
944 ORGANIZATION_ID,
945 LAST_UPDATE_DATE,
946 LAST_UPDATED_BY,
947 CREATION_DATE,
948 CREATED_BY,
949 LAST_UPDATE_LOGIN,
950 COLUMN_NAME,
951 TABLE_NAME,
952 MESSAGE_NAME,
953 ERROR_MESSAGE,
954 REQUEST_ID,
955 PROGRAM_APPLICATION_ID,
956 PROGRAM_ID,
957 PROGRAM_UPDATE_DATE
958 )
959 VALUES
960 (
961 trans_id,
962 mtl_system_items_interface_s.NEXTVAL,
963 org_id,
964 l_sysdate,
965 user_id,
966 l_sysdate,
967 user_id,
968 login_id,
969 p_column_name,
970 tbl_name,
971 msg_name,
972 SUBSTRB(translated_text, 1,2000),
973 req_id,
974 prog_appid,
975 prog_id,
976 l_sysdate
977 );
978
979 -- Output error information into the log file
980 if (to_number(nvl(fnd_profile.value('CONC_REQUEST_ID'),0)) <> 0) then
981 FND_FILE.PUT_LINE(FND_FILE.LOG,'************************************') ;
982 FND_FILE.PUT_LINE(FND_FILE.LOG,'TRANSACTION ID : ' || trans_id);
983 FND_FILE.PUT_LINE(FND_FILE.LOG,'ORGANIZATION ID : ' || org_id);
984 FND_FILE.PUT_LINE(FND_FILE.LOG,'TABLE NAME : ' || tbl_name);
985 FND_FILE.PUT_LINE(FND_FILE.LOG,'COLUMN NAME : ' || p_column_name);
986 FND_FILE.PUT_LINE(FND_FILE.LOG,'MESSAGE NAME : ' || msg_name);
987 FND_FILE.PUT_LINE(FND_FILE.LOG,'ERROR MESSAGE : ' || substrb(translated_text,1,2000));
988 end if ;
989
990 IF l_inv_debug_level IN(101, 102) THEN
991 INVPUTLI.info('INVPUOPI.mtl_log_interface_err: TRANSACTION ID : ' || trans_id);
995 INVPUTLI.info('INVPUOPI.mtl_log_interface_err: MESSAGE NAME : ' || msg_name);
992 INVPUTLI.info('INVPUOPI.mtl_log_interface_err: ORGANIZATION ID : ' || org_id);
993 INVPUTLI.info('INVPUOPI.mtl_log_interface_err: TABLE NAME : ' || tbl_name);
994 INVPUTLI.info('INVPUOPI.mtl_log_interface_err: COLUMN NAME : ' || p_column_name);
996 INVPUTLI.info('INVPUOPI.mtl_log_interface_err: ERROR MESSAGE : ' || substrb(translated_text,1,2000));
997 END IF;
998
999 RETURN (0);
1000
1001 EXCEPTION
1002
1003 WHEN others THEN
1004 err_text := SUBSTRB('INVPUOPI.mtl_log_interface_err: ' || SQLERRM, 1,240);
1005 RETURN (SQLCODE);
1006
1007 END mtl_log_interface_err;
1008
1009 --------------------------------------------------------------------
1010 -- To convert item_number to Item_id
1011 -----------------------------------------------------------------
1012 FUNCTION mtl_pr_parse_item_name
1013 (
1014 item_number_in varchar2,
1015 item_id_out out NOCOPY number,
1016 err_text out NOCOPY varchar2
1017 )
1018 RETURN INTEGER
1019 IS
1020 delimiter varchar2(10);
1021 min_segment varchar2(32);
1022 seg1 varchar2(40);
1023 dummy number;
1024 num_of_segments number;
1025 min_seg_num number;
1026 check_seg_num number;
1027
1028 l_application_id number(10) := 401;
1029 l_id_flex_code varchar2(4) := 'MSTK';
1030 l_enabled_flag varchar2(1) := 'Y';
1031 l_id_flex_num number(15) := 101 ;
1032 BEGIN
1033
1034 --Bug: 4654433
1035 delimiter := G_SEGMENT_DELIMITER;
1036 num_of_segments := G_NUM_OF_SEGMENTS;
1037 min_segment := G_MIN_SEGMENT;
1038 min_seg_num := G_MIN_SEG_NUM;
1039 check_seg_num := G_CHECK_SEG_NUM;
1040
1041 /* Start of Bugfix 4082723 Anmurali */
1042
1043 if (min_segment = 'SEGMENT1' and check_seg_num = min_seg_num) then
1044
1045 if (num_of_segments > 1) then
1046 dummy := instr(item_number_in, delimiter);
1047 if (dummy = 0) then
1048 seg1 := item_number_in;
1049 else
1050 seg1 := substr(item_number_in, 1, dummy - 1);
1051 end if;
1052 elsif (num_of_segments = 1) then
1053 seg1 := item_number_in;
1054 end if;
1055
1056 if (seg1 is null) then
1057 select inventory_item_id
1058 into item_id_out
1059 from mtl_system_items_b_kfv
1060 where concatenated_segments = item_number_in
1061 group by inventory_item_id; -- Bug: 3447718 - added group by to get distinct inventory_item_id
1062 else
1063 select inventory_item_id
1064 into item_id_out
1065 from mtl_system_items_b_kfv
1066 where segment1 = seg1
1067 and concatenated_segments = item_number_in
1068 group by inventory_item_id; -- Bug: 3447718 - added group by to get distinct inventory_item_id
1069 end if;
1070
1071 else
1072 select inventory_item_id
1073 into item_id_out
1074 from mtl_system_items_b_kfv
1075 where concatenated_segments = item_number_in
1076 group by inventory_item_id; -- Bug: 3447718 - added group by to get distinct inventory_item_id
1077
1078 end if;
1079
1080 RETURN (0);
1081
1082 EXCEPTION
1083
1084 WHEN OTHERS THEN
1085 item_id_out := NULL;
1086 err_text := substr('INVPUOPI.trans_item: ' || SQLERRM, 1,240);
1087 RETURN (SQLCODE);
1088
1089 END mtl_pr_parse_item_name;
1090
1091 /*------------------------------*/
1092 /* Package initialization block */
1093 /*------------------------------*/
1094
1095 BEGIN
1096
1097 SELECT FT.concatenated_segment_delimiter
1098 INTO G_SEGMENT_DELIMITER
1099 FROM fnd_id_flex_structures FT
1100 WHERE FT.id_flex_code = l_id_flex_code
1101 AND FT.APPLICATION_ID = l_application_id
1102 AND FT.ID_FLEX_NUM = l_id_flex_num;
1103
1104 SELECT max(FS.segment_num)
1105 INTO G_SEGMENTS_INUSE
1106 FROM FND_ID_FLEX_SEGMENTS FS
1107 WHERE FS.APPLICATION_ID = l_application_id
1108 AND FS.id_flex_code = l_id_flex_code
1109 AND FS.ENABLED_FLAG = l_enabled_flag
1110 AND FS.id_flex_num = l_id_flex_num;
1111
1112 SELECT to_number(substr(application_column_name,8)), segment_num BULK COLLECT INTO G_SEGMENTS_USED, G_SEGMENT_NUMS_USED
1113 FROM FND_ID_FLEX_SEGMENTS
1114 WHERE APPLICATION_ID = l_application_id
1115 AND id_flex_code = l_id_flex_code
1116 AND ENABLED_FLAG = l_enabled_flag
1117 AND id_flex_num = l_id_flex_num
1118 ORDER BY segment_num;
1119
1120 SELECT COUNT(*) INTO G_NUM_OF_SEGMENTS
1121 FROM FND_ID_FLEX_SEGMENTS FS
1122 WHERE FS.APPLICATION_ID = l_application_id
1123 AND FS.id_flex_code = l_id_flex_code
1124 AND FS.ENABLED_FLAG = l_enabled_flag
1125 AND FS.id_flex_num = l_id_flex_num;
1126
1127 SELECT min(FS.application_column_name),min(FS.segment_num)
1128 INTO G_MIN_SEGMENT,G_MIN_SEG_NUM
1129 FROM FND_ID_FLEX_SEGMENTS FS
1130 WHERE FS.APPLICATION_ID = l_application_id
1131 AND FS.id_flex_code = l_id_flex_code
1132 AND FS.ENABLED_FLAG = l_enabled_flag
1133 AND FS.id_flex_num = l_id_flex_num;
1134
1135 BEGIN
1136 select FS.segment_num into G_CHECK_SEG_NUM
1137 from FND_ID_FLEX_SEGMENTS FS
1138 where FS.APPLICATION_ID = l_application_id
1139 and FS.id_flex_code = l_id_flex_code
1140 and FS.ENABLED_FLAG = l_enabled_flag
1141 and FS.id_flex_num = l_id_flex_num
1142 and FS.application_column_name = 'SEGMENT1';
1143 EXCEPTION
1144 WHEN NO_DATA_FOUND THEN
1145 G_CHECK_SEG_NUM := null;
1146 END;
1147
1148 END INVPUOPI;
[Home] [Help]