1 PACKAGE ari_utilities AUTHID CURRENT_USER AS
2 /* $Header: ARIUTILS.pls 120.18.12020000.3 2012/12/25 18:33:10 shvimal ship $ */
3
4
5 FUNCTION check_external_user_access (p_person_party_id IN VARCHAR2,
6 p_customer_id IN VARCHAR2,
7 p_customer_site_use_id IN VARCHAR2)
8 RETURN VARCHAR2 ;
9
10 PROCEDURE send_notification(p_user_name IN VARCHAR2,
11 p_customer_name IN VARCHAR2,
12 p_request_id IN NUMBER,
13 p_requests IN NUMBER,
14 p_parameter IN VARCHAR2,
15 p_subject_msg_name IN VARCHAR2,
16 p_subject_msg_appl IN VARCHAR2 DEFAULT 'AR',
17 p_body_msg_name IN VARCHAR2 DEFAULT NULL,
18 p_body_msg_appl In VARCHAR2 DEFAULT 'AR');
19
20 /*========================================================================
21 | PUBLIC function curr_round_amt
22 |
23 | DESCRIPTION
24 | Rounds a given amount based on the precision defined for the currency code.
25 | ----------------------------------------
26 |
27 | PSEUDO CODE/LOGIC
28 | This function rounds the amount based on the precision defined for the
29 | currency code.
30 |
31 | PARAMETERS
32 | amount IN NUMBER Input amount for rounding
33 | currency_code IN VARCHAR2 Currency Code
34 |
35 | RETURNS
36 | NUMBER Rounded Amount
37 |
38 | KNOWN ISSUES
39 |
40 | NOTES
41 |
42 | MODIFICATION HISTORY
43 | Date Author Description of Changes
44 | 17-DEC-2004 vnb Created
45 |
46 *=======================================================================*/
47 FUNCTION curr_round_amt( p_amount IN NUMBER,
48 p_currency_code IN VARCHAR2)
49 RETURN NUMBER;
50
51 TYPE t_ar_lookups_table IS TABLE OF VARCHAR2(80)
52 INDEX BY BINARY_INTEGER;
53
54 pg_ar_lookups_rec t_ar_lookups_table;
55
56 FUNCTION get_lookup_meaning (p_lookup_type IN VARCHAR2,
57 p_lookup_code IN VARCHAR2)
58 RETURN VARCHAR2;
59
60 FUNCTION get_bill_to_site_use_id (p_address_id IN NUMBER) RETURN NUMBER;
61
62 FUNCTION get_site_uses (p_address_id IN NUMBER) RETURN VARCHAR2;
63
64 FUNCTION site_use_meaning (p_site_use IN VARCHAR2) RETURN VARCHAR2;
65
66 FUNCTION cust_srch_sec_predicate(obj_schema VARCHAR2,
67 obj_name VARCHAR2) RETURN VARCHAR2;
68
69 FUNCTION get_default_currency ( p_customer_id IN VARCHAR2,
70 p_session_id IN VARCHAR2)
71
72 RETURN VARCHAR2;
73
74 --------------------------------------------------------------------------------
75 --Check if the person party has access to this customer site
76 FUNCTION check_site_access (p_person_party_id IN VARCHAR2,
77 p_customer_id IN VARCHAR2,
78 p_customer_site_use_id IN VARCHAR2)
79 RETURN VARCHAR2;
80 --------------------------------------------------------------------------------
81 --Check if the admin identified by p_person_party_id has access to this customer
82 FUNCTION check_admin_access (p_person_party_id IN VARCHAR2,
83 p_customer_id IN VARCHAR2)
84 RETURN VARCHAR2;
85 --------------------------------------------------------------------------------
86
87
88 /*========================================================================
89 | PUBLIC procedure get_contact_id
90 |
91 | DESCRIPTION
92 | Returns contact id of the given site at the customer/site level
93 | ----------------------------------------
94 |
95 | PSEUDO CODE/LOGIC
96 |
97 | PARAMETERS
98 | p_customer_id IN Customer Id
99 | p_customer_site_use_id IN Customer Site Id
100 | p_contact_role_type IN Contact Role Type
101 |
102 | RETURNS
103 | l_contact_id Contact id of the given site at the customer/site level
104 | KNOWN ISSUES
105 |
106 | MODIFICATION HISTORY
107 | Date Author Description of Changes
108 | 12-AUG-2005 rsinthre Created
109 *=======================================================================*/
110 FUNCTION get_contact_id(p_customer_id IN NUMBER,
111 p_customer_site_use_id IN NUMBER DEFAULT NULL,
112 p_contact_role_type IN VARCHAR2 DEFAULT 'ALL') RETURN NUMBER;
113
114
115 /*========================================================================
116 | PUBLIC procedure get_contact
117 |
118 | DESCRIPTION
119 | Returns contact name of the given site at the customer/site level
120 | ----------------------------------------
121 |
122 | PSEUDO CODE/LOGIC
123 |
124 | PARAMETERS
125 | p_customer_id IN Customer Id
126 | p_customer_site_use_id IN Customer Site Id
127 | p_contact_role_type IN Contact Role Type
128 |
129 | RETURNS
130 | l_contact_name Contact name of the given site at the customer/site level
131 | KNOWN ISSUES
132 |
133 | MODIFICATION HISTORY
134 | Date Author Description of Changes
135 | 12-AUG-2005 rsinthre Created
136 *=======================================================================*/
137 FUNCTION get_contact(p_customer_id IN NUMBER,
138 p_customer_site_use_id IN NUMBER,
139 p_contact_role_type IN VARCHAR2 DEFAULT 'ALL') RETURN VARCHAR2;
140
141 /*========================================================================
142 | PUBLIC procedure get_contact
143 |
144 | DESCRIPTION
145 | Returns contact name of the given contact id
146 | ----------------------------------------
147 |
148 | PSEUDO CODE/LOGIC
149 |
150 | PARAMETERS
151 | p_contact_id IN Customer Id
152 |
153 | RETURNS
154 | l_contact_name Contact name of the given contact id
155 | KNOWN ISSUES
156 |
157 | MODIFICATION HISTORY
158 | Date Author Description of Changes
159 | 5-JUL-2005 hikumar Created
160 *=======================================================================*/
161 FUNCTION get_contact(p_contact_id IN NUMBER) RETURN VARCHAR2;
162
163 /*========================================================================
164 | PUBLIC procedure get_phone
165 |
166 | DESCRIPTION
167 | Returns contact point of the given contact type, site at the customer/site level
168 | ----------------------------------------
169 |
170 | PSEUDO CODE/LOGIC
171 |
172 | PARAMETERS
173 | p_customer_id IN Customer Id
174 | p_customer_site_use_id IN Customer Site Id
175 | p_contact_role_type IN Contact Role Type
176 | p_phone_type IN contact type like 'PHONE', 'FAX', 'GEN' etc
177 |
178 | RETURNS
179 | l_contact_phone Contact type number of the given site at the customer/site level
180 | KNOWN ISSUES
181 |
182 | MODIFICATION HISTORY
183 | Date Author Description of Changes
184 | 12-AUG-2005 rsinthre Created
185 | 24-DEC-2012 shvimal Removed DEFAULT from p_contact_role_type parameter
186 *=======================================================================*/
187 FUNCTION get_phone(p_customer_id IN NUMBER,
188 p_customer_site_use_id IN NUMBER DEFAULT NULL,
189 p_contact_role_type IN VARCHAR2,
190 p_phone_type IN VARCHAR2 DEFAULT 'ALL') RETURN VARCHAR2;
191 /*========================================================================
192 | PUBLIC procedure get_phone
193 |
194 | DESCRIPTION
195 | Returns contact point of the given contact id
196 | ----------------------------------------
197 |
198 | PSEUDO CODE/LOGIC
199 |
200 | PARAMETERS
201 | p_contact_id IN Customer Id
202 | p_phone_type IN contact type like 'PHONE', 'FAX', 'GEN' etc
203 |
204 | RETURNS
205 | l_contact_phone Contact type number of the given contact id
206 | KNOWN ISSUES
207 |
208 | MODIFICATION HISTORY
209 | Date Author Description of Changes
210 | 5-JUL-2005 hikumar Created
211 *=======================================================================*/
212 FUNCTION get_phone(p_contact_id IN NUMBER,
213 p_phone_type IN VARCHAR2 DEFAULT 'ALL') RETURN VARCHAR2;
214
215 FUNCTION get_service_charge_activity_id ( p_customer_id IN VARCHAR2,
216 p_customer_site_use_id IN VARCHAR2 DEFAULT NULL) RETURN NUMBER;
217
218 FUNCTION is_service_charge_enabled ( p_customer_id IN VARCHAR2,
219 p_customer_site_use_id IN VARCHAR2 DEFAULT NULL) RETURN boolean;
220
221 FUNCTION get_max_future_payment_date( p_customer_id IN VARCHAR2,
222 p_customer_site_use_id IN VARCHAR2 DEFAULT NULL) RETURN DATE;
223
224 FUNCTION save_payment_instrument_info ( p_customer_id IN VARCHAR2,
225 p_customer_site_use_id IN VARCHAR2 DEFAULT NULL) RETURN BOOLEAN;
226
227 FUNCTION is_save_payment_instr_enabled ( p_customer_id IN VARCHAR2,
228 p_customer_site_use_id IN VARCHAR2 DEFAULT NULL) RETURN VARCHAR2;
229
230 FUNCTION is_aging_enabled ( p_customer_id IN VARCHAR2,
231 p_customer_site_use_id IN VARCHAR2 DEFAULT NULL) RETURN VARCHAR2;
232
233 FUNCTION multi_print_limit ( p_customer_id IN VARCHAR2,
234 p_customer_site_use_id IN VARCHAR2 DEFAULT NULL) RETURN VARCHAR2;
235
236 FUNCTION is_discount_grace_days_enabled ( p_customer_id IN VARCHAR2,
237 p_customer_site_use_id IN VARCHAR2 DEFAULT NULL) RETURN VARCHAR2;
238
239 FUNCTION is_discount_grace_days_enabled RETURN boolean;
240
241 -- this procedure returns the html used for the 'Contact Us'
242 -- icon.
243
244 PROCEDURE get_contact_info (
245 p_customer_id IN VARCHAR2,
246 p_customer_site_use_id IN VARCHAR2,
247 p_language_string IN VARCHAR2,
248 p_page IN VARCHAR2,
249 p_trx_id IN VARCHAR2,
250 p_output_string OUT NOCOPY VARCHAR2
251 );
252
253 FUNCTION get_site_use_location (p_address_id IN NUMBER) RETURN VARCHAR2;
254
255 /*========================================================================
256 | PUBLIC function get_site_use_code
257 |
258 | DESCRIPTION
259 | Function returns the site use codes for the given adddress id
260 |
261 | PARAMETERS
262 | p_address_id IN NUMBER
263 |
264 | RETURNS
265 | Site Use Codes for the given address id.
266 |
267 | MODIFICATION HISTORY
268 | Date Author Description of Changes
269 | 12-Feb-2007 abhisjai Created
270 *=======================================================================*/
271 FUNCTION get_site_use_code (p_address_id IN NUMBER) RETURN VARCHAR2;
272
273 /*========================================================================
274 | PUBLIC function is_routing_number_valid
275 |
276 | DESCRIPTION
277 | Determines if a given routing number is valid.
278 | ----------------------------------------
279 |
280 | PSEUDO CODE/LOGIC
281 | This function validates routing number, note currently it only
282 | validates US specific ABA (ACH) routing number. When other
283 | types are added also new logic needs to be introduced.
284 |
285 | PARAMETERS
286 | p_routing_number IN Routing number
287 | p_routing_number_type IN Routing number type, defaults to ABA
288 |
289 | RETURNS
290 | 1 if Routing number is valid
291 | 0 if Routing number is invalid
292 |
293 | KNOWN ISSUES
294 |
295 | NOTES
296 |
297 | MODIFICATION HISTORY
298 | Date Author Description of Changes
299 | 27-Aug-2009 avepati Created
300 |
301 *=======================================================================*/
302 FUNCTION is_routing_number_valid(p_routing_number IN VARCHAR2,
303 p_routing_number_type IN VARCHAR2 DEFAULT 'ABA') RETURN NUMBER;
304
308 | DESCRIPTION
305 /*========================================================================
306 | PUBLIC function validate_ACH_checksum
307 |
309 | Determines if a given ACH routing number checksum is valid.
310 | ----------------------------------------
311 |
312 | PSEUDO CODE/LOGIC
313 | This function validates US specific ACH routing number.
314 | Note that even if a number passes this test, it does not
315 | necessarily mean that it is valid. The number may not, in fact,
316 | be assigned to any financial institution. ACH routing numbers are
317 | always nine digits long. The first four specify the routing
318 | symbol, the next four identify the institution and the last is
319 | the checksum digit.
320 | Here's how the algorithm works. First the code strips out any non-numeric characters
321 | (like dashes or spaces) and makes sure the resulting string's length is nine digits,
322 | 7 8 9 4 5 6 1 2 4
323 | Then we multiply the first digit by 3, the second by 7, the third by 1, the fourth by 3,
324 | the fifth by 7, the sixth by 1, etc., and add them all up.
325 | (7 x 3) + (8 x 7) + (9 x 1) +
326 | (4 x 3) + (5 x 7) + (6 x 1) +
327 | (1 x 3) + (2 x 7) + (4 x 1) = 160
328 | If this sum is an integer multiple of 10 (e.g., 10, 20, 30, 40, 50,...) then the number
329 | is valid, as far as the checksum is concerned.
330 |
331 | PARAMETERS
332 | p_routing_number IN ACH Routing number
333 |
334 | RETURNS
335 | 1 if ACH Routing number is valid
336 | 0 if ACH Routing number is invalid
337 |
338 | KNOWN ISSUES
339 |
340 | NOTES
341 |
342 | MODIFICATION HISTORY
343 | Date Author Description of Changes
344 | 27-Aug-2009 avepati Created
345 |
346 *=======================================================================*/
347 FUNCTION validate_ACH_checksum(p_routing_number IN VARCHAR2) RETURN NUMBER;
348
349 /*===========================================================================+
350 | FUNCTION validate_ACH_routing_number |
351 | |
352 | DESCRIPTION |
353 | This function validates that given routing number is an existing ACH |
354 | bank. |
355 | |
356 | SCOPE - PUBLIC |
357 | |
358 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
359 | None |
360 | |
361 | ARGUMENTS : IN: p_routing_number Routing Number |
362 | |
363 | RETURNS : 1 routing number is valid |
364 | 0 routing number is invalid |
365 | |
366 | MODIFICATION HISTORY |
367 | 27-Aug-2009 avepati Created |
368 | |
369 +===========================================================================*/
370 FUNCTION validate_ACH_routing_number(p_routing_number IN VARCHAR2) RETURN NUMBER;
371
372 /*===========================================================================+
373 | PROCEDURE strip_white_spaces |
374 | |
375 | DESCRIPTION |
376 | This proc stips out any non numberic characters like sapces,dashes etc |
377 | |
378 | SCOPE - PUBLIC |
379 | |
380 | |
381 | ARGUMENTS : IN: p_num_to_strip Number to be stripped |
382 | |
383 | RETURNS : OUT: p_stripped_num Stripped number |
384 | |
385 | MODIFICATION HISTORY |
386 | 27-Aug-2009 avepati Created |
387 | |
388 +===========================================================================*/
389
390 PROCEDURE strip_white_spaces( p_num_to_strip IN AP_BANK_ACCOUNTS.BANK_ACCOUNT_NUM%TYPE,
391 p_stripped_num OUT NOCOPY AP_BANK_ACCOUNTS.BANK_ACCOUNT_NUM%TYPE );
392
393 FUNCTION get_group_header (p_customer_id IN NUMBER,p_party_id IN NUMBER , p_trx_number IN VARCHAR) RETURN NUMBER;
394
395
396 FUNCTION invoke_invoice_email_notwf ( p_subscription_guid In RAW, p_event IN OUT NOCOPY WF_EVENT_T) RETURN VARCHAR2;
397
398 PROCEDURE det_if_send_email ( l_itemtype in varchar2,
399 l_itemkey in varchar2,
400 actid in number,
401 funcmode in varchar2,
402 rslt out NOCOPY varchar2);
403
407 Type email_addr_type IS TABLE OF hz_contact_points.email_Address%TYPE INDEX BY BINARY_INTEGER;
404 FUNCTION get_contact_emails_adhoc_list( p_customer_id IN VARCHAR2 ,
405 p_customer_acct_site_id IN VARCHAR2 ) RETURN VARCHAR2;
406
408
409 FUNCTION remove_existing_user_role( p_email_address IN VARCHAR2 ) RETURN VARCHAR2;
410
411 FUNCTION remove_duplicate_user_names ( l_user_email_list IN email_addr_type ) RETURN VARCHAR2;
412
413 TYPE CONTEXT_REC_TYPE IS RECORD
414 (
415 CONTEXT_TYPE VARCHAR2(100),
416 CONTEXT_ID NUMBER
417 );
418 TYPE CONTEXTS_TBL_TYPE IS TABLE OF CONTEXT_REC_TYPE INDEX BY BINARY_INTEGER;
419
420 PROCEDURE cancel_dispute(p_dispute_id IN NUMBER,
421 p_cancel_comments IN VARCHAR2,
422 p_return_status OUT NOCOPY VARCHAR2
423 );
424 FUNCTION get_link_filter(p_txn_no IN VARCHAR2,
425 p_txn_type IN VARCHAR2,
426 p_cust_trx_id IN NUMBER,
427 p_payment_schedule_id IN NUMBER) RETURN VARCHAR2;
428
429 /*========================================================================
430 | PUBLIC function get_pending_cmreq_amount
431 |
432 | DESCRIPTION
433 | Function returns the pending credit memo request amount for the customer id passed.
434 | If customer is not passed, then it will return the pending credit memo request amount
435 | for all the customer in current session.
436 |
437 | PARAMETERS
438 | p_customer_id IN NUMBER,
439 | p_customer_site_use_id IN NUMBER,
440 | p_session_id IN NUMBER,
441 | p_currency_code IN VARCHAR2
442 |
443 | RETURNS
444 | Pending Credit Memo Request amount
445 |
446 | MODIFICATION HISTORY
447 | Date Author Description of Changes
448 | 16-Nov-2011 melapaku Created
449 | 10-May-2012 parln Bug 13484644 - FP:13455876:13117430: ACCOUNT SUMMARY PERF ISSUES
450 *=======================================================================*/
451
452 FUNCTION get_pending_cmreq_amount(p_customer_id IN NUMBER,
453 p_customer_site_use_id IN NUMBER,
454 p_session_id IN NUMBER,
455 p_currency_code IN VARCHAR2) RETURN NUMBER;
456 END ari_utilities ;