1 package body iby_querycc_pkg as
2 /*$Header: ibyqrccb.pls 120.2 2011/02/18 11:50:46 lmallick 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 ELSIF (payment_operation = 'REVERSE') OR (payment_operation='ORAPMTREVERSE') THEN
103 OPEN s1('ORAPMTREVERSE');
104 END IF;
105
106 -- Fetch the first row to check the status
107 FETCH s1 INTO loc_status, bepid_out, bepkey_out, trxnid_out, trxnref_out;
108
109 IF s1%FOUND THEN
110 IF (loc_status = 0 OR -- previous successful ops, duplicate
111 loc_status = 1 OR -- communication error, retry
112 loc_status = 9999 OR -- timeout during ICX
113 loc_status = 2 OR -- If duplicate already
114 loc_status = 9 OR -- gateway transitional
115 loc_status = 14 OR -- cancelled, gateway
116 loc_status = 100 OR -- in an open batch
117 --loc_status = 101 OR -- batch comm error ;
118 -- enable when we have more time for regression testing
119 loc_status = 114 OR -- cancelled in an open batch
120 loc_status = 111 OR -- in a pending batch
121 loc_status = 109 OR -- in the midst of a patch close
122 loc_status = 11) THEN -- already scheduled offline
123 check_status_out := loc_status;
124 ELSE
125 check_status_out := -1; -- For any other error resubmit operation
126 END IF;
127 ELSE
128 check_status_out := -1; -- new order
129 bepid_out := -1;
130 bepkey_out := NULL;
131 END IF;
132
133 CLOSE s1;
134
135 -- get parent trace number for Concord gateway
136 IF payment_operation = 'CAPTURE' OR payment_operation = 'REVERSE' THEN
137 OPEN s2(2); -- authonly
138 ELSIF payment_operation = 'RETURN' THEN
139 OPEN s2(3, 8); -- authcapture/capture
140 ELSIF payment_operation = 'VOID' THEN
141 OPEN s2(trxn_type_in); -- trxn type to be void
142 ELSE
143 parent_trace_number_out := '';
144 RETURN;
145 END IF;
146
147 -- Fetch the first row to check the status
148 FETCH s2 INTO parent_trace_number_out;
149
150 IF s2%NOTFOUND THEN
151 parent_trace_number_out := NULL;
152 END IF;
153
154 CLOSE s2;
155
156 END checkunqorder;
157
158
159
160 PROCEDURE checkunqbatch (batch_id_in IN VARCHAR2,
161 merchant_id_in IN VARCHAR2,
162 stat OUT NOCOPY NUMBER,
163 viby_bid OUT NOCOPY VARCHAR2)
164
165
166 AS
167
168 loc_stat iby_batches_all.BatchStatus%TYPE;
169 loc_viby_bid iby_batches_all.VPSBatchID%TYPE;
170
171 CURSOR b1 IS
172 SELECT BatchStatus, VPSBatchID
173 FROM iby_batches_all
174 WHERE BatchID = batch_id_in
175 AND payeeid = merchant_id_in
176 ORDER BY BatchCloseDate DESC;
177
178
179 BEGIN
180
181 IF b1%ISOPEN THEN
182 CLOSE b1;
183 END IF;
184
185 OPEN b1;
186 FETCH b1 INTO loc_stat, loc_viby_bid;
187
188 IF b1%FOUND THEN
189 IF (loc_stat = 0 or loc_stat = 3 or loc_stat = 11) THEN
190 -- Batch successful,
191 -- Duplicate already marked. Return Duplicate batch id
192 -- already scheduled offline
193 stat := loc_stat;
194 CLOSE b1;
195 return;
196 ELSIF (loc_stat = 1 or loc_stat = 9999
197 or loc_stat = 6 or loc_stat = 7) THEN
198 -- communication error.
199 -- time out at Payment Server during ICX.
200 -- batch partial succeed
201 -- batch fail
202 --pass the VPSBatchID to payment systems in oraclosebatch
203 stat := loc_stat;
204 viby_bid := loc_viby_bid;
205 CLOSE b1;
206 return;
207 END IF;
208 END IF;
209
210 -- Batch not found
211 CLOSE b1;
212 stat := -1;
213
214 END checkunqbatch;
215
216
217
218 PROCEDURE getorderid (merchant_id_in IN VARCHAR2,
219 vendor_suffix IN VARCHAR2,
220 order_id_out OUT NOCOPY VARCHAR2)
221
222 AS
223
224 loc_order_id iby_trxn_summaries_all.Tangibleid%TYPE;
225
226 CURSOR o1 IS
227 SELECT order_Id
228 FROM iby_transactions_v trn, iby_bepinfo ven
229 WHERE merchant_id = merchant_id_in
230 AND UPPER(ven.suffix) = UPPER(vendor_suffix)
231 AND ven.bepid = trn.vendor_id
232 AND status = 0
233 AND trxn_type IN (8, 9, 5, 10) -- only looking at capture/return
234 AND merchbatchid IS NULL
235
236 ORDER BY time ASC;
237
238
239
240 BEGIN
241
242 OPEN o1;
243
244 -- Fetch the first row to get oldest order_id not settled
245 FETCH o1 INTO loc_order_id;
246
247 IF o1%FOUND THEN
248 order_id_out := loc_order_id;
249 CLOSE o1;
250 return;
251 END IF;
252
253 CLOSE o1;
254 order_id_out := -1;
255
256
257 END getorderid;
258
259 PROCEDURE getTrxnInfo
260 (
261 trxnid_in IN iby_trxn_summaries_all.transactionid%TYPE,
262 trxntypeid_in IN iby_trxn_summaries_all.trxntypeid%TYPE,
263 trxntypeid_aux_in IN iby_trxn_summaries_all.trxntypeid%TYPE,
264 status_in IN iby_trxn_summaries_all.status%TYPE,
265 status_aux_in IN iby_trxn_summaries_all.status%TYPE,
266 amount_out OUT NOCOPY iby_trxn_summaries_all.amount%TYPE,
267 currency_out OUT NOCOPY iby_trxn_summaries_all.currencynamecode%TYPE,
268 status_out OUT NOCOPY iby_trxn_summaries_all.status%TYPE
269 )
270 IS
271 BEGIN
272 SELECT amount, currencynamecode, status
273 INTO amount_out,currency_out, status_out
274 FROM iby_trxn_summaries_all
275 WHERE (transactionid=trxnid_in) AND (trxntypeid IN (trxntypeid_in,trxntypeid_aux_in))
276 AND (status in (status_in,status_aux_in));
277 EXCEPTION
278
279 WHEN no_data_found THEN
280 raise_application_error(-20000, 'IBY_20534#ID='||trxnid_in||'#TYPE='||trxntypeid_in||'/'||trxntypeid_aux_in||'#STATUS='||status_in||'/'||status_aux_in, FALSE);
281
282 END getTrxnInfo;
283
284 END iby_querycc_pkg;
[Home] [Help]