[Home] [Help]
PACKAGE BODY: APPS.IBY_INSTRREG_PUB
Source
1 PACKAGE BODY IBY_INSTRREG_PUB AS
2 /*$Header: ibypregb.pls 120.30 2011/03/23 15:00:52 lmallick ship $*/
3
4
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'IBY_INSTRREG_PUB';
6
7 g_validation_level CONSTANT NUMBER := FND_API.G_VALID_LEVEL_FULL;
8 -- Owner Type is actually 'PAYER' but it has been kept as 'USER'
9 -- as the java API uses it as the hardcoded value.
10 -- Bug 4298732
11 -- g_owner_type CONSTANT VARCHAR2(10) := 'USER';
12 g_owner_type CONSTANT VARCHAR2(10) := 'PAYER';
13
14 -------------------------------------------------------------------------------
15 /* UTILITY FUNCTION#2: ENCODE
16 NOTE: Encoding method was moved to the utility package iby_utility_pvt;
17 this now-wrapper function has not been completely removed b/c
18 it was defined in the spec file and so is public and thus
19 possibly used by existing customers
20 */
21 -------------------------------------------------------------------------------
22
23 FUNCTION encode(s IN VARCHAR2) RETURN VARCHAR2 IS
24 BEGIN
25 RETURN iby_utility_pvt.encode64(s);
26 END encode;
27
28 -------------------------------------------------------------------------------
29 /* UTILITY FUNCTION#3: DECODE
30 NOTE: See note for the ENCODE() function
31 */
32 -------------------------------------------------------------------------------
33
34 FUNCTION decode(s IN VARCHAR2) RETURN VARCHAR2 IS
35 BEGIN
36 RETURN iby_utility_pvt.decode64(s);
37 END decode;
38
39
40 -------------------------------------------------------------------------------
41 /* UTILITY PROCEDURE #1: GET_INSTRUMENT_DETAILS
42 This procedure will return all the instruments that it can find for a
43 payer_id and instr_id.If the instr_id is NULL then it will return all the
44 instruments for the payer_id alone. Each of the 3 PL/SQL tables that is
45 returned will have a collection of a particular instrument.
46 If instr_id is passed then only one instrument detail is returned.
47 */
48 -------------------------------------------------------------------------------
49 PROCEDURE Get_Instrument_Details ( payer_id IN VARCHAR2,
50 instr_id IN NUMBER,
51 sys_master_key IN IBY_SECURITY_PKG.DES3_KEY_TYPE,
52 creditcard_tbl OUT NOCOPY CreditCard_tbl_type,
53 purchasecard_tbl OUT NOCOPY PurchaseCard_tbl_type,
54 bankacct_tbl OUT NOCOPY BankAcct_tbl_type
55 ) IS
56 l_count INTEGER;
57 l_ccsubtype iby_creditcard.subtype%TYPE;
58
59 lx_pcard_flag iby_creditcard.purchasecard_flag%TYPE;
60 lx_pcard_type iby_creditcard.purchasecard_subtype%TYPE;
61
62 lx_result_code VARCHAR(30);
63
64 -- only query wanted instrument id's; then use these
65 -- in calls to the iby_creditcard_pkg which will take
66 -- care of decryption, etc.
67 --
68 CURSOR load_creditcard_csr( l_instr_id NUMBER ) IS
69 SELECT instrid
70 FROM iby_creditcard_v
71 WHERE ownerid = payer_id
72 AND instrid = nvl(l_instr_id, instrid);
73 CURSOR load_purchasecard_csr( l_instr_id NUMBER ) IS
74 SELECT instrid
75 FROM iby_purchasecard_v
76 WHERE ownerid = payer_id
77 AND instrid = nvl(l_instr_id, instrid);
78
79 CURSOR load_bankacct_csr( l_instr_id NUMBER ) IS
80 SELECT b.ext_bank_account_id
81 FROM iby_ext_bank_accounts_v b,
82 iby_account_owners ao
83 WHERE ao.account_owner_party_id = payer_id
84 AND ao.ext_bank_account_id = b.ext_bank_account_id
85 AND b.ext_bank_account_id = nvl(l_instr_id, b.ext_bank_account_id);
86
87 BEGIN
88
89 -- close the cursors, if they are already open.
90 IF( load_creditcard_csr%ISOPEN ) THEN
91 CLOSE load_creditcard_csr;
92 END IF;
93
94 IF( load_purchasecard_csr%ISOPEN ) THEN
95 CLOSE load_purchasecard_csr;
96 END IF;
97
98 IF( load_bankacct_csr%ISOPEN ) THEN
99 CLOSE load_bankacct_csr;
100 END IF;
101
102 /* --- Processing Credit Card information ---- */
103
104 l_count := 1; -- Initialize the counter for the loop
105
106 -- fetch all the credit card instruments for the payer
107 FOR t_creditcard IN load_creditcard_csr(instr_id) LOOP
108
109 iby_creditcard_pkg.Query_Card
110 (
111 t_creditcard.instrid,
112 NULL,
113 creditcard_tbl(l_count).Owner_Id,
114 creditcard_tbl(l_count).CC_HolderName,
115 creditcard_tbl(l_count).Billing_Address_Id,
116 creditcard_tbl(l_count).Billing_Address1,
117 creditcard_tbl(l_count).Billing_Address2,
118 creditcard_tbl(l_count).Billing_Address3,
119 creditcard_tbl(l_count).Billing_City,
120 creditcard_tbl(l_count).Billing_County,
121 creditcard_tbl(l_count).Billing_State,
122 creditcard_tbl(l_count).Billing_PostalCode,
123 creditcard_tbl(l_count).Billing_Country,
124 creditcard_tbl(l_count).CC_Num,
125 creditcard_tbl(l_count).CC_ExpDate,
126 creditcard_tbl(l_count).Instrument_Type,
127 lx_pcard_flag,
128 lx_pcard_type,
129 creditcard_tbl(l_count).CC_Type,
130 creditcard_tbl(l_count).FIName,
131 creditcard_tbl(l_count).Single_Use_Flag,
132 creditcard_tbl(l_count).Info_Only_Flag,
133 creditcard_tbl(l_count).Card_Purpose,
134 creditcard_tbl(l_count).CC_Desc,
135 creditcard_tbl(l_count).Active_Flag,
136 creditcard_tbl(l_count).Inactive_Date,
137 lx_result_code
138 );
139
140 l_count := l_count + 1;
141 END LOOP; -- For the load_creditcard_csr
142
143 /* --- Processing Purchase Card information ---- */
144
145 l_count := 1; -- Initialize the counter for the next loop
146
147 -- fetch all the purchase card instruments for the payer
148 FOR t_purchasecard IN load_purchasecard_csr(instr_id) LOOP
149
150 iby_creditcard_pkg.Query_Card
151 (
152 t_purchasecard.instrid,
153 NULL,
154 purchasecard_tbl(l_count).Owner_Id,
155 purchasecard_tbl(l_count).PC_HolderName,
156 purchasecard_tbl(l_count).Billing_Address_Id,
157 purchasecard_tbl(l_count).Billing_Address1,
158 purchasecard_tbl(l_count).Billing_Address2,
159 purchasecard_tbl(l_count).Billing_Address3,
160 purchasecard_tbl(l_count).Billing_City,
161 purchasecard_tbl(l_count).Billing_County,
162 purchasecard_tbl(l_count).Billing_State,
163 purchasecard_tbl(l_count).Billing_PostalCode,
164 purchasecard_tbl(l_count).Billing_Country,
165 purchasecard_tbl(l_count).PC_Num,
166 purchasecard_tbl(l_count).PC_ExpDate,
167 purchasecard_tbl(l_count).Instrument_Type,
168 lx_pcard_flag,
169 purchasecard_tbl(l_count).PC_Subtype,
170 purchasecard_tbl(l_count).PC_Type,
171 purchasecard_tbl(l_count).FIName,
172 purchasecard_tbl(l_count).Single_Use_Flag,
173 purchasecard_tbl(l_count).Info_Only_Flag,
174 purchasecard_tbl(l_count).Card_Purpose,
175 purchasecard_tbl(l_count).PC_Desc,
176 purchasecard_tbl(l_count).Active_Flag,
177 purchasecard_tbl(l_count).Inactive_Date,
178 lx_result_code
179 );
180
181 l_count := l_count + 1;
182
183 END LOOP; -- For the load_purchasecard_csr
184
185 /* --- Processing Bank Account information ---- */
186
187 l_count := 1; -- Initialize the counter for the next loop
188
189 -- fetch all the bank account instruments for the payer
190 FOR t_bankacct IN load_bankacct_csr(instr_id) LOOP
191 /*
192 iby_bankacct_pkg.queryBankAcct
193 (
194 673,
195 null,
196 payer_id,
197 t_bankacct.instrid,
198 sys_master_key,
199 bankacct_tbl(l_count).FIName,
200 bankacct_tbl(l_count).Bank_ID,
201 bankacct_tbl(l_count).Branch_ID,
202 bankacct_tbl(l_count).BankAcct_Type,
203 bankacct_tbl(l_count).BankAcct_Num,
204 bankacct_tbl(l_count).BankAcct_HolderName,
205 bankacct_tbl(l_count).Bank_Desc,
206 bankacct_tbl(l_count).BankAcct_Checkdigits
207 );
208 */
209 l_count := l_count + 1;
210
211 END LOOP;
212
213
214 END Get_Instrument_Details;
215
216 /* UTILITY FUNCTION # 4: ECAPP_RETURN_STATUS_SUCCESS
217
218 Returns true if and only if the given transaction
219 status indicates a success.
220
221 */
222 FUNCTION ecapp_return_status_success( p_ret_status NUMBER )
223 RETURN BOOLEAN
224 IS
225 BEGIN
226 IF (p_ret_status IS NULL) THEN
227 iby_debug_pub.add(debug_msg => 'ECApp servlet trxn status is NULL!',
228 debug_level => FND_LOG.LEVEL_UNEXPECTED,
229 module => G_DEBUG_MODULE || '.ecapp_return_status_success');
230 RETURN FALSE;
231 ELSIF ((IBY_PAYMENT_ADAPTER_PUB.C_TRXN_STATUS_SUCCESS = p_ret_status) OR
232 (IBY_PAYMENT_ADAPTER_PUB.C_TRXN_STATUS_INFO = p_ret_status) OR
233 (IBY_PAYMENT_ADAPTER_PUB.C_TRXN_STATUS_WARNING = p_ret_status)
234 )
235 THEN
236 RETURN TRUE;
237 ELSE
238 RETURN FALSE;
239 END IF;
240 END ecapp_return_status_success;
241
242
243 -------------------------------------------------------------------------------
244 ---*** APIS START BELOW ---***
245 -------------------------------------------------------------------------------
246 -- 1. OraInstrAdd
247 -- Start of comments
248 -- API name : OraInstrAdd
249 -- Type : Public
250 -- Pre-reqs : None
251 -- Function : Adds new Payment Instruments to iPayment.
252 -- Parameters :
253 -- IN : p_api_version IN NUMBER Required
254 -- p_init_msg_list IN VARCHAR2 Optional
255 -- p_commit IN VARCHAR2 Optional
256 -- p_validation_level IN NUMBER Optional
257 -- p_payer_id IN VARCHAR2 Required
258 -- p_pmtInstrRec IN PmtInstr_rec_type Required
259 --
260 -- OUT : x_return_status OUT VARCHAR2
261 -- x_msg_count OUT VARCHAR2
262 -- x_msg_data OUT NUMBER
263 -- x_instr_id OUT NUMBER
264 -- Version :
265 -- Current version 1.0
266 -- Previous version 1.0
267 -- Initial version 1.0
268 -- End of comments
269 -------------------------------------------------------------------------------
270
271 PROCEDURE OraInstrAdd
272 (
273 p_api_version IN NUMBER,
274 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
275 p_commit IN VARCHAR2 := FND_API.G_TRUE,
276 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
277 p_pmtInstrRec IN PmtInstr_rec_type,
278 x_return_status OUT NOCOPY VARCHAR2,
279 x_msg_count OUT NOCOPY NUMBER,
280 x_msg_data OUT NOCOPY VARCHAR2,
281 x_instr_id OUT NOCOPY NUMBER,
282 x_result OUT NOCOPY IBY_FNDCPT_COMMON_PUB.Result_rec_type
283 )
284 IS
285 l_api_name CONSTANT VARCHAR2(30) := 'OraInstrAdd';
286 l_oapf_action CONSTANT VARCHAR2(30) := 'oraInstrAdd';
287 l_api_version CONSTANT NUMBER := 1.0;
288
289 l_url VARCHAR2(30000) ;
290 l_get_baseurl VARCHAR2(2000);
291
292 l_pos NUMBER := 0;
293 l_post_body VARCHAR2(30000);
294 l_html VARCHAR2(32767) ;
295 l_names IBY_NETUTILS_PVT.v240_tbl_type;
296 l_values IBY_NETUTILS_PVT.v240_tbl_type;
297
298 l_status NUMBER := 0;
299 l_errcode NUMBER := 0;
300 l_index NUMBER := 1;
301 l_errmessage VARCHAR2(2000) := 'Success';
302
303 -- for NLS bug fix #1692300 - 4/3/2001 jleybovi
304 --
305 l_db_nls VARCHAR2(80) := NULL;
306 l_ecapp_nls VARCHAR2(80) := NULL;
307
308 l_instrument_type VARCHAR2(80) := C_INSTRTYPE_UNREG;
309 l_sec_cred NUMBER;
310
311 ERROR_FROM_SUBPROC Exception;
312
313 -- This will catch all the exceptions from the procedure which is
314 -- subsequently called.This will trap all exceptions that have
315 -- SQLCODE = -20000 and name it as 'ERROR_FROM_SUBPROC'.
316 PRAGMA EXCEPTION_INIT( ERROR_FROM_SUBPROC, -20000 );
317
318 l_dbg_mod VARCHAR2(100) := G_DEBUG_MODULE || '.' || l_api_name;
319 BEGIN
320 iby_debug_pub.add('Enter',iby_debug_pub.G_LEVEL_PROCEDURE,l_dbg_mod);
321
322 -- Standard call to check for call compatibility.
323 IF NOT FND_API.Compatible_API_Call ( l_api_version,
324 p_api_version,
325 l_api_name,
326 G_PKG_NAME )
327 THEN
328 FND_MESSAGE.SET_NAME('IBY', 'IBY_204400_API_VER_MISMATCH');
329 FND_MSG_PUB.Add;
330 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
331 END IF;
332
333 -- Initialize message list if p_init_msg_list is set to TRUE.
334 IF FND_API.to_Boolean( p_init_msg_list ) THEN
335 FND_MSG_PUB.initialize;
336 END IF;
337
338 -- Verifying if validation level is FULL, which is expected for PUBLIC APIs.
339 IF (p_validation_level <> g_validation_level) THEN
340 FND_MESSAGE.SET_NAME('IBY', 'IBY_204401_VAL_LEVEL_ERROR');
341 FND_MSG_PUB.Add;
342 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
343 END IF;
344
345 -- Initialize API return status to success
346 x_return_status := FND_API.G_RET_STS_SUCCESS;
347
348 -- START OF BODY OF API
349
350 -- Checks whether the instrument type passed is valid or not
351 l_instrument_type := p_pmtInstrRec.InstrumentType;
352 IF( ( l_instrument_type <> C_INSTRTYPE_CREDITCARD ) AND
353 ( l_instrument_type <> C_INSTRTYPE_PURCHASECARD ) AND
354 ( l_instrument_type <> C_INSTRTYPE_BANKACCT ) ) THEN
355 FND_MESSAGE.SET_NAME('IBY', 'IBY_20487');
356 FND_MSG_PUB.Add;
357 RAISE FND_API.G_EXC_ERROR;
358 -- Returns message 'Invalid instrument type passed'.
359 END IF;
360
361 -- Check whether Instrid is passed. It should not be passed for 'Add'.
362 IF( ( p_pmtInstrRec.CreditCardInstr.Instr_Id is not NULL ) OR
363 ( p_pmtInstrRec.PurchaseCardInstr.Instr_Id is not NULL ) OR
364 ( p_pmtInstrRec.BankAcctInstr.Instr_Id is not NULL ) ) THEN
365 FND_MESSAGE.SET_NAME('IBY', 'IBY_20488');
366 FND_MSG_PUB.Add;
367 RAISE FND_API.G_EXC_ERROR;
368 --Returns message 'INSTR_ID should not be passed'
369 END IF;
370
371 IF( l_instrument_type = C_INSTRTYPE_PURCHASECARD ) THEN
372 -- Purchase Subtype is mandatory.
373 IF( p_pmtInstrRec.PurchaseCardInstr.PC_SubType is NULL ) THEN
374 FND_MESSAGE.SET_NAME('IBY', 'IBY_20483');
375 FND_MSG_PUB.Add;
376 RAISE FND_API.G_EXC_ERROR;
377 -- Returns message 'Mandatory field(s) missing'
378 END IF;
379
380 END IF;
381
382 -- Finally call the procedures that will add the instrument.
383 --IBY_NETUTILS_PVT.get_baseurl(l_get_baseurl);
384
385 IBY_NETUTILS_PVT.get_baseurl(l_get_baseurl);
386
387 iby_debug_pub.add('GetBaseUrl :' || l_get_baseurl,
388 iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
389
390 -- Construct the full URL to send to the ECServlet.
391 l_url := l_get_baseurl;
392
393 l_db_nls := IBY_NETUTILS_PVT.get_local_nls();
394 l_ecapp_nls := NULL; -- not passed in this api??
395
396 IBY_NETUTILS_PVT.check_mandatory('OapfAction', l_oapf_action, l_url, l_db_nls, l_ecapp_nls);
397 IF ( l_instrument_type <> C_INSTRTYPE_UNREG) THEN
398 IBY_NETUTILS_PVT.check_mandatory('OapfPmtInstrType', l_instrument_type, l_url, l_db_nls, l_ecapp_nls);
399 END IF;
400
401 -- lmallick: PA-DSS fixes
402 -- For IMMEDIATE mode of encryption, pass the login_id and user_id to the
403 -- ecapp servlet. This would ensure that the audit trail is maintained even when the
404 -- requested is routed through the ecapp servlet
405 IBY_NETUTILS_PVT.check_mandatory('OapfLoginId', fnd_global.login_id, l_url, l_db_nls, l_ecapp_nls);
406 IBY_NETUTILS_PVT.check_mandatory('OapfUserId', fnd_global.user_id, l_url, l_db_nls, l_ecapp_nls);
407
408
409 IF ( l_instrument_type = C_INSTRTYPE_BANKACCT) THEN
410 IBY_NETUTILS_PVT.check_optional('OapfInstrOwnerType', p_pmtInstrRec.BankAcctInstr.BankAcct_HolderType, l_url, l_db_nls, l_ecapp_nls);
411 IBY_NETUTILS_PVT.check_optional('OapfInstrFIName', p_pmtInstrRec.BankAcctInstr.FIName, l_url, l_db_nls, l_ecapp_nls);
412 IBY_NETUTILS_PVT.check_mandatory('OapfInstrBankId', p_pmtInstrRec.BankAcctInstr.Branch_ID, l_url, l_db_nls, l_ecapp_nls);
413 IBY_NETUTILS_PVT.check_optional('OapfInstrBankSwiftCode', p_pmtInstrRec.BankAcctInstr.Bank_SwiftCode, l_url, l_db_nls, l_ecapp_nls);
414 IBY_NETUTILS_PVT.check_optional('OapfInstrBranchId', p_pmtInstrRec.BankAcctInstr.Bank_ID, l_url, l_db_nls, l_ecapp_nls);
415 IBY_NETUTILS_PVT.check_optional('OapfInstrAcctType', p_pmtInstrRec.BankAcctInstr.BankAcct_Type, l_url, l_db_nls, l_ecapp_nls);
416 IBY_NETUTILS_PVT.check_mandatory('OapfInstrNum', p_pmtInstrRec.BankAcctInstr.BankAcct_Num, l_url, l_db_nls, l_ecapp_nls);
417 IBY_NETUTILS_PVT.check_optional('OapfInstrCheckDigits', p_pmtInstrRec.BankAcctInstr.BankAcct_Checkdigits, l_url, l_db_nls, l_ecapp_nls);
418 IBY_NETUTILS_PVT.check_mandatory('OapfInstrHolderName', p_pmtInstrRec.BankAcctInstr.BankAcct_HolderName, l_url, l_db_nls, l_ecapp_nls);
419 IBY_NETUTILS_PVT.check_optional('OapfInstrBuf', p_pmtInstrRec.BankAcctInstr.Bank_Desc, l_url, l_db_nls, l_ecapp_nls);
420 IBY_NETUTILS_PVT.check_optional('OapfInstrCurrency', p_pmtInstrRec.BankAcctInstr.BankAcct_Currency, l_url, l_db_nls, l_ecapp_nls);
421 IBY_NETUTILS_PVT.check_optional('OapfInstrOwnerAddrId', p_pmtInstrRec.BankAcctInstr.Acct_HolderAddrId, l_url, l_db_nls, l_ecapp_nls);
422 IF ( p_pmtInstrRec.BankAcctInstr.Bank_AddrId is NOT null ) THEN
423 IBY_NETUTILS_PVT.check_optional('OapfInstrAddrId', p_pmtInstrRec.BankAcctInstr.Bank_AddrId, l_url, l_db_nls, l_ecapp_nls);
424 ELSIF ( p_pmtInstrRec.BankAcctInstr.Bank_Address1 is NOT NULL) THEN
425 IBY_NETUTILS_PVT.check_mandatory('OapfInstrAddrLine1', p_pmtInstrRec.BankAcctInstr.Bank_Address1, l_url, l_db_nls, l_ecapp_nls);
426 IBY_NETUTILS_PVT.check_optional('OapfInstrAddrLine2', p_pmtInstrRec.BankAcctInstr.Bank_Address2, l_url, l_db_nls, l_ecapp_nls);
427 IBY_NETUTILS_PVT.check_optional('OapfInstrAddrLine3', p_pmtInstrRec.BankAcctInstr.Bank_Address3, l_url, l_db_nls, l_ecapp_nls);
428 IBY_NETUTILS_PVT.check_optional('OapfInstrCity', p_pmtInstrRec.BankAcctInstr.Bank_City, l_url, l_db_nls, l_ecapp_nls);
429 IBY_NETUTILS_PVT.check_optional('OapfInstrCounty', p_pmtInstrRec.BankAcctInstr.Bank_County, l_url, l_db_nls, l_ecapp_nls);
430 IBY_NETUTILS_PVT.check_optional('OapfInstrState', p_pmtInstrRec.BankAcctInstr.Bank_State, l_url, l_db_nls, l_ecapp_nls);
431 IBY_NETUTILS_PVT.check_mandatory('OapfInstrCountry', p_pmtInstrRec.BankAcctInstr.Bank_Country, l_url, l_db_nls, l_ecapp_nls);
432 IBY_NETUTILS_PVT.check_optional('OapfInstrPostalCode', p_pmtInstrRec.BankAcctInstr.Bank_PostalCode, l_url, l_db_nls, l_ecapp_nls);
433 END IF;
434
435 ELSIF( l_instrument_type = C_INSTRTYPE_PURCHASECARD ) THEN
436
437 IBY_NETUTILS_PVT.check_optional('OapfInstrFIName', p_pmtInstrRec.PurchaseCardInstr.FIName, l_url, l_db_nls, l_ecapp_nls);
438 IBY_NETUTILS_PVT.check_mandatory('OapfCCType',p_pmtInstrRec.PurchaseCardInstr.PC_Type, l_url, l_db_nls, l_ecapp_nls);
439 IBY_NETUTILS_PVT.check_mandatory('OapfPmtInstrExp',to_char(p_pmtInstrRec.PurchaseCardInstr.PC_ExpDate,'YYYY-MM-DD'), l_url, l_db_nls, l_ecapp_nls);
440 IBY_NETUTILS_PVT.check_mandatory('OapfInstrNum', p_pmtInstrRec.PurchaseCardInstr.PC_Num, l_url, l_db_nls, l_ecapp_nls);
441 IBY_NETUTILS_PVT.check_mandatory('OapfInstrHolderName', p_pmtInstrRec.PurchaseCardInstr.PC_HolderName , l_url, l_db_nls, l_ecapp_nls);
442 IBY_NETUTILS_PVT.check_optional('OapfInstrOwnerId', p_pmtInstrRec.PurchaseCardInstr.Owner_Id, l_url, l_db_nls, l_ecapp_nls);
443 IBY_NETUTILS_PVT.check_optional('OapfInstrOwnerType', p_pmtInstrRec.PurchaseCardInstr.PC_HolderType, l_url, l_db_nls, l_ecapp_nls);
444 IBY_NETUTILS_PVT.check_mandatory('OapfCardSubType', p_pmtInstrRec.PurchaseCardInstr.PC_Subtype, l_url, l_db_nls, l_ecapp_nls);
445 IBY_NETUTILS_PVT.check_optional('OapfInstrBuf', p_pmtInstrRec.PurchaseCardInstr.PC_Desc, l_url, l_db_nls, l_ecapp_nls);
446 IF ( p_pmtInstrRec.PurchaseCardInstr.Billing_Address1 is NOT NULL) THEN
447 IBY_NETUTILS_PVT.check_mandatory('OapfInstrAddrLine1', p_pmtInstrRec.PurchaseCardInstr.Billing_Address1, l_url, l_db_nls, l_ecapp_nls);
448 IBY_NETUTILS_PVT.check_optional('OapfInstrAddrLine2', p_pmtInstrRec.PurchaseCardInstr.Billing_Address2, l_url, l_db_nls, l_ecapp_nls);
449 IBY_NETUTILS_PVT.check_optional('OapfInstrAddrLine3', p_pmtInstrRec.PurchaseCardInstr.Billing_Address3, l_url, l_db_nls, l_ecapp_nls);
450 IBY_NETUTILS_PVT.check_mandatory('OapfInstrCity', p_pmtInstrRec.PurchaseCardInstr.Billing_City, l_url, l_db_nls, l_ecapp_nls);
451 IBY_NETUTILS_PVT.check_optional('OapfInstrCounty', p_pmtInstrRec.PurchaseCardInstr.Billing_County, l_url, l_db_nls, l_ecapp_nls);
452 IBY_NETUTILS_PVT.check_optional('OapfInstrState', p_pmtInstrRec.PurchaseCardInstr.Billing_State, l_url, l_db_nls, l_ecapp_nls);
453 IBY_NETUTILS_PVT.check_mandatory('OapfInstrCountry', p_pmtInstrRec.PurchaseCardInstr.Billing_Country, l_url, l_db_nls, l_ecapp_nls);
454 IBY_NETUTILS_PVT.check_optional('OapfInstrPostalCode', p_pmtInstrRec.PurchaseCardInstr.Billing_PostalCode, l_url, l_db_nls, l_ecapp_nls);
455 END IF;
456
457 ELSIF( l_instrument_type = C_INSTRTYPE_CREDITCARD ) THEN
458
459 IBY_NETUTILS_PVT.check_optional('OapfInstrFIName', p_pmtInstrRec.CreditCardInstr.FIName, l_url, l_db_nls, l_ecapp_nls);
460 IBY_NETUTILS_PVT.check_optional('OapfCCType', p_pmtInstrRec.CreditCardInstr.CC_Type, l_url, l_db_nls, l_ecapp_nls);
461 IBY_NETUTILS_PVT.check_mandatory('OapfInstrNum', p_pmtInstrRec.CreditCardInstr.CC_Num, l_url, l_db_nls, l_ecapp_nls);
462 IBY_NETUTILS_PVT.check_optional('OapfPmtInstrExp', to_char(p_pmtInstrRec.CreditCardInstr.CC_ExpDate,'YYYY-MM-DD'), l_url, l_db_nls, l_ecapp_nls);
463 IBY_NETUTILS_PVT.check_optional('OapfInstrHolderName', p_pmtInstrRec.CreditCardInstr.CC_HolderName , l_url, l_db_nls, l_ecapp_nls);
464 IBY_NETUTILS_PVT.check_optional('OapfInstrOwnerType', p_pmtInstrRec.CreditCardInstr.CC_HolderType, l_url, l_db_nls, l_ecapp_nls);
465 IBY_NETUTILS_PVT.check_optional('OapfInstrBuf', p_pmtInstrRec.CreditCardInstr.CC_Desc, l_url, l_db_nls, l_ecapp_nls);
466
467 --lmallick
468 --ownerid is optional (OIE registers cards without passing an ownerid)
469 IBY_NETUTILS_PVT.check_optional('OapfInstrOwnerId', p_pmtInstrRec.CreditCardInstr.Owner_Id, l_url, l_db_nls, l_ecapp_nls);
470
471 IBY_NETUTILS_PVT.check_optional('OapfSingleUseFlag', p_pmtInstrRec.CreditCardInstr.Single_Use_Flag, l_url, l_db_nls, l_ecapp_nls);
472 IBY_NETUTILS_PVT.check_optional('OapfInfoOnlyFlag', p_pmtInstrRec.CreditCardInstr.Info_Only_Flag, l_url, l_db_nls, l_ecapp_nls);
473
474 iby_debug_pub.add('Card_purpose passed is :' || p_pmtInstrRec.CreditCardInstr.Card_Purpose, iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
475 IBY_NETUTILS_PVT.check_optional('OapfCardPurpose', p_pmtInstrRec.CreditCardInstr.Card_Purpose, l_url, l_db_nls, l_ecapp_nls);
476 IBY_NETUTILS_PVT.check_optional('OapfActiveFlag', p_pmtInstrRec.CreditCardInstr.Active_Flag, l_url, l_db_nls, l_ecapp_nls);
477 IBY_NETUTILS_PVT.check_optional('OapfInactiveDate', TO_CHAR(p_pmtInstrRec.CreditCardInstr.Inactive_Date,'YYYY-MM-DD'), l_url, l_db_nls, l_ecapp_nls);
478
479 --bug 8423951
480 iby_debug_pub.add('Address ID is :' || p_pmtInstrRec.CreditCardInstr.Billing_Address_Id, iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
481 IBY_NETUTILS_PVT.check_optional('OapfInstrAddrId', p_pmtInstrRec.CreditCardInstr.Billing_Address_Id, l_url, l_db_nls, l_ecapp_nls);
482
483 IF ( p_pmtInstrRec.CreditCardInstr.Billing_Address1 is NOT NULL) THEN
484 IBY_NETUTILS_PVT.check_mandatory('OapfInstrAddrLine1', p_pmtInstrRec.CreditCardInstr.Billing_Address1, l_url, l_db_nls, l_ecapp_nls);
485 IBY_NETUTILS_PVT.check_optional('OapfInstrAddrLine2', p_pmtInstrRec.CreditCardInstr.Billing_Address2, l_url, l_db_nls, l_ecapp_nls);
486 IBY_NETUTILS_PVT.check_optional('OapfInstrAddrLine3', p_pmtInstrRec.CreditCardInstr.Billing_Address3, l_url, l_db_nls, l_ecapp_nls);
487 IBY_NETUTILS_PVT.check_mandatory('OapfInstrCity', p_pmtInstrRec.CreditCardInstr.Billing_City, l_url, l_db_nls, l_ecapp_nls);
488 IBY_NETUTILS_PVT.check_optional('OapfInstrCounty', p_pmtInstrRec.CreditCardInstr.Billing_County, l_url, l_db_nls, l_ecapp_nls);
489 IBY_NETUTILS_PVT.check_optional('OapfInstrState', p_pmtInstrRec.CreditCardInstr.Billing_State, l_url, l_db_nls, l_ecapp_nls);
490 IBY_NETUTILS_PVT.check_mandatory('OapfInstrCountry', p_pmtInstrRec.CreditCardInstr.Billing_Country, l_url, l_db_nls, l_ecapp_nls);
491 IBY_NETUTILS_PVT.check_optional('OapfInstrPostalCode', p_pmtInstrRec.CreditCardInstr.Billing_PostalCode, l_url, l_db_nls, l_ecapp_nls);
492
493 END IF;
494
495 IBY_NETUTILS_PVT.check_optional('OapfInstrAttCategory', p_pmtInstrRec.CreditCardInstr.Attribute_category, l_url, l_db_nls, l_ecapp_nls);
496 IBY_NETUTILS_PVT.check_optional('OapfInstrAttribute1', p_pmtInstrRec.CreditCardInstr.Attribute1, l_url, l_db_nls, l_ecapp_nls);
497 IBY_NETUTILS_PVT.check_optional('OapfInstrAttribute2', p_pmtInstrRec.CreditCardInstr.Attribute2, l_url, l_db_nls, l_ecapp_nls);
498 IBY_NETUTILS_PVT.check_optional('OapfInstrAttribute3', p_pmtInstrRec.CreditCardInstr.Attribute3, l_url, l_db_nls, l_ecapp_nls);
499 IBY_NETUTILS_PVT.check_optional('OapfInstrAttribute4', p_pmtInstrRec.CreditCardInstr.Attribute4, l_url, l_db_nls, l_ecapp_nls);
500 IBY_NETUTILS_PVT.check_optional('OapfInstrAttribute5', p_pmtInstrRec.CreditCardInstr.Attribute5, l_url, l_db_nls, l_ecapp_nls);
501 IBY_NETUTILS_PVT.check_optional('OapfInstrAttribute6', p_pmtInstrRec.CreditCardInstr.Attribute6, l_url, l_db_nls, l_ecapp_nls);
502 IBY_NETUTILS_PVT.check_optional('OapfInstrAttribute7', p_pmtInstrRec.CreditCardInstr.Attribute7, l_url, l_db_nls, l_ecapp_nls);
503 IBY_NETUTILS_PVT.check_optional('OapfInstrAttribute8', p_pmtInstrRec.CreditCardInstr.Attribute8, l_url, l_db_nls, l_ecapp_nls);
504 IBY_NETUTILS_PVT.check_optional('OapfInstrAttribute9', p_pmtInstrRec.CreditCardInstr.Attribute9, l_url, l_db_nls, l_ecapp_nls);
505 IBY_NETUTILS_PVT.check_optional('OapfInstrAttribute10', p_pmtInstrRec.CreditCardInstr.Attribute10, l_url, l_db_nls, l_ecapp_nls);
506 IBY_NETUTILS_PVT.check_optional('OapfInstrAttribute11', p_pmtInstrRec.CreditCardInstr.Attribute11, l_url, l_db_nls, l_ecapp_nls);
507 IBY_NETUTILS_PVT.check_optional('OapfInstrAttribute12', p_pmtInstrRec.CreditCardInstr.Attribute12, l_url, l_db_nls, l_ecapp_nls);
508 IBY_NETUTILS_PVT.check_optional('OapfInstrAttribute13', p_pmtInstrRec.CreditCardInstr.Attribute13, l_url, l_db_nls, l_ecapp_nls);
509 IBY_NETUTILS_PVT.check_optional('OapfInstrAttribute14', p_pmtInstrRec.CreditCardInstr.Attribute14, l_url, l_db_nls, l_ecapp_nls);
510 IBY_NETUTILS_PVT.check_optional('OapfInstrAttribute15', p_pmtInstrRec.CreditCardInstr.Attribute15, l_url, l_db_nls, l_ecapp_nls);
511 IBY_NETUTILS_PVT.check_optional('OapfInstrAttribute16', p_pmtInstrRec.CreditCardInstr.Attribute16, l_url, l_db_nls, l_ecapp_nls);
512 IBY_NETUTILS_PVT.check_optional('OapfInstrAttribute17', p_pmtInstrRec.CreditCardInstr.Attribute17, l_url, l_db_nls, l_ecapp_nls);
513 IBY_NETUTILS_PVT.check_optional('OapfInstrAttribute18', p_pmtInstrRec.CreditCardInstr.Attribute18, l_url, l_db_nls, l_ecapp_nls);
514 IBY_NETUTILS_PVT.check_optional('OapfInstrAttribute19', p_pmtInstrRec.CreditCardInstr.Attribute19, l_url, l_db_nls, l_ecapp_nls);
515 IBY_NETUTILS_PVT.check_optional('OapfInstrAttribute20', p_pmtInstrRec.CreditCardInstr.Attribute20, l_url, l_db_nls, l_ecapp_nls);
516 IBY_NETUTILS_PVT.check_optional('OapfInstrAttribute21', p_pmtInstrRec.CreditCardInstr.Attribute21, l_url, l_db_nls, l_ecapp_nls);
517 IBY_NETUTILS_PVT.check_optional('OapfInstrAttribute22', p_pmtInstrRec.CreditCardInstr.Attribute22, l_url, l_db_nls, l_ecapp_nls);
518 IBY_NETUTILS_PVT.check_optional('OapfInstrAttribute23', p_pmtInstrRec.CreditCardInstr.Attribute23, l_url, l_db_nls, l_ecapp_nls);
519 IBY_NETUTILS_PVT.check_optional('OapfInstrAttribute24', p_pmtInstrRec.CreditCardInstr.Attribute24, l_url, l_db_nls, l_ecapp_nls);
520 IBY_NETUTILS_PVT.check_optional('OapfInstrAttribute25', p_pmtInstrRec.CreditCardInstr.Attribute25, l_url, l_db_nls, l_ecapp_nls);
521 IBY_NETUTILS_PVT.check_optional('OapfInstrAttribute26', p_pmtInstrRec.CreditCardInstr.Attribute26, l_url, l_db_nls, l_ecapp_nls);
522 IBY_NETUTILS_PVT.check_optional('OapfInstrAttribute27', p_pmtInstrRec.CreditCardInstr.Attribute27, l_url, l_db_nls, l_ecapp_nls);
523 IBY_NETUTILS_PVT.check_optional('OapfInstrAttribute28', p_pmtInstrRec.CreditCardInstr.Attribute28, l_url, l_db_nls, l_ecapp_nls);
524 IBY_NETUTILS_PVT.check_optional('OapfInstrAttribute29', p_pmtInstrRec.CreditCardInstr.Attribute29, l_url, l_db_nls, l_ecapp_nls);
525 IBY_NETUTILS_PVT.check_optional('OapfInstrAttribute30', p_pmtInstrRec.CreditCardInstr.Attribute30, l_url, l_db_nls, l_ecapp_nls);
526 IBY_NETUTILS_PVT.check_optional('OapfRegstrInvalidCard', p_pmtInstrRec.CreditCardInstr.Register_Invalid_Card, l_url, l_db_nls, l_ecapp_nls);
527
528 END IF;
529
530 -- Send http request to the payment server
531 --l_html := UTL_HTTP.REQUEST(l_url);
532
533 /* Bug 6318167 */
534 IF p_pmtInstrRec.nls_lang_param IS NOT NULL THEN
535 IBY_NETUTILS_PVT.check_optional('OapfNlsLang', p_pmtInstrRec.nls_lang_param, l_url, l_db_nls, l_ecapp_nls);
536 END IF;
537
538 -- set the security token
539 iby_security_pkg.store_credential(l_url,l_sec_cred);
540 iby_netutils_pvt.check_mandatory('OapfSecurityToken', TO_CHAR(l_sec_cred),
541 l_url, l_db_nls, l_ecapp_nls);
542
543 -- iby_debug_pub.add(debug_msg => 'OraInstrAdd => full url: '|| l_url,
544 -- debug_level => iby_debug_pub.G_LEVEL_INFO,
545 -- module => l_dbg_mod);
546
547 l_pos := INSTR(l_url,'?');
548 l_post_body := SUBSTR(l_url,l_pos+1,length(l_url));
549 l_post_body := RTRIM(l_post_body,'&');
550 l_url := SUBSTR(l_url,1,l_pos-1);
551
552 --dbms_output.put_line('l_pos : '||l_pos);
553 --dbms_output.put_line('l_url : '||l_url);
554 --dbms_output.put_line('l_post_body : '||l_post_body);
555
556 -- sending Post Request
557 IBY_NETUTILS_PVT.POST_REQUEST(l_url,l_post_body,l_html);
558
559 -- Unpack the results
560 IBY_NETUTILS_PVT.UNPACK_RESULTS_URL(l_html,l_names,l_values, l_status, l_errcode, l_errmessage);
561
562 iby_debug_pub.add('Return Parameter count : '|| l_values.COUNT,
563 iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
564
565 --Raising Exception to handle errors in unpacking resulting html file.
566 IF (l_status = -1) THEN
567 iby_debug_pub.add('Unpack status error',
568 FND_LOG.LEVEL_UNEXPECTED,
569 G_DEBUG_MODULE || l_api_name);
570
571 FND_MESSAGE.SET_NAME('IBY', 'IBY_204403_HTML_UNPACK_ERROR');
572 FND_MSG_PUB.Add;
573 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
574 END IF;
575
576 --Raising Exception to handle Servlet related errors.
577 IF (l_values.COUNT = 0 ) THEN
578 iby_debug_pub.add('Names count=0',
579 iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
580
581 FND_MESSAGE.SET_NAME('IBY', 'IBY_204402_JSERVLET_ERROR');
582 FND_MSG_PUB.Add;
583 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
584 END IF;
585 iby_debug_pub.add('l_names count = ' ||l_names.COUNT,
586 iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
587 FOR i IN 1..l_names.COUNT LOOP
588
589 iby_debug_pub.add(l_names(i) || ': ' ||l_values(i),
590 iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
591 IF l_names(i) = 'OapfStatus' THEN
592 iby_debug_pub.add('OapfStatus = '||l_values(i),
593 iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
594 -- Setting API return status to 'U' if iPayment response status is not 0.
595 IF (NOT ecapp_return_status_success(TO_NUMBER(l_values(i)))) THEN
596 x_return_status := FND_API.G_RET_STS_ERROR;
597 END IF;
598 ELSIF l_names(i) = 'OapfInstrId' THEN
599 x_instr_id := l_values(i);
600 ELSIF l_names(i) = 'OapfCode' THEN
601 x_result.Result_Code := l_values(i);
602 ELSIF l_names(i) = 'OapfCause' THEN
603 x_result.Result_Message := l_values(i);
604 --
605 -- simply copy the mesg returned verbatim;
606 -- this is done rather than reconstructing via 'OapfCode'
607 -- as msg tokens will not otherwise be filled
608 --
609 FND_MESSAGE.SET_NAME('IBY', 'IBY_9999');
610 FND_MESSAGE.SET_TOKEN('MESSAGE_TEXT', l_values(i));
611 FND_MSG_PUB.ADD;
612 END IF;
613
614 END LOOP;
615
616 IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
617 -- op completed successfully
618 FND_MESSAGE.SET_NAME('IBY','IBY_204170');
619 FND_MSG_PUB.ADD;
620 END IF;
621
622 FND_MSG_PUB.Count_And_Get
623 (
624 p_count => x_msg_count,
625 p_data => x_msg_data
626 );
627
628 iby_debug_pub.add('Exit',iby_debug_pub.G_LEVEL_PROCEDURE,l_dbg_mod);
629 EXCEPTION
630
631 -- Catch for version mismatch and
632 -- if the validation level is not full.
633 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
634
635
636 x_return_status := FND_API.G_RET_STS_ERROR;
637 x_msg_count := 1;
638 x_msg_data := FND_MSG_PUB.GET(
639 p_encoded => FND_API.g_false,
640 P_MSG_INDEX => FND_MSG_PUB.Count_msg
641 );
642
643 -- Catch for all the known errors
644 -- thrown from this procedure only.
645 WHEN FND_API.G_EXC_ERROR THEN
646
647 x_return_status := FND_API.G_RET_STS_ERROR;
648 x_msg_count := 1;
649 x_msg_data := FND_MSG_PUB.GET(
650 p_encoded => FND_API.g_false,
651 P_MSG_INDEX => FND_MSG_PUB.Count_msg
652 );
653
654 -- Catch for all the known errors
655 -- thrown from the procedures that are called by this procedure.
656 -- Whenever there is an error in the procedures that are called,
657 -- this exception is raised as long as the SQLCODE is -20000.
658 WHEN ERROR_FROM_SUBPROC THEN
659 iby_debug_pub.add('Subproc exception..',
660 iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
661 --dbms_output.put_line('ERROR: ERROR_FROM_SUBPROC during call to API ');
662 --dbms_output.put_line('SQLerr is :'||substr(SQLERRM,1,150));
663 x_return_status := FND_API.G_RET_STS_ERROR;
664 iby_utility_pvt.handleException(SQLERRM,SQLCODE);
665 FND_MSG_PUB.Count_And_Get
666 ( p_count => x_msg_count,
667 p_data => x_msg_data
668 );
669
670 WHEN OTHERS THEN
671 iby_debug_pub.add('Others exception..'||SQLERRM||' code: '||SQLCODE,
672 iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
673 --dbms_output.put_line('ERROR: Exception occured during call to API ' );
674 --dbms_output.put_line('SQLerr is :'||substr(SQLERRM,1,150));
675 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
676 FND_MSG_PUB.Count_And_Get
677 ( p_count => x_msg_count,
678 p_data => x_msg_data
679 );
680
681 END OraInstrAdd;
682
683
684 -------------------------------------------------------------------------------
685 -- 2. OraInstrMod
686 -- Start of comments
687 -- API name : OraInstrMod
688 -- Type : Public
689 -- Pre-reqs : None
690 -- Function : Modifies an existing payment instruments in iPayment.
691 -- Parameters :
692 -- IN : p_api_version IN NUMBER Required
693 -- p_init_msg_list IN VARCHAR2 Optional
694 -- p_commit IN VARCHAR2 Optional
695 -- p_validation_level IN NUMBER Optional
696 -- p_payer_id IN VARCHAR2 Required
697 -- p_pmtInstrRec IN PmtInstr_rec_type Required
698 --
699 -- OUT : x_return_status OUT VARCHAR2
700 -- x_msg_count OUT VARCHAR2
701 -- x_msg_data OUT NUMBER
702 -- Version :
703 -- Current version 1.0
704 -- Previous version 1.0
705 -- Initial version 1.0
706 -- End of comments
707 -------------------------------------------------------------------------------
708
709
710 PROCEDURE OraInstrMod (p_api_version IN NUMBER,
711 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
712 p_commit IN VARCHAR2 := FND_API.G_TRUE,
713 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
714 p_payer_id IN VARCHAR2,
715 p_pmtInstrRec IN PmtInstr_rec_type,
716 x_return_status OUT NOCOPY VARCHAR2,
717 x_msg_count OUT NOCOPY NUMBER,
718 x_msg_data OUT NOCOPY VARCHAR2,
719 x_result OUT NOCOPY IBY_FNDCPT_COMMON_PUB.Result_rec_type
720 ) IS
721
722 l_api_name CONSTANT VARCHAR2(30) := 'OraInstrMod';
723 l_api_version CONSTANT NUMBER := 1.0;
724
725 l_instrument_type VARCHAR2(80) := C_INSTRTYPE_UNREG;
726 l_cnt INTEGER;
727
728 ERROR_FROM_SUBPROC Exception;
729
730 -- This will catch all the exceptions from the procedure which is
731 -- subsequently called.This will trap all exceptions that have
732 -- SQLCODE = -20000 and name it as 'ERROR_FROM_SUBPROC'.
733 PRAGMA EXCEPTION_INIT( ERROR_FROM_SUBPROC, -20000 );
734
735 BEGIN
736
737 -- Standard call to check for call compatibility.
738 IF NOT FND_API.Compatible_API_Call ( l_api_version,
739 p_api_version,
740 l_api_name,
741 G_PKG_NAME )
742 THEN
743 FND_MESSAGE.SET_NAME('IBY', 'IBY_204400_API_VER_MISMATCH');
744 FND_MSG_PUB.Add;
745 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
746 END IF;
747
748 -- Initialize message list if p_init_msg_list is set to TRUE.
749 IF FND_API.to_Boolean( p_init_msg_list ) THEN
750 FND_MSG_PUB.initialize;
751 END IF;
752
753 -- Verifying if validation level is FULL, which is expected for PUBLIC APIs.
754 IF (p_validation_level <> g_validation_level) THEN
755 FND_MESSAGE.SET_NAME('IBY', 'IBY_204401_VAL_LEVEL_ERROR');
756 FND_MSG_PUB.Add;
757 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
758 END IF;
759
760 -- Initialize API return status to success
761 x_return_status := FND_API.G_RET_STS_SUCCESS;
762
763 -- START OF BODY OF API
764
765 -- check whether the payer_id is missing.
766 IF( TRIM( p_payer_id ) is NULL ) THEN
767 FND_MESSAGE.SET_NAME('IBY', 'IBY_20486');
768 FND_MSG_PUB.Add;
769 RAISE FND_API.G_EXC_ERROR;
770 -- Returns message 'PAYER_ID is mandatory'
771 END IF;
772
773 -- Checks whether the instrument type passed is valid or not
774 l_instrument_type := p_pmtInstrRec.InstrumentType;
775 IF( ( l_instrument_type <> C_INSTRTYPE_CREDITCARD ) AND
776 ( l_instrument_type <> C_INSTRTYPE_PURCHASECARD ) AND
777 ( l_instrument_type <> C_INSTRTYPE_BANKACCT ) ) THEN
778 FND_MESSAGE.SET_NAME('IBY', 'IBY_20487');
779 FND_MSG_PUB.Add;
780 RAISE FND_API.G_EXC_ERROR;
781 -- Returns message 'Invalid instrument type passed'.
782 END IF;
783
784 -- Check whether card number is passed for type Credit card.
785 IF( l_instrument_type = C_INSTRTYPE_CREDITCARD ) THEN
786
787 -- Card number should NOT be passed as it is an existing instrument.
788 IF( (p_pmtInstrRec.CreditCardInstr.CC_Num is not NULL) OR
789 (p_pmtInstrRec.CreditCardInstr.CC_Type is not NULL) ) THEN
790 FND_MESSAGE.SET_NAME('IBY', 'IBY_20489');
791 FND_MSG_PUB.Add;
792 RAISE FND_API.G_EXC_ERROR;
793 -- Returns message 'Neither Card number nor Card Type should be passed'
794 END IF;
795
796 -- Check whether mandatory/not desirable is passed for type Purchase card.
797 ELSIF( l_instrument_type = C_INSTRTYPE_PURCHASECARD ) THEN
798
799 -- Card number should NOT be passed as it is an existing instrument.
800 IF( (p_pmtInstrRec.PurchaseCardInstr.PC_Num is not NULL) OR
801 (p_pmtInstrRec.PurchaseCardInstr.PC_Type is not NULL) ) THEN
802 FND_MESSAGE.SET_NAME('IBY', 'IBY_20489');
803 FND_MSG_PUB.Add;
804 RAISE FND_API.G_EXC_ERROR;
805 -- Returns message 'Neither Card number nor Card Type should be passed'
806 END IF;
807
808 -- Subtype is mandatory.
809 IF( p_pmtInstrRec.PurchaseCardInstr.PC_SubType is NULL ) THEN
810 FND_MESSAGE.SET_NAME('IBY', 'IBY_20483');
811 FND_MSG_PUB.Add;
812 RAISE FND_API.G_EXC_ERROR;
813 -- Returns message 'Mandatory field(s) missing'
814 END IF;
815
816 -- Bank_Id and BankAcct_Num may NOT be modified for type Bank Account.
817 ELSIF( l_instrument_type = C_INSTRTYPE_BANKACCT ) THEN
818
819 -- Bank Id and BankAcct_Num should NOT be passed as it is an existing instrument.
820 IF( ( p_pmtInstrRec.BankAcctInstr.Bank_Id is not NULL ) OR
821 ( p_pmtInstrRec.BankAcctInstr.BankAcct_Num is not NULL ) ) THEN
822 FND_MESSAGE.SET_NAME('IBY', 'IBY_20490');
823 FND_MSG_PUB.Add;
824 RAISE FND_API.G_EXC_ERROR;
825 -- Returns message 'Neither Bank Id nor Bank Account Number should be passed'
826 END IF;
827
828 END IF;
829
830 -- Finally call the procedures that will modify the instrument.
831 IF( l_instrument_type = C_INSTRTYPE_BANKACCT ) THEN
832 null;
833 /*
834 IBY_BANKACCT_PKG.modifyBankAcct( NULL,
835 g_owner_type,
836 p_payer_id,
837 p_pmtInstrRec.BankAcctInstr.FIName,
838 NULL,
839 p_pmtInstrRec.BankAcctInstr.Bank_Id,
840 p_pmtInstrRec.BankAcctInstr.Branch_Id,
841 UPPER(p_pmtInstrRec.BankAcctInstr.BankAcct_Type),
842 p_pmtInstrRec.BankAcctInstr.BankAcct_Num,
843 p_pmtInstrRec.BankAcctInstr.BankAcct_HolderName,
844 p_pmtInstrRec.BankAcctInstr.Bank_Desc,
845 p_pmtInstrRec.Encryption_Key,
846 p_pmtInstrRec.BankAcctInstr.Instr_Id
847 );
848
849 */
850 ELSIF( l_instrument_type = C_INSTRTYPE_PURCHASECARD ) THEN
851 IBY_CREDITCARD_PKG.Update_Card
852 (FND_API.G_FALSE,
853 p_pmtInstrRec.PurchaseCardInstr.Instr_Id,
854 p_pmtInstrRec.PurchaseCardInstr.Owner_Id,
855 p_pmtInstrRec.PurchaseCardInstr.PC_HolderName,
856 p_pmtInstrRec.PurchaseCardInstr.Billing_Address_Id,
857 'S',
858 p_pmtInstrRec.PurchaseCardInstr.Billing_PostalCode,
859 p_pmtInstrRec.PurchaseCardInstr.Billing_Country,
860 p_pmtInstrRec.PurchaseCardInstr.PC_ExpDate,
861 'CREDITCARD',
862 'Y',
863 p_pmtInstrRec.PurchaseCardInstr.PC_Subtype,
864 p_pmtInstrRec.PurchaseCardInstr.FIName,
865 p_pmtInstrRec.PurchaseCardInstr.Single_Use_Flag,
866 p_pmtInstrRec.PurchaseCardInstr.Info_Only_Flag,
867 p_pmtInstrRec.PurchaseCardInstr.Card_Purpose,
868 p_pmtInstrRec.PurchaseCardInstr.PC_Desc,
869 p_pmtInstrRec.PurchaseCardInstr.Active_Flag,
870 p_pmtInstrRec.PurchaseCardInstr.Inactive_Date,
871 p_pmtInstrRec.PurchaseCardInstr.attribute_category,
872 p_pmtInstrRec.PurchaseCardInstr.attribute1,
873 p_pmtInstrRec.PurchaseCardInstr.attribute2,
874 p_pmtInstrRec.PurchaseCardInstr.attribute3,
875 p_pmtInstrRec.PurchaseCardInstr.attribute4,
876 p_pmtInstrRec.PurchaseCardInstr.attribute5,
877 p_pmtInstrRec.PurchaseCardInstr.attribute6,
878 p_pmtInstrRec.PurchaseCardInstr.attribute7,
879 p_pmtInstrRec.PurchaseCardInstr.attribute8,
880 p_pmtInstrRec.PurchaseCardInstr.attribute9,
881 p_pmtInstrRec.PurchaseCardInstr.attribute10,
882 p_pmtInstrRec.PurchaseCardInstr.attribute11,
883 p_pmtInstrRec.PurchaseCardInstr.attribute12,
884 p_pmtInstrRec.PurchaseCardInstr.attribute13,
885 p_pmtInstrRec.PurchaseCardInstr.attribute14,
886 p_pmtInstrRec.PurchaseCardInstr.attribute15,
887 p_pmtInstrRec.PurchaseCardInstr.attribute16,
888 p_pmtInstrRec.PurchaseCardInstr.attribute17,
889 p_pmtInstrRec.PurchaseCardInstr.attribute18,
890 p_pmtInstrRec.PurchaseCardInstr.attribute19,
891 p_pmtInstrRec.PurchaseCardInstr.attribute20,
892 p_pmtInstrRec.PurchaseCardInstr.attribute21,
893 p_pmtInstrRec.PurchaseCardInstr.attribute22,
894 p_pmtInstrRec.PurchaseCardInstr.attribute23,
895 p_pmtInstrRec.PurchaseCardInstr.attribute24,
896 p_pmtInstrRec.PurchaseCardInstr.attribute25,
897 p_pmtInstrRec.PurchaseCardInstr.attribute26,
898 p_pmtInstrRec.PurchaseCardInstr.attribute27,
899 p_pmtInstrRec.PurchaseCardInstr.attribute28,
900 p_pmtInstrRec.PurchaseCardInstr.attribute29,
901 p_pmtInstrRec.PurchaseCardInstr.attribute30,
902 x_result.Result_Code,
903 null,null
904 );
905 ELSIF( l_instrument_type = C_INSTRTYPE_CREDITCARD ) THEN
906 IBY_CREDITCARD_PKG.Update_Card
907 (FND_API.G_FALSE,
908 p_pmtInstrRec.CreditCardInstr.Instr_Id,
909 p_pmtInstrRec.CreditCardInstr.Owner_Id,
910 p_pmtInstrRec.CreditCardInstr.CC_HolderName,
911 p_pmtInstrRec.CreditCardInstr.Billing_Address_Id,
912 'S',
913 p_pmtInstrRec.CreditCardInstr.Billing_PostalCode,
914 p_pmtInstrRec.CreditCardInstr.Billing_Country,
915 p_pmtInstrRec.CreditCardInstr.CC_ExpDate,
916 'CREDITCARD',
917 'N',
918 NULL,
919 p_pmtInstrRec.CreditCardInstr.FIName,
920 p_pmtInstrRec.CreditCardInstr.Single_Use_Flag,
921 p_pmtInstrRec.CreditCardInstr.Info_Only_Flag,
922 p_pmtInstrRec.CreditCardInstr.Card_Purpose,
923 p_pmtInstrRec.CreditCardInstr.CC_Desc,
924 p_pmtInstrRec.CreditCardInstr.Active_Flag,
925 p_pmtInstrRec.CreditCardInstr.Inactive_Date,
926 p_pmtInstrRec.CreditCardInstr.attribute_category,
927 p_pmtInstrRec.CreditCardInstr.attribute1,
928 p_pmtInstrRec.CreditCardInstr.attribute2,
929 p_pmtInstrRec.CreditCardInstr.attribute3,
930 p_pmtInstrRec.CreditCardInstr.attribute4,
931 p_pmtInstrRec.CreditCardInstr.attribute5,
932 p_pmtInstrRec.CreditCardInstr.attribute6,
933 p_pmtInstrRec.CreditCardInstr.attribute7,
934 p_pmtInstrRec.CreditCardInstr.attribute8,
935 p_pmtInstrRec.CreditCardInstr.attribute9,
936 p_pmtInstrRec.CreditCardInstr.attribute10,
937 p_pmtInstrRec.CreditCardInstr.attribute11,
938 p_pmtInstrRec.CreditCardInstr.attribute12,
939 p_pmtInstrRec.CreditCardInstr.attribute13,
940 p_pmtInstrRec.CreditCardInstr.attribute14,
941 p_pmtInstrRec.CreditCardInstr.attribute15,
942 p_pmtInstrRec.CreditCardInstr.attribute16,
943 p_pmtInstrRec.CreditCardInstr.attribute17,
944 p_pmtInstrRec.CreditCardInstr.attribute18,
945 p_pmtInstrRec.CreditCardInstr.attribute19,
946 p_pmtInstrRec.CreditCardInstr.attribute20,
947 p_pmtInstrRec.CreditCardInstr.attribute21,
948 p_pmtInstrRec.CreditCardInstr.attribute22,
949 p_pmtInstrRec.CreditCardInstr.attribute23,
950 p_pmtInstrRec.CreditCardInstr.attribute24,
951 p_pmtInstrRec.CreditCardInstr.attribute25,
952 p_pmtInstrRec.CreditCardInstr.attribute26,
953 p_pmtInstrRec.CreditCardInstr.attribute27,
954 p_pmtInstrRec.CreditCardInstr.attribute28,
955 p_pmtInstrRec.CreditCardInstr.attribute29,
956 p_pmtInstrRec.CreditCardInstr.attribute30,
957 x_result.Result_Code,
958 null,null
959 );
960 END IF;
961
962
963 -- Return success when everything is fine.
964 x_msg_count := 1;
965
966 -- Returns message 'operation completed successfully.'
967 FND_MESSAGE.SET_NAME('IBY', 'IBY_204170' );
968 FND_MSG_PUB.Add;
969 x_msg_data := FND_MSG_PUB.GET(
970 p_encoded => FND_API.g_false,
971 P_MSG_INDEX => FND_MSG_PUB.Count_msg
972 );
973
974 EXCEPTION
975
976 -- Catch for version mismatch and
977 -- if the validation level is not full.
978 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
979
980 x_return_status := FND_API.G_RET_STS_ERROR;
981 x_msg_count := 1;
982 x_msg_data := FND_MSG_PUB.GET(
983 p_encoded => FND_API.g_false,
984 P_MSG_INDEX => FND_MSG_PUB.Count_msg
985 );
986
987 -- Catch for all the known errors
988 -- thrown from this procedure only.
989 WHEN FND_API.G_EXC_ERROR THEN
990
991 x_return_status := FND_API.G_RET_STS_ERROR;
992 x_msg_count := 1;
993 x_msg_data := FND_MSG_PUB.GET(
994 p_encoded => FND_API.g_false,
995 P_MSG_INDEX => FND_MSG_PUB.Count_msg
996 );
997
998 -- Catch for all the known errors
999 -- thrown from the procedures that are called by this procedure.
1000 -- Whenever there is an error in the procedures that are called,
1001 -- this exception is raised as long as the SQLCODE is -20000.
1002 WHEN ERROR_FROM_SUBPROC THEN
1003
1004 x_return_status := FND_API.G_RET_STS_ERROR;
1005 iby_utility_pvt.handleException(SQLERRM,SQLCODE);
1006 FND_MSG_PUB.Count_And_Get
1007 ( p_count => x_msg_count,
1008 p_data => x_msg_data
1009 );
1010
1011 WHEN OTHERS THEN
1012
1013 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1014 FND_MSG_PUB.Count_And_Get
1015 ( p_count => x_msg_count,
1016 p_data => x_msg_data
1017 );
1018
1019
1020 END OraInstrMod;
1021
1022
1023 -------------------------------------------------------------------------------
1024 -- 3. OraInstrDel
1025 -- Start of comments
1026 -- API name : OraInstrDel
1027 -- Type : Public
1028 -- Pre-reqs : None
1029 -- Function : Deletes an existing payment instruments in iPayment.
1030 -- Parameters :
1031 -- IN : p_api_version IN NUMBER Required
1032 -- p_init_msg_list IN VARCHAR2 Optional
1033 -- p_commit IN VARCHAR2 Optional
1034 -- p_validation_level IN NUMBER Optional
1035 -- p_payer_id IN VARCHAR2 Required
1036 -- p_instr_id IN NUMBER Required
1037 --
1038 -- OUT : x_return_status OUT VARCHAR2
1039 -- x_msg_count OUT VARCHAR2
1040 -- x_msg_data OUT NUMBER
1041 -- Version :
1042 -- Current version 1.0
1043 -- Previous version 1.0
1044 -- Initial version 1.0
1045 -- End of comments
1046 -------------------------------------------------------------------------------
1047
1048
1049 PROCEDURE OraInstrDel ( p_api_version IN NUMBER,
1050 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1051 p_commit IN VARCHAR2 := FND_API.G_TRUE,
1052 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1053 p_payer_id IN VARCHAR2,
1054 p_instr_id IN NUMBER,
1055 x_return_status OUT NOCOPY VARCHAR2,
1056 x_msg_count OUT NOCOPY NUMBER,
1057 x_msg_data OUT NOCOPY VARCHAR2
1058 ) IS
1059
1060 l_api_name CONSTANT VARCHAR2(30) := 'OraInstrDel';
1061 l_api_version CONSTANT NUMBER := 1.0;
1062
1063 ERROR_FROM_SUBPROC Exception;
1064
1065 -- This will catch all the exceptions from the procedure which is
1066 -- subsequently called.This will trap all exceptions that have
1067 -- SQLCODE = -20000 and name it as 'ERROR_FROM_SUBPROC'.
1068 PRAGMA EXCEPTION_INIT( ERROR_FROM_SUBPROC, -20000 );
1069
1070 BEGIN
1071
1072 -- Standard call to check for call compatibility.
1073 IF NOT FND_API.Compatible_API_Call ( l_api_version,
1074 p_api_version,
1075 l_api_name,
1076 G_PKG_NAME )
1077 THEN
1078 FND_MESSAGE.SET_NAME('IBY', 'IBY_204400_API_VER_MISMATCH');
1079 FND_MSG_PUB.Add;
1080 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1081 END IF;
1082
1083 -- Initialize message list if p_init_msg_list is set to TRUE.
1084 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1085 FND_MSG_PUB.initialize;
1086 END IF;
1087
1088 -- Verifying if validation level is FULL, which is expected for PUBLIC APIs.
1089 IF (p_validation_level <> g_validation_level) THEN
1090 FND_MESSAGE.SET_NAME('IBY', 'IBY_204401_VAL_LEVEL_ERROR');
1091 FND_MSG_PUB.Add;
1092 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1093 END IF;
1094
1095 -- Initialize API return status to success
1096 x_return_status := FND_API.G_RET_STS_SUCCESS;
1097
1098 -- START OF BODY OF API
1099
1100 -- check whether the payer_id is missing.
1101 IF( TRIM( p_payer_id ) is NULL ) THEN
1102 FND_MESSAGE.SET_NAME('IBY', 'IBY_20486');
1103 FND_MSG_PUB.Add;
1104 RAISE FND_API.G_EXC_ERROR;
1105 -- Returns message 'PAYER_ID is mandatory'
1106 END IF;
1107
1108 -- check whether the instr_id is missing.
1109 IF( p_instr_id is NULL ) THEN
1110 FND_MESSAGE.SET_NAME('IBY', 'IBY_20483');
1111 FND_MSG_PUB.Add;
1112 RAISE FND_API.G_EXC_ERROR;
1113 -- Returns message 'Mandatory field(s) missing'
1114 END IF;
1115
1116 -- Call the procedure that will delete the instrument.
1117 IBY_INSTRHOLDER_PKG.deleteHolderInstr( NULL,
1118 g_owner_type,
1119 p_payer_id,
1120 NULL,
1121 p_instr_id
1122 );
1123
1124 -- Return success when everything is fine.
1125 x_msg_count := 1;
1126
1127 -- Returns message 'operation completed successfully.'
1128 FND_MESSAGE.SET_NAME('IBY', 'IBY_204170' );
1129 FND_MSG_PUB.Add;
1130 x_msg_data := FND_MSG_PUB.GET(
1131 p_encoded => FND_API.g_false,
1132 P_MSG_INDEX => FND_MSG_PUB.Count_msg
1133 );
1134
1135 EXCEPTION
1136
1137 -- Catch for version mismatch and
1138 -- if the validation level is not full.
1139 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1140
1141 x_return_status := FND_API.G_RET_STS_ERROR;
1142 x_msg_count := 1;
1143 x_msg_data := FND_MSG_PUB.GET(
1144 p_encoded => FND_API.g_false,
1145 P_MSG_INDEX => FND_MSG_PUB.Count_msg
1146 );
1147
1148 -- Catch for all the known errors
1149 -- thrown from this procedure only.
1150 WHEN FND_API.G_EXC_ERROR THEN
1151
1152 x_return_status := FND_API.G_RET_STS_ERROR;
1153 x_msg_count := 1;
1154 x_msg_data := FND_MSG_PUB.GET(
1155 p_encoded => FND_API.g_false,
1156 P_MSG_INDEX => FND_MSG_PUB.Count_msg
1157 );
1158
1159 -- Catch for all the known errors
1160 -- thrown from the procedures that are called by this procedure.
1161 -- Whenever there is an error in the procedures that are called,
1162 -- this exception is raised as long as the SQLCODE is -20000.
1163 WHEN ERROR_FROM_SUBPROC THEN
1164
1165 x_return_status := FND_API.G_RET_STS_ERROR;
1166 iby_utility_pvt.handleException(SQLERRM,SQLCODE);
1167 FND_MSG_PUB.Count_And_Get
1168 ( p_count => x_msg_count,
1169 p_data => x_msg_data
1170 );
1171
1172 WHEN OTHERS THEN
1173
1174 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1175 FND_MSG_PUB.Count_And_Get
1176 ( p_count => x_msg_count,
1177 p_data => x_msg_data
1178 );
1179
1180
1181 END OraInstrDel;
1182
1183 -------------------------------------------------------------------------------
1184 -- 4. OraInstrInq
1185 -- Start of comments
1186 -- API name : OraInstrInq
1187 -- Type : Public
1188 -- Pre-reqs : None
1189 -- Function : Returns all the payment instruments that a payer may have.
1190 -- This is based on the payer_id only.
1191 -- Parameters :
1192 -- IN : p_api_version IN NUMBER Required
1193 -- p_init_msg_list IN VARCHAR2 Optional
1194 -- p_commit IN VARCHAR2 Optional
1195 -- p_validation_level IN NUMBER Optional
1196 -- p_payer_id IN VARCHAR2 Required
1197 --
1198 -- OUT : x_return_status OUT VARCHAR2
1199 -- x_msg_count OUT VARCHAR2
1200 -- x_msg_data OUT NUMBER
1201 -- x_creditcard_tbl OUT CreditCard_tbl_type
1202 -- x_purchasecard_tbl OUT PurchaseCard_tbl_type
1203 -- x_bankacct_tbl OUT BankAcct_tbl_type
1204 -- Version :
1205 -- Current version 1.0
1206 -- Previous version 1.0
1207 -- Initial version 1.0
1208 -- End of comments
1209 -------------------------------------------------------------------------------
1210
1211 PROCEDURE OraInstrInq ( p_api_version IN NUMBER,
1212 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1213 p_commit IN VARCHAR2 := FND_API.G_TRUE,
1214 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1215 p_payer_id IN VARCHAR2,
1216 x_return_status OUT NOCOPY VARCHAR2,
1217 x_msg_count OUT NOCOPY NUMBER,
1218 x_msg_data OUT NOCOPY VARCHAR2,
1219 x_creditcard_tbl OUT NOCOPY CreditCard_tbl_type,
1220 x_purchasecard_tbl OUT NOCOPY PurchaseCard_tbl_type,
1221 x_bankacct_tbl OUT NOCOPY BankAcct_tbl_type
1222 )
1223 IS
1224 BEGIN
1225 OraInstrInq(p_api_version,p_init_msg_list,p_commit,p_validation_level,p_payer_id,
1226 NULL,x_return_status,x_msg_count,x_msg_data,x_creditcard_tbl,
1227 x_purchasecard_tbl,x_bankacct_tbl);
1228 END OraInstrInq;
1229
1230
1231 PROCEDURE OraInstrInq ( p_api_version IN NUMBER,
1232 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1233 p_commit IN VARCHAR2 := FND_API.G_TRUE,
1234 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1235 p_payer_id IN VARCHAR2,
1236 p_sys_sec_key IN VARCHAR2,
1237 x_return_status OUT NOCOPY VARCHAR2,
1238 x_msg_count OUT NOCOPY NUMBER,
1239 x_msg_data OUT NOCOPY VARCHAR2,
1240 x_creditcard_tbl OUT NOCOPY CreditCard_tbl_type,
1241 x_purchasecard_tbl OUT NOCOPY PurchaseCard_tbl_type,
1242 x_bankacct_tbl OUT NOCOPY BankAcct_tbl_type
1243 ) IS
1244
1245 l_api_name CONSTANT VARCHAR2(30) := 'OraInstrInq';
1246 l_api_version CONSTANT NUMBER := 1.0;
1247
1248 l_count INTEGER;
1249
1250 BEGIN
1251
1252 -- Standard call to check for call compatibility.
1253 IF NOT FND_API.Compatible_API_Call ( l_api_version,
1254 p_api_version,
1255 l_api_name,
1256 G_PKG_NAME )
1257 THEN
1258 FND_MESSAGE.SET_NAME('IBY', 'IBY_204400_API_VER_MISMATCH');
1259 FND_MSG_PUB.Add;
1260 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1261 END IF;
1262
1263 -- Initialize message list if p_init_msg_list is set to TRUE.
1264 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1265 FND_MSG_PUB.initialize;
1266 END IF;
1267
1268 -- Verifying if validation level is FULL, which is expected for PUBLIC APIs.
1269 IF (p_validation_level <> g_validation_level) THEN
1270 FND_MESSAGE.SET_NAME('IBY', 'IBY_204401_VAL_LEVEL_ERROR');
1271 FND_MSG_PUB.Add;
1272 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1273 END IF;
1274
1275 -- Initialize API return status to success
1276 x_return_status := FND_API.G_RET_STS_SUCCESS;
1277
1278 -- START OF BODY OF API
1279
1280 -- check whether the payer_id is missing.
1281 IF( TRIM( p_payer_id ) is NULL ) THEN
1282 FND_MESSAGE.SET_NAME('IBY', 'IBY_20486');
1283 FND_MSG_PUB.Add;
1284 RAISE FND_API.G_EXC_ERROR;
1285 -- Returns message 'PAYER_ID is mandatory'
1286 END IF;
1287
1288 -- Check whether the payer exists.If not,
1289 -- then we don't even try to fetch the records.
1290 SELECT count(*) INTO l_count
1291 FROM IBY_INSTRHOLDER
1292 WHERE ownerid = p_payer_id
1293 AND activestatus = 1;
1294
1295 -- If nothing is found throw an error.
1296 IF( l_count = 0 ) THEN
1297 FND_MESSAGE.SET_NAME('IBY', 'IBY_20491');
1298 FND_MSG_PUB.Add;
1299 RAISE FND_API.G_EXC_ERROR;
1300 -- Returns message 'PAYER_ID does not exist'
1301 END IF;
1302
1303 -- Call the utility procedure that will do the job
1304 -- of returning all the instruments.
1305 Get_Instrument_Details ( payer_id => p_payer_id,
1306 instr_id => NULL,
1307 sys_master_key => p_sys_sec_key,
1308 creditcard_tbl => x_creditcard_tbl,
1309 purchasecard_tbl => x_purchasecard_tbl,
1310 bankacct_tbl => x_bankacct_tbl
1311 );
1312
1313
1314 -- Return success when everything is fine.
1315 x_msg_count := 1;
1316
1317 IF( x_creditcard_tbl.count = 0 AND
1318 x_purchasecard_tbl.count = 0 AND
1319 x_bankacct_tbl.count = 0 ) THEN
1320 -- Returns message 'No records found matching the given criteria.'
1321 FND_MESSAGE.SET_NAME('IBY', 'IBY_204041' );
1322 ELSE
1323 -- Returns message 'operation completed successfully.'
1324 FND_MESSAGE.SET_NAME('IBY', 'IBY_204170' );
1325 END IF;
1326
1327 FND_MSG_PUB.Add;
1328 x_msg_data := FND_MSG_PUB.GET(
1329 p_encoded => FND_API.g_false,
1330 P_MSG_INDEX => FND_MSG_PUB.Count_msg
1331 );
1332
1333 EXCEPTION
1334
1335 -- Catch for version mismatch and
1336 -- if the validation level is not full.
1337 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1338
1339 x_return_status := FND_API.G_RET_STS_ERROR;
1340 x_msg_count := 1;
1341 x_msg_data := FND_MSG_PUB.GET(
1342 p_encoded => FND_API.g_false,
1343 P_MSG_INDEX => FND_MSG_PUB.Count_msg
1344 );
1345
1346 -- Catch for all the known errors
1347 -- thrown from this procedure only.
1348 WHEN FND_API.G_EXC_ERROR THEN
1349
1350 x_return_status := FND_API.G_RET_STS_ERROR;
1351 x_msg_count := 1;
1352 x_msg_data := FND_MSG_PUB.GET(
1353 p_encoded => FND_API.g_false,
1354 P_MSG_INDEX => FND_MSG_PUB.Count_msg
1355 );
1356
1357 WHEN OTHERS THEN
1358
1359 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1360 iby_utility_pvt.handleException(SQLERRM,SQLCODE);
1361 FND_MSG_PUB.Count_And_Get
1362 ( p_count => x_msg_count,
1363 p_data => x_msg_data
1364 );
1365
1366 END OraInstrInq;
1367
1368 -------------------------------------------------------------------------------
1369 -- 5. OraInstrInq
1370 -- Start of comments
1371 -- API name : OraInstrInq
1372 -- Type : Public
1373 -- Pre-reqs : None
1374 -- Function : Returns the payment instrument information for an instr_id.
1375 -- This is based on the payer_id and instr_id.
1376 -- Parameters :
1377 -- IN : p_api_version IN NUMBER Required
1378 -- p_init_msg_list IN VARCHAR2 Optional
1379 -- p_commit IN VARCHAR2 Optional
1380 -- p_validation_level IN NUMBER Optional
1381 -- p_payer_id IN VARCHAR2 Required
1382 -- p_instr_id IN NUMBER Required
1383 --
1384 -- OUT : x_return_status OUT VARCHAR2
1385 -- x_msg_count OUT VARCHAR2
1386 -- x_msg_data OUT NUMBER
1387 -- x_pmtInstrRec OUT PmtInstr_rec_type
1388 -- Version :
1389 -- Current version 1.0
1390 -- Previous version 1.0
1391 -- Initial version 1.0
1392 -- End of comments
1393 -------------------------------------------------------------------------------
1394
1395 PROCEDURE OraInstrInq ( p_api_version IN NUMBER,
1396 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1397 p_commit IN VARCHAR2 := FND_API.G_TRUE,
1398 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1399 p_payer_id IN VARCHAR2,
1400 p_instr_id IN NUMBER,
1401 x_return_status OUT NOCOPY VARCHAR2,
1402 x_msg_count OUT NOCOPY NUMBER,
1403 x_msg_data OUT NOCOPY VARCHAR2,
1404 x_pmtInstrRec OUT NOCOPY PmtInstr_rec_type
1405 )
1406 IS
1407 BEGIN
1408 OraInstrInq(p_api_version,p_init_msg_list,p_commit,p_validation_level,p_payer_id,
1409 p_instr_id,NULL,x_return_status,x_msg_count,x_msg_data,x_pmtInstrRec);
1410 END OraInstrInq;
1411
1412
1413 PROCEDURE OraInstrInq ( p_api_version IN NUMBER,
1414 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1415 p_commit IN VARCHAR2 := FND_API.G_TRUE,
1416 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1417 p_payer_id IN VARCHAR2,
1418 p_instr_id IN NUMBER,
1419 p_sys_sec_key IN VARCHAR2,
1420 x_return_status OUT NOCOPY VARCHAR2,
1421 x_msg_count OUT NOCOPY NUMBER,
1422 x_msg_data OUT NOCOPY VARCHAR2,
1423 x_pmtInstrRec OUT NOCOPY PmtInstr_rec_type
1424 ) IS
1425
1426 l_api_name CONSTANT VARCHAR2(30) := 'OraInstrInq';
1427 l_api_version CONSTANT NUMBER := 1.0;
1428
1429 l_count INTEGER;
1430
1431 l_creditcard_tbl CreditCard_tbl_type;
1432 l_purchasecard_tbl PurchaseCard_tbl_type;
1433 l_bankacct_tbl BankAcct_tbl_type;
1434
1435 BEGIN
1436
1437 -- Standard call to check for call compatibility.
1438 IF NOT FND_API.Compatible_API_Call ( l_api_version,
1439 p_api_version,
1440 l_api_name,
1441 G_PKG_NAME )
1442 THEN
1443 FND_MESSAGE.SET_NAME('IBY', 'IBY_204400_API_VER_MISMATCH');
1444 FND_MSG_PUB.Add;
1445 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1446 END IF;
1447
1448 -- Initialize message list if p_init_msg_list is set to TRUE.
1449 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1450 FND_MSG_PUB.initialize;
1451 END IF;
1452
1453 -- Verifying if validation level is FULL, which is expected for PUBLIC APIs.
1454 IF (p_validation_level <> g_validation_level) THEN
1455 FND_MESSAGE.SET_NAME('IBY', 'IBY_204401_VAL_LEVEL_ERROR');
1456 FND_MSG_PUB.Add;
1457 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1458 END IF;
1459
1460 -- Initialize API return status to success
1461 x_return_status := FND_API.G_RET_STS_SUCCESS;
1462
1463 -- START OF BODY OF API
1464
1465 -- Initialize the instrument type
1466 x_pmtInstrRec.InstrumentType := C_INSTRTYPE_UNREG;
1467
1468 -- check whether the payer_id is missing.
1469 IF( TRIM( p_payer_id ) is NULL ) THEN
1470 FND_MESSAGE.SET_NAME('IBY', 'IBY_20486');
1471 FND_MSG_PUB.Add;
1472 RAISE FND_API.G_EXC_ERROR;
1473 -- Returns message 'PAYER_ID is mandatory'
1474 END IF;
1475
1476 -- check whether the instr_id is missing.
1477 IF( p_instr_id is NULL ) THEN
1478 FND_MESSAGE.SET_NAME('IBY', 'IBY_20483');
1479 FND_MSG_PUB.Add;
1480 RAISE FND_API.G_EXC_ERROR;
1481 -- Returns message 'Mandatory field(s) missing'
1482 END IF;
1483
1484 -- Check whether the payer exists.
1485 -- If not, then we don't even try to fetch the records.
1486 SELECT count(*) INTO l_count
1487 FROM IBY_INSTRHOLDER
1488 WHERE ownerid = p_payer_id
1489 AND activestatus = 1;
1490
1491 -- Throw an exception when payer not found.
1492 IF( l_count = 0 ) THEN
1493 FND_MESSAGE.SET_NAME('IBY', 'IBY_20491');
1494 FND_MSG_PUB.Add;
1495 RAISE FND_API.G_EXC_ERROR;
1496 -- Returns message 'PAYER_ID does not exist'
1497 END IF;
1498
1499 -- Check whether the instrument exists.
1500 -- If not, then we don't even try to fetch the records.
1501 SELECT count(*) INTO l_count
1502 FROM IBY_INSTRHOLDER
1503 WHERE instrid = p_instr_id
1504 AND activestatus = 1;
1505
1506 -- Throw an exception.
1507 IF( l_count = 0 ) THEN
1508 FND_MESSAGE.SET_NAME('IBY', 'IBY_20492');
1509 FND_MSG_PUB.Add;
1510 RAISE FND_API.G_EXC_ERROR;
1511 -- Returns message 'Instrument does not exist'
1512 END IF;
1513
1514 -- Check whether the payer holds the instrument.
1515 -- If not, then we don't even try to fetch the records.
1516 SELECT count(*) INTO l_count
1517 FROM IBY_INSTRHOLDER
1518 WHERE instrid = p_instr_id
1519 AND ownerid = p_payer_id
1520 AND activestatus = 1;
1521
1522 -- Throw an exception when nothing is found.
1523 IF( l_count = 0 ) THEN
1524 FND_MESSAGE.SET_NAME('IBY', 'IBY_20511');
1525 FND_MSG_PUB.Add;
1526 RAISE FND_API.G_EXC_ERROR;
1527 -- Returns message 'User does not hold instr'
1528 END IF;
1529
1530 -- Call the utility procedure that will do the job
1531 -- of returning the instrument information.
1532 Get_Instrument_Details ( payer_id => p_payer_id,
1533 instr_id => p_instr_id,
1534 sys_master_key => p_sys_sec_key,
1535 creditcard_tbl => l_creditcard_tbl,
1536 purchasecard_tbl => l_purchasecard_tbl,
1537 bankacct_tbl => l_bankacct_tbl
1538 );
1539
1540 IF( l_creditcard_tbl.count <> 0 ) THEN
1541 x_pmtInstrRec.InstrumentType := C_INSTRTYPE_CREDITCARD;
1542 x_pmtInstrRec.CreditCardInstr := l_creditcard_tbl(1);
1543 ELSIF( l_purchasecard_tbl.count <> 0 ) THEN
1544 x_pmtInstrRec.InstrumentType := C_INSTRTYPE_PURCHASECARD;
1545 x_pmtInstrRec.PurchaseCardInstr := l_purchasecard_tbl(1);
1546 ELSIF( l_bankacct_tbl.count <> 0 ) THEN
1547 x_pmtInstrRec.InstrumentType := C_INSTRTYPE_BANKACCT;
1548 x_pmtInstrRec.BankAcctInstr := l_bankacct_tbl(1);
1549 END IF;
1550
1551 -- Return success when everything is fine.
1552 x_msg_count := 1;
1553
1554 -- Returns message 'operation completed successfully.'
1555 FND_MESSAGE.SET_NAME('IBY', 'IBY_204170' );
1556 FND_MSG_PUB.Add;
1557 x_msg_data := FND_MSG_PUB.GET(
1558 p_encoded => FND_API.g_false,
1559 P_MSG_INDEX => FND_MSG_PUB.Count_msg
1560 );
1561
1562 EXCEPTION
1563
1564 -- Catch for version mismatch and
1565 -- if the validation level is not full.
1566 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1567
1568 x_return_status := FND_API.G_RET_STS_ERROR;
1569 x_msg_count := 1;
1570 x_msg_data := FND_MSG_PUB.GET(
1571 p_encoded => FND_API.g_false,
1572 P_MSG_INDEX => FND_MSG_PUB.Count_msg
1573 );
1574
1575 -- Catch for all the known errors
1576 -- thrown from this procedure only.
1577 WHEN FND_API.G_EXC_ERROR THEN
1578
1579 x_return_status := FND_API.G_RET_STS_ERROR;
1580 x_msg_count := 1;
1581 x_msg_data := FND_MSG_PUB.GET(
1582 p_encoded => FND_API.g_false,
1583 P_MSG_INDEX => FND_MSG_PUB.Count_msg
1584 );
1585
1586 WHEN OTHERS THEN
1587
1588 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1589 iby_utility_pvt.handleException(SQLERRM,SQLCODE);
1590 FND_MSG_PUB.Count_And_Get
1591 ( p_count => x_msg_count,
1592 p_data => x_msg_data
1593 );
1594
1595 END OraInstrInq;
1596
1597
1598 -- Start of comments
1599 -- API name : SecureCardInfo
1600 -- Type : Private
1601 -- Function : Secures other sensitive card data and returns the
1602 -- respective segment_IDs.
1603 -- Parameters :
1604 -- IN : p_cardExpiryDate IN DATE Optional
1605 -- p_cardHolderName IN VARCHAR2 Optional
1606 --
1607 PROCEDURE SecureCardInfo (p_cardExpiryDate IN DATE,
1608 p_expSegmentId IN NUMBER,
1609 p_cardHolderName IN VARCHAR2,
1610 p_chnameSegmentId IN NUMBER,
1611 p_chnameMaskSetting IN VARCHAR2,
1612 p_chnameUnmaskLength IN NUMBER,
1613 x_return_status OUT NOCOPY VARCHAR2,
1614 x_msg_count OUT NOCOPY NUMBER,
1615 x_msg_data OUT NOCOPY VARCHAR2,
1616 x_resp_rec OUT NOCOPY SecureCardInfoResp_rec_type
1617 ) IS
1618
1619
1620 --The following 3 variables are meant for output of
1621 --get_baseurl procedure.
1622 l_status_url VARCHAR2(2000);
1623 l_msg_count_url NUMBER := 0;
1624 l_msg_data_url VARCHAR2(2000);
1625
1626 l_api_name CONSTANT VARCHAR2(30) := 'SecureCardInfo';
1627 l_oapf_action CONSTANT VARCHAR2(30) := 'secureCardInfo';
1628 l_api_version CONSTANT NUMBER := 1.0;
1629
1630 l_url VARCHAR2(30000) ;
1631 l_get_baseurl VARCHAR2(2000);
1632
1633 l_db_nls VARCHAR2(80) := NULL;
1634 l_ecapp_nls VARCHAR2(80) := NULL;
1635
1636 l_sec_cred NUMBER;
1637
1638 l_pos NUMBER := 0;
1639 l_post_body VARCHAR2(30000);
1640 l_html VARCHAR2(32767) ;
1641 l_names IBY_NETUTILS_PVT.v240_tbl_type;
1642 l_values IBY_NETUTILS_PVT.v240_tbl_type;
1643
1644
1645 --The following 3 variables are meant for output of
1646 --unpack_results_url procedure.
1647 l_status NUMBER := 0;
1648 l_errcode NUMBER := 0;
1649 l_errmessage VARCHAR2(2000) := 'Success';
1650
1651 BEGIN
1652
1653 iby_debug_pub.add(debug_msg => 'Enter',
1654 debug_level => FND_LOG.LEVEL_PROCEDURE,
1655 module => G_DEBUG_MODULE || '.SecureCardInfo');
1656
1657 -- test_debug('SecureCardInfo=> Enter');
1658 -- Initialize API return status to success
1659 x_return_status := FND_API.G_RET_STS_SUCCESS;
1660
1661 -- START OF BODY OF API
1662
1663 IF (p_cardExpiryDate IS NULL
1664 AND p_cardHolderName IS NULL) THEN
1665 RETURN;
1666 END IF;
1667
1668 IBY_NETUTILS_PVT.get_baseurl(l_get_baseurl);
1669 --dbms_output.put_line('l_get_baseurl= ' || l_get_baseurl);
1670 --test_debug('SecureCardInfo l_get_baseurl= '|| l_get_baseurl);
1671 -- dbms_output.put_line('l_status_url= ' || l_status_url);
1672 -- dbms_output.put_line('l_msg_count_url= ' || l_msg_count_url);
1673 -- dbms_output.put_line('l_msg_data_url= ' || l_msg_data_url);
1674
1675 -- Construct the full URL to send to the ECServlet.
1676 l_url := l_get_baseurl;
1677
1678 l_db_nls := IBY_NETUTILS_PVT.get_local_nls();
1679 l_ecapp_nls := NULL; -- not passed in this api??
1680
1681 --MANDATORY INPUT PARAMETERS
1682 IBY_NETUTILS_PVT.check_mandatory('OapfAction', l_oapf_action, l_url, l_db_nls, l_ecapp_nls);
1683
1684 --OPTIONAL INPUT PARAMETERS
1685 IBY_NETUTILS_PVT.check_optional('OapfExpDate', to_char(p_cardExpiryDate,'YYYY-MM-DD'), l_url, l_db_nls, l_ecapp_nls);
1686 IBY_NETUTILS_PVT.check_optional('OapfExpSegmentId', p_expSegmentId, l_url, l_db_nls, l_ecapp_nls);
1687 IBY_NETUTILS_PVT.check_optional('OapfChname', p_cardHolderName, l_url, l_db_nls, l_ecapp_nls);
1688 IBY_NETUTILS_PVT.check_optional('OapfChnameSegmentId', p_chnameSegmentId, l_url, l_db_nls, l_ecapp_nls);
1689 IBY_NETUTILS_PVT.check_optional('OapfChnameMaskSetting', p_chnameMaskSetting, l_url, l_db_nls, l_ecapp_nls);
1690 IBY_NETUTILS_PVT.check_optional('OapfChnameUnmaskLen', p_chnameUnmaskLength, l_url, l_db_nls, l_ecapp_nls);
1691
1692 -- set the security token
1693 iby_security_pkg.store_credential(l_url,l_sec_cred);
1694 iby_netutils_pvt.check_mandatory('OapfSecurityToken', TO_CHAR(l_sec_cred),
1695 l_url, l_db_nls, l_ecapp_nls);
1696
1697 --test_debug('SecureCardInfo=> full url: '|| l_url);
1698 -- iby_debug_pub.add(debug_msg => 'SecureCardInfo=> full url: '|| l_url,
1699 -- debug_level => FND_LOG.LEVEL_PROCEDURE,
1700 -- module => G_DEBUG_MODULE || '.SecureCardInfo');
1701 l_pos := INSTR(l_url,'?');
1702 l_post_body := SUBSTR(l_url,l_pos+1,length(l_url));
1703 l_post_body := RTRIM(l_post_body,'&');
1704 l_url := SUBSTR(l_url,1,l_pos-1);
1705 -- test_debug('SecureCardInfo=> url after stripping: '|| l_url);
1706 -- test_debug('SecureCardInfo=> post body: '|| l_post_body);
1707
1708
1709 IBY_NETUTILS_PVT.POST_REQUEST(l_url,l_post_body,l_html);
1710
1711 -- Unpack the results
1712 IBY_NETUTILS_PVT.UNPACK_RESULTS_URL(l_html,l_names,l_values, l_status, l_errcode, l_errmessage);
1713
1714
1715 --Raising Exception to handle errors in unpacking resulting html file.
1716 IF (l_status = -1) THEN
1717 --test_debug('unpack error !!');
1718 iby_debug_pub.add(debug_msg => 'Unpack status error; HTML resp. invalid!',
1719 debug_level => FND_LOG.LEVEL_UNEXPECTED,
1720 module => G_DEBUG_MODULE || '.SecureCardInfo');
1721 FND_MESSAGE.SET_NAME('IBY', 'IBY_204403_HTML_UNPACK_ERROR');
1722 FND_MSG_PUB.Add;
1723 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1724 END IF;
1725
1726 --Raising Exception to handle Servlet related errors.
1727 IF (l_names.COUNT = 0) THEN
1728 --test_debug('response count is 0 !!');
1729 iby_debug_pub.add(debug_msg => 'HTML response names count=0',
1730 debug_level => FND_LOG.LEVEL_UNEXPECTED,
1731 module => G_DEBUG_MODULE || '.SecureCardInfo');
1732 FND_MESSAGE.SET_NAME('IBY', 'IBY_204402_JSERVLET_ERROR');
1733 FND_MSG_PUB.Add;
1734 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1735 END IF;
1736
1737 /* Retrieve name-value pairs stored in l_names and l_values, and assign
1738 them to the output record: x_reqresp_rec.
1739 */
1740 --test_debug('Setting fields from unpacked response');
1741 iby_debug_pub.add(debug_msg => 'Setting fields from unpacked response',
1742 debug_level => FND_LOG.LEVEL_STATEMENT,
1743 module => G_DEBUG_MODULE || '.SecureCardInfo');
1744
1745
1746 FOR i IN 1..l_names.COUNT LOOP
1747 --Payment Server Related Generic Response
1748 IF l_names(i) = 'OapfStatus' THEN
1749 x_resp_rec.Response.Status := TO_NUMBER(l_values(i));
1750 iby_debug_pub.add(debug_msg => 'Response status=' || x_resp_rec.Response.Status,
1751 debug_level => FND_LOG.LEVEL_STATEMENT,
1752 module => G_DEBUG_MODULE || '.SecureCardInfo');
1753 --test_debug('OapfStatus: '||x_resp_rec.Response.Status);
1754 ELSIF l_names(i) = 'OapfCode' THEN
1755 x_resp_rec.Response.ErrCode := l_values(i);
1756 iby_debug_pub.add(debug_msg => 'Response code=' || x_resp_rec.Response.ErrCode,
1757 debug_level => FND_LOG.LEVEL_STATEMENT,
1758 module => G_DEBUG_MODULE || '.SecureCardInfo');
1759 --test_debug('OapfCode: '||x_resp_rec.Response.ErrCode);
1760 ELSIF l_names(i) = 'OapfCause' THEN
1761 x_resp_rec.Response.ErrMessage := l_values(i);
1762 iby_debug_pub.add(debug_msg => 'Response message=' || x_resp_rec.Response.ErrMessage,
1763 debug_level => FND_LOG.LEVEL_STATEMENT,
1764 module => G_DEBUG_MODULE || '.SecureCardInfo');
1765 --test_debug('OapfCause: '||x_resp_rec.Response.ErrMessage);
1766 ELSIF l_names(i) = 'OapfNlsLang' THEN
1767 x_resp_rec.Response.NLS_LANG := l_values(i);
1768
1769 --SecureCardInfo Response Related Response
1770 ELSIF l_names(i) = 'OapfExpSegmentId' THEN
1771 x_resp_rec.ExpiryDateSegmentId := TO_NUMBER(l_values(i));
1772 --test_debug('OapfExpSegmentId: '||x_resp_rec.ExpiryDateSegmentId);
1773 ELSIF l_names(i) = 'OapfMaskedChname' THEN
1774 x_resp_rec.MaskedChname := l_values(i);
1775 ELSIF l_names(i) = 'OapfChnameSegmentId' THEN
1776 x_resp_rec.ChnameSegmentId := TO_NUMBER(l_values(i));
1777 --test_debug('OapfChnameSegmentId: '||x_resp_rec.ChnameSegmentId);
1778 ELSIF l_names(i) = 'OapfChnameMaskSetting' THEN
1779 x_resp_rec.ChnameMaskSetting := l_values(i);
1780 ELSIF l_names(i) = 'OapfChnameUnmaskLen' THEN
1781 x_resp_rec.ChnameUnmaskLength := TO_NUMBER(l_values(i));
1782 END IF;
1783
1784 END LOOP;
1785
1786 -- Use for Debugging
1787 --dbms_output.put_line('after successfully mapping results');
1788
1789 -- Standard check of p_commit.
1790 /*
1791 IF FND_API.To_Boolean( p_commit ) THEN
1792 COMMIT WORK;
1793 END IF;
1794 */
1795
1796 -- Standard call to get message count and if count is 1, get message info.
1797 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1798 p_data => x_msg_data
1799 );
1800
1801 iby_debug_pub.add(debug_msg => 'x_return_status=' || x_return_status,
1802 debug_level => FND_LOG.LEVEL_STATEMENT,
1803 module => G_DEBUG_MODULE || '.SecureCardInfo');
1804 iby_debug_pub.add(debug_msg => 'req response status=' || x_resp_rec.Response.Status,
1805 debug_level => FND_LOG.LEVEL_STATEMENT,
1806 module => G_DEBUG_MODULE || '.SecureCardInfo');
1807
1808 iby_debug_pub.add(debug_msg => 'Exit',
1809 debug_level => FND_LOG.LEVEL_PROCEDURE,
1810 module => G_DEBUG_MODULE || '.SecureCardInfo');
1811 --test_debug('Exit*******');
1812
1813 EXCEPTION
1814
1815 WHEN FND_API.G_EXC_ERROR THEN
1816
1817 iby_debug_pub.add(debug_msg => 'In G_EXC_ERROR Exception',
1818 debug_level => FND_LOG.LEVEL_ERROR,
1819 module => G_DEBUG_MODULE || '.SecureCardInfo');
1820 --ROLLBACK TO OraPmtReq_PUB;
1821 x_return_status := FND_API.G_RET_STS_ERROR ;
1822 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1823 p_data => x_msg_data
1824 );
1825 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1826
1827 iby_debug_pub.add(debug_msg => 'In G_EXC_UNEXPECTED_ERROR Exception',
1828 debug_level => FND_LOG.LEVEL_UNEXPECTED,
1829 module => G_DEBUG_MODULE || '.SecureCardInfo');
1830 --ROLLBACK TO OraPmtReq_PUB;
1831 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1832 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1833 p_data => x_msg_data
1834 );
1835 WHEN OTHERS THEN
1836
1837 iby_debug_pub.add(debug_msg => 'In OTHERS Exception',
1838 debug_level => FND_LOG.LEVEL_UNEXPECTED,
1839 module => G_DEBUG_MODULE || '.SecureCardInfo');
1840 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1841 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1842 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_api_name);
1843 END IF;
1844
1845 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1846 p_data => x_msg_data
1847 );
1848
1849 iby_debug_pub.add(debug_msg => 'x_return_status=' || x_return_status,
1850 debug_level => FND_LOG.LEVEL_UNEXPECTED,
1851 module => G_DEBUG_MODULE || '.SecureCardInfo');
1852 iby_debug_pub.add(debug_msg => 'Exit Exception',
1853 debug_level => FND_LOG.LEVEL_UNEXPECTED,
1854 module => G_DEBUG_MODULE || '.SecureCardInfo');
1855
1856 END SecureCardInfo;
1857
1858 PROCEDURE Get_Expiration_Status
1859 ( p_instrid IN NUMBER,
1860 p_inputDate IN DATE,
1861 x_return_status OUT NOCOPY VARCHAR2,
1862 x_msg_count OUT NOCOPY NUMBER,
1863 x_msg_data OUT NOCOPY VARCHAR2,
1864 x_resp_rec OUT NOCOPY GetExpStatusResp_rec_type
1865 )
1866 IS
1867 --The following 3 variables are meant for output of
1868 --get_baseurl procedure.
1869 l_status_url VARCHAR2(2000);
1870 l_msg_count_url NUMBER := 0;
1871 l_msg_data_url VARCHAR2(2000);
1872
1873 l_api_name CONSTANT VARCHAR2(30) := 'Get_Expiration_Status';
1874 l_oapf_action CONSTANT VARCHAR2(30) := 'checkCCExpiry';
1875 l_api_version CONSTANT NUMBER := 1.0;
1876
1877 l_url VARCHAR2(30000) ;
1878 l_get_baseurl VARCHAR2(2000);
1879
1880 l_db_nls VARCHAR2(80) := NULL;
1881 l_ecapp_nls VARCHAR2(80) := NULL;
1882
1883 l_sec_cred NUMBER;
1884
1885 l_pos NUMBER := 0;
1886 l_post_body VARCHAR2(30000);
1887 l_html VARCHAR2(32767) ;
1888 l_names IBY_NETUTILS_PVT.v240_tbl_type;
1889 l_values IBY_NETUTILS_PVT.v240_tbl_type;
1890
1891
1892 --The following 3 variables are meant for output of
1893 --unpack_results_url procedure.
1894 l_status NUMBER := 0;
1895 l_errcode NUMBER := 0;
1896 l_errmessage VARCHAR2(2000) := 'Success';
1897 BEGIN
1898 iby_debug_pub.add(debug_msg => 'Enter',
1899 debug_level => FND_LOG.LEVEL_PROCEDURE,
1900 module => G_DEBUG_MODULE || '.Get_Expiration_Status');
1901
1902 -- test_debug('SecureCardInfo=> Enter');
1903 -- Initialize API return status to success
1904 x_return_status := FND_API.G_RET_STS_SUCCESS;
1905
1906 -- START OF BODY OF API
1907
1908
1909 IBY_NETUTILS_PVT.get_baseurl(l_get_baseurl);
1910 --dbms_output.put_line('l_get_baseurl= ' || l_get_baseurl);
1911 --test_debug('SecureCardInfo l_get_baseurl= '|| l_get_baseurl);
1912 -- dbms_output.put_line('l_status_url= ' || l_status_url);
1913 -- dbms_output.put_line('l_msg_count_url= ' || l_msg_count_url);
1914 -- dbms_output.put_line('l_msg_data_url= ' || l_msg_data_url);
1915
1916 -- Construct the full URL to send to the ECServlet.
1917 l_url := l_get_baseurl;
1918
1919 l_db_nls := IBY_NETUTILS_PVT.get_local_nls();
1920 l_ecapp_nls := NULL; -- not passed in this api??
1921
1922 --MANDATORY INPUT PARAMETERS
1923 IBY_NETUTILS_PVT.check_mandatory('OapfAction', l_oapf_action, l_url, l_db_nls, l_ecapp_nls);
1924
1925 IBY_NETUTILS_PVT.check_mandatory('OapfInstrid', p_instrid, l_url, l_db_nls, l_ecapp_nls);
1926 IBY_NETUTILS_PVT.check_mandatory('OapfInputDate', to_char(p_inputDate,'YYYY-MM-DD'), l_url, l_db_nls, l_ecapp_nls);
1927
1928 -- set the security token
1929 iby_security_pkg.store_credential(l_url,l_sec_cred);
1930 iby_netutils_pvt.check_mandatory('OapfSecurityToken', TO_CHAR(l_sec_cred),
1931 l_url, l_db_nls, l_ecapp_nls);
1932
1933 --test_debug('Get_Expiration_Status=> full url: '|| l_url);
1934 iby_debug_pub.add(debug_msg => 'Get_Expiration_Status=> full url: '|| l_url,
1935 debug_level => FND_LOG.LEVEL_PROCEDURE,
1936 module => G_DEBUG_MODULE || '.Get_Expiration_Status');
1937 l_pos := INSTR(l_url,'?');
1938 l_post_body := SUBSTR(l_url,l_pos+1,length(l_url));
1939 l_post_body := RTRIM(l_post_body,'&');
1940 l_url := SUBSTR(l_url,1,l_pos-1);
1941 -- test_debug('SecureCardInfo=> url after stripping: '|| l_url);
1942 -- test_debug('SecureCardInfo=> post body: '|| l_post_body);
1943
1944
1945 IBY_NETUTILS_PVT.POST_REQUEST(l_url,l_post_body,l_html);
1946
1947 -- Unpack the results
1948 IBY_NETUTILS_PVT.UNPACK_RESULTS_URL(l_html,l_names,l_values, l_status, l_errcode, l_errmessage);
1949
1950
1951 --Raising Exception to handle errors in unpacking resulting html file.
1952 IF (l_status = -1) THEN
1953 --test_debug('unpack error !!');
1954 iby_debug_pub.add(debug_msg => 'Unpack status error; HTML resp. invalid!',
1955 debug_level => FND_LOG.LEVEL_UNEXPECTED,
1956 module => G_DEBUG_MODULE || '.Get_Expiration_Status');
1957 FND_MESSAGE.SET_NAME('IBY', 'IBY_204403_HTML_UNPACK_ERROR');
1958 FND_MSG_PUB.Add;
1959 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1960 END IF;
1961
1962 --Raising Exception to handle Servlet related errors.
1963 IF (l_names.COUNT = 0) THEN
1964 --test_debug('response count is 0 !!');
1965 iby_debug_pub.add(debug_msg => 'HTML response names count=0',
1966 debug_level => FND_LOG.LEVEL_UNEXPECTED,
1967 module => G_DEBUG_MODULE || '.Get_Expiration_Status');
1968 FND_MESSAGE.SET_NAME('IBY', 'IBY_204402_JSERVLET_ERROR');
1969 FND_MSG_PUB.Add;
1970 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1971 END IF;
1972
1973 /* Retrieve name-value pairs stored in l_names and l_values, and assign
1974 them to the output record: x_reqresp_rec.
1975 */
1976 --test_debug('Setting fields from unpacked response');
1977 iby_debug_pub.add(debug_msg => 'Setting fields from unpacked response',
1978 debug_level => FND_LOG.LEVEL_STATEMENT,
1979 module => G_DEBUG_MODULE || '.Get_Expiration_Status');
1980
1981
1982 FOR i IN 1..l_names.COUNT LOOP
1983 --Payment Server Related Generic Response
1984 IF l_names(i) = 'OapfStatus' THEN
1985 x_resp_rec.Response.Status := TO_NUMBER(l_values(i));
1986 iby_debug_pub.add(debug_msg => 'Response status=' || x_resp_rec.Response.Status,
1987 debug_level => FND_LOG.LEVEL_STATEMENT,
1988 module => G_DEBUG_MODULE || '.Get_Expiration_Status');
1989 --test_debug('OapfStatus: '||x_resp_rec.Response.Status);
1990 ELSIF l_names(i) = 'OapfCode' THEN
1991 x_resp_rec.Response.ErrCode := l_values(i);
1992 iby_debug_pub.add(debug_msg => 'Response code=' || x_resp_rec.Response.ErrCode,
1993 debug_level => FND_LOG.LEVEL_STATEMENT,
1994 module => G_DEBUG_MODULE || '.Get_Expiration_Status');
1995 --test_debug('OapfCode: '||x_resp_rec.Response.ErrCode);
1996 ELSIF l_names(i) = 'OapfCause' THEN
1997 x_resp_rec.Response.ErrMessage := l_values(i);
1998 iby_debug_pub.add(debug_msg => 'Response message=' || x_resp_rec.Response.ErrMessage,
1999 debug_level => FND_LOG.LEVEL_STATEMENT,
2000 module => G_DEBUG_MODULE || '.Get_Expiration_Status');
2001 --test_debug('OapfCause: '||x_resp_rec.Response.ErrMessage);
2002 ELSIF l_names(i) = 'OapfNlsLang' THEN
2003 x_resp_rec.Response.NLS_LANG := l_values(i);
2004
2005 --GetExpStatusResp_rec_type Response Related Response
2006 ELSIF l_names(i) = 'OapfExpired' THEN
2007 x_resp_rec.Expired := l_values(i);
2008 --test_debug('OapfExpSegmentId: '||x_resp_rec.ExpiryDateSegmentId);
2009 END IF;
2010
2011 END LOOP;
2012
2013 -- Use for Debugging
2014 --dbms_output.put_line('after successfully mapping results');
2015
2016 -- Standard check of p_commit.
2017 /*
2018 IF FND_API.To_Boolean( p_commit ) THEN
2019 COMMIT WORK;
2020 END IF;
2021 */
2022
2023 -- Standard call to get message count and if count is 1, get message info.
2024 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
2025 p_data => x_msg_data
2026 );
2027
2028 iby_debug_pub.add(debug_msg => 'x_return_status=' || x_return_status,
2029 debug_level => FND_LOG.LEVEL_STATEMENT,
2030 module => G_DEBUG_MODULE || '.Get_Expiration_Status');
2031 iby_debug_pub.add(debug_msg => 'req response status=' || x_resp_rec.Response.Status,
2032 debug_level => FND_LOG.LEVEL_STATEMENT,
2033 module => G_DEBUG_MODULE || '.Get_Expiration_Status');
2034
2035 iby_debug_pub.add(debug_msg => 'Exit',
2036 debug_level => FND_LOG.LEVEL_PROCEDURE,
2037 module => G_DEBUG_MODULE || '.Get_Expiration_Status');
2038 --test_debug('Exit*******');
2039
2040 EXCEPTION
2041
2042 WHEN FND_API.G_EXC_ERROR THEN
2043
2044 iby_debug_pub.add(debug_msg => 'In G_EXC_ERROR Exception',
2045 debug_level => FND_LOG.LEVEL_ERROR,
2046 module => G_DEBUG_MODULE || '.Get_Expiration_Status');
2047 --ROLLBACK TO OraPmtReq_PUB;
2048 x_return_status := FND_API.G_RET_STS_ERROR ;
2049 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
2050 p_data => x_msg_data
2051 );
2052 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2053
2054 iby_debug_pub.add(debug_msg => 'In G_EXC_UNEXPECTED_ERROR Exception',
2055 debug_level => FND_LOG.LEVEL_UNEXPECTED,
2056 module => G_DEBUG_MODULE || '.Get_Expiration_Status');
2057 --ROLLBACK TO OraPmtReq_PUB;
2058 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2059 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
2060 p_data => x_msg_data
2061 );
2062 WHEN OTHERS THEN
2063
2064 iby_debug_pub.add(debug_msg => 'In OTHERS Exception',
2065 debug_level => FND_LOG.LEVEL_UNEXPECTED,
2066 module => G_DEBUG_MODULE || '.Get_Expiration_Status');
2067 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2068 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2069 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_api_name);
2070 END IF;
2071
2072 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
2073 p_data => x_msg_data
2074 );
2075
2076 iby_debug_pub.add(debug_msg => 'x_return_status=' || x_return_status,
2077 debug_level => FND_LOG.LEVEL_UNEXPECTED,
2078 module => G_DEBUG_MODULE || '.Get_Expiration_Status');
2079 iby_debug_pub.add(debug_msg => 'Exit Exception',
2080 debug_level => FND_LOG.LEVEL_UNEXPECTED,
2081 module => G_DEBUG_MODULE || '.Get_Expiration_Status');
2082 END Get_Expiration_Status;
2083
2084
2085
2086 END IBY_INSTRREG_PUB;