1 PACKAGE BODY iby_paymentmanagerdb_pkg AS
2 /* $Header: ibypmmgb.pls 120.10.12010000.2 2008/07/29 05:51:53 sugottum ship $ */
3
4
5 G_DEBUG_MODULE CONSTANT VARCHAR2(100):='iby.plsql.IBY_PAYMENTMANAGERDB_PKG';
6
7
8 /* APIs to fetch the bep configuration for the given */
9
10
11 --IN: 1-15
12 --OUT: 12-28
13
14 PROCEDURE listbep (
15 p_amount IN iby_trxn_summaries_all.amount%type,
16 p_payment_channel_code IN iby_trxn_summaries_all.payment_channel_code%type,
17 p_currency IN iby_trxn_summaries_all.currencynamecode%type,
18 p_payee_id IN iby_trxn_summaries_all.payeeid%type,
19 p_cc_type IN VARCHAR2,
20 p_cc_num IN iby_creditcard.ccnumber%type,
21 p_aba_routing_no IN iby_bankacct.routingno%type,
22 p_org_id IN iby_trxn_summaries_all.org_id%type,
23 p_fin_app_type IN VARCHAR2,
24 p_transaction_id_in IN iby_trxn_summaries_all.TransactionID%TYPE,
25 p_payment_operation_in IN VARCHAR2,
26 p_ecappid_in IN iby_ecapp.ecappid%type,
27 p_instr_subtype IN iby_trxn_summaries_all.instrsubtype%type,
28 p_bnf_routing_no IN iby_bankacct.routingno%type,
29 p_factored_flag IN iby_trxn_summaries_all.factored_flag%type,
30 p_int_bank_acct_id IN NUMBER,
31 p_br_signed_flag IN iby_trxn_summaries_all.br_signed_flag%TYPE,
32 p_br_drawee_issued_flag IN iby_trxn_summaries_all.br_drawee_issued_flag%TYPE,
33 p_ar_receipt_mth_id IN iby_trxn_summaries_all.ar_receipt_method_id%TYPE,
34 px_payee_id_in_out IN OUT NOCOPY iby_payee.PayeeID%TYPE,
35 px_order_id_in_out IN OUT NOCOPY VARCHAR2,
36 px_payment_name_in_out IN OUT NOCOPY VARCHAR2,
37 px_bep_lang_in_out IN OUT NOCOPY VARCHAR2,
38 x_payee_username_out OUT NOCOPY iby_payee.Username%TYPE,
39 x_payee_passwd_out OUT NOCOPY iby_payee.Password%TYPE,
40 x_payee_operation_out OUT NOCOPY NUMBER,
41 x_bepid_out OUT NOCOPY iby_BEPInfo.BEPID%TYPE,
42 x_bep_suffix_out OUT NOCOPY IBY_BEPInfo.Suffix%TYPE,
43 x_bep_url_out OUT NOCOPY IBY_BEPInfo.BaseURL%TYPE,
44 x_bep_key_out OUT NOCOPY IBY_BEPKeys.Key%TYPE,
45 x_bep_pmtscheme_out OUT NOCOPY iby_pmtschemes.PmtSchemeName%TYPE,
46 x_bep_username_out OUT NOCOPY IBY_BEPInfo.BEPUsername%TYPE,
47 x_bep_passwd_out OUT NOCOPY IBY_BEPInfo.BEPPassword%TYPE,
48 x_security_out OUT NOCOPY NUMBER,
49 x_setnoinit_flag_out OUT NOCOPY NUMBER,
50 x_lead_time_out OUT NOCOPY iby_bepinfo.leadtime%TYPE,
51 x_bep_type_out OUT NOCOPY IBY_BEPInfo.Bep_Type%TYPE,
52 x_fndcpt_user_profile_code_out OUT NOCOPY
53 IBY_FNDCPT_USER_CC_PF_VL.USER_CC_PROFILE_CODE%TYPE
54 )
55 IS
56 BEGIN
57 listbep (
58 p_amount,
59 p_payment_channel_code,
60 p_currency,
61 p_payee_id,
62 p_cc_type,
63 p_cc_num,
64 p_aba_routing_no,
65 p_org_id,
66 p_fin_app_type,
67 p_transaction_id_in,
68 p_payment_operation_in,
69 p_ecappid_in,
70 p_instr_subtype,
71 p_bnf_routing_no,
72 null,
73 p_factored_flag,
74 p_int_bank_acct_id,
75 p_br_signed_flag,
76 p_br_drawee_issued_flag,
77 p_ar_receipt_mth_id,
78 px_payee_id_in_out,
79 px_order_id_in_out,
80 px_payment_name_in_out,
81 px_bep_lang_in_out,
82 x_payee_username_out,
83 x_payee_passwd_out,
84 x_payee_operation_out,
85 x_bepid_out,
86 x_bep_suffix_out,
87 x_bep_url_out,
88 x_bep_key_out,
89 x_bep_pmtscheme_out,
90 x_bep_username_out,
91 x_bep_passwd_out,
92 x_security_out,
93 x_setnoinit_flag_out,
94 x_lead_time_out,
95 x_bep_type_out,
96 x_fndcpt_user_profile_code_out);
97
98 END listbep;
99
100 PROCEDURE listbep (
101 p_amount IN iby_trxn_summaries_all.amount%type,
102 p_payment_channel_code IN iby_trxn_summaries_all.payment_channel_code%type,
103 p_currency IN iby_trxn_summaries_all.currencynamecode%type,
104 p_payee_id IN iby_trxn_summaries_all.payeeid%type,
105 p_cc_type IN VARCHAR2,
106 p_cc_num IN iby_creditcard.ccnumber%type,
107 p_aba_routing_no IN iby_bankacct.routingno%type,
108 p_org_id IN iby_trxn_summaries_all.org_id%type,
109 p_fin_app_type IN VARCHAR2,
110 p_transaction_id_in IN iby_trxn_summaries_all.TransactionID%TYPE,
111 p_payment_operation_in IN VARCHAR2,
112 p_ecappid_in IN iby_ecapp.ecappid%type,
113 p_instr_subtype IN iby_trxn_summaries_all.instrsubtype%type,
114 p_bnf_routing_no IN iby_bankacct.routingno%type,
115 p_merchant_bank_country IN VARCHAR2,
116 p_factored_flag IN iby_trxn_summaries_all.factored_flag%type,
117 p_int_bank_acct_id IN NUMBER,
118 p_br_signed_flag IN iby_trxn_summaries_all.br_signed_flag%TYPE,
119 p_br_drawee_issued_flag IN iby_trxn_summaries_all.br_drawee_issued_flag%TYPE,
120 p_ar_receipt_mth_id IN iby_trxn_summaries_all.ar_receipt_method_id%TYPE,
121 px_payee_id_in_out IN OUT NOCOPY iby_payee.PayeeID%TYPE,
122 px_order_id_in_out IN OUT NOCOPY VARCHAR2,
123 px_payment_name_in_out IN OUT NOCOPY VARCHAR2,
124 px_bep_lang_in_out IN OUT NOCOPY VARCHAR2,
125 x_payee_username_out OUT NOCOPY iby_payee.Username%TYPE,
126 x_payee_passwd_out OUT NOCOPY iby_payee.Password%TYPE,
127 x_payee_operation_out OUT NOCOPY NUMBER,
128 x_bepid_out OUT NOCOPY iby_BEPInfo.BEPID%TYPE,
129 x_bep_suffix_out OUT NOCOPY IBY_BEPInfo.Suffix%TYPE,
130 x_bep_url_out OUT NOCOPY IBY_BEPInfo.BaseURL%TYPE,
131 x_bep_key_out OUT NOCOPY IBY_BEPKeys.Key%TYPE,
132 x_bep_pmtscheme_out OUT NOCOPY iby_pmtschemes.PmtSchemeName%TYPE,
133 x_bep_username_out OUT NOCOPY IBY_BEPInfo.BEPUsername%TYPE,
134 x_bep_passwd_out OUT NOCOPY IBY_BEPInfo.BEPPassword%TYPE,
135 x_security_out OUT NOCOPY NUMBER,
136 x_setnoinit_flag_out OUT NOCOPY NUMBER,
137 x_lead_time_out OUT NOCOPY iby_bepinfo.leadtime%TYPE,
138 x_bep_type_out OUT NOCOPY IBY_BEPInfo.Bep_Type%TYPE,
139 x_fndcpt_user_profile_code_out OUT NOCOPY
140 IBY_FNDCPT_USER_CC_PF_VL.USER_CC_PROFILE_CODE%TYPE
141 )
142 IS
143 l_api_name CONSTANT VARCHAR2(30) := 'listbep';
144 l_module_name CONSTANT VARCHAR2(200) := G_DEBUG_MODULE || '.' ||
145 l_api_name;
146 l_bepid iby_default_bep.bepid%TYPE;
147 l_base_url iby_bepinfo.baseurl%TYPE;
148 l_pay_op VARCHAR2(100);
149 l_routing_fields RoutingAPIFields_rec_type;
150
151 l_currency VARCHAR2(15);
152 l_position NUMBER;
153
154 l_payeename iby_payee.name%type;
155 l_bepname iby_bepinfo.name%type;
156
157 -- *** R12 Modification *** ---
158 -- Added variables l_instr_type and l_mpayeeid
159 l_instr_type iby_trxn_summaries_all.instrtype%TYPE;
160 l_mpayeeid iby_payee.mpayeeid%TYPE;
161 l_bank_id ce_bank_accounts_v.bank_id%TYPE;
162
163 BEGIN
164 iby_debug_pub.add('ENTER',iby_debug_pub.G_LEVEL_PROCEDURE,l_module_name);
165
166 l_position := -1;
167 x_bepid_out := -1;
168
169 -- need perform this operation because inv/pay doesn't come in upper case
170 l_pay_op := UPPER(p_payment_operation_in);
171
172 -- basic error checking
173 -- Reject any illegal payment operation
174 checkPaymentOperation(l_pay_op);
175
176 ---Obtain BEP information by
177 --- 1) If a valid routingrule is supplied by user, use it (mostly used
178 --- by BATCH operations, ORAPAY/ORAINV
179 --- 2) Applying routing for ORAPMTREQ and ORAPMTCREDIT, or
180 --- 3) For Batch/ORAINV/ORAPAY operations,
181 --- fetch based on routing rule name
182 --- (pxp_ayment_name_in_out), use default bep for CC if not set
183 --- 4) For others, retrieve previous stored bepid by trxnid for other
184 --- operations, must have valid input trxnid
185 IF ((l_pay_op = 'ORAPMTREQ') OR
186 (l_pay_op = 'ORAPMTCREDIT') OR
187 (l_pay_op = 'ORAPMTCLOSEBATCH') OR
188 (l_pay_op = 'ORAPMTQRYBATCHSTATUS') OR
189 (l_pay_op = 'ORAINV') OR
190 (l_pay_op = 'ORAPMTBATCHREQ') OR
191 (l_pay_op = 'ORAPAY')) THEN
192 IF (px_payment_name_in_out IS NULL) THEN
193 -- we don't have a valid routing rule from user input!
194 -- need get bepid
195 --- *** R12 Modification *** ---
196 -- Fetch Instrument Type from Payment Channel Code
197 SELECT a.INSTRUMENT_TYPE INTO l_instr_type
198 FROM IBY_FNDCPT_ALL_PMT_CHANNELS_V a
199 WHERE a.PAYMENT_CHANNEL_CODE = p_payment_channel_code;
200
201 iby_debug_pub.add('INSTRUMENT_TYPE = '||l_instr_type,
202 iby_debug_pub.G_LEVEL_INFO,l_module_name);
203
204 --- *** R12 Modification *** ---
205 -- Fetch MPayeeId from PayeeId
206 SELECT a.mpayeeid INTO l_mpayeeid
207 FROM IBY_PAYEE a
208 WHERE a.payeeid = p_payee_id;
209 iby_debug_pub.add('debug pt 6',iby_debug_pub.G_LEVEL_PROCEDURE,l_module_name);
210 IF (NOT p_int_bank_acct_id IS NULL) THEN
211 SELECT bank_id INTO l_bank_id
212 FROM ce_bank_accounts_v
213 WHERE (bank_account_id = p_int_bank_acct_id);
214 END IF;
215 iby_debug_pub.add('debug pt 1',iby_debug_pub.G_LEVEL_PROCEDURE,l_module_name);
216 --- *** R12 Note *** ---
217 -- 1. The Instrument Type obtained above is used to populate the Routing fields and
218 -- evaluate the applicable Routing Rules, as in the earlier implementation
219 --- *** ---
220 --- *** R12 Modification *** ---
221 -- Parameter 'p_factored_flag' added
222 populateRoutingFields(p_amount, l_instr_type, p_instr_subtype, p_currency, p_payee_id,
223 p_cc_type, p_cc_num, p_aba_routing_no, p_bnf_routing_no, p_org_id,
224 p_fin_app_type,p_merchant_bank_country,p_factored_flag,l_routing_fields);
225 l_routing_fields.int_bank_id := l_bank_id;
226 l_routing_fields.int_bank_acct_id := p_int_bank_acct_id;
227 l_routing_fields.ar_receipt_method_id := p_ar_receipt_mth_id;
228 l_routing_fields.br_drawee_issued_flag := p_br_drawee_issued_flag;
229 l_routing_fields.br_signed_flag := p_br_signed_flag;
230
231 iby_debug_pub.add('debug pt 2',iby_debug_pub.G_LEVEL_PROCEDURE,l_module_name);
232 IF ((l_pay_op = 'ORAPMTREQ') OR (l_pay_op = 'ORAPMTCREDIT') OR (l_pay_op = 'ORAPMTBATCHREQ')) THEN
233
234 IF (not iby_payee_pkg.payeeExists(p_ecappid_in, p_payee_id)) THEN
235 raise_application_error(-20000, 'IBY_20515#', FALSE);
236 END IF; -- If payee does not exist
237 iby_debug_pub.add('debug pt 3',iby_debug_pub.G_LEVEL_PROCEDURE,l_module_name);
238 -- Apply routingrules and find the routing rule (pmt
239 -- method name) we should be using based on
240 -- amount, instrument type, not applicable to batch ops
241 -- getPmtName(payment_name_in_out, amount_in, l_instr_type, currency_in);
242 getPmtName(l_routing_fields,
243 px_payment_name_in_out);
244 END IF;
245 --dbms_output.put_line(SubStr('px_payment_name_in_out = '||px_payment_name_in_out,1,255));
246 --- *** R12 Modification *** ---
247 -- Procedure 'getBepIdByPmtName' gets the FndCptUserProfileCode.
248 IF (px_payment_name_in_out IS NOT NULL ) THEN
249 -- for ORAPMTREQ and ORAPMTCREDIT only, routing logic
250 -- has come up w/ some rule that fits
251 -- fetch the bepid that specified by the routing rule
252 getBepIdByPmtName(px_payment_name_in_out,
253 px_payee_id_in_out,
254 x_bepid_out,
255 x_bep_key_out,
256 x_fndcpt_user_profile_code_out);
257 -- now we have bep id from routing
258 iby_debug_pub.add('debug pt 4',iby_debug_pub.G_LEVEL_PROCEDURE,l_module_name);
259 ELSE
260 -- for ORAPMTCLOSEBATCH, ORAPMTQRYBATCHSTATUS, where rule
261 -- is not specified OR
262 -- no rule match case for ORAPMTREQ and ORAPMTCREDIT
263 -- still no applicable routing rule, go for default
264 -- Find default BEPID
265 l_position := 1;
266 --- *** R12 Modification *** ---
267 -- Given the MPayeeId and Payment Channel Code, getDefaultBepId
268 -- returns the BepAccountId and the FndCptUserProfileCode.
269 getDefaultBepId(l_mpayeeid,
270 p_payment_channel_code,
271 x_bepid_out,
272 x_bep_key_out,
273 x_fndcpt_user_profile_code_out);
274
275 IF ( x_bepid_out = -1) THEN
276 -- Default BEP has not been configured
277 raise_application_error(-20000, 'IBY_25001#', FALSE);
278 END IF;
279
280 IF ( x_bep_key_out IS NULL) THEN
281 -- Default BEP Key has not been configured
282 SELECT p.name INTO l_payeename FROM iby_payee p
283 WHERE px_payee_id_in_out = p.payeeid;
284 SELECT b.name INTO l_bepname FROM iby_bepinfo b
285 WHERE x_bepid_out = b.bepid;
286 raise_application_error(-20000, 'IBY_25002#PAYEENAME='
287 || l_payeename || '#BEPNAME='
288 || l_bepname || '#', FALSE);
289 END IF;
290 iby_debug_pub.add('debug pt 5',iby_debug_pub.G_LEVEL_PROCEDURE,l_module_name);
291 -- now we have default bep id
292 END IF;
293 END IF;
294 --- *** R12 Note *** ---
295 -- The lines below retrieve the Bep Information to populate the output parameters.
296 -- Though this is not used anymore, there is no need to change it.
297 --- *** --
298 -- everything is now can be retrieved based on bepid
299 getBepById(x_bepid_out,
300 x_bep_suffix_out,
301 l_base_url,
302 x_security_out,
303 x_bep_username_out,
304 x_bep_passwd_out,
305 x_bep_type_out,
306 x_lead_time_out);
307 -- getPmtSchemeName(x_bepid_out, l_instr_type, x_bep_pmtscheme_out);
308 getPmtSchemeName(x_bepid_out,
309 l_routing_fields.instr_type,
310 x_bep_pmtscheme_out);
311
312 ELSE -- Operation Types
313 --dbms_output.put_line(SubStr('From Transaction : '||p_transaction_id_in,1,255));
314 -- all other transactions should be non-batch operations,
315 -- follow-ups to ORAPMTREQ, ORAPMTCREDIT,
316 -- they should have a valid input trxnid, and have been previously
317 -- routed! we just need fetch previously routed bep info!
318 IF (p_transaction_id_in IS NULL or p_transaction_id_in < 0) THEN
319 raise_application_error(-20000, 'IBY_20528#', FALSE);
320 END IF;
321 -- routing rule is ignored for follow-up trxns
322 px_payment_name_in_out := NULL;
323
324 l_position := 3;-- possible non-existent tid
325 -- get related bep, tangible information
326
327 --- *** R12 Modification *** ---
328 -- Added fndcpt_user_profile_code to select Statement below
329 --- *** --
330 SELECT distinct BEPID,a.TangibleID, PayeeID, b.currencynamecode,
331 instrtype, bepkey, PROCESS_PROFILE_CODE
332 INTO x_bepid_out,px_order_id_in_out, px_payee_id_in_out, l_currency,
333 --
334 -- unfortunately pmt instr type is not passed for
335 -- follow-on trxns so it must also be fetched from the DB
336 -- [bug # 1925098]
337 --
338 l_routing_fields.instr_type, x_bep_key_out,
339 x_fndcpt_user_profile_code_out
340 FROM iby_trxn_summaries_all a, iby_tangible b
341 WHERE TransactionID = p_transaction_id_in
342 AND a.mtangibleid = b.mtangibleid
343 -- previously must have succeeded ones
344 -- 100 is for processors, indicating it is in an open batch
345 -- Added the conditions for Bankaccount
346 AND ( ((NOT instrtype IS NULL) AND status in (0, 11, 100, 9)) OR ((instrtype = 'BANKACCOUNT') AND status <> -99) );
347
348
349 --AND status <> -99 -- cancelled
350 --AND status <> 14; -- cancelled
351
352 -- we won't allow user to change the currency code in follow-on
353 -- transactions such as CAPTURE and RETURN
354 IF (l_pay_op = 'ORAPMTCAPTURE'
355 or l_pay_op = 'ORAPMTRETURN') THEN
356 IF (UPPER(l_currency) <> UPPER(l_routing_fields.currency)) THEN
357 raise_application_error(-20000, 'IBY_204462#CURRENT='
358 || l_routing_fields.currency || '#OLD=' ||
359 l_currency || '#' , FALSE);
360 END IF;
361 END IF;
362 --- *** R12 Note *** ---
363 -- The lines below retrieve the Bep Information to populate the output parameters.
364 -- Though this is not used anymore, there is no need to change it.
365 --- *** --
366 getBepById(x_bepid_out,
367 x_bep_suffix_out,
368 l_base_url,
369 x_security_out,
370 x_bep_username_out,
371 x_bep_passwd_out,
372 x_bep_type_out,
373 x_lead_time_out);
374 -- getPmtSchemeName(x_bepid_out, l_instr_type, x_bep_pmtscheme_out);
375 getPmtSchemeName(x_bepid_out,
376 l_routing_fields.instr_type,
377 x_bep_pmtscheme_out);
378 END IF;
379
380 IF ((l_pay_op = 'ORAPMTREQ') AND
381 (x_bep_pmtscheme_out = 'SET')) THEN
382 -- It's a special oraset_set not preceded by oraset_init,
383 -- so get bep by the payment name just like for
384 -- oraset_init, but set the setnoinit_flag to 1
385 x_setnoinit_flag_out := 1;
386 END IF;
387
388 -- NLS modification : get language information
389 getBEPLang(x_bepid_out, px_bep_lang_in_out);
390
391 -- Construct bep URL based on baseurl
392 getBepUrl(l_base_url,
393 l_pay_op,
394 x_bep_pmtscheme_out,
395 x_bep_suffix_out,
396 x_bep_url_out);
397
398 -- Get the payee name to set for OapfStoreId unless
399 -- it's an orapay for SSL, in which case we don't need it
400 IF (px_payee_id_in_out IS NOT NULL) THEN
401 -- Get payee info
402 l_position := 6;
403 -- hard coded the supportedOp as this feature is not supported
404 -- the value 2147483647 is the maximum integer value as set in the UI
405 SELECT Username, Password, '2147483647'
406 INTO x_payee_username_out, x_payee_passwd_out,
407 x_payee_operation_out
408 FROM iby_payee
409 WHERE PayeeID = px_payee_id_in_out
410 AND upper(Activestatus) = 'Y';
411
412 -- make sure payee support accepted payment instrument
413 checkPayeeByAccpPmtMthd(px_payee_id_in_out,
414 l_routing_fields.instr_type);
415
416 END IF;
417
418 -- we need some non-NULL parameter out
419 -- for default bep case or follow-up trxns, otherwise it will
420 -- crash in java layer
421 -- pl/sql layer (or jdbc) doesn't seem to allow empty string ('')
422 -- it becomes 'null' in java layer
423 IF (px_payment_name_in_out IS NULL) THEN
424 px_payment_name_in_out := ' ';
425 END IF;
426
427 EXCEPTION
428
429 WHEN NO_DATA_FOUND THEN
430 -- another possible error for SELECT ... INTO is:
431 -- multiple data found for all the select statement
432
433 IF (l_position = 1) THEN
434 --Default BEP has not been configured
435 raise_application_error(-20000, 'IBY_25001#', FALSE);
436 END IF;
437
438 IF (l_position = 3) THEN
439 -- Invalid transaction id
440 raise_application_error(-20000, 'IBY_20528#', FALSE);
441 END IF;
442
443 IF (l_position = 6) THEN
444 -- missing payee info, invalid payeeid
445 raise_application_error(-20000, 'IBY_20305#', FALSE);
446 END IF;
447
448 --Unknown no_data_found error
449 raise_application_error(-20000, 'IBY_20300#', FALSE);
450
451 END listbep;
452
453 PROCEDURE listbep (
454 p_payee_id IN iby_bepkeys.OwnerId%TYPE,
455 p_bepkey IN iby_bepkeys.KEY%TYPE,
456 p_instr_type IN iby_trxn_summaries_all.InstrType%TYPE,
457 px_bep_suffix_in_out IN OUT NOCOPY iby_bepinfo.Suffix%TYPE,
458 x_bepid_out OUT NOCOPY iby_bepinfo.BepId%TYPE,
459 x_bep_url_out OUT NOCOPY iby_bepinfo.BaseUrl%TYPE,
460 x_bep_pmtscheme_out OUT NOCOPY iby_pmtschemes.PmtSchemeName%TYPE,
461 x_bep_username_out OUT NOCOPY iby_bepinfo.BEPUsername%TYPE,
462 x_bep_passwd_out OUT NOCOPY iby_bepinfo.BEPPassword%TYPE,
463 x_security_out OUT NOCOPY NUMBER,
464 x_setnoinit_flag_out OUT NOCOPY NUMBER,
465 x_bep_type_out OUT NOCOPY iby_bepinfo.Bep_Type%TYPE,
466 x_bep_lang_out OUT NOCOPY VARCHAR2,
467 x_lead_time_out OUT NOCOPY iby_bepinfo.leadtime%TYPE
468 )
469 IS
470 l_op_type iby_trxn_summaries_all.reqtype%TYPE;
471 l_base_url IBY_BEPInfo.BaseURL%TYPE;
472 l_key_count NUMBER;
473
474 BEGIN
475 getBepBySuffix(px_bep_suffix_in_out,x_bepid_out,l_base_url,x_bep_pmtscheme_out,p_instr_type);
476 getBepById(x_bepid_out, px_bep_suffix_in_out, l_base_url, x_security_out, x_bep_username_out,
477 x_bep_passwd_out, x_bep_type_out, x_lead_time_out);
478 --
479 -- base empty string for payment operation, since invoice/pay is not supported any
480 -- longer
481 --
482 getBepUrl(l_base_url, '', x_bep_pmtscheme_out, px_bep_suffix_in_out, x_bep_url_out);
483 getBEPLang(x_bepid_out, x_bep_lang_out);
484
485 --
486 -- do not modify setnoninit_flag as it is used only for the SET protocol
487
488 SELECT count(Key)
489 INTO l_key_count
490 FROM iby_bepkeys
491 WHERE (OwnerId = p_payee_id) AND (BEPId = x_bepid_out) AND (Key = p_bepkey);
492 --
493 -- safeguard from the user providing a wrong bep key
494 --
495 IF (l_key_count <> 1) THEN
496 raise_application_error(-20000, 'IBY_20532#PAYEEID='||p_payee_id||'#BEPNAME='||px_bep_suffix_in_out, FALSE);
497 END IF;
498
499 IF (p_instr_type = 'BANKACCOUNT') THEN
500 l_op_type := 'ORAPMTEFTCLOSEBATCH';
501 ELSIF (p_instr_type = 'BANKPAYMENT') THEN
502 l_op_type := 'ORAPMTEFTPCLOSEBATCH';
503 ELSIF (p_instr_type = 'CREDITCARD') THEN
504 l_op_type := 'ORAPMTCLOSEBATCH';
505 END IF;
506
507 END listbep;
508
509
510
511 /* Internal procedure that finds the pmt method name using routing rules */
512 PROCEDURE getPmtName(
513 p_routingAPIfields IN RoutingAPIFields_rec_type,
514 px_pmt_name_in_out IN OUT NOCOPY VARCHAR2
515 )
516
517 IS
518 err_msg VARCHAR2(100);
519 l_flag NUMBER := 0;
520 l_hitCounter NUMBER;
521 l_payeeid VARCHAR2(100);
522
523 --- *** R12 Note *** ---
524 -- Though Payment Channel Code will be the driving parameter in the Procedure
525 -- listbep, keeping the the instrument type and instrument subtype as the driving
526 -- parameters in the main query below
527 --- *** --
528 CURSOR c_routingRules (p_payeeid iby_routinginfo.payeeid%TYPE,
529 p_instrtype iby_routinginfo.instr_type%TYPE,
530 p_instr_subtype iby_routinginfo.instr_sub_type%TYPE) IS
531 SELECT a.paymentmethodname, a.paymentmethodid FROM iby_routinginfo a
532 WHERE a.configured = 1
533 -- Once financing supports multiple payees, the payee condition should
534 -- be changed from 'like' back to '='
535 -- AND a.payeeid = p_payeeid
536 AND a.payeeid like p_payeeid
537 AND a.instr_type = p_instrtype
538 AND NVL(a.instr_sub_type, ' ') = NVL(p_instr_subtype, ' ')
539 ORDER BY a.priority;
540
541 CURSOR c_ruleCondt(p_key iby_routinginfo.paymentmethodid%TYPE) IS
542 SELECT * FROM iby_pmtmthd_conditions x
543 WHERE x.paymentmethodid = p_key order by x.entry_sequence ;
544
545 BEGIN
546 -- Get bep information for this order for this payee
547
548 -- set payee to '%' if instrument type is financing. This is only until
549 -- financing supports multiple payees.
550 IF (p_routingAPIfields.instr_type = 'FINANCING') THEN
551 l_payeeid := '%';
552 ELSE
553 l_payeeid := p_routingAPIfields.payee_id;
554 END IF;
555
556 FOR v_routingRules IN c_routingRules(l_payeeid, p_routingAPIfields.instr_type, p_routingAPIfields.instr_subtype) LOOP
557 l_flag := 0;
558 FOR v_ruleCondt IN c_ruleCondt(v_routingRules.paymentmethodid) LOOP
559 IF (v_ruleCondt.parameter_code = 'AMOUNT') THEN
560 IF (v_ruleCondt.operation_code = 'EQ') THEN
561 IF (p_routingAPIfields.amount = v_ruleCondt.value) THEN
562 l_flag := 1;
563 ELSE
564 l_flag := 0; EXIT;
565 END IF; -- if p_routingAPIfields.amount = v_ruleCondt.value
566 END IF; -- if v_ruleCondt.operation_code = 'EQ'
567 IF (v_ruleCondt.operation_code = 'NE') THEN
568 IF (p_routingAPIfields.amount <> v_ruleCondt.value) THEN
569 l_flag := 1;
570 ELSE
571 l_flag := 0; EXIT;
572 END IF; -- if p_routingAPIfields.amount <> v_ruleCondt.value
573 END IF; -- if v_ruleCondt.operation_code = 'NE'
574 IF (v_ruleCondt.operation_code = 'LE') THEN
575 IF (p_routingAPIfields.amount <= v_ruleCondt.value) THEN
576 l_flag := 1;
577 ELSE
578 l_flag := 0; EXIT;
579 END IF; -- if p_routingAPIfields.amount <= v_ruleCondt.value
580 END IF; -- if v_ruleCondt.operation_code = 'LE'
581 IF (v_ruleCondt.operation_code = 'LT') THEN
582 IF (p_routingAPIfields.amount < v_ruleCondt.value) THEN
583 l_flag := 1;
584 ELSE
585 l_flag := 0; EXIT;
586 END IF; -- if p_routingAPIfields.amount < v_ruleCondt.value
587 END IF; -- if v_ruleCondt.operation_code = 'LT'
588 IF (v_ruleCondt.operation_code = 'GE') THEN
589 IF (p_routingAPIfields.amount >= v_ruleCondt.value) THEN
590 l_flag := 1;
591 ELSE
592 l_flag := 0; EXIT;
593 END IF; -- if p_routingAPIfields.amount >= v_ruleCondt.value
594 END IF; -- if v_ruleCondt.operation_code = 'GE'
595 IF (v_ruleCondt.operation_code = 'GT') THEN
596 IF (p_routingAPIfields.amount > v_ruleCondt.value) THEN
597 l_flag := 1;
598 ELSE
599 l_flag := 0; EXIT;
600 END IF; -- if p_routingAPIfields.amount > v_ruleCondt.value
601 END IF; -- if v_ruleCondt.operation_code = 'GT'
602
603 --
604 -- adds routing based on currency where the parameter code
605 -- is "CURR" and the operators are "EQ" , "NE"
606 --
607 ELSIF (v_ruleCondt.parameter_code = 'CURR') THEN
608 IF (v_ruleCondt.operation_code = 'EQ') THEN
609 -- currency codes should be treated case insensitive
610 IF (UPPER(p_routingAPIfields.currency) = UPPER(v_ruleCondt.value)) THEN
611 l_flag := 1;
612 ELSE
613 l_flag := 0; EXIT;
614 END IF; -- if p_routingAPIfields.currency = v_ruleCondt.value
615 END IF; -- if v_ruleCondt.operation_code = 'EQ'
616 IF (v_ruleCondt.operation_code = 'NE') THEN
617 IF (UPPER(p_routingAPIfields.currency) <> UPPER(v_ruleCondt.value)) THEN
618 l_flag := 1;
619 ELSE
620 l_flag := 0; EXIT;
621 END IF; -- if p_routingAPIfields.currency <> v_ruleCondt.value
622 END IF; -- if v_ruleCondt.operation_code = 'NE'
623
624 ELSIF (v_ruleCondt.parameter_code = 'CC_TYPE') THEN
625 IF (v_ruleCondt.operation_code = 'EQ') THEN
626 IF (UPPER(p_routingAPIfields.cc_type) = UPPER(v_ruleCondt.value))
627 THEN
628 l_flag := 1;
629 ELSE
630 l_flag := 0; EXIT;
631 END IF; -- if p_routingAPIfields.cc_type = v_ruleCondt.value
632 ELSIF (v_ruleCondt.operation_code = 'NE') THEN
633 IF (UPPER(p_routingAPIfields.cc_type) <> UPPER(v_ruleCondt.value)) THEN
634 l_flag := 1;
635 ELSE
636 l_flag := 0; EXIT;
637 END IF; -- if v_ruleCondt.value <> p_routingAPIfields.cc_type
638 END IF; -- if v_ruleCondt.value = operation_code = 'EQ'
639
640 ELSIF (v_ruleCondt.parameter_code = 'CC_NUM') THEN
641 IF (v_ruleCondt.operation_code = 'EQ') THEN
642 IF (UPPER(p_routingAPIfields.cc_num) LIKE UPPER(v_ruleCondt.value))
643 THEN
644 l_flag := 1;
645 ELSE
646 l_flag := 0; EXIT;
647 END IF; -- if p_routingAPIfields.cc_num = v_ruleCondt.value
648 ELSIF (v_ruleCondt.operation_code = 'NE') THEN
649 IF (UPPER(p_routingAPIfields.cc_num) LIKE UPPER(v_ruleCondt.value)) THEN
650 l_flag := 0; EXIT;
651 ELSE
652 l_flag := 1;
653 END IF; -- if v_ruleCondt.value <> p_routingAPIfields.cc_num
654 END IF; -- if v_ruleCondt.value = operation_code = 'EQ'
655
656 ELSIF (v_ruleCondt.parameter_code = 'ABA_ROUTING_NO') THEN
657 IF (v_ruleCondt.operation_code = 'EQ') THEN
658 IF (UPPER(p_routingAPIfields.aba_routing_no) = UPPER(v_ruleCondt.value))
659 THEN
660 l_flag := 1;
661 ELSE
662 l_flag := 0; EXIT;
663 END IF; -- if p_routingAPIfields.aba_routing_no = v_ruleCondt.value
664 ELSIF (v_ruleCondt.operation_code = 'NE') THEN
665 IF (UPPER(p_routingAPIfields.aba_routing_no) <> UPPER(v_ruleCondt.value)) THEN
666 l_flag := 1;
667 ELSE
668 l_flag := 0; EXIT;
669 END IF; -- if v_ruleCondt.value <> p_routingAPIfields.aba_routing_no
670 END IF; -- if v_ruleCondt.value = operation_code = 'EQ'
671
672 ELSIF (v_ruleCondt.parameter_code = 'ABA_ROUTING_NO_PY') THEN
673 IF (v_ruleCondt.operation_code = 'EQ') THEN
674 IF (UPPER(p_routingAPIfields.bnf_routing_no) = UPPER(v_ruleCondt.value))
675 THEN
676 l_flag := 1;
677 ELSE
678 l_flag := 0; EXIT;
679 END IF; -- if p_routingAPIfields.bnf_routing_no = v_ruleCondt.value
680 ELSIF (v_ruleCondt.operation_code = 'NE') THEN
681 IF (UPPER(p_routingAPIfields.bnf_routing_no) <> UPPER(v_ruleCondt.value)) THEN
682 l_flag := 1;
683 ELSE
684 l_flag := 0; EXIT;
685 END IF; -- if v_ruleCondt.value <> p_routingAPIfields.bnf_routing_no
686 END IF; -- if v_ruleCondt.value = operation_code
687
688 ELSIF (v_ruleCondt.parameter_code = 'ORG_ID') THEN
689 IF (v_ruleCondt.operation_code = 'EQ') THEN
690 IF (UPPER(p_routingAPIfields.org_id) = UPPER(v_ruleCondt.value))
691 THEN
692 l_flag := 1;
693 ELSE
694 l_flag := 0; EXIT;
695 END IF; -- if p_routingAPIfields.org_id = v_ruleCondt.value
696 ELSIF (v_ruleCondt.operation_code = 'NE') THEN
697 IF (UPPER(p_routingAPIfields.org_id) <> UPPER(v_ruleCondt.value)) THEN
698 l_flag := 1;
699 ELSE
700 l_flag := 0; EXIT;
701 END IF; -- if v_ruleCondt.value <> p_routingAPIfields.org_id
702 END IF; -- if v_ruleCondt.value = operation_code = 'EQ'
703
704 ELSIF (v_ruleCondt.parameter_code = 'APPLICATION_TYPE') THEN
705 IF (v_ruleCondt.operation_code = 'EQ') THEN
706 IF (UPPER(p_routingAPIfields.financing_app_type) =
707 UPPER(v_ruleCondt.value))
708 THEN
709 l_flag := 1;
710 ELSE
711 l_flag := 0; EXIT;
712 END IF; -- if p_routingAPIfields.financing_app_type = v_ruleCondt.value
713 ELSIF (v_ruleCondt.operation_code = 'NE') THEN
714 IF (UPPER(p_routingAPIfields.financing_app_type) <>
715 UPPER(v_ruleCondt.value)) THEN
716 l_flag := 1;
717 ELSE
718 l_flag := 0; EXIT;
719 END IF; -- if v_ruleCondt.value <> p_routingAPIfields.financing_app_type
720 END IF; -- if v_ruleCondt.value = operation_code = 'EQ'
721
722 ELSIF (v_ruleCondt.parameter_code = 'COUNTRY_PY' or v_ruleCondt.parameter_code = 'COUNTRY_PR') THEN
723 IF (v_ruleCondt.operation_code = 'EQ') THEN
724 IF (UPPER(p_routingAPIfields.merchant_bank_country) =
725 UPPER(v_ruleCondt.value))
726 THEN
727 l_flag := 1;
728 ELSE
729 l_flag := 0; EXIT;
730 END IF; -- if p_routingAPIfields.financing_app_type = v_ruleCondt.value
731 ELSIF (v_ruleCondt.operation_code = 'NE') THEN
732 IF (UPPER(p_routingAPIfields.merchant_bank_country) <>
733 UPPER(v_ruleCondt.value)) THEN
734 l_flag := 1;
735 ELSE
736 l_flag := 0; EXIT;
737 END IF; -- if v_ruleCondt.value <> p_routingAPIfields.financing_app_type
738 END IF; -- if v_ruleCondt.value = operation_code = 'EQ'
739
740 --- *** R12 Modification *** ---
741 -- Added parameter 'FACTOR_FLAG'
742 ELSIF (v_ruleCondt.parameter_code = 'FACTOR_FLAG') THEN
743 IF (v_ruleCondt.operation_code = 'EQ') THEN
744 IF (UPPER(p_routingAPIfields.factor_flag) =
745 UPPER(v_ruleCondt.value))
746 THEN
747 l_flag := 1;
748 ELSE
749 l_flag := 0; EXIT;
750 END IF; -- if p_routingAPIfields.payment_factor_flag = v_ruleCondt.value
751 ELSIF (v_ruleCondt.operation_code = 'NE') THEN
752 IF (UPPER(p_routingAPIfields.factor_flag) <>
753 UPPER(v_ruleCondt.value)) THEN
754 l_flag := 1;
755 ELSE
756 l_flag := 0; EXIT;
757 END IF;
758 END IF;
759 --
760 -- 1st party ("payee") bank account id
761 ELSIF (v_ruleCondt.parameter_code = 'PY_BANK_ACCOUNT') THEN
762 IF (v_ruleCondt.operation_code = 'EQ') THEN
763 IF (TO_CHAR(p_routingAPIfields.int_bank_acct_id) = v_ruleCondt.value)
764 THEN
765 l_flag := 1;
766 ELSE
767 l_flag := 0; EXIT;
768 END IF;
769 ELSIF (v_ruleCondt.operation_code = 'NE') THEN
770 IF (TO_CHAR(p_routingAPIfields.int_bank_acct_id) <> v_ruleCondt.value)
771 THEN
772 l_flag := 1;
773 ELSE
774 l_flag := 0; EXIT;
775 END IF;
776 END IF;
777 ELSIF (v_ruleCondt.parameter_code = 'PY_BANK') THEN
778 IF (v_ruleCondt.operation_code = 'EQ') THEN
779 IF (TO_CHAR(p_routingAPIfields.int_bank_id) = v_ruleCondt.value)
780 THEN
781 l_flag := 1;
782 ELSE
783 l_flag := 0; EXIT;
784 END IF;
785 ELSIF (v_ruleCondt.operation_code = 'NE') THEN
786 IF (TO_CHAR(p_routingAPIfields.int_bank_id) <> v_ruleCondt.value)
787 THEN
788 l_flag := 1;
789 ELSE
790 l_flag := 0; EXIT;
791 END IF;
792 END IF;
793 ELSIF (v_ruleCondt.parameter_code = 'AR_RECEIPT_METHOD_ID') THEN
794 IF (v_ruleCondt.operation_code = 'EQ') THEN
795 IF (p_routingAPIfields.ar_receipt_method_id = v_ruleCondt.value)
796 THEN
797 l_flag := 1;
798 ELSE
799 l_flag := 0; EXIT;
800 END IF;
801 ELSIF (v_ruleCondt.operation_code = 'NE') THEN
802 IF (p_routingAPIfields.ar_receipt_method_id <> v_ruleCondt.value)
803 THEN
804 l_flag := 1;
805 ELSE
806 l_flag := 0; EXIT;
807 END IF;
808 END IF;
809 ELSIF (v_ruleCondt.parameter_code = 'BR_DRAWEE_ISSUED_FLAG') THEN
810 IF (v_ruleCondt.operation_code = 'EQ') THEN
811 IF (p_routingAPIfields.br_drawee_issued_flag = v_ruleCondt.value)
812 THEN
813 l_flag := 1;
814 ELSE
815 l_flag := 0; EXIT;
816 END IF;
817 END IF;
818 ELSIF (v_ruleCondt.parameter_code = 'BR_SIGNED_FLAG') THEN
819 IF (v_ruleCondt.operation_code = 'EQ') THEN
820 IF (p_routingAPIfields.br_signed_flag = v_ruleCondt.value)
821 THEN
822 l_flag := 1;
823 ELSE
824 l_flag := 0; EXIT;
825 END IF;
826 END IF;
827 END IF;
828 END LOOP;
829
830 IF (l_flag = 1) THEN
831 px_pmt_name_in_out := v_routingRules.paymentmethodname;
832 EXIT;
833 END IF;
834 END LOOP;
835
836 -- increment the hitCounter for this rule before leaving this method.
837 SELECT hitcounter
838 INTO l_hitcounter
839 FROM iby_routinginfo
840 WHERE paymentmethodname = px_pmt_name_in_out;
841
842 l_hitcounter := NVL(l_hitcounter,0) + 1;
843
844 UPDATE iby_routinginfo
845 SET hitcounter = l_hitcounter
846 WHERE paymentmethodname = px_pmt_name_in_out;
847 COMMIT;
848
849 EXCEPTION
850 WHEN NO_DATA_FOUND THEN
851
852 IF c_routingRules%ISOPEN THEN
853 CLOSE c_routingRules;
854 END IF;
855
856 IF c_ruleCondt%ISOPEN THEN
857 CLOSE c_ruleCondt;
858 END IF;
859
860 IF (p_routingAPIfields.amount IS NULL) THEN
861 raise_application_error(-20000, 'IBY_204562#', FALSE);
862 --raise_application_error(-20351, 'Amount not specified');
863 END IF;
864
865 IF (p_routingAPIfields.instr_type IS NULL) THEN
866 raise_application_error(-20000, 'IBY_204563#', FALSE);
867 --raise_application_error(-20352,'Instrument type not specified');
868 END IF;
869
870 WHEN OTHERS THEN
871 err_msg := SUBSTR(SQLERRM, 1, 100);
872 raise_application_error(-20000, 'IBY_204564#'||'ERROR#'||err_msg||'#', FALSE);
873 --raise_application_error(-20354,'OTHERS error in getPmtName: '||err_msg);
874 END getPmtName;
875
876
877 /* Internal procedure that gets the bep configuration by */
878 /* bep suffix. */
879 PROCEDURE getBepBySuffix
880 (i_suffix IN IBY_BEPInfo.Suffix%TYPE,
881 o_bepid OUT NOCOPY IBY_BEPInfo.BEPID%TYPE,
882 o_bep_base_url OUT NOCOPY IBY_BEPInfo.BaseURL%TYPE,
883 o_pmtschemename OUT NOCOPY iby_pmtschemes.PmtSchemeName%TYPE,
884 i_instrtype IN iby_accttype.instrtype%type )
885 IS
886
887 BEGIN
888
889 -- Get bep info
890 SELECT bepid , baseurl
891 INTO o_bepid, o_bep_base_url
892 FROM iby_bepinfo
893 WHERE suffix = i_suffix;
894
895 -- Get payment scheme name
896 getPmtSchemeName(o_bepid, i_instrtype, o_pmtschemename);
897
898 EXCEPTION
899
900 WHEN NO_DATA_FOUND THEN
901 -- Suffix not found in IBY_BEPINFO table
902 IF (o_bepid IS NULL) THEN
903 raise_application_error(-20000, 'IBY_20361#', FALSE);
904 END IF;
905
906 --unknown no data found error
907 raise_application_error(-20000, 'IBY_20360#', FALSE);
908
909 --WHEN OTHERS THEN
910 -- raise_application_error(-20000, 'IBY_20364#', FALSE);
911
912 END getBepBySuffix;
913
914
915 -- reject any illegal payment operation
916 PROCEDURE checkPaymentOperation(p_payment_operation_in IN VARCHAR2)
917 IS
918 BEGIN
919 IF ((p_payment_operation_in <> 'ORAPMTREQ')AND
920 (p_payment_operation_in <> 'ORAPMTBATCHREQ') AND
921 (p_payment_operation_in <> 'ORAPMTMOD') AND
922 (p_payment_operation_in <> 'ORAPMTCANC') AND
923 (p_payment_operation_in <> 'ORAPMTINQ') AND
924 (p_payment_operation_in <> 'ORAPMTCAPTURE') AND
925 (p_payment_operation_in <> 'ORAPMTRETURN') AND
926 (p_payment_operation_in <> 'ORAPMTCREDIT') AND
927 (p_payment_operation_in <> 'ORAPMTVOID') AND
928 (p_payment_operation_in <> 'ORAPMTCLOSEBATCH') AND
929 (p_payment_operation_in <> 'ORAPMTQRYBATCHSTATUS') AND
930 (p_payment_operation_in <> 'ORAPMTQRYTXSTATUS') AND
931 (p_payment_operation_in <> 'ORAINV') AND
932 (p_payment_operation_in <> 'ORAPAY'))
933
934 THEN
935 -- invalid payment operation
936 raise_application_error(-20000, 'IBY_20301#', FALSE);
937 END IF;
938
939 END checkPaymentOperation;
940
941
942 /* Procedure: checkPayeeByAccpPmtMthd
943 * Function: to make sure given instrument type is supported by payee
944 */
945
946 PROCEDURE checkPayeeByAccpPmtMthd(i_payeeid iby_payee.payeeid%type,
947 i_instr_type iby_accttype.instrtype%type)
948 IS
949 l_instr_type iby_accttype.instrtype%type;
950 l_fin_payee_flag iby_payee.financing_payee_flag%type;
951
952 cursor c_get_instrtype(ci_payeeid iby_payee.payeeid%type,
953 ci_instr_type iby_accttype.instrtype%type)
954 IS
955
956 SELECT instrtype FROM iby_accttype a, iby_accppmtmthd b
957 WHERE a.accttypeid = b.accttypeid
958 AND b.payeeid = ci_payeeid
959 AND b.status = 1
960 AND a.instrtype = ci_instr_type;
961
962 cursor c_get_fin_payee_flag(ci_payeeid iby_payee.payeeid%type)
963 IS
964 SELECT FINANCING_PAYEE_FLAG FROM iby_payee
965 WHERE payeeid = ci_payeeid;
966 BEGIN
967
968 IF (i_instr_type = 'FINANCING') THEN
969 IF c_get_fin_payee_flag%isopen THEN
970 close c_get_fin_payee_flag;
971 END IF;
972
973 OPEN c_get_fin_payee_flag(i_payeeid);
974 FETCH c_get_fin_payee_flag INTO l_fin_payee_flag;
975 IF (l_fin_payee_flag <> 'Y') THEN
976 raise_application_error(-20000, 'IBY_204455#INSTRTYPE=' ||
977 i_instr_type || '#PAYEEID=' || i_payeeid || '#', FALSE);
978 END IF;
979
980 CLOSE c_get_fin_payee_flag;
981
982 ELSE
983 IF c_get_instrtype%isopen THEN
984 close c_get_instrtype;
985 END IF;
986
987 OPEN c_get_instrtype(i_payeeid, i_instr_type);
988 FETCH c_get_instrtype INTO l_instr_type;
989 IF (c_get_instrtype%notfound) THEN
990 raise_application_error(-20000, 'IBY_204455#INSTRTYPE=' ||
991 i_instr_type || '#PAYEEID=' || i_payeeid || '#', FALSE);
992 END IF;
993
994 CLOSE c_get_instrtype;
995 END IF;
996
997 END checkPayeeByAccpPmtMthd;
998
999
1000 /* Procedure: getBEPLang
1001 * Function: fetch valid nlslang based on input lang
1002 */
1003 procedure getBEPLang(i_bepid IN iby_bepinfo.bepid%type,
1004 io_beplang IN OUT NOCOPY iby_beplangs.beplang%type)
1005 IS
1006 CURSOR c_getPreferred(ci_bepid iby_bepinfo.bepid%type) IS
1007 SELECT beplang
1008 FROM iby_BEPlangs
1009 WHERE BEPID = ci_bepid
1010 AND Preferred = 0
1011 AND BEPLang <> '' AND BEPLang IS NOT NULL; -- reject anything trivial
1012
1013 l_bep_lang_count NUMBER;
1014 BEGIN
1015
1016 l_bep_lang_count := 0;
1017
1018 IF (io_beplang IS NULL or
1019 io_beplang = '' or
1020 io_beplang = ' ') THEN
1021 return; -- it's not set, keep it that way
1022 END IF;
1023
1024 -- if given BEP accepts input lang, keep as is
1025 -- otherwise gives preferred, if preferred missing,
1026 -- give empty string (actually ' ', as plsql doesn't treat '' as NULL)
1027 SELECT count(*)
1028 INTO l_bep_lang_count
1029 FROM iby_BEPlangs
1030 WHERE BEPID = i_bepid
1031 AND UPPER(BEPlang) = UPPER(io_beplang);
1032
1033 IF (l_bep_lang_count = 0) THEN
1034 -- no match, fetched the preferred instead
1035 -- overwrite input lang w/ non-trivial preferred value
1036 IF (c_getPreferred%isopen) THEN
1037 CLOSE c_getPreferred;
1038 END IF;
1039 open c_getPreferred(i_bepid);
1040 fetch c_getPreferred into io_beplang;
1041 IF (c_getPreferred%notfound) THEN
1042 io_beplang := ' ';-- can't set empty string, see Note above
1043 END IF;
1044 close c_getPreferred;
1045 END IF;
1046 END getBEPLang;
1047
1048
1049 /*
1050 * This function is a wrapper around the getBEPLang() procedure.
1051 * Since a function call is an rvalue, it can be used in an SQL
1052 * statement to specify the NLSLANG.
1053 */
1054 /* comment out for now */
1055 /*
1056 FUNCTION getNLSLang(i_bepid IN iby_bepinfo.bepid%type,
1057 i_beplang IN iby_beplangs.beplang%type) RETURN VARCHAR2 IS
1058 v_Lang iby_beplangs.beplang%type;
1059 BEGIN
1060 v_Lang := i_beplang;
1061 iby_paymentmanagerdb_pkg.getBEPLang(i_bepid, v_Lang);
1062 RETURN v_Lang;
1063 END getNLSLang;
1064 */
1065
1066
1067 -- Get payment scheme name for given bep based on instrtype
1068 PROCEDURE getPmtSchemeName(i_bepid IN iby_bepinfo.bepid%type,
1069 i_instrtype IN iby_accttype.instrtype%type,
1070 o_pmtschemename OUT NOCOPY
1071 iby_pmtschemes.pmtschemename%type)
1072 IS
1073
1074 l_scheme_not_found BOOLEAN := TRUE;
1075 l_bankpay_bep VARCHAR2(10);
1076 l_trxn_bep VARCHAR2(10);
1077
1078 CURSOR c_getPmtSchemeName(ci_bepid iby_bepinfo.bepid%type,
1079 ci_name1 iby_pmtschemes.pmtschemename%type,
1080 ci_name2 iby_pmtschemes.pmtschemename%type DEFAULT NULL,
1081 ci_name3 iby_pmtschemes.pmtschemename%type DEFAULT NULL)
1082 IS
1083 SELECT p.PmtSchemename
1084 FROM iby_pmtschemes p, iby_bepinfo b
1085 WHERE p.bepid = b.bepid
1086 AND b.bepid = ci_bepid
1087 AND p.PMTSCHEMENAME IN (ci_name1, ci_name2, ci_name3);
1088
1089 BEGIN
1090
1091 IF (c_getPmtSchemeName%isOpen) THEN
1092 close c_getPmtSchemeName;
1093 END IF;
1094
1095 -- check to see if the indicated BEP matches
1096 -- the single BEP which has been configured to support
1097 -- bank payment trxns
1098 --
1099 -- If no Routing Rules are set, default payment system should
1100 -- be used. Hence commenting out code below to read from Profile
1101 -- option for BANKPAYMENT instrument.
1102 --
1103 --IF (UPPER(i_instrtype) = 'BANKPAYMENT') THEN
1104 -- o_pmtschemename := 'BANKPAYMENT';
1105 -- iby_utility_pvt.get_property(iby_paymentmanagerdb_pkg.C_PAYABLES_BEP_PROP_NAME,l_bankpay_bep);
1106 -- no need to use a cursor as the bepid filter
1107 -- is unique and should have been validated
1108 -- by now to point to an existing BEP
1109 --
1110 --SELECT suffix
1111 --INTO l_trxn_bep
1112 --FROM iby_bepinfo
1113 --WHERE (bepid=i_bepid);
1114 --l_scheme_not_found := l_trxn_bep <> NVL(l_bankpay_bep,'');
1115 --ELSE
1116 -- Change 'CREDITCARD' to 'SSL' and 'SET' for payment scheme
1117 -- Leave other payment types alone
1118
1119 -- Commenting out above condition as Routing Rules for
1120 -- instrument BANKPAYMENT are supported now -nmukerje
1121 IF (UPPER(i_instrtype) = 'CREDITCARD') THEN
1122 open c_getPmtSchemeName(i_bepid, 'SET', 'SSL');
1123 ELSE
1124 open c_getPmtSchemeName(i_bepid, UPPER(i_instrtype));
1125 END IF;
1126
1127 FETCH c_getPmtSchemeName INTO o_pmtschemename;
1128 l_scheme_not_found := c_getPmtSchemeName%NOTFOUND;
1129 CLOSE c_getPmtSchemeName;
1130
1131 --END IF;
1132
1133 IF (l_scheme_not_found) THEN
1134 -- No payment scheme found for bep with given instrtype
1135 raise_application_error(-20000, 'IBY_20362#BEPID=' ||
1136 i_bepid || '#', FALSE);
1137 --ELSE
1138 -- we should only have a single entry
1139 -- bep can only support 'SSL' and 'BANKACCOUNT' at the same time
1140 -- not any other combinations
1141 END IF;
1142
1143 END getPmtSchemeName;
1144
1145
1146 PROCEDURE getBepUrl(i_base_url IN iby_bepinfo.baseurl%type,
1147 i_payment_op IN VARCHAR2,
1148 i_pmtschemename IN iby_pmtschemes.pmtschemename%type,
1149 i_suffix IN iby_bepinfo.suffix%type,
1150 o_bep_url OUT NOCOPY VARCHAR2)
1151 IS
1152 BEGIN
1153 IF (i_pmtschemename = 'SSL' OR i_pmtschemename = 'PURCHASECARD' OR
1154 i_pmtschemename = 'FINANCING' OR i_pmtschemename = 'BANKACCOUNT' OR
1155 i_pmtschemename = 'BANKPAYMENT'
1156 OR i_pmtschemename = 'PINLESSDEBITCARD'
1157 ) THEN
1158 IF (i_payment_op = 'ORAINV') THEN
1159 -- Construct CIPP INVOICE URL
1160 o_bep_url := i_base_url||'/orainv_'|| i_suffix;
1161 ELSIF (i_payment_op = 'ORAPAY') THEN
1162 -- Construct CIPP PAY URL
1163 o_bep_url := i_base_url||'/orapay_'|| i_suffix;
1164 ELSE
1165 -- Construct MIPP URL
1166 o_bep_url := i_base_url||'/oramipp_'|| i_suffix;
1167 END IF;
1168 ELSIF (i_pmtschemename = 'SET') THEN
1169 -- Construct SET URL
1170 o_bep_url := i_base_url || '/oraset_' || i_suffix;
1171 END IF;
1172 END getBepUrl;
1173
1174 PROCEDURE getBepIdByPmtName(i_paymentmethodname IN VARCHAR2,
1175 i_payeeid IN iby_payee.payeeid%type,
1176 o_bepid OUT NOCOPY iby_bepinfo.bepid%type,
1177 o_bepkey OUT NOCOPY iby_bepkeys.key%type,
1178 o_fc_user_profile_code IN OUT NOCOPY VARCHAR2)
1179 IS
1180 CURSOR c_getBepIdByPmtName(ci_paymentmethodname VARCHAR2)
1181 IS
1182 SELECT b.bepid, r.bepkey, r.fndcpt_user_profile_code
1183 FROM iby_routinginfo r, iby_bepinfo b
1184 WHERE r.paymentmethodname = ci_paymentmethodname
1185 AND r.configured = 1
1186 AND r.bepid = b.bepid;
1187
1188 BEGIN
1189
1190 IF (c_getBepIdByPmtName%isopen) THEN
1191 close c_getBepIdByPmtName;
1192 END IF;
1193
1194 open c_getBepIdByPmtName(i_paymentmethodname);
1195 fetch c_getBepIdByPmtName into o_bepid, o_bepkey, o_fc_user_profile_code;
1196 IF (c_getBepIdByPmtName%notfound) THEN
1197 o_bepid := -1;
1198 END IF;
1199
1200 IF (c_getBepIdByPmtName%isopen) THEN
1201 close c_getBepIdByPmtName;
1202 END IF;
1203
1204 END getBepIdByPmtName;
1205
1206 PROCEDURE getDefaultBepId(i_mpayeeid IN iby_payee.mpayeeid%type,
1207 i_payment_channel_code IN iby_trxn_summaries_all.payment_channel_code%type,
1208 o_bepid OUT NOCOPY iby_bepinfo.bepid%type,
1209 o_bepkey OUT NOCOPY iby_bepkeys.key%type,
1210 o_fndcpt_user_profile_code OUT NOCOPY
1211 IBY_FNDCPT_USER_CC_PF_VL.USER_CC_PROFILE_CODE%TYPE)
1212 IS
1213 -- *** R12 Modification *** ---
1214 -- Cursor retrieves default bep key and FndcptUserProfileCode
1215 -- given the mpayeeid and payment channel code
1216 CURSOR c_defaultBep(ci_mpayeeid iby_payee.mpayeeid%type,
1217 ci_payment_channel_code iby_trxn_summaries_all.payment_channel_code%type)
1218 IS
1219 SELECT a.bepid,
1220 c.key,
1221 a.fndcpt_user_profile_code
1222 FROM iby_default_bep a, iby_bepinfo b, iby_bepkeys c
1223 WHERE a.payment_channel_code = ci_payment_channel_code
1224 AND a.mpayeeid = ci_mpayeeid
1225 AND a.bepid = b.bepid
1226 AND UPPER(b.activeStatus) = 'Y'
1227 AND c.bep_account_id = a.bep_account_id;
1228
1229 BEGIN
1230 --dbms_output.put_line(SubStr('in getDefaultBepId',1,255));
1231 IF (c_defaultBep%isopen) THEN
1232 close c_defaultBep;
1233 END IF;
1234 --dbms_output.put_line(SubStr('i_mpayeeid = '||i_mpayeeid,1,255));
1235 --dbms_output.put_line(SubStr('i_payment_channel_code = '||i_payment_channel_code,1,255));
1236 open c_defaultBep(i_mpayeeid, i_payment_channel_code);
1237 fetch c_defaultBep into o_bepid, o_bepkey, o_fndcpt_user_profile_code;
1238 close c_defaultBep;
1239
1240 END getDefaultBepId;
1241
1242
1243 PROCEDURE getBepById(i_bepid IN iby_bepinfo.bepid%type,
1244 o_suffix OUT NOCOPY iby_bepinfo.suffix%type,
1245 o_baseurl OUT NOCOPY iby_bepinfo.baseurl%type,
1246 o_securityscheme OUT NOCOPY iby_bepinfo.securityscheme%type,
1247 o_bepusername OUT NOCOPY iby_bepinfo.bepusername%type,
1248 o_beppassword OUT NOCOPY iby_bepinfo.beppassword%type,
1249 o_beptype OUT NOCOPY iby_bepinfo.bep_type%TYPE,
1250 o_leadtime OUT NOCOPY iby_bepinfo.leadtime%TYPE)
1251 IS
1252 CURSOR c_getBepById(ci_bepid iby_bepinfo.bepid%type)
1253 IS
1254 SELECT suffix, baseurl, securityscheme, BEPUsername, BEPPassword, bep_type, leadtime
1255 FROM iby_bepinfo
1256 WHERE bepid = ci_bepid;
1257
1258 BEGIN
1259 IF (i_bepid = -1) THEN
1260 -- shouldn't happen!
1261 raise_application_error(-20000, 'IBY_20521#', FALSE);
1262 END IF;
1263
1264 IF c_getBepById%isopen THEN
1265 close c_getBepById;
1266 END IF;
1267
1268 open c_getBepById(i_bepid);
1269 fetch c_getBepById into o_suffix, o_baseurl, o_securityscheme,
1270 o_bepusername, o_beppassword, o_beptype, o_leadtime;
1271
1272 close c_getBepById;
1273 END getBepById;
1274
1275 PROCEDURE populateRoutingFields(
1276 p_amount IN iby_trxn_summaries_all.amount%type,
1277 p_instr_type IN iby_trxn_summaries_all.instrtype%type,
1278 p_instr_subtype IN iby_trxn_summaries_all.instrsubtype%type,
1279 p_currency IN iby_trxn_summaries_all.currencynamecode%type,
1280 p_payee_id IN iby_trxn_summaries_all.payeeid%type,
1281 p_cc_type IN VARCHAR2,
1282 p_cc_num IN iby_creditcard.ccnumber%type,
1283 p_aba_routing_no IN iby_bankacct.routingno%type,
1284 p_bnf_routing_no IN iby_bankacct.routingno%type,
1285 p_org_id IN iby_trxn_summaries_all.org_id%type,
1286 p_fin_app_type IN VARCHAR2,
1287 p_merchant_bank_country IN VARCHAR2,
1288 p_factor_flag IN iby_trxn_summaries_all.factored_flag%type,
1289 x_routingfields OUT NOCOPY RoutingAPIFields_rec_type
1290 )
1291 IS
1292
1293
1294 BEGIN
1295
1296 --- *** R12 Modification *** ---
1297 -- Added 'factor_flag'.
1298 x_routingfields.amount := p_amount;
1299 x_routingfields.instr_type := p_instr_type;
1300 x_routingfields.instr_subtype := p_instr_subtype;
1301 x_routingfields.currency := p_currency;
1302 x_routingfields.payee_id := p_payee_id;
1303 x_routingfields.cc_type := p_cc_type;
1304 x_routingfields.cc_num := p_cc_num;
1305 x_routingfields.aba_routing_no := p_aba_routing_no;
1306 x_routingfields.bnf_routing_no := p_bnf_routing_no;
1307 x_routingfields.org_id := p_org_id;
1308 x_routingfields.financing_app_type := p_fin_app_type;
1309 x_routingfields.merchant_bank_country := p_merchant_bank_country;
1310 x_routingfields.factor_flag := p_factor_flag;
1311
1312 END populateRoutingFields;
1313
1314 END iby_paymentmanagerdb_pkg;
[Home] [Help]