1 Package Body ssp_ern_ins as
2 /* $Header: spernrhi.pkb 120.5.12010000.2 2008/08/13 13:25:38 ubhat ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ssp_ern_ins.'; -- Global package name
9 g_payment_period_func_status varchar2(3);
10 --
11 -- ----------------------------------------------------------------------------
12 -- |------------------------------< insert_dml >------------------------------|
13 -- ----------------------------------------------------------------------------
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 -- Pre Conditions:
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 Table Handler Use Only.
50 --
51 -- ----------------------------------------------------------------------------
52 Procedure insert_dml(p_rec in out nocopy ssp_ern_shd.g_rec_type) is
53 --
54 l_proc varchar2(72) := g_package||'insert_dml';
55 --
56 Begin
57 hr_utility.set_location('Entering:'||l_proc, 1);
58 p_rec.object_version_number := 1; -- Initialise the object version
59 --
60 ssp_ern_shd.g_api_dml := true; -- Set the api dml status
61 --
62 -- Insert the row into: ssp_earnings_calculations
63 --
64 insert into ssp_earnings_calculations
65 ( earnings_calculations_id,
66 object_version_number,
67 person_id,
68 effective_date,
69 average_earnings_amount,
70 user_entered,
71 payment_periods
72 )
73 Values
74 ( p_rec.earnings_calculations_id,
75 p_rec.object_version_number,
76 p_rec.person_id,
77 p_rec.effective_date,
78 p_rec.average_earnings_amount,
79 p_rec.user_entered,
80 p_rec.payment_periods
81 );
82 --
83 ssp_ern_shd.g_api_dml := false; -- Unset the api dml status
84 --
85 hr_utility.set_location(' Leaving:'||l_proc, 100);
86 Exception
87 When hr_api.check_integrity_violated Then
88 -- A check constraint has been violated
89 ssp_ern_shd.g_api_dml := false; -- Unset the api dml status
90 ssp_ern_shd.constraint_error
91 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
92 When hr_api.parent_integrity_violated Then
93 -- Parent integrity has been violated
94 ssp_ern_shd.g_api_dml := false; -- Unset the api dml status
95 ssp_ern_shd.constraint_error
96 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
97 When hr_api.unique_integrity_violated Then
98 -- Unique integrity has been violated
99 ssp_ern_shd.g_api_dml := false; -- Unset the api dml status
100 ssp_ern_shd.constraint_error
101 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
102 When Others Then
103 ssp_ern_shd.g_api_dml := false; -- Unset the api dml status
104 Raise;
105 End insert_dml;
106 --
107 -- ----------------------------------------------------------------------------
108 -- |------------------------------< pre_insert >------------------------------|
109 -- ----------------------------------------------------------------------------
110 --
111 -- Description:
112 -- This private procedure contains any processing which is required before
113 -- the insert dml. Presently, if the entity has a corresponding primary
114 -- key which is maintained by an associating sequence, the primary key for
115 -- the entity will be populated with the next sequence value in
116 -- preparation for the insert dml.
117 --
118 -- Pre Conditions:
119 -- This is an internal procedure which is called from the ins procedure.
120 --
121 -- In Parameters:
122 -- A Pl/Sql record structre.
123 --
124 -- Post Success:
125 -- Processing continues.
126 --
127 -- Post Failure:
128 -- If an error has occurred, an error message and exception will be raised
129 -- but not handled.
130 --
131 -- Developer Implementation Notes:
132 -- Any pre-processing required before the insert dml is issued should be
133 -- coded within this procedure. As stated above, a good example is the
134 -- generation of a primary key number via a corresponding sequence.
135 -- It is important to note that any 3rd party maintenance should be reviewed
136 -- before placing in this procedure.
137 --
138 -- Access Status:
139 -- Internal Table Handler Use Only.
140 --
141 -- ----------------------------------------------------------------------------
142 Procedure pre_insert(p_rec in out nocopy ssp_ern_shd.g_rec_type) is
143 --
144 l_proc varchar2(72) := g_package||'pre_insert';
145 --
146 Cursor C_Sel1 is select ssp_earnings_calculations_s.nextval from sys.dual;
147 --
148 Begin
149 hr_utility.set_location('Entering:'||l_proc, 1);
150 --
151 -- Select the next sequence number
152 --
153 Open C_Sel1;
154 Fetch C_Sel1 Into p_rec.earnings_calculations_id;
155 Close C_Sel1;
156 --
157 hr_utility.set_location('Leaving :'||l_proc, 100);
158 End pre_insert;
159 --
160 -- ----------------------------------------------------------------------------
161 -- |-----------------------------< post_insert >------------------------------|
162 -- ----------------------------------------------------------------------------
163 --
164 -- Description:
165 -- This private procedure contains any processing which is required after the
166 -- insert dml.
167 --
168 -- Pre Conditions:
169 -- This is an internal procedure which is called from the ins procedure.
170 --
171 -- In Parameters:
172 -- A Pl/Sql record structre.
173 --
174 -- Post Success:
175 -- Processing continues.
176 --
177 -- Post Failure:
178 -- If an error has occurred, an error message and exception will be raised
179 -- but not handled.
180 --
181 -- Developer Implementation Notes:
182 -- Any post-processing required after the insert dml is issued should be
183 -- coded within this procedure. It is important to note that any 3rd party
184 -- maintenance should be reviewed before placing in this procedure.
185 --
186 -- Access Status:
187 -- Internal Table Handler Use Only.
188 --
189 -- ----------------------------------------------------------------------------
190 Procedure post_insert(p_rec in ssp_ern_shd.g_rec_type) is
191 --
192 l_proc varchar2(72) := g_package||'post_insert';
193 --
194 Begin
195 hr_utility.set_location('Entering:'||l_proc, 1);
196 --
197 hr_utility.set_location('Leaving :'||l_proc, 100);
198 End post_insert;
199 --
200 -- ----------------------------------------------------------------------------
201 -- |---------------------------------< ins >----------------------------------|
202 -- ----------------------------------------------------------------------------
203 Procedure ins
204 (
205 p_rec in out nocopy ssp_ern_shd.g_rec_type,
206 p_validate in boolean default false
207 ) is
208 --
209 l_proc varchar2(72) := g_package||'ins';
210 --
211 Begin
212 hr_utility.set_location('Entering:'||l_proc, 1);
213 --
214 -- Determine if the business process is to be validated.
215 --
216 If p_validate
217 then
218 --
219 -- Issue the savepoint.
220 --
221 SAVEPOINT ins_ssp_ern;
222 End If;
223 --
224 -- Call the supporting insert validate operations
225 --
226 ssp_ern_bus.insert_validate(p_rec);
227 --
228 if g_payment_period_func_status = 'OLD' and p_rec.payment_periods is null
229 then
230 p_rec.payment_periods := ssp_ern_bus.number_of_periods;
231 end if;
232 --
233 -- Call the supporting pre-insert operation
234 -- if a value has been returned in p_rec.average_earnings_amount
235 --
236 if p_rec.average_earnings_amount is not null
237 then
238 pre_insert(p_rec);
239 --
240 -- Insert the row
241 --
242 insert_dml(p_rec);
243 --
244 -- Call the supporting post-insert operation
245 --
246 post_insert(p_rec);
247 end if;
248 --
249 -- If we are validating then raise the Validate_Enabled exception
250 --
251 If p_validate then
252 Raise HR_Api.Validate_Enabled;
253 End If;
254 --
255 hr_utility.set_location('Leaving :'||l_proc, 100);
256 Exception
257 When HR_Api.Validate_Enabled Then
258 --
259 -- As the Validate_Enabled exception has been raised
260 -- we must rollback to the savepoint
261 --
262 ROLLBACK TO ins_ssp_ern;
263 end ins;
264 --
265 -- ----------------------------------------------------------------------------
266 -- |---------------------------------< ins >----------------------------------|
267 -- ----------------------------------------------------------------------------
268 --
269 -- NOTE: this procedure is the old version, before the addition of parameters
270 -- for user_entered and payment_periods
271 --
272 Procedure ins
273 (
274 p_earnings_calculations_id out nocopy number,
275 p_object_version_number out nocopy number,
276 p_person_id in number,
277 p_effective_date in date,
278 p_average_earnings_amount in out nocopy number,
279 p_validate in boolean default false
280 ) is
281 --
282 l_rec ssp_ern_shd.g_rec_type;
283 l_proc varchar2(72) := g_package||'ins (old)';
284 --
285 Begin
286 hr_utility.set_location('Entering:'||l_proc, 1);
287 --
288 g_payment_period_func_status := 'OLD';
289 --
290 -- Call conversion function to turn arguments into the p_rec structure.
291 --
292 l_rec := ssp_ern_shd.convert_args (null,
293 null,
294 p_person_id,
295 p_effective_date,
296 p_average_earnings_amount,
297 'N',
298 'S', -- DFoster 1304683 Default to Sickness
299 null);
300 --
301 -- Having converted the arguments into the ssp_ern_rec
302 -- plsql record structure we call the corresponding record business process.
303 --
304 ins(l_rec, p_validate);
305 --
306 -- As the primary key argument(s)
307 -- are specified as an OUT's we must set these values.
308 --
309 p_earnings_calculations_id := l_rec.earnings_calculations_id;
310 p_object_version_number := l_rec.object_version_number;
311 p_average_earnings_amount := l_rec.average_earnings_amount;
312 --
313 hr_utility.set_location('Leaving :'||l_proc, 100);
314 End ins;
315 --
316 -- ----------------------------------------------------------------------------
317 -- |---------------------------------< ins >----------------------------------|
318 -- ----------------------------------------------------------------------------
319 --
320 -- NOTE: this procedure is the new version, with parameters for user_entered
321 -- and payment_periods
322 --
323 Procedure ins
324 (
325 p_earnings_calculations_id out nocopy number,
326 p_object_version_number out nocopy number,
327 p_person_id in number,
328 p_effective_date in date,
329 p_average_earnings_amount in out nocopy number,
330 p_user_entered in out nocopy varchar2,
331 p_absence_category in out nocopy varchar2, --DFoster 1304683
332 p_payment_periods in out nocopy number,
333 p_validate in boolean default false
334 ) is
335 --
336 l_rec ssp_ern_shd.g_rec_type;
337 l_proc varchar2(72) := g_package||'ins';
338 --
339 Begin
340 hr_utility.set_location('Entering:'||l_proc, 1);
341 --
342 g_payment_period_func_status := 'NEW';
343 --
344 -- Call conversion function to turn arguments into the p_rec structure.
345 --
346 l_rec := ssp_ern_shd.convert_args (null,
347 null,
348 p_person_id,
349 p_effective_date,
350 p_average_earnings_amount,
351 p_user_entered,
352 p_absence_category, --DFoster 1304683
353 p_payment_periods);
354 --
355 -- Having converted the arguments into the ssp_ern_rec
356 -- plsql record structure we call the corresponding record business process.
357 --
358 ins(l_rec, p_validate);
359 --
360 -- As the primary key argument(s)
361 -- are specified as an OUT's we must set these values.
362 --
363 p_earnings_calculations_id := l_rec.earnings_calculations_id;
364 p_object_version_number := l_rec.object_version_number;
365 p_average_earnings_amount := l_rec.average_earnings_amount;
366 p_user_entered := l_rec.user_entered;
367 p_absence_category := l_rec.absence_category; --DFoster 1304683
368 p_payment_periods := l_rec.payment_periods;
369 --
370 hr_utility.set_location('Leaving :'||l_proc, 100);
371 End ins;
372 --
373 end ssp_ern_ins;
[Home] [Help]