1 PACKAGE IBY_UTILITY_PVT AS
2 /* $Header: ibyvutls.pls 120.9.12010000.4 2008/11/21 09:53:04 vkarlapu ship $ */
3
4
5 -- iPayment trxn and batch status codes
6 -- must be in sync with mid-tier definition
7 -- in iby.ecapp.Constants
8 -- For an example of using these constants
9 -- in iby PL/SQL packages, see IBY_BANKPAYMENT_UPDT_PUB package body
10
11 -- for obselete statuses
12 STATUS_OBSELETE CONSTANT NUMBER :=-1;
13
14 STATUS_SUCCESS CONSTANT NUMBER := 0;
15
16 STATUS_COMM_ERROR CONSTANT NUMBER :=1;
17
18 STATUS_DUP_ORDER_ID CONSTANT NUMBER :=2;
19
20 STATUS_DUP_BATCH_ID CONSTANT NUMBER :=3;
21
22 STATUS_FIELD_MISSING CONSTANT NUMBER :=4;
23
24 STATUS_BEP_ERROR CONSTANT NUMBER :=5;
25
26 STATUS_BATCH_PARTIAL CONSTANT NUMBER :=6;
27
28 STATUS_BATCH_FAIL CONSTANT NUMBER :=7;
29
30 STATUS_NOT_SUPPORTED CONSTANT NUMBER :=8;
31
32 -- trxn interuppted before final status could be saved;
33 -- requires query of BEP to sync final statuses
34 STATUS_TRANSITIONAL CONSTANT NUMBER :=9;
35
36 STATUS_PENDING CONSTANT NUMBER := 11;
37
38 STATUS_INVALID CONSTANT NUMBER := -99;
39
40 STATUS_SCHED_IN_PROGRESS CONSTANT NUMBER := 12;
41
42 STATUS_SCHED_SUCCESS CONSTANT NUMBER := 13;
43
44 STATUS_CANCELLED CONSTANT NUMBER := 14;
45
46 STATUS_SCHED_FAILED CONSTANT NUMBER := 15;
47
48 STATUS_BEP_FAILED CONSTANT NUMBER := 16;
49
50 STATUS_UNABLE_TO_PAY CONSTANT NUMBER := 17;
51
52 STATUS_SUBMITTED CONSTANT NUMBER := 18;
53
54 STATUS_INVALID_CC CONSTANT NUMBER := 19;
55
56 STATUS_TRXN_DECLINED CONSTANT NUMBER := 20;
57
58 -- trxn is waiting in an open batch
59 STATUS_OPEN_BATCHED CONSTANT NUMBER := 100;
60
61 -- trxn was sent in a batch that resulted in comm error
62 STATUS_BATCH_COMM_ERROR CONSTANT NUMBER := 101;
63
64
65 -- trxn is about to be sent out as part of a batch.
66 -- Used to delimit which transactions were actually
67 -- sent out so that batched transactions which arrive
68 -- concurrently are not mistakenly updated to
69 -- see STATUS_BATCH_PENDING
70 STATUS_BATCH_TRANSITIONAL CONSTANT NUMBER := 109;
71
72 -- trxn is in a submitted batch
73 STATUS_BATCH_PENDING CONSTANT NUMBER := 111;
74
75 -- trxn was cancelled an open batch
76 STATUS_BATCH_CANCELLED CONSTANT NUMBER := 114;
77
78 -- iPayment has exceeded the number of batches
79 -- allowed in a day
80 STATUS_BATCH_MAX_EXCEEDED CONSTANT NUMBER := 120;
81
82 -- The statuses of all child trxns (and the EC batches
83 -- in turn for payable merged iby batches) are obtained
84 -- through querying the BEP. Some child trxns succeeded,
85 -- some failed. This is considered a final status
86 -- Usage: payable EC batch - iby_pay_batches_all.batch_status
87 -- payable iby merged batch - iby_batches_all.batchstatus
88 STATUS_QRY_BATCH_PARTIAL CONSTANT NUMBER :=206;
89
90 -- The batch is failed at the BEP side.
91 -- This can happen when the batch has a syntax error,
92 -- for example. As BEP generally take a batch as
93 -- an atomic entity, batch failure means all
94 -- child trxns are failed.
95 -- This status is also used when all trxns in
96 -- a batch failed on individual basis on the BEP side.
97 -- Usage: payable EC batch - iby_pay_batches_all.batch_status
98 -- payable iby merged batch - iby_batches_all.batchstatus
99 STATUS_QRY_BATCH_FAIL CONSTANT NUMBER :=207;
100
101 -- The statuses of some child trxns are obtained
102 -- through querying the BEP. Others are still unknown
103 -- (they will remain in STATUS_BATCH_PENDING).
104 -- Usage: payable EC batch - iby_pay_batches_all.batch_status
105 -- payable iby merged batch - iby_batches_all.batchstatus
106 STATUS_QRY_BATCH_PENDING CONSTANT NUMBER :=211;
107
108 -- This is the companion status for STATUS_QRY_BATCH_FAIL.
109 -- For trxn level. When a batch is failed at BEP side,
110 -- all it's child trxns will be failed also.
111 -- For a failed batch/trxn user must make corrections
112 -- in the originating EC app and submit them
113 -- as new trxns to iPayment.
114 -- Note for trxn only failure such as country rule
115 -- validation error for the trxn, it will get
116 -- STATUS_BEP_ERROR
117 -- Usage: payable trxn - iby_pay_payments_all.pmt_status
118 STATUS_QRY_TRXN_FAIL CONSTANT NUMBER :=220;
119
120 -- obseleted financing statuses
121 STATUS_LENDER_APPROVED CONSTANT NUMBER :=STATUS_OBSELETE;
122 STATUS_FINANCE_TIMEOUT CONSTANT NUMBER :=STATUS_OBSELETE;
123 STATUS_FINAPP_DRAFT_SAVED CONSTANT NUMBER :=STATUS_OBSELETE;
124 STATUS_FINAPP_RECEIVED CONSTANT NUMBER :=STATUS_OBSELETE;
125 STATUS_FINAPP_SENT CONSTANT NUMBER :=STATUS_OBSELETE;
126 STATUS_ADDINFO_SENT CONSTANT NUMBER :=STATUS_OBSELETE;
127 STATUS_ADDINFO_RECEIVED CONSTANT NUMBER :=STATUS_OBSELETE;
128 STATUS_FINAPP_EXTENDED CONSTANT NUMBER :=STATUS_OBSELETE;
129 STATUS_FINAPP_EXPIRED CONSTANT NUMBER :=STATUS_OBSELETE;
130 STATUS_FIN_OFFER_EXPIRED CONSTANT NUMBER :=STATUS_OBSELETE;
131 STATUS_FINANCE_OFFLINE CONSTANT NUMBER :=STATUS_OBSELETE;
132 STATUS_FINANCE_ADDINFO CONSTANT NUMBER :=STATUS_OBSELETE;
133 STATUS_FINANCE_CANCELLED CONSTANT NUMBER :=STATUS_OBSELETE;
134 STATUS_FIN_BUYER_ACCEPT CONSTANT NUMBER :=STATUS_OBSELETE;
135 STATUS_FIN_BUYER_DECLINE CONSTANT NUMBER :=STATUS_OBSELETE;
136 STATUS_LENDER_INIT_DECLINE CONSTANT NUMBER :=STATUS_OBSELETE;
137 STATUS_LENDER_DECLINE CONSTANT NUMBER :=STATUS_OBSELETE;
138 STATUS_FIN_COMPLETE CONSTANT NUMBER :=STATUS_OBSELETE;
139 STATUS_FIN_DOC_PROCESS CONSTANT NUMBER :=STATUS_OBSELETE;
140 STATUS_FIN_DOC_RECEIVED CONSTANT NUMBER :=STATUS_OBSELETE;
141 STATUS_FINAPP_PURGED CONSTANT NUMBER :=STATUS_OBSELETE;
142
143 --------------------End of Status Codes Definition-----------------------
144
145
146
147 G_EXPT_ERR CONSTANT VARCHAR2(80) := 'FND_API.G_EXC_ERROR';
148 G_EXPT_UNEXP_ERR CONSTANT VARCHAR2(80) := 'FND_API.G_EXC_UNEXPECTED_ERROR';
149 G_EXPT_OTR_ERR CONSTANT VARCHAR2(80) := 'G_EXC_OTHER_ERROR';
150
151 C_ERRCODE_PREFIX CONSTANT VARCHAR2(10) := 'IBY_';
152 C_TOKEN_CONCATENATOR CONSTANT VARCHAR2(1) := '#';
153 C_TOKEN_VAL_CONCATENATOR CONSTANT VARCHAR2(1) := '=';
154
155 -- Yes/no values for 'boolean' parameters of
156 -- all functions in all IBY* packages
157 --
158 C_API_YES CONSTANT VARCHAR2(1) := 'Y';
159 C_API_NO CONSTANT VARCHAR2(1) := 'N';
160
161
162 --
163 -- to_number wrapper that catches exceptions
164 --
165 FUNCTION to_num( p_str IN VARCHAR2 ) RETURN NUMBER;
166
167 -- Exceptions Handling Routine will do the following:
168 -- 1. Rollback to savepoint
169 -- 2. Handle expected, unexpected and other exceptions
170 -- 3. Add an error message to the API message list
171 -- 4. Return error status
172 --
173 -- The following is example of calling exception handling routines:
174 --
175
176 PROCEDURE handle_exceptions(
177 p_api_name IN VARCHAR2,
178 p_pkg_name IN VARCHAR2,
179 p_rollback_point IN VARCHAR2,
180 p_exception_type IN VARCHAR2,
181 x_msg_count OUT NOCOPY NUMBER,
182 x_msg_data OUT NOCOPY VARCHAR2,
183 x_return_status OUT NOCOPY VARCHAR2
184 );
185
186
187 --
188 -- Name: handleException
189 -- Args: p_err_msg => The error message associated with the exception
190 -- p_err_code => The error code generated by the exception
191 -- Notes: Scans the error message for the generated exception; if of the
192 -- form 'IBY_[0-9]*' then this is an iPayment internal error message
193 -- intended for Java which the procedure will then parse and correctly
194 -- put on the message stack
195 --
196 PROCEDURE handleException
197 (
198 p_err_msg IN VARCHAR2,
199 p_err_code IN VARCHAR2
200 );
201
202
203 --
204 -- Name: get_property
205 -- Args: p_name => property name
206 -- Outs: x_val => property value
207 --
208 -- Notes: gets an application-wide property
209 --
210 PROCEDURE get_property
211 (
212 p_name IN VARCHAR2,
213 x_val OUT NOCOPY VARCHAR2
214 );
215
216 --
217 -- Name: set_property
218 -- Args: p_name => property name
219 -- p_val => property value
220 -- Notes: properties are set at the site level
221 --
222 --
223 PROCEDURE set_property
224 (
225 p_name IN VARCHAR2,
226 p_val IN VARCHAR2
227 );
228
229 --
230 -- Name: get_jtf_property
231 -- Args: p_name => property name
232 -- Outs: x_val => property value
233 --
234 -- Notes: gets an IBY property from old JTF Property manager tables.
235 -- only the first element of the value list is returned.
236 -- this function is used by ibyprupg.sql
237 --
238 FUNCTION get_jtf_property(p_name IN VARCHAR2) RETURN VARCHAR2;
239
240
241 --
242 -- Name: encode64
243 -- Args: s => string to encode
244 -- Return: the given string in base64 encoding
245 --
246 FUNCTION encode64(s IN VARCHAR2) RETURN VARCHAR2;
247
248 --
249 -- Name: decode64
250 -- Args: s => base64 encoded string
251 -- Return: the given string decoded
252 --
253 FUNCTION decode64(s IN VARCHAR2) RETURN VARCHAR2;
254
255
256 --
257 -- Name: get_local_nls
258 -- Args: none
259 -- Return: the local (i.e. database) characterset.
260 --
261 FUNCTION get_local_nls RETURN VARCHAR2;
262
263 --
264 -- Name: get_nls_charset
265 -- Args: p_nls => NLSLang parameter (e.g. AMERICAN_AMERICA.US7ASCII)
266 -- Return: The character set encoding portion of the
267 -- NLSLang, e.g.:
268 -- get_nls_charset('AMERICAN_AMERICA.US7ASCII') = 'US7ASCII'
269 --
270 FUNCTION get_nls_charset( p_nls VARCHAR2 ) RETURN VARCHAR2;
271
272 --
273 -- Name: MAKE_ASCII
274 -- Args: p_from_text => text to be converted.
275 -- Return: The text converted to the US7ASCII character set.
276 --
277 FUNCTION MAKE_ASCII(p_from_text IN VARCHAR2) RETURN VARCHAR2;
278
279 --
280 -- Name: get_call_exec
281 -- Args: p_pkg_name => name of the package in which the procedure is defined
282 -- p_function_name => name of the function/procedure to invoke
283 -- p_params => Array of fixed parameters to the call; all positions
284 -- should be represented, with arguments that will
285 -- take bind variables given null values
286 -- Return: String usable by EXECUTE IMMEDIATE to invoke the
287 -- PL/SQL procedure
288 --
289 FUNCTION get_call_exec
290 (
291 p_pkg_name VARCHAR2,
292 p_function_name VARCHAR2,
293 p_params JTF_VARCHAR2_TABLE_200
294 )
295 RETURN VARCHAR2;
296
297 --
298 -- Name: set_view_param
299 -- Args: p_name => view parameter name
300 -- p_val => view parameter value
301 --
302 -- Notes: The name-value pair is lost at the end of
303 -- the current transaction
304 --
305 PROCEDURE set_view_param
306 (
307 p_name iby_view_parameters_gt.name%TYPE,
308 p_val iby_view_parameters_gt.value%TYPE
309 );
310
311 --
312 -- Name: get_view_param
313 -- Args: p_name => view parameter name
314 -- Return: The value of the parameter, or NULL if not defined
315 --
316 FUNCTION get_view_param( p_name iby_view_parameters_gt.name%TYPE )
317 RETURN iby_view_parameters_gt.value%TYPE;
318
319 --
320 -- Name: check_lookup_val
321 -- Args: p_val => lookup value
322 -- p_lookup => lookup name code
323 -- Return: True if the lookup contains that value code
324 --
325 --
326 FUNCTION check_lookup_val(p_val IN VARCHAR2, p_lookup IN VARCHAR2)
327 RETURN BOOLEAN;
328
329 --
330 -- Return: True if the party id is valid
331 FUNCTION validate_party_id(p_party_id IN hz_parties.party_id%TYPE)
332 RETURN BOOLEAN;
333
334 --
335 -- Return: True if the application id is valid
336 FUNCTION validate_app_id( p_app_id IN fnd_application.application_id%TYPE )
337 RETURN BOOLEAN;
338
339 --
340 -- Return: True if the territory code is valid
341 FUNCTION validate_territory
342 ( p_territory IN fnd_territories.territory_code%TYPE )
343 RETURN BOOLEAN;
344
345 --
346 -- Return: True if the string is trivial
347 FUNCTION is_trivial(p_string VARCHAR2)
348 RETURN BOOLEAN;
349
350 FUNCTION validate_organization(p_org_id IN iby_trxn_summaries_all.org_id%TYPE,
351 p_org_type IN iby_trxn_summaries_all.org_type%TYPE)
352 RETURN VARCHAR2;
353
354
355 PROCEDURE validate_pmt_channel_code(p_instrument_type IN iby_creditcard.instrument_type%TYPE,
356 p_payment_channel_code IN OUT NOCOPY iby_trxn_summaries_all.payment_channel_code%TYPE,
357 p_valid OUT NOCOPY VARCHAR2);
358
359
360
361 /*-----------------------------------------------------------------------------------------
362 | FUNCTION - get_psr_snapshot_count
363 | DESCRIPTION - This function is designed for the Payables Payment Manager
364 | Home Page . The function returns the total count of Payment
365 | Process Requests with a particular Status or a combination
366 | of Payment Process Request Statuses that map to a particular
367 | snapshot code
368 |
369 | SNAPSHOT CODE STATUS
370 | ------------- ------------------------------------------------
371 | NEED_ACTION_BY_ME AP:
372 | 'REVIEW', 'MISSING RATES'
373 | IBY:
374 | 'INFORMATION_REQUIRED'
375 | 'PENDING_REVIEW_DOC_VAL_ERRORS',
376 | 'PENDING_REVIEW_PMT_VAL_ERRORS',
377 | 'PENDING_REVIEW'
378 |
379 | PROCESSING AP:
380 | 'UNSTARTED', 'SELECTING', 'CANCELING',
381 | 'CALCULATING', 'SELECTED'
382 | IBY:
383 | 'INSERTED', 'SUBMITTED',
384 | 'ASSIGNMENT_COMPLETE','DOCUMENTS_VALIDATED',
385 | 'RETRY_DOCUMENT_VALIDATION',
386 | 'RETRY_PAYMENT_CREATION'
387 |
388 | USER_TERMINATED AP:
389 | 'CANCELED' , 'CANCELLED NO PAYMENTS'
390 | IBY:
391 | 'TERMINATED'
392 |
393 | PROGRAM_ERRORS IBY:
394 | 'PENDING_REVIEW_DOC_VAL_ERRORS'
395 | 'PENDING_REVIEW_PMT_VAL_ERRORS'
396 |
397 | COMPLETED IBY:
398 | 'PAYMENTS_CREATED'
399 |
400 | TOTAL COUNT(*) IN AP
401 |
402 -----------------------------------------------------------------------------------------
403 */
404 FUNCTION get_psr_snapshot_count(p_snapshot_code IN VARCHAR2)
405 RETURN NUMBER;
406
407
408 /*-----------------------------------------------------------------------------------------
409 FUNCTION - get_payment_status_flag
410 DESCRIPTION - This function returns the status w.r.t payments that have been
411 created in IBY for this Payment Process Request(payment_service_request_id)
412 ------------------------------------------------------------------------------------------
413 */
414
415 FUNCTION get_payment_status_flag(p_psr_id IN NUMBER)
416 RETURN VARCHAR2;
417
418
419 /*-----------------------------------------------------------------------------------------
420 FUNCTION - get_psr_status
421 DESCRIPTION - This function returns the status w.r.t payments that have been
422 created in IBY for this Payment Process Request(payment_service_request_id)
423
424 ------------------------------------------------------------------------------------------
425 */
426 FUNCTION get_psr_status(p_psr_id IN NUMBER,
427 p_psr_status IN VARCHAR2)
428 RETURN VARCHAR2;
429
430 /* Bug Number: 7279395
431 * This procedure is used to initialize the table type variable
432 * g_psr_table.
433 * The pages which are accessing the functions get_psr_status and
434 * get_payment_status_flag should take the responsibility of initializing
435 * g_psr_table by calling this procedure.
436 */
437 PROCEDURE initialize;
438
439 Function check_user_access(p_pay_instruction_id IN Number) RETURN VARCHAR2;
440
441
442 END IBY_UTILITY_PVT;
[Home] [Help]