[Home] [Help]
PACKAGE BODY: APPS.IBY_INSTRREG_PUB
Source
1 PACKAGE BODY IBY_INSTRREG_PUB AS
2 /*$Header: ibypregb.pls 120.14.12010000.7 2008/11/27 13:20:12 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
217 -------------------------------------------------------------------------------
218 ---*** APIS START BELOW ---***
219 -------------------------------------------------------------------------------
220 -- 1. OraInstrAdd
221 -- Start of comments
222 -- API name : OraInstrAdd
223 -- Type : Public
224 -- Pre-reqs : None
225 -- Function : Adds new Payment Instruments to iPayment.
226 -- Parameters :
227 -- IN : p_api_version IN NUMBER Required
228 -- p_init_msg_list IN VARCHAR2 Optional
229 -- p_commit IN VARCHAR2 Optional
230 -- p_validation_level IN NUMBER Optional
231 -- p_payer_id IN VARCHAR2 Required
232 -- p_pmtInstrRec IN PmtInstr_rec_type Required
233 --
234 -- OUT : x_return_status OUT VARCHAR2
235 -- x_msg_count OUT VARCHAR2
236 -- x_msg_data OUT NUMBER
237 -- x_instr_id OUT NUMBER
238 -- Version :
239 -- Current version 1.0
240 -- Previous version 1.0
241 -- Initial version 1.0
242 -- End of comments
243 -------------------------------------------------------------------------------
244
245 PROCEDURE OraInstrAdd
246 (
247 p_api_version IN NUMBER,
248 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
249 p_commit IN VARCHAR2 := FND_API.G_TRUE,
250 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
251 p_pmtInstrRec IN PmtInstr_rec_type,
252 x_return_status OUT NOCOPY VARCHAR2,
253 x_msg_count OUT NOCOPY NUMBER,
254 x_msg_data OUT NOCOPY VARCHAR2,
255 x_instr_id OUT NOCOPY NUMBER,
256 x_result OUT NOCOPY IBY_FNDCPT_COMMON_PUB.Result_rec_type
257 )
258 IS
259 l_api_name CONSTANT VARCHAR2(30) := 'OraInstrAdd';
260 l_oapf_action CONSTANT VARCHAR2(30) := 'oraInstrAdd';
261 l_api_version CONSTANT NUMBER := 1.0;
262
263 l_url VARCHAR2(30000) ;
264 l_get_baseurl VARCHAR2(2000);
265
266 l_pos NUMBER := 0;
267 l_post_body VARCHAR2(30000);
268 l_html VARCHAR2(32767) ;
269 l_names IBY_NETUTILS_PVT.v240_tbl_type;
270 l_values IBY_NETUTILS_PVT.v240_tbl_type;
271
272 l_status NUMBER := 0;
273 l_errcode NUMBER := 0;
274 l_index NUMBER := 1;
275 l_errmessage VARCHAR2(2000) := 'Success';
276
277 -- for NLS bug fix #1692300 - 4/3/2001 jleybovi
278 --
279 l_db_nls VARCHAR2(80) := NULL;
280 l_ecapp_nls VARCHAR2(80) := NULL;
281
282 l_instrument_type VARCHAR2(80) := C_INSTRTYPE_UNREG;
283 l_sec_cred NUMBER;
284
285 ERROR_FROM_SUBPROC Exception;
286
287 -- This will catch all the exceptions from the procedure which is
288 -- subsequently called.This will trap all exceptions that have
289 -- SQLCODE = -20000 and name it as 'ERROR_FROM_SUBPROC'.
290 PRAGMA EXCEPTION_INIT( ERROR_FROM_SUBPROC, -20000 );
291
292 l_dbg_mod VARCHAR2(100) := G_DEBUG_MODULE || '.' || l_api_name;
293 BEGIN
294 iby_debug_pub.add('Enter',iby_debug_pub.G_LEVEL_PROCEDURE,l_dbg_mod);
295
296 -- Standard call to check for call compatibility.
297 IF NOT FND_API.Compatible_API_Call ( l_api_version,
298 p_api_version,
299 l_api_name,
300 G_PKG_NAME )
301 THEN
302 FND_MESSAGE.SET_NAME('IBY', 'IBY_204400_API_VER_MISMATCH');
303 FND_MSG_PUB.Add;
304 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
305 END IF;
306
307 -- Initialize message list if p_init_msg_list is set to TRUE.
308 IF FND_API.to_Boolean( p_init_msg_list ) THEN
309 FND_MSG_PUB.initialize;
310 END IF;
311
312 -- Verifying if validation level is FULL, which is expected for PUBLIC APIs.
313 IF (p_validation_level <> g_validation_level) THEN
314 FND_MESSAGE.SET_NAME('IBY', 'IBY_204401_VAL_LEVEL_ERROR');
315 FND_MSG_PUB.Add;
316 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
317 END IF;
318
319 -- Initialize API return status to success
320 x_return_status := FND_API.G_RET_STS_SUCCESS;
321
322 -- START OF BODY OF API
323
324 -- Checks whether the instrument type passed is valid or not
325 l_instrument_type := p_pmtInstrRec.InstrumentType;
326 IF( ( l_instrument_type <> C_INSTRTYPE_CREDITCARD ) AND
327 ( l_instrument_type <> C_INSTRTYPE_PURCHASECARD ) AND
328 ( l_instrument_type <> C_INSTRTYPE_BANKACCT ) ) THEN
329 FND_MESSAGE.SET_NAME('IBY', 'IBY_20487');
330 FND_MSG_PUB.Add;
331 RAISE FND_API.G_EXC_ERROR;
332 -- Returns message 'Invalid instrument type passed'.
333 END IF;
334
335 -- Check whether Instrid is passed. It should not be passed for 'Add'.
336 IF( ( p_pmtInstrRec.CreditCardInstr.Instr_Id is not NULL ) OR
337 ( p_pmtInstrRec.PurchaseCardInstr.Instr_Id is not NULL ) OR
338 ( p_pmtInstrRec.BankAcctInstr.Instr_Id is not NULL ) ) THEN
339 FND_MESSAGE.SET_NAME('IBY', 'IBY_20488');
340 FND_MSG_PUB.Add;
341 RAISE FND_API.G_EXC_ERROR;
342 --Returns message 'INSTR_ID should not be passed'
343 END IF;
344
345 IF( l_instrument_type = C_INSTRTYPE_PURCHASECARD ) THEN
346 -- Purchase Subtype is mandatory.
347 IF( p_pmtInstrRec.PurchaseCardInstr.PC_SubType is NULL ) THEN
348 FND_MESSAGE.SET_NAME('IBY', 'IBY_20483');
349 FND_MSG_PUB.Add;
350 RAISE FND_API.G_EXC_ERROR;
351 -- Returns message 'Mandatory field(s) missing'
352 END IF;
353
354 END IF;
355
356 -- Finally call the procedures that will add the instrument.
357 --IBY_NETUTILS_PVT.get_baseurl(l_get_baseurl);
358
359 IBY_NETUTILS_PVT.get_baseurl(l_get_baseurl);
360
361 iby_debug_pub.add('GetBaseUrl :' || l_get_baseurl,
362 iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
363
364 -- Construct the full URL to send to the ECServlet.
365 l_url := l_get_baseurl;
366
367 l_db_nls := IBY_NETUTILS_PVT.get_local_nls();
368 l_ecapp_nls := NULL; -- not passed in this api??
369
370 IBY_NETUTILS_PVT.check_mandatory('OapfAction', l_oapf_action, l_url, l_db_nls, l_ecapp_nls);
371 IF ( l_instrument_type <> C_INSTRTYPE_UNREG) THEN
372 IBY_NETUTILS_PVT.check_mandatory('OapfPmtInstrType', l_instrument_type, l_url, l_db_nls, l_ecapp_nls);
373 END IF;
374
375 IF ( l_instrument_type = C_INSTRTYPE_BANKACCT) THEN
376 IBY_NETUTILS_PVT.check_optional('OapfInstrOwnerType', p_pmtInstrRec.BankAcctInstr.BankAcct_HolderType, l_url, l_db_nls, l_ecapp_nls);
377 IBY_NETUTILS_PVT.check_optional('OapfInstrFIName', p_pmtInstrRec.BankAcctInstr.FIName, l_url, l_db_nls, l_ecapp_nls);
378 IBY_NETUTILS_PVT.check_mandatory('OapfInstrBankId', p_pmtInstrRec.BankAcctInstr.Branch_ID, l_url, l_db_nls, l_ecapp_nls);
379 IBY_NETUTILS_PVT.check_optional('OapfInstrBankSwiftCode', p_pmtInstrRec.BankAcctInstr.Bank_SwiftCode, l_url, l_db_nls, l_ecapp_nls);
380 IBY_NETUTILS_PVT.check_optional('OapfInstrBranchId', p_pmtInstrRec.BankAcctInstr.Bank_ID, l_url, l_db_nls, l_ecapp_nls);
381 IBY_NETUTILS_PVT.check_optional('OapfInstrAcctType', p_pmtInstrRec.BankAcctInstr.BankAcct_Type, l_url, l_db_nls, l_ecapp_nls);
382 IBY_NETUTILS_PVT.check_mandatory('OapfInstrNum', p_pmtInstrRec.BankAcctInstr.BankAcct_Num, l_url, l_db_nls, l_ecapp_nls);
383 IBY_NETUTILS_PVT.check_optional('OapfInstrCheckDigits', p_pmtInstrRec.BankAcctInstr.BankAcct_Checkdigits, l_url, l_db_nls, l_ecapp_nls);
384 IBY_NETUTILS_PVT.check_mandatory('OapfInstrHolderName', p_pmtInstrRec.BankAcctInstr.BankAcct_HolderName, l_url, l_db_nls, l_ecapp_nls);
385 IBY_NETUTILS_PVT.check_optional('OapfInstrBuf', p_pmtInstrRec.BankAcctInstr.Bank_Desc, l_url, l_db_nls, l_ecapp_nls);
386 IBY_NETUTILS_PVT.check_optional('OapfInstrCurrency', p_pmtInstrRec.BankAcctInstr.BankAcct_Currency, l_url, l_db_nls, l_ecapp_nls);
387 IBY_NETUTILS_PVT.check_optional('OapfInstrOwnerAddrId', p_pmtInstrRec.BankAcctInstr.Acct_HolderAddrId, l_url, l_db_nls, l_ecapp_nls);
388 IF ( p_pmtInstrRec.BankAcctInstr.Bank_AddrId is NOT null ) THEN
389 IBY_NETUTILS_PVT.check_optional('OapfInstrAddrId', p_pmtInstrRec.BankAcctInstr.Bank_AddrId, l_url, l_db_nls, l_ecapp_nls);
390 ELSIF ( p_pmtInstrRec.BankAcctInstr.Bank_Address1 is NOT NULL) THEN
391 IBY_NETUTILS_PVT.check_mandatory('OapfInstrAddrLine1', p_pmtInstrRec.BankAcctInstr.Bank_Address1, l_url, l_db_nls, l_ecapp_nls);
392 IBY_NETUTILS_PVT.check_optional('OapfInstrAddrLine2', p_pmtInstrRec.BankAcctInstr.Bank_Address2, l_url, l_db_nls, l_ecapp_nls);
393 IBY_NETUTILS_PVT.check_optional('OapfInstrAddrLine3', p_pmtInstrRec.BankAcctInstr.Bank_Address3, l_url, l_db_nls, l_ecapp_nls);
394 IBY_NETUTILS_PVT.check_optional('OapfInstrCity', p_pmtInstrRec.BankAcctInstr.Bank_City, l_url, l_db_nls, l_ecapp_nls);
395 IBY_NETUTILS_PVT.check_optional('OapfInstrCounty', p_pmtInstrRec.BankAcctInstr.Bank_County, l_url, l_db_nls, l_ecapp_nls);
396 IBY_NETUTILS_PVT.check_optional('OapfInstrState', p_pmtInstrRec.BankAcctInstr.Bank_State, l_url, l_db_nls, l_ecapp_nls);
397 IBY_NETUTILS_PVT.check_mandatory('OapfInstrCountry', p_pmtInstrRec.BankAcctInstr.Bank_Country, l_url, l_db_nls, l_ecapp_nls);
398 IBY_NETUTILS_PVT.check_optional('OapfInstrPostalCode', p_pmtInstrRec.BankAcctInstr.Bank_PostalCode, l_url, l_db_nls, l_ecapp_nls);
399 END IF;
400
401 ELSIF( l_instrument_type = C_INSTRTYPE_PURCHASECARD ) THEN
402
403 IBY_NETUTILS_PVT.check_optional('OapfInstrFIName', p_pmtInstrRec.PurchaseCardInstr.FIName, l_url, l_db_nls, l_ecapp_nls);
404 IBY_NETUTILS_PVT.check_mandatory('OapfCCType',p_pmtInstrRec.PurchaseCardInstr.PC_Type, l_url, l_db_nls, l_ecapp_nls);
405 IBY_NETUTILS_PVT.check_mandatory('OapfPmtInstrExp',to_char(p_pmtInstrRec.PurchaseCardInstr.PC_ExpDate,'YYYY-MM-DD'), l_url, l_db_nls, l_ecapp_nls);
406 IBY_NETUTILS_PVT.check_mandatory('OapfInstrNum', p_pmtInstrRec.PurchaseCardInstr.PC_Num, l_url, l_db_nls, l_ecapp_nls);
407 IBY_NETUTILS_PVT.check_mandatory('OapfInstrHolderName', p_pmtInstrRec.PurchaseCardInstr.PC_HolderName , l_url, l_db_nls, l_ecapp_nls);
408 IBY_NETUTILS_PVT.check_optional('OapfInstrOwnerId', p_pmtInstrRec.PurchaseCardInstr.Owner_Id, l_url, l_db_nls, l_ecapp_nls);
409 IBY_NETUTILS_PVT.check_optional('OapfInstrOwnerType', p_pmtInstrRec.PurchaseCardInstr.PC_HolderType, l_url, l_db_nls, l_ecapp_nls);
410 IBY_NETUTILS_PVT.check_mandatory('OapfCardSubType', p_pmtInstrRec.PurchaseCardInstr.PC_Subtype, l_url, l_db_nls, l_ecapp_nls);
411 IBY_NETUTILS_PVT.check_optional('OapfInstrBuf', p_pmtInstrRec.PurchaseCardInstr.PC_Desc, l_url, l_db_nls, l_ecapp_nls);
412 IF ( p_pmtInstrRec.PurchaseCardInstr.Billing_Address1 is NOT NULL) THEN
413 IBY_NETUTILS_PVT.check_mandatory('OapfInstrAddrLine1', p_pmtInstrRec.PurchaseCardInstr.Billing_Address1, l_url, l_db_nls, l_ecapp_nls);
414 IBY_NETUTILS_PVT.check_optional('OapfInstrAddrLine2', p_pmtInstrRec.PurchaseCardInstr.Billing_Address2, l_url, l_db_nls, l_ecapp_nls);
415 IBY_NETUTILS_PVT.check_optional('OapfInstrAddrLine3', p_pmtInstrRec.PurchaseCardInstr.Billing_Address3, l_url, l_db_nls, l_ecapp_nls);
416 IBY_NETUTILS_PVT.check_mandatory('OapfInstrCity', p_pmtInstrRec.PurchaseCardInstr.Billing_City, l_url, l_db_nls, l_ecapp_nls);
417 IBY_NETUTILS_PVT.check_optional('OapfInstrCounty', p_pmtInstrRec.PurchaseCardInstr.Billing_County, l_url, l_db_nls, l_ecapp_nls);
418 IBY_NETUTILS_PVT.check_optional('OapfInstrState', p_pmtInstrRec.PurchaseCardInstr.Billing_State, l_url, l_db_nls, l_ecapp_nls);
419 IBY_NETUTILS_PVT.check_mandatory('OapfInstrCountry', p_pmtInstrRec.PurchaseCardInstr.Billing_Country, l_url, l_db_nls, l_ecapp_nls);
420 IBY_NETUTILS_PVT.check_optional('OapfInstrPostalCode', p_pmtInstrRec.PurchaseCardInstr.Billing_PostalCode, l_url, l_db_nls, l_ecapp_nls);
421 END IF;
422
423 ELSIF( l_instrument_type = C_INSTRTYPE_CREDITCARD ) THEN
424
425 IBY_NETUTILS_PVT.check_optional('OapfInstrFIName', p_pmtInstrRec.CreditCardInstr.FIName, l_url, l_db_nls, l_ecapp_nls);
426 IBY_NETUTILS_PVT.check_optional('OapfCCType', p_pmtInstrRec.CreditCardInstr.CC_Type, l_url, l_db_nls, l_ecapp_nls);
427 IBY_NETUTILS_PVT.check_mandatory('OapfInstrNum', p_pmtInstrRec.CreditCardInstr.CC_Num, l_url, l_db_nls, l_ecapp_nls);
428 IBY_NETUTILS_PVT.check_optional('OapfPmtInstrExp', to_char(p_pmtInstrRec.CreditCardInstr.CC_ExpDate,'YYYY-MM-DD'), l_url, l_db_nls, l_ecapp_nls);
429 IBY_NETUTILS_PVT.check_optional('OapfInstrHolderName', p_pmtInstrRec.CreditCardInstr.CC_HolderName , l_url, l_db_nls, l_ecapp_nls);
430 IBY_NETUTILS_PVT.check_optional('OapfInstrOwnerType', p_pmtInstrRec.CreditCardInstr.CC_HolderType, l_url, l_db_nls, l_ecapp_nls);
431 IBY_NETUTILS_PVT.check_optional('OapfInstrBuf', p_pmtInstrRec.CreditCardInstr.CC_Desc, l_url, l_db_nls, l_ecapp_nls);
432
433 --IBY_NETUTILS_PVT.check_optional('OapfCardOwnerId', p_pmtInstrRec.CreditCardInstr.Owner_Id, l_url, l_db_nls, l_ecapp_nls);
434 IBY_NETUTILS_PVT.check_mandatory('OapfInstrOwnerId', p_pmtInstrRec.CreditCardInstr.Owner_Id, l_url, l_db_nls, l_ecapp_nls);
435 IBY_NETUTILS_PVT.check_optional('OapfSingleUseFlag', p_pmtInstrRec.CreditCardInstr.Single_Use_Flag, l_url, l_db_nls, l_ecapp_nls);
436 IBY_NETUTILS_PVT.check_optional('OapfInfoOnlyFlag', p_pmtInstrRec.CreditCardInstr.Info_Only_Flag, l_url, l_db_nls, l_ecapp_nls);
437 IBY_NETUTILS_PVT.check_optional('OapfCardPurpose', p_pmtInstrRec.CreditCardInstr.Card_Purpose, l_url, l_db_nls, l_ecapp_nls);
438 IBY_NETUTILS_PVT.check_optional('OapfActiveFlag', p_pmtInstrRec.CreditCardInstr.Active_Flag, l_url, l_db_nls, l_ecapp_nls);
439 IBY_NETUTILS_PVT.check_optional('OapfInactiveDate', TO_CHAR(p_pmtInstrRec.CreditCardInstr.Inactive_Date,'YYYY-MM-DD'), l_url, l_db_nls, l_ecapp_nls);
440
441 IF ( p_pmtInstrRec.CreditCardInstr.Billing_Address1 is NOT NULL) THEN
442 IBY_NETUTILS_PVT.check_mandatory('OapfInstrAddrLine1', p_pmtInstrRec.CreditCardInstr.Billing_Address1, l_url, l_db_nls, l_ecapp_nls);
443 IBY_NETUTILS_PVT.check_optional('OapfInstrAddrLine2', p_pmtInstrRec.CreditCardInstr.Billing_Address2, l_url, l_db_nls, l_ecapp_nls);
444 IBY_NETUTILS_PVT.check_optional('OapfInstrAddrLine3', p_pmtInstrRec.CreditCardInstr.Billing_Address3, l_url, l_db_nls, l_ecapp_nls);
445 IBY_NETUTILS_PVT.check_mandatory('OapfInstrCity', p_pmtInstrRec.CreditCardInstr.Billing_City, l_url, l_db_nls, l_ecapp_nls);
446 IBY_NETUTILS_PVT.check_optional('OapfInstrCounty', p_pmtInstrRec.CreditCardInstr.Billing_County, l_url, l_db_nls, l_ecapp_nls);
447 IBY_NETUTILS_PVT.check_optional('OapfInstrState', p_pmtInstrRec.CreditCardInstr.Billing_State, l_url, l_db_nls, l_ecapp_nls);
448 IBY_NETUTILS_PVT.check_mandatory('OapfInstrCountry', p_pmtInstrRec.CreditCardInstr.Billing_Country, l_url, l_db_nls, l_ecapp_nls);
449 IBY_NETUTILS_PVT.check_optional('OapfInstrPostalCode', p_pmtInstrRec.CreditCardInstr.Billing_PostalCode, l_url, l_db_nls, l_ecapp_nls);
450
451 END IF;
452
453 END IF;
454
455 -- Send http request to the payment server
456 --l_html := UTL_HTTP.REQUEST(l_url);
457
458 /* Bug 6318167 */
459 IF p_pmtInstrRec.nls_lang_param IS NOT NULL THEN
460 IBY_NETUTILS_PVT.check_optional('OapfNlsLang', p_pmtInstrRec.nls_lang_param, l_url, l_db_nls, l_ecapp_nls);
461 END IF;
462
463 -- set the security token
464 iby_security_pkg.store_credential(l_url,l_sec_cred);
465 iby_netutils_pvt.check_mandatory('OapfSecurityToken', TO_CHAR(l_sec_cred),
466 l_url, l_db_nls, l_ecapp_nls);
467
468 l_pos := INSTR(l_url,'?');
469 l_post_body := SUBSTR(l_url,l_pos+1,length(l_url));
470 l_post_body := RTRIM(l_post_body,'&');
471 l_url := SUBSTR(l_url,1,l_pos-1);
472
473 --dbms_output.put_line('l_pos : '||l_pos);
474 --dbms_output.put_line('l_url : '||l_url);
475 --dbms_output.put_line('l_post_body : '||l_post_body);
476
477 -- sending Post Request
478 IBY_NETUTILS_PVT.POST_REQUEST(l_url,l_post_body,l_html);
479
480 -- Unpack the results
481 IBY_NETUTILS_PVT.UNPACK_RESULTS_URL(l_html,l_names,l_values, l_status, l_errcode, l_errmessage);
482
483 iby_debug_pub.add('Return Parameter count : '|| l_values.COUNT,
484 iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
485
486 --Raising Exception to handle errors in unpacking resulting html file.
487 IF (l_status = -1) THEN
488 iby_debug_pub.add('Unpack status error',
489 FND_LOG.LEVEL_UNEXPECTED,
490 G_DEBUG_MODULE || l_api_name);
491
492 FND_MESSAGE.SET_NAME('IBY', 'IBY_204403_HTML_UNPACK_ERROR');
493 FND_MSG_PUB.Add;
494 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
495 END IF;
496
497 --Raising Exception to handle Servlet related errors.
498 IF (l_values.COUNT = 0 ) THEN
499 iby_debug_pub.add('Names count=0',
500 iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
501
502 FND_MESSAGE.SET_NAME('IBY', 'IBY_204402_JSERVLET_ERROR');
503 FND_MSG_PUB.Add;
504 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
505 END IF;
506
507 FOR i IN 1..l_names.COUNT LOOP
508 iby_debug_pub.add(l_names(i) || ': ' ||l_values(i),
509 iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
510 IF l_names(i) = 'OapfStatus' THEN
511 x_return_status := l_values(i);
512 ELSIF l_names(i) = 'OapfInstrId' THEN
513 x_instr_id := l_values(i);
514 ELSIF l_names(i) = 'OapfCode' THEN
515 x_result.Result_Code := l_values(i);
516 ELSIF l_names(i) = 'OapfCause' THEN
517 x_result.Result_Message := l_values(i);
518 --
519 -- simply copy the mesg returned verbatim;
520 -- this is done rather than reconstructing via 'OapfCode'
521 -- as msg tokens will not otherwise be filled
522 --
523 FND_MESSAGE.SET_NAME('IBY', 'IBY_9999');
524 FND_MESSAGE.SET_TOKEN('MESSAGE_TEXT', l_values(i));
525 FND_MSG_PUB.ADD;
526 END IF;
527
528 END LOOP;
529
530 IF (x_return_status = 0) THEN
531 x_return_status := FND_API.G_RET_STS_SUCCESS;
532 -- op completed successfully
533 FND_MESSAGE.SET_NAME('IBY','IBY_204170');
534 FND_MSG_PUB.ADD;
535 ELSE
536 x_return_status := FND_API.G_RET_STS_ERROR;
537 END IF;
538
539 FND_MSG_PUB.Count_And_Get
540 (
541 p_count => x_msg_count,
542 p_data => x_msg_data
543 );
544
545 iby_debug_pub.add('Exit',iby_debug_pub.G_LEVEL_PROCEDURE,l_dbg_mod);
546 EXCEPTION
547
548 -- Catch for version mismatch and
549 -- if the validation level is not full.
550 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
551
552
553 x_return_status := FND_API.G_RET_STS_ERROR;
554 x_msg_count := 1;
555 x_msg_data := FND_MSG_PUB.GET(
556 p_encoded => FND_API.g_false,
557 P_MSG_INDEX => FND_MSG_PUB.Count_msg
558 );
559
560 -- Catch for all the known errors
561 -- thrown from this procedure only.
562 WHEN FND_API.G_EXC_ERROR THEN
563
564 x_return_status := FND_API.G_RET_STS_ERROR;
565 x_msg_count := 1;
566 x_msg_data := FND_MSG_PUB.GET(
567 p_encoded => FND_API.g_false,
568 P_MSG_INDEX => FND_MSG_PUB.Count_msg
569 );
570
571 -- Catch for all the known errors
572 -- thrown from the procedures that are called by this procedure.
573 -- Whenever there is an error in the procedures that are called,
574 -- this exception is raised as long as the SQLCODE is -20000.
575 WHEN ERROR_FROM_SUBPROC THEN
576 --dbms_output.put_line('ERROR: ERROR_FROM_SUBPROC during call to API ');
577 --dbms_output.put_line('SQLerr is :'||substr(SQLERRM,1,150));
578 x_return_status := FND_API.G_RET_STS_ERROR;
579 iby_utility_pvt.handleException(SQLERRM,SQLCODE);
580 FND_MSG_PUB.Count_And_Get
581 ( p_count => x_msg_count,
582 p_data => x_msg_data
583 );
584
585 WHEN OTHERS THEN
586 --dbms_output.put_line('ERROR: Exception occured during call to API ' );
587 --dbms_output.put_line('SQLerr is :'||substr(SQLERRM,1,150));
588 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
589 FND_MSG_PUB.Count_And_Get
590 ( p_count => x_msg_count,
591 p_data => x_msg_data
592 );
593
594 END OraInstrAdd;
595
596
597 -------------------------------------------------------------------------------
598 -- 2. OraInstrMod
599 -- Start of comments
600 -- API name : OraInstrMod
601 -- Type : Public
602 -- Pre-reqs : None
603 -- Function : Modifies an existing payment instruments in iPayment.
604 -- Parameters :
605 -- IN : p_api_version IN NUMBER Required
606 -- p_init_msg_list IN VARCHAR2 Optional
607 -- p_commit IN VARCHAR2 Optional
608 -- p_validation_level IN NUMBER Optional
609 -- p_payer_id IN VARCHAR2 Required
610 -- p_pmtInstrRec IN PmtInstr_rec_type Required
611 --
612 -- OUT : x_return_status OUT VARCHAR2
613 -- x_msg_count OUT VARCHAR2
614 -- x_msg_data OUT NUMBER
615 -- Version :
616 -- Current version 1.0
617 -- Previous version 1.0
618 -- Initial version 1.0
619 -- End of comments
620 -------------------------------------------------------------------------------
621
622
623 PROCEDURE OraInstrMod (p_api_version IN NUMBER,
624 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
625 p_commit IN VARCHAR2 := FND_API.G_TRUE,
626 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
627 p_payer_id IN VARCHAR2,
628 p_pmtInstrRec IN PmtInstr_rec_type,
629 x_return_status OUT NOCOPY VARCHAR2,
630 x_msg_count OUT NOCOPY NUMBER,
631 x_msg_data OUT NOCOPY VARCHAR2,
632 x_result OUT NOCOPY IBY_FNDCPT_COMMON_PUB.Result_rec_type
633 ) IS
634
635 l_api_name CONSTANT VARCHAR2(30) := 'OraInstrMod';
636 l_api_version CONSTANT NUMBER := 1.0;
637
638 l_instrument_type VARCHAR2(80) := C_INSTRTYPE_UNREG;
639 l_cnt INTEGER;
640
641 ERROR_FROM_SUBPROC Exception;
642
643 -- This will catch all the exceptions from the procedure which is
644 -- subsequently called.This will trap all exceptions that have
645 -- SQLCODE = -20000 and name it as 'ERROR_FROM_SUBPROC'.
646 PRAGMA EXCEPTION_INIT( ERROR_FROM_SUBPROC, -20000 );
647
648 BEGIN
649
650 -- Standard call to check for call compatibility.
651 IF NOT FND_API.Compatible_API_Call ( l_api_version,
652 p_api_version,
653 l_api_name,
654 G_PKG_NAME )
655 THEN
656 FND_MESSAGE.SET_NAME('IBY', 'IBY_204400_API_VER_MISMATCH');
657 FND_MSG_PUB.Add;
658 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
659 END IF;
660
661 -- Initialize message list if p_init_msg_list is set to TRUE.
662 IF FND_API.to_Boolean( p_init_msg_list ) THEN
663 FND_MSG_PUB.initialize;
664 END IF;
665
666 -- Verifying if validation level is FULL, which is expected for PUBLIC APIs.
667 IF (p_validation_level <> g_validation_level) THEN
668 FND_MESSAGE.SET_NAME('IBY', 'IBY_204401_VAL_LEVEL_ERROR');
669 FND_MSG_PUB.Add;
670 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
671 END IF;
672
673 -- Initialize API return status to success
674 x_return_status := FND_API.G_RET_STS_SUCCESS;
675
676 -- START OF BODY OF API
677
678 -- check whether the payer_id is missing.
679 IF( TRIM( p_payer_id ) is NULL ) THEN
680 FND_MESSAGE.SET_NAME('IBY', 'IBY_20486');
681 FND_MSG_PUB.Add;
682 RAISE FND_API.G_EXC_ERROR;
683 -- Returns message 'PAYER_ID is mandatory'
684 END IF;
685
686 -- Checks whether the instrument type passed is valid or not
687 l_instrument_type := p_pmtInstrRec.InstrumentType;
688 IF( ( l_instrument_type <> C_INSTRTYPE_CREDITCARD ) AND
689 ( l_instrument_type <> C_INSTRTYPE_PURCHASECARD ) AND
690 ( l_instrument_type <> C_INSTRTYPE_BANKACCT ) ) THEN
691 FND_MESSAGE.SET_NAME('IBY', 'IBY_20487');
692 FND_MSG_PUB.Add;
693 RAISE FND_API.G_EXC_ERROR;
694 -- Returns message 'Invalid instrument type passed'.
695 END IF;
696
697 -- Check whether card number is passed for type Credit card.
698 IF( l_instrument_type = C_INSTRTYPE_CREDITCARD ) THEN
699
700 -- Card number should NOT be passed as it is an existing instrument.
701 IF( (p_pmtInstrRec.CreditCardInstr.CC_Num is not NULL) OR
702 (p_pmtInstrRec.CreditCardInstr.CC_Type is not NULL) ) THEN
703 FND_MESSAGE.SET_NAME('IBY', 'IBY_20489');
704 FND_MSG_PUB.Add;
705 RAISE FND_API.G_EXC_ERROR;
706 -- Returns message 'Neither Card number nor Card Type should be passed'
707 END IF;
708
709 -- Check whether mandatory/not desirable is passed for type Purchase card.
710 ELSIF( l_instrument_type = C_INSTRTYPE_PURCHASECARD ) THEN
711
712 -- Card number should NOT be passed as it is an existing instrument.
713 IF( (p_pmtInstrRec.PurchaseCardInstr.PC_Num is not NULL) OR
714 (p_pmtInstrRec.PurchaseCardInstr.PC_Type is not NULL) ) THEN
715 FND_MESSAGE.SET_NAME('IBY', 'IBY_20489');
716 FND_MSG_PUB.Add;
717 RAISE FND_API.G_EXC_ERROR;
718 -- Returns message 'Neither Card number nor Card Type should be passed'
719 END IF;
720
721 -- Subtype is mandatory.
722 IF( p_pmtInstrRec.PurchaseCardInstr.PC_SubType is NULL ) THEN
723 FND_MESSAGE.SET_NAME('IBY', 'IBY_20483');
724 FND_MSG_PUB.Add;
725 RAISE FND_API.G_EXC_ERROR;
726 -- Returns message 'Mandatory field(s) missing'
727 END IF;
728
729 -- Bank_Id and BankAcct_Num may NOT be modified for type Bank Account.
730 ELSIF( l_instrument_type = C_INSTRTYPE_BANKACCT ) THEN
731
732 -- Bank Id and BankAcct_Num should NOT be passed as it is an existing instrument.
733 IF( ( p_pmtInstrRec.BankAcctInstr.Bank_Id is not NULL ) OR
734 ( p_pmtInstrRec.BankAcctInstr.BankAcct_Num is not NULL ) ) THEN
735 FND_MESSAGE.SET_NAME('IBY', 'IBY_20490');
736 FND_MSG_PUB.Add;
737 RAISE FND_API.G_EXC_ERROR;
738 -- Returns message 'Neither Bank Id nor Bank Account Number should be passed'
739 END IF;
740
741 END IF;
742
743 -- Finally call the procedures that will modify the instrument.
744 IF( l_instrument_type = C_INSTRTYPE_BANKACCT ) THEN
745 null;
746 /*
747 IBY_BANKACCT_PKG.modifyBankAcct( NULL,
748 g_owner_type,
749 p_payer_id,
750 p_pmtInstrRec.BankAcctInstr.FIName,
751 NULL,
752 p_pmtInstrRec.BankAcctInstr.Bank_Id,
753 p_pmtInstrRec.BankAcctInstr.Branch_Id,
754 UPPER(p_pmtInstrRec.BankAcctInstr.BankAcct_Type),
755 p_pmtInstrRec.BankAcctInstr.BankAcct_Num,
756 p_pmtInstrRec.BankAcctInstr.BankAcct_HolderName,
757 p_pmtInstrRec.BankAcctInstr.Bank_Desc,
758 p_pmtInstrRec.Encryption_Key,
759 p_pmtInstrRec.BankAcctInstr.Instr_Id
760 );
761
762 */
763 ELSIF( l_instrument_type = C_INSTRTYPE_PURCHASECARD ) THEN
764 IBY_CREDITCARD_PKG.Update_Card
765 (FND_API.G_FALSE,
766 p_pmtInstrRec.PurchaseCardInstr.Instr_Id,
767 p_pmtInstrRec.PurchaseCardInstr.Owner_Id,
768 p_pmtInstrRec.PurchaseCardInstr.PC_HolderName,
769 p_pmtInstrRec.PurchaseCardInstr.Billing_Address_Id,
770 'S',
771 p_pmtInstrRec.PurchaseCardInstr.Billing_PostalCode,
772 p_pmtInstrRec.PurchaseCardInstr.Billing_Country,
773 p_pmtInstrRec.PurchaseCardInstr.PC_ExpDate,
774 'CREDITCARD',
775 'Y',
776 p_pmtInstrRec.PurchaseCardInstr.PC_Subtype,
777 p_pmtInstrRec.PurchaseCardInstr.FIName,
778 p_pmtInstrRec.PurchaseCardInstr.Single_Use_Flag,
779 p_pmtInstrRec.PurchaseCardInstr.Info_Only_Flag,
780 p_pmtInstrRec.PurchaseCardInstr.Card_Purpose,
781 p_pmtInstrRec.PurchaseCardInstr.PC_Desc,
782 p_pmtInstrRec.PurchaseCardInstr.Active_Flag,
783 p_pmtInstrRec.PurchaseCardInstr.Inactive_Date,
784 p_pmtInstrRec.PurchaseCardInstr.attribute_category,
785 p_pmtInstrRec.PurchaseCardInstr.attribute1,
786 p_pmtInstrRec.PurchaseCardInstr.attribute2,
787 p_pmtInstrRec.PurchaseCardInstr.attribute3,
788 p_pmtInstrRec.PurchaseCardInstr.attribute4,
789 p_pmtInstrRec.PurchaseCardInstr.attribute5,
790 p_pmtInstrRec.PurchaseCardInstr.attribute6,
791 p_pmtInstrRec.PurchaseCardInstr.attribute7,
792 p_pmtInstrRec.PurchaseCardInstr.attribute8,
793 p_pmtInstrRec.PurchaseCardInstr.attribute9,
794 p_pmtInstrRec.PurchaseCardInstr.attribute10,
795 p_pmtInstrRec.PurchaseCardInstr.attribute11,
796 p_pmtInstrRec.PurchaseCardInstr.attribute12,
797 p_pmtInstrRec.PurchaseCardInstr.attribute13,
798 p_pmtInstrRec.PurchaseCardInstr.attribute14,
799 p_pmtInstrRec.PurchaseCardInstr.attribute15,
800 p_pmtInstrRec.PurchaseCardInstr.attribute16,
801 p_pmtInstrRec.PurchaseCardInstr.attribute17,
802 p_pmtInstrRec.PurchaseCardInstr.attribute18,
803 p_pmtInstrRec.PurchaseCardInstr.attribute19,
804 p_pmtInstrRec.PurchaseCardInstr.attribute20,
805 p_pmtInstrRec.PurchaseCardInstr.attribute21,
806 p_pmtInstrRec.PurchaseCardInstr.attribute22,
807 p_pmtInstrRec.PurchaseCardInstr.attribute23,
808 p_pmtInstrRec.PurchaseCardInstr.attribute24,
809 p_pmtInstrRec.PurchaseCardInstr.attribute25,
810 p_pmtInstrRec.PurchaseCardInstr.attribute26,
811 p_pmtInstrRec.PurchaseCardInstr.attribute27,
812 p_pmtInstrRec.PurchaseCardInstr.attribute28,
813 p_pmtInstrRec.PurchaseCardInstr.attribute29,
814 p_pmtInstrRec.PurchaseCardInstr.attribute30,
815 x_result.Result_Code
816 );
817 ELSIF( l_instrument_type = C_INSTRTYPE_CREDITCARD ) THEN
818 IBY_CREDITCARD_PKG.Update_Card
819 (FND_API.G_FALSE,
820 p_pmtInstrRec.CreditCardInstr.Instr_Id,
821 p_pmtInstrRec.CreditCardInstr.Owner_Id,
822 p_pmtInstrRec.CreditCardInstr.CC_HolderName,
823 p_pmtInstrRec.CreditCardInstr.Billing_Address_Id,
824 'S',
825 p_pmtInstrRec.CreditCardInstr.Billing_PostalCode,
826 p_pmtInstrRec.CreditCardInstr.Billing_Country,
827 p_pmtInstrRec.CreditCardInstr.CC_ExpDate,
828 'CREDITCARD',
829 'N',
830 NULL,
831 p_pmtInstrRec.CreditCardInstr.FIName,
832 p_pmtInstrRec.CreditCardInstr.Single_Use_Flag,
833 p_pmtInstrRec.CreditCardInstr.Info_Only_Flag,
834 p_pmtInstrRec.CreditCardInstr.Card_Purpose,
835 p_pmtInstrRec.CreditCardInstr.CC_Desc,
836 p_pmtInstrRec.CreditCardInstr.Active_Flag,
837 p_pmtInstrRec.CreditCardInstr.Inactive_Date,
838 p_pmtInstrRec.CreditCardInstr.attribute_category,
839 p_pmtInstrRec.CreditCardInstr.attribute1,
840 p_pmtInstrRec.CreditCardInstr.attribute2,
841 p_pmtInstrRec.CreditCardInstr.attribute3,
842 p_pmtInstrRec.CreditCardInstr.attribute4,
843 p_pmtInstrRec.CreditCardInstr.attribute5,
844 p_pmtInstrRec.CreditCardInstr.attribute6,
845 p_pmtInstrRec.CreditCardInstr.attribute7,
846 p_pmtInstrRec.CreditCardInstr.attribute8,
847 p_pmtInstrRec.CreditCardInstr.attribute9,
848 p_pmtInstrRec.CreditCardInstr.attribute10,
849 p_pmtInstrRec.CreditCardInstr.attribute11,
850 p_pmtInstrRec.CreditCardInstr.attribute12,
851 p_pmtInstrRec.CreditCardInstr.attribute13,
852 p_pmtInstrRec.CreditCardInstr.attribute14,
853 p_pmtInstrRec.CreditCardInstr.attribute15,
854 p_pmtInstrRec.CreditCardInstr.attribute16,
855 p_pmtInstrRec.CreditCardInstr.attribute17,
856 p_pmtInstrRec.CreditCardInstr.attribute18,
857 p_pmtInstrRec.CreditCardInstr.attribute19,
858 p_pmtInstrRec.CreditCardInstr.attribute20,
859 p_pmtInstrRec.CreditCardInstr.attribute21,
860 p_pmtInstrRec.CreditCardInstr.attribute22,
861 p_pmtInstrRec.CreditCardInstr.attribute23,
862 p_pmtInstrRec.CreditCardInstr.attribute24,
863 p_pmtInstrRec.CreditCardInstr.attribute25,
864 p_pmtInstrRec.CreditCardInstr.attribute26,
865 p_pmtInstrRec.CreditCardInstr.attribute27,
866 p_pmtInstrRec.CreditCardInstr.attribute28,
867 p_pmtInstrRec.CreditCardInstr.attribute29,
868 p_pmtInstrRec.CreditCardInstr.attribute30,
869 x_result.Result_Code
870 );
871 END IF;
872
873
874 -- Return success when everything is fine.
875 x_msg_count := 1;
876
877 -- Returns message 'operation completed successfully.'
878 FND_MESSAGE.SET_NAME('IBY', 'IBY_204170' );
879 FND_MSG_PUB.Add;
880 x_msg_data := FND_MSG_PUB.GET(
881 p_encoded => FND_API.g_false,
882 P_MSG_INDEX => FND_MSG_PUB.Count_msg
883 );
884
885 EXCEPTION
886
887 -- Catch for version mismatch and
888 -- if the validation level is not full.
889 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
890
891 x_return_status := FND_API.G_RET_STS_ERROR;
892 x_msg_count := 1;
893 x_msg_data := FND_MSG_PUB.GET(
894 p_encoded => FND_API.g_false,
895 P_MSG_INDEX => FND_MSG_PUB.Count_msg
896 );
897
898 -- Catch for all the known errors
899 -- thrown from this procedure only.
900 WHEN FND_API.G_EXC_ERROR THEN
901
902 x_return_status := FND_API.G_RET_STS_ERROR;
903 x_msg_count := 1;
904 x_msg_data := FND_MSG_PUB.GET(
905 p_encoded => FND_API.g_false,
906 P_MSG_INDEX => FND_MSG_PUB.Count_msg
907 );
908
909 -- Catch for all the known errors
910 -- thrown from the procedures that are called by this procedure.
911 -- Whenever there is an error in the procedures that are called,
912 -- this exception is raised as long as the SQLCODE is -20000.
913 WHEN ERROR_FROM_SUBPROC THEN
914
915 x_return_status := FND_API.G_RET_STS_ERROR;
916 iby_utility_pvt.handleException(SQLERRM,SQLCODE);
917 FND_MSG_PUB.Count_And_Get
918 ( p_count => x_msg_count,
919 p_data => x_msg_data
920 );
921
922 WHEN OTHERS THEN
923
924 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
925 FND_MSG_PUB.Count_And_Get
926 ( p_count => x_msg_count,
927 p_data => x_msg_data
928 );
929
930
931 END OraInstrMod;
932
933
934 -------------------------------------------------------------------------------
935 -- 3. OraInstrDel
936 -- Start of comments
937 -- API name : OraInstrDel
938 -- Type : Public
939 -- Pre-reqs : None
940 -- Function : Deletes an existing payment instruments in iPayment.
941 -- Parameters :
942 -- IN : p_api_version IN NUMBER Required
943 -- p_init_msg_list IN VARCHAR2 Optional
944 -- p_commit IN VARCHAR2 Optional
945 -- p_validation_level IN NUMBER Optional
946 -- p_payer_id IN VARCHAR2 Required
947 -- p_instr_id IN NUMBER Required
948 --
949 -- OUT : x_return_status OUT VARCHAR2
950 -- x_msg_count OUT VARCHAR2
951 -- x_msg_data OUT NUMBER
952 -- Version :
953 -- Current version 1.0
954 -- Previous version 1.0
955 -- Initial version 1.0
956 -- End of comments
957 -------------------------------------------------------------------------------
958
959
960 PROCEDURE OraInstrDel ( p_api_version IN NUMBER,
961 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
962 p_commit IN VARCHAR2 := FND_API.G_TRUE,
963 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
964 p_payer_id IN VARCHAR2,
965 p_instr_id IN NUMBER,
966 x_return_status OUT NOCOPY VARCHAR2,
967 x_msg_count OUT NOCOPY NUMBER,
968 x_msg_data OUT NOCOPY VARCHAR2
969 ) IS
970
971 l_api_name CONSTANT VARCHAR2(30) := 'OraInstrDel';
972 l_api_version CONSTANT NUMBER := 1.0;
973
974 ERROR_FROM_SUBPROC Exception;
975
976 -- This will catch all the exceptions from the procedure which is
977 -- subsequently called.This will trap all exceptions that have
978 -- SQLCODE = -20000 and name it as 'ERROR_FROM_SUBPROC'.
979 PRAGMA EXCEPTION_INIT( ERROR_FROM_SUBPROC, -20000 );
980
981 BEGIN
982
983 -- Standard call to check for call compatibility.
984 IF NOT FND_API.Compatible_API_Call ( l_api_version,
985 p_api_version,
986 l_api_name,
987 G_PKG_NAME )
988 THEN
989 FND_MESSAGE.SET_NAME('IBY', 'IBY_204400_API_VER_MISMATCH');
990 FND_MSG_PUB.Add;
991 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
992 END IF;
993
994 -- Initialize message list if p_init_msg_list is set to TRUE.
995 IF FND_API.to_Boolean( p_init_msg_list ) THEN
996 FND_MSG_PUB.initialize;
997 END IF;
998
999 -- Verifying if validation level is FULL, which is expected for PUBLIC APIs.
1000 IF (p_validation_level <> g_validation_level) THEN
1001 FND_MESSAGE.SET_NAME('IBY', 'IBY_204401_VAL_LEVEL_ERROR');
1002 FND_MSG_PUB.Add;
1003 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1004 END IF;
1005
1006 -- Initialize API return status to success
1007 x_return_status := FND_API.G_RET_STS_SUCCESS;
1008
1009 -- START OF BODY OF API
1010
1011 -- check whether the payer_id is missing.
1012 IF( TRIM( p_payer_id ) is NULL ) THEN
1013 FND_MESSAGE.SET_NAME('IBY', 'IBY_20486');
1014 FND_MSG_PUB.Add;
1015 RAISE FND_API.G_EXC_ERROR;
1016 -- Returns message 'PAYER_ID is mandatory'
1017 END IF;
1018
1019 -- check whether the instr_id is missing.
1020 IF( p_instr_id is NULL ) THEN
1021 FND_MESSAGE.SET_NAME('IBY', 'IBY_20483');
1022 FND_MSG_PUB.Add;
1023 RAISE FND_API.G_EXC_ERROR;
1024 -- Returns message 'Mandatory field(s) missing'
1025 END IF;
1026
1027 -- Call the procedure that will delete the instrument.
1028 IBY_INSTRHOLDER_PKG.deleteHolderInstr( NULL,
1029 g_owner_type,
1030 p_payer_id,
1031 NULL,
1032 p_instr_id
1033 );
1034
1035 -- Return success when everything is fine.
1036 x_msg_count := 1;
1037
1038 -- Returns message 'operation completed successfully.'
1039 FND_MESSAGE.SET_NAME('IBY', 'IBY_204170' );
1040 FND_MSG_PUB.Add;
1041 x_msg_data := FND_MSG_PUB.GET(
1042 p_encoded => FND_API.g_false,
1043 P_MSG_INDEX => FND_MSG_PUB.Count_msg
1044 );
1045
1046 EXCEPTION
1047
1048 -- Catch for version mismatch and
1049 -- if the validation level is not full.
1050 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1051
1052 x_return_status := FND_API.G_RET_STS_ERROR;
1053 x_msg_count := 1;
1054 x_msg_data := FND_MSG_PUB.GET(
1055 p_encoded => FND_API.g_false,
1056 P_MSG_INDEX => FND_MSG_PUB.Count_msg
1057 );
1058
1059 -- Catch for all the known errors
1060 -- thrown from this procedure only.
1061 WHEN FND_API.G_EXC_ERROR THEN
1062
1063 x_return_status := FND_API.G_RET_STS_ERROR;
1064 x_msg_count := 1;
1065 x_msg_data := FND_MSG_PUB.GET(
1066 p_encoded => FND_API.g_false,
1067 P_MSG_INDEX => FND_MSG_PUB.Count_msg
1068 );
1069
1070 -- Catch for all the known errors
1071 -- thrown from the procedures that are called by this procedure.
1072 -- Whenever there is an error in the procedures that are called,
1073 -- this exception is raised as long as the SQLCODE is -20000.
1074 WHEN ERROR_FROM_SUBPROC THEN
1075
1076 x_return_status := FND_API.G_RET_STS_ERROR;
1077 iby_utility_pvt.handleException(SQLERRM,SQLCODE);
1078 FND_MSG_PUB.Count_And_Get
1079 ( p_count => x_msg_count,
1080 p_data => x_msg_data
1081 );
1082
1083 WHEN OTHERS THEN
1084
1085 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1086 FND_MSG_PUB.Count_And_Get
1087 ( p_count => x_msg_count,
1088 p_data => x_msg_data
1089 );
1090
1091
1092 END OraInstrDel;
1093
1094 -------------------------------------------------------------------------------
1095 -- 4. OraInstrInq
1096 -- Start of comments
1097 -- API name : OraInstrInq
1098 -- Type : Public
1099 -- Pre-reqs : None
1100 -- Function : Returns all the payment instruments that a payer may have.
1101 -- This is based on the payer_id only.
1102 -- Parameters :
1103 -- IN : p_api_version IN NUMBER Required
1104 -- p_init_msg_list IN VARCHAR2 Optional
1105 -- p_commit IN VARCHAR2 Optional
1106 -- p_validation_level IN NUMBER Optional
1107 -- p_payer_id IN VARCHAR2 Required
1108 --
1109 -- OUT : x_return_status OUT VARCHAR2
1110 -- x_msg_count OUT VARCHAR2
1111 -- x_msg_data OUT NUMBER
1112 -- x_creditcard_tbl OUT CreditCard_tbl_type
1113 -- x_purchasecard_tbl OUT PurchaseCard_tbl_type
1114 -- x_bankacct_tbl OUT BankAcct_tbl_type
1115 -- Version :
1116 -- Current version 1.0
1117 -- Previous version 1.0
1118 -- Initial version 1.0
1119 -- End of comments
1120 -------------------------------------------------------------------------------
1121
1122 PROCEDURE OraInstrInq ( p_api_version IN NUMBER,
1123 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1124 p_commit IN VARCHAR2 := FND_API.G_TRUE,
1125 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1126 p_payer_id IN VARCHAR2,
1127 x_return_status OUT NOCOPY VARCHAR2,
1128 x_msg_count OUT NOCOPY NUMBER,
1129 x_msg_data OUT NOCOPY VARCHAR2,
1130 x_creditcard_tbl OUT NOCOPY CreditCard_tbl_type,
1131 x_purchasecard_tbl OUT NOCOPY PurchaseCard_tbl_type,
1132 x_bankacct_tbl OUT NOCOPY BankAcct_tbl_type
1133 )
1134 IS
1135 BEGIN
1136 OraInstrInq(p_api_version,p_init_msg_list,p_commit,p_validation_level,p_payer_id,
1137 NULL,x_return_status,x_msg_count,x_msg_data,x_creditcard_tbl,
1138 x_purchasecard_tbl,x_bankacct_tbl);
1139 END OraInstrInq;
1140
1141
1142 PROCEDURE OraInstrInq ( p_api_version IN NUMBER,
1143 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1144 p_commit IN VARCHAR2 := FND_API.G_TRUE,
1145 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1146 p_payer_id IN VARCHAR2,
1147 p_sys_sec_key IN VARCHAR2,
1148 x_return_status OUT NOCOPY VARCHAR2,
1149 x_msg_count OUT NOCOPY NUMBER,
1150 x_msg_data OUT NOCOPY VARCHAR2,
1151 x_creditcard_tbl OUT NOCOPY CreditCard_tbl_type,
1152 x_purchasecard_tbl OUT NOCOPY PurchaseCard_tbl_type,
1153 x_bankacct_tbl OUT NOCOPY BankAcct_tbl_type
1154 ) IS
1155
1156 l_api_name CONSTANT VARCHAR2(30) := 'OraInstrInq';
1157 l_api_version CONSTANT NUMBER := 1.0;
1158
1159 l_count INTEGER;
1160
1161 BEGIN
1162
1163 -- Standard call to check for call compatibility.
1164 IF NOT FND_API.Compatible_API_Call ( l_api_version,
1165 p_api_version,
1166 l_api_name,
1167 G_PKG_NAME )
1168 THEN
1169 FND_MESSAGE.SET_NAME('IBY', 'IBY_204400_API_VER_MISMATCH');
1170 FND_MSG_PUB.Add;
1171 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1172 END IF;
1173
1174 -- Initialize message list if p_init_msg_list is set to TRUE.
1175 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1176 FND_MSG_PUB.initialize;
1177 END IF;
1178
1179 -- Verifying if validation level is FULL, which is expected for PUBLIC APIs.
1180 IF (p_validation_level <> g_validation_level) THEN
1181 FND_MESSAGE.SET_NAME('IBY', 'IBY_204401_VAL_LEVEL_ERROR');
1182 FND_MSG_PUB.Add;
1183 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1184 END IF;
1185
1186 -- Initialize API return status to success
1187 x_return_status := FND_API.G_RET_STS_SUCCESS;
1188
1189 -- START OF BODY OF API
1190
1191 -- check whether the payer_id is missing.
1192 IF( TRIM( p_payer_id ) is NULL ) THEN
1193 FND_MESSAGE.SET_NAME('IBY', 'IBY_20486');
1194 FND_MSG_PUB.Add;
1195 RAISE FND_API.G_EXC_ERROR;
1196 -- Returns message 'PAYER_ID is mandatory'
1197 END IF;
1198
1199 -- Check whether the payer exists.If not,
1200 -- then we don't even try to fetch the records.
1201 SELECT count(*) INTO l_count
1202 FROM IBY_INSTRHOLDER
1203 WHERE ownerid = p_payer_id
1204 AND activestatus = 1;
1205
1206 -- If nothing is found throw an error.
1207 IF( l_count = 0 ) THEN
1208 FND_MESSAGE.SET_NAME('IBY', 'IBY_20491');
1209 FND_MSG_PUB.Add;
1210 RAISE FND_API.G_EXC_ERROR;
1211 -- Returns message 'PAYER_ID does not exist'
1212 END IF;
1213
1214 -- Call the utility procedure that will do the job
1215 -- of returning all the instruments.
1216 Get_Instrument_Details ( payer_id => p_payer_id,
1217 instr_id => NULL,
1218 sys_master_key => p_sys_sec_key,
1219 creditcard_tbl => x_creditcard_tbl,
1220 purchasecard_tbl => x_purchasecard_tbl,
1221 bankacct_tbl => x_bankacct_tbl
1222 );
1223
1224
1225 -- Return success when everything is fine.
1226 x_msg_count := 1;
1227
1228 IF( x_creditcard_tbl.count = 0 AND
1229 x_purchasecard_tbl.count = 0 AND
1230 x_bankacct_tbl.count = 0 ) THEN
1231 -- Returns message 'No records found matching the given criteria.'
1232 FND_MESSAGE.SET_NAME('IBY', 'IBY_204041' );
1233 ELSE
1234 -- Returns message 'operation completed successfully.'
1235 FND_MESSAGE.SET_NAME('IBY', 'IBY_204170' );
1236 END IF;
1237
1238 FND_MSG_PUB.Add;
1239 x_msg_data := FND_MSG_PUB.GET(
1240 p_encoded => FND_API.g_false,
1241 P_MSG_INDEX => FND_MSG_PUB.Count_msg
1242 );
1243
1244 EXCEPTION
1245
1246 -- Catch for version mismatch and
1247 -- if the validation level is not full.
1248 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1249
1250 x_return_status := FND_API.G_RET_STS_ERROR;
1251 x_msg_count := 1;
1252 x_msg_data := FND_MSG_PUB.GET(
1253 p_encoded => FND_API.g_false,
1254 P_MSG_INDEX => FND_MSG_PUB.Count_msg
1255 );
1256
1257 -- Catch for all the known errors
1258 -- thrown from this procedure only.
1259 WHEN FND_API.G_EXC_ERROR THEN
1260
1261 x_return_status := FND_API.G_RET_STS_ERROR;
1262 x_msg_count := 1;
1263 x_msg_data := FND_MSG_PUB.GET(
1264 p_encoded => FND_API.g_false,
1265 P_MSG_INDEX => FND_MSG_PUB.Count_msg
1266 );
1267
1268 WHEN OTHERS THEN
1269
1270 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1271 iby_utility_pvt.handleException(SQLERRM,SQLCODE);
1272 FND_MSG_PUB.Count_And_Get
1273 ( p_count => x_msg_count,
1274 p_data => x_msg_data
1275 );
1276
1277 END OraInstrInq;
1278
1279 -------------------------------------------------------------------------------
1280 -- 5. OraInstrInq
1281 -- Start of comments
1282 -- API name : OraInstrInq
1283 -- Type : Public
1284 -- Pre-reqs : None
1285 -- Function : Returns the payment instrument information for an instr_id.
1286 -- This is based on the payer_id and instr_id.
1287 -- Parameters :
1288 -- IN : p_api_version IN NUMBER Required
1289 -- p_init_msg_list IN VARCHAR2 Optional
1290 -- p_commit IN VARCHAR2 Optional
1291 -- p_validation_level IN NUMBER Optional
1292 -- p_payer_id IN VARCHAR2 Required
1293 -- p_instr_id IN NUMBER Required
1294 --
1295 -- OUT : x_return_status OUT VARCHAR2
1296 -- x_msg_count OUT VARCHAR2
1297 -- x_msg_data OUT NUMBER
1298 -- x_pmtInstrRec OUT PmtInstr_rec_type
1299 -- Version :
1300 -- Current version 1.0
1301 -- Previous version 1.0
1302 -- Initial version 1.0
1303 -- End of comments
1304 -------------------------------------------------------------------------------
1305
1306 PROCEDURE OraInstrInq ( p_api_version IN NUMBER,
1307 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1308 p_commit IN VARCHAR2 := FND_API.G_TRUE,
1309 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1310 p_payer_id IN VARCHAR2,
1311 p_instr_id IN NUMBER,
1312 x_return_status OUT NOCOPY VARCHAR2,
1313 x_msg_count OUT NOCOPY NUMBER,
1314 x_msg_data OUT NOCOPY VARCHAR2,
1315 x_pmtInstrRec OUT NOCOPY PmtInstr_rec_type
1316 )
1317 IS
1318 BEGIN
1319 OraInstrInq(p_api_version,p_init_msg_list,p_commit,p_validation_level,p_payer_id,
1320 p_instr_id,NULL,x_return_status,x_msg_count,x_msg_data,x_pmtInstrRec);
1321 END OraInstrInq;
1322
1323
1324 PROCEDURE OraInstrInq ( p_api_version IN NUMBER,
1325 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1326 p_commit IN VARCHAR2 := FND_API.G_TRUE,
1327 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1328 p_payer_id IN VARCHAR2,
1329 p_instr_id IN NUMBER,
1330 p_sys_sec_key IN VARCHAR2,
1331 x_return_status OUT NOCOPY VARCHAR2,
1332 x_msg_count OUT NOCOPY NUMBER,
1333 x_msg_data OUT NOCOPY VARCHAR2,
1334 x_pmtInstrRec OUT NOCOPY PmtInstr_rec_type
1335 ) IS
1336
1337 l_api_name CONSTANT VARCHAR2(30) := 'OraInstrInq';
1338 l_api_version CONSTANT NUMBER := 1.0;
1339
1340 l_count INTEGER;
1341
1342 l_creditcard_tbl CreditCard_tbl_type;
1343 l_purchasecard_tbl PurchaseCard_tbl_type;
1344 l_bankacct_tbl BankAcct_tbl_type;
1345
1346 BEGIN
1347
1348 -- Standard call to check for call compatibility.
1349 IF NOT FND_API.Compatible_API_Call ( l_api_version,
1350 p_api_version,
1351 l_api_name,
1352 G_PKG_NAME )
1353 THEN
1354 FND_MESSAGE.SET_NAME('IBY', 'IBY_204400_API_VER_MISMATCH');
1355 FND_MSG_PUB.Add;
1356 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1357 END IF;
1358
1359 -- Initialize message list if p_init_msg_list is set to TRUE.
1360 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1361 FND_MSG_PUB.initialize;
1362 END IF;
1363
1364 -- Verifying if validation level is FULL, which is expected for PUBLIC APIs.
1365 IF (p_validation_level <> g_validation_level) THEN
1366 FND_MESSAGE.SET_NAME('IBY', 'IBY_204401_VAL_LEVEL_ERROR');
1367 FND_MSG_PUB.Add;
1368 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1369 END IF;
1370
1371 -- Initialize API return status to success
1372 x_return_status := FND_API.G_RET_STS_SUCCESS;
1373
1374 -- START OF BODY OF API
1375
1376 -- Initialize the instrument type
1377 x_pmtInstrRec.InstrumentType := C_INSTRTYPE_UNREG;
1378
1379 -- check whether the payer_id is missing.
1380 IF( TRIM( p_payer_id ) is NULL ) THEN
1381 FND_MESSAGE.SET_NAME('IBY', 'IBY_20486');
1382 FND_MSG_PUB.Add;
1383 RAISE FND_API.G_EXC_ERROR;
1384 -- Returns message 'PAYER_ID is mandatory'
1385 END IF;
1386
1387 -- check whether the instr_id is missing.
1388 IF( p_instr_id is NULL ) THEN
1389 FND_MESSAGE.SET_NAME('IBY', 'IBY_20483');
1390 FND_MSG_PUB.Add;
1391 RAISE FND_API.G_EXC_ERROR;
1392 -- Returns message 'Mandatory field(s) missing'
1393 END IF;
1394
1395 -- Check whether the payer exists.
1396 -- If not, then we don't even try to fetch the records.
1397 SELECT count(*) INTO l_count
1398 FROM IBY_INSTRHOLDER
1399 WHERE ownerid = p_payer_id
1400 AND activestatus = 1;
1401
1402 -- Throw an exception when payer not found.
1403 IF( l_count = 0 ) THEN
1404 FND_MESSAGE.SET_NAME('IBY', 'IBY_20491');
1405 FND_MSG_PUB.Add;
1406 RAISE FND_API.G_EXC_ERROR;
1407 -- Returns message 'PAYER_ID does not exist'
1408 END IF;
1409
1410 -- Check whether the instrument exists.
1411 -- If not, then we don't even try to fetch the records.
1412 SELECT count(*) INTO l_count
1413 FROM IBY_INSTRHOLDER
1414 WHERE instrid = p_instr_id
1415 AND activestatus = 1;
1416
1417 -- Throw an exception.
1418 IF( l_count = 0 ) THEN
1419 FND_MESSAGE.SET_NAME('IBY', 'IBY_20492');
1420 FND_MSG_PUB.Add;
1421 RAISE FND_API.G_EXC_ERROR;
1422 -- Returns message 'Instrument does not exist'
1423 END IF;
1424
1425 -- Check whether the payer holds the instrument.
1426 -- If not, then we don't even try to fetch the records.
1427 SELECT count(*) INTO l_count
1428 FROM IBY_INSTRHOLDER
1429 WHERE instrid = p_instr_id
1430 AND ownerid = p_payer_id
1431 AND activestatus = 1;
1432
1433 -- Throw an exception when nothing is found.
1434 IF( l_count = 0 ) THEN
1435 FND_MESSAGE.SET_NAME('IBY', 'IBY_20511');
1436 FND_MSG_PUB.Add;
1437 RAISE FND_API.G_EXC_ERROR;
1438 -- Returns message 'User does not hold instr'
1439 END IF;
1440
1441 -- Call the utility procedure that will do the job
1442 -- of returning the instrument information.
1443 Get_Instrument_Details ( payer_id => p_payer_id,
1444 instr_id => p_instr_id,
1445 sys_master_key => p_sys_sec_key,
1446 creditcard_tbl => l_creditcard_tbl,
1447 purchasecard_tbl => l_purchasecard_tbl,
1448 bankacct_tbl => l_bankacct_tbl
1449 );
1450
1451 IF( l_creditcard_tbl.count <> 0 ) THEN
1452 x_pmtInstrRec.InstrumentType := C_INSTRTYPE_CREDITCARD;
1453 x_pmtInstrRec.CreditCardInstr := l_creditcard_tbl(1);
1454 ELSIF( l_purchasecard_tbl.count <> 0 ) THEN
1455 x_pmtInstrRec.InstrumentType := C_INSTRTYPE_PURCHASECARD;
1456 x_pmtInstrRec.PurchaseCardInstr := l_purchasecard_tbl(1);
1457 ELSIF( l_bankacct_tbl.count <> 0 ) THEN
1458 x_pmtInstrRec.InstrumentType := C_INSTRTYPE_BANKACCT;
1459 x_pmtInstrRec.BankAcctInstr := l_bankacct_tbl(1);
1460 END IF;
1461
1462 -- Return success when everything is fine.
1463 x_msg_count := 1;
1464
1465 -- Returns message 'operation completed successfully.'
1466 FND_MESSAGE.SET_NAME('IBY', 'IBY_204170' );
1467 FND_MSG_PUB.Add;
1468 x_msg_data := FND_MSG_PUB.GET(
1469 p_encoded => FND_API.g_false,
1470 P_MSG_INDEX => FND_MSG_PUB.Count_msg
1471 );
1472
1473 EXCEPTION
1474
1475 -- Catch for version mismatch and
1476 -- if the validation level is not full.
1477 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1478
1479 x_return_status := FND_API.G_RET_STS_ERROR;
1480 x_msg_count := 1;
1481 x_msg_data := FND_MSG_PUB.GET(
1482 p_encoded => FND_API.g_false,
1483 P_MSG_INDEX => FND_MSG_PUB.Count_msg
1484 );
1485
1486 -- Catch for all the known errors
1487 -- thrown from this procedure only.
1488 WHEN FND_API.G_EXC_ERROR THEN
1489
1490 x_return_status := FND_API.G_RET_STS_ERROR;
1491 x_msg_count := 1;
1492 x_msg_data := FND_MSG_PUB.GET(
1493 p_encoded => FND_API.g_false,
1494 P_MSG_INDEX => FND_MSG_PUB.Count_msg
1495 );
1496
1497 WHEN OTHERS THEN
1498
1499 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1500 iby_utility_pvt.handleException(SQLERRM,SQLCODE);
1501 FND_MSG_PUB.Count_And_Get
1502 ( p_count => x_msg_count,
1503 p_data => x_msg_data
1504 );
1505
1506 END OraInstrInq;
1507
1508
1509 -- Start of comments
1510 -- API name : SecureCardInfo
1511 -- Type : Private
1512 -- Function : Secures other sensitive card data and returns the
1513 -- respective segment_IDs.
1514 -- Parameters :
1515 -- IN : p_cardExpiryDate IN DATE Optional
1516 -- p_cardHolderName IN VARCHAR2 Optional
1517 --
1518 PROCEDURE SecureCardInfo (p_cardExpiryDate IN DATE,
1519 p_cardHolderName IN VARCHAR2,
1520 x_return_status OUT NOCOPY VARCHAR2,
1521 x_msg_count OUT NOCOPY NUMBER,
1522 x_msg_data OUT NOCOPY VARCHAR2,
1523 x_resp_rec OUT NOCOPY SecureCardInfoResp_rec_type
1524 ) IS
1525
1526
1527 --The following 3 variables are meant for output of
1528 --get_baseurl procedure.
1529 l_status_url VARCHAR2(2000);
1530 l_msg_count_url NUMBER := 0;
1531 l_msg_data_url VARCHAR2(2000);
1532
1533 l_api_name CONSTANT VARCHAR2(30) := 'SecureCardInfo';
1534 l_oapf_action CONSTANT VARCHAR2(30) := 'secureCardInfo';
1535 l_api_version CONSTANT NUMBER := 1.0;
1536
1537 l_url VARCHAR2(30000) ;
1538 l_get_baseurl VARCHAR2(2000);
1539
1540 l_db_nls VARCHAR2(80) := NULL;
1541 l_ecapp_nls VARCHAR2(80) := NULL;
1542
1543 l_sec_cred NUMBER;
1544
1545 l_pos NUMBER := 0;
1546 l_post_body VARCHAR2(30000);
1547 l_html VARCHAR2(32767) ;
1548 l_names IBY_NETUTILS_PVT.v240_tbl_type;
1549 l_values IBY_NETUTILS_PVT.v240_tbl_type;
1550
1551
1552 --The following 3 variables are meant for output of
1553 --unpack_results_url procedure.
1554 l_status NUMBER := 0;
1555 l_errcode NUMBER := 0;
1556 l_errmessage VARCHAR2(2000) := 'Success';
1557
1558 BEGIN
1559
1560 iby_debug_pub.add(debug_msg => 'Enter',
1561 debug_level => FND_LOG.LEVEL_PROCEDURE,
1562 module => G_DEBUG_MODULE || '.SecureCardInfo');
1563
1564 -- test_debug('SecureCardInfo=> Enter');
1565 -- Initialize API return status to success
1566 x_return_status := FND_API.G_RET_STS_SUCCESS;
1567
1568 -- START OF BODY OF API
1569
1570 IF (p_cardExpiryDate IS NULL
1571 AND p_cardHolderName IS NULL) THEN
1572 RETURN;
1573 END IF;
1574
1575 IBY_NETUTILS_PVT.get_baseurl(l_get_baseurl);
1576 --dbms_output.put_line('l_get_baseurl= ' || l_get_baseurl);
1577 --test_debug('SecureCardInfo l_get_baseurl= '|| l_get_baseurl);
1578 -- dbms_output.put_line('l_status_url= ' || l_status_url);
1579 -- dbms_output.put_line('l_msg_count_url= ' || l_msg_count_url);
1580 -- dbms_output.put_line('l_msg_data_url= ' || l_msg_data_url);
1581
1582 -- Construct the full URL to send to the ECServlet.
1583 l_url := l_get_baseurl;
1584
1585 l_db_nls := IBY_NETUTILS_PVT.get_local_nls();
1586 l_ecapp_nls := NULL; -- not passed in this api??
1587
1588 --MANDATORY INPUT PARAMETERS
1589 IBY_NETUTILS_PVT.check_mandatory('OapfAction', l_oapf_action, l_url, l_db_nls, l_ecapp_nls);
1590
1591 --OPTIONAL INPUT PARAMETERS
1592 IBY_NETUTILS_PVT.check_optional('OapfExpDate', to_char(p_cardExpiryDate,'YYYY-MM-DD'), l_url, l_db_nls, l_ecapp_nls);
1593 IBY_NETUTILS_PVT.check_optional('OapfChname', p_cardHolderName, l_url, l_db_nls, l_ecapp_nls);
1594
1595 -- set the security token
1596 iby_security_pkg.store_credential(l_url,l_sec_cred);
1597 iby_netutils_pvt.check_mandatory('OapfSecurityToken', TO_CHAR(l_sec_cred),
1598 l_url, l_db_nls, l_ecapp_nls);
1599
1600 --test_debug('SecureCardInfo=> full url: '|| l_url);
1601 iby_debug_pub.add(debug_msg => 'SecureCardInfo=> full url: '|| l_url,
1602 debug_level => FND_LOG.LEVEL_PROCEDURE,
1603 module => G_DEBUG_MODULE || '.SecureCardInfo');
1604 l_pos := INSTR(l_url,'?');
1605 l_post_body := SUBSTR(l_url,l_pos+1,length(l_url));
1606 l_post_body := RTRIM(l_post_body,'&');
1607 l_url := SUBSTR(l_url,1,l_pos-1);
1608 -- test_debug('SecureCardInfo=> url after stripping: '|| l_url);
1609 -- test_debug('SecureCardInfo=> post body: '|| l_post_body);
1610
1611
1612 IBY_NETUTILS_PVT.POST_REQUEST(l_url,l_post_body,l_html);
1613
1614 -- Unpack the results
1615 IBY_NETUTILS_PVT.UNPACK_RESULTS_URL(l_html,l_names,l_values, l_status, l_errcode, l_errmessage);
1616
1617
1618 --Raising Exception to handle errors in unpacking resulting html file.
1619 IF (l_status = -1) THEN
1620 --test_debug('unpack error !!');
1621 iby_debug_pub.add(debug_msg => 'Unpack status error; HTML resp. invalid!',
1622 debug_level => FND_LOG.LEVEL_UNEXPECTED,
1623 module => G_DEBUG_MODULE || '.SecureCardInfo');
1624 FND_MESSAGE.SET_NAME('IBY', 'IBY_204403_HTML_UNPACK_ERROR');
1625 FND_MSG_PUB.Add;
1626 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1627 END IF;
1628
1629 --Raising Exception to handle Servlet related errors.
1630 IF (l_names.COUNT = 0) THEN
1631 --test_debug('response count is 0 !!');
1632 iby_debug_pub.add(debug_msg => 'HTML response names count=0',
1633 debug_level => FND_LOG.LEVEL_UNEXPECTED,
1634 module => G_DEBUG_MODULE || '.SecureCardInfo');
1635 FND_MESSAGE.SET_NAME('IBY', 'IBY_204402_JSERVLET_ERROR');
1636 FND_MSG_PUB.Add;
1637 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1638 END IF;
1639
1640 /* Retrieve name-value pairs stored in l_names and l_values, and assign
1641 them to the output record: x_reqresp_rec.
1642 */
1643 --test_debug('Setting fields from unpacked response');
1644 iby_debug_pub.add(debug_msg => 'Setting fields from unpacked response',
1645 debug_level => FND_LOG.LEVEL_STATEMENT,
1646 module => G_DEBUG_MODULE || '.SecureCardInfo');
1647
1648
1649 FOR i IN 1..l_names.COUNT LOOP
1650 --Payment Server Related Generic Response
1651 IF l_names(i) = 'OapfStatus' THEN
1652 x_resp_rec.Response.Status := TO_NUMBER(l_values(i));
1653 iby_debug_pub.add(debug_msg => 'Response status=' || x_resp_rec.Response.Status,
1654 debug_level => FND_LOG.LEVEL_STATEMENT,
1655 module => G_DEBUG_MODULE || '.SecureCardInfo');
1656 --test_debug('OapfStatus: '||x_resp_rec.Response.Status);
1657 ELSIF l_names(i) = 'OapfCode' THEN
1658 x_resp_rec.Response.ErrCode := l_values(i);
1659 iby_debug_pub.add(debug_msg => 'Response code=' || x_resp_rec.Response.ErrCode,
1660 debug_level => FND_LOG.LEVEL_STATEMENT,
1661 module => G_DEBUG_MODULE || '.SecureCardInfo');
1662 --test_debug('OapfCode: '||x_resp_rec.Response.ErrCode);
1663 ELSIF l_names(i) = 'OapfCause' THEN
1664 x_resp_rec.Response.ErrMessage := l_values(i);
1665 iby_debug_pub.add(debug_msg => 'Response message=' || x_resp_rec.Response.ErrMessage,
1666 debug_level => FND_LOG.LEVEL_STATEMENT,
1667 module => G_DEBUG_MODULE || '.SecureCardInfo');
1668 --test_debug('OapfCause: '||x_resp_rec.Response.ErrMessage);
1669 ELSIF l_names(i) = 'OapfNlsLang' THEN
1670 x_resp_rec.Response.NLS_LANG := l_values(i);
1671
1672 --SecureCardInfo Response Related Response
1673 ELSIF l_names(i) = 'OapfExpSegmentId' THEN
1674 x_resp_rec.ExpiryDateSegmentId := TO_NUMBER(l_values(i));
1675 --test_debug('OapfExpSegmentId: '||x_resp_rec.ExpiryDateSegmentId);
1676 ELSIF l_names(i) = 'OapfChnameSegmentId' THEN
1677 x_resp_rec.ChnameSegmentId := TO_NUMBER(l_values(i));
1678 --test_debug('OapfChnameSegmentId: '||x_resp_rec.ChnameSegmentId);
1679 END IF;
1680
1681 END LOOP;
1682
1683 -- Use for Debugging
1684 --dbms_output.put_line('after successfully mapping results');
1685
1686 -- Standard check of p_commit.
1687 /*
1688 IF FND_API.To_Boolean( p_commit ) THEN
1689 COMMIT WORK;
1690 END IF;
1691 */
1692
1693 -- Standard call to get message count and if count is 1, get message info.
1694 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1695 p_data => x_msg_data
1696 );
1697
1698 iby_debug_pub.add(debug_msg => 'x_return_status=' || x_return_status,
1699 debug_level => FND_LOG.LEVEL_STATEMENT,
1700 module => G_DEBUG_MODULE || '.SecureCardInfo');
1701 iby_debug_pub.add(debug_msg => 'req response status=' || x_resp_rec.Response.Status,
1702 debug_level => FND_LOG.LEVEL_STATEMENT,
1703 module => G_DEBUG_MODULE || '.SecureCardInfo');
1704
1705 iby_debug_pub.add(debug_msg => 'Exit',
1706 debug_level => FND_LOG.LEVEL_PROCEDURE,
1707 module => G_DEBUG_MODULE || '.SecureCardInfo');
1708 --test_debug('Exit*******');
1709
1710 EXCEPTION
1711
1712 WHEN FND_API.G_EXC_ERROR THEN
1713
1714 iby_debug_pub.add(debug_msg => 'In G_EXC_ERROR Exception',
1715 debug_level => FND_LOG.LEVEL_ERROR,
1716 module => G_DEBUG_MODULE || '.SecureCardInfo');
1717 --ROLLBACK TO OraPmtReq_PUB;
1718 x_return_status := FND_API.G_RET_STS_ERROR ;
1719 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1720 p_data => x_msg_data
1721 );
1722 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1723
1724 iby_debug_pub.add(debug_msg => 'In G_EXC_UNEXPECTED_ERROR Exception',
1725 debug_level => FND_LOG.LEVEL_UNEXPECTED,
1726 module => G_DEBUG_MODULE || '.SecureCardInfo');
1727 --ROLLBACK TO OraPmtReq_PUB;
1728 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1729 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1730 p_data => x_msg_data
1731 );
1732 WHEN OTHERS THEN
1733
1734 iby_debug_pub.add(debug_msg => 'In OTHERS Exception',
1735 debug_level => FND_LOG.LEVEL_UNEXPECTED,
1736 module => G_DEBUG_MODULE || '.SecureCardInfo');
1737 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1738 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1739 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_api_name);
1740 END IF;
1741
1742 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1743 p_data => x_msg_data
1744 );
1745
1746 iby_debug_pub.add(debug_msg => 'x_return_status=' || x_return_status,
1747 debug_level => FND_LOG.LEVEL_UNEXPECTED,
1748 module => G_DEBUG_MODULE || '.SecureCardInfo');
1749 iby_debug_pub.add(debug_msg => 'Exit Exception',
1750 debug_level => FND_LOG.LEVEL_UNEXPECTED,
1751 module => G_DEBUG_MODULE || '.SecureCardInfo');
1752
1753 END SecureCardInfo;
1754
1755
1756 END IBY_INSTRREG_PUB;