1 PACKAGE BODY ONT_OEXOEOCS_XMLP_PKG AS
2 /* $Header: OEXOEOCSB.pls 120.3 2008/05/05 12:37:45 dwkrishn noship $ */
3 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
4 BEGIN
5 BEGIN
6
7 P_ORGANIZATION_ID1 := P_ORGANIZATION_ID;
8
9 BEGIN
10 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
11 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
12 EXCEPTION
13 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
14 BEGIN
15 /*SRW.MESSAGE(1000
16 ,'Failed in BEFORE REPORT trigger')*/NULL;
17 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
18 END;
19 END;
20 BEGIN
21 -- P_ORGANIZATION_ID := MO_GLOBAL.GET_CURRENT_ORG_ID;
22 P_ORGANIZATION_ID1 := MO_GLOBAL.GET_CURRENT_ORG_ID;
23 END;
24 BEGIN
25 IF P_ITEM IS NOT NULL THEN
26 SELECT
27 CONCATENATED_SEGMENTS
28 INTO P_ITEM_NAME
29 FROM
30 MTL_SYSTEM_ITEMS_KFV
31 WHERE INVENTORY_ITEM_ID = P_ITEM
32 AND CUSTOMER_ORDER_ENABLED_FLAG = 'Y'
33 AND BOM_ITEM_TYPE in ( 1 , 4 )
34 AND ORGANIZATION_ID = OE_SYS_PARAMETERS.VALUE('MASTER_ORGANIZATION_ID');
35 END IF;
36 END;
37 DECLARE
38 L_COMPANY_NAME VARCHAR2(100);
39 L_FUNCTIONAL_CURRENCY VARCHAR2(15);
40 BEGIN
41 SELECT
42 SOB.NAME,
43 SOB.CURRENCY_CODE
44 INTO L_COMPANY_NAME,L_FUNCTIONAL_CURRENCY
45 FROM
46 GL_SETS_OF_BOOKS SOB,
47 FND_CURRENCIES CUR
48 WHERE SOB.SET_OF_BOOKS_ID = P_SOB_ID
49 AND SOB.CURRENCY_CODE = CUR.CURRENCY_CODE;
50 RP_COMPANY_NAME := L_COMPANY_NAME;
51 RP_FUNCTIONAL_CURRENCY := L_FUNCTIONAL_CURRENCY;
52 EXCEPTION
53 WHEN NO_DATA_FOUND THEN
54 NULL;
55 END;
56 BEGIN
57 /*SRW.REFERENCE(P_ITEM_FLEX_CODE)*/NULL;
58 /*SRW.REFERENCE(P_ITEM_STRUCTURE_NUM)*/NULL;
59 IF P_ITEM IS NOT NULL THEN
60 LP_ITEM_FLEX_ALL_SEG := ' and ' || RP_ITEM_FLEX_ALL_SEG_WHERE;
61 END IF;
62 IF (lp_item_flex_all_seg IS NULL) THEN
63 lp_item_flex_all_seg := ' ';
64 END IF;
65 EXCEPTION
66 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
67 /*SRW.MESSAGE(1
68 ,'Failed in before report trigger:MSTK')*/NULL;
69 END;
70 DECLARE
71 L_REPORT_NAME VARCHAR2(240);
72 BEGIN
73 SELECT
74 CP.USER_CONCURRENT_PROGRAM_NAME
75 INTO L_REPORT_NAME
76 FROM
77 FND_CONCURRENT_PROGRAMS_VL CP,
78 FND_CONCURRENT_REQUESTS CR
79 WHERE CR.REQUEST_ID = P_CONC_REQUEST_ID
80 AND CP.APPLICATION_ID = CR.PROGRAM_APPLICATION_ID
81 AND CP.CONCURRENT_PROGRAM_ID = CR.CONCURRENT_PROGRAM_ID;
82 RP_REPORT_NAME := L_REPORT_NAME;
83
84 IF (UPPER(RP_REPORT_NAME) = 'CANCELLED ORDERS REPORT (XML)') THEN
85 RP_REPORT_NAME := 'Cancelled Orders Report' ;
86 END IF;
87
88 EXCEPTION
89 WHEN NO_DATA_FOUND THEN
90 RP_REPORT_NAME := 'Cancelled Orders Report';
91 END;
92 END;
93 RETURN (TRUE);
94 END BEFOREREPORT;
95
96 FUNCTION AFTERREPORT RETURN BOOLEAN IS
97 BEGIN
98 BEGIN
99 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
100 EXCEPTION
101 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
102 /*SRW.MESSAGE(1
103 ,'Failed in AFTER REPORT TRIGGER')*/NULL;
104 RETURN (FALSE);
105 END;
106 RETURN (TRUE);
107 END AFTERREPORT;
108
109 FUNCTION P_ITEM_FLEX_CODEVALIDTRIGGER RETURN BOOLEAN IS
110 BEGIN
111 RETURN (TRUE);
112 END P_ITEM_FLEX_CODEVALIDTRIGGER;
113
114 FUNCTION P_USE_FUNCTIONAL_CURRENCYVALID RETURN BOOLEAN IS
115 BEGIN
116 RETURN (TRUE);
117 END P_USE_FUNCTIONAL_CURRENCYVALID;
118
119 FUNCTION AFTERPFORM RETURN BOOLEAN IS
120 BEGIN
121 BEGIN
122 P_ORDER_DATE_LOW1 := TO_CHAR(P_ORDER_DATE_LOW,'DD-MON-YY');
123 P_ORDER_DATE_HIGH1 := TO_CHAR(P_ORDER_DATE_HIGH,'DD-MON-YY');
124 IF P_ORDER_NUM_LOW IS NOT NULL AND P_ORDER_NUM_HIGH IS NOT NULL THEN
125 LP_ORDER_NUM := ' AND h.order_number between to_number(:p_order_num_low) and to_number(:p_order_num_high) ';
126 ELSIF (P_ORDER_NUM_LOW IS NOT NULL) THEN
127 LP_ORDER_NUM := 'and h.order_number >= to_number(:p_order_num_low) ';
128 ELSIF (P_ORDER_NUM_HIGH IS NOT NULL) THEN
129 LP_ORDER_NUM := 'and h.order_number <= to_number(:p_order_num_high) ';
130 END IF;
131
132 IF (lp_order_num IS NULL) THEN
133 lp_order_num := ' ';
134 END IF;
135
136 IF P_SALESREP_LOW IS NOT NULL AND P_SALESREP_HIGH IS NOT NULL THEN
137 LP_SALESREP := ' AND nvl(sr.name,''zzzzzz'') between :p_salesrep_low and :p_salesrep_high ';
138 ELSIF (P_SALESREP_LOW IS NOT NULL) THEN
139 LP_SALESREP := 'and sr.name >= :p_salesrep_low ';
140 ELSIF (P_SALESREP_HIGH IS NOT NULL) THEN
141 LP_SALESREP := 'and sr.name <= :p_salesrep_high ';
142 END IF;
143
144 IF (lp_salesrep IS NULL) THEN
145 lp_salesrep := ' ';
146 END IF;
147
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 END IF;
159
160 IF (lp_order_date IS NULL) THEN
161 lp_order_date := ' ';
162 END IF;
163
164 IF P_CUSTOMER_NAME_LOW IS NOT NULL AND P_CUSTOMER_NAME_HIGH IS NOT NULL THEN
165 LP_CUSTOMER_NAME := ' AND org.name between :p_customer_name_low and :p_customer_name_high ';
166 ELSIF (P_CUSTOMER_NAME_LOW IS NOT NULL) THEN
167 LP_CUSTOMER_NAME := 'and org.name >= :p_customer_name_low ';
168 ELSIF (P_CUSTOMER_NAME_HIGH IS NOT NULL) THEN
169 LP_CUSTOMER_NAME := 'and org.name <= :p_customer_name_high ';
170 END IF;
171
172 IF (lp_customer_name IS NULL) THEN
173 lp_customer_name := ' ';
174 END IF;
175
176 IF P_ORDER_NUM_LOW = P_ORDER_NUM_HIGH THEN
177 NULL;
178 ELSE
179 IF P_ORDER_CATEGORY IS NOT NULL THEN
180 IF P_ORDER_CATEGORY = 'SALES' THEN
181 LP_ORDER_CATEGORY := 'and h.order_category_code in (''ORDER'', ''MIXED'') ';
182 ELSIF P_ORDER_CATEGORY = 'CREDIT' THEN
183 LP_ORDER_CATEGORY := 'and h.order_category_code in (''RETURN'', ''MIXED'') ';
184 ELSIF P_ORDER_CATEGORY = 'ALL' THEN
185 LP_ORDER_CATEGORY := NULL;
186 END IF;
187 ELSE
188 LP_ORDER_CATEGORY := 'and h.order_category_code in (''ORDER'', ''MIXED'') ';
189 END IF;
190 END IF;
191
192 IF (lp_order_category IS NULL) THEN
193 lp_order_category := ' ';
194 END IF;
195 IF P_LINE_CATEGORY IS NOT NULL THEN
196 IF P_LINE_CATEGORY = 'SALES' THEN
197 LP_LINE_CATEGORY := 'and l.line_category_code = ''ORDER'' ';
198 ELSIF P_LINE_CATEGORY = 'CREDIT' THEN
199 LP_LINE_CATEGORY := 'and l.line_category_code = ''RETURN'' ';
200 ELSIF P_LINE_CATEGORY = 'ALL' THEN
201 LP_LINE_CATEGORY := NULL;
202 END IF;
203 ELSE
204 LP_LINE_CATEGORY := 'and l.line_category_code = ''ORDER'' ';
205 END IF;
206 END;
207
208 IF (lp_line_category IS NULL) THEN
209 lp_line_category := ' ';
210 END IF;
211 RETURN (TRUE);
212 END AFTERPFORM;
213
214 function Item_dspFormula(item_identifier_type in varchar2, IID number, ORDERED_ITEM_ID number, ORDERED_ITEM varchar2,C_ORGANIZATION_ID in varchar2,C_INVENTORY_ITEM_ID in varchar2) return Char is
215 v_item varchar2(2000);
216 v_description varchar2(500);
217 begin
218 if (item_identifier_type is null or item_identifier_type = 'INT')
219 or (p_print_description in ('I','D','F')) then
220 select sitems.concatenated_segments item,
221 sitems.description description
222 into v_item,v_description
223 from mtl_system_items_vl sitems
224 where
225 -- sitems.customer_order_enabled_flag = 'Y' --> Commented for the bug 2864636
226 -- and sitems.bom_item_type in (1,4)
227 nvl(sitems.organization_id,0) = nvl(oe_sys_parameters.value('MASTER_ORGANIZATION_ID',mo_global.get_current_org_id()),0)
228 and sitems.inventory_item_id = iid;
229 rp_dummy_item := v_item;
230 rp_dummy_item := '';
231 v_item := fnd_flex_xml_publisher_apis.process_kff_combination_1('Item_dsp', 'INV', p_item_flex_code, p_item_structure_num, C_ORGANIZATION_ID,C_INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE');
232
233 elsif (item_identifier_type = 'CUST' and p_print_description in ('C','P','O')) then
234 select citems.customer_item_number item,
235 nvl(citems.customer_item_desc,sitems.description) description
236 into v_item,v_description
237 from mtl_customer_items citems,
238 mtl_customer_item_xrefs cxref,
239 mtl_system_items_vl sitems
240 where citems.customer_item_id = cxref.customer_item_id
241 and cxref.inventory_item_id = sitems.inventory_item_id
242 and citems.customer_item_id = ordered_item_id
243 and nvl(sitems.organization_id,0) = nvl(oe_sys_parameters.value('MASTER_ORGANIZATION_ID',mo_global.get_current_org_id()),0)
244 and sitems.inventory_item_id = iid;
245 -- and sitems.customer_order_enabled_flag = 'Y'
246 -- and sitems.bom_item_type in (1,4)
247 elsif (p_print_description in ('C','P','O')) then
248 Begin
249 select items.cross_reference item,
250 nvl(items.description,sitems.description) description
251 into v_item,v_description
252 from mtl_cross_reference_types xtypes,
253 mtl_cross_references items,
254 mtl_system_items_vl sitems
255 where xtypes.cross_reference_type = items.cross_reference_type
256 and items.inventory_item_id = sitems.inventory_item_id
257 and items.cross_reference = ordered_item
258 and items.cross_reference_type = item_identifier_type
259 and nvl(sitems.organization_id,0) = nvl(oe_sys_parameters.value('MASTER_ORGANIZATION_ID',mo_global.get_current_org_id()),0)
260 and sitems.inventory_item_id = iid
261 --Bug 3433353 Begin
262 and items.org_independent_flag = 'N'
263 and items.organization_id = nvl(oe_sys_parameters.value('MASTER_ORGANIZATION_ID',mo_global.get_current_org_id()),0);
264 -- and sitems.customer_order_enabled_flag = 'Y'
265 -- and sitems.bom_item_type in (1,4)
266 Exception When NO_DATA_FOUND Then
267 select items.cross_reference item,
268 nvl(items.description,sitems.description) description
269 into v_item,v_description
270 from mtl_cross_reference_types xtypes,
271 mtl_cross_references items,
272 mtl_system_items_vl sitems
273 where xtypes.cross_reference_type =
274 items.cross_reference_type
275 and items.inventory_item_id =
276 sitems.inventory_item_id
277 and items.cross_reference = ordered_item
278 and items.cross_reference_type = item_identifier_type
279 and nvl(sitems.organization_id,0) = nvl(oe_sys_parameters.value('MASTER_ORGANIZATION_ID',mo_global.get_current_org_id()),0)
280 and sitems.inventory_item_id = iid
281 and items.org_independent_flag = 'Y';
282 End;
283
284 end if;
285
286 if (p_print_description in ('I','C')) then
287 return(v_item||' - '||v_description);
288 elsif (p_print_description in ('D','P')) then
289 return(v_description);
290 else
291 return(v_item);
292 end if;
293
294 RETURN NULL;
295 Exception
296 When Others Then
297 return('Item Not Found');
298 end;
299
300
301 FUNCTION C_SET_LBLFORMULA RETURN VARCHAR2 IS
302 BEGIN
303 BEGIN
304 /*SRW.REFERENCE(P_ORDER_BY)*/NULL;
305 IF P_CUSTOMER_NAME_LOW IS NOT NULL OR P_CUSTOMER_NAME_HIGH IS NOT NULL THEN
306 RP_CUSTOMER_RANGE := 'From ' || NVL(SUBSTR(P_CUSTOMER_NAME_LOW
307 ,1
308 ,16)
309 ,' ') || ' To ' || NVL(SUBSTR(P_CUSTOMER_NAME_HIGH
310 ,1
311 ,16)
312 ,' ');
313 END IF;
314 IF P_SALESREP_LOW IS NOT NULL OR P_SALESREP_HIGH IS NOT NULL THEN
315 RP_SALESPERSON_RANGE := 'From ' || NVL(SUBSTR(P_SALESREP_LOW
316 ,1
317 ,16)
318 ,' ') || ' To ' || NVL(SUBSTR(P_SALESREP_HIGH
319 ,1
320 ,16)
321 ,' ');
322 END IF;
323 IF P_ORDER_DATE_LOW IS NOT NULL AND P_ORDER_DATE_HIGH IS NOT NULL THEN
324 --RP_ORDER_DATE_RANGE := 'From ' || P_ORDER_DATE_LOW || ' To ' || P_ORDER_DATE_HIGH;
325 RP_ORDER_DATE_RANGE := 'From ' || P_ORDER_DATE_LOW1 || ' To ' || P_ORDER_DATE_HIGH1;
326 ELSIF P_ORDER_DATE_LOW IS NOT NULL THEN
327 --RP_ORDER_DATE_RANGE := 'From ' || P_ORDER_DATE_LOW || ' To ' || ' ';
328 RP_ORDER_DATE_RANGE := 'From ' || P_ORDER_DATE_LOW1 || ' To ' || ' ';
329 ELSIF P_ORDER_DATE_HIGH IS NOT NULL THEN
330 --RP_ORDER_DATE_RANGE := 'From ' || ' ' || ' To ' || P_ORDER_DATE_HIGH;
331 RP_ORDER_DATE_RANGE := 'From ' || ' ' || ' To ' || P_ORDER_DATE_HIGH1;
332 END IF;
333 IF P_ORDER_NUM_LOW IS NOT NULL OR P_ORDER_NUM_HIGH IS NOT NULL THEN
334 RP_ORDER_RANGE := 'From ' || NVL(P_ORDER_NUM_LOW
335 ,' ') || ' To ' || NVL(P_ORDER_NUM_HIGH
336 ,' ');
337 END IF;
338 IF P_ORDER_BY IS NOT NULL THEN
339 DECLARE
340 ORDER_BY VARCHAR2(80);
341 BEGIN
342 SELECT
343 MEANING
344 INTO ORDER_BY
345 FROM
346 OE_LOOKUPS
347 WHERE LOOKUP_TYPE = 'OEXOEOCS SORT BY'
348 AND LOOKUP_CODE = P_ORDER_BY;
349 RP_ORDER_BY := ORDER_BY;
350 EXCEPTION
351 WHEN NO_DATA_FOUND THEN
352 RP_ORDER_BY := P_ORDER_BY;
353 END;
354 END IF;
355 DECLARE
356 MEANING VARCHAR2(80);
357 BEGIN
358 SELECT
359 MEANING
360 INTO MEANING
361 FROM
362 FND_LOOKUPS
363 WHERE LOOKUP_TYPE = 'YES_NO'
364 AND LOOKUP_CODE = P_USE_FUNCTIONAL_CURRENCY;
365 RP_USE_FUNCTIONAL_CURRENCY := MEANING;
366 EXCEPTION
367 WHEN NO_DATA_FOUND THEN
368 RP_USE_FUNCTIONAL_CURRENCY := P_USE_FUNCTIONAL_CURRENCY;
369 END;
370 DECLARE
371 ITEM_DISPLAY_MEANING VARCHAR2(80);
372 BEGIN
373 SELECT
374 MEANING
375 INTO ITEM_DISPLAY_MEANING
376 FROM
377 OE_LOOKUPS
378 WHERE LOOKUP_TYPE = 'ITEM_DISPLAY_CODE'
379 AND LOOKUP_CODE = P_PRINT_DESCRIPTION;
380 RP_FLEX_OR_DESC := ITEM_DISPLAY_MEANING;
381 END;
382 RETURN (1);
383 END;
384 RETURN NULL;
385 END C_SET_LBLFORMULA;
386
387 FUNCTION C_GL_CONV_RATEFORMULA(CURRENCY2 IN VARCHAR2
388 ,ORD_DATE IN DATE
389 ,CONVERSION_TYPE_CODE IN VARCHAR2
390 ,CONVERSION_RATE IN NUMBER) RETURN NUMBER IS
391 BEGIN
392 DECLARE
393 GL_RATE NUMBER;
394 BEGIN
395 /*SRW.REFERENCE(CURRENCY2)*/NULL;
396 /*SRW.REFERENCE(ORD_DATE)*/NULL;
397 /*SRW.REFERENCE(CONVERSION_TYPE_CODE)*/NULL;
398 IF P_USE_FUNCTIONAL_CURRENCY = 'Y' THEN
399 IF CURRENCY2 = RP_FUNCTIONAL_CURRENCY THEN
400 RETURN (1);
401 ELSE
402 IF CONVERSION_RATE IS NULL THEN
403 GL_RATE := GET_RATE(P_SOB_ID
404 ,CURRENCY2
405 ,ORD_DATE
406 ,CONVERSION_TYPE_CODE);
407 RETURN (GL_RATE);
408 ELSE
409 RETURN (CONVERSION_RATE);
410 END IF;
411 END IF;
412 ELSE
413 RETURN (1);
414 END IF;
415 EXCEPTION
416 WHEN NO_DATA_FOUND THEN
417 RETURN (-1);
418 WHEN OTHERS THEN
419 RETURN (-1);
420 END;
421 RETURN NULL;
422 END C_GL_CONV_RATEFORMULA;
423
424 FUNCTION C_AMOUNTFORMULA(AMOUNT IN NUMBER
425 ,C_GL_CONV_RATE IN NUMBER
426 ,C_PRECISION IN NUMBER) RETURN NUMBER IS
427 BEGIN
428 DECLARE
429 ORDER_AMOUNT NUMBER(14,2);
430 BEGIN
431 /*SRW.REFERENCE(AMOUNT)*/NULL;
432 /*SRW.REFERENCE(C_GL_CONV_RATE)*/NULL;
433 /*SRW.REFERENCE(C_PRECISION)*/NULL;
434 IF P_USE_FUNCTIONAL_CURRENCY = 'Y' THEN
435 IF C_GL_CONV_RATE <> -1 THEN
436 SELECT
437 C_GL_CONV_RATE * AMOUNT
438 INTO ORDER_AMOUNT
439 FROM
440 DUAL;
441 RETURN (ROUND(ORDER_AMOUNT
442 ,C_PRECISION));
443 ELSE
444 RETURN (0);
445 END IF;
446 ELSE
447 RETURN (ROUND(AMOUNT
448 ,C_PRECISION));
449 END IF;
450 END;
451 RETURN NULL;
452 END C_AMOUNTFORMULA;
453
454 FUNCTION C_CURRENCY_CODEFORMULA(CURRENCY2 IN VARCHAR2) RETURN VARCHAR2 IS
455 BEGIN
456 /*SRW.REFERENCE(CURRENCY2)*/NULL;
457 IF P_USE_FUNCTIONAL_CURRENCY = 'Y' THEN
458 RETURN (RP_FUNCTIONAL_CURRENCY);
459 ELSE
460 RETURN (CURRENCY2);
461 END IF;
462 RETURN NULL;
463 END C_CURRENCY_CODEFORMULA;
464
465 FUNCTION RP_ORDER_CATEGORYFORMULA RETURN VARCHAR2 IS
466 BEGIN
467 DECLARE
468 L_MEANING VARCHAR2(80);
469 BEGIN
470 SELECT
471 MEANING
472 INTO L_MEANING
473 FROM
474 OE_LOOKUPS
475 WHERE LOOKUP_TYPE = 'REPORT_ORDER_CATEGORY'
476 AND LOOKUP_CODE = P_ORDER_CATEGORY;
477 RETURN (L_MEANING);
478 EXCEPTION
479 WHEN NO_DATA_FOUND THEN
480 RETURN (NULL);
481 END;
482 RETURN NULL;
483 END RP_ORDER_CATEGORYFORMULA;
484
485 FUNCTION RP_LINE_CATEGORYFORMULA RETURN VARCHAR2 IS
486 BEGIN
487 DECLARE
488 L_MEANING VARCHAR2(80);
489 BEGIN
490 SELECT
491 MEANING
492 INTO L_MEANING
493 FROM
494 OE_LOOKUPS
495 WHERE LOOKUP_TYPE = 'REPORT_LINE_DISPLAY'
496 AND LOOKUP_CODE = P_LINE_CATEGORY;
497 RETURN (L_MEANING);
498 EXCEPTION
499 WHEN NO_DATA_FOUND THEN
500 RETURN (NULL);
501 END;
502 RETURN NULL;
503 END RP_LINE_CATEGORYFORMULA;
504
505 FUNCTION CF_UNIT4FORMULA(UNIT2 IN VARCHAR2) RETURN CHAR IS
506 BEGIN
507 CP_UNIT4 := UNIT2;
508 RETURN 1;
509 END CF_UNIT4FORMULA;
510
511 FUNCTION CF_UNIT3FORMULA(UNIT1 IN VARCHAR2) RETURN CHAR IS
512 BEGIN
513 CP_UNIT3 := UNIT1;
514 RETURN 1;
515 END CF_UNIT3FORMULA;
516
517 FUNCTION C_PRECISIONFORMULA(P_CURRENCY_CODE IN VARCHAR2) RETURN NUMBER IS
518 BEGIN
519 DECLARE
520 W_PRECISION NUMBER;
521 BEGIN
522 SELECT
523 PRECISION
524 INTO W_PRECISION
525 FROM
526 FND_CURRENCIES
527 -- WHERE CURRENCY_CODE = CURRENCY_CODE
528 WHERE CURRENCY_CODE = P_CURRENCY_CODE;
529 RETURN (W_PRECISION);
530 EXCEPTION
531 WHEN NO_DATA_FOUND THEN
532 W_PRECISION := 2;
533 RETURN (W_PRECISION);
534 END;
535 RETURN NULL;
536 END C_PRECISIONFORMULA;
537
538 FUNCTION RP_DUMMY_ITEM_P RETURN VARCHAR2 IS
539 BEGIN
540 RETURN RP_DUMMY_ITEM;
541 END RP_DUMMY_ITEM_P;
542
543 FUNCTION RP_REPORT_NAME_P RETURN VARCHAR2 IS
544 BEGIN
545 RETURN RP_REPORT_NAME;
546 END RP_REPORT_NAME_P;
547
548 FUNCTION RP_SUB_TITLE_P RETURN VARCHAR2 IS
549 BEGIN
550 RETURN RP_SUB_TITLE;
551 END RP_SUB_TITLE_P;
552
553 FUNCTION RP_COMPANY_NAME_P RETURN VARCHAR2 IS
554 BEGIN
555 RETURN RP_COMPANY_NAME;
556 END RP_COMPANY_NAME_P;
557
558 FUNCTION RP_FUNCTIONAL_CURRENCY_P RETURN VARCHAR2 IS
559 BEGIN
560 RETURN RP_FUNCTIONAL_CURRENCY;
561 END RP_FUNCTIONAL_CURRENCY_P;
562
563 FUNCTION RP_DATA_FOUND_P RETURN VARCHAR2 IS
564 BEGIN
565 RETURN RP_DATA_FOUND;
566 END RP_DATA_FOUND_P;
567
568 FUNCTION RP_ITEM_FLEX_LPROMPT_P RETURN VARCHAR2 IS
569 BEGIN
570 RETURN RP_ITEM_FLEX_LPROMPT;
571 END RP_ITEM_FLEX_LPROMPT_P;
572
573 FUNCTION RP_ITEM_FLEX_ALL_SEG_P RETURN VARCHAR2 IS
574 BEGIN
575 RETURN RP_ITEM_FLEX_ALL_SEG;
576 END RP_ITEM_FLEX_ALL_SEG_P;
577
578 FUNCTION RP_ITEM_FLEX_APROMPT_P RETURN VARCHAR2 IS
579 BEGIN
580 RETURN RP_ITEM_FLEX_APROMPT;
581 END RP_ITEM_FLEX_APROMPT_P;
582
583 FUNCTION RP_CUSTOMER_RANGE_P RETURN VARCHAR2 IS
584 BEGIN
585 RETURN RP_CUSTOMER_RANGE;
586 END RP_CUSTOMER_RANGE_P;
587
588 FUNCTION RP_SALESPERSON_RANGE_P RETURN VARCHAR2 IS
589 BEGIN
590 RETURN RP_SALESPERSON_RANGE;
591 END RP_SALESPERSON_RANGE_P;
592
593 FUNCTION RP_ORDER_RANGE_P RETURN VARCHAR2 IS
594 BEGIN
595 RETURN RP_ORDER_RANGE;
596 END RP_ORDER_RANGE_P;
597
598 FUNCTION RP_ORDER_BY_P RETURN VARCHAR2 IS
599 BEGIN
600 RETURN RP_ORDER_BY;
601 END RP_ORDER_BY_P;
602
603 FUNCTION RP_USE_FUNCTIONAL_CURRENCY_P RETURN VARCHAR2 IS
604 BEGIN
605 RETURN RP_USE_FUNCTIONAL_CURRENCY;
606 END RP_USE_FUNCTIONAL_CURRENCY_P;
607
608 FUNCTION RP_FLEX_OR_DESC_P RETURN VARCHAR2 IS
609 BEGIN
610 RETURN RP_FLEX_OR_DESC;
611 END RP_FLEX_OR_DESC_P;
612
613 FUNCTION RP_ITEM_FLEX_ALL_SEG_WHERE_P RETURN VARCHAR2 IS
614 BEGIN
615 RETURN RP_ITEM_FLEX_ALL_SEG_WHERE;
616 END RP_ITEM_FLEX_ALL_SEG_WHERE_P;
617
618 FUNCTION RP_ORDER_DATE_RANGE_P RETURN VARCHAR2 IS
619 BEGIN
620 RETURN RP_ORDER_DATE_RANGE;
621 END RP_ORDER_DATE_RANGE_P;
622
623 FUNCTION CP_UNIT3_P RETURN VARCHAR2 IS
624 BEGIN
625 RETURN CP_UNIT3;
626 END CP_UNIT3_P;
627
628 FUNCTION CP_UNIT4_P RETURN VARCHAR2 IS
629 BEGIN
630 RETURN CP_UNIT4;
631 END CP_UNIT4_P;
632
633 FUNCTION IS_FIXED_RATE(X_FROM_CURRENCY IN VARCHAR2
634 ,X_TO_CURRENCY IN VARCHAR2
635 ,X_EFFECTIVE_DATE IN DATE) RETURN VARCHAR2 IS
636 X0 VARCHAR2(2000);
637 BEGIN
638 /* STPROC.INIT('begin :X0 := GL_CURRENCY_API.IS_FIXED_RATE(:X_FROM_CURRENCY, :X_TO_CURRENCY, :X_EFFECTIVE_DATE); end;');
639 STPROC.BIND_O(X0);
640 STPROC.BIND_I(X_FROM_CURRENCY);
641 STPROC.BIND_I(X_TO_CURRENCY);
642 STPROC.BIND_I(X_EFFECTIVE_DATE);
643 STPROC.EXECUTE;
644 STPROC.RETRIEVE(1
645 ,X0);*/
646 null;
647 RETURN X0;
648 END IS_FIXED_RATE;
649
650 PROCEDURE GET_RELATION(X_FROM_CURRENCY IN VARCHAR2
651 ,X_TO_CURRENCY IN VARCHAR2
652 ,X_EFFECTIVE_DATE IN DATE
653 ,X_FIXED_RATE IN OUT NOCOPY BOOLEAN
654 ,X_RELATIONSHIP IN OUT NOCOPY VARCHAR2) IS
655 BEGIN
656 /* STPROC.INIT('declare X_FIXED_RATE BOOLEAN;
657 begin
658 X_FIXED_RATE := sys.diutil.int_to_bool(:X_FIXED_RATE);
659 GL_CURRENCY_API.GET_RELATION(:X_FROM_CURRENCY, :X_TO_CURRENCY, :X_EFFECTIVE_DATE, X_FIXED_RATE, :X_RELATIONSHIP); :X_FIXED_RATE := sys.diutil.bool_to_int(X_FIXED_RATE); end;');
660 STPROC.BIND_IO(X_FIXED_RATE);
661 STPROC.BIND_I(X_FROM_CURRENCY);
662 STPROC.BIND_I(X_TO_CURRENCY);
663 STPROC.BIND_I(X_EFFECTIVE_DATE);
664 STPROC.BIND_IO(X_RELATIONSHIP);
665 STPROC.EXECUTE;
666 STPROC.RETRIEVE(1
667 ,X_FIXED_RATE);
668 STPROC.RETRIEVE(5
669 ,X_RELATIONSHIP);*/
670 null;
671 END GET_RELATION;
672
673 FUNCTION GET_EURO_CODE RETURN VARCHAR2 IS
674 X0 VARCHAR2(2000);
675 BEGIN
676 /* STPROC.INIT('begin :X0 := GL_CURRENCY_API.GET_EURO_CODE; end;');
677 STPROC.BIND_O(X0);
678 STPROC.EXECUTE;
679 STPROC.RETRIEVE(1
680 ,X0);*/
681 null;
682 RETURN X0;
683 END GET_EURO_CODE;
684
685 FUNCTION GET_RATE(X_FROM_CURRENCY IN VARCHAR2
686 ,X_TO_CURRENCY IN VARCHAR2
687 ,X_CONVERSION_DATE IN DATE
688 ,X_CONVERSION_TYPE IN VARCHAR2) RETURN NUMBER IS
689 X0 NUMBER;
690 BEGIN
691 /* STPROC.INIT('begin :X0 := GL_CURRENCY_API.GET_RATE(:X_FROM_CURRENCY, :X_TO_CURRENCY, :X_CONVERSION_DATE, :X_CONVERSION_TYPE); end;');
692 STPROC.BIND_O(X0);
693 STPROC.BIND_I(X_FROM_CURRENCY);
694 STPROC.BIND_I(X_TO_CURRENCY);
695 STPROC.BIND_I(X_CONVERSION_DATE);
696 STPROC.BIND_I(X_CONVERSION_TYPE);
697 STPROC.EXECUTE;
698 STPROC.RETRIEVE(1
699 ,X0);
700 */ null;
701 RETURN X0;
702 END GET_RATE;
703
704 FUNCTION GET_RATE(X_SET_OF_BOOKS_ID IN NUMBER
705 ,X_FROM_CURRENCY IN VARCHAR2
706 ,X_CONVERSION_DATE IN DATE
707 ,X_CONVERSION_TYPE IN VARCHAR2) RETURN NUMBER IS
708 X0 NUMBER;
709 BEGIN
710 /* STPROC.INIT('begin :X0 := GL_CURRENCY_API.GET_RATE(:X_SET_OF_BOOKS_ID, :X_FROM_CURRENCY, :X_CONVERSION_DATE, :X_CONVERSION_TYPE); end;');
711 STPROC.BIND_O(X0);
712 STPROC.BIND_I(X_SET_OF_BOOKS_ID);
713 STPROC.BIND_I(X_FROM_CURRENCY);
714 STPROC.BIND_I(X_CONVERSION_DATE);
715 STPROC.BIND_I(X_CONVERSION_TYPE);
716 STPROC.EXECUTE;
717 STPROC.RETRIEVE(1
718 ,X0);
719 */ null;
720 RETURN X0;
721 END GET_RATE;
722
723 FUNCTION CONVERT_AMOUNT(X_FROM_CURRENCY IN VARCHAR2
724 ,X_TO_CURRENCY IN VARCHAR2
725 ,X_CONVERSION_DATE IN DATE
726 ,X_CONVERSION_TYPE IN VARCHAR2
727 ,X_AMOUNT IN NUMBER) RETURN NUMBER IS
728 X0 NUMBER;
729 BEGIN
730 /* STPROC.INIT('begin :X0 := GL_CURRENCY_API.CONVERT_AMOUNT(:X_FROM_CURRENCY, :X_TO_CURRENCY, :X_CONVERSION_DATE, :X_CONVERSION_TYPE, :X_AMOUNT); end;');
731 STPROC.BIND_O(X0);
732 STPROC.BIND_I(X_FROM_CURRENCY);
733 STPROC.BIND_I(X_TO_CURRENCY);
734 STPROC.BIND_I(X_CONVERSION_DATE);
735 STPROC.BIND_I(X_CONVERSION_TYPE);
736 STPROC.BIND_I(X_AMOUNT);
737 STPROC.EXECUTE;
738 STPROC.RETRIEVE(1
739 ,X0);
740 */ null;
741 RETURN X0;
742 END CONVERT_AMOUNT;
743
744 FUNCTION CONVERT_AMOUNT(X_SET_OF_BOOKS_ID IN NUMBER
745 ,X_FROM_CURRENCY IN VARCHAR2
746 ,X_CONVERSION_DATE IN DATE
747 ,X_CONVERSION_TYPE IN VARCHAR2
748 ,X_AMOUNT IN NUMBER) RETURN NUMBER IS
749 X0 NUMBER;
750 BEGIN
751 /* 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;');
752 STPROC.BIND_O(X0);
753 STPROC.BIND_I(X_SET_OF_BOOKS_ID);
754 STPROC.BIND_I(X_FROM_CURRENCY);
755 STPROC.BIND_I(X_CONVERSION_DATE);
756 STPROC.BIND_I(X_CONVERSION_TYPE);
757 STPROC.BIND_I(X_AMOUNT);
758 STPROC.EXECUTE;
759 STPROC.RETRIEVE(1
760 ,X0);
761 */ null;
762 RETURN X0;
763 END CONVERT_AMOUNT;
764
765 FUNCTION GET_DERIVE_TYPE(SOB_ID IN NUMBER
766 ,PERIOD IN VARCHAR2
767 ,CURR_CODE IN VARCHAR2) RETURN VARCHAR2 IS
768 X0 VARCHAR2(2000);
769 BEGIN
770 /* STPROC.INIT('begin :X0 := GL_CURRENCY_API.GET_DERIVE_TYPE(:SOB_ID, :PERIOD, :CURR_CODE); end;');
771 STPROC.BIND_O(X0);
772 STPROC.BIND_I(SOB_ID);
773 STPROC.BIND_I(PERIOD);
774 STPROC.BIND_I(CURR_CODE);
775 STPROC.EXECUTE;
776 STPROC.RETRIEVE(1
777 ,X0);
778 */ null;
779 RETURN X0;
780 END GET_DERIVE_TYPE;
781
782 END ONT_OEXOEOCS_XMLP_PKG;
783
784
[Home] [Help]