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