1 PACKAGE BODY ONT_OEXOEIOD_XMLP_PKG AS
2 /* $Header: OEXOEIODB.pls 120.3 2008/05/05 09:03:31 dwkrishn noship $ */
3 FUNCTION AFTERREPORT RETURN BOOLEAN IS
4 BEGIN
5 BEGIN
6 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
7 EXCEPTION
8 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
9 /*SRW.MESSAGE(1
10 ,'Failed in AFTER REPORT TRIGGER')*/NULL;
11 RETURN (FALSE);
12 END;
13 RETURN (TRUE);
14 END AFTERREPORT;
15
16 FUNCTION P_ITEM_FLEX_CODEVALIDTRIGGER RETURN BOOLEAN IS
17 BEGIN
18 RETURN (TRUE);
19 END P_ITEM_FLEX_CODEVALIDTRIGGER;
20
21 FUNCTION AFTERPFORM RETURN BOOLEAN IS
22 BEGIN
23 BEGIN
24 /*SRW.REFERENCE(P_SALESREP_LOW)*/NULL;
25 /*SRW.REFERENCE(P_SALESREP_HIGH)*/NULL;
26 IF (P_ORDER_TYPE_LOW IS NOT NULL) AND (P_ORDER_TYPE_HIGH IS NOT NULL) THEN
27 LP_ORDER_TYPE := 'and ( ot.transaction_type_id between p_order_type_low and p_order_type_high ) ';
28 SELECT
29 OEOT.NAME
30 INTO L_ORDER_TYPE_LOW
31 FROM
32 OE_TRANSACTION_TYPES_TL OEOT
33 WHERE OEOT.TRANSACTION_TYPE_ID = P_ORDER_TYPE_LOW
34 AND OEOT.LANGUAGE = USERENV('LANG');
35 SELECT
36 OEOT.NAME
37 INTO L_ORDER_TYPE_HIGH
38 FROM
39 OE_TRANSACTION_TYPES_TL OEOT
40 WHERE OEOT.TRANSACTION_TYPE_ID = P_ORDER_TYPE_HIGH
41 AND OEOT.LANGUAGE = USERENV('LANG');
42 ELSIF (P_ORDER_TYPE_LOW IS NOT NULL) THEN
43 LP_ORDER_TYPE := 'and ot.transaction_type_id >= :p_order_type_low ';
44 SELECT
45 OEOT.NAME
46 INTO L_ORDER_TYPE_LOW
47 FROM
48 OE_TRANSACTION_TYPES_TL OEOT
49 WHERE OEOT.TRANSACTION_TYPE_ID = P_ORDER_TYPE_LOW
50 AND OEOT.LANGUAGE = USERENV('LANG');
51 ELSIF (P_ORDER_TYPE_HIGH IS NOT NULL) THEN
52 LP_ORDER_TYPE := 'and ot.transaction_type_id <= p_order_type_high ';
53 SELECT
54 OEOT.NAME
55 INTO L_ORDER_TYPE_HIGH
56 FROM
57 OE_TRANSACTION_TYPES_TL OEOT
58 WHERE OEOT.TRANSACTION_TYPE_ID = P_ORDER_TYPE_HIGH
59 AND OEOT.LANGUAGE = USERENV('LANG');
60 END IF;
61 IF (P_CUSTOMER_NAME_LOW IS NOT NULL) AND (P_CUSTOMER_NAME_HIGH IS NOT NULL) THEN
62 LP_CUSTOMER_NAME := 'and ( party.party_name between :p_customer_name_low and :p_customer_name_high ) ';
63 ELSIF (P_CUSTOMER_NAME_LOW IS NOT NULL) THEN
64 LP_CUSTOMER_NAME := 'and party.party_name >= :p_customer_name_low ';
65 ELSIF (P_CUSTOMER_NAME_HIGH IS NOT NULL) THEN
66 LP_CUSTOMER_NAME := 'and party.party_name <= :p_customer_name_high ';
67 END IF;
68 IF (P_ORDER_NUM_LOW IS NOT NULL) AND (P_ORDER_NUM_HIGH IS NOT NULL) THEN
69 IF (P_ORDER_NUM_LOW = P_ORDER_NUM_HIGH) THEN
70 LP_ORDER_NUM := 'and h.order_number = :p_order_num_low ';
71 ELSE
72 LP_ORDER_NUM := 'and ( h.order_number between :p_order_num_low and :p_order_num_high ) ';
73 END IF;
74 ELSIF (P_ORDER_NUM_LOW IS NOT NULL) THEN
75 LP_ORDER_NUM := 'and h.order_number >= :p_order_num_low ';
76 ELSIF (P_ORDER_NUM_HIGH IS NOT NULL) THEN
77 LP_ORDER_NUM := 'and h.order_number <= :p_order_num_high ';
78 END IF;
79 IF (P_SALESREP_LOW IS NOT NULL) AND (P_SALESREP_HIGH IS NOT NULL) THEN
80 LP_SALESREP := 'and (nvl(sr.name,''zzzzzz'') between :p_salesrep_low and :p_salesrep_high ) ';
81 ELSIF (P_SALESREP_LOW IS NOT NULL) THEN
82 LP_SALESREP := 'and nvl(sr.name,''zzzzzz'') >= :p_salesrep_low ';
83 ELSIF (P_SALESREP_HIGH IS NOT NULL) THEN
84 LP_SALESREP := 'and nvl(sr.name,''zzzzzz'') <= :p_salesrep_high ';
85 END IF;
86 IF (P_COUNTRY_LOW IS NOT NULL) THEN
87 LP_COUNTRY := 'and terr.territory_short_name = :p_country_low ';
88 END IF;
89 IF P_OPEN_ORDERS_ONLY = 'Y' THEN
90 LP_OPEN_ORDERS_ONLY := 'and h.open_flag = ''Y'' ';
91 END IF;
92 IF P_ORDER_NUM_LOW = P_ORDER_NUM_HIGH THEN
93 NULL;
94 ELSE
95 IF P_ORDER_CATEGORY IS NOT NULL THEN
96 IF P_ORDER_CATEGORY = 'SALES' THEN
97 LP_ORDER_CATEGORY := 'and h.order_category_code in (''ORDER'', ''MIXED'') ';
98 ELSIF P_ORDER_CATEGORY = 'CREDIT' THEN
99 LP_ORDER_CATEGORY := 'and h.order_category_code in (''RETURN'', ''MIXED'') ';
100 ELSIF P_ORDER_CATEGORY = 'ALL' THEN
101 LP_ORDER_CATEGORY := NULL;
102 END IF;
103 ELSE
104 LP_ORDER_CATEGORY := 'and h.order_category_code in (''ORDER'', ''MIXED'') ';
105 END IF;
106 END IF;
107 IF P_LINE_CATEGORY IS NOT NULL THEN
108 IF P_LINE_CATEGORY = 'SALES' THEN
109 LP_LINE_CATEGORY := 'and l.line_category_code = ''ORDER'' ';
110 ELSIF P_LINE_CATEGORY = 'CREDIT' THEN
111 LP_LINE_CATEGORY := 'and l.line_category_code = ''RETURN'' ';
112 ELSIF P_LINE_CATEGORY = 'ALL' THEN
113 LP_LINE_CATEGORY := NULL;
114 END IF;
115 ELSE
116 LP_LINE_CATEGORY := 'and l.line_category_code = ''ORDER'' ';
117 END IF;
118 END;
119 RETURN (TRUE);
120 END AFTERPFORM;
121
122 FUNCTION C_DATA_NOT_FOUNDFORMULA(CURRENCY1 IN VARCHAR2) RETURN NUMBER IS
123 BEGIN
124 RP_DATA_FOUND := CURRENCY1;
125 RETURN (0);
126 END C_DATA_NOT_FOUNDFORMULA;
127
128 FUNCTION RP_CURR_LABELFORMULA RETURN VARCHAR2 IS
129 BEGIN
130 IF SUBSTR(UPPER(P_ORDER_BY)
131 ,1
132 ,1) = 'O' THEN
133 RETURN (' Currency:');
134 ELSIF SUBSTR(UPPER(P_ORDER_BY)
135 ,1
136 ,1) = 'S' THEN
137 RETURN (' Currency:');
138 ELSE
139 RETURN ('Currency:');
140 END IF;
141 RETURN NULL;
142 END RP_CURR_LABELFORMULA;
143
144 FUNCTION C_ORDER_COUNTFORMULA RETURN NUMBER IS
145 BEGIN
146 RETURN (1);
147 END C_ORDER_COUNTFORMULA;
148
149 FUNCTION RP_ORDER_BYFORMULA RETURN VARCHAR2 IS
150 BEGIN
151 DECLARE
152 L_TEMP VARCHAR2(100);
153 BEGIN
154 SELECT
155 MEANING
156 INTO L_TEMP
157 FROM
158 OE_LOOKUPS
159 WHERE LOOKUP_TYPE = 'OEXOEIOD ORDER BY'
160 AND SUBSTR(LOOKUP_CODE
161 ,1
162 ,1) = SUBSTR(UPPER(P_ORDER_BY)
163 ,1
164 ,1);
165 RETURN (L_TEMP);
166 EXCEPTION
167 WHEN NO_DATA_FOUND THEN
168 RETURN ('Customer');
169 END;
170 RETURN NULL;
171 END RP_ORDER_BYFORMULA;
172
173 FUNCTION C_LINE_COUNTFORMULA(HEADER_ID1 IN NUMBER) RETURN NUMBER IS
174 BEGIN
175 DECLARE
176 L_COUNT NUMBER(20);
177 BEGIN
178 /*SRW.REFERENCE(HEADER_ID1)*/NULL;
179 L_COUNT := 0;
180 SELECT
181 1
182 INTO L_COUNT
183 FROM
184 OE_ORDER_LINES_ALL L,
185 RA_CUSTOMER_TRX_LINES_ALL TRXL,
186 OE_ORDER_HEADERS H
187 WHERE L.HEADER_ID = C_LINE_COUNTFORMULA.HEADER_ID1
188 AND H.HEADER_ID = L.HEADER_ID
189 AND TRXL.INTERFACE_LINE_CONTEXT = P_INVOICE_LINE_CONTEXT
190 AND TRXL.INTERFACE_LINE_ATTRIBUTE1 = H.ORDER_NUMBER
191 AND TO_CHAR(L.LINE_ID) = TRXL.INTERFACE_LINE_ATTRIBUTE6
192 AND NVL(L.ORG_ID
193 ,0) = NVL(LP_ORG_ID
194 ,0)
195 AND NVL(TRXL.ORG_ID
196 ,0) = NVL(LP_ORG_ID
197 ,0)
198 AND ROWNUM = 1;
199 RETURN (L_COUNT);
200 EXCEPTION
201 WHEN NO_DATA_FOUND THEN
202 L_COUNT := 0;
203 RETURN (L_COUNT);
204 END;
205 RETURN NULL;
206 END C_LINE_COUNTFORMULA;
207
208 FUNCTION C_CONVERT_AMOUNTFORMULA(CURRENCY1 IN VARCHAR2
209 ,AMOUNT IN NUMBER
210 ,CONVERSION_TYPE_CODE IN VARCHAR2
211 ,ORDER_DATE IN DATE
212 ,CONVERSION_RATE IN NUMBER) RETURN NUMBER IS
213 BEGIN
214 DECLARE
215 L_CONVERSION_RATE NUMBER(15);
216 L_CURRENCY VARCHAR2(15);
217 L_PRECISION NUMBER;
218 BEGIN
219 /*SRW.REFERENCE(CURRENCY1)*/NULL;
220 /*SRW.REFERENCE(RP_FUNCTIONAL_CURRENCY)*/NULL;
221 /*SRW.REFERENCE(C_AMOUNT)*/NULL;
222 /*SRW.REFERENCE(AMOUNT)*/NULL;
223 /*SRW.REFERENCE(CONVERSION_TYPE_CODE)*/NULL;
224 /*SRW.REFERENCE(ORDER_DATE)*/NULL;
225 L_CONVERSION_RATE := 0;
226 BEGIN
227 IF P_USE_FUNCTIONAL_CURRENCY = 'N' THEN
228 L_CURRENCY := CURRENCY1;
229 ELSE
230 L_CURRENCY := RP_FUNCTIONAL_CURRENCY;
231 END IF;
232 SELECT
233 PRECISION
234 INTO L_PRECISION
235 FROM
236 FND_CURRENCIES
237 WHERE CURRENCY_CODE = L_CURRENCY;
238 EXCEPTION
239 WHEN NO_DATA_FOUND THEN
240 L_PRECISION := 2;
241 END;
242 IF P_USE_FUNCTIONAL_CURRENCY = 'N' THEN
243 C_AMOUNT := ROUND(NVL(AMOUNT
244 ,0)
245 ,L_PRECISION);
246 RETURN (0);
247 END IF;
248 IF P_USE_FUNCTIONAL_CURRENCY = 'Y' THEN
249 IF CURRENCY1 = RP_FUNCTIONAL_CURRENCY THEN
250 L_CONVERSION_RATE := 1;
251 ELSE
252 IF CONVERSION_RATE IS NULL THEN
253 L_CONVERSION_RATE := GET_RATE(P_SOB_ID
254 ,CURRENCY1
255 ,ORDER_DATE
256 ,CONVERSION_TYPE_CODE);
257 ELSE
258 L_CONVERSION_RATE := CONVERSION_RATE;
259 END IF;
260 END IF;
261 C_AMOUNT := ROUND((NVL(L_CONVERSION_RATE
262 ,0) * NVL(AMOUNT
263 ,0))
264 ,L_PRECISION);
265 RETURN (0);
266 END IF;
267 EXCEPTION
268 WHEN NO_DATA_FOUND THEN
269 C_AMOUNT := 0;
270 RETURN (0);
271 WHEN OTHERS THEN
272 C_AMOUNT := 0;
273 RETURN (0);
274 END;
275 RETURN NULL;
276 END C_CONVERT_AMOUNTFORMULA;
277
278 FUNCTION RP_USE_FUNCTIONAL_CURRENCYFORM RETURN VARCHAR2 IS
279 BEGIN
280 DECLARE
281 L_TEMP VARCHAR2(100);
282 BEGIN
283 SELECT
284 MEANING
285 INTO L_TEMP
286 FROM
287 FND_LOOKUPS
288 WHERE LOOKUP_CODE = P_USE_FUNCTIONAL_CURRENCY
289 AND LOOKUP_TYPE = 'YES_NO';
290 RETURN (L_TEMP);
291 EXCEPTION
292 WHEN NO_DATA_FOUND THEN
293 RETURN ('No');
294 END;
295 RETURN NULL;
296 END RP_USE_FUNCTIONAL_CURRENCYFORM;
297
298 FUNCTION C_CONVERT_SVC_AMOUNTFORMULA(CURRENCY1 IN VARCHAR2
299 ,SVC_AMOUNT IN NUMBER
300 ,CONVERSION_TYPE_CODE IN VARCHAR2
301 ,ORDER_DATE IN DATE
302 ,CONVERSION_RATE IN NUMBER) RETURN NUMBER IS
303 BEGIN
304 DECLARE
305 L_CONVERSION_RATE NUMBER(15);
306 L_CURRENCY VARCHAR2(15);
307 L_PRECISION NUMBER;
308 BEGIN
309 /*SRW.REFERENCE(CURRENCY1)*/NULL;
310 /*SRW.REFERENCE(RP_FUNCTIONAL_CURRENCY)*/NULL;
311 /*SRW.REFERENCE(C_SVC_AMOUNT)*/NULL;
312 /*SRW.REFERENCE(SVC_AMOUNT)*/NULL;
313 /*SRW.REFERENCE(CONVERSION_TYPE_CODE)*/NULL;
314 /*SRW.REFERENCE(ORDER_DATE)*/NULL;
315 L_CONVERSION_RATE := 0;
316 BEGIN
317 IF P_USE_FUNCTIONAL_CURRENCY = 'N' THEN
318 L_CURRENCY := CURRENCY1;
319 ELSE
320 L_CURRENCY := RP_FUNCTIONAL_CURRENCY;
321 END IF;
322 SELECT
323 PRECISION
324 INTO L_PRECISION
325 FROM
326 FND_CURRENCIES
327 WHERE CURRENCY_CODE = L_CURRENCY;
328 EXCEPTION
329 WHEN NO_DATA_FOUND THEN
330 L_PRECISION := 2;
331 END;
332 IF P_USE_FUNCTIONAL_CURRENCY = 'N' THEN
333 C_SVC_AMOUNT := ROUND(NVL(SVC_AMOUNT
334 ,0)
335 ,L_PRECISION);
336 RETURN (0);
337 END IF;
338 IF P_USE_FUNCTIONAL_CURRENCY = 'Y' THEN
339 IF CURRENCY1 = RP_FUNCTIONAL_CURRENCY THEN
340 L_CONVERSION_RATE := 1;
341 ELSE
342 IF CONVERSION_RATE IS NULL THEN
343 L_CONVERSION_RATE := GET_RATE(P_SOB_ID
344 ,CURRENCY1
345 ,ORDER_DATE
346 ,CONVERSION_TYPE_CODE);
347 ELSE
348 L_CONVERSION_RATE := CONVERSION_RATE;
349 END IF;
350 END IF;
351 C_SVC_AMOUNT := ROUND((NVL(L_CONVERSION_RATE
352 ,0) * NVL(SVC_AMOUNT
353 ,0))
354 ,L_PRECISION);
355 RETURN (0);
356 END IF;
357 EXCEPTION
358 WHEN NO_DATA_FOUND THEN
359 C_SVC_AMOUNT := 0;
360 RETURN (0);
361 WHEN OTHERS THEN
362 C_SVC_AMOUNT := 0;
363 RETURN (0);
364 END;
365 RETURN NULL;
366 END C_CONVERT_SVC_AMOUNTFORMULA;
367
368 FUNCTION S_AMOUNT_ONFORMULA(C_AMT_INV1 IN NUMBER
369 ,C_SVC_AMT1 IN NUMBER) RETURN NUMBER IS
370 BEGIN
371 RETURN (NVL(C_AMT_INV1
372 ,0) + NVL(C_SVC_AMT1
373 ,0));
374 END S_AMOUNT_ONFORMULA;
375
376 FUNCTION S_AMOUNT_CUFORMULA(C_AMT_INV_CU IN NUMBER
377 ,C_SVC_AMT_INV_CU IN NUMBER) RETURN NUMBER IS
378 BEGIN
379 RETURN (NVL(C_AMT_INV_CU
380 ,0) + NVL(C_SVC_AMT_INV_CU
381 ,0));
382 END S_AMOUNT_CUFORMULA;
383
384 FUNCTION S_AMOUNT_CURFORMULA(C_AMT_INV_CUR IN NUMBER
385 ,C_SVC_AMT_INV_CUR IN NUMBER) RETURN NUMBER IS
386 BEGIN
387 RETURN (NVL(C_AMT_INV_CUR
388 ,0) + NVL(C_SVC_AMT_INV_CUR
389 ,0));
390 END S_AMOUNT_CURFORMULA;
391
392 FUNCTION S_AMOUNT_CPBFORMULA(C_AMT_INV_CPB IN NUMBER
393 ,C_SVC_AMT_INV_CPB IN NUMBER) RETURN NUMBER IS
394 BEGIN
395 RETURN (NVL(C_AMT_INV_CPB
396 ,0) + NVL(C_SVC_AMT_INV_CPB
397 ,0));
398 END S_AMOUNT_CPBFORMULA;
399
400 FUNCTION S_AMOUNT_OTFORMULA(C_AMT_INV_OT IN NUMBER
401 ,C_SVC_AMT_OT IN NUMBER) RETURN NUMBER IS
402 BEGIN
403 RETURN (NVL(C_AMT_INV_OT
404 ,0) + NVL(C_SVC_AMT_OT
405 ,0));
406 END S_AMOUNT_OTFORMULA;
407
408 FUNCTION S_AMOUNT_SPFORMULA(C_AMT_INV_SP IN NUMBER
409 ,C_SVC_AMT_INV_SP IN NUMBER) RETURN NUMBER IS
410 BEGIN
411 RETURN (NVL(C_AMT_INV_SP
412 ,0) + NVL(C_SVC_AMT_INV_SP
413 ,0));
414 END S_AMOUNT_SPFORMULA;
415
416 FUNCTION BEFOREPFORM RETURN BOOLEAN IS
417 BEGIN
418 RETURN (TRUE);
419 END BEFOREPFORM;
420
421 FUNCTION RP_ORDER_CATEGORYFORMULA RETURN CHAR IS
422 BEGIN
423 DECLARE
424 L_MEANING VARCHAR2(80);
425 BEGIN
426 SELECT
427 MEANING
428 INTO L_MEANING
429 FROM
430 OE_LOOKUPS
431 WHERE LOOKUP_TYPE = 'REPORT_ORDER_CATEGORY'
432 AND LOOKUP_CODE = P_ORDER_CATEGORY;
433 RETURN (L_MEANING);
434 EXCEPTION
435 WHEN NO_DATA_FOUND THEN
436 RETURN (NULL);
437 END;
438 RETURN NULL;
439 END RP_ORDER_CATEGORYFORMULA;
440
441 FUNCTION RP_LINE_CATEGORYFORMULA RETURN CHAR IS
442 BEGIN
443 DECLARE
444 L_MEANING VARCHAR2(80);
445 BEGIN
446 SELECT
447 MEANING
448 INTO L_MEANING
449 FROM
450 OE_LOOKUPS
451 WHERE LOOKUP_TYPE = 'REPORT_LINE_DISPLAY'
452 AND LOOKUP_CODE = P_LINE_CATEGORY;
453 RETURN (L_MEANING);
454 EXCEPTION
455 WHEN NO_DATA_FOUND THEN
456 RETURN (NULL);
457 END;
458 RETURN NULL;
459 END RP_LINE_CATEGORYFORMULA;
460
461 FUNCTION C_MASTER_ORGFORMULA RETURN CHAR IS
462 V_MASTER_ORG VARCHAR2(20);
463 BEGIN
464 SELECT
465 NVL(OE_SYS_PARAMETERS.VALUE('MASTER_ORGANIZATION_ID'
466 ,MO_GLOBAL.GET_CURRENT_ORG_ID)
467 ,0)
468 INTO V_MASTER_ORG
469 FROM
470 DUAL;
471 RETURN V_MASTER_ORG;
472 END C_MASTER_ORGFORMULA;
473
474 FUNCTION C_QUANTITY_CURFORMULA(S_QUANTITY_CUR IN NUMBER
475 ,S_SVC_QUANTITY_CUR IN NUMBER) RETURN NUMBER IS
476 BEGIN
477 RETURN (S_QUANTITY_CUR + S_SVC_QUANTITY_CUR);
478 END C_QUANTITY_CURFORMULA;
479
480 FUNCTION C_QUANTITY_SPFORMULA(S_QUANTITY_SP IN NUMBER
481 ,S_SVC_QUANTITY_SP IN NUMBER) RETURN NUMBER IS
482 BEGIN
483 RETURN (S_QUANTITY_SP + S_SVC_QUANTITY_SP);
484 END C_QUANTITY_SPFORMULA;
485
486 FUNCTION CF_1FORMULA(S_QUANTITY_OT IN NUMBER
487 ,S_SVC_QUANTITY_OT IN NUMBER) RETURN NUMBER IS
488 BEGIN
489 RETURN (S_QUANTITY_OT + S_SVC_QUANTITY_OT);
490 END CF_1FORMULA;
491
492 FUNCTION CF_1FORMULA0009(S_QUANTITY_CPB IN NUMBER
493 ,S_SVC_QUANTITY_CPB IN NUMBER) RETURN NUMBER IS
494 BEGIN
495 RETURN (S_QUANTITY_CPB + S_SVC_QUANTITY_CPB);
496 END CF_1FORMULA0009;
497
498 FUNCTION CF_1FORMULA0011(S_QUANTITY_CU IN NUMBER
499 ,S_SVC_QUANTITY_CU IN NUMBER) RETURN NUMBER IS
500 BEGIN
501 RETURN (S_QUANTITY_CU + S_SVC_QUANTITY_CU);
502 END CF_1FORMULA0011;
503
504 FUNCTION C_QUANTITY_ONFORMULA(S_QUANTITY_ON IN NUMBER
505 ,S_SVC_QUANTITY_ON IN NUMBER) RETURN NUMBER IS
506 BEGIN
507 RETURN (S_QUANTITY_ON + S_SVC_QUANTITY_ON);
508 END C_QUANTITY_ONFORMULA;
509
510 FUNCTION C_PRECISIONFORMULA(CURRENCY1 IN VARCHAR2) RETURN NUMBER IS
511 BEGIN
512 DECLARE
513 W_PRECISION NUMBER;
514 BEGIN
515 SELECT
516 PRECISION
517 INTO W_PRECISION
518 FROM
519 FND_CURRENCIES
520 WHERE CURRENCY_CODE = CURRENCY1;
521 RETURN (W_PRECISION);
522 EXCEPTION
523 WHEN NO_DATA_FOUND THEN
524 W_PRECISION := 2;
525 RETURN (W_PRECISION);
526 END;
527 RETURN NULL;
528 END C_PRECISIONFORMULA;
529
530 FUNCTION C_CURRENCYFORMULA(CURRENCY1 IN VARCHAR2) RETURN CHAR IS
531 BEGIN
532 IF P_USE_FUNCTIONAL_CURRENCY = 'N' THEN
533 RETURN (CURRENCY1);
534 ELSE
535 RETURN (RP_FUNCTIONAL_CURRENCY);
536 END IF;
537 END C_CURRENCYFORMULA;
538
539 FUNCTION C_AMOUNT_P RETURN NUMBER IS
540 BEGIN
541 RETURN C_AMOUNT;
542 END C_AMOUNT_P;
543
544 FUNCTION C_SVC_AMOUNT_P RETURN NUMBER IS
545 BEGIN
546 RETURN C_SVC_AMOUNT;
547 END C_SVC_AMOUNT_P;
548
549 FUNCTION RP_SVC_DUMMY_ITEM_P RETURN DATE IS
550 BEGIN
551 RETURN RP_SVC_DUMMY_ITEM;
552 END RP_SVC_DUMMY_ITEM_P;
553
554 FUNCTION RP_REPORT_NAME_P RETURN VARCHAR2 IS
555 BEGIN
556 RETURN RP_REPORT_NAME;
557 END RP_REPORT_NAME_P;
558
559 FUNCTION RP_SUB_TITLE_P RETURN VARCHAR2 IS
560 BEGIN
561 RETURN RP_SUB_TITLE;
562 END RP_SUB_TITLE_P;
563
564 FUNCTION RP_COMPANY_NAME_P RETURN VARCHAR2 IS
565 BEGIN
566 RETURN RP_COMPANY_NAME;
567 END RP_COMPANY_NAME_P;
568
569 FUNCTION RP_FUNCTIONAL_CURRENCY_P RETURN VARCHAR2 IS
570 BEGIN
571 RETURN RP_FUNCTIONAL_CURRENCY;
572 END RP_FUNCTIONAL_CURRENCY_P;
573
574 FUNCTION RP_DATA_FOUND_P RETURN VARCHAR2 IS
575 BEGIN
576 RETURN RP_DATA_FOUND;
577 END RP_DATA_FOUND_P;
578
579 FUNCTION RP_ITEM_FLEX_ALL_SEG_P RETURN VARCHAR2 IS
580 BEGIN
581 RETURN RP_ITEM_FLEX_ALL_SEG;
582 END RP_ITEM_FLEX_ALL_SEG_P;
583
584 FUNCTION RP_ORDER_NUMBER_RANGE_P RETURN VARCHAR2 IS
585 BEGIN
586 RETURN RP_ORDER_NUMBER_RANGE;
587 END RP_ORDER_NUMBER_RANGE_P;
588
589 FUNCTION RP_SALESREP_RANGE_P RETURN VARCHAR2 IS
590 BEGIN
591 RETURN RP_SALESREP_RANGE;
592 END RP_SALESREP_RANGE_P;
593
594 FUNCTION RP_CUSTOMER_NAME_RANGE_P RETURN VARCHAR2 IS
595 BEGIN
596 RETURN RP_CUSTOMER_NAME_RANGE;
597 END RP_CUSTOMER_NAME_RANGE_P;
598
599 FUNCTION RP_ORDER_TYPE_RANGE_P RETURN VARCHAR2 IS
600 BEGIN
601 RETURN RP_ORDER_TYPE_RANGE;
602 END RP_ORDER_TYPE_RANGE_P;
603
604 FUNCTION RP_OPEN_ORDERS_ONLY_P RETURN VARCHAR2 IS
605 BEGIN
606 RETURN RP_OPEN_ORDERS_ONLY;
607 END RP_OPEN_ORDERS_ONLY_P;
608
609 FUNCTION RP_PRINT_DESCRIPTION_P RETURN VARCHAR2 IS
610 BEGIN
611 RETURN RP_PRINT_DESCRIPTION;
612 END RP_PRINT_DESCRIPTION_P;
613
614 FUNCTION RP_DUMMY_ITEM_P RETURN VARCHAR2 IS
615 BEGIN
616 RETURN RP_DUMMY_ITEM;
617 END RP_DUMMY_ITEM_P;
618
619 FUNCTION IS_FIXED_RATE(X_FROM_CURRENCY IN VARCHAR2
620 ,X_TO_CURRENCY IN VARCHAR2
621 ,X_EFFECTIVE_DATE IN DATE) RETURN VARCHAR2 IS
622 X0 VARCHAR2(2000);
623 BEGIN
624 /* STPROC.INIT('begin :X0 := GL_CURRENCY_API.IS_FIXED_RATE(:X_FROM_CURRENCY, :X_TO_CURRENCY, :X_EFFECTIVE_DATE); end;');
625 STPROC.BIND_O(X0);
626 STPROC.BIND_I(X_FROM_CURRENCY);
627 STPROC.BIND_I(X_TO_CURRENCY);
628 STPROC.BIND_I(X_EFFECTIVE_DATE);
629 STPROC.EXECUTE;
630 STPROC.RETRIEVE(1
631 ,X0);*/
632
633 X0 := GL_CURRENCY_API.IS_FIXED_RATE(X_FROM_CURRENCY, X_TO_CURRENCY, X_EFFECTIVE_DATE);
634
635 RETURN X0;
636 END IS_FIXED_RATE;
637
638 PROCEDURE GET_RELATION(X_FROM_CURRENCY IN VARCHAR2
639 ,X_TO_CURRENCY IN VARCHAR2
640 ,X_EFFECTIVE_DATE IN DATE
641 ,X_FIXED_RATE IN OUT NOCOPY BOOLEAN
642 ,X_RELATIONSHIP IN OUT NOCOPY VARCHAR2) IS
643 BEGIN
644 /*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);
645 :X_FIXED_RATE := sys.diutil.bool_to_int(X_FIXED_RATE); end;');
646 STPROC.BIND_IO(X_FIXED_RATE);
647 STPROC.BIND_I(X_FROM_CURRENCY);
648 STPROC.BIND_I(X_TO_CURRENCY);
649 STPROC.BIND_I(X_EFFECTIVE_DATE);
650 STPROC.BIND_IO(X_RELATIONSHIP);
651 STPROC.EXECUTE;
652 STPROC.RETRIEVE(1
653 ,X_FIXED_RATE);
654 STPROC.RETRIEVE(5
655 ,X_RELATIONSHIP);*/
656
657 GL_CURRENCY_API.GET_RELATION(X_FROM_CURRENCY, X_TO_CURRENCY, X_EFFECTIVE_DATE, X_FIXED_RATE, X_RELATIONSHIP);
658
659 END GET_RELATION;
660
661 FUNCTION GET_EURO_CODE RETURN VARCHAR2 IS
662 X0 VARCHAR2(2000);
663 BEGIN
664 /* STPROC.INIT('begin :X0 := GL_CURRENCY_API.GET_EURO_CODE; end;');
665 STPROC.BIND_O(X0);
666 STPROC.EXECUTE;
667 STPROC.RETRIEVE(1
668 ,X0);*/
669
670 X0 := GL_CURRENCY_API.GET_EURO_CODE;
671 RETURN X0;
672 END GET_EURO_CODE;
673
674 FUNCTION GET_RATE(X_FROM_CURRENCY IN VARCHAR2
675 ,X_TO_CURRENCY IN VARCHAR2
676 ,X_CONVERSION_DATE IN DATE
677 ,X_CONVERSION_TYPE IN VARCHAR2) RETURN NUMBER IS
678 X0 NUMBER;
679 BEGIN
680 /*STPROC.INIT('begin :X0 := GL_CURRENCY_API.GET_RATE(:X_FROM_CURRENCY, :X_TO_CURRENCY, :X_CONVERSION_DATE, :X_CONVERSION_TYPE); end;');
681 STPROC.BIND_O(X0);
682 STPROC.BIND_I(X_FROM_CURRENCY);
683 STPROC.BIND_I(X_TO_CURRENCY);
684 STPROC.BIND_I(X_CONVERSION_DATE);
685 STPROC.BIND_I(X_CONVERSION_TYPE);
686 STPROC.EXECUTE;
687 STPROC.RETRIEVE(1
688 ,X0);*/
689 X0 := GL_CURRENCY_API.GET_RATE(X_FROM_CURRENCY, X_TO_CURRENCY, X_CONVERSION_DATE, X_CONVERSION_TYPE);
690 RETURN X0;
691 END GET_RATE;
692
693 FUNCTION GET_RATE(X_SET_OF_BOOKS_ID IN NUMBER
694 ,X_FROM_CURRENCY IN VARCHAR2
695 ,X_CONVERSION_DATE IN DATE
696 ,X_CONVERSION_TYPE IN VARCHAR2) RETURN NUMBER IS
697 X0 NUMBER;
698 BEGIN
699 /* STPROC.INIT('begin :X0 := GL_CURRENCY_API.GET_RATE(:X_SET_OF_BOOKS_ID, :X_FROM_CURRENCY, :X_CONVERSION_DATE, :X_CONVERSION_TYPE); end;');
700 STPROC.BIND_O(X0);
701 STPROC.BIND_I(X_SET_OF_BOOKS_ID);
702 STPROC.BIND_I(X_FROM_CURRENCY);
703 STPROC.BIND_I(X_CONVERSION_DATE);
704 STPROC.BIND_I(X_CONVERSION_TYPE);
705 STPROC.EXECUTE;
706 STPROC.RETRIEVE(1
707 ,X0);*/
708
709 X0 := GL_CURRENCY_API.GET_RATE(X_SET_OF_BOOKS_ID, X_FROM_CURRENCY, X_CONVERSION_DATE, X_CONVERSION_TYPE);
710 RETURN X0;
711 END GET_RATE;
712
713 FUNCTION CONVERT_AMOUNT(X_FROM_CURRENCY IN VARCHAR2
714 ,X_TO_CURRENCY IN VARCHAR2
715 ,X_CONVERSION_DATE IN DATE
716 ,X_CONVERSION_TYPE IN VARCHAR2
717 ,X_AMOUNT IN NUMBER) RETURN NUMBER IS
718 X0 NUMBER;
719 BEGIN
720 /*STPROC.INIT('begin :X0 := GL_CURRENCY_API.CONVERT_AMOUNT(:X_FROM_CURRENCY, :X_TO_CURRENCY, :X_CONVERSION_DATE, :X_CONVERSION_TYPE, :X_AMOUNT); end;');
721 STPROC.BIND_O(X0);
722 STPROC.BIND_I(X_FROM_CURRENCY);
723 STPROC.BIND_I(X_TO_CURRENCY);
724 STPROC.BIND_I(X_CONVERSION_DATE);
725 STPROC.BIND_I(X_CONVERSION_TYPE);
726 STPROC.BIND_I(X_AMOUNT);
727 STPROC.EXECUTE;
728 STPROC.RETRIEVE(1
729 ,X0);*/
730 X0 := GL_CURRENCY_API.CONVERT_AMOUNT(X_FROM_CURRENCY, X_TO_CURRENCY, X_CONVERSION_DATE, X_CONVERSION_TYPE, X_AMOUNT);
731
732 RETURN X0;
733 END CONVERT_AMOUNT;
734
735 FUNCTION CONVERT_AMOUNT(X_SET_OF_BOOKS_ID IN NUMBER
736 ,X_FROM_CURRENCY IN VARCHAR2
737 ,X_CONVERSION_DATE IN DATE
738 ,X_CONVERSION_TYPE IN VARCHAR2
739 ,X_AMOUNT IN NUMBER) RETURN NUMBER IS
740 X0 NUMBER;
741 BEGIN
742 /*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;');
743 STPROC.BIND_O(X0);
744 STPROC.BIND_I(X_SET_OF_BOOKS_ID);
745 STPROC.BIND_I(X_FROM_CURRENCY);
746 STPROC.BIND_I(X_CONVERSION_DATE);
747 STPROC.BIND_I(X_CONVERSION_TYPE);
748 STPROC.BIND_I(X_AMOUNT);
749 STPROC.EXECUTE;
750 STPROC.RETRIEVE(1
751 ,X0);*/
752 X0 := GL_CURRENCY_API.CONVERT_AMOUNT(X_SET_OF_BOOKS_ID, X_FROM_CURRENCY, X_CONVERSION_DATE, X_CONVERSION_TYPE, X_AMOUNT);
753 RETURN X0;
754 END CONVERT_AMOUNT;
755
756 FUNCTION GET_DERIVE_TYPE(SOB_ID IN NUMBER
757 ,PERIOD IN VARCHAR2
758 ,CURR_CODE IN VARCHAR2) RETURN VARCHAR2 IS
759 X0 VARCHAR2(2000);
760 BEGIN
761 /*STPROC.INIT('begin :X0 := GL_CURRENCY_API.GET_DERIVE_TYPE(:SOB_ID, :PERIOD, :CURR_CODE); end;');
762 STPROC.BIND_O(X0);
763 STPROC.BIND_I(SOB_ID);
764 STPROC.BIND_I(PERIOD);
765 STPROC.BIND_I(CURR_CODE);
766 STPROC.EXECUTE;
767 STPROC.RETRIEVE(1
768 ,X0);*/
769 X0 := GL_CURRENCY_API.GET_DERIVE_TYPE(SOB_ID, PERIOD, CURR_CODE);
770 RETURN X0;
771 END GET_DERIVE_TYPE;
772
773 FUNCTION RATE_EXISTS(X_FROM_CURRENCY IN VARCHAR2
774 ,X_TO_CURRENCY IN VARCHAR2
775 ,X_CONVERSION_DATE IN DATE
776 ,X_CONVERSION_TYPE IN VARCHAR2) RETURN VARCHAR2 IS
777 X0 VARCHAR2(2000);
778 BEGIN
779 /*STPROC.INIT('begin :X0 := GL_CURRENCY_API.RATE_EXISTS(:X_FROM_CURRENCY, :X_TO_CURRENCY, :X_CONVERSION_DATE, :X_CONVERSION_TYPE); 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_CONVERSION_DATE);
784 STPROC.BIND_I(X_CONVERSION_TYPE);
785 STPROC.EXECUTE;
786 STPROC.RETRIEVE(1
787 ,X0);*/
788
789 X0 := GL_CURRENCY_API.RATE_EXISTS(X_FROM_CURRENCY, X_TO_CURRENCY, X_CONVERSION_DATE, X_CONVERSION_TYPE);
790 RETURN X0;
791 END RATE_EXISTS;
792
793
794 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
795 BEGIN
796
797 DECLARE
798 BEGIN
799
800 /*BEGIN
801 SRW.USER_EXIT('FND SRWINIT');
802 EXCEPTION
803 WHEN SRW.USER_EXIT_FAILURE THEN
804 SRW.MESSAGE (1000,'Failed in BEFORE REPORT trigger');
805 return (FALSE);
806 END;*/null;
807
808
809 BEGIN /*MOAC*/
810
811 LP_ORG_ID:= MO_GLOBAL.GET_CURRENT_ORG_ID();
812
813 END;
814
815 /*
816 DECLARE
817 COUNT_OE_ORDER_HEADERS NUMBER(10);
818 COUNT_oe_order_lines_all NUMBER(10);
819 COUNT_oe_transaction_types_tl NUMBER(10);
820 COUNT_hz_cust_site_uses_all NUMBER(10);
821 COUNT_hz_cust_acct_sites_all NUMBER(10);
822 COUNT_hz_party_sites NUMBER(10);
823 COUNT_hz_locations NUMBER(10);
824 COUNT_ra_salesreps_all NUMBER(10);
825 COUNT_fnd_territories_vl NUMBER(10);
826 COUNT_hz_parties NUMBER(10);
827 COUNT_hz_cust_accounts NUMBER(10);
828
829 BEGIN
830
831 SELECT COUNT(*) INTO COUNT_OE_ORDER_HEADERS FROM OE_ORDER_HEADERS;
832
833 SELECT COUNT(*) INTO COUNT_oe_order_lines_all FROM oe_order_lines_all;
834
835 SELECT COUNT(*) INTO COUNT_oe_transaction_types_tl FROM oe_transaction_types_tl;
836
837 SELECT COUNT(*) INTO COUNT_hz_cust_site_uses_all FROM hz_cust_site_uses_all;
838
839 SELECT COUNT(*) INTO COUNT_hz_cust_acct_sites_all FROM hz_cust_acct_sites_all;
840
841 SELECT COUNT(*) INTO COUNT_hz_party_sites FROM hz_party_sites;
842
843 SELECT COUNT(*) INTO COUNT_hz_locations FROM hz_locations;
844
845 SELECT COUNT(*) INTO COUNT_ra_salesreps_all FROM ra_salesreps_all;
846
847 SELECT COUNT(*) INTO COUNT_fnd_territories_vl FROM fnd_territories_vl;
848
849 SELECT COUNT(*) INTO COUNT_hz_parties FROM hz_parties;
850
851 SELECT COUNT(*) INTO COUNT_hz_cust_accounts FROM hz_cust_accounts;
852
853 DSP_COUNT:= DSP_COUNT ||' COUNT_OE_ORDER_HEADERS-->' || COUNT_OE_ORDER_HEADERS || ' COUNT_oe_order_lines_all--->' || COUNT_oe_order_lines_all ||'COUNT_oe_transaction_types_tl--->' || COUNT_oe_transaction_types_tl || 'COUNT_hz_cust_site_uses_all--->' ||
854 COUNT_hz_cust_site_uses_all || 'COUNT_hz_cust_acct_sites_all--->' || COUNT_hz_cust_acct_sites_all || 'COUNT_hz_party_sites--->'||COUNT_hz_party_sites|| 'COUNT_hz_locations-->' ||COUNT_hz_locations||'COUNT_ra_salesreps_all-->' ||
855 COUNT_ra_salesreps_all||' COUNT_fnd_territories_vl-->'|| COUNT_fnd_territories_vl || 'COUNT_hz_parties--->' || COUNT_hz_parties||' COUNT_hz_cust_accounts-->'|| COUNT_hz_cust_accounts;
856
857 END;*/
858 /*------------------------------------------------------------------------------
859 Following PL/SQL block gets the company name, functional currency and precision.
860 ------------------------------------------------------------------------------*/
861
862
863 DECLARE
864 l_company_name VARCHAR2 (100);
865 l_functional_currency VARCHAR2 (15);
866
867 BEGIN
868
869 SELECT sob.name ,
870 sob.currency_code
871 INTO
872 l_company_name ,
873 l_functional_currency
874 FROM gl_sets_of_books sob,
875 fnd_currencies cur
876 WHERE sob.set_of_books_id = p_sob_id
877 AND sob.currency_code = cur.currency_code
878 ;
879
880 rp_company_name := l_company_name;
881 rp_functional_currency := l_functional_currency ;
882
883 EXCEPTION
884 WHEN NO_DATA_FOUND THEN
885 NULL ;
886 END ;
887
888 /*------------------------------------------------------------------------------
889 Following PL/SQL block gets the report name for the passed concurrent request Id.
890 ------------------------------------------------------------------------------*/
891 DECLARE
892 l_report_name VARCHAR2(240);
893 BEGIN
894 SELECT cp.user_concurrent_program_name
895 INTO l_report_name
896 FROM FND_CONCURRENT_PROGRAMS_VL cp,
897 FND_CONCURRENT_REQUESTS cr
898 WHERE cr.request_id = P_CONC_REQUEST_ID
899 AND cp.application_id = cr.program_application_id
900 AND cp.concurrent_program_id = cr.concurrent_program_id
901 ;
902
903 RP_Report_Name := l_report_name;
904 EXCEPTION
905 WHEN NO_DATA_FOUND
906 THEN RP_REPORT_NAME := 'Order/Invoice Detail Report';
907 END;
908
909 /*------------------------------------------------------------------------------
910 Following PL/SQL block builds up the lexical parameters, to be used in the
911 WHERE clause of the query. This also populates the report level variables, used
912 to store the flexfield structure.
913 ------------------------------------------------------------------------------*/
914 /*BEGIN
915 SRW.REFERENCE(P_item_flex_code);
916 SRW.REFERENCE(P_item_structure_num);
917
918 SRW.USER_EXIT('FND FLEXSQL CODE=":p_item_flex_code"
919 NUM=":p_item_structure_num"
920 APPL_SHORT_NAME="INV"
921 OUTPUT=":rp_item_flex_all_seg"
922 MODE="SELECT"
923 DISPLAY="ALL"
924 TABLEALIAS="SI"
925 ');
926 EXCEPTION
927 WHEN SRW.USER_EXIT_FAILURE THEN
928 srw.message(2000,'Failed in BEFORE REPORT trigger. FND FLEXSQL USER_EXIT');
929 END;*/
930
931
932 DECLARE
933 l_order_type_low VARCHAR2 (50);
934 l_order_type_high VARCHAR2 (50);
935 l_customer_name_low VARCHAR2 (50);
936 l_customer_name_high VARCHAR2 (50);
937 l_salesrep_low VARCHAR2 (50);
938 l_salesrep_high VARCHAR2 (50);
939 l_order_number_low VARCHAR2 (50);
940 l_order_number_high VARCHAR2 (50);
941
942 BEGIN
943
944 if ( p_order_type_low is NULL) AND ( p_order_type_high is NULL ) then
945 NULL ;
946 else
947 if p_order_type_low is NULL then
948 l_order_type_low := ' ';
949 else
950 l_order_type_low := substr(l_order_type_low ,1,18);
951 end if ;
952 if p_order_type_high is NULL then
953 l_order_type_high := ' ';
954 else
955 l_order_type_high := substr(l_order_type_high,1,18);
956 end if ;
957 rp_order_type_range := 'From '||l_order_type_low||' To '||l_order_type_high ;
958
959 end if ;
960
961
962 if ( p_customer_name_low is NULL) AND ( p_customer_name_high is NULL ) then
963 NULL ;
964 else
965 if p_customer_name_low is NULL then
966 l_customer_name_low := ' ';
967 else
968 l_customer_name_low := substr(p_customer_name_low,1,18) ;
969 end if ;
970 if p_customer_name_high is NULL then
971 l_customer_name_high := ' ';
972 else
973 l_customer_name_high := substr(p_customer_name_high,1,18);
974 end if ;
975 rp_customer_name_range := 'From '||l_customer_name_low||' To '||l_customer_name_high ;
976 end if ;
977
978 if ( p_salesrep_low is NULL) AND ( p_salesrep_high is NULL ) then
979 NULL ;
980 else
981 if p_salesrep_low is NULL then
982 l_salesrep_low := ' ';
983 else
984 l_salesrep_low := substr(p_salesrep_low,1,18) ;
985 end if ;
986 if p_salesrep_high is NULL then
987 l_salesrep_high := ' ';
988 else
989 l_salesrep_high := substr(p_salesrep_high,1,18);
990 end if ;
991 rp_salesrep_range := 'From '||l_salesrep_low||' To '||l_salesrep_high ;
992 end if ;
993
994 if ( p_order_num_low is NULL) AND ( p_order_num_high is NULL ) then
995 NULL ;
996 else
997 if p_order_num_low is NULL then
998 l_order_number_low := ' ';
999 else
1000 l_order_number_low := substr(p_order_num_low,1,18) ;
1001 end if ;
1002 if p_order_num_high is NULL then
1003 l_order_number_high := ' ';
1004 else
1005 l_order_number_high := substr((p_order_num_high),1,18);
1006 end if ;
1007 rp_order_number_range := 'From '||l_order_number_low||' To '||l_order_number_high ;
1008 end if ;
1009
1010 END ;
1011
1012 DECLARE
1013 l_meaning VARCHAR2 (80);
1014 BEGIN
1015 SELECT MEANING
1016 INTO l_meaning
1017 FROM OE_LOOKUPS
1018 WHERE LOOKUP_TYPE = 'ITEM_DISPLAY_CODE'
1019 AND LOOKUP_CODE = substr(upper(p_print_description),1,1)
1020 ;
1021
1022 rp_print_description := l_meaning ;
1023 EXCEPTION WHEN NO_DATA_FOUND THEN
1024 rp_print_description := 'Internal Item Description';
1025 when OTHERS then
1026 /*srw.message(2000,'Failed in BEFORE REPORT trigger. Get Print Description'); */null;
1027
1028 END ;
1029
1030 /*DECLARE
1031 l_meaning VARCHAR2 (80);
1032 BEGIN
1033 SELECT MEANING
1034 INTO l_meaning
1035 FROM OE_LOOKUPS
1036 WHERE LOOKUP_TYPE = 'ITEM_DISPLAY_CODE'
1037 AND LOOKUP_CODE = substr(upper(p_print_description),1,1)
1038 ;
1039
1040 rp_print_description := l_meaning ;
1041 EXCEPTION WHEN NO_DATA_FOUND THEN
1042 rp_print_description := 'Description';
1043 END ;
1044 */
1045
1046 DECLARE
1047 l_meaning VARCHAR2 (80);
1048 BEGIN
1049 SELECT MEANING
1050 INTO l_meaning
1051 FROM FND_LOOKUPS
1052 WHERE LOOKUP_TYPE = 'YES_NO'
1053 AND LOOKUP_CODE = substr(upper(p_open_orders_only),1,1)
1054 ;
1055
1056 rp_open_orders_only := l_meaning ;
1057 EXCEPTION WHEN NO_DATA_FOUND THEN
1058 rp_open_orders_only := 'Yes';
1059 END ;
1060
1061 END ;
1062 RETURN (TRUE);
1063 END BEFOREREPORT;
1064
1065 FUNCTION ITEM_DSPFORMULA(ITEM_IDENTIFIER_TYPE IN VARCHAR2,INVENTORY_ITEM_ID IN NUMBER, C_MASTER_ORG IN VARCHAR2,ORDERED_ITEM_ID IN NUMBER,ORDERED_ITEM IN VARCHAR2,SI_ORGANIZATION_ID IN NUMBER, SI_INVENTORY_ITEM_ID IN NUMBER) RETURN VARCHAR2 IS
1066 v_item varchar2(2000);
1067 v_description varchar2(500);
1068 begin
1069 if (item_identifier_type is null or item_identifier_type = 'INT')
1070 or (p_print_description in ('I','D','F')) then
1071 select sitems.description description
1072 into v_description
1073 from mtl_system_items_vl sitems
1074 -- where sitems.customer_order_enabled_flag = 'Y'
1075 -- and sitems.bom_item_type in (1,4)
1076 where nvl(sitems.organization_id,0) = c_master_org
1077 and sitems.inventory_item_id = ITEM_DSPFORMULA.inventory_item_id;
1078 rp_dummy_item := v_item;
1079 /* srw.reference (:item_flex);
1080 srw.reference (:p_item_flex_code);
1081 srw.reference (:Item_dsp);
1082 srw.reference (:p_item_structure_num);
1083 srw.user_exit (' FND FLEXIDVAL
1084 CODE=":p_item_flex_code"
1085 NUM=":p_item_structure_num"
1086 APPL_SHORT_NAME="INV"
1087 DATA= ":item_flex"
1088 VALUE=":Item_dsp"
1089 DISPLAY="ALL"'
1090 );*/
1091 -- rp_dummy_item := '';
1092 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');
1093 elsif (item_identifier_type = 'CUST' and p_print_description in ('C','P','O')) then
1094 select citems.customer_item_number item,
1095 nvl(citems.customer_item_desc,sitems.description) description
1096 into v_item,v_description
1097 from mtl_customer_items citems,
1098 mtl_customer_item_xrefs cxref,
1099 mtl_system_items_vl sitems
1100 where citems.customer_item_id = cxref.customer_item_id
1101 and cxref.inventory_item_id = sitems.inventory_item_id
1102 and citems.customer_item_id = ordered_item_id
1103 and nvl(sitems.organization_id,0) = c_master_org
1104 and sitems.inventory_item_id = ITEM_DSPFORMULA.inventory_item_id;
1105 -- and sitems.customer_order_enabled_flag = 'Y'
1106 -- and sitems.bom_item_type in (1,4)
1107 elsif (p_print_description in ('C','P','O')) then
1108 Begin
1109 select items.cross_reference item,
1110 nvl(items.description,sitems.description) description
1111 into v_item,v_description
1112 from mtl_cross_reference_types xtypes,
1113 mtl_cross_references items,
1114 mtl_system_items_vl sitems
1115 where xtypes.cross_reference_type = items.cross_reference_type
1116 and items.inventory_item_id = sitems.inventory_item_id
1117 and items.cross_reference = ordered_item
1118 and items.cross_reference_type = item_identifier_type
1119 and nvl(sitems.organization_id,0) = c_master_org
1120 and sitems.inventory_item_id = ITEM_DSPFORMULA.inventory_item_id
1121 --Bug 3433353 begin
1122 and items.org_independent_flag = 'N'
1123 and items.organization_id = c_master_org;
1124 -- and sitems.customer_order_enabled_flag = 'Y'
1125 -- and sitems.bom_item_type in (1,4)
1126 Exception When NO_DATA_FOUND Then
1127 select items.cross_reference item,
1128 nvl(items.description,sitems.description) description
1129 into v_item,v_description
1130 from mtl_cross_reference_types xtypes,
1131 mtl_cross_references items,
1132 mtl_system_items_vl sitems
1133 where xtypes.cross_reference_type =
1134 items.cross_reference_type
1135 and items.inventory_item_id =
1136 sitems.inventory_item_id
1137 and items.cross_reference = ordered_item
1138 and items.cross_reference_type = item_identifier_type
1139 and nvl(sitems.organization_id,0) = c_master_org
1140 and sitems.inventory_item_id = ITEM_DSPFORMULA.inventory_item_id
1141 and items.org_independent_flag = 'Y';
1142 End;
1143 --Bug 343353 End
1144 end if;
1145
1146 if (p_print_description in ('I','C')) then
1147 return(v_item||' - '||v_description);
1148 elsif (p_print_description in ('D','P')) then
1149 return(v_description);
1150 else
1151 return(v_item);
1152 end if;
1153
1154
1155
1156 RETURN NULL;
1157 Exception
1158 When Others Then
1159 return('Item Not Found');
1160 end ITEM_DSPFORMULA;
1161 END ONT_OEXOEIOD_XMLP_PKG;
1162
1163
[Home] [Help]