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