[Home] [Help]
PACKAGE BODY: APPS.PQH_RLS_INS
Source
1 Package Body pqh_rls_ins as
2 /* $Header: pqrlsrhi.pkb 120.0.12020000.2 2013/04/12 18:48:44 pathota ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pqh_rls_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 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 -- Prerequisites:
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 Row Handler Use Only.
50 --
51 -- {End Of Comments}
52 -- ----------------------------------------------------------------------------
53 Procedure insert_dml
54 ( p_rec in out NOCOPY pqh_rls_shd.g_rec_type
55 ) is
56 --
57 l_proc varchar2(72) := g_package||'insert_dml';
58 --
59 Begin
60 hr_utility.set_location('Entering:'||l_proc,5);
61 p_rec.object_version_number := 1; -- Initialise the object version
62 --
63 --
64 --
65 -- Insert the row into: pqh_roles
66 --
67 insert into pqh_roles
68 (role_id
69 ,role_name
70 ,role_type_cd
71 ,enable_flag
72 ,object_version_number
73 ,business_group_id
74 ,information_category
75 ,information1
76 ,information2
77 ,information3
78 ,information4
79 ,information5
80 ,information6
81 ,information7
82 ,information8
83 ,information9
84 ,information10
85 ,information11
86 ,information12
87 ,information13
88 ,information14
89 ,information15
90 ,information16
91 ,information17
92 ,information18
93 ,information19
94 ,information20
95 ,information21
96 ,information22
97 ,information23
98 ,information24
99 ,information25
100 ,information26
101 ,information27
102 ,information28
103 ,information29
104 ,information30
105 )
106 Values
107 (p_rec.role_id
108 ,p_rec.role_name
109 ,p_rec.role_type_cd
110 ,p_rec.enable_flag
111 ,p_rec.object_version_number
112 ,p_rec.business_group_id
113 ,p_rec.information_category
114 ,p_rec.information1
115 ,p_rec.information2
116 ,p_rec.information3
117 ,p_rec.information4
118 ,p_rec.information5
119 ,p_rec.information6
120 ,p_rec.information7
121 ,p_rec.information8
122 ,p_rec.information9
123 ,p_rec.information10
124 ,p_rec.information11
125 ,p_rec.information12
126 ,p_rec.information13
127 ,p_rec.information14
128 ,p_rec.information15
129 ,p_rec.information16
130 ,p_rec.information17
131 ,p_rec.information18
132 ,p_rec.information19
133 ,p_rec.information20
134 ,p_rec.information21
135 ,p_rec.information22
136 ,p_rec.information23
140 ,p_rec.information27
137 ,p_rec.information24
138 ,p_rec.information25
139 ,p_rec.information26
141 ,p_rec.information28
142 ,p_rec.information29
143 ,p_rec.information30
144 );
145 --
146 --
147 --
148 hr_utility.set_location(' Leaving:'||l_proc,10);
149 Exception
150 When hr_api.check_integrity_violated Then
151 -- A check constraint has been violated
152 --
153 pqh_rls_shd.constraint_error
154 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
155 When hr_api.parent_integrity_violated Then
156 -- Parent integrity has been violated
157 --
158 pqh_rls_shd.constraint_error
159 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
160 When hr_api.unique_integrity_violated Then
161 -- Unique integrity has been violated
162 --
163 pqh_rls_shd.constraint_error
164 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
165 When Others Then
166 --
167 Raise;
168 End insert_dml;
169 --
170 -- ----------------------------------------------------------------------------
171 -- |------------------------------< pre_insert >------------------------------|
172 -- ----------------------------------------------------------------------------
173 -- {Start Of Comments}
174 --
175 -- Description:
176 -- This private procedure contains any processing which is required before
177 -- the insert dml. Presently,if the entity has a corresponding primary
178 -- key which is maintained by an associating sequence,the primary key for
179 -- the entity will be populated with the next sequence value in
180 -- preparation for the insert dml.
181 --
182 -- Prerequisites:
183 -- This is an internal procedure which is called from the ins procedure.
184 --
185 -- In Parameters:
186 -- A Pl/Sql record structre.
187 --
188 -- Post Success:
189 -- Processing continues.
190 --
191 -- Post Failure:
192 -- If an error has occurred,an error message and exception will be raised
193 -- but not handled.
194 --
195 -- Developer Implementation Notes:
196 -- Any pre-processing required before the insert dml is issued should be
197 -- coded within this procedure. As stated above,a good example is the
198 -- generation of a primary key number via a corresponding sequence.
199 -- It is important to note that any 3rd party maintenance should be reviewed
200 -- before placing in this procedure.
201 --
202 -- Access Status:
203 -- Internal Row Handler Use Only.
204 --
205 -- {End Of Comments}
206 -- ----------------------------------------------------------------------------
207 Procedure pre_insert
208 ( p_rec in out NOCOPY pqh_rls_shd.g_rec_type
209 ) is
210 --
211 l_proc varchar2(72) := g_package||'pre_insert';
212 --
213 Cursor C_Sel1 is select pqh_roles_s.nextval from sys.dual;
214 --
215 Begin
216 hr_utility.set_location('Entering:'||l_proc,5);
217 --
218 --
219 -- Select the next sequence number
220 --
221 Open C_Sel1;
222 Fetch C_Sel1 Into p_rec.role_id;
223 Close C_Sel1;
224 --
225 hr_utility.set_location(' Leaving:'||l_proc,10);
226 End pre_insert;
227 --
228 -- ----------------------------------------------------------------------------
229 -- |-----------------------------< post_insert >------------------------------|
230 -- ----------------------------------------------------------------------------
231 -- {Start Of Comments}
232 --
233 -- Description:
234 -- This private procedure contains any processing which is required after the
235 -- insert dml.
236 --
237 -- Prerequisites:
238 -- This is an internal procedure which is called from the ins procedure.
239 --
240 -- In Parameters:
241 -- A Pl/Sql record structre.
242 --
243 -- Post Success:
244 -- Processing continues.
245 --
246 -- Post Failure:
247 -- If an error has occurred,an error message and exception will be raised
248 -- but not handled.
249 --
250 -- Developer Implementation Notes:
251 -- Any post-processing required after the insert dml is issued should be
252 -- coded within this procedure. It is important to note that any 3rd party
253 -- maintenance should be reviewed before placing in this procedure.
254 --
255 -- Access Status:
256 -- Internal Row Handler Use Only.
257 --
258 -- {End Of Comments}
259 -- ----------------------------------------------------------------------------
260 Procedure post_insert
261 (p_effective_date in date
262 ,p_rec in pqh_rls_shd.g_rec_type
263 ) is
264 --
265 l_proc varchar2(72) := g_package||'post_insert';
266 --
267 Begin
268 hr_utility.set_location('Entering:'||l_proc,5);
269 begin
270 --
271 pqh_rls_rki.after_insert
272 (p_effective_date => p_effective_date
273 ,p_role_id => p_rec.role_id
274 ,p_role_name => p_rec.role_name
275 ,p_role_type_cd => p_rec.role_type_cd
276 ,p_enable_flag => p_rec.enable_flag
277 ,p_object_version_number => p_rec.object_version_number
278 ,p_business_group_id => p_rec.business_group_id
279 ,p_information_category => p_rec.information_category
280 ,p_information1 => p_rec.information1
281 ,p_information2 => p_rec.information2
282 ,p_information3 => p_rec.information3
283 ,p_information4 => p_rec.information4
284 ,p_information5 => p_rec.information5
288 ,p_information9 => p_rec.information9
285 ,p_information6 => p_rec.information6
286 ,p_information7 => p_rec.information7
287 ,p_information8 => p_rec.information8
289 ,p_information10 => p_rec.information10
290 ,p_information11 => p_rec.information11
291 ,p_information12 => p_rec.information12
292 ,p_information13 => p_rec.information13
293 ,p_information14 => p_rec.information14
294 ,p_information15 => p_rec.information15
295 ,p_information16 => p_rec.information16
296 ,p_information17 => p_rec.information17
297 ,p_information18 => p_rec.information18
298 ,p_information19 => p_rec.information19
299 ,p_information20 => p_rec.information20
300 ,p_information21 => p_rec.information21
301 ,p_information22 => p_rec.information22
302 ,p_information23 => p_rec.information23
303 ,p_information24 => p_rec.information24
304 ,p_information25 => p_rec.information25
305 ,p_information26 => p_rec.information26
306 ,p_information27 => p_rec.information27
307 ,p_information28 => p_rec.information28
308 ,p_information29 => p_rec.information29
309 ,p_information30 => p_rec.information30
310 );
311 --
312 hr_utility.set_location('Before calling WF_SYNC:'||l_proc, 5);
313 if p_rec.enable_flag = 'Y' then
314 declare
315 l_plist wf_parameter_list_t;
316 begin
317 hr_utility.set_location('Before calling WF_SYNC parameters:'||l_proc, 5);
318 WF_EVENT.AddParameterToList('USER_NAME','PQH_ROLE:'|| p_rec.role_id, l_plist);
319 WF_EVENT.AddParameterToList('DISPLAYNAME',p_rec.role_name, l_plist);
320 WF_EVENT.AddParameterToList('DESCRIPTION',p_rec.role_name, l_plist);
321 WF_EVENT.AddParameterToList('orclWorkFlowNotificationPref', 'QUERY', l_plist);
322 WF_EVENT.AddParameterToList('orclIsEnabled','ACTIVE', l_plist);
323 WF_EVENT.AddParameterToList('orclWFOrigSystem','PQH_ROLE', l_plist);
324 WF_EVENT.AddParameterToList('orclWFOrigSystemID',p_rec.role_id,l_plist);
325 WF_EVENT.AddParameterToList('ExpirationDate',to_char(null),l_plist);
326 WF_EVENT.AddParameterToList('StartDate',to_char(p_effective_date,wf_engine.date_format),l_plist);
327 WF_EVENT.AddParameterToList('RaiseErrorS','FALSE',l_plist);
328
329 hr_utility.set_location('Before calling WF_SYNC package role:'|| p_rec.role_id, 5);
330 WF_LOCAL_SYNCH.propagate_role(p_orig_system => 'PQH_ROLE',
331 p_orig_system_id => p_rec.role_id,
332 p_attributes => l_plist,
333 p_start_date => p_effective_date);
334 hr_utility.set_location('After calling WF_SYNC package:'||l_proc, 5);
335 end;
336 end if;
337
338 hr_utility.set_location('After calling WF_SYNC:'||l_proc, 5);
339
340 exception
341 --
342 when hr_api.cannot_find_prog_unit then
343 --
344 hr_api.cannot_find_prog_unit_error
345 (p_module_name => 'PQH_ROLES'
346 ,p_hook_type => 'AI');
347 --
348 end;
349 --
350 hr_utility.set_location(' Leaving:'||l_proc, 10);
351 End post_insert;
352 --
353 -- ----------------------------------------------------------------------------
354 -- |---------------------------------< ins >----------------------------------|
355 -- ----------------------------------------------------------------------------
356 Procedure ins
357 (p_effective_date in date
358 ,p_rec in out NOCOPY pqh_rls_shd.g_rec_type
359 ) is
360 --
361 l_proc varchar2(72) := g_package||'ins';
362 --
363 Begin
364 hr_utility.set_location('Entering:'||l_proc,5);
365 --
366 -- Call the supporting insert validate operations
367 --
368 pqh_rls_bus.insert_validate
369 (p_effective_date
370 ,p_rec
371 );
372 --
373 -- Call the supporting pre-insert operation
374 --
375 pqh_rls_ins.pre_insert(p_rec);
376 --
377 -- Insert the row
378 --
379 pqh_rls_ins.insert_dml(p_rec);
380 --
381 -- Call the supporting post-insert operation
382 --
383 pqh_rls_ins.post_insert
384 (p_effective_date
385 ,p_rec
386 );
387 --
388 hr_utility.set_location('Leaving:'||l_proc,20);
389 end ins;
390 --
391 -- ----------------------------------------------------------------------------
392 -- |---------------------------------< ins >----------------------------------|
393 -- ----------------------------------------------------------------------------
394 -- mvanakda
395 -- Added DDF columns to the Procedure ins
396 Procedure ins
397 (p_effective_date in date
398 ,p_role_name in varchar2
399 ,p_business_group_id in number
400 ,p_role_type_cd in varchar2
401 ,p_enable_flag in varchar2
402 ,p_information_category in varchar2
403 ,p_information1 in varchar2
404 ,p_information2 in varchar2
405 ,p_information3 in varchar2
406 ,p_information4 in varchar2
407 ,p_information5 in varchar2
408 ,p_information6 in varchar2
409 ,p_information7 in varchar2
410 ,p_information8 in varchar2
411 ,p_information9 in varchar2
412 ,p_information10 in varchar2
413 ,p_information11 in varchar2
414 ,p_information12 in varchar2
415 ,p_information13 in varchar2
416 ,p_information14 in varchar2
417 ,p_information15 in varchar2
418 ,p_information16 in varchar2
419 ,p_information17 in varchar2
420 ,p_information18 in varchar2
421 ,p_information19 in varchar2
422 ,p_information20 in varchar2
423 ,p_information21 in varchar2
424 ,p_information22 in varchar2
425 ,p_information23 in varchar2
426 ,p_information24 in varchar2
427 ,p_information25 in varchar2
428 ,p_information26 in varchar2
429 ,p_information27 in varchar2
430 ,p_information28 in varchar2
431 ,p_information29 in varchar2
432 ,p_information30 in varchar2
433 ,p_role_id out NOCOPY number
434 ,p_object_version_number out NOCOPY number
435 ) is
436 --
437 l_rec pqh_rls_shd.g_rec_type;
438 l_proc varchar2(72) := g_package||'ins';
439 --
440 Begin
441 hr_utility.set_location('Entering:'||l_proc,5);
442 --
443 -- Call conversion function to turn arguments into the
444 -- structure.
445 --
446 l_rec :=
447 pqh_rls_shd.convert_args
448 (null
449 ,p_role_name
450 ,p_role_type_cd
451 ,p_enable_flag
452 ,null
453 ,p_business_group_id
454 ,p_information_category
455 ,p_information1
456 ,p_information2
457 ,p_information3
458 ,p_information4
459 ,p_information5
460 ,p_information6
461 ,p_information7
462 ,p_information8
463 ,p_information9
464 ,p_information10
465 ,p_information11
466 ,p_information12
467 ,p_information13
468 ,p_information14
469 ,p_information15
470 ,p_information16
471 ,p_information17
472 ,p_information18
473 ,p_information19
474 ,p_information20
475 ,p_information21
476 ,p_information22
477 ,p_information23
478 ,p_information24
479 ,p_information25
480 ,p_information26
481 ,p_information27
482 ,p_information28
483 ,p_information29
484 ,p_information30
485 );
486 --
487 -- Having converted the arguments into the pqh_rls_rec
488 -- plsql record structure we call the corresponding record business process.
489 --
490 pqh_rls_ins.ins
491 (p_effective_date
492 ,l_rec
493 );
494 --
495 -- As the primary key argument(s)
496 -- are specified as an OUT's we must set these values.
497 --
498 p_role_id := l_rec.role_id;
499 p_object_version_number := l_rec.object_version_number;
500 --
501 hr_utility.set_location(' Leaving:'||l_proc,10);
502 End ins;
503 --
504 end pqh_rls_ins;