1 Package Body ben_ctk_ins as
2 /* $Header: bectkrhi.pkb 120.0 2005/05/28 01:25 appldev noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ben_ctk_ins.'; -- Global package name
9 g_debug boolean := hr_utility.debug_enabled;
10 --
11 -- The following global variables are only to be used by
12 -- the set_base_key_value and pre_insert procedures.
13 --
14 g_group_per_in_ler_id_i number default null;
15 g_task_id_i number default null;
16 --
17 -- ----------------------------------------------------------------------------
18 -- |------------------------< set_base_key_value >----------------------------|
19 -- ----------------------------------------------------------------------------
20 procedure set_base_key_value
21 (p_group_per_in_ler_id in number
22 ,p_task_id in number) is
23 --
24 l_proc varchar2(72) := g_package||'set_base_key_value';
25 --
26 Begin
27 if g_debug then
28 hr_utility.set_location('Entering:'||l_proc, 10);
29 end if;
30 --
31 ben_ctk_ins.g_group_per_in_ler_id_i := p_group_per_in_ler_id;
32 ben_ctk_ins.g_task_id_i := p_task_id;
33 --
34 if g_debug then
35 hr_utility.set_location(' Leaving:'||l_proc, 20);
36 end if;
37 End set_base_key_value;
38 --
39 --
40 -- ----------------------------------------------------------------------------
41 -- |------------------------------< insert_dml >------------------------------|
42 -- ----------------------------------------------------------------------------
43 -- {Start Of Comments}
44 --
45 -- Description:
46 -- This procedure controls the actual dml insert logic. The processing of
47 -- this procedure are as follows:
48 -- 1) Initialise the object_version_number to 1 if the object_version_number
49 -- is defined as an attribute for this entity.
50 -- 2) To set and unset the g_api_dml status as required (as we are about to
51 -- perform dml).
52 -- 3) To insert the row into the schema.
53 -- 4) To trap any constraint violations that may have occurred.
54 -- 5) To raise any other errors.
55 --
56 -- Prerequisites:
57 -- This is an internal private procedure which must be called from the ins
58 -- procedure and must have all mandatory attributes set (except the
59 -- object_version_number which is initialised within this procedure).
60 --
61 -- In Parameters:
62 -- A Pl/Sql record structre.
63 --
64 -- Post Success:
65 -- The specified row will be inserted into the schema.
66 --
67 -- Post Failure:
68 -- On the insert dml failure it is important to note that we always reset the
69 -- g_api_dml status to false.
70 -- If a check, unique or parent integrity constraint violation is raised the
71 -- constraint_error procedure will be called.
72 -- If any other error is reported, the error will be raised after the
73 -- g_api_dml status is reset.
74 --
75 -- Developer Implementation Notes:
76 -- None.
77 --
78 -- Access Status:
79 -- Internal Row Handler Use Only.
80 --
81 -- {End Of Comments}
82 -- ----------------------------------------------------------------------------
83 Procedure insert_dml
84 (p_rec in out nocopy ben_ctk_shd.g_rec_type
85 ) is
86 --
87 l_proc varchar2(72) := g_package||'insert_dml';
88 --
89 Begin
90 if g_debug then
91 hr_utility.set_location('Entering:'||l_proc, 5);
92 end if;
93 p_rec.object_version_number := 1; -- Initialise the object version
94 --
95 ben_ctk_shd.g_api_dml := true; -- Set the api dml status
96 --
97 -- Insert the row into: ben_cwb_person_tasks
98 --
99 insert into ben_cwb_person_tasks
100 (group_per_in_ler_id
101 ,task_id
102 ,group_pl_id
103 ,lf_evt_ocrd_dt
104 ,status_cd
105 ,access_cd
106 ,task_last_update_date
107 ,task_last_update_by
108 ,object_version_number
109 )
110 Values
111 (p_rec.group_per_in_ler_id
112 ,p_rec.task_id
113 ,p_rec.group_pl_id
114 ,p_rec.lf_evt_ocrd_dt
115 ,p_rec.status_cd
116 ,p_rec.access_cd
117 ,p_rec.task_last_update_date
118 ,p_rec.task_last_update_by
119 ,p_rec.object_version_number
120 );
121 --
122 ben_ctk_shd.g_api_dml := false; -- Unset the api dml status
123 --
124 if g_debug then
125 hr_utility.set_location(' Leaving:'||l_proc, 10);
126 end if;
127 Exception
128 When hr_api.check_integrity_violated Then
129 -- A check constraint has been violated
130 ben_ctk_shd.g_api_dml := false; -- Unset the api dml status
131 ben_ctk_shd.constraint_error
132 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
133 When hr_api.parent_integrity_violated Then
134 -- Parent integrity has been violated
135 ben_ctk_shd.g_api_dml := false; -- Unset the api dml status
136 ben_ctk_shd.constraint_error
137 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
138 When hr_api.unique_integrity_violated Then
139 -- Unique integrity has been violated
140 ben_ctk_shd.g_api_dml := false; -- Unset the api dml status
141 ben_ctk_shd.constraint_error
142 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
143 When Others Then
144 ben_ctk_shd.g_api_dml := false; -- Unset the api dml status
145 Raise;
146 End insert_dml;
147 --
148 -- ----------------------------------------------------------------------------
149 -- |------------------------------< pre_insert >------------------------------|
150 -- ----------------------------------------------------------------------------
151 -- {Start Of Comments}
152 --
153 -- Description:
154 -- This private procedure contains any processing which is required before
155 -- the insert dml. Presently, if the entity has a corresponding primary
156 -- key which is maintained by an associating sequence, the primary key for
157 -- the entity will be populated with the next sequence value in
158 -- preparation for the insert dml.
159 --
160 -- Prerequisites:
161 -- This is an internal procedure which is called from the ins procedure.
162 --
163 -- In Parameters:
164 -- A Pl/Sql record structure.
165 --
166 -- Post Success:
167 -- Processing continues.
168 --
169 -- Post Failure:
170 -- If an error has occurred, an error message and exception will be raised
171 -- but not handled.
172 --
173 -- Developer Implementation Notes:
174 -- Any pre-processing required before the insert dml is issued should be
175 -- coded within this procedure. As stated above, a good example is the
176 -- generation of a primary key number via a corresponding sequence.
177 -- It is important to note that any 3rd party maintenance should be reviewed
178 -- before placing in this procedure.
179 --
180 -- Access Status:
181 -- Internal Row Handler Use Only.
182 --
183 -- {End Of Comments}
184 -- ----------------------------------------------------------------------------
185 Procedure pre_insert
186 (p_rec in out nocopy ben_ctk_shd.g_rec_type
187 ) is
188 --
189 Cursor C_Sel2 is
190 Select null
191 from ben_cwb_person_tasks
192 where group_per_in_ler_id =
193 ben_ctk_ins.g_group_per_in_ler_id_i
194 or task_id =
195 ben_ctk_ins.g_task_id_i;
196 --
197 l_proc varchar2(72) := g_package||'pre_insert';
198 l_exists varchar2(1);
199 --
200 Begin
201 if g_debug then
202 hr_utility.set_location('Entering:'||l_proc, 5);
203 end if;
204 --
205 If (ben_ctk_ins.g_group_per_in_ler_id_i is not null or
206 ben_ctk_ins.g_task_id_i is not null) Then
207 --
208 -- Verify registered primary key values not already in use
209 --
210 Open C_Sel2;
211 Fetch C_Sel2 into l_exists;
212 If C_Sel2%found Then
213 Close C_Sel2;
214 --
215 -- The primary key values are already in use.
216 --
217 fnd_message.set_name('PER','PER_289391_KEY_ALREADY_USED');
218 fnd_message.set_token('TABLE_NAME','ben_cwb_person_tasks');
219 fnd_message.raise_error;
220 End If;
221 Close C_Sel2;
222 --
223 -- Use registered key values and clear globals
224 --
225 p_rec.group_per_in_ler_id :=
226 ben_ctk_ins.g_group_per_in_ler_id_i;
227 ben_ctk_ins.g_group_per_in_ler_id_i := null;
228 p_rec.task_id :=
229 ben_ctk_ins.g_task_id_i;
230 ben_ctk_ins.g_task_id_i := null;
231 Else
232 null;
233 /* --
234 -- No registerd key values, so select the next sequence number
235 --
236 --
237 -- Select the next sequence number
238 --
239 Open C_Sel1;
240 Fetch C_Sel1 Into p_rec.task_id;
241 Close C_Sel1; */
242 End If;
243 --
244 if g_debug then
245 hr_utility.set_location(' Leaving:'||l_proc, 10);
246 end if;
247 End pre_insert;
248 --
249 -- ----------------------------------------------------------------------------
250 -- |-----------------------------< post_insert >------------------------------|
251 -- ----------------------------------------------------------------------------
252 -- {Start Of Comments}
253 --
254 -- Description:
255 -- This private procedure contains any processing which is required after
256 -- the insert dml.
257 --
258 -- Prerequisites:
259 -- This is an internal procedure which is called from the ins procedure.
260 --
261 -- In Parameters:
262 -- A Pl/Sql record structre.
263 --
264 -- Post Success:
265 -- Processing continues.
266 --
267 -- Post Failure:
268 -- If an error has occurred, an error message and exception will be raised
269 -- but not handled.
270 --
271 -- Developer Implementation Notes:
272 -- Any post-processing required after the insert dml is issued should be
273 -- coded within this procedure. It is important to note that any 3rd party
274 -- maintenance should be reviewed before placing in this procedure.
275 --
276 -- Access Status:
277 -- Internal Row Handler Use Only.
278 --
279 -- {End Of Comments}
280 -- ----------------------------------------------------------------------------
281 Procedure post_insert
282 (p_rec in ben_ctk_shd.g_rec_type
283 ) is
284 --
285 l_proc varchar2(72) := g_package||'post_insert';
286 --
287 Begin
288 if g_debug then
289 hr_utility.set_location('Entering:'||l_proc, 5);
290 end if;
291 begin
292 --
293 ben_ctk_rki.after_insert
294 (p_group_per_in_ler_id
295 => p_rec.group_per_in_ler_id
296 ,p_task_id
297 => p_rec.task_id
298 ,p_group_pl_id
299 => p_rec.group_pl_id
300 ,p_lf_evt_ocrd_dt
301 => p_rec.lf_evt_ocrd_dt
302 ,p_status_cd
303 => p_rec.status_cd
304 ,p_access_cd
305 => p_rec.access_cd
306 ,p_task_last_update_date
307 => p_rec.task_last_update_date
308 ,p_task_last_update_by
309 => p_rec.task_last_update_by
310 ,p_object_version_number
311 => p_rec.object_version_number
312 );
313 --
314 exception
315 --
316 when hr_api.cannot_find_prog_unit then
317 --
318 hr_api.cannot_find_prog_unit_error
319 (p_module_name => 'BEN_CWB_PERSON_TASKS'
320 ,p_hook_type => 'AI');
321 --
322 end;
323 --
324 if g_debug then
325 hr_utility.set_location(' Leaving:'||l_proc, 10);
326 end if;
327 End post_insert;
328 --
329 -- ----------------------------------------------------------------------------
330 -- |---------------------------------< ins >----------------------------------|
331 -- ----------------------------------------------------------------------------
332 Procedure ins
333 (p_rec in out nocopy ben_ctk_shd.g_rec_type
334 ) is
335 --
336 l_proc varchar2(72) := g_package||'ins';
337 --
338 Begin
339 if g_debug then
340 hr_utility.set_location('Entering:'||l_proc, 5);
341 end if;
342 --
343 -- Call the supporting insert validate operations
344 --
345 ben_ctk_bus.insert_validate
346 (p_rec
347 );
348 --
349 -- Call to raise any errors on multi-message list
350 hr_multi_message.end_validation_set;
351 --
352 -- Call the supporting pre-insert operation
353 --
354 ben_ctk_ins.pre_insert(p_rec);
355 --
356 -- Insert the row
357 --
358 ben_ctk_ins.insert_dml(p_rec);
359 --
360 -- Call the supporting post-insert operation
361 --
362 ben_ctk_ins.post_insert
363 (p_rec
364 );
365 --
366 -- Call to raise any errors on multi-message list
367 hr_multi_message.end_validation_set;
368 --
369 if g_debug then
370 hr_utility.set_location('Leaving:'||l_proc, 20);
371 end if;
372 end ins;
373 --
374 -- ----------------------------------------------------------------------------
375 -- |---------------------------------< ins >----------------------------------|
376 -- ----------------------------------------------------------------------------
377 Procedure ins
378 (p_group_per_in_ler_id in number
379 ,p_task_id in number
380 ,p_group_pl_id in number
381 ,p_lf_evt_ocrd_dt in date
382 ,p_status_cd in varchar2 default null
383 ,p_access_cd in varchar2 default null
384 ,p_task_last_update_date in date default null
385 ,p_task_last_update_by in number default null
386 ,p_object_version_number out nocopy number
387 ) is
388 --
389 l_rec ben_ctk_shd.g_rec_type;
390 l_proc varchar2(72) := g_package||'ins';
391 --
392 Begin
393 if g_debug then
394 hr_utility.set_location('Entering:'||l_proc, 5);
395 end if;
396 --
397 -- Call conversion function to turn arguments into the
398 -- p_rec structure.
399 --
400 l_rec :=
401 ben_ctk_shd.convert_args
402 (p_group_per_in_ler_id
403 ,p_task_id
404 ,p_group_pl_id
405 ,p_lf_evt_ocrd_dt
406 ,p_status_cd
407 ,p_access_cd
408 ,p_task_last_update_date
409 ,p_task_last_update_by
410 ,null
411 );
412 --
413 -- Having converted the arguments into the ben_ctk_rec
414 -- plsql record structure we call the corresponding record business process.
415 --
416 ben_ctk_ins.ins
417 (l_rec
418 );
419 --
420 --
421 p_object_version_number := l_rec.object_version_number;
422 --
423 if g_debug then
424 hr_utility.set_location(' Leaving:'||l_proc, 10);
425 end if;
426 End ins;
427 --
428 end ben_ctk_ins;