[Home] [Help]
PACKAGE BODY: APPS.FA_DET_ADD_PKG
Source
1 PACKAGE BODY FA_DET_ADD_PKG as
2 /* $Header: faxdadb.pls 120.7 2009/04/16 14:46:42 bridgway ship $ */
3
4 PROCEDURE Initialize(X_Asset_Id NUMBER,
5 X_PC_Fully_Ret IN OUT NOCOPY NUMBER,
6 X_Current_PC IN OUT NOCOPY NUMBER,
7 X_Transfer_In_PC IN OUT NOCOPY NUMBER,
8 X_Books_Cost IN OUT NOCOPY NUMBER,
9 X_Inv_Cost IN OUT NOCOPY NUMBER,
10 X_Deprn_Reserve IN OUT NOCOPY NUMBER,
11 X_Calling_Fn VARCHAR2,
12 p_log_level_rec IN FA_API_TYPES.log_level_rec_type) IS
13 lv_corp_book varchar2(30);
14 BEGIN
15 SELECT BK.Period_Counter_Fully_Retired,BC.Book_Type_Code
16 INTO X_PC_Fully_Ret, lv_corp_book
17 FROM FA_BOOKS BK, FA_BOOK_CONTROLS BC
18 WHERE BK.Asset_Id = X_Asset_Id
19 AND BK.Date_Ineffective IS NULL
20 AND BK.Book_Type_Code = BC.Book_Type_Code
21 AND BC.Book_Class = 'CORPORATE';
22 --
23 SELECT DP.Period_Counter
24 into X_Current_PC
25 FROM FA_DEPRN_PERIODS DP
26 WHERE DP.Book_Type_Code = lv_corp_book
27 AND DP.Period_Close_Date IS NULL;
28 --
29 SELECT DP.Period_Counter
30 INTO X_Transfer_In_PC
31 FROM FA_DEPRN_PERIODS DP, FA_TRANSACTION_HEADERS TH
32 WHERE DP.Book_Type_Code = lv_corp_book
33 AND TH.Book_Type_Code = lv_corp_book
34 AND TH.Asset_Id = X_Asset_Id
35 AND TH.Transaction_Type_Code = 'TRANSFER IN'
36 AND TH.Date_Effective between
37 DP.Period_Open_Date and nvl(DP.Period_Close_Date,sysdate);
38 --
39 if X_Transfer_In_PC < X_Current_PC then
40 -- can't update asset type, so don't bother getting
41 -- costs for further validation
42 X_Books_Cost := 0;
43 X_Inv_Cost := 0;
44 else
45 select deprn_reserve into X_deprn_reserve
46 from fa_deprn_summary
47 where asset_id = X_Asset_id
48 and book_type_code = lv_corp_book
49 and deprn_source_code = 'BOOKS';
50
51 select cost into X_Books_Cost
52 from fa_books
53 where asset_id = X_Asset_Id
54 and book_type_code = lv_corp_book
55 and date_ineffective is null;
56 --
57 begin
58 select nvl(sum(fixed_assets_cost),0)
59 into X_Inv_Cost
60 from fa_asset_invoices ai
61 where asset_id = X_Asset_Id;
62 exception
63 when no_data_found then X_Inv_Cost := 0;
64 end;
65 end if;
66 EXCEPTION
67 WHEN Others THEN
68 FA_STANDARD_PKG.RAISE_ERROR
69 (Called_Fn => 'FA_DET_ADD_PKG.INITIALIZE',
70 Calling_Fn => X_Calling_Fn
71 , p_log_level_rec => p_log_level_rec);
72 END Initialize;
73 --
74 FUNCTION Val_Reclass(X_Old_Cat_Id NUMBER,
75 X_New_Cat_Id NUMBER,
76 X_Asset_Id NUMBER,
77 X_Asset_Type VARCHAR2,
78 X_Old_Cap_Flag VARCHAR2,
79 X_Old_Cat_Type VARCHAR2,
80 X_New_Cat_Type IN OUT NOCOPY VARCHAR2,
81 X_Lease_Id NUMBER,
82 X_Calling_Fn VARCHAR2,
83 p_log_level_rec IN FA_API_TYPES.log_level_rec_type) RETURN BOOLEAN IS
84 lv_corp_book varchar2(30);
85 lv_new_cap_flag varchar2(3);
86 lv_count number := 0;
87 lv_mesg varchar2(50);
88 h_status boolean := false;
89 validation_error exception;
90 BEGIN
91 -- find corporate book
92 select bc.book_type_code into lv_corp_book
93 from fa_books bk, fa_book_controls bc
94 where bc.book_class = 'CORPORATE'
95 and bk.asset_id = X_Asset_Id
96 and bk.book_type_code = bc.book_type_code
97 and bk.date_ineffective is null;
98
99 -- validate new category
100 if (X_Calling_Fn = 'FA_ASSET_VAL.Validate_Reclass') then
101 -- called from form. don't use stacked mesg.
102 h_status := FA_FIN_ADD_PKG.CAT_VAL
103 (X_Book_Type_Code => lv_corp_book,
104 X_Asset_Type => X_Asset_Type,
105 X_Category_Id => X_New_Cat_Id,
106 X_Stack_Mesg => 'NO',
107 X_Calling_Fn => 'FA_DET_ADD_PKG.Val_Reclass'
108 , p_log_level_rec => p_log_level_rec);
109 else
110 h_status := FA_FIN_ADD_PKG.CAT_VAL
111 (X_Book_Type_Code => lv_corp_book,
112 X_Asset_Type => X_Asset_Type,
113 X_Category_Id => X_New_Cat_Id,
114 X_Stack_Mesg => 'YES',
115 X_Calling_Fn => 'FA_DET_ADD_PKG.Val_Reclass'
116 , p_log_level_rec => p_log_level_rec);
117 end if;
118
119 if (not h_status and x_calling_fn <> 'FA_ASSET_VAL.Validate_Reclass') then
120 -- if not invoked from form, then add error message to stack.
121 raise validation_error;
122 end if;
123
124 -- both categories must be capitalized or expensed types
125 --
126 select capitalize_flag, category_type
127 into lv_new_cap_flag, X_New_Cat_Type
128 from fa_categories
129 where category_id = X_New_Cat_Id;
130 --
131 if X_Old_Cap_Flag = 'YES' then
132 if lv_new_cap_flag = 'NO' then
133 lv_mesg := 'FA_ADD_RECLS_TO_EXPENSE';
134 raise validation_error;
135 end if;
136 elsif X_Old_Cap_Flag = 'NO' then
137 if lv_new_cap_flag = 'YES' then
138 lv_mesg := 'FA_ADD_RECLS_TO_CAP_ASSET';
139 raise validation_error;
140 end if;
141 end if;
142 -- also check lease stuff
143 -- for bug 3057767, added X_Asset_Id to join since it seems that you
144 -- ony need to remove the lease for this asset and not all assets that
145 -- have this lease when the asset is being reclassed.
146 if X_Old_Cat_Type = 'LEASE' and X_New_Cat_Type <> 'LEASE' then
147 select count(*) into lv_count
148 from fa_additions_b ad
149 where ad.asset_id = X_Asset_Id
150 and ad.lease_id = X_Lease_Id
151 and exists
152 ( select 'X'
153 from fa_categories_b cat
154 where cat.category_id = ad.asset_category_id
155 and cat.category_type = 'LEASEHOLD IMPROVEMENT');
156 --
157 if lv_count > 0 then
158 lv_mesg := 'FA_ADD_DELETE_LHOLD_BEFORE_RCL';
159 raise validation_error;
160 end if;
161 --
162 -- syoung: performance tuning: count(*) to count(1).
163 -- and inserted rownum condition.
164 select count(1) into lv_count
165 from fa_leases
166 where lease_id = X_Lease_Id
167 and rownum < 2;
168 --
169 if lv_count > 0 then
170 lv_mesg := 'FA_ADD_DELETE_LEASE_BEFORE_RCL';
171 raise validation_error;
172 end if;
173 end if;
174 --
175 -- no pending retirements
176 -- syoung: performance tuning: count(*) to count(1).
177 -- and inserted rownum condition.
178 -- syoung: do the following check if called from single reclass only.
179
180 /*******************************************************+
181 | Bug 1544755. |
182 | Added a join to fa_book_controls to insure that we |
183 | don't include disabled books. |
184 +*******************************************************/
185
186 if (X_Calling_Fn = 'FA_ASSET_VAL.Validate_Reclass') then
187 select count(1)
188 into lv_count
189 from fa_retirements fr,
190 fa_book_controls fbc
191 where fr.book_type_code = fbc.book_type_code
192 and fbc.date_ineffective is null
193 and fr.asset_id = X_Asset_Id
194 and fr.status in ('PENDING', 'REINSTATE', 'PARTIAL')
195 and rownum < 2;
196 --
197 if lv_count > 0 then
198 lv_mesg := 'FA_RET_PENDING_RETIREMENTS';
199 raise validation_error;
200 end if;
201 end if;
202
203 return (true);
204 --
205 EXCEPTION
206 WHEN Validation_Error THEN
207 if (X_calling_fn = 'FA_ASSET_VAL.Validate_Reclass') then
208 FA_STANDARD_PKG.RAISE_ERROR
209 (Called_Fn => 'FA_DET_ADD_PKG.VAL_RECLASS',
210 Calling_Fn => X_Calling_Fn,
211 Name => LV_Mesg
212 , p_log_level_rec => p_log_level_rec);
213 else
214 FA_SRVR_MSG.Add_Message
215 (Calling_Fn => 'FA_DET_ADD_PKG.VAL_RECLASS',
216 Name => lv_mesg
217 , p_log_level_rec => p_log_level_rec);
218 end if;
219 return (false);
220 WHEN Others THEN
221 if (X_calling_fn = 'FA_ASSET_VAL.Validate_Reclass') then
222 FA_STANDARD_PKG.RAISE_ERROR
223 (Called_Fn => 'FA_DET_ADD_PKG.VAL_RECLASS',
224 Calling_Fn => X_Calling_Fn
225 , p_log_level_rec => p_log_level_rec);
226 else
227 FA_SRVR_MSG.Add_SQL_Error
228 (Calling_Fn => 'FA_DET_ADD_PKG.VAL_RECLASS'
229 , p_log_level_rec => p_log_level_rec);
230 end if;
231 return (false);
232 END Val_Reclass;
233
234 PROCEDURE UPDATE_LEASE_DF(X_Lease_Id NUMBER,
235 X_Last_Update_Date DATE,
236 X_Last_Updated_By NUMBER,
237 X_Last_Update_Login NUMBER,
238 X_Attribute1 VARCHAR2,
239 X_Attribute2 VARCHAR2,
240 X_Attribute3 VARCHAR2,
241 X_Attribute4 VARCHAR2,
242 X_Attribute5 VARCHAR2,
243 X_Attribute6 VARCHAR2,
244 X_Attribute7 VARCHAR2,
245 X_Attribute8 VARCHAR2,
246 X_Attribute9 VARCHAR2,
247 X_Attribute10 VARCHAR2,
248 X_Attribute11 VARCHAR2,
249 X_Attribute12 VARCHAR2,
250 X_Attribute13 VARCHAR2,
251 X_Attribute14 VARCHAR2,
252 X_Attribute15 VARCHAR2,
253 X_Attribute_Category_Code VARCHAR2,
254 X_Return_Status OUT NOCOPY BOOLEAN,
255 X_Calling_Fn VARCHAR2,
256 p_log_level_rec IN FA_API_TYPES.log_level_rec_type)
257 IS
258
259 cursor LS_CUR is
260 select ls.rowid row_id,
261 lease_id,
262 lease_number,
263 lessor_id,
264 description,
265 last_update_date,
266 last_updated_by,
267 created_by,
268 creation_date,
269 last_update_login,
270 attribute1,
271 attribute2,
272 attribute3,
273 attribute4,
274 attribute5,
275 attribute6,
276 attribute7,
277 attribute8,
278 attribute9,
279 attribute10,
280 attribute11,
281 attribute12,
282 attribute13,
283 attribute14,
284 attribute15,
285 attribute_category_code,
286 fasb_lease_type,
287 cost_capitalized,
288 transfer_ownership,
289 bargain_purchase_option,
290 payment_schedule_id,
291 fair_value,
292 present_value,
293 lease_type,
294 asset_life,
295 lease_term,
296 currency_code
297 from fa_leases ls
298 where ls.lease_id = X_Lease_Id;
299 LS_ROW LS_CUR%ROWTYPE;
300 h_status boolean := false;
301 update_error exception;
302 BEGIN
303
304 if X_Lease_Id is not null then
305 OPEN LS_CUR;
306 FETCH LS_CUR INTO LS_ROW;
307
308 FA_LEASES_PKG.UPDATE_ROW(
309 X_Rowid => LS_ROW.Row_id,
310 X_Lease_Id => LS_ROW.Lease_Id,
311 X_Lease_Number => LS_ROW.Lease_Number,
312 X_Lessor_Id => LS_ROW.Lessor_Id,
313 X_Description => LS_ROW.Description,
314 X_Last_Update_Date => X_Last_Update_Date,
315 X_Last_Updated_By => X_Last_Updated_By,
316 X_Last_Update_Login=> X_Last_Update_Login,
317 X_Attribute1 => X_Attribute1,
318 X_Attribute2 => X_Attribute2,
319 X_Attribute3 => X_Attribute3,
320 X_Attribute4 => X_Attribute4,
321 X_Attribute5 => X_Attribute5,
322 X_Attribute6 => X_Attribute6,
323 X_Attribute7 => X_Attribute7,
324 X_Attribute8 => X_Attribute8,
325 X_Attribute9 => X_Attribute9,
326 X_Attribute10 => X_Attribute10,
327 X_Attribute11 => X_Attribute11,
328 X_Attribute12 => X_Attribute12,
329 X_Attribute13 => X_Attribute13,
330 X_Attribute14 => X_Attribute14,
331 X_Attribute15 => X_Attribute15,
332 X_Attribute_Category_Code =>
333 X_Attribute_Category_Code,
334 X_Return_Status => h_status,
335 X_Calling_Fn => 'FA_DET_ADD2_PKG.UPDATE_LEASE_DF'
336 , p_log_level_rec => p_log_level_rec);
337
338 if not h_status then
339 raise update_error;
340 end if;
341
342 end if;
343 X_Return_Status := true;
344 exception
345 when update_error then
346 FA_SRVR_MSG.Add_Message(
347 CALLING_FN => 'FA_DET_ADD2_PKG.Update_Lease_DF'
348 , p_log_level_rec => p_log_level_rec);
349 X_Return_Status := false;
350 when others then
351 FA_SRVR_MSG.Add_SQL_Error(
352 CALLING_FN => 'FA_DET_ADD2_PKG.Update_Lease_DF'
353 ,p_log_level_rec => p_log_level_rec);
354 X_Return_Status := false;
355 END Update_Lease_DF;
356
357 END FA_DET_ADD_PKG;