[Home] [Help]
PACKAGE BODY: APPS.INV_INVIRILC_XMLP_PKG
Source
1 PACKAGE BODY INV_INVIRILC_XMLP_PKG AS
2 /* $Header: INVIRILCB.pls 120.2 2008/01/08 06:36:36 dwkrishn noship $ */
3 FUNCTION AFTERREPORT RETURN BOOLEAN IS
4 BEGIN
5 BEGIN
6 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
7 RETURN (TRUE);
8 EXCEPTION
9 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
10 /*SRW.MESSAGE(10
11 ,'Srwexit failure after rpt trig')*/NULL;
12 END;
13 RETURN (TRUE);
14 END AFTERREPORT;
15
16 FUNCTION WEIGHT_CONV_RATE(MOH_ITEM_ID IN NUMBER
17 ,MSI_UOM_CODE IN VARCHAR2
18 ,MIL_WEIGHT_UOM IN VARCHAR2) RETURN NUMBER IS
19 CONV_RATE NUMBER;
20 CLASS_RATE NUMBER;
21 ITEM_ID NUMBER;
22 FROM_CLASS_RATE NUMBER;
23 TO_CLASS_RATE NUMBER;
24 TO_RATE NUMBER;
25 FROM_RATE NUMBER;
26 TO_UOM_FLAG NUMBER;
27 FROM_CLASS VARCHAR2(10);
28 TO_CLASS VARCHAR2(10);
29 FROM_CODE VARCHAR2(3);
30 TO_CODE VARCHAR2(3);
31 CONV_MSG VARCHAR2(1);
32 BEGIN
33 ITEM_ID := MOH_ITEM_ID;
34 FROM_CODE := MSI_UOM_CODE;
35 TO_CODE := MIL_WEIGHT_UOM;
36 CONV_MSG := '';
37 CONV_RATE := 1;
38 TO_CLASS_RATE := NULL;
39 FROM_CLASS_RATE := NULL;
40 FROM_CLASS := 0;
41 TO_CLASS := 0;
42 CLASS_RATE := 1;
43 IF FROM_CODE = TO_CODE THEN
44 GOTO end_conv;
45 END IF;
46 BEGIN
47 SELECT
48 F.CONVERSION_RATE,
49 F.UOM_CLASS,
50 T.CONVERSION_RATE,
51 T.UOM_CLASS
52 INTO FROM_RATE,FROM_CLASS,TO_RATE,TO_CLASS
53 FROM
54 MTL_UOM_CONVERSIONS F,
55 MTL_UOM_CONVERSIONS T
56 WHERE F.INVENTORY_ITEM_ID IN ( ITEM_ID , 0 )
57 AND F.UOM_CODE = FROM_CODE
58 AND T.INVENTORY_ITEM_ID IN ( ITEM_ID , 0 )
59 AND T.UOM_CODE = TO_CODE;
60 EXCEPTION
61 WHEN NO_DATA_FOUND THEN
62 CONV_RATE := -1;
63 WHEN OTHERS THEN
64 CONV_RATE := -2;
65 END;
66 IF CONV_RATE < 0 THEN
67 GOTO end_conv;
68 END IF;
69 IF FROM_CLASS = TO_CLASS THEN
70 GOTO calc_conv_rate_1;
71 END IF;
72 BEGIN
73 SELECT
74 DECODE(TO_UOM_CLASS
75 ,TO_CLASS
76 ,1
77 ,2),
78 CONVERSION_RATE
79 INTO TO_UOM_FLAG,CLASS_RATE
80 FROM
81 MTL_UOM_CLASS_CONVERSIONS
82 WHERE INVENTORY_ITEM_ID = ITEM_ID
83 AND TO_UOM_CLASS IN ( FROM_CLASS , TO_CLASS )
84 AND FROM_UOM_CLASS IN ( FROM_CLASS , TO_CLASS )
85 AND FROM_UOM_CLASS <> TO_UOM_CLASS;
86 EXCEPTION
87 WHEN NO_DATA_FOUND THEN
88 CONV_RATE := -1;
89 WHEN OTHERS THEN
90 CONV_RATE := -2;
91 END;
92 IF CONV_RATE < 0 THEN
93 GOTO end_conv;
94 END IF;
95 IF TO_UOM_FLAG = 0 THEN
96 GOTO get_class_conv_2;
97 END IF;
98 IF TO_UOM_FLAG = 2 THEN
99 GOTO end_conv;
100 END IF;
101 IF CLASS_RATE = 0 THEN
102 CONV_RATE := -3;
103 END IF;
104 CLASS_RATE := 1 / CLASS_RATE;
105 GOTO calc_conv_rate_1;
106 <<GET_CLASS_CONV_2>>BEGIN
107 SELECT
108 F.CONVERSION_RATE,
109 T.CONVERSION_RATE
110 INTO FROM_CLASS_RATE,TO_CLASS_RATE
111 FROM
112 MTL_UOM_CLASS_CONVERSIONS F,
113 MTL_UOM_CLASS_CONVERSIONS T
114 WHERE F.INVENTORY_ITEM_ID = ITEM_ID
115 AND T.INVENTORY_ITEM_ID = ITEM_ID
116 AND F.TO_UOM_CLASS = FROM_CLASS
117 AND T.TO_UOM_CLASS = TO_CLASS;
118 EXCEPTION
119 WHEN NO_DATA_FOUND THEN
120 CONV_RATE := -1;
121 WHEN OTHERS THEN
122 CONV_RATE := -2;
123 END;
124 IF CONV_RATE < 0 THEN
125 GOTO end_conv;
126 END IF;
127 IF TO_CLASS_RATE = 0 THEN
128 CONV_RATE := -3;
129 GOTO end_conv;
130 END IF;
131 CLASS_RATE := FROM_CLASS_RATE / TO_CLASS_RATE;
132 GOTO calc_conv_rate_1;
133 <<CALC_CONV_RATE_1>>IF TO_RATE = 0 THEN
134 CONV_RATE := -3;
135 GOTO end_conv;
136 END IF;
137 CONV_RATE := FROM_RATE * CLASS_RATE;
138 CONV_RATE := CONV_RATE / TO_RATE;
139 GOTO end_conv;
140 <<END_CONV>>RETURN (CONV_RATE);
141 RETURN NULL;
142 EXCEPTION
143 WHEN NO_DATA_FOUND THEN
144 CONV_RATE := -4;
145 RETURN (CONV_RATE);
146 WHEN OTHERS THEN
147 CONV_RATE := -5;
148 RETURN (CONV_RATE);
149 END WEIGHT_CONV_RATE;
150
151 FUNCTION DSPLY_WT_CNV_RATE(C_WEIGHT_CONV_RATE IN NUMBER) RETURN CHARACTER IS
152 CONV_RATE NUMBER;
153 CONV_RATE_DSPLY VARCHAR2(80);
154 BEGIN
155 CONV_RATE := C_WEIGHT_CONV_RATE;
156 BEGIN
157 IF CONV_RATE = -1 THEN
158 SELECT
159 MEANING
160 INTO CONV_RATE_DSPLY
161 FROM
162 MFG_LOOKUPS
163 WHERE LOOKUP_TYPE = 'INV_LOC_QTY_RPT_MSGS'
164 AND LOOKUP_CODE = 1;
165 END IF;
166 IF CONV_RATE = -2 THEN
167 SELECT
168 MEANING
169 INTO CONV_RATE_DSPLY
170 FROM
171 MFG_LOOKUPS
172 WHERE LOOKUP_TYPE = 'INV_LOC_QTY_RPT_MSGS'
173 AND LOOKUP_CODE = 2;
174 END IF;
175 IF CONV_RATE < -2 THEN
176 SELECT
177 MEANING
178 INTO CONV_RATE_DSPLY
179 FROM
180 MFG_LOOKUPS
181 WHERE LOOKUP_TYPE = 'INV_LOC_QTY_RPT_MSGS'
182 AND LOOKUP_CODE = 3;
183 END IF;
184 EXCEPTION
185 WHEN OTHERS THEN
186 /*SRW.MESSAGE(90
187 ,'ERROR in fetching data from MFG_LOOKUPS table')*/NULL;
188 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
189 END;
190 IF CONV_RATE > -1 THEN
191 CONV_RATE_DSPLY := TO_CHAR(ROUND(CONV_RATE
192 ,6));
193 END IF;
194 RETURN (CONV_RATE_DSPLY);
195 END DSPLY_WT_CNV_RATE;
196
197 FUNCTION VOL_CONV_RATE(MOH_ITEM_ID IN NUMBER
198 ,MSI_UOM_CODE IN VARCHAR2
199 ,MIL_VOL_UOM IN VARCHAR2) RETURN NUMBER IS
200 CONV_RATE NUMBER;
201 CLASS_RATE NUMBER;
202 ITEM_ID NUMBER;
203 FROM_CLASS_RATE NUMBER;
204 TO_CLASS_RATE NUMBER;
205 TO_RATE NUMBER;
206 FROM_RATE NUMBER;
207 TO_UOM_FLAG NUMBER;
208 FROM_CLASS VARCHAR2(10);
209 TO_CLASS VARCHAR2(10);
210 FROM_CODE VARCHAR2(3);
211 TO_CODE VARCHAR2(3);
212 CONV_MSG VARCHAR2(1);
213 BEGIN
214 ITEM_ID := MOH_ITEM_ID;
215 FROM_CODE := MSI_UOM_CODE;
216 TO_CODE := MIL_VOL_UOM;
217 CONV_MSG := '';
218 CONV_RATE := 1;
219 TO_CLASS_RATE := NULL;
220 FROM_CLASS_RATE := NULL;
221 FROM_CLASS := 0;
222 TO_CLASS := 0;
223 CLASS_RATE := 1;
224 IF FROM_CODE = TO_CODE THEN
225 GOTO end_conv;
226 END IF;
227 BEGIN
228 SELECT
229 F.CONVERSION_RATE,
230 F.UOM_CLASS,
231 T.CONVERSION_RATE,
232 T.UOM_CLASS
233 INTO FROM_RATE,FROM_CLASS,TO_RATE,TO_CLASS
234 FROM
235 MTL_UOM_CONVERSIONS F,
236 MTL_UOM_CONVERSIONS T
237 WHERE F.INVENTORY_ITEM_ID IN ( ITEM_ID , 0 )
238 AND F.UOM_CODE = FROM_CODE
239 AND T.INVENTORY_ITEM_ID IN ( ITEM_ID , 0 )
240 AND T.UOM_CODE = TO_CODE;
241 EXCEPTION
242 WHEN NO_DATA_FOUND THEN
243 CONV_RATE := -1;
244 WHEN OTHERS THEN
245 CONV_RATE := -2;
246 END;
247 IF CONV_RATE < 0 THEN
248 GOTO end_conv;
249 END IF;
250 IF FROM_CLASS = TO_CLASS THEN
251 GOTO calc_conv_rate_1;
252 END IF;
253 BEGIN
254 SELECT
255 DECODE(TO_UOM_CLASS
256 ,TO_CLASS
257 ,1
258 ,2),
259 CONVERSION_RATE
260 INTO TO_UOM_FLAG,CLASS_RATE
261 FROM
262 MTL_UOM_CLASS_CONVERSIONS
263 WHERE INVENTORY_ITEM_ID = ITEM_ID
264 AND TO_UOM_CLASS IN ( FROM_CLASS , TO_CLASS )
265 AND FROM_UOM_CLASS IN ( FROM_CLASS , TO_CLASS )
266 AND FROM_UOM_CLASS <> TO_UOM_CLASS;
267 EXCEPTION
268 WHEN NO_DATA_FOUND THEN
269 CONV_RATE := -1;
270 WHEN OTHERS THEN
271 CONV_RATE := -2;
272 END;
273 IF CONV_RATE < 0 THEN
274 GOTO end_conv;
275 END IF;
276 IF TO_UOM_FLAG = 0 THEN
277 GOTO get_class_conv_2;
278 END IF;
279 IF TO_UOM_FLAG = 2 THEN
280 GOTO end_conv;
281 END IF;
282 IF CLASS_RATE = 0 THEN
283 CONV_RATE := -3;
284 END IF;
285 CLASS_RATE := 1 / CLASS_RATE;
286 GOTO calc_conv_rate_1;
287 <<GET_CLASS_CONV_2>>BEGIN
288 SELECT
289 F.CONVERSION_RATE,
290 T.CONVERSION_RATE
291 INTO FROM_CLASS_RATE,TO_CLASS_RATE
292 FROM
293 MTL_UOM_CLASS_CONVERSIONS F,
294 MTL_UOM_CLASS_CONVERSIONS T
295 WHERE F.INVENTORY_ITEM_ID = ITEM_ID
296 AND T.INVENTORY_ITEM_ID = ITEM_ID
297 AND F.TO_UOM_CLASS = FROM_CLASS
298 AND T.TO_UOM_CLASS = TO_CLASS;
299 EXCEPTION
300 WHEN NO_DATA_FOUND THEN
301 CONV_RATE := -1;
302 WHEN OTHERS THEN
303 CONV_RATE := -2;
304 END;
305 IF CONV_RATE < 0 THEN
306 GOTO end_conv;
307 END IF;
308 IF TO_CLASS_RATE = 0 THEN
309 CONV_RATE := -3;
310 GOTO end_conv;
311 END IF;
312 CLASS_RATE := FROM_CLASS_RATE / TO_CLASS_RATE;
313 GOTO calc_conv_rate_1;
314 <<CALC_CONV_RATE_1>>IF TO_RATE = 0 THEN
315 CONV_RATE := -3;
316 GOTO end_conv;
317 END IF;
318 CONV_RATE := FROM_RATE * CLASS_RATE;
319 CONV_RATE := CONV_RATE / TO_RATE;
320 GOTO end_conv;
321 <<END_CONV>>RETURN (CONV_RATE);
322 RETURN NULL;
323 EXCEPTION
324 WHEN NO_DATA_FOUND THEN
325 CONV_RATE := -4;
326 RETURN (CONV_RATE);
327 WHEN OTHERS THEN
328 CONV_RATE := -5;
329 RETURN (CONV_RATE);
330 END VOL_CONV_RATE;
331
332 FUNCTION DSPLY_VOL_CNV_RATE(C_VOL_CONV_RATE IN NUMBER) RETURN CHARACTER IS
333 CONV_RATE NUMBER;
334 CONV_RATE_DSPLY VARCHAR2(80);
335 BEGIN
336 CONV_RATE := C_VOL_CONV_RATE;
337 BEGIN
338 IF CONV_RATE = -1 THEN
339 SELECT
340 MEANING
341 INTO CONV_RATE_DSPLY
342 FROM
343 MFG_LOOKUPS
344 WHERE LOOKUP_TYPE = 'INV_LOC_QTY_RPT_MSGS'
345 AND LOOKUP_CODE = 1;
346 END IF;
347 IF CONV_RATE = -2 THEN
348 SELECT
349 MEANING
350 INTO CONV_RATE_DSPLY
351 FROM
352 MFG_LOOKUPS
353 WHERE LOOKUP_TYPE = 'INV_LOC_QTY_RPT_MSGS'
354 AND LOOKUP_CODE = 2;
355 END IF;
356 IF CONV_RATE < -2 THEN
357 SELECT
358 MEANING
359 INTO CONV_RATE_DSPLY
360 FROM
361 MFG_LOOKUPS
362 WHERE LOOKUP_TYPE = 'INV_LOC_QTY_RPT_MSGS'
363 AND LOOKUP_CODE = 3;
364 END IF;
365 EXCEPTION
366 WHEN OTHERS THEN
367 /*SRW.MESSAGE(90
368 ,'ERROR in fetching data from MFG_LOOKUPS table')*/NULL;
369 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
370 END;
371 IF CONV_RATE > -1 THEN
372 CONV_RATE_DSPLY := TO_CHAR(ROUND(CONV_RATE
373 ,6));
374 END IF;
375 RETURN (CONV_RATE_DSPLY);
376 END DSPLY_VOL_CNV_RATE;
377
378 FUNCTION ITEM_REV_WT_CALC(C_WEIGHT_CONV_RATE IN NUMBER
379 ,C_ITEM_REV_QTY IN NUMBER) RETURN NUMBER IS
380 CONV_RATE NUMBER;
381 FROM_QTY NUMBER;
382 CONV_WEIGHT NUMBER;
383 BEGIN
384 CONV_RATE := C_WEIGHT_CONV_RATE;
385 FROM_QTY := C_ITEM_REV_QTY;
386 IF CONV_RATE < 0 THEN
387 CONV_RATE := 0;
388 END IF;
389 CONV_WEIGHT := FROM_QTY * CONV_RATE;
390 RETURN (CONV_WEIGHT);
391 RETURN NULL;
392 EXCEPTION
393 WHEN OTHERS THEN
394 /*SRW.MESSAGE(12
395 ,'Wt Calc Error')*/NULL;
396 RETURN (0);
397 END ITEM_REV_WT_CALC;
398
399 FUNCTION ITEM_REV_VOL_CALC(C_VOL_CONV_RATE IN NUMBER
400 ,C_ITEM_REV_QTY IN NUMBER) RETURN NUMBER IS
401 CONV_RATE NUMBER;
402 FROM_QTY NUMBER;
403 CONV_VOL NUMBER;
404 BEGIN
405 CONV_RATE := C_VOL_CONV_RATE;
406 FROM_QTY := C_ITEM_REV_QTY;
407 IF CONV_RATE < 0 THEN
408 CONV_RATE := 0;
409 END IF;
410 CONV_VOL := FROM_QTY * CONV_RATE;
411 RETURN (CONV_VOL);
412 END ITEM_REV_VOL_CALC;
413
414 FUNCTION LOC_PCT_MAX_WT(MIL_MAX_WEIGHT IN NUMBER
415 ,C_GROSS_WT IN NUMBER) RETURN NUMBER IS
416 LOC_MAX_WT NUMBER;
417 SUM_ITEM_WT NUMBER;
418 PCT_MAX_WT NUMBER;
419 BEGIN
420 LOC_MAX_WT := MIL_MAX_WEIGHT;
421 SUM_ITEM_WT := C_GROSS_WT;
422 PCT_MAX_WT := 0;
423 IF LOC_MAX_WT = 0 THEN
424 PCT_MAX_WT := 0;
425 ELSE
426 PCT_MAX_WT := ROUND(((SUM_ITEM_WT / LOC_MAX_WT) * 100)
427 ,4);
428 END IF;
429 RETURN (PCT_MAX_WT);
430 RETURN NULL;
431 EXCEPTION
432 WHEN OTHERS THEN
433 RETURN (0);
434 END LOC_PCT_MAX_WT;
435
436 FUNCTION LOC_PCT_MAX_VOL(MIL_MAX_CUBIC_AREA IN NUMBER
437 ,C_GROSS_VOL IN NUMBER) RETURN NUMBER IS
438 LOC_MAX_VOL NUMBER;
439 SUM_ITEM_VOL NUMBER;
440 PCT_MAX_VOL NUMBER;
441 BEGIN
442 LOC_MAX_VOL := MIL_MAX_CUBIC_AREA;
443 SUM_ITEM_VOL := C_GROSS_VOL;
444 PCT_MAX_VOL := 0;
445 IF LOC_MAX_VOL = 0 THEN
446 PCT_MAX_VOL := 0;
447 ELSE
448 PCT_MAX_VOL := ROUND(((SUM_ITEM_VOL / LOC_MAX_VOL) * 100)
449 ,4);
450 END IF;
451 RETURN (PCT_MAX_VOL);
452 RETURN NULL;
453 EXCEPTION
454 WHEN OTHERS THEN
455 RETURN (0);
456 END LOC_PCT_MAX_VOL;
457
458 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
459 BEGIN
460 DECLARE
461 P_ORG_ID_CHAR VARCHAR2(100) := P_ORG;
462 BEGIN
463 FND_PROFILE.PUT('MFG_ORGANIZATION_ID'
464 ,P_ORG_ID_CHAR);
465 /*SRW.USER_EXIT('FND PUTPROFILE NAME="' || 'MFG_ORGANIZATION_ID' || '" FIELD="' || P_ORG_ID_CHAR || '"')*/NULL;
466 EXCEPTION
467 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
468 /*SRW.MESSAGE(020
469 ,'Failed in before report trigger, setting org profile ')*/NULL;
470 RAISE;
471 END;
472 BEGIN
473 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
474 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
475 EXCEPTION
476 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
477 /*SRW.MESSAGE(1
478 ,'Failed srwinit in before rpt trigger')*/NULL;
479 RAISE;
480 END;
481 BEGIN
482 NULL;
483 EXCEPTION
484 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
485 /*SRW.MESSAGE(5
486 ,'Failed flexsql loc select in before report trigger')*/NULL;
487 RAISE;
488 END;
489 BEGIN
490 NULL;
491 EXCEPTION
492 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
493 /*SRW.MESSAGE(15
494 ,'Failed flexsql loc where in before report trigger')*/NULL;
495 RAISE;
496 END;
497 BEGIN
498 NULL;
499 EXCEPTION
500 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
501 /*SRW.MESSAGE(16
502 ,'Failed flexsql loc order by in before report trigger. ')*/NULL;
503 RAISE;
504 END;
505 BEGIN
506 NULL;
507 EXCEPTION
508 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
509 /*SRW.MESSAGE(20
510 ,'Failed flexsql item select in before report trigger')*/NULL;
511 RAISE;
512 END;
513 BEGIN
514 NULL;
515 EXCEPTION
516 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
517 /*SRW.MESSAGE(25
518 ,'Failed flexsql item order by in before report trigger')*/NULL;
519 RAISE;
520 END;
521 RETURN (TRUE);
522 END BEFOREREPORT;
523
524 FUNCTION AFTERPFORM RETURN BOOLEAN IS
525 BEGIN
526 RETURN (TRUE);
527 END AFTERPFORM;
528
529 FUNCTION F_LOC_PROJ_TASK_NUMBER(P_LOC_FLEXDATA IN VARCHAR2
530 ,P_LOC IN VARCHAR2) RETURN CHAR IS
531 L_LOC_TEMP VARCHAR2(820);
532 CNT NUMBER := 0;
533 L_LOC_LEFT_STR VARCHAR2(820);
534 L_LOC_RIGHT_STR VARCHAR2(820);
535 L_PROJECT_ID MTL_PROJECT_V.PROJECT_ID%TYPE;
536 L_TASK_ID MTL_TASK_V.TASK_ID%TYPE;
537 L_PROJECT_NUMBER MTL_PROJECT_V.PROJECT_NUMBER%TYPE;
538 L_TASK_NUMBER MTL_TASK_V.TASK_NUMBER%TYPE;
539 L_LOC_DATA VARCHAR2(820);
540 L_DELIMITER VARCHAR2(10);
541 BEGIN
542 BEGIN
543 L_DELIMITER := FND_FLEX_EXT.GET_DELIMITER('INV'
544 ,'MTLL'
545 ,P_LOC_NUM);
546 EXCEPTION
547 WHEN OTHERS THEN
548 RETURN (P_LOC);
549 END;
550 L_LOC_TEMP := SUBSTR(P_LOC_FLEXDATA
551 ,1
552 ,INSTR(P_LOC_FLEXDATA
553 ,'SEGMENT19') - 1);
554 IF L_LOC_TEMP IS NULL THEN
555 RETURN (P_LOC);
556 END IF;
557 WHILE (INSTR(L_LOC_TEMP
558 ,'||''') > 0) LOOP
559
560 CNT := CNT + 1;
561 L_LOC_TEMP := SUBSTR(L_LOC_TEMP
562 ,1
563 ,INSTR(L_LOC_TEMP
564 ,'||''') - 1) || SUBSTR(L_LOC_TEMP
565 ,INSTR(L_LOC_TEMP
566 ,'||''') + 5);
567 END LOOP;
568 IF CNT = 0 THEN
569 CNT := 1;
570 L_LOC_LEFT_STR := NULL;
571 L_LOC_TEMP := SUBSTR(P_LOC
572 ,1
573 ,INSTR(P_LOC
574 ,L_DELIMITER
575 ,1
576 ,CNT));
577 ELSE
578 L_LOC_LEFT_STR := SUBSTR(P_LOC
579 ,1
580 ,INSTR(P_LOC
581 ,L_DELIMITER
582 ,1
583 ,CNT));
584 L_LOC_TEMP := SUBSTR(P_LOC
585 ,INSTR(P_LOC
586 ,L_DELIMITER
587 ,1
588 ,CNT) + 1);
589 END IF;
590 SELECT
591 DECODE(INSTR(L_LOC_TEMP
592 ,L_DELIMITER)
593 ,0
594 ,L_LOC_TEMP
595 ,SUBSTR(L_LOC_TEMP
596 ,1
597 ,INSTR(L_LOC_TEMP
598 ,L_DELIMITER) - 1)),
599 DECODE(INSTR(L_LOC_TEMP
600 ,L_DELIMITER)
601 ,0
602 ,NULL
603 ,SUBSTR(L_LOC_TEMP
604 ,INSTR(L_LOC_TEMP
605 ,L_DELIMITER)))
606 INTO L_PROJECT_ID,L_LOC_RIGHT_STR
607 FROM
608 DUAL;
609 BEGIN
610 SELECT
611 PROJECT_NUMBER
612 INTO L_PROJECT_NUMBER
613 FROM
614 MTL_PROJECT_V
615 WHERE PROJECT_ID = L_PROJECT_ID;
616 EXCEPTION
617 WHEN OTHERS THEN
618 RETURN (P_LOC);
619 END;
620 L_LOC_DATA := L_LOC_LEFT_STR || L_PROJECT_NUMBER || L_LOC_RIGHT_STR;
621 CNT := 0;
622 L_LOC_TEMP := SUBSTR(P_LOC_FLEXDATA
623 ,1
624 ,INSTR(P_LOC_FLEXDATA
625 ,'SEGMENT20') - 1);
626 IF L_LOC_TEMP IS NULL THEN
627 RETURN (L_LOC_DATA);
628 END IF;
629 WHILE (INSTR(L_LOC_TEMP
630 ,'||''') > 0) LOOP
631
632 CNT := CNT + 1;
633 L_LOC_TEMP := SUBSTR(L_LOC_TEMP
634 ,1
635 ,INSTR(L_LOC_TEMP
636 ,'||''') - 1) || SUBSTR(L_LOC_TEMP
637 ,INSTR(L_LOC_TEMP
638 ,'||''') + 5);
639 END LOOP;
640 L_LOC_LEFT_STR := L_LOC_LEFT_STR || L_PROJECT_NUMBER || L_DELIMITER;
641 L_LOC_TEMP := SUBSTR(P_LOC
642 ,INSTR(P_LOC
643 ,L_DELIMITER
644 ,1
645 ,CNT) + 1);
646 SELECT
647 DECODE(INSTR(L_LOC_TEMP
648 ,L_DELIMITER)
649 ,0
650 ,L_LOC_TEMP
651 ,SUBSTR(L_LOC_TEMP
652 ,1
653 ,INSTR(L_LOC_TEMP
654 ,L_DELIMITER) - 1)),
655 DECODE(INSTR(L_LOC_TEMP
656 ,L_DELIMITER)
657 ,0
658 ,NULL
659 ,SUBSTR(L_LOC_TEMP
660 ,INSTR(L_LOC_TEMP
661 ,L_DELIMITER)))
662 INTO L_TASK_ID,L_LOC_RIGHT_STR
663 FROM
664 DUAL;
665 BEGIN
666 SELECT
667 TASK_NUMBER
668 INTO L_TASK_NUMBER
669 FROM
670 MTL_TASK_V
671 WHERE PROJECT_ID = L_PROJECT_ID
672 AND TASK_ID = L_TASK_ID;
673 EXCEPTION
674 WHEN OTHERS THEN
675 RETURN (L_LOC_DATA);
676 END;
677 L_LOC_DATA := L_LOC_LEFT_STR || L_TASK_NUMBER || L_LOC_RIGHT_STR;
678 RETURN (L_LOC_DATA);
679 EXCEPTION
680 WHEN OTHERS THEN
681 RETURN (P_LOC);
682 END F_LOC_PROJ_TASK_NUMBER;
683
684 FUNCTION CF_LOC_HIFORMULA RETURN CHAR IS
685 BEGIN
686 RETURN F_LOC_PROJ_TASK_NUMBER(P_LOC_FLEXDATA
687 ,P_LOC_HI);
688 END CF_LOC_HIFORMULA;
689
690 FUNCTION CF_LOC_LOFORMULA RETURN CHAR IS
691 BEGIN
692 RETURN F_LOC_PROJ_TASK_NUMBER(P_LOC_FLEXDATA
693 ,P_LOC_LO);
694 END CF_LOC_LOFORMULA;
695
696 END INV_INVIRILC_XMLP_PKG;
697