1 Package Body per_pss_ins as
2 /* $Header: pepssrhi.pkb 120.1 2006/08/08 11:27:06 amigarg noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' per_pss_ins.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------------------------------< insert_dml >------------------------------|
12 -- ----------------------------------------------------------------------------
13 -- {Start Of Comments}
14 --
15 -- Description:
16 -- This procedure controls the actual dml insert logic. The processing of
17 -- this procedure are as follows:
18 -- 1) Initialise the object_version_number to 1 if the object_version_number
19 -- is defined as an attribute for this entity.
20 -- 2) To set and unset the g_api_dml status as required (as we are about to
21 -- perform dml).
22 -- 3) To insert the row into the schema.
23 -- 4) To trap any constraint violations that may have occurred.
24 -- 5) To raise any other errors.
25 --
26 -- Prerequisites:
27 -- This is an internal private procedure which must be called from the ins
28 -- procedure and must have all mandatory attributes set (except the
29 -- object_version_number which is initialised within this procedure).
30 --
31 -- In Parameters:
32 -- A Pl/Sql record structre.
33 --
34 -- Post Success:
35 -- The specified row will be inserted into the schema.
36 --
37 -- Post Failure:
38 -- On the insert dml failure it is important to note that we always reset the
39 -- g_api_dml status to false.
40 -- If a check, unique or parent integrity constraint violation is raised the
41 -- constraint_error procedure will be called.
42 -- If any other error is reported, the error will be raised after the
43 -- g_api_dml status is reset.
44 --
45 -- Developer Implementation Notes:
46 -- None.
47 --
48 -- Access Status:
49 -- Internal Row Handler Use Only.
50 --
51 -- {End Of Comments}
52 -- ----------------------------------------------------------------------------
53 Procedure insert_dml(p_rec in out nocopy per_pss_shd.g_rec_type) is
54 --
55 l_proc varchar2(72) := g_package||'insert_dml';
56 --
57 Begin
58 hr_utility.set_location('Entering:'||l_proc, 5);
59 p_rec.object_version_number := 1; -- Initialise the object version
60 --
61 --
62 -- Insert the row into: per_salary_surveys
63 --
64 insert into per_salary_surveys
65 ( salary_survey_id,
66 object_version_number,
67 survey_name,
68 survey_company_code,
69 identifier,
70 --ras currency_code,
71 survey_type_code,
72 base_region,
73 attribute_category,
74 attribute1,
75 attribute2,
76 attribute3,
77 attribute4,
78 attribute5,
79 attribute6,
80 attribute7,
81 attribute8,
82 attribute9,
83 attribute10,
84 attribute11,
85 attribute12,
86 attribute13,
87 attribute14,
88 attribute15,
89 attribute16,
90 attribute17,
91 attribute18,
92 attribute19,
93 attribute20
94 )
95 Values
96 ( p_rec.salary_survey_id,
97 p_rec.object_version_number,
98 p_rec.survey_name,
99 p_rec.survey_company_code,
100 p_rec.identifier,
101 --ras p_rec.currency_code,
102 p_rec.survey_type_code,
103 p_rec.base_region,
104 p_rec.attribute_category,
105 p_rec.attribute1,
106 p_rec.attribute2,
107 p_rec.attribute3,
108 p_rec.attribute4,
109 p_rec.attribute5,
110 p_rec.attribute6,
111 p_rec.attribute7,
112 p_rec.attribute8,
113 p_rec.attribute9,
114 p_rec.attribute10,
115 p_rec.attribute11,
116 p_rec.attribute12,
117 p_rec.attribute13,
118 p_rec.attribute14,
119 p_rec.attribute15,
120 p_rec.attribute16,
121 p_rec.attribute17,
122 p_rec.attribute18,
123 p_rec.attribute19,
124 p_rec.attribute20
125 );
126 --
127 hr_utility.set_location(' Leaving:'||l_proc, 10);
128 Exception
129 When hr_api.check_integrity_violated Then
130 -- A check constraint has been violated
131 per_pss_shd.constraint_error
135 per_pss_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
136 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
137 When hr_api.unique_integrity_violated Then
138 -- Unique integrity has been violated
139 per_pss_shd.constraint_error
140 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
141 When Others Then
142 Raise;
143 End insert_dml;
144 --
145 -- ----------------------------------------------------------------------------
146 -- |------------------------------< pre_insert >------------------------------|
147 -- ----------------------------------------------------------------------------
148 -- {Start Of Comments}
149 --
150 -- Description:
151 -- This private procedure contains any processing which is required before
152 -- the insert dml. Presently, if the entity has a corresponding primary
153 -- key which is maintained by an associating sequence, the primary key for
154 -- the entity will be populated with the next sequence value in
155 -- preparation for the insert dml.
156 --
157 -- Prerequisites:
158 -- This is an internal procedure which is called from the ins procedure.
159 --
160 -- In Parameters:
161 -- A Pl/Sql record structure.
162 --
163 -- Post Success:
164 -- Processing continues.
165 --
166 -- Post Failure:
167 -- If an error has occurred, an error message and exception will be raised
168 -- but not handled.
169 --
170 -- Developer Implementation Notes:
171 -- Any pre-processing required before the insert dml is issued should be
172 -- coded within this procedure. As stated above, a good example is the
173 -- generation of a primary key number via a corresponding sequence.
174 -- It is important to note that any 3rd party maintenance should be reviewed
175 -- before placing in this procedure.
176 --
177 -- Access Status:
178 -- Internal Row Handler Use Only.
179 --
180 -- {End Of Comments}
181 -- ----------------------------------------------------------------------------
182 Procedure pre_insert(p_rec in out nocopy per_pss_shd.g_rec_type) is
183 --
184 l_proc varchar2(72) := g_package||'pre_insert';
185 --
186 Cursor C_Sel1 is select per_salary_surveys_s.nextval from sys.dual;
187 --
188 Begin
189 hr_utility.set_location('Entering:'||l_proc, 5);
190 --
191 --
192 -- Select the next sequence number
193 --
194 Open C_Sel1;
195 Fetch C_Sel1 Into p_rec.salary_survey_id;
196 Close C_Sel1;
197 --
198 hr_utility.set_location(' Leaving:'||l_proc, 10);
199 End pre_insert;
200 --
201 -- ----------------------------------------------------------------------------
202 -- |-----------------------------< post_insert >------------------------------|
203 -- ----------------------------------------------------------------------------
204 -- {Start Of Comments}
205 --
206 -- Description:
207 -- This private procedure contains any processing which is required after the
208 -- insert dml.
209 --
210 -- Prerequisites:
211 -- This is an internal procedure which is called from the ins procedure.
212 --
213 -- In Parameters:
214 -- A Pl/Sql record structre.
215 --
216 -- Post Success:
217 -- Processing continues.
218 --
219 -- Post Failure:
220 -- If an error has occurred, an error message and exception will be raised
221 -- but not handled.
222 --
223 -- Developer Implementation Notes:
224 -- Any post-processing required after the insert dml is issued should be
225 -- coded within this procedure. It is important to note that any 3rd party
226 -- maintenance should be reviewed before placing in this procedure.
227 --
228 -- Access Status:
229 -- Internal Row Handler Use Only.
230 --
231 -- {End Of Comments}
232 -- ----------------------------------------------------------------------------
233 Procedure post_insert(p_rec in per_pss_shd.g_rec_type,
234 p_effective_date in date) is
235 --
236 l_proc varchar2(72) := g_package||'post_insert';
237 --
238 Begin
239 hr_utility.set_location('Entering:'||l_proc, 5);
240 --
241 begin
242 per_pss_rki.after_insert
243 (p_salary_survey_id
244 => p_rec.salary_survey_id,
245 p_object_version_number
246 => p_rec.object_version_number,
247 p_survey_name
248 => p_rec.survey_name,
249 p_survey_company_code
250 => p_rec.survey_company_code,
251 p_identifier
252 => p_rec.identifier,
253 --ras p_currency_code
254 --ras => p_rec.currency_code,
255 p_survey_type_code
256 => p_rec.survey_type_code,
257 p_base_region
258 => p_rec.base_region,
259 p_effective_date
260 => p_effective_date,
261 p_attribute_category
262 => p_rec.attribute_category,
263 p_attribute1
264 => p_rec.attribute1,
265 p_attribute2
266 => p_rec.attribute2,
267 p_attribute3
268 => p_rec.attribute3,
269 p_attribute4
270 => p_rec.attribute4,
271 p_attribute5
272 => p_rec.attribute5,
273 p_attribute6
274 => p_rec.attribute6,
275 p_attribute7
276 => p_rec.attribute7,
277 p_attribute8
278 => p_rec.attribute8,
279 p_attribute9
280 => p_rec.attribute9,
284 => p_rec.attribute11,
281 p_attribute10
282 => p_rec.attribute10,
283 p_attribute11
285 p_attribute12
286 => p_rec.attribute12,
287 p_attribute13
288 => p_rec.attribute13,
289 p_attribute14
290 => p_rec.attribute14,
291 p_attribute15
292 => p_rec.attribute15,
293 p_attribute16
294 => p_rec.attribute16,
295 p_attribute17
296 => p_rec.attribute17,
297 p_attribute18
298 => p_rec.attribute18,
299 p_attribute19
300 => p_rec.attribute19,
301 p_attribute20
302 => p_rec.attribute20
303 );
304
305 exception
306 when hr_api.cannot_find_prog_unit then
307 hr_api.cannot_find_prog_unit_error
308 (p_module_name => 'PER_SALARY_SURVEYS'
309 ,p_hook_type => 'AI'
310 );
311 end;
312 --
313 hr_utility.set_location(' Leaving:'||l_proc, 10);
314 --
315 End post_insert;
316 --
317 -- ----------------------------------------------------------------------------
318 -- |---------------------------------< ins >----------------------------------|
319 -- ----------------------------------------------------------------------------
320 Procedure ins
321 (
322 p_rec in out nocopy per_pss_shd.g_rec_type,
323 p_effective_date in date
324 ) is
325 --
326 l_proc varchar2(72) := g_package||'ins';
327 --
328 Begin
329 hr_utility.set_location('Entering:'||l_proc, 5);
330 --
331 -- Call the supporting insert validate operations
332 --
333 per_pss_bus.insert_validate(p_rec, p_effective_date);
334 --
335 -- Call the supporting pre-insert operation
336 --
337 pre_insert(p_rec);
338 --
339 -- Insert the row
340 --
341 insert_dml(p_rec);
342 --
343 -- Call the supporting post-insert operation
344 --
345 post_insert(p_rec,p_effective_date);
346 end ins;
347 --
348 -- ----------------------------------------------------------------------------
349 -- |---------------------------------< ins >----------------------------------|
350 -- ----------------------------------------------------------------------------
351 Procedure ins
352 (
353 p_salary_survey_id out nocopy number,
354 p_object_version_number out nocopy number,
355 p_survey_name in varchar2,
356 p_survey_company_code in varchar2,
357 p_identifier in varchar2,
358 --ras p_currency_code in varchar2,
359 p_survey_type_code in varchar2,
360 p_base_region in varchar2 default null,
361 p_attribute_category in varchar2 default null,
362 p_attribute1 in varchar2 default null,
363 p_attribute2 in varchar2 default null,
364 p_attribute3 in varchar2 default null,
365 p_attribute4 in varchar2 default null,
366 p_attribute5 in varchar2 default null,
367 p_attribute6 in varchar2 default null,
368 p_attribute7 in varchar2 default null,
369 p_attribute8 in varchar2 default null,
370 p_attribute9 in varchar2 default null,
371 p_attribute10 in varchar2 default null,
372 p_attribute11 in varchar2 default null,
373 p_attribute12 in varchar2 default null,
374 p_attribute13 in varchar2 default null,
375 p_attribute14 in varchar2 default null,
376 p_attribute15 in varchar2 default null,
377 p_attribute16 in varchar2 default null,
378 p_attribute17 in varchar2 default null,
379 p_attribute18 in varchar2 default null,
380 p_attribute19 in varchar2 default null,
381 p_attribute20 in varchar2 default null,
382 p_effective_date in date default null
383 ) is
384 --
385 l_rec per_pss_shd.g_rec_type;
386 l_proc varchar2(72) := g_package||'ins';
387 --
388 Begin
389 hr_utility.set_location('Entering:'||l_proc, 5);
390 --
391 -- Call conversion function to turn arguments into the
392 -- p_rec structure.
393 --
394 l_rec :=
395 per_pss_shd.convert_args
396 (
397 null,
398 null,
399 p_survey_name,
400 p_survey_company_code,
401 p_identifier,
402 --ras p_currency_code,
403 p_survey_type_code,
404 p_base_region,
405 p_attribute_category,
406 p_attribute1,
407 p_attribute2,
408 p_attribute3,
409 p_attribute4,
410 p_attribute5,
411 p_attribute6,
412 p_attribute7,
413 p_attribute8,
414 p_attribute9,
415 p_attribute10,
416 p_attribute11,
417 p_attribute12,
418 p_attribute13,
419 p_attribute14,
420 p_attribute15,
421 p_attribute16,
422 p_attribute17,
423 p_attribute18,
424 p_attribute19,
425 p_attribute20
426 );
427 --
428 -- Having converted the arguments into the per_pss_rec
429 -- plsql record structure we call the corresponding record business process.
430 --
431 ins(l_rec,p_effective_date);
432 --
433 -- As the primary key argument(s)
434 -- are specified as an OUT's we must set these values.
435 --
436 p_salary_survey_id := l_rec.salary_survey_id;
437 p_object_version_number := l_rec.object_version_number;
438 --
439 hr_utility.set_location(' Leaving:'||l_proc, 10);
440 End ins;
441 --
442 end per_pss_ins;