1 Package Body pqh_att_ins as
2 /* $Header: pqattrhi.pkb 120.7 2011/06/09 08:12:49 sidsaxen ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pqh_att_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 insert the row into the schema.
21 -- 3) To trap any constraint violations that may have occurred.
22 -- 4) 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_att_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 -- Added the following code as a part of Zero Downtime Patching Project.
57 -- Code Starts Here.
58 --
59 per_ric_pkg.Chk_integrity (
60 p_entity_name=>'PQH_ATTRIBUTES',
61 p_ref_entity=>'PQH_TABLE_ROUTE',
62 p_ref_column_name=>'MASTER_TABLE_ROUTE_ID',
63 p_ref_col_value_number=>p_rec.master_table_route_id,
64 p_ref_col_value_varchar=>NULL,
65 p_ref_col_value_date=>NULL,
66 p_ref_type=> 'INS');
67 --
68 -- Code Ends Here
69 --
70 --
71 -- Insert the row into: pqh_attributes
72 --
73 insert into pqh_attributes
74 ( attribute_id,
75 attribute_name,
76 master_attribute_id,
77 master_table_route_id,
78 column_name,
79 column_type,
80 enable_flag,
81 width,
82 object_version_number,
83 region_itemname,
84 attribute_itemname,
85 decode_function_name
86 )
87 Values
88 ( p_rec.attribute_id,
89 p_rec.attribute_name,
90 p_rec.master_attribute_id,
91 p_rec.master_table_route_id,
92 p_rec.column_name,
93 p_rec.column_type,
94 p_rec.enable_flag,
95 p_rec.width,
96 p_rec.object_version_number,
97 p_rec.region_itemname,
98 p_rec.attribute_itemname,
99 p_rec.decode_function_name
100 );
101 --
102 --
103 hr_utility.set_location(' Leaving:'||l_proc, 10);
104 Exception
105 When hr_api.check_integrity_violated Then
106 -- A check constraint has been violated
107 pqh_att_shd.constraint_error
108 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
109 When hr_api.parent_integrity_violated Then
110 -- Parent integrity has been violated
111 pqh_att_shd.constraint_error
112 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
113 When hr_api.unique_integrity_violated Then
114 -- Unique integrity has been violated
115 pqh_att_shd.constraint_error
116 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
117 When Others Then
118 Raise;
119 End insert_dml;
120 --
121 -- ----------------------------------------------------------------------------
122 -- |------------------------------< pre_insert >------------------------------|
123 -- ----------------------------------------------------------------------------
124 -- {Start Of Comments}
125 --
126 -- Description:
127 -- This private procedure contains any processing which is required before
128 -- the insert dml. Presently, if the entity has a corresponding primary
129 -- key which is maintained by an associating sequence, the primary key for
130 -- the entity will be populated with the next sequence value in
131 -- preparation for the insert dml.
132 --
133 -- Prerequisites:
134 -- This is an internal procedure which is called from the ins procedure.
135 --
136 -- In Parameters:
137 -- A Pl/Sql record structre.
138 --
139 -- Post Success:
140 -- Processing continues.
141 --
142 -- Post Failure:
143 -- If an error has occurred, an error message and exception will be raised
144 -- but not handled.
145 --
146 -- Developer Implementation Notes:
147 -- Any pre-processing required before the insert dml is issued should be
148 -- coded within this procedure. As stated above, a good example is the
149 -- generation of a primary key number via a corresponding sequence.
150 -- It is important to note that any 3rd party maintenance should be reviewed
151 -- before placing in this procedure.
152 --
153 -- Access Status:
154 -- Internal Row Handler Use Only.
155 --
156 -- {End Of Comments}
157 -- ----------------------------------------------------------------------------
158 Procedure pre_insert(p_rec in out nocopy pqh_att_shd.g_rec_type) is
159 --
160 l_proc varchar2(72) := g_package||'pre_insert';
161 --
162 Cursor C_Sel1 is select pqh_attributes_s.nextval from sys.dual;
163 --
164 Begin
165 hr_utility.set_location('Entering:'||l_proc, 5);
166 --
167 --
168 -- Select the next sequence number
169 --
170 Open C_Sel1;
171 Fetch C_Sel1 Into p_rec.attribute_id;
172 Close C_Sel1;
173 --
174 hr_utility.set_location(' Leaving:'||l_proc, 10);
175 End pre_insert;
176 --
177 -- ----------------------------------------------------------------------------
178 -- |-----------------------------< post_insert >------------------------------|
179 -- ----------------------------------------------------------------------------
180 -- {Start Of Comments}
181 --
182 -- Description:
183 -- This private procedure contains any processing which is required after the
184 -- insert dml.
185 --
186 -- Prerequisites:
187 -- This is an internal procedure which is called from the ins procedure.
188 --
189 -- In Parameters:
190 -- A Pl/Sql record structre.
191 --
192 -- Post Success:
193 -- Processing continues.
194 --
195 -- Post Failure:
196 -- If an error has occurred, an error message and exception will be raised
197 -- but not handled.
198 --
199 -- Developer Implementation Notes:
200 -- Any post-processing required after the insert dml is issued should be
201 -- coded within this procedure. It is important to note that any 3rd party
202 -- maintenance should be reviewed before placing in this procedure.
203 --
204 -- Access Status:
205 -- Internal Row Handler Use Only.
206 --
207 -- {End Of Comments}
208 -- ----------------------------------------------------------------------------
209 Procedure post_insert(
210 p_effective_date in date,p_rec in pqh_att_shd.g_rec_type) is
211 --
212 l_proc varchar2(72) := g_package||'post_insert';
213 --
214 Begin
215 hr_utility.set_location('Entering:'||l_proc, 5);
216 --
217 --
218 -- Start of API User Hook for post_insert.
219 --
220 begin
221 --
222 pqh_att_rki.after_insert
223 (
224 p_attribute_id => p_rec.attribute_id
225 ,p_attribute_name => p_rec.attribute_name
226 ,p_master_attribute_id => p_rec.master_attribute_id
227 ,p_master_table_route_id => p_rec.master_table_route_id
228 ,p_column_name => p_rec.column_name
229 ,p_column_type => p_rec.column_type
230 ,p_enable_flag => p_rec.enable_flag
231 ,p_width => p_rec.width
232 ,p_object_version_number => p_rec.object_version_number
233 ,p_effective_date => p_effective_date
234 ,p_region_itemname => p_rec.region_itemname
235 ,p_attribute_itemname => p_rec.attribute_itemname
236 ,p_decode_function_name => p_rec.decode_function_name
237 );
238 --
239 exception
240 --
241 when hr_api.cannot_find_prog_unit then
242 --
243 hr_api.cannot_find_prog_unit_error
244 (p_module_name => 'pqh_attributes'
245 ,p_hook_type => 'AI');
246 --
247 end;
248 --
249 -- End of API User Hook for post_insert.
250 --
251 --
252 hr_utility.set_location(' Leaving:'||l_proc, 10);
253 End post_insert;
254 --
255 -- ----------------------------------------------------------------------------
256 -- |---------------------------------< ins >----------------------------------|
257 -- ----------------------------------------------------------------------------
258 Procedure ins
259 (
260 p_effective_date in date,
261 p_rec in out nocopy pqh_att_shd.g_rec_type
262 ) is
263 --
264 l_proc varchar2(72) := g_package||'ins';
265 --
266 Begin
267 hr_utility.set_location('Entering:'||l_proc, 5);
268 --
269 -- Call the supporting insert validate operations
270 --
271 pqh_att_bus.insert_validate(p_rec
272 ,p_effective_date);
273 --
274 -- Call the supporting pre-insert operation
275 --
276 pre_insert(p_rec);
277 --
278 -- Insert the row
279 --
280 insert_dml(p_rec);
281 --
282 -- Call the supporting post-insert operation
283 --
284 post_insert(
285 p_effective_date,p_rec);
286 end ins;
287 --
288 -- ----------------------------------------------------------------------------
289 -- |---------------------------------< ins >----------------------------------|
290 -- ----------------------------------------------------------------------------
291 Procedure ins
292 (
293 p_effective_date in date,
294 p_attribute_id out nocopy number,
295 p_attribute_name in varchar2,
296 p_master_attribute_id in number default null,
297 p_master_table_route_id in number,
298 p_column_name in varchar2,
299 p_column_type in varchar2,
300 p_enable_flag in varchar2 default null,
301 p_width in number,
302 p_object_version_number out nocopy number,
303 p_region_itemname in varchar2,
304 p_attribute_itemname in varchar2,
305 p_decode_function_name in varchar2
306 ) is
307 --
308 l_rec pqh_att_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_att_shd.convert_args
319 (
320 null,
321 p_attribute_name,
322 p_master_attribute_id,
323 p_master_table_route_id,
324 p_column_name,
325 p_column_type,
326 p_enable_flag,
327 p_width,
328 null,
329 p_region_itemname,
330 p_attribute_itemname,
331 p_decode_function_name
332 );
333 --
334 -- Having converted the arguments into the pqh_att_rec
335 -- plsql record structure we call the corresponding record business process.
336 --
337 ins(
338 p_effective_date,l_rec);
339 --
340 -- As the primary key argument(s)
341 -- are specified as an OUT's we must set these values.
342 --
343 p_attribute_id := l_rec.attribute_id;
344 p_object_version_number := l_rec.object_version_number;
345 --
346 hr_utility.set_location(' Leaving:'||l_proc, 10);
347 End ins;
348 --
349 end pqh_att_ins;