[Home] [Help]
PACKAGE BODY: APPS.CSE_IPV_FA_PKG
Source
1 PACKAGE BODY cse_ipv_fa_pkg AS
2 /* $Header: CSEIPVFB.pls 120.15.12000000.2 2007/07/06 12:58:36 dhdas 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 -- get the po rcpt txns that are already processed to fa
208 CURSOR csi_txn_cur(p_po_dist_id IN number) IS
209 SELECT transaction_id,
210 inv_material_transaction_id,
211 transaction_quantity,
212 transaction_uom_code
213 FROM csi_transactions
214 WHERE source_dist_ref_id1 = p_po_dist_id
215 AND transaction_type_id IN (105, 112) -- po rcpt in to proj/inv
216 AND transaction_status_code = 'COMPLETE';
217
218 -- for po receipt transactions we always create/update destination instance
219 -- there is no concept of source instance
220 CURSOR asset_cur(p_csi_txn_id IN number, p_po_dist_id in number) IS
221 SELECT cia.fa_asset_id fa_id,
222 cia.fa_book_type_code fa_book_type_code,
223 'FA' fa_state
224 FROM csi_i_assets cia,
225 csi_item_instances_h ciih
226 WHERE ciih.transaction_id = p_csi_txn_id
227 AND cia.instance_id = ciih.instance_id
228 AND cia.fa_asset_id is not null
229 AND EXISTS (
230 SELECT 1 FROM fa_asset_invoices fai
231 WHERE fai.asset_id = cia.fa_asset_id
232 AND fai.feeder_system_name = cse_asset_util_pkg.g_fa_feeder_name
233 AND fai.po_distribution_id = p_po_dist_id)
234 UNION
235 SELECT cia.fa_mass_addition_id fa_id,
236 cia.fa_book_type_code fa_book_type_code,
237 'FMA' fa_state
238 FROM csi_i_assets cia,
239 csi_item_instances_h ciih
240 WHERE ciih.transaction_id = p_csi_txn_id
241 AND cia.instance_id = ciih.instance_id
242 AND cia.fa_asset_id is null
243 AND EXISTS (
244 SELECT 1 FROM fa_mass_additions fma
245 WHERE fma.mass_addition_id = cia.fa_mass_addition_id
246 AND fma.feeder_system_name = cse_asset_util_pkg.g_fa_feeder_name
247 AND fma.po_distribution_id = p_po_dist_id);
248
249 CURSOR fma_cur(p_csi_txn_id IN number, p_book_type_code in VARCHAR2) IS
250 SELECT fma.mass_addition_id,
251 fma.description,
252 fma.asset_category_id,
253 fma.book_type_code,
254 fma.date_placed_in_service,
255 fmd.units
256 FROM csi_item_instances_h ciih,
257 fa_mass_additions fma,
258 fa_massadd_distributions fmd
259 WHERE ciih.transaction_id = p_csi_txn_id
260 AND fma.reviewer_comments = to_char(ciih.instance_id)
261 AND fma.feeder_system_name = cse_asset_util_pkg.g_fa_feeder_name
262 AND fma.posting_status <> 'POSTED'
263 AND fma.book_type_code = p_book_type_code
264 AND fma.add_to_asset_id IS null
265 AND fma.split_merged_code = 'MP'
266 AND fmd.mass_addition_id = fma.mass_addition_id;
267
268 BEGIN
269
270 debug('inside interface_to_fa');
271 IF fnd_global.conc_request_id is not null THEN
272 l_conc_request_id := fnd_global.conc_request_id ;
273 END IF;
274 x_return_status := fnd_api.g_ret_sts_success;
275
276 FOR csi_txn_rec IN csi_txn_cur(p_invoice_rec.po_dist_id)
277 LOOP
278
279 debug(' csi_transaction_id : '||csi_txn_rec.transaction_id);
280
281 l_asset_attrib_rec.transaction_id := csi_txn_rec.transaction_id;
282 l_asset_attrib_rec.inventory_item_id := p_invoice_rec.inventory_item_id;
283 l_asset_attrib_rec.organization_id := p_invoice_rec.organization_id;
284
285 l_expense_ccid := cse_asset_util_pkg.deprn_expense_ccid(
286 p_asset_attrib_rec => l_asset_attrib_rec,
287 x_error_msg => l_error_message,
288 x_return_status => l_return_status);
289
290 IF l_return_status <> fnd_api.g_ret_sts_success THEN
291 RAISE fnd_api.g_exc_error;
292 END IF;
293
294 debug(' deprn_expense_ccid : '||l_expense_ccid);
295
296 SELECT fnd_profile.value('CSE_FA_BOOK_TYPE_CODE')
297 INTO l_dflt_book_type_code
298 FROM sys.dual;
299
300 FOR asset_rec in asset_cur(csi_txn_rec.transaction_id, p_invoice_rec.po_dist_id)
301 LOOP
302
303 l_link_found := TRUE;
304
305 debug(' asset_rec.fa_state : '||asset_rec.fa_state);
306
307 IF asset_rec.fa_state = 'FA' THEN
308
309 debug(' asset_rec.fa_id : '||asset_rec.fa_id);
310
311 -- cost adj case
312 SELECT current_units,
313 description,
314 asset_category_id
315 INTO l_units,
316 l_asset_description,
317 l_asset_category_id
318 FROM fa_additions
319 WHERE asset_id = asset_rec.fa_id;
320
321 debug(' asset_description : '||l_asset_description);
322
323 SELECT date_placed_in_service
324 INTO l_date_placed_in_service
325 FROM fa_books
326 WHERE asset_id = asset_rec.fa_id
327 AND book_type_code = asset_rec.fa_book_type_code
328 AND date_ineffective is NULL;
329
330 debug(' date_placed_in_service : '||l_date_placed_in_service);
331
332 l_total_asset_units := l_total_asset_units + l_units;
333
334 l_ind := l_ind + 1;
335 l_ma_proc_tbl(l_ind).asset_id := asset_rec.fa_id;
336 l_ma_proc_tbl(l_ind).mass_addition_id := null;
337 l_ma_proc_tbl(l_ind).book_type_code := asset_rec.fa_book_type_code;
338 l_ma_proc_tbl(l_ind).asset_category_id := l_asset_category_id;
339 l_ma_proc_tbl(l_ind).units := l_units;
340 l_ma_proc_tbl(l_ind).description := l_asset_description;
341 l_ma_proc_tbl(l_ind).date_placed_in_service := l_date_placed_in_service;
342 l_ma_proc_tbl(l_ind).expense_ccid := l_expense_ccid;
343
344 ELSIF asset_rec.fa_state = 'FMA' THEN
345
346 -- merge case pending in fma.
347 SELECT description,
348 asset_category_id,
349 book_type_code,
350 date_placed_in_service
351 INTO l_asset_description,
352 l_asset_category_id,
353 l_book_type_code,
354 l_date_placed_in_service
355 FROM fa_mass_additions
356 WHERE mass_addition_id = asset_rec.fa_id;
357
358 SELECT units
359 INTO l_units
360 FROM fa_massadd_distributions
361 WHERE mass_addition_id = asset_rec.fa_id;
362
363 l_total_pending_units := l_total_pending_units + l_units;
364
365 l_ind := l_ind + 1;
366 l_ma_proc_tbl(l_ind).asset_id := null;
367 l_ma_proc_tbl(l_ind).mass_addition_id := asset_rec.fa_id;
368 l_ma_proc_tbl(l_ind).book_type_code := l_book_type_code;
369 l_ma_proc_tbl(l_ind).asset_category_id := l_asset_category_id;
370 l_ma_proc_tbl(l_ind).units := l_units;
371 l_ma_proc_tbl(l_ind).description := l_asset_description;
372 l_ma_proc_tbl(l_ind).date_placed_in_service := l_date_placed_in_service;
373 l_ma_proc_tbl(l_ind).expense_ccid := l_expense_ccid;
374
375 END IF;
376
377 END LOOP; -- cia loop
378
379 -- this is to address the pending data in fma prior to r12
380 IF NOT(l_link_found) THEN
381 FOR fma_rec IN fma_cur(csi_txn_rec.transaction_id, l_dflt_book_type_code)
382 LOOP
383
384 l_link_found := TRUE;
385
386 l_total_pending_units := l_total_pending_units + l_units;
387
388 l_ind := l_ind + 1;
389 l_ma_proc_tbl(l_ind).asset_id := null;
390 l_ma_proc_tbl(l_ind).mass_addition_id := fma_rec.mass_addition_id;
391 l_ma_proc_tbl(l_ind).book_type_code := fma_rec.book_type_code;
392 l_ma_proc_tbl(l_ind).asset_category_id := fma_rec.asset_category_id;
393 l_ma_proc_tbl(l_ind).units := fma_rec.units;
394 l_ma_proc_tbl(l_ind).description := fma_rec.description;
395 l_ma_proc_tbl(l_ind).date_placed_in_service := fma_rec.date_placed_in_service;
396 l_ma_proc_tbl(l_ind).expense_ccid := l_expense_ccid;
397
398 END LOOP;
399 END IF;
400
401 IF l_ma_proc_tbl.COUNT > 0 THEN
402 FOR ma_ind IN l_ma_proc_tbl.FIRST .. l_ma_proc_tbl.LAST
403 LOOP
404
405 l_mass_add_rec := null;
406
407 IF l_ma_proc_tbl(ma_ind).asset_id IS NOT null THEN
408 l_mass_add_rec.add_to_asset_id := l_ma_proc_tbl(ma_ind).asset_id;
409 l_mass_add_rec.posting_status := 'POST';
410 l_mass_add_rec.queue_name := 'ADD TO ASSET';
411 l_per_unit_ipv := p_invoice_rec.invoice_price_variance/l_total_asset_units;
412 END IF;
413
414 IF l_ma_proc_tbl(ma_ind).mass_addition_id IS NOT null THEN
415 l_mass_add_rec.parent_mass_addition_id := l_ma_proc_tbl(ma_ind).mass_addition_id;
416 l_mass_add_rec.merge_parent_mass_additions_id := l_ma_proc_tbl(ma_ind).mass_addition_id;
417 l_mass_add_rec.queue_name := 'POST';
418 l_mass_add_rec.posting_status := 'MERGED';
419 l_mass_add_rec.split_merged_code := 'MC';
420 l_mass_add_rec.merged_code := 'MC';
421 l_per_unit_ipv := p_invoice_rec.invoice_price_variance/l_total_pending_units;
422 END IF;
423
424 l_allocated_ipv := l_ma_proc_tbl(ma_ind).units * l_per_unit_ipv;
425
426 l_mass_add_rec.book_type_code := l_ma_proc_tbl(ma_ind).book_type_code;
427 l_mass_add_rec.asset_category_id := l_ma_proc_tbl(ma_ind).asset_category_id;
428 l_mass_add_rec.description := l_ma_proc_tbl(ma_ind).description;
429 l_mass_add_rec.expense_code_combination_id := l_ma_proc_tbl(ma_ind).expense_ccid;
430 l_mass_add_rec.payables_code_combination_id := p_invoice_rec.payables_ccid;
431 l_mass_add_rec.ap_distribution_line_number := p_invoice_rec.invoice_dist_line_num;
432 l_mass_add_rec.po_number := p_invoice_rec.po_num;
433 l_mass_add_rec.po_vendor_id := p_invoice_rec.po_vendor_id;
434 l_mass_add_rec.invoice_number := p_invoice_rec.invoice_num;
435 l_mass_add_rec.invoice_id := p_invoice_rec.invoice_id;
436 --
437 l_mass_add_rec.payables_cost := l_allocated_ipv;
438 l_mass_add_rec.fixed_assets_cost := l_allocated_ipv;
439 l_mass_add_rec.fixed_assets_units := 1;
440 --
441 l_mass_add_rec.feeder_system_name := cse_asset_util_pkg.g_fa_feeder_name;
442 l_mass_add_rec.reviewer_comments := 'IPV' ;
443 l_mass_add_rec.asset_type := 'CAPITALIZED';
444 l_mass_add_rec.depreciate_flag := 'YES' ;
445 l_mass_add_rec.creation_date := sysdate;
446 l_mass_add_rec.last_update_date := sysdate;
447 l_mass_add_rec.created_by := fnd_global.user_id;
448 l_mass_add_rec.last_updated_by := fnd_global.user_id;
449 l_mass_add_rec.last_update_login := fnd_global.login_id;
450
451 cse_asset_util_pkg.insert_mass_add(
452 p_api_version => 1.0,
453 p_commit => fnd_api.g_false,
454 p_init_msg_list => fnd_api.g_true,
455 p_mass_add_rec => l_mass_add_rec,
456 x_return_status => l_return_status,
457 x_msg_count => l_msg_count,
458 x_msg_data => l_msg_data);
459
460 IF l_return_status <> fnd_api.g_ret_sts_success THEN
461 RAISE fnd_api.g_exc_error;
462 END IF;
463
464 x_processed_flag := 'Y';
465
466 END LOOP;
467 END IF;
468
469 END LOOP; -- csi_txn_loop;
470
471
472 EXCEPTION
473 WHEN fnd_api.g_exc_error THEN
474 l_error_message := nvl(l_error_message, cse_util_pkg.dump_error_stack);
475 debug('Error : '||l_error_message);
476 x_return_status := fnd_api.g_ret_sts_error;
477 x_processed_flag := 'N';
478 l_txn_error_rec := cse_util_pkg.init_txn_error_rec;
479 l_txn_error_rec.error_text := l_error_message;
480 l_txn_error_rec.source_group_ref_id := l_conc_request_id;
481 l_txn_error_rec.source_type := 'AP_INVOICE_DISTRIBUTIONS_ALL';
482 l_txn_error_rec.source_id := p_invoice_rec.invoice_dist_id;
483 l_txn_error_rec.processed_flag := 'N';
484 csi_transactions_pvt.create_txn_error(
485 p_api_version => 1.0,
486 p_init_msg_list => fnd_api.g_true,
487 p_commit => fnd_api.g_false,
488 p_validation_level => fnd_api.g_valid_level_full,
489 p_txn_error_rec => l_txn_error_rec,
490 x_return_status => l_return_status,
491 x_msg_count => l_msg_count,
492 x_msg_data => l_msg_data,
493 x_transaction_error_id => l_transaction_error_id);
494 END interface_to_fa ;
495
496 PROCEDURE process_ipv_to_fa(
497 errbuf OUT NOCOPY VARCHAR2,
498 retcode OUT NOCOPY NUMBER,
499 p_po_header_id IN number,
500 p_inventory_item_id IN number,
501 p_organization_id IN number,
502 p_include_zero_ipv IN varchar2)
503 IS
504
505 l_ib_trackable_flag varchar2(1);
506 l_asset_creation_code varchar2(1);
507 l_conc_request_id number := fnd_global.conc_request_id;
508 l_processed_flag varchar2(1);
509 l_invoice_rec invoice_rec;
510 l_payables_ccid number;
511
512 l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
513
514 l_txn_error_rec csi_datastructures_pub.transaction_error_rec;
515 l_msg_count NUMBER;
516 l_msg_data VARCHAR2(2000);
517 l_transaction_error_Id NUMBER;
518 l_invoice_distribution_id NUMBER;
519
520 CURSOR invoice_cur IS
521 SELECT aida.invoice_id,
522 aida.distribution_line_number,
523 aida.unit_price,
524 aila.quantity_invoiced ,
525 aida.invoice_distribution_id,
526 aida.po_distribution_id,
527 aia.invoice_num,
528 nvl(aida.amount, 0) price_variance,
529 aila.inventory_item_id,
530 aila.org_id
531 FROM ap_invoice_distributions_all aida,
532 ap_invoice_lines_all aila,
533 ap_invoices_all aia
534 WHERE aida.line_type_lookup_code = 'IPV'
535 AND aida.posted_flag = 'Y'
536 AND aida.assets_addition_flag IN ('U','I','N')
537 --AND NVL(aida.assets_tracking_flag,'x') = 'Y'
538 AND aida.po_distribution_id is not null
539 AND aila.invoice_id = aida.invoice_id
540 AND aila.line_number = aida.invoice_line_number
541 AND aila.inventory_item_id = nvl(p_inventory_item_id, aila.inventory_item_id)
542 AND aia.invoice_id = aida.invoice_id
543 AND EXISTS (
544 SELECT '1'
545 FROM csi_transactions ct
546 WHERE ct.transaction_type_id in (105, 112)
547 AND ct.transaction_status_code = 'COMPLETE'
548 AND ct.source_dist_ref_id1 = aida.po_distribution_id)
549 AND EXISTS (
550 SELECT '1'
551 FROM po_distributions_all pod
552 WHERE pod.po_distribution_id = aida.po_distribution_id
553 AND pod.po_header_id = nvl(p_po_header_id,pod.po_header_id)
554 AND pod.destination_organization_id = nvl(p_organization_id, pod.destination_organization_id));
555
556 PROCEDURE get_freight_and_tax(
557 p_item_dist_id IN number,
558 p_invoice_id IN number,
559 p_project_id IN number,
560 p_task_id IN number,
561 x_ft_amount OUT nocopy number,
562 px_ap_ft_tbl OUT nocopy ap_ft_tbl)
563 IS
564
565 l_ft_amount number := 0;
566 l_alloc_ft_amount number := 0;
567 l_ind binary_integer := 0;
568
569 CURSOR ft_cur IS
570 SELECT aida.line_type_lookup_code invoice_distribution_type,
571 aida.invoice_distribution_id,
572 aida.invoice_line_number,
573 aia.invoice_type_lookup_code invoice_type,
574 nvl(aida.amount,0) base_amount,
575 aida.accounting_date,
576 aida.dist_code_combination_id inv_dist_ccid
577 FROM ap_invoice_distributions_all aida,
578 ap_invoices_all aia
579 WHERE aida.invoice_id = p_invoice_id
580 AND aida.project_id = p_project_id
581 AND aida.task_id = p_task_id
582 AND aida.line_type_lookup_code IN ('FREIGHT', 'TAX', 'NONREC_TAX')
583 AND aida.posted_flag = 'Y'
584 AND aida.pa_addition_flag = 'N'
585 AND nvl(aida.reversal_flag, 'N') <> 'Y'
586 AND nvl(aida.tax_recoverable_flag, 'N') = 'N'
587 AND aia.invoice_id = aida.invoice_id
588 AND exists (
589 SELECT 'x' FROM ap_chrg_allocations_all
590 WHERE item_dist_id = p_item_dist_id
591 AND charge_dist_id = aida.invoice_distribution_id);
592
593 FUNCTION allocated_amount (pf_item_dist_id IN number, pf_charge_dist_id IN number)
594 RETURN number
595 IS
596 l_alloc_amount number := 0;
597 CURSOR chrg_alloc_cur IS
598 SELECT allocated_amount
599 FROM ap_chrg_allocations_all
600 WHERE item_dist_id = pf_item_dist_id
601 AND charge_dist_id = pf_charge_dist_id;
602 BEGIN
603 FOR chrg_alloc_rec IN chrg_alloc_cur
604 LOOP
605 l_alloc_amount := l_alloc_amount + chrg_alloc_rec.allocated_amount;
606 END LOOP;
607 RETURN l_alloc_amount;
608 END allocated_amount;
609
610 BEGIN
611 debug('Inside API get_freight_and_tax');
612 FOR ft_rec IN ft_cur
613 LOOP
614 debug(' invoice_dist_id : '||ft_rec.invoice_distribution_id);
615 debug(' line_type : '||ft_rec.invoice_type);
616 debug(' base_amount : '||ft_rec.base_amount);
617
618 l_ind := px_ap_ft_tbl.COUNT + 1;
619 px_ap_ft_tbl(l_ind).invoice_type := ft_rec.invoice_type;
620 px_ap_ft_tbl(l_ind).chrg_dist_id := ft_rec.invoice_distribution_id;
621 px_ap_ft_tbl(l_ind).item_dist_id := p_item_dist_id;
622 px_ap_ft_tbl(l_ind).base_amount := ft_rec.base_amount;
623 px_ap_ft_tbl(l_ind).accounting_date := ft_rec.accounting_date;
624 px_ap_ft_tbl(l_ind).inv_dist_ccid := ft_rec.inv_dist_ccid;
625
626 l_alloc_ft_amount := allocated_amount(p_item_dist_id, ft_rec.invoice_distribution_id);
627 debug(' allocated_amount : '||l_alloc_ft_amount);
628 px_ap_ft_tbl(l_ind).alloc_amount := l_alloc_ft_amount;
629
630 l_ft_amount := l_ft_amount + l_alloc_ft_amount;
631 END LOOP;
632 x_ft_amount := l_ft_amount;
633 debug('TOTAL freight and tax amount : '||l_ft_amount);
634 END get_freight_and_tax;
635
636 BEGIN
637
638 cse_util_pkg.set_debug;
639
640 debug('Inside API cse_ipv_fa_pkg.process_ipv_to_fa');
641 debug(' param.inv_item_id : '||p_inventory_item_id);
642 debug(' param.inv_org_id : '||p_organization_id);
643 debug(' param.po_header_id : '||p_po_header_id);
644 debug(' param.include_zero_ipv : '||p_include_zero_ipv);
645
646 FOR invoice_rec IN invoice_cur
647 LOOP
648
649 debug('processing record # '||invoice_cur%rowcount);
650 mo_global.set_policy_context('S', invoice_rec.org_id);
651
652 l_processed_flag := 'N';
653
654 BEGIN
655
656 debug(' invoice_dist_id : '||invoice_rec.invoice_distribution_id);
657 debug(' invoice_dist_line_num : '||invoice_rec.distribution_line_number);
658 debug(' invoice_id : '||invoice_rec.invoice_id);
659 debug(' price_variance : '||invoice_rec.price_variance);
660 l_invoice_distribution_id := invoice_rec.invoice_distribution_id;
661 l_invoice_rec.invoice_dist_id := invoice_rec.invoice_distribution_id;
662 l_invoice_rec.invoice_dist_line_num := invoice_rec.distribution_line_number;
663 l_invoice_rec.invoice_id := invoice_rec.invoice_id;
664 l_invoice_rec.invoice_num := invoice_rec.invoice_num;
665 l_invoice_rec.po_dist_id := invoice_rec.po_distribution_id;
666 l_invoice_rec.invoice_price_variance := invoice_rec.price_variance;
667 l_invoice_rec.unit_price := invoice_rec.unit_price;
668
669 -- Added for bug 5255658
670 IF NVL(invoice_rec.quantity_invoiced,0) =0 THEN
671 l_invoice_rec.quantity_invoiced := null;
672 ELSE
673 l_invoice_rec.quantity_invoiced := invoice_rec.quantity_invoiced;
674 END IF;
675
676 SELECT pol.item_id,
677 pod.destination_organization_id,
678 pod.variance_account_id,
679 poh.segment1,
680 poh.vendor_id
681 INTO l_invoice_rec.inventory_item_id,
682 l_invoice_rec.organization_id,
683 l_invoice_rec.payables_ccid,
684 l_invoice_rec.po_num,
685 l_invoice_rec.po_vendor_id
686 FROM po_distributions_all pod,
687 po_lines_all pol,
688 po_headers_all poh
689 WHERE pod.po_distribution_id = invoice_rec.po_distribution_id
690 AND pol.po_line_id = pod.po_line_id
691 AND poh.po_header_id = pol.po_header_id;
692
693 l_payables_ccid := cse_asset_util_pkg.get_ap_sla_acct_id(
694 p_invoice_id => invoice_rec.invoice_id,
695 p_invoice_dist_type => 'IPV');
696
697 debug(' price_variance_ccid : '||l_payables_ccid);
698
699 l_invoice_rec.payables_ccid := nvl(l_payables_ccid, l_invoice_rec.payables_ccid);
700
701 IF l_invoice_rec.inventory_item_id is not null
702 AND
703 l_invoice_rec.organization_id is not null
704 THEN
705
706 SELECT nvl(comms_nl_trackable_flag, 'N'),
707 nvl(asset_creation_code, '0')
708 INTO l_ib_trackable_flag,
709 l_asset_creation_code
710 FROM mtl_system_items
711 WHERE inventory_item_id = l_invoice_rec.inventory_item_id
712 AND organization_id = l_invoice_rec.organization_id;
713
714 IF l_ib_trackable_flag = 'Y' AND l_asset_creation_code in ('1', 'Y') THEN
715
716 debug(' po_distribution_id : '|| invoice_rec.po_distribution_id);
717 debug(' inventory_item_id : '||l_invoice_rec.inventory_item_id);
718 debug(' organization_id : '||l_invoice_rec.organization_id);
719
720 interface_to_fa(
721 p_invoice_rec => l_invoice_rec,
722 x_processed_flag => l_processed_flag,
723 x_return_status => l_return_status);
724
725 IF l_return_status <> fnd_api.g_ret_sts_success THEN
726 RAISE fnd_api.g_exc_error;
727 END IF;
728
729 END IF; -- item based, ib tracked and depreciable
730
731 END IF; -- pol.inventory_item_is is not null;
732
733 IF l_processed_flag = 'Y' THEN
734 UPDATE ap_invoice_distributions_all
735 SET assets_addition_flag = 'Y',
736 request_id = l_conc_request_id
737 WHERE invoice_distribution_id = invoice_rec.invoice_distribution_id
738 AND assets_addition_flag <>'Y';
739
740 debug('invoice_distribution_id : '||invoice_rec.invoice_distribution_id);
741 debug('processed successfully. updating ap_invoice_distributions_all.assets_addition_flag = Y');
742 END IF;
743
744 EXCEPTION
745 WHEN fnd_api.g_exc_error THEN
746 --null;
747 l_txn_error_rec := cse_util_pkg.init_txn_error_rec;
748 l_txn_error_rec.error_text := SQLERRM;
749 l_txn_error_rec.source_group_ref_id := l_conc_request_id;
750 l_txn_error_rec.source_type := 'AP_INVOICE_DISTRIBUTIONS_ALL';
751 l_txn_error_rec.source_id := invoice_rec.invoice_distribution_id;
752 l_txn_error_rec.processed_flag := 'N';
753 csi_transactions_pvt.create_txn_error(
754 p_api_version => 1.0,
755 p_init_msg_list => fnd_api.g_true,
756 p_commit => fnd_api.g_false,
757 p_validation_level => fnd_api.g_valid_level_full,
758 p_txn_error_rec => l_txn_error_rec,
759 x_return_status => l_return_status,
760 x_msg_count => l_msg_count,
761 x_msg_data => l_msg_data,
762 x_transaction_error_id => l_transaction_error_id);
763 END;
764
765 END LOOP;
766 --for successful
767 Report_OutPut( p_po_header_id => p_po_header_id,
768 p_inventory_item_id => p_inventory_item_id,
769 p_organization_id => p_organization_id,
770 p_include_zero_ipv => p_include_zero_ipv );
771 EXCEPTION
772 WHEN OTHERS THEN
773 l_txn_error_rec := cse_util_pkg.init_txn_error_rec;
774 l_txn_error_rec.error_text := SQLERRM;
775 l_txn_error_rec.source_group_ref_id := l_conc_request_id;
776 l_txn_error_rec.source_type := 'AP_INVOICE_DISTRIBUTIONS_ALL';
777 l_txn_error_rec.source_id := l_invoice_distribution_id;
778 l_txn_error_rec.processed_flag := 'N';
779 csi_transactions_pvt.create_txn_error(
780 p_api_version => 1.0,
781 p_init_msg_list => fnd_api.g_true,
782 p_commit => fnd_api.g_false,
783 p_validation_level => fnd_api.g_valid_level_full,
784 p_txn_error_rec => l_txn_error_rec,
785 x_return_status => l_return_status,
786 x_msg_count => l_msg_count,
787 x_msg_data => l_msg_data,
788 x_transaction_error_id => l_transaction_error_id);
789 Report_OutPut( p_po_header_id => p_po_header_id,
790 p_inventory_item_id => p_inventory_item_id,
791 p_organization_id => p_organization_id,
792 p_include_zero_ipv => p_include_zero_ipv );
793
794 END process_ipv_to_fa;
795
796 END cse_ipv_fa_pkg;