1 PACKAGE BODY ONT_OEXPRPRS_XMLP_PKG AS
2 /* $Header: OEXPRPRSB.pls 120.2 2008/01/04 07:26:26 nchinnam noship $ */
3 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
4 BEGIN
5 BEGIN
6 BEGIN
7 F1:=Oe_Sys_Parameters.Value('RECURRING_CHARGES',mo_global.get_current_org_id());
8 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
9 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
10 /*SRW.MESSAGE(5000
11 ,'Changed Report is running')*/NULL;
12 EXCEPTION
13 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
14 /*SRW.MESSAGE(1000
15 ,'Failed in BEFORE REPORT trigger')*/NULL;
16 RETURN (FALSE);
17 END;
18 BEGIN
19 P_ORGANIZATION_ID := MO_GLOBAL.GET_CURRENT_ORG_ID;
20 END;
21 DECLARE
22 L_COMPANY_NAME VARCHAR2(100);
23 L_FUNCTIONAL_CURRENCY VARCHAR2(15);
24 BEGIN
25 SELECT
26 SOB.NAME,
27 SOB.CURRENCY_CODE
28 INTO L_COMPANY_NAME,L_FUNCTIONAL_CURRENCY
29 FROM
30 GL_SETS_OF_BOOKS SOB,
31 FND_CURRENCIES CUR
32 WHERE SOB.SET_OF_BOOKS_ID = P_SOB_ID
33 AND SOB.CURRENCY_CODE = CUR.CURRENCY_CODE;
34 RP_COMPANY_NAME := L_COMPANY_NAME;
35 RP_FUNCTIONAL_CURRENCY := L_FUNCTIONAL_CURRENCY;
36 EXCEPTION
37 WHEN NO_DATA_FOUND THEN
38 NULL;
39 END;
40 DECLARE
41 L_REPORT_NAME VARCHAR2(240);
42 BEGIN
43 SELECT
44 CP.USER_CONCURRENT_PROGRAM_NAME
45 INTO L_REPORT_NAME
46 FROM
47 FND_CONCURRENT_PROGRAMS_VL CP,
48 FND_CONCURRENT_REQUESTS CR
49 WHERE CR.REQUEST_ID = P_CONC_REQUEST_ID
50 AND CP.APPLICATION_ID = CR.PROGRAM_APPLICATION_ID
51 AND CP.CONCURRENT_PROGRAM_ID = CR.CONCURRENT_PROGRAM_ID;
52 L_REPORT_NAME := substr(L_REPORT_NAME,1,instr(L_REPORT_NAME,' (XML)'));
53 RP_REPORT_NAME := L_REPORT_NAME;
54 EXCEPTION
55 WHEN NO_DATA_FOUND THEN
56 RP_REPORT_NAME := 'Pricing Report - Summary ';
57 END;
58 DECLARE
59 L_ORDER_DATE_LOW VARCHAR2(50);
60 L_ORDER_DATE_HIGH VARCHAR2(50);
61 L_ORDER_AMOUNT_LOW VARCHAR2(50);
62 L_ORDER_AMOUNT_HIGH VARCHAR2(50);
63 L_ORDER_LIST_LOW VARCHAR2(50);
64 L_ORDER_LIST_HIGH VARCHAR2(50);
65 L_ORDER_NUMBER_LOW VARCHAR2(50);
66 L_ORDER_NUMBER_HIGH VARCHAR2(50);
67 L_ORDER_TYPE_LOW VARCHAR2(50);
68 L_ORDER_TYPE_HIGH VARCHAR2(50);
69 L_SALESREP_LOW VARCHAR2(50);
70 L_SALESREP_HIGH VARCHAR2(50);
71 L_CUSTOMER_NUMBER_LOW VARCHAR2(50);
72 L_CUSTOMER_NUMBER_HIGH VARCHAR2(50);
73 L_CUSTOMER_NAME_LOW VARCHAR2(50);
74 L_CUSTOMER_NAME_HIGH VARCHAR2(50);
75 BEGIN
76 IF (P_ORDER_DATE_LOW IS NULL) AND (P_ORDER_DATE_HIGH IS NULL) THEN
77 NULL;
78 ELSE
79 IF P_ORDER_DATE_LOW IS NULL THEN
80 L_ORDER_DATE_LOW := ' ';
81 ELSE
82 L_ORDER_DATE_LOW := TO_CHAR(P_ORDER_DATE_LOW
83 ,'DD-MON-YYYY');
84 END IF;
85 IF P_ORDER_DATE_HIGH IS NULL THEN
86 L_ORDER_DATE_HIGH := ' ';
87 ELSE
88 L_ORDER_DATE_HIGH := TO_CHAR(P_ORDER_DATE_HIGH
89 ,'DD-MON-YYYY');
90 END IF;
91 RP_ORDER_DATE_RANGE := 'From ' || L_ORDER_DATE_LOW || ' To ' || L_ORDER_DATE_HIGH;
92 END IF;
93 IF (P_ORDER_AMOUNT_LOW IS NULL) AND (P_ORDER_AMOUNT_HIGH IS NULL) THEN
94 NULL;
95 ELSE
96 IF P_ORDER_AMOUNT_LOW IS NULL THEN
97 L_ORDER_AMOUNT_LOW := ' ';
98 ELSE
99 L_ORDER_AMOUNT_LOW := SUBSTR(TO_CHAR(P_ORDER_AMOUNT_LOW)
100 ,1
101 ,18);
102 END IF;
103 IF P_ORDER_AMOUNT_HIGH IS NULL THEN
104 L_ORDER_AMOUNT_HIGH := ' ';
105 ELSE
106 L_ORDER_AMOUNT_HIGH := SUBSTR(TO_CHAR(P_ORDER_AMOUNT_HIGH)
107 ,1
108 ,18);
109 END IF;
110 RP_ORDER_AMOUNT_RANGE := 'From ' || L_ORDER_AMOUNT_LOW || ' To ' || L_ORDER_AMOUNT_HIGH;
111 END IF;
112 IF (P_ORDER_LIST_LOW IS NULL) AND (P_ORDER_LIST_HIGH IS NULL) THEN
113 NULL;
114 ELSE
115 IF P_ORDER_LIST_LOW IS NULL THEN
116 L_ORDER_LIST_LOW := ' ';
117 ELSE
118 L_ORDER_LIST_LOW := SUBSTR(TO_CHAR(P_ORDER_LIST_LOW)
119 ,1
120 ,18);
121 END IF;
122 IF P_ORDER_LIST_HIGH IS NULL THEN
123 L_ORDER_LIST_HIGH := ' ';
124 ELSE
125 L_ORDER_LIST_HIGH := SUBSTR(TO_CHAR(P_ORDER_LIST_HIGH)
126 ,1
127 ,18);
128 END IF;
129 RP_ORDER_LIST_RANGE := 'From ' || L_ORDER_LIST_LOW || ' To ' || L_ORDER_LIST_HIGH;
130 END IF;
131 IF (P_ORDER_NUM_LOW IS NULL) AND (P_ORDER_NUM_HIGH IS NULL) THEN
132 NULL;
133 ELSE
134 IF P_ORDER_NUM_LOW IS NULL THEN
135 L_ORDER_NUMBER_LOW := ' ';
136 ELSE
137 L_ORDER_NUMBER_LOW := SUBSTR(P_ORDER_NUM_LOW
138 ,1
139 ,18);
140 END IF;
141 IF P_ORDER_NUM_HIGH IS NULL THEN
142 L_ORDER_NUMBER_HIGH := ' ';
143 ELSE
144 L_ORDER_NUMBER_HIGH := SUBSTR((P_ORDER_NUM_HIGH)
145 ,1
146 ,18);
147 END IF;
148 RP_ORDER_NUMBER_RANGE := 'From ' || L_ORDER_NUMBER_LOW || ' To ' || L_ORDER_NUMBER_HIGH;
149 END IF;
150 IF (P_CUSTOMER_NUMBER_LO IS NULL) AND (P_CUSTOMER_NUMBER_HI IS NULL) THEN
151 NULL;
152 ELSE
153 IF P_CUSTOMER_NUMBER_LO IS NULL THEN
154 L_CUSTOMER_NUMBER_LOW := ' ';
155 ELSE
156 L_CUSTOMER_NUMBER_LOW := SUBSTR(P_CUSTOMER_NUMBER_LO
157 ,1
158 ,18);
159 END IF;
160 IF P_CUSTOMER_NUMBER_HI IS NULL THEN
161 L_CUSTOMER_NUMBER_HIGH := ' ';
162 ELSE
163 L_CUSTOMER_NUMBER_HIGH := SUBSTR((P_CUSTOMER_NUMBER_HI)
164 ,1
165 ,18);
166 END IF;
167 RP_CUSTOMER_NUMBER_RANGE := 'From ' || L_CUSTOMER_NUMBER_LOW || ' To ' || L_CUSTOMER_NUMBER_HIGH;
168 END IF;
169 IF (P_CUSTOMER_NAME_LO IS NULL) AND (P_CUSTOMER_NAME_HI IS NULL) THEN
170 NULL;
171 ELSE
172 IF P_CUSTOMER_NAME_LO IS NULL THEN
173 L_CUSTOMER_NAME_LOW := ' ';
174 ELSE
175 L_CUSTOMER_NAME_LOW := SUBSTR(P_CUSTOMER_NAME_LO
176 ,1
177 ,18);
178 END IF;
179 IF P_CUSTOMER_NAME_HI IS NULL THEN
180 L_CUSTOMER_NAME_HIGH := ' ';
181 ELSE
182 L_CUSTOMER_NAME_HIGH := SUBSTR((P_CUSTOMER_NAME_HI)
183 ,1
184 ,18);
185 END IF;
186 RP_CUSTOMER_NAME_RANGE := 'From ' || L_CUSTOMER_NAME_LOW || ' To ' || L_CUSTOMER_NAME_HIGH;
187 END IF;
188 IF (P_ORDER_TYPE_LO IS NULL) AND (P_ORDER_TYPE_HI IS NULL) THEN
189 NULL;
190 ELSE
191 IF P_ORDER_TYPE_LO IS NULL THEN
192 L_ORDER_TYPE_LOW := ' ';
193 ELSE
194 L_ORDER_TYPE_LOW := SUBSTR(L_ORDER_TYPE_LOW
195 ,1
196 ,18);
197 END IF;
198 IF P_ORDER_TYPE_HI IS NULL THEN
199 L_ORDER_TYPE_HIGH := ' ';
200 ELSE
201 L_ORDER_TYPE_HIGH := SUBSTR((L_ORDER_TYPE_HIGH)
202 ,1
203 ,18);
204 END IF;
205 RP_ORDER_TYPE_RANGE := 'From ' || L_ORDER_TYPE_LOW || ' To ' || L_ORDER_TYPE_HIGH;
206 END IF;
207 IF (P_SALESREP_LO IS NULL) AND (P_SALESREP_HI IS NULL) THEN
208 NULL;
209 ELSE
210 IF P_SALESREP_LO IS NULL THEN
211 L_SALESREP_LOW := ' ';
212 ELSE
213 L_SALESREP_LOW := SUBSTR(P_SALESREP_LO
214 ,1
215 ,18);
216 END IF;
217 IF P_SALESREP_HI IS NULL THEN
218 L_SALESREP_HIGH := ' ';
219 ELSE
220 L_SALESREP_HIGH := SUBSTR((P_SALESREP_HI)
221 ,1
222 ,18);
223 END IF;
224 RP_SALESREP_RANGE := 'From ' || L_SALESREP_LOW || ' To ' || L_SALESREP_HIGH;
225 END IF;
226 END;
227 DECLARE
228 L_MEANING VARCHAR2(80);
229 L_LOOKUP_TYPE VARCHAR2(80);
230 BEGIN
231 L_LOOKUP_TYPE := 'YES_NO';
232 SELECT
233 MEANING
234 INTO L_MEANING
235 FROM
236 FND_LOOKUPS
237 WHERE LOOKUP_TYPE = L_LOOKUP_TYPE
238 AND LOOKUP_CODE = SUBSTR(UPPER(P_OPEN_ORDERS_ONLY)
239 ,1
240 ,1);
241 RP_OPEN_ORDERS_ONLY := L_MEANING;
242 EXCEPTION
243 WHEN NO_DATA_FOUND THEN
244 RP_OPEN_ORDERS_ONLY := 'Yes';
245 END;
246 DECLARE
247 L_MEANING VARCHAR2(80);
248 L_LOOKUP_TYPE VARCHAR2(80);
249 L_LOOKUP_CODE VARCHAR2(80);
250 BEGIN
251 L_LOOKUP_TYPE := 'REPORT_ORDER_CATEGORY';
252 L_LOOKUP_CODE := P_ORDER_CATEGORY;
253 SELECT
254 MEANING
255 INTO L_MEANING
256 FROM
257 OE_LOOKUPS
258 WHERE LOOKUP_TYPE = L_LOOKUP_TYPE
259 AND LOOKUP_CODE = L_LOOKUP_CODE;
260 RP_ORDER_CATEGORY := L_MEANING;
261 EXCEPTION
262 WHEN NO_DATA_FOUND THEN
263 RP_ORDER_CATEGORY := 'All Orders';
264 END;
265 DECLARE
266 L_MEANING VARCHAR2(80);
267 L_LOOKUP_TYPE VARCHAR2(80);
268 L_LOOKUP_CODE VARCHAR2(80);
269 BEGIN
270 L_LOOKUP_TYPE := 'REPORT_LINE_DISPLAY';
271 L_LOOKUP_CODE := P_LINE_CATEGORY;
272 SELECT
273 MEANING
274 INTO L_MEANING
275 FROM
276 OE_LOOKUPS
277 WHERE LOOKUP_TYPE = L_LOOKUP_TYPE
278 AND LOOKUP_CODE = L_LOOKUP_CODE;
279 RP_LINE_CATEGORY := L_MEANING;
280 EXCEPTION
281 WHEN NO_DATA_FOUND THEN
282 RP_LINE_CATEGORY := 'All Lines';
283 END;
284 EXCEPTION
285 WHEN OTHERS THEN
286 /*SRW.MESSAGE(4000
287 ,' Error in Before Report Trigger')*/NULL;
288 END;
289 DECLARE
290 L_AGREEMENT_NAME VARCHAR2(240);
291 BEGIN
292 IF (P_AGREEMENT IS NOT NULL) THEN
293 BEGIN
294 SELECT
295 NAME
296 INTO L_AGREEMENT_NAME
297 FROM
298 OE_AGREEMENTS
299 WHERE AGREEMENT_ID = P_AGREEMENT;
300 RP_AGREEMENT_NAME := L_AGREEMENT_NAME;
301 EXCEPTION
302 WHEN NO_DATA_FOUND THEN
303 RP_AGREEMENT_NAME := NULL;
304 END;
305 END IF;
306 END;
307 RETURN (TRUE);
308 END BEFOREREPORT;
309 FUNCTION AFTERREPORT RETURN BOOLEAN IS
310 BEGIN
311 BEGIN
312 EXECUTE IMMEDIATE
313 'alter session set sql_trace=false';
314 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
315 EXCEPTION
316 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
317 /*SRW.MESSAGE(1
318 ,'Failed in AFTER REPORT TRIGGER')*/NULL;
319 RETURN (FALSE);
320 END;
321 RETURN (TRUE);
322 END AFTERREPORT;
323 FUNCTION AFTERPFORM RETURN BOOLEAN IS
324 BEGIN
325 BEGIN
326 IF (P_CUSTOMER_NAME_LO IS NOT NULL) AND (P_CUSTOMER_NAME_HI IS NOT NULL) THEN
327 LP_CUSTOMER_NAME := 'and ( PARTY.PARTY_NAME between :p_customer_name_lo and :p_customer_name_hi ) ';
328 ELSIF (P_CUSTOMER_NAME_LO IS NOT NULL) THEN
329 LP_CUSTOMER_NAME := 'and PARTY.PARTY_NAME >= :p_customer_name_lo ';
330 ELSIF (P_CUSTOMER_NAME_HI IS NOT NULL) THEN
331 LP_CUSTOMER_NAME := 'and PARTY.PARTY_NAME <= :p_customer_name_hi ';
332 END IF;
333 IF (P_CUSTOMER_NUMBER_LO IS NOT NULL) AND (P_CUSTOMER_NUMBER_HI IS NOT NULL) THEN
334 LP_CUSTOMER_NUMBER := 'and ( CUST_ACCT.ACCOUNT_NUMBER between :p_customer_number_lo and :p_customer_number_hi ) ';
335 ELSIF (P_CUSTOMER_NUMBER_LO IS NOT NULL) THEN
336 LP_CUSTOMER_NUMBER := 'and CUST_ACCT.ACCOUNT_NUMBER >= :p_customer_number_lo ';
337 ELSIF (P_CUSTOMER_NUMBER_HI IS NOT NULL) THEN
338 LP_CUSTOMER_NUMBER := 'and CUST_ACCT.ACCOUNT_NUMBER <= :p_customer_number_hi ';
339 END IF;
340 IF (P_AGREEMENT IS NOT NULL) THEN
341 LP_AGREEMENT := 'and ag.agreement_id = :p_agreement ';
342 END IF;
343 IF (P_SALESREP_LO IS NOT NULL) AND (P_SALESREP_HI IS NOT NULL) THEN
344 LP_SALESREP := 'and ( sr.name between :p_salesrep_lo and :p_salesrep_hi ) ';
345 ELSIF (P_SALESREP_LO IS NOT NULL) THEN
346 LP_SALESREP := 'and sr.name >= :p_salesrep_lo ';
347 ELSIF (P_SALESREP_HI IS NOT NULL) THEN
348 LP_SALESREP := 'and sr.name <= :p_salesrep_hi ';
349 END IF;
350 IF (P_ORDER_TYPE_LO IS NOT NULL) AND (P_ORDER_TYPE_HI IS NOT NULL) THEN
351 LP_ORDER_TYPE := 'and ( ot.transaction_type_id between :p_order_type_lo and :p_order_type_hi ) ';
352 SELECT
353 OEOT.NAME
354 INTO L_ORDER_TYPE_LOW
355 FROM
356 OE_TRANSACTION_TYPES_TL OEOT
357 WHERE OEOT.TRANSACTION_TYPE_ID = P_ORDER_TYPE_LO
358 AND OEOT.LANGUAGE = USERENV('LANG');
359 SELECT
360 OEOT.NAME
361 INTO L_ORDER_TYPE_HIGH
362 FROM
363 OE_TRANSACTION_TYPES_TL OEOT
364 WHERE OEOT.TRANSACTION_TYPE_ID = P_ORDER_TYPE_HI
365 AND OEOT.LANGUAGE = USERENV('LANG');
366 ELSIF (P_ORDER_TYPE_LO IS NOT NULL) THEN
367 LP_ORDER_TYPE := 'and ot.transaction_type_id >= :p_order_type_lo ';
368 SELECT
369 OEOT.NAME
370 INTO L_ORDER_TYPE_LOW
371 FROM
372 OE_TRANSACTION_TYPES_TL OEOT
373 WHERE OEOT.TRANSACTION_TYPE_ID = P_ORDER_TYPE_LO
374 AND OEOT.LANGUAGE = USERENV('LANG');
375 ELSIF (P_ORDER_TYPE_HI IS NOT NULL) THEN
376 LP_ORDER_TYPE := 'and ot.transaction_type_id <= :p_order_type_hi ';
377 SELECT
378 OEOT.NAME
379 INTO L_ORDER_TYPE_HIGH
380 FROM
381 OE_TRANSACTION_TYPES_TL OEOT
382 WHERE OEOT.TRANSACTION_TYPE_ID = P_ORDER_TYPE_HI
383 AND OEOT.LANGUAGE = USERENV('LANG');
384 END IF;
385 IF (P_ORDER_NUM_LOW IS NOT NULL) AND (P_ORDER_NUM_HIGH IS NOT NULL) THEN
386 LP_ORDER_NUM := 'and ( h.order_number between to_number(:p_order_num_low) and to_number(:p_order_num_high) ) ';
387 ELSIF (P_ORDER_NUM_LOW IS NOT NULL) THEN
388 LP_ORDER_NUM := 'and h.order_number >= to_number(:p_order_num_low) ';
389 ELSIF (P_ORDER_NUM_HIGH IS NOT NULL) THEN
390 LP_ORDER_NUM := 'and h.order_number <= to_number(:p_order_num_high) ';
391 END IF;
392 IF (P_ORDER_DATE_LOW IS NOT NULL) AND (P_ORDER_DATE_HIGH IS NOT NULL) THEN
393 LP_ORDER_DATE := 'and (h.ordered_date between :p_order_date_low
394 and :p_order_date_high) ';
395 ELSIF (P_ORDER_DATE_LOW IS NOT NULL) THEN
396 LP_ORDER_DATE := 'and h.ordered_date >= :p_order_date_low ';
397 ELSIF (P_ORDER_DATE_HIGH IS NOT NULL) THEN
398 LP_ORDER_DATE := 'and h.ordered_date <= :p_order_date_high ';
399 END IF;
400 IF ((P_ORDER_AMOUNT_LOW IS NULL) AND (P_ORDER_AMOUNT_HIGH IS NULL) AND (P_ORDER_LIST_LOW IS NULL) AND (P_ORDER_LIST_HIGH IS NULL)) THEN
401 NULL;
402 ELSE
403 IF (P_ORDER_AMOUNT_LOW IS NOT NULL) AND (P_ORDER_AMOUNT_HIGH IS NOT NULL) THEN
404 LP_ORDER_AMOUNT := ' sum(nvl(l.ordered_quantity,0)*
405 nvl(l.unit_selling_price,0)) between :p_order_amount_low and :p_order_amount_high';
406 ELSIF (P_ORDER_AMOUNT_LOW IS NOT NULL) THEN
407 LP_ORDER_AMOUNT := ' sum(nvl(l.ordered_quantity,0)*
408 nvl(l.unit_selling_price,0)) >= :p_order_amount_low ';
409 ELSIF (P_ORDER_AMOUNT_HIGH IS NOT NULL) THEN
410 LP_ORDER_AMOUNT := ' sum(nvl(l.ordered_quantity,0)*
411 nvl(l.unit_selling_price,0)) <= :p_order_amount_high ';
412 END IF;
413 IF (P_ORDER_LIST_LOW IS NOT NULL) AND (P_ORDER_LIST_HIGH IS NOT NULL) THEN
414 LP_ORDER_LIST := ' sum(nvl(l.ordered_quantity,0)*
415 nvl(l.unit_list_price,0)) between :p_order_list_low and :p_order_list_high';
416 ELSIF (P_ORDER_LIST_LOW IS NOT NULL) THEN
417 LP_ORDER_LIST := ' sum(nvl(l.ordered_quantity,0)*
418 nvl(l.unit_list_price,0)) >= :p_order_list_low ';
419 ELSIF (P_ORDER_LIST_HIGH IS NOT NULL) THEN
420 LP_ORDER_LIST := ' sum(nvl(l.ordered_quantity,0)*
421 nvl(l.unit_list_price,0)) <= :p_order_list_high ';
422 END IF;
423 IF (LP_ORDER_AMOUNT IS NOT NULL) AND (LP_ORDER_LIST IS NOT NULL) THEN
424 LP_HAVING := ' having ' || LP_ORDER_AMOUNT || ' and ' || LP_ORDER_LIST;
425 ELSIF (LP_ORDER_AMOUNT IS NOT NULL) AND (LP_ORDER_LIST IS NULL) THEN
426 LP_HAVING := ' having ' || LP_ORDER_AMOUNT;
427 ELSIF (LP_ORDER_AMOUNT IS NULL) AND (LP_ORDER_LIST IS NOT NULL) THEN
428 LP_HAVING := ' having ' || LP_ORDER_LIST;
429 END IF;
430 END IF;
431 IF P_OPEN_ORDERS_ONLY = 'Y' THEN
432 LP_OPEN_ORDERS_ONLY := 'and nvl(h.open_flag,''N'') = ''Y'' ';
433 END IF;
434 IF P_ORDER_NUM_LOW = P_ORDER_NUM_HIGH THEN
435 NULL;
436 ELSE
437 IF P_ORDER_CATEGORY IS NOT NULL THEN
438 IF P_ORDER_CATEGORY = 'SALES' THEN
439 LP_ORDER_CATEGORY := 'and h.order_category_code in (''ORDER'', ''MIXED'') ';
440 ELSIF P_ORDER_CATEGORY = 'CREDIT' THEN
441 LP_ORDER_CATEGORY := 'and h.order_category_code in (''RETURN'', ''MIXED'') ';
442 ELSIF P_ORDER_CATEGORY = 'ALL' THEN
443 LP_ORDER_CATEGORY := ' ';
444 END IF;
445 END IF;
446 END IF;
447 IF P_LINE_CATEGORY IS NOT NULL THEN
448 IF P_LINE_CATEGORY = 'SALES' THEN
449 LP_LINE_CATEGORY := 'and l.line_category_code = ''ORDER'' ';
450 ELSIF P_LINE_CATEGORY = 'CREDIT' THEN
451 LP_LINE_CATEGORY := 'and l.line_category_code = ''RETURN'' ';
452 ELSIF P_LINE_CATEGORY = 'ALL' THEN
453 LP_LINE_CATEGORY := ' ';
454 END IF;
455 ELSE
456 LP_LINE_CATEGORY := ' ';
457 END IF;
458 IF (P_ORDER_BY IS NOT NULL) THEN
459 IF (P_ORDER_BY = 'CUSTOMER') THEN
460 LP_SORT_BY := ', PARTY.PARTY_NAME ';
461 ELSIF (P_ORDER_BY = 'ORDER_NUMBER') THEN
462 LP_SORT_BY := ', h.order_number ';
463 END IF;
464 ELSE
465 LP_SORT_BY := ', h.order_number ';
466 END IF;
467 END;
468 RETURN (TRUE);
469 END AFTERPFORM;
470 FUNCTION RP_ORDER_BYFORMULA RETURN VARCHAR2 IS
471 BEGIN
472 DECLARE
473 L_SORT_BY VARCHAR2(100);
474 L_LOOKUP_TYPE VARCHAR2(80);
475 L_LOOKUP_CODE VARCHAR2(80);
476 BEGIN
477 L_LOOKUP_TYPE := 'OEXPRPRS ORDER BY';
478 L_LOOKUP_CODE := P_ORDER_BY;
479 SELECT
480 MEANING
481 INTO L_SORT_BY
482 FROM
483 OE_LOOKUPS
484 WHERE LOOKUP_CODE = L_LOOKUP_CODE
485 AND LOOKUP_TYPE = L_LOOKUP_TYPE;
486 RETURN (L_SORT_BY);
487 EXCEPTION
488 WHEN NO_DATA_FOUND THEN
489 RETURN ('Order Number');
490 END;
491 RETURN NULL;
492 END RP_ORDER_BYFORMULA;
493 FUNCTION C_DISCOUNT_PRICEFORMULA(ORDER_AMOUNT IN NUMBER
494 ,ORDER_LIST IN NUMBER) RETURN NUMBER IS
495 BEGIN
496 /*SRW.REFERENCE(ORDER_AMOUNT)*/NULL;
497 /*SRW.REFERENCE(ORDER_LIST)*/NULL;
498 RETURN (NVL(ORDER_LIST
499 ,0) - NVL(ORDER_AMOUNT
500 ,0));
501 END C_DISCOUNT_PRICEFORMULA;
502 FUNCTION C_DATA_NOT_FOUNDFORMULA(CURRENCY2 IN VARCHAR2) RETURN NUMBER IS
503 BEGIN
504 RP_DATA_FOUND := CURRENCY2;
505 RETURN (0);
506 END C_DATA_NOT_FOUNDFORMULA;
507 FUNCTION C_ORDER_AMOUNTFORMULA(ORDER_AMOUNT IN NUMBER
508 ,C_PRE IN NUMBER) RETURN NUMBER IS
509 BEGIN
510 RETURN (ROUND(ORDER_AMOUNT
511 ,C_PRE));
512 END C_ORDER_AMOUNTFORMULA;
513 FUNCTION C_ORDER_LISTFORMULA(ORDER_LIST IN NUMBER
514 ,C_PRE IN NUMBER) RETURN NUMBER IS
515 BEGIN
516 RETURN (ROUND(ORDER_LIST
517 ,C_PRE));
518 END C_ORDER_LISTFORMULA;
519 FUNCTION C_DISCOUNT_PRICE_CUFORMULA(S_DISCOUNT_PRICE_CU IN NUMBER
520 ,C_PRE IN NUMBER) RETURN NUMBER IS
521 BEGIN
522 RETURN (ROUND(S_DISCOUNT_PRICE_CU
523 ,C_PRE));
524 END C_DISCOUNT_PRICE_CUFORMULA;
525 FUNCTION C_ORDER_LIST_CUFORMULA(S_ORDER_LIST_CU IN NUMBER
526 ,C_PRE IN NUMBER) RETURN NUMBER IS
527 BEGIN
528 RETURN (ROUND(S_ORDER_LIST_CU
529 ,C_PRE));
530 END C_ORDER_LIST_CUFORMULA;
531 FUNCTION C_ORDER_AMOUNT_CUFORMULA(S_ORDER_AMOUNT_CU IN NUMBER
532 ,C_PRE IN NUMBER) RETURN NUMBER IS
533 BEGIN
534 RETURN (ROUND(S_ORDER_AMOUNT_CU
535 ,C_PRE));
536 END C_ORDER_AMOUNT_CUFORMULA;
537 FUNCTION C_DISCOUNT_PRICE_OTFORMULA(S_DISCOUNT_PRICE_OT IN NUMBER
538 ,C_PRE IN NUMBER) RETURN NUMBER IS
539 BEGIN
540 RETURN (ROUND(S_DISCOUNT_PRICE_OT
541 ,C_PRE));
542 END C_DISCOUNT_PRICE_OTFORMULA;
543 FUNCTION C_ORDER_LIST_OTFORMULA(S_ORDER_LIST_OT IN NUMBER
544 ,C_PRE IN NUMBER) RETURN NUMBER IS
545 BEGIN
546 RETURN (ROUND(S_ORDER_LIST_OT
547 ,C_PRE));
548 END C_ORDER_LIST_OTFORMULA;
549 FUNCTION C_ORDER_AMOUNT_OTFORMULA(S_ORDER_AMOUNT_OT IN NUMBER
550 ,C_PRE IN NUMBER) RETURN NUMBER IS
551 BEGIN
552 RETURN (ROUND(S_ORDER_AMOUNT_OT
553 ,C_PRE));
554 END C_ORDER_AMOUNT_OTFORMULA;
555 FUNCTION C_DISCOUNT_PRCE_CFORMULA(S_DISCOUNT_PRICE_C IN NUMBER
556 ,C_PRE IN NUMBER) RETURN NUMBER IS
557 BEGIN
558 RETURN (ROUND(S_DISCOUNT_PRICE_C
559 ,C_PRE));
560 END C_DISCOUNT_PRCE_CFORMULA;
561 FUNCTION C_ORDER_LIST_CFORMULA(S_ORDER_LIST_C IN NUMBER
562 ,C_PRE IN NUMBER) RETURN NUMBER IS
563 BEGIN
564 RETURN (ROUND(S_ORDER_LIST_C
565 ,C_PRE));
566 END C_ORDER_LIST_CFORMULA;
567 FUNCTION C_ORDER_AMOUNT_CFORMULA(S_ORDER_AMOUNT_C IN NUMBER
568 ,C_PRE IN NUMBER) RETURN NUMBER IS
569 BEGIN
570 RETURN (ROUND(S_ORDER_AMOUNT_C
571 ,C_PRE));
572 END C_ORDER_AMOUNT_CFORMULA;
573 FUNCTION CF_1FORMULA(CHARGE_PERIODICITY_CODE IN VARCHAR2) RETURN VARCHAR2 IS
574 L_UOM_CLASS VARCHAR2(50) := FND_PROFILE.VALUE('ONT_UOM_CLASS_CHARGE_PERIODICITY');
575 L_CHARGE_PERIODICITY VARCHAR2(25);
576 BEGIN
577 IF CHARGE_PERIODICITY_CODE IS NOT NULL THEN
578 SELECT
579 UNIT_OF_MEASURE
580 INTO L_CHARGE_PERIODICITY
581 FROM
582 MTL_UNITS_OF_MEASURE_VL
583 WHERE UOM_CLASS = L_UOM_CLASS
584 AND UOM_CODE = CHARGE_PERIODICITY_CODE;
585 RETURN L_CHARGE_PERIODICITY;
586 ELSE
587 RETURN (P_ONE_TIME);
588 END IF;
589 EXCEPTION
590 WHEN NO_DATA_FOUND THEN
591 RETURN NULL;
592 END CF_1FORMULA;
593 FUNCTION CF_2FORMULA(S_ORDER_AMOUNT_P IN NUMBER
594 ,C_PRE IN NUMBER) RETURN NUMBER IS
595 BEGIN
596 RETURN (ROUND(S_ORDER_AMOUNT_P
597 ,C_PRE));
598 END CF_2FORMULA;
599 FUNCTION C_DISCOUNT_PRICE_PFORMULA(S_DISCOUNT_PRICE_P IN NUMBER
600 ,C_PRE IN NUMBER) RETURN NUMBER IS
601 BEGIN
602 RETURN (ROUND(S_DISCOUNT_PRICE_P
603 ,C_PRE));
604 END C_DISCOUNT_PRICE_PFORMULA;
605 FUNCTION C_ORDER_LIST_PFORMULA(S_ORDER_LIST_P IN NUMBER
606 ,C_PRE IN NUMBER) RETURN NUMBER IS
607 BEGIN
608 RETURN (ROUND(S_ORDER_LIST_P
609 ,C_PRE));
610 END C_ORDER_LIST_PFORMULA;
611 FUNCTION RP_REPORT_NAME_P RETURN VARCHAR2 IS
612 BEGIN
613 RETURN RP_REPORT_NAME;
614 END RP_REPORT_NAME_P;
615 FUNCTION RP_SUB_TITLE_P RETURN VARCHAR2 IS
616 BEGIN
617 RETURN RP_SUB_TITLE;
618 END RP_SUB_TITLE_P;
619 FUNCTION RP_COMPANY_NAME_P RETURN VARCHAR2 IS
620 BEGIN
621 RETURN RP_COMPANY_NAME;
622 END RP_COMPANY_NAME_P;
623 FUNCTION RP_FUNCTIONAL_CURRENCY_P RETURN VARCHAR2 IS
624 BEGIN
625 RETURN RP_FUNCTIONAL_CURRENCY;
626 END RP_FUNCTIONAL_CURRENCY_P;
627 FUNCTION RP_DATA_FOUND_P RETURN VARCHAR2 IS
628 BEGIN
629 RETURN RP_DATA_FOUND;
630 END RP_DATA_FOUND_P;
631 FUNCTION RP_ORDER_NUMBER_RANGE_P RETURN VARCHAR2 IS
632 BEGIN
633 RETURN RP_ORDER_NUMBER_RANGE;
634 END RP_ORDER_NUMBER_RANGE_P;
635 FUNCTION RP_ORDER_LIST_RANGE_P RETURN VARCHAR2 IS
636 BEGIN
637 RETURN RP_ORDER_LIST_RANGE;
638 END RP_ORDER_LIST_RANGE_P;
639 FUNCTION RP_ORDER_DATE_RANGE_P RETURN VARCHAR2 IS
640 BEGIN
641 RETURN RP_ORDER_DATE_RANGE;
642 END RP_ORDER_DATE_RANGE_P;
643 FUNCTION RP_OPEN_ORDERS_ONLY_P RETURN VARCHAR2 IS
644 BEGIN
645 RETURN RP_OPEN_ORDERS_ONLY;
646 END RP_OPEN_ORDERS_ONLY_P;
647 FUNCTION RP_ORDER_AMOUNT_RANGE_P RETURN VARCHAR2 IS
648 BEGIN
649 RETURN RP_ORDER_AMOUNT_RANGE;
650 END RP_ORDER_AMOUNT_RANGE_P;
651 FUNCTION RP_AGREEMENT_NAME_P RETURN VARCHAR2 IS
652 BEGIN
653 RETURN RP_AGREEMENT_NAME;
654 END RP_AGREEMENT_NAME_P;
655 FUNCTION RP_ORDER_TYPE_RANGE_P RETURN VARCHAR2 IS
656 BEGIN
657 RETURN RP_ORDER_TYPE_RANGE;
658 END RP_ORDER_TYPE_RANGE_P;
659 FUNCTION RP_CUSTOMER_NUMBER_RANGE_P RETURN VARCHAR2 IS
660 BEGIN
661 RETURN RP_CUSTOMER_NUMBER_RANGE;
662 END RP_CUSTOMER_NUMBER_RANGE_P;
663 FUNCTION RP_CUSTOMER_NAME_RANGE_P RETURN VARCHAR2 IS
664 BEGIN
665 RETURN RP_CUSTOMER_NAME_RANGE;
666 END RP_CUSTOMER_NAME_RANGE_P;
667 FUNCTION RP_SALESREP_RANGE_P RETURN VARCHAR2 IS
668 BEGIN
669 RETURN RP_SALESREP_RANGE;
670 END RP_SALESREP_RANGE_P;
671 FUNCTION RP_ORDER_CATEGORY_P RETURN VARCHAR2 IS
672 BEGIN
673 RETURN RP_ORDER_CATEGORY;
674 END RP_ORDER_CATEGORY_P;
675 FUNCTION RP_LINE_CATEGORY_P RETURN VARCHAR2 IS
676 BEGIN
677 RETURN RP_LINE_CATEGORY;
678 END RP_LINE_CATEGORY_P;
679 END ONT_OEXPRPRS_XMLP_PKG;
680
[Home] [Help]