1 PACKAGE iby_transactionset_pkg AS
2 /*$Header: ibytxsts.pls 120.2 2005/10/30 05:51:19 appldev ship $ */
3
4 /* This procedure would be used every time a SET INIT transaction */
5 /* occurred. Since INIT is not idempotent, this procedure only */
6 /* inserts a new row into the transactions table after the vendor */
7 /* application has returned. It does not perform any error checking */
8 /* to make sure that the row already exists since the programmer */
9 /* should have called queryset.listvendor to check beforehand. */
10 PROCEDURE insert_init_txn
11 (ecapp_id_in IN iby_trxn_summaries_all.ECAPPID%TYPE,
12 req_type_in IN iby_trxn_summaries_all.ReqType%TYPE,
13 order_id_in IN iby_trxn_summaries_all.TangibleID%TYPE,
14 merchant_id_in IN iby_trxn_summaries_all.PayeeID%TYPE,
15 vendor_id_in IN iby_trxn_summaries_all.BEPID%TYPE,
16 trxn_type_in IN OUT NOCOPY iby_trxn_summaries_all.TrxntypeID%TYPE,
17 payment_name_in IN iby_trxn_core.InstrName%TYPE,
18 price_in IN iby_trxn_summaries_all.Amount%TYPE,
19 currency_in IN iby_trxn_summaries_all.CurrencyNameCode%TYPE,
20 time_in IN iby_trxn_summaries_all.UpdateDate%TYPE,
21 status_in IN iby_trxn_summaries_all.Status%TYPE,
22 transaction_id_in_out IN OUT NOCOPY iby_trxn_summaries_all.TransactionID%TYPE,
23 vendor_code_in IN iby_trxn_summaries_all.BEPCode%TYPE
24 DEFAULT NULL,
25 vendor_message_in IN iby_trxn_summaries_all.BEPMessage%TYPE
26 DEFAULT NULL,
27 error_location_in IN iby_trxn_summaries_all.ErrorLocation%TYPE
28 DEFAULT NULL,
29 billeracct_in IN iby_tangible.acctno%type,
30 refinfo_in IN iby_tangible.refinfo%type,
31 memo_in IN iby_tangible.memo%type,
32 order_medium_in IN iby_tangible.order_medium%TYPE,
33 eft_auth_method_in IN iby_tangible.eft_auth_method%TYPE,
34 org_id_in IN iby_trxn_summaries_all.org_id%TYPE,
35 payerinstrid_in IN iby_trxn_summaries_all.payerinstrid%type,
36 instrnum_in IN iby_trxn_summaries_all.instrnumber%type);
37
38 /* This procedure would be used every time a SET SET */
39 /* transaction occurred. Since SET is idempotent, the procedure */
40 /* checks to see if the row already exists based upon order_id, */
41 /* merchant_id, trxn_type, and request_type. */
42 PROCEDURE insert_set_txn
43 (ecapp_id_in IN iby_trxn_summaries_all.ECAPPID%TYPE,
44 req_type_in IN iby_trxn_summaries_all.ReqType%TYPE,
45 order_id_in IN iby_trxn_summaries_all.TangibleID%TYPE,
46 merchant_id_in IN iby_trxn_summaries_all.PayeeID%TYPE,
47 vendor_id_in IN iby_trxn_summaries_all.BEPID%TYPE,
48 trxn_type_in IN iby_trxn_summaries_all.TrxntypeID%TYPE,
49 split_id_in IN iby_trxn_extended.SplitID%TYPE,
50 payment_name_in IN iby_trxn_core.InstrName%TYPE,
51 price_in IN iby_trxn_summaries_all.Amount%TYPE,
52 currency_in IN iby_trxn_summaries_all.CurrencyNameCode%TYPE,
53 time_in IN iby_trxn_summaries_all.UpdateDate%TYPE,
54 status_in IN iby_trxn_summaries_all.Status%TYPE,
55 authcode_in IN iby_trxn_core.AuthCode%TYPE,
56 capcode_in IN iby_trxn_core.OperationCode%TYPE,
57 completion_code_in IN iby_trxn_extended.CompletionCode%TYPE,
58 set_trxn_id_in IN iby_trxn_extended.SETTrxnID%TYPE,
59 batch_id_in IN iby_trxn_summaries_all.batchID%TYPE,
60 batch_seq_num_in IN iby_trxn_extended.BatchSeqNum%TYPE,
61 AVS_result_in IN iby_trxn_core.AVSCode%TYPE,
62 ret_ref_num_in IN iby_trxn_core.ReferenceCode%TYPE,
63 card_BIN_in IN iby_trxn_extended.Cardbin%TYPE,
64 terminal_id_in IN iby_trxn_extended.TerminalID%TYPE,
65 request_type_in IN iby_trxn_extended.SETReqType%TYPE,
66 subseq_auth_ind_in IN iby_trxn_extended.SubAuthInd%TYPE,
67 transaction_id_in_out IN OUT NOCOPY iby_trxn_summaries_all.TransactionID%TYPE,
68 payment_method_in IN iby_trxn_summaries_all.PAYMENTMETHODNAME%TYPE,
69 vendor_code_in IN iby_trxn_summaries_all.BEPCode%TYPE
70 DEFAULT NULL,
71 vendor_message_in IN iby_trxn_summaries_all.BEPMessage%TYPE
72 DEFAULT NULL,
73 error_location_in IN iby_trxn_summaries_all.ErrorLocation%TYPE
74 DEFAULT NULL,
75 billeracct_in IN iby_tangible.acctno%type ,
76 refinfo_in IN iby_tangible.refinfo%type,
77 memo_in IN iby_tangible.memo%type ,
78 order_medium_in IN iby_tangible.order_medium%TYPE,
79 eft_auth_method_in IN iby_tangible.eft_auth_method%TYPE,
80 org_id_in IN iby_trxn_summaries_all.org_id%TYPE,
81 payerinstrid_in IN iby_trxn_summaries_all.payerinstrid%type,
82 instrnum_in IN iby_trxn_summaries_all.instrnumber%type);
83
84
85 /* This procedure would be used every time a SET AUTH or AUTHREV */
86 /* transaction occurred. Since AUTH and AUTHREV are idempotent, the */
87 /* procedure checks to see if the row already exists based upon */
88 /* order_id, merchant_id, and split_id. */
89 PROCEDURE insert_auth_txn
90 (ecapp_id_in IN iby_trxn_summaries_all.ECAPPID%TYPE,
91 req_type_in IN iby_trxn_summaries_all.ReqType%TYPE,
92 order_id_in IN iby_trxn_summaries_all.TangibleID%TYPE,
93 merchant_id_in IN iby_trxn_summaries_all.PayeeID%TYPE,
94 vendor_id_in IN iby_trxn_summaries_all.BEPID%TYPE,
95 trxn_type_in IN iby_trxn_summaries_all.TrxntypeID%TYPE,
96 split_id_in IN iby_trxn_extended.SplitID%TYPE,
97 payment_name_in IN iby_trxn_summaries_all.PaymentMethodName%TYPE,
98 price_in IN iby_trxn_summaries_all.Amount%TYPE,
99 currency_in IN iby_trxn_summaries_all.CurrencyNameCode%TYPE,
100 time_in IN iby_trxn_summaries_all.UpdateDate%TYPE,
101 status_in IN iby_trxn_summaries_all.Status%TYPE,
102 authcode_in IN iby_trxn_core.AuthCode%TYPE,
103 capcode_in IN iby_trxn_core.OperationCode%TYPE,
104 completion_code_in IN iby_trxn_extended.CompletionCode%TYPE,
105 set_trxn_id_in IN iby_trxn_extended.SETTrxnID%TYPE,
106 batch_id_in IN OUT NOCOPY iby_trxn_summaries_all.batchID%TYPE,
107 batch_seq_num_in IN OUT NOCOPY iby_trxn_extended.BatchSeqNum%TYPE,
108 AVS_result_in IN OUT NOCOPY iby_trxn_core.AVSCode%TYPE,
109 ret_ref_num_in IN iby_trxn_core.ReferenceCode%TYPE,
110 card_BIN_in IN OUT NOCOPY iby_trxn_extended.Cardbin%TYPE,
111 terminal_id_in IN OUT NOCOPY iby_trxn_extended.TerminalID%TYPE,
112 subseq_auth_ind_in IN iby_trxn_extended.SubAuthInd%TYPE,
113 vendor_code_in IN OUT NOCOPY iby_trxn_summaries_all.BEPCode%TYPE,
114 vendor_message_in IN OUT NOCOPY iby_trxn_summaries_all.BEPMessage%TYPE,
115 error_location_in IN OUT NOCOPY iby_trxn_summaries_all.ErrorLocation%TYPE,
116 transaction_id_in_out IN OUT NOCOPY iby_trxn_summaries_all.TransactionID%TYPE,
117 payment_method_in IN
118 iby_trxn_summaries_all.PAYMENTMETHODNAME%TYPE,
119 billeracct_in IN iby_tangible.acctno%type,
120 refinfo_in IN iby_tangible.refinfo%type,
121 memo_in IN iby_tangible.memo%type,
122 order_medium_in IN iby_tangible.order_medium%TYPE,
123 eft_auth_method_in IN iby_tangible.eft_auth_method%TYPE,
124 org_id_in IN iby_trxn_summaries_all.org_id%TYPE,
125 payerinstrid_in IN iby_trxn_summaries_all.payerinstrid%type,
126 instrnum_in IN iby_trxn_summaries_all.instrnumber%type);
127
128
129 /* Inserts a new row into the PS_TRANSACTIONS table. This method */
130 /* would be called every time a SET CAPTURE, CAPTUREREV, CREDIT, or */
131 /* CREDITREV operation is performed. */
132
133 PROCEDURE insert_other_txn
134 (ecapp_id_in IN iby_trxn_summaries_all.ECAPPID%TYPE,
135 req_type_in IN iby_trxn_summaries_all.ReqType%TYPE,
136 order_id_in IN iby_trxn_summaries_all.TangibleID%TYPE,
137 merchant_id_in IN iby_trxn_summaries_all.PayeeID%TYPE,
138 vendor_id_in IN iby_trxn_summaries_all.BEPID%TYPE,
139 trxn_type_in IN iby_trxn_summaries_all.TrxntypeID%TYPE,
140 split_id_in IN iby_trxn_extended.SplitID%TYPE,
141 payment_name_in IN iby_trxn_summaries_all.PAYMENTMETHODNAME%TYPE,
142 price_in IN iby_trxn_summaries_all.Amount%TYPE,
143 currency_in IN iby_trxn_summaries_all.CurrencyNameCode%TYPE,
144 time_in IN iby_trxn_summaries_all.UpdateDate%TYPE,
145 status_in IN iby_trxn_summaries_all.Status%TYPE,
146 operation_code_in IN iby_trxn_core.OperationCode%TYPE,
147 set_trxn_id_in IN iby_trxn_extended.SETTrxnID%TYPE,
148 batch_id_in IN OUT NOCOPY iby_trxn_summaries_all.batchID%TYPE,
149 batch_seq_num_in IN OUT NOCOPY iby_trxn_extended.BatchSeqNum%TYPE,
150 terminal_id_in IN OUT NOCOPY iby_trxn_extended.TerminalID%TYPE,
151 subseq_auth_ind_in IN iby_trxn_extended.SubAuthInd%TYPE,
152 vendor_code_in IN OUT NOCOPY iby_trxn_summaries_all.BEPCode%TYPE,
153 vendor_message_in IN OUT NOCOPY iby_trxn_summaries_all.BEPMessage%TYPE,
154 error_location_in IN OUT NOCOPY iby_trxn_summaries_all.ErrorLocation%TYPE,
155 transaction_id_in_out IN OUT NOCOPY iby_trxn_summaries_all.TransactionID%TYPE,
156 billeracct_in IN iby_tangible.acctno%type,
157 refinfo_in IN iby_tangible.refinfo%type,
158 memo_in IN iby_tangible.memo%type,
159 order_medium_in IN iby_tangible.order_medium%TYPE,
160 eft_auth_method_in IN iby_tangible.eft_auth_method%TYPE,
161 org_id_in IN iby_trxn_summaries_all.org_id%TYPE,
162 payerinstrid_in IN iby_trxn_summaries_all.payerinstrid%type,
163 instrnum_in IN iby_trxn_summaries_all.instrnumber%type);
164
165
166 /* Inserts or updates a row in the PS_TRXN_TABLE if the */
167 /* operation timed out from calling the vendor. */
168 PROCEDURE insert_timeout_txn
169 (ecapp_id_in IN iby_trxn_summaries_all.ECAPPID%TYPE,
170 req_type_in IN iby_trxn_summaries_all.ReqType%TYPE,
171 order_id_in IN iby_trxn_summaries_all.TangibleID%TYPE,
172 merchant_id_in IN iby_trxn_summaries_all.PayeeID%TYPE,
173 vendor_id_in IN iby_trxn_summaries_all.BEPID%TYPE,
174 trxn_type_in IN iby_trxn_summaries_all.TrxntypeID%TYPE,
175 split_id_in IN iby_trxn_extended.SplitID%TYPE,
176 payment_name_in IN iby_trxn_core.InstrName%TYPE,
177 time_in IN iby_trxn_summaries_all.UpdateDate%TYPE,
178 status_in IN iby_trxn_summaries_all.Status%TYPE,
179 transaction_id_in_out IN OUT NOCOPY iby_trxn_summaries_all.TransactionID%TYPE,
180 currency_in IN iby_tangible.currencynamecode%type,
181 amount_in IN iby_transactions_v.amount%TYPE,
182 billeracct_in IN iby_tangible.acctno%type,
183 refinfo_in IN iby_tangible.refinfo%type,
184 memo_in IN iby_tangible.memo%type,
185 order_medium_in IN iby_tangible.order_medium%TYPE,
186 eft_auth_method_in IN iby_tangible.eft_auth_method%TYPE,
187 org_id_in IN iby_trxn_summaries_all.org_id%TYPE,
188 payerinstrid_in IN iby_trxn_summaries_all.payerinstrid%type,
189 instrnum_in IN iby_trxn_summaries_all.instrnumber%type);
190
191
192 /* Inserts or updates a batch summary row into the PS_BATCH_SET */
193 /* table. This should happen for open or close batch operations. */
194 PROCEDURE insert_batch_status
195 (batch_id_in IN iby_batches_all.BatchID%TYPE,
196 merchant_id_in IN iby_batches_all.PayeeID%TYPE,
197 bep_id_in IN iby_batches_all.BEPID%TYPE,
198 /* vendor_suffix_in IN iby_batches_all.vendor_suffix%TYPE, */
199 /* close_status_in IN iby_batches_all.BatchCloseStatus%TYPE, */
200 currency_in IN iby_batches_all.CurrencyNameCode%TYPE,
201 sale_price_in IN iby_batches_all.BatchSales%TYPE,
202 credit_price_in IN iby_batches_all.BatchCredit%TYPE,
203 trxn_count_in IN iby_batches_all.NumTrxns%TYPE,
204 sale_trxn_count_in IN iby_batches_all.NumTrxns%TYPE,
205 credit_trxn_count_in IN iby_batches_all.NumTrxns%TYPE,
206 open_date_in IN iby_batches_all.BatchOpenDate%TYPE,
207 close_date_in IN iby_batches_all.BatchCloseDate%TYPE,
208 status_in IN iby_batches_all.BatchStatus%TYPE,
209 vendor_code_in IN iby_batches_all.BEPCode%TYPE,
210 vendor_message_in IN iby_batches_all.BEPMessage%TYPE,
211 error_location_in IN iby_batches_all.ErrorLocation%TYPE,
212 org_id_in IN iby_batches_all.org_id%TYPE,
213 req_type_in IN iby_batches_all.reqtype%type);
214
215
216 /* Inserts or updates the batch detail record upon the */
217 /* closebatch or querybatch operations. */
218 PROCEDURE insert_batch_txn
219 (ecapp_id_in IN iby_trxn_summaries_all.ECAPPID%TYPE,
220 order_id_in IN iby_trxn_summaries_all.TangibleID%TYPE,
221 merchant_id_in IN iby_trxn_summaries_all.PayeeID%TYPE,
222 vendor_id_in IN iby_trxn_summaries_all.BEPID%TYPE,
223 trxn_type_in IN iby_trxn_summaries_all.TrxntypeID%TYPE,
224 split_id_in IN iby_trxn_extended.SplitID%TYPE,
225 payment_name_in IN iby_trxn_core.InstrName%TYPE,
226 price_in IN iby_trxn_summaries_all.Amount%TYPE,
227 currency_in IN iby_trxn_summaries_all.CurrencyNameCode%TYPE,
228 time_in IN iby_trxn_summaries_all.UpdateDate%TYPE,
229 status_in IN iby_trxn_summaries_all.Status%TYPE,
230 set_trxn_id_in IN iby_trxn_extended.SETTrxnID%TYPE,
231 prev_set_trxn_id_in IN iby_trxn_extended.SETTrxnID%TYPE,
232 batch_id_in IN iby_trxn_summaries_all.batchID%TYPE,
233 batch_seq_num_in IN iby_trxn_extended.BatchSeqNum%TYPE,
234 batch_trxn_status_in IN iby_trxn_extended.BatchTrxnStatus%TYPE,
235 card_BIN_in IN iby_trxn_extended.Cardbin%TYPE,
236 terminal_id_in IN iby_trxn_extended.TerminalID%TYPE,
237 vendor_code_in IN iby_trxn_summaries_all.BEPCode%TYPE,
238 vendor_message_in IN iby_trxn_summaries_all.BEPMessage%TYPE,
239 error_location_in IN iby_trxn_summaries_all.ErrorLocation%TYPE,
240 split_id_in_out IN OUT NOCOPY iby_trxn_extended.SplitID%TYPE,
241 org_id_in IN iby_trxn_summaries_all.org_id%TYPE);
242 /* Inserts transaction record for transaction query operation */
243 PROCEDURE insert_query_txn
244 (ecapp_id_in IN iby_trxn_summaries_all.ECAPPID%TYPE,
245 order_id_in IN iby_trxn_summaries_all.TangibleID%TYPE,
246 merchant_id_in IN iby_trxn_summaries_all.PayeeID%TYPE,
247 vendor_id_in IN iby_trxn_summaries_all.BEPID%TYPE,
248 trxn_type_in IN iby_trxn_summaries_all.TrxntypeID%TYPE,
249 price_in IN iby_trxn_summaries_all.Amount%TYPE,
250 currency_in IN iby_trxn_summaries_all.CurrencyNameCode%TYPE,
251 time_in IN iby_trxn_summaries_all.UpdateDate%TYPE,
252 status_in IN iby_trxn_summaries_all.Status%TYPE,
253 set_trxn_id_in IN iby_trxn_extended.SETTrxnID%TYPE,
254 prev_set_trxn_id_in IN iby_trxn_extended.SETTrxnID%TYPE,
255 ret_ref_num_in IN iby_trxn_core.ReferenceCode%TYPE,
256 card_BIN_in IN OUT NOCOPY iby_trxn_extended.Cardbin%TYPE,
257 terminal_id_in IN OUT NOCOPY iby_trxn_extended.TerminalID%TYPE,
258 vendor_code_in IN OUT NOCOPY iby_trxn_summaries_all.BEPCode%TYPE,
259 vendor_message_in IN OUT NOCOPY iby_trxn_summaries_all.BEPMessage%TYPE,
260 error_location_in IN OUT NOCOPY iby_trxn_summaries_all.ErrorLocation%TYPE,
261 split_id_in_out IN OUT NOCOPY iby_trxn_extended.SplitID%TYPE,
262 transaction_id_in IN iby_trxn_summaries_all.TransactionID%TYPE,
263 payment_method_in IN iby_trxn_summaries_all.PaymentMethodName%TYPE,
264 org_id_in IN iby_trxn_summaries_all.org_id%TYPE,
265 req_type_in IN iby_trxn_summaries_all.reqtype%type);
266
267 /* Internal procedure to get the split_id of the parent */
268 /* transaction. */
269 PROCEDURE find_parent_splitid
270 (order_id_in IN iby_trxn_summaries_all.TangibleID%TYPE,
271 merchant_id_in IN iby_trxn_summaries_all.PayeeID%TYPE,
272 vendor_id_in IN iby_trxn_summaries_all.BEPID%TYPE,
273 trxn_type_in IN iby_trxn_summaries_all.TrxntypeID%TYPE,
274 prev_set_trxn_id_in IN iby_trxn_extended.SETTrxnID%TYPE,
275 split_id_in_out IN OUT NOCOPY iby_trxn_extended.SplitID%TYPE);
276 END iby_transactionSET_pkg;