1 package body fffunc as
2 /* $Header: fffunc.pkb 120.2 2006/11/10 13:39:11 alogue noship $ */
3 /*
4 Copyright (c) Oracle Corporation (UK) Ltd 1993.
5 All rights reserved
6
7 Name: fffunc
8
9 Description: All functions which are called, but are not built-in pl/sql
10 functions are defined here, so that formula can call them as
11 External functions rather than UDF's
12
13 Change List
14 -----------
15 P Gowers 21-JAN-1993 Creation
16 A Roussel 27-OCT-1994 Moved header to after create/replace
17 A Rashid 30-MAR-1999 Added nc, tn, td, and dc functions.
18 Call fnd_number and fnd_date canonical
19 functions where appropriate.
20 A Rashid 27-APR-1999 Replace fnd_number and fnd_date canonical
21 calls by standard to_date, to_number,
22 and to_char calls. Added cn/cd functions
23 for converting canonical strings to
24 number/date.
25 A Rashid 26-JUL-1999 Have to undo the previous change because
26 of formulas using TO_NUMBER etc. to
27 read from user_tables which store data in
28 canonical format (bug946606).
29 A Rashid 19-AUG-1999 Correct function header comments.
30 A Rashid 19-JAN-2000 Use explicit format mask
31 FXYYYY/MM/DD HH24:MI:SS
32 because the date_to_canonical
33 function does not use the FX
34 qualifier which allows certain
35 DD-MON-YYYY date strings to be
36 incorrectly accepted.
37 A Rashid 11-JUL-2002 Added GFM for FND message handling.
38 A Rashid 12-JUL-2002 Hold DD-MON-YYYY date format in a
39 local variable to get around GSSC
40 compliance fatal error. DD-MON-YYYY
41 is the legacy FF date format.
42 A Rashid 12-JUL-2002 Use substrb instead of substr for
43 message text truncation. Use
44 FF_EXEC.FF_BIND_LEN to truncate the
45 returned message text.
46 A Logue 20-JAN-2006 Remove Group Seperator in tn
47 before passing to fnd_number
48 canonical_to_number. Bug 4765352.
49 A Logue 10-NOV-2006 Undo last change. Bug 5654185.
50 */
51 ---------------------------------- nc -----------------------------------
52 /*
53 NAME
54 nc - Number to Character
55 DESCRIPTION
56 Converts to string in canonical format.
57 NOTES
58 Short name to keep formula text short.
59 */
60 function nc (p_number in number) return varchar2 is
61 begin
62 return fnd_number.number_to_canonical(p_number);
63 end nc;
64 ------------------------------ date_to_char ------------------------------
65 /*
66 NAME
67 date_to_char
68 DESCRIPTION
69 Calls dc.
70 */
71 function date_to_char (datevar in date) return varchar2 is
72 begin
73 return dc(p_date => datevar);
74 end date_to_char;
75 --------------------------------- dc -------------------------------------
76 /*
77 NAME
78 dc - Date to Char
79 DESCRIPTION
80 Returns date string in canonical format.
81 NOTES
82 Shortened name to keep formula text short.
83 */
84 function dc (p_date in date) return varchar2 is
85 begin
86 return fnd_date.date_to_canonical(p_date);
87 end dc;
88 ------------------------------ round_up ------------------------------
89 /*
90 NAME
91 round_up
92 DESCRIPTION
93 Special rounding function not available in pl/sql
94 */
95 function round_up (num in number, places in number ) return number is
96 pow_res number;
97 begin
98 pow_res:=power(10,places);
99 return (ceil(num * pow_res)/pow_res);
100 end round_up;
101 ------------------------------ add_days ------------------------------
102 /*
103 NAME
104 add_days
105 DESCRIPTION
106 No equivalent function in pl/sql. Adding days to a date is a simple
107 addition of a number to a date, but a function is needed for FastFormula
108 */
109 function add_days (datevar in date, days in number) return date is
110 begin
111 return (datevar + days);
112 end add_days;
113 ------------------------------ add_years ------------------------------
114 /*
115 NAME
116 add_years
117 DESCRIPTION
118 No equivalent function in pl/sql. Need to call add_months but multiply
119 the passed year count by 12
120 */
121 function add_years (datevar in date, years in number) return date is
122 begin
123 return (add_months(datevar, trunc(years)*12));
124 end add_years;
125 ------------------------------ days_between ------------------------------
126 /*
127 NAME
128 days_between
129 DESCRIPTION
130 No equivalent function in pl/sql. Normally evaluate by subtracting
131 one date from the other, but a function is needed for FastFormula
132 */
133 function days_between (date1 in date, date2 in date) return number is
134 begin
135 return (date1 - date2);
136 end days_between;
137 ---------------------------------- tn -----------------------------------
138 /*
139 NAME
140 tn - Text to Number
141 DESCRIPTION
142 Converts a string in the canonical format to a number.
143 NOTES
144 Short name to keep formula text short.
145 */
146 function tn (p_numstr in varchar2) return number is
147 begin
148 return fnd_number.canonical_to_number(p_numstr);
149 end tn;
150 ------------------------------ text_to_date ------------------------------
151 /*
152 NAME
153 text_to_date
154 DESCRIPTION
155 Accepts the 11 character date format specifier 'DD-MON-YYYY', or the
156 canonical format.
157 */
158 function text_to_date (datestr in varchar2) return date is
159 d date;
160 begin
161 return td(p_datestr => datestr);
162 end text_to_date;
163 ---------------------------------- td -----------------------------------
164 /*
165 NAME
166 td - Text to Date
167 DESCRIPTION
168 Called by text_to_date.
169 NOTES
170 Shortened name to keep formula text short.
171 */
172 function td (p_datestr in varchar2) return date is
173 d date;
174 legacy_format varchar2(64) := 'DD-MON-YYYY';
175 begin
176 --
177 -- Try the canonical date format first.
178 --
179 begin
180 d := to_date(p_datestr, 'FXYYYY/MM/DD HH24:MI:SS');
181 return d;
182 exception
183 when others then
184 null;
185 end;
186 --
187 -- Try the old FF date format.
188 --
189 begin
190 d := to_date(p_datestr,legacy_format);
191 return d;
192 exception
193 when others then
194 null;
195 end;
196 --
197 raise value_error;
198 end td;
199 ---------------------------------- cd -----------------------------------
200 /*
201 NAME
202 cd - Canonical string to Date
203 DESCRIPTION
204 Converts a string, in the canonical date format, to a date.
205 */
206 function cd (p_datestr in varchar2) return date is
207 begin
208 return fnd_date.canonical_to_date(canonical => p_datestr);
209 end cd;
210 ---------------------------------- cn -----------------------------------
211 /*
212 NAME
213 cn - Canonical string to Number
214 DESCRIPTION
215 Converts a string, in the canonical number format, to a number.
216 */
217 function cn (p_numstr in varchar2) return number is
218 begin
219 return fnd_number.canonical_to_number(canonical => p_numstr);
220 end cn;
221 --------------------------------- gfm -----------------------------------
222 function gfm
223 (p_application in varchar2
224 ,p_message in varchar2
225 ,p_token_name1 in varchar2 default null
226 ,p_token_value1 in varchar2 default null
227 ,p_token_name2 in varchar2 default null
228 ,p_token_value2 in varchar2 default null
229 ,p_token_name3 in varchar2 default null
230 ,p_token_value3 in varchar2 default null
231 ,p_token_name4 in varchar2 default null
232 ,p_token_value4 in varchar2 default null
233 ,p_token_name5 in varchar2 default null
234 ,p_token_value5 in varchar2 default null
235 ) return varchar2 is
236 begin
237 --
238 -- Keep this code as simple as possible.
239 --
240 fnd_message.set_name(p_application, p_message);
241 --
242 if p_token_name1 is not null and p_token_value1 is not null then
243 fnd_message.set_token(p_token_name1, p_token_value1, false);
244 end if;
245 --
246 if p_token_name2 is not null and p_token_value2 is not null then
247 fnd_message.set_token(p_token_name2, p_token_value2, false);
248 end if;
249 --
250 if p_token_name3 is not null and p_token_value3 is not null then
251 fnd_message.set_token(p_token_name3, p_token_value3, false);
252 end if;
253 --
254 if p_token_name4 is not null and p_token_value4 is not null then
255 fnd_message.set_token(p_token_name4, p_token_value4, false);
256 end if;
257 --
258 if p_token_name5 is not null and p_token_value5 is not null then
259 fnd_message.set_token(p_token_name5, p_token_value5, false);
260 end if;
261 --
262 -- Just return the message name itself if the text is NULL,
263 -- otherwise return the (truncated) message text.
264 --
265 return substrb(nvl(fnd_message.get, p_message), 1, FF_EXEC.FF_BIND_LEN);
266 end gfm;
267 --
268 end fffunc;
[Home] [Help]