[Home] [Help]
PACKAGE BODY: APPS.IBY_PAYMENT_ADAPTER_PUB
Source
1 PACKAGE BODY IBY_PAYMENT_ADAPTER_PUB AS
2 /* $Header: ibyppadb.pls 120.20.12010000.3 2008/08/29 06:03:15 lmallick ship $ */
3
4
5 -- *** Declaring global datatypes and variables ***
6 G_PKG_NAME CONSTANT VARCHAR2(30) := 'IBY_PAYMENT_ADAPTER_PUB';
7
8 g_validation_level CONSTANT NUMBER := FND_API.G_VALID_LEVEL_FULL;
9
10
11 ------------------------------------------------------------------------------
12 /* UTILITY PROCEDURE #1: UNPACK_RESULTS_URL
13 PARSER Procedure to take in given l_string in html file format,
14 parse l_string, and store the Name-Value pairs in l_names and l_values.
15 For example, if OapfPrice Name-Value pairs exist in l_string, it would be
16 stored as l_names(i) := 'OapfPrice' and l_values(i) := '17.00'.
17
18 NOTE: This procedure logic is exactly similar to the iPayment 3i version
19 of procedure with minor enhancements and bug fixes.
20 */
21 ------------------------------------------------------------------------------
22 PROCEDURE unpack_results_url(p_string IN VARCHAR2,
23 x_names OUT NOCOPY v240_tbl_type,
24 x_values OUT NOCOPY v240_tbl_type,
25 x_status OUT NOCOPY NUMBER,
26 x_errcode OUT NOCOPY NUMBER,
27 x_errmessage OUT NOCOPY VARCHAR2
28 ) IS
29
30 l_length NUMBER(15) := LENGTH(p_string) + 1;
31 l_count NUMBER(15) := 0;
32 l_index NUMBER(15) := 1;
33 l_char VARCHAR2(1) := '';
34 l_word VARCHAR2(2400) := '';
35 l_name BOOLEAN := TRUE;
36 l_local_nls VARCHAR2(200);
37 l_remote_nls VARCHAR2(200);
38 BEGIN
39
40 iby_debug_pub.add(debug_msg => 'Enter', debug_level => FND_LOG.LEVEL_PROCEDURE,
41 module => G_DEBUG_MODULE || '.unpack_results');
42
43 -- Initialize status, errcode, errmessage to Success.
44 x_status := 0;
45 x_errcode := 0;
46 x_errmessage := 'Success';
47
48 l_local_nls := iby_utility_pvt.get_local_nls();
49
50 -- verify what HTTP response format is returned by the server
51 -- NOTE: Since ECServlet is not supposed to return in this format,
52 -- this condition should not be encountered.
53 l_count := instr(p_string,'</H2>');
54 IF l_count > 0 THEN
55 l_count := l_count + 5;
56 END IF;
57
58 --Fixing Bug from OM: 1104438
59 --Suggested improvement to this: Search for the first alphanumeric
60 --character [a-zA-Z0-9] encountered in this string.set l_count to that position.
61 l_count := INSTR(p_string, 'Oapf');
62 --End of Bug Fix 1104438
63
64 WHILE l_count < l_length LOOP
65 IF (l_name) AND (substr(p_string,l_count,1) = ':') THEN
66 x_names(l_index) := substr( (ltrim(rtrim(l_word))), 1, 240 );
67 l_name := FALSE;
68 l_word := '';
69 l_count := l_count + 1;
70 ELSIF (l_name) THEN
71 l_char := substr(p_string,l_count,1);
72 l_word := l_word||l_char;
73 l_count := l_count + 1;
74 ELSIF upper(substr(p_string,l_count,4)) = '<BR>' THEN
75 x_values(l_index) := substr( (ltrim(rtrim(l_word))), 1, 240 );
76 --
77 -- remember the NLS Lang parameter for below decoding
78 IF (x_names(l_index) = 'OapfNlsLang') THEN
79 l_remote_nls := x_values(l_index);
80 END IF;
81
82 l_name := TRUE;
83 l_word := '';
84 l_index := l_index + 1;
85 l_count := l_count + 4;
86 ELSE
87 l_char := substr(p_string,l_count,1);
88 l_word := l_word||l_char;
89 l_count := l_count + 1;
90 END IF;
91
92 /*--Note: Can Add this to extra ensure that
93 --additional white spaces get trimmed.
94 x_names(l_count) := LTRIM(RTRIM(x_names(l_count) ));
95 x_values(l_count) := LTRIM(RTRIM(x_values(l_count) )); */
96
97 END LOOP;
98
99 -- do URL decoding if on the output values if possible
100 --
101
102 --dbms_output.put_line('unpack::local nls: ' || l_local_nls);
103 --dbms_output.put_line('unpack::remote nls: ' || l_remote_nls);
104
105 /*
106 IF ((l_remote_nls IS NOT NULL) AND (l_local_nls IS NOT NULL)) THEN
107 FOR i in 1..x_values.COUNT LOOP
108 x_values(i) := iby_netutils_pvt.decode_url_chars(x_values(i),l_local_nls,l_remote_nls);
109 END LOOP;
110 END IF;
111 */
112
113 iby_debug_pub.add(debug_msg => 'Exit', debug_level => FND_LOG.LEVEL_PROCEDURE,
114 module => G_DEBUG_MODULE || '.unpack_results');
115
116 EXCEPTION
117 WHEN OTHERS THEN
118 /* Return a status of -1 to the calling API to indicate
119 errors in unpacking html body results. */
120 --dbms_output.put_line('error in unpacking procedure');
121 x_status := -1;
122 x_errcode := to_char(SQLCODE);
123 x_errmessage := SQLERRM;
124 END unpack_results_url;
125
126
127 /* UTILITY PROCEDURE #1.4: TRXN_STATUS_SUCCESS
128
129 Returns true if and only if the given transaction
130 status indicates a success.
131
132 */
133 FUNCTION trxn_status_success( p_trxn_status NUMBER )
134 RETURN BOOLEAN
135 IS
136 BEGIN
137 IF (p_trxn_status IS NULL) THEN
138 iby_debug_pub.add(debug_msg => 'ECApp servlet trxn status is NULL!',
139 debug_level => FND_LOG.LEVEL_UNEXPECTED,
140 module => G_DEBUG_MODULE || '.trxn_status_success');
141 RETURN FALSE;
142 ELSIF ((IBY_PAYMENT_ADAPTER_PUB.C_TRXN_STATUS_SUCCESS = p_trxn_status) OR
143 (IBY_PAYMENT_ADAPTER_PUB.C_TRXN_STATUS_INFO = p_trxn_status) OR
144 (IBY_PAYMENT_ADAPTER_PUB.C_TRXN_STATUS_WARNING = p_trxn_status)
145 )
146 THEN
147 RETURN TRUE;
148 ELSE
149 RETURN FALSE;
150 END IF;
151 END trxn_status_success;
152
153 --------------------------------------------------------------------------------------------
154 /* UTILITY PROCEDURE #2: CHECK_MANDATORY
155 Procedure to take in given URL string: p_url,
156 name-value pair strings: p_name, p_value
157 Check if p_value is NOT NULL. If not NULL, then append p_name=p_value to p_url.
158 If p_value is NULL, then an exception is raised and passed to the
159 calling program.
160 NOTE: This procedure checks only for MANDATORY INPUT parameters.
161 Decision on which should be mandatory is decided by the business logic.
162
163 NLS ARGS (used to encode the parameters that go into the URL):
164
165 p_local_nls - the NLS value of the local system (as pulled
166 from DB)
167 p_remote_nls - the NLS value for the remote system
168
169 */
170 --------------------------------------------------------------------------------------------
171 PROCEDURE check_mandatory (p_name IN VARCHAR2,
172 p_value IN VARCHAR2,
173 p_url IN OUT NOCOPY VARCHAR2,
174 p_local_nls IN VARCHAR2 DEFAULT NULL,
175 p_remote_nls IN VARCHAR2 DEFAULT NULL
176 ) IS
177 l_url VARCHAR2(2000) := p_url;
178 BEGIN
179 /* Logic:
180 1. Check if value is null. if null, then raise exception to pass to ECApp;
181 3. If not null, then append to URL.
182 */
183
184 IF (p_value is NULL) THEN
185 --Note: Reused an existing IBY message and token.
186 FND_MESSAGE.SET_NAME('IBY', 'IBY_0004');
187 FND_MESSAGE.SET_TOKEN('FIELD', p_name);
188 FND_MSG_PUB.Add;
189
190 -- jleybovi [10/24/00]
191 --
192 -- should return an expected error exception here as missing input is
193 -- considered a "normal" error
194 --
195 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
196 RAISE FND_API.G_EXC_ERROR;
197 ELSE
198 --Append this <name>=<value> to the input URL
199 p_url := p_url||p_name||'='||
200 iby_netutils_pvt.escape_url_chars(p_value,p_local_nls,p_remote_nls)||'&';
201 END IF;
202
203 -- ??? who installed the exception catch below???
204 -- keep it for the time being as it allows a better error message, code
205 -- to be returned than by aborting within PL/SQL
206 -- [jlebovi 11/29/2001]
207 EXCEPTION
208 /*
209 WHEN iby_netutils_pvt.encoding_error THEN
210 --
211 -- catch any of the conversion errors that may
212 -- have occured here
213 --
214 FND_MESSAGE.SET_NAME('IBY', 'IBY_204011');
215 FND_MESSAGE.SET_TOKEN('FIELD', 'NLS_LANG');
216 FND_MSG_PUB.Add;
217 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
218 */
219 WHEN OTHERS THEN
220 p_url := l_url;
221
222 END check_mandatory;
223 --------------------------------------------------------------------------------------------
224 /* UTILITY PROCEDURE #3: CHECK_OPTIONAL
225 Procedure to take in given URL string: p_url,
226 name-value pair strings: p_name, p_value
227 Check if p_value is NOT NULL, If NOT NULL, append p_name=p_value to p_url.
228 Otherwise, if p_value is NULL, then p_url is unchanged.
229 NOTE: This procedure checks only for OPTIONAL INPUT parameters and does not
230 validate MANDATORY INPUT parameters.
231
232 NLS ARGS (used to encode the parameters that go into the URL):
233
234 p_local_nls - the NLS value of the local system (as pulled
235 from DB)
236 p_remote_nls - the NLS value for the remote system
237
238 */
239 --------------------------------------------------------------------------------------------
240 PROCEDURE check_optional (p_name IN VARCHAR2,
241 p_value IN VARCHAR2,
242 p_url IN OUT NOCOPY VARCHAR2,
243 p_local_nls IN VARCHAR2 DEFAULT NULL,
244 p_remote_nls IN VARCHAR2 DEFAULT NULL
245 ) IS
246 l_url VARCHAR2(2000) := p_url;
247 BEGIN
248
249 /* Logic:
250 1. check value if null.
251 if null then don't do anything.
252 2. If not null, then append to URL.
253 */
254
255 IF (p_value IS NULL) THEN
256 p_url := l_url;
257 ELSE
258 --Append this <name>=<value> to the input URL
259 p_url := p_url||p_name||'='||
260 iby_netutils_pvt.escape_url_chars(p_value,p_local_nls,p_remote_nls)||'&';
261 END IF;
262
263 EXCEPTION
264 /*
265 WHEN iby_netutils_pvt.encoding_error THEN
266 --
267 -- catch any of the conversion errors that may
268 -- have occured here
269 --
270 FND_MESSAGE.SET_NAME('IBY', 'IBY_204011');
271 FND_MESSAGE.SET_TOKEN('FIELD', 'NLS_LANG');
272 FND_MSG_PUB.Add;
273 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
274 */
275 WHEN OTHERS THEN
276 p_url := l_url;
277
278 END check_optional;
279 --------------------------------------------------------------------------------------------
280 /* UTILITY PROCEDURE #4: SHOW_INPUT_DEBUG
281 Procedure to take in given Final Input URL string (p_string)
282 and print out the string in chunks.
283 */
284 --------------------------------------------------------------------------------------------
285 PROCEDURE show_input_debug (p_string IN VARCHAR2) IS
286 j NUMBER := 0;
287 BEGIN
288 -- Use For Debugging
289 --dbms_output.put_line('---------------------');
290 --dbms_output.put_line('FINAL INPUT URL IS : ');
291 --dbms_output.put_line('---------------------');
292 j := 0;
293 WHILE ( j < (length(p_string) + 1) )
294 LOOP
295 --dbms_output.put(' ');
296 --dbms_output.put(substr(p_string,1+(90*j),90) );
297 --dbms_output.new_line;
298 j := j+1;
299 END LOOP;
300 --dbms_output.put_line('---------------------');
301
302 EXCEPTION
303 WHEN OTHERS THEN
304 --dbms_output.put_line('Error in displaying debug strings output: procedure show_input_debug');
305 NULL;
306 END show_input_debug;
307 --------------------------------------------------------------------------------------------
308 /* UTILITY PROCEDURE #5: SHOW_OUTPUT_DEBUG
309 Procedure to take in given HTML body response string (p_string)
310 and print out the string in chunks.
311 */
312 --------------------------------------------------------------------------------------------
313 PROCEDURE show_output_debug (p_string IN VARCHAR2) IS
314 j NUMBER := 0;
315 BEGIN
316 -- Use For Debugging
317 -- dbms_output.put_line('---------------------');
318 -- dbms_output.put_line('HTML BODY RESPONSE STRING IS : ');
319 -- dbms_output.put_line('---------------------');
320 j := 0;
321 WHILE ( j < (length(p_string) + 1) )
322 LOOP
323 -- dbms_output.put(' ');
324 -- dbms_output.put(substr(p_string,1+(90*j),90) );
325 -- dbms_output.new_line;
326 j := j+1;
327 END LOOP;
328 -- dbms_output.put_line('---------------------');
329
330 EXCEPTION
331 WHEN OTHERS THEN
332 --dbms_output.put_line('Error in displaying input URL output: Procedure show_output_debug');
333 NULL;
334 END show_output_debug;
335 --------------------------------------------------------------------------------------------
336 /* UTILITY PROCEDURE #5: SHOW_OUTPUT_DEBUG
337 Procedure to take in given HTML body response string (p_string)
338 and print out long strings in smaller chunks to
339 bypass buffer size constraints.
340 */
341 --------------------------------------------------------------------------------------------
342 PROCEDURE show_table_debug (p_table IN v240_tbl_type) IS
343 i NUMBER := 0;
344 BEGIN
345 --Use For Debugging
346 --dbms_output.put_line('Table Count = '||to_char(p_table.COUNT) );
347 FOR i IN 1..p_table.COUNT
348 LOOP
349 --dbms_output.put(' ');
350 --dbms_output.put_line('Element.'||to_char(i)||' = '||p_table(i) );
351 NULL;
352 END LOOP;
353 --dbms_output.put_line('---------------------');
354 EXCEPTION
355 WHEN OTHERS THEN
356 --dbms_output.put_line('Error in displaying table output: Procedure show_table_debug');
357 NULL;
358 END show_table_debug;
359 --------------------------------------------------------------------------------------------
360 /* UTILITY FUNCTION #1: IS_RISKINFO_REC_MISSING
361 Function to check if the optional/missing parameter RiskInfo_rec
362 has been passed or not in the OraPmtReq API.
363 */
364 --------------------------------------------------------------------------------------------
365 FUNCTION Is_RiskInfo_rec_Missing (riskinfo_rec IN Riskinfo_rec_type)
366 RETURN BOOLEAN IS
367 BEGIN
368 IF (riskinfo_rec.Formula_Name <> FND_API.G_MISS_CHAR) THEN
369 RETURN FALSE;
370 ELSIF (riskinfo_rec.ShipToBillTo_Flag <> FND_API.G_MISS_CHAR) THEN
371 RETURN FALSE;
372 ELSIF (riskinfo_rec.Time_Of_Purchase <> FND_API.G_MISS_CHAR) THEN
373 RETURN FALSE;
374 ELSIF (riskinfo_rec.Customer_Acct_Num <> FND_API.G_MISS_CHAR) THEN
375 RETURN FALSE;
376 -- ELSIF (riskinfo_rec.Org_ID <> FND_API.G_MISS_NUM) THEN
377 -- RETURN FALSE;
378 ELSE
379 RETURN TRUE;
380 END IF;
381 END Is_RiskInfo_rec_Missing;
382
383
384 --------------------------------------------------------------------------------------------
385 /* UTILITY FUNCTION #1: cipher_url
386 Removes sensitive data (e.g. credit card #'s) from the
387 given ECAPP URL string before it is written to log.
388 */
389 --------------------------------------------------------------------------------------------
390 FUNCTION cipher_url (p_url IN VARCHAR2)
391 RETURN VARCHAR
392 IS
393 l_name_start NUMBER;
394 l_val_start NUMBER;
395 l_val_end NUMBER;
396 BEGIN
397 l_name_start := INSTR(p_url,'OapfPmtInstrID');
398 IF (l_name_start <1) THEN
399 RETURN p_url;
400 END IF;
401
402 l_val_start := INSTR(p_url,'=',l_name_start);
403 IF (l_val_start <1) THEN
404 RETURN p_url;
405 END IF;
406
407 l_val_end := instr(p_url,'&',l_val_start);
408 IF (l_val_end <1) THEN
409 l_val_end := length(p_url);
410 END IF;
411
412 RETURN substr(p_url,1,l_val_start) || substr(p_url,l_val_end);
413 END cipher_url;
414
415 --------------------------------------------------------------------------------------------
416 /* UTILITY PROCEDURE #6: GET_BASEURL
417 Procedure to retrieve the iPayment ECAPP BASE URL
418 */
419 --------------------------------------------------------------------------------------------
420 PROCEDURE get_baseurl(x_baseurl OUT NOCOPY VARCHAR2)
421 IS
422 -- Local variable to hold the property name for the URL.
423 p_temp_var VARCHAR2(2);
424
425 BEGIN
426
427 iby_debug_pub.add(debug_msg => 'Enter', debug_level => FND_LOG.LEVEL_PROCEDURE,
428 module => G_DEBUG_MODULE || '.get_baseurl');
429
430 iby_utility_pvt.get_property(iby_payment_adapter_pub.C_ECAPP_URL_PROP_NAME,x_baseurl);
431
432 --dbms_output.put_line('x_return_status = '|| x_return_status);
433 --Raising Exception to handle errors if value is missing
434 IF ((x_baseurl IS NULL) OR (trim(x_baseurl) = '')) THEN
435 FND_MESSAGE.SET_NAME('IBY', 'IBY_204406');
436 FND_MSG_PUB.Add;
437 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
438 END IF;
439
440 --appending '?' if not already present in the url
441 p_temp_var := SUBSTR(x_baseurl, -1);
442
443 IF( p_temp_var <> '?' ) THEN
444 x_baseurl := x_baseurl || '?';
445 END IF;
446
447 iby_debug_pub.add(debug_msg => 'base url=' || x_baseurl,
448 debug_level => FND_LOG.LEVEL_STATEMENT,
449 module => G_DEBUG_MODULE || '.get_baseurl');
450
451 iby_debug_pub.add(debug_msg => 'Exit',
452 debug_level => FND_LOG.LEVEL_PROCEDURE,
453 module => G_DEBUG_MODULE || '.get_baseurl');
454
455 END get_baseurl;
456
457
458 --------------------------------------------------------------------------------------------
459 /* UTILITY PROCEDURE #7: SEND_REQUEST
460 Procedure to call HTTP_UTIL.SEND_REQUEST and handle exceptions thrown by it
461 */
462 --------------------------------------------------------------------------------------------
463 PROCEDURE send_request(x_url IN OUT NOCOPY VARCHAR2,
464 x_htmldoc OUT NOCOPY VARCHAR2
465 ) IS
466 l_wallet_location VARCHAR2(3000);
467 l_wallet_password VARCHAR2(200);
468 l_hash_string VARCHAR2(30000) :='';
469 l_sec_cred NUMBER;
470 l_index NUMBER := 0;
471 l_post_body VARCHAR2(30000);
472
473 l_timeout VARCHAR2(200);
474 l_num_val NUMBER;
475
476 BEGIN
477 -- Send http request to the payment server.
478 iby_debug_pub.add(debug_msg => 'Enter',
479 debug_level => FND_LOG.LEVEL_PROCEDURE,
480 module => G_DEBUG_MODULE || '.send_request');
481
482 iby_utility_pvt.get_property(iby_security_pkg.C_SHARED_WALLET_LOC_PROP_NAME,l_wallet_location);
483
484 /*
485 * Fix for bug 6690545:
486 *
487 * Get the network timeout setting from the profile
488 * option 'IBY: Network Timeout'.
489 *
490 * If this profile option is set, we will use this
491 * value to set the UTL_HTTP timeout. If not set
492 * the UTL_HTTP call will timeout after 60 seconds
493 * by default.
494 */
495 iby_utility_pvt.get_property('IBY_NETWORK_TIMEOUT', l_timeout);
496
497 iby_debug_pub.add(debug_msg => 'Timeout setting at profile level = '
498 || l_timeout,
499 debug_level => FND_LOG.LEVEL_STATEMENT,
500 module => G_DEBUG_MODULE || '.send_request');
501
502 BEGIN
503
504 IF (l_timeout IS NOT NULL) THEN
505
506 /* convert timeout setting to a numeric value */
507 l_num_val := TO_NUMBER(l_timeout);
508
509 /* timeout is in milliseconds; convert this to seconds */
510 l_num_val := l_num_val / 1000;
511
512 /*
513 * If the user sets the timeout to less than 60 seconds
514 * do not honor it (it will simply cause more frequent
515 * trxn failures). Default the timeout back to 60 seconds.
516 */
517 IF (l_num_val < 60) THEN
518 l_num_val := 60;
519 END IF;
520
521 UTL_HTTP.SET_TRANSFER_TIMEOUT(l_num_val);
522
523 iby_debug_pub.add(debug_msg => 'UTL_HTTP timeout set to '
524 || l_num_val || ' seconds.',
525 debug_level => FND_LOG.LEVEL_STATEMENT,
526 module => G_DEBUG_MODULE || '.send_request');
527
528 END IF;
529
530 EXCEPTION
531 WHEN OTHERS THEN
532
533 /*
534 * It's not a big deal if we cannot set the
535 * timeout explicitly. The timeout will be
536 * defaulted to 60 seconds.
537 *
538 * So, swallow the exception and move on.
539 */
540 iby_debug_pub.add(debug_msg => 'Ignoring timeout setting',
541 debug_level => FND_LOG.LEVEL_STATEMENT,
542 module => G_DEBUG_MODULE || '.send_request');
543
544 END;
545
546 iby_debug_pub.add(debug_msg => 'Continuing after setting timeout ..',
547 debug_level => FND_LOG.LEVEL_STATEMENT,
548 module => G_DEBUG_MODULE || '.send_request');
549
550 --
551 -- using a SSO password-less wallet
552 l_wallet_password := NULL;
553
554 IF (NOT (TRIM(l_wallet_location) IS NULL)) THEN
555 l_wallet_location := iby_netutils_pvt.path_to_url(l_wallet_location);
556 END IF;
557
558 iby_debug_pub.add(debug_msg => 'Input url = '|| cipher_url(x_url),
559 debug_level => FND_LOG.LEVEL_STATEMENT,
560 module => G_DEBUG_MODULE || '.send_request');
561
562 --show_input_debug(cipher_url(x_url));
563
564 l_index := instr(x_url,'?');
565 IF ( (l_index>0) AND (l_index<length(x_url)) ) THEN
566 l_hash_string := substr(x_url,l_index+1);
567 x_url := x_url || '&';
568 END IF;
569
570 iby_security_pkg.store_credential(l_hash_string,l_sec_cred);
571 x_url := x_url || 'OapfSecurityToken=' || l_sec_cred;
572
573 l_post_body := SUBSTR(x_url,l_index+1,length(x_url));
574 l_post_body := RTRIM(l_post_body,'&');
575
576 IBY_NETUTILS_PVT.POST_REQUEST
577 ( SUBSTR(x_url,1,l_index-1), l_post_body, x_htmldoc );
578
579
580 iby_debug_pub.add(debug_msg => 'Exit',
581 debug_level => FND_LOG.LEVEL_PROCEDURE,
582 module => G_DEBUG_MODULE || '.send_request');
583
584 --dbms_output.put_line('Input url = '|| x_url);
585
586 EXCEPTION
587 WHEN UTL_HTTP.REQUEST_FAILED THEN
588
589 --FND_MSG_PUB.Add_Exc_Msg(p_error_text => SQLCODE||':-:'||SQLERRM);
590 FND_MESSAGE.SET_NAME('IBY', 'IBY_0001');
591 FND_MSG_PUB.Add;
592 -- x_return_status := 3 ;
593 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
594
595 END send_request;
596 --------------------------------------------------------------------------------------------
597 ---*** APIS START BELOW ---***
598 --------------------------------------------------------------------------------------------
599 --1. OraPmtReq
600 -- Start of comments
601 -- API name : OraPmtReq
602 -- Type : Public
603 -- Pre-reqs : None
604 -- Function : Handles new Payment requests from E-Commerce applications.
605 -- Parameters :
606 -- IN : p_api_version IN NUMBER Required
607 -- p_init_msg_list IN VARCHAR2 Optional
608 -- p_commit IN VARCHAR2 Optional
609 -- p_validation_level IN NUMBER Optional
610 -- p_ecapp_id IN NUMBER Required
611 -- p_payee_id_rec IN Payee_rec_type Required
612 -- p_payer_id_rec IN Payer_rec_type Optional
613 -- p_pmtinstr_rec IN PmtInstr_rec_type Required
614 -- p_tangible_rec IN Tangible_rec_type Required
615 -- p_pmtreqtrxn_rec IN PmtReqTrxn_rec_type Required
616 -- p_riskinfo_rec IN RiskInfo_rec_type Optional
617 -- Version :
618 -- Current version 1.0
619 -- Previous version 1.0
620 -- Initial version 1.0
621 -- End of comments
622 --------------------------------------------------------------------------------------------
623 PROCEDURE OraPmtReq ( p_api_version IN NUMBER,
624 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
625 p_commit IN VARCHAR2 := FND_API.G_FALSE,
626 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
627 p_ecapp_id IN NUMBER,
628 p_payee_rec IN Payee_rec_type,
629 p_payer_rec IN Payer_rec_type,
630 p_pmtinstr_rec IN PmtInstr_rec_type,
631 p_tangible_rec IN Tangible_rec_type,
632 p_pmtreqtrxn_rec IN PmtReqTrxn_rec_type,
633 p_riskinfo_rec IN RiskInfo_rec_type
634 := IBY_PAYMENT_ADAPTER_PUB.G_MISS_RISKINFO_REC,
635 x_return_status OUT NOCOPY VARCHAR2,
636 x_msg_count OUT NOCOPY NUMBER,
637 x_msg_data OUT NOCOPY VARCHAR2,
638 x_reqresp_rec OUT NOCOPY ReqResp_rec_type
639 ) IS
640
641
642 l_get_baseurl VARCHAR2(2000);
643 --The following 3 variables are meant for output of
644 --get_baseurl procedure.
645 l_status_url VARCHAR2(2000);
646 l_msg_count_url NUMBER := 0;
647 l_msg_data_url VARCHAR2(2000);
648
649 l_api_name CONSTANT VARCHAR2(30) := 'OraPmtReq';
650 l_oapf_action CONSTANT VARCHAR2(30) := 'oraPmtReq';
651 l_api_version CONSTANT NUMBER := 1.0;
652
653 l_url VARCHAR2(30000) ;
654 l_html VARCHAR2(30000) ;
655 l_names v240_tbl_type;
656 l_values v240_tbl_type;
657
658 --The following 3 variables are meant for output of
659 --unpack_results_url procedure.
660 l_status NUMBER := 0;
661 l_errcode NUMBER := 0;
662 l_errmessage VARCHAR2(2000) := 'Success';
663
664 -- remember if address parameters have been output so that they do not
665 -- appear twice
666 l_addrinfo_set BOOLEAN := FALSE;
667
668 -- for NLS bug fix #1692300 - 4/3/2001 jleybovi
669 --
670 l_db_nls p_pmtreqtrxn_rec.NLS_LANG%TYPE := NULL;
671 l_ecapp_nls p_pmtreqtrxn_rec.NLS_LANG%TYPE := NULL;
672
673 v_trxnTimestamp DATE := NULL;
674
675 --Defining a local variable to hold the payment instrument type.
676 l_pmtinstr_type VARCHAR2(200);
677
678 BEGIN
679
680 iby_debug_pub.add(debug_msg => 'Enter',
681 debug_level => FND_LOG.LEVEL_PROCEDURE,
682 module => G_DEBUG_MODULE || '.OraPmtReq');
683
684 -- Standard Start of API savepoint
685 --SAVEPOINT OraPmtReq_PUB;
686
687 /* ***** Performing Validations, Initializations
688 for Standard IN, OUT Parameters ***** */
689
690 -- Standard call to check for call compatibility.
691 IF NOT FND_API.Compatible_API_Call ( l_api_version,
692 p_api_version,
693 l_api_name,
694 G_PKG_NAME )
695 THEN
696 FND_MESSAGE.SET_NAME('IBY', 'IBY_204400_API_VER_MISMATCH');
697 FND_MSG_PUB.Add;
698 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
699 END IF;
700
701 -- Initialize message list if p_init_msg_list is set to TRUE.
702 IF FND_API.to_Boolean( p_init_msg_list ) THEN
703 FND_MSG_PUB.initialize;
704 END IF;
705
706 -- Verifying if validation level is FULL, which is expected for PUBLIC APIs.
707 IF (p_validation_level <> g_validation_level) THEN
708 FND_MESSAGE.SET_NAME('IBY', 'IBY_204401_VAL_LEVEL_ERROR');
709 FND_MSG_PUB.Add;
710 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
711 END IF;
712
713 -- Initialize API return status to success
714 x_return_status := FND_API.G_RET_STS_SUCCESS;
715
716 -- START OF BODY OF API
717
718 get_baseurl(l_get_baseurl);
719 -- dbms_output.put_line('l_get_baseurl= ' || l_get_baseurl);
720 -- dbms_output.put_line('l_status_url= ' || l_status_url);
721 -- dbms_output.put_line('l_msg_count_url= ' || l_msg_count_url);
722 -- dbms_output.put_line('l_msg_data_url= ' || l_msg_data_url);
723
724 -- Construct the full URL to send to the ECServlet.
725 l_url := l_get_baseurl;
726
727 l_db_nls := iby_utility_pvt.get_local_nls();
728 l_ecapp_nls := p_pmtreqtrxn_rec.NLS_LANG;
729
730 -- dbms_output.put_line('db NLS val is: ' || l_db_nls);
731 -- dbms_output.put_line('ecapp NLS val is: ' || l_ecapp_nls);
732
733 --MANDATORY INPUT PARAMETERS
734 check_mandatory('OapfAction', l_oapf_action, l_url, l_db_nls, l_ecapp_nls);
735 check_mandatory('OapfECAppId', to_char(p_ecapp_id), l_url, l_db_nls, l_ecapp_nls);
736 check_mandatory('OapfStoreId', p_payee_rec.Payee_ID, l_url, l_db_nls, l_ecapp_nls);
737
738 -- the mode has to be mandatory as per the specifications
739 check_mandatory('OapfMode', p_pmtreqtrxn_rec.PmtMode, l_url, l_db_nls, l_ecapp_nls);
740
741 check_optional('OapfPmtFactorFlag', p_pmtreqtrxn_rec.Payment_Factor_Flag, l_url, l_db_nls, l_ecapp_nls);
742 --Tangible related mandatory input
743 check_mandatory('OapfOrderId', p_tangible_rec.Tangible_ID, l_url, l_db_nls, l_ecapp_nls);
744 check_mandatory('OapfPrice', FND_NUMBER.NUMBER_TO_CANONICAL(p_tangible_rec.Tangible_Amount), l_url, l_db_nls, l_ecapp_nls);
745 check_mandatory('OapfCurr', p_tangible_rec.Currency_Code, l_url, l_db_nls, l_ecapp_nls);
746
747 -- Party id mandatory paramter
748 check_mandatory('OapfPayerId', p_payer_rec.party_id, l_url, l_db_nls, l_ecapp_nls);
749
750 -- org_id required paramter. Org_type defaulted to OPERATING_UNIT
751 check_mandatory('OapfOrgId', to_char(p_pmtreqtrxn_rec.Org_ID), l_url, l_db_nls, l_ecapp_nls);
752 check_mandatory('OapfOrgType', to_char(p_pmtreqtrxn_rec.org_type), l_url, l_db_nls, l_ecapp_nls);
753
754 -- instrument id and instrument type are required
755 -- possible values CREDITCARD,PURCHASECARD,PINLESSDEBITCARD,
756 -- BANKACCOUNT, and DUALPAYMENTINSTR
757 l_pmtinstr_type := p_pmtinstr_rec.PmtInstr_Type;
758
759 --CONDITIONALLY MANDATORY INPUT PARAMETERS
760
761 IF (l_pmtinstr_type = 'DUALPAYMENTINSTR') THEN
762 -- both payment instruments set to BANKACCOUNT since
763 -- dualpaymetninstrument is only used for bankaccount transfer.
764 check_mandatory('OapfPmtRegId', to_char(p_pmtinstr_rec.DualPaymentInstr.PmtInstr_ID), l_url, l_db_nls, l_ecapp_nls);
765 check_mandatory('OapfPmtInstrType', 'BANKACCOUNT', l_url, l_db_nls, l_ecapp_nls);
766 check_optional('OapfBnfPmtRegId', to_char(p_pmtinstr_rec.DualPaymentInstr.BnfPmtInstr_ID), l_url, l_db_nls, l_ecapp_nls);
767
768 IF (p_pmtinstr_rec.DualPaymentInstr.BnfPmtInstr_ID is NOT NULL) THEN
769 check_optional('OapfBnfPmtInstrType', 'BANKACCOUNT', l_url, l_db_nls, l_ecapp_nls);
770
771 END IF;
772 ELSE
773 --d. REGISTERED INSTRUMENT RELATED CONDITIONALLY MANDATORY INPUT
774 check_mandatory('OapfPmtRegId', to_char(p_pmtinstr_rec.PmtInstr_ID), l_url, l_db_nls, l_ecapp_nls);
775 check_mandatory('OapfPmtInstrType', l_pmtinstr_type, l_url, l_db_nls, l_ecapp_nls);
776
777 IF ((l_pmtinstr_type = 'CREDITCARD') OR ( l_pmtinstr_type = 'PURCHASECARD') OR
778 (l_pmtinstr_type = 'PINLESSDEBITCARD')) THEN
779
780 check_mandatory('OapfAuthType', UPPER(p_pmtreqtrxn_rec.Auth_Type), l_url, l_db_nls, l_ecapp_nls);
781 check_optional('OapfCVV2', p_pmtreqtrxn_rec.CVV2, l_url, l_db_nls, l_ecapp_nls);
782
783 ELSIF (l_pmtinstr_type='BANKACCOUNT') THEN
784
785 -- ACH. Added to pass the new AuthType VERIFY in the case of ONLINE ACH verification
786 check_optional('OapfAuthType', UPPER(p_pmtreqtrxn_rec.Auth_Type), l_url, l_db_nls, l_ecapp_nls);
787
788 END IF;
789
790 END IF;
791
792 --2. Payment Mode related Conditional Mandatory Input Parameters
793 IF (p_pmtreqtrxn_rec.PmtMode = 'OFFLINE') THEN
794 check_mandatory('OapfSchedDate', to_char(p_pmtreqtrxn_rec.Settlement_Date, 'YYYY-MM-DD'), l_url, l_db_nls, l_ecapp_nls);
795
796 -- Bug #1248563
797 --check if CheckFlag is supplied, if not, use 'TRUE' default
798 IF p_pmtreqtrxn_rec.Check_Flag IS NULL THEN
799 check_optional('OapfCheckFlag', 'TRUE', l_url, l_db_nls, l_ecapp_nls);
800 ELSE
801 check_optional('OapfCheckFlag', p_pmtreqtrxn_rec.Check_Flag, l_url, l_db_nls, l_ecapp_nls);
802 END IF;
803 --check_mandatory('OapfCheckFlag', p_pmtreqtrxn_rec.Check_Flag, l_url, l_db_nls, l_ecapp_nls);
804 -- End of Bug #1248563
805 END IF;
806
807
808 --OPTIONAL INPUT PARAMETERS
809
810 --Instrument related optional input
811 check_optional('OapfInstrName', p_pmtinstr_rec.PmtInstr_ShortName, l_url, l_db_nls, l_ecapp_nls);
812
813 -- options payment channel code parameter
814 check_optional('OapfPmtChannelCode', UPPER(p_pmtreqtrxn_rec.payment_channel_code), l_url, l_db_nls, l_ecapp_nls);
815
816 --Newly Added for ECServlet requirements (12/16/99)
817 check_optional('OapfRetry', p_pmtreqtrxn_rec.Retry_Flag, l_url, l_db_nls, l_ecapp_nls);
818
819 --Add Voice auth flag and AuthCode, if present
820 IF (NVL(p_pmtreqtrxn_rec.VoiceAuthFlag, 'N') = 'Y') THEN
821 check_mandatory('OapfVoiceAuthFlag',
822 p_pmtreqtrxn_rec.VoiceAuthFlag, l_url, l_db_nls, l_ecapp_nls);
823 check_mandatory('OapfAuthCode', p_pmtreqtrxn_rec.AuthCode, l_url, l_db_nls, l_ecapp_nls);
824
825 check_mandatory('OapfDateOfVoiceAuth',TO_CHAR(p_pmtreqtrxn_rec.DateOfVoiceAuthorization, 'YYYY-MM-DD'), l_url,
826 l_db_nls, l_ecapp_nls);
827 END IF;
828
829
830 --Tangible related optional input
831 check_optional('OapfMemo', p_tangible_rec.Memo, l_url, l_db_nls, l_ecapp_nls);
832 check_optional('OapfAcctNumber', p_tangible_rec.Acct_Num, l_url, l_db_nls, l_ecapp_nls);
833 check_optional('OapfRefNumber', p_tangible_rec.RefInfo, l_url, l_db_nls, l_ecapp_nls);
834 check_optional('OapfOrderMedium', p_tangible_rec.OrderMedium, l_url, l_db_nls, l_ecapp_nls);
835 check_optional('OapfEftAuthMethod', p_tangible_rec.EFTAuthMethod, l_url, l_db_nls, l_ecapp_nls);
836
837 check_optional('OapfNlsLang', p_pmtreqtrxn_rec.NLS_LANG, l_url, l_db_nls, l_ecapp_nls);
838
839 --Purchase Card related optional input
840 check_optional('OapfPONum', to_char(p_pmtreqtrxn_rec.PONum), l_url, l_db_nls, l_ecapp_nls);
841 check_optional('OapfTaxAmount', to_char(p_pmtreqtrxn_rec.TaxAmount), l_url, l_db_nls, l_ecapp_nls);
842 check_optional('OapfShipToZip', p_pmtreqtrxn_rec.ShipToZip, l_url, l_db_nls, l_ecapp_nls);
843 check_optional('OapfShipFromZip', p_pmtreqtrxn_rec.ShipFromZip, l_url, l_db_nls, l_ecapp_nls);
844
845 -- Optional flag to indicate whether to do risk analysis or not
846 check_optional('OapfAnalyzeRisk', p_pmtreqtrxn_rec.AnalyzeRisk, l_url, l_db_nls, l_ecapp_nls);
847
848 -- RISK INFO OBJECT INPUT PARAMETERS
849
850 -- Check all Risk related inputs only if Formula_Name is specified.
851 IF (p_riskinfo_rec.Formula_Name <> FND_API.G_MISS_CHAR)
852 AND (p_riskinfo_rec.Formula_Name IS NOT NULL) THEN
853 check_optional('OapfFormulaName', p_riskinfo_rec.Formula_Name, l_url, l_db_nls, l_ecapp_nls);
854 -- check_optional is not checking for FND_API.G_MISS_CHAR
855 IF (p_riskinfo_rec.ShipToBillTo_Flag <> FND_API.G_MISS_CHAR) THEN
856 check_optional('OapfShipToBillToFlag', p_riskinfo_rec.ShipToBillTo_Flag, l_url, l_db_nls, l_ecapp_nls);
857 END IF;
858 -- check_optional is not checking for FND_API.G_MISS_CHAR
859 IF (p_riskinfo_rec.Time_Of_Purchase <> FND_API.G_MISS_CHAR) THEN
860 check_optional('OapfTimeOfPurchase', p_riskinfo_rec.Time_Of_Purchase, l_url, l_db_nls, l_ecapp_nls);
861 END IF;
862
863 --NOTE: Customer_Acct_Num and Org_ID are together Mandatory
864 --if AR risk factors are to be used.
865 IF ( (p_riskinfo_rec.Customer_Acct_Num IS NOT NULL)
866 AND (p_riskinfo_rec.Customer_Acct_Num <> FND_API.G_MISS_CHAR) )
867 -- OR ( (p_riskinfo_rec.Org_ID IS NOT NULL)
868 -- AND (p_riskinfo_rec.Org_ID <> FND_API.G_MISS_NUM) )
869 THEN
870 -- check_mandatory('OapfOrgId', to_char(p_riskinfo_rec.Org_ID), l_url, l_db_nls, l_ecapp_nls);
871 check_mandatory('OapfCustAccountNum', p_riskinfo_rec.Customer_Acct_Num, l_url, l_db_nls, l_ecapp_nls);
872 END IF;
873 END IF;
874
875
876 check_optional('OapfTrxnRef', p_pmtreqtrxn_rec.TrxnRef, l_url, l_db_nls, l_ecapp_nls);
877 check_optional('OapfTrxnExtensionId', p_pmtreqtrxn_rec.Trxn_Extension_Id, l_url, l_db_nls, l_ecapp_nls);
878 -- add new parameter for instrument assignment id
879
880 check_optional('OapfPmtInstrAssignmentId', p_pmtinstr_rec.PmtInstr_Assignment_Id, l_url, l_db_nls, l_ecapp_nls);
881
882 -- Remove ampersand from the last input parameter.
883 l_url := RTRIM(l_url,'&');
884
885 -- no longer needed as escape_url_chars() does this
886 --
887 -- ^^^^ Replace blank characters with + sign.^^^^
888 --l_url := REPLACE(l_url,' ','+');
889
890 --show_input_debug(l_url);
891
892 -- Send http request to the payment server
893 --l_html := UTL_HTTP.REQUEST(l_url);
894 --dbms_output.put_line('send request ');
895 --dbms_output.put_line('l_url is:'||substr(l_url,1, 50));
896
897 -- dbms_output.put_line('l_html is:'||substr(l_html, 1, 50));
898 SEND_REQUEST(l_url, l_html);
899 -- show_output_debug(l_html);
900
901 -- Unpack the results
902 UNPACK_RESULTS_URL(l_html,l_names,l_values, l_status, l_errcode, l_errmessage);
903
904 --show_table_debug(l_names);
905 --show_table_debug(l_values);
906
907 --Raising Exception to handle errors in unpacking resulting html file.
908 IF (l_status = -1) THEN
909 iby_debug_pub.add(debug_msg => 'Unpack status error; HTML resp. invalid!',
910 debug_level => FND_LOG.LEVEL_UNEXPECTED,
911 module => G_DEBUG_MODULE || '.OraPmtReq');
912 FND_MESSAGE.SET_NAME('IBY', 'IBY_204403_HTML_UNPACK_ERROR');
913 FND_MSG_PUB.Add;
914 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
915 END IF;
916
917 --Raising Exception to handle Servlet related errors.
918 IF (l_names.COUNT = 0) THEN
919 iby_debug_pub.add(debug_msg => 'HTML response names count=0',
920 debug_level => FND_LOG.LEVEL_UNEXPECTED,
921 module => G_DEBUG_MODULE || '.OraPmtReq');
922 FND_MESSAGE.SET_NAME('IBY', 'IBY_204402_JSERVLET_ERROR');
923 FND_MSG_PUB.Add;
924 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
925 END IF;
926
927 /* Retrieve name-value pairs stored in l_names and l_values, and assign
928 them to the output record: x_reqresp_rec.
929 */
930
931 iby_debug_pub.add(debug_msg => 'Setting fields from unpacked response',
932 debug_level => FND_LOG.LEVEL_STATEMENT,
933 module => G_DEBUG_MODULE || '.OraPmtReq');
934
935
936 FOR i IN 1..l_names.COUNT LOOP
937 --Payment Server Related Generic Response
938 IF l_names(i) = 'OapfStatus' THEN
939 x_reqresp_rec.Response.Status := TO_NUMBER(l_values(i));
940 iby_debug_pub.add(debug_msg => 'Response status=' || x_reqresp_rec.Response.Status,
941 debug_level => FND_LOG.LEVEL_STATEMENT,
942 module => G_DEBUG_MODULE || '.OraPmtReq');
943 ELSIF l_names(i) = 'OapfCode' THEN
944 x_reqresp_rec.Response.ErrCode := l_values(i);
945 iby_debug_pub.add(debug_msg => 'Response code=' || x_reqresp_rec.Response.ErrCode,
946 debug_level => FND_LOG.LEVEL_STATEMENT,
947 module => G_DEBUG_MODULE || '.OraPmtReq');
948 ELSIF l_names(i) = 'OapfCause' THEN
949 x_reqresp_rec.Response.ErrMessage := l_values(i);
950 iby_debug_pub.add(debug_msg => 'Response message=' || x_reqresp_rec.Response.ErrMessage,
951 debug_level => FND_LOG.LEVEL_STATEMENT,
952 module => G_DEBUG_MODULE || '.OraPmtReq');
953 ELSIF l_names(i) = 'OapfNlsLang' THEN
954 x_reqresp_rec.Response.NLS_LANG := l_values(i);
955
956 --Payment Operation Related Response
957 ELSIF l_names(i) = 'OapfTransactionId' THEN
958 x_reqresp_rec.Trxn_ID := TO_NUMBER(l_values(i));
959 ELSIF l_names(i) = 'OapfTrxnType' THEN
960 x_reqresp_rec.Trxn_Type := TO_NUMBER(l_values(i));
961 ELSIF l_names(i) = 'OapfTrxnDate' THEN
962 x_reqresp_rec.Trxn_Date := TO_DATE(l_values(i), 'YYYY-MM-DD');
963 --
964 -- if timestamp parameter is returned then use it instead of
965 -- the less precise trxn date parameter as some applications
966 -- require it (see bug #1649833)
967 --
968 -- nature of this loop, however, requires it to be stored in a
969 -- local variable and then later put into the response record
970 --
971 -- jleybovi [02/20/2001]
972 --
973 ELSIF l_names(i) = 'OapfTimestamp' THEN
974 v_trxnTimestamp := TO_DATE(l_values(i),'YYYYMMDDHH24MISS');
975 ELSIF l_names(i) = 'OapfAuthcode' THEN
976 x_reqresp_rec.AuthCode := l_values(i);
977 ELSIF l_names(i) = 'OapfAVScode' THEN
978 x_reqresp_rec.AVScode := l_values(i);
979 ELSIF l_names(i) = 'OapfCVV2Result' THEN
980 x_reqresp_rec.CVV2Result := l_values(i);
981 ELSIF l_names(i) = 'OapfPmtInstrType' THEN
982 x_reqresp_rec.PmtInstr_Type := l_values(i);
983 ELSIF l_names(i) = 'OapfRefcode' THEN
984 x_reqresp_rec.RefCode := l_values(i);
985 ELSIF l_names(i) = 'OapfAcquirer' THEN
986 x_reqresp_rec.Acquirer := l_values(i);
987 ELSIF l_names(i) = 'OapfVpsBatchId' THEN
988 x_reqresp_rec.VpsBatch_ID := l_values(i);
989 ELSIF l_names(i) = 'OapfAuxMsg' THEN
990 x_reqresp_rec.AuxMsg := l_values(i);
991 ELSIF l_names(i) = 'OapfErrLocation' THEN
992 x_reqresp_rec.ErrorLocation := TO_NUMBER(l_values(i));
993 ELSIF l_names(i) = 'OapfVendErrCode' THEN
994 x_reqresp_rec.BEPErrCode := l_values(i);
995 ELSIF l_names(i) = 'OapfVendErrmsg' THEN
996 x_reqresp_rec.BEPErrMessage := l_values(i);
997
998 --OFFLINE Payment Mode Related Response
999 ELSIF l_names(i) = 'OapfEarliestSettlementDate' THEN
1000 x_reqresp_rec.OffLineResp.EarliestSettlement_Date := TO_DATE(l_values(i), 'YYYY-MM-DD');
1001 ELSIF l_names(i) = 'OapfSchedDate' THEN
1002 x_reqresp_rec.OffLineResp.Scheduled_Date := TO_DATE(l_values(i), 'YYYY-MM-DD');
1003
1004 --RISK Related Response
1005 ELSIF l_names(i) = 'OapfRiskStatus' THEN
1006 x_reqresp_rec.RiskResponse.Status := TO_NUMBER(l_values(i));
1007 ELSIF l_names(i) = 'OapfRiskErrorCode' THEN
1008 x_reqresp_rec.RiskResponse.ErrCode := l_values(i);
1009 ELSIF l_names(i) = 'OapfRiskErrorMsg' THEN
1010 x_reqresp_rec.RiskResponse.ErrMessage := l_values(i);
1011 ELSIF l_names(i) = 'OapfRiskAdditionalErrorMsg' THEN
1012 x_reqresp_rec.RiskResponse.Additional_ErrMessage := l_values(i);
1013 ELSIF l_names(i) = 'OapfRiskScore' THEN
1014 x_reqresp_rec.RiskResponse.Risk_Score := TO_NUMBER(l_values(i));
1015 ELSIF l_names(i) = 'OapfRiskThresholdVal' THEN
1016 x_reqresp_rec.RiskResponse.Risk_Threshold_Val := TO_NUMBER(l_values(i));
1017 ELSIF l_names(i) = 'OapfRiskyFlag' THEN
1018 x_reqresp_rec.RiskResponse.Risky_Flag := l_values(i);
1019
1020 END IF;
1021
1022 --If there is some risk response, ECApp can look
1023 --into the riskresp object for risk response details.
1024 IF (x_reqresp_rec.RiskResponse.Status IS NOT NULL) THEN
1025 x_reqresp_rec.RiskRespIncluded := 'YES';
1026 ELSE
1027 x_reqresp_rec.RiskRespIncluded := 'NO';
1028 END IF;
1029
1030 END LOOP;
1031
1032 -- use the more accurate time stamp parameter if returned
1033 --
1034 IF NOT (v_trxnTimestamp IS NULL) THEN
1035 x_reqresp_rec.Trxn_Date := v_trxnTimestamp;
1036 END IF;
1037
1038 -- Setting API return status to 'U' if iPayment response status is not 0.
1039 IF (NOT trxn_status_success(x_reqresp_rec.Response.Status)) THEN
1040 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1041 END IF;
1042
1043 -- END OF BODY OF API
1044
1045 -- Use for Debugging
1046 --dbms_output.put_line('after successfully mapping results');
1047
1048 -- Standard check of p_commit.
1049 /*
1050 IF FND_API.To_Boolean( p_commit ) THEN
1051 COMMIT WORK;
1052 END IF;
1053 */
1054
1055 -- Standard call to get message count and if count is 1, get message info.
1056 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1057 p_data => x_msg_data
1058 );
1059
1060 iby_debug_pub.add(debug_msg => 'x_return_status=' || x_return_status,
1061 debug_level => FND_LOG.LEVEL_STATEMENT,
1062 module => G_DEBUG_MODULE || '.OraPmtReq');
1063 iby_debug_pub.add(debug_msg => 'req response status=' || x_reqresp_rec.Response.Status,
1064 debug_level => FND_LOG.LEVEL_STATEMENT,
1065 module => G_DEBUG_MODULE || '.OraPmtReq');
1066
1067 iby_debug_pub.add(debug_msg => 'Exit',
1068 debug_level => FND_LOG.LEVEL_PROCEDURE,
1069 module => G_DEBUG_MODULE || '.OraPmtReq');
1070
1071 EXCEPTION
1072
1073 WHEN FND_API.G_EXC_ERROR THEN
1074
1075 iby_debug_pub.add(debug_msg => 'In G_EXC_ERROR Exception',
1076 debug_level => FND_LOG.LEVEL_ERROR,
1077 module => G_DEBUG_MODULE || '.OraPmtReq');
1078 --ROLLBACK TO OraPmtReq_PUB;
1079 x_return_status := FND_API.G_RET_STS_ERROR ;
1080 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1081 p_data => x_msg_data
1082 );
1083 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1084
1085 iby_debug_pub.add(debug_msg => 'In G_EXC_UNEXPECTED_ERROR Exception',
1086 debug_level => FND_LOG.LEVEL_UNEXPECTED,
1087 module => G_DEBUG_MODULE || '.OraPmtReq');
1088 --ROLLBACK TO OraPmtReq_PUB;
1089 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1090 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1091 p_data => x_msg_data
1092 );
1093 WHEN OTHERS THEN
1094
1095 iby_debug_pub.add(debug_msg => 'In OTHERS Exception',
1096 debug_level => FND_LOG.LEVEL_UNEXPECTED,
1097 module => G_DEBUG_MODULE || '.OraPmtReq');
1098 --ROLLBACK TO OraPmtReq_PUB;
1099 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1100 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1101 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_api_name);
1102 END IF;
1103
1104 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1105 p_data => x_msg_data
1106 );
1107
1108 iby_debug_pub.add(debug_msg => 'x_return_status=' || x_return_status,
1109 debug_level => FND_LOG.LEVEL_UNEXPECTED,
1110 module => G_DEBUG_MODULE || '.OraPmtReq');
1111 iby_debug_pub.add(debug_msg => 'Exit Exception',
1112 debug_level => FND_LOG.LEVEL_UNEXPECTED,
1113 module => G_DEBUG_MODULE || '.OraPmtReq');
1114
1115 END OraPmtReq;
1116
1117 --------------------------------------------------------------------------------------------
1118 --2. OraPmtMod
1119 -- Start of comments
1120 -- API name : OraPmtMod
1121 -- Type : Public
1122 -- Pre-reqs : Previous Payment Request.
1123 -- Function : Handles Modifications to existing Payment Request
1124 -- Transactions in case of Scheduled (OFFLINE) payments.
1125 -- Parameters :
1126 -- IN : p_api_version IN NUMBER Required
1127 -- p_init_msg_list IN VARCHAR2 Optional
1128 -- p_commit IN VARCHAR2 Optional
1129 -- p_validation_level IN NUMBER Optional
1130 -- p_ecapp_id IN NUMBER Required
1131 -- p_payee_id_rec IN Payee_rec_type Required
1132 -- p_payer_id_rec IN Payer_rec_type Optional
1133 -- p_pmtinstr_rec IN PmtInstr_rec_type Required
1134 -- p_tangible_rec IN Tangible_rec_type Required
1135 -- p_ModTrxn_rec IN ModTrxn_rec_type Required
1136 -- Version :
1137 -- Current version 1.0
1138 -- Previous version 1.0
1139 -- Initial version 1.0
1140 -- End of comments
1141 --------------------------------------------------------------------------------------------
1142 PROCEDURE OraPmtMod ( p_api_version IN NUMBER,
1143 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1144 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1145 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1146 p_ecapp_id IN NUMBER,
1147 p_payee_rec IN Payee_rec_type,
1148 p_payer_rec IN Payer_rec_type,
1149 p_pmtinstr_rec IN PmtInstr_rec_type,
1150 p_tangible_rec IN Tangible_rec_type,
1151 p_ModTrxn_rec IN ModTrxn_rec_type,
1152 x_return_status OUT NOCOPY VARCHAR2,
1153 x_msg_count OUT NOCOPY NUMBER,
1154 x_msg_data OUT NOCOPY VARCHAR2,
1155 x_modresp_rec OUT NOCOPY ModResp_rec_type
1156 ) IS
1157
1158 l_get_baseurl VARCHAR2(2000);
1159 --The following 3 variables are meant for output of
1160 --get_baseurl procedure.
1161 l_status_url VARCHAR2(2000);
1162 l_msg_count_url NUMBER := 0;
1163 l_msg_data_url VARCHAR2(2000);
1164
1165 l_api_name CONSTANT VARCHAR2(30) := 'OraPmtMod';
1166 l_oapf_action CONSTANT VARCHAR2(30) := 'oraPmtMod';
1167 l_api_version CONSTANT NUMBER := 1.0;
1168
1169 TYPE l_v240_tbl IS TABLE of VARCHAR2(240) INDEX BY BINARY_INTEGER;
1170 l_url VARCHAR2(4000) ;
1171 l_html VARCHAR2(7000) ;
1172 l_names v240_tbl_type;
1173 l_values v240_tbl_type;
1174
1175 --The following 3 variables are meant for unpack_results_url procedure.
1176 l_status NUMBER := 0;
1177 l_errcode NUMBER := 0;
1178 l_errmessage VARCHAR2(2000) := 'Success';
1179
1180 -- for NLS bug fix #1692300 - 4/3/2001 jleybovi
1181 --
1182 l_db_nls x_modresp_rec.Response.NLS_LANG%TYPE := null;
1183 l_ecapp_nls x_modresp_rec.Response.NLS_LANG%TYPE := NULL;
1184
1185 --Defining a local variable to hold the payment instrument type.
1186 l_pmtinstr_type VARCHAR2(200);
1187
1188 BEGIN
1189 -- Standard Start of API savepoint
1190 --SAVEPOINT OraPmtMod_PUB;
1191
1192 /* ***** Performing Validations, Initializations
1193 for Standard IN, OUT Parameters ***** */
1194
1195 -- Standard call to check for call compatibility.
1196 IF NOT FND_API.Compatible_API_Call ( l_api_version,
1197 p_api_version,
1198 l_api_name,
1199 G_PKG_NAME )
1200 THEN
1201 FND_MESSAGE.SET_NAME('IBY', 'IBY_204400_API_VER_MISMATCH');
1202 FND_MSG_PUB.Add;
1203 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1204 END IF;
1205
1206 -- Initialize message list if p_init_msg_list is set to TRUE.
1207 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1208 FND_MSG_PUB.initialize;
1209 END IF;
1210
1211 -- Verifying if validation level is FULL, which is expected for PUBLIC APIs.
1212 IF (p_validation_level <> g_validation_level) THEN
1213 FND_MESSAGE.SET_NAME('IBY', 'IBY_204401_VAL_LEVEL_ERROR');
1214 FND_MSG_PUB.Add;
1215 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1216 END IF;
1217
1218 -- Initialize API return status to success
1219 x_return_status := FND_API.G_RET_STS_SUCCESS;
1220
1221 -- START OF BODY OF API
1222
1223 get_baseurl(l_get_baseurl);
1224
1225 -- Construct the full URL to send to the ECServlet.
1226 l_url := l_get_baseurl;
1227
1228 l_db_nls := iby_utility_pvt.get_local_nls();
1229 l_ecapp_nls := NULL; -- not passed in this api??
1230
1231 --MANDATORY INPUT PARAMETERS
1232 check_mandatory('OapfAction', l_oapf_action, l_url, l_db_nls, l_ecapp_nls);
1233 check_mandatory('OapfECAppId', to_char(p_ecapp_id), l_url, l_db_nls, l_ecapp_nls);
1234 check_mandatory('OapfStoreId', p_payee_rec.Payee_ID, l_url, l_db_nls, l_ecapp_nls);
1235
1236 /*
1237 --check if mode is supplied, if not use 'OFFLINE' default
1238 IF p_modtrxn_rec.PmtMode IS NULL THEN
1239 check_optional('OapfMode', 'OFFLINE', l_url, l_db_nls, l_ecapp_nls);
1240 ELSE
1241 check_optional('OapfMode', p_modtrxn_rec.PmtMode, l_url, l_db_nls, l_ecapp_nls);
1242 END IF;
1243 */
1244 -- the mode has to be mandatory as per the specifications
1245 check_mandatory('OapfMode', p_modtrxn_rec.PmtMode, l_url, l_db_nls, l_ecapp_nls);
1246
1247 --Tangible related mandatory input
1248 check_mandatory('OapfOrderId', p_tangible_rec.Tangible_ID, l_url, l_db_nls, l_ecapp_nls);
1249 check_mandatory('OapfPrice', FND_NUMBER.NUMBER_TO_CANONICAL(p_tangible_rec.Tangible_Amount), l_url, l_db_nls, l_ecapp_nls);
1250 check_mandatory('OapfCurr', p_tangible_rec.Currency_Code, l_url, l_db_nls, l_ecapp_nls);
1251
1252 check_mandatory('OapfTransactionId', to_char(p_modtrxn_rec.Trxn_ID), l_url, l_db_nls, l_ecapp_nls);
1253
1254 check_mandatory('OapfPayerId', p_payer_rec.Party_ID, l_url, l_db_nls, l_ecapp_nls);
1255
1256 l_pmtinstr_type := p_pmtinstr_rec.PmtInstr_type;
1257
1258 -- 1. required registered instrument
1259 check_mandatory('OapfPmtRegId', to_char(p_pmtinstr_rec.PmtInstr_ID), l_url, l_db_nls, l_ecapp_nls);
1260 check_mandatory('OapfPmtInstrType', l_pmtinstr_type, l_url, l_db_nls, l_ecapp_nls);
1261
1262 IF ( l_pmtinstr_type = 'CREDITCARD') OR ( l_pmtinstr_type = 'PURCHASECARD') THEN
1263 check_mandatory('OapfAuthType', UPPER(p_modtrxn_rec.Auth_Type), l_url, l_db_nls, l_ecapp_nls);
1264
1265 END IF;
1266
1267 --2. Payment Mode related Conditional Mandatory Input Parameters
1268 IF (p_modtrxn_rec.PmtMode = 'OFFLINE') THEN
1269 check_mandatory('OapfSchedDate', to_char(p_modtrxn_rec.Settlement_Date, 'YYYY-MM-DD'), l_url, l_db_nls, l_ecapp_nls);
1270 -- Bug #1248563
1271 --check if CheckFlag is supplied, if not, use 'TRUE' default
1272 IF p_modtrxn_rec.Check_Flag IS NULL THEN
1273 check_optional('OapfCheckFlag', 'TRUE', l_url, l_db_nls, l_ecapp_nls);
1274 ELSE
1275 check_optional('OapfCheckFlag', p_modtrxn_rec.Check_Flag, l_url, l_db_nls, l_ecapp_nls);
1276 END IF;
1277 --check_mandatory('OapfCheckFlag', p_modtrxn_rec.Check_Flag, l_url, l_db_nls, l_ecapp_nls);
1278 -- End of Bug #1248563
1279 END IF;
1280
1281
1282 --OPTIONAL INPUT PARAMETERS
1283 --Tangible related optional input
1284 check_optional('OapfMemo', p_tangible_rec.Memo, l_url, l_db_nls, l_ecapp_nls);
1285 check_optional('OapfAcctNumber', p_tangible_rec.Acct_Num, l_url, l_db_nls, l_ecapp_nls);
1286 check_optional('OapfRefNumber', p_tangible_rec.RefInfo, l_url, l_db_nls, l_ecapp_nls);
1287 check_optional('OapfOrderMedium', p_tangible_rec.OrderMedium, l_url, l_db_nls, l_ecapp_nls);
1288 check_optional('OapfEftAuthMethod', p_tangible_rec.EFTAuthMethod, l_url, l_db_nls, l_ecapp_nls);
1289
1290
1291 --Purchase Card related optional input
1292 check_optional('OapfPONum', to_char(p_modtrxn_rec.PONum), l_url, l_db_nls, l_ecapp_nls);
1293 check_optional('OapfTaxAmount', to_char(p_modtrxn_rec.TaxAmount), l_url, l_db_nls, l_ecapp_nls);
1294 check_optional('OapfShipToZip', p_modtrxn_rec.ShipToZip, l_url, l_db_nls, l_ecapp_nls);
1295 check_optional('OapfShipFromZip', p_modtrxn_rec.ShipFromZip, l_url, l_db_nls, l_ecapp_nls);
1296
1297 -- Remove ampersand from the last input parameter.
1298 l_url := RTRIM(l_url,'&');
1299
1300 -- done in the escape_url_chars() method
1301 -- ^^^^ Replace blank characters with + sign. ^^^^
1302 --l_url := REPLACE(l_url,' ','+');
1303
1304
1305 -- Send http request to the payment server.
1306 --l_html := UTL_HTTP.REQUEST(l_url);
1307 SEND_REQUEST(l_url, l_html);
1308
1309 -- show_output_debug(l_html);
1310
1311 -- Unpack the results
1312 UNPACK_RESULTS_URL(l_html,l_names,l_values, l_status, l_errcode, l_errmessage);
1313
1314 --Raising Exception to handle errors in unpacking resulting html file.
1315 IF (l_status = -1) THEN
1316 FND_MESSAGE.SET_NAME('IBY', 'IBY_204403_HTML_UNPACK_ERROR');
1317 FND_MSG_PUB.Add;
1318 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1319 END IF;
1320
1321 --Raising Exception to handle Servlet related errors.
1322 IF (l_names.COUNT = 0) THEN
1323 FND_MESSAGE.SET_NAME('IBY', 'IBY_204402_JSERVLET_ERROR');
1324 FND_MSG_PUB.Add;
1325 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1326 END IF;
1327
1328 /* Retrieve name-value pairs stored in l_names and l_values, and assign
1329 them to the output record: x_modresp_rec.
1330 */
1331 FOR i IN 1..l_names.COUNT LOOP
1332
1333 IF l_names(i) = 'OapfStatus' THEN
1334 x_modresp_rec.Response.Status := TO_NUMBER(l_values(i));
1335 ELSIF l_names(i) = 'OapfCode' THEN
1336 x_modresp_rec.Response.ErrCode := l_values(i);
1337 ELSIF l_names(i) = 'OapfCause' THEN
1338 x_modresp_rec.Response.ErrMessage := l_values(i);
1339 ELSIF l_names(i) = 'OapfNlsLang' THEN
1340 x_modresp_rec.Response.NLS_LANG := l_values(i);
1341 ELSIF l_names(i) = 'OapfTransactionId' THEN
1342 x_modresp_rec.Trxn_ID := TO_NUMBER(l_values(i));
1343 END IF;
1344
1345 END LOOP;
1346
1347 -- Setting API return status to 'U' if iPayment response status is not 0.
1348 IF (NOT trxn_status_success(x_modresp_rec.Response.Status)) THEN
1349 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1350 END IF;
1351
1352 -- END OF BODY OF API
1353
1354 -- Standard check of p_commit.
1355 /*
1356 IF FND_API.To_Boolean( p_commit ) THEN
1357 COMMIT WORK;
1358 END IF;
1359 */
1360
1361 -- Standard call to get message count and if count is 1, get message info.
1362 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1363 p_data => x_msg_data
1364 );
1365 EXCEPTION
1366
1367
1368 WHEN FND_API.G_EXC_ERROR THEN
1369 --ROLLBACK TO OraPmtMod_PUB;
1370 x_return_status := FND_API.G_RET_STS_ERROR ;
1371 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1372 p_data => x_msg_data
1373 );
1374 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1375 --ROLLBACK TO OraPmtMod_PUB;
1376 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1377 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1378 p_data => x_msg_data
1379 );
1380 WHEN OTHERS THEN
1381 --ROLLBACK TO OraPmtMod_PUB;
1382 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1383 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1384 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_api_name);
1385 END IF;
1386
1387 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1388 p_data => x_msg_data
1389 );
1390
1391 END OraPmtMod;
1392
1393 --------------------------------------------------------------------------------------------
1394 --3. OraPmtCanc
1395 -- Start of comments
1396 -- API name : OraPmtCanc
1397 -- Type : Public
1398 -- Pre-reqs : None
1399 -- Function : Handles cancellations of off-line Payments.
1400 -- Parameters :
1401 -- IN : p_api_version IN NUMBER Required
1402 -- p_init_msg_list IN VARCHAR2 Optional
1403 -- p_commit IN VARCHAR2 Optional
1404 -- p_validation_level IN NUMBER Optional
1405 -- p_ecapp_id IN NUMBER Required
1406 -- p_canctrxn_rec IN CancelTrxn_rec_type Required
1407 -- Version :
1408 -- Current version 1.0
1409 -- Previous version 1.0
1410 -- Initial version 1.0
1411 -- End of comments
1412 --------------------------------------------------------------------------------------------
1413 PROCEDURE OraPmtCanc ( p_api_version IN NUMBER,
1414 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1415 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1416 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1417 p_ecapp_id IN NUMBER,
1418 p_canctrxn_rec IN CancelTrxn_rec_type,
1419 x_return_status OUT NOCOPY VARCHAR2,
1420 x_msg_count OUT NOCOPY NUMBER,
1421 x_msg_data OUT NOCOPY VARCHAR2,
1422 x_cancresp_rec OUT NOCOPY CancelResp_rec_type
1423 ) IS
1424
1425 l_get_baseurl VARCHAR2(2000);
1426 --The following 3 variables are meant for output of
1427 --get_baseurl procedure.
1428 l_status_url VARCHAR2(2000);
1429 l_msg_count_url NUMBER := 0;
1430 l_msg_data_url VARCHAR2(2000);
1431
1432 l_api_name CONSTANT VARCHAR2(30) := 'OraPmtCanc';
1433 l_oapf_action CONSTANT VARCHAR2(30) := 'oraPmtCanc';
1434 l_api_version CONSTANT NUMBER := 1.0;
1435
1436 TYPE l_v240_tbl IS TABLE of VARCHAR2(240) INDEX BY BINARY_INTEGER;
1437 l_url VARCHAR2(4000) ;
1438 l_html VARCHAR2(7000) ;
1439 l_names v240_tbl_type;
1440 l_values v240_tbl_type;
1441
1442 -- for NLS bug fix #1692300 - 4/3/2001 jleybovi
1443 --
1444 l_db_nls p_canctrxn_rec.NLS_LANG%TYPE := NULL;
1445 l_ecapp_nls p_canctrxn_rec.NLS_LANG%TYPE := NULL;
1446
1447 --The following 3 variables are meant for unpack_results_url procedure.
1448 l_status NUMBER := 0;
1449 l_errcode NUMBER := 0;
1450 l_errmessage VARCHAR2(2000) := 'Success';
1451
1452 l_pmtinstr_type VARCHAR2(80); --for querying up instrument type from DB.
1453 --NOTE* : **** IF INSTRTYPE IS NULL, this is assumed to be 'CREDITCARD' for release 11i ****.
1454
1455 CURSOR pmtcanc_csr IS
1456 SELECT NVL(instrtype, 'CREDITCARD')
1457 FROM IBY_PAYMENTS_V
1458 WHERE transactionid = p_canctrxn_rec.Trxn_ID;
1459
1460 BEGIN
1461 -- Standard Start of API savepoint
1462 --SAVEPOINT OraPmtCanc_PUB;
1463
1464 /* ***** Performing Validations, Initializations
1465 for Standard IN, OUT Parameters ***** */
1466
1467 -- Standard call to check for call compatibility.
1468 IF NOT FND_API.Compatible_API_Call ( l_api_version,
1469 p_api_version,
1470 l_api_name,
1471 G_PKG_NAME )
1472 THEN
1473 FND_MESSAGE.SET_NAME('IBY', 'IBY_204400_API_VER_MISMATCH');
1474 FND_MSG_PUB.Add;
1475 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1476 END IF;
1477
1478 -- Initialize message list if p_init_msg_list is set to TRUE.
1479 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1480 FND_MSG_PUB.initialize;
1481 END IF;
1482
1483 -- Verifying if validation level is FULL, which is expected for PUBLIC APIs.
1484 IF (p_validation_level <> g_validation_level) THEN
1485 FND_MESSAGE.SET_NAME('IBY', 'IBY_204401_VAL_LEVEL_ERROR');
1486 FND_MSG_PUB.Add;
1487 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1488 END IF;
1489
1490 -- Initialize API return status to success
1491 x_return_status := FND_API.G_RET_STS_SUCCESS;
1492
1493 -- START OF BODY OF API
1494
1495 get_baseurl(l_get_baseurl);
1496
1497 -- Construct the full URL to send to the ECServlet.
1498 l_url := l_get_baseurl;
1499
1500 l_db_nls := iby_utility_pvt.get_local_nls();
1501 l_ecapp_nls := p_canctrxn_rec.NLS_LANG;
1502
1503 --Mandatory Input Parameters
1504 check_mandatory('OapfAction', l_oapf_action, l_url, l_db_nls, l_ecapp_nls);
1505 check_mandatory('OapfMode', 'OFFLINE', l_url, l_db_nls, l_ecapp_nls);
1506 check_mandatory('OapfECAppId', to_char(p_ecapp_id), l_url, l_db_nls, l_ecapp_nls);
1507 check_mandatory('OapfTransactionId', to_char(p_canctrxn_rec.Trxn_ID), l_url, l_db_nls, l_ecapp_nls);
1508 check_mandatory('OapfReqType', p_canctrxn_rec.Req_Type, l_url, l_db_nls, l_ecapp_nls);
1509
1510 --Optional Input Parameters
1511 check_optional('OapfNlsLang', p_canctrxn_rec.NLS_LANG, l_url, l_db_nls, l_ecapp_nls);
1512
1513 --dbms_output.put_line('Before opening cursor');
1514
1515 --NOTE*: Query DB to get the PmtInstrType from TransactionID
1516 --to pass to ECServlet.
1517 OPEN pmtcanc_csr;
1518 FETCH pmtcanc_csr
1519 INTO l_pmtinstr_type;
1520
1521 IF (pmtcanc_csr%ROWCOUNT = 0) THEN
1522 FND_MESSAGE.SET_NAME('IBY', 'IBY_204404_CURSOR_ERROR');
1523 FND_MSG_PUB.Add;
1524 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1525 END IF;
1526
1527 CLOSE pmtcanc_csr;
1528 --dbms_output.put_line('after closing cursor');
1529
1530 check_mandatory('OapfPmtInstrType', l_pmtinstr_type, l_url, l_db_nls, l_ecapp_nls);
1531
1532 -- Remove ampersand from the last input parameter.
1533 l_url := RTRIM(l_url,'&');
1534
1535 -- No longer needed; escape_url_chars does this
1536 -- ^^^ Replace blank characters with + sign. ^^^
1537 --l_url := REPLACE(l_url,' ','+');
1538
1539 --show_input_debug(l_url);
1540
1541 -- Send http request to the payment server.
1542 --l_html := UTL_HTTP.REQUEST(l_url);
1543 SEND_REQUEST(l_url, l_html);
1544
1545 --show_output_debug(l_html);
1546
1547 -- Unpack the results
1548 UNPACK_RESULTS_URL(l_html,l_names,l_values, l_status, l_errcode, l_errmessage);
1549
1550 --Raising Exception to handle errors in unpacking resulting html file.
1551 IF (l_status = -1) THEN
1552 FND_MESSAGE.SET_NAME('IBY', 'IBY_204403_HTML_UNPACK_ERROR');
1553 FND_MSG_PUB.Add;
1554 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1555 END IF;
1556
1557 --Raising Exception to handle Servlet related errors.
1558 IF (l_names.COUNT = 0) THEN
1559 FND_MESSAGE.SET_NAME('IBY', 'IBY_204402_JSERVLET_ERROR');
1560 FND_MSG_PUB.Add;
1561 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1562 END IF;
1563
1564 --show_table_debug(l_names);
1565 --show_table_debug(l_values);
1566
1567 /* Retrieve name-value pairs stored in l_names and l_values, and assign
1568 them to the output record: x_cancresp_rec.
1569 */
1570
1571 FOR i IN 1..l_names.COUNT LOOP
1572
1573 --PAYMENT SERVER GENERIC RESPONSE.
1574 IF l_names(i) = 'OapfStatus' THEN
1575 x_cancresp_rec.Response.Status := TO_NUMBER(l_values(i));
1576 ELSIF l_names(i) = 'OapfCode' THEN
1577 x_cancresp_rec.Response.ErrCode := l_values(i);
1578 ELSIF l_names(i) = 'OapfCause' THEN
1579 x_cancresp_rec.Response.ErrMessage := l_values(i);
1580 ELSIF l_names(i) = 'OapfNlsLang' THEN
1581 x_cancresp_rec.Response.NLS_LANG := l_values(i);
1582
1583 -- CANCEL OPERATION SPECIFIC RESPONSE
1584 ELSIF l_names(i) = 'OapfTransactionId' THEN
1585 x_cancresp_rec.Trxn_ID := TO_NUMBER(l_values(i));
1586 ELSIF l_names(i) = 'OapfErrLocation' THEN
1587 x_cancresp_rec.ErrorLocation := TO_NUMBER(l_values(i));
1588 ELSIF l_names(i) = 'OapfVendErrCode' THEN
1589 x_cancresp_rec.BEPErrCode := l_values(i);
1590 ELSIF l_names(i) = 'OapfVendErrmsg' THEN
1591 x_cancresp_rec.BEPErrMessage := l_values(i);
1592 END IF;
1593
1594 END LOOP;
1595
1596 -- Setting API return status to 'U' if iPayment response status is not 0.
1597 IF (NOT trxn_status_success(x_cancresp_rec.Response.Status)) THEN
1598 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1599 END IF;
1600
1601 -- END OF BODY OF API
1602
1603 -- Standard check of p_commit.
1604 /*
1605 IF FND_API.To_Boolean( p_commit ) THEN
1606 COMMIT WORK;
1607 END IF;
1608 */
1609
1610 -- Standard call to get message count and if count is 1, get message info.
1611 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1612 p_data => x_msg_data
1613 );
1614 EXCEPTION
1615 WHEN FND_API.G_EXC_ERROR THEN
1616 --ROLLBACK TO OraPmtCanc_PUB;
1617 x_return_status := FND_API.G_RET_STS_ERROR ;
1618 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1619 p_data => x_msg_data
1620 );
1621 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1622 --ROLLBACK TO OraPmtCanc_PUB;
1623 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1624 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1625 p_data => x_msg_data
1626 );
1627 WHEN OTHERS THEN
1628 --ROLLBACK TO OraPmtCanc_PUB;
1629 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1630 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1631 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_api_name);
1632 END IF;
1633
1634 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1635 p_data => x_msg_data
1636 );
1637
1638 END OraPmtCanc;
1639
1640 --------------------------------------------------------------------------------------------
1641 --4. OraPmtQryTrxn
1642 -- Start of comments
1643 -- API name : OraPmtQryTrxn
1644 -- Type : Public
1645 -- Pre-reqs : None
1646 -- Function : Provides an interface for querying payment
1647 -- transactions details.
1648 -- Parameters :
1649 -- IN : p_api_version IN NUMBER Required
1650 -- p_init_msg_list IN VARCHAR2 Optional
1651 -- p_commit IN VARCHAR2 Optional
1652 -- p_validation_level IN NUMBER Optional
1653 -- p_ecapp_id IN NUMBER Required
1654 -- p_querytrxn_rec IN QueryTrxn_rec_type Required
1655 -- Version :
1656 -- Current version 1.0
1657 -- Previous version 1.0
1658 -- Initial version 1.0
1659 -- End of comments
1660 --------------------------------------------------------------------------------------------
1661 PROCEDURE OraPmtQryTrxn ( p_api_version IN NUMBER,
1662 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1663 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1664 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1665 p_ecapp_id IN NUMBER,
1666 p_querytrxn_rec IN QueryTrxn_rec_type,
1667 x_return_status OUT NOCOPY VARCHAR2,
1668 x_msg_count OUT NOCOPY NUMBER,
1669 x_msg_data OUT NOCOPY VARCHAR2,
1670 x_qrytrxnrespsum_rec OUT NOCOPY QryTrxnRespSum_rec_type,
1671 x_qrytrxnrespdet_tbl OUT NOCOPY QryTrxnRespDet_tbl_type
1672 ) IS
1673
1674 l_get_baseurl VARCHAR2(2000);
1675 --The following 3 variables are meant for output of
1676 --get_baseurl procedure.
1677 l_status_url VARCHAR2(2000);
1678 l_msg_count_url NUMBER := 0;
1679 l_msg_data_url VARCHAR2(2000);
1680
1681
1682 l_api_name CONSTANT VARCHAR2(30) := 'OraPmtQryTrxn';
1683 l_oapf_action CONSTANT VARCHAR2(30) := 'oraPmtQryTrxn';
1684 l_api_version CONSTANT NUMBER := 1.0;
1685
1686 TYPE l_v240_tbl IS TABLE of VARCHAR2(240) INDEX BY BINARY_INTEGER;
1687 l_url VARCHAR2(4000) ;
1688 l_html VARCHAR2(7000) ;
1689 l_names v240_tbl_type;
1690 l_values v240_tbl_type;
1691
1692 --The following 3 variables are meant for unpack_results_url procedure.
1693 l_status NUMBER := 0;
1694 l_errcode NUMBER := 0;
1695 l_errmessage VARCHAR2(2000) := 'Success';
1696
1697 -- for NLS bug fix #1692300 - 4/3/2001 jleybovi
1698 --
1699 l_db_nls p_querytrxn_rec.NLS_LANG%TYPE := NULL;
1700 l_ecapp_nls p_querytrxn_rec.NLS_LANG%TYPE := NULL;
1701
1702 --Local variable to handle detail response table of records index
1703 l_index NUMBER := 0;
1704 i NUMBER := 0;
1705
1706 l_pmtinstr_type VARCHAR2(80); --for querying up instrument type from DB.
1707 CURSOR pmtqrytrxn_csr IS
1708 SELECT NVL(instrtype, 'CREDITCARD')
1709 FROM IBY_PAYMENTS_V
1710 WHERE transactionid = p_querytrxn_rec.Trxn_ID;
1711
1712 BEGIN
1713 -- Standard Start of API savepoint
1714 --SAVEPOINT OraPmtQryTrxn_PUB;
1715
1716 /* ***** Performing Validations, Initializations
1717 for Standard IN, OUT Parameters ***** */
1718
1719 -- Standard call to check for call compatibility.
1720 IF NOT FND_API.Compatible_API_Call ( l_api_version,
1721 p_api_version,
1722 l_api_name,
1723 G_PKG_NAME )
1724 THEN
1725 FND_MESSAGE.SET_NAME('IBY', 'IBY_204400_API_VER_MISMATCH');
1726 FND_MSG_PUB.Add;
1727 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1728 END IF;
1729
1730 -- Initialize message list if p_init_msg_list is set to TRUE.
1731 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1732 FND_MSG_PUB.initialize;
1733 END IF;
1734
1735 -- Verifying if validation level is FULL, which is expected for PUBLIC APIs.
1736 IF (p_validation_level <> g_validation_level) THEN
1737 FND_MESSAGE.SET_NAME('IBY', 'IBY_204401_VAL_LEVEL_ERROR');
1738 FND_MSG_PUB.Add;
1739 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1740 END IF;
1741
1742
1743 -- Initialize API return status to success
1744 x_return_status := FND_API.G_RET_STS_SUCCESS;
1745
1746 -- START OF BODY OF API
1747
1748 get_baseurl(l_get_baseurl);
1749
1750 -- Construct the full URL to send to the ECServlet.
1751 l_url := l_get_baseurl;
1752
1753 l_db_nls := iby_utility_pvt.get_local_nls();
1754 l_ecapp_nls := p_querytrxn_rec.NLS_LANG;
1755
1756 --Mandatory Input Parameters
1757 check_mandatory('OapfAction', l_oapf_action, l_url, l_db_nls, l_ecapp_nls);
1758 check_mandatory('OapfECAppId', to_char(p_ecapp_id), l_url, l_db_nls, l_ecapp_nls);
1759 check_mandatory('OapfTransactionId', to_char(p_querytrxn_rec.Trxn_ID), l_url, l_db_nls, l_ecapp_nls);
1760 check_mandatory('OapfHistory', p_querytrxn_rec.History_Flag, l_url, l_db_nls, l_ecapp_nls);
1761
1762 --For this API Mode has to be ONLINE only.
1763 check_mandatory('OapfMode', 'ONLINE', l_url, l_db_nls, l_ecapp_nls);
1764
1765 --dbms_output.put_line('Before opening cursor');
1766
1767 --NOTE*: Query DB to get the PmtInstrType from TransactionID
1768 --to pass to ECServlet.
1769
1770 OPEN pmtqrytrxn_csr;
1771 FETCH pmtqrytrxn_csr
1772 INTO l_pmtinstr_type;
1773
1774 IF (pmtqrytrxn_csr%ROWCOUNT = 0) THEN
1775 FND_MESSAGE.SET_NAME('IBY', 'IBY_204404_CURSOR_ERROR');
1776 FND_MSG_PUB.Add;
1777 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1778 END IF;
1779
1780 CLOSE pmtqrytrxn_csr;
1781 --dbms_output.put_line('after closing cursor');
1782 --dbms_output.put_line('l_pmtinstrtyp = '|| l_pmtinstr_type);
1783
1784 check_mandatory('OapfPmtInstrType', l_pmtinstr_type, l_url, l_db_nls, l_ecapp_nls);
1785
1786 --Optional Input Parameters
1787 check_optional('OapfNlsLang', p_querytrxn_rec.NLS_LANG, l_url, l_db_nls, l_ecapp_nls);
1788
1789 -- Remove ampersand from the last input parameter.
1790 l_url := RTRIM(l_url,'&');
1791
1792 -- escape_url_chars() alread does this
1793 --
1794 -- ^^^ Replace blank characters with + sign. ^^^
1795 --l_url := REPLACE(l_url,' ','+');
1796
1797 --show_input_debug(l_url);
1798
1799 -- Send http request to the payment server.
1800 -- l_html := UTL_HTTP.REQUEST(l_url);
1801 SEND_REQUEST(l_url, l_html);
1802 --show_output_debug(l_html);
1803
1804 -- Unpack the results
1805 UNPACK_RESULTS_URL(l_html,l_names,l_values, l_status, l_errcode, l_errmessage);
1806
1807 --Raising Exception to handle errors in unpacking resulting html file.
1808 IF (l_status = -1) THEN
1809 FND_MESSAGE.SET_NAME('IBY', 'IBY_204403_HTML_UNPACK_ERROR');
1810 FND_MSG_PUB.Add;
1811 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1812 END IF;
1813
1814 --Raising Exception to handle Servlet related errors.
1815 IF (l_names.COUNT = 0) THEN
1816 FND_MESSAGE.SET_NAME('IBY', 'IBY_204402_JSERVLET_ERROR');
1817 FND_MSG_PUB.Add;
1818 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1819 END IF;
1820
1821 --show_table_debug(l_names);
1822 --show_table_debug(l_values);
1823
1824 /* Retrieve name-value pairs stored in l_names and l_values, and map
1825 them to the output record, table: x_qrytrxnrespsum_rec, x_qrytrxnrespdet_tbl
1826 */
1827
1828 FOR i IN 1..l_names.COUNT LOOP
1829
1830 --MAPPING SUMMARY RESPONSE RECORD
1831 IF l_names(i) = 'OapfStatus' THEN
1832 x_qrytrxnrespsum_rec.Response.Status := TO_NUMBER(l_values(i));
1833 ELSIF l_names(i) = 'OapfCode' THEN
1834 x_qrytrxnrespsum_rec.Response.ErrCode := l_values(i);
1835 ELSIF l_names(i) = 'OapfCause' THEN
1836 x_qrytrxnrespsum_rec.Response.ErrMessage := l_values(i);
1837 ELSIF l_names(i) = 'OapfNlsLang' THEN
1838 x_qrytrxnrespsum_rec.Response.NLS_LANG := l_values(i);
1839 ELSIF l_names(i) = 'OapfErrLocation' THEN
1840 x_qrytrxnrespsum_rec.ErrorLocation := TO_NUMBER(l_values(i));
1841 ELSIF l_names(i) = 'OapfVendErrCode' THEN
1842 x_qrytrxnrespsum_rec.BEPErrCode := l_values(i);
1843 ELSIF l_names(i) = 'OapfVendErrmsg' THEN
1844 x_qrytrxnrespsum_rec.BEPErrMessage := l_values(i);
1845
1846 --MAPPING DETAIL RESPONSE TABLE OF RECORDS
1847 ELSIF INSTR(l_names(i) , 'OapfQryStatus-') <> 0 THEN
1848 l_index := TO_NUMBER(LTRIM(l_names(i), 'OapfQryStatus-'));
1849 x_qrytrxnrespdet_tbl(l_index).Status := TO_NUMBER(l_values(i));
1850 ELSIF INSTR(l_names(i) , 'OapfTransactionId-') <>0 THEN
1851 l_index := TO_NUMBER( LTRIM(l_names(i), 'OapfTransactionId-') );
1852 x_qrytrxnrespdet_tbl(l_index).Trxn_ID := TO_NUMBER(l_values(i));
1853 ELSIF INSTR(l_names(i) , 'OapfTrxnType-') <> 0 THEN
1854 l_index := TO_NUMBER( LTRIM(l_names(i), 'OapfTrxnType-') );
1855 x_qrytrxnrespdet_tbl(l_index).Trxn_Type := TO_NUMBER(l_values(i));
1856
1857 ELSIF INSTR(l_names(i) , 'OapfTrxnDate-') <> 0 THEN
1858 l_index := TO_NUMBER( LTRIM(l_names(i), 'OapfTrxnDate-') );
1859 x_qrytrxnrespdet_tbl(l_index).Trxn_Date := TO_DATE(l_values(i), 'YYYY-MM-DD');
1860 ELSIF INSTR(l_names(i) , 'OapfPmtInstrType-') <> 0 THEN
1861 l_index := TO_NUMBER( LTRIM(l_names(i), 'OapfPmtInstrType-') );
1862 x_qrytrxnrespdet_tbl(l_index).PmtInstr_Type := l_values(i);
1863 ELSIF INSTR(l_names(i) , 'OapfQryPrice-') <> 0 THEN
1864 l_index := TO_NUMBER( LTRIM(l_names(i), 'OapfQryPrice-') );
1865 x_qrytrxnrespdet_tbl(l_index).Price := FND_NUMBER.CANONICAL_TO_NUMBER(l_values(i));
1866 ELSIF INSTR(l_names(i) , 'OapfQryCurr-') <> 0 THEN
1867 l_index := TO_NUMBER( LTRIM(l_names(i), 'OapfQryCurr-') );
1868 x_qrytrxnrespdet_tbl(l_index).Currency := l_values(i);
1869 ELSIF INSTR(l_names(i) , 'OapfAuthcode-') <> 0 THEN
1870 l_index := TO_NUMBER( LTRIM(l_names(i), 'OapfAuthcode-') );
1871 x_qrytrxnrespdet_tbl(l_index).Authcode := l_values(i);
1872
1873 ELSIF INSTR(l_names(i) , 'OapfRefcode-') <> 0 THEN
1874 l_index := TO_NUMBER( LTRIM(l_names(i), 'OapfRefcode-') );
1875 x_qrytrxnrespdet_tbl(l_index).Refcode := l_values(i);
1876 ELSIF INSTR(l_names(i) , 'OapfAVScode-') <> 0 THEN
1877 l_index := TO_NUMBER( LTRIM(l_names(i), 'OapfAVScode-') );
1878 x_qrytrxnrespdet_tbl(l_index).AVScode := l_values(i);
1879 ELSIF INSTR(l_names(i) , 'OapfAcquirer-') <> 0 THEN
1880 l_index := TO_NUMBER( LTRIM(l_names(i), 'OapfAcquirer-') );
1881 x_qrytrxnrespdet_tbl(l_index).Acquirer := l_values(i);
1882 ELSIF INSTR(l_names(i) , 'OapfVpsBatchId-') <> 0 THEN
1883 l_index := TO_NUMBER( LTRIM(l_names(i), 'OapfVpsBatchId-') );
1884 x_qrytrxnrespdet_tbl(l_index).VpsBatch_ID := l_values(i);
1885
1886 ELSIF INSTR(l_names(i) , 'OapfAuxMsg-') <> 0 THEN
1887 l_index := TO_NUMBER( LTRIM(l_names(i), 'OapfAuxMsg-') );
1888 x_qrytrxnrespdet_tbl(l_index).AuxMsg := l_values(i);
1889 ELSIF INSTR(l_names(i) , 'OapfErrLocation-') <> 0 THEN
1890 l_index := TO_NUMBER( LTRIM(l_names(i), 'OapfErrLocation-') );
1891 x_qrytrxnrespdet_tbl(l_index).ErrorLocation := TO_NUMBER(l_values(i));
1892 ELSIF INSTR(l_names(i) , 'OapfVendErrCode-') <> 0 THEN
1893 l_index := TO_NUMBER( LTRIM(l_names(i), 'OapfVendErrCode-') );
1894 x_qrytrxnrespdet_tbl(l_index).BEPErrCode := l_values(i);
1895 ELSIF INSTR(l_names(i) , 'OapfVendErrmsg-') <> 0 THEN
1896 l_index := TO_NUMBER( LTRIM(l_names(i), 'OapfVendErrmsg-') );
1897 x_qrytrxnrespdet_tbl(l_index).BEPErrMessage := l_values(i);
1898 END IF;
1899
1900 END LOOP;
1901
1902 -- Setting API return status to 'U' if iPayment response status is not 0.
1903 IF (NOT trxn_status_success(x_qrytrxnrespsum_rec.Response.Status)) THEN
1904 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1905 END IF;
1906
1907 -- END OF BODY OF API
1908
1909
1910 -- Standard check of p_commit.
1911 /*
1912 IF FND_API.To_Boolean( p_commit ) THEN
1913 COMMIT WORK;
1914 END IF;
1915 */
1916
1917 -- Standard call to get message count and if count is 1, get message info.
1918 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1919 p_data => x_msg_data
1920 );
1921 EXCEPTION
1922 WHEN FND_API.G_EXC_ERROR THEN
1923 --ROLLBACK TO OraPmtQryTrxn_PUB;
1924 x_return_status := FND_API.G_RET_STS_ERROR ;
1925 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1926 p_data => x_msg_data
1927 );
1928 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1929 --ROLLBACK TO OraPmtQryTrxn_PUB;
1930 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1931 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1932 p_data => x_msg_data
1933 );
1934 WHEN OTHERS THEN
1935 --ROLLBACK TO OraPmtQryTrxn_PUB;
1936 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1937 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1938 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_api_name);
1939 END IF;
1940
1941 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1942 p_data => x_msg_data
1943 );
1944
1945 END OraPmtQryTrxn;
1946
1947 --------------------------------------------------------------------------------------------
1948 --5. OraPmtCapture
1949 -- Start of comments
1950 -- API name : OraPmtCapture
1951 -- Type : Public
1952 -- Pre-reqs : None
1953 -- Function : Handles capture of funds from Payer accounts for authorized
1954 -- Payment Requests in case of CreditCard instruments.
1955 -- Parameters :
1956 -- IN : p_api_version IN NUMBER Required
1957 -- p_init_msg_list IN VARCHAR2 Optional
1958 -- p_commit IN VARCHAR2 Optional
1959 -- p_validation_level IN NUMBER Optional
1960 -- p_ecapp_id IN NUMBER Required
1961 -- p_capturetrxn_rec IN CaptureTrxn_rec_type Required
1962 -- Version :
1963 -- Current version 1.0
1964 -- Previous version 1.0
1965 -- Initial version 1.0
1966 -- End of comments
1967 --------------------------------------------------------------------------------------------
1968 PROCEDURE OraPmtCapture ( p_api_version IN NUMBER,
1969 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1970 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1971 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1972 p_ecapp_id IN NUMBER,
1973 p_capturetrxn_rec IN CaptureTrxn_rec_type,
1974 x_return_status OUT NOCOPY VARCHAR2,
1975 x_msg_count OUT NOCOPY NUMBER,
1976 x_msg_data OUT NOCOPY VARCHAR2,
1977 x_capresp_rec OUT NOCOPY CaptureResp_rec_type
1978 ) IS
1979
1980 l_get_baseurl VARCHAR2(2000);
1981 --The following 3 variables are meant for output of
1982 --get_baseurl procedure.
1983 l_status_url VARCHAR2(2000);
1984 l_msg_count_url NUMBER := 0;
1985 l_msg_data_url VARCHAR2(2000);
1986
1987
1988 l_api_name CONSTANT VARCHAR2(30) := 'OraPmtCapture';
1989 l_oapf_action CONSTANT VARCHAR2(30) := 'oraPmtCapture';
1990 l_api_version CONSTANT NUMBER := 1.0;
1991
1992 TYPE l_v240_tbl IS TABLE of VARCHAR2(240) INDEX BY BINARY_INTEGER;
1993 l_url VARCHAR2(4000) ;
1994 l_html VARCHAR2(7000) ;
1995 l_names v240_tbl_type;
1996 l_values v240_tbl_type;
1997
1998 --The following 3 variables are meant for unpack_results_url procedure.
1999 l_status NUMBER := 0;
2000 l_errcode NUMBER := 0;
2001 l_errmessage VARCHAR2(2000) := 'Success';
2002
2003 -- for NLS bug fix #1692300 - 4/3/2001 jleybovi
2004 --
2005 l_db_nls p_capturetrxn_rec.NLS_LANG%TYPE := NULL;
2006 l_ecapp_nls p_capturetrxn_rec.NLS_LANG%TYPE := NULL;
2007
2008 BEGIN
2009 -- Standard Start of API savepoint
2010 --SAVEPOINT OraPmtCapture_PUB;
2011
2012 /* ***** Performing Validations, Initializations
2013 for Standard IN, OUT Parameters ***** */
2014
2015 -- Standard call to check for call compatibility.
2016 IF NOT FND_API.Compatible_API_Call ( l_api_version,
2017 p_api_version,
2018 l_api_name,
2019 G_PKG_NAME )
2020 THEN
2021 FND_MESSAGE.SET_NAME('IBY', 'IBY_204400_API_VER_MISMATCH');
2022 FND_MSG_PUB.Add;
2023 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2024 END IF;
2025
2026 -- Initialize message list if p_init_msg_list is set to TRUE.
2027 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2028 FND_MSG_PUB.initialize;
2029 END IF;
2030
2031 -- Verifying if validation level is FULL, which is expected for PUBLIC APIs.
2032 IF (p_validation_level <> g_validation_level) THEN
2033 FND_MESSAGE.SET_NAME('IBY', 'IBY_204401_VAL_LEVEL_ERROR');
2034 FND_MSG_PUB.Add;
2035 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2036 END IF;
2037
2038 -- Initialize API return status to success
2039 x_return_status := FND_API.G_RET_STS_SUCCESS;
2040
2041 -- START OF BODY OF API
2042
2043 get_baseurl(l_get_baseurl);
2044
2045 -- Construct the full URL to send to the ECServlet.
2046 l_url := l_get_baseurl;
2047
2048 l_db_nls := iby_utility_pvt.get_local_nls();
2049 l_ecapp_nls := p_capturetrxn_rec.NLS_LANG;
2050
2051 --1. MANDATORY INPUT PARAMETERS
2052 check_mandatory('OapfAction', l_oapf_action, l_url, l_db_nls, l_ecapp_nls);
2053 check_mandatory('OapfECAppId', to_char(p_ecapp_id), l_url, l_db_nls, l_ecapp_nls);
2054 check_mandatory('OapfTransactionId', to_char(p_capturetrxn_rec.Trxn_ID), l_url, l_db_nls, l_ecapp_nls);
2055 check_mandatory('OapfPrice', FND_NUMBER.NUMBER_TO_CANONICAL(p_capturetrxn_rec.Price), l_url, l_db_nls, l_ecapp_nls);
2056 check_mandatory('OapfCurr', p_capturetrxn_rec.Currency, l_url, l_db_nls, l_ecapp_nls);
2057
2058 /*
2059 --check if mode is supplied, if not use 'ONLINE' default
2060 IF p_capturetrxn_rec.PmtMode IS NULL THEN
2061 check_optional('OapfMode', 'ONLINE', l_url, l_db_nls, l_ecapp_nls);
2062 ELSE
2063 check_optional('OapfMode', p_capturetrxn_rec.PmtMode, l_url, l_db_nls, l_ecapp_nls);
2064 END IF;
2065 */
2066 -- the mode has to be mandatory as per the specifications
2067 check_mandatory('OapfMode', p_capturetrxn_rec.PmtMode, l_url, l_db_nls, l_ecapp_nls);
2068
2069 --2. Payment Mode related Conditional Mandatory Input Parameters
2070 IF (p_capturetrxn_rec.PmtMode = 'OFFLINE') THEN
2071 check_mandatory('OapfSchedDate', to_char(p_capturetrxn_rec.Settlement_Date, 'YYYY-MM-DD'), l_url, l_db_nls, l_ecapp_nls);
2072 END IF;
2073
2074 --OPTIONAL INPUT PARAMETERS
2075 check_optional('OapfNlsLang', p_capturetrxn_rec.NLS_LANG, l_url, l_db_nls, l_ecapp_nls);
2076 check_optional('OapfTrxnRef', p_capturetrxn_rec.TrxnRef, l_url, l_db_nls, l_ecapp_nls);
2077 -- Remove ampersand from the last input parameter.
2078 l_url := RTRIM(l_url,'&');
2079
2080 -- escape_ulr_chars() does this now
2081 --
2082 -- ^^^ Replace blank characters with + sign. ^^^
2083 --l_url := REPLACE(l_url,' ','+');
2084
2085 --show_input_debug(l_url);
2086 --dbms_output.put_line(l_url);
2087 -- Send HTTP request to ipayment servlet
2088 --l_html := UTL_HTTP.REQUEST(l_url);
2089 SEND_REQUEST(l_url, l_html);
2090
2091 --dbms_output.put_line(l_html);
2092
2093 --show_output_debug(l_html);
2094
2095 -- Parse the resulting HTML page body
2096 UNPACK_RESULTS_URL(l_html,l_names,l_values, l_status, l_errcode, l_errmessage);
2097
2098
2099 --Raising Exception to handle errors in unpacking resulting html file.
2100 IF (l_status = -1) THEN
2101 FND_MESSAGE.SET_NAME('IBY', 'IBY_204403_HTML_UNPACK_ERROR');
2102 FND_MSG_PUB.Add;
2103 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2104 END IF;
2105
2106 --Raising Exception to handle Servlet related errors.
2107 IF (l_names.COUNT = 0) THEN
2108 FND_MESSAGE.SET_NAME('IBY', 'IBY_204402_JSERVLET_ERROR');
2109 FND_MSG_PUB.Add;
2110 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2111 END IF;
2112
2113 -- Assign output parameters to output record x_capresp_rec
2114 FOR i IN 1..l_names.COUNT LOOP
2115
2116 --Payment Server GENERIC Response
2117 IF l_names(i) = 'OapfStatus' THEN
2118 x_capresp_rec.Response.Status := TO_NUMBER(l_values(i));
2119 ELSIF l_names(i) = 'OapfCode' THEN
2120 x_capresp_rec.Response.ErrCode := l_values(i);
2121 ELSIF l_names(i) = 'OapfCause' THEN
2122 x_capresp_rec.Response.ErrMessage := l_values(i);
2123 ELSIF l_names(i) = 'OapfNlsLang' THEN
2124 x_capresp_rec.Response.NLS_LANG := l_values(i);
2125
2126 --CAPTURE Operation Related Response
2127 ELSIF l_names(i) = 'OapfTransactionId' THEN
2128 x_capresp_rec.Trxn_ID := TO_NUMBER(l_values(i));
2129 ELSIF l_names(i) = 'OapfTrxnType' THEN
2130 x_capresp_rec.Trxn_Type := TO_NUMBER(l_values(i));
2131 ELSIF l_names(i) = 'OapfTrxnDate' THEN
2132 x_capresp_rec.Trxn_Date := TO_DATE(l_values(i), 'YYYY-MM-DD');
2133 ELSIF l_names(i) = 'OapfPmtInstrType' THEN
2134 x_capresp_rec.PmtInstr_Type := l_values(i);
2135 ELSIF l_names(i) = 'OapfRefcode' THEN
2136 x_capresp_rec.Refcode := l_values(i);
2137 ELSIF l_names(i) = 'OapfErrLocation' THEN
2138 x_capresp_rec.ErrorLocation := l_values(i);
2139 ELSIF l_names(i) = 'OapfVendErrCode' THEN
2140 x_capresp_rec.BEPErrCode := l_values(i);
2141 ELSIF l_names(i) = 'OapfVendErrmsg' THEN
2142 x_capresp_rec.BEPErrmessage := l_values(i);
2143
2144 --OFFLINE Payment Mode Related Response
2145 ELSIF l_names(i) = 'OapfEarliestSettlementDate' THEN
2146 x_capresp_rec.OffLineResp.EarliestSettlement_Date := TO_DATE(l_values(i), 'YYYY-MM-DD');
2147 ELSIF l_names(i) = 'OapfSchedDate' THEN
2148 x_capresp_rec.OffLineResp.Scheduled_Date := TO_DATE(l_values(i), 'YYYY-MM-DD');
2149 END IF;
2150
2151 END LOOP;
2152
2153 -- Setting API return status to 'U' if iPayment response status is not 0.
2154 IF (NOT trxn_status_success(x_capresp_rec.Response.Status)) THEN
2155 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2156 END IF;
2157
2158 -- END OF BODY OF API
2159
2160 -- Standard check of p_commit.
2161 /*
2162 IF FND_API.To_Boolean( p_commit ) THEN
2163 COMMIT WORK;
2164 END IF;
2165 */
2166
2167 -- Standard call to get message count and if count is 1, get message info.
2168 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
2169 p_data => x_msg_data
2170 );
2171 EXCEPTION
2172 WHEN FND_API.G_EXC_ERROR THEN
2173 --ROLLBACK TO OraPmtCapture_PUB;
2174 x_return_status := FND_API.G_RET_STS_ERROR ;
2175 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
2176 p_data => x_msg_data
2177 );
2178 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2179 --ROLLBACK TO OraPmtCapture_PUB;
2180 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2181 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
2182 p_data => x_msg_data
2183 );
2184 WHEN OTHERS THEN
2185 --ROLLBACK TO OraPmtCapture_PUB;
2186 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2187 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2188 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_api_name);
2189 END IF;
2190
2191 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
2192 p_data => x_msg_data
2193 );
2194
2195 END OraPmtCapture;
2196
2197 --------------------------------------------------------------------------------------------
2198 --6. OraPmtReturn
2199 -- Start of comments
2200 -- API name : OraPmtReturn
2201 -- Type : Public
2202 -- Pre-reqs : None
2203 -- Function : Handles return of funds to Payer accounts for
2204 -- existing Payment request transactions.
2205 -- Parameters :
2206 -- IN : p_api_version IN NUMBER Required
2207 -- p_init_msg_list IN VARCHAR2 Optional
2208 -- p_commit IN VARCHAR2 Optional
2209 -- p_validation_level IN NUMBER Optional
2210 -- p_ecapp_id IN NUMBER Required
2211 -- p_returntrxn_rec IN ReturnTrxn_rec_type Required
2212 -- Version :
2213 -- Current version 1.0
2214 -- Previous version 1.0
2215 -- Initial version 1.0
2216 -- End of comments
2217 --------------------------------------------------------------------------------------------
2218 PROCEDURE OraPmtReturn ( p_api_version IN NUMBER,
2219 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2220 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2221 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2222 p_ecapp_id IN NUMBER,
2223 p_returntrxn_rec IN ReturnTrxn_rec_type,
2224 x_return_status OUT NOCOPY VARCHAR2,
2225 x_msg_count OUT NOCOPY NUMBER,
2226 x_msg_data OUT NOCOPY VARCHAR2,
2227 x_retresp_rec OUT NOCOPY ReturnResp_rec_type
2228 ) IS
2229
2230
2231 l_get_baseurl VARCHAR2(2000);
2232 --The following 3 variables are meant for output of
2233 --get_baseurl procedure.
2234 l_status_url VARCHAR2(2000);
2235 l_msg_count_url NUMBER := 0;
2236 l_msg_data_url VARCHAR2(2000);
2237
2238 l_api_name CONSTANT VARCHAR2(30) := 'OraPmtReturn';
2239 l_oapf_action CONSTANT VARCHAR2(30) := 'oraPmtReturn';
2240 l_api_version CONSTANT NUMBER := 1.0;
2241
2242 TYPE l_v240_tbl IS TABLE of VARCHAR2(240) INDEX BY BINARY_INTEGER;
2243 l_url VARCHAR2(4000) ;
2244 l_html VARCHAR2(7000) ;
2245 l_names v240_tbl_type;
2246 l_values v240_tbl_type;
2247
2248 --The following 3 variables are meant for unpack_results_url procedure.
2249 l_status NUMBER := 0;
2250 l_errcode NUMBER := 0;
2251 l_errmessage VARCHAR2(2000) := 'Success';
2252
2253 -- for NLS bug fix #1692300 - 4/3/2001 jleybovi
2254 --
2255 l_db_nls p_returntrxn_rec.NLS_LANG%TYPE := NULL;
2256 l_ecapp_nls p_returntrxn_rec.NLS_LANG%TYPE := NULL;
2257
2258 BEGIN
2259 -- Standard Start of API savepoint
2260 --SAVEPOINT OraPmtReturn_PUB;
2261
2262 /* ***** Performing Validations, Initializations
2263 for Standard IN, OUT Parameters ***** */
2264
2265 -- Standard call to check for call compatibility.
2266 IF NOT FND_API.Compatible_API_Call ( l_api_version,
2267 p_api_version,
2268 l_api_name,
2269 G_PKG_NAME )
2270 THEN
2271 FND_MESSAGE.SET_NAME('IBY', 'IBY_204400_API_VER_MISMATCH');
2272 FND_MSG_PUB.Add;
2273 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2274 END IF;
2275
2276 -- Initialize message list if p_init_msg_list is set to TRUE.
2277 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2278 FND_MSG_PUB.initialize;
2279 END IF;
2280
2281 -- Verifying if validation level is FULL, which is expected for PUBLIC APIs.
2282 IF (p_validation_level <> g_validation_level) THEN
2283 FND_MESSAGE.SET_NAME('IBY', 'IBY_204401_VAL_LEVEL_ERROR');
2284 FND_MSG_PUB.Add;
2285 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2286 END IF;
2287
2288
2289 -- Initialize API return status to success
2290 x_return_status := FND_API.G_RET_STS_SUCCESS;
2291
2292 -- START OF BODY OF API
2293
2294 get_baseurl(l_get_baseurl);
2295
2296 -- Construct the full URL to send to the ECServlet.
2297 l_url := l_get_baseurl;
2298
2299 l_db_nls := iby_utility_pvt.get_local_nls();
2300 l_ecapp_nls := p_returntrxn_rec.NLS_LANG;
2301
2302 --Mandatory Input Parameters
2303 check_mandatory('OapfAction', l_oapf_action, l_url, l_db_nls, l_ecapp_nls);
2304 check_mandatory('OapfECAppId', to_char(p_ecapp_id), l_url, l_db_nls, l_ecapp_nls);
2305 check_mandatory('OapfTransactionId', to_char(p_returntrxn_rec.Trxn_ID), l_url, l_db_nls, l_ecapp_nls);
2306 check_mandatory('OapfPrice', FND_NUMBER.NUMBER_TO_CANONICAL(p_returntrxn_rec.Price), l_url, l_db_nls, l_ecapp_nls);
2307 check_mandatory('OapfCurr', p_returntrxn_rec.Currency, l_url, l_db_nls, l_ecapp_nls);
2308
2309 /*
2310 --check if mode is supplied, if not, use 'ONLINE' default
2311 IF p_returntrxn_rec.PmtMode IS NULL THEN
2312 check_optional('OapfMode', 'ONLINE', l_url, l_db_nls, l_ecapp_nls);
2313 ELSE
2314 check_optional('OapfMode', p_returntrxn_rec.PmtMode, l_url, l_db_nls, l_ecapp_nls);
2315 END IF;
2316 */
2317 -- the mode has to be mandatory as per the specifications
2318 check_mandatory('OapfMode', p_returntrxn_rec.PmtMode, l_url, l_db_nls, l_ecapp_nls);
2319
2320 --Payment Mode related Conditional Mandatory Input Parameters
2321 IF (p_returntrxn_rec.PmtMode = 'OFFLINE') THEN
2322 check_mandatory('OapfSchedDate', to_char(p_returntrxn_rec.Settlement_Date, 'YYYY-MM-DD'), l_url, l_db_nls, l_ecapp_nls);
2323 END IF;
2324
2325 --Optional Input Parameters
2326 check_optional('OapfNlsLang', p_returntrxn_rec.NLS_LANG, l_url, l_db_nls, l_ecapp_nls);
2327 check_optional('OapfTrxnRef', p_returntrxn_rec.TrxnRef, l_url, l_db_nls, l_ecapp_nls);
2328
2329 -- Remove ampersand from the last input parameter.
2330 l_url := RTRIM(l_url,'&');
2331
2332 -- already done by escape_url_chars()
2333 --
2334 -- ^^^Replace blank characters with + sign.^^^
2335 --l_url := REPLACE(l_url,' ','+');
2336
2337 -- Send HTTP request to ipayment servlet
2338 -- l_html := UTL_HTTP.REQUEST(l_url);
2339 SEND_REQUEST(l_url, l_html);
2340
2341 -- Parse the resulting HTML page
2342 UNPACK_RESULTS_URL(l_html,l_names,l_values, l_status, l_errcode, l_errmessage);
2343
2344 --Raising Exception to handle errors in unpacking resulting html file.
2345 IF (l_status = -1) THEN
2346 FND_MESSAGE.SET_NAME('IBY', 'IBY_204403_HTML_UNPACK_ERROR');
2347 FND_MSG_PUB.Add;
2348 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2349 END IF;
2350
2351 --Raising Exception to handle Servlet related errors.
2352 IF (l_names.COUNT = 0) THEN
2353 FND_MESSAGE.SET_NAME('IBY', 'IBY_204402_JSERVLET_ERROR');
2354 FND_MSG_PUB.Add;
2355 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2356 END IF;
2357
2358 -- Assign output parameters to output record x_retresp_rec
2359 FOR i IN 1..l_names.COUNT LOOP
2360
2361 --Payment Server GENERIC Response
2362 IF l_names(i) = 'OapfStatus' THEN
2363 x_retresp_rec.Response.Status := TO_NUMBER(l_values(i));
2364 ELSIF l_names(i) = 'OapfCode' THEN
2365 x_retresp_rec.Response.ErrCode := l_values(i);
2366 ELSIF l_names(i) = 'OapfCause' THEN
2367 x_retresp_rec.Response.ErrMessage := l_values(i);
2368 ELSIF l_names(i) = 'OapfNlsLang' THEN
2369 x_retresp_rec.Response.NLS_LANG := l_values(i);
2370
2371 --RETURN Operation Related Response
2372 ELSIF l_names(i) = 'OapfTransactionId' THEN
2373 x_retresp_rec.Trxn_ID := TO_NUMBER(l_values(i));
2374 ELSIF l_names(i) = 'OapfTrxnType' THEN
2375 x_retresp_rec.Trxn_Type := TO_NUMBER(l_values(i));
2376 ELSIF l_names(i) = 'OapfTrxnDate' THEN
2377 x_retresp_rec.Trxn_Date := TO_DATE(l_values(i), 'YYYY-MM-DD');
2378 ELSIF l_names(i) = 'OapfPmtInstrType' THEN
2379 x_retresp_rec.PmtInstr_Type := l_values(i);
2380 ELSIF l_names(i) = 'OapfRefcode' THEN
2381 x_retresp_rec.Refcode := l_values(i);
2382 ELSIF l_names(i) = 'OapfErrLocation' THEN
2383 x_retresp_rec.ErrorLocation := l_values(i);
2384 ELSIF l_names(i) = 'OapfVendErrCode' THEN
2385 x_retresp_rec.BEPErrCode := l_values(i);
2386 ELSIF l_names(i) = 'OapfVendErrmsg' THEN
2387 x_retresp_rec.BEPErrmessage := l_values(i);
2388
2389 --OFFLINE Payment Mode Related Response
2390 ELSIF l_names(i) = 'OapfEarliestSettlementDate' THEN
2391 x_retresp_rec.OffLineResp.EarliestSettlement_Date := TO_DATE(l_values(i), 'YYYY-MM-DD');
2392 ELSIF l_names(i) = 'OapfSchedDate' THEN
2393 x_retresp_rec.OffLineResp.Scheduled_Date := TO_DATE(l_values(i), 'YYYY-MM-DD');
2394 END IF;
2395 END LOOP;
2396
2397 -- Setting API return status to 'U' if iPayment response status is not 0.
2398 IF (NOT trxn_status_success(x_retresp_rec.Response.Status)) THEN
2399 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2400 END IF;
2401
2402 -- END OF BODY OF API
2403
2404 -- Standard check of p_commit.
2405 /*
2406 IF FND_API.To_Boolean( p_commit ) THEN
2407 COMMIT WORK;
2408 END IF;
2409 */
2410
2411 -- Standard call to get message count and if count is 1, get message info.
2412 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
2413 p_data => x_msg_data
2414 );
2415 EXCEPTION
2416 WHEN FND_API.G_EXC_ERROR THEN
2417 --ROLLBACK TO OraPmtReturn_PUB;
2418 x_return_status := FND_API.G_RET_STS_ERROR ;
2419 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
2420 p_data => x_msg_data
2421 );
2422 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2423 --ROLLBACK TO OraPmtReturn_PUB;
2424 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2425 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
2426 p_data => x_msg_data
2427 );
2428 WHEN OTHERS THEN
2429 --ROLLBACK TO OraPmtReturn_PUB;
2430 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2431 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2432 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_api_name);
2433 END IF;
2434
2435 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
2436 p_data => x_msg_data
2437 );
2438
2439 END OraPmtReturn;
2440
2441 --------------------------------------------------------------------------------------------
2442 --7. OraPmtVoid
2443 -- Start of comments
2444 -- API name : OraPmtVoid
2445 -- Type : Public
2446 -- Pre-reqs : None
2447 -- Function : Handles Void Operations for existing Payment transactions.
2448 -- Can be performed on Capture and Return operation for all
2449 -- BEPs on Authorization operation for some BEPs.
2450 -- Parameters :
2451 -- IN : p_api_version IN NUMBER Required
2452 -- p_init_msg_list IN VARCHAR2 Optional
2453 -- p_commit IN VARCHAR2 Optional
2454 -- p_validation_level IN NUMBER Optional
2455 -- p_ecapp_id IN NUMBER Required
2456 -- p_voidtrxn_rec IN VoidTrxn_rec_type Required
2457 -- Version :
2458 -- Current version 1.0
2459 -- Previous version 1.0
2460 -- Initial version 1.0
2461 -- End of comments
2462 --------------------------------------------------------------------------------------------
2463 PROCEDURE OraPmtVoid ( p_api_version IN NUMBER,
2464 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2465 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2466 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2467 p_ecapp_id IN NUMBER,
2468 p_voidtrxn_rec IN VoidTrxn_rec_type,
2469 x_return_status OUT NOCOPY VARCHAR2,
2470 x_msg_count OUT NOCOPY NUMBER,
2471 x_msg_data OUT NOCOPY VARCHAR2,
2472 x_voidresp_rec OUT NOCOPY VoidResp_rec_type
2473 ) IS
2474
2475 l_get_baseurl VARCHAR2(2000);
2476 --The following 3 variables are meant for output of
2477 --get_baseurl procedure.
2478 l_status_url VARCHAR2(2000);
2479 l_msg_count_url NUMBER := 0;
2480 l_msg_data_url VARCHAR2(2000);
2481
2482 l_api_name CONSTANT VARCHAR2(30) := 'OraPmtVoid';
2483 l_oapf_action CONSTANT VARCHAR2(30) := 'oraPmtVoid';
2484 l_api_version CONSTANT NUMBER := 1.0;
2485
2486 TYPE l_v240_tbl IS TABLE of VARCHAR2(240) INDEX BY BINARY_INTEGER;
2487 l_url VARCHAR2(4000) ;
2488 l_html VARCHAR2(7000) ;
2489 l_names v240_tbl_type;
2490 l_values v240_tbl_type;
2491
2492 --The following 3 variables are meant for unpack_results_url procedure.
2493 l_status NUMBER := 0;
2494 l_errcode NUMBER := 0;
2495 l_errmessage VARCHAR2(2000) := 'Success';
2496
2497 -- for NLS bug fix #1692300 - 4/3/2001 jleybovi
2498 --
2499 l_db_nls p_voidtrxn_rec.NLS_LANG%TYPE := NULL;
2500 l_ecapp_nls p_voidtrxn_rec.NLS_LANG%TYPE := NULL;
2501
2502 v_trxnTimestamp DATE := NULL;
2503 BEGIN
2504 -- Standard Start of API savepoint
2505 --SAVEPOINT OraPmtVoid_PUB;
2506
2507 /* ***** Performing Validations, Initializations
2508 for Standard IN, OUT Parameters ***** */
2509
2510 -- Standard call to check for call compatibility.
2511 IF NOT FND_API.Compatible_API_Call ( l_api_version,
2512 p_api_version,
2513 l_api_name,
2514 G_PKG_NAME )
2515 THEN
2516 FND_MESSAGE.SET_NAME('IBY', 'IBY_204400_API_VER_MISMATCH');
2517 FND_MSG_PUB.Add;
2518 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2519 END IF;
2520
2521 -- Initialize message list if p_init_msg_list is set to TRUE.
2522 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2523 FND_MSG_PUB.initialize;
2524 END IF;
2525
2526 -- Verifying if validation level is FULL, which is expected for PUBLIC APIs.
2527 IF (p_validation_level <> g_validation_level) THEN
2528 FND_MESSAGE.SET_NAME('IBY', 'IBY_204401_VAL_LEVEL_ERROR');
2529 FND_MSG_PUB.Add;
2530 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2531 END IF;
2532
2533 -- Initialize API return status to success
2534 x_return_status := FND_API.G_RET_STS_SUCCESS;
2535
2536 -- START OF BODY OF API
2537
2538 get_baseurl(l_get_baseurl);
2539
2540 -- Construct the full URL to send to the ECServlet.
2541 l_url := l_get_baseurl;
2542
2543 l_db_nls := iby_utility_pvt.get_local_nls();
2544 l_ecapp_nls := p_voidtrxn_rec.NLS_LANG;
2545
2546 --Mandatory Input Parameters
2547 check_mandatory('OapfAction', l_oapf_action, l_url, l_db_nls, l_ecapp_nls);
2548 check_mandatory('OapfECAppId', to_char(p_ecapp_id), l_url, l_db_nls, l_ecapp_nls);
2549 check_mandatory('OapfTransactionId', to_char(p_voidtrxn_rec.Trxn_ID), l_url, l_db_nls, l_ecapp_nls);
2550 check_mandatory('OapfTrxnType', to_char(p_voidtrxn_rec.Trxn_Type), l_url, l_db_nls, l_ecapp_nls);
2551
2552 /*
2553 --check if mode is supplied, if not use 'ONLINE' default
2554 IF p_voidtrxn_rec.PmtMode IS NULL THEN
2555 check_optional('OapfMode', 'ONLINE', l_url, l_db_nls, l_ecapp_nls);
2556 ELSE
2557 check_optional('OapfMode', p_voidtrxn_rec.PmtMode, l_url, l_db_nls, l_ecapp_nls);
2558 END IF;
2559 */
2560 -- the mode has to be mandatory as per the specifications
2561 check_mandatory('OapfMode', p_voidtrxn_rec.PmtMode, l_url, l_db_nls, l_ecapp_nls);
2562
2563 --Payment Mode related Conditional Mandatory Input Parameters
2564 IF (p_voidtrxn_rec.PmtMode = 'OFFLINE') THEN
2565 check_mandatory('OapfSchedDate', to_char(p_voidtrxn_rec.Settlement_Date, 'YYYY-MM-DD'), l_url, l_db_nls, l_ecapp_nls);
2566 END IF;
2567
2568 --Optional Input Parameters
2569 check_optional('OapfNlsLang', p_voidtrxn_rec.NLS_LANG, l_url, l_db_nls, l_ecapp_nls);
2570 check_optional('OapfTrxnRef', p_voidtrxn_rec.TrxnRef, l_url, l_db_nls, l_ecapp_nls);
2571
2572 -- Remove ampersand from the last input parameter.
2573 l_url := RTRIM(l_url,'&');
2574
2575 -- done by escape_url_chars()
2576 --
2577 -- ^^^ Replace blank characters with + sign. ^^^
2578 -- l_url := REPLACE(l_url,' ','+');
2579
2580 --dbms_output.put_line('before utl_http call');
2581 --show_input_debug(l_url);
2582
2583 -- Send HTTP request to ipayment servlet
2584 -- l_html := UTL_HTTP.REQUEST(l_url);
2585 SEND_REQUEST(l_url, l_html);
2586
2587 --dbms_output.put_line('after utl_http call');
2588 --show_output_debug(l_html);
2589
2590 -- Parse the resulting HTML page
2591 UNPACK_RESULTS_URL(l_html,l_names,l_values, l_status, l_errcode, l_errmessage);
2592
2593 --show_table_debug(l_names);
2594 --show_table_debug(l_values);
2595
2596 --Raising Exception to handle errors in unpacking resulting html file.
2597 IF (l_status = -1) THEN
2598 FND_MESSAGE.SET_NAME('IBY', 'IBY_204403_HTML_UNPACK_ERROR');
2599 FND_MSG_PUB.Add;
2600 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2601 END IF;
2602
2603 --Raising Exception to handle Servlet related errors.
2604 IF (l_names.COUNT = 0) THEN
2605 FND_MESSAGE.SET_NAME('IBY', 'IBY_204402_JSERVLET_ERROR');
2606 FND_MSG_PUB.Add;
2607 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2608 END IF;
2609
2610 --dbms_output.put_line('after successful unpacking');
2611
2612 -- Assign output parameters to output record x_retresp_rec
2613 FOR i IN 1..l_names.COUNT LOOP
2614
2615 --Payment Server Related Generic Response
2616 IF l_names(i) = 'OapfStatus' THEN
2617 x_voidresp_rec.Response.Status := TO_NUMBER(l_values(i));
2618 ELSIF l_names(i) = 'OapfCode' THEN
2619 x_voidresp_rec.Response.ErrCode := l_values(i);
2620 ELSIF l_names(i) = 'OapfCause' THEN
2621 x_voidresp_rec.Response.ErrMessage := l_values(i);
2622 ELSIF l_names(i) = 'OapfNlsLang' THEN
2623 x_voidresp_rec.Response.NLS_LANG := l_values(i);
2624
2625 --VOID Operation Specific Response
2626 ELSIF l_names(i) = 'OapfTransactionId' THEN
2627 x_voidresp_rec.Trxn_ID := TO_NUMBER(l_values(i));
2628 ELSIF l_names(i) = 'OapfTrxnType' THEN
2629 x_voidresp_rec.Trxn_Type := TO_NUMBER(l_values(i));
2630 ELSIF l_names(i) = 'OapfTrxnDate' THEN
2631 x_voidresp_rec.Trxn_Date := TO_DATE(l_values(i), 'YYYY-MM-DD');
2632 --
2633 -- for bug #1649833 to return a more accurate trxn
2634 -- date use the 'OapfTimestamp' parameter if present
2635 --
2636
2637 ELSIF l_names(i) = 'OapfTimestamp' THEN
2638 v_trxnTimestamp := TO_DATE(l_values(i),'YYYYMMDDHH24MISS');
2639
2640 --
2641 ELSIF l_names(i) = 'OapfPmtInstrType' THEN
2642 x_voidresp_rec.PmtInstr_Type := l_values(i);
2643 ELSIF l_names(i) = 'OapfRefcode' THEN
2644 x_voidresp_rec.Refcode := l_values(i);
2645 ELSIF l_names(i) = 'OapfErrLocation' THEN
2646 x_voidresp_rec.ErrorLocation := l_values(i);
2647 ELSIF l_names(i) = 'OapfVendErrCode' THEN
2648 x_voidresp_rec.BEPErrCode := l_values(i);
2649 ELSIF l_names(i) = 'OapfVendErrmsg' THEN
2650 x_voidresp_rec.BEPErrMessage := l_values(i);
2651
2652 --OFFLINE Payment Mode Related Response
2653 ELSIF l_names(i) = 'OapfEarliestSettlementDate' THEN
2654 x_voidresp_rec.OffLineResp.EarliestSettlement_Date := TO_DATE(l_values(i), 'YYYY-MM-DD');
2655 ELSIF l_names(i) = 'OapfSchedDate' THEN
2656 x_voidresp_rec.OffLineResp.Scheduled_Date := TO_DATE(l_values(i), 'YYYY-MM-DD');
2657
2658 END IF;
2659 END LOOP;
2660
2661 -- use the more accurate time stampe parameter if returned
2662 --
2663
2664 IF NOT (v_trxnTimestamp IS NULL) THEN
2665 x_voidresp_rec.Trxn_Date := v_trxnTimestamp;
2666 END IF;
2667
2668 -- Setting API return status to 'U' if iPayment response status is not 0.
2669 IF (NOT trxn_status_success(x_voidresp_rec.Response.Status)) THEN
2670 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2671 END IF;
2672
2673 -- END OF BODY OF API
2674
2675
2676 -- Standard check of p_commit.
2677 /*
2678 IF FND_API.To_Boolean( p_commit ) THEN
2679 COMMIT WORK;
2680 END IF;
2681 */
2682
2683 -- Standard call to get message count and if count is 1, get message info.
2684 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
2685 p_data => x_msg_data
2686 );
2687 EXCEPTION
2688 WHEN FND_API.G_EXC_ERROR THEN
2689 --ROLLBACK TO OraPmtVoid_PUB;
2690 x_return_status := FND_API.G_RET_STS_ERROR ;
2691 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
2692 p_data => x_msg_data
2693 );
2694 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2695 --ROLLBACK TO OraPmtVoid_PUB;
2696 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2697 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
2698 p_data => x_msg_data
2699 );
2700 WHEN OTHERS THEN
2701 --ROLLBACK TO OraPmtVoid_PUB;
2702 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2703 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2704 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_api_name);
2705 END IF;
2706
2707 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
2708 p_data => x_msg_data
2709 );
2710
2711 END OraPmtVoid;
2712
2713 --------------------------------------------------------------------------------------------
2714 --8. OraPmtCredit
2715 -- Start of comments
2716 -- API name : OraPmtCredit
2717 -- Type : Public
2718 -- Pre-reqs : None
2719 -- Function : Handles Credit transaction operations such as making
2720 -- credit payments/discounts to payer accounts.
2721 -- Parameters :
2722 -- IN : p_api_version IN NUMBER Required
2723 -- p_init_msg_list IN VARCHAR2 Optional
2724 -- p_commit IN VARCHAR2 Optional
2725 -- p_validation_level IN NUMBER Optional
2726 -- p_ecapp_id IN NUMBER Required
2727 -- p_payee_rec IN Payee_rec_type Required
2728 -- p_pmtinstr_rec IN PmtInstr_rec_type Required
2729 -- p_tangible_rec IN Tangible_rec_type Required
2730 -- p_credittrxn_rec IN CreditTrxn_rec_type Required
2731 -- Version :
2732 -- Current version 1.0
2733 -- Previous version 1.0
2734 -- Initial version 1.0
2735 -- End of comments
2736 --------------------------------------------------------------------------------------------
2737 PROCEDURE OraPmtCredit ( p_api_version IN NUMBER,
2738 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2739 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2740 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2741 p_ecapp_id IN NUMBER,
2742 p_payee_rec IN Payee_rec_type,
2743 p_pmtinstr_rec IN PmtInstr_rec_type,
2744 p_tangible_rec IN Tangible_rec_type,
2745 p_credittrxn_rec IN CreditTrxn_rec_type,
2746 x_return_status OUT NOCOPY VARCHAR2,
2747 x_msg_count OUT NOCOPY NUMBER,
2748 x_msg_data OUT NOCOPY VARCHAR2,
2749 x_creditresp_rec OUT NOCOPY CreditResp_rec_type
2750 ) IS
2751
2752 l_get_baseurl VARCHAR2(2000);
2753 --The following 3 variables are meant for output of
2754 --get_baseurl procedure.
2755 l_status_url VARCHAR2(2000);
2756 l_msg_count_url NUMBER := 0;
2757 l_msg_data_url VARCHAR2(2000);
2758
2759 l_api_name CONSTANT VARCHAR2(30) := 'OraPmtCredit';
2760 l_oapf_action CONSTANT VARCHAR2(30) := 'oraPmtCredit';
2761 l_api_version CONSTANT NUMBER := 1.0;
2762
2763 TYPE l_v240_tbl IS TABLE of VARCHAR2(240) INDEX BY BINARY_INTEGER;
2764 l_url VARCHAR2(4000) ;
2765 l_html VARCHAR2(7000) ;
2766 l_names v240_tbl_type;
2767 l_values v240_tbl_type;
2768
2769 --The following 3 variables are meant for unpack_results_url procedure.
2770 l_status NUMBER := 0;
2771 l_errcode NUMBER := 0;
2772 l_errmessage VARCHAR2(2000) := 'Success';
2773
2774 -- for NLS bug fix #1692300 - 4/3/2001 jleybovi
2775 --
2776 l_db_nls p_credittrxn_rec.NLS_LANG%TYPE := NULL;
2777 l_ecapp_nls p_credittrxn_rec.NLS_LANG%TYPE := NULL;
2778
2779 --Defining a local variable to hold the payment instrument type.
2780 l_pmtinstr_type VARCHAR2(200);
2781
2782 BEGIN
2783 -- Standard Start of API savepoint
2784 --SAVEPOINT OraPmtCredit_PUB;
2785
2786 /* ***** Performing Validations, Initializations
2787 for Standard IN, OUT Parameters ***** */
2788
2789 -- Standard call to check for call compatibility.
2790 IF NOT FND_API.Compatible_API_Call ( l_api_version,
2791 p_api_version,
2792 l_api_name,
2793 G_PKG_NAME )
2794 THEN
2795 FND_MESSAGE.SET_NAME('IBY', 'IBY_204400_API_VER_MISMATCH');
2796 FND_MSG_PUB.Add;
2797 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2798 END IF;
2799
2800 -- Initialize message list if p_init_msg_list is set to TRUE.
2801 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2802 FND_MSG_PUB.initialize;
2803 END IF;
2804
2805 -- Verifying if validation level is FULL, which is expected for PUBLIC APIs.
2806 IF (p_validation_level <> g_validation_level) THEN
2807 FND_MESSAGE.SET_NAME('IBY', 'IBY_204401_VAL_LEVEL_ERROR');
2808 FND_MSG_PUB.Add;
2809 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2810 END IF;
2811
2812 -- Initialize API return status to success
2813 x_return_status := FND_API.G_RET_STS_SUCCESS;
2814
2815
2816 -- START OF BODY OF API
2817
2818 get_baseurl(l_get_baseurl);
2819
2820 -- Construct the full URL to send to the ECServlet.
2821 l_url := l_get_baseurl;
2822
2823 l_db_nls := iby_utility_pvt.get_local_nls();
2824 l_ecapp_nls := p_credittrxn_rec.NLS_LANG;
2825
2826 --1. MANDATORY INPUT PARAMETERS
2827 check_mandatory('OapfAction', l_oapf_action, l_url, l_db_nls, l_ecapp_nls);
2828 check_mandatory('OapfECAppId', to_char(p_ecapp_id), l_url, l_db_nls, l_ecapp_nls);
2829 check_mandatory('OapfStoreId', p_payee_rec.Payee_ID, l_url, l_db_nls, l_ecapp_nls);
2830
2831 check_mandatory('OapfPayerId', p_credittrxn_rec.Payer_Party_ID, l_url, l_db_nls, l_ecapp_nls);
2832 /*
2833 --check if mode is supplied, if not, use 'ONLINE' default
2834 IF p_credittrxn_rec.PmtMode IS NULL THEN
2835 check_optional('OapfMode', 'ONLINE', l_url, l_db_nls, l_ecapp_nls);
2836 ELSE
2837 check_optional('OapfMode', p_credittrxn_rec.PmtMode, l_url, l_db_nls, l_ecapp_nls);
2838 END IF;
2839 */
2840 -- the mode has to be mandatory as per the specifications
2841 check_mandatory('OapfMode', p_credittrxn_rec.PmtMode, l_url, l_db_nls, l_ecapp_nls);
2842
2843 --Tangible Related Mandatory Input
2844 check_mandatory('OapfOrderId', p_tangible_rec.Tangible_ID, l_url, l_db_nls, l_ecapp_nls);
2845 check_mandatory('OapfPrice', FND_NUMBER.NUMBER_TO_CANONICAL(p_tangible_rec.Tangible_Amount), l_url, l_db_nls, l_ecapp_nls);
2846 check_mandatory('OapfCurr', p_tangible_rec.Currency_Code, l_url, l_db_nls, l_ecapp_nls);
2847
2848 -- mandatory org_id and default org_type to OPERATING_UNIT, so check as
2849 -- mandatory as well.
2850 check_mandatory('OapfOrgId', to_char(p_credittrxn_rec.Org_ID), l_url, l_db_nls, l_ecapp_nls);
2851 check_mandatory('OapfOrgType', to_char(p_credittrxn_rec.Org_type), l_url, l_db_nls, l_ecapp_nls);
2852
2853
2854 l_pmtinstr_type := p_pmtinstr_rec.PmtInstr_Type;
2855
2856 -- mandatory instrument registration
2857 check_mandatory('OapfPmtRegId', to_char(p_pmtinstr_rec.PmtInstr_ID), l_url, l_db_nls, l_ecapp_nls);
2858 check_mandatory('OapfPmtInstrType', l_pmtinstr_type, l_url, l_db_nls, l_ecapp_nls);
2859
2860 --CONDITIONALLY MANDATORY INPUT PARAMETERS
2861
2862 --2. CONDITIONALLY MANDATORY INPUT PARAMETERS
2863
2864 --Payment Mode related Conditional Mandatory Input Parameters
2865 IF (p_credittrxn_rec.PmtMode = 'OFFLINE') THEN
2866 check_mandatory('OapfSchedDate', to_char(p_credittrxn_rec.Settlement_Date, 'YYYY-MM-DD'), l_url, l_db_nls, l_ecapp_nls);
2867 END IF;
2868
2869
2870 --OPTIONAL INPUT PARAMETERS
2871
2872 --Instrument related optional input
2873 check_optional('OapfInstrName', p_pmtinstr_rec.PmtInstr_ShortName, l_url, l_db_nls, l_ecapp_nls);
2874
2875 -- optional payment channel code
2876 check_optional('OapfPmtChannelCode', UPPER(p_credittrxn_rec.payment_channel_code), l_url, l_db_nls, l_ecapp_nls);
2877
2878 --Tangible related optional input
2879 check_optional('OapfRefNumber', p_tangible_rec.RefInfo, l_url, l_db_nls, l_ecapp_nls);
2880 check_optional('OapfMemo', p_tangible_rec.Memo, l_url, l_db_nls, l_ecapp_nls);
2881 check_optional('OapfAcctNumber', p_tangible_rec.Acct_Num, l_url, l_db_nls, l_ecapp_nls);
2882 check_optional('OapfOrderMedium', p_tangible_rec.OrderMedium, l_url, l_db_nls, l_ecapp_nls);
2883 -- no need to pass the EFTAuthMethod in the Credit API. It is not called for ACH transactions
2884
2885 check_optional('OapfRetry', p_credittrxn_rec.Retry_Flag, l_url, l_db_nls, l_ecapp_nls);
2886 check_optional('OapfNlsLang', p_credittrxn_rec.NLS_LANG, l_url, l_db_nls, l_ecapp_nls);
2887 check_optional('OapfTrxnRef', p_credittrxn_rec.TrxnRef, l_url, l_db_nls, l_ecapp_nls);
2888
2889 -- Remove ampersand from the last input parameter.
2890 l_url := RTRIM(l_url,'&');
2891
2892 -- already done in escape_url_chars
2893 --
2894 -- ^^^ Replace blank characters with + sign. ^^^
2895 -- l_url := REPLACE(l_url,' ','+');
2896 --show_input_debug(l_url);
2897
2898 -- Send HTTP request to ipayment servlet
2899 -- l_html := UTL_HTTP.REQUEST(l_url);
2900 SEND_REQUEST(l_url, l_html);
2901
2902 --show_output_debug(l_html);
2903
2904 -- Parse the resulting HTML page
2905 UNPACK_RESULTS_URL(l_html,l_names,l_values, l_status, l_errcode, l_errmessage);
2906
2907 --Raising Exception to handle errors in unpacking resulting html file.
2908 IF (l_status = -1) THEN
2909 FND_MESSAGE.SET_NAME('IBY', 'IBY_204403_HTML_UNPACK_ERROR');
2910 FND_MSG_PUB.Add;
2911 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2912 END IF;
2913
2914 --Raising Exception to handle Servlet related errors.
2915 IF (l_names.COUNT = 0) THEN
2916 FND_MESSAGE.SET_NAME('IBY', 'IBY_204402_JSERVLET_ERROR');
2917 FND_MSG_PUB.Add;
2918 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2919 END IF;
2920
2921 --show_table_debug(l_names);
2922 --show_table_debug(l_values);
2923
2924 -- Assign output parameters to output record x_creditresp_rec
2925 FOR i IN 1..l_names.COUNT LOOP
2926
2927 --Payment Server Related Generic Response
2928 IF l_names(i) = 'OapfStatus' THEN
2929 x_creditresp_rec.Response.Status := TO_NUMBER(l_values(i));
2930 ELSIF l_names(i) = 'OapfCode' THEN
2931 x_creditresp_rec.Response.ErrCode := l_values(i);
2932 ELSIF l_names(i) = 'OapfCause' THEN
2933 x_creditresp_rec.Response.ErrMessage := l_values(i);
2934 ELSIF l_names(i) = 'OapfNlsLang' THEN
2935 x_creditresp_rec.Response.NLS_LANG := l_values(i);
2936
2937 --Credit Operation Related Response
2938 ELSIF l_names(i) = 'OapfTransactionId' THEN
2939 x_creditresp_rec.Trxn_ID := TO_NUMBER(l_values(i));
2940 ELSIF l_names(i) = 'OapfTrxnType' THEN
2941 x_creditresp_rec.Trxn_Type := TO_NUMBER(l_values(i));
2942 ELSIF l_names(i) = 'OapfTrxnDate' THEN
2943 x_creditresp_rec.Trxn_Date := TO_DATE(l_values(i), 'YYYY-MM-DD');
2944 ELSIF l_names(i) = 'OapfPmtInstrType' THEN
2945 x_creditresp_rec.PmtInstr_Type := l_values(i);
2946 ELSIF l_names(i) = 'OapfRefCode' THEN
2947 x_creditresp_rec.Refcode := l_values(i);
2948 ELSIF l_names(i) = 'OapfErrLocation' THEN
2949 x_creditresp_rec.ErrorLocation := TO_NUMBER(l_values(i));
2950 ELSIF l_names(i) = 'OapfVendErrCode' THEN
2951 x_creditresp_rec.BEPErrCode := l_values(i);
2952 ELSIF l_names(i) = 'OapfVendErrmsg' THEN
2953 x_creditresp_rec.BEPErrMessage := l_values(i);
2954
2955 --OFFLINE Payment Mode Related Response
2956 ELSIF l_names(i) = 'OapfEarliestSettlementDate' THEN
2957 x_creditresp_rec.OffLineResp.EarliestSettlement_Date := TO_DATE(l_values(i), 'YYYY-MM-DD');
2958 ELSIF l_names(i) = 'OapfSchedDate' THEN
2959 x_creditresp_rec.OffLineResp.Scheduled_Date := TO_DATE(l_values(i), 'YYYY-MM-DD');
2960
2961 END IF;
2962 END LOOP;
2963
2964 -- Setting API return status to 'U' if iPayment response status is not 0.
2965 IF (NOT trxn_status_success(x_creditresp_rec.Response.Status)) THEN
2966 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2967 END IF;
2968
2969 -- END OF BODY OF API
2970
2971 -- Standard check of p_commit.
2972 /*
2973 IF FND_API.To_Boolean( p_commit ) THEN
2974 COMMIT WORK;
2975 END IF;
2976 */
2977
2978 -- Standard call to get message count and if count is 1, get message info.
2979 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
2980 p_data => x_msg_data
2981 );
2982 EXCEPTION
2983 WHEN FND_API.G_EXC_ERROR THEN
2984 --ROLLBACK TO OraPmtCredit_PUB;
2985 x_return_status := FND_API.G_RET_STS_ERROR ;
2986 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
2987 p_data => x_msg_data
2988 );
2989 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2990 --ROLLBACK TO OraPmtCredit_PUB;
2991 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2992 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
2993 p_data => x_msg_data
2994 );
2995 WHEN OTHERS THEN
2996 --ROLLBACK TO OraPmtCredit_PUB;
2997 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2998 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2999 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_api_name);
3000 END IF;
3001
3002 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
3003 p_data => x_msg_data
3004 );
3005
3006 END OraPmtCredit;
3007
3008 --------------------------------------------------------------------------------------------
3009 --9. OraPmtInq
3010 -- Start of comments
3011 -- API name : OraPmtInq
3012 -- Type : Public
3013 -- Pre-reqs : None
3014 -- Function : Provides high-level payment information such as Payee,
3015 -- Payer, Instrument, and Tangible related information.
3016 -- Parameters :
3017 -- IN : p_api_version IN NUMBER Required
3018 -- p_init_msg_list IN VARCHAR2 Optional
3019 -- p_commit IN VARCHAR2 Optional
3020 -- p_validation_level IN NUMBER Optional
3021 -- p_ecapp_id IN NUMBER Required
3022 -- p_tid IN NUMBER Required
3023 -- Version :
3024 -- Current version 1.0
3025 -- Previous version 1.0
3026 -- Initial version 1.0
3027 -- End of comments
3028 --------------------------------------------------------------------------------------------
3029 PROCEDURE OraPmtInq ( p_api_version IN NUMBER,
3030 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3031 p_commit IN VARCHAR2 := FND_API.G_FALSE,
3032 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
3033 p_ecapp_id IN NUMBER,
3034 p_tid IN NUMBER,
3035 x_return_status OUT NOCOPY VARCHAR2,
3036 x_msg_count OUT NOCOPY NUMBER,
3037 x_msg_data OUT NOCOPY VARCHAR2,
3038 x_inqresp_rec OUT NOCOPY InqResp_rec_type
3039 ) IS
3040
3041
3042 l_api_name CONSTANT VARCHAR2(30) := 'OraPmtInq';
3043 l_oapf_action CONSTANT VARCHAR2(30) := 'oraPmtInq';
3044 l_api_version CONSTANT NUMBER := 1.0;
3045
3046 TYPE l_v240_tbl IS TABLE of VARCHAR2(240) INDEX BY BINARY_INTEGER;
3047 l_url VARCHAR2(4000) ;
3048 l_html VARCHAR2(7000) ;
3049 l_names v240_tbl_type;
3050 l_values v240_tbl_type;
3051
3052
3053 CURSOR pmtinq_csr IS
3054 SELECT payeeid,
3055 payerid,
3056 payerinstrid,
3057 tangibleid,
3058 amount,
3059 currencyNameCode,
3060 acctNo,
3061 refInfo,
3062 memo
3063 FROM IBY_PAYMENTS_V
3064 WHERE ecappid = p_ecapp_id
3065 AND transactionid = p_tid
3066 ORDER BY payeeid, payerid, tangibleid;
3067
3068 BEGIN
3069
3070 -- Standard Start of API savepoint
3071 --SAVEPOINT OraPmtInq_PUB;
3072
3073 /* ***** Performing Validations, Initializations
3074 for Standard IN, OUT Parameters ***** */
3075
3076 -- Standard call to check for call compatibility.
3077 IF NOT FND_API.Compatible_API_Call ( l_api_version,
3078 p_api_version,
3079 l_api_name,
3080 G_PKG_NAME )
3081 THEN
3082 FND_MESSAGE.SET_NAME('IBY', 'IBY_204400_API_VER_MISMATCH');
3083 FND_MSG_PUB.Add;
3084 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3085 END IF;
3086
3087 -- Initialize message list if p_init_msg_list is set to TRUE.
3088 IF FND_API.to_Boolean( p_init_msg_list ) THEN
3089 FND_MSG_PUB.initialize;
3090 END IF;
3091
3092 -- Verifying if validation level is FULL, which is expected for PUBLIC APIs.
3093 IF (p_validation_level <> g_validation_level) THEN
3094 FND_MESSAGE.SET_NAME('IBY', 'IBY_204401_VAL_LEVEL_ERROR');
3095 FND_MSG_PUB.Add;
3096 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3097 END IF;
3098
3099 -- Initialize API return status to success
3100 x_return_status := FND_API.G_RET_STS_SUCCESS;
3101
3102 -- START OF BODY OF API
3103
3104 --dbms_output.put_line('Before opening cursor');
3105
3106 OPEN pmtinq_csr;
3107 FETCH pmtinq_csr
3108 INTO x_inqresp_rec.Payee.Payee_ID,
3109 x_inqresp_rec.Payer.Party_ID,
3110 x_inqresp_rec.PmtInstr.PmtInstr_ID,
3111 x_inqresp_rec.Tangible.Tangible_ID,
3112 x_inqresp_rec.Tangible.Tangible_Amount,
3113 x_inqresp_rec.Tangible.Currency_Code,
3114 x_inqresp_rec.Tangible.Acct_Num,
3115 x_inqresp_rec.Tangible.RefInfo,
3116 x_inqresp_rec.Tangible.Memo;
3117
3118 IF (pmtinq_csr%ROWCOUNT = 0) THEN
3119 x_inqresp_rec.Response.Status := 3;
3120 -- NOTE: Response.ErrCode, Response.ErrMessage are not populated
3121 -- since that would imply hardcoding the message text here (NLS issues)
3122 -- Instead it is sent to the API message list.
3123 FND_MESSAGE.SET_NAME('IBY', 'IBY_204404_CURSOR_ERROR');
3124 FND_MSG_PUB.Add;
3125 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3126 END IF;
3127
3128 CLOSE pmtinq_csr;
3129
3130 --dbms_output.put_line('after closing cursor');
3131
3132 x_inqresp_rec.Response.Status := 0;
3133 -- END OF BODY OF API
3134
3135
3136 -- Standard check of p_commit.
3137 /*
3138 IF FND_API.To_Boolean( p_commit ) THEN
3139 COMMIT WORK;
3140 END IF;
3141 */
3142
3143 -- Standard call to get message count and if count is 1, get message info.
3144 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
3145 p_data => x_msg_data
3146 );
3147 EXCEPTION
3148 WHEN FND_API.G_EXC_ERROR THEN
3149 --ROLLBACK TO OraPmtInq_PUB;
3150 x_return_status := FND_API.G_RET_STS_ERROR ;
3151 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
3152 p_data => x_msg_data
3153 );
3154 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3155 --ROLLBACK TO OraPmtInq_PUB;
3156 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3157 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
3158 p_data => x_msg_data
3159 );
3160 WHEN OTHERS THEN
3161 --ROLLBACK TO OraPmtInq_PUB;
3162 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3163 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3164 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_api_name);
3165 END IF;
3166
3167 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
3168 p_data => x_msg_data
3169 );
3170
3171 END OraPmtInq;
3172
3173 --------------------------------------------------------------------------------------------
3174 --10. OraPmtCloseBatch
3175 -- Start of comments
3176 -- API name : OraPmtCloseBatch
3177 -- Type : Public
3178 -- Pre-reqs : None
3179 -- Function : Closes the existing current batch of transactions and
3180 -- opens a new batch. The operaions that can be included
3181 -- are Capture, Return, and Credit. This operation is
3182 -- mandatory for terminal-based merchants.
3183 -- Parameters :
3184 -- IN : p_api_version IN NUMBER Required
3185 -- p_init_msg_list IN VARCHAR2 Optional
3186 -- p_commit IN VARCHAR2 Optional
3187 -- p_validation_level IN NUMBER Optional
3188 -- p_ecapp_id IN NUMBER Required
3189 -- p_batchtrxn_rec IN BatchTrxn_rec_type Required
3190 -- Version :
3191 -- Current version 1.0
3192 -- Previous version 1.0
3193 -- Initial version 1.0
3194 -- End of comments
3195 --------------------------------------------------------------------------------------------
3196 PROCEDURE OraPmtCloseBatch ( p_api_version IN NUMBER,
3197 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3198 p_commit IN VARCHAR2 := FND_API.G_FALSE,
3199 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
3200 p_ecapp_id IN NUMBER,
3201 p_batchtrxn_rec IN BatchTrxn_rec_type,
3202 x_return_status OUT NOCOPY VARCHAR2,
3203 x_msg_count OUT NOCOPY NUMBER,
3204 x_msg_data OUT NOCOPY VARCHAR2,
3205 x_closebatchrespsum_rec OUT NOCOPY BatchRespSum_rec_type,
3206 x_closebatchrespdet_tbl OUT NOCOPY BatchRespDet_tbl_type
3207 ) IS
3208
3209 l_get_baseurl VARCHAR2(2000);
3210 --The following 3 variables are meant for output of
3211 --get_baseurl procedure.
3212 l_status_url VARCHAR2(2000);
3213 l_msg_count_url NUMBER := 0;
3214 l_msg_data_url VARCHAR2(2000);
3215
3216 l_api_name CONSTANT VARCHAR2(30) := 'OraPmtCloseBatch';
3217 l_oapf_action CONSTANT VARCHAR2(30) := 'oraPmtCloseBatch';
3218 l_api_version CONSTANT NUMBER := 1.0;
3219
3220 TYPE l_v240_tbl IS TABLE of VARCHAR2(240) INDEX BY BINARY_INTEGER;
3221 l_url VARCHAR2(4000) ;
3222 l_html VARCHAR2(7000) ;
3223 l_names v240_tbl_type;
3224 l_values v240_tbl_type;
3225
3226 --The following 3 variables are meant for unpack_results_url procedure.
3227 l_status NUMBER := 0;
3228 l_errcode NUMBER := 0;
3229 l_errmessage VARCHAR2(2000) := 'Success';
3230
3231 --Local variable to handle detail response table of records index
3232 l_index NUMBER := 0;
3233
3234 -- for NLS bug fix #1692300 - 4/3/2001 jleybovi
3235 --
3236 l_db_nls p_batchtrxn_rec.NLS_LANG%TYPE := NULL;
3237 l_ecapp_nls p_batchtrxn_rec.NLS_LANG%TYPE := NULL;
3238
3239 BEGIN
3240
3241 -- Standard Start of API savepoint
3242 --SAVEPOINT OraPmtCloseBatch_PUB;
3243
3244 /* ***** Performing Validations, Initializations
3245 for Standard IN, OUT Parameters ***** */
3246
3247 -- Standard call to check for call compatibility.
3248 IF NOT FND_API.Compatible_API_Call ( l_api_version,
3249 p_api_version,
3250 l_api_name,
3251 G_PKG_NAME )
3252 THEN
3253 FND_MESSAGE.SET_NAME('IBY', 'IBY_204400_API_VER_MISMATCH');
3254 FND_MSG_PUB.Add;
3255 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3256 END IF;
3257
3258 -- Initialize message list if p_init_msg_list is set to TRUE.
3259 IF FND_API.to_Boolean( p_init_msg_list ) THEN
3260 FND_MSG_PUB.initialize;
3261 END IF;
3262
3263 -- Verifying if validation level is FULL, which is expected for PUBLIC APIs.
3264 IF (p_validation_level <> g_validation_level) THEN
3265 FND_MESSAGE.SET_NAME('IBY', 'IBY_204401_VAL_LEVEL_ERROR');
3266 FND_MSG_PUB.Add;
3267 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3268 END IF;
3269
3270
3271 -- Initialize API return status to success
3272 x_return_status := FND_API.G_RET_STS_SUCCESS;
3273
3274 -- START OF BODY OF API
3275
3276 get_baseurl(l_get_baseurl);
3277
3278 -- Construct the full URL to send to the ECServlet.
3279 l_url := l_get_baseurl;
3280
3281 l_db_nls := iby_utility_pvt.get_local_nls();
3282 l_ecapp_nls:= p_batchtrxn_rec.NLS_LANG;
3283
3284 --1. MANDATORY INPUT PARAMETERS
3285
3286 check_mandatory('OapfAction', l_oapf_action, l_url, l_db_nls, l_ecapp_nls);
3287 check_mandatory('OapfECAppId', to_char(p_ecapp_id), l_url, l_db_nls, l_ecapp_nls);
3288 check_mandatory('OapfStoreId', p_batchtrxn_rec.Payee_ID, l_url, l_db_nls, l_ecapp_nls);
3289 check_mandatory('OapfMerchBatchId', p_batchtrxn_rec.MerchBatch_ID, l_url, l_db_nls, l_ecapp_nls);
3290 --
3291 -- fix for bug # 2129295
3292 --
3293 check_mandatory('OapfBEPSuffix', p_batchtrxn_rec.BEP_Suffix, l_url, l_db_nls, l_ecapp_nls);
3294 check_mandatory('OapfBEPAccount', p_batchtrxn_rec.BEP_Account, l_url, l_db_nls, l_ecapp_nls);
3295 check_mandatory('OapfBEPAccount', p_batchtrxn_rec.BEP_Account, l_url, l_db_nls, l_ecapp_nls);
3296 check_mandatory('OapfAccountProfile', p_batchtrxn_rec.Account_Profile, l_url, l_db_nls, l_ecapp_nls);
3297
3298 /*
3299 --check if mode is supplied, if not use 'ONLINE' default
3300 IF p_batchtrxn_rec.PmtMode IS NULL THEN
3301 check_optional('OapfMode', 'ONLINE', l_url, l_db_nls, l_ecapp_nls);
3302 ELSE
3303 check_optional('OapfMode', p_batchtrxn_rec.PmtMode, l_url, l_db_nls, l_ecapp_nls);
3304 END IF;
3305 */
3306 -- the mode has to be mandatory as per the specifications
3307 check_mandatory('OapfMode', p_batchtrxn_rec.PmtMode, l_url, l_db_nls, l_ecapp_nls);
3308
3309 --2. Payment Mode related Conditionally Mandatory Input Parameters
3310 IF (p_batchtrxn_rec.PmtMode = 'OFFLINE') THEN
3311 check_mandatory('OapfSchedDate', to_char(p_batchtrxn_rec.Settlement_Date, 'YYYY-MM-DD'), l_url, l_db_nls, l_ecapp_nls);
3312 END IF;
3313
3314 --3. OPTIONAL INPUT PARAMETERS
3315
3316 check_optional('OapfPmtType', p_batchtrxn_rec.PmtType, l_url, l_db_nls, l_ecapp_nls);
3317 check_optional('OapfPmtInstrType',p_batchtrxn_rec.PmtInstrType,l_url,l_db_nls,l_ecapp_nls);
3318 check_optional('OapfNlsLang', p_batchtrxn_rec.NLS_LANG, l_url, l_db_nls, l_ecapp_nls);
3319
3320 -- Remove ampersand from the last input parameter.
3321 l_url := RTRIM(l_url,'&');
3322
3323 -- now done in escape_url_chars()
3324 --
3325 -- ^^^^ Replace blank characters with + sign. ^^^
3326 -- l_url := REPLACE(l_url,' ','+');
3327
3328 --dbms_output.put_line('before utl_http call');
3329 --show_input_debug(l_url);
3330
3331 -- Send http request to the payment server.
3332 -- l_html := UTL_HTTP.REQUEST(l_url);
3333 SEND_REQUEST(l_url, l_html);
3334
3335 --dbms_output.put_line('after utl_http call');
3336 --show_output_debug(l_html);
3337
3338 -- Unpack the results
3339 UNPACK_RESULTS_URL(l_html,l_names,l_values, l_status, l_errcode, l_errmessage);
3340
3341 --Raising Exception to handle errors in unpacking resulting html file.
3342 IF (l_status = -1) THEN
3343 FND_MESSAGE.SET_NAME('IBY', 'IBY_204403_HTML_UNPACK_ERROR');
3344 FND_MSG_PUB.Add;
3345 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3346 END IF;
3347
3348 --Raising Exception to handle Servlet related errors.
3349 IF (l_names.COUNT = 0) THEN
3350 FND_MESSAGE.SET_NAME('IBY', 'IBY_204402_JSERVLET_ERROR');
3351 FND_MSG_PUB.Add;
3352 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3353 END IF;
3354
3355 --show_table_debug(l_names);
3356 --show_table_debug(l_values);
3357
3358 /* Retrieve name-value pairs stored in l_names and l_values, and assign
3359 them to the output table of records: x_closebatchrespdet_tbl
3360 */
3361
3362 FOR i IN 1..l_names.COUNT LOOP
3363
3364 -- MAPPING NAME-VALUE PAIRS TO CLOSEBATCH SUMMARY RESPONSE RECORD
3365
3366 -- Payment Server generic response.
3367 IF l_names(i) = 'OapfStatus' THEN
3368 x_closebatchrespsum_rec.Response.Status := TO_NUMBER(l_values(i));
3369 ELSIF l_names(i) = 'OapfCode' THEN
3370 x_closebatchrespsum_rec.Response.ErrCode := l_values(i);
3371 ELSIF l_names(i) = 'OapfCause' THEN
3372 x_closebatchrespsum_rec.Response.ErrMessage := l_values(i);
3373 ELSIF l_names(i) = 'OapfNlsLang' THEN
3374 x_closebatchrespsum_rec.Response.NLS_LANG := l_values(i);
3375
3376 -- OFFLINE PAYMENT MODE SPECIFIC RESPONSE
3377 ELSIF l_names(i) = 'OapfEarliestSettlementDate' THEN
3378 x_closebatchrespsum_rec.OffLineResp.EarliestSettlement_Date := TO_DATE(l_values(i), 'YYYY-MM-DD');
3379 ELSIF l_names(i) = 'OapfSchedDate' THEN
3380 x_closebatchrespsum_rec.OffLineResp.Scheduled_Date := TO_DATE(l_values(i), 'YYYY-MM-DD');
3381
3382 -- OTHER SUMMARY RECORD RESPONSE OBJECTS
3383 ELSIF l_names(i) = 'OapfNumTrxns' THEN
3384 x_closebatchrespsum_rec.NumTrxns := TO_NUMBER(l_values(i));
3385 ELSIF l_names(i) = 'OapfMerchBatchId' THEN
3386 x_closebatchrespsum_rec.MerchBatch_ID := l_values(i);
3387 ELSIF l_names(i) = 'OapfBatchState' THEN
3388 x_closebatchrespsum_rec.BatchState := TO_NUMBER(l_values(i));
3389 ELSIF l_names(i) = 'OapfBatchDate' THEN
3390 x_closebatchrespsum_rec.BatchDate := TO_DATE(l_values(i), 'YYYY-MM-DD');
3391 ELSIF l_names(i) = 'OapfStoreId' THEN
3392 x_closebatchrespsum_rec.Payee_ID := l_values(i);
3393 ELSIF l_names(i) = 'OapfCreditAmount' THEN
3394 x_closebatchrespsum_rec.Credit_Amount := TO_NUMBER(l_values(i));
3395 ELSIF l_names(i) = 'OapfSalesAmount' THEN
3396 x_closebatchrespsum_rec.Sales_Amount := TO_NUMBER(l_values(i));
3397 ELSIF l_names(i) = 'OapfBatchTotal' THEN
3398 x_closebatchrespsum_rec.Batch_Total := TO_NUMBER(l_values(i));
3399 ELSIF l_names(i) = 'OapfCurr' THEN
3400 x_closebatchrespsum_rec.Currency := l_values(i);
3401 ELSIF l_names(i) = 'OapfVpsBatchID' THEN
3402 x_closebatchrespsum_rec.VpsBatch_ID := l_values(i);
3403 ELSIF l_names(i) = 'OapfGwBatchID' THEN
3404 x_closebatchrespsum_rec.GWBatch_ID := l_values(i);
3405
3406 ELSIF l_names(i) = 'OapfErrLocation' THEN
3407 x_closebatchrespsum_rec.ErrorLocation := TO_NUMBER(l_values(i));
3408 ELSIF l_names(i) = 'OapfVendErrCode' THEN
3409 x_closebatchrespsum_rec.BEPErrCode := l_values(i);
3410 ELSIF l_names(i) = 'OapfVendErrmsg' THEN
3411 x_closebatchrespsum_rec.BEPErrMessage := l_values(i);
3412
3413 -- MAPPING NAME-VALUE PAIRS TO CLOSEBATCH DETAIL RESPONSE TABLE OF RECORDS
3414 ELSIF INSTR(l_names(i), 'OapfTransactionId-') <> 0 THEN
3415 l_index := TO_NUMBER( LTRIM(l_names(i), 'OapfTransactionId-') );
3416 x_closebatchrespdet_tbl(l_index).Trxn_ID := TO_NUMBER(l_values(i));
3417 ELSIF INSTR(l_names(i), 'OapfTrxnType-') <> 0 THEN
3418 l_index := TO_NUMBER( LTRIM(l_names(i), 'OapfTrxnType-') );
3419 x_closebatchrespdet_tbl(l_index).Trxn_Type := TO_NUMBER(l_values(i));
3420 ELSIF INSTR(l_names(i), 'OapfTrxnDate-') <> 0 THEN
3421 l_index := TO_NUMBER( LTRIM(l_names(i), 'OapfTrxnDate-') );
3422 x_closebatchrespdet_tbl(l_index).Trxn_Date := TO_DATE(l_values(i), 'YYYY-MM-DD');
3423
3424 ELSIF INSTR(l_names(i), 'OapfStatus-') <> 0 THEN
3425 l_index := TO_NUMBER( LTRIM(l_names(i), 'OapfStatus-') );
3426 x_closebatchrespdet_tbl(l_index).Status := TO_NUMBER(l_values(i));
3427 ELSIF INSTR(l_names(i), 'OapfBatchErrLocation-') <> 0 THEN
3428 l_index := TO_NUMBER( LTRIM(l_names(i), 'OapfBatchErrLocation-') );
3429 x_closebatchrespdet_tbl(l_index).ErrorLocation := TO_NUMBER(l_values(i));
3430 ELSIF INSTR(l_names(i), 'OapfBatchErrCode-') <> 0 THEN
3431 l_index := TO_NUMBER( LTRIM(l_names(i), 'OapfBatchErrCode-') );
3432 x_closebatchrespdet_tbl(l_index).BEPErrCode := l_values(i);
3433 ELSIF INSTR(l_names(i), 'OapfBatchErrmsg-') <> 0 THEN
3434 l_index := TO_NUMBER( LTRIM(l_names(i), 'OapfBatchErrmsg-') );
3435 x_closebatchrespdet_tbl(l_index).BEPErrMessage := l_values(i);
3436 ELSIF INSTR(l_names(i), 'OapfNlsLang-') <> 0 THEN
3437 l_index := TO_NUMBER( LTRIM(l_names(i), 'OapfNlsLang-') );
3438 x_closebatchrespdet_tbl(l_index).NLS_LANG := l_values(i);
3439
3440 END IF;
3441
3442 END LOOP;
3443
3444 -- Setting API return status to 'U' if iPayment response status is not 0.
3445 IF (NOT trxn_status_success(x_closebatchrespsum_rec.Response.Status)) THEN
3446 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3447 END IF;
3448
3449 -- END OF BODY OF API
3450
3451 -- Standard check of p_commit.
3452 /*
3453 IF FND_API.To_Boolean( p_commit ) THEN
3454 COMMIT WORK;
3455 END IF;
3456 */
3457
3458 -- Standard call to get message count and if count is 1, get message info.
3459 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
3460 p_data => x_msg_data
3461 );
3462 EXCEPTION
3463 WHEN FND_API.G_EXC_ERROR THEN
3464 --ROLLBACK TO OraPmtCloseBatch_PUB;
3465 x_return_status := FND_API.G_RET_STS_ERROR ;
3466 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
3467 p_data => x_msg_data
3468 );
3469 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3470 --ROLLBACK TO OraPmtCloseBatch_PUB;
3471 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3472 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
3473 p_data => x_msg_data
3474 );
3475 WHEN OTHERS THEN
3476 --ROLLBACK TO OraPmtCloseBatch_PUB;
3477 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3478 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3479 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_api_name);
3480 END IF;
3481
3482 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
3483 p_data => x_msg_data
3484 );
3485
3486 END OraPmtCloseBatch;
3487
3488 --------------------------------------------------------------------------------------------
3489 --11. OraPmtQueryBatch
3490 -- Start of comments
3491 -- API name : OraPmtQueryBatch
3492 -- Type : Public
3493 -- Pre-reqs : None
3494 -- Function : Provides an interface to query the status of any previous
3495 -- batch of transactions.
3496 -- Parameters :
3497 -- IN : p_api_version IN NUMBER Required
3498 -- p_init_msg_list IN VARCHAR2 Optional
3499 -- p_commit IN VARCHAR2 Optional
3500 -- p_validation_level IN NUMBER Optional
3501 -- p_ecapp_id IN NUMBER Required
3502 -- p_batchtrxn_rec IN BatchTrxn_rec_type Required
3503 -- Version :
3504 -- Current version 1.0
3505 -- Previous version 1.0
3506 -- Initial version 1.0
3507 -- End of comments
3508 --------------------------------------------------------------------------------------------
3509 PROCEDURE OraPmtQueryBatch ( p_api_version IN NUMBER,
3510 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3511 p_commit IN VARCHAR2 := FND_API.G_FALSE,
3512 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
3513 p_ecapp_id IN NUMBER,
3514 p_batchtrxn_rec IN BatchTrxn_rec_type,
3515 x_return_status OUT NOCOPY VARCHAR2,
3516 x_msg_count OUT NOCOPY NUMBER,
3517 x_msg_data OUT NOCOPY VARCHAR2,
3518 x_qrybatchrespsum_rec OUT NOCOPY BatchRespSum_rec_type,
3519 x_qrybatchrespdet_tbl OUT NOCOPY BatchRespDet_tbl_type
3520 ) IS
3521
3522 l_get_baseurl VARCHAR2(2000);
3523 --The following 3 variables are meant for output of
3524 --get_baseurl procedure.
3525 l_status_url VARCHAR2(2000);
3526 l_msg_count_url NUMBER := 0;
3527 l_msg_data_url VARCHAR2(2000);
3528
3529 l_api_name CONSTANT VARCHAR2(30) := 'OraPmtQueryBatch';
3530 l_oapf_action CONSTANT VARCHAR2(30) := 'oraPmtQueryBatch';
3531 l_api_version CONSTANT NUMBER := 1.0;
3532
3533 TYPE l_v240_tbl IS TABLE of VARCHAR2(240) INDEX BY BINARY_INTEGER;
3534 l_url VARCHAR2(4000) ;
3535 l_html VARCHAR2(7000) ;
3536 l_names v240_tbl_type;
3537 l_values v240_tbl_type;
3538
3539 --The following 3 variables are meant for unpack_results_url procedure.
3540 l_status NUMBER := 0;
3541 l_errcode NUMBER := 0;
3542 l_errmessage VARCHAR2(2000) := 'Success';
3543
3544 --Local variable to handle detail response table of records index
3545 l_index NUMBER := 0;
3546
3547 -- for NLS bug fix #1692300 - 4/3/2001 jleybovi
3548 --
3549 l_db_nls p_batchtrxn_rec.NLS_LANG%TYPE := NULL;
3550 l_ecapp_nls p_batchtrxn_rec.NLS_LANG%TYPE := NULL;
3551
3552 BEGIN
3553
3554 -- Standard Start of API savepoint
3555 --SAVEPOINT OraPmtQueryBatch_PUB;
3556
3557 /* ***** Performing Validations, Initializations
3558 for Standard IN, OUT Parameters ***** */
3559
3560 -- Standard call to check for call compatibility.
3561 IF NOT FND_API.Compatible_API_Call ( l_api_version,
3562 p_api_version,
3563 l_api_name,
3564 G_PKG_NAME )
3565 THEN
3566 FND_MESSAGE.SET_NAME('IBY', 'IBY_204400_API_VER_MISMATCH');
3567 FND_MSG_PUB.Add;
3568 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3569 END IF;
3570
3571 -- Initialize message list if p_init_msg_list is set to TRUE.
3572 IF FND_API.to_Boolean( p_init_msg_list ) THEN
3573 FND_MSG_PUB.initialize;
3574 END IF;
3575
3576 -- Verifying if validation level is FULL, which is expected for PUBLIC APIs.
3577 IF (p_validation_level <> g_validation_level) THEN
3578 FND_MESSAGE.SET_NAME('IBY', 'IBY_204401_VAL_LEVEL_ERROR');
3579 FND_MSG_PUB.Add;
3580 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3581 END IF;
3582
3583 -- Initialize API return status to success
3584 x_return_status := FND_API.G_RET_STS_SUCCESS;
3585
3586
3587 -- START OF BODY OF API
3588
3589 get_baseurl(l_get_baseurl);
3590
3591 -- Construct the full URL to send to the ECServlet.
3592 l_url := l_get_baseurl;
3593
3594 l_db_nls := iby_utility_pvt.get_local_nls();
3595 l_ecapp_nls := p_batchtrxn_rec.NLS_LANG;
3596
3597 --Mandatory Input Parameters
3598 check_mandatory('OapfAction', l_oapf_action, l_url, l_db_nls, l_ecapp_nls);
3599 check_mandatory('OapfECAppId', to_char(p_ecapp_id), l_url, l_db_nls, l_ecapp_nls);
3600 check_mandatory('OapfStoreId', p_batchtrxn_rec.Payee_ID, l_url, l_db_nls, l_ecapp_nls);
3601 check_mandatory('OapfMerchBatchId', p_batchtrxn_rec.MerchBatch_ID, l_url, l_db_nls, l_ecapp_nls);
3602 --
3603 -- fix for bug # 2129295
3604 --
3605 check_mandatory('OapfBEPSuffix', p_batchtrxn_rec.BEP_Suffix, l_url, l_db_nls, l_ecapp_nls);
3606 check_mandatory('OapfBEPAccount', p_batchtrxn_rec.BEP_Account, l_url, l_db_nls, l_ecapp_nls);
3607
3608 /*
3609 --check if mode is supplied, if not use 'ONLINE' default
3610 IF p_batchtrxn_rec.PmtMode IS NULL THEN
3611 check_optional('OapfMode', 'ONLINE', l_url, l_db_nls, l_ecapp_nls);
3612 ELSE
3613 check_optional('OapfMode', p_batchtrxn_rec.PmtMode, l_url, l_db_nls, l_ecapp_nls);
3614 END IF;
3615 */
3616 -- the mode has to be mandatory as per the specifications
3617 check_mandatory('OapfMode', p_batchtrxn_rec.PmtMode, l_url, l_db_nls, l_ecapp_nls);
3618
3619 --Optional Input Parameters
3620 check_optional('OapfPmtType', p_batchtrxn_rec.PmtType, l_url, l_db_nls, l_ecapp_nls);
3621 check_optional('OapfPmtInstrType',p_batchtrxn_rec.PmtInstrType,l_url,l_db_nls,l_ecapp_nls);
3622 check_optional('OapfNlsLang', p_batchtrxn_rec.NLS_LANG, l_url, l_db_nls, l_ecapp_nls);
3623
3624 -- Remove ampersand from the last input parameter.
3625 l_url := RTRIM(l_url,'&');
3626
3627 -- escape_url_chars does this now
3628 --
3629 -- ^^^ Replace blank characters with + sign. ^^^
3630 --l_url := REPLACE(l_url,' ','+');
3631
3632 --show_input_debug(l_url);
3633
3634 -- Send http request to the payment server.
3635 -- l_html := UTL_HTTP.REQUEST(l_url);
3636 SEND_REQUEST(l_url, l_html);
3637
3638 --show_output_debug(l_html);
3639
3640 -- Unpack the results
3641 UNPACK_RESULTS_URL(l_html,l_names,l_values, l_status, l_errcode, l_errmessage);
3642
3643 --Raising Exception to handle errors in unpacking resulting html file.
3644 IF (l_status = -1) THEN
3645 FND_MESSAGE.SET_NAME('IBY', 'IBY_204403_HTML_UNPACK_ERROR');
3646 FND_MSG_PUB.Add;
3647 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3648 END IF;
3649
3650 --Raising Exception to handle Servlet related errors.
3651 IF (l_names.COUNT = 0) THEN
3652 FND_MESSAGE.SET_NAME('IBY', 'IBY_204402_JSERVLET_ERROR');
3653 FND_MSG_PUB.Add;
3654 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3655 END IF;
3656
3657 --show_table_debug(l_names);
3658 --show_table_debug(l_values);
3659
3660 /* Retrieve name-value pairs stored in l_names and l_values, and assign
3661 them to the output table of records: x_qrybatchrespdet_tbl
3662 */
3663
3664 FOR i IN 1..l_names.COUNT LOOP
3665
3666 -- Mapping Name-Value Pairs to QueryBatch summary response record
3667 -- Payment Server generic response.
3668 IF l_names(i) = 'OapfStatus' THEN
3669 x_qrybatchrespsum_rec.Response.Status := TO_NUMBER(l_values(i));
3670 ELSIF l_names(i) = 'OapfCode' THEN
3671 x_qrybatchrespsum_rec.Response.ErrCode := l_values(i);
3672 ELSIF l_names(i) = 'OapfCause' THEN
3673 x_qrybatchrespsum_rec.Response.ErrMessage := l_values(i);
3674 ELSIF l_names(i) = 'OapfNlsLang' THEN
3675 x_qrybatchrespsum_rec.Response.NLS_LANG := l_values(i);
3676
3677 -- OTHER SUMMARY RECORD RESPONSE OBJECTS
3678 ELSIF l_names(i) = 'OapfNumTrxns' THEN
3679 x_qrybatchrespsum_rec.NumTrxns := TO_NUMBER(l_values(i));
3680 ELSIF l_names(i) = 'OapfMerchBatchId' THEN
3681 x_qrybatchrespsum_rec.MerchBatch_ID := l_values(i);
3682 ELSIF l_names(i) = 'OapfBatchState' THEN
3683 x_qrybatchrespsum_rec.BatchState := TO_NUMBER(l_values(i));
3684 ELSIF l_names(i) = 'OapfBatchDate' THEN
3685 x_qrybatchrespsum_rec.BatchDate := TO_DATE(l_values(i), 'YYYY-MM-DD');
3686 ELSIF l_names(i) = 'OapfStoreId' THEN
3687 x_qrybatchrespsum_rec.Payee_ID := l_values(i);
3688 ELSIF l_names(i) = 'OapfCreditAmount' THEN
3689 x_qrybatchrespsum_rec.Credit_Amount := FND_NUMBER.CANONICAL_TO_NUMBER(l_values(i));
3690 ELSIF l_names(i) = 'OapfSalesAmount' THEN
3691 x_qrybatchrespsum_rec.Sales_Amount := FND_NUMBER.CANONICAL_TO_NUMBER(l_values(i));
3692 ELSIF l_names(i) = 'OapfBatchTotal' THEN
3693 x_qrybatchrespsum_rec.Batch_Total := FND_NUMBER.CANONICAL_TO_NUMBER(l_values(i));
3694 ELSIF l_names(i) = 'OapfCurr' THEN
3695 x_qrybatchrespsum_rec.Currency := l_values(i);
3696 ELSIF l_names(i) = 'OapfVpsBatchID' THEN
3697 x_qrybatchrespsum_rec.VpsBatch_ID := l_values(i);
3698 ELSIF l_names(i) = 'OapfGwBatchID' THEN
3699 x_qrybatchrespsum_rec.GWBatch_ID := l_values(i);
3700
3701 ELSIF l_names(i) = 'OapfErrLocation' THEN
3702 x_qrybatchrespsum_rec.ErrorLocation := TO_NUMBER(l_values(i));
3703 ELSIF l_names(i) = 'OapfVendErrCode' THEN
3704 x_qrybatchrespsum_rec.BEPErrCode := l_values(i);
3705 ELSIF l_names(i) = 'OapfVendErrmsg' THEN
3706 x_qrybatchrespsum_rec.BEPErrMessage := l_values(i);
3707
3708 -- MAPPING NAME-VALUE PAIRS TO CLOSEBATCH DETAIL RESPONSE TABLE OF RECORDS
3709 ELSIF INSTR(l_names(i), 'OapfTransactionId-') <> 0 THEN
3710 l_index := TO_NUMBER( LTRIM(l_names(i), 'OapfTransactionId-') );
3711 x_qrybatchrespdet_tbl(l_index).Trxn_ID := TO_NUMBER(l_values(i));
3712 ELSIF INSTR(l_names(i), 'OapfTrxnType-') <> 0 THEN
3713 l_index := TO_NUMBER( LTRIM(l_names(i), 'OapfTrxnType-') );
3714 x_qrybatchrespdet_tbl(l_index).Trxn_Type := TO_NUMBER(l_values(i));
3715 ELSIF INSTR(l_names(i), 'OapfTrxnDate-') <> 0 THEN
3716 l_index := TO_NUMBER( LTRIM(l_names(i), 'OapfTrxnDate-') );
3717 x_qrybatchrespdet_tbl(l_index).Trxn_Date := TO_DATE(l_values(i), 'YYYY-MM-DD');
3718
3719 ELSIF INSTR(l_names(i), 'OapfStatus-') <> 0 THEN
3720 l_index := TO_NUMBER( LTRIM(l_names(i), 'OapfStatus-') );
3721 x_qrybatchrespdet_tbl(l_index).Status := TO_NUMBER(l_values(i));
3722 ELSIF INSTR(l_names(i), 'OapfBatchErrLocation-') <> 0 THEN
3723 l_index := TO_NUMBER( LTRIM(l_names(i), 'OapfBatchErrLocation-') );
3724 x_qrybatchrespdet_tbl(l_index).ErrorLocation := TO_NUMBER(l_values(i));
3725 ELSIF INSTR(l_names(i), 'OapfBatchErrCode-') <> 0 THEN
3726 l_index := TO_NUMBER( LTRIM(l_names(i), 'OapfBatchErrCode-') );
3727 x_qrybatchrespdet_tbl(l_index).BEPErrCode := l_values(i);
3728 ELSIF INSTR(l_names(i), 'OapfBatchErrmsg-') <> 0 THEN
3729 l_index := TO_NUMBER( LTRIM(l_names(i), 'OapfBatchErrmsg-') );
3730 x_qrybatchrespdet_tbl(l_index).BEPErrMessage := l_values(i);
3731 ELSIF INSTR(l_names(i), 'OapfNlsLang-') <> 0 THEN
3732 l_index := TO_NUMBER( LTRIM(l_names(i), 'OapfNlsLang-') );
3733 x_qrybatchrespdet_tbl(l_index).NLS_LANG := l_values(i);
3734
3735 END IF;
3736
3737 END LOOP;
3738
3739 -- Setting API return status to 'U' if iPayment response status is not 0.
3740 IF (NOT trxn_status_success(x_qrybatchrespsum_rec.Response.Status)) THEN
3741 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3742 END IF;
3743
3744 -- END OF BODY OF API
3745
3746
3747 -- Standard check of p_commit.
3748 /*
3749 IF FND_API.To_Boolean( p_commit ) THEN
3750 COMMIT WORK;
3751 END IF;
3752 */
3753
3754 -- Standard call to get message count and if count is 1, get message info.
3755 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
3756 p_data => x_msg_data
3757 );
3758 EXCEPTION
3759 WHEN FND_API.G_EXC_ERROR THEN
3760 --ROLLBACK TO OraPmtQueryBatch_PUB;
3761 x_return_status := FND_API.G_RET_STS_ERROR ;
3762 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
3763 p_data => x_msg_data
3764 );
3765 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3766 --ROLLBACK TO OraPmtQueryBatch_PUB;
3767 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3768 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
3769 p_data => x_msg_data
3770 );
3771 WHEN OTHERS THEN
3772 --ROLLBACK TO OraPmtQueryBatch_PUB;
3773 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3774 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3775 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_api_name);
3776 END IF;
3777
3778 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
3779 p_data => x_msg_data
3780 );
3781
3782 END OraPmtQueryBatch;
3783 --------------------------------------------------------------------------------------------
3784
3785 --12. OraPmtReq
3786 -- Start of comments
3787 -- API name : OraPmtReq
3788 -- Type : Public
3789 -- Pre-reqs : None
3790 -- Function : Handles new Payment requests from E-Commerce applications without Risk Input Parameter.
3791 -- Parameters :
3792 -- IN : p_api_version IN NUMBER Required
3793 -- p_init_msg_list IN VARCHAR2 Optional
3794 -- p_commit IN VARCHAR2 Optional
3795 -- p_validation_level IN NUMBER Optional
3796 -- p_ecapp_id IN NUMBER Required
3797 -- p_payee_id_rec IN Payee_rec_type Required
3798 -- p_payer_id_rec IN Payer_rec_type Optional
3799 -- p_pmtinstr_rec IN PmtInstr_rec_type Required
3800 -- p_tangible_rec IN Tangible_rec_type Required
3801 -- p_pmtreqtrxn_rec IN PmtReqTrxn_rec_type Required
3802 -- Version :
3803 -- Current version 1.0
3804 -- Previous version 1.0
3805 -- Initial version 1.0
3806 -- End of comments
3807 --------------------------------------------------------------------------------------------
3808 PROCEDURE OraPmtReq ( p_api_version IN NUMBER,
3809 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3810 p_commit IN VARCHAR2 := FND_API.G_FALSE,
3811 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
3812 p_ecapp_id IN NUMBER,
3813 p_payee_rec IN Payee_rec_type,
3814 p_payer_rec IN Payer_rec_type,
3815 p_pmtinstr_rec IN PmtInstr_rec_type,
3816 p_tangible_rec IN Tangible_rec_type,
3817 p_pmtreqtrxn_rec IN PmtReqTrxn_rec_type,
3818 x_return_status OUT NOCOPY VARCHAR2,
3819 x_msg_count OUT NOCOPY NUMBER,
3820 x_msg_data OUT NOCOPY VARCHAR2,
3821 x_reqresp_rec OUT NOCOPY ReqResp_rec_type
3822 ) IS
3823
3824 --Initialize all Risk Info related Parameters to default NULL values
3825 p_riskinfo_rec IBY_PAYMENT_ADAPTER_PUB.RiskInfo_rec_type;
3826
3827 BEGIN
3828 --Call OraPmtReq with the Risk input parameters.
3829 IBY_PAYMENT_ADAPTER_PUB.OraPmtReq( p_api_version,
3830 p_init_msg_list,
3831 p_commit,
3832 p_validation_level,
3833 p_ecapp_id ,
3834 p_payee_rec,
3835 p_payer_rec,
3836 p_pmtinstr_rec,
3837 p_tangible_rec,
3838 p_pmtreqtrxn_rec,
3839 p_riskinfo_rec,
3840 x_return_status,
3841 x_msg_count ,
3842 x_msg_data ,
3843 x_reqresp_rec
3844 );
3845 END OraPmtReq;
3846 --------------------------------------------------------------------------------------------
3847 --13. OraRiskEval
3848 -- Start of comments
3849 -- API name : OraRiskEval
3850 -- Type : Public
3851 -- Pre-reqs :
3852 -- Function : Evaluate Risk with no AVS
3853 -- Parameters :
3854 -- IN : p_api_version IN NUMBER Required
3855 -- p_init_msg_list IN VARCHAR2 Optional
3856 -- p_commit IN VARCHAR2 Optional
3857 -- p_validation_level IN NUMBER Optional
3858 -- p_ecapp_id IN NUMBER Required
3859 -- p_payment_risk_info IN PaymentRiskInfo_rec_type Required
3860 -- Version :
3861 -- Current version 1.0
3862 -- Previous version 1.0
3863 -- Initial version 1.0
3864 -- End of comments
3865 --------------------------------------------------------------------------------------------
3866
3867
3868 PROCEDURE OraRiskEval ( p_api_version IN NUMBER,
3869 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3870 p_commit IN VARCHAR2 := FND_API.G_FALSE,
3871 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
3872 p_ecapp_id IN NUMBER,
3873 p_payment_risk_info IN PaymentRiskInfo_rec_type,
3874 x_return_status OUT NOCOPY VARCHAR2,
3875 x_msg_count OUT NOCOPY NUMBER,
3876 x_msg_data OUT NOCOPY VARCHAR2,
3877 x_risk_resp OUT NOCOPY RiskResp_rec_type
3878 ) IS
3879
3880 l_get_baseurl VARCHAR2(2000);
3881 --The following 3 variables are meant for output of
3882 --get_baseurl procedure.
3883 l_status_url VARCHAR2(2000);
3884 l_msg_count_url NUMBER := 0;
3885 l_msg_data_url VARCHAR2(2000);
3886
3887 l_api_name CONSTANT VARCHAR2(30) := 'OraRiskEval';
3888 l_oapf_action CONSTANT VARCHAR2(30) := 'oraRiskEval';
3889 l_api_version CONSTANT NUMBER := 1.0;
3890
3891 l_url VARCHAR2(7000) ;
3892 l_html VARCHAR2(7000) ;
3893 l_names v240_tbl_type;
3894 l_values v240_tbl_type;
3895
3896 --The following 3 variables are meant for unpack_results_url procedure.
3897 l_status NUMBER := 0;
3898 l_errcode NUMBER := 0;
3899 l_errmessage VARCHAR2(2000) := 'Success';
3900
3901 l_pmtinstr_type VARCHAR2(200);
3902
3903 -- for NLS bug fix #1692300 - 4/3/2001 jleybovi
3904 --
3905 l_db_nls VARCHAR2(80) := NULL;
3906 l_ecapp_nls VARCHAR2(80) := NULL;
3907
3908 BEGIN
3909
3910 -- Standard Start of API savepoint
3911 --SAVEPOINT OraRiskEval_PUB;
3912
3913
3914 /* ***** Performing Validations, Initializations
3915 for Standard IN, OUT Parameters ***** */
3916
3917 -- Standard call to check for call compatibility.
3918 IF NOT FND_API.Compatible_API_Call ( l_api_version,
3919 p_api_version,
3920 l_api_name,
3921 G_PKG_NAME )
3922 THEN
3923 FND_MESSAGE.SET_NAME('IBY', 'IBY_204400_API_VER_MISMATCH');
3924 FND_MSG_PUB.Add;
3925 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3926 END IF;
3927
3928 -- Initialize message list if p_init_msg_list is set to TRUE.
3929 IF FND_API.to_Boolean( p_init_msg_list ) THEN
3930 FND_MSG_PUB.initialize;
3931 END IF;
3932
3933 -- Verifying if validation level is FULL, which is expected for PUBLIC APIs.
3934 IF (p_validation_level <> g_validation_level) THEN
3935 FND_MESSAGE.SET_NAME('IBY', 'IBY_204401_VAL_LEVEL_ERROR');
3936 FND_MSG_PUB.Add;
3937 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3938 END IF;
3939
3940
3941 -- Initialize API return status to success
3942 x_return_status := FND_API.G_RET_STS_SUCCESS;
3943
3944
3945 -- START OF BODY OF API
3946
3947 get_baseurl(l_get_baseurl);
3948
3949 -- Construct the full URL to send to the ECServlet.
3950 l_url := l_get_baseurl;
3951
3952 l_db_nls := iby_utility_pvt.get_local_nls();
3953
3954 --Mandatory Input Parameters
3955 check_mandatory('AVSRiskApi', 'false', l_url, l_db_nls, l_ecapp_nls);
3956 check_mandatory('OapfAction', l_oapf_action, l_url, l_db_nls, l_ecapp_nls);
3957 check_mandatory('OapfECAppId', to_char(p_ecapp_id), l_url, l_db_nls, l_ecapp_nls);
3958
3959 check_mandatory('OapfStoreId', p_payment_risk_info.Payee_ID, l_url, l_db_nls, l_ecapp_nls);
3960 check_mandatory('OapfPrice', FND_NUMBER.NUMBER_TO_CANONICAL(p_payment_risk_info.Amount), l_url, l_db_nls, l_ecapp_nls);
3961 check_mandatory('OapfCurr', p_payment_risk_info.Currency_Code, l_url, l_db_nls, l_ecapp_nls);
3962
3963 -- mandatory registration of payment instrument
3964 check_mandatory('OapfPmtRegId', to_char(p_payment_risk_info.PmtInstr.PmtInstr_ID), l_url, l_db_nls, l_ecapp_nls);
3965 check_mandatory('OapfPmtInstrType', p_payment_risk_info.PmtInstr.PmtInstr_type, l_url, l_db_nls, l_ecapp_nls);
3966
3967
3968 --OPTIONAL INPUT PARAMETERS
3969 check_optional('OapfPayerId', p_payment_risk_info.Party_ID, l_url, l_db_nls, l_ecapp_nls);
3970 check_optional('OapfFormulaName', p_payment_risk_info.Formula_Name, l_url, l_db_nls, l_ecapp_nls);
3971 check_optional('OapfShipToBillToFlag', p_payment_risk_info.ShipToBillTo_Flag, l_url, l_db_nls, l_ecapp_nls);
3972 check_optional('OapfCustAccountNum', p_payment_risk_info.Customer_Acct_Num, l_url, l_db_nls, l_ecapp_nls);
3973 check_optional('OapfAVSCode', p_payment_risk_info.AVSCode, l_url, l_db_nls, l_ecapp_nls);
3974 check_optional('OapfTimeOfPurchase', p_payment_risk_info.Time_Of_Purchase, l_url, l_db_nls, l_ecapp_nls);
3975
3976 -- Remove ampersand from the last input parameter.
3977 l_url := RTRIM(l_url,'&');
3978
3979 -- done in escape_url_chars
3980 --
3981 -- ^^^ Replace blank characters with + sign. ^^^
3982 --l_url := REPLACE(l_url,' ','+');
3983
3984
3985 -- show_input_debug(l_url);
3986
3987 -- Send http request to the payment server
3988 --l_html := UTL_HTTP.REQUEST(l_url);
3989 SEND_REQUEST(l_url, l_html);
3990
3991
3992 -- show_output_debug(l_html);
3993
3994 -- Unpack the results
3995 UNPACK_RESULTS_URL(l_html,l_names,l_values, l_status, l_errcode, l_errmessage);
3996
3997 --show_table_debug(l_names);
3998 --show_table_debug(l_values);
3999
4000 --Raising Exception to handle errors in unpacking resulting html file.
4001 IF (l_status = -1) THEN
4002 FND_MESSAGE.SET_NAME('IBY', 'IBY_204403_HTML_UNPACK_ERROR');
4003 FND_MSG_PUB.Add;
4004 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4005 END IF;
4006
4007 --Raising Exception to handle Servlet related errors.
4008 IF (l_names.COUNT = 0) THEN
4009 FND_MESSAGE.SET_NAME('IBY', 'IBY_204402_JSERVLET_ERROR');
4010 FND_MSG_PUB.Add;
4011 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4012 END IF;
4013
4014 FOR i IN 1..l_names.COUNT LOOP
4015 --Payment Server Related Generic Response
4016 IF l_names(i) = 'OapfStatus' THEN
4017 x_risk_resp.Status := TO_NUMBER(l_values(i));
4018 ELSIF l_names(i) = 'OapfRiskScore' THEN
4019 x_risk_resp.Risk_Score := TO_NUMBER(l_values(i));
4020 ELSIF l_names(i) = 'OapfRiskThresholdVal' THEN
4021 x_risk_resp.Risk_Threshold_Val := TO_NUMBER(l_values(i));
4022 ELSIF l_names(i) = 'OapfCode' THEN
4023 x_risk_resp.ErrCode := l_values(i);
4024 ELSIF l_names(i) = 'OapfCause' THEN
4025 x_risk_resp.ErrMessage := l_values(i);
4026 ELSIF l_names(i) = 'OapfAuxMsg' THEN
4027 x_risk_resp.Additional_ErrMessage := l_values(i);
4028 ELSIF l_names(i) = 'OapfRiskyFlag' THEN
4029 x_risk_resp.Risky_Flag := l_values(i);
4030 ELSIF l_names(i) = 'OapfAVSCodeFlag' THEN
4031 x_risk_resp.AVSCode_Flag := l_values(i);
4032 END IF;
4033
4034 END LOOP;
4035
4036 -- Setting API return status to 'U' if EvalRisk response status is not 0.
4037 IF (NOT trxn_status_success(x_risk_resp.Status)) THEN
4038 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4039 END IF;
4040
4041 -- END OF BODY OF API
4042
4043 -- Standard check of p_commit.
4044 /*
4045 IF FND_API.To_Boolean( p_commit ) THEN
4046 COMMIT WORK;
4047 END IF;
4048 */
4049
4050 -- Standard call to get message count and if count is 1, get message info.
4051 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
4052 p_data => x_msg_data
4053 );
4054 EXCEPTION
4055
4056 WHEN FND_API.G_EXC_ERROR THEN
4057 --ROLLBACK TO OraRiskEval_PUB;
4058 x_return_status := FND_API.G_RET_STS_ERROR ;
4059 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
4060 p_data => x_msg_data
4061 );
4062 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4063 --ROLLBACK TO OraRiskEval_PUB;
4064 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4065 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
4066 p_data => x_msg_data
4067 );
4068
4069 WHEN OTHERS THEN
4070 --ROLLBACK TO OraRiskEval_PUB;
4071 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4072 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
4073 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_api_name);
4074 END IF;
4075
4076 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
4077 p_data => x_msg_data
4078 );
4079
4080
4081 END OraRiskEval;
4082
4083
4084 --------------------------------------------------------------------------------------------
4085
4086 --14. OraRiskEval ( Overloaded )
4087 -- Start of comments
4088 -- API name : OraRiskEval
4089 -- Type : Public
4090 -- Pre-reqs :
4091 -- Function : Evaluate Risk with AVS
4092 -- Parameters :
4093 -- IN : p_api_version IN NUMBER Required
4094 -- p_init_msg_list IN VARCHAR2 Optional
4095 -- p_commit IN VARCHAR2 Optional
4096 -- p_validation_level IN NUMBER Optional
4097 -- p_ecapp_id IN NUMBER Required
4098 -- p_avs_risk_info IN AVSRiskInfo_rec_type Required
4099 -- Version :
4100 -- Current version 1.0
4101 -- Previous version 1.0
4102 -- Initial version 1.0
4103 -- End of comments
4104
4105 --------------------------------------------------------------------------------------------
4106 PROCEDURE OraRiskEval ( p_api_version IN NUMBER,
4107 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
4108 p_commit IN VARCHAR2 := FND_API.G_FALSE,
4109 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
4110 p_ecapp_id IN NUMBER,
4111 p_avs_risk_info IN AVSRiskInfo_rec_type,
4112 x_return_status OUT NOCOPY VARCHAR2,
4113 x_msg_count OUT NOCOPY NUMBER,
4114 x_msg_data OUT NOCOPY VARCHAR2,
4115 x_risk_resp OUT NOCOPY RiskResp_rec_type
4116 ) IS
4117
4118 l_get_baseurl VARCHAR2(2000);
4119 --The following 3 variables are meant for output of
4120 --get_baseurl procedure.
4121 l_status_url VARCHAR2(2000);
4122 l_msg_count_url NUMBER := 0;
4123 l_msg_data_url VARCHAR2(2000);
4124
4125 l_api_name CONSTANT VARCHAR2(30) := 'OraRiskEval';
4126 l_oapf_action CONSTANT VARCHAR2(30) := 'oraRiskEval';
4127 l_api_version CONSTANT NUMBER := 1.0;
4128
4129 l_url VARCHAR2(2000) ;
4130 l_html VARCHAR2(7000) ;
4131 l_names v240_tbl_type;
4132 l_values v240_tbl_type;
4133
4134 --The following 3 variables are meant for unpack_results_url procedure.
4135 l_status NUMBER := 0;
4136 l_errcode NUMBER := 0;
4137 l_errmessage VARCHAR2(2000) := 'Success';
4138
4139 -- for NLS bug fix #1692300 - 4/3/2001 jleybovi
4140 --
4141 l_db_nls VARCHAR2(80) := NULL;
4142 l_ecapp_nls VARCHAR2(80) := NULL;
4143
4144 BEGIN
4145
4146 -- Standard Start of API savepoint
4147 --SAVEPOINT OraRiskEval_PUB;
4148
4149
4150 /* ***** Performing Validations, Initializations
4151 for Standard IN, OUT Parameters ***** */
4152
4153 -- Standard call to check for call compatibility.
4154 IF NOT FND_API.Compatible_API_Call ( l_api_version,
4155 p_api_version,
4156 l_api_name,
4157 G_PKG_NAME )
4158 THEN
4159 FND_MESSAGE.SET_NAME('IBY', 'IBY_204400_API_VER_MISMATCH');
4160 FND_MSG_PUB.Add;
4161 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4162 END IF;
4163
4164 -- Initialize message list if p_init_msg_list is set to TRUE.
4165 IF FND_API.to_Boolean( p_init_msg_list ) THEN
4166 FND_MSG_PUB.initialize;
4167 END IF;
4168
4169 -- Verifying if validation level is FULL, which is expected for PUBLIC APIs.
4170 IF (p_validation_level <> g_validation_level) THEN
4171 FND_MESSAGE.SET_NAME('IBY', 'IBY_204401_VAL_LEVEL_ERROR');
4172 FND_MSG_PUB.Add;
4173 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4174 END IF;
4175
4176
4177 -- Initialize API return status to success
4178 x_return_status := FND_API.G_RET_STS_SUCCESS;
4179
4180
4181 -- START OF BODY OF API
4182
4183 get_baseurl(l_get_baseurl);
4184
4185 -- Construct the full URL to send to the ECServlet.
4186 l_url := l_get_baseurl;
4187
4188 l_db_nls := iby_utility_pvt.get_local_nls();
4189
4190 --Mandatory Input Parameters
4191 check_mandatory('AVSRiskApi', 'true', l_url, l_db_nls, l_ecapp_nls);
4192 check_mandatory('OapfAction', l_oapf_action, l_url, l_db_nls, l_ecapp_nls);
4193 check_mandatory('OapfECAppId', to_char(p_ecapp_id), l_url, l_db_nls, l_ecapp_nls);
4194
4195 check_mandatory('OapfStoreId', p_avs_risk_info.Payee_ID, l_url, l_db_nls, l_ecapp_nls);
4196 check_mandatory('OapfPrevRiskScore', p_avs_risk_info.Previous_Risk_Score, l_url, l_db_nls, l_ecapp_nls);
4197 check_mandatory('OapfAVSCode', p_avs_risk_info.AVSCode, l_url, l_db_nls, l_ecapp_nls);
4198
4199 --OPTIONAL INPUT PARAMETERS
4200 check_optional('OapfFormulaName', p_avs_risk_info.Formula_Name, l_url, l_db_nls, l_ecapp_nls);
4201
4202 -- Remove ampersand from the last input parameter.
4203 l_url := RTRIM(l_url,'&');
4204
4205 -- already done by escape_url_chars()
4206 --
4207 -- ^^^ Replace blank characters with + sign. ^^^
4208 -- l_url := REPLACE(l_url,' ','+');
4209
4210
4211 --show_input_debug(l_url);
4212
4213 -- Send http request to the payment server
4214 --l_html := UTL_HTTP.REQUEST(l_url);
4215 SEND_REQUEST(l_url, l_html);
4216
4217
4218 -- show_output_debug(l_html);
4219
4220 -- Unpack the results
4221 UNPACK_RESULTS_URL(l_html,l_names,l_values, l_status, l_errcode, l_errmessage);
4222
4223 --show_table_debug(l_names);
4224 --show_table_debug(l_values);
4225
4226 --Raising Exception to handle errors in unpacking resulting html file.
4227 IF (l_status = -1) THEN
4228 FND_MESSAGE.SET_NAME('IBY', 'IBY_204403_HTML_UNPACK_ERROR');
4229 FND_MSG_PUB.Add;
4230 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4231 END IF;
4232
4233 --Raising Exception to handle Servlet related errors.
4234 IF (l_names.COUNT = 0) THEN
4235 FND_MESSAGE.SET_NAME('IBY', 'IBY_204402_JSERVLET_ERROR');
4236 FND_MSG_PUB.Add;
4237 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4238 END IF;
4239
4240 FOR i IN 1..l_names.COUNT LOOP
4241 --Payment Server Related Generic Response
4242 IF l_names(i) = 'OapfStatus' THEN
4243 x_risk_resp.Status := TO_NUMBER(l_values(i));
4244 ELSIF l_names(i) = 'OapfRiskScore' THEN
4245 x_risk_resp.Risk_Score := TO_NUMBER(l_values(i));
4246 ELSIF l_names(i) = 'OapfRiskThresholdVal' THEN
4247 x_risk_resp.Risk_Threshold_Val := TO_NUMBER(l_values(i));
4248 ELSIF l_names(i) = 'OapfCode' THEN
4249 x_risk_resp.ErrCode := l_values(i);
4250 ELSIF l_names(i) = 'OapfCause' THEN
4251 x_risk_resp.ErrMessage := l_values(i);
4252 ELSIF l_names(i) = 'OapfAuxMsg' THEN
4253 x_risk_resp.Additional_ErrMessage := l_values(i);
4254 ELSIF l_names(i) = 'OapfRiskyFlag' THEN
4255 x_risk_resp.Risky_Flag := l_values(i);
4256 ELSIF l_names(i) = 'OapfAVSCodeFlag' THEN
4257 x_risk_resp.AVSCode_Flag := l_values(i);
4258
4259 END IF;
4260
4261 END LOOP;
4262
4263 -- Setting API return status to 'U' if EvalRisk response status is not 0.
4264 IF (NOT trxn_status_success(x_risk_resp.Status)) THEN
4265 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4266 END IF;
4267
4268 -- END OF BODY OF API
4269
4270 -- Standard check of p_commit.
4271 /*
4272 IF FND_API.To_Boolean( p_commit ) THEN
4273 COMMIT WORK;
4274 END IF;
4275 */
4276
4277 -- Standard call to get message count and if count is 1, get message info.
4278 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
4279 p_data => x_msg_data
4280 );
4281 EXCEPTION
4282
4283
4284 WHEN FND_API.G_EXC_ERROR THEN
4285 --ROLLBACK TO OraRiskEval_PUB;
4286 x_return_status := FND_API.G_RET_STS_ERROR ;
4287 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
4288 p_data => x_msg_data
4289 );
4290 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4291 --ROLLBACK TO OraRiskEval_PUB;
4292 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4293 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
4294 p_data => x_msg_data
4295 );
4296
4297 WHEN OTHERS THEN
4298 --ROLLBACK TO OraRiskEval_PUB;
4299 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4300 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
4301 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_api_name, SQLCODE||':'||SQLERRM);
4302 END IF;
4303
4304 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
4305 p_data => x_msg_data
4306 );
4307
4308
4309 END OraRiskEval;
4310
4311 --------------------------------------------------------------------------------------------
4312 --15. OraCCBatchCapture
4313 -- Start of comments
4314 -- API name : OraCCBatchCapture
4315 -- Type : Public
4316 -- Pre-reqs : None
4317 -- Function : Handles batch capture of funds from Payer accounts for authorized
4318 -- Payment Requests in case of CreditCard instruments.
4319 -- Parameters :
4320 -- IN : p_api_version IN NUMBER Required
4321 -- p_init_msg_list IN VARCHAR2 Optional
4322 -- p_commit IN VARCHAR2 Optional
4323 -- p_validation_level IN NUMBER Optional
4324 -- p_ecapp_id IN NUMBER Required
4325 -- p_capturetrxn_rec_tbl IN CaptureTrxn_tbl Required
4326 -- Version :
4327 -- Current version 1.0
4328 -- Previous version 1.0
4329 -- Initial version 1.0
4330 -- End of comments
4331
4332 PROCEDURE OraCCBatchCapture ( p_api_version IN NUMBER,
4333 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
4334 p_commit IN VARCHAR2 := FND_API.G_FALSE,
4335 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
4336 p_ecapp_id IN NUMBER,
4337 p_capturetrxn_rec_tbl IN CaptureTrxn_tbl,
4338 x_return_status OUT NOCOPY VARCHAR2,
4339 x_msg_count OUT NOCOPY NUMBER,
4340 x_msg_data OUT NOCOPY VARCHAR2,
4341 x_capresp_rec_tbl OUT NOCOPY CaptureResp_tbl
4342 ) IS
4343
4344 l_get_baseurl VARCHAR2(2000);
4345 --The following 3 variables are meant for output of
4346 --get_baseurl procedure.
4347 l_status_url VARCHAR2(2000);
4348 l_msg_count_url NUMBER := 0;
4349 l_msg_data_url VARCHAR2(2000);
4350
4351 l_api_name CONSTANT VARCHAR2(30) := 'OraCCBatchCapture';
4352 l_oapf_action CONSTANT VARCHAR2(30) := 'oraCCBatch';
4353 l_api_version CONSTANT NUMBER := 1.0;
4354
4355 l_url VARCHAR2(4000);
4356 l_html VARCHAR2(32000);
4357 l_names v240_tbl_type;
4358 l_values v240_tbl_type;
4359 l_ret_name VARCHAR2(1000);
4360 l_ret_value VARCHAR2(1000);
4361 l_ret_index NUMBER;
4362 l_ret_pos NUMBER;
4363 l_http_body boolean := false;
4364
4365 --The following 3 variables are meant for unpack_results_url procedure.
4366 l_status NUMBER := 0;
4367 l_errcode NUMBER := 0;
4368 l_errmessage VARCHAR2(2000) := 'Success';
4369
4370 l_db_nls VARCHAR2(80) := NULL;
4371 l_ecapp_nls VARCHAR2(80) := NULL;
4372
4373 l_position NUMBER := 0;
4374 l_host VARCHAR2(4000);
4375 l_port VARCHAR2(80) := NULL;
4376 l_post_info VARCHAR2(2000);
4377
4378 l_conn UTL_TCP.CONNECTION; -- TCP/IP connection to the Web server
4379 l_ret_val PLS_INTEGER;
4380 l_curr_index NUMBER;
4381 l_index NUMBER := 0;
4382 l_temp_buff VARCHAR2(2000) := NULL;
4383 l_content_len NUMBER := 0;
4384
4385 l_batch_status NUMBER := NULL;
4386 l_batch_msg VARCHAR2(2000);
4387
4388 BEGIN
4389
4390 iby_debug_pub.add('Enter',FND_LOG.LEVEL_PROCEDURE,
4391 G_DEBUG_MODULE || '.OraCCBatchCapture');
4392
4393 /* ***** Performing Validations, Initializations
4394 for Standard IN, OUT Parameters ***** */
4395 -- Standard call to check for call compatibility.
4396 IF NOT FND_API.Compatible_API_Call ( l_api_version,
4397 p_api_version,
4398 l_api_name,
4399 G_PKG_NAME )
4400 THEN
4401 FND_MESSAGE.SET_NAME('IBY', 'IBY_204400_API_VER_MISMATCH');
4402 FND_MSG_PUB.Add;
4403 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4404 END IF;
4405
4406 -- Initialize message list if p_init_msg_list is set to TRUE.
4407 IF FND_API.to_Boolean( p_init_msg_list ) THEN
4408 FND_MSG_PUB.initialize;
4409 END IF;
4410
4411 -- Verifying if validation level is FULL, which is expected for PUBLIC APIs.
4412 IF (p_validation_level <> g_validation_level) THEN
4413 FND_MESSAGE.SET_NAME('IBY', 'IBY_204401_VAL_LEVEL_ERROR');
4414 FND_MSG_PUB.Add;
4415 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4416 END IF;
4417
4418 iby_debug_pub.add('Batch size = ' || p_capturetrxn_rec_tbl.count,
4419 FND_LOG.LEVEL_STATEMENT,
4420 G_DEBUG_MODULE || '.OraCCBatchCapture');
4421 --
4422 -- Verifying if the batch size exceeded max.
4423 -- The batch size has exceeded the max limit size.
4424 IF (p_capturetrxn_rec_tbl.count > C_MAX_CC_BATCH_SIZE) THEN
4425 FND_MESSAGE.SET_NAME('IBY', 'IBY_204407_VAL_ERROR');
4426 FND_MSG_PUB.Add;
4427 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4428 END IF;
4429
4430 -- Initialize API return status to success
4431 x_return_status := FND_API.G_RET_STS_SUCCESS;
4432
4433 -- START OF BODY OF API
4434
4435 get_baseurl(l_get_baseurl);
4436 -- Construct the full URL to send to the ECServlet.
4437 l_url := l_get_baseurl;
4438 iby_debug_pub.add('iPaymentURL = ' || l_url,
4439 FND_LOG.LEVEL_STATEMENT,
4440 G_DEBUG_MODULE || '.OraCCBatchCapture');
4441
4442 l_db_nls := iby_utility_pvt.get_local_nls();
4443
4444 -- 1. MANDATORY INPUT PARAMETERS
4445 check_mandatory('OapfAction', l_oapf_action, l_url, l_db_nls, l_ecapp_nls);
4446
4447 -- Remove ampersand from the last input parameter.
4448 l_url := RTRIM(l_url,'&');
4449
4450 l_position := INSTR(lower(l_url),lower('http://'));
4451 --remove the 'http://'
4452 IF (l_position > 0) THEN
4453 l_url := SUBSTR(l_url,8);
4454 ELSE
4455 l_position := INSTR(lower(l_url),lower('https://'));
4456 --remove the 'https://'
4457 IF (l_position > 0) THEN
4458 l_url := SUBSTR(l_url,9);
4459 END IF;
4460 END IF;
4461
4462 -- get the host address
4463 l_position := INSTR(l_url,':');
4464 IF (l_position > 0) THEN
4465 l_host := SUBSTR(l_url,1,l_position-1);
4466 --remove the 'the host + :' from the URL
4467 l_url := SUBSTR(l_url,l_position+1);
4468 ELSE
4469 l_position := INSTR(l_url,'/');
4470 IF (l_position > 0) THEN
4471 l_host := SUBSTR(l_url,1,l_position-1);
4472 --remove the 'the host' from the URL
4473 l_url := SUBSTR(l_url,l_position);
4474 END IF;
4475 END IF;
4476
4477 -- get the port number
4478 l_position := INSTR(l_url,'/');
4479 IF (l_position > 0) THEN
4480 l_port := SUBSTR(l_url,1,l_position-1);
4481 END IF;
4482 IF (l_port is NULL) THEN
4483 l_port := '80';
4484 END IF;
4485
4486 --remove the port number from the URL
4487 l_post_info := SUBSTR(l_url,l_position);
4488 l_post_info := 'POST ' || l_post_info || ' HTTP/1.0';
4489 --dbms_output.put_line('l_post_info = ' || l_post_info);
4490
4491 iby_debug_pub.add('l_host = ' || l_host,
4492 FND_LOG.LEVEL_STATEMENT,
4493 G_DEBUG_MODULE || '.OraCCBatchCapture');
4494 iby_debug_pub.add('l_port = ' || l_port,
4495 FND_LOG.LEVEL_STATEMENT,
4496 G_DEBUG_MODULE || '.OraCCBatchCapture');
4497
4498 -- open connection
4499 l_conn := utl_tcp.open_connection(remote_host => l_host,
4500 remote_port => l_port);
4501
4502 iby_debug_pub.add('opened socket to EC Servlet',
4503 FND_LOG.LEVEL_STATEMENT,
4504 G_DEBUG_MODULE || '.OraCCBatchCapture');
4505
4506 l_ret_val := utl_tcp.write_line(l_conn, l_post_info);
4507 l_ret_val := utl_tcp.write_line(l_conn,'Accept: text/plain');
4508 l_ret_val := utl_tcp.write_line(l_conn,'Content-type: text/plain');
4509
4510 -- calcuate the content length
4511 l_content_len := 0;
4512 l_temp_buff := 'OapfBatchType=oraPmtCapture';
4513 l_content_len := l_content_len + length(l_temp_buff) + 2;
4514 l_temp_buff := 'OapfBatchSize='||p_capturetrxn_rec_tbl.count;
4515 l_content_len := l_content_len + length(l_temp_buff) + 2;
4516 l_curr_index := p_capturetrxn_rec_tbl.first;
4517 l_index := 0;
4518
4519 WHILE (l_curr_index <= p_capturetrxn_rec_tbl.last) LOOP
4520
4521 l_temp_buff := 'OapfPrice-'||l_index||'='||FND_NUMBER.NUMBER_TO_CANONICAL(p_capturetrxn_rec_tbl(l_curr_index).Price);
4522 l_content_len := l_content_len + length(l_temp_buff) + 2;
4523 l_temp_buff := 'OapfECAppId-'||l_index||'='||p_ecapp_id;
4524 l_content_len := l_content_len + length(l_temp_buff) + 2;
4525 l_temp_buff := 'OapfTransactionId-'||l_index||'='||p_capturetrxn_rec_tbl(l_curr_index).Trxn_Id;
4526 l_content_len := l_content_len + length(l_temp_buff) + 2;
4527 l_temp_buff := 'OapfCurr-'||l_index||'='||p_capturetrxn_rec_tbl(l_curr_index).Currency;
4528 l_content_len := l_content_len + length(l_temp_buff) + 2;
4529 l_temp_buff := 'OapfMode-'||l_index||'='||p_capturetrxn_rec_tbl(l_curr_index).PmtMode;
4530 l_content_len := l_content_len + length(l_temp_buff) + 2;
4531
4532 IF (p_capturetrxn_rec_tbl(l_curr_index).NLS_LANG IS NOT NULL) THEN
4533 l_temp_buff := 'OapfNlsLang-'||l_index||'='||p_capturetrxn_rec_tbl(l_curr_index).NLS_LANG;
4534 l_content_len := l_content_len + length(l_temp_buff) + 2;
4535 END IF;
4536 IF (p_capturetrxn_rec_tbl(l_curr_index).PmtMode = 'OFFLINE') THEN
4537 l_temp_buff := 'OapfSchedDate-'||l_index||'='||to_char(p_capturetrxn_rec_tbl(l_curr_index).Settlement_Date, 'YYYY-MM-DD');
4538 l_content_len := l_content_len + length(l_temp_buff) + 2;
4539 END IF;
4540 l_index := l_index + 1;
4541 l_curr_index := p_capturetrxn_rec_tbl.next(l_curr_index);
4542
4543 END LOOP;
4544
4545 iby_debug_pub.add('l_content_len = ' || l_content_len,
4546 FND_LOG.LEVEL_STATEMENT,
4547 G_DEBUG_MODULE || '.OraCCBatchCapture');
4548
4549 l_ret_val := utl_tcp.write_line(l_conn,'Content-length: '||l_content_len);
4550 l_ret_val := utl_tcp.write_line(l_conn);
4551 l_ret_val := utl_tcp.write_line(l_conn,'OapfBatchType=oraPmtCapture');
4552 l_ret_val := utl_tcp.write_line(l_conn,'OapfBatchSize='||p_capturetrxn_rec_tbl.count);
4553
4554 -- Transmits a text line to a service on an open connection.
4555 -- The newline character sequence will be appended to the message before it is transmitted.
4556 l_curr_index := p_capturetrxn_rec_tbl.first;
4557 l_index := 0;
4558
4559 WHILE (l_curr_index <= p_capturetrxn_rec_tbl.last) LOOP
4560
4561 l_ret_val := utl_tcp.write_line(l_conn,'OapfPrice-'||l_index||'='||FND_NUMBER.NUMBER_TO_CANONICAL(p_capturetrxn_rec_tbl(l_curr_index).Price));
4562 l_ret_val := utl_tcp.write_line(l_conn,'OapfECAppId-'||l_index||'='||p_ecapp_id);
4563 l_ret_val := utl_tcp.write_line(l_conn,'OapfTransactionId-'||l_index||'='||p_capturetrxn_rec_tbl(l_curr_index).Trxn_Id);
4564 --save the trxn_id for return value.
4565 x_capresp_rec_tbl(l_index).Trxn_ID := p_capturetrxn_rec_tbl(l_curr_index).Trxn_Id;
4566 l_ret_val := utl_tcp.write_line(l_conn,'OapfCurr-'||l_index||'='||p_capturetrxn_rec_tbl(l_curr_index).Currency);
4567 l_ret_val := utl_tcp.write_line(l_conn,'OapfMode-'||l_index||'='||p_capturetrxn_rec_tbl(l_curr_index).PmtMode);
4568 IF (p_capturetrxn_rec_tbl(l_curr_index).NLS_LANG IS NOT NULL) THEN
4569 l_ret_val := utl_tcp.write_line(l_conn,'OapfNlsLang-'||l_index||'='||p_capturetrxn_rec_tbl(l_curr_index).NLS_LANG);
4570 END IF;
4571 IF (p_capturetrxn_rec_tbl(l_curr_index).PmtMode = 'OFFLINE') THEN
4572 l_ret_val := utl_tcp.write_line(l_conn,'OapfSchedDate-'||l_index||'='||to_char(p_capturetrxn_rec_tbl(l_curr_index).Settlement_Date, 'YYYY-MM-DD'));
4573 END IF;
4574 l_index := l_index + 1;
4575 l_curr_index := p_capturetrxn_rec_tbl.next(l_curr_index);
4576
4577 END LOOP;
4578
4579 l_ret_val := utl_tcp.write_line(l_conn);
4580 l_http_body := false;
4581
4582 BEGIN LOOP
4583 -- read result
4584 l_html := substr(utl_tcp.get_line(l_conn,TRUE),1,3000);
4585 l_position := instr(l_html,'</H2>');
4586
4587 --dbms_output.put_line('l_html body = ' || substr(l_html,1,200));
4588
4589 --we only want the html body here
4590 IF ((l_position > 0) OR (l_http_body)) THEN
4591
4592 l_http_body := true;
4593 l_position := instr(l_html,'Oapf');
4594
4595 IF l_position > 0 THEN
4596 --add the <BR> to the end, b/c the UNPACK_RESULTS_URL expected to be there.
4597 -- l_html := l_html || '<BR>';
4598 UNPACK_RESULTS_URL(l_html,l_names,l_values, l_status, l_errcode, l_errmessage);
4599 --Raising Exception to handle errors in unpacking resulting html file.
4600 IF (l_status = -1) THEN
4601
4602 iby_debug_pub.add('error unpacking results',
4603 FND_LOG.LEVEL_UNEXPECTED,
4604 G_DEBUG_MODULE || '.OraCCBatchCapture');
4605
4606 FND_MESSAGE.SET_NAME('IBY', 'IBY_204403_HTML_UNPACK_ERROR');
4607 FND_MSG_PUB.Add;
4608 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4609 END IF;
4610
4611 --Raising Exception to handle Servlet related errors.
4612 IF (l_names.COUNT = 0) THEN
4613
4614 iby_debug_pub.add('servlet errors; no names returned',
4615 FND_LOG.LEVEL_UNEXPECTED,
4616 G_DEBUG_MODULE || '.OraCCBatchCapture');
4617
4618 FND_MESSAGE.SET_NAME('IBY', 'IBY_204402_JSERVLET_ERROR');
4619 FND_MSG_PUB.Add;
4620 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4621 END IF;
4622 -- Assign output parameters to output record x_capresp_rec_tbl
4623 FOR i IN 1..l_names.COUNT LOOP
4624 l_ret_pos := instr(l_names(i),'-');
4625 IF (l_ret_pos > 0) THEN
4626 l_ret_name := SUBSTR(l_names(i),1,l_ret_pos-1);
4627 l_ret_index := TO_NUMBER(SUBSTR(l_names(i),l_ret_pos+1));
4628 l_ret_value := l_values(i);
4629
4630 --Payment Server GENERIC Response
4631 IF l_ret_name = 'OapfStatus' THEN
4632 x_capresp_rec_tbl(l_ret_index).Status := TO_NUMBER(l_ret_value);
4633 iby_debug_pub.add('status #'||l_ret_index||'='||x_capresp_rec_tbl(l_ret_index).Status,
4634 FND_LOG.LEVEL_STATEMENT,
4635 G_DEBUG_MODULE || '.OraCCBatchCapture');
4636 ELSIF l_ret_name = 'OapfCode' THEN
4637 x_capresp_rec_tbl(l_ret_index).ErrCode := l_ret_value;
4638 iby_debug_pub.add('code #'||l_ret_index||'='||x_capresp_rec_tbl(l_ret_index).ErrCode,
4639 FND_LOG.LEVEL_STATEMENT,
4640 G_DEBUG_MODULE || '.OraCCBatchCapture');
4641 ELSIF l_ret_name = 'OapfCause' THEN
4642 x_capresp_rec_tbl(l_ret_index).ErrMessage := l_ret_value;
4643 iby_debug_pub.add('casue #'||l_ret_index||'='||x_capresp_rec_tbl(l_ret_index).ErrMessage,
4644 FND_LOG.LEVEL_STATEMENT,
4645 G_DEBUG_MODULE || '.OraCCBatchCapture');
4646 ELSIF l_ret_name = 'OapfNlsLang' THEN
4647 x_capresp_rec_tbl(l_ret_index).NLS_LANG := l_ret_value;
4648
4649 --CAPTURE Operation Related Response
4650 ELSIF l_ret_name = 'OapfTransactionId' THEN
4651 x_capresp_rec_tbl(l_ret_index).Trxn_ID := TO_NUMBER(l_ret_value);
4652 ELSIF l_ret_name = 'OapfTrxnType' THEN
4653 x_capresp_rec_tbl(l_ret_index).Trxn_Type := TO_NUMBER(l_ret_value);
4654 ELSIF l_ret_name = 'OapfTrxnDate' THEN
4655 x_capresp_rec_tbl(l_ret_index).Trxn_Date := TO_DATE(l_ret_value, 'YYYY-MM-DD');
4656 ELSIF l_ret_name = 'OapfPmtInstrType' THEN
4657 x_capresp_rec_tbl(l_ret_index).PmtInstr_Type := l_ret_value;
4658 ELSIF l_ret_name = 'OapfRefcode' THEN
4659 x_capresp_rec_tbl(l_ret_index).Refcode := l_ret_value;
4660 ELSIF l_ret_name = 'OapfErrLocation' THEN
4661 x_capresp_rec_tbl(l_ret_index).ErrorLocation := l_ret_value;
4662 ELSIF l_ret_name = 'OapfVendErrCode' THEN
4663 x_capresp_rec_tbl(l_ret_index).BEPErrCode := l_ret_value;
4664 ELSIF l_ret_name = 'OapfVendErrmsg' THEN
4665 x_capresp_rec_tbl(l_ret_index).BEPErrmessage := l_ret_value;
4666
4667 --OFFLINE Payment Mode Related Response
4668 ELSIF l_ret_name = 'OapfEarliestSettlementDate' THEN
4669 x_capresp_rec_tbl(l_ret_index).EarliestSettlement_Date := TO_DATE(l_ret_value, 'YYYY-MM-DD');
4670 ELSIF l_ret_name = 'OapfSchedDate' THEN
4671 x_capresp_rec_tbl(l_ret_index).Scheduled_Date := TO_DATE(l_ret_value, 'YYYY-MM-DD');
4672 END IF;
4673 --
4674 -- this should be the value of the batch status itself;
4675 -- if the batch failed then so has the API call; note
4676 -- that it is always considered a batch success if the batch
4677 -- is successfully submitted, even if every single trxn
4678 -- within it failed for one reason or another
4679 --
4680 ELSIF l_names(i) = 'OapfStatus' THEN
4681 iby_debug_pub.add('trxn status='||l_values(i),
4682 FND_LOG.LEVEL_STATEMENT,
4683 G_DEBUG_MODULE || '.OraCCBatchCapture');
4684 l_batch_status := TO_NUMBER(l_values(i));
4685 ELSIF l_names(i) = 'OapfCause' THEN
4686 iby_debug_pub.add('batch err msg='||l_values(i),
4687 FND_LOG.LEVEL_STATEMENT,
4688 G_DEBUG_MODULE || '.OraCCBatchCapture');
4689 l_batch_msg := l_values(i);
4690 END IF;
4691
4692 END LOOP;
4693 END IF;
4694 END IF;
4695 END LOOP;
4696 EXCEPTION
4697 WHEN utl_tcp.end_of_input THEN
4698 NULL; -- end of input
4699 END;
4700 utl_tcp.close_connection(l_conn);
4701
4702 -- Setting API return status to 'U' if response status is not 0.
4703 IF (NOT trxn_status_success(l_batch_status)) THEN
4704
4705 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4706 --
4707 -- if the batch failed an error message should have been
4708 -- returned
4709 --
4710 IF (NOT l_batch_msg IS NULL) THEN
4711 --FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,l_api_name,l_batch_msg);
4712 --
4713 -- setting dummy message so that the results
4714 -- from the EC Servlet/Java engine can be returned
4715 -- verbatim
4716 --
4717 FND_MESSAGE.SET_NAME('IBY', 'IBY_9999');
4718 FND_MESSAGE.SET_TOKEN('MESSAGE_TEXT', l_batch_msg);
4719 FND_MSG_PUB.ADD;
4720 ELSE
4721 -- no error message; assuming a complete communication
4722 -- failure where the iPayment URL is bad but the webserver
4723 -- is up and returns a parseable error page
4724 --
4725 FND_MESSAGE.SET_NAME('IBY', 'IBY_0001');
4726 FND_MSG_PUB.Add;
4727 END IF;
4728
4729 END IF;
4730
4731
4732 -- Standard call to get message count and if count is 1, get message info.
4733 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
4734 p_data => x_msg_data
4735 );
4736
4737 iby_debug_pub.add('Exit',
4738 FND_LOG.LEVEL_PROCEDURE,
4739 G_DEBUG_MODULE || '.OraCCBatchCapture');
4740
4741 EXCEPTION
4742 WHEN FND_API.G_EXC_ERROR THEN
4743
4744 iby_debug_pub.add('In Expected Error',
4745 FND_LOG.LEVEL_ERROR,
4746 G_DEBUG_MODULE || '.OraCCBatchCapture');
4747 --ROLLBACK TO OraCCBatchCapture_PUB;
4748 x_return_status := FND_API.G_RET_STS_ERROR ;
4749 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
4750 p_data => x_msg_data
4751 );
4752 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4753
4754 iby_debug_pub.add('In Unexpected Error',
4755 FND_LOG.LEVEL_UNEXPECTED,
4756 G_DEBUG_MODULE || '.OraCCBatchCapture');
4757 --ROLLBACK TO OraCCBatchCapture_PUB;
4758 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4759 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
4760 p_data => x_msg_data
4761 );
4762 WHEN OTHERS THEN
4763
4764 iby_debug_pub.add('In Others Exception',
4765 FND_LOG.LEVEL_UNEXPECTED,
4766 G_DEBUG_MODULE || '.OraCCBatchCapture');
4767 iby_debug_pub.add('Exception code='||SQLCODE,
4768 FND_LOG.LEVEL_UNEXPECTED,
4769 G_DEBUG_MODULE || '.OraCCBatchCapture');
4770 iby_debug_pub.add('Exception message='||SQLERRM,
4771 FND_LOG.LEVEL_UNEXPECTED,
4772 G_DEBUG_MODULE || '.OraCCBatchCapture');
4773
4774 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4775 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
4776 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_api_name, SQLCODE||':'||SQLERRM);
4777 END IF;
4778
4779 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
4780 p_data => x_msg_data
4781 );
4782
4783
4784 END OraCCBatchCapture;
4785
4786 -- Start of comments
4787 -- API name : OraSecureExtension
4788 -- Type : Private
4789 -- Function : Secures the CVV value passed and returns the segment_ID.
4790 -- Parameters :
4791 -- IN : p_commit IN VARCHAR2 Optional
4792 -- p_cvv IN NUMBER Required
4793 --
4794 PROCEDURE OraSecureExtension (p_commit IN VARCHAR2 := FND_API.G_FALSE,
4795 p_cvv IN VARCHAR2,
4796 x_return_status OUT NOCOPY VARCHAR2,
4797 x_msg_count OUT NOCOPY NUMBER,
4798 x_msg_data OUT NOCOPY VARCHAR2,
4799 x_resp_rec OUT NOCOPY SecureCVVResp_rec_type
4800 ) IS
4801
4802
4803 l_get_baseurl VARCHAR2(2000);
4804 --The following 3 variables are meant for output of
4805 --get_baseurl procedure.
4806 l_status_url VARCHAR2(2000);
4807 l_msg_count_url NUMBER := 0;
4808 l_msg_data_url VARCHAR2(2000);
4809
4810 l_api_name CONSTANT VARCHAR2(30) := 'OraSecureExtension';
4811 l_oapf_action CONSTANT VARCHAR2(30) := 'oraSecureExtension';
4812 l_api_version CONSTANT NUMBER := 1.0;
4813
4814 l_url VARCHAR2(30000) ;
4815 l_html VARCHAR2(30000) ;
4816 l_names v240_tbl_type;
4817 l_values v240_tbl_type;
4818
4819 -- for NLS bug fix #1692300 - 4/3/2001 jleybovi
4820 --
4821 -- l_db_nls p_pmtreqtrxn_rec.NLS_LANG%TYPE := NULL;
4822 -- l_ecapp_nls p_pmtreqtrxn_rec.NLS_LANG%TYPE := NULL;
4823
4824 --The following 3 variables are meant for output of
4825 --unpack_results_url procedure.
4826 l_status NUMBER := 0;
4827 l_errcode NUMBER := 0;
4828 l_errmessage VARCHAR2(2000) := 'Success';
4829
4830 BEGIN
4831
4832 iby_debug_pub.add(debug_msg => 'Enter',
4833 debug_level => FND_LOG.LEVEL_PROCEDURE,
4834 module => G_DEBUG_MODULE || '.OraSecureExtension');
4835
4836 --test_debug('OraSecureExtension=> Enter');
4837 -- Initialize API return status to success
4838 x_return_status := FND_API.G_RET_STS_SUCCESS;
4839
4840 -- START OF BODY OF API
4841
4842 get_baseurl(l_get_baseurl);
4843 dbms_output.put_line('l_get_baseurl= ' || l_get_baseurl);
4844 --test_debug('OraSecureExtension=> l_get_baseurl= '|| l_get_baseurl);
4845 -- dbms_output.put_line('l_status_url= ' || l_status_url);
4846 -- dbms_output.put_line('l_msg_count_url= ' || l_msg_count_url);
4847 -- dbms_output.put_line('l_msg_data_url= ' || l_msg_data_url);
4848
4849 -- Construct the full URL to send to the ECServlet.
4850 l_url := l_get_baseurl;
4851
4852 --l_db_nls := iby_utility_pvt.get_local_nls();
4853 --l_ecapp_nls := p_pmtreqtrxn_rec.NLS_LANG;
4854
4855 -- dbms_output.put_line('db NLS val is: ' || l_db_nls);
4856 -- dbms_output.put_line('ecapp NLS val is: ' || l_ecapp_nls);
4857
4858 --MANDATORY INPUT PARAMETERS
4859 check_mandatory('OapfAction', l_oapf_action, l_url, null, null);
4860 check_mandatory('OapfSecCode', p_cvv, l_url, null, null);
4861
4862
4863 -- Remove ampersand from the last input parameter.
4864 l_url := RTRIM(l_url,'&');
4865 --dbms_output.put_line('send request ');
4866 --test_debug('OraSecureExtension=> send request ');
4867 --dbms_output.put_line('l_url is:'||substr(l_url,1, 50));
4868 --test_debug('OraSecureExtension=> l_url is: '|| substr(l_url,1, 150));
4869
4870 --dbms_output.put_line('l_html is:'||substr(l_html, 1, 50));
4871 --test_debug('OraSecureExtension=> l_html is: '|| substr(l_html, 1, 100));
4872 SEND_REQUEST(l_url, l_html);
4873 -- show_output_debug(l_html);
4874
4875 -- Unpack the results
4876 UNPACK_RESULTS_URL(l_html,l_names,l_values, l_status, l_errcode, l_errmessage);
4877
4878 --show_table_debug(l_names);
4879 --show_table_debug(l_values);
4880
4881 --Raising Exception to handle errors in unpacking resulting html file.
4882 IF (l_status = -1) THEN
4883 --test_debug('unpack error !!');
4884 iby_debug_pub.add(debug_msg => 'Unpack status error; HTML resp. invalid!',
4885 debug_level => FND_LOG.LEVEL_UNEXPECTED,
4886 module => G_DEBUG_MODULE || '.OraSecureExtension');
4887 FND_MESSAGE.SET_NAME('IBY', 'IBY_204403_HTML_UNPACK_ERROR');
4888 FND_MSG_PUB.Add;
4889 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4890 END IF;
4891
4892 --Raising Exception to handle Servlet related errors.
4893 IF (l_names.COUNT = 0) THEN
4894 --test_debug('response count is 0 !!');
4895 iby_debug_pub.add(debug_msg => 'HTML response names count=0',
4896 debug_level => FND_LOG.LEVEL_UNEXPECTED,
4897 module => G_DEBUG_MODULE || '.OraSecureExtension');
4898 FND_MESSAGE.SET_NAME('IBY', 'IBY_204402_JSERVLET_ERROR');
4899 FND_MSG_PUB.Add;
4900 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4901 END IF;
4902
4903 /* Retrieve name-value pairs stored in l_names and l_values, and assign
4904 them to the output record: x_reqresp_rec.
4905 */
4906 --test_debug('Setting fields from unpacked response');
4907 iby_debug_pub.add(debug_msg => 'Setting fields from unpacked response',
4908 debug_level => FND_LOG.LEVEL_STATEMENT,
4909 module => G_DEBUG_MODULE || '.OraSecureExtension');
4910
4911
4912 FOR i IN 1..l_names.COUNT LOOP
4913 --Payment Server Related Generic Response
4914 IF l_names(i) = 'OapfStatus' THEN
4915 x_resp_rec.Response.Status := TO_NUMBER(l_values(i));
4916 iby_debug_pub.add(debug_msg => 'Response status=' || x_resp_rec.Response.Status,
4917 debug_level => FND_LOG.LEVEL_STATEMENT,
4918 module => G_DEBUG_MODULE || '.OraSecureExtension');
4919 --test_debug('OapfStatus: '||x_resp_rec.Response.Status);
4920 ELSIF l_names(i) = 'OapfCode' THEN
4921 x_resp_rec.Response.ErrCode := l_values(i);
4922 iby_debug_pub.add(debug_msg => 'Response code=' || x_resp_rec.Response.ErrCode,
4923 debug_level => FND_LOG.LEVEL_STATEMENT,
4924 module => G_DEBUG_MODULE || '.OraSecureExtension');
4925 --test_debug('OapfCode: '||x_resp_rec.Response.ErrCode);
4926 ELSIF l_names(i) = 'OapfCause' THEN
4927 x_resp_rec.Response.ErrMessage := l_values(i);
4928 iby_debug_pub.add(debug_msg => 'Response message=' || x_resp_rec.Response.ErrMessage,
4929 debug_level => FND_LOG.LEVEL_STATEMENT,
4930 module => G_DEBUG_MODULE || '.OraSecureExtension');
4931 --test_debug('OapfCause: '||x_resp_rec.Response.ErrMessage);
4932 ELSIF l_names(i) = 'OapfNlsLang' THEN
4933 x_resp_rec.Response.NLS_LANG := l_values(i);
4934
4935 --Secure Extension Related Response
4936 ELSIF l_names(i) = 'OapfSegmentId' THEN
4937 x_resp_rec.Segment_ID := TO_NUMBER(l_values(i));
4938 --test_debug('OapfSegmentId: '||x_resp_rec.Segment_ID);
4939 END IF;
4940
4941 END LOOP;
4942
4943 -- Use for Debugging
4944 --dbms_output.put_line('after successfully mapping results');
4945
4946 -- Standard check of p_commit.
4947 /*
4948 IF FND_API.To_Boolean( p_commit ) THEN
4949 COMMIT WORK;
4950 END IF;
4951 */
4952
4953 -- Standard call to get message count and if count is 1, get message info.
4954 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
4955 p_data => x_msg_data
4956 );
4957
4958 iby_debug_pub.add(debug_msg => 'x_return_status=' || x_return_status,
4959 debug_level => FND_LOG.LEVEL_STATEMENT,
4960 module => G_DEBUG_MODULE || '.OraSecureExtension');
4961 iby_debug_pub.add(debug_msg => 'req response status=' || x_resp_rec.Response.Status,
4962 debug_level => FND_LOG.LEVEL_STATEMENT,
4963 module => G_DEBUG_MODULE || '.OraSecureExtension');
4964
4965 iby_debug_pub.add(debug_msg => 'Exit',
4966 debug_level => FND_LOG.LEVEL_PROCEDURE,
4967 module => G_DEBUG_MODULE || '.OraSecureExtension');
4968 --test_debug('Exit*******');
4969
4970 EXCEPTION
4971
4972 WHEN FND_API.G_EXC_ERROR THEN
4973
4974 iby_debug_pub.add(debug_msg => 'In G_EXC_ERROR Exception',
4975 debug_level => FND_LOG.LEVEL_ERROR,
4976 module => G_DEBUG_MODULE || '.OraSecureExtension');
4977 --ROLLBACK TO OraPmtReq_PUB;
4978 x_return_status := FND_API.G_RET_STS_ERROR ;
4979 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
4980 p_data => x_msg_data
4981 );
4982 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4983
4984 iby_debug_pub.add(debug_msg => 'In G_EXC_UNEXPECTED_ERROR Exception',
4985 debug_level => FND_LOG.LEVEL_UNEXPECTED,
4986 module => G_DEBUG_MODULE || '.OraSecureExtension');
4987 --ROLLBACK TO OraPmtReq_PUB;
4988 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4989 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
4990 p_data => x_msg_data
4991 );
4992 WHEN OTHERS THEN
4993
4994 iby_debug_pub.add(debug_msg => 'In OTHERS Exception',
4995 debug_level => FND_LOG.LEVEL_UNEXPECTED,
4996 module => G_DEBUG_MODULE || '.OraSecureExtension');
4997 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4998 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
4999 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_api_name);
5000 END IF;
5001
5002 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
5003 p_data => x_msg_data
5004 );
5005
5006 iby_debug_pub.add(debug_msg => 'x_return_status=' || x_return_status,
5007 debug_level => FND_LOG.LEVEL_UNEXPECTED,
5008 module => G_DEBUG_MODULE || '.OraSecureExtension');
5009 iby_debug_pub.add(debug_msg => 'Exit Exception',
5010 debug_level => FND_LOG.LEVEL_UNEXPECTED,
5011 module => G_DEBUG_MODULE || '.OraSecureExtension');
5012
5013 END OraSecureExtension;
5014
5015 --------------------------------------------------------------------------------------------
5016 END IBY_PAYMENT_ADAPTER_PUB;
5017