1 PACKAGE iby_transactioncc_pkg AUTHID CURRENT_USER AS
2 /*$Header: ibytxccs.pls 120.37 2011/02/24 06:49:00 lmallick ship $ */
3
4 --
5 -- various trxn status constants
6 --
7 C_STATUS_SUCCESS CONSTANT NUMBER := 0;
8 C_STATUS_COMMUNICATION_ERROR CONSTANT NUMBER := 1;
9 C_STATUS_BEP_FAIL CONSTANT NUMBER := 5;
10 C_STATUS_REQUEST_PENDING CONSTANT NUMBER := 11;
11 C_STATUS_SCHED_IN_PROGRESS CONSTANT NUMBER := 12;
12 C_STATUS_REQUEST_SCHEDULED CONSTANT NUMBER := 13;
13 C_STATUS_VOICE_AUTH_REQD CONSTANT NUMBER := 21;
14 C_STATUS_OPEN_BATCHED CONSTANT NUMBER := 100;
15 C_STATUS_BATCH_TRANSITIONAL CONSTANT NUMBER := 109;
16 C_STATUS_BATCH_PENDING CONSTANT NUMBER := 111;
17
18 --
19 -- credit card req types
20 --
21 C_REQTYPE_BATCHCLOSE CONSTANT VARCHAR2(100) := 'ORAPMTCLOSEBATCH';
22 C_REQTYPE_PDC_BATCHCLOSE CONSTANT VARCHAR2(100) := 'ORAPMTPDCCLOSEBATCH';
23 C_REQTYPE_EFT_BATCHCLOSE CONSTANT VARCHAR2(100) := 'ORAPMTEFTCLOSEBATCH';
24
25 C_REQTYPE_CAPTURE CONSTANT VARCHAR2(100) := 'ORAPMTCAPTURE';
26 C_REQTYPE_CREDIT CONSTANT VARCHAR2(100) := 'ORAPMTCREDIT';
27 C_REQTYPE_RETURN CONSTANT VARCHAR2(100) := 'ORAPMTRETURN';
28 C_REQTYPE_REQUEST CONSTANT VARCHAR2(100) := 'ORAPMTREQ';
29 C_REQTYPE_BATCHREQ CONSTANT VARCHAR2(100) := 'ORAPMTBATCHREQ';
30
31 /*
32 * Record that stores the transaction attributes that are
33 * used as criteria for grouping.
34 */
35 TYPE trxnGroupCriteriaType IS RECORD (
36 trxn_id
37 IBY_TRXN_SUMMARIES_ALL.transactionid%TYPE,
38 process_profile_code
39 IBY_TRXN_SUMMARIES_ALL.process_profile_code%TYPE,
40 bep_key
41 IBY_TRXN_SUMMARIES_ALL.bepkey%TYPE,
42 org_id
43 IBY_TRXN_SUMMARIES_ALL.org_id%TYPE,
44 org_type
45 IBY_TRXN_SUMMARIES_ALL.org_type%TYPE,
46 curr_code
47 IBY_TRXN_SUMMARIES_ALL.currencynamecode%TYPE,
48 amount
49 IBY_TRXN_SUMMARIES_ALL.amount%TYPE,
50 legal_entity_id
51 IBY_TRXN_SUMMARIES_ALL.legal_entity_id%TYPE,
52 int_bank_acct_id
53 IBY_TRXN_SUMMARIES_ALL.payeeinstrid%TYPE,
54 settle_date
55 IBY_TRXN_SUMMARIES_ALL.settledate%TYPE,
56 group_by_org
57 IBY_FNDCPT_SYS_CC_PF_B.group_by_org%TYPE,
58 group_by_le
59 IBY_FNDCPT_SYS_CC_PF_B.group_by_legal_entity%TYPE,
60 group_by_int_bank_acct
61 IBY_FNDCPT_SYS_CC_PF_B.group_by_int_bank_account%TYPE,
62 group_by_curr
63 IBY_FNDCPT_SYS_CC_PF_B.group_by_settlement_curr%TYPE,
64 group_by_settle_date
65 IBY_FNDCPT_SYS_CC_PF_B.group_by_settlement_date%TYPE,
66 max_amt_curr
67 IBY_FNDCPT_SYS_CC_PF_B.limit_by_amt_curr%TYPE,
68 fx_rate_type
69 IBY_FNDCPT_SYS_CC_PF_B.limit_by_exch_rate_type%TYPE,
70 max_amt_limit
71 IBY_FNDCPT_SYS_CC_PF_B.limit_by_total_amt%TYPE,
72 num_trxns_limit
73 IBY_FNDCPT_SYS_CC_PF_B.limit_by_settlement_num%TYPE
74 );
75
76 /*
77 * Table of transaction grouping criteria.
78 */
79 TYPE trxnGroupCriteriaTabType IS TABLE OF trxnGroupCriteriaType
80 INDEX BY BINARY_INTEGER;
81
82 /*
83 * Record to store attributes of a batch that
84 * are influenced by transaction grouping rules.
85 *
86 * The IBY_BATCHES_ALL table will be updated with
87 * these attributes after transaction grouping.
88 */
89 TYPE batchAttrRecType IS RECORD (
90 mbatch_id
91 IBY_BATCHES_ALL.mbatchid%TYPE,
92 batch_id
93 IBY_BATCHES_ALL.batchid%TYPE,
94 profile_code
95 IBY_BATCHES_ALL.process_profile_code%TYPE,
96 bep_key
97 IBY_BATCHES_ALL.bepkey%TYPE,
98 org_id
99 IBY_BATCHES_ALL.org_id%TYPE,
100 org_type
101 IBY_BATCHES_ALL.org_type%TYPE,
102 le_id
103 IBY_BATCHES_ALL.legal_entity_id%TYPE,
104 int_bank_acct_id
105 IBY_BATCHES_ALL.payeeinstrid%TYPE,
106 curr_code
107 IBY_BATCHES_ALL.currencynamecode%TYPE,
108 settle_date
109 IBY_BATCHES_ALL.settledate%TYPE,
110 trxn_count
111 IBY_BATCHES_ALL.numtrxns%TYPE
112 );
113
114 /*
115 * Table of grouping based batch attributes.
116 */
117 TYPE batchAttrTabType IS TABLE OF batchAttrRecType
118 INDEX BY BINARY_INTEGER;
119
120
121 /*
122 * Record that holds the relationship between
123 * a batch and transaction.
124 */
125 TYPE trxnsInBatchRecType IS RECORD (
126 trxn_id
127 IBY_TRXN_SUMMARIES_ALL.transactionid%TYPE,
128 mbatch_id
129 IBY_TRXN_SUMMARIES_ALL.mbatchid%TYPE,
130 batch_id
131 IBY_TRXN_SUMMARIES_ALL.batchid%TYPE
132 );
133
134 /*
135 * Table of trxn-batch relationships.
136 */
137 TYPE trxnsInBatchTabType IS TABLE OF trxnsInBatchRecType
138 INDEX BY BINARY_INTEGER;
139
140 /*
141 * Table of mbatch ids.
142 *
143 * Maps to IBY_BATCHES_ALL.MBATCHID.
144 */
145 TYPE mBatchIdsTab IS TABLE OF IBY_BATCHES_ALL.mbatchid%TYPE
146 INDEX BY BINARY_INTEGER;
147
148 -- Changes for bug# 8889709
149 TYPE t_mbatchid IS TABLE OF
150 IBY_BATCHES_ALL.mbatchid%TYPE
151 INDEX BY BINARY_INTEGER;
152
153 TYPE t_batchid IS TABLE OF
154 IBY_BATCHES_ALL.batchid%TYPE
155 INDEX BY BINARY_INTEGER;
156
157 TYPE t_profcode IS TABLE OF
158 IBY_BATCHES_ALL.process_profile_code%TYPE
159 INDEX BY BINARY_INTEGER;
160
161 TYPE t_bepkey IS TABLE OF
162 IBY_BATCHES_ALL.bepkey%TYPE
163 INDEX BY BINARY_INTEGER;
164
165 TYPE t_org_id IS TABLE OF
166 IBY_BATCHES_ALL.org_id%TYPE
167 INDEX BY BINARY_INTEGER;
168
169 TYPE t_org_type IS TABLE OF
170 IBY_BATCHES_ALL.org_type%TYPE
171 INDEX BY BINARY_INTEGER;
172
173 TYPE t_legal_entity_id IS TABLE OF
174 IBY_BATCHES_ALL.legal_entity_id%TYPE
175 INDEX BY BINARY_INTEGER;
176
177 TYPE t_payeeinstrid IS TABLE OF
178 IBY_BATCHES_ALL.payeeinstrid%TYPE
179 INDEX BY BINARY_INTEGER;
180
181 TYPE t_currencynamecode IS TABLE OF
182 IBY_BATCHES_ALL.currencynamecode%TYPE
183 INDEX BY BINARY_INTEGER;
184
185 TYPE t_settledate IS TABLE OF
186 IBY_BATCHES_ALL.settledate%TYPE
187 INDEX BY BINARY_INTEGER;
188
189 TYPE batch_tab_type IS RECORD
190 (
191 mbatchid t_mbatchid,
192 batchid t_batchid,
193 profCode t_profcode,
194 bepkey t_bepkey,
195 orgid t_org_id,
196 orgtype t_org_type,
197 legalentityid t_legal_entity_id,
198 payeeinstrid t_payeeinstrid,
199 currencycode t_currencynamecode,
200 settledate t_settledate
201 );
202 batchTab batch_tab_type;
203
204 -- Bug# 8889709 - Transaction Tab
205 TYPE t_transactionid IS TABLE OF
206 IBY_TRXN_SUMMARIES_ALL.transactionid%TYPE
207 INDEX BY BINARY_INTEGER;
208
209 TYPE t_mbatch_id IS TABLE OF
210 IBY_TRXN_SUMMARIES_ALL.mbatchid%TYPE
211 INDEX BY BINARY_INTEGER;
212
213 TYPE t_batch_id IS TABLE OF
214 IBY_TRXN_SUMMARIES_ALL.batchid%TYPE
215 INDEX BY BINARY_INTEGER;
216 TYPE transaction_tab_type IS RECORD
217 (
218 transactionid t_transactionid,
219 mbatchid t_mbatch_id,
220 batchid t_batch_id
221 );
222 trxnTab transaction_tab_type;
223 -- End bug# 8889709
224
225 PROCEDURE validate_open_batch
226 (
227 p_bep_id IN iby_trxn_summaries_all.bepid%TYPE,
228 p_mbatch_id IN iby_batches_all.mbatchid%TYPE,
229 p_sec_key_on IN VARCHAR2,
230 x_trxn_count OUT NOCOPY iby_batches_all.numtrxns%TYPE,
231 x_batch_currency OUT NOCOPY iby_batches_all.currencynamecode%TYPE
232 );
233
234 PROCEDURE prepare_instr_data
235 (p_commit IN VARCHAR2,
236 p_sys_key IN iby_security_pkg.DES3_KEY_TYPE,
237 p_instrnum IN iby_trxn_summaries_all.instrnumber%TYPE,
238 p_instrtype IN iby_trxn_summaries_all.instrtype%TYPE,
239 x_instrnum OUT NOCOPY iby_trxn_summaries_all.instrnumber%TYPE,
240 x_instr_subtype OUT NOCOPY iby_trxn_summaries_all.instrsubtype%TYPE,
241 x_instr_hash OUT NOCOPY iby_trxn_summaries_all.instrnum_hash%TYPE,
242 x_range_id OUT NOCOPY iby_trxn_summaries_all.instrnum_sec_segment_id%TYPE,
243 x_instr_len OUT NOCOPY iby_trxn_summaries_all.instrnum_length%TYPE,
244 x_segment_id OUT NOCOPY iby_trxn_summaries_all.instrnum_sec_segment_id%TYPE
245 );
246
247 PROCEDURE insert_extensibility
248 (
249 p_trxnmid IN iby_trxn_summaries_all.trxnmid%TYPE,
250 p_commit IN VARCHAR2,
251 p_extend_names IN JTF_VARCHAR2_TABLE_100,
252 p_extend_vals IN JTF_VARCHAR2_TABLE_200
253 );
254
255 /* Inserts a new row into the IBY_TRANSACTIONS table. This method */
256 /* would be called every time a MIPP authorize operation is performed. */
257
258 PROCEDURE insert_auth_txn
259 (
260 ecapp_id_in IN iby_trxn_summaries_all.ecappid%TYPE,
261 req_type_in IN iby_trxn_summaries_all.ReqType%TYPE,
262 order_id_in IN iby_transactions_v.order_id%TYPE,
263 merchant_id_in IN iby_transactions_v.merchant_id%TYPE,
264 vendor_id_in IN iby_transactions_v.vendor_id%TYPE,
265 vendor_key_in IN iby_transactions_v.bepkey%TYPE,
266 amount_in IN iby_transactions_v.amount%TYPE,
267 currency_in IN iby_transactions_v.currency%TYPE,
268 status_in IN iby_transactions_v.status%TYPE,
269 time_in IN iby_transactions_v.time%TYPE DEFAULT sysdate,
270 payment_name_in IN iby_transactions_v.payment_name%TYPE,
271 payment_type_in IN iby_transactions_v.payment_type%TYPE,
272 trxn_type_in IN iby_transactions_v.trxn_type%TYPE DEFAULT NULL,
273 authcode_in IN iby_transactions_v.authcode%TYPE DEFAULT NULL,
274 referencecode_in IN iby_transactions_v.referencecode%TYPE DEFAULT NULL,
275 AVScode_in IN iby_transactions_v.AVScode%TYPE DEFAULT NULL,
276 acquirer_in IN iby_transactions_v.acquirer%TYPE DEFAULT NULL,
277 Auxmsg_in IN iby_transactions_v.Auxmsg%TYPE DEFAULT NULL,
278 vendor_code_in IN iby_transactions_v.vendor_code%TYPE DEFAULT NULL,
279 vendor_message_in IN iby_transactions_v.vendor_message%TYPE DEFAULT NULL,
280 error_location_in IN iby_transactions_v.error_location%TYPE DEFAULT NULL,
281 trace_number_in IN iby_transactions_v.TraceNumber%TYPE DEFAULT NULL,
282 org_id_in IN iby_trxn_summaries_all.org_id%type DEFAULT NULL,
283 billeracct_in IN iby_tangible.acctno%type,
284 refinfo_in IN iby_tangible.refinfo%type,
285 memo_in IN iby_tangible.memo%type,
286 order_medium_in IN iby_tangible.order_medium%TYPE,
287 eft_auth_method_in IN iby_tangible.eft_auth_method%TYPE,
288 payerinstrid_in IN iby_trxn_summaries_all.payerinstrid%type,
289 instrnum_in IN iby_trxn_summaries_all.instrnumber%type,
290 payerid_in IN iby_trxn_summaries_all.payerid%type,
291 instrtype_in IN iby_trxn_summaries_all.instrType%type,
292 cvv2result_in IN iby_trxn_core.CVV2Result%type,
293 master_key_in IN iby_security_pkg.DES3_KEY_TYPE,
294 subkey_seed_in IN RAW,
295 trxnref_in IN iby_trxn_summaries_all.trxnref%TYPE,
296 dateofvoiceauth_in IN iby_trxn_core.date_of_voice_authorization%TYPE,
297 instr_expirydate_in IN iby_trxn_core.instr_expirydate%TYPE,
298 instr_sec_val_in IN VARCHAR2,
299 card_subtype_in IN iby_trxn_core.card_subtype_code%TYPE,
300 card_data_level_in IN iby_trxn_core.card_data_level%TYPE,
301 instr_owner_name_in IN iby_trxn_core.instr_owner_name%TYPE,
302 instr_address_line1_in IN iby_trxn_core.instr_owner_address_line1%TYPE,
303 instr_address_line2_in IN iby_trxn_core.instr_owner_address_line2%TYPE,
304 instr_address_line3_in IN iby_trxn_core.instr_owner_address_line3%TYPE,
305 instr_city_in IN iby_trxn_core.instr_owner_city%TYPE,
306 instr_state_in IN iby_trxn_core.instr_owner_state_province%TYPE,
307 instr_country_in IN iby_trxn_core.instr_owner_country%TYPE,
308 instr_postalcode_in IN iby_trxn_core.instr_owner_postalcode%TYPE,
309 instr_phonenumber_in IN iby_trxn_core.instr_owner_phone%TYPE,
310 instr_email_in IN iby_trxn_core.instr_owner_email%TYPE,
311 pos_reader_cap_in IN iby_trxn_core.pos_reader_capability_code%TYPE,
312 pos_entry_method_in IN iby_trxn_core.pos_entry_method_code%TYPE,
313 pos_card_id_method_in IN iby_trxn_core.pos_id_method_code%TYPE,
314 pos_auth_source_in IN iby_trxn_core.pos_auth_source_code%TYPE,
315 reader_data_in IN iby_trxn_core.reader_data%TYPE,
316 extend_names_in IN JTF_VARCHAR2_TABLE_100,
317 extend_vals_in IN JTF_VARCHAR2_TABLE_200,
318 debit_network_code_in IN iby_trxn_core.debit_network_code%TYPE,
319 surcharge_amount_in IN iby_trxn_core.surcharge_amount%TYPE,
320 proc_tracenumber_in IN iby_trxn_core.proc_tracenumber%TYPE,
321 transaction_id_out OUT NOCOPY iby_trxn_summaries_all.TransactionID%TYPE,
322 transaction_mid_out OUT NOCOPY iby_trxn_summaries_all.trxnmid%TYPE,
323 org_type_in IN iby_trxn_summaries_all.org_type%TYPE,
324 payment_channel_code_in IN iby_trxn_summaries_all.payment_channel_code%TYPE,
325 factored_flag_in IN iby_trxn_summaries_all.factored_flag%TYPE,
326 process_profile_code_in IN iby_trxn_summaries_all.process_profile_code%TYPE,
327 sub_key_id_in IN iby_trxn_summaries_all.sub_key_id%TYPE,
328 voiceAuthFlag_in IN iby_trxn_core.voiceauthflag%TYPE,
329 reauth_trxnid_in IN iby_trxn_summaries_all.TransactionID%TYPE
330 );
331
332
333 /* Inserts/Updates the results of a reversal into the IBY_TRANSACTIONS table. */
334 PROCEDURE insert_rev_result
335 ( trxnmid_in IN iby_trxn_summaries_all.trxnmid%TYPE,
336 trxnid_in IN iby_trxn_summaries_all.transactionid%TYPE,
337 status_in IN iby_trxn_summaries_all.status%TYPE,
338 trxntype_in IN iby_trxn_summaries_all.trxntypeid%TYPE,
339 revised_amt_in IN iby_trxn_summaries_all.amount%TYPE,
340 referencecode_in IN iby_transactions_v.referencecode%TYPE DEFAULT NULL,
341 AVScode_in IN iby_transactions_v.AVScode%TYPE DEFAULT NULL,
342 acquirer_in IN iby_transactions_v.acquirer%TYPE DEFAULT NULL,
343 Auxmsg_in IN iby_transactions_v.Auxmsg%TYPE DEFAULT NULL,
344 cvv2result_in IN iby_trxn_core.CVV2Result%type,
345 bepcode_in IN iby_trxn_summaries_all.bepcode%TYPE,
346 bepmessage_in IN iby_trxn_summaries_all.bepmessage%TYPE,
347 error_loc_in IN iby_trxn_summaries_all.errorlocation%TYPE,
348 tracenumber_in IN iby_trxn_core.tracenumber%TYPE,
349 authcode_in IN iby_trxn_core.authcode%TYPE,
350 extend_names_in IN JTF_VARCHAR2_TABLE_100,
351 extend_vals_in IN JTF_VARCHAR2_TABLE_200
352 );
353
354
355 /* Inserts a new row into the IBY_TRANSACTIONS table. This method */
356 /* would be called every time a MIPP capture, credit, return, or void */
357 /* operation is performed. */
358
359
360 PROCEDURE insert_other_txn
361 (
362 ecapp_id_in IN iby_trxn_summaries_all.ECAPPID%TYPE,
363 req_type_in IN iby_trxn_summaries_all.ReqType%TYPE,
364 order_id_in IN iby_transactions_v.order_id%TYPE,
365 merchant_id_in IN iby_transactions_v.merchant_id%TYPE,
366 vendor_id_in IN iby_transactions_v.vendor_id%TYPE,
367 vendor_key_in IN iby_transactions_v.bepkey%TYPE,
368 status_in IN iby_transactions_v.status%TYPE,
369 time_in IN iby_transactions_v.time%TYPE DEFAULT sysdate,
370 payment_type_in IN iby_transactions_v.payment_type%TYPE,
371 payment_name_in IN iby_transactions_v.payment_name%TYPE DEFAULT NULL,
372 trxn_type_in IN iby_transactions_v.trxn_type%TYPE DEFAULT NULL,
373 amount_in IN iby_transactions_v.amount%TYPE DEFAULT NULL,
374 currency_in IN iby_transactions_v.currency%TYPE DEFAULT NULL,
375 referencecode_in IN iby_transactions_v.referencecode%TYPE DEFAULT NULL,
376 vendor_code_in IN iby_transactions_v.vendor_code%TYPE DEFAULT NULL,
377 vendor_message_in IN iby_transactions_v.vendor_message%TYPE DEFAULT NULL,
378 error_location_in IN iby_transactions_v.error_location%TYPE DEFAULT NULL,
379 trace_number_in IN iby_transactions_v.TraceNumber%TYPE DEFAULT NULL,
380 org_id_in IN iby_trxn_summaries_all.org_id%type DEFAULT NULL,
381 billeracct_in IN iby_tangible.acctno%type,
382 refinfo_in IN iby_tangible.refinfo%type,
383 memo_in IN iby_tangible.memo%type,
384 order_medium_in IN iby_tangible.order_medium%TYPE,
385 eft_auth_method_in IN iby_tangible.eft_auth_method%TYPE,
386 payerinstrid_in IN iby_trxn_summaries_all.payerinstrid%type,
387 instrnum_in IN iby_trxn_summaries_all.instrnumber%type,
388 payerid_in IN iby_trxn_summaries_all.payerid%type,
389 master_key_in IN iby_security_pkg.DES3_KEY_TYPE,
390 subkey_seed_in IN RAW,
391 trxnref_in IN iby_trxn_summaries_all.trxnref%TYPE,
392 instr_expirydate_in IN iby_trxn_core.instr_expirydate%TYPE,
393 card_subtype_in IN iby_trxn_core.card_subtype_code%TYPE,
394 instr_owner_name_in IN iby_trxn_core.instr_owner_name%TYPE,
395 instr_address_line1_in IN iby_trxn_core.instr_owner_address_line1%TYPE,
396 instr_address_line2_in IN iby_trxn_core.instr_owner_address_line2%TYPE,
397 instr_address_line3_in IN iby_trxn_core.instr_owner_address_line3%TYPE,
398 instr_city_in IN iby_trxn_core.instr_owner_city%TYPE,
399 instr_state_in IN iby_trxn_core.instr_owner_state_province%TYPE,
400 instr_country_in IN iby_trxn_core.instr_owner_country%TYPE,
401 instr_postalcode_in IN iby_trxn_core.instr_owner_postalcode%TYPE,
402 instr_phonenumber_in IN iby_trxn_core.instr_owner_phone%TYPE,
403 instr_email_in IN iby_trxn_core.instr_owner_email%TYPE,
404 extend_names_in IN JTF_VARCHAR2_TABLE_100,
405 extend_vals_in IN JTF_VARCHAR2_TABLE_200,
406 transaction_id_in_out IN OUT NOCOPY iby_trxn_summaries_all.TransactionID%TYPE,
407 transaction_mid_out OUT NOCOPY iby_trxn_summaries_all.trxnmid%TYPE,
408 org_type_in IN iby_trxn_summaries_all.org_type%TYPE,
409 payment_channel_code_in IN iby_trxn_summaries_all.payment_channel_code%TYPE,
410 factored_flag_in IN iby_trxn_summaries_all.factored_flag%TYPE,
411 settlement_date_in IN iby_trxn_summaries_all.settledate%TYPE,
412 settlement_due_date_in IN iby_trxn_summaries_all.settlement_due_date%TYPE,
413 process_profile_code_in IN iby_trxn_summaries_all.process_profile_code%TYPE,
414 instrtype_in IN iby_trxn_summaries_all.instrtype%TYPE
415 );
416
417
418 /* Inserts a row into the iby_transaction table if auth, capture, */
419 /* return, credit, and void timeout */
420
421 PROCEDURE insert_timeout_txn
422 (
423 req_type_in IN iby_trxn_summaries_all.ReqType%TYPE,
424 order_id_in IN iby_transactions_v.order_id%TYPE,
425 merchant_id_in IN iby_transactions_v.merchant_id%TYPE,
426 vendor_id_in IN iby_transactions_v.vendor_id%TYPE,
427 vendor_key_in IN iby_transactions_v.bepkey%TYPE,
428 ecapp_id_in IN iby_trxn_summaries_all.ecappid%TYPE,
429 time_in IN iby_transactions_v.time%TYPE DEFAULT sysdate,
430 status_in IN iby_transactions_v.status%TYPE,
431 org_id_in IN iby_trxn_summaries_all.org_id%type DEFAULT NULL,
432 amount_in IN iby_tangible.amount%type,
433 currency_in IN iby_tangible.currencynamecode%type,
434 billeracct_in IN iby_tangible.acctno%type,
435 refinfo_in IN iby_tangible.refinfo%type,
436 memo_in IN iby_tangible.memo%type,
437 order_medium_in IN iby_tangible.order_medium%TYPE,
438 eft_auth_method_in IN iby_tangible.eft_auth_method%TYPE,
439 payerinstrid_in IN iby_trxn_summaries_all.payerinstrid%type,
440 instrnum_in IN iby_trxn_summaries_all.instrnumber%type,
441 payerid_in IN iby_trxn_summaries_all.payerid%type,
442 instrtype_in IN iby_trxn_summaries_all.instrType%type,
443 master_key_in IN iby_security_pkg.DES3_KEY_TYPE,
444 subkey_seed_in IN RAW,
445 trxnref_in IN iby_trxn_summaries_all.trxnref%TYPE,
446 transaction_id_out OUT NOCOPY iby_trxn_summaries_all.TransactionID%TYPE,
447 transaction_mid_out OUT NOCOPY iby_trxn_summaries_all.trxnmid%TYPE,
448 trxntypeid_in IN iby_trxn_summaries_all.trxntypeid%TYPE,
449 org_type_in IN iby_trxn_summaries_all.org_type%TYPE,
450 payment_channel_code_in IN iby_trxn_summaries_all.payment_channel_code%TYPE,
451 factored_flag_in IN iby_trxn_summaries_all.factored_flag%TYPE,
452 reauth_transaction_id IN iby_trxn_summaries_all.TransactionID%TYPE
453 );
454
455
456 /* Checks if a row exists for a set of parameters */
457 /* This function is used by querybatch and query transaction */
458 /* procedures before inserting a new row into the transactions */
459 /* table */
460
461 FUNCTION checkrows
462 (order_id_in IN iby_transactions_v.order_id%TYPE,
463 merchant_id_in IN iby_transactions_v.merchant_id%TYPE,
464 vendor_id_in IN iby_transactions_v.vendor_id%TYPE,
465 status_in IN iby_transactions_v.status%TYPE,
466 trxn_type_in IN iby_transactions_v.trxn_type%TYPE)
467 RETURN number;
468
469
470
471 /*
472 * Inserts a row about batch status into iby_batches_all. This will
473 * be called for link error, timeout error or other batch status with
474 * gateway payment systems. For processor payment systems, this will
475 * create a new batch and associate all trxns in the current, unnamed
476 * open batch with it.
477 */
478 PROCEDURE insert_batch_status
479 (merch_batchid_in IN iby_batches_all.batchid%TYPE,
480 merchant_id_in IN iby_batches_all.payeeid%TYPE,
481 vendor_id_in IN iby_batches_all.bepid%TYPE,
482 vendor_key_in IN iby_batches_all.bepkey%TYPE,
483 pmt_type_in IN iby_batches_all.paymentmethodname%TYPE,
484 status_in IN iby_batches_all.batchstatus%TYPE,
485 time_in IN iby_batches_all.batchclosedate%TYPE DEFAULT SYSDATE,
486 viby_batchid_in IN iby_batches_all.vpsbatchid%TYPE,
487 currency_in IN iby_batches_all.currencynamecode%TYPE DEFAULT NULL,
488 numtrxns_in IN iby_batches_all.NumTrxns%TYPE DEFAULT NULL,
489 batchstate_in IN iby_batches_all.BatchStateid%TYPE DEFAULT NULL,
490 batchtotal_in IN iby_batches_all.BatchTotal%TYPE DEFAULT NULL,
491 saleamount_in IN iby_batches_all.BatchSales%TYPE DEFAULT NULL,
492 cramount_in IN iby_batches_all.BatchCredit%TYPE DEFAULT NULL,
493 gwid_in IN iby_batches_all.GWBatchID%TYPE DEFAULT NULL,
494 vendor_code_in IN iby_batches_all.bepcode%TYPE DEFAULT NULL,
495 vendor_message_in IN iby_batches_all.bepmessage%TYPE DEFAULT NULL,
496 error_location_in IN iby_batches_all.errorlocation%TYPE DEFAULT NULL,
497 terminal_id_in IN iby_batches_all.TerminalId%TYPE DEFAULT NULL,
498 acquirer_id_in IN iby_batches_all.Acquirer%TYPE DEFAULT NULL,
499 org_id_in IN iby_trxn_summaries_all.org_id%type DEFAULT NULL,
500 req_type_in IN iby_batches_all.reqtype%type,
501 sec_key_present_in IN VARCHAR2,
502 mbatchid_out OUT NOCOPY iby_batches_all.mbatchid%type
503 );
504
505 /*
506 * Performs batch close operation by grouping pending transactions.
507 * This method can generate multiple mbatchids for a single
508 * batch close call depending upon how many batches are
509 * generated by applying grouping rules.
510 */
511 PROCEDURE insert_batch_status_new
512 (merch_batchid_in IN iby_batches_all.batchid%TYPE,
513 profile_code_in IN iby_batches_all.process_profile_code%TYPE,
514 merchant_id_in IN iby_batches_all.payeeid%TYPE,
515 vendor_id_in IN iby_batches_all.bepid%TYPE,
516 vendor_key_in IN iby_batches_all.bepkey%TYPE,
517 pmt_type_in IN iby_batches_all.paymentmethodname%TYPE,
518 status_in IN iby_batches_all.batchstatus%TYPE,
519 time_in IN iby_batches_all.batchclosedate%TYPE,
520 viby_batchid_in IN iby_batches_all.vpsbatchid%TYPE ,
521 currency_in IN iby_batches_all.currencynamecode%TYPE,
522 numtrxns_in IN iby_batches_all.NumTrxns%TYPE,
523 batchstate_in IN iby_batches_all.BatchStateid%TYPE,
524 batchtotal_in IN iby_batches_all.BatchTotal%TYPE,
525 saleamount_in IN iby_batches_all.BatchSales%TYPE,
526 cramount_in IN iby_batches_all.BatchCredit%TYPE,
527 gwid_in IN iby_batches_all.GWBatchID%TYPE,
528 vendor_code_in IN iby_batches_all.BEPcode%TYPE,
529 vendor_message_in IN iby_batches_all.BEPmessage%TYPE,
530 error_location_in IN iby_batches_all.errorlocation%TYPE,
531 terminal_id_in IN iby_batches_all.TerminalId%TYPE,
532 acquirer_id_in IN iby_batches_all.Acquirer%TYPE,
533 org_id_in IN iby_trxn_summaries_all.org_id%TYPE,
534 req_type_in IN iby_batches_all.reqtype%TYPE,
535 sec_key_present_in IN VARCHAR2,
536 acct_profile_in IN iby_batches_all.process_profile_code%TYPE,
537 instr_type_in IN iby_batches_all.instrument_type%TYPE,
538 br_disputed_flag_in IN iby_batches_all.br_disputed_flag%TYPE,
539 f_pmt_channel_in IN iby_trxn_summaries_all.
540 payment_channel_code%TYPE,
541 f_curr_in IN iby_trxn_summaries_all.
542 currencynamecode%TYPE,
543 f_settle_date IN iby_trxn_summaries_all.
544 settledate%TYPE,
545 f_due_date IN iby_trxn_summaries_all.
546 settlement_due_date%TYPE,
547 f_maturity_date IN iby_trxn_summaries_all.
548 br_maturity_date%TYPE,
549 f_instr_type IN iby_trxn_summaries_all.
550 instrtype%TYPE,
551 mbatch_ids_out OUT NOCOPY JTF_NUMBER_TABLE,
552 batch_ids_out OUT NOCOPY JTF_VARCHAR2_TABLE_100
553 );
554
555 /* Overloaded form of the above API. This one takes an
556 Array of user profiles instead of a single one.
557 */
558 PROCEDURE insert_batch_status_new
559 (merch_batchid_in IN iby_batches_all.batchid%TYPE,
560 profile_code_array IN JTF_VARCHAR2_TABLE_100,
561 merchant_id_in IN iby_batches_all.payeeid%TYPE,
562 vendor_id_in IN iby_batches_all.bepid%TYPE,
563 vendor_key_in IN iby_batches_all.bepkey%TYPE,
564 pmt_type_in IN iby_batches_all.paymentmethodname%TYPE,
565 status_in IN iby_batches_all.batchstatus%TYPE,
566 time_in IN iby_batches_all.batchclosedate%TYPE,
567 viby_batchid_in IN iby_batches_all.vpsbatchid%TYPE ,
568 currency_in IN iby_batches_all.currencynamecode%TYPE,
569 numtrxns_in IN iby_batches_all.NumTrxns%TYPE,
570 batchstate_in IN iby_batches_all.BatchStateid%TYPE,
571 batchtotal_in IN iby_batches_all.BatchTotal%TYPE,
572 saleamount_in IN iby_batches_all.BatchSales%TYPE,
573 cramount_in IN iby_batches_all.BatchCredit%TYPE,
574 gwid_in IN iby_batches_all.GWBatchID%TYPE,
575 vendor_code_in IN iby_batches_all.BEPcode%TYPE,
576 vendor_message_in IN iby_batches_all.BEPmessage%TYPE,
577 error_location_in IN iby_batches_all.errorlocation%TYPE,
578 terminal_id_in IN iby_batches_all.TerminalId%TYPE,
579 acquirer_id_in IN iby_batches_all.Acquirer%TYPE,
580 org_id_in IN iby_trxn_summaries_all.org_id%TYPE,
581 req_type_in IN iby_batches_all.reqtype%TYPE,
582 sec_key_present_in IN VARCHAR2,
583 acct_profile_in IN iby_batches_all.process_profile_code%TYPE,
584 instr_type_in IN iby_batches_all.instrument_type%TYPE,
585 br_disputed_flag_in IN iby_batches_all.br_disputed_flag%TYPE,
586 f_pmt_channel_in IN iby_trxn_summaries_all.
587 payment_channel_code%TYPE,
588 f_curr_in IN iby_trxn_summaries_all.
589 currencynamecode%TYPE,
590 f_settle_date IN iby_trxn_summaries_all.
591 settledate%TYPE,
592 f_due_date IN iby_trxn_summaries_all.
593 settlement_due_date%TYPE,
594 f_maturity_date IN iby_trxn_summaries_all.
595 br_maturity_date%TYPE,
596 f_instr_type IN iby_trxn_summaries_all.
597 instrtype%TYPE,
598 mbatch_ids_out OUT NOCOPY JTF_NUMBER_TABLE,
599 batch_ids_out OUT NOCOPY JTF_VARCHAR2_TABLE_100
600 );
601
602
603
604 /* Inserts the transaction record for the closebatch operation */
605
606 PROCEDURE insert_batch_txn
607 (ecapp_id_in IN iby_trxn_summaries_all.ECAPPID%TYPE,
608 order_id_in IN iby_transactions_v.order_id%TYPE,
609 merchant_id_in IN iby_transactions_v.merchant_id%TYPE,
610 merch_batchid_in IN iby_transactions_v.MerchBatchID%TYPE,
611 vendor_id_in IN iby_transactions_v.vendor_id%TYPE,
612 vendor_key_in IN iby_transactions_v.bepkey%TYPE,
613 status_in IN iby_transactions_v.status%TYPE,
614 time_in IN iby_transactions_v.time%TYPE
615 DEFAULT sysdate,
616 trxn_type_in IN iby_transactions_v.trxn_type%TYPE,
617 vendor_code_in IN iby_transactions_v.vendor_code%TYPE
618 DEFAULT NULL,
619 vendor_message_in IN iby_transactions_v.vendor_message%TYPE
620 DEFAULT NULL,
621 error_location_in IN iby_transactions_v.error_location%TYPE
622 DEFAULT NULL,
623 trace_number_in IN iby_transactions_v.TraceNumber%TYPE
624 DEFAULT NULL,
625
626 org_id_in IN iby_trxn_summaries_all.org_id%type
627 DEFAULT NULL,
628 transaction_id_out OUT NOCOPY iby_trxn_summaries_all.TransactionID%TYPE);
629
630
631 /* Inserts transaction record for transaction query operation */
632
633
634 PROCEDURE insert_query_txn
635 (transaction_id_in IN iby_trxn_summaries_all.TransactionID%TYPE,
636 order_id_in IN iby_transactions_v.order_id%TYPE,
637 merchant_id_in IN iby_transactions_v.merchant_id%TYPE,
638 vendor_id_in IN iby_transactions_v.vendor_id%TYPE,
639 vendor_key_in IN iby_transactions_v.bepkey%TYPE,
640 status_in IN iby_transactions_v.status%TYPE,
641 time_in IN iby_transactions_v.time%TYPE DEFAULT sysdate,
642 trxn_type_in IN iby_transactions_v.trxn_type%TYPE,
643 amount_in IN iby_transactions_v.amount%TYPE DEFAULT NULL,
644 currency_in IN iby_transactions_v.currency%TYPE DEFAULT NULL,
645 payment_name_in IN iby_transactions_v.payment_name%TYPE DEFAULT NULL,
646 authcode_in IN iby_transactions_v.authcode%TYPE DEFAULT NULL,
647 referencecode_in IN iby_transactions_v.referencecode%TYPE DEFAULT NULL,
648 avscode_in IN iby_transactions_v.AVScode%TYPE DEFAULT NULL,
649 acquirer_in IN iby_transactions_v.acquirer%TYPE DEFAULT NULL,
650 auxmsg_in IN iby_transactions_v.Auxmsg%TYPE DEFAULT NULL,
651 vendor_code_in IN iby_transactions_v.vendor_code%TYPE DEFAULT NULL,
652 vendor_message_in IN iby_transactions_v.vendor_message%TYPE DEFAULT NULL,
653 error_location_in IN iby_transactions_v.error_location%TYPE DEFAULT NULL,
654 trace_number_in IN iby_transactions_v.TraceNumber%TYPE DEFAULT NULL,
655 org_id_in IN iby_trxn_summaries_all.org_id%type DEFAULT NULL,
656 ecappid_in IN iby_ecapp.ecappid%type,
657 req_type_in IN iby_trxn_summaries_all.reqtype%type);
658
659
660
661 /*
662 ** Procedure: getMBatchId
663 ** Purpose: retrieve mBatchid from iby_Batch table based on Batchid
664 */
665 Procedure getMBatchId(i_Batchid in iby_Batches_all.Batchid%type,
666 i_Payeeid in iby_Batches_all.Payeeid%type,
667 o_mBatchid out nocopy iby_Batches_all.mBatchid%type);
668
669 /*
670 ** Function: getTID
671 ** Purpose: get the next trxnid availabe, make sure there is only one TID
672 ** per tangibleid, payeeid combination
673 */
674 Function getTID(i_payeeid in iby_payee.payeeid%type,
675 i_tangibleid in iby_tangible.tangibleid%type)
676 return number;
677
678
679 /*
680 ** Function: getTIDUniqueCheck
681 ** Purpose: If there is already a trxnid available for a
682 ** tangibleid, payeeid combination this method
683 ** returns -1, else it returns a unique trxnid.
684 */
685 Function getTIDUniqueCheck(i_payeeid in iby_payee.payeeid%type,
686 i_tangibleid in iby_tangible.tangibleid%type)
687 return number;
688
689
690 Function getNumPendingTrxns(i_payeeid in iby_payee.payeeid%type,
691 i_tangibleid in iby_tangible.tangibleid%type,
692 i_reqtype in iby_trxn_summaries_all.reqtype%type)
693 return number;
694
695 Function getOrgId(i_tid in iby_trxn_summaries_all.transactionid%type)
696 return number;
697
698 /*
699 * USE: Updates the status of trxns stored in an iPayment internal batch.
700 * This must be done after a batch close so that they are not sent
701 * multiple times.
702 *
703 * ARGS:
704 * 1. the merchant/payee id
705 * 2. the bep/payment system id
706 * 3. the bep key
707 * 4. the old status; used to filter which trxns are in a batch
708 * 5. the new status to which all trxns should be set
709 * 6. the id of the batch to which the trxns will belong
710 */
711 PROCEDURE updateBatchedTrxns
712 (
713 payeeid_in IN iby_trxn_summaries_all.payeeid%TYPE,
714 bepid_in IN iby_trxn_summaries_all.bepid%TYPE,
715 bepkey_in IN iby_trxn_summaries_all.bepkey%TYPE,
716 oldstatus_in IN iby_trxn_summaries_all.status%TYPE,
717 newstatus_in IN iby_trxn_summaries_all.status%TYPE,
718 oldbatchid_in IN iby_trxn_summaries_all.batchid%TYPE,
719 newbatchid_in IN iby_trxn_summaries_all.batchid%TYPE
720 );
721
722 /*
723 * USE: Updates a batched trxn based on batch query values.
724 *
725 * ARGS:
726 * 1. the merchant/payee id
727 * 2. the order tangible id of the trxn
728 * 3. the trxn type id of the trxn
729 * 4. the id of the batch to which the trxn belongs
730 * 5. the new status of the trxn
731 * 6. the final BEP code for the trxn
732 * 7. the final BEP message for the trxn
733 * 8. error location for the trxn
734 *
735 * OUTS:
736 * 9. the trxnid of the given transaction
737 *
738 * NOTES:
739 * For all parameters that default to null- if not
740 * set then the applicable row in the table will not be updated
741 */
742 PROCEDURE updateBatchQueryTrxn
743 (
744 payeeid_in IN iby_trxn_summaries_all.payeeid%TYPE,
745 orderid_in IN iby_trxn_summaries_all.batchid%TYPE,
746 trxn_type_in IN iby_trxn_summaries_all.trxntypeid%TYPE,
747 batchid_in IN iby_trxn_summaries_all.batchid%TYPE,
748 status_in IN iby_trxn_summaries_all.status%TYPE,
749 bep_code_in IN iby_trxn_summaries_all.bepcode%TYPE DEFAULT NULL,
750 bep_msg_in IN iby_trxn_summaries_all.bepmessage%TYPE DEFAULT NULL,
751 error_loc_in IN iby_trxn_summaries_all.errorlocation%TYPE DEFAULT NULL,
752 trxnid_out OUT NOCOPY iby_trxn_summaries_all.transactionid%TYPE
753 );
754
755 /*
756 * USE: Overloaded version of the above method used for batched
757 * authorizations; passes extra fields such as auth code, cvv2
758 * result, etc.
759 *
760 * ARGS:
761 * 9. the auth code
762 * 10. the avs code
763 * 11. the cvv2 result
764 *
765 * OUTS:
766 * 12. the trxnid of the given transaction
767 *
768 * NOTES:
769 * For all parameters that default to null- if not
770 * set then the applicable row in the table will not be updated
771 */
772 PROCEDURE updateBatchQueryTrxn
773 (
774 payeeid_in IN iby_trxn_summaries_all.payeeid%TYPE,
775 orderid_in IN iby_trxn_summaries_all.batchid%TYPE,
776 trxn_type_in IN iby_trxn_summaries_all.trxntypeid%TYPE,
777 batchid_in IN iby_trxn_summaries_all.batchid%TYPE,
778 status_in IN iby_trxn_summaries_all.status%TYPE,
779 bep_code_in IN iby_trxn_summaries_all.bepcode%TYPE DEFAULT NULL,
780 bep_msg_in IN iby_trxn_summaries_all.bepmessage%TYPE DEFAULT NULL,
781 error_loc_in IN iby_trxn_summaries_all.errorlocation%TYPE DEFAULT NULL,
782 authcode_in IN iby_trxn_core.authcode%TYPE DEFAULT NULL,
783 avscode_in IN iby_trxn_core.avscode%TYPE DEFAULT NULL,
784 cvv2result_in IN iby_trxn_core.cvv2result%TYPE DEFAULT NULL,
785 trxnid_out OUT NOCOPY iby_trxn_summaries_all.transactionid%TYPE
786 );
787
788 PROCEDURE Update_Batch
789 (
790 ecapp_id_in IN iby_batches_all.ecappid%TYPE,
791 payeeid_in IN iby_trxn_summaries_all.payeeid%TYPE,
792 batchid_in IN iby_trxn_summaries_all.batchid%TYPE,
793 batch_status_in IN iby_batches_all.batchstatus%TYPE,
794 batch_total_in IN iby_batches_all.batchtotal%TYPE,
795 sale_amount_in IN iby_batches_all.batchsales%TYPE,
796 credit_amount_in IN iby_batches_all.batchcredit%TYPE,
797 bep_code_in IN iby_batches_all.bepcode%TYPE,
798 bep_message_in IN iby_batches_all.bepmessage%TYPE,
799 error_location_in IN iby_batches_all.errorlocation%TYPE,
800 ack_type_in IN VARCHAR2,
801 trxn_orderid_in IN JTF_VARCHAR2_TABLE_100,
802 trxn_reqtype_in IN JTF_VARCHAR2_TABLE_100,
803 trxn_status_in IN JTF_VARCHAR2_TABLE_100,
804 trxn_bep_code_in IN JTF_VARCHAR2_TABLE_100,
805 trxn_bep_msg_in IN JTF_VARCHAR2_TABLE_100,
806 trxn_error_loc_in IN JTF_VARCHAR2_TABLE_100,
807 trxn_authcode_in IN JTF_VARCHAR2_TABLE_100,
808 trxn_avscode_in IN JTF_VARCHAR2_TABLE_100,
809 trxn_cvv2result_in IN JTF_VARCHAR2_TABLE_100,
810 trxn_tracenumber IN JTF_VARCHAR2_TABLE_100
811 );
812
813
814 FUNCTION unencrypt_instr_num
815 (p_instrnum IN iby_trxn_summaries_all.instrnumber%TYPE,
816 p_payee_key IN iby_security_pkg.des3_key_type,
817 p_payee_subkey_cipher IN iby_payee_subkeys.subkey_cipher_text%TYPE,
818 p_sys_key IN RAW,
819 p_sys_subkey_cipher IN iby_sys_security_subkeys.subkey_cipher_text%TYPE,
820 p_segment_id IN iby_security_segments.sec_segment_id%TYPE,
821 p_segment_cipher IN iby_security_segments.segment_cipher_text%TYPE,
822 p_card_prefix IN iby_cc_issuer_ranges.card_number_prefix%TYPE,
823 p_card_len IN iby_cc_issuer_ranges.card_number_length%TYPE,
824 p_digit_check IN iby_creditcard_issuers_b.digit_check_flag%TYPE
825 )
826 RETURN iby_trxn_summaries_all.instrnumber%TYPE;
827
828 /*
829 * USE: Unencrypts the instrument number associated with the given trxn
830 * master id.
831 *
832 * ARGS:
833 * 1. trxn master id
834 * 2. the payee master key for that trxn
835 *
836 * OUTS:
837 * 3. unencrypted instrument number
838 *
839 * NOTES:
840 * For all parameters that default to null- if not
841 * set then the applicable row in the table will not be updated
842 */
843 PROCEDURE unencrypt_instr_num
844 (trxnmid_in IN iby_trxn_summaries_all.trxnmid%TYPE,
845 master_key_in IN iby_security_pkg.DES3_KEY_TYPE,
846 instr_num_out OUT NOCOPY iby_trxn_summaries_all.instrnumber%TYPE
847 );
848
849 /* Functional version of above procedure so that it can be used
850 * 'in-line' in SQL statements.
851 */
852 FUNCTION unencrypt_instr_num
853 (trxnmid_in IN iby_trxn_summaries_all.trxnmid%TYPE,
854 master_key_in IN iby_security_pkg.DES3_KEY_TYPE
855 )
856 RETURN iby_trxn_summaries_all.instrnumber%TYPE;
857
858 /* Wrapper of above function for UI. In the UI the
859 * SQL is executed by the framework. We can not catch
860 * the exception thrown from the function call.
861 * It will cause unacceptable error in the UI.
862 * In case of exceptions this wrapper function will
863 * simply swallow it and return null.
864 * The UI will display empty instrument number
865 * for this case.
866 */
867 FUNCTION unencrypt_instr_num_ui_wrp
868 (
869 trxnmid_in IN iby_trxn_summaries_all.trxnmid%TYPE,
870 master_key_in IN iby_security_pkg.DES3_KEY_TYPE
871 )
872 RETURN iby_trxn_summaries_all.instrnumber%TYPE;
873
874 -- USE
875 -- Encrypts (historical) transactional credit card data
876 --
877 PROCEDURE Encrypt_CC_Data
878 (p_sys_key IN IBY_SECURITY_PKG.DES3_KEY_TYPE,x_err_code OUT NOCOPY VARCHAR2);
879
880 -- USE
881 -- Decrypts (historical) transactional credit card data
882 --
883 PROCEDURE Decrypt_CC_Data
884 (p_sys_key IN IBY_SECURITY_PKG.DES3_KEY_TYPE,x_err_code OUT NOCOPY VARCHAR2);
885
886 /*
887 * USE: Checks if the number of transactions in the current open
888 * batch exceeds the max batch size.
889 * ARGS:
890 * 1. the ecapp id - used to scope the payee id
891 * 2. the payee id - specifies the owning payee of the batch
892 * 3. bep id - specifies the bep associated with the batch
893 * 4. bep key - the account number for the associated batch
894 *
895 * OUTS:
896 * 5. trxn count - number of trxns in the open batch
897 * 6. batch id - the next value from the batch id sequence if the
898 * batch size exceeds its specifified limit; if within
899 * its limits then NULL
900 *
901 */
902 PROCEDURE check_batch_size
903 (
904 ecappid_in IN iby_trxn_summaries_all.ecappid%TYPE,
905 payeeid_in IN iby_trxn_summaries_all.payeeid%TYPE,
906 bepid_in IN iby_trxn_summaries_all.bepid%TYPE,
907 bepkey_in IN iby_trxn_summaries_all.bepkey%TYPE,
908 orgid_in IN iby_batches_all.org_id%TYPE,
909 seckey_present_in IN VARCHAR2,
910 trxncount_out OUT NOCOPY NUMBER,
911 batchid_out OUT NOCOPY iby_batches_all.batchid%TYPE
912 );
913
914 /*--------------------------------------------------------------------
915 | NAME:
916 | performTransactionGrouping
917 |
918 | PURPOSE:
919 |
920 |
921 |
922 | PARAMETERS:
923 | IN
924 |
925 |
926 | OUT
927 |
928 |
929 | RETURNS:
930 |
931 | NOTES:
932 |
933 *---------------------------------------------------------------------*/
934 PROCEDURE performTransactionGrouping(
935 p_profile_code IN IBY_FNDCPT_USER_CC_PF_B.
936 user_cc_profile_code%TYPE,
937 instr_type IN IBY_TRXN_SUMMARIES_ALL.
938 instrtype%TYPE,
939 req_type IN IBY_BATCHES_ALL.
940 reqtype%TYPE,
941 f_pmt_channel_in IN IBY_TRXN_SUMMARIES_ALL.
942 payment_channel_code%TYPE,
943 f_curr_in IN IBY_TRXN_SUMMARIES_ALL.
944 currencynamecode%TYPE,
945 f_settle_date IN IBY_TRXN_SUMMARIES_ALL.
946 settledate%TYPE,
947 f_due_date IN IBY_TRXN_SUMMARIES_ALL.
948 settlement_due_date%TYPE,
949 f_maturity_date IN IBY_TRXN_SUMMARIES_ALL.
950 br_maturity_date%TYPE,
951 f_instr_type IN IBY_TRXN_SUMMARIES_ALL.
952 instrtype%TYPE,
953 x_batchTab IN OUT NOCOPY IBY_TRANSACTIONCC_PKG.
954 batchAttrTabType,
955 x_trxnsInBatchTab IN OUT NOCOPY IBY_TRANSACTIONCC_PKG.
956 trxnsInBatchTabType
957 );
958
959
960 /*--------------------------------------------------------------------
961 | NAME:
962 | performTransactionGrouping
963 |
964 | PURPOSE:
965 | Overloaded form of the earlier API.
966 | Takes an array of user profiles as input
967 | instead of a single one.
968 |
969 | PARAMETERS:
970 | IN
971 |
972 |
973 | OUT
974 |
975 |
976 | RETURNS:
977 |
978 | NOTES:
979 |
980 *---------------------------------------------------------------------*/
981 PROCEDURE performTransactionGrouping(
982 profile_code_array IN JTF_VARCHAR2_TABLE_100,
983 instr_type IN IBY_TRXN_SUMMARIES_ALL.
984 instrtype%TYPE,
985 req_type IN IBY_BATCHES_ALL.
986 reqtype%TYPE,
987 f_pmt_channel_in IN IBY_TRXN_SUMMARIES_ALL.
988 payment_channel_code%TYPE,
989 f_curr_in IN IBY_TRXN_SUMMARIES_ALL.
990 currencynamecode%TYPE,
991 f_settle_date IN IBY_TRXN_SUMMARIES_ALL.
992 settledate%TYPE,
993 f_due_date IN IBY_TRXN_SUMMARIES_ALL.
994 settlement_due_date%TYPE,
995 f_maturity_date IN IBY_TRXN_SUMMARIES_ALL.
996 br_maturity_date%TYPE,
997 f_instr_type IN IBY_TRXN_SUMMARIES_ALL.
998 instrtype%TYPE,
999 merch_batchid_in IN iby_batches_all.batchid%TYPE,
1000
1001 x_batchTab IN OUT NOCOPY IBY_TRANSACTIONCC_PKG.
1002 batchAttrTabType,
1003 x_trxnsInBatchTab IN OUT NOCOPY IBY_TRANSACTIONCC_PKG.
1004 trxnsInBatchTabType
1005 );
1006
1007 /*--------------------------------------------------------------------
1008 | NAME:
1009 | insertTrxnIntoBatch
1010 |
1011 | PURPOSE:
1012 | Inserts a given transaction into a currently running batch
1013 | or into a new batch as per given flag.
1014 |
1015 | This method is called by every grouping rule to add
1016 | a given transaction into a current batch/new batch.
1017 |
1018 | PARAMETERS:
1019 | IN
1020 |
1021 |
1022 | OUT
1023 |
1024 |
1025 | RETURNS:
1026 |
1027 | NOTES:
1028 |
1029 *---------------------------------------------------------------------*/
1030 PROCEDURE insertTrxnIntoBatch(
1031 x_batchRec IN OUT NOCOPY batchAttrRecType,
1032 x_batchTab IN OUT NOCOPY batchAttrTabType,
1033 p_newBatchFlag IN BOOLEAN,
1034 x_currentBatchId IN OUT NOCOPY IBY_BATCHES_ALL.batchid%TYPE,
1035 x_trxnsInBatchTab IN OUT NOCOPY trxnsInBatchTabType,
1036 x_trxnsInBatchRec IN OUT NOCOPY trxnsInBatchRecType,
1037 x_trxnsInBatchCount IN OUT NOCOPY NUMBER
1038 );
1039
1040 /*--------------------------------------------------------------------
1041 | NAME:
1042 | getNextBatchId
1043 |
1044 | PURPOSE:
1045 | Returns the next batch id from a sequence. These ids are
1046 | used to uniquely number batches.
1047 |
1048 | PARAMETERS:
1049 | IN
1050 |
1051 |
1052 | OUT
1053 |
1054 |
1055 | RETURNS:
1056 |
1057 | NOTES:
1058 |
1059 *---------------------------------------------------------------------*/
1060 PROCEDURE getNextBatchId(
1061 x_batchID IN OUT NOCOPY IBY_BATCHES_ALL.batchid%TYPE
1062 );
1063
1064 /*--------------------------------------------------------------------
1065 | NAME:
1066 | print_debuginfo
1067 |
1068 | PURPOSE:
1069 | This procedure prints the debug message to the concurrent manager
1070 | log file.
1071 |
1072 | PARAMETERS:
1073 | IN
1074 | p_debug_text - The debug message to be printed
1075 |
1076 | OUT
1077 |
1078 |
1079 | RETURNS:
1080 |
1081 | NOTES:
1082 |
1083 *---------------------------------------------------------------------*/
1084 PROCEDURE print_debuginfo(
1085 p_module IN VARCHAR2,
1086 p_debug_text IN VARCHAR2
1087 );
1088
1089 /*--------------------------------------------------------------------
1090 | NAME:
1091 | Update_Payer_Notif_Batch
1092 |
1093 | PURPOSE:
1094 | This procedure updates the payer_notification_required flag for
1095 | all the transactions in a batch.
1096 |
1097 | PARAMETERS:
1098 | IN
1099 | p_debug_text - The debug message to be printed
1100 |
1101 | OUT
1102 |
1103 |
1104 | RETURNS:
1105 |
1106 | NOTES:
1107 |
1108 *---------------------------------------------------------------------*/
1109 PROCEDURE Update_Payer_Notif_Batch(
1110 mbatchid_in IN iby_batches_all.mbatchid%TYPE
1111 );
1112
1113 /*--------------------------------------------------------------------
1114 | NAME:
1115 |
1116 | PURPOSE:
1117 | This procedure is used to free up the memory used by
1118 | global memory structure [trxnTab]
1119 |
1120 | PARAMETERS:
1121 |
1122 | NONE
1123 |
1124 | RETURNS:
1125 |
1126 | NOTES:
1127 |
1128 *---------------------------------------------------------------------*/
1129 PROCEDURE delete_trxnTable;
1130
1131 END iby_transactioncc_pkg;
1132
1133
[Home] [Help]