1 PACKAGE BODY FA_DELETION_PVT as
2 /* $Header: FAVDELB.pls 120.11 2010/02/12 05:38:31 anujain ship $ */
3
4 FUNCTION do_validation
5 (px_asset_hdr_rec IN OUT NOCOPY FA_API_TYPES.asset_hdr_rec_type,
6 p_asset_type_rec IN FA_API_TYPES.asset_type_rec_type,
7 p_asset_desc_rec IN FA_API_TYPES.asset_desc_rec_type,
8 p_asset_cat_rec IN FA_API_TYPES.asset_cat_rec_type,
9 p_log_level_rec IN FA_API_TYPES.log_level_rec_type ) RETURN BOOLEAN IS
10
11 l_count NUMBER;
12
13 l_calling_fn varchar2(35) := 'fa_deletion_pvt.do_validation';
14 del_err EXCEPTION;
15
16 BEGIN
17
18 -- currently only restriction are the following:
19 -- 1) that assets can be deleted only in period of addition (unless skipping validation)
20 -- 2) asset is not a parent asset
21 -- 3) no add-to-asset lines exists in interface
22 -- 3) that asset has never been assigned to a group
23 -- 4) that group asset has never had any members
24 -- 5) must not be attached to a lease
25 --
26 -- the first several only matter if the book is still active - otherwise
27 -- corruption from missing foreign keys doesn't matter
28
29 if (fa_cache_pkg.fazcbc_record.date_ineffective is null) then
30
31 if (px_asset_hdr_rec.period_of_addition <> 'Y') then
32 fa_srvr_msg.add_message
33 (calling_fn => l_calling_fn,
34 name => 'FA_ADD_CANT_DELETE'
35 ,p_log_level_rec => p_log_level_rec);
36 raise del_err;
37 end if;
38
39 --5738269
40 select count(1)
41 into l_count
42 from fa_additions_b
43 where parent_asset_id = px_asset_hdr_rec.asset_id;
44
45 IF (l_count > 0) THEN
46 fa_srvr_msg.add_message
47 (calling_fn => l_calling_fn,
48 name => 'FA_ADD_CANT_DELETE_PARENT'
49 ,p_log_level_rec => p_log_level_rec);
50 raise del_err;
51 END IF;
52 --reset
53 l_count :=0;
54
55 if (p_asset_type_rec.asset_type = 'GROUP') then
56
57 select count(*)
58 into l_count
59 from fa_books
60 where book_type_code = px_asset_hdr_rec.book_type_code
61 and group_asset_id = px_asset_hdr_rec.asset_id;
62
63 if (l_count > 0) then
64 fa_srvr_msg.add_message
65 (calling_fn => l_calling_fn,
66 name => '***FA_DELETE_GORUP_ASSET***'
67 ,p_log_level_rec => p_log_level_rec);
68 raise del_err;
69 end if;
70
71 else
72
73 select count(*)
74 into l_count
75 from fa_books
76 where asset_id = px_asset_hdr_rec.asset_id
77 and book_type_code = px_asset_hdr_rec.book_type_code
78 and group_asset_id is not null;
79
80 if (l_count > 0) then
81 fa_srvr_msg.add_message
82 (calling_fn => l_calling_fn,
83 name => '***FA_DELETE_GORUP_MEMBER***'
84 ,p_log_level_rec => p_log_level_rec);
85 raise del_err;
86 end if;
87
88 select count(*)
89 into l_count
90 from fa_asset_invoices
91 where asset_id = px_asset_hdr_rec.asset_id
92 and feeder_system_name = 'ORACLE PROJECTS';
93
94 if l_count > 0 then
95 fa_srvr_msg.add_message
96 (calling_fn => l_calling_fn,
97 name => 'FA_ADD_CANT_DELETE_PROJECT'
98 ,p_log_level_rec => p_log_level_rec);
99 raise del_err;
100 end if;
101
102 end if;
103
104 end if; -- book effective
105
106 if (fa_cache_pkg.fazcat_record.category_type = 'LEASE' and
107 p_asset_desc_rec.lease_id is not null) then
108
109 SELECT count(*)
110 INTO l_count
111 FROM FA_ADDITIONS_B
112 WHERE LEASE_ID = p_asset_desc_rec.lease_id
113 AND ASSET_CATEGORY_ID =
114 ANY (SELECT CATEGORY_ID
115 FROM FA_CATEGORIES
116 WHERE CATEGORY_TYPE = 'LEASEHOLD IMPROVEMENT');
117
118 if l_count > 0 then
119 -- can't delete asset
120 fa_srvr_msg.add_message
121 (calling_fn => l_calling_fn,
122 name => 'FA_ADD_DELETE_LHOLD'
123 ,p_log_level_rec => p_log_level_rec);
124 raise del_err;
125 end if;
126
127 end if;
128
129 -- SLA Note; we need to check both standard trxs and dists
130 -- hense the two executions
131
132 if (fa_cache_pkg.fazcbc_record.book_class = 'TAX') then
133
134 select count(*)
135 into l_count
136 from fa_transaction_headers th,
137 xla_transaction_entities en,
138 xla_events ev,
139 fa_book_controls bc
140 where bc.book_type_code = px_asset_hdr_rec.book_type_code
141 and th.book_type_code = bc.distribution_source_book
142 and th.asset_id = px_asset_hdr_rec.asset_id
143 and en.application_id = 140
144 and en.ledger_id = bc.set_of_books_id
145 and en.entity_code = 'TRANSACTIONS'
146 and nvl(en.source_id_int_1, (-99)) = th.transaction_header_id
147 and en.valuation_method = px_asset_hdr_rec.book_type_code
148 and ev.application_id = 140
149 and ev.entity_id = en.entity_id
150 and ev.event_status_code = 'P';
151
152 if (l_count > 0) then
153
154 -- can't delete asset
155 fa_srvr_msg.add_message
156 (calling_fn => l_calling_fn,
157 name => 'FA_ADD_CANT_DELETE');
158 raise del_err;
159
160 end if;
161
162 end if;
163
164 select count(*)
165 into l_count
166 from fa_transaction_headers th,
167 xla_transaction_entities en,
168 xla_events ev,
169 fa_book_controls bc
170 where bc.book_type_code = px_asset_hdr_rec.book_type_code
171 and th.book_type_code = px_asset_hdr_rec.book_type_code
172 and th.asset_id = px_asset_hdr_rec.asset_id
173 and en.application_id = 140
174 and en.ledger_id = bc.set_of_books_id
175 and en.entity_code = 'TRANSACTIONS'
176 and nvl(en.source_id_int_1, (-99)) = th.transaction_header_id
177 and en.valuation_method = px_asset_hdr_rec.book_type_code
178 and ev.application_id = 140
179 and ev.entity_id = en.entity_id
180 and ev.event_status_code = 'P';
181
182 if (l_count > 0) then
183
184 -- can't delete asset
185 fa_srvr_msg.add_message
186 (calling_fn => l_calling_fn,
187 name => 'FA_ADD_CANT_DELETE');
188 raise del_err;
189
190 end if;
191
192 select count(*)
193 into l_count
194 from xla_transaction_entities en,
195 xla_events ev,
196 fa_book_controls bc
197 where bc.distribution_source_book = px_asset_hdr_rec.book_type_code
198 and en.application_id = 140
199 and en.ledger_id = bc.set_of_books_id
200 and en.entity_code = 'DEPRECIATION'
201 and nvl(en.source_id_int_1, (-99)) = px_asset_hdr_rec.asset_id
202 and nvl(en.source_id_char_1, ' ') = bc.book_type_code
203 and ev.application_id = 140
204 and ev.entity_id = en.entity_id
205 and ev.event_status_code = 'P';
206
207 if (l_count > 0) then
208
209 -- can't delete asset
210 fa_srvr_msg.add_message
211 (calling_fn => l_calling_fn,
212 name => 'FA_ADD_CANT_DELETE');
213 raise del_err;
214
215 end if;
216
217 -- BUG# 8554742
218 -- removing staus check as we need to prevent deletion
219 -- of any asset involved in inter-asset transaction
220 select count(*)
221 into l_count
222 from fa_transaction_headers th,
223 xla_transaction_entities en,
224 xla_events ev,
225 fa_book_controls bc
226 where bc.distribution_source_book = px_asset_hdr_rec.book_type_code
227 and th.book_type_code = bc.book_type_code
228 and th.asset_id = px_asset_hdr_rec.asset_id
229 and en.application_id = 140
230 and en.ledger_id = bc.set_of_books_id
231 and en.entity_code = 'INTER_ASSET_TRANSACTIONS'
232 and nvl(en.source_id_int_1, (-99)) = th.trx_reference_id
233 and en.valuation_method = px_asset_hdr_rec.book_type_code
234 and ev.application_id = 140
235 and ev.entity_id = en.entity_id;
236
237 if (l_count > 0) then
238
239 -- can't delete asset
240 fa_srvr_msg.add_message
241 (calling_fn => l_calling_fn,
242 name => 'FA_ADD_CANT_DELETE');
243 raise del_err;
244
245 end if;
246
247 return true;
248
249 EXCEPTION
250
251 WHEN DEL_ERR THEN
252 fa_srvr_msg.add_message(calling_fn => l_calling_fn
253 ,p_log_level_rec => p_log_level_rec);
254 return FALSE;
255
256 WHEN OTHERS THEN
257 fa_srvr_msg.add_sql_error(calling_fn => l_calling_fn
258 ,p_log_level_rec => p_log_level_rec);
259 return FALSE;
260
261 END do_validation;
262
263 -----------------------------------------------------------------------------
264
265 END FA_DELETION_PVT;
[Home] [Help]