1 PACKAGE BODY ONT_OEXOEOCR_XMLP_PKG AS
2 /* $Header: OEXOEOCRB.pls 120.3 2008/05/05 09:06:18 dwkrishn noship $ */
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 SELECT
612 DISTINCT
613 UNIT_SELLING_PRICE
614 INTO UNIT_SELLING_PRICE_VAR
615 FROM
616 OE_ORDER_LINES_HISTORY
617 WHERE LINE_ID = LINEID
618 AND CANCELLED_QUANTITY = CAN_QTY_FINAL2
619 AND HIST_TYPE_CODE = 'CANCELLATION';
620 RETURN ((CAN_QTY_FINAL2 - CANCELLED_QTY) * UNIT_SELLING_PRICE_VAR);
621 END IF;
622 END IF;
623 EXCEPTION
624 WHEN NO_DATA_FOUND THEN
625 RETURN (NULL);
626 END;
627 END CALC_AMOUNTFORMULA;
628
629 FUNCTION C_PRECISIONFORMULA(CURRENCY_CODE IN VARCHAR2) RETURN NUMBER IS
630 BEGIN
631 DECLARE
632 W_PRECISION NUMBER;
633 BEGIN
634 SELECT
635 PRECISION
636 INTO W_PRECISION
637 FROM
638 FND_CURRENCIES CUR
639 WHERE CUR.CURRENCY_CODE = C_PRECISIONFORMULA.CURRENCY_CODE;
640 RETURN (W_PRECISION);
641 EXCEPTION
642 WHEN NO_DATA_FOUND THEN
643 W_PRECISION := 2;
644 RETURN (W_PRECISION);
645 END;
646 RETURN NULL;
647 END C_PRECISIONFORMULA;
648
649 FUNCTION RP_REPORT_NAME_P RETURN VARCHAR2 IS
650 BEGIN
651 RETURN RP_REPORT_NAME;
652 END RP_REPORT_NAME_P;
653
654 FUNCTION RP_SUB_TITLE_P RETURN VARCHAR2 IS
655 BEGIN
656 RETURN RP_SUB_TITLE;
657 END RP_SUB_TITLE_P;
658
659 FUNCTION RP_COMPANY_NAME_P RETURN VARCHAR2 IS
660 BEGIN
661 RETURN RP_COMPANY_NAME;
662 END RP_COMPANY_NAME_P;
663
664 FUNCTION RP_FUNCTIONAL_CURRENCY_P RETURN VARCHAR2 IS
665 BEGIN
666 RETURN RP_FUNCTIONAL_CURRENCY;
667 END RP_FUNCTIONAL_CURRENCY_P;
668
669 FUNCTION RP_DATA_FOUND_P RETURN VARCHAR2 IS
670 BEGIN
671 RETURN RP_DATA_FOUND;
672 END RP_DATA_FOUND_P;
673
674 FUNCTION RP_ITEM_FLEX_LPROMPT_P RETURN VARCHAR2 IS
675 BEGIN
676 RETURN RP_ITEM_FLEX_LPROMPT;
677 END RP_ITEM_FLEX_LPROMPT_P;
678
679 FUNCTION RP_ITEM_FLEX_ALL_SEG_P RETURN VARCHAR2 IS
680 BEGIN
681 RETURN RP_ITEM_FLEX_ALL_SEG;
682 END RP_ITEM_FLEX_ALL_SEG_P;
683
684 FUNCTION RP_ITEM_FLEX_APROMPT_P RETURN VARCHAR2 IS
685 BEGIN
686 RETURN RP_ITEM_FLEX_APROMPT;
687 END RP_ITEM_FLEX_APROMPT_P;
688
689 FUNCTION RP_SALES_REASON_LBL_P RETURN VARCHAR2 IS
690 BEGIN
691 RETURN RP_SALES_REASON_LBL;
692 END RP_SALES_REASON_LBL_P;
693
694 FUNCTION RP_CUSTOMER_RANGE_P RETURN VARCHAR2 IS
695 BEGIN
696 RETURN RP_CUSTOMER_RANGE;
697 END RP_CUSTOMER_RANGE_P;
698
699 FUNCTION RP_SALESPERSON_RANGE_P RETURN VARCHAR2 IS
700 BEGIN
701 RETURN RP_SALESPERSON_RANGE;
702 END RP_SALESPERSON_RANGE_P;
703
704 FUNCTION RP_ORDER_RANGE_P RETURN VARCHAR2 IS
705 BEGIN
706 RETURN RP_ORDER_RANGE;
707 END RP_ORDER_RANGE_P;
708
709 FUNCTION RP_CANCEL_DATE_RANGE_P RETURN VARCHAR2 IS
710 BEGIN
711 RETURN RP_CANCEL_DATE_RANGE;
712 END RP_CANCEL_DATE_RANGE_P;
713
714 FUNCTION RP_SALES_REASON_LBL_2_P RETURN VARCHAR2 IS
715 BEGIN
716 RETURN RP_SALES_REASON_LBL_2;
717 END RP_SALES_REASON_LBL_2_P;
718
719 FUNCTION RP_ORDER_BY_P RETURN VARCHAR2 IS
720 BEGIN
721 RETURN RP_ORDER_BY;
722 END RP_ORDER_BY_P;
723
724 FUNCTION RP_CANCEL_REASON_P RETURN VARCHAR2 IS
725 BEGIN
726 RETURN RP_CANCEL_REASON;
727 END RP_CANCEL_REASON_P;
728
729 FUNCTION RP_USE_FUNCTIONAL_CURRENCY_P RETURN VARCHAR2 IS
730 BEGIN
731 RETURN RP_USE_FUNCTIONAL_CURRENCY;
732 END RP_USE_FUNCTIONAL_CURRENCY_P;
733
734 FUNCTION RP_FLEX_OR_DESC_P RETURN VARCHAR2 IS
735 BEGIN
736 RETURN RP_FLEX_OR_DESC;
737 END RP_FLEX_OR_DESC_P;
738
739 FUNCTION RP_ITEM_FLEX_ALL_SEG_WHERE_P RETURN VARCHAR2 IS
740 BEGIN
741 RETURN RP_ITEM_FLEX_ALL_SEG_WHERE;
742 END RP_ITEM_FLEX_ALL_SEG_WHERE_P;
743
744 FUNCTION RP_ORDER_DATE_RANGE_P RETURN VARCHAR2 IS
745 BEGIN
746 RETURN RP_ORDER_DATE_RANGE;
747 END RP_ORDER_DATE_RANGE_P;
748
749 FUNCTION RP_CANCELLED_BY_RANGE_P RETURN VARCHAR2 IS
750 BEGIN
751 RETURN RP_CANCELLED_BY_RANGE;
752 END RP_CANCELLED_BY_RANGE_P;
753
754 FUNCTION RP_DATE_LBL_P RETURN VARCHAR2 IS
755 BEGIN
756 RETURN RP_DATE_LBL;
757 END RP_DATE_LBL_P;
758
759 FUNCTION CP_UNIT1_P RETURN VARCHAR2 IS
760 BEGIN
761 RETURN CP_UNIT1;
762 END CP_UNIT1_P;
763
764 FUNCTION CP_UNIT2_P RETURN VARCHAR2 IS
765 BEGIN
766 RETURN CP_UNIT2;
767 END CP_UNIT2_P;
768
769 FUNCTION RP_DUMMY_ITEM_P RETURN NUMBER IS
770 BEGIN
771 RETURN RP_DUMMY_ITEM;
772 END RP_DUMMY_ITEM_P;
773
774 FUNCTION IS_FIXED_RATE(X_FROM_CURRENCY IN VARCHAR2
775 ,X_TO_CURRENCY IN VARCHAR2
776 ,X_EFFECTIVE_DATE IN DATE) RETURN VARCHAR2 IS
777 X0 VARCHAR2(2000);
778 BEGIN
779 /* STPROC.INIT('begin :X0 := GL_CURRENCY_API.IS_FIXED_RATE(:X_FROM_CURRENCY, :X_TO_CURRENCY, :X_EFFECTIVE_DATE); end;');
780 STPROC.BIND_O(X0);
781 STPROC.BIND_I(X_FROM_CURRENCY);
782 STPROC.BIND_I(X_TO_CURRENCY);
783 STPROC.BIND_I(X_EFFECTIVE_DATE);
784 STPROC.EXECUTE;
785 STPROC.RETRIEVE(1
786 ,X0);
787 RETURN X0;*/
788 NULL;
789 RETURN(NULL);
790
791 END IS_FIXED_RATE;
792
793 PROCEDURE GET_RELATION(X_FROM_CURRENCY IN VARCHAR2
794 ,X_TO_CURRENCY IN VARCHAR2
795 ,X_EFFECTIVE_DATE IN DATE
796 ,X_FIXED_RATE IN OUT NOCOPY BOOLEAN
797 ,X_RELATIONSHIP IN OUT NOCOPY VARCHAR2) IS
798 BEGIN
799 /* 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);
800 :X_FIXED_RATE := sys.diutil.bool_to_int(X_FIXED_RATE); end;');
801 STPROC.BIND_IO(X_FIXED_RATE);
802 STPROC.BIND_I(X_FROM_CURRENCY);
803 STPROC.BIND_I(X_TO_CURRENCY);
804 STPROC.BIND_I(X_EFFECTIVE_DATE);
805 STPROC.BIND_IO(X_RELATIONSHIP);
806 STPROC.EXECUTE;
807 STPROC.RETRIEVE(1
808 ,X_FIXED_RATE);
809 STPROC.RETRIEVE(5
810 ,X_RELATIONSHIP);*/
811 NULL;
812 END GET_RELATION;
813
814 FUNCTION GET_EURO_CODE RETURN VARCHAR2 IS
815 X0 VARCHAR2(2000);
816 BEGIN
817 /* STPROC.INIT('begin :X0 := GL_CURRENCY_API.GET_EURO_CODE; end;');
818 STPROC.BIND_O(X0);
819 STPROC.EXECUTE;
820 STPROC.RETRIEVE(1
821 ,X0);
822 RETURN X0;*/
823 NULL;
824 RETURN(NULL);
825 END GET_EURO_CODE;
826
827 FUNCTION GET_RATE(X_FROM_CURRENCY IN VARCHAR2
828 ,X_TO_CURRENCY IN VARCHAR2
829 ,X_CONVERSION_DATE IN DATE
830 ,X_CONVERSION_TYPE IN VARCHAR2) RETURN NUMBER IS
831 X0 NUMBER;
832 BEGIN
833 /* STPROC.INIT('begin :X0 := GL_CURRENCY_API.GET_RATE(:X_FROM_CURRENCY, :X_TO_CURRENCY, :X_CONVERSION_DATE, :X_CONVERSION_TYPE); end;');
834 STPROC.BIND_O(X0);
835 STPROC.BIND_I(X_FROM_CURRENCY);
836 STPROC.BIND_I(X_TO_CURRENCY);
837 STPROC.BIND_I(X_CONVERSION_DATE);
838 STPROC.BIND_I(X_CONVERSION_TYPE);
839 STPROC.EXECUTE;
840 STPROC.RETRIEVE(1
841 ,X0);
842 RETURN X0;*/
843 NULL;
844 RETURN(NULL);
845 END GET_RATE;
846
847 FUNCTION GET_RATE(X_SET_OF_BOOKS_ID IN NUMBER
848 ,X_FROM_CURRENCY IN VARCHAR2
849 ,X_CONVERSION_DATE IN DATE
850 ,X_CONVERSION_TYPE IN VARCHAR2) RETURN NUMBER IS
851 X0 NUMBER;
852 BEGIN
853 /* STPROC.INIT('begin :X0 := GL_CURRENCY_API.GET_RATE(:X_SET_OF_BOOKS_ID, :X_FROM_CURRENCY, :X_CONVERSION_DATE, :X_CONVERSION_TYPE); end;');
854 STPROC.BIND_O(X0);
855 STPROC.BIND_I(X_SET_OF_BOOKS_ID);
856 STPROC.BIND_I(X_FROM_CURRENCY);
857 STPROC.BIND_I(X_CONVERSION_DATE);
858 STPROC.BIND_I(X_CONVERSION_TYPE);
859 STPROC.EXECUTE;
860 STPROC.RETRIEVE(1
861 ,X0);
862 RETURN X0;*/
863 NULL;
864 RETURN(NULL);
865 END GET_RATE;
866
867 FUNCTION CONVERT_AMOUNT(X_FROM_CURRENCY IN VARCHAR2
868 ,X_TO_CURRENCY IN VARCHAR2
869 ,X_CONVERSION_DATE IN DATE
870 ,X_CONVERSION_TYPE IN VARCHAR2
871 ,X_AMOUNT IN NUMBER) RETURN NUMBER IS
872 X0 NUMBER;
873 BEGIN
874 /* STPROC.INIT('begin :X0 := GL_CURRENCY_API.CONVERT_AMOUNT(:X_FROM_CURRENCY, :X_TO_CURRENCY, :X_CONVERSION_DATE, :X_CONVERSION_TYPE, :X_AMOUNT); end;');
875 STPROC.BIND_O(X0);
876 STPROC.BIND_I(X_FROM_CURRENCY);
877 STPROC.BIND_I(X_TO_CURRENCY);
878 STPROC.BIND_I(X_CONVERSION_DATE);
879 STPROC.BIND_I(X_CONVERSION_TYPE);
880 STPROC.BIND_I(X_AMOUNT);
881 STPROC.EXECUTE;
882 STPROC.RETRIEVE(1
883 ,X0);
884 RETURN X0;*/
885 NULL;
886 RETURN(NULL);
887 END CONVERT_AMOUNT;
888
889 FUNCTION CONVERT_AMOUNT(X_SET_OF_BOOKS_ID IN NUMBER
890 ,X_FROM_CURRENCY IN VARCHAR2
891 ,X_CONVERSION_DATE IN DATE
892 ,X_CONVERSION_TYPE IN VARCHAR2
893 ,X_AMOUNT IN NUMBER) RETURN NUMBER IS
894 X0 NUMBER;
895 BEGIN
896 /* 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;');
897 STPROC.BIND_O(X0);
898 STPROC.BIND_I(X_SET_OF_BOOKS_ID);
899 STPROC.BIND_I(X_FROM_CURRENCY);
900 STPROC.BIND_I(X_CONVERSION_DATE);
901 STPROC.BIND_I(X_CONVERSION_TYPE);
902 STPROC.BIND_I(X_AMOUNT);
903 STPROC.EXECUTE;
904 STPROC.RETRIEVE(1
905 ,X0);
906 RETURN X0;*/
907 NULL;
908 RETURN(NULL);
909 END CONVERT_AMOUNT;
910
911 FUNCTION GET_DERIVE_TYPE(SOB_ID IN NUMBER
912 ,PERIOD IN VARCHAR2
913 ,CURR_CODE IN VARCHAR2) RETURN VARCHAR2 IS
914 X0 VARCHAR2(2000);
915 BEGIN
916 /* STPROC.INIT('begin :X0 := GL_CURRENCY_API.GET_DERIVE_TYPE(:SOB_ID, :PERIOD, :CURR_CODE); end;');
917 STPROC.BIND_O(X0);
918 STPROC.BIND_I(SOB_ID);
919 STPROC.BIND_I(PERIOD);
920 STPROC.BIND_I(CURR_CODE);
921 STPROC.EXECUTE;
922 STPROC.RETRIEVE(1
923 ,X0);
924 RETURN X0;*/
925 NULL;
926 RETURN(NULL);
927 END GET_DERIVE_TYPE;
928
929 FUNCTION C_AMOUNT_DSPFORMULA RETURN VARCHAR2 IS
930 C_GL_CONV_RATE NUMBER;
931 C_AMOUNT_DSP VARCHAR2(10);
932 BEGIN
933
934 --SRW.REFERENCE(:C_AMOUNT);
935 --SRW.REFERENCE(:CURRENCY_CODE);
936 --SRW.REFERENCE(:C_GL_CONV_RATE);
937 --SRW.REFERENCE(:RP_FUNCTIONAL_CURRENCY);
938
939 RP_DATA_FOUND := 'X';
940 IF C_GL_CONV_RATE <> -1 THEN
941 IF P_USE_FUNCTIONAL_CURRENCY = 'Y' THEN
942 /* SRW.USER_EXIT('FND FORMAT_CURRENCY
943 CODE=":RP_FUNCTIONAL_CURRENCY"
944 DISPLAY_WIDTH="14"
945 AMOUNT=":C_AMOUNT"
946 DISPLAY=":C_AMOUNT_DSP"
947 MINIMUM_PRECISION=":P_MIXED_PRECISION"');*/
948 RETURN(C_AMOUNT_DSP);
949 ELSE
950 /* SRW.USER_EXIT('FND FORMAT_CURRENCY
951 CODE=":CURRENCY_CODE"
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 END IF;
958 ELSE
959 RETURN('NO RATE');
960 END IF;
961
962 RETURN NULL;
963
964 END;
965
966
967 function item_dspFormula
968 (
969 ITEM_IDENTIFIER_TYPE_T in varchar2,
970 iid IN NUMBER,
971 SI_ORGANIZATION_ID IN NUMBER,
972 SI_INVENTORY_ITEM_ID IN NUMBER,
973 ordered_item_id IN NUMBER,
974 ORDERED_ITEM IN VARCHAR
975 )
976 return Char is
977 v_item varchar2(2000);
978 v_description varchar2(500);
979 begin
980 if (ITEM_IDENTIFIER_TYPE_T is null or ITEM_IDENTIFIER_TYPE_T = 'INT')
981 or (p_print_description in ('I','D','F')) then
982
983 --v_item := :item_flex;
984
985 select sitems.description description
986 into v_description
987 from mtl_system_items_vl sitems
988 where
989 nvl(sitems.organization_id,0) = nvl(oe_sys_parameters.value('MASTER_ORGANIZATION_ID',mo_global.get_current_org_id()),0)
990 and sitems.inventory_item_id = IID;
991
992 /* srw.reference (:item_flex);
993 srw.reference (:p_item_flex_code);
994 srw.reference (:Item_dsp);
995 srw.reference (:p_item_structure_num);
996 srw.user_exit (' FND FLEXIDVAL
997 CODE=":p_item_flex_code"
998 NUM=":p_item_structure_num"
999 APPL_SHORT_NAME="INV"
1000 DATA= ":item_flex"
1001 VALUE=":Item_dsp"
1002 DISPLAY="ALL"'
1003 );*/
1004 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');
1005 elsif (ITEM_IDENTIFIER_TYPE_T = 'CUST' and p_print_description in ('C','P','O')) then
1006 select citems.customer_item_number item,
1007 nvl(citems.customer_item_desc,sitems.description) description
1008 into v_item,v_description
1009 from mtl_customer_items citems,
1010 mtl_customer_item_xrefs cxref,
1011 mtl_system_items_vl sitems
1012 where citems.customer_item_id = cxref.customer_item_id
1013 and cxref.inventory_item_id = sitems.inventory_item_id
1014 and citems.customer_item_id = ordered_item_id
1015 and nvl(sitems.organization_id,0) = nvl(oe_sys_parameters.value('MASTER_ORGANIZATION_ID',mo_global.get_current_org_id()),0)
1016 and sitems.inventory_item_id = IID;
1017 elsif (p_print_description in ('C','P','O')) then
1018 Begin
1019 select items.cross_reference item,
1020 nvl(items.description,sitems.description) description
1021 into v_item,v_description
1022 from mtl_cross_reference_types xtypes,
1023 mtl_cross_references items,
1024 mtl_system_items_vl sitems
1025 where xtypes.cross_reference_type = items.cross_reference_type
1026 and items.inventory_item_id = sitems.inventory_item_id
1027 and items.cross_reference = ORDERED_ITEM
1028 and items.cross_reference_type = ITEM_IDENTIFIER_TYPE_T
1029 and nvl(sitems.organization_id,0) = nvl(oe_sys_parameters.value('MASTER_ORGANIZATION_ID',mo_global.get_current_org_id()),0)
1030 and sitems.inventory_item_id = IID
1031 and items.org_independent_flag = 'N'
1032 and items.organization_id = nvl(oe_sys_parameters.value('MASTER_ORGANIZATION_ID',mo_global.get_current_org_id()),0);
1033 Exception When NO_DATA_FOUND Then
1034 Select items.cross_reference item,
1035 nvl(items.description,sitems.description) description
1036 into v_item,v_description
1037 from mtl_cross_reference_types xtypes,
1038 mtl_cross_references items,
1039 mtl_system_items_vl sitems
1040 where xtypes.cross_reference_type = items.cross_reference_type
1041 and items.inventory_item_id = sitems.inventory_item_id
1042 and items.cross_reference = ORDERED_ITEM
1043 and items.cross_reference_type = ITEM_IDENTIFIER_TYPE_T
1044 and nvl(sitems.organization_id,0 ) = nvl(oe_sys_parameters.value('MASTER_ORGANIZATION_ID',mo_global.get_current_org_id()),0)
1045 and sitems.inventory_item_id = IID
1046 and items.org_independent_flag ='Y';
1047 End;
1048 --Bug 3433353 End
1049
1050 end if;
1051
1052 if (p_print_description in ('I','C')) then
1053 return(v_item||' - '||v_description);
1054 elsif (p_print_description in ('D','P')) then
1055 return(v_description);
1056 else
1057 return(v_item);
1058 end if;
1059
1060
1061
1062
1063 RETURN NULL;
1064 exception
1065 when no_data_found then
1066 return ('Item not found');
1067 end;
1068
1069
1070 END ONT_OEXOEOCR_XMLP_PKG;
1071
1072
[Home] [Help]