1 PACKAGE BODY ONT_OEXOEACK_XMLP_PKG AS
2 /* $Header: OEXOEACKB.pls 120.1 2008/05/30 05:44:04 dwkrishn noship $ */
3 FUNCTION AFTERREPORT RETURN BOOLEAN IS
4 BEGIN
5 BEGIN
6 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
7 EXCEPTION
8 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
9 /*SRW.MESSAGE(1
10 ,'Failed in AFTER REPORT TRIGGER')*/NULL;
11 RETURN (FALSE);
12 END;
13 RETURN (TRUE);
14 END AFTERREPORT;
15
16 FUNCTION P_ITEM_FLEX_CODEVALIDTRIGGER RETURN BOOLEAN IS
17 BEGIN
18 RETURN (TRUE);
19 END P_ITEM_FLEX_CODEVALIDTRIGGER;
20
21 FUNCTION AFTERPFORM RETURN BOOLEAN IS
22 BEGIN
23 /*SRW.MESSAGE(99999
24 ,'$Header: OEXOEACKB.pls 120.1 2008/05/30 05:44:04 dwkrishn noship $')*/NULL;
25 BEGIN
26 IF P_BOOKED_STATUS IS NOT NULL THEN
27 IF P_BOOKED_STATUS = 'Y' THEN
28 LP_BOOKED_STATUS := ' and h.booked_flag = ''Y'' ';
29 ELSE
30 LP_BOOKED_STATUS := ' and h.booked_flag = ''N'' ';
31 END IF;
32 ELSE
33 LP_BOOKED_STATUS := ' ';
34 END IF;
35 IF (P_ORDER_DATE_LOW IS NOT NULL) AND (P_ORDER_DATE_HIGH IS NOT NULL) THEN
36 LP_ORDER_DATE := ' and h.ordered_date between :p_order_date_low and (:p_order_date_high+1) ';
37 ELSIF (P_ORDER_DATE_LOW IS NOT NULL) THEN
38 LP_ORDER_DATE := ' and h.ordered_date >= :p_order_date_low ';
39 ELSIF (P_ORDER_DATE_HIGH IS NOT NULL) THEN
40 LP_ORDER_DATE := ' and h.ordered_date <= (:p_order_date_high+1) ';
41 END IF;
42 IF (P_SCHEDULE_DATE_LOW IS NOT NULL) AND (P_SCHEDULE_DATE_HIGH IS NOT NULL) THEN
43 LP_SCHEDULE_DATE := ' and (l.schedule_ship_date between :p_schedule_date_low and (:p_schedule_date_high+1)) ';
44 ELSIF (P_SCHEDULE_DATE_LOW IS NOT NULL) THEN
45 LP_SCHEDULE_DATE := ' and l.schedule_ship_date >= :p_schedule_date_low ';
46 ELSIF (P_SCHEDULE_DATE_HIGH IS NOT NULL) THEN
47 LP_SCHEDULE_DATE := ' and l.schedule_ship_date <= (:p_schedule_date_high+1) ';
48 END IF;
49 IF (P_REQUEST_DATE_LOW IS NOT NULL) AND (P_REQUEST_DATE_HIGH IS NOT NULL) THEN
50 LP_REQUEST_DATE := ' and (l.request_date between :p_request_date_low and (:p_request_date_high+1)) ';
51 ELSIF (P_REQUEST_DATE_LOW IS NOT NULL) THEN
52 LP_REQUEST_DATE := ' and l.request_date >= :p_request_date_low ';
53 ELSIF (P_REQUEST_DATE_HIGH IS NOT NULL) THEN
54 LP_REQUEST_DATE := ' and l.request_date <= (:p_request_date_high+1) ';
55 END IF;
56 IF (P_PROMISE_DATE_LOW IS NOT NULL) AND (P_PROMISE_DATE_HIGH IS NOT NULL) THEN
57 LP_PROMISE_DATE := ' and (l.promise_date between :p_promise_date_low and (:p_promise_date_high+1)) ';
58 ELSIF (P_PROMISE_DATE_LOW IS NOT NULL) THEN
59 LP_PROMISE_DATE := ' and l.promise_date >= :p_promise_date_low ';
60 ELSIF (P_PROMISE_DATE_HIGH IS NOT NULL) THEN
61 LP_PROMISE_DATE := ' and l.promise_date <= (:p_promise_date_high+1) ';
62 END IF;
63 IF (P_SHIP_TO_CUSTOMER_NAME_LO IS NOT NULL) AND (P_SHIP_TO_CUSTOMER_NAME_HI IS NOT NULL) THEN
64 LP_SHIP_TO_CUSTOMER_NAME := ' and (ship_party.party_name between :p_ship_to_customer_name_lo and
65
66 :p_ship_to_customer_name_hi) ';
67 ELSIF (P_SHIP_TO_CUSTOMER_NAME_LO IS NOT NULL) THEN
68 LP_SHIP_TO_CUSTOMER_NAME := ' and ship_party.party_name >= :p_ship_to_customer_name_lo ';
69 ELSIF (P_SHIP_TO_CUSTOMER_NAME_HI IS NOT NULL) THEN
70 LP_SHIP_TO_CUSTOMER_NAME := ' and ship_party.party_name <= :p_ship_to_customer_name_hi ';
71 END IF;
72 IF (P_BILL_TO_CUSTOMER_NAME_LO IS NOT NULL) AND (P_BILL_TO_CUSTOMER_NAME_HI IS NOT NULL) THEN
73 LP_BILL_TO_CUSTOMER_NAME := ' and (bill_party.party_name between :p_bill_to_customer_name_lo and
74
75 :p_bill_to_customer_name_hi) ';
76 ELSIF (P_BILL_TO_CUSTOMER_NAME_LO IS NOT NULL) THEN
77 LP_BILL_TO_CUSTOMER_NAME := ' and bill_party.party_name >= :p_bill_to_customer_name_lo ';
78 ELSIF (P_BILL_TO_CUSTOMER_NAME_HI IS NOT NULL) THEN
79 LP_BILL_TO_CUSTOMER_NAME := ' and bill_party.party_name <= :p_bill_to_customer_name_hi ';
80 END IF;
81 IF (P_DEL_TO_CUSTOMER_NAME_LO IS NOT NULL) AND (P_DEL_TO_CUSTOMER_NAME_HI IS NOT NULL) THEN
82 LP_DEL_TO_CUSTOMER_NAME := ' and (del_party.party_name between :p_del_to_customer_name_lo and
83
84 :p_del_to_customer_name_hi) ';
85 ELSIF (P_DEL_TO_CUSTOMER_NAME_LO IS NOT NULL) THEN
86 LP_DEL_TO_CUSTOMER_NAME := ' and del_party.party_name >= :p_del_to_customer_name_lo ';
87 ELSIF (P_DEL_TO_CUSTOMER_NAME_HI IS NOT NULL) THEN
88 LP_DEL_TO_CUSTOMER_NAME := ' and del_party.party_name <= :p_del_to_customer_name_hi ';
89 END IF;
90 IF (P_ORDER_NUM_LOW IS NOT NULL) AND (P_ORDER_NUM_HIGH IS NOT NULL) THEN
91 IF (P_ORDER_NUM_LOW = P_ORDER_NUM_HIGH) THEN
92 LP_ORDER_NUM := ' and h.order_number = :p_order_num_low ';
93 ELSE
94 LP_ORDER_NUM := ' and (h.order_number between to_number(:p_order_num_low) and to_number(:p_order_num_high)) ';
95 END IF;
96 ELSIF (P_ORDER_NUM_LOW IS NOT NULL) THEN
97 LP_ORDER_NUM := ' and h.order_number >= to_number(:p_order_num_low) ';
98 ELSIF (P_ORDER_NUM_HIGH IS NOT NULL) THEN
99 LP_ORDER_NUM := ' and h.order_number <= to_number(:p_order_num_high) ';
100 END IF;
101 IF P_SALESREP IS NOT NULL THEN
102 LP_SALESREP := ' and sr.name = :p_salesrep ';
103 ELSE
104 LP_SALESREP := ' ';
105 END IF;
106 IF P_CREATED_BY IS NOT NULL THEN
107 LP_CREATED_BY := ' and u.user_name = :p_created_by ';
108 ELSE
109 LP_CREATED_BY := ' ';
110 END IF;
111 IF P_OPEN_ORDERS = 'Y' THEN
112 LP_OPEN_ORDERS := 'and h.open_flag = ''Y''';
113 ELSE
114 LP_OPEN_ORDERS := ' ';
115 END IF;
116 IF P_ORDER_TYPE IS NOT NULL THEN
117 LP_ORDER_TYPE := ' and h.order_type_id = :p_order_type ';
118 ELSE
119 LP_ORDER_TYPE := ' ';
120 END IF;
121 IF P_ORDER_NUM_LOW = P_ORDER_NUM_HIGH THEN
122 NULL;
123 ELSE
124 IF P_ORDER_CATEGORY IS NOT NULL THEN
125 IF P_ORDER_CATEGORY = 'SALES' THEN
126 LP_ORDER_CATEGORY := 'and h.order_category_code in (''ORDER'', ''MIXED'') ';
127 ELSIF P_ORDER_CATEGORY = 'CREDIT' THEN
128 LP_ORDER_CATEGORY := 'and h.order_category_code in (''RETURN'', ''MIXED'') ';
129 ELSIF P_ORDER_CATEGORY = 'ALL' THEN
130 LP_ORDER_CATEGORY := NULL;
131 END IF;
132 ELSE
133 LP_ORDER_CATEGORY := 'and h.order_category_code in (''ORDER'', ''MIXED'') ';
134 END IF;
135 END IF;
136 IF P_LINE_CATEGORY IS NOT NULL THEN
137 IF P_LINE_CATEGORY = 'SALES' THEN
138 LP_LINE_CATEGORY := 'and l.line_category_code = ''ORDER'' ';
139 ELSIF P_LINE_CATEGORY = 'CREDIT' THEN
140 LP_LINE_CATEGORY := 'and l.line_category_code = ''RETURN'' ';
141 ELSIF P_LINE_CATEGORY = 'ALL' THEN
142 LP_LINE_CATEGORY := ' ';
143 END IF;
144 ELSE
145 LP_LINE_CATEGORY := 'and l.line_category_code = ''ORDER'' ';
146 END IF;
147 SELECT
148 USERENV('LANG')
149 INTO P_USER_LANG
150 FROM
151 DUAL;
152 END;
153 RETURN (TRUE);
154 END AFTERPFORM;
155
156 FUNCTION C_LINE_SHIP_TO_CUSTFORMULA(LINE_SHIP_TO_ORG_ID IN NUMBER
157 ,HDR_SHIP_TO_ORG_ID IN NUMBER
158 ,LINE_S_CITY_ST_ZIP IN VARCHAR2) RETURN VARCHAR2 IS
159 BEGIN
160 BEGIN
161 IF LINE_SHIP_TO_ORG_ID IS NULL THEN
162 RETURN (NULL);
163 END IF;
164 IF NVL(HDR_SHIP_TO_ORG_ID
165 ,0) <> NVL(LINE_SHIP_TO_ORG_ID
166 ,0) THEN
167 RETURN (LINE_S_CITY_ST_ZIP);
168 ELSE
169 RETURN (NULL);
170 END IF;
171 END;
172 RETURN NULL;
173 END C_LINE_SHIP_TO_CUSTFORMULA;
174
175 FUNCTION C_DATA_NOT_FOUNDFORMULA(ORDER_NUMBER IN NUMBER) RETURN NUMBER IS
176 BEGIN
177 RP_DATA_FOUND := TO_CHAR(ORDER_NUMBER);
178 RETURN (0);
179 END C_DATA_NOT_FOUNDFORMULA;
180
181 FUNCTION C_EXTENDED_PRICEFORMULA(SELLING_PRICE IN NUMBER
182 ,C_PRECISION IN NUMBER
183 ,PRICING_QUANTITY IN NUMBER
184 ,LINE_CATEGORY_CODE IN VARCHAR2
185 ,ORDERED_QUANTITY IN NUMBER) RETURN NUMBER IS
186 BEGIN
187 /*SRW.REFERENCE(SELLING_PRICE)*/NULL;
188 /*SRW.REFERENCE(C_PRECISION)*/NULL;
189 /*SRW.REFERENCE(PRICING_QUANTITY)*/NULL;
190 IF LINE_CATEGORY_CODE = 'RETURN' THEN
191 RETURN (ROUND(NVL(SELLING_PRICE
192 ,0) * NVL(ORDERED_QUANTITY
193 ,0)
194 ,C_PRECISION) * (-1));
195 ELSE
196 RETURN (ROUND(NVL(SELLING_PRICE
197 ,0) * NVL(ORDERED_QUANTITY
198 ,0)
199 ,C_PRECISION));
200 END IF;
201 END C_EXTENDED_PRICEFORMULA;
202
203 FUNCTION S_TAX_TOTAL_DSPFORMULA RETURN VARCHAR2 IS
204 BEGIN
205 RETURN NULL;
206 END S_TAX_TOTAL_DSPFORMULA;
207
208 FUNCTION C_PRECISIONFORMULA(CURRENCY1 IN VARCHAR2) RETURN NUMBER IS
209 BEGIN
210 DECLARE
211 W_PRECISION NUMBER;
212 BEGIN
213 SELECT
214 PRECISION
215 INTO W_PRECISION
216 FROM
217 FND_CURRENCIES
218 WHERE CURRENCY_CODE = CURRENCY1;
219 RETURN (W_PRECISION);
220 EXCEPTION
221 WHEN NO_DATA_FOUND THEN
222 W_PRECISION := 2;
223 RETURN (W_PRECISION);
224 END;
225 RETURN NULL;
226 END C_PRECISIONFORMULA;
227
228 PROCEDURE POPULATE_MLS_LEXICALS IS
229 SQLSTMT VARCHAR2(1000) := '';
230 SESSION_LANGUAGE VARCHAR2(30) := '';
231 BASE_LANGUAGE VARCHAR2(30) := '';
232 BEGIN
233 SQLSTMT := 'select multi_lingual_flag
234 into :mls_flag
235 from fnd_product_groups
236 where multi_lingual_flag = ''Y''';
237 EXECUTE IMMEDIATE
238 SQLSTMT;
239 IF (MLS_FLAG = 'Y') THEN
240 SELECT
241 SUBSTR(USERENV('LANGUAGE')
242 ,1
243 ,INSTR(USERENV('LANGUAGE')
244 ,'_') - 1)
245 INTO SESSION_LANGUAGE
246 FROM
247 DUAL;
248 SELECT
249 NLS_LANGUAGE
250 INTO BASE_LANGUAGE
251 FROM
252 FND_LANGUAGES
253 WHERE INSTALLED_FLAG = 'B';
254 LP_LANGUAGE_WHERE := 'and nvl(bill_addr.language,' || '''' || BASE_LANGUAGE || ''') = ' || '''' || SESSION_LANGUAGE || '''';
255 SQLSTMT := 'select alax.attribute_column_name
256 into :att_column_name
257 from ak_translated_columns atc,
258 ak_language_attribute_xrefs alax
259 where atc.table_name = ' || '''MTL_UNITS_OF_MEASURE''' || ' and atc.column_name = ' || '''UNIT_OF_MEASURE''' || ' and alax.translated_column_number = atc.translated_column_number
260 and alax.language = ' || '''' || SESSION_LANGUAGE || '''';
261 EXECUTE IMMEDIATE
262 SQLSTMT;
263 IF (ATT_COLUMN_NAME IS NOT NULL) THEN
264 LP_UNIT_OF_MEASURE := 'uom.' || RTRIM(ATT_COLUMN_NAME
265 ,' ');
266 END IF;
267 END IF;
268 EXCEPTION
269 WHEN /*SRW.DO_SQL_FAILURE*/OTHERS THEN
270 /*SRW.MESSAGE(2000
271 ,'Failed in BEFORE REPORT trigger. populate mls lexical')*/NULL;
272 END POPULATE_MLS_LEXICALS;
273
274 FUNCTION C_USE_CURRENCYFORMULA(C_BASE_CURRENCY IN VARCHAR2
275 ,CURRENCY1 IN VARCHAR2) RETURN VARCHAR2 IS
276 BEGIN
277 /*SRW.REFERENCE(C_BASE_CURRENCY)*/NULL;
278 /*SRW.REFERENCE(CURRENCY1)*/NULL;
279 IF P_FUNCTIONAL_CURRENCY = 'Y' THEN
280 RETURN (C_BASE_CURRENCY);
281 ELSIF CURRENCY1 IS NULL THEN
282 RETURN (C_BASE_CURRENCY);
283 ELSE
284 RETURN (CURRENCY1);
285 END IF;
286 RETURN NULL;
287 END C_USE_CURRENCYFORMULA;
288
289 FUNCTION C_BASE_CURRENCYFORMULA RETURN VARCHAR2 IS
290 BEGIN
291 DECLARE
292 BASE_CURRENCY VARCHAR2(30);
293 BEGIN
294 SELECT
295 CURRENCY_CODE
296 INTO BASE_CURRENCY
297 FROM
298 GL_SETS_OF_BOOKS
299 WHERE SET_OF_BOOKS_ID = P_SOB_ID;
300 RETURN (BASE_CURRENCY);
301 EXCEPTION
302 WHEN OTHERS THEN
303 NULL;
304 END;
305 RETURN NULL;
306 END C_BASE_CURRENCYFORMULA;
307
308 FUNCTION C_GL_CONV_RATEFORMULA(CURRENCY1 IN VARCHAR2
309 ,C_BASE_CURRENCY IN VARCHAR2
310 ,CONVERSION_RATE IN NUMBER
311 ,ORDER_DATE IN DATE
312 ,CONVERSION_TYPE_CODE IN VARCHAR2) RETURN NUMBER IS
313 BEGIN
314 DECLARE
315 GL_RATE NUMBER;
316 BEGIN
317 IF P_FUNCTIONAL_CURRENCY = 'Y' THEN
318 IF CURRENCY1 = C_BASE_CURRENCY THEN
319 RETURN (1);
320 ELSE
321 IF CONVERSION_RATE IS NULL THEN
322 GL_RATE := GET_RATE(P_SOB_ID
323 ,CURRENCY1
324 ,TRUNC(ORDER_DATE)
325 ,CONVERSION_TYPE_CODE);
326 RETURN (GL_RATE);
327 ELSE
328 RETURN (CONVERSION_RATE);
329 END IF;
330 END IF;
331 ELSE
332 RETURN (1);
333 END IF;
334 EXCEPTION
335 WHEN NO_DATA_FOUND THEN
336 RETURN (-1);
337 END;
338 RETURN NULL;
339 END C_GL_CONV_RATEFORMULA;
340
341 FUNCTION C_LINE_BILL_TO_CUSTFORMULA(LINE_BILL_TO_ORG_ID IN NUMBER
342 ,HDR_BILL_TO_ORG_ID IN NUMBER
343 ,LINE_B_CITY_ST_ZIP IN VARCHAR2) RETURN CHAR IS
344 BEGIN
345 BEGIN
346 IF LINE_BILL_TO_ORG_ID IS NULL THEN
347 RETURN (NULL);
348 END IF;
349 IF NVL(HDR_BILL_TO_ORG_ID
350 ,0) <> NVL(LINE_BILL_TO_ORG_ID
351 ,0) THEN
352 RETURN (LINE_B_CITY_ST_ZIP);
353 ELSE
354 RETURN (NULL);
355 END IF;
356 END;
357 RETURN NULL;
358 END C_LINE_BILL_TO_CUSTFORMULA;
359
360 FUNCTION C_LINE_DEL_TO_CUSTFORMULA(LINE_DEL_TO_ORG_ID IN NUMBER
361 ,HDR_DEL_TO_ORG_ID IN NUMBER
362 ,LINE_D_CITY_ST_ZIP IN VARCHAR2) RETURN CHAR IS
363 BEGIN
364 BEGIN
365 IF LINE_DEL_TO_ORG_ID IS NULL THEN
366 RETURN (NULL);
367 END IF;
368 IF NVL(HDR_DEL_TO_ORG_ID
369 ,0) <> NVL(LINE_DEL_TO_ORG_ID
370 ,0) THEN
371 RETURN (LINE_D_CITY_ST_ZIP);
372 ELSE
373 RETURN (NULL);
374 END IF;
375 END;
376 RETURN NULL;
377 END C_LINE_DEL_TO_CUSTFORMULA;
378
379 FUNCTION C_LINE_SALESREPFORMULA(LINE_SALESREP IN VARCHAR2
380 ,SALES_PERSON IN VARCHAR2) RETURN CHAR IS
381 BEGIN
382 BEGIN
383 IF LINE_SALESREP IS NULL THEN
384 RETURN (NULL);
385 END IF;
386 IF NVL(SALES_PERSON
387 ,' ') = NVL(LINE_SALESREP
388 ,' ') THEN
389 RETURN (NULL);
390 ELSE
391 RETURN (LINE_SALESREP);
392 END IF;
393 END;
394 RETURN NULL;
395 END C_LINE_SALESREPFORMULA;
396
397 FUNCTION C_LINE_POFORMULA(LINE_PO IN VARCHAR2
398 ,PURCHASE_ORDER IN VARCHAR2) RETURN CHAR IS
399 BEGIN
400 BEGIN
401 IF LINE_PO IS NULL THEN
402 RETURN (NULL);
403 END IF;
404 IF NVL(PURCHASE_ORDER
405 ,' ') = NVL(LINE_PO
406 ,' ') THEN
407 RETURN (NULL);
408 ELSE
409 RETURN (LINE_PO);
410 END IF;
411 END;
412 RETURN NULL;
413 END C_LINE_POFORMULA;
414
415 FUNCTION C_LINE_TYPEFORMULA(LINE_TYPE_ID IN NUMBER
416 ,LINE_TYPE IN VARCHAR2) RETURN CHAR IS
417 BEGIN
418 BEGIN
419 IF LINE_TYPE_ID IS NOT NULL THEN
420 RETURN (LINE_TYPE);
421 ELSE
422 RETURN (NULL);
423 END IF;
424 END;
425 RETURN NULL;
426 END C_LINE_TYPEFORMULA;
427
428 FUNCTION C_LINE_FREIGHT_TERMSFORMULA(LINE_FREIGHT_TERMS IN VARCHAR2
429 ,FREIGHT_TERMS IN VARCHAR2) RETURN CHAR IS
430 BEGIN
431 BEGIN
432 IF LINE_FREIGHT_TERMS IS NULL THEN
433 RETURN (NULL);
434 END IF;
435 IF NVL(FREIGHT_TERMS
436 ,' ') = NVL(LINE_FREIGHT_TERMS
437 ,' ') THEN
438 RETURN (NULL);
439 ELSE
440 RETURN (LINE_FREIGHT_TERMS);
441 END IF;
442 END;
443 RETURN NULL;
444 END C_LINE_FREIGHT_TERMSFORMULA;
445
446 FUNCTION C_LINE_FOBFORMULA(LINE_FOB IN VARCHAR2
447 ,FOB IN VARCHAR2) RETURN CHAR IS
448 BEGIN
449 BEGIN
450 IF LINE_FOB IS NULL THEN
451 RETURN (NULL);
452 END IF;
453 IF NVL(FOB
454 ,' ') = NVL(LINE_FOB
455 ,' ') THEN
456 RETURN (NULL);
457 ELSE
458 RETURN (LINE_FOB);
459 END IF;
460 END;
461 RETURN NULL;
462 END C_LINE_FOBFORMULA;
463
464 FUNCTION C_LINE_CARRIERFORMULA(LINE_CARRIER IN VARCHAR2
465 ,CARRIER IN VARCHAR2) RETURN CHAR IS
466 BEGIN
467 BEGIN
468 IF LINE_CARRIER IS NULL THEN
469 RETURN (NULL);
470 END IF;
471 IF NVL(CARRIER
472 ,' ') <> NVL(LINE_CARRIER
473 ,' ') THEN
474 RETURN (LINE_CARRIER);
475 ELSE
476 RETURN (NULL);
477 END IF;
478 END;
479 RETURN NULL;
480 END C_LINE_CARRIERFORMULA;
481
482 FUNCTION C_FC_EXTEND_PRICEFORMULA(C_GL_CONV_RATE IN NUMBER
483 ,SVC_EXTENDED_PRICE IN NUMBER
484 ,C_PRECISION IN NUMBER) RETURN NUMBER IS
485 BEGIN
486 DECLARE
487 FC_SVC_EXTENDED_PRICE NUMBER;
488 BEGIN
489 IF P_FUNCTIONAL_CURRENCY = 'Y' THEN
490 SELECT
491 C_GL_CONV_RATE * NVL(SVC_EXTENDED_PRICE
492 ,0)
493 INTO FC_SVC_EXTENDED_PRICE
494 FROM
495 DUAL;
496 RETURN (ROUND(NVL(FC_SVC_EXTENDED_PRICE
497 ,0)
498 ,C_PRECISION));
499 ELSE
500 RETURN (ROUND(NVL(SVC_EXTENDED_PRICE
501 ,0)
502 ,C_PRECISION));
503 END IF;
504 END;
505 RETURN NULL;
506 END C_FC_EXTEND_PRICEFORMULA;
507
508 FUNCTION C_FC_SALE_PRICEFORMULA(C_GL_CONV_RATE IN NUMBER
509 ,SVC_SELLING_PRICE IN NUMBER) RETURN NUMBER IS
510 BEGIN
511 DECLARE
512 FC_SVC_SELLING_PRICE NUMBER;
513 BEGIN
514 IF P_FUNCTIONAL_CURRENCY = 'Y' THEN
515 SELECT
516 C_GL_CONV_RATE * SVC_SELLING_PRICE
517 INTO FC_SVC_SELLING_PRICE
518 FROM
519 DUAL;
520 RETURN (FC_SVC_SELLING_PRICE);
521 ELSE
522 RETURN (SVC_SELLING_PRICE);
523 END IF;
524 END;
525 RETURN NULL;
526 END C_FC_SALE_PRICEFORMULA;
527
528 FUNCTION C_LINE_AGREEMENTFORMULA(LINE_AGREEMENT IN VARCHAR2
529 ,AGREEMENT IN VARCHAR2) RETURN CHAR IS
530 BEGIN
531 BEGIN
532 IF LINE_AGREEMENT IS NULL THEN
533 RETURN (NULL);
534 END IF;
535 IF NVL(AGREEMENT
536 ,' ') <> NVL(LINE_AGREEMENT
537 ,' ') THEN
538 RETURN (LINE_AGREEMENT);
539 ELSE
540 RETURN (NULL);
541 END IF;
542 END;
543 RETURN NULL;
544 END C_LINE_AGREEMENTFORMULA;
545
546 FUNCTION C_LINE_TERMSFORMULA(LINE_TERM IN VARCHAR2
547 ,PAYMENT_TERMS IN VARCHAR2) RETURN CHAR IS
548 BEGIN
549 BEGIN
550 IF LINE_TERM IS NULL THEN
551 RETURN (NULL);
552 END IF;
553 IF NVL(PAYMENT_TERMS
554 ,' ') = NVL(LINE_TERM
555 ,' ') THEN
556 RETURN (NULL);
557 ELSE
558 RETURN (LINE_TERM);
559 END IF;
560 END;
561 RETURN NULL;
562 END C_LINE_TERMSFORMULA;
563
564 FUNCTION C_FMT_TAX_ON_LINEFORMULA(LINE_CATEGORY_CODE IN VARCHAR2
565 ,C_TOTAL_LINE_TAX IN NUMBER
566 ,C_PRECISION IN NUMBER) RETURN NUMBER IS
567 BEGIN
568 IF LINE_CATEGORY_CODE = 'RETURN' THEN
569 RETURN (ROUND(NVL(C_TOTAL_LINE_TAX
570 ,0)
571 ,C_PRECISION) * (-1));
572 ELSE
573 RETURN (ROUND(NVL(C_TOTAL_LINE_TAX
574 ,0)
575 ,C_PRECISION));
576 END IF;
577 END C_FMT_TAX_ON_LINEFORMULA;
578
579 FUNCTION C_LINE_CATEGORYFORMULA(LINE_CATEGORY_CODE IN VARCHAR2) RETURN CHAR IS
580 BEGIN
581 BEGIN
582 IF LINE_CATEGORY_CODE IS NOT NULL THEN
583 RETURN (LINE_CATEGORY_CODE);
584 ELSE
585 RETURN (NULL);
586 END IF;
587 END;
588 RETURN NULL;
589 END C_LINE_CATEGORYFORMULA;
590
591 FUNCTION C_LINE_CHARGEFORMULA(LINE_CHARGE IN NUMBER
592 ,C_PRECISION IN NUMBER) RETURN NUMBER IS
593 BEGIN
594 /*SRW.REFERENCE(LINE_CHARGE)*/NULL;
595 /*SRW.REFERENCE(C_PRECISION)*/NULL;
596 RETURN (ROUND(NVL(LINE_CHARGE
597 ,0)
598 ,C_PRECISION));
599 END C_LINE_CHARGEFORMULA;
600
601 FUNCTION C_MASTER_ORGFORMULA RETURN CHAR IS
602 V_MASTER_ORG VARCHAR2(20);
603 BEGIN
604 SELECT
605 NVL(OE_SYS_PARAMETERS.VALUE('MASTER_ORGANIZATION_ID'
606 ,MO_GLOBAL.GET_CURRENT_ORG_ID)
607 ,0)
608 INTO V_MASTER_ORG
609 FROM
610 DUAL;
611 RETURN V_MASTER_ORG;
612 END C_MASTER_ORGFORMULA;
613
614 FUNCTION CF_1FORMULA(S_TAX_TOTAL IN NUMBER
615 ,S_LINE_CHARGE IN NUMBER
616 ,S_EXTENDED_PRICE IN NUMBER
617 ,S_SVC_EXTENDED_PRICE IN NUMBER
618 ,S_HEADER_CHARGE IN NUMBER
619 ,C_PRECISION IN NUMBER) RETURN NUMBER IS
620 BEGIN
621 RETURN (ROUND(NVL(S_TAX_TOTAL
622 ,0) + NVL(S_LINE_CHARGE
623 ,0) + NVL(S_EXTENDED_PRICE
624 ,0) + NVL(S_SVC_EXTENDED_PRICE
625 ,0) + NVL(S_HEADER_CHARGE
626 ,0)
627 ,C_PRECISION));
628 END CF_1FORMULA;
629
630 FUNCTION C_TAX_ON_LINEFORMULA(LINE_CATEGORY_CODE IN VARCHAR2
631 ,C_TOTAL_LINE_TAX IN NUMBER
632 ,C_PRECISION IN NUMBER) RETURN NUMBER IS
633 BEGIN
634 IF LINE_CATEGORY_CODE = 'RETURN' THEN
635 RETURN (ROUND(NVL(C_TOTAL_LINE_TAX
636 ,0)
637 ,C_PRECISION) * (-1));
638 ELSE
639 RETURN (ROUND(NVL(C_TOTAL_LINE_TAX
640 ,0)
641 ,C_PRECISION));
642 END IF;
643 END C_TAX_ON_LINEFORMULA;
644
645 FUNCTION C_ORDERED_QUANTITY(LINE_CATEGORY_CODE IN VARCHAR2
646 ,ORDERED_QUANTITY IN NUMBER) RETURN NUMBER IS
647 BEGIN
648 IF LINE_CATEGORY_CODE = 'RETURN' THEN
649 RETURN ((ORDERED_QUANTITY) * (-1));
650 ELSE
651 RETURN (ORDERED_QUANTITY);
652 END IF;
653 END C_ORDERED_QUANTITY;
654
655 FUNCTION C_TOTAL_LINE_TAXFORMULA(TAX_ON_LINE IN NUMBER
656 ,S_TOTAL_SVC_TAX IN NUMBER) RETURN NUMBER IS
657 BEGIN
658 RETURN (NVL(TAX_ON_LINE
659 ,0) + NVL(S_TOTAL_SVC_TAX
660 ,0));
661 END C_TOTAL_LINE_TAXFORMULA;
662
663 FUNCTION C_SVC_TAXFORMULA(TAX_ON_SVC_LINE IN NUMBER
664 ,C_PRECISION IN NUMBER) RETURN NUMBER IS
665 BEGIN
666 RETURN (ROUND(NVL(TAX_ON_SVC_LINE
667 ,0)
668 ,C_PRECISION));
669 END C_SVC_TAXFORMULA;
670
671 FUNCTION C_CHARGE_PERIODICITYFORMULA(CHARGE_PERIODICITY_CODE IN VARCHAR2) RETURN CHAR IS
672 L_PERIODICITY VARCHAR2(25);
673 BEGIN
674 IF CHARGE_PERIODICITY_CODE IS NOT NULL THEN
675 SELECT
676 UNIT_OF_MEASURE
677 INTO L_PERIODICITY
678 FROM
679 MTL_UNITS_OF_MEASURE_VL
680 WHERE UOM_CODE = CHARGE_PERIODICITY_CODE
681 AND UOM_CLASS = FND_PROFILE.VALUE('ONT_UOM_CLASS_CHARGE_PERIODICITY');
682 RETURN (L_PERIODICITY);
683 ELSE
684 RETURN (P_CHARGE_PERIODICITY);
685 END IF;
686 RETURN NULL;
687 EXCEPTION
688 WHEN NO_DATA_FOUND THEN
689 RETURN NULL;
690 END C_CHARGE_PERIODICITYFORMULA;
691
692 FUNCTION G_GRAND_TOTALFORMULA(CHARGE_PERIODICITY_CODE IN VARCHAR2
693 ,C_TAX_TOTAL IN NUMBER
694 ,C_LINE_RECUR_CHARGE IN NUMBER
695 ,CF_EXTENDED_PRICE IN NUMBER
696 ,C_SVC_EXTENDED_PRICE IN NUMBER
697 ,C_HEADER_CHARGE_PERIODICITY IN NUMBER
698 ,C_PRECISION IN NUMBER) RETURN NUMBER IS
699 BEGIN
700 IF CHARGE_PERIODICITY_CODE IS NULL THEN
701 RETURN (ROUND(NVL(C_TAX_TOTAL
702 ,0) + NVL(C_LINE_RECUR_CHARGE
703 ,0) + NVL(CF_EXTENDED_PRICE
704 ,0) + NVL(C_SVC_EXTENDED_PRICE
705 ,0) + NVL(C_HEADER_CHARGE_PERIODICITY
706 ,0)
707 ,C_PRECISION));
708 ELSE
709 RETURN (ROUND(NVL(C_TAX_TOTAL
710 ,0) + NVL(C_LINE_RECUR_CHARGE
711 ,0) + NVL(CF_EXTENDED_PRICE
712 ,0) + NVL(C_SVC_EXTENDED_PRICE
713 ,0)
714 ,C_PRECISION));
715 END IF;
716 END G_GRAND_TOTALFORMULA;
717
718 FUNCTION G_PRECISIONFORMULA(CURRENCY1 IN VARCHAR2) RETURN NUMBER IS
719 BEGIN
720 DECLARE
721 W_PRECISION NUMBER;
722 BEGIN
723 SELECT
724 PRECISION
725 INTO W_PRECISION
726 FROM
727 FND_CURRENCIES
728 WHERE CURRENCY_CODE = CURRENCY1;
729 RETURN (W_PRECISION);
730 EXCEPTION
731 WHEN NO_DATA_FOUND THEN
732 W_PRECISION := 2;
733 RETURN (W_PRECISION);
734 END;
735 RETURN NULL;
736 END G_PRECISIONFORMULA;
737
738 FUNCTION C_HEADER_CHARGE_PERIODICITYFOR(CHARGE_PERIODICITY_CODE IN VARCHAR2
739 ,C_HEADER_CHARGE IN NUMBER
740 ,C_PRECISION IN NUMBER) RETURN NUMBER IS
741 BEGIN
742 IF CHARGE_PERIODICITY_CODE IS NULL THEN
743 RETURN (ROUND(NVL(C_HEADER_CHARGE
744 ,0)
745 ,C_PRECISION));
746 ELSE
747 RETURN (ROUND(0.00
748 ,C_PRECISION));
749 END IF;
750 END C_HEADER_CHARGE_PERIODICITYFOR;
751
752 FUNCTION C_HEADER_CHARGEFORMULA(HEADER_ID IN NUMBER) RETURN NUMBER IS
753 L_HEADER_CHARGE NUMBER;
754 BEGIN
755 SELECT
756 SUM(NVL(CHARGE_AMOUNT
757 ,0))
758 INTO L_HEADER_CHARGE
759 FROM
760 OE_CHARGE_LINES_V
761 WHERE HEADER_ID =C_HEADER_CHARGEFORMULA.HEADER_ID
762 AND LINE_ID is null;
763 RETURN (L_HEADER_CHARGE);
764 EXCEPTION
765 WHEN NO_DATA_FOUND THEN
766 RETURN (0);
767 WHEN OTHERS THEN
768 RETURN (0);
769 END C_HEADER_CHARGEFORMULA;
770
771 FUNCTION C_ACCEPT_REQUIREDFORMULA(CONTINGENCY_ID IN NUMBER) RETURN CHAR IS
772 L_RETURN_VALUE VARCHAR(10);
773 BEGIN
774 IF CONTINGENCY_ID IS NULL THEN
775 SELECT
776 MEANING
777 INTO L_RETURN_VALUE
778 FROM
779 OE_LOOKUPS
780 WHERE LOOKUP_TYPE = 'OM_YES_NO'
781 AND LOOKUP_CODE = 'NO';
782 ELSE
783 SELECT
784 MEANING
785 INTO L_RETURN_VALUE
786 FROM
787 OE_LOOKUPS
788 WHERE LOOKUP_TYPE = 'OM_YES_NO'
789 AND LOOKUP_CODE = 'YES';
790 END IF;
791 RETURN L_RETURN_VALUE;
792 END C_ACCEPT_REQUIREDFORMULA;
793
794 FUNCTION C_BILL_CONTACTFORMULA(INVOICE_TO_CONTACT_ID IN NUMBER) RETURN CHAR IS
795 L_BILL_CONTACT VARCHAR2(300);
796 BEGIN
797 SELECT
798 SUBSTRB(BILL_CT_PARTY.PERSON_FIRST_NAME
799 ,1
800 ,40) || ' ' || SUBSTRB(BILL_CT_PARTY.PERSON_LAST_NAME
801 ,1
802 ,50)
803 INTO L_BILL_CONTACT
804 FROM
805 HZ_CUST_ACCOUNT_ROLES BILL_CT_ACCT_ROLE,
806 HZ_PARTIES BILL_CT_PARTY,
807 HZ_RELATIONSHIPS BILL_CT_REL,
808 HZ_ORG_CONTACTS BILL_CT_ORG_CONT,
809 HZ_PARTIES BILL_CT_REL_PARTY,
810 HZ_CUST_ACCOUNTS BILL_CT_ROLE_ACCT
811 WHERE BILL_CT_ACCT_ROLE.CUST_ACCOUNT_ROLE_ID = INVOICE_TO_CONTACT_ID
812 AND BILL_CT_ACCT_ROLE.PARTY_ID = BILL_CT_REL.PARTY_ID
813 AND BILL_CT_ACCT_ROLE.ROLE_TYPE = 'CONTACT'
814 AND BILL_CT_REL.RELATIONSHIP_ID = BILL_CT_ORG_CONT.PARTY_RELATIONSHIP_ID
815 AND BILL_CT_REL.SUBJECT_ID = BILL_CT_PARTY.PARTY_ID
816 AND BILL_CT_REL.PARTY_ID = BILL_CT_REL_PARTY.PARTY_ID
817 AND BILL_CT_REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
818 AND BILL_CT_REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
819 AND BILL_CT_ACCT_ROLE.CUST_ACCOUNT_ID = BILL_CT_ROLE_ACCT.CUST_ACCOUNT_ID
820 AND BILL_CT_ROLE_ACCT.PARTY_ID = BILL_CT_REL.OBJECT_ID;
821 RETURN (L_BILL_CONTACT);
822 EXCEPTION
823 WHEN OTHERS THEN
824 RETURN NULL;
825 END C_BILL_CONTACTFORMULA;
826
827 FUNCTION C_SHIP_CONTACTFORMULA(SHIP_TO_CONTACT_ID IN NUMBER) RETURN CHAR IS
828 L_SHIP_CONTACT VARCHAR2(300);
829 BEGIN
830 SELECT
831 SUBSTRB(SHIP_CT_PARTY.PERSON_FIRST_NAME
832 ,1
833 ,40) || ' ' || SUBSTRB(SHIP_CT_PARTY.PERSON_LAST_NAME
834 ,1
835 ,50)
836 INTO L_SHIP_CONTACT
837 FROM
838 HZ_CUST_ACCOUNT_ROLES SHIP_CT_ACCT_ROLE,
839 HZ_PARTIES SHIP_CT_PARTY,
840 HZ_RELATIONSHIPS SHIP_CT_REL,
841 HZ_ORG_CONTACTS SHIP_CT_ORG_CONT,
842 HZ_PARTIES SHIP_CT_REL_PARTY,
843 HZ_CUST_ACCOUNTS SHIP_CT_ROLE_ACCT
844 WHERE SHIP_CT_ACCT_ROLE.CUST_ACCOUNT_ROLE_ID = SHIP_TO_CONTACT_ID
845 AND SHIP_CT_ACCT_ROLE.PARTY_ID = SHIP_CT_REL.PARTY_ID
846 AND SHIP_CT_ACCT_ROLE.ROLE_TYPE = 'CONTACT'
847 AND SHIP_CT_REL.RELATIONSHIP_ID = SHIP_CT_ORG_CONT.PARTY_RELATIONSHIP_ID
848 AND SHIP_CT_REL.SUBJECT_ID = SHIP_CT_PARTY.PARTY_ID
849 AND SHIP_CT_REL.PARTY_ID = SHIP_CT_REL_PARTY.PARTY_ID
850 AND SHIP_CT_REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
851 AND SHIP_CT_REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
852 AND SHIP_CT_ACCT_ROLE.CUST_ACCOUNT_ID = SHIP_CT_ROLE_ACCT.CUST_ACCOUNT_ID
853 AND SHIP_CT_ROLE_ACCT.PARTY_ID = SHIP_CT_REL.OBJECT_ID;
854 RETURN (L_SHIP_CONTACT);
855 EXCEPTION
856 WHEN OTHERS THEN
857 RETURN NULL;
858 END C_SHIP_CONTACTFORMULA;
859
860 FUNCTION C_DEL_CONTACTFORMULA(DELIVER_TO_CONTACT_ID IN NUMBER) RETURN CHAR IS
861 L_DEL_CONTACT VARCHAR2(300);
862 BEGIN
863 SELECT
864 SUBSTRB(DEL_CT_PARTY.PERSON_FIRST_NAME
865 ,1
866 ,40) || ' ' || SUBSTRB(DEL_CT_PARTY.PERSON_LAST_NAME
867 ,1
868 ,50)
869 INTO L_DEL_CONTACT
870 FROM
871 HZ_CUST_ACCOUNT_ROLES DEL_CT_ACCT_ROLE,
872 HZ_PARTIES DEL_CT_PARTY,
873 HZ_RELATIONSHIPS DEL_CT_REL,
874 HZ_ORG_CONTACTS DEL_CT_ORG_CONT,
875 HZ_PARTIES DEL_CT_REL_PARTY,
876 HZ_CUST_ACCOUNTS DEL_CT_ROLE_ACCT
877 WHERE DEL_CT_ACCT_ROLE.CUST_ACCOUNT_ROLE_ID = DELIVER_TO_CONTACT_ID
878 AND DEL_CT_ACCT_ROLE.PARTY_ID = DEL_CT_REL.PARTY_ID
879 AND DEL_CT_ACCT_ROLE.ROLE_TYPE = 'CONTACT'
880 AND DEL_CT_REL.RELATIONSHIP_ID = DEL_CT_ORG_CONT.PARTY_RELATIONSHIP_ID
881 AND DEL_CT_REL.SUBJECT_ID = DEL_CT_PARTY.PARTY_ID
882 AND DEL_CT_REL.PARTY_ID = DEL_CT_REL_PARTY.PARTY_ID
883 AND DEL_CT_REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
884 AND DEL_CT_REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
885 AND DEL_CT_ACCT_ROLE.CUST_ACCOUNT_ID = DEL_CT_ROLE_ACCT.CUST_ACCOUNT_ID
886 AND DEL_CT_ROLE_ACCT.PARTY_ID = DEL_CT_REL.OBJECT_ID;
887 RETURN (L_DEL_CONTACT);
888 EXCEPTION
889 WHEN OTHERS THEN
890 RETURN NULL;
891 END C_DEL_CONTACTFORMULA;
892
893 FUNCTION RP_DUMMY_ITEM_P RETURN VARCHAR2 IS
894 BEGIN
895 RETURN RP_DUMMY_ITEM;
896 END RP_DUMMY_ITEM_P;
897
898 FUNCTION RP_REPORT_NAME_P RETURN VARCHAR2 IS
899 BEGIN
900 RETURN RP_REPORT_NAME;
901 END RP_REPORT_NAME_P;
902
903 FUNCTION RP_SUB_TITLE_P RETURN VARCHAR2 IS
904 BEGIN
905 RETURN RP_SUB_TITLE;
906 END RP_SUB_TITLE_P;
907
908 FUNCTION RP_COMPANY_NAME_P RETURN VARCHAR2 IS
909 BEGIN
910 RETURN RP_COMPANY_NAME;
911 END RP_COMPANY_NAME_P;
912
913 FUNCTION RP_FUNCTIONAL_CURRENCY_P RETURN VARCHAR2 IS
914 BEGIN
915 RETURN RP_FUNCTIONAL_CURRENCY;
916 END RP_FUNCTIONAL_CURRENCY_P;
917
918 FUNCTION RP_DATA_FOUND_P RETURN VARCHAR2 IS
919 BEGIN
920 RETURN RP_DATA_FOUND;
921 END RP_DATA_FOUND_P;
922
923 FUNCTION RP_ITEM_FLEX_ALL_SEG_P RETURN VARCHAR2 IS
924 BEGIN
925 RETURN RP_ITEM_FLEX_ALL_SEG;
926 END RP_ITEM_FLEX_ALL_SEG_P;
927
928 FUNCTION RP_PRINT_DESCRIPTION_P RETURN VARCHAR2 IS
929 BEGIN
930 RETURN RP_PRINT_DESCRIPTION;
931 END RP_PRINT_DESCRIPTION_P;
932
933 FUNCTION RP_CURR_PROFILE_P RETURN VARCHAR2 IS
934 BEGIN
935 RETURN RP_CURR_PROFILE;
936 END RP_CURR_PROFILE_P;
937
938 FUNCTION RP_ITEM_FLEX_SEG_VAL_P RETURN VARCHAR2 IS
939 BEGIN
940 RETURN RP_ITEM_FLEX_SEG_VAL;
941 END RP_ITEM_FLEX_SEG_VAL_P;
942
943 FUNCTION RP_TAX_TOTAL_ROUNDED_P RETURN NUMBER IS
944 BEGIN
945 RETURN RP_TAX_TOTAL_ROUNDED;
946 END RP_TAX_TOTAL_ROUNDED_P;
947
948 FUNCTION RP_LINE_CHARGE_TOTAL_ROUNDED_P RETURN NUMBER IS
949 BEGIN
950 RETURN RP_LINE_CHARGE_TOTAL_ROUNDED;
951 END RP_LINE_CHARGE_TOTAL_ROUNDED_P;
952
953 FUNCTION RP_HDR_CHARGE_TOTAL_ROUNDED_P RETURN NUMBER IS
954 BEGIN
955 RETURN RP_HDR_CHARGE_TOTAL_ROUNDED;
956 END RP_HDR_CHARGE_TOTAL_ROUNDED_P;
957
958 FUNCTION RP_HDR_CHARGE_TOTAL_P RETURN NUMBER IS
959 BEGIN
960 RETURN RP_HDR_CHARGE_TOTAL;
961 END RP_HDR_CHARGE_TOTAL_P;
962
963 FUNCTION IS_FIXED_RATE(X_FROM_CURRENCY IN VARCHAR2
964 ,X_TO_CURRENCY IN VARCHAR2
965 ,X_EFFECTIVE_DATE IN DATE) RETURN VARCHAR2 IS
966 X0 VARCHAR2(2000);
967 BEGIN
968 /*STPROC.INIT('begin :X0 := GL_CURRENCY_API.IS_FIXED_RATE(:X_FROM_CURRENCY, :X_TO_CURRENCY, :X_EFFECTIVE_DATE); end;');
969 STPROC.BIND_O(X0);
970 STPROC.BIND_I(X_FROM_CURRENCY);
971 STPROC.BIND_I(X_TO_CURRENCY);
972 STPROC.BIND_I(X_EFFECTIVE_DATE);
973 STPROC.EXECUTE;
974 STPROC.RETRIEVE(1
975 ,X0);*/
976 X0 := GL_CURRENCY_API.IS_FIXED_RATE(X_FROM_CURRENCY, X_TO_CURRENCY, X_EFFECTIVE_DATE);
977 RETURN X0;
978 END IS_FIXED_RATE;
979
980 PROCEDURE GET_RELATION(X_FROM_CURRENCY IN VARCHAR2
981 ,X_TO_CURRENCY IN VARCHAR2
982 ,X_EFFECTIVE_DATE IN DATE
983 ,X_FIXED_RATE IN OUT NOCOPY BOOLEAN
984 ,X_RELATIONSHIP IN OUT NOCOPY VARCHAR2) IS
985 BEGIN
986 /* STPROC.INIT('declare X_FIXED_RATE BOOLEAN;
987 begin X_FIXED_RATE := sys.diutil.int_to_bool(:X_FIXED_RATE);
988 GL_CURRENCY_API.GET_RELATION(:X_FROM_CURRENCY, :X_TO_CURRENCY,
989 :X_EFFECTIVE_DATE, X_FIXED_RATE, :X_RELATIONSHIP);
990 :X_FIXED_RATE := sys.diutil.bool_to_int(X_FIXED_RATE); end;');
991 STPROC.BIND_IO(X_FIXED_RATE);
992 STPROC.BIND_I(X_FROM_CURRENCY);
993 STPROC.BIND_I(X_TO_CURRENCY);
994 STPROC.BIND_I(X_EFFECTIVE_DATE);
995 STPROC.BIND_IO(X_RELATIONSHIP);
996 STPROC.EXECUTE;
997 STPROC.RETRIEVE(1
998 ,X_FIXED_RATE);
999 STPROC.RETRIEVE(5
1000 ,X_RELATIONSHIP);*/
1001 --X_FIXED_RATE := sys.diutil.int_to_bool(X_FIXED_RATE);
1002 GL_CURRENCY_API.GET_RELATION(X_FROM_CURRENCY, X_TO_CURRENCY, X_EFFECTIVE_DATE, X_FIXED_RATE, X_RELATIONSHIP);
1003 --X_FIXED_RATE := sys.diutil.bool_to_int(X_FIXED_RATE);
1004 END GET_RELATION;
1005
1006 FUNCTION GET_EURO_CODE RETURN VARCHAR2 IS
1007 X0 VARCHAR2(2000);
1008 BEGIN
1009 /*STPROC.INIT('begin :X0 := GL_CURRENCY_API.GET_EURO_CODE; end;');
1010 STPROC.BIND_O(X0);
1011 STPROC.EXECUTE;
1012 STPROC.RETRIEVE(1
1013 ,X0);*/
1014 X0 := GL_CURRENCY_API.GET_EURO_CODE;
1015 RETURN X0;
1016 END GET_EURO_CODE;
1017
1018 FUNCTION GET_RATE(X_FROM_CURRENCY IN VARCHAR2
1019 ,X_TO_CURRENCY IN VARCHAR2
1020 ,X_CONVERSION_DATE IN DATE
1021 ,X_CONVERSION_TYPE IN VARCHAR2) RETURN NUMBER IS
1022 X0 NUMBER;
1023 BEGIN
1024 /*STPROC.INIT('begin :X0 := GL_CURRENCY_API.GET_RATE(:X_FROM_CURRENCY, :X_TO_CURRENCY, :X_CONVERSION_DATE, :X_CONVERSION_TYPE); end;');
1025 STPROC.BIND_O(X0);
1026 STPROC.BIND_I(X_FROM_CURRENCY);
1027 STPROC.BIND_I(X_TO_CURRENCY);
1028 STPROC.BIND_I(X_CONVERSION_DATE);
1029 STPROC.BIND_I(X_CONVERSION_TYPE);
1030 STPROC.EXECUTE;
1031 STPROC.RETRIEVE(1
1032 ,X0);*/
1033 X0 := GL_CURRENCY_API.GET_RATE(X_FROM_CURRENCY, X_TO_CURRENCY, X_CONVERSION_DATE, X_CONVERSION_TYPE);
1034 RETURN X0;
1035 END GET_RATE;
1036
1037 FUNCTION GET_RATE(X_SET_OF_BOOKS_ID IN NUMBER
1038 ,X_FROM_CURRENCY IN VARCHAR2
1039 ,X_CONVERSION_DATE IN DATE
1040 ,X_CONVERSION_TYPE IN VARCHAR2) RETURN NUMBER IS
1041 X0 NUMBER;
1042 BEGIN
1043 /*STPROC.INIT('begin :X0 := GL_CURRENCY_API.GET_RATE(:X_SET_OF_BOOKS_ID, :X_FROM_CURRENCY, :X_CONVERSION_DATE, :X_CONVERSION_TYPE); end;');
1044 STPROC.BIND_O(X0);
1045 STPROC.BIND_I(X_SET_OF_BOOKS_ID);
1046 STPROC.BIND_I(X_FROM_CURRENCY);
1047 STPROC.BIND_I(X_CONVERSION_DATE);
1048 STPROC.BIND_I(X_CONVERSION_TYPE);
1049 STPROC.EXECUTE;
1050 STPROC.RETRIEVE(1
1051 ,X0);*/
1052 X0 := GL_CURRENCY_API.GET_RATE(X_SET_OF_BOOKS_ID, X_FROM_CURRENCY, X_CONVERSION_DATE, X_CONVERSION_TYPE);
1053 RETURN X0;
1054 END GET_RATE;
1055
1056 FUNCTION CONVERT_AMOUNT(X_FROM_CURRENCY IN VARCHAR2
1057 ,X_TO_CURRENCY IN VARCHAR2
1058 ,X_CONVERSION_DATE IN DATE
1059 ,X_CONVERSION_TYPE IN VARCHAR2
1060 ,X_AMOUNT IN NUMBER) RETURN NUMBER IS
1061 X0 NUMBER;
1062 BEGIN
1063 /*STPROC.INIT('begin :X0 := GL_CURRENCY_API.CONVERT_AMOUNT(:X_FROM_CURRENCY, :X_TO_CURRENCY, :X_CONVERSION_DATE, :X_CONVERSION_TYPE, :X_AMOUNT); end;');
1064 STPROC.BIND_O(X0);
1065 STPROC.BIND_I(X_FROM_CURRENCY);
1066 STPROC.BIND_I(X_TO_CURRENCY);
1067 STPROC.BIND_I(X_CONVERSION_DATE);
1068 STPROC.BIND_I(X_CONVERSION_TYPE);
1069 STPROC.BIND_I(X_AMOUNT);
1070 STPROC.EXECUTE;
1071 STPROC.RETRIEVE(1
1072 ,X0);*/
1073 X0 := GL_CURRENCY_API.CONVERT_AMOUNT(X_FROM_CURRENCY, X_TO_CURRENCY, X_CONVERSION_DATE, X_CONVERSION_TYPE, X_AMOUNT);
1074 RETURN X0;
1075 END CONVERT_AMOUNT;
1076
1077 FUNCTION CONVERT_AMOUNT(X_SET_OF_BOOKS_ID IN NUMBER
1078 ,X_FROM_CURRENCY IN VARCHAR2
1079 ,X_CONVERSION_DATE IN DATE
1080 ,X_CONVERSION_TYPE IN VARCHAR2
1081 ,X_AMOUNT IN NUMBER) RETURN NUMBER IS
1082 X0 NUMBER;
1083 BEGIN
1084 /*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;');
1085 STPROC.BIND_O(X0);
1086 STPROC.BIND_I(X_SET_OF_BOOKS_ID);
1087 STPROC.BIND_I(X_FROM_CURRENCY);
1088 STPROC.BIND_I(X_CONVERSION_DATE);
1089 STPROC.BIND_I(X_CONVERSION_TYPE);
1090 STPROC.BIND_I(X_AMOUNT);
1091 STPROC.EXECUTE;
1092 STPROC.RETRIEVE(1
1093 ,X0);*/
1094 X0 := GL_CURRENCY_API.CONVERT_AMOUNT(X_SET_OF_BOOKS_ID, X_FROM_CURRENCY, X_CONVERSION_DATE, X_CONVERSION_TYPE, X_AMOUNT);
1095 RETURN X0;
1096 END CONVERT_AMOUNT;
1097
1098 FUNCTION GET_DERIVE_TYPE(SOB_ID IN NUMBER
1099 ,PERIOD IN VARCHAR2
1100 ,CURR_CODE IN VARCHAR2) RETURN VARCHAR2 IS
1101 X0 VARCHAR2(2000);
1102 BEGIN
1103 /*STPROC.INIT('begin :X0 := GL_CURRENCY_API.GET_DERIVE_TYPE(:SOB_ID, :PERIOD, :CURR_CODE); end;');
1104 STPROC.BIND_O(X0);
1105 STPROC.BIND_I(SOB_ID);
1106 STPROC.BIND_I(PERIOD);
1107 STPROC.BIND_I(CURR_CODE);
1108 STPROC.EXECUTE;
1109 STPROC.RETRIEVE(1
1110 ,X0);*/
1111 X0 := GL_CURRENCY_API.GET_DERIVE_TYPE(SOB_ID, PERIOD, CURR_CODE);
1112 RETURN X0;
1113 END GET_DERIVE_TYPE;
1114
1115 FUNCTION RATE_EXISTS(X_FROM_CURRENCY IN VARCHAR2
1116 ,X_TO_CURRENCY IN VARCHAR2
1117 ,X_CONVERSION_DATE IN DATE
1118 ,X_CONVERSION_TYPE IN VARCHAR2) RETURN VARCHAR2 IS
1119 X0 VARCHAR2(2000);
1120 BEGIN
1121 /*STPROC.INIT('begin :X0 := GL_CURRENCY_API.RATE_EXISTS(:X_FROM_CURRENCY, :X_TO_CURRENCY, :X_CONVERSION_DATE, :X_CONVERSION_TYPE); end;');
1122 STPROC.BIND_O(X0);
1123 STPROC.BIND_I(X_FROM_CURRENCY);
1124 STPROC.BIND_I(X_TO_CURRENCY);
1125 STPROC.BIND_I(X_CONVERSION_DATE);
1126 STPROC.BIND_I(X_CONVERSION_TYPE);
1127 STPROC.EXECUTE;
1128 STPROC.RETRIEVE(1
1129 ,X0);*/
1130 X0 := GL_CURRENCY_API.RATE_EXISTS(X_FROM_CURRENCY, X_TO_CURRENCY, X_CONVERSION_DATE, X_CONVERSION_TYPE);
1131 RETURN X0;
1132 END RATE_EXISTS;
1133
1134 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
1135 BEGIN
1136
1137 DECLARE
1138 BEGIN
1139
1140 /* BEGIN
1141 SRW.USER_EXIT('FND SRWINIT');
1142 EXCEPTION
1143 WHEN SRW.USER_EXIT_FAILURE THEN
1144 SRW.MESSAGE (1000,'Failed in BEFORE REPORT trigger - SRWINIT USER EXIT');
1145 return (FALSE);
1146 END;*/
1147
1148 BEGIN /*MOAC*/
1149
1150 P_ORG_ID:= MO_GLOBAL.GET_CURRENT_ORG_ID();
1151 END;
1152
1153
1154
1155 /*------------------------------------------------------------------------------
1156 Following PL/SQL block gets the company name, functional currency and precision.
1157 ------------------------------------------------------------------------------*/
1158
1159
1160 DECLARE
1161 l_company_name VARCHAR2 (100);
1162 l_functional_currency VARCHAR2 (15);
1163
1164 BEGIN
1165
1166 SELECT sob.name ,
1167 sob.currency_code
1168 INTO
1169 l_company_name ,
1170 l_functional_currency
1171 FROM gl_sets_of_books sob,
1172 fnd_currencies cur
1173 WHERE sob.set_of_books_id = p_sob_id
1174 AND sob.currency_code = cur.currency_code
1175 ;
1176
1177 rp_company_name := l_company_name;
1178 rp_functional_currency := l_functional_currency ;
1179
1180
1181 EXCEPTION
1182 WHEN NO_DATA_FOUND THEN
1183 /*srw.message(2000,'Failed in BEFORE REPORT trigger. get company, currency');*/null;
1184 when OTHERS then
1185 /*srw.message(2000,'Failed in BEFORE REPORT trigger. get company, currency'); */null;
1186 END ;
1187
1188 /*------------------------------------------------------------------------------
1189 Following PL/SQL block gets the report name for the passed concurrent request Id.
1190 ------------------------------------------------------------------------------*/
1191 DECLARE
1192 l_report_name VARCHAR2(240);
1193 BEGIN
1194 SELECT cp.user_concurrent_program_name
1195 INTO l_report_name
1196 FROM FND_CONCURRENT_PROGRAMS_VL cp,
1197 FND_CONCURRENT_REQUESTS cr
1198 WHERE cr.request_id = P_CONC_REQUEST_ID
1199 AND cp.application_id = cr.program_application_id
1200 AND cp.concurrent_program_id = cr.concurrent_program_id
1201 ;
1202
1203 RP_Report_Name := l_report_name;
1204 EXCEPTION
1205 WHEN NO_DATA_FOUND
1206 THEN RP_REPORT_NAME := 'Sales Order Acknowledgement';
1207 when OTHERS then
1208 /*srw.message(2000,'Failed in BEFORE REPORT trigger. get company, currency'); */null;
1209 END;
1210
1211 /*------------------------------------------------------------------------------
1212 The following block retrieves the profile option value for standard vs
1213 extended currency
1214 -------------------------------------------------------------------------------*/
1215
1216 /* BEGIN
1217 SRW.REFERENCE(:p_profile_name);
1218 SRW.USER_EXIT('FND GETPROFILE NAME=":p_profile_name"
1219 FIELD=":RP_CURR_PROFILE"
1220 PRINT_ERROR="N"
1221 ');
1222
1223
1224 EXCEPTION
1225 WHEN SRW.USER_EXIT_FAILURE THEN
1226 srw.message(2000,'Failed in BEFORE REPORT trigger. FND GETPROFILE - CURR USER_EXIT');
1227 END; */
1228
1229
1230
1231 /*------------------------------------------------------------------------------
1232 Following PL/SQL block builds up the lexical parameters, to be used in the
1233 WHERE clause of the query. This also populates the report level variables, used
1234 to store the flexfield structure.
1235 ------------------------------------------------------------------------------*/
1236 /*BEGIN
1237 SRW.REFERENCE(:P_item_flex_code);
1238 SRW.REFERENCE(:P_item_structure_num);
1239
1240
1241 SRW.USER_EXIT('FND FLEXSQL CODE=":p_item_flex_code"
1242 NUM=":p_item_structure_num"
1243 APPL_SHORT_NAME="INV"
1244 OUTPUT=":rp_item_flex_all_seg"
1245 MODE="SELECT"
1246 DISPLAY="ALL"
1247 TABLEALIAS="SI"
1248 ');
1249
1250 EXCEPTION
1251 WHEN SRW.USER_EXIT_FAILURE THEN
1252 srw.message(2000,'Failed in BEFORE REPORT trigger. FND FLEXSQL USER_EXIT');
1253 END; */
1254
1255
1256
1257 DECLARE
1258 l_meaning VARCHAR2 (80);
1259 BEGIN
1260 SELECT MEANING
1261 INTO l_meaning
1262 FROM OE_LOOKUPS
1263 WHERE LOOKUP_TYPE = 'ITEM_DISPLAY_CODE'
1264 AND LOOKUP_CODE = substr(upper(p_print_description),1,1)
1265 ;
1266
1267 rp_print_description := l_meaning ;
1268 EXCEPTION WHEN NO_DATA_FOUND THEN
1269 rp_print_description := 'Internal Item Description';
1270 when OTHERS then
1271 /*srw.message(2000,'Failed in BEFORE REPORT trigger. Get Print Description'); */null;
1272
1273 END ;
1274
1275
1276
1277 /* ----- The follwoing PL/SQL block populates the multi-lingual
1278 lexical parameters----- */
1279 BEGIN
1280 populate_mls_lexicals;
1281 END;
1282
1283 END ;
1284 RETURN (TRUE);
1285 END;
1286
1287 function F_PeriodicityFormatTrigger return VARCHAR2 is
1288 begin
1289 if OE_SYS_PARAMETERS.Value('RECURRING_CHARGES',mo_global.get_current_org_id()) = 'Y' THEN
1290 return('TRUE');
1291 else
1292 return ('FALSE');
1293 end if;
1294 return ('FALSE');
1295 end;
1296
1297 END ONT_OEXOEACK_XMLP_PKG;
1298
1299
[Home] [Help]