1 PACKAGE IEX_UTILITIES AUTHID CURRENT_USER AS
2 /* $Header: iexvutls.pls 120.24.12020000.6 2013/01/17 05:27:05 sunagesh ship $ */
3
4 TYPE resource_rec_type IS RECORD (
5 resource_id NUMBER,
6 person_id NUMBER,
7 user_id NUMBER,
8 person_name VARCHAR2(360),
9 user_name VARCHAR2(100));
10
11 TYPE resource_tab_type IS TABLE OF resource_Rec_type INDEX BY BINARY_INTEGER;
12
13 -- use for building dynamic where clauses
14 type Condition_REC is record (
15 COL_NAME VARCHAR2(25),
16 CONDITION VARCHAR2(5),
17 VALUE VARCHAR2(100));
18
19 type Condition_Tbl is table of Condition_REC
20 index by binary_integer;
21
22 -- Table Of Delinquency Ids.
23 TYPE t_del_id is TABLE of NUMBER
24 INDEX BY BINARY_INTEGER;
25
26 -- Table of varchar2(1)
27 TYPE t_varchar1 is TABLE of Varchar2(1)
28 INDEX BY BINARY_INTEGER;
29
30 -- Table of Asset_ids
31 TYPE t_asset_id is TABLE of Number
32 INDEX BY BINARY_INTEGER;
33
34 -- Table of del_children (Repo, Woff, Litg, Bank)
35 TYPE t_del_children is TABLE of Number
36 INDEX BY BINARY_INTEGER;
37
38 -- Table of del_asset_id
39 TYPE t_del_asset_id is TABLE of NUMBER
40 INDEX BY BINARY_INTEGER;
41
42 -- Table of Numbers
43 TYPE t_numbers is TABLE of NUMBER
44 INDEX BY BINARY_INTEGER;
45
46
47
48
49 PROCEDURE ACCT_BALANCE
50 (p_api_version IN NUMBER := 1.0,
51 p_init_msg_list IN VARCHAR2,
52 p_commit IN VARCHAR2,
53 p_validation_level IN NUMBER,
54 x_return_status OUT NOCOPY VARCHAR2,
55 x_msg_count OUT NOCOPY NUMBER,
56 x_msg_data OUT NOCOPY VARCHAR2,
57 p_cust_acct_id IN Number,
58 x_balance OUT NOCOPY Number);
59
60 PROCEDURE Validate_any_id(p_api_version IN NUMBER := 1.0,
61 p_init_msg_list IN VARCHAR2,
62 x_msg_count OUT NOCOPY NUMBER,
63 x_msg_data OUT NOCOPY VARCHAR2,
64 x_return_status OUT NOCOPY VARCHAR2,
65 p_col_id IN NUMBER,
66 p_col_name IN VARCHAR2,
67 p_table_name IN VARCHAR2);
68
69 -- added raverma 01162002
70 PROCEDURE Validate_any_varchar(p_api_version IN NUMBER := 1.0,
71 p_init_msg_list IN VARCHAR2,
72 x_msg_count OUT NOCOPY NUMBER,
73 x_msg_data OUT NOCOPY VARCHAR2,
74 x_return_status OUT NOCOPY VARCHAR2,
75 p_col_value IN VARCHAR2,
76 p_col_name IN VARCHAR2,
77 p_table_name IN VARCHAR2);
78
79 PROCEDURE Validate_Lookup_CODE(p_api_version IN NUMBER := 1.0,
80 p_init_msg_list IN VARCHAR2,
81 x_msg_count OUT NOCOPY NUMBER,
82 x_msg_data OUT NOCOPY VARCHAR2,
83 x_return_status OUT NOCOPY VARCHAR2,
84 p_lookup_type IN VARCHAR2,
85 p_lookup_code IN VARCHAR2,
86 p_lookup_view IN VARCHAR2);
87 --Begin bug#5373412 schekuri 10-Jul-2006
88 --Removed the following procedures and added a single consolidate procedure get_assigned_collector
89 /*
90 -- added by jypark 03052002
91 PROCEDURE get_access_resources(p_api_version IN NUMBER := 1.0,
92 p_init_msg_list IN VARCHAR2,
93 p_commit IN VARCHAR2,
94 p_validation_level IN NUMBER,
95 x_msg_count OUT NOCOPY NUMBER,
96 x_msg_data OUT NOCOPY VARCHAR2,
97 x_return_status OUT NOCOPY VARCHAR2,
98 p_party_id IN VARCHAR2,
99 x_resource_tab OUT NOCOPY resource_tab_type);
100 -- added by ehuh 02102003 based on kalis request
101 PROCEDURE get_assign_resources(p_api_version IN NUMBER := 1.0,
102 p_init_msg_list IN VARCHAR2,
103 p_commit IN VARCHAR2,
104 p_validation_level IN NUMBER,
105 x_msg_count OUT NOCOPY NUMBER,
106 x_msg_data OUT NOCOPY VARCHAR2,
107 x_return_status OUT NOCOPY VARCHAR2,
108 p_party_id IN VARCHAR2,
109 x_resource_tab OUT NOCOPY resource_tab_type);
110
111 PROCEDURE get_assign_account_resources(p_api_version IN NUMBER := 1.0,
112 p_init_msg_list IN VARCHAR2,
113 p_commit IN VARCHAR2,
114 p_validation_level IN NUMBER,
115 x_msg_count OUT NOCOPY NUMBER,
116 x_msg_data OUT NOCOPY VARCHAR2,
117 x_return_status OUT NOCOPY VARCHAR2,
118 p_account_id IN VARCHAR2,
119 x_resource_tab OUT NOCOPY resource_tab_type);
120
121 -- added by jsanju 12/22/2003 based on kalis request
122 PROCEDURE get_case_resources(p_api_version IN NUMBER := 1.0,
123 p_init_msg_list IN VARCHAR2,
124 p_commit IN VARCHAR2,
125 p_validation_level IN NUMBER,
126 x_msg_count OUT NOCOPY NUMBER,
127 x_msg_data OUT NOCOPY VARCHAR2,
128 x_return_status OUT NOCOPY VARCHAR2,
129 p_party_id IN VARCHAR2,
130 x_resource_tab OUT NOCOPY resource_tab_type);*/
131 --End bug#5373412 schekuri 10-Jul-2006
132
133 /*
134 || Overview: builds a dynamic where clause based on name / condition / value array
135 ||
136 || Parameter: array of name / condition / value like
137 || 'PARTY_NAME', '=', 'Anna Kournikova'
138 || 'AMOUNT_OVERDUE', '>=', '5000'
139 ||
140 || Return value: String with "Where party_name = 'Anna Kournikova' AND
141 || amount_overdue >= 5000"
142 ||
143 || Source Tables: NA
144 ||
145 || Target Tables: NA
146 ||
147 || Creation date: 01/28/2003 5:53PM
148 ||
149 || Major Modifications: when who what
150 || 01/28/2003 5:53PM raverma created
151 */
152 function buildWhereClause(P_CONDITIONS IN IEX_UTILITIES.Condition_Tbl) return VARCHAR2;
153
154 -- Added by acaraujo 07/27/2004 -
155 -- This procedure will return access to a bill to site use instead of party_id
156 -- this assumes use of the script to transfer collector from customer profiles
157 -- to as_accesses, this will place the site_use_id into the attribute1 column
158
159 --Begin bug#5373412 schekuri 10-Jul-2006
160 --Removed the following procedures and added a single consolidate procedure get_assigned_collector
161 /*PROCEDURE get_billto_resources(p_api_version IN NUMBER := 1.0,
162 p_init_msg_list IN VARCHAR2,
163 p_commit IN VARCHAR2,
164 p_validation_level IN NUMBER,
165 x_msg_count OUT NOCOPY NUMBER,
166 x_msg_data OUT NOCOPY VARCHAR2,
167 x_return_status OUT NOCOPY VARCHAR2,
168 p_site_use_id IN VARCHAR2,
169 x_resource_tab OUT NOCOPY resource_tab_type);*/
170
171 --Added the following procedure to consolidate the functionality of procedures
172 --get_billto_resources, get_assign_account_resources, get_assign_resources and get_access_resources
173 --into a single procedure.
174
175 PROCEDURE get_assigned_collector(p_api_version IN NUMBER := 1.0,
176 p_init_msg_list IN VARCHAR2,
177 p_commit IN VARCHAR2,
178 p_validation_level IN NUMBER,
179 p_level IN VARCHAR2,
180 p_level_id IN VARCHAR2,
181 x_msg_count OUT NOCOPY NUMBER,
182 x_msg_data OUT NOCOPY VARCHAR2,
183 x_return_status OUT NOCOPY VARCHAR2,
184 x_resource_tab OUT NOCOPY resource_tab_type);
185
186 --End bug#5373412 schekuri 10-Jul-2006
187
188
189 -- End- Andre 07/28/2004 - Add bill to assignmnet
190
191 PROCEDURE get_dunning_resource(p_api_version IN NUMBER := 1.0,
192 p_init_msg_list IN VARCHAR2,
193 p_commit IN VARCHAR2,
194 p_validation_level IN NUMBER,
195 p_level IN VARCHAR2,
196 p_level_id IN VARCHAR2,
197 x_msg_count OUT NOCOPY NUMBER,
198 x_msg_data OUT NOCOPY VARCHAR2,
199 x_return_status OUT NOCOPY VARCHAR2,
200 x_resource_tab OUT NOCOPY resource_tab_type);
201
202 PROCEDURE get_grace_days (p_api_version IN NUMBER := 1.0,
203 p_init_msg_list IN VARCHAR2,
204 p_commit IN VARCHAR2,
205 p_validation_level IN NUMBER,
206 p_level IN VARCHAR2,
207 p_party_id IN NUMBER,
208 p_account_id IN NUMBER,
209 p_site_use_id IN NUMBER,
210 x_msg_count OUT NOCOPY NUMBER,
211 x_msg_data OUT NOCOPY VARCHAR2,
212 x_return_status OUT NOCOPY VARCHAR2,
213 x_grace_days OUT NOCOPY NUMBER);
214
215 TYPE t_lookups_table IS TABLE OF VARCHAR2(80)
216 INDEX BY BINARY_INTEGER;
217
218 pg_lookups_rec t_lookups_table;
219
220 TYPE INC_INV_CURR_TBL IS TABLE OF VARCHAR2(80)
221 INDEX BY BINARY_INTEGER;
222
223 l_inc_inv_curr INC_INV_CURR_TBL;
224
225 FUNCTION get_lookup_meaning (p_lookup_type IN VARCHAR2,
226 p_lookup_code IN VARCHAR2)
227 RETURN VARCHAR2;
228 -- End- Andre 09/15/2004 - Function to get lookup meaning - performance enhancement as per Ramakant Alat
229
230 -- Begin- jypark 09/22/2004 - Function to get parameter value
231
232 TYPE t_param_tab_type IS TABLE OF VARCHAR2(32000)
233 INDEX BY BINARY_INTEGER;
234
235 pg_param_tab t_param_tab_type;
236
237
238 -- Begin- Andre 11/22/2005 - bug4740016 - Cache for SQL populated data.
239 pg_iexcache_rec t_lookups_table;
240 FUNCTION get_cache_value (p_Identifier IN VARCHAR2,
241 p_PopulateSql IN VARCHAR2)
242 RETURN VARCHAR2;
243 -- End- Andre 11/22/2005 - bug4740016 - Cache for SQL populated data.
244
245
246 PROCEDURE put_param_value (p_param_value IN VARCHAR2,
247 p_param_key OUT NOCOPY NUMBER);
248
249 PROCEDURE get_param_value(p_param_key IN NUMBER,
250 p_param_value OUT NOCOPY VARCHAR2);
251
252 PROCEDURE delete_param_value(p_param_key IN NUMBER);
253
254 -- End- jypark 09/22/2004 - Function to get parameter value
255
256 /*
257 Overview : check if the dunning letter flag before send out a dunning letter
258 It checks the billto level first then account level; at last customer level.
259 Parameter: p_party_id: if customer level then pass the party_id
260 p_cust_account_id : if account level then pass the cust_account_id
261 p_site_use_id : if bill_to level then pass the customer_site_use_id
262 p_delinquency_id : if delinquency level then pass the delinquency_id
263 Return: 'Y' if ok to send dunning letter
264 'N' if no dunning letter should be sent
265 creation date: 06/02/2004
266 author: ctlee
267 */
268 FUNCTION DunningProfileCheck
269 (
270 p_party_id IN number
271 , p_cust_account_id IN number
272 , p_site_use_id IN number
273 , p_delinquency_id IN number
274 )
275 return varchar2 ;
276
277 /*
278 Overview: This function is to determine if the required min_dunning and min_invoice_dunning amount are
279 met before sending the dunning letter.
280 Parameter: p_cust_account_id : if account level then pass the cust_account_id
281 p_site_use_id : if bill_to level then pass the customer_site_use_id
282 Return: 'Y' if ok to send dunning letter
283 'N' if no dunning letter should be sent
284 creation date: 06/02/2004
285 author: ctlee
286 Note: it is not available in the customer level
287 */
288 FUNCTION DunningMinAmountCheck
289 (
290 p_cust_account_id IN number
291 , p_site_use_id IN number
292 , p_delinquency_id IN number -- added for bug 15933013
293 , p_org_id in number
294 , p_grace_days IN number DEFAULT 0
295 , p_dun_disputed_items IN VARCHAR2 DEFAULT 'N'
296 , p_correspondence_date IN DATE DEFAULT sysdate
297 , p_running_level IN varchar2
298 ) return varchar2 ;
299
300 Procedure StagedDunningMinAmountCheck
301 (
302 p_cust_account_id IN number
303 , p_site_use_id IN number
304 , p_party_id IN number
305 , p_dunning_plan_id IN number
306 , p_grace_days IN number
307 , p_dun_disputed_items IN VARCHAR2
308 , p_correspondence_date IN DATE
309 , p_running_level IN VARCHAR2
310 ,p_org_id in number --Added for Bug 10401991 20-Jan-2011 barathsr
311 , p_inc_inv_curr OUT NOCOPY INC_INV_CURR_TBL
312 , p_dunning_letters OUT NOCOPY varchar2
313 );
314
315 Procedure MaxStageForanDelinquency ( p_delinquency_id IN number
316 , p_stage_number OUT NOCOPY number);
317
318 Procedure WriteLog ( p_msg IN VARCHAR2);
319
320 --Begin bug#4368394 schekuri 30-Nov-2005
321 --Added the following to provide a way to get the view by level of collections header
322 --in the database view itself
323 PROCEDURE SET_VIEW_BY_LEVEL(p_view_by in VARCHAR2);
324
325 FUNCTION GET_VIEW_BY_LEVEL RETURN VARCHAR2;
326
327 --End bug#4368394 schekuri 30-Nov-2005
328
329 --Begin bug#4773082 ctlee 1-Dec-2005 performance issue
330 FUNCTION get_amount_due_remaining (p_customer_trx_id IN number)
331 RETURN number;
332 --End bug#4773082 ctlee 1-Dec-2005
333
334
335 --Begin bug#4864641 ctlee 6-Dec-2005 performance issue
336 FUNCTION get_amount_due_original (p_customer_trx_id IN number)
337 RETURN number;
338 --End bug#4864641 ctlee 6-Dec-2005 performance issue
339
340 --Begin bug 6723556 gnramasa 11th Jan 08
341 FUNCTION CheckContractStatus
342 (
343 p_contract_number IN VARCHAR2
344 )
345 return varchar2 ;
346 --End bug 6723556 gnramasa 11th Jan 08
347
348 --Begin bug 6627832 gnramasa 21st Jan 08
349 FUNCTION ValidateXMLRequestId
350 (
351 p_xml_request_id IN number
352
353 )
354 return boolean;
355 --End bug 6627832 gnramasa 21st Jan 08
356
357 --Begin bug 6717279 by gnramasa 25th Aug 08
358 p_be_cust_acct_rec t_numbers;
359 Procedure copy_cust_acct_value
360 (
361 p_fe_cust_acct_rec IN DBMS_SQL.NUMBER_TABLE
362 );
363
364 FUNCTION cust_acct_id_check
365 (
366 p_cust_acct_id IN number
367 )
368 return varchar;
369 --End bug 6717279 by gnramasa 25th Aug 08
370 --Begin bug#6717849 by schekuri 27-Jul-2009
371 --Created for multi level strategy enhancement
372 FUNCTION VALIDATE_RUNNING_LEVEL
373 (
374 p_running_level IN varchar2
375 )
376 return varchar2;
377
378 FUNCTION GET_PARTY_RUNNING_LEVEL
379 (
380 p_party_id IN NUMBER,
381 p_org_id IN NUMBER DEFAULT NULL
382 )
383 return varchar2;
384 --End bug#6717849 by schekuri 27-Jul-2009
385
386 Function Get_Manager_role(p_user_id in number) return varchar2;
387
388 Function Delete_delinquncies(p_transaction_id number) return varchar2;
389
390 FUNCTION REPLACE_SPECIAL_CHARS(P_XML_DATA IN VARCHAR2) RETURN VARCHAR2; -- added for bug 13594457 by sunagesh on 23-jan-2012
391
392 Function get_BR_BillTOID (p_id in number)return number;
393 Function get_BR_DrweeSiteID (p_id in number)return number;
394
395 END;
[Home] [Help]