[Home] [Help]
PACKAGE BODY: APPS.IGI_IAC_WEBADI_PKG
Source
1 PACKAGE BODY IGI_IAC_WEBADI_PKG AS
2 -- $Header: igiiasab.pls 120.12 2011/10/25 11:53:40 aarsridh ship $
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 = (select max(period_counter)
727 from igi_iac_asset_balances
728 where book_type_code = cp_book_type_code
729 and asset_id = cp_asset_id); -- Bug 8484461
730
731 Cursor C_File_Status(
732 cp_file_name in igi_iac_upload_headers.status_flag%type) Is
733 Select status_flag, period_counter, currency_code
734 From igi_iac_upload_headers
735 Where file_name = cp_file_name;
736
737 Cursor C_Line_Num(
738 cp_file_name in igi_iac_upload_headers.file_name%type) Is
739 Select nvl(max(line_num),0) + 1 Line_Num
740 From igi_iac_upload_lines
741 Where file_name = cp_file_name;
742
743 l_status_flag igi_iac_upload_headers.status_flag%type;
744 l_period_counter igi_iac_upload_headers.period_counter%type;
745 l_currency_code igi_iac_upload_headers.currency_code%type;
746 l_upload_record Upload_Record;
747 E_Non_Iac_Book exception;
748 E_Igi_Tol_Yes exception;
749 E_Igi_Tol_No exception;
750
751 E_Zero_New_Cost exception; /*Added for bug 13250900 */
752 Begin
753 l_upload_record.File_Name := p_file_name;
754 l_upload_record.Book_Type_Code := p_book_type_code;
755 l_upload_record.Hdr_Status_Flag := 'A';
756 l_upload_record.Tolerance_Flag := p_tolerance_flag;
757 l_upload_record.Tolerance_Amount := p_tolerance_amount;
758 l_upload_record.Tolerance_Percent := p_tolerance_percent;
759 l_upload_record.New_Cost := p_new_cost;
760 l_upload_record.Line_Status_Flag := 'A';
761 l_upload_record.Gross_Flag := p_gross_flag;
762 If not igi_gen.is_req_installed('IAC') Then
763 Raise E_Iac_Not_Enabled;
764 End If;
765
766 If not igi_iac_common_utils.Is_IAC_Book(
767 l_upload_record.book_type_code) Then
768 Raise E_Non_Iac_Book;
769 End If;
770
771 /*Added for bug 13250900 */
772 IF (p_new_cost = 0) THEN
773 Raise E_Zero_New_Cost;
774 END IF;
775
776 For C_Book_Defaults_Rec in C_Book_Defaults(
777 l_upload_record.book_type_code) Loop
778 l_upload_record.period_counter := C_Book_Defaults_Rec.last_period_counter;
779 l_upload_record.currency_code := C_Book_Defaults_Rec.currency_code;
780 End Loop;
781
782 If l_upload_record.tolerance_flag = 'Y' Then
783 If (l_upload_record.tolerance_amount is null
784 and l_upload_record.tolerance_percent is null ) Then
785 Raise E_Igi_Tol_Yes;
786 End If;
787 Else
788 If (l_upload_record.tolerance_amount is not null
789 or l_upload_record.tolerance_percent is not null ) Then
790 Raise E_Igi_Tol_No;
791 End If;
792 End If;
793
794 For C_Asset_Details_Rec in C_Asset_Details(l_upload_record.book_type_code,
795 p_asset_number) Loop
796 l_upload_record.original_cost := C_Asset_Details_Rec.cost;
797 l_upload_record.asset_id := C_Asset_Details_Rec.asset_id;
798 l_upload_record.category_id := C_Asset_Details_Rec.asset_category_id;
799 l_upload_record.period_counter_fully_retired := C_Asset_Details_Rec.period_counter_fully_retired;
800 End Loop;
801
802 If l_upload_record.asset_id is null Then
803 Raise E_Invalid_Asset;
804 End If;
805
806 For C_Get_Adjustments_Rec in C_Get_Adjustments(
807 l_upload_record.book_type_code,
808 l_upload_record.asset_id,
809 l_upload_record.period_counter) Loop
810 l_upload_record.original_cost := l_upload_record.original_cost + C_Get_Adjustments_Rec.adjusted_cost;
811 End Loop;
812
813 Open C_File_Status (p_file_name);
814 Fetch C_File_Status into l_status_flag, l_period_counter, l_currency_code;
815 If C_File_Status%notfound Then
816 Insert_Header(l_upload_record);
817 l_upload_record.line_num := 1;
818 Insert_Line(l_upload_record);
819 Else
820 If l_status_flag = 'T' Then
821 Close C_File_Status;
822 Raise E_File_Trans;
823 Else
824 l_upload_record.period_counter := l_period_Counter;
825 l_upload_record.currency_code := l_currency_code;
826 Update_Header(l_upload_record);
827 For C_Line_Num_Rec in C_Line_Num(p_file_name) Loop
828 l_upload_record.Line_Num := C_Line_Num_rec.line_num;
829 End Loop;
830 Insert_Line(l_upload_record);
831 End If;
832 End If;
833 Close C_File_Status;
834 Check_Exceptions (l_upload_record);
835 If l_upload_record.line_status_flag = 'A' Then
836 If l_upload_record.gross_flag = 'Y' Then
837 Gross_Up(l_upload_record);
838 End If;
839 If l_upload_record.tolerance_flag = 'Y' Then
840 Check_Tolerances(l_upload_record);
841 End If;
842 End If;
843 Update_Line(l_upload_record);
844 Update_Header_Status(l_upload_record.file_name);
845 Commit;
846 Exception
847 When E_Iac_Not_Enabled Then
848 Fnd_Message.Set_Name('IGI', 'IGI_IAC_NOT_INSTALLED') ;
849 Fnd_Message.Raise_Error;
850 When E_Non_Iac_Book Then
851 Fnd_Message.Set_Name('IGI','IGI_IAC_UPL_INVALID_BOOK');
852 Fnd_Message.Raise_Error;
853 /*Added for bug 13250900 */
854 When E_Zero_New_Cost Then
855 Fnd_Message.Set_Name('IGI', 'IGI_IAC_NEW_COST_ZERO') ;
856 Fnd_Message.Raise_Error;
857 When E_Igi_Tol_Yes Then
858 Fnd_Message.Set_Name('IGI', 'IGI_IAC_UPL_TOL_REQ');
859 Fnd_Message.Raise_Error;
860 When E_Igi_Tol_No Then
861 Fnd_Message.Set_Name('IGI', 'IGI_IAC_UPL_TOL_NOT_REQ');
862 Fnd_Message.Raise_Error;
863 When E_Invalid_Asset Then
864 Fnd_Message.Set_Name('IGI', 'IGI_IAC_UPL_INVALID_ASSET');
865 Fnd_Message.Raise_Error;
866 When E_File_Trans Then
867 Fnd_Message.Set_Name('IGI','IGI_IAC_UPL_FILE_TRANS');
868 Fnd_Message.Raise_Error;
869 End New_File;
870
871 Procedure Tol_Errors(
872 P_File_Name IN igi_iac_upload_headers.file_name%type,
873 P_Book_Type_Code IN igi_iac_upload_headers.book_type_code%type,
874 P_Period IN fa_deprn_periods.period_name%type,
875 P_Currency IN igi_iac_upload_headers.currency_code%type,
876 P_Status IN igi_iac_upload_headers.status_flag%type,
877 P_Hdr_Action IN fnd_lookup_values.lookup_code%type,
878 P_Asset_Number IN fa_additions.asset_number%type,
879 P_Asset_Desc IN fa_additions.description%type,
880 P_Cat_Desc IN fa_categories.description%type,
881 P_Original_Cost IN igi_iac_upload_lines.original_cost%type,
882 P_New_Cost IN igi_iac_upload_lines.new_cost%type,
883 P_Amt_Diff IN igi_iac_upload_lines.amount_diff%type,
884 P_Per_Diff IN igi_iac_upload_lines.percentage_diff%type,
885 P_Message IN igi_iac_upload_lines.exception_message%type,
886 P_Line_Action IN fnd_lookup_values.lookup_code%type,
887 P_Comments IN igi_iac_upload_lines.comments%type,
888 -- Bug 3391921 Start
889 P_Gross_Flag IN igi_iac_upload_lines.gross_flag%type) Is
890 -- Bug 3391921 End
891 l_dummy varchar2(1);
892 l_upload_record upload_record;
893 l_upd_line_flag varchar2(1) := 'Y';
894 Begin
895 If not igi_gen.is_req_installed('IAC') Then
896 Raise E_Iac_Not_Enabled;
897 End If;
898
899 Open C_File_Transferred(p_file_name);
900 Fetch C_File_Transferred into l_dummy;
901 If C_File_Transferred%found Then
902 Close C_File_Transferred;
903 Raise E_File_Trans;
904 End If;
905 Close C_File_Transferred;
906
907 Assign_Values_To_Rec(l_upload_record,p_file_name, p_asset_number);
908 l_upload_record.Comments := p_Comments;
909 -- Bug 3391921 Start
910 l_upload_record.Gross_Flag := p_gross_flag;
911 -- Bug 3391921 End
912
913 If l_upload_record.asset_id is null Then
914 Raise E_Invalid_Asset;
915 End If;
916
917 If p_line_action = 'D' Then
918 Delete_Line(p_file_name,
919 l_upload_record.asset_id,
920 l_upload_record.line_num);
921 l_upd_line_flag := 'N';
922 Elsif p_line_action = 'A' Then
923 If p_hdr_action = 'A' Then
924 Fnd_Message.Set_Name('IGI', 'IGI_IAC_UPL_TOL_ACCEPT');
925 l_upload_record.comments := Fnd_Message.Get;
926 End If;
927 l_upload_record.line_status_flag := 'A';
928 l_upload_record.exception_message := null;
929 Else -- N
930 If p_hdr_action = 'D' Then
931 Delete_Line(p_file_name,
932 l_upload_record.asset_Id,
933 l_upload_record.line_num);
934 l_upd_line_flag := 'N';
935 Elsif p_hdr_action = 'A' Then
936 Fnd_Message.Set_Name('IGI', 'IGI_IAC_UPL_TOL_ACCEPT');
937 l_upload_record.comments := Fnd_Message.Get;
938 l_upload_record.line_status_flag := 'A';
939 l_upload_record.exception_message := null;
940 Else -- N
941 l_upd_line_flag := 'N';
942 End If;
943 End If;
944
945 If l_upload_record.line_status_flag = 'A' Then
946 -- Bug 3391921 Start
947 If ((p_new_cost <> l_upload_record.new_cost) or p_gross_flag = 'Y') Then
948 -- Bug 3391921 End
949 l_upload_record.new_cost := p_new_cost;
950 If l_upload_record.gross_flag = 'Y' Then
951 Gross_Up(l_upload_record);
952 End If;
953 If l_upload_record.tolerance_flag = 'Y' Then
954 Check_Tolerances(l_upload_record);
955 End If;
956 End If;
957 End If;
958 If l_upd_line_flag = 'Y' Then
959 Update_Line(l_upload_record);
960 End If;
961 Update_Header_Status(l_upload_record.file_name);
962 Commit;
963 Exception
964 When E_Iac_Not_Enabled Then
965 FND_MESSAGE.Set_Name('IGI','IGI_IAC_NOT_INSTALLED');
966 Fnd_Message.Raise_Error;
967 When E_File_Trans Then
968 FND_MESSAGE.Set_Name('IGI','IGI_IAC_UPL_FILE_TRANS');
969 Fnd_Message.Raise_Error;
970 When E_Invalid_Asset Then
971 Fnd_Message.Set_Name('IGI','IGI_IAC_UPL_NO_ASSET_IN_FILE');
972 Fnd_Message.Raise_Error;
973 End Tol_Errors;
974
975 Procedure Excpt_Errors(
976 P_File_Name IN igi_iac_upload_headers.file_name%type,
977 P_Book_Type_Code IN igi_iac_upload_headers.book_type_code%type,
978 P_Period IN fa_deprn_periods.period_name%type,
979 P_Currency IN igi_iac_upload_headers.currency_code%type,
980 P_Status IN igi_iac_upload_headers.status_flag%type,
981 P_Hdr_Action IN fnd_lookup_values.lookup_code%type,
982 P_Asset_Number IN fa_additions.asset_number%type,
983 P_Line_Num IN igi_iac_upload_lines.line_num%type,
984 P_Asset_Desc IN fa_additions.description%type,
985 P_Cat_Desc IN fa_categories.description%type,
986 P_New_Cost IN igi_iac_upload_lines.new_cost%type,
987 P_Message IN igi_iac_upload_lines.exception_message%type,
988 P_Line_Action IN fnd_lookup_values.lookup_code%type,
989 -- Bug 3391921 Start
990 P_Gross_Flag IN igi_iac_upload_lines.gross_flag%type) Is
991 -- Bug 3391921 End
992
993 l_dummy igi_iac_upload_headers.status_flag%type;
994 l_upload_record upload_record;
995 l_upd_line_flag varchar2(1) := 'Y';
996 Begin
997 If not igi_gen.is_req_installed('IAC') Then
998 Raise E_Iac_Not_Enabled;
999 End If;
1000
1001 Open C_File_Transferred(p_file_name);
1002 Fetch C_File_Transferred into l_dummy;
1003 If C_File_Transferred%found Then
1004 Close C_File_Transferred;
1005 Raise E_File_Trans;
1006 End If;
1007 Close C_File_Transferred;
1008
1009 For C_Excpt_Asset_Rec in C_Excpt_Asset(p_file_name,
1010 p_asset_number,
1011 p_line_num) Loop
1012 l_upload_record.File_Name := p_file_name;
1013 l_upload_record.Book_Type_Code := C_Excpt_Asset_Rec.Book_Type_Code;
1014 l_upload_record.Period_Counter := C_Excpt_Asset_Rec.Period_Counter;
1015 l_upload_record.Currency_Code := C_Excpt_Asset_Rec.Currency_Code;
1016 l_upload_record.Hdr_Status_Flag := C_Excpt_Asset_Rec.Hdr_Status_Flag;
1017 l_upload_record.Tolerance_Flag := C_Excpt_Asset_Rec.Tolerance_Flag;
1018 l_upload_record.Tolerance_Amount := C_Excpt_Asset_Rec.Tolerance_Amount;
1019 l_upload_record.Tolerance_Percent := C_Excpt_Asset_Rec.Tolerance_Percent;
1020 l_upload_record.Revaluation_Id := C_Excpt_Asset_Rec.Revaluation_Id;
1021 l_upload_record.Asset_Id := C_Excpt_Asset_Rec.Asset_Id;
1022 l_upload_record.Line_Num := C_Excpt_Asset_Rec.Line_Num;
1023 l_upload_record.Category_Id := C_Excpt_Asset_Rec.Category_Id;
1024 l_upload_record.Original_Cost := C_Excpt_Asset_Rec.Original_Cost;
1025 -- Bug 3391921 Start
1026 l_upload_record.New_Cost := p_new_cost;
1027 -- Bug 3391921 End
1028 l_upload_record.Line_Status_Flag := C_Excpt_Asset_Rec.Line_Status_Flag;
1029 -- Bug 3391921 Start
1030 l_upload_record.Gross_Flag := p_gross_flag;
1031 -- Bug 3391921 End
1032 l_upload_record.Percentage_Diff := C_Excpt_Asset_Rec.Percentage_Diff;
1033 l_upload_record.Amount_Diff := C_Excpt_Asset_Rec.Amount_Diff;
1034 l_upload_record.Exception_Message := C_Excpt_Asset_Rec.Exception_Message;
1035 l_upload_record.Comments := C_Excpt_Asset_Rec.Comments;
1036 End Loop;
1037
1038 If l_upload_record.Asset_Id is null Then
1039 Raise E_Invalid_Asset;
1040 End If;
1041
1042 For C_Full_Ret_Counter_Rec in C_Full_Ret_Counter(
1043 l_upload_record.book_type_code, l_upload_record.asset_id)Loop
1044 l_upload_record.period_counter_fully_retired := C_Full_Ret_Counter_Rec.period_counter_fully_retired;
1045 End Loop;
1046
1047 If p_line_action = 'D' Then
1048 Delete_Line(p_file_name,
1049 l_upload_record.asset_id,
1050 l_upload_record.line_num);
1051 l_upd_line_flag := 'N';
1052 Elsif p_line_action = 'A' Then
1053 If p_hdr_action = 'A' Then
1054 Fnd_Message.Set_Name('IGI', 'IGI_IAC_UPL_EXC_ACCEPT');
1055 l_upload_record.comments := Fnd_Message.Get;
1056 End If;
1057 Check_Exceptions(l_upload_record);
1058 Else -- N
1059 If p_hdr_action = 'D' Then
1060 Delete_Line(p_file_name,
1061 l_upload_record.asset_id,
1062 l_upload_record.line_num);
1063 l_upd_line_flag := 'N';
1064 Elsif p_hdr_action = 'A' Then
1065 Fnd_Message.Set_Name('IGI', 'IGI_IAC_UPL_EXC_ACCEPT');
1066 l_upload_record.comments := Fnd_Message.Get;
1067 Check_Exceptions(l_upload_record);
1068 Else -- N
1069 l_upd_line_flag := 'N';
1070 End If;
1071 End if;
1072
1073 If l_upload_record.line_status_flag = 'A' Then
1074 If l_upload_record.gross_flag = 'Y' Then
1075 Gross_Up(l_upload_record);
1076 End If;
1077 If l_upload_record.tolerance_flag = 'Y' Then
1078 Check_Tolerances(l_upload_record);
1079 End If;
1080 End If;
1081 If l_upd_line_flag = 'Y' Then
1082 Update_Line(l_upload_record);
1083 End If;
1084 Final_Dup_Asset_Check(
1085 p_file_name,
1086 l_upload_record.asset_id,
1087 l_upload_record.line_num,
1088 p_asset_number);
1089 Update_Header_Status(l_upload_record.file_name);
1090 Commit;
1091 Exception
1092 When E_Iac_Not_Enabled Then
1093 FND_MESSAGE.Set_Name('IGI','IGI_IAC_NOT_INSTALLED');
1094 Fnd_Message.Raise_Error;
1095 When E_File_Trans Then
1096 FND_MESSAGE.Set_Name('IGI','IGI_IAC_UPL_FILE_TRANS');
1097 Fnd_Message.Raise_Error;
1098 When E_Invalid_Asset Then
1099 Fnd_Message.Set_Name('IGI','IGI_IAC_UPL_NO_ASSET_IN_FILE');
1100 Fnd_Message.Raise_Error;
1101 End Excpt_Errors;
1102
1103 Procedure Valid_Assets(
1104 P_File_Name IN igi_iac_upload_headers.file_name%type,
1105 P_Book_Type_Code IN igi_iac_upload_headers.book_type_code%type,
1106 P_Period IN fa_deprn_periods.period_name%type,
1107 P_Currency IN igi_iac_upload_headers.currency_code%type,
1108 P_Status IN igi_iac_upload_headers.status_flag%type,
1109 P_Hdr_Action IN fnd_lookup_values.lookup_code%type,
1110 P_Asset_Number IN fa_additions.asset_number%type,
1111 P_Asset_Desc IN fa_additions.description%type,
1112 P_Cat_Desc IN fa_categories.description%type,
1113 P_Original_Cost IN igi_iac_upload_lines.original_cost%type,
1114 P_New_Cost IN igi_iac_upload_lines.new_cost%type,
1115 P_Line_Action IN fnd_lookup_values.lookup_code%type,
1116 -- Bug 3391921 Start
1117 P_Gross_Flag IN igi_iac_upload_lines.gross_flag%type) Is
1118 -- Bug 3391921 End
1119
1120 l_dummy varchar2(1);
1121 l_upload_record upload_record;
1122 Begin
1123 If not igi_gen.is_req_installed('IAC') Then
1124 Raise E_Iac_Not_Enabled;
1125 End If;
1126
1127 Open C_File_Transferred(p_file_name);
1128 Fetch C_File_Transferred into l_dummy;
1129 If C_File_Transferred%found Then
1130 Close C_File_Transferred;
1131 Raise E_File_Trans;
1132 End If;
1133 Close C_File_Transferred;
1134
1135 If p_hdr_action = 'Y' Then
1136 Delete from igi_iac_upload_headers where file_name = p_file_name;
1137 Delete from igi_iac_upload_lines where file_name = p_file_name;
1138 Else -- N
1139 Assign_Values_To_Rec(l_upload_record, p_file_name, p_asset_number);
1140 -- Bug 3391921 Start
1141 l_upload_record.gross_flag := p_gross_flag;
1142 -- Bug 3391921 End
1143 If l_upload_record.asset_id is null Then
1144 Raise E_Invalid_Asset;
1145 End If;
1146 If p_line_action = 'D' Then
1147 Delete_Line(p_file_name,
1148 l_upload_record.asset_id,
1149 l_upload_record.line_num);
1150 Else -- N
1151 -- Bug 3391921 Start
1152 If ((p_new_cost <> l_upload_record.new_cost) or p_gross_flag = 'Y') Then
1153 -- Bug 3391921 End
1154 l_upload_record.new_cost := p_new_cost;
1155 If l_upload_record.gross_flag = 'Y' Then
1156 Gross_Up(l_upload_record);
1157 End If;
1158 If l_upload_record.tolerance_flag = 'Y' Then
1159 Check_Tolerances(l_upload_record);
1160 End If;
1161 Update_Line(l_upload_record);
1162 End If;
1163 End If;
1164 Update_Header_Status(l_upload_record.file_name);
1165 End If;
1166 Commit;
1167 Exception
1168 When E_Iac_Not_Enabled Then
1169 FND_MESSAGE.Set_Name('IGI','IGI_IAC_NOT_INSTALLED');
1170 Fnd_Message.Raise_Error;
1171 When E_File_Trans Then
1172 FND_MESSAGE.Set_Name('IGI','IGI_IAC_UPL_FILE_TRANS');
1173 Fnd_Message.Raise_Error;
1174 When E_Invalid_Asset Then
1175 Fnd_Message.Set_Name('IGI','IGI_IAC_UPL_NO_ASSET_IN_FILE');
1176 Fnd_Message.Raise_Error;
1177 End Valid_Assets;
1178
1179 Procedure Transfer_Data(
1180 errbuf OUT NOCOPY varchar2,
1181 retcode OUT NOCOPY number,
1182 p_file_name IN igi_iac_upload_headers.file_name%type,
1183 p_preview_flag IN varchar2) Is
1184
1185 Cursor C_Upload_Hdr(
1186 cp_file_name in igi_iac_upload_headers.file_name%type) Is
1187 Select * from igi_iac_upload_headers
1188 Where file_name = cp_file_name;
1189
1190 Cursor C_Upload_Lines(
1191 cp_file_name in igi_iac_upload_headers.file_name%type) Is
1192 Select * from igi_iac_upload_lines
1193 Where file_name = cp_file_name;
1194
1195 Cursor C_Categories(
1196 cp_file_name in igi_iac_upload_headers.file_name%type) Is
1197 Select distinct category_id from igi_iac_upload_lines
1198 where file_name = cp_file_name;
1199
1200 Cursor C_Max_Period_counter(
1201 cp_book_type_code in igi_iac_upload_headers.book_type_code%type) Is
1202 Select max(period_counter) max_period_counter
1203 From fa_deprn_summary
1204 Where book_type_code = cp_book_type_code;
1205
1206 Cursor C_Last_Closed_Period(
1207 cp_book_type_code in igi_iac_upload_headers.book_type_code%type) Is
1208 Select last_period_counter
1209 From fa_book_controls
1210 Where book_type_code = cp_book_type_code;
1211
1212 -- bug 3443410, start 1
1213 CURSOR c_preview_exists(cp_book_type_code igi_iac_upload_headers.book_type_code%TYPE)
1214 IS
1215 SELECT irar.asset_id,
1216 fa.asset_number,
1217 irar.selected_for_reval_flag,
1218 iir.status
1219 FROM igi_iac_reval_asset_rules irar,
1220 igi_iac_revaluations iir,
1221 fa_additions fa
1222 WHERE irar.book_type_code = cp_book_type_code
1223 AND fa.asset_id = irar.asset_id
1224 AND irar.revaluation_id = iir.revaluation_id
1225 AND irar.revaluation_id = (SELECT max(revaluation_id)
1226 FROM igi_iac_revaluations
1227 WHERE book_type_code = cp_book_type_code
1228 AND calling_program IN ('IGIIAIAR', 'SSUPLOAD') -- bug 3510376, add
1229 AND status in ('PREVIEWED', 'NEW', 'FAILED_PRE',
1230 'FAILED_RUN', 'PREVIEW', 'UPDATED'));
1231
1232 l_preview_exists c_preview_exists%ROWTYPE;
1233 -- l_book_type_code igi_iac_revaluations.book_type_code%TYPE;
1234 -- bug 3443410, end 1
1235
1236 -- bug 3536362, start 1
1237 -- cursor to retrieve the historic portion of the cost of an asset
1238 CURSOR C_FA_Asset_Cost(cp_book_type_code IN igi_iac_upload_headers.book_type_code%TYPE,
1239 cp_asset_id IN fa_additions.asset_id%TYPE
1240 )
1241 IS
1242 SELECT bk.cost
1243 FROM fa_books bk,
1244 fa_additions ad
1245 WHERE bk.book_type_code = cp_book_type_code
1246 AND bk.transaction_header_id_out IS NULL
1247 AND ad.asset_id = bk.asset_id
1248 AND ad.asset_id = cp_asset_id;
1249
1250 -- cursor to retrieve the iac portion of the cost of an asset
1251 CURSOR C_IAC_Asset_Cost(cp_book_type_code IN igi_iac_upload_headers.book_type_code%TYPE,
1252 cp_asset_id IN igi_iac_upload_lines.asset_id%TYPE,
1253 cp_period_counter IN igi_iac_upload_headers.period_counter%TYPE
1254 )
1255 IS
1256 SELECT nvl(adjusted_cost,0) adjusted_cost
1257 FROM igi_iac_asset_balances
1258 WHERE book_type_code = cp_book_type_code
1259 AND asset_id = cp_asset_id
1260 AND period_counter = (SELECT MAX(period_counter)
1261 FROM igi_iac_asset_balances
1262 WHERE book_type_code = cp_book_type_code
1263 AND asset_id = cp_asset_id); -- Bug 8484461
1264
1265 l_fa_asset_cost fa_books.cost%TYPE;
1266 l_iac_asset_cost igi_iac_asset_balances.adjusted_cost%TYPE;
1267 l_current_cost igi_iac_reval_asset_rules.current_cost%TYPE;
1268 -- bug 3536362, end 1
1269
1270 l_reval_id Number;
1271 l_reval_date Date;
1272 l_asset_count Number := 0;
1273 l_max_period_counter number;
1274 l_last_closed_period number;
1275 l_request_id Number;
1276 l_message varchar2(1000);
1277 l_get_period_rec igi_iac_types.prd_rec;
1278 l_create_req_id igi_iac_revaluations.create_request_id%type := null;
1279
1280 -- bug 3412940, start 1
1281 l_reval_factor igi_iac_reval_asset_rules.revaluation_factor%TYPE;
1282 -- bug 3412940, end 1
1283
1284 E_Request_Submit_Error exception;
1285 E_Unavailable_Period exception;
1286 E_Period_Not_Closed exception;
1287
1288 -- bug 3443410, start 2
1289 e_preview_exists EXCEPTION;
1290 -- bug 3443410, end 2
1291
1292 Begin
1293
1294 /* bug 3443410, start 3
1295 comment this out - the nextval should be retrieved only if
1296 the upload process is allowed to create a NEW or PREVIEWED
1297 revaluation. Move it to after the Preview/New exists exception
1298 check
1299 Select igi_iac_revaluations_s.NEXTVAL
1300 Into l_reval_id
1301 From dual;
1302
1303 bug 3443419, end 3 */
1304
1305 For C_Upload_Hdr_Rec in C_Upload_Hdr(p_file_name) Loop
1306
1307 -- mh start 4
1308 -- check if a previewed revaluation exists for the book
1309 -- if it does then error the transfer process out
1310 OPEN c_preview_exists(C_Upload_Hdr_Rec.book_type_code);
1311 FETCH c_preview_exists INTO l_preview_exists;
1312 IF c_preview_exists%FOUND THEN
1313 -- l_book_type_code := C_Upload_Hdr_Rec.book_type_code;
1314 CLOSE c_preview_exists;
1315 RAISE e_preview_exists;
1316 END IF;
1317 CLOSE c_preview_exists;
1318
1319 Select igi_iac_revaluations_s.NEXTVAL
1320 Into l_reval_id
1321 From dual;
1322 -- mh end 4
1323
1324 If igi_iac_common_utils.get_open_Period_Info(
1325 C_Upload_Hdr_Rec.book_type_code, l_get_period_rec) Then
1326 If C_Upload_Hdr_Rec.period_counter <>
1327 (l_get_period_rec.period_counter - 1) Then
1328 Raise E_Unavailable_Period;
1329 End If;
1330 End If;
1331 For C_Max_Period_Counter_Rec in C_max_period_counter(
1332 C_Upload_Hdr_Rec.book_type_code) Loop
1333 l_max_period_counter := C_max_period_counter_rec.max_period_counter;
1334 End Loop;
1335
1336 For C_Last_Closed_Period_Rec in C_Last_Closed_Period(
1337 C_Upload_Hdr_Rec.book_type_code) Loop
1338 l_last_closed_period := C_Last_Closed_Period_Rec.last_period_counter;
1339 End Loop;
1340
1341 If l_max_period_counter > l_last_closed_period Then
1342 Raise E_Period_Not_Closed;
1343 End If;
1344
1345 If igi_iac_common_utils.get_period_info_for_counter(
1346 C_Upload_Hdr_rec.book_type_code,
1347 C_Upload_Hdr_Rec.period_counter,
1348 l_get_period_rec) Then
1349 l_reval_date := l_get_period_rec.period_end_date;
1350 End If;
1351 Insert into igi_iac_revaluations(
1352 Revaluation_Id,
1353 Book_Type_Code,
1354 Revaluation_Date ,
1355 Revaluation_Period ,
1356 Status,
1357 Reval_Request_Id ,
1358 Create_Request_Id ,
1359 Calling_Program ,
1360 Last_Update_Date,
1361 Created_By ,
1362 Last_Update_Login ,
1363 Last_Updated_By ,
1364 Creation_Date)
1365 Values(
1366 l_reval_id,
1367 C_Upload_Hdr_Rec.book_type_code,
1368 l_reval_date,
1369 C_Upload_Hdr_Rec.period_counter,
1370 'NEW',
1371 null,
1372 null,
1373 'SSUPLOAD',
1374 l_global_date,
1375 l_global_user_id,
1376 l_global_login_id,
1377 l_global_user_id,
1378 l_global_date);
1379
1380 For C_Categories_Rec in C_Categories(p_file_name) Loop
1381 Insert into igi_iac_reval_categories(
1382 Revaluation_Id,
1383 Book_Type_Code,
1384 Category_Id,
1385 Select_Category,
1386 Last_Update_Date,
1387 Created_By ,
1388 Last_Update_Login ,
1389 Last_Updated_By ,
1390 Creation_Date)
1391 Values(
1392 l_reval_id,
1393 C_Upload_Hdr_Rec.book_type_code,
1394 C_Categories_Rec.category_id,
1395 'Y',
1396 l_global_date,
1397 l_global_user_id,
1398 l_global_login_id,
1399 l_global_user_id,
1400 l_global_date);
1401 End Loop;
1402
1403 For C_Upload_Lines_Rec in C_Upload_Lines(p_file_name) Loop
1404
1405 -- bug 3536362, start 2
1406 OPEN C_FA_Asset_Cost(cp_book_type_code => C_Upload_Hdr_Rec.book_type_code,
1407 cp_asset_id => C_Upload_Lines_Rec.asset_id
1408 );
1409 FETCH C_FA_Asset_Cost INTO l_fa_asset_cost;
1410 IF C_FA_Asset_Cost%NOTFOUND THEN
1411 RAISE NO_DATA_FOUND;
1412 END IF;
1413 CLOSE C_FA_Asset_Cost;
1414
1415 OPEN C_IAC_Asset_Cost(cp_book_type_code => C_Upload_Hdr_Rec.book_type_code,
1416 cp_asset_id => C_Upload_Lines_Rec.asset_id,
1417 cp_period_counter => C_Upload_Hdr_Rec.period_counter
1418 );
1419 FETCH C_IAC_Asset_Cost INTO l_iac_asset_cost;
1420 IF C_IAC_Asset_Cost%NOTFOUND THEN
1421 l_iac_asset_cost := 0; -- Bug 8484461
1422 --RAISE NO_DATA_FOUND;
1423 END IF;
1424 CLOSE C_IAC_Asset_Cost;
1425
1426 l_current_cost := l_fa_asset_cost + l_iac_asset_cost;
1427 -- bug 3536362, end 2
1428
1429 -- bug 3412940, start 2
1430 -- l_reval_factor := C_Upload_lines_Rec.new_cost/C_Upload_Lines_Rec.original_cost;
1431 -- bug 3412940, end 2
1432
1433 -- bug 3536362, start 3
1434 l_reval_factor := C_Upload_lines_Rec.new_cost/l_current_cost;
1435 -- bug 3536362, end 3
1436
1437 Insert into igi_iac_reval_asset_rules(
1438 Revaluation_Id,
1439 Book_Type_Code,
1440 Category_Id,
1441 Asset_Id,
1442 Revaluation_Factor,
1443 Revaluation_Type,
1444 New_Cost,
1445 Current_Cost,
1446 Selected_For_Reval_Flag,
1447 Selected_For_Calc_Flag,
1448 Allow_Prof_Update,
1449 Created_By,
1450 Creation_Date,
1451 Last_Update_Login,
1452 Last_Update_Date,
1453 Last_Updated_By)
1454 Values(
1455 l_reval_id,
1456 C_Upload_Hdr_Rec.book_type_code,
1457 C_Upload_Lines_Rec.category_id,
1458 C_Upload_Lines_Rec.asset_id,
1459 l_reval_factor, -- bug 3412940 1,
1460 'P',
1461 C_Upload_Lines_Rec.new_cost,
1462 l_current_cost, -- bug 3536362 C_Upload_Lines_Rec.original_cost,
1463 'Y',
1464 null,
1465 null,
1466 l_global_user_id,
1467 l_global_date,
1468 l_global_login_id,
1469 l_global_date,
1470 l_global_user_id);
1471 If sql%found Then
1472 l_asset_count := l_asset_count + 1;
1473 End If;
1474 End Loop ;
1475
1476 Update igi_iac_upload_headers
1477 Set status_flag = 'T', revaluation_id = l_reval_id
1478 Where file_name = p_file_name;
1479
1480 If sql%notfound then
1481 Fnd_Message.Set_Name('IGI', 'IGI_IAC_UPL_TRANS_FAILURE');
1482 l_message := Fnd_Message.Get;
1483 -- Debug(l_message);
1484 -- bug 3299718, start block
1485 IF (l_state_level >= l_debug_level) THEN
1486 FND_LOG.STRING(l_state_level, 'igi.plsql.IGI_IAC_WEBADI_PKG.Transfer_Data.Msg1',
1487 l_message);
1488 END IF;
1489 -- bug 3299718, end block
1490
1491
1492 Elsif sql%found then
1493 Fnd_Message.Set_Name('IGI', 'IGI_IAC_UPL_TRANS_SUCCESS');
1494 l_message := Fnd_Message.Get;
1495 -- Debug(l_message);
1496 -- bug 3299718, start block
1497 IF (l_state_level >= l_debug_level) THEN
1498 FND_LOG.STRING(l_state_level, 'igi.plsql.IGI_IAC_WEBADI_PKG.Transfer_Data.Msg2',
1499 l_message);
1500 END IF;
1501 -- bug 3299718, end block
1502
1503 Fnd_Message.Set_Name('IGI', 'IGI_IAC_UPL_TRANS_COUNT');
1504 Fnd_Message.Set_Token('ASSET_COUNT',l_asset_count);
1505 l_message := Fnd_Message.Get;
1506 -- Debug(l_message);
1507 -- bug 3299718, start block
1508 IF (l_state_level >= l_debug_level) THEN
1509 FND_LOG.STRING(l_state_level, 'igi.plsql.IGI_IAC_WEBADI_PKG.Transfer_Data.Msg3',
1510 l_message);
1511 END IF;
1512 -- bug 3299718, end block
1513 Fnd_Message.Set_Name('IGI', 'IGI_IAC_UPL_REVAL_ID');
1514 Fnd_Message.Set_Token('REVAL_ID',l_reval_id);
1515 l_message := Fnd_Message.Get;
1516 -- Debug(l_message);
1517 -- bug 3299718, start block
1518 IF (l_state_level >= l_debug_level) THEN
1519 FND_LOG.STRING(l_state_level, 'igi.plsql.IGI_IAC_WEBADI_PKG.Transfer_Data.Msg4',
1520 l_message);
1521 END IF;
1522 -- bug 3299718, end block
1523 End if;
1524 Commit;
1525
1526 If p_preview_flag = 'Y' Then
1527 l_request_id := FND_REQUEST.SUBMIT_REQUEST(
1528 APPLICATION => 'IGI',
1529 PROGRAM => 'IGIIAIAR',
1530 DESCRIPTION => '',
1531 START_TIME => NULL,
1532 SUB_REQUEST => FALSE,
1533 ARGUMENT1 => to_char(l_reval_id),
1534 ARGUMENT2 => C_Upload_Hdr_Rec.book_type_code,
1535 ARGUMENT3 => 'P',
1536 ARGUMENT4 => to_char(C_Upload_Hdr_Rec.period_counter),
1537 ARGUMENT5 => to_char(l_create_req_id),
1538 ARGUMENT6 => CHR(0), ARGUMENT7 => NULL, ARGUMENT8 => NULL,
1539 ARGUMENT9 => NULL, ARGUMENT10 => NULL, ARGUMENT11 => NULL,
1540 ARGUMENT12 => NULL, ARGUMENT13 => NULL, ARGUMENT14 => NULL,
1541 ARGUMENT15 => NULL, ARGUMENT16 => NULL, ARGUMENT17 => NULL,
1542 ARGUMENT18 => NULL, ARGUMENT19 => NULL, ARGUMENT20 => NULL,
1543 ARGUMENT21 => NULL, ARGUMENT22 => NULL, ARGUMENT23 => NULL,
1544 ARGUMENT24 => NULL, ARGUMENT25 => NULL, ARGUMENT26 => NULL,
1545 ARGUMENT27 => NULL, ARGUMENT28 => NULL, ARGUMENT29 => NULL,
1546 ARGUMENT30 => NULL, ARGUMENT31 => NULL, ARGUMENT32 => NULL,
1547 ARGUMENT33 => NULL, ARGUMENT34 => NULL, ARGUMENT35 => NULL,
1548 ARGUMENT36 => NULL, ARGUMENT37 => NULL, ARGUMENT38 => NULL,
1549 ARGUMENT39 => NULL, ARGUMENT40 => NULL, ARGUMENT41 => NULL,
1550 ARGUMENT42 => NULL, ARGUMENT43 => NULL, ARGUMENT44 => NULL,
1551 ARGUMENT45 => NULL, ARGUMENT46 => NULL, ARGUMENT47 => NULL,
1552 ARGUMENT48 => NULL, ARGUMENT49 => NULL, ARGUMENT50 => NULL,
1553 ARGUMENT51 => NULL, ARGUMENT52 => NULL, ARGUMENT53 => NULL,
1554 ARGUMENT54 => NULL, ARGUMENT55 => NULL, ARGUMENT56 => NULL,
1555 ARGUMENT57 => NULL, ARGUMENT58 => NULL, ARGUMENT59 => NULL,
1556 ARGUMENT60 => NULL, ARGUMENT61 => NULL, ARGUMENT62 => NULL,
1557 ARGUMENT63 => NULL, ARGUMENT64 => NULL, ARGUMENT65 => NULL,
1558 ARGUMENT66 => NULL, ARGUMENT67 => NULL, ARGUMENT68 => NULL,
1559 ARGUMENT69 => NULL, ARGUMENT70 => NULL, ARGUMENT71 => NULL,
1560 ARGUMENT72 => NULL, ARGUMENT73 => NULL, ARGUMENT74 => NULL,
1561 ARGUMENT75 => NULL, ARGUMENT76 => NULL, ARGUMENT77 => NULL,
1562 ARGUMENT78 => NULL, ARGUMENT79 => NULL, ARGUMENT80 => NULL,
1563 ARGUMENT81 => NULL, ARGUMENT82 => NULL, ARGUMENT83 => NULL,
1564 ARGUMENT84 => NULL, ARGUMENT85 => NULL, ARGUMENT86 => NULL,
1565 ARGUMENT87 => NULL, ARGUMENT88 => NULL, ARGUMENT89 => NULL,
1566 ARGUMENT90 => NULL, ARGUMENT91 => NULL, ARGUMENT92 => NULL,
1567 ARGUMENT93 => NULL, ARGUMENT94 => NULL, ARGUMENT95 => NULL,
1568 ARGUMENT96 => NULL, ARGUMENT97 => NULL, ARGUMENT98 => NULL,
1569 ARGUMENT99 => NULL, ARGUMENT100 => NULL);
1570
1571 IF l_request_id = 0 THEN
1572 RAISE E_Request_Submit_Error;
1573 End If;
1574 End If;
1575 End Loop;
1576 retcode := 0;
1577 Exception
1578 When E_Unavailable_Period Then
1579 Fnd_Message.Set_Name('IGI', 'IGI_IAC_UPL_UNAVAILABLE_PERIOD');
1580 l_message := Fnd_Message.Get;
1581 -- Debug(l_message);
1582 -- bug 3299718, start block
1583 IF (l_state_level >= l_debug_level) THEN
1584 FND_LOG.STRING(l_state_level, 'igi.plsql.IGI_IAC_WEBADI_PKG.Transfer_Data.Msg5',
1585 l_message);
1586 END IF;
1587 -- bug 3299718, end block
1588
1589 errbuf := l_message;
1590 retcode :=2;
1591 When E_Period_Not_Closed Then
1592 Fnd_Message.Set_Name('IGI', 'IGI_IAC_UPL_DEP_NOT_CLOSED');
1593 l_message := Fnd_Message.Get;
1594 -- Debug(l_message);
1595 -- bug 3299718, start block
1596 IF (l_state_level >= l_debug_level) THEN
1597 FND_LOG.STRING(l_state_level, 'igi.plsql.IGI_IAC_WEBADI_PKG.Transfer_Data.Msg6',
1598 l_message);
1599 END IF;
1600 -- bug 3299718, end block
1601
1602 errbuf := l_message;
1603 retcode :=2;
1604 When E_Request_Submit_Error THEN
1605 Fnd_Message.Set_Name('IGI', 'IGI_IAC_UPL_PREVIEW_FAILED');
1606 l_message := Fnd_Message.Get;
1607 -- Debug(l_message);
1608
1609 -- bug 3299718, start block
1610 IF (l_state_level >= l_debug_level) THEN
1611 FND_LOG.STRING(l_state_level, 'igi.plsql.IGI_IAC_WEBADI_PKG.Transfer_Data.Msg7',
1612 l_message);
1613 END IF;
1614 -- bug 3299718, end block
1615
1616 -- bug 3443410, start 5
1617 WHEN e_preview_exists THEN
1618 /* bug 3495368, start 1
1619 -- commenting out as no longer required
1620 IF (l_error_level >= l_debug_level) THEN
1621 fnd_message.set_name('IGI', 'IGI_IAC_UPL_PREVIEW_EXISTS');
1622 l_mesg1 := fnd_message.get;
1623 FND_LOG.MESSAGE(l_error_level,'igi.plsql.IGI_IAC_WEBADI_PKG.Transfer_Data.Preview Exists', TRUE);
1624 -- fnd_file.put_line required for display to user
1625 fnd_file.put_line(fnd_file.log, l_mesg1);
1626
1627 -- list of assets
1628 l_preview_exists := NULL;
1629 FOR l_preview_exists IN c_preview_exists(l_book_type_code) LOOP
1630 fnd_message.set_name('IGI', 'IGI_IAC_UPL_PREVIEW_ASSETS');
1631 fnd_message.set_token('ASSET_NUM', l_preview_exists.asset_number, FALSE);
1632 l_mesg1 := fnd_message.get;
1633 FND_LOG.MESSAGE(l_error_level,'igi.plsql.IGI_IAC_WEBADI_PKG.Transfer_Data.Preview Exists', TRUE);
1634 -- fnd_file.put_line required for display to user
1635 fnd_file.put_line(fnd_file.log, l_mesg1);
1636 END LOOP;
1637 END IF;
1638 bug 3495368, end 1 */
1639
1640 -- bug 3495368, start 2
1641 fnd_message.set_name('IGI', 'IGI_IAC_UPL_PREVIEW_EXISTS');
1642 l_message := fnd_message.get;
1643 -- bug 3495368, end 2
1644
1645 errbuf := SQLERRM||': '||l_message;
1646 retcode :=2;
1647 -- bug 3443410, end 5
1648
1649 When Others then
1650 errbuf := SQLERRM;
1651 retcode :=2;
1652 End Transfer_Data;
1653 End IGI_IAC_WEBADI_PKG;