1 PACKAGE BODY INVPUOPI AS
2 /* $Header: INVPUP1B.pls 120.7.12010000.2 2009/03/02 21:35:56 sanmani 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
99 ,p_segment6 OUT NOCOPY VARCHAR2
100 ,p_segment7 OUT NOCOPY VARCHAR2
101 ,p_segment8 OUT NOCOPY VARCHAR2
102 ,p_segment9 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 (
244 item_number varchar2,
245 item_id number,
246 trans_id number,
247 org_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)),
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)),
393 segment15 = DECODE(seg_value(15),NULL,segment15,seg_value(15)),
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);
414
411 return(SQLCODE);
412
413 END mtl_pr_parse_item_number;
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
550 AND tmp_value(pos) IS NOT NULL
551 AND (INSTR(tmp_value(i),'\',-1) = LENGTH(tmp_value(i)))
552 LOOP
553
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
568 END IF;
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);
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 pos := 1 ;
588 for n in G_SEGMENTS_USED.FIRST..G_SEGMENTS_USED.LAST loop
589 seg_value(G_SEGMENTS_USED(n)) := tmp_value(pos);
590 pos := pos + 1;
591 end loop;
592
593 --Start :3632767 Code to suport \. in item number
594 IF flex_code = 'MSTK' THEN
595 FOR i IN pos ..tmp_value.count LOOP
596 IF tmp_value(i) IS NOT NULL THEN
597 seg_value(ind) := seg_value(ind) ||delimiter||tmp_value(i);
598 END IF;
599 END LOOP;
600 END IF;
601 --End :3632767 Code to suport \. in item number
602
603 /* search for the matched segment values record */
604
605 /*
606 ** since we do not support dynamic SQL at this moment, so
607 ** we search for different table, based on the flex code
608 */
609
610 statement_num := 7;
611
612 if flex_code = 'MSTK' then
613 IF l_inv_debug_level IN(101, 102) THEN
614 INVPUTLI.info('INVPUP1.mtl_pr_parse_flex_name :verifying in MSII table' ||flex_name);
615 END IF;
616
617 select INVENTORY_ITEM_ID
618 into flex_id
619 from MTL_SYSTEM_ITEMS_INTERFACE
620 where ORGANIZATION_ID = org_id
621 and NVL(segment1,' ') = DECODE(seg_value(1),NULL,' ',seg_value(1))
622 and NVL(segment2,' ') = DECODE(seg_value(2),NULL,' ',seg_value(2))
623 and NVL(segment3,' ') = DECODE(seg_value(3),NULL,' ',seg_value(3))
624 and NVL(segment4,' ') = DECODE(seg_value(4),NULL,' ',seg_value(4))
625 and NVL(segment5,' ') = DECODE(seg_value(5),NULL,' ',seg_value(5))
626 and NVL(segment6,' ') = DECODE(seg_value(6),NULL,' ',seg_value(6))
627 and NVL(segment7,' ') = DECODE(seg_value(7),NULL,' ',seg_value(7))
628 and NVL(segment8,' ') = DECODE(seg_value(8),NULL,' ',seg_value(8))
629 and NVL(segment9,' ') = DECODE(seg_value(9),NULL,' ',seg_value(9))
630 and NVL(segment10,' ') = DECODE(seg_value(10),NULL,' ',seg_value(10))
631 and NVL(segment11,' ') = DECODE(seg_value(11),NULL,' ',seg_value(11))
632 and NVL(segment12,' ') = DECODE(seg_value(12),NULL,' ',seg_value(12))
633 and NVL(segment13,' ') = DECODE(seg_value(13),NULL,' ',seg_value(13))
634 and NVL(segment14,' ') = DECODE(seg_value(14),NULL,' ',seg_value(14))
635 and NVL(segment15,' ') = DECODE(seg_value(15),NULL,' ',seg_value(15))
636 and NVL(segment16,' ') = DECODE(seg_value(16),NULL,' ',seg_value(16))
637 and NVL(segment17,' ') = DECODE(seg_value(17),NULL,' ',seg_value(17))
638 and NVL(segment18,' ') = DECODE(seg_value(18),NULL,' ',seg_value(18))
639 and NVL(segment19,' ') = DECODE(seg_value(19),NULL,' ',seg_value(19))
640 and NVL(segment20,' ') = DECODE(seg_value(20),NULL,' ',seg_value(20))
641 --Bug: 6192567 process_flag = 1
642 and process_flag IN (1,2) and inventory_item_id is NOT NULL
643 and rownum = 1; --Bug:3340808,3531430
644
645 else if flex_code = 'MTLL' then
646 select INVENTORY_LOCATION_ID
647 into flex_id
648 from MTL_ITEM_LOCATIONS
649 where ORGANIZATION_ID = org_id
650 and NVL(segment1,' ') = DECODE(seg_value(1),NULL,' ',seg_value(1))
651 and NVL(segment2,' ') = DECODE(seg_value(2),NULL,' ',seg_value(2))
652 and NVL(segment3,' ') = DECODE(seg_value(3),NULL,' ',seg_value(3))
653 and NVL(segment4,' ') = DECODE(seg_value(4),NULL,' ',seg_value(4))
654 and NVL(segment5,' ') = DECODE(seg_value(5),NULL,' ',seg_value(5))
655 and NVL(segment6,' ') = DECODE(seg_value(6),NULL,' ',seg_value(6))
656 and NVL(segment7,' ') = DECODE(seg_value(7),NULL,' ',seg_value(7))
657 and NVL(segment8,' ') = DECODE(seg_value(8),NULL,' ',seg_value(8))
658 and NVL(segment9,' ') = DECODE(seg_value(9),NULL,' ',seg_value(9))
659 and NVL(segment10,' ') = DECODE(seg_value(10),NULL,' ',seg_value(10))
660 and NVL(segment11,' ') = DECODE(seg_value(11),NULL,' ',seg_value(11))
661 and NVL(segment12,' ') = DECODE(seg_value(12),NULL,' ',seg_value(12))
662 and NVL(segment13,' ') = DECODE(seg_value(13),NULL,' ',seg_value(13))
663 and NVL(segment14,' ') = DECODE(seg_value(14),NULL,' ',seg_value(14))
664 and NVL(segment15,' ') = DECODE(seg_value(15),NULL,' ',seg_value(15))
665 and NVL(segment16,' ') = DECODE(seg_value(16),NULL,' ',seg_value(16))
666 and NVL(segment17,' ') = DECODE(seg_value(17),NULL,' ',seg_value(17))
667 and NVL(segment18,' ') = DECODE(seg_value(18),NULL,' ',seg_value(18))
668 and NVL(segment19,' ') = DECODE(seg_value(19),NULL,' ',seg_value(19))
669 and NVL(segment20,' ') = DECODE(seg_value(20),NULL,' ',seg_value(20));
670
671 else if flex_code = 'MCAT' then
672 select CATEGORY_ID
676 and NVL(segment1,' ') = DECODE(seg_value(1),NULL,' ',seg_value(1))
673 into flex_id
674 from mtl_categories_b
675 where structure_id = struct_id
677 and NVL(segment2,' ') = DECODE(seg_value(2),NULL,' ',seg_value(2))
678 and NVL(segment3,' ') = DECODE(seg_value(3),NULL,' ',seg_value(3))
679 and NVL(segment4,' ') = DECODE(seg_value(4),NULL,' ',seg_value(4))
680 and NVL(segment5,' ') = DECODE(seg_value(5),NULL,' ',seg_value(5))
681 and NVL(segment6,' ') = DECODE(seg_value(6),NULL,' ',seg_value(6))
682 and NVL(segment7,' ') = DECODE(seg_value(7),NULL,' ',seg_value(7))
683 and NVL(segment8,' ') = DECODE(seg_value(8),NULL,' ',seg_value(8))
684 and NVL(segment9,' ') = DECODE(seg_value(9),NULL,' ',seg_value(9))
685 and NVL(segment10,' ') = DECODE(seg_value(10),NULL,' ',seg_value(10))
686 and NVL(segment11,' ') = DECODE(seg_value(11),NULL,' ',seg_value(11))
687 and NVL(segment12,' ') = DECODE(seg_value(12),NULL,' ',seg_value(12))
688 and NVL(segment13,' ') = DECODE(seg_value(13),NULL,' ',seg_value(13))
689 and NVL(segment14,' ') = DECODE(seg_value(14),NULL,' ',seg_value(14))
690 and NVL(segment15,' ') = DECODE(seg_value(15),NULL,' ',seg_value(15))
691 and NVL(segment16,' ') = DECODE(seg_value(16),NULL,' ',seg_value(16))
692 and NVL(segment17,' ') = DECODE(seg_value(17),NULL,' ',seg_value(17))
693 and NVL(segment18,' ') = DECODE(seg_value(18),NULL,' ',seg_value(18))
694 and NVL(segment19,' ') = DECODE(seg_value(19),NULL,' ',seg_value(19))
695 and NVL(segment20,' ') = DECODE(seg_value(20),NULL,' ',seg_value(20));
696
697 end if; /* MCAT */
698 end if; /* MTLL */
699 end if; /* MSTK */
700
701 RETURN (0);
702
703 EXCEPTION
704
705 WHEN others THEN
706 flex_id := NULL;
707 err_text := SUBSTRB('INVPUOPI.flex_name' || statement_num || SQLERRM, 1,240);
708 IF l_inv_debug_level IN(101, 102) THEN
709 INVPUTLI.info( err_text);
710 END IF;
711
712 RETURN (SQLCODE);
713
714 END mtl_pr_parse_flex_name;
715
716 /*
717 ** Search for the matched item number in the MSTK key flexfield view
718 ** and return the item id, if found.
719 */
720
721 FUNCTION mtl_pr_trans_prod_item
722 (
723 item_number_in varchar2,
724 org_id number,
725 item_id_out out NOCOPY number,
726 err_text out NOCOPY varchar2
727 )
728 RETURN INTEGER
729 IS
730 delimiter varchar2(10);
731 min_segment varchar2(32);
732 seg1 varchar2(40);
733 dummy number;
734 num_of_segments number;
735 min_seg_num number;
736 check_seg_num number;
737 BEGIN
738 --Bug: 4654433
739 delimiter := G_SEGMENT_DELIMITER;
740 num_of_segments := G_NUM_OF_SEGMENTS;
741
742 select min(FS.application_column_name),min(FS.segment_num)
743 into min_segment,min_seg_num
744 from FND_ID_FLEX_SEGMENTS FS
745 where FS.APPLICATION_ID = l_application_id
746 and FS.id_flex_code = l_id_flex_code
747 and FS.ENABLED_FLAG = l_enabled_flag
748 and FS.id_flex_num = l_id_flex_num;
749
750 /* Start of Bugfix 4082723 Anmurali */
751 BEGIN
752 select FS.segment_num into check_seg_num
753 from FND_ID_FLEX_SEGMENTS FS
754 where FS.APPLICATION_ID = l_application_id
755 and FS.id_flex_code = l_id_flex_code
756 and FS.ENABLED_FLAG = l_enabled_flag
757 and FS.id_flex_num = l_id_flex_num
758 and FS.application_column_name = 'SEGMENT1';
759 EXCEPTION
760
761 WHEN NO_DATA_FOUND THEN
762 check_seg_num := null;
763 END;
764
765
766
767 if (min_segment = 'SEGMENT1' and check_seg_num = min_seg_num) then
768 /*
769 ** this is being done because we assume most customers will define
770 ** flex combination using atleast segment1. Atleast they will benefit
771 ** from the index on org_id+segment1. Else the statemnt does a range
772 ** scan based on org id (since the index on segment1 is suppressed as
773 ** item_number is a concatenation of columns) and is not selective enough
774 **
775 ** 31-MAY-95: Added new if clause to not look at delimiter if
776 ** num_of_segments = 1; Fix for bug 285002
777 ** This is to avoid tripping over in the case where there is only one
778 ** segment and that segment value contains the delimiter character
779 */
780 if (num_of_segments > 1) then
781 dummy := instr(item_number_in, delimiter);
782 if (dummy = 0) then
783 seg1 := item_number_in;
784 else
785 seg1 := substr(item_number_in, 1, dummy - 1);
786 end if;
787 elsif (num_of_segments = 1) then
788 seg1 := item_number_in;
789 end if;
790 /* If condition added for bug 2935221
791 For customers who have segment1 as null, the use of segment1 doesn't
792 return the inventroy_item_id. This is taken care of in the if-else condition
793 */
794 if (seg1 is null) then
795 select inventory_item_id
796 into item_id_out
797 from mtl_system_items_b_kfv
798 where organization_id = org_id
802 into item_id_out
799 and concatenated_segments = item_number_in;
800 else
801 select inventory_item_id
803 from mtl_system_items_b_kfv
804 where segment1 = seg1
805 and organization_id = org_id
806 and concatenated_segments = item_number_in;
807 end if;
808 else
809
810 select inventory_item_id
811 into item_id_out
812 from mtl_system_items_b_kfv
813 where organization_id = org_id
814 and concatenated_segments = item_number_in;
815
816 end if;
817
818 RETURN (0);
819
820 EXCEPTION
821
822 WHEN OTHERS THEN
823 item_id_out := NULL;
824 err_text := substr('INVPUOPI.trans_item: ' || SQLERRM, 1,240);
825 RETURN (SQLCODE);
826
827 END mtl_pr_trans_prod_item;
828
829
830 FUNCTION mtl_pr_trans_org_id
831 (
832 org_code varchar2,
833 org_id out NOCOPY number,
834 err_text out NOCOPY varchar2
835 )
836 RETURN INTEGER
837 is
838 begin
839
840 --4932347: Using MTL_PARAMETERS instead of org_org
841 select organization_id
842 into org_id
843 from mtl_parameters
844 where organization_code = org_code;
845
846 return(0);
847
848 exception
849
850 WHEN OTHERS THEN
851 org_id := NULL;
852 err_text := substr('INVPUOPI.trans_org: ' || SQLERRM , 1, 240);
853 return(SQLCODE);
854
855 end mtl_pr_trans_org_id;
856
857
858 FUNCTION mtl_pr_trans_template_id
859 (
860 templ_name varchar2,
861 templ_id out NOCOPY number,
862 err_text out NOCOPY varchar2
863 )
864 RETURN INTEGER
865 IS
866 BEGIN
867 select template_id
868 into templ_id
869 from mtl_item_templates
870 where template_name = templ_name;
871
872 return(0);
873
874 exception
875
876 WHEN OTHERS THEN
877 templ_id := NULL;
878 err_text := substr('INVPUOPI.parse_template: ' || SQLERRM, 1, 240);
879 return(SQLCODE);
880
881 END mtl_pr_trans_template_id;
882
883
884 FUNCTION mtl_log_interface_err
885 (
886 org_id number,
887 user_id number,
888 login_id number,
889 prog_appid number,
890 prog_id number,
891 req_id number,
892 trans_id number,
893 error_text varchar2,
894 p_column_name VARCHAR2 := NULL,
895 tbl_name varchar2,
896 msg_name varchar2,
897 err_text OUT NOCOPY VARCHAR2
898 )
899 RETURN INTEGER
900 IS
901 dumm_status number;
902 translated_text fnd_new_messages.message_text%TYPE; --3699144
903 l_sysdate DATE := SYSDATE;
904 l_inv_debug_level NUMBER := INVPUTLI.get_debug_level; --Bug: 4667452
905 BEGIN
906
907 IF (msg_name = 'INV_IOI_ERR') OR (msg_name = 'INV_ICOI_ERROR')
908 OR (msg_name like 'BOM%') OR (msg_name like 'CST%')
909 OR (msg_name like 'INV_CAT_SET_NO_DEFAULT_CAT')THEN
910 translated_text := error_text;
911 ELSE
912 dumm_status := INVUPD2B.get_message(msg_name, translated_text);
913 END IF;
914
915 IF l_inv_debug_level IN(101, 102) THEN
916 INVPUTLI.info('INVPUOPI.mtl_log_interface_err: msg_name = '||msg_name);
917 END IF;
918
919 -- Bug 4052362 To display the entire error message - Anmurali
920
921 INSERT INTO mtl_interface_errors
922 (
923 TRANSACTION_ID,
924 UNIQUE_ID,
925 ORGANIZATION_ID,
926 LAST_UPDATE_DATE,
927 LAST_UPDATED_BY,
928 CREATION_DATE,
929 CREATED_BY,
930 LAST_UPDATE_LOGIN,
931 COLUMN_NAME,
932 TABLE_NAME,
933 MESSAGE_NAME,
934 ERROR_MESSAGE,
935 REQUEST_ID,
936 PROGRAM_APPLICATION_ID,
937 PROGRAM_ID,
938 PROGRAM_UPDATE_DATE
939 )
940 VALUES
941 (
942 trans_id,
943 mtl_system_items_interface_s.NEXTVAL,
944 org_id,
945 l_sysdate,
946 user_id,
947 l_sysdate,
948 user_id,
949 login_id,
950 p_column_name,
951 tbl_name,
952 msg_name,
953 SUBSTRB(translated_text, 1,2000),
954 req_id,
955 prog_appid,
956 prog_id,
957 l_sysdate
958 );
959
960 -- Output error information into the log file
961 if (to_number(nvl(fnd_profile.value('CONC_REQUEST_ID'),0)) <> 0) then
962 FND_FILE.PUT_LINE(FND_FILE.LOG,'************************************') ;
963 FND_FILE.PUT_LINE(FND_FILE.LOG,'TRANSACTION ID : ' || trans_id);
964 FND_FILE.PUT_LINE(FND_FILE.LOG,'ORGANIZATION ID : ' || org_id);
965 FND_FILE.PUT_LINE(FND_FILE.LOG,'TABLE NAME : ' || tbl_name);
966 FND_FILE.PUT_LINE(FND_FILE.LOG,'COLUMN NAME : ' || p_column_name);
967 FND_FILE.PUT_LINE(FND_FILE.LOG,'MESSAGE NAME : ' || msg_name);
968 FND_FILE.PUT_LINE(FND_FILE.LOG,'ERROR MESSAGE : ' || substrb(translated_text,1,2000));
969 end if ;
970
971 IF l_inv_debug_level IN(101, 102) THEN
972 INVPUTLI.info('INVPUOPI.mtl_log_interface_err: TRANSACTION ID : ' || trans_id);
976 INVPUTLI.info('INVPUOPI.mtl_log_interface_err: MESSAGE NAME : ' || msg_name);
973 INVPUTLI.info('INVPUOPI.mtl_log_interface_err: ORGANIZATION ID : ' || org_id);
974 INVPUTLI.info('INVPUOPI.mtl_log_interface_err: TABLE NAME : ' || tbl_name);
975 INVPUTLI.info('INVPUOPI.mtl_log_interface_err: COLUMN NAME : ' || p_column_name);
977 INVPUTLI.info('INVPUOPI.mtl_log_interface_err: ERROR MESSAGE : ' || substrb(translated_text,1,2000));
978 END IF;
979
980 RETURN (0);
981
982 EXCEPTION
983
984 WHEN others THEN
985 err_text := SUBSTRB('INVPUOPI.mtl_log_interface_err: ' || SQLERRM, 1,240);
986 RETURN (SQLCODE);
987
988 END mtl_log_interface_err;
989
990 --------------------------------------------------------------------
991 -- To convert item_number to Item_id
992 -----------------------------------------------------------------
993 FUNCTION mtl_pr_parse_item_name
994 (
995 item_number_in varchar2,
996 item_id_out out NOCOPY number,
997 err_text out NOCOPY varchar2
998 )
999 RETURN INTEGER
1000 IS
1001 delimiter varchar2(10);
1002 min_segment varchar2(32);
1003 seg1 varchar2(40);
1004 dummy number;
1005 num_of_segments number;
1006 min_seg_num number;
1007 check_seg_num number;
1008
1009 l_application_id number(10) := 401;
1010 l_id_flex_code varchar2(4) := 'MSTK';
1011 l_enabled_flag varchar2(1) := 'Y';
1012 l_id_flex_num number(15) := 101 ;
1013 BEGIN
1014
1015 --Bug: 4654433
1016 delimiter := G_SEGMENT_DELIMITER;
1017 num_of_segments := G_NUM_OF_SEGMENTS;
1018 min_segment := G_MIN_SEGMENT;
1019 min_seg_num := G_MIN_SEG_NUM;
1020 check_seg_num := G_CHECK_SEG_NUM;
1021
1022 /* Start of Bugfix 4082723 Anmurali */
1023
1024 if (min_segment = 'SEGMENT1' and check_seg_num = min_seg_num) then
1025
1026 if (num_of_segments > 1) then
1027 dummy := instr(item_number_in, delimiter);
1028 if (dummy = 0) then
1029 seg1 := item_number_in;
1030 else
1031 seg1 := substr(item_number_in, 1, dummy - 1);
1032 end if;
1033 elsif (num_of_segments = 1) then
1034 seg1 := item_number_in;
1035 end if;
1036
1037 if (seg1 is null) then
1038 select inventory_item_id
1039 into item_id_out
1040 from mtl_system_items_b_kfv
1041 where concatenated_segments = item_number_in
1042 group by inventory_item_id; -- Bug: 3447718 - added group by to get distinct inventory_item_id
1043 else
1044 select inventory_item_id
1045 into item_id_out
1046 from mtl_system_items_b_kfv
1047 where segment1 = seg1
1048 and concatenated_segments = item_number_in
1049 group by inventory_item_id; -- Bug: 3447718 - added group by to get distinct inventory_item_id
1050 end if;
1051
1052 else
1053 select inventory_item_id
1054 into item_id_out
1055 from mtl_system_items_b_kfv
1056 where concatenated_segments = item_number_in
1057 group by inventory_item_id; -- Bug: 3447718 - added group by to get distinct inventory_item_id
1058
1059 end if;
1060
1061 RETURN (0);
1062
1063 EXCEPTION
1064
1065 WHEN OTHERS THEN
1066 item_id_out := NULL;
1067 err_text := substr('INVPUOPI.trans_item: ' || SQLERRM, 1,240);
1068 RETURN (SQLCODE);
1069
1070 END mtl_pr_parse_item_name;
1071
1072 /*------------------------------*/
1073 /* Package initialization block */
1074 /*------------------------------*/
1075
1076 BEGIN
1077
1078 SELECT FT.concatenated_segment_delimiter
1079 INTO G_SEGMENT_DELIMITER
1080 FROM fnd_id_flex_structures FT
1081 WHERE FT.id_flex_code = l_id_flex_code
1082 AND FT.APPLICATION_ID = l_application_id
1083 AND FT.ID_FLEX_NUM = l_id_flex_num;
1084
1085 SELECT max(FS.segment_num)
1086 INTO G_SEGMENTS_INUSE
1087 FROM FND_ID_FLEX_SEGMENTS FS
1088 WHERE FS.APPLICATION_ID = l_application_id
1089 AND FS.id_flex_code = l_id_flex_code
1090 AND FS.ENABLED_FLAG = l_enabled_flag
1091 AND FS.id_flex_num = l_id_flex_num;
1092
1093 SELECT to_number(substr(application_column_name,8)), segment_num BULK COLLECT INTO G_SEGMENTS_USED, G_SEGMENT_NUMS_USED
1094 FROM FND_ID_FLEX_SEGMENTS
1095 WHERE APPLICATION_ID = l_application_id
1096 AND id_flex_code = l_id_flex_code
1097 AND ENABLED_FLAG = l_enabled_flag
1098 AND id_flex_num = l_id_flex_num
1099 ORDER BY segment_num;
1100
1101 SELECT COUNT(*) INTO G_NUM_OF_SEGMENTS
1102 FROM FND_ID_FLEX_SEGMENTS FS
1103 WHERE FS.APPLICATION_ID = l_application_id
1104 AND FS.id_flex_code = l_id_flex_code
1105 AND FS.ENABLED_FLAG = l_enabled_flag
1106 AND FS.id_flex_num = l_id_flex_num;
1107
1108 SELECT min(FS.application_column_name),min(FS.segment_num)
1109 INTO G_MIN_SEGMENT,G_MIN_SEG_NUM
1110 FROM FND_ID_FLEX_SEGMENTS FS
1111 WHERE FS.APPLICATION_ID = l_application_id
1112 AND FS.id_flex_code = l_id_flex_code
1113 AND FS.ENABLED_FLAG = l_enabled_flag
1114 AND FS.id_flex_num = l_id_flex_num;
1115
1116 BEGIN
1117 select FS.segment_num into G_CHECK_SEG_NUM
1118 from FND_ID_FLEX_SEGMENTS FS
1122 and FS.id_flex_num = l_id_flex_num
1119 where FS.APPLICATION_ID = l_application_id
1120 and FS.id_flex_code = l_id_flex_code
1121 and FS.ENABLED_FLAG = l_enabled_flag
1123 and FS.application_column_name = 'SEGMENT1';
1124 EXCEPTION
1125 WHEN NO_DATA_FOUND THEN
1126 G_CHECK_SEG_NUM := null;
1127 END;
1128
1129 END INVPUOPI;
[Home] [Help]