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