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