1 PACKAGE iby_transactioneft_pkg AUTHID CURRENT_USER AS
2 /*$Header: ibytefts.pls 120.14.12020000.4 2013/03/25 20:25:35 gmamidip ship $ */
3
4 /*
5 * The purpose of this procedure is to check if there is any open
6 * transaction which are due.
7 * If there is, it will insert a row into the iby_batches_all table
8 * to keep track of the batch status, and change the transactions status
9 * to other status. So, the open transactions will be sent as part of
10 * future batch close. Also, it will not allow any modification and
11 * cancellation to these transactions.
12 */
13 PROCEDURE createBatchCloseTrxns(
14 merch_batchid_in IN VARCHAR2,
15 merchant_id_in IN VARCHAR2,
16 vendor_id_in IN NUMBER,
17 vendor_key_in IN VARCHAR2,
18 newstatus_in IN NUMBER,
19 oldstatus_in IN NUMBER,
20 batchstate_in IN NUMBER DEFAULT null,
21 settlement_date_in IN DATE DEFAULT sysdate,
22 req_type_in IN VARCHAR2 DEFAULT null,
23 numtrxns_out OUT NOCOPY NUMBER
24 );
25
26 /*
27 * The purpose of this procedure is to check if there is any open
28 * transaction which are due.
29 * If there is, it will insert a row into the iby_batches_all table
30 * to keep track of the batch status, and change the transactions status
31 * to other status. So, the open transactions will be sent as part of
32 * future batch close. Also, it will not allow any modification and
33 * cancellation to these transactions.
34 */
35 PROCEDURE createBatchCloseTrxnsNew(
36 merch_batchid_in IN VARCHAR2,
37 profile_code_in IN iby_batches_all.
38 process_profile_code%TYPE,
39 merchant_id_in IN VARCHAR2,
40 vendor_id_in IN NUMBER,
41 vendor_key_in IN VARCHAR2,
42 newstatus_in IN NUMBER,
43 oldstatus_in IN NUMBER,
44 batchstate_in IN NUMBER DEFAULT null,
45 settlement_date_in IN DATE DEFAULT sysdate,
46 req_type_in IN VARCHAR2 DEFAULT null,
47 instr_type_in IN iby_batches_all.
48 instrument_type%TYPE,
49 br_disputed_flag_in IN iby_batches_all.
50 br_disputed_flag%TYPE,
51 f_pmt_channel_in IN iby_trxn_summaries_all.
52 payment_channel_code%TYPE,
53 f_curr_in IN iby_trxn_summaries_all.
54 currencynamecode%TYPE,
55 f_settle_date IN iby_trxn_summaries_all.
56 settledate%TYPE,
57 f_due_date IN iby_trxn_summaries_all.
58 settlement_due_date%TYPE,
59 f_maturity_date IN iby_trxn_summaries_all.
60 br_maturity_date%TYPE,
61 f_instr_type IN iby_trxn_summaries_all.
62 instrtype%TYPE,
63 numtrxns_out OUT NOCOPY NUMBER,
64 mbatch_ids_out OUT NOCOPY JTF_NUMBER_TABLE,
65 batch_ids_out OUT NOCOPY JTF_VARCHAR2_TABLE_100
66 );
67
68 /*
69 * This is the overloaded form of the previous API. This takes an array of
70 * profile codes as input parameter (instead of a single one)
71 * The purpose of this procedure is to check if there is any open
72 * transaction which are due. If there is, it will insert a row into
73 * the iby_batches_all table to keep track of the batch status, and
74 * change the transactions status to other status. So, the open
75 * transactions will be sent as part of future batch close. Also, it
76 * will not allow any modification and cancellation to these transactions.
77 */
78 PROCEDURE createBatchCloseTrxnsNew(
79 merch_batchid_in IN VARCHAR2,
80 profile_code_array IN JTF_VARCHAR2_TABLE_100,
81 merchant_id_in IN VARCHAR2,
82 vendor_id_in IN NUMBER,
83 vendor_key_in IN VARCHAR2,
84 newstatus_in IN NUMBER,
85 oldstatus_in IN NUMBER,
86 batchstate_in IN NUMBER,
87 settlement_date_in IN DATE,
88 req_type_in IN VARCHAR2,
89 instr_type_in IN iby_batches_all.
90 instrument_type%TYPE,
91 br_disputed_flag_in IN iby_batches_all.
92 br_disputed_flag%TYPE,
93 f_pmt_channel_in IN iby_trxn_summaries_all.
94 payment_channel_code%TYPE,
95 f_curr_in IN iby_trxn_summaries_all.
96 currencynamecode%TYPE,
97 f_settle_date IN iby_trxn_summaries_all.
98 settledate%TYPE,
99 f_due_date IN iby_trxn_summaries_all.
100 settlement_due_date%TYPE,
101 f_maturity_date IN iby_trxn_summaries_all.
102 br_maturity_date%TYPE,
103 f_instr_type IN iby_trxn_summaries_all.
104 instrtype%TYPE,
105 numtrxns_out OUT NOCOPY NUMBER,
106 mbatch_ids_out OUT NOCOPY JTF_NUMBER_TABLE,
107 batch_ids_out OUT NOCOPY JTF_VARCHAR2_TABLE_100
108 );
109
110 /*Update the batch and transactions status and other infomations based on the
111 payeeid and batchid */
112 PROCEDURE updateBatchCloseTrxns(
113 merch_batchid_in IN VARCHAR2,
114 merchant_id_in IN VARCHAR2,
115 newstatus_in IN NUMBER,
116 batchstate_in IN NUMBER DEFAULT null,
117 numtrxns_in IN NUMBER DEFAULT null,
118 batchtotal_in IN NUMBER DEFAULT null,
119 salestotal_in IN NUMBER DEFAULT null,
120 credittotal_in IN NUMBER DEFAULT null,
121 time_in IN DATE DEFAULT sysdate,
122 vendor_code_in IN VARCHAR2 DEFAULT null,
123 vendor_message_in IN VARCHAR2 DEFAULT null
124 );
125
126 /*Update the transactions status and other informations by passed the data in as array.*/
127 PROCEDURE updateTrxnResultStatus(
128 i_merch_batchid IN VARCHAR2,
129 i_merchant_id IN VARCHAR2,
130 i_status_arr IN JTF_NUMBER_TABLE,
131 i_errCode_arr IN JTF_VARCHAR2_TABLE_100,
132 i_errMsg_arr IN JTF_VARCHAR2_TABLE_300,
133 i_tangibleId_arr IN JTF_VARCHAR2_TABLE_100,
134 o_status_arr OUT NOCOPY JTF_NUMBER_TABLE,
135 o_error_code OUT NOCOPY NUMBER,
136 o_error_msg OUT NOCOPY VARCHAR2
137 );
138
139 /** Insert bulk EFT transactions **/
140 PROCEDURE insertEFTBatchTrxns(
141 i_ecappid IN iby_trxn_summaries_all.ecappid%TYPE,
142 i_payeeid IN iby_trxn_summaries_all.payeeid%TYPE,
143 i_ecbatchid IN iby_trxn_summaries_all.ecbatchid%TYPE,
144 i_bepid IN iby_trxn_summaries_all.bepid%TYPE,
145 i_bepkey IN iby_trxn_summaries_all.bepkey%TYPE,
146 i_pmtmethod IN iby_trxn_summaries_all.paymentmethodname%TYPE,
147 i_reqtype IN iby_trxn_summaries_all.reqtype%TYPE,
148 i_reqdate IN iby_trxn_summaries_all.reqdate%TYPE,
149 i_payeeinstrid IN iby_trxn_summaries_all.payeeinstrid%TYPE,
150 i_orgid IN iby_trxn_summaries_all.org_id%TYPE,
151
152 i_payerinstrid IN JTF_NUMBER_TABLE,
153 i_amount IN JTF_NUMBER_TABLE,
154 i_payerid IN JTF_VARCHAR2_TABLE_100,
155 i_tangibleid IN JTF_VARCHAR2_TABLE_100,
156 i_currency IN JTF_VARCHAR2_TABLE_100,
157 i_refinfo IN JTF_VARCHAR2_TABLE_100,
158 i_memo IN JTF_VARCHAR2_TABLE_100,
159 i_OrderMedium IN JTF_VARCHAR2_TABLE_100,
160 i_EftAuthMethod IN JTF_VARCHAR2_TABLE_100,
161 i_instrsubtype IN JTF_VARCHAR2_TABLE_100,
162 i_settledate IN JTF_DATE_TABLE,
163 i_issuedate IN JTF_DATE_TABLE,
164 i_customerref IN JTF_VARCHAR2_TABLE_100,
165 o_trxnId OUT NOCOPY JTF_NUMBER_TABLE
166 );
167
168 /** Insert verify EFT transaction **/
169 PROCEDURE createEFTVerifyTrxn(
170 i_ecappid IN iby_trxn_summaries_all.ecappid%TYPE,
171 i_reqtype IN iby_trxn_summaries_all.reqtype%TYPE,
172 i_bepid IN iby_trxn_summaries_all.bepid%TYPE,
173 i_bepkey IN iby_trxn_summaries_all.bepkey%TYPE,
174 i_payeeid IN iby_trxn_summaries_all.payeeid%TYPE,
175 i_payeeinstrid IN iby_trxn_summaries_all.payeeinstrid%TYPE,
176 i_tangibleid IN iby_trxn_summaries_all.tangibleid%TYPE,
177 i_amount IN iby_trxn_summaries_all.amount%TYPE,
178 i_currency IN iby_trxn_summaries_all.currencynamecode%TYPE,
179 i_status IN iby_trxn_summaries_all.status%TYPE,
180 i_refinfo IN iby_tangible.refinfo%TYPE,
181 i_memo IN iby_tangible.memo%TYPE,
182 i_acctno IN iby_tangible.acctno%TYPE,
183 i_OrderMedium IN iby_tangible.order_medium%TYPE,
184 i_EftAuthMethod IN iby_tangible.eft_auth_method%TYPE,
185 i_orgid IN iby_trxn_summaries_all.org_id%TYPE,
186 i_pmtmethod IN iby_trxn_summaries_all.paymentmethodname%TYPE,
187 i_payerid IN iby_trxn_summaries_all.payerid%TYPE,
188 i_instrtype IN iby_trxn_summaries_all.instrtype%TYPE,
189 i_instrsubtype IN iby_trxn_summaries_all.instrsubtype%TYPE,
190 i_payerinstrid IN iby_trxn_summaries_all.payerinstrid%TYPE,
191 i_trxndate IN iby_trxn_summaries_all.updatedate%TYPE,
192 i_trxntypeid IN iby_trxn_summaries_all.TrxntypeID%TYPE,
193 i_bepcode IN iby_trxn_summaries_all.BEPCode%TYPE,
194 i_bepmessage IN iby_trxn_summaries_all.BEPMessage%TYPE,
195 i_errorlocation IN iby_trxn_summaries_all.errorlocation%TYPE,
196 i_referenceCode IN iby_trxn_summaries_all.proc_reference_code%TYPE,
197 o_trxnid OUT NOCOPY iby_trxn_summaries_all.transactionid%TYPE,
198 i_orgtype IN iby_trxn_summaries_all.org_type%TYPE,
199 i_pmtchannelcode IN iby_trxn_summaries_all.payment_channel_code%TYPE,
200 i_factoredflag IN iby_trxn_summaries_all.factored_flag%TYPE,
201 i_pmtinstrassignmentId IN iby_trxn_summaries_all.payer_instr_assignment_id%TYPE,
202 i_process_profile_code IN iby_trxn_summaries_all.process_profile_code%TYPE,
203 o_trxnmid OUT NOCOPY iby_trxn_summaries_all.trxnmid%TYPE
204 );
205
206 /*Added funds transfer format code as an additional paramater to fetch validations attached to the format.*/
207 PROCEDURE validate_open_batch
208 (
209 p_bep_id IN iby_trxn_summaries_all.bepid%TYPE,
210 p_mbatch_id IN iby_batches_all.mbatchid%TYPE,
211 p_funds_xfer_format_code IN iby_fndcpt_sys_eft_pf_b.funds_xfer_format_code%TYPE
212 );
213
214
215 /*--------------------------------------------------------------------
216 | NAME:
217 | print_debuginfo
218 |
219 | PURPOSE:
220 | This procedure prints the debug message to the concurrent manager
221 | log file.
222 |
223 | PARAMETERS:
224 | IN
225 | p_debug_text - The debug message to be printed
226 |
227 | OUT
228 |
229 |
230 | RETURNS:
231 |
232 | NOTES:
233 |
234 *---------------------------------------------------------------------*/
235 PROCEDURE print_debuginfo(
236 p_module IN VARCHAR2,
237 p_debug_text IN VARCHAR2
238 );
239
240 END iby_transactioneft_pkg;
241