[Home] [Help]
PACKAGE BODY: APPS.IBY_FNDCPT_VLD_PUB
Source
1 PACKAGE BODY IBY_FNDCPT_VLD_PUB AS
2 /* $Header: ibypfcvb.pls 120.14.12020000.6 2013/03/25 20:13:12 gmamidip ship $ */
3
4 /* ======================================================================*
5 | Global Data Types |
6 * ======================================================================*/
7 G_CURRENT_RUNTIME_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
8 G_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
9 G_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
10 G_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
11 G_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
12 G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
13 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
14
15 G_DEBUG_MODULE CONSTANT VARCHAR2(100) := 'iby.plsql.IBY_FNDCPT_VLD_PUB';
16
17 -- Package global constants
18 PG_DEBUG VARCHAR2(1) := nvl(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
19
20
21 --------------- The following are internal procedures ---------------
22
23 /*--------------------------------------------------------------------
24 | NAME:
25 | print_debuginfo
26 |
27 | PURPOSE:
28 | This procedure prints the debug message to the concurrent manager
29 | log file.
30 |
31 | PARAMETERS:
32 | IN
33 | p_debug_text - The debug message to be printed
34 |
35 | OUT
36 |
37 |
38 | RETURNS:
39 |
40 | NOTES:
41 |
42 *---------------------------------------------------------------------*/
43 PROCEDURE print_debuginfo(
44 p_module IN VARCHAR2,
45 p_debug_text IN VARCHAR2
46 )
47 IS
48 BEGIN
49
50 IBY_TRANSACTIONCC_PKG.print_debuginfo(p_module, p_debug_text);
51
52 END print_debuginfo;
53
54
55
56 PROCEDURE validate_zip (
57 P_POSTAL_CODE IN VARCHAR2,
58 P_COUNTRY_CODE IN VARCHAR2,
59 X_STATUS OUT NOCOPY VARCHAR2
60 ) IS
61 l_zip_length NUMBER;
62 l_zip VARCHAR2(80);
63 BEGIN
64 X_STATUS := 'Y';
65
66 if P_COUNTRY_CODE = 'US' then
67 l_zip_length := length(P_POSTAL_CODE);
68
69 if l_zip_length = 5 or l_zip_length = 9 or (l_zip_length = 10 and substr(P_POSTAL_CODE,6,1) = '-') then
70 if l_zip_length = 10 then
71 l_zip := substr(P_POSTAL_CODE,1,5)||substr(P_POSTAL_CODE,7,4);
72 else
73 l_zip := P_POSTAL_CODE;
74 end if;
75
76 if translate(trim(l_zip),'0123456789',' ') <> rpad(' ',length(trim(l_zip)), ' ') THEN
77 X_STATUS := 'N';
78 end if;
79 else
80 X_STATUS := 'N';
81 end if; -- if l_zip_length is 5, 9 or 10 etc.
82 end if;
83
84 END; -- procedure validate_zip
85
86 --------------- The following are PUBLIC procedures ---------------
87
88
89 -- Validate Citibank credit card batch
90 PROCEDURE Validate_Citibank_Batch (
91 p_api_version IN NUMBER,
92 p_init_msg_list IN VARCHAR2 default FND_API.G_FALSE,
93 P_MBATCH_ID IN NUMBER,
94 x_return_status OUT NOCOPY VARCHAR2,
95 x_msg_count OUT NOCOPY NUMBER,
96 x_msg_data OUT NOCOPY VARCHAR2
97 ) IS
98 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Citibank_Batch';
99 l_api_version CONSTANT NUMBER := 1.0;
100
101 l_batchid_size NUMBER;
102
103 l_validzip VARCHAR2(1);
104 l_cust_trx_id NUMBER;
105 l_ar_return_status VARCHAR2(1);
106 l_ar_msg_count NUMBER;
107 l_ar_msg_data VARCHAR2(2000);
108 l_ship_to_zip VARCHAR2(80);
109 l_ship_to_country VARCHAR2(10);
110
111 cursor level2_trxn is
112 select ts.instrtype,
113 tc.card_data_level,
114 ts.tangibleid,
115 tc.instr_owner_postalcode,
116 tc.instr_owner_country,
117 tc.shipfromzip,
118 tc.shiptozip
119 from iby_batches_all b,
120 iby_trxn_summaries_all ts,
121 iby_trxn_core tc
122 where b.mbatchid = P_MBATCH_ID
123 and b.mbatchid = ts.mbatchid
124 and ts.trxnmid = tc.trxnmid;
125
126 l_trxn_rec level2_trxn%ROWTYPE;
127
128 BEGIN
129 -- SAVEPOINT Validate_Paymentech_Batch;
130
131 -- Standard call to check for call compatibility
132 if NOT FND_API.Compatible_API_Call( l_api_version,
133 p_api_version,
134 l_api_name,
135 G_PKG_NAME ) then
136 raise FND_API.G_EXC_UNEXPECTED_ERROR;
137 end if;
138
139 if FND_API.To_Boolean(p_init_msg_list) then
140 FND_MSG_PUB.initialize;
141 end if;
142
143 x_return_status := FND_API.G_RET_STS_SUCCESS;
144
145 if PG_DEBUG in ('Y', 'C') then
146 null; -- iby.debug('some info');
147 end if;
148
149 -- Validate BATCH NAME
150 select length(batchid)
151 into l_batchid_size
152 from iby_batches_all
153 where mbatchid = P_MBATCH_ID;
154
155 if l_batchid_size > 8 then
156 x_return_status := FND_API.G_RET_STS_ERROR;
157 FND_MESSAGE.set_name('IBY','IBY_VALID_IS_INCORRECT');
158 FND_MESSAGE.set_token('ERR_OBJECT', 'Batch name');
159 FND_MSG_PUB.add;
160 FND_MSG_PUB.Count_And_Get('T',x_msg_count,x_msg_data);
161 return;
162 end if;
163
164 -- Validate POSTAL CODE's
165 open level2_trxn;
166 loop
167 fetch level2_trxn into l_trxn_rec;
168 exit when level2_trxn%NOTFOUND;
169
170 if l_trxn_rec.instrtype = 'CREDITCARD' then
171 -- Billing postal code
172 validate_zip(l_trxn_rec.instr_owner_postalcode,
173 l_trxn_rec.instr_owner_country,
174 l_validzip );
175
176 if l_validzip = 'N' then
177 x_return_status := FND_API.G_RET_STS_ERROR;
178 FND_MESSAGE.set_name('IBY','IBY_VALID_IS_INCORRECT');
179 FND_MESSAGE.set_token('ERR_OBJECT', 'Postal code');
180 FND_MSG_PUB.add;
181 FND_MSG_PUB.Count_And_Get('T',x_msg_count,x_msg_data);
182 return;
183 end if;
184
185 -- PENDING: MERCHANT_POSTAL_CODE
186
187 elsif l_trxn_rec.instrtype = 'PURCHASECARD' then
188 -- Billing postal code
189 validate_zip(l_trxn_rec.instr_owner_postalcode,
190 l_trxn_rec.instr_owner_country,
191 l_validzip );
192
193 if l_validzip = 'N' then
194 x_return_status := FND_API.G_RET_STS_ERROR;
195 FND_MESSAGE.set_name('IBY','IBY_VALID_IS_INCORRECT');
196 FND_MESSAGE.set_token('ERR_OBJECT', 'Postal code');
197 FND_MSG_PUB.add;
198 FND_MSG_PUB.Count_And_Get('T',x_msg_count,x_msg_data);
199 return;
200 end if;
201
202 -- PENDING: MERCHANT_POSTAL_CODE
203
204 /*
205 -- Shipping origin postal code: invoiceHeader.sellerPartner.address.postalCode
206 validate_zip(l_trxn_rec.shipfromzip,
207 l_trxn_rec.shipfromcountry, -- this does NOT exist now
208 l_validzip );
209
210 if l_validzip = 'N' then
211 x_return_status := FND_API.G_RET_STS_ERROR;
212 FND_MESSAGE.set_name('IBY','IBY_VALID_IS_INCORRECT');
213 FND_MESSAGE.set_token('ERR_OBJECT', 'Postal code');
214 FND_MSG_PUB.add;
215 FND_MSG_PUB.Count_And_Get('T',x_msg_count,x_msg_data);
216 return;
217 end if;
218
219 -- Shipping destination postal code: invoiceHeader.buyerPartner.address.postalCode
220 validate_zip(l_trxn_rec.shiptozip,
221 l_trxn_rec.shiptocountry, -- this does NOT exist now
222 l_validzip );
223
224 if l_validzip = 'N' then
225 x_return_status := FND_API.G_RET_STS_ERROR;
226 FND_MESSAGE.set_name('IBY','IBY_VALID_IS_INCORRECT');
227 FND_MESSAGE.set_token('ERR_OBJECT', 'Postal code');
228 FND_MSG_PUB.add;
229 FND_MSG_PUB.Count_And_Get('T',x_msg_count,x_msg_data);
230 return;
231 end if;
232 */
233
234 else
235 null;
236 end if; -- instrtype is CREDITCARD, PURCHASECARD, etc.
237
238 end loop;
239 close level2_trxn;
240
241 FND_MSG_PUB.Count_And_Get('T',x_msg_count,x_msg_data);
242
243 END; -- procedure Validate_Citibank_Batch
244
245
246 -- Validate FDCNorth credit card batch
247 PROCEDURE Validate_FDCNorth_Batch (
248 p_api_version IN NUMBER,
249 p_init_msg_list IN VARCHAR2 default FND_API.G_FALSE,
250 P_MBATCH_ID IN NUMBER,
251 x_return_status OUT NOCOPY VARCHAR2,
252 x_msg_count OUT NOCOPY NUMBER,
253 x_msg_data OUT NOCOPY VARCHAR2
254 ) IS
255 l_api_name CONSTANT VARCHAR2(30) := 'Validate_FDCNorth_Batch';
256 l_api_version CONSTANT NUMBER := 1.0;
257
258 l_payee_id NUMBER;
259 l_batch_close_date DATE;
260 l_batch_count NUMBER;
261 l_doc_line_count NUMBER;
262 l_security_code VARCHAR2(500);
263
264 l_validzip VARCHAR2(1);
265 l_cust_trx_id NUMBER;
266 l_ar_return_status VARCHAR2(1);
267 l_ar_msg_count NUMBER;
268 l_ar_msg_data VARCHAR2(2000);
269 l_ship_to_zip VARCHAR2(80);
270 l_ship_to_country VARCHAR2(10);
271 l_bepkey iby_bepkeys.key%TYPE;
272
273 cursor level2_trxn is
274 select ts.instrtype,
275 tc.card_data_level,
276 ts.tangibleid,
277 ts.reqtype,
278 tc.instr_owner_postalcode,
279 tc.instr_owner_country,
280 tc.shipfromzip,
281 tc.shiptozip
282 from iby_batches_all b,
283 iby_trxn_summaries_all ts,
284 iby_trxn_core tc
285 where b.mbatchid = P_MBATCH_ID
286 and b.mbatchid = ts.mbatchid
287 and ts.trxnmid = tc.trxnmid;
288
289 l_trxn_rec level2_trxn%ROWTYPE;
290
291 BEGIN
292 -- SAVEPOINT Validate_Paymentech_Batch;
293
294 -- Standard call to check for call compatibility
295 if NOT FND_API.Compatible_API_Call( l_api_version,
296 p_api_version,
297 l_api_name,
298 G_PKG_NAME ) then
299 raise FND_API.G_EXC_UNEXPECTED_ERROR;
300 end if;
301
302 if FND_API.To_Boolean(p_init_msg_list) then
303 FND_MSG_PUB.initialize;
304 end if;
305
306 x_return_status := FND_API.G_RET_STS_SUCCESS;
307
308 if PG_DEBUG in ('Y', 'C') then
309 null; -- iby.debug('some info');
310 end if;
311
312 /*
313 * Fix for bug 5717285:
314 *
315 * FDC North has a limit on batches per day
316 * submitted per security code (this limit
317 * is not per payee itself, but per security code).
318 *
319 * If a company has two payees A and B, and these
320 * two payess share the same security code X,
321 * then the first batch sent by payee A will have
322 * submission sequence id of 1, the first batch
323 * submitted by payee B should have submission
324 * sequence id of 2 (because the security code is
325 * the same).
326 */
327
328 -- Validate BATCH COUNT PER SECURITY CODE
329 SELECT
330 mpayeeid,
331 TRUNC(nvl(batchclosedate, SYSDATE))
332 INTO
333 l_payee_id,
334 l_batch_close_date
335 FROM
336 iby_batches_all
337 WHERE
338 mbatchid = P_MBATCH_ID
339 ;
340
341 -- Bug# 12656074
342 -- Fetching the bep key
343 SELECT
344 bepkey
345 INTO
346 l_bepkey
347 FROM
348 iby_trxn_summaries_all
349 WHERE mbatchid = P_MBATCH_ID
350 AND rownum=1;
351
352 /*
353 * Find security code for payee
354 */
355 -- Bug# 12656074
356 -- Adding the additional filter condition
357 -- based on the bep key
358 SELECT
359 val.account_option_value
360 INTO
361 l_security_code
362 FROM
363 IBY_BEP_ACCT_OPT_VALS val,
364 IBY_BEPKEYS key,
365 IBY_PAYEE payee
366 WHERE
367 val.bep_account_id = key.bep_account_id
368 AND payee.payeeid = key.ownerid
369 AND val.account_option_code = 'SEC_CODE'
370 AND payee.mpayeeid = l_payee_id
371 AND key.key = l_bepkey
372 ;
373 /* this sql statement has been replaced by the one below */
374 --select count(*)
375 -- into l_batch_count
376 -- from iby_batches_all
377 -- where mpayeeid = l_payee_id
378 -- and trunc(batchclosedate) = l_batch_close_date;
379
380 /*
381 * Select batch count based on the security code
382 * linked to the payee account (not by the
383 * payee id itself).
384 */
385 --Bug# 13977390
386 -- Added another filter condition with IBY_BEPKEYS
387 SELECT
388 COUNT(*)
389 INTO
390 l_batch_count
391 FROM
392 IBY_BEP_ACCT_OPT_VALS val,
393 IBY_BEPKEYS bepkey,
394 IBY_PAYEE payee,
395 IBY_BATCHES_ALL batch
396 WHERE
397 val.account_option_code = 'SEC_CODE'
398 AND
399 val.account_option_value = l_security_code
400 AND
401 val.bep_account_id = bepkey.bep_account_id
402 AND
403 bepkey.key = l_bepkey
404 AND
405 payee.payeeid = bepkey.ownerid
406 AND
407 payee.mpayeeid = l_payee_id
408 AND
409 TRUNC(batchclosedate) = l_batch_close_date;
410
411 if l_batch_count > 9 then
412 x_return_status := FND_API.G_RET_STS_ERROR;
413 FND_MESSAGE.set_name('IBY','IBY_VALID_IS_INCORRECT');
414 FND_MESSAGE.set_token('ERR_OBJECT', 'Batch count per merchant security code');
415 FND_MSG_PUB.add;
416 FND_MSG_PUB.Count_And_Get('T',x_msg_count,x_msg_data);
417 return;
418 end if;
419
420 -- Validate TRANSACTION TYPE
421 -- Combined into postal code validation. Check for TRANSACTION TYPE
422
423 -- Validate DOCUMENT LINE COUNT for Visa purchase card
424 -- Combined into postal code validation. Check for DOCUMENT LINE COUNT
425
426 -- Validate POSTAL CODE's
427 open level2_trxn;
428 loop
429 fetch level2_trxn into l_trxn_rec;
430 exit when level2_trxn%NOTFOUND;
431
432 -- Validate TRANSACTION TYPE
433 /*
434 * Fix for bug 5857483:
435 *
436 * Added ORAPMTRETURN to the list of valid transaction
437 * types.
438 */
439 if l_trxn_rec.reqtype not in ('ORAPMTCAPTURE', 'ORAPMTCREDIT',
440 'ORAPMTRETURN') then
441 x_return_status := FND_API.G_RET_STS_ERROR;
442 FND_MESSAGE.set_name('IBY','IBY_VALID_IS_INCORRECT');
443 FND_MESSAGE.set_token('ERR_OBJECT', 'Transaction type');
444 FND_MSG_PUB.add;
445 FND_MSG_PUB.Count_And_Get('T',x_msg_count,x_msg_data);
446 return;
447
448 end if;
449
450 if l_trxn_rec.instrtype = 'CREDITCARD' then
451 validate_zip(l_trxn_rec.instr_owner_postalcode,
452 l_trxn_rec.instr_owner_country,
453 l_validzip );
454
455 if l_validzip = 'N' then
456 x_return_status := FND_API.G_RET_STS_ERROR;
457 FND_MESSAGE.set_name('IBY','IBY_VALID_IS_INCORRECT');
458 FND_MESSAGE.set_token('ERR_OBJECT', 'Postal code');
459 FND_MSG_PUB.add;
460 FND_MSG_PUB.Count_And_Get('T',x_msg_count,x_msg_data);
461 return;
462 end if;
463
464 elsif l_trxn_rec.instrtype = 'PURCHASECARD' then
465
466 -- Purchase card level 2
467 if l_trxn_rec.card_data_level = 2 then
468 -- Billing postal code
469 validate_zip(l_trxn_rec.instr_owner_postalcode,
470 l_trxn_rec.instr_owner_country,
471 l_validzip );
472
473 if l_validzip = 'N' then
474 x_return_status := FND_API.G_RET_STS_ERROR;
475 FND_MESSAGE.set_name('IBY','IBY_VALID_IS_INCORRECT');
476 FND_MESSAGE.set_token('ERR_OBJECT', 'Postal code');
477 FND_MSG_PUB.add;
478 FND_MSG_PUB.Count_And_Get('T',x_msg_count,x_msg_data);
479 return;
480 end if;
481
482 /*
483 -- Shipping origin postal code
484 validate_zip(l_trxn_rec.shipfromzip,
485 l_trxn_rec.shipfromcountry, -- this does NOT exist now
486 l_validzip );
487
488 if l_validzip = 'N' then
489 x_return_status := FND_API.G_RET_STS_ERROR;
490 FND_MESSAGE.set_name('IBY','IBY_VALID_IS_INCORRECT');
491 FND_MESSAGE.set_token('ERR_OBJECT', 'Postal code');
492 FND_MSG_PUB.add;
493 FND_MSG_PUB.Count_And_Get('T',x_msg_count,x_msg_data);
494 return;
495 end if;
496
497 -- Shipping destination postal code
498 validate_zip(l_trxn_rec.shiptozip,
499 l_trxn_rec.shiptocountry, -- this does NOT exist now
500 l_validzip );
501
502 if l_validzip = 'N' then
503 x_return_status := FND_API.G_RET_STS_ERROR;
504 FND_MESSAGE.set_name('IBY','IBY_VALID_IS_INCORRECT');
505 FND_MESSAGE.set_token('ERR_OBJECT', 'Postal code');
506 FND_MSG_PUB.add;
507 FND_MSG_PUB.Count_And_Get('T',x_msg_count,x_msg_data);
508 return;
509 end if;
510 */
511
512 -- Purchase card level 3
513 elsif l_trxn_rec.card_data_level = 3 then
514 -- Billing postal code
515 validate_zip(l_trxn_rec.instr_owner_postalcode,
516 l_trxn_rec.instr_owner_country,
517 l_validzip );
518
519 if l_validzip = 'N' then
520 x_return_status := FND_API.G_RET_STS_ERROR;
521 FND_MESSAGE.set_name('IBY','IBY_VALID_IS_INCORRECT');
522 FND_MESSAGE.set_token('ERR_OBJECT', 'Postal code');
523 FND_MSG_PUB.add;
524 FND_MSG_PUB.Count_And_Get('T',x_msg_count,x_msg_data);
525 return;
526 end if;
527
528 -- Shipping-to postal code
529 iby_ar_utils.call_get_payment_info(l_trxn_rec.tangibleid,
530 l_cust_trx_id,
531 l_ar_return_status,
532 x_msg_count,
533 x_msg_data);
534 /*
535 if l_cust_trx_id = 0 then
536 x_return_status := FND_API.G_RET_STS_ERROR;
537 FND_MSG_PUB.Count_And_Get('T',x_msg_count,x_msg_data);
538 return;
539 else
540 begin
541 -- Validate DOCUMENT LINE COUNT for Visa purchase card
542 select count(*)
543 into l_doc_line_count
544 from ar_invoice_lines_v
545 where customer_trx_id = l_cust_trx_id;
546
547 if l_doc_line_count > 98 then
548 x_return_status := FND_API.G_RET_STS_ERROR;
549 FND_MESSAGE.set_name('IBY','IBY_VALID_IS_INCORRECT');
550 FND_MESSAGE.set_token('ERR_OBJECT', 'Invoice line count for Visa purchase card');
551 FND_MSG_PUB.add;
552 FND_MSG_PUB.Count_And_Get('T',x_msg_count,x_msg_data);
553 return;
554 end if;
555
556 select hl.postal_code, hl.country
557 into l_ship_to_zip, l_ship_to_country
558 from hz_locations hl,
559 hz_party_sites hps,
560 hz_cust_acct_sites_all hcasa,
561 hz_cust_site_uses_all hcsua,
562 ar_invoice_header_v arihv
563 where hl.location_id = hps.location_id and
564 hps.party_site_id = hcasa.party_site_id and
565 hcasa.cust_acct_site_id = hcsua.cust_acct_site_id and
566 hcsua.site_use_id = arihv.ship_to_site_use_id and
567 arihv.customer_trx_id = l_cust_trx_id;
568 exception
569 when others then
570 l_ship_to_zip := null;
571 l_ship_to_country := null;
572 end;
573
574 validate_zip(l_ship_to_zip,
575 l_ship_to_country,
576 l_validzip );
577
578 if l_validzip = 'N' then
579 x_return_status := FND_API.G_RET_STS_ERROR;
580 FND_MESSAGE.set_name('IBY','IBY_VALID_IS_INCORRECT');
581 FND_MESSAGE.set_token('ERR_OBJECT', 'Postal code');
582 FND_MSG_PUB.add;
583 FND_MSG_PUB.Count_And_Get('T',x_msg_count,x_msg_data);
584 return;
585 end if;
586
587 end; -- l_cust_trx_id has a value
588 */
589 -- Shipping-from postal code
590 -- This validation is NOT added, as shipfromzip is never populated
591
592 else
593 null;
594 end if; -- card_data_level is 2, 3 or else
595
596 else
597 null;
598 end if; -- instrtype is CREDITCARD, PURCHASECARD, etc.
599
600 end loop;
601 close level2_trxn;
602
603 -- Bug 4243738: Added
604 if x_return_status = FND_API.G_RET_STS_SUCCESS then
605 update iby_batches_all
606 set SENTCOUNTERDAILY = l_batch_count
607 where mbatchid = P_MBATCH_ID;
608 end if;
609
610 FND_MSG_PUB.Count_And_Get('T',x_msg_count,x_msg_data);
611
612 END; -- procedure Validate_FDCNorth_Batch
613
614 -- Validate Paymentech credit card batch
615 PROCEDURE Validate_Paymentech_Batch (
616 p_api_version IN NUMBER,
617 p_init_msg_list IN VARCHAR2 default FND_API.G_FALSE,
618 P_MBATCH_ID IN NUMBER,
619 x_return_status OUT NOCOPY VARCHAR2,
620 x_msg_count OUT NOCOPY NUMBER,
621 x_msg_data OUT NOCOPY VARCHAR2
622 ) IS
623 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Paymentech_Batch';
624 l_api_version CONSTANT NUMBER := 1.0;
625
626 l_batchid_size NUMBER;
627
628 l_validzip VARCHAR2(1);
629 l_cust_trx_id NUMBER;
630 l_ar_return_status VARCHAR2(1);
631 l_ar_msg_count NUMBER;
632 l_ar_msg_data VARCHAR2(2000);
633 l_ship_to_zip VARCHAR2(80);
634 l_ship_to_country VARCHAR2(10);
635
636 cursor level2_trxn is
637 select /*+INDEX(TC IBY_TRXN_CORE_TRXNMID_U1)*/
638 ts.instrtype,
639 tc.card_data_level,
640 ts.tangibleid,
641 tc.instr_owner_postalcode,
642 tc.instr_owner_country,
643 tc.shipfromzip,
644 tc.shiptozip
645 from iby_batches_all b,
646 iby_trxn_summaries_all ts,
647 iby_trxn_core tc
648 where b.mbatchid = P_MBATCH_ID
649 and b.mbatchid = ts.mbatchid
650 and ts.trxnmid = tc.trxnmid;
651
652 l_trxn_rec level2_trxn%ROWTYPE;
653
654 BEGIN
655 -- SAVEPOINT Validate_Paymentech_Batch;
656
657 -- Standard call to check for call compatibility
658 if NOT FND_API.Compatible_API_Call( l_api_version,
659 p_api_version,
660 l_api_name,
661 G_PKG_NAME ) then
662 raise FND_API.G_EXC_UNEXPECTED_ERROR;
663 end if;
664
665 if FND_API.To_Boolean(p_init_msg_list) then
666 FND_MSG_PUB.initialize;
667 end if;
668
669 x_return_status := FND_API.G_RET_STS_SUCCESS;
670
671 if PG_DEBUG in ('Y', 'C') then
672 null; -- iby.debug('some info');
673 end if;
674
675 -- Validate BATCH NAME
676 select length(batchid)
677 into l_batchid_size
678 from iby_batches_all
679 where mbatchid = P_MBATCH_ID;
680
681 if l_batchid_size > 8 then
682 x_return_status := FND_API.G_RET_STS_ERROR;
683 FND_MESSAGE.set_name('IBY','IBY_VALID_IS_INCORRECT');
684 FND_MESSAGE.set_token('ERR_OBJECT', 'Batch name');
685 FND_MSG_PUB.add;
686 FND_MSG_PUB.Count_And_Get('T',x_msg_count,x_msg_data);
687 return;
688 end if;
689
690 -- Validate POSTAL CODE's
691 open level2_trxn;
692 loop
693 fetch level2_trxn into l_trxn_rec;
694 exit when level2_trxn%NOTFOUND;
695
696 if l_trxn_rec.instrtype = 'CREDITCARD' then
697 validate_zip(l_trxn_rec.instr_owner_postalcode,
698 l_trxn_rec.instr_owner_country,
699 l_validzip );
700
701 if l_validzip = 'N' then
702 x_return_status := FND_API.G_RET_STS_ERROR;
703 FND_MESSAGE.set_name('IBY','IBY_VALID_IS_INCORRECT');
704 FND_MESSAGE.set_token('ERR_OBJECT', 'Postal code');
705 FND_MSG_PUB.add;
706 FND_MSG_PUB.Count_And_Get('T',x_msg_count,x_msg_data);
707 return;
708 end if;
709
710 elsif l_trxn_rec.instrtype = 'PURCHASECARD' then
711
712 -- Purchase card level 2
713 if l_trxn_rec.card_data_level = 2 then
714 -- Billing postal code
715 validate_zip(l_trxn_rec.instr_owner_postalcode,
716 l_trxn_rec.instr_owner_country,
717 l_validzip );
718
719 if l_validzip = 'N' then
720 x_return_status := FND_API.G_RET_STS_ERROR;
721 FND_MESSAGE.set_name('IBY','IBY_VALID_IS_INCORRECT');
722 FND_MESSAGE.set_token('ERR_OBJECT', 'Postal code');
723 FND_MSG_PUB.add;
724 FND_MSG_PUB.Count_And_Get('T',x_msg_count,x_msg_data);
725 return;
726 end if;
727
728 /*
729 -- Shipping origin postal code
730 validate_zip(l_trxn_rec.shipfromzip,
731 l_trxn_rec.shipfromcountry, -- this does NOT exist now
732 l_validzip );
733
734 if l_validzip = 'N' then
735 x_return_status := FND_API.G_RET_STS_ERROR;
736 FND_MESSAGE.set_name('IBY','IBY_VALID_IS_INCORRECT');
737 FND_MESSAGE.set_token('ERR_OBJECT', 'Postal code');
738 FND_MSG_PUB.add;
739 FND_MSG_PUB.Count_And_Get('T',x_msg_count,x_msg_data);
740 return;
741 end if;
742
743 -- Shipping destination postal code
744 validate_zip(l_trxn_rec.shiptozip,
745 l_trxn_rec.shiptocountry, -- this does NOT exist now
746 l_validzip );
747
748 if l_validzip = 'N' then
749 x_return_status := FND_API.G_RET_STS_ERROR;
750 FND_MESSAGE.set_name('IBY','IBY_VALID_IS_INCORRECT');
751 FND_MESSAGE.set_token('ERR_OBJECT', 'Postal code');
752 FND_MSG_PUB.add;
753 FND_MSG_PUB.Count_And_Get('T',x_msg_count,x_msg_data);
754 return;
755 end if;
756 */
757
758 -- Purchase card level 3
759 elsif l_trxn_rec.card_data_level = 3 then
760 -- Billing postal code
761 validate_zip(l_trxn_rec.instr_owner_postalcode,
762 l_trxn_rec.instr_owner_country,
763 l_validzip );
764
765 if l_validzip = 'N' then
766 x_return_status := FND_API.G_RET_STS_ERROR;
767 FND_MESSAGE.set_name('IBY','IBY_VALID_IS_INCORRECT');
768 FND_MESSAGE.set_token('ERR_OBJECT', 'Postal code');
769 FND_MSG_PUB.add;
770 FND_MSG_PUB.Count_And_Get('T',x_msg_count,x_msg_data);
771 return;
772 end if;
773
774 -- Shipping-to postal code
775 iby_ar_utils.call_get_payment_info(l_trxn_rec.tangibleid,
776 l_cust_trx_id,
777 l_ar_return_status,
778 x_msg_count,
779 x_msg_data);
780 /*
781 if l_cust_trx_id = 0 then
782 x_return_status := FND_API.G_RET_STS_ERROR;
783 FND_MSG_PUB.Count_And_Get('T',x_msg_count,x_msg_data);
784 return;
785 else
786 begin
787 select hl.postal_code, hl.country
788 into l_ship_to_zip, l_ship_to_country
789 from hz_locations hl,
790 hz_party_sites hps,
791 hz_cust_acct_sites_all hcasa,
792 hz_cust_site_uses_all hcsua,
793 ar_invoice_header_v arihv
794 where hl.location_id = hps.location_id and
795 hps.party_site_id = hcasa.party_site_id and
796 hcasa.cust_acct_site_id = hcsua.cust_acct_site_id and
797 hcsua.site_use_id = arihv.ship_to_site_use_id and
798 arihv.customer_trx_id = l_cust_trx_id;
799 exception
800 when others then
801 l_ship_to_zip := null;
802 l_ship_to_country := null;
803 end;
804
805 validate_zip(l_ship_to_zip,
806 l_ship_to_country,
807 l_validzip );
808
809 if l_validzip = 'N' then
810 x_return_status := FND_API.G_RET_STS_ERROR;
811 FND_MESSAGE.set_name('IBY','IBY_VALID_IS_INCORRECT');
812 FND_MESSAGE.set_token('ERR_OBJECT', 'Postal code');
813 FND_MSG_PUB.add;
814 FND_MSG_PUB.Count_And_Get('T',x_msg_count,x_msg_data);
815 return;
816 end if;
817
818
819 end; -- l_cust_trx_id has a value
820 */
821 -- Shipping-from postal code
822 -- This validation is NOT added, as shipfromzip is never populated
823
824 else
825 null;
826 end if; -- card_data_level is 2, 3 or else
827
828 else
829 null;
830 end if; -- instrtype is CREDITCARD, PURCHASECARD, etc.
831
832 end loop;
833 close level2_trxn;
834
835 FND_MSG_PUB.Count_And_Get('T',x_msg_count,x_msg_data);
836
837 END; -- procedure Validate_Paymentech_Batch
838
839
840 /* Validations on Mandate for SEPA DD */
841
842 /*
843 To verify that one active direct debit authorization present for customer / customer site at bank account assignment level and its cancellation date has not yet passed
844 To verify that the Mandate has the mandatory attributes like Unique Authorization Reference ID, Authorization Signing Date, Payee Legal Entity, Payee Address, Payee Identifier, Customer Address and Customer Identification Code.
845
846 */
847 PROCEDURE Validate_SEPA_Mandate (
848 p_assignment_id IN NUMBER,
849 x_message OUT NOCOPY VARCHAR2,
850 x_return_status OUT NOCOPY VARCHAR2
851 ) IS
852
853 l_dbg_mod VARCHAR2(100) := G_DEBUG_MODULE || '.Validate_SEPA_Mandate';
854
855 --13980034 - To fetch the future End dated mandates. comparison to nvl(da.debit_auth_end,sysdate) >= sysdate
856 CURSOR c_mandate (ci_account_use_id IN iby_debit_authorizations.external_bank_account_use_id%TYPE)
857 IS
858 SELECT da.authorization_reference_number
859 ,da.auth_sign_date
860 ,da.creditor_legal_entity_id
861 ,da.creditor_identifier
862 ,da.cust_addr_id
863 FROM iby_debit_authorizations da
864 WHERE debit_auth_flag = 'Y'
865 --- AND auth_cancel_date is null
866 AND nvl(da.auth_cancel_date,sysdate+10) >= sysdate
867 AND nvl(da.debit_auth_end,sysdate+10) >= sysdate
868 AND da.debit_authorization_id = ( SELECT MAX(debit_authorization_id)
869 FROM iby_debit_authorizations
870 WHERE external_bank_account_use_id = ci_account_use_id );
871
872 l_auth_reference_number iby_debit_authorizations.authorization_reference_number%TYPE;
873 l_auth_sign_date iby_debit_authorizations.auth_sign_date%TYPE;
874 l_creditor_legal_entity_id iby_debit_authorizations.creditor_legal_entity_id%TYPE;
875 l_creditor_identifier iby_debit_authorizations.creditor_identifier%TYPE;
876 l_cust_addr_id iby_debit_authorizations.cust_addr_id%TYPE;
877 l_payee_address VARCHAR2(4000);
878 l_iban iby_ext_bank_accounts.iban%TYPE;
879 l_swift_code ce_bank_branches_v.eft_swift_code%TYPE;
880
881 BEGIN
882
883 x_return_status := FND_API.G_RET_STS_SUCCESS;
884
885 print_debuginfo(l_dbg_mod, 'Enter:');
886
887 print_debuginfo(l_dbg_mod, 'p_assignment_id: ' || p_assignment_id);
888
889 IF (c_mandate%ISOPEN) THEN CLOSE c_mandate; END IF;
890
891 OPEN c_mandate(p_assignment_id);
892 FETCH c_mandate INTO l_auth_reference_number ,l_auth_sign_date
893 ,l_creditor_legal_entity_id ,l_creditor_identifier
894 ,l_cust_addr_id;
895
896 IF (c_mandate%NOTFOUND) THEN
897 x_return_status := 'INVALID';
898 x_message := 'NO ACTIVE MANDATE';
899
900 print_debuginfo(l_dbg_mod, 'x_message ' || x_message);
901 return;
902
903 END IF;
904 CLOSE c_mandate;
905
906 BEGIN
907
908 SELECT eb.iban , bbv.eft_swift_code into
909 l_iban , l_swift_code
910 FROM iby_pmt_instr_uses_all uses,
911 iby_ext_bank_accounts eb,
912 ce_bank_branches_v bbv
913 WHERE uses.instrument_id = eb.ext_bank_account_id
914 AND uses.payment_flow = 'FUNDS_CAPTURE'
915 AND uses.instrument_type = 'BANKACCOUNT'
916 AND eb.branch_id = bbv.branch_party_id
917 AND uses.instrument_payment_use_id = p_assignment_id;
918 EXCEPTION WHEN OTHERS THEN
919 x_return_status := 'INVALID';
920 x_message := 'iban/swiftcode';
921 print_debuginfo(l_dbg_mod, 'x_message ' || x_message);
922 return;
923 END;
924
925
926 IF ( l_creditor_legal_entity_id IS NOT NULL )
927 THEN
928 BEGIN
929 SELECT (address_line_1 || ', '
930 || DECODE(address_line_2, NULL, '', ',')
931 || DECODE(address_line_3, NULL, '', ',')
932 || town_or_city || ', ' || country || ', '
933 || postal_code) PayeeAddress
934 INTO l_payee_address
935 FROM xle_firstparty_information_v
936 WHERE legal_entity_id = l_creditor_legal_entity_id;
937 EXCEPTION WHEN OTHERS THEN
938 x_return_status := 'INVALID';
939 x_message := 'Payee Address';
940 print_debuginfo(l_dbg_mod, 'x_message ' || x_message);
941 return;
942 END;
943 END IF;
944
945 IF (l_auth_reference_number IS NULL )
946 THEN
947 x_return_status := 'INVALID';
948 x_message := 'UNIQUE AUTH REFERENCE';
949 print_debuginfo(l_dbg_mod, 'x_message ' || x_message);
950 return;
951 ELSIF (l_auth_sign_date IS NULL )
952 THEN
953 x_return_status := 'INVALID';
954 x_message := 'AUTH SIGN DATE';
955 print_debuginfo(l_dbg_mod, 'x_message ' || x_message);
956 return;
957 ELSIF (l_creditor_legal_entity_id IS NULL )
958 THEN
959 x_return_status := 'INVALID';
960 x_message := 'Payee LE';
961 print_debuginfo(l_dbg_mod, 'x_message ' || x_message);
962 return;
963 ELSIF (l_payee_address IS NULL )
964 THEN
965 x_return_status := 'INVALID';
966 x_message := 'Payee Address';
967 print_debuginfo(l_dbg_mod, 'x_message ' || x_message);
968 return;
969 ELSIF (l_creditor_identifier IS NULL )
970 THEN
971 x_return_status := 'INVALID';
972 x_message := 'Payee Identification';
973 print_debuginfo(l_dbg_mod, 'x_message ' || x_message);
974 return;
975 ELSIF (l_cust_addr_id IS NULL )
976 THEN
977 x_return_status := 'INVALID';
978 x_message := 'Customer Address';
979 print_debuginfo(l_dbg_mod, 'x_message ' || x_message);
980 return;
981 ELSIF (l_iban IS NULL )
982 THEN
983 x_return_status := 'INVALID';
984 x_message := 'Customer Address';
985 print_debuginfo(l_dbg_mod, 'x_message ' || x_message);
986 return;
987 ELSIF (l_swift_code IS NULL )
988 THEN
989 x_return_status := 'INVALID';
990 x_message := 'Customer Address';
991 print_debuginfo(l_dbg_mod, 'x_message ' || x_message);
992 return;
993 END IF;
994
995 print_debuginfo(l_dbg_mod, 'x_return_status ' || x_return_status);
996 print_debuginfo(l_dbg_mod, 'x_message ' || x_message);
997
998 print_debuginfo(l_dbg_mod, 'Exit ');
999
1000 END; -- procedure Validate_SEPA_Mandate
1001
1002
1003
1004 -- Validate SEPA DD batch
1005 PROCEDURE Validate_Sepa_DD_Batch (
1006 p_api_version IN NUMBER,
1007 p_init_msg_list IN VARCHAR2 default FND_API.G_FALSE,
1008 P_MBATCH_ID IN NUMBER,
1009 x_return_status OUT NOCOPY VARCHAR2,
1010 x_msg_count OUT NOCOPY NUMBER,
1011 x_msg_data OUT NOCOPY VARCHAR2
1012 ) IS
1013
1014 l_dbg_mod VARCHAR2(100) := G_DEBUG_MODULE || '.Validate_Sepa_DD_Batch';
1015 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Sepa_DD_Batch';
1016 l_api_version CONSTANT NUMBER := 1.0;
1017
1018 -- Cursor for settlement records.
1019 cursor c_settle_trxns is
1020 select ts.trxnmid
1021 from iby_batches_all b
1022 ,iby_trxn_summaries_all ts
1023 where b.mbatchid = P_MBATCH_ID
1024 and b.mbatchid = ts.mbatchid ;
1025
1026 -- Cursor to retrieve the certain mandate params.
1027 cursor c_mandate(ci_trxnmid IN iby_trxn_summaries_all.trxnmid%TYPE)
1028 is
1029 select da.authorization_reference_number
1030 ,da.auth_sign_date
1031 ,da.creditor_legal_entity_id
1032 ,da.creditor_identifier
1033 ,da.cust_addr_id
1034 ,da.creditor_le_name
1035 ,da.external_bank_account_use_id
1036 from iby_trxn_summaries_all ts
1037 ,iby_debit_authorizations da
1038 where ts.trxnmid = ci_trxnmid
1039 and ts.debit_authorization_id = da.debit_authorization_id
1040 -- and da.auth_cancel_date is NULL
1041 AND nvl(da.auth_cancel_date,sysdate+10) >= sysdate
1042 AND nvl(da.debit_auth_end,sysdate+10) >= sysdate
1043 and da.curr_rec_indi = 'Y' ;
1044
1045 -- Cursor to retrieve the account option values.
1046 cursor c_acct_option_vals
1047 ( ci_trxnmid IN iby_trxn_summaries_all.trxnmid%TYPE )
1048 is
1049 select vals.account_option_code
1050 ,vals.account_option_value
1051 from iby_trxn_summaries_all ts
1052 ,iby_bepkeys keys
1053 ,iby_bep_acct_opt_vals vals
1054 ,iby_payee payee
1055 where ts.trxnmid = ci_trxnmid
1056 and ts.payeeid = payee.payeeid
1057 and ts.payeeid = keys.ownerid
1058 and ts.bepkey = keys.key
1059 and keys.ownertype = 'PAYEE'
1060 and keys.bep_account_id = vals.bep_account_id (+)
1061 -- and vals.account_option_code = ci_acct_option_code;
1062 and vals.account_option_code in ('SEPA_INITIATING_PARTY_ID' , 'SEPA_INITIATING_PARTY_ID_ISSR'
1063 ,'SEPA_INITIATING_PARTY_NAME' ,'SEPA_BATCH_BOOKING' );
1064
1065 l_trxnmid iby_trxn_summaries_all.trxnmid%TYPE;
1066
1067 l_auth_reference_number iby_debit_authorizations.authorization_reference_number%TYPE;
1068 l_auth_sign_date iby_debit_authorizations.auth_sign_date%TYPE;
1069 l_creditor_legal_entity_id iby_debit_authorizations.creditor_legal_entity_id%TYPE;
1070 l_creditor_identifier iby_debit_authorizations.creditor_identifier%TYPE;
1071 l_cust_addr_id iby_debit_authorizations.cust_addr_id%TYPE;
1072
1073 l_ini_party_id iby_bep_acct_opt_vals.account_option_value%TYPE;
1074 l_ini_party_id_issuer iby_bep_acct_opt_vals.account_option_value%TYPE;
1075 l_ini_party_name iby_bep_acct_opt_vals.account_option_value%TYPE;
1076 l_batch_booking_flag iby_bep_acct_opt_vals.account_option_value%TYPE;
1077
1078 l_account_option_code iby_bep_acct_opt_vals.account_option_code%TYPE;
1079 l_account_option_value iby_bep_acct_opt_vals.account_option_value%TYPE;
1080 l_creditor_le_name iby_debit_authorizations.creditor_le_name%TYPE;
1081 l_payee_address VARCHAR2(4000);
1082 l_le_name VARCHAR2(4000);
1083 l_external_bank_account_use_id iby_debit_authorizations.external_bank_account_use_id%TYPE;
1084 l_iban iby_ext_bank_accounts.iban%TYPE;
1085 l_swift_code ce_bank_branches_v.eft_swift_code%TYPE;
1086
1087
1088 BEGIN
1089
1090 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1091 print_debuginfo(G_DEBUG_MODULE, 'Entered ... ');
1092 END IF;
1093
1094 -- Standard call to check for call compatibility
1095 if NOT FND_API.Compatible_API_Call( l_api_version,
1096 p_api_version,
1097 l_api_name,
1098 G_PKG_NAME ) then
1099 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1100 end if;
1101
1102 if FND_API.To_Boolean(p_init_msg_list) then
1103 FND_MSG_PUB.initialize;
1104 end if;
1105
1106 x_return_status := FND_API.G_RET_STS_SUCCESS;
1107
1108
1109 open c_settle_trxns;
1110 loop
1111 fetch c_settle_trxns into l_trxnmid;
1112 exit when c_settle_trxns%NOTFOUND;
1113
1114 OPEN c_mandate(l_trxnmid);
1115 FETCH c_mandate INTO l_auth_reference_number ,l_auth_sign_date
1116 ,l_creditor_legal_entity_id ,l_creditor_identifier
1117 ,l_cust_addr_id ,l_creditor_le_name ,l_external_bank_account_use_id;
1118
1119 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1120 print_debuginfo(G_DEBUG_MODULE, 'After mandate cursor ');
1121 END IF;
1122
1123 IF (c_mandate%NOTFOUND) THEN
1124 x_return_status := FND_API.G_RET_STS_ERROR;
1125 FND_MESSAGE.set_name('IBY','Active Debit Authorization is required');
1126 FND_MESSAGE.set_token('ERR_OBJECT', 'Batch name');
1127 FND_MSG_PUB.add;
1128 FND_MSG_PUB.Count_And_Get('T',x_msg_count,x_msg_data);
1129
1130 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1131 print_debuginfo(G_DEBUG_MODULE, 'Checking Active Debit Authorization');
1132 END IF;
1133
1134 return;
1135 END IF;
1136 CLOSE c_mandate;
1137
1138 BEGIN
1139 SELECT eb.iban , bbv.eft_swift_code into
1140 l_iban , l_swift_code
1141 FROM iby_pmt_instr_uses_all uses,
1142 iby_ext_bank_accounts eb,
1143 ce_bank_branches_v bbv
1144 WHERE uses.instrument_id = eb.ext_bank_account_id
1145 AND uses.payment_flow = 'FUNDS_CAPTURE'
1146 AND uses.instrument_type = 'BANKACCOUNT'
1147 AND eb.branch_id = bbv.branch_party_id
1148 AND uses.instrument_payment_use_id = l_external_bank_account_use_id;
1149 EXCEPTION WHEN OTHERS THEN
1150 x_return_status := FND_API.G_RET_STS_ERROR;
1151 FND_MESSAGE.set_name('IBY','iban and BIC name is required on Debit Authorization');
1152 FND_MESSAGE.set_token('ERR_OBJECT', 'Batch name');
1153 FND_MSG_PUB.add;
1154 FND_MSG_PUB.Count_And_Get('T',x_msg_count,x_msg_data);
1155 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1156 print_debuginfo(G_DEBUG_MODULE, 'Checking Payee iban and BIC');
1157 END IF;
1158 END;
1159
1160
1161 IF (NOT l_creditor_legal_entity_id IS NULL )
1162 THEN
1163 BEGIN
1164 SELECT (address_line_1 || ', '
1165 || DECODE(address_line_2, NULL, '', ',')
1166 || DECODE(address_line_3, NULL, '', ',')
1167 || town_or_city || ', ' || country || ', '
1168 || postal_code ) PayeeAddress
1169 , name LegalEntityName
1170 INTO l_payee_address, l_le_name
1171 FROM xle_firstparty_information_v
1172 WHERE legal_entity_id = l_creditor_legal_entity_id ;
1173 EXCEPTION WHEN OTHERS THEN
1174 x_return_status := FND_API.G_RET_STS_ERROR;
1175 FND_MESSAGE.set_name('IBY','LE name and payee address required on Debit Authorization');
1176 FND_MESSAGE.set_token('ERR_OBJECT', 'Batch name');
1177 FND_MSG_PUB.add;
1178 FND_MSG_PUB.Count_And_Get('T',x_msg_count,x_msg_data);
1179 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1180 print_debuginfo(G_DEBUG_MODULE, 'Checking Payee address and LE name');
1181 END IF;
1182 END;
1183 END IF;
1184
1185 /* checking the mandate attributes */
1186 IF(l_auth_reference_number IS NULL )
1187 THEN
1188 x_return_status := FND_API.G_RET_STS_ERROR;
1189 FND_MESSAGE.set_name('IBY','Unique Authorization Reference ID is required on Enter Debit Authorization');
1190 FND_MESSAGE.set_token('ERR_OBJECT', 'Batch name');
1191 FND_MSG_PUB.add;
1192 FND_MSG_PUB.Count_And_Get('T',x_msg_count,x_msg_data);
1193 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1194 print_debuginfo(G_DEBUG_MODULE, 'Checking auth ref id ');
1195 END IF;
1196 ELSIF (l_auth_sign_date IS NULL )
1197 THEN
1198 x_return_status := FND_API.G_RET_STS_ERROR;
1199 FND_MESSAGE.set_name('IBY','Authorization Signing Date is required on Enter Debit Authorization');
1200 FND_MESSAGE.set_token('ERR_OBJECT', 'Batch name');
1201 FND_MSG_PUB.add;
1202 FND_MSG_PUB.Count_And_Get('T',x_msg_count,x_msg_data);
1203 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1204 print_debuginfo(G_DEBUG_MODULE, 'Authsign date');
1205 END IF;
1206 ELSIF (l_creditor_legal_entity_id IS NULL )
1207 THEN
1208 x_return_status := FND_API.G_RET_STS_ERROR;
1209 FND_MESSAGE.set_name('IBY','Payee Legal Entity is required on Enter Debit Authorization');
1210 FND_MESSAGE.set_token('ERR_OBJECT', 'Batch name');
1211 FND_MSG_PUB.add;
1212 FND_MSG_PUB.Count_And_Get('T',x_msg_count,x_msg_data);
1213 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1214 print_debuginfo(G_DEBUG_MODULE, 'Payee legal entity ');
1215 END IF;
1216 ELSIF (l_payee_address IS NULL )
1217 THEN
1218 x_return_status := FND_API.G_RET_STS_ERROR;
1219 FND_MESSAGE.set_name('IBY','Payee Address is required on Enter Debit Authorization');
1220 FND_MESSAGE.set_token('ERR_OBJECT', 'Batch name');
1221 FND_MSG_PUB.add;
1222 FND_MSG_PUB.Count_And_Get('T',x_msg_count,x_msg_data);
1223 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1224 print_debuginfo(G_DEBUG_MODULE, 'Payee Address ');
1225 END IF;
1226 ELSIF (l_creditor_identifier IS NULL )
1227 THEN
1228 x_return_status := FND_API.G_RET_STS_ERROR;
1229 FND_MESSAGE.set_name('IBY','Payee Identifier is required on Enter Debit Authorization');
1230 FND_MESSAGE.set_token('ERR_OBJECT', 'Batch name');
1231 FND_MSG_PUB.add;
1232 FND_MSG_PUB.Count_And_Get('T',x_msg_count,x_msg_data);
1233 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1234 print_debuginfo(G_DEBUG_MODULE, 'Payee identifier');
1235 END IF;
1236 ELSIF (l_cust_addr_id IS NULL )
1237 THEN
1238 x_return_status := FND_API.G_RET_STS_ERROR;
1239 FND_MESSAGE.set_name('IBY','Customer Address is required on Enter Debit Authorizationd');
1240 FND_MESSAGE.set_token('ERR_OBJECT', 'Batch name');
1241 FND_MSG_PUB.add;
1242 FND_MSG_PUB.Count_And_Get('T',x_msg_count,x_msg_data);
1243 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1244 print_debuginfo(G_DEBUG_MODULE, 'Customer address');
1245 END IF;
1246 ELSIF (l_creditor_le_name <> l_le_name)
1247 THEN
1248 x_return_status := FND_API.G_RET_STS_ERROR;
1249 FND_MESSAGE.set_name('IBY','Payee LegalEntity Name is Invalid');
1250 FND_MESSAGE.set_token('ERR_OBJECT', 'Batch name');
1251 FND_MSG_PUB.add;
1252 FND_MSG_PUB.Count_And_Get('T',x_msg_count,x_msg_data);
1253 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1254 print_debuginfo(G_DEBUG_MODULE, 'Payee LE Name');
1255 END IF;
1256 ELSIF (l_iban IS NULL )
1257 THEN
1258 x_return_status := FND_API.G_RET_STS_ERROR;
1259 FND_MESSAGE.set_name('IBY','IBAN is required on Enter Debit Authorizationd');
1260 FND_MESSAGE.set_token('ERR_OBJECT', 'Batch name');
1261 FND_MSG_PUB.add;
1262 FND_MSG_PUB.Count_And_Get('T',x_msg_count,x_msg_data);
1263 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1264 print_debuginfo(G_DEBUG_MODULE, 'IBAN');
1265 END IF;
1266 ELSIF (l_swift_code IS NULL )
1267 THEN
1268 x_return_status := FND_API.G_RET_STS_ERROR;
1269 FND_MESSAGE.set_name('IBY','BIC is required on Enter Debit Authorizationd');
1270 FND_MESSAGE.set_token('ERR_OBJECT', 'Batch name');
1271 FND_MSG_PUB.add;
1272 FND_MSG_PUB.Count_And_Get('T',x_msg_count,x_msg_data);
1273 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1274 print_debuginfo(G_DEBUG_MODULE, 'BIC');
1275 END IF;
1276 END IF;
1277
1278 /* checking the payment system option */
1279 l_ini_party_id := NULL;
1280 l_ini_party_id_issuer := NULL;
1281 l_ini_party_name := NULL;
1282 l_batch_booking_flag := NULL;
1283
1284 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1285 print_debuginfo(G_DEBUG_MODULE, 'Checking Account Options');
1286 END IF;
1287
1288 OPEN c_acct_option_vals(l_trxnmid);
1289 loop
1290 FETCH c_acct_option_vals INTO l_account_option_code ,l_account_option_value;
1291 EXIT when c_acct_option_vals%NOTFOUND;
1292
1293 IF (l_account_option_code = 'SEPA_INITIATING_PARTY_ID')
1294 THEN
1295 l_ini_party_id := l_account_option_value ;
1296 ELSIF (l_account_option_code = 'SEPA_INITIATING_PARTY_ID_ISSR')
1297 THEN
1298 l_ini_party_id_issuer := l_account_option_value ;
1299 ELSIF (l_account_option_code = 'SEPA_INITIATING_PARTY_NAME')
1300 THEN
1301 l_ini_party_name := l_account_option_value ;
1302 ELSIF (l_account_option_code = 'SEPA_BATCH_BOOKING')
1303 THEN
1304 l_batch_booking_flag := l_account_option_value ;
1305 END IF;
1306
1307 end loop;
1308
1309 CLOSE c_acct_option_vals;
1310
1311 IF (l_ini_party_id IS NULL)
1312 THEN
1313 x_return_status := FND_API.G_RET_STS_ERROR;
1314 FND_MESSAGE.set_name('IBY','Initiating Party ID is required');
1315 FND_MESSAGE.set_token('ERR_OBJECT', 'Batch name');
1316 FND_MSG_PUB.add;
1317 FND_MSG_PUB.Count_And_Get('T',x_msg_count,x_msg_data);
1318 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1319 print_debuginfo(G_DEBUG_MODULE, 'Initaiting party id');
1320 END IF;
1321 ELSIF (l_ini_party_id_issuer IS NULL)
1322 THEN
1323 x_return_status := FND_API.G_RET_STS_ERROR;
1324 FND_MESSAGE.set_name('IBY','Initiating Party ID Issuer is required');
1325 FND_MESSAGE.set_token('ERR_OBJECT', 'Batch name');
1326 FND_MSG_PUB.add;
1327 FND_MSG_PUB.Count_And_Get('T',x_msg_count,x_msg_data);
1328 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1329 print_debuginfo(G_DEBUG_MODULE, 'Init Party Id issr ');
1330 END IF;
1331 ELSIF (l_ini_party_name IS NULL)
1332 THEN
1333 x_return_status := FND_API.G_RET_STS_ERROR;
1334 FND_MESSAGE.set_name('IBY','Initiating Party Name is required');
1335 FND_MESSAGE.set_token('ERR_OBJECT', 'Batch name');
1336 FND_MSG_PUB.add;
1337 FND_MSG_PUB.Count_And_Get('T',x_msg_count,x_msg_data);
1338 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1339 print_debuginfo(G_DEBUG_MODULE, 'Ini Party Name');
1340 END IF;
1341 ELSIF (l_batch_booking_flag IS NULL)
1342 THEN
1343 x_return_status := FND_API.G_RET_STS_ERROR;
1344 FND_MESSAGE.set_name('IBY','Batch Booking is required');
1345 FND_MESSAGE.set_token('ERR_OBJECT', 'Batch name');
1346 FND_MSG_PUB.add;
1347 FND_MSG_PUB.Count_And_Get('T',x_msg_count,x_msg_data);
1348 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1349 print_debuginfo(G_DEBUG_MODULE, 'batch booking');
1350 END IF;
1351 ELSIF ((l_batch_booking_flag <> 'TRUE') AND (l_batch_booking_flag <> 'FALSE' ))
1352 THEN
1353 x_return_status := FND_API.G_RET_STS_ERROR;
1354 FND_MESSAGE.set_name('IBY','Batch Booking is Invalid');
1355 FND_MESSAGE.set_token('ERR_OBJECT', 'Batch name');
1356 FND_MSG_PUB.add;
1357 FND_MSG_PUB.Count_And_Get('T',x_msg_count,x_msg_data);
1358 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1359 print_debuginfo(G_DEBUG_MODULE, 'Batch booking values');
1360 END IF;
1361 END IF;
1362
1363 /* End of checking the payment system option */
1364
1365 end loop;
1366 close c_settle_trxns;
1367
1368 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1369 print_debuginfo(G_DEBUG_MODULE, 'Inactivating the One-Off and Final Mandates. ');
1370 END IF;
1371
1372 update iby_debit_authorizations
1373 set debit_auth_flag = 'N' , debit_auth_end = sysdate
1374 where debit_authorization_id in
1375 ( select debit_authorization_id
1376 from iby_trxn_summaries_all
1377 where trxntypeid = 100
1378 and instrtype = 'BANKACCOUNT'
1379 and mbatchid = P_MBATCH_ID
1380 and seq_type in ('FNAL', 'OOFF')
1381 );
1382
1383 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1384 print_debuginfo(G_DEBUG_MODULE, 'After inactivating ... ');
1385 END IF;
1386
1387 FND_MSG_PUB.Count_And_Get('T',x_msg_count,x_msg_data);
1388
1389 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1390 print_debuginfo(G_DEBUG_MODULE, 'Exiting ... ');
1391 END IF;
1392
1393 END; -- procedure Validate_Sepa_DD_Batch
1394
1395
1396 --Active Mandate for NON SEPA Customers.
1397 PROCEDURE NonSEPA_Active_Mandate(
1398 p_api_version IN NUMBER,
1399 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1400 P_MBATCH_ID IN NUMBER,
1401 x_return_status OUT NOCOPY VARCHAR2,
1402 x_msg_count OUT NOCOPY NUMBER,
1403 x_msg_data OUT NOCOPY VARCHAR2 )
1404 IS
1405 l_dbg_mod VARCHAR2(100) := G_DEBUG_MODULE || '.Validate_NonSEPA_Active_Mandate';
1406 l_api_name CONSTANT VARCHAR2(50) := 'Validate_NonSEPA_Active_Mandate';
1407 l_api_version CONSTANT NUMBER := 1.0;
1408 -- Cursor for settlement records.
1409 CURSOR c_settle_trxns
1410 IS
1411 SELECT ts.trxnmid
1412 FROM iby_batches_all b ,
1413 iby_trxn_summaries_all ts
1414 WHERE b.mbatchid = P_MBATCH_ID
1415 AND b.mbatchid = ts.mbatchid ;
1416 -- Cursor to retrieve the certain mandate params.
1417 CURSOR c_mandate(ci_trxnmid IN iby_trxn_summaries_all.trxnmid%TYPE)
1418 IS
1419 SELECT da.authorization_reference_number
1420 FROM iby_trxn_summaries_all ts ,
1421 iby_debit_authorizations da
1422 WHERE ts.trxnmid = ci_trxnmid
1423 AND ts.debit_authorization_id = da.debit_authorization_id
1424 AND NVL(da.auth_cancel_date,sysdate) >= ts.settledate
1425 AND NVL(da.debit_auth_end,sysdate) >= ts.settledate
1426 AND NVL(da.auth_sign_date,sysdate) <= ts.settledate
1427 AND da.curr_rec_indi = 'Y' ;
1428 l_trxnmid iby_trxn_summaries_all.trxnmid%TYPE;
1429 l_auth_reference_number iby_debit_authorizations.authorization_reference_number%TYPE;
1430 BEGIN
1431 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1432 print_debuginfo(G_DEBUG_MODULE, 'Entered ... ');
1433 END IF;
1434 -- Standard call to check for call compatibility
1435 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME ) THEN
1436 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1437 END IF;
1438 IF FND_API.To_Boolean(p_init_msg_list) THEN
1439 FND_MSG_PUB.initialize;
1440 END IF;
1441 x_return_status := FND_API.G_RET_STS_SUCCESS;
1442 OPEN c_settle_trxns;
1443 LOOP
1444 FETCH c_settle_trxns INTO l_trxnmid;
1445 EXIT
1446 WHEN c_settle_trxns%NOTFOUND;
1447 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1448 print_debuginfo(G_DEBUG_MODULE, 'Trxnmid:' || l_trxnmid);
1449 END IF;
1450 OPEN c_mandate(l_trxnmid);
1451 FETCH c_mandate INTO l_auth_reference_number;
1452 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1453 print_debuginfo(G_DEBUG_MODULE, 'Active mandate Reference:' || l_auth_reference_number);
1454 END IF;
1455 IF (c_mandate%NOTFOUND) THEN
1456 x_return_status := FND_API.G_RET_STS_ERROR;
1457 FND_MESSAGE.set_name('IBY','No active mandate');
1458 FND_MESSAGE.set_token('ERR_OBJECT', 'Batch:'||P_MBATCH_ID ||' Trxnmid : ' || l_trxnmid);
1459 FND_MSG_PUB.add;
1460 FND_MSG_PUB.Count_And_Get('T',x_msg_count,x_msg_data);
1461 RETURN;
1462 END IF;
1463 CLOSE c_mandate;
1464 END LOOP;
1465 CLOSE c_settle_trxns;
1466 FND_MSG_PUB.Count_And_Get('T',x_msg_count,x_msg_data);
1467 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1468 print_debuginfo(G_DEBUG_MODULE, 'Exiting ... ');
1469 END IF;
1470 END; -- End Validate_NonSEPA_Active_Mandate
1471
1472 END IBY_FNDCPT_VLD_PUB;