1 package body iby_queryset_pkg as
2 /*$Header: ibyqsetb.pls 115.11 2002/11/19 23:50:17 jleybovi ship $*/
3
4 /* This procedure gets the trxn info for this particular */
5 /* trxn. Given a merchant_id, order_id, split_id, */
6 /* payment_operation, and vendor_id, it returns */
7 /* the status (if the operation occurred before -- useful for*/
8 /* determining retries) and the set_trxn_id to indicate the */
9 /* parent SET trxn to the vendor. */
10
11 PROCEDURE get_settrxninfo
12 (merchant_id_in IN IBY_Payee.PayeeID%TYPE,
13 order_id_in IN iby_trxn_summaries_all.TangibleID%TYPE,
14 split_id_in IN iby_trxn_extended.SplitID%TYPE,
15 payment_operation_in IN iby_trxn_summaries_all.PaymentMethodName%TYPE,
16 vendor_id_in IN IBY_BEPInfo.BEPID%TYPE,
17 status_out OUT NOCOPY iby_trxn_summaries_all.Status%TYPE,
18 prev_set_trxn_id_in_out IN OUT NOCOPY iby_trxn_extended.SETTrxnID%TYPE,
19 price_out OUT NOCOPY iby_trxn_summaries_all.Amount%TYPE,
20 currency_out OUT NOCOPY iby_trxn_summaries_all.CurrencyNameCode%TYPE,
21 previous_price_out OUT NOCOPY iby_trxn_summaries_all.Amount%TYPE,
22 previous_currency_out OUT NOCOPY iby_trxn_summaries_all.CurrencyNameCode%TYPE)
23 IS
24 in_trxn_type NUMBER;
25 num_trxns NUMBER;
26 num_succ_trxns NUMBER;
27 max_time DATE;
28 BEGIN
29 -- NULL or make zero output parameters just in case
30 status_out := NULL;
31 price_out := NULL;
32 currency_out := NULL;
33 previous_price_out := NULL;
34 previous_currency_out := NULL;
35 in_trxn_type := NULL;
36 -- If not a query operation, get status of current trxn, if it's
37 -- already in database
38 IF (UPPER(payment_operation_in) = 'ORASET_BATCHADMIN')
39 THEN
40 -- Trxn is a batch operation, so look into the batch
41 -- table to get the status of the previous operation,
42 -- if any, to help set the RETRY flag
43 SELECT count(*)
44 INTO num_trxns
45 FROM iby_batches_all
46 WHERE BatchID = order_id_in;
47 IF (num_trxns > 0)
48 THEN
49 SELECT BatchStatus
50 INTO status_out
51 FROM iby_batches_all
52 WHERE BatchID = order_id_in;
53 END IF;
54 ELSIF ((UPPER(payment_operation_in) <> 'ORASET_QRYBATCHSTATUS') AND
55 (UPPER(payment_operation_in) <> 'ORASET_TRXNSTATUSQUERY'))
56 THEN
57 -- Not a batch or query operation, so get the status from
58 -- the SET table
59 getStatus_SET(order_id_in, merchant_id_in, UPPER(payment_operation_in), split_id_in, status_out);
60 -- get previous price information
61 getAmount_SET(order_id_in, merchant_id_in, UPPER(payment_operation_in), split_id_in, previous_price_out,previous_currency_out);
62 -- Do additional processing per operation to get previous SET trxn id
63 IF (UPPER(payment_operation_in) = 'ORASET_AUTH')
64 THEN
65 -- It's a subsequent auth, so raise an error since this
66 -- procedure is not to be used for this case
67
68 raise_application_error(-20000, 'IBY_20311#', FALSE);
69 --raise_application_error(-20311,'GET_SETTRXNID: This procedure does not handle split auths, use processSplitAuth instead');
70 ELSIF (UPPER(payment_operation_in) = 'ORASET_AUTHREV')
71 THEN
72 -- It's an auth reversal, so parent will be
73 -- an auth (VeriFone doesn't support authrev of subsequentauth)
74 -- dbms_output.put_line('In ORASET_AUTHREV, m_id, o_id, m_tx_id: '||merchant_id_in||' '||order_id_in||' '||split_id_in);
75
76 -- See how many trxns of type SET (PREQ=2) or AUTH
77 -- of this order_id, trxn_id occurred before
78 SELECT count(*)
79 INTO num_trxns
80 FROM iby_transactions_set_v
81 WHERE order_id = order_id_in
82 AND merchant_id = merchant_id_in
83 AND trxn_type IN (2)
84 AND split_id = split_id_in
85 AND (request_type IN (2,4)
86 OR
87 request_type IS NULL)
88 AND status = 0;
89 -- dbms_output.put_line('Num m_trx_ids found for oid,mid,mtxid combo: '||num_trxns);
90 IF (num_trxns = 1)
91 THEN
92 -- Only one matching trxn, so get the SET trxn id
93 -- dbms_output.put_line('Getting SET Trxn ID');
94 SELECT TRX.set_trxn_id, TRX.amount, TRX.currency
95 INTO prev_set_trxn_id_in_out,price_out,currency_out
96 FROM iby_transactions_set_v TRX
97 WHERE order_id = order_id_in
98 AND merchant_id = merchant_id_in
99 AND trxn_type IN (2)
100 AND split_id = split_id_in
101 AND (request_type IN (2,4)
102 OR
103 request_type IS NULL)
104 AND status = 0;
105 -- dbms_output.put_line('SET trxn id: '||prev_set_trxn_id_in_out);
106 ELSIF (num_trxns = 0)
107 THEN
108 -- No matching trxns, so return error
109 raise_application_error(-20000, 'IBY_20312#', FALSE);
110 --raise_application_error(-20312,'No authorization matching given order_id '||order_id_in||', split_id '||split_id_in||' to be voided');
111 ELSE
112 -- Too many transactions match
113 raise_application_error(-20000, 'IBY_20313#', FALSE);
114 --raise_application_error(-20313,num_trxns || ' duplicate transactions matching given order_id, split_id: '||order_id_in||' '||split_id_in||' to be voided');
115 END IF;
116 ELSIF (UPPER(payment_operation_in) = 'ORASET_CAPTURE')
117 THEN
118 -- It's a capture, so parent will be
119 -- an auth, authrev or subsequentauth
120 -- dbms_output.put_line('In ORASET_CAPTURE, m_id, o_id, m_tx_id: '||merchant_id_in||' '||order_id_in||' '||split_id_in);
121 -- See how many trxns of type SET (PREQ=2) or AUTH
122 -- of this order_id, trxn_id occurred before
123 SELECT count(*)
124 INTO num_trxns
125 FROM iby_transactions_set_v
126 WHERE order_id = order_id_in
127 AND merchant_id = merchant_id_in
128 AND trxn_type IN (2,4,101) -- auth, authrev, or subsequentauth
129 AND split_id = split_id_in
130 AND (request_type IN (2,4)
131 OR
132 request_type IS NULL)
133 AND status = 0;
134 -- dbms_output.put_line('Num m_trx_ids found for oid,mid,mtxid combo: '||num_trxns);
135 IF (num_trxns = 2)
136 THEN
137 -- If two transactions match, then get the authrev, which will be
138 -- later than the auth or the subsequentauth
139 SELECT count(*)
140 INTO num_trxns
141 FROM iby_transactions_set_v
142 WHERE order_id = order_id_in
143 AND merchant_id = merchant_id_in
144 AND trxn_type = 4 -- authrev
145 AND split_id = split_id_in
146 AND (request_type IN (2,4)
147 OR
148 request_type IS NULL)
149 AND status = 0;
150 IF (num_trxns = 1)
151 THEN
152 -- Only one matching trxn, so get the SET trxn id
153 -- dbms_output.put_line('Getting SET Trxn ID');
154 SELECT TRX.set_trxn_id, TRX.amount, TRX.currency
155 INTO prev_set_trxn_id_in_out,price_out,currency_out
156 FROM iby_transactions_set_v TRX
157 WHERE order_id = order_id_in
158 AND merchant_id = merchant_id_in
159 AND trxn_type = 4 --authrev
160 AND split_id = split_id_in
161 AND (request_type IN (2,4)
162 OR
163 request_type IS NULL)
164 AND status = 0;
165
166 -- dbms_output.put_line('SET trxn id: '||prev_set_trxn_id_in_out);
167
168 ELSIF (num_trxns = 0)
169 THEN
170 -- No matching trxns, so return error
171 raise_application_error(-20000, 'IBY_20314#', FALSE);
172 --raise_application_error(-20314,'No void (of authorization) matching given order_id '||order_id_in||', split_id '||split_id_in||' to capture');
173 ELSE
174 -- Too many transactions match
175 raise_application_error(-20000, 'IBY_20315#', FALSE);
176 --raise_application_error(-20315,num_trxns||' duplicate void of auth transactions matching given order_id, split_id: '||order_id_in||' '||split_id_in||' to capture');
177 END IF;
178 ELSIF (num_trxns = 1)
179 THEN
180 -- Only one matching trxn, so get the SET trxn id
181 -- dbms_output.put_line('Getting SET Trxn ID');
182
183 SELECT TRX.set_trxn_id, TRX.amount, TRX.currency
184 INTO prev_set_trxn_id_in_out,price_out,currency_out
185 FROM iby_transactions_set_v TRX
186 WHERE order_id = order_id_in
187 AND merchant_id = merchant_id_in
188 AND trxn_type IN (2, 4, 101)
189 AND split_id = split_id_in
190 AND (request_type IN (2,4)
191 OR
192 request_type IS NULL)
193 AND status = 0;
194 -- dbms_output.put_line('SET trxn id: '||prev_set_trxn_id_in_out);
195 ELSIF (num_trxns = 0)
196 THEN
197 -- No matching trxns, so return error
198 raise_application_error(-20000, 'IBY_20316#', FALSE);
199 --raise_application_error(-20316,'No authorization matching given order_id '||order_id_in||', split_id '||split_id_in||'merchant_id_in'||merchant_id_in||' to capture');
200 ELSE
201 -- Too many transactions match
202 raise_application_error(-20000, 'IBY_20317#', FALSE);
203 --raise_application_error(-20317,num_trxns||' duplicate transactions matching given order_id, split_id: '||order_id_in||' '||split_id_in);
204 END IF;
205 ELSIF ((UPPER(payment_operation_in) = 'ORASET_CAPREV') OR
206 (UPPER(payment_operation_in) = 'ORASET_CREDIT'))
207 THEN
208 -- It's a capture reversal or credit operation, so parent
209 -- trxn will be a capture or an auth capture
210 -- dbms_output.put_line('In ORASET_CAPREV or CREDIT, m_id, o_id, m_tx_id: '||merchant_id_in||' '||order_id_in||' '||split_id_in);
211 -- See if trxn is duplicate
212 SELECT count(*)
213 INTO num_trxns
214 FROM iby_transactions_set_v
215 WHERE order_id = order_id_in
216 AND merchant_id = merchant_id_in
217 AND ( trxn_type IN (8, 9)
218 OR
219 (trxn_type = 3 AND request_type IN (2,4)))
220 AND split_id = split_id_in;
221 -- dbms_output.put_line('Num m_trx_ids found for oid,mid,mtxid combo: '||num_trxns);
222 IF (num_trxns = 1)
223 THEN
224 -- dbms_output.put_line('Getting SET Trxn ID');
225
226 SELECT TRX.set_trxn_id, TRX.amount, TRX.currency
227 INTO prev_set_trxn_id_in_out,price_out,currency_out
228 FROM iby_transactions_set_v TRX
229 WHERE order_id = order_id_in
230 AND merchant_id = merchant_id_in
231 AND ( trxn_type IN (8, 9)
232 OR
233 (trxn_type = 3 AND request_type IN (2,4)))
234 AND split_id = split_id_in;
235 -- dbms_output.put_line('SET trxn id: '||prev_set_trxn_id_in_out);
236 ELSIF (num_trxns = 0)
237 THEN
238 raise_application_error(-20000, 'IBY_20318#', FALSE);
239 --raise_application_error(-20318,'No CAPTURE trxn matching given order_id '||order_id_in||', split_id '||split_id_in||' to to void or credit');
240 ELSE
241 -- Too many transactions match
242 raise_application_error(-20000, 'IBY_20319#', FALSE);
243 --raise_application_error(-20319,num_trxns||' duplicate transactions '||num_trxns||' matching given order_id, split_id: '||order_id_in||' '||split_id_in);
244 END IF;
245 ELSIF (UPPER(payment_operation_in) = 'ORASET_CREDITREV')
246 THEN
247 -- It's a credit reversal, so parent will be a credit
248 -- dbms_output.put_line('In ORASET_CREDITREV, m_id, o_id, m_tx_id: '||merchant_id_in||' '||order_id_in||' '||split_id_in);
249 -- See if trxn is duplicate
250 SELECT count(*)
251 INTO num_trxns
252 FROM iby_transactions_set_v
253 WHERE order_id = order_id_in
254 AND merchant_id = merchant_id_in
255 AND trxn_type = 5
256 AND split_id = split_id_in;
257 -- dbms_output.put_line('Num m_trx_ids found for oid,mid,mtxid combo: '||num_trxns);
258 IF (num_trxns = 1)
259 THEN
260 -- dbms_output.put_line('Getting SET Trxn ID');
261
262 SELECT TRX.set_trxn_id, TRX.amount, TRX.currency
263 INTO prev_set_trxn_id_in_out,price_out,currency_out
264 FROM iby_transactions_set_v TRX
265 WHERE order_id = order_id_in
266 AND merchant_id = merchant_id_in
267 AND trxn_type = 5
268 AND split_id = split_id_in;
269 -- dbms_output.put_line('SET trxn id: '||prev_set_trxn_id_in_out);
270 ELSIF (num_trxns = 0)
271 THEN
272 raise_application_error(-20000, 'IBY_20320#', FALSE);
273 --raise_application_error(-20320,'No RETURN/CREDIT trxn matching given order_id '||order_id_in||', split_id '||split_id_in||' to be voided');
274 ELSE
275 -- Too many transactions match
276 raise_application_error(-20000, 'IBY_20321#', FALSE);
277 --raise_application_error(-20321,num_trxns||' duplicate RETURN/CREDIT transactions matching given order_id, split_id: '||order_id_in||' '||split_id_in);
278 END IF;
279 END IF;
280 END IF;
281 END get_settrxninfo;
282
283
284 /* Internal procedure to get the vendor configuration by the */
285 /* payment name. */
286 /** was this procedure used any where??? --jjwu*/
287
288 /* -- no longer supported
289 PROCEDURE getVendorByPmtName
290 (payment_name_in IN iby_routinginfo.PaymentMethodName%TYPE,
291 p_id IN OUT NOCOPY iby_routinginfo.PaymentMethodID%TYPE,
292 v_id IN OUT NOCOPY IBY_BEPInfo.BEPID%TYPE,
293 v_suffix OUT NOCOPY IBY_BEPInfo.suffix%TYPE,
294 v_base_url OUT NOCOPY IBY_BEPInfo.BaseURL%TYPE,
295 v_pmtscheme IN OUT NOCOPY iby_pmtschemes.PmtSchemeName%TYPE)
296 IS
297 BEGIN
298
299 -- Get bep info based on paymentmethod name
300 SELECT ROUT.bepid , BEP.baseurl, BEP.suffix
301 INTO v_id, v_base_url, v_suffix
302 FROM iby_bepinfo BEP, iby_routinginfo ROUT
303 WHERE UPPER(ROUT.paymentmethodname) = UPPER(payment_name_in)
304 AND BEP.bepid = ROUT.bepid
305 AND ROUT.configured = 1;
306
307
308 iby_pmtschemes_pkg.getPmtSchemeName(v_id, v_pmtscheme);
309
310 EXCEPTION
311
312 WHEN NO_DATA_FOUND THEN
313
314 IF p_id IS NULL
315 THEN
316 raise_application_error(-20000, 'IBY_20331#', FALSE);
317 --raise_application_error(-20331,'Invalid payment method (OapfPmtType).' || UPPER(payment_name_in));
318 END IF;
319
320 raise_application_error(-20000, 'IBY_20330#', FALSE);
321 --raise_application_error(-20330,'NO DATA FOUND error in getVendorByPmtName.');
322
323 END getVendorByPmtName;
324 */
325
326
327 /* Procedure used to get the status for a particular SET */
328 /* trxn. The status_out will be set to some value if the */
329 /* order occurred previously, else it'll be null. */
330 PROCEDURE getStatus_SET
331 (order_id_in IN iby_trxn_summaries_all.TangibleID%TYPE,
332 merchant_id_in IN IBY_Payee.PayeeID%TYPE,
336 IS
333 payment_operation_in IN VARCHAR2,
334 split_id_in IN iby_trxn_extended.SplitID%TYPE,
335 status_out OUT NOCOPY iby_trxn_summaries_all.Status%TYPE)
337 loc_status iby_transactions_set_v.status%TYPE;
338 num_trxns NUMBER;
339 max_time DATE;
340 CURSOR s1(tx1 NUMBER,
341 tx2 NUMBER DEFAULT NULL) IS
342 SELECT status
343 FROM iby_transactions_set_v
344 WHERE order_id = order_id_in
345 AND merchant_id = merchant_id_in
346 AND trxn_type IN (tx1, tx2)
347 AND split_id = split_id_in
348 ORDER BY time DESC;
349 BEGIN
350
351 -- Depending on the payment operation the trxn_type varies
352 -- So we need to select based on this set
353 -- For an explanation of various trxn_types refer the design doc
354
355 IF (payment_operation_in <> 'ORASET_SET')
356 THEN
357 -- Handle normally
358 IF payment_operation_in = 'ORASET_INIT' THEN
359 OPEN s1(0);
360 ELSIF payment_operation_in = 'ORASET_AUTHREV' THEN
361 OPEN s1(4,7);
362 ELSIF payment_operation_in = 'ORASET_CAPTURE' THEN
363 OPEN s1(8);
364 ELSIF payment_operation_in = 'ORASET_CAPREV' THEN
365 OPEN s1(13);
366 ELSIF payment_operation_in = 'ORASET_CREDIT' THEN
367 OPEN s1(5);
368 ELSIF payment_operation_in = 'ORASET_CREDITREV' THEN
369 OPEN s1(17);
370 END IF;
371
372 -- Fetch the first row to check the status
373 FETCH s1 INTO loc_status;
374 IF s1%FOUND THEN
375 -- Make sure it's not an empty status
376 IF (loc_status IS NULL)
377 THEN
378 status_out := 9999;
379 ELSE
380 status_out := loc_status;
381 END IF;
382
383 ELSE
384 -- No rows found
385 status_out := NULL;
386 END IF;
387
388 CLOSE s1;
389 ELSE
390 -- oraset_set requires special handling because in the SET protocol,
391 -- TWO oraset_set trxns are logged into the DB: one for
392 -- PInitReq (request_type=1) and one for PReq (request_type=2)
393 -- Get number of PReq trxns
394 SELECT count(*)
395 INTO num_trxns
396 FROM iby_transactions_set_v
397 WHERE order_id = order_id_in
398 AND merchant_id = merchant_id_in
399 AND trxn_type in (2,3)
400 AND request_type = 2;
401 -- dbms_output.put_line('Num PREQ trxns: '||num_trxns);
402 IF (num_trxns = 0)
403 THEN
404 status_out := null;
405
406 ELSE
407 -- A previous PReq was found
408 SELECT status
409 INTO loc_status
410 FROM iby_transactions_set_v
411 WHERE order_id = order_id_in
412 AND merchant_id = merchant_id_in
413 AND trxn_type in (2,3)
414 AND request_type = 2
415 ORDER BY TIME DESC;
416 IF (loc_status IS NULL)
417 THEN
418 status_out := 9999;
419 ELSE
420 status_out := loc_status;
421 END IF;
422 END IF;
423 END IF;
424 END getStatus_SET;
425
426
427 /* Procedure used to get the status for a particular SET */
428 /* trxn. The status_out will be set to some value if the */
429 /* order occurred previously, else it'll be null. */
430 PROCEDURE getAmount_SET
431 (order_id_in IN iby_trxn_summaries_all.TangibleID%TYPE,
432 merchant_id_in IN IBY_Payee.PayeeID%TYPE,
433 payment_operation_in IN VARCHAR2,
434 split_id_in IN iby_trxn_extended.SplitID%TYPE,
435 price_out OUT NOCOPY iby_trxn_summaries_all.Amount%TYPE,
436 currency_out OUT NOCOPY iby_trxn_summaries_all.CurrencyNameCode%TYPE)
437 IS
438 num_trxns NUMBER;
439 max_time DATE;
440 CURSOR s1(tx1 NUMBER,
441 tx2 NUMBER DEFAULT NULL) IS
442 SELECT amount,currency
443 FROM iby_transactions_set_v
444 WHERE order_id = order_id_in
445 AND merchant_id = merchant_id_in
446 AND trxn_type IN (tx1, tx2)
447 AND split_id = split_id_in
448 ORDER BY time DESC;
449 BEGIN
450
451 -- Depending on the payment operation the trxn_type varies
452 -- So we need to select based on this set
453 -- For an explanation of various trxn_types refer the design doc
454 price_out := NULL;
455 currency_out := NULL;
456
457 IF (payment_operation_in <> 'ORASET_SET')
458 THEN
459 -- Handle normally
460 IF payment_operation_in = 'ORASET_INIT' THEN
461 OPEN s1(0);
462 ELSIF payment_operation_in = 'ORASET_AUTH' THEN
463 OPEN s1(101);
464 ELSIF payment_operation_in = 'ORASET_AUTHREV' THEN
465 OPEN s1(4,7);
466 ELSIF payment_operation_in = 'ORASET_CAPTURE' THEN
467 OPEN s1(8);
468 ELSIF payment_operation_in = 'ORASET_CAPREV' THEN
469 OPEN s1(13);
470 ELSIF payment_operation_in = 'ORASET_CREDIT' THEN
471 OPEN s1(5);
475
472 ELSIF payment_operation_in = 'ORASET_CREDITREV' THEN
473 OPEN s1(17);
474 END IF;
476 -- Fetch the first row to check the status
477 FETCH s1 INTO price_out,currency_out;
478 CLOSE s1;
479 ELSE
480 -- oraset_set requires special handling because in the SET protocol,
481 -- TWO oraset_set trxns are logged into the DB: one for
482 -- PInitReq (request_type=1) and one for PReq (request_type=2)
483 -- Get number of PReq trxns
484 SELECT count(*)
485 INTO num_trxns
486 FROM iby_transactions_set_v
487 WHERE order_id = order_id_in
488 AND merchant_id = merchant_id_in
489 AND trxn_type in (2,3)
490 AND request_type = 2;
491 -- dbms_output.put_line('Num PREQ trxns: '||num_trxns);
492 IF (num_trxns > 0)
493 THEN
494 -- A previous PReq was found
495 SELECT amount,currency
496 INTO price_out, currency_out
497 FROM iby_transactions_set_v
498 WHERE order_id = order_id_in
499 AND merchant_id = merchant_id_in
500 AND trxn_type in (2,3)
501 AND request_type = 2
502 ORDER BY TIME DESC;
503 END IF;
504 END IF;
505 END getAmount_SET;
506
507
508 /* Procedure used for orasubsequentauth instead of the */
509 /* getStatus_SET procedure. It performs some special */
510 /* processing. */
511
512 PROCEDURE processsplitauth
513 (merchant_id_in IN IBY_Payee.PayeeID%TYPE,
514 order_id_in IN iby_trxn_summaries_all.TangibleID%TYPE,
515 prev_split_id_in IN iby_trxn_extended.SPlitID%TYPE,
516 split_id_in IN iby_trxn_extended.SPlitID%TYPE,
517 vendor_id_in IN IBY_Payee.PayeeID%TYPE,
518 status_out OUT NOCOPY iby_trxn_summaries_all.Status%TYPE,
519 set_trxn_id_out IN OUT NOCOPY iby_trxn_extended.SETTrxnID%TYPE,
520 previous_price_out OUT NOCOPY iby_trxn_summaries_all.Amount%TYPE,
521 previous_currency_out OUT NOCOPY iby_trxn_summaries_all.CurrencyNameCode%TYPE)
522 IS
523 num_trxns NUMBER;
524 max_time DATE;
525 num_succ_trxns NUMBER;
526 auth_ind NUMBER;
527 BEGIN
528 num_trxns := NULL;
529 max_time := NULL;
530 num_succ_trxns := NULL;
531 -- get previous price information
532 getAmount_SET(order_id_in, merchant_id_in,'ORASET_AUTH', split_id_in, previous_price_out,previous_currency_out);
533 -- See if trxn is duplicate
534 SELECT count(*)
535 INTO num_trxns
536 FROM iby_transactions_set_v
537 WHERE order_id = order_id_in
538 AND merchant_id = merchant_id_in
539 AND split_id = split_id_in
540 AND trxn_type = 101;
541 -- dbms_output.put_line('Num m_trx_ids found for oid,mid,mtxid combo: '||num_trxns);
542
543 IF (num_trxns > 0)
544 THEN
545 -- Duplicate transaction found, so return status of that
546 -- transaction
547 -- dbms_output.put_line('Dup txn id');
548 SELECT status
549 INTO status_out
550 FROM iby_transactions_set_v
551 WHERE order_id = order_id_in
552 AND merchant_id = merchant_id_in
553 AND split_id = split_id_in
554 AND trxn_type = 101;
555 END IF;
556
557 -- Get count number of successful parent set trxn ids
558 SELECT count(*)
559 INTO num_trxns
560 FROM iby_transactions_set_v
561 WHERE order_id = order_id_in
562 AND merchant_id = merchant_id_in
563 AND split_id = prev_split_id_in
564 AND trxn_type IN (2, 101)
565 AND (request_type IN (2,4)
566 OR
567 request_type IS NULL)
568 AND status = 0;
569 IF (num_trxns = 0)
570 THEN
571 raise_application_error(-20000, 'IBY_20391#', FALSE);
572 --raise_application_error(-20391,'processSplitAuth: Either parent not found or parent not successful. Please check parameters and perhaps query trxn status');
573 ELSE
574 -- dbms_output.put_line('found # of parents: '||num_trxns);
575 -- Get previous SET transaction id and
576 -- subsequent authorization indicator
577 SELECT set_trxn_id, subseq_auth_ind
578 INTO set_trxn_id_out, auth_ind
579 FROM iby_transactions_set_v
580 WHERE order_id = order_id_in
581 AND merchant_id = merchant_id_in
582 AND split_id = prev_split_id_in
583 AND trxn_type IN (2,101)
584 AND (request_type IN (2,4)
585 OR
586 request_type IS NULL)
587 AND status = 0;
588
589 -- dbms_output.put_line('Prev SET trxn id: '||set_trxn_id_out);
590 -- Check to see if the previous transaction allows a split.
591 -- If not, return an error.
592 IF (auth_ind <> 1)
593 THEN
594 raise_application_error(-20000, 'IBY_20392#', FALSE);
595 --raise_application_error(-20392,'Previous transaction does not allow a subsequent authorization - subseq_auth_ind is not 1');
596 END IF;
597 END IF;
598 END processSplitAuth;
599
600 END iby_queryset_pkg;
[Home] [Help]