1 PACKAGE BODY ONT_OEXOESOS_XMLP_PKG AS
2 /* $Header: OEXOESOSB.pls 120.3 2008/05/05 06:38:53 dwkrishn noship $ */
3
4 function Item_dspFormula(item_identifier_type_L varchar2,
5 inventory_item_id_L number,
6 ordered_item_id_L number,
7 ordered_item_L varchar2,
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 sitems.concatenated_segments item,
15 sitems.description description
16 into v_item,v_description
17 from mtl_system_items_vl sitems
18 -- where sitems.customer_order_enabled_flag = 'Y'
19 -- and sitems.bom_item_type in (1,4)
20 where nvl(sitems.organization_id,0) = nvl(oe_sys_parameters.value('MASTER_ORGANIZATION_ID',mo_global.get_current_org_id()),0)
21 and sitems.inventory_item_id = inventory_item_id_L;
22 /* srw.reference (:p_item_flex_code);
23 srw.reference (:Item_dsp);
24 srw.reference (:p_item_structure_num);
25 srw.user_exit (' FND FLEXIDVAL
26 CODE=":p_item_flex_code"
27 NUM=":p_item_structure_num"
28 APPL_SHORT_NAME="INV"
29 DATA= ":item_flex"
30 VALUE=":Item_dsp"
31 DISPLAY="ALL"'
32 ); */
33 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');
34 elsif (item_identifier_type_L = 'CUST' and p_print_description in ('C','P','O')) then
35 select citems.customer_item_number item,
36 nvl(citems.customer_item_desc,sitems.description) description
37 into v_item,v_description
38 from mtl_customer_items citems,
39 mtl_customer_item_xrefs cxref,
40 mtl_system_items_vl sitems
41 where citems.customer_item_id = cxref.customer_item_id
42 and cxref.inventory_item_id = sitems.inventory_item_id
43 and citems.customer_item_id = ordered_item_id_L
44 and nvl(sitems.organization_id,0) = nvl(oe_sys_parameters.value('MASTER_ORGANIZATION_ID',mo_global.get_current_org_id()),0)
45 and sitems.inventory_item_id = inventory_item_id_L;
46 -- and sitems.customer_order_enabled_flag = 'Y'
47 -- and sitems.bom_item_type in (1,4)
48 elsif (p_print_description in ('C','P','O')) then
49 select items.cross_reference item,
50 nvl(items.description,sitems.description) description
51 into v_item,v_description
52 from mtl_cross_reference_types xtypes,
53 mtl_cross_references items,
54 mtl_system_items_vl sitems
55 where xtypes.cross_reference_type = items.cross_reference_type
56 and items.inventory_item_id = sitems.inventory_item_id
57 and items.cross_reference = ordered_item_L
58 and items.cross_reference_type = item_identifier_type_L
59 and nvl(sitems.organization_id,0) = nvl(oe_sys_parameters.value('MASTER_ORGANIZATION_ID',mo_global.get_current_org_id()),0)
60 and sitems.inventory_item_id = inventory_item_id_L;
61 -- and sitems.customer_order_enabled_flag = 'Y'
62 -- and sitems.bom_item_type in (1,4)
63 end if;
64
65 if (p_print_description in ('I','C')) then
66 return(v_item||' - '||v_description);
67 elsif (p_print_description in ('D','P')) then
68 return(v_description);
69 else
70 return(v_item);
71 end if;
72
73 RETURN NULL;
74 Exception
75 When Others Then
76 return('Item Not Found');
77 end;
78
79
80 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
81 BEGIN
82
83 DECLARE
84
85 BEGIN
86
87 BEGIN
88 -- SRW.USER_EXIT('FND SRWINIT');
89
90 /* EXCEPTION
91 WHEN SRW.USER_EXIT_FAILURE THEN
92 SRW.MESSAGE(1000,'FAILED IN BEFORE REPORT TRIGGER');
93 RAISE SRW.PROGRAM_ABORT;
94 WHEN OTHERS THEN NULL;*/
95 NULL;
96 END;
97
98 BEGIN /*MOAC*/
99
100 P_ORGANIZATION_ID:= MO_GLOBAL.GET_CURRENT_ORG_ID();
101
102 END;
103
104 /*------------------------------------------------------------------------------
105 FOLLOWING PL/SQL BLOCK GETS THE COMPANY NAME, FUNCTIONAL CURRENCY AND PRECISION.
106 ------------------------------------------------------------------------------*/
107 DECLARE
108 L_COMPANY_NAME VARCHAR2 (100);
109 L_FUNCTIONAL_CURRENCY VARCHAR2 (15);
110
111 BEGIN
112
113 SELECT SOB.NAME ,
114 SOB.CURRENCY_CODE
115 INTO
116 L_COMPANY_NAME ,
117 L_FUNCTIONAL_CURRENCY
118 FROM GL_SETS_OF_BOOKS SOB,
119 FND_CURRENCIES CUR
120 WHERE SOB.SET_OF_BOOKS_ID = P_SOB_ID
121 AND SOB.CURRENCY_CODE = CUR.CURRENCY_CODE
122 ;
123
124 RP_COMPANY_NAME := L_COMPANY_NAME;
125 RP_FUNCTIONAL_CURRENCY := L_FUNCTIONAL_CURRENCY ;
126
127 EXCEPTION
128 WHEN NO_DATA_FOUND THEN NULL ;
129 WHEN OTHERS THEN NULL;
130 END ;
131
132 /*------------------------------------------------------------------------------
133 FOLLOWING PL/SQL BLOCK GETS THE ITEM FLEXFIELD
134 ------------------------------------------------------------------------------*/
135 BEGIN
136 /*SRW.REFERENCE(:P_ITEM_FLEX_CODE);
137 SRW.REFERENCE(:P_ITEM_STRUCTURE_NUM);
138 SRW.USER_EXIT('FND FLEXSQL CODE=":P_ITEM_FLEX_CODE"
139 NUM=":P_ITEM_STRUCTURE_NUM"
140 APPL_SHORT_NAME="INV"
141 OUTPUT=":RP_ITEM_FLEX_ALL_SEG"
142 MODE="SELECT"
143 DISPLAY="ALL"
144 TABLEALIAS="SI"');*/
145 /*EXCEPTION
146 WHEN SRW.USER_EXIT_FAILURE THEN
147 SRW.MESSAGE(1,'FAILED IN BEFORE REPORT TRIGGER:MSTK');
148 WHEN OTHERS THEN NULL;*/
149 NULL;
150 END;
151
152
153 /*------------------------------------------------------------------------------
154 FOLLOWING PL/SQL BLOCK GETS THE REPORT NAME FOR THE PASSED CONCURRENT REQUEST ID.
155 ------------------------------------------------------------------------------*/
156 DECLARE
157 L_REPORT_NAME VARCHAR2(240);
158 BEGIN
159 SELECT CP.USER_CONCURRENT_PROGRAM_NAME
160 INTO L_REPORT_NAME
161 FROM FND_CONCURRENT_PROGRAMS_VL CP,
162 FND_CONCURRENT_REQUESTS CR
163 WHERE CR.REQUEST_ID = P_CONC_REQUEST_ID
164 AND CP.APPLICATION_ID = CR.PROGRAM_APPLICATION_ID
165 AND CP.CONCURRENT_PROGRAM_ID = CR.CONCURRENT_PROGRAM_ID
166 ;
167
168 RP_REPORT_NAME := SUBSTR(L_REPORT_NAME,1,INSTR(L_REPORT_NAME,' (XML)'));
169 EXCEPTION
170 WHEN NO_DATA_FOUND
171 THEN RP_REPORT_NAME := 'Salesperson Order Summary Report';
172 WHEN OTHERS THEN NULL;
173 END;
174
175 END;
176
177 /*-----------------------------------------------------------------------------------------
178 FOLLOWING PL/SQL BLOCK GETS THE AGREEMENT NAME FOR THE PASSED AGREEMENT ID.
179 ------------------------------------------------------------------------------------------*/
180 DECLARE
181 L_AGREEMENT_NAME VARCHAR2 (50);
182
183 BEGIN
184
185 IF ( P_AGREEMENT IS NOT NULL) THEN
186 BEGIN
187 SELECT NAME
188 INTO L_AGREEMENT_NAME
189 FROM OE_AGREEMENTS
190 WHERE AGREEMENT_ID = P_AGREEMENT;
191 RP_AGREEMENT_NAME := L_AGREEMENT_NAME ;
192 EXCEPTION
193 WHEN NO_DATA_FOUND
194 THEN RP_AGREEMENT_NAME := NULL;
195 END;
196 END IF;
197
198 END;
199 LP_ORDER_DATE_LOW := to_char(P_ORDER_DATE_LOW,'DD-MON-YY');
200 LP_ORDER_DATE_HIGH := to_char(P_ORDER_DATE_HIGH,'DD-MON-YY');
201 RETURN (TRUE);
202 END BEFOREREPORT;
203
204 FUNCTION AFTERREPORT RETURN BOOLEAN IS
205 BEGIN
206 BEGIN
207 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
208 EXCEPTION
209 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
210 /*SRW.MESSAGE(1
211 ,'Failed in AFTER REPORT TRIGGER')*/NULL;
212 RETURN (FALSE);
213 END;
214 RETURN (TRUE);
215 END AFTERREPORT;
216
217 FUNCTION P_ORGANIZATION_IDVALIDTRIGGER RETURN BOOLEAN IS
218 BEGIN
219 RETURN (TRUE);
220 END P_ORGANIZATION_IDVALIDTRIGGER;
221
222 FUNCTION P_ITEM_FLEX_CODEVALIDTRIGGER RETURN BOOLEAN IS
223 BEGIN
224 RETURN (TRUE);
225 END P_ITEM_FLEX_CODEVALIDTRIGGER;
226
227 FUNCTION P_SOB_IDVALIDTRIGGER RETURN BOOLEAN IS
228 BEGIN
229 RETURN (TRUE);
230 END P_SOB_IDVALIDTRIGGER;
231
232 FUNCTION P_USE_FUNCTIONAL_CURRENCYVALID RETURN BOOLEAN IS
233 BEGIN
234 RETURN (TRUE);
235 END P_USE_FUNCTIONAL_CURRENCYVALID;
236
237 FUNCTION AFTERPFORM RETURN BOOLEAN IS
238 BEGIN
239 BEGIN
240 IF P_ORDER_NUM_LOW IS NOT NULL AND P_ORDER_NUM_HIGH IS NOT NULL THEN
241 IF (P_ORDER_NUM_LOW = P_ORDER_NUM_HIGH) THEN
242 LP_ORDER_NUM := ' and h.order_number = to_number(:p_order_num_low) ';
243 ELSE
244 LP_ORDER_NUM := ' AND h.order_number between to_number(:p_order_num_low) and to_number(:p_order_num_high) ';
245 END IF;
246 ELSIF (P_ORDER_NUM_LOW IS NOT NULL) THEN
247 LP_ORDER_NUM := 'and h.order_number >= to_number(:p_order_num_low) ';
248 ELSIF (P_ORDER_NUM_HIGH IS NOT NULL) THEN
249 LP_ORDER_NUM := 'and h.order_number <= to_number(:p_order_num_high) ';
250 ELSE
251 LP_ORDER_NUM := ' ';
252 END IF;
253 P_ORGANIZATION_ID := NVL(P_ORGANIZATION_ID
254 ,0);
255 SELECT
256 USERENV('LANG')
257 INTO P_LANG
258 FROM
259 DUAL;
260 IF P_SALESREP_LOW IS NOT NULL AND P_SALESREP_HIGH IS NOT NULL THEN
261 IF (P_SALESREP_LOW = P_SALESREP_HIGH) THEN
262 LP_SALESREP := ' and sr.name = :p_salesrep_low ';
263 ELSE
264 LP_SALESREP := ' AND nvl(sr.name,''zzzzzz'') between :p_salesrep_low and :p_salesrep_high ';
265 END IF;
266 ELSIF (P_SALESREP_LOW IS NOT NULL) THEN
267 LP_SALESREP := 'and sr.name >= :p_salesrep_low ';
268 ELSIF (P_SALESREP_HIGH IS NOT NULL) THEN
269 LP_SALESREP := 'and sr.name <= :p_salesrep_high ';
270 ELSE
271 LP_SALESREP := ' ';
272 END IF;
273 IF P_ORDER_DATE_LOW IS NOT NULL AND P_ORDER_DATE_HIGH IS NOT NULL THEN
274 LP_ORDER_DATE := ' AND h.ordered_date >= :p_order_date_low and h.ordered_date < :p_order_date_high + 1';
275 ELSIF (P_ORDER_DATE_LOW IS NOT NULL) THEN
276 LP_ORDER_DATE := ' and h.ordered_date >= :p_order_date_low';
277 ELSIF (P_ORDER_DATE_HIGH IS NOT NULL) THEN
278 LP_ORDER_DATE := ' and h.ordered_date < :p_order_date_high + 1';
279 ELSE
280 LP_ORDER_DATE := ' ';
281 END IF;
282 IF P_CUSTOMER_NAME_LOW IS NOT NULL AND P_CUSTOMER_NAME_HIGH IS NOT NULL THEN
283 IF (P_CUSTOMER_NAME_LOW = P_CUSTOMER_NAME_HIGH) THEN
284 LP_CUSTOMER_NAME := 'and party.party_name = :p_customer_name_low ';
285 ELSE
286 LP_CUSTOMER_NAME := ' AND party.party_name between :p_customer_name_low and :p_customer_name_high ';
287 END IF;
288 ELSIF (P_CUSTOMER_NAME_LOW IS NOT NULL) THEN
289 LP_CUSTOMER_NAME := 'and party.party_name >= :p_customer_name_low ';
290 ELSIF (P_CUSTOMER_NAME_HIGH IS NOT NULL) THEN
291 LP_CUSTOMER_NAME := 'and party.party_name <= :p_customer_name_high ';
292 ELSE
293 LP_CUSTOMER_NAME := ' ';
294 END IF;
295 IF P_CUSTOMER_NUM_LOW IS NOT NULL AND P_CUSTOMER_NUM_HIGH IS NOT NULL THEN
296 IF (P_CUSTOMER_NUM_LOW = P_CUSTOMER_NUM_HIGH) THEN
297 LP_CUSTOMER_NUM := 'and cust_acct.account_number = :p_customer_num_low ';
298 ELSE
299 LP_CUSTOMER_NUM := 'and cust_acct.account_number between :p_customer_num_low and :p_customer_num_high ';
300 END IF;
301 ELSIF (P_CUSTOMER_NUM_LOW IS NOT NULL) THEN
302 LP_CUSTOMER_NUM := ' and cust_acct.account_number >= :p_customer_num_low';
303 ELSIF (P_CUSTOMER_NUM_HIGH IS NOT NULL) THEN
304 LP_CUSTOMER_NUM := ' and cust_acct.account_number <= :p-customer_num_high';
305 ELSE
306 LP_CUSTOMER_NUM := ' ';
307 END IF;
308 IF P_ORDER_TYPE IS NOT NULL THEN
309 LP_ORDER_TYPE := ' and ot.transaction_type_id = :p_order_type ';
310 SELECT
311 OEOT.NAME
312 INTO L_ORDER_TYPE
313 FROM
314 OE_TRANSACTION_TYPES_TL OEOT
315 WHERE OEOT.TRANSACTION_TYPE_ID = P_ORDER_TYPE
316 AND OEOT.LANGUAGE = USERENV('LANG');
317 ELSE
318 LP_ORDER_TYPE := ' ';
319 END IF;
320 IF P_AGREEMENT IS NOT NULL THEN
321 LP_AGREEMENT := ' and t.agreement_id = :p_agreement ';
322 ELSE
323 LP_AGREEMENT := ' ';
324 END IF;
325 IF P_OPEN_ORDERS_ONLY = 'Y' THEN
326 LP_OPEN_ORDERS_ONLY := ' and h.open_flag = ''Y'' ';
327 ELSE
328 LP_OPEN_ORDERS_ONLY := ' ';
329 END IF;
330 IF P_LINE_TYPE IS NOT NULL THEN
331 LP_LINE_TYPE := ' and lt.line_type_id = :p_line_type ';
332 SELECT
333 OEOT.NAME
334 INTO L_LINE_TYPE
335 FROM
336 OE_TRANSACTION_TYPES_TL OEOT
337 WHERE OEOT.TRANSACTION_TYPE_ID = P_LINE_TYPE
338 AND OEOT.LANGUAGE = USERENV('LANG');
339 ELSE
340 LP_LINE_TYPE := ' ';
341 END IF;
342 IF P_ORDER_NUM_LOW = P_ORDER_NUM_HIGH THEN
343 LP_ORDER_CATEGORY := ' ';
344 ELSE
345 IF P_ORDER_CATEGORY IS NOT NULL THEN
346 IF P_ORDER_CATEGORY = 'f' THEN
347 LP_ORDER_CATEGORY := 'and h.order_category_code in (''ORDER'', ''MIXED'') ';
348 ELSIF P_ORDER_CATEGORY = 'CREDIT' THEN
349 LP_ORDER_CATEGORY := 'and h.order_category_code in (''RETURN'', ''MIXED'') ';
350 ELSIF P_ORDER_CATEGORY = 'ALL' THEN
351 LP_ORDER_CATEGORY := ' ';
352 ELSE
353 LP_ORDER_CATEGORY := ' ';
354 END IF;
355 ELSE
356 LP_ORDER_CATEGORY := 'and h.order_category_code in (''ORDER'', ''MIXED'') ';
357 END IF;
358 END IF;
359 IF P_LINE_CATEGORY IS NOT NULL THEN
360 IF P_LINE_CATEGORY = 'SALES' THEN
361 LP_LINE_CATEGORY := 'and l.line_category_code = ''ORDER'' ';
362 ELSIF P_LINE_CATEGORY = 'CREDIT' THEN
363 LP_LINE_CATEGORY := 'and l.line_category_code = ''RETURN'' ';
364 ELSIF P_LINE_CATEGORY = 'ALL' THEN
365 LP_LINE_CATEGORY := ' ';
366 END IF;
367 ELSE
368 LP_LINE_CATEGORY := 'and l.line_category_code = ''ORDER'' ';
369 END IF;
370 END;
371 RETURN (TRUE);
372 END AFTERPFORM;
373
374 FUNCTION C_SET_LBLFORMULA RETURN VARCHAR2 IS
375 BEGIN
376 BEGIN
377 /*SRW.REFERENCE(P_ORDER_BY)*/NULL;
378 IF P_CUSTOMER_NAME_LOW IS NOT NULL OR P_CUSTOMER_NAME_HIGH IS NOT NULL THEN
379 RP_CUSTOMER_RANGE := 'From ' || NVL(SUBSTR(P_CUSTOMER_NAME_LOW
380 ,1
381 ,16)
382 ,' ') || ' To ' || NVL(SUBSTR(P_CUSTOMER_NAME_HIGH
383 ,1
384 ,16)
385 ,' ');
386 END IF;
387 IF P_CUSTOMER_NUM_LOW IS NOT NULL OR P_CUSTOMER_NUM_HIGH IS NOT NULL THEN
388 RP_CUSTOMER_NUM_RANGE := 'From ' || NVL(P_CUSTOMER_NUM_LOW
389 ,' ') || 'To ' || NVL(P_CUSTOMER_NUM_HIGH
390 ,' ');
391 END IF;
392 IF P_SALESREP_LOW IS NOT NULL OR P_SALESREP_HIGH IS NOT NULL THEN
393 RP_SALESPERSON_RANGE := 'From ' || NVL(SUBSTR(P_SALESREP_LOW
394 ,1
395 ,16)
396 ,' ') || ' To ' || NVL(SUBSTR(P_SALESREP_HIGH
397 ,1
398 ,16)
399 ,' ');
400 END IF;
401 IF P_ORDER_DATE_LOW IS NOT NULL AND P_ORDER_DATE_HIGH IS NOT NULL THEN
402 RP_ORDER_DATE_RANGE := 'From ' || P_ORDER_DATE_LOW || ' To ' || P_ORDER_DATE_HIGH;
403 ELSIF P_ORDER_DATE_LOW IS NOT NULL THEN
404 RP_ORDER_DATE_RANGE := 'From ' || P_ORDER_DATE_LOW || ' To ' || ' ';
405 ELSIF P_ORDER_DATE_HIGH IS NOT NULL THEN
406 RP_ORDER_DATE_RANGE := 'From ' || ' ' || ' To ' || P_ORDER_DATE_HIGH;
407 END IF;
408 IF P_ORDER_NUM_LOW IS NOT NULL OR P_ORDER_NUM_HIGH IS NOT NULL THEN
409 RP_ORDER_RANGE := 'From ' || NVL(P_ORDER_NUM_LOW
410 ,' ') || ' To ' || NVL(P_ORDER_NUM_HIGH
411 ,' ');
412 END IF;
413 IF P_ORDER_BY IS NOT NULL THEN
414 DECLARE
415 ORDER_BY VARCHAR2(80);
416 L_LOOKUP_TYPE VARCHAR2(30) := 'OEXOESOS ORDER BY';
417 L_LOOKUP_CODE VARCHAR2(30) := P_ORDER_BY;
418 BEGIN
419 SELECT
420 MEANING
421 INTO ORDER_BY
422 FROM
423 OE_LOOKUPS
424 WHERE LOOKUP_TYPE = L_LOOKUP_TYPE
425 AND LOOKUP_CODE = L_LOOKUP_CODE;
426 RP_ORDER_BY := ORDER_BY;
427 EXCEPTION
428 WHEN NO_DATA_FOUND THEN
429 RP_ORDER_BY := P_ORDER_BY;
430 END;
431 END IF;
432 IF P_OPEN_ORDERS_ONLY IS NOT NULL THEN
433 DECLARE
434 MEANING VARCHAR2(80);
435 L_LOOKUP_TYPE VARCHAR2(30) := 'YES_NO';
436 L_LOOKUP_CODE VARCHAR2(30) := NVL(P_OPEN_ORDERS_ONLY
437 ,'N');
438 BEGIN
439 SELECT
440 MEANING
441 INTO MEANING
442 FROM
443 FND_LOOKUPS
444 WHERE LOOKUP_TYPE = L_LOOKUP_TYPE
445 AND LOOKUP_CODE = L_LOOKUP_CODE;
446 RP_OPEN_ORDERS_ONLY := MEANING;
447 EXCEPTION
448 WHEN NO_DATA_FOUND THEN
449 RP_OPEN_ORDERS_ONLY := P_OPEN_ORDERS_ONLY;
450 END;
451 END IF;
452 DECLARE
453 MEANING VARCHAR2(80);
454 L_LOOKUP_TYPE VARCHAR2(30) := 'YES_NO';
455 L_LOOKUP_CODE VARCHAR2(30) := P_USE_FUNCTIONAL_CURRENCY;
456 BEGIN
457 SELECT
458 MEANING
459 INTO MEANING
460 FROM
461 FND_LOOKUPS
462 WHERE LOOKUP_TYPE = L_LOOKUP_TYPE
463 AND LOOKUP_CODE = L_LOOKUP_CODE;
464 RP_USE_FUNCTIONAL_CURRENCY := MEANING;
465 EXCEPTION
466 WHEN NO_DATA_FOUND THEN
467 RP_USE_FUNCTIONAL_CURRENCY := P_USE_FUNCTIONAL_CURRENCY;
468 END;
469 DECLARE
470 ITEM_DISPLAY_MEANING VARCHAR2(80);
471 L_LOOKUP_TYPE VARCHAR2(30) := 'ITEM_DISPLAY_CODE';
472 L_LOOKUP_CODE VARCHAR2(30) := P_PRINT_DESCRIPTION;
473 BEGIN
474 SELECT
475 MEANING
476 INTO ITEM_DISPLAY_MEANING
477 FROM
478 OE_LOOKUPS
479 WHERE LOOKUP_TYPE = L_LOOKUP_TYPE
480 AND LOOKUP_CODE = L_LOOKUP_CODE;
481 RP_FLEX_OR_DESC := ITEM_DISPLAY_MEANING;
482 END;
483 RETURN (1);
484 END;
485 RETURN NULL;
486 END C_SET_LBLFORMULA;
487
488 FUNCTION C_GL_CONV_RATEFORMULA(CURRENCY_CODE IN VARCHAR2
489 ,ORDERED_DATE IN DATE
490 ,CONVERSION_TYPE_CODE IN VARCHAR2
491 ,CONVERSION_RATE IN NUMBER) RETURN NUMBER IS
492 BEGIN
493 DECLARE
494 GL_RATE NUMBER;
495 BEGIN
496 /*SRW.REFERENCE(CURRENCY_CODE)*/NULL;
497 /*SRW.REFERENCE(ORDERED_DATE)*/NULL;
498 /*SRW.REFERENCE(CONVERSION_TYPE_CODE)*/NULL;
499 IF P_USE_FUNCTIONAL_CURRENCY = 'Y' THEN
500 IF CURRENCY_CODE = RP_FUNCTIONAL_CURRENCY THEN
501 RETURN (1);
502 ELSE
503 IF CONVERSION_RATE IS NULL THEN
504 GL_RATE := GET_RATE(P_SOB_ID
505 ,CURRENCY_CODE
506 ,TRUNC(ORDERED_DATE)
507 ,CONVERSION_TYPE_CODE);
508 RETURN (GL_RATE);
509 ELSE
510 RETURN (CONVERSION_RATE);
511 END IF;
512 END IF;
513 ELSE
514 RETURN (1);
515 END IF;
516 EXCEPTION
517 WHEN NO_DATA_FOUND THEN
518 RETURN (-1);
519 WHEN OTHERS THEN
520 RETURN (-1);
521 END;
522 RETURN NULL;
523 END C_GL_CONV_RATEFORMULA;
524
525 FUNCTION C_PRICEFORMULA(EXTENDED_PRICE IN NUMBER
526 ,C_GL_CONV_RATE IN NUMBER) RETURN NUMBER IS
527 BEGIN
528 DECLARE
529 AMOUNT NUMBER(14,2);
530 BEGIN
531 /*SRW.REFERENCE(EXTENDED_PRICE)*/NULL;
532 /*SRW.REFERENCE(C_GL_CONV_RATE)*/NULL;
533 IF P_USE_FUNCTIONAL_CURRENCY = 'Y' THEN
534 IF C_GL_CONV_RATE <> -1 THEN
535 SELECT
536 C_GL_CONV_RATE * EXTENDED_PRICE
537 INTO AMOUNT
538 FROM
539 DUAL;
540 RETURN (AMOUNT);
541 ELSE
542 RETURN (0);
543 END IF;
544 ELSE
545 RETURN (EXTENDED_PRICE);
546 END IF;
547 END;
548 RETURN NULL;
549 END C_PRICEFORMULA;
550
551 FUNCTION C_CURRENCY_CODEFORMULA(CURRENCY_CODE IN VARCHAR2) RETURN VARCHAR2 IS
552 BEGIN
553 /*SRW.REFERENCE(CURRENCY_CODE)*/NULL;
554 RP_DATA_FOUND := 'X';
555 IF P_USE_FUNCTIONAL_CURRENCY = 'Y' THEN
556 RETURN (RP_FUNCTIONAL_CURRENCY);
557 ELSE
558 RETURN (CURRENCY_CODE);
559 END IF;
560 RETURN NULL;
561 END C_CURRENCY_CODEFORMULA;
562
563 FUNCTION C_DISCOUNT_CURRFORMULA(S_SELLP_CURR IN NUMBER
564 ,S_LISTP_CURR IN NUMBER
565 ,S_SELLP_CURR_RMA IN NUMBER
566 ,S_LISTP_CURR_RMA IN NUMBER) RETURN NUMBER IS
567 BEGIN
568 DECLARE
569 DISCOUNT NUMBER;
570 BEGIN
571 /*SRW.REFERENCE(S_SELLP_CURR)*/NULL;
572 /*SRW.REFERENCE(S_LISTP_CURR)*/NULL;
573 /*SRW.REFERENCE(S_SELLP_CURR_RMA)*/NULL;
574 /*SRW.REFERENCE(S_LISTP_CURR_RMA)*/NULL;
575 IF S_LISTP_CURR - 2 * S_LISTP_CURR_RMA <> 0 THEN
576 SELECT
577 ( ( S_LISTP_CURR - 2 * S_LISTP_CURR_RMA ) - ( S_SELLP_CURR - 2 * S_SELLP_CURR_RMA ) ) / ( S_LISTP_CURR - 2 * S_LISTP_CURR_RMA ) * 100
578 INTO DISCOUNT
579 FROM
580 DUAL;
581 RETURN (DISCOUNT);
582 ELSE
583 RETURN (0);
584 END IF;
585 END;
586 RETURN NULL;
587 END C_DISCOUNT_CURRFORMULA;
588
589 FUNCTION C_DISCOUNT_SRFORMULA(S_SELLP_SR IN NUMBER
590 ,S_LISTP_SR IN NUMBER
591 ,S_SELLP_SR_RMA IN NUMBER
592 ,S_LISTP_SR_RMA IN NUMBER) RETURN NUMBER IS
593 BEGIN
594 DECLARE
595 DISCOUNT NUMBER;
596 BEGIN
597 /*SRW.REFERENCE(S_SELLP_SR)*/NULL;
598 /*SRW.REFERENCE(S_LISTP_SR)*/NULL;
599 /*SRW.REFERENCE(S_SELLP_SR_RMA)*/NULL;
600 /*SRW.REFERENCE(S_LISTP_SR_RMA)*/NULL;
601 IF S_LISTP_SR - 2 * S_LISTP_SR_RMA <> 0 THEN
602 SELECT
603 ( ( S_LISTP_SR - 2 * S_LISTP_SR_RMA ) - ( S_SELLP_SR - 2 * S_SELLP_SR_RMA ) ) / ( S_LISTP_SR - 2 * S_LISTP_SR_RMA ) * 100
604 INTO DISCOUNT
605 FROM
606 DUAL;
607 RETURN (DISCOUNT);
608 ELSE
609 RETURN (0);
610 END IF;
611 END;
612 RETURN NULL;
613 END C_DISCOUNT_SRFORMULA;
614
615 FUNCTION C_DISCOUNT_CUSTFORMULA(S_SELLP_CUST IN NUMBER
616 ,S_LISTP_CUST IN NUMBER) RETURN NUMBER IS
617 BEGIN
618 DECLARE
619 DISCOUNT NUMBER;
620 BEGIN
621 /*SRW.REFERENCE(S_SELLP_CUST)*/NULL;
622 /*SRW.REFERENCE(S_LISTP_CUST)*/NULL;
623 IF S_LISTP_CUST <> 0 THEN
624 SELECT
625 ( S_LISTP_CUST - S_SELLP_CUST ) / S_LISTP_CUST * 100
626 INTO DISCOUNT
627 FROM
628 DUAL;
629 RETURN (DISCOUNT);
630 ELSE
631 RETURN (0);
632 END IF;
633 END;
634 RETURN NULL;
635 END C_DISCOUNT_CUSTFORMULA;
636
637 FUNCTION C_SALE_PRICEFORMULA(SALE_PRICE IN NUMBER
638 ,C_GL_CONV_RATE IN NUMBER) RETURN NUMBER IS
639 BEGIN
640 DECLARE
641 AMOUNT NUMBER(14,2);
642 BEGIN
643 /*SRW.REFERENCE(SALE_PRICE)*/NULL;
644 /*SRW.REFERENCE(C_GL_CONV_RATE)*/NULL;
645 IF P_USE_FUNCTIONAL_CURRENCY = 'Y' THEN
646 IF C_GL_CONV_RATE <> -1 THEN
647 SELECT
648 C_GL_CONV_RATE * SALE_PRICE
649 INTO AMOUNT
650 FROM
651 DUAL;
652 RETURN (AMOUNT);
653 ELSE
654 RETURN (0);
655 END IF;
656 ELSE
657 RETURN (SALE_PRICE);
658 END IF;
659 END;
660 RETURN NULL;
661 END C_SALE_PRICEFORMULA;
662
663 FUNCTION C_DISCOUNT_ORDERFORMULA(S_SELLP_ORDER IN NUMBER
664 ,S_LISTP_ORDER IN NUMBER) RETURN NUMBER IS
665 BEGIN
666 DECLARE
667 DISCOUNT NUMBER;
668 BEGIN
669 /*SRW.REFERENCE(S_SELLP_ORDER)*/NULL;
670 /*SRW.REFERENCE(S_LISTP_ORDER)*/NULL;
671 IF S_LISTP_ORDER <> 0 THEN
672 SELECT
673 ( S_LISTP_ORDER - S_SELLP_ORDER ) / S_LISTP_ORDER * 100
674 INTO DISCOUNT
675 FROM
676 DUAL;
677 RETURN (DISCOUNT);
678 ELSE
679 RETURN (0);
680 END IF;
681 END;
682 RETURN NULL;
683 END C_DISCOUNT_ORDERFORMULA;
684
685 FUNCTION C_SALESREP_TOTAL_NETFORMULA RETURN NUMBER IS
686 BEGIN
687 RETURN NULL;
688 END C_SALESREP_TOTAL_NETFORMULA;
689
690 FUNCTION S_PRICE_CURR_NETFORMULA(S_PRICE_CURR IN NUMBER
691 ,S_PRICE_CURR_RMA IN NUMBER) RETURN NUMBER IS
692 BEGIN
693 DECLARE
694 TOTAL_NET NUMBER;
695 BEGIN
696 /*SRW.REFERENCE(S_PRICE_CURR)*/NULL;
697 /*SRW.REFERENCE(S_PRICE_CURR_RMA)*/NULL;
698 SELECT
699 ( S_PRICE_CURR - 2 * S_PRICE_CURR_RMA )
700 INTO TOTAL_NET
701 FROM
702 DUAL;
703 RETURN (TOTAL_NET);
704 END;
705 RETURN NULL;
706 END S_PRICE_CURR_NETFORMULA;
707
708 FUNCTION RP_ORDER_CATEGORYFORMULA RETURN VARCHAR2 IS
709 BEGIN
710 DECLARE
711 L_MEANING VARCHAR2(80);
712 L_LOOKUP_TYPE VARCHAR2(30) := 'REPORT_ORDER_CATEGORY';
713 L_LOOKUP_CODE VARCHAR2(30) := P_ORDER_CATEGORY;
714 BEGIN
715 SELECT
716 MEANING
717 INTO L_MEANING
718 FROM
719 OE_LOOKUPS
720 WHERE LOOKUP_TYPE = L_LOOKUP_TYPE
721 AND LOOKUP_CODE = L_LOOKUP_CODE;
722 RETURN (L_MEANING);
723 EXCEPTION
724 WHEN NO_DATA_FOUND THEN
725 RETURN (NULL);
726 END;
727 RETURN (NULL);
728 END RP_ORDER_CATEGORYFORMULA;
729
730 FUNCTION RP_LINE_CATEGORYFORMULA RETURN VARCHAR2 IS
731 BEGIN
732 DECLARE
733 L_MEANING VARCHAR2(80);
734 L_LOOKUP_TYPE VARCHAR2(30) := 'REPORT_LINE_DISPLAY';
735 L_LOOKUP_CODE VARCHAR2(30) := P_LINE_CATEGORY;
736 BEGIN
737 SELECT
738 MEANING
739 INTO L_MEANING
740 FROM
741 OE_LOOKUPS
742 WHERE LOOKUP_TYPE = L_LOOKUP_TYPE
743 AND LOOKUP_CODE = L_LOOKUP_CODE;
744 RETURN (L_MEANING);
745 EXCEPTION
746 WHEN NO_DATA_FOUND THEN
747 RETURN (NULL);
748 END;
749 RETURN NULL;
750 END RP_LINE_CATEGORYFORMULA;
751
752 FUNCTION C_COUNT_LINEFORMULA(HEADER_ID IN NUMBER) RETURN NUMBER IS
753 BEGIN
754 DECLARE
755 LINE_NUM NUMBER;
756 BEGIN
757 SELECT
758 count(SC.LINE_ID)
759 INTO LINE_NUM
760 FROM
761 OE_SALES_CREDITS SC
762 WHERE SC.HEADER_ID = C_COUNT_LINEFORMULA.HEADER_ID;
763 RETURN (LINE_NUM);
764 END;
765 RETURN NULL;
766 END C_COUNT_LINEFORMULA;
767
768 FUNCTION C_COUNT_SALESREPFORMULA(HEADER_ID IN NUMBER) RETURN NUMBER IS
769 BEGIN
770 DECLARE
771 SALESREP_NUM NUMBER;
772 BEGIN
773 IF (G_HEADER_SC_TBL.EXISTS(MOD(HEADER_ID
774 ,G_BINARY_LIMIT))) THEN
775 /*SRW.MESSAGE(1
776 ,'In c_count_salesrep: header_id ' || HEADER_ID || ' exists')*/NULL;
777 SALESREP_NUM := G_HEADER_SC_TBL(MOD(HEADER_ID
778 ,G_BINARY_LIMIT)).COUNT_SALESREP;
779 /*SRW.MESSAGE(1
780 ,'In c_count_salesrep: salesrep_num ' || SALESREP_NUM)*/NULL;
781 ELSE
782 SELECT
783 count(SC.SALESREP_ID)
784 INTO SALESREP_NUM
785 FROM
786 OE_SALES_CREDITS SC
787 WHERE SC.HEADER_ID = C_COUNT_SALESREPFORMULA.HEADER_ID;
788 G_HEADER_SC_TBL(MOD(HEADER_ID
789 ,G_BINARY_LIMIT)).HEADER_ID := HEADER_ID;
790 G_HEADER_SC_TBL(MOD(HEADER_ID
791 ,G_BINARY_LIMIT)).COUNT_SALESREP := SALESREP_NUM;
792 END IF;
793 RETURN (SALESREP_NUM);
794 END;
795 RETURN NULL;
796 END C_COUNT_SALESREPFORMULA;
797
798 FUNCTION S_SALESREP_TOTAL_NETFORMULA(C_SALESREP_TOTAL IN NUMBER
799 ,C_SALESREP_TOTAL_RMA IN NUMBER) RETURN NUMBER IS
800 BEGIN
801 DECLARE
802 TOTAL_NET NUMBER;
803 BEGIN
804 /*SRW.REFERENCE(C_SALESREP_TOTAL)*/NULL;
805 /*SRW.REFERENCE(C_SALESREP_TOTAL_RMA)*/NULL;
806 SELECT
807 ( C_SALESREP_TOTAL - 2 * C_SALESREP_TOTAL_RMA )
808 INTO TOTAL_NET
809 FROM
810 DUAL;
811 RETURN (TOTAL_NET);
812 END;
813 RETURN NULL;
814 END S_SALESREP_TOTAL_NETFORMULA;
815
816 FUNCTION C_CHARGE_PERIODICITYFORMULA(CHARGE_PERIODICITY_CODE IN VARCHAR2) RETURN CHAR IS
817 L_CHARGE_PERIODICITY VARCHAR2(25);
818 BEGIN
819 IF CHARGE_PERIODICITY_CODE IS NOT NULL THEN
820 SELECT
821 UNIT_OF_MEASURE
822 INTO L_CHARGE_PERIODICITY
823 FROM
824 MTL_UNITS_OF_MEASURE_VL
825 WHERE UOM_CODE = CHARGE_PERIODICITY_CODE
826 AND UOM_CLASS = FND_PROFILE.VALUE('ONT_UOM_CLASS_CHARGE_PERIODICITY');
827 RETURN L_CHARGE_PERIODICITY;
828 ELSE
829 RETURN (P_CHARGE_PERIODICITY);
830 END IF;
831 RETURN NULL;
832 EXCEPTION
833 WHEN NO_DATA_FOUND THEN
834 RETURN NULL;
835 END C_CHARGE_PERIODICITYFORMULA;
836
837 FUNCTION C_DISCOUNT_PERIODICITYFORMULA(S_SELLP_PERIODICITY IN NUMBER
838 ,S_LISTP_PERIODICITY IN NUMBER) RETURN NUMBER IS
839 BEGIN
840 DECLARE
841 DISCOUNT NUMBER;
842 BEGIN
843 /*SRW.REFERENCE(S_SELLP_PERIODICITY)*/NULL;
844 /*SRW.REFERENCE(S_LISTP_PERIODICITY)*/NULL;
845 IF S_LISTP_PERIODICITY <> 0 THEN
846 SELECT
847 ( S_LISTP_PERIODICITY - S_SELLP_PERIODICITY ) / S_LISTP_PERIODICITY * 100
848 INTO DISCOUNT
849 FROM
850 DUAL;
851 RETURN (DISCOUNT);
852 ELSE
853 RETURN (0);
854 END IF;
855 END;
856 RETURN NULL;
857 END C_DISCOUNT_PERIODICITYFORMULA;
858
859 FUNCTION RP_REPORT_NAME_P RETURN VARCHAR2 IS
860 BEGIN
861 RETURN RP_REPORT_NAME;
862 END RP_REPORT_NAME_P;
863
864 FUNCTION RP_SUB_TITLE_P RETURN VARCHAR2 IS
865 BEGIN
866 RETURN RP_SUB_TITLE;
867 END RP_SUB_TITLE_P;
868
869 FUNCTION RP_COMPANY_NAME_P RETURN VARCHAR2 IS
870 BEGIN
871 RETURN RP_COMPANY_NAME;
872 END RP_COMPANY_NAME_P;
873
874 FUNCTION RP_FUNCTIONAL_CURRENCY_P RETURN VARCHAR2 IS
875 BEGIN
876 RETURN RP_FUNCTIONAL_CURRENCY;
877 END RP_FUNCTIONAL_CURRENCY_P;
878
879 FUNCTION RP_DATA_FOUND_P RETURN VARCHAR2 IS
880 BEGIN
881 RETURN RP_DATA_FOUND;
882 END RP_DATA_FOUND_P;
883
884 FUNCTION RP_ITEM_FLEX_LPROMPT_P RETURN VARCHAR2 IS
885 BEGIN
886 RETURN RP_ITEM_FLEX_LPROMPT;
887 END RP_ITEM_FLEX_LPROMPT_P;
888
889 FUNCTION RP_ITEM_FLEX_ALL_SEG_P RETURN VARCHAR2 IS
890 BEGIN
891 RETURN RP_ITEM_FLEX_ALL_SEG;
892 END RP_ITEM_FLEX_ALL_SEG_P;
893
894 FUNCTION RP_ITEM_FLEX_APROMPT_P RETURN VARCHAR2 IS
895 BEGIN
896 RETURN RP_ITEM_FLEX_APROMPT;
897 END RP_ITEM_FLEX_APROMPT_P;
898
899 FUNCTION RP_SALES_REASON_LBL_P RETURN VARCHAR2 IS
900 BEGIN
901 RETURN RP_SALES_REASON_LBL;
902 END RP_SALES_REASON_LBL_P;
903
904 FUNCTION RP_CUSTOMER_RANGE_P RETURN VARCHAR2 IS
905 BEGIN
906 RETURN RP_CUSTOMER_RANGE;
907 END RP_CUSTOMER_RANGE_P;
908
909 FUNCTION RP_SALESPERSON_RANGE_P RETURN VARCHAR2 IS
910 BEGIN
911 RETURN RP_SALESPERSON_RANGE;
912 END RP_SALESPERSON_RANGE_P;
913
914 FUNCTION RP_ORDER_RANGE_P RETURN VARCHAR2 IS
915 BEGIN
916 RETURN RP_ORDER_RANGE;
917 END RP_ORDER_RANGE_P;
918
919 FUNCTION RP_ORDER_DATE_RANGE_P RETURN VARCHAR2 IS
920 BEGIN
921 RETURN RP_ORDER_DATE_RANGE;
922 END RP_ORDER_DATE_RANGE_P;
923
924 FUNCTION RP_SALES_REASON_LBL_2_P RETURN VARCHAR2 IS
925 BEGIN
926 RETURN RP_SALES_REASON_LBL_2;
927 END RP_SALES_REASON_LBL_2_P;
928
929 FUNCTION RP_ORDER_BY_P RETURN VARCHAR2 IS
930 BEGIN
931 RETURN RP_ORDER_BY;
932 END RP_ORDER_BY_P;
933
934 FUNCTION RP_OPEN_ORDERS_ONLY_P RETURN VARCHAR2 IS
935 BEGIN
936 RETURN RP_OPEN_ORDERS_ONLY;
937 END RP_OPEN_ORDERS_ONLY_P;
938
939 FUNCTION RP_USE_FUNCTIONAL_CURRENCY_P RETURN VARCHAR2 IS
940 BEGIN
941 RETURN RP_USE_FUNCTIONAL_CURRENCY;
942 END RP_USE_FUNCTIONAL_CURRENCY_P;
943
944 FUNCTION RP_FLEX_OR_DESC_P RETURN VARCHAR2 IS
945 BEGIN
946 RETURN RP_FLEX_OR_DESC;
947 END RP_FLEX_OR_DESC_P;
948
949 FUNCTION RP_AGREEMENT_NAME_P RETURN VARCHAR2 IS
950 BEGIN
951 RETURN RP_AGREEMENT_NAME;
952 END RP_AGREEMENT_NAME_P;
953
954 FUNCTION RP_CUSTOMER_NUM_RANGE_P RETURN VARCHAR2 IS
955 BEGIN
956 RETURN RP_CUSTOMER_NUM_RANGE;
957 END RP_CUSTOMER_NUM_RANGE_P;
958
959 FUNCTION IS_FIXED_RATE(X_FROM_CURRENCY IN VARCHAR2
960 ,X_TO_CURRENCY IN VARCHAR2
961 ,X_EFFECTIVE_DATE IN DATE) RETURN VARCHAR2 IS
962 X0 VARCHAR2(2000);
963 BEGIN
964 /*STPROC.INIT('begin :X0 := GL_CURRENCY_API.IS_FIXED_RATE(:X_FROM_CURRENCY, :X_TO_CURRENCY, :X_EFFECTIVE_DATE); end;');
965 STPROC.BIND_O(X0);
966 STPROC.BIND_I(X_FROM_CURRENCY);
967 STPROC.BIND_I(X_TO_CURRENCY);
968 STPROC.BIND_I(X_EFFECTIVE_DATE);
969 STPROC.EXECUTE;
970 STPROC.RETRIEVE(1
971 ,X0);
972 RETURN X0;*/
973 RETURN(NULL);
974 END IS_FIXED_RATE;
975
976 PROCEDURE GET_RELATION(X_FROM_CURRENCY IN VARCHAR2
977 ,X_TO_CURRENCY IN VARCHAR2
978 ,X_EFFECTIVE_DATE IN DATE
979 ,X_FIXED_RATE IN OUT NOCOPY BOOLEAN
980 ,X_RELATIONSHIP IN OUT NOCOPY VARCHAR2) IS
981 BEGIN
982 /*STPROC.INIT('declare X_FIXED_RATE BOOLEAN;
983 begin X_FIXED_RATE := sys.diutil.int_to_bool(:X_FIXED_RATE);
984 GL_CURRENCY_API.GET_RELATION(:X_FROM_CURRENCY, :X_TO_CURRENCY, :X_EFFECTIVE_DATE, X_FIXED_RATE,
985 :X_RELATIONSHIP); :X_FIXED_RATE := sys.diutil.bool_to_int(X_FIXED_RATE); end;');
986 STPROC.BIND_IO(X_FIXED_RATE);
987 STPROC.BIND_I(X_FROM_CURRENCY);
988 STPROC.BIND_I(X_TO_CURRENCY);
989 STPROC.BIND_I(X_EFFECTIVE_DATE);
990 STPROC.BIND_IO(X_RELATIONSHIP);
991 STPROC.EXECUTE;
992 STPROC.RETRIEVE(1
993 ,X_FIXED_RATE);
994 STPROC.RETRIEVE(5
995 ,X_RELATIONSHIP);*/
996 NULL;
997 END GET_RELATION;
998
999 FUNCTION GET_EURO_CODE RETURN VARCHAR2 IS
1000 X0 VARCHAR2(2000);
1001 BEGIN
1002 /*(STPROC.INIT('begin :X0 := GL_CURRENCY_API.GET_EURO_CODE; end;');
1003 STPROC.BIND_O(X0);
1004 STPROC.EXECUTE;
1005 STPROC.RETRIEVE(1
1006 ,X0);
1007 RETURN X0;*/
1008 RETURN (NULL);
1009 END GET_EURO_CODE;
1010
1011 FUNCTION GET_RATE(X_FROM_CURRENCY IN VARCHAR2
1012 ,X_TO_CURRENCY IN VARCHAR2
1013 ,X_CONVERSION_DATE IN DATE
1014 ,X_CONVERSION_TYPE IN VARCHAR2) RETURN NUMBER IS
1015 X0 NUMBER;
1016 BEGIN
1017 /*STPROC.INIT('begin :X0 := GL_CURRENCY_API.GET_RATE(:X_FROM_CURRENCY, :X_TO_CURRENCY, :X_CONVERSION_DATE, :X_CONVERSION_TYPE); end;');
1018 STPROC.BIND_O(X0);
1019 STPROC.BIND_I(X_FROM_CURRENCY);
1020 STPROC.BIND_I(X_TO_CURRENCY);
1021 STPROC.BIND_I(X_CONVERSION_DATE);
1022 STPROC.BIND_I(X_CONVERSION_TYPE);
1023 STPROC.EXECUTE;
1024 STPROC.RETRIEVE(1
1025 ,X0);
1026 RETURN X0;*/
1027 RETURN (NULL);
1028 END GET_RATE;
1029
1030 FUNCTION GET_RATE(X_SET_OF_BOOKS_ID IN NUMBER
1031 ,X_FROM_CURRENCY IN VARCHAR2
1032 ,X_CONVERSION_DATE IN DATE
1033 ,X_CONVERSION_TYPE IN VARCHAR2) RETURN NUMBER IS
1034 X0 NUMBER;
1035 BEGIN
1036 /*STPROC.INIT('begin :X0 := GL_CURRENCY_API.GET_RATE(:X_SET_OF_BOOKS_ID, :X_FROM_CURRENCY, :X_CONVERSION_DATE, :X_CONVERSION_TYPE); end;');
1037 STPROC.BIND_O(X0);
1038 STPROC.BIND_I(X_SET_OF_BOOKS_ID);
1039 STPROC.BIND_I(X_FROM_CURRENCY);
1040 STPROC.BIND_I(X_CONVERSION_DATE);
1041 STPROC.BIND_I(X_CONVERSION_TYPE);
1042 STPROC.EXECUTE;
1043 STPROC.RETRIEVE(1
1044 ,X0);
1045 RETURN X0; */
1046 RETURN (NULL);
1047 END GET_RATE;
1048
1049 FUNCTION CONVERT_AMOUNT(X_FROM_CURRENCY IN VARCHAR2
1050 ,X_TO_CURRENCY IN VARCHAR2
1051 ,X_CONVERSION_DATE IN DATE
1052 ,X_CONVERSION_TYPE IN VARCHAR2
1053 ,X_AMOUNT IN NUMBER) RETURN NUMBER IS
1054 X0 NUMBER;
1055 BEGIN
1056 /*STPROC.INIT('begin :X0 := GL_CURRENCY_API.CONVERT_AMOUNT(:X_FROM_CURRENCY, :X_TO_CURRENCY, :X_CONVERSION_DATE, :X_CONVERSION_TYPE, :X_AMOUNT); end;');
1057 STPROC.BIND_O(X0);
1058 STPROC.BIND_I(X_FROM_CURRENCY);
1059 STPROC.BIND_I(X_TO_CURRENCY);
1060 STPROC.BIND_I(X_CONVERSION_DATE);
1061 STPROC.BIND_I(X_CONVERSION_TYPE);
1062 STPROC.BIND_I(X_AMOUNT);
1063 STPROC.EXECUTE;
1064 STPROC.RETRIEVE(1
1065 ,X0);
1066 RETURN X0;*/
1067 RETURN (NULL);
1068
1069 END CONVERT_AMOUNT;
1070
1071 FUNCTION CONVERT_AMOUNT(X_SET_OF_BOOKS_ID IN NUMBER
1072 ,X_FROM_CURRENCY IN VARCHAR2
1073 ,X_CONVERSION_DATE IN DATE
1074 ,X_CONVERSION_TYPE IN VARCHAR2
1075 ,X_AMOUNT IN NUMBER) RETURN NUMBER IS
1076 X0 NUMBER;
1077 BEGIN
1078 /*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;');
1079 STPROC.BIND_O(X0);
1080 STPROC.BIND_I(X_SET_OF_BOOKS_ID);
1081 STPROC.BIND_I(X_FROM_CURRENCY);
1082 STPROC.BIND_I(X_CONVERSION_DATE);
1083 STPROC.BIND_I(X_CONVERSION_TYPE);
1084 STPROC.BIND_I(X_AMOUNT);
1085 STPROC.EXECUTE;
1086 STPROC.RETRIEVE(1
1087 ,X0);
1088 RETURN X0;*/
1089 RETURN (NULL);
1090 END CONVERT_AMOUNT;
1091
1092 FUNCTION GET_DERIVE_TYPE(SOB_ID IN NUMBER
1093 ,PERIOD IN VARCHAR2
1094 ,CURR_CODE IN VARCHAR2) RETURN VARCHAR2 IS
1095 X0 VARCHAR2(2000);
1096 BEGIN
1097 /*STPROC.INIT('begin :X0 := GL_CURRENCY_API.GET_DERIVE_TYPE(:SOB_ID, :PERIOD, :CURR_CODE); end;');
1098 STPROC.BIND_O(X0);
1099 STPROC.BIND_I(SOB_ID);
1100 STPROC.BIND_I(PERIOD);
1101 STPROC.BIND_I(CURR_CODE);
1102 STPROC.EXECUTE;
1103 STPROC.RETRIEVE(1
1104 ,X0);
1105 RETURN X0;*/
1106 RETURN (NULL);
1107 END GET_DERIVE_TYPE;
1108
1109 FUNCTION F_PERIODICITYFORMATTRIGGER RETURN VARCHAR2 IS
1110 BEGIN
1111 IF OE_SYS_PARAMETERS.VALUE ('RECURRING_CHARGES',MO_GLOBAL.GET_CURRENT_ORG_ID()) = 'Y' THEN
1112 RETURN ('TRUE');
1113 ELSE
1114 RETURN ('FALSE');
1115 END IF;
1116 RETURN ('FALSE');
1117 END;
1118
1119 END ONT_OEXOESOS_XMLP_PKG;
1120
1121
[Home] [Help]