[Home] [Help]
PACKAGE BODY: APPS.AP_RETAINAGE_RELEASE_PKG
Source
1 Package Body ap_retainage_release_pkg As
2 /* $Header: apcwrelb.pls 120.4.12010000.2 2008/08/08 03:08:07 sparames ship $ */
3
4 -- FND logging global variables
5
6 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AP_RETAINAGE_RELEASE_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_RELEASE_PKG.';
23
24 Cursor c_release_invoice_info (c_invoice_id IN ap_invoices_all.invoice_id%type) Is
25 Select ai.invoice_currency_code,
26 --Bug6893516 Exchange rate should be 1 for base currency invoices
27 nvl(ai.exchange_rate,1) exchange_rate,
28 ai.gl_date,
29 ai.vendor_id,
30 ai.vendor_site_id,
31 ai.org_id
32 From ap_invoices_all ai
33 Where invoice_id = c_invoice_id;
34
35 g_release_invoice_info c_release_invoice_info%rowtype;
36 g_period_name gl_period_statuses.period_name%TYPE;
37 g_user_id NUMBER;
38 g_login_id NUMBER;
39
40 TYPE releaseLinesType IS TABLE OF ap_invoice_lines_all%rowtype INDEX BY PLS_INTEGER;
41 TYPE relDistType IS TABLE OF ap_invoice_distributions_all%rowtype INDEX BY PLS_INTEGER;
42
43 Procedure Lock_Retained_Invoices (x_retained_lines_tab IN retainedLinesType,
44 x_lock_status OUT NOCOPY BOOLEAN);
45
46
47 Procedure Unlock_Retained_Invoices (x_retained_lines_tab IN retainedLinesType,
48 x_lock_status OUT NOCOPY BOOLEAN);
49
50 Procedure create_release_lines (x_invoice_id IN ap_invoices_all.invoice_id%TYPE,
51 x_release_amount IN number,
52 x_release_amount_remaining IN number,
53 x_retained_lines_tab IN retainedLinesType,
54 x_released_lines_tab OUT NOCOPY releaseLinesType);
55
56 Procedure create_release_distributions (x_release_amount IN number,
57 x_release_amount_remaining IN number,
58 x_released_lines_tab IN releaseLinesType);
59
60 Procedure final_release_rounding (x_line_location_id IN po_line_locations_all.line_location_id%type,
61 x_max_invoice_dist_id IN NUMBER);
62
63 Procedure Update_PO_Shipment_Dists
64 (x_line_location_id IN ap_invoice_lines_all.po_line_location_id%type,
65 x_released_amount IN ap_invoice_lines_all.retained_amount%type,
66 x_released_dist_tab IN relDistType);
67
68 PROCEDURE log (x_api_name IN varchar2, x_debug_info IN varchar2);
69
70 --
71 -- Main Procedure
72 --
73 PROCEDURE create_release (x_invoice_id IN ap_invoices_all.invoice_id%TYPE,
74 x_release_shipments_tab IN release_shipments_tab) As
75
76 released_lines_tab releaseLinesType;
77 l_retained_lines_locked boolean;
78
79 l_debug_info Varchar2(240);
80 l_api_name Constant Varchar2(100) := 'Create_Release';
81
82 BEGIN
83
84 log(l_api_name,'Create_Release (+)');
85
86 -----------------------------------------------------------------
87 l_debug_info := 'Step 1: Get Retainage Release Invoice Details';
88 log(l_api_name, l_debug_info);
89 -----------------------------------------------------------------
90
91 OPEN c_release_invoice_info (x_invoice_id);
92 FETCH c_release_invoice_info
93 INTO g_release_invoice_info;
94 CLOSE c_release_invoice_info;
95
96 g_user_id := fnd_profile.value('USER_ID');
97 g_login_id := fnd_profile.value('LOGIN_ID');
98
99 g_period_name := AP_UTILITIES_PKG.get_current_gl_date(g_release_invoice_info.gl_date, g_release_invoice_info.org_id);
100
101 IF (g_period_name IS NULL) THEN
102 -- Get gl_period and Date from a future period for the accounting date
103 ap_utilities_pkg.get_open_gl_date(p_date => g_release_invoice_info.gl_date,
104 p_period_name => g_period_name,
105 p_gl_date => g_release_invoice_info.gl_date);
106
107 IF (g_release_invoice_info.gl_date IS NULL) THEN
108 fnd_message.set_name('SQLAP','AP_DISTS_NO_OPEN_FUT_PERIOD');
109 app_exception.raise_exception;
110 END IF;
111 END IF;
112
113 IF x_release_shipments_tab.count > 0 THEN
114
115 -----------------------------------------------------------------
116 l_debug_info := 'Step 2: Iterate each shipment to create release lines';
117 log(l_api_name, l_debug_info);
118 -----------------------------------------------------------------
119
120 FOR i in x_release_shipments_tab.first .. x_release_shipments_tab.last
121 LOOP
122
123 -----------------------------------------------------------------
124 l_debug_info := 'Step 3: Fetch invoice lines with retainage';
125 log(l_api_name, l_debug_info);
126 -----------------------------------------------------------------
127
128 IF x_release_shipments_tab(i).invoice_id IS NULL THEN
129
130 OPEN c_retained_lines_po (x_release_shipments_tab(i).line_location_id);
131 FETCH c_retained_lines_po
132 BULK COLLECT INTO retained_lines_tab;
133 CLOSE c_retained_lines_po;
134
135 ELSIF (x_release_shipments_tab(i).invoice_id IS NOT NULL AND
136 x_release_shipments_tab(i).line_number IS NOT NULL) THEN
137
138 OPEN c_retained_lines_inv (x_release_shipments_tab(i).invoice_id,
139 x_release_shipments_tab(i).line_number);
140 FETCH c_retained_lines_inv
141 BULK COLLECT INTO retained_lines_tab;
142 CLOSE c_retained_lines_inv;
143
144 END IF;
145
146 IF retained_lines_tab.count > 0 THEN
147
148 -----------------------------------------------------------------
149 l_debug_info := 'Step 4: Lock Retained Invoice Lines';
150 log(l_api_name, l_debug_info);
151 -----------------------------------------------------------------
152
153 Lock_Retained_Invoices (x_retained_lines_tab => retained_lines_tab,
154 x_lock_status => l_retained_lines_locked);
155
156 IF l_retained_lines_locked THEN
157
158 -----------------------------------------------------------------
159 l_debug_info := 'Step 5: Create Retainage Release Lines';
160 log(l_api_name, l_debug_info);
161 -----------------------------------------------------------------
162
163 create_release_lines
164 (x_invoice_id => x_invoice_id,
165 x_release_amount => x_release_shipments_tab(i).release_amount,
166 x_release_amount_remaining => x_release_shipments_tab(i).release_amount_remaining,
167 x_retained_lines_tab => retained_lines_tab,
168 x_released_lines_tab => released_lines_tab);
169
170 -----------------------------------------------------------------
171 l_debug_info := 'Step 6: Create Retainage Release Distributions';
172 log(l_api_name, l_debug_info);
173 -----------------------------------------------------------------
174
175 IF released_lines_tab.count > 0 THEN
176
177 create_release_distributions
178 (x_release_amount => x_release_shipments_tab(i).release_amount,
179 x_release_amount_remaining => x_release_shipments_tab(i).release_amount_remaining,
180 x_released_lines_tab => released_lines_tab);
181 END IF;
182 END IF;
183 END IF;
184
185 END LOOP;
186 END IF;
187
188 COMMIT;
189
190 -----------------------------------------------------------------
191 l_debug_info := 'Step 7: Unlock Retained Invoice Lines';
192 log(l_api_name, l_debug_info);
193 -----------------------------------------------------------------
194
195 IF l_retained_lines_locked THEN
196 Unlock_Retained_Invoices (x_retained_lines_tab => retained_lines_tab,
197 x_lock_status => l_retained_lines_locked);
198 END IF;
199
200 log(l_api_name, 'Create_Release (-)');
201
202 EXCEPTION
203
204 WHEN OTHERS THEN
205
206 IF l_retained_lines_locked THEN
207 Unlock_Retained_Invoices (x_retained_lines_tab => retained_lines_tab,
208 x_lock_status => l_retained_lines_locked);
209 END IF;
210
211 APP_EXCEPTION.RAISE_EXCEPTION;
212
213 END create_release;
214
215 Procedure create_release_lines (x_invoice_id IN ap_invoices_all.invoice_id%TYPE,
216 x_release_amount IN number,
217 x_release_amount_remaining IN number,
218 x_retained_lines_tab IN retainedLinesType,
219 x_released_lines_tab OUT NOCOPY releaseLinesType) As
220
221 l_release_proration_factor NUMBER;
222 l_release_base_amount NUMBER;
223 l_invoice_line_amount NUMBER;
224 l_sum_amount NUMBER := 0;
225 l_sum_base_amount NUMBER := 0;
226 l_max_line_amount NUMBER := 0;
227 l_rounding_index NUMBER;
228 l_max_inv_line_num NUMBER;
229
230 TYPE invIDType IS TABLE OF ap_invoice_lines_all.invoice_id%type INDEX BY PLS_INTEGER;
231 TYPE LineNumType IS TABLE OF ap_invoice_lines_all.line_number%type INDEX BY PLS_INTEGER;
232 TYPE relAmtType IS TABLE OF ap_invoice_lines_all.retained_amount_remaining%type INDEX BY PLS_INTEGER;
233
234 retained_inv_id_tab invIDType;
235 retained_line_num_tab LineNumType;
236 release_amount_tab relAmtType;
237
238 l_debug_info Varchar2(240);
239 l_api_name Constant Varchar2(100) := 'Create_Release_Lines';
240
241 Begin
242
243 l_release_proration_factor := x_release_amount / x_release_amount_remaining;
244
245 l_release_base_amount := ap_utilities_pkg.ap_round_currency
246 (x_release_amount * g_release_invoice_info.exchange_rate,
247 g_release_invoice_info.invoice_currency_code);
248
249 SELECT nvl(max(line_number),0) + 1
250 INTO l_max_inv_line_num
251 FROM ap_invoice_lines_all
252 WHERE invoice_id = X_invoice_id;
253
254 -- l_max_inv_line_num := ap_invoices_pkg.get_max_line_number(x_invoice_id) + 1;
255
256 FOR i in x_retained_lines_tab.first .. x_retained_lines_tab.last
257 LOOP
258
259 x_released_lines_tab(i).invoice_id := x_invoice_id;
260 x_released_lines_tab(i).line_number := l_max_inv_line_num;
261 x_released_lines_tab(i).line_type_lookup_code := 'RETAINAGE RELEASE';
262 x_released_lines_tab(i).description := x_retained_lines_tab(i).description;
263
264 x_released_lines_tab(i).org_id := x_retained_lines_tab(i).org_id;
265
266 x_released_lines_tab(i).generate_dists := 'D';
267 x_released_lines_tab(i).match_type := x_retained_lines_tab(i).match_type;
268
269 x_released_lines_tab(i).prorate_across_all_items := 'N';
270 x_released_lines_tab(i).accounting_date := g_release_invoice_info.gl_date;
271 x_released_lines_tab(i).period_name := g_period_name;
272
273 x_released_lines_tab(i).deferred_acctg_flag := 'N';
274 x_released_lines_tab(i).set_of_books_id := x_retained_lines_tab(i).set_of_books_id;
275
276 l_invoice_line_amount := l_release_proration_factor *
277 x_retained_lines_tab(i).retained_amount_remaining;
278
279 x_released_lines_tab(i).amount := ap_utilities_pkg.ap_round_currency
280 (l_invoice_line_amount,
281 g_release_invoice_info.invoice_currency_code);
282
283 x_released_lines_tab(i).base_amount := ap_utilities_pkg.ap_round_currency
284 (x_released_lines_tab(i).amount *
285 g_release_invoice_info.exchange_rate,
286 g_release_invoice_info.invoice_currency_code);
287
288 x_released_lines_tab(i).quantity_invoiced := l_release_proration_factor * x_retained_lines_tab(i).quantity_invoiced;
289 x_released_lines_tab(i).unit_price := l_release_proration_factor * x_retained_lines_tab(i).unit_price;
290 x_released_lines_tab(i).unit_meas_lookup_code := x_retained_lines_tab(i).unit_meas_lookup_code;
291
292 x_released_lines_tab(i).wfapproval_status := x_retained_lines_tab(i).wfapproval_status;
293
294 x_released_lines_tab(i).discarded_flag := 'N';
295 x_released_lines_tab(i).cancelled_flag := 'N';
296
297 x_released_lines_tab(i).po_header_id := x_retained_lines_tab(i).po_header_id;
298 x_released_lines_tab(i).po_line_id := x_retained_lines_tab(i).po_line_id;
299 x_released_lines_tab(i).po_release_id := x_retained_lines_tab(i).po_release_id;
300 x_released_lines_tab(i).po_line_location_id := x_retained_lines_tab(i).po_line_location_id;
301 x_released_lines_tab(i).po_distribution_id := x_retained_lines_tab(i).po_distribution_id;
302 x_released_lines_tab(i).rcv_transaction_id := x_retained_lines_tab(i).rcv_transaction_id;
303
304 x_released_lines_tab(i).final_match_flag := x_retained_lines_tab(i).final_match_flag;
305 x_released_lines_tab(i).assets_tracking_flag := x_retained_lines_tab(i).assets_tracking_flag;
306 x_released_lines_tab(i).asset_book_type_code := x_retained_lines_tab(i).asset_book_type_code;
307 x_released_lines_tab(i).asset_category_id := x_retained_lines_tab(i).asset_category_id;
308 x_released_lines_tab(i).project_id := x_retained_lines_tab(i).project_id;
309 x_released_lines_tab(i).task_id := x_retained_lines_tab(i).task_id;
310 x_released_lines_tab(i).expenditure_type := x_retained_lines_tab(i).expenditure_type;
311 x_released_lines_tab(i).expenditure_item_date := x_retained_lines_tab(i).expenditure_item_date;
312 x_released_lines_tab(i).expenditure_organization_id := x_retained_lines_tab(i).expenditure_organization_id;
313
314 x_released_lines_tab(i).award_id := x_retained_lines_tab(i).award_id;
315 -- x_released_lines_tab(i).awt_group_id
316 x_released_lines_tab(i).reference_1 := x_retained_lines_tab(i).reference_1;
317 x_released_lines_tab(i).reference_2 := x_retained_lines_tab(i).reference_2;
318 x_released_lines_tab(i).receipt_verified_flag := x_retained_lines_tab(i).receipt_verified_flag;
319 x_released_lines_tab(i).receipt_required_flag := x_retained_lines_tab(i).receipt_required_flag;
320 x_released_lines_tab(i).receipt_missing_flag := x_retained_lines_tab(i).receipt_missing_flag;
321 x_released_lines_tab(i).justification := x_retained_lines_tab(i).justification;
322 x_released_lines_tab(i).expense_group := x_retained_lines_tab(i).expense_group;
323 x_released_lines_tab(i).start_expense_date := x_retained_lines_tab(i).start_expense_date;
324 x_released_lines_tab(i).end_expense_date := x_retained_lines_tab(i).end_expense_date;
325 x_released_lines_tab(i).receipt_currency_code := x_retained_lines_tab(i).receipt_currency_code;
326 x_released_lines_tab(i).receipt_conversion_rate := x_retained_lines_tab(i).receipt_conversion_rate;
327 x_released_lines_tab(i).receipt_currency_amount := x_retained_lines_tab(i).receipt_currency_amount;
328 x_released_lines_tab(i).daily_amount := x_retained_lines_tab(i).daily_amount;
329 x_released_lines_tab(i).web_parameter_id := x_retained_lines_tab(i).web_parameter_id;
330 x_released_lines_tab(i).adjustment_reason := x_retained_lines_tab(i).adjustment_reason;
331 x_released_lines_tab(i).merchant_document_number := x_retained_lines_tab(i).merchant_document_number;
332 x_released_lines_tab(i).merchant_name := x_retained_lines_tab(i).merchant_name;
333 x_released_lines_tab(i).merchant_reference := x_retained_lines_tab(i).merchant_reference;
334 x_released_lines_tab(i).merchant_tax_reg_number := x_retained_lines_tab(i).merchant_tax_reg_number;
338 x_released_lines_tab(i).company_prepaid_invoice_id := x_retained_lines_tab(i).company_prepaid_invoice_id;
335 x_released_lines_tab(i).merchant_taxpayer_id := x_retained_lines_tab(i).merchant_taxpayer_id;
336 x_released_lines_tab(i).country_of_supply := x_retained_lines_tab(i).country_of_supply;
337 x_released_lines_tab(i).credit_card_trx_id := x_retained_lines_tab(i).credit_card_trx_id;
339 x_released_lines_tab(i).cc_reversal_flag := x_retained_lines_tab(i).cc_reversal_flag;
340
341 x_released_lines_tab(i).creation_date := sysdate;
342 x_released_lines_tab(i).created_by := g_user_id;
343 x_released_lines_tab(i).last_updated_by := g_user_id;
344 x_released_lines_tab(i).last_update_date := sysdate;
345 x_released_lines_tab(i).last_update_login := g_login_id;
346
347 x_released_lines_tab(i).attribute_category := x_retained_lines_tab(i).attribute_category;
348 x_released_lines_tab(i).attribute1 := x_retained_lines_tab(i).attribute1;
349 x_released_lines_tab(i).attribute2 := x_retained_lines_tab(i).attribute2;
350 x_released_lines_tab(i).attribute3 := x_retained_lines_tab(i).attribute3;
351 x_released_lines_tab(i).attribute4 := x_retained_lines_tab(i).attribute4;
352 x_released_lines_tab(i).attribute5 := x_retained_lines_tab(i).attribute5;
353 x_released_lines_tab(i).attribute6 := x_retained_lines_tab(i).attribute6;
354 x_released_lines_tab(i).attribute7 := x_retained_lines_tab(i).attribute7;
355 x_released_lines_tab(i).attribute8 := x_retained_lines_tab(i).attribute8;
356 x_released_lines_tab(i).attribute9 := x_retained_lines_tab(i).attribute9;
357 x_released_lines_tab(i).attribute10 := x_retained_lines_tab(i).attribute10;
358 x_released_lines_tab(i).attribute11 := x_retained_lines_tab(i).attribute11;
359 x_released_lines_tab(i).attribute12 := x_retained_lines_tab(i).attribute12;
360 x_released_lines_tab(i).attribute13 := x_retained_lines_tab(i).attribute13;
361 x_released_lines_tab(i).attribute14 := x_retained_lines_tab(i).attribute14;
362 x_released_lines_tab(i).attribute15 := x_retained_lines_tab(i).attribute15;
363 x_released_lines_tab(i).global_attribute_category := x_retained_lines_tab(i).global_attribute_category;
364 x_released_lines_tab(i).global_attribute1 := x_retained_lines_tab(i).global_attribute1;
365 x_released_lines_tab(i).global_attribute2 := x_retained_lines_tab(i).global_attribute2;
366 x_released_lines_tab(i).global_attribute3 := x_retained_lines_tab(i).global_attribute3;
367 x_released_lines_tab(i).global_attribute4 := x_retained_lines_tab(i).global_attribute4;
368 x_released_lines_tab(i).global_attribute5 := x_retained_lines_tab(i).global_attribute5;
369 x_released_lines_tab(i).global_attribute6 := x_retained_lines_tab(i).global_attribute6;
370 x_released_lines_tab(i).global_attribute7 := x_retained_lines_tab(i).global_attribute7;
371 x_released_lines_tab(i).global_attribute8 := x_retained_lines_tab(i).global_attribute8;
372 x_released_lines_tab(i).global_attribute9 := x_retained_lines_tab(i).global_attribute9;
373 x_released_lines_tab(i).global_attribute10 := x_retained_lines_tab(i).global_attribute10;
374 x_released_lines_tab(i).global_attribute11 := x_retained_lines_tab(i).global_attribute11;
375 x_released_lines_tab(i).global_attribute12 := x_retained_lines_tab(i).global_attribute12;
376 x_released_lines_tab(i).global_attribute13 := x_retained_lines_tab(i).global_attribute13;
377 x_released_lines_tab(i).global_attribute14 := x_retained_lines_tab(i).global_attribute14;
378 x_released_lines_tab(i).global_attribute15 := x_retained_lines_tab(i).global_attribute15;
379 x_released_lines_tab(i).global_attribute16 := x_retained_lines_tab(i).global_attribute16;
380 x_released_lines_tab(i).global_attribute17 := x_retained_lines_tab(i).global_attribute17;
381 x_released_lines_tab(i).global_attribute18 := x_retained_lines_tab(i).global_attribute18;
382 x_released_lines_tab(i).global_attribute19 := x_retained_lines_tab(i).global_attribute19;
383 x_released_lines_tab(i).global_attribute20 := x_retained_lines_tab(i).global_attribute20;
384
385 x_released_lines_tab(i).ship_to_location_id := x_retained_lines_tab(i).ship_to_location_id;
386 x_released_lines_tab(i).primary_intended_use := x_retained_lines_tab(i).primary_intended_use;
387 x_released_lines_tab(i).product_fisc_classification := x_retained_lines_tab(i).product_fisc_classification;
388 x_released_lines_tab(i).trx_business_category := x_retained_lines_tab(i).trx_business_category;
389 x_released_lines_tab(i).product_type := x_retained_lines_tab(i).product_type;
390 x_released_lines_tab(i).product_category := x_retained_lines_tab(i).product_category;
391 x_released_lines_tab(i).user_defined_fisc_class := x_retained_lines_tab(i).user_defined_fisc_class;
392 x_released_lines_tab(i).purchasing_category_id := x_retained_lines_tab(i).purchasing_category_id;
393 x_released_lines_tab(i).tax_classification_code := x_retained_lines_tab(i).tax_classification_code;
394 /* Bug 6729532 : Added code to copy the requester_id from the Standard invoice lines */
395 x_released_lines_tab(i).requester_id := x_retained_lines_tab(i).requester_id;
396
397
398 IF (x_released_lines_tab(i).amount >= l_max_line_amount) THEN
399 l_rounding_index := i;
400 l_max_line_amount := x_released_lines_tab(i).amount;
401 END IF;
402
403 x_released_lines_tab(i).retained_invoice_id := x_retained_lines_tab(i).invoice_id;
404 x_released_lines_tab(i).retained_line_number := x_retained_lines_tab(i).line_number;
405
406 retained_inv_id_tab(i) := x_retained_lines_tab(i).invoice_id;
407 retained_line_num_tab(i) := x_retained_lines_tab(i).line_number;
408 release_amount_tab(i) := x_released_lines_tab(i).amount;
409
413 l_max_inv_line_num := l_max_inv_line_num + 1;
410 l_sum_amount := l_sum_amount + x_released_lines_tab(i).amount;
411 l_sum_base_amount := l_sum_base_amount + x_released_lines_tab(i).base_amount;
412
414
415 END LOOP;
416
417 --
418 -- Amount and Base Amount rounding due to proration of shipment
419 -- release amount to invoice lines.
420 --
421 IF l_rounding_index Is Not Null THEN
422
423 IF l_sum_amount <> x_release_amount THEN
424
425 x_released_lines_tab(l_rounding_index).amount := x_released_lines_tab(l_rounding_index).amount +
426 (x_release_amount - l_sum_amount);
427
428 release_amount_tab(l_rounding_index) := x_released_lines_tab(l_rounding_index).amount;
429
430 END IF;
431
432 IF g_release_invoice_info.exchange_rate Is Not Null THEN
433
434 IF l_sum_base_amount <> l_release_base_amount THEN
435
436 x_released_lines_tab(l_rounding_index).rounding_amt := l_release_base_amount - l_sum_base_amount;
437
438 x_released_lines_tab(l_rounding_index).base_amount := x_released_lines_tab(l_rounding_index).base_amount +
439 x_released_lines_tab(l_rounding_index).rounding_amt;
440
441 END IF;
442 END IF;
443 END IF;
444
445 -- Insert Release Lines
446
447 IF x_released_lines_tab.count > 0 THEN
448
449 FORALL i in x_released_lines_tab.first .. x_released_lines_tab.last
450
451 INSERT INTO ap_invoice_lines_all VALUES x_released_lines_tab(i);
452
453 END IF;
454
455 -- Update original invoice line retained amount remaining
456
457 IF retained_inv_id_tab.count > 0 THEN
458
459 FORALL i in retained_inv_id_tab.first .. retained_inv_id_tab.last
460
461 UPDATE ap_invoice_lines
462 SET retained_amount_remaining = (abs(retained_amount_remaining) - abs(release_amount_tab(i)))
463 WHERE invoice_id = retained_inv_id_tab(i)
464 AND line_number = retained_line_num_tab(i);
465
466 END IF;
467
468 EXCEPTION
469
470 WHEN OTHERS THEN
471 log(l_api_name,'Error: '||sqlerrm);
472 APP_EXCEPTION.RAISE_EXCEPTION;
473
474 End create_release_lines;
475
476 Procedure create_release_distributions (x_release_amount IN number,
477 x_release_amount_remaining IN number,
478 x_released_lines_tab IN releaseLinesType) As
479
480 Cursor c_retained_distributions (c_invoice_id in ap_invoice_distributions_all.invoice_id%type,
481 c_line_number in ap_invoice_distributions_all.invoice_line_number%type) Is
482 select aid.*,
483 ap_invoice_distributions_s.nextval released_invoice_dist_id
484 from ap_invoice_distributions aid
485 where invoice_id = c_invoice_id
486 and invoice_line_number = c_line_number
487 and line_type_lookup_code = 'RETAINAGE';
488
489 Type retDistType IS TABLE OF c_retained_distributions%rowtype INDEX BY PLS_INTEGER;
490
491 retained_dist_tab retDistType;
492 released_dist_tab relDistType;
493
494 Type invDistIDType IS TABLE OF ap_invoice_distributions_all.invoice_distribution_id%type INDEX BY PLS_INTEGER;
495 Type relAmtType IS TABLE OF ap_invoice_distributions_all.amount%type INDEX BY PLS_INTEGER;
496
497 retained_inv_dist_id_tab invDistIDType;
498 release_amount_tab relAmtType;
499
500 l_release_proration_factor NUMBER;
501 l_distribution_line_number NUMBER;
502 l_distribution_amount NUMBER;
503
504 -- Rounding variables
505 l_sum_amount NUMBER;
506 l_sum_base_amount NUMBER;
507 l_max_dist_amount NUMBER;
508 l_rounding_index NUMBER;
509
510 -- Recoupment
511 l_recoupment_rate po_lines_all.recoupment_rate%TYPE;
512 l_amount_to_recoup ap_invoice_lines_all.amount%TYPE;
513 l_success Boolean;
514 l_error_message Varchar2(4000);
515
516 l_debug_info Varchar2(240);
517 l_api_name Constant Varchar2(100) := 'Create_Release_Distributions';
518
519 Begin
520 log(l_api_name,'Create_Release_Distributions (+)');
521
522 l_release_proration_factor := abs(x_release_amount) / abs(x_release_amount_remaining);
523
524 -----------------------------------------------------------------
525 l_debug_info := 'Step 1: Iterate each invoice line with retainage';
526 log(l_api_name,l_debug_info);
527 -----------------------------------------------------------------
528
529 FOR i in x_released_lines_tab.first .. x_released_lines_tab.last
530 LOOP
531
532 -----------------------------------------------------------------
533 l_debug_info := 'Step 2: Fetch invoice distributions'||
534 ' Invoice ID : '|| x_released_lines_tab(i).retained_invoice_id ||
535 ' Line Number: '|| x_released_lines_tab(i).retained_line_number;
536 log(l_api_name,l_debug_info);
537 -----------------------------------------------------------------
538
539 Open c_retained_distributions (x_released_lines_tab(i).retained_invoice_id,
540 x_released_lines_tab(i).retained_line_number);
541 Fetch c_retained_distributions
545 IF retained_dist_tab.count > 0 Then
542 Bulk Collect Into retained_dist_tab;
543 Close c_retained_distributions;
544
546
547 l_rounding_index := NULL;
548 l_sum_amount := 0;
549 l_sum_base_amount := 0;
550 l_max_dist_amount := 0;
551 l_distribution_line_number := 1;
552
553 -----------------------------------------------------------------
554 l_debug_info := 'Step 3: Iterate each invoice distribution';
555 log(l_api_name,l_debug_info);
556 -----------------------------------------------------------------
557
558 For j in retained_dist_tab.first .. retained_dist_tab.last
559 Loop
560 -----------------------------------------------------------------
561 l_debug_info := 'Step 4: Derive Retainage Release Distribution Attributes';
562 log(l_api_name,l_debug_info);
563 -----------------------------------------------------------------
564
565 released_dist_tab(j).invoice_id := x_released_lines_tab(i).invoice_id;
566 released_dist_tab(j).invoice_line_number := x_released_lines_tab(i).line_number;
567
568 released_dist_tab(j).invoice_distribution_id := retained_dist_tab(j).released_invoice_dist_id;
569 released_dist_tab(j).retained_invoice_dist_id := retained_dist_tab(j).invoice_distribution_id;
570
571 released_dist_tab(j).distribution_line_number := l_distribution_line_number;
572
573 released_dist_tab(j).line_type_lookup_code := retained_dist_tab(j).line_type_lookup_code;
574 released_dist_tab(j).dist_code_combination_id := retained_dist_tab(j).dist_code_combination_id;
575
576 l_distribution_amount := -1 * l_release_proration_factor *
577 retained_dist_tab(j).amount;
578
579 released_dist_tab(j).amount := ap_utilities_pkg.ap_round_currency
580 (l_distribution_amount,
581 g_release_invoice_info.invoice_currency_code);
582
583 released_dist_tab(j).base_amount := ap_utilities_pkg.ap_round_currency
584 (released_dist_tab(j).amount,
585 g_release_invoice_info.invoice_currency_code);
586
587 released_dist_tab(j).description := retained_dist_tab(j).description;
588 released_dist_tab(j).dist_match_type := retained_dist_tab(j).dist_match_type;
589 released_dist_tab(j).distribution_class := retained_dist_tab(j).distribution_class;
590
591 released_dist_tab(j).set_of_books_id := retained_dist_tab(j).set_of_books_id;
592 released_dist_tab(j).org_id := retained_dist_tab(j).org_id;
593
594 released_dist_tab(j).accounting_date := x_released_lines_tab(i).accounting_date;
595 released_dist_tab(j).period_name := x_released_lines_tab(i).period_name;
596 released_dist_tab(j).posted_flag := 'N';
597
598 released_dist_tab(j).po_distribution_id := retained_dist_tab(j).po_distribution_id;
599 released_dist_tab(j).rcv_transaction_id := retained_dist_tab(j).rcv_transaction_id;
600
601 released_dist_tab(j).matched_uom_lookup_code := retained_dist_tab(j).matched_uom_lookup_code;
602 released_dist_tab(j).unit_price := l_release_proration_factor * retained_dist_tab(j).unit_price;
603 released_dist_tab(j).quantity_invoiced := l_release_proration_factor * retained_dist_tab(j).quantity_invoiced;
604
605 released_dist_tab(j).match_status_flag := null;
606
607 released_dist_tab(j).creation_date := sysdate;
608 released_dist_tab(j).created_by := g_user_id;
609 released_dist_tab(j).last_updated_by := g_user_id;
610 released_dist_tab(j).last_update_date := sysdate;
611 released_dist_tab(j).last_update_login := g_login_id;
612
613 released_dist_tab(j).assets_addition_flag := retained_dist_tab(j).assets_addition_flag;
614 released_dist_tab(j).assets_tracking_flag := 'N';
615 released_dist_tab(j).asset_book_type_code := NULL;
616 released_dist_tab(j).asset_category_id := NULL;
617
618 released_dist_tab(j).inventory_transfer_status := 'N';
619 released_dist_tab(j).reference_1 := retained_dist_tab(j).reference_1;
620 released_dist_tab(j).reference_2 := retained_dist_tab(j).reference_2;
621
622 released_dist_tab(j).project_id := retained_dist_tab(j).project_id;
623 released_dist_tab(j).task_id := retained_dist_tab(j).task_id;
624 released_dist_tab(j).expenditure_type := retained_dist_tab(j).expenditure_type;
625 released_dist_tab(j).expenditure_item_date := retained_dist_tab(j).expenditure_item_date;
626 released_dist_tab(j).expenditure_organization_id := retained_dist_tab(j).expenditure_organization_id;
627 released_dist_tab(j).pa_quantity := retained_dist_tab(j).pa_quantity;
628 released_dist_tab(j).pa_addition_flag := retained_dist_tab(j).pa_addition_flag;
629 released_dist_tab(j).pa_cc_ar_invoice_id := retained_dist_tab(j).pa_cc_ar_invoice_id;
630 released_dist_tab(j).pa_cc_ar_invoice_line_num := retained_dist_tab(j).pa_cc_ar_invoice_line_num;
631 released_dist_tab(j).pa_cc_processed_code := retained_dist_tab(j).pa_cc_processed_code;
632 released_dist_tab(j).award_id := retained_dist_tab(j).award_id;
633 released_dist_tab(j).gms_burdenable_raw_cost := retained_dist_tab(j).gms_burdenable_raw_cost;
634
638 released_dist_tab(j).attribute3 := retained_dist_tab(j).attribute3;
635 released_dist_tab(j).attribute_category := retained_dist_tab(j).attribute_category;
636 released_dist_tab(j).attribute1 := retained_dist_tab(j).attribute1;
637 released_dist_tab(j).attribute2 := retained_dist_tab(j).attribute2;
639 released_dist_tab(j).attribute4 := retained_dist_tab(j).attribute4;
640 released_dist_tab(j).attribute5 := retained_dist_tab(j).attribute5;
641 released_dist_tab(j).attribute6 := retained_dist_tab(j).attribute6;
642 released_dist_tab(j).attribute7 := retained_dist_tab(j).attribute7;
643 released_dist_tab(j).attribute8 := retained_dist_tab(j).attribute8;
644 released_dist_tab(j).attribute9 := retained_dist_tab(j).attribute9;
645 released_dist_tab(j).attribute10 := retained_dist_tab(j).attribute10;
646 released_dist_tab(j).attribute11 := retained_dist_tab(j).attribute11;
647 released_dist_tab(j).attribute12 := retained_dist_tab(j).attribute12;
648 released_dist_tab(j).attribute13 := retained_dist_tab(j).attribute13;
649 released_dist_tab(j).attribute14 := retained_dist_tab(j).attribute14;
650 released_dist_tab(j).attribute15 := retained_dist_tab(j).attribute15;
651 released_dist_tab(j).global_attribute_category := retained_dist_tab(j).global_attribute_category;
652 released_dist_tab(j).global_attribute1 := retained_dist_tab(j).global_attribute1;
653 released_dist_tab(j).global_attribute2 := retained_dist_tab(j).global_attribute2;
654 released_dist_tab(j).global_attribute3 := retained_dist_tab(j).global_attribute3;
655 released_dist_tab(j).global_attribute4 := retained_dist_tab(j).global_attribute4;
656 released_dist_tab(j).global_attribute5 := retained_dist_tab(j).global_attribute5;
657 released_dist_tab(j).global_attribute6 := retained_dist_tab(j).global_attribute6;
658 released_dist_tab(j).global_attribute7 := retained_dist_tab(j).global_attribute7;
659 released_dist_tab(j).global_attribute8 := retained_dist_tab(j).global_attribute8;
660 released_dist_tab(j).global_attribute9 := retained_dist_tab(j).global_attribute9;
661 released_dist_tab(j).global_attribute10 := retained_dist_tab(j).global_attribute10;
662 released_dist_tab(j).global_attribute11 := retained_dist_tab(j).global_attribute11;
663 released_dist_tab(j).global_attribute12 := retained_dist_tab(j).global_attribute12;
664 released_dist_tab(j).global_attribute13 := retained_dist_tab(j).global_attribute13;
665 released_dist_tab(j).global_attribute14 := retained_dist_tab(j).global_attribute14;
666 released_dist_tab(j).global_attribute15 := retained_dist_tab(j).global_attribute15;
667 released_dist_tab(j).global_attribute16 := retained_dist_tab(j).global_attribute16;
668 released_dist_tab(j).global_attribute17 := retained_dist_tab(j).global_attribute17;
669 released_dist_tab(j).global_attribute18 := retained_dist_tab(j).global_attribute18;
670 released_dist_tab(j).global_attribute19 := retained_dist_tab(j).global_attribute19;
671 released_dist_tab(j).global_attribute20 := retained_dist_tab(j).global_attribute20;
672
673 released_dist_tab(j).intended_use := retained_dist_tab(j).intended_use;
674
675 IF (released_dist_tab(j).amount >= l_max_dist_amount) THEN
676 l_rounding_index := j;
677 l_max_dist_amount := released_dist_tab(j).amount;
678 END IF;
679
680 l_sum_amount := l_sum_amount + released_dist_tab(j).amount;
681 l_sum_base_amount := l_sum_base_amount + released_dist_tab(j).base_amount;
682
683 l_distribution_line_number := l_distribution_line_number + 1;
684
685 release_amount_tab(j) := released_dist_tab(j).amount;
686 retained_inv_dist_id_tab(j) := retained_dist_tab(j).invoice_distribution_id;
687
688 End Loop;
689
690 IF l_rounding_index Is Not Null THEN
691
692 IF l_sum_amount <> x_released_lines_tab(i).amount THEN
693
694 released_dist_tab(l_rounding_index).amount := released_dist_tab(l_rounding_index).amount +
695 (x_released_lines_tab(i).amount - l_sum_amount);
696
697 release_amount_tab(l_rounding_index) := released_dist_tab(l_rounding_index).amount;
698
699 END IF;
700
701 IF g_release_invoice_info.exchange_rate Is Not Null THEN
702
703 IF l_sum_base_amount <> x_released_lines_tab(i).base_amount THEN
704
705 released_dist_tab(l_rounding_index).rounding_amt := x_released_lines_tab(i).base_amount -
706 l_sum_base_amount;
707
708 released_dist_tab(l_rounding_index).base_amount := released_dist_tab(l_rounding_index).base_amount +
709 released_dist_tab(l_rounding_index).rounding_amt;
710 END IF;
711 END IF;
712 END IF;
713
714 -----------------------------------------------------------------
715 l_debug_info := 'Step 5: Update PO Shipment and Distributions';
719 Update_PO_Shipment_Dists (x_released_lines_tab(i).po_line_location_id,
716 log(l_api_name,l_debug_info);
717 -----------------------------------------------------------------
718
720 x_released_lines_tab(i).amount,
721 released_dist_tab);
722
723 -----------------------------------------------------------------
724 l_debug_info := 'Step 6: Insert Retainage Release Distributions: Count: '||released_dist_tab.count;
725 log(l_api_name,l_debug_info);
726 -----------------------------------------------------------------
727
728 IF released_dist_tab.count > 0 THEN
729
730 FORALL k in released_dist_tab.first .. released_dist_tab.last
731
732 INSERT INTO ap_invoice_distributions_all VALUES released_dist_tab(k);
733
734 END IF;
735
736 -----------------------------------------------------------------
737 l_debug_info := 'Step 7: Update original invoice line retained amount remaining';
738 log(l_api_name,l_debug_info);
739 -----------------------------------------------------------------
740
741 IF retained_inv_dist_id_tab.count > 0 THEN
742
743 FORALL i in retained_inv_dist_id_tab.first .. retained_inv_dist_id_tab.last
744
745 UPDATE ap_invoice_distributions_all
746 SET retained_amount_remaining = (abs(retained_amount_remaining) - abs(release_amount_tab(i)))
747 WHERE invoice_distribution_id = retained_inv_dist_id_tab(i);
748
749 END IF;
750
751 IF (x_released_lines_tab(i).po_line_location_id IS NOT NULL) THEN
752
753 SELECT pl.recoupment_rate
754 INTO l_recoupment_rate
755 FROM po_line_locations_all pll, po_lines_all pl
756 WHERE pll.line_location_id = x_released_lines_tab(i).po_line_location_id
757 AND pll.po_line_id = pl.po_line_id;
758
759 END IF;
760
761 IF (l_recoupment_rate IS NOT NULL) THEN
762
763 l_amount_to_recoup := ap_utilities_pkg.ap_round_currency(
764 (x_released_lines_tab(i).amount * l_recoupment_rate / 100), g_release_invoice_info.invoice_currency_code);
765
766 l_success := ap_matching_utils_pkg.ap_recoup_invoice_line(
767 P_Invoice_Id => x_released_lines_tab(i).invoice_id,
768 P_Invoice_Line_Number => x_released_lines_tab(i).line_number,
769 P_Amount_To_Recoup => l_amount_to_recoup,
770 P_Po_Line_Id => x_released_lines_tab(i).po_line_id,
771 P_Vendor_Id => g_release_invoice_info.vendor_id,
772 P_Vendor_Site_Id => g_release_invoice_info.vendor_site_id,
773 P_Accounting_Date => g_release_invoice_info.gl_date,
774 P_Period_Name => g_period_name,
775 P_User_Id => g_user_id,
776 P_Last_Update_Login => g_login_id,
777 P_Error_Message => l_error_message,
778 P_Calling_Sequence => 'AP_RETAINAGE_RELEASE_PKG.CREATE_RELEASE_DISTRIBUTIONS');
779
780 END IF;
781
782 END IF;
783 END LOOP;
784
785 log(l_api_name, 'Create_Release_Distributions (-)');
786
787 EXCEPTION
788 WHEN OTHERS THEN
789 log(l_api_name,'Error: '||sqlerrm);
790 APP_EXCEPTION.RAISE_EXCEPTION;
791
792 End create_release_distributions;
793
794 Procedure final_release_rounding (x_line_location_id IN po_line_locations_all.line_location_id%type,
795 x_max_invoice_dist_id IN NUMBER) Is
796
797 l_sum_retained_base_amount NUMBER;
798 l_sum_released_base_amount NUMBER;
799 l_final_release_rounding_amt NUMBER;
800
801 Begin
802
803 SELECT sum(ap_utilities_pkg.ap_round_currency(ail.retained_amount * ai.exchange_rate, ai.invoice_currency_code))
804 INTO l_sum_retained_base_amount
805 FROM ap_invoices ai,
806 ap_invoice_lines ail
807 WHERE ai.invoice_id = ail.invoice_id
808 AND ail.po_line_location_id = x_line_location_id
809 AND ail.line_type_lookup_code = 'ITEM'
810 AND NVL(ail.discarded_flag,'N') <> 'Y';
811
812 SELECT sum(base_amount)
813 INTO l_sum_released_base_amount
814 FROM ap_invoice_lines ail
815 WHERE (ail.retained_invoice_id, ail.retained_line_number)
816 IN
817 (Select invoice_id, line_number
818 From ap_invoice_lines ail2
819 Where ail.po_line_location_id = x_line_location_id
820 And ail.line_type_lookup_code = 'ITEM'
821 And nvl(ail.discarded_flag,'N') <> 'Y');
822
823 l_final_release_rounding_amt := l_sum_retained_base_amount - l_sum_released_base_amount;
824
825 UPDATE ap_invoice_distributions
826 SET final_release_rounding = l_final_release_rounding_amt
827 WHERE invoice_distribution_id = x_max_invoice_dist_id;
828
829 End final_release_rounding;
830
831 Procedure Lock_Retained_Invoices (x_retained_lines_tab IN retainedLinesType,
832 x_lock_status OUT NOCOPY BOOLEAN) As
833 Pragma Autonomous_Transaction;
834
835 TYPE invIDType IS TABLE OF ap_invoice_lines_all.invoice_id%type INDEX BY PLS_INTEGER;
836 TYPE lineNumType IS TABLE OF ap_invoice_lines_all.line_number%type INDEX BY PLS_INTEGER;
837
838 lock_invoice_id_tab invIDType;
839 lock_line_number_tab lineNumType;
840
841 Begin
842
843 SAVEPOINT lock_invoice;
844
845 FOR i in x_retained_lines_tab.first .. x_retained_lines_tab.last
846 LOOP
847 lock_invoice_id_tab(i) := x_retained_lines_tab(i).invoice_id;
848 lock_line_number_tab(i) := x_retained_lines_tab(i).line_number;
849 END LOOP;
850
851 FORALL i in x_retained_lines_tab.first .. x_retained_lines_tab.last
852
853 Update ap_invoice_lines
854 Set line_selected_for_release_flag = 'Y'
855 Where invoice_id = lock_invoice_id_tab(i)
856 And line_number = lock_line_number_tab(i);
857
858 x_lock_status := SQL%ROWCOUNT > 0;
859
860 COMMIT;
861
862 Exception
863
864 When Others Then
865
866 x_lock_status := FALSE;
867
868 ROLLBACK To lock_invoice;
869
870 End Lock_Retained_Invoices;
871
872 Procedure Unlock_Retained_Invoices (x_retained_lines_tab IN retainedLinesType,
873 x_lock_status OUT NOCOPY BOOLEAN) As
874 Pragma Autonomous_Transaction;
875
876 TYPE invIDType IS TABLE OF ap_invoice_lines_all.invoice_id%type INDEX BY PLS_INTEGER;
877 TYPE lineNumType IS TABLE OF ap_invoice_lines_all.line_number%type INDEX BY PLS_INTEGER;
878
879 lock_invoice_id_tab invIDType;
880 lock_line_number_tab lineNumType;
881
882 l_debug_info Varchar2(240);
883 l_api_name Constant Varchar2(100) := 'Unlock_Retained_Invoices';
884
885 Begin
886
887 SAVEPOINT unlock_invoice;
888
889 -----------------------------------------------------------------
890 l_debug_info := 'Step 1';
891 log(l_api_name, l_debug_info);
892 -----------------------------------------------------------------
893
894 FOR i in x_retained_lines_tab.first .. x_retained_lines_tab.last
895 LOOP
896 lock_invoice_id_tab(i) := x_retained_lines_tab(i).invoice_id;
897 lock_line_number_tab(i) := x_retained_lines_tab(i).line_number;
898 END LOOP;
899
900 -----------------------------------------------------------------
901 l_debug_info := 'Step 2';
902 log(l_api_name, l_debug_info);
903 -----------------------------------------------------------------
904
905 FORALL i in x_retained_lines_tab.first .. x_retained_lines_tab.last
906
907 Update ap_invoice_lines
908 Set line_selected_for_release_flag = NULL
909 Where invoice_id = lock_invoice_id_tab(i)
910 And line_number = lock_line_number_tab(i);
911
912 -----------------------------------------------------------------
913 l_debug_info := 'Step 3';
914 log(l_api_name, l_debug_info);
915 -----------------------------------------------------------------
916
917 x_lock_status := SQL%ROWCOUNT > 0;
918
919 COMMIT;
920
921 Exception
922
923 When Others Then
924
925 x_lock_status := FALSE;
926
927 ROLLBACK To unlock_invoice;
928
929 End Unlock_Retained_Invoices;
930
931 Procedure Update_PO_Shipment_Dists
932 (x_line_location_id IN ap_invoice_lines_all.po_line_location_id%type,
933 x_released_amount IN ap_invoice_lines_all.retained_amount%type,
934 x_released_dist_tab IN relDistType) As
935
936 l_po_ap_dist_rec PO_AP_DIST_REC_TYPE;
937 l_po_ap_line_loc_rec PO_AP_LINE_LOC_REC_TYPE;
938
939 l_return_status VARCHAR2(100);
940 l_msg_data VARCHAR2(4000);
941
942 l_api_name VARCHAR2(50);
943 l_debug_info VARCHAR2(2000);
944
945 BEGIN
946
947 l_api_name := 'Update_PO_Shipment_Dists';
948
949 l_po_ap_dist_rec := PO_AP_DIST_REC_TYPE.create_object();
950
951 l_po_ap_line_loc_rec := PO_AP_LINE_LOC_REC_TYPE.create_object(
952 p_po_line_location_id => x_line_location_id,
953 p_uom_code => NULL,
954 p_quantity_billed => NULL,
955 p_amount_billed => NULL,
956 p_quantity_financed => NULL,
957 p_amount_financed => NULL,
958 p_quantity_recouped => NULL,
959 p_amount_recouped => NULL,
960 p_retainage_withheld_amt => NULL,
961 p_retainage_released_amt => x_released_amount
962 );
963
964 FOR i in nvl(x_released_dist_tab.first,0)..nvl(x_released_dist_tab.last,0)
965 LOOP
966
967 IF (x_released_dist_tab.exists(i)) THEN
968
969 l_po_ap_dist_rec.add_change
970 (p_po_distribution_id => x_released_dist_tab(i).po_distribution_id,
971 p_uom_code => NULL,
972 p_quantity_billed => NULL,
973 p_amount_billed => NULL,
974 p_quantity_financed => NULL,
975 p_amount_financed => NULL,
976 p_quantity_recouped => NULL,
977 p_amount_recouped => NULL,
978 p_retainage_withheld_amt => NULL,
979 p_retainage_released_amt => x_released_dist_tab(i).amount);
980
981
982 END IF;
983
984 END LOOP;
985
986 PO_AP_INVOICE_MATCH_GRP.Update_Document_Ap_Values(
987 P_Api_Version => 1.0,
988 P_Line_Loc_Changes_Rec => l_po_ap_line_loc_rec,
989 P_Dist_Changes_Rec => l_po_ap_dist_rec,
990 X_Return_Status => l_return_status,
991 X_Msg_Data => l_msg_data);
992 END Update_PO_Shipment_Dists;
993
994
995 PROCEDURE log (x_api_name IN varchar2,
996 x_debug_info IN varchar2) As
997
998 BEGIN
999 IF (g_level_procedure >= g_current_runtime_level) THEN
1000 fnd_log.string(g_level_procedure,g_module_name||x_api_name,x_debug_info);
1001 END IF;
1002 END log;
1003
1004 End ap_retainage_release_pkg;