1 PACKAGE IEX_UTILITIES AS
2 /* $Header: iexvutls.pls 120.15.12010000.2 2008/08/29 13:56:33 gnramasa 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
192
193 TYPE t_lookups_table IS TABLE OF VARCHAR2(80)
194 INDEX BY BINARY_INTEGER;
195
196 pg_lookups_rec t_lookups_table;
197
198 FUNCTION get_lookup_meaning (p_lookup_type IN VARCHAR2,
199 p_lookup_code IN VARCHAR2)
200 RETURN VARCHAR2;
201 -- End- Andre 09/15/2004 - Function to get lookup meaning - performance enhancement as per Ramakant Alat
202
203 -- Begin- jypark 09/22/2004 - Function to get parameter value
204
205 TYPE t_param_tab_type IS TABLE OF VARCHAR2(32000)
206 INDEX BY BINARY_INTEGER;
207
208 pg_param_tab t_param_tab_type;
209
210
211 -- Begin- Andre 11/22/2005 - bug4740016 - Cache for SQL populated data.
212 pg_iexcache_rec t_lookups_table;
213 FUNCTION get_cache_value (p_Identifier IN VARCHAR2,
214 p_PopulateSql IN VARCHAR2)
215 RETURN VARCHAR2;
216 -- End- Andre 11/22/2005 - bug4740016 - Cache for SQL populated data.
217
218
219 PROCEDURE put_param_value (p_param_value IN VARCHAR2,
220 p_param_key OUT NOCOPY NUMBER);
221
222 PROCEDURE get_param_value(p_param_key IN NUMBER,
223 p_param_value OUT NOCOPY VARCHAR2);
224
225 PROCEDURE delete_param_value(p_param_key IN NUMBER);
226
227 -- End- jypark 09/22/2004 - Function to get parameter value
228
229 /*
230 Overview : check if the dunning letter flag before send out a dunning letter
231 It checks the billto level first then account level; at last customer level.
232 Parameter: p_party_id: if customer level then pass the party_id
233 p_cust_account_id : if account level then pass the cust_account_id
234 p_site_use_id : if bill_to level then pass the customer_site_use_id
235 p_delinquency_id : if delinquency level then pass the delinquency_id
236 Return: 'Y' if ok to send dunning letter
237 'N' if no dunning letter should be sent
238 creation date: 06/02/2004
239 author: ctlee
240 */
241 FUNCTION DunningProfileCheck
242 (
243 p_party_id IN number
244 , p_cust_account_id IN number
245 , p_site_use_id IN number
246 , p_delinquency_id IN number
247 )
248 return varchar2 ;
249
250 /*
251 Overview: This function is to determine if the required min_dunning and min_invoice_dunning amount are
252 met before sending the dunning letter.
253 Parameter: p_cust_account_id : if account level then pass the cust_account_id
254 p_site_use_id : if bill_to level then pass the customer_site_use_id
255 Return: 'Y' if ok to send dunning letter
256 'N' if no dunning letter should be sent
257 creation date: 06/02/2004
258 author: ctlee
259 Note: it is not available in the customer level
260 */
261 FUNCTION DunningMinAmountCheck
262 (
263 p_cust_account_id IN number
264 , p_site_use_id IN number
265 )
266 return varchar2 ;
267
268
269 Procedure WriteLog ( p_msg IN VARCHAR2);
270
271 --Begin bug#4368394 schekuri 30-Nov-2005
272 --Added the following to provide a way to get the view by level of collections header
273 --in the database view itself
274 PROCEDURE SET_VIEW_BY_LEVEL(p_view_by in VARCHAR2);
275
276 FUNCTION GET_VIEW_BY_LEVEL RETURN VARCHAR2;
277
278 --End bug#4368394 schekuri 30-Nov-2005
279
280 --Begin bug#4773082 ctlee 1-Dec-2005 performance issue
281 FUNCTION get_amount_due_remaining (p_customer_trx_id IN number)
282 RETURN number;
283 --End bug#4773082 ctlee 1-Dec-2005
284
285
286 --Begin bug#4864641 ctlee 6-Dec-2005 performance issue
287 FUNCTION get_amount_due_original (p_customer_trx_id IN number)
288 RETURN number;
289 --End bug#4864641 ctlee 6-Dec-2005 performance issue
290
291 --Begin bug 6723556 gnramasa 11th Jan 08
292 FUNCTION CheckContractStatus
293 (
294 p_contract_number IN VARCHAR2
295 )
299 --Begin bug 6627832 gnramasa 21st Jan 08
296 return varchar2 ;
297 --End bug 6723556 gnramasa 11th Jan 08
298
300 FUNCTION ValidateXMLRequestId
301 (
302 p_xml_request_id IN number
303
304 )
305 return boolean;
306 --End bug 6627832 gnramasa 21st Jan 08
307
308 --Begin bug 6717279 by gnramasa 25th Aug 08
309 p_be_cust_acct_rec t_numbers;
310 Procedure copy_cust_acct_value
311 (
312 p_fe_cust_acct_rec IN DBMS_SQL.NUMBER_TABLE
313 );
314
315 FUNCTION cust_acct_id_check
316 (
317 p_cust_acct_id IN number
318 )
319 return varchar;
320 --End bug 6717279 by gnramasa 25th Aug 08
321
322 END;
[Home] [Help]