1 Package Body pqh_rlm_ins as
2 /* $Header: pqrlmrhi.pkb 115.13 2003/08/19 15:07:31 hsajja noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pqh_rlm_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 -- 3) To insert the row into the schema.
21 -- 4) To trap any constraint violations that may have occurred.
22 -- 5) To raise any other errors.
23 --
24 -- Prerequisites:
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 Row Handler Use Only.
44 --
45 -- {End Of Comments}
46 -- ----------------------------------------------------------------------------
47 Procedure insert_dml(p_rec in out nocopy pqh_rlm_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: pqh_routing_list_members
57 --
58 insert into pqh_routing_list_members
59 ( role_id,
60 routing_list_id,
61 routing_list_member_id,
62 seq_no,
63 approver_flag,
64 enable_flag,
65 object_version_number,
66 user_id
67 )
68 Values
69 ( p_rec.role_id,
70 p_rec.routing_list_id,
71 p_rec.routing_list_member_id,
72 p_rec.seq_no,
73 p_rec.approver_flag,
74 p_rec.enable_flag,
75 p_rec.object_version_number,
76 p_rec.user_id
77 );
78 --
79 --
80 hr_utility.set_location(' Leaving:'||l_proc, 10);
81 Exception
82 When hr_api.check_integrity_violated Then
83 -- A check constraint has been violated
84 pqh_rlm_shd.constraint_error
85 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
86 When hr_api.parent_integrity_violated Then
87 -- Parent integrity has been violated
88 pqh_rlm_shd.constraint_error
89 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
90 When hr_api.unique_integrity_violated Then
91 -- Unique integrity has been violated
92 pqh_rlm_shd.constraint_error
93 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
94 When Others Then
95 Raise;
96 End insert_dml;
97 --
98 -- ----------------------------------------------------------------------------
99 -- |------------------------------< pre_insert >------------------------------|
100 -- ----------------------------------------------------------------------------
101 -- {Start Of Comments}
102 --
103 -- Description:
104 -- This private procedure contains any processing which is required before
105 -- the insert dml. Presently, if the entity has a corresponding primary
106 -- key which is maintained by an associating sequence, the primary key for
107 -- the entity will be populated with the next sequence value in
108 -- preparation for the insert dml.
109 --
110 -- Prerequisites:
111 -- This is an internal procedure which is called from the ins procedure.
112 --
113 -- In Parameters:
114 -- A Pl/Sql record structre.
115 --
116 -- Post Success:
117 -- Processing continues.
118 --
119 -- Post Failure:
120 -- If an error has occurred, an error message and exception will be raised
121 -- but not handled.
122 --
123 -- Developer Implementation Notes:
124 -- Any pre-processing required before the insert dml is issued should be
125 -- coded within this procedure. As stated above, a good example is the
126 -- generation of a primary key number via a corresponding sequence.
127 -- It is important to note that any 3rd party maintenance should be reviewed
128 -- before placing in this procedure.
129 --
130 -- Access Status:
131 -- Internal Row Handler Use Only.
132 --
133 -- {End Of Comments}
134 -- ----------------------------------------------------------------------------
135 Procedure pre_insert(p_rec in out nocopy pqh_rlm_shd.g_rec_type) is
136 --
137 l_proc varchar2(72) := g_package||'pre_insert';
138 --
139 Cursor C_Sel1 is select pqh_routing_list_members_s.nextval from sys.dual;
140 --
141 Begin
142 hr_utility.set_location('Entering:'||l_proc, 5);
143 --
144 --
145 -- Select the next sequence number
146 --
147 Open C_Sel1;
148 Fetch C_Sel1 Into p_rec.routing_list_member_id;
149 Close C_Sel1;
150 --
151 hr_utility.set_location(' Leaving:'||l_proc, 10);
152 End pre_insert;
153 --
154 -- ----------------------------------------------------------------------------
155 -- |-----------------------------< post_insert >------------------------------|
156 -- ----------------------------------------------------------------------------
157 -- {Start Of Comments}
158 --
159 -- Description:
160 -- This private procedure contains any processing which is required after the
161 -- insert dml.
162 --
163 -- Prerequisites:
164 -- This is an internal procedure which is called from the ins procedure.
165 --
166 -- In Parameters:
167 -- A Pl/Sql record structre.
168 --
169 -- Post Success:
170 -- Processing continues.
171 --
172 -- Post Failure:
173 -- If an error has occurred, an error message and exception will be raised
174 -- but not handled.
175 --
176 -- Developer Implementation Notes:
177 -- Any post-processing required after the insert dml is issued should be
178 -- coded within this procedure. It is important to note that any 3rd party
179 -- maintenance should be reviewed before placing in this procedure.
180 --
181 -- Access Status:
182 -- Internal Row Handler Use Only.
183 --
184 -- {End Of Comments}
185 -- ----------------------------------------------------------------------------
186 Procedure post_insert(
187 p_effective_date in date,p_rec in pqh_rlm_shd.g_rec_type) is
188 --
189 l_proc varchar2(72) := g_package||'post_insert';
190 --
191 Begin
192 hr_utility.set_location('Entering:'||l_proc, 5);
193 --
194 --
195 -- Start of API User Hook for post_insert.
196 --
197 begin
198 --
199 pqh_rlm_rki.after_insert
200 (
201 p_role_id =>p_rec.role_id
202 ,p_routing_list_id =>p_rec.routing_list_id
203 ,p_routing_list_member_id =>p_rec.routing_list_member_id
204 ,p_seq_no =>p_rec.seq_no
205 ,p_approver_flag =>p_rec.approver_flag
206 ,p_enable_flag =>p_rec.enable_flag
207 ,p_object_version_number =>p_rec.object_version_number
208 ,p_user_id =>p_rec.user_id
209 ,p_effective_date =>p_effective_date
210 );
211 --
212 exception
213 --
214 when hr_api.cannot_find_prog_unit then
215 --
216 hr_api.cannot_find_prog_unit_error
217 (p_module_name => 'pqh_routing_list_members'
218 ,p_hook_type => 'AI');
219 --
220 end;
221 --
222 -- End of API User Hook for post_insert.
223 --
224 --
225 hr_utility.set_location(' Leaving:'||l_proc, 10);
226 End post_insert;
227 --
228 -- ----------------------------------------------------------------------------
229 -- |---------------------------------< ins >----------------------------------|
230 -- ----------------------------------------------------------------------------
231 Procedure ins
232 (
233 p_effective_date in date,
234 p_rec in out nocopy pqh_rlm_shd.g_rec_type
235 ) is
236 --
237 l_proc varchar2(72) := g_package||'ins';
238 --
239 Begin
240 hr_utility.set_location('Entering:'||l_proc, 5);
241 --
242 -- Call the supporting insert validate operations
243 --
244 pqh_rlm_bus.insert_validate(p_rec
245 ,p_effective_date);
246 --
247 -- Call the supporting pre-insert operation
248 --
249 pre_insert(p_rec);
250 --
251 -- Insert the row
252 --
253 insert_dml(p_rec);
254 --
255 -- Call the supporting post-insert operation
256 --
257 post_insert(
258 p_effective_date,p_rec);
259 end ins;
260 --
261 -- ----------------------------------------------------------------------------
262 -- |---------------------------------< ins >----------------------------------|
263 -- ----------------------------------------------------------------------------
264 Procedure ins
265 (
266 p_effective_date in date,
267 p_role_id in number default null,
268 p_routing_list_id in number,
269 p_routing_list_member_id out nocopy number,
270 p_seq_no in number default null,
271 p_approver_flag in varchar2 default null,
272 p_enable_flag in varchar2 default 'Y',
273 p_object_version_number out nocopy number,
274 p_user_id in number default null
275 ) is
276 --
277 l_rec pqh_rlm_shd.g_rec_type;
278 l_proc varchar2(72) := g_package||'ins';
279 --
280 Begin
281 hr_utility.set_location('Entering:'||l_proc, 5);
282 --
283 -- Call conversion function to turn arguments into the
284 -- p_rec structure.
285 --
286 l_rec :=
287 pqh_rlm_shd.convert_args
288 (
289 p_role_id,
290 p_routing_list_id,
291 null,
292 p_seq_no,
293 p_approver_flag,
294 p_enable_flag,
295 null,
296 p_user_id
297 );
298 --
299 -- Having converted the arguments into the pqh_rlm_rec
300 -- plsql record structure we call the corresponding record business process.
301 --
302 ins(
303 p_effective_date,l_rec);
304 --
305 -- As the primary key argument(s)
306 -- are specified as an OUT's we must set these values.
307 --
308 p_routing_list_member_id := l_rec.routing_list_member_id;
309 p_object_version_number := l_rec.object_version_number;
310 --
311 hr_utility.set_location(' Leaving:'||l_proc, 10);
312 End ins;
313 --
314 end pqh_rlm_ins;