[Home] [Help]
PACKAGE BODY: APPS.AP_RETAINAGE_RELEASE_PKG
Source
1 Package Body ap_retainage_release_pkg As
2 /* $Header: apcwrelb.pls 120.8.12020000.2 2013/01/30 07:23:51 harsanan 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;
11 G_MSG_MEDIUM CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM;
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;
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
138 OPEN c_retained_lines_inv (x_release_shipments_tab(i).invoice_id,
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
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 l_patch_level varchar2(1); --bug13090879
242 l_create_awt_dists_type varchar2(50); --bug13090879
243
244 Begin
245
246 -- start bug13090879
247 select
248 substr(patch_level, 8, 1)
249 into
250 l_patch_level
251 from fnd_product_installations
252 where application_id = 200;
253
254 select
255 create_awt_dists_type
256 into
257 l_create_awt_dists_type
258 from ap_system_parameters;
259 --end bug13090879
260
261 l_release_proration_factor := x_release_amount / x_release_amount_remaining;
262
263 l_release_base_amount := ap_utilities_pkg.ap_round_currency
264 (x_release_amount * g_release_invoice_info.exchange_rate,
265 g_release_invoice_info.invoice_currency_code);
266
267 SELECT nvl(max(line_number),0) + 1
268 INTO l_max_inv_line_num
269 FROM ap_invoice_lines_all
270 WHERE invoice_id = X_invoice_id;
271
272 -- l_max_inv_line_num := ap_invoices_pkg.get_max_line_number(x_invoice_id) + 1;
273
274 FOR i in x_retained_lines_tab.first .. x_retained_lines_tab.last
275 LOOP
276
277 x_released_lines_tab(i).invoice_id := x_invoice_id;
278 x_released_lines_tab(i).line_number := l_max_inv_line_num;
282 x_released_lines_tab(i).org_id := x_retained_lines_tab(i).org_id;
279 x_released_lines_tab(i).line_type_lookup_code := 'RETAINAGE RELEASE';
280 x_released_lines_tab(i).description := x_retained_lines_tab(i).description;
281
283
284 x_released_lines_tab(i).generate_dists := 'D';
285 x_released_lines_tab(i).match_type := x_retained_lines_tab(i).match_type;
286
287 x_released_lines_tab(i).prorate_across_all_items := 'N';
288 x_released_lines_tab(i).accounting_date := g_release_invoice_info.gl_date;
289 x_released_lines_tab(i).period_name := g_period_name;
290
291 x_released_lines_tab(i).deferred_acctg_flag := 'N';
292 x_released_lines_tab(i).set_of_books_id := x_retained_lines_tab(i).set_of_books_id;
293
294 l_invoice_line_amount := l_release_proration_factor *
295 x_retained_lines_tab(i).retained_amount_remaining;
296
297 x_released_lines_tab(i).amount := ap_utilities_pkg.ap_round_currency
298 (l_invoice_line_amount,
299 g_release_invoice_info.invoice_currency_code);
300
301 x_released_lines_tab(i).base_amount := ap_utilities_pkg.ap_round_currency
302 (x_released_lines_tab(i).amount *
303 g_release_invoice_info.exchange_rate,
304 g_release_invoice_info.invoice_currency_code);
305
306 x_released_lines_tab(i).quantity_invoiced := l_release_proration_factor * x_retained_lines_tab(i).quantity_invoiced;
307 x_released_lines_tab(i).unit_price := l_release_proration_factor * x_retained_lines_tab(i).unit_price;
308 x_released_lines_tab(i).unit_meas_lookup_code := x_retained_lines_tab(i).unit_meas_lookup_code;
309
310 x_released_lines_tab(i).wfapproval_status := x_retained_lines_tab(i).wfapproval_status;
311
312 x_released_lines_tab(i).discarded_flag := 'N';
313 x_released_lines_tab(i).cancelled_flag := 'N';
314
315 x_released_lines_tab(i).po_header_id := x_retained_lines_tab(i).po_header_id;
316 x_released_lines_tab(i).po_line_id := x_retained_lines_tab(i).po_line_id;
317 x_released_lines_tab(i).po_release_id := x_retained_lines_tab(i).po_release_id;
318 x_released_lines_tab(i).po_line_location_id := x_retained_lines_tab(i).po_line_location_id;
319 x_released_lines_tab(i).po_distribution_id := x_retained_lines_tab(i).po_distribution_id;
320 x_released_lines_tab(i).rcv_transaction_id := x_retained_lines_tab(i).rcv_transaction_id;
321
322 x_released_lines_tab(i).final_match_flag := x_retained_lines_tab(i).final_match_flag;
323 x_released_lines_tab(i).assets_tracking_flag := x_retained_lines_tab(i).assets_tracking_flag;
324 x_released_lines_tab(i).asset_book_type_code := x_retained_lines_tab(i).asset_book_type_code;
325 x_released_lines_tab(i).asset_category_id := x_retained_lines_tab(i).asset_category_id;
326 x_released_lines_tab(i).project_id := x_retained_lines_tab(i).project_id;
327 x_released_lines_tab(i).task_id := x_retained_lines_tab(i).task_id;
328 x_released_lines_tab(i).expenditure_type := x_retained_lines_tab(i).expenditure_type;
329 x_released_lines_tab(i).expenditure_item_date := x_retained_lines_tab(i).expenditure_item_date;
330 x_released_lines_tab(i).expenditure_organization_id := x_retained_lines_tab(i).expenditure_organization_id;
331
332 x_released_lines_tab(i).award_id := x_retained_lines_tab(i).award_id;
333 -- x_released_lines_tab(i).awt_group_id
334 x_released_lines_tab(i).reference_1 := x_retained_lines_tab(i).reference_1;
335 x_released_lines_tab(i).reference_2 := x_retained_lines_tab(i).reference_2;
336 x_released_lines_tab(i).receipt_verified_flag := x_retained_lines_tab(i).receipt_verified_flag;
337 x_released_lines_tab(i).receipt_required_flag := x_retained_lines_tab(i).receipt_required_flag;
338 x_released_lines_tab(i).receipt_missing_flag := x_retained_lines_tab(i).receipt_missing_flag;
339 x_released_lines_tab(i).justification := x_retained_lines_tab(i).justification;
340 x_released_lines_tab(i).expense_group := x_retained_lines_tab(i).expense_group;
341 x_released_lines_tab(i).start_expense_date := x_retained_lines_tab(i).start_expense_date;
342 x_released_lines_tab(i).end_expense_date := x_retained_lines_tab(i).end_expense_date;
343 x_released_lines_tab(i).receipt_currency_code := x_retained_lines_tab(i).receipt_currency_code;
344 x_released_lines_tab(i).receipt_conversion_rate := x_retained_lines_tab(i).receipt_conversion_rate;
345 x_released_lines_tab(i).receipt_currency_amount := x_retained_lines_tab(i).receipt_currency_amount;
346 x_released_lines_tab(i).daily_amount := x_retained_lines_tab(i).daily_amount;
347 x_released_lines_tab(i).web_parameter_id := x_retained_lines_tab(i).web_parameter_id;
348 x_released_lines_tab(i).adjustment_reason := x_retained_lines_tab(i).adjustment_reason;
349 x_released_lines_tab(i).merchant_document_number := x_retained_lines_tab(i).merchant_document_number;
350 x_released_lines_tab(i).merchant_name := x_retained_lines_tab(i).merchant_name;
351 x_released_lines_tab(i).merchant_reference := x_retained_lines_tab(i).merchant_reference;
352 x_released_lines_tab(i).merchant_tax_reg_number := x_retained_lines_tab(i).merchant_tax_reg_number;
353 x_released_lines_tab(i).merchant_taxpayer_id := x_retained_lines_tab(i).merchant_taxpayer_id;
354 x_released_lines_tab(i).country_of_supply := x_retained_lines_tab(i).country_of_supply;
355 x_released_lines_tab(i).credit_card_trx_id := x_retained_lines_tab(i).credit_card_trx_id;
356 x_released_lines_tab(i).company_prepaid_invoice_id := x_retained_lines_tab(i).company_prepaid_invoice_id;
357 x_released_lines_tab(i).cc_reversal_flag := x_retained_lines_tab(i).cc_reversal_flag;
358
359 x_released_lines_tab(i).creation_date := sysdate;
360 x_released_lines_tab(i).created_by := g_user_id;
361 x_released_lines_tab(i).last_updated_by := g_user_id;
362 x_released_lines_tab(i).last_update_date := sysdate;
363 x_released_lines_tab(i).last_update_login := g_login_id;
364
365 x_released_lines_tab(i).attribute_category := x_retained_lines_tab(i).attribute_category;
366 x_released_lines_tab(i).attribute1 := x_retained_lines_tab(i).attribute1;
370 x_released_lines_tab(i).attribute5 := x_retained_lines_tab(i).attribute5;
367 x_released_lines_tab(i).attribute2 := x_retained_lines_tab(i).attribute2;
368 x_released_lines_tab(i).attribute3 := x_retained_lines_tab(i).attribute3;
369 x_released_lines_tab(i).attribute4 := x_retained_lines_tab(i).attribute4;
371 x_released_lines_tab(i).attribute6 := x_retained_lines_tab(i).attribute6;
372 x_released_lines_tab(i).attribute7 := x_retained_lines_tab(i).attribute7;
373 x_released_lines_tab(i).attribute8 := x_retained_lines_tab(i).attribute8;
374 x_released_lines_tab(i).attribute9 := x_retained_lines_tab(i).attribute9;
375 x_released_lines_tab(i).attribute10 := x_retained_lines_tab(i).attribute10;
376 x_released_lines_tab(i).attribute11 := x_retained_lines_tab(i).attribute11;
377 x_released_lines_tab(i).attribute12 := x_retained_lines_tab(i).attribute12;
378 x_released_lines_tab(i).attribute13 := x_retained_lines_tab(i).attribute13;
379 x_released_lines_tab(i).attribute14 := x_retained_lines_tab(i).attribute14;
380 x_released_lines_tab(i).attribute15 := x_retained_lines_tab(i).attribute15;
381 x_released_lines_tab(i).global_attribute_category := x_retained_lines_tab(i).global_attribute_category;
382 x_released_lines_tab(i).global_attribute1 := x_retained_lines_tab(i).global_attribute1;
383 x_released_lines_tab(i).global_attribute2 := x_retained_lines_tab(i).global_attribute2;
384 x_released_lines_tab(i).global_attribute3 := x_retained_lines_tab(i).global_attribute3;
385 x_released_lines_tab(i).global_attribute4 := x_retained_lines_tab(i).global_attribute4;
386 x_released_lines_tab(i).global_attribute5 := x_retained_lines_tab(i).global_attribute5;
387 x_released_lines_tab(i).global_attribute6 := x_retained_lines_tab(i).global_attribute6;
388 x_released_lines_tab(i).global_attribute7 := x_retained_lines_tab(i).global_attribute7;
389 x_released_lines_tab(i).global_attribute8 := x_retained_lines_tab(i).global_attribute8;
390 x_released_lines_tab(i).global_attribute9 := x_retained_lines_tab(i).global_attribute9;
391 x_released_lines_tab(i).global_attribute10 := x_retained_lines_tab(i).global_attribute10;
392 x_released_lines_tab(i).global_attribute11 := x_retained_lines_tab(i).global_attribute11;
393 x_released_lines_tab(i).global_attribute12 := x_retained_lines_tab(i).global_attribute12;
394 x_released_lines_tab(i).global_attribute13 := x_retained_lines_tab(i).global_attribute13;
395 x_released_lines_tab(i).global_attribute14 := x_retained_lines_tab(i).global_attribute14;
396 x_released_lines_tab(i).global_attribute15 := x_retained_lines_tab(i).global_attribute15;
397 x_released_lines_tab(i).global_attribute16 := x_retained_lines_tab(i).global_attribute16;
398 x_released_lines_tab(i).global_attribute17 := x_retained_lines_tab(i).global_attribute17;
399 x_released_lines_tab(i).global_attribute18 := x_retained_lines_tab(i).global_attribute18;
400 x_released_lines_tab(i).global_attribute19 := x_retained_lines_tab(i).global_attribute19;
401 x_released_lines_tab(i).global_attribute20 := x_retained_lines_tab(i).global_attribute20;
402
403 x_released_lines_tab(i).ship_to_location_id := x_retained_lines_tab(i).ship_to_location_id;
404 x_released_lines_tab(i).primary_intended_use := x_retained_lines_tab(i).primary_intended_use;
405 x_released_lines_tab(i).product_fisc_classification := x_retained_lines_tab(i).product_fisc_classification;
406 x_released_lines_tab(i).trx_business_category := x_retained_lines_tab(i).trx_business_category;
407 x_released_lines_tab(i).product_type := x_retained_lines_tab(i).product_type;
408 x_released_lines_tab(i).product_category := x_retained_lines_tab(i).product_category;
409 x_released_lines_tab(i).user_defined_fisc_class := x_retained_lines_tab(i).user_defined_fisc_class;
410 x_released_lines_tab(i).purchasing_category_id := x_retained_lines_tab(i).purchasing_category_id;
411 x_released_lines_tab(i).tax_classification_code := x_retained_lines_tab(i).tax_classification_code;
412 /* Bug 6729532 : Added code to copy the requester_id from the Standard invoice lines */
413 x_released_lines_tab(i).requester_id := x_retained_lines_tab(i).requester_id;
414
415 -- start bug13090879
416 if l_create_awt_dists_type in ('PAYMENT', 'BOTH') then
417 if l_patch_level = 'A' then
418 x_released_lines_tab(i).awt_group_id := x_retained_lines_tab(i).awt_group_id;
419 else
420 x_released_lines_tab(i).pay_awt_group_id := x_retained_lines_tab(i).pay_awt_group_id;
421 end if;
422 end if;
423 -- end bug13090879
424
425
426 IF (x_released_lines_tab(i).amount >= l_max_line_amount) THEN
427 l_rounding_index := i;
428 l_max_line_amount := x_released_lines_tab(i).amount;
429 END IF;
430
431 x_released_lines_tab(i).retained_invoice_id := x_retained_lines_tab(i).invoice_id;
432 x_released_lines_tab(i).retained_line_number := x_retained_lines_tab(i).line_number;
433
434 retained_inv_id_tab(i) := x_retained_lines_tab(i).invoice_id;
435 retained_line_num_tab(i) := x_retained_lines_tab(i).line_number;
436 release_amount_tab(i) := x_released_lines_tab(i).amount;
437
438 l_sum_amount := l_sum_amount + x_released_lines_tab(i).amount;
439 l_sum_base_amount := l_sum_base_amount + x_released_lines_tab(i).base_amount;
440
441 l_max_inv_line_num := l_max_inv_line_num + 1;
442
443 END LOOP;
444
445 --
449 IF l_rounding_index Is Not Null THEN
446 -- Amount and Base Amount rounding due to proration of shipment
447 -- release amount to invoice lines.
448 --
450
451 IF l_sum_amount <> x_release_amount THEN
452
453 x_released_lines_tab(l_rounding_index).amount := x_released_lines_tab(l_rounding_index).amount +
454 (x_release_amount - l_sum_amount);
455
456 release_amount_tab(l_rounding_index) := x_released_lines_tab(l_rounding_index).amount;
457
458 END IF;
459
460 IF g_release_invoice_info.exchange_rate Is Not Null THEN
461
462 IF l_sum_base_amount <> l_release_base_amount THEN
463
464 x_released_lines_tab(l_rounding_index).rounding_amt := l_release_base_amount - l_sum_base_amount;
465
466 x_released_lines_tab(l_rounding_index).base_amount := x_released_lines_tab(l_rounding_index).base_amount +
467 x_released_lines_tab(l_rounding_index).rounding_amt;
468
469 END IF;
470 END IF;
471 END IF;
472
473 -- Insert Release Lines
474
475 IF x_released_lines_tab.count > 0 THEN
476
477 FORALL i in x_released_lines_tab.first .. x_released_lines_tab.last
478
479 INSERT INTO ap_invoice_lines_all VALUES x_released_lines_tab(i);
480
481 END IF;
482
483 -- Update original invoice line retained amount remaining
484
485 IF retained_inv_id_tab.count > 0 THEN
486
487 FORALL i in retained_inv_id_tab.first .. retained_inv_id_tab.last
488
489 UPDATE ap_invoice_lines
490 SET retained_amount_remaining = (abs(retained_amount_remaining) - abs(release_amount_tab(i)))
491 WHERE invoice_id = retained_inv_id_tab(i)
492 AND line_number = retained_line_num_tab(i);
493
494 END IF;
495
496 EXCEPTION
497
498 WHEN OTHERS THEN
499 log(l_api_name,'Error: '||sqlerrm);
500 APP_EXCEPTION.RAISE_EXCEPTION;
501
502 End create_release_lines;
503
504 Procedure create_release_distributions (x_release_amount IN number,
505 x_release_amount_remaining IN number,
506 x_released_lines_tab IN releaseLinesType) As
507
508 Cursor c_retained_distributions (c_invoice_id in ap_invoice_distributions_all.invoice_id%type,
509 c_line_number in ap_invoice_distributions_all.invoice_line_number%type) Is
510 select aid.*,
511 ap_invoice_distributions_s.nextval released_invoice_dist_id
512 from ap_invoice_distributions aid
513 where invoice_id = c_invoice_id
514 and invoice_line_number = c_line_number
515 and line_type_lookup_code = 'RETAINAGE';
516
517 Type retDistType IS TABLE OF c_retained_distributions%rowtype INDEX BY PLS_INTEGER;
518
519 retained_dist_tab retDistType;
520 released_dist_tab relDistType;
521
522 Type invDistIDType IS TABLE OF ap_invoice_distributions_all.invoice_distribution_id%type INDEX BY PLS_INTEGER;
523 Type relAmtType IS TABLE OF ap_invoice_distributions_all.amount%type INDEX BY PLS_INTEGER;
524
525 retained_inv_dist_id_tab invDistIDType;
526 release_amount_tab relAmtType;
527
528 l_release_proration_factor NUMBER;
529 l_distribution_line_number NUMBER;
530 l_distribution_amount NUMBER;
531
532 -- Rounding variables
533 l_sum_amount NUMBER;
534 l_sum_base_amount NUMBER;
535 l_max_dist_amount NUMBER;
536 l_rounding_index NUMBER;
537
538 -- Recoupment
539 l_recoupment_rate po_lines_all.recoupment_rate%TYPE;
540 l_amount_to_recoup ap_invoice_lines_all.amount%TYPE;
541 l_success Boolean;
542 l_error_message Varchar2(4000);
543
544 l_debug_info Varchar2(240);
545 l_api_name Constant Varchar2(100) := 'Create_Release_Distributions';
546
547
548
549 Begin
550 log(l_api_name,'Create_Release_Distributions (+)');
551
552 l_release_proration_factor := abs(x_release_amount) / abs(x_release_amount_remaining);
553
554 -----------------------------------------------------------------
555 l_debug_info := 'Step 1: Iterate each invoice line with retainage';
556 log(l_api_name,l_debug_info);
557 -----------------------------------------------------------------
558
559 FOR i in x_released_lines_tab.first .. x_released_lines_tab.last
560 LOOP
561
562 -----------------------------------------------------------------
563 l_debug_info := 'Step 2: Fetch invoice distributions'||
564 ' Invoice ID : '|| x_released_lines_tab(i).retained_invoice_id ||
565 ' Line Number: '|| x_released_lines_tab(i).retained_line_number;
566 log(l_api_name,l_debug_info);
567 -----------------------------------------------------------------
568
569 Open c_retained_distributions (x_released_lines_tab(i).retained_invoice_id,
570 x_released_lines_tab(i).retained_line_number);
571 Fetch c_retained_distributions
572 Bulk Collect Into retained_dist_tab;
573 Close c_retained_distributions;
574
575 IF retained_dist_tab.count > 0 Then
576
577 l_rounding_index := NULL;
578 l_sum_amount := 0;
579 l_sum_base_amount := 0;
580 l_max_dist_amount := 0;
581 l_distribution_line_number := 1;
582
583 -----------------------------------------------------------------
584 l_debug_info := 'Step 3: Iterate each invoice distribution';
585 log(l_api_name,l_debug_info);
586 -----------------------------------------------------------------
590 -----------------------------------------------------------------
587
588 For j in retained_dist_tab.first .. retained_dist_tab.last
589 Loop
591 l_debug_info := 'Step 4: Derive Retainage Release Distribution Attributes';
592 log(l_api_name,l_debug_info);
593 -----------------------------------------------------------------
594
595 released_dist_tab(j).invoice_id := x_released_lines_tab(i).invoice_id;
596 released_dist_tab(j).invoice_line_number := x_released_lines_tab(i).line_number;
597
598 released_dist_tab(j).invoice_distribution_id := retained_dist_tab(j).released_invoice_dist_id;
599 released_dist_tab(j).retained_invoice_dist_id := retained_dist_tab(j).invoice_distribution_id;
600
601 released_dist_tab(j).distribution_line_number := l_distribution_line_number;
602
603 released_dist_tab(j).line_type_lookup_code := retained_dist_tab(j).line_type_lookup_code;
604 released_dist_tab(j).dist_code_combination_id := retained_dist_tab(j).dist_code_combination_id;
605
606 l_distribution_amount := -1 * l_release_proration_factor *
607 retained_dist_tab(j).amount;
608
609 released_dist_tab(j).amount := ap_utilities_pkg.ap_round_currency
610 (l_distribution_amount,
611 g_release_invoice_info.invoice_currency_code);
612
613 released_dist_tab(j).base_amount := ap_utilities_pkg.ap_round_currency
614 (released_dist_tab(j).amount,
615 g_release_invoice_info.invoice_currency_code);
616
617 released_dist_tab(j).description := retained_dist_tab(j).description;
618 released_dist_tab(j).dist_match_type := retained_dist_tab(j).dist_match_type;
619 released_dist_tab(j).distribution_class := retained_dist_tab(j).distribution_class;
620
621 released_dist_tab(j).set_of_books_id := retained_dist_tab(j).set_of_books_id;
622 released_dist_tab(j).org_id := retained_dist_tab(j).org_id;
623
624 released_dist_tab(j).accounting_date := x_released_lines_tab(i).accounting_date;
625 released_dist_tab(j).period_name := x_released_lines_tab(i).period_name;
626 released_dist_tab(j).posted_flag := 'N';
627
628 released_dist_tab(j).po_distribution_id := retained_dist_tab(j).po_distribution_id;
629 released_dist_tab(j).rcv_transaction_id := retained_dist_tab(j).rcv_transaction_id;
630
631 released_dist_tab(j).matched_uom_lookup_code := retained_dist_tab(j).matched_uom_lookup_code;
632 released_dist_tab(j).unit_price := l_release_proration_factor * retained_dist_tab(j).unit_price;
633 released_dist_tab(j).quantity_invoiced := l_release_proration_factor * retained_dist_tab(j).quantity_invoiced;
634
635 released_dist_tab(j).match_status_flag := null;
636
637 released_dist_tab(j).creation_date := sysdate;
638 released_dist_tab(j).created_by := g_user_id;
639 released_dist_tab(j).last_updated_by := g_user_id;
640 released_dist_tab(j).last_update_date := sysdate;
641 released_dist_tab(j).last_update_login := g_login_id;
642
643 released_dist_tab(j).assets_addition_flag := retained_dist_tab(j).assets_addition_flag;
644 released_dist_tab(j).assets_tracking_flag := 'N';
645 released_dist_tab(j).asset_book_type_code := NULL;
646 released_dist_tab(j).asset_category_id := NULL;
647
648 released_dist_tab(j).inventory_transfer_status := 'N';
649 released_dist_tab(j).reference_1 := retained_dist_tab(j).reference_1;
650 released_dist_tab(j).reference_2 := retained_dist_tab(j).reference_2;
651
652 released_dist_tab(j).project_id := retained_dist_tab(j).project_id;
653 released_dist_tab(j).task_id := retained_dist_tab(j).task_id;
654 released_dist_tab(j).expenditure_type := retained_dist_tab(j).expenditure_type;
655 released_dist_tab(j).expenditure_item_date := retained_dist_tab(j).expenditure_item_date;
656 released_dist_tab(j).expenditure_organization_id := retained_dist_tab(j).expenditure_organization_id;
657 released_dist_tab(j).pa_quantity := retained_dist_tab(j).pa_quantity;
658 released_dist_tab(j).pa_addition_flag := retained_dist_tab(j).pa_addition_flag;
659 released_dist_tab(j).pa_cc_ar_invoice_id := retained_dist_tab(j).pa_cc_ar_invoice_id;
660 released_dist_tab(j).pa_cc_ar_invoice_line_num := retained_dist_tab(j).pa_cc_ar_invoice_line_num;
661 released_dist_tab(j).pa_cc_processed_code := retained_dist_tab(j).pa_cc_processed_code;
662 released_dist_tab(j).award_id := retained_dist_tab(j).award_id;
663 released_dist_tab(j).gms_burdenable_raw_cost := retained_dist_tab(j).gms_burdenable_raw_cost;
664
665 released_dist_tab(j).attribute_category := retained_dist_tab(j).attribute_category;
666 released_dist_tab(j).attribute1 := retained_dist_tab(j).attribute1;
667 released_dist_tab(j).attribute2 := retained_dist_tab(j).attribute2;
668 released_dist_tab(j).attribute3 := retained_dist_tab(j).attribute3;
669 released_dist_tab(j).attribute4 := retained_dist_tab(j).attribute4;
670 released_dist_tab(j).attribute5 := retained_dist_tab(j).attribute5;
671 released_dist_tab(j).attribute6 := retained_dist_tab(j).attribute6;
672 released_dist_tab(j).attribute7 := retained_dist_tab(j).attribute7;
673 released_dist_tab(j).attribute8 := retained_dist_tab(j).attribute8;
674 released_dist_tab(j).attribute9 := retained_dist_tab(j).attribute9;
678 released_dist_tab(j).attribute13 := retained_dist_tab(j).attribute13;
675 released_dist_tab(j).attribute10 := retained_dist_tab(j).attribute10;
676 released_dist_tab(j).attribute11 := retained_dist_tab(j).attribute11;
677 released_dist_tab(j).attribute12 := retained_dist_tab(j).attribute12;
679 released_dist_tab(j).attribute14 := retained_dist_tab(j).attribute14;
680 released_dist_tab(j).attribute15 := retained_dist_tab(j).attribute15;
681 released_dist_tab(j).global_attribute_category := retained_dist_tab(j).global_attribute_category;
682 released_dist_tab(j).global_attribute1 := retained_dist_tab(j).global_attribute1;
683 released_dist_tab(j).global_attribute2 := retained_dist_tab(j).global_attribute2;
684 released_dist_tab(j).global_attribute3 := retained_dist_tab(j).global_attribute3;
685 released_dist_tab(j).global_attribute4 := retained_dist_tab(j).global_attribute4;
686 released_dist_tab(j).global_attribute5 := retained_dist_tab(j).global_attribute5;
687 released_dist_tab(j).global_attribute6 := retained_dist_tab(j).global_attribute6;
688 released_dist_tab(j).global_attribute7 := retained_dist_tab(j).global_attribute7;
689 released_dist_tab(j).global_attribute8 := retained_dist_tab(j).global_attribute8;
690 released_dist_tab(j).global_attribute9 := retained_dist_tab(j).global_attribute9;
691 released_dist_tab(j).global_attribute10 := retained_dist_tab(j).global_attribute10;
692 released_dist_tab(j).global_attribute11 := retained_dist_tab(j).global_attribute11;
693 released_dist_tab(j).global_attribute12 := retained_dist_tab(j).global_attribute12;
694 released_dist_tab(j).global_attribute13 := retained_dist_tab(j).global_attribute13;
695 released_dist_tab(j).global_attribute14 := retained_dist_tab(j).global_attribute14;
696 released_dist_tab(j).global_attribute15 := retained_dist_tab(j).global_attribute15;
697 released_dist_tab(j).global_attribute16 := retained_dist_tab(j).global_attribute16;
698 released_dist_tab(j).global_attribute17 := retained_dist_tab(j).global_attribute17;
699 released_dist_tab(j).global_attribute18 := retained_dist_tab(j).global_attribute18;
700 released_dist_tab(j).global_attribute19 := retained_dist_tab(j).global_attribute19;
701 released_dist_tab(j).global_attribute20 := retained_dist_tab(j).global_attribute20;
702 released_dist_tab(j).intended_use := retained_dist_tab(j).intended_use;
703 released_dist_tab(j).cash_posted_flag := 'N'; --bug 16227625
704 released_dist_tab(j).accrual_posted_flag := 'N'; -- bug 16227625
705
706 IF (released_dist_tab(j).amount >= l_max_dist_amount) THEN
707 l_rounding_index := j;
708 l_max_dist_amount := released_dist_tab(j).amount;
709 END IF;
710
711 -- start bug13090879
712 released_dist_tab(j).awt_group_id := retained_dist_tab(j).awt_group_id;
713 released_dist_tab(j).pay_awt_group_id := retained_dist_tab(j).pay_awt_group_id;
714 -- end bug13090879
715
716 l_sum_amount := l_sum_amount + released_dist_tab(j).amount;
717 l_sum_base_amount := l_sum_base_amount + released_dist_tab(j).base_amount;
718
719 l_distribution_line_number := l_distribution_line_number + 1;
720
721 release_amount_tab(j) := released_dist_tab(j).amount;
722 retained_inv_dist_id_tab(j) := retained_dist_tab(j).invoice_distribution_id;
723
724 End Loop;
725
726 IF l_rounding_index Is Not Null THEN
727
728 IF l_sum_amount <> x_released_lines_tab(i).amount THEN
729
730 released_dist_tab(l_rounding_index).amount := released_dist_tab(l_rounding_index).amount +
731 (x_released_lines_tab(i).amount - l_sum_amount);
732
733 release_amount_tab(l_rounding_index) := released_dist_tab(l_rounding_index).amount;
734
735 END IF;
736
737 IF g_release_invoice_info.exchange_rate Is Not Null THEN
738
739 IF l_sum_base_amount <> x_released_lines_tab(i).base_amount THEN
740
741 released_dist_tab(l_rounding_index).rounding_amt := x_released_lines_tab(i).base_amount -
742 l_sum_base_amount;
743
744 released_dist_tab(l_rounding_index).base_amount := released_dist_tab(l_rounding_index).base_amount +
745 released_dist_tab(l_rounding_index).rounding_amt;
746 END IF;
747 END IF;
748 END IF;
749
750 -----------------------------------------------------------------
751 l_debug_info := 'Step 5: Update PO Shipment and Distributions';
752 log(l_api_name,l_debug_info);
753 -----------------------------------------------------------------
754
755 Update_PO_Shipment_Dists (x_released_lines_tab(i).po_line_location_id,
756 x_released_lines_tab(i).amount,
757 released_dist_tab);
758
759 -----------------------------------------------------------------
760 l_debug_info := 'Step 6: Insert Retainage Release Distributions: Count: '||released_dist_tab.count;
761 log(l_api_name,l_debug_info);
762 -----------------------------------------------------------------
763
764 IF released_dist_tab.count > 0 THEN
765
766 FORALL k in released_dist_tab.first .. released_dist_tab.last
767
768 INSERT INTO ap_invoice_distributions_all VALUES released_dist_tab(k);
769
770 END IF;
771
772 -----------------------------------------------------------------
773 l_debug_info := 'Step 7: Update original invoice line retained amount remaining';
774 log(l_api_name,l_debug_info);
775 -----------------------------------------------------------------
776
777 IF retained_inv_dist_id_tab.count > 0 THEN
778
779 FORALL i in retained_inv_dist_id_tab.first .. retained_inv_dist_id_tab.last
780
781 UPDATE ap_invoice_distributions_all
782 SET retained_amount_remaining = (abs(retained_amount_remaining) - abs(release_amount_tab(i)))
783 WHERE invoice_distribution_id = retained_inv_dist_id_tab(i);
784
785 END IF;
786
787 IF (x_released_lines_tab(i).po_line_location_id IS NOT NULL) THEN
788
789 SELECT pl.recoupment_rate
790 INTO l_recoupment_rate
791 FROM po_line_locations_all pll, po_lines_all pl
792 WHERE pll.line_location_id = x_released_lines_tab(i).po_line_location_id
793 AND pll.po_line_id = pl.po_line_id;
794
795 END IF;
796
797 IF (l_recoupment_rate IS NOT NULL) THEN
798
799 l_amount_to_recoup := ap_utilities_pkg.ap_round_currency(
800 (x_released_lines_tab(i).amount * l_recoupment_rate / 100), g_release_invoice_info.invoice_currency_code);
801
802 l_success := ap_matching_utils_pkg.ap_recoup_invoice_line(
803 P_Invoice_Id => x_released_lines_tab(i).invoice_id,
804 P_Invoice_Line_Number => x_released_lines_tab(i).line_number,
805 P_Amount_To_Recoup => l_amount_to_recoup,
806 P_Po_Line_Id => x_released_lines_tab(i).po_line_id,
807 P_Vendor_Id => g_release_invoice_info.vendor_id,
808 P_Vendor_Site_Id => g_release_invoice_info.vendor_site_id,
809 P_Accounting_Date => g_release_invoice_info.gl_date,
810 P_Period_Name => g_period_name,
811 P_User_Id => g_user_id,
812 P_Last_Update_Login => g_login_id,
813 P_Error_Message => l_error_message,
814 P_Calling_Sequence => 'AP_RETAINAGE_RELEASE_PKG.CREATE_RELEASE_DISTRIBUTIONS');
815
816 END IF;
817
818 END IF;
819 END LOOP;
820
821 log(l_api_name, 'Create_Release_Distributions (-)');
822
823 EXCEPTION
824 WHEN OTHERS THEN
825 log(l_api_name,'Error: '||sqlerrm);
826 APP_EXCEPTION.RAISE_EXCEPTION;
827
828 End create_release_distributions;
829
830 Procedure final_release_rounding (x_line_location_id IN po_line_locations_all.line_location_id%type,
831 x_max_invoice_dist_id IN NUMBER) Is
832
833 l_sum_retained_base_amount NUMBER;
834 l_sum_released_base_amount NUMBER;
835 l_final_release_rounding_amt NUMBER;
836
837 Begin
838
839 SELECT sum(ap_utilities_pkg.ap_round_currency(ail.retained_amount * ai.exchange_rate, ai.invoice_currency_code))
840 INTO l_sum_retained_base_amount
841 FROM ap_invoices ai,
842 ap_invoice_lines ail
843 WHERE ai.invoice_id = ail.invoice_id
844 AND ail.po_line_location_id = x_line_location_id
845 AND ail.line_type_lookup_code = 'ITEM'
846 AND NVL(ail.discarded_flag,'N') <> 'Y';
847
848 SELECT sum(base_amount)
849 INTO l_sum_released_base_amount
850 FROM ap_invoice_lines ail
851 WHERE (ail.retained_invoice_id, ail.retained_line_number)
852 IN
853 (Select invoice_id, line_number
854 From ap_invoice_lines ail2
855 Where ail.po_line_location_id = x_line_location_id
856 And ail.line_type_lookup_code = 'ITEM'
857 And nvl(ail.discarded_flag,'N') <> 'Y');
858
859 l_final_release_rounding_amt := l_sum_retained_base_amount - l_sum_released_base_amount;
860
861 UPDATE ap_invoice_distributions
862 SET final_release_rounding = l_final_release_rounding_amt
863 WHERE invoice_distribution_id = x_max_invoice_dist_id;
864
865 End final_release_rounding;
866
867 Procedure Lock_Retained_Invoices (x_retained_lines_tab IN retainedLinesType,
868 x_lock_status OUT NOCOPY BOOLEAN) As
869 Pragma Autonomous_Transaction;
870
871 TYPE invIDType IS TABLE OF ap_invoice_lines_all.invoice_id%type INDEX BY PLS_INTEGER;
872 TYPE lineNumType IS TABLE OF ap_invoice_lines_all.line_number%type INDEX BY PLS_INTEGER;
873
874 lock_invoice_id_tab invIDType;
875 lock_line_number_tab lineNumType;
876
877 Begin
878
879 SAVEPOINT lock_invoice;
880
881 FOR i in x_retained_lines_tab.first .. x_retained_lines_tab.last
882 LOOP
883 lock_invoice_id_tab(i) := x_retained_lines_tab(i).invoice_id;
884 lock_line_number_tab(i) := x_retained_lines_tab(i).line_number;
885 END LOOP;
886
887 FORALL i in x_retained_lines_tab.first .. x_retained_lines_tab.last
888
889 Update ap_invoice_lines
890 Set line_selected_for_release_flag = 'Y'
891 Where invoice_id = lock_invoice_id_tab(i)
892 And line_number = lock_line_number_tab(i);
893
894 x_lock_status := SQL%ROWCOUNT > 0;
895
896 COMMIT;
897
898 Exception
899
900 When Others Then
901
902 x_lock_status := FALSE;
903
904 ROLLBACK To lock_invoice;
905
906 End Lock_Retained_Invoices;
907
908 Procedure Unlock_Retained_Invoices (x_retained_lines_tab IN retainedLinesType,
909 x_lock_status OUT NOCOPY BOOLEAN) As
910 Pragma Autonomous_Transaction;
911
912 TYPE invIDType IS TABLE OF ap_invoice_lines_all.invoice_id%type INDEX BY PLS_INTEGER;
913 TYPE lineNumType IS TABLE OF ap_invoice_lines_all.line_number%type INDEX BY PLS_INTEGER;
914
915 lock_invoice_id_tab invIDType;
916 lock_line_number_tab lineNumType;
917
918 l_debug_info Varchar2(240);
919 l_api_name Constant Varchar2(100) := 'Unlock_Retained_Invoices';
920
921 Begin
922
923 SAVEPOINT unlock_invoice;
924
925 -----------------------------------------------------------------
926 l_debug_info := 'Step 1';
927 log(l_api_name, l_debug_info);
928 -----------------------------------------------------------------
929
930 FOR i in x_retained_lines_tab.first .. x_retained_lines_tab.last
931 LOOP
932 lock_invoice_id_tab(i) := x_retained_lines_tab(i).invoice_id;
933 lock_line_number_tab(i) := x_retained_lines_tab(i).line_number;
934 END LOOP;
935
936 -----------------------------------------------------------------
937 l_debug_info := 'Step 2';
938 log(l_api_name, l_debug_info);
939 -----------------------------------------------------------------
940
941 FORALL i in x_retained_lines_tab.first .. x_retained_lines_tab.last
942
943 Update ap_invoice_lines
944 Set line_selected_for_release_flag = NULL
945 Where invoice_id = lock_invoice_id_tab(i)
946 And line_number = lock_line_number_tab(i);
947
948 -----------------------------------------------------------------
949 l_debug_info := 'Step 3';
950 log(l_api_name, l_debug_info);
951 -----------------------------------------------------------------
952
953 x_lock_status := SQL%ROWCOUNT > 0;
954
955 COMMIT;
956
957 Exception
958
959 When Others Then
960
961 x_lock_status := FALSE;
962
963 ROLLBACK To unlock_invoice;
964
965 End Unlock_Retained_Invoices;
966
967 Procedure Update_PO_Shipment_Dists
968 (x_line_location_id IN ap_invoice_lines_all.po_line_location_id%type,
969 x_released_amount IN ap_invoice_lines_all.retained_amount%type,
970 x_released_dist_tab IN relDistType) As
971
972 l_po_ap_dist_rec PO_AP_DIST_REC_TYPE;
973 l_po_ap_line_loc_rec PO_AP_LINE_LOC_REC_TYPE;
974
975 l_return_status VARCHAR2(100);
976 l_msg_data VARCHAR2(4000);
977
978 l_api_name VARCHAR2(50);
979 l_debug_info VARCHAR2(2000);
980
981 BEGIN
982
983 l_api_name := 'Update_PO_Shipment_Dists';
984
985 l_po_ap_dist_rec := PO_AP_DIST_REC_TYPE.create_object();
986
987 l_po_ap_line_loc_rec := PO_AP_LINE_LOC_REC_TYPE.create_object(
988 p_po_line_location_id => x_line_location_id,
989 p_uom_code => NULL,
990 p_quantity_billed => NULL,
991 p_amount_billed => NULL,
992 p_quantity_financed => NULL,
993 p_amount_financed => NULL,
994 p_quantity_recouped => NULL,
995 p_amount_recouped => NULL,
996 p_retainage_withheld_amt => NULL,
997 p_retainage_released_amt => x_released_amount
998 );
999
1000 FOR i in nvl(x_released_dist_tab.first,0)..nvl(x_released_dist_tab.last,0)
1001 LOOP
1002
1003 IF (x_released_dist_tab.exists(i)) THEN
1004
1005 l_po_ap_dist_rec.add_change
1006 (p_po_distribution_id => x_released_dist_tab(i).po_distribution_id,
1007 p_uom_code => NULL,
1008 p_quantity_billed => NULL,
1009 p_amount_billed => NULL,
1010 p_quantity_financed => NULL,
1011 p_amount_financed => NULL,
1012 p_quantity_recouped => NULL,
1013 p_amount_recouped => NULL,
1014 p_retainage_withheld_amt => NULL,
1015 p_retainage_released_amt => x_released_dist_tab(i).amount);
1016
1017
1018 END IF;
1019
1020 END LOOP;
1021
1022 PO_AP_INVOICE_MATCH_GRP.Update_Document_Ap_Values(
1023 P_Api_Version => 1.0,
1024 P_Line_Loc_Changes_Rec => l_po_ap_line_loc_rec,
1025 P_Dist_Changes_Rec => l_po_ap_dist_rec,
1026 X_Return_Status => l_return_status,
1027 X_Msg_Data => l_msg_data);
1028 END Update_PO_Shipment_Dists;
1029
1030
1031 PROCEDURE log (x_api_name IN varchar2,
1032 x_debug_info IN varchar2) As
1033
1034 BEGIN
1035 IF (g_level_procedure >= g_current_runtime_level) THEN
1036 fnd_log.string(g_level_procedure,g_module_name||x_api_name,x_debug_info);
1037 END IF;
1038 END log;
1039
1040 End ap_retainage_release_pkg;