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