[Home] [Help]
PACKAGE BODY: APPS.ONT_OEXPRPRD_XMLP_PKG
Source
1 PACKAGE BODY ONT_OEXPRPRD_XMLP_PKG AS
2 /* $Header: OEXPRPRDB.pls 120.3 2008/05/05 06:40:04 dwkrishn noship $ */
3 function Item_dspFormula(item_identifier_type_L varchar2,
4 iid_L number ,
5 oid_L number,
6 oi_L varchar2,
7 INVENTORY_ITEM_ID_L number,
8 ORGANIZATION_ID_L number) return Char is
9 v_item varchar2(2000);
10 v_description varchar2(500);
11 begin
12 if (item_identifier_type_L is null or item_identifier_type_L = 'INT')
13 or (p_print_description in ('I','D','F')) then
14 select
15 -- sitems.concatenated_segments item,
16 sitems.description description
17 into
18 -- v_item,
19 v_description
20 from mtl_system_items_vl sitems
21 where
22 sitems.customer_order_enabled_flag = 'Y' and
23 sitems.bom_item_type in (1,4)
24 and nvl(sitems.organization_id,0) = nvl(oe_sys_parameters.value('MASTER_ORGANIZATION_ID',mo_global.get_current_org_id()),0)
25 and sitems.inventory_item_id = iid_L;
26 -- :rp_dummy_item := v_item;
27
28 /* srw.reference (:ITEM_FLEX);
29 srw.reference (:p_item_flex_code);
30 srw.reference (:Item_dsp);
31 srw.reference (:p_item_structure_num);
32 srw.user_exit (' FND FLEXIDVAL
33 CODE=":p_item_flex_code"
34 NUM=":p_item_structure_num"
35 APPL_SHORT_NAME="INV"
36 DATA= ":ITEM_FLEX"
37 VALUE=":Item_dsp"
38 DISPLAY="ALL"'
39 );
40 -- :rp_dummy_item := ''; */
41 v_item :=fnd_flex_xml_publisher_apis.process_kff_combination_1('Item_dsp', 'INV', p_item_flex_code,p_item_structure_num,ORGANIZATION_ID_L,INVENTORY_ITEM_ID_L, 'ALL', 'Y', 'VALUE');
42 elsif (item_identifier_type_L = 'CUST' and p_print_description in ('C','P','O')) then
43 select citems.customer_item_number item,
44 nvl(citems.customer_item_desc,sitems.description) description
45 into v_item,v_description
46 from mtl_customer_items citems,
47 mtl_customer_item_xrefs cxref,
48 mtl_system_items_vl sitems
49 where citems.customer_item_id = cxref.customer_item_id
50 and cxref.inventory_item_id = sitems.inventory_item_id
51 and citems.customer_item_id = oid_L
52 and nvl(sitems.organization_id,0) = nvl(oe_sys_parameters.value('MASTER_ORGANIZATION_ID',mo_global.get_current_org_id()),0)
53 and sitems.inventory_item_id = iid_L;
54 -- and sitems.customer_order_enabled_flag = 'Y'
55 -- and sitems.bom_item_type in (1,4)
56 elsif (p_print_description in ('C','P','O')) then
57 select items.cross_reference item,
58 nvl(items.description,sitems.description) description
59 into v_item,v_description
60 from mtl_cross_reference_types xtypes,
61 mtl_cross_references items,
62 mtl_system_items_vl sitems
63 where xtypes.cross_reference_type = items.cross_reference_type
64 and items.inventory_item_id = sitems.inventory_item_id
65 and items.cross_reference = oi_L
66 and items.cross_reference_type = item_identifier_type_L
67 and nvl(sitems.organization_id,0) = nvl(oe_sys_parameters.value('MASTER_ORGANIZATION_ID',mo_global.get_current_org_id()),0)
68 and sitems.inventory_item_id = iid_L;
69 -- and sitems.customer_order_enabled_flag = 'Y'
70 -- and sitems.bom_item_type in (1,4)
71 end if;
72
73 if (p_print_description in ('I','C')) then
74 return(v_item||' - '||v_description);
75 elsif (p_print_description in ('D','P')) then
76 return(v_description);
77 else
78 return(v_item);
79 end if;
80
81 RETURN NULL;
82 Exception
83 When Others Then
84 return('Item Not Found');
85 end;
86 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
87 BEGIN
88 BEGIN
89 BEGIN
90 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
91 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
92 EXCEPTION
93 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
94 /*SRW.MESSAGE(1000
95 ,'Failed in BEFORE REPORT trigger')*/NULL;
96 RETURN (FALSE);
97 END;
98 BEGIN
99 P_ORGANIZATION_ID := MO_GLOBAL.GET_CURRENT_ORG_ID;
100 END;
101 DECLARE
102 L_COMPANY_NAME VARCHAR2(100);
103 L_FUNCTIONAL_CURRENCY VARCHAR2(15);
104 BEGIN
105 SELECT
106 SOB.NAME,
107 SOB.CURRENCY_CODE
108 INTO L_COMPANY_NAME,L_FUNCTIONAL_CURRENCY
109 FROM
110 GL_SETS_OF_BOOKS SOB,
111 FND_CURRENCIES CUR
112 WHERE SOB.SET_OF_BOOKS_ID = P_SOB_ID
113 AND SOB.CURRENCY_CODE = CUR.CURRENCY_CODE;
114 RP_COMPANY_NAME := L_COMPANY_NAME;
115 RP_FUNCTIONAL_CURRENCY := L_FUNCTIONAL_CURRENCY;
116 EXCEPTION
117 WHEN NO_DATA_FOUND THEN
118 NULL;
119 END;
120 DECLARE
121 L_REPORT_NAME VARCHAR2(240);
122 BEGIN
123 SELECT
124 CP.USER_CONCURRENT_PROGRAM_NAME
125 INTO L_REPORT_NAME
126 FROM
127 FND_CONCURRENT_PROGRAMS_VL CP,
128 FND_CONCURRENT_REQUESTS CR
129 WHERE CR.REQUEST_ID = P_CONC_REQUEST_ID
130 AND CP.APPLICATION_ID = CR.PROGRAM_APPLICATION_ID
131 AND CP.CONCURRENT_PROGRAM_ID = CR.CONCURRENT_PROGRAM_ID;
132 RP_REPORT_NAME := SUBSTR(L_REPORT_NAME,1,INSTR(L_REPORT_NAME,' (XML)'));
133 EXCEPTION
134 WHEN NO_DATA_FOUND THEN
135 RP_REPORT_NAME := 'Order Discount Detail Report';
136 END;
137 DECLARE
138 L_ITEM_STRING VARCHAR2(5000);
139 BEGIN
140 /*SRW.REFERENCE(P_ITEM_FLEX_CODE)*/NULL;
141 /*SRW.REFERENCE(P_ITEM_STRUCTURE_NUM)*/NULL;
142 IF P_ITEM_LOW IS NOT NULL OR P_ITEM_HI IS NOT NULL THEN
143 LP_ITEM := ' and ' || L_ITEM_STRING;
144 ELSE
145 LP_ITEM := ' ';
146 END IF;
147 EXCEPTION
148 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
149 /*SRW.MESSAGE(2000
150 ,'Failed in BEFORE REPORT trigger:MSTK')*/NULL;
151 END;
152 DECLARE
153 L_MEANING VARCHAR2(80);
154 BEGIN
155 SELECT
156 MEANING
157 INTO L_MEANING
158 FROM
159 OE_LOOKUPS
160 WHERE LOOKUP_TYPE = 'ITEM_DISPLAY_CODE'
161 AND LOOKUP_CODE = SUBSTR(UPPER(NVL(P_PRINT_DESCRIPTION,'D'))
162 ,1
163 ,1);
164 ITEM_FLEX_DESC_MEANING := L_MEANING;
165 EXCEPTION
166 WHEN NO_DATA_FOUND THEN
167 ITEM_FLEX_DESC_MEANING := 'Internal Item Description';
168 WHEN OTHERS THEN
169 /*SRW.MESSAGE(2000
170 ,'Failed in BEFORE REPORT trigger. Get Print Description')*/NULL;
171 END;
172 BEGIN
173 RP_CURR_PROFILE := FND_PROFILE.VALUE('ONT_UNIT_PRICE_PRECISION_TYPE');
174 EXCEPTION
175 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
176 /*SRW.MESSAGE(3000
177 ,'Failed in BEFORE REPORT Trigger FND GETPROFILE USER_EXIT')*/NULL;
178 END;
179 END;
180 RETURN (TRUE);
181 END BEFOREREPORT;
182 FUNCTION AFTERREPORT RETURN BOOLEAN IS
183 BEGIN
184 BEGIN
185 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
186 EXCEPTION
187 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
188 /*SRW.MESSAGE(1
189 ,'Failed in AFTER REPORT TRIGGER')*/NULL;
190 RETURN (FALSE);
191 END;
192 RETURN (TRUE);
193 END AFTERREPORT;
194 FUNCTION AFTERPFORM RETURN BOOLEAN IS
195 openflag boolean;
196 BEGIN
197 DECLARE
198 BLANKS CONSTANT VARCHAR2(5) DEFAULT ' ';
199 ALL_RANGE CONSTANT VARCHAR2(16) DEFAULT 'From' || BLANKS || 'To' || BLANKS;
200 BEGIN
201 IF (P_ORDER_NUM_LOW IS NOT NULL) AND (P_ORDER_NUM_HIGH IS NOT NULL) THEN
202 LP_ORDER_NUM := ' AND (H.ORDER_NUMBER BETWEEN' || ' TO_NUMBER(:P_ORDER_NUM_LOW) AND' || ' TO_NUMBER(:P_ORDER_NUM_HIGH)) ';
203 ORDER_NUMBER_PARMS := 'From ' || SUBSTR(P_ORDER_NUM_LOW
204 ,1
205 ,6) || ' To ' || SUBSTR(P_ORDER_NUM_HIGH
206 ,1
207 ,6);
208 ORDER_NUMBER_PARMS_LOW := SUBSTR(P_ORDER_NUM_LOW
209 ,1
210 ,6);
211 ORDER_NUMBER_PARMS_HIGH := SUBSTR(P_ORDER_NUM_HIGH
212 ,1
213 ,6);
214 ELSIF (P_ORDER_NUM_LOW IS NOT NULL) THEN
215 LP_ORDER_NUM := ' AND H.ORDER_NUMBER >=' || ' TO_NUMBER(:P_ORDER_NUM_LOW) ';
216 ORDER_NUMBER_PARMS := 'From ' || SUBSTR(P_ORDER_NUM_LOW
217 ,1
218 ,6) || ' To ' || BLANKS;
219 ORDER_NUMBER_PARMS_LOW := SUBSTR(P_ORDER_NUM_LOW
220 ,1
221 ,6);
222 ORDER_NUMBER_PARMS_HIGH := BLANKS;
223 ELSIF (P_ORDER_NUM_HIGH IS NOT NULL) THEN
224 LP_ORDER_NUM := ' AND H.ORDER_NUMBER <=' || ' TO_NUMBER(:P_ORDER_NUM_HIGH) ';
225 ORDER_NUMBER_PARMS := 'From ' || BLANKS || 'To ' || SUBSTR(P_ORDER_NUM_HIGH
226 ,1
227 ,6);
228 ORDER_NUMBER_PARMS_LOW := BLANKS;
229 ORDER_NUMBER_PARMS_HIGH := SUBSTR(P_ORDER_NUM_HIGH
230 ,1
231 ,6);
232 ELSE
233 LP_ORDER_NUM := ' ';
234 ORDER_NUMBER_PARMS := ALL_RANGE;
235 ORDER_NUMBER_PARMS_LOW := BLANKS;
236 ORDER_NUMBER_PARMS_HIGH := BLANKS;
237 END IF;
238 IF (P_CUSTOMER_NAME_LOW IS NOT NULL) AND (P_CUSTOMER_NAME_HIGH IS NOT NULL) THEN
239 LP_CUSTOMER_NAME := ' AND (ORG.NAME BETWEEN' || ' :P_CUSTOMER_NAME_LOW AND' || ' :P_CUSTOMER_NAME_HIGH) ';
240 CUSTOMER_PARMS := 'From ' || SUBSTR(P_CUSTOMER_NAME_LOW
241 ,1
242 ,20) || ' To ' || SUBSTR(P_CUSTOMER_NAME_HIGH
243 ,1
244 ,20);
245 CUSTOMER_PARMS_LOW := SUBSTR(P_CUSTOMER_NAME_LOW
246 ,1
247 ,20);
248 CUSTOMER_PARMS_HIGH := SUBSTR(P_CUSTOMER_NAME_HIGH
249 ,1
250 ,20);
251 ELSIF (P_CUSTOMER_NAME_LOW IS NOT NULL) THEN
252 LP_CUSTOMER_NAME := ' AND ORG.NAME >=' || ' :P_CUSTOMER_NAME_LOW ';
253 CUSTOMER_PARMS := 'From ' || SUBSTR(P_CUSTOMER_NAME_LOW
254 ,1
255 ,20) || ' To ' || BLANKS;
256 CUSTOMER_PARMS_LOW := SUBSTR(P_CUSTOMER_NAME_LOW
257 ,1
258 ,20);
259 CUSTOMER_PARMS_HIGH := BLANKS;
260 ELSIF (P_CUSTOMER_NAME_HIGH IS NOT NULL) THEN
261 LP_CUSTOMER_NAME := ' AND ORG.NAME <=' || ' :P_CUSTOMER_NAME_HIGH ';
262 CUSTOMER_PARMS := 'From ' || BLANKS || 'To ' || SUBSTR(P_CUSTOMER_NAME_HIGH
263 ,1
264 ,20);
265 CUSTOMER_PARMS_LOW := BLANKS;
266 CUSTOMER_PARMS_HIGH := SUBSTR(P_CUSTOMER_NAME_HIGH
267 ,1
268 ,20);
269 ELSE
270 LP_CUSTOMER_NAME := ' ';
271 CUSTOMER_PARMS := ALL_RANGE;
272 CUSTOMER_PARMS_LOW := BLANKS;
273 CUSTOMER_PARMS_HIGH := BLANKS;
274 END IF;
275 IF (P_CUSTOMER_NUM_LOW IS NOT NULL) AND (P_CUSTOMER_NUM_HIGH IS NOT NULL) THEN
276 LP_CUSTOMER_NUM := ' AND (ORG.CUSTOMER_NUMBER BETWEEN' || ' :P_CUSTOMER_NUM_LOW AND' || ' :P_CUSTOMER_NUM_HIGH) ';
277 CUSTOMER_NUM_PARMS := 'From ' || SUBSTR(P_CUSTOMER_NUM_LOW
278 ,1
279 ,20) || ' To ' || SUBSTR(P_CUSTOMER_NUM_HIGH
280 ,1
281 ,20);
282 CUSTOMER_NUM_PARMS_LOW := SUBSTR(P_CUSTOMER_NUM_LOW
283 ,1
284 ,20);
285 CUSTOMER_NUM_PARMS_HIGH := SUBSTR(P_CUSTOMER_NUM_HIGH
286 ,1
287 ,20);
288 ELSIF (P_CUSTOMER_NUM_LOW IS NOT NULL) THEN
289 LP_CUSTOMER_NUM := ' AND ORG.CUSTOMER_NUMBER >=' || ' :P_CUSTOMER_NUM_LOW ';
290 CUSTOMER_NUM_PARMS := 'From ' || SUBSTR(P_CUSTOMER_NUM_LOW
291 ,1
292 ,20) || ' To ' || BLANKS;
293 CUSTOMER_NUM_PARMS_LOW := SUBSTR(P_CUSTOMER_NUM_LOW
294 ,1
295 ,20);
296 CUSTOMER_NUM_PARMS_HIGH := BLANKS;
297 ELSIF (P_CUSTOMER_NUM_HIGH IS NOT NULL) THEN
298 LP_CUSTOMER_NUM := ' AND ORG.CUSTOMER_NUMBER <=' || ' :P_CUSTOMER_NUM_HIGH ';
299 CUSTOMER_NUM_PARMS := 'From ' || BLANKS || 'To ' || SUBSTR(P_CUSTOMER_NUM_HIGH
300 ,1
301 ,20);
302 CUSTOMER_NUM_PARMS_LOW := BLANKS;
303 CUSTOMER_NUM_PARMS_HIGH := SUBSTR(P_CUSTOMER_NUM_HIGH
304 ,1
305 ,20);
306 ELSE
307 LP_CUSTOMER_NUM := ' ';
308 CUSTOMER_NUM_PARMS := ALL_RANGE;
309 CUSTOMER_NUM_PARMS_LOW := BLANKS;
310 CUSTOMER_NUM_PARMS_HIGH := BLANKS;
311 END IF;
312 IF (P_ORDER_DATE_LOW IS NOT NULL) AND (P_ORDER_DATE_HIGH IS NOT NULL) THEN
313 LP_ORDER_DATE := ' AND (trunc(H.ORDERED_DATE) BETWEEN' || ' :P_ORDER_DATE_LOW AND' || ' :P_ORDER_DATE_HIGH) ';
314 ORDER_DATE_PARMS := 'From ' || TO_CHAR(P_ORDER_DATE_LOW
315 ,'YYYY/MM/DD') || ' To ' || TO_CHAR(P_ORDER_DATE_HIGH
316 ,'YYYY/MM/DD');
317 ORDER_DATE_PARMS_LOW := TO_CHAR(P_ORDER_DATE_LOW
318 ,'YYYY/MM/DD');
319 ORDER_DATE_PARMS_HIGH := TO_CHAR(P_ORDER_DATE_HIGH
320 ,'YYYY/MM/DD');
321 ELSIF (P_ORDER_DATE_LOW IS NOT NULL) THEN
322 LP_ORDER_DATE := ' AND trunc(H.ORDERED_DATE) >= :P_ORDER_DATE_LOW';
323 ORDER_DATE_PARMS := 'From ' || TO_CHAR(P_ORDER_DATE_LOW
324 ,'YYYY/MM/DD') || ' To ' || BLANKS;
325 ORDER_DATE_PARMS_LOW := TO_CHAR(P_ORDER_DATE_LOW
326 ,'YYYY/MM/DD');
327 ORDER_DATE_PARMS_HIGH := BLANKS;
328 ELSIF (P_ORDER_DATE_HIGH IS NOT NULL) THEN
329 LP_ORDER_DATE := ' AND trunc(H.ORDERED_DATE) <= :P_ORDER_DATE_HIGH';
330 ORDER_DATE_PARMS := 'From ' || BLANKS || 'To ' || TO_CHAR(P_ORDER_DATE_HIGH
331 ,'YYYY/MM/DD');
332 ORDER_DATE_PARMS_LOW := BLANKS;
333 ORDER_DATE_PARMS_HIGH := TO_CHAR(P_ORDER_DATE_HIGH
334 ,'YYYY/MM/DD');
335 ELSE
336 LP_ORDER_DATE := ' ';
337 ORDER_DATE_PARMS := ALL_RANGE;
338 ORDER_DATE_PARMS_LOW := BLANKS;
339 ORDER_DATE_PARMS_HIGH := BLANKS;
340 END IF;
341 IF (P_ORDER_TYPE_LOW IS NOT NULL) AND (P_ORDER_TYPE_HIGH IS NOT NULL) THEN
342 LP_ORDER_TYPE := ' AND (OT.transaction_type_id BETWEEN' || ' :P_ORDER_TYPE_LOW AND' || ' :P_ORDER_TYPE_HIGH) ';
343 SELECT
344 OEOT.NAME
345 INTO L_ORDER_TYPE_LOW
346 FROM
347 OE_TRANSACTION_TYPES_TL OEOT
348 WHERE OEOT.TRANSACTION_TYPE_ID = P_ORDER_TYPE_LOW
349 AND OEOT.LANGUAGE = USERENV('LANG');
350 SELECT
351 OEOT.NAME
352 INTO L_ORDER_TYPE_HIGH
353 FROM
354 OE_TRANSACTION_TYPES_TL OEOT
355 WHERE OEOT.TRANSACTION_TYPE_ID = P_ORDER_TYPE_HIGH
356 AND OEOT.LANGUAGE = USERENV('LANG');
357 ORDER_TYPE_PARMS := 'From ' || SUBSTR(L_ORDER_TYPE_LOW
358 ,1
359 ,16) || ' To ' || SUBSTR(L_ORDER_TYPE_HIGH
360 ,1
361 ,16);
362 ORDER_TYPE_PARMS_LOW := SUBSTR(L_ORDER_TYPE_LOW
363 ,1
364 ,16);
365 ORDER_TYPE_PARMS_HIGH := SUBSTR(L_ORDER_TYPE_HIGH
366 ,1
367 ,16);
368 ELSIF (P_ORDER_TYPE_LOW IS NOT NULL) THEN
369 LP_ORDER_TYPE := ' AND OT.transaction_type_id >=' || ' :P_ORDER_TYPE_LOW ';
370 SELECT
371 OEOT.NAME
372 INTO L_ORDER_TYPE_LOW
373 FROM
374 OE_TRANSACTION_TYPES_TL OEOT
375 WHERE OEOT.TRANSACTION_TYPE_ID = P_ORDER_TYPE_LOW
376 AND OEOT.LANGUAGE = USERENV('LANG');
377 ORDER_TYPE_PARMS := 'From ' || SUBSTR(L_ORDER_TYPE_LOW
378 ,1
379 ,16) || ' To ' || BLANKS;
380 ORDER_TYPE_PARMS_LOW := SUBSTR(L_ORDER_TYPE_LOW
381 ,1
382 ,16);
383 ORDER_TYPE_PARMS_HIGH := BLANKS;
384 ELSIF (P_ORDER_TYPE_HIGH IS NOT NULL) THEN
385 LP_ORDER_TYPE := ' AND OT.transaction_type_id <=' || ' :P_ORDER_TYPE_HIGH ';
386 SELECT
387 OEOT.NAME
388 INTO L_ORDER_TYPE_HIGH
389 FROM
390 OE_TRANSACTION_TYPES_TL OEOT
391 WHERE OEOT.TRANSACTION_TYPE_ID = P_ORDER_TYPE_HIGH
392 AND OEOT.LANGUAGE = USERENV('LANG');
393 ORDER_TYPE_PARMS := 'From ' || BLANKS || 'To ' || SUBSTR(L_ORDER_TYPE_HIGH
394 ,1
395 ,16);
396 ORDER_TYPE_PARMS_LOW := BLANKS;
397 ORDER_TYPE_PARMS_HIGH := SUBSTR(L_ORDER_TYPE_HIGH
398 ,1
399 ,16);
400 ELSE
401 LP_ORDER_TYPE := ' ';
402 ORDER_TYPE_PARMS := ALL_RANGE;
403 ORDER_TYPE_PARMS_LOW := BLANKS;
404 ORDER_TYPE_PARMS_HIGH := BLANKS;
405 END IF;
406 IF (P_LINE_TYPE_LOW IS NOT NULL) THEN
407 SELECT
408 OEOT.NAME
409 INTO L_LINE_TYPE_LOW
410 FROM
411 OE_TRANSACTION_TYPES_TL OEOT
412 WHERE OEOT.TRANSACTION_TYPE_ID = P_LINE_TYPE_LOW
413 AND OEOT.LANGUAGE = USERENV('LANG');
414 END IF;
415 IF (P_LINE_TYPE_HIGH IS NOT NULL) THEN
416 SELECT
417 OEOT.NAME
418 INTO L_LINE_TYPE_HIGH
419 FROM
420 OE_TRANSACTION_TYPES_TL OEOT
421 WHERE OEOT.TRANSACTION_TYPE_ID = P_LINE_TYPE_HIGH
422 AND OEOT.LANGUAGE = USERENV('LANG');
423 END IF;
424 IF (P_ITEM_LOW IS NOT NULL) AND (P_ITEM_HI IS NOT NULL) THEN
425 /*SRW.MESSAGE(1
426 ,':P_ITEM_LOW' || P_ITEM_LOW)*/NULL;
427 LINE_TYPE_PARMS := 'From ' || SUBSTR(L_LINE_TYPE_LOW
428 ,1
429 ,16) || ' To ' || SUBSTR(L_LINE_TYPE_HIGH
430 ,1
431 ,16);
432 LINE_TYPE_PARMS_LOW := SUBSTR(L_LINE_TYPE_LOW
433 ,1
434 ,16);
435 LINE_TYPE_PARMS_HIGH := SUBSTR(L_LINE_TYPE_HIGH
436 ,1
437 ,16);
438 ELSIF (P_ITEM_LOW IS NOT NULL) THEN
439 LINE_TYPE_PARMS := 'From ' || SUBSTR(P_ITEM_LOW
440 ,1
441 ,16) || ' To ' || BLANKS;
442 LINE_TYPE_PARMS_LOW := SUBSTR(P_ITEM_LOW
443 ,1
444 ,16);
445 LINE_TYPE_PARMS_HIGH := BLANKS;
446 ELSIF (P_ITEM_HI IS NOT NULL) THEN
447 LINE_TYPE_PARMS := 'From ' || BLANKS || 'To ' || SUBSTR(P_ITEM_HI
448 ,1
449 ,16);
450 LINE_TYPE_PARMS_LOW := BLANKS;
451 LINE_TYPE_PARMS_HIGH := SUBSTR(P_ITEM_HI
452 ,1
453 ,16);
454 ELSE
455 LINE_TYPE_PARMS := ALL_RANGE;
456 LINE_TYPE_PARMS_LOW := BLANKS;
457 LINE_TYPE_PARMS_HIGH := BLANKS;
458 END IF;
459 IF (P_SALESREP_LOW IS NOT NULL) AND (P_SALESREP_HIGH IS NOT NULL) THEN
460 LP_SALESREP := ' AND (H.SALESREP_ID BETWEEN' || ' :P_SALESREP_LOW AND' || ' :P_SALESREP_HIGH) ';
461 SALESREP_PARMS := 'From ' || SUBSTR(P_SALESREP_LOW
462 ,1
463 ,20) || ' To ' || SUBSTR(P_SALESREP_HIGH
464 ,1
465 ,20);
466 SALESREP_PARMS_LOW := SUBSTR(P_SALESREP_LOW
467 ,1
468 ,20);
469 SALESREP_PARMS_HIGH := SUBSTR(P_SALESREP_HIGH
470 ,1
471 ,20);
472 ELSIF (P_SALESREP_LOW IS NOT NULL) THEN
473 LP_SALESREP := ' AND H.SALESREP_ID >=' || ' :P_SALESREP_LOW ';
474 SALESREP_PARMS := 'From ' || SUBSTR(P_SALESREP_LOW
475 ,1
476 ,20) || ' To ' || BLANKS;
477 SALESREP_PARMS_LOW := SUBSTR(P_SALESREP_LOW
478 ,1
479 ,20);
480 SALESREP_PARMS_HIGH := BLANKS;
481 ELSIF (P_SALESREP_HIGH IS NOT NULL) THEN
482 LP_SALESREP := ' AND H.SALESREP_ID <=' || ' :P_SALESREP_HIGH ';
483 SALESREP_PARMS := 'From ' || BLANKS || 'To ' || SUBSTR(P_SALESREP_HIGH
484 ,1
485 ,20);
486 SALESREP_PARMS_LOW := BLANKS;
487 SALESREP_PARMS_HIGH := SUBSTR(P_SALESREP_HIGH
488 ,1
489 ,20);
490 ELSE
491 LP_SALESREP := ' ';
492 SALESREP_PARMS := ALL_RANGE;
493 SALESREP_PARMS_LOW := BLANKS;
494 SALESREP_PARMS_HIGH := BLANKS;
495 END IF;
496 IF (P_SORT_BY IS NOT NULL) THEN
497 IF (P_SORT_BY = 'CUSTOMER') THEN
498 LP_SORT_BY := ' ORG.NAME, ';
499 ELSIF (P_SORT_BY = 'ORDER_NUMBER') THEN
500 LP_SORT_BY := ' H.ORDER_NUMBER, ';
501 ELSIF (P_SORT_BY = 'ITEM') THEN
502 LP_SORT_BY := ' SI.SEGMENT1, ';
503 END IF;
504 ELSE
505 LP_SORT_BY := ' H.ORDER_NUMBER, ';
506 END IF;
507 IF (P_OPEN_FLAG IS NOT NULL) THEN
508 IF ((SUBSTR(UPPER(P_OPEN_FLAG)
509 ,1
510 ,1)) = 'Y') THEN
511 LP_OPEN_FLAG := ' AND H.OPEN_FLAG = ''Y''';
512 ELSE
513 LP_OPEN_FLAG := ' AND H.OPEN_FLAG IS NOT NULL';
514 END IF;
515 ELSE
516 LP_OPEN_FLAG := ' ';
517 END IF;
518 IF P_ORDER_NUM_LOW = P_ORDER_NUM_HIGH THEN
519 NULL;
520 ELSE
521 IF P_ORDER_CATEGORY IS NOT NULL THEN
522 IF P_ORDER_CATEGORY = 'SALES' THEN
523 LP_ORDER_CATEGORY := 'and h.order_category_code in (''ORDER'', ''MIXED'') ';
524 ELSIF P_ORDER_CATEGORY = 'CREDIT' THEN
525 LP_ORDER_CATEGORY := 'and h.order_category_code in (''RETURN'', ''MIXED'') ';
526 ELSIF P_ORDER_CATEGORY = 'ALL' THEN
527 LP_ORDER_CATEGORY := ' ';
528 END IF;
529 ELSE
530 LP_ORDER_CATEGORY := 'and h.order_category_code in (''ORDER'', ''MIXED'') ';
531 END IF;
532 END IF;
533 IF P_LINE_CATEGORY IS NOT NULL THEN
534 IF P_LINE_CATEGORY = 'SALES' THEN
535 LP_LINE_CATEGORY := 'and l.line_category_code = ''ORDER'' ';
536 ELSIF P_LINE_CATEGORY = 'CREDIT' THEN
537 LP_LINE_CATEGORY := 'and l.line_category_code = ''RETURN'' ';
538 ELSIF P_LINE_CATEGORY = 'ALL' THEN
539 LP_LINE_CATEGORY := ' ';
540 END IF;
541 ELSE
542 LP_LINE_CATEGORY := 'and l.line_category_code = ''ORDER'' ';
543 END IF;
544 END;
545 openflag:=P_OPEN_FLAGVALIDTRIGGER();
546 RETURN (TRUE);
547 END AFTERPFORM;
548 FUNCTION TOTAL_SELL_PRICEFORMULA(SELL_PRICE IN NUMBER
549 ,QUANTITY IN NUMBER) RETURN NUMBER IS
550 BEGIN
551 RETURN (NVL(SELL_PRICE
552 ,0) * NVL(QUANTITY
553 ,0));
554 END TOTAL_SELL_PRICEFORMULA;
555 FUNCTION ORDER_DISCOUNTFORMULA(ORDER_LIST_AMT IN NUMBER
556 ,ORDER_SELL_AMT IN NUMBER) RETURN NUMBER IS
557 BEGIN
558 IF (ORDER_LIST_AMT = 0) THEN
559 RETURN (0);
560 ELSE
561 RETURN (100 - (ORDER_SELL_AMT / ORDER_LIST_AMT) * 100);
562 END IF;
563 RETURN NULL;
564 END ORDER_DISCOUNTFORMULA;
565 FUNCTION TOTAL_LIST_PRICEFORMULA(LIST_PRICE IN NUMBER
566 ,QUANTITY IN NUMBER) RETURN NUMBER IS
567 BEGIN
568 RETURN (NVL(LIST_PRICE
569 ,0) * NVL(QUANTITY
570 ,0));
571 END TOTAL_LIST_PRICEFORMULA;
572 FUNCTION SORT_BY_MEANINGVALIDTRIGGER RETURN BOOLEAN IS
573 BEGIN
574 RETURN (TRUE);
575 END SORT_BY_MEANINGVALIDTRIGGER;
576 FUNCTION ORDER_TYPE_PARMSVALIDTRIGGER RETURN BOOLEAN IS
577 BEGIN
578 RETURN (TRUE);
579 END ORDER_TYPE_PARMSVALIDTRIGGER;
580 FUNCTION ORDER_NUMBER_PARMSVALIDTRIGGER RETURN BOOLEAN IS
581 BEGIN
582 RETURN (TRUE);
583 END ORDER_NUMBER_PARMSVALIDTRIGGER;
584 FUNCTION SALESREP_PARMSVALIDTRIGGER RETURN BOOLEAN IS
585 BEGIN
586 RETURN (TRUE);
587 END SALESREP_PARMSVALIDTRIGGER;
588 FUNCTION CUSTOMER_PARMSVALIDTRIGGER RETURN BOOLEAN IS
589 BEGIN
590 RETURN (TRUE);
591 END CUSTOMER_PARMSVALIDTRIGGER;
592 FUNCTION ORDER_DATE_PARMSVALIDTRIGGER RETURN BOOLEAN IS
593 BEGIN
594 RETURN (TRUE);
595 END ORDER_DATE_PARMSVALIDTRIGGER;
596 FUNCTION P_SORT_BYVALIDTRIGGER RETURN BOOLEAN IS
597 BEGIN
598 BEGIN
599 RETURN (TRUE);
600 EXCEPTION
601 WHEN NO_DATA_FOUND THEN
602 RETURN (FALSE);
603 END;
604 RETURN (TRUE);
605 END P_SORT_BYVALIDTRIGGER;
606 FUNCTION P_OPEN_FLAGVALIDTRIGGER RETURN BOOLEAN IS
607 BEGIN
608 BEGIN
609 IF (P_OPEN_FLAG IS NOT NULL) THEN
610 SELECT
611 SUBSTR(MEANING
612 ,1
613 ,5)
614 INTO OPEN_FLAG_MEANING
615 FROM
616 FND_LOOKUPS
617 WHERE LOOKUP_TYPE = 'YES_NO'
618 AND LOOKUP_CODE = SUBSTR(UPPER(P_OPEN_FLAG)
619 ,1
620 ,1);
621 END IF;
622 RETURN (TRUE);
623 EXCEPTION
624 WHEN NO_DATA_FOUND THEN
625 RETURN (FALSE);
626 END;
627 RETURN (TRUE);
628 END P_OPEN_FLAGVALIDTRIGGER;
629 FUNCTION P_PRINT_DESCVALIDTRIGGER RETURN BOOLEAN IS
630 BEGIN
631 RETURN (TRUE);
632 END P_PRINT_DESCVALIDTRIGGER;
633 FUNCTION RP_ORDER_CATEGORYFORMULA RETURN VARCHAR2 IS
634 BEGIN
635 DECLARE
636 L_MEANING VARCHAR2(80);
637 BEGIN
638 SELECT
639 MEANING
640 INTO L_MEANING
641 FROM
642 OE_LOOKUPS
643 WHERE LOOKUP_TYPE = 'REPORT_ORDER_CATEGORY'
644 AND LOOKUP_CODE = P_ORDER_CATEGORY;
645 RETURN (L_MEANING);
646 EXCEPTION
647 WHEN NO_DATA_FOUND THEN
648 RETURN (NULL);
649 END;
650 RETURN NULL;
651 END RP_ORDER_CATEGORYFORMULA;
652 FUNCTION TOTAL_ORDER_LIST_PRICEFORMULA(LINE_LIST_PRICE IN NUMBER
653 ,QUANTITY IN NUMBER) RETURN NUMBER IS
654 BEGIN
655 RETURN (NVL(LINE_LIST_PRICE
656 ,0) * NVL(QUANTITY
657 ,0));
658 END TOTAL_ORDER_LIST_PRICEFORMULA;
659 FUNCTION TOTAL_LINE_SELL_PRICEFORMULA(LINE_SELL_PRICE IN NUMBER
660 ,QUANTITY IN NUMBER) RETURN NUMBER IS
661 BEGIN
662 RETURN (NVL(LINE_SELL_PRICE
663 ,0) * NVL(QUANTITY
664 ,0));
665 END TOTAL_LINE_SELL_PRICEFORMULA;
666 FUNCTION BEFOREPFORM RETURN BOOLEAN IS
667 BEGIN
668 RETURN (TRUE);
669 END BEFOREPFORM;
670 FUNCTION BETWEENPAGE RETURN BOOLEAN IS
671 BEGIN
672 RETURN (TRUE);
673 END BETWEENPAGE;
674 FUNCTION RP_LINE_CATEGORYFORMULA RETURN VARCHAR2 IS
675 BEGIN
676 DECLARE
677 L_MEANING VARCHAR2(80);
678 BEGIN
679 SELECT
680 MEANING
681 INTO L_MEANING
682 FROM
683 OE_LOOKUPS
684 WHERE LOOKUP_TYPE = 'REPORT_LINE_DISPLAY'
685 AND LOOKUP_CODE = P_LINE_CATEGORY;
686 RETURN (L_MEANING);
687 EXCEPTION
688 WHEN NO_DATA_FOUND THEN
689 RETURN (NULL);
690 END;
691 RETURN NULL;
692 END RP_LINE_CATEGORYFORMULA;
693 FUNCTION DISCOUNT_AMOUNTFORMULA(DISCOUNT_AMT IN NUMBER) RETURN NUMBER IS
694 BEGIN
695 /*SRW.REFERENCE(P_MIN_PRECISION)*/NULL;
696 RETURN (ROUND(NVL(DISCOUNT_AMT
697 ,0)
698 ,TO_NUMBER(NVL(P_MIN_PRECISION,2))));
699 END DISCOUNT_AMOUNTFORMULA;
700 FUNCTION CF_CHARGE_PERIODICITYFORMULA(CHARGE_PERIODICITY_CODE IN VARCHAR2) RETURN CHAR IS
701 L_UOM_CLASS VARCHAR2(50) := FND_PROFILE.VALUE('ONT_UOM_CLASS_CHARGE_PERIODICITY');
702 L_CHARGE_PERIODICITY VARCHAR2(25);
703 BEGIN
704 IF CHARGE_PERIODICITY_CODE IS NOT NULL THEN
705 SELECT
706 UNIT_OF_MEASURE
707 INTO L_CHARGE_PERIODICITY
708 FROM
709 MTL_UNITS_OF_MEASURE_VL
710 WHERE UOM_CLASS = L_UOM_CLASS
711 AND UOM_CODE = CHARGE_PERIODICITY_CODE;
712 RETURN L_CHARGE_PERIODICITY;
713 ELSE
714 RETURN (P_ONE_TIME);
715 END IF;
716 EXCEPTION
717 WHEN NO_DATA_FOUND THEN
718 RETURN NULL;
719 END CF_CHARGE_PERIODICITYFORMULA;
720 FUNCTION LIST_PRICE_DISPLAYFORMULA(LIST_PRICE IN NUMBER)
721 RETURN VARCHAR2 IS
722 BEGIN
723 /*SRW.REFERENCE(:RP_FUNCTIONAL_CURRENCY);
724 SRW.REFERENCE(:LIST_PRICE);
725 SRW.REFERENCE(:LIST_PRICE_DISPLAY);
726 SRW.REFERENCE(:P_MIN_PRECISION);*/
727 --BUG 3485175 STARTS
728 DECLARE
729 L_STD_PRECISION NUMBER;
730 L_EXT_PRECISION NUMBER;
731 L_MIN_ACCT_UNIT NUMBER;
732 L_LIST_PRICE NUMBER;
733 BEGIN
734 /*SRW.REFERENCE(:RP_CURR_PROFILE);
735 SRW.REFERENCE(:RP_LIST_PRICE);*/
736 L_LIST_PRICE := LIST_PRICE;
737 FND_CURRENCY.GET_INFO(RP_FUNCTIONAL_CURRENCY,L_STD_PRECISION,L_EXT_PRECISION,L_MIN_ACCT_UNIT);
738 IF( FND_PROFILE.VALUE('ONT_UNIT_PRICE_PRECISION_TYPE') = 'EXTENDED' ) THEN
739 L_LIST_PRICE := ROUND(L_LIST_PRICE,L_EXT_PRECISION);
740 ELSE
741 L_LIST_PRICE := ROUND(L_LIST_PRICE,L_STD_PRECISION);
742 END IF;
743 RP_LIST_PRICE := L_LIST_PRICE;
744 EXCEPTION
745 WHEN OTHERS THEN
746 RP_LIST_PRICE := LIST_PRICE;
747 END;
748 --BUG 3485175 END
749 /*SRW.USER_EXIT('FND FORMAT_CURRENCY
750 CODE=":RP_FUNCTIONAL_CURRENCY"
751 DISPLAY_WIDTH="13"
752 AMOUNT=":RP_LIST_PRICE"
753 DISPLAY=":LIST_PRICE_DISPLAY"
754 MINIMUM_PRECISION=":P_MIN_PRECISION"');*/
755 RETURN(LIST_PRICE);
756 EXCEPTION
757 WHEN NO_DATA_FOUND THEN
758 RETURN ('NO RATE');
759 WHEN OTHERS THEN
760 RETURN('NO RATE');
761 END LIST_PRICE_DISPLAYFORMULA;
762 FUNCTION SELL_PRICE_DISPLAYFORMULA (SELL_PRICE IN NUMBER) RETURN VARCHAR2 IS
763 BEGIN
764 /*SRW.REFERENCE(:RP_FUNCTIONAL_CURRENCY);
765 SRW.REFERENCE(:SELL_PRICE);
766 SRW.REFERENCE(:SELL_PRICE_DISPLAY);
767 SRW.REFERENCE(:P_MIN_PRECISION);*/
768 --BUG 3485175 STARTS
769 DECLARE
770 L_STD_PRECISION NUMBER;
771 L_EXT_PRECISION NUMBER;
772 L_MIN_ACCT_UNIT NUMBER;
773 L_SELL_PRICE NUMBER;
774 BEGIN
775 --SRW.REFERENCE(:RP_CURR_PROFILE);
776 --SRW.REFERENCE(:RP_SELL_PRICE);
777 L_SELL_PRICE := SELL_PRICE;
778 FND_CURRENCY.GET_INFO(RP_FUNCTIONAL_CURRENCY,L_STD_PRECISION,L_EXT_PRECISION,L_MIN_ACCT_UNIT);
779 IF( FND_PROFILE.VALUE('ONT_UNIT_PRICE_PRECISION_TYPE') = 'EXTENDED' ) THEN
780 L_SELL_PRICE := ROUND(L_SELL_PRICE,L_EXT_PRECISION);
781 ELSE
782 L_SELL_PRICE := ROUND(L_SELL_PRICE,L_STD_PRECISION);
783 END IF;
784 RP_SELL_PRICE := L_SELL_PRICE;
785 EXCEPTION
786 WHEN OTHERS THEN
787 RP_SELL_PRICE := SELL_PRICE;
788 END;
789 --BUG 3485175 END
790 /*SRW.USER_EXIT('FND FORMAT_CURRENCY
791 CODE=":RP_FUNCTIONAL_CURRENCY"
792 DISPLAY_WIDTH="37"
793 AMOUNT=":RP_SELL_PRICE"
794 DISPLAY=":SELL_PRICE_DISPLAY"
795 MINIMUM_PRECISION=":P_MIN_PRECISION"');*/
796 RETURN(SELL_PRICE);
797 EXCEPTION
798 WHEN NO_DATA_FOUND THEN
799 RETURN ('NO RATE');
800 WHEN OTHERS THEN
801 RETURN('NO RATE');
802 END;
803 FUNCTION RP_DUMMY_ITEM_P RETURN VARCHAR2 IS
804 BEGIN
805 RETURN RP_DUMMY_ITEM;
806 END RP_DUMMY_ITEM_P;
807 FUNCTION RP_REPORT_NAME_P RETURN VARCHAR2 IS
808 BEGIN
809 RETURN RP_REPORT_NAME;
810 END RP_REPORT_NAME_P;
811 FUNCTION RP_SUB_TITLE_P RETURN VARCHAR2 IS
812 BEGIN
813 RETURN RP_SUB_TITLE;
814 END RP_SUB_TITLE_P;
815 FUNCTION RP_COMPANY_NAME_P RETURN VARCHAR2 IS
816 BEGIN
817 RETURN RP_COMPANY_NAME;
818 END RP_COMPANY_NAME_P;
819 FUNCTION RP_FUNCTIONAL_CURRENCY_P RETURN VARCHAR2 IS
820 BEGIN
821 RETURN RP_FUNCTIONAL_CURRENCY;
822 END RP_FUNCTIONAL_CURRENCY_P;
823 FUNCTION RP_DATA_FOUND_P RETURN VARCHAR2 IS
824 BEGIN
825 RETURN RP_DATA_FOUND;
826 END RP_DATA_FOUND_P;
827 FUNCTION RP_ITEM_FLEX_ALL_SEG_P RETURN VARCHAR2 IS
828 BEGIN
829 RETURN RP_ITEM_FLEX_ALL_SEG;
830 END RP_ITEM_FLEX_ALL_SEG_P;
831 FUNCTION RP_CURR_PROFILE_P RETURN VARCHAR2 IS
832 BEGIN
833 RETURN RP_CURR_PROFILE;
834 END RP_CURR_PROFILE_P;
835 FUNCTION RP_LIST_PRICE_P RETURN NUMBER IS
836 BEGIN
837 RETURN RP_LIST_PRICE;
838 END RP_LIST_PRICE_P;
839 FUNCTION RP_SELL_PRICE_P RETURN NUMBER IS
840 BEGIN
841 RETURN RP_SELL_PRICE;
842 END RP_SELL_PRICE_P;
843 FUNCTION F_1FORMATTRIGGER RETURN VARCHAR2 IS
844 BEGIN
845 IF Oe_Sys_Parameters.Value('RECURRING_CHARGES',mo_global.get_current_org_id())='Y' Then
846 return ('TRUE');
847 ELSE
848 return ('FALSE');
849 END IF;
850 END F_1FORMATTRIGGER;
851
852
853
854 END ONT_OEXPRPRD_XMLP_PKG;
855