[Home] [Help]
PACKAGE BODY: APPS.AP_RETAINAGE_PKG
Source
1 PACKAGE BODY AP_RETAINAGE_PKG AS
2 /* $Header: apcwrtnb.pls 120.6 2006/10/13 18:46:59 dgulraja noship $ */
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 Update_Payment_Schedules (x_invoice_id => x_invoice_id);
122 END IF;
123
124 -----------------------------------------------------------------
125 l_debug_info := 'Step 4: Update PO Shipment/Distributions';
126 Print (l_api_name, l_debug_info);
127 -----------------------------------------------------------------
128
129 Update_PO_Shipment_Dists (x_line_location_id => g_invoice_info.po_line_location_id,
130 x_retained_amount => g_invoice_info.retained_amount,
131 x_retainage_dist_tab => x_retainage_dist_tab);
132
133 x_retainage_dist_tab.delete;
134
135 End If;
136
137 Print (l_api_name,'Create_Retainage_Distributions (-)');
138
139 END Create_Retainage_Distributions;
140
141 PROCEDURE Insert_Distributions (x_invoice_id IN NUMBER,
142 x_invoice_line_number IN NUMBER,
143 x_retainage_dist_tab OUT NOCOPY retDistType) AS
144
145 CURSOR c_invoice_distributions (c_invoice_id IN ap_invoices.invoice_id%type,
146 c_invoice_line_number IN ap_invoice_lines.line_number%type,
147 c_max_dist_line_number IN ap_invoice_lines.line_number%type,
148 c_retainage_rate IN number) IS
149 SELECT
150 aid.batch_id,
151 aid.invoice_id,
152 aid.invoice_line_number,
153 aid.invoice_distribution_id invoice_distribution_id,
154 ap_invoice_distributions_s.nextval retainage_distribution_id,
155 aid.distribution_line_number + c_max_dist_line_number retainage_dist_line_number,
156 'RETAINAGE' line_type_lookup_code,
157 aid.description,
158 aid.dist_match_type,
159 aid.distribution_class,
160 aid.org_id,
161 aid.accounting_date,
162 aid.period_name,
163 'N' posted_flag,
164 aid.set_of_books_id,
165 decode(g_invoice_info.minimum_accountable_unit,
166 null, round(aid.amount * c_retainage_rate,
167 g_invoice_info.precision),
168 round((aid.amount * c_retainage_rate)
169 / g_invoice_info.minimum_accountable_unit)
170 * g_invoice_info.minimum_accountable_unit) amount,
171 decode(g_invoice_info.minimum_accountable_unit,
172 null, round((aid.amount * c_retainage_rate * g_invoice_info.exchange_rate),
173 g_invoice_info.precision),
174 round((aid.amount * c_retainage_rate * g_invoice_info.exchange_rate)
175 / g_invoice_info.minimum_accountable_unit)
176 * g_invoice_info.minimum_accountable_unit) base_amount,
177 aid.match_status_flag,
178 aid.ussgl_transaction_code,
179 aid.ussgl_trx_code_context,
180 aid.po_distribution_id,
181 aid.rcv_transaction_id,
182 aid.unit_price,
183 aid.matched_uom_lookup_code,
184 aid.quantity_invoiced,
185 aid.final_match_flag,
186 aid.related_id,
187 aid.assets_addition_flag,
188 aid.project_id,
189 aid.task_id,
190 aid.expenditure_type,
191 aid.expenditure_item_date,
192 aid.expenditure_organization_id,
193 aid.pa_quantity,
194 'R' pa_addition_flag, -- Bug 5388196
195 aid.pa_cc_ar_invoice_id,
196 aid.pa_cc_ar_invoice_line_num,
197 aid.pa_cc_processed_code,
198 aid.award_id,
199 aid.gms_burdenable_raw_cost,
200 aid.awt_flag,
201 aid.awt_group_id,
202 aid.awt_tax_rate_id,
203 aid.awt_gross_amount,
204 aid.awt_invoice_id,
205 aid.awt_origin_group_id,
206 aid.awt_invoice_payment_id,
207 aid.awt_withheld_amt,
208 aid.inventory_transfer_status,
209 aid.reference_1,
210 aid.reference_2,
211 aid.receipt_verified_flag,
212 aid.receipt_required_flag,
213 aid.receipt_missing_flag,
214 aid.justification,
215 aid.expense_group,
216 aid.start_expense_date,
217 aid.end_expense_date,
218 aid.receipt_currency_code,
219 aid.receipt_conversion_rate,
220 aid.receipt_currency_amount,
221 aid.attribute_category,
222 aid.attribute1,
223 aid.attribute2,
224 aid.attribute3,
225 aid.attribute4,
226 aid.attribute5,
227 aid.attribute6,
228 aid.attribute7,
229 aid.attribute8,
230 aid.attribute9,
231 aid.attribute10,
232 aid.attribute11,
233 aid.attribute12,
234 aid.attribute13,
235 aid.attribute14,
236 aid.attribute15,
237 aid.global_attribute_category,
238 aid.global_attribute1,
239 aid.global_attribute2,
240 aid.global_attribute3,
241 aid.global_attribute4,
242 aid.global_attribute5,
243 aid.global_attribute6,
244 aid.global_attribute7,
245 aid.global_attribute8,
246 aid.global_attribute9,
247 aid.global_attribute10,
248 aid.global_attribute11,
249 aid.global_attribute12,
250 aid.global_attribute13,
251 aid.global_attribute14,
252 aid.global_attribute15,
253 aid.global_attribute16,
254 aid.global_attribute17,
255 aid.global_attribute18,
256 aid.global_attribute19,
257 aid.global_attribute20,
258 aid.intended_use
259 FROM ap_invoice_lines ail,
260 ap_invoice_distributions aid
261 WHERE ail.invoice_id = aid.invoice_id
262 AND ail.line_number = aid.invoice_line_number
263 AND ail.invoice_id = c_invoice_id
264 AND ail.line_number = c_invoice_line_number
265 AND (
266 aid.line_type_lookup_code IN ('ITEM', 'ACCRUAL')
267 or
268 (ail.match_type = 'PRICE_CORRECTION' and
269 aid.line_type_lookup_code = 'IPV')
270 );
271
272 TYPE itemDistType IS TABLE OF c_invoice_distributions%rowtype INDEX BY PLS_INTEGER;
273 TYPE invDistType IS TABLE OF ap_invoice_distributions_all.invoice_distribution_id%type INDEX BY PLS_INTEGER;
274
275 item_dist_tab itemDistType;
276 inv_dist_tab invDistType;
277
278 l_max_dist_line_number ap_invoice_distributions.distribution_line_number%TYPE;
279 l_retainage_rate number;
280
281 l_sum_amount number;
282 l_sum_base_amount number;
283 l_max_dist_amount number;
284 l_rounding_index number;
285
286 l_debug_info Varchar2(240);
287 l_api_name Constant Varchar2(100) := 'Insert_Distributions';
288
289 BEGIN
290
291 Print (l_api_name,'Insert_Distributions (+)');
292
293 -----------------------------------------------------------------
294 l_debug_info := 'Step 1: Initialize cursor parameters and fetch invoice distributions';
295 Print (l_api_name, l_debug_info);
296 -----------------------------------------------------------------
297
298 l_retainage_rate := g_invoice_info.retained_amount / g_invoice_info.line_amount;
299
300 l_max_dist_line_number := AP_INVOICE_LINES_PKG.get_max_dist_line_num(x_invoice_id, x_invoice_line_number);
301
302 OPEN c_invoice_distributions (x_invoice_id,
303 x_invoice_line_number,
304 l_max_dist_line_number,
305 l_retainage_rate);
306 FETCH c_invoice_distributions
307 BULK COLLECT INTO item_dist_tab;
308 CLOSE c_invoice_distributions;
309
310 l_sum_amount := 0;
311 l_sum_base_amount := 0;
312 l_max_dist_amount := 0;
313
314 -----------------------------------------------------------------
315 l_debug_info := 'Step 2: Populate pl/sql table with prorated retainage distributions';
316 Print (l_api_name, l_debug_info);
317 -----------------------------------------------------------------
318
319 IF item_dist_tab.COUNT > 0 THEN
320
321 FOR i IN item_dist_tab.first .. item_dist_tab.last
322 LOOP
323 x_retainage_dist_tab(i).invoice_id := item_dist_tab(i).invoice_id;
324 x_retainage_dist_tab(i).invoice_line_number := item_dist_tab(i).invoice_line_number;
325 x_retainage_dist_tab(i).line_type_lookup_code := item_dist_tab(i).line_type_lookup_code;
326
327 x_retainage_dist_tab(i).invoice_distribution_id := item_dist_tab(i).retainage_distribution_id;
328 x_retainage_dist_tab(i).distribution_line_number := item_dist_tab(i).retainage_dist_line_number;
329 x_retainage_dist_tab(i).dist_code_combination_id := g_invoice_info.retainage_code_combination_id;
330
331 x_retainage_dist_tab(i).amount := item_dist_tab(i).amount;
332 x_retainage_dist_tab(i).base_amount := item_dist_tab(i).base_amount;
333
334 x_retainage_dist_tab(i).description := item_dist_tab(i).description;
335 x_retainage_dist_tab(i).dist_match_type := item_dist_tab(i).dist_match_type;
336 x_retainage_dist_tab(i).distribution_class := item_dist_tab(i).distribution_class;
337
338 x_retainage_dist_tab(i).set_of_books_id := item_dist_tab(i).set_of_books_id;
339 x_retainage_dist_tab(i).org_id := item_dist_tab(i).org_id;
340
341 x_retainage_dist_tab(i).accounting_date := item_dist_tab(i).accounting_date;
342 x_retainage_dist_tab(i).period_name := item_dist_tab(i).period_name;
343 x_retainage_dist_tab(i).posted_flag := item_dist_tab(i).posted_flag;
344
345 x_retainage_dist_tab(i).ussgl_transaction_code := item_dist_tab(i).ussgl_transaction_code;
346 x_retainage_dist_tab(i).ussgl_trx_code_context := item_dist_tab(i).ussgl_trx_code_context;
347
348 x_retainage_dist_tab(i).po_distribution_id := item_dist_tab(i).po_distribution_id;
349 x_retainage_dist_tab(i).rcv_transaction_id := item_dist_tab(i).rcv_transaction_id;
350
351 x_retainage_dist_tab(i).unit_price := item_dist_tab(i).unit_price;
352 x_retainage_dist_tab(i).matched_uom_lookup_code := item_dist_tab(i).matched_uom_lookup_code;
353
354 -- Quantity will not be prorated.
355 -- x_retainage_dist_tab(i).quantity_invoiced := item_dist_tab(i).quantity_invoiced;
356
357 x_retainage_dist_tab(i).match_status_flag := item_dist_tab(i).match_status_flag;
358 x_retainage_dist_tab(i).final_match_flag := item_dist_tab(i).final_match_flag;
359
360 x_retainage_dist_tab(i).related_id := item_dist_tab(i).related_id;
361 x_retainage_dist_tab(i).inventory_transfer_status := item_dist_tab(i).inventory_transfer_status;
362 x_retainage_dist_tab(i).reference_1 := item_dist_tab(i).reference_1;
363 x_retainage_dist_tab(i).reference_2 := item_dist_tab(i).reference_2;
364
365 x_retainage_dist_tab(i).assets_addition_flag := item_dist_tab(i).assets_addition_flag;
366 x_retainage_dist_tab(i).assets_tracking_flag := 'N';
367 x_retainage_dist_tab(i).asset_book_type_code := NULL;
368 x_retainage_dist_tab(i).asset_category_id := NULL;
369
370 x_retainage_dist_tab(i).project_id := item_dist_tab(i).project_id;
371 x_retainage_dist_tab(i).task_id := item_dist_tab(i).task_id;
372 x_retainage_dist_tab(i).expenditure_type := item_dist_tab(i).expenditure_type;
373 x_retainage_dist_tab(i).expenditure_item_date := item_dist_tab(i).expenditure_item_date;
374 x_retainage_dist_tab(i).expenditure_organization_id := item_dist_tab(i).expenditure_organization_id;
375 x_retainage_dist_tab(i).pa_quantity := item_dist_tab(i).pa_quantity;
376 x_retainage_dist_tab(i).pa_addition_flag := item_dist_tab(i).pa_addition_flag;
377 x_retainage_dist_tab(i).pa_cc_ar_invoice_id := item_dist_tab(i).pa_cc_ar_invoice_id;
378 x_retainage_dist_tab(i).pa_cc_ar_invoice_line_num := item_dist_tab(i).pa_cc_ar_invoice_line_num;
379 x_retainage_dist_tab(i).pa_cc_processed_code := item_dist_tab(i).pa_cc_processed_code;
380 x_retainage_dist_tab(i).award_id := item_dist_tab(i).award_id;
381 x_retainage_dist_tab(i).gms_burdenable_raw_cost := item_dist_tab(i).gms_burdenable_raw_cost;
382
383 x_retainage_dist_tab(i).attribute_category := item_dist_tab(i).attribute_category;
384 x_retainage_dist_tab(i).attribute1 := item_dist_tab(i).attribute1;
385 x_retainage_dist_tab(i).attribute2 := item_dist_tab(i).attribute2;
386 x_retainage_dist_tab(i).attribute3 := item_dist_tab(i).attribute3;
387 x_retainage_dist_tab(i).attribute4 := item_dist_tab(i).attribute4;
388 x_retainage_dist_tab(i).attribute5 := item_dist_tab(i).attribute5;
389 x_retainage_dist_tab(i).attribute6 := item_dist_tab(i).attribute6;
390 x_retainage_dist_tab(i).attribute7 := item_dist_tab(i).attribute7;
391 x_retainage_dist_tab(i).attribute8 := item_dist_tab(i).attribute8;
392 x_retainage_dist_tab(i).attribute9 := item_dist_tab(i).attribute9;
393 x_retainage_dist_tab(i).attribute10 := item_dist_tab(i).attribute10;
394 x_retainage_dist_tab(i).attribute11 := item_dist_tab(i).attribute11;
395 x_retainage_dist_tab(i).attribute12 := item_dist_tab(i).attribute12;
396 x_retainage_dist_tab(i).attribute13 := item_dist_tab(i).attribute13;
397 x_retainage_dist_tab(i).attribute14 := item_dist_tab(i).attribute14;
398 x_retainage_dist_tab(i).attribute15 := item_dist_tab(i).attribute15;
399 x_retainage_dist_tab(i).global_attribute_category := item_dist_tab(i).global_attribute_category;
400 x_retainage_dist_tab(i).global_attribute1 := item_dist_tab(i).global_attribute1;
401 x_retainage_dist_tab(i).global_attribute2 := item_dist_tab(i).global_attribute2;
402 x_retainage_dist_tab(i).global_attribute3 := item_dist_tab(i).global_attribute3;
403 x_retainage_dist_tab(i).global_attribute4 := item_dist_tab(i).global_attribute4;
404 x_retainage_dist_tab(i).global_attribute5 := item_dist_tab(i).global_attribute5;
405 x_retainage_dist_tab(i).global_attribute6 := item_dist_tab(i).global_attribute6;
406 x_retainage_dist_tab(i).global_attribute7 := item_dist_tab(i).global_attribute7;
407 x_retainage_dist_tab(i).global_attribute8 := item_dist_tab(i).global_attribute8;
408 x_retainage_dist_tab(i).global_attribute9 := item_dist_tab(i).global_attribute9;
409 x_retainage_dist_tab(i).global_attribute10 := item_dist_tab(i).global_attribute10;
410 x_retainage_dist_tab(i).global_attribute11 := item_dist_tab(i).global_attribute11;
411 x_retainage_dist_tab(i).global_attribute12 := item_dist_tab(i).global_attribute12;
412 x_retainage_dist_tab(i).global_attribute13 := item_dist_tab(i).global_attribute13;
413 x_retainage_dist_tab(i).global_attribute14 := item_dist_tab(i).global_attribute14;
414 x_retainage_dist_tab(i).global_attribute15 := item_dist_tab(i).global_attribute15;
415 x_retainage_dist_tab(i).global_attribute16 := item_dist_tab(i).global_attribute16;
416 x_retainage_dist_tab(i).global_attribute17 := item_dist_tab(i).global_attribute17;
417 x_retainage_dist_tab(i).global_attribute18 := item_dist_tab(i).global_attribute18;
418 x_retainage_dist_tab(i).global_attribute19 := item_dist_tab(i).global_attribute19;
419 x_retainage_dist_tab(i).global_attribute20 := item_dist_tab(i).global_attribute20;
420
421 x_retainage_dist_tab(i).created_by := g_user_id;
422 x_retainage_dist_tab(i).creation_date := sysdate;
423 x_retainage_dist_tab(i).last_updated_by := g_user_id;
424 x_retainage_dist_tab(i).last_update_date := sysdate;
425 x_retainage_dist_tab(i).last_update_login := g_login_id;
426
427 x_retainage_dist_tab(i).intended_use := item_dist_tab(i).intended_use;
428
429 x_retainage_dist_tab(i).related_retainage_dist_id := item_dist_tab(i).invoice_distribution_id;
430 x_retainage_dist_tab(i).retained_amount_remaining := -1 * item_dist_tab(i).amount;
431
432 IF (item_dist_tab(i).amount >= l_max_dist_amount) THEN
433 l_rounding_index := i;
434 l_max_dist_amount := item_dist_tab(i).amount;
435 END IF;
436
437 l_sum_amount := l_sum_amount + item_dist_tab(i).amount;
438 l_sum_base_amount := l_sum_base_amount + item_dist_tab(i).base_amount;
439
440 inv_dist_tab(i) := item_dist_tab(i).invoice_distribution_id;
441
442 END LOOP;
443
444 -----------------------------------------------------------------
445 l_debug_info := 'Step 3: Perform rounding on prorated retainage distributions';
446 Print (l_api_name, l_debug_info);
447 -----------------------------------------------------------------
448
449 IF l_rounding_index Is Not Null THEN
450
451 IF l_sum_amount <> g_invoice_info.retained_amount THEN
452
453 x_retainage_dist_tab(l_rounding_index).amount := x_retainage_dist_tab(l_rounding_index).amount +
454 (g_invoice_info.retained_amount - l_sum_amount);
455
456 x_retainage_dist_tab(l_rounding_index).retained_amount_remaining
457 := -1 * x_retainage_dist_tab(l_rounding_index).amount;
458 END IF;
459
460 IF g_invoice_info.exchange_rate Is Not Null THEN
461
462 IF l_sum_base_amount <> g_invoice_info.base_retained_amount THEN
463
464 x_retainage_dist_tab(l_rounding_index).rounding_amt := g_invoice_info.base_retained_amount - l_sum_base_amount;
465
466 x_retainage_dist_tab(l_rounding_index).base_amount := x_retainage_dist_tab(l_rounding_index).base_amount +
467 x_retainage_dist_tab(l_rounding_index).rounding_amt;
468 END IF;
469 END IF;
470 END IF;
471
472 -----------------------------------------------------------------
473 l_debug_info := 'Step 4: Insert Retainage Distributions';
474 Print (l_api_name, l_debug_info);
475 -----------------------------------------------------------------
476
477 FORALL j in x_retainage_dist_tab.first .. x_retainage_dist_tab.last
478
479 INSERT INTO ap_invoice_distributions VALUES x_retainage_dist_tab(j);
480
481 -----------------------------------------------------------------
482 l_debug_info := 'Step 5: Update related_retainage_dist_id on parent distributions';
483 Print (l_api_name, l_debug_info);
484 -----------------------------------------------------------------
485
486 FORALL k in inv_dist_tab.first .. inv_dist_tab.last
487
488 UPDATE ap_invoice_distributions_all
489 SET related_retainage_dist_id = inv_dist_tab(k)
490 WHERE invoice_distribution_id = inv_dist_tab(k);
491
492 -----------------------------------------------------------------
493 l_debug_info := 'Step 6: Clear the local pl/sql tables';
494 Print (l_api_name, l_debug_info);
495 -----------------------------------------------------------------
496
497 inv_dist_tab.delete;
498 item_dist_tab.delete;
499
500 END IF;
501
502 Print (l_api_name, 'Insert_Distributions (-)');
503
504 END Insert_Distributions;
505
506 PROCEDURE Update_Payment_Schedules (x_invoice_id IN ap_invoices.invoice_id%type) AS
507
508 CURSOR c_payment_schedules (c_invoice_id IN ap_invoices.invoice_id%type) IS
509 SELECT *
510 FROM ap_payment_schedules_all
511 WHERE invoice_id = c_invoice_id
512 FOR UPDATE OF amount_remaining;
513
514 TYPE payNum IS TABLE OF ap_payment_schedules_all.payment_num%type INDEX BY PLS_INTEGER;
515 TYPE paySchedUpd IS TABLE OF ap_payment_schedules_all%rowtype INDEX BY PLS_INTEGER;
516
517 pay_num_tab payNum;
518 pay_sched_upd_tab paySchedUpd;
519
520 l_inv_curr_gross_amount ap_payment_schedules_all.inv_curr_gross_amount%type;
521 l_invoice_amount ap_invoices_all.invoice_amount%type
522 := g_invoice_info.invoice_amount;
523 l_retained_amount ap_invoice_lines_all.retained_amount%type
524 := g_invoice_info.retained_amount;
525 l_invoice_currency_code ap_invoices_all.invoice_currency_code%type
526 := g_invoice_info.invoice_currency_code;
527 l_payment_currency_code ap_invoices_all.payment_currency_code%type
528 := g_invoice_info.payment_currency_code;
529 l_payment_cross_rate ap_invoices_all.payment_cross_rate%type
530 := g_invoice_info.payment_cross_rate;
531 l_amt_applicable_to_discount ap_invoices_all.amount_applicable_to_discount%type
532 := g_invoice_info.amount_applicable_to_discount;
533
534 l_amt_to_subtract NUMBER;
535 l_amt_to_subtract_pay_curr NUMBER;
536 l_disc_amt_factor NUMBER;
537 l_sum_amt_to_subtract NUMBER := 0;
538 l_last_schedule NUMBER;
539
540 l_debug_info Varchar2(240);
541 l_api_name Constant Varchar2(100)
542 := 'Update_Payment_Schedules';
543
544 BEGIN
545
546 Print (l_api_name, 'Update_Payment_Schedules (+)');
547
548 OPEN c_payment_schedules(x_invoice_id);
549 FETCH c_payment_schedules
550 BULK COLLECT INTO pay_sched_upd_tab;
551 CLOSE c_payment_schedules;
552
553 If pay_sched_upd_tab.COUNT > 0 Then
554
555 -----------------------------------------------------------------
556 l_debug_info := 'Step 1: Fetch Payment Schedules';
557 Print (l_api_name, l_debug_info);
558 -----------------------------------------------------------------
559
560 For i IN pay_sched_upd_tab.first .. pay_sched_upd_tab.last
561 Loop
562 If (l_invoice_amount = 0) Then
563
564 l_amt_to_subtract := 0;
565 l_disc_amt_factor := 0;
566
567 Else
568
569 l_inv_curr_gross_amount := nvl(pay_sched_upd_tab(i).inv_curr_gross_amount, pay_sched_upd_tab(i).gross_amount);
570
571 l_amt_to_subtract := l_retained_amount *
572 (l_inv_curr_gross_amount / l_invoice_amount);
573
574 l_amt_to_subtract := ap_utilities_pkg.ap_round_currency
575 (l_amt_to_subtract, l_invoice_currency_code);
576
577 l_amt_to_subtract_pay_curr := ap_utilities_pkg.ap_round_currency
578 (l_amt_to_subtract * l_payment_cross_rate, l_payment_currency_code);
579
580 l_disc_amt_factor := l_retained_amount /
581 nvl(l_amt_applicable_to_discount, l_invoice_amount);
582
583 pay_sched_upd_tab(i).gross_amount
584 := pay_sched_upd_tab(i).gross_amount +
585 l_amt_to_subtract_pay_curr;
586
587 pay_sched_upd_tab(i).inv_curr_gross_amount := pay_sched_upd_tab(i).gross_amount;
588
589 pay_sched_upd_tab(i).amount_remaining
590 := pay_sched_upd_tab(i).amount_remaining +
591 l_amt_to_subtract_pay_curr;
592
593 pay_sched_upd_tab(i).discount_amount_available
594 := pay_sched_upd_tab(i).discount_amount_available +
595 ap_utilities_pkg.ap_round_currency
596 (pay_sched_upd_tab(i).discount_amount_available * l_disc_amt_factor,
597 l_payment_currency_code);
598
599 pay_sched_upd_tab(i).second_disc_amt_available
600 := pay_sched_upd_tab(i).second_disc_amt_available +
601 ap_utilities_pkg.ap_round_currency
602 (pay_sched_upd_tab(i).second_disc_amt_available * l_disc_amt_factor,
603 l_payment_currency_code);
604
605 pay_sched_upd_tab(i).third_disc_amt_available
606 := pay_sched_upd_tab(i).third_disc_amt_available +
607 ap_utilities_pkg.ap_round_currency
608 (pay_sched_upd_tab(i).third_disc_amt_available * l_disc_amt_factor,
609 l_payment_currency_code);
610
611 l_sum_amt_to_subtract := l_sum_amt_to_subtract + l_amt_to_subtract;
612
613 pay_num_tab(i) := pay_sched_upd_tab(i).payment_num;
614
615 End If;
616 End Loop;
617
618 -- To keep it consistent with the withholding logic, rounding
619 -- due to proration is applied to the last payment schedule.
620 -----------------------------------------------------------------
621 l_debug_info := 'Step 2: Perform rounding on payment schedules';
622 Print (l_api_name, l_debug_info);
623 -----------------------------------------------------------------
624
625 If l_retained_amount <> l_sum_amt_to_subtract Then
626
627 l_last_schedule := pay_sched_upd_tab.last;
628
629 pay_sched_upd_tab(l_last_schedule).gross_amount
630 := pay_sched_upd_tab(l_last_schedule).gross_amount -
631 (l_retained_amount - l_sum_amt_to_subtract);
632
633 pay_sched_upd_tab(l_last_schedule).inv_curr_gross_amount
634 := pay_sched_upd_tab(l_last_schedule).gross_amount;
635
636 pay_sched_upd_tab(l_last_schedule).amount_remaining
637 := pay_sched_upd_tab(l_last_schedule).amount_remaining -
638 (l_retained_amount - l_sum_amt_to_subtract);
639 End If;
640
641 -----------------------------------------------------------------
642 l_debug_info := 'Step 3: Bulk Update Payment Schedules';
643 Print (l_api_name, l_debug_info);
644 -----------------------------------------------------------------
645
646 FORALL i in pay_sched_upd_tab.first .. pay_sched_upd_tab.last
647
648 UPDATE ap_payment_schedules_all
649 SET ROW = pay_sched_upd_tab(i)
650 WHERE invoice_id = x_invoice_id
651 AND payment_num = pay_num_tab(i);
652
653 /*
654 -----------------------------------------------------------------
655 l_debug_info := 'Step 4: Update amount_applicable_to_discount';
656 Print (l_api_name, l_debug_info);
657 -----------------------------------------------------------------
658
659 UPDATE ap_invoices_all
660 SET amount_applicable_to_discount = (amount_applicable_to_discount + l_retained_amount)
661 WHERE invoice_id = x_invoice_id;
662 */
663 pay_num_tab.delete;
664 pay_sched_upd_tab.delete;
665
666 End If;
667
668 Print (l_api_name, 'Update_Payment_Schedules(-)');
669
670 END Update_Payment_Schedules;
671
672 Procedure Update_PO_Shipment_Dists
673 (x_line_location_id IN ap_invoice_lines_all.po_line_location_id%type,
674 x_retained_amount IN ap_invoice_lines_all.retained_amount%type,
675 x_retainage_dist_tab IN retDistType) AS
676
677 l_po_ap_dist_rec PO_AP_DIST_REC_TYPE;
678 l_po_ap_line_loc_rec PO_AP_LINE_LOC_REC_TYPE;
679
680 l_return_status VARCHAR2(100);
681 l_msg_data VARCHAR2(4000);
682
683 l_api_name VARCHAR2(50);
684 l_debug_info VARCHAR2(2000);
685
686 BEGIN
687
688 l_api_name := 'Update_PO_Shipment_Dists';
689
690 l_po_ap_dist_rec := PO_AP_DIST_REC_TYPE.create_object();
691
692 l_po_ap_line_loc_rec := PO_AP_LINE_LOC_REC_TYPE.create_object(
693 p_po_line_location_id => x_line_location_id,
694 p_uom_code => NULL,
695 p_quantity_billed => NULL,
696 p_amount_billed => NULL,
697 p_quantity_financed => NULL,
698 p_amount_financed => NULL,
699 p_quantity_recouped => NULL,
700 p_amount_recouped => NULL,
701 p_retainage_withheld_amt => -x_retained_amount,
702 p_retainage_released_amt => NULL
703 );
704
705 FOR i in nvl(x_retainage_dist_tab.first,0)..nvl(x_retainage_dist_tab.last,0)
706 LOOP
707
708 IF (x_retainage_dist_tab.exists(i)) THEN
709
710 l_po_ap_dist_rec.add_change
711 (p_po_distribution_id => x_retainage_dist_tab(i).po_distribution_id,
712 p_uom_code => NULL,
713 p_quantity_billed => NULL,
714 p_amount_billed => NULL,
715 p_quantity_financed => NULL,
716 p_amount_financed => NULL,
717 p_quantity_recouped => NULL,
718 p_amount_recouped => NULL,
719 p_retainage_withheld_amt => -x_retainage_dist_tab(i).amount,
720 p_retainage_released_amt => NULL);
721
722
723 END IF;
724
725 END LOOP;
726
727 PO_AP_INVOICE_MATCH_GRP.Update_Document_Ap_Values(
728 P_Api_Version => 1.0,
729 P_Line_Loc_Changes_Rec => l_po_ap_line_loc_rec,
730 P_Dist_Changes_Rec => l_po_ap_dist_rec,
731 X_Return_Status => l_return_status,
732 X_Msg_Data => l_msg_data);
733 END Update_PO_Shipment_Dists;
734
735 Procedure Print (x_api_name IN Varchar2, x_debug_info IN Varchar2) As
736 Begin
737 IF (g_level_procedure >= g_current_runtime_level) THEN
738 fnd_log.string(g_level_procedure,g_module_name||x_api_name,x_debug_info);
739 END IF;
740 End Print;
741
742 END AP_RETAINAGE_PKG;