[Home] [Help]
PACKAGE BODY: APPS.CSE_IPV_FA_PKG
Source
1 PACKAGE BODY cse_ipv_fa_pkg AS
2 /* $Header: CSEIPVFB.pls 120.20.12020000.3 2012/08/27 18:21:54 devijay ship $ */
3
4 l_debug varchar2(1) := NVL(fnd_profile.value('CSE_DEBUG_OPTION'),'N');
5
6 TYPE ap_ft_rec IS RECORD (
7 invoice_type varchar2(15),
8 chrg_dist_id number,
9 item_dist_id number,
10 base_amount number,
11 alloc_amount number,
12 accounting_date date,
13 inv_dist_ccid number);
14
15 TYPE ap_ft_tbl IS TABLE OF ap_ft_rec index by binary_integer;
16
17 PROCEDURE debug(
18 p_message IN varchar2)
19 IS
20 BEGIN
21 IF l_debug = 'Y' THEN
22 cse_debug_pub.add(p_message);
23 IF nvl(fnd_global.conc_request_id, -1) <> -1 THEN
24 fnd_file.put_line(fnd_file.log, p_message);
25 END IF;
26 END IF;
27 EXCEPTION
28 WHEN others THEN
29 null;
30 END debug;
31
32 PROCEDURE write_report( p_message IN VARCHAR2)
33 IS
34 BEGIN
35 IF fnd_global.conc_request_id is not null THEN
36 fnd_file.put_line(fnd_file.output,p_message);
37 END IF;
38 END write_report;
39
40 PROCEDURE report_output(
41 --p_acct_from_date IN DATE,
42 --p_acct_to_date IN DATE,
43 p_po_header_id IN number,
44 p_inventory_item_id IN number,
45 p_organization_id IN number,
46 p_include_zero_ipv IN VARCHAR2)
47
48 IS
49 Cursor c_success_count( cp_conc_request_id NUMBER ) IS
50 select count(1) success_count
51 from ap_invoice_distributions_all
52 where request_id = cp_conc_request_id ;
53
54 Cursor c_failed_count( cp_conc_request_id NUMBER ) IS
55 select count(1 ) failed_count
56 from csi_txn_errors
57 where source_group_ref_id=cp_conc_request_id ;
58
59 Cursor c_Exceptions( cp_conc_request_id NUMBER ) IS
60 select aia.invoice_num invoice_number,
61 aida.distribution_line_number distribution_line_number,
62 aida.quantity_invoiced quantity_invoiced,
63 aida.base_invoice_price_variance base_invoice_price_variance,
64 cte.error_text error_text
65 from ap_invoices_all aia,
66 ap_invoice_distributions_all aida,
67 csi_txn_errors cte
68 where aida.invoice_id = aia.invoice_id
69 and cte.source_id = aida.invoice_distribution_id
70 and cte.source_group_ref_id = cp_conc_request_id ;
71
72 l_message VARCHAR2(32767);
73 l_success_count NUMBER;
74 l_failed_count NUMBER;
75 l_total_count NUMBER;
76 l_Exceptions c_Exceptions%ROWTYPE;
77 l_conc_request_id NUMBER;
78
79 BEGIN
80 IF fnd_global.conc_request_id is not null THEN
81 l_conc_request_id := fnd_global.conc_request_id ;
82
83 OPEN c_success_count(l_conc_request_id) ;
84 FETCH c_success_count INTO l_success_count;
85 CLOSE c_success_count ;
86
87 OPEN c_failed_count(l_conc_request_id) ;
88 FETCH c_failed_count INTO l_failed_count;
89 CLOSE c_failed_count ;
90
91 l_total_count := l_success_count + l_failed_count ;
92
93 l_message := lpad('Report Date :',104,' ') ||to_char(sysdate);
94 fnd_file.put_line(fnd_file.output,l_message);
95 l_message := lpad('Invoice Adjustments to Assets',71,' ');
96 Write_report(l_message);
97 l_message := lpad('Summary',57,' ');
98 Write_report(l_message);
99 l_message := Null;
100 Write_report(l_message);
101 Write_report(l_message);
102 Write_report(l_message);
103 Write_report(l_message);
104 l_message := rpad(lpad('Number Of Transactions Successfully Processed',53,' '),88,' ')||l_success_count ;
105 Write_report(l_message);
106 l_message := rpad(lpad('Number Of Transactions Pending or Failed',48,' '),88,' ')|| l_failed_count;
107 Write_report(l_message);
108 l_message := Null;
109 Write_report(l_message);
110 l_message := lpad(rpad('-',21,'-'),90,' ');
111 Write_report(l_message);
112 l_message := rpad(lpad('Total Transactions Processed',63,' '),88,' ')|| l_total_count;
113 Write_report(l_message);
114 l_message := Null;
115 Write_report(l_message);
116 Write_report(l_message);
117 l_message := lpad('Report Date :',104,' ') ||to_char(sysdate);
118 Write_report(l_message);
119 l_message := lpad('Invoice Adjustments to Assets',71,' ');
120 Write_report(l_message);
121 l_message := lpad('Exception Report',57,' ');
122 Write_report(l_message);
123
124 l_message := lpad('Quantity Base Invoice',81,' ') ;
125 Write_report(l_message);
126
127 l_message := ' Invoice Number Distribution Line Number Invoiced Price Variance Error Text' ;
128 Write_report(l_message);
129 l_message := ' -------------- ------------------------ -------- ------------- -----------';
130 Write_report(l_message);
131 l_message := Null;
132
133 FOR l_Exceptions in c_Exceptions(l_conc_request_id )
134 LOOP
135 l_message := rpad( ' '||l_Exceptions.invoice_number,26,' ')||' ';
136 l_message := l_message ||rpad(l_Exceptions.distribution_line_number,30,' ')||' ';
137 l_message := l_message ||rpad(to_char(l_Exceptions.quantity_invoiced),8,' ')||' ';
138 l_message := l_message ||rpad(to_char(l_Exceptions.base_invoice_price_variance),15,' ')||' ';
139 l_message := l_message ||rpad(l_Exceptions.error_text,40,' ');
140 Write_report(l_message);
141 IF LENGTH(l_Exceptions.invoice_number) > 24 OR
142 LENGTH(l_Exceptions.distribution_line_number) > 30 OR
143 LENGTH(l_Exceptions.error_text) > 40 THEN
144
145 l_message := rpad( ' '||substr(l_Exceptions.invoice_number,26),26,' ')||' ';
146 l_message := l_message ||rpad(substr(l_Exceptions.distribution_line_number,30),30,' ')||' ';
147 l_message := l_message ||rpad(' ',8,' ')||' ';
148 l_message := l_message ||rpad(' ',15,' ')||' ';
149 l_message := l_message ||rpad(substr(l_Exceptions.error_text,40),40,' ');
150 Write_report(l_message);
151 END IF;
152 END LOOP;
153 l_message := Null;
154 Write_report(l_message);
155 Write_report(l_message);
156 l_message := ' Report Parameter: Value:';
157 Write_report(l_message);
158 l_message := ' PO Number : '||p_po_header_id;
159 Write_report(l_message);
160 l_message := ' Inventory Item : '||p_inventory_item_id;
161 Write_report(l_message);
162 l_message := ' Organization : '||p_organization_id;
163 Write_report(l_message);
164 l_message := ' Include Zero IPV : '||p_include_zero_ipv;
165 Write_report(l_message);
166 l_message := Null;
167 Write_report(l_message);
168
169 END IF;
170 EXCEPTION
171 WHEN others THEN
172 null;
173 END report_output;
174
175 PROCEDURE interface_to_fa(
176 p_invoice_rec IN invoice_rec,
177 x_processed_flag OUT NOCOPY varchar2,
178 x_return_status OUT NOCOPY varchar2)
179 IS
180
181 l_total_asset_units number := 0;
182 l_total_pending_units number := 0;
183 l_per_unit_ipv number;
184 l_units number;
185 l_asset_description varchar2(240);
186 l_asset_category_id number;
187 l_book_type_code varchar2(30);
188 l_date_placed_in_service date;
189 l_expense_ccid number;
190
191 l_allocated_ipv number;
192 l_link_found boolean := FALSE;
193
194 l_ind binary_integer := 0;
195 l_ma_proc_tbl ma_process_tbl;
196 l_mass_add_rec fa_mass_additions%rowtype;
197 l_asset_attrib_rec cse_datastructures_pub.asset_attrib_rec;
198 l_dflt_book_type_code varchar2(30);
199
200 l_error_message varchar2(2000);
201 l_return_status varchar2(1);
202 l_msg_data varchar2(2000);
203 l_msg_count number;
204 l_conc_request_id NUMBER;
205 l_txn_error_rec csi_datastructures_pub.transaction_error_rec;
206 l_transaction_error_Id NUMBER;
207
208 l_cumulative_ipv_amount NUMBER;
209 l_fma_index NUMBER;
210
211 -- get the po rcpt txns that are already processed to fa
212 CURSOR csi_txn_cur(p_po_dist_id IN number) IS
213 SELECT transaction_id,
214 inv_material_transaction_id,
215 transaction_quantity,
216 transaction_uom_code
217 FROM csi_transactions
218 WHERE source_dist_ref_id1 = p_po_dist_id
219 AND transaction_type_id IN (105, 112) -- po rcpt in to proj/inv
220 AND transaction_status_code = 'COMPLETE';
221
222 -- for po receipt transactions we always create/update destination instance
223 -- there is no concept of source instance
224 CURSOR asset_cur(p_csi_txn_id IN number, p_po_dist_id in number) IS
225 SELECT cia.fa_asset_id fa_id,
226 cia.fa_book_type_code fa_book_type_code,
227 'FA' fa_state
228 FROM csi_i_assets cia,
229 csi_item_instances_h ciih
230 WHERE ciih.transaction_id = p_csi_txn_id
231 AND cia.instance_id = ciih.instance_id
232 AND cia.fa_asset_id is not null
233 AND EXISTS (
234 SELECT 1 FROM fa_asset_invoices fai
235 WHERE fai.asset_id = cia.fa_asset_id
236 AND fai.feeder_system_name = cse_asset_util_pkg.g_fa_feeder_name
237 AND fai.po_distribution_id = p_po_dist_id)
238 UNION
239 SELECT cia.fa_mass_addition_id fa_id,
240 cia.fa_book_type_code fa_book_type_code,
241 'FMA' fa_state
242 FROM csi_i_assets cia,
243 csi_item_instances_h ciih
244 WHERE ciih.transaction_id = p_csi_txn_id
245 AND cia.instance_id = ciih.instance_id
246 AND cia.fa_asset_id is null
247 AND EXISTS (
248 SELECT 1 FROM fa_mass_additions fma
249 WHERE fma.mass_addition_id = cia.fa_mass_addition_id
250 AND fma.feeder_system_name = cse_asset_util_pkg.g_fa_feeder_name
251 AND fma.po_distribution_id = p_po_dist_id);
252
253 CURSOR fma_cur(p_csi_txn_id IN number, p_book_type_code in VARCHAR2) IS
254 SELECT fma.mass_addition_id,
255 fma.description,
256 fma.asset_category_id,
257 fma.book_type_code,
258 fma.date_placed_in_service,
259 fmd.units
260 FROM csi_item_instances_h ciih,
261 fa_mass_additions fma,
262 fa_massadd_distributions fmd
263 WHERE ciih.transaction_id = p_csi_txn_id
264 AND fma.reviewer_comments = to_char(ciih.instance_id)
265 AND fma.feeder_system_name = cse_asset_util_pkg.g_fa_feeder_name
266 AND fma.posting_status <> 'POSTED'
267 AND fma.book_type_code = p_book_type_code
268 AND fma.add_to_asset_id IS null
269 AND fma.split_merged_code = 'MP'
270 AND fmd.mass_addition_id = fma.mass_addition_id;
271
272 BEGIN
273
274 debug('inside interface_to_fa');
275 IF fnd_global.conc_request_id is not null THEN
276 l_conc_request_id := fnd_global.conc_request_id ;
277 END IF;
278 x_return_status := fnd_api.g_ret_sts_success;
279
280 FOR csi_txn_rec IN csi_txn_cur(p_invoice_rec.po_dist_id)
281 LOOP
282
283 debug(' csi_transaction_id : '||csi_txn_rec.transaction_id);
284
285 l_asset_attrib_rec.transaction_id := csi_txn_rec.transaction_id;
286 l_asset_attrib_rec.inventory_item_id := p_invoice_rec.inventory_item_id;
287 l_asset_attrib_rec.organization_id := p_invoice_rec.organization_id;
288
289 l_expense_ccid := cse_asset_util_pkg.deprn_expense_ccid(
290 p_asset_attrib_rec => l_asset_attrib_rec,
291 x_error_msg => l_error_message,
292 x_return_status => l_return_status);
293
294 IF l_return_status <> fnd_api.g_ret_sts_success THEN
295 RAISE fnd_api.g_exc_error;
296 END IF;
297
298 debug(' deprn_expense_ccid : '||l_expense_ccid);
299
300 SELECT fnd_profile.value('CSE_FA_BOOK_TYPE_CODE')
301 INTO l_dflt_book_type_code
302 FROM sys.dual;
303
304 FOR asset_rec in asset_cur(csi_txn_rec.transaction_id, p_invoice_rec.po_dist_id)
305 LOOP
306
307 l_link_found := TRUE;
308
309 debug(' asset_rec.fa_state : '||asset_rec.fa_state);
310
311 IF asset_rec.fa_state = 'FA' THEN
312
313 debug(' asset_rec.fa_id : '||asset_rec.fa_id);
314
315 -- cost adj case
316 SELECT current_units,
317 description,
318 asset_category_id
319 INTO l_units,
320 l_asset_description,
321 l_asset_category_id
322 FROM fa_additions
323 WHERE asset_id = asset_rec.fa_id;
324
325 debug(' asset_description : '||l_asset_description);
326
327 SELECT date_placed_in_service
328 INTO l_date_placed_in_service
329 FROM fa_books
330 WHERE asset_id = asset_rec.fa_id
331 AND book_type_code = asset_rec.fa_book_type_code
332 AND date_ineffective is NULL;
333
334 debug(' date_placed_in_service : '||l_date_placed_in_service);
335
336 l_total_asset_units := l_total_asset_units + l_units;
337
338 l_ind := l_ind + 1;
339 l_ma_proc_tbl(l_ind).asset_id := asset_rec.fa_id;
340 l_ma_proc_tbl(l_ind).mass_addition_id := null;
341 l_ma_proc_tbl(l_ind).book_type_code := asset_rec.fa_book_type_code;
342 l_ma_proc_tbl(l_ind).asset_category_id := l_asset_category_id;
343 l_ma_proc_tbl(l_ind).units := l_units;
344 l_ma_proc_tbl(l_ind).description := l_asset_description;
345 l_ma_proc_tbl(l_ind).date_placed_in_service := l_date_placed_in_service;
346 l_ma_proc_tbl(l_ind).expense_ccid := l_expense_ccid;
347
348 ELSIF asset_rec.fa_state = 'FMA' THEN
349
350 -- merge case pending in fma.
351 SELECT description,
352 asset_category_id,
353 book_type_code,
354 date_placed_in_service
355 INTO l_asset_description,
356 l_asset_category_id,
357 l_book_type_code,
358 l_date_placed_in_service
359 FROM fa_mass_additions
360 WHERE mass_addition_id = asset_rec.fa_id;
361
362 SELECT units
363 INTO l_units
364 FROM fa_massadd_distributions
365 WHERE mass_addition_id = asset_rec.fa_id;
366
367 l_total_pending_units := l_total_pending_units + l_units;
368
369 l_ind := l_ind + 1;
370 l_ma_proc_tbl(l_ind).asset_id := null;
371 l_ma_proc_tbl(l_ind).mass_addition_id := asset_rec.fa_id;
372 l_ma_proc_tbl(l_ind).book_type_code := l_book_type_code;
373 l_ma_proc_tbl(l_ind).asset_category_id := l_asset_category_id;
374 l_ma_proc_tbl(l_ind).units := l_units;
375 l_ma_proc_tbl(l_ind).description := l_asset_description;
376 l_ma_proc_tbl(l_ind).date_placed_in_service := l_date_placed_in_service;
377 l_ma_proc_tbl(l_ind).expense_ccid := l_expense_ccid;
378
379 END IF;
380
381 END LOOP; -- cia loop
382
383 -- this is to address the pending data in fma prior to r12
384 IF NOT(l_link_found) THEN
385 FOR fma_rec IN fma_cur(csi_txn_rec.transaction_id, l_dflt_book_type_code)
386 LOOP
387
388 l_link_found := TRUE;
389
390 l_total_pending_units := l_total_pending_units + l_units;
391
392 l_ind := l_ind + 1;
393 l_ma_proc_tbl(l_ind).asset_id := null;
394 l_ma_proc_tbl(l_ind).mass_addition_id := fma_rec.mass_addition_id;
395 l_ma_proc_tbl(l_ind).book_type_code := fma_rec.book_type_code;
396 l_ma_proc_tbl(l_ind).asset_category_id := fma_rec.asset_category_id;
397 l_ma_proc_tbl(l_ind).units := fma_rec.units;
398 l_ma_proc_tbl(l_ind).description := fma_rec.description;
399 l_ma_proc_tbl(l_ind).date_placed_in_service := fma_rec.date_placed_in_service;
400 l_ma_proc_tbl(l_ind).expense_ccid := l_expense_ccid;
401
402 END LOOP;
403 END IF;
404 END LOOP; -- csi_txn_loop; --moved it up for bug 13688378
405 debug(' l_ma_proc_tbl.count : '||l_ma_proc_tbl.count);
406
407 -- l_cumulative_ipv_amount will hold the intermittend total of the IPV added to the asset units
408 -- and will be compared with the p_invoice_rec.invoice_price_variance amount for the last asset unit
409 -- the difference between the p_invoice_rec.invoice_price_variance and l_cumulative_ipv_amount is
410 -- added to the l_per_unit_ipv of the last asset unit accounting for rounding difference
411 l_cumulative_ipv_amount := 0;
412 l_fma_index := 0;
413
414 IF l_ma_proc_tbl.COUNT > 0 THEN
415 FOR ma_ind IN l_ma_proc_tbl.FIRST .. l_ma_proc_tbl.LAST
416 LOOP
417
418 l_fma_index := l_fma_index + 1;
419 l_mass_add_rec := null;
420
421 IF l_ma_proc_tbl(ma_ind).asset_id IS NOT null THEN
422 l_mass_add_rec.add_to_asset_id := l_ma_proc_tbl(ma_ind).asset_id;
423 l_mass_add_rec.posting_status := 'POST';
424 l_mass_add_rec.queue_name := 'ADD TO ASSET';
425 l_per_unit_ipv := p_invoice_rec.invoice_price_variance/(l_total_asset_units+l_total_pending_units); -- Facebook IPV being interfaced twice for cases where there are records in FA and FMA
426 l_per_unit_ipv := trunc(l_per_unit_ipv, 2);
427 l_cumulative_ipv_amount := l_cumulative_ipv_amount + l_per_unit_ipv;
428 END IF;
429
430 IF l_ma_proc_tbl(ma_ind).mass_addition_id IS NOT null THEN
431 l_mass_add_rec.parent_mass_addition_id := l_ma_proc_tbl(ma_ind).mass_addition_id;
432 l_mass_add_rec.merge_parent_mass_additions_id := l_ma_proc_tbl(ma_ind).mass_addition_id;
433 l_mass_add_rec.queue_name := 'POST';
434 l_mass_add_rec.posting_status := 'MERGED';
435 l_mass_add_rec.split_merged_code := 'MC';
436 l_mass_add_rec.merged_code := 'MC';
437 l_per_unit_ipv := p_invoice_rec.invoice_price_variance/(l_total_asset_units+l_total_pending_units); -- Facebook IPV being interfaced twice for cases where there are records in FA and FMA
438 l_per_unit_ipv := trunc(l_per_unit_ipv, 2);
439 l_cumulative_ipv_amount := l_cumulative_ipv_amount + l_per_unit_ipv;
440 END IF;
441
442 -- last unit
443 IF l_fma_index = l_ma_proc_tbl.COUNT THEN
444 debug('Last Unit processing');
445 debug('Last Unit processing - l_cumulative_ipv_amount - ' || l_cumulative_ipv_amount);
446 debug('Last Unit processing - p_invoice_rec.invoice_price_variance - ' || p_invoice_rec.invoice_price_variance);
447 --l_cumulative_ipv_amount := l_cumulative_ipv_amount + l_per_unit_ipv;
448 IF l_cumulative_ipv_amount < p_invoice_rec.invoice_price_variance THEN
449 -- Adding the rounding difference
450 --l_per_unit_ipv := l_per_unit_ipv + (p_invoice_rec.invoice_price_variance - l_cumulative_ipv_amount);
451 l_per_unit_ipv := l_per_unit_ipv + (p_invoice_rec.invoice_price_variance - l_cumulative_ipv_amount);
452 debug('Modified l_per_unit_ipv - ' || l_per_unit_ipv);
453 END IF; -- l_cumulative_ipv_amount < p_invoice_rec.invoice_price_variance
454 -- Bug 13951891
455 IF (p_invoice_rec.invoice_price_variance < 0) AND (ABS(l_cumulative_ipv_amount) < ABS(p_invoice_rec.invoice_price_variance) ) THEN
456 -- Price variance is negative so compare ABS and add the difference
457 l_per_unit_ipv := ABS(l_per_unit_ipv) + ( ABS(p_invoice_rec.invoice_price_variance) - ABS(l_cumulative_ipv_amount));
458 l_per_unit_ipv := -1 * l_per_unit_ipv;
459 debug('Modified l_per_unit_ipv - ' || l_per_unit_ipv);
460 END IF; -- (p_invoice_rec.invoice_price_variance < 0) AND (ABS(l_cumulative_ipv_amount) < ABS(p_invoice_rec.invoice_price_variance) )
461 END IF; -- l_fma_index = l_ma_proc_tbl.COUNT
462
463 debug('l_fma_index - ' || l_fma_index );
464 debug('l_cumulative_ipv_amount - ' || l_cumulative_ipv_amount );
465 debug('l_per_unit_ipv : '||l_per_unit_ipv);
466
467 l_allocated_ipv := l_ma_proc_tbl(ma_ind).units * l_per_unit_ipv;
468
469 debug(' l_allocated_ipv : '||l_allocated_ipv);
470
471
472 l_mass_add_rec.book_type_code := l_ma_proc_tbl(ma_ind).book_type_code;
473 l_mass_add_rec.asset_category_id := l_ma_proc_tbl(ma_ind).asset_category_id;
474 l_mass_add_rec.description := l_ma_proc_tbl(ma_ind).description;
475 l_mass_add_rec.expense_code_combination_id := l_ma_proc_tbl(ma_ind).expense_ccid;
476 l_mass_add_rec.payables_code_combination_id := p_invoice_rec.payables_ccid;
477 l_mass_add_rec.ap_distribution_line_number := p_invoice_rec.invoice_dist_line_num;
478 l_mass_add_rec.po_number := p_invoice_rec.po_num;
479 l_mass_add_rec.po_vendor_id := p_invoice_rec.po_vendor_id;
480 l_mass_add_rec.invoice_number := p_invoice_rec.invoice_num;
481 l_mass_add_rec.invoice_id := p_invoice_rec.invoice_id;
482 --
483 l_mass_add_rec.payables_cost := l_allocated_ipv;
484 l_mass_add_rec.fixed_assets_cost := l_allocated_ipv;
485 l_mass_add_rec.fixed_assets_units := 1;
486 --
487 l_mass_add_rec.feeder_system_name := cse_asset_util_pkg.g_fa_feeder_name;
488 l_mass_add_rec.reviewer_comments := 'IPV' ;
489 l_mass_add_rec.asset_type := 'CAPITALIZED';
490 l_mass_add_rec.depreciate_flag := 'YES' ;
491 l_mass_add_rec.creation_date := sysdate;
492 l_mass_add_rec.last_update_date := sysdate;
493 l_mass_add_rec.created_by := fnd_global.user_id;
494 l_mass_add_rec.last_updated_by := fnd_global.user_id;
495 l_mass_add_rec.last_update_login := fnd_global.login_id;
496
497 cse_asset_util_pkg.insert_mass_add(
498 p_api_version => 1.0,
499 p_commit => fnd_api.g_false,
500 p_init_msg_list => fnd_api.g_true,
501 p_mass_add_rec => l_mass_add_rec,
502 x_return_status => l_return_status,
503 x_msg_count => l_msg_count,
504 x_msg_data => l_msg_data);
505
506 IF l_return_status <> fnd_api.g_ret_sts_success THEN
507 RAISE fnd_api.g_exc_error;
508 END IF;
509
510 x_processed_flag := 'Y';
511
512 END LOOP;
513 END IF;
514
515 --END LOOP; -- csi_txn_loop;--commented for bug 13688378- facebook
516
517
518 EXCEPTION
519 WHEN fnd_api.g_exc_error THEN
520 l_error_message := nvl(l_error_message, cse_util_pkg.dump_error_stack);
521 debug('Error : '||l_error_message);
522 x_return_status := fnd_api.g_ret_sts_error;
523 x_processed_flag := 'N';
524 l_txn_error_rec := cse_util_pkg.init_txn_error_rec;
525 l_txn_error_rec.error_text := l_error_message;
526 l_txn_error_rec.source_group_ref_id := l_conc_request_id;
527 l_txn_error_rec.source_type := 'AP_INVOICE_DISTRIBUTIONS_ALL';
528 l_txn_error_rec.source_id := p_invoice_rec.invoice_dist_id;
529 l_txn_error_rec.processed_flag := 'N';
530 csi_transactions_pvt.create_txn_error(
531 p_api_version => 1.0,
532 p_init_msg_list => fnd_api.g_true,
533 p_commit => fnd_api.g_false,
534 p_validation_level => fnd_api.g_valid_level_full,
535 p_txn_error_rec => l_txn_error_rec,
536 x_return_status => l_return_status,
537 x_msg_count => l_msg_count,
538 x_msg_data => l_msg_data,
539 x_transaction_error_id => l_transaction_error_id);
540 END interface_to_fa ;
541
542 PROCEDURE process_ipv_to_fa(
543 errbuf OUT NOCOPY VARCHAR2,
544 retcode OUT NOCOPY NUMBER,
545 p_po_header_id IN number,
546 p_inventory_item_id IN number,
547 p_organization_id IN number,
548 p_include_zero_ipv IN varchar2)
549 IS
550
551 l_ib_trackable_flag varchar2(1);
552 l_asset_creation_code varchar2(1);
553 l_conc_request_id number := fnd_global.conc_request_id;
554 l_processed_flag varchar2(1);
555 l_invoice_rec invoice_rec;
556 l_payables_ccid number;
557
558 l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
559
560 l_txn_error_rec csi_datastructures_pub.transaction_error_rec;
561 l_msg_count NUMBER;
562 l_msg_data VARCHAR2(2000);
563 l_transaction_error_Id NUMBER;
564 l_invoice_distribution_id NUMBER;
565 --Added for bug 13770784
566 l_hook_used NUMBER;
567 l_error_msg VARCHAR2(2000);
568 l_derived_po_distribution_id NUMBER;
569
570 CURSOR invoice_cur IS
571 SELECT aida.invoice_id,
572 aida.distribution_line_number,
573 aida.unit_price,
574 decode(aida.line_type_lookup_code,'NONREC_TAX', (SELECT aida1.quantity_invoiced
575 FROM ap_invoice_distributions_all aida1
576 WHERE aida1.invoice_distribution_id = aida.charge_applicable_to_dist_id),'TRV', (SELECT aida2.quantity_invoiced
577 FROM ap_invoice_distributions_all aida2
578 WHERE aida2.invoice_distribution_id = aida.charge_applicable_to_dist_id),'TIPV', (SELECT aida2.quantity_invoiced
579 FROM ap_invoice_distributions_all aida2
580 WHERE aida2.invoice_distribution_id = aida.charge_applicable_to_dist_id),'ERV', (SELECT aida2.quantity_invoiced
581 FROM ap_invoice_distributions_all aida2
582 WHERE aida2.invoice_distribution_id = aida.charge_applicable_to_dist_id),aila.quantity_invoiced) quantity_invoiced, --Modified for bug 12991094 and 13770784
583 aida.invoice_distribution_id,
584 aida.line_type_lookup_code invoice_distribution_type, --Added for bug 12991094
585 aida.po_distribution_id,
586 aia.invoice_num,
587 --nvl(aida.amount, 0) price_variance,
588 NVL(aida.base_amount,nvl(aida.amount, 0)) price_variance,
589 aila.inventory_item_id,
590 aila.org_id,
591 'N' as self_assessed_flag
592 FROM ap_invoice_distributions_all aida,
593 ap_invoice_lines_all aila,
594 ap_invoices_all aia
595 WHERE aida.line_type_lookup_code IN ('IPV','FREIGHT', 'NONREC_TAX', 'TRV','TIPV','ERV') --Modified for bug 12991094,13770784 and 13647752
596 AND aida.posted_flag = 'Y'
597 AND aida.assets_addition_flag IN ('U','I','N')
598 --AND NVL(aida.assets_tracking_flag,'x') = 'Y'
599 AND aida.po_distribution_id is not null
600 AND aila.invoice_id = aida.invoice_id
601 AND aila.line_number = aida.invoice_line_number
602 -- AND aila.inventory_item_id = nvl(p_inventory_item_id, aila.inventory_item_id)--Commented for bug 12991094
603 AND aia.invoice_id = aida.invoice_id
604 AND EXISTS ( --Added for bug 12991094
605 SELECT '1'
606 FROM ap_invoice_lines_all aila2
607 WHERE aila2.invoice_id = aila.invoice_id
608 AND aila2.inventory_item_id = nvl(p_inventory_item_id, aila2.inventory_item_id))
609 AND EXISTS (
610 SELECT '1'
611 FROM csi_transactions ct
612 WHERE ct.transaction_type_id in (105, 112)
613 AND ct.transaction_status_code = 'COMPLETE'
614 AND ct.source_dist_ref_id1 = aida.po_distribution_id)
615 AND EXISTS (
616 SELECT '1'
617 FROM po_distributions_all pod
618 WHERE pod.po_distribution_id = aida.po_distribution_id
619 AND pod.po_header_id = nvl(p_po_header_id,pod.po_header_id)
620 AND pod.destination_organization_id = nvl(p_organization_id, pod.destination_organization_id))
621 UNION -- Added for bug 13770784
622
623 SELECT aida.invoice_id,
624 aida.distribution_line_number,
625 aida.unit_price,
626 decode(aida.line_type_lookup_code,'FREIGHT', (SELECT aida1.quantity_invoiced
627 FROM ap_invoice_distributions_all aida1
628 WHERE aida1.invoice_distribution_id = aida.charge_applicable_to_dist_id),aila.quantity_invoiced) quantity_invoiced,
629 aida.invoice_distribution_id,
630 aida.line_type_lookup_code invoice_distribution_type,
631 decode(aida.line_type_lookup_code,'FREIGHT', (SELECT aida2.po_distribution_id
632 FROM ap_invoice_distributions_all aida2
633 WHERE aida2.invoice_distribution_id = aida.charge_applicable_to_dist_id),aida.po_distribution_id) po_distribution_id,
634 aia.invoice_num,
635 --nvl(aida.amount, 0) price_variance,
636 NVL(aida.base_amount,nvl(aida.amount, 0)) price_variance,
637 aila.inventory_item_id,
638 aila.org_id,
639 'N' as self_assessed_flag
640 FROM ap_invoice_distributions_all aida,
641 ap_invoice_lines_all aila,
642 ap_invoices_all aia
643 WHERE aida.line_type_lookup_code IN ('FREIGHT')
644 AND aida.posted_flag = 'Y'
645 AND aida.assets_addition_flag IN ('U','I','N')
646 AND aida.po_distribution_id is null
647 AND aila.invoice_id = aida.invoice_id
648 AND aila.line_number = aida.invoice_line_number
649 AND aia.invoice_id = aida.invoice_id
650 AND EXISTS (
651 SELECT '1'
652 FROM ap_invoice_lines_all aila2
653 WHERE aila2.invoice_id = aila.invoice_id
654 AND aila2.inventory_item_id = nvl(p_inventory_item_id, aila2.inventory_item_id))
655 AND EXISTS (
656 SELECT '1'
657 FROM ap_invoice_distributions_all aida4
658 WHERE aida4.invoice_distribution_id = aida.charge_applicable_to_dist_id
659 AND EXISTS (
660 SELECT '1'
661 FROM csi_transactions ct
662 WHERE ct.transaction_type_id in (105, 112)
663 AND ct.transaction_status_code = 'COMPLETE'
664 AND ct.source_dist_ref_id1 = aida4.po_distribution_id)
665 AND EXISTS (
666 SELECT '1'
667 FROM po_distributions_all pod
668 WHERE pod.po_distribution_id = aida4.po_distribution_id
669 AND pod.po_header_id = nvl(p_po_header_id,pod.po_header_id)
670 AND pod.destination_organization_id = nvl(p_organization_id, pod.destination_organization_id)))
671 -- Cursor to get all the NONREC_TAX line types which has po_distribution_id as NULL -- Added for bug 13770784
672 UNION
673 SELECT aida.invoice_id,
674 aida.distribution_line_number,
675 aida.unit_price,
676 decode(aida.line_type_lookup_code,'NONREC_TAX', (SELECT aida1.quantity_invoiced
677 FROM ap_invoice_distributions_all aida1
678 WHERE aida1.invoice_distribution_id = aida.charge_applicable_to_dist_id),aila.quantity_invoiced) quantity_invoiced, --Modified for bug 12991094
679 aida.invoice_distribution_id,
680 aida.line_type_lookup_code invoice_distribution_type, --Added for bug 12991094
681 aida.po_distribution_id,
682 aia.invoice_num,
683 --nvl(aida.amount, 0) price_variance,
684 NVL(aida.base_amount,nvl(aida.amount, 0)) price_variance,
685 aila.inventory_item_id,
686 aila.org_id,
687 'N' as self_assessed_flag
688 FROM ap_invoice_distributions_all aida,
689 ap_invoice_lines_all aila,
690 ap_invoices_all aia
691 WHERE aida.line_type_lookup_code IN ('NONREC_TAX')
692 --WHERE aida.line_type_lookup_code IN ('NONREC_TAX', 'TRV','TIPV','ERV')
693 AND aida.posted_flag = 'Y'
694 AND aida.assets_addition_flag IN ('U','I','N')
695 AND aida.po_distribution_id is null
696 AND aila.invoice_id = aida.invoice_id
697 AND aila.line_number = aida.invoice_line_number
698 AND aia.invoice_id = aida.invoice_id
699 AND EXISTS ( --Added for bug 12991094
700 SELECT '1'
701 FROM ap_invoice_lines_all aila2
702 WHERE aila2.invoice_id = aila.invoice_id
703 AND aila2.inventory_item_id = nvl(p_inventory_item_id, aila2.inventory_item_id))
704 /*
705 AND EXISTS (
706 SELECT '1'
707 FROM csi_transactions ct
708 WHERE ct.transaction_type_id in (105, 112)
709 AND ct.transaction_status_code = 'COMPLETE'
710 AND ct.source_dist_ref_id1 = aida.po_distribution_id)
711 AND EXISTS (
712 SELECT '1'
713 FROM po_distributions_all pod
714 WHERE pod.po_distribution_id = aida.po_distribution_id
715 AND pod.po_header_id = nvl(p_po_header_id,pod.po_header_id)
716 AND pod.destination_organization_id = nvl(p_organization_id, pod.destination_organization_id));*/
717 UNION
718 -- To get self-assessed tax 13917406
719 SELECT asstda.invoice_id, asstda.distribution_line_number, asstda.unit_price,
720 decode(asstda.line_type_lookup_code,'NONREC_TAX', (SELECT asstda.quantity_invoiced
721 FROM AP_SELF_ASSESSED_TAX_DIST_all asstda1
722 WHERE asstda1.invoice_distribution_id = asstda.charge_applicable_to_dist_id),aila.quantity_invoiced) quantity_invoiced,
723 asstda.invoice_distribution_id,
724 asstda.line_type_lookup_code invoice_distribution_type,
725 asstda.po_distribution_id po_distribution_id,
726 aia.invoice_num,
727 --nvl(asstda.amount, 0) price_variance,
728 NVL(asstda.base_amount,nvl(asstda.amount, 0)) price_variance,
729 aila.inventory_item_id,
730 aila.org_id,
731 asstda.SELF_ASSESSED_FLAG as self_assessed_flag
732 FROM AP_SELF_ASSESSED_TAX_DIST_all asstda,
733 --ap_invoice_distributions_all aida,
734 ap_invoice_lines_all aila,
735 ap_invoices_all aia
736 WHERE asstda.line_type_lookup_code IN ('NONREC_TAX')
737 AND asstda.posted_flag = 'Y'
738 AND asstda.assets_addition_flag IN ('U','I','N')
739 --AND asstda.po_distribution_id is null
740 AND aila.invoice_id = asstda.invoice_id
741 AND aila.line_number = asstda.invoice_line_number
742 AND aia.invoice_id = asstda.invoice_id
743 AND EXISTS
744 (SELECT '1'
745 FROM ap_invoice_lines_all aila2
746 WHERE aila2.invoice_id = aila.invoice_id
747 AND aila2.inventory_item_id = NVL(p_inventory_item_id, aila2.inventory_item_id)
748 );
749
750
751
752 PROCEDURE get_freight_and_tax(
753 p_item_dist_id IN number,
754 p_invoice_id IN number,
755 p_project_id IN number,
756 p_task_id IN number,
757 x_ft_amount OUT nocopy number,
758 px_ap_ft_tbl OUT nocopy ap_ft_tbl)
759 IS
760
761 l_ft_amount number := 0;
762 l_alloc_ft_amount number := 0;
763 l_ind binary_integer := 0;
764
765 CURSOR ft_cur IS
766 SELECT aida.line_type_lookup_code invoice_distribution_type,
767 aida.invoice_distribution_id,
768 aida.invoice_line_number,
769 aia.invoice_type_lookup_code invoice_type,
770 nvl(aida.amount,0) base_amount,
771 aida.accounting_date,
772 aida.dist_code_combination_id inv_dist_ccid
773 FROM ap_invoice_distributions_all aida,
774 ap_invoices_all aia
775 WHERE aida.invoice_id = p_invoice_id
776 AND aida.project_id = p_project_id
777 AND aida.task_id = p_task_id
778 AND aida.line_type_lookup_code IN ('FREIGHT', 'TAX', 'NONREC_TAX','TRV','TIPV','ERV')
779 AND aida.posted_flag = 'Y'
780 AND aida.pa_addition_flag = 'N'
781 AND nvl(aida.reversal_flag, 'N') <> 'Y'
782 AND nvl(aida.tax_recoverable_flag, 'N') = 'N'
783 AND aia.invoice_id = aida.invoice_id
784 AND exists (
785 SELECT 'x' FROM ap_chrg_allocations_all
786 WHERE item_dist_id = p_item_dist_id
787 AND charge_dist_id = aida.invoice_distribution_id);
788
789 FUNCTION allocated_amount (pf_item_dist_id IN number, pf_charge_dist_id IN number)
790 RETURN number
791 IS
792 l_alloc_amount number := 0;
793 CURSOR chrg_alloc_cur IS
794 SELECT allocated_amount
795 FROM ap_chrg_allocations_all
796 WHERE item_dist_id = pf_item_dist_id
797 AND charge_dist_id = pf_charge_dist_id;
798 BEGIN
799 FOR chrg_alloc_rec IN chrg_alloc_cur
800 LOOP
801 l_alloc_amount := l_alloc_amount + chrg_alloc_rec.allocated_amount;
802 END LOOP;
803 RETURN l_alloc_amount;
804 END allocated_amount;
805
806 BEGIN
807 debug('Inside API get_freight_and_tax');
808 FOR ft_rec IN ft_cur
809 LOOP
810 debug(' invoice_dist_id : '||ft_rec.invoice_distribution_id);
811 debug(' line_type : '||ft_rec.invoice_type);
812 debug(' base_amount : '||ft_rec.base_amount);
813
814 l_ind := px_ap_ft_tbl.COUNT + 1;
815 px_ap_ft_tbl(l_ind).invoice_type := ft_rec.invoice_type;
816 px_ap_ft_tbl(l_ind).chrg_dist_id := ft_rec.invoice_distribution_id;
817 px_ap_ft_tbl(l_ind).item_dist_id := p_item_dist_id;
818 px_ap_ft_tbl(l_ind).base_amount := ft_rec.base_amount;
819 px_ap_ft_tbl(l_ind).accounting_date := ft_rec.accounting_date;
820 px_ap_ft_tbl(l_ind).inv_dist_ccid := ft_rec.inv_dist_ccid;
821
822 l_alloc_ft_amount := allocated_amount(p_item_dist_id, ft_rec.invoice_distribution_id);
823 debug(' allocated_amount : '||l_alloc_ft_amount);
824 px_ap_ft_tbl(l_ind).alloc_amount := l_alloc_ft_amount;
825
826 l_ft_amount := l_ft_amount + l_alloc_ft_amount;
827 END LOOP;
828 x_ft_amount := l_ft_amount;
829 debug('TOTAL freight and tax amount : '||l_ft_amount);
830 END get_freight_and_tax;
831
832 BEGIN
833
834 cse_util_pkg.set_debug;
835
836 debug('Inside API cse_ipv_fa_pkg.process_ipv_to_fa');
837 debug(' param.inv_item_id : '||p_inventory_item_id);
838 debug(' param.inv_org_id : '||p_organization_id);
839 debug(' param.po_header_id : '||p_po_header_id);
840 debug(' param.include_zero_ipv : '||p_include_zero_ipv);
841
842 FOR invoice_rec IN invoice_cur
843 LOOP
844
845 debug('processing record # '||invoice_cur%rowcount);
846 mo_global.set_policy_context('S', invoice_rec.org_id);
847
848 l_processed_flag := 'N';
849
850 BEGIN
851
852 -- If the po_distribution_id is NULL call hook to dervice po_distribution_id -- Added for bug 13770784
853 IF NVL(invoice_rec.po_distribution_id, FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM THEN
854
855 debug('po_distribution_id is NULL. Calling cse_asset_client_ext_stub.derive_po_distribution_id');
856
857 cse_asset_client_ext_stub.derive_po_distribution_id(
858 p_invoice_distribution_id => invoice_rec.invoice_distribution_id,
859 p_line_type_lookup_code => invoice_rec.invoice_distribution_type,
860 x_derived_po_distribution_id => l_derived_po_distribution_id,
861 x_hook_used => l_hook_used,
862 x_error_msg => l_error_msg);
863
864 IF l_hook_used = 1 THEN
865 invoice_rec.po_distribution_id := l_derived_po_distribution_id;
866 END IF;
867 debug('cse_asset_client_ext_stub.derive_po_distribution_id - l_hook_used - ' || l_hook_used);
868 debug('invoice_rec.po_distribution_id - ' || invoice_rec.po_distribution_id);
869
870 END IF;
871
872 -- If po_distribution_id is not null proceed with processing
873 IF NVL(invoice_rec.po_distribution_id, FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM THEN
874
875 debug(' invoice_dist_id : '||invoice_rec.invoice_distribution_id);
876 debug(' invoice_dist_line_num : '||invoice_rec.distribution_line_number);
877 debug(' invoice_id : '||invoice_rec.invoice_id);
878 debug(' price_variance : '||invoice_rec.price_variance);
879 debug(' invoice_distribution_type : '||invoice_rec.invoice_distribution_type);
880 debug(' po_distribution_id : '||invoice_rec.po_distribution_id);
881
882 -- Bug 13951891
883 IF (NVL(p_include_zero_ipv, 'Y') = 'Y' OR
884 (NVL(p_include_zero_ipv, 'Y') = 'N' AND invoice_rec.price_variance <> 0)) THEN
885
886 l_invoice_distribution_id := invoice_rec.invoice_distribution_id;
887 l_invoice_rec.invoice_dist_id := invoice_rec.invoice_distribution_id;
888 l_invoice_rec.invoice_dist_line_num := invoice_rec.distribution_line_number;
889 l_invoice_rec.invoice_id := invoice_rec.invoice_id;
890 l_invoice_rec.invoice_num := invoice_rec.invoice_num;
891 l_invoice_rec.po_dist_id := invoice_rec.po_distribution_id;
892 l_invoice_rec.invoice_price_variance := invoice_rec.price_variance;
893 l_invoice_rec.unit_price := invoice_rec.unit_price;
894
895 -- Added for bug 5255658
896 IF NVL(invoice_rec.quantity_invoiced,0) =0 THEN
897 l_invoice_rec.quantity_invoiced := null;
898 ELSE
899 l_invoice_rec.quantity_invoiced := invoice_rec.quantity_invoiced;
900 END IF;
901
902 SELECT pol.item_id,
903 pod.destination_organization_id,
904 pod.variance_account_id,
905 poh.segment1,
906 poh.vendor_id
907 INTO l_invoice_rec.inventory_item_id,
908 l_invoice_rec.organization_id,
909 l_invoice_rec.payables_ccid,
910 l_invoice_rec.po_num,
911 l_invoice_rec.po_vendor_id
912 FROM po_distributions_all pod,
913 po_lines_all pol,
914 po_headers_all poh
915 WHERE pod.po_distribution_id = invoice_rec.po_distribution_id
916 AND pol.po_line_id = pod.po_line_id
917 AND poh.po_header_id = pol.po_header_id;
918
919 --Modified for bug 12991094
920 IF invoice_rec.invoice_distribution_type = 'IPV' THEN
921 l_payables_ccid := cse_asset_util_pkg.get_ap_sla_acct_for_dist_id(
922 p_invoice_distribution_id => invoice_rec.invoice_distribution_id,
923 p_invoice_id => invoice_rec.invoice_id,
924 p_invoice_dist_type => 'IPV',
925 p_self_assesed_flag => invoice_rec.self_assessed_flag);
926 ELSIF invoice_rec.invoice_distribution_type = 'FREIGHT' THEN
927 l_payables_ccid := cse_asset_util_pkg.get_ap_sla_acct_for_dist_id(
928 p_invoice_distribution_id => invoice_rec.invoice_distribution_id,
929 p_invoice_id => invoice_rec.invoice_id,
930 p_invoice_dist_type => 'FREIGHT',
931 p_self_assesed_flag => invoice_rec.self_assessed_flag);
932 ELSIF invoice_rec.invoice_distribution_type = 'REC_TAX' THEN
933 l_payables_ccid := cse_asset_util_pkg.get_ap_sla_acct_for_dist_id(
934 p_invoice_distribution_id => invoice_rec.invoice_distribution_id,
935 p_invoice_id => invoice_rec.invoice_id,
936 p_invoice_dist_type => 'RTAX',
937 p_self_assesed_flag => invoice_rec.self_assessed_flag);
938 ELSIF invoice_rec.invoice_distribution_type = 'NONREC_TAX' THEN
939 l_payables_ccid := cse_asset_util_pkg.get_ap_sla_acct_for_dist_id(
940 p_invoice_distribution_id => invoice_rec.invoice_distribution_id,
941 p_invoice_id => invoice_rec.invoice_id,
942 p_invoice_dist_type => 'NRTAX',
943 p_self_assesed_flag => invoice_rec.self_assessed_flag);
944 ELSIF invoice_rec.invoice_distribution_type = 'TRV' THEN --Added for bug 13647752
945 l_payables_ccid := cse_asset_util_pkg.get_ap_sla_acct_for_dist_id(
946 p_invoice_distribution_id => invoice_rec.invoice_distribution_id,
947 p_invoice_id => invoice_rec.invoice_id,
948 p_invoice_dist_type => 'TRV',
949 p_self_assesed_flag => invoice_rec.self_assessed_flag);
950 ELSIF invoice_rec.invoice_distribution_type = 'TIPV' THEN -- Added for bug 13770784
951 l_payables_ccid := cse_asset_util_pkg.get_ap_sla_acct_for_dist_id(
952 p_invoice_distribution_id => invoice_rec.invoice_distribution_id,
953 p_invoice_id => invoice_rec.invoice_id,
954 p_invoice_dist_type => 'TIPV',
955 p_self_assesed_flag => invoice_rec.self_assessed_flag);
956 ELSIF invoice_rec.invoice_distribution_type = 'ERV' THEN -- Added for bug 13770784
957 l_payables_ccid := cse_asset_util_pkg.get_ap_sla_acct_for_dist_id(
958 p_invoice_distribution_id => invoice_rec.invoice_distribution_id,
959 p_invoice_id => invoice_rec.invoice_id,
960 p_invoice_dist_type => 'EXCHANGE_RATE_VARIANCE',
961 p_self_assesed_flag => invoice_rec.self_assessed_flag);
962 END IF; --Modified for bug 12991094
963
964 debug(' price_variance_ccid : '||l_payables_ccid);
965
966 l_invoice_rec.payables_ccid := nvl(l_payables_ccid, l_invoice_rec.payables_ccid);
967
968 IF l_invoice_rec.inventory_item_id is not null
969 AND
970 l_invoice_rec.organization_id is not null
971 THEN
972
973 SELECT nvl(comms_nl_trackable_flag, 'N'),
974 nvl(asset_creation_code, '0')
975 INTO l_ib_trackable_flag,
976 l_asset_creation_code
977 FROM mtl_system_items
978 WHERE inventory_item_id = l_invoice_rec.inventory_item_id
979 AND organization_id = l_invoice_rec.organization_id;
980
981 IF l_ib_trackable_flag = 'Y' AND l_asset_creation_code in ('1', 'Y') THEN
982
983 debug(' po_distribution_id : '|| invoice_rec.po_distribution_id);
984 debug(' inventory_item_id : '||l_invoice_rec.inventory_item_id);
985 debug(' organization_id : '||l_invoice_rec.organization_id);
986
987 interface_to_fa(
988 p_invoice_rec => l_invoice_rec,
989 x_processed_flag => l_processed_flag,
990 x_return_status => l_return_status);
991
992 IF l_return_status <> fnd_api.g_ret_sts_success THEN
993 RAISE fnd_api.g_exc_error;
994 END IF;
995
996 END IF; -- item based, ib tracked and depreciable
997
998 END IF; -- pol.inventory_item_is is not null;
999
1000 ELSE
1001 -- Bug 13951891
1002 -- Zero IPV was 'N' and the price variance is 0
1003 -- In that case make the l_processed_flag to Y so that
1004 -- asset_addition_flag is updated propertly
1005 debug('Zero IPV - Skipping addition and updating assets_addition_flag to Y');
1006 l_processed_flag := 'Y';
1007
1008 END IF; -- NVL(p_include_zero_ipv, 'Y') = 'Y' || (NVL(p_include_zero_ipv, 'Y') = 'N' AND invoice_rec.price_variance > 0)
1009
1010 IF l_processed_flag = 'Y' AND NVL(invoice_rec.self_assessed_flag,'N') = 'N' THEN
1011 UPDATE ap_invoice_distributions_all
1012 SET assets_addition_flag = 'Y',
1013 request_id = l_conc_request_id
1014 WHERE invoice_distribution_id = invoice_rec.invoice_distribution_id
1015 AND assets_addition_flag <>'Y';
1016
1017 debug('invoice_distribution_id : '||invoice_rec.invoice_distribution_id);
1018 debug('Processed successfully. updating ap_invoice_distributions_all.assets_addition_flag = Y');
1019 END IF;
1020
1021 -- Updating ap_self_assessed_tax_dist_all table
1022 IF l_processed_flag = 'Y' AND NVL(invoice_rec.self_assessed_flag,'N') = 'Y' THEN
1023 UPDATE ap_self_assessed_tax_dist_all
1024 SET assets_addition_flag = 'Y',
1025 request_id = l_conc_request_id
1026 WHERE invoice_distribution_id = invoice_rec.invoice_distribution_id
1027 AND assets_addition_flag <>'Y';
1028
1029 debug('invoice_distribution_id : '||invoice_rec.invoice_distribution_id);
1030 debug('Processed successfully. updating ap_self_assessed_tax_dist_all.assets_addition_flag = Y');
1031 END IF;
1032
1033
1034 END IF; --IF NVL(invoice_rec.po_distribution_id, FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM THEN -- Added for bug 13770784
1035
1036 EXCEPTION
1037 WHEN fnd_api.g_exc_error THEN
1038 --null;
1039 l_txn_error_rec := cse_util_pkg.init_txn_error_rec;
1040 l_txn_error_rec.error_text := SQLERRM;
1041 l_txn_error_rec.source_group_ref_id := l_conc_request_id;
1042 l_txn_error_rec.source_type := 'AP_INVOICE_DISTRIBUTIONS_ALL';
1043 l_txn_error_rec.source_id := invoice_rec.invoice_distribution_id;
1044 l_txn_error_rec.processed_flag := 'N';
1045 csi_transactions_pvt.create_txn_error(
1046 p_api_version => 1.0,
1047 p_init_msg_list => fnd_api.g_true,
1048 p_commit => fnd_api.g_false,
1049 p_validation_level => fnd_api.g_valid_level_full,
1050 p_txn_error_rec => l_txn_error_rec,
1051 x_return_status => l_return_status,
1052 x_msg_count => l_msg_count,
1053 x_msg_data => l_msg_data,
1054 x_transaction_error_id => l_transaction_error_id);
1055 END;
1056
1057 END LOOP;
1058 --for successful
1059 Report_OutPut( p_po_header_id => p_po_header_id,
1060 p_inventory_item_id => p_inventory_item_id,
1061 p_organization_id => p_organization_id,
1062 p_include_zero_ipv => p_include_zero_ipv );
1063 EXCEPTION
1064 WHEN OTHERS THEN
1065 l_txn_error_rec := cse_util_pkg.init_txn_error_rec;
1066 l_txn_error_rec.error_text := SQLERRM;
1067 l_txn_error_rec.source_group_ref_id := l_conc_request_id;
1068 l_txn_error_rec.source_type := 'AP_INVOICE_DISTRIBUTIONS_ALL';
1069 l_txn_error_rec.source_id := l_invoice_distribution_id;
1070 l_txn_error_rec.processed_flag := 'N';
1071 csi_transactions_pvt.create_txn_error(
1072 p_api_version => 1.0,
1073 p_init_msg_list => fnd_api.g_true,
1074 p_commit => fnd_api.g_false,
1075 p_validation_level => fnd_api.g_valid_level_full,
1076 p_txn_error_rec => l_txn_error_rec,
1077 x_return_status => l_return_status,
1078 x_msg_count => l_msg_count,
1079 x_msg_data => l_msg_data,
1080 x_transaction_error_id => l_transaction_error_id);
1081 Report_OutPut( p_po_header_id => p_po_header_id,
1082 p_inventory_item_id => p_inventory_item_id,
1083 p_organization_id => p_organization_id,
1084 p_include_zero_ipv => p_include_zero_ipv );
1085
1086 END process_ipv_to_fa;
1087
1088 END cse_ipv_fa_pkg;