DBA Data[Home] [Help]

PACKAGE BODY: APPS.PAY_RFM_DEL

Source


1 Package Body pay_rfm_del as
2 /* $Header: pyrfmrhi.pkb 120.0 2005/05/29 08:20 appldev noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- |                     Private Global Definitions                           |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package  varchar2(33) := '  pay_rfm_del.';  -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |----------------------------< dt_delete_dml >-----------------------------|
12 -- ----------------------------------------------------------------------------
13 -- {Start Of Comments}
14 --
15 -- Description:
16 --   This procedure controls the actual dml delete logic for the datetrack
17 --   delete modes: ZAP, DELETE, FUTURE_CHANGE and DELETE_NEXT_CHANGE. The
18 --   execution is as follows:
19 --   1) To set and unset the g_api_dml status as required (as we are about to
20 --      perform dml).
21 --   2) If the delete mode is DELETE_NEXT_CHANGE then delete where the
22 --      effective start date is equal to the validation start date.
23 --   3) If the delete mode is not DELETE_NEXT_CHANGE then delete
24 --      all rows for the entity where the effective start date is greater
25 --      than or equal to the validation start date.
26 --   4) To raise any errors.
27 --
28 -- Prerequisites:
29 --   This is an internal private procedure which must be called from the
30 --   delete_dml procedure.
31 --
32 -- In Parameters:
33 --   A Pl/Sql record structure.
34 --
35 -- Post Success:
36 --   The specified row will be delete from the schema.
37 --
38 -- Post Failure:
39 --   On the delete dml failure it is important to note that we always reset the
40 --   g_api_dml status to false.
41 --   If any other error is reported, the error will be raised after the
42 --   g_api_dml status is reset.
43 --
44 -- Developer Implementation Notes:
45 --   This is an internal private procedure which must be called from the
46 --   delete_dml procedure.
47 --
48 -- Access Status:
49 --   Internal Row Handler Use Only.
50 --
51 -- {End Of Comments}
52 -- ----------------------------------------------------------------------------
53 Procedure dt_delete_dml
54   (p_rec                     in out nocopy pay_rfm_shd.g_rec_type
55   ,p_effective_date          in date
56   ,p_datetrack_mode          in varchar2
57   ,p_validation_start_date   in date
58   ,p_validation_end_date     in date
59   ) is
60 --
61   l_proc        varchar2(72) := g_package||'dt_delete_dml';
62 --
63 Begin
64   hr_utility.set_location('Entering:'||l_proc, 5);
65   If (p_datetrack_mode = hr_api.g_delete_next_change) then
66     pay_rfm_shd.g_api_dml := true;  -- Set the api dml status
67     --
68     -- Delete the where the effective start date is equal
69     -- to the validation end date.
70     --
71     delete from pay_report_format_mappings_f
72     where       report_format_mapping_id = p_rec.report_format_mapping_id
73     and   effective_start_date = p_validation_start_date;
74     --
75     pay_rfm_shd.g_api_dml := false;   -- Unset the api dml status
76   Else
77     pay_rfm_shd.g_api_dml := true;  -- Set the api dml status
78     --
79     -- Delete the row(s) where the effective start date is greater than
80     -- or equal to the validation start date.
81     --
82     delete from pay_report_format_mappings_f
83     where        report_format_mapping_id = p_rec.report_format_mapping_id
84     and   effective_start_date >= p_validation_start_date;
85     --
86     pay_rfm_shd.g_api_dml := false;   -- Unset the api dml status
87   End If;
88   --
89   hr_utility.set_location(' Leaving:'||l_proc, 20);
90 --
91 Exception
92   When Others Then
93     pay_rfm_shd.g_api_dml := false;   -- Unset the api dml status
94     Raise;
95 --
96 End dt_delete_dml;
97 --
98 -- ----------------------------------------------------------------------------
99 -- ----------------------< delete_app_ownerships >----------------------------|
100 -- ----------------------------------------------------------------------------
101 --
102 -- Description:
103 --   Deletes row(s) from hr_application_ownerships depending on the mode that
104 --   the row handler has been called in.
105 --
106 -- ----------------------------------------------------------------------------
107 PROCEDURE delete_app_ownerships(p_pk_column  IN  varchar2
108                                ,p_pk_value   IN  varchar2
109                                ,p_datetrack_mode IN varchar2) IS
110 --
111 BEGIN
112   --
113   IF ((hr_startup_data_api_support.return_startup_mode
114                            IN ('STARTUP','GENERIC')) AND
115       p_datetrack_mode = 'ZAP') THEN
116      --
117      DELETE FROM hr_application_ownerships
118       WHERE key_name = p_pk_column
119         AND key_value = p_pk_value;
120      --
121   END IF;
122 END delete_app_ownerships;
123 --
124 -- ----------------------------------------------------------------------------
125 -- ----------------------< delete_app_ownerships >----------------------------|
126 -- ----------------------------------------------------------------------------
127 PROCEDURE delete_app_ownerships(p_pk_column IN varchar2
128                                ,p_pk_value  IN number
129                                ,p_datetrack_mode IN varchar2) IS
130 --
131 BEGIN
132   delete_app_ownerships(p_pk_column,
133                         to_char(p_pk_value),
134                         p_datetrack_mode
135                        );
136 END delete_app_ownerships;
137 --
138 -- ----------------------------------------------------------------------------
139 -- |------------------------------< delete_dml >------------------------------|
140 -- ----------------------------------------------------------------------------
141 Procedure delete_dml
142   (p_rec                     in out nocopy pay_rfm_shd.g_rec_type
143   ,p_effective_date          in date
144   ,p_datetrack_mode          in varchar2
145   ,p_validation_start_date   in date
146   ,p_validation_end_date     in date
147   ) is
148 --
149   l_proc        varchar2(72) := g_package||'delete_dml';
150 --
151 Begin
152   hr_utility.set_location('Entering:'||l_proc, 5);
153   --
154   pay_rfm_del.dt_delete_dml
155     (p_rec                   => p_rec
156     ,p_effective_date        => p_effective_date
157     ,p_datetrack_mode        => p_datetrack_mode
158     ,p_validation_start_date => p_validation_start_date
159     ,p_validation_end_date   => p_validation_end_date
160     );
161   --
162   hr_utility.set_location(' Leaving:'||l_proc, 10);
163 End delete_dml;
164 --
165 -- ----------------------------------------------------------------------------
166 -- |----------------------------< dt_pre_delete >-----------------------------|
167 -- ----------------------------------------------------------------------------
168 -- {Start Of Comments}
169 --
170 -- Description:
171 --   The dt_pre_delete process controls the execution of dml
172 --   for the datetrack modes: DELETE, FUTURE_CHANGE
173 --   and DELETE_NEXT_CHANGE only.
174 --
175 -- Prerequisites:
176 --   This is an internal procedure which is called from the pre_delete
177 --   procedure.
178 --
179 -- In Parameters:
180 --   A Pl/Sql record structure.
181 --
182 -- Post Success:
183 --   Processing continues.
184 --
185 -- Post Failure:
186 --   If an error has occurred, an error message and exception will be raised
187 --   but not handled.
188 --
189 -- Developer Implementation Notes:
190 --   This is an internal procedure which is required by Datetrack. Don't
191 --   remove or modify.
192 --
193 -- Access Status:
194 --   Internal Row Handler Use Only.
195 --
196 -- {End Of Comments}
197 -- ----------------------------------------------------------------------------
198 Procedure dt_pre_delete
199   (p_rec                     in out nocopy pay_rfm_shd.g_rec_type
200   ,p_effective_date          in date
201   ,p_datetrack_mode          in varchar2
202   ,p_validation_start_date   in date
203   ,p_validation_end_date     in date
204   ) is
205 --
206   l_proc        varchar2(72) := g_package||'dt_pre_delete';
207 --
208 Begin
209   hr_utility.set_location('Entering:'||l_proc, 5);
210   --
211   If (p_datetrack_mode <> hr_api.g_zap) then
212     --
213     p_rec.effective_start_date
214       := pay_rfm_shd.g_old_rec.effective_start_date;
215     --
216     If (p_datetrack_mode = hr_api.g_delete) then
217       p_rec.effective_end_date := p_validation_start_date - 1;
218     Else
219       p_rec.effective_end_date := p_validation_end_date;
220     End If;
221     --
222     -- Update the current effective end date record
223     --
224     pay_rfm_shd.upd_effective_end_date
225       (p_effective_date         => p_effective_date
226       ,p_base_key_value         => p_rec.report_format_mapping_id
227       ,p_new_effective_end_date => p_rec.effective_end_date
228       ,p_validation_start_date  => p_validation_start_date
229       ,p_validation_end_date    => p_validation_end_date
230       ,p_object_version_number            => p_rec.object_version_number
231       );
232   Else
233     p_rec.effective_start_date := null;
234     p_rec.effective_end_date   := null;
235   End If;
236   hr_utility.set_location(' Leaving:'||l_proc, 10);
237 End dt_pre_delete;
238 --
239 -- ----------------------------------------------------------------------------
240 -- |------------------------------< pre_delete >------------------------------|
241 -- ----------------------------------------------------------------------------
242 -- {Start Of Comments}
243 --
244 -- Description:
245 --   This private procedure contains any processing which is required before
246 --   the delete dml.
247 --
248 -- Prerequisites:
249 --   This is an internal procedure which is called from the del procedure.
250 --
251 -- In Parameters:
252 --   A Pl/Sql record structure.
253 --
254 -- Post Success:
255 --   Processing continues.
256 --
257 -- Post Failure:
258 --   If an error has occurred, an error message and exception will be raised
259 --   but not handled.
260 --
261 -- Developer Implementation Notes:
262 --   Any pre-processing required before the delete dml is issued should be
263 --   coded within this procedure. It is important to note that any 3rd party
264 --   maintenance should be reviewed before placing in this procedure. The call
265 --   to the dt_delete_dml procedure should NOT be removed.
266 --
267 -- Access Status:
268 --   Internal Row Handler Use Only.
269 --
270 -- {End Of Comments}
271 -- ----------------------------------------------------------------------------
272 Procedure pre_delete
273   (p_rec                   in out nocopy pay_rfm_shd.g_rec_type
274   ,p_effective_date        in date
275   ,p_datetrack_mode        in varchar2
276   ,p_validation_start_date in date
277   ,p_validation_end_date   in date
278   ) is
279 --
280   l_proc        varchar2(72) := g_package||'pre_delete';
281 --
282   --
283 --
284 Begin
285   hr_utility.set_location('Entering:'||l_proc, 5);
286   --
287 --
288   --
289   pay_rfm_del.dt_pre_delete
290     (p_rec                   => p_rec
291     ,p_effective_date        => p_effective_date
292     ,p_datetrack_mode        => p_datetrack_mode
293     ,p_validation_start_date => p_validation_start_date
294     ,p_validation_end_date   => p_validation_end_date
295     );
296   --
297   hr_utility.set_location(' Leaving:'||l_proc, 10);
298 End pre_delete;
299 --
300 -- ----------------------------------------------------------------------------
301 -- |----------------------------< post_delete >-------------------------------|
302 -- ----------------------------------------------------------------------------
303 -- {Start Of Comments}
304 --
305 -- Description:
306 --   This private procedure contains any processing which is required after
307 --   the delete dml.
308 --
309 -- Prerequisites:
310 --   This is an internal procedure which is called from the del procedure.
311 --
312 -- In Parameters:
313 --   A Pl/Sql record structure.
314 --
315 -- Post Success:
316 --   Processing continues.
317 --
318 -- Post Failure:
319 --   If an error has occurred, an error message and exception will be raised
320 --   but not handled.
321 --
322 -- Developer Implementation Notes:
323 --   Any post-processing required after the delete dml is issued should be
324 --   coded within this procedure. It is important to note that any 3rd party
325 --   maintenance should be reviewed before placing in this procedure.
326 --
327 -- Access Status:
328 --   Internal Row Handler Use Only.
329 --
330 -- {End Of Comments}
331 -- ----------------------------------------------------------------------------
332 Procedure post_delete
333   (p_rec                   in pay_rfm_shd.g_rec_type
334   ,p_effective_date        in date
335   ,p_datetrack_mode        in varchar2
336   ,p_validation_start_date in date
337   ,p_validation_end_date   in date
338   ) is
339 --
340   l_proc        varchar2(72) := g_package||'post_delete';
341 --
342 Begin
343   hr_utility.set_location('Entering:'||l_proc, 5);
344   --
345   --
346   hr_utility.set_location(' Leaving:'||l_proc, 10);
347 End post_delete;
348 --
349 -- ----------------------------------------------------------------------------
350 -- |---------------------------------< del >----------------------------------|
351 -- ----------------------------------------------------------------------------
352 Procedure del
353   (p_effective_date in     date
354   ,p_datetrack_mode in     varchar2
355   ,p_rec            in out nocopy pay_rfm_shd.g_rec_type
356   ) is
357 --
358   l_proc                        varchar2(72) := g_package||'del';
359   l_validation_start_date       date;
360   l_validation_end_date         date;
361 --
362 Begin
363   hr_utility.set_location('Entering:'||l_proc, 5);
364   --
365   -- Ensure that the DateTrack delete mode is valid
366   --
367   dt_api.validate_dt_del_mode(p_datetrack_mode => p_datetrack_mode);
368   --
369   -- We must lock the row which we need to delete.
370   --
371   pay_rfm_shd.lck
372     (p_effective_date                   => p_effective_date
373     ,p_datetrack_mode                   => p_datetrack_mode
374     ,p_report_format_mapping_id         => p_rec.report_format_mapping_id
375     ,p_object_version_number            => p_rec.object_version_number
376     ,p_validation_start_date            => l_validation_start_date
377     ,p_validation_end_date              => l_validation_end_date
378     );
379   --
380   -- Call the supporting delete validate operation
381   --
382   pay_rfm_bus.delete_validate
383     (p_rec                              => p_rec
387     ,p_validation_end_date              => l_validation_end_date
384     ,p_effective_date                   => p_effective_date
385     ,p_datetrack_mode                   => p_datetrack_mode
386     ,p_validation_start_date            => l_validation_start_date
388     );
389   --
390   -- Call to raise any errors on multi-message list
391   hr_multi_message.end_validation_set;
392   --
393   -- Call the supporting pre-delete operation
394   --
395   pay_rfm_del.pre_delete
396     (p_rec                              => p_rec
397     ,p_effective_date                   => p_effective_date
398     ,p_datetrack_mode                   => p_datetrack_mode
399     ,p_validation_start_date            => l_validation_start_date
400     ,p_validation_end_date              => l_validation_end_date
401     );
402   --
403   -- Delete the row.
404   --
405   pay_rfm_del.delete_dml
406     (p_rec                              => p_rec
407     ,p_effective_date                   => p_effective_date
408     ,p_datetrack_mode                   => p_datetrack_mode
409     ,p_validation_start_date            => l_validation_start_date
410     ,p_validation_end_date              => l_validation_end_date
411     );
412   -- Call the supporting post-delete operation
413   --
414   pay_rfm_del.post_delete
415     (p_rec                              => p_rec
416     ,p_effective_date                   => p_effective_date
417     ,p_datetrack_mode                   => p_datetrack_mode
418     ,p_validation_start_date            => l_validation_start_date
419     ,p_validation_end_date              => l_validation_end_date
420     );
421   --
422   -- Call to raise any errors on multi-message list
423   hr_multi_message.end_validation_set;
424   --
425   hr_utility.set_location(' Leaving:'||l_proc, 5);
426 End del;
427 --
428 -- ----------------------------------------------------------------------------
429 -- |--------------------------------< del >-----------------------------------|
430 -- ----------------------------------------------------------------------------
431 Procedure del
432   (p_effective_date                   in     date
433   ,p_datetrack_mode                   in     varchar2
434   ,p_report_format_mapping_id         in     number
435   ,p_object_version_number            in out nocopy number
436   ,p_effective_start_date                out nocopy date
437   ,p_effective_end_date                  out nocopy date
438   ) is
439 --
440   l_rec         pay_rfm_shd.g_rec_type;
441   l_proc        varchar2(72) := g_package||'del';
442 --
443 Begin
444   hr_utility.set_location('Entering:'||l_proc, 5);
445   --
446   -- As the delete procedure accepts a plsql record structure we do need to
447   -- convert the  arguments into the record structure.
448   -- We don't need to call the supplied conversion argument routine as we
449   -- only need a few attributes.
450   --
451   l_rec.report_format_mapping_id          := p_report_format_mapping_id;
452   l_rec.object_version_number     := p_object_version_number;
453   --
454   -- Having converted the arguments into the pay_rfm_rec
455   -- plsql record structure we must call the corresponding entity
456   -- business process
457   --
458   pay_rfm_del.del
459      (trunc(p_effective_date)
460      ,p_datetrack_mode
461      ,l_rec
462      );
463   --
464   --
465   -- Set the out arguments
466   --
467   p_object_version_number            := l_rec.object_version_number;
468   p_effective_start_date             := l_rec.effective_start_date;
469   p_effective_end_date               := l_rec.effective_end_date;
470   --
471   hr_utility.set_location(' Leaving:'||l_proc, 10);
472 End del;
473 --
474 end pay_rfm_del;