[Home] [Help]
PACKAGE BODY: APPS.PER_CPL_SHD
Source
1 Package Body per_cpl_shd as
2 /* $Header: pecplrhi.pkb 120.0.12000000.2 2007/05/30 12:19:15 arumukhe ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' per_cpl_shd.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |---------------------------< constraint_error >---------------------------|
12 -- ----------------------------------------------------------------------------
13 Procedure constraint_error
14 (p_constraint_name in all_constraints.constraint_name%TYPE
15 ) Is
16 --
17 l_proc varchar2(72) := g_package||'constraint_error';
18 --
19 Begin
20 --
21 If (p_constraint_name = 'PER_COMPETENCES_TL_PK') Then
22 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
23 fnd_message.set_token('PROCEDURE', l_proc);
24 fnd_message.set_token('STEP','5');
25 fnd_message.raise_error;
26 Else
27 fnd_message.set_name('PAY', 'HR_7877_API_INVALID_CONSTRAINT');
28 fnd_message.set_token('PROCEDURE', l_proc);
29 fnd_message.set_token('CONSTRAINT_NAME', p_constraint_name);
30 fnd_message.raise_error;
31 End If;
32 --
33 End constraint_error;
34 --
35 -- ----------------------------------------------------------------------------
36 -- |-----------------------------< api_updating >-----------------------------|
37 -- ----------------------------------------------------------------------------
38 Function api_updating
39 (p_competence_id in number
40 ,p_language in varchar2
41 ) Return Boolean Is
42 --
43 --
44 -- Cursor selects the 'current' row from the HR Schema
45 --
46 Cursor C_Sel1 is
47 select
48 competence_id
49 ,language
50 ,source_lang
51 ,name
52 ,competence_alias
53 ,behavioural_indicator
54 ,description
55 from per_competences_tl
56 where competence_id = p_competence_id
57 and language = p_language;
58 --
59 l_fct_ret boolean;
60 --
61 Begin
62 --
63 If (p_competence_id is null or
64 p_language is null
65 ) Then
66 --
67 -- One of the primary key arguments is null therefore we must
68 -- set the returning function value to false
69 --
70 l_fct_ret := false;
71 Else
72 If (p_competence_id
73 = per_cpl_shd.g_old_rec.competence_id and
74 p_language
75 = per_cpl_shd.g_old_rec.language
76 ) Then
77 --
78 -- The g_old_rec is current therefore we must
79 -- set the returning function to true
80 --
81 l_fct_ret := true;
82 Else
83 --
84 -- Select the current row into g_old_rec
85 --
86 Open C_Sel1;
87 Fetch C_Sel1 Into per_cpl_shd.g_old_rec;
88 If C_Sel1%notfound Then
89 Close C_Sel1;
90 --
91 -- The primary key is invalid therefore we must error
92 --
93 fnd_message.set_name('PAY', 'HR_7220_INVALID_PRIMARY_KEY');
94 fnd_message.raise_error;
95 End If;
96 Close C_Sel1;
97 --
98 l_fct_ret := true;
99 End If;
100 End If;
101 Return (l_fct_ret);
102 --
103 End api_updating;
104 --
105 -- ----------------------------------------------------------------------------
106 -- |---------------------------------< lck >----------------------------------|
107 -- ----------------------------------------------------------------------------
108 Procedure lck
109 (p_competence_id in number
110 ,p_language in varchar2
111 ) is
112 --
113 -- Cursor selects the 'current' row from the HR Schema
114 --
115 Cursor C_Sel1 is
116 select
117 competence_id
118 ,language
119 ,source_lang
120 ,name
121 ,competence_alias
122 ,behavioural_indicator
123 ,description
124 from per_competences_tl
125 where competence_id = p_competence_id
126 and language = p_language
127 for update nowait;
128 --
129 l_proc varchar2(72) := g_package||'lck';
130 --
131 Begin
132 hr_utility.set_location('Entering:'||l_proc, 5);
133 --
134 hr_api.mandatory_arg_error
135 (p_api_name => l_proc
136 ,p_argument => 'COMPETENCE_ID'
137 ,p_argument_value => p_competence_id
138 );
139 hr_utility.set_location(l_proc,6);
140 hr_api.mandatory_arg_error
141 (p_api_name => l_proc
142 ,p_argument => 'LANGUAGE'
143 ,p_argument_value => p_language
144 );
145 --
146 Open C_Sel1;
147 Fetch C_Sel1 Into per_cpl_shd.g_old_rec;
148 If C_Sel1%notfound then
149 Close C_Sel1;
150 --
151 -- The primary key is invalid therefore we must error
152 --
153 fnd_message.set_name('PAY', 'HR_7220_INVALID_PRIMARY_KEY');
154 fnd_message.raise_error;
155 End If;
156 Close C_Sel1;
157 --
158 --
159 hr_utility.set_location(' Leaving:'||l_proc, 10);
160 --
161 -- We need to trap the ORA LOCK exception
162 --
163 Exception
164 When HR_Api.Object_Locked then
165 --
166 -- The object is locked therefore we need to supply a meaningful
167 -- error message.
168 --
169 fnd_message.set_name('PAY', 'HR_7165_OBJECT_LOCKED');
170 fnd_message.set_token('TABLE_NAME', 'per_competences_tl');
171 fnd_message.raise_error;
172 End lck;
173 --
174 -- ----------------------------------------------------------------------------
175 -- |----------------------------< add_language >------------------------------|
176 procedure ADD_LANGUAGE
177 is
178 --
179 -- Return new tl competence records for new languages
180 --
181 cursor c_new_competences_tl is
182 select cpl.competence_id
183 , cd.id_flex_num
184 , cd.competence_definition_id
185 , cpl.created_by
186 , cpl.creation_date
187 , cpl.last_updated_by
188 , cpl.last_update_date
189 , cpl.last_update_login
190 , l.language_code
191 , cpl.source_lang
192 , l.nls_language
193 , cpl.behavioural_indicator
194 , cpl.description
195 , cpl.competence_alias
196 , cpl.name
197 --
198 from per_competences cpn
199 , per_competence_definitions cd
200 , per_competences_tl cpl
201 , fnd_languages l
202 where cpn.competence_definition_id = cd.competence_definition_id
203 and cpn.competence_id = cpl.competence_id
204 and cpl.language = userenv('LANG')
205 and l.installed_flag in ('I', 'B')
206 and not exists (select null
207 from per_competences_tl t
208 where t.competence_id = cpl.competence_id
209 and t.language = l.language_code)
210 order by l.language_code;
211 --
212 l_userenv_language_code VARCHAR2(4) := userenv('LANG'); --Bug 2962837.
213 --
214 l_current_nls_language VARCHAR2(30);
215 --
216 l_proc varchar2(72) := g_package||'add_language';
217 --
218 begin
219 --
220 hr_utility.set_location('Entering:'||l_proc, 5);
221 --
222 delete from PER_COMPETENCES_TL T
223 where not exists
224 (select NULL
225 from PER_COMPETENCES B
226 where B.COMPETENCE_ID = T.COMPETENCE_ID
227 );
228
229
230 For l_competences_tl IN c_new_competences_tl loop
231 --
232 -- Only set session nls language if changed.
233 --
234 If l_current_nls_language = l_competences_tl.nls_language then
235
236 null; -- Have not changed so do nothing.
237
238 Else
239
240 hr_kflex_utility.set_session_nls_language( l_competences_tl.nls_language );
241 l_current_nls_language := l_competences_tl.nls_language;
242
243 End if;
244 --
245
246 insert into PER_COMPETENCES_TL (
247 COMPETENCE_ID,
248 NAME,
249 CREATED_BY,
250 CREATION_DATE,
251 LAST_UPDATED_BY,
252 LAST_UPDATE_DATE,
253 LAST_UPDATE_LOGIN,
254 LANGUAGE,
255 SOURCE_LANG,
256 BEHAVIOURAL_INDICATOR,
257 DESCRIPTION,
258 COMPETENCE_ALIAS
259 ) values
260 (l_competences_tl.competence_id
261 , nvl(fnd_flex_ext.get_segs( g_app_code
262 , g_flex_code
263 , l_competences_tl.id_flex_num
264 , l_competences_tl.competence_definition_id
265 )
266 , l_competences_tl.name
267 )
268 , l_competences_tl.created_by
269 , l_competences_tl.creation_date
270 , l_competences_tl.last_updated_by
271 , l_competences_tl.last_update_date
272 , l_competences_tl.last_update_login
273 , l_competences_tl.language_code
274 , l_competences_tl.source_lang
275 , l_competences_tl.behavioural_indicator
276 , l_competences_tl.description
277 , l_competences_tl.competence_alias);
278 --
279
280 --
281 End loop;
282 --
283 hr_kflex_utility.set_session_language_code( l_userenv_language_code );
284 --
285 hr_utility.set_location(' Leaving:'||l_proc, 10);
286 --
287
288 Exception
289 --
290 When Others Then
291 --
292 hr_kflex_utility.set_session_language_code( l_userenv_language_code );
293 --
294 raise;
295 end ADD_LANGUAGE;
296 -- ----------------------------------------------------------------------------
297 -- |-----------------------------< convert_args >-----------------------------|
298 -- ----------------------------------------------------------------------------
299 Function convert_args
300 (p_competence_id in number
301 ,p_language in varchar2
302 ,p_source_lang in varchar2
303 ,p_name in varchar2
304 ,p_competence_alias in varchar2
305 ,p_behavioural_indicator in varchar2
306 ,p_description in varchar2
307 )
308 Return g_rec_type is
309 --
310 l_rec g_rec_type;
311 --
312 Begin
313 --
314 -- Convert arguments into local l_rec structure.
315 --
316 l_rec.competence_id := p_competence_id;
317 l_rec.language := p_language;
318 l_rec.source_lang := p_source_lang;
319 l_rec.name := p_name;
320 l_rec.competence_alias := p_competence_alias;
321 l_rec.behavioural_indicator := p_behavioural_indicator;
322 l_rec.description := p_description;
323 --
324 -- Return the plsql record structure.
325 --
326 Return(l_rec);
327 --
328 End convert_args;
329 --
330 end per_cpl_shd;