1 PACKAGE BODY ONT_OEXOEASR_XMLP_PKG AS
2 /* $Header: OEXOEASRB.pls 120.1 2007/12/25 07:13:10 npannamp noship $ */
3 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
4 BEGIN
5 BEGIN
6 BEGIN
7 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
8 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
9 EXCEPTION
10 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
11 /*SRW.MESSAGE(1000
12 ,'Failed in BEFORE REPORT trigger')*/NULL;
13 RETURN (FALSE);
14 END;
15 DECLARE
16 L_COMPANY_NAME VARCHAR2(30);
17 L_FUNCTIONAL_CURRENCY VARCHAR2(15);
18 L_SOB_ID VARCHAR2(30);
19 BEGIN
20 /*SRW.REFERENCE(P_SOB_ID)*/NULL;
21 IF P_SOB_ID IS NULL THEN
22 L_SOB_ID := OE_SYS_PARAMETERS.VALUE('SET_OF_BOOKS_ID'
23 ,MO_GLOBAL.GET_CURRENT_ORG_ID);
24 ELSE
25 L_SOB_ID := P_SOB_ID;
26 END IF;
27 SELECT
28 SOB.NAME,
29 SOB.CURRENCY_CODE
30 INTO L_COMPANY_NAME,L_FUNCTIONAL_CURRENCY
31 FROM
32 GL_SETS_OF_BOOKS SOB,
33 FND_CURRENCIES CUR
34 WHERE SOB.SET_OF_BOOKS_ID = L_SOB_ID
35 AND SOB.CURRENCY_CODE = CUR.CURRENCY_CODE;
36 RP_COMPANY_NAME := L_COMPANY_NAME;
37 RP_FUNCTIONAL_CURRENCY := L_FUNCTIONAL_CURRENCY;
38 EXCEPTION
39 WHEN NO_DATA_FOUND THEN
40 NULL;
41 END;
42 DECLARE
43 L_REPORT_NAME VARCHAR2(240);
44 BEGIN
45 SELECT
46 CP.USER_CONCURRENT_PROGRAM_NAME
47 INTO L_REPORT_NAME
48 FROM
49 FND_CONCURRENT_PROGRAMS_VL CP,
50 FND_CONCURRENT_REQUESTS CR
51 WHERE CR.REQUEST_ID = P_CONC_REQUEST_ID
52 AND CP.APPLICATION_ID = CR.PROGRAM_APPLICATION_ID
53 AND CP.CONCURRENT_PROGRAM_ID = CR.CONCURRENT_PROGRAM_ID;
54 L_REPORT_NAME := substr(L_REPORT_NAME,1,instr(L_REPORT_NAME,' (XML)'));
55 RP_REPORT_NAME := L_REPORT_NAME;
56 EXCEPTION
57 WHEN NO_DATA_FOUND THEN
58 RP_REPORT_NAME := 'Agreement Activity Report' || P_ORDER_NUMBER_LOW;
59 END;
60 DECLARE
61 L_CUSTOMER_NAME_LOW VARCHAR2(50);
62 L_CUSTOMER_NAME_HIGH VARCHAR2(50);
63 L_AGREEMENT_NAME_LOW VARCHAR2(50);
64 L_AGREEMENT_NAME_HIGH VARCHAR2(50);
65 BEGIN
66 /*SRW.REFERENCE(P_AGREEMENT_LOW)*/NULL;
67 /*SRW.REFERENCE(P_AGREEMENT_HIGH)*/NULL;
68 IF (P_CUSTOMER_NAME_LOW IS NULL) AND (P_CUSTOMER_NAME_HIGH IS NULL) THEN
69 NULL;
70 ELSE
71 IF P_CUSTOMER_NAME_LOW IS NULL THEN
72 L_CUSTOMER_NAME_LOW := ' ';
73 ELSE
74 L_CUSTOMER_NAME_LOW := SUBSTR(P_CUSTOMER_NAME_LOW
75 ,1
76 ,18);
77 END IF;
78 IF P_CUSTOMER_NAME_HIGH IS NULL THEN
79 L_CUSTOMER_NAME_HIGH := ' ';
80 ELSE
81 L_CUSTOMER_NAME_HIGH := SUBSTR(P_CUSTOMER_NAME_HIGH
82 ,1
83 ,18);
84 END IF;
85 RP_CUSTOMER_NAME_RANGE := 'From ' || L_CUSTOMER_NAME_LOW || ' To ' || L_CUSTOMER_NAME_HIGH;
86 END IF;
87 IF (P_AGREEMENT_LOW IS NULL) AND (P_AGREEMENT_HIGH IS NULL) THEN
88 NULL;
89 ELSE
90 RP_AGREEMENT_RANGE := 'From ' || P_AGREEMENT_LOW || ' To ' || P_AGREEMENT_HIGH;
91 RP_AGREEMENT_NAME_LOW := P_AGREEMENT_LOW;
92 RP_AGREEMENT_NAME_HIGH := P_AGREEMENT_HIGH;
93 END IF;
94 END;
95 END;
96 RETURN (TRUE);
97 END BEFOREREPORT;
98 FUNCTION AFTERREPORT RETURN BOOLEAN IS
99 BEGIN
100 BEGIN
101 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
102 EXCEPTION
103 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
104 /*SRW.MESSAGE(1
105 ,'Failed in AFTER REPORT TRIGGER')*/NULL;
106 RETURN (FALSE);
107 END;
108 RETURN (TRUE);
109 END AFTERREPORT;
110 FUNCTION P_SOB_IDVALIDTRIGGER RETURN BOOLEAN IS
111 BEGIN
112 RETURN (TRUE);
113 END P_SOB_IDVALIDTRIGGER;
114 FUNCTION P_USE_FUNCTIONAL_CURRENCYVALID RETURN BOOLEAN IS
115 BEGIN
116 RETURN (TRUE);
117 END P_USE_FUNCTIONAL_CURRENCYVALID;
118 FUNCTION P_INTERFACE_LINE_CONTEXTVALIDT RETURN BOOLEAN IS
119 BEGIN
120 RETURN (TRUE);
121 END P_INTERFACE_LINE_CONTEXTVALIDT;
122 FUNCTION C_USE_CURRENCYFORMULA(CURRENCY2 IN VARCHAR2) RETURN VARCHAR2 IS
123 BEGIN
124 /*SRW.REFERENCE(RP_FUNCTIONAL_CURRENCY)*/NULL;
125 /*SRW.REFERENCE(CURRENCY2)*/NULL;
126 IF P_USE_FUNCTIONAL_CURRENCY = 'Y' THEN
127 RETURN (RP_FUNCTIONAL_CURRENCY);
128 ELSE
129 RETURN (CURRENCY2);
130 END IF;
131 RETURN NULL;
132 END C_USE_CURRENCYFORMULA;
133 FUNCTION C_DATA_NOT_FOUNDFORMULA(CURRENCY2 IN VARCHAR2) RETURN NUMBER IS
134 BEGIN
135 RP_DATA_FOUND := CURRENCY2;
136 RETURN (0);
137 END C_DATA_NOT_FOUNDFORMULA;
138 FUNCTION AFTERPFORM RETURN BOOLEAN IS
139 BEGIN
140 DECLARE
141 L_AGREEMENT_TYPE VARCHAR2(100);
142 L_DEBUG VARCHAR2(100);
143 BEGIN
144 /*SRW.REFERENCE(P_CUSTOMER_NAME_LOW)*/NULL;
145 /*SRW.REFERENCE(P_CUSTOMER_NAME_HIGH)*/NULL;
146 /*SRW.REFERENCE(P_AGREEMENT_LOW)*/NULL;
147 /*SRW.REFERENCE(P_AGREEMENT_HIGH)*/NULL;
148 /*SRW.REFERENCE(P_AGREEMENT_TYPE)*/NULL;
149 /*SRW.REFERENCE(P_PURCHASE_NUM)*/NULL;
150 /*SRW.REFERENCE(P_SALESPERSON)*/NULL;
151 /*SRW.REFERENCE(P_ORDER_NUMBER_HIGH)*/NULL;
152 /*SRW.REFERENCE(P_ORDER_NUMBER_LOW)*/NULL;
153 IF (P_CUSTOMER_NAME_LOW IS NOT NULL) AND (P_CUSTOMER_NAME_HIGH IS NOT NULL) THEN
154 LP_CUSTOMER_RANGE := 'and c.party_name between :p_customer_name_low and :p_customer_name_high';
155 ELSIF (P_CUSTOMER_NAME_LOW IS NOT NULL) THEN
156 LP_CUSTOMER_RANGE := 'and c.party_name >= :p_customer_name_low ';
157 ELSIF (P_CUSTOMER_NAME_HIGH IS NOT NULL) THEN
158 LP_CUSTOMER_RANGE := 'and c.party_name <= :p_customer_name_high ';
159 END IF;
160 IF (P_AGREEMENT_LOW IS NOT NULL) AND (P_AGREEMENT_HIGH IS NOT NULL) THEN
161 LP_AGREEMENT_RANGE := 'and a.name between :p_agreement_low and :p_agreement_high ';
162 ELSIF (P_AGREEMENT_LOW IS NOT NULL) THEN
163 LP_AGREEMENT_RANGE := 'and a.name >= :p_agreement_low ';
164 ELSIF (P_AGREEMENT_HIGH IS NOT NULL) THEN
165 LP_AGREEMENT_RANGE := 'and a.name <= :p_agreement_high ';
166 END IF;
167 IF (P_ORDER_NUMBER_LOW IS NOT NULL) AND (P_ORDER_NUMBER_HIGH IS NOT NULL) THEN
168 L_DEBUG := 'stmt#1';
169 L_DEBUG := P_ORDER_NUMBER_LOW || ':' || P_ORDER_NUMBER_HIGH;
170 LP_ORDER_NUMBER_RANGE := 'and h.order_number between :p_order_number_low and :p_order_number_high ';
171 ELSIF (P_ORDER_NUMBER_LOW IS NOT NULL) THEN
172 LP_ORDER_NUMBER_RANGE := 'and h.order_number >= :p_order_number_low';
173 ELSIF (P_ORDER_NUMBER_HIGH IS NOT NULL) THEN
174 LP_ORDER_NUMBER_RANGE := 'and h.order_number <= :p_order_number_high ';
175 END IF;
176 IF P_PURCHASE_NUM IS NOT NULL THEN
177 LP_PURCHASE_NUM := 'and a.purchase_order_num = :p_purchase_num';
178 END IF;
179 IF P_SALESPERSON IS NOT NULL THEN
180 LP_SALESPERSON := 'and sr.name = :p_salesperson';
181 END IF;
182 L_DEBUG := 'Before agreement lookup';
183 IF P_AGREEMENT_TYPE IS NOT NULL THEN
184 LP_AGREEMENT_TYPE := 'and lu.lookup_code = :p_agreement_type and lu.lookup_type = ''QP_AGREEMENT_TYPE''';
185 SELECT
186 LU.MEANING
187 INTO L_AGREEMENT_TYPE
188 FROM
189 QP_LOOKUPS LU
190 WHERE LU.LOOKUP_TYPE = 'QP_AGREEMENT_TYPE'
191 AND LU.LOOKUP_CODE = P_AGREEMENT_TYPE;
192 LP_AGREEMENT_TYPE_DSP := L_AGREEMENT_TYPE;
193 END IF;
194 EXCEPTION
195 WHEN NO_DATA_FOUND THEN
196 NULL;
197 WHEN OTHERS THEN
198 /*SRW.MESSAGE(1000
199 ,'Error in AFTER FORM Trigger:' || L_DEBUG || ':' || SQLERRM)*/NULL;
200 END;
201 RETURN (TRUE);
202 END AFTERPFORM;
203 FUNCTION RP_USE_FUNCTIONAL_CURRENCYFORM RETURN VARCHAR2 IS
204 BEGIN
205 DECLARE
206 L_CURRENCY VARCHAR2(80);
207 BEGIN
208 SELECT
209 MEANING
210 INTO L_CURRENCY
211 FROM
212 FND_LOOKUPS
213 WHERE LOOKUP_CODE = P_USE_FUNCTIONAL_CURRENCY
214 AND LOOKUP_TYPE = 'YES_NO';
215 RETURN (L_CURRENCY);
216 EXCEPTION
217 WHEN NO_DATA_FOUND THEN
218 RETURN (L_CURRENCY);
219 END;
220 RETURN NULL;
221 END RP_USE_FUNCTIONAL_CURRENCYFORM;
222 FUNCTION BEFOREPFORM RETURN BOOLEAN IS
223 BEGIN
224 RETURN (TRUE);
225 END BEFOREPFORM;
226 FUNCTION C_QTY_ORDERED_P RETURN NUMBER IS
227 BEGIN
228 RETURN C_QTY_ORDERED;
229 END C_QTY_ORDERED_P;
230 FUNCTION C_QTY_SHIPPED_P RETURN NUMBER IS
231 BEGIN
232 RETURN C_QTY_SHIPPED;
233 END C_QTY_SHIPPED_P;
234 FUNCTION C_QTY_SHIPPED_DSP_P RETURN VARCHAR2 IS
235 BEGIN
236 RETURN C_QTY_SHIPPED_DSP;
237 END C_QTY_SHIPPED_DSP_P;
238 FUNCTION C_QTY_ORDERED_DSP_P RETURN VARCHAR2 IS
239 BEGIN
240 RETURN C_QTY_ORDERED_DSP;
241 END C_QTY_ORDERED_DSP_P;
242 FUNCTION C_QTY_INVOICED_DSP_P RETURN VARCHAR2 IS
243 BEGIN
244 RETURN C_QTY_INVOICED_DSP;
245 END C_QTY_INVOICED_DSP_P;
246 FUNCTION C_QTY_INVOICED_P RETURN NUMBER IS
247 BEGIN
248 RETURN C_QTY_INVOICED;
249 END C_QTY_INVOICED_P;
250 FUNCTION RP_REPORT_NAME_P RETURN VARCHAR2 IS
251 BEGIN
252 RETURN RP_REPORT_NAME;
253 END RP_REPORT_NAME_P;
254 FUNCTION RP_SUB_TITLE_P RETURN VARCHAR2 IS
255 BEGIN
256 RETURN RP_SUB_TITLE;
257 END RP_SUB_TITLE_P;
258 FUNCTION RP_COMPANY_NAME_P RETURN VARCHAR2 IS
259 BEGIN
260 RETURN RP_COMPANY_NAME;
261 END RP_COMPANY_NAME_P;
262 FUNCTION RP_FUNCTIONAL_CURRENCY_P RETURN VARCHAR2 IS
263 BEGIN
264 RETURN RP_FUNCTIONAL_CURRENCY;
265 END RP_FUNCTIONAL_CURRENCY_P;
266 FUNCTION RP_DATA_FOUND_P RETURN VARCHAR2 IS
267 BEGIN
268 RETURN RP_DATA_FOUND;
269 END RP_DATA_FOUND_P;
270 FUNCTION RP_CUSTOMER_NAME_RANGE_P RETURN VARCHAR2 IS
271 BEGIN
272 RETURN RP_CUSTOMER_NAME_RANGE;
273 END RP_CUSTOMER_NAME_RANGE_P;
274 FUNCTION RP_AGREEMENT_RANGE_P RETURN VARCHAR2 IS
275 BEGIN
276 RETURN RP_AGREEMENT_RANGE;
277 END RP_AGREEMENT_RANGE_P;
278 FUNCTION RP_AGREEMENT_NAME_LOW_P RETURN VARCHAR2 IS
279 BEGIN
280 RETURN RP_AGREEMENT_NAME_LOW;
281 END RP_AGREEMENT_NAME_LOW_P;
282 FUNCTION RP_AGREEMENT_NAME_HIGH_P RETURN VARCHAR2 IS
283 BEGIN
284 RETURN RP_AGREEMENT_NAME_HIGH;
285 END RP_AGREEMENT_NAME_HIGH_P;
286 FUNCTION IS_FIXED_RATE(X_FROM_CURRENCY IN VARCHAR2
287 ,X_TO_CURRENCY IN VARCHAR2
288 ,X_EFFECTIVE_DATE IN DATE) RETURN VARCHAR2 IS
289 X0 VARCHAR2(2000);
290 BEGIN
291 /* STPROC.INIT('begin :X0 := GL_CURRENCY_API.IS_FIXED_RATE(:X_FROM_CURRENCY, :X_TO_CURRENCY, :X_EFFECTIVE_DATE); end;');
292 STPROC.BIND_O(X0);
293 STPROC.BIND_I(X_FROM_CURRENCY);
294 STPROC.BIND_I(X_TO_CURRENCY);
295 STPROC.BIND_I(X_EFFECTIVE_DATE);
296 STPROC.EXECUTE;
297 STPROC.RETRIEVE(1
298 ,X0);*/
299 X0 := GL_CURRENCY_API.IS_FIXED_RATE(X_FROM_CURRENCY,X_TO_CURRENCY,X_EFFECTIVE_DATE);
300 RETURN X0;
301 END IS_FIXED_RATE;
302 /*PROCEDURE GET_RELATION(X_FROM_CURRENCY IN VARCHAR2
303 ,X_TO_CURRENCY IN VARCHAR2
304 ,X_EFFECTIVE_DATE IN DATE
305 ,X_FIXED_RATE IN OUT NOCOPY BOOLEAN
306 ,X_RELATIONSHIP IN OUT NOCOPY VARCHAR2) IS
307 BEGIN
308 X_FIXED_RATE := sys.diutil.int_to_bool(X_FIXED_RATE);
309 GL_CURRENCY_API.GET_RELATION(X_FROM_CURRENCY, X_TO_CURRENCY, X_EFFECTIVE_DATE, X_FIXED_RATE, X_RELATIONSHIP);
310 X_FIXED_RATE := sys.diutil.bool_to_int(X_FIXED_RATE);
311 END GET_RELATION;*/
312 FUNCTION GET_EURO_CODE RETURN VARCHAR2 IS
313 X0 VARCHAR2(2000);
314 BEGIN
315 /* STPROC.INIT('begin :X0 := GL_CURRENCY_API.GET_EURO_CODE; end;');
316 STPROC.BIND_O(X0);
317 STPROC.EXECUTE;
318 STPROC.RETRIEVE(1
319 ,X0);*/
320 X0 := GL_CURRENCY_API.GET_EURO_CODE;
321 RETURN X0;
322 END GET_EURO_CODE;
323 FUNCTION GET_RATE(X_FROM_CURRENCY IN VARCHAR2
324 ,X_TO_CURRENCY IN VARCHAR2
325 ,X_CONVERSION_DATE IN DATE
326 ,X_CONVERSION_TYPE IN VARCHAR2) RETURN NUMBER IS
327 X0 NUMBER;
328 BEGIN
329 /* STPROC.INIT('begin :X0 := GL_CURRENCY_API.GET_RATE(:X_FROM_CURRENCY, :X_TO_CURRENCY, :X_CONVERSION_DATE, :X_CONVERSION_TYPE); end;');
330 STPROC.BIND_O(X0);
331 STPROC.BIND_I(X_FROM_CURRENCY);
332 STPROC.BIND_I(X_TO_CURRENCY);
333 STPROC.BIND_I(X_CONVERSION_DATE);
334 STPROC.BIND_I(X_CONVERSION_TYPE);
335 STPROC.EXECUTE;
336 STPROC.RETRIEVE(1
337 ,X0);*/
338 X0 := GL_CURRENCY_API.GET_RATE(X_FROM_CURRENCY, X_TO_CURRENCY, X_CONVERSION_DATE, X_CONVERSION_TYPE);
339 RETURN X0;
340 END GET_RATE;
341 FUNCTION GET_RATE(X_SET_OF_BOOKS_ID IN NUMBER
342 ,X_FROM_CURRENCY IN VARCHAR2
343 ,X_CONVERSION_DATE IN DATE
344 ,X_CONVERSION_TYPE IN VARCHAR2) RETURN NUMBER IS
345 X0 NUMBER;
346 BEGIN
347 /* STPROC.INIT('begin :X0 := GL_CURRENCY_API.GET_RATE(:X_SET_OF_BOOKS_ID, :X_FROM_CURRENCY, :X_CONVERSION_DATE, :X_CONVERSION_TYPE); end;');
348 STPROC.BIND_O(X0);
349 STPROC.BIND_I(X_SET_OF_BOOKS_ID);
350 STPROC.BIND_I(X_FROM_CURRENCY);
351 STPROC.BIND_I(X_CONVERSION_DATE);
352 STPROC.BIND_I(X_CONVERSION_TYPE);
353 STPROC.EXECUTE;
354 STPROC.RETRIEVE(1
355 ,X0);*/
356 X0 := GL_CURRENCY_API.GET_RATE(X_SET_OF_BOOKS_ID, X_FROM_CURRENCY, X_CONVERSION_DATE, X_CONVERSION_TYPE);
357 RETURN X0;
358 END GET_RATE;
359 FUNCTION CONVERT_AMOUNT(X_FROM_CURRENCY IN VARCHAR2
360 ,X_TO_CURRENCY IN VARCHAR2
361 ,X_CONVERSION_DATE IN DATE
362 ,X_CONVERSION_TYPE IN VARCHAR2
363 ,X_AMOUNT IN NUMBER) RETURN NUMBER IS
364 X0 NUMBER;
365 BEGIN
366 /* STPROC.INIT('begin :X0 := GL_CURRENCY_API.CONVERT_AMOUNT(:X_FROM_CURRENCY, :X_TO_CURRENCY, :X_CONVERSION_DATE, :X_CONVERSION_TYPE, :X_AMOUNT); end;');
367 STPROC.BIND_O(X0);
368 STPROC.BIND_I(X_FROM_CURRENCY);
369 STPROC.BIND_I(X_TO_CURRENCY);
370 STPROC.BIND_I(X_CONVERSION_DATE);
371 STPROC.BIND_I(X_CONVERSION_TYPE);
372 STPROC.BIND_I(X_AMOUNT);
373 STPROC.EXECUTE;
374 STPROC.RETRIEVE(1
375 ,X0);*/
376 X0 := GL_CURRENCY_API.CONVERT_AMOUNT(X_FROM_CURRENCY, X_TO_CURRENCY, X_CONVERSION_DATE, X_CONVERSION_TYPE, X_AMOUNT);
377 RETURN X0;
378 END CONVERT_AMOUNT;
379 FUNCTION CONVERT_AMOUNT(X_SET_OF_BOOKS_ID IN NUMBER
380 ,X_FROM_CURRENCY IN VARCHAR2
381 ,X_CONVERSION_DATE IN DATE
382 ,X_CONVERSION_TYPE IN VARCHAR2
383 ,X_AMOUNT IN NUMBER) RETURN NUMBER IS
384 X0 NUMBER;
385 BEGIN
386 /* 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;');
387 STPROC.BIND_O(X0);
388 STPROC.BIND_I(X_SET_OF_BOOKS_ID);
389 STPROC.BIND_I(X_FROM_CURRENCY);
390 STPROC.BIND_I(X_CONVERSION_DATE);
391 STPROC.BIND_I(X_CONVERSION_TYPE);
392 STPROC.BIND_I(X_AMOUNT);
393 STPROC.EXECUTE;
394 STPROC.RETRIEVE(1
395 ,X0);*/
396 X0 := GL_CURRENCY_API.CONVERT_AMOUNT(X_SET_OF_BOOKS_ID, X_FROM_CURRENCY, X_CONVERSION_DATE, X_CONVERSION_TYPE, X_AMOUNT);
397 RETURN X0;
398 END CONVERT_AMOUNT;
399 FUNCTION GET_DERIVE_TYPE(SOB_ID IN NUMBER
400 ,PERIOD IN VARCHAR2
401 ,CURR_CODE IN VARCHAR2) RETURN VARCHAR2 IS
402 X0 VARCHAR2(2000);
403 BEGIN
404 /* STPROC.INIT('begin :X0 := GL_CURRENCY_API.GET_DERIVE_TYPE(:SOB_ID, :PERIOD, :CURR_CODE); end;');
405 STPROC.BIND_O(X0);
406 STPROC.BIND_I(SOB_ID);
407 STPROC.BIND_I(PERIOD);
408 STPROC.BIND_I(CURR_CODE);
409 STPROC.EXECUTE;
410 STPROC.RETRIEVE(1
411 ,X0);
412 RETURN X0;*/
413 X0 := GL_CURRENCY_API.GET_DERIVE_TYPE(SOB_ID, PERIOD, CURR_CODE);
414 END GET_DERIVE_TYPE;
415 --added
416 function C_CONVERT_AMOUNTFormula(p_use_functional_currency in varchar2,qty_ordered in number ,qty_invoiced in number ,qty_shipped in number ,
417 currency2 IN VARCHAR2,rp_functional_currency IN VARCHAR2,conversion_rate in number,p_sob_id in number
418 ,order_date in date,conversion_type_code in varchar2) return Number is
419 begin
420 Declare
421 l_conversion_rate number (15,3);
422 BEGIN
423 l_conversion_rate := 0 ;
424 if p_use_functional_currency = 'N' then
425 c_qty_ordered := nvl (qty_ordered,0);
426 c_qty_invoiced := nvl (qty_invoiced,0);
427 c_qty_shipped := nvl (qty_shipped,0);
428 end if ;
429 IF p_use_functional_currency = 'Y' THEN
430 IF currency2 = rp_functional_currency then
431 l_conversion_rate := 1 ;
432 else
433 IF conversion_rate is null then
434 l_conversion_rate := gl_currency_api.get_rate (
435 p_sob_id,
436 currency2,
437 order_date,
438 conversion_type_code );
439 ELSE
440 l_conversion_rate := conversion_rate ;
441 END IF;
442 END IF;
443 c_qty_ordered := nvl (l_conversion_rate,0) * nvl ( qty_ordered,0);
444 c_qty_shipped := nvl (l_conversion_rate,0) * nvl ( qty_shipped,0);
445 c_qty_invoiced := nvl (l_conversion_rate,0) * nvl (qty_invoiced,0);
446 END IF ;
447 return (0);
448 EXCEPTION
449 WHEN NO_DATA_FOUND THEN
450 c_qty_ordered := 0 ;
451 c_qty_shipped := 0 ;
452 c_qty_invoiced := 0 ;
453 c_qty_ordered_dsp := 'NO RATE' ;
454 c_qty_shipped_dsp := 'NO RATE' ;
455 c_qty_invoiced_dsp := 'NO RATE' ;
456 return (0);
457 end;
458 RETURN NULL; end;
459 END ONT_OEXOEASR_XMLP_PKG;
460