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