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