[Home] [Help]
PACKAGE BODY: APPS.ONT_OEXOECOD_XMLP_PKG
Source
1 PACKAGE BODY ONT_OEXOECOD_XMLP_PKG AS
2 /* $Header: OEXOECODB.pls 120.2 2008/05/05 09:02:28 dwkrishn noship $ */
3 FUNCTION C_ORDER_BY_DISPLAYFORMULA RETURN VARCHAR2 IS
4 BEGIN
5 DECLARE
6 ORDER_BY_DISPLAY VARCHAR2(80);
7 BEGIN
8 SELECT
9 MEANING
10 INTO ORDER_BY_DISPLAY
11 FROM
12 OE_LOOKUPS
13 WHERE LOOKUP_TYPE = 'OEXOAPOA ORDER BY'
14 AND LOOKUP_CODE = P_ORDER_BY;
15 RETURN (ORDER_BY_DISPLAY);
16 END;
17 RETURN NULL;
18 END C_ORDER_BY_DISPLAYFORMULA;
19
20 FUNCTION AFTERREPORT RETURN BOOLEAN IS
21 BEGIN
22 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
23 RETURN (TRUE);
24 EXCEPTION
25 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
26 /*SRW.MESSAGE(1
27 ,'FAILED IN AFTER REPORT TRIGGER')*/NULL;
28 RETURN (FALSE);
29 END AFTERREPORT;
30
31 FUNCTION C_ORDER_NUM_WHERE RETURN VARCHAR2 IS
32 BEGIN
33 IF P_ORDER_NUM_LO IS NOT NULL AND P_ORDER_NUM_HI IS NOT NULL THEN
34 RETURN ('and h.order_number between to_number(''' || P_ORDER_NUM_LO || ''') and to_number(''' || P_ORDER_NUM_HI || ''') ');
35 ELSE
36 IF P_ORDER_NUM_LO IS NULL AND P_ORDER_NUM_HI IS NOT NULL THEN
37 RETURN ('and h.order_number <= to_number(''' || P_ORDER_NUM_HI || ''') ');
38 ELSE
39 IF P_ORDER_NUM_LO IS NOT NULL AND P_ORDER_NUM_HI IS NULL THEN
40 RETURN ('and h.order_number >= to_number(''' || P_ORDER_NUM_LO || ''') ');
41 ELSE
42 RETURN (NULL);
43 END IF;
44 END IF;
45 END IF;
46 RETURN NULL;
47 END C_ORDER_NUM_WHERE;
48
49 FUNCTION GET_SOB_NAME RETURN VARCHAR2 IS
50 SOB_NAME VARCHAR2(30);
51 BEGIN
52 SELECT
53 NAME
54 INTO SOB_NAME
55 FROM
56 GL_SETS_OF_BOOKS
57 WHERE SET_OF_BOOKS_ID = P_SOB_ID;
58 RETURN (SOB_NAME);
59 RETURN NULL;
60 EXCEPTION
61 WHEN NO_DATA_FOUND THEN
62 RETURN (NULL);
63 END GET_SOB_NAME;
64
65 FUNCTION C_OPEN_ORDERS_DISPLAYFORMULA RETURN VARCHAR2 IS
66 BEGIN
67 DECLARE
68 OPEN_ORDERS VARCHAR2(80);
69 BEGIN
70 SELECT
71 MEANING
72 INTO OPEN_ORDERS
73 FROM
74 OE_LOOKUPS
75 WHERE LOOKUP_TYPE = 'YES_NO'
76 AND LOOKUP_CODE = P_OPEN_ORDERS;
77 RETURN (OPEN_ORDERS);
78 EXCEPTION
79 WHEN NO_DATA_FOUND THEN
80 RETURN (NULL);
81 END;
82 RETURN NULL;
83 END C_OPEN_ORDERS_DISPLAYFORMULA;
84
85 FUNCTION CONTACT_PHONE(CONTACT_ID IN NUMBER) RETURN VARCHAR2 IS
86 PHONE VARCHAR2(25);
87 BEGIN
88 SELECT
89 MIN(PHONE_AREA_CODE || '-' || PHONE_NUMBER)
90 INTO PHONE
91 FROM
92 HZ_CONTACT_POINTS
93 WHERE CONTACT_POINT_ID = CONTACT_ID;
94 RETURN (PHONE);
95 RETURN NULL;
96 EXCEPTION
97 WHEN NO_DATA_FOUND THEN
98 RETURN (NULL);
99 END CONTACT_PHONE;
100
101 FUNCTION C_BASE_CURRENCYFORMULA RETURN VARCHAR2 IS
102 BEGIN
103 DECLARE
104 BASE_CURRENCY VARCHAR2(30);
105 BEGIN
106 SELECT
107 CURRENCY_CODE
108 INTO BASE_CURRENCY
109 FROM
110 GL_SETS_OF_BOOKS
111 WHERE SET_OF_BOOKS_ID = P_SOB_ID;
112 RETURN (BASE_CURRENCY);
113 EXCEPTION
114 WHEN OTHERS THEN
115 NULL;
116 END;
117 RETURN NULL;
118 END C_BASE_CURRENCYFORMULA;
119
120 FUNCTION C_SALES_CREDITS_DISPLAYFORMULA RETURN VARCHAR2 IS
121 BEGIN
122 DECLARE
123 SC_DISPLAY VARCHAR2(80);
124 BEGIN
125 SELECT
126 MEANING
127 INTO SC_DISPLAY
128 FROM
129 OE_LOOKUPS
130 WHERE LOOKUP_TYPE = 'YES_NO'
131 AND LOOKUP_CODE = P_SALES_CREDITS;
132 RETURN (SC_DISPLAY);
133 EXCEPTION
134 WHEN NO_DATA_FOUND THEN
135 RETURN (NULL);
136 END;
137 RETURN NULL;
138 END C_SALES_CREDITS_DISPLAYFORMULA;
139
140 FUNCTION C_ADJUSTMENTS_DISPLAYFORMULA RETURN VARCHAR2 IS
141 BEGIN
142 DECLARE
143 PA_DISPLAY VARCHAR2(80);
144 BEGIN
145 SELECT
146 MEANING
147 INTO PA_DISPLAY
148 FROM
149 OE_LOOKUPS
150 WHERE LOOKUP_TYPE = 'YES_NO'
151 AND LOOKUP_CODE = P_ADJUSTMENTS;
152 RETURN (PA_DISPLAY);
153 EXCEPTION
154 WHEN NO_DATA_FOUND THEN
155 RETURN (NULL);
156 END;
157 RETURN NULL;
158 END C_ADJUSTMENTS_DISPLAYFORMULA;
159
160 FUNCTION C_FUNCTIONAL_CURRENCY_DISPFORM RETURN VARCHAR2 IS
161 BEGIN
162 DECLARE
163 FC_DISPLAY VARCHAR2(80);
164 BEGIN
165 SELECT
166 MEANING
167 INTO FC_DISPLAY
168 FROM
169 OE_LOOKUPS
170 WHERE LOOKUP_TYPE = 'YES_NO'
171 AND LOOKUP_CODE = P_FUNCTIONAL_CURRENCY;
172 RETURN (FC_DISPLAY);
173 EXCEPTION
174 WHEN NO_DATA_FOUND THEN
175 RETURN (NULL);
176 END;
177 RETURN NULL;
178 END C_FUNCTIONAL_CURRENCY_DISPFORM;
179
180 FUNCTION C_GL_CONV_RATEFORMULA(CURRENCY1 IN VARCHAR2
181 ,C_BASE_CURRENCY IN VARCHAR2
182 ,CONVERSION_RATE IN NUMBER
183 ,ORDER_DATE IN DATE
184 ,CONVERSION_TYPE_CODE IN VARCHAR2) RETURN NUMBER IS
185 BEGIN
186 DECLARE
187 GL_RATE NUMBER;
188 BEGIN
189 IF P_FUNCTIONAL_CURRENCY = 'Y' THEN
190 IF CURRENCY1 = C_BASE_CURRENCY THEN
191 RETURN (1);
192 ELSE
193 IF CONVERSION_RATE IS NULL THEN
194 GL_RATE := GET_RATE(P_SOB_ID
195 ,CURRENCY1
196 ,ORDER_DATE
197 ,CONVERSION_TYPE_CODE);
198 RETURN (GL_RATE);
199 ELSE
200 RETURN (CONVERSION_RATE);
201 END IF;
202 END IF;
203 ELSE
204 RETURN (1);
205 END IF;
206 EXCEPTION
207 WHEN NO_DATA_FOUND THEN
208 RETURN (-1);
209 END;
210 RETURN NULL;
211 END C_GL_CONV_RATEFORMULA;
212
213 FUNCTION C_FC_ORDER_VALUEFORMULA(C_GL_CONV_RATE IN NUMBER
214 ,HEADER_ID IN NUMBER) RETURN NUMBER IS
215 BEGIN
216 DECLARE
217 FC_ORDER_VALUE NUMBER;
218 L_ORDER_VALUE NUMBER;
219 BEGIN
220 IF P_FUNCTIONAL_CURRENCY = 'Y' THEN
221 SELECT
222 C_GL_CONV_RATE * OE_OE_TOTALS_SUMMARY.PRT_ORDER_TOTAL(HEADER_ID)
223 INTO FC_ORDER_VALUE
224 FROM
225 DUAL;
226 /*SRW.MESSAGE(1
227 ,'Order Value' || FC_ORDER_VALUE)*/NULL;
228 IF (RP_CURR_PROFILE = 'EXTENDED') THEN
229 FC_ORDER_VALUE := ROUND(FC_ORDER_VALUE
230 ,CP_EXT_PRECISION);
231 ELSE
232 FC_ORDER_VALUE := ROUND(FC_ORDER_VALUE
233 ,CP_STD_PRECISION);
234 END IF;
235 RETURN (FC_ORDER_VALUE);
236 ELSE
237 L_ORDER_VALUE := OE_OE_TOTALS_SUMMARY.PRT_ORDER_TOTAL(HEADER_ID);
238 IF (RP_CURR_PROFILE = 'EXTENDED') THEN
239 L_ORDER_VALUE := ROUND(L_ORDER_VALUE
240 ,CP_EXT_PRECISION);
241 ELSE
242 L_ORDER_VALUE := ROUND(L_ORDER_VALUE
243 ,CP_STD_PRECISION);
244 END IF;
245 RETURN (L_ORDER_VALUE);
246 END IF;
247 END;
248 RETURN NULL;
249 END C_FC_ORDER_VALUEFORMULA;
250
251 FUNCTION C_FC_HDR_PA_AMOUNTFORMULA(C_GL_CONV_RATE IN NUMBER
252 ,HDR_PA_AMOUNT IN NUMBER) RETURN NUMBER IS
253 BEGIN
254 DECLARE
255 FC_PA_AMOUNT NUMBER;
256 BEGIN
257 IF P_FUNCTIONAL_CURRENCY = 'Y' THEN
258 SELECT
259 C_GL_CONV_RATE * HDR_PA_AMOUNT
260 INTO FC_PA_AMOUNT
261 FROM
262 DUAL;
263 RETURN (FC_PA_AMOUNT);
264 ELSE
265 RETURN (HDR_PA_AMOUNT);
266 END IF;
267 END;
268 RETURN NULL;
269 END C_FC_HDR_PA_AMOUNTFORMULA;
270
271 FUNCTION C_FC_LIST_PRICEFORMULA(C_GL_CONV_RATE IN NUMBER
272 ,LIST_PRICE IN NUMBER) RETURN NUMBER IS
273 BEGIN
274 DECLARE
275 FC_LIST_PRICE NUMBER;
276 L_LIST_PRICE NUMBER;
277 BEGIN
278 IF P_FUNCTIONAL_CURRENCY = 'Y' THEN
279 SELECT
280 C_GL_CONV_RATE * LIST_PRICE
281 INTO FC_LIST_PRICE
282 FROM
283 DUAL;
284 IF (RP_CURR_PROFILE = 'EXTENDED') THEN
285 FC_LIST_PRICE := ROUND(FC_LIST_PRICE
286 ,CP_EXT_PRECISION);
287 ELSE
288 FC_LIST_PRICE := ROUND(FC_LIST_PRICE
289 ,CP_STD_PRECISION);
290 END IF;
291 RETURN (FC_LIST_PRICE);
292 ELSE
293 L_LIST_PRICE := LIST_PRICE;
294 IF (RP_CURR_PROFILE = 'EXTENDED') THEN
295 L_LIST_PRICE := ROUND(L_LIST_PRICE
296 ,CP_EXT_PRECISION);
297 ELSE
298 L_LIST_PRICE := ROUND(L_LIST_PRICE
299 ,CP_STD_PRECISION);
300 END IF;
301 RETURN (L_LIST_PRICE);
302 END IF;
303 END;
304 RETURN NULL;
305 END C_FC_LIST_PRICEFORMULA;
306
307 FUNCTION C_FC_SELLING_PRICEFORMULA(C_GL_CONV_RATE IN NUMBER
308 ,SELLING_PRICE IN NUMBER) RETURN NUMBER IS
309 BEGIN
310 DECLARE
311 FC_SELLING_PRICE NUMBER;
312 L_SELLING_PRICE NUMBER;
313 BEGIN
314 IF P_FUNCTIONAL_CURRENCY = 'Y' THEN
315 SELECT
316 C_GL_CONV_RATE * SELLING_PRICE
317 INTO FC_SELLING_PRICE
318 FROM
319 DUAL;
320 IF (RP_CURR_PROFILE = 'EXTENDED') THEN
321 FC_SELLING_PRICE := ROUND(FC_SELLING_PRICE
322 ,CP_EXT_PRECISION);
323 ELSE
324 FC_SELLING_PRICE := ROUND(FC_SELLING_PRICE
325 ,CP_STD_PRECISION);
326 END IF;
327 RETURN (FC_SELLING_PRICE);
328 ELSE
329 L_SELLING_PRICE := SELLING_PRICE;
330 IF (RP_CURR_PROFILE = 'EXTENDED') THEN
331 L_SELLING_PRICE := ROUND(L_SELLING_PRICE
332 ,CP_EXT_PRECISION);
333 ELSE
334 L_SELLING_PRICE := ROUND(L_SELLING_PRICE
335 ,CP_STD_PRECISION);
336 END IF;
337 RETURN (L_SELLING_PRICE);
338 END IF;
339 END;
340 RETURN NULL;
341 END C_FC_SELLING_PRICEFORMULA;
342
343 FUNCTION C_FC_EXTENDED_PRICEFORMULA(C_GL_CONV_RATE IN NUMBER
344 ,EXTENDED_PRICE IN NUMBER) RETURN NUMBER IS
345 BEGIN
346 DECLARE
347 FC_EXTENDED_PRICE NUMBER;
348 L_EXTENDED_PRICE NUMBER;
349 BEGIN
350 IF P_FUNCTIONAL_CURRENCY = 'Y' THEN
351 SELECT
352 C_GL_CONV_RATE * EXTENDED_PRICE
353 INTO FC_EXTENDED_PRICE
354 FROM
355 DUAL;
356 IF (RP_CURR_PROFILE = 'EXTENDED') THEN
357 FC_EXTENDED_PRICE := ROUND(FC_EXTENDED_PRICE
358 ,CP_EXT_PRECISION);
359 ELSE
360 FC_EXTENDED_PRICE := ROUND(FC_EXTENDED_PRICE
361 ,CP_STD_PRECISION);
362 END IF;
363 RETURN (FC_EXTENDED_PRICE);
364 ELSE
365 L_EXTENDED_PRICE := EXTENDED_PRICE;
366 IF (RP_CURR_PROFILE = 'EXTENDED') THEN
367 FC_EXTENDED_PRICE := ROUND(FC_EXTENDED_PRICE
368 ,CP_EXT_PRECISION);
369 ELSE
370 FC_EXTENDED_PRICE := ROUND(FC_EXTENDED_PRICE
371 ,CP_STD_PRECISION);
372 END IF;
373 RETURN (L_EXTENDED_PRICE);
374 END IF;
375 END;
376 RETURN NULL;
377 END C_FC_EXTENDED_PRICEFORMULA;
378
379 FUNCTION C_FC_L_PA_AMOUNTFORMULA(C_GL_CONV_RATE IN NUMBER
380 ,L_PA_AMOUNT IN NUMBER) RETURN NUMBER IS
381 BEGIN
382 DECLARE
383 FC_PA_AMOUNT NUMBER;
384 BEGIN
385 IF P_FUNCTIONAL_CURRENCY = 'Y' THEN
386 SELECT
387 C_GL_CONV_RATE * L_PA_AMOUNT
388 INTO FC_PA_AMOUNT
389 FROM
390 DUAL;
391 RETURN (FC_PA_AMOUNT);
392 ELSE
393 RETURN (L_PA_AMOUNT);
394 END IF;
395 END;
396 RETURN NULL;
397 END C_FC_L_PA_AMOUNTFORMULA;
398
399 FUNCTION C_OPEN_ORDERS_WHERE RETURN VARCHAR2 IS
400 FLAG_VALUE VARCHAR2(2);
401 BEGIN
402 FLAG_VALUE := 'Y';
403 IF P_OPEN_ORDERS = 'Y' THEN
404 RETURN ('and h.open_flag = ''' || FLAG_VALUE || ''' ');
405 ELSE
406 RETURN (NULL);
407 END IF;
408 RETURN NULL;
409 END C_OPEN_ORDERS_WHERE;
410
411 FUNCTION C_USE_CURRENCYFORMULA(C_BASE_CURRENCY IN VARCHAR2
412 ,CURRENCY1 IN VARCHAR2) RETURN VARCHAR2 IS
413 BEGIN
414 /*SRW.REFERENCE(C_BASE_CURRENCY)*/NULL;
415 /*SRW.REFERENCE(CURRENCY1)*/NULL;
416 IF P_FUNCTIONAL_CURRENCY = 'Y' THEN
417 FND_CURRENCY.GET_INFO(C_BASE_CURRENCY
418 ,CP_STD_PRECISION
419 ,CP_EXT_PRECISION
420 ,CP_MIN_ACCT_UNIT);
421 RETURN (C_BASE_CURRENCY);
422 ELSE
423 IF CURRENCY1 IS NULL THEN
424 FND_CURRENCY.GET_INFO(C_BASE_CURRENCY
425 ,CP_STD_PRECISION
426 ,CP_EXT_PRECISION
427 ,CP_MIN_ACCT_UNIT);
428 RETURN (C_BASE_CURRENCY);
429 ELSE
430 FND_CURRENCY.GET_INFO(C_BASE_CURRENCY
431 ,CP_STD_PRECISION
432 ,CP_EXT_PRECISION
433 ,CP_MIN_ACCT_UNIT);
434 RETURN (CURRENCY1);
435 END IF;
436 END IF;
437 RETURN NULL;
438 END C_USE_CURRENCYFORMULA;
439
440 FUNCTION RP_ORDER_CATEGORYFORMULA RETURN VARCHAR2 IS
441 BEGIN
442 DECLARE
443 L_MEANING VARCHAR2(80);
444 BEGIN
445 SELECT
446 MEANING
447 INTO L_MEANING
448 FROM
449 OE_LOOKUPS
450 WHERE LOOKUP_TYPE = 'REPORT_ORDER_CATEGORY'
451 AND LOOKUP_CODE = P_ORDER_CATEGORY;
452 RETURN (L_MEANING);
453 EXCEPTION
454 WHEN NO_DATA_FOUND THEN
455 RETURN (NULL);
456 END;
457 RETURN NULL;
458 END RP_ORDER_CATEGORYFORMULA;
459
460 function BeforeReport return boolean is
461 apf boolean;
462 BEGIN
463 apf := afterpform;
464 LP_ORGANIZATION_ID := P_ORGANIZATION_ID;
465 LP_ORGANIZATION_ID:= MO_GLOBAL.GET_CURRENT_ORG_ID();
466 fnd_profile.get('ONT_UNIT_PRICE_PRECISION_TYPE', RP_CURR_PROFILE);
467 P_ORDER_DATE_HI_T := to_char(P_ORDER_DATE_HI,'DD-MON-YY');
468 P_ORDER_DATE_LO_T := to_char(P_ORDER_DATE_LO,'DD-MON-YY');
469 return (TRUE);
470 end;
471
472 FUNCTION AFTERPFORM RETURN BOOLEAN IS
473 BEGIN
474 /*SRW.MESSAGE(99999
475 ,'$Header: OEXOECODB.pls 120.2 2008/05/05 09:02:28 dwkrishn noship $')*/NULL;
476 BEGIN
477 IF P_ORDER_NUM_LO = P_ORDER_NUM_HI THEN
478 NULL;
479 ELSE
480 IF P_ORDER_CATEGORY IS NOT NULL THEN
481 IF P_ORDER_CATEGORY = 'SALES' THEN
482 LP_ORDER_CATEGORY := 'and h.order_category_code in (''ORDER'', ''MIXED'') ';
483 ELSIF P_ORDER_CATEGORY = 'CREDIT' THEN
484 LP_ORDER_CATEGORY := 'and h.order_category_code in (''RETURN'', ''MIXED'') ';
485 ELSIF P_ORDER_CATEGORY = 'ALL' THEN
486 LP_ORDER_CATEGORY := ' ';
487 END IF;
488 ELSE
489 LP_ORDER_CATEGORY := 'and h.order_category_code in (''ORDER'', ''MIXED'') ';
490 END IF;
491 END IF;
492 IF (P_ORDER_NUM_LO IS NOT NULL) AND (P_ORDER_NUM_HI IS NOT NULL) THEN
493 IF (P_ORDER_NUM_LO = P_ORDER_NUM_HI) THEN
494 LP_ORDER_NUM := ' and h.order_number = :p_order_num_lo ';
495 ELSE
496 LP_ORDER_NUM := ' and (h.order_number between :p_order_num_lo and :p_order_num_hi) ';
497 END IF;
498 ELSIF (P_ORDER_NUM_LO IS NOT NULL) THEN
499 LP_ORDER_NUM := ' and h.order_number >= :p_order_num_lo ';
500 ELSIF (P_ORDER_NUM_HI IS NOT NULL) THEN
501 LP_ORDER_NUM := ' and h.order_number <= :p_order_num_hi ';
502 END IF;
503 IF P_LINE_CATEGORY IS NOT NULL THEN
504 IF P_LINE_CATEGORY = 'SALES' THEN
505 LP_LINE_CATEGORY := 'and l.line_category_code = ''ORDER'' ';
506 ELSIF P_LINE_CATEGORY = 'CREDIT' THEN
507 LP_LINE_CATEGORY := 'and l.line_category_code = ''RETURN'' ';
508 ELSIF P_LINE_CATEGORY = 'ALL' THEN
509 LP_LINE_CATEGORY := ' ';
510 END IF;
511 ELSE
512 LP_LINE_CATEGORY := 'and l.line_category_code = ''ORDER'' ';
513 END IF;
514 IF P_ORDER_TYPE_LO IS NOT NULL AND P_ORDER_TYPE_HI IS NOT NULL THEN
515 LP_ORDER_TYPE := 'and ot.TRANSACTION_TYPE_ID between :P_order_type_lo and :P_order_type_hi ';
516 SELECT
517 OEOT.NAME
518 INTO L_ORDER_TYPE_LOW
519 FROM
520 OE_TRANSACTION_TYPES_TL OEOT
521 WHERE OEOT.TRANSACTION_TYPE_ID = P_ORDER_TYPE_LO
522 AND OEOT.LANGUAGE = USERENV('LANG');
523 SELECT
524 OEOT.NAME
525 INTO L_ORDER_TYPE_HIGH
526 FROM
527 OE_TRANSACTION_TYPES_TL OEOT
528 WHERE OEOT.TRANSACTION_TYPE_ID = P_ORDER_TYPE_HI
529 AND OEOT.LANGUAGE = USERENV('LANG');
530 ELSE
531 IF P_ORDER_TYPE_LO IS NULL AND P_ORDER_TYPE_HI IS NOT NULL THEN
532 LP_ORDER_TYPE := 'and ot.TRANSACTION_TYPE_ID <= :P_order_type_hi ';
533 SELECT
534 OEOT.NAME
535 INTO L_ORDER_TYPE_HIGH
536 FROM
537 OE_TRANSACTION_TYPES_TL OEOT
538 WHERE OEOT.TRANSACTION_TYPE_ID = P_ORDER_TYPE_HI
539 AND OEOT.LANGUAGE = USERENV('LANG');
540 ELSE
541 IF P_ORDER_TYPE_LO IS NOT NULL AND P_ORDER_TYPE_HI IS NULL THEN
542 LP_ORDER_TYPE := 'and ot.TRANSACTION_TYPE_ID >= :P_order_type_lo ';
543 SELECT
544 OEOT.NAME
545 INTO L_ORDER_TYPE_LOW
546 FROM
547 OE_TRANSACTION_TYPES_TL OEOT
548 WHERE OEOT.TRANSACTION_TYPE_ID = P_ORDER_TYPE_LO
549 AND OEOT.LANGUAGE = USERENV('LANG');
550 ELSE
551 LP_ORDER_TYPE := ' ';
552 END IF;
553 END IF;
554 END IF;
555 IF P_ORDER_DATE_LO IS NOT NULL AND P_ORDER_DATE_HI IS NOT NULL THEN
556 LP_ORDER_DATE := 'and h.ordered_date between :P_order_date_lo and (:P_order_date_hi+1) ';
557 ELSE
558 IF P_ORDER_DATE_LO IS NULL AND P_ORDER_DATE_HI IS NOT NULL THEN
559 LP_ORDER_DATE := 'and h.ordered_date <= (:P_order_date_hi+1) ';
560 ELSE
561 IF P_ORDER_DATE_LO IS NOT NULL AND P_ORDER_DATE_HI IS NULL THEN
562 LP_ORDER_DATE := 'and h.ordered_date >= :P_order_date_lo ';
563 ELSE
564 LP_ORDER_DATE := ' ';
565 END IF;
566 END IF;
567 END IF;
568 IF P_LINE_TYPE_LO IS NOT NULL AND P_LINE_TYPE_HI IS NOT NULL THEN
569 LP_LINE_TYPE := 'and ol.transaction_type_id between :P_line_type_lo and :P_line_type_hi ';
570 SELECT
571 OEOT.NAME
572 INTO L_LINE_TYPE_HIGH
573 FROM
574 OE_TRANSACTION_TYPES_TL OEOT
575 WHERE OEOT.TRANSACTION_TYPE_ID = P_LINE_TYPE_HI
576 AND OEOT.LANGUAGE = USERENV('LANG');
577 SELECT
578 OEOT.NAME
579 INTO L_LINE_TYPE_LOW
580 FROM
581 OE_TRANSACTION_TYPES_TL OEOT
582 WHERE OEOT.TRANSACTION_TYPE_ID = P_LINE_TYPE_LO
583 AND OEOT.LANGUAGE = USERENV('LANG');
584 ELSE
585 IF P_LINE_TYPE_LO IS NULL AND P_LINE_TYPE_HI IS NOT NULL THEN
586 LP_LINE_TYPE := 'and ol.transaction_type_id <= :P_line_type_hi ';
587 SELECT
588 OEOT.NAME
589 INTO L_LINE_TYPE_HIGH
590 FROM
591 OE_TRANSACTION_TYPES_TL OEOT
592 WHERE OEOT.TRANSACTION_TYPE_ID = P_LINE_TYPE_HI
593 AND OEOT.LANGUAGE = USERENV('LANG');
594 ELSE
595 IF P_LINE_TYPE_LO IS NOT NULL AND P_LINE_TYPE_HI IS NULL THEN
596 LP_LINE_TYPE := 'and ol.transaction_type_id >= :P_line_type_lo ';
597 SELECT
598 OEOT.NAME
599 INTO L_LINE_TYPE_LOW
600 FROM
601 OE_TRANSACTION_TYPES_TL OEOT
602 WHERE OEOT.TRANSACTION_TYPE_ID = P_LINE_TYPE_LO
603 AND OEOT.LANGUAGE = USERENV('LANG');
604 ELSE
605 LP_LINE_TYPE := ' ';
606 END IF;
607 END IF;
608 END IF;
609 IF P_CUSTOMER_LO IS NOT NULL AND P_CUSTOMER_HI IS NOT NULL THEN
610 LP_CUSTOMER_NAME := 'and sold_to_org.name between :P_customer_lo and :P_customer_hi ';
611 ELSE
612 IF P_CUSTOMER_LO IS NULL AND P_CUSTOMER_HI IS NOT NULL THEN
613 LP_CUSTOMER_NAME := 'and sold_to_org.name <= :P_customer_hi ';
614 ELSE
615 IF P_CUSTOMER_LO IS NOT NULL AND P_CUSTOMER_HI IS NULL THEN
616 LP_CUSTOMER_NAME := 'and sold_To_org.name >= :P_customer_lo ';
617 ELSE
618 LP_CUSTOMER_NAME := ' ';
619 END IF;
620 END IF;
621 END IF;
622 IF P_CUST_NUM_LO IS NOT NULL AND P_CUST_NUM_HI IS NOT NULL THEN
623 LP_CUSTOMER_NUMBER := 'and sold_to_org.customer_number between :P_cust_num_lo and :P_cust_num_hi ';
624 ELSE
625 IF P_CUST_NUM_LO IS NULL AND P_CUST_NUM_HI IS NOT NULL THEN
626 LP_CUSTOMER_NUMBER := 'and sold_to_org.customer_number <= :P_cust_num_hi ';
627 ELSE
628 IF P_CUST_NUM_LO IS NOT NULL AND P_CUST_NUM_HI IS NULL THEN
629 LP_CUSTOMER_NUMBER := 'and sold_to_org.customer_number >= :P_cust_num_lo ';
630 ELSE
631 LP_CUSTOMER_NUMBER := ' ';
632 END IF;
633 END IF;
634 END IF;
635 IF P_SALESREP_LO IS NOT NULL AND P_SALESREP_HI IS NOT NULL THEN
636 LP_SALESREP_NAME := 'and sr.name between :P_salesrep_lo and :P_salesrep_hi ';
637 ELSE
638 IF P_SALESREP_LO IS NULL AND P_SALESREP_HI IS NOT NULL THEN
639 LP_SALESREP_NAME := 'and sr.name <= :P_salesrep_hi ';
640 ELSE
641 IF P_SALESREP_LO IS NOT NULL AND P_SALESREP_HI IS NULL THEN
642 LP_SALESREP_NAME := 'and sr.name >= :P_salesrep_lo ';
643 ELSE
644 LP_SALESREP_NAME := ' ';
645 END IF;
646 END IF;
647 END IF;
648 IF P_ORDER_BY = 'CUSTOMER' THEN
649 LP_ORDER_BY := ' sold_to_org.name,';
650 ELSE
651 IF P_ORDER_BY = 'SALESREP' THEN
652 LP_ORDER_BY := ' sr.name,';
653 ELSE
654 IF P_ORDER_BY = 'ORDER_TYPE' THEN
655 LP_ORDER_BY := ' ot.name,';
656 ELSE
657 IF P_ORDER_BY = 'AGREEMENT' THEN
658 LP_ORDER_BY := ' agree.name,';
659 ELSE
660 IF P_ORDER_BY = 'ORDER_NUMBER' THEN
661 LP_ORDER_BY := ' h.order_number,';
662 ELSE
663 IF P_ORDER_BY = 'ORDER_DATE' THEN
664 LP_ORDER_BY := ' h.ordered_date,';
665 ELSE
666 IF P_ORDER_BY = 'SHIP_TO_COUNTRY' THEN
667 LP_ORDER_BY := ' loc.country,';
668 ELSE
669 LP_ORDER_BY := ' ';
670 END IF;
671 END IF;
672 END IF;
673 END IF;
674 END IF;
675 END IF;
676 END IF;
677 IF P_ENTERED_BY_LO IS NOT NULL AND P_ENTERED_BY_HI IS NOT NULL THEN
678 LP_ENTERED_BY := 'and u.user_name between :P_entered_by_lo and :P_entered_by_hi ';
679 ELSE
680 IF P_ENTERED_BY_LO IS NULL AND P_ENTERED_BY_HI IS NOT NULL THEN
681 LP_ENTERED_BY := 'and u.user_name <= :P_entered_by_hi ';
682 ELSE
683 IF P_ENTERED_BY_LO IS NOT NULL AND P_ENTERED_BY_HI IS NULL THEN
684 LP_ENTERED_BY := 'and u.user_name >= :P_entered_by_lo ';
685 ELSE
686 LP_ENTERED_BY := ' ';
687 END IF;
688 END IF;
689 END IF;
690 END;
691 RETURN (TRUE);
692 END AFTERPFORM;
693
694 FUNCTION C_FC_TAXFORMULA(C_GL_CONV_RATE IN NUMBER
695 ,TAX_ON_LINE IN NUMBER) RETURN NUMBER IS
696 BEGIN
697 DECLARE
698 FC_TAX NUMBER;
699 BEGIN
700 IF P_FUNCTIONAL_CURRENCY = 'Y' THEN
701 SELECT
702 C_GL_CONV_RATE * NVL(TAX_ON_LINE
703 ,0)
704 INTO FC_TAX
705 FROM
706 DUAL;
707 RETURN (FC_TAX);
708 ELSE
709 RETURN (TAX_ON_LINE);
710 END IF;
711 END;
712 RETURN NULL;
713 END C_FC_TAXFORMULA;
714
715 FUNCTION C_FC_LINE_CHARGEFORMULA(C_GL_CONV_RATE IN NUMBER
716 ,LINE_CHARGE IN NUMBER) RETURN NUMBER IS
717 BEGIN
718 DECLARE
719 FC_LINE_CHARGE NUMBER;
720 BEGIN
721 IF P_FUNCTIONAL_CURRENCY = 'Y' THEN
722 SELECT
723 C_GL_CONV_RATE * ROUND(LINE_CHARGE
724 ,2)
725 INTO FC_LINE_CHARGE
726 FROM
727 DUAL;
728 RETURN (FC_LINE_CHARGE);
729 ELSE
730 FC_LINE_CHARGE := ROUND(LINE_CHARGE
731 ,2);
732 RETURN (FC_LINE_CHARGE);
733 END IF;
734 END;
735 RETURN NULL;
736 END C_FC_LINE_CHARGEFORMULA;
737
738 FUNCTION C_LINE_BILL_TO_CUSTFORMULA(LINE_BILL_TO_ORG_ID IN NUMBER
739 ,INVOICE_TO_ORG_ID IN NUMBER
740 ,L_BILL_ADDRESS IN VARCHAR2) RETURN CHAR IS
741 BEGIN
742 BEGIN
743 IF LINE_BILL_TO_ORG_ID IS NULL THEN
744 RETURN (NULL);
745 END IF;
746 IF NVL(INVOICE_TO_ORG_ID
747 ,0) <> NVL(LINE_BILL_TO_ORG_ID
748 ,0) THEN
749 RETURN (L_BILL_ADDRESS);
750 ELSE
751 RETURN (NULL);
752 END IF;
753 END;
754 RETURN NULL;
755 END C_LINE_BILL_TO_CUSTFORMULA;
756
757 FUNCTION C_LINE_SHIP_TO_CUSTFORMULA(LINE_SHIP_TO_ORG_ID IN NUMBER
758 ,HDR_SHIP_SITE_USE_ID IN NUMBER
759 ,L_SHIP_ADDRESS IN VARCHAR2) RETURN CHAR IS
760 BEGIN
761 BEGIN
762 IF LINE_SHIP_TO_ORG_ID IS NULL THEN
763 RETURN (NULL);
764 END IF;
765 IF NVL(HDR_SHIP_SITE_USE_ID
766 ,0) <> NVL(LINE_SHIP_TO_ORG_ID
767 ,0) THEN
768 RETURN (L_SHIP_ADDRESS);
769 ELSE
770 RETURN (NULL);
771 END IF;
772 END;
773 RETURN NULL;
774 END C_LINE_SHIP_TO_CUSTFORMULA;
775
776 FUNCTION C_LINE_AGREEMENTFORMULA(LINE_AGREEMENT IN VARCHAR2
777 ,AGREEMENT1 IN VARCHAR2) RETURN CHAR IS
778 BEGIN
779 BEGIN
780 IF LINE_AGREEMENT IS NULL THEN
781 RETURN (NULL);
782 END IF;
783 IF NVL(AGREEMENT1
784 ,' ') <> NVL(LINE_AGREEMENT
785 ,' ') THEN
786 RETURN (LINE_AGREEMENT);
787 ELSE
788 RETURN (NULL);
789 END IF;
790 END;
791 RETURN NULL;
792 END C_LINE_AGREEMENTFORMULA;
793
794 FUNCTION C_LINE_POFORMULA(LINE_PO IN VARCHAR2
795 ,PURCHASE_ORDER IN VARCHAR2) RETURN CHAR IS
796 BEGIN
797 BEGIN
798 IF LINE_PO IS NULL THEN
799 RETURN (NULL);
800 END IF;
801 IF NVL(PURCHASE_ORDER
802 ,' ') <> NVL(LINE_PO
803 ,' ') THEN
804 RETURN (LINE_PO);
805 ELSE
806 RETURN (NULL);
807 END IF;
808 END;
809 RETURN NULL;
810 END C_LINE_POFORMULA;
811
812 FUNCTION C_LINE_TERMSFORMULA(LINE_TERMS IN VARCHAR2
813 ,TERMS1 IN VARCHAR2) RETURN CHAR IS
814 BEGIN
815 BEGIN
816 IF LINE_TERMS IS NULL THEN
817 RETURN (NULL);
818 END IF;
819 IF NVL(TERMS1
820 ,' ') <> NVL(LINE_TERMS
821 ,' ') THEN
822 RETURN (LINE_TERMS);
823 ELSE
824 RETURN (NULL);
825 END IF;
826 END;
827 RETURN NULL;
828 END C_LINE_TERMSFORMULA;
829
830 FUNCTION C_ITEM_REVISIONFORMULA(ITEM_REVISION IN VARCHAR2) RETURN CHAR IS
831 BEGIN
832 BEGIN
833 IF ITEM_REVISION IS NULL THEN
834 RETURN (NULL);
835 ELSE
836 RETURN (ITEM_REVISION);
837 END IF;
838 END;
839 RETURN NULL;
840 END C_ITEM_REVISIONFORMULA;
841
842 FUNCTION P_ITEM_FLEX_CODEVALIDTRIGGER RETURN BOOLEAN IS
843 BEGIN
844 RETURN (TRUE);
845 END P_ITEM_FLEX_CODEVALIDTRIGGER;
846
847 FUNCTION C_FC_EXTEND_PRICEFORMULA(C_GL_CONV_RATE IN NUMBER
848 ,SVC_EXTENDED_PRICE IN NUMBER) RETURN NUMBER IS
849 BEGIN
850 DECLARE
851 FC_SVC_EXTENDED_PRICE NUMBER;
852 BEGIN
853 IF P_FUNCTIONAL_CURRENCY = 'Y' THEN
854 SELECT
855 C_GL_CONV_RATE * SVC_EXTENDED_PRICE
856 INTO FC_SVC_EXTENDED_PRICE
857 FROM
858 DUAL;
859 RETURN (FC_SVC_EXTENDED_PRICE);
860 ELSE
861 RETURN (SVC_EXTENDED_PRICE);
862 END IF;
863 END;
864 RETURN NULL;
865 END C_FC_EXTEND_PRICEFORMULA;
866
867 FUNCTION C_FC_SALE_PRICEFORMULA(C_GL_CONV_RATE IN NUMBER
868 ,SVC_SELLING_PRICE IN NUMBER) RETURN NUMBER IS
869 BEGIN
870 DECLARE
871 FC_SVC_SELLING_PRICE NUMBER;
872 BEGIN
873 IF P_FUNCTIONAL_CURRENCY = 'Y' THEN
874 SELECT
875 C_GL_CONV_RATE * SVC_SELLING_PRICE
876 INTO FC_SVC_SELLING_PRICE
877 FROM
878 DUAL;
879 RETURN (FC_SVC_SELLING_PRICE);
880 ELSE
881 RETURN (SVC_SELLING_PRICE);
882 END IF;
883 END;
884 RETURN NULL;
885 END C_FC_SALE_PRICEFORMULA;
886
887 FUNCTION C_FMT_EXTEND_PRICEFORMULA(C_FC_EXTEND_PRICE IN NUMBER) RETURN NUMBER IS
888 BEGIN
889 RETURN (C_FC_EXTEND_PRICE);
890 END C_FMT_EXTEND_PRICEFORMULA;
891
892 FUNCTION RP_LINE_CATEGORYFORMULA RETURN VARCHAR2 IS
893 BEGIN
894 DECLARE
895 L_MEANING VARCHAR2(80);
896 BEGIN
897 SELECT
898 MEANING
899 INTO L_MEANING
900 FROM
901 OE_LOOKUPS
902 WHERE LOOKUP_TYPE = 'REPORT_LINE_DISPLAY'
903 AND LOOKUP_CODE = P_LINE_CATEGORY;
904 RETURN (L_MEANING);
905 EXCEPTION
906 WHEN NO_DATA_FOUND THEN
907 RETURN (NULL);
908 END;
909 RETURN NULL;
910 END RP_LINE_CATEGORYFORMULA;
911
912 FUNCTION RP_ITEM_DISPLAYFORMULA RETURN VARCHAR2 IS
913 BEGIN
914 DECLARE
915 L_MEANING VARCHAR2(80);
916 BEGIN
917 SELECT
918 MEANING
919 INTO L_MEANING
920 FROM
921 OE_LOOKUPS
922 WHERE LOOKUP_TYPE = 'ITEM_DISPLAY_CODE'
923 AND LOOKUP_CODE = P_PRINT_DESCRIPTION;
924 RETURN (L_MEANING);
925 EXCEPTION
926 WHEN NO_DATA_FOUND THEN
927 RETURN (NULL);
928 END;
929 RETURN NULL;
930 END RP_ITEM_DISPLAYFORMULA;
931
932 FUNCTION C_FC_AMOUNTFORMULA(C_GL_CONV_RATE IN NUMBER
933 ,AMOUNT IN NUMBER
934 ,C_USE_CURRENCY IN VARCHAR2) RETURN NUMBER IS
935 BEGIN
936 DECLARE
937 FC_AMOUNT NUMBER;
938 L_STD_PRECISION NUMBER;
939 L_EXT_PRECISION NUMBER;
940 L_MIN_ACCT_UNIT NUMBER;
941 BEGIN
942 IF P_FUNCTIONAL_CURRENCY = 'Y' THEN
943 SELECT
944 C_GL_CONV_RATE * AMOUNT
945 INTO FC_AMOUNT
946 FROM
947 DUAL;
948 IF C_GL_CONV_RATE <> 1 THEN
949 FND_CURRENCY.GET_INFO(C_USE_CURRENCY
950 ,L_STD_PRECISION
951 ,L_EXT_PRECISION
952 ,L_MIN_ACCT_UNIT);
953 IF (RP_CURR_PROFILE = 'EXTENDED') THEN
954 FC_AMOUNT := ROUND(FC_AMOUNT
955 ,L_EXT_PRECISION);
956 ELSE
957 FC_AMOUNT := ROUND(FC_AMOUNT
958 ,L_STD_PRECISION);
959 END IF;
960 END IF;
961 RETURN (FC_AMOUNT);
962 ELSE
963 RETURN (AMOUNT);
964 END IF;
965 END;
966 RETURN NULL;
967 END C_FC_AMOUNTFORMULA;
968
969 FUNCTION C_MASTER_ORGFORMULA RETURN CHAR IS
970 V_MASTER_ORG VARCHAR2(20);
971 BEGIN
972 V_MASTER_ORG := NVL(OE_SYS_PARAMETERS.VALUE('MASTER_ORGANIZATION_ID'
973 ,MO_GLOBAL.GET_CURRENT_ORG_ID)
974 ,0);
975 RETURN V_MASTER_ORG;
976 END C_MASTER_ORGFORMULA;
977
978 FUNCTION C_SHIP_HDR_ADDRESS4FORMULA(S_ADDRESS4 IN VARCHAR2
979 ,HDR_SHIP_SITE_USE_ID IN NUMBER) RETURN CHAR IS
980 BEGIN
981 /*SRW.REFERENCE(S_ADDRESS4)*/NULL;
982 /*SRW.REFERENCE(HDR_SHIP_SITE_USE_ID)*/NULL;
983 IF HDR_SHIP_SITE_USE_ID IS NOT NULL THEN
984 RETURN (S_ADDRESS4);
985 ELSE
986 RETURN NULL;
987 END IF;
988 END C_SHIP_HDR_ADDRESS4FORMULA;
989
990 FUNCTION C_BILL_HDR_ADDRESS4FORMULA(B_ADDRESS4 IN VARCHAR2
991 ,INVOICE_TO_ORG_ID IN NUMBER) RETURN CHAR IS
992 BEGIN
993 /*SRW.REFERENCE(B_ADDRESS4)*/NULL;
994 /*SRW.REFERENCE(INVOICE_TO_ORG_ID)*/NULL;
995 IF INVOICE_TO_ORG_ID IS NOT NULL THEN
996 RETURN (B_ADDRESS4);
997 ELSE
998 RETURN NULL;
999 END IF;
1000 END C_BILL_HDR_ADDRESS4FORMULA;
1001
1002 FUNCTION CF_HDR_PERCENTFORMULA(PREPAID_AMOUNT IN NUMBER
1003 ,C_FC_ORDER_VALUE IN NUMBER) RETURN NUMBER IS
1004 L_PERCENT NUMBER;
1005 BEGIN
1006 /*SRW.MESSAGE(1
1007 ,'in cf_hdr_percent')*/NULL;
1008 /*SRW.MESSAGE(1
1009 ,'prepaid amount : ' || PREPAID_AMOUNT)*/NULL;
1010 /*SRW.MESSAGE(1
1011 ,'order value : ' || C_FC_ORDER_VALUE)*/NULL;
1012 L_PERCENT := PREPAID_AMOUNT / C_FC_ORDER_VALUE * 100;
1013 /*SRW.MESSAGE(1
1014 ,'percent : ' || L_PERCENT)*/NULL;
1015 RETURN (ROUND(L_PERCENT
1016 ,2));
1017 END CF_HDR_PERCENTFORMULA;
1018
1019 FUNCTION CF_LINE_PERCENTFORMULA(PREPAID_AMOUNT1 IN NUMBER
1020 ,C_FC_ORDER_VALUE IN NUMBER) RETURN NUMBER IS
1021 L_LINE_PERCENT NUMBER;
1022 BEGIN
1023 L_LINE_PERCENT := PREPAID_AMOUNT1 / C_FC_ORDER_VALUE * 100;
1024 RETURN (ROUND(L_LINE_PERCENT
1025 ,2));
1026 END CF_LINE_PERCENTFORMULA;
1027
1028 FUNCTION C_CHARGE_PERIODICITYFORMULA(CHARGE_PERIODICITY_CODE IN VARCHAR2) RETURN CHAR IS
1029 L_PERIODICITY VARCHAR2(60);
1030 BEGIN
1031 IF CHARGE_PERIODICITY_CODE IS NOT NULL THEN
1032 SELECT
1033 UNIT_OF_MEASURE
1034 INTO L_PERIODICITY
1035 FROM
1036 MTL_UNITS_OF_MEASURE_VL
1037 WHERE UOM_CODE = CHARGE_PERIODICITY_CODE
1038 AND UOM_CLASS = FND_PROFILE.VALUE('ONT_UOM_CLASS_CHARGE_PERIODICITY');
1039 RETURN (L_PERIODICITY);
1040 ELSE
1041 RETURN (P_CHARGE_PERIODICITY);
1042 END IF;
1043 RETURN NULL;
1044 EXCEPTION
1045 WHEN NO_DATA_FOUND THEN
1046 RETURN NULL;
1047 END C_CHARGE_PERIODICITYFORMULA;
1048
1049 FUNCTION CF_INITIAL_DUE_TOTALFORMULA(HEADER_ID IN NUMBER) RETURN NUMBER IS
1050 L_PAY_NOW_SUBTOTAL NUMBER;
1051 L_PAY_NOW_TAX NUMBER;
1052 L_PAY_NOW_CHARGES NUMBER;
1053 L_PAY_NOW_TOTAL NUMBER;
1054 L_PAY_NOW_COMMITMENT NUMBER;
1055 L_MSG_COUNT NUMBER;
1056 L_MSG_DATA VARCHAR2(30);
1057 L_RETURN_STATUS VARCHAR2(30);
1058 BEGIN
1059 IF OE_PREPAYMENT_UTIL.GET_INSTALLMENT_OPTIONS in ('ENABLE_PAY_NOW','AUTHORIZE_FIRST_INSTALLMENT') THEN
1060 /*SRW.REFERENCE(HEADER_ID)*/NULL;
1061 OE_PREPAYMENT_PVT.GET_PAY_NOW_AMOUNTS(P_HEADER_ID => HEADER_ID
1062 ,P_LINE_ID => NULL
1063 ,X_PAY_NOW_SUBTOTAL => L_PAY_NOW_SUBTOTAL
1064 ,X_PAY_NOW_TAX => L_PAY_NOW_TAX
1065 ,X_PAY_NOW_CHARGES => L_PAY_NOW_CHARGES
1066 ,X_PAY_NOW_TOTAL => L_PAY_NOW_TOTAL
1067 ,X_PAY_NOW_COMMITMENT => L_PAY_NOW_COMMITMENT
1068 ,X_MSG_COUNT => L_MSG_COUNT
1069 ,X_MSG_DATA => L_MSG_DATA
1070 ,X_RETURN_STATUS => L_RETURN_STATUS);
1071 IF L_PAY_NOW_COMMITMENT <> 0 THEN
1072 CP_COMMITMENT := L_PAY_NOW_COMMITMENT;
1073 ELSE
1074 CP_COMMITMENT := NULL;
1075 END IF;
1076 RETURN L_PAY_NOW_TOTAL;
1077 ELSE
1078 RETURN 0;
1079 END IF;
1080 EXCEPTION
1081 WHEN OTHERS THEN
1082 RETURN 0;
1083 END CF_INITIAL_DUE_TOTALFORMULA;
1084
1085 FUNCTION CF_INITIAL_DUE_BALANCEFORMULA(CF_INITIAL_DUE_TOTAL IN NUMBER
1086 ,CS_PREPAID_AMOUNT IN NUMBER) RETURN NUMBER IS
1087 L_BALANCE_DUE NUMBER;
1088 BEGIN
1089 /*SRW.REFERENCE(CF_INITIAL_DUE_TOTAL)*/NULL;
1090 /*SRW.REFERENCE(CP_COMMITMENT)*/NULL;
1091 /*SRW.REFERENCE(CS_PREPAID_AMOUNT)*/NULL;
1092 /*SRW.MESSAGE(1
1093 ,'prepaid_amount' || CS_PREPAID_AMOUNT)*/NULL;
1094 /*SRW.MESSAGE(1
1095 ,'commitment' || CP_COMMITMENT)*/NULL;
1096 L_BALANCE_DUE := NVL(CF_INITIAL_DUE_TOTAL
1097 ,0) - NVL(CP_COMMITMENT
1098 ,0) - NVL(CS_PREPAID_AMOUNT
1099 ,0);
1100 IF L_BALANCE_DUE >= 0 THEN
1101 RETURN L_BALANCE_DUE;
1102 ELSE
1103 RETURN 0;
1104 END IF;
1105 END CF_INITIAL_DUE_BALANCEFORMULA;
1106
1107 FUNCTION CF_AUTHORIZED_AMOUNTFORMULA(HEADER_ID IN NUMBER) RETURN NUMBER IS
1108 CURSOR CC_TRXN_EXTN_IDS_CUR(P_HEADER_ID IN NUMBER) IS
1109 SELECT
1110 TRXN_EXTENSION_ID,
1111 TANGIBLE_ID
1112 FROM
1113 OE_PAYMENTS
1114 WHERE HEADER_ID = P_HEADER_ID
1115 AND NVL(PAYMENT_TYPE_CODE
1116 ,'COMMITMENT') = 'CREDIT_CARD';
1117 L_TRXN_EXTENSION_ID VARCHAR2(80);
1118 L_TANGIBLE_ID NUMBER;
1119 L_AUTHORIZED_AMOUNT NUMBER := 0;
1120 L_AUTHORIZED_AMOUNT_SUM NUMBER := 0;
1121 BEGIN
1122 /*SRW.REFERENCE(HEADER_ID)*/NULL;
1123 FOR cc_trxn_extn_ids IN cc_trxn_extn_ids_cur(header_id) LOOP
1124 L_TRXN_EXTENSION_ID := CC_TRXN_EXTN_IDS.TRXN_EXTENSION_ID;
1125 L_TANGIBLE_ID := CC_TRXN_EXTN_IDS.TANGIBLE_ID;
1126 BEGIN
1127 IF L_TRXN_EXTENSION_ID IS NOT NULL THEN
1128 SELECT
1129 AUTHORIZATION_AMOUNT
1130 INTO L_AUTHORIZED_AMOUNT
1131 FROM
1132 IBY_TRXN_EXT_AUTHS_V
1133 WHERE TRXN_EXTENSION_ID = L_TRXN_EXTENSION_ID
1134 AND AUTHORIZATION_STATUS = 0;
1135 ELSE
1136 IF L_TANGIBLE_ID IS NOT NULL THEN
1137 SELECT
1138 AMOUNT
1139 INTO L_AUTHORIZED_AMOUNT
1140 FROM
1141 IBY_TRXN_SUMMARIES_ALL
1142 WHERE TANGIBLEID = L_TANGIBLE_ID
1143 AND REQTYPE = 'ORAPMTREQ';
1144 ELSE
1145 L_AUTHORIZED_AMOUNT := 0;
1146 END IF;
1147 END IF;
1148 EXCEPTION
1149 WHEN OTHERS THEN
1150 L_AUTHORIZED_AMOUNT := 0;
1151 END;
1152 L_AUTHORIZED_AMOUNT_SUM := L_AUTHORIZED_AMOUNT_SUM + L_AUTHORIZED_AMOUNT;
1153 END LOOP;
1154 RETURN L_AUTHORIZED_AMOUNT_SUM;
1155 EXCEPTION
1156 WHEN OTHERS THEN
1157 RETURN 0;
1158 END CF_AUTHORIZED_AMOUNTFORMULA;
1159
1160 FUNCTION CF_LINE_INITIAL_DUE_TOTALFORMU(HEADER_ID IN NUMBER
1161 ,LINE_ID IN NUMBER) RETURN NUMBER IS
1162 L_PAY_NOW_SUBTOTAL NUMBER;
1163 L_PAY_NOW_TAX NUMBER;
1164 L_PAY_NOW_CHARGES NUMBER;
1165 L_PAY_NOW_TOTAL NUMBER;
1166 L_PAY_NOW_COMMITMENT NUMBER;
1167 L_MSG_COUNT NUMBER;
1168 L_MSG_DATA VARCHAR2(30);
1169 L_RETURN_STATUS VARCHAR2(30);
1170 BEGIN
1171 IF OE_PREPAYMENT_UTIL.GET_INSTALLMENT_OPTIONS in ('ENABLE_PAY_NOW','AUTHORIZE_FIRST_INSTALLMENT') THEN
1172 /*SRW.REFERENCE(HEADER_ID)*/NULL;
1173 /*SRW.REFERENCE(LINE_ID)*/NULL;
1174 OE_PREPAYMENT_PVT.GET_PAY_NOW_AMOUNTS(P_HEADER_ID => HEADER_ID
1175 ,P_LINE_ID => LINE_ID
1176 ,X_PAY_NOW_SUBTOTAL => L_PAY_NOW_SUBTOTAL
1177 ,X_PAY_NOW_TAX => L_PAY_NOW_TAX
1178 ,X_PAY_NOW_CHARGES => L_PAY_NOW_CHARGES
1179 ,X_PAY_NOW_TOTAL => L_PAY_NOW_TOTAL
1180 ,X_PAY_NOW_COMMITMENT => L_PAY_NOW_COMMITMENT
1181 ,X_MSG_COUNT => L_MSG_COUNT
1182 ,X_MSG_DATA => L_MSG_DATA
1183 ,X_RETURN_STATUS => L_RETURN_STATUS);
1184 CP_LINE_COMMITMENT := L_PAY_NOW_COMMITMENT;
1185 RETURN L_PAY_NOW_TOTAL;
1186 ELSE
1187 RETURN 0;
1188 END IF;
1189 EXCEPTION
1190 WHEN OTHERS THEN
1191 RETURN 0;
1192 END CF_LINE_INITIAL_DUE_TOTALFORMU;
1193
1194 FUNCTION CF_LINE_INITIAL_DUE_BALFORMULA(CF_LINE_INITIAL_DUE_TOTAL IN NUMBER) RETURN NUMBER IS
1195 L_BALANCE_DUE NUMBER;
1196 BEGIN
1197 /*SRW.REFERENCE(CF_LINE_INITIAL_DUE_TOTAL)*/NULL;
1198 /*SRW.REFERENCE(CP_LINE_COMMITMENT)*/NULL;
1199 L_BALANCE_DUE := NVL(CF_LINE_INITIAL_DUE_TOTAL
1200 ,0) - NVL(CP_LINE_COMMITMENT
1201 ,0);
1202 IF L_BALANCE_DUE >= 0 THEN
1203 RETURN L_BALANCE_DUE;
1204 ELSE
1205 RETURN 0;
1206 END IF;
1207 END CF_LINE_INITIAL_DUE_BALFORMULA;
1208
1209 FUNCTION CF_LINE_AUTHORIZED_AMOUNTFORMU(LINE_ID IN NUMBER) RETURN NUMBER IS
1210 CURSOR CC_TRXN_EXTN_IDS_CUR(P_LINE_ID IN NUMBER) IS
1211 SELECT
1212 TRXN_EXTENSION_ID,
1213 TANGIBLE_ID
1214 FROM
1215 OE_PAYMENTS
1216 WHERE LINE_ID = P_LINE_ID
1217 AND NVL(PAYMENT_TYPE_CODE
1218 ,'COMMITMENT') = 'CREDIT_CARD';
1219 L_TRXN_EXTENSION_ID VARCHAR2(80);
1220 L_TANGIBLE_ID NUMBER;
1221 L_AUTHORIZED_AMOUNT NUMBER := 0;
1222 L_AUTHORIZED_AMOUNT_SUM NUMBER := 0;
1223 BEGIN
1224 /*SRW.REFERENCE(LINE_ID)*/NULL;
1225 FOR cc_trxn_extn_ids IN cc_trxn_extn_ids_cur(line_id) LOOP
1226 L_TRXN_EXTENSION_ID := CC_TRXN_EXTN_IDS.TRXN_EXTENSION_ID;
1227 L_TANGIBLE_ID := CC_TRXN_EXTN_IDS.TANGIBLE_ID;
1228 BEGIN
1229 IF L_TRXN_EXTENSION_ID IS NOT NULL THEN
1230 SELECT
1231 AUTHORIZATION_AMOUNT
1232 INTO L_AUTHORIZED_AMOUNT
1233 FROM
1234 IBY_TRXN_EXT_AUTHS_V
1235 WHERE TRXN_EXTENSION_ID = L_TRXN_EXTENSION_ID
1236 AND AUTHORIZATION_STATUS = 0;
1237 ELSE
1238 IF L_TANGIBLE_ID IS NOT NULL THEN
1239 SELECT
1240 AMOUNT
1241 INTO L_AUTHORIZED_AMOUNT
1242 FROM
1243 IBY_TRXN_SUMMARIES_ALL
1244 WHERE TANGIBLEID = L_TANGIBLE_ID
1245 AND REQTYPE = 'ORAPMTREQ';
1246 ELSE
1247 L_AUTHORIZED_AMOUNT := 0;
1248 END IF;
1249 END IF;
1250 EXCEPTION
1251 WHEN OTHERS THEN
1252 L_AUTHORIZED_AMOUNT := 0;
1253 END;
1254 L_AUTHORIZED_AMOUNT_SUM := L_AUTHORIZED_AMOUNT_SUM + L_AUTHORIZED_AMOUNT;
1255 END LOOP;
1256 RETURN L_AUTHORIZED_AMOUNT_SUM;
1257 EXCEPTION
1258 WHEN OTHERS THEN
1259 RETURN 0;
1260 END CF_LINE_AUTHORIZED_AMOUNTFORMU;
1261
1262 FUNCTION CF_END_CUSTOMERFORMULA(END_CUSTOMER_ID IN NUMBER) RETURN CHAR IS
1263 L_END_CUSTOMER VARCHAR2(150);
1264 BEGIN
1265 IF END_CUSTOMER_ID IS NOT NULL THEN
1266 SELECT
1267 PARTY.PARTY_NAME
1268 INTO L_END_CUSTOMER
1269 FROM
1270 HZ_PARTIES PARTY,
1271 HZ_CUST_ACCOUNTS CUST_ACCT
1272 WHERE cust_acct.cust_account_id (+) = END_CUSTOMER_ID
1273 AND CUST_ACCT.PARTY_ID = party.party_id (+);
1274 RETURN (L_END_CUSTOMER);
1275 ELSE
1276 RETURN NULL;
1277 END IF;
1278 EXCEPTION
1279 WHEN NO_DATA_FOUND THEN
1280 RETURN NULL;
1281 END CF_END_CUSTOMERFORMULA;
1282
1283 FUNCTION CF_END_CUSTOMER_ADDRESS1FORMUL(END_CUSTOMER_SITE_USE_ID IN NUMBER) RETURN CHAR IS
1284 L_END_CUSTOMER_ADDRESS1 VARCHAR2(50);
1285 BEGIN
1286 IF END_CUSTOMER_SITE_USE_ID IS NOT NULL THEN
1287 SELECT
1288 LOC.ADDRESS1
1289 INTO L_END_CUSTOMER_ADDRESS1
1290 FROM
1291 HZ_LOCATIONS LOC,
1292 HZ_PARTY_SITES SITE,
1293 HZ_CUST_SITE_USES_ALL SITE_USE,
1294 HZ_CUST_ACCT_SITES_ALL ACCT_SITE
1295 WHERE SITE_USE.SITE_USE_ID = END_CUSTOMER_SITE_USE_ID
1296 AND SITE_USE.CUST_ACCT_SITE_ID = ACCT_SITE.CUST_ACCT_SITE_ID
1297 AND ACCT_SITE.PARTY_SITE_ID = SITE.PARTY_SITE_ID
1298 AND SITE.LOCATION_ID = LOC.LOCATION_ID;
1299 RETURN (L_END_CUSTOMER_ADDRESS1);
1300 ELSE
1301 RETURN NULL;
1302 END IF;
1303 EXCEPTION
1304 WHEN NO_DATA_FOUND THEN
1305 RETURN NULL;
1306 END CF_END_CUSTOMER_ADDRESS1FORMUL;
1307
1308 FUNCTION CF_END_CUSTOMER_ADDRESS5FORMUL(END_CUSTOMER_SITE_USE_ID IN NUMBER) RETURN CHAR IS
1309 L_END_CUSTOMER_CITY VARCHAR2(50);
1310 L_END_CUSTOMER_STATE VARCHAR2(50);
1311 L_END_CUSTOMER_POSTAL_CODE VARCHAR2(50);
1312 L_END_CUSTOMER_COUNTRY VARCHAR2(50);
1313 L_END_CUSTOMER_ADDRESS5 VARCHAR2(240);
1314 BEGIN
1315 IF END_CUSTOMER_SITE_USE_ID IS NOT NULL THEN
1316 SELECT
1317 LOC.CITY,
1318 NVL(LOC.STATE
1319 ,LOC.PROVINCE),
1320 LOC.POSTAL_CODE,
1321 LOC.COUNTRY
1322 INTO L_END_CUSTOMER_CITY,L_END_CUSTOMER_STATE,L_END_CUSTOMER_POSTAL_CODE,L_END_CUSTOMER_COUNTRY
1323 FROM
1324 HZ_LOCATIONS LOC,
1325 HZ_PARTY_SITES SITE,
1326 HZ_CUST_SITE_USES SITE_USE,
1327 HZ_CUST_ACCT_SITES ACCT_SITE
1328 WHERE SITE_USE.SITE_USE_ID = END_CUSTOMER_SITE_USE_ID
1329 AND SITE_USE.CUST_ACCT_SITE_ID = ACCT_SITE.CUST_ACCT_SITE_ID
1330 AND ACCT_SITE.PARTY_SITE_ID = SITE.PARTY_SITE_ID
1331 AND SITE.LOCATION_ID = LOC.LOCATION_ID;
1332 SELECT
1333 DECODE(L_END_CUSTOMER_CITY
1334 ,NULL
1335 ,NULL
1336 ,L_END_CUSTOMER_CITY || ', ') || DECODE(L_END_CUSTOMER_STATE
1337 ,NULL
1338 ,NULL
1339 ,L_END_CUSTOMER_STATE || ', ') || DECODE(L_END_CUSTOMER_POSTAL_CODE
1340 ,NULL
1341 ,NULL
1342 ,L_END_CUSTOMER_POSTAL_CODE || ',') || DECODE(L_END_CUSTOMER_COUNTRY
1343 ,NULL
1344 ,NULL
1345 ,L_END_CUSTOMER_COUNTRY)
1346 INTO L_END_CUSTOMER_ADDRESS5
1347 FROM
1348 DUAL;
1349 RETURN (L_END_CUSTOMER_ADDRESS5);
1350 ELSE
1351 RETURN NULL;
1352 END IF;
1353 EXCEPTION
1354 WHEN NO_DATA_FOUND THEN
1355 RETURN NULL;
1356 END CF_END_CUSTOMER_ADDRESS5FORMUL;
1357
1358 FUNCTION CF_END_CUSTOMER_CONTACTFORMULA(END_CUSTOMER_CONTACT_ID IN NUMBER) RETURN CHAR IS
1359 L_END_CUSTOMER_CONTACT VARCHAR2(400);
1360 BEGIN
1361 IF END_CUSTOMER_CONTACT_ID IS NOT NULL THEN
1362 SELECT
1363 NAME
1364 INTO L_END_CUSTOMER_CONTACT
1365 FROM
1366 OE_CONTACTS_V
1367 WHERE CONTACT_ID = END_CUSTOMER_CONTACT_ID;
1368 RETURN L_END_CUSTOMER_CONTACT;
1369 ELSE
1370 RETURN NULL;
1371 END IF;
1372 EXCEPTION
1373 WHEN NO_DATA_FOUND THEN
1374 RETURN NULL;
1375 END CF_END_CUSTOMER_CONTACTFORMULA;
1376
1377 FUNCTION CF_END_CUSTOMER_COUNTRYFORMULA(END_CUSTOMER_SITE_USE_ID IN NUMBER) RETURN CHAR IS
1378 L_END_CUSTOMER_COUNTRY VARCHAR2(20);
1379 BEGIN
1380 IF END_CUSTOMER_SITE_USE_ID IS NOT NULL THEN
1381 SELECT
1382 LOC.COUNTRY
1383 INTO L_END_CUSTOMER_COUNTRY
1384 FROM
1385 HZ_LOCATIONS LOC,
1386 HZ_PARTY_SITES SITE,
1387 HZ_CUST_SITE_USES_ALL SITE_USE,
1388 HZ_CUST_ACCT_SITES_ALL ACCT_SITE
1389 WHERE SITE_USE.SITE_USE_ID = END_CUSTOMER_SITE_USE_ID
1390 AND SITE_USE.CUST_ACCT_SITE_ID = ACCT_SITE.CUST_ACCT_SITE_ID
1391 AND ACCT_SITE.PARTY_SITE_ID = SITE.PARTY_SITE_ID
1392 AND SITE.LOCATION_ID = LOC.LOCATION_ID;
1393 RETURN (L_END_CUSTOMER_COUNTRY);
1394 ELSE
1395 RETURN NULL;
1396 END IF;
1397 EXCEPTION
1398 WHEN NO_DATA_FOUND THEN
1399 RETURN NULL;
1400 END CF_END_CUSTOMER_COUNTRYFORMULA;
1401
1402 FUNCTION CF_END_CUSTOMER_NUMBERFORMULA(END_CUSTOMER_ID IN NUMBER) RETURN NUMBER IS
1403 L_END_CUSTOMER NUMBER;
1404 BEGIN
1405 IF END_CUSTOMER_ID IS NOT NULL THEN
1406 SELECT
1407 PARTY.PARTY_NUMBER
1408 INTO L_END_CUSTOMER
1409 FROM
1410 HZ_PARTIES PARTY,
1411 HZ_CUST_ACCOUNTS CUST_ACCT
1412 WHERE cust_acct.cust_account_id (+) = END_CUSTOMER_ID
1413 AND CUST_ACCT.PARTY_ID = party.party_id (+);
1414 RETURN (L_END_CUSTOMER);
1415 ELSE
1416 RETURN NULL;
1417 END IF;
1418 EXCEPTION
1419 WHEN NO_DATA_FOUND THEN
1420 RETURN NULL;
1421 END CF_END_CUSTOMER_NUMBERFORMULA;
1422
1423 FUNCTION CF_IB_CURRENT_ADDRESS1FORMULA(IB_CURRENT_LOCATION IN VARCHAR2
1424 ,L_SHIP_ADDRESS IN VARCHAR2
1425 ,L_BILL_ADDRESS IN VARCHAR2
1426 ,DELIVER_TO_ORG_ID IN NUMBER
1427 ,HEADER_ID IN NUMBER
1428 ,END_CUSTOMER_SITE_USE_ID IN NUMBER) RETURN CHAR IS
1429 L_ADDRESS1 VARCHAR2(40);
1430 BEGIN
1431 IF IB_CURRENT_LOCATION = 'SHIP_TO' THEN
1432 RETURN (L_SHIP_ADDRESS);
1433 ELSIF IB_CURRENT_LOCATION = 'BILL_TO' THEN
1434 RETURN (L_BILL_ADDRESS);
1435 ELSIF IB_CURRENT_LOCATION = 'DELIVER_TO' THEN
1436 SELECT
1437 L.ADDRESS_LINE_1
1438 INTO L_ADDRESS1
1439 FROM
1440 OE_DELIVER_TO_ORGS_V L
1441 WHERE L.ORGANIZATION_ID = DELIVER_TO_ORG_ID;
1442 RETURN (L_ADDRESS1);
1443 ELSIF IB_CURRENT_LOCATION = 'SOLD_TO' THEN
1444 SELECT
1445 LOC.ADDRESS1
1446 INTO L_ADDRESS1
1447 FROM
1448 HZ_LOCATIONS LOC,
1449 HZ_PARTY_SITES SITE,
1450 HZ_CUST_SITE_USES_ALL SITE_USE,
1451 HZ_CUST_ACCT_SITES_ALL ACCT_SITE
1452 WHERE SITE_USE.SITE_USE_ID = (
1453 SELECT
1454 SOLD_TO_SITE_USE_ID
1455 FROM
1456 OE_ORDER_HEADERS_ALL
1457 WHERE HEADER_ID = HEADER_ID )
1458 AND SITE_USE.CUST_ACCT_SITE_ID = ACCT_SITE.CUST_ACCT_SITE_ID
1459 AND ACCT_SITE.PARTY_SITE_ID = SITE.PARTY_SITE_ID
1460 AND SITE.LOCATION_ID = LOC.LOCATION_ID;
1461 RETURN (L_ADDRESS1);
1462 ELSIF IB_CURRENT_LOCATION = 'END_CUSTOMER' THEN
1463 SELECT
1464 LOC.ADDRESS1
1465 INTO L_ADDRESS1
1466 FROM
1467 HZ_LOCATIONS LOC,
1468 HZ_PARTY_SITES SITE,
1469 HZ_CUST_SITE_USES_ALL SITE_USE,
1470 HZ_CUST_ACCT_SITES_ALL ACCT_SITE
1471 WHERE SITE_USE.SITE_USE_ID = END_CUSTOMER_SITE_USE_ID
1472 AND SITE_USE.CUST_ACCT_SITE_ID = ACCT_SITE.CUST_ACCT_SITE_ID
1473 AND ACCT_SITE.PARTY_SITE_ID = SITE.PARTY_SITE_ID
1474 AND SITE.LOCATION_ID = LOC.LOCATION_ID;
1475 RETURN (L_ADDRESS1);
1476 ELSE
1477 RETURN NULL;
1478 END IF;
1479 EXCEPTION
1480 WHEN NO_DATA_FOUND THEN
1481 RETURN NULL;
1482 END CF_IB_CURRENT_ADDRESS1FORMULA;
1483
1484 FUNCTION CF_IB_CURRENT_ADDRESS5FORMULA(IB_CURRENT_LOCATION IN VARCHAR2
1485 ,SHIP_TO_ADDRESS5 IN VARCHAR2
1486 ,INVOICE_TO_ADDRESS5 IN VARCHAR2
1487 ,DELIVER_TO_ORG_ID IN NUMBER
1488 ,HEADER_ID IN NUMBER
1489 ,END_CUSTOMER_SITE_USE_ID IN NUMBER) RETURN CHAR IS
1490 L_CITY VARCHAR2(20);
1491 L_STATE VARCHAR2(20);
1492 L_POSTAL_CODE VARCHAR2(20);
1493 L_COUNTRY VARCHAR2(20);
1494 L_ADDRESS5 VARCHAR2(240);
1495 BEGIN
1496 IF IB_CURRENT_LOCATION = 'SHIP_TO' THEN
1497 RETURN (SHIP_TO_ADDRESS5);
1498 ELSIF IB_CURRENT_LOCATION = 'BILL_TO' THEN
1499 RETURN (INVOICE_TO_ADDRESS5);
1500 ELSIF IB_CURRENT_LOCATION = 'DELIVER_TO' THEN
1501 SELECT
1502 L.TOWN_OR_CITY,
1503 L.STATE,
1504 L.POSTAL_CODE,
1505 L.COUNTRY
1506 INTO L_CITY,L_STATE,L_POSTAL_CODE,L_COUNTRY
1507 FROM
1508 OE_DELIVER_TO_ORGS_V L
1509 WHERE L.ORGANIZATION_ID = DELIVER_TO_ORG_ID;
1510 SELECT
1511 DECODE(L_CITY
1512 ,NULL
1513 ,NULL
1514 ,L_CITY || ', ') || DECODE(L_STATE
1515 ,NULL
1516 ,NULL
1517 ,L_STATE || ', ') || DECODE(L_POSTAL_CODE
1518 ,NULL
1519 ,NULL
1520 ,L_POSTAL_CODE || ',') || DECODE(L_COUNTRY
1521 ,NULL
1522 ,NULL
1523 ,L_COUNTRY)
1524 INTO L_ADDRESS5
1525 FROM
1526 DUAL;
1527 RETURN (L_ADDRESS5);
1528 ELSIF IB_CURRENT_LOCATION = 'SOLD_TO' THEN
1529 SELECT
1530 LOC.CITY,
1531 LOC.STATE,
1532 LOC.POSTAL_CODE,
1533 LOC.COUNTRY
1534 INTO L_CITY,L_STATE,L_POSTAL_CODE,L_COUNTRY
1535 FROM
1536 HZ_LOCATIONS LOC,
1537 HZ_PARTY_SITES SITE,
1538 HZ_CUST_SITE_USES_ALL SITE_USE,
1539 HZ_CUST_ACCT_SITES_ALL ACCT_SITE
1540 WHERE SITE_USE.SITE_USE_ID = (
1541 SELECT
1542 SOLD_TO_SITE_USE_ID
1543 FROM
1544 OE_ORDER_HEADERS_ALL
1545 WHERE HEADER_ID = HEADER_ID )
1546 AND SITE_USE.CUST_ACCT_SITE_ID = ACCT_SITE.CUST_ACCT_SITE_ID
1547 AND ACCT_SITE.PARTY_SITE_ID = SITE.PARTY_SITE_ID
1548 AND SITE.LOCATION_ID = LOC.LOCATION_ID;
1549 SELECT
1550 DECODE(L_CITY
1551 ,NULL
1552 ,NULL
1553 ,L_CITY || ', ') || DECODE(L_STATE
1554 ,NULL
1555 ,NULL
1556 ,L_STATE || ', ') || DECODE(L_POSTAL_CODE
1557 ,NULL
1558 ,NULL
1559 ,L_POSTAL_CODE || ',') || DECODE(L_COUNTRY
1560 ,NULL
1561 ,NULL
1562 ,L_COUNTRY)
1563 INTO L_ADDRESS5
1564 FROM
1565 DUAL;
1566 RETURN (L_ADDRESS5);
1567 ELSIF IB_CURRENT_LOCATION = 'END_CUSTOMER' THEN
1568 SELECT
1569 LOC.CITY,
1570 LOC.STATE,
1571 LOC.POSTAL_CODE,
1572 LOC.COUNTRY
1573 INTO L_CITY,L_STATE,L_POSTAL_CODE,L_COUNTRY
1574 FROM
1575 HZ_LOCATIONS LOC,
1576 HZ_PARTY_SITES SITE,
1577 HZ_CUST_SITE_USES_ALL SITE_USE,
1578 HZ_CUST_ACCT_SITES_ALL ACCT_SITE
1579 WHERE SITE_USE.SITE_USE_ID = END_CUSTOMER_SITE_USE_ID
1580 AND SITE_USE.CUST_ACCT_SITE_ID = ACCT_SITE.CUST_ACCT_SITE_ID
1581 AND ACCT_SITE.PARTY_SITE_ID = SITE.PARTY_SITE_ID
1582 AND SITE.LOCATION_ID = LOC.LOCATION_ID;
1583 SELECT
1584 DECODE(L_CITY
1585 ,NULL
1586 ,NULL
1587 ,L_CITY || ', ') || DECODE(L_STATE
1588 ,NULL
1589 ,NULL
1590 ,L_STATE || ', ') || DECODE(L_POSTAL_CODE
1591 ,NULL
1592 ,NULL
1593 ,L_POSTAL_CODE || ',') || DECODE(L_COUNTRY
1594 ,NULL
1595 ,NULL
1596 ,L_COUNTRY)
1597 INTO L_ADDRESS5
1598 FROM
1599 DUAL;
1600 RETURN (L_ADDRESS5);
1601 ELSE
1602 RETURN NULL;
1603 END IF;
1604 EXCEPTION
1605 WHEN NO_DATA_FOUND THEN
1606 RETURN NULL;
1607 END CF_IB_CURRENT_ADDRESS5FORMULA;
1608
1609 FUNCTION CF_IB_INSTALLED_AT_ADDRESS1FOR(IB_INSTALLED_AT_LOCATION IN VARCHAR2
1610 ,L_SHIP_ADDRESS IN VARCHAR2
1611 ,L_BILL_ADDRESS IN VARCHAR2
1612 ,DELIVER_TO_ORG_ID IN NUMBER
1613 ,HEADER_ID IN NUMBER
1614 ,END_CUSTOMER_SITE_USE_ID IN NUMBER) RETURN CHAR IS
1615 L_ADDRESS1 VARCHAR2(40);
1616 BEGIN
1617 IF IB_INSTALLED_AT_LOCATION = 'SHIP_TO' THEN
1618 RETURN (L_SHIP_ADDRESS);
1619 ELSIF IB_INSTALLED_AT_LOCATION = 'BILL_TO' THEN
1620 RETURN (L_BILL_ADDRESS);
1621 ELSIF IB_INSTALLED_AT_LOCATION = 'DELIVER_TO' THEN
1622 SELECT
1623 L.ADDRESS_LINE_1
1624 INTO L_ADDRESS1
1625 FROM
1626 OE_DELIVER_TO_ORGS_V L
1627 WHERE L.ORGANIZATION_ID = DELIVER_TO_ORG_ID;
1628 RETURN (L_ADDRESS1);
1629 ELSIF IB_INSTALLED_AT_LOCATION = 'SOLD_TO' THEN
1630 SELECT
1631 LOC.ADDRESS1
1632 INTO L_ADDRESS1
1633 FROM
1634 HZ_LOCATIONS LOC,
1635 HZ_PARTY_SITES SITE,
1636 HZ_CUST_SITE_USES_ALL SITE_USE,
1637 HZ_CUST_ACCT_SITES_ALL ACCT_SITE
1638 WHERE SITE_USE.SITE_USE_ID = (
1639 SELECT
1640 SOLD_TO_SITE_USE_ID
1641 FROM
1642 OE_ORDER_HEADERS_ALL
1643 WHERE HEADER_ID = HEADER_ID )
1644 AND SITE_USE.CUST_ACCT_SITE_ID = ACCT_SITE.CUST_ACCT_SITE_ID
1645 AND ACCT_SITE.PARTY_SITE_ID = SITE.PARTY_SITE_ID
1646 AND SITE.LOCATION_ID = LOC.LOCATION_ID;
1647 RETURN (L_ADDRESS1);
1648 ELSIF IB_INSTALLED_AT_LOCATION = 'END_CUSTOMER' THEN
1649 SELECT
1650 LOC.ADDRESS1
1651 INTO L_ADDRESS1
1652 FROM
1653 HZ_LOCATIONS LOC,
1654 HZ_PARTY_SITES SITE,
1655 HZ_CUST_SITE_USES_ALL SITE_USE,
1656 HZ_CUST_ACCT_SITES_ALL ACCT_SITE
1657 WHERE SITE_USE.SITE_USE_ID = END_CUSTOMER_SITE_USE_ID
1658 AND SITE_USE.CUST_ACCT_SITE_ID = ACCT_SITE.CUST_ACCT_SITE_ID
1659 AND ACCT_SITE.PARTY_SITE_ID = SITE.PARTY_SITE_ID
1660 AND SITE.LOCATION_ID = LOC.LOCATION_ID;
1661 RETURN (L_ADDRESS1);
1662 ELSE
1663 RETURN NULL;
1664 END IF;
1665 EXCEPTION
1666 WHEN NO_DATA_FOUND THEN
1667 RETURN NULL;
1668 END CF_IB_INSTALLED_AT_ADDRESS1FOR;
1669
1670 FUNCTION CF_INSTALLED_AT_ADDRESS5FORMUL(IB_INSTALLED_AT_LOCATION IN VARCHAR2
1671 ,SHIP_TO_ADDRESS5 IN VARCHAR2
1672 ,INVOICE_TO_ADDRESS5 IN VARCHAR2
1673 ,DELIVER_TO_ORG_ID IN NUMBER
1674 ,HEADER_ID IN NUMBER
1675 ,END_CUSTOMER_SITE_USE_ID IN NUMBER) RETURN CHAR IS
1676 L_CITY VARCHAR2(20);
1677 L_STATE VARCHAR2(20);
1678 L_POSTAL_CODE VARCHAR2(20);
1679 L_COUNTRY VARCHAR2(20);
1680 L_ADDRESS5 VARCHAR2(240);
1681 BEGIN
1682 IF IB_INSTALLED_AT_LOCATION = 'SHIP_TO' THEN
1683 RETURN (SHIP_TO_ADDRESS5);
1684 ELSIF IB_INSTALLED_AT_LOCATION = 'BILL_TO' THEN
1685 RETURN (INVOICE_TO_ADDRESS5);
1686 ELSIF IB_INSTALLED_AT_LOCATION = 'DELIVER_TO' THEN
1687 SELECT
1688 L.TOWN_OR_CITY,
1689 L.STATE,
1690 L.POSTAL_CODE,
1691 L.COUNTRY
1692 INTO L_CITY,L_STATE,L_POSTAL_CODE,L_COUNTRY
1693 FROM
1694 OE_DELIVER_TO_ORGS_V L
1695 WHERE L.ORGANIZATION_ID = DELIVER_TO_ORG_ID;
1696 SELECT
1697 DECODE(L_CITY
1698 ,NULL
1699 ,NULL
1700 ,L_CITY || ', ') || DECODE(L_STATE
1701 ,NULL
1702 ,NULL
1703 ,L_STATE || ', ') || DECODE(L_POSTAL_CODE
1704 ,NULL
1705 ,NULL
1706 ,L_POSTAL_CODE || ',') || DECODE(L_COUNTRY
1707 ,NULL
1708 ,NULL
1709 ,L_COUNTRY)
1710 INTO L_ADDRESS5
1711 FROM
1712 DUAL;
1713 RETURN (L_ADDRESS5);
1714 ELSIF IB_INSTALLED_AT_LOCATION = 'SOLD_TO' THEN
1715 SELECT
1716 LOC.CITY,
1717 LOC.STATE,
1718 LOC.POSTAL_CODE,
1719 LOC.COUNTRY
1720 INTO L_CITY,L_STATE,L_POSTAL_CODE,L_COUNTRY
1721 FROM
1722 HZ_LOCATIONS LOC,
1723 HZ_PARTY_SITES SITE,
1724 HZ_CUST_SITE_USES_ALL SITE_USE,
1725 HZ_CUST_ACCT_SITES_ALL ACCT_SITE
1726 WHERE SITE_USE.SITE_USE_ID = (
1727 SELECT
1728 SOLD_TO_SITE_USE_ID
1729 FROM
1730 OE_ORDER_HEADERS_ALL
1731 WHERE HEADER_ID = HEADER_ID )
1732 AND SITE_USE.CUST_ACCT_SITE_ID = ACCT_SITE.CUST_ACCT_SITE_ID
1733 AND ACCT_SITE.PARTY_SITE_ID = SITE.PARTY_SITE_ID
1734 AND SITE.LOCATION_ID = LOC.LOCATION_ID;
1735 SELECT
1736 DECODE(L_CITY
1737 ,NULL
1738 ,NULL
1739 ,L_CITY || ', ') || DECODE(L_STATE
1740 ,NULL
1741 ,NULL
1742 ,L_STATE || ', ') || DECODE(L_POSTAL_CODE
1743 ,NULL
1744 ,NULL
1745 ,L_POSTAL_CODE || ',') || DECODE(L_COUNTRY
1746 ,NULL
1747 ,NULL
1748 ,L_COUNTRY)
1749 INTO L_ADDRESS5
1750 FROM
1751 DUAL;
1752 RETURN (L_ADDRESS5);
1753 ELSIF IB_INSTALLED_AT_LOCATION = 'END_CUSTOMER' THEN
1754 SELECT
1755 LOC.CITY,
1756 LOC.STATE,
1757 LOC.POSTAL_CODE,
1758 LOC.COUNTRY
1759 INTO L_CITY,L_STATE,L_POSTAL_CODE,L_COUNTRY
1760 FROM
1761 HZ_LOCATIONS LOC,
1762 HZ_PARTY_SITES SITE,
1763 HZ_CUST_SITE_USES_ALL SITE_USE,
1764 HZ_CUST_ACCT_SITES_ALL ACCT_SITE
1765 WHERE SITE_USE.SITE_USE_ID = END_CUSTOMER_SITE_USE_ID
1766 AND SITE_USE.CUST_ACCT_SITE_ID = ACCT_SITE.CUST_ACCT_SITE_ID
1767 AND ACCT_SITE.PARTY_SITE_ID = SITE.PARTY_SITE_ID
1768 AND SITE.LOCATION_ID = LOC.LOCATION_ID;
1769 SELECT
1770 DECODE(L_CITY
1771 ,NULL
1772 ,NULL
1773 ,L_CITY || ', ') || DECODE(L_STATE
1774 ,NULL
1775 ,NULL
1776 ,L_STATE || ', ') || DECODE(L_POSTAL_CODE
1777 ,NULL
1778 ,NULL
1779 ,L_POSTAL_CODE || ',') || DECODE(L_COUNTRY
1780 ,NULL
1781 ,NULL
1782 ,L_COUNTRY)
1783 INTO L_ADDRESS5
1784 FROM
1785 DUAL;
1786 RETURN (L_ADDRESS5);
1787 ELSE
1788 RETURN NULL;
1789 END IF;
1790 EXCEPTION
1791 WHEN NO_DATA_FOUND THEN
1792 RETURN NULL;
1793 END CF_INSTALLED_AT_ADDRESS5FORMUL;
1794
1795 FUNCTION CF_IB_OWNERFORMULA(IB_OWNER IN VARCHAR2
1796 ,HEADER_ID IN NUMBER
1797 ,END_CUSTOMER_ID IN NUMBER) RETURN CHAR IS
1798 L_SOLD_TO VARCHAR2(50);
1799 L_END_CUSTOMER VARCHAR2(50);
1800 BEGIN
1801 IF IB_OWNER = 'SOLD_TO' THEN
1802 SELECT
1803 SOLD_TO
1804 INTO L_SOLD_TO
1805 FROM
1806 OE_ORDER_HEADERS_V H
1807 WHERE H.HEADER_ID = HEADER_ID;
1808 RETURN (L_SOLD_TO);
1809 ELSIF IB_OWNER = 'END_CUSTOMER' THEN
1810 SELECT
1811 PARTY.PARTY_NAME
1812 INTO L_END_CUSTOMER
1813 FROM
1814 HZ_PARTIES PARTY,
1815 HZ_CUST_ACCOUNTS CUST_ACCT
1816 WHERE cust_acct.cust_account_id (+) = END_CUSTOMER_ID
1817 AND CUST_ACCT.PARTY_ID = party.party_id (+);
1818 RETURN (L_END_CUSTOMER);
1819 ELSE
1820 RETURN NULL;
1821 END IF;
1822 EXCEPTION
1823 WHEN NO_DATA_FOUND THEN
1824 RETURN NULL;
1825 END CF_IB_OWNERFORMULA;
1826
1827 FUNCTION CF_END_DISPLAYFORMULA RETURN CHAR IS
1828 PA_END_DISPLAY VARCHAR2(80);
1829 BEGIN
1830 SELECT
1831 MEANING
1832 INTO PA_END_DISPLAY
1833 FROM
1834 OE_LOOKUPS
1835 WHERE LOOKUP_TYPE = 'YES_NO'
1836 AND LOOKUP_CODE = P_END_CUST;
1837 RETURN (PA_END_DISPLAY);
1838 EXCEPTION
1839 WHEN NO_DATA_FOUND THEN
1840 RETURN (NULL);
1841 END CF_END_DISPLAYFORMULA;
1842
1843 FUNCTION CP_STD_PRECISION_P RETURN NUMBER IS
1844 BEGIN
1845 RETURN CP_STD_PRECISION;
1846 END CP_STD_PRECISION_P;
1847
1848 FUNCTION CP_EXT_PRECISION_P RETURN NUMBER IS
1849 BEGIN
1850 RETURN CP_EXT_PRECISION;
1851 END CP_EXT_PRECISION_P;
1852
1853 FUNCTION CP_MIN_ACCT_UNIT_P RETURN NUMBER IS
1854 BEGIN
1855 RETURN CP_MIN_ACCT_UNIT;
1856 END CP_MIN_ACCT_UNIT_P;
1857
1858 FUNCTION CP_COMMITMENT_P RETURN NUMBER IS
1859 BEGIN
1860 RETURN CP_COMMITMENT;
1861 END CP_COMMITMENT_P;
1862
1863 FUNCTION CP_LINE_COMMITMENT_P RETURN NUMBER IS
1864 BEGIN
1865 RETURN CP_LINE_COMMITMENT;
1866 END CP_LINE_COMMITMENT_P;
1867
1868 FUNCTION RP_CURR_PROFILE_P RETURN VARCHAR2 IS
1869 BEGIN
1870 RETURN RP_CURR_PROFILE;
1871 END RP_CURR_PROFILE_P;
1872
1873 FUNCTION RP_ITEM_FLEX_ALL_SEG_P RETURN VARCHAR2 IS
1874 BEGIN
1875 RETURN RP_ITEM_FLEX_ALL_SEG;
1876 END RP_ITEM_FLEX_ALL_SEG_P;
1877
1878 FUNCTION RP_ITEM_FLEX_SEG_VAL_P RETURN VARCHAR2 IS
1879 BEGIN
1880 RETURN RP_ITEM_FLEX_SEG_VAL;
1881 END RP_ITEM_FLEX_SEG_VAL_P;
1882
1883 FUNCTION IS_FIXED_RATE(X_FROM_CURRENCY IN VARCHAR2
1884 ,X_TO_CURRENCY IN VARCHAR2
1885 ,X_EFFECTIVE_DATE IN DATE) RETURN VARCHAR2 IS
1886 X0 VARCHAR2(2000);
1887 BEGIN
1888 /*STPROC.INIT('begin :X0 := GL_CURRENCY_API.IS_FIXED_RATE(:X_FROM_CURRENCY, :X_TO_CURRENCY, :X_EFFECTIVE_DATE); end;');
1889 STPROC.BIND_O(X0);
1890 STPROC.BIND_I(X_FROM_CURRENCY);
1891 STPROC.BIND_I(X_TO_CURRENCY);
1892 STPROC.BIND_I(X_EFFECTIVE_DATE);
1893 STPROC.EXECUTE;
1894 STPROC.RETRIEVE(1
1895 ,X0);*/
1896
1897 X0 := GL_CURRENCY_API.IS_FIXED_RATE(X_FROM_CURRENCY, X_TO_CURRENCY, X_EFFECTIVE_DATE);
1898 RETURN X0;
1899 END IS_FIXED_RATE;
1900
1901 PROCEDURE GET_RELATION(X_FROM_CURRENCY IN VARCHAR2
1902 ,X_TO_CURRENCY IN VARCHAR2
1903 ,X_EFFECTIVE_DATE IN DATE
1904 ,X_FIXED_RATE IN OUT NOCOPY BOOLEAN
1905 ,X_RELATIONSHIP IN OUT NOCOPY VARCHAR2) IS
1906 BEGIN
1907 /*STPROC.INIT('declare X_FIXED_RATE BOOLEAN;
1908 begin X_FIXED_RATE := sys.diutil.int_to_bool(:X_FIXED_RATE);
1909 GL_CURRENCY_API.GET_RELATION(:X_FROM_CURRENCY, :X_TO_CURRENCY, :X_EFFECTIVE_DATE, X_FIXED_RATE, :X_RELATIONSHIP);
1910 :X_FIXED_RATE := sys.diutil.bool_to_int(X_FIXED_RATE); end;');
1911 STPROC.BIND_IO(X_FIXED_RATE);
1912 STPROC.BIND_I(X_FROM_CURRENCY);
1913 STPROC.BIND_I(X_TO_CURRENCY);
1914 STPROC.BIND_I(X_EFFECTIVE_DATE);
1915 STPROC.BIND_IO(X_RELATIONSHIP);
1916 STPROC.EXECUTE;
1917 STPROC.RETRIEVE(1
1918 ,X_FIXED_RATE);
1919 STPROC.RETRIEVE(5
1920 ,X_RELATIONSHIP);*/
1921
1922 declare
1923 X_FIXED_RATE BOOLEAN;
1924 X_FIXED_RATE1 number;
1925 begin
1926 X_FIXED_RATE1 := sys.diutil.bool_to_int(X_FIXED_RATE);
1927 X_FIXED_RATE := sys.diutil.int_to_bool(X_FIXED_RATE1);
1928 GL_CURRENCY_API.GET_RELATION(X_FROM_CURRENCY, X_TO_CURRENCY, X_EFFECTIVE_DATE, X_FIXED_RATE, X_RELATIONSHIP);
1929 X_FIXED_RATE1 := sys.diutil.bool_to_int(X_FIXED_RATE);
1930 end;
1931 END GET_RELATION;
1932
1933 FUNCTION GET_EURO_CODE RETURN VARCHAR2 IS
1934 X0 VARCHAR2(2000);
1935 BEGIN
1936 /*STPROC.INIT('begin :X0 := GL_CURRENCY_API.GET_EURO_CODE; end;');
1937 STPROC.BIND_O(X0);
1938 STPROC.EXECUTE;
1939 STPROC.RETRIEVE(1
1940 ,X0);*/
1941 X0 := GL_CURRENCY_API.GET_EURO_CODE;
1942 RETURN X0;
1943 END GET_EURO_CODE;
1944
1945 FUNCTION GET_RATE(X_FROM_CURRENCY IN VARCHAR2
1946 ,X_TO_CURRENCY IN VARCHAR2
1947 ,X_CONVERSION_DATE IN DATE
1948 ,X_CONVERSION_TYPE IN VARCHAR2) RETURN NUMBER IS
1949 X0 NUMBER;
1950 BEGIN
1951 /*STPROC.INIT('begin :X0 := GL_CURRENCY_API.GET_RATE(:X_FROM_CURRENCY, :X_TO_CURRENCY, :X_CONVERSION_DATE, :X_CONVERSION_TYPE); end;');
1952 STPROC.BIND_O(X0);
1953 STPROC.BIND_I(X_FROM_CURRENCY);
1954 STPROC.BIND_I(X_TO_CURRENCY);
1955 STPROC.BIND_I(X_CONVERSION_DATE);
1956 STPROC.BIND_I(X_CONVERSION_TYPE);
1957 STPROC.EXECUTE;
1958 STPROC.RETRIEVE(1
1959 ,X0);*/
1960
1961 X0 := GL_CURRENCY_API.GET_RATE(X_FROM_CURRENCY, X_TO_CURRENCY, X_CONVERSION_DATE, X_CONVERSION_TYPE);
1962 RETURN X0;
1963 END GET_RATE;
1964
1965 FUNCTION GET_RATE(X_SET_OF_BOOKS_ID IN NUMBER
1966 ,X_FROM_CURRENCY IN VARCHAR2
1967 ,X_CONVERSION_DATE IN DATE
1968 ,X_CONVERSION_TYPE IN VARCHAR2) RETURN NUMBER IS
1969 X0 NUMBER;
1970 BEGIN
1971 /*STPROC.INIT('begin :X0 := GL_CURRENCY_API.GET_RATE(:X_SET_OF_BOOKS_ID, :X_FROM_CURRENCY, :X_CONVERSION_DATE, :X_CONVERSION_TYPE); end;');
1972 STPROC.BIND_O(X0);
1973 STPROC.BIND_I(X_SET_OF_BOOKS_ID);
1974 STPROC.BIND_I(X_FROM_CURRENCY);
1975 STPROC.BIND_I(X_CONVERSION_DATE);
1976 STPROC.BIND_I(X_CONVERSION_TYPE);
1977 STPROC.EXECUTE;
1978 STPROC.RETRIEVE(1
1979 ,X0);*/
1980
1981 X0 := GL_CURRENCY_API.GET_RATE(X_SET_OF_BOOKS_ID, X_FROM_CURRENCY, X_CONVERSION_DATE, X_CONVERSION_TYPE);
1982 RETURN X0;
1983 END GET_RATE;
1984
1985 FUNCTION CONVERT_AMOUNT(X_FROM_CURRENCY IN VARCHAR2
1986 ,X_TO_CURRENCY IN VARCHAR2
1987 ,X_CONVERSION_DATE IN DATE
1988 ,X_CONVERSION_TYPE IN VARCHAR2
1989 ,X_AMOUNT IN NUMBER) RETURN NUMBER IS
1990 X0 NUMBER;
1991 BEGIN
1992 /*STPROC.INIT('begin :X0 := GL_CURRENCY_API.CONVERT_AMOUNT(:X_FROM_CURRENCY, :X_TO_CURRENCY, :X_CONVERSION_DATE, :X_CONVERSION_TYPE, :X_AMOUNT); end;');
1993 STPROC.BIND_O(X0);
1994 STPROC.BIND_I(X_FROM_CURRENCY);
1995 STPROC.BIND_I(X_TO_CURRENCY);
1996 STPROC.BIND_I(X_CONVERSION_DATE);
1997 STPROC.BIND_I(X_CONVERSION_TYPE);
1998 STPROC.BIND_I(X_AMOUNT);
1999 STPROC.EXECUTE;
2000 STPROC.RETRIEVE(1
2001 ,X0);*/
2002
2003 X0 := GL_CURRENCY_API.CONVERT_AMOUNT(X_FROM_CURRENCY, X_TO_CURRENCY, X_CONVERSION_DATE, X_CONVERSION_TYPE, X_AMOUNT);
2004 RETURN X0;
2005 END CONVERT_AMOUNT;
2006
2007 FUNCTION CONVERT_AMOUNT(X_SET_OF_BOOKS_ID IN NUMBER
2008 ,X_FROM_CURRENCY IN VARCHAR2
2009 ,X_CONVERSION_DATE IN DATE
2010 ,X_CONVERSION_TYPE IN VARCHAR2
2011 ,X_AMOUNT IN NUMBER) RETURN NUMBER IS
2012 X0 NUMBER;
2013 BEGIN
2014 /*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;');
2015 STPROC.BIND_O(X0);
2016 STPROC.BIND_I(X_SET_OF_BOOKS_ID);
2017 STPROC.BIND_I(X_FROM_CURRENCY);
2018 STPROC.BIND_I(X_CONVERSION_DATE);
2019 STPROC.BIND_I(X_CONVERSION_TYPE);
2020 STPROC.BIND_I(X_AMOUNT);
2021 STPROC.EXECUTE;
2022 STPROC.RETRIEVE(1
2023 ,X0);*/
2024
2025 X0 := GL_CURRENCY_API.CONVERT_AMOUNT(X_SET_OF_BOOKS_ID, X_FROM_CURRENCY, X_CONVERSION_DATE, X_CONVERSION_TYPE, X_AMOUNT);
2026 RETURN X0;
2027 END CONVERT_AMOUNT;
2028
2029 FUNCTION GET_DERIVE_TYPE(SOB_ID IN NUMBER
2030 ,PERIOD IN VARCHAR2
2031 ,CURR_CODE IN VARCHAR2) RETURN VARCHAR2 IS
2032 X0 VARCHAR2(2000);
2033 BEGIN
2034 /*STPROC.INIT('begin :X0 := GL_CURRENCY_API.GET_DERIVE_TYPE(:SOB_ID, :PERIOD, :CURR_CODE); end;');
2035 STPROC.BIND_O(X0);
2036 STPROC.BIND_I(SOB_ID);
2037 STPROC.BIND_I(PERIOD);
2038 STPROC.BIND_I(CURR_CODE);
2039 STPROC.EXECUTE;
2040 STPROC.RETRIEVE(1
2041 ,X0);*/
2042
2043 X0 := GL_CURRENCY_API.GET_DERIVE_TYPE(SOB_ID, PERIOD, CURR_CODE);
2044 RETURN X0;
2045 END GET_DERIVE_TYPE;
2046
2047 FUNCTION RATE_EXISTS(X_FROM_CURRENCY IN VARCHAR2
2048 ,X_TO_CURRENCY IN VARCHAR2
2049 ,X_CONVERSION_DATE IN DATE
2050 ,X_CONVERSION_TYPE IN VARCHAR2) RETURN VARCHAR2 IS
2051 X0 VARCHAR2(2000);
2052 BEGIN
2053 /*STPROC.INIT('begin :X0 := GL_CURRENCY_API.RATE_EXISTS(:X_FROM_CURRENCY, :X_TO_CURRENCY, :X_CONVERSION_DATE, :X_CONVERSION_TYPE); end;');
2054 STPROC.BIND_O(X0);
2055 STPROC.BIND_I(X_FROM_CURRENCY);
2056 STPROC.BIND_I(X_TO_CURRENCY);
2057 STPROC.BIND_I(X_CONVERSION_DATE);
2058 STPROC.BIND_I(X_CONVERSION_TYPE);
2059 STPROC.EXECUTE;
2060 STPROC.RETRIEVE(1
2061 ,X0);*/
2062
2063 X0 := GL_CURRENCY_API.RATE_EXISTS(X_FROM_CURRENCY, X_TO_CURRENCY, X_CONVERSION_DATE, X_CONVERSION_TYPE);
2064 RETURN X0;
2065 END RATE_EXISTS;
2066
2067 function Item_dspFormula
2068 (
2069 ITEM_IDENTIFIER_TYPE in varchar,
2070 C_MASTER_ORG in varchar,
2071 INVENTORY_ITEM_ID_T in number,
2072 ORDERED_ITEM_ID_T in number,
2073 ORDERED_ITEM in varchar2,
2074 SI_ORGANIZATION_ID in number,
2075 SI_INVENTORY_ITEM_ID in number
2076 )
2077 return Char is
2078 v_item varchar2(2000);
2079 v_description varchar2(500);
2080 begin
2081 if (ITEM_IDENTIFIER_TYPE is null or ITEM_IDENTIFIER_TYPE = 'INT')
2082 or (P_PRINT_DESCRIPTION in ('I','D','F')) then
2083 select sitems.concatenated_segments item,
2084 sitems.description description
2085 into v_item,v_description
2086 from mtl_system_items_vl sitems
2087 where nvl(sitems.organization_id,0) = C_MASTER_ORG
2088 and sitems.inventory_item_id = INVENTORY_ITEM_ID_T;
2089 /* srw.reference (:p_item_flex_code);
2090 srw.reference (:Item_dsp);
2091 srw.reference (:p_item_structure_num);
2092 srw.user_exit (' FND FLEXIDVAL
2093 CODE=":p_item_flex_code"
2094 NUM=":p_item_structure_num"
2095 APPL_SHORT_NAME="INV"
2096 DATA= ":item_flex"
2097 VALUE=":Item_dsp"
2098 DISPLAY="ALL"'
2099 );*/
2100 v_item := fnd_flex_xml_publisher_apis.process_kff_combination_1('Item_dsp', 'INV', p_item_flex_code, p_item_structure_num, SI_ORGANIZATION_ID, SI_INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE');
2101 elsif (ITEM_IDENTIFIER_TYPE = 'CUST' and P_PRINT_DESCRIPTION in ('C','P','O')) then
2102 select citems.customer_item_number item,
2103 nvl(citems.customer_item_desc,sitems.description) description
2104 into v_item,v_description
2105 from mtl_customer_items citems,
2106 mtl_customer_item_xrefs cxref,
2107 mtl_system_items_vl sitems
2108 where citems.customer_item_id = cxref.customer_item_id
2109 and cxref.inventory_item_id = sitems.inventory_item_id
2110 and citems.customer_item_id = ORDERED_ITEM_ID_T
2111 and nvl(sitems.organization_id,0) = C_MASTER_ORG
2112 and sitems.inventory_item_id = INVENTORY_ITEM_ID_T;
2113 elsif (P_PRINT_DESCRIPTION in ('C','P','O')) then
2114 Begin
2115 select items.cross_reference item,
2116 nvl(items.description,sitems.description) description
2117 into v_item,v_description
2118 from mtl_cross_reference_types xtypes,
2119 mtl_cross_references items,
2120 mtl_system_items_vl sitems
2121 where xtypes.cross_reference_type = items.cross_reference_type
2122 and items.inventory_item_id = sitems.inventory_item_id
2123 and items.cross_reference = ORDERED_ITEM
2124 and items.cross_reference_type = ITEM_IDENTIFIER_TYPE
2125 and nvl(sitems.organization_id,0) = C_MASTER_ORG
2126 and sitems.inventory_item_id = INVENTORY_ITEM_ID_T
2127 --Bug 3433353 Begin
2128 and items.org_independent_flag = 'N'
2129 and items.organization_id = C_MASTER_ORG;
2130 Exception When NO_DATA_FOUND Then
2131 select items.cross_reference item,
2132 nvl(items.description,sitems.description) description
2133 into v_item, v_description
2134 from mtl_cross_reference_types xtypes,
2135 mtl_cross_references items,
2136 mtl_system_items_vl sitems
2137 where xtypes.cross_reference_type =
2138 items.cross_reference_type
2139 and items.inventory_item_id = sitems.inventory_item_id
2140 and items.cross_reference = ORDERED_ITEM
2141 and items.cross_reference_type = ITEM_IDENTIFIER_TYPE
2142 and nvl(sitems.organization_id,0) = C_MASTER_ORG
2143 and sitems.inventory_item_id = INVENTORY_ITEM_ID_T
2144 and items.org_independent_flag = 'Y';
2145 End;
2146 -- Bug 3433353 End
2147 end if;
2148
2149 if (P_PRINT_DESCRIPTION in ('I','C')) then
2150 return(v_item||' - '||v_description);
2151 elsif (P_PRINT_DESCRIPTION in ('D','P')) then
2152 return(v_description);
2153 else
2154 return(v_item);
2155 end if;
2156
2157
2158
2159
2160
2161 RETURN NULL;
2162 Exception
2163 When Others Then
2164 return('Item Not Found');
2165 end;
2166
2167
2168 END ONT_OEXOECOD_XMLP_PKG;
2169
2170