1 Package Body pqh_bvr_ins as
2 /* $Header: pqbvrrhi.pkb 115.10 2002/12/05 19:30:27 rpasapul ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pqh_bvr_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 -- perform dml).
21 -- 3) To insert the row into the schema.
22 -- 4) To trap any constraint violations that may have occurred.
23 -- 5) To raise any other errors.
24 --
25 -- Prerequisites:
26 -- This is an internal private procedure which must be called from the ins
27 -- procedure and must have all mandatory attributes set (except the
28 -- object_version_number which is initialised within this procedure).
29 --
30 -- In Parameters:
31 -- A Pl/Sql record structre.
32 --
33 -- Post Success:
34 -- The specified row will be inserted into the schema.
35 --
36 -- Post Failure:
37 -- On the insert dml failure it is important to note that we always reset the
38 -- If a check, unique or parent integrity constraint violation is raised the
39 -- constraint_error procedure will be called.
40 -- If any other error is reported, the error will be raised after the
41 --
42 -- Developer Implementation Notes:
43 -- None.
44 --
45 -- Access Status:
46 -- Internal Row Handler Use Only.
47 --
48 -- {End Of Comments}
49 -- ----------------------------------------------------------------------------
50 Procedure insert_dml(p_rec in out nocopy pqh_bvr_shd.g_rec_type) is
51 --
52 l_proc varchar2(72) := g_package||'insert_dml';
53 --
54 Begin
55 hr_utility.set_location('Entering:'||l_proc, 5);
56 p_rec.object_version_number := 1; -- Initialise the object version
57 --
58 --
59 -- Insert the row into: pqh_budget_versions
60 --
61 insert into pqh_budget_versions
62 ( budget_version_id,
63 budget_id,
64 version_number,
65 date_from,
66 date_to,
67 transfered_to_gl_flag,
68 gl_status,
69 xfer_to_other_apps_cd,
70 object_version_number,
71 budget_unit1_value,
72 budget_unit2_value,
73 budget_unit3_value,
74 budget_unit1_available,
75 budget_unit2_available,
76 budget_unit3_available
77 )
78 Values
79 ( p_rec.budget_version_id,
80 p_rec.budget_id,
81 p_rec.version_number,
82 p_rec.date_from,
83 p_rec.date_to,
84 p_rec.transfered_to_gl_flag,
85 p_rec.gl_status,
86 p_rec.xfer_to_other_apps_cd,
87 p_rec.object_version_number,
88 p_rec.budget_unit1_value,
89 p_rec.budget_unit2_value,
90 p_rec.budget_unit3_value,
91 p_rec.budget_unit1_available,
92 p_rec.budget_unit2_available,
93 p_rec.budget_unit3_available
94 );
95 --
96 --
97 hr_utility.set_location(' Leaving:'||l_proc, 10);
98 Exception
99 When hr_api.check_integrity_violated Then
100 -- A check constraint has been violated
101 pqh_bvr_shd.constraint_error
102 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
103 When hr_api.parent_integrity_violated Then
104 -- Parent integrity has been violated
105 pqh_bvr_shd.constraint_error
106 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
107 When hr_api.unique_integrity_violated Then
108 -- Unique integrity has been violated
109 pqh_bvr_shd.constraint_error
110 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
111 When Others Then
112 Raise;
113 End insert_dml;
114 --
115 -- ----------------------------------------------------------------------------
116 -- |------------------------------< pre_insert >------------------------------|
117 -- ----------------------------------------------------------------------------
118 -- {Start Of Comments}
119 --
120 -- Description:
121 -- This private procedure contains any processing which is required before
122 -- the insert dml. Presently, if the entity has a corresponding primary
123 -- key which is maintained by an associating sequence, the primary key for
124 -- the entity will be populated with the next sequence value in
125 -- preparation for the insert dml.
126 --
127 -- Prerequisites:
128 -- This is an internal procedure which is called from the ins procedure.
129 --
130 -- In Parameters:
131 -- A Pl/Sql record structre.
132 --
133 -- Post Success:
134 -- Processing continues.
135 --
136 -- Post Failure:
137 -- If an error has occurred, an error message and exception will be raised
138 -- but not handled.
139 --
140 -- Developer Implementation Notes:
141 -- Any pre-processing required before the insert dml is issued should be
142 -- coded within this procedure. As stated above, a good example is the
143 -- generation of a primary key number via a corresponding sequence.
144 -- It is important to note that any 3rd party maintenance should be reviewed
145 -- before placing in this procedure.
146 --
147 -- Access Status:
148 -- Internal Row Handler Use Only.
149 --
150 -- {End Of Comments}
151 -- ----------------------------------------------------------------------------
152 Procedure pre_insert(p_rec in out nocopy pqh_bvr_shd.g_rec_type) is
153 --
154 l_proc varchar2(72) := g_package||'pre_insert';
155 --
156 Cursor C_Sel1 is select pqh_budget_versions_s.nextval from sys.dual;
157 --
158 Begin
159 hr_utility.set_location('Entering:'||l_proc, 5);
160 --
161 --
162 -- Select the next sequence number
163 --
164 Open C_Sel1;
165 Fetch C_Sel1 Into p_rec.budget_version_id;
166 Close C_Sel1;
167 --
168 hr_utility.set_location(' Leaving:'||l_proc, 10);
169 End pre_insert;
170 --
171 -- ----------------------------------------------------------------------------
172 -- |-----------------------------< post_insert >------------------------------|
173 -- ----------------------------------------------------------------------------
174 -- {Start Of Comments}
175 --
176 -- Description:
177 -- This private procedure contains any processing which is required after the
178 -- insert dml.
179 --
180 -- Prerequisites:
181 -- This is an internal procedure which is called from the ins procedure.
182 --
183 -- In Parameters:
184 -- A Pl/Sql record structre.
185 --
186 -- Post Success:
187 -- Processing continues.
188 --
189 -- Post Failure:
190 -- If an error has occurred, an error message and exception will be raised
191 -- but not handled.
192 --
193 -- Developer Implementation Notes:
194 -- Any post-processing required after the insert dml is issued should be
195 -- coded within this procedure. It is important to note that any 3rd party
196 -- maintenance should be reviewed before placing in this procedure.
197 --
198 -- Access Status:
199 -- Internal Row Handler Use Only.
200 --
201 -- {End Of Comments}
202 -- ----------------------------------------------------------------------------
203 Procedure post_insert(
204 p_effective_date in date,p_rec in pqh_bvr_shd.g_rec_type) is
205 --
206 l_proc varchar2(72) := g_package||'post_insert';
207 --
208 Begin
209 hr_utility.set_location('Entering:'||l_proc, 5);
210 --
211 --
212 -- Start of API User Hook for post_insert.
213 --
214 begin
215 --
216 pqh_bvr_rki.after_insert
217 (
218 p_budget_version_id =>p_rec.budget_version_id
219 ,p_budget_id =>p_rec.budget_id
220 ,p_version_number =>p_rec.version_number
221 ,p_date_from =>p_rec.date_from
222 ,p_date_to =>p_rec.date_to
223 ,p_transfered_to_gl_flag =>p_rec.transfered_to_gl_flag
224 ,p_gl_status =>p_rec.gl_status
225 ,p_xfer_to_other_apps_cd =>p_rec.xfer_to_other_apps_cd
226 ,p_object_version_number =>p_rec.object_version_number
227 ,p_budget_unit1_value =>p_rec.budget_unit1_value
228 ,p_budget_unit2_value =>p_rec.budget_unit2_value
229 ,p_budget_unit3_value =>p_rec.budget_unit3_value
230 ,p_budget_unit1_available =>p_rec.budget_unit1_available
231 ,p_budget_unit2_available =>p_rec.budget_unit2_available
232 ,p_budget_unit3_available =>p_rec.budget_unit3_available
233 ,p_effective_date =>p_effective_date
234 );
235 --
236 exception
237 --
238 when hr_api.cannot_find_prog_unit then
239 --
240 hr_api.cannot_find_prog_unit_error
241 (p_module_name => 'pqh_budget_versions'
242 ,p_hook_type => 'AI');
243 --
244 end;
245 --
246 -- End of API User Hook for post_insert.
247 --
248 --
249 hr_utility.set_location(' Leaving:'||l_proc, 10);
250 End post_insert;
251 --
252 -- ----------------------------------------------------------------------------
253 -- |---------------------------------< ins >----------------------------------|
254 -- ----------------------------------------------------------------------------
255 Procedure ins
256 (
257 p_effective_date in date,
258 p_rec in out nocopy pqh_bvr_shd.g_rec_type
259 ) is
260 --
261 l_proc varchar2(72) := g_package||'ins';
262 --
263 Begin
264 hr_utility.set_location('Entering:'||l_proc, 5);
265 --
266 -- Call the supporting insert validate operations
267 --
268 pqh_bvr_bus.insert_validate(p_rec
269 ,p_effective_date);
270 --
271 -- Call the supporting pre-insert operation
272 --
273 pre_insert(p_rec);
274 --
275 -- Insert the row
276 --
277 insert_dml(p_rec);
278 --
279 -- Call the supporting post-insert operation
280 --
281 post_insert(
282 p_effective_date,p_rec);
283 end ins;
284 --
285 -- ----------------------------------------------------------------------------
286 -- |---------------------------------< ins >----------------------------------|
287 -- ----------------------------------------------------------------------------
288 Procedure ins
289 (
290 p_effective_date in date,
291 p_budget_version_id out nocopy number,
292 p_budget_id in number default null,
293 p_version_number in number,
294 p_date_from in date default null,
295 p_date_to in date default null,
296 p_transfered_to_gl_flag in varchar2 default null,
297 p_gl_status in varchar2 default null,
298 p_xfer_to_other_apps_cd in varchar2 default null,
299 p_object_version_number out nocopy number,
300 p_budget_unit1_value in number default null,
301 p_budget_unit2_value in number default null,
302 p_budget_unit3_value in number default null,
303 p_budget_unit1_available in number default null,
304 p_budget_unit2_available in number default null,
305 p_budget_unit3_available in number default null
306 ) is
307 --
308 l_rec pqh_bvr_shd.g_rec_type;
309 l_proc varchar2(72) := g_package||'ins';
310 --
311 Begin
312 hr_utility.set_location('Entering:'||l_proc, 5);
313 --
314 -- Call conversion function to turn arguments into the
315 -- p_rec structure.
316 --
317 l_rec :=
318 pqh_bvr_shd.convert_args
319 (
320 null,
321 p_budget_id,
322 p_version_number,
323 p_date_from,
324 p_date_to,
325 p_transfered_to_gl_flag,
326 p_gl_status,
327 p_xfer_to_other_apps_cd,
328 null,
329 p_budget_unit1_value,
330 p_budget_unit2_value,
331 p_budget_unit3_value,
332 p_budget_unit1_available,
333 p_budget_unit2_available,
334 p_budget_unit3_available
335 );
336 --
337 -- Having converted the arguments into the pqh_bvr_rec
338 -- plsql record structure we call the corresponding record business process.
339 --
340 ins(
341 p_effective_date,l_rec);
342 --
343 -- As the primary key argument(s)
344 -- are specified as an OUT's we must set these values.
345 --
346 p_budget_version_id := l_rec.budget_version_id;
347 p_object_version_number := l_rec.object_version_number;
348 --
349 hr_utility.set_location(' Leaving:'||l_proc, 10);
350 End ins;
351 --
352 end pqh_bvr_ins;