1 Package Body ghr_pdh_ins as
2 /* $Header: ghpdhrhi.pkb 120.1 2006/01/17 06:21:22 sumarimu noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ghr_pdh_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 insert the row into the schema.
21 -- 3) To trap any constraint violations that may have occurred.
22 -- 4) To raise any other errors.
23 --
24 -- Pre Conditions:
28 --
25 -- This is an internal private procedure which must be called from the ins
26 -- procedure and must have all mandatory attributes set (except the
27 -- object_version_number which is initialised within this procedure).
29 -- In Parameters:
30 -- A Pl/Sql record structre.
31 --
32 -- Post Success:
33 -- The specified row will be inserted into the schema.
34 --
35 -- Post Failure:
36 -- If a check, unique or parent integrity constraint violation is raised the
37 -- constraint_error procedure will be called.
38 --
39 -- Developer Implementation Notes:
40 -- None.
41 --
42 -- Access Status:
43 -- Internal Table Handler Use Only.
44 --
45 -- {End Of Comments}
46 -- ----------------------------------------------------------------------------
47 Procedure insert_dml(p_rec in out NOCOPY ghr_pdh_shd.g_rec_type) is
48 --
49 l_proc varchar2(72) := g_package||'insert_dml';
50
51 Begin
52 hr_utility.set_location('Entering:'||l_proc, 5);
53 p_rec.object_version_number := 1; -- Initialise the object version
54 --
55
56 -- Insert the row into: ghr_pd_routing_history
57 --
58
59 insert into ghr_pd_routing_history
60 (pd_routing_history_id,
61 position_description_id,
62 initiator_flag,
63 requester_flag,
64 approver_flag,
65 reviewer_flag,
66 authorizer_flag,
67 personnelist_flag,
68 approved_flag,
69 user_name,
70 user_name_employee_id,
71 user_name_emp_first_name,
72 user_name_emp_last_name,
73 user_name_emp_middle_names,
74 action_taken,
75 groupbox_id,
76 routing_list_id,
77 routing_seq_number,
78 date_notification_sent,
79 object_version_number,
80 item_key
81 )
82 Values
83 ( p_rec.pd_routing_history_id,
84 p_rec.position_description_id,
85 p_rec.initiator_flag,
86 p_rec.requester_flag,
87 p_rec.approver_flag,
88 p_rec.reviewer_flag,
89 p_rec.authorizer_flag,
90 p_rec.personnelist_flag,
91 p_rec.approved_flag,
92 p_rec.user_name,
93 p_rec.user_name_employee_id,
94 p_rec.user_name_emp_first_name,
95 p_rec.user_name_emp_last_name,
96 p_rec.user_name_emp_middle_names,
97 p_rec.action_taken,
98 p_rec.groupbox_id,
99 p_rec.routing_list_id,
100 p_rec.routing_seq_number,
101 p_rec.date_notification_sent,
102 p_rec.object_version_number,
103 p_rec.item_key
104 );
105
106 --
107 hr_utility.set_location(' Leaving:'||l_proc, 10);
108 Exception
109 When hr_api.check_integrity_violated Then
110 -- A check constraint has been violated
111 ghr_pdh_shd.constraint_error
112 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
113 When hr_api.parent_integrity_violated Then
114 -- Parent integrity has been violated
115 ghr_pdh_shd.constraint_error
116 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
117 When hr_api.unique_integrity_violated Then
118 -- Unique integrity has been violated
119 ghr_pdh_shd.constraint_error
120 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
121 When Others Then
122 Raise;
123 End insert_dml;
124 --
125 -- ----------------------------------------------------------------------------
126 -- |------------------------------< pre_insert >------------------------------|
127 -- ----------------------------------------------------------------------------
128 -- {Start Of Comments}
129 --
130 -- Description:
131 -- This private procedure contains any processing which is required before
132 -- the insert dml. Presently, if the entity has a corresponding primary
133 -- key which is maintained by an associating sequence, the primary key for
134 -- the entity will be populated with the next sequence value in
135 -- preparation for the insert dml.
136 --
137 -- Pre Conditions:
138 -- This is an internal procedure which is called from the ins procedure.
139 --
140 -- In Parameters:
141 -- A Pl/Sql record structre.
142 --
143 -- Post Success:
144 -- Processing continues.
145 --
146 -- Post Failure:
147 -- If an error has occurred, an error message and exception will be raised
148 -- but not handled.
149 --
150 -- Developer Implementation Notes:
151 -- Any pre-processing required before the insert dml is issued should be
152 -- coded within this procedure. As stated above, a good example is the
153 -- generation of a primary key number via a corresponding sequence.
154 -- It is important to note that any 3rd party maintenance should be reviewed
155 -- before placing in this procedure.
156 --
157 -- Access Status:
158 -- Internal Table Handler Use Only.
159 --
160 -- {End Of Comments}
161 -- ----------------------------------------------------------------------------
162 Procedure pre_insert
163 (p_rec in out NOCOPY ghr_pdh_shd.g_rec_type)is
164 --
165 l_proc varchar2(72) := g_package||'pre_insert';
166 --
167 Cursor C_Sel1 is select ghr_pd_routing_history_s.nextval from sys.dual;
168 --
169 Begin
170 hr_utility.set_location('Entering:'||l_proc, 5);
171 --
172 --
173 -- Select the next sequence number
174 --
175 open C_Sel1;
176 Fetch C_Sel1 Into p_rec.pd_routing_history_id;
177 Close C_Sel1;
178 hr_utility.set_location(' Leaving:'||l_proc, 10);
179 End pre_insert;
180
181
182
183 --
184 -- ----------------------------------------------------------------------------
188 --
185 -- |-----------------------------< post_insert >------------------------------|
186 -- ----------------------------------------------------------------------------
187 -- {Start Of Comments}
189 -- Description:
190 -- This private procedure contains any processing which is required after the
191 -- insert dml.
192 --
193 -- Pre Conditions:
194 -- This is an internal procedure which is called from the ins procedure.
195 --
196 -- In Parameters:
197 -- A Pl/Sql record structre.
198 --
199 -- Post Success:
200 -- Processing continues.
201 --
202 -- Post Failure:
203 -- If an error has occurred, an error message and exception will be raised
204 -- but not handled.
205 --
206 -- Developer Implementation Notes:
207 -- Any post-processing required after the insert dml is issued should be
208 -- coded within this procedure. It is important to note that any 3rd party
209 -- maintenance should be reviewed before placing in this procedure.
210 --
211 -- Access Status:
212 -- Internal Table Handler Use Only.
213 --
214 -- {End Of Comments}
215 -- ----------------------------------------------------------------------------
216 Procedure post_insert(p_rec in ghr_pdh_shd.g_rec_type) is
217 --
218 l_proc varchar2(72) := g_package||'post_insert';
219 --
220 Begin
221 hr_utility.set_location('Entering:'||l_proc, 5);
222 --
223 -- This is a hook point and the user hook for post_insert is called here.
224 --
225 begin
226 ghr_pdh_rki.after_insert (
227 p_pd_routing_history_id => p_rec.pd_routing_history_id,
228 p_position_description_id => p_rec.position_description_id,
229 p_initiator_flag => p_rec.initiator_flag,
230 p_requester_flag => p_rec.requester_flag,
231 p_approver_flag => p_rec.approver_flag,
232 p_personnelist_flag => p_rec.personnelist_flag,
233 p_reviewer_flag => p_rec.reviewer_flag,
234 p_authorizer_flag => p_rec.authorizer_flag,
235 p_approved_flag => p_rec.approved_flag,
236 p_user_name => p_rec.user_name,
237 p_user_name_employee_id => p_rec.user_name_employee_id,
238 p_user_name_emp_first_name => p_rec.user_name_emp_first_name,
239 p_user_name_emp_last_name => p_rec.user_name_emp_last_name,
240 p_user_name_emp_middle_names => p_rec.user_name_emp_middle_names,
241 p_action_taken => p_rec.action_taken,
242 p_groupbox_id => p_rec.groupbox_id,
243 p_routing_list_id => p_rec.routing_list_id,
244 p_routing_seq_number => p_rec.routing_seq_number,
245 p_date_notification_sent => p_rec.date_notification_sent,
246 p_item_key => p_rec.item_key,
247 p_object_version_number => p_rec.object_version_number);
248
249 exception
250 when hr_api.cannot_find_prog_unit then
251 hr_api.cannot_find_prog_unit_error
252 ( p_module_name => 'GHR_PD_ROUTING_HISTORY'
253 ,p_hook_type => 'AI'
254 );
255 end;
256 -- End of API User Hook for post_insert.
257 --
258 hr_utility.set_location(' Leaving:'||l_proc, 10);
259 End post_insert;
260 --
261 -- ----------------------------------------------------------------------------
262 -- |---------------------------------< ins >----------------------------------|
263 -- ----------------------------------------------------------------------------
264 Procedure ins
265 (
266 p_rec in out NOCOPY ghr_pdh_shd.g_rec_type,
267 p_validate in boolean default false
268 ) is
269 --
270 l_proc varchar2(72) := g_package||'ins';
271 --
272 Begin
273 hr_utility.set_location('Entering:'||l_proc, 5);
274 --
275 -- Determine if the business process is to be validated.
276 --
277 If p_validate then
278 --
279 -- Issue the savepoint.
280 --
281 SAVEPOINT ins_ghr_pdh;
282 End If;
283 --
284 -- Call the supporting insert validate operations
285 --
286 ghr_pdh_bus.insert_validate(p_rec);
287 --
288 -- Call the supporting pre-insert operation
289 --
290
291 pre_insert(p_rec);
292 --
293 -- Insert the row
294 --
295 insert_dml(p_rec);
296 --
297 -- Call the supporting post-insert operation
298 --
299 post_insert(p_rec);
300 --
301 -- If we are validating then raise the Validate_Enabled exception
302 --
303 If p_validate then
304 Raise HR_Api.Validate_Enabled;
305 End If;
306 --
307 hr_utility.set_location(' Leaving:'||l_proc, 10);
308 Exception
309 When HR_Api.Validate_Enabled Then
310 --
311 -- As the Validate_Enabled exception has been raised
312 -- we must rollback to the savepoint
313 --
314 ROLLBACK TO ins_ghr_pdh;
315 end ins;
316 --
317 -- ----------------------------------------------------------------------------
318 -- |---------------------------------< ins>----------------------------------|
319 -- ----------------------------------------------------------------------------
320 Procedure ins
321 (
322 p_pd_routing_history_id out NOCOPY number,
323 p_position_description_id in number,
324 p_initiator_flag in varchar2 default null,
325 p_requester_flag in varchar2 default null,
329 p_personnelist_flag in varchar2 default null,
326 p_approver_flag in varchar2 default null,
327 p_reviewer_flag in varchar2 default null,
328 p_authorizer_flag in varchar2 default null,
330 p_approved_flag in varchar2 default null,
331 p_user_name in varchar2 default null,
332 p_user_name_employee_id in number default null,
333 p_user_name_emp_first_name in varchar2 default null,
334 p_user_name_emp_last_name in varchar2 default null,
335 p_user_name_emp_middle_names in varchar2 default null,
336 p_action_taken in varchar2 default null,
337 p_groupbox_id in number default null,
338 p_routing_list_id in number default null,
339 p_routing_seq_number in number default null,
340 p_date_notification_sent in date default sysdate,
341 p_object_version_number out NOCOPY number,
342 p_validate in boolean default false ,
343 p_item_key in varchar2
344 ) is
345 --
346 l_rec ghr_pdh_shd.g_rec_type;
347 l_proc varchar2(72) := g_package||'ins';
348 --
349 Begin
350 hr_utility.set_location('Entering:'||l_proc, 5);
351 --
352 -- Call conversion function to turn arguments into the
353 -- p_rec structure.
354 --
355 l_rec :=
356 ghr_pdh_shd.convert_args
357 (
358 null,
359 p_position_description_id,
360 p_initiator_flag,
361 p_requester_flag,
362 p_approver_flag,
363 p_reviewer_flag,
364 p_authorizer_flag,
365 p_personnelist_flag,
366 p_approved_flag,
367 p_user_name,
368 p_user_name_employee_id,
369 p_user_name_emp_first_name,
370 p_user_name_emp_last_name,
371 p_user_name_emp_middle_names,
372 p_action_taken,
373 p_groupbox_id,
374 p_routing_list_id,
375 p_routing_seq_number,
376 p_date_notification_sent,
377 null,
378 p_item_key
379 );
380
381 --
382 -- Having converted the arguments into the ghr_pdh_rec
383 -- plsql record structure we call the corresponding record business process.
384 --
385 ins(l_rec,p_validate);
386 --
387 -- As the primary key argument(s)
388 -- are specified as an OUT's we must set these values.
389 --
390 p_pd_routing_history_id := l_rec.pd_routing_history_id;
391 p_object_version_number := l_rec.object_version_number;
392 --
393 hr_utility.set_location(' Leaving:'||l_proc, 10);
394 End ins;
395 --
396 end ghr_pdh_ins;