1 Package Body per_qat_shd as
2 /* $Header: peqatrhi.pkb 120.0.12010000.2 2008/11/20 12:27:31 kgowripe ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' per_qat_shd.'; -- Global package name
9 --
13 Procedure constraint_error
10 -- ----------------------------------------------------------------------------
11 -- |---------------------------< constraint_error >---------------------------|
12 -- ----------------------------------------------------------------------------
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_QUALIFICATIONS_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_qualification_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 qualification_id
49 ,language
50 ,source_lang
51 ,title
52 ,group_ranking
53 ,license_restrictions
54 ,awarding_body
55 ,grade_attained
56 ,reimbursement_arrangements
57 ,training_completed_units
58 ,membership_category
59 from per_qualifications_tl
60 where qualification_id = p_qualification_id
61 and language = p_language;
62 --
63 l_fct_ret boolean;
64 --
65 Begin
66 --
67 If (p_qualification_id is null or
68 p_language is null
69 ) Then
70 --
71 -- One of the primary key arguments is null therefore we must
72 -- set the returning function value to false
73 --
74 l_fct_ret := false;
75 Else
76 If (p_qualification_id
77 = per_qat_shd.g_old_rec.qualification_id and
78 p_language
79 = per_qat_shd.g_old_rec.language
80 ) Then
81 --
82 -- The g_old_rec is current therefore we must
83 -- set the returning function to true
84 --
85 l_fct_ret := true;
86 Else
87 --
88 -- Select the current row into g_old_rec
89 --
90 Open C_Sel1;
91 Fetch C_Sel1 Into per_qat_shd.g_old_rec;
92 If C_Sel1%notfound Then
93 Close C_Sel1;
94 --
95 -- The primary key is invalid therefore we must error
96 --
97 fnd_message.set_name('PAY', 'HR_7220_INVALID_PRIMARY_KEY');
98 fnd_message.raise_error;
99 End If;
100 Close C_Sel1;
101 --
102 l_fct_ret := true;
103 End If;
104 End If;
105 Return (l_fct_ret);
106 --
107 End api_updating;
108 --
109 -- ----------------------------------------------------------------------------
110 -- |---------------------------------< lck >----------------------------------|
111 -- ----------------------------------------------------------------------------
112 Procedure lck
113 (p_qualification_id in number
114 ,p_language in varchar2
115 ) is
116 --
117 -- Cursor selects the 'current' row from the HR Schema
118 --
119 Cursor C_Sel1 is
120 select
121 qualification_id
122 ,language
123 ,source_lang
124 ,title
125 ,group_ranking
126 ,license_restrictions
127 ,awarding_body
128 ,grade_attained
129 ,reimbursement_arrangements
130 ,training_completed_units
131 ,membership_category
132 from per_qualifications_tl
133 where qualification_id = p_qualification_id
134 and language = p_language
135 for update nowait;
136 --
137 l_proc varchar2(72) := g_package||'lck';
138 --
139 Begin
140 hr_utility.set_location('Entering:'||l_proc, 5);
141 --
142 hr_api.mandatory_arg_error
143 (p_api_name => l_proc
144 ,p_argument => 'QUALIFICATION_ID'
145 ,p_argument_value => p_qualification_id
146 );
147 hr_utility.set_location(l_proc,6);
148 hr_api.mandatory_arg_error
149 (p_api_name => l_proc
150 ,p_argument => 'LANGUAGE'
151 ,p_argument_value => p_language
152 );
153 --
154 Open C_Sel1;
155 Fetch C_Sel1 Into per_qat_shd.g_old_rec;
156 If C_Sel1%notfound then
157 Close C_Sel1;
158 --
159 -- The primary key is invalid therefore we must error
160 --
161 fnd_message.set_name('PAY', 'HR_7220_INVALID_PRIMARY_KEY');
162 fnd_message.raise_error;
163 End If;
164 Close C_Sel1;
165 --
166 --
167 hr_utility.set_location(' Leaving:'||l_proc, 10);
168 --
169 -- We need to trap the ORA LOCK exception
170 --
171 Exception
172 When HR_Api.Object_Locked then
173 --
174 -- The object is locked therefore we need to supply a meaningful
175 -- error message.
176 --
177 fnd_message.set_name('PAY', 'HR_7165_OBJECT_LOCKED');
178 fnd_message.set_token('TABLE_NAME', 'per_qualifications_tl');
179 fnd_message.raise_error;
180 End lck;
181 --
182 -- ----------------------------------------------------------------------------
183 -- |----------------------------< add_language >------------------------------|
184 -- ----------------------------------------------------------------------------
185 PROCEDURE add_language IS
186 Begin
187 --
188 delete from PER_QUALIFICATIONS_TL T
189 where not exists
190 (select NULL
191 from PER_QUALIFICATIONS B
192 where B.QUALIFICATION_ID = T.QUALIFICATION_ID
193 );
194
195 update PER_QUALIFICATIONS_TL T set (
196 TITLE,
197 GROUP_RANKING,
198 LICENSE_RESTRICTIONS,
199 AWARDING_BODY,
200 GRADE_ATTAINED,
201 REIMBURSEMENT_ARRANGEMENTS,
202 TRAINING_COMPLETED_UNITS,
203 MEMBERSHIP_CATEGORY
204 ) = (select
205 B.TITLE,
206 B.GROUP_RANKING,
207 B.LICENSE_RESTRICTIONS,
208 B.AWARDING_BODY,
209 B.GRADE_ATTAINED,
210 B.REIMBURSEMENT_ARRANGEMENTS,
211 B.TRAINING_COMPLETED_UNITS,
212 B.MEMBERSHIP_CATEGORY
213 from PER_QUALIFICATIONS_TL B
214 where B.QUALIFICATION_ID = T.QUALIFICATION_ID
215 and B.LANGUAGE = T.SOURCE_LANG)
216 where (
217 T.QUALIFICATION_ID,
218 T.LANGUAGE
219 ) in (select
220 SUBT.QUALIFICATION_ID,
221 SUBT.LANGUAGE
222 from PER_QUALIFICATIONS_TL SUBB, PER_QUALIFICATIONS_TL SUBT
223 where SUBB.QUALIFICATION_ID = SUBT.QUALIFICATION_ID
224 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
225 and (SUBB.GROUP_RANKING <> SUBT.GROUP_RANKING
226 or (SUBB.GROUP_RANKING is null and SUBT.GROUP_RANKING is not null)
227 or (SUBB.GROUP_RANKING is not null and SUBT.GROUP_RANKING is null)
228 or SUBB.TITLE <> SUBT.TITLE
229 or (SUBB.TITLE is null and SUBT.TITLE is not null)
230 or (SUBB.TITLE is not null and SUBT.TITLE is null)
231 or SUBB.LICENSE_RESTRICTIONS <> SUBT.LICENSE_RESTRICTIONS
232 or (SUBB.LICENSE_RESTRICTIONS is null and SUBT.LICENSE_RESTRICTIONS is not null)
233 or (SUBB.LICENSE_RESTRICTIONS is not null and SUBT.LICENSE_RESTRICTIONS is null)
234 or SUBB.AWARDING_BODY <> SUBT.AWARDING_BODY
235 or (SUBB.AWARDING_BODY is null and SUBT.AWARDING_BODY is not null)
236 or (SUBB.AWARDING_BODY is not null and SUBT.AWARDING_BODY is null)
237 or SUBB.GRADE_ATTAINED <> SUBT.GRADE_ATTAINED
238 or (SUBB.GRADE_ATTAINED is null and SUBT.GRADE_ATTAINED is not null)
239 or (SUBB.GRADE_ATTAINED is not null and SUBT.GRADE_ATTAINED is null)
240 or SUBB.REIMBURSEMENT_ARRANGEMENTS <> SUBT.REIMBURSEMENT_ARRANGEMENTS
241 or (SUBB.REIMBURSEMENT_ARRANGEMENTS is null and SUBT.REIMBURSEMENT_ARRANGEMENTS is not null)
242 or (SUBB.REIMBURSEMENT_ARRANGEMENTS is not null and SUBT.REIMBURSEMENT_ARRANGEMENTS is null)
243 or SUBB.TRAINING_COMPLETED_UNITS <> SUBT.TRAINING_COMPLETED_UNITS
244 or (SUBB.TRAINING_COMPLETED_UNITS is null and SUBT.TRAINING_COMPLETED_UNITS is not null)
245 or (SUBB.TRAINING_COMPLETED_UNITS is not null and SUBT.TRAINING_COMPLETED_UNITS is null)
246 or SUBB.MEMBERSHIP_CATEGORY <> SUBT.MEMBERSHIP_CATEGORY
247 or (SUBB.MEMBERSHIP_CATEGORY is null and SUBT.MEMBERSHIP_CATEGORY is not null)
248 or (SUBB.MEMBERSHIP_CATEGORY is not null and SUBT.MEMBERSHIP_CATEGORY is null)
249 ));
250
251 insert into PER_QUALIFICATIONS_TL (
252 QUALIFICATION_ID,
253 TITLE,
254 GROUP_RANKING,
255 LICENSE_RESTRICTIONS,
256 AWARDING_BODY,
257 GRADE_ATTAINED,
258 REIMBURSEMENT_ARRANGEMENTS,
259 TRAINING_COMPLETED_UNITS,
260 MEMBERSHIP_CATEGORY,
261 CREATED_BY,
262 CREATION_DATE,
263 LAST_UPDATED_BY,
264 LAST_UPDATE_DATE,
265 LAST_UPDATE_LOGIN,
266 LANGUAGE,
267 SOURCE_LANG
268 ) select
269 B.QUALIFICATION_ID,
270 B.TITLE,
271 B.GROUP_RANKING,
272 B.LICENSE_RESTRICTIONS,
273 B.AWARDING_BODY,
274 B.GRADE_ATTAINED,
275 B.REIMBURSEMENT_ARRANGEMENTS,
276 B.TRAINING_COMPLETED_UNITS,
277 B.MEMBERSHIP_CATEGORY,
278 B.CREATED_BY,
279 B.CREATION_DATE,
280 B.LAST_UPDATED_BY,
281 B.LAST_UPDATE_DATE,
282 B.LAST_UPDATE_LOGIN,
283 L.LANGUAGE_CODE,
284 B.SOURCE_LANG
285 from PER_QUALIFICATIONS_TL B, FND_LANGUAGES L
286 where L.INSTALLED_FLAG in ('I', 'B')
287 and B.LANGUAGE = userenv('LANG')
288 and not exists
289 (select NULL
290 from PER_QUALIFICATIONS_TL T
291 where T.QUALIFICATION_ID = B.QUALIFICATION_ID
292 and T.LANGUAGE = L.LANGUAGE_CODE);
293 --
294 End;
295 --
296 -- ----------------------------------------------------------------------------
297 -- |-----------------------------< convert_args >-----------------------------|
298 -- ----------------------------------------------------------------------------
299 Function convert_args
300 (p_qualification_id in number
301 ,p_language in varchar2
302 ,p_source_lang in varchar2
303 ,p_title in varchar2
304 ,p_group_ranking in varchar2
305 ,p_license_restrictions in varchar2
306 ,p_awarding_body in varchar2
307 ,p_grade_attained in varchar2
308 ,p_reimbursement_arrangements in varchar2
309 ,p_training_completed_units in varchar2
310 ,p_membership_category in varchar2
311 )
312 Return g_rec_type is
313 --
314 l_rec g_rec_type;
315 --
316 Begin
317 --
318 -- Convert arguments into local l_rec structure.
319 --
320 l_rec.qualification_id := p_qualification_id;
321 l_rec.language := p_language;
322 l_rec.source_lang := p_source_lang;
323 l_rec.title := p_title;
324 l_rec.group_ranking := p_group_ranking;
325 l_rec.license_restrictions := p_license_restrictions;
326 l_rec.awarding_body := p_awarding_body;
327 l_rec.grade_attained := p_grade_attained;
328 l_rec.reimbursement_arrangements := p_reimbursement_arrangements;
329 l_rec.training_completed_units := p_training_completed_units;
330 l_rec.membership_category := p_membership_category;
331 --
332 -- Return the plsql record structure.
333 --
334 Return(l_rec);
335 --
336 End convert_args;
337 --
338 end per_qat_shd;
[Home] [Help]