[Home] [Help]
PACKAGE BODY: APPS.PQH_GIN_SHD
Source
1 Package Body pqh_gin_shd as
2 /* $Header: pqginrhi.pkb 115.7 2004/03/15 03:05 svorugan noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pqh_gin_shd.'; -- Global package name
9 g_debug boolean := hr_utility.debug_enabled;
10 --
11 -- ----------------------------------------------------------------------------
12 -- |---------------------------< constraint_error >---------------------------|
13 -- ----------------------------------------------------------------------------
14 Procedure constraint_error
15 (p_constraint_name in all_constraints.constraint_name%TYPE
16 ) Is
17 --
18 l_proc varchar2(72) := g_package||'constraint_error';
19 --
20 Begin
21 --
22 If (p_constraint_name = 'PQH_FR_GLOBAL_INDICES_PK') Then
23 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
24 fnd_message.set_token('PROCEDURE', l_proc);
25 fnd_message.set_token('STEP','5');
26 fnd_message.raise_error;
27 ElsIf (p_constraint_name = 'PQH_FR_GLOBAL_INDICES_U1') Then
28 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
29 fnd_message.set_token('PROCEDURE', l_proc);
30 fnd_message.set_token('STEP','10');
31 fnd_message.raise_error;
32 Else
33 fnd_message.set_name('PAY', 'HR_7877_API_INVALID_CONSTRAINT');
34 fnd_message.set_token('PROCEDURE', l_proc);
35 fnd_message.set_token('CONSTRAINT_NAME', p_constraint_name);
36 fnd_message.raise_error;
37 End If;
38 --
39 End constraint_error;
40 --
41 -- ----------------------------------------------------------------------------
42 -- |-----------------------------< api_updating >-----------------------------|
43 -- ----------------------------------------------------------------------------
44 Function api_updating
45 (p_effective_date in date
46 ,p_global_index_id in number
47 ,p_object_version_number in number
48 ) Return Boolean Is
49 --
50 -- Cursor selects the 'current' row from the HR Schema
51 --
52 Cursor C_Sel1 is
53 select
54 global_index_id
55 ,effective_start_date
56 ,effective_end_date
57 ,type_of_record
58 ,gross_index
59 ,increased_index
60 ,basic_salary_rate
61 ,housing_indemnity_rate
62 ,object_version_number
63 ,currency_code
64 from pqh_fr_global_indices_f
65 where global_index_id = p_global_index_id
66 and p_effective_date
67 between effective_start_date and effective_end_date;
68 --
69 l_fct_ret boolean;
70 --
71 Begin
72 --
73 If (p_effective_date is null or
74 p_global_index_id is null or
75 p_object_version_number is null) Then
76 --
77 -- One of the primary key arguments is null therefore we must
78 -- set the returning function value to false
79 --
80 l_fct_ret := false;
81 Else
82 If (p_global_index_id =
83 pqh_gin_shd.g_old_rec.global_index_id and
84 p_object_version_number =
85 pqh_gin_shd.g_old_rec.object_version_number
86 ) Then
87 --
88 -- The g_old_rec is current therefore we must
89 -- set the returning function to true
90 --
91 l_fct_ret := true;
92 Else
93 --
94 -- Select the current row
95 --
96 Open C_Sel1;
97 Fetch C_Sel1 Into pqh_gin_shd.g_old_rec;
98 If C_Sel1%notfound Then
99 Close C_Sel1;
100 --
101 -- The primary key is invalid therefore we must error
102 --
103 fnd_message.set_name('PAY', 'HR_7220_INVALID_PRIMARY_KEY');
104 fnd_message.raise_error;
105 End If;
106 Close C_Sel1;
107 If (p_object_version_number
108 <> pqh_gin_shd.g_old_rec.object_version_number) Then
109 fnd_message.set_name('PAY', 'HR_7155_OBJECT_INVALID');
110 fnd_message.raise_error;
111 End If;
112 l_fct_ret := true;
113 End If;
114 End If;
115 Return (l_fct_ret);
116 --
117 End api_updating;
118 --
119 -- ----------------------------------------------------------------------------
120 -- |---------------------------< find_dt_upd_modes >--------------------------|
121 -- ----------------------------------------------------------------------------
122 Procedure find_dt_upd_modes
123 (p_effective_date in date
124 ,p_base_key_value in number
125 ,p_correction out nocopy boolean
126 ,p_update out nocopy boolean
127 ,p_update_override out nocopy boolean
128 ,p_update_change_insert out nocopy boolean
129 ) is
130 --
131 l_proc varchar2(72) := g_package||'find_dt_upd_modes';
132 --
133 Begin
134
135 If g_debug then
136 --
137 hr_utility.set_location('Entering:'||l_proc, 5);
138 --
139 End if;
140
141 --
142 -- Call the corresponding datetrack api
143 --
144 dt_api.find_dt_upd_modes
145 (p_effective_date => p_effective_date
146 ,p_base_table_name => 'pqh_fr_global_indices_f'
147 ,p_base_key_column => 'global_index_id'
148 ,p_base_key_value => p_base_key_value
149 ,p_correction => p_correction
150 ,p_update => p_update
151 ,p_update_override => p_update_override
152 ,p_update_change_insert => p_update_change_insert
153 );
154 --
155 If g_debug then
156 --
157 hr_utility.set_location(' Leaving:'||l_proc, 10);
158 --
159 End if;
160
161 End find_dt_upd_modes;
162 --
163 -- ----------------------------------------------------------------------------
164 -- |---------------------------< find_dt_del_modes >--------------------------|
165 -- ----------------------------------------------------------------------------
166 Procedure find_dt_del_modes
167 (p_effective_date in date
168 ,p_base_key_value in number
169 ,p_zap out nocopy boolean
170 ,p_delete out nocopy boolean
171 ,p_future_change out nocopy boolean
172 ,p_delete_next_change out nocopy boolean
173 ) is
174 --
175 l_proc varchar2(72) := g_package||'find_dt_del_modes';
176 --
177 --
178 Begin
179 hr_utility.set_location('Entering:'||l_proc, 5);
180 --
181 -- Call the corresponding datetrack api
182 --
183 dt_api.find_dt_del_modes
184 (p_effective_date => p_effective_date
185 ,p_base_table_name => 'pqh_fr_global_indices_f'
186 ,p_base_key_column => 'global_index_id'
187 ,p_base_key_value => p_base_key_value
188 ,p_zap => p_zap
189 ,p_delete => p_delete
190 ,p_future_change => p_future_change
191 ,p_delete_next_change => p_delete_next_change
192 );
193 --
194 hr_utility.set_location(' Leaving:'||l_proc, 10);
195 End find_dt_del_modes;
196 --
197 -- ----------------------------------------------------------------------------
198 -- |-----------------------< upd_effective_end_date >-------------------------|
199 -- ----------------------------------------------------------------------------
200 Procedure upd_effective_end_date
201 (p_effective_date in date
202 ,p_base_key_value in number
203 ,p_new_effective_end_date in date
204 ,p_validation_start_date in date
205 ,p_validation_end_date in date
206 ,p_object_version_number out nocopy number
207 ) is
208 --
209 l_proc varchar2(72) := g_package||'upd_effective_end_date';
210 l_object_version_number number;
211 --
212 Begin
213 hr_utility.set_location('Entering:'||l_proc, 5);
214 --
215 -- Because we are updating a row we must get the next object
216 -- version number.
217 --
218 l_object_version_number :=
219 dt_api.get_object_version_number
220 (p_base_table_name => 'pqh_fr_global_indices_f'
221 ,p_base_key_column => 'global_index_id'
222 ,p_base_key_value => p_base_key_value
223 );
224 --
225 hr_utility.set_location(l_proc, 10);
226 --
227 --
228 -- Update the specified datetrack row setting the effective
229 -- end date to the specified new effective end date.
230 --
231 update pqh_fr_global_indices_f t
232 set t.effective_end_date = p_new_effective_end_date
233 , t.object_version_number = l_object_version_number
234 where t.global_index_id = p_base_key_value
235 and p_effective_date
236 between t.effective_start_date and t.effective_end_date;
237 --
238 --
239 p_object_version_number := l_object_version_number;
240 hr_utility.set_location(' Leaving:'||l_proc, 15);
241 --
242 End upd_effective_end_date;
243 --
244 -- ----------------------------------------------------------------------------
245 -- |---------------------------------< lck >----------------------------------|
246 -- ----------------------------------------------------------------------------
247 Procedure lck
248 (p_effective_date in date
249 ,p_datetrack_mode in varchar2
250 ,p_global_index_id in number
251 ,p_object_version_number in number
252 ,p_validation_start_date out nocopy date
253 ,p_validation_end_date out nocopy date
254 ) is
255 --
256 l_proc varchar2(72) := g_package||'lck';
257 l_validation_start_date date;
258 l_validation_end_date date;
259 l_argument varchar2(30);
260 --
261 -- Cursor C_Sel1 selects the current locked row as of session date
262 -- ensuring that the object version numbers match.
263 --
264 Cursor C_Sel1 is
265 select
266 global_index_id
267 ,effective_start_date
268 ,effective_end_date
269 ,type_of_record
270 ,gross_index
271 ,increased_index
272 ,basic_salary_rate
273 ,housing_indemnity_rate
274 ,object_version_number
275 ,currency_code
276 from pqh_fr_global_indices_f
277 where global_index_id = p_global_index_id
278 and p_effective_date
279 between effective_start_date and effective_end_date
280 for update nowait;
281 --
282 --
283 --
284 Begin
285 hr_utility.set_location('Entering:'||l_proc, 5);
286 --
287 -- Ensure that all the mandatory arguments are not null
288 --
289 hr_api.mandatory_arg_error(p_api_name => l_proc
290 ,p_argument => 'effective_date'
291 ,p_argument_value => p_effective_date
292 );
293 --
294 hr_api.mandatory_arg_error(p_api_name => l_proc
295 ,p_argument => 'datetrack_mode'
296 ,p_argument_value => p_datetrack_mode
297 );
298 --
299 hr_api.mandatory_arg_error(p_api_name => l_proc
300 ,p_argument => 'global_index_id'
301 ,p_argument_value => p_global_index_id
302 );
303 --
304 hr_api.mandatory_arg_error(p_api_name => l_proc
305 ,p_argument => 'object_version_number'
306 ,p_argument_value => p_object_version_number
307 );
308 --
309
310 Open C_Sel1;
311 Fetch C_Sel1 Into pqh_gin_shd.g_old_rec;
312 If C_Sel1%notfound then
313 Close C_Sel1;
314 --
315 -- The primary key is invalid therefore we must error
316 --
317 fnd_message.set_name('PAY', 'HR_7220_INVALID_PRIMARY_KEY');
318 fnd_message.raise_error;
319 End If;
320 Close C_Sel1;
321
322 If (pqh_gin_shd.g_old_rec.type_of_record = 'INM') then
323 --
324 hr_api.mandatory_arg_error(p_api_name => l_proc
325 ,p_argument => 'currency_code'
326 ,p_argument_value => p_object_version_number
327 );
328 --
329 End if;
330
331 --
332 -- Check to ensure the datetrack mode is not INSERT.
333 --
334 If (p_datetrack_mode <> hr_api.g_insert) then
335 --
336 -- We must select and lock the current row.
337 --
338 Open C_Sel1;
339 Fetch C_Sel1 Into pqh_gin_shd.g_old_rec;
340 If C_Sel1%notfound then
341 Close C_Sel1;
342 --
343 -- The primary key is invalid therefore we must error
344 --
345 fnd_message.set_name('PAY', 'HR_7220_INVALID_PRIMARY_KEY');
346 fnd_message.raise_error;
347 End If;
348 Close C_Sel1;
349 If (p_object_version_number
350 <> pqh_gin_shd.g_old_rec.object_version_number) Then
351 fnd_message.set_name('PAY', 'HR_7155_OBJECT_INVALID');
352 fnd_message.raise_error;
353 End If;
354 --
355 --
356 -- Validate the datetrack mode mode getting the validation start
357 -- and end dates for the specified datetrack operation.
358 --
359 dt_api.validate_dt_mode
360 (p_effective_date => p_effective_date
361 ,p_datetrack_mode => p_datetrack_mode
362 ,p_base_table_name => 'pqh_fr_global_indices_f'
363 ,p_base_key_column => 'global_index_id'
364 ,p_base_key_value => p_global_index_id
365 ,p_enforce_foreign_locking => true
366 ,p_validation_start_date => l_validation_start_date
367 ,p_validation_end_date => l_validation_end_date
368 );
369 Else
370 --
371 -- We are doing a datetrack 'INSERT' which is illegal within this
372 -- procedure therefore we must error (note: to lck on insert the
373 -- private procedure ins_lck should be called).
374 --
375 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
376 fnd_message.set_token('PROCEDURE', l_proc);
377 fnd_message.set_token('STEP','20');
378 fnd_message.raise_error;
379 End If;
380 --
381 -- Set the validation start and end date OUT arguments
382 --
383 p_validation_start_date := l_validation_start_date;
384 p_validation_end_date := l_validation_end_date;
385 --
386 hr_utility.set_location(' Leaving:'||l_proc, 30);
387 --
388 -- We need to trap the ORA LOCK exception
389 --
390 Exception
391 When HR_Api.Object_Locked then
392 --
393 -- The object is locked therefore we need to supply a meaningful
394 -- error message.
395 --
396 fnd_message.set_name('PAY', 'HR_7165_OBJECT_LOCKED');
397 fnd_message.set_token('TABLE_NAME', 'pqh_fr_global_indices_f');
398 fnd_message.raise_error;
399 End lck;
400 --
401 -- ----------------------------------------------------------------------------
402 -- |-----------------------------< convert_args >-----------------------------|
403 -- ----------------------------------------------------------------------------
404 Function convert_args
405 (p_global_index_id in number
406 ,p_effective_start_date in date
407 ,p_effective_end_date in date
408 ,p_type_of_record in varchar2
409 ,p_gross_index in number
410 ,p_increased_index in number
411 ,p_basic_salary_rate in number
412 ,p_housing_indemnity_rate in number
413 ,p_object_version_number in number
414 ,p_currency_code in varchar2
415 )
416 Return g_rec_type is
417 --
418 l_rec g_rec_type;
419 --
420 Begin
421 --
422 -- Convert arguments into local l_rec structure.
423 --
424 l_rec.global_index_id := p_global_index_id;
425 l_rec.effective_start_date := p_effective_start_date;
426 l_rec.effective_end_date := p_effective_end_date;
427 l_rec.type_of_record := p_type_of_record;
428 l_rec.gross_index := p_gross_index;
429 l_rec.increased_index := p_increased_index;
430 l_rec.basic_salary_rate := p_basic_salary_rate;
431 l_rec.housing_indemnity_rate := p_housing_indemnity_rate;
432 l_rec.object_version_number := p_object_version_number;
433 l_rec.currency_code := p_currency_code;
434 --
435 -- Return the plsql record structure.
436 --
437 Return(l_rec);
438 --
439 End convert_args;
440 --
441 end pqh_gin_shd;