1 PACKAGE BODY FA_INVOICE_PVT as
2 /* $Header: FAVINVB.pls 120.28.12020000.3 2013/03/14 13:36:39 dvjoshi ship $ */
3
4 g_release number := fa_cache_pkg.fazarel_release;
5
6 -- used for tracking the total payables cost cleared for all invoices
7 TYPE payables_cost_rec_type IS RECORD
8 (set_of_books_id number,
9 payables_cost number,
10 payables_code_combination_id number,
11 source_dest_code varchar2(15),
12 source_line_id number,
13 asset_invoice_id number);
14
15 TYPE payables_cost_tbl_type IS TABLE OF payables_cost_rec_type index by binary_integer;
16
17 -- private prottypes
18
19 FUNCTION inv_calc_info
20 (p_asset_hdr_rec IN FA_API_TYPES.asset_hdr_rec_type,
21 p_asset_type_rec IN FA_API_TYPES.asset_type_rec_type,
22 p_inv_trans_rec IN FA_API_TYPES.inv_trans_rec_type,
23 px_inv_rec IN OUT NOCOPY FA_API_TYPES.inv_rec_type,
24 px_asset_fin_rec_new IN OUT NOCOPY FA_API_TYPES.asset_fin_rec_type,
25 px_asset_deprn_rec_new IN OUT NOCOPY FA_API_TYPES.asset_deprn_rec_type
26 , p_log_level_rec IN FA_API_TYPES.log_level_rec_type) RETURN BOOLEAN;
27
28 FUNCTION process_invoice
29 (px_trans_rec IN OUT NOCOPY FA_API_TYPES.trans_rec_type,
30 px_asset_hdr_rec IN OUT NOCOPY FA_API_TYPES.asset_hdr_rec_type,
31 p_inv_trans_rec IN FA_API_TYPES.inv_trans_rec_type,
32 px_inv_rec IN OUT NOCOPY FA_API_TYPES.inv_rec_type,
33 p_inv_rate_rec IN FA_API_TYPES.inv_rate_rec_type,
34 p_mrc_sob_type_code IN VARCHAR2
35 , p_log_level_rec IN FA_API_TYPES.log_level_rec_type) RETURN BOOLEAN;
36
37 FUNCTION get_inv_rate
38 (p_trans_rec IN FA_API_TYPES.trans_rec_type,
39 p_asset_hdr_rec IN FA_API_TYPES.asset_hdr_rec_type,
40 p_inv_trans_rec IN FA_API_TYPES.inv_trans_rec_type,
41 px_inv_tbl IN OUT NOCOPY FA_API_TYPES.inv_tbl_type
42 , p_log_level_rec IN FA_API_TYPES.log_level_rec_type) RETURN BOOLEAN;
43
44 FUNCTION post_clearing
45 (p_trans_rec IN FA_API_TYPES.trans_rec_type,
46 p_asset_hdr_rec IN FA_API_TYPES.asset_hdr_rec_type,
47 p_asset_desc_rec IN FA_API_TYPES.asset_desc_rec_type,
48 p_asset_type_rec IN FA_API_TYPES.asset_type_rec_type,
49 p_asset_cat_rec IN FA_API_TYPES.asset_cat_rec_type,
50 p_inv_trans_rec IN FA_API_TYPES.inv_trans_rec_type,
51 p_payables_cost_tbl IN payables_cost_tbl_type,
52 p_payables_cost_mrc_tbl IN payables_cost_tbl_type
53 , p_log_level_rec IN FA_API_TYPES.log_level_rec_type) RETURN BOOLEAN;
54
55 -- public function
56
57 FUNCTION invoice_engine
58 (px_trans_rec IN OUT NOCOPY FA_API_TYPES.trans_rec_type,
59 px_asset_hdr_rec IN OUT NOCOPY FA_API_TYPES.asset_hdr_rec_type,
60 p_asset_desc_rec IN FA_API_TYPES.asset_desc_rec_type,
61 p_asset_type_rec IN FA_API_TYPES.asset_type_rec_type,
62 p_asset_cat_rec IN FA_API_TYPES.asset_cat_rec_type,
63 p_asset_fin_rec_adj IN FA_API_TYPES.asset_fin_rec_type,
64 x_asset_fin_rec_new OUT NOCOPY FA_API_TYPES.asset_fin_rec_type,
65 x_asset_fin_mrc_tbl_new OUT NOCOPY FA_API_TYPES.asset_fin_tbl_type,
66 px_inv_trans_rec IN OUT NOCOPY FA_API_TYPES.inv_trans_rec_type,
67 px_inv_tbl IN OUT NOCOPY FA_API_TYPES.inv_tbl_type,
68 x_asset_deprn_rec_new OUT NOCOPY FA_API_TYPES.asset_deprn_rec_type,
69 x_asset_deprn_mrc_tbl_new OUT NOCOPY FA_API_TYPES.asset_deprn_tbl_type,
70 p_calling_fn IN varchar2
71 , p_log_level_rec IN FA_API_TYPES.log_level_rec_type) RETURN BOOLEAN IS
72
73 l_rowid varchar2(100); -- placeholder for table handlers
74 l_row_count number := 0;
75 l_deprn_count number := 0;
76 l_create_new_row varchar2(3);
77 l_current_fa_cost number;
78 l_current_pa_cost number;
79 l_current_source_line_id number;
80 l_inv_rec_fa_cost_primary number;
81
82 -- local structs used for manipulation
83 l_asset_hdr_rec FA_API_TYPES.asset_hdr_rec_type;
84 l_inv_rec FA_API_TYPES.inv_rec_type;
85 l_inv_rate_rec FA_API_TYPES.inv_rate_rec_type;
86 l_asset_deprn_rec_adj FA_API_TYPES.asset_deprn_rec_type;
87
88 l_count number;
89
90 -- mrc checks
91 l_sob_tbl FA_CACHE_PKG.fazcrsob_sob_tbl_type;
92 l_reporting_flag varchar2(1);
93 l_mrc_rate_found_count number := 0;
94 l_mrc_rate_index number := 0;
95 l_mrc_fin_book_rec number := 0;
96 l_mrc_deprn_rec number := 0;
97
98 l_mrc_date date;
99
100 l_payables_cost_tbl payables_cost_tbl_type;
101 l_payables_cost_mrc_tbl payables_cost_tbl_type;
102 l_payables_cost_count number;
103 l_payables_cost_mrc_count number;
104
105 -- new variables for merging mrc trigger logic
106 l_primary_sob_id NUMBER;
107 l_exchange_date date;
108
109
110 -- exceptions
111 l_calling_fn varchar2(35) := 'fa_inv_pvt.invoice_engine';
112 error_found exception;
113
114
115 BEGIN
116
117 l_asset_hdr_rec := px_asset_hdr_rec;
118
119 if (p_log_level_rec.statement_level) then
120 fa_debug_pkg.add(l_calling_fn,
121 'px_inv_tbl.count',
122 px_inv_tbl.count, p_log_level_rec => p_log_level_rec);
123 end if;
124
125 if (px_inv_tbl.count = 0) then
126 fa_srvr_msg.add_message(
127 calling_fn => l_calling_fn,
128 name => '***NO_INVOICES***',
129 p_log_level_rec => p_log_level_rec);
130 raise error_found;
131 end if;
132
133 /*Bug#13861504 - Validate payabales ccid */
134 for i in 1 .. px_inv_tbl.COUNT loop
135 if (px_inv_tbl(i).Payables_Code_Combination_Id is not NULL) then
136 if not FA_ASSET_VAL_PVT.validate_ccid
137 (p_book_type_code => px_asset_hdr_rec.book_type_code,
138 p_ccid => px_inv_tbl(i).Payables_Code_Combination_Id,
139 p_gl_chart_id => fa_cache_pkg.fazcbc_record.accounting_flex_structure,
140 p_ledger_id => fa_cache_pkg.fazcbc_record.set_of_books_id,
141 p_validation_date => NULL,
142 p_calling_fn => l_calling_fn,
143 p_log_level_rec => p_log_level_rec
144 ) then
145 raise error_found;
146 end if;
147 end if;
148 end loop;
149
150 -- store the current profile value and then use the definitive value
151 -- for the book in question and check whether this is a reporting book.
152
153 if (fa_cache_pkg.fazcbc_record.mc_source_flag = 'Y') then
154
155 -- call the sob cache to get the table of sob_ids
156 if not FA_CACHE_PKG.fazcrsob
157 (x_book_type_code => px_asset_hdr_rec.book_type_code,
158 x_sob_tbl => l_sob_tbl, p_log_level_rec => p_log_level_rec) then
159 raise error_found;
160 end if;
161
162 end if;
163
164
165
166 -- insert invoice transaction row(only for null values)
167 -- when called for destination asset in a source line transfer it will be populated)
168
169
170 if (px_inv_trans_rec.invoice_transaction_id is null) then
171
172 FA_INVOICE_TRANSACTIONS_PKG.Insert_Row
173 (X_Rowid => l_rowid,
174 X_Invoice_Transaction_Id => px_inv_trans_rec.invoice_transaction_id ,
175 X_Book_Type_Code => px_asset_hdr_rec.book_type_code,
176 X_Transaction_Type => px_inv_trans_rec.transaction_type,
177 X_Date_Effective => sysdate,
178 X_Calling_Fn => 'FA_INVOICE_API_PKG.invoice_engine'
179 , p_log_level_rec => p_log_level_rec);
180
181 end if;
182
183
184 -- initialize the new fin struct to the incoming values
185 -- in the case of the addition engine, this will be
186 -- populated before hand for fields like DPIS, etc.
187 -- for adjustments, it will be null anyway
188
189 x_asset_fin_rec_new := p_asset_fin_rec_adj;
190 x_asset_fin_rec_new.original_cost := 0;
191 -- NOTE: do not set salvage here as it needs to be null
192 -- when not populated do the default percent salvage is used
193 -- within the cal engine
194 -- x_asset_fin_rec_new.salvage_value := 0;
195 x_asset_fin_rec_new.recoverable_cost := 0;
196 x_asset_fin_rec_new.adjusted_recoverable_cost := 0;
197 x_asset_fin_rec_new.reval_amortization_basis := 0;
198 x_asset_fin_rec_new.old_adjusted_cost := 0;
199 x_asset_fin_rec_new.cost := 0;
200
201 x_asset_deprn_rec_new.deprn_amount := 0;
202 x_asset_deprn_rec_new.ytd_deprn := 0;
203 x_asset_deprn_rec_new.deprn_reserve := 0;
204 x_asset_deprn_rec_new.bonus_ytd_deprn := 0;
205 x_asset_deprn_rec_new.bonus_deprn_reserve := 0;
206 x_asset_deprn_rec_new.reval_amortization_basis := 0;
207 x_asset_deprn_rec_new.reval_deprn_expense := 0;
208 x_asset_deprn_rec_new.reval_ytd_deprn := 0;
209 x_asset_deprn_rec_new.reval_deprn_reserve := 0;
210
211
212 -- init the mrc fin structs - the fin struct will NOT be populated yet for invoice trx's
213 -- any trx or just adjs via invoice trx?
214
215
216 l_count := 0;
217
218 FOR l_sob_index in 1..l_sob_tbl.count LOOP
219
220 l_count := l_count + 1;
221 x_asset_fin_mrc_tbl_new(l_count) := x_asset_fin_rec_new;
222 x_asset_fin_mrc_tbl_new(l_count).set_of_books_id := l_sob_tbl(l_sob_index);
223 x_asset_deprn_mrc_tbl_new(l_count) := x_asset_deprn_rec_new;
224 x_asset_deprn_mrc_tbl_new(l_count).set_of_books_id := l_sob_tbl(l_sob_index);
225
226 end loop;
227
228
229 l_asset_hdr_rec.set_of_books_id := px_asset_hdr_rec.set_of_books_id;
230
231 l_row_count := 0;
232
233 -- load the inv_rate table for existing source lines
234 -- SLA - this will fetch all currencies at one time
235
236 if not get_inv_rate
237 (p_trans_rec => px_trans_rec,
238 p_asset_hdr_rec => px_asset_hdr_rec,
239 p_inv_trans_rec => px_inv_trans_rec,
240 px_inv_tbl => px_inv_tbl,
241 p_log_level_rec => p_log_level_rec) then
242 raise error_found;
243 end if;
244
245 while (l_row_count < px_inv_tbl.count) loop -- loop for invoices array
246
247 l_current_source_line_id := px_inv_tbl(l_row_count + 1).source_line_id;
248
249 -- call for primary book first
250 -- initialize the invoice structs to be passed
251 -- to process_invoice to the current row in the table.
252 -- The values in this struct will be added to those
253 -- being incremented in the p_fin_new struct.
254
255
256 -- load the non financial info into the struct using get utility
257 if (px_inv_trans_rec.transaction_type <> 'MASS ADDITION' and
258 px_inv_trans_rec.transaction_type <> 'INVOICE ADDITION' and
259 px_inv_tbl(l_row_count + 1).source_line_id is not null) then
260
261 if (p_log_level_rec.statement_level) then
262 fa_debug_pkg.add(l_calling_fn,
263 'in invoice loop, source_line_id',
264 px_inv_tbl(l_row_count + 1).source_line_id);
265 fa_debug_pkg.add(l_calling_fn,
266 'in invoice loop, fa cost',
267 px_inv_tbl(l_row_count + 1).fixed_assets_cost);
268 fa_debug_pkg.add(l_calling_fn,
269 'in invoice loop, ap cost',
270 px_inv_tbl(l_row_count + 1).payables_cost);
271 end if;
272
273 -- set the source_line and sob in the local struct
274 l_inv_rec.source_line_Id := px_inv_tbl(l_row_count + 1).source_line_id;
275 if not FA_UTIL_PVT.get_inv_rec
276 (px_inv_rec => l_inv_rec,
277 p_mrc_sob_type_code => 'P',
278 p_set_of_books_id => null,
279 p_inv_trans_rec => px_inv_trans_rec, p_log_level_rec => p_log_level_rec) then
280 raise error_found;
281 end if;
282
283 if (p_log_level_rec.statement_level) then
284 fa_debug_pkg.add(l_calling_fn,
285 'after get_inv_rec, deleted_flag',
286 l_inv_rec.deleted_flag, p_log_level_rec => p_log_level_rec);
287 fa_debug_pkg.add(l_calling_fn,
288 'after get_inv_rec, dep_in_grp_flag',
289 l_inv_rec.depreciate_in_group_flag, p_log_level_rec => p_log_level_rec);
290 end if;
291
292 -- set the current fa cost from the get_inv call
293 l_current_fa_cost := l_inv_rec.fixed_assets_cost;
294 l_current_pa_cost := l_inv_rec.payables_cost;
295
296 -- reset any info that might change as a result of the invoice transaction
297 -- remember that the fa cost is always the delta thus, it goes to 0 if there
298 -- is no change. for deletes and reinstates only the depreciate can flag
299 -- however, we need the current cost in order to set the delta info in the
300 -- fin rec struct. For deletes, we flip the sign and later on flip it back
301
302 -- adding new flag for group requirements to allow for portions of
303 -- a cip asset's cost to be included in the depreciable basis
304 -- when set to Y, the cip_cost decreases!
305
306 if (px_inv_trans_rec.transaction_type = 'INVOICE DELETE') then
307 if (l_inv_rec.deleted_flag = 'YES') then
308 fa_srvr_msg.add_message(
309 calling_fn => l_calling_fn,
310 name => '***FA_INV_ALREADY_DEL***',
311 p_log_level_rec => p_log_level_rec);
312 raise error_found;
313 end if;
314
315 l_inv_rec.deleted_flag := 'YES';
316 l_inv_rec.Fixed_Assets_Cost := -l_current_fa_cost;
317 l_inv_rec.Payables_Cost := -l_current_pa_cost;
318 if (p_asset_type_rec.asset_type = 'CIP') then
319 if (nvl(l_inv_rec.depreciate_in_group_flag, 'N') = 'Y') then
320 l_inv_rec.Cip_Cost := -l_current_fa_cost;
321 else
322 l_inv_rec.Cip_Cost := 0;
323 end if;
324 end if;
325 elsif (px_inv_trans_rec.transaction_type = 'INVOICE REINSTATE') then
326 if (nvl(l_inv_rec.deleted_flag, 'NO') = 'NO') then
327 fa_srvr_msg.add_message(
328 calling_fn => l_calling_fn,
329 name => '***FA_INV_ALREADY_REINS***',
330 p_log_level_rec => p_log_level_rec);
331 raise error_found;
332 end if;
333
334 l_inv_rec.deleted_flag := 'NO';
335 l_inv_rec.Fixed_Assets_Cost := l_current_fa_cost;
336 l_inv_rec.Payables_Cost := l_current_pa_cost;
337 if (p_asset_type_rec.asset_type = 'CIP') then
338 if (nvl(l_inv_rec.depreciate_in_group_flag, 'N') = 'Y') then
339 l_inv_rec.Cip_Cost := l_current_fa_cost;
340 else
341 l_inv_rec.Cip_Cost := 0;
342 end if;
343 end if;
344 elsif (px_inv_trans_rec.transaction_type = 'INVOICE DEP') then
345 if (l_inv_rec.depreciate_in_group_flag = 'Y') then
346 fa_srvr_msg.add_message(
347 calling_fn => l_calling_fn,
348 name => '***FA_INV_ALREADY_DEP***',
349 p_log_level_rec => p_log_level_rec);
350 raise error_found;
351 end if;
352
353 if (p_asset_type_rec.asset_type = 'CIP') then
354 l_inv_rec.depreciate_in_group_flag := 'Y';
355 l_inv_rec.Cip_Cost := -l_current_fa_cost;
356 l_inv_rec.Fixed_Assets_Cost := 0;
357 l_inv_rec.Payables_Cost := 0;
358 else
359 raise error_found;
360 end if;
361 elsif (px_inv_trans_rec.transaction_type = 'INVOICE NO DEP') then
362 if (nvl(l_inv_rec.depreciate_in_group_flag, 'N') = 'N') then
363 fa_srvr_msg.add_message(
364 calling_fn => l_calling_fn,
365 name => '***FA_INV_ALREADY_NO_DEP***',
366 p_log_level_rec => p_log_level_rec);
367 raise error_found;
368 end if;
369
370 if (p_asset_type_rec.asset_type = 'CIP') then
371 l_inv_rec.depreciate_in_group_flag := 'N';
372 l_inv_rec.Cip_Cost := l_current_fa_cost;
373 l_inv_rec.Fixed_Assets_Cost := 0;
374 l_inv_rec.Payables_Cost := 0;
375 else
376 raise error_found;
377 end if;
378 else
379 l_inv_rec.Fixed_Assets_Cost := nvl(px_inv_tbl(l_row_count + 1).Fixed_Assets_Cost, 0);
380
381 -- do not allow delta cost to exceed the current fa cost
382 -- in the case of an invoice transfer
383 if (px_inv_trans_rec.transaction_type = 'INVOICE TRANSFER' and
384 px_inv_tbl(l_row_count + 1).source_line_id is not null) then
385
386 -- can't transfer nothing on non-zero line
387 if (l_inv_rec.Fixed_Assets_Cost = 0) then
388 if (l_current_fa_cost <> 0) then
389 fa_srvr_msg.add_message(
390 calling_fn => l_calling_fn,
391 name => 'FA_TFRINV_NONE_TFR_COST', p_log_level_rec => p_log_level_rec);
392 raise error_found;
393 end if;
394 -- must transfer zero on a zero line
395 elsif (l_current_fa_cost = 0) then
396 if (l_inv_rec.Fixed_Assets_Cost <> 0) then
397 fa_srvr_msg.add_message(
398 calling_fn => l_calling_fn,
399 name => 'FA_TFRINV_ZERO_TFR_COST', p_log_level_rec => p_log_level_rec);
400 raise error_found;
401 end if;
402 -- may not increase the value (due to same sign)
403 elsif (sign(l_inv_rec.Fixed_Assets_Cost) = sign(l_current_fa_cost)) then
404 fa_srvr_msg.add_message(
405 calling_fn => l_calling_fn,
406 name => 'FA_TFRINV_NOT_BTWN_TFR_COST', p_log_level_rec => p_log_level_rec);
407 raise error_found;
408 -- may not transfer more than current value
409 elsif ((sign(l_current_fa_cost) > 0 and
410 -l_inv_rec.Fixed_Assets_Cost > l_current_fa_cost) or
411 (sign(l_current_fa_cost) < 0 and
412 -l_inv_rec.Fixed_Assets_Cost < l_current_fa_cost)) then
413 fa_srvr_msg.add_message(
414 calling_fn => l_calling_fn,
415 name => 'FA_TFRINV_NOT_BTWN_TFR_COST', p_log_level_rec => p_log_level_rec);
416 raise error_found;
417 end if;
418
419 end if; -- invoice transfer
420
421 -- BUG# 2314466
422 -- do not use nvl here as we want to insert null intentionally
423 -- and the null value is needed to correctly derive original cost later
424 l_inv_rec.Payables_Cost := px_inv_tbl(l_row_count + 1).Payables_Cost;
425
426 if (p_log_level_rec.statement_level) then
427 fa_debug_pkg.add('fa_inv_pvt',
428 'asset_type',
429 p_asset_type_rec.asset_type, p_log_level_rec => p_log_level_rec);
430 end if;
431
432 -- group related stuff - insure we reflect changes to fa_cost in cip_cost
433 l_inv_rec.depreciate_in_group_flag := l_inv_rec.depreciate_in_group_flag;
434
435 if (p_asset_type_rec.asset_type = 'CIP') then
436 if (nvl(l_inv_rec.depreciate_in_group_flag, 'N') = 'Y') then
437 l_inv_rec.Cip_Cost := 0;
438 else
439 l_inv_rec.Cip_Cost := nvl(px_inv_tbl(l_row_count + 1).Fixed_Assets_Cost, 0);
440 end if;
441 end if;
442
443 end if;
444
445 if (p_log_level_rec.statement_level) then
446 fa_debug_pkg.add('fa_inv_pvt',
447 'cip_cost',
448 l_inv_rec.Cip_Cost, p_log_level_rec => p_log_level_rec);
449 fa_debug_pkg.add('fa_inv_pvt',
450 'fa_cost',
451 l_inv_rec.fixed_assets_Cost, p_log_level_rec => p_log_level_rec);
452 end if;
453
454
455 l_inv_rec.Po_Vendor_Id := nvl(px_inv_tbl(l_row_count + 1).Po_Vendor_Id,
456 l_inv_rec.Po_Vendor_Id);
457 l_inv_rec.Asset_Invoice_Id := nvl(px_inv_tbl(l_row_count + 1).Asset_Invoice_Id,
458 l_inv_rec.Asset_Invoice_Id);
459 l_inv_rec.Po_Number := nvl(px_inv_tbl(l_row_count + 1).Po_Number,
460 l_inv_rec.Po_Number);
461 l_inv_rec.Invoice_Number := nvl(px_inv_tbl(l_row_count + 1).Invoice_Number,
462 l_inv_rec.Invoice_Number);
463 l_inv_rec.Payables_Batch_Name := nvl(px_inv_tbl(l_row_count + 1).Payables_Batch_Name,
464 l_inv_rec.Payables_Batch_Name);
465 l_inv_rec.Payables_Code_Combination_Id := nvl(px_inv_tbl(l_row_count + 1).Payables_Code_Combination_Id,
466 l_inv_rec.Payables_Code_Combination_Id);
467 l_inv_rec.Feeder_System_Name := nvl(px_inv_tbl(l_row_count + 1).Feeder_System_Name,
468 l_inv_rec.Feeder_System_Name);
469 l_inv_rec.Create_Batch_Date := nvl(px_inv_tbl(l_row_count + 1).Create_Batch_Date,
470 l_inv_rec.Create_Batch_Date);
471 l_inv_rec.Create_Batch_Id := nvl(px_inv_tbl(l_row_count + 1).Create_Batch_Id,
472 l_inv_rec.Create_Batch_Id);
473 l_inv_rec.Invoice_Date := nvl(px_inv_tbl(l_row_count + 1).Invoice_Date,
474 l_inv_rec.Invoice_Date);
475 l_inv_rec.Payables_Cost := nvl(px_inv_tbl(l_row_count + 1).Payables_Cost,
476 l_inv_rec.Payables_Cost);
477 l_inv_rec.Post_Batch_Id := nvl(px_inv_tbl(l_row_count + 1).Post_Batch_Id,
478 l_inv_rec.Post_Batch_Id);
479 l_inv_rec.Invoice_Id := nvl(px_inv_tbl(l_row_count + 1).Invoice_Id,
480 l_inv_rec.Invoice_Id);
481 l_inv_rec.Ap_Distribution_Line_Number := nvl(px_inv_tbl(l_row_count + 1).Ap_Distribution_Line_Number,
482 l_inv_rec.Ap_Distribution_Line_Number);
483 l_inv_rec.Payables_Units := nvl(px_inv_tbl(l_row_count + 1).Payables_Units,
484 l_inv_rec.Payables_Units);
485 l_inv_rec.Split_Merged_Code := nvl(px_inv_tbl(l_row_count + 1).Split_Merged_Code,
486 l_inv_rec.Split_Merged_Code);
487 l_inv_rec.Description := nvl(px_inv_tbl(l_row_count + 1).Description,
488 l_inv_rec.Description);
489 l_inv_rec.Parent_Mass_Addition_Id := nvl(px_inv_tbl(l_row_count + 1).Parent_Mass_Addition_Id,
490 l_inv_rec.Parent_Mass_Addition_Id);
491 l_inv_rec.Attribute1 := nvl(px_inv_tbl(l_row_count + 1).Attribute1,
492 l_inv_rec.Attribute1);
493 l_inv_rec.Attribute2 := nvl(px_inv_tbl(l_row_count + 1).Attribute2,
494 l_inv_rec.Attribute2);
495 l_inv_rec.Attribute3 := nvl(px_inv_tbl(l_row_count + 1).Attribute3,
496 l_inv_rec.Attribute3);
497 l_inv_rec.Attribute4 := nvl(px_inv_tbl(l_row_count + 1).Attribute4,
498 l_inv_rec.Attribute4);
499 l_inv_rec.Attribute5 := nvl(px_inv_tbl(l_row_count + 1).Attribute5,
500 l_inv_rec.Attribute5);
501 l_inv_rec.Attribute6 := nvl(px_inv_tbl(l_row_count + 1).Attribute6,
502 l_inv_rec.Attribute6);
503 l_inv_rec.Attribute7 := nvl(px_inv_tbl(l_row_count + 1).Attribute7,
504 l_inv_rec.Attribute7);
505 l_inv_rec.Attribute8 := nvl(px_inv_tbl(l_row_count + 1).Attribute8,
506 l_inv_rec.Attribute8);
507 l_inv_rec.Attribute9 := nvl(px_inv_tbl(l_row_count + 1).Attribute9,
508 l_inv_rec.Attribute9);
509 l_inv_rec.Attribute10 := nvl(px_inv_tbl(l_row_count + 1).Attribute10,
510 l_inv_rec.Attribute10);
511 l_inv_rec.Attribute11 := nvl(px_inv_tbl(l_row_count + 1).Attribute11,
512 l_inv_rec.Attribute11);
513 l_inv_rec.Attribute12 := nvl(px_inv_tbl(l_row_count + 1).Attribute12,
514 l_inv_rec.Attribute12);
515 l_inv_rec.Attribute13 := nvl(px_inv_tbl(l_row_count + 1).Attribute13,
516 l_inv_rec.Attribute13);
517 l_inv_rec.Attribute14 := nvl(px_inv_tbl(l_row_count + 1).Attribute14,
518 l_inv_rec.Attribute14);
519 l_inv_rec.Attribute15 := nvl(px_inv_tbl(l_row_count + 1).Attribute15,
520 l_inv_rec.Attribute15);
521 l_inv_rec.Attribute_Category_Code := nvl(px_inv_tbl(l_row_count + 1).Attribute_Category_Code,
522 l_inv_rec.Attribute_Category_Code);
523 l_inv_rec.Unrevalued_Cost := nvl(px_inv_tbl(l_row_count + 1).Unrevalued_Cost,
524 l_inv_rec.Unrevalued_Cost);
525 l_inv_rec.Merged_Code := nvl(px_inv_tbl(l_row_count + 1).Merged_Code,
526 l_inv_rec.Merged_Code);
527 l_inv_rec.Split_Code := nvl(px_inv_tbl(l_row_count + 1).Split_Code,
528 l_inv_rec.Split_Code);
529 l_inv_rec.Merge_Parent_Mass_Additions_Id := nvl(px_inv_tbl(l_row_count + 1).Merge_Parent_Mass_Additions_Id,
530 l_inv_rec.Merge_Parent_Mass_Additions_Id);
531 l_inv_rec.Split_Parent_Mass_Additions_Id := nvl(px_inv_tbl(l_row_count + 1).Split_Parent_Mass_Additions_Id,
532 l_inv_rec.Split_Parent_Mass_Additions_Id);
533 l_inv_rec.Project_Asset_Line_Id := nvl(px_inv_tbl(l_row_count + 1).Project_Asset_Line_Id,
534 l_inv_rec.Project_Asset_Line_Id);
535 l_inv_rec.Project_Id := nvl(px_inv_tbl(l_row_count + 1).Project_Id,
536 l_inv_rec.Project_Id);
537 l_inv_rec.Task_Id := nvl(px_inv_tbl(l_row_count + 1).Task_Id,
538 l_inv_rec.Task_Id);
539
540 -- added for R12
541 l_inv_rec.invoice_distribution_id := nvl(px_inv_tbl(l_row_count + 1).invoice_distribution_id,
542 l_inv_rec.invoice_distribution_id);
543 l_inv_rec.invoice_line_number := nvl(px_inv_tbl(l_row_count + 1).invoice_line_number,
544 l_inv_rec.invoice_line_number);
545 l_inv_rec.po_distribution_id := nvl(px_inv_tbl(l_row_count + 1).po_distribution_id,
546 l_inv_rec.po_distribution_id);
547
548 l_inv_rate_rec := null;
549
550 else
551
552 l_current_fa_cost := 0;
553 l_current_pa_cost := 0;
554
555 l_inv_rec := px_inv_tbl(l_row_count + 1);
556 l_inv_rate_rec := null;
557
558 if (l_inv_rec.deleted_flag is null) then
559 l_inv_rec.deleted_flag := 'NO';
560 end if;
561
562 if (p_asset_type_rec.asset_type = 'CIP') then
563 if (nvl(l_inv_rec.depreciate_in_group_flag, 'N') = 'Y') then
564 l_inv_rec.Cip_Cost := 0;
565 else
566 l_inv_rec.Cip_Cost := nvl(px_inv_tbl(l_row_count + 1).Fixed_Assets_Cost, 0);
567 end if;
568 end if;
569
570 if (p_log_level_rec.statement_level) then
571 fa_debug_pkg.add('fa_inv_pvt',
572 'cip_cost',
573 l_inv_rec.Cip_Cost, p_log_level_rec => p_log_level_rec);
574 fa_debug_pkg.add('fa_inv_pvt',
575 'fa_cost',
576 l_inv_rec.fixed_assets_Cost, p_log_level_rec => p_log_level_rec);
577 end if;
578
579 end if;
580
581 -- call invoice calc
582
583 if not inv_calc_info
584 (p_asset_hdr_rec => px_asset_hdr_rec,
585 p_asset_type_rec => p_asset_type_rec,
586 p_inv_trans_rec => px_inv_trans_rec,
587 px_inv_rec => l_inv_rec,
588 px_asset_fin_rec_new => x_asset_fin_rec_new,
589 px_asset_deprn_rec_new => x_asset_deprn_rec_new,
590 p_log_level_rec => p_log_level_rec
591 ) then
592 raise error_found;
593 end if;
594
595 -- In the case of a source line transfer on the source side,
596 -- check the delta and original cost values for the primary book
597 -- if they are equal and opposite (i.e. tranferring out all of the cost)
598 -- we will only terminate the existing row, not create a new one.
599 -- SRC line ret should have same behavior with inv transfer.
600 -- Reisntate is not handle below since existing cost +
601 -- cost to reinstate won't be zero.
602
603 -- In case of full source line retirement, new line with 0 amount
604 -- still needs to be created.
605
606 -- bug 2543777 (old 2557171)
607
608 -- also adding a join to asset_id to insure the invoice line
609 -- in question belongs to the asset on which transaction is being
610 -- performed. get_inv_rec doesn't have such a parameter so doing
611 -- this to avoid dependancies.
612
613 if (px_inv_trans_rec.transaction_type in ('INVOICE TRANSFER', 'REINSTATEMENT') and
614 l_inv_rec.source_line_id is not null) then
615
616 select decode(fixed_assets_cost + l_inv_rec.fixed_assets_cost,
617 0, 'NO',
618 'YES')
619 into l_create_new_row
620 from fa_asset_invoices
621 where source_line_id = l_inv_rec.source_line_id
622 and asset_id = px_asset_hdr_rec.asset_Id;
623
624 else
625 if (l_inv_rec.source_line_id is not null) then
626 select 'YES'
627 into l_create_new_row
628 from fa_asset_invoices
629 where source_line_id = l_inv_rec.source_line_id
630 and asset_id = px_asset_hdr_rec.asset_id;
631 else
632 l_create_new_row := 'YES';
633 end if;
634 end if;
635
636 -- for non-invoice-addition calls, terminate the existing rows for both
637 -- primary and mrc based on the incoming source line id
638
639 if (px_inv_trans_rec.transaction_type <> 'MASS ADDITION' and
640 px_inv_trans_rec.transaction_type <> 'INVOICE ADDITION') then
641
642 update fa_asset_invoices
643 set date_ineffective = sysdate,
644 invoice_transaction_id_out = px_inv_trans_rec.invoice_transaction_id
645 where source_line_id = l_inv_rec.source_line_id;
646
647 update fa_mc_asset_invoices
648 set date_ineffective = sysdate,
649 invoice_transaction_id_out = px_inv_trans_rec.invoice_transaction_id
650 where source_line_id = l_inv_rec.source_line_id;
651
652 end if;
653
654
655 -- BUG# 2622722
656 -- do this for all lines for addition and transfer transactions,
657 -- not just those from mass additions. note that non-xfr adjustment
658 -- will use the flexbuilt ccids for now. section has been moved up so
659 -- tht the deltas are used, not the final amounts (alos done for mrc below)
660
661 if (p_log_level_rec.statement_level) then
662 fa_debug_pkg.add(l_calling_fn,
663 'prior to loading payables info, trx_type',
664 px_inv_trans_rec.transaction_type, p_log_level_rec => p_log_level_rec);
665 fa_debug_pkg.add(l_calling_fn,
666 'prior to loading payables info, payables_cost',
667 l_inv_rec.payables_cost, p_log_level_rec => p_log_level_rec);
668 end if;
669
670 -- SLA conditional handling for clearing for 11i and R12 below:
671 if ((px_inv_trans_rec.transaction_type = 'MASS ADDITION' or
672 px_inv_trans_rec.transaction_type = 'INVOICE TRANSFER' or
673 px_inv_trans_rec.transaction_type = 'INVOICE ADDITION') and
674 l_inv_rec.payables_cost <> 0 and
675 (G_release <> 11 or
676 l_inv_rec.payables_code_combination_id is not null)) then
677 l_payables_cost_count := l_payables_cost_tbl.count + 1;
678 l_payables_cost_tbl(l_payables_cost_count).payables_cost :=
679 l_inv_rec.payables_cost;
680 l_payables_cost_tbl(l_payables_cost_count).payables_code_combination_id :=
681 l_inv_rec.payables_code_combination_id;
682 l_payables_cost_tbl(l_payables_cost_count).source_dest_code :=
683 l_inv_rec.source_dest_code;
684
685 -- for new lines, the source line is is not known
686 -- yet so we assign this after nexval fetch
687 -- l_payables_cost_tbl(l_payables_cost_count).source_line_id :=
688 -- l_inv_rec.source_line_id;
689
690 end if;
691
692
693 if (l_create_new_row = 'YES') OR
694 (px_inv_trans_rec.transaction_type = 'INVOICE TRANSFER' and
695 l_create_new_row = 'NO') then
696
697 -- now for invoice transfers and adjustments, use the current values of the invoice
698 -- that was just terminated and then add the delta. This will be the resulting cost
699 -- of the new line. For invoice transfers, if full cost was transfered, we will
700 -- be entering this logic for the source asset. Delta is the net effect, neg or pos.
701 -- SRC line ret should have same behavior with inv transfer.
702
703 if (px_inv_trans_rec.transaction_type = 'INVOICE ADJUSTMENT' or
704 (px_inv_trans_rec.transaction_type in ('INVOICE TRANSFER',
705 'RETIREMENT',
706 'REINSTATEMENT') and
707 l_inv_rec.source_line_id is not null)) then
708
709 l_inv_rec.fixed_assets_cost := nvl(l_current_fa_cost, 0) +
710 nvl(l_inv_rec.fixed_assets_cost, 0);
711
712 -- BUG# 2314466
713 -- do not use nvl as form transactions should insert null
714 -- and this is used for deriving original_cost later on too
715
716 l_inv_rec.payables_cost := l_current_pa_cost +
717 l_inv_rec.payables_cost;
718
719
720 -- for deletes, reset the sign on fa_cost
721 -- no need for group depreciation change as delta = 0
722 elsif (px_inv_trans_rec.transaction_type = 'INVOICE DELETE' or
723 px_inv_trans_rec.transaction_type = 'INVOICE DEP' or
724 px_inv_trans_rec.transaction_type = 'INVOICE NO DEP') then
725 l_inv_rec.Fixed_Assets_Cost := l_current_fa_cost;
726 l_inv_rec.Payables_Cost := l_current_pa_cost;
727 end if;
728
729 -- original code here for setting up the payables cost rec/table has been moved above
730 -- the previous section as this needs to be done, before resetting the cost amounts
731
732
733 -- Need to get the asset_invoice_id and source_line_id values here
734 -- first assign the old source_line_id to self join column
735 -- for audit trail (BUG# 3033220). On the source side, the
736 -- source line id is populated, on the destination side, it
737 -- is not, but the invoice transfer api will populate the
738 -- the prior value for the dest
739
740 if (l_inv_rec.source_line_id is not null) then
741 l_inv_rec.prior_source_line_id := l_inv_rec.source_line_id;
742 end if;
743
744 select FA_ASSET_INVOICES_S.nextval
745 into l_inv_rec.source_line_id
746 from dual;
747
748
749 -- R12: need to assign source lne id here
750 if ((px_inv_trans_rec.transaction_type = 'MASS ADDITION' or
751 px_inv_trans_rec.transaction_type = 'INVOICE TRANSFER' or
752 px_inv_trans_rec.transaction_type = 'INVOICE ADDITION') and
753 l_inv_rec.payables_cost <> 0) then
754
755 l_payables_cost_tbl(l_payables_cost_count).source_line_id :=
756 l_inv_rec.source_line_id;
757 end if;
758
759
760 if l_inv_rec.asset_invoice_id is null then
761 select FA_MASS_ADDITIONS_S.nextval
762 into l_inv_rec.asset_invoice_id
763 from dual;
764 end if;
765
766 if (l_create_new_row = 'YES') then
767 if not process_invoice
768 (px_trans_rec => px_trans_rec,
769 px_asset_hdr_rec => px_asset_hdr_rec,
770 p_inv_trans_rec => px_inv_trans_rec,
771 px_inv_rec => l_inv_rec, -- current row primary,
772 p_inv_rate_rec => l_inv_rate_rec, -- null for primary
773 p_mrc_sob_type_code => 'P',
774 p_log_level_rec => p_log_level_rec
775 ) then raise error_found;
776 end if;
777 end if;
778
779 l_inv_rec_fa_cost_primary := l_inv_rec.fixed_assets_cost;
780
781 -- call process_invoice for reporting books by looping through each reporting book
782
783 if (fa_cache_pkg.fazcbc_record.mc_source_flag = 'Y') then
784
785 if (px_inv_tbl(l_row_count + 1).inv_rate_tbl.count > 0 and
786 px_inv_tbl(l_row_count + 1).inv_rate_tbl.count <> l_sob_tbl.count) then
787 fa_srvr_msg.add_message(
788 calling_fn => l_calling_fn,
789 name => '***NOT_ENOUGH_INVRATES***');
790 raise error_found; -- not enough rates for each invoice
791 end if; -- end init mrc
792
793
794 FOR l_sob_index in 1..l_sob_tbl.count LOOP
795 if (p_log_level_rec.statement_level) then
796 fa_debug_pkg.add(l_calling_fn,
797 'in reporting loop, reporting_sob',
798 l_sob_tbl(l_sob_index));
799 end if;
800
801 -- BUG# 2632955
802 -- call the cache to set the sob_id used for rounding and other lower
803 -- level code for each book.
804 if NOT fa_cache_pkg.fazcbcs(X_book => px_asset_hdr_rec.book_type_code,
805 X_set_of_books_id => l_sob_tbl(l_sob_index),
806 p_log_level_rec => p_log_level_rec) then
807 raise error_found;
808 end if;
809
810 l_asset_hdr_rec.set_of_books_id := l_sob_tbl(l_sob_index);
811 l_mrc_rate_found_count := 0;
812 l_mrc_rate_index := 0;
813
814
815 -- need to locate the matching invoice rate for the given invoice.
816 -- can do this by this inv_indicator for new lines or theoretically
817 -- via source_line_id for existing ones. inv_indicator is simply a
818 -- a temporary key so calling code doesn't have to access sequences, etc.
819
820 for i in 1..px_inv_tbl(l_row_count + 1).inv_rate_tbl.count loop
821
822 if (p_log_level_rec.statement_level) then
823 fa_debug_pkg.add(l_calling_fn,
824 'inside',
825 'inv rate loop',
826 p_log_level_rec);
827 fa_debug_pkg.add(l_calling_fn,
828 'px_inv_tbl(l_row_count + 1).inv_rate_tbl(i).set_of_books_id',
829 px_inv_tbl(l_row_count + 1).inv_rate_tbl(i).set_of_books_id,
830 p_log_level_rec);
831 fa_debug_pkg.add(l_calling_fn,
832 'sob_id',
833 l_sob_tbl(l_sob_index),
834 p_log_level_rec);
835
836 end if;
837
838 -- match only based on sob now, indicator is obsolete in SLA as we have nested the rate
839 if (px_inv_tbl(l_row_count + 1).inv_rate_tbl(i).set_of_books_id = l_sob_tbl(l_sob_index)) then
840 l_mrc_rate_found_count := l_mrc_rate_found_count + 1;
841 l_mrc_rate_index := i;
842 l_inv_rate_rec := px_inv_tbl(l_row_count + 1).inv_rate_tbl(i);
843 end if;
844
845 end loop;
846
847 -- BUG# 2613834
848 -- exchange will no longer be derived at insertion
849 -- via the trigger, but will be done here inside the engine
850 -- thus if rate isn't populated, we'll find it for AP/PA/other lines
851
852 if (l_mrc_rate_found_count > 1) then
853
854 fa_srvr_msg.add_message(
855 calling_fn => l_calling_fn,
856 name => '***WRONG_NUM_INVRATES2***',
857 p_log_level_rec => p_log_level_rec);
858 raise error_found;
859 elsif (l_mrc_rate_found_count = 0) then
860
861 if (p_log_level_rec.statement_level) then
862 fa_debug_pkg.add(l_calling_fn,
863 'entering',
864 'no rate found logic', p_log_level_rec => p_log_level_rec);
865 end if;
866
867 l_inv_rate_rec.set_of_books_id := l_sob_tbl(l_sob_index);
868
869 -- note: the following is only used for non-AP and non-PA lines
870 l_exchange_date := p_asset_fin_rec_adj.date_placed_in_service; -- inv date?
871 l_exchange_date := px_trans_rec.transaction_date_entered;
872
873 if not FA_MC_UTIL_PVT.get_invoice_rate
874 (p_inv_rec => l_inv_rec,
875 p_book_type_code => px_asset_hdr_rec.book_type_code,
876 p_set_of_books_id => l_sob_tbl(l_sob_index),
877 px_exchange_date => l_exchange_date,
878 px_inv_rate_rec => l_inv_rate_rec,
879 p_log_level_rec => p_log_level_rec) then
880 raise error_found;
881 end if;
882
883 end if; -- l_mrc_rate_found_count
884
885
886 if (p_log_level_rec.statement_level) then
887 fa_debug_pkg.add(l_calling_fn,
888 'after',
889 'rate found logic', p_log_level_rec => p_log_level_rec);
890 end if;
891
892 -- use the rate located and multiply by the primary amounts
893 -- in order to get the reporting value - taking precision into account.
894 -- since the values in l_rec were already changed to the values to
895 -- be inserted, we must revert to using the values in array
896 --
897 -- for invoice deletes and reinstates use the values already in
898 -- record as they are the true deltas in terms of the adjustment
899 -- for deletes, we need to flip the sign here
900
901 -- R12 / SLA - we will no longer be using the rate, but the amount
902 -- directly in the same structure. logi will be to use that for
903 -- fa cost and then compare the remaining values and either
904 -- use that same amount or the exchange rate if different
905 --
906 -- however in case where amount is not provided, we will still
907 -- use the old method
908
909 if (p_log_level_rec.statement_level) then
910 fa_debug_pkg.add(l_calling_fn,
911 'checking',
912 'l_inv_rate_rec.cost',
913 p_log_level_rec);
914 end if;
915
916 -- the following code mirrors that for primary
917 -- but we only need to worry about the amounts for alc
918 -- validation has already been done and rest of inv_rec values
919 -- can equal the primary values
920
921 if (px_inv_trans_rec.transaction_type <> 'MASS ADDITION' and
922 px_inv_trans_rec.transaction_type <> 'INVOICE ADDITION' and
923 px_inv_tbl(l_row_count + 1).source_line_id is not null) then
924
925 if (p_log_level_rec.statement_level) then
926 fa_debug_pkg.add(l_calling_fn,
927 'in lgoic for existing line',
928 '',
929 p_log_level_rec);
930 end if;
931
932
933 select fixed_assets_cost,
934 payables_cost
935 into l_current_fa_cost,
936 l_current_pa_cost
937 from fa_mc_asset_invoices
938 where source_line_id = l_current_source_line_id
939 and set_of_books_id = l_sob_tbl(l_sob_index);
940
941
942 if (px_inv_trans_rec.transaction_type = 'INVOICE DELETE') then
943 l_inv_rec.Fixed_Assets_Cost := -l_current_fa_cost;
944 l_inv_rec.Payables_Cost := -l_current_pa_cost;
945 if (p_asset_type_rec.asset_type = 'CIP') then
946 if (nvl(l_inv_rec.depreciate_in_group_flag, 'N') = 'Y') then
947 l_inv_rec.Cip_Cost := -l_current_fa_cost;
948 else
949 l_inv_rec.Cip_Cost := 0;
950 end if;
951 end if;
952 elsif (px_inv_trans_rec.transaction_type = 'INVOICE REINSTATE') then
953 l_inv_rec.Fixed_Assets_Cost := l_current_fa_cost;
954 l_inv_rec.Payables_Cost := l_current_pa_cost;
955 if (p_asset_type_rec.asset_type = 'CIP') then
956 if (nvl(l_inv_rec.depreciate_in_group_flag, 'N') = 'Y') then
957 l_inv_rec.Cip_Cost := l_current_fa_cost;
958 else
959 l_inv_rec.Cip_Cost := 0;
960 end if;
961 end if;
962 elsif (px_inv_trans_rec.transaction_type = 'INVOICE DEP') then
963 l_inv_rec.Cip_Cost := -l_current_fa_cost;
964 l_inv_rec.Fixed_Assets_Cost := 0;
965 l_inv_rec.Payables_Cost := 0;
966 elsif (px_inv_trans_rec.transaction_type = 'INVOICE NO DEP') then
967 l_inv_rec.Cip_Cost := l_current_fa_cost;
968 l_inv_rec.Fixed_Assets_Cost := 0;
969 l_inv_rec.Payables_Cost := 0;
970 else
971 -- on existing lines, we currently do not allow
972 -- a different exchange rate to be used that the original
973 -- thus we can ignore the amount and go directly to the rate logic
974
975 -- use the rate located and multiply by the primary amounts
976 -- in order to get the reporting value - taking precision into account.
977 -- since the values in l_rec were already changed to the values to
978 -- be inserted, we must revert to using the values in array
979 --
980 -- for invoice deletes and reinstates use the values already in
981 -- record as they are the true deltas in terms of the adjustment
982 -- for deletes, we need to flip the sign here
983
984 l_inv_rec.fixed_assets_cost := nvl(px_inv_tbl(l_row_count + 1).fixed_assets_cost, 0) *
985 l_inv_rate_rec.exchange_rate;
986
987 -- bug 2258936 we do not want to set these values to 0, but leave null
988 l_inv_rec.payables_cost := px_inv_tbl(l_row_count + 1).payables_cost *
989 l_inv_rate_rec.exchange_rate;
990 l_inv_rec.unrevalued_cost := px_inv_tbl(l_row_count + 1).unrevalued_cost *
991 l_inv_rate_rec.exchange_rate;
992
993 -- adding for new group requirements
994 l_inv_rec.cip_cost := px_inv_tbl(l_row_count + 1).cip_cost *
995 l_inv_rate_rec.exchange_rate;
996
997 /* l_inv_rec.salvage_value := nvl(px_inv_tbl(l_row_count
998 * + 1).salvage_value, 0) *
999 * l_inv_rate_rec.exchange_rate;
1000 * */
1001
1002
1003 end if; -- invoice transaction type
1004
1005 /* may not be needed
1006 * elsif () then
1007 * -- special ALC for treatment for destinat8ion side of invoice transfers
1008 * -- still need to insure we use the correct exchange rate here
1009 *
1010 */
1011
1012 else -- new line scenario (add/massadd)
1013
1014 if (p_log_level_rec.statement_level) then
1015 fa_debug_pkg.add(l_calling_fn,
1016 'in logic for new line',
1017 '',
1018 p_log_level_rec);
1019 end if;
1020
1021
1022 if (l_mrc_rate_found_count = 1 and
1023 px_inv_tbl(l_row_count + 1).inv_rate_tbl(l_mrc_rate_index).cost is not null) then
1024
1025 -- now set the rate for subsequent use
1026 -- accounting for possibility that primary is 0/null
1027
1028 if (px_inv_tbl(l_row_count + 1).fixed_assets_cost is not null and
1029 px_inv_tbl(l_row_count + 1).fixed_assets_cost <> 0) then
1030 l_inv_rate_rec.exchange_rate := l_inv_rate_rec.cost /
1031 px_inv_tbl(l_row_count + 1).fixed_assets_cost;
1032 l_inv_rec.fixed_assets_cost := px_inv_tbl(l_row_count + 1).inv_rate_tbl(l_mrc_rate_index).cost;
1033 else
1034 l_inv_rate_rec.exchange_rate := nvl(l_inv_rate_rec.exchange_rate, 0);
1035 end if;
1036
1037 else -- old rate based method
1038
1039 if (p_log_level_rec.statement_level) then
1040 fa_debug_pkg.add(l_calling_fn,
1041 'entering',
1042 'null ALC amount logic for cost',
1043 p_log_level_rec);
1044 end if;
1045
1046 l_inv_rec.fixed_assets_cost := nvl(px_inv_tbl(l_row_count + 1).fixed_assets_cost, 0) *
1047 l_inv_rate_rec.exchange_rate;
1048 end if;
1049
1050 -- BUG# 2314466
1051 -- do not use nvl here as we want to insert null intentionally
1052 -- and the null value is needed to correctly derive original cost later
1053 if (px_inv_tbl(l_row_count + 1).payables_cost = px_inv_tbl(l_row_count + 1).fixed_assets_cost) then
1054 l_inv_rec.payables_cost := l_inv_rec.fixed_assets_cost;
1055 else
1056 -- bug 2258936 we do not want to set these values to 0, but leave null
1057 l_inv_rec.payables_cost := px_inv_tbl(l_row_count + 1).payables_cost *
1058 l_inv_rate_rec.exchange_rate;
1059 end if;
1060
1061 if (px_inv_tbl(l_row_count + 1).unrevalued_cost = px_inv_tbl(l_row_count + 1).fixed_assets_cost) then
1062 l_inv_rec.unrevalued_cost := l_inv_rec.fixed_assets_cost;
1063 else
1064 l_inv_rec.unrevalued_cost := px_inv_tbl(l_row_count + 1).unrevalued_cost *
1065 l_inv_rate_rec.exchange_rate;
1066 end if;
1067
1068 /* l_inv_rec.salvage_value := nvl(px_inv_tbl(l_row_count +
1069 * 1).salvage_value, 0) *
1070 * l_inv_rate_rec.exchange_rate;
1071 */
1072
1073
1074 if (p_asset_type_rec.asset_type = 'CIP') then
1075 if (nvl(l_inv_rec.depreciate_in_group_flag, 'N') = 'Y')
1076 then
1077 l_inv_rec.Cip_Cost := 0;
1078 else
1079 l_inv_rec.Cip_Cost := l_inv_rec.fixed_assets_cost;
1080 end if;
1081 end if;
1082
1083 if (px_asset_hdr_rec.period_of_addition = 'Y' and
1084 px_inv_trans_rec.transaction_type in ('MASS ADDITION',
1085 'INVOICE ADDITION') and
1086 p_asset_type_rec.asset_type = 'CAPITALIZED') then
1087
1088 l_inv_rec.ytd_deprn := nvl(px_inv_tbl(l_row_count + 1).ytd_deprn, 0) *
1089 l_inv_rate_rec.exchange_rate;
1090 l_inv_rec.deprn_reserve := nvl(px_inv_tbl(l_row_count + 1).deprn_reserve, 0) *
1091 l_inv_rate_rec.exchange_rate;
1092 l_inv_rec.bonus_ytd_deprn := nvl(px_inv_tbl(l_row_count + 1).bonus_ytd_deprn, 0) *
1093 l_inv_rate_rec.exchange_rate;
1094 l_inv_rec.bonus_deprn_reserve := nvl(px_inv_tbl(l_row_count + 1).bonus_deprn_reserve, 0) *
1095 l_inv_rate_rec.exchange_rate;
1096 l_inv_rec.reval_ytd_deprn := nvl(px_inv_tbl(l_row_count + 1).reval_ytd_deprn, 0) *
1097 l_inv_rate_rec.exchange_rate;
1098 l_inv_rec.reval_deprn_reserve := nvl(px_inv_tbl(l_row_count + 1).reval_deprn_reserve, 0) *
1099 l_inv_rate_rec.exchange_rate;
1100 l_inv_rec.reval_amortization_basis := nvl(px_inv_tbl(l_row_count + 1).reval_amortization_basis, 0) *
1101 l_inv_rate_rec.exchange_rate;
1102 end if;
1103
1104 end if; -- end existing / new line logic
1105
1106 if (p_log_level_rec.statement_level) then
1107 fa_debug_pkg.add(l_calling_fn,
1108 'calculated delta ALC cost',
1109 l_inv_rec.fixed_assets_cost,
1110 p_log_level_rec);
1111 end if;
1112
1113
1114
1115 -- retrieve the mrc fin struct row from the table of structs
1116 -- note that the order should match the sob_id cursor since
1117 -- that was used to populate the above. This will also be
1118 -- used for deprn table
1119
1120 l_mrc_fin_book_rec := 0;
1121 for i in 1..x_asset_fin_mrc_tbl_new.count loop
1122
1123 if ((x_asset_fin_mrc_tbl_new(i).set_of_books_id = l_sob_tbl(l_sob_index))) then
1124 l_mrc_fin_book_rec := i;
1125 end if;
1126
1127 end loop;
1128
1129 if (l_mrc_fin_book_rec = 0) then
1130 fa_srvr_msg.add_message(
1131 calling_fn => l_calling_fn,
1132 name => '***FAILED LOOKUP FININFO***',
1133 p_log_level_rec => p_log_level_rec);
1134 raise error_found;
1135 end if;
1136
1137 -- call calc_inv for amounts
1138 if not inv_calc_info
1139 (p_asset_hdr_rec => l_asset_hdr_rec,
1140 p_asset_type_rec => p_asset_type_rec,
1141 p_inv_trans_rec => px_inv_trans_rec,
1142 px_inv_rec => l_inv_rec,
1143 px_asset_fin_rec_new => x_asset_fin_mrc_tbl_new(l_mrc_fin_book_rec),
1144 px_asset_deprn_rec_new => x_asset_deprn_mrc_tbl_new(l_mrc_fin_book_rec),
1145 p_log_level_rec => p_log_level_rec
1146 ) then
1147 raise error_found;
1148 end if;
1149
1150 -- BUG# 2622722
1151 -- do this for all lines for addition and transfer transactions,
1152 -- not just those from mass additions. note that non-xfr adjustment
1153 -- will use the flexbuilt ccids for now. section has been moved up so
1154 -- tht the deltas are used, not the final amounts
1155
1156 if ((px_inv_trans_rec.transaction_type = 'MASS ADDITION' or
1157 px_inv_trans_rec.transaction_type = 'INVOICE TRANSFER' or
1158 px_inv_trans_rec.transaction_type = 'INVOICE ADDITION') and
1159 l_inv_rec.payables_cost <> 0 and
1160 (G_release <> 11 or
1161 l_inv_rec.payables_code_combination_id is not null)) then
1162 l_payables_cost_mrc_count := l_payables_cost_mrc_tbl.count + 1;
1163 l_payables_cost_mrc_tbl(l_payables_cost_mrc_count).set_of_books_id :=
1164 l_sob_tbl(l_sob_index);
1165 l_payables_cost_mrc_tbl(l_payables_cost_mrc_count).payables_cost :=
1166 l_inv_rec.payables_cost;
1167 l_payables_cost_mrc_tbl(l_payables_cost_mrc_count).payables_code_combination_id :=
1168 l_inv_rec.payables_code_combination_id;
1169 l_payables_cost_mrc_tbl(l_payables_cost_mrc_count).source_dest_code :=
1170 l_inv_rec.source_dest_code;
1171 l_payables_cost_mrc_tbl(l_payables_cost_mrc_count).source_line_id :=
1172 l_inv_rec.source_line_id;
1173
1174 end if;
1175
1176
1177 -- now for invoice transfers and adjustments, get the current values of the invoice
1178 -- that was just terminated and then add the delta. This will be the resulting cost
1179 -- of the new line. For invoice transfers, if full cost was transfered, we will
1180 -- be entering this logic for the source asset. Delta is the net effect, neg or pos.
1181 -- SRC line ret should have same behavior with inv transfer.
1182 --
1183 -- for source line deletes and reinstatements, no need to rederive this
1184 -- as the value set above using the rate is fine however we do need to
1185 -- flip the sign back for deletes
1186
1187 if (px_inv_trans_rec.transaction_type = 'INVOICE ADJUSTMENT' or
1188 (px_inv_trans_rec.transaction_type in ('INVOICE TRANSFER',
1189 'RETIREMENT',
1190 'REINSTATEMENT') and
1191 l_current_source_line_id is not null)) then
1192
1193 -- special case - for invoice transfer (source)
1194 -- load the delta amount back into the rate array
1195 -- which will be used by the destination asset
1196 if (px_inv_trans_rec.transaction_type = 'INVOICE TRANSFER') then
1197 px_inv_tbl(l_row_count + 1).inv_rate_tbl(l_mrc_rate_index).cost := -l_inv_rec.fixed_assets_cost;
1198 end if;
1199
1200 l_inv_rec.fixed_assets_cost := l_current_fa_cost + l_inv_rec.fixed_assets_cost;
1201 l_inv_rec.payables_cost := l_current_pa_cost + l_inv_rec.payables_cost;
1202
1203 end if;
1204
1205
1206 if (l_create_new_row = 'YES') then
1207
1208 if not process_invoice
1209 (px_trans_rec => px_trans_rec,
1210 px_asset_hdr_rec => l_asset_hdr_rec,
1211 p_inv_trans_rec => px_inv_trans_rec,
1212 px_inv_rec => l_inv_rec, -- current row reporting
1213 p_inv_rate_rec => l_inv_rate_rec,
1214 p_mrc_sob_type_code => 'R',
1215 p_log_level_rec => p_log_level_rec
1216 ) then raise error_found;
1217 end if;
1218 end if;
1219
1220
1221 -- store the total payables cost cleared per book
1222
1223
1224 end loop; -- end mrc sob loop
1225
1226 -- call the cache to reset the sob_id for the primary book
1227 if NOT fa_cache_pkg.fazcbcs(X_book => px_asset_hdr_rec.book_type_code,
1228 X_set_of_books_id => px_asset_hdr_rec.set_of_books_id,
1229 p_log_level_rec => p_log_level_rec) then
1230 raise error_found;
1231 end if;
1232
1233 end if; -- end mrc enabled book
1234
1235 end if; -- end create new row is yes
1236
1237 l_row_count := l_row_count + 1;
1238
1239 end loop; -- end invoice loop
1240
1241 -- call the post clearing code to clear all payables cost
1242 -- R12 conditional handling
1243 if ((px_inv_trans_rec.transaction_type = 'MASS ADDITION' or
1244 px_inv_trans_rec.transaction_type = 'INVOICE TRANSFER' or
1245 px_inv_trans_rec.transaction_type = 'INVOICE ADDITION') and
1246 ((G_release <> 11 and
1247 p_calling_fn <> 'fa_addition_pub.do_addition') or
1248 px_asset_hdr_rec.period_of_addition <> 'Y')) then
1249 if not post_clearing
1250 (p_trans_rec => px_trans_rec,
1251 p_asset_hdr_rec => px_asset_hdr_rec,
1252 p_asset_desc_rec => p_asset_desc_rec,
1253 p_asset_type_rec => p_asset_type_rec,
1254 p_asset_cat_rec => p_asset_cat_rec,
1255 p_inv_trans_rec => px_inv_trans_rec,
1256 p_payables_cost_tbl => l_payables_cost_tbl,
1257 p_payables_cost_mrc_tbl => l_payables_cost_mrc_tbl,
1258 p_log_level_rec => p_log_level_rec
1259 ) then raise error_found;
1260 end if;
1261 end if;
1262
1263 -- BUG# 2632955
1264 -- call the cache to reset the sob_id for the primary book
1265 if NOT fa_cache_pkg.fazcbcs(X_book => px_asset_hdr_rec.book_type_code,
1266 X_set_of_books_id => px_asset_hdr_rec.set_of_books_id,
1267 p_log_level_rec => p_log_level_rec) then
1268 raise error_found;
1269 end if;
1270
1271 return true;
1272
1273 EXCEPTION
1274 when error_found then
1275 fa_srvr_msg.add_message(calling_fn => 'fa_invoice_pvt.inv_engine', p_log_level_rec => p_log_level_rec);
1276
1277 return false;
1278
1279 when others then
1280 fa_srvr_msg.add_sql_error(calling_fn => 'fa_invoice_pvt.inv_engine', p_log_level_rec => p_log_level_rec);
1281
1282 return false;
1283
1284 END invoice_engine;
1285
1286 ------------------------------------------------------------------------------------------
1287
1288 FUNCTION inv_calc_info
1289 (p_asset_hdr_rec IN FA_API_TYPES.asset_hdr_rec_type,
1290 p_asset_type_rec IN FA_API_TYPES.asset_type_rec_type,
1291 p_inv_trans_rec IN FA_API_TYPES.inv_trans_rec_type,
1292 px_inv_rec IN OUT NOCOPY FA_API_TYPES.inv_rec_type,
1293 px_asset_fin_rec_new IN OUT NOCOPY FA_API_TYPES.asset_fin_rec_type,
1294 px_asset_deprn_rec_new IN OUT NOCOPY FA_API_TYPES.asset_deprn_rec_type
1295 , p_log_level_rec IN FA_API_TYPES.log_level_rec_type) RETURN BOOLEAN IS
1296
1297 error_found exception;
1298
1299 BEGIN
1300
1301 -- Bug:5907174
1302 if not FA_UTILS_PKG.faxrnd(px_inv_rec.fixed_assets_cost,
1303 p_asset_hdr_rec.book_type_code,
1304 p_asset_hdr_rec.set_of_books_id,
1305 p_log_level_rec => p_log_level_rec) then
1306 raise error_found;
1307 end if;
1308
1309 -- populate / increment the fin_rec using the delta
1310
1311 px_asset_fin_rec_new.cost := nvl(px_asset_fin_rec_new.cost, 0) +
1312 nvl(px_inv_rec.fixed_assets_cost, 0);
1313 px_asset_fin_rec_new.unrevalued_cost := nvl(px_asset_fin_rec_new.unrevalued_cost, 0) +
1314 nvl(px_inv_rec.unrevalued_cost,
1315 nvl(px_inv_rec.fixed_assets_cost, 0));
1316 px_asset_fin_rec_new.cip_cost := nvl(px_asset_fin_rec_new.cip_cost, 0) +
1317 nvl(px_inv_rec.cip_cost,0);
1318
1319
1320 if (p_log_level_rec.statement_level) then
1321 fa_debug_pkg.add('fa_inv_pvt.inv_calc_info',
1322 'px_asset_fin_rec_new.cost',
1323 px_asset_fin_rec_new.cost, p_log_level_rec => p_log_level_rec);
1324 fa_debug_pkg.add('fa_inv_pvt.inv_calc_info',
1325 'px_asset_fin_rec_new.cip_cost',
1326 px_asset_fin_rec_new.cip_cost, p_log_level_rec => p_log_level_rec);
1327 end if;
1328
1329
1330 /* px_asset_fin_rec_new.salvage_value := nvl(px_asset_fin_rec_new.salvage_value, 0) +
1331 nvl(px_inv_rec.salvage_value, 0);
1332 */
1333
1334 if (p_asset_hdr_rec.period_of_addition = 'Y') then
1335 -- Bug:5907174
1336 if not FA_UTILS_PKG.faxrnd(px_inv_rec.payables_cost,
1337 p_asset_hdr_rec.book_type_code,
1338 p_asset_hdr_rec.set_of_books_id,
1339 p_log_level_rec => p_log_level_rec) then
1340 raise error_found;
1341 end if;
1342
1343 px_asset_fin_rec_new.original_cost := nvl(px_asset_fin_rec_new.original_cost,0) +
1344 nvl(px_inv_rec.payables_cost,
1345 nvl(px_inv_rec.fixed_assets_cost, 0));
1346 end if;
1347
1348
1349 if not FA_UTILS_PKG.faxrnd(px_inv_rec.fixed_assets_cost,
1350 p_asset_hdr_rec.book_type_code,
1351 p_asset_hdr_rec.set_of_books_id,
1352 p_log_level_rec => p_log_level_rec) then
1353 raise error_found;
1354 end if;
1355
1356 if not FA_UTILS_PKG.faxrnd(px_inv_rec.payables_cost,
1357 p_asset_hdr_rec.book_type_code,
1358 p_asset_hdr_rec.set_of_books_id,
1359 p_log_level_rec => p_log_level_rec) then
1360 raise error_found;
1361 end if;
1362
1363 if not FA_UTILS_PKG.faxrnd(px_inv_rec.cip_cost,
1364 p_asset_hdr_rec.book_type_code,
1365 p_asset_hdr_rec.set_of_books_id,
1366 p_log_level_rec => p_log_level_rec) then
1367 raise error_found;
1368 end if;
1369
1370 /*
1371 if not FA_UTILS_PKG.faxrnd(px_inv_rec.payables_cost,
1372 p_asset_hdr_rec.book_type_code,
1373 p_asset_hdr_rec.set_of_books_id,
1374 p_log_level_rec => p_log_level_rec) then
1375 raise error_found;
1376 end if;
1377 */
1378
1379 if (p_asset_hdr_rec.period_of_addition = 'Y' and
1380 p_inv_trans_rec.transaction_type in ('MASS ADDITION', 'INVOICE ADDITION') and
1381 p_asset_type_rec.asset_type = 'CAPITALIZED') then
1382
1383 px_asset_deprn_rec_new.ytd_deprn := nvl(px_asset_deprn_rec_new.ytd_deprn, 0) +
1384 nvl(px_inv_rec.ytd_deprn, 0);
1385 px_asset_deprn_rec_new.deprn_reserve := nvl(px_asset_deprn_rec_new.deprn_reserve, 0) +
1386 nvl(px_inv_rec.deprn_reserve, 0);
1387 px_asset_deprn_rec_new.bonus_ytd_deprn := nvl(px_asset_deprn_rec_new.bonus_ytd_deprn, 0) +
1388 nvl(px_inv_rec.bonus_ytd_deprn, 0);
1389 px_asset_deprn_rec_new.bonus_deprn_reserve := nvl(px_asset_deprn_rec_new.bonus_deprn_reserve, 0) +
1390 nvl(px_inv_rec.bonus_deprn_reserve, 0);
1391 px_asset_deprn_rec_new.reval_ytd_deprn := nvl(px_asset_deprn_rec_new.reval_ytd_deprn, 0) +
1392 nvl(px_inv_rec.reval_ytd_deprn, 0);
1393 px_asset_deprn_rec_new.reval_deprn_reserve := nvl(px_asset_deprn_rec_new.reval_deprn_reserve, 0) +
1394 nvl(px_inv_rec.reval_deprn_reserve, 0);
1395 px_asset_deprn_rec_new.reval_amortization_basis := nvl(px_asset_deprn_rec_new.reval_amortization_basis, 0) +
1396 nvl(px_inv_rec.reval_amortization_basis, 0);
1397
1398 -- Bug 4243541 : Load to fin_rec and round the reval_amort_bais .
1399 px_asset_fin_rec_new.reval_amortization_basis := px_asset_deprn_rec_new.reval_amortization_basis;
1400
1401 if not FA_UTILS_PKG.faxrnd(px_inv_rec.ytd_deprn,
1402 p_asset_hdr_rec.book_type_code,
1403 p_asset_hdr_rec.set_of_books_id,
1404 p_log_level_rec => p_log_level_rec) then
1405 raise error_found;
1406 end if;
1407
1408 if not FA_UTILS_PKG.faxrnd(px_inv_rec.deprn_reserve ,
1409 p_asset_hdr_rec.book_type_code,
1410 p_asset_hdr_rec.set_of_books_id,
1411 p_log_level_rec => p_log_level_rec) then
1412 raise error_found;
1413 end if;
1414
1415 if not FA_UTILS_PKG.faxrnd(px_inv_rec.bonus_ytd_deprn ,
1416 p_asset_hdr_rec.book_type_code,
1417 p_asset_hdr_rec.set_of_books_id,
1418 p_log_level_rec => p_log_level_rec) then
1419 raise error_found;
1420 end if;
1421
1422 if not FA_UTILS_PKG.faxrnd(px_inv_rec.bonus_deprn_reserve ,
1423 p_asset_hdr_rec.book_type_code,
1424 p_asset_hdr_rec.set_of_books_id,
1425 p_log_level_rec => p_log_level_rec) then
1426 raise error_found;
1427 end if;
1428
1429 if not FA_UTILS_PKG.faxrnd(px_inv_rec.reval_ytd_deprn ,
1430 p_asset_hdr_rec.book_type_code,
1431 p_asset_hdr_rec.set_of_books_id,
1432 p_log_level_rec => p_log_level_rec) then
1433 raise error_found;
1434 end if;
1435
1436 if not FA_UTILS_PKG.faxrnd(px_inv_rec.reval_deprn_reserve ,
1437 p_asset_hdr_rec.book_type_code,
1438 p_asset_hdr_rec.set_of_books_id,
1439 p_log_level_rec => p_log_level_rec) then
1440 raise error_found;
1441 end if;
1442
1443 if not FA_UTILS_PKG.faxrnd(px_inv_rec.reval_amortization_basis,
1444 p_asset_hdr_rec.book_type_code,
1445 p_asset_hdr_rec.set_of_books_id,
1446 p_log_level_rec => p_log_level_rec) then
1447 raise error_found;
1448 end if;
1449
1450 -- MVK
1451 if not FA_UTILS_PKG.faxrnd(px_asset_fin_rec_new.reval_amortization_basis,
1452 p_asset_hdr_rec.book_type_code,
1453 p_asset_hdr_rec.set_of_books_id,
1454 p_log_level_rec => p_log_level_rec) then
1455 raise error_found;
1456 end if;
1457
1458 end if;
1459
1460 return true;
1461
1462 EXCEPTION
1463 when error_found then
1464 fa_srvr_msg.add_sql_error(calling_fn => 'fa_invoice_pvt.inv_calc_info', p_log_level_rec => p_log_level_rec);
1465 return false;
1466
1467 when others then
1468 fa_srvr_msg.add_sql_error(calling_fn => 'fa_invoice_pvt.inv_calc_info', p_log_level_rec => p_log_level_rec);
1469 return false;
1470
1471 END inv_calc_info;
1472
1473 ------------------------------------------------------------------------------------------
1474
1475 FUNCTION process_invoice
1476 (px_trans_rec IN OUT NOCOPY FA_API_TYPES.trans_rec_type,
1477 px_asset_hdr_rec IN OUT NOCOPY FA_API_TYPES.asset_hdr_rec_type,
1478 p_inv_trans_rec IN FA_API_TYPES.inv_trans_rec_type,
1479 px_inv_rec IN OUT NOCOPY FA_API_TYPES.inv_rec_type,
1480 p_inv_rate_rec IN FA_API_TYPES.inv_rate_rec_type,
1481 p_mrc_sob_type_code IN VARCHAR2
1482 , p_log_level_rec IN FA_API_TYPES.log_level_rec_type) RETURN BOOLEAN IS
1483
1484 l_rowid varchar2(30);
1485 l_reporting_flag varchar2(1);
1486 error_found EXCEPTION;
1487 /*
1488 * Following two variables are created for creating 0 cost source line
1489 * which may happned in source line retirement
1490 */
1491 l_date_ineffective date := null;
1492 l_invoice_transaction_id_out number := null;
1493
1494 BEGIN
1495
1496 /*
1497 * In case of full source line retirement, new line with 0 amount
1498 * still needs to be created, but it needs to have DATE_INEFFECTIVE and
1499 * INVOICE_TRANSACTION_ID_OUT populated to not to display on source line window.
1500 */
1501 if (p_inv_trans_rec.transaction_type = 'RETIREMENT' and
1502 px_inv_rec.fixed_assets_cost = 0) then
1503
1504 l_date_ineffective := px_trans_rec.who_info.last_update_date;
1505 l_invoice_transaction_id_out := p_inv_trans_rec.invoice_transaction_id;
1506
1507 end if;
1508
1509 FA_ASSET_INVOICES_PKG.Insert_Row
1510 (X_Rowid => l_rowid,
1511 X_Asset_Id => px_asset_hdr_rec.asset_id,
1512 X_Po_Vendor_Id => px_inv_rec.po_vendor_id,
1513 X_Asset_Invoice_Id => px_inv_rec.asset_invoice_id, -- mass_add_id
1514 X_Fixed_Assets_Cost => px_inv_rec.fixed_assets_cost,
1515 X_Date_Effective => px_trans_rec.who_info.last_update_date,
1516 X_Date_Ineffective => l_date_ineffective,
1517 X_Invoice_Transaction_Id_In => p_inv_trans_rec.invoice_transaction_id,
1518 X_Invoice_Transaction_Id_Out => l_invoice_transaction_id_out,
1519 X_Deleted_Flag => px_inv_rec.deleted_flag,
1520 X_Po_Number => px_inv_rec.po_number,
1521 X_Invoice_Number => px_inv_rec.invoice_number,
1522 X_Payables_Batch_Name => px_inv_rec.payables_batch_name,
1523 X_Payables_Code_Combination_Id => px_inv_rec.payables_code_combination_id,
1524 X_Feeder_System_Name => px_inv_rec.feeder_system_name,
1525 X_Create_Batch_Date => px_inv_rec.create_batch_date,
1526 X_Create_Batch_Id => px_inv_rec.create_batch_id,
1527 X_Invoice_Date => px_inv_rec.invoice_date,
1528 X_Payables_Cost => px_inv_rec.payables_cost,
1529 X_Post_Batch_Id => px_inv_rec.post_batch_id,
1530 X_Invoice_Id => px_inv_rec.invoice_id,
1531 X_Ap_Distribution_Line_Number => px_inv_rec.ap_distribution_line_number,
1532 X_Payables_Units => px_inv_rec.payables_units,
1533 X_Split_Merged_Code => px_inv_rec.split_merged_code,
1534 X_Description => px_inv_rec.description,
1535 X_Parent_Mass_Addition_Id => px_inv_rec.parent_mass_addition_id,
1536 X_Last_Update_Date => px_trans_rec.who_info.last_update_date,
1537 X_Last_Updated_By => px_trans_rec.who_info.last_updated_by,
1538 X_Created_By => px_trans_rec.who_info.created_by,
1539 X_Creation_Date => px_trans_rec.who_info.creation_date,
1540 X_Last_Update_Login => px_trans_rec.who_info.last_update_login,
1541 X_Attribute1 => px_inv_rec.ATTRIBUTE1,
1542 X_Attribute2 => px_inv_rec.ATTRIBUTE2,
1543 X_Attribute3 => px_inv_rec.ATTRIBUTE3,
1544 X_Attribute4 => px_inv_rec.ATTRIBUTE4,
1545 X_Attribute5 => px_inv_rec.ATTRIBUTE5,
1546 X_Attribute6 => px_inv_rec.ATTRIBUTE6,
1547 X_Attribute7 => px_inv_rec.ATTRIBUTE7,
1548 X_Attribute8 => px_inv_rec.ATTRIBUTE8,
1549 X_Attribute9 => px_inv_rec.ATTRIBUTE9,
1550 X_Attribute10 => px_inv_rec.ATTRIBUTE10,
1551 X_Attribute11 => px_inv_rec.ATTRIBUTE11,
1552 X_Attribute12 => px_inv_rec.ATTRIBUTE12,
1553 X_Attribute13 => px_inv_rec.ATTRIBUTE13,
1554 X_Attribute14 => px_inv_rec.ATTRIBUTE14,
1555 X_Attribute15 => px_inv_rec.ATTRIBUTE15,
1556 X_Attribute_Category_Code => px_inv_rec.ATTRIBUTE_CATEGORY_CODE,
1557 X_Unrevalued_Cost => px_inv_rec.unrevalued_cost,
1558 X_Merged_Code => px_inv_rec.merged_code,
1559 X_Split_Code => px_inv_rec.split_code,
1560 X_Merge_Parent_Mass_Add_Id => px_inv_rec.merge_parent_mass_additions_id,
1561 X_Split_Parent_Mass_Add_Id => px_inv_rec.split_parent_mass_additions_id,
1562 X_Project_Asset_Line_Id => px_inv_rec.project_asset_line_id,
1563 X_Project_Id => px_inv_rec.project_id,
1564 X_Task_Id => px_inv_rec.task_id,
1565 X_Material_Indicator_Flag => px_inv_rec.material_indicator_flag,
1566 X_source_line_id => px_inv_rec.source_line_id,
1567 X_prior_source_line_id => px_inv_rec.prior_source_line_id,
1568 X_depreciate_in_group_flag => px_inv_rec.depreciate_in_group_flag,
1569 -- added for R12
1570 X_invoice_distribution_id => px_inv_rec.invoice_distribution_id,
1571 X_invoice_line_number => px_inv_rec.invoice_line_number,
1572 X_po_distribution_id => px_inv_rec.po_distribution_id,
1573 X_exchange_rate => p_inv_rate_rec.exchange_rate,
1574 X_mrc_sob_type_code => p_mrc_sob_type_code,
1575 X_set_of_books_id => px_asset_hdr_rec.set_of_books_id,
1576 X_Calling_Fn => 'fa_invoice_api_pkg.process_invoice'
1577 , p_log_level_rec => p_log_level_rec);
1578
1579 return true;
1580
1581 EXCEPTION
1582 when error_found then
1583 fa_srvr_msg.add_message(calling_fn => 'fa_invoice_pvt.process_invoice', p_log_level_rec => p_log_level_rec);
1584 return false;
1585
1586 when others then
1587 fa_srvr_msg.add_sql_error(calling_fn => 'fa_invoice_pvt.process_invoice', p_log_level_rec => p_log_level_rec);
1588 return false;
1589
1590 END process_invoice;
1591
1592 --------------------------------------------------------------------------------
1593
1594 FUNCTION get_inv_rate
1595 (p_trans_rec IN FA_API_TYPES.trans_rec_type,
1596 p_asset_hdr_rec IN FA_API_TYPES.asset_hdr_rec_type,
1597 p_inv_trans_rec IN FA_API_TYPES.inv_trans_rec_type,
1598 px_inv_tbl IN OUT NOCOPY FA_API_TYPES.inv_tbl_type
1599 , p_log_level_rec IN FA_API_TYPES.log_level_rec_type) RETURN BOOLEAN IS
1600
1601 i number := 0;
1602 l_inv_rate_ctr number := 0;
1603
1604 cursor c_mc_invoices (p_source_line_id in number) is
1605 select mcai.set_of_books_id,
1606 mcai.exchange_rate,
1607 mcai.fixed_assets_cost
1608 from fa_mc_asset_invoices mcai,
1609 fa_mc_book_controls mcbk
1610 where mcai.source_line_id = p_source_line_id
1611 and mcai.set_of_books_id = mcbk.set_of_books_id
1612 and mcbk.book_type_code = p_asset_hdr_rec.book_type_code
1613 and mcbk.enabled_flag = 'Y';
1614
1615 l_calling_fn varchar2(35) := 'fa_inv_pvt.get_inv_rate';
1616
1617 error_found EXCEPTION;
1618
1619 BEGIN
1620
1621 for i in 1..px_inv_tbl.count loop
1622
1623 -- reset the starting value to one in each invoice
1624 l_inv_rate_ctr := 0;
1625
1626 -- only populate for existing source lines
1627
1628 if (px_inv_tbl(i).source_line_id is not null) then
1629
1630 if (p_log_level_rec.statement_level) then
1631 fa_debug_pkg.add('fa_inv_pvt.get_inv_rate',
1632 'source_line_id',
1633 px_inv_tbl(i).source_line_id);
1634 end if;
1635
1636 for c_rec in c_mc_invoices(px_inv_tbl(i).source_line_id) loop
1637
1638 if (p_log_level_rec.statement_level) then
1639 fa_debug_pkg.add('fa_inv_pvt.get_inv_rate',
1640 'set_of_books_id',
1641 c_rec.set_of_books_id, p_log_level_rec => p_log_level_rec);
1642 fa_debug_pkg.add('fa_inv_pvt.get_inv_rate',
1643 'exchange_rate',
1644 c_rec.exchange_rate, p_log_level_rec => p_log_level_rec);
1645 end if;
1646
1647 l_inv_rate_ctr := l_inv_rate_ctr + 1;
1648 px_inv_tbl(i).inv_rate_tbl(l_inv_rate_ctr).set_of_books_id := c_rec.set_of_books_id;
1649 px_inv_tbl(i).inv_rate_tbl(l_inv_rate_ctr).exchange_rate := c_rec.exchange_rate;
1650 px_inv_tbl(i).inv_rate_tbl(l_inv_rate_ctr).cost := c_rec.fixed_assets_cost;
1651
1652
1653 end loop;
1654 end if;
1655 end loop;
1656 return true;
1657
1658 EXCEPTION
1659 when error_found then
1660 fa_srvr_msg.add_message(calling_fn => 'fa_invoice_pvt.get_inv_rate', p_log_level_rec => p_log_level_rec);
1661 return false;
1662
1663 when others then
1664 fa_srvr_msg.add_sql_error(calling_fn => 'fa_invoice_pvt.get_inv_rate', p_log_level_rec => p_log_level_rec);
1665 return false;
1666
1667 END get_inv_rate;
1668
1669 --------------------------------------------------------------------------------
1670
1671 FUNCTION post_clearing
1672 (p_trans_rec IN FA_API_TYPES.trans_rec_type,
1673 p_asset_hdr_rec IN FA_API_TYPES.asset_hdr_rec_type,
1674 p_asset_desc_rec IN FA_API_TYPES.asset_desc_rec_type,
1675 p_asset_type_rec IN FA_API_TYPES.asset_type_rec_type,
1676 p_asset_cat_rec IN FA_API_TYPES.asset_cat_rec_type,
1677 p_inv_trans_rec IN FA_API_TYPES.inv_trans_rec_type,
1678 p_payables_cost_tbl IN payables_cost_tbl_type,
1679 p_payables_cost_mrc_tbl IN payables_cost_tbl_type
1680 , p_log_level_rec IN FA_API_TYPES.log_level_rec_type) RETURN BOOLEAN IS
1681
1682 TYPE num_tbl IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
1683
1684 i number;
1685 l_adj fa_adjust_type_pkg.fa_adj_row_struct;
1686 sob_processed_tbl num_tbl;
1687 l_sob_tbl FA_CACHE_PKG.fazcrsob_sob_tbl_type;
1688 l_sob_index number;
1689
1690 l_calling_fn varchar2(35) := 'fa_inv_pvt.post_clearing';
1691
1692 error_found exception;
1693
1694 BEGIN
1695
1696 l_adj.transaction_header_id := p_trans_rec.transaction_header_id;
1697 l_adj.asset_id := p_asset_hdr_rec.asset_id;
1698 l_adj.book_type_code := p_asset_hdr_rec.book_type_code;
1699 l_adj.period_counter_created := fa_cache_pkg.fazcbc_record.last_period_counter + 1;
1700 l_adj.period_counter_adjusted := fa_cache_pkg.fazcbc_record.last_period_counter + 1;
1701 l_adj.current_units := p_asset_desc_rec.current_units ;
1702 l_adj.selection_mode := FA_ADJUST_TYPE_PKG.FA_AJ_ACTIVE;
1703 l_adj.selection_thid := 0;
1704 l_adj.selection_retid := 0;
1705 l_adj.leveling_flag := TRUE;
1706 l_adj.last_update_date := p_trans_rec.who_info.last_update_date;
1707
1708 l_adj.flush_adj_flag := FALSE;
1709 l_adj.gen_ccid_flag := FALSE;
1710 l_adj.annualized_adjustment := 0;
1711 l_adj.distribution_id := 0;
1712
1713 l_adj.adjustment_type := 'COST CLEARING';
1714 l_adj.source_type_code := p_trans_rec.transaction_type_code;
1715
1716 if l_adj.source_type_code = 'CIP ADJUSTMENT' then
1717 l_adj.account_type := 'CIP_CLEARING_ACCT';
1718 else
1719 l_adj.account_type := 'ASSET_CLEARING_ACCT';
1720 end if;
1721
1722 -- post the primary rows
1723 for i in 1..p_payables_cost_tbl.count loop
1724
1725 -- SLA changes
1726 l_adj.asset_invoice_id := p_payables_cost_tbl(i).asset_invoice_id;
1727 l_adj.source_line_id := p_payables_cost_tbl(i).source_line_id;
1728 l_adj.source_dest_code := p_payables_cost_tbl(i).source_dest_code;
1729
1730 -- the nvl was added for R12 handling...
1731 l_adj.code_combination_id := nvl(p_payables_cost_tbl(i).payables_code_combination_id, 0);
1732 -- l_adj.asset_invoice_id := 0; -- could pass this in tbl
1733
1734 if p_payables_cost_tbl(i).payables_cost > 0 then
1735 l_adj.debit_credit_flag := 'CR';
1736 l_adj.adjustment_amount := p_payables_cost_tbl(i).payables_cost ;
1737 else
1738 l_adj.debit_credit_flag := 'DR';
1739 l_adj.adjustment_amount := -p_payables_cost_tbl(i).payables_cost ;
1740 end if;
1741
1742 l_adj.mrc_sob_type_code := 'P';
1743 l_adj.set_of_books_id := p_asset_hdr_rec.set_of_books_id;
1744
1745 if not FA_INS_ADJUST_PKG.faxinaj
1746 (l_adj,
1747 p_trans_rec.who_info.last_update_date,
1748 p_trans_rec.who_info.last_updated_by,
1749 p_trans_rec.who_info.last_update_login, p_log_level_rec => p_log_level_rec) then
1750 raise error_found;
1751 end if;
1752 end loop;
1753
1754 -- flush them
1755 l_adj.transaction_header_id := 0;
1756 l_adj.flush_adj_flag := TRUE;
1757 l_adj.leveling_flag := TRUE;
1758
1759 if not FA_INS_ADJUST_PKG.faxinaj
1760 (l_adj,
1761 p_trans_rec.who_info.last_update_date,
1762 p_trans_rec.who_info.last_updated_by,
1763 p_trans_rec.who_info.last_update_login, p_log_level_rec => p_log_level_rec) then
1764 raise error_found;
1765 end if;
1766
1767 -- need to reset this after each flush
1768 l_adj.transaction_header_id := p_trans_rec.transaction_header_id;
1769
1770
1771 -- call the sob cache to get the table of sob_ids
1772 if not FA_CACHE_PKG.fazcrsob
1773 (x_book_type_code => p_asset_hdr_rec.book_type_code,
1774 x_sob_tbl => l_sob_tbl, p_log_level_rec => p_log_level_rec) then
1775 raise error_found;
1776 end if;
1777
1778 -- post reporting rows one reporting book at a time
1779 FOR l_sob_index in 1..l_sob_tbl.count LOOP
1780
1781 for i in 1..p_payables_cost_mrc_tbl.count loop
1782
1783 if (p_payables_cost_mrc_tbl(i).set_of_books_id = l_sob_tbl(l_sob_index)) then
1784
1785 -- SLA changes
1786 l_adj.asset_invoice_id := p_payables_cost_mrc_tbl(i).asset_invoice_id;
1787 l_adj.source_line_id := p_payables_cost_mrc_tbl(i).source_line_id;
1788 l_adj.source_dest_code := p_payables_cost_mrc_tbl(i).source_dest_code;
1789
1790 -- nvl added for R12
1791 l_adj.code_combination_id := nvl(p_payables_cost_mrc_tbl(i).payables_code_combination_id, 0);
1792
1793 -- l_adj.asset_invoice_id := 0; -- could pass this in tbl
1794
1795 if p_payables_cost_mrc_tbl(i).payables_cost > 0 then
1796 l_adj.debit_credit_flag := 'CR';
1797 l_adj.adjustment_amount := p_payables_cost_mrc_tbl(i).payables_cost ;
1798 else
1799 l_adj.debit_credit_flag := 'DR';
1800 l_adj.adjustment_amount := -p_payables_cost_mrc_tbl(i).payables_cost ;
1801 end if;
1802
1803 l_adj.mrc_sob_type_code := 'R';
1804 l_adj.set_of_books_id := l_sob_tbl(l_sob_index);
1805
1806 if not FA_INS_ADJUST_PKG.faxinaj
1807 (l_adj,
1808 p_trans_rec.who_info.last_update_date,
1809 p_trans_rec.who_info.last_updated_by,
1810 p_trans_rec.who_info.last_update_login, p_log_level_rec => p_log_level_rec) then
1811 raise error_found;
1812 end if;
1813
1814 end if;
1815
1816 end loop; -- invoices
1817
1818 -- flush them
1819 l_adj.transaction_header_id := 0;
1820 l_adj.flush_adj_flag := TRUE;
1821 l_adj.leveling_flag := TRUE;
1822
1823 if not FA_INS_ADJUST_PKG.faxinaj
1824 (l_adj,
1825 p_trans_rec.who_info.last_update_date,
1826 p_trans_rec.who_info.last_updated_by,
1827 p_trans_rec.who_info.last_update_login, p_log_level_rec => p_log_level_rec) then
1828 raise error_found;
1829 end if;
1830
1831 -- need to reset this after each flush
1832 l_adj.transaction_header_id := p_trans_rec.transaction_header_id;
1833
1834 end loop; -- mrc
1835
1836 return true;
1837
1838 EXCEPTION
1839 when error_found then
1840 fa_srvr_msg.add_message(calling_fn => 'fa_invoice_pvt.post_clearing', p_log_level_rec => p_log_level_rec);
1841 return false;
1842
1843 when others then
1844 fa_srvr_msg.add_sql_error(calling_fn => 'fa_invoice_pvt.post_clearing', p_log_level_rec => p_log_level_rec);
1845 return false;
1846
1847 END;
1848
1849
1850 END FA_INVOICE_PVT;