1 PACKAGE BODY ONT_OEXOEOSR_XMLP_PKG AS
2 /* $Header: OEXOEOSRB.pls 120.1 2007/12/25 07:23:40 npannamp noship $ */
3 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
4 BEGIN
5 BEGIN
6 BEGIN
7 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
8 P_ORDER_DATE_LOW_V:=to_char(P_ORDER_DATE_LOW,'DD-MON-YY');
9 P_ORDER_DATE_HIGH_V:=to_char(P_ORDER_DATE_HIGH,'DD-MON-YY');
10 --added as fix
11 F1:=Oe_Sys_Parameters.Value('RECURRING_CHARGES',mo_global.get_current_org_id());
12 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
13 EXCEPTION
14 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
15 /*SRW.MESSAGE(1000
16 ,'Failed in BEFORE REPORT trigger')*/NULL;
17 RETURN (FALSE);
18 END;
19 BEGIN
20 -- P_ORG_ID := MO_GLOBAL.GET_CURRENT_ORG_ID;
21 P_ORG_ID_V := MO_GLOBAL.GET_CURRENT_ORG_ID;
22 END;
23 DECLARE
24 L_COMPANY_NAME VARCHAR2(100);
25 L_FUNCTIONAL_CURRENCY VARCHAR2(15);
26 BEGIN
27 SELECT
28 SOB.NAME,
29 SOB.CURRENCY_CODE
30 INTO L_COMPANY_NAME,L_FUNCTIONAL_CURRENCY
31 FROM
32 GL_SETS_OF_BOOKS SOB,
33 FND_CURRENCIES CUR
34 WHERE SOB.SET_OF_BOOKS_ID = P_SOB_ID
35 AND SOB.CURRENCY_CODE = CUR.CURRENCY_CODE;
36 RP_COMPANY_NAME := L_COMPANY_NAME;
37 RP_FUNCTIONAL_CURRENCY := L_FUNCTIONAL_CURRENCY;
38 EXCEPTION
39 WHEN NO_DATA_FOUND THEN
40 NULL;
41 END;
42 DECLARE
43 L_ORDER_SOURCE_NAME VARCHAR2(50);
44 BEGIN
45 SELECT
46 NAME
47 INTO L_ORDER_SOURCE_NAME
48 FROM
49 OE_ORDER_SOURCES
50 WHERE ORDER_SOURCE_ID = NVL(P_ORDER_SOURCE
51 ,-999);
52 LP_ORDER_SOURCE_NAME := L_ORDER_SOURCE_NAME;
53 EXCEPTION
54 WHEN NO_DATA_FOUND THEN
55 NULL;
56 END;
57 DECLARE
58 L_REPORT_NAME VARCHAR2(240);
59 BEGIN
60 SELECT
61 CP.USER_CONCURRENT_PROGRAM_NAME
62 INTO L_REPORT_NAME
63 FROM
64 FND_CONCURRENT_PROGRAMS_VL CP,
65 FND_CONCURRENT_REQUESTS CR
66 WHERE CR.REQUEST_ID = P_CONC_REQUEST_ID
67 AND CP.APPLICATION_ID = CR.PROGRAM_APPLICATION_ID
68 AND CP.CONCURRENT_PROGRAM_ID = CR.CONCURRENT_PROGRAM_ID;
69 RP_REPORT_NAME := L_REPORT_NAME;
70 EXCEPTION
71 WHEN NO_DATA_FOUND THEN
72 RP_REPORT_NAME := 'Orders Summary Report';
73 END;
74 DECLARE
75 L_ORDER_TYPE_LOW_V VARCHAR2(50);
76 L_ORDER_TYPE_HIGH_V VARCHAR2(50);
77 L_CUSTOMER_NAME_LOW VARCHAR2(50);
78 L_CUSTOMER_NAME_HIGH VARCHAR2(50);
79 L_SALESREP_LOW VARCHAR2(50);
80 L_SALESREP_HIGH VARCHAR2(50);
81 L_ORDER_NUMBER_LOW VARCHAR2(50);
82 L_ORDER_NUMBER_HIGH VARCHAR2(50);
83 L_PO_NUMBER_LOW VARCHAR2(50);
84 L_PO_NUMBER_HIGH VARCHAR2(50);
85 L_ORDER_DATE_LOW VARCHAR2(50);
86 L_ORDER_DATE_HIGH VARCHAR2(50);
87 L_ORDER_STATUS_LOW VARCHAR2(50);
88 L_ORDER_STATUS_HIGH VARCHAR2(50);
89 L_COUNTRY_LOW VARCHAR2(50);
90 L_COUNTRY_HIGH VARCHAR2(50);
91 L_CREATED_BY_LOW VARCHAR2(50);
92 L_CREATED_BY_HIGH VARCHAR2(50);
93 BEGIN
94 IF (P_ORDER_TYPE_LOW IS NULL) AND (P_ORDER_TYPE_HIGH IS NULL) THEN
95 NULL;
96 ELSE
97 IF P_ORDER_TYPE_LOW IS NULL THEN
98 L_ORDER_TYPE_LOW_V := ' ';
99 ELSE
100 L_ORDER_TYPE_LOW_V := SUBSTR(L_ORDER_TYPE_LOW
101 ,1
102 ,18);
103 END IF;
104 IF P_ORDER_TYPE_HIGH IS NULL THEN
105 L_ORDER_TYPE_HIGH_V := ' ';
106 ELSE
107 L_ORDER_TYPE_HIGH_V := SUBSTR(L_ORDER_TYPE_HIGH
108 ,1
109 ,18);
110 END IF;
111 RP_ORDER_TYPE_RANGE := 'From ' || L_ORDER_TYPE_LOW_V || ' To ' || L_ORDER_TYPE_HIGH_V;
112 END IF;
113 IF (P_CUSTOMER_NAME_LOW IS NULL) AND (P_CUSTOMER_NAME_HIGH IS NULL) THEN
114 NULL;
115 ELSE
116 IF P_CUSTOMER_NAME_LOW IS NULL THEN
117 L_CUSTOMER_NAME_LOW := ' ';
118 ELSE
119 L_CUSTOMER_NAME_LOW := P_CUSTOMER_NAME_LOW;
120 END IF;
121 IF P_CUSTOMER_NAME_HIGH IS NULL THEN
122 L_CUSTOMER_NAME_HIGH := ' ';
123 ELSE
124 L_CUSTOMER_NAME_HIGH := P_CUSTOMER_NAME_HIGH;
125 END IF;
126 RP_CUSTOMER_NAME_RANGE := 'From ' || L_CUSTOMER_NAME_LOW || ' To ' || L_CUSTOMER_NAME_HIGH;
127 END IF;
128 IF (P_SALESREP_LOW IS NULL) AND (P_SALESREP_HIGH IS NULL) THEN
129 NULL;
130 ELSE
131 IF P_SALESREP_LOW IS NULL THEN
132 L_SALESREP_LOW := ' ';
133 ELSE
134 L_SALESREP_LOW := SUBSTR(P_SALESREP_LOW
135 ,1
136 ,18);
137 END IF;
138 IF P_SALESREP_HIGH IS NULL THEN
139 L_SALESREP_HIGH := ' ';
140 ELSE
141 L_SALESREP_HIGH := SUBSTR(P_SALESREP_HIGH
142 ,1
143 ,18);
144 END IF;
145 RP_SALESREP_RANGE := 'From ' || L_SALESREP_LOW || ' To ' || L_SALESREP_HIGH;
146 END IF;
147 IF (P_ORDER_NUM_LOW IS NULL) AND (P_ORDER_NUM_HIGH IS NULL) THEN
148 NULL;
149 ELSE
150 IF P_ORDER_NUM_LOW IS NULL THEN
151 L_ORDER_NUMBER_LOW := ' ';
152 ELSE
153 L_ORDER_NUMBER_LOW := SUBSTR(P_ORDER_NUM_LOW
154 ,1
155 ,18);
156 END IF;
157 IF P_ORDER_NUM_HIGH IS NULL THEN
158 L_ORDER_NUMBER_HIGH := ' ';
159 ELSE
160 L_ORDER_NUMBER_HIGH := SUBSTR((P_ORDER_NUM_HIGH)
161 ,1
162 ,18);
163 END IF;
164 RP_ORDER_NUMBER_RANGE := 'From ' || L_ORDER_NUMBER_LOW || ' To ' || L_ORDER_NUMBER_HIGH;
165 END IF;
166 IF (P_PO_NUM_LOW IS NULL) AND (P_PO_NUM_HIGH IS NULL) THEN
167 NULL;
168 ELSE
169 IF P_PO_NUM_LOW IS NULL THEN
170 L_PO_NUMBER_LOW := ' ';
171 ELSE
172 L_PO_NUMBER_LOW := SUBSTR(P_PO_NUM_LOW
173 ,1
174 ,18);
175 END IF;
176 IF P_PO_NUM_HIGH IS NULL THEN
177 L_PO_NUMBER_HIGH := ' ';
178 ELSE
179 L_PO_NUMBER_HIGH := SUBSTR((P_PO_NUM_HIGH)
180 ,1
181 ,18);
182 END IF;
183 RP_PO_NUMBER_RANGE := 'From ' || L_PO_NUMBER_LOW || ' To ' || L_PO_NUMBER_HIGH;
184 END IF;
185 IF (P_ORDER_DATE_LOW IS NULL) AND (P_ORDER_DATE_HIGH IS NULL) THEN
186 NULL;
187 ELSE
188 IF P_ORDER_DATE_LOW IS NULL THEN
189 L_ORDER_DATE_LOW := ' ';
190 ELSE
191 L_ORDER_DATE_LOW := P_ORDER_DATE_LOW;
192 END IF;
193 IF P_ORDER_DATE_HIGH IS NULL THEN
194 L_ORDER_DATE_HIGH := ' ';
195 ELSE
196 L_ORDER_DATE_HIGH := P_ORDER_DATE_HIGH;
197 END IF;
198 --RP_ORDER_DATE_RANGE := 'From ' || L_ORDER_DATE_LOW || ' To ' || L_ORDER_DATE_HIGH;
199 RP_ORDER_DATE_RANGE := 'From ' || substr(L_ORDER_DATE_LOW,1,7) ||substr(L_ORDER_DATE_LOW,10,11)|| ' To ' || substr(L_ORDER_DATE_HIGH,1,7)||substr(L_ORDER_DATE_HIGH,10,11);
200 END IF;
201 IF (P_ORDER_STATUS_LOW IS NULL) AND (P_ORDER_STATUS_HIGH IS NULL) THEN
202 NULL;
203 ELSE
204 IF P_ORDER_STATUS_LOW IS NULL THEN
205 L_ORDER_STATUS_LOW := ' ';
206 ELSE
207 L_ORDER_STATUS_LOW := SUBSTR(P_ORDER_STATUS_LOW
208 ,1
209 ,18);
210 END IF;
211 IF P_ORDER_STATUS_HIGH IS NULL THEN
212 L_ORDER_STATUS_HIGH := ' ';
213 ELSE
214 L_ORDER_STATUS_HIGH := SUBSTR((P_ORDER_STATUS_HIGH)
215 ,1
216 ,18);
217 END IF;
218 RP_ORDER_STATUS_RANGE := 'From ' || L_ORDER_STATUS_LOW || ' To ' || L_ORDER_STATUS_HIGH;
219 END IF;
220 IF (P_COUNTRY_LOW IS NULL) AND (P_COUNTRY_HIGH IS NULL) THEN
221 NULL;
222 ELSE
223 IF P_COUNTRY_LOW IS NULL THEN
224 L_COUNTRY_LOW := ' ';
225 ELSE
226 L_COUNTRY_LOW := SUBSTR(P_COUNTRY_LOW
227 ,1
228 ,18);
229 END IF;
230 IF P_COUNTRY_HIGH IS NULL THEN
231 L_COUNTRY_HIGH := ' ';
232 ELSE
233 L_COUNTRY_HIGH := SUBSTR((P_COUNTRY_HIGH)
234 ,1
235 ,18);
236 END IF;
237 RP_COUNTRY_RANGE := 'From ' || L_COUNTRY_LOW || ' To ' || L_COUNTRY_HIGH;
238 END IF;
239 IF (P_CREATED_BY_LOW IS NULL) AND (P_CREATED_BY_HIGH IS NULL) THEN
240 NULL;
241 ELSE
242 IF P_CREATED_BY_LOW IS NULL THEN
243 L_CREATED_BY_LOW := ' ';
244 ELSE
245 L_CREATED_BY_LOW := SUBSTR(P_CREATED_BY_LOW
246 ,1
247 ,18);
248 END IF;
249 IF P_CREATED_BY_HIGH IS NULL THEN
250 L_CREATED_BY_HIGH := ' ';
251 ELSE
252 L_CREATED_BY_HIGH := SUBSTR((P_CREATED_BY_HIGH)
253 ,1
254 ,18);
255 END IF;
256 RP_CREATED_BY_RANGE := 'From ' || L_CREATED_BY_LOW || ' To ' || L_CREATED_BY_HIGH;
257 END IF;
258 END;
259 DECLARE
260 L_AGREEMENT_NAME VARCHAR2(50);
261 BEGIN
262 IF (P_AGREEMENT IS NOT NULL) THEN
263 BEGIN
264 SELECT
265 NAME
266 INTO L_AGREEMENT_NAME
267 FROM
268 OE_AGREEMENTS
269 WHERE AGREEMENT_ID = P_AGREEMENT;
270 RP_AGREEMENT := L_AGREEMENT_NAME;
271 EXCEPTION
272 WHEN NO_DATA_FOUND THEN
273 RP_AGREEMENT := NULL;
274 END;
275 END IF;
276 END;
277 DECLARE
278 L_MEANING VARCHAR2(80);
279 L_LOOKUP_TYPE VARCHAR2(80);
280 BEGIN
281 L_LOOKUP_TYPE := 'YES_NO';
282 SELECT
283 MEANING
284 INTO L_MEANING
285 FROM
286 FND_LOOKUPS
287 WHERE LOOKUP_TYPE = L_LOOKUP_TYPE
288 AND LOOKUP_CODE = SUBSTR(UPPER(P_OPEN_ORDERS_ONLY)
289 ,1
290 ,1);
291 RP_OPEN_ORDERS_ONLY := L_MEANING;
292 EXCEPTION
293 WHEN NO_DATA_FOUND THEN
294 RP_OPEN_ORDERS_ONLY := 'Yes';
295 END;
296 IF P_ORDER_BY IS NOT NULL THEN
297 DECLARE
298 ORDER_BY VARCHAR2(80);
299 L_LOOKUP_TYPE VARCHAR2(80);
300 L_LOOKUP_CODE VARCHAR2(80);
301 BEGIN
302 L_LOOKUP_TYPE := 'ONT_OEXOEOSR_XMLP_PKG SORT BY';
303 L_LOOKUP_CODE := P_ORDER_BY;
304 SELECT
305 MEANING
306 INTO ORDER_BY
307 FROM
308 OE_LOOKUPS
309 WHERE LOOKUP_TYPE = L_LOOKUP_TYPE
310 AND LOOKUP_CODE = L_LOOKUP_CODE;
311 RP_ORDER_BY := ORDER_BY;
312 EXCEPTION
313 WHEN NO_DATA_FOUND THEN
314 RP_ORDER_BY := P_ORDER_BY;
315 END;
316 END IF;
317 END;
318 RETURN (TRUE);
319 END BEFOREREPORT;
320
321 FUNCTION AFTERREPORT RETURN BOOLEAN IS
322 BEGIN
323 BEGIN
324 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
325 EXCEPTION
326 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
327 /*SRW.MESSAGE(1
328 ,'Failed in AFTER REPORT TRIGGER')*/NULL;
329 RETURN (FALSE);
330 END;
331 RETURN (TRUE);
332 END AFTERREPORT;
333
334 FUNCTION AFTERPFORM RETURN BOOLEAN IS
335 BEGIN
336 /*SRW.MESSAGE(99999
337 ,'$Header: ONT_OEXOEOSR_XMLP_PKG.rdf 120.7 2006/04/28 02:43 ddey ship
338 $')*/NULL;
339 /*SRW.MESSAGE(99999
340 ,'Oracle - Test Report')*/NULL;
341 BEGIN
342 IF (P_ORDER_TYPE_LOW IS NOT NULL) AND (P_ORDER_TYPE_HIGH IS NOT NULL) THEN
343 IF (P_ORDER_TYPE_LOW = P_ORDER_TYPE_HIGH) THEN
344 LP_ORDER_TYPE := ' and ot.transaction_type_id = :p_order_type_low ';
345 SELECT
346 OEOT.NAME
347 INTO L_ORDER_TYPE_LOW
348 FROM
349 OE_TRANSACTION_TYPES_TL OEOT
350 WHERE OEOT.TRANSACTION_TYPE_ID = P_ORDER_TYPE_LOW
351 AND OEOT.LANGUAGE = USERENV('LANG');
352 ELSE
353 LP_ORDER_TYPE := 'and ( ot.transaction_type_id between :p_order_type_low and :p_order_type_high ) ';
354 SELECT
355 OEOT.NAME
356 INTO L_ORDER_TYPE_LOW
357 FROM
358 OE_TRANSACTION_TYPES_TL OEOT
359 WHERE OEOT.TRANSACTION_TYPE_ID = P_ORDER_TYPE_LOW
360 AND OEOT.LANGUAGE = USERENV('LANG');
361 SELECT
362 OEOT.NAME
363 INTO L_ORDER_TYPE_HIGH
364 FROM
365 OE_TRANSACTION_TYPES_TL OEOT
366 WHERE OEOT.TRANSACTION_TYPE_ID = P_ORDER_TYPE_HIGH
367 AND OEOT.LANGUAGE = USERENV('LANG');
368 END IF;
369 ELSIF (P_ORDER_TYPE_LOW IS NOT NULL) THEN
370 LP_ORDER_TYPE := 'and ot.transaction_type_id >= :p_order_type_low ';
371 SELECT
372 OEOT.NAME
373 INTO L_ORDER_TYPE_LOW
374 FROM
375 OE_TRANSACTION_TYPES_TL OEOT
376 WHERE OEOT.TRANSACTION_TYPE_ID = P_ORDER_TYPE_LOW
377 AND OEOT.LANGUAGE = USERENV('LANG');
378 ELSIF (P_ORDER_TYPE_HIGH IS NOT NULL) THEN
379 LP_ORDER_TYPE := 'and ot.transaction_type_id <= :p_order_type_high ';
380 SELECT
381 OEOT.NAME
382 INTO L_ORDER_TYPE_HIGH
383 FROM
384 OE_TRANSACTION_TYPES_TL OEOT
385 WHERE OEOT.TRANSACTION_TYPE_ID = P_ORDER_TYPE_HIGH
386 AND OEOT.LANGUAGE = USERENV('LANG');
387 END IF;
388 IF (P_CUSTOMER_NAME_LOW IS NOT NULL) AND (P_CUSTOMER_NAME_HIGH IS NOT NULL) THEN
389 IF (P_CUSTOMER_NAME_LOW = P_CUSTOMER_NAME_HIGH) THEN
390 LP_CUSTOMER_NAME := ' and party.party_name = :p_customer_name_low ';
391 ELSE
392 LP_CUSTOMER_NAME := 'and ( party.party_name between :p_customer_name_low and :p_customer_name_high ) ';
393 END IF;
394 ELSIF (P_CUSTOMER_NAME_LOW IS NOT NULL) THEN
395 LP_CUSTOMER_NAME := 'and party.party_name >= :p_customer_name_low ';
396 ELSIF (P_CUSTOMER_NAME_HIGH IS NOT NULL) THEN
397 LP_CUSTOMER_NAME := 'and party.party_name <= :p_customer_name_high ';
398 END IF;
399 IF (P_ORDER_NUM_LOW IS NOT NULL) AND (P_ORDER_NUM_HIGH IS NOT NULL) THEN
400 IF (P_ORDER_NUM_LOW = P_ORDER_NUM_HIGH) THEN
401 LP_ORDER_NUM := 'and h.order_number = :p_order_num_low ';
402 ELSE
403 LP_ORDER_NUM := 'and ( h.order_number between to_number(:p_order_num_low) and to_number(:p_order_num_high) ) ';
404 END IF;
405 ELSIF (P_ORDER_NUM_LOW IS NOT NULL) THEN
406 LP_ORDER_NUM := 'and h.order_number >= to_number(:p_order_num_low) ';
407 ELSIF (P_ORDER_NUM_HIGH IS NOT NULL) THEN
408 LP_ORDER_NUM := 'and h.order_number <= to_number(:p_order_num_high) ';
409 END IF;
410 IF (P_SALESREP_LOW IS NOT NULL) AND (P_SALESREP_HIGH IS NOT NULL) THEN
411 IF (P_SALESREP_LOW = P_SALESREP_HIGH) THEN
412 LP_SALESREP := ' and sr.name = :p_salesrep_low ';
413 ELSE
414 LP_SALESREP := 'and sr.name between :p_salesrep_low and :p_salesrep_high ';
415 END IF;
416 ELSIF (P_SALESREP_LOW IS NOT NULL) THEN
417 LP_SALESREP := 'and sr.name >= :p_salesrep_low ';
418 ELSIF (P_SALESREP_HIGH IS NOT NULL) THEN
419 LP_SALESREP := 'and sr.name <= :p_salesrep_high ';
420 END IF;
421 IF (P_COUNTRY_LOW IS NOT NULL) AND (P_COUNTRY_HIGH IS NOT NULL) THEN
422 LP_COUNTRY := 'and ( terr.territory_short_name between :p_country_low and :p_country_high ) ';
423 ELSIF (P_COUNTRY_LOW IS NOT NULL) THEN
424 LP_COUNTRY := 'and terr.territory_short_name >= :p_country_low ';
425 ELSIF (P_COUNTRY_HIGH IS NOT NULL) THEN
426 LP_COUNTRY := 'and terr.territory_short_name <= :p_country_high ';
427 END IF;
428 IF (P_PO_NUM_LOW IS NOT NULL) AND (P_PO_NUM_HIGH IS NOT NULL) THEN
429 IF (P_PO_NUM_LOW = P_PO_NUM_HIGH) THEN
430 LP_PO_NUM := ' and h.cust_po_number = :p_po_num_low ';
431 ELSE
432 LP_PO_NUM := 'and ( h.cust_po_number between :p_po_num_low and :p_po_num_high ) ';
433 END IF;
434 ELSIF (P_PO_NUM_LOW IS NOT NULL) THEN
435 LP_PO_NUM := 'and h.cust_po_number >= :p_po_num_low ';
436 ELSIF (P_PO_NUM_HIGH IS NOT NULL) THEN
437 LP_PO_NUM := 'and h.cust_po_number <= :p_po_num_high ';
438 END IF;
439 IF P_ORDER_DATE_LOW IS NOT NULL AND P_ORDER_DATE_HIGH IS NOT NULL THEN
440 LP_ORDER_DATE := ' AND h.ordered_date >= :p_order_date_low and h.ordered_date < :p_order_date_high+1';
441 ELSIF (P_ORDER_DATE_LOW IS NOT NULL) THEN
442 LP_ORDER_DATE := 'and h.ordered_date >= :p_order_date_low';
443 ELSIF (P_ORDER_DATE_HIGH IS NOT NULL) THEN
444 LP_ORDER_DATE := 'and h.ordered_date <= :p_order_date_high+1';
445 END IF;
446 IF P_CREATED_BY_LOW IS NOT NULL AND P_CREATED_BY_HIGH IS NOT NULL THEN
447 LP_CREATED_BY := ' AND u.user_name between :p_created_by_low and :p_created_by_high';
448 ELSIF (P_CREATED_BY_LOW IS NOT NULL) THEN
449 LP_CREATED_BY := 'and u.user_name >= :p_created_by_low';
450 ELSIF (P_CREATED_BY_HIGH IS NOT NULL) THEN
451 LP_CREATED_BY := 'and u.user_name <= :p_created_by_high';
452 END IF;
453 IF (P_AGREEMENT IS NOT NULL) THEN
454 LP_AGREEMENT := 'and agree.agreement_id = :p_agreement';
455 END IF;
456 IF (P_ORDER_SOURCE IS NOT NULL) THEN
457 LP_ORDER_SOURCE := 'and h.order_source_id = :p_order_source ';
458 END IF;
459 IF P_OPEN_ORDERS_ONLY = 'Y' THEN
460 LP_OPEN_ORDERS_ONLY := 'and nvl(h.open_flag,''N'') = ''Y'' ';
461 END IF;
462 END;
463 RETURN (TRUE);
464 END AFTERPFORM;
465
466 FUNCTION C_DATA_NOT_FOUNDFORMULA(CURRENCY1 IN VARCHAR2) RETURN NUMBER IS
467 BEGIN
468 RP_DATA_FOUND := CURRENCY1;
469 RETURN (0);
470 END C_DATA_NOT_FOUNDFORMULA;
471
472 FUNCTION RP_CURR_LABELFORMULA RETURN VARCHAR2 IS
473 BEGIN
474 IF SUBSTR(UPPER(P_ORDER_BY)
475 ,1
476 ,1) = 'O' THEN
477 RETURN (' Currency');
478 ELSIF SUBSTR(UPPER(P_ORDER_BY)
479 ,1
480 ,1) = 'S' THEN
481 RETURN (' Currency');
482 ELSE
483 RETURN ('Currency');
484 END IF;
485 RETURN NULL;
486 END RP_CURR_LABELFORMULA;
487
488 FUNCTION C_ORDER_COUNTFORMULA RETURN NUMBER IS
489 BEGIN
490 RETURN (1);
491 END C_ORDER_COUNTFORMULA;
492
493 FUNCTION C_LINE_COUNTFORMULA(HEADER_ID1 IN NUMBER) RETURN NUMBER IS
494 BEGIN
495 DECLARE
496 L_COUNT NUMBER(20);
497 BEGIN
498 /*SRW.REFERENCE(HEADER_ID1)*/NULL;
499 L_COUNT := 0;
500 SELECT
501 COUNT(1)
502 INTO L_COUNT
503 FROM
504 OE_ORDER_LINES_ALL L
505 WHERE L.HEADER_ID = HEADER_ID1;
506 RETURN (L_COUNT);
507 END;
508 RETURN NULL;
509 END C_LINE_COUNTFORMULA;
510
511 FUNCTION RP_USE_FUNCTIONAL_CURRENCYFORM RETURN VARCHAR2 IS
512 BEGIN
513 DECLARE
514 L_TEMP VARCHAR2(100);
515 L_LOOKUP_TYPE VARCHAR2(80);
516 L_LOOKUP_CODE VARCHAR2(80);
517 BEGIN
518 L_LOOKUP_TYPE := 'YES_NO';
519 L_LOOKUP_CODE := P_USE_FUNCTIONAL_CURRENCY;
520 SELECT
521 MEANING
522 INTO L_TEMP
523 FROM
524 FND_LOOKUPS
525 WHERE LOOKUP_CODE = L_LOOKUP_CODE
526 AND LOOKUP_TYPE = L_LOOKUP_TYPE;
527 RETURN (L_TEMP);
528 EXCEPTION
529 WHEN NO_DATA_FOUND THEN
530 RETURN ('No');
531 END;
532 RETURN NULL;
533 END RP_USE_FUNCTIONAL_CURRENCYFORM;
534
535 FUNCTION C_ORDER_AMOUNTFORMULA(CURRENCY1 IN VARCHAR2
536 ,ORDER_AMOUNT IN NUMBER
537 ,CONVERSION_TYPE_CODE IN VARCHAR2
538 ,ORDER_DATE IN DATE
539 ,CONVERSION_RATE IN NUMBER) RETURN NUMBER IS
540 BEGIN
541 DECLARE
542 L_CONVERSION_RATE NUMBER;
543 BEGIN
544 /*SRW.REFERENCE(CURRENCY1)*/NULL;
545 /*SRW.REFERENCE(RP_FUNCTIONAL_CURRENCY)*/NULL;
546 /*SRW.REFERENCE(ORDER_AMOUNT)*/NULL;
547 /*SRW.REFERENCE(CONVERSION_TYPE_CODE)*/NULL;
548 /*SRW.REFERENCE(ORDER_DATE)*/NULL;
549 L_CONVERSION_RATE := 0;
550 IF P_USE_FUNCTIONAL_CURRENCY = 'N' THEN
551 RETURN (ROUND(NVL(ORDER_AMOUNT
552 ,0)
553 ,2));
554 ELSIF P_USE_FUNCTIONAL_CURRENCY = 'Y' THEN
555 IF CURRENCY1 = RP_FUNCTIONAL_CURRENCY THEN
556 L_CONVERSION_RATE := 1;
557 ELSE
558 IF CONVERSION_RATE IS NULL THEN
559 L_CONVERSION_RATE := GET_RATE(P_SOB_ID
560 ,CURRENCY1
561 ,ORDER_DATE
562 ,CONVERSION_TYPE_CODE);
563 ELSE
564 L_CONVERSION_RATE := CONVERSION_RATE;
565 END IF;
566 END IF;
567 RETURN (NVL(L_CONVERSION_RATE
568 ,0) * ROUND(NVL(ORDER_AMOUNT
569 ,0)
570 ,2));
571 END IF;
572 EXCEPTION
573 WHEN NO_DATA_FOUND THEN
574 RETURN (0);
575 WHEN OTHERS THEN
576 RETURN (0);
577 END;
578 RETURN NULL;
579 END C_ORDER_AMOUNTFORMULA;
580
581 FUNCTION C_LIST_AMOUNTFORMULA(CURRENCY1 IN VARCHAR2
582 ,LIST_VALUE IN NUMBER
583 ,CONVERSION_TYPE_CODE IN VARCHAR2
584 ,ORDER_DATE IN DATE
585 ,CONVERSION_RATE IN NUMBER) RETURN NUMBER IS
586 BEGIN
587 DECLARE
588 L_CONVERSION_RATE NUMBER;
589 BEGIN
590 /*SRW.REFERENCE(CURRENCY1)*/NULL;
591 /*SRW.REFERENCE(RP_FUNCTIONAL_CURRENCY)*/NULL;
592 /*SRW.REFERENCE(LIST_VALUE)*/NULL;
593 /*SRW.REFERENCE(CONVERSION_TYPE_CODE)*/NULL;
594 /*SRW.REFERENCE(ORDER_DATE)*/NULL;
595 L_CONVERSION_RATE := 0;
596 IF P_USE_FUNCTIONAL_CURRENCY = 'N' THEN
597 RETURN (ROUND(NVL(LIST_VALUE
598 ,0)
599 ,2));
600 ELSIF P_USE_FUNCTIONAL_CURRENCY = 'Y' THEN
601 IF CURRENCY1 = RP_FUNCTIONAL_CURRENCY THEN
602 L_CONVERSION_RATE := 1;
603 ELSE
604 IF CONVERSION_RATE IS NULL THEN
605 L_CONVERSION_RATE := GET_RATE(P_SOB_ID
606 ,CURRENCY1
607 ,ORDER_DATE
608 ,CONVERSION_TYPE_CODE);
609 ELSE
610 L_CONVERSION_RATE := CONVERSION_RATE;
611 END IF;
612 END IF;
613 RETURN (NVL(L_CONVERSION_RATE
614 ,0) * ROUND(NVL(LIST_VALUE
615 ,0)
616 ,2));
617 END IF;
618 EXCEPTION
619 WHEN NO_DATA_FOUND THEN
620 RETURN (0);
621 WHEN OTHERS THEN
622 RETURN (0);
623 END;
624 END C_LIST_AMOUNTFORMULA;
625
626 FUNCTION C_SHIPPED_AMOUNTFORMULA(CURRENCY1 IN VARCHAR2
627 ,SHIP_VALUE IN NUMBER
628 ,CONVERSION_TYPE_CODE IN VARCHAR2
629 ,ORDER_DATE IN DATE
630 ,CONVERSION_RATE IN NUMBER) RETURN NUMBER IS
631 BEGIN
632 DECLARE
633 L_CONVERSION_RATE NUMBER;
634 BEGIN
635 /*SRW.REFERENCE(CURRENCY1)*/NULL;
636 /*SRW.REFERENCE(RP_FUNCTIONAL_CURRENCY)*/NULL;
637 /*SRW.REFERENCE(SHIP_VALUE)*/NULL;
638 /*SRW.REFERENCE(CONVERSION_TYPE_CODE)*/NULL;
639 /*SRW.REFERENCE(ORDER_DATE)*/NULL;
640 L_CONVERSION_RATE := 0;
641 IF P_USE_FUNCTIONAL_CURRENCY = 'N' THEN
642 RETURN (ROUND(NVL(SHIP_VALUE
643 ,0)
644 ,2));
645 ELSIF P_USE_FUNCTIONAL_CURRENCY = 'Y' THEN
646 IF CURRENCY1 = RP_FUNCTIONAL_CURRENCY THEN
647 L_CONVERSION_RATE := 1;
648 ELSE
649 IF CONVERSION_RATE IS NULL THEN
650 L_CONVERSION_RATE := GET_RATE(P_SOB_ID
651 ,CURRENCY1
652 ,ORDER_DATE
653 ,CONVERSION_TYPE_CODE);
654 ELSE
655 L_CONVERSION_RATE := CONVERSION_RATE;
656 END IF;
657 END IF;
658 RETURN (NVL(L_CONVERSION_RATE
659 ,0) * ROUND(NVL(SHIP_VALUE
660 ,0)
661 ,2));
662 END IF;
663 EXCEPTION
664 WHEN NO_DATA_FOUND THEN
665 RETURN (0);
666 WHEN OTHERS THEN
667 RETURN (0);
668 END;
669 END C_SHIPPED_AMOUNTFORMULA;
670
671 FUNCTION CF_1FORMULA(CHARGE_PERIODICITY_CODE IN VARCHAR2) RETURN CHAR IS
672 L_UOM_CLASS VARCHAR2(50) := FND_PROFILE.VALUE('ONT_UOM_CLASS_CHARGE_PERIODICITY');
673 L_CHARGE_PERIODICITY VARCHAR2(25);
674 BEGIN
675 IF CHARGE_PERIODICITY_CODE IS NOT NULL THEN
676 SELECT
677 UNIT_OF_MEASURE
678 INTO L_CHARGE_PERIODICITY
679 FROM
680 MTL_UNITS_OF_MEASURE_VL
681 WHERE UOM_CLASS = L_UOM_CLASS
682 AND UOM_CODE = CHARGE_PERIODICITY_CODE;
683 RETURN L_CHARGE_PERIODICITY;
684 ELSE
685 RETURN (P_ONE_TIME);
686 END IF;
687 EXCEPTION
688 WHEN NO_DATA_FOUND THEN
689 RETURN NULL;
690 END CF_1FORMULA;
691
692 FUNCTION C_CURRENCYFORMULA(CURRENCY1 IN VARCHAR2) RETURN CHAR IS
693 BEGIN
694 IF P_USE_FUNCTIONAL_CURRENCY = 'N' THEN
695 RETURN (CURRENCY1);
696 ELSE
697 RETURN (RP_FUNCTIONAL_CURRENCY);
698 END IF;
699 END C_CURRENCYFORMULA;
700
701 FUNCTION RP_REPORT_NAME_P RETURN VARCHAR2 IS
702 BEGIN
703 RETURN RP_REPORT_NAME;
704 END RP_REPORT_NAME_P;
705
706 FUNCTION RP_SUB_TITLE_P RETURN VARCHAR2 IS
707 BEGIN
708 RETURN RP_SUB_TITLE;
709 END RP_SUB_TITLE_P;
710
711 FUNCTION RP_COMPANY_NAME_P RETURN VARCHAR2 IS
712 BEGIN
713 RETURN RP_COMPANY_NAME;
714 END RP_COMPANY_NAME_P;
715
716 FUNCTION RP_FUNCTIONAL_CURRENCY_P RETURN VARCHAR2 IS
717 BEGIN
718 RETURN RP_FUNCTIONAL_CURRENCY;
719 END RP_FUNCTIONAL_CURRENCY_P;
720
721 FUNCTION RP_DATA_FOUND_P RETURN VARCHAR2 IS
722 BEGIN
723 RETURN RP_DATA_FOUND;
724 END RP_DATA_FOUND_P;
725
726 FUNCTION RP_ORDER_NUMBER_RANGE_P RETURN VARCHAR2 IS
727 BEGIN
728 RETURN RP_ORDER_NUMBER_RANGE;
729 END RP_ORDER_NUMBER_RANGE_P;
730
731 FUNCTION RP_SALESREP_RANGE_P RETURN VARCHAR2 IS
732 BEGIN
733 RETURN RP_SALESREP_RANGE;
734 END RP_SALESREP_RANGE_P;
735
736 FUNCTION RP_CUSTOMER_NAME_RANGE_P RETURN VARCHAR2 IS
737 BEGIN
738 RETURN RP_CUSTOMER_NAME_RANGE;
739 END RP_CUSTOMER_NAME_RANGE_P;
740
741 FUNCTION RP_ORDER_TYPE_RANGE_P RETURN VARCHAR2 IS
742 BEGIN
743 RETURN RP_ORDER_TYPE_RANGE;
744 END RP_ORDER_TYPE_RANGE_P;
745
746 FUNCTION RP_OPEN_ORDERS_ONLY_P RETURN VARCHAR2 IS
747 BEGIN
748 RETURN RP_OPEN_ORDERS_ONLY;
749 END RP_OPEN_ORDERS_ONLY_P;
750
751 FUNCTION RP_PO_NUMBER_RANGE_P RETURN VARCHAR2 IS
752 BEGIN
753 RETURN RP_PO_NUMBER_RANGE;
754 END RP_PO_NUMBER_RANGE_P;
755
756 FUNCTION RP_ORDER_DATE_RANGE_P RETURN VARCHAR2 IS
757 BEGIN
758 RETURN RP_ORDER_DATE_RANGE;
759 END RP_ORDER_DATE_RANGE_P;
760
761 FUNCTION RP_ORDER_STATUS_RANGE_P RETURN VARCHAR2 IS
762 BEGIN
763 RETURN RP_ORDER_STATUS_RANGE;
764 END RP_ORDER_STATUS_RANGE_P;
765
766 FUNCTION RP_COUNTRY_RANGE_P RETURN VARCHAR2 IS
767 BEGIN
768 RETURN RP_COUNTRY_RANGE;
769 END RP_COUNTRY_RANGE_P;
770
771 FUNCTION RP_CREATED_BY_RANGE_P RETURN VARCHAR2 IS
772 BEGIN
773 RETURN RP_CREATED_BY_RANGE;
774 END RP_CREATED_BY_RANGE_P;
775
776 FUNCTION RP_ORDER_BY_P RETURN VARCHAR2 IS
777 BEGIN
778 RETURN RP_ORDER_BY;
779 END RP_ORDER_BY_P;
780
781 FUNCTION RP_AGREEMENT_P RETURN VARCHAR2 IS
782 BEGIN
783 RETURN RP_AGREEMENT;
784 END RP_AGREEMENT_P;
785
786 FUNCTION IS_FIXED_RATE(X_FROM_CURRENCY IN VARCHAR2
787 ,X_TO_CURRENCY IN VARCHAR2
788 ,X_EFFECTIVE_DATE IN DATE) RETURN VARCHAR2 IS
789 X0 VARCHAR2(2000);
790 BEGIN
791 begin X0 := GL_CURRENCY_API.IS_FIXED_RATE(X_FROM_CURRENCY, X_TO_CURRENCY, X_EFFECTIVE_DATE);
792 end;
793 /*STPROC.INIT('begin :X0 := GL_CURRENCY_API.IS_FIXED_RATE(:X_FROM_CURRENCY, :X_TO_CURRENCY, :X_EFFECTIVE_DATE); end;');
794 STPROC.BIND_O(X0);
795 STPROC.BIND_I(X_FROM_CURRENCY);
796 STPROC.BIND_I(X_TO_CURRENCY);
797 STPROC.BIND_I(X_EFFECTIVE_DATE);
798 STPROC.EXECUTE;
799 STPROC.RETRIEVE(1
800 ,X0);*/
801 RETURN X0;
802 END IS_FIXED_RATE;
803
804 PROCEDURE GET_RELATION(X_FROM_CURRENCY IN VARCHAR2
805 ,X_TO_CURRENCY IN VARCHAR2
806 ,X_EFFECTIVE_DATE IN DATE
807 ,X_FIXED_RATE IN OUT NOCOPY BOOLEAN
808 ,X_RELATIONSHIP IN OUT NOCOPY VARCHAR2) IS
809 BEGIN
810
811 /*
812
813 STPROC.BIND_IO(X_FIXED_RATE);
814 STPROC.BIND_I(X_FROM_CURRENCY);
815 STPROC.BIND_I(X_TO_CURRENCY);
816 STPROC.BIND_I(X_EFFECTIVE_DATE);
817 STPROC.BIND_IO(X_RELATIONSHIP);
818 STPROC.EXECUTE;
819 STPROC.RETRIEVE(1
820 ,X_FIXED_RATE);
821 STPROC.RETRIEVE(5
822 ,X_RELATIONSHIP);*/null;
823 END GET_RELATION;
824
825 FUNCTION GET_EURO_CODE RETURN VARCHAR2 IS
826 X0 VARCHAR2(2000);
827 BEGIN
828 begin
829 X0 := GL_CURRENCY_API.GET_EURO_CODE;
830 end;
831 /*STPROC.INIT('begin :X0 := GL_CURRENCY_API.GET_EURO_CODE; end;');
832 STPROC.BIND_O(X0);
833 STPROC.EXECUTE;
834 STPROC.RETRIEVE(1
835 ,X0);*/
836 RETURN X0;
837 END GET_EURO_CODE;
838
839 FUNCTION GET_RATE(X_FROM_CURRENCY IN VARCHAR2
840 ,X_TO_CURRENCY IN VARCHAR2
841 ,X_CONVERSION_DATE IN DATE
842 ,X_CONVERSION_TYPE IN VARCHAR2) RETURN NUMBER IS
843 X0 NUMBER;
844 BEGIN
845 /* STPROC.INIT('begin :X0 := GL_CURRENCY_API.GET_RATE(:X_FROM_CURRENCY, :X_TO_CURRENCY, :X_CONVERSION_DATE, :X_CONVERSION_TYPE); end;');
846 STPROC.BIND_O(X0);
847 STPROC.BIND_I(X_FROM_CURRENCY);
848 STPROC.BIND_I(X_TO_CURRENCY);
849 STPROC.BIND_I(X_CONVERSION_DATE);
850 STPROC.BIND_I(X_CONVERSION_TYPE);
851 STPROC.EXECUTE;
852 STPROC.RETRIEVE(1
853 ,X0);*/null;
854 RETURN X0;
855 END GET_RATE;
856
857 FUNCTION GET_RATE(X_SET_OF_BOOKS_ID IN NUMBER
858 ,X_FROM_CURRENCY IN VARCHAR2
859 ,X_CONVERSION_DATE IN DATE
860 ,X_CONVERSION_TYPE IN VARCHAR2) RETURN NUMBER IS
861 X0 NUMBER;
862 BEGIN
863 /*STPROC.INIT('begin :X0 := GL_CURRENCY_API.GET_RATE(:X_SET_OF_BOOKS_ID, :X_FROM_CURRENCY, :X_CONVERSION_DATE, :X_CONVERSION_TYPE); end;');
864 STPROC.BIND_O(X0);
865 STPROC.BIND_I(X_SET_OF_BOOKS_ID);
866 STPROC.BIND_I(X_FROM_CURRENCY);
867 STPROC.BIND_I(X_CONVERSION_DATE);
868 STPROC.BIND_I(X_CONVERSION_TYPE);
869 STPROC.EXECUTE;
870 STPROC.RETRIEVE(1
871 ,X0);*/null;
872 RETURN X0;
873 END GET_RATE;
874
875 FUNCTION CONVERT_AMOUNT(X_FROM_CURRENCY IN VARCHAR2
876 ,X_TO_CURRENCY IN VARCHAR2
877 ,X_CONVERSION_DATE IN DATE
878 ,X_CONVERSION_TYPE IN VARCHAR2
879 ,X_AMOUNT IN NUMBER) RETURN NUMBER IS
880 X0 NUMBER;
881 BEGIN
882 /*STPROC.INIT('begin :X0 := GL_CURRENCY_API.CONVERT_AMOUNT(:X_FROM_CURRENCY, :X_TO_CURRENCY, :X_CONVERSION_DATE, :X_CONVERSION_TYPE, :X_AMOUNT); end;');
883 STPROC.BIND_O(X0);
884 STPROC.BIND_I(X_FROM_CURRENCY);
885 STPROC.BIND_I(X_TO_CURRENCY);
886 STPROC.BIND_I(X_CONVERSION_DATE);
887 STPROC.BIND_I(X_CONVERSION_TYPE);
888 STPROC.BIND_I(X_AMOUNT);
889 STPROC.EXECUTE;
890 STPROC.RETRIEVE(1
891 ,X0);*/ null;
892 RETURN X0;
893 END CONVERT_AMOUNT;
894
895 FUNCTION CONVERT_AMOUNT(X_SET_OF_BOOKS_ID IN NUMBER
896 ,X_FROM_CURRENCY IN VARCHAR2
897 ,X_CONVERSION_DATE IN DATE
898 ,X_CONVERSION_TYPE IN VARCHAR2
899 ,X_AMOUNT IN NUMBER) RETURN NUMBER IS
900 X0 NUMBER;
901 BEGIN
902 /*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;');
903 STPROC.BIND_O(X0);
904 STPROC.BIND_I(X_SET_OF_BOOKS_ID);
905 STPROC.BIND_I(X_FROM_CURRENCY);
906 STPROC.BIND_I(X_CONVERSION_DATE);
907 STPROC.BIND_I(X_CONVERSION_TYPE);
908 STPROC.BIND_I(X_AMOUNT);
909 STPROC.EXECUTE;
910 STPROC.RETRIEVE(1
911 ,X0);*/ null;
912 RETURN X0;
913 END CONVERT_AMOUNT;
914
915 FUNCTION GET_DERIVE_TYPE(SOB_ID IN NUMBER
916 ,PERIOD IN VARCHAR2
917 ,CURR_CODE IN VARCHAR2) RETURN VARCHAR2 IS
918 X0 VARCHAR2(2000);
919 BEGIN
920 /*STPROC.INIT('begin :X0 := GL_CURRENCY_API.GET_DERIVE_TYPE(:SOB_ID, :PERIOD, :CURR_CODE); end;');
921 STPROC.BIND_O(X0);
922 STPROC.BIND_I(SOB_ID);
923 STPROC.BIND_I(PERIOD);
924 STPROC.BIND_I(CURR_CODE);
925 STPROC.EXECUTE;
926 STPROC.RETRIEVE(1
927 ,X0);*/null;
928 RETURN X0;
929 END GET_DERIVE_TYPE;
930
931 END ONT_OEXOEOSR_XMLP_PKG;
932
933
[Home] [Help]