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