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