1 PACKAGE BODY ONT_OEXOEOCR_XMLP_PKG AS
2 /* $Header: OEXOEOCRB.pls 120.3.12020000.2 2013/03/12 08:38:42 adandu ship $ */
3 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
4 BEGIN
5 BEGIN
6 BEGIN
7 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
8 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
9 EXCEPTION
10 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
11 BEGIN
12 /*SRW.MESSAGE(1000
13 ,'Failed in BEFORE REPORT trigger')*/NULL;
14 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
15 END;
16 END;
17 BEGIN
18 P_ORGANIZATION_ID := MO_GLOBAL.GET_CURRENT_ORG_ID;
19 END;
20 BEGIN
21 IF P_ITEM IS NOT NULL THEN
22 SELECT
23 CONCATENATED_SEGMENTS
24 INTO P_ITEM_NAME
25 FROM
26 MTL_SYSTEM_ITEMS_KFV
27 WHERE INVENTORY_ITEM_ID = P_ITEM
28 AND CUSTOMER_ORDER_ENABLED_FLAG = 'Y'
29 AND BOM_ITEM_TYPE in ( 1 , 4 )
30 AND ORGANIZATION_ID = OE_SYS_PARAMETERS.VALUE('MASTER_ORGANIZATION_ID');
31 END IF;
32 END;
33 DECLARE
34 L_COMPANY_NAME VARCHAR2(100);
35 L_FUNCTIONAL_CURRENCY VARCHAR2(15);
36 BEGIN
37 SELECT
38 SOB.NAME,
39 SOB.CURRENCY_CODE
40 INTO L_COMPANY_NAME,L_FUNCTIONAL_CURRENCY
41 FROM
42 GL_SETS_OF_BOOKS SOB,
43 FND_CURRENCIES CUR
44 WHERE SOB.SET_OF_BOOKS_ID = P_SOB_ID
45 AND SOB.CURRENCY_CODE = CUR.CURRENCY_CODE;
46 RP_COMPANY_NAME := L_COMPANY_NAME;
47 RP_FUNCTIONAL_CURRENCY := L_FUNCTIONAL_CURRENCY;
48 EXCEPTION
49 WHEN NO_DATA_FOUND THEN
50 NULL;
51 END;
52 BEGIN
53 /*SRW.REFERENCE(P_ITEM_FLEX_CODE)*/NULL;
54 /*SRW.REFERENCE(P_ITEM_STRUCTURE_NUM)*/NULL;
55 IF P_ITEM IS NOT NULL THEN
56 LP_ITEM_FLEX_ALL_SEG := ' and ' || RP_ITEM_FLEX_ALL_SEG_WHERE;
57 ELSE
58 LP_ITEM_FLEX_ALL_SEG := ' ';
59 END IF;
60 EXCEPTION
61 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
62 /*SRW.MESSAGE(1
63 ,'Failed in before report trigger:MSTK')*/NULL;
64 END;
65 DECLARE
66 L_REPORT_NAME VARCHAR2(240);
67 BEGIN
68 SELECT
69 CP.USER_CONCURRENT_PROGRAM_NAME
70 INTO L_REPORT_NAME
71 FROM
72 FND_CONCURRENT_PROGRAMS_VL CP,
73 FND_CONCURRENT_REQUESTS CR
74 WHERE CR.REQUEST_ID = P_CONC_REQUEST_ID
75 AND CP.APPLICATION_ID = CR.PROGRAM_APPLICATION_ID
76 AND CP.CONCURRENT_PROGRAM_ID = CR.CONCURRENT_PROGRAM_ID;
77 RP_REPORT_NAME := SUBSTR(L_REPORT_NAME,1,INSTR(L_REPORT_NAME,' (XML)'));
78 EXCEPTION
79 WHEN NO_DATA_FOUND THEN
80 RP_REPORT_NAME := 'Cancelled Orders Reason Detail Report';
81 END;
82 END;
83 LP_ORDER_BY := UPPER(P_ORDER_BY);
84 LP_ORDER_DATE_LOW := TO_CHAR(P_ORDER_DATE_LOW,'DD-MON-YY');
85 LP_ORDER_DATE_HIGH := TO_CHAR(P_ORDER_DATE_HIGH,'DD-MON-YY');
86 LP_CANCEL_DATE_LOW := TO_CHAR(P_CANCEL_DATE_LOW,'DD-MON-YY');
87 LP_CANCEL_DATE_HIGH := TO_CHAR(P_CANCEL_DATE_HIGH,'DD-MON-YY');
88 RETURN (TRUE);
89 END BEFOREREPORT;
90
91 FUNCTION AFTERREPORT RETURN BOOLEAN IS
92 BEGIN
93 BEGIN
94 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
95 EXCEPTION
96 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
97 /*SRW.MESSAGE(1
98 ,'Failed in AFTER REPORT TRIGGER')*/NULL;
99 RETURN (FALSE);
100 END;
101 RETURN (TRUE);
102 END AFTERREPORT;
103
104 FUNCTION P_ITEM_FLEX_CODEVALIDTRIGGER RETURN BOOLEAN IS
105 BEGIN
106 RETURN (TRUE);
107 END P_ITEM_FLEX_CODEVALIDTRIGGER;
108
109 FUNCTION P_USE_FUNCTIONAL_CURRENCYVALID RETURN BOOLEAN IS
110 BEGIN
111 RETURN (TRUE);
112 END P_USE_FUNCTIONAL_CURRENCYVALID;
113
114 FUNCTION AFTERPFORM RETURN BOOLEAN IS
115 BEGIN
116 BEGIN
117 IF P_ORDER_NUM_LOW IS NOT NULL AND P_ORDER_NUM_HIGH IS NOT NULL THEN
118 LP_ORDER_NUM := ' AND h.order_number between to_number(:p_order_num_low) and to_number(:p_order_num_high) ';
119 ELSIF (P_ORDER_NUM_LOW IS NOT NULL) THEN
120 LP_ORDER_NUM := 'and h.order_number >= to_number(:p_order_num_low) ';
121 ELSIF (P_ORDER_NUM_HIGH IS NOT NULL) THEN
122 LP_ORDER_NUM := 'and h.order_number <= to_number(:p_order_num_high) ';
123 ELSE
124 LP_ORDER_NUM := ' ';
125 END IF;
126 IF P_SALESREP_LOW IS NOT NULL AND P_SALESREP_HIGH IS NOT NULL THEN
127 LP_SALESREP := ' AND nvl(sr.name,''zzzzzz'') between :p_salesrep_low and :p_salesrep_high ';
128 ELSIF (P_SALESREP_LOW IS NOT NULL) THEN
129 LP_SALESREP := 'and sr.name >= :p_salesrep_low ';
130 ELSIF (P_SALESREP_HIGH IS NOT NULL) THEN
131 LP_SALESREP := 'and sr.name <= :p_salesrep_high ';
132 ELSE
133 LP_SALESREP := ' '; --praveen
134 END IF;
135 IF P_CANCEL_DATE_LOW IS NOT NULL AND P_CANCEL_DATE_HIGH IS NOT NULL THEN
136 LP_CANCEL_DATE := ' AND trunc(lh.hist_creation_date, ''DD'')
137 between trunc(:p_cancel_date_low, ''DD'')
138 and trunc(:p_cancel_date_high, ''DD'') ';
139 ELSIF (P_CANCEL_DATE_LOW IS NOT NULL) THEN
140 LP_CANCEL_DATE := ' AND trunc(lh.hist_creation_date, ''DD'')
141 >= trunc(:p_cancel_date_low, ''DD'') ';
142 ELSIF (P_CANCEL_DATE_HIGH IS NOT NULL) THEN
143 LP_CANCEL_DATE := ' AND trunc(lh.hist_creation_date, ''DD'')
144 <= trunc(:p_cancel_date_high, ''DD'') ';
145 ELSE
146 LP_CANCEL_DATE := ' ';
147 END IF;
148 IF P_ORDER_DATE_LOW IS NOT NULL AND P_ORDER_DATE_HIGH IS NOT NULL THEN
149 LP_ORDER_DATE := ' AND trunc(h.ordered_date, ''DD'')
150 between trunc(:p_order_date_low, ''DD'')
151 and trunc(:p_order_date_high, ''DD'') ';
152 ELSIF (P_ORDER_DATE_LOW IS NOT NULL) THEN
153 LP_ORDER_DATE := ' AND trunc(h.ordered_date, ''DD'')
154 >= trunc(:p_order_date_low, ''DD'') ';
155 ELSIF (P_ORDER_DATE_HIGH IS NOT NULL) THEN
156 LP_ORDER_DATE := ' AND trunc(h.ordered_date, ''DD'')
157 <= trunc(:p_order_date_high, ''DD'') ';
158 ELSE
159 LP_ORDER_DATE := ' ';
160 END IF;
161 IF P_CUSTOMER_NAME_LOW IS NOT NULL AND P_CUSTOMER_NAME_HIGH IS NOT NULL THEN
162 LP_CUSTOMER_NAME := ' AND org.name between :p_customer_name_low and :p_customer_name_high ';
163 ELSIF (P_CUSTOMER_NAME_LOW IS NOT NULL) THEN
164 LP_CUSTOMER_NAME := 'and org.name >= :p_customer_name_low ';
165 ELSIF (P_CUSTOMER_NAME_HIGH IS NOT NULL) THEN
166 LP_CUSTOMER_NAME := 'and org.name <= :p_customer_name_high ';
167 ELSE
168 LP_CUSTOMER_NAME := ' '; --praveen
169 END IF;
170 IF P_CANCELLED_BY_LOW IS NOT NULL AND P_CANCELLED_BY_HIGH IS NOT NULL THEN
171 LP_CANCELLED_BY := ' and fnd_user.user_name between :p_cancelled_by_low and
172 :p_cancelled_by_high ';
173 ELSIF (P_CANCELLED_BY_LOW IS NOT NULL) THEN
174 LP_CANCELLED_BY := 'and fnd_user.user_name >= :p_cancelled_by_low ';
175 ELSIF (P_CANCELLED_BY_HIGH IS NOT NULL) THEN
176 LP_CANCELLED_BY := ' and fnd_user.user_name <= :p_cancelled_by_high ';
177 ELSE
178 LP_CANCELLED_BY := ' ';
179 END IF;
180 IF P_CANCEL_REASON IS NOT NULL THEN
181 LP_CANCEL_REASON := ' and r.reason_code = :p_cancel_reason ';
182 ELSE
183 LP_CANCEL_REASON := ' ';
184 END IF;
185 IF P_ORDER_NUM_LOW = P_ORDER_NUM_HIGH THEN
186 NULL;
187 ELSE
188 IF P_ORDER_CATEGORY IS NOT NULL THEN
189 IF P_ORDER_CATEGORY = 'SALES' THEN
190 LP_ORDER_CATEGORY := 'and h.order_category_code in (''ORDER'', ''MIXED'') ';
191 ELSIF P_ORDER_CATEGORY = 'CREDIT' THEN
192 LP_ORDER_CATEGORY := 'and h.order_category_code in (''RETURN'', ''MIXED'') ';
193 ELSIF P_ORDER_CATEGORY = 'ALL' THEN
194 LP_ORDER_CATEGORY := ' '; --praveen
195 END IF;
196 ELSE
197 LP_ORDER_CATEGORY := 'and h.order_category_code in (''ORDER'', ''MIXED'') ';
198 END IF;
199 END IF;
200 IF P_LINE_CATEGORY IS NOT NULL THEN
201 IF P_LINE_CATEGORY = 'SALES' THEN
202 LP_LINE_CATEGORY := 'and lh.line_category_code = ''ORDER'' ';
203 ELSIF P_LINE_CATEGORY = 'CREDIT' THEN
204 LP_LINE_CATEGORY := 'and lh.line_category_code = ''RETURN'' ';
205 ELSIF P_LINE_CATEGORY = 'ALL' THEN
206 LP_LINE_CATEGORY := ' '; --praveen
207 END IF;
208 ELSE
209 LP_LINE_CATEGORY := 'and lh.line_category_code = ''ORDER'' ';
210 END IF;
211 END;
212 RETURN (TRUE);
213 END AFTERPFORM;
214
215 FUNCTION C_SET_LBLFORMULA RETURN VARCHAR2 IS
216 BEGIN
217 BEGIN
218 /*SRW.REFERENCE(P_ORDER_BY)*/NULL;
219 IF UPPER(P_ORDER_BY) = 'SALESREP' THEN
220 RP_SALES_REASON_LBL := 'Salesperson';
221 RP_SALES_REASON_LBL_2 := 'Salesperson: ';
222 ELSIF UPPER(P_ORDER_BY) = 'CANCEL_REASON' THEN
223 RP_SALES_REASON_LBL := 'Cancel Reason';
224 RP_SALES_REASON_LBL_2 := 'Cancel Reason: ';
225 ELSIF UPPER(P_ORDER_BY) = 'ORDER_DATE' THEN
226 RP_DATE_LBL := 'Order Date';
227 ELSIF UPPER(P_ORDER_BY) = 'CANCEL_DATE' THEN
228 RP_DATE_LBL := 'Cancel Date';
229 END IF;
230 IF P_CUSTOMER_NAME_LOW IS NOT NULL OR P_CUSTOMER_NAME_HIGH IS NOT NULL THEN
231 RP_CUSTOMER_RANGE := 'From ' || NVL(SUBSTR(P_CUSTOMER_NAME_LOW
232 ,1
233 ,16)
234 ,' ') || ' To ' || NVL(SUBSTR(P_CUSTOMER_NAME_HIGH
235 ,1
236 ,16)
237 ,' ');
238 END IF;
239 IF P_SALESREP_LOW IS NOT NULL OR P_SALESREP_HIGH IS NOT NULL THEN
240 RP_SALESPERSON_RANGE := 'From ' || NVL(SUBSTR(P_SALESREP_LOW
241 ,1
242 ,16)
243 ,' ') || ' To ' || NVL(SUBSTR(P_SALESREP_HIGH
244 ,1
245 ,16)
246 ,' ');
247 END IF;
248 IF P_CANCEL_DATE_LOW IS NOT NULL AND P_CANCEL_DATE_HIGH IS NOT NULL THEN
249 RP_CANCEL_DATE_RANGE := 'From ' || LP_CANCEL_DATE_LOW || ' To ' || LP_CANCEL_DATE_HIGH;
250 ELSIF P_CANCEL_DATE_LOW IS NOT NULL THEN
251 RP_CANCEL_DATE_RANGE := 'From ' || LP_CANCEL_DATE_LOW || ' To ' || ' ';
252 ELSIF P_CANCEL_DATE_HIGH IS NOT NULL THEN
253 RP_CANCEL_DATE_RANGE := 'From ' || ' ' || ' To ' || LP_CANCEL_DATE_HIGH;
254 END IF;
255 IF P_ORDER_DATE_LOW IS NOT NULL AND P_ORDER_DATE_HIGH IS NOT NULL THEN
256 RP_ORDER_DATE_RANGE := 'From ' || LP_ORDER_DATE_LOW || ' To ' || LP_ORDER_DATE_HIGH;
257 ELSIF P_ORDER_DATE_LOW IS NOT NULL THEN
258 RP_ORDER_DATE_RANGE := 'From ' || LP_ORDER_DATE_LOW || ' To ' || ' ';
259 ELSIF P_ORDER_DATE_HIGH IS NOT NULL THEN
260 RP_ORDER_DATE_RANGE := 'From ' || ' ' || ' To ' || LP_ORDER_DATE_HIGH;
261 END IF;
262 IF P_ORDER_NUM_LOW IS NOT NULL OR P_ORDER_NUM_HIGH IS NOT NULL THEN
263 RP_ORDER_RANGE := 'From ' || NVL(P_ORDER_NUM_LOW
264 ,' ') || ' To ' || NVL(P_ORDER_NUM_HIGH
265 ,' ');
266 END IF;
267 IF P_CANCELLED_BY_LOW IS NOT NULL OR P_CANCELLED_BY_HIGH IS NOT NULL THEN
268 RP_CANCELLED_BY_RANGE := 'From ' || NVL(SUBSTR(P_CANCELLED_BY_LOW
269 ,1
270 ,16)
271 ,' ') || ' To ' || NVL(SUBSTR(P_CANCELLED_BY_HIGH
272 ,1
273 ,16)
274 ,' ');
275 END IF;
276 IF P_ORDER_BY IS NOT NULL THEN
277 DECLARE
278 ORDER_BY VARCHAR2(80);
279 L_LOOKUP_TYPE VARCHAR2(80);
280 L_LOOKUP_CODE VARCHAR2(80);
281 BEGIN
282 L_LOOKUP_TYPE := 'OEXOEOCR SORT BY';
283 L_LOOKUP_CODE := P_ORDER_BY;
284 SELECT
285 MEANING
286 INTO ORDER_BY
287 FROM
288 OE_LOOKUPS
289 WHERE LOOKUP_TYPE = L_LOOKUP_TYPE
290 AND LOOKUP_CODE = L_LOOKUP_CODE;
291 RP_ORDER_BY := ORDER_BY;
292 EXCEPTION
293 WHEN NO_DATA_FOUND THEN
294 RP_ORDER_BY := P_ORDER_BY;
295 END;
296 END IF;
297 IF P_CANCEL_REASON IS NOT NULL THEN
298 DECLARE
299 MEANING VARCHAR2(80);
300 L_LOOKUP_TYPE VARCHAR2(80);
301 L_LOOKUP_CODE VARCHAR2(80);
302 BEGIN
303 L_LOOKUP_TYPE := 'CANCEL_CODE';
304 L_LOOKUP_CODE := P_CANCEL_REASON;
305 SELECT
306 MEANING
307 INTO MEANING
308 FROM
309 OE_LOOKUPS
310 WHERE LOOKUP_TYPE = L_LOOKUP_TYPE
311 AND LOOKUP_CODE = L_LOOKUP_CODE;
312 RP_CANCEL_REASON := MEANING;
313 EXCEPTION
314 WHEN NO_DATA_FOUND THEN
315 RP_CANCEL_REASON := P_CANCEL_REASON;
316 END;
317 END IF;
318 DECLARE
319 MEANING VARCHAR2(80);
320 L_LOOKUP_TYPE VARCHAR2(80);
321 L_LOOKUP_CODE VARCHAR2(80);
322 BEGIN
323 L_LOOKUP_TYPE := 'YES_NO';
324 L_LOOKUP_CODE := P_USE_FUNCTIONAL_CURRENCY;
325 SELECT
326 MEANING
327 INTO MEANING
328 FROM
329 FND_LOOKUPS
330 WHERE LOOKUP_TYPE = L_LOOKUP_TYPE
331 AND LOOKUP_CODE = L_LOOKUP_CODE;
332 RP_USE_FUNCTIONAL_CURRENCY := MEANING;
333 EXCEPTION
334 WHEN NO_DATA_FOUND THEN
335 RP_USE_FUNCTIONAL_CURRENCY := P_USE_FUNCTIONAL_CURRENCY;
336 END;
337 DECLARE
338 ITEM_DISPLAY_MEANING VARCHAR2(80);
339 L_LOOKUP_TYPE VARCHAR2(80);
340 L_LOOKUP_CODE VARCHAR2(80);
341 BEGIN
342 L_LOOKUP_TYPE := 'ITEM_DISPLAY_CODE';
343 L_LOOKUP_CODE := P_PRINT_DESCRIPTION;
344 SELECT
345 MEANING
346 INTO ITEM_DISPLAY_MEANING
347 FROM
348 OE_LOOKUPS
349 WHERE LOOKUP_TYPE = L_LOOKUP_TYPE
350 AND LOOKUP_CODE = L_LOOKUP_CODE;
351 RP_FLEX_OR_DESC := ITEM_DISPLAY_MEANING;
352 END;
353 RETURN (1);
354 END;
355 RETURN NULL;
356 END C_SET_LBLFORMULA;
357
358 FUNCTION C_GL_CONV_RATEFORMULA(CURRENCY_CODE IN VARCHAR2
359 ,ORD_DATE IN DATE
360 ,CONVERSION_TYPE_CODE IN VARCHAR2
361 ,CONVERSION_RATE IN NUMBER) RETURN NUMBER IS
362 BEGIN
363 DECLARE
364 GL_RATE NUMBER;
365 BEGIN
366 /*SRW.REFERENCE(CURRENCY_CODE)*/NULL;
367 /*SRW.REFERENCE(ORD_DATE)*/NULL;
368 /*SRW.REFERENCE(CONVERSION_TYPE_CODE)*/NULL;
369 IF P_USE_FUNCTIONAL_CURRENCY = 'Y' THEN
370 IF CURRENCY_CODE = RP_FUNCTIONAL_CURRENCY THEN
371 RETURN (1);
372 ELSE
373 IF CONVERSION_RATE IS NULL THEN
374 GL_RATE := GET_RATE(P_SOB_ID
375 ,CURRENCY_CODE
376 ,ORD_DATE
377 ,CONVERSION_TYPE_CODE);
378 RETURN (GL_RATE);
379 ELSE
380 RETURN (CONVERSION_RATE);
381 END IF;
382 END IF;
383 ELSE
384 RETURN (1);
385 END IF;
386 EXCEPTION
387 WHEN NO_DATA_FOUND THEN
388 RETURN (-1);
389 WHEN OTHERS THEN
390 RETURN (-1);
391 END;
392 RETURN NULL;
393 END C_GL_CONV_RATEFORMULA;
394
395 FUNCTION C_AMOUNTFORMULA(CALC_AMOUNT IN NUMBER
396 ,C_GL_CONV_RATE IN NUMBER
397 ,C_PRECISION IN NUMBER) RETURN NUMBER IS
398 BEGIN
399 DECLARE
400 ORDER_AMOUNT NUMBER(14,2);
401
402 BEGIN
403 /*SRW.REFERENCE(CALC_AMOUNT)*/NULL;
404 /*SRW.REFERENCE(C_GL_CONV_RATE)*/NULL;
405 /*SRW.REFERENCE(C_PRECISION)*/NULL;
406 IF P_USE_FUNCTIONAL_CURRENCY = 'Y' THEN
407 IF C_GL_CONV_RATE <> -1 THEN
408 SELECT
409 C_GL_CONV_RATE * CALC_AMOUNT
410 INTO ORDER_AMOUNT
411 FROM
412 DUAL;
413 RETURN (ROUND(ORDER_AMOUNT
414 ,C_PRECISION));
415 ELSE
416 RETURN (0);
417 END IF;
418 ELSE
419 RETURN (ROUND(CALC_AMOUNT
420 ,C_PRECISION));
421 END IF;
422 END;
423 RETURN NULL;
424 END C_AMOUNTFORMULA;
425
426 FUNCTION C_CURRENCY_CODEFORMULA(CURRENCY_CODE IN VARCHAR2) RETURN VARCHAR2 IS
427 BEGIN
428 /*SRW.REFERENCE(CURRENCY_CODE)*/NULL;
429 IF P_USE_FUNCTIONAL_CURRENCY = 'Y' THEN
430 RETURN (RP_FUNCTIONAL_CURRENCY);
431 ELSE
432 RETURN (CURRENCY_CODE);
433 END IF;
434 RETURN NULL;
435 END C_CURRENCY_CODEFORMULA;
436
437 FUNCTION PR_ORDER_CATEGORYFORMULA RETURN CHAR IS
438 BEGIN
439 DECLARE
440 L_MEANING VARCHAR2(80);
441 L_LOOKUP_TYPE VARCHAR2(80);
442 L_LOOKUP_CODE VARCHAR2(80);
443 BEGIN
444 L_LOOKUP_TYPE := 'REPORT_ORDER_CATEGORY';
445 L_LOOKUP_CODE := P_ORDER_CATEGORY;
446 SELECT
447 MEANING
448 INTO L_MEANING
449 FROM
450 OE_LOOKUPS
451 WHERE LOOKUP_TYPE = L_LOOKUP_TYPE
452 AND LOOKUP_CODE = L_LOOKUP_CODE;
453 RETURN (L_MEANING);
454 EXCEPTION
455 WHEN NO_DATA_FOUND THEN
456 RETURN (NULL);
457 END;
458 END PR_ORDER_CATEGORYFORMULA;
459
460 FUNCTION RP_LINE_CATEGORYFORMULA RETURN VARCHAR2 IS
461 BEGIN
462 DECLARE
463 L_MEANING VARCHAR2(80);
464 L_LOOKUP_TYPE VARCHAR2(80);
465 L_LOOKUP_CODE VARCHAR2(80);
466 BEGIN
467 L_LOOKUP_TYPE := 'REPORT_LINE_DISPLAY';
468 L_LOOKUP_CODE := P_LINE_CATEGORY;
469 SELECT
470 MEANING
471 INTO L_MEANING
472 FROM
473 OE_LOOKUPS
474 WHERE LOOKUP_TYPE = L_LOOKUP_TYPE
475 AND LOOKUP_CODE = L_LOOKUP_CODE;
476 RETURN (L_MEANING);
477 EXCEPTION
478 WHEN NO_DATA_FOUND THEN
479 RETURN (NULL);
480 END;
481 END RP_LINE_CATEGORYFORMULA;
482
483 FUNCTION CF_UNIT1FORMULA(UNIT1 IN VARCHAR2) RETURN CHAR IS
484 BEGIN
485 CP_UNIT1 := UNIT1;
486 RETURN 1;
487 END CF_UNIT1FORMULA;
488
489 FUNCTION CF_UNIT2FORMULA(UNIT2 IN VARCHAR2) RETURN CHAR IS
490 BEGIN
491 CP_UNIT2 := UNIT2;
492 RETURN 1;
493 END CF_UNIT2FORMULA;
494
495 FUNCTION C_CANCELLED_QTYFORMULA(LINEID IN NUMBER
496 ,CANCELLED_QTY IN NUMBER) RETURN NUMBER IS
497 BEGIN
498 DECLARE
499 HIST_LINES NUMBER;
500 CAN_QTY_1 NUMBER;
501 ORD_QTY_MAX NUMBER;
502 CAN_QTY_MAX NUMBER;
503 CAN_QTY_FINAL1 NUMBER;
504 CAN_QTY_FINAL2 NUMBER;
505 BEGIN
506 SELECT
507 COUNT(1)
508 INTO HIST_LINES
509 FROM
510 OE_ORDER_LINES_HISTORY
511 WHERE LINE_ID = LINEID
512 AND HIST_TYPE_CODE = 'CANCELLATION';
513 IF HIST_LINES = 1 THEN
514 SELECT
515 CANCELLED_QUANTITY
516 INTO CAN_QTY_1
517 FROM
518 OE_ORDER_LINES_ALL
519 WHERE LINE_ID = LINEID;
520 RETURN (CAN_QTY_1);
521 ELSE
522 SELECT
523 MAX(ORDERED_QUANTITY),
524 MAX(CANCELLED_QUANTITY)
525 INTO ORD_QTY_MAX,CAN_QTY_MAX
526 FROM
527 OE_ORDER_LINES_HISTORY
528 WHERE LINE_ID = LINEID
529 AND HIST_TYPE_CODE = 'CANCELLATION';
530 IF CANCELLED_QTY = CAN_QTY_MAX THEN
531 SELECT
532 ( CANCELLED_QUANTITY - CAN_QTY_MAX )
533 INTO CAN_QTY_FINAL1
534 FROM
535 OE_ORDER_LINES_ALL
536 WHERE LINE_ID = LINEID;
537 RETURN (CAN_QTY_FINAL1);
538 ELSE
539 SELECT
540 MIN(CANCELLED_QUANTITY)
541 INTO CAN_QTY_FINAL2
542 FROM
543 OE_ORDER_LINES_HISTORY
544 WHERE LINE_ID = LINEID
545 AND CANCELLED_QUANTITY > CANCELLED_QTY
546 AND HIST_TYPE_CODE = 'CANCELLATION';
547 RETURN (CAN_QTY_FINAL2 - CANCELLED_QTY);
548 END IF;
549 END IF;
550 EXCEPTION
551 WHEN NO_DATA_FOUND THEN
552 RETURN (NULL);
553 END;
554 END C_CANCELLED_QTYFORMULA;
555
556 FUNCTION CALC_AMOUNTFORMULA(LINEID IN NUMBER
557 ,CANCELLED_QTY IN NUMBER) RETURN NUMBER IS
558 BEGIN
559 DECLARE
560 HIST_LINES NUMBER;
561 CAN_QTY_1 NUMBER;
562 ORD_QTY_MAX NUMBER;
563 CAN_QTY_MAX NUMBER;
564 CAN_QTY_FINAL1 NUMBER;
565 CAN_QTY_FINAL2 NUMBER;
566 UNIT_SELLING_PRICE_VAR NUMBER;
567 BEGIN
568 SELECT
569 COUNT(1)
570 INTO HIST_LINES
571 FROM
572 OE_ORDER_LINES_HISTORY
573 WHERE LINE_ID = LINEID
574 AND HIST_TYPE_CODE = 'CANCELLATION';
575 IF HIST_LINES = 1 THEN
576 SELECT
577 CANCELLED_QUANTITY,
578 UNIT_SELLING_PRICE
579 INTO CAN_QTY_1,UNIT_SELLING_PRICE_VAR
580 FROM
581 OE_ORDER_LINES_ALL
582 WHERE LINE_ID = LINEID;
583 RETURN (CAN_QTY_1 * UNIT_SELLING_PRICE_VAR);
584 ELSE
585 SELECT
586 MAX(ORDERED_QUANTITY),
587 MAX(CANCELLED_QUANTITY)
588 INTO ORD_QTY_MAX,CAN_QTY_MAX
589 FROM
590 OE_ORDER_LINES_HISTORY
591 WHERE LINE_ID = LINEID
592 AND HIST_TYPE_CODE = 'CANCELLATION';
593 IF CANCELLED_QTY = CAN_QTY_MAX THEN
594 SELECT
595 ( CANCELLED_QUANTITY - CAN_QTY_MAX ),
596 UNIT_SELLING_PRICE
597 INTO CAN_QTY_FINAL1,UNIT_SELLING_PRICE_VAR
598 FROM
599 OE_ORDER_LINES_ALL
600 WHERE LINE_ID = LINEID;
601 RETURN (CAN_QTY_FINAL1 * UNIT_SELLING_PRICE_VAR);
602 ELSE
603 SELECT
604 MIN(CANCELLED_QUANTITY)
605 INTO CAN_QTY_FINAL2
606 FROM
607 OE_ORDER_LINES_HISTORY
608 WHERE LINE_ID = LINEID
609 AND CANCELLED_QUANTITY > CANCELLED_QTY
610 AND HIST_TYPE_CODE = 'CANCELLATION';
611
612 BEGIN--Added for bug 7135763
613 SELECT
614 DISTINCT
615 UNIT_SELLING_PRICE
616 INTO UNIT_SELLING_PRICE_VAR
617 FROM
618 OE_ORDER_LINES_HISTORY
619 WHERE LINE_ID = LINEID
620 AND CANCELLED_QUANTITY = CAN_QTY_FINAL2
621 AND HIST_TYPE_CODE = 'CANCELLATION';
622 RETURN ((CAN_QTY_FINAL2 - CANCELLED_QTY) * UNIT_SELLING_PRICE_VAR);
623 EXCEPTION --Added the exception block to fix the bug 7135763
624 WHEN TOO_MANY_ROWS THEN
625 oe_debug_pub.add('Multiple records returned while fetching the Unit Selling Price for line id '||LINEID||' and the cancelled quantity '||CAN_QTY_FINAL2||' from the order lines history');
626 RETURN (NULL);
627 END;
628
629 END IF;
630 END IF;
631 EXCEPTION
632 WHEN NO_DATA_FOUND THEN
633 RETURN (NULL);
634 END;
635 END CALC_AMOUNTFORMULA;
636
637 FUNCTION C_PRECISIONFORMULA(CURRENCY_CODE IN VARCHAR2) RETURN NUMBER IS
638 BEGIN
639 DECLARE
640 W_PRECISION NUMBER;
641 BEGIN
642 SELECT
643 PRECISION
644 INTO W_PRECISION
645 FROM
646 FND_CURRENCIES CUR
647 WHERE CUR.CURRENCY_CODE = C_PRECISIONFORMULA.CURRENCY_CODE;
648 RETURN (W_PRECISION);
649 EXCEPTION
650 WHEN NO_DATA_FOUND THEN
651 W_PRECISION := 2;
652 RETURN (W_PRECISION);
653 END;
654 RETURN NULL;
655 END C_PRECISIONFORMULA;
656
657 FUNCTION RP_REPORT_NAME_P RETURN VARCHAR2 IS
658 BEGIN
659 RETURN RP_REPORT_NAME;
660 END RP_REPORT_NAME_P;
661
662 FUNCTION RP_SUB_TITLE_P RETURN VARCHAR2 IS
663 BEGIN
664 RETURN RP_SUB_TITLE;
665 END RP_SUB_TITLE_P;
666
667 FUNCTION RP_COMPANY_NAME_P RETURN VARCHAR2 IS
668 BEGIN
669 RETURN RP_COMPANY_NAME;
670 END RP_COMPANY_NAME_P;
671
672 FUNCTION RP_FUNCTIONAL_CURRENCY_P RETURN VARCHAR2 IS
673 BEGIN
674 RETURN RP_FUNCTIONAL_CURRENCY;
675 END RP_FUNCTIONAL_CURRENCY_P;
676
677 FUNCTION RP_DATA_FOUND_P RETURN VARCHAR2 IS
678 BEGIN
679 RETURN RP_DATA_FOUND;
680 END RP_DATA_FOUND_P;
681
682 FUNCTION RP_ITEM_FLEX_LPROMPT_P RETURN VARCHAR2 IS
683 BEGIN
684 RETURN RP_ITEM_FLEX_LPROMPT;
685 END RP_ITEM_FLEX_LPROMPT_P;
686
687 FUNCTION RP_ITEM_FLEX_ALL_SEG_P RETURN VARCHAR2 IS
688 BEGIN
689 RETURN RP_ITEM_FLEX_ALL_SEG;
690 END RP_ITEM_FLEX_ALL_SEG_P;
691
692 FUNCTION RP_ITEM_FLEX_APROMPT_P RETURN VARCHAR2 IS
693 BEGIN
694 RETURN RP_ITEM_FLEX_APROMPT;
695 END RP_ITEM_FLEX_APROMPT_P;
696
697 FUNCTION RP_SALES_REASON_LBL_P RETURN VARCHAR2 IS
698 BEGIN
699 RETURN RP_SALES_REASON_LBL;
700 END RP_SALES_REASON_LBL_P;
701
702 FUNCTION RP_CUSTOMER_RANGE_P RETURN VARCHAR2 IS
703 BEGIN
704 RETURN RP_CUSTOMER_RANGE;
705 END RP_CUSTOMER_RANGE_P;
706
707 FUNCTION RP_SALESPERSON_RANGE_P RETURN VARCHAR2 IS
708 BEGIN
709 RETURN RP_SALESPERSON_RANGE;
710 END RP_SALESPERSON_RANGE_P;
711
712 FUNCTION RP_ORDER_RANGE_P RETURN VARCHAR2 IS
713 BEGIN
714 RETURN RP_ORDER_RANGE;
715 END RP_ORDER_RANGE_P;
716
717 FUNCTION RP_CANCEL_DATE_RANGE_P RETURN VARCHAR2 IS
718 BEGIN
719 RETURN RP_CANCEL_DATE_RANGE;
720 END RP_CANCEL_DATE_RANGE_P;
721
722 FUNCTION RP_SALES_REASON_LBL_2_P RETURN VARCHAR2 IS
723 BEGIN
724 RETURN RP_SALES_REASON_LBL_2;
725 END RP_SALES_REASON_LBL_2_P;
726
727 FUNCTION RP_ORDER_BY_P RETURN VARCHAR2 IS
728 BEGIN
729 RETURN RP_ORDER_BY;
730 END RP_ORDER_BY_P;
731
732 FUNCTION RP_CANCEL_REASON_P RETURN VARCHAR2 IS
733 BEGIN
734 RETURN RP_CANCEL_REASON;
735 END RP_CANCEL_REASON_P;
736
737 FUNCTION RP_USE_FUNCTIONAL_CURRENCY_P RETURN VARCHAR2 IS
738 BEGIN
739 RETURN RP_USE_FUNCTIONAL_CURRENCY;
740 END RP_USE_FUNCTIONAL_CURRENCY_P;
741
742 FUNCTION RP_FLEX_OR_DESC_P RETURN VARCHAR2 IS
743 BEGIN
744 RETURN RP_FLEX_OR_DESC;
745 END RP_FLEX_OR_DESC_P;
746
747 FUNCTION RP_ITEM_FLEX_ALL_SEG_WHERE_P RETURN VARCHAR2 IS
748 BEGIN
749 RETURN RP_ITEM_FLEX_ALL_SEG_WHERE;
750 END RP_ITEM_FLEX_ALL_SEG_WHERE_P;
751
752 FUNCTION RP_ORDER_DATE_RANGE_P RETURN VARCHAR2 IS
753 BEGIN
754 RETURN RP_ORDER_DATE_RANGE;
755 END RP_ORDER_DATE_RANGE_P;
756
757 FUNCTION RP_CANCELLED_BY_RANGE_P RETURN VARCHAR2 IS
758 BEGIN
759 RETURN RP_CANCELLED_BY_RANGE;
760 END RP_CANCELLED_BY_RANGE_P;
761
762 FUNCTION RP_DATE_LBL_P RETURN VARCHAR2 IS
763 BEGIN
764 RETURN RP_DATE_LBL;
765 END RP_DATE_LBL_P;
766
767 FUNCTION CP_UNIT1_P RETURN VARCHAR2 IS
768 BEGIN
769 RETURN CP_UNIT1;
770 END CP_UNIT1_P;
771
772 FUNCTION CP_UNIT2_P RETURN VARCHAR2 IS
773 BEGIN
774 RETURN CP_UNIT2;
775 END CP_UNIT2_P;
776
777 FUNCTION RP_DUMMY_ITEM_P RETURN NUMBER IS
778 BEGIN
779 RETURN RP_DUMMY_ITEM;
780 END RP_DUMMY_ITEM_P;
781
782 FUNCTION IS_FIXED_RATE(X_FROM_CURRENCY IN VARCHAR2
783 ,X_TO_CURRENCY IN VARCHAR2
784 ,X_EFFECTIVE_DATE IN DATE) RETURN VARCHAR2 IS
785 X0 VARCHAR2(2000);
786 BEGIN
787 /* STPROC.INIT('begin :X0 := GL_CURRENCY_API.IS_FIXED_RATE(:X_FROM_CURRENCY, :X_TO_CURRENCY, :X_EFFECTIVE_DATE); end;');
788 STPROC.BIND_O(X0);
789 STPROC.BIND_I(X_FROM_CURRENCY);
790 STPROC.BIND_I(X_TO_CURRENCY);
791 STPROC.BIND_I(X_EFFECTIVE_DATE);
792 STPROC.EXECUTE;
793 STPROC.RETRIEVE(1
794 ,X0);
795 RETURN X0;*/
796 NULL;
797 RETURN(NULL);
798
799 END IS_FIXED_RATE;
800
801 PROCEDURE GET_RELATION(X_FROM_CURRENCY IN VARCHAR2
802 ,X_TO_CURRENCY IN VARCHAR2
803 ,X_EFFECTIVE_DATE IN DATE
804 ,X_FIXED_RATE IN OUT NOCOPY BOOLEAN
805 ,X_RELATIONSHIP IN OUT NOCOPY VARCHAR2) IS
806 BEGIN
807 /* STPROC.INIT('declare X_FIXED_RATE BOOLEAN; begin X_FIXED_RATE := sys.diutil.int_to_bool(:X_FIXED_RATE); GL_CURRENCY_API.GET_RELATION(:X_FROM_CURRENCY, :X_TO_CURRENCY, :X_EFFECTIVE_DATE, X_FIXED_RATE, :X_RELATIONSHIP);
808 :X_FIXED_RATE := sys.diutil.bool_to_int(X_FIXED_RATE); end;');
809 STPROC.BIND_IO(X_FIXED_RATE);
810 STPROC.BIND_I(X_FROM_CURRENCY);
811 STPROC.BIND_I(X_TO_CURRENCY);
812 STPROC.BIND_I(X_EFFECTIVE_DATE);
813 STPROC.BIND_IO(X_RELATIONSHIP);
814 STPROC.EXECUTE;
815 STPROC.RETRIEVE(1
816 ,X_FIXED_RATE);
817 STPROC.RETRIEVE(5
818 ,X_RELATIONSHIP);*/
819 NULL;
820 END GET_RELATION;
821
822 FUNCTION GET_EURO_CODE RETURN VARCHAR2 IS
823 X0 VARCHAR2(2000);
824 BEGIN
825 /* STPROC.INIT('begin :X0 := GL_CURRENCY_API.GET_EURO_CODE; end;');
826 STPROC.BIND_O(X0);
827 STPROC.EXECUTE;
828 STPROC.RETRIEVE(1
829 ,X0);
830 RETURN X0;*/
831 NULL;
832 RETURN(NULL);
833 END GET_EURO_CODE;
834
835 FUNCTION GET_RATE(X_FROM_CURRENCY IN VARCHAR2
836 ,X_TO_CURRENCY IN VARCHAR2
837 ,X_CONVERSION_DATE IN DATE
838 ,X_CONVERSION_TYPE IN VARCHAR2) RETURN NUMBER IS
839 X0 NUMBER;
840 BEGIN
841 /* STPROC.INIT('begin :X0 := GL_CURRENCY_API.GET_RATE(:X_FROM_CURRENCY, :X_TO_CURRENCY, :X_CONVERSION_DATE, :X_CONVERSION_TYPE); end;');
842 STPROC.BIND_O(X0);
843 STPROC.BIND_I(X_FROM_CURRENCY);
844 STPROC.BIND_I(X_TO_CURRENCY);
845 STPROC.BIND_I(X_CONVERSION_DATE);
846 STPROC.BIND_I(X_CONVERSION_TYPE);
847 STPROC.EXECUTE;
848 STPROC.RETRIEVE(1
849 ,X0);
850 RETURN X0;*/
851 NULL;
852 RETURN(NULL);
853 END GET_RATE;
854
855 FUNCTION GET_RATE(X_SET_OF_BOOKS_ID IN NUMBER
856 ,X_FROM_CURRENCY IN VARCHAR2
857 ,X_CONVERSION_DATE IN DATE
858 ,X_CONVERSION_TYPE IN VARCHAR2) RETURN NUMBER IS
859 X0 NUMBER;
860 BEGIN
861 /* STPROC.INIT('begin :X0 := GL_CURRENCY_API.GET_RATE(:X_SET_OF_BOOKS_ID, :X_FROM_CURRENCY, :X_CONVERSION_DATE, :X_CONVERSION_TYPE); end;');
862 STPROC.BIND_O(X0);
863 STPROC.BIND_I(X_SET_OF_BOOKS_ID);
864 STPROC.BIND_I(X_FROM_CURRENCY);
865 STPROC.BIND_I(X_CONVERSION_DATE);
866 STPROC.BIND_I(X_CONVERSION_TYPE);
867 STPROC.EXECUTE;
868 STPROC.RETRIEVE(1
869 ,X0);
870 RETURN X0;*/
871 NULL;
872 RETURN(NULL);
873 END GET_RATE;
874
875 FUNCTION CONVERT_AMOUNT(X_FROM_CURRENCY IN VARCHAR2
876 ,X_TO_CURRENCY IN VARCHAR2
877 ,X_CONVERSION_DATE IN DATE
878 ,X_CONVERSION_TYPE IN VARCHAR2
879 ,X_AMOUNT IN NUMBER) RETURN NUMBER IS
880 X0 NUMBER;
881 BEGIN
882 /* STPROC.INIT('begin :X0 := GL_CURRENCY_API.CONVERT_AMOUNT(:X_FROM_CURRENCY, :X_TO_CURRENCY, :X_CONVERSION_DATE, :X_CONVERSION_TYPE, :X_AMOUNT); end;');
883 STPROC.BIND_O(X0);
884 STPROC.BIND_I(X_FROM_CURRENCY);
885 STPROC.BIND_I(X_TO_CURRENCY);
886 STPROC.BIND_I(X_CONVERSION_DATE);
887 STPROC.BIND_I(X_CONVERSION_TYPE);
888 STPROC.BIND_I(X_AMOUNT);
889 STPROC.EXECUTE;
890 STPROC.RETRIEVE(1
891 ,X0);
892 RETURN X0;*/
893 NULL;
894 RETURN(NULL);
895 END CONVERT_AMOUNT;
896
897 FUNCTION CONVERT_AMOUNT(X_SET_OF_BOOKS_ID IN NUMBER
898 ,X_FROM_CURRENCY IN VARCHAR2
899 ,X_CONVERSION_DATE IN DATE
900 ,X_CONVERSION_TYPE IN VARCHAR2
901 ,X_AMOUNT IN NUMBER) RETURN NUMBER IS
902 X0 NUMBER;
903 BEGIN
904 /* STPROC.INIT('begin :X0 := GL_CURRENCY_API.CONVERT_AMOUNT(:X_SET_OF_BOOKS_ID, :X_FROM_CURRENCY, :X_CONVERSION_DATE, :X_CONVERSION_TYPE, :X_AMOUNT); end;');
905 STPROC.BIND_O(X0);
906 STPROC.BIND_I(X_SET_OF_BOOKS_ID);
907 STPROC.BIND_I(X_FROM_CURRENCY);
908 STPROC.BIND_I(X_CONVERSION_DATE);
909 STPROC.BIND_I(X_CONVERSION_TYPE);
910 STPROC.BIND_I(X_AMOUNT);
911 STPROC.EXECUTE;
912 STPROC.RETRIEVE(1
913 ,X0);
914 RETURN X0;*/
915 NULL;
916 RETURN(NULL);
917 END CONVERT_AMOUNT;
918
919 FUNCTION GET_DERIVE_TYPE(SOB_ID IN NUMBER
920 ,PERIOD IN VARCHAR2
921 ,CURR_CODE IN VARCHAR2) RETURN VARCHAR2 IS
922 X0 VARCHAR2(2000);
923 BEGIN
924 /* STPROC.INIT('begin :X0 := GL_CURRENCY_API.GET_DERIVE_TYPE(:SOB_ID, :PERIOD, :CURR_CODE); end;');
925 STPROC.BIND_O(X0);
926 STPROC.BIND_I(SOB_ID);
927 STPROC.BIND_I(PERIOD);
928 STPROC.BIND_I(CURR_CODE);
929 STPROC.EXECUTE;
930 STPROC.RETRIEVE(1
931 ,X0);
932 RETURN X0;*/
933 NULL;
934 RETURN(NULL);
935 END GET_DERIVE_TYPE;
936
937 FUNCTION C_AMOUNT_DSPFORMULA RETURN VARCHAR2 IS
938 C_GL_CONV_RATE NUMBER;
939 C_AMOUNT_DSP VARCHAR2(10);
940 BEGIN
941
942 --SRW.REFERENCE(:C_AMOUNT);
943 --SRW.REFERENCE(:CURRENCY_CODE);
944 --SRW.REFERENCE(:C_GL_CONV_RATE);
945 --SRW.REFERENCE(:RP_FUNCTIONAL_CURRENCY);
946
947 RP_DATA_FOUND := 'X';
948 IF C_GL_CONV_RATE <> -1 THEN
949 IF P_USE_FUNCTIONAL_CURRENCY = 'Y' THEN
950 /* SRW.USER_EXIT('FND FORMAT_CURRENCY
951 CODE=":RP_FUNCTIONAL_CURRENCY"
952 DISPLAY_WIDTH="14"
953 AMOUNT=":C_AMOUNT"
954 DISPLAY=":C_AMOUNT_DSP"
955 MINIMUM_PRECISION=":P_MIXED_PRECISION"');*/
956 RETURN(C_AMOUNT_DSP);
957 ELSE
958 /* SRW.USER_EXIT('FND FORMAT_CURRENCY
959 CODE=":CURRENCY_CODE"
960 DISPLAY_WIDTH="14"
961 AMOUNT=":C_AMOUNT"
962 DISPLAY=":C_AMOUNT_DSP"
963 MINIMUM_PRECISION=":P_MIXED_PRECISION"');*/
964 RETURN(C_AMOUNT_DSP);
965 END IF;
966 ELSE
967 RETURN('NO RATE');
968 END IF;
969
970 RETURN NULL;
971
972 END;
973
974
975 function item_dspFormula
976 (
977 ITEM_IDENTIFIER_TYPE_T in varchar2,
978 iid IN NUMBER,
979 SI_ORGANIZATION_ID IN NUMBER,
980 SI_INVENTORY_ITEM_ID IN NUMBER,
981 ordered_item_id IN NUMBER,
982 ORDERED_ITEM IN VARCHAR
983 )
984 return Char is
985 v_item varchar2(2000);
986 v_description varchar2(500);
987 begin
988 if (ITEM_IDENTIFIER_TYPE_T is null or ITEM_IDENTIFIER_TYPE_T = 'INT')
989 or (p_print_description in ('I','D','F')) then
990
991 --v_item := :item_flex;
992
993 select sitems.description description
994 into v_description
995 from mtl_system_items_vl sitems
996 where
997 nvl(sitems.organization_id,0) = nvl(oe_sys_parameters.value('MASTER_ORGANIZATION_ID',mo_global.get_current_org_id()),0)
998 and sitems.inventory_item_id = IID;
999
1000 /* srw.reference (:item_flex);
1001 srw.reference (:p_item_flex_code);
1002 srw.reference (:Item_dsp);
1003 srw.reference (:p_item_structure_num);
1004 srw.user_exit (' FND FLEXIDVAL
1005 CODE=":p_item_flex_code"
1006 NUM=":p_item_structure_num"
1007 APPL_SHORT_NAME="INV"
1008 DATA= ":item_flex"
1009 VALUE=":Item_dsp"
1010 DISPLAY="ALL"'
1011 );*/
1012 v_item := fnd_flex_xml_publisher_apis.process_kff_combination_1('Item_dsp', 'INV', p_item_flex_code, p_item_structure_num, SI_ORGANIZATION_ID, SI_INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE');
1013 elsif (ITEM_IDENTIFIER_TYPE_T = 'CUST' and p_print_description in ('C','P','O')) then
1014 select citems.customer_item_number item,
1015 nvl(citems.customer_item_desc,sitems.description) description
1016 into v_item,v_description
1017 from mtl_customer_items citems,
1018 mtl_customer_item_xrefs cxref,
1019 mtl_system_items_vl sitems
1020 where citems.customer_item_id = cxref.customer_item_id
1021 and cxref.inventory_item_id = sitems.inventory_item_id
1022 and citems.customer_item_id = ordered_item_id
1023 and nvl(sitems.organization_id,0) = nvl(oe_sys_parameters.value('MASTER_ORGANIZATION_ID',mo_global.get_current_org_id()),0)
1024 and sitems.inventory_item_id = IID;
1025 elsif (p_print_description in ('C','P','O')) then
1026 Begin
1027 select items.cross_reference item,
1028 nvl(items.description,sitems.description) description
1029 into v_item,v_description
1030 from mtl_cross_reference_types xtypes,
1031 mtl_cross_references items,
1032 mtl_system_items_vl sitems
1033 where xtypes.cross_reference_type = items.cross_reference_type
1034 and items.inventory_item_id = sitems.inventory_item_id
1035 and items.cross_reference = ORDERED_ITEM
1036 and items.cross_reference_type = ITEM_IDENTIFIER_TYPE_T
1037 and nvl(sitems.organization_id,0) = nvl(oe_sys_parameters.value('MASTER_ORGANIZATION_ID',mo_global.get_current_org_id()),0)
1038 and sitems.inventory_item_id = IID
1039 and items.org_independent_flag = 'N'
1040 and items.organization_id = nvl(oe_sys_parameters.value('MASTER_ORGANIZATION_ID',mo_global.get_current_org_id()),0);
1041 Exception When NO_DATA_FOUND Then
1042 Select items.cross_reference item,
1043 nvl(items.description,sitems.description) description
1044 into v_item,v_description
1045 from mtl_cross_reference_types xtypes,
1046 mtl_cross_references items,
1047 mtl_system_items_vl sitems
1048 where xtypes.cross_reference_type = items.cross_reference_type
1049 and items.inventory_item_id = sitems.inventory_item_id
1050 and items.cross_reference = ORDERED_ITEM
1051 and items.cross_reference_type = ITEM_IDENTIFIER_TYPE_T
1052 and nvl(sitems.organization_id,0 ) = nvl(oe_sys_parameters.value('MASTER_ORGANIZATION_ID',mo_global.get_current_org_id()),0)
1053 and sitems.inventory_item_id = IID
1054 and items.org_independent_flag ='Y';
1055 End;
1056 --Bug 3433353 End
1057
1058 end if;
1059
1060 if (p_print_description in ('I','C')) then
1061 return(v_item||' - '||v_description);
1062 elsif (p_print_description in ('D','P')) then
1063 return(v_description);
1064 else
1065 return(v_item);
1066 end if;
1067
1068
1069
1070
1071 RETURN NULL;
1072 exception
1073 when no_data_found then
1074 return ('Item not found');
1075 end;
1076
1077
1078 END ONT_OEXOEOCR_XMLP_PKG;
1079
1080
[Home] [Help]