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