DBA Data[Home] [Help]

PACKAGE BODY: APPS.PQH_BGT_DEL

Source


1 Package Body pqh_bgt_del as
2 /* $Header: pqbgtrhi.pkb 120.1 2005/09/21 03:11:10 hmehta noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- |                     Private Global Definitions                           |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package  varchar2(33)	:= '  pqh_bgt_del.';  -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------------------------------< delete_dml >------------------------------|
12 -- ----------------------------------------------------------------------------
13 -- {Start Of Comments}
14 --
15 -- Description:
16 --   This procedure controls the actual dml delete logic. The functions of
17 --   this procedure are as follows:
18 --   2) To delete the specified row from the schema using the primary key in
19 --      the predicates.
20 --   3) To trap any constraint violations that may have occurred.
21 --   4) To raise any other errors.
22 --
23 -- Prerequisites:
24 --   This is an internal private procedure which must be called from the del
25 --   procedure.
26 --
27 -- In Parameters:
28 --   A Pl/Sql record structre.
29 --
30 -- Post Success:
31 --   The specified row will be delete from the schema.
32 --
33 -- Post Failure:
34 --   If a child integrity constraint violation is raised the
35 --   constraint_error procedure will be called.
36 --
37 -- Developer Implementation Notes:
38 --   None.
39 --
40 -- Access Status:
41 --   Internal Row Handler Use Only.
42 --
43 -- {End Of Comments}
44 -- ----------------------------------------------------------------------------
45 Procedure delete_dml(p_rec in pqh_bgt_shd.g_rec_type) is
46 --
47   l_proc  varchar2(72) := g_package||'delete_dml';
48 --
49 Begin
50   hr_utility.set_location('Entering:'||l_proc, 5);
51   --
52   --
53   -- Delete the pqh_budgets row.
54   --
55   delete from pqh_budgets
56   where budget_id = p_rec.budget_id;
57   --
58   --
59   hr_utility.set_location(' Leaving:'||l_proc, 10);
60 --
61 Exception
62   When hr_api.child_integrity_violated then
63     -- Child integrity has been violated
64     pqh_bgt_shd.constraint_error
65       (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
66   When Others Then
67     Raise;
68 End delete_dml;
69 --
70 -- ----------------------------------------------------------------------------
71 -- |------------------------------< pre_delete >------------------------------|
72 -- ----------------------------------------------------------------------------
73 -- {Start Of Comments}
74 --
75 -- Description:
76 --   This private procedure contains any processing which is required before
77 --   the delete dml.
78 --
79 -- Prerequisites:
80 --   This is an internal procedure which is called from the del procedure.
81 --
82 -- In Parameters:
83 --   A Pl/Sql record structre.
84 --
85 -- Post Success:
86 --   Processing continues.
87 --
88 -- Post Failure:
89 --   If an error has occurred, an error message and exception will be raised
90 --   but not handled.
91 --
92 -- Developer Implementation Notes:
93 --   Any pre-processing required before the delete dml is issued should be
94 --   coded within this procedure. It is important to note that any 3rd party
95 --   maintenance should be reviewed before placing in this procedure.
96 --
97 -- Access Status:
98 --   Internal Row Handler Use Only.
99 --
100 -- {End Of Comments}
101 -- ----------------------------------------------------------------------------
102 Procedure pre_delete(p_rec in pqh_bgt_shd.g_rec_type) is
103 --
104   l_proc  varchar2(72) := g_package||'pre_delete';
105 --
106  l_budgets_rec   pqh_budgets%ROWTYPE;
107 
108  cursor csr_budget(p_budget_id IN number) is
109  select *
110  from pqh_budgets
111  where budget_id = p_budget_id;
112 --
113 Begin
114   hr_utility.set_location('Entering:'||l_proc, 5);
115   --
116     --
117       OPEN csr_budget(p_budget_id =>  p_rec.budget_id);
118         FETCH csr_budget INTO l_budgets_rec;
119       CLOSE csr_budget;
120 
121 
122   hr_utility.set_location('Status :'||l_budgets_rec.status, 6);
123 
124        IF NVL(l_budgets_rec.status,'X') = 'FROZEN' THEN
125          --
126          -- raise error as you cannot delete frozen budget
127             hr_utility.set_message(8302,'PQH_FROZEN_BUDGET');
128             hr_utility.raise_error;
129          --
130        ELSE
131          --
132          -- delete the record in pqh_budget_versions
133             delete from pqh_budget_versions
134             where budget_id = l_budgets_rec.budget_id;
135          --
136          -- delete the records from pqh_budget_gl_flex_maps
137             delete from pqh_budget_gl_flex_maps
138             where budget_id = l_budgets_rec.budget_id;
139          --
140          -- delete from pqh_bdgt_cmmtmnt_elmnts
141             delete from pqh_bdgt_cmmtmnt_elmnts
142             where  budget_id = l_budgets_rec.budget_id;
143          --
144        END IF;
145 
146   --
147   hr_utility.set_location(' Leaving:'||l_proc, 10);
148 End pre_delete;
149 --
150 -- ----------------------------------------------------------------------------
151 -- |-----------------------------< post_delete >------------------------------|
152 -- ----------------------------------------------------------------------------
153 -- {Start Of Comments}
154 --
155 -- Description:
156 --   This private procedure contains any processing which is required after the
157 --   delete dml.
158 --
159 -- Prerequisites:
160 --   This is an internal procedure which is called from the del procedure.
161 --
162 -- In Parameters:
163 --   A Pl/Sql record structre.
164 --
165 -- Post Success:
166 --   Processing continues.
167 --
168 -- Post Failure:
169 --   If an error has occurred, an error message and exception will be raised
170 --   but not handled.
171 --
172 -- Developer Implementation Notes:
173 --   Any post-processing required after the delete dml is issued should be
174 --   coded within this procedure. It is important to note that any 3rd party
175 --   maintenance should be reviewed before placing in this procedure.
176 --
177 -- Access Status:
178 --   Internal table Handler Use Only.
179 --
180 -- {End Of Comments}
181 -- ----------------------------------------------------------------------------
182 Procedure post_delete(
183 p_effective_date in date,p_rec in pqh_bgt_shd.g_rec_type) is
184 --
185   l_proc  varchar2(72) := g_package||'post_delete';
186 --
187 Begin
188   hr_utility.set_location('Entering:'||l_proc, 5);
189 --
190   --
191   -- Start of API User Hook for post_delete.
192   --
193   begin
194     --
195     pqh_bgt_rkd.after_delete
196       (
197   p_budget_id                     =>p_rec.budget_id
198  ,p_business_group_id_o           =>pqh_bgt_shd.g_old_rec.business_group_id
199  ,p_start_organization_id_o       =>pqh_bgt_shd.g_old_rec.start_organization_id
200  ,p_org_structure_version_id_o    =>pqh_bgt_shd.g_old_rec.org_structure_version_id
201  ,p_budgeted_entity_cd_o          =>pqh_bgt_shd.g_old_rec.budgeted_entity_cd
202  ,p_budget_style_cd_o             =>pqh_bgt_shd.g_old_rec.budget_style_cd
203  ,p_budget_name_o                 =>pqh_bgt_shd.g_old_rec.budget_name
204  ,p_period_set_name_o             =>pqh_bgt_shd.g_old_rec.period_set_name
205  ,p_budget_start_date_o           =>pqh_bgt_shd.g_old_rec.budget_start_date
206  ,p_budget_end_date_o             =>pqh_bgt_shd.g_old_rec.budget_end_date
207  ,p_gl_budget_name_o              =>pqh_bgt_shd.g_old_rec.gl_budget_name
208  ,P_psb_budget_flag_o             =>pqh_bgt_shd.g_old_rec.psb_budget_flag
209  ,p_transfer_to_gl_flag_o         =>pqh_bgt_shd.g_old_rec.transfer_to_gl_flag
210  ,p_transfer_to_grants_flag_o     =>pqh_bgt_shd.g_old_rec.transfer_to_grants_flag
211  ,p_status_o                      =>pqh_bgt_shd.g_old_rec.status
212  ,p_object_version_number_o       =>pqh_bgt_shd.g_old_rec.object_version_number
213  ,p_budget_unit1_id_o             =>pqh_bgt_shd.g_old_rec.budget_unit1_id
214  ,p_budget_unit2_id_o             =>pqh_bgt_shd.g_old_rec.budget_unit2_id
215  ,p_budget_unit3_id_o             =>pqh_bgt_shd.g_old_rec.budget_unit3_id
216  ,p_gl_set_of_books_id_o          =>pqh_bgt_shd.g_old_rec.gl_set_of_books_id
217  ,p_budget_unit1_aggregate_o      =>pqh_bgt_shd.g_old_rec.budget_unit1_aggregate
218  ,p_budget_unit2_aggregate_o      =>pqh_bgt_shd.g_old_rec.budget_unit2_aggregate
219  ,p_budget_unit3_aggregate_o      =>pqh_bgt_shd.g_old_rec.budget_unit3_aggregate
220  ,p_position_control_flag_o       =>pqh_bgt_shd.g_old_rec.position_control_flag
221  ,p_valid_grade_reqd_flag_o       =>pqh_bgt_shd.g_old_rec.valid_grade_reqd_flag
222  ,p_currency_code_o               =>pqh_bgt_shd.g_old_rec.currency_code
223  ,p_dflt_budget_set_id_o          =>pqh_bgt_shd.g_old_rec.dflt_budget_set_id
224       );
225     --
226   exception
227     --
228     when hr_api.cannot_find_prog_unit then
229       --
230       hr_api.cannot_find_prog_unit_error
231         (p_module_name => 'pqh_budgets'
232         ,p_hook_type   => 'AD');
233       --
234   end;
235   --
236   -- End of API User Hook for post_delete.
237   --
238   --
239   hr_utility.set_location(' Leaving:'||l_proc, 10);
240 End post_delete;
241 --
242 -- ----------------------------------------------------------------------------
243 -- |---------------------------------< del >----------------------------------|
244 -- ----------------------------------------------------------------------------
245 Procedure del
246   (
247   p_effective_date in date,
248   p_rec	      in pqh_bgt_shd.g_rec_type
249   ) is
250 --
251   l_proc  varchar2(72) := g_package||'del';
252 --
253 Begin
254   hr_utility.set_location('Entering:'||l_proc, 5);
255   --
256   -- We must lock the row which we need to delete.
257   --
258   pqh_bgt_shd.lck
259 	(
260 	p_rec.budget_id,
261 	p_rec.object_version_number
262 	);
263   --
264   -- Call the supporting delete validate operation
265   --
266   pqh_bgt_bus.delete_validate(p_rec
267   ,p_effective_date);
268   --
269   -- Call the supporting pre-delete operation
270   --
271   pre_delete(p_rec);
272   --
273   -- Delete the row.
274   --
275   delete_dml(p_rec);
276   --
277   -- Call the supporting post-delete operation
278   --
279   post_delete(
280 p_effective_date,p_rec);
281 End del;
282 --
283 -- ----------------------------------------------------------------------------
284 -- |---------------------------------< del >----------------------------------|
285 -- ----------------------------------------------------------------------------
286 Procedure del
287   (
288   p_effective_date in date,
289   p_budget_id                          in number,
290   p_object_version_number              in number
291   ) is
292 --
293   l_rec	  pqh_bgt_shd.g_rec_type;
294   l_proc  varchar2(72) := g_package||'del';
295 --
296 Begin
297   hr_utility.set_location('Entering:'||l_proc, 5);
298   --
299   -- As the delete procedure accepts a plsql record structure we do need to
300   -- convert the  arguments into the record structure.
301   -- We don't need to call the supplied conversion argument routine as we
302   -- only need a few attributes.
303   --
304   l_rec.budget_id:= p_budget_id;
305   l_rec.object_version_number := p_object_version_number;
306   --
307   -- Having converted the arguments into the pqh_bgt_rec
308   -- plsql record structure we must call the corresponding entity
309   -- business process
310   --
311   del(
312     p_effective_date,l_rec);
313   --
314   hr_utility.set_location(' Leaving:'||l_proc, 10);
315 End del;
316 --
317 end pqh_bgt_del;