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