1 PACKAGE iby_paymentmanagerdb_pkg AUTHID CURRENT_USER AS
5 /* Property name for the BEP which supports bank payables. */
2 /* $Header: ibypmmgs.pls 120.7.12010000.5 2010/01/21 06:28:52 sgogula ship $ */
3
4
6 C_PAYABLES_BEP_PROP_NAME CONSTANT VARCHAR2(100) := 'IBY_BANK_PAYMENT_SYSTEM_SUFFIX';
7
8
9 /* This record is used as an input to listBep and to getPmtName. */
10 /* It contains all the parameters that may be used for routing. */
11 TYPE RoutingAPIFields_rec_type IS RECORD (
12 amount NUMBER,
13 instr_type VARCHAR2(30),
14 instr_subtype VARCHAR2(30),
15 currency VARCHAR2(15),
16 payee_id VARCHAR2(80),
17 cc_type VARCHAR2(80),
18 cc_num VARCHAR2(80),
19 aba_routing_no VARCHAR2(80),
20 bnf_routing_no VARCHAR2(80),
21 org_id NUMBER,
22 financing_app_type VARCHAR2(80),
23 merchant_bank_country VARCHAR2(80),
24 factor_flag VARCHAR2(1),
25 int_bank_acct_id NUMBER,
26 int_bank_id NUMBER,
27 br_signed_flag VARCHAR2(1),
28 br_drawee_issued_flag VARCHAR2(1),
29 ar_receipt_method_id NUMBER,
30 payer_bank_country VARCHAR2(80),
31 pmt_channel_code VARCHAR2(30)
32 );
33
34 /* This procedure is used across both Core and ExtendedSET */
35
36 /* APIs to fetch the bep configuration for the given */
37 /* transaction type. The logic is pretty much the following:*/
38 /* - For orainv, oraauth, oracredit, oraclosebatch, or */
39 /* oraqrybatchstatus, go by the payment_name */
40 /* - For orapay or orasync, go by the bep suffix */
41 /* - For all other operations, go by the order_id */
42 /* */
43 /* The parameters are the following: */
44 /* - PayeeID_in: an IN parameter with the */
45 /* id corresponding to PAYEE_ID in the IBY_PAYEE */
46 /* table. */
47 /* - order_id_in: an IN parameter with the order_id of the */
48 /* current trxn. */
49 /* - payment_name_in: an optional IN parameter, routing rule */
50 /* name */
51 /* - payment_operation_in: an IN parameter with the name of */
52 /* the current operation type, e.g., orainv, ORAPMTREQ */
53 /* - BEPID_out: an OUT parameter with the numeric value */
54 /* of the bep id corresponding to BEP_ID in the */
55 /* IBY_BEPINFO table. */
56 /* - bep_suffix_in: an OUT parameter of bep suffix. */
57 /* Corresponds to the SUFFIX column in the IBY_BEPINFO table*/
58 /* - bep_url_out: an OUT parameter that contains the URL */
59 /* to access the bep. It's a combination of the base */
60 /* URL in the IBY_BEPINFO table, plus add'l info based upon */
61 /* the bep pmtscheme plus the trxn type. */
62 /* - bep_key_out: an OUT parameter set up for this */
63 /* payee/bep combination, the KEY in the */
64 /* IBY_bepkey table. */
65 /* - bep_pmtscheme_out: an OUT parameter specifying the */
66 /* bep API that the bep recognizes. */
67 /* Corresponds to the PMTSCHEME_NAME in the */
68 /* IBY_PMTSCHEMES table. */
69 /* - security_out: The value of OapfSecurity for the input */
70 /* payment_name_in. 1=SET, which brings up a wallet. */
71 /* 2=SSL, which brings up an HTML page. */
72 /* - setnoinit_flag_out: an OUT parameter which is set to 1 */
73 /* during an ORAPMTREQ for a SET bep. This may not be */
74 /* supported by all beps since standalone auth is not */
75 /* an official SET functionality. */
76 /* - bep_lang_in_out: an OUT parameter which returns the */
77 /* bep language set in the configuration. */
78 /* - amount_in: an IN paramater that tells the amount. This */
79 /* will be used to evaluate the routing rules */
80 /* - instrtype_in: an IN paramater that tells the instrument */
81 /* used. This will be used to evaluate the routing rules. */
82 /* and payment scheme */
83 /* - bep_type_out: indicates whether the bep is a gateway or */
84 /* processor-model one */
85
86 PROCEDURE listbep (
87 p_amount IN iby_trxn_summaries_all.amount%type
88 default null,
89 p_payment_channel_code IN iby_trxn_summaries_all.payment_channel_code%type default null,
90 p_currency IN iby_trxn_summaries_all.currencynamecode%type default null,
91 p_payee_id IN iby_trxn_summaries_all.payeeid%type
92 default null,
93 p_cc_type IN VARCHAR2 default null,
94 p_cc_num IN iby_creditcard.ccnumber%type
95 default null,
96 p_aba_routing_no IN iby_bankacct.routingno%type
97 default null,
98 p_org_id IN iby_trxn_summaries_all.org_id%type
99 default null,
100 p_fin_app_type IN VARCHAR2 default null,
101 p_transaction_id_in IN iby_trxn_summaries_all.TransactionID%TYPE default null,
102 p_payment_operation_in IN VARCHAR2,
103 p_ecappid_in IN iby_ecapp.ecappid%type default null,
104 p_instr_subtype IN iby_trxn_summaries_all.instrsubtype%type default null,
108 p_int_bank_acct_id IN NUMBER,
105 p_bnf_routing_no IN iby_bankacct.routingno%type,
106 p_merchant_bank_country IN VARCHAR2,
107 p_factored_flag IN iby_trxn_summaries_all.factored_flag%type default null,
109 p_br_signed_flag IN iby_trxn_summaries_all.br_signed_flag%TYPE,
110 p_br_drawee_issued_flag IN iby_trxn_summaries_all.br_drawee_issued_flag%TYPE,
111 p_ar_receipt_mth_id IN iby_trxn_summaries_all.ar_receipt_method_id%TYPE,
112 px_payee_id_in_out IN OUT NOCOPY iby_payee.PayeeID%TYPE,
113 px_order_id_in_out IN OUT NOCOPY VARCHAR2,
114 px_payment_name_in_out IN OUT NOCOPY VARCHAR2,
115 px_bep_lang_in_out IN OUT NOCOPY VARCHAR2,
116 x_payee_username_out OUT NOCOPY iby_payee.Username%TYPE,
117 x_payee_passwd_out OUT NOCOPY iby_payee.Password%TYPE,
118 x_payee_operation_out OUT NOCOPY NUMBER,
119 x_bepid_out OUT NOCOPY iby_BEPInfo.BEPID%TYPE,
120 x_bep_suffix_out OUT NOCOPY IBY_BEPInfo.Suffix%TYPE,
121 x_bep_url_out OUT NOCOPY IBY_BEPInfo.BaseURL%TYPE,
122 x_bep_key_out OUT NOCOPY IBY_BEPKeys.Key%TYPE,
123 x_bep_pmtscheme_out OUT NOCOPY iby_pmtschemes.PmtSchemeName%TYPE,
124 x_bep_username_out OUT NOCOPY IBY_BEPInfo.BEPUsername%TYPE,
125 x_bep_passwd_out OUT NOCOPY IBY_BEPInfo.BEPPassword%TYPE,
126 x_security_out OUT NOCOPY NUMBER,
127 x_setnoinit_flag_out OUT NOCOPY NUMBER,
128 x_lead_time_out OUT NOCOPY iby_bepinfo.leadtime%TYPE,
129 x_bep_type_out OUT NOCOPY IBY_BEPInfo.Bep_Type%TYPE,
130 x_fndcpt_user_profile_code_out OUT NOCOPY
131 IBY_FNDCPT_USER_CC_PF_VL.USER_CC_PROFILE_CODE%TYPE,
132 p_payer_bank_country IN VARCHAR2
133 );
134
135 PROCEDURE listbep (
136 p_amount IN iby_trxn_summaries_all.amount%type
137 default null,
138 p_payment_channel_code IN iby_trxn_summaries_all.payment_channel_code%type,
139 p_currency IN iby_trxn_summaries_all.currencynamecode%type default null,
140 p_payee_id IN iby_trxn_summaries_all.payeeid%type
141 default null,
142 p_cc_type IN VARCHAR2 default null,
143 p_cc_num IN iby_creditcard.ccnumber%type
144 default null,
145 p_aba_routing_no IN iby_bankacct.routingno%type
146 default null,
147 p_org_id IN iby_trxn_summaries_all.org_id%type
148 default null,
149 p_fin_app_type IN VARCHAR2 default null,
150 p_transaction_id_in IN iby_trxn_summaries_all.TransactionID%TYPE default null,
151 p_payment_operation_in IN VARCHAR2,
152 p_ecappid_in IN iby_ecapp.ecappid%type default null,
153 p_instr_subtype IN iby_trxn_summaries_all.instrsubtype%type default null,
154 p_bnf_routing_no IN iby_bankacct.routingno%type,
155 p_factored_flag IN iby_trxn_summaries_all.factored_flag%type default null,
156 p_int_bank_acct_id IN NUMBER,
157
158 p_br_signed_flag IN iby_trxn_summaries_all.br_signed_flag%TYPE,
159 p_br_drawee_issued_flag IN iby_trxn_summaries_all.br_drawee_issued_flag%TYPE,
160 p_ar_receipt_mth_id IN iby_trxn_summaries_all.ar_receipt_method_id%TYPE,
161 px_payee_id_in_out IN OUT NOCOPY iby_payee.PayeeID%TYPE,
162 px_order_id_in_out IN OUT NOCOPY VARCHAR2,
163 px_payment_name_in_out IN OUT NOCOPY VARCHAR2,
164 px_bep_lang_in_out IN OUT NOCOPY VARCHAR2,
165 x_payee_username_out OUT NOCOPY iby_payee.Username%TYPE,
166 x_payee_passwd_out OUT NOCOPY iby_payee.Password%TYPE,
167 x_payee_operation_out OUT NOCOPY NUMBER,
168 x_bepid_out OUT NOCOPY iby_BEPInfo.BEPID%TYPE,
169 x_bep_suffix_out OUT NOCOPY IBY_BEPInfo.Suffix%TYPE,
170 x_bep_url_out OUT NOCOPY IBY_BEPInfo.BaseURL%TYPE,
171 x_bep_key_out OUT NOCOPY IBY_BEPKeys.Key%TYPE,
172 x_bep_pmtscheme_out OUT NOCOPY iby_pmtschemes.PmtSchemeName%TYPE,
173 x_bep_username_out OUT NOCOPY IBY_BEPInfo.BEPUsername%TYPE,
174 x_bep_passwd_out OUT NOCOPY IBY_BEPInfo.BEPPassword%TYPE,
175 x_security_out OUT NOCOPY NUMBER,
176 x_setnoinit_flag_out OUT NOCOPY NUMBER,
177 x_lead_time_out OUT NOCOPY iby_bepinfo.leadtime%TYPE,
178 x_bep_type_out OUT NOCOPY IBY_BEPInfo.Bep_Type%TYPE,
179 x_fndcpt_user_profile_code_out OUT NOCOPY
180 IBY_FNDCPT_USER_CC_PF_VL.USER_CC_PROFILE_CODE%TYPE
181 );
182
183 /*
184 * Gets bep info by the bep suffix; method used by batch
185 * trxns which have no routing data and which in any case require
186 * eventual closure of batches for all bep's.
187 *
188 */
189 PROCEDURE listbep (
190 p_payee_id IN iby_bepkeys.OwnerId%TYPE,
191 p_bepkey IN iby_bepkeys.KEY%TYPE,
192 p_instr_type IN iby_trxn_summaries_all.InstrType%TYPE,
193 px_bep_suffix_in_out IN OUT NOCOPY iby_bepinfo.Suffix%TYPE,
194 x_bepid_out OUT NOCOPY iby_bepinfo.BepId%TYPE,
198 x_bep_passwd_out OUT NOCOPY iby_bepinfo.BEPPassword%TYPE,
195 x_bep_url_out OUT NOCOPY iby_bepinfo.BaseUrl%TYPE,
196 x_bep_pmtscheme_out OUT NOCOPY iby_pmtschemes.PmtSchemeName%TYPE,
197 x_bep_username_out OUT NOCOPY iby_bepinfo.BEPUsername%TYPE,
199 x_security_out OUT NOCOPY NUMBER,
200 x_setnoinit_flag_out OUT NOCOPY NUMBER,
201 x_bep_type_out OUT NOCOPY iby_bepinfo.Bep_Type%TYPE,
202 x_bep_lang_out OUT NOCOPY VARCHAR2,
203 x_lead_time_out OUT NOCOPY iby_bepinfo.leadtime%TYPE
204 );
205
206
207 /* Internal procedure to get the bep configuration by the */
208 /* payment name. */
209
210 /* PROCEDURE getBepByPmtName
211 (payment_name_in IN iby_routinginfo.PaymentMethodName%TYPE,
212 o_payment_method_id OUT NOCOPY iby_routinginfo.PaymentMethodID%TYPE,
213 o_bepid OUT NOCOPY IBY_BEPInfo.BEPID%TYPE,
214 o_suffix OUT NOCOPY IBY_BEPInfo.suffix%TYPE,
215 o_bep_base_url OUT NOCOPY IBY_BEPInfo.BaseURL%TYPE,
216 o_pmtschemeName OUT NOCOPY iby_pmtschemes.PmtSchemeName%TYPE,
217 instr_type_in IN iby_accttype.instrtype%type);*/
218
219 /* Internal procedure that gets the bep configuration by */
220 /* the order_id for a non-SET bep (since it looks in the */
221 /* IBY_TRANSACTIONS table). */
222 /* PROCEDURE getBepByOrderId
223 (order_id_in IN iby_trxn_summaries_all.TangibleID%TYPE,
224 payee_id_in IN IBY_Payee.PayeeID%TYPE,
225 o_bepid OUT NOCOPY IBY_BEPInfo.BEPID%TYPE,
226 o_suffix OUT NOCOPY IBY_BEPInfo.Suffix%TYPE,
227 o_bep_base_url OUT NOCOPY IBY_BEPInfo.BaseURL%TYPE,
228 o_pmtschemeName OUT NOCOPY iby_pmtschemes.PmtSchemeName%TYPE,
229 i_instrtype IN iby_accttype.instrtype%type);
230 */
231 /* Internal procedure that gets the bep configuration by */
232 /* the order_id for a SET bep (since it looks in the */
233 /* IBY_TRANSACTIONS_SET table). */
234 /* PROCEDURE getBepByOrderId_SET
235 (order_id_in IN iby_trxn_summaries_all.TangibleID%TYPE,
236 payee_id_in IN IBY_Payee.PayeeID%TYPE,
237 v_id IN OUT NOCOPY IBY_BEPInfo.BEPID%TYPE,
238 v_suffix IN OUT NOCOPY IBY_BEPInfo.Suffix%TYPE,
239 v_base_url IN OUT NOCOPY IBY_BEPInfo.BaseURL%TYPE,
240 v_pmtscheme IN OUT NOCOPY iby_pmtschemes.PmtSchemeName%TYPE);
241 */
242
243 /* Internal procedure that gets the bep configuration by */
244 /* bep suffix. */
245 PROCEDURE getBepBySuffix
246 (i_suffix IN IBY_BEPInfo.Suffix%TYPE,
247 o_bepid OUT NOCOPY IBY_BEPInfo.BEPID%TYPE,
248 o_bep_base_url OUT NOCOPY IBY_BEPInfo.BaseURL%TYPE,
249 o_pmtschemename OUT NOCOPY iby_pmtschemes.PmtSchemeName%TYPE,
250 i_instrtype IN iby_accttype.instrtype%type );
251
252
253
254 /* Internal procedure that finds the pmt method name using routing rules */
255 PROCEDURE getPmtName(
256 p_routingAPIfields IN RoutingAPIFields_rec_type,
257 px_pmt_name_in_out IN OUT NOCOPY VARCHAR2 );
258
259
260 -- reject any illegal payment operation
261 procedure checkPaymentOperation(p_payment_operation_in IN VARCHAR2);
262
263
264 /* Procedure: checkPayeeByAccpPmtMthd
265 * Function: to make sure given instrument type is supported by payee
266 */
267
268 PROCEDURE checkPayeeByAccpPmtMthd(i_payeeid iby_payee.payeeid%type,
269 i_instr_type iby_accttype.instrtype%type);
270
271
272 /* Procedure: getBEPLang
273 * Function: fetch valid nlslang based on input lang
274 */
275 PROCEDURE getBEPLang(i_bepid IN iby_bepinfo.bepid%type,
276 io_beplang IN OUT NOCOPY iby_beplangs.beplang%type);
277 /*
278 * This function is a wrapper around the getBEPLang() procedure.
279 * The purpose of this wrapper is to make the NLS lang available
280 * as a return parameter (so that it can be used in an SQL statement).
281 */
282 /* comment out for now */
283 /* FUNCTION getNLSLang(i_bepid IN iby_bepinfo.bepid%type,
284 io_beplang IN OUT NOCOPY iby_beplangs.beplang%type);
285 */
286
287 PROCEDURE getPmtSchemeName(i_bepid IN iby_bepinfo.bepid%type,
288 i_instrtype IN iby_accttype.instrtype%type,
289 o_pmtschemename OUT
290 iby_pmtschemes.pmtschemename%type);
291
292 PROCEDURE getBepUrl(i_base_url IN iby_bepinfo.baseurl%type,
293 i_payment_op IN VARCHAR2,
294 i_pmtschemename IN iby_pmtschemes.pmtschemename%type,
295 i_suffix IN iby_bepinfo.suffix%type,
296 o_bep_url OUT NOCOPY VARCHAR2);
297
298 PROCEDURE getBepIdByPmtName(i_paymentmethodname IN VARCHAR2,
299 i_payeeid IN iby_payee.payeeid%type,
300 o_bepid OUT NOCOPY iby_bepinfo.bepid%type,
301 o_bepkey OUT NOCOPY iby_bepkeys.key%type,
302 o_fc_user_profile_code IN OUT NOCOPY VARCHAR2);
303
304 PROCEDURE getDefaultBepId(i_mpayeeid IN iby_payee.mpayeeid%type,
305 i_payment_channel_code IN iby_trxn_summaries_all.payment_channel_code%type,
306 o_bepid OUT NOCOPY iby_bepinfo.bepid%type,
307 o_bepkey OUT NOCOPY iby_bepkeys.key%type,
308 o_fndcpt_user_profile_code OUT NOCOPY
309 IBY_FNDCPT_USER_CC_PF_VL.USER_CC_PROFILE_CODE%TYPE);
310
311
312 PROCEDURE getBepById(i_bepid IN iby_bepinfo.bepid%type,
313 o_suffix OUT NOCOPY iby_bepinfo.suffix%type,
314 o_baseurl OUT NOCOPY iby_bepinfo.baseurl%type,
315 o_securityscheme OUT NOCOPY iby_bepinfo.securityscheme%type,
316 o_bepusername OUT NOCOPY iby_bepinfo.bepusername%type,
317 o_beppassword OUT NOCOPY iby_bepinfo.beppassword%type,
318 o_beptype OUT NOCOPY iby_bepinfo.bep_type%TYPE,
319 o_leadtime OUT NOCOPY iby_bepinfo.leadtime%TYPE);
320
321 PROCEDURE populateRoutingFields(
322 p_amount IN iby_trxn_summaries_all.amount%type default null,
323 p_instr_type IN iby_trxn_summaries_all.instrtype%type
324 default null,
325 p_instr_subtype IN iby_trxn_summaries_all.instrsubtype%type
326 default null,
327 p_currency IN iby_trxn_summaries_all.currencynamecode%type
328 default null,
329 p_payee_id IN iby_trxn_summaries_all.payeeid%type default null,
330 p_cc_type IN VARCHAR2 default null,
331 p_cc_num IN iby_creditcard.ccnumber%type default null,
332 p_aba_routing_no IN iby_bankacct.routingno%type default null,
333 p_bnf_routing_no IN iby_bankacct.routingno%type default null,
334 p_org_id IN iby_trxn_summaries_all.org_id%type default null,
335 p_fin_app_type IN VARCHAR2 default null,
336 p_merchant_bank_country IN VARCHAR2,
337 p_factor_flag IN iby_trxn_summaries_all.factored_flag%type,
338 p_payer_bank_country IN VARCHAR2,
339 x_routingfields OUT NOCOPY RoutingAPIFields_rec_type
340 );
341
342 END iby_paymentmanagerdb_pkg;
[Home] [Help]