[Home] [Help]
PACKAGE BODY: APPS.ONT_OEXOEIOS_XMLP_PKG
Source
1 PACKAGE BODY ONT_OEXOEIOS_XMLP_PKG AS
2 /* $Header: OEXOEIOSB.pls 120.1.12020000.2 2012/07/03 09:59:31 amallik 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 /*SRW.MESSAGE(1000
12 ,'Failed in BEFORE REPORT trigger')*/NULL;
13 RETURN (FALSE);
14 END;
15 BEGIN
16 P_ORG_ID := MO_GLOBAL.GET_CURRENT_ORG_ID;
17 END;
18 DECLARE
19 L_COMPANY_NAME VARCHAR2(100);
20 L_FUNCTIONAL_CURRENCY VARCHAR2(15);
21 BEGIN
22 SELECT
23 SOB.NAME,
24 SOB.CURRENCY_CODE
25 INTO L_COMPANY_NAME,L_FUNCTIONAL_CURRENCY
26 FROM
27 GL_SETS_OF_BOOKS SOB,
28 FND_CURRENCIES CUR
29 WHERE SOB.SET_OF_BOOKS_ID = P_SOB_ID
30 AND SOB.CURRENCY_CODE = CUR.CURRENCY_CODE;
31 RP_COMPANY_NAME := L_COMPANY_NAME;
32 RP_FUNCTIONAL_CURRENCY := L_FUNCTIONAL_CURRENCY;
33 EXCEPTION
34 WHEN NO_DATA_FOUND THEN
35 NULL;
36 END;
37 DECLARE
38 L_REPORT_NAME VARCHAR2(240);
39 BEGIN
40 SELECT
41 CP.USER_CONCURRENT_PROGRAM_NAME
42 INTO L_REPORT_NAME
43 FROM
44 FND_CONCURRENT_PROGRAMS_VL CP,
45 FND_CONCURRENT_REQUESTS CR
46 WHERE CR.REQUEST_ID = P_CONC_REQUEST_ID
47 AND CP.APPLICATION_ID = CR.PROGRAM_APPLICATION_ID
48 AND CP.CONCURRENT_PROGRAM_ID = CR.CONCURRENT_PROGRAM_ID;
49 RP_REPORT_NAME := L_REPORT_NAME;
50 IF (RP_REPORT_NAME = 'Order/Invoice Summary Report (XML)') THEN
51 RP_REPORT_NAME := 'Order/Invoice Summary Report';
52 END IF;
53 EXCEPTION
54 WHEN NO_DATA_FOUND THEN
55 RP_REPORT_NAME := 'Order/Invoice Summary Report';
56 END;
57 DECLARE
58 L_ORDER_TYPE_LOW VARCHAR2(50);
59 L_ORDER_TYPE_HIGH VARCHAR2(50);
60 L_CUSTOMER_NAME_LOW VARCHAR2(50);
61 L_CUSTOMER_NAME_HIGH VARCHAR2(50);
62 L_SALESREP_LOW VARCHAR2(50);
63 L_SALESREP_HIGH VARCHAR2(50);
64 L_ORDER_NUMBER_LOW VARCHAR2(50);
65 L_ORDER_NUMBER_HIGH VARCHAR2(50);
66 BEGIN
67 IF (P_ORDER_TYPE_LOW IS NULL) AND (P_ORDER_TYPE_HIGH IS NULL) THEN
68 NULL;
69 ELSE
70 IF P_ORDER_TYPE_LOW IS NULL THEN
71 L_ORDER_TYPE_LOW := ' ';
72 ELSE
73 L_ORDER_TYPE_LOW := SUBSTR(L_ORDER_TYPE_LOW
74 ,1
75 ,18);
76 END IF;
77 IF P_ORDER_TYPE_HIGH IS NULL THEN
78 L_ORDER_TYPE_HIGH := ' ';
79 ELSE
80 L_ORDER_TYPE_HIGH := SUBSTR(L_ORDER_TYPE_HIGH
81 ,1
82 ,18);
83 END IF;
84 RP_ORDER_TYPE_RANGE := 'From ' || L_ORDER_TYPE_LOW || ' To ' || L_ORDER_TYPE_HIGH;
85 END IF;
86 IF (P_CUSTOMER_NAME_LOW IS NULL) AND (P_CUSTOMER_NAME_HIGH IS NULL) THEN
87 NULL;
88 ELSE
89 IF P_CUSTOMER_NAME_LOW IS NULL THEN
90 L_CUSTOMER_NAME_LOW := ' ';
91 ELSE
92 L_CUSTOMER_NAME_LOW := P_CUSTOMER_NAME_LOW;
93 END IF;
94 IF P_CUSTOMER_NAME_HIGH IS NULL THEN
95 L_CUSTOMER_NAME_HIGH := ' ';
96 ELSE
97 L_CUSTOMER_NAME_HIGH := P_CUSTOMER_NAME_HIGH;
98 END IF;
99 RP_CUSTOMER_NAME_RANGE := 'From ' || L_CUSTOMER_NAME_LOW || ' To ' || L_CUSTOMER_NAME_HIGH;
100 END IF;
101 IF (P_SALESREP_LOW IS NULL) AND (P_SALESREP_HIGH IS NULL) THEN
102 NULL;
103 ELSE
104 IF P_SALESREP_LOW IS NULL THEN
105 L_SALESREP_LOW := ' ';
106 ELSE
107 L_SALESREP_LOW := SUBSTR(P_SALESREP_LOW
108 ,1
109 ,18);
110 END IF;
111 IF P_SALESREP_HIGH IS NULL THEN
112 L_SALESREP_HIGH := ' ';
113 ELSE
114 L_SALESREP_HIGH := SUBSTR(P_SALESREP_HIGH
115 ,1
116 ,18);
117 END IF;
118 RP_SALESREP_RANGE := 'From ' || L_SALESREP_LOW || ' To ' || L_SALESREP_HIGH;
119 END IF;
120 IF (P_ORDER_NUM_LOW IS NULL) AND (P_ORDER_NUM_HIGH IS NULL) THEN
121 NULL;
122 ELSE
123 IF P_ORDER_NUM_LOW IS NULL THEN
124 L_ORDER_NUMBER_LOW := ' ';
125 ELSE
126 L_ORDER_NUMBER_LOW := SUBSTR(P_ORDER_NUM_LOW
127 ,1
128 ,18);
129 END IF;
130 IF P_ORDER_NUM_HIGH IS NULL THEN
131 L_ORDER_NUMBER_HIGH := ' ';
132 ELSE
133 L_ORDER_NUMBER_HIGH := SUBSTR((P_ORDER_NUM_HIGH)
134 ,1
135 ,18);
136 END IF;
137 RP_ORDER_NUMBER_RANGE := 'From ' || L_ORDER_NUMBER_LOW || ' To ' || L_ORDER_NUMBER_HIGH;
138 END IF;
139 END;
140 DECLARE
141 L_MEANING VARCHAR2(80);
142 BEGIN
143 SELECT
144 MEANING
145 INTO L_MEANING
146 FROM
147 FND_LOOKUPS
148 WHERE LOOKUP_TYPE = 'YES_NO'
149 AND LOOKUP_CODE = SUBSTR(UPPER(P_OPEN_ORDERS_ONLY)
150 ,1
151 ,1);
152 RP_OPEN_ORDERS_ONLY := L_MEANING;
153 EXCEPTION
154 WHEN NO_DATA_FOUND THEN
155 RP_OPEN_ORDERS_ONLY := 'Yes';
156 END;
157 END;
158 RETURN (TRUE);
159 END BEFOREREPORT;
160
161 FUNCTION AFTERREPORT RETURN BOOLEAN IS
162 BEGIN
163 BEGIN
164 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
165 EXCEPTION
166 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
167 /*SRW.MESSAGE(1
168 ,'Failed in AFTER REPORT TRIGGER')*/NULL;
169 RETURN (FALSE);
170 END;
171 RETURN (TRUE);
172 END AFTERREPORT;
173
174 FUNCTION AFTERPFORM RETURN BOOLEAN IS
175 BEGIN
176 BEGIN
177 IF (P_ORDER_TYPE_LOW IS NOT NULL) AND (P_ORDER_TYPE_HIGH IS NOT NULL) THEN
178 LP_ORDER_TYPE := 'and ( ot.transaction_type_id between :p_order_type_low and :p_order_type_high ) ';
179 SELECT
180 OEOT.NAME
181 INTO L_ORDER_TYPE_LOW
182 FROM
183 OE_TRANSACTION_TYPES_TL OEOT
184 WHERE OEOT.TRANSACTION_TYPE_ID = P_ORDER_TYPE_LOW
185 AND OEOT.LANGUAGE = USERENV('LANG');
186 SELECT
187 OEOT.NAME
188 INTO L_ORDER_TYPE_HIGH
189 FROM
190 OE_TRANSACTION_TYPES_TL OEOT
191 WHERE OEOT.TRANSACTION_TYPE_ID = P_ORDER_TYPE_HIGH
192 AND OEOT.LANGUAGE = USERENV('LANG');
193 ELSIF (P_ORDER_TYPE_LOW IS NOT NULL) THEN
194 LP_ORDER_TYPE := 'and ot.transaction_type_id >= :p_order_type_low ';
195 SELECT
196 OEOT.NAME
197 INTO L_ORDER_TYPE_LOW
198 FROM
199 OE_TRANSACTION_TYPES_TL OEOT
200 WHERE OEOT.TRANSACTION_TYPE_ID = P_ORDER_TYPE_LOW
201 AND OEOT.LANGUAGE = USERENV('LANG');
202 ELSIF (P_ORDER_TYPE_HIGH IS NOT NULL) THEN
203 LP_ORDER_TYPE := 'and ot.transaction_type_id <= :p_order_type_high ';
204 SELECT
205 OEOT.NAME
206 INTO L_ORDER_TYPE_HIGH
207 FROM
208 OE_TRANSACTION_TYPES_TL OEOT
209 WHERE OEOT.TRANSACTION_TYPE_ID = P_ORDER_TYPE_HIGH
210 AND OEOT.LANGUAGE = USERENV('LANG');
211 END IF;
212
213 IF (LP_ORDER_TYPE IS NULL) THEN
214 LP_ORDER_TYPE := ' ';
215 END IF;
216
217 IF (P_CUSTOMER_NAME_LOW IS NOT NULL) AND (P_CUSTOMER_NAME_HIGH IS NOT NULL) THEN
218 LP_CUSTOMER_NAME := 'and ( party.party_name between :p_customer_name_low and :p_customer_name_high ) ';
219 ELSIF (P_CUSTOMER_NAME_LOW IS NOT NULL) THEN
220 LP_CUSTOMER_NAME := 'and party.party_name >= :p_customer_name_low ';
221 ELSIF (P_CUSTOMER_NAME_HIGH IS NOT NULL) THEN
222 LP_CUSTOMER_NAME := 'and party.party_name <= :p_customer_name_high ';
223 END IF;
224 IF (LP_CUSTOMER_NAME IS NULL) THEN
225 LP_CUSTOMER_NAME := ' ';
226 END IF;
227
228 IF (P_ORDER_NUM_LOW IS NOT NULL) AND (P_ORDER_NUM_HIGH IS NOT NULL) THEN
229 LP_ORDER_NUM := 'and ( h.order_number between to_number(:p_order_num_low) and to_number(:p_order_num_high) ) ';
230 ELSIF (P_ORDER_NUM_LOW IS NOT NULL) THEN
231 LP_ORDER_NUM := 'and h.order_number >= to_number(:p_order_num_low) ';
232 ELSIF (P_ORDER_NUM_HIGH IS NOT NULL) THEN
233 LP_ORDER_NUM := 'and h.order_number <= to_number(:p_order_num_high) ';
234 END IF;
235
236 IF (LP_ORDER_NUM IS NULL) THEN
237 LP_ORDER_NUM := ' ';
238 END IF;
239
240 IF (P_SALESREP_LOW IS NOT NULL) AND (P_SALESREP_HIGH IS NOT NULL) THEN
241 LP_SALESREP := 'and ((nvl(sr.name,''zzzzzz'') between :p_salesrep_low and :p_salesrep_high ) and l.salesrep_id = sr.salesrep_id(+)
242 and nvl(l.org_id,0) = nvl(sr.org_id(+),0)) ';
243 ELSIF (P_SALESREP_LOW IS NOT NULL) THEN
244 LP_SALESREP := 'and (nvl(sr.name,''zzzzzz'') >= :p_salesrep_low and l.salesrep_id = sr.salesrep_id(+) and nvl(l.org_id,0) = nvl(sr.org_id(+),0)) ';
245 ELSIF (P_SALESREP_HIGH IS NOT NULL) THEN
246 LP_SALESREP := 'and (nvl(sr.name,''zzzzzz'') <= :p_salesrep_high and l.salesrep_id = sr.salesrep_id(+) and nvl(l.org_id,0) = nvl(sr.org_id(+),0)) ';
247 ELSE
248 LP_SALESREP := 'and (h.salesrep_id = sr.salesrep_id(+) and nvl(h.org_id,0) = nvl(sr.org_id(+),0)) ';
249 END IF;
250 IF (P_COUNTRY IS NOT NULL) THEN
251 LP_COUNTRY := 'and terr.territory_short_name = :p_country ';
252 END IF;
253 IF (LP_COUNTRY IS NULL) THEN
254 LP_COUNTRY := ' ';
255 END IF;
256
257 IF P_OPEN_ORDERS_ONLY = 'Y' THEN
258 LP_OPEN_ORDERS_ONLY := 'and nvl(h.open_flag,''N'') = ''Y'' ';
259 /*starting the fix for bug 14086052 */
260 ELSE
261 LP_OPEN_ORDERS_ONLY := ' ';
262 /*Ending the fix for bug 14086052 */
263 END IF;
264 END;
265 RETURN (TRUE);
266 END AFTERPFORM;
267
268 FUNCTION C_DATA_NOT_FOUNDFORMULA(CURRENCY1 IN VARCHAR2) RETURN NUMBER IS
269 BEGIN
270 RP_DATA_FOUND := CURRENCY1;
271 RETURN (0);
272 END C_DATA_NOT_FOUNDFORMULA;
273
274 FUNCTION RP_CURR_LABELFORMULA RETURN VARCHAR2 IS
275 BEGIN
276 IF SUBSTR(UPPER(P_ORDER_BY)
277 ,1
278 ,1) = 'O' THEN
279 RETURN (' Currency');
280 ELSIF SUBSTR(UPPER(P_ORDER_BY)
281 ,1
282 ,1) = 'S' THEN
283 RETURN (' Currency');
284 ELSE
285 RETURN ('Currency');
286 END IF;
287 RETURN NULL;
288 END RP_CURR_LABELFORMULA;
289
290 FUNCTION C_ORDER_COUNTFORMULA RETURN NUMBER IS
291 BEGIN
292 RETURN (1);
293 END C_ORDER_COUNTFORMULA;
294
295 FUNCTION C_LINE_COUNTFORMULA(HEADER_ID1 IN NUMBER) RETURN NUMBER IS
296 BEGIN
297 DECLARE
298 L_COUNT NUMBER(20);
299 BEGIN
300 /*SRW.REFERENCE(HEADER_ID1)*/NULL;
301 L_COUNT := 0;
302 SELECT
303 COUNT(1)
304 INTO L_COUNT
305 FROM
306 OE_ORDER_LINES L,
307 RA_CUSTOMER_TRX_ALL TRX,
308 RA_CUSTOMER_TRX_LINES_ALL TRXL
309 WHERE L.HEADER_ID = HEADER_ID1
310 AND TO_CHAR(L.LINE_ID) = TRXL.INTERFACE_LINE_ATTRIBUTE6
311 AND TRXL.INTERFACE_LINE_CONTEXT = P_INVOICE_LINE_CONTEXT
312 AND TRXL.CUSTOMER_TRX_ID = TRX.CUSTOMER_TRX_ID;
313 RETURN (L_COUNT);
314 END;
315 RETURN NULL;
316 END C_LINE_COUNTFORMULA;
317
318 FUNCTION C_COMPUTE_AMOUNTSFORMULA(TRX_ID IN NUMBER
319 ,CURRENCY1 IN VARCHAR2
320 ,INV_ORDER_AMT IN NUMBER
321 ,CONVERSION_TYPE_CODE IN VARCHAR2
322 ,ORDER_DATE IN DATE
323 ,C_PRECISION IN NUMBER
324 ,CONVERSION_RATE IN NUMBER) RETURN NUMBER IS
325 BEGIN
326 BEGIN
327 DECLARE
328 L_AMOUNT NUMBER(12,2);
329 L_CREDIT_AMOUNT NUMBER(12,2);
330 L_BALANCE_DUE NUMBER(12,2);
331 L_TERMS_SEQUANCE_NUMBER NUMBER(15);
332 BEGIN
333 /*SRW.REFERENCE(TRX_ID)*/NULL;
334 C_AMOUNT := 0;
335 C_CREDIT_AMOUNT := 0;
336 C_BALANCE_DUE := 0;
337 SELECT
338 MAX(TERMS_SEQUENCE_NUMBER)
339 INTO L_TERMS_SEQUANCE_NUMBER
340 FROM
341 AR_PAYMENT_SCHEDULES
342 WHERE CUSTOMER_TRX_ID = TRX_ID;
343 IF L_TERMS_SEQUANCE_NUMBER IS NULL THEN
344 SELECT
345 SUM(PS.AMOUNT_DUE_ORIGINAL),
346 SUM(PS.AMOUNT_DUE_ORIGINAL - PS.AMOUNT_DUE_REMAINING),
347 SUM(PS.AMOUNT_DUE_REMAINING)
348 INTO L_AMOUNT,L_CREDIT_AMOUNT,L_BALANCE_DUE
349 FROM
350 AR_PAYMENT_SCHEDULES PS
351 WHERE PS.CUSTOMER_TRX_ID = TRX_ID;
352 ELSE
353 SELECT
354 PS.AMOUNT_DUE_ORIGINAL,
355 ( PS.AMOUNT_DUE_ORIGINAL - PS.AMOUNT_DUE_REMAINING ),
356 PS.AMOUNT_DUE_REMAINING
357 INTO L_AMOUNT,L_CREDIT_AMOUNT,L_BALANCE_DUE
358 FROM
359 AR_PAYMENT_SCHEDULES PS
360 WHERE PS.CUSTOMER_TRX_ID = TRX_ID
361 AND PS.TERMS_SEQUENCE_NUMBER = L_TERMS_SEQUANCE_NUMBER;
362 END IF;
363 C_AMOUNT := L_AMOUNT;
364 C_CREDIT_AMOUNT := L_CREDIT_AMOUNT;
365 C_BALANCE_DUE := L_BALANCE_DUE;
366 END;
367 DECLARE
368 L_CONVERSION_RATE NUMBER(15,3);
369 BEGIN
370 /*SRW.REFERENCE(CURRENCY1)*/NULL;
371 /*SRW.REFERENCE(RP_FUNCTIONAL_CURRENCY)*/NULL;
372 /*SRW.REFERENCE(C_INV_ORDER_AMT)*/NULL;
373 /*SRW.REFERENCE(C_AMOUNT)*/NULL;
374 /*SRW.REFERENCE(C_CREDIT_AMOUNT)*/NULL;
375 /*SRW.REFERENCE(C_BALANCE_DUE)*/NULL;
376 /*SRW.REFERENCE(INV_ORDER_AMT)*/NULL;
377 /*SRW.REFERENCE(CONVERSION_TYPE_CODE)*/NULL;
378 /*SRW.REFERENCE(ORDER_DATE)*/NULL;
379 L_CONVERSION_RATE := 0;
380 IF P_USE_FUNCTIONAL_CURRENCY = 'N' THEN
381 C_INV_ORDER_AMT := ROUND(NVL(INV_ORDER_AMT
382 ,0)
383 ,C_PRECISION);
384 RETURN (0);
385 ELSIF P_USE_FUNCTIONAL_CURRENCY = 'Y' THEN
386 IF CURRENCY1 = RP_FUNCTIONAL_CURRENCY THEN
387 L_CONVERSION_RATE := 1;
388 ELSE
389 IF CONVERSION_RATE IS NULL THEN
390 L_CONVERSION_RATE := GET_RATE(P_SOB_ID
391 ,CURRENCY1
392 ,ORDER_DATE
393 ,CONVERSION_TYPE_CODE);
394 ELSE
395 L_CONVERSION_RATE := CONVERSION_RATE;
396 END IF;
397 END IF;
398 C_INV_ORDER_AMT := ROUND(NVL(L_CONVERSION_RATE
399 ,0) * NVL(INV_ORDER_AMT
400 ,0)
401 ,C_PRECISION);
402 C_AMOUNT := ROUND(NVL(L_CONVERSION_RATE
403 ,0) * NVL(C_AMOUNT
404 ,0)
405 ,C_PRECISION);
406 C_CREDIT_AMOUNT := ROUND(NVL(L_CONVERSION_RATE
407 ,0) * NVL(C_CREDIT_AMOUNT
408 ,0)
409 ,C_PRECISION);
410 C_BALANCE_DUE := ROUND(NVL(L_CONVERSION_RATE
411 ,0) * NVL(C_BALANCE_DUE
412 ,0)
413 ,C_PRECISION);
414 RETURN (0);
415 END IF;
416 EXCEPTION
417 WHEN NO_DATA_FOUND THEN
418 C_INV_ORDER_AMT := 0;
419 C_AMOUNT := 0;
420 C_CREDIT_AMOUNT := 0;
421 C_BALANCE_DUE := 0;
422 RETURN (0);
423 WHEN OTHERS THEN
424 C_INV_ORDER_AMT := 0;
425 C_AMOUNT := 0;
426 C_CREDIT_AMOUNT := 0;
427 C_BALANCE_DUE := 0;
428 RETURN (0);
429 END;
430 END;
431 RETURN NULL;
432 END C_COMPUTE_AMOUNTSFORMULA;
433
434 FUNCTION RP_USE_FUNCTIONAL_CURRENCYFORM RETURN VARCHAR2 IS
435 BEGIN
436 DECLARE
437 L_TEMP VARCHAR2(100);
438 BEGIN
439 SELECT
440 MEANING
441 INTO L_TEMP
442 FROM
443 FND_LOOKUPS
444 WHERE LOOKUP_CODE = P_USE_FUNCTIONAL_CURRENCY
445 AND LOOKUP_TYPE = 'YES_NO';
446 RETURN (L_TEMP);
447 EXCEPTION
448 WHEN NO_DATA_FOUND THEN
449 RETURN ('No');
450 END;
451 RETURN NULL;
452 END RP_USE_FUNCTIONAL_CURRENCYFORM;
453
454 FUNCTION C_ORDER_AMOUNTFORMULA(CURRENCY1 IN VARCHAR2
455 ,ORDER_AMOUNT IN NUMBER
456 ,CONVERSION_TYPE_CODE IN VARCHAR2
457 ,ORDER_DATE IN DATE
458 ,C_PRECISION IN NUMBER
459 ,CONVERSION_RATE IN NUMBER) RETURN NUMBER IS
460 BEGIN
461 DECLARE
462 L_CONVERSION_RATE NUMBER(15,3);
463 BEGIN
464 /*SRW.REFERENCE(CURRENCY1)*/NULL;
465 /*SRW.REFERENCE(RP_FUNCTIONAL_CURRENCY)*/NULL;
466 /*SRW.REFERENCE(ORDER_AMOUNT)*/NULL;
467 /*SRW.REFERENCE(CONVERSION_TYPE_CODE)*/NULL;
468 /*SRW.REFERENCE(ORDER_DATE)*/NULL;
469 L_CONVERSION_RATE := 0;
470 IF P_USE_FUNCTIONAL_CURRENCY = 'N' THEN
471 RETURN (ROUND(NVL(ORDER_AMOUNT
472 ,0)
473 ,C_PRECISION));
474 ELSIF P_USE_FUNCTIONAL_CURRENCY = 'Y' THEN
475 IF CURRENCY1 = RP_FUNCTIONAL_CURRENCY THEN
476 L_CONVERSION_RATE := 1;
477 ELSE
478 IF CONVERSION_RATE IS NULL THEN
479 L_CONVERSION_RATE := GET_RATE(P_SOB_ID
480 ,CURRENCY1
481 ,ORDER_DATE
482 ,CONVERSION_TYPE_CODE);
483 ELSE
484 L_CONVERSION_RATE := CONVERSION_RATE;
485 END IF;
486 END IF;
487 RETURN (ROUND(NVL(L_CONVERSION_RATE
488 ,0) * NVL(ORDER_AMOUNT
489 ,0)
490 ,C_PRECISION));
491 END IF;
492 EXCEPTION
493 WHEN NO_DATA_FOUND THEN
494 RETURN (0);
495 WHEN OTHERS THEN
496 RETURN (0);
497 END;
498 RETURN NULL;
499 END C_ORDER_AMOUNTFORMULA;
500
501 FUNCTION BEFOREPFORM RETURN BOOLEAN IS
502 BEGIN
503 RETURN (TRUE);
504 END BEFOREPFORM;
505
506 FUNCTION C_PRECISIONFORMULA(CURRENCY1 IN VARCHAR2) RETURN NUMBER IS
507 BEGIN
508 DECLARE
509 W_PRECISION NUMBER;
510 BEGIN
511 SELECT
512 PRECISION
513 INTO W_PRECISION
514 FROM
515 FND_CURRENCIES
516 WHERE CURRENCY_CODE = CURRENCY1;
517 RETURN (W_PRECISION);
518 EXCEPTION
519 WHEN NO_DATA_FOUND THEN
520 W_PRECISION := 2;
521 RETURN (W_PRECISION);
522 END;
523 RETURN NULL;
524 END C_PRECISIONFORMULA;
525
526 FUNCTION C_INV_ORDER_AMT_P RETURN NUMBER IS
527 BEGIN
528 RETURN C_INV_ORDER_AMT;
529 END C_INV_ORDER_AMT_P;
530
531 FUNCTION C_BALANCE_DUE_P RETURN NUMBER IS
532 BEGIN
533 RETURN C_BALANCE_DUE;
534 END C_BALANCE_DUE_P;
535
536 FUNCTION C_CREDIT_AMOUNT_P RETURN NUMBER IS
537 BEGIN
538 RETURN C_CREDIT_AMOUNT;
539 END C_CREDIT_AMOUNT_P;
540
541 FUNCTION C_AMOUNT_P RETURN NUMBER IS
542 BEGIN
543 RETURN C_AMOUNT;
544 END C_AMOUNT_P;
545
546 FUNCTION RP_REPORT_NAME_P RETURN VARCHAR2 IS
547 BEGIN
548 RETURN RP_REPORT_NAME;
549 END RP_REPORT_NAME_P;
550
551 FUNCTION RP_SUB_TITLE_P RETURN VARCHAR2 IS
552 BEGIN
553 RETURN RP_SUB_TITLE;
554 END RP_SUB_TITLE_P;
555
556 FUNCTION RP_COMPANY_NAME_P RETURN VARCHAR2 IS
557 BEGIN
558 RETURN RP_COMPANY_NAME;
559 END RP_COMPANY_NAME_P;
560
561 FUNCTION RP_FUNCTIONAL_CURRENCY_P RETURN VARCHAR2 IS
562 BEGIN
563 RETURN RP_FUNCTIONAL_CURRENCY;
564 END RP_FUNCTIONAL_CURRENCY_P;
565
566 FUNCTION RP_DATA_FOUND_P RETURN VARCHAR2 IS
567 BEGIN
568 RETURN RP_DATA_FOUND;
569 END RP_DATA_FOUND_P;
570
571 FUNCTION RP_ORDER_NUMBER_RANGE_P RETURN VARCHAR2 IS
572 BEGIN
573 RETURN RP_ORDER_NUMBER_RANGE;
574 END RP_ORDER_NUMBER_RANGE_P;
575
576 FUNCTION RP_SALESREP_RANGE_P RETURN VARCHAR2 IS
577 BEGIN
578 RETURN RP_SALESREP_RANGE;
579 END RP_SALESREP_RANGE_P;
580
581 FUNCTION RP_CUSTOMER_NAME_RANGE_P RETURN VARCHAR2 IS
582 BEGIN
583 RETURN RP_CUSTOMER_NAME_RANGE;
584 END RP_CUSTOMER_NAME_RANGE_P;
585
586 FUNCTION RP_ORDER_TYPE_RANGE_P RETURN VARCHAR2 IS
587 BEGIN
588 RETURN RP_ORDER_TYPE_RANGE;
589 END RP_ORDER_TYPE_RANGE_P;
590
591 FUNCTION RP_OPEN_ORDERS_ONLY_P RETURN VARCHAR2 IS
592 BEGIN
593 RETURN RP_OPEN_ORDERS_ONLY;
594 END RP_OPEN_ORDERS_ONLY_P;
595
596 FUNCTION IS_FIXED_RATE(X_FROM_CURRENCY IN VARCHAR2
597 ,X_TO_CURRENCY IN VARCHAR2
598 ,X_EFFECTIVE_DATE IN DATE) RETURN VARCHAR2 IS
599 X0 VARCHAR2(2000);
600 BEGIN
601 /* STPROC.INIT('begin :X0 := GL_CURRENCY_API.IS_FIXED_RATE(:X_FROM_CURRENCY, :X_TO_CURRENCY, :X_EFFECTIVE_DATE); end;');
602 STPROC.BIND_O(X0);
603 STPROC.BIND_I(X_FROM_CURRENCY);
604 STPROC.BIND_I(X_TO_CURRENCY);
605 STPROC.BIND_I(X_EFFECTIVE_DATE);
606 STPROC.EXECUTE;
607 STPROC.RETRIEVE(1
608 ,X0);*/
609 null;
610 RETURN X0;
611 END IS_FIXED_RATE;
612
613 PROCEDURE GET_RELATION(X_FROM_CURRENCY IN VARCHAR2
614 ,X_TO_CURRENCY IN VARCHAR2
615 ,X_EFFECTIVE_DATE IN DATE
616 ,X_FIXED_RATE IN OUT NOCOPY BOOLEAN
617 ,X_RELATIONSHIP IN OUT NOCOPY VARCHAR2) IS
618 BEGIN
619 /*
620 STPROC.BIND_IO(X_FIXED_RATE);
621 STPROC.BIND_I(X_FROM_CURRENCY);
622 STPROC.BIND_I(X_TO_CURRENCY);
623 STPROC.BIND_I(X_EFFECTIVE_DATE);
624 STPROC.BIND_IO(X_RELATIONSHIP);
625 STPROC.EXECUTE;
626 STPROC.RETRIEVE(1
627 ,X_FIXED_RATE);
628 STPROC.RETRIEVE(5
629 ,X_RELATIONSHIP);*/
630 null;
631 END GET_RELATION;
632
633 FUNCTION GET_EURO_CODE RETURN VARCHAR2 IS
634 X0 VARCHAR2(2000);
635 BEGIN
636 /* STPROC.INIT('begin :X0 := GL_CURRENCY_API.GET_EURO_CODE; end;');
637 STPROC.BIND_O(X0);
638 STPROC.EXECUTE;
639 STPROC.RETRIEVE(1
640 ,X0);*/
641 null;
642 RETURN X0;
643 END GET_EURO_CODE;
644
645 FUNCTION GET_RATE(X_FROM_CURRENCY IN VARCHAR2
646 ,X_TO_CURRENCY IN VARCHAR2
647 ,X_CONVERSION_DATE IN DATE
648 ,X_CONVERSION_TYPE IN VARCHAR2) RETURN NUMBER IS
649 X0 NUMBER;
650 BEGIN
651 /* STPROC.INIT('begin :X0 := GL_CURRENCY_API.GET_RATE(:X_FROM_CURRENCY, :X_TO_CURRENCY, :X_CONVERSION_DATE, :X_CONVERSION_TYPE); end;');
652 STPROC.BIND_O(X0);
653 STPROC.BIND_I(X_FROM_CURRENCY);
654 STPROC.BIND_I(X_TO_CURRENCY);
655 STPROC.BIND_I(X_CONVERSION_DATE);
656 STPROC.BIND_I(X_CONVERSION_TYPE);
657 STPROC.EXECUTE;
658 STPROC.RETRIEVE(1
659 ,X0);*/
660 RETURN X0;
661 END GET_RATE;
662
663 FUNCTION GET_RATE(X_SET_OF_BOOKS_ID IN NUMBER
664 ,X_FROM_CURRENCY IN VARCHAR2
665 ,X_CONVERSION_DATE IN DATE
666 ,X_CONVERSION_TYPE IN VARCHAR2) RETURN NUMBER IS
667 X0 NUMBER;
668 BEGIN
669 /* STPROC.INIT('begin :X0 := GL_CURRENCY_API.GET_RATE(:X_SET_OF_BOOKS_ID, :X_FROM_CURRENCY, :X_CONVERSION_DATE, :X_CONVERSION_TYPE); end;');
670 STPROC.BIND_O(X0);
671 STPROC.BIND_I(X_SET_OF_BOOKS_ID);
672 STPROC.BIND_I(X_FROM_CURRENCY);
673 STPROC.BIND_I(X_CONVERSION_DATE);
674 STPROC.BIND_I(X_CONVERSION_TYPE);
675 STPROC.EXECUTE;
676 STPROC.RETRIEVE(1
677 ,X0);*/
678 RETURN X0;
679 END GET_RATE;
680
681 FUNCTION CONVERT_AMOUNT(X_FROM_CURRENCY IN VARCHAR2
682 ,X_TO_CURRENCY IN VARCHAR2
683 ,X_CONVERSION_DATE IN DATE
684 ,X_CONVERSION_TYPE IN VARCHAR2
685 ,X_AMOUNT IN NUMBER) RETURN NUMBER IS
686 X0 NUMBER;
687 BEGIN
688 /* STPROC.INIT('begin :X0 := GL_CURRENCY_API.CONVERT_AMOUNT(:X_FROM_CURRENCY, :X_TO_CURRENCY, :X_CONVERSION_DATE, :X_CONVERSION_TYPE, :X_AMOUNT); end;');
689 STPROC.BIND_O(X0);
690 STPROC.BIND_I(X_FROM_CURRENCY);
691 STPROC.BIND_I(X_TO_CURRENCY);
692 STPROC.BIND_I(X_CONVERSION_DATE);
693 STPROC.BIND_I(X_CONVERSION_TYPE);
694 STPROC.BIND_I(X_AMOUNT);
695 STPROC.EXECUTE;
696 STPROC.RETRIEVE(1
697 ,X0);*/
698 RETURN X0;
699 END CONVERT_AMOUNT;
700
701 FUNCTION CONVERT_AMOUNT(X_SET_OF_BOOKS_ID IN NUMBER
702 ,X_FROM_CURRENCY IN VARCHAR2
703 ,X_CONVERSION_DATE IN DATE
704 ,X_CONVERSION_TYPE IN VARCHAR2
705 ,X_AMOUNT IN NUMBER) RETURN NUMBER IS
706 X0 NUMBER;
707 BEGIN
708 /* 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;');
709 STPROC.BIND_O(X0);
710 STPROC.BIND_I(X_SET_OF_BOOKS_ID);
711 STPROC.BIND_I(X_FROM_CURRENCY);
712 STPROC.BIND_I(X_CONVERSION_DATE);
713 STPROC.BIND_I(X_CONVERSION_TYPE);
714 STPROC.BIND_I(X_AMOUNT);
715 STPROC.EXECUTE;
716 STPROC.RETRIEVE(1
717 ,X0);*/
718 RETURN X0;
719 END CONVERT_AMOUNT;
720
721 FUNCTION GET_DERIVE_TYPE(SOB_ID IN NUMBER
722 ,PERIOD IN VARCHAR2
723 ,CURR_CODE IN VARCHAR2) RETURN VARCHAR2 IS
724 X0 VARCHAR2(2000);
725 BEGIN
726 /* STPROC.INIT('begin :X0 := GL_CURRENCY_API.GET_DERIVE_TYPE(:SOB_ID, :PERIOD, :CURR_CODE); end;');
727 STPROC.BIND_O(X0);
728 STPROC.BIND_I(SOB_ID);
729 STPROC.BIND_I(PERIOD);
730 STPROC.BIND_I(CURR_CODE);
731 STPROC.EXECUTE;
732 STPROC.RETRIEVE(1
733 ,X0);*/
734 null;
735 RETURN X0;
736 END GET_DERIVE_TYPE;
737
738 END ONT_OEXOEIOS_XMLP_PKG;
739
740