1 package body iby_querycc_pkg as
2 /*$Header: ibyqrccb.pls 115.35 2003/04/29 18:30:58 jleybovi ship $*/
3
4 PROCEDURE listvpsid (merchant_id_in IN VARCHAR2,
5 batch_id_in IN VARCHAR2,
6 vendor_id_in IN NUMBER,
7 vendor_key_in IN VARCHAR2,
8 viby_id OUT NOCOPY VARCHAR2)
9
10 IS
11
12 loc_vpsid iby_batches_all.VPSBatchID%TYPE;
13 BEGIN
14
15 loc_vpsid := NULL;
16
17 SELECT distinct VPSBatchID
18 INTO loc_vpsid
19 FROM iby_batches_all
20 WHERE payeeid = merchant_id_in AND
21 BatchID = batch_id_in AND
22 bepid = vendor_id_in AND
23 bepkey = vendor_key_in;
24
25 viby_id := loc_vpsid;
26
27 EXCEPTION
28 WHEN NO_DATA_FOUND THEN
29 IF loc_vpsid IS NULL
30 THEN
31 raise_application_error(-20000, 'IBY_20209#', FALSE);
32 --raise_application_error(-20209,'No VPSBatchID for MerchBatchID');
33 END IF;
34
35 END listvpsid;
36
37
38 PROCEDURE checkunqorder
39 (order_id_in IN VARCHAR2,
40 merchant_id_in IN VARCHAR2,
41 payment_operation IN VARCHAR2,
42 trxn_type_in IN NUMBER,
43 check_status_out OUT NOCOPY NUMBER,
44 parent_trace_number_out OUT NOCOPY VARCHAR2,
45 bepid_out OUT NOCOPY NUMBER,
46 bepkey_out OUT NOCOPY VARCHAR2,
47 trxnid_out OUT NOCOPY NUMBER,
48 trxnref_out OUT NOCOPY VARCHAR2
49 )
50
51 IS
52
53 loc_status iby_transactions_v.status%TYPE;
54
55 CURSOR s1(i_reqtype1 iby_trxn_summaries_all.reqtype%type,
56 i_reqtype2 iby_trxn_summaries_all.reqtype%type DEFAULT
57 NULL)
58 IS
59 SELECT status, bepid, bepkey, transactionid, trxnref
60 FROM iby_trxn_summaries_all
61 WHERE tangibleid = order_id_in
62 AND payeeid = merchant_id_in
63 AND reqtype IN (i_reqtype1, i_reqtype2)
64 AND reqtype IS NOT NULL -- to ignore the 2nd arg
65 ORDER BY reqdate DESC;
66 -- first one is always the latest
67
68 CURSOR s2(tx1 NUMBER,
69 tx2 NUMBER DEFAULT NULL) IS
70 SELECT tracenumber
71 FROM iby_transactions_v
72 WHERE order_Id = order_id_in
73 AND merchant_id = merchant_id_in
74 AND trxn_type IN (tx1, tx2)
75 ORDER BY time DESC;
76
77 BEGIN
78
79 -- Depending on the payment operation the trxn_type varies
80 -- So we need to select based on this set
81 -- For an explanation of various trxn_types refer the design doc
82
83 IF (s1%ISOPEN) THEN
84 CLOSE s1;
85 END IF;
86
87 IF (s2%ISOPEN) THEN
88 CLOSE s2;
89 END IF;
90
91 IF (SUBSTR(payment_operation,1,4)='AUTH') OR (payment_operation='ORAPMTREQ') THEN
92 OPEN s1('ORAPMTREQ');
93 ELSIF (payment_operation = 'CAPTURE') OR (payment_operation='ORAPMTCAPTURE') THEN
94 OPEN s1('ORAPMTCAPTURE');
95 ELSIF (payment_operation = 'RETURN' OR
96 payment_operation = 'CREDIT') OR (payment_operation='ORAPMTRETURN' OR payment_operation='ORAPMTCREDIT') THEN
97 OPEN s1('ORAPMTRETURN', 'ORAPMTCREDIT');
98 ELSIF (payment_operation = 'VOID') OR (payment_operation='ORAPMTVOID') THEN
99 -- can't use this as offline void
100 -- will put the trxntypeid to be void
101 OPEN s1('ORAPMTVOID');
102 END IF;
103
104 -- Fetch the first row to check the status
105 FETCH s1 INTO loc_status, bepid_out, bepkey_out, trxnid_out, trxnref_out;
106
107 IF s1%FOUND THEN
108 IF (loc_status = 0 OR -- previous successful ops, duplicate
109 loc_status = 1 OR -- communication error, retry
110 loc_status = 9999 OR -- timeout during ICX
111 loc_status = 2 OR -- If duplicate already
112 loc_status = 9 OR -- gateway transitional
113 loc_status = 14 OR -- cancelled, gateway
114 loc_status = 100 OR -- in an open batch
115 --loc_status = 101 OR -- batch comm error ;
116 -- enable when we have more time for regression testing
117 loc_status = 114 OR -- cancelled in an open batch
118 loc_status = 111 OR -- in a pending batch
119 loc_status = 109 OR -- in the midst of a patch close
120 loc_status = 11) THEN -- already scheduled offline
121 check_status_out := loc_status;
122 ELSE
123 check_status_out := -1; -- For any other error resubmit operation
124 END IF;
125 ELSE
126 check_status_out := -1; -- new order
127 bepid_out := -1;
128 bepkey_out := NULL;
129 END IF;
130
131 CLOSE s1;
132
133 -- get parent trace number for Concord gateway
134 IF payment_operation = 'CAPTURE' THEN
135 OPEN s2(2); -- authonly
136 ELSIF payment_operation = 'RETURN' THEN
137 OPEN s2(3, 8); -- authcapture/capture
138 ELSIF payment_operation = 'VOID' THEN
139 OPEN s2(trxn_type_in); -- trxn type to be void
140 ELSE
141 parent_trace_number_out := '';
142 RETURN;
143 END IF;
144
145 -- Fetch the first row to check the status
146 FETCH s2 INTO parent_trace_number_out;
147
148 IF s2%NOTFOUND THEN
149 parent_trace_number_out := NULL;
150 END IF;
151
152 CLOSE s2;
153
154 END checkunqorder;
155
156
157
158 PROCEDURE checkunqbatch (batch_id_in IN VARCHAR2,
159 merchant_id_in IN VARCHAR2,
160 stat OUT NOCOPY NUMBER,
161 viby_bid OUT NOCOPY VARCHAR2)
162
163
164 AS
165
166 loc_stat iby_batches_all.BatchStatus%TYPE;
167 loc_viby_bid iby_batches_all.VPSBatchID%TYPE;
168
169 CURSOR b1 IS
170 SELECT BatchStatus, VPSBatchID
171 FROM iby_batches_all
172 WHERE BatchID = batch_id_in
173 AND payeeid = merchant_id_in
174 ORDER BY BatchCloseDate DESC;
175
176
177 BEGIN
178
179 IF b1%ISOPEN THEN
180 CLOSE b1;
181 END IF;
182
183 OPEN b1;
184 FETCH b1 INTO loc_stat, loc_viby_bid;
185
186 IF b1%FOUND THEN
187 IF (loc_stat = 0 or loc_stat = 3 or loc_stat = 11) THEN
188 -- Batch successful,
189 -- Duplicate already marked. Return Duplicate batch id
190 -- already scheduled offline
191 stat := loc_stat;
192 CLOSE b1;
193 return;
194 ELSIF (loc_stat = 1 or loc_stat = 9999
195 or loc_stat = 6 or loc_stat = 7) THEN
196 -- communication error.
197 -- time out at Payment Server during ICX.
198 -- batch partial succeed
199 -- batch fail
200 --pass the VPSBatchID to payment systems in oraclosebatch
201 stat := loc_stat;
202 viby_bid := loc_viby_bid;
203 CLOSE b1;
204 return;
205 END IF;
206 END IF;
207
208 -- Batch not found
209 CLOSE b1;
210 stat := -1;
211
212 END checkunqbatch;
213
214
215
216 PROCEDURE getorderid (merchant_id_in IN VARCHAR2,
217 vendor_suffix IN VARCHAR2,
218 order_id_out OUT NOCOPY VARCHAR2)
219
220 AS
221
222 loc_order_id iby_trxn_summaries_all.Tangibleid%TYPE;
223
224 CURSOR o1 IS
225 SELECT order_Id
226 FROM iby_transactions_v trn, iby_bepinfo ven
227 WHERE merchant_id = merchant_id_in
228 AND UPPER(ven.suffix) = UPPER(vendor_suffix)
229 AND ven.bepid = trn.vendor_id
230 AND status = 0
231 AND trxn_type IN (8, 9, 5, 10) -- only looking at capture/return
232 AND merchbatchid IS NULL
233
234 ORDER BY time ASC;
235
236
237
238 BEGIN
239
240 OPEN o1;
241
242 -- Fetch the first row to get oldest order_id not settled
243 FETCH o1 INTO loc_order_id;
244
245 IF o1%FOUND THEN
246 order_id_out := loc_order_id;
247 CLOSE o1;
248 return;
249 END IF;
250
251 CLOSE o1;
252 order_id_out := -1;
253
254
255 END getorderid;
256
257 PROCEDURE getTrxnInfo
258 (
259 trxnid_in IN iby_trxn_summaries_all.transactionid%TYPE,
260 trxntypeid_in IN iby_trxn_summaries_all.trxntypeid%TYPE,
261 trxntypeid_aux_in IN iby_trxn_summaries_all.trxntypeid%TYPE,
262 status_in IN iby_trxn_summaries_all.status%TYPE,
263 status_aux_in IN iby_trxn_summaries_all.status%TYPE,
264 amount_out OUT NOCOPY iby_trxn_summaries_all.amount%TYPE,
265 currency_out OUT NOCOPY iby_trxn_summaries_all.currencynamecode%TYPE,
266 status_out OUT NOCOPY iby_trxn_summaries_all.status%TYPE
267 )
268 IS
269 BEGIN
270 SELECT amount, currencynamecode, status
271 INTO amount_out,currency_out, status_out
272 FROM iby_trxn_summaries_all
273 WHERE (transactionid=trxnid_in) AND (trxntypeid IN (trxntypeid_in,trxntypeid_aux_in))
274 AND (status in (status_in,status_aux_in));
275 EXCEPTION
276
277 WHEN no_data_found THEN
278 raise_application_error(-20000, 'IBY_20534#ID='||trxnid_in||'#TYPE='||trxntypeid_in||'/'||trxntypeid_aux_in||'#STATUS='||status_in||'/'||status_aux_in, FALSE);
279
280 END getTrxnInfo;
281
282 END iby_querycc_pkg;
[Home] [Help]