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