1 PACKAGE BODY FA_DELETION_PUB as
2 /* $Header: FAPDELB.pls 120.8 2005/09/14 19:18:33 bridgway noship $ */
3
4 --*********************** Global constants ******************************--
5
6 G_PKG_NAME CONSTANT varchar2(30) := 'FA_DELETION_PUB';
7 G_API_NAME CONSTANT varchar2(30) := 'Deletion API';
8 G_API_VERSION CONSTANT number := 1.0;
9
10 g_log_level_rec FA_API_TYPES.log_level_rec_type;
11
12 --*********************** Private functions ******************************--
13
14 -- private declaration for books (mrc) wrapper
15
16 FUNCTION do_all_books
17 (px_asset_hdr_rec IN OUT NOCOPY FA_API_TYPES.asset_hdr_rec_type,
18 p_asset_desc_rec IN FA_API_TYPES.asset_desc_rec_type,
19 p_asset_type_rec IN FA_API_TYPES.asset_type_rec_type,
20 p_asset_cat_rec IN FA_API_TYPES.asset_cat_rec_type,
21 p_validation_level IN NUMBER,
22 p_log_level_rec IN fa_api_types.log_level_rec_type default null) RETURN BOOLEAN;
23
24
25 --*********************** Public procedures ******************************--
26
27 PROCEDURE do_delete
28 (p_api_version IN NUMBER,
29 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
30 p_commit IN VARCHAR2 := FND_API.G_FALSE,
31 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
32 p_calling_fn IN VARCHAR2,
33 x_return_status OUT NOCOPY VARCHAR2,
34 x_msg_count OUT NOCOPY NUMBER,
35 x_msg_data OUT NOCOPY VARCHAR2,
36 px_asset_hdr_rec IN OUT NOCOPY FA_API_TYPES.asset_hdr_rec_type) IS
37
38 CURSOR c_tax_books IS
39 select distinct book_type_code
40 from fa_books
41 where asset_id = px_asset_hdr_rec.asset_id
42 /*code fix for bug no.3768406.Changed the field from date_effective to date_ineffective*/
43 and date_ineffective is null;
44
45 l_reporting_flag varchar2(1);
46 l_inv_count number := 0;
47 l_rate_count number := 0;
48 l_deprn_count number := 0;
49 l_count number := 0;
50
51 l_asset_desc_rec FA_API_TYPES.asset_desc_rec_type;
52 l_asset_type_rec FA_API_TYPES.asset_type_rec_type;
53 l_asset_cat_rec FA_API_TYPES.asset_cat_rec_type;
54
55
56 -- used to store original sob info upon entry into api
57 l_orig_set_of_books_id number;
58 l_orig_currency_context varchar2(64);
59
60 -- used for tax book loop
61 l_asset_hdr_rec FA_API_TYPES.asset_hdr_rec_type;
62 l_tax_book_tbl FA_CACHE_PKG.fazctbk_tbl_type;
63 l_tax_index NUMBER; -- index for tax loop
64
65 l_calling_fn VARCHAR2(35) := 'fa_deletion_pub.do_delete';
66 del_err EXCEPTION;
67
68
69 BEGIN
70
71 SAVEPOINT do_delete;
72
73 if (not g_log_level_rec.initialized) then
74 if (NOT fa_util_pub.get_log_level_rec (
75 x_log_level_rec => g_log_level_rec
76 )) then
77 raise del_err;
78 end if;
79 end if;
80
81 -- Initialize message list if p_init_msg_list is set to TRUE.
82 if (fnd_api.to_boolean(p_init_msg_list)) then
83 -- initialize error message stack.
84 fa_srvr_msg.init_server_message;
85
86 -- initialize debug message stack.
87 fa_debug_pkg.initialize;
88 end if;
89
90 -- Check version of the API
91 -- Standard call to check for API call compatibility.
92 if NOT fnd_api.compatible_api_call (
93 G_API_VERSION,
94 p_api_version,
95 G_API_NAME,
96 G_PKG_NAME) then
97 x_return_status := FND_API.G_RET_STS_ERROR;
98 raise del_err;
99 end if;
100
101 -- check to see if the asset is populated
102 if (px_asset_hdr_rec.asset_id is null) then
103 raise del_err;
104 end if;
105
106
107 -- check to see if the book is populated
108 -- if not assume corporate
109 if (px_asset_hdr_rec.book_type_code is null) then
110
111 select bk.book_type_code
112 into px_asset_hdr_rec.book_type_code
113 from fa_books bk,
114 fa_book_controls bc
115 where bk.asset_id = px_asset_hdr_rec.asset_id
116 and bk.date_ineffective is null
117 and bk.book_type_code = bc.book_type_code
118 and bc.book_class = 'CORPORATE';
119
120 end if;
121
122
123 -- call the cache for the primary transaction book
124 if NOT fa_cache_pkg.fazcbc(X_book => px_asset_hdr_rec.book_type_code,
125 p_log_level_rec => g_log_level_rec) then
126 raise del_err;
127 end if;
128
129 px_asset_hdr_rec.set_of_books_id := fa_cache_pkg.fazcbc_record.set_of_books_id;
130
131 -- verify the asset exist in the book already
132 if not FA_ASSET_VAL_PVT.validate_asset_book
133 (p_transaction_type_code => 'ADJUSTMENT',
134 p_book_type_code => px_asset_hdr_rec.book_type_code,
135 p_asset_id => px_asset_hdr_rec.asset_id,
136 p_calling_fn => l_calling_fn,
137 p_log_level_rec => g_log_level_rec) then
138 raise del_err;
139 end if;
140
141 -- get the current info for the primary book
142
143 fnd_profile.get ('GL_SET_OF_BKS_ID',l_orig_set_of_books_id);
144 l_orig_currency_context := SUBSTRB(USERENV('CLIENT_INFO'),45,10);
145
146 fnd_profile.put('GL_SET_OF_BKS_ID', px_asset_hdr_rec.set_of_books_id);
147 fnd_client_info.set_currency_context (px_asset_hdr_rec.set_of_books_id);
148
149 -- Account for transaction submitted from a responsibility
150 -- that is not tied to a SOB_ID by getting the value from
151 -- the book struct
152
153 -- Get the book type code P,R or N
154 if not fa_cache_pkg.fazcsob
155 (X_set_of_books_id => px_asset_hdr_rec.set_of_books_id,
156 X_mrc_sob_type_code => l_reporting_flag,
157 p_log_level_rec => g_log_level_rec) then
158 raise del_err;
159 end if;
160
161 -- Error out if the program is submitted from the Reporting Responsibility
162 -- No transaction permitted directly on reporting books.
163
164 IF l_reporting_flag = 'R' THEN
165 fa_srvr_msg.add_message
166 (calling_fn => l_calling_fn,
167 name => 'MRC_OSP_INVALID_BOOK_TYPE'
168 ,p_log_level_rec => g_log_level_rec);
169 raise del_err;
170 END IF;
171
172 -- end initial MRC validation
173
174 -- pop the structs for the non-fin information needed for trx
175 if not FA_UTIL_PVT.get_asset_desc_rec
176 (p_asset_hdr_rec => px_asset_hdr_rec,
177 px_asset_desc_rec => l_asset_desc_rec,
178 p_log_level_rec => g_log_level_rec) then
179 raise del_err;
180 end if;
181
182 if not FA_UTIL_PVT.get_asset_type_rec
183 (p_asset_hdr_rec => px_asset_hdr_rec,
184 px_asset_type_rec => l_asset_type_rec,
185 p_date_effective => null,
186 p_log_level_rec => g_log_level_rec) then
187 raise del_err;
188 end if;
189
190 if not FA_UTIL_PVT.get_asset_cat_rec
191 (p_asset_hdr_rec => px_asset_hdr_rec,
192 px_asset_cat_rec => l_asset_cat_rec,
193 p_date_effective => null,
194 p_log_level_rec => g_log_level_rec) then
195 raise del_err;
196 end if;
197
198
199 -- cache the category info
200 if not fa_cache_pkg.fazcat(X_cat_id => l_asset_cat_rec.category_id
201 ,p_log_level_rec => g_log_level_rec) then
202 raise del_err;
203 end if;
204
205
206 -- call the mrc wrapper for the transaction book
207 if not do_all_books
208 (px_asset_hdr_rec => px_asset_hdr_rec,
209 p_asset_type_rec => l_asset_type_rec,
210 p_asset_desc_rec => l_asset_desc_rec,
211 p_asset_cat_rec => l_asset_cat_rec,
212 p_validation_level => p_validation_level,
213 p_log_level_rec => g_log_level_rec)then
214 raise del_err;
215 end if;
216
217 if (fa_cache_pkg.fazcbc_record.book_class = 'CORPORATE') then
218
219 -- note: don't want to use fazctbk cache here
220 -- because the asset may exist in ineffective books
221
222 for l_tax_rec in c_tax_books loop
223
224 l_asset_hdr_rec := px_asset_hdr_rec;
225 l_asset_hdr_rec.book_type_code := l_tax_rec.book_type_code;
226
227 -- cache the book information for the tax book
228 if (NOT fa_cache_pkg.fazcbc(X_book => l_tax_rec.book_type_code
229 ,p_log_level_rec => g_log_level_rec)) then
230 raise del_err;
231 end if;
232
233
234 -- set the gl sob info for the primary tax book
235 fnd_profile.put('GL_SET_OF_BKS_ID', l_asset_hdr_rec.set_of_books_id);
236 fnd_client_info.set_currency_context (l_asset_hdr_rec.set_of_books_id);
237
238 if not do_all_books
239 (px_asset_hdr_rec => l_asset_hdr_rec , -- tax
240 p_asset_type_rec => l_asset_type_rec,
241 p_asset_desc_rec => l_asset_desc_rec,
242 p_asset_cat_rec => l_asset_cat_rec,
243 p_validation_level => p_validation_level,
244 p_log_level_rec => g_log_level_rec) then
245 raise del_err;
246 end if;
247
248 end loop; -- tax books
249
250 end if; -- corporate book
251
252 -- commit if p_commit is TRUE.
253 if (fnd_api.to_boolean (p_commit)) then
254 COMMIT WORK;
255 end if;
256
257 x_return_status := FND_API.G_RET_STS_SUCCESS;
258
259
260 EXCEPTION
261
262 when del_err then
263 ROLLBACK TO do_delete;
264
265 fnd_profile.put('GL_SET_OF_BKS_ID', l_orig_set_of_books_id);
266 fnd_client_info.set_currency_context (l_orig_currency_context);
267
268 fa_srvr_msg.add_message(calling_fn => l_calling_fn
269 ,p_log_level_rec => g_log_level_rec);
270
271 -- do not retrieve / clear messaging when this is being called
272 -- from reclass api - allow calling util to dump them
273 FND_MSG_PUB.count_and_get (
274 p_count => x_msg_count,
275 p_data => x_msg_data
276 );
277 x_return_status := FND_API.G_RET_STS_ERROR;
278
279 when others then
280 ROLLBACK TO do_delete;
281
282 fnd_profile.put('GL_SET_OF_BKS_ID', l_orig_set_of_books_id);
283 fnd_client_info.set_currency_context (l_orig_currency_context);
284
285 fa_srvr_msg.add_sql_error(
286 calling_fn => l_calling_fn
287 ,p_log_level_rec => g_log_level_rec);
288
289 -- do not retrieve / clear messaging when this is being called
290 -- from reclass api - allow calling util to dump them
291 FND_MSG_PUB.count_and_get (
292 p_count => x_msg_count,
293 p_data => x_msg_data
294 );
295
296 x_return_status := FND_API.G_RET_STS_ERROR;
297
298 END do_delete;
299
300 -----------------------------------------------------------------------------
301
302 -- Books (MRC) Wrapper - called from public API above
303 --
304 -- For non mrc books, this just calls the private API with provided params
305 -- For MRC, it processes the primary and then loops through each reporting
306 -- book calling the private api for each.
307
308
309 FUNCTION do_all_books
310 (px_asset_hdr_rec IN OUT NOCOPY FA_API_TYPES.asset_hdr_rec_type,
311 p_asset_desc_rec IN FA_API_TYPES.asset_desc_rec_type,
312 p_asset_type_rec IN FA_API_TYPES.asset_type_rec_type,
313 p_asset_cat_rec IN FA_API_TYPES.asset_cat_rec_type,
314 p_validation_level IN NUMBER,
315 p_log_level_rec IN fa_api_types.log_level_rec_type default null) RETURN BOOLEAN IS
316
317 -- used for calling private api for reporting books
318 l_asset_hdr_rec FA_API_TYPES.asset_hdr_rec_type;
319
320 l_period_rec FA_API_TYPES.period_rec_type;
321 l_sob_tbl FA_CACHE_PKG.fazcrsob_sob_tbl_type;
322
323 -- used for local runs
324 l_responsibility_id number;
325 l_application_id number;
326
327 l_rowid varchar2(120);
328
329 l_calling_fn varchar2(30) := 'fa_delete_pub.do_all_books';
330 del_err EXCEPTION;
331
332 -- Added as a result of High Cost SQL drill bugfix 3116047 msiddiqu
333 Cursor C1 is
334 SELECT INVOICE_TRANSACTION_ID_IN,
335 INVOICE_TRANSACTION_ID_OUT
336 FROM FA_ASSET_INVOICES
337 WHERE ASSET_ID = px_asset_hdr_rec.asset_id;
338
339 BEGIN
340
341 -- only call transaction approval
342 -- BUG# 2247404 and 2230178 - call regardless if from a mass request
343 if not FA_TRX_APPROVAL_PKG.faxcat
344 (X_book => px_asset_hdr_rec.book_type_code,
345 X_asset_id => px_asset_hdr_rec.asset_id,
346 X_trx_type => 'DELETE',
347 X_trx_date => sysdate,
348 X_init_message_flag => 'NO',
349 p_log_level_rec => p_log_level_rec) then
350 raise del_err;
351 end if;
352
353 -- check if this is the period of addition - use absolute mode for adjustments
354 if not FA_ASSET_VAL_PVT.validate_period_of_addition
355 (p_asset_id => px_asset_hdr_rec.asset_id,
356 p_book => px_asset_hdr_rec.book_type_code,
357 p_mode => 'ABSOLUTE',
358 px_period_of_addition => px_asset_hdr_rec.period_of_addition,
359 p_log_level_rec => p_log_level_rec) then
360 raise del_err;
361 end if;
362
363 -- load the period struct for current period info
364 if not FA_UTIL_PVT.get_period_rec
365 (p_book => px_asset_hdr_rec.book_type_code,
366 p_effective_date => NULL,
367 x_period_rec => l_period_rec,
368 p_log_level_rec => p_log_level_rec) then
369 raise del_err;
370 end if;
371
372 -- handle needed validation
373 -- bypass if validation level <> FULL
374
375 if (p_validation_level = FND_API.G_VALID_LEVEL_FULL) then
376 if not fa_deletion_pvt.do_validation
377 (px_asset_hdr_rec => px_asset_hdr_rec,
378 p_asset_type_rec => p_asset_type_rec,
379 p_asset_desc_rec => p_asset_desc_rec,
380 p_asset_cat_rec => p_asset_cat_rec,
381 p_log_level_rec => p_log_level_rec) then
382 raise del_err;
383 end if;
384 end if;
385
386
387 DELETE FROM FA_ADJUSTMENTS
388 WHERE Asset_Id = px_asset_hdr_rec.asset_id
389 AND book_Type_Code = px_asset_hdr_rec.book_type_code;
390
391 DELETE FROM FA_BOOKS
392 WHERE Asset_Id = px_asset_hdr_rec.asset_id
393 AND book_Type_Code = px_asset_hdr_rec.book_type_code;
394
395 DELETE FROM FA_DEPRN_DETAIL
396 WHERE Asset_Id = px_asset_hdr_rec.asset_id
397 AND book_Type_Code = px_asset_hdr_rec.book_type_code;
398
399 DELETE FROM FA_DEPRN_SUMMARY
400 WHERE Asset_Id = px_asset_hdr_rec.asset_id
401 AND book_Type_Code = px_asset_hdr_rec.book_type_code;
402
403 DELETE FROM FA_RETIREMENTS
404 WHERE Asset_Id = px_asset_hdr_rec.asset_id
405 AND book_Type_Code = px_asset_hdr_rec.book_type_code;
406
407 DELETE FROM FA_TRANSACTION_HEADERS
408 WHERE Asset_Id = px_asset_hdr_rec.asset_id
409 AND book_Type_Code = px_asset_hdr_rec.book_type_code;
410
411 -- mrc
412 DELETE FROM FA_MC_ADJUSTMENTS
413 WHERE Asset_Id = px_asset_hdr_rec.asset_id
414 AND book_Type_Code = px_asset_hdr_rec.book_type_code;
415
416 DELETE FROM FA_MC_BOOKS
417 WHERE Asset_Id = px_asset_hdr_rec.asset_id
418 AND book_Type_Code = px_asset_hdr_rec.book_type_code;
419
420 DELETE FROM FA_MC_DEPRN_DETAIL
421 WHERE Asset_Id = px_asset_hdr_rec.asset_id
422 AND book_Type_Code = px_asset_hdr_rec.book_type_code;
423
424 DELETE FROM FA_MC_DEPRN_SUMMARY
425 WHERE Asset_Id = px_asset_hdr_rec.asset_id
426 AND book_Type_Code = px_asset_hdr_rec.book_type_code;
427
428 DELETE FROM FA_MC_RETIREMENTS
429 WHERE Asset_Id = px_asset_hdr_rec.asset_id
430 AND book_Type_Code = px_asset_hdr_rec.book_type_code;
431
432
433 -- Asset hierarchy delete
434 if (nvl(fnd_profile.value('CRL-FA ENABLED'), 'N') = 'Y') then
435 fa_cua_wb_ext_pkg.facuas1(px_asset_hdr_rec.Asset_Id);
436 end if;
437
438
439 if (fa_cache_pkg.fazcbc_record.book_class = 'CORPORATE') then
440
441 -- Added transfer_header_id in the where clause
442 -- as a result of High Cost SQL drill bugfix 3116047 msiddiqu
443
444 DELETE FROM FA_TRANSFER_DETAILS
445 WHERE ( DISTRIBUTION_ID, transfer_header_id) IN
446 ( SELECT DISTRIBUTION_ID, transaction_header_id_in transfer_header_id
447 FROM FA_DISTRIBUTION_HISTORY
448 WHERE ASSET_ID = px_asset_hdr_rec.asset_id);
449
450 -- BUG# 4173695
451 -- removing this for performance and because invoice
452 -- transfers could affect other assets
453
454 -- For C1_rec in C1 Loop
455 -- DELETE FROM FA_INVOICE_TRANSACTIONS
456 -- WHERE INVOICE_TRANSACTION_ID = C1_rec.INVOICE_TRANSACTION_ID_IN
457 -- OR INVOICE_TRANSACTION_ID = C1_rec.INVOICE_TRANSACTION_ID_OUT;
458 -- End Loop;
459
460 DELETE FROM FA_DISTRIBUTION_HISTORY
461 WHERE Asset_Id = px_asset_hdr_rec.asset_id;
462
463 DELETE FROM FA_ASSET_HISTORY
464 WHERE Asset_Id = px_asset_hdr_rec.asset_id;
465
466 FA_ADDITIONS_PKG.DELETE_ROW
467 (X_Rowid => l_rowid,
468 X_Asset_id => px_asset_hdr_rec.asset_id,
469 X_Calling_Fn => l_calling_fn
470 ,p_log_level_rec => p_log_level_rec);
471
472 DELETE FROM FA_ASSET_INVOICES
473 WHERE Asset_Id = px_asset_hdr_rec.asset_id;
474
475 DELETE FROM FA_MC_ASSET_INVOICES
476 WHERE Asset_Id = px_asset_hdr_rec.asset_id;
477
478 DELETE FROM FA_PERIODIC_PRODUCTION WHERE ASSET_ID = px_asset_hdr_rec.asset_id;
479
480 DELETE FROM fa_add_warranties
481 WHERE asset_id= px_asset_hdr_rec.asset_id;
482
483 if ( (fa_cache_pkg.fazcat_record.category_type = 'LEASEHOLD IMPROVEMENT')
484 and (p_asset_desc_rec.lease_id is not null)) then
485 FA_LEASES_PKG.Delete_Row
486 (X_Lease_Id => p_asset_desc_rec.lease_id,
487 X_Calling_Fn => l_calling_fn
488 ,p_log_level_rec => p_log_level_rec);
489 end if;
490
491 elsif (fa_cache_pkg.fazcbc_record.book_class = 'BUDGET') then
492
493 DELETE FROM FA_CAPITAL_BUDGET WHERE ASSET_ID = px_asset_hdr_rec.asset_id ;
494
495 End if;
496
497 /*
498 DELETE FROM FA_ACE_BOOKS
499 WHERE ASSET_ID = px_asset_hdr_rec.asset_id;
500
501 DELETE FROM FA_BALANCES_REPORT
502 WHERE ASSET_ID = px_asset_hdr_rec.asset_id;
503
504 DELETE FROM FA_DEFERRED_DEPRN
505 WHERE ASSET_ID = px_asset_hdr_rec.asset_id;
506
507 DELETE FROM FA_MASS_REVALUATION_RULES
508 WHERE ASSET_ID = px_asset_hdr_rec.asset_id;
509
510 DELETE FROM FA_RESERVE_LEDGER
511 WHERE ASSET_ID = px_asset_hdr_rec.asset_id;
512 */
513
514
515 return true;
516
517
518 EXCEPTION
519
520 WHEN DEL_ERR THEN
521 fa_srvr_msg.add_message(calling_fn => l_calling_fn
522 ,p_log_level_rec => p_log_level_rec);
523 return FALSE;
524
525 WHEN OTHERS THEN
526 fa_srvr_msg.add_sql_error(calling_fn => l_calling_fn
527 ,p_log_level_rec => p_log_level_rec);
528 return FALSE;
529
530 END do_all_books;
531
532 -----------------------------------------------------------------------------
533
534 END FA_DELETION_PUB;
[Home] [Help]