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