[Home] [Help]
PACKAGE BODY: APPS.IGI_IAC_WEBADI_PKG
Source
1 PACKAGE BODY IGI_IAC_WEBADI_PKG AS
2 -- $Header: igiiasab.pls 120.10.12000000.1 2007/08/01 16:18:55 npandya noship $
3 l_debug_level number:=FND_LOG.G_CURRENT_RUNTIME_LEVEL;
4
5 l_state_level number:=FND_LOG.LEVEL_STATEMENT;
6 l_proc_level number:=FND_LOG.LEVEL_PROCEDURE;
7 l_event_level number:=FND_LOG.LEVEL_EVENT;
8 l_excep_level number:=FND_LOG.LEVEL_EXCEPTION;
9 l_error_level number:=FND_LOG.LEVEL_ERROR;
10 l_unexp_level number:=FND_LOG.LEVEL_UNEXPECTED;
11
12 Cursor C_File_Transferred(
13 cp_file_name IN igi_iac_upload_headers.file_name%type) Is
14 Select 'X'
15 From igi_iac_upload_headers
16 Where file_name = cp_file_name
17 and status_flag = 'T';
18
19 Cursor C_Asset_Exists(cp_file_name IN igi_iac_upload_headers.file_name%type,
20 cp_asset_number IN fa_additions.asset_number%type) Is
21 Select hd.Book_Type_Code,
22 hd.Period_Counter,
23 hd.Currency_Code,
24 hd.Status_Flag Hdr_Status_Flag,
25 hd.Tolerance_Flag,
26 hd.Tolerance_Amount,
27 hd.Tolerance_Percent,
28 hd.Revaluation_Id,
29 ln.Asset_Id,
30 ln.Line_Num,
31 ln.Category_Id,
32 ln.Original_Cost,
33 ln.New_Cost,
34 ln.status_flag Line_Status_Flag,
35 ln.Gross_Flag,
36 ln.Percentage_Diff,
37 ln.Amount_Diff,
38 ln.Exception_Message,
39 ln.Comments
40 From igi_iac_upload_headers hd,
41 igi_iac_upload_lines ln,
42 fa_additions fa
43 Where fa.asset_number = cp_asset_number
44 and hd.file_name = cp_file_name
45 and ln.file_name = hd.file_name
46 and ln.asset_id = fa.asset_id;
47
48 Cursor C_Excpt_Asset(cp_file_name IN igi_iac_upload_headers.file_name%type,
49 cp_asset_number IN fa_additions.asset_number%type,
50 cp_line_num IN igi_iac_upload_lines.line_num%type) Is
51 Select hd.Book_Type_Code,
52 hd.Period_Counter,
53 hd.Currency_Code,
54 hd.Status_Flag Hdr_Status_Flag,
55 hd.Tolerance_Flag,
56 hd.Tolerance_Amount,
57 hd.Tolerance_Percent,
58 hd.Revaluation_Id,
59 ln.Asset_Id,
60 ln.Line_Num,
61 ln.Category_Id,
62 ln.Original_Cost,
63 ln.New_Cost,
64 ln.status_flag Line_Status_Flag,
65 ln.Gross_Flag,
66 ln.Percentage_Diff,
67 ln.Amount_Diff,
68 ln.Exception_Message,
69 ln.Comments
70 From igi_iac_upload_headers hd,
71 igi_iac_upload_lines ln,
72 fa_additions fa
73 Where fa.asset_number = cp_asset_number
74 and hd.file_name = cp_file_name
75 and ln.file_name = hd.file_name
76 and ln.asset_id = fa.asset_id
77 and ln.line_num = cp_line_num;
78
79 -- Bug 3413035 Start
80 Cursor C_Full_Ret_Counter(
81 cp_book_type_code in igi_iac_upload_headers.book_type_code%type,
82 cp_asset_id in fa_books.asset_id%type) Is
83 Select bk.period_counter_fully_retired
84 From fa_books bk
85 Where bk.book_type_code = cp_book_type_code
86 and bk.asset_id = cp_asset_id
87 and bk.transaction_header_id_out is null;
88 -- Bug 3413035 End
89
90 l_global_date date := sysdate;
91 l_global_user_id number := fnd_global.user_id;
92 l_global_login_id number := fnd_global.login_id;
93
94 E_Iac_Not_Enabled exception;
95 E_File_Trans exception;
96 E_Invalid_Asset exception;
97 /*
98 Procedure Debug(p_debug in varchar2, p_print in boolean := TRUE) Is
99 Begin
100 If p_print Then
101 fnd_file.put_line(fnd_file.log, p_debug);
102 End If;
103 End;
104 */
105 Function Last_Period_Counter(
106 p_asset_id IN fa_books.asset_id%type,
107 p_book_type_code IN igi_iac_upload_headers.book_type_code%type,
108 p_dpis_period_counter IN number,
109 p_last_period_counter OUT NOCOPY number) Return Boolean Is
110 l_calendar_type varchar2(40);
111 l_number_per_fiscal_year number;
112 l_life_in_months number;
113 Begin
114 Select ct.calendar_type, ct.number_per_fiscal_year, bk.life_in_months
115 Into l_calendar_type, l_number_per_fiscal_year, l_life_in_months
116 From fa_calendar_types ct, fa_book_controls bc, fa_books bk
117 Where ct.calendar_type = bc.deprn_calendar
118 and bk.book_type_code = p_book_type_code
119 and bk.date_ineffective is null
120 and bk.asset_id = p_asset_id
121 and bc.date_ineffective is null
122 and bc.book_type_code = p_book_type_code;
123
124 p_last_period_counter := p_dpis_period_counter + ((l_life_in_months/12)
125 * l_number_per_fiscal_year) - 1 ;
126 Return True ;
127 Exception
128 When Others Then
129 Return False ;
130 End;
131
132 Procedure Assign_Values_To_Rec(
133 p_upload_record IN out nocopy upload_record,
134 p_file_name IN igi_iac_upload_headers.file_name%type,
135 p_asset_number IN fa_additions.asset_number%type) Is
136 Begin
137 For C_Asset_Exists_Rec in C_Asset_Exists(p_file_Name, p_asset_number) Loop
138 p_upload_record.File_Name := p_file_name;
139 p_upload_record.Book_Type_Code := C_Asset_Exists_Rec.Book_Type_Code;
140 p_upload_record.Period_Counter := C_Asset_Exists_Rec.Period_Counter;
141 p_upload_record.Currency_Code := C_Asset_Exists_Rec.Currency_Code;
142 p_upload_record.Hdr_Status_Flag := C_Asset_Exists_Rec.Hdr_Status_Flag;
143 p_upload_record.Tolerance_Flag := C_Asset_Exists_Rec.Tolerance_Flag;
144 p_upload_record.Tolerance_Amount := C_Asset_Exists_Rec.Tolerance_Amount;
145 p_upload_record.Tolerance_Percent := C_Asset_Exists_Rec.Tolerance_Percent;
146 p_upload_record.Revaluation_Id := C_Asset_Exists_Rec.Revaluation_Id;
147 p_upload_record.Asset_Id := C_Asset_Exists_Rec.Asset_Id;
148 p_upload_record.Line_Num := C_Asset_Exists_Rec.Line_Num;
149 p_upload_record.Category_Id := C_Asset_Exists_Rec.Category_Id;
150 p_upload_record.Original_Cost := C_Asset_Exists_Rec.Original_Cost;
151 p_upload_record.New_Cost := C_Asset_Exists_Rec.New_Cost;
152 p_upload_record.Line_Status_Flag := C_Asset_Exists_Rec.Line_Status_Flag;
153 p_upload_record.Gross_Flag := C_Asset_Exists_Rec.Gross_Flag;
154 p_upload_record.Percentage_Diff := C_Asset_Exists_Rec.Percentage_Diff;
155 p_upload_record.Amount_Diff := C_Asset_Exists_Rec.Amount_Diff;
156 p_upload_record.Exception_Message := C_Asset_Exists_Rec.Exception_Message;
157 p_upload_record.Comments := C_Asset_Exists_Rec.Comments;
158 End Loop;
159 End;
160
161 Procedure Delete_Line(p_file_name IN igi_iac_upload_headers.file_name%type,
162 p_asset_id IN igi_iac_upload_lines.asset_id%type,
163 p_line_num IN igi_iac_upload_lines.line_num%type) Is
164 Begin
165 Delete from igi_iac_upload_lines
166 Where file_name = p_file_name
167 and asset_id = p_asset_id
168 and line_num = p_line_num;
169 End;
170
171 Procedure Insert_Header(p_upload_record IN upload_record) Is
172 Begin
173 Insert into igi_iac_upload_headers(
174 File_Name,
175 Book_Type_Code,
176 Period_Counter,
177 Currency_Code,
178 Status_Flag,
179 Tolerance_Flag,
180 Tolerance_Amount,
181 Tolerance_Percent,
182 Revaluation_Id,
183 Created_By,
184 Creation_Date,
185 Last_Update_Login,
186 Last_Update_Date,
187 Last_Updated_By)
188 Values(
189 p_upload_record.File_Name,
190 p_upload_record.Book_Type_Code,
191 p_upload_record.Period_Counter,
192 p_upload_record.Currency_Code,
193 p_upload_record.Hdr_Status_Flag,
194 p_upload_record.Tolerance_Flag,
195 p_upload_record.Tolerance_Amount,
196 p_upload_record.Tolerance_Percent,
197 p_upload_record.Revaluation_Id,
198 l_global_user_id,
199 l_global_date,
200 l_global_login_id,
201 l_global_date,
202 l_global_user_id);
203 End Insert_Header;
204
205 Procedure Insert_Line(p_upload_record IN upload_record) Is
206 Begin
207 Insert into igi_iac_upload_lines(
208 File_Name,
209 Asset_Id,
210 Line_Num,
211 Category_Id,
212 Original_Cost,
213 New_Cost,
214 Status_Flag,
215 Gross_Flag,
216 Percentage_Diff,
217 Amount_Diff,
218 Exception_Message,
219 Comments,
220 Created_By,
221 Creation_Date,
222 Last_Update_Login,
223 Last_Update_Date,
224 Last_Updated_By)
225 Values (
226 p_upload_record.file_name,
227 p_upload_record.asset_id,
228 p_upload_record.line_num,
229 p_upload_record.category_id,
230 p_upload_record.original_cost,
231 p_upload_record.new_cost,
232 p_upload_record.line_status_flag,
233 p_upload_record.gross_flag,
234 p_upload_record.percentage_diff,
235 p_upload_record.amount_diff,
236 p_upload_record.exception_message,
237 p_upload_record.comments,
238 l_global_user_id,
239 l_global_date,
240 l_global_login_id,
241 l_global_date,
242 l_global_user_id);
243 End Insert_Line;
244
245 Procedure Update_Header(p_upload_record IN upload_record) Is
246 Begin
247 Update igi_iac_upload_headers set
248 Tolerance_Flag = p_upload_record.tolerance_flag,
249 Tolerance_Amount = p_upload_record.tolerance_amount,
250 Tolerance_Percent = p_upload_record.tolerance_percent,
251 Revaluation_Id = p_upload_record.revaluation_id,
252 Last_Update_Login = l_global_login_id,
253 Last_Update_Date = l_global_date,
254 Last_Updated_By = l_global_user_id
255 Where file_name = p_upload_record.file_name;
256 End Update_Header;
257
258 Procedure Gross_Up(p_upload_record IN OUT nocopy upload_record) Is
259 Cursor C_Dpis(
260 cp_book_type_code IN igi_iac_upload_headers.book_type_code%type,
261 cp_asset_id IN igi_iac_upload_lines.asset_id%type) Is
262 Select date_placed_in_service, life_in_months
263 From fa_books
264 Where book_type_code = cp_book_type_code
265 and asset_id = cp_asset_id
266 and date_ineffective is null;
267
268 l_date_placed_in_service fa_books.date_placed_in_service%type;
269 l_life_in_months fa_books.life_in_months%type;
270 l_dpis_period_counter fa_deprn_summary.period_counter%type;
271 l_end_date fa_books.date_placed_in_service%type;
272 l_end_period_counter fa_deprn_summary.period_counter%type;
273 l_total_periods number;
274 l_remaining_periods number;
275 Begin
276 Open C_Dpis(p_upload_record.book_type_code, p_upload_record.asset_id);
277 Fetch C_Dpis into l_date_placed_in_service, l_life_in_months;
278 Close C_Dpis;
279 If igi_iac_common_utils.get_dpis_period_counter(
280 p_upload_record.book_type_code,
281 p_upload_record.asset_id,
282 l_dpis_period_counter) Then
283 l_end_date := add_months(l_date_placed_in_service, l_life_in_months);
284 If Last_Period_Counter(p_upload_record.asset_id,
285 p_upload_record.book_type_code,
286 l_dpis_period_counter,
287 l_end_period_counter) Then
288 l_total_periods := (l_end_period_counter - l_dpis_period_counter) + 1;
289 l_remaining_periods := l_end_period_counter - p_upload_record.period_counter;
290 If l_remaining_periods = 0 then
291 l_remaining_periods := 1;
292 End If;
293 p_upload_record.new_cost := ((p_upload_record.new_cost/l_remaining_periods)*l_total_periods) ;
294 If igi_iac_common_utils.Iac_Round(p_upload_record.new_cost,
295 p_upload_record.book_type_code) Then
296 Null;
297 End If;
298 End If;
299 End If;
300 End Gross_Up;
301
302 Procedure Update_Duplicate_Assets(
303 p_file_name IN igi_iac_upload_lines.file_name%type,
304 p_book_type_code IN igi_iac_upload_headers.book_type_code%type,
305 p_period_counter IN igi_iac_upload_headers.period_counter%type,
306 p_asset_id IN igi_iac_upload_lines.asset_id%type,
307 p_line_num IN igi_iac_upload_lines.line_num%type,
308 p_message IN igi_iac_upload_lines.exception_message%type) Is
309 Cursor C_Dup_Asset_Info(
310 cp_file_name IN igi_iac_upload_lines.file_name%type,
311 cp_asset_id IN igi_iac_upload_lines.asset_id%type,
312 cp_line_num IN igi_iac_upload_lines.line_num%type) IS
313 Select Line_Num, New_Cost, Gross_Flag, Status_Flag
314 From igi_iac_upload_lines
315 Where file_name = cp_file_name
316 and asset_id = cp_asset_id
317 and line_num <> cp_line_num;
318
319 Cursor C_Dpis(
320 cp_book_type_code IN igi_iac_upload_headers.book_type_code%type,
321 cp_asset_id IN igi_iac_upload_lines.asset_id%type) Is
322 Select date_placed_in_service, life_in_months
323 From fa_books
324 Where book_type_code = cp_book_type_code
325 and asset_id = cp_asset_id
326 and date_ineffective is null;
327
328 l_date_placed_in_service fa_books.date_placed_in_service%type;
329 l_life_in_months fa_books.life_in_months%type;
330 l_dpis_period_counter fa_deprn_summary.period_counter%type;
331 l_end_date fa_books.date_placed_in_service%type;
332 l_end_period_counter fa_deprn_summary.period_counter%type;
333 l_total_periods number;
334 l_remaining_periods number;
335 Begin
336 Open C_Dpis(p_book_type_code, p_asset_id);
337 Fetch C_Dpis into l_date_placed_in_service, l_life_in_months;
338 Close C_Dpis;
339 If igi_iac_common_utils.get_dpis_period_counter(p_book_type_code,
340 p_asset_id,
341 l_dpis_period_counter) Then
342 l_end_date := add_months(l_date_placed_in_service, l_life_in_months);
343 If Last_Period_Counter(p_asset_id,
344 p_book_type_code,
345 l_dpis_period_counter,
346 l_end_period_counter) Then
347 l_total_periods := (l_end_period_counter - l_dpis_period_counter) + 1;
348 l_remaining_periods := l_end_period_counter - p_period_counter;
349 If l_remaining_periods = 0 then
350 l_remaining_periods := 1;
351 End If;
352 For C_Dup_Asset_Info_Rec in C_Dup_Asset_Info(p_file_name,
353 p_asset_id,
354 p_line_num) Loop
355 If C_Dup_Asset_Info_Rec.status_flag <> 'E' and
356 C_Dup_Asset_Info_Rec.gross_flag = 'Y' Then
357 C_Dup_Asset_Info_Rec.new_cost :=
358 ((C_Dup_Asset_Info_Rec.new_cost/l_total_periods) *
359 l_remaining_periods);
360 If igi_iac_common_utils.Iac_Round(
361 C_Dup_Asset_Info_Rec.new_cost,p_book_type_code) Then
362 null;
363 End If;
364 End If;
365 Update igi_iac_upload_lines
366 Set New_Cost = C_Dup_Asset_Info_Rec.new_cost,
367 Status_Flag = 'E',
368 Amount_Diff = null,
369 Percentage_Diff = null,
370 Exception_Message = p_message
371 Where file_name = p_file_name
372 and asset_id = p_asset_id
373 and line_num = C_Dup_Asset_Info_Rec.line_num;
374 End Loop;
375 End If;
376 End If;
377 End Update_Duplicate_Assets;
378
379 Procedure Check_Tolerances(p_upload_record IN OUT nocopy upload_record) Is
380 Begin
381 p_upload_record.percentage_diff := abs((p_upload_record.new_cost/ (p_upload_record.original_cost) -1)*100);
382 p_upload_record.amount_diff := abs(p_upload_record.new_cost - p_upload_record.original_cost);
383 If igi_iac_common_utils.Iac_Round(p_upload_record.percentage_diff,
384 p_upload_record.book_type_code) Then
385 Null;
386 End If;
387 If igi_iac_common_utils.Iac_Round(p_upload_record.amount_diff,
388 p_upload_record.book_type_code) Then
389 Null;
390 End If;
391 p_upload_record.line_status_flag := 'A';
392 If p_upload_record.tolerance_amount is null Then
393 If p_upload_record.percentage_diff >
394 abs(p_upload_record.tolerance_percent) Then
395 p_upload_record.line_status_flag := 'L';
396 Fnd_Message.Set_Name('IGI', 'IGI_IAC_UPL_PER_ERROR');
397 p_upload_record.exception_message := Fnd_Message.Get;
398 End If;
399 Elsif p_upload_record.tolerance_percent is null Then
400 If p_upload_record.amount_diff >
401 abs(p_upload_record.tolerance_amount) Then
402 p_upload_record.line_status_flag := 'L';
403 Fnd_Message.Set_Name('IGI', 'IGI_IAC_UPL_AMT_ERROR');
404 p_upload_record.exception_message := Fnd_Message.Get;
405 End If;
406 Else
407 If ((p_upload_record.percentage_diff >
408 abs(p_upload_record.tolerance_percent)) OR
409 (p_upload_record.amount_diff >
410 abs(p_upload_record.tolerance_amount))) Then
411 p_upload_record.line_status_flag := 'L';
412 If ((p_upload_record.percentage_diff >
413 abs(p_upload_record.tolerance_percent)) AND
414 (p_upload_record.amount_diff >
415 abs(p_upload_record.tolerance_amount))) Then
416 Fnd_Message.Set_Name('IGI', 'IGI_IAC_UPL_AMT_PER_ERROR');
417 p_upload_record.exception_message := Fnd_Message.Get;
418 Elsif p_upload_record.percentage_diff >
419 abs(p_upload_record.tolerance_percent) Then
420 Fnd_Message.Set_Name('IGI', 'IGI_IAC_UPL_PER_ERROR');
421 p_upload_record.exception_message := Fnd_Message.Get;
422 Elsif p_upload_record.amount_diff >
423 abs(p_upload_record.tolerance_amount) Then
424 Fnd_Message.Set_Name('IGI', 'IGI_IAC_UPL_AMT_ERROR');
425 p_upload_record.exception_message := Fnd_Message.Get;
426 End If;
427 End If;
428 End If;
429 End Check_Tolerances;
430
431 Procedure Check_Exceptions(p_upload_record IN OUT nocopy upload_record)Is
432
433 Cursor C_Dup_Assets(cp_file_name IN igi_iac_upload_headers.file_name%type,
434 cp_asset_id IN igi_iac_upload_lines.asset_id%type) Is
435 Select count(*)
436 From igi_iac_upload_lines
437 Where file_name = cp_file_name
438 and asset_id = cp_asset_id;
439
440 Cursor C_Valid_Iac_Cat(
441 cp_book_type_code in igi_iac_upload_headers.book_type_code%type,
442 cp_category_id in igi_iac_upload_lines.category_id%type) Is
443 Select allow_prof_reval_flag
444 From igi_iac_category_books
445 Where book_type_code = cp_book_type_code
446 and category_id = cp_category_id;
447
448 Cursor C_Max_Period_counter(
449 cp_book_type_code in igi_iac_upload_headers.book_type_code%type) Is
450 Select max(period_counter)
451 From fa_deprn_summary
452 Where book_type_code = cp_book_type_code;
453
454 Cursor C_Last_Closed_Period(
455 cp_book_type_code in igi_iac_upload_headers.book_type_code%type) Is
456 Select last_period_counter
457 From fa_book_controls
458 Where book_type_code = cp_book_type_code;
459
460 l_asset_cnt number;
461 l_allow_prof_reval_flag igi_iac_category_books.allow_prof_reval_flag%type;
462 l_max_period_counter number;
463 l_last_closed_period number;
464 l_get_period_rec igi_iac_types.prd_rec;
465 Begin
466 p_upload_record.line_status_flag := 'A';
467 p_upload_record.exception_message := null;
468
469 Open C_Valid_Iac_Cat(p_upload_record.book_type_code,
470 p_upload_record.category_id);
471 Fetch C_Valid_Iac_Cat into l_allow_prof_reval_flag;
472 If C_Valid_Iac_Cat%notfound Then
473 p_upload_record.line_status_flag := 'E';
474 Fnd_Message.Set_Name('IGI', 'IGI_IAC_UPL_NON_IAC_CATEGORY');
475 p_upload_record.exception_message := ltrim(
476 p_upload_record.exception_message || ' ' || Fnd_Message.Get);
477 Else
478 If l_allow_prof_reval_flag = 'Y' Then
479 Null;
480 Else
481 p_upload_record.line_status_flag := 'E';
482 Fnd_Message.Set_Name('IGI', 'IGI_IAC_UPL_NON_PROF_CATEGORY');
483 p_upload_record.exception_message := ltrim(
484 p_upload_record.exception_message || ' ' || Fnd_Message.Get);
485 End If;
486 End If;
487 Close C_Valid_Iac_Cat;
488
489 -- Bug 3391784 Start
490 If p_upload_record.original_cost < 0 Then
491 p_upload_record.line_status_flag := 'E';
492 Fnd_Message.Set_Name('IGI', 'IGI_IAC_NEGATIVE_ASSETS');
493 p_upload_record.exception_message := ltrim(
494 p_upload_record.exception_message || ' ' || Fnd_Message.Get);
495 End If;
496 -- Bug 3391784 End
497
498 If igi_iac_common_utils.get_open_Period_Info(
499 p_upload_record.book_type_code, l_get_period_rec) Then
500 If p_upload_record.period_counter <> (l_get_period_rec.period_counter
501 - 1) Then
502 p_upload_record.line_status_flag := 'E';
503 Fnd_Message.Set_Name('IGI', 'IGI_IAC_UPL_UNAVAILABLE_PERIOD');
504 p_upload_record.exception_message := ltrim(
505 p_upload_record.exception_message || ' ' || Fnd_Message.Get);
506 End If;
507 End If;
508 Open C_max_period_counter(p_upload_record.book_type_code);
509 Fetch C_max_period_counter into l_max_period_counter;
510 Close C_max_period_counter;
511
512 Open C_Last_Closed_Period(p_upload_record.book_type_code);
513 Fetch C_Last_Closed_Period into l_last_closed_period;
514 Close C_Last_Closed_Period;
515
516 If l_max_period_counter > l_last_closed_period Then
517 p_upload_record.line_status_flag := 'E';
518 Fnd_Message.Set_Name('IGI', 'IGI_IAC_UPL_DEP_NOT_CLOSED');
519 p_upload_record.exception_message := ltrim(
520 p_upload_record.exception_message || ' ' || Fnd_Message.Get);
521 End If;
522
523 If igi_iac_common_utils.Any_Txns_In_Open_Period(
524 p_upload_record.book_type_code, p_upload_record.asset_id) Then
525 p_upload_record.line_status_flag := 'E';
526 Fnd_Message.Set_Name('IGI', 'IGI_IAC_UPL_PENDING_TRX');
527 p_upload_record.exception_message := ltrim(
528 p_upload_record.exception_message || ' ' || Fnd_Message.Get);
529 End If;
530 If p_upload_record.period_counter_fully_retired is not null Then
531 p_upload_record.line_status_flag := 'E';
532 Fnd_Message.Set_Name('IGI', 'IGI_IAC_UPL_FULLY_RET');
533 p_upload_record.exception_message := ltrim(
534 p_upload_record.exception_message || ' ' || Fnd_Message.Get);
535 End If;
536 Open C_Dup_Assets(p_upload_record.File_Name, p_upload_record.Asset_Id);
537 Fetch C_Dup_Assets into l_asset_cnt;
538 Close C_Dup_Assets;
539 If l_asset_cnt > 1 Then
540 p_upload_record.line_status_flag := 'E';
541 Fnd_Message.Set_Name('IGI', 'IGI_IAC_UPL_DUP_ASSET');
542 p_upload_record.exception_message := ltrim(
543 p_upload_record.exception_message || ' ' || Fnd_Message.Get);
544 Update_Duplicate_Assets(p_upload_record.file_name,
545 p_upload_record.book_type_code,
546 p_upload_record.period_counter,
547 p_upload_record.asset_id,
548 p_upload_record.line_num,
549 p_upload_record.exception_message);
550 End If;
551 Exception
552 When Others Then
553 If C_Dup_Assets%isopen Then
554 Close C_Dup_Assets;
555 End If;
556 If C_Valid_Iac_Cat%isopen Then
557 Close C_Valid_Iac_Cat;
558 End If;
559 If C_Max_Period_counter%isopen Then
560 Close C_Max_Period_counter;
561 End If;
562 If C_Last_Closed_Period%isopen Then
563 Close C_Last_Closed_Period;
564 End If;
565 Raise;
566 End Check_Exceptions;
567
568 Procedure Update_Line(p_upload_record IN upload_record) Is
569 Begin
570 Update igi_iac_upload_lines
571 Set new_cost = p_upload_record.new_cost,
572 original_cost = p_upload_record.original_cost,
573 percentage_diff = p_upload_record.percentage_diff,
574 amount_diff = p_upload_record.amount_diff,
575 status_flag = p_upload_record.line_status_flag,
576 gross_flag = p_upload_record.gross_flag,
577 exception_message = p_upload_record.exception_message,
578 comments = p_upload_record.comments
579 Where file_name = p_upload_record.file_name
580 and asset_id = p_upload_record.asset_id
581 and line_num = p_upload_record.line_num;
582 End Update_Line;
583
584 Procedure Update_Header_Status(
585 p_file_name IN igi_iac_upload_headers.file_name%type) Is
586 Cursor C_Lines(cp_file_name IN igi_iac_upload_headers.file_name%type) Is
587 Select count(*) line_cnt
588 From igi_iac_upload_lines
589 Where file_name = cp_file_name;
590
591 Cursor C_Errors(cp_file_name IN igi_iac_upload_headers.file_name%type,
592 cp_status_flag IN varchar2) Is
593 Select count(*) exp_cnt
594 From igi_iac_upload_lines
595 Where file_name = cp_file_name
596 and status_flag = cp_status_flag;
597 l_errors number;
598 l_lines number;
599 Begin
600 Open C_Lines(p_file_name);
601 Fetch C_Lines into l_lines;
602 Close C_Lines;
603 If l_lines = 0 Then
604 Delete from igi_iac_upload_headers where file_name = p_file_name;
605 Else
606 Open C_Errors(p_file_name, 'E');
607 Fetch C_Errors into l_errors;
608 Close C_Errors;
609 If l_errors > 0 Then
610 Update igi_iac_upload_headers
611 Set status_flag = 'E'
612 Where file_name = p_file_name;
613 Else
614 l_errors := 0;
615 Open C_Errors(p_file_name, 'L');
616 Fetch C_Errors into l_errors;
617 Close C_Errors;
618 If l_errors > 0 Then
619 Update igi_iac_upload_headers
620 Set status_flag = 'L'
621 Where file_name = p_file_name;
622 Else
623 Update igi_iac_upload_headers
624 Set status_flag = 'A'
625 Where file_name = p_file_name;
626 End If;
627 End If;
628 End If;
629 Exception
630 When Others Then
631 If C_Lines%isopen Then
632 Close C_Lines;
633 End If;
634 If C_Errors%isopen Then
635 Close C_Errors;
636 End If;
637 Raise;
638 End Update_Header_Status;
639
640 Procedure Final_Dup_Asset_Check(
641 p_file_name IN igi_iac_upload_headers.file_name%type,
642 p_asset_id IN igi_iac_upload_lines.asset_id%type,
643 p_line_num IN igi_iac_upload_lines.line_num%type,
644 p_asset_number IN fa_additions.asset_number%type) Is
645 Cursor C_Dup_Count(cp_file_name IN igi_iac_upload_headers.file_name%type,
646 cp_asset_id IN igi_iac_upload_lines.asset_id%type,
647 cp_line_num IN igi_iac_upload_lines.line_num%type) Is
648 Select count(*)
649 From igi_iac_upload_lines
650 Where file_name = cp_file_name
651 and asset_id = cp_asset_id
652 and line_num <> cp_line_num
653 and status_flag = 'E';
654
655 l_cnt number;
656 l_dup_record upload_record;
657 Begin
658 Open C_Dup_Count(p_file_name, p_asset_id, p_line_num);
659 Fetch C_Dup_Count into l_cnt;
660 Close C_Dup_Count;
661 If l_cnt = 1 Then
662 Assign_Values_To_Rec(l_dup_record,p_file_name,p_asset_number);
663 -- Bug 3413035 Start
664 For C_Full_Ret_Counter_Rec in C_Full_Ret_Counter(
665 l_dup_record.book_type_code, l_dup_record.asset_id)Loop
666 l_dup_record.period_counter_fully_retired := C_Full_Ret_Counter_Rec.period_counter_fully_retired;
667 End Loop;
668 -- Bug 3413035 End
669 Check_Exceptions(l_dup_record);
670 If l_dup_record.line_status_flag = 'A' Then
671 If l_dup_record.gross_flag = 'Y' Then
672 Gross_Up(l_dup_record);
673 End If;
674 If l_dup_record.tolerance_flag = 'Y' Then
675 Check_Tolerances(l_dup_record);
676 End If;
677 End If;
678 End If;
679 Update_Line(l_dup_record);
680 Exception
681 When Others Then
682 If C_Dup_Count%isopen Then
683 Close C_Dup_Count;
684 End If;
685 Raise;
686 End Final_Dup_Asset_Check;
687
688 Procedure New_File(
689 P_File_Name IN igi_iac_upload_headers.file_name%type,
690 P_Book_Type_Code IN igi_iac_upload_headers.book_type_code%type,
691 P_Tolerance_Flag IN igi_iac_upload_headers.tolerance_flag%type,
692 P_Tolerance_Amount IN igi_iac_upload_headers.tolerance_amount%type,
693 P_Tolerance_Percent IN igi_iac_upload_headers.tolerance_percent%type,
694 P_Asset_Number IN fa_additions.asset_number%type,
695 P_New_Cost IN igi_iac_upload_lines.new_cost%type,
696 P_Gross_Flag IN igi_iac_upload_lines.gross_flag%type) Is
697
698 Cursor C_Book_Defaults(
699 cp_book_type_code in igi_iac_upload_headers.book_type_code%type) Is
700 Select fa.last_period_counter, gl.currency_code
701 From fa_book_controls fa, gl_sets_of_books gl
702 Where book_type_code = cp_book_type_code
703 and gl.set_of_books_id = fa.set_of_books_id;
704
705 Cursor C_Asset_Details(
706 cp_book_type_code in igi_iac_upload_headers.book_type_code%type,
707 cp_asset_number in fa_additions.asset_number%type) Is
708 Select bk.cost,
709 bk.period_counter_fully_retired,
710 ad.asset_id,
711 ad.asset_category_id
712 From fa_books bk, fa_additions ad
713 Where bk.book_type_code = cp_book_type_code
714 and bk.transaction_header_id_out is null
715 and ad.asset_id = bk.asset_id
716 and ad.asset_number = cp_asset_number;
717
718 Cursor C_Get_Adjustments(
719 cp_book_type_code in igi_iac_upload_headers.book_type_code%type,
720 cp_asset_id in igi_iac_upload_lines.asset_id%type,
721 cp_period_counter in igi_iac_upload_headers.period_counter%type) Is
722 Select nvl(adjusted_cost,0)adjusted_cost
723 From igi_iac_asset_balances
724 Where book_type_code = cp_book_type_code
725 and asset_id = cp_asset_id
726 and period_counter = cp_period_counter;
727
728 Cursor C_File_Status(
729 cp_file_name in igi_iac_upload_headers.status_flag%type) Is
730 Select status_flag, period_counter, currency_code
731 From igi_iac_upload_headers
732 Where file_name = cp_file_name;
733
734 Cursor C_Line_Num(
735 cp_file_name in igi_iac_upload_headers.file_name%type) Is
736 Select nvl(max(line_num),0) + 1 Line_Num
737 From igi_iac_upload_lines
738 Where file_name = cp_file_name;
739
740 l_status_flag igi_iac_upload_headers.status_flag%type;
741 l_period_counter igi_iac_upload_headers.period_counter%type;
742 l_currency_code igi_iac_upload_headers.currency_code%type;
743 l_upload_record Upload_Record;
744 E_Non_Iac_Book exception;
745 E_Igi_Tol_Yes exception;
746 E_Igi_Tol_No exception;
747 Begin
748 l_upload_record.File_Name := p_file_name;
749 l_upload_record.Book_Type_Code := p_book_type_code;
750 l_upload_record.Hdr_Status_Flag := 'A';
751 l_upload_record.Tolerance_Flag := p_tolerance_flag;
752 l_upload_record.Tolerance_Amount := p_tolerance_amount;
753 l_upload_record.Tolerance_Percent := p_tolerance_percent;
754 l_upload_record.New_Cost := p_new_cost;
755 l_upload_record.Line_Status_Flag := 'A';
756 l_upload_record.Gross_Flag := p_gross_flag;
757 If not igi_gen.is_req_installed('IAC') Then
758 Raise E_Iac_Not_Enabled;
759 End If;
760
761 If not igi_iac_common_utils.Is_IAC_Book(
762 l_upload_record.book_type_code) Then
763 Raise E_Non_Iac_Book;
764 End If;
765
766 For C_Book_Defaults_Rec in C_Book_Defaults(
767 l_upload_record.book_type_code) Loop
768 l_upload_record.period_counter := C_Book_Defaults_Rec.last_period_counter;
769 l_upload_record.currency_code := C_Book_Defaults_Rec.currency_code;
770 End Loop;
771
772 If l_upload_record.tolerance_flag = 'Y' Then
773 If (l_upload_record.tolerance_amount is null
774 and l_upload_record.tolerance_percent is null ) Then
775 Raise E_Igi_Tol_Yes;
776 End If;
777 Else
778 If (l_upload_record.tolerance_amount is not null
779 or l_upload_record.tolerance_percent is not null ) Then
780 Raise E_Igi_Tol_No;
781 End If;
782 End If;
783
784 For C_Asset_Details_Rec in C_Asset_Details(l_upload_record.book_type_code,
785 p_asset_number) Loop
786 l_upload_record.original_cost := C_Asset_Details_Rec.cost;
787 l_upload_record.asset_id := C_Asset_Details_Rec.asset_id;
788 l_upload_record.category_id := C_Asset_Details_Rec.asset_category_id;
789 l_upload_record.period_counter_fully_retired := C_Asset_Details_Rec.period_counter_fully_retired;
790 End Loop;
791
792 If l_upload_record.asset_id is null Then
793 Raise E_Invalid_Asset;
794 End If;
795
796 For C_Get_Adjustments_Rec in C_Get_Adjustments(
797 l_upload_record.book_type_code,
798 l_upload_record.asset_id,
799 l_upload_record.period_counter) Loop
800 l_upload_record.original_cost := l_upload_record.original_cost + C_Get_Adjustments_Rec.adjusted_cost;
801 End Loop;
802
803 Open C_File_Status (p_file_name);
804 Fetch C_File_Status into l_status_flag, l_period_counter, l_currency_code;
805 If C_File_Status%notfound Then
806 Insert_Header(l_upload_record);
807 l_upload_record.line_num := 1;
808 Insert_Line(l_upload_record);
809 Else
810 If l_status_flag = 'T' Then
811 Close C_File_Status;
812 Raise E_File_Trans;
813 Else
814 l_upload_record.period_counter := l_period_Counter;
815 l_upload_record.currency_code := l_currency_code;
816 Update_Header(l_upload_record);
817 For C_Line_Num_Rec in C_Line_Num(p_file_name) Loop
818 l_upload_record.Line_Num := C_Line_Num_rec.line_num;
819 End Loop;
820 Insert_Line(l_upload_record);
821 End If;
822 End If;
823 Close C_File_Status;
824 Check_Exceptions (l_upload_record);
825 If l_upload_record.line_status_flag = 'A' Then
826 If l_upload_record.gross_flag = 'Y' Then
827 Gross_Up(l_upload_record);
828 End If;
829 If l_upload_record.tolerance_flag = 'Y' Then
830 Check_Tolerances(l_upload_record);
831 End If;
832 End If;
833 Update_Line(l_upload_record);
834 Update_Header_Status(l_upload_record.file_name);
835 Commit;
836 Exception
837 When E_Iac_Not_Enabled Then
838 Fnd_Message.Set_Name('IGI', 'IGI_IAC_NOT_INSTALLED') ;
839 Fnd_Message.Raise_Error;
840 When E_Non_Iac_Book Then
841 Fnd_Message.Set_Name('IGI','IGI_IAC_UPL_INVALID_BOOK');
842 Fnd_Message.Raise_Error;
843 When E_Igi_Tol_Yes Then
844 Fnd_Message.Set_Name('IGI', 'IGI_IAC_UPL_TOL_REQ');
845 Fnd_Message.Raise_Error;
846 When E_Igi_Tol_No Then
847 Fnd_Message.Set_Name('IGI', 'IGI_IAC_UPL_TOL_NOT_REQ');
848 Fnd_Message.Raise_Error;
849 When E_Invalid_Asset Then
850 Fnd_Message.Set_Name('IGI', 'IGI_IAC_UPL_INVALID_ASSET');
851 Fnd_Message.Raise_Error;
852 When E_File_Trans Then
853 Fnd_Message.Set_Name('IGI','IGI_IAC_UPL_FILE_TRANS');
854 Fnd_Message.Raise_Error;
855 End New_File;
856
857 Procedure Tol_Errors(
858 P_File_Name IN igi_iac_upload_headers.file_name%type,
859 P_Book_Type_Code IN igi_iac_upload_headers.book_type_code%type,
860 P_Period IN fa_deprn_periods.period_name%type,
861 P_Currency IN igi_iac_upload_headers.currency_code%type,
862 P_Status IN igi_iac_upload_headers.status_flag%type,
863 P_Hdr_Action IN fnd_lookup_values.lookup_code%type,
864 P_Asset_Number IN fa_additions.asset_number%type,
865 P_Asset_Desc IN fa_additions.description%type,
866 P_Cat_Desc IN fa_categories.description%type,
867 P_Original_Cost IN igi_iac_upload_lines.original_cost%type,
868 P_New_Cost IN igi_iac_upload_lines.new_cost%type,
869 P_Amt_Diff IN igi_iac_upload_lines.amount_diff%type,
870 P_Per_Diff IN igi_iac_upload_lines.percentage_diff%type,
871 P_Message IN igi_iac_upload_lines.exception_message%type,
872 P_Line_Action IN fnd_lookup_values.lookup_code%type,
873 P_Comments IN igi_iac_upload_lines.comments%type,
874 -- Bug 3391921 Start
875 P_Gross_Flag IN igi_iac_upload_lines.gross_flag%type) Is
876 -- Bug 3391921 End
877 l_dummy varchar2(1);
878 l_upload_record upload_record;
879 l_upd_line_flag varchar2(1) := 'Y';
880 Begin
881 If not igi_gen.is_req_installed('IAC') Then
882 Raise E_Iac_Not_Enabled;
883 End If;
884
885 Open C_File_Transferred(p_file_name);
886 Fetch C_File_Transferred into l_dummy;
887 If C_File_Transferred%found Then
888 Close C_File_Transferred;
889 Raise E_File_Trans;
890 End If;
891 Close C_File_Transferred;
892
893 Assign_Values_To_Rec(l_upload_record,p_file_name, p_asset_number);
894 l_upload_record.Comments := p_Comments;
895 -- Bug 3391921 Start
896 l_upload_record.Gross_Flag := p_gross_flag;
897 -- Bug 3391921 End
898
899 If l_upload_record.asset_id is null Then
900 Raise E_Invalid_Asset;
901 End If;
902
903 If p_line_action = 'D' Then
904 Delete_Line(p_file_name,
905 l_upload_record.asset_id,
906 l_upload_record.line_num);
907 l_upd_line_flag := 'N';
908 Elsif p_line_action = 'A' Then
909 If p_hdr_action = 'A' Then
910 Fnd_Message.Set_Name('IGI', 'IGI_IAC_UPL_TOL_ACCEPT');
911 l_upload_record.comments := Fnd_Message.Get;
912 End If;
913 l_upload_record.line_status_flag := 'A';
914 l_upload_record.exception_message := null;
915 Else -- N
916 If p_hdr_action = 'D' Then
917 Delete_Line(p_file_name,
918 l_upload_record.asset_Id,
919 l_upload_record.line_num);
920 l_upd_line_flag := 'N';
921 Elsif p_hdr_action = 'A' Then
922 Fnd_Message.Set_Name('IGI', 'IGI_IAC_UPL_TOL_ACCEPT');
923 l_upload_record.comments := Fnd_Message.Get;
924 l_upload_record.line_status_flag := 'A';
925 l_upload_record.exception_message := null;
926 Else -- N
927 l_upd_line_flag := 'N';
928 End If;
929 End If;
930
931 If l_upload_record.line_status_flag = 'A' Then
932 -- Bug 3391921 Start
933 If ((p_new_cost <> l_upload_record.new_cost) or p_gross_flag = 'Y') Then
934 -- Bug 3391921 End
935 l_upload_record.new_cost := p_new_cost;
936 If l_upload_record.gross_flag = 'Y' Then
937 Gross_Up(l_upload_record);
938 End If;
939 If l_upload_record.tolerance_flag = 'Y' Then
940 Check_Tolerances(l_upload_record);
941 End If;
942 End If;
943 End If;
944 If l_upd_line_flag = 'Y' Then
945 Update_Line(l_upload_record);
946 End If;
947 Update_Header_Status(l_upload_record.file_name);
948 Commit;
949 Exception
950 When E_Iac_Not_Enabled Then
951 FND_MESSAGE.Set_Name('IGI','IGI_IAC_NOT_INSTALLED');
952 Fnd_Message.Raise_Error;
953 When E_File_Trans Then
954 FND_MESSAGE.Set_Name('IGI','IGI_IAC_UPL_FILE_TRANS');
955 Fnd_Message.Raise_Error;
956 When E_Invalid_Asset Then
957 Fnd_Message.Set_Name('IGI','IGI_IAC_UPL_NO_ASSET_IN_FILE');
958 Fnd_Message.Raise_Error;
959 End Tol_Errors;
960
961 Procedure Excpt_Errors(
962 P_File_Name IN igi_iac_upload_headers.file_name%type,
963 P_Book_Type_Code IN igi_iac_upload_headers.book_type_code%type,
964 P_Period IN fa_deprn_periods.period_name%type,
965 P_Currency IN igi_iac_upload_headers.currency_code%type,
966 P_Status IN igi_iac_upload_headers.status_flag%type,
967 P_Hdr_Action IN fnd_lookup_values.lookup_code%type,
968 P_Asset_Number IN fa_additions.asset_number%type,
969 P_Line_Num IN igi_iac_upload_lines.line_num%type,
970 P_Asset_Desc IN fa_additions.description%type,
971 P_Cat_Desc IN fa_categories.description%type,
972 P_New_Cost IN igi_iac_upload_lines.new_cost%type,
973 P_Message IN igi_iac_upload_lines.exception_message%type,
974 P_Line_Action IN fnd_lookup_values.lookup_code%type,
975 -- Bug 3391921 Start
976 P_Gross_Flag IN igi_iac_upload_lines.gross_flag%type) Is
977 -- Bug 3391921 End
978
979 l_dummy igi_iac_upload_headers.status_flag%type;
980 l_upload_record upload_record;
981 l_upd_line_flag varchar2(1) := 'Y';
982 Begin
983 If not igi_gen.is_req_installed('IAC') Then
984 Raise E_Iac_Not_Enabled;
985 End If;
986
987 Open C_File_Transferred(p_file_name);
988 Fetch C_File_Transferred into l_dummy;
989 If C_File_Transferred%found Then
990 Close C_File_Transferred;
991 Raise E_File_Trans;
992 End If;
993 Close C_File_Transferred;
994
995 For C_Excpt_Asset_Rec in C_Excpt_Asset(p_file_name,
996 p_asset_number,
997 p_line_num) Loop
998 l_upload_record.File_Name := p_file_name;
999 l_upload_record.Book_Type_Code := C_Excpt_Asset_Rec.Book_Type_Code;
1000 l_upload_record.Period_Counter := C_Excpt_Asset_Rec.Period_Counter;
1001 l_upload_record.Currency_Code := C_Excpt_Asset_Rec.Currency_Code;
1002 l_upload_record.Hdr_Status_Flag := C_Excpt_Asset_Rec.Hdr_Status_Flag;
1003 l_upload_record.Tolerance_Flag := C_Excpt_Asset_Rec.Tolerance_Flag;
1004 l_upload_record.Tolerance_Amount := C_Excpt_Asset_Rec.Tolerance_Amount;
1005 l_upload_record.Tolerance_Percent := C_Excpt_Asset_Rec.Tolerance_Percent;
1006 l_upload_record.Revaluation_Id := C_Excpt_Asset_Rec.Revaluation_Id;
1007 l_upload_record.Asset_Id := C_Excpt_Asset_Rec.Asset_Id;
1008 l_upload_record.Line_Num := C_Excpt_Asset_Rec.Line_Num;
1009 l_upload_record.Category_Id := C_Excpt_Asset_Rec.Category_Id;
1010 l_upload_record.Original_Cost := C_Excpt_Asset_Rec.Original_Cost;
1011 -- Bug 3391921 Start
1012 l_upload_record.New_Cost := p_new_cost;
1013 -- Bug 3391921 End
1014 l_upload_record.Line_Status_Flag := C_Excpt_Asset_Rec.Line_Status_Flag;
1015 -- Bug 3391921 Start
1016 l_upload_record.Gross_Flag := p_gross_flag;
1017 -- Bug 3391921 End
1018 l_upload_record.Percentage_Diff := C_Excpt_Asset_Rec.Percentage_Diff;
1019 l_upload_record.Amount_Diff := C_Excpt_Asset_Rec.Amount_Diff;
1020 l_upload_record.Exception_Message := C_Excpt_Asset_Rec.Exception_Message;
1021 l_upload_record.Comments := C_Excpt_Asset_Rec.Comments;
1022 End Loop;
1023
1024 If l_upload_record.Asset_Id is null Then
1025 Raise E_Invalid_Asset;
1026 End If;
1027
1028 For C_Full_Ret_Counter_Rec in C_Full_Ret_Counter(
1029 l_upload_record.book_type_code, l_upload_record.asset_id)Loop
1030 l_upload_record.period_counter_fully_retired := C_Full_Ret_Counter_Rec.period_counter_fully_retired;
1031 End Loop;
1032
1033 If p_line_action = 'D' Then
1034 Delete_Line(p_file_name,
1035 l_upload_record.asset_id,
1036 l_upload_record.line_num);
1037 l_upd_line_flag := 'N';
1038 Elsif p_line_action = 'A' Then
1039 If p_hdr_action = 'A' Then
1040 Fnd_Message.Set_Name('IGI', 'IGI_IAC_UPL_EXC_ACCEPT');
1041 l_upload_record.comments := Fnd_Message.Get;
1042 End If;
1043 Check_Exceptions(l_upload_record);
1044 Else -- N
1045 If p_hdr_action = 'D' Then
1046 Delete_Line(p_file_name,
1047 l_upload_record.asset_id,
1048 l_upload_record.line_num);
1049 l_upd_line_flag := 'N';
1050 Elsif p_hdr_action = 'A' Then
1051 Fnd_Message.Set_Name('IGI', 'IGI_IAC_UPL_EXC_ACCEPT');
1052 l_upload_record.comments := Fnd_Message.Get;
1053 Check_Exceptions(l_upload_record);
1054 Else -- N
1055 l_upd_line_flag := 'N';
1056 End If;
1057 End if;
1058
1059 If l_upload_record.line_status_flag = 'A' Then
1060 If l_upload_record.gross_flag = 'Y' Then
1061 Gross_Up(l_upload_record);
1062 End If;
1063 If l_upload_record.tolerance_flag = 'Y' Then
1064 Check_Tolerances(l_upload_record);
1065 End If;
1066 End If;
1067 If l_upd_line_flag = 'Y' Then
1068 Update_Line(l_upload_record);
1069 End If;
1070 Final_Dup_Asset_Check(
1071 p_file_name,
1072 l_upload_record.asset_id,
1073 l_upload_record.line_num,
1074 p_asset_number);
1075 Update_Header_Status(l_upload_record.file_name);
1076 Commit;
1077 Exception
1078 When E_Iac_Not_Enabled Then
1079 FND_MESSAGE.Set_Name('IGI','IGI_IAC_NOT_INSTALLED');
1080 Fnd_Message.Raise_Error;
1081 When E_File_Trans Then
1082 FND_MESSAGE.Set_Name('IGI','IGI_IAC_UPL_FILE_TRANS');
1083 Fnd_Message.Raise_Error;
1084 When E_Invalid_Asset Then
1085 Fnd_Message.Set_Name('IGI','IGI_IAC_UPL_NO_ASSET_IN_FILE');
1086 Fnd_Message.Raise_Error;
1087 End Excpt_Errors;
1088
1089 Procedure Valid_Assets(
1090 P_File_Name IN igi_iac_upload_headers.file_name%type,
1091 P_Book_Type_Code IN igi_iac_upload_headers.book_type_code%type,
1092 P_Period IN fa_deprn_periods.period_name%type,
1093 P_Currency IN igi_iac_upload_headers.currency_code%type,
1094 P_Status IN igi_iac_upload_headers.status_flag%type,
1095 P_Hdr_Action IN fnd_lookup_values.lookup_code%type,
1096 P_Asset_Number IN fa_additions.asset_number%type,
1097 P_Asset_Desc IN fa_additions.description%type,
1098 P_Cat_Desc IN fa_categories.description%type,
1099 P_Original_Cost IN igi_iac_upload_lines.original_cost%type,
1100 P_New_Cost IN igi_iac_upload_lines.new_cost%type,
1101 P_Line_Action IN fnd_lookup_values.lookup_code%type,
1102 -- Bug 3391921 Start
1103 P_Gross_Flag IN igi_iac_upload_lines.gross_flag%type) Is
1104 -- Bug 3391921 End
1105
1106 l_dummy varchar2(1);
1107 l_upload_record upload_record;
1108 Begin
1109 If not igi_gen.is_req_installed('IAC') Then
1110 Raise E_Iac_Not_Enabled;
1111 End If;
1112
1113 Open C_File_Transferred(p_file_name);
1114 Fetch C_File_Transferred into l_dummy;
1115 If C_File_Transferred%found Then
1116 Close C_File_Transferred;
1117 Raise E_File_Trans;
1118 End If;
1119 Close C_File_Transferred;
1120
1121 If p_hdr_action = 'Y' Then
1122 Delete from igi_iac_upload_headers where file_name = p_file_name;
1123 Delete from igi_iac_upload_lines where file_name = p_file_name;
1124 Else -- N
1125 Assign_Values_To_Rec(l_upload_record, p_file_name, p_asset_number);
1126 -- Bug 3391921 Start
1127 l_upload_record.gross_flag := p_gross_flag;
1128 -- Bug 3391921 End
1129 If l_upload_record.asset_id is null Then
1130 Raise E_Invalid_Asset;
1131 End If;
1132 If p_line_action = 'D' Then
1133 Delete_Line(p_file_name,
1134 l_upload_record.asset_id,
1135 l_upload_record.line_num);
1136 Else -- N
1137 -- Bug 3391921 Start
1138 If ((p_new_cost <> l_upload_record.new_cost) or p_gross_flag = 'Y') Then
1139 -- Bug 3391921 End
1140 l_upload_record.new_cost := p_new_cost;
1141 If l_upload_record.gross_flag = 'Y' Then
1142 Gross_Up(l_upload_record);
1143 End If;
1144 If l_upload_record.tolerance_flag = 'Y' Then
1145 Check_Tolerances(l_upload_record);
1146 End If;
1147 Update_Line(l_upload_record);
1148 End If;
1149 End If;
1150 Update_Header_Status(l_upload_record.file_name);
1151 End If;
1152 Commit;
1153 Exception
1154 When E_Iac_Not_Enabled Then
1155 FND_MESSAGE.Set_Name('IGI','IGI_IAC_NOT_INSTALLED');
1156 Fnd_Message.Raise_Error;
1157 When E_File_Trans Then
1158 FND_MESSAGE.Set_Name('IGI','IGI_IAC_UPL_FILE_TRANS');
1159 Fnd_Message.Raise_Error;
1160 When E_Invalid_Asset Then
1161 Fnd_Message.Set_Name('IGI','IGI_IAC_UPL_NO_ASSET_IN_FILE');
1162 Fnd_Message.Raise_Error;
1163 End Valid_Assets;
1164
1165 Procedure Transfer_Data(
1166 errbuf OUT NOCOPY varchar2,
1167 retcode OUT NOCOPY number,
1168 p_file_name IN igi_iac_upload_headers.file_name%type,
1169 p_preview_flag IN varchar2) Is
1170
1171 Cursor C_Upload_Hdr(
1172 cp_file_name in igi_iac_upload_headers.file_name%type) Is
1173 Select * from igi_iac_upload_headers
1174 Where file_name = cp_file_name;
1175
1176 Cursor C_Upload_Lines(
1177 cp_file_name in igi_iac_upload_headers.file_name%type) Is
1178 Select * from igi_iac_upload_lines
1179 Where file_name = cp_file_name;
1180
1181 Cursor C_Categories(
1182 cp_file_name in igi_iac_upload_headers.file_name%type) Is
1183 Select distinct category_id from igi_iac_upload_lines
1184 where file_name = cp_file_name;
1185
1186 Cursor C_Max_Period_counter(
1187 cp_book_type_code in igi_iac_upload_headers.book_type_code%type) Is
1188 Select max(period_counter) max_period_counter
1189 From fa_deprn_summary
1190 Where book_type_code = cp_book_type_code;
1191
1192 Cursor C_Last_Closed_Period(
1193 cp_book_type_code in igi_iac_upload_headers.book_type_code%type) Is
1194 Select last_period_counter
1195 From fa_book_controls
1196 Where book_type_code = cp_book_type_code;
1197
1198 -- bug 3443410, start 1
1199 CURSOR c_preview_exists(cp_book_type_code igi_iac_upload_headers.book_type_code%TYPE)
1200 IS
1201 SELECT irar.asset_id,
1202 fa.asset_number,
1203 irar.selected_for_reval_flag,
1204 iir.status
1205 FROM igi_iac_reval_asset_rules irar,
1206 igi_iac_revaluations iir,
1207 fa_additions fa
1208 WHERE irar.book_type_code = cp_book_type_code
1209 AND fa.asset_id = irar.asset_id
1210 AND irar.revaluation_id = iir.revaluation_id
1211 AND irar.revaluation_id = (SELECT max(revaluation_id)
1212 FROM igi_iac_revaluations
1213 WHERE book_type_code = cp_book_type_code
1214 AND calling_program IN ('IGIIAIAR', 'SSUPLOAD') -- bug 3510376, add
1215 AND status in ('PREVIEWED', 'NEW', 'FAILED_PRE',
1216 'FAILED_RUN', 'PREVIEW', 'UPDATED'));
1217
1218 l_preview_exists c_preview_exists%ROWTYPE;
1219 -- l_book_type_code igi_iac_revaluations.book_type_code%TYPE;
1220 -- bug 3443410, end 1
1221
1222 -- bug 3536362, start 1
1223 -- cursor to retrieve the historic portion of the cost of an asset
1224 CURSOR C_FA_Asset_Cost(cp_book_type_code IN igi_iac_upload_headers.book_type_code%TYPE,
1225 cp_asset_id IN fa_additions.asset_id%TYPE
1226 )
1227 IS
1228 SELECT bk.cost
1229 FROM fa_books bk,
1230 fa_additions ad
1231 WHERE bk.book_type_code = cp_book_type_code
1232 AND bk.transaction_header_id_out IS NULL
1233 AND ad.asset_id = bk.asset_id
1234 AND ad.asset_id = cp_asset_id;
1235
1236 -- cursor to retrieve the iac portion of the cost of an asset
1237 CURSOR C_IAC_Asset_Cost(cp_book_type_code IN igi_iac_upload_headers.book_type_code%TYPE,
1238 cp_asset_id IN igi_iac_upload_lines.asset_id%TYPE,
1239 cp_period_counter IN igi_iac_upload_headers.period_counter%TYPE
1240 )
1241 IS
1242 SELECT nvl(adjusted_cost,0) adjusted_cost
1243 FROM igi_iac_asset_balances
1244 WHERE book_type_code = cp_book_type_code
1245 AND asset_id = cp_asset_id
1246 AND period_counter = cp_period_counter;
1247
1248 l_fa_asset_cost fa_books.cost%TYPE;
1249 l_iac_asset_cost igi_iac_asset_balances.adjusted_cost%TYPE;
1250 l_current_cost igi_iac_reval_asset_rules.current_cost%TYPE;
1251 -- bug 3536362, end 1
1252
1253 l_reval_id Number;
1254 l_reval_date Date;
1255 l_asset_count Number := 0;
1256 l_max_period_counter number;
1257 l_last_closed_period number;
1258 l_request_id Number;
1259 l_message varchar2(1000);
1260 l_get_period_rec igi_iac_types.prd_rec;
1261 l_create_req_id igi_iac_revaluations.create_request_id%type := null;
1262
1263 -- bug 3412940, start 1
1264 l_reval_factor igi_iac_reval_asset_rules.revaluation_factor%TYPE;
1265 -- bug 3412940, end 1
1266
1267 E_Request_Submit_Error exception;
1268 E_Unavailable_Period exception;
1269 E_Period_Not_Closed exception;
1270
1271 -- bug 3443410, start 2
1272 e_preview_exists EXCEPTION;
1273 -- bug 3443410, end 2
1274
1275 Begin
1276
1277 /* bug 3443410, start 3
1278 comment this out - the nextval should be retrieved only if
1279 the upload process is allowed to create a NEW or PREVIEWED
1280 revaluation. Move it to after the Preview/New exists exception
1281 check
1282 Select igi_iac_revaluations_s.NEXTVAL
1283 Into l_reval_id
1284 From dual;
1285
1286 bug 3443419, end 3 */
1287
1288 For C_Upload_Hdr_Rec in C_Upload_Hdr(p_file_name) Loop
1289
1290 -- mh start 4
1291 -- check if a previewed revaluation exists for the book
1292 -- if it does then error the transfer process out
1293 OPEN c_preview_exists(C_Upload_Hdr_Rec.book_type_code);
1294 FETCH c_preview_exists INTO l_preview_exists;
1295 IF c_preview_exists%FOUND THEN
1296 -- l_book_type_code := C_Upload_Hdr_Rec.book_type_code;
1297 CLOSE c_preview_exists;
1298 RAISE e_preview_exists;
1299 END IF;
1300 CLOSE c_preview_exists;
1301
1302 Select igi_iac_revaluations_s.NEXTVAL
1303 Into l_reval_id
1304 From dual;
1305 -- mh end 4
1306
1307 If igi_iac_common_utils.get_open_Period_Info(
1308 C_Upload_Hdr_Rec.book_type_code, l_get_period_rec) Then
1309 If C_Upload_Hdr_Rec.period_counter <>
1310 (l_get_period_rec.period_counter - 1) Then
1311 Raise E_Unavailable_Period;
1312 End If;
1313 End If;
1314 For C_Max_Period_Counter_Rec in C_max_period_counter(
1315 C_Upload_Hdr_Rec.book_type_code) Loop
1316 l_max_period_counter := C_max_period_counter_rec.max_period_counter;
1317 End Loop;
1318
1319 For C_Last_Closed_Period_Rec in C_Last_Closed_Period(
1320 C_Upload_Hdr_Rec.book_type_code) Loop
1321 l_last_closed_period := C_Last_Closed_Period_Rec.last_period_counter;
1322 End Loop;
1323
1324 If l_max_period_counter > l_last_closed_period Then
1325 Raise E_Period_Not_Closed;
1326 End If;
1327
1328 If igi_iac_common_utils.get_period_info_for_counter(
1329 C_Upload_Hdr_rec.book_type_code,
1330 C_Upload_Hdr_Rec.period_counter,
1331 l_get_period_rec) Then
1332 l_reval_date := l_get_period_rec.period_end_date;
1333 End If;
1334 Insert into igi_iac_revaluations(
1335 Revaluation_Id,
1336 Book_Type_Code,
1337 Revaluation_Date ,
1338 Revaluation_Period ,
1339 Status,
1340 Reval_Request_Id ,
1341 Create_Request_Id ,
1342 Calling_Program ,
1343 Last_Update_Date,
1344 Created_By ,
1345 Last_Update_Login ,
1346 Last_Updated_By ,
1347 Creation_Date)
1348 Values(
1349 l_reval_id,
1350 C_Upload_Hdr_Rec.book_type_code,
1351 l_reval_date,
1352 C_Upload_Hdr_Rec.period_counter,
1353 'NEW',
1354 null,
1355 null,
1356 'SSUPLOAD',
1357 l_global_date,
1358 l_global_user_id,
1359 l_global_login_id,
1360 l_global_user_id,
1361 l_global_date);
1362
1363 For C_Categories_Rec in C_Categories(p_file_name) Loop
1364 Insert into igi_iac_reval_categories(
1365 Revaluation_Id,
1366 Book_Type_Code,
1367 Category_Id,
1368 Select_Category,
1369 Last_Update_Date,
1370 Created_By ,
1371 Last_Update_Login ,
1372 Last_Updated_By ,
1373 Creation_Date)
1374 Values(
1375 l_reval_id,
1376 C_Upload_Hdr_Rec.book_type_code,
1377 C_Categories_Rec.category_id,
1378 'Y',
1379 l_global_date,
1380 l_global_user_id,
1381 l_global_login_id,
1382 l_global_user_id,
1383 l_global_date);
1384 End Loop;
1385
1386 For C_Upload_Lines_Rec in C_Upload_Lines(p_file_name) Loop
1387
1388 -- bug 3536362, start 2
1389 OPEN C_FA_Asset_Cost(cp_book_type_code => C_Upload_Hdr_Rec.book_type_code,
1390 cp_asset_id => C_Upload_Lines_Rec.asset_id
1391 );
1392 FETCH C_FA_Asset_Cost INTO l_fa_asset_cost;
1393 IF C_FA_Asset_Cost%NOTFOUND THEN
1394 RAISE NO_DATA_FOUND;
1395 END IF;
1396 CLOSE C_FA_Asset_Cost;
1397
1398 OPEN C_IAC_Asset_Cost(cp_book_type_code => C_Upload_Hdr_Rec.book_type_code,
1399 cp_asset_id => C_Upload_Lines_Rec.asset_id,
1400 cp_period_counter => C_Upload_Hdr_Rec.period_counter
1401 );
1402 FETCH C_IAC_Asset_Cost INTO l_iac_asset_cost;
1403 IF C_IAC_Asset_Cost%NOTFOUND THEN
1404 RAISE NO_DATA_FOUND;
1405 END IF;
1406 CLOSE C_IAC_Asset_Cost;
1407
1408 l_current_cost := l_fa_asset_cost + l_iac_asset_cost;
1409 -- bug 3536362, end 2
1410
1411 -- bug 3412940, start 2
1412 -- l_reval_factor := C_Upload_lines_Rec.new_cost/C_Upload_Lines_Rec.original_cost;
1413 -- bug 3412940, end 2
1414
1415 -- bug 3536362, start 3
1416 l_reval_factor := C_Upload_lines_Rec.new_cost/l_current_cost;
1417 -- bug 3536362, end 3
1418
1419 Insert into igi_iac_reval_asset_rules(
1420 Revaluation_Id,
1421 Book_Type_Code,
1422 Category_Id,
1423 Asset_Id,
1424 Revaluation_Factor,
1425 Revaluation_Type,
1426 New_Cost,
1427 Current_Cost,
1428 Selected_For_Reval_Flag,
1429 Selected_For_Calc_Flag,
1430 Allow_Prof_Update,
1431 Created_By,
1432 Creation_Date,
1433 Last_Update_Login,
1434 Last_Update_Date,
1435 Last_Updated_By)
1436 Values(
1437 l_reval_id,
1438 C_Upload_Hdr_Rec.book_type_code,
1439 C_Upload_Lines_Rec.category_id,
1440 C_Upload_Lines_Rec.asset_id,
1441 l_reval_factor, -- bug 3412940 1,
1442 'P',
1443 C_Upload_Lines_Rec.new_cost,
1444 l_current_cost, -- bug 3536362 C_Upload_Lines_Rec.original_cost,
1445 'Y',
1446 null,
1447 null,
1448 l_global_user_id,
1449 l_global_date,
1450 l_global_login_id,
1451 l_global_date,
1452 l_global_user_id);
1453 If sql%found Then
1454 l_asset_count := l_asset_count + 1;
1455 End If;
1456 End Loop ;
1457
1458 Update igi_iac_upload_headers
1459 Set status_flag = 'T', revaluation_id = l_reval_id
1460 Where file_name = p_file_name;
1461
1462 If sql%notfound then
1463 Fnd_Message.Set_Name('IGI', 'IGI_IAC_UPL_TRANS_FAILURE');
1464 l_message := Fnd_Message.Get;
1465 -- Debug(l_message);
1466 -- bug 3299718, start block
1467 IF (l_state_level >= l_debug_level) THEN
1468 FND_LOG.STRING(l_state_level, 'igi.plsql.IGI_IAC_WEBADI_PKG.Transfer_Data.Msg1',
1469 l_message);
1470 END IF;
1471 -- bug 3299718, end block
1472
1473
1474 Elsif sql%found then
1475 Fnd_Message.Set_Name('IGI', 'IGI_IAC_UPL_TRANS_SUCCESS');
1476 l_message := Fnd_Message.Get;
1477 -- Debug(l_message);
1478 -- bug 3299718, start block
1479 IF (l_state_level >= l_debug_level) THEN
1480 FND_LOG.STRING(l_state_level, 'igi.plsql.IGI_IAC_WEBADI_PKG.Transfer_Data.Msg2',
1481 l_message);
1482 END IF;
1483 -- bug 3299718, end block
1484
1485 Fnd_Message.Set_Name('IGI', 'IGI_IAC_UPL_TRANS_COUNT');
1486 Fnd_Message.Set_Token('ASSET_COUNT',l_asset_count);
1487 l_message := Fnd_Message.Get;
1488 -- Debug(l_message);
1489 -- bug 3299718, start block
1490 IF (l_state_level >= l_debug_level) THEN
1491 FND_LOG.STRING(l_state_level, 'igi.plsql.IGI_IAC_WEBADI_PKG.Transfer_Data.Msg3',
1492 l_message);
1493 END IF;
1494 -- bug 3299718, end block
1495 Fnd_Message.Set_Name('IGI', 'IGI_IAC_UPL_REVAL_ID');
1496 Fnd_Message.Set_Token('REVAL_ID',l_reval_id);
1497 l_message := Fnd_Message.Get;
1498 -- Debug(l_message);
1499 -- bug 3299718, start block
1500 IF (l_state_level >= l_debug_level) THEN
1501 FND_LOG.STRING(l_state_level, 'igi.plsql.IGI_IAC_WEBADI_PKG.Transfer_Data.Msg4',
1502 l_message);
1503 END IF;
1504 -- bug 3299718, end block
1505 End if;
1506 Commit;
1507
1508 If p_preview_flag = 'Y' Then
1509 l_request_id := FND_REQUEST.SUBMIT_REQUEST(
1510 APPLICATION => 'IGI',
1511 PROGRAM => 'IGIIAIAR',
1512 DESCRIPTION => '',
1513 START_TIME => NULL,
1514 SUB_REQUEST => FALSE,
1515 ARGUMENT1 => to_char(l_reval_id),
1516 ARGUMENT2 => C_Upload_Hdr_Rec.book_type_code,
1517 ARGUMENT3 => 'P',
1518 ARGUMENT4 => to_char(C_Upload_Hdr_Rec.period_counter),
1519 ARGUMENT5 => to_char(l_create_req_id),
1520 ARGUMENT6 => CHR(0), ARGUMENT7 => NULL, ARGUMENT8 => NULL,
1521 ARGUMENT9 => NULL, ARGUMENT10 => NULL, ARGUMENT11 => NULL,
1522 ARGUMENT12 => NULL, ARGUMENT13 => NULL, ARGUMENT14 => NULL,
1523 ARGUMENT15 => NULL, ARGUMENT16 => NULL, ARGUMENT17 => NULL,
1524 ARGUMENT18 => NULL, ARGUMENT19 => NULL, ARGUMENT20 => NULL,
1525 ARGUMENT21 => NULL, ARGUMENT22 => NULL, ARGUMENT23 => NULL,
1526 ARGUMENT24 => NULL, ARGUMENT25 => NULL, ARGUMENT26 => NULL,
1527 ARGUMENT27 => NULL, ARGUMENT28 => NULL, ARGUMENT29 => NULL,
1528 ARGUMENT30 => NULL, ARGUMENT31 => NULL, ARGUMENT32 => NULL,
1529 ARGUMENT33 => NULL, ARGUMENT34 => NULL, ARGUMENT35 => NULL,
1530 ARGUMENT36 => NULL, ARGUMENT37 => NULL, ARGUMENT38 => NULL,
1531 ARGUMENT39 => NULL, ARGUMENT40 => NULL, ARGUMENT41 => NULL,
1532 ARGUMENT42 => NULL, ARGUMENT43 => NULL, ARGUMENT44 => NULL,
1533 ARGUMENT45 => NULL, ARGUMENT46 => NULL, ARGUMENT47 => NULL,
1534 ARGUMENT48 => NULL, ARGUMENT49 => NULL, ARGUMENT50 => NULL,
1535 ARGUMENT51 => NULL, ARGUMENT52 => NULL, ARGUMENT53 => NULL,
1536 ARGUMENT54 => NULL, ARGUMENT55 => NULL, ARGUMENT56 => NULL,
1537 ARGUMENT57 => NULL, ARGUMENT58 => NULL, ARGUMENT59 => NULL,
1538 ARGUMENT60 => NULL, ARGUMENT61 => NULL, ARGUMENT62 => NULL,
1539 ARGUMENT63 => NULL, ARGUMENT64 => NULL, ARGUMENT65 => NULL,
1540 ARGUMENT66 => NULL, ARGUMENT67 => NULL, ARGUMENT68 => NULL,
1541 ARGUMENT69 => NULL, ARGUMENT70 => NULL, ARGUMENT71 => NULL,
1542 ARGUMENT72 => NULL, ARGUMENT73 => NULL, ARGUMENT74 => NULL,
1543 ARGUMENT75 => NULL, ARGUMENT76 => NULL, ARGUMENT77 => NULL,
1544 ARGUMENT78 => NULL, ARGUMENT79 => NULL, ARGUMENT80 => NULL,
1545 ARGUMENT81 => NULL, ARGUMENT82 => NULL, ARGUMENT83 => NULL,
1546 ARGUMENT84 => NULL, ARGUMENT85 => NULL, ARGUMENT86 => NULL,
1547 ARGUMENT87 => NULL, ARGUMENT88 => NULL, ARGUMENT89 => NULL,
1548 ARGUMENT90 => NULL, ARGUMENT91 => NULL, ARGUMENT92 => NULL,
1549 ARGUMENT93 => NULL, ARGUMENT94 => NULL, ARGUMENT95 => NULL,
1550 ARGUMENT96 => NULL, ARGUMENT97 => NULL, ARGUMENT98 => NULL,
1551 ARGUMENT99 => NULL, ARGUMENT100 => NULL);
1552
1553 IF l_request_id = 0 THEN
1554 RAISE E_Request_Submit_Error;
1555 End If;
1556 End If;
1557 End Loop;
1558 retcode := 0;
1559 Exception
1560 When E_Unavailable_Period Then
1561 Fnd_Message.Set_Name('IGI', 'IGI_IAC_UPL_UNAVAILABLE_PERIOD');
1562 l_message := Fnd_Message.Get;
1563 -- Debug(l_message);
1564 -- bug 3299718, start block
1565 IF (l_state_level >= l_debug_level) THEN
1566 FND_LOG.STRING(l_state_level, 'igi.plsql.IGI_IAC_WEBADI_PKG.Transfer_Data.Msg5',
1567 l_message);
1568 END IF;
1569 -- bug 3299718, end block
1570
1571 errbuf := l_message;
1572 retcode :=2;
1573 When E_Period_Not_Closed Then
1574 Fnd_Message.Set_Name('IGI', 'IGI_IAC_UPL_DEP_NOT_CLOSED');
1575 l_message := Fnd_Message.Get;
1576 -- Debug(l_message);
1577 -- bug 3299718, start block
1578 IF (l_state_level >= l_debug_level) THEN
1579 FND_LOG.STRING(l_state_level, 'igi.plsql.IGI_IAC_WEBADI_PKG.Transfer_Data.Msg6',
1580 l_message);
1581 END IF;
1582 -- bug 3299718, end block
1583
1584 errbuf := l_message;
1585 retcode :=2;
1586 When E_Request_Submit_Error THEN
1587 Fnd_Message.Set_Name('IGI', 'IGI_IAC_UPL_PREVIEW_FAILED');
1588 l_message := Fnd_Message.Get;
1589 -- Debug(l_message);
1590
1591 -- bug 3299718, start block
1592 IF (l_state_level >= l_debug_level) THEN
1593 FND_LOG.STRING(l_state_level, 'igi.plsql.IGI_IAC_WEBADI_PKG.Transfer_Data.Msg7',
1594 l_message);
1595 END IF;
1596 -- bug 3299718, end block
1597
1598 -- bug 3443410, start 5
1599 WHEN e_preview_exists THEN
1600 /* bug 3495368, start 1
1601 -- commenting out as no longer required
1602 IF (l_error_level >= l_debug_level) THEN
1603 fnd_message.set_name('IGI', 'IGI_IAC_UPL_PREVIEW_EXISTS');
1604 l_mesg1 := fnd_message.get;
1605 FND_LOG.MESSAGE(l_error_level,'igi.plsql.IGI_IAC_WEBADI_PKG.Transfer_Data.Preview Exists', TRUE);
1606 -- fnd_file.put_line required for display to user
1607 fnd_file.put_line(fnd_file.log, l_mesg1);
1608
1609 -- list of assets
1610 l_preview_exists := NULL;
1611 FOR l_preview_exists IN c_preview_exists(l_book_type_code) LOOP
1612 fnd_message.set_name('IGI', 'IGI_IAC_UPL_PREVIEW_ASSETS');
1613 fnd_message.set_token('ASSET_NUM', l_preview_exists.asset_number, FALSE);
1614 l_mesg1 := fnd_message.get;
1615 FND_LOG.MESSAGE(l_error_level,'igi.plsql.IGI_IAC_WEBADI_PKG.Transfer_Data.Preview Exists', TRUE);
1616 -- fnd_file.put_line required for display to user
1617 fnd_file.put_line(fnd_file.log, l_mesg1);
1618 END LOOP;
1619 END IF;
1620 bug 3495368, end 1 */
1621
1622 -- bug 3495368, start 2
1623 fnd_message.set_name('IGI', 'IGI_IAC_UPL_PREVIEW_EXISTS');
1624 l_message := fnd_message.get;
1625 -- bug 3495368, end 2
1626
1627 errbuf := SQLERRM||': '||l_message;
1628 retcode :=2;
1629 -- bug 3443410, end 5
1630
1631 When Others then
1632 errbuf := SQLERRM;
1633 retcode :=2;
1634 End Transfer_Data;
1635 End IGI_IAC_WEBADI_PKG;