1 PACKAGE BODY FA_DELETION_PVT as
2 /* $Header: FAVDELB.pls 120.4 2005/10/19 14:15:36 bridgway noship $ */
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 default null) 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 if (p_asset_type_rec.asset_type = 'GROUP') then
40
41 select count(*)
42 into l_count
43 from fa_books
44 where book_type_code = px_asset_hdr_rec.book_type_code
45 and group_asset_id = px_asset_hdr_rec.asset_id;
46
47 if (l_count > 0) then
48 fa_srvr_msg.add_message
49 (calling_fn => l_calling_fn,
50 name => '***FA_DELETE_GORUP_ASSET***'
51 ,p_log_level_rec => p_log_level_rec);
52 raise del_err;
53 end if;
54
55 else
56
57 select count(*)
58 into l_count
59 from fa_books
60 where asset_id = px_asset_hdr_rec.asset_id
61 and book_type_code = px_asset_hdr_rec.book_type_code
62 and group_asset_id is not null;
63
64 if (l_count > 0) then
65 fa_srvr_msg.add_message
66 (calling_fn => l_calling_fn,
67 name => '***FA_DELETE_GORUP_MEMBER***'
68 ,p_log_level_rec => p_log_level_rec);
69 raise del_err;
70 end if;
71
72 select count(*)
73 into l_count
74 from fa_asset_invoices
75 where asset_id = px_asset_hdr_rec.asset_id
76 and feeder_system_name = 'ORACLE PROJECTS';
77
78 if l_count > 0 then
79 fa_srvr_msg.add_message
80 (calling_fn => l_calling_fn,
81 name => 'FA_ADD_CANT_DELETE_PROJECT'
82 ,p_log_level_rec => p_log_level_rec);
83 raise del_err;
84 end if;
85
86 end if;
87
88 end if; -- book effective
89
90 if (fa_cache_pkg.fazcat_record.category_type = 'LEASE' and
91 p_asset_desc_rec.lease_id is not null) then
92
93 SELECT count(*)
94 INTO l_count
95 FROM FA_ADDITIONS_B
96 WHERE LEASE_ID = p_asset_desc_rec.lease_id
97 AND ASSET_CATEGORY_ID =
98 ANY (SELECT CATEGORY_ID
99 FROM FA_CATEGORIES
100 WHERE CATEGORY_TYPE = 'LEASEHOLD IMPROVEMENT');
101
102 if l_count > 0 then
103 -- can't delete asset
104 fa_srvr_msg.add_message
105 (calling_fn => l_calling_fn,
106 name => 'FA_ADD_DELETE_LHOLD'
107 ,p_log_level_rec => p_log_level_rec);
108 raise del_err;
109 end if;
110
111 end if;
112
113 -- SLA: note - putting this inside the corp book level
114 -- validation because for transactions, tax books
115 -- share the corporate book's th row and thus we have
116 -- no direct join between TH and EN...
117
118 if (fa_cache_pkg.fazcbc_record.book_class = 'CORPORATE') then
119
120 select count(*)
121 into l_count
122 from fa_transaction_headers th,
123 xla_transaction_entities en,
124 xla_events ev,
125 fa_book_controls bc
126 where bc.distribution_source_book = px_asset_hdr_rec.book_type_code
127 and th.book_type_code = bc.book_type_code
128 and th.asset_id = px_asset_hdr_rec.asset_id
129 and en.application_id = 140
130 and en.ledger_id = bc.set_of_books_id
131 and en.entity_code = 'TRANSACTIONS'
132 and nvl(en.source_id_int_1, (-99)) = th.transaction_header_id
133 and ev.application_id = 140
134 and ev.entity_id = en.entity_id
135 and ev.event_status_code = 'P';
136
137 if (l_count > 0) then
138
139 -- can't delete asset
140 fa_srvr_msg.add_message
141 (calling_fn => l_calling_fn,
142 name => 'FA_ADD_CANT_DELETE');
143 raise del_err;
144
145 end if;
146
147 select count(*)
148 into l_count
149 from xla_transaction_entities en,
150 xla_events ev,
151 fa_book_controls bc
152 where bc.distribution_source_book = px_asset_hdr_rec.book_type_code
153 and en.application_id = 140
154 and en.ledger_id = bc.set_of_books_id
155 and en.entity_code = 'DEPRECIATION'
156 and nvl(en.source_id_int_1, (-99)) = px_asset_hdr_rec.asset_id
157 and nvl(en.source_id_char_1, ' ') = bc.book_type_code
158 and ev.application_id = 140
159 and ev.entity_id = en.entity_id
160 and ev.event_status_code = 'P';
161
162 if (l_count > 0) then
163
164 -- can't delete asset
165 fa_srvr_msg.add_message
166 (calling_fn => l_calling_fn,
167 name => 'FA_ADD_CANT_DELETE');
168 raise del_err;
169
170 end if;
171
172 select count(*)
173 into l_count
174 from fa_transaction_headers th,
175 xla_transaction_entities en,
176 xla_events ev,
177 fa_book_controls bc
178 where bc.distribution_source_book = px_asset_hdr_rec.book_type_code
179 and th.book_type_code = bc.book_type_code
180 and th.asset_id = px_asset_hdr_rec.asset_id
181 and en.application_id = 140
182 and en.ledger_id = bc.set_of_books_id
183 and en.entity_code = 'INTER_ASSET_TRANSACTIONS'
184 and nvl(en.source_id_int_1, (-99)) = th.trx_reference_id
185 and ev.application_id = 140
186 and ev.entity_id = en.entity_id
187 and ev.event_status_code = 'P';
188
189 if (l_count > 0) then
190
191 -- can't delete asset
192 fa_srvr_msg.add_message
193 (calling_fn => l_calling_fn,
194 name => 'FA_ADD_CANT_DELETE');
195 raise del_err;
196
197 end if;
198
199 end if;
200
201 return true;
202
203 EXCEPTION
204
205 WHEN DEL_ERR THEN
206 fa_srvr_msg.add_message(calling_fn => l_calling_fn
207 ,p_log_level_rec => p_log_level_rec);
208 return FALSE;
209
210 WHEN OTHERS THEN
211 fa_srvr_msg.add_sql_error(calling_fn => l_calling_fn
212 ,p_log_level_rec => p_log_level_rec);
213 return FALSE;
214
215 END do_validation;
216
217 -----------------------------------------------------------------------------
218
219 END FA_DELETION_PVT;
[Home] [Help]