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