[Home] [Help]
PACKAGE BODY: APPS.AP_PAYMENT_PUBLIC_PKG
Source
1 PACKAGE BODY AP_PAYMENT_PUBLIC_PKG AS
2 /* $Header: appaypkb.pls 120.2.12010000.4 2009/04/15 08:22:17 njakkula ship $ */
3
4 -- =====================================================================
5 -- P U B L I C O B J E C T S
6 -- =====================================================================
7
8 G_CURRENT_RUNTIME_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
9 G_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
10 G_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
11 G_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
12 G_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
13 G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
14 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
15 G_MODULE_NAME CONSTANT VARCHAR2(35) := 'AP.PLSQL.AP_PAYMENT_PUBLIC_PKG.';
16
17 PROCEDURE Create_Netting_Payment(
18 P_Check_Rec IN
19 AP_CHECKS_ALL%ROWTYPE,
20 P_Invoice_Payment_Info_Tab IN
21 AP_PAYMENT_PUBLIC_PKG.Invoice_Payment_Info_Tab,
22 P_Check_ID OUT NOCOPY NUMBER,
23 P_Curr_Calling_Sequence IN VARCHAR2,
24 p_gl_date IN DATE DEFAULT NULL/* p_gl_date Added for bug#7663371 */) IS
25
26 l_debug_info VARCHAR2(1000);
27 l_curr_calling_sequence VARCHAR2(2000);
28
29 -- Check Record Related Variables
30 l_rowid VARCHAR2(18);
31 l_seq_num_profile VARCHAR2(100);
32 l_dbseqnm FND_DOCUMENT_SEQUENCES.DB_SEQUENCE_NAME%TYPE;
33 l_dbseqid FND_DOCUMENT_SEQUENCES.DOC_SEQUENCE_ID%TYPE;
34 l_set_of_books_id NUMBER(15);
35 l_return_code NUMBER;
36 l_doc_category_code VARCHAR2(100);
37 l_doc_sequence_value NUMBER;
38 l_check_id NUMBER;
39 l_payment_method_code VARCHAR2(100);
40 l_payment_type VARCHAR2(1);
41 l_payment_status VARCHAR2(30);
42 l_base_currency_code VARCHAR2(15);
43
44 l_accounting_event_id NUMBER;
45 l_period_name gl_period_statuses.period_name%TYPE;
46
47 -- Package Related Variables
48 Netting_Exception EXCEPTION;
49 l_gl_date DATE; /* Added for bug#7663371 */
50
51 BEGIN
52
53 ---------------------------------------------------------------------------
54 l_debug_info := 'Begin Create_Netting_Payment';
55 l_curr_calling_sequence := 'AP_PAYMENT_PUBLIC_PKG.Create_Netting_Payment'||
56 ' <-- '||P_Curr_Calling_Sequence;
57
58
59 ---------------------------------------------------------------------------
60 l_debug_info := 'Initialize Variables';
61
62 l_doc_category_code := 'NETTING';
63 l_payment_method_code := 'NETTING';
64 l_payment_Type := 'N';
65 l_payment_status := 'NEGOTIABLE';
66
67 /* Added for bug#7663371 Start */
68 IF p_gl_date IS NULL OR p_gl_date = ''
69 THEN
70 l_gl_date := p_check_rec.check_date;
71 ELSE
72 l_gl_date := p_gl_date;
73 END IF;
74 /* Added for bug#7663371 End */
75
76 ---------------------------------------------------------------------------
77 l_debug_info := 'Get Set of Books/Currency Information';
78 BEGIN
79 SELECT set_of_books_id,
80 base_currency_code
81 INTO l_set_of_books_id,
82 l_base_currency_code
83 FROM ap_system_parameters
84 WHERE org_id = p_check_rec.org_id;
85 EXCEPTION
86 WHEN OTHERS THEN
87 RAISE Netting_Exception;
88 END;
89
90 ---------------------------------------------------------------------------
91 l_debug_info := 'Get Period Name';
92
93 l_period_name := ap_utilities_pkg.Get_current_gl_date
94 (/*p_check_rec.check_date, Commented for bug#7663371 */
95 l_gl_date, /* Added for bug#7663371*/
96 p_check_rec.org_id);
97
98
99 ---------------------------------------------------------------------------
100 l_debug_info := 'Get Document Sequencing Information';
101
102 FND_PROFILE.GET('UNIQUE:SEQ_NUMBERS',l_seq_num_profile);
103 IF (l_seq_num_profile IN ('A','P')) Then
104 BEGIN
105 SELECT SEQ.DB_SEQUENCE_NAME,
106 SEQ.DOC_SEQUENCE_ID
107 INTO l_dbseqnm, l_dbseqid
108 FROM FND_DOCUMENT_SEQUENCES SEQ,
109 FND_DOC_SEQUENCE_ASSIGNMENTS SA
110 WHERE SEQ.DOC_SEQUENCE_ID = SA.DOC_SEQUENCE_ID
111 AND SA.APPLICATION_ID = 200
112 AND SA.CATEGORY_CODE = 'NETTING'
113 AND NVL(SA.METHOD_CODE,'A') = 'A'
114 AND SA.SET_OF_BOOKS_ID = l_set_of_books_id
115 AND nvl(p_check_rec.check_date, sysdate) BETWEEN SA.START_DATE
116 AND nvl(SA.END_DATE, TO_DATE('31/12/4712','DD/MM/YYYY'));
117 EXCEPTION
118 WHEN NO_DATA_FOUND THEN
119 FND_Message.set_name('SQLAP', 'AP_SEQ_DOC_CAT_NO_FOUND');
120 APP_EXCEPTION.RAISE_EXCEPTION;
121 END;
122
123 -------------------------------------------------------------------------
124 l_debug_info := 'Get Doc Sequence Next Val';
125 l_return_code := FND_SEQNUM.GET_SEQ_VAL(
126 200,
127 'NETTING',
128 l_set_of_books_id,
129 'A',
130 nvl(trunc(p_check_rec.check_date), trunc(sysdate)),
131 l_doc_sequence_value,
132 l_dbseqid,
133 'N',
134 'N');
135 IF ((l_doc_sequence_value IS NULL) OR (l_return_code <> 0)) THEN
136 FND_MESSAGE.SET_NAME('SQLAP', 'AP_SEQ_CREATE_ERROR');
137 APP_EXCEPTION.RAISE_EXCEPTION;
138 END IF;
139 END IF;
140
141 ---------------------------------------------------------------------------
142 l_debug_info := 'Get Check ID';
143 BEGIN
144 SELECT ap_checks_s.nextval
145 INTO l_check_id
146 FROM DUAL;
147 EXCEPTION
148 WHEN OTHERS THEN
149 RAISE Netting_Exception;
150 END;
151
152 ---------------------------------------------------------------------------
153 l_debug_info := 'Create AP_CHECKS_ALL Record';
154 AP_AC_TABLE_HANDLER_PKG.Insert_Row(
155 l_rowid,
156 p_check_rec.amount,
157 p_check_rec.ce_bank_acct_use_id,
158 p_check_rec.bank_account_name,
159 p_check_rec.check_date,
160 l_check_id,
161 p_check_rec.check_number,
162 p_check_rec.currency_code,
163 p_check_rec.last_updated_by,
164 p_check_rec.last_update_date,
165 l_payment_type,
166 p_check_rec.address_line1,
167 p_check_rec.address_line2,
168 p_check_rec.address_line3,
169 p_check_rec.checkrun_name,
170 p_check_rec.check_format_id,
171 p_check_rec.check_stock_id,
172 p_check_rec.city,
173 p_check_rec.country,
174 p_check_rec.created_by,
175 p_check_rec.creation_date,
176 p_check_rec.last_update_login,
177 l_payment_status,
178 p_check_rec.vendor_name,
179 p_check_rec.vendor_site_code,
180 p_check_rec.external_bank_account_id,
181 p_check_rec.zip,
182 p_check_rec.bank_account_num,
183 p_check_rec.bank_account_type,
184 p_check_rec.bank_num,
185 p_check_rec.check_voucher_num,
186 p_check_rec.cleared_amount,
187 p_check_rec.cleared_date,
188 l_doc_category_code,
189 l_dbseqid,
190 l_doc_sequence_value,
191 p_check_rec.province,
192 p_check_rec.released_date,
193 p_check_rec.released_by,
194 p_check_rec.state,
195 p_check_rec.stopped_date,
196 p_check_rec.stopped_by,
197 p_check_rec.void_date,
198 p_check_rec.attribute1,
199 p_check_rec.attribute10,
200 p_check_rec.attribute11,
201 p_check_rec.attribute12,
202 p_check_rec.attribute13,
203 p_check_rec.attribute14,
204 p_check_rec.attribute15,
205 p_check_rec.attribute2,
206 p_check_rec.attribute3,
207 p_check_rec.attribute4,
208 p_check_rec.attribute5,
209 p_check_rec.attribute6,
210 p_check_rec.attribute7,
211 p_check_rec.attribute8,
212 p_check_rec.attribute9,
213 p_check_rec.attribute_category,
214 p_check_rec.future_pay_due_date,
215 p_check_rec.treasury_pay_date,
216 p_check_rec.treasury_pay_number,
217 p_check_rec.withholding_status_lookup_code,
218 p_check_rec.reconciliation_batch_id,
219 p_check_rec.cleared_base_amount,
220 p_check_rec.cleared_exchange_rate,
221 p_check_rec.cleared_exchange_date,
222 p_check_rec.cleared_exchange_rate_type,
223 p_check_rec.address_line4,
224 p_check_rec.county,
225 p_check_rec.address_style,
226 p_check_rec.org_id,
227 p_check_rec.vendor_id,
228 p_check_rec.vendor_site_id,
229 p_check_rec.exchange_rate,
230 p_check_rec.exchange_date,
231 p_check_rec.exchange_rate_type,
232 p_check_rec.base_amount,
233 p_check_rec.checkrun_id,
234 p_check_rec.global_attribute_category,
235 p_check_rec.global_attribute1,
236 p_check_rec.global_attribute2,
237 p_check_rec.global_attribute3,
238 p_check_rec.global_attribute4,
239 p_check_rec.global_attribute5,
240 p_check_rec.global_attribute6,
241 p_check_rec.global_attribute7,
242 p_check_rec.global_attribute8,
243 p_check_rec.global_attribute9,
244 p_check_rec.global_attribute10,
245 p_check_rec.global_attribute11,
246 p_check_rec.global_attribute12,
247 p_check_rec.global_attribute13,
248 p_check_rec.global_attribute14,
249 p_check_rec.global_attribute15,
250 p_check_rec.global_attribute16,
251 p_check_rec.global_attribute17,
252 p_check_rec.global_attribute18,
253 p_check_rec.global_attribute19,
254 p_check_rec.global_attribute20,
255 p_check_rec.transfer_priority,
256 p_check_rec.maturity_exchange_rate_type,
257 p_check_rec.maturity_exchange_date,
258 p_check_rec.maturity_exchange_rate,
259 p_check_rec.description,
260 p_check_rec.anticipated_value_date,
261 p_check_rec.actual_value_date,
262 l_payment_method_code,
263 p_check_rec.payment_profile_id,
264 p_check_rec.bank_charge_bearer,
265 p_check_rec.settlement_priority,
266 p_check_rec.payment_document_id,
267 p_check_rec.party_id,
268 p_check_rec.party_site_id,
269 p_check_rec.legal_entity_id,
270 p_check_rec.payment_id,
271 l_curr_calling_sequence);
272
273 ---------------------------------------------------------------------------
274 l_debug_info := 'Create Accounting Events';
275 AP_ACCOUNTING_EVENTS_PKG.CREATE_EVENTS
276 (
277 p_event_type => 'PAYMENT',
278 p_doc_type => l_payment_type,
279 p_doc_id => l_check_id,
280 p_accounting_date => l_gl_date, /* SYSDATE, Changed Sysdate to l_gl_date for bug#7663371 */
281 p_accounting_event_id => l_accounting_event_id,
282 p_checkrun_name => NULL,
283 p_calling_sequence => 'l_curr_calling_sequence'
284 );
285
286 ---------------------------------------------------------------------------
287 l_debug_info := 'Create Payment History for Payment Creation';
288 AP_RECONCILIATION_PKG.insert_payment_history
289 (
290 x_check_id => l_check_id,
291 x_transaction_type => 'PAYMENT CREATED',
292 x_accounting_date => l_gl_date, /* SYSDATE, Changed Sysdate to l_gl_date for bug#7663371 */
293 x_trx_bank_amount => NULL,
294 x_errors_bank_amount => NULL,
295 x_charges_bank_amount => NULL,
296 x_bank_currency_code => NULL,
297 x_bank_to_base_xrate_type => NULL,
298 x_bank_to_base_xrate_date => NULL,
299 x_bank_to_base_xrate => NULL,
300 x_trx_pmt_amount => p_check_rec.amount,
301 x_errors_pmt_amount => NULL,
302 x_charges_pmt_amount => NULL,
303 x_pmt_currency_code => p_check_rec.currency_code,
304 x_pmt_to_base_xrate_type => p_check_rec.exchange_rate_type,
305 x_pmt_to_base_xrate_date => p_check_Rec.exchange_date,
306 x_pmt_to_base_xrate => p_check_Rec.exchange_rate,
307 x_trx_base_amount => p_check_rec.base_amount,
308 x_errors_base_amount => NULL,
309 x_charges_base_amount => NULL,
310 x_matched_flag => NULL,
311 x_rev_pmt_hist_id => NULL,
312 x_org_id => p_check_rec.org_id,
313 x_creation_date => p_check_rec.creation_date,
314 x_created_by => p_check_rec.created_by,
315 x_last_update_date => p_check_rec.last_update_date,
316 x_last_updated_by => p_check_rec.last_updated_by,
317 x_last_update_login => p_check_rec.last_update_login,
318 x_program_update_date => NULL,
319 x_program_application_id => NULL,
320 x_program_id => NULL,
321 x_request_id => NULL,
322 x_calling_sequence => l_curr_calling_sequence,
323 x_accounting_event_id => l_accounting_event_id
324 );
325
326
327 ---------------------------------------------------------------------------
328 l_debug_info := 'Create Invoice Payments';
329 AP_PAYMENT_PUBLIC_PKG.Create_Netting_Inv_Payment
330 (P_Invoice_Payment_Info_Tab => P_Invoice_Payment_Info_Tab,
331 P_check_id => l_check_id,
332 P_payment_type_flag => l_payment_type,
333 P_payment_method => l_payment_method_code,
334 P_ce_bank_acct_use_id => p_check_rec.ce_bank_acct_use_id,
335 P_bank_account_num => p_check_rec.bank_account_num,
336 P_bank_account_type => p_check_rec.bank_account_type,
337 P_bank_num => p_check_rec.bank_num,
338 P_check_date => l_gl_date, /* p_check_rec.check_date, Added l_gl_date for bug#7663371 */
339 P_period_name => l_period_name,
340 P_currency_code => p_check_rec.currency_code,
341 P_base_currency_code => l_base_currency_code,
342 P_checkrun_id => p_check_rec.checkrun_id,
343 P_exchange_rate => p_check_rec.exchange_rate,
344 P_exchange_rate_type => p_check_rec.exchange_rate_type,
345 P_exchange_date => p_check_rec.exchange_date,
346 P_set_of_books_id => l_set_of_books_id,
347 P_last_updated_by => p_check_Rec.last_updated_by,
348 P_last_update_login => p_check_Rec.last_update_login,
349 P_accounting_event_id => l_accounting_event_id,
350 P_org_id => p_check_rec.org_id,
351 P_calling_sequence => l_curr_calling_sequence
352 );
353
354 ---------------------------------------------------------------------------
355 l_debug_info := 'Insert Clearing Records';
356 AP_RECONCILIATION_PKG.Recon_Payment_History
357 (X_CHECKRUN_ID => NULL,
358 X_CHECK_ID => l_check_id,
359 X_ACCOUNTING_DATE => l_gl_date, /* SYSDATE, Changed Sysdate to p_gl_date for bug#7663371 */
360 X_CLEARED_DATE => SYSDATE,
361 X_TRANSACTION_AMOUNT => p_check_rec.amount,
362 X_TRANSACTION_TYPE => 'PAYMENT CLEARING',
363 X_ERROR_AMOUNT => NULL,
364 X_CHARGE_AMOUNT => NULL,
365 X_CURRENCY_CODE => p_check_rec.currency_code,
366 X_EXCHANGE_RATE_TYPE => p_check_rec.exchange_rate_type,
367 X_EXCHANGE_RATE_DATE => p_check_Rec.exchange_date,
368 X_EXCHANGE_RATE => p_check_Rec.exchange_rate,
369 X_MATCHED_FLAG => 'N',
370 X_ACTUAL_VALUE_DATE => NULL,
371 X_LAST_UPDATE_DATE => SYSDATE,
372 X_LAST_UPDATED_BY => p_check_rec.created_by,
373 X_LAST_UPDATE_LOGIN => p_check_rec.last_update_login,
374 X_CREATED_BY => p_check_rec.created_by,
375 X_CREATION_DATE => SYSDATE,
376 X_PROGRAM_UPDATE_DATE => NULL,
377 X_PROGRAM_APPLICATION_ID => NULL,
378 X_PROGRAM_ID => NULL,
379 X_REQUEST_ID => NULL,
380 X_CALLING_SEQUENCE => l_curr_calling_sequence);
381
382
383 ---------------------------------------------------------------------------
384 l_debug_info := 'Assign the OUT Variable';
385 P_Check_ID := l_check_id;
386
387 EXCEPTION
388 WHEN OTHERS THEN
389 IF (SQLCODE <> -20001) THEN
390 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
391 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
392 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',l_curr_calling_sequence);
393 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
394 END IF;
395 APP_EXCEPTION.RAISE_EXCEPTION;
396 END Create_Netting_Payment;
397
398 --
399 PROCEDURE Create_Netting_Inv_Payment
400 (P_Invoice_Payment_Info_Tab IN
401 AP_PAYMENT_PUBLIC_PKG.Invoice_Payment_Info_Tab,
402 P_check_id IN NUMBER,
403 P_payment_type_flag IN VARCHAR2,
404 P_payment_method IN VARCHAR2,
405 P_ce_bank_acct_use_id IN NUMBER,
406 P_bank_account_num IN VARCHAR2,
407 P_bank_account_type IN VARCHAR2,
408 P_bank_num IN VARCHAR2,
409 P_check_date IN DATE,
410 P_period_name IN VARCHAR2,
411 P_currency_code IN VARCHAR2,
412 P_base_currency_code IN VARCHAR2,
413 P_checkrun_id IN NUMBER,
414 P_exchange_rate IN NUMBER,
415 P_exchange_rate_type IN VARCHAR2,
416 P_exchange_date IN DATE,
417 P_set_of_books_id IN NUMBER,
418 P_last_updated_by IN NUMBER,
419 P_last_update_login IN NUMBER,
420 P_accounting_event_id IN NUMBER,
421 P_org_id IN NUMBER,
422 P_calling_sequence IN VARCHAR2
423 )
424 IS
425 l_debug_info VARCHAR2(240);
426 l_curr_calling_sequence VARCHAR2(2000);
427
428 l_invoice_payment_id NUMBER;
429 l_payment_num NUMBER;
430 l_invoice_type VARCHAR2(25);
431 l_invoice_num VARCHAR2(50);
432 l_vendor_id NUMBER;
433 l_vendor_site_id NUMBER;
434 l_exclusive_payment_flag VARCHAR2(1);
435 l_future_pay_posted_flag VARCHAR2(1);
436 l_accts_pay_ccid NUMBER;
437 l_amount NUMBER;
438 l_amount_remaining NUMBER;
439 l_discount_available NUMBER;
440 l_discount_taken NUMBER;
441 l_interest_invoice_id NUMBER;
442 l_interest_invoice_pay_id NUMBER;
443 l_interest_amount NUMBER;
444 l_payment_amount NUMBER;
445 l_due_date DATE;
446 l_interest_invoice_num VARCHAR2(50);
447 l_invoice_description VARCHAR2(240);
448 l_attribute1 VARCHAR2(150);
449 l_attribute2 VARCHAR2(150);
450 l_attribute3 VARCHAR2(150);
451 l_attribute4 VARCHAR2(150);
452 l_attribute5 VARCHAR2(150);
453 l_attribute6 VARCHAR2(150);
454 l_attribute7 VARCHAR2(150);
455 l_attribute8 VARCHAR2(150);
456 l_attribute9 VARCHAR2(150);
457 l_attribute10 VARCHAR2(150);
458 l_attribute11 VARCHAR2(150);
459 l_attribute12 VARCHAR2(150);
460 l_attribute13 VARCHAR2(150);
461 l_attribute14 VARCHAR2(150);
462 l_attribute15 VARCHAR2(150);
463 l_attribute_category VARCHAR2(150);
464
465 BEGIN
466 l_curr_calling_sequence :=
467 'AP_PAYMENT_PUBLIC_PKG.Create_Netting_Inv_Payment<-' ||
468 P_calling_sequence;
469
470 --
471 -- Create Invoice Payments Start
472 --
473 l_debug_info := 'Create Invoice Payments Start';
474
475
476 FOR i IN
477 P_Invoice_Payment_Info_Tab.FIRST ..
478 P_Invoice_Payment_Info_Tab.LAST
479 LOOP
480
481 --
482 -- Get Payment Schedules information
483 --
484 l_debug_info := 'Get Payment Schedules Information';
485
486 SELECT APS.payment_num,
487 AIRP.invoice_type_lookup_code,
488 AIRP.invoice_num,
489 AIRP.vendor_id,
490 AIRP.vendor_site_id,
491 AIRP.exclusive_payment_flag,
492 AIRP.accts_pay_code_combination_id,
493 APS.amount_remaining,
494 APS.attribute1,
495 APS.attribute2,
496 APS.attribute3,
497 APS.attribute4,
498 APS.attribute5,
499 APS.attribute6,
500 APS.attribute7,
501 APS.attribute8,
502 APS.attribute9,
503 APS.attribute10,
504 APS.attribute11,
505 APS.attribute12,
506 APS.attribute13,
507 APS.attribute14,
508 APS.attribute15,
509 APS.attribute_category
510 INTO
511 l_payment_num,
512 l_invoice_type,
513 l_invoice_num,
514 l_vendor_id,
515 l_vendor_site_id,
516 l_exclusive_payment_flag,
517 l_accts_pay_ccid,
518 l_amount_remaining,
519 l_attribute1,
520 l_attribute2,
521 l_attribute3,
522 l_attribute4,
523 l_attribute5,
524 l_attribute6,
525 l_attribute7,
526 l_attribute8,
527 l_attribute9,
528 l_attribute10,
529 l_attribute11,
530 l_attribute12,
531 l_attribute13,
532 l_attribute14,
533 l_attribute15,
534 l_attribute_category
535 FROM ap_invoices AIRP,
536 ap_payment_schedules APS
537 WHERE AIRP.invoice_id = P_Invoice_Payment_Info_Tab(i).invoice_id
538 AND APS.payment_num = P_Invoice_Payment_Info_Tab(i).payment_schedule_num
539 AND APS.checkrun_id = P_checkrun_id
540 AND APS.invoice_id = AIRP.invoice_id;
541
542 l_amount := P_Invoice_Payment_Info_Tab(i).amount_to_pay;
543 l_payment_amount := l_amount;
544
545 --
546 -- Get next invoice_payment_id
547 --
548 l_debug_info := 'Get next invoice_payment_id';
549
550 SELECT ap_invoice_payments_s.nextval
551 INTO l_invoice_payment_id
552 FROM sys.dual;
553
554 l_debug_info := 'Create invoice payment for invoice_id:' ||
555 to_char(P_Invoice_Payment_Info_Tab(i).invoice_id) || ' payment_num:' ||
556 to_char(P_Invoice_Payment_Info_Tab(i).Payment_Schedule_num);
557
558 --Bug# 8305713: Passing P_Invoice_Payment_Info_Tab(i).Discount_Taken
559 AP_PAY_INVOICE_PKG.AP_PAY_INVOICE(
560 P_invoice_id => P_Invoice_Payment_Info_Tab(i).invoice_id,
561 P_check_id => P_check_id,
562 P_payment_num => P_Invoice_Payment_Info_Tab(i).Payment_Schedule_num,
563 P_invoice_payment_id => l_invoice_payment_id,
564 P_old_invoice_payment_id => NULL,
565 P_period_name => P_period_name,
566 P_invoice_type => l_invoice_type,
567 P_accounting_date => P_check_date,
568 P_amount => l_amount,
569 P_discount_taken => P_Invoice_Payment_Info_Tab(i).Discount_Taken,
570 P_discount_lost => NULL,
571 P_invoice_base_amount => NULL,
572 P_payment_base_amount => NULL,
573 P_accrual_posted_flag => 'N',
574 P_cash_posted_flag => 'N',
575 P_posted_flag => 'N',
576 P_set_of_books_id => P_set_of_books_id,
577 P_last_updated_by => P_last_updated_by,
578 P_last_update_login => P_last_update_login,
579 P_currency_code => P_currency_code,
580 P_base_currency_code => P_base_currency_code,
581 P_exchange_rate => P_exchange_rate,
582 P_exchange_rate_type => P_exchange_rate_type,
583 P_exchange_date => P_exchange_date,
584 P_ce_bank_acct_use_id => P_ce_bank_acct_use_id,
585 P_bank_account_num => P_bank_account_num,
586 P_bank_account_type => P_bank_account_type,
587 P_bank_num => P_bank_num,
588 P_future_pay_posted_flag => l_future_pay_posted_flag,
589 P_exclusive_payment_flag => l_exclusive_payment_flag,
590 P_accts_pay_ccid => l_accts_pay_ccid,
591 P_gain_ccid => NULL,
592 P_loss_ccid => NULL,
593 P_future_pay_ccid => NULL,
594 P_asset_ccid => NULL,
595 P_payment_dists_flag => 'N',
596 P_payment_mode => 'PAY',
597 P_replace_flag => 'N',
598 P_attribute1 => l_attribute1,
599 P_attribute2 => l_attribute2,
600 P_attribute3 => l_attribute3,
601 P_attribute4 => l_attribute4,
602 P_attribute5 => l_attribute5,
603 P_attribute6 => l_attribute6,
604 P_attribute7 => l_attribute7,
605 P_attribute8 => l_attribute8,
606 P_attribute9 => l_attribute9,
607 P_attribute10 => l_attribute10,
608 P_attribute11 => l_attribute11,
609 P_attribute12 => l_attribute12,
610 P_attribute13 => l_attribute13,
611 P_attribute14 => l_attribute14,
612 P_attribute15 => l_attribute15,
613 P_attribute_category => l_attribute_category,
614 P_calling_sequence => l_curr_calling_sequence,
615 P_accounting_event_id => P_accounting_event_id,
616 P_org_id => P_org_id);
617
618 END LOOP;
619
620 EXCEPTION
621 WHEN OTHERS THEN
622 IF (SQLCODE <> -20001) THEN
623 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
624 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
625 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',l_curr_calling_sequence);
626 FND_MESSAGE.SET_TOKEN('PARAMETERS',
627 ' P_check_id = ' || P_check_id ||
628 ' P_payment_type_flag = ' || P_payment_type_flag ||
629 ' P_payment_method = ' || P_payment_method ||
630 ' P_bank_account_id = ' || P_ce_bank_acct_use_id ||
631 ' P_bank_account_num = ' || P_bank_account_num ||
632 ' P_bank_account_type = ' || P_bank_account_type ||
633 ' P_bank_num = ' || P_bank_num ||
634 ' P_check_date = ' || P_check_date ||
635 ' P_period_name = ' || P_period_name ||
636 ' P_currency_code = ' || P_currency_code ||
637 ' P_base_currency_code = ' || P_base_currency_code ||
638 ' P_checkrun_id = ' || P_checkrun_id ||
639 ' P_exchange_rate = ' || P_exchange_rate ||
640 ' P_exchange_rate_type = ' || P_exchange_rate_type ||
641 ' P_exchange_date = ' || P_exchange_date ||
642 ' P_set_of_books_id = ' || P_set_of_books_id ||
643 ' P_last_updated_by = ' || P_last_updated_by ||
644 ' P_last_update_login = ' || P_last_update_login
645 );
646 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
647 END IF;
648 APP_EXCEPTION.RAISE_EXCEPTION;
649 END Create_Netting_Inv_Payment;
650
651
652 --===========================================================================
653 -- Get_Discount_For_Schedule: Function that returns the
654 -- discount amount available for given invoice id and date
655 -- Parameters:
656 -- P_Invoice_Id: Invoice on which discount should be calculated
657 -- P_Payment_Num: Payment number used to pay the invoice.
658 -- P_Date: Date on which discount needs to be calculated
659 -- Returns: Discount Amount
660 --===========================================================================
661 FUNCTION Get_Disc_For_Pmt_Schedule(P_Invoice_Id IN NUMBER,
662 P_Payment_Num IN NUMBER,
663 P_Date IN DATE)
664 RETURN NUMBER IS
665
666 CURSOR discount_amt_cursor(c_invoice_id NUMBER,c_payment_num number,c_date DATE) is
667 SELECT
668 DECODE(ai.invoice_type_lookup_code,'PAYMENT REQUEST',
669 0,
670 DECODE(PS.GROSS_AMOUNT,
671 0, 0,
672 DECODE(asi.ALWAYS_TAKE_DISC_FLAG,
673 'Y', NVL(PS.DISCOUNT_AMOUNT_AVAILABLE,0),
674 GREATEST(DECODE(SIGN(c_date
675 - NVL(PS.DISCOUNT_DATE,
676 TO_DATE('01/01/1901',
677 'MM/DD/YYYY'))),
678 1, 0,
679 NVL(ABS(PS.DISCOUNT_AMOUNT_AVAILABLE),0)),
680 DECODE(SIGN(c_date
681 - NVL(PS.SECOND_DISCOUNT_DATE,
682 TO_DATE('01/01/1901',
683 'MM/DD/YYYY'))),
684 1, 0,
685 NVL(ABS(PS.SECOND_DISC_AMT_AVAILABLE),0)),
686 DECODE(SIGN(c_date
687 - NVL(PS.THIRD_DISCOUNT_DATE,
688 TO_DATE('01/01/1901',
689 'MM/DD/YYYY'))),
690 1, 0,
691 NVL(ABS(PS.THIRD_DISC_AMT_AVAILABLE),0)),
692 0) * DECODE(SIGN(ps.gross_amount),-1,-1,1))
693 * (PS.AMOUNT_REMAINING / DECODE(PS.GROSS_AMOUNT,
694 0, 1,
695 PS.GROSS_AMOUNT))))
696 FROM ap_payment_schedules_all PS,
697 ap_invoices_all ai,
698 ap_supplier_sites_all asi
699 WHERE ai.invoice_id = ps.invoice_id
700 AND ai.vendor_id = asi.vendor_id
701 AND ai.vendor_site_id = asi.vendor_site_id
702 AND ai.invoice_id = c_invoice_id
703 AND ps.payment_num = c_payment_num;
704
705 l_discount_amount ap_payment_schedules_all.discount_amount_available%type;
706 Netting_Exception EXCEPTION;
707 l_debug_info FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
708 DBG_Loc VARCHAR2(50) := 'Get_Discount_For_Payment_Schedule';
709
710 BEGIN
711 l_debug_info := 'Begin: Invoice id: '||P_Invoice_Id||' Payment Num: '||P_Payment_Num
712 || 'P_Date:' || P_Date;
713 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
714 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,l_debug_info);
715 END IF;
716
717 OPEN discount_amt_cursor(P_Invoice_Id,P_Payment_Num,P_Date);
718 FETCH discount_amt_cursor INTO l_discount_amount;
719 CLOSE discount_amt_cursor;
720
721 l_debug_info := 'l_discount_amount: '||l_discount_amount;
722 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
723 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,l_debug_info);
724 END IF;
725
726 RETURN nvl(l_discount_amount,0);
727
728 EXCEPTION
729 WHEN OTHERS THEN
730 l_debug_info := 'Error: '||SQLCODE|| '-'||SQLERRM;
731 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
732 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,l_debug_info);
733 END IF;
734 RAISE Netting_Exception;
735 END Get_Disc_For_Pmt_Schedule;
736
737 --===========================================================================
738 -- Get_Disc_On_Netted_Amt: Function that returns the
739 -- discount amount taken on the netted amount
740 -- Parameters:
741 -- P_Invoice_Id: Invoice on which discount should be calculated
742 -- P_Payment_Num: Payment number used to pay the invoice.
743 -- P_Date: Date on which discount needs to be calculated
744 -- P_Netted_Amt: Nettend amount on which discount taken should be calculated
745 -- Returns: Discount Amount taken on netted amount
746
747 --Following will be the algorithm to be followed
748 -- a. Determine/Calculate Remaining amount, discount,
749 -- total netted amount for the given schedule.
750 -- b. From the step a values, determine discount for one unit of netted amount.
751 -- c. Calculate discount for the netted amount passed as input.
752 --===========================================================================
753
754 FUNCTION Get_Disc_For_Netted_Amt(P_Invoice_Id IN NUMBER,
755 P_Payment_Num IN NUMBER,
756 P_Date IN DATE,
757 P_Netted_Amt IN NUMBER)
758 RETURN NUMBER IS
759 l_amount_remaining ap_payment_schedules_all.amount_remaining%type;
760 l_discount_amount ap_payment_schedules_all.discount_amount_available%type;
761 l_discount_on_netted_amt ap_payment_schedules_all.discount_amount_available%type;
762 l_inv_curr ap_invoices_all.invoice_currency_code%type;
763 l_debug_info FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
764 DBG_Loc VARCHAR2(50) := 'Get_Discount_For_Netted_Amt';
765 Netting_Exception EXCEPTION;
766 BEGIN
767 l_debug_info := 'Begin: Invoice id: '||P_Invoice_Id||' Payment Num: '||P_Payment_Num ;
768 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
769 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,l_debug_info);
770 END IF;
771
772 l_debug_info := 'P_Date '||P_Date||' P_Netted_Amt: '||P_Netted_Amt;
773 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
774 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,l_debug_info);
775 END IF;
776
777 --Get Amount Remaining to be paid for the given Invoice and Payment Number
778 SELECT amount_remaining
779 INTO l_amount_remaining
780 FROM ap_payment_schedules_all
781 WHERE invoice_id = P_Invoice_Id
782 AND payment_num = P_Payment_Num;
783
784 --Get the Discount Amount available on the payment
785 l_discount_amount := AP_PAYMENT_PUBLIC_PKG.Get_Disc_For_Pmt_Schedule(P_Invoice_Id,P_Payment_Num,P_Date);
786
787 --Get the Discount on Netted Amount
788 l_discount_on_netted_amt := (l_discount_amount / (l_amount_remaining-l_discount_amount))*p_netted_amt;
789
790 l_debug_info := 'Amount Remaining: '||l_amount_remaining || 'l_discount_amount:' ||l_discount_amount
791 || 'l_discount_on_netted_amt:'||l_discount_on_netted_amt;
792 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
793 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,l_debug_info);
794 END IF;
795
796 SELECT invoice_currency_code
797 INTO l_inv_curr
798 FROM ap_invoices_all
799 WHERE invoice_id = P_Invoice_Id;
800
801 l_discount_on_netted_amt := AP_UTILITIES_PKG.Ap_Round_Currency(l_discount_on_netted_amt,l_inv_curr);
802
803 l_debug_info := 'After rounding: '||l_discount_on_netted_amt;
804 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
805 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,l_debug_info);
806 END IF;
807
808
809 RETURN nvl(l_discount_on_netted_amt,0);
810
811 EXCEPTION
812 WHEN OTHERS THEN
813 l_debug_info := 'Error: '||SQLCODE|| '-'||SQLERRM;
814 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
815 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,l_debug_info);
816 END IF;
817 RAISE Netting_Exception;
818 END Get_Disc_For_Netted_Amt;
819
820
821 END AP_PAYMENT_PUBLIC_PKG;