1 PACKAGE IBY_UTILITY_PVT AUTHID CURRENT_USER AS
2 /* $Header: ibyvutls.pls 120.21 2011/12/12 10:21:02 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 PARAM_EXTRACT_DEBUG_MODE CONSTANT VARCHAR2(30) := 'EXTRACT_DEBUG_MODE';
162
163 /*New parameters for : 10022548 */
164 TYPE snapshot_count_type IS RECORD (
165 l_need_action NUMBER,
166 l_processing NUMBER,
167 l_terminated NUMBER,
168 l_errors NUMBER,
169 l_completed NUMBER
170 );
171 /*End of New parameters for : 10022548 */
172 TYPE snapshot_count_t IS TABLE OF snapshot_count_type ;
173
174
175 --
176 -- to_number wrapper that catches exceptions
177 --
178 FUNCTION to_num( p_str IN VARCHAR2 ) RETURN NUMBER;
179
180 --
181 -- Returns 'Y' if the input is numeric. Else 'N'
182 --
183 FUNCTION isNumeric (p_input IN VARCHAR2) RETURN VARCHAR2;
184
185 -- Exceptions Handling Routine will do the following:
186 -- 1. Rollback to savepoint
187 -- 2. Handle expected, unexpected and other exceptions
188 -- 3. Add an error message to the API message list
189 -- 4. Return error status
190 --
191 -- The following is example of calling exception handling routines:
192 --
193
194 PROCEDURE handle_exceptions(
195 p_api_name IN VARCHAR2,
196 p_pkg_name IN VARCHAR2,
197 p_rollback_point IN VARCHAR2,
198 p_exception_type IN VARCHAR2,
199 x_msg_count OUT NOCOPY NUMBER,
200 x_msg_data OUT NOCOPY VARCHAR2,
201 x_return_status OUT NOCOPY VARCHAR2
202 );
203
204
205 --
206 -- Name: handleException
207 -- Args: p_err_msg => The error message associated with the exception
208 -- p_err_code => The error code generated by the exception
209 -- Notes: Scans the error message for the generated exception; if of the
210 -- form 'IBY_[0-9]*' then this is an iPayment internal error message
211 -- intended for Java which the procedure will then parse and correctly
212 -- put on the message stack
213 --
214 PROCEDURE handleException
215 (
216 p_err_msg IN VARCHAR2,
217 p_err_code IN VARCHAR2
218 );
219
220
221 --
222 -- Name: get_property
223 -- Args: p_name => property name
224 -- Outs: x_val => property value
225 --
226 -- Notes: gets an application-wide property
227 --
228 PROCEDURE get_property
229 (
230 p_name IN VARCHAR2,
231 x_val OUT NOCOPY VARCHAR2
232 );
233
234 --
235 -- Name: set_property
236 -- Args: p_name => property name
237 -- p_val => property value
238 -- Notes: properties are set at the site level
239 --
240 --
241 PROCEDURE set_property
242 (
243 p_name IN VARCHAR2,
244 p_val IN VARCHAR2
245 );
246
247 --
248 -- Name: get_jtf_property
249 -- Args: p_name => property name
250 -- Outs: x_val => property value
251 --
252 -- Notes: gets an IBY property from old JTF Property manager tables.
253 -- only the first element of the value list is returned.
254 -- this function is used by ibyprupg.sql
255 --
256 FUNCTION get_jtf_property(p_name IN VARCHAR2) RETURN VARCHAR2;
257
258
259 --
260 -- Name: encode64
261 -- Args: s => string to encode
262 -- Return: the given string in base64 encoding
263 --
264 FUNCTION encode64(s IN VARCHAR2) RETURN VARCHAR2;
265
266 --
267 -- Name: decode64
268 -- Args: s => base64 encoded string
269 -- Return: the given string decoded
270 --
271 FUNCTION decode64(s IN VARCHAR2) RETURN VARCHAR2;
272
273
274 --
275 -- Name: get_local_nls
276 -- Args: none
277 -- Return: the local (i.e. database) characterset.
278 --
279 FUNCTION get_local_nls RETURN VARCHAR2;
280
281 --
282 -- Name: get_nls_charset
283 -- Args: p_nls => NLSLang parameter (e.g. AMERICAN_AMERICA.US7ASCII)
284 -- Return: The character set encoding portion of the
285 -- NLSLang, e.g.:
286 -- get_nls_charset('AMERICAN_AMERICA.US7ASCII') = 'US7ASCII'
287 --
288 FUNCTION get_nls_charset( p_nls VARCHAR2 ) RETURN VARCHAR2;
289
290 --
291 -- Name: MAKE_ASCII
292 -- Args: p_from_text => text to be converted.
293 -- Return: The text converted to the US7ASCII character set.
294 --
295 FUNCTION MAKE_ASCII(p_from_text IN VARCHAR2) RETURN VARCHAR2;
296
297 --
298 -- Name: get_call_exec
299 -- Args: p_pkg_name => name of the package in which the procedure is defined
300 -- p_function_name => name of the function/procedure to invoke
301 -- p_params => Array of fixed parameters to the call; all positions
302 -- should be represented, with arguments that will
303 -- take bind variables given null values
304 -- Return: String usable by EXECUTE IMMEDIATE to invoke the
305 -- PL/SQL procedure
306 --
307 FUNCTION get_call_exec
308 (
309 p_pkg_name VARCHAR2,
310 p_function_name VARCHAR2,
311 p_params JTF_VARCHAR2_TABLE_200
312 )
313 RETURN VARCHAR2;
314
315 --
316 -- Name: set_view_param
317 -- Args: p_name => view parameter name
318 -- p_val => view parameter value
319 --
320 -- Notes: The name-value pair is lost at the end of
321 -- the current transaction
322 --
323 PROCEDURE set_view_param
324 (
325 p_name iby_view_parameters_gt.name%TYPE,
326 p_val iby_view_parameters_gt.value%TYPE
327 );
328
329 --
330 -- Name: get_view_param
331 -- Args: p_name => view parameter name
332 -- Return: The value of the parameter, or NULL if not defined
333 --
334 FUNCTION get_view_param( p_name iby_view_parameters_gt.name%TYPE )
335 RETURN iby_view_parameters_gt.value%TYPE;
336
337 --
338 -- Name: check_lookup_val
339 -- Args: p_val => lookup value
340 -- p_lookup => lookup name code
344 FUNCTION check_lookup_val(p_val IN VARCHAR2, p_lookup IN VARCHAR2)
341 -- Return: True if the lookup contains that value code
342 --
343 --
345 RETURN BOOLEAN;
346
347 --
348 -- Return: True if the party id is valid
349 FUNCTION validate_party_id(p_party_id IN hz_parties.party_id%TYPE)
350 RETURN BOOLEAN;
351
352 --
353 -- Return: True if the application id is valid
354 FUNCTION validate_app_id( p_app_id IN fnd_application.application_id%TYPE )
355 RETURN BOOLEAN;
356
357 --
358 -- Return: True if the territory code is valid
359 FUNCTION validate_territory
360 ( p_territory IN fnd_territories.territory_code%TYPE )
361 RETURN BOOLEAN;
362
363 --
364 -- Return: True if the string is trivial
365 FUNCTION is_trivial(p_string VARCHAR2)
366 RETURN BOOLEAN;
367
368 FUNCTION validate_organization(p_org_id IN iby_trxn_summaries_all.org_id%TYPE,
369 p_org_type IN iby_trxn_summaries_all.org_type%TYPE)
370 RETURN VARCHAR2;
371
372
373 PROCEDURE validate_pmt_channel_code(p_instrument_type IN iby_creditcard.instrument_type%TYPE,
374 p_payment_channel_code IN OUT NOCOPY iby_trxn_summaries_all.payment_channel_code%TYPE,
375 p_valid OUT NOCOPY VARCHAR2);
376
377
378
379 /*-----------------------------------------------------------------------------------------
380 | FUNCTION - get_psr_snapshot_count
381 | DESCRIPTION - This function is designed for the Payables Payment Manager
382 | Home Page . The function returns the total count of Payment
383 | Process Requests with a particular Status or a combination
384 | of Payment Process Request Statuses that map to a particular
385 | snapshot code
386 |
387 | SNAPSHOT CODE STATUS
388 | ------------- ------------------------------------------------
389 | NEED_ACTION_BY_ME AP:
390 | 'REVIEW', 'MISSING RATES'
391 | IBY:
392 | 'INFORMATION_REQUIRED'
393 | 'PENDING_REVIEW_DOC_VAL_ERRORS',
394 | 'PENDING_REVIEW_PMT_VAL_ERRORS',
395 | 'PENDING_REVIEW'
396 |
397 | PROCESSING AP:
398 | 'UNSTARTED', 'SELECTING', 'CANCELING',
399 | 'CALCULATING', 'SELECTED'
400 | IBY:
401 | 'INSERTED', 'SUBMITTED',
402 | 'ASSIGNMENT_COMPLETE','DOCUMENTS_VALIDATED',
403 | 'RETRY_DOCUMENT_VALIDATION',
404 | 'RETRY_PAYMENT_CREATION'
405 |
406 | USER_TERMINATED AP:
407 | 'CANCELED' , 'CANCELLED NO PAYMENTS'
408 | IBY:
409 | 'TERMINATED'
410 |
411 | PROGRAM_ERRORS IBY:
412 | 'PENDING_REVIEW_DOC_VAL_ERRORS'
413 | 'PENDING_REVIEW_PMT_VAL_ERRORS'
414 |
415 | COMPLETED IBY:
416 | 'PAYMENTS_CREATED'
417 |
418 | TOTAL COUNT(*) IN AP
419 |
420 -----------------------------------------------------------------------------------------
421 */
422 FUNCTION get_psr_snapshot_count(p_snapshot_code IN VARCHAR2)
423 RETURN NUMBER;
424
425
426 /*-----------------------------------------------------------------------------------------
427 | FUNCTION - get_psr_snapshot_count PIPELINED.
428 |
429 | DESCRIPTION - This function is designed for the Payables Payment Manager
430 | Home Page . The function returns the total count of Payment
431 | Process Requests with a particular Status or a combination
432 | of Payment Process Request Statuses that map to a particular
433 | snapshot code
434 |
435 | SNAPSHOT CODE STATUS
436 | ------------- ------------------------------------------------
437 | NEED_ACTION_BY_ME AP:
438 | 'REVIEW', 'MISSING RATES'
439 | IBY:
440 | 'INFORMATION_REQUIRED'
441 | 'PENDING_REVIEW_DOC_VAL_ERRORS',
442 | 'PENDING_REVIEW_PMT_VAL_ERRORS',
443 | 'PENDING_REVIEW'
444 |
445 | PROCESSING AP:
446 | 'UNSTARTED', 'SELECTING', 'CANCELING',
447 | 'CALCULATING', 'SELECTED'
448 | IBY:
449 | 'INSERTED', 'SUBMITTED',
450 | 'ASSIGNMENT_COMPLETE','DOCUMENTS_VALIDATED',
451 | 'RETRY_DOCUMENT_VALIDATION',
452 | 'RETRY_PAYMENT_CREATION'
453 |
454 | USER_TERMINATED AP:
455 | 'CANCELED' , 'CANCELLED NO PAYMENTS'
456 | IBY:
457 | 'TERMINATED'
458 |
459 | PROGRAM_ERRORS IBY:
460 | 'PENDING_REVIEW_DOC_VAL_ERRORS'
461 | 'PENDING_REVIEW_PMT_VAL_ERRORS'
462 |
463 | COMPLETED IBY:
464 | 'PAYMENTS_CREATED'
465 |
466 | TOTAL COUNT(*) IN AP
467 |
468 |===========================================================================================
469 |Understanding PIPELINED FUNCTION:
470 |-----------------------------------
474 |PIPELINED functions will operate like a table.
471 |PIPELINED functions are piece of code that can be used for querying SQL.
472 |Basically, when you would like a PLSQL routine to be the source
473 |of data -- instead of a table -- you would use a pipelined function.
475 |Using PL/SQL table functions can significantly lower the over-head of
476 |doing such transformations. PL/SQL table functions accept and return
477 |multiple rows, delivering them as they are ready rather than all at once,
478 |and can be made to execute as parallel operations.
479 |
480 -----------------------------------------------------------------------------------------
481 */
482 FUNCTION get_psr_snapshot_pipe RETURN snapshot_count_t PIPELINED;
483
484
485 /*-----------------------------------------------------------------------------------------
486 FUNCTION - get_payment_status_flag
487 DESCRIPTION - This function returns the status w.r.t payments that have been
488 created in IBY for this Payment Process Request(payment_service_request_id)
489 ------------------------------------------------------------------------------------------
490 */
491
492 FUNCTION get_payment_status_flag(p_psr_id IN NUMBER,
493 p_from_cache IN VARCHAR2 DEFAULT 'FALSE')
494 RETURN VARCHAR2;
495
496
497 /*-----------------------------------------------------------------------------------------
498 FUNCTION - get_psr_status
499 DESCRIPTION - This function returns the status w.r.t payments that have been
500 created in IBY for this Payment Process Request(payment_service_request_id)
501
502 ------------------------------------------------------------------------------------------
503 */
504 FUNCTION get_psr_status(p_psr_id IN NUMBER,
505 p_psr_status IN VARCHAR2,
506 p_from_cache IN VARCHAR2 DEFAULT 'FALSE')
507 RETURN VARCHAR2;
508
509 /* Bug Number: 7279395
510 * This procedure is used to initialize the table type variable
511 * g_psr_table.
512 * The pages which are accessing the functions get_psr_status and
513 * get_payment_status_flag should take the responsibility of initializing
514 * g_psr_table by calling this procedure.
515 */
516 PROCEDURE initialize;
517
518 Function check_user_access(p_pay_instruction_id IN Number) RETURN VARCHAR2;
519
520
521 /*-----------------------------------------------------------------------------------------
522 | FUNCTION - get_format_program_name
523 | DESCRIPTION - This method returns the name of the concurrent program to be invoked for
524 | for formatting given the instruction id. The valid return values can be
525 | IBY_FD_PAYMENT_FORMAT, IBY_FD_PAYMENT_FORMAT_TEXT
526 ------------------------------------------------------------------------------------------
527 */
528 FUNCTION get_format_program_name(p_pay_instruction_id IN NUMBER)
529 RETURN VARCHAR2;
530
531
532 FUNCTION check_org_access( p_payment_service_request_id IN NUMBER)
533 RETURN VARCHAR2;
534
535 /*-----------------------------------------------------------------------------------------
536 FUNCTION - Spawn_Random_Card
537 DESCRIPTION - Utility function to help scramble production data on a cloned instance.
538 ------------------------------------------------------------------------------------------
539 */
540 FUNCTION Spawn_Random_Card
541 (p_prefix IN VARCHAR2,
542 p_card_len IN NUMBER,
543 p_mod_check IN VARCHAR2
544 )
545 RETURN VARCHAR2;
546
547 PROCEDURE init_debug_mode_for_extract;
548
549 --
550 -- Name: debug_mode_for_extract_enabled
551 -- Return: Y/N
552 --
553 FUNCTION debug_mode_for_extract_enabled
554 RETURN VARCHAR2;
555
556
557 -- This function populates psr snapshot PPRs in to GT table
558 PROCEDURE populate_psr_snapshot_count;
559
560 END IBY_UTILITY_PVT;
[Home] [Help]