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