[Home] [Help]
PACKAGE BODY: APPS.IBY_FNDCPT_VLD_PUB
Source
1 PACKAGE BODY IBY_FNDCPT_VLD_PUB AS
2 /* $Header: ibypfcvb.pls 120.4.12010000.1 2008/07/28 05:41:40 appldev ship $ */
3
4 -- Package global constants
5 PG_DEBUG VARCHAR2(1) := nvl(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
6
7
8 --------------- The following are internal procedures ---------------
9
10 PROCEDURE validate_zip (
11 P_POSTAL_CODE IN VARCHAR2,
12 P_COUNTRY_CODE IN VARCHAR2,
13 X_STATUS OUT NOCOPY VARCHAR2
14 ) IS
15 l_zip_length NUMBER;
16 l_zip VARCHAR2(80);
17 BEGIN
18 X_STATUS := 'Y';
19
20 if P_COUNTRY_CODE = 'US' then
21 l_zip_length := length(P_POSTAL_CODE);
22
23 if l_zip_length = 5 or l_zip_length = 9 or (l_zip_length = 10 and substr(P_POSTAL_CODE,6,1) = '-') then
24 if l_zip_length = 10 then
25 l_zip := substr(P_POSTAL_CODE,1,5)||substr(P_POSTAL_CODE,7,4);
26 else
27 l_zip := P_POSTAL_CODE;
28 end if;
29
30 if translate(trim(l_zip),'0123456789',' ') <> rpad(' ',length(trim(l_zip)), ' ') THEN
31 X_STATUS := 'N';
32 end if;
33 else
34 X_STATUS := 'N';
35 end if; -- if l_zip_length is 5, 9 or 10 etc.
36 end if;
37
38 END; -- procedure validate_zip
39
40 --------------- The following are PUBLIC procedures ---------------
41
42
43 -- Validate Citibank credit card batch
44 PROCEDURE Validate_Citibank_Batch (
45 p_api_version IN NUMBER,
46 p_init_msg_list IN VARCHAR2 default FND_API.G_FALSE,
47 P_MBATCH_ID IN NUMBER,
48 x_return_status OUT NOCOPY VARCHAR2,
49 x_msg_count OUT NOCOPY NUMBER,
50 x_msg_data OUT NOCOPY VARCHAR2
51 ) IS
52 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Citibank_Batch';
53 l_api_version CONSTANT NUMBER := 1.0;
54
55 l_batchid_size NUMBER;
56
57 l_validzip VARCHAR2(1);
58 l_cust_trx_id NUMBER;
59 l_ar_return_status VARCHAR2(1);
60 l_ar_msg_count NUMBER;
61 l_ar_msg_data VARCHAR2(2000);
62 l_ship_to_zip VARCHAR2(80);
63 l_ship_to_country VARCHAR2(10);
64
65 cursor level2_trxn is
66 select ts.instrtype,
67 tc.card_data_level,
68 ts.tangibleid,
69 tc.instr_owner_postalcode,
70 tc.instr_owner_country,
71 tc.shipfromzip,
72 tc.shiptozip
73 from iby_batches_all b,
74 iby_trxn_summaries_all ts,
75 iby_trxn_core tc
76 where b.mbatchid = P_MBATCH_ID
77 and b.mbatchid = ts.mbatchid
78 and ts.trxnmid = tc.trxnmid;
79
80 l_trxn_rec level2_trxn%ROWTYPE;
81
82 BEGIN
83 -- SAVEPOINT Validate_Paymentech_Batch;
84
85 -- Standard call to check for call compatibility
86 if NOT FND_API.Compatible_API_Call( l_api_version,
87 p_api_version,
88 l_api_name,
89 G_PKG_NAME ) then
90 raise FND_API.G_EXC_UNEXPECTED_ERROR;
91 end if;
92
93 if FND_API.To_Boolean(p_init_msg_list) then
94 FND_MSG_PUB.initialize;
95 end if;
96
97 x_return_status := FND_API.G_RET_STS_SUCCESS;
98
99 if PG_DEBUG in ('Y', 'C') then
100 null; -- iby.debug('some info');
101 end if;
102
103 -- Validate BATCH NAME
104 select length(batchid)
105 into l_batchid_size
106 from iby_batches_all
107 where mbatchid = P_MBATCH_ID;
108
109 if l_batchid_size > 8 then
110 x_return_status := FND_API.G_RET_STS_ERROR;
111 FND_MESSAGE.set_name('IBY','IBY_VALID_IS_INCORRECT');
112 FND_MESSAGE.set_token('ERR_OBJECT', 'Batch name');
113 FND_MSG_PUB.add;
114 FND_MSG_PUB.Count_And_Get('T',x_msg_count,x_msg_data);
115 return;
116 end if;
117
118 -- Validate POSTAL CODE's
119 open level2_trxn;
120 loop
121 fetch level2_trxn into l_trxn_rec;
122 exit when level2_trxn%NOTFOUND;
123
124 if l_trxn_rec.instrtype = 'CREDITCARD' then
125 -- Billing postal code
126 validate_zip(l_trxn_rec.instr_owner_postalcode,
127 l_trxn_rec.instr_owner_country,
128 l_validzip );
129
130 if l_validzip = 'N' then
131 x_return_status := FND_API.G_RET_STS_ERROR;
132 FND_MESSAGE.set_name('IBY','IBY_VALID_IS_INCORRECT');
133 FND_MESSAGE.set_token('ERR_OBJECT', 'Postal code');
134 FND_MSG_PUB.add;
135 FND_MSG_PUB.Count_And_Get('T',x_msg_count,x_msg_data);
136 return;
137 end if;
138
139 -- PENDING: MERCHANT_POSTAL_CODE
140
141 elsif l_trxn_rec.instrtype = 'PURCHASECARD' then
142 -- Billing postal code
143 validate_zip(l_trxn_rec.instr_owner_postalcode,
144 l_trxn_rec.instr_owner_country,
145 l_validzip );
146
147 if l_validzip = 'N' then
148 x_return_status := FND_API.G_RET_STS_ERROR;
149 FND_MESSAGE.set_name('IBY','IBY_VALID_IS_INCORRECT');
150 FND_MESSAGE.set_token('ERR_OBJECT', 'Postal code');
151 FND_MSG_PUB.add;
152 FND_MSG_PUB.Count_And_Get('T',x_msg_count,x_msg_data);
153 return;
154 end if;
155
156 -- PENDING: MERCHANT_POSTAL_CODE
157
158 /*
159 -- Shipping origin postal code: invoiceHeader.sellerPartner.address.postalCode
160 validate_zip(l_trxn_rec.shipfromzip,
161 l_trxn_rec.shipfromcountry, -- this does NOT exist now
162 l_validzip );
163
164 if l_validzip = 'N' then
165 x_return_status := FND_API.G_RET_STS_ERROR;
166 FND_MESSAGE.set_name('IBY','IBY_VALID_IS_INCORRECT');
167 FND_MESSAGE.set_token('ERR_OBJECT', 'Postal code');
168 FND_MSG_PUB.add;
169 FND_MSG_PUB.Count_And_Get('T',x_msg_count,x_msg_data);
170 return;
171 end if;
172
173 -- Shipping destination postal code: invoiceHeader.buyerPartner.address.postalCode
174 validate_zip(l_trxn_rec.shiptozip,
175 l_trxn_rec.shiptocountry, -- this does NOT exist now
176 l_validzip );
177
178 if l_validzip = 'N' then
179 x_return_status := FND_API.G_RET_STS_ERROR;
180 FND_MESSAGE.set_name('IBY','IBY_VALID_IS_INCORRECT');
181 FND_MESSAGE.set_token('ERR_OBJECT', 'Postal code');
182 FND_MSG_PUB.add;
183 FND_MSG_PUB.Count_And_Get('T',x_msg_count,x_msg_data);
184 return;
185 end if;
186 */
187
188 else
189 null;
190 end if; -- instrtype is CREDITCARD, PURCHASECARD, etc.
191
192 end loop;
193 close level2_trxn;
194
195 FND_MSG_PUB.Count_And_Get('T',x_msg_count,x_msg_data);
196
197 END; -- procedure Validate_Citibank_Batch
198
199
200 -- Validate FDCNorth credit card batch
201 PROCEDURE Validate_FDCNorth_Batch (
202 p_api_version IN NUMBER,
203 p_init_msg_list IN VARCHAR2 default FND_API.G_FALSE,
204 P_MBATCH_ID IN NUMBER,
205 x_return_status OUT NOCOPY VARCHAR2,
206 x_msg_count OUT NOCOPY NUMBER,
207 x_msg_data OUT NOCOPY VARCHAR2
208 ) IS
209 l_api_name CONSTANT VARCHAR2(30) := 'Validate_FDCNorth_Batch';
210 l_api_version CONSTANT NUMBER := 1.0;
211
212 l_payee_id NUMBER;
213 l_batch_close_date DATE;
214 l_batch_count NUMBER;
215 l_doc_line_count NUMBER;
216 l_security_code VARCHAR2(500);
217
218 l_validzip VARCHAR2(1);
219 l_cust_trx_id NUMBER;
220 l_ar_return_status VARCHAR2(1);
221 l_ar_msg_count NUMBER;
222 l_ar_msg_data VARCHAR2(2000);
223 l_ship_to_zip VARCHAR2(80);
224 l_ship_to_country VARCHAR2(10);
225
226 cursor level2_trxn is
227 select ts.instrtype,
228 tc.card_data_level,
229 ts.tangibleid,
230 ts.reqtype,
231 tc.instr_owner_postalcode,
232 tc.instr_owner_country,
233 tc.shipfromzip,
234 tc.shiptozip
235 from iby_batches_all b,
236 iby_trxn_summaries_all ts,
237 iby_trxn_core tc
238 where b.mbatchid = P_MBATCH_ID
239 and b.mbatchid = ts.mbatchid
240 and ts.trxnmid = tc.trxnmid;
241
242 l_trxn_rec level2_trxn%ROWTYPE;
243
244 BEGIN
245 -- SAVEPOINT Validate_Paymentech_Batch;
246
247 -- Standard call to check for call compatibility
248 if NOT FND_API.Compatible_API_Call( l_api_version,
249 p_api_version,
250 l_api_name,
251 G_PKG_NAME ) then
252 raise FND_API.G_EXC_UNEXPECTED_ERROR;
253 end if;
254
255 if FND_API.To_Boolean(p_init_msg_list) then
256 FND_MSG_PUB.initialize;
257 end if;
258
259 x_return_status := FND_API.G_RET_STS_SUCCESS;
260
261 if PG_DEBUG in ('Y', 'C') then
262 null; -- iby.debug('some info');
263 end if;
264
265 /*
266 * Fix for bug 5717285:
267 *
268 * FDC North has a limit on batches per day
269 * submitted per security code (this limit
270 * is not per payee itself, but per security code).
271 *
272 * If a company has two payees A and B, and these
273 * two payess share the same security code X,
274 * then the first batch sent by payee A will have
275 * submission sequence id of 1, the first batch
276 * submitted by payee B should have submission
277 * sequence id of 2 (because the security code is
278 * the same).
279 */
280
281 -- Validate BATCH COUNT PER SECURITY CODE
282 SELECT
283 mpayeeid,
284 TRUNC(nvl(batchclosedate, SYSDATE))
285 INTO
286 l_payee_id,
287 l_batch_close_date
288 FROM
289 iby_batches_all
290 WHERE
291 mbatchid = P_MBATCH_ID
292 ;
293
294 /*
295 * Find security code for payee
296 */
297 SELECT
298 val.account_option_value
299 INTO
300 l_security_code
301 FROM
302 IBY_BEP_ACCT_OPT_VALS val,
303 IBY_BEPKEYS key,
304 IBY_PAYEE payee
305 WHERE
306 val.bep_account_id = key.bep_account_id
307 AND payee.payeeid = key.ownerid
308 AND val.account_option_code = 'SEC_CODE'
309 AND payee.mpayeeid = l_payee_id
310 ;
311 /* this sql statement has been replaced by the one below */
312 --select count(*)
313 -- into l_batch_count
314 -- from iby_batches_all
315 -- where mpayeeid = l_payee_id
316 -- and trunc(batchclosedate) = l_batch_close_date;
317
318 /*
319 * Select batch count based on the security code
320 * linked to the payee account (not by the
321 * payee id itself).
322 */
323
324 SELECT
325 COUNT(*)
326 INTO
327 l_batch_count
328 FROM
329 IBY_BEP_ACCT_OPT_VALS val,
330 IBY_BEPKEYS key,
331 IBY_PAYEE payee,
332 IBY_BATCHES_ALL batch
333 WHERE
334 val.account_option_code = 'SEC_CODE'
335 AND
336 val.account_option_value = l_security_code
337 AND
338 val.bep_account_id = key.bep_account_id
339 AND
340 payee.payeeid = key.ownerid
341 AND
342 batch.mpayeeid = payee.mpayeeid
343 AND
344 TRUNC(batchclosedate) = l_batch_close_date
345 ;
346
347 if l_batch_count > 9 then
348 x_return_status := FND_API.G_RET_STS_ERROR;
349 FND_MESSAGE.set_name('IBY','IBY_VALID_IS_INCORRECT');
350 FND_MESSAGE.set_token('ERR_OBJECT', 'Batch count per merchant security code');
351 FND_MSG_PUB.add;
352 FND_MSG_PUB.Count_And_Get('T',x_msg_count,x_msg_data);
353 return;
354 end if;
355
356 -- Validate TRANSACTION TYPE
357 -- Combined into postal code validation. Check for TRANSACTION TYPE
358
359 -- Validate DOCUMENT LINE COUNT for Visa purchase card
360 -- Combined into postal code validation. Check for DOCUMENT LINE COUNT
361
362 -- Validate POSTAL CODE's
363 open level2_trxn;
364 loop
365 fetch level2_trxn into l_trxn_rec;
366 exit when level2_trxn%NOTFOUND;
367
368 -- Validate TRANSACTION TYPE
369 /*
370 * Fix for bug 5857483:
371 *
372 * Added ORAPMTRETURN to the list of valid transaction
373 * types.
374 */
375 if l_trxn_rec.reqtype not in ('ORAPMTCAPTURE', 'ORAPMTCREDIT',
376 'ORAPMTRETURN') then
377 x_return_status := FND_API.G_RET_STS_ERROR;
378 FND_MESSAGE.set_name('IBY','IBY_VALID_IS_INCORRECT');
379 FND_MESSAGE.set_token('ERR_OBJECT', 'Transaction type');
380 FND_MSG_PUB.add;
381 FND_MSG_PUB.Count_And_Get('T',x_msg_count,x_msg_data);
382 return;
383
384 end if;
385
386 if l_trxn_rec.instrtype = 'CREDITCARD' then
387 validate_zip(l_trxn_rec.instr_owner_postalcode,
388 l_trxn_rec.instr_owner_country,
389 l_validzip );
390
391 if l_validzip = 'N' then
392 x_return_status := FND_API.G_RET_STS_ERROR;
393 FND_MESSAGE.set_name('IBY','IBY_VALID_IS_INCORRECT');
394 FND_MESSAGE.set_token('ERR_OBJECT', 'Postal code');
395 FND_MSG_PUB.add;
396 FND_MSG_PUB.Count_And_Get('T',x_msg_count,x_msg_data);
397 return;
398 end if;
399
400 elsif l_trxn_rec.instrtype = 'PURCHASECARD' then
401
402 -- Purchase card level 2
403 if l_trxn_rec.card_data_level = 2 then
404 -- Billing postal code
405 validate_zip(l_trxn_rec.instr_owner_postalcode,
406 l_trxn_rec.instr_owner_country,
407 l_validzip );
408
409 if l_validzip = 'N' then
410 x_return_status := FND_API.G_RET_STS_ERROR;
411 FND_MESSAGE.set_name('IBY','IBY_VALID_IS_INCORRECT');
412 FND_MESSAGE.set_token('ERR_OBJECT', 'Postal code');
413 FND_MSG_PUB.add;
414 FND_MSG_PUB.Count_And_Get('T',x_msg_count,x_msg_data);
415 return;
416 end if;
417
418 /*
419 -- Shipping origin postal code
420 validate_zip(l_trxn_rec.shipfromzip,
421 l_trxn_rec.shipfromcountry, -- this does NOT exist now
422 l_validzip );
423
424 if l_validzip = 'N' then
425 x_return_status := FND_API.G_RET_STS_ERROR;
426 FND_MESSAGE.set_name('IBY','IBY_VALID_IS_INCORRECT');
427 FND_MESSAGE.set_token('ERR_OBJECT', 'Postal code');
428 FND_MSG_PUB.add;
429 FND_MSG_PUB.Count_And_Get('T',x_msg_count,x_msg_data);
430 return;
431 end if;
432
433 -- Shipping destination postal code
434 validate_zip(l_trxn_rec.shiptozip,
435 l_trxn_rec.shiptocountry, -- this does NOT exist now
436 l_validzip );
437
438 if l_validzip = 'N' then
439 x_return_status := FND_API.G_RET_STS_ERROR;
440 FND_MESSAGE.set_name('IBY','IBY_VALID_IS_INCORRECT');
441 FND_MESSAGE.set_token('ERR_OBJECT', 'Postal code');
442 FND_MSG_PUB.add;
443 FND_MSG_PUB.Count_And_Get('T',x_msg_count,x_msg_data);
444 return;
445 end if;
446 */
447
448 -- Purchase card level 3
449 elsif l_trxn_rec.card_data_level = 3 then
450 -- Billing postal code
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 -- Shipping-to postal code
465 iby_ar_utils.call_get_payment_info(l_trxn_rec.tangibleid,
466 l_cust_trx_id,
467 l_ar_return_status,
468 x_msg_count,
469 x_msg_data);
470 /*
471 if l_cust_trx_id = 0 then
472 x_return_status := FND_API.G_RET_STS_ERROR;
473 FND_MSG_PUB.Count_And_Get('T',x_msg_count,x_msg_data);
474 return;
475 else
476 begin
477 -- Validate DOCUMENT LINE COUNT for Visa purchase card
478 select count(*)
479 into l_doc_line_count
480 from ar_invoice_lines_v
481 where customer_trx_id = l_cust_trx_id;
482
483 if l_doc_line_count > 98 then
484 x_return_status := FND_API.G_RET_STS_ERROR;
485 FND_MESSAGE.set_name('IBY','IBY_VALID_IS_INCORRECT');
486 FND_MESSAGE.set_token('ERR_OBJECT', 'Invoice line count for Visa purchase card');
487 FND_MSG_PUB.add;
488 FND_MSG_PUB.Count_And_Get('T',x_msg_count,x_msg_data);
489 return;
490 end if;
491
492 select hl.postal_code, hl.country
493 into l_ship_to_zip, l_ship_to_country
494 from hz_locations hl,
495 hz_party_sites hps,
496 hz_cust_acct_sites_all hcasa,
497 hz_cust_site_uses_all hcsua,
498 ar_invoice_header_v arihv
499 where hl.location_id = hps.location_id and
500 hps.party_site_id = hcasa.party_site_id and
501 hcasa.cust_acct_site_id = hcsua.cust_acct_site_id and
502 hcsua.site_use_id = arihv.ship_to_site_use_id and
503 arihv.customer_trx_id = l_cust_trx_id;
504 exception
505 when others then
506 l_ship_to_zip := null;
507 l_ship_to_country := null;
508 end;
509
510 validate_zip(l_ship_to_zip,
511 l_ship_to_country,
512 l_validzip );
513
514 if l_validzip = 'N' then
515 x_return_status := FND_API.G_RET_STS_ERROR;
516 FND_MESSAGE.set_name('IBY','IBY_VALID_IS_INCORRECT');
517 FND_MESSAGE.set_token('ERR_OBJECT', 'Postal code');
518 FND_MSG_PUB.add;
519 FND_MSG_PUB.Count_And_Get('T',x_msg_count,x_msg_data);
520 return;
521 end if;
522
523 end; -- l_cust_trx_id has a value
524 */
525 -- Shipping-from postal code
526 -- This validation is NOT added, as shipfromzip is never populated
527
528 else
529 null;
530 end if; -- card_data_level is 2, 3 or else
531
532 else
533 null;
534 end if; -- instrtype is CREDITCARD, PURCHASECARD, etc.
535
536 end loop;
537 close level2_trxn;
538
539 -- Bug 4243738: Added
540 if x_return_status = FND_API.G_RET_STS_SUCCESS then
541 update iby_batches_all
542 set SENTCOUNTERDAILY = l_batch_count
543 where mbatchid = P_MBATCH_ID;
544 end if;
545
546 FND_MSG_PUB.Count_And_Get('T',x_msg_count,x_msg_data);
547
548 END; -- procedure Validate_FDCNorth_Batch
549
550 -- Validate Paymentech credit card batch
551 PROCEDURE Validate_Paymentech_Batch (
552 p_api_version IN NUMBER,
553 p_init_msg_list IN VARCHAR2 default FND_API.G_FALSE,
554 P_MBATCH_ID IN NUMBER,
555 x_return_status OUT NOCOPY VARCHAR2,
556 x_msg_count OUT NOCOPY NUMBER,
557 x_msg_data OUT NOCOPY VARCHAR2
558 ) IS
559 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Paymentech_Batch';
560 l_api_version CONSTANT NUMBER := 1.0;
561
562 l_batchid_size NUMBER;
563
564 l_validzip VARCHAR2(1);
565 l_cust_trx_id NUMBER;
566 l_ar_return_status VARCHAR2(1);
567 l_ar_msg_count NUMBER;
568 l_ar_msg_data VARCHAR2(2000);
569 l_ship_to_zip VARCHAR2(80);
570 l_ship_to_country VARCHAR2(10);
571
572 cursor level2_trxn is
573 select ts.instrtype,
574 tc.card_data_level,
575 ts.tangibleid,
576 tc.instr_owner_postalcode,
577 tc.instr_owner_country,
578 tc.shipfromzip,
579 tc.shiptozip
580 from iby_batches_all b,
581 iby_trxn_summaries_all ts,
582 iby_trxn_core tc
583 where b.mbatchid = P_MBATCH_ID
584 and b.mbatchid = ts.mbatchid
585 and ts.trxnmid = tc.trxnmid;
586
587 l_trxn_rec level2_trxn%ROWTYPE;
588
589 BEGIN
590 -- SAVEPOINT Validate_Paymentech_Batch;
591
592 -- Standard call to check for call compatibility
593 if NOT FND_API.Compatible_API_Call( l_api_version,
594 p_api_version,
595 l_api_name,
596 G_PKG_NAME ) then
597 raise FND_API.G_EXC_UNEXPECTED_ERROR;
598 end if;
599
600 if FND_API.To_Boolean(p_init_msg_list) then
601 FND_MSG_PUB.initialize;
602 end if;
603
604 x_return_status := FND_API.G_RET_STS_SUCCESS;
605
606 if PG_DEBUG in ('Y', 'C') then
607 null; -- iby.debug('some info');
608 end if;
609
610 -- Validate BATCH NAME
611 select length(batchid)
612 into l_batchid_size
613 from iby_batches_all
614 where mbatchid = P_MBATCH_ID;
615
616 if l_batchid_size > 8 then
617 x_return_status := FND_API.G_RET_STS_ERROR;
618 FND_MESSAGE.set_name('IBY','IBY_VALID_IS_INCORRECT');
619 FND_MESSAGE.set_token('ERR_OBJECT', 'Batch name');
620 FND_MSG_PUB.add;
621 FND_MSG_PUB.Count_And_Get('T',x_msg_count,x_msg_data);
622 return;
623 end if;
624
625 -- Validate POSTAL CODE's
626 open level2_trxn;
627 loop
628 fetch level2_trxn into l_trxn_rec;
629 exit when level2_trxn%NOTFOUND;
630
631 if l_trxn_rec.instrtype = 'CREDITCARD' then
632 validate_zip(l_trxn_rec.instr_owner_postalcode,
633 l_trxn_rec.instr_owner_country,
634 l_validzip );
635
636 if l_validzip = 'N' then
637 x_return_status := FND_API.G_RET_STS_ERROR;
638 FND_MESSAGE.set_name('IBY','IBY_VALID_IS_INCORRECT');
639 FND_MESSAGE.set_token('ERR_OBJECT', 'Postal code');
640 FND_MSG_PUB.add;
641 FND_MSG_PUB.Count_And_Get('T',x_msg_count,x_msg_data);
642 return;
643 end if;
644
645 elsif l_trxn_rec.instrtype = 'PURCHASECARD' then
646
647 -- Purchase card level 2
648 if l_trxn_rec.card_data_level = 2 then
649 -- Billing postal code
650 validate_zip(l_trxn_rec.instr_owner_postalcode,
651 l_trxn_rec.instr_owner_country,
652 l_validzip );
653
654 if l_validzip = 'N' then
655 x_return_status := FND_API.G_RET_STS_ERROR;
656 FND_MESSAGE.set_name('IBY','IBY_VALID_IS_INCORRECT');
657 FND_MESSAGE.set_token('ERR_OBJECT', 'Postal code');
658 FND_MSG_PUB.add;
659 FND_MSG_PUB.Count_And_Get('T',x_msg_count,x_msg_data);
660 return;
661 end if;
662
663 /*
664 -- Shipping origin postal code
665 validate_zip(l_trxn_rec.shipfromzip,
666 l_trxn_rec.shipfromcountry, -- this does NOT exist now
667 l_validzip );
668
669 if l_validzip = 'N' then
670 x_return_status := FND_API.G_RET_STS_ERROR;
671 FND_MESSAGE.set_name('IBY','IBY_VALID_IS_INCORRECT');
672 FND_MESSAGE.set_token('ERR_OBJECT', 'Postal code');
673 FND_MSG_PUB.add;
674 FND_MSG_PUB.Count_And_Get('T',x_msg_count,x_msg_data);
675 return;
676 end if;
677
678 -- Shipping destination postal code
679 validate_zip(l_trxn_rec.shiptozip,
680 l_trxn_rec.shiptocountry, -- this does NOT exist now
681 l_validzip );
682
683 if l_validzip = 'N' then
684 x_return_status := FND_API.G_RET_STS_ERROR;
685 FND_MESSAGE.set_name('IBY','IBY_VALID_IS_INCORRECT');
686 FND_MESSAGE.set_token('ERR_OBJECT', 'Postal code');
687 FND_MSG_PUB.add;
688 FND_MSG_PUB.Count_And_Get('T',x_msg_count,x_msg_data);
689 return;
690 end if;
691 */
692
693 -- Purchase card level 3
694 elsif l_trxn_rec.card_data_level = 3 then
695 -- Billing postal code
696 validate_zip(l_trxn_rec.instr_owner_postalcode,
697 l_trxn_rec.instr_owner_country,
698 l_validzip );
699
700 if l_validzip = 'N' then
701 x_return_status := FND_API.G_RET_STS_ERROR;
702 FND_MESSAGE.set_name('IBY','IBY_VALID_IS_INCORRECT');
703 FND_MESSAGE.set_token('ERR_OBJECT', 'Postal code');
704 FND_MSG_PUB.add;
705 FND_MSG_PUB.Count_And_Get('T',x_msg_count,x_msg_data);
706 return;
707 end if;
708
709 -- Shipping-to postal code
710 iby_ar_utils.call_get_payment_info(l_trxn_rec.tangibleid,
711 l_cust_trx_id,
712 l_ar_return_status,
713 x_msg_count,
714 x_msg_data);
715 /*
716 if l_cust_trx_id = 0 then
717 x_return_status := FND_API.G_RET_STS_ERROR;
718 FND_MSG_PUB.Count_And_Get('T',x_msg_count,x_msg_data);
719 return;
720 else
721 begin
722 select hl.postal_code, hl.country
723 into l_ship_to_zip, l_ship_to_country
724 from hz_locations hl,
725 hz_party_sites hps,
726 hz_cust_acct_sites_all hcasa,
727 hz_cust_site_uses_all hcsua,
728 ar_invoice_header_v arihv
729 where hl.location_id = hps.location_id and
730 hps.party_site_id = hcasa.party_site_id and
731 hcasa.cust_acct_site_id = hcsua.cust_acct_site_id and
732 hcsua.site_use_id = arihv.ship_to_site_use_id and
733 arihv.customer_trx_id = l_cust_trx_id;
734 exception
735 when others then
736 l_ship_to_zip := null;
737 l_ship_to_country := null;
738 end;
739
740 validate_zip(l_ship_to_zip,
741 l_ship_to_country,
742 l_validzip );
743
744 if l_validzip = 'N' then
745 x_return_status := FND_API.G_RET_STS_ERROR;
746 FND_MESSAGE.set_name('IBY','IBY_VALID_IS_INCORRECT');
747 FND_MESSAGE.set_token('ERR_OBJECT', 'Postal code');
748 FND_MSG_PUB.add;
749 FND_MSG_PUB.Count_And_Get('T',x_msg_count,x_msg_data);
750 return;
751 end if;
752
753
754 end; -- l_cust_trx_id has a value
755 */
756 -- Shipping-from postal code
757 -- This validation is NOT added, as shipfromzip is never populated
758
759 else
760 null;
761 end if; -- card_data_level is 2, 3 or else
762
763 else
764 null;
765 end if; -- instrtype is CREDITCARD, PURCHASECARD, etc.
766
767 end loop;
768 close level2_trxn;
769
770 FND_MSG_PUB.Count_And_Get('T',x_msg_count,x_msg_data);
771
772 END; -- procedure Validate_Paymentech_Batch
773
774 END IBY_FNDCPT_VLD_PUB;