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