[Home] [Help]
PACKAGE BODY: APPS.AP_PREPAY_PKG
Source
1 PACKAGE BODY AP_PREPAY_PKG AS
2 /*$Header: aprepayb.pls 120.62.12020000.5 2012/10/08 07:04:46 harsanan ship $*/
3
4
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AP_PREPAY_PKG';
6 G_MSG_UERROR CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR;
7 G_MSG_ERROR CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_ERROR;
8 G_MSG_SUCCESS CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_SUCCESS;
9 G_MSG_HIGH CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH;
10 G_MSG_MEDIUM CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM;
11 G_MSG_LOW CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW;
12 G_LINES_PER_FETCH CONSTANT NUMBER := 1000;
13
14 G_CURRENT_RUNTIME_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
15 G_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
16 G_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
17 G_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
18 G_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
19 G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
20 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
21 G_MODULE_NAME CONSTANT VARCHAR2(100) := 'AP.PLSQL.AP_PREPAY_PKG.';
22
23 FUNCTION Check_Supplier_Consistency (
24 p_prepay_num IN VARCHAR2,
25 p_vendor_id IN NUMBER) RETURN VARCHAR2
26 IS
27
28 l_reject_code VARCHAR2(30) := NULL;
29
30 CURSOR C_Supplier_Consistent(
31 cv_prepay_num VARCHAR2,
32 cv_vendor_id NUMBER)
33 IS
34 SELECT DECODE(COUNT(*),
35 0, 'INCONSISTENT PREPAY SUPPL', NULL)
36 FROM ap_invoices ai
37 WHERE ai.invoice_num = cv_prepay_num
38 AND ai.vendor_id = cv_vendor_id;
39
40 BEGIN
41
42 OPEN C_Supplier_Consistent
43 (p_prepay_num,
44 p_vendor_id);
45
46 FETCH C_Supplier_Consistent
47 INTO l_reject_code;
48
49 CLOSE C_Supplier_Consistent;
50
51 RETURN (l_reject_code);
52
53 EXCEPTION
54 WHEN OTHERS THEN
55 IF C_Supplier_Consistent%ISOPEN THEN
56 CLOSE C_Supplier_Consistent;
57 END IF;
58 APP_EXCEPTION.RAISE_EXCEPTION;
59
60 END Check_Supplier_Consistency;
61
62
63 FUNCTION Check_Currency_Consistency (
64 p_prepay_num IN VARCHAR2,
65 p_vendor_id IN NUMBER,
66 p_base_currency_code IN VARCHAR2,
67 p_invoice_currency_code IN VARCHAR2,
68 p_payment_currency_code IN VARCHAR2) RETURN VARCHAR2
69 IS
70
71 l_reject_code VARCHAR2(30) := NULL;
72
73 CURSOR C_Currency_Consistent
74 (cv_prepay_num VARCHAR2,
75 cv_vendor_id NUMBER,
76 cv_base_currency_code VARCHAR2,
77 cv_invoice_currency_code VARCHAR2,
78 cv_payment_currency_code VARCHAR2) IS
79 SELECT DECODE(COUNT(*),
80 0, 'INCONSISTENT PREPAY CURR', NULL)
81 FROM ap_invoices ai
82 WHERE invoice_num = cv_prepay_num
83 AND vendor_id = cv_vendor_id
84 AND cv_base_currency_code =
85 (SELECT base_currency_code
86 FROM ap_system_parameters)
87 AND ai.invoice_currency_code = cv_invoice_currency_code
88 AND ai.payment_currency_code = NVL(cv_payment_currency_code,
89 cv_invoice_currency_code);
90 BEGIN
91
92 OPEN C_Currency_Consistent
93 (p_prepay_num,
94 p_vendor_id,
95 p_base_currency_code,
96 p_invoice_currency_code,
97 p_payment_currency_code);
98
99 FETCH C_Currency_Consistent
100 INTO l_reject_code;
101
102 CLOSE C_Currency_Consistent;
103
104 RETURN (l_reject_code);
105
106 EXCEPTION
107 WHEN OTHERS THEN
108 IF C_Currency_Consistent%ISOPEN THEN
109 CLOSE C_Currency_Consistent;
110 END IF;
111 APP_EXCEPTION.RAISE_EXCEPTION;
112
113 END Check_Currency_Consistency;
114
115
116 FUNCTION Check_Prepayment_Invoice (
117 p_prepay_num IN VARCHAR2,
118 p_vendor_id IN VARCHAR2,
119 p_prepay_invoice_id OUT NOCOPY NUMBER) RETURN VARCHAR2
120 IS
121
122 l_reject_code VARCHAR2(30) := NULL;
123 l_api_name VARCHAR2(50);
124 l_count NUMBER;
125 l_debug_info VARCHAR2(4000);
126
127 BEGIN
128 l_api_name := 'Check_Prepayment_Invoice';
129 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
130 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_PREPAY_PKG.Check_Prepayment_Invoice(+)');
131 END IF;
132
133
134 l_debug_info := 'Check if the prepayment invoice is a valid invoice ';
135 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
136 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
137 END IF;
138
139 SELECT count(*)
140 INTO l_count
141 FROM ap_invoices ai
142 WHERE ai.invoice_num = p_prepay_num
143 AND ai.invoice_type_lookup_code = 'PREPAYMENT'
144 AND ai.payment_status_flag = 'Y'
145 AND ai.vendor_id = p_vendor_id
146 AND NVL(ai.earliest_settlement_date,sysdate+1) <= SYSDATE;
147
148 IF l_count = 0 THEN
149 l_reject_code := 'INVALID PREPAY INFO';
150 END IF;
151
152 l_debug_info := 'Get prepay_invoice_id ';
153 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
154 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
155 END IF;
156
157 IF l_reject_code IS NULL THEN
158 SELECT invoice_id
159 INTO p_prepay_invoice_id
160 FROM ap_invoices
161 WHERE invoice_num = p_prepay_num
162 AND vendor_id = p_vendor_id;
163 END IF;
164
165 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
166 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_PREPAY_PKG.Check_Prepayment_Invoice(-)');
167 END IF;
168
169 RETURN (l_reject_code);
170
171 EXCEPTION
172 WHEN OTHERS THEN
173 app_exception.raise_exception;
174 END Check_Prepayment_Invoice;
175
176
177 FUNCTION Check_Prepayment_Line (
178 p_prepay_num IN VARCHAR2,
179 p_prepay_line_num IN NUMBER,
180 p_vendor_id IN NUMBER) RETURN VARCHAR2
181 IS
182
183 l_reject_code VARCHAR2(30) := NULL;
184
185 CURSOR C_Check_Prepay_Line_Apply_Info
186 (cv_prepay_num VARCHAR2,
187 cv_prepay_line_num NUMBER,
188 cv_vendor_id NUMBER)
189 IS
190 SELECT DECODE(COUNT(*),
191 0, 'INVALID PREPAY LINE NUM', NULL)
192 FROM ap_invoices ai,
193 ap_invoice_lines ail
194 WHERE ai.invoice_num = cv_prepay_num
195 AND ail.line_number = cv_prepay_line_num
196 AND ail.invoice_id = ai.invoice_id
197 AND ai.invoice_type_lookup_code = 'PREPAYMENT'
198 AND ai.payment_status_flag = 'Y'
199 AND ai.vendor_id = cv_vendor_id
200 AND NVL(ai.earliest_settlement_date,sysdate+1) <= SYSDATE
201 AND ail.line_type_lookup_code = 'ITEM'
202 AND NVL(ail.discarded_flag,'N') <> 'Y';
203
204 BEGIN
205
206 OPEN C_Check_Prepay_Line_Apply_Info
207 (p_prepay_num,
208 p_prepay_line_num,
209 p_vendor_id);
210
211 FETCH C_Check_Prepay_Line_Apply_Info
212 INTO l_reject_code;
213
214 CLOSE C_Check_Prepay_Line_Apply_Info;
215
216 RETURN (l_reject_code);
217
218 EXCEPTION
219 WHEN OTHERS THEN
220 IF C_Check_Prepay_Line_Apply_Info %ISOPEN THEN
221 CLOSE C_Check_Prepay_Line_Apply_Info;
222 END IF;
223 APP_EXCEPTION.RAISE_EXCEPTION;
224
225 END Check_Prepayment_Line;
226
227
228 FUNCTION Check_Nothing_To_Apply_Line (
229 p_prepay_invoice_id IN NUMBER,
230 p_prepay_line_num IN NUMBER) RETURN VARCHAR2
231 IS
232 l_prepay_amount_remaining NUMBER;
233 l_reject_code VARCHAR2(30) := NULL;
234
235 CURSOR C_Check_No_Apply_Line_Amount
236 (cv_prepay_invoice_id NUMBER,
237 cv_prepay_line_num NUMBER)
238 IS
239 SELECT SUM(NVL(aid.prepay_amount_remaining, aid.total_dist_amount))
240 FROM ap_invoice_lines_all ail,
241 ap_invoice_distributions_all aid
242 WHERE ail.invoice_id = cv_prepay_invoice_id
243 AND ail.line_number = cv_prepay_line_num
244 AND NVL(ail.line_selected_for_appl_flag,'N') <> 'Y'
245 AND aid.invoice_id = ail.invoice_id
246 AND aid.invoice_line_number = ail.line_number
247 AND aid.line_type_lookup_code IN ('ITEM', 'ACCRUAL', 'REC_TAX', 'NONREC_TAX')
248 -- Included tax distributions for inclusive tax if any
249 AND NVL(aid.reversal_flag,'N') <> 'Y';
250
251 BEGIN
252
253 OPEN C_Check_No_Apply_Line_Amount
254 (p_prepay_invoice_id,
255 p_prepay_line_num);
256
257
258 FETCH C_Check_No_Apply_Line_Amount
259 INTO l_prepay_amount_remaining;
260
261 CLOSE C_Check_No_Apply_Line_Amount;
262
263 IF NVL(l_prepay_amount_remaining,0) <= 0 THEN
264 l_reject_code := 'NOTHING TO APPLY';
265 END IF;
266
267 RETURN (l_reject_code);
268
269 EXCEPTION
270 WHEN OTHERS THEN
271 IF C_Check_No_Apply_Line_Amount %ISOPEN THEN
272 CLOSE C_Check_No_Apply_Line_Amount;
273 END IF;
274 APP_EXCEPTION.RAISE_EXCEPTION;
275
276 END Check_Nothing_To_Apply_Line;
277
278
279 FUNCTION Check_Nothing_To_Apply_Invoice (
280 p_prepay_invoice_id IN NUMBER) RETURN VARCHAR2
281 IS
282 l_prepay_amount_remaining NUMBER;
283 l_reject_code VARCHAR2(30) := NULL;
284
285 CURSOR C_Check_No_Apply_Inv_Amount
286 (cv_prepay_invoice_id NUMBER)
287 IS
288 SELECT SUM(NVL(aid.prepay_amount_remaining, aid.total_dist_amount))
289 FROM ap_invoice_lines_all ail,
290 ap_invoice_distributions_all aid
291 WHERE ail.invoice_id = cv_prepay_invoice_id
292 AND ail.line_type_lookup_code <> 'TAX'
293 AND NVL(ail.line_selected_for_appl_flag,'N') <> 'Y'
294 AND aid.invoice_id = ail.invoice_id
295 AND aid.invoice_line_number = ail.line_number
296 AND aid.line_type_lookup_code IN ('ITEM', 'ACCRUAL', 'REC_TAX', 'NONREC_TAX')
297 AND NVL(aid.reversal_flag,'N') <> 'Y';
298 -- Included tax distributions for inclusive tax if any and excluded
299 -- any tax line (exclusive case)
300
301 BEGIN
302
303 OPEN C_Check_No_Apply_Inv_Amount
304 (p_prepay_invoice_id);
305
306 FETCH C_Check_No_Apply_Inv_Amount
307 INTO l_prepay_amount_remaining;
308
309 CLOSE C_Check_No_Apply_Inv_Amount;
310
311 IF NVL(l_prepay_amount_remaining,0) <= 0 THEN
312 l_reject_code := 'NOTHING TO APPLY';
313 END IF;
314
315 RETURN (l_reject_code);
316
317 EXCEPTION
318 WHEN OTHERS THEN
319 IF C_Check_No_Apply_Inv_Amount %ISOPEN THEN
320 CLOSE C_Check_No_Apply_Inv_Amount;
321 END IF;
322 APP_EXCEPTION.RAISE_EXCEPTION;
323
324 END Check_Nothing_To_Apply_Invoice;
325
326
327 FUNCTION Check_Nothing_To_Apply_Vendor (
328 p_vendor_id IN NUMBER) RETURN VARCHAR2
329 IS
330 l_prepay_amount_remaining NUMBER;
331 l_reject_code VARCHAR2(30) := NULL;
332
333 CURSOR C_Check_No_Apply_Vendor_Amount
334 (cv_vendor_id NUMBER)
335 IS
336 SELECT SUM(NVL(aid.prepay_amount_remaining, aid.total_dist_amount))
337 FROM ap_invoices ai,
338 ap_invoice_lines ail,
339 ap_invoice_distributions aid
340 WHERE ai.vendor_id = cv_vendor_id
341 AND ai.invoice_type_lookup_code = 'PREPAYMENT'
342 AND ai.payment_status_flag = 'Y'
343 AND NVL(ai.earliest_settlement_date,sysdate+1) <= SYSDATE
344 AND ail.invoice_id = ai.invoice_id
345 AND ail.line_type_lookup_code = 'ITEM'
346 -- this will make sure exclusive TAX lines are not included
347 AND NVL(ail.discarded_flag,'N') <> 'Y'
348 AND NVL(ail.line_selected_for_appl_flag,'N') <> 'Y'
349 AND aid.invoice_id = ail.invoice_id
350 AND aid.invoice_line_number = ail.line_number
351 AND aid.line_type_lookup_code IN ('ITEM', 'ACCRUAL', 'REC_TAX', 'NONREC_TAX')
352 AND NVL(aid.reversal_flag,'N') <> 'Y';
353 -- Included inclusive tax amount
354
355 BEGIN
356
357 OPEN C_Check_No_Apply_Vendor_Amount
358 (p_vendor_id);
359
360 FETCH C_Check_No_Apply_Vendor_Amount
361 INTO l_prepay_amount_remaining;
362
363 CLOSE C_Check_No_Apply_Vendor_Amount;
364
365 IF NVL(l_prepay_amount_remaining,0) <= 0 THEN
366 l_reject_code := 'NOTHING TO APPLY';
367 END IF;
368
369 RETURN (l_reject_code);
370
371 EXCEPTION
372 WHEN OTHERS THEN
373 IF C_Check_No_Apply_Vendor_Amount %ISOPEN THEN
374 CLOSE C_Check_No_Apply_Vendor_Amount;
375 END IF;
376 APP_EXCEPTION.RAISE_EXCEPTION;
377
378 END Check_Nothing_To_Apply_Vendor;
379
380
381 FUNCTION Check_Period_Status (
382 p_prepay_gl_date IN OUT NOCOPY DATE,
383 p_prepay_period_name IN OUT NOCOPY VARCHAR2) RETURN VARCHAR2
384 IS
385
386 l_reject_code VARCHAR2(30) := NULL;
387
388 CURSOR C_Get_Period_Name
389 (cv_gl_date DATE) IS
390 SELECT G.period_name
391 FROM gl_period_statuses G,
392 ap_system_parameters P
393 WHERE G.application_id = 200
394 AND G.set_of_books_id = P.set_of_books_id
395 AND TRUNC(cv_gl_date) BETWEEN G.start_date AND
396 G.end_date
397 AND G.closing_status IN ('O', 'F')
398 AND NVL(G.adjustment_period_flag, 'N') = 'N';
399
400 BEGIN
401
402 IF p_prepay_gl_date IS NULL THEN
403 P_prepay_gl_date := SYSDATE;
404 END IF;
405
406 OPEN C_Get_Period_Name
407 (p_prepay_gl_date);
408
409 FETCH C_Get_Period_Name
410 INTO p_prepay_period_name;
411
412 IF C_Get_Period_Name%NOTFOUND THEN
413 l_reject_code := 'PP GL DATE IN CLOSED PD';
414 END IF;
415
416 CLOSE c_get_period_name;
417
418 RETURN (l_reject_code);
419
420 EXCEPTION
421 WHEN OTHERS THEN
422 IF C_Get_Period_Name %ISOPEN THEN
423 CLOSE C_Get_Period_Name;
424 END IF;
425 APP_EXCEPTION.RAISE_EXCEPTION;
426
427 END Check_Period_Status;
428
429
430 FUNCTION Validate_Prepay_Info (
431 p_prepay_case_name IN VARCHAR2,
432 p_prepay_num IN OUT NOCOPY VARCHAR2,
433 p_prepay_line_num IN OUT NOCOPY NUMBER,
434 p_prepay_apply_amount IN OUT NOCOPY NUMBER, -- Bug 7004765
435 p_invoice_amount IN NUMBER,
436 p_prepay_gl_date IN OUT NOCOPY DATE,
437 p_prepay_period_name IN OUT NOCOPY VARCHAR2,
438 p_vendor_id IN NUMBER,
439 p_import_invoice_id IN NUMBER,
440 p_source IN VARCHAR2,
441 p_apply_advances_flag IN VARCHAR2,
442 p_invoice_date IN DATE,
443 p_base_currency_code IN VARCHAR2,
444 p_invoice_currency_code IN VARCHAR2,
445 p_payment_currency_code IN VARCHAR2,
446 p_calling_sequence IN VARCHAR2,
447 p_prepay_invoice_id OUT NOCOPY NUMBER,
448 p_invoice_type_lookup_code IN VARCHAR2 DEFAULT NULL) RETURN VARCHAR2 -- Bug 7004765
449 IS
450
451 l_reject_code VARCHAR2(30) := NULL;
452 l_debug_info VARCHAR2(4000); --Changed length from 100 to 4000(8534097)
453 l_api_name VARCHAR2(50);
454
455 BEGIN
456 l_api_name := 'Validate_Prepay_Info';
457
458 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
459 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_PREPAY_PKG.Validate_Prepay_Info(+)');
460 END IF;
461
462 IF p_prepay_case_name = 'DO_NOTHING_CASE' THEN
463
464 l_debug_info := 'Import - No Prepay related information is given and '||
465 'apply advances flag is set to N';
466 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
467 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
468 END IF;
469
470 l_reject_code := NULL;
471 RETURN (l_reject_code);
472
473 ELSIF p_prepay_case_name = 'INVALID_CASE' THEN
474
475 l_debug_info := 'Import - Insufficient Prepayment information provided';
476 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
477 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
478 END IF;
479
480 l_reject_code := 'INSUFFICIENT PREPAY INFO';
481 RETURN (l_reject_code);
482
483 END IF;
484
485 IF p_prepay_case_name IN (
486 'LINE_PREPAY_APPL_WITH_AMOUNT',
487 'INV_PREPAY_APPL_WITH_AMOUNT',
488 'VND_PREPAY_APPL_WITH_AMOUNT') THEN
489
490 IF p_prepay_apply_amount <= 0 THEN
491
492 l_debug_info := 'Import - Apply amount should be positive and non zero';
493 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
494 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
495 END IF;
496
497 l_reject_code := 'CANNOT APPLY ZERO OR NEG';
498
499 RETURN (l_reject_code);
500
501 END IF;
502 END IF;
503
504 IF p_prepay_case_name IN (
505 'LINE_PREPAY_APPL_WITH_AMOUNT',
506 'INV_PREPAY_APPL_WITH_AMOUNT',
507 'LINE_PREPAY_APPL_WITHOUT_AMOUNT',
508 'INV_PREPAY_APPL_WITHOUT_AMOUNT') THEN
509
510 l_debug_info := 'Import - Check Supplier Consistency';
511 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
512 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
513 END IF;
514
515 l_reject_code := Check_Supplier_Consistency
516 (p_prepay_num,
517 p_vendor_id);
518
519 IF l_reject_code IS NOT NULL THEN
520 RETURN (l_reject_code);
521 END IF;
522
523 l_debug_info := 'Import - Check Currency Consistency';
524 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
525 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
526 END IF;
527
528 l_reject_code := Check_Currency_Consistency
529 (p_prepay_num,
530 p_vendor_id,
531 p_base_currency_code,
532 p_invoice_currency_code,
533 p_payment_currency_code);
534
535 IF l_reject_code IS NOT NULL THEN
536 RETURN (l_reject_code);
537 END IF;
538
539 l_debug_info := 'Import - Check Prepayment Validity';
540 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
541 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
542 END IF;
543
544 l_reject_code := Check_Prepayment_Invoice
545 (p_prepay_num,
546 p_vendor_id,
547 p_prepay_invoice_id);
548
549 IF l_reject_code IS NOT NULL THEN
550 RETURN (l_reject_code);
551 END IF;
552
553 IF p_prepay_case_name IN (
554 --'INV_PREPAY_APPL_WITH_AMOUNT', Bug5506845
555 'LINE_PREPAY_APPL_WITH_AMOUNT',
556 --'INV_PREPAY_APPL_WITHOUT_AMOUNT', Bug5506845
557 'LINE_PREPAY_APPL_WITHOUT_AMOUNT') THEN
558 l_debug_info := 'Import - Check Prepayment Line Validity';
559 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
560 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
561 END IF;
562
563 l_reject_code := Check_Prepayment_Line
564 (p_prepay_num,
565 p_prepay_line_num,
566 p_vendor_id);
567
568
569 IF l_reject_code IS NOT NULL THEN
570 RETURN (l_reject_code);
571 END IF;
572 END IF;
573
574 IF p_prepay_case_name IN (
575 'LINE_PREPAY_APPL_WITHOUT_AMOUNT',
576 'LINE_PREPAY_APPL_WITH_AMOUNT') THEN
577 l_debug_info := 'Import - Check Nothing to Apply in this Prepay Line';
578 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
579 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
580 END IF;
581
582 l_reject_code := Check_Nothing_To_Apply_Line
583 (p_prepay_invoice_id,
584 p_prepay_line_num);
585
586 IF l_reject_code IS NOT NULL THEN
587 IF p_invoice_type_lookup_code = 'EXPENSE REPORT' -- Bug 7004765
588 or p_source in ('SelfService', 'XpenseXpress') THEN
589 l_debug_info := 'Import - Nothing to Apply in this Prepay Line. But not REJECTING an ER invoice';
590 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
591 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
592 END IF;
593 p_prepay_apply_amount := null;
594 p_prepay_line_num := null;
595 p_prepay_num := null;
596 ELSE
597 RETURN (l_reject_code);
598 END IF;
599 END IF;
600 END IF;
601
602 IF p_prepay_case_name IN (
603 'INV_PREPAY_APPL_WITHOUT_AMOUNT',
604 'INV_PREPAY_APPL_WITH_AMOUNT') THEN
605 l_debug_info := 'Import - Check Nothing to Apply in this Prepay Invoice';
606 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
607 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
608 END IF;
609
610 l_reject_code := Check_Nothing_To_Apply_Invoice
611 (p_prepay_invoice_id);
612
613 IF l_reject_code IS NOT NULL THEN
614 IF p_invoice_type_lookup_code = 'EXPENSE REPORT' -- Bug 7004765
615 or p_source in ('SelfService', 'XpenseXpress') THEN
616 l_debug_info := 'Import - Nothing to Apply in this Prepay Line. But not REJECTING an ER invoice';
617 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
618 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
619 END IF;
620 p_prepay_apply_amount := null;
621 p_prepay_num := null;
622 ELSE
623 RETURN (l_reject_code);
624 END IF;
625 END IF;
626 END IF;
627 END IF;
628
629 IF p_prepay_case_name IN (
630 'VND_PREPAY_APPL_WITHOUT_AMOUNT',
631 'VND_PREPAY_APPL_WITH_AMOUNT') THEN
632 l_debug_info := 'Import - Check Nothing to Apply for this vendor';
633 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
634 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
635 END IF;
636
637 l_reject_code := Check_Nothing_To_Apply_Vendor (p_vendor_id);
638
639 IF l_reject_code IS NOT NULL THEN
640 IF p_invoice_type_lookup_code = 'EXPENSE REPORT' -- Bug 7004765
641 or p_source in ('SelfService', 'XpenseXpress') THEN
642 l_debug_info := 'Import - Nothing to Apply in this Prepay Line. But not REJECTING an ER invoice';
643 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
644 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
645 END IF;
646 p_prepay_apply_amount := null;
647 ELSE
648 RETURN (l_reject_code);
649 END IF;
650 END IF;
651 END IF;
652
653 l_debug_info := 'Import - Check Period Status';
654 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
655 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
656 END IF;
657
658 l_reject_code := Check_Period_Status
659 (p_prepay_gl_date,
660 p_prepay_period_name);
661
662 IF l_reject_code IS NOT NULL THEN
663 RETURN (l_reject_code);
664 END IF;
665
666 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
667 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_PREPAY_PKG.Validate_Prepay_Info(-)');
668 END IF;
669
670 RETURN (l_reject_code);
671
672 EXCEPTION
673 WHEN OTHERS THEN
674 APP_EXCEPTION.RAISE_EXCEPTION;
675 END Validate_Prepay_Info;
676
677
678 FUNCTION Get_Prepay_Case_Name (
679 p_prepay_num IN VARCHAR2,
680 p_prepay_line_num IN NUMBER,
681 p_prepay_apply_amount IN NUMBER,
682 p_source IN VARCHAR2,
683 p_apply_advances_flag IN VARCHAR2,
684 p_calling_sequence IN VARCHAR2) RETURN VARCHAR2
685 IS
686
687 l_prepay_case_name VARCHAR2(100);
688 l_api_name VARCHAR2(50);
689 l_debug_info VARCHAR2(4000);
690
691 BEGIN
692 l_api_name := 'Get_Prepay_Case_Name';
693 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
694 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_PREPAY_PKG.Get_Prepay_Case_Name(+)');
695 END IF;
696
697 l_debug_info := 'Derive case name';
698 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
699 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
700 END IF;
701
702 IF (p_prepay_num IS NOT NULL AND
703 p_prepay_line_num IS NOT NULL AND
704 p_prepay_apply_amount IS NOT NULL) THEN
705
706 l_prepay_case_name := 'LINE_PREPAY_APPL_WITH_AMOUNT';
707
708 ELSIF (p_prepay_num IS NOT NULL AND
709 p_prepay_line_num IS NOT NULL AND
710 p_prepay_apply_amount IS NULL) THEN
711
712 l_prepay_case_name := 'LINE_PREPAY_APPL_WITHOUT_AMOUNT';
713
714 ELSIF (p_prepay_num IS NOT NULL AND
715 p_prepay_line_num IS NULL AND
716 p_prepay_apply_amount IS NOT NULL) THEN
717
718 l_prepay_case_name := 'INV_PREPAY_APPL_WITH_AMOUNT';
719
720 ELSIF (p_prepay_num IS NOT NULL AND
721 p_prepay_line_num IS NULL AND
722 p_prepay_apply_amount IS NULL) THEN
723
724 l_prepay_case_name := 'INV_PREPAY_APPL_WITHOUT_AMOUNT';
725
726 ELSIF (p_prepay_num IS NULL AND
727 p_prepay_line_num IS NULL AND
728 p_prepay_apply_amount IS NOT NULL) THEN
729
730 l_prepay_case_name := 'VND_PREPAY_APPL_WITH_AMOUNT';
731
732 ELSIF (p_prepay_num IS NULL AND
733 p_prepay_line_num IS NULL AND
734 p_prepay_apply_amount IS NULL AND
735 p_source IN ('SelfService', 'XpenseXpress') AND
736 NVl(p_apply_advances_flag,'N') = 'Y') THEN
737
738 l_prepay_case_name := 'VND_PREPAY_APPL_WITHOUT_AMOUNT_EXP';
739
740 ELSIF (p_prepay_num IS NULL AND
741 p_prepay_line_num IS NULL AND
742 p_prepay_apply_amount IS NULL AND
743 p_source NOT IN ('SelfService', 'XpenseXpress') AND
744 NVl(p_apply_advances_flag,'N') = 'Y') THEN
745
746 l_prepay_case_name := 'VND_PREPAY_APPL_WITHOUT_AMOUNT';
747
748 ELSIF (p_prepay_num IS NULL AND
749 p_prepay_line_num IS NULL AND
750 p_prepay_apply_amount IS NULL AND
751 NVl(p_apply_advances_flag,'N') = 'N') THEN
752
753 l_prepay_case_name := 'DO_NOTHING_CASE';
754
755 ELSE
756 l_prepay_case_name := 'INVALID_CASE';
757
758 END IF;
759
760 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
761 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_PREPAY_PKG.Get_Prepay_Case_Name(-)');
762 END IF;
763
764 RETURN (l_prepay_case_name);
765
766 EXCEPTION
767 WHEN OTHERS THEN
768 APP_EXCEPTION.RAISE_EXCEPTION;
769 END Get_Prepay_Case_Name;
770
771
772 FUNCTION get_prepay_apply_amount(
773 p_prepay_case_name VARCHAR2,
774 p_prepay_invoice_id NUMBER,
775 P_prepay_line_num NUMBER,
776 p_prepay_apply_amount NUMBER,
777 p_invoice_id NUMBER,
778 p_vendor_id NUMBER,
779 p_prepay_included VARCHAR2) RETURN NUMBER
780 IS
781 l_prepay_apply_amount NUMBER := p_prepay_apply_amount;
782 l_invoice_amount_remaining NUMBER;
783 l_prepay_amount_remaining NUMBER;
784
785 BEGIN
786 -- Get the Invoice Amount Remaining for the standard invoice.
787 -- This is the unpaid amount. Since we are importing the invoice
788 -- there is no posibility of partial payment of the invoice, so
789 -- there is no need to verify the payment_status flag.
790
791 /* Start of fix for bug8692604 */
792 /* SELECT SUM(NVL(aid.amount,0))
793 INTO l_invoice_amount_remaining
794 FROM ap_invoice_distributions_all aid, ap_invoice_lines_all ail
795 WHERE ail.invoice_id = p_invoice_id
796 AND ail.invoice_id = aid.invoice_id
797 AND ail.line_number = aid.invoice_line_number
798 --Contract Payments: Although Recoupment is modelled as 'PREPAY' dists
799 --amount_recouped will effect the invoice_amount_remaining
800 --regardless of invoice_includes_prepay_flag.
801 AND ((ail.line_type_lookup_code = 'PREPAY' and
802 ((aid.line_type_lookup_code <> 'PREPAY'
803 and aid.prepay_distribution_id IS NULL
804 )
805 or NVL(ail.invoice_includes_prepay_flag,'N') = 'Y'
806 )
807 ) OR
808 (ail.line_type_lookup_code <> 'PREPAY')
809 ); */
810
811 select sum(nvl(amount,0))
812 into l_invoice_amount_remaining
813 from ap_invoice_lines_all
814 where invoice_id = p_invoice_id
815 and line_type_lookup_code <> 'PREPAY';
816 /* End of fix for bug8692604 */
817
818 -- Get the correct Prepay Amount Remaining and hence the correct
819 -- Apply Amount and Additional Amount if Applicable.
820
821 IF p_prepay_case_name IN ('LINE_PREPAY_APPL_WITH_AMOUNT',
822 'LINE_PREPAY_APPL_WITHOUT_AMOUNT') THEN
823
824 -- Get the Prepay Amount Remaining
825 SELECT SUM(NVL(aid.prepay_amount_remaining, aid.total_dist_amount))
826 INTO l_prepay_amount_remaining
827 FROM ap_invoice_lines_all ail,
828 ap_invoice_distributions_all aid
829 WHERE ail.invoice_id = p_prepay_invoice_id
830 AND ail.line_number = p_prepay_line_num
831 AND ail.line_type_lookup_code = 'ITEM'
832 AND NVL(ail.discarded_flag,'N') <> 'Y'
833 AND NVL(ail.line_selected_for_appl_flag,'N') <> 'Y'
834 AND aid.invoice_id = ail.invoice_id
835 AND aid.invoice_line_number = ail.line_number
836 AND aid.line_type_lookup_code
837 IN ('ITEM','ACCRUAL','REC_TAX','NONREC_TAX')
838 AND nvl(aid.reversal_flag,'N') <> 'Y';
839 -- eTax Uptake. Included inclusive tax distributions
840
841 IF p_prepay_apply_amount is NOT NULL THEN
842
843 IF (p_prepay_apply_amount > l_prepay_amount_remaining) THEN
844 l_prepay_apply_amount := l_prepay_amount_remaining;
845 END IF;
846
847 IF (l_invoice_amount_remaining < l_prepay_apply_amount) THEN
848 l_prepay_apply_amount := l_invoice_amount_remaining;
849 END IF;
850 ELSE
851 IF (l_invoice_amount_remaining <= l_prepay_amount_remaining) THEN
852 l_prepay_apply_amount := l_invoice_amount_remaining;
853 ELSE
854 l_prepay_apply_amount := l_prepay_amount_remaining;
855 END IF;
856 END IF;
857 END IF;
858
859 IF p_prepay_case_name IN ('INV_PREPAY_APPL_WITH_AMOUNT',
860 'INV_PREPAY_APPL_WITHOUT_AMOUNT') THEN
861
862 -- Get the Prepay Amount Remaining
863 SELECT SUM(NVL(aid.prepay_amount_remaining, aid.total_dist_amount))
864 INTO l_prepay_amount_remaining
865 FROM ap_invoice_lines_all ail,
866 ap_invoice_distributions_all aid
867 WHERE ail.invoice_id = p_prepay_invoice_id
868 AND ail.line_type_lookup_code = 'ITEM'
869 AND NVL(ail.discarded_flag,'N') <> 'Y'
870 AND NVL(ail.line_selected_for_appl_flag, 'N') <> 'Y'
871 AND aid.invoice_id = ail.invoice_id
872 AND aid.invoice_line_number = ail.line_number
873 AND aid.line_type_lookup_code
874 IN ( 'ITEM','ACCRUAL','REC_TAX','NONREC_TAX')
875 AND NVL(aid.reversal_flag,'N') <> 'Y';
876 -- eTax Uptake. Included inclusive tax distributions. No TAX
877 -- lines included (exclusive case)
878
879 IF p_prepay_apply_amount is NOT NULL THEN
880 IF (p_prepay_apply_amount > l_prepay_amount_remaining) THEN
881 l_prepay_apply_amount := l_prepay_amount_remaining;
882 END IF;
883
884 IF (l_invoice_amount_remaining < l_prepay_apply_amount) THEN
885 l_prepay_apply_amount := l_invoice_amount_remaining;
886 END IF;
887 ELSE
888 IF (l_invoice_amount_remaining <= l_prepay_amount_remaining) THEN
889 l_prepay_apply_amount := l_invoice_amount_remaining;
890 ELSE
891 l_prepay_apply_amount := l_prepay_amount_remaining;
892 END IF;
893 END IF;
894 END IF;
895
896 IF p_prepay_case_name IN ('VND_PREPAY_APPL_WITH_AMOUNT',
897 'VND_PREPAY_APPL_WITHOUT_AMOUNT',
898 'VND_PREPAY_APPL_WITHOUT_AMOUNT_EXP') THEN
899 -- Get the Prepay Amount Remaining
900 SELECT SUM(NVL(aid.prepay_amount_remaining, aid.total_dist_amount))
901 INTO l_prepay_amount_remaining
902 FROM ap_invoices ai,
903 ap_invoice_lines ail,
904 ap_invoice_distributions aid
905 WHERE ai.vendor_id = p_vendor_id
906 AND ai.invoice_type_lookup_code = 'PREPAYMENT'
907 AND nvl(ai.earliest_settlement_date,sysdate+1) <= SYSDATE
908 AND ai.payment_status_flag = 'Y'
909 AND ail.invoice_id = ai.invoice_id
910 AND ail.line_type_lookup_code = 'ITEM'
911 AND NVL(ail.discarded_flag,'N') <> 'Y'
912 AND NVL(ail.line_selected_for_appl_flag, 'N') <> 'Y'
913 AND aid.invoice_id = ail.invoice_id
914 AND aid.invoice_line_number = ail.line_number
915 AND aid.line_type_lookup_code
916 IN ( 'ITEM','ACCRUAL','REC_TAX','NONREC_TAX')
917 AND NVL(aid.reversal_flag,'N') <> 'Y';
918 -- eTax Uptake. Included inclusive tax distributions
919
920 IF p_prepay_apply_amount is NOT NULL THEN
921 IF (p_prepay_apply_amount > l_prepay_amount_remaining) THEN
922 l_prepay_apply_amount := l_prepay_amount_remaining;
923 END IF;
924
925 IF (l_invoice_amount_remaining < l_prepay_apply_amount) THEN
926 l_prepay_apply_amount := l_invoice_amount_remaining;
927 END IF;
928 ELSE
929 IF (l_invoice_amount_remaining <= l_prepay_amount_remaining) THEN
930 l_prepay_apply_amount := l_invoice_amount_remaining;
931 ELSE
932 l_prepay_apply_amount := l_prepay_amount_remaining;
933 END IF;
934 END IF;
935 END IF;
936
937 RETURN (l_prepay_apply_amount);
938
939 EXCEPTION
940 WHEN OTHERS THEN
941 APP_EXCEPTION.RAISE_EXCEPTION;
942 END get_prepay_apply_amount;
943
944
945 PROCEDURE Select_Lines_For_Application (
946 p_prepay_case_name IN VARCHAR2,
947 p_prepay_invoice_id IN NUMBER,
948 p_prepay_line_num IN NUMBER,
949 p_apply_amount IN NUMBER,
950 p_vendor_id IN NUMBER,
951 p_calling_sequence IN VARCHAR2,
952 p_request_id IN NUMBER,
953 p_invoice_id IN NUMBER, -- Bug 6394865
954 p_prepay_appl_info OUT NOCOPY ap_prepay_pkg.prepay_appl_tab)
955 IS
956
957 l_application_result BOOLEAN;
958 l_apply_amount_remaining NUMBER;
959 l_cur_invoice_id NUMBER;
960 l_prepay_invoice_num VARCHAR2(50);
961 l_prepay_line_num NUMBER;
962 l_cursor_amount_remaining NUMBER;
963 l_loop_counter BINARY_INTEGER := 1;
964 l_request_id NUMBER := p_request_id;
965 l_is_line_locked VARCHAR2(100);
966 l_lock_result BOOLEAN;
967 l_invoice_currency_code ap_invoices_all.INVOICE_CURRENCY_CODE%TYPE; -- Bug 6394865
968 l_payment_currency_code ap_invoices_all.PAYMENT_CURRENCY_CODE%TYPE; -- Bug 6394865
969
970 CURSOR C_INV_LEVEL_APPLY (cv_prepay_invoice_id IN NUMBER)
971 IS
972 SELECT ai.invoice_id,
973 ail.line_number,
974 AP_Prepay_Utils_PKG.Get_Line_Prepay_AMT_Remaining
975 (ail.invoice_id,
976 ail.line_number)
977 FROM ap_invoices_all ai,
978 ap_invoice_lines_all ail
979 WHERE ai.invoice_id = cv_prepay_invoice_id
980 AND ail.invoice_id = ai.invoice_id
981 AND AP_Prepay_Utils_PKG.Get_Line_Prepay_AMT_Remaining
982 (ail.invoice_id,
983 ail.line_number) > 0
984 AND ail.line_type_lookup_code = 'ITEM'
985 AND NVL(ail.discarded_flag,'N') <> 'Y'
986 AND NVL(ail.line_selected_for_appl_flag,'N') <> 'Y'
987 ORDER BY ail.line_number;
988 -- The application is based only in ITEM lines. No TAX
989 -- lines should be included
990
991 CURSOR C_VND_LEVEL_APPLY (cv_vendor_id IN NUMBER,
992 cv_invoice_currency_code IN ap_invoices_all.INVOICE_CURRENCY_CODE%TYPE,
993 cv_payment_currency_code IN ap_invoices_all.INVOICE_CURRENCY_CODE%TYPE)
994 -- Bug 6394865
995 IS
996 SELECT ai.invoice_id,
997 ail.line_number,
998 AP_Prepay_Utils_PKG.Get_Line_Prepay_AMT_Remaining
999 (ail.invoice_id,
1000 ail.line_number)
1001 FROM ap_invoices ai,
1002 ap_invoice_lines ail
1003 WHERE ai.vendor_id = cv_vendor_id
1004 AND ai.invoice_type_lookup_code = 'PREPAYMENT'
1005 AND ai.payment_status_flag = 'Y'
1006 AND NVL(ai.earliest_settlement_date,SYSDATE+1) <= SYSDATE
1007 AND ail.invoice_id = ai.invoice_id
1008 AND AP_Prepay_Utils_PKG.Get_Line_Prepay_AMT_Remaining
1009 (ail.invoice_id,
1010 ail.line_number) > 0
1011 AND ail.line_type_lookup_code = 'ITEM'
1012 AND NVL(ail.discarded_flag,'N') <> 'Y'
1013 AND NVL(ail.line_selected_for_appl_flag,'N') <> 'Y'
1014 AND ai.invoice_currency_code = cv_invoice_currency_code -- Bug 6394865
1015 AND ai.payment_currency_code = cv_payment_currency_code -- Bug 6394865
1016 ORDER BY ai.gl_date,
1017 ai.invoice_id,
1018 ail.line_number;
1019
1020 BEGIN
1021 -- Clear unwanted buffers
1022 p_prepay_appl_info.DELETE;
1023
1024 l_apply_amount_remaining := p_apply_amount;
1025
1026 IF p_prepay_case_name IN ('LINE_PREPAY_APPL_WITH_AMOUNT',
1027 'LINE_PREPAY_APPL_WITHOUT_AMOUNT') THEN
1028
1029 -- Lock the line if not locked and populate the application info
1030 -- into a PL/SQL table.
1031
1032 l_is_line_locked := AP_PREPAY_UTILS_PKG.Is_Line_Locked (
1033 p_prepay_invoice_id,
1034 p_prepay_line_num,
1035 l_request_id);
1036
1037 IF l_is_line_locked = 'UNLOCKED' THEN
1038
1039 l_lock_result := AP_PREPAY_UTILS_PKG.Lock_Line(
1040 p_prepay_invoice_id,
1041 p_prepay_line_num,
1042 l_request_id);
1043
1044 -- Populate the PL/SQL table
1045
1046 p_prepay_appl_info(l_loop_counter).prepay_invoice_id := p_prepay_invoice_id;
1047 p_prepay_appl_info(l_loop_counter).prepay_line_num := p_prepay_line_num;
1048 p_prepay_appl_info(l_loop_counter).prepay_apply_amount := p_apply_amount;
1049
1050 END IF;
1051 END IF;
1052
1053 IF p_prepay_case_name IN ('INV_PREPAY_APPL_WITH_AMOUNT',
1054 'INV_PREPAY_APPL_WITHOUT_AMOUNT') THEN
1055 OPEN C_INV_LEVEL_APPLY (p_prepay_invoice_id);
1056 LOOP
1057 FETCH C_INV_LEVEL_APPLY
1058 INTO l_cur_invoice_id,
1059 l_prepay_line_num,
1060 l_cursor_amount_remaining;
1061
1062 EXIT WHEN C_INV_LEVEL_APPLY%NOTFOUND;
1063
1064 IF l_apply_amount_remaining <= l_cursor_amount_remaining THEN
1065
1066 l_lock_result := AP_PREPAY_UTILS_PKG.Lock_Line(
1067 p_prepay_invoice_id,
1068 p_prepay_line_num,
1069 l_request_id);
1070
1071 -- Populate the PL/SQL table
1072
1073 p_prepay_appl_info(l_loop_counter).prepay_invoice_id := l_cur_invoice_id;
1074 p_prepay_appl_info(l_loop_counter).prepay_line_num := l_prepay_line_num;
1075 p_prepay_appl_info(l_loop_counter).prepay_apply_amount := l_apply_amount_remaining;
1076
1077 EXIT;
1078 ELSE
1079 l_lock_result := AP_PREPAY_UTILS_PKG.Lock_Line(
1080 p_prepay_invoice_id,
1081 p_prepay_line_num,
1082 l_request_id);
1083
1084 -- Populate the PL/SQL table
1085
1086 p_prepay_appl_info(l_loop_counter).prepay_invoice_id := l_cur_invoice_id;
1087 p_prepay_appl_info(l_loop_counter).prepay_line_num := l_prepay_line_num;
1088 p_prepay_appl_info(l_loop_counter).prepay_apply_amount := l_cursor_amount_remaining;
1089
1090 l_loop_counter := l_loop_counter + 1;
1091
1092 l_apply_amount_remaining :=
1093 l_apply_amount_remaining -
1094 l_cursor_amount_remaining;
1095
1096 END IF;
1097 END LOOP;
1098
1099 CLOSE C_INV_LEVEL_APPLY;
1100
1101 END IF;
1102
1103 IF p_prepay_case_name IN ('VND_PREPAY_APPL_WITH_AMOUNT',
1104 'VND_PREPAY_APPL_WITHOUT_AMOUNT') THEN
1105
1106 select invoice_currency_code, payment_currency_code
1107 into l_invoice_currency_code, l_payment_currency_code
1108 from ap_invoices where invoice_id = p_invoice_id; -- Bug 6394865
1109
1110 OPEN C_VND_LEVEL_APPLY (p_vendor_id, l_invoice_currency_code,
1111 l_payment_currency_code); -- Bug 6394865
1112 LOOP
1113 FETCH C_VND_LEVEL_APPLY
1114 INTO l_cur_invoice_id,
1115 l_prepay_line_num,
1116 l_cursor_amount_remaining;
1117
1118 EXIT WHEN C_VND_LEVEL_APPLY%NOTFOUND;
1119
1120 IF l_apply_amount_remaining <= l_cursor_amount_remaining THEN
1121
1122 l_lock_result := AP_PREPAY_UTILS_PKG.Lock_Line(
1123 p_prepay_invoice_id,
1124 p_prepay_line_num,
1125 l_request_id);
1126
1127 -- Populate the PL/SQL table
1128
1129 p_prepay_appl_info(l_loop_counter).prepay_invoice_id := l_cur_invoice_id;
1130 p_prepay_appl_info(l_loop_counter).prepay_line_num := l_prepay_line_num;
1131 p_prepay_appl_info(l_loop_counter).prepay_apply_amount := l_apply_amount_remaining;
1132
1133 EXIT;
1134 ELSE
1135 l_lock_result := AP_PREPAY_UTILS_PKG.Lock_Line(
1136 p_prepay_invoice_id,
1137 p_prepay_line_num,
1138 l_request_id);
1139
1140 -- Populate the PL/SQL table
1141
1142 p_prepay_appl_info(l_loop_counter).prepay_invoice_id := l_cur_invoice_id;
1143 p_prepay_appl_info(l_loop_counter).prepay_line_num := l_prepay_line_num;
1144 p_prepay_appl_info(l_loop_counter).prepay_apply_amount := l_cursor_amount_remaining;
1145
1146 l_loop_counter := l_loop_counter + 1;
1147
1148 l_apply_amount_remaining :=
1149 l_apply_amount_remaining -
1150 l_cursor_amount_remaining;
1151 END IF;
1152 END LOOP;
1153
1154 CLOSE C_VND_LEVEL_APPLY;
1155
1156 END IF;
1157
1158 EXCEPTION
1159 WHEN OTHERS THEN
1160 IF C_INV_LEVEL_APPLY%ISOPEN THEN
1161 CLOSE C_INV_LEVEL_APPLY;
1162 END IF;
1163
1164 IF C_VND_LEVEL_APPLY%ISOPEN THEN
1165 CLOSE C_VND_LEVEL_APPLY;
1166 END IF;
1167 APP_EXCEPTION.RAISE_EXCEPTION;
1168
1169 END Select_Lines_For_Application;
1170
1171
1172
1173 FUNCTION Check_Prepay_Info_Import (
1174 p_prepay_num IN OUT NOCOPY VARCHAR2,
1175 p_prepay_line_num IN OUT NOCOPY NUMBER,
1176 p_prepay_apply_amount IN OUT NOCOPY NUMBER, -- Bug 7004765
1177 p_invoice_amount IN NUMBER,
1178 p_prepay_gl_date IN OUT NOCOPY DATE,
1179 p_prepay_period_name IN OUT NOCOPY VARCHAR2,
1180 p_vendor_id IN NUMBER,
1181 p_prepay_included IN VARCHAR2,
1182 p_import_invoice_id IN NUMBER,
1183 p_source IN VARCHAR2,
1184 p_apply_advances_flag IN VARCHAR2,
1185 p_invoice_date IN DATE,
1186 p_base_currency_code IN VARCHAR2,
1187 p_invoice_currency_code IN VARCHAR2,
1188 p_payment_currency_code IN VARCHAR2,
1189 p_calling_sequence IN VARCHAR2,
1190 p_request_id IN NUMBER,
1191 p_prepay_case_name OUT NOCOPY VARCHAR2,
1192 p_prepay_invoice_id OUT NOCOPY NUMBER,
1193 p_invoice_type_lookup_code IN VARCHAR2 DEFAULT NULL) -- Bug 7004765
1194 RETURN VARCHAR2 IS
1195
1196 l_reject_code VARCHAR2(30);
1197 l_apply_amount NUMBER;
1198 l_api_name VARCHAR2(50);
1199 l_debug_info VARCHAR2(4000);
1200
1201 BEGIN
1202
1203 l_api_name := 'Check_Prepay_Info_Import';
1204
1205 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1206 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_PREPAY_PKG.Check_Prepay_Info_Import(+)');
1207 END IF;
1208
1209 p_prepay_invoice_id := p_import_invoice_id;
1210
1211 -- ============================================================================
1212 -- Step 1: Identify the case name based on the prepayment information provided.
1213 -- ============================================================================
1214 l_debug_info := 'Call Get_Prepay_Case_Name';
1215 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1216 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1217 END IF;
1218
1219 p_prepay_case_name := Get_Prepay_Case_Name (
1220 p_prepay_num,
1221 p_prepay_line_num,
1222 p_prepay_apply_amount,
1223 p_source,
1224 p_apply_advances_flag,
1225 p_calling_sequence);
1226
1227 -- ============================================================================
1228 -- Step 2: Validate the prepayment information provided based on the Case Name.
1229 -- ============================================================================
1230
1231 l_debug_info := 'Call Validate_Prepay_Info';
1232 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1233 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1234 END IF;
1235
1236 l_reject_code := Validate_Prepay_Info (
1237 p_prepay_case_name,
1238 p_prepay_num,
1239 p_prepay_line_num,
1240 p_prepay_apply_amount,
1241 p_invoice_amount,
1242 p_prepay_gl_date,
1243 p_prepay_period_name,
1244 p_vendor_id,
1245 p_import_invoice_id,
1246 p_source,
1247 p_apply_advances_flag,
1248 p_invoice_date,
1249 p_base_currency_code,
1250 p_invoice_currency_code,
1251 p_payment_currency_code,
1252 p_calling_sequence,
1253 p_prepay_invoice_id,
1254 p_invoice_type_lookup_code); -- Bug 7004765
1255
1256 IF l_reject_code IS NOT NULL THEN
1257 RETURN (l_reject_code);
1258 END IF;
1259
1260 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1261 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_PREPAY_PKG.Check_Prepay_Info_Import(-)');
1262 END IF;
1263
1264 RETURN (l_reject_code);
1265
1266 EXCEPTION
1267 WHEN OTHERS THEN
1268 APP_EXCEPTION.RAISE_EXCEPTION;
1269 END Check_Prepay_Info_Import;
1270
1271
1272 PROCEDURE Get_Prepay_Info_Import (
1273 p_prepay_case_name IN VARCHAR2,
1274 p_prepay_invoice_id IN NUMBER,
1275 p_prepay_num IN VARCHAR2,
1276 p_prepay_line_num IN NUMBER,
1277 p_prepay_apply_amount IN NUMBER,
1278 p_prepay_included IN VARCHAR2,
1279 p_import_invoice_id IN NUMBER,
1280 p_vendor_id IN NUMBER,
1281 p_request_id IN NUMBER,
1282 p_prepay_appl_info OUT NOCOPY ap_prepay_pkg.prepay_appl_tab,
1283 p_calling_sequence IN VARCHAR2) IS
1284
1285 l_apply_amount NUMBER;
1286 BEGIN
1287
1288 l_apply_amount := get_prepay_apply_amount(
1289 p_prepay_case_name,
1290 p_prepay_invoice_id,
1291 p_prepay_line_num,
1292 p_prepay_apply_amount,
1293 p_import_invoice_id,
1294 p_vendor_id,
1295 p_prepay_included);
1296
1297 --============================================================================
1298 -- Step 4: Select/lock the lines for prepayment application
1299 --============================================================================
1300
1301 Select_Lines_For_Application (
1302 p_prepay_case_name,
1303 p_prepay_invoice_id,
1304 p_prepay_line_num,
1305 l_apply_amount,
1306 p_vendor_id,
1307 p_calling_sequence,
1308 p_request_id,
1309 p_import_invoice_id, -- Bug 6394865
1310 p_prepay_appl_info);
1311
1312 EXCEPTION
1313 WHEN OTHERS THEN
1314 APP_EXCEPTION.RAISE_EXCEPTION;
1315 END Get_Prepay_Info_Import;
1316
1317
1318 PROCEDURE Apply_Prepay_Import (
1319 p_prepay_invoice_id IN NUMBER,
1320 p_prepay_num IN VARCHAR2,
1321 p_prepay_line_num IN NUMBER,
1322 p_prepay_apply_amount IN NUMBER,
1323 p_prepay_case_name IN VARCHAR2,
1324 p_import_invoice_id IN NUMBER,
1325 p_request_id IN NUMBER,
1326 p_invoice_id IN NUMBER,
1327 p_vendor_id IN NUMBER,
1328 p_prepay_gl_date IN DATE,
1329 p_prepay_period_name IN VARCHAR2,
1330 p_prepay_included IN VARCHAR2,
1331 p_user_id IN NUMBER,
1332 p_last_update_login IN NUMBER,
1333 p_calling_sequence IN VARCHAR2,
1334 p_prepay_appl_log OUT NOCOPY ap_prepay_pkg.Prepay_Appl_Log_Tab)
1335 IS
1336 l_prepay_apply_amount NUMBER;
1337 l_loop_counter BINARY_INTEGER;
1338 l_dummy BOOLEAN;
1339 l_error_message VARCHAR2(2000);
1340 l_prepay_appl_info ap_prepay_pkg.prepay_appl_tab;
1341 l_prepay_dist_info AP_PREPAY_PKG.PREPAY_DIST_TAB_TYPE;
1342 l_curr_calling_sequence VARCHAR2(2000);
1343 BEGIN
1344
1345
1346 l_curr_calling_sequence := 'Apply_Prepay_Import <-'||p_calling_sequence;
1347
1348 AP_PREPAY_PKG.Get_Prepay_Info_Import(
1349 P_Prepay_Case_Name => p_prepay_case_name,
1350 P_Prepay_Invoice_Id => p_prepay_invoice_id,
1351 P_Prepay_Num => p_prepay_num,
1352 P_Prepay_Line_Num => p_prepay_line_num,
1353 P_Prepay_Apply_Amount => p_prepay_apply_amount,
1354 P_Prepay_Included => p_prepay_included,
1355 P_Import_Invoice_Id => p_invoice_id, -- p_import_invoice_id, Modified for bug 7110038
1356 P_Vendor_Id => p_vendor_id,
1357 P_Request_Id => p_request_id,
1358 P_Prepay_Appl_Info => l_prepay_appl_info,
1359 P_Calling_Sequence => l_curr_calling_sequence);
1360
1361 IF l_prepay_appl_info.count <=0 THEN
1362 RETURN;
1363 END IF;
1364
1365 FOR l_loop_counter IN NVL(l_prepay_appl_info.FIRST,0) .. NVL(l_prepay_appl_info.LAST,0) LOOP
1366
1367 -- Call the Apply_Prepay_line API to apply the prepayment
1368 IF (AP_PREPAY_PKG.Apply_Prepay_Line (
1369 l_prepay_appl_info(l_loop_counter).prepay_invoice_id,
1370 l_prepay_appl_info(l_loop_counter).prepay_line_num,
1371 l_prepay_dist_info,
1372 'Y',
1373 p_invoice_id,
1374 NULL, --p_invoice_line_number
1375 l_prepay_appl_info(l_loop_counter).prepay_apply_amount,
1376 p_prepay_gl_date,
1377 p_prepay_period_name,
1378 p_prepay_included,
1379 p_user_id,
1380 p_last_update_login,
1381 p_calling_sequence,
1382 'PREPAYMENT APPLICATION',
1383 l_error_message) = FALSE ) THEN
1384
1385 p_prepay_appl_log(l_loop_counter).success := 'N';
1386
1387 p_prepay_appl_log(l_loop_counter).error_message := l_error_message;
1388
1389 ELSE
1390
1391 p_prepay_appl_log(l_loop_counter).success := 'Y';
1392
1393 END IF;
1394
1395 p_prepay_appl_log(l_loop_counter).prepay_invoice_id :=
1396 l_prepay_appl_info(l_loop_counter).prepay_invoice_id;
1397
1398 p_prepay_appl_log(l_loop_counter).prepay_line_num :=
1399 l_prepay_appl_info(l_loop_counter).prepay_line_num;
1400
1401 p_prepay_appl_log(l_loop_counter).prepay_apply_amount :=
1402 l_prepay_appl_info(l_loop_counter).prepay_apply_amount;
1403
1404 END LOOP;
1405
1406 EXCEPTION
1407 WHEN OTHERS THEN
1408 APP_EXCEPTION.RAISE_EXCEPTION;
1409 END Apply_Prepay_Import;
1410
1411
1412
1413 FUNCTION Apply_Prepay_Line (
1414 P_PREPAY_INVOICE_ID IN NUMBER,
1415 P_PREPAY_LINE_NUM IN NUMBER,
1416 P_PREPAY_DIST_INFO IN OUT NOCOPY AP_PREPAY_PKG.PREPAY_DIST_TAB_TYPE,
1417 P_PRORATE_FLAG IN VARCHAR2,
1418 P_INVOICE_ID IN NUMBER,
1419 /*Contract Payments*/
1420 P_INVOICE_LINE_NUMBER IN NUMBER DEFAULT NULL,
1421 P_APPLY_AMOUNT IN NUMBER,
1422 P_GL_DATE IN DATE,
1423 P_PERIOD_NAME IN VARCHAR2,
1424 P_PREPAY_INCLUDED IN VARCHAR2,
1425 P_USER_ID IN NUMBER,
1426 P_LAST_UPDATE_LOGIN IN NUMBER,
1427 P_CALLING_SEQUENCE IN VARCHAR2,
1428 /*Contract Payments*/
1429 P_CALLING_MODE IN VARCHAR2 DEFAULT 'PREPAYMENT APPLICATION',
1430 P_ERROR_MESSAGE OUT NOCOPY VARCHAR2) RETURN BOOLEAN
1431 IS
1432
1433 l_base_currency_code ap_system_parameters_all.base_currency_code%TYPE;
1434
1435 -- Standard Invoice Related Variables
1436 l_std_inv_batch_id ap_invoices_all.batch_id%TYPE;
1437 l_std_inv_curr_code ap_invoices_all.invoice_currency_code%TYPE;
1438 l_std_inv_xrate ap_invoices_all.exchange_rate%TYPE;
1439 l_std_inv_xdate ap_invoices_all.exchange_date%TYPE;
1440 l_std_inv_xrate_type ap_invoices_all.exchange_rate_type%TYPE;
1441 l_std_inv_pay_curr_code ap_invoices_all.payment_currency_code%TYPE;
1442 l_std_inv_pay_cross_rate_date ap_invoices_all.payment_cross_rate_date%TYPE;
1443 l_std_inv_pay_cross_rate_type ap_invoices_all.payment_cross_rate_type%TYPE;
1444
1445 -- Prepayment Invoice Related Variables
1446 l_ppay_inv_curr_code ap_invoices_all.invoice_currency_code%TYPE;
1447 l_ppay_inv_xrate ap_invoices_all.exchange_rate%TYPE;
1448 l_ppay_inv_xdate ap_invoices_all.exchange_date%TYPE;
1449 l_ppay_inv_xrate_type ap_invoices_all.exchange_rate_type%TYPE;
1450 l_ppay_inv_pay_curr_code ap_invoices_all.payment_currency_code%TYPE;
1451 l_ppay_inv_pay_cross_rate_date ap_invoices_all.payment_cross_rate_date%TYPE;
1452 l_ppay_inv_pay_cross_rate_type ap_invoices_all.payment_cross_rate_type%TYPE;
1453
1454 -- Prepayment Line Related Variables
1455 l_ppay_ln_amount ap_invoice_lines_all.amount%TYPE;
1456 l_ppay_ln_base_amount ap_invoice_lines_all.base_amount%TYPE;
1457 l_ppay_ln_amount_remaining NUMBER;
1458 l_ppay_ln_quantity_invoiced ap_invoice_lines_all.quantity_invoiced%TYPE;
1459 l_ppay_ln_pa_quantity ap_invoice_lines_all.pa_quantity%TYPE;
1460 l_ppay_ln_stat_amount ap_invoice_lines_all.stat_amount%TYPE;
1461 l_ppay_ln_po_line_location_id ap_invoice_lines_all.po_line_location_id%TYPE;
1462 l_ppay_ln_po_distribution_id ap_invoice_lines_all.po_distribution_id%TYPE;
1463 l_ppay_ln_rcv_transaction_id ap_invoice_lines_all.rcv_transaction_id%TYPE;
1464 l_ppay_ln_uom ap_invoice_lines_all.unit_meas_lookup_code%TYPE;
1465 l_ppay_ln_match_basis po_line_types.matching_basis%TYPE;
1466
1467 -- Prepayment Payment Related Variables
1468 l_ppay_pay_curr_code ap_checks_all.currency_code%TYPE;
1469 l_ppay_pay_xrate ap_checks_all.exchange_rate%TYPE;
1470 l_ppay_pay_xdate ap_invoices_all.exchange_date%TYPE;
1471 l_ppay_pay_xrate_type ap_checks_all.exchange_rate_type%TYPE;
1472
1473 -- Other Prepayment Application Related Variables
1474 l_apply_amount NUMBER := p_apply_amount;
1475 l_ppay_apply_amt_in_pay_curr NUMBER;
1476 l_dummy BOOLEAN;
1477 l_final_application VARCHAR2(1) := 'N';
1478 l_result BOOLEAN;
1479 l_calling_program VARCHAR2(1000);
1480
1481 -- PREPAY LINE related variables
1482 l_prepay_ln_base_amount ap_invoice_lines_all.base_amount%TYPE;
1483 l_prepay_ln_quantity_invoiced ap_invoice_lines_all.quantity_invoiced%TYPE;
1484 l_prepay_ln_pa_quantity ap_invoice_lines_all.pa_quantity%TYPE;
1485 l_prepay_ln_stat_amount ap_invoice_lines_all.stat_amount%TYPE;
1486 l_prepay_ln_number ap_invoice_lines_all.line_number%TYPE;
1487 l_prepay_ln_s_quant_invoiced ap_invoice_lines_all.quantity_invoiced%TYPE;
1488 l_prepay_ln_s_pa_quantity ap_invoice_lines_all.pa_quantity%TYPE;
1489 l_prepay_ln_s_stat_amount ap_invoice_lines_all.stat_amount%TYPE;
1490
1491 -- PREPAY distributions related variables
1492 l_prepay_dist_info AP_PREPAY_PKG.PREPAY_DIST_TAB_TYPE;
1493 l_max_dist_number
1494 ap_invoice_distributions_all.distribution_line_number%TYPE;
1495 l_loop_counter BINARY_INTEGER := 1;
1496 l_loop_variable BINARY_INTEGER;
1497 l_dist_line_counter NUMBER := 1;
1498 l_prepay_dist_s_quant_invoiced
1499 ap_invoice_distributions_all.quantity_invoiced%TYPE;
1500 l_prepay_dist_s_pa_quantity ap_invoice_distributions_all.pa_quantity%TYPE;
1501 l_prepay_dist_s_stat_amount ap_invoice_distributions_all.stat_amount%TYPE;
1502
1503 l_debug_info VARCHAR2(4000);
1504 l_current_calling_sequence VARCHAR2(2000);
1505
1506 l_inclusive_tax_amount ap_invoice_lines_all.included_tax_amount%TYPE;
1507 l_apply_amount_no_tax_incl NUMBER;
1508 l_ppay_ln_amt_remaining_no_tax NUMBER;
1509 l_prepay_tax_diff_amt NUMBER;
1510
1511 l_invoice_line_number ap_invoice_lines_all.line_number%TYPE;
1512
1513 l_api_name VARCHAR2(50);
1514
1515 tax_exception EXCEPTION;
1516 l_prepay_excl_tax_amt NUMBER; --5224883
1517
1518 total_item_apply_amount NUMBER; -- 7834255
1519
1520 l_prepay_adjusted_amount NUMBER; --12888895
1521
1522 CURSOR C_SYS_PARAMS IS
1523 SELECT base_currency_code
1524 FROM ap_system_parameters;
1525
1526 CURSOR C_STD_INVOICE_INFO (CV_Std_Invoice_ID IN NUMBER) IS
1527 SELECT batch_id,
1528 invoice_currency_code,
1529 exchange_rate,
1530 exchange_date,
1531 exchange_rate_type,
1532 payment_currency_code,
1533 payment_cross_rate_date,
1534 payment_cross_rate_type
1535 FROM AP_Invoices
1536 WHERE invoice_id = CV_Std_Invoice_ID;
1537
1538 CURSOR C_PPAY_INVOICE_INFO (CV_PPay_Invoice_ID IN NUMBER) IS
1539 SELECT invoice_currency_code,
1540 exchange_rate,
1541 exchange_date,
1542 exchange_rate_type,
1543 payment_currency_code,
1544 payment_cross_rate_date,
1545 payment_cross_rate_type
1546 FROM AP_Invoices
1547 WHERE invoice_id = CV_PPay_Invoice_ID;
1548
1549 CURSOR C_PPAY_LINE_INFO (CV_PPAY_Invoice_ID IN NUMBER,
1550 CV_PPAY_LINE_NUM IN NUMBER) IS
1551 SELECT ail.amount,
1552 NVL(ail.base_amount,0),
1553 /*
1554 Decode(p_calling_mode,'PREPAYMENT APPLICATION',
1555 AP_Prepay_Utils_PKG.get_line_prepay_amt_remaining(
1556 ail.invoice_id,
1557 ail.line_number),
1558 'RECOUPMENT',
1559 AP_Prepay_Utils_Pkg.get_ln_prep_amt_remain_recoup(
1560 ail.invoice_id,
1561 ail.line_number)
1562 ),
1563 */
1564 AP_Prepay_Utils_PKG.get_line_prepay_amt_remaining(
1565 ail.invoice_id,
1566 ail.line_number),
1567 ail.quantity_invoiced,
1568 ail.pa_quantity,
1569 ail.stat_amount,
1570 ail.po_line_location_id,
1571 ail.po_distribution_id,
1572 ail.rcv_transaction_id,
1573 ail.unit_meas_lookup_code,
1574 plt.matching_basis
1575 FROM AP_invoice_lines ail,
1576 po_lines pl, /* Amount Based Matching. PO related tables and conditions */
1577 po_line_locations pll,
1578 po_line_types_b plt --bug 5056269
1579 -- po_line_types_tl T --bug 5119694
1580 WHERE invoice_id = CV_PPAY_Invoice_ID
1581 AND line_number = CV_PPAY_LINE_NUM
1582 AND ail.po_line_location_id = pll.line_location_id(+)
1583 AND pll.po_line_id = pl.po_line_id(+)
1584 AND pl.line_type_id = plt.line_type_id(+);
1585 -- AND plt.LINE_TYPE_ID = T.LINE_TYPE_ID
1586 -- AND T.LANGUAGE = userenv('LANG');
1587
1588 CURSOR C_PPAY_PAY_INFO (CV_PPAY_Invoice_ID IN NUMBER) IS
1589 SELECT AC.currency_code,
1590 AC.exchange_rate_type,
1591 AC.exchange_date,
1592 AC.exchange_rate
1593 FROM AP_checks_all AC,
1594 AP_invoice_payments_all AIP
1595 WHERE AC.check_id = AIP.check_id
1596 AND AIP.invoice_id = CV_PPAY_Invoice_ID
1597 AND AIP.reversal_inv_pmt_id IS NULL -- bug8971713
1598 AND NOT EXISTS (SELECT 'Invoice payment has been reversed'
1599 FROM AP_invoice_payments_all AIP2
1600 WHERE AIP2.reversal_inv_pmt_id = AIP.invoice_payment_id
1601 AND AIP2.check_id = AC.check_id);
1602
1603 CURSOR C_PPAY_DIST_INFO (CV_PPAY_Invoice_ID IN NUMBER,
1604 CV_PPAY_LINE_NUM IN NUMBER) IS
1605 SELECT invoice_distribution_id,
1606 total_dist_amount,
1607 total_dist_base_amount,
1608 nvl(prepay_amount_remaining,total_dist_amount),
1609 po_distribution_id,
1610 rcv_transaction_id,
1611 quantity_invoiced,
1612 stat_amount,
1613 pa_quantity,
1614 p_gl_date,
1615 p_period_name,
1616 global_attribute_category,
1617 'PREPAY' line_type_lookup_code
1618 FROM ap_invoice_distributions
1619 WHERE invoice_id = CV_PPAY_Invoice_ID
1620 AND invoice_line_number = CV_PPAY_LINE_NUM
1621 AND line_type_lookup_code IN ('ITEM', 'ACCRUAL')
1622 AND NVL(prepay_amount_remaining,total_dist_amount) > 0
1623 --AND NVL(prepay_amount_remaining,0) > 0
1624 AND NVL(reversal_flag,'N') <> 'Y'
1625 ORDER BY nvl(prepay_amount_remaining,total_dist_amount); -- 7834255
1626
1627
1628 CURSOR C_PPAY_DIST_INFO_RECOUP(CV_PPAY_Invoice_ID IN NUMBER,
1629 CV_PPAY_LINE_NUM IN NUMBER) IS
1630 /*Get the distributions including inclusive tax distributions
1631 of the Prepayment Invoice - Item line */
1632 SELECT invoice_distribution_id,
1633 total_dist_amount,
1634 total_dist_base_amount,
1635 nvl(prepay_amount_remaining,total_dist_amount),
1636 po_distribution_id,
1637 rcv_transaction_id,
1638 quantity_invoiced,
1639 stat_amount,
1640 pa_quantity,
1641 p_gl_date,
1642 p_period_name,
1643 global_attribute_category,
1644 decode(line_type_lookup_code,'ITEM','PREPAY',
1645 'ACCRUAL','PREPAY',line_type_lookup_code) line_type_lookup_code,
1646 decode(line_type_lookup_code,'NONREC_TAX',charge_applicable_to_dist_id,
1647 'REC_TAX',charge_applicable_to_dist_id,NULL) parent_chrg_appl_to_dist_id,
1648 decode(line_type_lookup_code,'TERV',related_id, 'TIPV', related_id,
1649 'TRV',related_id, NULL) parent_related_id
1650 FROM ap_invoice_distributions
1651 WHERE invoice_id = CV_PPAY_Invoice_ID
1652 AND invoice_line_number = CV_PPAY_LINE_NUM
1653 AND line_type_lookup_code IN ('ITEM','ACCRUAL')
1654 AND NVL(prepay_amount_remaining,total_dist_amount) > 0
1655 AND NVL(reversal_flag,'N') <> 'Y'
1656 ORDER BY nvl(prepay_amount_remaining,total_dist_amount); -- 7834255
1657
1658 BEGIN
1659
1660 l_current_calling_sequence := 'Apply_Prepay_Line<-'
1661 ||p_calling_sequence;
1662
1663 l_calling_program := p_calling_sequence;
1664
1665 l_api_name := 'Apply_Prepay_Line';
1666
1667 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1668 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_PREPAY_PKG.Apply_Prepay_Line(+)');
1669 END IF;
1670
1671 -- ==========================================================
1672 -- Step 0: Get Base Currency Code
1673 -- ==========================================================
1674
1675 l_debug_info := 'Get Base Currency Code';
1676 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1677 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1678 END IF;
1679
1680 OPEN C_SYS_PARAMS;
1681
1682 FETCH C_SYS_PARAMS INTO
1683 l_base_currency_code;
1684
1685 CLOSE C_SYS_PARAMS;
1686
1687 -- ==========================================================
1688 -- Step 1: Get Required Information from the STANDARD INVOICE
1689 -- ==========================================================
1690
1691 l_debug_info := 'Get Required Information from the STANDARD INVOICE';
1692 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1693 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1694 END IF;
1695
1696 OPEN C_STD_INVOICE_INFO (P_INVOICE_ID);
1697
1698 FETCH C_STD_INVOICE_INFO INTO
1699 l_std_inv_batch_id,
1700 l_std_inv_curr_code,
1701 l_std_inv_xrate,
1702 l_std_inv_xdate,
1703 l_std_inv_xrate_type,
1704 l_std_inv_pay_curr_code,
1705 l_std_inv_pay_cross_rate_date,
1706 l_std_inv_pay_cross_rate_type;
1707
1708 CLOSE C_STD_INVOICE_INFO;
1709
1710 -- =============================================================
1711 -- Step 2: Get Required Information from the PREPAYMENT INVOICE
1712 -- =============================================================
1713
1714 l_debug_info := 'Get Required Information from the PREPAYMENT INVOICE';
1715 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1716 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1717 END IF;
1718
1719 OPEN C_PPAY_INVOICE_INFO (P_PREPAY_INVOICE_ID);
1720
1721 FETCH C_PPAY_INVOICE_INFO INTO
1722 l_ppay_inv_curr_code,
1723 l_ppay_inv_xrate,
1724 l_ppay_inv_xdate,
1725 l_ppay_inv_xrate_type,
1726 l_ppay_inv_pay_curr_code,
1727 l_ppay_inv_pay_cross_rate_date,
1728 l_ppay_inv_pay_cross_rate_type;
1729
1730 CLOSE C_PPAY_INVOICE_INFO;
1731
1732 -- ==========================================================
1733 -- Step 3: Get the Required Line Information for the Selected
1734 -- Prepayment Invoice Line
1735 -- ==========================================================
1736
1737 l_debug_info := 'Get the Required Line Information for the Selected '||
1738 'Prepayment Invoice Line';
1739 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1740 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1741 END IF;
1742
1743 OPEN C_PPAY_LINE_INFO (P_PREPAY_INVOICE_ID,
1744 P_PREPAY_LINE_NUM);
1745
1746 FETCH C_PPAY_LINE_INFO INTO
1747 l_ppay_ln_amount,
1748 l_ppay_ln_base_amount,
1749 l_ppay_ln_amount_remaining,
1750 l_ppay_ln_quantity_invoiced,
1751 l_ppay_ln_pa_quantity,
1752 l_ppay_ln_stat_amount,
1753 l_ppay_ln_po_line_location_id,
1754 l_ppay_ln_po_distribution_id,
1755 l_ppay_ln_rcv_transaction_id,
1756 l_ppay_ln_uom,
1757 l_ppay_ln_match_basis;
1758
1759 CLOSE C_PPAY_LINE_INFO;
1760
1761
1762 --Upgrade the PO Shipment and Po Distributions if the Po Shipment
1763 --or the Prepayment invoice is pre-upgrade data from a release prior to R12.
1764 IF (l_ppay_ln_po_line_location_id IS NOT NULL) THEN
1765 /*Bug 14213367: Added input parameter to the debug info*/
1766 l_debug_info := 'Call Upgrade Po Shipment by passing l_ppay_ln_po_line_location_id '||
1767 l_ppay_ln_po_line_location_id||' as an input parameter';
1768 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1769 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1770 END IF;
1771
1772 AP_MATCHING_UTILS_PKG.AP_Upgrade_Po_Shipment(
1773 l_ppay_ln_po_line_location_id,
1774 l_current_calling_sequence);
1775
1776 END IF;
1777
1778 -- ==========================================================
1779 -- Step 4: Get the Prepayment Payment Related Information
1780 -- ==========================================================
1781
1782 l_debug_info := 'Get the Prepayment Payment Related Information';
1783 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1784 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1785 END IF;
1786
1787 OPEN C_PPAY_PAY_INFO (P_PREPAY_INVOICE_ID);
1788
1789 FETCH C_PPAY_PAY_INFO INTO
1790 l_ppay_pay_curr_code,
1791 l_ppay_pay_xrate_type,
1792 l_ppay_pay_xdate,
1793 l_ppay_pay_xrate;
1794
1795 CLOSE C_PPAY_PAY_INFO;
1796
1797 -- ==========================================================
1798 -- Step 5: Round the Apply Amount
1799 -- ==========================================================
1800 /*Bug 14213367: Added input parameters to the debug info*/
1801 l_debug_info := 'Round the Apply Amount '||l_apply_amount||
1802 ' with standard invoice currency code '||l_std_inv_curr_code;
1803 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1804 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1805 END IF;
1806 l_apply_amount := AP_Utilities_PKG.AP_Round_Currency (
1807 l_apply_amount,
1808 l_std_inv_curr_code);
1809 /*Bug 14213367:Start */
1810 l_debug_info := 'l_apply_amount: '||l_apply_amount;
1811 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1812 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1813 END IF;
1814 /*Bug 14213367:end */
1815
1816 -- ==========================================================
1817 -- Step 6: Get Line Level Apply Base Amount
1818 -- This base amount will be the base amount of the
1819 -- PREPAY line that will be created as a result of
1820 -- this application. This base amount will be
1821 -- calculated using the Standard Invoice XRATE.
1822 -- ==========================================================
1823 /*Bug 14213367: Added parameters to the debug info*/
1824 l_debug_info := 'Get Line Level Apply Base Amount.l_base_currency_code: '||
1825 l_base_currency_code|| ',l_std_inv_xdate: '||l_std_inv_xdate;
1826 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1827 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1828 END IF;
1829 l_debug_info := 'l_std_inv_xrate_type: '||l_std_inv_xrate_type||
1830 ',l_std_inv_xrate: '||l_std_inv_xrate;
1831 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1832 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1833 END IF;
1834
1835 IF (l_std_inv_curr_code <> l_base_currency_code) THEN
1836
1837 IF l_std_inv_xrate_type = 'User' THEN
1838 l_prepay_ln_base_amount := AP_Utilities_PKG.AP_Round_Currency(
1839 l_apply_amount* l_std_inv_xrate,
1840 l_base_currency_code);
1841 ELSE
1842 /*Bug 14592297:Start*/
1843 l_debug_info:='There might not be a defined Currency Exchange Rate available for '||
1844 l_std_inv_curr_code||' to '||l_base_currency_code|| ' on '||
1845 l_std_inv_xdate||' for '||l_std_inv_xrate_type||' exchange rate type' ;
1846
1847 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1848 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1849 END IF;
1850 /*Bug 14592297:End*/
1851
1852 l_prepay_ln_base_amount := GL_Currency_API.Convert_Amount(
1853 l_std_inv_curr_code,
1854 l_base_currency_code,
1855 l_std_inv_xdate,
1856 l_std_inv_xrate_type,
1857 l_apply_amount);
1858 /*Bug 14592297:Start*/
1859
1860 l_debug_info:='Successfull call returned from GL_CURRENCY_API';
1861 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1862 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1863 END IF;
1864 /*Bug 14592297:End*/
1865 END IF;
1866 END IF;
1867 /*Bug 14213367:Start */
1868 l_debug_info := 'l_prepay_ln_base_amount: '||l_prepay_ln_base_amount;
1869 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1870 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1871 END IF;
1872 /*Bug 14213367:end */
1873
1874 -- ==========================================================
1875 -- Step 7: Get Line Level Quantity Invoiced, Stat Amount and
1876 -- PA Quantity
1877 -- ==========================================================
1878
1879 /*Bug 14213367: Added parameters to the debug info*/
1880 l_debug_info := 'Get Line Level Quantity Invoiced, Stat Amount and '||
1881 'PA Quantity. l_ppay_ln_amount_remaining: '||l_ppay_ln_amount_remaining;
1882 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1883 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1884 END IF;
1885
1886 IF (l_apply_amount = l_ppay_ln_amount_remaining) THEN
1887
1888 SELECT NVL( ABS(SUM(quantity_invoiced)), 0 ),
1889 NVL( ABS(SUM(stat_amount)), 0 ),
1890 NVL( ABS(SUM(pa_quantity)), 0 )
1891 INTO l_prepay_ln_s_quant_invoiced,
1892 l_prepay_ln_s_stat_amount,
1893 l_prepay_ln_s_pa_quantity
1894 FROM ap_invoice_lines
1895 WHERE prepay_invoice_id = p_prepay_invoice_id
1896 AND prepay_line_number = p_prepay_line_num;
1897
1898 IF (l_ppay_ln_po_line_location_id IS NOT NULL OR
1899 l_ppay_ln_po_distribution_id IS NOT NULL OR
1900 l_ppay_ln_rcv_transaction_id IS NOT NULL) THEN
1901
1902 l_prepay_ln_quantity_invoiced :=
1903 ( -1 ) *
1904 (l_ppay_ln_quantity_invoiced -
1905 l_prepay_ln_s_quant_invoiced);
1906 END IF;
1907
1908 IF l_ppay_ln_stat_amount IS NOT NULL THEN
1909
1910 l_prepay_ln_stat_amount :=
1911 ( -1 ) *
1912 (l_ppay_ln_stat_amount -
1913 l_prepay_ln_s_stat_amount);
1914 END IF;
1915
1916 IF l_ppay_ln_pa_quantity IS NOT NULL THEN
1917
1918 l_prepay_ln_pa_quantity :=
1919 ( -1 ) *
1920 (l_ppay_ln_pa_quantity -
1921 l_prepay_ln_s_pa_quantity);
1922 END IF;
1923
1924 ELSE
1925
1926 IF (l_ppay_ln_po_line_location_id IS NOT NULL OR
1927 l_ppay_ln_po_distribution_id IS NOT NULL OR
1928 l_ppay_ln_rcv_transaction_id IS NOT NULL) THEN
1929
1930 l_prepay_ln_quantity_invoiced :=
1931 (-1) *
1932 ((l_apply_amount/l_ppay_ln_amount) *
1933 l_ppay_ln_quantity_invoiced);
1934
1935 END IF;
1936
1937 IF l_ppay_ln_stat_amount IS NOT NULL THEN
1938
1939 l_prepay_ln_stat_amount :=
1940 (-1) *
1941 ((l_apply_amount/l_ppay_ln_amount) *
1942 l_ppay_ln_stat_amount);
1943
1944 END IF;
1945
1946 IF l_ppay_ln_pa_quantity IS NOT NULL THEN
1947
1948 l_prepay_ln_pa_quantity :=
1949 (-1) *
1950 ((l_apply_amount/l_ppay_ln_amount) *
1951 l_ppay_ln_pa_quantity);
1952
1953 END IF;
1954
1955 END IF;
1956 /*Bug 14213367:Start */
1957 l_debug_info := 'l_prepay_ln_quantity_invoiced: '||l_prepay_ln_quantity_invoiced||
1958 ',l_prepay_ln_stat_amount: '||l_prepay_ln_stat_amount||',l_prepay_ln_pa_quantity: '||
1959 l_prepay_ln_pa_quantity;
1960 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1961 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1962 END IF;
1963 /*Bug 14213367:end */
1964
1965 -- ==========================================================
1966 -- Step 8: Get Next Line Number for the PREPAY Line from the
1967 -- STANDARD Invoice if performing 'Prepayment Application'
1968 -- not for 'Recoupment'.
1969 -- ==========================================================
1970
1971 l_debug_info := 'Get Next Line Number for the PREPAY Line from the '||
1972 'STANDARD Invoice';
1973 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1974 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1975 END IF;
1976
1977 --Contract Payments: Added the IF condition
1978 IF (P_calling_mode = 'PREPAYMENT APPLICATION') THEN
1979
1980 SELECT NVL(MAX (line_number),0) + 1
1981 INTO l_prepay_ln_number
1982 FROM ap_invoice_lines
1983 WHERE invoice_id = p_invoice_id;
1984
1985
1986 -- ===========================================================
1987 -- Step 9: Insert PREPAY Line - We will call the INSERT_PREPAY
1988 -- _LINE procedure to create the PREPAY line if the
1989 -- calling mode is 'PREPAYMENT APPLICATION', not for
1990 -- RECOUPMENT.
1991 -- ===========================================================
1992 /*Bug 14213367: Added parameters to the debug info*/
1993 l_debug_info := 'Call Ap_Prepay_Pkg.Insert_Prepay_Line. p_prepay_invoice_id: '||
1994 p_prepay_invoice_id|| ',p_prepay_line_num: '||p_prepay_line_num||',p_invoice_id: '||
1995 p_invoice_id||',l_prepay_ln_number: '||l_prepay_ln_number;
1996 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1997 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1998 END IF;
1999
2000 l_dummy := AP_PREPAY_PKG.INSERT_PREPAY_LINE(
2001 p_prepay_invoice_id,
2002 p_prepay_line_num,
2003 p_invoice_id,
2004 l_prepay_ln_number,
2005 l_apply_amount,
2006 l_prepay_ln_base_amount,
2007 p_gl_date,
2008 p_period_name,
2009 p_prepay_included,
2010 l_prepay_ln_quantity_invoiced,
2011 l_prepay_ln_stat_amount,
2012 l_prepay_ln_pa_quantity,
2013 p_user_id,
2014 p_last_update_login,
2015 p_calling_sequence,
2016 p_error_message);
2017
2018 IF l_dummy = FALSE THEN
2019
2020 l_result:= AP_PREPAY_UTILS_PKG.Unlock_Line(
2021 p_prepay_invoice_id,
2022 p_prepay_line_num);
2023
2024 RETURN (FALSE);
2025
2026 END IF;
2027
2028 -- ===========================================================
2029 -- Step 10: Calculate Tax
2030 -- Call eTax service.
2031 -- ===========================================================
2032 l_debug_info := 'Call to calculate tax';
2033 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2034 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
2035 END IF;
2036
2037 IF NOT (ap_etax_pkg.calling_etax(
2038 p_invoice_id => p_invoice_id,
2039 p_line_number => NULL,
2040 p_calling_mode => 'APPLY PREPAY',
2041 p_override_status => NULL,
2042 p_line_number_to_delete => NULL,
2043 p_Interface_Invoice_Id => NULL,
2044 p_all_error_messages => 'N',
2045 p_error_code => p_error_message,
2046 p_calling_sequence => l_current_calling_sequence)) THEN
2047
2048 RAISE tax_exception;
2049
2050 END IF;
2051
2052 -- ===========================================================
2053 -- Step 11: Get inclusive tax amount calculated by eTax
2054 --
2055 -- ===========================================================
2056 l_debug_info := 'Get inclusive tax amount from the PREPAY line '||
2057 'after tax calculation';
2058 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2059 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
2060 END IF;
2061
2062 BEGIN
2063 SELECT NVL(included_tax_amount, 0)
2064 INTO l_inclusive_tax_amount
2065 FROM ap_invoice_lines_all
2066 WHERE invoice_id = p_invoice_id
2067 AND line_number = l_prepay_ln_number;
2068
2069 EXCEPTION
2070 WHEN NO_DATA_FOUND THEN
2071 -- If there is a no_data_found in this case the PREPAY
2072 -- line was not created. End the process and unlock the
2073 -- PREPAYMENT line
2074 l_result:= AP_PREPAY_UTILS_PKG.Unlock_Line(
2075 p_prepay_invoice_id,
2076 p_prepay_line_num);
2077
2078 RETURN (FALSE);
2079 END;
2080
2081 -- ===========================================================
2082 -- Step 12: Reduce inclusive tax if any from the line total to
2083 -- create the ITEM distributions.
2084 -- The distribution of tax (determine_recovery) will
2085 -- create the recoverable and non-rec distributions
2086 -- and tax variances
2087 -- ===========================================================
2088 /*Bug 14213367: Added parameters to the debug info*/
2089 l_debug_info := 'Reduce inclusive tax amount if any from PREPAY '||
2090 'line amount. l_inclusive_tax_amount: '||l_inclusive_tax_amount;
2091 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2092 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
2093 END IF;
2094
2095 IF (l_inclusive_tax_amount <> 0) THEN
2096 --Bugfix: 5577707 added the ABS for l_inclusive_tax_amount.
2097 l_apply_amount_no_tax_incl := l_apply_amount - abs(l_inclusive_tax_amount);
2098
2099 l_ppay_ln_amt_remaining_no_tax :=
2100 AP_Prepay_Utils_PKG.get_ln_pp_amt_remaining_no_tax(
2101 p_prepay_invoice_id,
2102 p_prepay_line_num);
2103
2104 END IF;
2105 /*Bug 14213367:Start */
2106 l_debug_info := 'l_apply_amount_no_tax_incl: '||l_apply_amount_no_tax_incl||
2107 ',l_ppay_ln_amt_remaining_no_tax: '||l_ppay_ln_amt_remaining_no_tax;
2108 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2109 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
2110 END IF;
2111 /*Bug 14213367:end */
2112
2113 END IF; /* P_calling_mode = 'PREPAYMENT APPLICATION' */
2114
2115 -- ==========================================================
2116 -- Step 13: Get Distribution information
2117 -- Here when we are coming in the context of the
2118 -- invoice import or prepayment application from
2119 -- the prepayment invoice and line level application,
2120 -- we will always have the
2121 -- prorate_flag parameter set to Y. Hence we need to
2122 -- derive all the distribution related information.
2123 -- We will populate the information into a PL/SQL table
2124 -- and during insertion we will loop through the table
2125 -- to get the values.
2126 -- In case of apply prepayments from the distribute
2127 -- prepayments window, we will know the basic distribution
2128 -- information including the apply amount.
2129 -- We will only calculate the quantities, stat_amount
2130 -- and new distribution line numbers in this case.
2131 -- ==========================================================
2132
2133 -- ==========================================================
2134 -- Step 13.1: Get maximum distribution line number from
2135 -- the standard invoice.
2136 -- ==========================================================
2137
2138 l_debug_info := 'Get maximum distribution line number from '||
2139 'the standard invoice';
2140 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2141 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
2142 END IF;
2143
2144 --From the Prepay line
2145 IF (P_calling_mode = 'PREPAYMENT APPLICATION') THEN
2146
2147 SELECT NVL(MAX(distribution_line_number),0)
2148 INTO l_max_dist_number
2149 FROM ap_invoice_distributions
2150 WHERE invoice_id = p_invoice_id
2151 AND invoice_line_number = l_prepay_ln_number;
2152
2153 --For Recoupment, 'PREPAY' distributions are tied to the ITEM line itself.
2154 ELSIF (P_calling_mode = 'RECOUPMENT') THEN
2155
2156 SELECT NVL(MAX(distribution_line_number),0)
2157 INTO l_max_dist_number
2158 FROM ap_invoice_distributions
2159 WHERE invoice_id = p_invoice_id
2160 AND invoice_line_number = p_invoice_line_number ;
2161
2162 END IF;
2163 /*Bug 14213367:Start */
2164 l_debug_info := 'P_calling_mode: '||P_calling_mode|| ',l_max_dist_number: '||l_max_dist_number;
2165 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2166 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
2167 END IF;
2168 /*Bug 14213367:end */
2169
2170 -- ===========================================================
2171 -- Step 13.2: Populate the PL/SQL table with the basic
2172 -- distribution information if the prorate flag
2173 -- is 'Y'
2174 -- ==========================================================
2175
2176 l_debug_info := 'Populate the PL/SQL table with the basic '||
2177 'distribution information p_calling_mode'||p_calling_mode;
2178 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2179 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
2180 END IF;
2181
2182 -- IF p_prepay_dist_info is not empty then assign it to
2183 -- l_prepay_dist_info, otherwise populate l_prepay_dist_info
2184 -- from the cursor c_ppay_dist_info.
2185 IF (NVL(P_calling_mode,'PREPAYMENT APPLICATION') = 'PREPAYMENT APPLICATION') THEN
2186
2187 IF (p_prorate_flag = 'Y') THEN -- p_prepay_dist_info is Empty
2188 l_debug_info := 'Open Cursor C_PPay_Dist_Info';
2189 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2190 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
2191 END IF;
2192 OPEN C_PPAY_DIST_INFO(
2193 p_prepay_invoice_id,
2194 p_prepay_line_num);
2195
2196 total_item_apply_amount := 0; -- 7834255
2197
2198 LOOP
2199 FETCH C_PPAY_DIST_INFO INTO
2200 l_prepay_dist_info(l_loop_counter).PREPAY_DISTRIBUTION_ID,
2201 l_prepay_dist_info(l_loop_counter).PPAY_AMOUNT,
2202 l_prepay_dist_info(l_loop_counter).PPAY_BASE_AMOUNT,
2203 l_prepay_dist_info(l_loop_counter).PPAY_AMOUNT_REMAINING,
2204 l_prepay_dist_info(l_loop_counter).PPAY_PO_DISTRIBUTION_ID,
2205 l_prepay_dist_info(l_loop_counter).PPAY_RCV_TRANSACTION_ID,
2206 l_prepay_dist_info(l_loop_counter).PPAY_QUANTITY_INVOICED,
2207 l_prepay_dist_info(l_loop_counter).PPAY_STAT_AMOUNT,
2208 l_prepay_dist_info(l_loop_counter).PPAY_PA_QUANTITY,
2209 l_prepay_dist_info(l_loop_counter).PREPAY_ACCOUNTING_DATE,
2210 l_prepay_dist_info(l_loop_counter).PREPAY_PERIOD_NAME,
2211 l_prepay_dist_info(l_loop_counter).PREPAY_GLOBAL_ATTR_CATEGORY,
2212 l_prepay_dist_info(l_loop_counter).LINE_TYPE_LOOKUP_CODE;
2213 EXIT WHEN C_PPAY_DIST_INFO%NOTFOUND OR C_PPAY_DIST_INFO%NOTFOUND IS NULL;
2214 -- Populate the APPLY Amount
2215 IF (l_apply_amount = l_ppay_ln_amount_remaining) THEN
2216
2217 l_prepay_dist_info(l_loop_counter).PREPAY_APPLY_AMOUNT :=
2218 l_prepay_dist_info(l_loop_counter).PPAY_AMOUNT_REMAINING;
2219
2220
2221 ELSE
2222
2223 -- for the case of inclusive tax the proration should be
2224 -- done with the apply_amount without tax and the prepay_line
2225 -- amount remaining wihtout tax as well.
2226 IF (l_inclusive_tax_amount <> 0) THEN
2227 -- Tax amount is included in PREPAY line amount
2228 l_prepay_dist_info(l_loop_counter).PREPAY_APPLY_AMOUNT :=
2229 AP_Utilities_PKG.AP_Round_Currency (
2230 (l_prepay_dist_info(l_loop_counter).PPAY_AMOUNT_REMAINING/
2231 l_ppay_ln_amt_remaining_no_tax) * l_apply_amount_no_tax_incl
2232 , l_std_inv_curr_code); -- 7834255
2233 ELSE
2234
2235 -- Tax amount is not included in PREPAY line amount
2236 -- this is an exclusive case or inclusive with tax amount as 0
2237 l_prepay_dist_info(l_loop_counter).PREPAY_APPLY_AMOUNT :=
2238 AP_Utilities_PKG.AP_Round_Currency (
2239 (l_prepay_dist_info(l_loop_counter).PPAY_AMOUNT_REMAINING/
2240 l_ppay_ln_amount_remaining) *
2241 l_apply_amount
2242 , l_std_inv_curr_code); -- 7834255
2243 END IF;
2244 END IF;
2245
2246 -- 7834255
2247 total_item_apply_amount := total_item_apply_amount + l_prepay_dist_info(l_loop_counter).PREPAY_APPLY_AMOUNT;
2248
2249 l_loop_counter := l_loop_counter + 1;
2250
2251 END LOOP;
2252
2253 --bug12888895, commented below code
2254 /*
2255 -- 7834255
2256 IF l_loop_counter > 1 THEN
2257 IF l_inclusive_tax_amount <> 0 THEN
2258 IF total_item_apply_amount <> l_apply_amount_no_tax_incl
2259 AND abs(total_item_apply_amount - l_apply_amount_no_tax_incl) <= 1
2260 THEN
2261 l_prepay_dist_info(l_loop_counter-1).PREPAY_APPLY_AMOUNT := l_prepay_dist_info(l_loop_counter-1).PREPAY_APPLY_AMOUNT
2262 - (total_item_apply_amount - l_apply_amount_no_tax_incl);
2263 END IF;
2264 ELSE
2265 IF total_item_apply_amount <> l_apply_amount
2266 AND abs(total_item_apply_amount - l_apply_amount) <= 1
2267 THEN
2268 l_prepay_dist_info(l_loop_counter-1).PREPAY_APPLY_AMOUNT := l_prepay_dist_info(l_loop_counter-1).PREPAY_APPLY_AMOUNT
2269 - (total_item_apply_amount - l_apply_amount);
2270 END IF;
2271 END IF;
2272 END IF;
2273 */
2274
2275 --bug12888895
2276 if l_inclusive_tax_amount <> 0 THEN
2277 l_prepay_adjusted_amount := total_item_apply_amount - l_apply_amount_no_tax_incl;
2278 else
2279 l_prepay_adjusted_amount := total_item_apply_amount - l_apply_amount;
2280 end if;
2281
2282 ELSE /* p_prepay_dist_info is not empty */
2283 -- the form will make sure the prepay_apply_amount
2284 -- does not have tax included in the case of inclusive
2285 -- tax calculation.
2286 l_prepay_dist_info := p_prepay_dist_info;
2287 END IF;
2288
2289
2290 ELSIF (P_calling_mode = 'RECOUPMENT') THEN
2291
2292 l_debug_info := 'Open Cursor C_PPay_Dist_Info_Recoup l_ppay_invoice_id, l_ppay_line_number'||p_prepay_invoice_id||','||p_prepay_line_num;
2293 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2294 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
2295 END IF;
2296
2297 OPEN C_PPAY_DIST_INFO_RECOUP(
2298 p_prepay_invoice_id,
2299 p_prepay_line_num);
2300
2301 total_item_apply_amount := 0; -- 7834255
2302
2303 LOOP
2304 l_debug_info := 'Fetch C_PPay_Dist_Info_Recoup into local variables';
2305 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2306 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
2307 END IF;
2308
2309 FETCH C_PPAY_DIST_INFO_RECOUP INTO
2310 l_prepay_dist_info(l_loop_counter).PREPAY_DISTRIBUTION_ID,
2311 l_prepay_dist_info(l_loop_counter).PPAY_AMOUNT,
2312 l_prepay_dist_info(l_loop_counter).PPAY_BASE_AMOUNT,
2313 l_prepay_dist_info(l_loop_counter).PPAY_AMOUNT_REMAINING,
2314 l_prepay_dist_info(l_loop_counter).PPAY_PO_DISTRIBUTION_ID,
2315 l_prepay_dist_info(l_loop_counter).PPAY_RCV_TRANSACTION_ID,
2316 l_prepay_dist_info(l_loop_counter).PPAY_QUANTITY_INVOICED,
2317 l_prepay_dist_info(l_loop_counter).PPAY_STAT_AMOUNT,
2318 l_prepay_dist_info(l_loop_counter).PPAY_PA_QUANTITY,
2319 l_prepay_dist_info(l_loop_counter).PREPAY_ACCOUNTING_DATE,
2320 l_prepay_dist_info(l_loop_counter).PREPAY_PERIOD_NAME,
2321 l_prepay_dist_info(l_loop_counter).PREPAY_GLOBAL_ATTR_CATEGORY,
2322 l_prepay_dist_info(l_loop_counter).LINE_TYPE_LOOKUP_CODE,
2323 l_prepay_dist_info(l_loop_counter).PARENT_CHRG_APPL_TO_DIST_ID,
2324 l_prepay_dist_info(l_loop_counter).PARENT_RELATED_ID;
2325
2326 EXIT WHEN C_PPAY_DIST_INFO_RECOUP%NOTFOUND OR C_PPAY_DIST_INFO_RECOUP%NOTFOUND IS NULL;
2327
2328 -- Populate the APPLY Amount
2329
2330 IF (l_apply_amount = l_ppay_ln_amount_remaining) THEN
2331 l_debug_info := 'Test1';
2332 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2333 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
2334 END IF;
2335
2336 l_prepay_dist_info(l_loop_counter).PREPAY_APPLY_AMOUNT :=
2337 l_prepay_dist_info(l_loop_counter).PPAY_AMOUNT_REMAINING;
2338
2339
2340 ELSE
2341
2342 l_debug_info := 'Test2';
2343 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2344 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
2345 END IF;
2346
2347 --Contract Payments:
2348 --Proration for both exclusive and inclusive tax cases, since
2349 --for recoupment regardless of how the tax was calculated on the
2350 --parent Item line on the Prepayment invoice, when recouping
2351 --the Prepay dists as well as tax tied to those distributions will
2352 --be part of the same ITEM line on the Standard invoice which is recouping.
2353
2354 l_prepay_dist_info(l_loop_counter).PREPAY_APPLY_AMOUNT :=
2355 AP_Utilities_PKG.AP_Round_Currency (
2356 (l_prepay_dist_info(l_loop_counter).PPAY_AMOUNT_REMAINING/
2357 l_ppay_ln_amount_remaining) *
2358 l_apply_amount
2359 , l_std_inv_curr_code); -- 7834255
2360
2361 END IF;
2362
2363 -- 7834255
2364 total_item_apply_amount := total_item_apply_amount + l_prepay_dist_info(l_loop_counter).PREPAY_APPLY_AMOUNT;
2365
2366 l_loop_counter := l_loop_counter + 1;
2367
2368 END LOOP;
2369
2370 --bug12888895, commented below code
2371 /*
2372 -- 7834255
2373 IF l_loop_counter > 1 THEN
2374 IF total_item_apply_amount <> l_apply_amount
2375 AND abs(total_item_apply_amount - l_apply_amount) <= 1
2376 THEN
2377 l_prepay_dist_info(l_loop_counter-1).PREPAY_APPLY_AMOUNT := l_prepay_dist_info(l_loop_counter-1).PREPAY_APPLY_AMOUNT
2378 - (total_item_apply_amount - l_apply_amount);
2379 END IF;
2380 END IF;
2381 */
2382
2383 --bug12888895
2384 l_prepay_adjusted_amount := total_item_apply_amount - l_apply_amount;
2385
2386 END IF; /* P_calling_mode .... */
2387
2388 --start bug12888895
2389 l_loop_counter := l_loop_counter - 1;
2390
2391
2392 IF l_prepay_adjusted_amount <> 0 THEN
2393 while l_loop_counter > 0 and l_prepay_adjusted_amount <> 0
2394 loop
2395
2396 if l_prepay_dist_info(l_loop_counter).PREPAY_APPLY_AMOUNT - l_prepay_adjusted_amount >= 0 then
2397 l_prepay_dist_info(l_loop_counter).PREPAY_APPLY_AMOUNT := l_prepay_dist_info(l_loop_counter).PREPAY_APPLY_AMOUNT - l_prepay_adjusted_amount;
2398 l_prepay_adjusted_amount := 0;
2399 else
2400 l_prepay_adjusted_amount := l_prepay_adjusted_amount - l_prepay_dist_info(l_loop_counter).PREPAY_APPLY_AMOUNT;
2401 l_prepay_dist_info(l_loop_counter).PREPAY_APPLY_AMOUNT := 0;
2402 end if;
2403
2404 l_loop_counter := l_loop_counter - 1;
2405
2406 end loop;
2407 END IF;
2408
2409 --end bug12888895
2410
2411 -- ===========================================================
2412 -- Step 13.3: Populate the PL/SQL table with the distribution
2413 -- line number, base amounts, quantity invoiced,
2414 -- pa quantity and stat amount. (PREPAY Distributions)
2415 -- ===========================================================
2416
2417 l_debug_info := 'Populate the PL/SQL table with the PREPAY Distributions '||
2418 'information';
2419 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2420 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
2421 END IF;
2422
2423
2424 FOR l_loop_variable IN nvl(l_prepay_dist_info.FIRST,0)..nvl(l_prepay_dist_info.LAST,0) LOOP
2425
2426 -- Get the Next Distribution Line Number
2427
2428 l_debug_info := 'Populate other generic info for the prepay dists';
2429 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2430 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
2431 END IF;
2432
2433 l_prepay_dist_info(l_loop_variable).PREPAY_DIST_LINE_NUMBER :=
2434 l_max_dist_number + l_dist_line_counter;
2435 l_dist_line_counter := l_dist_line_counter+1;
2436
2437 -- Get the Base Amount at the Standard Invoice Exchange Rate
2438
2439 IF (l_std_inv_curr_code <> l_base_currency_code) THEN
2440 IF l_std_inv_xrate_type = 'User' THEN
2441 l_prepay_dist_info(l_loop_variable).PREPAY_BASE_AMOUNT :=
2442 AP_Utilities_PKG.AP_Round_Currency(
2443 l_prepay_dist_info(l_loop_variable).PREPAY_APPLY_AMOUNT *
2444 l_std_inv_xrate,
2445 l_base_currency_code);
2446 ELSE
2447 /*Bug 14592297:Start*/
2448 l_debug_info:='There might not be a defined Currency Exchange Rate available for '||
2449 l_std_inv_curr_code||' to '||l_base_currency_code|| ' on '||
2450 l_std_inv_xdate||' for '||l_std_inv_xrate_type||' exchange rate type' ;
2451
2452
2453 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2454 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
2455 END IF;
2456 /*Bug 14592297:End*/
2457 l_prepay_dist_info(l_loop_variable).PREPAY_BASE_AMOUNT :=
2458 GL_Currency_API.Convert_Amount(
2459 l_std_inv_curr_code,
2460 l_base_currency_code,
2461 l_std_inv_xdate,
2462 l_std_inv_xrate_type,
2463 l_prepay_dist_info(l_loop_variable).PREPAY_APPLY_AMOUNT);
2464 /*Bug 14592297:Start*/
2465
2466 l_debug_info:='Successfull call returned from GL_CURRENCY_API';
2467 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2468 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
2469 END IF;
2470 /*Bug 14592297:End*/
2471 END IF;
2472
2473 -- Get the Base Amount at the Prepayment Invoice Exchange Rate
2474 IF l_ppay_inv_xrate_type = 'User' THEN
2475 l_prepay_dist_info(l_loop_variable).PREPAY_BASE_AMT_PPAY_XRATE :=
2476 AP_Utilities_PKG.AP_Round_Currency(
2477 l_prepay_dist_info(l_loop_variable).PREPAY_APPLY_AMOUNT *
2478 l_ppay_inv_xrate,
2479 l_base_currency_code);
2480 ELSE
2481 /*Bug 14592297:Start*/
2482 l_debug_info:='There might not be a defined Currency Exchange Rate available for '||
2483 l_ppay_inv_curr_code||' to '||l_base_currency_code|| ' on '||
2484 l_ppay_inv_xdate||' for '||l_ppay_inv_xrate_type||' exchange rate type' ;
2485
2486 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2487 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
2488 END IF;
2489 /*Bug 14592297:End*/
2490
2491 l_prepay_dist_info(l_loop_variable).PREPAY_BASE_AMT_PPAY_XRATE :=
2492 GL_Currency_API.Convert_Amount(
2493 l_ppay_inv_curr_code,
2494 l_base_currency_code,
2495 l_ppay_inv_xdate,
2496 l_ppay_inv_xrate_type,
2497 l_prepay_dist_info(l_loop_variable).PREPAY_APPLY_AMOUNT);
2498 /*Bug 14592297:Start*/
2499
2500 l_debug_info:='Successfull call returned from GL_CURRENCY_API';
2501 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2502 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
2503 END IF;
2504 /*Bug 14592297:End*/
2505 END IF;
2506
2507 -- Get the Base Amount at the Prepayment Payment Exchange Rate
2508 IF l_ppay_pay_xrate_type = 'User' THEN
2509 l_prepay_dist_info(l_loop_variable).PREPAY_BASE_AMT_PPAY_PAY_XRATE :=
2510 AP_Utilities_PKG.AP_Round_Currency(
2511 l_prepay_dist_info(l_loop_variable).PREPAY_APPLY_AMOUNT *
2512 l_ppay_pay_xrate,
2513 l_base_currency_code);
2514 ELSE
2515 /*Bug 14592297:Start*/
2516 l_debug_info:='There might not be a defined Currency Exchange Rate available for '||
2517 l_ppay_pay_curr_code||' to '||l_base_currency_code|| ' on '||
2518 l_ppay_pay_xdate||' for '||l_ppay_pay_xrate_type||' exchange rate type' ;
2519
2520 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2521 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
2522 END IF;
2523 /*Bug 14592297:End*/
2524 l_prepay_dist_info(l_loop_variable).PREPAY_BASE_AMT_PPAY_PAY_XRATE :=
2525 GL_Currency_API.Convert_Amount(
2526 l_ppay_pay_curr_code,
2527 l_base_currency_code,
2528 l_ppay_pay_xdate,
2529 l_ppay_pay_xrate_type,
2530 l_prepay_dist_info(l_loop_variable).PREPAY_APPLY_AMOUNT);
2531 /*Bug 14592297:Start*/
2532
2533 l_debug_info:='Successfull call returned from GL_CURRENCY_API';
2534 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2535 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
2536 END IF;
2537 /*Bug 14592297:End*/
2538
2539 END IF;
2540 END IF;
2541
2542 -- Get Quantity Invoiced, PA_Quantity and the STAT Amount at the
2543 -- Distribution Level
2544
2545 IF (l_apply_amount = l_ppay_ln_amount_remaining) THEN
2546
2547 l_debug_info := 'Get Quantity_Invoiced, PA_Quantity, Stat_Amount';
2548 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2549 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
2550 END IF;
2551
2552 SELECT NVL( ABS(SUM(quantity_invoiced)), 0 ),
2553 NVL( ABS(SUM(stat_amount)), 0 ),
2554 NVL( ABS(SUM(pa_quantity)), 0 )
2555 INTO l_prepay_dist_s_quant_invoiced,
2556 l_prepay_dist_s_stat_amount,
2557 l_prepay_dist_s_pa_quantity
2558 FROM ap_invoice_distributions
2559 WHERE prepay_distribution_id =
2560 l_prepay_dist_info(l_loop_variable).PREPAY_DISTRIBUTION_ID;
2561
2562 IF (l_prepay_dist_info(l_loop_variable).PPAY_PO_DISTRIBUTION_ID IS NOT NULL
2563 OR
2564 l_prepay_dist_info(l_loop_variable).PPAY_RCV_TRANSACTION_ID IS NOT NULL)
2565 THEN
2566
2567 l_prepay_dist_info(l_loop_variable).PREPAY_QUANTITY_INVOICED :=
2568 ( -1 ) *
2569 (l_prepay_dist_info(l_loop_variable).PPAY_QUANTITY_INVOICED -
2570 l_prepay_dist_s_quant_invoiced);
2571 END IF;
2572
2573 IF l_prepay_dist_info(l_loop_variable).PPAY_STAT_AMOUNT IS NOT NULL THEN
2574
2575 l_prepay_dist_info(l_loop_variable).PREPAY_STAT_AMOUNT :=
2576 ( -1 ) *
2577 (l_prepay_dist_info(l_loop_variable).PPAY_STAT_AMOUNT -
2578 l_prepay_dist_s_stat_amount);
2579 END IF;
2580
2581 IF l_prepay_dist_info(l_loop_variable).PPAY_PA_QUANTITY IS NOT NULL THEN
2582
2583 l_prepay_dist_info(l_loop_variable).PREPAY_PA_QUANTITY :=
2584 ( -1 ) *
2585 (l_prepay_dist_info(l_loop_variable).PPAY_PA_QUANTITY -
2586 l_prepay_dist_s_pa_quantity);
2587 END IF;
2588
2589 ELSE
2590
2591 IF (l_prepay_dist_info(l_loop_variable).PPAY_PO_DISTRIBUTION_ID IS NOT NULL
2592 OR
2593 l_prepay_dist_info(l_loop_variable).PPAY_RCV_TRANSACTION_ID IS NOT NULL)
2594 THEN
2595 l_prepay_dist_info(l_loop_variable).PREPAY_QUANTITY_INVOICED :=
2596 (-1) *
2597 ((l_prepay_dist_info(l_loop_variable).PREPAY_APPLY_AMOUNT/
2598 l_prepay_dist_info(l_loop_variable).PPAY_AMOUNT) *
2599 l_prepay_dist_info(l_loop_variable).PPAY_QUANTITY_INVOICED);
2600
2601 END IF;
2602
2603 IF l_prepay_dist_info(l_loop_variable).PPAY_STAT_AMOUNT IS NOT NULL THEN
2604
2605 l_prepay_dist_info(l_loop_variable).PREPAY_STAT_AMOUNT :=
2606 (-1) *
2607 ((l_prepay_dist_info(l_loop_variable).PREPAY_APPLY_AMOUNT/
2608 l_prepay_dist_info(l_loop_variable).PPAY_AMOUNT) *
2609 l_prepay_dist_info(l_loop_variable).PPAY_STAT_AMOUNT);
2610
2611 END IF;
2612
2613 IF l_prepay_dist_info(l_loop_variable).PPAY_PA_QUANTITY IS NOT NULL THEN
2614
2615 l_prepay_dist_info(l_loop_variable).PREPAY_PA_QUANTITY :=
2616 (-1) *
2617 ((l_prepay_dist_info(l_loop_variable).PREPAY_APPLY_AMOUNT/
2618 l_prepay_dist_info(l_loop_variable).PPAY_AMOUNT) *
2619 l_prepay_dist_info(l_loop_variable).PPAY_PA_QUANTITY);
2620
2621 END IF;
2622
2623 END IF;
2624 /*Bug 14213367:Start */
2625 l_debug_info := 'l_prepay_dist_info(l_loop_variable).PREPAY_QUANTITY_INVOICED: '||
2626 l_prepay_dist_info(l_loop_variable).PREPAY_QUANTITY_INVOICED||
2627 ',l_prepay_dist_info(l_loop_variable).PREPAY_STAT_AMOUNT: '||l_prepay_dist_info(l_loop_variable).PREPAY_STAT_AMOUNT;
2628 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2629 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
2630 END IF;
2631 l_debug_info := 'l_prepay_dist_info(l_loop_variable).PREPAY_PA_QUANTITY: '||
2632 l_prepay_dist_info(l_loop_variable).PREPAY_PA_QUANTITY;
2633 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2634 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
2635 END IF;
2636 /*Bug 14213367:end */
2637
2638 END LOOP;
2639
2640 -- ===========================================================
2641 -- Step 14: Insert PREPAY Distributions - We will call the
2642 -- INSERT_PREPAY_DISTS to insert the PREPAY
2643 -- distributions.
2644 -- ===========================================================
2645 --Contract Payments
2646 IF (p_calling_mode = 'RECOUPMENT') THEN
2647 l_invoice_line_number := p_invoice_line_number;
2648 ELSE
2649 l_invoice_line_number := l_prepay_ln_number;
2650 END IF;
2651
2652 /*Bug 14213367:Added input parameter to the debug info */
2653 l_debug_info := 'Call Ap_Prepay_Pkg.Insert_Prepay_Dists. l_invoice_line_number: '||
2654 l_invoice_line_number;
2655 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2656 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
2657 END IF;
2658
2659 l_dummy := AP_PREPAY_PKG.INSERT_PREPAY_DISTS(
2660 p_prepay_invoice_id,
2661 p_prepay_line_num,
2662 p_invoice_id,
2663 l_std_inv_batch_id,
2664 l_invoice_line_number,
2665 l_prepay_dist_info,
2666 p_user_id,
2667 p_last_update_login,
2668 p_calling_sequence,
2669 p_error_message);
2670
2671 IF l_dummy = FALSE THEN
2672
2673 l_result:= AP_PREPAY_UTILS_PKG.Unlock_Line(
2674 p_prepay_invoice_id,
2675 p_prepay_line_num);
2676
2677 RETURN (FALSE);
2678
2679 END IF;
2680
2681
2682 -- =======================================================================
2683 -- Step 15: Determine_recovery IF calling_mode is 'PREPAYMENT APPLICATION'
2684 -- Call eTax service.
2685 -- =======================================================================
2686 IF (P_calling_mode = 'PREPAYMENT APPLICATION') THEN
2687
2688 l_debug_info := 'Distribute tax';
2689 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2690 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
2691 END IF;
2692
2693 IF NOT (ap_etax_pkg.calling_etax(
2694 p_invoice_id => p_invoice_id,
2695 p_line_number => NULL,
2696 p_calling_mode => 'DISTRIBUTE',
2697 p_override_status => NULL,
2698 p_line_number_to_delete => NULL,
2699 p_Interface_Invoice_Id => NULL,
2700 p_all_error_messages => 'N',
2701 p_error_code => p_error_message,
2702 p_calling_sequence => l_current_calling_sequence)) THEN
2703
2704 RAISE tax_exception;
2705
2706 END IF;
2707
2708 -- ===========================================================
2709 -- Step 16: Update line amount total if there is a diff of tax
2710 -- rate or tax recovery rate and the calculation point
2711 -- is the Standard Invoice. This is valid only in the
2712 -- inclusive case.
2713 -- ===========================================================
2714 l_debug_info := 'Update parent PREPAY line amount if required';
2715 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2716 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
2717 END IF;
2718
2719 IF (l_inclusive_tax_amount <> 0) THEN
2720 -- We need to update the PREPAY line amount only in the case the
2721 -- tax is inclusive. For the exclusive case the parent line is
2722 -- the TAX line and it will be created with the correct amount
2723 -- while calling calculate tax
2724
2725 l_debug_info := 'Get prepay tax difference amount from non-rec '||
2726 'tax distributions';
2727 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2728 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
2729 END IF;
2730
2731 BEGIN
2732
2733 SELECT SUM(NVL(aid.prepay_tax_diff_amount, 0))
2734 INTO l_prepay_tax_diff_amt
2735 FROM ap_invoice_distributions_all aid
2736 WHERE aid.invoice_id = p_invoice_id
2737 AND aid.invoice_line_number = l_prepay_ln_number
2738 AND aid.line_type_lookup_code = 'NONREC_TAX'
2739 AND NVL(aid.reversal_flag,'N') <> 'Y';
2740
2741 EXCEPTION
2742 WHEN NO_DATA_FOUND THEN
2743 l_prepay_tax_diff_amt := 0;
2744
2745 END;
2746
2747
2748 IF (l_prepay_tax_diff_amt <> 0) THEN
2749
2750 l_apply_amount := (-1 * l_apply_amount) - l_prepay_tax_diff_amt;
2751
2752 l_debug_info := 'Recalculate base amount including the tax '||
2753 'difference amount';
2754
2755 IF (l_std_inv_curr_code <> l_base_currency_code) THEN
2756 IF l_std_inv_xrate_type = 'User' THEN
2757 l_prepay_ln_base_amount := AP_Utilities_PKG.AP_Round_Currency(
2758 l_apply_amount* l_std_inv_xrate,
2759 l_base_currency_code);
2760 ELSE
2761 /*Bug 14592297:Start*/
2762 l_debug_info:='There might not be a defined Currency Exchange Rate available for '||
2763 l_std_inv_curr_code||' to '||l_base_currency_code|| ' on '||
2764 l_std_inv_xdate||' for '||l_std_inv_xrate_type||' exchange rate type' ;
2765
2766 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2767 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
2768 END IF;
2769 /*Bug 14592297:End*/
2770 l_prepay_ln_base_amount := GL_Currency_API.Convert_Amount(
2771 l_std_inv_curr_code,
2772 l_base_currency_code,
2773 l_std_inv_xdate,
2774 l_std_inv_xrate_type,
2775 l_apply_amount);
2776 /*Bug 14592297:Start*/
2777
2778 l_debug_info:='Successfull call returned from GL_CURRENCY_API';
2779 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2780 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
2781 END IF;
2782 /*Bug 14592297:End*/
2783
2784 END IF;
2785 END IF;
2786
2787 l_debug_info := 'Update PREPAY line amount and base amount '||
2788 'including the prepay tax difference';
2789 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2790 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
2791 END IF;
2792
2793 BEGIN
2794 UPDATE ap_invoice_lines_all ail
2795 SET amount = l_apply_amount,
2796 base_amount = l_prepay_ln_base_amount
2797 WHERE ail.invoice_id = p_invoice_id
2798 AND ail.line_number = l_prepay_ln_number;
2799 END;
2800 END IF;
2801
2802 END IF;
2803
2804 ELSIF (p_calling_mode = 'RECOUPMENT') THEN
2805
2806 --SMYADAM: Need to call the ETAX api once the decision
2807 --is made regarding which api needs to be called,
2808 --to sync up the recouped tax distributions
2809 --with the etax repository.Also need to add
2810 --a similar call in discard inv line api too.
2811 NULL;
2812
2813 END IF; /* P_calling_mode = 'PREPAYMENT APPLICATION' */
2814
2815 -- ===========================================================
2816 -- Step 17: Update Prepayment
2817 -- ===========================================================
2818
2819 l_debug_info := 'Update Prepayment distributions';
2820 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2821 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
2822 END IF;
2823
2824 l_dummy := AP_PREPAY_PKG.Update_Prepayment(
2825 l_prepay_dist_info,
2826 p_prepay_invoice_id,
2827 p_prepay_line_num,
2828 p_invoice_id,
2829 l_invoice_line_number,
2830 'APPLICATION',
2831 p_calling_mode,
2832 p_calling_sequence,
2833 p_error_message);
2834
2835 IF l_dummy = FALSE THEN
2836 l_debug_info := 'Unlock Line';
2837 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2838 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
2839 END IF;
2840
2841 l_result:= AP_PREPAY_UTILS_PKG.Unlock_Line(
2842 p_prepay_invoice_id,
2843 p_prepay_line_num);
2844
2845 RETURN (FALSE);
2846
2847 END IF;
2848
2849 -- ===========================================================
2850 -- Step 18: Update PO/RCV information
2851 -- ===========================================================
2852
2853 l_debug_info := 'Update_PO_Receipt_Info';
2854 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2855 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
2856 END IF;
2857
2858
2859 l_dummy := AP_PREPAY_PKG.Update_PO_Receipt_Info(
2860 l_prepay_dist_info,
2861 p_prepay_invoice_id,
2862 p_prepay_line_num,
2863 p_invoice_id,
2864 l_invoice_line_number,
2865 l_ppay_ln_po_line_location_id,
2866 l_ppay_ln_uom,
2867 'APPLICATION',
2868 l_ppay_ln_match_basis,
2869 p_calling_sequence,
2870 p_error_message);
2871
2872 IF l_dummy = FALSE THEN
2873
2874 l_result := AP_PREPAY_UTILS_PKG.Unlock_Line(
2875 p_prepay_invoice_id,
2876 p_prepay_line_num);
2877
2878 RETURN (FALSE);
2879
2880 END IF;
2881
2882 -- ==========================================================
2883 -- Step 19: Get Apply Amount in Payment Currency
2884 -- This information is used only when we update the
2885 -- Payment Schedules.
2886 -- Here we will get this amount and pass it to the
2887 -- Sub Procedure that updates the Payment Schedules.
2888 -- ==========================================================
2889 --Bug5224883
2890 -- Get the exculusive tax amount for the prepay appln line.
2891 SELECT sum(aid.amount) into l_prepay_excl_tax_amt
2892 FROM ap_invoice_lines_all ail,ap_invoice_distributions_all aid
2893 WHERE ail.line_type_lookup_code='TAX'
2894 AND ail.invoice_id=p_invoice_id
2895 and aid.invoice_id=ail.invoice_id
2896 AND aid.invoice_line_number=ail.line_number
2897 AND ail.prepay_line_number is not null
2898 AND exists( select 1 from ap_invoice_distributions_all aid1
2899 where aid1.invoice_id=p_invoice_id
2900 and aid1.invoice_line_number=l_prepay_ln_number
2901 and aid1.invoice_distribution_id=aid.charge_applicable_to_dist_id);
2902 -- Bug 5307022. Added the NVL for amount calculation in case of non exclusive tax
2903 l_apply_amount:= l_apply_amount - nvl(l_prepay_excl_tax_amt,0); --Bug5224883
2904
2905 /*Bug 14213367:Added input parameter to the debug info */
2906 l_debug_info := 'Get Apply Amount in Payment Currency. l_prepay_excl_tax_amt: '||
2907 l_prepay_excl_tax_amt|| ',l_apply_amount: '||l_apply_amount;
2908 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2909 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
2910 END IF;
2911 l_debug_info := 'l_ppay_inv_curr_code: '||l_ppay_inv_curr_code||',l_ppay_inv_pay_curr_code: '||
2912 l_ppay_inv_pay_curr_code;
2913 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2914 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
2915 END IF;
2916
2917 IF (l_ppay_inv_curr_code <> l_ppay_inv_pay_curr_code) THEN
2918 /*Bug 14592297:Start*/
2919 l_debug_info:='There might not be a defined Currency Exchange Rate available for '||l_ppay_inv_curr_code||' to '||
2920 l_ppay_inv_pay_curr_code|| ' on '||l_ppay_inv_pay_cross_rate_date||' for '||
2921 l_ppay_inv_pay_cross_rate_type||' exchange rate type' ;
2922 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2923 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME,l_debug_info);
2924 END IF;
2925 /*Bug 14592297:End*/
2926
2927 l_ppay_apply_amt_in_pay_curr :=
2928 GL_Currency_API.Convert_Amount (
2929 l_ppay_inv_curr_code,
2930 l_ppay_inv_pay_curr_code,
2931 l_ppay_inv_pay_cross_rate_date,
2932 l_ppay_inv_pay_cross_rate_type,
2933 l_apply_amount);
2934 /*Bug 14592297:Start*/
2935 l_debug_info:='Successfull call returned from GL_CURRENCY_API';
2936
2937 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2938 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME,l_debug_info);
2939 END IF;
2940 /*Bug 14592297:End*/
2941 ELSE
2942 l_ppay_apply_amt_in_pay_curr := l_apply_amount;
2943 END IF;
2944
2945
2946 -- ===========================================================
2947 -- Step 20: Update Payment Schedules
2948 -- ===========================================================
2949
2950 IF NVL(p_prepay_included, 'N') = 'N' THEN
2951 --Contract Payments: No modification needed for this api.
2952 l_debug_info := 'Update Payment Schedules';
2953 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2954 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
2955 END IF;
2956
2957 l_dummy := AP_PREPAY_PKG.Update_Payment_Schedule(
2958 p_invoice_id,
2959 p_prepay_invoice_id,
2960 p_prepay_line_num,
2961 l_ppay_apply_amt_in_pay_curr,
2962 'APPLICATION',
2963 l_ppay_inv_pay_curr_code,
2964 p_user_id,
2965 p_last_update_login,
2966 p_calling_sequence,
2967 p_calling_mode,
2968 p_error_message);
2969
2970 IF l_dummy = FALSE THEN
2971 l_result := AP_PREPAY_UTILS_PKG.Unlock_Line(
2972 p_prepay_invoice_id,
2973 p_prepay_line_num);
2974
2975 RETURN (FALSE);
2976 END IF;
2977 END IF;
2978
2979 -- Bug 5056104 - below step obsolete in R12
2980
2981 -- ===========================================================
2982 -- Step 21: Calculate/Update Rounding Amounts
2983 -- ===========================================================
2984
2985 -- l_debug_info := 'Calculate/Update Rounding Amounts';
2986 -- IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2987 -- FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
2988 --END IF;
2989 -- ===========================================================
2990 -- Step 21.1: Identify if this application is the final
2991 -- application for this prepayment invoice line. If so then
2992 -- it is considered final application for all the distributions.
2993 -- pertaining to this prepayment invoice line.
2994 -- ===========================================================
2995
2996 --COMMENT!!!!!!This comment is for who is going to modify latter on the
2997 -- final application rounding. The l_apply_amount at this moment includes
2998 -- any tax difference etax has returned in the case the calculation
2999 -- point for the tax is the standard invoice.
3000 -- IF (l_apply_amount = l_ppay_ln_amount_remaining) THEN
3001 -- l_final_application := 'Y';
3002 -- END IF;
3003
3004 --IF l_base_currency_code <> l_std_inv_curr_code THEN
3005 --l_debug_info := 'Update Rounding Amounts';
3006 --IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3007 -- FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
3008 --END IF;
3009
3010 -- l_dummy := AP_PREPAY_PKG.Update_Rounding_Amounts(
3011 -- p_prepay_invoice_id,
3012 -- p_prepay_line_num,
3013 -- p_invoice_id,
3014 -- l_invoice_line_number,
3015 -- l_final_application,
3016 -- l_current_calling_sequence,
3017 -- p_error_message);
3018
3019 -- IF l_dummy = FALSE THEN
3020 -- l_result := AP_PREPAY_UTILS_PKG.Unlock_Line(
3021 -- p_prepay_invoice_id,
3022 -- p_prepay_line_num);
3023 -- RETURN (FALSE);
3024 -- END IF;
3025 -- END IF;
3026
3027 -- ===========================================================
3028 -- Step 22: Unlock the Locked Line
3029 -- ===========================================================
3030
3031 l_debug_info := 'Unlock the Locked Line';
3032 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3033 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
3034 END IF;
3035
3036 l_result := AP_PREPAY_UTILS_PKG.Unlock_Line(
3037 p_prepay_invoice_id,
3038 p_prepay_line_num);
3039
3040 -- ===========================================================
3041 -- Step 23: If we are here we have done everything we need to
3042 -- Hence we can return TRUE to the calling module.
3043 -- ===========================================================
3044
3045 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
3046 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_PREPAY_PKG.Apply_Prepay_Line(-)');
3047 END IF;
3048
3049 RETURN (TRUE);
3050
3051 EXCEPTION
3052 WHEN tax_exception THEN
3053 l_result:= AP_PREPAY_UTILS_PKG.Unlock_Line(
3054 p_prepay_invoice_id,
3055 p_prepay_line_num);
3056
3057 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
3058 FND_MESSAGE.SET_TOKEN('ERROR', p_error_message, TRUE);
3059 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_current_calling_sequence);
3060 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
3061 APP_EXCEPTION.RAISE_EXCEPTION;
3062
3063 WHEN OTHERS THEN
3064 IF (SQLCODE <> -20001) THEN
3065 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
3066 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
3067 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',
3068 l_current_calling_sequence);
3069 FND_MESSAGE.SET_TOKEN('PARAMETERS',
3070 'P_PREPAY_INVOICE_ID = '||P_PREPAY_INVOICE_ID
3071 ||', P_PREPAY_LINE_NUM = '||P_PREPAY_LINE_NUM
3072 ||', P_PRORATE_FLAG = '||P_PRORATE_FLAG
3073 ||', P_INVOICE_ID = '||P_INVOICE_ID
3074 ||', P_APPLY_AMOUNT = '||P_APPLY_AMOUNT
3075 ||', P_GL_DATE = '||P_GL_DATE
3076 ||', P_PERIOD_NAME = '||P_PERIOD_NAME
3077 ||', P_PREPAY_INCLUDED = '||P_PREPAY_INCLUDED
3078 ||', P_USER_ID = '||P_USER_ID
3079 ||', P_LAST_UPDATE_LOGIN = '||P_LAST_UPDATE_LOGIN);
3080
3081 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
3082
3083 IF INSTR(l_calling_program,'Apply Prepayment Form') > 0 THEN
3084 APP_EXCEPTION.RAISE_EXCEPTION;
3085 ELSE
3086 p_error_message := FND_MESSAGE.GET;
3087 END IF;
3088
3089 END IF;
3090
3091 l_result := AP_PREPAY_UTILS_PKG.Unlock_Line(
3092 p_prepay_invoice_id,
3093 p_prepay_line_num);
3094 RETURN (FALSE);
3095 END Apply_Prepay_Line;
3096
3097
3098 FUNCTION Insert_Prepay_Line(
3099 p_prepay_invoice_id IN NUMBER,
3100 p_prepay_line_num IN NUMBER,
3101 p_invoice_id IN NUMBER,
3102 p_prepay_line_number IN NUMBER,
3103 p_amount_to_apply IN NUMBER,
3104 p_base_amount_to_apply IN NUMBER,
3105 p_gl_date IN DATE,
3106 p_period_name IN VARCHAR2,
3107 p_prepay_included IN VARCHAR2,
3108 p_quantity_invoiced IN NUMBER,
3109 p_stat_amount IN NUMBER,
3110 p_pa_quantity IN NUMBER,
3111 p_user_id IN NUMBER,
3112 p_last_update_login IN NUMBER,
3113 p_calling_sequence IN VARCHAR2,
3114 p_error_message OUT NOCOPY VARCHAR2)
3115 RETURN BOOLEAN IS
3116
3117 l_debug_info VARCHAR2(4000); --Changed length from 100 to 4000 (8534097)
3118 l_current_calling_sequence VARCHAR2(2000);
3119 l_calling_program VARCHAR2(1000);
3120 l_result BOOLEAN;
3121 l_api_name VARCHAR2(50);
3122
3123 BEGIN
3124
3125 l_api_name := 'Insert_Prepay_Line';
3126 l_calling_program := p_calling_sequence;
3127 l_current_calling_sequence := 'Insert_Prepay_Line<-'
3128 ||p_calling_sequence;
3129
3130 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
3131 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_PREPAY_PKG.Insert_Prepay_Line(+)');
3132 END IF;
3133
3134 l_debug_info := 'Insert PREPAY Line';
3135 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3136 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
3137 END IF;
3138
3139 INSERT INTO AP_Invoice_Lines(
3140 INVOICE_ID,
3141 LINE_NUMBER,
3142 LINE_TYPE_LOOKUP_CODE,
3143 REQUESTER_ID,
3144 DESCRIPTION,
3145 LINE_SOURCE,
3146 LINE_GROUP_NUMBER ,
3147 INVENTORY_ITEM_ID ,
3148 ITEM_DESCRIPTION ,
3149 SERIAL_NUMBER ,
3150 MANUFACTURER ,
3151 MODEL_NUMBER ,
3152 WARRANTY_NUMBER ,
3153 GENERATE_DISTS ,
3154 MATCH_TYPE ,
3155 DISTRIBUTION_SET_ID,
3156 ACCOUNT_SEGMENT ,
3157 BALANCING_SEGMENT,
3158 COST_CENTER_SEGMENT,
3159 OVERLAY_DIST_CODE_CONCAT,
3160 DEFAULT_DIST_CCID,
3161 PRORATE_ACROSS_ALL_ITEMS,
3162 ACCOUNTING_DATE,
3163 PERIOD_NAME,
3164 DEFERRED_ACCTG_FLAG,
3165 DEF_ACCTG_START_DATE,
3166 DEF_ACCTG_END_DATE,
3167 DEF_ACCTG_NUMBER_OF_PERIODS,
3168 DEF_ACCTG_PERIOD_TYPE,
3169 SET_OF_BOOKS_ID,
3170 AMOUNT,
3171 BASE_AMOUNT,
3172 ROUNDING_AMT,
3173 QUANTITY_INVOICED,
3174 UNIT_MEAS_LOOKUP_CODE,
3175 UNIT_PRICE,
3176 WFAPPROVAL_STATUS,
3177 -- USSGL_TRANSACTION_CODE, - Bug 4277744
3178 DISCARDED_FLAG,
3179 ORIGINAL_AMOUNT,
3180 ORIGINAL_BASE_AMOUNT,
3181 ORIGINAL_ROUNDING_AMT,
3182 CANCELLED_FLAG,
3183 INCOME_TAX_REGION,
3184 TYPE_1099,
3185 STAT_AMOUNT,
3186 PREPAY_INVOICE_ID,
3187 PREPAY_LINE_NUMBER,
3188 INVOICE_INCLUDES_PREPAY_FLAG,
3189 CORRECTED_INV_ID,
3190 CORRECTED_LINE_NUMBER,
3191 PO_HEADER_ID,
3192 PO_LINE_ID,
3193 PO_RELEASE_ID,
3194 PO_LINE_LOCATION_ID,
3195 PO_DISTRIBUTION_ID,
3196 RCV_TRANSACTION_ID,
3197 FINAL_MATCH_FLAG,
3198 ASSETS_TRACKING_FLAG,
3199 ASSET_BOOK_TYPE_CODE,
3200 ASSET_CATEGORY_ID,
3201 PROJECT_ID,
3202 TASK_ID,
3203 EXPENDITURE_TYPE,
3204 EXPENDITURE_ITEM_DATE,
3205 EXPENDITURE_ORGANIZATION_ID,
3206 PA_QUANTITY,
3207 PA_CC_AR_INVOICE_ID,
3208 PA_CC_AR_INVOICE_LINE_NUM,
3209 PA_CC_PROCESSED_CODE,
3210 AWARD_ID,
3211 AWT_GROUP_ID,
3212 REFERENCE_1,
3213 REFERENCE_2,
3214 RECEIPT_VERIFIED_FLAG,
3215 RECEIPT_REQUIRED_FLAG,
3216 RECEIPT_MISSING_FLAG,
3217 JUSTIFICATION ,
3218 EXPENSE_GROUP ,
3219 START_EXPENSE_DATE,
3220 END_EXPENSE_DATE ,
3221 RECEIPT_CURRENCY_CODE,
3222 RECEIPT_CONVERSION_RATE,
3223 RECEIPT_CURRENCY_AMOUNT,
3224 DAILY_AMOUNT ,
3225 WEB_PARAMETER_ID,
3226 ADJUSTMENT_REASON,
3227 MERCHANT_DOCUMENT_NUMBER,
3228 MERCHANT_NAME ,
3229 MERCHANT_REFERENCE ,
3230 MERCHANT_TAX_REG_NUMBER,
3231 MERCHANT_TAXPAYER_ID ,
3232 COUNTRY_OF_SUPPLY ,
3233 CREDIT_CARD_TRX_ID ,
3234 COMPANY_PREPAID_INVOICE_ID,
3235 CC_REVERSAL_FLAG ,
3236 CREATION_DATE ,
3237 CREATED_BY,
3238 LAST_UPDATED_BY ,
3239 LAST_UPDATE_DATE ,
3240 LAST_UPDATE_LOGIN ,
3241 PROGRAM_APPLICATION_ID ,
3242 PROGRAM_ID,
3243 PROGRAM_UPDATE_DATE ,
3244 REQUEST_ID,
3245 LINE_SELECTED_FOR_APPL_FLAG,
3246 PREPAY_APPL_REQUEST_ID ,
3247 ATTRIBUTE_CATEGORY ,
3248 ATTRIBUTE1,
3249 ATTRIBUTE2,
3250 ATTRIBUTE3,
3251 ATTRIBUTE4,
3252 ATTRIBUTE5,
3253 ATTRIBUTE6,
3254 ATTRIBUTE7,
3255 ATTRIBUTE8,
3256 ATTRIBUTE9,
3257 ATTRIBUTE10,
3258 ATTRIBUTE11,
3259 ATTRIBUTE12,
3260 ATTRIBUTE13,
3261 ATTRIBUTE14,
3262 ATTRIBUTE15,
3263 GLOBAL_ATTRIBUTE_CATEGORY,
3264 GLOBAL_ATTRIBUTE1,
3265 GLOBAL_ATTRIBUTE2,
3266 GLOBAL_ATTRIBUTE3,
3267 GLOBAL_ATTRIBUTE4,
3268 GLOBAL_ATTRIBUTE5,
3269 GLOBAL_ATTRIBUTE6,
3270 GLOBAL_ATTRIBUTE7,
3271 GLOBAL_ATTRIBUTE8,
3272 GLOBAL_ATTRIBUTE9,
3273 GLOBAL_ATTRIBUTE10,
3274 GLOBAL_ATTRIBUTE11,
3275 GLOBAL_ATTRIBUTE12,
3276 GLOBAL_ATTRIBUTE13,
3277 GLOBAL_ATTRIBUTE14,
3278 GLOBAL_ATTRIBUTE15,
3279 GLOBAL_ATTRIBUTE16,
3280 GLOBAL_ATTRIBUTE17,
3281 GLOBAL_ATTRIBUTE18,
3282 GLOBAL_ATTRIBUTE19,
3283 GLOBAL_ATTRIBUTE20,
3284 --ETAX: Invwkb
3285 SHIP_TO_LOCATION_ID,
3286 PRIMARY_INTENDED_USE,
3287 PRODUCT_FISC_CLASSIFICATION,
3288 TRX_BUSINESS_CATEGORY,
3289 PRODUCT_TYPE,
3290 PRODUCT_CATEGORY,
3291 USER_DEFINED_FISC_CLASS,
3292 PURCHASING_CATEGORY_ID,
3293 ORG_ID,
3294 PAY_AWT_GROUP_ID) --Bug 9058369
3295 SELECT
3296 p_invoice_id,
3297 p_prepay_line_number,
3298 'PREPAY',
3299 NULL,
3300 description,
3301 'PREPAY APPL',
3302 NULL,
3303 NULL,
3304 NULL,
3305 NULL,
3306 NULL,
3307 NULL,
3308 NULL,
3309 'D',
3310 match_type,
3311 NULL,
3312 NULL,
3313 NULL,
3314 NULL,
3315 NULL,
3316 NULL,
3317 'N',
3318 trunc(p_gl_date),--8532204
3319 p_period_name,
3320 'N',
3321 NULL,
3322 NULL,
3323 NULL,
3324 NULL,
3325 set_of_books_id,
3326 (-1 * p_amount_to_apply),
3327 (-1 * p_base_amount_to_apply),
3328 rounding_amt,
3329 p_quantity_invoiced,
3330 unit_meas_lookup_code,
3331 unit_price,
3332 'NOT REQUIRED',
3333 -- ussgl_transaction_code, - Bug 4277744
3334 'N',
3335 0,
3336 0,
3337 0,
3338 'N',
3339 income_tax_region,
3340 type_1099,
3341 p_stat_amount,
3342 invoice_id,
3343 line_number,
3344 p_prepay_included,
3345 NULL,
3346 NULL,
3347 po_header_id,
3348 po_line_id,
3349 po_release_id,
3350 po_line_location_id,
3351 po_distribution_id,
3352 rcv_transaction_id,
3353 final_match_flag,
3354 'N',
3355 asset_book_type_code,
3356 asset_category_id,
3357 project_id,
3358 task_id,
3359 expenditure_type,
3360 expenditure_item_date,
3361 expenditure_organization_id,
3362 p_pa_quantity,
3363 NULL,
3364 NULL,
3365 NULL,
3366 award_id,
3367 awt_group_id,
3368 reference_1,
3369 reference_2,
3370 receipt_verified_flag,
3371 receipt_required_flag,
3372 receipt_missing_flag,
3373 justification,
3374 expense_group,
3375 start_expense_date,
3376 end_expense_date,
3377 receipt_currency_code,
3378 receipt_conversion_rate,
3379 receipt_currency_amount,
3380 daily_amount,
3381 web_parameter_id,
3382 adjustment_reason,
3383 merchant_document_number,
3384 merchant_name,
3385 merchant_reference,
3386 merchant_tax_reg_number,
3387 merchant_taxpayer_id,
3388 country_of_supply,
3389 credit_card_trx_id,
3390 company_prepaid_invoice_id,
3391 cc_reversal_flag,
3392 SYSDATE,
3393 p_user_id,
3394 p_user_id,
3395 SYSDATE,
3396 p_last_update_login,
3397 program_application_id,
3398 program_id,
3399 program_update_date,
3400 request_id,
3401 'N',
3402 NULL,
3403 attribute_category,
3404 attribute1,
3405 attribute2,
3406 attribute3,
3407 attribute4,
3408 attribute5,
3409 attribute6,
3410 attribute7,
3411 attribute8,
3412 attribute9,
3413 attribute10,
3414 attribute11,
3415 attribute12,
3416 attribute13,
3417 attribute14,
3418 attribute15,
3419 global_attribute_category,
3420 global_attribute1,
3421 global_attribute2,
3422 global_attribute3,
3423 global_attribute4,
3424 global_attribute5,
3425 global_attribute6,
3426 global_attribute7,
3427 global_attribute8,
3428 global_attribute9,
3429 global_attribute10,
3430 global_attribute11,
3431 global_attribute12,
3432 global_attribute13,
3433 global_attribute14,
3434 global_attribute15,
3435 global_attribute16,
3436 global_attribute17,
3437 global_attribute18,
3438 global_attribute19,
3439 global_attribute20,
3440 --ETAX: Invwkb
3441 ship_to_location_id,
3442 primary_intended_use,
3443 product_fisc_classification,
3444 trx_business_category,
3445 product_type,
3446 product_category,
3447 user_defined_fisc_class,
3448 purchasing_category_id,
3449 org_id,
3450 pay_awt_group_id --Bug 9058369
3451 FROM ap_invoice_lines
3452 WHERE invoice_id = p_prepay_invoice_id
3453 AND line_number = p_prepay_line_num;
3454
3455 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
3456 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_PREPAY_PKG.Insert_Prepay_Line(-)');
3457 END IF;
3458
3459 RETURN (TRUE);
3460 EXCEPTION
3461 WHEN OTHERS THEN
3462 IF (SQLCODE <> -20001) THEN
3463 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
3464 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
3465 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',
3466 p_calling_sequence);
3467 FND_MESSAGE.SET_TOKEN('PARAMETERS',
3468 'P_PREPAY_INVOICE_ID = '||P_PREPAY_INVOICE_ID
3469 ||', P_PREPAY_LINE_NUM = '||P_PREPAY_LINE_NUM
3470 ||', P_PREPAY_LINE_NUMBER = '||P_PREPAY_LINE_NUMBER
3471 ||', P_INVOICE_ID = '||P_INVOICE_ID
3472 ||', P_AMOUNT_TO_APPLY = '||P_AMOUNT_TO_APPLY
3473 ||', P_BASE_AMOUNT_TO_APPLY = '||P_BASE_AMOUNT_TO_APPLY
3474 ||', P_GL_DATE = '||P_GL_DATE
3475 ||', P_PERIOD_NAME = '||P_PERIOD_NAME
3476 ||', P_PREPAY_INCLUDED = '||P_PREPAY_INCLUDED
3477 ||', P_QUANTITY_INVOICED = '||P_QUANTITY_INVOICED
3478 ||', P_STAT_AMOUNT = '||P_STAT_AMOUNT
3479 ||', P_PA_QUANTITY = '||P_PA_QUANTITY
3480 ||', P_USER_ID = '||P_USER_ID
3481 ||', P_LAST_UPDATE_LOGIN = '||P_LAST_UPDATE_LOGIN);
3482
3483 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
3484
3485 IF INSTR(l_calling_program,'Apply Prepayment Form') > 0 THEN
3486 APP_EXCEPTION.RAISE_EXCEPTION;
3487 ELSE
3488 p_error_message := FND_MESSAGE.GET;
3489 END IF;
3490
3491 END IF;
3492
3493 l_result := AP_PREPAY_UTILS_PKG.Unlock_Line(
3494 p_prepay_invoice_id,
3495 p_prepay_line_num);
3496 RETURN (FALSE);
3497 END Insert_Prepay_Line;
3498
3499
3500 FUNCTION Insert_Prepay_Dists(
3501 P_prepay_invoice_id IN NUMBER,
3502 P_prepay_line_num IN NUMBER,
3503 P_invoice_id IN NUMBER,
3504 P_batch_id IN NUMBER,
3505 P_line_number IN NUMBER,
3506 P_prepay_dist_info IN OUT NOCOPY AP_PREPAY_PKG.Prepay_Dist_Tab_Type,
3507 P_user_id IN NUMBER,
3508 P_last_update_login IN NUMBER,
3509 P_calling_sequence IN VARCHAR2,
3510 P_error_message OUT NOCOPY VARCHAR2)
3511 RETURN BOOLEAN IS
3512
3513 l_loop_counter BINARY_INTEGER;
3514 l_invoice_distribution_id NUMBER;
3515 l_global_attr_category ap_invoice_distributions_all.global_attribute_category%TYPE;
3516 l_result BOOLEAN;
3517 l_debug_info VARCHAR2(4000); --Changed length from 100 to 4000 (8534097)
3518 l_calling_sequence VARCHAR2(2000);
3519 l_calling_program VARCHAR2(1000);
3520 l_bug varchar2(2000);
3521 l_api_name VARCHAR2(50);
3522 l_invoice_includes_prepay_flag VARCHAR2(1); --Bug5224996
3523
3524 BEGIN
3525
3526 l_api_name := 'Insert_Prepay_Dists';
3527 l_calling_program := p_calling_sequence;
3528
3529 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
3530 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_PREPAY_PKG.Insert_Prepay_Dists(+)');
3531 END IF;
3532
3533 l_debug_info := 'Insert PREPAY Distributions';
3534 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3535 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
3536 END IF;
3537
3538 --Bug5224996 Added following select stmt
3539 SELECT invoice_includes_prepay_flag
3540 INTO l_invoice_includes_prepay_flag
3541 FROM ap_invoice_lines
3542 WHERE invoice_id=p_invoice_id
3543 AND line_number=p_line_number;
3544
3545 FOR l_loop_counter IN nvl(p_prepay_dist_info.first,0) .. nvl(p_prepay_dist_info.last,0) LOOP
3546
3547 SELECT ap_invoice_distributions_s.NEXTVAL
3548 INTO p_prepay_dist_info(l_loop_counter).invoice_distribution_id
3549 FROM sys.dual; -- Check if it's better to use sequence.CURRVAL instead of dual.
3550
3551
3552 l_debug_info := 'Derive the charge_applicable_to_dist_id and related_id for Tax Dists and Tax variances';
3553 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3554 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
3555 END IF;
3556
3557 --Note: Need to derive it only after the 'PREPAY' distributions have been created as
3558 --part of the loop, hence ordered the cursor C_PPAY_DIST_INFO_RECOUP by line_type_lookup_code
3559 --so that the PREPAY dists are created before TAX dists and TAX variances.
3560
3561 IF (p_prepay_dist_info(l_loop_counter).parent_chrg_appl_to_dist_id IS NOT NULL) THEN
3562
3563 IF (p_prepay_dist_info(l_loop_counter).line_type_lookup_code IN ('REC_TAX','NONREC_TAX')) THEN
3564
3565 l_debug_info := 'Derive Charge_Applicable_to_Dist_Id for the Tax distributions';
3566 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3567 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
3568 END IF;
3569
3570 SELECT invoice_distribution_id
3571 INTO p_prepay_dist_info(l_loop_counter).charge_applicable_to_dist_id
3572 FROM ap_invoice_distributions
3573 WHERE invoice_id = p_invoice_id
3574 AND invoice_line_number = p_line_number
3575 AND line_type_lookup_code = 'PREPAY'
3576 and prepay_distribution_id = p_prepay_dist_info(l_loop_counter).parent_chrg_appl_to_dist_id;
3577
3578 ELSIF (p_prepay_dist_info(l_loop_counter).line_type_lookup_code IN ('TERV','TIPV','TRV')) THEN
3579
3580 l_debug_info := 'Derive Related_Id for the Tax Variance distributions';
3581 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3582 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
3583 END IF;
3584
3585
3586 SELECT invoice_distribution_id
3587 INTO p_prepay_dist_info(l_loop_counter).related_id
3588 FROM ap_invoice_distributions
3589 WHERE invoice_id = p_invoice_id
3590 AND invoice_line_number = p_line_number
3591 AND line_type_lookup_code in ('NONREC_TAX','REC_TAX')
3592 AND prepay_distribution_id = p_prepay_dist_info(l_loop_counter).parent_related_id;
3593
3594 END IF;
3595
3596 END IF;
3597
3598 l_debug_info := 'Insert into ap_invoice_distributions';
3599 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3600 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
3601 END IF;
3602
3603 l_debug_info := 'Prepay Distribution Id is '||p_prepay_dist_info(l_loop_counter).prepay_distribution_id;
3604
3605 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3606 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
3607 END IF;
3608
3609
3610
3611 INSERT INTO AP_INVOICE_DISTRIBUTIONS
3612 (ACCOUNTING_DATE,
3613 ACCRUAL_POSTED_FLAG,
3614 ASSETS_ADDITION_FLAG,
3615 ASSETS_TRACKING_FLAG,
3616 CASH_POSTED_FLAG,
3617 DISTRIBUTION_LINE_NUMBER,
3618 DIST_CODE_COMBINATION_ID,
3619 INVOICE_ID,
3620 LAST_UPDATED_BY,
3621 LAST_UPDATE_DATE,
3622 LINE_TYPE_LOOKUP_CODE,
3623 PERIOD_NAME,
3624 SET_OF_BOOKS_ID,
3625 ACCTS_PAY_CODE_COMBINATION_ID,
3626 AMOUNT,
3627 BASE_AMOUNT,
3628 BATCH_ID,
3629 CREATED_BY,
3630 CREATION_DATE,
3631 DESCRIPTION,
3632 FINAL_MATCH_FLAG,
3633 INCOME_TAX_REGION ,
3634 LAST_UPDATE_LOGIN,
3635 MATCH_STATUS_FLAG,
3636 POSTED_FLAG,
3637 PO_DISTRIBUTION_ID,
3638 PROGRAM_APPLICATION_ID,
3639 PROGRAM_ID,
3640 PROGRAM_UPDATE_DATE,
3641 QUANTITY_INVOICED,
3642 REQUEST_ID,
3643 REVERSAL_FLAG,
3644 TYPE_1099,
3645 UNIT_PRICE,
3646 ENCUMBERED_FLAG ,
3647 STAT_AMOUNT,
3648 AMOUNT_TO_POST,
3649 ATTRIBUTE1,
3650 ATTRIBUTE10,
3651 ATTRIBUTE11,
3652 ATTRIBUTE12,
3653 ATTRIBUTE13,
3654 ATTRIBUTE14,
3655 ATTRIBUTE15,
3656 ATTRIBUTE2,
3657 ATTRIBUTE3,
3658 ATTRIBUTE4,
3659 ATTRIBUTE5,
3660 ATTRIBUTE6,
3661 ATTRIBUTE7,
3662 ATTRIBUTE8,
3663 ATTRIBUTE9,
3664 ATTRIBUTE_CATEGORY,
3665 BASE_AMOUNT_TO_POST,
3666 EXPENDITURE_ITEM_DATE,
3667 EXPENDITURE_ORGANIZATION_ID,
3668 EXPENDITURE_TYPE,
3669 PARENT_INVOICE_ID ,
3670 PA_ADDITION_FLAG,
3671 PA_QUANTITY,
3672 POSTED_AMOUNT,
3673 POSTED_BASE_AMOUNT,
3674 PREPAY_AMOUNT_REMAINING,
3675 PROJECT_ID,
3676 TASK_ID ,
3677 -- USSGL_TRANSACTION_CODE, - Bug 4277744
3678 -- USSGL_TRX_CODE_CONTEXT, - Bug 4277744
3679 QUANTITY_VARIANCE ,
3680 BASE_QUANTITY_VARIANCE,
3681 PACKET_ID,
3682 AWT_FLAG,
3683 AWT_GROUP_ID,
3684 AWT_TAX_RATE_ID,
3685 AWT_GROSS_AMOUNT,
3686 AWT_INVOICE_ID,
3687 AWT_ORIGIN_GROUP_ID,
3688 REFERENCE_1,
3689 REFERENCE_2,
3690 AWT_INVOICE_PAYMENT_ID,
3691 GLOBAL_ATTRIBUTE_CATEGORY,
3692 GLOBAL_ATTRIBUTE1 ,
3693 GLOBAL_ATTRIBUTE2 ,
3694 GLOBAL_ATTRIBUTE3 ,
3695 GLOBAL_ATTRIBUTE4 ,
3696 GLOBAL_ATTRIBUTE5 ,
3697 GLOBAL_ATTRIBUTE6 ,
3698 GLOBAL_ATTRIBUTE7 ,
3699 GLOBAL_ATTRIBUTE8 ,
3700 GLOBAL_ATTRIBUTE9 ,
3701 GLOBAL_ATTRIBUTE10,
3702 GLOBAL_ATTRIBUTE11,
3703 GLOBAL_ATTRIBUTE12,
3704 GLOBAL_ATTRIBUTE13,
3705 GLOBAL_ATTRIBUTE14,
3706 GLOBAL_ATTRIBUTE15,
3707 GLOBAL_ATTRIBUTE16,
3708 GLOBAL_ATTRIBUTE17,
3709 GLOBAL_ATTRIBUTE18,
3710 GLOBAL_ATTRIBUTE19,
3711 GLOBAL_ATTRIBUTE20,
3712 RECEIPT_VERIFIED_FLAG,
3713 RECEIPT_REQUIRED_FLAG,
3714 RECEIPT_MISSING_FLAG ,
3715 JUSTIFICATION,
3716 EXPENSE_GROUP,
3717 START_EXPENSE_DATE,
3718 END_EXPENSE_DATE,
3719 RECEIPT_CURRENCY_CODE,
3720 RECEIPT_CONVERSION_RATE,
3721 RECEIPT_CURRENCY_AMOUNT,
3722 DAILY_AMOUNT,
3723 WEB_PARAMETER_ID,
3724 ADJUSTMENT_REASON ,
3725 AWARD_ID,
3726 CREDIT_CARD_TRX_ID,
3727 DIST_MATCH_TYPE,
3728 RCV_TRANSACTION_ID,
3729 INVOICE_DISTRIBUTION_ID ,
3730 PARENT_REVERSAL_ID,
3731 TAX_RECOVERABLE_FLAG,
3732 TAX_CODE_ID,
3733 MERCHANT_DOCUMENT_NUMBER,
3734 MERCHANT_NAME ,
3735 MERCHANT_REFERENCE,
3736 MERCHANT_TAX_REG_NUMBER,
3737 MERCHANT_TAXPAYER_ID,
3738 COUNTRY_OF_SUPPLY,
3739 MATCHED_UOM_LOOKUP_CODE,
3740 GMS_BURDENABLE_RAW_COST,
3741 ACCOUNTING_EVENT_ID,
3742 PREPAY_DISTRIBUTION_ID,
3743 UPGRADE_POSTED_AMT,
3744 UPGRADE_BASE_POSTED_AMT,
3745 INVENTORY_TRANSFER_STATUS,
3746 COMPANY_PREPAID_INVOICE_ID,
3747 CC_REVERSAL_FLAG,
3748 AWT_WITHHELD_AMT,
3749 PRICE_CORRECT_INV_ID,
3750 PRICE_CORRECT_QTY,
3751 PA_CMT_XFACE_FLAG,
3752 CANCELLATION_FLAG,
3753 INVOICE_LINE_NUMBER,
3754 ROUNDING_AMT,
3755 CHARGE_APPLICABLE_TO_DIST_ID ,
3756 CORRECTED_INVOICE_DIST_ID,
3757 CORRECTED_QUANTITY,
3758 RELATED_ID,
3759 JE_BATCH_ID,
3760 CASH_JE_BATCH_ID ,
3761 INVOICE_PRICE_VARIANCE,
3762 BASE_INVOICE_PRICE_VARIANCE,
3763 PRICE_ADJUSTMENT_FLAG,
3764 PRICE_VAR_CODE_COMBINATION_ID,
3765 RATE_VAR_CODE_COMBINATION_ID,
3766 EXCHANGE_RATE_VARIANCE,
3767 AMOUNT_ENCUMBERED ,
3768 BASE_AMOUNT_ENCUMBERED,
3769 QUANTITY_UNENCUMBERED,
3770 EARLIEST_SETTLEMENT_DATE,
3771 OTHER_INVOICE_ID,
3772 LINE_GROUP_NUMBER ,
3773 REQ_DISTRIBUTION_ID,
3774 PROJECT_ACCOUNTING_CONTEXT,
3775 PA_CC_AR_INVOICE_ID,
3776 PA_CC_AR_INVOICE_LINE_NUM,
3777 PA_CC_PROCESSED_CODE ,
3778 ASSET_BOOK_TYPE_CODE ,
3779 ASSET_CATEGORY_ID ,
3780 DISTRIBUTION_CLASS,
3781 FINAL_PAYMENT_ROUNDING,
3782 AMOUNT_AT_PREPAY_XRATE,
3783 AMOUNT_AT_PREPAY_PAY_XRATE,
3784 --ETAX: Invwkb
3785 INTENDED_USE,
3786 --Freight and Special Charges
3787 rcv_charge_addition_flag,
3788 invoice_includes_prepay_flag, --Bug5224996
3789 org_id,
3790 pay_awt_group_id) --Bug 9058369
3791 SELECT
3792 trunc(p_prepay_dist_info(l_loop_counter).PREPAY_ACCOUNTING_DATE), --8532204
3793 'N',
3794 'U',
3795 ASSETS_TRACKING_FLAG,
3796 'N',
3797 p_prepay_dist_info(l_loop_counter).PREPAY_DIST_LINE_NUMBER,
3798 DIST_CODE_COMBINATION_ID,
3799 p_invoice_id,
3800 p_user_id,
3801 SYSDATE,
3802 p_prepay_dist_info(l_loop_counter).LINE_TYPE_LOOKUP_CODE,
3803 p_prepay_dist_info(l_loop_counter).PREPAY_PERIOD_NAME,
3804 SET_OF_BOOKS_ID,
3805 NULL,
3806 (- 1 * p_prepay_dist_info(l_loop_counter).PREPAY_APPLY_AMOUNT),
3807 (-1 * p_prepay_dist_info(l_loop_counter).PREPAY_BASE_AMOUNT),
3808 p_batch_id,
3809 p_user_id,
3810 SYSDATE,
3811 DESCRIPTION,
3812 NULL,
3813 INCOME_TAX_REGION ,
3814 p_last_update_login,
3815 Null,
3816 'N',
3817 PO_DISTRIBUTION_ID,
3818 program_application_id,
3819 program_id,
3820 SYSDATE,
3821 p_prepay_dist_info(l_loop_counter).PREPAY_QUANTITY_INVOICED,
3822 request_id,
3823 'N',
3824 TYPE_1099,
3825 UNIT_PRICE,
3826 'N' ,
3827 p_prepay_dist_info(l_loop_counter).PREPAY_STAT_AMOUNT,
3828 NULL,
3829 ATTRIBUTE1,
3830 ATTRIBUTE10,
3831 ATTRIBUTE11,
3832 ATTRIBUTE12,
3833 ATTRIBUTE13,
3834 ATTRIBUTE14,
3835 ATTRIBUTE15,
3836 ATTRIBUTE2,
3837 ATTRIBUTE3,
3838 ATTRIBUTE4,
3839 ATTRIBUTE5,
3840 ATTRIBUTE6,
3841 ATTRIBUTE7,
3842 ATTRIBUTE8,
3843 ATTRIBUTE9,
3844 ATTRIBUTE_CATEGORY,
3845 NULL,
3846 EXPENDITURE_ITEM_DATE,
3847 EXPENDITURE_ORGANIZATION_ID,
3848 EXPENDITURE_TYPE,
3849 NULL,
3850 --bugfix:4924696
3851 DECODE(pa_addition_flag,'E','E','N'),
3852 p_prepay_dist_info(l_loop_counter).PREPAY_PA_QUANTITY,
3853 NULL,
3854 NULL,
3855 NULL,
3856 PROJECT_ID,
3857 TASK_ID ,
3858 -- USSGL_TRANSACTION_CODE, - Bug 4277744
3859 -- USSGL_TRX_CODE_CONTEXT, - Bug 4277744
3860 NULL,
3861 NULL,
3862 NULL,
3863 NULL,
3864 AWT_GROUP_ID,
3865 AWT_TAX_RATE_ID,
3866 AWT_GROSS_AMOUNT,
3867 AWT_INVOICE_ID,
3868 AWT_ORIGIN_GROUP_ID,
3869 REFERENCE_1,
3870 REFERENCE_2,
3871 AWT_INVOICE_PAYMENT_ID,
3872 GLOBAL_ATTRIBUTE_CATEGORY,
3873 GLOBAL_ATTRIBUTE1 ,
3874 GLOBAL_ATTRIBUTE2 ,
3875 GLOBAL_ATTRIBUTE3 ,
3876 GLOBAL_ATTRIBUTE4 ,
3877 GLOBAL_ATTRIBUTE5 ,
3878 GLOBAL_ATTRIBUTE6 ,
3879 GLOBAL_ATTRIBUTE7 ,
3880 GLOBAL_ATTRIBUTE8 ,
3881 GLOBAL_ATTRIBUTE9 ,
3882 GLOBAL_ATTRIBUTE10,
3883 GLOBAL_ATTRIBUTE11,
3884 GLOBAL_ATTRIBUTE12,
3885 GLOBAL_ATTRIBUTE13,
3886 GLOBAL_ATTRIBUTE14,
3887 GLOBAL_ATTRIBUTE15,
3888 GLOBAL_ATTRIBUTE16,
3889 GLOBAL_ATTRIBUTE17,
3890 GLOBAL_ATTRIBUTE18,
3891 GLOBAL_ATTRIBUTE19,
3892 GLOBAL_ATTRIBUTE20,
3893 RECEIPT_VERIFIED_FLAG,
3894 RECEIPT_REQUIRED_FLAG,
3895 RECEIPT_MISSING_FLAG ,
3896 JUSTIFICATION,
3897 EXPENSE_GROUP,
3898 START_EXPENSE_DATE,
3899 END_EXPENSE_DATE,
3900 RECEIPT_CURRENCY_CODE,
3901 RECEIPT_CONVERSION_RATE,
3902 RECEIPT_CURRENCY_AMOUNT,
3903 DAILY_AMOUNT,
3904 WEB_PARAMETER_ID,
3905 ADJUSTMENT_REASON ,
3906 AWARD_ID,
3907 CREDIT_CARD_TRX_ID,
3908 DIST_MATCH_TYPE,
3909 RCV_TRANSACTION_ID,
3910 p_prepay_dist_info(l_loop_counter).INVOICE_DISTRIBUTION_ID,
3911 NULL,
3912 TAX_RECOVERABLE_FLAG,
3913 TAX_CODE_ID,
3914 MERCHANT_DOCUMENT_NUMBER,
3915 MERCHANT_NAME ,
3916 MERCHANT_REFERENCE,
3917 MERCHANT_TAX_REG_NUMBER,
3918 MERCHANT_TAXPAYER_ID,
3919 COUNTRY_OF_SUPPLY,
3920 MATCHED_UOM_LOOKUP_CODE,
3921 NULL,
3922 NULL,
3923 p_prepay_dist_info(l_loop_counter).PREPAY_DISTRIBUTION_ID,
3924 NULL,
3925 NULL,
3926 'N',
3927 COMPANY_PREPAID_INVOICE_ID,
3928 CC_REVERSAL_FLAG,
3929 NULL,
3930 PRICE_CORRECT_INV_ID,
3931 PRICE_CORRECT_QTY,
3932 PA_CMT_XFACE_FLAG,
3933 'N',
3934 p_line_number,
3935 ROUNDING_AMT,
3936 p_prepay_dist_info(l_loop_counter).charge_applicable_to_dist_id,
3937 NULL,
3938 NULL,
3939 p_prepay_dist_info(l_loop_counter).related_id,
3940 NULL,
3941 NULL,
3942 INVOICE_PRICE_VARIANCE,
3943 BASE_INVOICE_PRICE_VARIANCE,
3944 PRICE_ADJUSTMENT_FLAG,
3945 PRICE_VAR_CODE_COMBINATION_ID,
3946 RATE_VAR_CODE_COMBINATION_ID,
3947 EXCHANGE_RATE_VARIANCE,
3948 AMOUNT_ENCUMBERED ,
3949 BASE_AMOUNT_ENCUMBERED,
3950 QUANTITY_UNENCUMBERED,
3951 EARLIEST_SETTLEMENT_DATE,
3952 NULL,
3953 LINE_GROUP_NUMBER ,
3954 REQ_DISTRIBUTION_ID,
3955 PROJECT_ACCOUNTING_CONTEXT,
3956 NULL,
3957 NULL,
3958 NULL,
3959 ASSET_BOOK_TYPE_CODE ,
3960 ASSET_CATEGORY_ID ,
3961 'PERMANENT',
3962 NULL,
3963 (-1 * p_prepay_dist_info(l_loop_counter).PREPAY_BASE_AMT_PPAY_XRATE),
3964 (-1 * p_prepay_dist_info(l_loop_counter).PREPAY_BASE_AMT_PPAY_PAY_XRATE),
3965 --ETAX: Invwkb
3966 INTENDED_USE,
3967 'N',
3968 l_invoice_includes_prepay_flag, --Bug5224996
3969 ORG_ID,
3970 PAY_AWT_GROUP_ID --Bug 9058369
3971 FROM ap_invoice_distributions
3972 WHERE invoice_distribution_id = p_prepay_dist_info(l_loop_counter).PREPAY_DISTRIBUTION_ID;
3973
3974 -- ===============================================================
3975 -- Call GMS
3976 -- ===============================================================
3977
3978 l_debug_info := 'Call Create Prepay ADL';
3979 GMS_AP_API.Create_Prepay_Adl (
3980 p_prepay_dist_info(l_loop_counter).PREPAY_DISTRIBUTION_ID,
3981 p_invoice_id,
3982 p_prepay_dist_info(l_loop_counter).PREPAY_DIST_LINE_NUMBER,
3983 p_prepay_dist_info(l_loop_counter).INVOICE_DISTRIBUTION_ID);
3984
3985
3986 --------------------------------------------------------------------
3987 -- Execute the Argentine/Colombian prepayment defaulting procedure
3988 --------------------------------------------------------------------
3989
3990 IF (AP_EXTENDED_WITHHOLDING_PKG.Ap_Extended_Withholding_Active) THEN
3991 AP_EXTENDED_WITHHOLDING_PKG.Ap_Ext_Withholding_Prepay (
3992 p_prepay_dist_id => p_prepay_dist_info(l_loop_counter).prepay_distribution_id,
3993 p_invoice_id => p_invoice_id,
3994 p_inv_dist_id => p_prepay_dist_info(l_loop_counter).invoice_distribution_id,
3995 p_user_id => p_user_id,
3996 p_last_update_login => p_last_update_login,
3997 p_calling_sequence => p_calling_sequence );
3998 END IF;
3999
4000 -- ===============================================================
4001 -- Call Global
4002 -- ===============================================================
4003
4004 l_debug_info := 'Update global context code';
4005 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4006 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
4007 END IF;
4008
4009 l_global_attr_category := p_prepay_dist_info(l_loop_counter).PREPAY_global_attr_category;
4010
4011 -- Bug 4014019. Commented out this call as it is invalid in 116 instance.
4012 -- Logged a bug against JG to resolve the issue. This comment will have to
4013 -- be taken out once JG code is fixed.
4014 -- IF ( jg_globe_flex_val.reassign_context_code(
4015 -- l_global_attr_category) <> TRUE) THEN
4016 -- -- > IN global context code in interface table
4017 -- -- > OUT NOCOPY global context code in base table
4018 -- l_calling_sequence := 'reassign_context_code<-'||p_calling_sequence;
4019 -- END IF;
4020 END LOOP;
4021
4022 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
4023 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_PREPAY_PKG.Insert_Prepay_Dists(-)');
4024 END IF;
4025
4026 RETURN (TRUE);
4027 EXCEPTION
4028 WHEN OTHERS THEN
4029 IF (SQLCODE <> -20001) THEN
4030 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
4031 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
4032 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',
4033 p_calling_sequence);
4034 FND_MESSAGE.SET_TOKEN('PARAMETERS',
4035 'P_PREPAY_INVOICE_ID = '||P_PREPAY_INVOICE_ID
4036 ||', P_PREPAY_LINE_NUM = '||P_PREPAY_LINE_NUM
4037 ||', P_INVOICE_ID = '||P_INVOICE_ID
4038 ||', P_BATCH_ID = '||P_BATCH_ID
4039 ||', P_LINE_NUMBER = '||P_LINE_NUMBER
4040 ||', P_USER_ID = '||P_USER_ID
4041 ||', P_LAST_UPDATE_LOGIN = '||P_LAST_UPDATE_LOGIN);
4042
4043 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
4044
4045 IF INSTR(l_calling_program,'Apply Prepayment Form') > 0 THEN
4046 APP_EXCEPTION.RAISE_EXCEPTION;
4047 ELSE
4048 p_error_message := FND_MESSAGE.GET;
4049 END IF;
4050
4051 END IF;
4052
4053 l_result := AP_PREPAY_UTILS_PKG.Unlock_Line(
4054 p_prepay_invoice_id,
4055 p_prepay_line_num);
4056 RETURN (FALSE);
4057
4058 END Insert_Prepay_Dists;
4059
4060
4061 FUNCTION Update_Prepayment(
4062 p_prepay_dist_info IN AP_PREPAY_PKG.Prepay_Dist_Tab_Type,
4063 p_prepay_invoice_id IN NUMBER,
4064 p_prepay_line_num IN NUMBER,
4065 p_invoice_id IN NUMBER,
4066 p_invoice_line_num IN NUMBER,
4067 p_appl_type IN VARCHAR2,
4068 p_calling_mode IN VARCHAR2 DEFAULT 'PREPAYMENT APPLICATION',
4069 p_calling_sequence IN VARCHAR2,
4070 P_error_message OUT NOCOPY VARCHAR2)
4071 RETURN BOOLEAN IS
4072
4073 l_current_calling_sequence VARCHAR2(2000);
4074 l_calling_program VARCHAR2(1000);
4075 l_debug_info VARCHAR2(4000); --Changed length from 100 to 4000 (8534097)
4076 l_result BOOLEAN;
4077 l_api_name VARCHAR2(50);
4078
4079
4080 BEGIN
4081
4082 l_api_name := 'Update_Prepayment';
4083 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
4084 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_PREPAY_PKG.Update_Prepayment(+)');
4085 END IF;
4086
4087 l_current_calling_sequence := 'update_prepayment<-' ||p_calling_sequence;
4088
4089 l_debug_info := 'Update Prepayment Info';
4090 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4091 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
4092 END IF;
4093
4094 -- 7338249
4095 UPDATE ap_invoice_distributions_all dp
4096 SET dp.prepay_amount_remaining = dp.total_dist_amount +
4097 (SELECT SUM(
4098 (NVL(ds.amount, 0) + NVL(ds.prepay_tax_diff_amount, 0)))
4099 FROM ap_invoice_distributions_all ds
4100 WHERE ds.prepay_distribution_id = dp.invoice_distribution_id),
4101 dp.last_updated_by = FND_GLOBAL.user_id, /*Bug10101705: Added who columns*/
4102 dp.last_update_date = SYSDATE,/*Bug10101705: Added who columns*/
4103 dp.last_update_login = FND_GLOBAL.login_id /*Bug10101705: Added who columns*/
4104 WHERE dp.invoice_id = p_prepay_invoice_id
4105 AND (dp.invoice_line_number = p_prepay_line_num
4106 OR EXISTS (SELECT 'Exclusive Prepay Tax Line'
4107 FROM ap_allocation_rule_lines arl
4108 WHERE arl.invoice_id = p_prepay_invoice_id
4109 AND arl.to_invoice_line_number = p_prepay_line_num
4110 AND arl.chrg_invoice_line_number = dp.invoice_line_number));
4111
4112
4113 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
4114 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_PREPAY_PKG.Update_Prepayment(-)');
4115 END IF;
4116
4117 RETURN (TRUE);
4118
4119 EXCEPTION
4120 WHEN OTHERS THEN
4121 IF (SQLCODE <> -20001) THEN
4122 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
4123 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
4124 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',
4125 l_current_calling_sequence);
4126 FND_MESSAGE.SET_TOKEN('PARAMETERS',
4127 'P_PREPAY_INVOICE_ID = '||P_PREPAY_INVOICE_ID
4128 ||', P_PREPAY_LINE_NUM = '||P_PREPAY_LINE_NUM
4129 ||', P_INVOICE_ID = '||P_INVOICE_ID
4130 ||', P_INVOICE_LINE_NUM = '||P_INVOICE_LINE_NUM);
4131
4132 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
4133
4134 IF INSTR(l_calling_program,'Apply Prepayment Form') > 0 THEN
4135 APP_EXCEPTION.RAISE_EXCEPTION;
4136 ELSE
4137 p_error_message := FND_MESSAGE.GET;
4138 END IF;
4139
4140 END IF;
4141
4142 l_result := AP_PREPAY_UTILS_PKG.Unlock_Line(
4143 p_prepay_invoice_id,
4144 p_prepay_line_num);
4145 RETURN (FALSE);
4146
4147 END Update_Prepayment;
4148
4149
4150 FUNCTION Update_PO_Receipt_Info(
4151 p_prepay_dist_info IN AP_PREPAY_PKG.Prepay_Dist_Tab_Type,
4152 p_prepay_invoice_id IN NUMBER,
4153 p_prepay_line_num IN NUMBER,
4154 p_invoice_id IN NUMBER,
4155 p_invoice_line_num IN NUMBER,
4156 p_po_line_location_id IN NUMBER,
4157 p_matched_UOM_lookup_code IN VARCHAR2,
4158 p_appl_type IN VARCHAR2,
4159 p_match_basis IN VARCHAR2,
4160 p_calling_sequence IN VARCHAR2,
4161 p_error_message OUT NOCOPY VARCHAR2)
4162 RETURN BOOLEAN IS
4163
4164 l_current_calling_sequence VARCHAR2(2000);
4165 l_calling_program VARCHAR2(1000);
4166 l_debug_info VARCHAR2(4000); --Changed length from 100 to 4000 (8534097)
4167 l_loop_counter BINARY_INTEGER;
4168
4169 l_rcv_transaction_id NUMBER;
4170 l_po_distribution_id NUMBER;
4171 l_po_line_location_id NUMBER;
4172 l_unit_meas_lookup_code NUMBER;
4173 l_apply_amount NUMBER;
4174 l_quantity_invoiced NUMBER;
4175 l_match_basis VARCHAR2(40);
4176
4177 --Contract Payments: Replacing the PO apis
4178 --with new unified API
4179 l_po_ap_dist_rec PO_AP_DIST_REC_TYPE;
4180 l_po_ap_line_loc_rec PO_AP_LINE_LOC_REC_TYPE;
4181 l_api_name VARCHAR2(50);
4182 l_return_status VARCHAR2(100);
4183 l_msg_data VARCHAR2(4000);
4184 l_shipment_quantity_recouped NUMBER;
4185 l_shipment_amount_recouped NUMBER;
4186
4187 CURSOR C_PO_Receipt_Update IS
4188 SELECT ail.po_line_location_id,
4189 ail.unit_meas_lookup_code,
4190 aid.rcv_transaction_id,
4191 aid.po_distribution_id,
4192 aid.amount,
4193 aid.quantity_invoiced,
4194 plt.matching_basis
4195 FROM ap_invoice_distributions aid,
4196 ap_invoice_lines ail,
4197 po_lines pl, /* Amount Based Matching. PO related tables and conditions */
4198 po_line_locations pll,
4199 po_line_types_b plt, --bug 5056269
4200 po_line_types_tl T
4201 WHERE aid.invoice_id = ail.invoice_id
4202 AND aid.invoice_line_number = ail.line_number
4203 AND aid.invoice_id = p_invoice_id
4204 AND aid.invoice_line_number = p_invoice_line_num
4205 AND NVL(aid.reversal_flag,'N') = 'Y'
4206 AND aid.parent_invoice_id IS NOT NULL
4207 AND ail.po_line_location_id = pll.line_location_id(+)
4208 and pll.po_line_id = pl.po_line_id(+)
4209 and pl.line_type_id = plt.line_type_id(+)
4210 and plt.LINE_TYPE_ID = T.LINE_TYPE_ID
4211 and T.LANGUAGE = userenv('LANG');
4212
4213
4214 l_result BOOLEAN;
4215
4216 BEGIN
4217
4218 l_api_name := 'Update_Po_Receipt_Info';
4219 l_shipment_quantity_recouped := 0;
4220 l_shipment_amount_recouped := 0;
4221
4222 l_calling_program := p_calling_sequence;
4223
4224 -- Update the calling sequence for debugging purposes
4225
4226 l_current_calling_sequence := 'update_po_receipt_info<-'||
4227 p_calling_sequence;
4228 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
4229 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_PREPAY_PKG.Update_PO_Receipt_Info(+)');
4230 END IF;
4231
4232 l_debug_info := 'Create l_po_ap_dist_rec object';
4233 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4234 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
4235 END IF;
4236
4237 l_po_ap_dist_rec := PO_AP_DIST_REC_TYPE.create_object();
4238
4239 l_debug_info := 'Create l_po_ap_line_loc_rec object and populate the data';
4240 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4241 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
4242 END IF;
4243
4244
4245 IF p_appl_type = 'APPLICATION' THEN
4246
4247 FOR l_loop_counter IN nvl(p_prepay_dist_info.FIRST,0) .. nvl(p_prepay_dist_info.LAST,0) LOOP
4248
4249 IF (p_prepay_dist_info(l_loop_counter).ppay_po_distribution_id IS NOT NULL) THEN
4250 --SMYADAM: Need to modify this so that we call the PO apis to update
4251 --rather than the adjust po. For calling_mode of RECOUPMENT we need
4252 --to update the recouped_amounts as oppose to billed columns...
4253
4254 l_po_ap_dist_rec.add_change(
4255 p_po_distribution_id => p_prepay_dist_info(l_loop_counter).ppay_po_distribution_id,
4256 p_uom_code => p_matched_uom_lookup_code,
4257 p_quantity_billed => NULL,
4258 p_amount_billed => NULL,
4259 p_quantity_financed => NULL,
4260 p_amount_financed => NULL,
4261 p_quantity_recouped => (-1) *p_prepay_dist_info(l_loop_counter).prepay_quantity_invoiced ,
4262 p_amount_recouped => p_prepay_dist_info(l_loop_counter).prepay_apply_amount,
4263 p_retainage_withheld_amt => NULL,
4264 p_retainage_released_amt => NULL);
4265
4266 l_shipment_quantity_recouped := l_shipment_quantity_recouped +
4267 nvl((-1)*p_prepay_dist_info(l_loop_counter).prepay_quantity_invoiced,0);
4268 l_shipment_amount_recouped := l_shipment_amount_recouped +
4269 nvl(p_prepay_dist_info(l_loop_counter).prepay_apply_amount,0);
4270
4271
4272 END IF;
4273
4274 IF (p_prepay_dist_info(l_loop_counter).PPAY_RCV_TRANSACTION_ID IS NOT NULL) THEN
4275
4276 RCV_BILL_UPDATING_SV.ap_update_rcv_transactions(
4277 p_prepay_dist_info(l_loop_counter).ppay_rcv_transaction_id,
4278 p_prepay_dist_info(l_loop_counter).prepay_quantity_invoiced,
4279 p_matched_UOM_lookup_code,
4280 (-1) * p_prepay_dist_info(l_loop_counter).prepay_apply_amount,
4281 p_match_basis);
4282
4283 END IF;
4284
4285 END LOOP;
4286
4287 IF (l_shipment_quantity_recouped <> 0 OR l_shipment_amount_recouped <> 0) THEN
4288 l_po_ap_line_loc_rec := PO_AP_LINE_LOC_REC_TYPE.create_object(
4289 p_po_line_location_id => p_po_line_location_id,
4290 p_uom_code => p_matched_uom_lookup_code,
4291 p_quantity_billed => NULL,
4292 p_amount_billed => NULL,
4293 p_quantity_financed => NULL,
4294 p_amount_financed => NULL,
4295 p_quantity_recouped => l_shipment_quantity_recouped,
4296 p_amount_recouped => l_shipment_amount_recouped,
4297 p_retainage_withheld_amt => NULL,
4298 p_retainage_released_amt => NULL,
4299 p_last_update_login => NULL,
4300 p_request_id => NULL
4301 );
4302
4303 END IF;
4304
4305
4306 ELSE
4307
4308 OPEN C_PO_Receipt_Update;
4309
4310 LOOP
4311 FETCH C_PO_Receipt_Update INTO
4312 l_po_line_location_id,
4313 l_unit_meas_lookup_code,
4314 l_rcv_transaction_id,
4315 l_po_distribution_id,
4316 l_apply_amount,
4317 l_quantity_invoiced,
4318 l_match_basis
4319 ;
4320
4321 EXIT WHEN C_PO_Receipt_Update%NOTFOUND;
4322
4323 IF (l_po_distribution_id IS NOT NULL) THEN
4324
4325 l_po_ap_dist_rec.add_change(
4326 p_po_distribution_id => l_po_distribution_id,
4327 p_uom_code => l_unit_meas_lookup_code,
4328 p_quantity_billed => NULL,
4329 p_amount_billed => NULL,
4330 p_quantity_financed => NULL,
4331 p_amount_financed => NULL,
4332 p_quantity_recouped => l_quantity_invoiced ,
4333 p_amount_recouped => l_apply_amount,
4334 p_retainage_withheld_amt => NULL,
4335 p_retainage_released_amt => NULL);
4336
4337 l_shipment_quantity_recouped := l_shipment_quantity_recouped + nvl(l_quantity_invoiced,0);
4338 l_shipment_amount_recouped := l_shipment_amount_recouped + nvl(l_apply_amount,0);
4339
4340 END IF;
4341
4342 IF (l_rcv_transaction_id IS NOT NULL) THEN
4343
4344 RCV_BILL_UPDATING_SV.ap_update_rcv_transactions(
4345 l_rcv_transaction_id,
4346 l_quantity_invoiced,
4347 l_unit_meas_lookup_code,
4348 l_apply_amount,
4349 l_match_basis);
4350
4351 END IF;
4352
4353 END LOOP;
4354
4355 CLOSE C_PO_Receipt_Update;
4356
4357 IF (l_shipment_quantity_recouped <> 0 OR l_shipment_amount_recouped <> 0) THEN
4358
4359 l_po_ap_line_loc_rec := PO_AP_LINE_LOC_REC_TYPE.create_object(
4360 p_po_line_location_id => l_po_line_location_id,
4361 p_uom_code => l_unit_meas_lookup_code,
4362 p_quantity_billed => NULL,
4363 p_amount_billed => NULL,
4364 p_quantity_financed => NULL,
4365 p_amount_financed => NULL,
4366 p_quantity_recouped => l_shipment_quantity_recouped,
4367 p_amount_recouped => l_shipment_amount_recouped,
4368 p_retainage_withheld_amt => NULL,
4369 p_retainage_released_amt => NULL,
4370 p_last_update_login => NULL,
4371 p_request_id => NULL
4372 );
4373
4374 END IF;
4375
4376 END IF; /* IF p_appl_type = ... */
4377
4378 l_debug_info := 'Call the PO_AP_INVOICE_MATCH_GRP to update the Po Distributions and Po Line Locations';
4379 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4380 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
4381 END IF;
4382
4383 PO_AP_INVOICE_MATCH_GRP.Update_Document_Ap_Values(
4384 P_Api_Version => 1.0,
4385 P_Line_Loc_Changes_Rec => l_po_ap_line_loc_rec,
4386 P_Dist_Changes_Rec => l_po_ap_dist_rec,
4387 X_Return_Status => l_return_status,
4388 X_Msg_Data => l_msg_data);
4389
4390 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
4391 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_PREPAY_PKG.Update_PO_Receipt_Info(-)');
4392 END IF;
4393
4394 RETURN (TRUE);
4395 EXCEPTION
4396 WHEN OTHERS THEN
4397 IF (SQLCODE <> -20001) THEN
4398 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
4399 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
4400 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',
4401 l_current_calling_sequence);
4402 FND_MESSAGE.SET_TOKEN('PARAMETERS',
4403 'P_INVOICE_ID = '||P_INVOICE_ID
4404 ||', P_INVOICE_LINE_NUM = '||P_INVOICE_LINE_NUM
4405 ||', P_PO_LINE_LOCATION_ID = '||P_PO_LINE_LOCATION_ID
4406 ||', P_MATCHED_UOM_LOOKUP_CODE = '||P_MATCHED_UOM_LOOKUP_CODE
4407 ||', P_APPL_TYPE = '||P_APPL_TYPE);
4408
4409 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
4410
4411 IF INSTR(l_calling_program,'Apply Prepayment Form') > 0 THEN
4412 APP_EXCEPTION.RAISE_EXCEPTION;
4413 ELSE
4414 p_error_message := FND_MESSAGE.GET;
4415 END IF;
4416
4417 END IF;
4418
4419 l_result := AP_PREPAY_UTILS_PKG.Unlock_Line(
4420 p_prepay_invoice_id,
4421 p_prepay_line_num);
4422
4423 RETURN (FALSE);
4424 END Update_PO_Receipt_Info;
4425
4426
4427 FUNCTION Update_Payment_Schedule(
4428 p_invoice_id IN NUMBER,
4429 p_prepay_invoice_id IN NUMBER,
4430 p_prepay_line_num IN NUMBER,
4431 p_apply_amount IN NUMBER,
4432 p_appl_type IN VARCHAR2,
4433 p_payment_currency_code IN VARCHAR2,
4434 p_user_id IN NUMBER,
4435 p_last_update_login IN NUMBER,
4436 p_calling_sequence IN VARCHAR2,
4437 p_calling_mode IN VARCHAR2 DEFAULT NULL,
4438 p_error_message OUT NOCOPY VARCHAR2)
4439 RETURN BOOLEAN IS
4440
4441 l_debug_info VARCHAR2(4000); --Changed length from 100 to 4000 (8534097)
4442 l_current_calling_sequence VARCHAR2(2000);
4443 l_calling_program VARCHAR2(1000);
4444 l_apply_amount_remaining NUMBER;
4445 l_cursor_payment_num NUMBER;
4446 l_cursor_amount NUMBER;
4447 l_result BOOLEAN;
4448 l_api_name VARCHAR2(50);
4449 l_total_gross_amount NUMBER; --Bug10012482
4450 l_amount_paid NUMBER;
4451
4452 CURSOR Schedules IS
4453 SELECT aps.payment_num,
4454 DECODE(p_appl_type, 'UNAPPLICATION', ((aps.gross_amount / ai.invoice_amount ) *
4455 (ai.invoice_amount - nvl(ap_invoices_pkg.get_amount_withheld(p_invoice_id),0)) - aps.amount_remaining),/*Bug10012482*/
4456 aps.amount_remaining),
4457 ai.invoice_amount /*Bug10012482*/
4458 FROM ap_payment_schedules aps,
4459 ap_invoices_all ai
4460 WHERE ai.invoice_id = aps.invoice_id
4461 AND ai.invoice_id = p_invoice_id
4462 AND (aps.payment_status_flag||'' = 'P'
4463 OR aps.payment_status_flag||'' = DECODE(p_appl_type, 'UNAPPLICATION', 'Y', 'N'))
4464 ORDER BY DECODE(p_appl_type,
4465 'UNAPPLICATION', DECODE(aps.payment_status_flag,'P',1,'Y',2,3),
4466 DECODE(NVL(aps.hold_flag,'N'),'N',1,2)),
4467 DECODE(p_appl_type,
4468 'UNAPPLICATION', aps.due_date,
4469 NULL) DESC,
4470 DECODE(p_appl_type,
4471 'APPLICATION', aps.due_date,
4472 NULL),
4473 DECODE(p_appl_type,
4474 'UNAPPLICATION', DECODE(aps.hold_flag,'N',1,'Y',2,3),
4475 DECODE(NVL(aps.payment_status_flag,'N'),'P',1,'N',2,3));
4476 BEGIN
4477
4478 l_api_name := 'Update_Payment_Schedule';
4479 l_calling_program := p_calling_sequence;
4480 l_current_calling_sequence := 'update_payment_schedule<-'||
4481 p_calling_sequence;
4482
4483 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
4484 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_PREPAY_PKG.Update_Payment_Schedule(+)');
4485 END IF;
4486
4487 IF (p_invoice_id IS NULL OR
4488 p_apply_amount IS NULL OR
4489 p_appl_type IS NULL OR
4490 p_payment_currency_code IS NULL) THEN
4491
4492 RAISE NO_DATA_FOUND;
4493
4494 END IF;
4495
4496 -- l_amount_apply_remaining will keep track of the apply amount that is
4497 -- remaining to be factored into amount remaining.
4498
4499 l_apply_amount_remaining := p_apply_amount;
4500
4501 l_debug_info := 'Open Payment Schedule Cursor';
4502 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
4503 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,l_debug_info);
4504 END IF;
4505
4506 OPEN SCHEDULES;
4507
4508 LOOP
4509
4510 l_debug_info := 'Fetch Schedules into local variables';
4511 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
4512 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,l_debug_info);
4513 END IF;
4514
4515 FETCH SCHEDULES INTO
4516 l_cursor_payment_num, l_cursor_amount,l_total_gross_amount; --Bug10012482:Added l_total_gross_amount
4517 EXIT WHEN SCHEDULES%NOTFOUND;
4518
4519 --Bug 8891266 Changes Start here
4520 IF (p_calling_mode = 'RECOUPMENT') THEN
4521 l_debug_info := 'Update ap_payment_schedule for the recoupments';
4522 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
4523 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,l_debug_info);
4524 END IF;
4525
4526 UPDATE ap_payment_schedules
4527 SET amount_remaining = amount_remaining -
4528 ap_utilities_pkg.ap_round_currency(
4529 l_apply_amount_remaining,
4530 p_payment_currency_code),
4531 payment_status_flag =
4532 DECODE(amount_remaining -
4533 ap_utilities_pkg.ap_round_currency(
4534 l_apply_amount_remaining,
4535 p_payment_currency_code),
4536 0,'Y',
4537 /*Bug10012482:Taking AWT amounts into account*/
4538 ((gross_amount / l_total_gross_amount) * (l_total_gross_amount -
4539 nvl(ap_invoices_pkg.get_amount_withheld(p_invoice_id),0))), 'N',
4540 'P'),
4541 last_update_date = SYSDATE,
4542 last_updated_by = p_user_id,
4543 last_update_login = p_last_update_login
4544 WHERE invoice_id = p_invoice_id
4545 AND payment_num = l_cursor_payment_num;
4546
4547 EXIT;
4548
4549
4550 ELSE
4551 --Bug 8891266 Changes end here
4552
4553 IF ((((l_apply_amount_remaining - l_cursor_amount) <= 0) AND
4554 (p_appl_type = 'APPLICATION')) OR
4555 (((l_apply_amount_remaining + l_cursor_amount) >= 0) AND
4556 (p_appl_type = 'UNAPPLICATION'))) THEN
4557
4558 /*---------------------------------------------------------------------------+
4559 * Case 1 for *
4560 * 1. In apply prepayment(appl_type = 'APPLICATION'), the amount remaining *
4561 * is greater than apply amount remaining. *
4562 * 2. In unapply prepayment, the apply amount (actually unapply amount *
4563 * here) is greater than amount_paid (gross amount-amount remaining). *
4564 * *
4565 * It means that this schedule line has enough amount to apply(unapply) *
4566 * the whole apply_amount. *
4567 * *
4568 * Update the amount remaining for this payment schedule line so that: *
4569 * (amount remaining - apply amount remaining). *
4570 +---------------------------------------------------------------------------*/
4571
4572 l_debug_info := 'Update ap_payment_schedule for the invoice, case 1';
4573 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
4574 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,l_debug_info);
4575 END IF;
4576
4577 UPDATE ap_payment_schedules
4578 SET amount_remaining = (amount_remaining -
4579 ap_utilities_pkg.ap_round_currency(
4580 l_apply_amount_remaining,
4581 p_payment_currency_code)),
4582 payment_status_flag =
4583 DECODE(amount_remaining -
4584 ap_utilities_pkg.ap_round_currency(
4585 l_apply_amount_remaining,
4586 p_payment_currency_code),
4587 0,'Y',
4588 /*Bug10012482:Taking AWT amounts into account*/
4589 ((gross_amount / l_total_gross_amount) * (l_total_gross_amount -
4590 nvl(ap_invoices_pkg.get_amount_withheld(p_invoice_id),0))), 'N',
4591 'P'),
4592 last_update_date = SYSDATE,
4593 last_updated_by = p_user_id,
4594 last_update_login = p_last_update_login
4595 WHERE invoice_id = p_invoice_id
4596 AND payment_num = l_cursor_payment_num;
4597
4598 EXIT; -- No more amount left
4599
4600 ELSE
4601 /*----------------------------------------------------------------------*
4602 *Case 2 for this line don't have enough amount to apply(unapply). *
4603 * *
4604 * Update the amount_remaining to 0 and amount_apply_remaining become *
4605 * (amount_apply - amount_remaining(this line)), then go to next *
4606 * schedule line. *
4607 *----------------------------------------------------------------------*/
4608
4609 l_debug_info := 'Update ap_payment_schedule for the invoice, case 2';
4610 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
4611 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,l_debug_info);
4612 END IF;
4613
4614 UPDATE ap_payment_schedules
4615 SET amount_remaining = DECODE(p_appl_type,
4616 'APPLICATION',0,
4617 /*Bug10012482:Taking AWT amounts into account*/
4618 ((gross_amount / l_total_gross_amount )
4619 * (l_total_gross_amount -
4620 nvl(ap_invoices_pkg.get_amount_withheld(p_invoice_id),0)))),
4621 payment_status_flag = DECODE(p_appl_type,
4622 'APPLICATION','Y',
4623 'N'),
4624 last_update_date = SYSDATE,
4625 last_updated_by = p_user_id,
4626 last_update_login = p_last_update_login
4627 WHERE invoice_id = p_invoice_id
4628 AND payment_num = l_cursor_payment_num;
4629
4630 IF (p_appl_type = 'APPLICATION') THEN
4631 l_apply_amount_remaining := l_apply_amount_remaining - l_cursor_amount;
4632 ELSE
4633 l_apply_amount_remaining := l_apply_amount_remaining + l_cursor_amount;
4634 END IF;
4635
4636 END IF;
4637 END IF;
4638 END LOOP;
4639
4640 l_debug_info := 'Close Schedule Cursor';
4641 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
4642 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,l_debug_info);
4643 END IF;
4644 CLOSE SCHEDULES;
4645
4646 --------------------------------------------------------------------------
4647 -- After update the payment schedules, the payment status flag should
4648 -- be updated according to the payment_status_flag of ap_payment_schedules
4649 -- to reflect the prepayment application
4650 --------------------------------------------------------------------------
4651 l_debug_info := 'Update ap_invoices to reflect the amount applied p_apply_amount, p_invoice_id '||p_apply_amount||','||p_invoice_id;
4652 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
4653 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,l_debug_info);
4654 END IF;
4655 --Bug8414549 : Update the amount_paid column for Recoupment
4656 --IF nvl(p_calling_mode,'X') <> 'RECOUPMENT' THEN
4657
4658 /*Bug 9643901: Moved the unused select to fetch amount paid, from after update to before
4659 and used it to check that amount paid does not go negative in case of unapplication.
4660 Similar check is present for amount remaining update on payment schedule above.*/
4661
4662 SELECT nvl(amount_paid,0)
4663 into l_amount_paid
4664 from ap_invoices
4665 where invoice_id=p_invoice_id;
4666
4667 if sign((l_amount_paid + p_apply_amount)) = -1 and p_apply_amount <> 0 then /* bug 12595394 */
4668 l_amount_paid := 0;
4669 Else
4670 l_amount_paid := l_amount_paid + p_apply_amount;
4671 END if;
4672 /*Bug 9643901 end*/
4673
4674 UPDATE ap_invoices
4675 SET amount_paid = l_amount_paid , /*Bug 9643901: replaced with local variable as set above*/
4676 payment_status_flag =
4677 AP_INVOICES_UTILITY_PKG.get_payment_status(p_invoice_id ),
4678 last_update_date = SYSDATE,
4679 last_updated_by = P_user_id,
4680 last_update_login = p_last_update_login
4681 WHERE invoice_id = p_invoice_id;
4682
4683 --END IF;
4684
4685 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
4686 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_PREPAY_PKG.Update_Payment_Schedule(-)');
4687 END IF;
4688
4689 RETURN (TRUE);
4690
4691 EXCEPTION
4692 WHEN OTHERS THEN
4693
4694 IF (SQLCODE <> -20001 ) THEN
4695 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
4696 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
4697 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',l_current_calling_sequence);
4698 FND_MESSAGE.SET_TOKEN('PARAMETERS','Invoice_id = '||TO_CHAR(p_invoice_id)
4699 ||' APPLY_AMOUNT = '||TO_CHAR(p_apply_amount)
4700 ||' APPLICATION_TYPE = '||p_appl_type
4701 ||' APPLY_AMOUNT_REMAINING = '||
4702 TO_CHAR(l_apply_amount_remaining)
4703 ||' CURSOR_AMOUNT = '||TO_CHAR(l_cursor_amount)
4704 ||' CURSOR_PAYMENT_NUM = '||TO_CHAR(l_cursor_payment_num)
4705 ||' USER_ID = '||TO_CHAR(p_user_id)
4706 ||' LAST_UPDATE_LOGIN = '||TO_CHAR(p_last_update_login)
4707 ||' PAYMENT_CURRENCY_CODE = '||p_payment_currency_code);
4708
4709 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
4710
4711 IF INSTR(l_calling_program,'Apply Prepayment Form') > 0 THEN
4712 APP_EXCEPTION.RAISE_EXCEPTION;
4713 ELSE
4714 p_error_message := FND_MESSAGE.GET;
4715 END IF;
4716
4717 END IF;
4718
4719 l_result := AP_PREPAY_UTILS_PKG.Unlock_Line(
4720 p_prepay_invoice_id,
4721 p_prepay_line_num);
4722
4723 RETURN (FALSE);
4724
4725 END update_payment_schedule;
4726
4727 -- bug 5056104 - removing obsolete functionality
4728
4729 --FUNCTION Update_Rounding_Amounts (
4730 -- p_prepay_invoice_id IN NUMBER,
4731 -- p_prepay_line_num IN NUMBER,
4732 -- p_invoice_id IN NUMBER,
4733 -- p_line_number IN NUMBER,
4734 -- p_final_application IN VARCHAR2,
4735 -- p_calling_sequence IN VARCHAR2 DEFAULT NULL,
4736 -- p_error_message OUT NOCOPY VARCHAR2)
4737 --RETURN BOOLEAN IS
4738 -- bug 5056104 Removed code
4739 --END Update_Rounding_Amounts;
4740
4741
4742 FUNCTION Unapply_Prepay_Line (
4743 P_prepay_invoice_id IN NUMBER,
4744 P_prepay_line_num IN NUMBER,
4745 P_invoice_id IN NUMBER,
4746 P_line_num IN NUMBER,
4747 P_unapply_amount IN NUMBER,
4748 P_gl_date IN DATE,
4749 P_period_name IN VARCHAR2,
4750 P_prepay_included IN VARCHAR2,
4751 P_user_id IN NUMBER,
4752 P_last_update_login IN NUMBER,
4753 P_calling_sequence IN VARCHAR2,
4754 P_error_message OUT NOCOPY VARCHAR2)
4755 RETURN BOOLEAN
4756 IS
4757
4758 -- Prepayment Invoice Related Variables
4759
4760 l_ppay_inv_curr_code ap_invoices_all.invoice_currency_code%TYPE;
4761 l_ppay_inv_pay_curr_code ap_invoices_all.payment_currency_code%TYPE;
4762 l_ppay_inv_pay_cross_rate_date ap_invoices_all.payment_cross_rate_date%TYPE;
4763 l_ppay_inv_pay_cross_rate_type ap_invoices_all.payment_cross_rate_type%TYPE;
4764 l_ppay_apply_amt_in_pay_curr NUMBER;
4765 l_prepay_line_rec ap_invoice_lines_all%ROWTYPE;
4766 l_prepay_dist_info AP_PREPAY_PKG.prepay_dist_tab_type;
4767 l_debug_info VARCHAR2(4000); --Changed length from 100 to 4000 (8534097)
4768 l_current_calling_sequence VARCHAR2(2000);
4769 l_calling_program VARCHAR2(1000);
4770 l_result BOOLEAN;
4771 l_dummy BOOLEAN;
4772 --Bug 5225036 changed the datatype for l_error_code to VARCHAR2 from NUMBER
4773 l_error_code VARCHAR2(4000);
4774 l_token VARCHAR2(4000);
4775
4776 tax_exception EXCEPTION;
4777
4778 CURSOR C_PPAY_INVOICE_INFO(CV_PPay_Invoice_ID IN NUMBER) IS
4779 SELECT invoice_currency_code,
4780 payment_currency_code,
4781 payment_cross_rate_date,
4782 payment_cross_rate_type
4783 FROM AP_Invoices
4784 WHERE invoice_id = CV_PPay_Invoice_ID;
4785
4786 CURSOR C_PPAY_LINE_REC (CV_INVOICE_ID IN NUMBER,
4787 CV_LINE_NUM IN NUMBER)
4788 IS
4789 SELECT *
4790 FROM AP_INVOICE_LINES
4791 WHERE invoice_id = CV_Invoice_ID
4792 AND line_number = CV_line_num;
4793
4794 BEGIN
4795
4796
4797 l_current_calling_sequence := 'Unapply_Prepay_Line <- '||p_calling_sequence;
4798 l_calling_program := p_calling_sequence;
4799
4800 -- =============================================================
4801 -- Step 1: Get Required Information from the Prepayment Invoice
4802 -- and Line.
4803 -- =============================================================
4804
4805 OPEN C_PPAY_INVOICE_INFO (P_PREPAY_INVOICE_ID);
4806
4807 FETCH C_PPAY_INVOICE_INFO INTO
4808 l_ppay_inv_curr_code,
4809 l_ppay_inv_pay_curr_code,
4810 l_ppay_inv_pay_cross_rate_date,
4811 l_ppay_inv_pay_cross_rate_type;
4812
4813 CLOSE C_PPAY_INVOICE_INFO;
4814
4815 OPEN C_PPAY_LINE_REC (P_INVOICE_ID,
4816 P_LINE_NUM);
4817
4818 FETCH C_PPAY_LINE_REC INTO l_prepay_line_rec;
4819
4820 CLOSE C_PPAY_LINE_REC;
4821
4822
4823 --Upgrade the PO Shipment if the invoice line is a matched one and if the
4824 --prepayment application had happened before upgrade to R12.
4825
4826 AP_Matching_Utils_Pkg.AP_Upgrade_Po_Shipment(l_prepay_line_rec.po_line_location_id,
4827 l_current_calling_sequence);
4828
4829
4830 -- Take the accounting_date and period_name passed from
4831 -- the form not the original ones in the Prepay Line.
4832
4833 l_prepay_line_rec.accounting_date := P_gl_date;
4834 l_prepay_line_rec.period_name := P_period_name;
4835
4836 -- ==========================================================
4837 -- Step 1: Get Apply Amount in Payment Currency
4838 -- This information is used only when we update the
4839 -- Payment Schedules.
4840 -- Here we will get this amount and pass it to the
4841 -- Sub Procedure that updates the Payment Schedules.
4842 -- ==========================================================
4843 IF (l_ppay_inv_curr_code <> l_ppay_inv_pay_curr_code) THEN
4844 /*Bug 14592297:Start*/
4845 l_debug_info:='There might not be a defined Currency Exchange Rate available for '||l_ppay_inv_curr_code||' to '||
4846 l_ppay_inv_pay_curr_code|| ' on '||l_ppay_inv_pay_cross_rate_date||' for '||
4847 l_ppay_inv_pay_cross_rate_type||' exchange rate type' ;
4848 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4849 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME,l_debug_info);
4850 END IF;
4851 /*Bug 14592297:End*/
4852
4853 l_ppay_apply_amt_in_pay_curr :=
4854 GL_Currency_API.Convert_Amount
4855 (l_ppay_inv_curr_code,
4856 l_ppay_inv_pay_curr_code,
4857 l_ppay_inv_pay_cross_rate_date,
4858 l_ppay_inv_pay_cross_rate_type,
4859 P_unapply_amount);
4860 /*Bug 14592297:Start*/
4861 l_debug_info:='Successfull call returned from GL_CURRENCY_API';
4862
4863 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4864 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME,l_debug_info);
4865 END IF;
4866 /*Bug 14592297:End*/
4867
4868 ELSE
4869 l_ppay_apply_amt_in_pay_curr := p_unapply_amount;
4870 END IF;
4871
4872 -- ==========================================================
4873 -- Step 2: Discard PREPAY Line
4874 -- We will call the generic DISCARD LINE code that
4875 -- is written by Shelly. We don't have to rewrite this
4876 -- piece just for prepayments. We will still have to
4877 -- modify the code that she had written. I have listed them
4878 -- below:
4879 -- Parameters: We should be able to pass GL DATE and Period
4880 -- Name to it.
4881 -- The code should not do anything related to updating PO.
4882 -- We will address this during the Coding Stage.
4883
4884 -- ==========================================================
4885 l_dummy := AP_INVOICE_LINES_PKG.Discard_Inv_Line (
4886 P_line_rec => l_prepay_line_rec,
4887 P_calling_mode => 'UNAPPLY_PREPAY',
4888 P_inv_cancellable => 'Y',
4889 P_last_updated_by => p_user_id,
4890 P_last_update_login => p_last_update_login,
4891 P_error_code => l_error_code,
4892 P_Token => l_token,
4893 P_calling_sequence => l_current_calling_sequence);
4894
4895 IF l_dummy = FALSE THEN
4896 RETURN (FALSE);
4897 END IF;
4898
4899
4900 -- ===========================================================
4901 -- Step 3: Calculate Tax
4902 -- Call eTax service.
4903 -- ===========================================================
4904 /* Bug 5388370: Tax unapplication will be handled in Discard_Inv_Line.
4905 Seperate call to tax calulation and determine_recovery
4906 is not required.
4907
4908 l_debug_info := 'Call to calculate tax';
4909 IF NOT (ap_etax_pkg.calling_etax(
4910 p_invoice_id => p_invoice_id,
4911 p_line_number => l_prepay_line_rec.line_number,
4912 p_calling_mode => 'UNAPPLY PREPAY',
4913 p_override_status => NULL,
4914 p_line_number_to_delete => NULL,
4915 P_Interface_Invoice_Id => NULL,
4916 p_all_error_messages => 'N',
4917 p_error_code => p_error_message,
4918 p_calling_sequence => l_current_calling_sequence)) THEN
4919
4920 RAISE tax_exception;
4921
4922 END IF;
4923
4924 -- ===========================================================
4925 -- Step 4: Determine_recovery
4926 -- Call eTax service.
4927 -- ===========================================================
4928 l_debug_info := ' Call to Distribute tax';
4929
4930 IF NOT (ap_etax_pkg.calling_etax(
4931 p_invoice_id => p_invoice_id,
4932 p_line_number => NULL,
4933 p_calling_mode => 'DISTRIBUTE',
4934 p_override_status => NULL,
4935 p_line_number_to_delete => NULL,
4936 P_Interface_Invoice_Id => NULL,
4937 p_all_error_messages => 'N',
4938 p_error_code => p_error_message,
4939 p_calling_sequence => l_current_calling_sequence)) THEN
4940
4941 RAISE tax_exception;
4942
4943 END IF;
4944 */
4945 -- ===========================================================
4946 -- Step 6: Update Prepayment
4947 -- ===========================================================
4948 l_dummy := AP_PREPAY_PKG.Update_Prepayment(
4949 l_prepay_dist_info,
4950 p_prepay_invoice_id,
4951 p_prepay_line_num,
4952 p_invoice_id,
4953 p_line_num,
4954 'UNAPPLICATION',
4955 NULL, --p_calling_mode
4956 p_calling_sequence,
4957 p_error_message);
4958
4959 IF l_dummy = FALSE THEN
4960 RETURN (FALSE);
4961 END IF;
4962
4963 -- ===========================================================
4964 -- Step 7: Update PO/RCV information
4965 -- ===========================================================
4966
4967 l_dummy := AP_PREPAY_PKG.Update_PO_Receipt_Info(
4968 l_prepay_dist_info,
4969 p_prepay_invoice_id,
4970 p_prepay_line_num,
4971 p_invoice_id,
4972 p_line_num,
4973 NULL,
4974 NULL,
4975 'UNAPPLICATION',
4976 NULL,
4977 p_calling_sequence,
4978 p_error_message);
4979
4980 IF l_dummy = FALSE THEN
4981 RETURN (FALSE);
4982 END IF;
4983
4984 -- ===========================================================
4985 -- Step 8: Update Payment Schedules
4986 -- ===========================================================
4987
4988 IF NVL(p_prepay_included, 'N') = 'N' THEN
4989 l_dummy := AP_PREPAY_PKG.Update_Payment_Schedule (
4990 p_invoice_id,
4991 p_prepay_invoice_id,
4992 p_prepay_line_num,
4993 l_ppay_apply_amt_in_pay_curr,
4994 'UNAPPLICATION',
4995 l_ppay_inv_pay_curr_code,
4996 p_user_id,
4997 p_last_update_login,
4998 p_calling_sequence,
4999 NULL,
5000 p_error_message);
5001
5002 IF l_dummy = FALSE THEN
5003 RETURN (FALSE) ;
5004 END IF;
5005
5006 END IF;
5007
5008 -- ===========================================================
5009 -- Step 9: If we are here we have done everything we need to
5010 -- Hence we can return TRUE to the calling module.
5011 -- ===========================================================
5012
5013 RETURN (TRUE);
5014
5015 EXCEPTION
5016 WHEN tax_exception THEN
5017 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
5018 FND_MESSAGE.SET_TOKEN('ERROR', p_error_message, TRUE);
5019 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_current_calling_sequence);
5020 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
5021 APP_EXCEPTION.RAISE_EXCEPTION;
5022
5023 WHEN OTHERS THEN
5024 IF (SQLCODE <> -20001) THEN
5025 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
5026 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
5027 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',
5028 l_current_calling_sequence);
5029 FND_MESSAGE.SET_TOKEN('PARAMETERS',
5030 'P_PREPAY_INVOICE_ID = '||P_PREPAY_INVOICE_ID
5031 ||', P_PREPAY_LINE_NUM = '||P_PREPAY_LINE_NUM
5032 ||', P_INVOICE_ID = '||P_INVOICE_ID
5033 ||', P_LINE_NUM = '||P_LINE_NUM
5034 ||', P_GL_DATE = '||P_GL_DATE
5035 ||', P_PERIOD_NAME = '||P_PERIOD_NAME
5036 ||', P_PREPAY_INCLUDED = '||P_PREPAY_INCLUDED
5037 ||', P_UNAPPLY_AMOUNT = '||P_UNAPPLY_AMOUNT
5038 ||', P_USER_ID = '||P_USER_ID
5039 ||', P_LAST_UPDATE_LOGIN = '||P_LAST_UPDATE_LOGIN);
5040
5041 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
5042
5043 IF INSTR(l_calling_program,'Apply Prepayment Form') > 0 THEN
5044 APP_EXCEPTION.RAISE_EXCEPTION;
5045 ELSE
5046 p_error_message := FND_MESSAGE.GET;
5047 END IF;
5048
5049 END IF;
5050
5051 l_result := AP_PREPAY_UTILS_PKG.Unlock_Line(
5052 p_prepay_invoice_id,
5053 p_prepay_line_num);
5054
5055 RETURN (FALSE);
5056
5057 END Unapply_Prepay_Line;
5058
5059
5060 FUNCTION Apply_Prepay_FR_Prepay(
5061 p_invoice_id IN NUMBER,
5062 p_prepay_num IN VARCHAR2,
5063 p_vendor_id IN NUMBER,
5064 p_prepay_apply_amount IN NUMBER,
5065 p_prepay_gl_date IN DATE,
5066 p_prepay_period_name IN VARCHAR2,
5067 p_user_id IN NUMBER,
5068 p_last_update_login IN NUMBER,
5069 p_calling_sequence IN VARCHAR2,
5070 p_error_message OUT NOCOPY VARCHAR2)
5071 RETURN BOOLEAN IS
5072
5073 l_prepay_dist_info AP_PREPAY_PKG.PREPAY_DIST_TAB_TYPE;
5074
5075 l_prepay_id NUMBER;
5076 l_prepay_num VARCHAR2(50);
5077 l_prepay_ln_num NUMBER;
5078 l_prepay_apply_amount NUMBER := p_prepay_apply_amount;
5079 l_apply_amount_remaining NUMBER;
5080 l_cursor_amount_remaining NUMBER;
5081 l_prepay_gl_date DATE := p_prepay_gl_date;
5082 l_prepay_period_name VARCHAR2(15):= p_prepay_period_name;
5083 l_invoice_date AP_INVOICES.INVOICE_DATE%TYPE;
5084 l_result BOOLEAN;
5085 l_debug_info VARCHAR2(4000); --Changed length from 100 to 4000 (8534097)
5086 l_current_calling_sequence VARCHAR2(2000);
5087 l_calling_program VARCHAR2(1000);
5088 l_apply_amount NUMBER;
5089
5090
5091 CURSOR c_get_prepay_id (
5092 cv_prepay_num VARCHAR2,
5093 cv_vendor_id NUMBER) IS
5094 SELECT ai.invoice_id
5095 FROM ap_invoices ai
5096 WHERE ai.vendor_id = cv_vendor_id
5097 AND ai.invoice_num = cv_prepay_num
5098 AND ai.invoice_type_lookup_code = 'PREPAYMENT'
5099 AND ai.payment_status_flag = 'Y'
5100 AND NVL(ai.earliest_settlement_date,sysdate+1) <= SYSDATE;
5101
5102 CURSOR c_case_c_apply(cv_prepay_invoice_id NUMBER) IS
5103 SELECT ai.invoice_num,
5104 ail.line_number,
5105 AP_Prepay_Utils_PKG.get_line_prepay_amt_remaining
5106 (ail.invoice_id,
5107 ail.line_number)
5108 FROM ap_invoices ai,
5109 ap_invoice_lines ail
5110 WHERE ai.invoice_id = cv_prepay_invoice_id
5111 AND ail.invoice_id = ai.invoice_id
5112 AND AP_Prepay_Utils_PKG.get_line_prepay_amt_remaining
5113 (ail.invoice_id,
5114 ail.line_number) > 0
5115 AND ail.line_type_lookup_code = 'ITEM'
5116 AND NVL(ail.discarded_flag,'N') <> 'Y'
5117 ORDER BY ail.line_number;
5118
5119 BEGIN
5120
5121 l_calling_program := p_calling_sequence;
5122 l_current_calling_sequence := 'Apply prepayment and prorate<-'
5123 ||p_calling_sequence;
5124
5125 l_apply_amount_remaining := l_prepay_apply_amount;
5126
5127 OPEN c_get_prepay_id (p_prepay_num, p_vendor_id);
5128 FETCH c_get_prepay_id INTO l_prepay_id;
5129 CLOSE c_get_prepay_id;
5130
5131 OPEN c_case_c_apply(l_prepay_id);
5132 LOOP
5133 FETCH c_case_c_apply INTO
5134 l_prepay_num,
5135 l_prepay_ln_num,
5136 l_cursor_amount_remaining;
5137 EXIT WHEN c_case_c_apply%NOTFOUND or l_apply_amount_remaining = 0;
5138
5139 -----------------------------------------------------------------
5140 -- To apply the specified prepayment to an invoice, we take the
5141 -- first available distribution line to apply, if it is enough
5142 -- to apply, exit from the loop; Otherwise, continue to the take
5143 -- the other distribution lines of the prepayment.
5144 ----------------------------------------------------------------
5145
5146 IF (l_cursor_amount_remaining > l_apply_amount_remaining) THEN
5147 l_apply_amount := l_apply_amount_remaining;
5148 ELSE
5149 l_apply_amount := l_cursor_amount_remaining;
5150 END IF;
5151
5152 l_apply_amount_remaining := l_apply_amount_remaining - l_apply_amount;
5153
5154 l_result := AP_PREPAY_PKG.Apply_Prepay_Line (
5155 l_prepay_id,
5156 l_prepay_ln_num,
5157 l_prepay_dist_info,
5158 'Y',
5159 p_invoice_id,
5160 NULL, --p_invoice_line_number
5161 l_apply_amount,
5162 p_prepay_gl_date,
5163 p_prepay_period_name,
5164 'N',
5165 p_user_id,
5166 p_last_update_login,
5167 l_current_calling_sequence,
5168 'PREPAYMENT APPLICATION',
5169 p_error_message);
5170
5171 -- Bug 14112307
5172 IF ((l_result = FALSE) OR (l_apply_amount_remaining = 0) OR
5173 (l_apply_amount_remaining < l_cursor_amount_remaining) ) THEN
5174 EXIT;
5175 END IF;
5176
5177 /* Bug 14112307
5178 IF (l_apply_amount_remaining <= l_cursor_amount_remaining) THEN
5179 EXIT; -- We are done applying the last amount;
5180 ELSE
5181 IF l_result = FALSE THEN
5182 EXIT;
5183 ELSE
5184 -- No errors. So, application was
5185 -- successful. Therefore, update the apply_amount_remaining
5186 -- to go ahead with the next prepayment application.
5187 l_apply_amount_remaining := l_apply_amount_remaining -
5188 l_cursor_amount_remaining;
5189
5190 END IF; -- IF (l_reject_code IS NOT NULL)
5191 END IF; -- IF (l_apply_amount_remaining <= l_prepay_apply_amount)
5192 */
5193 END LOOP;
5194 CLOSE c_case_c_apply;
5195
5196 RETURN (TRUE);
5197
5198 EXCEPTION
5199 WHEN OTHERS THEN
5200 IF (SQLCODE <> -20001) THEN
5201 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
5202 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
5203 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',
5204 l_current_calling_sequence);
5205 FND_MESSAGE.SET_TOKEN('PARAMETERS',
5206 'P_INVOICE_ID = '||P_INVOICE_ID
5207 ||', P_PREPAY_NUM = '||P_PREPAY_NUM
5208 ||', P_VENDOR_ID = '||P_VENDOR_ID
5209 ||', P_PREPAY_APPLY_AMOUNT = '||P_PREPAY_APPLY_AMOUNT
5210 ||', P_PREPAY_GL_DATE = '||P_PREPAY_GL_DATE
5211 ||', P_PREPAY_PERIOD_NAME = '||P_PREPAY_PERIOD_NAME
5212 ||', P_USER_ID = '||P_USER_ID
5213 ||', P_LAST_UPDATE_LOGIN = '||P_LAST_UPDATE_LOGIN);
5214
5215 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
5216
5217 IF INSTR(l_calling_program,'Apply Prepayment Form') > 0 THEN
5218 APP_EXCEPTION.RAISE_EXCEPTION;
5219 ELSE
5220 p_error_message := FND_MESSAGE.GET;
5221 END IF;
5222
5223 END IF;
5224
5225 RETURN (FALSE);
5226
5227 END Apply_Prepay_FR_Prepay;
5228
5229
5230 END AP_PREPAY_PKG;