[Home] [Help]
PACKAGE BODY: APPS.IEX_UTILITIES
Source
1 PACKAGE BODY IEX_UTILITIES AS
2 /* $Header: iexvutlb.pls 120.27.12010000.4 2008/08/29 13:57:19 gnramasa ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'IEX_UTILITIES';
5 G_FILE_NAME CONSTANT VARCHAR2(12) := 'iexvutlb.pls';
6 G_APPL_ID NUMBER;
7 G_LOGIN_ID NUMBER;
8 G_PROGRAM_ID NUMBER;
9 G_USER_ID NUMBER;
10 G_REQUEST_ID NUMBER;
11
12 PG_DEBUG NUMBER(2);
13 --Added by schekuri for bug#4368394 on 30-NOV-2005
14 G_VIEW_BY_LEVEL VARCHAR2(20);
15
16 PROCEDURE ACCT_BALANCE
17 (p_api_version IN NUMBER := 1.0,
18 p_init_msg_list IN VARCHAR2,
19 p_commit IN VARCHAR2,
20 p_validation_level IN NUMBER,
21 x_return_status OUT NOCOPY VARCHAR2,
22 x_msg_count OUT NOCOPY NUMBER,
23 x_msg_data OUT NOCOPY VARCHAR2,
24 p_cust_acct_id IN Number,
25 x_balance OUT NOCOPY Number)
26 IS
27 l_api_version CONSTANT NUMBER := 1.0;
28 l_api_name CONSTANT VARCHAR2(30) := 'Acct_Balance';
29 l_return_status VARCHAR2(1);
30 l_msg_count NUMBER;
31 l_msg_data VARCHAR2(32767);
32
33 amount Number;
34 total Number;
35
36 BEGIN
37 SAVEPOINT Acct_Balance_Pvt;
38
39 amount := 0;
40 total := 0;
41 -- Standard call to check for call compatibility.
42 IF NOT FND_API.Compatible_API_Call (l_api_version,
43 p_api_version,
44 l_api_name,
45 G_PKG_NAME) THEN
46 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
47 END IF;
48
49 -- Check p_init_msg_list
50 IF FND_API.to_Boolean( p_init_msg_list ) THEN
51 FND_MSG_PUB.initialize;
52 END IF;
53
54 x_return_status := FND_API.G_RET_STS_SUCCESS;
55
56 SELECT nvl( SUM (acctd_amount_due_remaining), 0) AMOUNT
57 into Amount
58 FROM AR_PAYMENT_SCHEDULES
59 WHERE customer_id = p_cust_acct_id
60 AND customer_id+0 = p_cust_acct_id
61 AND class IN ('INV', 'GUAR', 'CB', 'DM', 'DEP')
62 AND status = 'OP' ;
63
64 total := amount ;
65
66 SELECT nvl(SUM (acctd_amount_due_remaining), 0) AMOUNT
67 into amount
68 FROM AR_PAYMENT_SCHEDULES
69 WHERE customer_id = p_cust_acct_id
70 AND customer_id+0 = p_cust_acct_id
71 AND class = 'PMT'
72 AND status = 'OP'
73 AND acctd_amount_due_remaining <> 0;
74
75 --total := total - amount ;
76 total := total + amount ;
77
78
79 SELECT nvl(SUM (acctd_amount_due_remaining), 0) AMOUNT
80 into amount
81 FROM AR_PAYMENT_SCHEDULES
82 WHERE customer_id = p_cust_acct_id
83 AND customer_id+0 = p_cust_acct_id
84 AND class = 'CM'
85 AND status = 'OP';
86
87 --total := total - amount ;
88 total := total + amount ;
89 x_balance := total ;
90
91 -- Standard check of p_commit
92 IF FND_API.To_Boolean(p_commit) THEN
93 COMMIT WORK;
94 END IF;
95
96 -- Standard call to get message count and if count is 1, get message info
97 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
98
99 EXCEPTION
100 WHEN FND_API.G_EXC_ERROR THEN
101 ROLLBACK TO Acct_Balance_PVT;
102 x_return_status := FND_API.G_RET_STS_ERROR;
103 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
104
105 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
106 ROLLBACK TO Acct_Balance_PVT;
107 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
108 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
109
110 WHEN OTHERS THEN
111 ROLLBACK TO Acct_Balance_PVT;
112 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
113 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
114 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
115 END IF;
116 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
117
118 END Acct_Balance;
119
120 PROCEDURE Validate_any_id(p_api_version IN NUMBER := 1.0,
121 p_init_msg_list IN VARCHAR2,
122 x_msg_count OUT NOCOPY NUMBER,
123 x_msg_data OUT NOCOPY VARCHAR2,
124 x_return_status OUT NOCOPY VARCHAR2,
125 p_col_id IN NUMBER,
126 p_col_name IN VARCHAR2,
127 p_table_name IN VARCHAR2)
128 IS
129
130 TYPE refCur IS REF CURSOR;
131 valid_id refCur;
132
133 l_return_status VARCHAR2(1);
134 count_id VARCHAR2(1);
135 l_api_version CONSTANT NUMBER := p_api_version;
136 l_init_msg_list CONSTANT VARCHAR2(1) := p_init_msg_list;
137 l_api_name CONSTANT VARCHAR2(20) := 'VALIDATE_ANY_ID';
138 vPlsql VARCHAR2(2000);
139
140 -- clchang updated for sql bind var 05/07/2003
141 vstr1 VARCHAR2(100);
142 vstr2 VARCHAR2(100);
143 vstr3 VARCHAR2(100);
144 vstr4 VARCHAR2(100);
145 vstr5 VARCHAR2(100);
146 vstr6 VARCHAR2(100);
147 vstr7 VARCHAR2(100);
148
149 BEGIN
150
151 -- Standard Start of API savepoint
152 SAVEPOINT Validate_any_id_PVT;
153
154 -- Initialize API return status to SUCCESS
155 x_return_status := FND_API.G_RET_STS_SUCCESS;
156
157 vstr1 := ' Select ''X'' ';
158 vstr2 := ' From ';
159 vstr3 := ' Where exists ';
160 vstr4 := ' (Select ' ;
161 vstr5 := ' From ';
162 vstr6 := ' Where ' ;
163 vstr7 := ' = :a1)';
164
165
166 -- Standard call to check for call compatibility.
167 IF NOT FND_API.Compatible_API_Call (l_api_version,
168 p_api_version,
169 l_api_name,
170 G_PKG_NAME)
171 THEN
172 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
173 END IF;
174
175 -- Initialize message list if p_init_msg_list is set to TRUE.
176 IF FND_API.to_Boolean(p_init_msg_list)
177 THEN
178 FND_MSG_PUB.initialize;
179 END IF;
180
181 -- API body
182 -- clchang updated for sql bind var 05/07/2003
183 vPlsql := vstr1 ||
184 vstr2 || p_table_name ||
185 vstr3 ||
186 vstr4 || p_col_name ||
187 vstr5 || p_table_name ||
188 vstr6 || p_col_name || vstr7;
189 WriteLog('iexvutlb:validateid:plsql='||vPlsql);
190 /*
191 vPlsql :=
192 ' Select ''X'' ' ||
193 ' From ' || p_table_name || ' ' ||
194 ' Where exists ' ||
195 ' (Select ' || p_col_name ||
196 ' From ' || p_table_name ||
197 ' Where ' || p_col_name || ' = :a1)';
198 --dbms_output.put_line('plsql is ' || vPLSQL);
199 */
200 -- end
201 open valid_id for
202 vPlsql
203 using p_col_id;
204 FETCH valid_id INTO count_id;
205
206 WriteLog('iexvutlb:validateid:count_id='||count_id);
207
208 if valid_id%FOUND then
209 --dbms_output.put_line('FOUND!!');
210 WriteLog('iexvutlb:validateid:Found!');
211 l_return_status := FND_API.G_RET_STS_SUCCESS;
212 else
213 --dbms_output.put_line('NOT FOUND!!');
214 WriteLog('iexvutlb:validateid:NotFound!');
215 l_return_status := FND_API.G_RET_STS_ERROR;
216 end if;
217 CLOSE valid_id;
218
219 x_return_status := l_return_status;
220
221 EXCEPTION
222 WHEN FND_API.G_EXC_ERROR THEN
223 ROLLBACK TO Validate_any_id_PVT;
224 x_return_status := FND_API.G_RET_STS_ERROR;
225 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
226
227 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
228 ROLLBACK TO Validate_any_id_PVT;
229 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
230 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
231
232 WHEN OTHERS THEN
233 ROLLBACK TO Validate_any_id_PVT;
234 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
235 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
236 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
237 END IF;
238 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
239
240 END Validate_any_id;
241
242 PROCEDURE Validate_any_varchar(p_api_version IN NUMBER := 1.0,
243 p_init_msg_list IN VARCHAR2,
244 x_msg_count OUT NOCOPY NUMBER,
245 x_msg_data OUT NOCOPY VARCHAR2,
246 x_return_status OUT NOCOPY VARCHAR2,
247 p_col_value IN VARCHAR2,
248 p_col_name IN VARCHAR2,
249 p_table_name IN VARCHAR2)
250 IS
251
252 TYPE refCur IS REF CURSOR;
253 valid_id refCur;
254
255 l_return_status VARCHAR2(1);
256 count_id VARCHAR2(1);
257 l_api_version CONSTANT NUMBER := p_api_version;
258 l_init_msg_list CONSTANT VARCHAR2(1) := p_init_msg_list;
259 l_api_name CONSTANT VARCHAR2(20) := 'VALIDATE_ANY_VARCHAR';
260
261 l_col_value varchar2(240);
262 vPLSQL varchar2(1000);
263
264 -- clchang updated for sql bind var 05/07/2003
265 vstr1 VARCHAR2(100);
266 vstr2 VARCHAR2(100);
267 vstr3 VARCHAR2(100);
268 vstr4 VARCHAR2(100);
269 vstr5 VARCHAR2(100);
270 vstr6 VARCHAR2(100);
271 vstr7 VARCHAR2(100);
272
273 BEGIN
274
275 -- Standard Start of API savepoint
276 SAVEPOINT Validate_any_varchar_PVT;
277
278 -- Initialize API return status to SUCCESS
279 x_return_status := FND_API.G_RET_STS_SUCCESS;
280
281
282 vstr1 := ' Select ''X'' ';
283 vstr2 := ' From ';
284 vstr3 := ' Where exists ';
285 vstr4 := ' (Select ' ;
286 vstr5 := ' From ';
287 vstr6 := ' Where ' ;
288 vstr7 := ' = :a1)';
289
290 -- Standard call to check for call compatibility.
291 IF NOT FND_API.Compatible_API_Call (l_api_version,
292 p_api_version,
293 l_api_name,
294 G_PKG_NAME)
295 THEN
296 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
297 END IF;
298
299 -- Initialize message list if p_init_msg_list is set to TRUE.
300 IF FND_API.to_Boolean(p_init_msg_list)
301 THEN
302 FND_MSG_PUB.initialize;
303 END IF;
304
305 -- API body
306 --dbms_output.put_line('col is ' || p_col_value);
307 -- clchang updated for sql bind var 05/07/2003
308 vPLSQL := vstr1 ||
309 vstr2 || p_table_name ||
310 vstr3 ||
311 vstr4 || p_col_name ||
312 vstr5 || p_table_name ||
313 vstr6 || p_col_name || vstr7;
314 /*
315 vPLSQL := ' Select ''X'' ' ||
316 ' From ' || p_table_name ||
317 ' Where exists ' ||
318 ' (Select ' || p_col_name ||
319 ' From ' || p_table_name ||
320 ' Where ' || p_col_name || ' = :a1)';
321 */
322 --dbms_output.put_line('plsql is ' || vPLSQL);
323
324 OPEN valid_id FOR
325 vPLSQL
326 using p_col_value;
327 FETCH valid_id INTO count_id;
328
329 if valid_id%FOUND then
330 --dbms_output.put_line('FOUND!!');
331 l_return_status := FND_API.G_RET_STS_SUCCESS;
332 else
333 --dbms_output.put_line('NOT FOUND!!');
334 l_return_status := FND_API.G_RET_STS_ERROR;
335 end if;
336 CLOSE valid_id;
337
338 x_return_status := l_return_status;
339
340 EXCEPTION
341 WHEN FND_API.G_EXC_ERROR THEN
342 ROLLBACK TO Validate_any_varchar_PVT;
343 x_return_status := FND_API.G_RET_STS_ERROR;
344 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
345
346 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
347 ROLLBACK TO Validate_any_varchar_PVT;
348 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
349 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
350
351 WHEN OTHERS THEN
352 ROLLBACK TO Validate_any_varchar_PVT;
353 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
354 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
355 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
356 END IF;
357 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
358
359 END Validate_any_varchar;
360
361 PROCEDURE Validate_Lookup_CODE(p_api_version IN NUMBER := 1.0,
362 p_init_msg_list IN VARCHAR2,
363 x_msg_count OUT NOCOPY NUMBER,
364 x_msg_data OUT NOCOPY VARCHAR2,
365 x_return_status OUT NOCOPY VARCHAR2,
366 p_lookup_type IN VARCHAR2,
367 p_lookup_code IN VARCHAR2,
368 p_lookup_view IN VARCHAR2)
369 IS
370
371 TYPE refCur IS REF CURSOR;
372 valid_id refCur;
373
374 l_return_status VARCHAR2(1);
375 count_id NUMBER := 0;
376 l_api_version CONSTANT NUMBER := p_api_version;
377 l_init_msg_list CONSTANT VARCHAR2(1) := p_init_msg_list;
378 l_api_name CONSTANT VARCHAR2(20) := 'VALIDATE_LOOKUP_CODE';
379
380 l_lookup_code varchar2(30);
381 l_lookup_type varchar2(30);
382 vPLSQL varchar2(1000);
383
384 -- clchang updated for sql bind var 05/07/2003
385 vstr1 varchar2(1000);
386 vstr2 varchar2(1000);
387 vstr3 varchar2(1000);
388 vstr4 varchar2(1000);
389 vstr5 varchar2(1000);
390
391 BEGIN
392
393 -- Standard Start of API savepoint
394 SAVEPOINT Validate_any_varchar_PVT;
395
396 -- Initialize API return status to SUCCESS
397 x_return_status := FND_API.G_RET_STS_SUCCESS;
398
399 vstr1 := 'Select Count(LOOKUP_CODE) ';
400 vstr2 := 'From ';
401 vstr3 := 'Where LOOKUP_TYPE = :l_lookup_type ' ;
402 vstr4 := ' AND LOOKUP_CODE = :l_lookup_code ';
403 vstr5 := ' AND ENABLED_FLAG = ''Y''';
404
405 -- Standard call to check for call compatibility.
406 IF NOT FND_API.Compatible_API_Call (l_api_version,
407 p_api_version,
408 l_api_name,
409 G_PKG_NAME)
410 THEN
411 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
412 END IF;
413
414 -- Initialize message list if p_init_msg_list is set to TRUE.
415 IF FND_API.to_Boolean(p_init_msg_list)
416 THEN
417 FND_MSG_PUB.initialize;
418 END IF;
419
420 -- API body
421 l_lookup_code := '''' || p_lookup_code || '''';
422 l_lookup_type := '''' || p_lookup_type || '''';
423
424 WriteLog('iexvutlb:validatelookup:lookup_code='||l_lookup_code);
425 WriteLog('iexvutlb:validatelookup:lookup_type='||l_lookup_type);
426
427 --dbms_output.put_line('col is ' || l_lookup_code);
428 -- clchang updated for sql bind var 05/07/2003
429 vPLSQL := vstr1 ||
430 vstr2 || p_lookup_view || ' ' ||
431 vstr3 ||
432 vstr4 ||
433 vstr5;
434 /*
435 vPLSQL :=
436 'Select Count(LOOKUP_CODE) ' ||
437 'From ' || p_lookup_view || ' ' ||
438 'Where LOOKUP_TYPE = ' || l_lookup_type || ' AND ' ||
439 'LOOKUP_CODE = ' || l_lookup_code || ' AND ' ||
440 'ENABLED_FLAG = ''Y''';
441 */
442 --
443
444 --dbms_output.put_line('plsql is ' || vPLSQL);
445 /*
446 OPEN valid_id FOR
447 vPLSQL;
448 */
449
450
451 select count(lookup_code)
452 into count_id
453 from iex_lookups_v
454 where lookup_type = p_lookup_type
455 and lookup_code = p_lookup_code
456 and enabled_flag = 'Y';
457
458 /*
459 open valid_id for
460 vPlsql
461 using l_lookup_type, l_lookup_code;
462
463 FETCH valid_id INTO count_id;
464
465 CLOSE valid_id ;
466 */
467
468 WriteLog('iexvutlb:validatelookup:count_id='||count_id);
469
470 IF (count_id > 0) then
471 l_return_status := FND_API.G_RET_STS_SUCCESS;
472 ELSE
473 l_return_status := FND_API.G_RET_STS_ERROR;
474 END IF ;
475 x_return_status := l_return_status;
476
477 EXCEPTION
478 WHEN FND_API.G_EXC_ERROR THEN
479 ROLLBACK TO Validate_any_varchar_PVT;
480 x_return_status := FND_API.G_RET_STS_ERROR;
481 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
482
483 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
484 ROLLBACK TO Validate_any_varchar_PVT;
485 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
486 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
487
488 WHEN OTHERS THEN
489 ROLLBACK TO Validate_any_varchar_PVT;
490 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
491 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
492 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
493 END IF;
494 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
495
496 END Validate_LOOKUP_CODE;
497
498 -- added by jypark 03052002
499 --Begin bug#5373412 schekuri 10-Jul-2006
500 --Removed the following procedures and added a single consolidate procedure get_assigned_collector
501 /*PROCEDURE get_access_resources(p_api_version IN NUMBER := 1.0,
502 p_init_msg_list IN VARCHAR2,
503 p_commit IN VARCHAR2,
504 p_validation_level IN NUMBER,
505 x_msg_count OUT NOCOPY NUMBER,
506 x_msg_data OUT NOCOPY VARCHAR2,
507 x_return_status OUT NOCOPY VARCHAR2,
508 p_party_id IN VARCHAR2,
509 x_resource_tab OUT NOCOPY resource_tab_type) IS
510 --Territory Assignment Changes
511 CURSOR c_get_person IS
512 SELECT DISTINCT ac.employee_id
513 FROM hz_customer_profiles hp,ar_collectors ac
514 WHERE hp.collector_id = ac.collector_id
515 AND ac.employee_id is not null
516 AND hp.party_id = p_party_id;
517
518 CURSOR c_get_resource(p_person_id NUMBER) IS
519 SELECT resource_id, source_id, user_id, source_name, user_name
520 FROM jtf_rs_resource_extns
521 WHERE source_id = p_person_id;
522 --AND start_date_active <= sysdate
523 --AND end_date_active > sysdate;
524
525 l_resource_row c_get_resource%rowtype;
526 l_api_version CONSTANT NUMBER := p_api_version;
527 l_api_name CONSTANT VARCHAR2(100) := 'GET_ACCESS_RESOURCES';
528 l_init_msg_list CONSTANT VARCHAR2(1) := p_init_msg_list;
529 l_return_status VARCHAR2(1);
530 l_msg_count NUMBER;
531 l_msg_data VARCHAR2(32767);
532 idx NUMBER := 0;
533 BEGIN
534
535 IF PG_DEBUG < 10 THEN
536 iex_debug_pub.logmessage ('**** BEGIN get_access_resources ************');
537 iex_debug_pub.logmessage ('get_person cursor = ' ||
538 'SELECT DISTINCT hp.employee_id
539 FROM hz_customer_profiles hp,ar_collectors ac
540 WHERE m.person_id = ac.employee_id
541 AND hp.collector_id = ac.collector_id
542 and ac.employee_id is not null
543 AND hp.party_id = p_party_id ' ||
544 'SELECT resource_id, source_id, user_id, source_name, user_name ' ||
545 'FROM jtf_rs_resource_extns ' ||
546 'WHERE source_id = p_person_id');
547 END IF;
548 SAVEPOINT Access_Resources_Pvt;
549
550 -- Standard call to check for call compatibility.
551 IF NOT FND_API.Compatible_API_Call (l_api_version,
552 p_api_version,
553 l_api_name,
554 G_PKG_NAME) THEN
555 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
556 END IF;
557
558 -- Check p_init_msg_list
559 IF FND_API.to_Boolean( p_init_msg_list ) THEN
560 FND_MSG_PUB.initialize;
561 END IF;
562
563 x_return_status := FND_API.G_RET_STS_SUCCESS;
564
565 FOR r_person IN c_get_person LOOP
566 IF PG_DEBUG < 10 THEN
567 iex_debug_pub.logmessage ('employee_id = ' || r_person.employee_id);
568 END IF;
569 OPEN c_get_resource(r_person.employee_id);
570 FETCH c_get_resource INTO l_resource_row;
571 IF c_get_resource%FOUND THEN
572 idx := idx + 1;
573 IF PG_DEBUG < 10 THEN
574 iex_debug_pub.logmessage ('idx= ' || idx);
575 iex_debug_pub.logmessage ('l_resource_row.resource_id = ' || l_resource_row.resource_id);
576 iex_debug_pub.logmessage ('l_resource_row.user_name = ' || l_resource_row.user_name);
577 iex_debug_pub.logmessage ('l_resource_row.source_name = ' || l_resource_row.source_name);
578 END IF;
579 x_resource_tab(idx).resource_id := l_resource_row.resource_id;
580 x_resource_tab(idx).user_id := l_resource_row.user_id;
581 x_resource_tab(idx).person_id := l_resource_row.source_id;
582 x_resource_tab(idx).user_name := l_resource_row.user_name;
583 x_resource_tab(idx).person_name := l_resource_row.source_name;
584 END IF;
585 CLOSE c_get_resource;
586 End LOOP;
587
588 -- Standard check of p_commit
589 IF FND_API.To_Boolean(p_commit) THEN
590 COMMIT WORK;
591 END IF;
592
593 -- Standard call to get message count and if count is 1, get message info
594 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
595
596 IF PG_DEBUG < 10 THEN
597 iex_debug_pub.logmessage ('**** END get_access_resources ************');
598 END IF;
599
600 EXCEPTION
601 WHEN FND_API.G_EXC_ERROR THEN
602 ROLLBACK TO Access_Resources_Pvt;
603 x_return_status := FND_API.G_RET_STS_ERROR;
604 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
605
606 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
607 ROLLBACK TO Access_Resources_Pvt;
608 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
609 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
610
611 WHEN OTHERS THEN
612 ROLLBACK TO Access_Resources_Pvt;
613 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
614 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
615 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
616 END IF;
617 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
618
619 END get_access_resources;
620
621 PROCEDURE get_assign_resources(p_api_version IN NUMBER := 1.0,
622 p_init_msg_list IN VARCHAR2,
623 p_commit IN VARCHAR2,
624 p_validation_level IN NUMBER,
625 x_msg_count OUT NOCOPY NUMBER,
626 x_msg_data OUT NOCOPY VARCHAR2,
627 x_return_status OUT NOCOPY VARCHAR2,
628 p_party_id IN VARCHAR2,
629 x_resource_tab OUT NOCOPY resource_tab_type) IS
630 /* cursor rewritten to avoid full table scan iex_strategy_work_items
631 CURSOR c_get_person IS
632 SELECT acc.person_id, acc.salesforce_id, count(work_item_id)
633 FROM as_accesses acc, jtf_rs_resource_extns rs, iex_strategy_work_items wi
634 WHERE acc.customer_id = p_party_id and rs.resource_id = acc.salesforce_id
635 and acc.salesforce_id = wi.resource_id(+)
636 and wi.status_code(+) = 'OPEN'
637 and acc.sales_lead_id is null and acc.lead_id is null
638 and rs.user_id is not null
639 group by acc.salesforce_id, acc.person_id ORDER BY 3;
640 */
641 /*
642 --Territory Assignment Changes
643 CURSOR c_get_person IS
644 (
645 SELECT ac.employee_id, ac.resource_id, count(work_item_id)
646 FROM hz_customer_profiles hp, jtf_rs_resource_extns rs, iex_strategy_work_items wi,
647 ar_collectors ac
648 WHERE hp.party_id = p_party_id
649 and hp.cust_account_id = -1
650 and rs.resource_id = ac.resource_id
651 and hp.collector_id = ac.collector_id
652 and ac.resource_id = wi.resource_id
653 and wi.status_code = 'OPEN'
654 and rs.user_id is not null
655 and ac.employee_id is not null
656 group by ac.resource_id, ac.employee_id
657 union all
658 SELECT ac.employee_id, ac.resource_id, 0
659 FROM hz_customer_profiles hp, jtf_rs_resource_extns rs,ar_collectors ac
660 WHERE hp.party_id = p_party_id
661 and hp.cust_account_id = -1
662 and rs.resource_id = ac.resource_id
663 and hp.collector_id = ac.collector_id
664 and rs.user_id is not null and
665 not exists (select null from iex_strategy_work_items wi
666 where ac.resource_id = wi.resource_id
667 and wi.status_code = 'OPEN')
668 and ac.employee_id is not null
669 group by ac.resource_id, ac.employee_id
670 ) order by 3;
671 */
672
673 /* We use hz_customer_profiles and
674 Load balancing when the assigned with Group Resource
675 */
676 /*cursor c_get_person IS
677 SELECT ac.employee_id, ac.resource_id, 0
678 FROM hz_customer_profiles hp, ar_collectors ac
679 WHERE hp.party_id = p_party_id
680 and hp.cust_account_id = -1
681 and hp.collector_id = ac.collector_id
682 and ac.resource_type = 'RS_RESOURCE'
683 -- Bug4483896. Fixed by lkkumar. Check for inactive_date. Start.
684 and trunc(nvl(ac.inactive_date,sysdate)) >= trunc(sysdate)
685 and nvl(ac.status,'A') = 'A'
686 -- Bug4483896. Fixed by lkkumar. Check for inactive_date. End.
687 union all
688 ( SELECT jtg.person_id, jtg.resource_id, count(work_item_id)
689 FROM hz_customer_profiles hp, iex_strategy_work_items wi,
690 ar_collectors ac, jtf_rs_group_members jtg
691 WHERE hp.party_id = P_PARTY_ID
692 and hp.cust_account_id = -1
693 and hp.collector_id = ac.collector_id
694 and ac.resource_type = 'RS_GROUP'
695 and ac.resource_id = jtg.group_id
696 and jtg.resource_id = wi.resource_id
697 and wi.status_code = 'OPEN'
698 -- Bug4483896. Fixed by lkkumar. Check for inactive_date. Start.
699 and trunc(nvl(ac.inactive_date,sysdate)) >= trunc(sysdate)
700 and nvl(ac.status,'A') = 'A'
701 group by jtg.resource_id, jtg.person_id
702 UNION ALL
703 SELECT jtg.person_id, jtg.resource_id, 0
704 FROM hz_customer_profiles hp, ar_collectors ac,
705 jtf_rs_group_members jtg
706 WHERE hp.party_id = p_party_id
707 and hp.cust_account_id = -1
708 and hp.collector_id = ac.collector_id
709 and ac.resource_type = 'RS_GROUP'
710 and ac.resource_id = jtg.group_id
711 and not exists (select null from iex_strategy_work_items wi
712 where jtg.resource_id = wi.resource_id
713 and wi.status_code = 'OPEN')
714 -- Bug4483896. Fixed by lkkumar. Check for inactive_date. Start.
715 and trunc(nvl(ac.inactive_date,sysdate)) >= trunc(sysdate)
716 and nvl(ac.status,'A') = 'A'
717 -- Bug4483896. Fixed by lkkumar. Check for inactive_date. End.
718 group by jtg.resource_id, jtg.person_id
719 ) order by 3;
720
721 /*
722 CURSOR c_get_person IS
723 SELECT DISTINCT person_id, salesforce_id
724 FROM as_accesses acc
725 WHERE acc.customer_id = p_party_id;
726 */
727
728 /* l_api_version CONSTANT NUMBER := p_api_version;
729 l_api_name CONSTANT VARCHAR2(100) := 'GET_ASSIGN_RESOURCES';
730 l_init_msg_list VARCHAR2(1);
731 l_return_status VARCHAR2(1);
732 l_msg_count NUMBER;
733 l_msg_data VARCHAR2(32767);
734 idx NUMBER := 0;
735 BEGIN
736
737 iex_debug_pub.logmessage ('**** BEGIN on all
738 get_access_resources ************');
739 l_init_msg_list := p_init_msg_list;
740 iex_debug_pub.logmessage ('get_person cursor = ' ||
741 'SELECT ac.employee_id, ac.resource_id, count(work_item_id) ' ||
742 ' FROM hz_customer_profiles hp, jtf_rs_resource_extns rs, iex_strategy_work_items wi, '||
743 ' ar_collectors ac ' ||
744 ' WHERE hp.party_id = p_party_id ' ||
745 ' and rs.resource_id = ac.resource_id' ||
746 ' and hp.collector_id = ac.collector_id' ||
747 ' and ac.resource_id = wi.resource_id' ||
748 ' and wi.status_code = OPEN ' ||
749 ' and rs.user_id is not null'||
750 ' group by ac.resource_id, ac.employee_id ');
751
752 SAVEPOINT get_assign_resources;
753
754 -- Standard call to check for call compatibility.
755 IF NOT FND_API.Compatible_API_Call (l_api_version,
756 p_api_version,
757 l_api_name,
758 G_PKG_NAME) THEN
759 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
760 END IF;
761
762 -- Check p_init_msg_list
763 IF FND_API.to_Boolean( p_init_msg_list ) THEN
764 FND_MSG_PUB.initialize;
765 END IF;
766
767 x_return_status := FND_API.G_RET_STS_SUCCESS;
768
769 FOR r_person IN c_get_person LOOP
770 idx := idx + 1;
771 iex_debug_pub.logmessage ('idx= ' || idx);
772 iex_debug_pub.logmessage ('r_person.salesforce_id = ' || r_person.resource_id);
773 x_resource_tab(idx).resource_id := r_person.resource_id;
774 x_resource_tab(idx).person_id := r_person.employee_id;
775 End LOOP;*/
776
777 --use bulk collect for performance as per kasreeni's request
778 --jsanju 04/13/2004
779 /*
780 OPEN c_get_person ;
781 FETCH c_get_person BULK COLLECT INTO x_resource_tab;
782 CLOSE c_get_person;
783 */
784
785 /*
786 -- Standard check of p_commit
787 IF FND_API.To_Boolean(p_commit) THEN
788 COMMIT WORK;
789 END IF;
790
791 -- Standard call to get message count and if count is 1, get message info
792 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
793
794 iex_debug_pub.logmessage ('**** END get_access_resources ************');
795
796 EXCEPTION
797 WHEN FND_API.G_EXC_ERROR THEN
798 ROLLBACK TO get_assign_resources;
799 x_return_status := FND_API.G_RET_STS_ERROR;
800 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
801
802 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
803 ROLLBACK TO get_assign_resources;
804 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
805 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
806
807 WHEN OTHERS THEN
808 ROLLBACK TO get_assign_resources;
809 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
810 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
811 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
812 END IF;
813 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
814
815 END get_assign_resources;
816
817 PROCEDURE get_assign_account_resources(p_api_version IN NUMBER := 1.0,
818 p_init_msg_list IN VARCHAR2,
819 p_commit IN VARCHAR2,
820 p_validation_level IN NUMBER,
821 x_msg_count OUT NOCOPY NUMBER,
822 x_msg_data OUT NOCOPY VARCHAR2,
823 x_return_status OUT NOCOPY VARCHAR2,
824 p_account_id IN VARCHAR2,
825 x_resource_tab OUT NOCOPY resource_tab_type) IS
826 --Territory Assignment Changes
827 /* CURSOR c_get_person IS
828 (
829 SELECT ac.employee_id, ac.resource_id, count(work_item_id)
830 FROM hz_customer_profiles hp, jtf_rs_resource_extns rs, iex_strategy_work_items wi,
831 ar_collectors ac
832 WHERE hp.cust_account_id = p_account_id
833 and hp.site_use_id is null
834 and rs.resource_id = ac.resource_id
835 and hp.collector_id = ac.collector_id
836 and ac.resource_id = wi.resource_id
837 and wi.status_code = 'OPEN'
838 and rs.user_id is not null
839 and ac.employee_id is not null
840 group by ac.resource_id, ac.employee_id
841 union all
842 SELECT ac.employee_id, ac.resource_id, 0
843 FROM hz_customer_profiles hp, jtf_rs_resource_extns rs,ar_collectors ac
844 WHERE hp.cust_account_id = p_account_id
845 and hp.site_use_id is null
846 and rs.resource_id = ac.resource_id
847 and hp.collector_id = ac.collector_id
848 and rs.user_id is not null and
849 not exists (select null from iex_strategy_work_items wi
850 where ac.resource_id = wi.resource_id
851 and wi.status_code = 'OPEN')
852 and ac.employee_id is not null
853 group by ac.resource_id, ac.employee_id
854 ) order by 3;
855 */
856
857 /* We use hz_customer_profiles and
858 Load balancing when the assigned with Group Resource
859 */
860 /*CURSOR c_get_person IS
861 SELECT ac.employee_id, ac.resource_id, 0
862 FROM hz_customer_profiles hp, ar_collectors ac
863 WHERE hp.cust_account_id = p_account_id
864 and hp.site_use_id is null
865 and hp.collector_id = ac.collector_id
866 and ac.resource_type = 'RS_RESOURCE'
867 union all
868 ( SELECT jtg.person_id, jtg.resource_id, count(work_item_id)
869 FROM hz_customer_profiles hp, iex_strategy_work_items wi,
870 ar_collectors ac, jtf_rs_group_members jtg
871 WHERE hp.cust_account_id = p_account_id
872 and hp.site_use_id is NULL
873 and hp.collector_id = ac.collector_id
874 and ac.resource_type = 'RS_GROUP'
875 and ac.resource_id = jtg.group_id
876 and jtg.resource_id = wi.resource_id
877 and wi.status_code = 'OPEN'
878 group by jtg.resource_id, jtg.person_id
879 UNION ALL
880 SELECT jtg.person_id, jtg.resource_id, 0
881 FROM hz_customer_profiles hp, ar_collectors ac,
882 jtf_rs_group_members jtg
883 WHERE hp.cust_account_id = p_account_id
884 and hp.site_use_id is null
885 and hp.collector_id = ac.collector_id
886 and ac.resource_type = 'RS_GROUP'
887 and ac.resource_id = jtg.group_id
888 and not exists (select null from iex_strategy_work_items wi
889 where jtg.resource_id = wi.resource_id
890 and wi.status_code = 'OPEN')
891 group by jtg.resource_id, jtg.person_id
892 ) order by 3;*/
893
894 /*
895 CURSOR c_get_person IS
896 SELECT DISTINCT person_id, salesforce_id
897 FROM as_accesses acc
898 WHERE acc.customer_id = p_party_id;
899 */
900
901 /* l_api_version CONSTANT NUMBER := p_api_version;
902 l_api_name CONSTANT VARCHAR2(100) := 'GET_ASSIGN_RESOURCES';
903 l_init_msg_list VARCHAR2(1);
904 l_return_status VARCHAR2(1);
905 l_msg_count NUMBER;
906 l_msg_data VARCHAR2(32767);
907 idx NUMBER := 0;
908 BEGIN
909
910 iex_debug_pub.logmessage ('**** BEGIN on all
911 get_access_resources ************');
912 l_init_msg_list := p_init_msg_list;
913 iex_debug_pub.logmessage ('get_person cursor = ' ||
914 'SELECT ac.employee_id, ac.resource_id, count(work_item_id) ' ||
915 ' FROM hz_customer_profiles hp, jtf_rs_resource_extns rs, iex_strategy_work_items wi, '||
916 ' ar_collectors ac ' ||
917 ' WHERE hp.party_id = p_party_id ' ||
918 ' and rs.resource_id = ac.resource_id' ||
919 ' and hp.collector_id = ac.collector_id' ||
920 ' and ac.resource_id = wi.resource_id' ||
921 ' and wi.status_code = OPEN ' ||
922 ' and rs.user_id is not null'||
923 ' group by ac.resource_id, ac.employee_id ');
924
925 SAVEPOINT get_assign_account_resources;
926
927 -- Standard call to check for call compatibility.
928 IF NOT FND_API.Compatible_API_Call (l_api_version,
929 p_api_version,
930 l_api_name,
931 G_PKG_NAME) THEN
932 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
933 END IF;
934
935 -- Check p_init_msg_list
936 IF FND_API.to_Boolean( p_init_msg_list ) THEN
937 FND_MSG_PUB.initialize;
938 END IF;
939
940 x_return_status := FND_API.G_RET_STS_SUCCESS;
941
942 FOR r_person IN c_get_person LOOP
943 idx := idx + 1;
944 iex_debug_pub.logmessage ('idx= ' || idx);
945 iex_debug_pub.logmessage ('r_person.salesforce_id = ' || r_person.resource_id);
946 x_resource_tab(idx).resource_id := r_person.resource_id;
947 x_resource_tab(idx).person_id := r_person.employee_id;
948 End LOOP;*/
949
950 --use bulk collect for performance as per kasreeni's request
951 --jsanju 04/13/2004
952 /*
953 OPEN c_get_person ;
954 FETCH c_get_person BULK COLLECT INTO x_resource_tab;
955 CLOSE c_get_person;
956 */
957
958
959 -- Standard check of p_commit
960 /* IF FND_API.To_Boolean(p_commit) THEN
961 COMMIT WORK;
962 END IF;
963
964 -- Standard call to get message count and if count is 1, get message info
965 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
966
967 iex_debug_pub.logmessage ('**** END get_access_resources ************');
968
969 EXCEPTION
970 WHEN FND_API.G_EXC_ERROR THEN
971 ROLLBACK TO get_assign_account_resources;
972 x_return_status := FND_API.G_RET_STS_ERROR;
973 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
974
975 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
976 ROLLBACK TO get_assign_account_resources;
977 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
978 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
979
980 WHEN OTHERS THEN
981 ROLLBACK TO get_assign_account_resources;
982 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
983 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
984 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
985 END IF;
986 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
987
988 END get_assign_account_resources;
989
990 PROCEDURE get_case_resources(p_api_version IN NUMBER := 1.0,
991 p_init_msg_list IN VARCHAR2,
992 p_commit IN VARCHAR2,
993 p_validation_level IN NUMBER,
994 x_msg_count OUT NOCOPY NUMBER,
995 x_msg_data OUT NOCOPY VARCHAR2,
996 x_return_status OUT NOCOPY VARCHAR2,
997 p_party_id IN VARCHAR2,
998 x_resource_tab OUT NOCOPY resource_tab_type) IS
999 --Territory Assignment Changes
1000 CURSOR c_get_person IS
1001 SELECT ac.employee_id, ac.resource_id, count(cas_id)
1002 FROM hz_customer_profiles hp, jtf_rs_resource_extns rs, iex_cases_vl wi,ar_collectors ac
1003 WHERE hp.party_id = p_party_id
1004 and rs.resource_id = ac.resource_id
1005 and hp.collector_id = ac.collector_id
1006 and ac.resource_id = wi.owner_resource_id(+)
1007 and rs.user_id is not null
1008 and ac.employee_id is not null
1009 group by ac.resource_id, ac.employee_id ORDER BY 3;*/
1010
1011 /*
1012 CURSOR c_get_person IS
1013 SELECT DISTINCT person_id, salesforce_id
1014 FROM as_accesses acc
1015 WHERE acc.customer_id = p_party_id;
1016 */
1017
1018 /* l_api_version CONSTANT NUMBER := p_api_version;
1019 l_api_name CONSTANT VARCHAR2(100) := 'GET_CASE_RESOURCES';
1020 l_init_msg_list CONSTANT VARCHAR2(1) := p_init_msg_list;
1021 l_return_status VARCHAR2(1);
1022 l_msg_count NUMBER;
1023 l_msg_data VARCHAR2(32767);
1024 idx NUMBER := 0;
1025 BEGIN
1026
1027 iex_debug_pub.logmessage ('**** BEGIN get_case_resources ************');
1028 iex_debug_pub.logmessage ('get_person cursor = ' ||
1029 'SELECT ac.employee_id, ac.resource_id, count(cas_id)
1030 FROM hz_customer_profiles hp, jtf_rs_resource_extns rs, iex_cases_vl wi,ar_collectors ac
1031 WHERE hp.party_id = p_party_id
1032 and rs.resource_id = ac.resource_id
1033 and hp.collector_id = ac.collector_id
1034 and ac.resource_id = wi.owner_resource_id(+)
1035 and rs.user_id is not null
1036 group by ac.resource_id, ac.employee_id ORDER BY 3');
1037
1038 SAVEPOINT get_case_resources;
1039
1040 -- Standard call to check for call compatibility.
1041 IF NOT FND_API.Compatible_API_Call (l_api_version,
1042 p_api_version,
1043 l_api_name,
1044 G_PKG_NAME) THEN
1045 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1046 END IF;
1047
1048 -- Check p_init_msg_list
1049 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1050 FND_MSG_PUB.initialize;
1051 END IF;
1052
1053 x_return_status := FND_API.G_RET_STS_SUCCESS;
1054
1055 FOR r_person IN c_get_person LOOP
1056 idx := idx + 1;
1057 iex_debug_pub.logmessage ('idx= ' || idx);
1058 iex_debug_pub.logmessage ('r_person.salesforce_id = ' || r_person.resource_id);
1059 x_resource_tab(idx).resource_id := r_person.resource_id;
1060 x_resource_tab(idx).person_id := r_person.employee_id;
1061 End LOOP;
1062
1063 -- Standard check of p_commit
1064 IF FND_API.To_Boolean(p_commit) THEN
1065 COMMIT WORK;
1066 END IF;
1067
1068 -- Standard call to get message count and if count is 1, get message info
1069 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1070
1071 iex_debug_pub.logmessage ('**** END get_case_resources ************');
1072
1073 EXCEPTION
1074 WHEN FND_API.G_EXC_ERROR THEN
1075 ROLLBACK TO get_case_resources;
1076 x_return_status := FND_API.G_RET_STS_ERROR;
1077 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1078
1079 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1080 ROLLBACK TO get_case_resources;
1081 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1082 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1083
1084 WHEN OTHERS THEN
1085 ROLLBACK TO get_case_resources;
1086 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1087 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1088 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
1089 END IF;
1090 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1091
1092 END get_case_resources;*/
1093 --End bug#5373412 schekuri 10-Jul-2006
1094
1095
1096
1097 /*
1098 || Overview: builds a dynamic where clause based on name / condition / value array
1099 ||
1100 || Parameter: array of name / condition / value like
1101 || 'PARTY_NAME', '=', 'Anna Kournikova'
1102 || 'AMOUNT_OVERDUE', '>=', '5000'
1103 ||
1104 || Return value: String with "Where party_name = 'Anna Kournikova' AND
1105 || amount_overdue >= 5000"
1106 ||
1107 || Source Tables: NA
1108 ||
1109 || Target Tables: NA
1110 ||
1111 || Creation date: 01/28/2003 5:53PM
1112 ||
1113 || Major Modifications: when who what
1114 || 01/28/2003 5:53PM raverma created
1115 */
1116 function buildWhereClause(P_CONDITIONS IN IEX_UTILITIES.Condition_TBL) return VARCHAR2
1117 IS
1118
1119 l_conditions IEX_UTILITIES.Condition_TBL;
1120 l_return VARCHAR2(5000);
1121 l_count NUMBER := 0;
1122
1123 --clchang updated for sql bind var 05/07/2003
1124 vstr varchar2(10);
1125
1126 Begin
1127 l_conditions := p_conditions;
1128 l_count := 0;
1129 vstr := 'WHERE ';
1130
1131 for J in 1..P_CONDITIONS.COUNT
1132 loop
1133
1134 if J <> P_CONDITIONS.COUNT then
1135 l_return := l_return || P_CONDITIONS(J).COL_NAME || ' ' || P_CONDITIONS(J).CONDITION || ' ' || P_CONDITIONS(J).VALUE || ' AND ';
1136 else
1137 l_return := l_return || P_CONDITIONS(J).COL_NAME || ' ' || P_CONDITIONS(J).CONDITION || ' ' || P_CONDITIONS(J).VALUE;
1138 end if;
1139 --dbms_output.put_line(l_return);
1140 end loop;
1141
1142 --clchang updated for sql bind var 05/07/2003
1143 -- return 'WHERE ' || l_return;
1144 return vstr ||l_return ;
1145
1146 End buildWhereClause;
1147
1148 -- Begin- Andre 07/28/2004 - Add bill to assignmnet
1149
1150 -- This procedure will return access to a bill to site use instead of site use id
1151 -- this assumes use of the script to transfer collector from customer profiles
1152 -- to as_accesses, this will place the site_use_id into the attribute1 column
1153 --Begin bug#5373412 schekuri 10-Jul-2006
1154 --Removed the following procedures and added a single consolidate procedure get_assigned_collector
1155 /*PROCEDURE get_billto_resources(p_api_version IN NUMBER := 1.0,
1156 p_init_msg_list IN VARCHAR2,
1157 p_commit IN VARCHAR2,
1158 p_validation_level IN NUMBER,
1159 x_msg_count OUT NOCOPY NUMBER,
1160 x_msg_data OUT NOCOPY VARCHAR2,
1161 x_return_status OUT NOCOPY VARCHAR2,
1162 p_site_use_id IN VARCHAR2,
1163 x_resource_tab OUT NOCOPY resource_tab_type) IS
1164
1165
1166 --Territory Assignment Changes
1167 /* CURSOR c_get_person IS
1168 (
1169 SELECT ac.employee_id, ac.resource_id
1170 FROM hz_customer_profiles hp, jtf_rs_resource_extns rs,
1171 ar_collectors ac
1172 WHERE hp.site_use_id = p_site_use_id
1173 and rs.resource_id = ac.resource_id
1174 and hp.collector_id = ac.collector_id
1175 and rs.user_id is not null
1176 and ac.employee_id is not null
1177 group by ac.resource_id, ac.employee_id
1178 union all
1179 SELECT ac.employee_id, ac.resource_id, 0
1180 FROM hz_customer_profiles hp, jtf_rs_resource_extns rs,ar_collectors ac
1181 WHERE hp.site_use_id = p_site_use_id
1182 and rs.resource_id = ac.resource_id
1183 and hp.collector_id = ac.collector_id
1184 and rs.user_id is not null and
1185 not exists (select null from iex_strategy_work_items wi
1186 where ac.resource_id = wi.resource_id
1187 and wi.status_code = 'OPEN')
1188 and ac.employee_id is not null
1189 group by ac.resource_id, ac.employee_id
1190 ) order by 3;
1191 */
1192 /* We use hz_customer_profiles and
1193 Load balancing when the assigned with Group Resource
1194 */
1195 /*CURSOR c_get_person is
1196 SELECT ac.employee_id, ac.resource_id, 0
1197 FROM hz_customer_profiles hp, ar_collectors ac
1198 WHERE hp.site_use_id = p_site_use_id
1199 and hp.collector_id = ac.collector_id
1200 and ac.resource_type = 'RS_RESOURCE'
1201 union all
1202 ( SELECT jtg.person_id, jtg.resource_id, count(work_item_id)
1203 FROM hz_customer_profiles hp, iex_strategy_work_items wi,
1204 ar_collectors ac, jtf_rs_group_members jtg
1205 WHERE hp.site_use_id = p_site_use_id
1206 and hp.collector_id = ac.collector_id
1207 and ac.resource_type = 'RS_GROUP'
1208 and ac.resource_id = jtg.group_id
1209 and jtg.resource_id = wi.resource_id
1210 and wi.status_code = 'OPEN'
1211 group by jtg.resource_id, jtg.person_id
1212 UNION ALL
1213 SELECT jtg.person_id, jtg.resource_id, 0
1214 FROM hz_customer_profiles hp, ar_collectors ac,
1215 jtf_rs_group_members jtg
1216 WHERE hp.site_use_id = p_site_use_id
1217 and hp.collector_id = ac.collector_id
1218 and ac.resource_type = 'RS_GROUP'
1219 and ac.resource_id = jtg.group_id
1220 and not exists (select null from iex_strategy_work_items wi
1221 where jtg.resource_id = wi.resource_id
1222 and wi.status_code = 'OPEN')
1223 group by jtg.resource_id, jtg.person_id
1224 ) order by 3;
1225
1226
1227 l_api_version CONSTANT NUMBER := p_api_version;
1228 l_api_name CONSTANT VARCHAR2(100) := 'GET_BILLTO_RESOURCES';
1229 l_init_msg_list CONSTANT VARCHAR2(1) := p_init_msg_list;
1230 l_return_status VARCHAR2(1);
1231 l_msg_count NUMBER;
1232 l_msg_data VARCHAR2(32767);
1233 idx NUMBER := 0;
1234 BEGIN
1235
1236 iex_debug_pub.logmessage ('**** BEGIN on all
1237 get_billto_resources ************');
1238 iex_debug_pub.logmessage ('get_person cursor = ' ||
1239 ' SELECT ac.employee_id, ac.resource_id, count(work_item_id)
1240 FROM hz_customer_profiles hp, jtf_rs_resource_extns rs, iex_strategy_work_items wi,ar_collectors ac
1241 WHERE hp.site_use_id = p_site_use_id
1242 and rs.resource_id = ac.resource_id
1243 and ac.resource_id = wi.resource_id
1244 and hp.collector_id = ac.collector_id
1245 and wi.status_code = OPEN
1246 and rs.user_id is not null
1247 group by ac.resource_id, ac.employee_id');
1248
1249
1250 SAVEPOINT get_assign_resources;
1251
1252 -- Standard call to check for call compatibility.
1253 IF NOT FND_API.Compatible_API_Call (l_api_version,
1254 p_api_version,
1255 l_api_name,
1256 G_PKG_NAME) THEN
1257 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1258 END IF;
1259
1260 -- Check p_init_msg_list
1261 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1262 FND_MSG_PUB.initialize;
1263 END IF;
1264
1265 x_return_status := FND_API.G_RET_STS_SUCCESS;
1266
1267 FOR r_person IN c_get_person LOOP
1268 idx := idx + 1;
1269 iex_debug_pub.logmessage ('idx= ' || idx);
1270 iex_debug_pub.logmessage ('r_person.salesforce_id = ' || r_person.resource_id);
1271 x_resource_tab(idx).resource_id := r_person.resource_id;
1272 x_resource_tab(idx).person_id := r_person.employee_id;
1273 End LOOP;
1274
1275 -- Standard check of p_commit
1276 IF FND_API.To_Boolean(p_commit) THEN
1277 COMMIT WORK;
1278 END IF;
1279
1280 -- Standard call to get message count and if count is 1, get message info
1281 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1282
1283 iex_debug_pub.logmessage ('**** END get_billto_resources ************');
1284
1285 EXCEPTION
1286 WHEN FND_API.G_EXC_ERROR THEN
1287 ROLLBACK TO get_assign_resources;
1288 x_return_status := FND_API.G_RET_STS_ERROR;
1289 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1290
1291 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1292 ROLLBACK TO get_assign_resources;
1293 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1294 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1295
1296 WHEN OTHERS THEN
1297 ROLLBACK TO get_assign_resources;
1298 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1299 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1300 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
1301 END IF;
1302 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1303
1304 END get_billto_resources;*/
1305 -- End- Andre 07/28/2004 - Add bill to assignmnet
1306 --End bug#5373412 schekuri 10-Jul-2006
1307
1308
1309 -- Begin- Andre 09/15/2004 - Function to get lookup meaning - performance enhancement as per Ramakant Alat
1310 FUNCTION get_lookup_meaning (p_lookup_type IN VARCHAR2,
1311 p_lookup_code IN VARCHAR2)
1312 RETURN VARCHAR2 IS
1313 l_meaning iex_lookups_v.meaning%TYPE;
1314 l_hash_value NUMBER;
1315 BEGIN
1316 IF p_lookup_code IS NOT NULL AND
1317 p_lookup_type IS NOT NULL THEN
1318
1319 l_hash_value := DBMS_UTILITY.get_hash_value(
1320 p_lookup_type||'@*?'||p_lookup_code,
1321 1000,
1322 25000);
1323
1324 IF pg_lookups_rec.EXISTS(l_hash_value) THEN
1325 l_meaning := pg_lookups_rec(l_hash_value);
1326 ELSE
1327
1328 SELECT meaning
1329 INTO l_meaning
1330 FROM iex_lookups_v
1331 WHERE lookup_type = p_lookup_type
1332 AND lookup_code = p_lookup_code ;
1333
1334 pg_lookups_rec(l_hash_value) := l_meaning;
1335
1336 END IF;
1337
1338 END IF;
1339
1340 return(l_meaning);
1341
1342 EXCEPTION
1343 WHEN no_data_found THEN
1344 return(null);
1345 WHEN OTHERS THEN
1346 raise;
1347 END;
1348 -- End- Andre 09/15/2004 - Function to get lookup meaning - performance enhancement as per Ramakant Alat
1349
1350 PROCEDURE put_param_value (p_param_value IN VARCHAR2,
1351 p_param_key OUT NOCOPY NUMBER) IS
1352 l_hash_value NUMBER;
1353 BEGIN
1354 IF p_param_value IS NOT NULL THEN
1355
1356 l_hash_value := DBMS_UTILITY.get_hash_value(
1357 p_param_value,
1358 1000,
1359 25000);
1360
1361 IF pg_param_tab.EXISTS(l_hash_value) THEN
1362 p_param_key := l_hash_value;
1363 ELSE
1364 pg_param_tab(l_hash_value) := p_param_value;
1365 p_param_key := l_hash_value;
1366
1367 END IF;
1368
1369 END IF;
1370 END;
1371
1372
1373 PROCEDURE get_param_value(p_param_key IN NUMBER,
1374 p_param_value OUT NOCOPY VARCHAR2) IS
1375 BEGIN
1376 IF pg_param_tab.EXISTS(p_param_key) THEN
1377 p_param_value := pg_param_tab(p_param_key);
1378 ELSE
1379 p_param_value := 'N/A';
1380 END IF;
1381 END;
1382
1383
1384 PROCEDURE delete_param_value(p_param_key IN NUMBER) IS
1385 BEGIN
1386 IF pg_param_tab.EXISTS(p_param_key) THEN
1387 pg_param_tab.DELETE(p_param_key);
1388 ELSE
1389 NULL;
1390 END IF;
1391 END;
1392
1393 /*
1394 Overview : Check if the dunning letter flag before send out a dunning letter.
1395 It checks the billto level first then account level; at last customer level.
1396 Parameter: p_party_id: if customer level then pass the party_id
1397 p_cust_account_id : if account level then pass the cust_account_id
1398 p_site_use_id : if bill_to level then pass the customer_site_use_id
1399 p_delinquency_id : if delinquency level then pass the delinquency_id
1400 Return: 'Y' if ok to send dunning letter
1401 'N' if no dunning letter should be sent
1402 creation date: 06/02/2004
1403 author: ctlee
1404 */
1405 FUNCTION DunningProfileCheck
1406 (
1407 p_party_id IN number
1408 , p_cust_account_id IN number
1409 , p_site_use_id IN number
1410 , p_delinquency_id IN number
1411 )
1412 return varchar2
1413 IS
1414 l_dunning_letters varchar2(10);
1415 l_party_id number;
1416 l_cust_account_id number;
1417 l_site_use_id number;
1418 l_delinquency_id number;
1419
1420 CURSOR get_party_account_id_cur (p_customer_site_use_id number) is
1421 SELECT ca.party_id party_id, ca.cust_account_id cust_account_id
1422 FROM hz_cust_site_uses site_uses, hz_cust_acct_sites acct_sites, hz_cust_accounts ca
1423 WHERE site_uses.site_use_id = p_customer_site_use_id
1424 AND acct_sites.cust_acct_site_id = site_uses.cust_acct_site_id
1425 AND ca.cust_account_id = acct_sites.cust_account_id;
1426
1427 cursor c_billto (p_site_use_id number) is
1428 select dunning_letters from hz_customer_profiles
1429 where site_use_id = p_site_use_id and status = 'A';
1430
1431 CURSOR get_party_id_cur (p_cust_account_id number) is
1432 SELECT party_id FROM HZ_CUST_ACCOUNTS
1433 WHERE cust_account_id = p_cust_account_id ;
1434
1435 cursor c_account (p_cust_account_id number) is
1436 select dunning_letters from hz_customer_profiles
1437 where cust_account_id = p_cust_account_id and status = 'A' and site_use_id is null;
1438
1439 cursor c_party (p_party_id number) is
1440 select dunning_letters from hz_customer_profiles
1441 -- begin bug 4587842 ctlee 09/06/2005
1442 where party_id = p_party_id and status = 'A'
1443 and site_use_id is null;
1444 -- where party_id = p_party_id and status = 'A' and site_use_id is null and cust_account_id is null;
1445 -- end bug 4587842 ctlee 09/06/2005
1446
1447 cursor c_get_billto (p_delinquency_id number) is
1448 select customer_site_use_id from iex_delinquencies_all
1449 where delinquency_id = p_delinquency_id;
1450 BEGIN
1451
1452 l_party_id := p_party_id;
1453 l_cust_account_id := p_cust_account_id;
1454 l_site_use_id := p_site_use_id;
1455 l_delinquency_id := p_delinquency_id;
1456
1457 -- default to no dunning letter to send, if there is no record that match
1458 l_dunning_letters := 'Y';
1459
1460 -- get bill_to id to check, because there is no delinquency level in customer_profile
1461 if (l_delinquency_id is not null) Then
1462 OPEN c_get_billto (l_delinquency_id);
1463 FETCH c_get_billto INTO l_site_use_id;
1464 CLOSE c_get_billto;
1465 end if;
1466
1467 if (l_site_use_id is not null) Then
1468 -- set up accout and party id in case there is no record for this bill to
1469 OPEN get_party_account_id_cur (l_site_use_id);
1470 FETCH get_party_account_id_cur INTO l_party_id, l_cust_account_id;
1471 CLOSE get_party_account_id_cur;
1472
1473 open c_billto(l_site_use_id);
1474 loop
1475 fetch c_billto into l_dunning_letters;
1476 if c_billto%notfound then
1477 exit;
1478 else
1479 -- Commented the if condition for Bug#6649352 bibeura 12-Dec-2007
1480 -- if l_dunning_letters = 'N' then
1481 return l_dunning_letters;
1482 -- end if;
1483 end if;
1484 end loop;
1485 close c_billto;
1486 end if;
1487
1488 if (l_cust_account_id is not null) Then
1489 -- set up party id in case there is no record for this account to
1490 OPEN get_party_id_cur (l_cust_account_id);
1491 FETCH get_party_id_cur INTO l_party_id;
1492 CLOSE get_party_id_cur;
1493
1494 open c_account(l_cust_account_id);
1495 loop
1496 fetch c_account into l_dunning_letters;
1497 if c_account%notfound then
1498 exit;
1499 else
1500 --Bug5348445. Fix By LKKUMAR on 21-Jun-2006. Start.
1501 -- if l_dunning_letters = 'N' then
1502 return l_dunning_letters;
1503 -- end if;
1504 --Bug5348445. Fix By LKKUMAR on 21-Jun-2006. End.
1505 end if;
1506 end loop;
1507 close c_account;
1508 end if;
1509
1510 if (l_party_id is not null) Then
1511 open c_party(l_party_id);
1512 loop
1513 fetch c_party into l_dunning_letters;
1514 if c_party%notfound then
1515 exit;
1516 else
1517 -- Commented the if condition for Bug#6649352 bibeura 12-Dec-2007
1518 -- if l_dunning_letters = 'N' then
1519 return l_dunning_letters;
1520 -- end if;
1521 end if;
1522 end loop;
1523 close c_party;
1524 end if;
1525
1526 return l_dunning_letters;
1527
1528 EXCEPTION
1529 WHEN OTHERS THEN
1530 return l_dunning_letters;
1531 END DunningProfileCheck;
1532
1533
1534
1535 /*
1536 Overview: This function is to determine if the required min_dunning and min_invoice_dunning amount are
1537 met before sending the dunning letter.
1538 Parameter: p_cust_account_id : if account level then pass the cust_account_id
1539 p_site_use_id : if bill_to level then pass the customer_site_use_id
1540 Return: 'Y' if ok to send dunning letter
1541 'N' if no dunning letter should be sent
1542 creation date: 06/02/2004
1543 author: ctlee
1544 Note: it is not available in the customer and delinquency level
1545 */
1546 FUNCTION DunningMinAmountCheck
1547 (
1548 p_cust_account_id IN number
1549 , p_site_use_id IN number
1550 )
1551 return varchar2
1552 IS
1553 l_dunning_letters varchar2(10);
1554 l_cust_account_id number;
1555 l_site_use_id number;
1556 l_min_dunning_amount number;
1557 l_min_dunning_invoice_amount number;
1558 l_min_currency_code varchar2(15);
1559
1560 l_rate_type varchar2(100);
1561 l_amount number;
1562 l_invoice_amount number;
1563 l_convert_amount number;
1564 l_amount_due_remaining number;
1565 l_invoice_currency_code varchar2(15);
1566 l_class varchar2(20);
1567
1568 -- there is no status to check if active or not,
1569 -- field expiration_date is null only - not used
1570 -- because there is more than 1 record for the same site_use_id, so we pick the functional currency one
1571 cursor c_billto (p_customer_site_use_id number) is
1572 select currency_code, nvl(min_dunning_amount,0), nvl(min_dunning_invoice_amount,0) from hz_cust_profile_amts
1573 where site_use_id = p_customer_site_use_id and currency_code = (
1574 SELECT sob.currency_code FROM ar_system_parameters sp, gl_sets_of_books sob
1575 WHERE sob.set_of_books_id = sp.set_of_books_id);
1576
1577 -- only open status
1578 cursor c_amount_billto (p_site_use_id number) is
1579 select nvl(a.amount_due_remaining, 0), a.invoice_currency_code, class
1580 from ar_payment_schedules_all a, iex_delinquencies_all b --Changed to ar_payment_schedules_all for bug#5652343 by gnramasa on 07-Mar-2007
1581 where a.payment_schedule_id = b.payment_schedule_id
1582 and b.status in ('DELINQUENT', 'PREDELINQUENT')
1583 AND a.class IN ('INV', 'GUAR', 'CB', 'DM', 'DEP')
1584 and a.status = 'OP'
1585 and b.customer_site_use_id = p_site_use_id;
1586
1587 cursor c_account (p_cust_account_id number) is
1588 select currency_code, nvl(min_dunning_amount,0), nvl(min_dunning_invoice_amount,0)
1589 from hz_cust_profile_amts
1590 where cust_account_id = p_cust_account_id and currency_code = (
1591 SELECT sob.currency_code FROM ar_system_parameters sp, gl_sets_of_books sob
1592 WHERE sob.set_of_books_id = sp.set_of_books_id);
1593
1594 -- only open status
1595 cursor c_amount_account (p_cust_account_id number) is
1596 select nvl(a.amount_due_remaining,0), a.invoice_currency_code, class
1597 from ar_payment_schedules_all a, iex_delinquencies_all b --Changed to ar_payment_schedules_all for bug#5652343 by gnramasa on 07-Mar-2007
1598 where a.payment_schedule_id = b.payment_schedule_id
1599 and b.status in ('DELINQUENT', 'PREDELINQUENT')
1600 AND a.class IN ('INV', 'GUAR', 'CB', 'DM', 'DEP')
1601 and a.status = 'OP'
1602 and b.cust_account_id = p_cust_account_id;
1603
1604 --Start bug 7026222 gnramasa 20th July 08
1605 l_check_dunn_profile varchar2(10);
1606 l_tot_amt_due_rem number;
1607
1608 cursor c_billto_dist_inv_cur (p_site_use_id number) is
1609 select distinct a.invoice_currency_code
1610 from ar_payment_schedules_all a, iex_delinquencies_all b
1611 where a.payment_schedule_id = b.payment_schedule_id
1612 and b.status in ('DELINQUENT', 'PREDELINQUENT')
1613 AND a.class IN ('INV', 'GUAR', 'CB', 'DM', 'DEP')
1614 and a.status = 'OP'
1615 and b.customer_site_use_id = p_site_use_id
1616 order by 1;
1617
1618 cursor c_billto_min_dunn_amt (p_site_use_id number, p_currency_code varchar) is
1619 select nvl(min_dunning_amount,0), nvl(min_dunning_invoice_amount,0)
1620 from hz_cust_profile_amts
1621 where site_use_id = p_site_use_id
1622 and currency_code = p_currency_code;
1623
1624 cursor c_billto_tot_amt_due_rem (p_site_use_id number, p_currency_code varchar, p_min_dun_inv_amt number) is
1625 select sum(nvl(a.amount_due_remaining,0))
1626 from ar_payment_schedules_all a, iex_delinquencies_all b
1627 where a.payment_schedule_id = b.payment_schedule_id
1628 and b.status in ('DELINQUENT', 'PREDELINQUENT')
1629 AND a.class IN ('INV', 'GUAR', 'CB', 'DM', 'DEP')
1630 and a.status = 'OP'
1631 and b.customer_site_use_id = p_site_use_id
1632 and a.invoice_currency_code = p_currency_code
1633 and a.amount_due_remaining >= p_min_dun_inv_amt;
1634
1635 cursor c_acc_dist_inv_cur (p_cust_account_id number) is
1636 select distinct a.invoice_currency_code
1637 from ar_payment_schedules_all a, iex_delinquencies_all b
1638 where a.payment_schedule_id = b.payment_schedule_id
1639 and b.status in ('DELINQUENT', 'PREDELINQUENT')
1640 AND a.class IN ('INV', 'GUAR', 'CB', 'DM', 'DEP')
1641 and a.status = 'OP'
1642 and b.cust_account_id = p_cust_account_id
1643 order by 1;
1644
1645 cursor c_acc_min_dunn_amt (p_cust_account_id number, p_currency_code varchar) is
1646 select nvl(min_dunning_amount,0), nvl(min_dunning_invoice_amount,0)
1647 from hz_cust_profile_amts
1648 where cust_account_id = p_cust_account_id
1649 and currency_code = p_currency_code
1650 and site_use_id is null;
1651
1652 cursor c_acc_tot_amt_due_rem (p_cust_account_id number, p_currency_code varchar, p_min_dun_inv_amt number) is
1653 select sum(nvl(a.amount_due_remaining,0))
1654 from ar_payment_schedules_all a, iex_delinquencies_all b
1655 where a.payment_schedule_id = b.payment_schedule_id
1656 and b.status in ('DELINQUENT', 'PREDELINQUENT')
1657 AND a.class IN ('INV', 'GUAR', 'CB', 'DM', 'DEP')
1658 and a.status = 'OP'
1659 and b.cust_account_id = p_cust_account_id
1660 and a.invoice_currency_code = p_currency_code
1661 and a.amount_due_remaining >= p_min_dun_inv_amt;
1662
1663 --End bug 7026222 gnramasa 20th July 08
1664 BEGIN
1665
1666 -- default to no dunning letter to send
1667 l_dunning_letters := 'Y';
1668 l_amount := 0 ;
1669 l_invoice_amount := 0 ;
1670 l_convert_amount := 0 ;
1671 l_amount_due_remaining := 0 ;
1672
1673 l_cust_account_id := p_cust_account_id;
1674 l_site_use_id := p_site_use_id;
1675 l_min_dunning_amount := 0;
1676 l_min_dunning_invoice_amount := 0;
1677
1678 l_rate_type := nvl(fnd_profile.value('IEX_COLLECTIONS_RATE_TYPE'), 'Corporate');
1679
1680 --Start bug 7026222 gnramasa 20th July 08
1681 l_check_dunn_profile := nvl(fnd_profile.value('IEX_CHK_DUNN_AT_FUNC_CURR'), 'Y');
1682 --If the profile 'IEX: Check Dunning amount at function currency' is set to 'Yes' then the current functionality will continue
1683 --and if the profile is set to 'No' then we can check amount at currency level. By default the profile value is 'Yes'
1684 if l_check_dunn_profile = 'Y' then
1685 -- determine the min dunning and min dunning invoice amount first
1686 if (l_site_use_id is not null) Then
1687
1688 open c_billto(l_site_use_id);
1689 fetch c_billto into l_min_currency_code, l_min_dunning_amount, l_min_dunning_invoice_amount;
1690 close c_billto;
1691
1692 elsif (l_cust_account_id is not null ) Then
1693 -- no need to set up party id because there is no party level record in hz_cust_profile_amts
1694 open c_account(l_cust_account_id);
1695 fetch c_account into l_min_currency_code, l_min_dunning_amount, l_min_dunning_invoice_amount;
1696 close c_account;
1697 end if;
1698
1699 -- no amout record to check
1700 if (l_min_currency_code is null) then
1701 return l_dunning_letters;
1702 end if;
1703
1704 l_amount := 0;
1705 l_invoice_amount := 0;
1706
1707 -- get the delinquency amount and convert it to the profile currency code
1708 -- do it line by line because each line may have different currency code
1709 if (l_site_use_id is not null) Then
1710 open c_amount_billto(l_site_use_id);
1711 loop
1712 fetch c_amount_billto into l_amount_due_remaining, l_invoice_currency_code, l_class;
1713 if c_amount_billto%notfound then
1714 exit;
1715 end if;
1716 if (l_invoice_currency_code <> l_min_currency_code) then
1717 l_convert_amount := gl_currency_api.convert_amount(
1718 x_from_currency => l_invoice_currency_code
1719 ,x_to_currency => l_min_currency_code
1720 ,x_conversion_date => sysdate
1721 ,x_conversion_type => l_rate_type
1722 ,x_amount => l_amount_due_remaining);
1723 l_amount := l_amount + l_convert_amount;
1724 -- also calculate the invoice amount
1725 if (l_class = 'INV') then
1726 l_invoice_amount := l_invoice_amount + l_convert_amount;
1727 end if;
1728 else
1729 l_amount := l_amount + l_amount_due_remaining;
1730 -- also calculate the invoice amount
1731 if (l_class = 'INV') then
1732 l_invoice_amount := l_invoice_amount + l_amount_due_remaining;
1733 end if;
1734 end if;
1735 end loop;
1736 close c_amount_billto;
1737 elsif (l_cust_account_id is not null ) Then -- account level calculation
1738 open c_amount_account(l_cust_account_id);
1739 loop
1740 fetch c_amount_account into l_amount_due_remaining, l_invoice_currency_code, l_class;
1741 if c_amount_account%notfound then
1742 exit;
1743 end if;
1744 if (l_invoice_currency_code <> l_min_currency_code) then
1745 l_convert_amount := gl_currency_api.convert_amount(
1746 x_from_currency => l_invoice_currency_code
1747 ,x_to_currency => l_min_currency_code
1748 ,x_conversion_date => sysdate
1749 ,x_conversion_type => l_rate_type
1750 ,x_amount => l_amount_due_remaining);
1751 l_amount := l_amount + l_convert_amount;
1752 -- also calculate the invoice amount
1753 if (l_class = 'INV') then
1754 l_invoice_amount := l_invoice_amount + l_convert_amount;
1755 end if;
1756 else
1757 l_amount := l_amount + l_amount_due_remaining;
1758 -- also calculate the invoice amount
1759 if (l_class = 'INV') then
1760 l_invoice_amount := l_invoice_amount + l_amount_due_remaining;
1761 end if;
1762 end if;
1763 end loop;
1764 close c_amount_account;
1765
1766 end if;
1767
1768 -- determine the flag
1769 if (l_amount < l_min_dunning_amount or l_invoice_amount < l_min_dunning_invoice_amount) then
1770 l_dunning_letters := 'N';
1771 end if;
1772 else
1773
1774 if (l_site_use_id is not null) Then
1775 l_dunning_letters := 'N'; --initially set it no
1776 open c_billto_dist_inv_cur(l_site_use_id);
1777 loop
1778 fetch c_billto_dist_inv_cur into l_invoice_currency_code;
1779 if c_billto_dist_inv_cur%notfound then
1780 exit;
1781 end if;
1782
1783 open c_billto_min_dunn_amt (l_site_use_id, l_invoice_currency_code);
1784 fetch c_billto_min_dunn_amt into l_min_dunning_amount, l_min_dunning_invoice_amount;
1785 close c_billto_min_dunn_amt;
1786
1787 if l_min_dunning_amount is null then
1788 l_min_dunning_amount := 0;
1789 end if;
1790
1791 if l_min_dunning_invoice_amount is null then
1792 l_min_dunning_invoice_amount := 0;
1793 end if;
1794
1795 open c_billto_tot_amt_due_rem (l_site_use_id, l_invoice_currency_code, l_min_dunning_invoice_amount);
1796 fetch c_billto_tot_amt_due_rem into l_tot_amt_due_rem;
1797 close c_billto_tot_amt_due_rem;
1798
1799 --If any currency group satisfies the condition then we have to send the dunning letter,
1800 --so no need to check for other currencies.
1801 if l_tot_amt_due_rem >= l_min_dunning_amount then
1802 l_dunning_letters := 'Y';
1803 exit;
1804 end if;
1805 end loop;
1806 close c_billto_dist_inv_cur;
1807 elsif (l_cust_account_id is not null ) Then -- account level calculation
1808 l_dunning_letters := 'N'; --initially set it no
1809 open c_acc_dist_inv_cur(l_cust_account_id);
1810 loop
1811 fetch c_acc_dist_inv_cur into l_invoice_currency_code;
1812 if c_acc_dist_inv_cur%notfound then
1813 exit;
1814 end if;
1815
1816 open c_acc_min_dunn_amt (l_cust_account_id, l_invoice_currency_code);
1817 fetch c_acc_min_dunn_amt into l_min_dunning_amount, l_min_dunning_invoice_amount;
1818 close c_acc_min_dunn_amt;
1819
1820 if l_min_dunning_amount is null then
1821 l_min_dunning_amount := 0;
1822 end if;
1823
1824 if l_min_dunning_invoice_amount is null then
1825 l_min_dunning_invoice_amount := 0;
1826 end if;
1827
1828 open c_acc_tot_amt_due_rem (l_cust_account_id, l_invoice_currency_code, l_min_dunning_invoice_amount);
1829 fetch c_acc_tot_amt_due_rem into l_tot_amt_due_rem;
1830 close c_acc_tot_amt_due_rem;
1831
1832 --If any currency group satisfies the condition then we have to send the dunning letter,
1833 --so no need to check for other currencies.
1834 if l_tot_amt_due_rem >= l_min_dunning_amount then
1835 l_dunning_letters := 'Y';
1836 exit;
1837 end if;
1838 end loop;
1839 close c_acc_dist_inv_cur;
1840 end if;
1841 end if; --if l_check_dunn_profile = 'Y' then
1842
1843 --End bug 7026222 gnramasa 20th July 08
1844
1845 return l_dunning_letters;
1846 EXCEPTION
1847 WHEN OTHERS THEN
1848 return l_dunning_letters;
1849 End DunningMinAmountCheck;
1850
1851
1852
1853 Procedure WriteLog ( p_msg IN VARCHAR2)
1854 IS
1855 BEGIN
1856 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1857 iex_debug_pub.LogMessage (p_msg);
1858 END IF;
1859
1860 END WriteLog;
1861
1862 -- Begin- Andre 11/22/2005 - bug4740016 - Cache for SQL populated data.
1863 FUNCTION get_cache_value (p_Identifier IN VARCHAR2,
1864 p_PopulateSql IN VARCHAR2)
1865 RETURN VARCHAR2 IS
1866 l_return varchar2(80);
1867 l_hash_value NUMBER;
1868 BEGIN
1869 IF p_Identifier IS NOT NULL THEN
1870
1871 l_hash_value := DBMS_UTILITY.get_hash_value(
1872 p_Identifier||'@*?',
1873 1000,
1874 25000);
1875
1876 IF pg_iexcache_rec.EXISTS(l_hash_value) THEN
1877 l_return := pg_iexcache_rec(l_hash_value);
1878 ELSE
1879 IF p_PopulateSql IS NOT NULL then
1880 -- simple select statement
1881 EXECUTE IMMEDIATE p_PopulateSql
1882 INTO l_return;
1883 pg_iexcache_rec(l_hash_value) := l_return;
1884 ELSE
1885 pg_iexcache_rec(l_hash_value) := Null;
1886 END IF;
1887
1888 END IF;
1889
1890 END IF;
1891
1892 return(l_return);
1893
1894 EXCEPTION
1895 WHEN no_data_found THEN
1896 return(null);
1897 WHEN OTHERS THEN
1898 raise;
1899 END;
1900 -- End- Andre 11/22/2005 - bug4740016 - Cache for SQL populated data.
1901
1902 --Begin bug#4368394 schekuri 30-Nov-2005
1903 --Added the following to provide a way to get the view by level of collections header
1904 --in the database view itself
1905 PROCEDURE SET_VIEW_BY_LEVEL(p_view_by in VARCHAR2) IS
1906 BEGIN
1907 G_VIEW_BY_LEVEL:=p_view_by;
1908 END SET_VIEW_BY_LEVEL;
1909
1910 FUNCTION GET_VIEW_BY_LEVEL RETURN VARCHAR2 IS
1911 BEGIN
1912 IF G_VIEW_BY_LEVEL IS NULL THEN
1913 RETURN 'PARTY';
1914 ELSE
1915 RETURN G_VIEW_BY_LEVEL;
1916 END IF;
1917 END GET_VIEW_BY_LEVEL;
1918 --End bug#4368394 schekuri 30-Nov-2005
1919
1920
1921 --Begin bug#4773082 ctlee 1-Dec-2005 performance issue
1922 FUNCTION get_amount_due_remaining (p_customer_trx_id IN number)
1923 return number is
1924 amount_due_remaining number;
1925 BEGIN
1926 select sum(amount_due_remaining) into amount_due_remaining from ar_payment_schedules_all
1927 where customer_trx_id = p_customer_trx_id;
1928 return amount_due_remaining;
1929 EXCEPTION
1930 WHEN OTHERS THEN
1931 return amount_due_remaining;
1932 END get_amount_due_remaining;
1933 --End bug#4773082 ctlee 1-Dec-2005
1934
1935 --Begin bug#4864641 ctlee 6-Dec-2005 performance issue
1936 FUNCTION get_amount_due_original (p_customer_trx_id IN number)
1937 return number is
1938 amount_due_original number;
1939 BEGIN
1940 select sum(amount_due_original) into amount_due_original from ar_payment_schedules_all
1941 where customer_trx_id = p_customer_trx_id;
1942 return amount_due_original;
1943 EXCEPTION
1944 WHEN OTHERS THEN
1945 return amount_due_original;
1946 END get_amount_due_original;
1947 --End bug#4864641 ctlee 6-Dec-2005 performance issue
1948
1949 --Begin bug#5373412 schekuri 10-Jul-2006
1950 --Added the following procedure to consolidate the functionality of procedures
1951 --get_billto_resources, get_assign_account_resources, get_assign_resources and get_access_resources
1952 --into a single procedure.
1953 -- This procedure will return the resource assigned to a customer, account, site, case
1954 PROCEDURE get_assigned_collector(p_api_version IN NUMBER := 1.0,
1955 p_init_msg_list IN VARCHAR2,
1956 p_commit IN VARCHAR2,
1957 p_validation_level IN NUMBER,
1958 p_level IN VARCHAR2,
1959 p_level_id IN VARCHAR2,
1960 x_msg_count OUT NOCOPY NUMBER,
1961 x_msg_data OUT NOCOPY VARCHAR2,
1962 x_return_status OUT NOCOPY VARCHAR2,
1963 x_resource_tab OUT NOCOPY resource_tab_type) IS
1964
1965 /* ------ SQLs assembled at run time -----------------------------------
1966 CURSOR c_billto_collector is
1967 SELECT ac.employee_id, ac.resource_id, 0
1968 FROM hz_customer_profiles hp, ar_collectors ac
1969 WHERE hp.site_use_id = p_site_use_id
1970 and hp.collector_id = ac.collector_id
1971 and ac.resource_type = 'RS_RESOURCE'
1972 and trunc(nvl(ac.inactive_date,sysdate)) >= trunc(sysdate)
1973 and nvl(ac.status,'A') = 'A'
1974 and nvl(hp.status,'A') = 'A'
1975 union all
1976 ( SELECT jtg.person_id, jtg.resource_id, count(work_item_id)
1977 FROM hz_customer_profiles hp, iex_strategy_work_items wi,
1978 ar_collectors ac, jtf_rs_group_members jtg
1979 WHERE hp.site_use_id = p_site_use_id
1980 and hp.collector_id = ac.collector_id
1981 and ac.resource_type = 'RS_GROUP'
1982 and ac.resource_id = jtg.group_id
1983 and jtg.resource_id = wi.resource_id
1984 and wi.status_code = 'OPEN'
1985 and trunc(nvl(ac.inactive_date,sysdate)) >= trunc(sysdate)
1986 and nvl(ac.status,'A') = 'A'
1987 and nvl(hp.status,'A') = 'A'
1988 and nvl(jtg.delete_flag,'N') = 'N'
1989 group by jtg.resource_id, jtg.person_id
1990 UNION ALL
1991 SELECT jtg.person_id, jtg.resource_id, 0
1992 FROM hz_customer_profiles hp, ar_collectors ac,
1993 jtf_rs_group_members jtg
1994 WHERE hp.site_use_id = p_site_use_id
1995 and hp.collector_id = ac.collector_id
1996 and ac.resource_type = 'RS_GROUP'
1997 and ac.resource_id = jtg.group_id
1998 and not exists (select null from iex_strategy_work_items wi
1999 where jtg.resource_id = wi.resource_id
2000 and wi.status_code = 'OPEN')
2001 and trunc(nvl(ac.inactive_date,sysdate)) >= trunc(sysdate)
2002 and nvl(ac.status,'A') = 'A'
2003 and nvl(hp.status,'A') = 'A'
2004 and nvl(jtg.delete_flag,'N') = 'N'
2005 group by jtg.resource_id, jtg.person_id
2006 ) order by 3;
2007
2008 CURSOR c_account_collector IS
2009 SELECT ac.employee_id, ac.resource_id, 0
2010 FROM hz_customer_profiles hp, ar_collectors ac
2011 WHERE hp.cust_account_id = p_account_id
2012 and hp.site_use_id is null
2013 and hp.collector_id = ac.collector_id
2014 and ac.resource_type = 'RS_RESOURCE'
2015 and trunc(nvl(ac.inactive_date,sysdate)) >= trunc(sysdate)
2016 and nvl(ac.status,'A') = 'A'
2017 and nvl(hp.status,'A') = 'A'
2018 union all
2019 ( SELECT jtg.person_id, jtg.resource_id, count(work_item_id)
2020 FROM hz_customer_profiles hp, iex_strategy_work_items wi,
2021 ar_collectors ac, jtf_rs_group_members jtg
2022 WHERE hp.cust_account_id = p_account_id
2023 and hp.site_use_id is NULL
2024 and hp.collector_id = ac.collector_id
2025 and ac.resource_type = 'RS_GROUP'
2026 and ac.resource_id = jtg.group_id
2027 and jtg.resource_id = wi.resource_id
2028 and wi.status_code = 'OPEN'
2029 and trunc(nvl(ac.inactive_date,sysdate)) >= trunc(sysdate)
2030 and nvl(ac.status,'A') = 'A'
2031 and nvl(hp.status,'A') = 'A'
2032 and nvl(jtg.delete_flag,'N') = 'N'
2033 group by jtg.resource_id, jtg.person_id
2034 UNION ALL
2035 SELECT jtg.person_id, jtg.resource_id, 0
2036 FROM hz_customer_profiles hp, ar_collectors ac,
2037 jtf_rs_group_members jtg
2038 WHERE hp.cust_account_id = p_account_id
2039 and hp.site_use_id is null
2040 and hp.collector_id = ac.collector_id
2041 and ac.resource_type = 'RS_GROUP'
2042 and ac.resource_id = jtg.group_id
2043 and not exists (select null from iex_strategy_work_items wi
2044 where jtg.resource_id = wi.resource_id
2045 and wi.status_code = 'OPEN')
2046 and trunc(nvl(ac.inactive_date,sysdate)) >= trunc(sysdate)
2047 and nvl(ac.status,'A') = 'A'
2048 and nvl(hp.status,'A') = 'A'
2049 and nvl(jtg.delete_flag,'N') = 'N'
2050 group by jtg.resource_id, jtg.person_id
2051 ) order by 3;
2052
2053 cursor c_party_collector IS
2054 SELECT ac.employee_id, ac.resource_id, 0
2055 FROM hz_customer_profiles hp, ar_collectors ac
2056 WHERE hp.party_id = p_party_id
2057 and hp.cust_account_id = -1
2058 and hp.collector_id = ac.collector_id
2059 and ac.resource_type = 'RS_RESOURCE'
2060 and trunc(nvl(ac.inactive_date,sysdate)) >= trunc(sysdate)
2061 and nvl(ac.status,'A') = 'A'
2062 and nvl(hp.status,'A') = 'A'
2063 union all
2064 ( SELECT jtg.person_id, jtg.resource_id, count(work_item_id)
2065 FROM hz_customer_profiles hp, iex_strategy_work_items wi,
2066 ar_collectors ac, jtf_rs_group_members jtg
2067 WHERE hp.party_id = P_PARTY_ID
2068 and hp.cust_account_id = -1
2069 and hp.collector_id = ac.collector_id
2070 and ac.resource_type = 'RS_GROUP'
2071 and ac.resource_id = jtg.group_id
2072 and jtg.resource_id = wi.resource_id
2073 and wi.status_code = 'OPEN'
2074 and trunc(nvl(ac.inactive_date,sysdate)) >= trunc(sysdate)
2075 and nvl(ac.status,'A') = 'A'
2076 and nvl(hp.status,'A') = 'A'
2077 and nvl(jtg.delete_flag,'N') = 'N'
2078 group by jtg.resource_id, jtg.person_id
2079 UNION ALL
2080 SELECT jtg.person_id, jtg.resource_id, 0
2081 FROM hz_customer_profiles hp, ar_collectors ac,
2082 jtf_rs_group_members jtg
2083 WHERE hp.party_id = p_party_id
2084 and hp.cust_account_id = -1
2085 and hp.collector_id = ac.collector_id
2086 and ac.resource_type = 'RS_GROUP'
2087 and ac.resource_id = jtg.group_id
2088 and not exists (select null from iex_strategy_work_items wi
2089 where jtg.resource_id = wi.resource_id
2090 and wi.status_code = 'OPEN')
2091 and trunc(nvl(ac.inactive_date,sysdate)) >= trunc(sysdate)
2092 and nvl(ac.status,'A') = 'A'
2093 and nvl(hp.status,'A') = 'A'
2094 and nvl(jtg.delete_flag,'N') = 'N'
2095 group by jtg.resource_id, jtg.person_id
2096 ) order by 3;
2097
2098 CURSOR c_case_collector IS
2099 SELECT ac.employee_id, ac.resource_id, count(cas_id)
2100 FROM hz_customer_profiles hp, jtf_rs_resource_extns rs, iex_cases_vl wi,ar_collectors ac
2101 WHERE hp.party_id = p_party_id
2102 and rs.resource_id = ac.resource_id
2103 and hp.collector_id = ac.collector_id
2104 and ac.resource_id = wi.owner_resource_id(+)
2105 and rs.user_id is not null
2106 and ac.employee_id is not null
2107 and trunc(nvl(ac.inactive_date,sysdate)) >= trunc(sysdate)
2108 and nvl(ac.status,'A') = 'A'
2109 and nvl(hp.status,'A') = 'A'
2110 group by ac.resource_id, ac.employee_id ORDER BY 3;
2111 */
2112
2113 l_api_version CONSTANT NUMBER := p_api_version;
2114 l_api_name CONSTANT VARCHAR2(100) := 'get_assigned_collector';
2115 l_init_msg_list CONSTANT VARCHAR2(1):= p_init_msg_list;
2116 l_return_status VARCHAR2(1);
2117 l_msg_count NUMBER;
2118 l_msg_data VARCHAR2(32767);
2119 idx NUMBER := 0;
2120 l_count NUMBER := 0;
2121
2122 l_select1 VARCHAR2(3000);
2123 l_select2 VARCHAR2(3000);
2124 l_select3 VARCHAR2(3000);
2125 l_select4 VARCHAR2(3000);
2126 --Begin Bug#5229763 schekuri 27-Jul-2006
2127 l_select5 VARCHAR2(3000);
2128 l_select6 VARCHAR2(3000);
2129 --End Bug#5229763 schekuri 27-Jul-2006
2130
2131 l_where1 VARCHAR2(3000);
2132 l_where2 VARCHAR2(3000);
2133 l_where3 VARCHAR2(3000);
2134 l_where4 VARCHAR2(3000);
2135 --Begin Bug#5229763 schekuri 27-Jul-2006
2136 l_where5 VARCHAR2(3000);
2137 l_where6 VARCHAR2(3000);
2138 --End Bug#5229763 schekuri 27-Jul-2006
2139
2140 l_group2 VARCHAR2(3000);
2141 l_group3 VARCHAR2(3000);
2142 l_group4 VARCHAR2(3000);
2143 --Begin Bug#5229763 schekuri 27-Jul-2006
2144 l_group5 VARCHAR2(3000);
2145 l_group6 VARCHAR2(3000);
2146 --End Bug#5229763 schekuri 27-Jul-2006
2147
2148 l_order VARCHAR2(3000) := ' ORDER BY 3';
2149 l_union VARCHAR2(3000) := ' UNION ALL ';
2150
2151 l_query VARCHAR2(32767);
2152
2153 TYPE c_cur_type IS REF CURSOR;
2154 c_collector c_cur_type;
2155
2156 BEGIN
2157
2158 iex_debug_pub.logmessage ('**** BEGIN get_assigned_collector ************');
2159
2160
2161 SAVEPOINT get_assigned_collector;
2162
2163 -- Standard call to check for call compatibility.
2164 IF NOT FND_API.Compatible_API_Call (l_api_version,
2165 p_api_version,
2166 l_api_name,
2167 G_PKG_NAME) THEN
2168 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2169 END IF;
2170
2171 -- Check p_init_msg_list
2172 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2173 FND_MSG_PUB.initialize;
2174 END IF;
2175
2176 x_return_status := FND_API.G_RET_STS_SUCCESS;
2177
2178 --Start Bug 7134688 gnramasa 17th June 08
2179
2180 -- Initialize SQL statements - REMEMBER CHANGING 1 MEANS CHANGING ALL
2181 l_select1 := 'SELECT ac.employee_id, ac.resource_id, 0 ';
2182 l_select1 := l_select1 || 'FROM hz_customer_profiles hp, ar_collectors ac,jtf_rs_resource_extns rs ';
2183 l_where1 := 'WHERE ';
2184 l_where1 := l_where1 || ' hp.collector_id = ac.collector_id ';
2185 l_where1 := l_where1 || ' and ac.resource_type = ''RS_RESOURCE'' ';
2186 l_where1 := l_where1 || ' and trunc(nvl(ac.inactive_date,sysdate)) >= trunc(sysdate) ';
2187 l_where1 := l_where1 || ' and nvl(ac.status,''A'') = ''A'' ';
2188 l_where1 := l_where1 || ' and nvl(hp.status,''A'') = ''A'' ';
2189 l_where1 := l_where1 || ' and ac.resource_id = rs.resource_id ';
2190 l_where1 := l_where1 || ' and trunc(nvl(rs.end_date_active,sysdate)) >= trunc(sysdate) ';
2191
2192 l_select2 := 'SELECT jtg.person_id, jtg.resource_id, count(work_item_id) ';
2193 l_select2 := l_select2 || 'FROM hz_customer_profiles hp, iex_strategy_work_items wi, ';
2194 l_select2 := l_select2 || ' ar_collectors ac, jtf_rs_group_members jtg, jtf_rs_resource_extns rs ';
2195 l_select2 := l_select2 || ' , jtf_rs_role_relations jtr,JTF_RS_ROLES_b jtrr ';
2196 l_where2 := 'WHERE ';
2197 l_where2 := l_where2 || ' hp.collector_id = ac.collector_id ';
2198 l_where2 := l_where2 || ' and ac.resource_type = ''RS_GROUP'' ';
2199 l_where2 := l_where2 || ' and ac.resource_id = jtg.group_id ';
2200 l_where2 := l_where2 || ' and jtg.resource_id = wi.resource_id ';
2201 l_where2 := l_where2 || ' and jtg.group_member_id = jtr.role_resource_id ';
2202 l_where2 := l_where2 || ' and jtr.role_id=jtrr.role_id ';
2203 l_where2 := l_where2 || ' and jtrr.role_type_code=''COLLECTIONS'' ';
2204 l_where2 := l_where2 || ' and wi.status_code = ''OPEN'' ';
2205 l_where2 := l_where2 || ' and trunc(nvl(ac.inactive_date,sysdate)) >= trunc(sysdate) ';
2206 l_where2 := l_where2 || ' and nvl(ac.status,''A'') = ''A'' ';
2207 l_where2 := l_where2 || ' and nvl(hp.status,''A'') = ''A'' ';
2208 l_where2 := l_where2 || ' and nvl(jtg.delete_flag,''N'') = ''N'' ';
2209 l_where2 := l_where2 || ' AND nvl(jtr.delete_flag,''N'') = ''N'' ';
2210 l_where2 := l_where2 || ' and jtg.resource_id = rs.resource_id ';
2211 l_where2 := l_where2 || ' and trunc(nvl(rs.end_date_active,sysdate)) >= trunc(sysdate) ';
2212 l_where2 := l_where2 || ' and trunc(nvl(jtr.end_date_active,sysdate)) >= trunc(sysdate) ';
2213 l_group2 := 'group by jtg.resource_id, jtg.person_id ';
2214
2215 l_select3 := 'SELECT jtg.person_id, jtg.resource_id, 0 ';
2216 l_select3 := l_select3 || 'FROM hz_customer_profiles hp, ar_collectors ac, ';
2217 l_select3 := l_select3 || ' jtf_rs_group_members jtg, jtf_rs_resource_extns rs ';
2218 l_select3 := l_select3 || ' , jtf_rs_role_relations jtr,JTF_RS_ROLES_b jtrr ';
2219 l_where3 := ' WHERE ';
2220 l_where3 := l_where3 || ' hp.collector_id = ac.collector_id ';
2221 l_where3 := l_where3 || ' and ac.resource_type = ''RS_GROUP'' ';
2222 l_where3 := l_where3 || ' and ac.resource_id = jtg.group_id ';
2223 l_where3 := l_where3 || ' and jtg.group_member_id = jtr.role_resource_id ';
2224 l_where3 := l_where3 || ' and jtr.role_id=jtrr.role_id ';
2225 l_where3 := l_where3 || ' and jtrr.role_type_code=''COLLECTIONS'' ';
2226 l_where3 := l_where3 || ' and not exists (select null from iex_strategy_work_items wi ';
2227 l_where3 := l_where3 || ' where jtg.resource_id = wi.resource_id ';
2228 l_where3 := l_where3 || ' and wi.status_code = ''OPEN'') ';
2229 l_where3 := l_where3 || ' and trunc(nvl(ac.inactive_date,sysdate)) >= trunc(sysdate) ';
2230 l_where3 := l_where3 || ' and nvl(ac.status,''A'') = ''A'' ';
2231 l_where3 := l_where3 || ' and nvl(hp.status,''A'') = ''A'' ';
2232 l_where3 := l_where3 || ' and nvl(jtg.delete_flag,''N'') = ''N'' ';
2233 l_where3 := l_where3 || ' and nvl(jtr.delete_flag,''N'') = ''N'' ';
2234 l_where3 := l_where3 || ' and jtg.resource_id = rs.resource_id ';
2235 l_where3 := l_where3 || ' and trunc(nvl(rs.end_date_active,sysdate)) >= trunc(sysdate) ';
2236 l_where3 := l_where3 || ' and trunc(nvl(jtr.end_date_active,sysdate)) >= trunc(sysdate) ';
2237 l_group3 := ' group by jtg.resource_id, jtg.person_id ';
2238
2239 l_select4 := 'SELECT ac.employee_id, ac.resource_id, count(cas_id) ';
2240 --Begin Bug#6962575 29-Jul-2008 barathsr
2241 -- l_select4 := l_select4 || ' FROM hz_customer_profiles hp, jtf_rs_resource_extns rs, iex_cases_vl wi,ar_collectors ac ';
2242 l_select4 := l_select4 || ' FROM hz_customer_profiles hp, jtf_rs_resource_extns rs, iex_cases_all_b wi,ar_collectors ac ';
2243 --End Bug#6962575 29-Jul-2008 barathsr
2244 l_where4 := ' WHERE hp.party_id = :1 ';
2245 l_where4 := l_where4 || ' and rs.resource_id = ac.resource_id ';
2246 l_where4 := l_where4 || ' and hp.collector_id = ac.collector_id ';
2247 l_where4 := l_where4 || ' and ac.resource_id = wi.owner_resource_id(+) ';
2248 l_where4 := l_where4 || ' and rs.user_id is not null ';
2249 l_where4 := l_where4 || ' and ac.employee_id is not null ';
2250 l_where4 := l_where4 || ' and trunc(nvl(ac.inactive_date,sysdate)) >= trunc(sysdate) ';
2251 l_where2 := l_where2 || ' and trunc(nvl(rs.end_date_active,sysdate)) >= trunc(sysdate) ';
2252 l_where4 := l_where4 || ' and nvl(ac.status,''A'') = ''A'' ';
2253 l_where4 := l_where4 || ' and nvl(hp.status,''A'') = ''A'' ';
2254 l_group4 := l_group4 || ' group by ac.resource_id, ac.employee_id ORDER BY 3 ';
2255
2256 --Begin Bug#5229763 schekuri 27-Jul-2006
2257 --Added following sql's for getting resource for task creation in dunning callback concurrent program
2258 l_select5 := ' SELECT jtg.person_id, jtg.resource_id, count(t.task_id) ';
2259 l_select5 := l_select5 || ' FROM hz_customer_profiles hp, jtf_tasks_vl t, jtf_task_statuses_vl s, ';
2260 l_select5 := l_select5 || ' ar_collectors ac, jtf_rs_group_members jtg, jtf_rs_resource_extns rs ';
2261 l_select5 := l_select5 || ' , jtf_rs_role_relations jtr,JTF_RS_ROLES_b jtrr ';
2262 l_where5 := ' WHERE ';
2263 l_where5 := l_where5 || ' hp.collector_id = ac.collector_id ';
2264 l_where5 := l_where5 || ' and ac.resource_type = ''RS_GROUP'' ';
2265 l_where5 := l_where5 || ' and ac.resource_id = jtg.group_id ';
2266 l_where5 := l_where5 || ' and jtg.resource_id = t.owner_id ';
2267 l_where5 := l_where5 || ' and jtg.group_member_id = jtr.role_resource_id ';
2268 l_where5 := l_where5 || ' and jtr.role_id=jtrr.role_id ';
2269 l_where5 := l_where5 || ' and jtrr.role_type_code=''COLLECTIONS'' ';
2270 l_where5 := l_where5 || ' and t.task_name = ''Dunning Callback'' ';
2271 l_where5 := l_where5 || ' and t.task_status_id = s.task_status_id ';
2272 l_where5 := l_where5 || ' and upper(s.name) = ''OPEN'' ';
2273 l_where5 := l_where5 || ' and trunc(nvl(ac.inactive_date,sysdate)) >= trunc(sysdate) ';
2274 l_where5 := l_where5 || ' and nvl(ac.status,''A'') = ''A'' ';
2275 l_where5 := l_where5 || ' and nvl(hp.status,''A'') = ''A'' ';
2276 l_where5 := l_where5 || ' and nvl(jtg.delete_flag,''N'') = ''N'' ';
2277 l_where5 := l_where5 || ' and nvl(jtr.delete_flag,''N'') = ''N'' ';
2278 l_where5 := l_where5 || ' and jtg.resource_id = rs.resource_id ';
2279 l_where5 := l_where5 || ' and trunc(nvl(rs.end_date_active,sysdate)) >= trunc(sysdate) ';
2280 l_where5 := l_where5 || ' and trunc(nvl(jtr.end_date_active,sysdate)) >= trunc(sysdate) ';
2281 l_group5 := 'group by jtg.resource_id, jtg.person_id ';
2282
2283 l_select6 := 'SELECT jtg.person_id, jtg.resource_id, 0 ';
2284 l_select6 := l_select6 || ' FROM hz_customer_profiles hp, ar_collectors ac, ';
2285 l_select6 := l_select6 || ' jtf_rs_group_members jtg, jtf_rs_resource_extns rs ';
2286 l_select6 := l_select6 || ' , jtf_rs_role_relations jtr,JTF_RS_ROLES_b jtrr ';
2287 l_where6 := ' WHERE ';
2288 l_where6 := l_where6 || ' hp.collector_id = ac.collector_id ';
2289 l_where6 := l_where6 || ' and ac.resource_type = ''RS_GROUP'' ';
2290 l_where6 := l_where6 || ' and ac.resource_id = jtg.group_id ';
2291 l_where6 := l_where6 || ' and jtg.group_member_id = jtr.role_resource_id ';
2292 l_where6 := l_where6 || ' and jtr.role_id=jtrr.role_id ';
2293 l_where6 := l_where6 || ' and jtrr.role_type_code=''COLLECTIONS'' ';
2294 l_where6 := l_where6 || ' and not exists (select 1 from jtf_tasks_vl t, jtf_task_statuses_vl s ';
2295 l_where6 := l_where6 || ' where jtg.resource_id = t.owner_id ';
2296 l_where6 := l_where6 || ' and t.task_name = ''Dunning Callback'' ';
2297 l_where6 := l_where6 || ' and t.task_status_id = s.task_status_id ';
2298 l_where6 := l_where6 || ' and upper(s.name) = ''OPEN'') ';
2299 l_where6 := l_where6 || ' and trunc(nvl(ac.inactive_date,sysdate)) >= trunc(sysdate) ';
2300 l_where6 := l_where6 || ' and nvl(ac.status,''A'') = ''A'' ';
2301 l_where6 := l_where6 || ' and nvl(hp.status,''A'') = ''A'' ';
2302 l_where6 := l_where6 || ' and nvl(jtg.delete_flag,''N'') = ''N'' ';
2303 l_where6 := l_where6 || ' and nvl(jtr.delete_flag,''N'') = ''N'' ';
2304 l_where6 := l_where6 || ' and jtg.resource_id = rs.resource_id ';
2305 l_where6 := l_where6 || ' and trunc(nvl(rs.end_date_active,sysdate)) >= trunc(sysdate) ';
2306 l_where6 := l_where6 || ' and trunc(nvl(jtr.end_date_active,sysdate)) >= trunc(sysdate) ';
2307 l_group6 := ' group by jtg.resource_id, jtg.person_id ';
2308 --End Bug#5229763 schekuri 27-Jul-2006
2309 --End Bug 7134688 gnramasa 17th June 08
2310
2311 if p_level = 'PARTY' then
2312 l_query := l_select1 || l_where1 || ' and hp.party_id = :1 and hp.cust_account_id = -1 ' ;
2313 l_query := l_query || l_union || '( ' || l_select2 || l_where2 || ' and hp.party_id = :1 and hp.cust_account_id = -1 ' || l_group2;
2314 l_query := l_query || l_union || l_select3 || l_where3 || ' and hp.party_id = :1 and hp.cust_account_id = -1 ' || l_group3 || ' )' || l_order;
2315 end if;
2316 if p_level = 'ACCOUNT' then
2317 l_query := l_select1 || l_where1 || ' and hp.cust_account_id = :1 and hp.site_use_id is null ' ;
2318 l_query := l_query || l_union || '( ' || l_select2 || l_where2 || ' and hp.cust_account_id = :1 and hp.site_use_id is null ' || l_group2;
2319 l_query := l_query || l_union || l_select3 || l_where3 || ' and hp.cust_account_id = :1 and hp.site_use_id is null ' || l_group3 || ' )' || l_order;
2320 end if;
2321 if p_level = 'BILLTO' then
2322 l_query := l_select1 || l_where1 || ' and hp.site_use_id = :1 ' ;
2323 l_query := l_query || l_union || '( ' || l_select2 || l_where2 || ' and hp.site_use_id = :1 ' || l_group2;
2324 l_query := l_query || l_union || l_select3 || l_where3 || ' and hp.site_use_id = :1 ' || l_group3 || ' )' || l_order;
2325 end if;
2326 if p_level = 'CASE' then
2327 l_query := l_select4 || l_where4 || l_group4 ;
2328 end if;
2329 --Begin Bug#5229763 schekuri 27-Jul-2006
2330 --Added following for getting resource for task creation in dunning callback concurrent program
2331 if p_level = 'DUNNING_PARTY' then
2332 l_query := l_select1 || l_where1 || ' and hp.party_id = :1 and hp.cust_account_id = -1 ' ;
2333 l_query := l_query || l_union || '( ' || l_select5 || l_where5 || ' and hp.party_id = :1 and hp.cust_account_id = -1 ' || l_group5;
2334 l_query := l_query || l_union || l_select6 || l_where6 || ' and hp.party_id = :1 and hp.cust_account_id = -1 ' || l_group6 || ' )' || l_order;
2335 end if;
2336 if p_level = 'DUNNING_ACCOUNT' then
2337 l_query := l_select1 || l_where1 || ' and hp.cust_account_id = :1 and hp.site_use_id is null ' ;
2338 l_query := l_query || l_union || '( ' || l_select5 || l_where5 || ' and hp.cust_account_id = :1 and hp.site_use_id is null ' || l_group5;
2339 l_query := l_query || l_union || l_select6 || l_where6 || ' and hp.cust_account_id = :1 and hp.site_use_id is null ' || l_group6 || ' )' || l_order;
2340 end if;
2341 if p_level = 'DUNNING_BILLTO' then
2342 l_query := l_select1 || l_where1 || ' and hp.site_use_id = :1 ' ;
2343 l_query := l_query || l_union || '( ' || l_select5 || l_where5 || ' and hp.site_use_id = :1 ' || l_group5;
2344 l_query := l_query || l_union || l_select6 || l_where6 || ' and hp.site_use_id = :1 ' || l_group6 || ' )' || l_order;
2345 end if;
2346 if p_level = 'DUNNING_PARTY_ACCOUNT' then
2347 l_query := l_select1 || l_where1 || ' and hp.party_id = :1 and hp.cust_account_id <> -1 and hp.site_use_id is null ' ;
2348 l_query := l_query || l_union || '( ' || l_select5 || l_where5 || ' and hp.party_id = :1 and hp.cust_account_id <> -1 and hp.site_use_id is null ' || l_group5;
2349 l_query := l_query || l_union || l_select6 || l_where6 || ' and hp.party_id = :1 and hp.cust_account_id <> -1 and hp.site_use_id is null ' || l_group6 || ' )' || l_order;
2350 end if;
2351 --End Bug#5229763 schekuri 27-Jul-2006
2352
2353 -- End SQL statements
2354
2355 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ':Parameters: ');
2356 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ':p_level: ' || p_level);
2357 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ':p_level_id: ' || p_level_id);
2358 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ':Data query is: ' || l_query);
2359
2360 --if p_level = 'PARTY' or p_level = 'ACCOUNT' or p_level = 'BILLTO' then
2361 if p_level <> 'CASE' then --Modified for Bug#5229763 schekuri 27-Jul-2006
2362 OPEN c_collector FOR l_query USING p_level_id, p_level_id, p_level_id;
2363 else
2364 OPEN c_collector FOR l_query USING p_level_id;
2365 end if;
2366
2367 LOOP
2368 idx := idx + 1;
2369 -- Begin fix bug #5360791-JYPARK-06/28/2006-Change fetch variable order
2370 -- FETCH c_collector INTO x_resource_tab(idx).resource_id,
2371 -- x_resource_tab(idx).person_id,
2372 -- l_count;
2373 FETCH c_collector INTO x_resource_tab(idx).person_id,
2374 x_resource_tab(idx).resource_id,
2375 l_count;
2376 -- End fix bug #5360791-JYPARK-06/28/2006-Change fetch variable order
2377 if c_collector%notfound then
2378 exit;
2379 end if;
2380
2381 iex_debug_pub.logmessage (G_PKG_NAME || '.' || l_api_name || 'idx= ' || idx);
2382 iex_debug_pub.logmessage (G_PKG_NAME || '.' || l_api_name || 'collector_resource_id = ' || x_resource_tab(idx).person_id);
2383 End LOOP;
2384
2385 CLOSE c_collector;
2386
2387 -- Standard check of p_commit
2388 IF FND_API.To_Boolean(p_commit) THEN
2389 COMMIT WORK;
2390 END IF;
2391
2392 -- Standard call to get message count and if count is 1, get message info
2393 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
2394
2395 iex_debug_pub.logmessage ('**** END get_assigned_collector ************');
2396
2397 EXCEPTION
2398 WHEN FND_API.G_EXC_ERROR THEN
2399 ROLLBACK TO get_assigned_collector;
2400 x_return_status := FND_API.G_RET_STS_ERROR;
2401 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
2402
2403 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2404 ROLLBACK TO get_assigned_collector;
2405 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2406 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
2407
2408 WHEN OTHERS THEN
2409 ROLLBACK TO get_assigned_collector;
2410 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2411 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2412 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
2413 END IF;
2414 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
2415
2416 END get_assigned_collector;
2417 --End bug#5373412 schekuri 10-Jul-2006
2418
2419 --Begin bug 6723556 gnramasa 11th Jan 08
2420 --If all the tranactions that belong to a contract has a status not equal to CURRENT or CLOSE
2421 --then return contract status as DELINQUENT else return as CURRENT
2422
2423 FUNCTION CheckContractStatus
2424 (
2425 p_contract_number IN VARCHAR2
2426 )
2427 return varchar2 is
2428 l_contract_status VARCHAR2(10) := 'CURRENT';
2429 l_count NUMBER := 0;
2430 BEGIN
2431 iex_debug_pub.logmessage ('Start CheckContractStatus');
2432 iex_debug_pub.logmessage ('p_contract_number :' || p_contract_number);
2433
2434 IF p_contract_number IS NOT NULL then
2435 select count(1) into l_count
2436 from iex_delinquencies_all del,ra_customer_trx_lines_all trl
2437 where del.TRANSACTION_ID = trl.CUSTOMER_TRX_ID
2438 and del.status not in ('CURRENT', 'CLOSE')
2439 and trl.INTERFACE_LINE_CONTEXT = 'OKL_CONTRACTS'
2440 and trl.INTERFACE_LINE_ATTRIBUTE6 = p_contract_number;
2441
2442 if l_count >1 then
2443 l_contract_status := 'DELINQUENT';
2444 else
2445 l_contract_status := 'CURRENT';
2446 end if;
2447 END IF;
2448 iex_debug_pub.logmessage ('CheckContractStatus : l_contract_status :' || l_contract_status);
2449 iex_debug_pub.logmessage ('End CheckContractStatus');
2450 return l_contract_status;
2451 EXCEPTION
2452 WHEN OTHERS THEN
2453 iex_debug_pub.logmessage ('CheckContractStatus: In other exception');
2454 iex_debug_pub.logmessage ('CheckContractStatus : l_contract_status :' || l_contract_status);
2455 return l_contract_status;
2456 END;
2457 --End bug 6723556 gnramasa 11th Jan 08
2458
2459 --Begin bug 6627832 gnramasa 21st Jan 08
2460 FUNCTION ValidateXMLRequestId
2461 (
2462 p_xml_request_id IN number
2463 ) return boolean is
2464 l_req_count number;
2465 begin
2466 select count(1)
2467 into l_req_count
2468 from iex_xml_request_histories
2469 where xml_request_id=p_xml_request_id
2470 and length(document)>0;
2471 if l_req_count>0 then
2472 return true;
2473 else
2474 return false;
2475 end if;
2476 exception
2477 when others then
2478 return false;
2479 end;
2480 --End bug 6627832 gnramasa 21st Jan 08
2481
2482 --Begin bug 6717279 by gnramasa 25th Aug 08
2483 -- this function will copy the value to pl/sql table.
2484 Procedure copy_cust_acct_value
2485 (
2486 p_fe_cust_acct_rec IN DBMS_SQL.NUMBER_TABLE
2487 ) is
2488 l_count number;
2489 begin
2490 iex_debug_pub.logmessage ('Begin copy_cust_acct_value');
2491 l_count := p_fe_cust_acct_rec.count;
2492 iex_debug_pub.logmessage ('copy_cust_acct_value, l_count: ' || l_count);
2493 p_be_cust_acct_rec.delete;
2494 for i in 1..l_count loop
2495 p_be_cust_acct_rec(i) := p_fe_cust_acct_rec(i);
2496 iex_debug_pub.logmessage ('p_be_cust_acct_rec('||i||') : ' || p_be_cust_acct_rec(i));
2497 end loop;
2498 iex_debug_pub.logmessage ('End copy_cust_acct_value');
2499 End copy_cust_acct_value;
2500
2501 --this function accepts cust_account_id. If this value exists in the pl/sql table
2502 --then it will return 'Y', else 'N'
2503 FUNCTION cust_acct_id_check
2504 (
2505 p_cust_acct_id IN number
2506 )
2507 return varchar is
2508 l_count number;
2509 begin
2510 iex_debug_pub.logmessage ('Begin cust_acct_id_check');
2511 iex_debug_pub.logmessage ('cust_acct_id_check, p_cust_acct_id: ' || p_cust_acct_id);
2512 l_count := p_be_cust_acct_rec.count;
2513 iex_debug_pub.logmessage ('cust_acct_id_check, l_count: ' || l_count);
2514 for i in 1..l_count loop
2515 if p_be_cust_acct_rec(i) = p_cust_acct_id then
2516 iex_debug_pub.logmessage ('cust_acct_id_check, value exists in pl/sql table, so returning Y');
2517 return 'Y'; -- value exists in pl/sql table, so return 'Y'
2518 end if;
2519 end loop;
2520 iex_debug_pub.logmessage ('End cust_acct_id_check');
2521 iex_debug_pub.logmessage ('cust_acct_id_check, value doesn''t exists in pl/sql table, so returning N');
2522 return 'N'; -- value doesn't exists in pl/sql table, so return 'N'
2523 End cust_acct_id_check;
2524 --End bug 6717279 by gnramasa 25th Aug 08
2525
2526 BEGIN
2527 G_APPL_ID := FND_GLOBAL.Prog_Appl_Id;
2528 G_LOGIN_ID := FND_GLOBAL.Conc_Login_Id;
2529 G_PROGRAM_ID := FND_GLOBAL.Conc_Program_Id;
2530 G_USER_ID := FND_GLOBAL.User_Id;
2531 G_REQUEST_ID := FND_GLOBAL.Conc_Request_Id;
2532
2533 PG_DEBUG := TO_NUMBER(NVL(FND_PROFILE.value('IEX_DEBUG_LEVEL'), '20'));
2534
2535 END IEX_UTILITIES;