[Home] [Help]
PACKAGE BODY: APPS.CSE_AP_PA_PKG
Source
1 PACKAGE BODY cse_ap_pa_pkg AS
2 /* $Header: CSEAPINB.pls 120.16 2008/01/18 14:01:36 aradhakr ship $ */
3
4 l_debug varchar2(1) := nvl(fnd_profile.value('cse_debug_option'),'N');
5
6 TYPE ap_pa_rec IS RECORD(
7 invoice_id number,
8 invoice_type varchar2(30),
9 invoice_line_number number,
10 invoice_distribution_id number,
11 invoice_distribution_type varchar2(30),
12 distribution_line_number varchar2(30),
13 po_header_id number,
14 po_line_id number,
15 po_distribution_id number,
16 project_id number,
17 task_id number,
18 expenditure_item_date date,
19 expenditure_type varchar2(30),
20 exp_org_id number,
21 dest_org_id number,
22 org_id number,
23 accounting_date date,
24 base_amount number,
25 prorated_amount number,
26 quantity_invoiced number,
27 unit_price number,
28 inv_dist_ccid number,
29 acct_pay_ccid number,
30 inventory_item_id number,
31 item_name varchar2(80),
32 project_num varchar2(30),
33 task_num varchar2(30),
34 exp_org_name varchar2(240),--#5763437
35 exp_ending_date date,
36 depreciable varchar2(1),
37 vendor_id number,
38 vendor_num varchar2(30),
39 expenditure_item_id number,
40 expenditure_item_qty number,
41 attribute6 varchar2(150),
42 attribute7 varchar2(150),
43 attribute8 varchar2(150),
44 attribute9 varchar2(150),
45 attribute10 varchar2(150),
46 orig_transaction_reference varchar2(150),
47 in_service_flag varchar2(1));
48
49 TYPE ap_pa_tbl IS TABLE OF ap_pa_rec index by binary_integer;
50
51 TYPE ap_ft_rec IS RECORD (
52 invoice_type varchar2(15),
53 chrg_dist_id number,
54 item_dist_id number,
55 base_amount number,
56 alloc_amount number,
57 accounting_date date,
58 inv_dist_ccid number);
59
60 TYPE ap_ft_tbl IS TABLE OF ap_ft_rec index by binary_integer;
61
62 PROCEDURE debug(
63 p_message IN varchar2)
64 IS
65 BEGIN
66 IF l_debug = 'Y' THEN
67 cse_debug_pub.add(p_message);
68 IF nvl(fnd_global.conc_request_id, -1) <> -1 THEN
69 fnd_file.put_line(fnd_file.log,p_message);
70 END IF;
71 END IF;
72 EXCEPTION
73 WHEN others THEN
74 null;
75 END debug;
76
77 PROCEDURE get_prorated_ei(
78 px_ap_pa_tbl in out nocopy ap_pa_tbl,
79 x_return_status out nocopy varchar2)
80 IS
81
82 l_ap_pa_tbl ap_pa_tbl;
83 l_ind binary_integer := 0;
84 l_unit_ipv_amount number;
85 l_remaining_qty number;
86 l_prorate_qty number;
87 l_already_prorated boolean;
88
89 MAX_BUFFER_SIZE NUMBER := 5000;
90
91 --Modified cursor for bug6754713
92 CURSOR rcv_ei_cur(p_project_id IN number, p_task_id IN NUMBER, p_org_ID IN NUMBER, p_po_distribution_id IN number) IS
93 SELECT 'EI' ei_stage,
94 ei.expenditure_item_id,
95 ei.quantity,
96 ei.attribute6,
97 ei.attribute7,
98 ei.attribute8,
99 ei.attribute9,
100 ei.attribute10,
101 ei.orig_transaction_reference,
102 substr(orig_transaction_reference, 1,(instr(orig_transaction_reference, '-', 1, 2)-1)) instance_id,
103 ei.transaction_source
104 FROM pa_cost_distribution_lines_all cdl,
105 pa_expenditure_items_all ei
106 WHERE cdl.project_id = p_project_id
107 AND cdl.task_id = p_task_id
108 AND cdl.org_id = p_org_id
109 AND cdl.project_id = ei.project_id
110 AND cdl.task_id = ei.task_id
111 AND cdl.org_id = ei.org_id
112 AND ei.expenditure_item_id = cdl.expenditure_item_id
113 AND ei.transaction_source IN ( 'CSE_PO_RECEIPT', 'CSE_IPV_ADJUSTMENT')
114 AND (ei.transaction_source, cdl.system_reference3) IN (
115 SELECT 'CSE_PO_RECEIPT' txn_source , p_po_distribution_id sys_reference3
116 FROM dual
117 UNION ALL
118 SELECT 'CSE_IPV_ADJUSTMENT' txn_source , distribution_line_number
119 sys_reference3
120 FROM ap_invoice_distributions_all aida
121 WHERE po_distribution_id = p_po_distribution_id
122 and aida.invoice_id = cdl.system_reference2)
123 AND nvl(ei.net_zero_adjustment_flag, 'N') = 'N'
124 UNION ALL
125 SELECT 'TI' ei_stage,
126 null expenditure_item_id,
127 ti.quantity,
128 ti.attribute6,
129 ti.attribute7,
130 ti.attribute8,
131 ti.attribute9,
132 ti.attribute10,
133 ti.orig_transaction_reference,
134 substr(orig_transaction_reference, 1,(instr(orig_transaction_reference, '-', 1, 2)-1)) instance_id,
135 ti.transaction_source
136 FROM pa_transaction_interface_all ti
137 WHERE ti.transaction_source IN ( 'CSE_PO_RECEIPT', 'CSE_IPV_ADJUSTMENT')
138 AND (ti.transaction_source, cdl_system_reference3) IN ( SELECT
139 'CSE_PO_RECEIPT' txn_source , p_po_distribution_id sys_reference3
140 FROM dual
141 UNION ALL
142 SELECT 'CSE_IPV_ADJUSTMENT' txn_source , distribution_line_number
143 sys_reference3
144 FROM ap_invoice_distributions_all aida
145 WHERE po_distribution_id = p_po_distribution_id
146 and aida.invoice_id = ti.cdl_system_reference2)
147 ORDER BY instance_id, transaction_source desc;
148
149 TYPE rcv_ei_tbl IS TABLE OF rcv_ei_cur%ROWTYPE index by binary_integer;
150
151 l_rcv_ei_tbl rcv_ei_tbl;
152
153 BEGIN
154
155 x_return_status := fnd_api.g_ret_sts_success;
156
157 debug('Inside API cse_ap_pa_pkg.get_prorated_ei');
158
159 IF px_ap_pa_tbl.COUNT > 0 THEN
160 FOR ind IN px_ap_pa_tbl.FIRST .. px_ap_pa_tbl.LAST
161 LOOP
162
163 IF px_ap_pa_tbl(ind).depreciable = 'Y' THEN
164 l_ind := l_ind + 1;
165 l_ap_pa_tbl(l_ind) := px_ap_pa_tbl(ind);
166 l_ap_pa_tbl(l_ind).orig_transaction_reference := 'IPV-DEPR-'||px_ap_pa_tbl(ind).invoice_distribution_id;
167 l_ap_pa_tbl(l_ind).prorated_amount := px_ap_pa_tbl(ind).base_amount;
168 ELSE
169
170 l_remaining_qty := px_ap_pa_tbl(ind).quantity_invoiced;
171 l_unit_ipv_amount := px_ap_pa_tbl(ind).base_amount/px_ap_pa_tbl(ind).quantity_invoiced;
172
173 debug(' invoiced_quantity : '||px_ap_pa_tbl(ind).quantity_invoiced);
174 debug(' base_ipv_amount : '||px_ap_pa_tbl(ind).base_amount);
175 debug(' unit_ipv_amount : '||l_unit_ipv_amount);
176
177 debug(' Project_id : '||px_ap_pa_tbl(ind).project_id);
178 debug(' Task id : '||px_ap_pa_tbl(ind).task_id);
179 debug(' Org Id : '||px_ap_pa_tbl(ind).org_id);
180 debug(' Dist Id : '||px_ap_pa_tbl(ind).po_distribution_id);
181
182 OPEN rcv_ei_cur(px_ap_pa_tbl(ind).project_id, px_ap_pa_tbl(ind).task_id, px_ap_pa_tbl(ind).org_id, px_ap_pa_tbl(ind).po_distribution_id);
183 LOOP
184 debug(' Inside rcv cursor - open');
185
186 --Added bulk collect for bug 6716720--
187 FETCH rcv_ei_cur BULK COLLECT
188 INTO l_rcv_ei_tbl
189 LIMIT MAX_BUFFER_SIZE;
190 debug(' Inside rcv cursor -fetch count '||l_rcv_ei_tbl.COUNT);
191
192 IF l_rcv_ei_tbl.COUNT > 0 THEN
193 FOR j IN 1 .. l_rcv_ei_tbl.COUNT
194 LOOP
195 if (l_rcv_ei_tbl(j).transaction_source = 'CSE_PO_RECEIPT') THEN --loop added anjgupta
196 debug('received expenditure record # '||j);
197 debug(' expenditure_stage : '||l_rcv_ei_tbl(j).ei_stage);
198 debug(' expenditure_item_id : '||l_rcv_ei_tbl(j).expenditure_item_id);
199 debug(' orig_transaction_ref : '||l_rcv_ei_tbl(j).orig_transaction_reference);
200 debug(' attribute6 : '||l_rcv_ei_tbl(j).attribute6);
201 debug(' attribute7 : '||l_rcv_ei_tbl(j).attribute7);
202
203 --Modified the below code FP bug--
204 -- check if this ei is already prorated for an earlier ipv
205
206 for q in j .. l_rcv_ei_tbl.LAST
207 loop
208 l_already_prorated := FALSE;
209 if (l_rcv_ei_tbl(q).transaction_source = 'CSE_IPV_ADJUSTMENT') THEN
210 IF (l_rcv_ei_tbl(q).instance_id = 'EI-'||l_rcv_ei_tbl(j).expenditure_item_id OR
211 l_rcv_ei_tbl(q).instance_id = 'TI-'||l_rcv_ei_tbl(j).orig_transaction_reference OR
212 l_rcv_ei_tbl(q).attribute6 = l_rcv_ei_tbl(j).attribute6 OR
213 l_rcv_ei_tbl(q).attribute7 = l_rcv_ei_tbl(j).attribute7 )
214 THEN
215 l_already_prorated := TRUE;
216 exit;
217 end if;
218 end if;
219 end loop;
220
221 IF (NOT l_already_prorated) THEN
222 debug(' not already prorated');
223 ELSE
224 debug(' already prorated');
225 END IF;
226
227 IF (px_ap_pa_tbl(ind).quantity_invoiced < l_rcv_ei_tbl(j).quantity) THEN
228 debug(' partially prorated already');
229 END IF;
230
231 IF (NOT l_already_prorated) OR (px_ap_pa_tbl(ind).quantity_invoiced < l_rcv_ei_tbl(j).quantity) THEN
232
233 l_prorate_qty := l_rcv_ei_tbl(j).quantity;
234
235 l_remaining_qty := l_remaining_qty - l_rcv_ei_tbl(j).quantity;
236
237 IF l_remaining_qty < 0 THEN
238 l_prorate_qty := px_ap_pa_tbl(ind).quantity_invoiced;
239 END IF;
240
241 debug(' remaining_quantity : '||l_remaining_qty);
242 debug(' prorate_quantity : '||l_prorate_qty);
243
244 l_ind := l_ind + 1;
245 l_ap_pa_tbl(l_ind) := px_ap_pa_tbl(ind);
246 l_ap_pa_tbl(l_ind).attribute6 := l_rcv_ei_tbl(j).attribute6;
247 l_ap_pa_tbl(l_ind).attribute7 := l_rcv_ei_tbl(j).attribute7;
248 l_ap_pa_tbl(l_ind).attribute8 := l_rcv_ei_tbl(j).attribute8;
249 l_ap_pa_tbl(l_ind).attribute9 := l_rcv_ei_tbl(j).attribute9;
250 l_ap_pa_tbl(l_ind).attribute10 := l_rcv_ei_tbl(j).attribute10;
251 IF l_rcv_ei_tbl(j).ei_stage = 'EI' THEN
252 l_ap_pa_tbl(l_ind).orig_transaction_reference := 'EI-'||l_rcv_ei_tbl(j).expenditure_item_id||'-'||
253 px_ap_pa_tbl(ind).invoice_distribution_id;
254 ELSIF l_rcv_ei_tbl(j).ei_stage = 'TI' THEN
255 l_ap_pa_tbl(l_ind).orig_transaction_reference := 'TI-'||l_rcv_ei_tbl(j).orig_transaction_reference||'-'||
256 px_ap_pa_tbl(ind).invoice_distribution_id;
257 END IF;
258
259 l_ap_pa_tbl(l_ind).prorated_amount := l_prorate_qty * l_unit_ipv_amount;
260 l_ap_pa_tbl(l_ind).expenditure_item_id := l_rcv_ei_tbl(j).expenditure_item_id;
261 l_ap_pa_tbl(l_ind).expenditure_item_qty := l_rcv_ei_tbl(j).quantity;
262
263 IF l_rcv_ei_tbl(j).attribute7 is not null AND l_rcv_ei_tbl(j).attribute8 is not null THEN
264 l_ap_pa_tbl(l_ind).in_service_flag := 'Y';
265 ELSE
266 l_ap_pa_tbl(l_ind).in_service_flag := 'N';
267 END IF;
268
269 EXIT WHEN l_remaining_qty <= 0;
270
271 END IF; -- if rcv ei not already processed by another partial ipv txn
272 END IF;
273 END LOOP;
274 END IF;
275 EXIT when rcv_ei_cur%NOTFOUND OR l_remaining_qty <= 0;
276 END LOOP; -- get all the received eis rcv_ei_cur loop
277
278 CLOSE rcv_ei_cur;
279 END IF; -- depreciable
280 END LOOP;
281 END IF; -- px tbl count > 0
282
283 px_ap_pa_tbl := l_ap_pa_tbl;
284
285 END get_prorated_ei;
286
287 PROCEDURE populate_pa_txn_intf(
288 p_ap_pa_tbl IN ap_pa_tbl,
289 x_return_status OUT nocopy varchar2,
290 x_error_message OUT nocopy varchar2)
291 IS
292
293 l_ind binary_integer := 0;
294 l_nl_pa_tbl cse_ipa_trans_pkg.nl_pa_interface_tbl_type;
295
296 l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
297 l_error_message varchar2(2000);
298
299 BEGIN
300
301 x_return_status := fnd_api.g_ret_sts_success;
302
303 debug('Inside API cse_ap_pa_pkg.populate_pa_txn_intf');
304
305 IF p_ap_pa_tbl.count > 0 THEN
306 FOR ind IN p_ap_pa_tbl.FIRST .. p_ap_pa_tbl.LAST
307 LOOP
308
309 debug('prorated invoice distributions. record # '||ind);
310 debug(' invoice_distribution_id : '||p_ap_pa_tbl(ind).invoice_distribution_id);
311 debug(' project_id : '||p_ap_pa_tbl(ind).project_id);
312 debug(' task_id : '||p_ap_pa_tbl(ind).task_id);
313 debug(' prorated_amount : '||p_ap_pa_tbl(ind).prorated_amount);
314 debug(' related_exp_item_id : '||p_ap_pa_tbl(ind).expenditure_item_id);
315 debug(' orig_transaction_ref : '||p_ap_pa_tbl(ind).orig_transaction_reference);
316
317 l_ind := l_ind + 1;
318 l_nl_pa_tbl(l_ind).expenditure_ending_date := p_ap_pa_tbl(ind).exp_ending_date;
319 l_nl_pa_tbl(l_ind).organization_name := p_ap_pa_tbl(ind).exp_org_name;
320 l_nl_pa_tbl(l_ind).expenditure_item_date := p_ap_pa_tbl(ind).expenditure_item_date;
321 l_nl_pa_tbl(l_ind).project_number := p_ap_pa_tbl(ind).project_num;
322 l_nl_pa_tbl(l_ind).task_number := p_ap_pa_tbl(ind).task_num;
323 l_nl_pa_tbl(l_ind).expenditure_type := p_ap_pa_tbl(ind).expenditure_type;
324 l_nl_pa_tbl(l_ind).quantity := 1;
325 l_nl_pa_tbl(l_ind).batch_name := 'IPV-'||p_ap_pa_tbl(ind).invoice_distribution_id;
326 l_nl_pa_tbl(l_ind).raw_cost_rate := p_ap_pa_tbl(ind).prorated_amount;
327 l_nl_pa_tbl(l_ind).acct_raw_cost := p_ap_pa_tbl(ind).prorated_amount;
328 l_nl_pa_tbl(l_ind).burdened_cost := p_ap_pa_tbl(ind).prorated_amount;
329 l_nl_pa_tbl(l_ind).burdened_cost_rate := p_ap_pa_tbl(ind).prorated_amount;
330 l_nl_pa_tbl(l_ind).denom_raw_cost := p_ap_pa_tbl(ind).prorated_amount;
331 l_nl_pa_tbl(l_ind).raw_cost := p_ap_pa_tbl(ind).prorated_amount;
332 l_nl_pa_tbl(l_ind).expenditure_comment := 'ENTERPRISE INSTALL BASE';
333 l_nl_pa_tbl(l_ind).transaction_status_code := 'P';
334 l_nl_pa_tbl(l_ind).attribute6 := p_ap_pa_tbl(ind).attribute6;
335 l_nl_pa_tbl(l_ind).attribute7 := p_ap_pa_tbl(ind).attribute7;
336 l_nl_pa_tbl(l_ind).attribute8 := p_ap_pa_tbl(ind).attribute8;
337 l_nl_pa_tbl(l_ind).attribute9 := p_ap_pa_tbl(ind).attribute9;
338 l_nl_pa_tbl(l_ind).attribute10 := p_ap_pa_tbl(ind).attribute10;
339 l_nl_pa_tbl(l_ind).orig_transaction_reference := p_ap_pa_tbl(ind).orig_transaction_reference;
340 l_nl_pa_tbl(l_ind).unmatched_negative_txn_flag := 'Y';
341 l_nl_pa_tbl(l_ind).org_Id := p_ap_pa_tbl(ind).org_id ;
342 l_nl_pa_tbl(l_ind).dr_code_combination_id := p_ap_pa_tbl(ind).inv_dist_ccid;
343 l_nl_pa_tbl(l_ind).cr_code_combination_id := p_ap_pa_tbl(ind).acct_pay_ccid;
344 l_nl_pa_tbl(l_ind).gl_date := p_ap_pa_tbl(ind).accounting_date;
345 l_nl_pa_tbl(l_ind).system_linkage := 'VI';
346 l_nl_pa_tbl(l_ind).cdl_system_reference1 := p_ap_pa_tbl(ind).vendor_id;
347 l_nl_pa_tbl(l_ind).cdl_system_reference2 := p_ap_pa_tbl(ind).invoice_id ;
348 l_nl_pa_tbl(l_ind).cdl_system_reference3 := p_ap_pa_tbl(ind).invoice_line_number;
349 l_nl_pa_tbl(l_ind).cdl_system_reference5 := p_ap_pa_tbl(ind).invoice_distribution_id;
350 l_nl_pa_tbl(l_ind).document_type := p_ap_pa_tbl(ind).invoice_type;
351 l_nl_pa_tbl(l_ind).document_distribution_type := p_ap_pa_tbl(ind).invoice_distribution_type;
352 l_nl_pa_tbl(l_ind).user_transaction_source := 'ENTERPRISE INSTALL BASE';
353 l_nl_pa_tbl(l_ind).last_update_date := sysdate;
354 l_nl_pa_tbl(l_ind).last_updated_by := fnd_global.user_id;
355 l_nl_pa_tbl(l_ind).creation_date := sysdate;
356 l_nl_pa_tbl(l_ind).created_by := fnd_global.user_Id;
357 l_nl_pa_tbl(l_ind).vendor_number := p_ap_pa_tbl(ind).vendor_num;
358 l_nl_pa_tbl(l_ind).vendor_id := p_ap_pa_tbl(ind).vendor_id;
359 l_nl_pa_tbl(l_ind).inventory_item_id := p_ap_pa_tbl(ind).inventory_item_id;
360 l_nl_pa_tbl(l_ind).project_id := p_ap_pa_tbl(ind).project_id;
361 l_nl_pa_tbl(l_ind).task_id := p_ap_pa_tbl(ind).task_id;
362 l_nl_pa_tbl(l_ind).po_header_id := p_ap_pa_tbl(ind).po_header_id;
363 l_nl_pa_tbl(l_ind).po_line_id := p_ap_pa_tbl(ind).po_line_id;
364
365 IF p_ap_pa_tbl(ind).depreciable = 'Y' THEN
366 l_nl_pa_tbl(l_ind).billable_flag := 'N';
367 l_nl_pa_tbl(l_ind).transaction_source := 'CSE_IPV_ADJUSTMENT_DEPR';
368 ELSE
369 l_nl_pa_tbl(l_ind).billable_flag := 'Y';
370 l_nl_pa_tbl(l_ind).transaction_source := 'CSE_IPV_ADJUSTMENT';
371 END IF;
372
373 END LOOP;
374
375 debug('Inside API cse_ipa_trans_pkg.populate_pa_interface');
376 debug(' nl_pa_tbl.count : '||l_nl_pa_tbl.COUNT);
377
378 cse_ipa_trans_pkg.populate_pa_interface(
379 p_nl_pa_interface_tbl => l_nl_pa_tbl,
380 x_return_status => l_return_status,
381 x_error_message => l_error_message);
382
383 IF l_return_status <> fnd_api.g_ret_sts_success THEN
384 RAISE fnd_api.g_exc_error;
385 END IF;
386
387 END IF;
388
389 EXCEPTION
390 WHEN others THEN
391 x_return_status := fnd_api.g_ret_sts_error;
392 x_error_message := l_error_message;
393 END populate_pa_txn_intf;
394
395
396 PROCEDURE process_ipv_to_pa(
397 errbuf OUT NOCOPY VARCHAR2,
398 retcode OUT NOCOPY NUMBER,
399 p_project_id IN number,
400 p_task_id IN number,
401 p_po_header_id IN number,
402 p_inventory_item_id IN number,
403 p_organization_id IN number)
404 IS
405
406 CURSOR ap_inv_ipv_cur(p_project_id IN number) IS
407 SELECT aia.invoice_type_lookup_code invoice_type,
408 aida.line_type_lookup_code invoice_distribution_type,
409 aida.invoice_distribution_id,
410 pda.po_header_id,
411 pda.po_line_id,
412 pda.po_distribution_id,
413 pda.project_id,
414 pda.task_id,
415 pda.expenditure_item_date,
416 pda.expenditure_type,
417 pda.expenditure_organization_id exp_org_id,
418 pda.destination_organization_id dest_org_id,
419 aida.org_id,
420 aida.accounting_date,
421 aida.invoice_id,
422 aida.distribution_line_number,
423 nvl(aida.amount,0) base_amount,
424 aila.line_number,
425 aila.quantity_invoiced,
426 aida.unit_price,
427 aida.price_var_code_combination_id inv_dist_ccid
428 FROM po_distributions_all pda,
429 ap_invoice_distributions_all aida,
430 ap_invoice_lines_all aila,
431 ap_invoices_all aia
432 WHERE EXISTS (
433 SELECT '1' FROM csi_transactions ct
434 WHERE ct.transaction_type_id = 105
435 AND ct.transaction_status_code = 'COMPLETE'
436 AND ct.source_dist_ref_id1 = pda.po_distribution_id)
437 AND pda.project_id = nvl(p_project_id, pda.project_id)
438 AND pda.task_id = nvl(p_task_id, pda.task_id)
439 AND aida.po_distribution_id = pda.po_distribution_id
440 and aida.line_type_lookup_code = 'IPV'
441 AND aida.posted_flag = 'Y'
442 AND aida.pa_addition_flag = 'N'
443 AND nvl(aida.reversal_flag, 'N') <> 'Y'
444 AND aila.invoice_id = aida.invoice_id
445 AND aila.line_number = aida.invoice_line_number
446 AND aia.invoice_id = aida.invoice_id;
447
448 l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
449 l_error_message varchar2(2000);
450 l_txn_error_rec csi_datastructures_pub.transaction_error_rec;
451 l_msg_data varchar2(2000);
452 l_msg_count number;
453 l_txn_error_id number;
454 l_inv_dist_id number;
455 l_total_ipv_amount number := 0;
456 l_total_ft_amount number := 0;
457 l_total_amount number := 0;
458 l_ap_ft_tbl ap_ft_tbl;
459 l_ap_pa_tbl ap_pa_tbl;
460
461 PROCEDURE get_freight_and_tax(
462 p_item_dist_id IN number,
463 p_invoice_id IN number,
464 p_project_id IN number,
465 p_task_id IN number,
466 x_ft_amount OUT nocopy number,
467 px_ap_ft_tbl OUT nocopy ap_ft_tbl)
468 IS
469
470 l_ft_amount number := 0;
471 l_alloc_ft_amount number := 0;
472 l_ind binary_integer := 0;
473
474 CURSOR ft_cur IS
475 SELECT aida.line_type_lookup_code invoice_distribution_type,
476 aida.invoice_distribution_id,
477 aida.invoice_line_number,
478 aia.invoice_type_lookup_code invoice_type,
479 nvl(aida.amount,0) base_amount,
480 aida.accounting_date,
481 aida.dist_code_combination_id inv_dist_ccid
482 FROM ap_invoice_distributions_all aida,
483 ap_invoices_all aia
484 WHERE aida.invoice_id = p_invoice_id
485 AND aida.project_id = p_project_id
486 AND aida.task_id = p_task_id
487 AND aida.line_type_lookup_code IN ('FREIGHT', 'TAX', 'NONREC_TAX')
488 AND aida.posted_flag = 'Y'
489 AND aida.pa_addition_flag = 'N'
490 AND nvl(aida.reversal_flag, 'N') <> 'Y'
491 AND nvl(aida.tax_recoverable_flag, 'N') = 'N'
492 AND aia.invoice_id = aida.invoice_id
493 AND exists (
494 SELECT 'x' FROM ap_chrg_allocations_all
495 WHERE item_dist_id = p_item_dist_id
496 AND charge_dist_id = aida.invoice_distribution_id);
497
498 FUNCTION allocated_amount (pf_item_dist_id IN number, pf_charge_dist_id IN number)
499 RETURN number
500 IS
501 l_alloc_amount number := 0;
502 CURSOR chrg_alloc_cur IS
503 SELECT allocated_amount
504 FROM ap_chrg_allocations_all
505 WHERE item_dist_id = pf_item_dist_id
506 AND charge_dist_id = pf_charge_dist_id;
507 BEGIN
508 FOR chrg_alloc_rec IN chrg_alloc_cur
509 LOOP
510 l_alloc_amount := l_alloc_amount + chrg_alloc_rec.allocated_amount;
511 END LOOP;
512 RETURN l_alloc_amount;
513 END allocated_amount;
514
515 BEGIN
516 debug('Inside API get_freight_and_tax');
517 FOR ft_rec IN ft_cur
518 LOOP
519 debug(' invoice_dist_id : '||ft_rec.invoice_distribution_id);
520 debug(' line_type : '||ft_rec.invoice_type);
521 debug(' base_amount : '||ft_rec.base_amount);
522
523 l_ind := px_ap_ft_tbl.COUNT + 1;
524 px_ap_ft_tbl(l_ind).invoice_type := ft_rec.invoice_type;
525 px_ap_ft_tbl(l_ind).invoice_type := ft_rec.invoice_type;
526 px_ap_ft_tbl(l_ind).chrg_dist_id := ft_rec.invoice_distribution_id;
527 px_ap_ft_tbl(l_ind).item_dist_id := p_item_dist_id;
528 px_ap_ft_tbl(l_ind).base_amount := ft_rec.base_amount;
529 px_ap_ft_tbl(l_ind).accounting_date := ft_rec.accounting_date;
530 px_ap_ft_tbl(l_ind).inv_dist_ccid := ft_rec.inv_dist_ccid;
531
532 l_alloc_ft_amount := allocated_amount(p_item_dist_id, ft_rec.invoice_distribution_id);
533 debug(' allocated_amount : '||l_alloc_ft_amount);
534 px_ap_ft_tbl(l_ind).alloc_amount := l_alloc_ft_amount;
535
536 l_ft_amount := l_ft_amount + l_alloc_ft_amount;
537 END LOOP;
538 x_ft_amount := l_ft_amount;
539 debug('TOTAL freight and tax amount : '||l_ft_amount);
540 END get_freight_and_tax;
541
542 BEGIN
543
544 cse_util_pkg.set_debug;
545
546 debug('Inside API cse_ap_pa_pkg.process_ipv_to_pa');
547
548 debug(' p_project_id : '||p_project_id);
549
550
551 FOR ap_inv_rec in ap_inv_ipv_cur(p_project_id)
552 LOOP
553
554 debug('invoice dist record # '||ap_inv_ipv_cur%rowcount);
555
556 debug(' invoice_dist_id : '||ap_inv_rec.invoice_distribution_id);
557 debug(' invoice_id : '||ap_inv_rec.invoice_id);
558 debug(' po_distribution_id : '||ap_inv_rec.po_distribution_id);
559 debug(' project_id : '||ap_inv_rec.project_id);
560 debug(' task_id : '||ap_inv_rec.task_id);
561 debug(' base_amount : '||ap_inv_rec.base_amount);
562 debug(' org_id : '||ap_inv_rec.org_id);
563
564 l_total_ft_amount := 0;
565 l_total_ipv_amount := 0;
566 l_ap_pa_tbl.delete;
567
568 IF ap_inv_rec.base_amount <> 0 THEN
569 l_total_ipv_amount := l_total_ipv_amount + ap_inv_rec.base_amount;
570 END IF;
571
572 get_freight_and_tax(
573 p_item_dist_id => ap_inv_rec.invoice_distribution_id,
574 p_invoice_id => ap_inv_rec.invoice_id,
575 p_project_id => ap_inv_rec.project_id,
576 p_task_id => ap_inv_rec.task_id,
577 x_ft_amount => l_total_ft_amount,
578 px_ap_ft_tbl => l_ap_ft_tbl);
579
580 l_total_amount := l_total_ipv_amount + l_total_ft_amount;
581
582 debug('TOTAL ipv and freight and tax amount : '||l_total_amount);
583
584 IF l_total_amount <> 0 THEN
585
586 mo_global.set_policy_context('S', ap_inv_rec.org_id);
587
588 SELECT accts_pay_code_combination_id
589 INTO l_ap_pa_tbl(1).acct_pay_ccid
590 FROM ap_system_parameters_all
591 WHERE org_id = ap_inv_rec.org_id;
592
593 debug(' ap_code_combination_id : '||l_ap_pa_tbl(1).acct_pay_ccid);
594
595 SELECT item_id
596 INTO l_ap_pa_tbl(1).inventory_item_id
597 FROM po_lines_all
598 WHERE po_line_id = ap_inv_rec.po_line_id;
599
600 debug(' inventory_item_id : '||l_ap_pa_tbl(1).inventory_item_id);
601
602 SELECT concatenated_segments
603 INTO l_ap_pa_tbl(1).item_name
604 FROM mtl_system_items_kfv
605 WHERE inventory_item_id = l_ap_pa_tbl(1).inventory_item_id
606 AND organization_id = ap_inv_rec.dest_org_id;
607
608 debug(' inventory_item : '||l_ap_pa_tbl(1).item_name);
609
610 SELECT name
611 INTO l_ap_pa_tbl(1).exp_org_name
612 FROM hr_all_organization_units
613 WHERE organization_id = ap_inv_rec.exp_org_id;
614
615 debug(' organization : '||l_ap_pa_tbl(1).exp_org_name);
616
617 SELECT segment1
618 INTO l_ap_pa_tbl(1).project_num
619 FROM pa_projects_all
620 WHERE project_id = ap_inv_rec.project_id;
621
622 debug(' project_number : '||l_ap_pa_tbl(1).project_num);
623
624 SELECT task_number
625 INTO l_ap_pa_tbl(1).task_num
626 FROM pa_tasks
627 WHERE project_id = ap_inv_rec.project_id
628 AND task_id = ap_inv_rec.task_id;
629
630 debug(' task_number : '||l_ap_pa_tbl(1).task_num);
631
632 SELECT vendor_id
633 INTO l_ap_pa_tbl(1).vendor_id
634 FROM ap_invoices_all
635 WHERE invoice_id = ap_inv_rec.invoice_id;
636
637 debug(' vendor_id : '||l_ap_pa_tbl(1).vendor_id);
638
639 SELECT segment1
640 INTO l_ap_pa_tbl(1).vendor_num
641 FROM po_vendors
642 WHERE vendor_id = l_ap_pa_tbl(1).vendor_id;
643
644 debug(' vendor_number : '||l_ap_pa_tbl(1).vendor_num);
645
646 cse_util_pkg.check_depreciable(
647 p_inventory_item_id => l_ap_pa_tbl(1).inventory_item_id,
648 p_depreciable => l_ap_pa_tbl(1).depreciable);
649
650 l_ap_pa_tbl(1).exp_ending_date := pa_utils.getweekending(ap_inv_rec.expenditure_item_date);
651
652 debug(' exp_ending_date : '||l_ap_pa_tbl(1).exp_ending_date);
653
654 l_ap_pa_tbl(1).invoice_id := ap_inv_rec.invoice_id;
655 l_ap_pa_tbl(1).invoice_type := ap_inv_rec.invoice_type;
656 l_ap_pa_tbl(1).invoice_line_number := ap_inv_rec.line_number;
657 l_ap_pa_tbl(1).invoice_distribution_id := ap_inv_rec.invoice_distribution_id;
658 l_ap_pa_tbl(1).invoice_distribution_type := ap_inv_rec.invoice_distribution_type;
659 l_ap_pa_tbl(1).po_header_id := ap_inv_rec.po_header_id;
660 l_ap_pa_tbl(1).po_line_id := ap_inv_rec.po_line_id;
661 l_ap_pa_tbl(1).po_distribution_id := ap_inv_rec.po_distribution_id;
662 l_ap_pa_tbl(1).project_id := ap_inv_rec.project_id;
663 l_ap_pa_tbl(1).task_id := ap_inv_rec.task_id;
664 l_ap_pa_tbl(1).expenditure_item_date := ap_inv_rec.expenditure_item_date;
665 l_ap_pa_tbl(1).expenditure_type := ap_inv_rec.expenditure_type;
666 l_ap_pa_tbl(1).exp_org_id := ap_inv_rec.exp_org_id;
667 l_ap_pa_tbl(1).dest_org_id := ap_inv_rec.dest_org_id;
668 l_ap_pa_tbl(1).org_id := ap_inv_rec.org_id;
669 l_ap_pa_tbl(1).accounting_date := ap_inv_rec.accounting_date;
670 l_ap_pa_tbl(1).distribution_line_number := ap_inv_rec.distribution_line_number;
671 l_ap_pa_tbl(1).base_amount := l_total_amount;
672 l_ap_pa_tbl(1).quantity_invoiced := ap_inv_rec.quantity_invoiced;
673 l_ap_pa_tbl(1).unit_price := ap_inv_rec.unit_price;
674 l_ap_pa_tbl(1).inv_dist_ccid := cse_asset_util_pkg.get_ap_sla_acct_id(
675 p_invoice_id => ap_inv_rec.invoice_id,
676 p_invoice_dist_type => 'IPV');
677 debug(' ipv_ccid : '||l_ap_pa_tbl(1).inv_dist_ccid);
678 IF l_ap_pa_tbl(1).inv_dist_ccid = -99 THEN
679 RAISE fnd_api.g_exc_error;
680 END IF;
681
682 get_prorated_ei(
683 px_ap_pa_tbl => l_ap_pa_tbl,
684 x_return_status => l_return_status);
685
686 IF l_return_status <> fnd_api.g_ret_sts_success THEN
687 RAISE fnd_api.g_exc_error;
688 END IF;
689
690 populate_pa_txn_intf(
691 p_ap_pa_tbl => l_ap_pa_tbl,
692 x_return_status => l_return_status,
693 x_error_message => l_error_message);
694
695 IF l_return_status <> fnd_api.g_ret_sts_success THEN
696 RAISE fnd_api.g_exc_error;
697 END IF;
698
699 END IF;
700
701 debug('updating ap_invoice_distributions_all.pa_addition_flag = Y ');
702
703 UPDATE ap_invoice_distributions_all
704 SET pa_addition_flag = 'Y',
705 last_update_date = sysdate,
706 last_updated_by = fnd_global.user_id,
707 last_update_login = fnd_global.login_id,
708 request_id = fnd_global.conc_request_id
709 WHERE invoice_distribution_id = ap_inv_rec.invoice_distribution_id;
710
711 IF l_ap_ft_tbl.COUNT > 0 THEN
712 FOR l_ind IN l_ap_ft_tbl.FIRST .. l_ap_ft_tbl.LAST
713 LOOP
714
715 null;
716 --debug('updating ap_invoice_distributions_all.pa_addition_flag = Y ');
717 END LOOP;
718 END IF;
719
720 END LOOP;
721
722 EXCEPTION
723 WHEN fnd_api.g_exc_error THEN
724
725 l_txn_error_rec := CSE_UTIL_PKG.init_txn_error_Rec;
726 l_txn_error_rec.error_text := nvl(l_error_message,cse_util_pkg.dump_error_stack);
727 l_txn_error_rec.source_group_ref_id := fnd_global.conc_request_id;
728 l_txn_error_rec.source_type := 'CSEIPVP';
729 l_txn_error_rec.source_id := l_inv_dist_id;
730 l_txn_error_rec.processed_flag := 'N';
731
732 csi_transactions_pvt.create_txn_error(
733 p_api_version => 1.0 ,
734 p_init_msg_list => fnd_api.g_true,
735 p_commit => fnd_api.g_true,
736 p_validation_level => fnd_api.g_valid_level_full,
737 p_txn_error_rec => l_txn_error_rec,
738 x_return_status => l_return_status,
739 x_msg_count => l_msg_count,
740 x_msg_data => l_msg_data,
741 x_transaction_error_id => l_txn_error_id);
742
743 END process_ipv_to_pa;
744
745 END cse_ap_pa_pkg;