[Home] [Help]
PACKAGE BODY: APPS.AP_RETAINAGE_PKG
Source
1 PACKAGE BODY AP_RETAINAGE_PKG AS
2 /* $Header: apcwrtnb.pls 120.10.12020000.3 2013/01/30 07:21:14 harsanan ship $ */
3
4 -- FND logging global variables
5
6 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AP_RETAINAGE_PKG';
7 G_MSG_UERROR CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR;
8 G_MSG_ERROR CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_ERROR;
9 G_MSG_SUCCESS CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_SUCCESS;
10 G_MSG_HIGH CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH;
11 G_MSG_MEDIUM CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM;
12 G_MSG_LOW CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW;
13 G_LINES_PER_FETCH CONSTANT NUMBER := 1000;
14
15 G_CURRENT_RUNTIME_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
16 G_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
17 G_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
18 G_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
19 G_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
20 G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
21 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
22 G_MODULE_NAME CONSTANT VARCHAR2(100) := 'AP.PLSQL.AP_RETAINAGE_PKG.';
23
24 TYPE retDistType IS TABLE OF ap_invoice_distributions_all%rowtype INDEX BY PLS_INTEGER;
25
26 x_retainage_dist_tab retDistType;
27
28 Procedure Insert_Distributions (x_invoice_id IN NUMBER,
29 x_invoice_line_number IN NUMBER,
30 x_retainage_dist_tab OUT NOCOPY retDistType);
31
32 Procedure Update_Payment_Schedules (x_invoice_id IN ap_invoices.invoice_id%type);
33
34 Procedure Update_PO_Shipment_Dists (x_line_location_id IN ap_invoice_lines_all.po_line_location_id%type,
35 x_retained_amount IN ap_invoice_lines_all.retained_amount%type,
36 x_retainage_dist_tab IN retDistType);
37
38 Procedure Print (x_api_name IN Varchar2, x_debug_info IN Varchar2);
39
40
41 CURSOR c_invoice_info(c_invoice_id IN ap_invoices.invoice_id%type,
42 c_invoice_line_number IN ap_invoice_lines.line_number%type) IS
43 Select ai.invoice_amount invoice_amount,
44 ai.exchange_rate exchange_rate,
45 ai.invoice_currency_code invoice_currency_code,
46 ai.payment_currency_code payment_currency_code,
47 ai.payment_cross_rate payment_cross_rate,
48 ai.amount_applicable_to_discount amount_applicable_to_discount,
49 ai.invoice_type_lookup_code invoice_type_lookup_code,
50 ai.net_of_retainage_flag net_of_retainage_flag,
51 ail.amount line_amount,
52 ail.retained_amount retained_amount,
53 ail.po_line_location_id po_line_location_id,
54 decode(fc.minimum_accountable_unit,
55 null, round((ail.retained_amount * ai.exchange_rate),
56 fc.precision),
57 round((ail.retained_amount * ai.exchange_rate)
58 / fc.minimum_accountable_unit) * fc.minimum_accountable_unit)
59 base_retained_amount,
60 fc.precision precision,
61 fc.minimum_accountable_unit minimum_accountable_unit,
62 fsp.retainage_code_combination_id retainage_code_combination_id
63 From ap_invoices ai,
64 ap_invoice_lines ail,
65 ap_system_parameters asp,
66 financials_system_parameters fsp,
67 fnd_currencies fc
68 Where ail.invoice_id = c_invoice_id
69 And ail.line_number = c_invoice_line_number
70 And ai.invoice_id = ail.invoice_id
71 And ai.org_id = asp.org_id
72 And asp.org_id = fsp.org_id
73 And ai.invoice_currency_code = fc.currency_code (+);
74
75 g_invoice_info c_invoice_info%ROWTYPE;
76 g_user_id NUMBER;
77 g_login_id NUMBER;
78
79 PROCEDURE Create_Retainage_Distributions(x_invoice_id IN ap_invoices.invoice_id%type,
80 x_invoice_line_number IN ap_invoice_lines.line_number%type) AS
81
82 l_debug_info Varchar2(240);
83 l_api_name Constant Varchar2(100) := 'Create_Retainage_Distributions';
84
85 BEGIN
86
87 Print (l_api_name,'Create_Retainage_Distributions (+)');
88
89 -----------------------------------------------------------------
90 l_debug_info := 'Step 1: Fetch Invoice Details';
91 Print (l_api_name, l_debug_info);
92 -----------------------------------------------------------------
93
94 Open c_invoice_info (x_invoice_id, x_invoice_line_number);
95 Fetch c_invoice_info
96 Into g_invoice_info;
97 Close c_invoice_info;
98
99 g_user_id := fnd_profile.value('USER_ID');
100 g_login_id := fnd_profile.value('LOGIN_ID');
101
102 If g_invoice_info.invoice_type_lookup_code <> 'PREPAYMENT' And
103 g_invoice_info.retained_amount Is Not Null Then
104
105 -----------------------------------------------------------------
106 l_debug_info := 'Step 2: Insert Retainage Distributions';
107 Print (l_api_name, l_debug_info);
108 -----------------------------------------------------------------
109
110 Insert_Distributions (x_invoice_id => x_invoice_id,
111 x_invoice_line_number => x_invoice_line_number,
112 x_retainage_dist_tab => x_retainage_dist_tab);
113
114 -----------------------------------------------------------------
115 l_debug_info := 'Step 3: Update Payment Schedules';
116 Print (l_api_name, l_debug_info);
117 -----------------------------------------------------------------
118 --Bug 5558693 If Invoice has Net of Retainage = 'N' then the payment
119 -- schedules need to be adjusted with the Retainage Amount.
120 IF nvl(g_invoice_info.net_of_retainage_flag, 'N') = 'N' THEN
121
122 Update_Payment_Schedules (x_invoice_id => x_invoice_id);
123
124 END IF;
125
126 -----------------------------------------------------------------
127 l_debug_info := 'Step 4: Update PO Shipment/Distributions';
128 Print (l_api_name, l_debug_info);
129 -----------------------------------------------------------------
130
131 Update_PO_Shipment_Dists (x_line_location_id => g_invoice_info.po_line_location_id,
132 x_retained_amount => g_invoice_info.retained_amount,
133 x_retainage_dist_tab => x_retainage_dist_tab);
134
135 x_retainage_dist_tab.delete;
136
137 End If;
138
139 Print (l_api_name,'Create_Retainage_Distributions (-)');
140
141 END Create_Retainage_Distributions;
142
143 PROCEDURE Insert_Distributions (x_invoice_id IN NUMBER,
144 x_invoice_line_number IN NUMBER,
145 x_retainage_dist_tab OUT NOCOPY retDistType) AS
146
147 CURSOR c_invoice_distributions (c_invoice_id IN ap_invoices.invoice_id%type,
148 c_invoice_line_number IN ap_invoice_lines.line_number%type,
149 c_max_dist_line_number IN ap_invoice_lines.line_number%type,
150 c_retainage_rate IN number) IS
151 SELECT
152 aid.batch_id,
153 aid.invoice_id,
154 aid.invoice_line_number,
155 aid.invoice_distribution_id invoice_distribution_id,
156 ap_invoice_distributions_s.nextval retainage_distribution_id,
157 aid.distribution_line_number + c_max_dist_line_number retainage_dist_line_number,
158 'RETAINAGE' line_type_lookup_code,
159 aid.description,
160 aid.dist_match_type,
161 aid.distribution_class,
162 aid.org_id,
163 aid.accounting_date,
164 aid.period_name,
165 'N' posted_flag,
166 aid.set_of_books_id,
167 decode(g_invoice_info.minimum_accountable_unit,
168 null, round(aid.amount * c_retainage_rate,
169 g_invoice_info.precision),
170 round((aid.amount * c_retainage_rate)
171 / g_invoice_info.minimum_accountable_unit)
172 * g_invoice_info.minimum_accountable_unit) amount,
173 decode(g_invoice_info.minimum_accountable_unit,
174 null, round((aid.amount * c_retainage_rate * g_invoice_info.exchange_rate),
175 g_invoice_info.precision),
176 round((aid.amount * c_retainage_rate * g_invoice_info.exchange_rate)
177 / g_invoice_info.minimum_accountable_unit)
178 * g_invoice_info.minimum_accountable_unit) base_amount,
179 aid.match_status_flag,
180 aid.ussgl_transaction_code,
181 aid.ussgl_trx_code_context,
182 aid.po_distribution_id,
183 aid.rcv_transaction_id,
184 aid.unit_price,
185 aid.matched_uom_lookup_code,
186 aid.quantity_invoiced,
187 aid.final_match_flag,
188 aid.related_id,
189 aid.assets_addition_flag,
190 aid.project_id,
191 aid.task_id,
192 aid.expenditure_type,
193 aid.expenditure_item_date,
194 aid.expenditure_organization_id,
195 aid.pa_quantity,
196 'R' pa_addition_flag, -- Bug 5388196
197 aid.pa_cc_ar_invoice_id,
198 aid.pa_cc_ar_invoice_line_num,
199 aid.pa_cc_processed_code,
200 aid.award_id,
201 aid.gms_burdenable_raw_cost,
202 aid.awt_flag,
203 aid.awt_group_id,
204 aid.awt_tax_rate_id,
205 aid.awt_gross_amount,
206 aid.awt_invoice_id,
207 aid.awt_origin_group_id,
208 aid.awt_invoice_payment_id,
209 aid.awt_withheld_amt,
210 aid.inventory_transfer_status,
211 aid.reference_1,
212 aid.reference_2,
213 aid.receipt_verified_flag,
214 aid.receipt_required_flag,
215 aid.receipt_missing_flag,
216 aid.justification,
217 aid.expense_group,
218 aid.start_expense_date,
219 aid.end_expense_date,
220 aid.receipt_currency_code,
221 aid.receipt_conversion_rate,
222 aid.receipt_currency_amount,
223 aid.attribute_category,
224 aid.attribute1,
225 aid.attribute2,
226 aid.attribute3,
227 aid.attribute4,
228 aid.attribute5,
229 aid.attribute6,
230 aid.attribute7,
231 aid.attribute8,
232 aid.attribute9,
233 aid.attribute10,
234 aid.attribute11,
235 aid.attribute12,
236 aid.attribute13,
237 aid.attribute14,
238 aid.attribute15,
239 aid.global_attribute_category,
240 aid.global_attribute1,
241 aid.global_attribute2,
242 aid.global_attribute3,
243 aid.global_attribute4,
244 aid.global_attribute5,
245 aid.global_attribute6,
246 aid.global_attribute7,
247 aid.global_attribute8,
248 aid.global_attribute9,
249 aid.global_attribute10,
250 aid.global_attribute11,
251 aid.global_attribute12,
252 aid.global_attribute13,
253 aid.global_attribute14,
254 aid.global_attribute15,
255 aid.global_attribute16,
256 aid.global_attribute17,
257 aid.global_attribute18,
258 aid.global_attribute19,
259 aid.global_attribute20,
260 aid.intended_use,
261 aid.pay_awt_group_id /*bug13090879 */
262 FROM ap_invoice_lines ail,
263 ap_invoice_distributions aid
264 WHERE ail.invoice_id = aid.invoice_id
265 AND ail.line_number = aid.invoice_line_number
266 AND ail.invoice_id = c_invoice_id
267 AND ail.line_number = c_invoice_line_number
268 AND (
269 aid.line_type_lookup_code IN ('ITEM', 'ACCRUAL')
270 or
271 (ail.match_type = 'PRICE_CORRECTION' and
272 aid.line_type_lookup_code = 'IPV')
273 );
274
275 TYPE itemDistType IS TABLE OF c_invoice_distributions%rowtype INDEX BY PLS_INTEGER;
276 TYPE invDistType IS TABLE OF ap_invoice_distributions_all.invoice_distribution_id%type INDEX BY PLS_INTEGER;
277
278 item_dist_tab itemDistType;
279 inv_dist_tab invDistType;
280
281 l_max_dist_line_number ap_invoice_distributions.distribution_line_number%TYPE;
282 l_retainage_rate number;
283
284 l_sum_amount number;
285 l_sum_base_amount number;
286 l_max_dist_amount number;
287 l_rounding_index number;
288
289 l_debug_info Varchar2(240);
290 l_api_name Constant Varchar2(100) := 'Insert_Distributions';
291
292 l_patch_level varchar2(1); --bug13090879
293 l_create_awt_dists_type varchar2(50); --bug13090879
294
295 BEGIN
296
297 Print (l_api_name,'Insert_Distributions (+)');
298
299 -----------------------------------------------------------------
300 l_debug_info := 'Step 1: Initialize cursor parameters and fetch invoice distributions';
301 Print (l_api_name, l_debug_info);
302 -----------------------------------------------------------------
303
304 l_retainage_rate := g_invoice_info.retained_amount / g_invoice_info.line_amount;
305
306 l_max_dist_line_number := AP_INVOICE_LINES_PKG.get_max_dist_line_num(x_invoice_id, x_invoice_line_number);
307
308 -- start bug13090879
309 select
310 substr(patch_level, 8, 1)
311 into
312 l_patch_level
313 from fnd_product_installations
314 where application_id = 200;
315
316 select
317 create_awt_dists_type
318 into
319 l_create_awt_dists_type
320 from ap_system_parameters;
321 --end bug13090879
322
323 OPEN c_invoice_distributions (x_invoice_id,
324 x_invoice_line_number,
325 l_max_dist_line_number,
326 l_retainage_rate);
327 FETCH c_invoice_distributions
328 BULK COLLECT INTO item_dist_tab;
329 CLOSE c_invoice_distributions;
330
331 l_sum_amount := 0;
332 l_sum_base_amount := 0;
333 l_max_dist_amount := 0;
334
335 -----------------------------------------------------------------
336 l_debug_info := 'Step 2: Populate pl/sql table with prorated retainage distributions';
337 Print (l_api_name, l_debug_info);
338 -----------------------------------------------------------------
339
340 IF item_dist_tab.COUNT > 0 THEN
341
342 FOR i IN item_dist_tab.first .. item_dist_tab.last
343 LOOP
344 x_retainage_dist_tab(i).invoice_id := item_dist_tab(i).invoice_id;
345 x_retainage_dist_tab(i).invoice_line_number := item_dist_tab(i).invoice_line_number;
346 x_retainage_dist_tab(i).line_type_lookup_code := item_dist_tab(i).line_type_lookup_code;
347
348 x_retainage_dist_tab(i).invoice_distribution_id := item_dist_tab(i).retainage_distribution_id;
349 x_retainage_dist_tab(i).distribution_line_number := item_dist_tab(i).retainage_dist_line_number;
350 x_retainage_dist_tab(i).dist_code_combination_id := g_invoice_info.retainage_code_combination_id;
351
352 x_retainage_dist_tab(i).amount := item_dist_tab(i).amount;
353 x_retainage_dist_tab(i).base_amount := item_dist_tab(i).base_amount;
354
355 x_retainage_dist_tab(i).description := item_dist_tab(i).description;
356 x_retainage_dist_tab(i).dist_match_type := item_dist_tab(i).dist_match_type;
357 x_retainage_dist_tab(i).distribution_class := item_dist_tab(i).distribution_class;
358
359 x_retainage_dist_tab(i).set_of_books_id := item_dist_tab(i).set_of_books_id;
360 x_retainage_dist_tab(i).org_id := item_dist_tab(i).org_id;
361
362 x_retainage_dist_tab(i).accounting_date := item_dist_tab(i).accounting_date;
363 x_retainage_dist_tab(i).period_name := item_dist_tab(i).period_name;
364 x_retainage_dist_tab(i).posted_flag := item_dist_tab(i).posted_flag;
365
366 x_retainage_dist_tab(i).ussgl_transaction_code := item_dist_tab(i).ussgl_transaction_code;
367 x_retainage_dist_tab(i).ussgl_trx_code_context := item_dist_tab(i).ussgl_trx_code_context;
368
369 x_retainage_dist_tab(i).po_distribution_id := item_dist_tab(i).po_distribution_id;
370 x_retainage_dist_tab(i).rcv_transaction_id := item_dist_tab(i).rcv_transaction_id;
371
372 x_retainage_dist_tab(i).unit_price := item_dist_tab(i).unit_price;
373 x_retainage_dist_tab(i).matched_uom_lookup_code := item_dist_tab(i).matched_uom_lookup_code;
374
375 -- Quantity will not be prorated.
376 -- x_retainage_dist_tab(i).quantity_invoiced := item_dist_tab(i).quantity_invoiced;
377
378 x_retainage_dist_tab(i).match_status_flag := item_dist_tab(i).match_status_flag;
379 x_retainage_dist_tab(i).final_match_flag := item_dist_tab(i).final_match_flag;
380
381 x_retainage_dist_tab(i).related_id := item_dist_tab(i).related_id;
382 x_retainage_dist_tab(i).inventory_transfer_status := item_dist_tab(i).inventory_transfer_status;
383 x_retainage_dist_tab(i).reference_1 := item_dist_tab(i).reference_1;
384 x_retainage_dist_tab(i).reference_2 := item_dist_tab(i).reference_2;
385
386 x_retainage_dist_tab(i).assets_addition_flag := item_dist_tab(i).assets_addition_flag;
387 x_retainage_dist_tab(i).assets_tracking_flag := 'N';
388 x_retainage_dist_tab(i).asset_book_type_code := NULL;
389 x_retainage_dist_tab(i).asset_category_id := NULL;
390
391 x_retainage_dist_tab(i).project_id := item_dist_tab(i).project_id;
392 x_retainage_dist_tab(i).task_id := item_dist_tab(i).task_id;
393 x_retainage_dist_tab(i).expenditure_type := item_dist_tab(i).expenditure_type;
394 x_retainage_dist_tab(i).expenditure_item_date := item_dist_tab(i).expenditure_item_date;
395 x_retainage_dist_tab(i).expenditure_organization_id := item_dist_tab(i).expenditure_organization_id;
396 x_retainage_dist_tab(i).pa_quantity := item_dist_tab(i).pa_quantity;
397 x_retainage_dist_tab(i).pa_addition_flag := item_dist_tab(i).pa_addition_flag;
398 x_retainage_dist_tab(i).pa_cc_ar_invoice_id := item_dist_tab(i).pa_cc_ar_invoice_id;
399 x_retainage_dist_tab(i).pa_cc_ar_invoice_line_num := item_dist_tab(i).pa_cc_ar_invoice_line_num;
400 x_retainage_dist_tab(i).pa_cc_processed_code := item_dist_tab(i).pa_cc_processed_code;
401 x_retainage_dist_tab(i).award_id := item_dist_tab(i).award_id;
402 x_retainage_dist_tab(i).gms_burdenable_raw_cost := item_dist_tab(i).gms_burdenable_raw_cost;
403
404 x_retainage_dist_tab(i).attribute_category := item_dist_tab(i).attribute_category;
405 x_retainage_dist_tab(i).attribute1 := item_dist_tab(i).attribute1;
406 x_retainage_dist_tab(i).attribute2 := item_dist_tab(i).attribute2;
407 x_retainage_dist_tab(i).attribute3 := item_dist_tab(i).attribute3;
408 x_retainage_dist_tab(i).attribute4 := item_dist_tab(i).attribute4;
409 x_retainage_dist_tab(i).attribute5 := item_dist_tab(i).attribute5;
410 x_retainage_dist_tab(i).attribute6 := item_dist_tab(i).attribute6;
411 x_retainage_dist_tab(i).attribute7 := item_dist_tab(i).attribute7;
412 x_retainage_dist_tab(i).attribute8 := item_dist_tab(i).attribute8;
413 x_retainage_dist_tab(i).attribute9 := item_dist_tab(i).attribute9;
414 x_retainage_dist_tab(i).attribute10 := item_dist_tab(i).attribute10;
415 x_retainage_dist_tab(i).attribute11 := item_dist_tab(i).attribute11;
416 x_retainage_dist_tab(i).attribute12 := item_dist_tab(i).attribute12;
417 x_retainage_dist_tab(i).attribute13 := item_dist_tab(i).attribute13;
418 x_retainage_dist_tab(i).attribute14 := item_dist_tab(i).attribute14;
419 x_retainage_dist_tab(i).attribute15 := item_dist_tab(i).attribute15;
420 x_retainage_dist_tab(i).global_attribute_category := item_dist_tab(i).global_attribute_category;
421 x_retainage_dist_tab(i).global_attribute1 := item_dist_tab(i).global_attribute1;
422 x_retainage_dist_tab(i).global_attribute2 := item_dist_tab(i).global_attribute2;
423 x_retainage_dist_tab(i).global_attribute3 := item_dist_tab(i).global_attribute3;
424 x_retainage_dist_tab(i).global_attribute4 := item_dist_tab(i).global_attribute4;
425 x_retainage_dist_tab(i).global_attribute5 := item_dist_tab(i).global_attribute5;
426 x_retainage_dist_tab(i).global_attribute6 := item_dist_tab(i).global_attribute6;
427 x_retainage_dist_tab(i).global_attribute7 := item_dist_tab(i).global_attribute7;
428 x_retainage_dist_tab(i).global_attribute8 := item_dist_tab(i).global_attribute8;
429 x_retainage_dist_tab(i).global_attribute9 := item_dist_tab(i).global_attribute9;
430 x_retainage_dist_tab(i).global_attribute10 := item_dist_tab(i).global_attribute10;
431 x_retainage_dist_tab(i).global_attribute11 := item_dist_tab(i).global_attribute11;
432 x_retainage_dist_tab(i).global_attribute12 := item_dist_tab(i).global_attribute12;
433 x_retainage_dist_tab(i).global_attribute13 := item_dist_tab(i).global_attribute13;
434 x_retainage_dist_tab(i).global_attribute14 := item_dist_tab(i).global_attribute14;
435 x_retainage_dist_tab(i).global_attribute15 := item_dist_tab(i).global_attribute15;
436 x_retainage_dist_tab(i).global_attribute16 := item_dist_tab(i).global_attribute16;
437 x_retainage_dist_tab(i).global_attribute17 := item_dist_tab(i).global_attribute17;
438 x_retainage_dist_tab(i).global_attribute18 := item_dist_tab(i).global_attribute18;
439 x_retainage_dist_tab(i).global_attribute19 := item_dist_tab(i).global_attribute19;
440 x_retainage_dist_tab(i).global_attribute20 := item_dist_tab(i).global_attribute20;
441 x_retainage_dist_tab(i).created_by := g_user_id;
442 x_retainage_dist_tab(i).creation_date := sysdate;
443 x_retainage_dist_tab(i).last_updated_by := g_user_id;
444 x_retainage_dist_tab(i).last_update_date := sysdate;
445 x_retainage_dist_tab(i).last_update_login := g_login_id;
446 x_retainage_dist_tab(i).intended_use := item_dist_tab(i).intended_use;
447 x_retainage_dist_tab(i).related_retainage_dist_id := item_dist_tab(i).invoice_distribution_id;
448 x_retainage_dist_tab(i).retained_amount_remaining := -1 * item_dist_tab(i).amount;
449 x_retainage_dist_tab(i).cash_posted_flag := 'N'; -- bug 16227625
450 x_retainage_dist_tab(i).accrual_posted_flag := 'N'; -- bug 16227625
451
452 -- start bug13090879
453 if l_create_awt_dists_type in ('PAYMENT', 'BOTH') then
454 if l_patch_level = 'A' then
455 x_retainage_dist_tab(i).awt_group_id := item_dist_tab(i).awt_group_id;
456 else
457 x_retainage_dist_tab(i).pay_awt_group_id := item_dist_tab(i).pay_awt_group_id;
458 end if;
459 end if;
460 -- end bug13090879
461
462 IF (item_dist_tab(i).amount >= l_max_dist_amount) THEN
463 l_rounding_index := i;
464 l_max_dist_amount := item_dist_tab(i).amount;
465 END IF;
466
467 l_sum_amount := l_sum_amount + item_dist_tab(i).amount;
468 l_sum_base_amount := l_sum_base_amount + item_dist_tab(i).base_amount;
469
470 inv_dist_tab(i) := item_dist_tab(i).invoice_distribution_id;
471
472 END LOOP;
473
474 -----------------------------------------------------------------
475 l_debug_info := 'Step 3: Perform rounding on prorated retainage distributions';
476 Print (l_api_name, l_debug_info);
477 -----------------------------------------------------------------
478
479 IF l_rounding_index Is Not Null THEN
480
481 IF l_sum_amount <> g_invoice_info.retained_amount THEN
482
483 x_retainage_dist_tab(l_rounding_index).amount := x_retainage_dist_tab(l_rounding_index).amount +
484 (g_invoice_info.retained_amount - l_sum_amount);
485
486 x_retainage_dist_tab(l_rounding_index).retained_amount_remaining
487 := -1 * x_retainage_dist_tab(l_rounding_index).amount;
488 END IF;
489
490 IF g_invoice_info.exchange_rate Is Not Null THEN
491
492 IF l_sum_base_amount <> g_invoice_info.base_retained_amount THEN
493
494 x_retainage_dist_tab(l_rounding_index).rounding_amt := g_invoice_info.base_retained_amount - l_sum_base_amount;
495
496 x_retainage_dist_tab(l_rounding_index).base_amount := x_retainage_dist_tab(l_rounding_index).base_amount +
497 x_retainage_dist_tab(l_rounding_index).rounding_amt;
498 END IF;
499 END IF;
500 END IF;
501
502 -----------------------------------------------------------------
503 l_debug_info := 'Step 4: Insert Retainage Distributions';
504 Print (l_api_name, l_debug_info);
505 -----------------------------------------------------------------
506
507 FORALL j in x_retainage_dist_tab.first .. x_retainage_dist_tab.last
508
509 INSERT INTO ap_invoice_distributions VALUES x_retainage_dist_tab(j);
510
511 -----------------------------------------------------------------
512 l_debug_info := 'Step 5: Update related_retainage_dist_id on parent distributions';
513 Print (l_api_name, l_debug_info);
514 -----------------------------------------------------------------
515
516 FORALL k in inv_dist_tab.first .. inv_dist_tab.last
517
518 UPDATE ap_invoice_distributions_all
519 SET related_retainage_dist_id = inv_dist_tab(k)
520 WHERE invoice_distribution_id = inv_dist_tab(k);
521
522 -----------------------------------------------------------------
523 l_debug_info := 'Step 6: Clear the local pl/sql tables';
524 Print (l_api_name, l_debug_info);
525 -----------------------------------------------------------------
526
527 inv_dist_tab.delete;
528 item_dist_tab.delete;
529
530 END IF;
531
532 Print (l_api_name, 'Insert_Distributions (-)');
533
534 END Insert_Distributions;
535
536 PROCEDURE Update_Payment_Schedules (x_invoice_id IN ap_invoices.invoice_id%type) AS
537
538 CURSOR c_payment_schedules (c_invoice_id IN ap_invoices.invoice_id%type) IS
539 SELECT *
540 FROM ap_payment_schedules_all
541 WHERE invoice_id = c_invoice_id
542 FOR UPDATE OF amount_remaining;
543
544 TYPE payNum IS TABLE OF ap_payment_schedules_all.payment_num%type INDEX BY PLS_INTEGER;
545 TYPE paySchedUpd IS TABLE OF ap_payment_schedules_all%rowtype INDEX BY PLS_INTEGER;
546
547 pay_num_tab payNum;
548 pay_sched_upd_tab paySchedUpd;
549
550 l_inv_curr_gross_amount ap_payment_schedules_all.inv_curr_gross_amount%type;
551 l_invoice_amount ap_invoices_all.invoice_amount%type
552 := g_invoice_info.invoice_amount;
553 l_retained_amount ap_invoice_lines_all.retained_amount%type
554 := g_invoice_info.retained_amount;
555 l_invoice_currency_code ap_invoices_all.invoice_currency_code%type
556 := g_invoice_info.invoice_currency_code;
557 l_payment_currency_code ap_invoices_all.payment_currency_code%type
558 := g_invoice_info.payment_currency_code;
559 l_payment_cross_rate ap_invoices_all.payment_cross_rate%type
560 := g_invoice_info.payment_cross_rate;
561 l_amt_applicable_to_discount ap_invoices_all.amount_applicable_to_discount%type
562 := g_invoice_info.amount_applicable_to_discount;
563
564 l_amt_to_subtract NUMBER;
565 l_amt_to_subtract_pay_curr NUMBER;
566 l_disc_amt_factor NUMBER;
567 l_sum_amt_to_subtract NUMBER := 0;
568 l_last_schedule NUMBER;
569
570 l_debug_info Varchar2(2000);
571 l_api_name Constant Varchar2(100)
572 := 'Update_Payment_Schedules';
573
574 BEGIN
575
576 Print (l_api_name, 'Update_Payment_Schedules (+)');
577
578 OPEN c_payment_schedules(x_invoice_id);
579 FETCH c_payment_schedules
580 BULK COLLECT INTO pay_sched_upd_tab;
581 CLOSE c_payment_schedules;
582
583 If pay_sched_upd_tab.COUNT > 0 Then
584
585 -----------------------------------------------------------------
586 l_debug_info := 'Step 1: Fetch Payment Schedules';
587 Print (l_api_name, l_debug_info);
588 -----------------------------------------------------------------
589
590 For i IN pay_sched_upd_tab.first .. pay_sched_upd_tab.last
591 Loop
592 If (l_invoice_amount = 0) Then
593
594 l_amt_to_subtract := 0;
595 l_disc_amt_factor := 0;
596
597 Else
598
599 l_inv_curr_gross_amount := nvl(pay_sched_upd_tab(i).inv_curr_gross_amount, pay_sched_upd_tab(i).gross_amount);
600
601 l_amt_to_subtract := l_retained_amount *
602 (l_inv_curr_gross_amount / l_invoice_amount);
603
604 l_amt_to_subtract := ap_utilities_pkg.ap_round_currency
605 (l_amt_to_subtract, l_invoice_currency_code);
606
607 l_amt_to_subtract_pay_curr := ap_utilities_pkg.ap_round_currency
608 (l_amt_to_subtract * l_payment_cross_rate, l_payment_currency_code);
609
610 l_disc_amt_factor := l_retained_amount /
611 nvl(l_amt_applicable_to_discount, l_invoice_amount);
612
613 pay_sched_upd_tab(i).gross_amount
614 := pay_sched_upd_tab(i).gross_amount +
615 l_amt_to_subtract_pay_curr;
616
617 pay_sched_upd_tab(i).inv_curr_gross_amount := pay_sched_upd_tab(i).gross_amount;
618
619 pay_sched_upd_tab(i).amount_remaining
620 := pay_sched_upd_tab(i).amount_remaining +
621 l_amt_to_subtract_pay_curr;
622
623 pay_sched_upd_tab(i).discount_amount_available
624 := pay_sched_upd_tab(i).discount_amount_available +
625 ap_utilities_pkg.ap_round_currency
626 (pay_sched_upd_tab(i).discount_amount_available * l_disc_amt_factor,
627 l_payment_currency_code);
628
629 pay_sched_upd_tab(i).second_disc_amt_available
630 := pay_sched_upd_tab(i).second_disc_amt_available +
631 ap_utilities_pkg.ap_round_currency
632 (pay_sched_upd_tab(i).second_disc_amt_available * l_disc_amt_factor,
633 l_payment_currency_code);
634
635 pay_sched_upd_tab(i).third_disc_amt_available
636 := pay_sched_upd_tab(i).third_disc_amt_available +
637 ap_utilities_pkg.ap_round_currency
638 (pay_sched_upd_tab(i).third_disc_amt_available * l_disc_amt_factor,
639 l_payment_currency_code);
640
641 l_sum_amt_to_subtract := l_sum_amt_to_subtract + l_amt_to_subtract;
642
643 pay_num_tab(i) := pay_sched_upd_tab(i).payment_num;
644
645 --bug 8991699
646 -----------------------------------------------------------------
647 l_debug_info := 'Step 1.1: After fetching each record: l_inv_curr_gross_amount->'||l_inv_curr_gross_amount||
648 'l_amt_to_subtract->'||l_amt_to_subtract||'l_amt_to_subtract_pay_curr->'||l_amt_to_subtract_pay_curr||
649 'pay_sched_upd_tab(i).inv_curr_gross_amount->'||pay_sched_upd_tab(i).inv_curr_gross_amount||
650 'pay_sched_upd_tab(i).amount_remaining->'||pay_sched_upd_tab(i).amount_remaining;
651 Print (l_api_name, l_debug_info);
652 -----------------------------------------------------------------
653
654 End If;
655 End Loop;
656
657 -- To keep it consistent with the withholding logic, rounding
658 -- due to proration is applied to the last payment schedule.
659 --bug 8991699
660 -----------------------------------------------------------------
661 l_debug_info := 'Step 2: Perform rounding on payment schedules->l_retained_amount'||l_retained_amount||
662 'l_sum_amt_to_subtract->'||l_sum_amt_to_subtract;
663 Print (l_api_name, l_debug_info);
664 -----------------------------------------------------------------
665
666 If l_retained_amount <> l_sum_amt_to_subtract Then
667
668 l_last_schedule := pay_sched_upd_tab.last;
669
670 --Changed '-' to '+' in the below statement for the bug #8795837
671
672 pay_sched_upd_tab(l_last_schedule).gross_amount
673 := pay_sched_upd_tab(l_last_schedule).gross_amount
674 + (l_retained_amount - l_sum_amt_to_subtract);
675
676 pay_sched_upd_tab(l_last_schedule).inv_curr_gross_amount
677 := pay_sched_upd_tab(l_last_schedule).gross_amount;
678
679 --Changed '-' to '+' in the below statement for the bug #8849377
680
681 pay_sched_upd_tab(l_last_schedule).amount_remaining
682 := pay_sched_upd_tab(l_last_schedule).amount_remaining
683 + (l_retained_amount - l_sum_amt_to_subtract);
684 End If;
685
686 -----------------------------------------------------------------
687 l_debug_info := 'Step 3: Bulk Update Payment Schedules';
688 Print (l_api_name, l_debug_info);
689 -----------------------------------------------------------------
690
691 FORALL i in pay_sched_upd_tab.first .. pay_sched_upd_tab.last
692
693 UPDATE ap_payment_schedules_all
694 SET ROW = pay_sched_upd_tab(i)
695 WHERE invoice_id = x_invoice_id
696 AND payment_num = pay_num_tab(i);
697
698 /*
699 -----------------------------------------------------------------
700 l_debug_info := 'Step 4: Update amount_applicable_to_discount';
701 Print (l_api_name, l_debug_info);
702 -----------------------------------------------------------------
703
704 UPDATE ap_invoices_all
705 SET amount_applicable_to_discount = (amount_applicable_to_discount + l_retained_amount)
706 WHERE invoice_id = x_invoice_id;
707 */
708 pay_num_tab.delete;
709 pay_sched_upd_tab.delete;
710
711 End If;
712
713 --bug 8991699 begin
714 ---------------------------------------------------------------------------------------------------------
715 l_debug_info := 'Step 4: Update invoice header amount_applicable_to_discount and pay_curr_invoice_amount';
716 Print (l_api_name, l_debug_info);
717 ----------------------------------------------------------------------------------------------------------
718
719 IF abs(l_retained_amount) > 0 then
720 UPDATE ap_invoices_all
721 SET amount_applicable_to_discount = (amount_applicable_to_discount + l_retained_amount),
722 pay_curr_invoice_amount = (pay_curr_invoice_amount + l_retained_amount),
723 validated_tax_amount = NVL(validated_tax_amount,0)+abs(l_retained_amount) /* Bug#14582311*/
724 WHERE invoice_id = x_invoice_id;
725
726 ENd IF;
727
728 --bug 8991699 end
729
730 Print (l_api_name, 'Update_Payment_Schedules(-)');
731
732 END Update_Payment_Schedules;
733
734 Procedure Update_PO_Shipment_Dists
735 (x_line_location_id IN ap_invoice_lines_all.po_line_location_id%type,
736 x_retained_amount IN ap_invoice_lines_all.retained_amount%type,
737 x_retainage_dist_tab IN retDistType) AS
738
739 l_po_ap_dist_rec PO_AP_DIST_REC_TYPE;
740 l_po_ap_line_loc_rec PO_AP_LINE_LOC_REC_TYPE;
741
742 l_return_status VARCHAR2(100);
743 l_msg_data VARCHAR2(4000);
744
745 l_api_name VARCHAR2(50);
746 l_debug_info VARCHAR2(2000);
747
748 BEGIN
749
750 l_api_name := 'Update_PO_Shipment_Dists';
751
752 l_po_ap_dist_rec := PO_AP_DIST_REC_TYPE.create_object();
753
754 l_po_ap_line_loc_rec := PO_AP_LINE_LOC_REC_TYPE.create_object(
755 p_po_line_location_id => x_line_location_id,
756 p_uom_code => NULL,
757 p_quantity_billed => NULL,
758 p_amount_billed => NULL,
759 p_quantity_financed => NULL,
760 p_amount_financed => NULL,
761 p_quantity_recouped => NULL,
762 p_amount_recouped => NULL,
763 p_retainage_withheld_amt => -x_retained_amount,
764 p_retainage_released_amt => NULL
765 );
766
767 FOR i in nvl(x_retainage_dist_tab.first,0)..nvl(x_retainage_dist_tab.last,0)
768 LOOP
769
770 IF (x_retainage_dist_tab.exists(i)) THEN
771
772 l_po_ap_dist_rec.add_change
773 (p_po_distribution_id => x_retainage_dist_tab(i).po_distribution_id,
774 p_uom_code => NULL,
775 p_quantity_billed => NULL,
776 p_amount_billed => NULL,
777 p_quantity_financed => NULL,
778 p_amount_financed => NULL,
779 p_quantity_recouped => NULL,
780 p_amount_recouped => NULL,
781 p_retainage_withheld_amt => -x_retainage_dist_tab(i).amount,
782 p_retainage_released_amt => NULL);
783
784
785 END IF;
786
787 END LOOP;
788
789 PO_AP_INVOICE_MATCH_GRP.Update_Document_Ap_Values(
790 P_Api_Version => 1.0,
791 P_Line_Loc_Changes_Rec => l_po_ap_line_loc_rec,
792 P_Dist_Changes_Rec => l_po_ap_dist_rec,
793 X_Return_Status => l_return_status,
794 X_Msg_Data => l_msg_data);
795 END Update_PO_Shipment_Dists;
796
797 Procedure Print (x_api_name IN Varchar2, x_debug_info IN Varchar2) As
798 Begin
799 IF (g_level_procedure >= g_current_runtime_level) THEN
800 fnd_log.string(g_level_procedure,g_module_name||x_api_name,x_debug_info);
801 END IF;
802 End Print;
803
804 END AP_RETAINAGE_PKG;