DBA Data[Home] [Help]

PACKAGE BODY: APPS.PQH_DFS_INS

Source


1 Package Body pqh_dfs_ins as
2 /* $Header: pqdfsrhi.pkb 115.11 2003/04/02 20:02:02 srajakum noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- |                     Private Global Definitions                           |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package  varchar2(33)	:= '  pqh_dfs_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_dfs_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_dflt_fund_srcs
57   --
58   insert into pqh_dflt_fund_srcs
59   (	dflt_fund_src_id,
60 	dflt_budget_element_id,
61 	dflt_dist_percentage,
62         project_id,
63         award_id,
64         task_id,
65         expenditure_type,
66         organization_id,
67 	object_version_number,
68 	cost_allocation_keyflex_id
69   )
70   Values
71   (	p_rec.dflt_fund_src_id,
72 	p_rec.dflt_budget_element_id,
73 	p_rec.dflt_dist_percentage,
74         p_rec.project_id,
75         p_rec.award_id,
76         p_rec.task_id,
77         p_rec.expenditure_type,
78         p_rec.organization_id,
79 	p_rec.object_version_number,
80 	p_rec.cost_allocation_keyflex_id
81   );
82   --
83   --
84   hr_utility.set_location(' Leaving:'||l_proc, 10);
85 Exception
86   When hr_api.check_integrity_violated Then
87     -- A check constraint has been violated
88     pqh_dfs_shd.constraint_error
89       (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
90   When hr_api.parent_integrity_violated Then
91     -- Parent integrity has been violated
92     pqh_dfs_shd.constraint_error
93       (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
94   When hr_api.unique_integrity_violated Then
95     -- Unique integrity has been violated
96     pqh_dfs_shd.constraint_error
97       (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
98   When Others Then
99     Raise;
100 End insert_dml;
104 -- ----------------------------------------------------------------------------
101 --
102 -- ----------------------------------------------------------------------------
103 -- |------------------------------< pre_insert >------------------------------|
105 -- {Start Of Comments}
106 --
107 -- Description:
108 --   This private procedure contains any processing which is required before
109 --   the insert dml. Presently, if the entity has a corresponding primary
110 --   key which is maintained by an associating sequence, the primary key for
111 --   the entity will be populated with the next sequence value in
112 --   preparation for the insert dml.
113 --
114 -- Prerequisites:
115 --   This is an internal procedure which is called from the ins procedure.
116 --
117 -- In Parameters:
118 --   A Pl/Sql record structre.
119 --
120 -- Post Success:
121 --   Processing continues.
122 --
123 -- Post Failure:
124 --   If an error has occurred, an error message and exception will be raised
125 --   but not handled.
126 --
127 -- Developer Implementation Notes:
128 --   Any pre-processing required before the insert dml is issued should be
129 --   coded within this procedure. As stated above, a good example is the
130 --   generation of a primary key number via a corresponding sequence.
131 --   It is important to note that any 3rd party maintenance should be reviewed
132 --   before placing in this procedure.
133 --
134 -- Access Status:
135 --   Internal Row Handler Use Only.
136 --
137 -- {End Of Comments}
138 -- ----------------------------------------------------------------------------
139 Procedure pre_insert(p_rec  in out nocopy pqh_dfs_shd.g_rec_type) is
140 --
141   l_proc  varchar2(72) := g_package||'pre_insert';
142 --
143   Cursor C_Sel1 is select pqh_dflt_fund_srcs_s.nextval from sys.dual;
144 --
145 Begin
146   hr_utility.set_location('Entering:'||l_proc, 5);
147   --
148   --
149   -- Select the next sequence number
150   --
151   Open C_Sel1;
152   Fetch C_Sel1 Into p_rec.dflt_fund_src_id;
153   Close C_Sel1;
154   --
155   hr_utility.set_location(' Leaving:'||l_proc, 10);
156 End pre_insert;
157 --
158 -- ----------------------------------------------------------------------------
159 -- |-----------------------------< post_insert >------------------------------|
160 -- ----------------------------------------------------------------------------
161 -- {Start Of Comments}
162 --
163 -- Description:
164 --   This private procedure contains any processing which is required after the
165 --   insert dml.
166 --
167 -- Prerequisites:
168 --   This is an internal procedure which is called from the ins procedure.
169 --
170 -- In Parameters:
171 --   A Pl/Sql record structre.
172 --
173 -- Post Success:
174 --   Processing continues.
175 --
176 -- Post Failure:
177 --   If an error has occurred, an error message and exception will be raised
178 --   but not handled.
179 --
180 -- Developer Implementation Notes:
181 --   Any post-processing required after the insert dml is issued should be
182 --   coded within this procedure. It is important to note that any 3rd party
183 --   maintenance should be reviewed before placing in this procedure.
184 --
185 -- Access Status:
186 --   Internal Row Handler Use Only.
187 --
188 -- {End Of Comments}
189 -- ----------------------------------------------------------------------------
190 Procedure post_insert(p_rec in pqh_dfs_shd.g_rec_type) is
191 --
192   l_proc  varchar2(72) := g_package||'post_insert';
193 --
194 --
195 l_sum       number(15,2) := 0;
196 
197  cursor csr_element(p_dflt_budget_element_id in number) is
198  select SUM(NVL(dflt_dist_percentage,0))
199  from pqh_dflt_fund_srcs
200  where dflt_budget_element_id = p_dflt_budget_element_id;
201 
202 
203 --
204 
205 Begin
206   hr_utility.set_location('Entering:'||l_proc, 5);
207 --
208   --
209   open csr_element(p_dflt_budget_element_id  => p_rec.dflt_budget_element_id);
210    fetch csr_element into l_sum;
211   close csr_element;
212 
213    if l_sum > 100 then
214      -- sum cannot be more then 100
215      --
216       hr_utility.set_message(8302,'PQH_WKS_INVALID_SRCS_SUM');
217       hr_utility.raise_error;
218     --
219    end if;
220   --
221 
222   --
223   -- Start of API User Hook for post_insert.
224   --
225   begin
226     --
227     pqh_dfs_rki.after_insert
228       (
229   p_dflt_fund_src_id              =>p_rec.dflt_fund_src_id
230  ,p_dflt_budget_element_id        =>p_rec.dflt_budget_element_id
231  ,p_dflt_dist_percentage          =>p_rec.dflt_dist_percentage
232  ,p_object_version_number         =>p_rec.object_version_number
233  ,p_cost_allocation_keyflex_id    =>p_rec.cost_allocation_keyflex_id
234  ,p_project_id                    =>p_rec.project_id
235  ,p_award_id                      =>p_rec.award_id
236  ,p_task_id                       =>p_rec.task_id
237  ,p_expenditure_type              =>p_rec.expenditure_type
238  ,p_organization_id               =>p_rec.organization_id
239 );
240     --
241   exception
242     --
243     when hr_api.cannot_find_prog_unit then
244       --
245       hr_api.cannot_find_prog_unit_error
246         (p_module_name => 'pqh_dflt_fund_srcs'
247         ,p_hook_type   => 'AI');
248       --
249   end;
250   --
251   -- End of API User Hook for post_insert.
252   --
253   --
254   hr_utility.set_location(' Leaving:'||l_proc, 10);
255 End post_insert;
256 --
257 -- ----------------------------------------------------------------------------
258 -- |---------------------------------< ins >----------------------------------|
259 -- ----------------------------------------------------------------------------
260 Procedure ins
261   (
262   p_rec        in out nocopy pqh_dfs_shd.g_rec_type
263   ) is
264 --
265   l_proc  varchar2(72) := g_package||'ins';
266 --
267 Begin
268   hr_utility.set_location('Entering:'||l_proc, 5);
269   --
270   -- Call the supporting insert validate operations
271   --
272   pqh_dfs_bus.insert_validate(p_rec);
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(p_rec);
285 end ins;
286 --
287 -- ----------------------------------------------------------------------------
288 -- |---------------------------------< ins >----------------------------------|
289 -- ----------------------------------------------------------------------------
290 Procedure ins
291   (
292   p_dflt_fund_src_id             out nocopy number,
293   p_dflt_budget_element_id       in number,
294   p_dflt_dist_percentage         in number           default null,
295   p_project_id                   in number           default null,
296   p_award_id                     in number           default null,
297   p_task_id                      in number           default null,
298   p_expenditure_type             in varchar2         default null,
299   p_organization_id              in number           default null,
300   p_object_version_number        out nocopy number,
301   p_cost_allocation_keyflex_id   in number           default null
302   ) is
303 --
304   l_rec	  pqh_dfs_shd.g_rec_type;
305   l_proc  varchar2(72) := g_package||'ins';
306 --
307 Begin
308   hr_utility.set_location('Entering:'||l_proc, 5);
309   --
310   -- Call conversion function to turn arguments into the
311   -- p_rec structure.
312   --
313   l_rec :=
314   pqh_dfs_shd.convert_args
315   (
316   null,
317   p_dflt_budget_element_id,
318   p_dflt_dist_percentage,
319   p_project_id,
320   p_award_id,
321   p_task_id,
322   p_expenditure_type,
323   p_organization_id,
324   null,
325   p_cost_allocation_keyflex_id
326   );
327   --
328   -- Having converted the arguments into the pqh_dfs_rec
329   -- plsql record structure we call the corresponding record business process.
330   --
331   ins(l_rec);
332   --
333   -- As the primary key argument(s)
334   -- are specified as an OUT's we must set these values.
335   --
336   p_dflt_fund_src_id := l_rec.dflt_fund_src_id;
337   p_object_version_number := l_rec.object_version_number;
338   --
339   hr_utility.set_location(' Leaving:'||l_proc, 10);
340 End ins;
341 --
342 end pqh_dfs_ins;