1 package body hr_tips_api as
2 /* $Header: hrtipapi.pkb 120.4 2012/02/01 10:24:02 chsharma ship $ */
3 --
4 --
5 -- private function
6 --
7 function isR11i(p_application_id in number default 800)
8 RETURN BOOLEAN is
9 --
10 cursor csr_get_prod_verison is
11 select PRODUCT_VERSION
12 from FND_PRODUCT_INSTALLATIONS
13 where APPLICATION_ID = p_application_id;
14 --
15 l_version FND_PRODUCT_INSTALLATIONS.PRODUCT_VERSION%TYPE;
16 --
17 begin
18 open csr_get_prod_verison;
19 fetch csr_get_prod_verison into l_version;
20 close csr_get_prod_verison;
21 l_version := substr(l_version,1,4);
22 l_version := replace(l_version,'.');
23 if to_number(l_version) >= 115 then
24 return true;
25 else
26 return false;
27 end if;
28
29 end isR11i;
30
31 function getTip(p_screen varchar2
32 ,p_field varchar2
33 ,p_language varchar2
34 ,p_business_group_id number default null
35 ,p_default boolean default true
36 ) return varchar2 is
37 --
38
39 l_screen hr_tips.screen%type default null;
40 l_field hr_tips.field%type default null;
41 l_lang_code hr_tips.language_code%type default null;
42 --
43
44 -- For performance improvement:
45 -- We assume that the database attributes, screen, field and language_code
46 -- are loaded to the database in upper case. Thus, we can use the index.
47 cursor csr_get_tip(p_bg NUMBER) is
48 select t.text
49 from hr_tips t
50 where t.screen = l_screen
51 and t.field = l_field
52 and t.language_code = l_lang_code
53 and t.business_group_id = p_bg;
54 --
55 cursor csr_get_tip_default is
56 select t.text
57 from hr_tips t
58 where t.screen = l_screen
59 and t.field = l_field
60 and t.language_code = l_lang_code
61 and t.business_group_id is null;
62 --
63 l_text varchar2 (32000);
64 --
65 begin
66 -- Convert the input parms to upper first for performance improvement.
67 l_screen := upper(p_screen);
68 l_field := upper(p_field);
69 l_lang_code := upper(p_language);
70
71 if p_business_group_id is null then
72 open csr_get_tip_default;
73 fetch csr_get_tip_default into l_text;
74 if csr_get_tip_default%notfound then
75 close csr_get_tip_default;
76 return null;
77 end if;
78 return l_text;
79 else
80 open csr_get_tip(p_business_group_id);
81 fetch csr_get_tip into l_text;
82 if csr_get_tip%notfound then
83 close csr_get_tip;
84 -- if p_default is set to true, and the business group that was
85 -- passed in was not null then return the default tip (bg = null)
86 if p_default AND (p_business_group_id is not null) then
87 open csr_get_tip_default;
88 fetch csr_get_tip_default into l_text;
89 if csr_get_tip_default%notfound then
90 close csr_get_tip_default;
91 return null;
92 end if;
93 else
94 return null;
95 end if;
96 close csr_get_tip_default;
97 return l_text;
98 end if;
99 close csr_get_tip;
100 end if;
101
102 return l_text;
103 end;
104 --
105 function getAllTips(p_screen varchar2
106 ,p_language varchar2
107 ,p_business_group_id number default null
108 ,p_default boolean default true
109 ) return TipRecTable is
110 --
111 l_screen hr_tips.screen%type default null;
112 l_lang_code hr_tips.language_code%type default null;
113 --
114
115 -- For performance improvement:
116 -- We assume that the database attributes, screen and language_code
117 -- are loaded to the database in upper case. Thus, we can use the index.
118
119 cursor csr_get_all_tips(p_bg NUMBER) is
120 select t.field, t.text
121 from hr_tips t
122 where t.screen = l_screen
123 and t.language_code = l_lang_code
124 and t.business_group_id = p_bg
125 and t.field NOT IN ('DISCLAIMER', 'INSTRUCTIONS');
126 --
127 cursor csr_get_all_tips_default(p_bg NUMBER) is
128 select t.field, t.text
129 from hr_tips t
130 where t.screen = l_screen
131 and t.language_code = l_lang_code
132 and t.field NOT IN ('DISCLAIMER', 'INSTRUCTIONS')
133 and ((t.business_group_id is null and not exists (select 'Y'
134 from hr_tips
135 where screen = l_screen
136 and language_code = l_lang_code
137 and business_group_id = p_bg
138 and field = t.field))
139 or t.business_group_id = p_bg);
140 --
141 l_tip_rec TipRecTable;
142
143 v_tipData csr_get_all_tips_default%ROWTYPE;
144 l_count number;
145 --
146 begin
147
148 -- Convert the input parms to upper first for performance improvement.
149 l_screen := upper(p_screen);
150 l_lang_code := upper(p_language);
151
152
153 if p_default or p_business_group_id is null then
154 -- fill table array with tips
155 l_count := 1;
156 for v_tipData in csr_get_all_tips_default(p_business_group_id) loop
157 -- l_tip_rec(l_count) := v_tipData;
158 l_count := l_count + 1;
159 end loop;
160
161 return l_tip_rec;
162 else
163 -- fill table array with tips
164 l_count := 1;
165 for v_tipData in csr_get_all_tips(p_business_group_id) loop
166 -- l_tip_rec(l_count) := v_tipData;
167 l_count := l_count + 1;
168 end loop;
169
170 return l_tip_rec;
171 end if;
172
173 end;
174 --
175 function getInstruction(p_screen varchar2
176 ,p_language varchar2
177 ,p_business_group_id number default null
178 ,p_instruction_name varchar2 default 'INSTRUCTIONS'
179 ,p_default boolean default true
180 ) return varchar2 is
181 --
182 l_screen hr_tips.screen%type default null;
183 l_lang_code hr_tips.language_code%type default null;
184 l_instruction_name hr_tips.field%type default null;
185 --
186
187 -- For performance improvement:
188 -- We assume that the database attributes, screen,field and language_code
189 -- are loaded to the database in upper case. Thus, we can use the index.
190
191 cursor csr_get_instructions(p_default_bg NUMBER) IS
192 select t.text
193 from hr_tips t
194 where t.screen = l_screen
195 and t.field = l_instruction_name
196 and t.language_code = l_lang_code
197 and (NVL(t.business_group_id,-1) = NVL(p_business_group_id,-1)
198 OR NVL(t.business_group_id,-1) = NVL(p_default_bg,-1))
199 order by t.business_group_id;
200 --
201 l_text varchar2 (32000);
202 l_default_business_group_id NUMBER;
203 l_r11i boolean;
204 --
205 begin
206
207 -- Convert the input parms to upper first for performance improvement.
208 l_screen := upper(p_screen);
209 l_lang_code := upper(p_language);
210 l_instruction_name := upper(p_instruction_name);
211
212 l_r11i := isR11i;
213
214 IF p_default THEN
215 l_default_business_group_id := null;
216 ELSE
217 l_default_business_group_id := p_business_group_id;
218 END IF;
219
220 open csr_get_instructions(l_default_business_group_id);
221 fetch csr_get_instructions into l_text;
222 close csr_get_instructions;
223 if l_r11i then
224 l_text := replace(l_text, '/OA_MEDIA/US/','/OA_MEDIA/');
225 end if;
226 IF substr(l_text, 1, 4) = '<BR>' THEN
227 l_text := substr(l_text, 5);
228 END IF;
229 IF substr(l_text, -4, 4) = '<BR>' THEN
230 l_text := substr(l_text, 1, (length(l_text) - 4));
231 END IF;
232 return l_text;
233
234 end;
235 --
236 function getDisclaimer(p_screen varchar2
237 ,p_language varchar2
238 ,p_business_group_id number default null
239 ,p_default boolean default true
240 ) return varchar2 is
241 --
242 --
243 l_screen hr_tips.screen%type default null;
244 l_lang_code hr_tips.language_code%type default null;
245 --
246 -- For performance improvement:
247 -- We assume that the database attributes, screen and language_code
248 -- are loaded to the database in upper case. Thus, we can use the index.
249
250 cursor csr_get_disclaimer(p_default_bg NUMBER) is
251 select t.text
252 from hr_tips t
253 where t.screen = l_screen
254 and t.field = 'DISCLAIMER'
255 and t.language_code = l_lang_code
256 and (NVL(t.business_group_id,0) = NVL(p_business_group_id,0)
257 OR NVL(t.business_group_id,0) = NVL(p_default_bg,0));
258 --
259 l_text varchar2 (32000);
260 l_default_business_group_id NUMBER;
261
262 --
263 begin
264
265 -- Convert the input parms to upper first for performance improvement.
266 l_screen := upper(p_screen);
267 l_lang_code := upper(p_language);
268
269 IF p_default THEN
270 l_default_business_group_id := null;
271 ELSE
272 l_default_business_group_id := p_business_group_id;
273 END IF;
274 open csr_get_disclaimer(l_default_business_group_id);
275 fetch csr_get_disclaimer into l_text;
276 close csr_get_disclaimer;
277 IF substr(l_text, 1, 4) = '<BR>' THEN
278 l_text := substr(l_text, 5);
279 END IF;
280 IF substr(l_text, -4, 4) = '<BR>' THEN
281 l_text := substr(l_text, 1, (length(l_text) - 4));
282 END IF;
283 return l_text;
284
285 end;
286 --
287 end hr_tips_api;
[Home] [Help]