1 PACKAGE BODY ONT_OEXOEACK_XMLP_PKG AS
2 /* $Header: OEXOEACKB.pls 120.2 2011/11/11 14:32:00 vmachett ship $ */
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.2 2011/11/11 14:32:00 vmachett ship $')*/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 oe_debug_pub.add('C_HEADER_CHARGEFORMULA.HEADER_ID :' || C_HEADER_CHARGEFORMULA.HEADER_ID);
756 SELECT
757 SUM(NVL(CHARGE_AMOUNT
758 ,0))
759 INTO L_HEADER_CHARGE
760 FROM
761 OE_CHARGE_LINES_V
762 WHERE HEADER_ID =C_HEADER_CHARGEFORMULA.HEADER_ID
763 AND LINE_ID is null;
764 RETURN (L_HEADER_CHARGE);
765 EXCEPTION
766 WHEN NO_DATA_FOUND THEN
767 RETURN (0);
768 WHEN OTHERS THEN
769 RETURN (0);
770 END C_HEADER_CHARGEFORMULA;
771
772 FUNCTION C_ACCEPT_REQUIREDFORMULA(CONTINGENCY_ID IN NUMBER) RETURN CHAR IS
773 L_RETURN_VALUE VARCHAR(10);
774 BEGIN
775 IF CONTINGENCY_ID IS NULL THEN
776 SELECT
777 MEANING
778 INTO L_RETURN_VALUE
779 FROM
780 OE_LOOKUPS
781 WHERE LOOKUP_TYPE = 'OM_YES_NO'
782 AND LOOKUP_CODE = 'NO';
783 ELSE
784 SELECT
785 MEANING
786 INTO L_RETURN_VALUE
787 FROM
788 OE_LOOKUPS
789 WHERE LOOKUP_TYPE = 'OM_YES_NO'
790 AND LOOKUP_CODE = 'YES';
791 END IF;
792 RETURN L_RETURN_VALUE;
793 END C_ACCEPT_REQUIREDFORMULA;
794
795 FUNCTION C_BILL_CONTACTFORMULA(INVOICE_TO_CONTACT_ID IN NUMBER) RETURN CHAR IS
796 L_BILL_CONTACT VARCHAR2(300);
797 BEGIN
798 SELECT
799 SUBSTRB(BILL_CT_PARTY.PERSON_FIRST_NAME
800 ,1
801 ,40) || ' ' || SUBSTRB(BILL_CT_PARTY.PERSON_LAST_NAME
802 ,1
803 ,50)
804 INTO L_BILL_CONTACT
805 FROM
806 HZ_CUST_ACCOUNT_ROLES BILL_CT_ACCT_ROLE,
807 HZ_PARTIES BILL_CT_PARTY,
808 HZ_RELATIONSHIPS BILL_CT_REL,
809 HZ_ORG_CONTACTS BILL_CT_ORG_CONT,
810 HZ_PARTIES BILL_CT_REL_PARTY,
811 HZ_CUST_ACCOUNTS BILL_CT_ROLE_ACCT
812 WHERE BILL_CT_ACCT_ROLE.CUST_ACCOUNT_ROLE_ID = INVOICE_TO_CONTACT_ID
813 AND BILL_CT_ACCT_ROLE.PARTY_ID = BILL_CT_REL.PARTY_ID
814 AND BILL_CT_ACCT_ROLE.ROLE_TYPE = 'CONTACT'
815 AND BILL_CT_REL.RELATIONSHIP_ID = BILL_CT_ORG_CONT.PARTY_RELATIONSHIP_ID
816 AND BILL_CT_REL.SUBJECT_ID = BILL_CT_PARTY.PARTY_ID
817 AND BILL_CT_REL.PARTY_ID = BILL_CT_REL_PARTY.PARTY_ID
818 AND BILL_CT_REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
819 AND BILL_CT_REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
820 AND BILL_CT_ACCT_ROLE.CUST_ACCOUNT_ID = BILL_CT_ROLE_ACCT.CUST_ACCOUNT_ID
821 AND BILL_CT_ROLE_ACCT.PARTY_ID = BILL_CT_REL.OBJECT_ID;
822 RETURN (L_BILL_CONTACT);
823 EXCEPTION
824 WHEN OTHERS THEN
825 RETURN NULL;
826 END C_BILL_CONTACTFORMULA;
827
828 FUNCTION C_SHIP_CONTACTFORMULA(SHIP_TO_CONTACT_ID IN NUMBER) RETURN CHAR IS
829 L_SHIP_CONTACT VARCHAR2(300);
830 BEGIN
831 SELECT
832 SUBSTRB(SHIP_CT_PARTY.PERSON_FIRST_NAME
833 ,1
834 ,40) || ' ' || SUBSTRB(SHIP_CT_PARTY.PERSON_LAST_NAME
835 ,1
836 ,50)
837 INTO L_SHIP_CONTACT
838 FROM
839 HZ_CUST_ACCOUNT_ROLES SHIP_CT_ACCT_ROLE,
840 HZ_PARTIES SHIP_CT_PARTY,
841 HZ_RELATIONSHIPS SHIP_CT_REL,
842 HZ_ORG_CONTACTS SHIP_CT_ORG_CONT,
843 HZ_PARTIES SHIP_CT_REL_PARTY,
844 HZ_CUST_ACCOUNTS SHIP_CT_ROLE_ACCT
845 WHERE SHIP_CT_ACCT_ROLE.CUST_ACCOUNT_ROLE_ID = SHIP_TO_CONTACT_ID
846 AND SHIP_CT_ACCT_ROLE.PARTY_ID = SHIP_CT_REL.PARTY_ID
847 AND SHIP_CT_ACCT_ROLE.ROLE_TYPE = 'CONTACT'
848 AND SHIP_CT_REL.RELATIONSHIP_ID = SHIP_CT_ORG_CONT.PARTY_RELATIONSHIP_ID
849 AND SHIP_CT_REL.SUBJECT_ID = SHIP_CT_PARTY.PARTY_ID
850 AND SHIP_CT_REL.PARTY_ID = SHIP_CT_REL_PARTY.PARTY_ID
851 AND SHIP_CT_REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
852 AND SHIP_CT_REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
853 AND SHIP_CT_ACCT_ROLE.CUST_ACCOUNT_ID = SHIP_CT_ROLE_ACCT.CUST_ACCOUNT_ID
854 AND SHIP_CT_ROLE_ACCT.PARTY_ID = SHIP_CT_REL.OBJECT_ID;
855 RETURN (L_SHIP_CONTACT);
856 EXCEPTION
857 WHEN OTHERS THEN
858 RETURN NULL;
859 END C_SHIP_CONTACTFORMULA;
860
861 FUNCTION C_DEL_CONTACTFORMULA(DELIVER_TO_CONTACT_ID IN NUMBER) RETURN CHAR IS
862 L_DEL_CONTACT VARCHAR2(300);
863 BEGIN
864 SELECT
865 SUBSTRB(DEL_CT_PARTY.PERSON_FIRST_NAME
866 ,1
867 ,40) || ' ' || SUBSTRB(DEL_CT_PARTY.PERSON_LAST_NAME
868 ,1
869 ,50)
870 INTO L_DEL_CONTACT
871 FROM
872 HZ_CUST_ACCOUNT_ROLES DEL_CT_ACCT_ROLE,
873 HZ_PARTIES DEL_CT_PARTY,
874 HZ_RELATIONSHIPS DEL_CT_REL,
875 HZ_ORG_CONTACTS DEL_CT_ORG_CONT,
876 HZ_PARTIES DEL_CT_REL_PARTY,
877 HZ_CUST_ACCOUNTS DEL_CT_ROLE_ACCT
878 WHERE DEL_CT_ACCT_ROLE.CUST_ACCOUNT_ROLE_ID = DELIVER_TO_CONTACT_ID
879 AND DEL_CT_ACCT_ROLE.PARTY_ID = DEL_CT_REL.PARTY_ID
880 AND DEL_CT_ACCT_ROLE.ROLE_TYPE = 'CONTACT'
881 AND DEL_CT_REL.RELATIONSHIP_ID = DEL_CT_ORG_CONT.PARTY_RELATIONSHIP_ID
882 AND DEL_CT_REL.SUBJECT_ID = DEL_CT_PARTY.PARTY_ID
883 AND DEL_CT_REL.PARTY_ID = DEL_CT_REL_PARTY.PARTY_ID
884 AND DEL_CT_REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
885 AND DEL_CT_REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
886 AND DEL_CT_ACCT_ROLE.CUST_ACCOUNT_ID = DEL_CT_ROLE_ACCT.CUST_ACCOUNT_ID
887 AND DEL_CT_ROLE_ACCT.PARTY_ID = DEL_CT_REL.OBJECT_ID;
888 RETURN (L_DEL_CONTACT);
889 EXCEPTION
890 WHEN OTHERS THEN
891 RETURN NULL;
892 END C_DEL_CONTACTFORMULA;
893
894 FUNCTION RP_DUMMY_ITEM_P RETURN VARCHAR2 IS
895 BEGIN
896 RETURN RP_DUMMY_ITEM;
897 END RP_DUMMY_ITEM_P;
898
899 FUNCTION RP_REPORT_NAME_P RETURN VARCHAR2 IS
900 BEGIN
901 RETURN RP_REPORT_NAME;
902 END RP_REPORT_NAME_P;
903
904 FUNCTION RP_SUB_TITLE_P RETURN VARCHAR2 IS
905 BEGIN
906 RETURN RP_SUB_TITLE;
907 END RP_SUB_TITLE_P;
908
909 FUNCTION RP_COMPANY_NAME_P RETURN VARCHAR2 IS
910 BEGIN
911 RETURN RP_COMPANY_NAME;
912 END RP_COMPANY_NAME_P;
913
914 FUNCTION RP_FUNCTIONAL_CURRENCY_P RETURN VARCHAR2 IS
915 BEGIN
916 RETURN RP_FUNCTIONAL_CURRENCY;
917 END RP_FUNCTIONAL_CURRENCY_P;
918
919 FUNCTION RP_DATA_FOUND_P RETURN VARCHAR2 IS
920 BEGIN
921 RETURN RP_DATA_FOUND;
922 END RP_DATA_FOUND_P;
923
924 FUNCTION RP_ITEM_FLEX_ALL_SEG_P RETURN VARCHAR2 IS
925 BEGIN
926 RETURN RP_ITEM_FLEX_ALL_SEG;
927 END RP_ITEM_FLEX_ALL_SEG_P;
928
929 FUNCTION RP_PRINT_DESCRIPTION_P RETURN VARCHAR2 IS
930 BEGIN
931 RETURN RP_PRINT_DESCRIPTION;
932 END RP_PRINT_DESCRIPTION_P;
933
934 FUNCTION RP_CURR_PROFILE_P RETURN VARCHAR2 IS
935 BEGIN
936 RETURN RP_CURR_PROFILE;
937 END RP_CURR_PROFILE_P;
938
939 FUNCTION RP_ITEM_FLEX_SEG_VAL_P RETURN VARCHAR2 IS
940 BEGIN
941 RETURN RP_ITEM_FLEX_SEG_VAL;
942 END RP_ITEM_FLEX_SEG_VAL_P;
943
944 FUNCTION RP_TAX_TOTAL_ROUNDED_P RETURN NUMBER IS
945 BEGIN
946 RETURN RP_TAX_TOTAL_ROUNDED;
947 END RP_TAX_TOTAL_ROUNDED_P;
948
949 FUNCTION RP_LINE_CHARGE_TOTAL_ROUNDED_P RETURN NUMBER IS
950 BEGIN
951 RETURN RP_LINE_CHARGE_TOTAL_ROUNDED;
952 END RP_LINE_CHARGE_TOTAL_ROUNDED_P;
953
954 FUNCTION RP_HDR_CHARGE_TOTAL_ROUNDED_P RETURN NUMBER IS
955 BEGIN
956 RETURN RP_HDR_CHARGE_TOTAL_ROUNDED;
957 END RP_HDR_CHARGE_TOTAL_ROUNDED_P;
958
959 FUNCTION RP_HDR_CHARGE_TOTAL_P RETURN NUMBER IS
960 BEGIN
961 RETURN RP_HDR_CHARGE_TOTAL;
962 END RP_HDR_CHARGE_TOTAL_P;
963
964 FUNCTION IS_FIXED_RATE(X_FROM_CURRENCY IN VARCHAR2
965 ,X_TO_CURRENCY IN VARCHAR2
966 ,X_EFFECTIVE_DATE IN DATE) RETURN VARCHAR2 IS
967 X0 VARCHAR2(2000);
968 BEGIN
969 /*STPROC.INIT('begin :X0 := GL_CURRENCY_API.IS_FIXED_RATE(:X_FROM_CURRENCY, :X_TO_CURRENCY, :X_EFFECTIVE_DATE); end;');
970 STPROC.BIND_O(X0);
971 STPROC.BIND_I(X_FROM_CURRENCY);
972 STPROC.BIND_I(X_TO_CURRENCY);
973 STPROC.BIND_I(X_EFFECTIVE_DATE);
974 STPROC.EXECUTE;
975 STPROC.RETRIEVE(1
976 ,X0);*/
977 X0 := GL_CURRENCY_API.IS_FIXED_RATE(X_FROM_CURRENCY, X_TO_CURRENCY, X_EFFECTIVE_DATE);
978 RETURN X0;
979 END IS_FIXED_RATE;
980
981 PROCEDURE GET_RELATION(X_FROM_CURRENCY IN VARCHAR2
982 ,X_TO_CURRENCY IN VARCHAR2
983 ,X_EFFECTIVE_DATE IN DATE
984 ,X_FIXED_RATE IN OUT NOCOPY BOOLEAN
985 ,X_RELATIONSHIP IN OUT NOCOPY VARCHAR2) IS
986 BEGIN
987 /* STPROC.INIT('declare X_FIXED_RATE BOOLEAN;
988 begin X_FIXED_RATE := sys.diutil.int_to_bool(:X_FIXED_RATE);
989 GL_CURRENCY_API.GET_RELATION(:X_FROM_CURRENCY, :X_TO_CURRENCY,
990 :X_EFFECTIVE_DATE, X_FIXED_RATE, :X_RELATIONSHIP);
991 :X_FIXED_RATE := sys.diutil.bool_to_int(X_FIXED_RATE); end;');
992 STPROC.BIND_IO(X_FIXED_RATE);
993 STPROC.BIND_I(X_FROM_CURRENCY);
994 STPROC.BIND_I(X_TO_CURRENCY);
995 STPROC.BIND_I(X_EFFECTIVE_DATE);
996 STPROC.BIND_IO(X_RELATIONSHIP);
997 STPROC.EXECUTE;
998 STPROC.RETRIEVE(1
999 ,X_FIXED_RATE);
1000 STPROC.RETRIEVE(5
1001 ,X_RELATIONSHIP);*/
1002 --X_FIXED_RATE := sys.diutil.int_to_bool(X_FIXED_RATE);
1003 GL_CURRENCY_API.GET_RELATION(X_FROM_CURRENCY, X_TO_CURRENCY, X_EFFECTIVE_DATE, X_FIXED_RATE, X_RELATIONSHIP);
1004 --X_FIXED_RATE := sys.diutil.bool_to_int(X_FIXED_RATE);
1005 END GET_RELATION;
1006
1007 FUNCTION GET_EURO_CODE RETURN VARCHAR2 IS
1008 X0 VARCHAR2(2000);
1009 BEGIN
1010 /*STPROC.INIT('begin :X0 := GL_CURRENCY_API.GET_EURO_CODE; end;');
1011 STPROC.BIND_O(X0);
1012 STPROC.EXECUTE;
1013 STPROC.RETRIEVE(1
1014 ,X0);*/
1015 X0 := GL_CURRENCY_API.GET_EURO_CODE;
1016 RETURN X0;
1017 END GET_EURO_CODE;
1018
1019 FUNCTION GET_RATE(X_FROM_CURRENCY IN VARCHAR2
1020 ,X_TO_CURRENCY IN VARCHAR2
1021 ,X_CONVERSION_DATE IN DATE
1022 ,X_CONVERSION_TYPE IN VARCHAR2) RETURN NUMBER IS
1023 X0 NUMBER;
1024 BEGIN
1025 /*STPROC.INIT('begin :X0 := GL_CURRENCY_API.GET_RATE(:X_FROM_CURRENCY, :X_TO_CURRENCY, :X_CONVERSION_DATE, :X_CONVERSION_TYPE); end;');
1026 STPROC.BIND_O(X0);
1027 STPROC.BIND_I(X_FROM_CURRENCY);
1028 STPROC.BIND_I(X_TO_CURRENCY);
1029 STPROC.BIND_I(X_CONVERSION_DATE);
1030 STPROC.BIND_I(X_CONVERSION_TYPE);
1031 STPROC.EXECUTE;
1032 STPROC.RETRIEVE(1
1033 ,X0);*/
1034 X0 := GL_CURRENCY_API.GET_RATE(X_FROM_CURRENCY, X_TO_CURRENCY, X_CONVERSION_DATE, X_CONVERSION_TYPE);
1035 RETURN X0;
1036 END GET_RATE;
1037
1038 FUNCTION GET_RATE(X_SET_OF_BOOKS_ID IN NUMBER
1039 ,X_FROM_CURRENCY IN VARCHAR2
1040 ,X_CONVERSION_DATE IN DATE
1041 ,X_CONVERSION_TYPE IN VARCHAR2) RETURN NUMBER IS
1042 X0 NUMBER;
1043 BEGIN
1044 /*STPROC.INIT('begin :X0 := GL_CURRENCY_API.GET_RATE(:X_SET_OF_BOOKS_ID, :X_FROM_CURRENCY, :X_CONVERSION_DATE, :X_CONVERSION_TYPE); end;');
1045 STPROC.BIND_O(X0);
1046 STPROC.BIND_I(X_SET_OF_BOOKS_ID);
1047 STPROC.BIND_I(X_FROM_CURRENCY);
1048 STPROC.BIND_I(X_CONVERSION_DATE);
1049 STPROC.BIND_I(X_CONVERSION_TYPE);
1050 STPROC.EXECUTE;
1051 STPROC.RETRIEVE(1
1052 ,X0);*/
1053 X0 := GL_CURRENCY_API.GET_RATE(X_SET_OF_BOOKS_ID, X_FROM_CURRENCY, X_CONVERSION_DATE, X_CONVERSION_TYPE);
1054 RETURN X0;
1055 END GET_RATE;
1056
1057 FUNCTION CONVERT_AMOUNT(X_FROM_CURRENCY IN VARCHAR2
1058 ,X_TO_CURRENCY IN VARCHAR2
1059 ,X_CONVERSION_DATE IN DATE
1060 ,X_CONVERSION_TYPE IN VARCHAR2
1061 ,X_AMOUNT IN NUMBER) RETURN NUMBER IS
1062 X0 NUMBER;
1063 BEGIN
1064 /*STPROC.INIT('begin :X0 := GL_CURRENCY_API.CONVERT_AMOUNT(:X_FROM_CURRENCY, :X_TO_CURRENCY, :X_CONVERSION_DATE, :X_CONVERSION_TYPE, :X_AMOUNT); end;');
1065 STPROC.BIND_O(X0);
1066 STPROC.BIND_I(X_FROM_CURRENCY);
1067 STPROC.BIND_I(X_TO_CURRENCY);
1068 STPROC.BIND_I(X_CONVERSION_DATE);
1069 STPROC.BIND_I(X_CONVERSION_TYPE);
1070 STPROC.BIND_I(X_AMOUNT);
1071 STPROC.EXECUTE;
1072 STPROC.RETRIEVE(1
1073 ,X0);*/
1074 X0 := GL_CURRENCY_API.CONVERT_AMOUNT(X_FROM_CURRENCY, X_TO_CURRENCY, X_CONVERSION_DATE, X_CONVERSION_TYPE, X_AMOUNT);
1075 RETURN X0;
1076 END CONVERT_AMOUNT;
1077
1078 FUNCTION CONVERT_AMOUNT(X_SET_OF_BOOKS_ID IN NUMBER
1079 ,X_FROM_CURRENCY IN VARCHAR2
1080 ,X_CONVERSION_DATE IN DATE
1081 ,X_CONVERSION_TYPE IN VARCHAR2
1082 ,X_AMOUNT IN NUMBER) RETURN NUMBER IS
1083 X0 NUMBER;
1084 BEGIN
1085 /*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;');
1086 STPROC.BIND_O(X0);
1087 STPROC.BIND_I(X_SET_OF_BOOKS_ID);
1088 STPROC.BIND_I(X_FROM_CURRENCY);
1089 STPROC.BIND_I(X_CONVERSION_DATE);
1090 STPROC.BIND_I(X_CONVERSION_TYPE);
1091 STPROC.BIND_I(X_AMOUNT);
1092 STPROC.EXECUTE;
1093 STPROC.RETRIEVE(1
1094 ,X0);*/
1095 X0 := GL_CURRENCY_API.CONVERT_AMOUNT(X_SET_OF_BOOKS_ID, X_FROM_CURRENCY, X_CONVERSION_DATE, X_CONVERSION_TYPE, X_AMOUNT);
1096 RETURN X0;
1097 END CONVERT_AMOUNT;
1098
1099 FUNCTION GET_DERIVE_TYPE(SOB_ID IN NUMBER
1100 ,PERIOD IN VARCHAR2
1101 ,CURR_CODE IN VARCHAR2) RETURN VARCHAR2 IS
1102 X0 VARCHAR2(2000);
1103 BEGIN
1104 /*STPROC.INIT('begin :X0 := GL_CURRENCY_API.GET_DERIVE_TYPE(:SOB_ID, :PERIOD, :CURR_CODE); end;');
1105 STPROC.BIND_O(X0);
1106 STPROC.BIND_I(SOB_ID);
1107 STPROC.BIND_I(PERIOD);
1108 STPROC.BIND_I(CURR_CODE);
1109 STPROC.EXECUTE;
1110 STPROC.RETRIEVE(1
1111 ,X0);*/
1112 X0 := GL_CURRENCY_API.GET_DERIVE_TYPE(SOB_ID, PERIOD, CURR_CODE);
1113 RETURN X0;
1114 END GET_DERIVE_TYPE;
1115
1116 FUNCTION RATE_EXISTS(X_FROM_CURRENCY IN VARCHAR2
1117 ,X_TO_CURRENCY IN VARCHAR2
1118 ,X_CONVERSION_DATE IN DATE
1119 ,X_CONVERSION_TYPE IN VARCHAR2) RETURN VARCHAR2 IS
1120 X0 VARCHAR2(2000);
1121 BEGIN
1122 /*STPROC.INIT('begin :X0 := GL_CURRENCY_API.RATE_EXISTS(:X_FROM_CURRENCY, :X_TO_CURRENCY, :X_CONVERSION_DATE, :X_CONVERSION_TYPE); end;');
1123 STPROC.BIND_O(X0);
1124 STPROC.BIND_I(X_FROM_CURRENCY);
1125 STPROC.BIND_I(X_TO_CURRENCY);
1126 STPROC.BIND_I(X_CONVERSION_DATE);
1127 STPROC.BIND_I(X_CONVERSION_TYPE);
1128 STPROC.EXECUTE;
1129 STPROC.RETRIEVE(1
1130 ,X0);*/
1131 X0 := GL_CURRENCY_API.RATE_EXISTS(X_FROM_CURRENCY, X_TO_CURRENCY, X_CONVERSION_DATE, X_CONVERSION_TYPE);
1132 RETURN X0;
1133 END RATE_EXISTS;
1134
1135 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
1136 BEGIN
1137
1138 DECLARE
1139 BEGIN
1140
1141 /* BEGIN
1142 SRW.USER_EXIT('FND SRWINIT');
1143 EXCEPTION
1144 WHEN SRW.USER_EXIT_FAILURE THEN
1145 SRW.MESSAGE (1000,'Failed in BEFORE REPORT trigger - SRWINIT USER EXIT');
1146 return (FALSE);
1147 END;*/
1148
1149 BEGIN /*MOAC*/
1150
1151 P_ORG_ID:= MO_GLOBAL.GET_CURRENT_ORG_ID();
1152 END;
1153
1154
1155
1156 /*------------------------------------------------------------------------------
1157 Following PL/SQL block gets the company name, functional currency and precision.
1158 ------------------------------------------------------------------------------*/
1159
1160
1161 DECLARE
1162 l_company_name VARCHAR2 (100);
1163 l_functional_currency VARCHAR2 (15);
1164
1165 BEGIN
1166
1167 SELECT sob.name ,
1168 sob.currency_code
1169 INTO
1170 l_company_name ,
1171 l_functional_currency
1172 FROM gl_sets_of_books sob,
1173 fnd_currencies cur
1174 WHERE sob.set_of_books_id = p_sob_id
1175 AND sob.currency_code = cur.currency_code
1176 ;
1177
1178 rp_company_name := l_company_name;
1179 rp_functional_currency := l_functional_currency ;
1180
1181
1182 EXCEPTION
1183 WHEN NO_DATA_FOUND THEN
1184 /*srw.message(2000,'Failed in BEFORE REPORT trigger. get company, currency');*/null;
1185 when OTHERS then
1186 /*srw.message(2000,'Failed in BEFORE REPORT trigger. get company, currency'); */null;
1187 END ;
1188
1189 /*------------------------------------------------------------------------------
1190 Following PL/SQL block gets the report name for the passed concurrent request Id.
1191 ------------------------------------------------------------------------------*/
1192 DECLARE
1193 l_report_name VARCHAR2(240);
1194 BEGIN
1195 SELECT cp.user_concurrent_program_name
1196 INTO l_report_name
1197 FROM FND_CONCURRENT_PROGRAMS_VL cp,
1198 FND_CONCURRENT_REQUESTS cr
1199 WHERE cr.request_id = P_CONC_REQUEST_ID
1200 AND cp.application_id = cr.program_application_id
1201 AND cp.concurrent_program_id = cr.concurrent_program_id
1202 ;
1203
1204 RP_Report_Name := l_report_name;
1205 EXCEPTION
1206 WHEN NO_DATA_FOUND
1207 THEN RP_REPORT_NAME := 'Sales Order Acknowledgement';
1208 when OTHERS then
1209 /*srw.message(2000,'Failed in BEFORE REPORT trigger. get company, currency'); */null;
1210 END;
1211
1212 /*------------------------------------------------------------------------------
1213 The following block retrieves the profile option value for standard vs
1214 extended currency
1215 -------------------------------------------------------------------------------*/
1216
1217 /* BEGIN
1218 SRW.REFERENCE(:p_profile_name);
1219 SRW.USER_EXIT('FND GETPROFILE NAME=":p_profile_name"
1220 FIELD=":RP_CURR_PROFILE"
1221 PRINT_ERROR="N"
1222 ');
1223
1224
1225 EXCEPTION
1226 WHEN SRW.USER_EXIT_FAILURE THEN
1227 srw.message(2000,'Failed in BEFORE REPORT trigger. FND GETPROFILE - CURR USER_EXIT');
1228 END; */
1229
1230
1231
1232 /*------------------------------------------------------------------------------
1233 Following PL/SQL block builds up the lexical parameters, to be used in the
1234 WHERE clause of the query. This also populates the report level variables, used
1235 to store the flexfield structure.
1236 ------------------------------------------------------------------------------*/
1237 /*BEGIN
1238 SRW.REFERENCE(:P_item_flex_code);
1239 SRW.REFERENCE(:P_item_structure_num);
1240
1241
1242 SRW.USER_EXIT('FND FLEXSQL CODE=":p_item_flex_code"
1243 NUM=":p_item_structure_num"
1244 APPL_SHORT_NAME="INV"
1245 OUTPUT=":rp_item_flex_all_seg"
1246 MODE="SELECT"
1247 DISPLAY="ALL"
1248 TABLEALIAS="SI"
1249 ');
1250
1251 EXCEPTION
1252 WHEN SRW.USER_EXIT_FAILURE THEN
1253 srw.message(2000,'Failed in BEFORE REPORT trigger. FND FLEXSQL USER_EXIT');
1254 END; */
1255
1256
1257
1258 DECLARE
1259 l_meaning VARCHAR2 (80);
1260 BEGIN
1261 SELECT MEANING
1262 INTO l_meaning
1263 FROM OE_LOOKUPS
1264 WHERE LOOKUP_TYPE = 'ITEM_DISPLAY_CODE'
1265 AND LOOKUP_CODE = substr(upper(p_print_description),1,1)
1266 ;
1267
1268 rp_print_description := l_meaning ;
1269 EXCEPTION WHEN NO_DATA_FOUND THEN
1270 rp_print_description := 'Internal Item Description';
1271 when OTHERS then
1272 /*srw.message(2000,'Failed in BEFORE REPORT trigger. Get Print Description'); */null;
1273
1274 END ;
1275
1276
1277
1278 /* ----- The follwoing PL/SQL block populates the multi-lingual
1279 lexical parameters----- */
1280 BEGIN
1281 populate_mls_lexicals;
1282 END;
1283
1284 END ;
1285 RETURN (TRUE);
1286 END;
1287
1288 function F_PeriodicityFormatTrigger return VARCHAR2 is
1289 begin
1290 if OE_SYS_PARAMETERS.Value('RECURRING_CHARGES',mo_global.get_current_org_id()) = 'Y' THEN
1291 return('TRUE');
1292 else
1293 return ('FALSE');
1294 end if;
1295 return ('FALSE');
1296 end;
1297
1298 -- Added this function for Bug 12690206
1299 FUNCTION ITEM_DSPFORMULA(ITEM_IDENTIFIER_TYPE IN VARCHAR2,INVENTORY_ITEM_ID IN NUMBER,
1300 C_MASTER_ORG IN VARCHAR2,ORDERED_ITEM_ID IN NUMBER,ORDERED_ITEM IN VARCHAR2,SI_ORGANIZATION_ID IN NUMBER,
1301 SI_INVENTORY_ITEM_ID IN NUMBER) RETURN VARCHAR2 IS
1302 v_item varchar2(2000);
1303 v_description varchar2(500);
1304 begin
1305
1306 if (item_identifier_type is null or item_identifier_type = 'INT')
1307 or (p_print_description in ('I','D','F')) then
1308 select sitems.description description
1309 into v_description
1310 from mtl_system_items_vl sitems
1311 where nvl(sitems.organization_id,0) = c_master_org
1312 and sitems.inventory_item_id = ITEM_DSPFORMULA.inventory_item_id;
1313
1314
1315 rp_dummy_item := v_item;
1316 -- rp_dummy_item := '';
1317 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');
1318
1319
1320 elsif (item_identifier_type = 'CUST' and p_print_description in ('C','P','O')) then
1321
1322 select citems.customer_item_number item,
1323 nvl(citems.customer_item_desc,sitems.description) description
1324 into v_item,v_description
1325 from mtl_customer_items citems,
1326 mtl_customer_item_xrefs cxref,
1327 mtl_system_items_vl sitems
1328 where citems.customer_item_id = cxref.customer_item_id
1329 and cxref.inventory_item_id = sitems.inventory_item_id
1330 and citems.customer_item_id = ordered_item_id
1331 and nvl(sitems.organization_id,0) = c_master_org
1332 and sitems.inventory_item_id = ITEM_DSPFORMULA.inventory_item_id;
1333
1334 elsif (p_print_description in ('C','P','O')) then
1335 Begin
1336 select items.cross_reference item,
1337 nvl(items.description,sitems.description) description
1338 into v_item,v_description
1339 from mtl_cross_reference_types xtypes,
1340 mtl_cross_references items,
1341 mtl_system_items_vl sitems
1342 where xtypes.cross_reference_type = items.cross_reference_type
1343 and items.inventory_item_id = sitems.inventory_item_id
1344 and items.cross_reference = ordered_item
1345 and items.cross_reference_type = item_identifier_type
1346 and nvl(sitems.organization_id,0) = c_master_org
1347 and sitems.inventory_item_id = ITEM_DSPFORMULA.inventory_item_id
1348 --Bug 3433353 begin
1349 and items.org_independent_flag = 'N'
1350 and items.organization_id = c_master_org;
1351 -- and sitems.customer_order_enabled_flag = 'Y'
1352 -- and sitems.bom_item_type in (1,4)
1353 Exception When NO_DATA_FOUND Then
1354 select items.cross_reference item,
1355 nvl(items.description,sitems.description) description
1356 into v_item,v_description
1357 from mtl_cross_reference_types xtypes,
1358 mtl_cross_references items,
1359 mtl_system_items_vl sitems
1360 where xtypes.cross_reference_type = items.cross_reference_type
1361 and items.inventory_item_id = sitems.inventory_item_id
1362 and items.cross_reference = ordered_item
1363 and items.cross_reference_type = item_identifier_type
1364 and nvl(sitems.organization_id,0) = c_master_org
1365 and sitems.inventory_item_id = ITEM_DSPFORMULA.inventory_item_id
1366 and items.org_independent_flag = 'Y';
1367 End;
1368 --Bug 343353 End
1369 end if;
1370
1371 if (p_print_description in ('I','C')) then
1372 return(v_item||' - '||v_description);
1373 elsif (p_print_description in ('D','P')) then
1374 return(v_description);
1375 else
1376 return(v_item);
1377 end if;
1378
1379 RETURN NULL;
1380 Exception
1381 When Others Then
1382 return('Item Not Found');
1383 end ITEM_DSPFORMULA;
1384
1385 FUNCTION test (abc IN VARCHAR) RETURN VARCHAR2 IS
1386 BEGIN
1387 oe_debug_pub.add(abc);
1388 return 'abc';
1389 END test; --End of bug# 12690206
1390
1391 END ONT_OEXOEACK_XMLP_PKG;
1392
[Home] [Help]